Question

Write the scope of research paper about database testing techniques where we provide comparisons with different techniques. Take 8 research papers ( 4 from 2016) and write a scope through these papers which are selected or downloaded from the web.

The main topic is database testing techniques and we have to generate a comparison between these testing techniques and write down a scope.

Answer 1

What Is Database Testing And Why Is It Important?

The database is a software subsystem which provides an efficient way to store user data and allows requesting it via a structured query language known as SQL. Not only does it stores the critical business information but also functions as the backbone of an entire application. Hence, database testing is essential for software testers to ensure the system is working correctly.

Database testing is a means to validate the data stored in the database, objects controlling data, and the functionality wrapped around it. The databases use objects to manage data like tables for storage, views for representation, and functions/triggers for manipulation.

Nowadays, the use of databases is wide-spread, especially in web applications. And they are getting more and more complex with new technologies and platforms. That’ss why checking the quality, security, and correctness of data make it eminent to learn database testing.

Database Testing

The process to perform database testing is similar to testing of other applications. DB testing can be described with key processes given below.

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

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

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

Database Testing Stages

DB testing is not a tedious process and includes various stages in database testing lifecycle in accordance with the test processes.

The key stages in database testing are −

• Checking the initial state
• Test run
• Outcome validation as per expected result
• Generating the results

What Is The Scope Of Database Testing Techniques Or What Are The Areas To Cover While Testing A Database?

Testing a database requires checking the following areas are working as expected.

• Database objects which include tables, views and stored procedures.
• Validation of data being entered and getting stored in the database.
• Making sure the system is honoring the constraints and data changes (insert/delete/update) are reflecting correctly.
• Ensure the system can execute end to end database transactions and support concurrency.
• Testing of database migration to ensure compatibility across different versions.
• Verify performance of database indices, triggers, and procedures.
• Security compliance testing to rule out any unauthorized access or any threats to data.

Database Testing Techniques:

• Database Schema Testing
• Stored Procedure Tests
• Trigger Tests
• Functional Testing Method
• Stress Testing
• Benchmark Testing

1.Database Schema Testing

in DB Testing is to check the initial state of the database before starting the testing process. Then database behavior is tested for defined test cases. In accordance with the results obtained, test cases are customized.

For successful database testing, the workflow given below is executed by every single test.

• Cleaning up the database − If there is testable data in the database, it should be emptied.

• Set up Fixture − This involves entering the data into the database and check the current state of the database.

• Perform test, verify results and generate results − The Test is run and the output is verified. If the output is as per expected results, the next step is to generate the results as per requirement. Otherwise, testing is repeated to find the bugs in database.

Verifying Databases and devices

• Verifying the name of database
• Verifying the data device, log device and dump device
• Verifying if enough space allocated for each database
• Verifying database option setting

Tables, columns, column types rules check

Verify the items given below to find out the differences between actual and applied setting.

• Name of all the tables in database

• Column names for each table

• Column types for each table

• NULL value checked or not

• Whether a default is bound to correct table columns

• Rule definitions to correct table names and access privileges

Key and Indexes

Verify the Key and indexes in each table −

• Primary key for each table

• Foreign keys for each table

• Data types between a foreign key column and a column in other table Indices, clustered or non-clustered unique or not unique

2.  Stored Procedure Tests

It involves checking whether a stored procedure is defined and the output results are compared. In a Stored Procedure test, the following points are checked −

• Stored procedure name

• Parameter names, parameter types, etc.

• Output − Whether the output contains many records. Zero rows are effected or only a few records are extracted.

• What is the function of Stored Procedure and what a stored procedure is not supposed to do?

• Passing sample input queries to check if a stored procedure extracts correct data.

• Stored Procedure Parameters − Call stored procedure with boundary data and with valid data. Make each parameter invalid once and run a procedure.

• Return values − Check the values that are returned by stored procedure. In case of a failure, nonzero must be returned.

• Error messages check − Make changes in such a way that the stored procedure fails and generate every error message at least once. Check any exception scenarios when there is no predefined error message.

3.Trigger Tests

In a Trigger test, the tester must perform the following tasks −

• Make sure the trigger name is correct.
• Validate the trigger if it is generated for a specific table column.
• Trigger’s update validation.
• Update a record with a valid data.
• Update a record with invalid data and cover every trigger error.
• Update a record when it is still referenced by a row in other table.
• Ensure rolling back transactions when a failure occurs.
• Find out any cases in which a trigger is not supposed to roll back transactions.

Server Setup Scripts

Two types of tests should be performed −

• Setting up the database from scratch, and
• To set up an existing database.

Integration Tests of SQL Server

Integration tests should be performed after you are through with component testing.

• Stored procedures should be called intensively to select, insert, update, and delete records in different tables to find any conflicts and incompatibility.

• Any conflicts between schema and triggers.

• Any conflicts between stored procedures and schema.

• Any conflicts between stored procedures and triggers.

4.Functional Testing Method

Functional testing can be performed by dividing the database into modules as per functionality. The functionalities are of the following two types −

• Type 1 − In Type 1 testing, find out the features of the project. For each major feature, find out the schema, triggers, and stored procedures responsible to implement that function and put them into a functional group. Then test each group together.

• Type 2 − In Type 2 testing, the border of functional groups in a back-end is not obvious. You can check the data flow and see where you can check the data. Start from the front-end.

The following process takes place −

• When a service has a request or saves data, some stored procedures will get called.

• The procedures will update some tables.

• Those stored procedures will be the place to start testing and those tables will be the place to check the test results.

5.Stress Testing

Stress Testing involves getting a list of major database functions and corresponding stored procedures. Follow the steps given below for Stress Testing −

• Write test scripts to try those functions and every function must be checked at least once in a full cycle.

• Perform the test scripts again and again for a specific time period.

• Verifying the log files to check any deadlocks, failure out of memory, data corruption, etc.

6.Benchmark Testing

If your database does not have any data problems or bugs, system performance can be checked. A poor system performance can be found in benchmark testing by checking the parameters given below −

• System level performance
• Identify most-likely-used functions/features
• Timing – maximum time, minimum time and average time to perform functions
• Access volume

Testing a Database via Front-end

Back-end bugs can also be found sometimes by doing front-end testing. You can follow the simple steps given below to detect bugs by front-end testing.

• Write queries from the front-end and issue the searches.

• Pick up an existing record, change the values in some fields, and save the record. (It involves the UPDATE statement or update stored procedures and update triggers.)

• Insert a new menu item in the front-end window. Fill in the information and save the record. (It involves the INSERT statements or insertion stored procedures and deletion triggers.)

• Pick up an existing record, click on the DELETE or REMOVE button, and confirm the deletion. (It involves the DELETE statement or deletion stored procedures and deletion triggers.)

• Repeat these test-cases with invalid data and see how the database responds.