Database Testing

Introduction:

Database testing is a kind of software testing where you make sure that what you enter on web forms that information is being saved in to basked database as expected. Data constraints are maintained while doing migration or up gradation of your web application. You meta data gets intact. CRUD operations are working as required.

The most important thing which matters is the transaction flows between User Interface and back-end.

Layered process of Database testing

Database testing in the image clearly depicts the layered process between User Interface and Database layer.

Different types of layers:

  • User Interface Layer: is what is actually used by the end user for entering data.
  • Business Logic Layer: contains all the database related to the particular domain that keeps on changing with time.
  • Data Access Layer: is the most important and critical layer because it is the one that actually communicates with the database layer.

Last but not the least the Database layer which runs at the back end and about which the end user has no idea. They are hit the most the whole day during transactions getting queries and retrieving results in form of data

Database testing basically includes the following:

  • Checking the integrity of user interface(UI) data with Database Data
  • Checking whether any junk data is displaying in the user interface(UI) other than that stored in Database
  • Checking execution of stored procedures with the input values taken from the database tables
  • Checking the Data Migration
  • Data validity testing.
  • Data Integrity testing.
  • Performance related to the database.
  • Testing of Procedure, triggers, and functions.

ACID PROPERTY

  • Atomicity: All or Nothing rule i.e. if you are doing a transaction it is either to be executed completely or its not executed at all.
  • Consistency: It leads to the validity of a data. If a data is not valid the transaction reverts to its previous stage following all rules and constraint described for database
  • Isolation: It ensures multiple transactions being processed without interference at the same time securely and independently. Isolation, however, does not ensure the order of transaction that will be executed first or later.
  • Durability: It ensures that any transaction that is committed in a database is not lost.

 

How To Write Test Cases For Database Testing?

A software tester should prefer writing a separate set of test scenarios and test cases for database testing. That’s how you can make sure your test cases are independent and won’t mix with the UI related use cases.

Database testing is a type of grey-box testing. And if you don’t’ know, then note that it’s a mixture of both black box & white box testing methods. So the tester would need to know about the internal functioning of the application and also about the database structure used.

At times, an application could be using multiple databases so you should be aware of how they relate to each other. Also, make a data mapping sheet which should tell you the UI actions and the tables (and columns) getting affected when the action takes place. For example, sometimes it would intend adding a row in one table and update another row in a different table. While writing the test cases, you need to keep an eye on all of these actions to successfully test a database.

And as we’ve said earlier in this post that you need good SQL skills for testing databases. It will help you write efficient SQL queries that extract data from a table without returning thousands of rows from the database table.

Database Testing Guidelines.

You can use the below guidelines to prepare good test cases for database testing.

  • Get clarity on the functional requirements.
  • Make a list of all the tables used and find out-
    • Joins used between tables
    • Cursors used, triggers used
    • Stored procedures used
    • Input/Output parameters used.
  • Create test cases with multiple input data and try to cover all the paths.

Database Testing Checklist.

After writing the test cases, refer to the following checklist and see if any information is missing.

  • The tests are taking care of all the backend tables used for each requirement.
  • If application/database is using status flags, then tests should verify each of them.
  • Tests cover the triggers/stored procedures with combinations of input and expected output parameters.
  • Tables might have columns with default values, tests should check them too.

 

What Are The Possible Test Scenarios For Database Testing?

Here, we are listing down three types of scenarios possible for testing a database.

1. General Test Scenarios.

General tests should capture the following scenarios.

  • Name of the database.
  • Name of the log file.
  • Disk space allocation for databases.
  • Names of all tables, columns, and their types.
  • Null value checks.
  • Verify keys (primary/foreign), indexes, and data types of columns used.

2. Functional Test Scenarios.

  • Identify events causing the triggers
  • Functions inside stored procedures and possible combinations.
  • End to end data flow starting from the front end to the backend.

3. Non-Functional Test Scenarios.

  • Create test scripts for major features and use them for regression testing at regular intervals.
  • Write tests that track errors (OOM/deadlocks/exceptions) in log files.
  • Change data in backend tables and watch the effect on the frontend.
  • Insert invalid values from the backend and observe the effect.