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
- Verifying the stored procedures / functions to ensure that the right data goes into the DB on execution
- Verify the Stored procedures/ and functions for syntax, parameter passing, parameter types and the result.
2) Data Load testing
- Compatibility between source and target
- Check for junk and NULL values
- With random data using tools
- Executing Stored procedures
- Executing queries
3) Performance testing
- Executing queries (read/ write/ delete) for a single row
- For common queries
- For subqueries
4) Data Quality test
- Verifying default values
- Ensure that any column with the DEFAULT constraint contains only those values which have been permitted.
- Integrity checks
- Ensure that the Integrity constraints such as Primary Key, Foreign Key
5) Structural testing
- Table existence
- Correct column reflected in the table, queries
- Correct number of rows based on the execution of the queries
- Cols in the right order
6) Relationship testing
- Foreign key constraints
- Cascading deletes
- Master-child relationship