Database Testing – Techniques

Database Schema Testing

As mentioned earlier, it involves testing each object in the Schema.

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

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.

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.

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.

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.

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.