•
Database Testing includes Testing for Data Integrity,
Data Validity , Data manipulation.
Case Study
•
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
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
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:
| |
|
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