Database testing

Database Testing

Introduction

Database is nothing but the collection of such files which are interconnected.Databases at different servers may not be of same type i.e. databases may be heterogeneous.So many kind of implementation and integration errors may occur in large database systems which is not feasible from the point of view of system's performance, reliability, consistency and security.So in order to obtain database which satisfy ACID properties of database management system, we need to test it, so the term database testing comes in a picture.

Need of database testing

Basically,database testing is a layered process.It consists of four layers namely User interface layer,Business layer,data access layer and database.Testing at these different layers is important for consistent database.Many of system and web soft wares access databases from different back-ends i.e. they access heterogeneous databases.Testing productivity process is very difficult for client-server databases or web-based databases.From the above mentioned layers, UI layer deals with interface design of database,Business layer includes databases supporting business strategies.The most important layer is data access layer which deals with databases directly during communication process.Database testing is mainly takes place at this layer which includes our further testing strategies, quality control and quality assurance of the product databases. Finally in database,we are supposed to with atomic, consistent, isolated and durable database.The points why we need database testing are as follows:

• The data is very important from business point of view.Most of the big companies such as Google,Symantec who deal with storage of databases should have a durable and consistent databases.If the database operations such as insert,delete,update are preformed without testing database for its consistency,then whole database may crash.
• Some companies have different types database, have different goals and mission.So in order to achieve a mission critical functionality,they need to test their databases.
• The current approach of testing may not be sufficient in which developer formally test some databases which is not so effective.

So in this case database developers will face some difficulties such slowing the testing process,difficult to test,communication gap between them.So the separate database testing team is needed.

• Database testing is mainly deals with the finding defect in the databases so as to eliminate them.This will improve the quality of your system software or web-based software.
• Something different should be done in order to tolerate with database crash, broken insertion,deletion or update.So Database refactoring is evolutionary technique to deals with it.

Different testings involves in Database Testing process

The figure indicates the areas of testings that need to be tested during database testing i.e.black box testing and White box testing

Blackbox and white box testing in database testing

Black box testing in database testing

Black box testing involves testing interfaces,integration of the databases, which includes:

1. Mapping of data including meta data
2. It verifies incoming data from data load.
3. It also verifies out going data from queried function.
4. various techniques such as Cause effect graphing technique,Equivalence partitioning and boundary value analysis are used.With the help of these techniques,we independently test the functionality of the database.

• Pros and Cons of black box testing:

Test case generation in block box testing is very easy.Their generation are completely independent of program and we can state them in early stage of development.because of this programmer has advanced knowledge of how to design the database application resulting in reduction in efforts and debugging.Cost for development of black box test cases is lower as compare to white box test cases.The major drawback of the black box testing is,we do not know how much part of our program is being tested and it also not applicable for some faults.

White box Testing in database testing

White box testing is mainly deals with the internal structure of the database.The specification details are hidden from the user.It involves:

1. It involves the testing of database triggers, logical views which are going to support database refactoring.
2. It performs module testing of our database functions,triggers,views,SQL queries etc.
3. Validate our database tables,data models,database schema etc.
4. Checks Rules of Referential integrity
5. Selection of default table values maintaining database consistency.
6. The techniques used in white box testing are condition coverage,decision coverage,statement coverage,cyclomatic complexity.

• pros and cons of white box testing:

The main advantage of white box testing in database testing is that we are aware of coding details,so we can eliminate internal bugs in database.The limitation of white box testing is that, SQL statements are not traced in white box testing.

The WHODATE approach for database testing

While generating the test cases for database testing, the semantic of SQL statement need to be included in resulting test cases.For that purpose, a technique called White box database application technique (WHODATE)is used.

WHODATE approach for SQL statement transformation

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.
• Testing system under test.
• Outcome Verification.
• Tear down.

Test fixture describes the initial state of your database before entering into the testing.After setting fixtures,we need to test our database behavior under defined test cases.Depending on the outcome we get,we either modify test case or just keep it as it is.Tear down either results in terminating the testing or results in another test cases. In order to make successful database testing we should follow the following workflow to execute each single test,

• 1.Clean up the database:

In the case of database testing,if the testable data is already present in the database,then we need to reset it to empty.

• 2.Set up Fixture:

A tool like PHPUnit will then iterate over fixtures and insert into table.

• 3.Run Test, Verify outcome and then Tear down:

After resetting database to empty and listing of fixtures,we need to run the test and verify the output.If it results in success, then tear down other wise do testing again,and repeat same procedure.

Some problems in database testing

1. The setup for database testing is costly and complex to maintain, because database schema are constantly changing with insert,delete and update operations.
2. Extra overhead is involved in order to determine state of the database transactions.
3. After cleaning up the database,new test cases are required to design.
4. The SQL generator is required to transform SQL statement in order to include SQL semantic into database test cases.

Database Testing Tips

1. If we are using the SQL server, then start SQL query analyzer,then verify the result.
2. For dynamic inputs to the query to validate the calculation of the process,we must take care of the function create_input_dialog["label"]
3. Design some automated database testing form front end and back end, depending on object such as

• list
• tables
• objects etc.
• Data load Testing:
• In data load testing, you should have knowledge about source database and destination database.
• Then check the compatibility between source database and destination database using DTS package.
• By updating the database, compare it with target database.
• It measures a capacity of the database server to handle queries and measures the response time for database server and client.
• In database testing, you should consider the following issues:

             *Atomicity
             *Consistency
             *Isolation
             *Durability
             *Integrity
             *Execution of triggers
             *Recovery

See Also

• Unit Testing
• Structured Query Language
• Software Testing
• Database Normalization

External links

• example.com