Importance of Database Testing for Quality Product

Database testing
The data is stored in the database in tables. However, tables may not be the only objects in the database. A database may have other objects like views, stored procedures and functions. These other objects help the users access the data in required forms. The data itself is stored in the tables. Database testing involves finding out the answers to the following questions:

Questions related to database structure
1. Is the data organized well logically?
2. Does the database perform well?
3. Do the database objects like views, triggers, stored procedures, functions and jobs work correctly?
4. Does the database implement constraints to allow only correct data to be stored in it?
5. Is the data secure from unauthorized access?

Questions related to data
1. Is the data complete?
2. Is all data factually correct i.e. in sync with its source, for example the data entered by a user via the application UI?
3. Is there any unnecessary data present?

The general test process for DB testing is not very different from any other application. The following are the steps:

Step #1) Prepare the environment
Step #2) Run a test
Step #3) Check test result
Step #4) Validate according to the expected results
Step #5) Report the findings to the respective stakeholders

What to test

1) Transactions:
When testing transactions it is important to make sure that they satisfy the ACID properties.

The following are the statements commonly used:
• BEGIN TRANSACTION TRANSACTION# • END TRANSACTION TRANSACTION#

Rollback statement ensures that the database lies in a consistent state.
• ROLLBACK TRANSACTION#

After these statements are executed, use a select to make sure if the changes have been reflected.

• SELECT * FROM TABLENAME

2) Database schema:

Database schema is nothing but a formal definition of the how the data is going to be organized into a DB. To test it:

• Identify the requirements based on which the database operates. Sample requirements:

• Primary keys to be created before any other fields are created.

• Foreign keys should be completely indexed for easy retrieval and searching.

• Field names starting or ending with certain characters.

• Fields with a constraint that certain values can or cannot be inserted.

• Use one of the following ways according to the relevance:

• SQL Query DESC

to validate the schema.

• Regular expressions for validating the names of the individual fields and their values

• Tools like SchemaCrawler

3) Trigger:

When a certain event takes places on a certain table, a piece of code (a trigger) can be auto-instructed to be executed.

For example, a new student joined a school. The student is taking 2 classes; math and science. The student is added to the “student table”. A trigger could be adding the student to the corresponding subject tables once he is added to the student table.
The common method to test is to execute SQL query embedded in the trigger independently first and record the result. Follow this up with executing the trigger as a whole. Compare the results.

These are tested during both the black box and white box testing phases.

• White box testing: Stubs and drivers are to insert or update or delete data that would result in the trigger being invoked. The basic idea is to just test the DB alone even before the integration with the front end (UI) is made.

• Black box testing:

a) Since the UI and DB integration is now available; we can insert/delete/update data from the front end in a way that the trigger gets invoked. Following that select statements can be used to retrieve the DB data to see if the trigger was successful in performing the intended operation.

b) Second way to test this is to directly load the data that would invoke the trigger and see if it works as intended.

4) Stored Procedures:

Stored procedures are more or less similar to user defined functions. These can be invoked by a call procedure/execute procedure statements and the output is usually in the form of result sets.

These are stored in the RDBMS and are available for applications.

These are also tested during:

• White box testing: Stubs are used to invoke the stored procedures and then the results are validated against the expected values.

• Black box testing: Perform an operation from the frontend(UI) of the application and check for the execution of the stored procedure and its results.

  1. Field constraints – Default value, unique value and foreign key:

• Perform a front end operation which overruns the database object condition • Validate the results with a SQL Query.

Checking the default value for a certain field is quite simple. It is a part of business rule validation. You can do it manually or you can use tools like QTP to do so. Manually, you can perform an action that will add a value other than the default value into the field from the front end and see if it results in an error.

The following is a sample VBScript code:
1 Function VBScriptRegularexpressionvlaidation(pattern , stringtomatch)
2 Set newregexp = new RegExp
3 newregexp.Pattern = “
4 newregexp.Ignorecase = True
5 newregexp.Global = True
6 VBScriptRegularexpressionvlaidation = newregexp.Test(stringtomatch)
7 End Function
8 Msgbox VBScriptRegularexpressionvlaidation(pattern , stringtomatch)

The result to the above code is true if the default value exists or false if it doesn’t.
Checking the unique value can be done exactly the way we did for the default values. Try entering values from the UI that will violate this rule and see if an error gets displayed.

Automation VB script code can be:
1 Function VBScriptRegularexpressionvlaidation(pattern , stringtomatch)
2 Set newregexp = new RegExp
3 newregexp.Pattern = “
4 newregexp.Ignorecase = True
5 newregexp.Global = True
6 VBScriptRegularexpressionvlaidation = newregexp.Test(stringtomatch)
7 End Function
8 Msgbox VBScriptRegularexpressionvlaidation(pattern , stringtomatch)

For the foreign key constraint validation use data loads that directly input data that violates the constraint and see if the application restricts the same or not. Along with the back end data load, perform the front end UI operations too in a way that are going to violate the constraints and see if the relevant error is displayed.

Database Testing is really important part of Testing for a quality product.