Database Testing – Practical Tips and Insight on How to Test Database

Database is one of the inevitable parts of a software application these days. 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 backend. Similarly, whether it is healthcare of finance, leasing or retail, mailing application or controlling spaceship, behind the scene a database is always in action.

Moreover, as the complexity of application increases the need of stronger and secure database emerges. In the same way, for the applications with high frequency of transactions (e.g. banking or finance application), necessity of fully featured DB Tool is coupled.

Currently, several database tools are available in the market e.g. MS-Access2010, MS SQL Server 2008 r2, Oracle 10g, Oracle Financial, 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 digging into the topic, let me comprehend the foreword. When the application is under execution, the end user mainly utilizes the ‘CRUD’ operations facilitated by the DB Tool.

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 of DB is performed.
D: Delete – when user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.

It does not matter at all, which DB is used and how the operation is preformed. End user has no concern if any join or sub-query, trigger or stored-procedure, query or function was used to do what he wanted. But, the interesting thing is that all DB operations performed by user, from UI of any application, is one of the above four, acronym as CRUD.

Database Testing

As a database tester one should be focusing on following DB testing activities:

What to test in database testing:

1) Ensure data mapping:

Make sure that the mapping between different forms or screens of AUT and the Relations of its DB is not only accurate but is also according to design documents. For all CRUD operations, verify that respective tables and records are updated when user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.

2) Ensure 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.

3) Ensure Data Integrity:

Consider that different modules (i.e. screens or forms) of application use the same data in different ways and perform all the CRUD operations on the data. In that case, 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.

------------

4) Ensure Accuracy of implemented Business Rules:

Today, databases are not meant only to store the records. In fact, DBs have been evolved into extremely powerful tools that provide ample 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. Tester must ensure that the implemented business logic is correct and works accurately.

Above points describe the four most important ‘What Tos’ of database testing. Now, I will put some light on ‘How Tos’ of DB Testing. But, first of all I feel it better to explicitly mention an important point. 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:

1. Create your own Queries

In order to test the DB properly and accurately, first of all a tester should have very good knowledge of SQL and specially DML (Data Manipulation Language) statements. Secondly, the tester should acquire good understanding of internal DB structure of AUT. If these two pre-requisites are fulfilled, then the tester is ready to test DB with complete confidence. (S)He will perform any CRUD operation from the UI of application, and 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 or impossible 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 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.

2. Observe data table by table

If the tester is not good in SQL, then he or she may verify the result of CRUD operation, performed using GUI of the application, by viewing the tables (relations) of DB. Yet, this way may be a bit tedious and cumbersome 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 AUT.

3. Get query from developer

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. It requires neither good knowledge of SQL nor good knowledge of application’s DB structure.

So, this method seems easy and good choice for testing DB. 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.

 

Conclusion:

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 AUT and proper training.

In order to have the confident test report of this activity, this task should be assigned to a resource with all the four qualities stated above. Otherwise, shipment time surprises, bugs identification by the client, improper or unintended application’s behavior or even wrong outputs of business critical tasks are more likely to be observed. Get this task done by most suitable resources and pay it the well-deserved attention.

 

This is a guest post by Rizwan Jafri, a Software Testing expert. If you want to post your article on this blog, check out the guidelines here.

If you have queries related to database testing, database testing interview questions, database testing tools or concepts please put it below. We will be happy to address all the queries in comments or part II of database testing article.

Recommended reading

68 comments ↓

#1 K Saren

I was waiting for this since long. thanks for posting. I also want to learn SQL queries. please also add one more article with all SQL queries testers should know at the minimum.

#2 Pratap Gusain

nice article and very useful…

#3 ram

very good article…very helpful…keep up the good work guys….and keep us posted on interesting topics…

#4 BHANU

Nice Article and very useful

#5 rifazy

very useful artilce …really helpfull

#6 MOhsina

Hi I have 1 que related to SQL query please if possible reply.
We have 3 tables name- Student,Marks,subject.
Students Marks Subjects
A 45 Maths
B 100 Science
C 85 Phy

