_edited_edited.png)
Introduction:
Database Testing is a type of software testing that checks the schema, tables, triggers, etc. of the Database under test. It also checks data integrity and consistency. It may involve creating complex queries to load/stress test the Database and check its responsiveness.
Database testing includes performing data validity, data integrity testing, performance check related to database and testing of procedures, triggers and functions in the database.
Example
Consider an application that captures the day-to-day transaction details for users and stores the details in the database. From database testing point of view, the following checks should be performed −
The transactional information from the application should be stored in the database and it should provide correct information to the user.
Information should not be lost when it is loaded to database.
Only completed transactions should be stored and all incomplete operations should be aborted by the application.
Access authorization to database should be maintained. No unapproved or unauthorized access to user information should be provided.
Why Database Testing is Important?
Database Testing is important in software testing because it ensures data values and information received and stored into database are valid or not. Database testing helps to save data loss, saves aborted transaction data and no unauthorized access to the information. Database is important for any software application hence testers must have good knowledge of SQL for database testing.
The GUI is usually given the most emphasis by the test and development team members since the Graphical User Interface happens to be the most visible part of the application. However, what is also important is to validate the information that is the heart of the application, aka DATABASE.
Let us consider a Banking application wherein a user makes transactions. Now from Database Testing or DB Testing viewpoint following, things are important:
The application stores the transaction information in the application database and displays them correctly to the user.
No information is lost in the process.
No partially performed or aborted operation information is saved by the application.
No unauthorized individual is allowed to access the user’s information.
To ensure all these above objectives, we need to use data validation or data testing.
Database Testing – Types
Based on the function and structure of a database, DB testing can be categorized into three categories −
Structural Database Testing − It deals with table and column testing, schema testing, stored procedures and views testing, checking triggers, etc.
Functional Testing − It involves checking functionality of database from user point of view. Most common type of Functional testing are White box and black box testing.
Nonfunctional Testing − It involves load-testing, risk testing in database, stress testing, minimum system requirements, and deals with the performance of the database
Database Testing – Processes
The process to perform database testing is similar to testing of other applications. DB testing can be described with key processes given below.
Set up the environment
Run a test
Check the test result
Validate according to the expected results
Report the findings to the respective stakeholders
Various SQL statements are used to develop the Test cases. The most common SQL statement, which is used to perform DB testing, is the Select statement. Apart from this, various DDL, DML, DCL statements can also be used.
Example − Create, Insert, Select, Update, etc.
Database Testing Stages
DB testing is not a tedious process and includes various stages in database testing lifecycle in accordance with the test processes.
The key stages in database testing are −
Checking the initial state
Test run
Outcome validation as per expected result
Generating the results
First stage in DB Testing is to check the initial state of the database before starting the testing process. Then database behavior is tested for defined test cases. In accordance with the results obtained, test cases are customized.
For successful database testing, the workflow given below is executed by every single test.
Cleaning up the database − If there is testable data in the database, it should be emptied.
Set up Fixture − This involves entering the data into the database and check the current state of the database.
Perform test, verify results and generate results − The Test is run and the output is verified. If the output is as per expected results, the next step is to generate the results as per requirement. Otherwise, testing is repeated to find the bugs in database
Database Testing – Tools
There are various tools provided by vendors that can be used to generate Test data, to manage Test data and perform database testing like Load Testing and Regression Testing.
A few common tools that are used are given below.
Load Testing Tools
These tools are used to put high usage loads on your database, which enables to determine whether
your system's landscape will stand up to your business needs.
Examples: Web Performance, Rad View, Mercury
Data Security Tools
These tools are used to implement compliance and standards as per the information security
regulations
Example: IBM Optim Data Privacy
Test Data generator tools
A tester uses these tools to generate the test data for a database system. These are mostly required
when you have huge amount of data and you need sample to perform DB Testing. It is commonly used
for Load and Stress testing.
Example: Data Factory, DTM Data Generator, Turbo Data
Test Data Management Tool
These tools are used to maintain version control for test data. You have to define the expected
results and then you compare it with the actual outcomes of the tests.
Examples: IBM Optim Test Data Management
Tools to perform Unit Testing
These tools are used to perform regression testing on your database.
Example: SQLUnit, TSQLUnit, DBFit, DBUnit
Conclusion:
Best Practices
All data including the metadata as well as the functional data needs to be validated according to their mapping by the requirement specification documents.
Verification of the test data which has been created by / in consultation with the development team needs to be validated.
Validation of the output data by using both manual as well as automation procedures.
Deployment of various techniques such as the cause effect graphing technique, equivalence partitioning technique and boundary-value analysis technique for generation of required test data conditions.
The validation rules of referential integrity for the required database tables also need to be validated.
The selection of default table values for validation on database consistency is a very important concept Whether the log events have been successfully added in the Database for all required login events
Does scheduled jobs execute in timely manner?
Take timely backup of Database.