All About Database Testing – Why to Test, How to Test, and What to Test?

Database Testing Guide:

Computer applications are more complex these days with technologies like Android and also with lots of smartphone apps. The more complex the front ends, the more intricate the back ends become.  So it is all the more important to learn about DB testing and be able to validate databases effectively to ensure security and quality databases.

In this article, you will learn all about Database Testing. Why test- How to test- What to test.

What You Will Learn:

Why do we test a database?

Below, we will see why the following aspects of a DB should be validated:

1) Data Mapping: In software systems, data often travels back and forth from the UI (user interface) to the backend DB and vice versa. So these are some aspects to watch for:

2) ACID properties validation:  Atomicity, Consistency, Isolation and Durability. Every transaction a DB performs has to adhere to these four properties. (image credit)

3) Data integrity:

This means that following any of the CRUD operations, the updated and most recent values/status of shared data should appear on all the forms and screens. A value should not be updated on one screen and display an older value on another one. So devise your DB test cases in a way to include checking the data in all the places it appears to see if it is consistently the same.

4) Business rule conformity:  More complexity in databases means more complicated components like relational constraints, triggers, stored procedures, etc. So testers will have to come up with appropriate SQL queries in order to validate these complex objects.

How to test – Database Testing Process

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

Usually, SQL queries are used to develop the tests. The most commonly used command is “Select”.

Select * from <tablename> where <condition>

Apart from Select, SQL has 3 important types of commands:

  1. DDL: Data definition language
  2. DML: Data manipulation language
  3. DCL: Data control language

Let us see the syntax for the most commonly used statements.

Data Definition language Uses CREATE, ALTER, RENAME, DROP and TRUNCATE to handle tables (and indexes).

Data Manipulation language Includes statements to add, update and delete records.



Data control language: Deals with giving authorization to users for manipulation and access to the data. Grant and Revoke are the two statements used.

Grant syntax:

Grant select/update

On <table name>

To <user id1, user id2…useridn>;

Revoke syntax:

Revokeselect/update

on <table name>

from<user id1, user id2…useridn>;

What to test – different components

1) Transactions:

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

These are the statements commonly used:

The Rollback statement ensures that the database remains in a consistent state.

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

2) Database schema:

A database schema is nothing more than a formal definition of the how the data is going to be organized inside a DB. To test it:

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 add the student to the corresponding subject tables once he is added to the student table.

The common method to test is to execute the 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.

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) The 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 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:

5) Field constraints – Default value, unique value and foreign key:

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

The following is a sample VBScript code:


Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)
Set newregexp = new RegExp
newregexp.Pattern = “&lt;Default value as required by the business requirements&gt;”
newregexp.Ignorecase = True
newregexp.Global = True
VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)
End Function
Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)

The result of 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 is displayed.

Automation VB script code can be:


Function VBScriptRegularexpressionvlaidation(pattern , string_to_match)
Set newregexp = new RegExp
newregexp.Pattern = “&lt;Unique value as required by the business requirements&gt;”
newregexp.Ignorecase = True
newregexp.Global = True
VBScriptRegularexpressionvlaidation = newregexp.Test(string_to_match)
End Function
Msgbox VBScriptRegularexpressionvlaidation(pattern , string_to_match)

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

Conclusion:

With all these features, factors and processes to test on a database, there is an increasing demand on the tester to be technically proficient in the key DB concepts. Despite some of the negative beliefs that DB testing creates new bottlenecks and is a lot of additional expenditure – this is a realm of testing that is attracting significant attention and demand.

I hope this article has helped to focus on why that is so and has also provided you with the basic details of what goes into testing a database.

Please let us know your feedback and also share your personal experiences if you have done this before. Your comments and feedback are important to us and our readers to learn from your experience.

If you Like it, Share it!