What & How to test a Database – The Basics

Today I’ll discuss about some basic techniques of the database testing. A Test Engineer needs to test DB on the testing phase of software. Database is the most important part of any software. It is impossible for us to develop software without Database. It does not matter at all whether it is web or desktop, client server or peer to peer, enterprise or individual business, database is working at back-end.

Currently, several database tools are available in the market like MS-Access, MS SQL Server, Oracle, MySQL, PostgreSQL, DB2 etc.  All of these vary in cost, robustness, features and security. Each of these DBs possesses its own benefits and drawbacks. One thing is certain; a business application must be built using one of these or other DB Tools.

Before I start the What & How topic, I would like to share some common characteristics of Database. The end user mainly operates the CRUD (Create, Retrieve, Update, and Delete) operations when the application is under execution. Every Test Engineer needs to know at least the basic about the CRUD operations. These are:

C: Create – When user ‘Save’ any new transaction, ‘Create’ operation is performed.
R: Retrieve – When user ‘Search’ or ‘View’ any saved transaction, ‘Retrieve’ operation is performed.

U: Update – when user ‘Edit’ or ‘Modify’ an existing record, the ‘Update’ operation is performed.
D: Delete – when user ‘Remove’ any record from the system, ‘Delete’ operation is performed.

It does not matter at all, which DB is used and how the operation is performed. End users performed lot of DB operations. But the end users are not concerned about any join or sub-query, trigger or stored-procedure, query or function was used to do what they wanted.

 

What we need to test in database testing:

We need to ensure some DB features/functions  for Database testing like Data Mapping, ACID test, Data Integrity, Implementation of Business role etc.

Ensure data mapping

We need to make sure that the mapping between different forms/screens of the application and the relations of its DB is not only accurate but is also according to design documents. We need to verify that respective tables and records are updated when user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.

 

Ensure the ACID Properties of Transactions

ACID properties of DB Transactions refer to the ‘Atomicity’, ‘Consistency’, ‘Isolation’ and ‘Durability’. Proper testing of these four properties must be done during the DB testing activity. This area demands more rigorous, thorough and keen testing when the database is distributed.

Ensure Data Integrity

We need to consider that different modules of application use the same data in different ways and perform all the CRUD operations on the data. In that case, we need to make it sure that the latest state of data is reflected everywhere. System must show the updated and most recent values or the status of such shared data on all the forms and screens. This is called the Data Integrity.

Ensure Accuracy of implemented Business Rules:

Databases have been evolved into extremely powerful tools that provide sufficient support to the developers in order to implement the business logic at DB level. Some simple examples of powerful features of DBs are referential integrity, relational constrains, triggers and stored procedures. So, using these and many other features offered by DBs, developers implement the business logic on DB level. Test Engineers must ensure that the implemented business logic is correct and works accurately. They may execute the stored procedures, views to compare the result with real business.

Above points describe the four most important ‘What To’ of database testing. Now, I will briefly describe about ‘How To’ of DB Testing. But, first of all I like to strongly say that DB Testing is a business critical task, and it should never be assigned to a fresh or inexperienced resource without proper training.

 

How to Test Database:

We can perform the Database testing by the following below listed ways as a Software Test Engineer:

Create own Queries

First of all Test Engineer should have very good knowledge of SQL and specially DML statements in order to test the DB properly and accurately. Secondly, the tester should acquire good understanding of internal DB structure of application. If these two pre-requisites are fulfilled, then the Test Engineers are ready to test DB with complete confidence.  

We will perform any CRUD operation from the UI of application and we will verify the result using SQL query. This is the best and robust way of DB testing especially for applications with small to medium level of complexity. Yet, the two pre-requisites described are necessary. Otherwise, this way of DB testing cannot be adopted by the tester.

Moreover, if the application is very complex then it may be hard for the tester to write all of the needed SQL queries himself or herself. However, for some complex queries, tester may get help from the developer (or DB Specialist) too. I always recommend this method for the testers because it does not only give them the confidence on the testing they have performed but, also enhance their SQL skill.

Observe data table by table

The Test Engineers can verify the CRUD operation result by viewing the tables (relations) of DB, if they are not good at SQL. This way may be annoying and bulky (especially when the DB and tables have large amount of data).

Similarly, this way of DB testing may be extremely difficult for tester if the data to be verified belongs to multiple tables. This way of DB testing also requires at least good knowledge of Table structure of the application database.

Get query from developer/DB Specialist

Test engineer may collect the query from the developer or DB specialist.  This is the simplest way for the tester to test the DB. Perform any CRUD operation from GUI and verify its impacts by executing the respective SQL query obtained from the developer.

Tester will get knowledge about SQL by using this method. But, its drawback is havoc. What if the query given by the developer is semantically wrong or does not fulfill the user’s requirement correctly? In this situation, the client will report the issue and will demand its fix as the best case. While, the worst case is that client may refuse to accept the application.

Database is the core and critical part of almost every software application. So DB testing of an application demands keen attention, good SQL skills, proper knowledge of DB structure of application and proper training.