how to initiate database testing
DESCRIPTION
Database Testing includes Testing for Data Integrity , Data Validity , Data manipulation. Database Objects can be tables, views , stored procedures , indexes etcTRANSCRIPT
![Page 2: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/2.jpg)
Objectives
What is DB Testing ? Testing at the Data Access Layer Scope of DB Testing Need for Testing DB Objects Common Problems that affect the Application Testing Application Vs DB Testing
contd..
![Page 3: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/3.jpg)
Objectives
Should Testers Know Databases Testing the Performance of the Database Data Integrity Understanding E/R Model Identifying defects in E/R Diagrams Case Studies on E/R Diagrams Writing Test Cases to Test Databases
![Page 4: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/4.jpg)
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
What is Database Testing
![Page 5: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/5.jpg)
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
Layers in a Application
![Page 6: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/6.jpg)
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 Datastorage are
kept separately
Application Types
![Page 7: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/7.jpg)
Client LayerBusiness Logic
Layer Database Layer
3 or N Tier Architecture
![Page 8: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/8.jpg)
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
Testing Should Include
![Page 9: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/9.jpg)
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
Why Test Database Objects
![Page 10: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/10.jpg)
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.
GUI Vs Database Testing
![Page 11: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/11.jpg)
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
Problems if Database Testing is Ignored
![Page 12: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/12.jpg)
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
Why Should Test Professional know DBMS
![Page 13: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/13.jpg)
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.
Data Integrity
![Page 14: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/14.jpg)
• 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
Normalisation
![Page 15: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/15.jpg)
Goals of Normalisation
There are two goals of the normalization process
Eliminate redundant data Storing Related Data in a Table
![Page 16: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/16.jpg)
• First Normal Form
• Second Normal Form
• Third Normal Form
• Boyce Codd Normal Form
Normalisation
![Page 17: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/17.jpg)
In the First Normal Form
Every Cell should contain a single value. Eliminate redundant data (for example,
storing the same data in more than one table)
Create separate tables for each group of related data and identify each row with a unique column (the primary key).
Normalization
![Page 18: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/18.jpg)
Normalization
Emp Code Dept ProjCode Hours
E101 Systems P1, P2 , P3
12,14,16
E102 Finance P2,P3 14,16
![Page 19: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/19.jpg)
In the Second Normal Form
Meet all the requirements of the first normal form.
And Every attribute in the row is functionally dependent upon the whole key and not part of the key
Normalisation
![Page 20: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/20.jpg)
Functional Dependany
Given a table R, Attribute A is functionally dependant on attribute B if each value of A is associated precisely with one value of B
Eg. In the Employee Table against every EmpCode there will only one Name so Name is functionally dependant on EmpCode
![Page 21: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/21.jpg)
Normalisation
ECode ProjCode Dept Hours
E101 P27 Systems 90
E305 P27 Finance 10
E508 P51 Admin 101
E101 P51 Systems 101
E101 P20 Systems 60
![Page 22: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/22.jpg)
Normalization
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 untill the employee is assigned a project
![Page 23: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/23.jpg)
Normalization
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 loose information about the department for that employee
![Page 24: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/24.jpg)
Normalisation
PK = Ecode + ProjCode The above table is in the First Normal Form we
need to check if its in 2nd Normal Form Hours is not functionally dependent on Ecode. Hours is not functionally dependent on
ProjCode Hours is functionally dependent on
Ecode+ProjCode
![Page 25: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/25.jpg)
Normalisation
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
![Page 26: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/26.jpg)
Normalisation
Ecode Dept
E101 Systems
E305 Sales
E508 Admin
EMPLOYEEDEPT
![Page 27: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/27.jpg)
Normalisation
PROJECT
Ecode ProjCode Hours
E101 P27 90
E101 P51 101
E101 P20 60
E305 P27 10
![Page 28: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/28.jpg)
In the Third Normal Form Meet all the requirements of the second
normal form. Remove columns that are not dependent
upon the primary key. In other words a relation is said to be in 3NF
when every non key attribute is functionally dependent only on the Primary Key
Normalisation
![Page 29: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/29.jpg)
Normalization
Ecode Dept DeptHead
E101 Systems E901
E305 Finance E906
E402 Sales E906
E508 Admin E908
E607 Finance E909
E608 Finance E909
![Page 30: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/30.jpg)
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 .
Normalization
![Page 31: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/31.jpg)
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 .
Normalization
![Page 32: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/32.jpg)
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
Normalization
![Page 33: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/33.jpg)
Normalization
Ecode Dept
E101 Systems
E305 Finance
E402 Sales
E508 Admin
E607 Finance
Employee
![Page 34: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/34.jpg)
Normalization
Dept DeptHead
Systems E901
Sales E906
Admin E908
Finance E909
Department
![Page 35: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/35.jpg)
Denormalization is the process of
attempting to optimize the performance
of a database by adding redundant data
Denormalisation
![Page 36: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/36.jpg)
Validate the table naming conventions Validate the column naming conventions To check if the correct datatype is selected
for a column To check the consistency in datatypes for
columns common across tables To ensure the usage of correct field width
What do we Test at the DB Level
![Page 37: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/37.jpg)
To ensure consistency in field width for columns common across tables
Existence of a primary key on a table Existence of a foreign key on a table Validity of check constraints Validity of default constraints Check for presence of indexes on a column
What do we Test at the DB Level
![Page 38: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/38.jpg)
Check for Unique indexes Existence of non-clustered indexes Existence of clustered indexes Note the time of execution of queries Note the time of compilation of queries
What do we Test at the DB Level
![Page 39: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/39.jpg)
Evaluate the query execution plan Note the time of execution of stored
procedures Note the time of compilation of stored
procedures Evaluate the query execution plan Denormalize the tables Normalize the tables
What do we Test at the DB Level
![Page 40: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/40.jpg)
Write Review Cases for the following Table Structures (Check for Table Naming Conventions, DataType, Field Size , Keys ,Constraints)
Case Study
![Page 41: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/41.jpg)
Data Factory : Quest SoftwareData generator tool and data manager for
database testingSQL Tuner : Embaradero Eases the complexity of writing high-
performance SQL code by providing built-in help for writing syntactically correct SQL, and by assisting in every aspect of complex tuning efforts.
Database Test Tools
![Page 42: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/42.jpg)
Datatect : Banner Software Generate a variety of realistic test data to
RDBMS including Oracle, Sybase, SQL Server, and Informix
DB Stress : DTM Utility for stress testing the server parts of
information systems and applications, as well as DBMSs and servers themselves.
Database Test Tools
![Page 43: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/43.jpg)
Datatect : Banner Software Generate a variety of realistic test data to
RDBMS including Oracle, Sybase, SQL Server, and Informix
DB Stress : DTM Utility for stress testing the server parts of
information systems and applications, as well as DBMSs and servers themselves.
Database Test Tools
![Page 44: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/44.jpg)
– Database Opensource Test Suite– The Database Opensource Test Suite
(DOTS) is a set of test cases designed for the purpose of stress-testing database server systems in order to measure database server performance and reliability.
Database Test Tools
![Page 45: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/45.jpg)
– DBMonster– DBMonster is an application to generate
random data for testing SQL database driven applications under heavy load.
Database Test Tools
![Page 46: How to initiate database testing](https://reader036.vdocuments.us/reader036/viewer/2022062313/558a50f7d8b42ac66a8b459e/html5/thumbnails/46.jpg)