Database Testing Checklist

Testing the backend system of a software application or system is as much important as testing the front-end features. Database testing is a unique testing methodology which focuses on the database system, as database i.e. data forms a major and integral part of the backend system.

Why is Database Testing Important?

If you are well aware of the importance of database testing, then jump to the checklist. But if you are still not sure about including database testing in your testing life cycle, then the following discussion is crucial for you.

The database system is a complex architecture due to the involvement of multiple relationships amongst the data and their attributes. Further, the inclusion of core elements such as tables, views, procedures, constraints, index, triggers etc. increases the complexity of the database.

Thus, by performing database testing the test engineer can ensure the credibility of these components, as well as, improve the quality of the product. Additionally, with its assistance, they can ensure the accuracy of the values retrieved or stored in the database.

However, the test engineer needs to be very attentive and thorough in his/her testing activities so as to avoid any kind of compromise to software quality. To ensure this, here, we are providing the checklist for testing the database comprising certain and essential activities, sufficient to cover each and every aspect and domain of the database.

CHECKLIST

While performing database testing, it is important to cover the following aspects and components, to guarantee the quality of the software product.

Data Integrity:

  • To check whether the data is well arranged and logically placed in the database.
  • To evaluate the consistency of the data.
  • To ensure that the data values are being placed correctly in their respective tables or columns.
  • Verifying and validating the correctness & integrity of the data, stored in the database.
  • To locate and get rid of redundant and useless data.
  • Whether data reflecting at front-end and stored at back-end is correctly synchronized and updated.
  • Whether the data or values, fetched from the front-end is being correctly and successfully getting stored at the back-end.
  • To check, if no data valued is present outside the table.
  • Whether data outside the table could be modified or not.
  • Whether the database is able to store or export blank or null value.
  • Checking the compatibility of the data with the software and hardware, especially the outdated or obsolete one.

Stored Procedures

  • Whether the execution of stored procedures or functions is outputting correct and reliable result sets.
  • Manual execution of the stored procedures updates the database tables.
  • To ensure that correct and standard coding conventions are adopted and followed for the stored procedures.
  • Does input data able to encompass all sort of loops and conditions?
  • To ensure proper and standard error and exception handling mechanism.
  • Verifying and validating the attributes and parameters associated with each procedure.
  • Evaluating the working or execution of all stored procedures in the presence of a blank or empty database.

Triggers

  • Similar to stored procedures, adoption, and implementation of correct and standing conventions for triggers, by the developers, is being evaluated.
  • To ensure that the execution of a trigger updates the data in the database, successfully.
  • If the triggers are being executed for the DML transaction.
  • Checking the execution of a trigger in the event of addition or deletion or update in the data.
  • To check whether the execution of a stored procedure is followed by the firing of a trigger.

Field Validation

  • Whether the database system is allowing the entry and storage of null data value or not.
  • To ensure the appropriate and sufficient length of each field to import and accommodate the respective data value of varying range.
  • Verifying and validating the data type for each field against specified and given specifications.
  • To check whether all identical fields have similar name throughout the database and tables.
  • To locate any computed field(if any) in the database and tables.

Constraints

  • Whether the primary key and the foreign key constraints are specified and created for each table or not.
  • Proper and valid referencing of the foreign key between the database table has been done or not.
  • Whether the null value is being accepted as a valid input both for the primary and the foreign key.
  • To ensure primary key data type of a table is same as to that of a corresponding foreign key of another table.

Transactions

  • To check out whether the correct transaction is being executed or not.
  • To ensure that the data is being committed to the successful execution of the transaction.
  • To check that if the data rollbacks in the event of transaction failure along with the involvement of multiple variants of the database.
  • To check whether the transactions are fulfilling the ACID (Atomicity, Consistency, Isolation, Durability) properties.
  • To ensure that all the transactions are being called upon & executed by the TRANSACTION Keyword.

Indexes

  • To check the presence of clustered and non-clustered indexes to fulfill the necessary need for a given table as per the business requirements.
  • To evaluate the size and length of the indexes.
  • Naming conventions for the indexes.

Performance

  • Database performance in terms of time taken, for the execution of a lesser number of queries for a small set of records.
  • Database performance in terms of time taken, for the execution of queries pertaining to a comparatively large set of records.
  • Performance of database in the event of simultaneous and concurrent access to data by multiple users.
  • To verify and validate the normalization of the database.
  • Time in retrieving or updating the data or records.

Security

  • Verifying & validating the access and no access to the database by authorized and non-authorized users, respectively.
  • Verifying & validating the different permission granted to each different role, assigned for the database.
  • Other security aspects comprise of evaluation of following features:
  1. Authentication
  2. Confidentiality
  3. Availability
  4. Integrity
  5. Resilience

Miscellaneous

Apart from the above-stated cases, some miscellaneous points may also be considered and included in the checklist such as

  • Data Redundancy
  • Data Duplication
  • Data Migration
  • Database timely backup & recovery management and plan.

Conclusion:

In light of the above, it may be concluded that database testing checklist not only acts a guide or manual to test engineers, in considering and covering each and every essential area of the database under the test but also reminds them about the things got tested and the things yet to be tested throughout the testing process.


Originally published at www.professionalqa.com.