Database Testing
AGENDA
6. Schema Validation
1. What is Database Testing?
a. Structure Validation
2. Why Database Testing When We Have UI Based
b. Data Type Validation
Testing?
7. Constraint Validation
3. What to Test in a Database?
8. Trigger Validation
a. Transactions
9. Stored Procedure Validation
b. ACID Properties
10. Functional Database Testing
c. DB Schema
11. Non-functional Database Testing
d. Triggers
a. Load Testing
e. Constraints
b. Stress Testing
f. Data Mapping
12. Writing Database Test Cases
g. Data Integrity
13. Database Testing Tools
4. How to Test a Database?
14. Assignments
5. Types of Database Testing
What is Database Testing?
Testing Databases
01 What is Database Testing?
● Validating UI data against the records ● Creating complex queries to test the
stored in the database meets the database and check it’s responsiveness
requirement and design
Contd ...
Testing Databases
01 What is Database Testing?
● Checking the schema, tables, triggers, etc. ● To check data integrity and consistency
of the database under test
Why Database Testing When We Have
UI Based Testing?
Big Data Testing - Testing Databases
Why Database Testing When We
02 Have UI Based Testing?
● To ease the complexity of calls to database backend, developers increase the use of View and Stored
Procedure.
● These Stored procedures and Views contain critical tasks such as inserting customer details like name,
contact information, etc. and sales data. These tasks need to be tested at several levels.
● Black-box testing performed on front-end is important, but makes it difficult to isolate the problem. Testing
at the backend system increases the robustness of the data. That is why database testing is performed on back
end system.
● In a database, data comes from multiple applications and there is a possibility that harmful or incorrect data
is stored in the database. Therefore, there is a need to check database components regularly. In addition, data
integrity and consistency should be checked regularly.
Contd ...
Big Data Testing - Testing Databases
Why Database Testing When We
02 Have UI Based Testing?
What to Test in a Database?
Big Data Testing - Testing Databases
03 What to Test in Database?
a. Transactions
● By using ACID Properties
● BEGIN & END TRANSACTIONS
● COMMIT & ROLLBACK method
Big Data Testing - Testing Databases
03 What to Test in Database?
B. ACID Properties
Atomic Consistent
“ALL or NOTHING”. Transfer DB from one consistent
Transaction cannot be subdivided. state to another consistent state.
Any data must be valid
according to the established
rules.
Isolated Durable
Transactions execute The permanence of the DB
independently of one other. consistent state.
DB changes not revealed to user DB changes are permanent.
until after transactions has
completed.
Testing Databases
03 What to Test in Database?
C. DB schema
● Tables / Views are created in the corresponding schema
● The table / view definition
● Validating expected domain attribute
● Validation of Column count
● Validation of Column data type
● Validation of Length of column
● DESC command
Testing Databases
03 What to Test in Database?
D. triggers
● White Box Testing:
○ Without integration invoke the trigger using Stubs & Drivers
● Black Box Testing:
○ Integrate with GUI and invoke trigger by satisfying the
condition
○ Bulk load data direct to DB to invoke the trigger
Testing Databases
03 What to Test in Database?
E. constraints
Testing Databases
03 What to Test in Database?
F. Data mapping
● Data Mapping is mapping a specific data field in the GUI to
backend DB
● Data Travels Back and Forth (DB->Application->DB)
by mapping application fields to DB columns
● Validating Mappings amongst Application Interface and DB is
critical to maintain data consistency
● Validate Mapping correctness by firing CRUD actions from
Front end and check for corresponding changes at backend
CRUD: Create, Retrieve, Update, Delete
Testing Databases
03 What to Test in Database?
F. Data mapping
● Table mapping, column mapping, and data type mapping
● Lookup data mapping
● Correct CRUD operation is invoked for every user action at
User Interface
Testing Databases
03 What to Test in Database?
G. Integrity
● Data integrity is the overall completeness, accuracy and consistency of data
● Logically well organized data
● Data is correct and as per the business requirements
● Unnecessary data present in the application under test
● Data Concurrency in respect to GUI input
● TRIM operations performed on the data before inserting data
● Data has been properly committed
● Data has been rollbacked successfully if the transaction is unsuccessful
● Transactions have been executed by using the required design procedures
Contd ...
Testing Databases
03 What to Test in Database?
G. Integrity Legacy &
Data Profiling 3rd Party
Interfaces
Data Cleansing ETL Design &
Development
Data Merging, Structured/
Migration & Unstructured
Replication Data
Data Modeling Data
Warehousing
How to Test a Database?
Testing Databases
04 How to Test Database?
Prepare the test environment
01
Run a test
02
Check the result
03
Validate
04
Report the findings
05
Types of Database Testing
Big Data Testing - Testing Databases
05 Types of Database Testing
Structural testing
It deals with table and column testing, schema testing, stored procedures and
views testing, checking triggers, etc
functional testing
It involves checking the functionality of a database from the user point of view
non-functional testing
It involves load-testing, risk testing in database, stress testing, minimum system
requirements, and deals with the performance of the database
Schema Validation
Big Data Testing - Testing Databases
06 Schema Validation
a. Structure validation for Tables and Views
● Most DDLs are mentioned in Flat files
● DDLs are manually updated by the developers
● DDLs are hard coded
● Provided to the data loader during run time
● Validation of the DDLs and the table structure is
important
● Mismatch in the DDLs and the Table structure causes
record drops
Big Data Testing - Testing Databases
06 Schema Validation
B. Data type & length validation
● Data type validation is necessary due to changeable nature
of the data type
● An int variable can be changed to string variable and vice
versa
● Data drop during loading and fetching can occur due to
bad data type
● Data truncation can occur due to column length being
short
● Performance issue if the column length is greater than
required
Constraint Validation
Testing Databases
07 Constraint Validation
Constraints are used to limit the type of data that can go into a table
● Primary key: No duplicates of same value should be present in the column
● NULL Value: The values if mentioned NOT NULL, should not be populated with NULL Values
● Surrogate Key: If column is defined as a surrogate key then there should not be any duplications
● Unique value: If column is defined to have Unique values, then the duplicate values cannot exist
● Default value: Default value to be populated if there are no values entered for the specific column
● Referential Integrity Check: If two or more tables have primary key - foreign key relationship then the
primary key should follow cascading update and cascading delete
Testing Databases
07 Constraint Validation
Example
Trigger Validation
Testing Databases
08 Trigger Validation
A trigger is an object related to tables and stored in DB. Its name derives from its behaviour as it is executed
automatically when an event occurs in the database server.
● Whether the required coding conventions have been
followed during the coding phase of the Triggers
● Check whether the triggers executed for the respective
DML transactions have fulfilled the required conditions
● Whether the trigger updates the data correctly once they
have been executed
● Validation of the required Update/Insert/Delete triggers
functionality in the realm of the application under test
Testing Databases
08 Trigger Validation
example
● Audit data received “New employee name XYZ is added at Mar 04 2018 10:10 AM” is an example of
INSERT Trigger
● Audit data received “An existing employee name XYZ is updated Phone Number at Mar 05 2018 16:05
PM” is an example of Update trigger
● Audit data received “An existing employee name XYZ is deleted at Mar 10 2018 20:00 PM” is an example
of DELETE trigger
Stored Procedure Validation
Testing Databases
09 Stored Procedure Validation
A stored procedure is a set of SQL statements with an assigned name, which are stored in a
relational database management system as a group, so it can be reused and shared by multiple programs.
● Whether the development team did adopt the required coding standard conventions and exception and error
handling
● Whether the development team did cover all the conditions/loops by applying the required input data to the
application under test
● Whether the manual execution of the Stored Procedure provides the end user with the required result
● Whether the manual execution of the Stored Procedure ensures the table fields are being updated as required
by the application under test
● Validation of the fact that all the Stored Procedures and Functions have been successfully executed when the
Database under test is blank.
● Validation of the overall integration of the stored procedure modules as per as the requirements of the
application under test
Some of the interesting tools for testing stored procedures are LINQ , SP Test tool etc
Testing Databases
09 Stored Procedure Validation
Example
Employee ID Name Age Mobile
If I have an ‘Employee’ table -----------------------------------------------------------------
-----
001 Mickey 30 1234567890
First I am retrieving the Employee table: 002 Mouse 25 9876543210
Create Procedure Employee details
As
Begin
Select * from Employee
End
To run the procedure on SQL Server:
Execute Employee details
--- (‘Employee details’ is a user defined name, give a name as you want)
Functional Database Testing
Testing Databases
10 Functional Database Testing
The Functional DB testing as specified by the requirement specification needs to ensure most of those transactions
and operations as performed by the end users are consistent with the requirement specifications.
Following are the basic conditions which need to be observed for database validations.
● Whether the field is mandatory while allowing NULL values on that field?
● Whether the length of each field is of sufficient size?
● Whether all similar fields have same names across tables?
● Whether there are any computed fields present in the Database?
This particular process is the validation of the field mappings from the end user viewpoint. In this particular
scenario the tester would perform an operation at the database level and then would navigate to the relevant user
interface item to observe and validate whether the proper field validations have been carried out or not.
The vice versa condition whereby first an operation is carried out by the tester at the user interface and then the
same is validated from the back end is also considered to be a valid option.
Contd ...
Testing Databases
10 Functional Database Testing
You can use the built-in functions or create your own user-defined functions. SQL has many built-in functions for
performing processing on string or numeric data.
Following is the list of all useful SQL built-in functions
● Aggregate functions: It perform a calculation on a set of values and return a single value
● Analytic functions: It compute an aggregate value based on a group of rows
● Ranking functions: It return a ranking value for each row in a partition
● Scalar functions: It operate on a single value and then return a single value. Scalar functions
can be used wherever an expression is valid
● Rowset functionS: It return an object that can be used like table references in an SQL
statement
Non-Functional Database Testing
Testing Databases
11 Non-Functional Database Testing
The Non-functional DB testing involves performing load testing, stress testing, checking minimum system
requirements to meet business specification, risk finding and performance optimization of database.
Following testing should be considered in non-functional testing types:
● Localization testing and
● Availability Testing Internationalization testing
● Baseline testing ● Performance testing
● Compatibility testing ● Recovery testing
● Compliance testing ● Reliability Testing
● Configuration Testing ● Resilience testing
● Documentation testing ● Security testing
● Endurance testing ● Scalability testing
● Interoperability Testing ● Usability testing
● Installation Testing ● Volume testing
● Load testing
● Stress testing
Testing Databases
11 Non-Functional Database Testing
a. Load testing
The process of putting demand on a software system or computing device and measuring its response. It is
performed to determine a system's behavior under both normal and anticipated peak load conditions.
The following types of configurations are a must for load testing:
● The most frequently used user transactions have the potential to impact the performance of all of the other
transactions if they are not efficient
● At least one non-editing user transaction should be included in the final test suite, so that performance of
such transactions can be differentiated from other more complex transactions
● The more important transactions that facilitate the core objectives of the system should be included, as
failure under load of these transactions has, by definition, the greatest impact
Testing Databases
11 Non-Functional Database Testing
● At least one editable transaction should be included so that performance of such transactions can be
differentiated from other transactions
● The observation of the optimum response time under huge number of virtual users for all the prospective
requirements
● The observation of the effective times for fetching of various records
Testing Databases
11 Non-Functional Database Testing
B. stress testing
It is used to test the stability & reliability of the system. This test mainly determines the system on its robustness
and error handling under extremely heavy load conditions. This helps in identifying breakdown points of the
system.
The following types of configurations are a must for stress testing:
● SQL extraction - We extract a representative sample of
actual SQL and DML from the production database
● SQL preparation - We create PL/SQL wrappers around
the SQL to pass-in valid values for the host variables, using data
from the application tables
Testing Databases
11 Non-Functional Database Testing
● load preparation - Benchmark factory allow us to submit up to 2000 transaction threads per TNS
client, however the practical limit is based on network bandwidth of the client, for example 60 users tends to
saturate a 100Mbit line. We will configure 2 clients, installing BF Agent software and TNS in order to set-up
a load of 200 transaction threads
● Test Execution - We then start the server data capture (vmstat, iostat) and take an AWR snapshot. After
a pre-designated interval we will step up the user load by 10 users at each step
● Post-hoc Analysis - We plot the overall response_time and correlate the drop-off with internal Oracle
metrics (e.g. wait events) and external server metrics (CPU enqueues, RAM swapping)
Writing Database Test Cases
Testing Databases
12 Writing Database Test Cases
There's no magic when it comes to writing a database test, you write them just like you
would any other type of test.
Database tests are typically a three-step process:
● Setup the test- You need to put your database into a known state before running tests against it
(WORKING DATABASE)
● Run the test- Using a database regression testing tool, run your database tests just like you would
run your application tests
● Check the results- You'll need to be able to do "table dumps" to obtain the current values in the
database so that you can compare them against the results which you expected
Popular Database Testing Tools
Testing Databases
13 Popular Database Testing Tools
THANKS!
If you have questions, please reach out to qe-bigdata@globant.com
