Database (DB)Testing - Strategy, Concepts, Checklist with sample test cases

In software testing there will be so many types of testings we will be carried out to deliver the product as per the client needs or as per the requirements. In which database testing or db testing is the one which requires thorough knowledge on sql commands, stored procedures, functions and triggers. There will be so many database software available in the market like SQL Server, Oracle, MySQL, PostgreSQL, Hadoop (Hadoop distributed file system) etc., What ever may be the database software we are using there will be slight difference in SQL query syntax, however most of the things will be same in all software. Database testing or DB testing is a technique of validating the database in which data is getting inserted/updated/deleted in various tables on various databases. Database testing is will differ based on the type of the data you are going to verify. I mean based on the OLTP or OLAP (Online Analytical Processing) the method of database testing will differ. For OLTP (Online Transaction Processing) we are verifying the stored procedures and validating the data is inserted/deleted/modified properly or not. And for OLAP lot of scripts or DW jobs will comes into picture. Having extensive knowledge on database testing I can say database testing should be carried out manually, why because I didn't find any automation testing tool which fulfills the db tsting requirements. Whenever the project started and ready for testing initially we will get the packages, stored procedures, functions etc for QA. In the sense the Database testing concept is divided into two phases.

1. Backend Database Testing
2. Frontend Database Testing

Database Backend Testing Strategy:

Generally the data gets inserted in databases by calling simple sql statements in simple database design, however for major projects you will be having PL/SQL or MySQL stored procedures. Initially these database objects will be ready for testing before the front end changes gets ready. You have to carry out database back-end testing either calling the procedures directly on the host by execute command or you can use any database tools available in the market. For oracle procedures I feel pl/sql developer is the best database tool to test the procedures, functions. Simply login to the database using pl/sql developer and go the concerned package and through procedure and right click and test the procedure which will show all the in and out parameters. Enter the input parameters and click on run command which will retrieve the data from database and display in a tabular format. By that way you can test with varieties of input data and verify the out parameter result. Your testing should include the negative testing also like you are giving some input parameters which is not exists in the table, then it should throw the concerned errors. So you need to check proper and valid database error messages are getting displayed or not. So most of the bugs will be fixed in this back-end database testing.

Checklist for Database Backend Testing:

Before starting this Database back end testing you need to cover the following checklist with respect to database design document.

1. Check that all the tables gets created/modified properly
2. Check no of columns, column descriptions, their data type, size etc.
3. Check primary key and foreign keys are created
4. Check the indexes are created
5. Check for views creation

Strictly saying we are not going to do any database testing on indexes, just we will check the database indexes are created as per the database design document or not in data dictionary tables. These are responsible for increase the performance while querying against the database.

Once you are done with the database testing finally you have to check whether the data is replicated to various other distributed databases without any duplicates or data mismatch or data missing if needed.

Frontend Database Testing Strategy:

Don’t be confuse with the word frontend, we are not doing any frontend UI testing, however we are passing the values from frontend, so many values whatever entering by the user or some application or servlet should call the procedures itself. So we need to check the data integrity and data validity  here. For example if you take any web site that will contains the registration form where users create/update/delete or cancel their accounts. By doing these operations data will be inserted modified and deleted etc.

Checklist for Database Testing or DB Testing:

1. Check the database whether all the data records are inserted properly into various tables on various databases depending upon the design.

2. Verify the sequence, suppose you are creating couple of accounts the number generated should be unique for each account.

3. Validate the constraints:

a. Validate for primary key and foreign key constraint data, for example if we are having one number which corresponds to one account should have only one record(primary key) in one table and should have multiple records (foreign) in other database tables. Check that without primary key there won’t be any foreign keys.

b. Validate the CHECK constraint - in the sense it should allow the values based on some condition like the value should be A/I/P/D/T something which stands Active/Inactive or Intermediate/Pending or Processing/Deleted/Terminated etc., or some values like the salary should be more than 500 etc....as per the DB design.

C. Validate the default constraint - if the value is not passing from front-end or servlet then the default values should be stored in database.

d. Validate the NOT NULL constraint - this field value should not contain any NULL values.

e. Validate the UNIQUE Constraint - the values should be unique only one NULL value can be acceptable.

4. Verify the all database fields in all the tables as many values come from the servlets or front-end application. So you need to check those are valid data or not.

5. You will be having so many database lookup tables in which the data won’t change frequently, but the values getting inserted into other database tables are based on these lookup table data.

Once the front end database testing is done, finally come up with some test cases from both back end database testing and front end database testing to carryout end to end testing. 

ETL Testing Data Migration Testing