Monday, October 21, 2013

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
 
Testing Should Include

          Testing the Front End / GUI / Client Layer

          Testing the Business Logic Layer

          Testing the Database

1.     Reviewing  E/R Diagrams

2.     Reviewing the Database Designs

3.     Reviewing the Tables, views , stored procedures etc.

Why Test Database Objects?

          Data is stored in the tables

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

          No 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 2nd 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
Telcordia
The AETG algorithms employ combinatorial design techniques to create minimal sets of tests that cover all the pairwise interactions among input values.
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
DTM
Fully customizable utility that generates data, tables (views, procedures etc) for database testing (performance testing, QA testing, load testing or usability testing) purposes.
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 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.
Banner Software
Produces "model-driven" test data in support of Allfusion ERwin and ER/Studio (enterprise editions).
 
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.
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.
Embaradero
Identify, isolate, and correct coding mishaps that could lead to costly performance problems.
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.
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.
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.
 
Open source, unit-testing framework for Oracle PL/SQL developers.
 


 

No comments:

Post a Comment