Database Testing – Interview Questions

Database testing includes performing the data validity, data Integrity testing, performance check related to database and testing of Procedures, triggers and functions in the database.

There are multiple reasons why database testing is performed. There is a need to perform data integrity, validation and data consistency check on database as the backend system is responsible to store the data and is accessed for multiple purpose.

Some of the common reasons why one needs to perform Database testing are as follows −

• To ease the complexity of calls to database backend, developers increase the use of View and Stored Procedures.

• These Stored procedures and Views contain critical tasks such as inserting customer details (name, contact information, etc.) and sales data. These tasks need to be tested at several levels.

• Black box testing performed on front-end is important, but makes it difficult to isolate the problem. Testing at the backend system increases the robustness of the data. That is why database testing is performed on back end system.

• In a database, data comes from multiple applications and there is a possibility that harmful or incorrect data is stored in the database. Therefore, there is a need to check database components regularly. In addition, data integrity and consistency should be checked regularly.

The steps that you need to follow while performing database testing are as follows −

• The data that is being in the database must be verified.
• Verify if the constraints are maintained.
• The performance of the procedures and execution of triggers must be checked.
• Roll back and commit of transaction must be checked.

On the basis of function and structure of a database, DB testing can be categorized into the following categories −

• Structural Database testing − It deals with table and column testing, schema testing, stored procedures and views testing, checking triggers, etc.

• Functional Testing − It involves checking functionality of database from user point of view. Most common type of Functional testing are White box and black box testing.

• Nonfunctional Testing − It involves load testing, risk testing in database, stress testing, minimum system requirement, and deals wot performance of the database.

The most common tools that are used to perform stored procedures testing are LINQ, SP Test tool, etc.

Joins are used to connect two or more tables in some logical manner. Common types of joins include: Inner join, Non-equijoin, Outer join, Self-join, and Cross join.

You can join a single table to itself. In this case, you are using the same table twice.

Step 1 − Connect to the database

db_connect(query1 DRIVER {drivername};SERVER server_name;UID uidname;
   PWD password;DBQ database_name );

Step 2 − Execute the query of the database −

db_excecute_query (write the required query that is to execute); Specify the appropriate condition

Step 3 − Disconnect the database connection by using

db_disconnect(query);

Using Output database checkpoints, SQL manual queries options must be selected. Here, the select query can be written.

First, check the requirement of the stored procedure. The next step is to check if indexes, joins, deletions, update are correct in comparison with tables mentioned in stored procedure.

Next, perform the following tasks −

• Validate the calling procedure name, calling parameters and expected responses for different sets of input parameters.

• Execute the procedure with TOAD or MySQL or Query Analyzer.

• Re-execute the available procedures by sending different parameters, and check the results against expected values.

• Concluding to the process, automate the tests with WinRunner.

The tester should call the stored procedure in the database using the EXEC command. If any parameters are required, they must be passed. Different values of parameters must be passed to confirm if the stored procedure is executed or not. On calling this command it must check and verify the nature and behavior of the database.

Example − If the stored procedure is written to populate some table, the table values must be checked.

We have three types of SQL statements −

• Data Manipulation Language (DML)
• Data Definition Language (DDL)
• Data Control Language (DCL)

DDL statements are used to define the database structure or schema. Some examples −

• CREATE − to create objects in the database

• ALTER − alters the structure of the database

• DROP − delete objects from the database

Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

• Arithmetic Operators
• Comparison/Relational Operators
• Logical Operators
• Set Operators
• Operators used to negate conditions

Union is used to combine the results of two or more Select statements. However it will eliminate the duplicate rows. Union is a set operator.

Union is used to combine the results of two or more Select statements. However it will eliminate duplicate rows

Union All operation is similar to Union, but it also shows the duplicate rows.

Triggers are used to maintain the Integrity of database. To check Trigger is fired or not you can check in audit logs.

Triggers can’t be invoked on demand. They are invoked when an associated action (insert, delete & update) happens on the table on which they are defined. Triggers are used to apply business rules, auditing and also for the referential integrity checks.

First, get the functional requirement. Then, understand the table structure, Joins, Cursors and Triggers, Stored procedure used, and other parameters. Next, you can write a test-case with different values as input to these objects.

DB testing involves testing of back-end components which are not visible to users. It includes database components and DBMS systems such as MySQL and Oracle.

Front-end testing involves checking functionalities of an application and its components like forms, graphs, menus, reports, etc. These components are created using front-end development tools like VB.net, C#, Delphi, etc.

The process to perform database testing is similar to testing of other applications. DB testing can be described with the following key processes −

• Setting up the environment
• Run a test
• Check the test result
• Validating according to the expected results
• Report the findings to the respective stakeholders

