Database Testing

Database Testing includes Testing for Data Integrity, Data Validity , Data Manipulation.  

• Database Objects can be tables, views , stored procedures , indexes etc

• Time taken for Retrieval of Records from the Database

• Time for Query Execution 

Layers in Application

• Client Layer – Is responsible for the presentation of data, receiving user events and controlling the user interface. 

• Application Layer - Business-objects that implement the business rules "live" here, and are available to the client-tier 

• Data Layer - This tier is responsible for data storage

Application Types

• Single Tier Architecture(Client & Business Logic& Data Storage are all wrapped together)

• 2 Tier Architecture(Business Logic and Data Storage are together and the Client is a separate layer)

• 3 or the N tier Architecture(Client, the Business Logic and the Data storage are kept separately)

 

3 or the N tier Architecture

Application Testing Should Include

Testing the Front End / GUI / Client Layer

Testing the Business Logic Layer

       Testing the Database(Reviewing  E/R Diagrams, Reviewing the Database Designs & Reviewing the Tables, views , stored procedures etc.)     

     Why Test Database Objects? 

       • Data is stored in the tables

       • Stored Procedures will handle the Insertion , deletion & Updating & Retrieval of Information from  the Database

       • Testing/Improper testing will result in missing critical application functionality

GUI Vs. Database Testing

Traditionally all the data testing is done at the GUI Level

Corruption of data can occur at any layer

We must present verification of application correctness as data travels through the system.

 

Problems if Database Testing is Ignored

Data Corruption: Occurs due to poor design

Redundant Data: Hidden duplicate records (same customer added twice with different primary keys).

Inconsistent Data: Data records added to the same database through multiple applications can add inconsistent data.

Redundant Validation: Validating business rules on the db, as well as the client, can cause conflicts if they’re not well-coordinated

 

Why Should Test Professional know DBMS

 

•          Inadequate knowledge of Relational db design fundamentals leads to logic errors and very common bugs in systems

 

•          Basic normalization principles can and should be tested but isn’t -- because most testers have no idea what that is

 

•          Effective DB testers should be able to uncover design problems quickly

 

Data Integrity

 

•          Data Integrity Ensures the Consistency and correctness of Data stored in a Database

 

•          Maintenance of data values according to data model and data type. For example, to maintain integrity, numeric columns will not accept alphabetic data.

 

Normalization

 

•          It's the process of efficiently organizing data in a database.

 

•          The database community has developed a series of guidelines for ensuring that databases are normalized. These are referred to as normal forms Goals of Normalization

 

•          There are two goals of the normalization process

 

•          Eliminate redundant data

 

•          Storing Related Data in a Table

 

Form of Normalization

 

•          First Normal Form

 

1.     Every Cell should contain a single value.

 

2.     Eliminate redundant data (for example, storing the same data in more than one table)

 

3.     Create separate tables for each group of related data and identify each row with a unique column (the primary key).

 

Emp Code	Dept	ProjCode	Hours
E101	Systems	P1, P2 , P3	12,14,16
E102	Finance	P2,P3	14,16

 

 

•          Second Normal Form

 

1.     Meet all the requirements of the first normal form.

 

2.     And Every attribute in the row is functionally dependent upon the whole key and not part of the key

 
 

Functional Dependency : Given a table R, Attribute A is functionally dependent on attribute B if each value of A is associated precisely with one value of B

 

E.g. In the Employee Table against every EmpCode there will only one Name so Name is functionally dependent on EmpCode

 

 

 

 

 

ECode	ProjCode	Dept	Hours
E101	P27	Systems	90
E305	P27	Finance	10
E508	P51	Admin	101
E101	P51	Systems	101
E101	P20	Systems	60

 

 

 

       The Above Table is in the First Normal Form.

 

The Above table will lead to the following problems:

 

Insertion – Dept of a particular employee cannot be inserted until the employee is assigned a project

 

Updation: If an employee is transferred from one department to another the changes have to be made n number of times in the table

 

Deletion: When the Project is over and the record deleted we will lose information about the department for that employee

 

 

 

PK = Ecode + ProjCode

 

The above table is in the First Normal Form we need to check if its in 2^nd Normal Form

 

Hours are not functionally dependent on Ecode.

 

Hours is not functionally dependent on ProjCode

 

Hours is functionally dependent on Ecode+ProjCode

 

Dept is functionally dependent on Ecode.

 

Dept is not Functionally dependent on ProjCode

 

Dept is functionally dependent on part of the key (Ecode+ProjCode)

 

Therefore table is not in 2 N F

 

Therefore Place Dept along with Ecode in a separate table

 

EMPLOYEEDEPT

 

ECode	Dept
E101	Systems
E305	Finance
E508	Admin

 

 

 

PROJECT

 

ECode	ProjCode	Hours
E101	P27	90
E101	P51	101
E101	P20	60
E305	P27	10
E101	P27	90

 

 

•          Third Normal Form

 

1.     Meet all the requirements of the second normal form.

 

2.     Remove columns that are not dependent upon the primary key.

 

3.     In other words a relation is said to be in 3NF when every non key attribute is functionally dependent only on the Primary Key

 

Ecode	Dept	DeptHead
E101	Systems	E901
E305	Finance	E906
E402	Sales	E906
E508	Admin	E908
E607	Finance	E909
E608	Finance	E909

 

 

 

The Primary Key is Ecode

 

Dept is functionally dependent on Ecode

 

