How to Perform Database Testing?

In the previous blogs, Overview of Database Testing and What should we test in a Database we got a fair idea about database testing concepts and types of database testing. Now with this blog, we are moving a step ahead to enhance our knowledge by defining the keys steps that will help in performing “Database Testing”

DBMS vs RDBMS

While performing testing of a database, we should be thoroughly clear about the concepts of DBMS and RDBMS. DBMS stands for Data Base Management System and RDBMS stands for Relational Data Base Management System. There are some key differences between the two which are listed below:

Tasks involved in Database Testing:

Database testing is not a single process or an activity, rather it is a bunch of processes put together to complete a mammoth like task of database testing. These processes involve:

1. SQL – Structured Query language
2. Stored Procedures
3. Triggers

Fig. 1: Aspects of Database Testing

1. SQL: SQL or Structured Query Language is a statement or set of statements which fall under either of the categories viz. DML, DDL, DCL and TCL that communicates with database ad perform their specific tasks. Following are the 4 basic categorizations of SQL:

• DML or Data Manipulation Language: DML statements drive users to play with or perform action(s) on data in a database. Most commonly used DML commands are Select, Insert, Delete and Update. Although Select is a DML statement but it is used for fetching the data from database rather than manipulating the data. The other commands are often used to manipulate the data as per the user needs.
• DDL or Data Definition Language: DDL statements define the structure of the database objects which includes creation and modification of database objects. Database Objects referred here mainly involve tables, indexes, views, schema etc. Most commonly used DDL commands are Create, Alter, and Drop.

Fig. 2: Categorization of SQL

• DCL or Data Control Language: DCL statements govern privileges to database users in a database. Most commonly used DCL commands are Grant and Revoke. Grant allows user to create a session and access database objects in database whereas on the other hand Revoke just performs the opposite function.
• TCL or Transaction Control Language: TCL statements are used to manage the transactions occurring in a database. Most commonly used DCL commands are save point, Roll back and Commit.

2. Stored Procedure: Stored procedure is reusable SQL code that can be compiled and saved in your system with a named reference to perform a specific task. So, all you need to do is to execute this code using the named reference anytime to perform the same task rather than executing the SQL queries one by one repeatedly. Below is sample procedure to print “Hello World” Console:

How it works:

• The procedure consists of two parameters: “IN_EMPLOYEE_ID” & “IN_PERCENT”.
• The procedure adjusts the income of an employee specified in “IN_EMPLOYEE_ID” by a given percentage “IN_PERCENT”.
• We are using the UPDATE statement to update the income information in Procedure body.

3. Database Triggers: Trigger is a stored procedure which is fired or executed implicitly whenever a DML (Insert, Delete, and Update) statement is executed on a table. There can be Row level or Statement level triggers which implies that Row level triggers are fired for every single row whenever Insert/Delete/Update operations are performed on each row whereas statement level triggers are fired on execution of complete SQL statement. Please note that triggers can never be executed explicitly by any user of the database.

Fig. 3: Types of Database Triggers

Types of Triggers:

• BEFORE Triggers: These triggers are fired before execution of DML statement on a table (Row or Statement Level).
• AFTER Triggers: These triggers are fired after execution of DML statement on a table (Row or Statement Level)
• INSTEAD OF Triggers: Instead of Triggers are programmed to carry out updates (DML operations) on non-updatable views. Since views are based on multiple base tables, they cannot be updated directly, and throws error whenever a DML statement is fired on any view. This gives stress to implement “Instead of” triggers while executing DML statements on views in a database.

Misconceptions about Database Testing:

There are a lot of misconceptions about database testing but the grass is greener on the other side of the fence. Let’s take a look at the facts over these misconceptions:

1. Database Testing requires an in-depth knowledge about SQL and is an exhaustive job but in reality efficient Database testing provides functional stability to the application and hence the effort applied is quite fruitful.
2. Database testing hampers the pace of overall development process but in reality significant amount of database testing helps in improving the quality for the database application.
3. Database testing could be an expensive affair but it pays off in long term as it provides stability and robustness of the application.

Hope that this blog provides all the key aspects involved in performing Database testing.