Trying to figure out the Importance of database testing?
This guide will help you.
It is an important part of software testing because as it ensures whether the data values and information we receive and store into the database are valid or not.
Database testing helps to save data loss, saves aborted transaction data, and no unauthorized access to the information.
In this context, we shall look into what database testing is and what is the importance of Database testing.
What is the importance of Database testing?
Before going into the importance of database testing we will take a glimpse of what database testing is.
What is Database testing?
The database is a collection of data that is organized well enough to access, manage and update.
Database testing is a process of validating data, checking integrity and consistency of data, checking performance issues caused by the database, testing stored procedures, triggers, functions, and views.
Basics for testing a Database
The following are the basic requirements to do database testing:
1. Basic SQL queries
2. Database referential integrity and different constraint
3. Idea about the table structure and design
How to prepare test cases for database testing ?
1. Firstly we need to understand functional requirements
2. And then find out the back-end tables, joins used between the tables, cursors used, triggers used, stored procedures used, input parameter used, and output parameters used for developing that requirement.
3. Finally, after clearly understanding all the above, we can start writing test cases with different input values for checking all the possible paths of stored procedures.
Different types of database testing
Following are the two main types of testing:
1. White box testing
2. Black box testing
White box testing
In white box testing, we will be testing the structure of the database code.
This includes testing stored procedures, functions, triggers, views, referential integrity, data consistency, and the quality of existing data.
Black box testing
In black box testing, we will be executing SQL queries manually in the database and check for the data values being retrieved, data values being persisted, and stored procedures/functions.
Stored Procedures Testing
Following are the important tests to check stored procedures:
1. How the development team adopts the requirement for coding standard conventions along with exception and error handling
2. Then we will check whether the development team covers all the conditions/loops by applying the required input data to the application under test.
3. Has the development team applied TRIM operation whenever data is fetched from the required tables in the database?
4. Does manual execution of the stored procedure provide the end-users with the result they expect.
5. After that we need to check whether the manual execution of the stored procedure ensures updating the table fields as the application requires under test.
6. Validating the presence of any unused stored procedures.
7. Finally, we must validate the overall integration of the stored procedure modules as per the requirements of the application under test.
Following are the important tests to check while doing Trigger Testing:
1. Firstly we need to follow coding conventions during the coding phase of the Triggers.
2. Secondly, check whether the triggers executed for the respective DML transactions have fulfilled the required conditions.
3. Then check whether the trigger updates the data correctly once they have been executed.
4. Finally, validating the required Update/Insert/Delete triggers functionality.
Functional Database Testing
Functional Database Testing is a type of database testing that validates the functional requirements of a database from the end-user’s perspective.
Following are the basic conditions to check:
1. Firstly, check whether the field is mandatory while allowing NULL values on that field.
2. Secondly check whether the length of each field is of sufficient size.
3. After that, check if all similar fields have the same names across tables.
4. Finally check whether there are any computed fields present in the Database.
Checking data integrity and consistency
Following checks are important:
1. Firstly, check if the data is logically well organized.
2. Then check if the data stored in the tables are correct and as per the business requirements.
3. After that check for any unnecessary data present in the application under test.
4. Check whether the data has been stored as per as the requirement with respect to data that has been updated from the user interface.
5. Then check whether the TRIM operations performed on the data before inserting data into the database under test.
6. Check whether the transactions have been performed according to the business requirement specifications and whether the results are correct or not.
7. Next check whether the data has been properly committed if the transaction has been successfully executed?
Problems in database testing
Following are some of the problems:
1. Tester should have a good understanding of SQL queries and well aware of changes in database after executing queries
2. Should check the changes in the database after executing tests
3. Inserting and deleting the rows used for testing without affecting the actual database
Best practices when performing database testing
Following are some of the best practices for database testing:
1. We need to validate all data including the metadata as well as the functional data according to their mapping by the requirement specification documents.
2. Need to validate the test data which we create with the help of the development team.
3. Validating the output data by using both manual as well as automation procedures.
4. The validation rules of referential integrity for the required database tables also need to be validated.
5. Selecting default table values for validation on database consistency.
6. Checking whether the scheduled jobs execute in a timely manner.
7. Taking timely backup of Database.