Database Testing – Need and How to?

Why Database testing is required?

It is important to test the DATA ACCESS layer, since it is the interface between the front end and the DB.

The traditional testing always focused on the FRONT END/ GUI and the DATA ACCESS layer was ignored.

Some valid reasons necessitating the need for database testing-

–          In this age of competitive cut-throat business environment, Corporate acquisitions and mergers have paved the way for different applications with different/ multiple database backends. Hence it has become essential to test these applications running on different databases which may be integrated through some means.

–          With most financial data on the internet and its susceptibility to hacking and misuse; it has been a prime concern among business establishments. This scenario has initiated the need to have the major application functionality as regards security to the Database level which can be achieved by

  • Security at DB level to restrict access to resources
  • Securing the database and hence the data against hacks
  • Optimization carried out at Database level for maximum performance.

What are the common defects at the Database level?

–          Data corruption

  • Due to poor design (poor normalization techniques employed)
  • Unscalable design (not feasible to enhance and scale with later modifications)

–          Redundant data (duplicates and dealing with duplicates, invalid data)

–          Inconsistent data

  • Not following business rules (The database may contain data not satisfying any of the business rules indicated for the application and hence the business)

–          Incorrect validation

  • Business rules on DB and client may cause conflicts due to inconsistent usage. (Ensuring that the database level business rules are implemented correctly and consistent with the client side business rules).

Types of Database testing

1) Functional testing

  1. Verifying the stored procedures / functions to ensure that the right data goes into the DB on execution
    1. Verify the Stored procedures/ and functions for syntax, parameter passing, parameter types and the result.

2) Data Load testing

  1. Compatibility between source and target
  2. Check for junk and NULL values
  3. With random data using tools
  4. Executing Stored procedures
  5. Executing queries

3) Performance testing

  1. Executing queries (read/ write/ delete) for a single row
  2. For common queries
  3. For subqueries

4) Data Quality test

  1. Verifying default values
    1. Ensure that any column with the DEFAULT constraint contains only those values which have been permitted.
  2. Integrity checks
    1. Ensure that the Integrity constraints such as Primary Key, Foreign Key

5) Structural testing

  1. Table existence
  2. Correct column reflected in the table, queries
  3. Correct number of rows based on the execution of the queries
  4. Cols in the right order

6) Relationship testing

  1. Foreign key constraints
  2. Cascading deletes
  3. Master-child relationship

Published by Abhilash Gopi

Am a simple guy, loves to see the smiles on my friend's faces. So what are you waiting for? Be my friend, Guys n Gals.

2 thoughts on “Database Testing – Need and How to?

  1. There are number of ways to carry out Database testing.

    These are broadly classified as given below.

    1) Functional Test (Verify for stored procedures/ functions and is used to verify the appropriate data into an execution)
    2) DataLoad Testing (to verify for compatibility between source and target data sources. You can also verify for Stored procedure data, query data, and integrity constraints data).
    3) Performance testing (Do this by executing queries)
    4) Structural test (Look for tables, table entities like columns, rows and the right order of columns and rows)
    5) Relationship Verification (verify the constraints such as Foreign key constraint, Master child relationship, and most important Cascading deletes)
    6) Data Quality test (look for default values, integrity checks and verification of the data based on the integrity checks implemented within the database)


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: