Database Testing — Part 01

Database Testing

Hello readers,

Today, as we start the new year 2023, we are going to discuss the important topic called database testing, why database testing is important, how to test it, and much more will discuss in this blog.

In software development, processing and storing data in different states reflect the business rules an application is built on. The heart and soul of any software application is the data that is persisted in databases for retrieval and further processing. The database is maintained using a DBMS like MySQL, Oracle, or any other DBMS to store all its data. Selenium Web Driver is used for testing web applications and operations are performed like submitting information, retrieving data, and validating them. When there is a need of getting data from the database in selenium scripts, we may use APIs that help to interact with databases like JDBC.

What mean by Database

Data: Data is information of an entity. Data is stored in table format 
      as rows and columns.  
      Raw uninterpreted chunks of information

Base: Storage area
1. A storage area for raw interpreted chunks of information, presented in 
   tabular formats.
2. A storage area where groups of tables that contain data about entities 
   are stored.An entity is anything, a person, a place, an event etc. 
   that has an information. 

Table: A logical grouping and storage of data about an entity organized 
       by rows and columns.

Database testing involves checking things such as the schema, tables, or triggers. The process also checks data integrity and consistency. To do this, complex queries are created to load or stress test the database. Doing this helps to test the responsiveness of the system, and how well it can stand up to different scenarios.

Database testing is a layered process, as the testing usually covers several different areas. When doing a complete database test, you need to test the user interface layer, the business layer, the data access layer, and the database itself.

How to perform Database Testing

Now, we know what database means but as testers we should know how the database works in our project, and what type of schema we are implementing in the project.

While performing testing of a database, we should be thoroughly clear about the concepts of DBMS and RDBMS. DBMS stands for Data Base Management System and RDBMS stands for Relational Data Base Management System.

To understand more about DBMS and RDBMS please checkout the below chart.

Now, after understanding what mean by the database we should know how to create test cases for database testing.

Create test cases to perform database testing

Firstly, we need a schema document to create test cases for database testing.

Database Schema

A database schema is a formal description of the structure or organization of a particular database. The term “database schema” is most commonly used in reference to relational databases, i.e. databases that organize information in tables and use the SQL query language. Non-relational (i.e. “NoSQL”) databases come in several different formats and are not generally considered to have a “schema” in the same way that relational databases do (although they do have an underlying structure).

Relationship between tables in the database

Whenever you start database testing, you should perform the following tests before executing any queries.

1. Check table presence in databse schema.
2. Check table name conventions.
3. Check number of columns in a table.
4. Check column name, data type, size of columns.
5. Check null fields in a table.
6. Check column keys in the table.

Now, we are going to understand how to perform database testing on SQL databases with the help of queries.

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language. A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database.

Now, we are going to perform all the queries on the customer table to understand how to perform database testing.

Test cases to test database.

Now, connect to your database to perform the queries on every table.

Image 01

In the above image, we are checking all tables from the database so we need to checkout to the database to check tables from the database.

The USE keyword is used for checkout to the database and the SHOW keyword is used for checking tables from the respective database.

Now, we will execute all the test cases from the above test case image.

To check the number of columns in the database we need to check out into the database then we need to fetch the count from the table for that we need to execute the below command.

Test Case 3

Test Case 4

A database schema design can verify that our tests are failing or passing defined criteria.

Testcase 5 and 6

We have checked our database table with the help of some queries and executed some tests.

We will move forward with this topic in the next blog so keep reading and share your feedback and let me know your thoughts on database testing.

Here to make the community stronger by sharing our knowledge. Follow me and my team to stay updated on the latest and greatest in the web & mobile tech world.

Thank you.