Database Testing Checklist.doc

Database Testing Checklist 1. Field size validation 2. Check constraints 3. Indexes are done or not (for performance related issues) 4. Store procedure 5. Field size defined in the application is matching with that in database 6. Events like insert, update, delete 7. Data integrity, data validity and data manipulation and update

Check list for database testing: 1. Data integrity. The complete data belonging to each entity should be stored in the database. Depending on the database design, the data may be present in a single table or multiple related tables. Parent-child relationships should exist in the data. There should not be any missing data. 2. Correctness of the data stored in the database. The data should be present in the correct table and correct field within the table. 3. Correctness and completeness of data migration (in case some or all the original data has come from another source). 4. Functionality and performance of user objects e.g. functions, procedures, triggers.

5. Database performance (query execution times, throughput etc.) and locking problems. You may identify the main queries (or procedures) that are used in the application and time them with sample data. Locking problems may become apparent when multiple inserts/ updates are being made to the same entity simultaneously. 6. Data security You may check if any data that should be encrypted e.g. passwords, credit card numbers is in plain text or not. The database should not have the default passwords. Even application accounts should have passwords that are complex and not easily guessed.

Additionally, you might need to review the following items: 1. Database fields (if they meet the specifications e.g. width, data type etc. as given in the design documentation/ data dictionary etc.) 2. Normalization level 3. Compliance of user objects code to coding guidelines

Check list for database testing: First understand what is there in a Database       

Tables Views Procedures Triggers Indexes Cursors Constraints 1. Verify Data stored in Tables (Data integrity) The complete data in the database is stored in the tables. Depending on the database design, the data may be present in a single table or multiple related tables. Verify the data in the tables is correct and also validate the data is stored correct with respect to Front End updated data. Verify the data is present in the correct table and correct field within the table. 2. Constraints Verify all the required constraints are created on the Tables, likes Primary key, Foreign key, rules…etc Verify valid references are done for foreign key. 3. Procedures: Verify the procedures will run correct and validate the data updates after the procedure is executed 4. Triggers:

Verify the triggers are executed for the DML transactions and verify the data updates after the trigger is fired.

5. Indexes: Verify the required indexes are created on the tables like Clustered index, non clustered index…etc 6. Transactions: Verify the transactions are done correct and check the data is committed only if the transaction is successfully executed else rollback 7. Security: Verify the security for database, like who is given access and what are the permissions 8. Performance Check the performance of the database in retrieving the data Correctness and completeness of data migration (in case some or all the original data has come from another source) Check the performance of query execution times, throughput etc. Verify the performance when multiple users access same data...Etc

Sample database checklist Features to be checked

Steps to be Followed

Check for Foreign key:

Foreign keys should not contain NULL values

Check for Primary key:

Data type of Primary key and the same key acting as foreign key should be same in two tables. 'Allow Null' condition should be checked at data base level for mandatory fields on UI Field length should be sufficient in size especially when field is going to get grown at run time (depending on master entries). LTRIM and RTRIM should be performed on data before inserting data into data base Field length of field in data base and on UI (if mentioned) should be same.

Check for mandatory fields on UI: Check for all fields: Check for all fields: Check for each field and UI element:

Check for Foreign key:

Foreign key should be marked as FK in table design so that field will not allow NULL to be entered.

Check for all fields:

Null' as value should not be allowed in data base, validations should be made so as to avoid user to enter such data. For Primary key 'Allow Null' condition should not be allowed. Data type of Primary key should be selected as per the project requirement (unique identifier or int as per the requirement) Allow Null condition should be checked in Stored Procedures as well as data base level. Data types of each field should be selected taking into consideration the values it is going to store.(like some times tinyint or smallint suffice instead of defining field as int) While allowing NULL values on particular field verify that that field is not mandatory. Field length specified on UI should be same with the field length specified in table design to store same element from UI into database. Suitable option should be chosen from separate mapping table and foreign key according to the requirement. Verify that log events are added in database for all login events.

Check for Primary key: Check for Primary key: Check for all fields: Check for all fields:

Check for all fields: Check for each field and UI element: Check for All tables which are associated with some other table: Enable logging of all user login events