Enterprise applications are complex and have multiple layers, including user interface, business or application layer, data access layer, and a database layer. For any application to perform as expected, all these layers need to work together consistently and accurately. To ensure accuracy they should be periodically tested for issues.
In this post, we will focus on testing of a key layer — the database. Database testing is very crucial to the entire application testing process. Let’s get started by understanding the basics of database testing.
What is database testing?
Database testing is essentially a 360-degree view of the processes that have a direct or indirect impact on the database, such as mapping of forms fields with corresponding tables, or those on the backend, including updates due to a specific action on the front end.
Database testing- is it really that crucial?
In short- absolutely! Almost all applications have a data access layer in which the code is written to manipulate and perform actions on the database. Most applications are also subject to constant change. For instance, business processes are re-engineered, authorization rules are changed, components are replaced by other more powerful components, or optimizations are added to achieve better performance for a growing number of users and data. The more complex an application becomes, the more frequently its configuration may need to be tweaked.
Database testing involves validation of values or data retrieved by an application from the database to ensure the data shown on the user interface matches the data present in the database or vice versa. This testing is at the data access layer–the layer through which the application communicates with the database. It involves testing application database integrity testing and its data stores, such as stored procedures, views, and tables.
Components of database testing
Generally, database testing involves a number of variables, because it touches both the data and processes. These layers make a direct or indirect impact on the data. Database testing includes the following:
- Data accuracy and validity (field size validation)
- Data integrity (check constraints, insert, delete and update)
- Database objects (stored procedures, views and tables)
- Data migration (import and export)
- Data transaction consistency and concurrency (locks)
- Database performance indices, number of triggers and procedures)
- Database security – data access (unauthorized access)
Database testing process: What to test and verify
For comprehensive database testing, the testing team should ensure the following areas are tested and verified:
- Verification of data mapping. In most applications, the data travels both ways between the user interface or front end, and the database. Hence, it is imperative that data verification is carried out by mapping the data entered from the front end with the corresponding tables in the database. When any action, such as submitting a form or entering details, is performed from the front end, the respective tables also should be updated. A tester needs to ensure the respective tables are updated when the action is performed, and correct values are returned in the back end after the associated processes are completed.
- Verification of data integrity. Testing data integrity involves verification of constraints as well as CRUD operations (Create, Retrieve, Update, and Delete). Data integrity should be ensured in all cases of CRUD functions irrespective of the device being used and pages being viewed. The verification process assures consistency and accuracy of data throughout its lifecycle, including accuracy at the time of storage, retrieval and update.
- Verification of ACID properties. ACID properties refer to the Atomicity, Consistency, Isolation and Durability of the database. In the case of relational database design, various tables are dependent and related to each other resulting in a single operation affecting multiple related or dependent tables. Hence, the verification of ACID properties plays an important role to ensure the data remains consistent from creation to processing and deletion.
Data integrity is critical for timely business decisions. Therefore accuracy is imperative at every stage of its processing. Database testing analyzes the application requirements to identify the details of actions performed on any data element after it’s created either by a user input or through another process. This is a key component of the overall testing process and if ignored or not done properly, can cost the organization dearly.
Emtec’s database testing experts work with our global clients on a daily basis to ensure data consistency and integrity across their applications. If you have database related challenges, or need additional database testing support, please contact us.