Database Testing Tutorial for Beginners
Database Testing Tutorial for Beginners, What is SQL Database Testing?, How to conduct SQL Database testing?, Manual Database testing, and Automated database testing.
1. What is Database Testing?
Database Testing is a type of Software Testing that checks the data validity, data integrity, database schema, tables, procedures, triggers, and performance of the Database under test.
2. Database Testing Attributes:
i. Transactions:
Transactions means the access and retrieve of the data. Hence in order during the transaction processes the ACID (Atomicity, Consistency, Isolation, and Durability) properties should be followed.
ii. Database Schema:
It is the design or the structure about the organization of the data in the database.
iii. Triggers:
When a certain event occurs in a certain table, a trigger is auto-instructed to be executed.
iv. Procedures:
It is the collection of the statements or functions governing the transactions in the database.
3. How to conduct Database Testing?
Database Testing is done in two ways using SQL (Structured Query Language), one is Manual Testing and another is Automated Testing. We can test databases manually or using any functional test tool like Selenium or UFT.
4. Database Testing Types
Database testing can be categorized into three categories:
i. Structural Database Testing:
Structural database testing involves verifying components of database, which are not exposed to end users. It involves all the components of repository, which are used to store the data and are not changed by the end users.
Database administrators with good command over SQL stored procedures and other concepts normally perform this testing.
Structural Testing focus on:
Schema or Mapping Testing, Stored Procedures and Views Testing, Trigger Testing, Tables and Column testing, and Database Server Check.
ii. Functional Testing:
Functional testing is performed keeping in mind an end-user point of view; whether the required transactions and operations run by the end-users meet the business specifications.
It involves checking functionality of database, and most common type of Functional testing are White box and black box testing.
iii. Nonfunctional Testing:
Nonfunctional testing involves performing load testing, stress testing, checking minimum system requirements to meet business specification, risk finding and performance optimization of database.
5. How to Test Database as Manual Tester
i. Prepare the Environment
Depending on the type of the database selected for the project e.g. like MySQL or MSSQL, you have to get access credentials for the database.
You need to know SQL queries in case if you choose to execute the queries.
If you’re using NoSQL (Like MongoDB or CouchDB) as database for the project then setting up the environment is different to than popular database like SQLite or MySQL.
Make sure you get clean database without any prior test data.
ii. Run a Test
Create queries for insert, update, delete and modify. Make sure you use the test data along with the specific test cases to add your data.
Use both negative and positive test cases and adjust the queries accordingly to test the database.
iii. Check Test Result
Once you start executing the test, you have to log the test result into the test log.
Check if the results are logged as soon as the test executed. Check if the tests are resulted in any warning or error, and note the respective values down.
iv. Verify Test Results
Check if the logged result has any errors and verify the tests. Check if all the tests produced the results as per the exepcted results. If not, file the report with bug in next step.
v. Defect Reporting
After verification of the test, report the defects found in the verification of the tests.
Note: In order to test the SQL database you need to have knowledge of sequel queries. You need to know how to create database and table, insert entry, modify table, drop table, delete entry, update entry.
To test NoSQL database the queries are lot different and require you to know JSON and key & index, along with the concept of key-value pair in the database.
Database Testing Tutorial for Beginners,
6. Database Testing using Selenium
Selenium Webdriver is limited to Testing your applications using Browser.
To use Selenium Webdriver for Database Verification you need to use the JDBC (Java Database Connectivity).
JDBC is a SQL level API that allows you to execute SQL statements. It is responsible for the connectivity between the Java Programming language and a wide range of databases.
you need to the following steps in order to test your Database using Selenium:
i. Make a connection to the Database
In order to make a connection to the database the syntax is,
DriverManager.getConnection(URL, “userid”, “password”)
And the code to create connection looks like
Connection con = DriverManager.getConnection(dbUrl,username,password);
You also need to load the JDBC Driver using the code
Class.forName(“com.mysql.jdbc.Driver”);
ii. Send Queries to the Database
Once connection is made, you need to execute queries. You can use the Statement Object to send queries.
Statement stmt = con.createStatement();
Once the statement object is created use the executeQuery method to execute the SQL queries
stmt.executeQuery(select * from employee;);
iii. Process the results
Results from the executed query are stored in the ResultSet Object. Java provides loads of advance methods to process the results.
7. Database aspects to be Validated:
i. Data Mapping
In software systems, data often travels from the UI (User Interface) to the backend DB and vice versa.
Check whether the fields in the UI/frontend forms are mapped consistently with the corresponding fields in the DB table.
Whenever a certain action is performed at the front end of an application, a corresponding CRUD (Create, Retrieve, Update and Delete) action gets invoked at the back end. A tester will have to check if the right action is invoked and whether the invoked action in itself is successful or not.
ii. ACID Properties Validation
Atomicity, Consistency, Isolation, and Durability. Every transaction a DB performs has to adhere to these four properties.
iii. Data Integrity
The term data integrity refers to the accuracy and consistency of data. Verify CRUD (Create, Retrieve, Update, Delete) Operations
Any database operation performed by the end-user is always one of the following operations,
C: Create – When user ‘Save’ any new transaction, ‘Create’ operation is performed.
R: Retrieve – When user ‘Search’ or ‘View’ any saved transaction, ‘Retrieve’ operation is performed.
U: Update – When user ‘Edit’ or ‘Modify’ an existing record, the ‘Update’ operation of DB is performed.
D: Delete – When a user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.
iv. Business Rule Conformity
Validate the Database complicated components like relational constraints, triggers, stored procedures, etc.
8. Database Testing Checklist
i. Transactions
When testing Transactions it is important to make sure that they satisfy the ACID properties.
ii. Database Schemas
A Database Schema is nothing more than a formal definition of how the data is going to be organized inside a Database.
iii. Triggers
When a certain event takes place on a certain table, a piece of code (a trigger) can be auto-instructed to be executed.
iv. Stored Procedures
Stored Procedures are more or less similar to user-defined functions. These can be invoked by Call Procedure/Execute Procedure statements and the output is usually in the form of result sets.
v. Field Constraints
Check The Default value, Unique value, and Foreign key for certain fields. It is part of business rule validation.
9. Database Testing Scenarios
i. Check if correct data is getting saved in the database upon successful page submit.
ii. Check values for columns which are not accepting null values
iii. Check for data integrity. Data should be stored in single or multiple tables based on design
iv) Index names should be given as per the standards, e.g. IND_<Tablename>_<ColumnName>
v. Tables should have a primary key column
vi. Table columns should have description information available.
vii. Required table indexes should be created
viii. Check if data is committed to the database only when the operation is completed
ix. Data should be rolled back in case of failed transactions
x. Database logical names should be given according to database name.
(it is not standard but helpful for DB maintenance)
xi. Stored procedures should not be named with prefix “sp_.”
xiii. Check values for table audit columns (like created date, created by, updated date, updated by, isdeleted, deleteddate, deletedby, etc.) are populated properly
xiv. Check if input data is not truncated while saving. Field length shown to the user on a page and in database schema should be the same
xv. Check numeric fields with minimum, maximum, and float values
xvi. Check numeric fields with negative values (for both acceptance and non-acceptance)
xvii.Check if radio button and dropdown list options are saved correctly in the database
xvii.Check if database fields are designed with the correct data type and data length
xix. Check if all table constraints like a Primary key, Foreign key, etc. are implemented correctly
xx. Test stored procedures and triggers with sample input data
xxi. Input field leading and trailing spaces should be truncated before committing data to the database
xxii. Null values should not be allowed for the Primary key column
xiii. Verify that data inserted from UI is reflecting properly in the appropriate table.
Database Testing Tutorial for Beginners,
