In this tutorial, you will get to learn about database testing concepts. The database is a software subsystem that provides an efficient way to store user data and allows requesting it via a structured query language known as SQL. Not only does it stores critical business information but also functions as the backbone of an entire application. Hence, database testing is essential for software testers to ensure the system is working correctly.
Database Testing
Let’s begin this tutorial by explaining the most fundamental concept of database testing. After that, you will know the key areas and use cases which you should focus on for preparing a database test plan. Subsequently, we’ll cover more crucial factors related to this topic.
What is Database testing and why is it important?
Database testing is a means to validate the data stored in the database, objects controlling data, and the functionality wrapped around it. The databases use objects to manage data like tables for storage, views for representation, and functions/triggers for manipulation.
Nowadays, the use of databases is widespread, especially in web applications. And they are getting more and more complex with new technologies and platforms. That’s why checking the quality, security, and correctness of data makes it eminent to learn database testing.
What are the areas to cover while testing a Database?
Testing a database requires checking the following areas are working as expected.
- Database objects which include tables, views, and stored procedures.
- Validation of data being entered and getting stored in the database.
- Make sure the system is honoring the constraints and data changes (insert/delete/update) are reflecting correctly.
- Ensure the system can execute end-to-end database transactions and support concurrency.
- Testing of database migration to ensure compatibility across different versions.
- Verify the performance of database indices, triggers, and procedures.
- Security compliance testing to rule out any unauthorized access or any threats to data.
What are the known database validations?
Testing a database involves the following four validations.
- Data mapping.
- ACID (Atomicity, Consistency, Isolation, Durability) properties validation.
- Data Integrity.
- Business Rule Conformance.
What is data mapping in database testing?
Data mapping is a part of the database testing scope. It focuses on validating the data traversing back and forth from the application to the backend database. A software tester can look at it with the following two aspects.
He can check whether the application’s user interface or the input screen has a one-to-one mapping with the target table in the database. This type of information is usually available in software design documents.
Whenever the user submits a form on the application UI, it triggers a CRUD (Create/Retrieve/Update/Delete) event at the backend. So here the tester should ascertain if the right event gets fired and finished successfully or not.
What is ACID properties validation in database testing?
ACID is an acronym for Atomicity, Consistency, Isolation, and Durability. It refers to confirming the four properties (as mentioned) against each database transaction.
- Atomicity – It means that all Database Transactions are atomic. They can end in one of the two i.e. Success or Failure.
- Consistency – It indicates that the database state will remain valid after the transaction gets completed.
- Isolation – Multiple transactions shall run without impacting each other and won’t hinder the database state.
- Durability – Committing a transaction shall preserve the change and will not lose it due to any power loss or crash later.
What are the steps involved in database testing?
For the successful testing of a database, we need to adopt a structured approach. And a tester should have at least a basic level of understanding of database concepts like SQL commands including DDL (Create/Drop), DML (Insert/Delete/Update), and DCL (Grant/Revoke).
- DDL => Data Definition Language.
- DML => Data Manipulation Language.
- DCL => Data Control Language.
Now, we are laying down the steps to carry out the testing of a database.
Set up testing pre-requisites
First of all, the tester has to create the setup for testing the database. He can do this by cloning a production database or creating a stand-alone database using demo data. He should keep in mind the following points.
The system running the database (Physical or virtual) should have the same hardware characteristics as the production system.
The database software (Oracle/SQL Server/MySQL or any) should have the same version as running on the production system database.
While you are beginning to execute database testing test cases, make sure to run through a sufficient number of SQL queries for practice.
Execute the tests
A software tester can run the tests from the application UI. Or he can write SQL scripts to invoke the CRUD actions. He should monitor the operations, track database changes, and verify whether they are ending with success or not.
Better come with both positive and negative tests covering views, triggers, and stored procedures. Make sure all the functions/blocks are getting tested.
Verify test status
After running the tests, the tester should analyze all failures or tests that get ignored from execution. It’s wise to come up with a summary of all types of cases and then focus on failures and skipped scenarios.
Validate results
In this step, we’ll concentrate if the tests approached the right action after executing the SQL queries or not. Also, he has to ensure the data changes affected the right tables and columns.
Consolidate and Publish Report
The final step is to gather all the results and capture all of them into a report. You need to share this data with all stakeholders. Please make sure that all failures or skipped tests have given proper reasons.
How to write test cases for database testing?
A software tester should prefer writing a separate set of test scenarios and test cases for database testing. That’s how you can make sure your test cases are independent and won’t mix with the UI-related use cases.
Database testing is a type of grey-box testing. And if you don’t know, then note that it’s a mixture of both black box & white box testing methods. So the tester would need to know about the internal functioning of the application and also about the database structure used.
At times, an application could be using multiple databases so you should be aware of how they relate to each other. Also, make a data mapping sheet that should tell you the UI actions and the tables (and columns) getting affected when the action takes place. For example, sometimes it would intend to add a row in one table and update another row in a different table. While writing the test cases, you need to keep an eye on all of these actions to successfully test a database.
And as we’ve said earlier in this post you need good SQL skills for testing databases. It will help you write efficient SQL queries that extract data from a table without returning thousands of rows from the database table.
Database Testing Guidelines
You can use the below guidelines to prepare good test cases for database testing.
- Get clarity on the functional requirements.
- Make a list of all the tables used and find out-
- Joins used between tables
- Cursors used, triggers used
- Stored procedures used
- Input/Output parameters used.
- Create test cases with multiple input data and try to cover all the paths.
Database Testing Checklist
After writing the test cases, refer to the following checklist and see if any information is missing.
- The tests are taking care of all the backend tables used for each requirement.
- If the application/database is using status flags, then tests should verify each of them.
- Tests cover the triggers/stored procedures with combinations of input and expected output parameters.
- Tables might have columns with default values, tests should check them too.
If you are planning for a job change in testing, don’t miss these SQL interview questions and answers for experienced.
What are the possible test scenarios for database testing?
Here, we are listing down three types of scenarios possible for testing a database.
General Test Scenarios
General tests should capture the following scenarios.
- Name of the database.
- Name of the log file.
- Disk space allocation for databases.
- Names of all tables, columns, and their types.
- Null value checks.
- Verify keys (primary/foreign), indexes, and data types of columns used.
Functional Test Scenarios
- Identify events causing the triggers
- Functions inside stored procedures and possible combinations.
- End-to-end data flows, starting right from the front end to the back end.
Non-functional Test Scenarios
- Create test scripts for major features and use them for regression testing at regular intervals.
- Write tests that track errors (OOM/deadlocks/exceptions) in log files.
- Change data in backend tables and watch the effect on the front end.
- Insert invalid values from the backend and observe the effect.
Summary – Database Testing
Great, you have successfully completed this database testing crash course. We believe this but you can confirm whether or not have you achieved the following goals at the end of this tutorial.
- The first goal is that you are now quite aware of what database testing is all about.
- Secondly, you can efficiently manage any challenges that occur during database testing.
- And, the third most important one is you can now devise an effective database testing strategy.
If any of you have any queries or questions, feel free to contact us. We’ll try to respond within the next business day.
Best,
TechBeamers
