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.
Database system is a complex architecture due to 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, a test engineers needs to be very attentive and thorough in his/her testing activities so as to avoid any kind of compromise to software quality.
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.
Database Testing Checklist:
1. 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 synchronised and updated.
Whether the data or values, fetched from the front-end is being correctly and successfully getting stored at back-end.
To check, if no data valued is present outside the table.
Whether data outside the table, could be modified or not.
Whether 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.
2. Stored Procedures:
Whether the execution of stored procedures or functions are outputting correct and reliable result sets.
Manual execution of the stored procedures, updates the database tables.
To ensure that correct and standard coding conventions is adopted and followed for the stored procedures.
Does input data is able to encompass all sort of loops and conditions.
To ensure proper and standard error and exception handing 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 blank or empty database.
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.
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 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.
Whether the primary key and the foreign key constraints is specified and created for each table or not.
Proper and valid referencing of 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 corresponding foreign key of other table.
To check out whether the correct transaction is being executed or not.
To ensure that the data is being committed on the successful execution of the transaction.
To check that if the data is rollback in the event of transaction failure along with the involvement of multiple variants of database.
To check whether the transactions are fulfilling the ACID (Atomicity, Consistency, Isolation, Durability) properties.
To ensure that all the transactions is being called upon & executed by the TRANSACTION Keyword.
To check the presence of clustered and non-clustered indexes to fulfil 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.
Database performance in terms of time taken, for the execution of lesser number of queries for small set of records.
Database performance in terms of time taken, for the execution of queries pertaining to 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.
Verifying & validating the access and no access to 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 comprises of evaluation of following features:
Apart from the above stated cases, some miscellaneous points may also be considered and included in the checklist such as
Database timely backup & recovery management and plan.
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.