Now want to select student name who have maximum marks in Maths.
what should be the query for that?

#7 Varsha

Nice Article

#8 Mrs. H

Great article! I found this article to be extremely beneficial. I’m sure I’ll refer to it often as I work to improve my SQL skills. Can’t wait for part 2.

#9 Farhana Ashraf

Very useful!

#10 Naeem

A very good and informative article.I would like to add two more things
1.Some times it is very helpful for a tester to observe the trace of the application specially the trace of an application developed in JAVA platform can easily be view after a single transaction.
2.The tester must be very keen in learning about the impact of a single transaction e.g. how many tables will be effected and why?

Please correct me if there is any thing missing

#11 Jitendra

Can anybody elaborate the ETL part of Database testing?

#12 Viplav Anand

Nice article dude.

#13 Rizwan Jafri - The Author

First of all Thank you so much guys for all your interest and encouragement. I will keep sharing my knowledge with you on this forum.

@Naeem: I appreciate your positive critic which reflects you have keenly studied this article. I think that first 3 points of “What to test in database testing:” section address both the issues you have mentioned in the comments.

@Mohsina: I will be extremely glad to give you the exact query you need once you provide the details of table structures (i.e. their attributes and Primary-Foreign Key relationships). However, at the moment there is a hint for you.

Subject Id (from ‘Subject’ table) should be referred as FK in both tables ‘Student’ and ‘Marks’. So, First join ‘Marks’ and ‘Subject’ table and get the highest marks in math. Then find the Student Id against the resultant record.

Once again, thank you all for your feedback.

Best Regards.

#14 Vijay

To Author,
Your way of delivery is so crispy and easily implementable. I would suggest you to write articles on Selenium IDE, RC, GRID With Automation framework setting.

#15 neha

very good article

#16 Deena

This is a nice and aidful article.
Would’ve been more helpful if provided with some tables and SQL editor to implement the learned concepts for better understandability of the reader.

#17 Gaurav Khanna

Nice Article. DB Testing is really very important.

#18 Srinivas

@MOhsina

I think it will work for your question… Try to use subquery..
and try this query…
SQL> select students from stud where marks=(select max(marks) from stud where su
bjects=’Maths’);

#19 Srinivas

SQL> select students from stud where marks=(select max(marks) from stud where subjects=’Maths’);

#20 Deepesh Agrawal

Select student name who have maximum marks in Maths.

Student
Stu_ID Stu_Name
1 Ram
2 Shyam
3 Mohan

Subject
Sub_ID Sub_Name
10 Maths
11 Science
12 Physics

Marks
Stu_ID Sub_ID Sub_Marks
1 10 24
1 20 19
1 30 34
2 10 22
2 20 19
2 30 45
3 10 39
3 20 31
3 30 13

select student.stu_name from student where student.stu_id=(select marks.stu_id from marks where marks.sub_marks=(select max(marks.sub_marks) from marks join subject on marks.sub_id=subject.sub_id where marks.sub_id=(select subject.sub_id from subject where subject.sub_name=’Maths’)));

#21 gaurav gupta

very useful …really helpfull and easily understandable article…

#22 Rishi

The article is perfect to explain database testing..

#23 aruna

resolved many confusions. Thanks

#24 sri

Can u please explain how to test SSIS packages.And what are the activities of ETL testers?

#25 sreenivas

hi,

Database testing articles should give with examples.

Example:
1. how to retrieve passwords from table:

SELECT emailid,password
FROM XYZ_Company
WHERE emailid = ‘xyz@xyz.com’_;

This is one technique to get passwords from tables.

If possible provide same way provide better examples for all our users.

#26 JOHN

Select student name who have maximum marks in Maths.

select * from table_name where marks in (select max(marks) from table_name where Subject = ‘Maths’)

#27 saritha

hi,
i need some more clear information on Database testing,like examples of testcases,how it is in real time environment

#28 vikas

hi rizwan,