DeptHead is functionally dependent on the primary Key Ecode.

 

All attributes are functionally dependent on the whole key Ecode Therefore Table is in 2NF

 

But DeptHead is functionally dependent on Dept.

 

The table is not in the 3 NF because as per the third Normal Form every attribute should be functionally dependent only on the Primary Key.

 

Identify and remove the  attributes that are not functionally dependent on the primary key. Place them in a different table

 

Employee

 

Ecode	Dept
E101	Systems
E305	Finance
E402	Sales
E508	Admin
E607	Finance

 

 

 

Department

 

Dept	DeptHead
Systems	E901
Sales	E906
Admin	E908
Finance	E909

 

 

•          Boyce Codd Normal Form

 

1.     When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF.

 

2.     3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys

 

3.     i.e. composite candidate keys with at least one attribute in common.

 

4.     BCNF is based on the concept of a determinant.

 

5.     A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent.

 

6.     A relation is in BCNF is, and only if, every determinant is a candidate key.

 

Consider the following relation and determinants.

 

           R(a,b,c,d)
                       a,c -> b,d
                       a,d -> b

 

Here, the first determinant suggests that the primary key of R could be changed from a,b to a,c. If this change was done all of the non-key attributes present in R could still be determined, and therefore this change is legal. However, the second determinant indicates that a,d determines b, but a,d could not be the key of R as a,d does not determine all of the non key attributes of R (it does not determine c). We would say that the first determinate is a candidate key, but the second determinant is not a candidate key, and thus this relation is not in BCNF (but is in 3rd normal form).

 

De-Normalization

 

De-Normalization is   the    process of attempting to optimize the performance of a database by adding redundant data

 

What do we Test at the DB Level

 

1.     Validate the table naming conventions

 

2.     Validate the column naming conventions

 

3.     To check if the correct data type is selected for a column

 

4.     To check the consistency in data types for columns common across tables

 

5.     To ensure the usage of correct field width

 

6.     To ensure consistency in field width for columns common across tables

 

7.     Existence of a primary key on a table

 

8.     Existence of a foreign key on a table

 

9.     Validity of check constraints

 

10.  Validity of default constraints

 

11.  Check for presence of indexes on a column

 

12.  Check for Unique indexes

 

13.  Existence of non-clustered indexes

 

14.  Existence of clustered indexes

 

15.  Note the time of execution of queries

 

16.  Note the time of compilation of queries

 

17.  Evaluate the query execution plan

 

18.  Note the time of execution of stored procedures

 

19.  Note the time of compilation of stored procedures

 

20.  Evaluate the query execution plan

 

21.  De-Normalize the tables

 

22.  Normalize the tables

Case Study 

Write Review Cases for the following Table Structures (Check for Table Naming Conventions, Data Type, Field Size , Keys ,Constraints)

 

Database Test Tools

Product	Vendor	Comments
AETG	Telcordia	The AETG algorithms employ combinatorial design techniques to create minimal sets of tests that cover all the pairwise interactions among input values.
Data Generator	GSApps	Test data generator, used for creating intelligent data in almost any database or text file. GS DataGenerator enables users to: Complete application testing by inflating a database with meaningful data Create industry-specific data that can be used for a demonstration Protect data privacy by creating a clone of the existing data and masking confidential values Accelerate the development cycle by simplifying testing and prototyping
Data Generator	DTM	Fully customizable utility that generates data, tables (views, procedures etc) for database testing (performance testing, QA testing, load testing or usability testing) purposes.
Datatect	Banner Software	Generate a variety of realistic test data to ASCII flat files or directly to RDBMS including Oracle, Sybase, SQL Server, and Informix.
DTM DB Stress	DTM Soft	Utility for stress testing the server parts of information systems and applications, as well as DBMSs and servers themselves. This tool allows you to create and configure a continuous set of requests to the server of the OLAP (query execution) and OLTP (adding, modifying and deleting data in the database) types.
ER/Datagen	Banner Software	Produces "model-driven" test data in support of Allfusion ERwin and ER/Studio (enterprise editions).
Jenny		JENNY is a free tool similar to AETG and ALLPAIRS. Given many dimensions of a piece of software, with several possible features per dimension, and some restrictions saying which features cannot be used together, JENNY will recommend tests that cover all pairs or triples of allowed feature combinations. For example, given 20 dimensions, 10 features each, JENNY recommends 210 testcases.
SQL DB Validator	C and C Productions	Performs database and data cube verification and validation testing. Tests the content of a database extract against its original data store using the specific SQL syntax for each of the data sources. Tests can be contained in a single file and each test can be configured to connect to different data sources.
SQL Profiler	Embaradero	Identify, isolate, and correct coding mishaps that could lead to costly performance problems.
SQS/Test Professional	SQS	Develops business-oriented requirements for test data - usually for individual functions in an application - so that the necessary data is input (e.g. in a dialog) to ensure that all critical processes in an application are called.
TestIt!	TdgTeam	Test your programs with data that 'feels' right. Simple yet powerful way to generate great quantity of random but real-life looking data, real fast. Generates meaningful people names, companies, streets, cities, IDs, etc. Quick-start tutorial with examples.
TurboData	Canam Software	Test data generator. Generate very realistic test data with foreign keys resolved automatically. Also generates Select, Update and Delete SQL. TurboData includes an SQL Builder, Grid Data Editor and has full Version Control. Free trial version never expires.
utPLSQL		Open source, unit-testing framework for Oracle PL/SQL developers.