Database testing usually consists of a layered process, including the
user interface (UI) layer, the business layer, the data access layer and the
database itself. The UI layer deals with the interface design of the database,
while the business layer includes databases supporting business strategies. The
most critical layer is the data access layer, which deals with databases
directly during the communication process. Database testing mainly takes place
at this layer and involves testing strategies such as quality control and
quality assurance of the product databases. Testing at these different layers
is frequently used to maintain consistency of database systems, most commonly
seen in the following examples:
- Data is critical from a business point of view. Companies such as Google or Symantec, who are associated with data storage, need to have a durable and consistent database system. If database operations such as insert, delete, and update are performed without testing the database for consistency first, the company risks a crash of the entire system.
- Some companies have different types of databases, and also different goals and missions. In order to achieve a level of functionality to meet said goals, they need to test their database system.
- The current approach of testing may not be sufficient in which developers formally test the databases. However, this approach is not sufficiently effective since database developers are likely to slow down the testing process due to communication gaps. A separate database testing team seems advisable.
- Database testing mainly deals with finding errors in the databases so as to eliminate them. This will improve the quality of the database or web-based system.
- Database testing should be distinguished from strategies to deal with other problems such as database crashes, broken insertions, deletions or updates. Here, database refactoring is an evolutionary technique that may apply.
Types of testing and
processes
The figure indicates the areas of testing involved during
different database testing methods, such as black-box testing and white-box
testing.
Black Box Testing in
database testing
Black box testing involves testing interfaces and the
integration of the database, which includes:
- Mapping of data (including metadata)
- Verifying incoming data
- Verifying outgoing data from query functions
- Various techniques such as Cause effect graphing technique, equivalence partitioning and boundary-value analysis.
Pros and Cons of black box testing include: Test case
generation in black box testing is fairly simple. Their generation is
completely independent of software development and can be done in an early
stage of development. As a consequence, the programmer has better knowledge of
how to design the database application and uses less time for debugging. Cost
for development of black box test cases is lower than development of white box
test cases. The major drawback of black box testing is that it is unknown how
much of the program is being tested. Also, certain errors cannot be detected.
White Box Testing in
database testing
White box testing mainly deals with the internal structure
of the database. The specification details are hidden from the user.
- It involves the testing of database triggers and logical views which are going to support database refactoring.
- It performs module testing of database functions, triggers, views, SQL queries etc.
- It validates database tables, data models, database schema etc.
- It checks rules of Referential integrity.
- It selects default table values to check on database consistency.
- The techniques used in white box testing are condition coverage, decision coverage, statement coverage, cyclomatic complexity.
The main advantage of white box testing in database testing
is that coding error are detected, so internal bugs in the database can be
eliminated. The limitation of white box testing is that SQL statements are not
covered.
The WHODATE approach
for database testing
WHODATE approach for SQL statement transformation
While generating test cases for database testing, the
semantics of SQL statement need to be reflected in the test cases. For that
purpose, a technique called WHite bOx Database Application TEchnique
"(WHODATE)" is used. As shown in the figure, SQL statements are
independently converted into GPL statements, followed by traditional white box
testing to generate test cases which include SQL semantics.
Four stages of database
testing
- Set Fixture
- Test run
- Outcome verification
- Tear down
For successful database testing the following workflow
executed by each single test is commonly executed:
- Clean up the database: If the testable data is already present in the database, the database needs to be emptied.
- Set up Fixture: A tool like PHPUnit will then iterate over fixtures and do insertions into the database.
- Run test, Verify outcome and then Tear down: After resetting the database to empty and listing the fixtures, the test is run and the output is verified. If the output is as expected, the tear down process follows, otherwise testing is repeated.
Some problems in
database testing
- The setup for database testing is costly and complex to maintain because database systems are constantly changing with expected insert, delete and update operations.
- Extra overhead is involved in order to determine the state of the database transactions.
- After cleaning up the database, new test cases have to be designed.
- An SQL generator is needed to transform SQL statements in order to include the SQL semantic into database test cases.
Basic techniques
- SQL Query Analyzer is a helpful tool when using Microsoft SQL Server.
- One commonly used function, create_input_dialog["label"], is used to validate the output with user inputs.
- The design of forms for automated database testing, form front-end and back-end, is helpful to database maintenance workers.
- Data load testing:
- For data load testing, knowledge about source database and destination database is required.
- Workers check the compatibility between source database and destination database using the DTS package.
- When updating the source database, workers make sure to compare it with the target database.
- Database load testing measures the capacity of the database server to handle queries as well as the response time of database server and client.