recently i had an interview where in i was asked as to how do you test an entire cycle of “email registration process” that involves sending a confirmation id to mobile phone. If this whole process is to be automated that is filling application -> submitting -> open thus activated web page -> feed with confirmation id from mobile phone -> submitting -> verification of username and password to be activated… how do we automate it? is it possible to do at all as it may involve third party service provider?

#29 vinoth kannn

tooo usefull one…

#30 Megha

Very usefull article…..

#31 gurpreet

very informative article.. but please post some article on database testing test case. how to write them.

#32 suchismita

I need interview qstions on database testing.plz help me out

#33 Atul Kumar Sinha

Good Article for fresher level

#34 Mario

Well, this only takes into account consolidated databases. ACID does not applyto distributed DBs and the passing remark that “This area demands more rigorous, thorough and keen testing when the database is distributed” is not correct. In the case of distrib DBs, CAP should be tested, not ACID

#35 Balachandar.M

How to compare the graphs,charts and reports present in two different data base.

#36 Syam

Great article.

I have a small doubt,

How much SQL knowledge need to be a DB Tester?

Who should write complex SQL Queries?

#37 techlib

Very good article! This was useful for testing my my college project database

#38 Mayasen

@ Syam

Basic query knowledge is enough i.e. the level to verify the inserted data, table structure, query view, etc. in the DB. For complex queries you can get assistance from the developers.

#39 sachin

Nice article

#40 Deepika

any tools are used for database testing?, plz let me know………

#41 Rahul

What is the difference between database testing and backend testing, ellaborate with example?

#42 Raghavender

Very Very useful article. Thanks a lot.

#43 Krishna Kishore

Article is very good and very Useful

#44 chandru

I had a doubt, for DBA testing we need to write quries from that want to test or just need to test the back end.

#45 Varsha

Hi,
I want to know how to start with Database Testing…Is thr any tool or if we have to do manually then how?
I am using MySql as a back end and i have one product as a project…then how to do testing..?

#46 satz

SELECT MAX(column_name) FROM table_name

#47 ROUSHAN

anyone can tell me the diff b\n inner,outer and self join in sql.

#48 ROUSHAN (yo jo)

select name, phone, selling from people left join property
on people.pid (+) = property.pid;

is this write…?

#49 Sowmya

Nicely written article !

#50 Shagun thakur

Hi,
I want to know ,How create data base testing report.

#51 Garry

That’s perfect man.

Very informative and clear .
U can be a good technical author.

Thanks again :)

#52 volga

I love the post i am becoming professional tester by reading this post fantastic

#53 Varshita

This is very informative article.
Could you please explain on how to prepare test cases, scenarios and test report for DB Testing

#54 sushmitha

I want to know about the database automation testing tools either open source or commercial tools.

#55 Ashish

Thanks Vijay for this valuable article.
I am a regular reader and also have bought a book.
Can you provide me any document which you have for the Quality Center not ALM (With Practical example) on my mail id.
I have already mail to you for the same.
Please provide me because there is no book available in market.
Thanks,
Ashish Kulkarni
ashish.kulkarni2@wns.com

#56 xyz

Hi,

Nice article.

xyz

#57 Himanshu Tyagi

What is Alternate Flow. Can u explain me about it

#58 Jayachandiran

What you mean AUT?

#59 Jayachandiran

Whether it is Application Under Test?

Anybody knows pls reply immediately.

#60 Tejeshwini

It was very good info.

Thank you :)

#61 clang

Nice article very useful information

#62 Sonu Rathore

Nice one

#63 geetha

can u give more information on this.

#64 Geetha

This is simple and superb! A great intro to what SQL testing is all about and what the prerequisites of it are. Thank you very much!

#65 Thoko

very useful

#66 Rizwan

Nice Article, Thanks for Sharing Tips.

#67 mohit

How we can test user data and transaction security in payment gateway?

#68 Namita Devadiga

Select Studentname from student where marks=(Select max(marks)from marks where subject =(select subject from subject where subject=’maths’);

Leave a Comment