What are the typical steps involved in Testing Databases

Posted in: Database Testing

Download Link for your Favorite Presentation is at the End of this Page
******************************************************************************************
What are the typical steps involved in Testing Databases

Database testing primarily involves two key activities like:

A) Organising Sandboxes

B) Developing Test Cases

Now let us discuss these two key activities in detail

A) Key Activity – 1: Organising Sandboxes: Database testing involves the need of a copy of databases which are called sandboxes. These sandboxes are of following three types

1) Functionality Sandbox:In this we check the new functionality of database and refactor the existing functionality. Then we pass the tested sandbox to the next stage, which is integrated sandbox.

2) Integrated Sandbox: In this we integrate all the sandboxes and then test the system.

3) QA sandbox: After the system is tested, sandboxes are sent for acceptance testing. This will ensure the quality of the database.

B) Key Activity – 2: Development of test cases: The step by step procedure for the development of test cases is as under:

Step – 1: Setting up of the test cases: Set up the database to a known state.

The sources of test data are

1) External test data.

2) Test scripts.

3) Test data with known values.

4) Real world data.

Step – 2: Running the test cases : The test cases are then run. The running of the database test cases is analogous to usual development testing.

Traditional Approach of Test Case Execution:

Test cases are executed on the browser side. Inputs are entered on web-input forms and data i submitted to the back-end database via the web browser interface. The results sent back to the browser are then validated against expected values.

Advantages of Traditional Approach: It is simple and no programming skill is required. It not only addresses thi functionality of stored procedures, rules, triggers and data integrity but also the functionality of application as a whole.

Disadvantages of Traditional Approach:

1) Sometimes the results sent to the browser after test case execution do no necessarily indicate that the data itself is properly written to a record in the table.

2) When erroneous results are sent back to the browser after the execution of test cases, it doesn’t necessarily mean that the error is a database error.

3) A crucial danger with database testing and with regression testing in specific is coupling between tests. If we put the database in to a known state, run several tests against that known states, before setting it, then those tests are potentially coupled to one another.

Advanced Approach of Test Case Execution:

First of all we need to do a schematic preparation for Database Testing, which involves:

Generate a list of database tables, stored procedures, triggers, defaults, rules and so on. This will help us to have a good handle on the scope of testing required for database testing.

Thereafter we can follow the following points:

1. Generate data schemata for tables. Analyzing the schema will help us determine the following:

# Can a certain field value be Null?

# What are the allowed or disallowed values?

# What are the constraints?

# Is the value dependent upon values in another table?

# Will the values of this field be in the look-up table?

# What are user defined data types?

# What are primary key and foreign key relationships among tables?

2. At a high level, analyze how the stored procedures, triggers, defaults and rules work. This will help us determine the following:

# What is the primary function of each stored procedure and trigger? Does it read data and produce outputs, write data or both?

# What are the accepted parameters?

# What are the return values?

# When is the stored procedure called and by whom?

# When is a trigger fired?

3. Determine what the configuration management process is. That is how the new tables, stored procedures, triggers and such are integrated.

Step – 3: Checking the results: Actual database test results and expected database test results are compared in this step as described in the following example.

CREATE FUNCTION f_is leapyear (@ ai_year small int)

RETURNS small int

BEGIN

-if year is illegal (null or -ve ), return -1

IF (@ ai_year IS NULL) or

(@ ai_year <=0) RETURN -1

IF (((@ ai_year % ) = 0) AND

((ai_year % 100) <> 0)) OR

((ai_year % 400) = 0)

RETURN 1 -leap year

RETURN 0 – Not a leap year

END

Following test cases are derived for the above piece of code:

Test_id	Year (Year to Test)	Expected Result	Observed Result	Match
1	-1	-1	-1	Yes
2	-400	-1	-1	Yes
3	100	0	0	Yes
4	1000	0	0	Yes
5	1800	0	0	Yes
6	1900	0	0	Yes
7	2010	0	0	Yes
8	400	1	1	Yes
9	1600	1	1	Yes
10	2000	1	1	Yes
11	2400	1	1	Yes
12	4	1	1	Yes
13	1204	1	1	Yes
14	1996	1	1	Yes
15	2004	1	1	Yes