Database Testing

Before we start the tutorial on database testing, let’s take an example of an e-commerce website. We will see how the database is used at almost every level of the user’s shopping experience-

1. User logs in to the system.
2. The user searches for a product and adds the product to his cart.
3. He sees the calculated price (which might include taxes, promo code, etc.).
4. He proceeds to buy the product and enters credit card information.
5. After placing an order, the user can track the delivery status.

In the above-mentioned process, every step deals with the database. User’s login information, profile information, product details, price calculation, payment information and so on.

Nowadays, any web or mobile applications use relational database management systems such as MySQL, Oracle Database, Microsoft SQL Server. As we can see in the example, web applications build dynamic objects which provide information to the users in real-time as per the user’s requirements.

For step 3, in order to show the user, the correct information, first, the information is stored in the database i.e. the price of the selected product. Then, when the user views the final pricing calculation, various prices are extracted from the database. Such as base price of the product, taxes, promo code deduction if it has been applied, etc. This extracted information is then displayed to the user in a form that he can understand.

As you can see, a single step of the process includes frequent access to the database, and it is very likely that an error might occur. To find out such errors, database testing is required. Let’s study it in detail now.

What is Database Testing?

Database testing is the testing of schema, stored procedures, tables, triggers, etc. of the database. It also includes testing of data integrity, data validity, and performance testing.

Importance of Database Testing

There are many crucial reasons why database testing is required, some of them are explained below-

• Data Mapping – Database testing makes sure that the UI fields on the front end are mapped correctly with the fields in the database tables. Also, if any action is performed on the front end, a CRUD (Create, Read, Update and Delete) action is triggered in the database and the testers need to check whether the correct action is performed, and the respective tables are updated or not. 
  
  
• Database ACID Properties Validation – ACID stands for Atomicity, Consistency, Isolation, and Durability. These are a standard set of properties that make sure that the database is reliable after actions are performed.
  
  
• Data Integrity – Any operation on a database will be one of the CRUD (Create, Read, Update and Delete) operations. Database testing after any CRUD operation confirms that data is consistent and correct. For example, if a user deletes any record on the front end, at the back end, in the database ‘Delete’ action should be performed.
  
  
• Implementation of Business Rules – database has many features that are useful to developers in implementing business logic at the database level. These features are stored procedures, triggers, relational constraints, etc. The tester should perform database testing to check whether correct business rules have been implemented or not.
  
  
  

Types of Database testing

There are mainly three types of database testing – Structural Testing, Functional Testing, and Non-functional Testing. Let’s look at them one by one.

1. Structural Testing

In structural testing, database components such as tables and columns, stored procedures, views, triggers, schema are tested. These components are not exposed to the end-user.

For this type of testing, testers should have complete knowledge of database concepts. Let’s look at some components involved in structural testing.

• Schema/Mapping Testing: Schema or mapping testing is carried out to check whether correct schema mapping on the front and back end is done or not. Testers need to make sure that every field on the front end has a corresponding correct field at the back end mapped to it. Testers are also responsible for finding unmapped objects.
  
  
• Database Table and Column Testing: In this testing, validation of data types of the fields, length of the fields, naming conventions of tables and columns in the database is done. Moreover, this testing verifies that keys and indexes are defined correctly and, primary keys and foreign keys are mapped correctly. 
  
  
• Stored Procedure Testing: In this testing, testing of stored procedures is carried out. Naming and coding conventions of stored procedures are checked. Testers make sure developers have covered all the conditions in the coding of stored procedures and all the exceptions are handled. It is also checked whether the execution of the stored procedure gives the correct result.
  
  
• Trigger Testing: In this testing, triggers’ functionalities are checked i.e. whether details are updated successfully after trigger execution. Naming and coding conventions are also checked.
  
  
• Database Server Testing: In this testing, database server configuration details are checked. Also, it is tested whether the database can handle the desired number of user transactions.
   
  

2. Functional Testing

Functional testing in databases is done from the end user’s point of view. In this type of testing, it is made sure that the transactions and operations performed by the users are as per the system requirements.

This type of testing can be categorized further into-

• Black Box Testing – In this type of testing, interfaces, and database integration is tested. It includes testing the mapping of data and, incoming and outgoing data. This method uses various techniques such as equivalence partitioning, boundary value analysis, and cause-effect graphing.
  
  
• White Box Testing – In this type of testing, the internal structure of the database is tested. It includes testing of triggers, views, tables, columns, schema, and so on. This method uses various techniques such as cyclomatic complexity, condition coverage, decision coverage, and statement coverage. Here, these internal specification details are hidden from the user.
  
  

3. Non-functional Testing

Non-functional testing is done to check non-functional parameters of the database such as performance (load and stress handling), recovery, security, etc.

• Load Testing: In load testing, the system’s ability to manage the load of multiple transactions happening at the same time, is checked. Testers check the response time of the database system during this testing. 
  
  
• Stress Testing: In stress testing, the system’s robustness is checked. The system is loaded with multiple users and transactions until the database system’s breakpoint is reached or the system is failed. 
  
  
  

Conclusion

Database testing is required by all the complex applications of today as most of them deal with extensive data. It demands superior knowledge of databases and it is highly recommended that experienced testers are used to perform this testing as it is an extremely important type of testing. 

We hope this tutorial was helpful in providing an understating of database testing.