Various SQL statements are used to develop the Test cases. Most common SQL statement which is used to perform DB testing is select statement. Apart from this various DDL, DML, DCL statements can also be used.

Example − Create, Insert, Select, Update, etc.

A view is a table that does not really exist in its own right but is instead derived from one or more base table. In other words, there is no stored file that direct represents the view instead a definition of view is stored in data dictionary.

Growth and restructuring of base tables is not reflected in views. Thus the view can insulate users from the changes in the database. Hence accounts for logical data independence.

It specifies user views and their mappings to the conceptual schema.

It is a process of decomposing a table into multiple tables without losing any information. Normalization is done to achieve the following goals −

• To minimize redundancy.
• To minimize insertion, deletion and update anomalies.

Indexing is a technique for determining how quickly specific data can be found. It is used for query performance optimization. Indexing can be of the following types −

• Binary search style indexing
• B-Tree indexing
• Inverted list indexing
• Memory resident table
• Table indexing

SQL is a Structured Query language that is designed specifically for data access operations on normalized relational database structures.

The primary difference between SQL and other conventional programming languages is that SQL statements specify what data operations should be performed rather than how to perform them.

Stored procedures are used to perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client.

PL/SQL uses cursors for all database information accesses statements. The language supports the use two types of cursors − implicit and explicit.

Cold Backup − Cold back is known as taking back up of database files, redo logs, and control file when the instance is shut down. This is a file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy.

If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All the changes that are performed after the last backup is lost.

Hot Backup − Some databases can’t shut down while making a backup copy of the files, so cold backup is not an available option. For these types of database we use hot backup.

SQL subquery is a means of querying two or more tables at the same time. The subquery itself is an SQL SELECT statement contained within the WHERE clause of another SQL SELECT statement, and separated by being enclosed in parenthesis. Some subqueries have equivalent SQL join structures, but correlated subqueries cannot be duplicated by a join

In such a case, you need to test the following aspects −

• Multivalued dependencies
• Functional dependencies
• Candidate keys
• Primary keys
• Foreign keys

You can go to the database and run a relevant SQL query. In WinRunner, you can use database checkpoint function. If the application provides view function, then you can verify the same from the front-end.

Data-driven testing is defined as an automation testing process where application will be tested with multiple test data. It is simple and easy than retesting where tester just sit in front of system and enter different new input values manually from front-end interface.

Once you execute the test-cases and find the defects that has been already detected and fixed. Re-execution of the same test with different input values to confirm the original defect has been successfully removed is called Re-testing.

Retesting is also called Data Driven Testing with a small difference −

• Retesting − It is a manual testing process whereas application testing done with entire new set of data.

• Data-driven Testing − It is an Automation testing process where application will be tested with multiple test data. It is simple and easy than retesting where tester just sit in front of system and enter different new input values manually from front-end interface.

There are four types of data driven testing −

• Dynamic test data submission through keyboard
• Data Driven Tests via .txt, .doc flat files
• Data Driven Tests via front-end objects
• Data Driven Tests via excel sheet

Performance testing is a software testing technique to determine how a system performs in terms of speed, sensitivity and stability under a heavy workload.

The following key points are to be considered while performing database recovery testing −

• Time span when changes or modifications occurs in database system.

• The period by which you want your recovery plan conducted.

• The sensitivity of data in database system. More critical the data is, the more regularly you will need to test the software.

The following tools are used to generate test data −

• Data Factory
• DTM Data Generator
• Turbo Data

There are two types of backup that can be used −

• Physical Backups − Physical backup includes taking back up using 3^rd party backup tools like Veritas net back, IBM Tivoli Manager or user manager backups using OS utilities.

• Logical Backups − Logical backup of database includes taking back up of logical objects like tables, indexes, procedures, etc.

A common tool to take data backup is Oracle Recovery Manager (RMAN) that is an Oracle utility to take database backup.

The following actions are performed in database recovery testing −

• Testing of database system
• Testing of the SQL files
• Testing of partial files
• Testing of data backup
• Testing of Backup tool
• Testing log backups

Database security testing is performed to find the loop holes in security mechanisms and also about finding the vulnerabilities or weaknesses of database system.

Database security testing is performed to check the following aspects −

• Authentication
• Authorization
• Confidentiality
• Availability
• Integrity
• Resilience

SQL Injection threat is the most common type of attack in a database system where malicious SQL statements are inserted in database system and executed to get critical information from database system. This attack takes advantage of loopholes in implementation of user applications. To prevent this user inputs fields should be carefully handled.

The following tools can be used to perform database security testing: Zed Attack Proxy, Paros, Social Engineer Toolkit, Skipfish, Vega, Wapiti, and Web Scarab.

The common challenges that one faces while performing database testing are as follows −

• Testing scope is too large
• Scaled-down test database
• Changes in database structure
• Complex Test Plans
• Good understanding of SQL