Question

How To Test The Database (Step-By-Step Process)?

Answer 1

The general test process testing database is not very different from any other application.

The following are the core 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>;

Some Practical Tips

#1) Write Queries yourself:

To test the Database accurately, the tester should have very good knowledge of SQL and DML (Data Manipulation Language) statements. The tester should also know the internal DB structure of AUT.

You can combine GUI and data verification in respective tables for better coverage. If you are using the SQL server then you can make use of SQL Query Analyzer for writing queries, executing them and retrieving results.

This is the best and robust way of testing a database when the application is of a small or medium level of complexity.

If the application is very complex then it may be hard or impossible for the tester to write all the required SQL queries. For complex queries, you take help from the developer. I always recommend this method as it gives you confidence in testing and also enhances your SQL skills.

#2) Observe the data in each table:

You can perform data verification using the results of CRUD operations. This can be done manually by using application UI when you know the database integration. But this can be a tedious and cumbersome task when there is huge data in different database tables.

For Manual Data Testing, the Database tester must possess a good knowledge of database table structure.

#3) Get queries from the developers:

This is the simplest way to test the Database. Perform any CRUD operation from GUI and verify its impacts by executing the respective SQL queries obtained from the developer. It neither requires a good knowledge of SQL nor requires a good knowledge of the application’s DB structure.

But this method needs to be used cautiously. What if the query given by the developer is semantically wrong or does not fulfill the user’s requirement correctly? The process will simply fail to validate data.

#4) Make use of Database Automation Testing tools:

There are several tools available for the Data Testing process. You should choose the correct tool as per your needs and make the best use of it.