1/9/11

Database Testing

Although not all databases are the same, there are a set of test areas that will be covered in all test specifications. Below are the test objects that should be covered in all databases.

 Schema

 Stored Procedures

 Triggers

Database schema testings

• Databases and devices
• Tables, columns, column types, defaults, and rules
• Keys and indexes

Stored procedure tests

• Individual procedure tests
• Integration tests of procedures

Trigger tests

• Updating triggers
• Inserting triggers
• Deleting triggers

Database Testing Brief:

Why Database or back end testing is so important?

A back end is the engine of any client/server system. If the back end malfunctions, it may cause system deadlock, data corruption, data loss and bad performance. A bug in a back end may put serious impact on the whole system. Too many bugs in a back end will cost tremendous resources to find and fix bugs and delay the system developments

It is very likely that many tests in a front end only hit a small portion of a back end. Many bugs in a back end can not be easily discovered without direct testing. Back end testing has several advantages: The back end is no longer a "black box" to testers. We have full control of test coverage and depth. Many bugs can be effectively found and fixed in the early development stage.

Differences between back end testing and front end testing:

It is not easier to understand and verify a back end than a front end because a front end usually has friendly and intuitive user interfaces.

A back end has its own objects, such as, tables, stored procedures and triggers. Data integrity and protection is critical. Performance and multi-user support are big issues. Slowness in operation can be vital to the project’s future. There are no sufficient tools for back end testing. SQL language is mainly a testing tool. However, there are a large number of test tools available for front end testing.

To be able to do back end testing, a tester must have strong background in Database and SQL language. It is relatively difficult to find testers who understand both Database server and SQL testing.

Back end test methodology

Back end test methodology has many things in common with front end testing and API testing. Many test methods can be used for back end testing. Structural testing and functional testing are more effective approaches in back end testing

Structural testing:

A back end can be broken down into a finite number of testable pieces based on a back end’s structure. Tests will verify each and every object in a type of structure.

Functional testing:

A back end can be broken down into a finite number of testable pieces based on application’s functionality. The test focus is on functionality of input and output but not on the implementation and structure. Different projects may have different ways to break down.

Boundary testing:

Many columns have boundary conditions. For example, in a column for percentages, the value can not be less than zero and can not be greater than 100%. We should find out these types of boundary conditions and test them.

Stress testing:

It involves subjecting a database to heavy loads. For incidence, many users heavily access the same table that has a large number of records. To simulate this situation, we need to start as many machines as possible and run the tests over and over.

Test a Database Via Front End:

Sometimes back end bugs can be found by front end testing, try to do the following things while testing a backend via front-end

• Make queries from a front end and issue the searches (It hits SELECT statements or query procedures in a back end)

• Pick up an existing record, change values in some fields and save the record. (It involves UPDATE statement or update stored procedures, update triggers.)

• Push FILE - NEW menu item or the NEW button in a front end window. Fill in information and save the record. (It involves INSERT statements or insertion stored procedures, deletion triggers.)

• Pick up an existing record, click on the DELETE or REMOVE button, and confirm the deletion. (It involves DELETE statement or deletion stored procedures, deletion triggers.)

• Repeat the first three test cases with invalid data and see how the back end handles them.

SQL Queries most used in DB Testing:

Querying the recent data

Select top 1 * from tablename order by date/time desc

Ex: Select top 1 * from employee order by joiningdate desc

Checking for the duplicate records

Select * from tablename group by keycolumn having count (*) > 1

Ex: Select * from employee group by empid having count (*) > 1

Find out the existence of objects

Select * from sysobjects where type = “

Test Scenarios to Test Table:

 Verify Table name
 Verify Column names
 Verify Column types
 Verify Column allows NULL or not
 Verify Default definitions
 Verify Access privileges are granted to correct groups
 Verify Primary Key
 Verify Candidate Keys
 Verify Foriegn Keys

Test Scenarios to Test Stored Procedures:

1. Individual procedure tests

Verify the following things and compare them with design specification

• whether a stored procedure is installed in a database
• Stored procedure name
• Parameter names, parameter types and the number of parameters

Outputs:

• when output is zero (zero row affected)
• when some records are extracted
• Output contains many records
• what a stored procedure is supposed to do
• What a stored procedure is not supposed to do
• Write simple queries to see if a stored procedure populates right data

Parameters:

• Check parameters if they are required.
• Call stored procedures with valid data
• Call procedures with boundary data
• Make each parameter invalid a time and run a procedure

Return values:

• Whether a stored procedure returns values
• When a failure occurs, nonzero must be returned.

Error messages:

• Make stored procedure fail and cause every error message to occur at least once
• Find out any exception that doesn’t have a predefined error message

Others:

• Whether a stored procedure grants correct access privilege to a group/user
• See if a stored procedure hits any trigger error, index error, and rule error
• Look into a procedure code and make sure major branches are test covered.

2. Integration tests of procedures

• Group related stored procedures together. Call them in particular order
• If there are many sequences to call a group of procedures, find out equivalent classes and run tests to cover every class.
• Make invalid calling sequence and run a group of stored procedures.


Test Scenarios to test Triggers:

1.Updating triggers

Verify the following things and compare them with design specification

• Make sure trigger name spelling is correct
• See if a trigger is generated for a specific table column
• Trigger’s update validation
• Update a record with a valid data
• Update a record, a trigger prevents, with invalid data and cover every trigger error
• Update a record when it is still referenced by a row in other table
• Make sure rolling back transactions when a failure occurs
• Find out any case in which a trigger is not supposed to roll back transactions

2.Inserting triggers

Verify the following things and compare them with design specification

• Make sure trigger name spelling
• See if a trigger is generated for a specific table column
• Trigger’s insertion validation
• Insert a record with a valid data
• Insert a record, a trigger prevents, with invalid data and cover every trigger error
• Try to insert a record that already exists in a table
• Make sure rolling back transactions when an insertion failure occurs
• Find out any case in which a trigger should roll back transactions
• Find out any failure in which a trigger should not roll back transactions
• Conflicts between a trigger and a stored procedure/rules
(i.e. a column allows NULL while a trigger doesn’t)

3. Deleting triggers

Verify the following things and compare them with design specification

• Make sure trigger name spelling
• See if a trigger is generated for a specific table column
• Trigger’s deletion validation
• Delete a record
• Delete a record when it is still referenced by a row in other table
• Every trigger error
• Try to delete a record that does not exists in a table
• Make sure rolling back transactions when a deletion fails
• Find out any case in which a trigger should roll back transactions
• Find out any failure in which a trigger should not roll back transactions
• Conflicts between a trigger and a stored procedure/rules
(i.e. a column allows NULL while a trigger doesn’t)

0 comments:

Post a Comment