Why should we test a Relational Database
First of all let us quickly understand as to what are the Relational Datatbases
Relational databases are tabular databases that are used to store target related data that can be easily reorganized and queried. They are used in many applications by millions of end users.
What are the key aspects of Database Testing?
1) Testing of the actual data.
2) Database integrity.
3) Functionality testing of database application.
The users may access, update, delete or append to the database. The modified database should be error free. To make the database error free and to deliver the quality product, regression testing of the database must be done. Regression testing involves retesting of database again and again to ensure that it is free of all errors.
It is a relatively new idea in the data community. Agile software developers take this approach to the application code.
Why should we test a RDBMS?
Extensive testing of RDBMS is done due to the following reasons:
1) Quality data is an important asset:According to a survey on the importance of quality data following conclusions were drawn:
a) 95.7% of people believed that data is a corporate asset.
b) 4.3% believed that data is not a corporate asset.
c) Out of the 95.7%, 40.3% had a test suite for data validation.
d) 31.6% discussed the importance of data.
2) Target related business functions are implemented in RDBMS: RDBMS should focus on mission-critical business functionality.
3) Present approaches of RDBMS testing are inefficient: Presently we develop a database by setting up database, write code to access the database, run code, and do SELECT operation to find the query results. Although visual inspection is a good start, it may help us to find problems but not prevent them.
4) Testing provides a concrete test suite to regression test an RDBMS: Database regression testing is the act of running the database test suite on a regular basis. This includes testing of actual data, database integrity, ensuring that database is not corrupted, schemas are correct as well as the functionality testing of database applications.
5) Verification of all modifications: Making changes to the database, may result in some serious errors like missing-data and regression testing may help us in detecting such missing – data.
What should be Tested in Relational Database?
Here we will be discussing both Black Box and White Box testing approaches on relational databases.
Black Box Testing involves the following:
1) I/O Validation: Regression testing will help us in validating incoming data-values; outgoing data -values from queues, stored- functions and views.
2) Error Handling: Regression testing of an RDBMS allows us to test quasi-nulls that is, empty strings are not allowed.
3) Validating Table Structure: We can validate the relationships between the rows in different tables. This is known as referential integrity. For example: if a row in an employee table references a row within the position table then that row should actually exist.
4) Testing interaction between SQL and other components such as scripts: Regression testing allows testing of interfaces between SQL and scripts by techniques such as parameter passing.
5) Testing of stored data: Data stored in the form of tables can be tested extensively by regression testing.
6) Testing of modified data: Updating the tables might introduce new errors which can be easily detected by regression testing.
White Box testing involves the following:
1) Testing of the entire structure of stored procedures and functions: Entire schema can be tested by regression testing. We can refactor our database tables into structures, which are more performant. The process of refactoring here means a small change to a database schema, which improves its design without changing its semantics. It is an evolutionary improvement of our database schema, which will support three things:
a) New needs of our customers.
b) Evolutionary software development.
c) Fix legacy database design problems.
2) Testing various stimulations: Regression testing allows unit testing of stored procedures, functions and triggers. The idea is that the testis automatically run via a test framework and success or failure is indicated via a Boolean flag.
3) Testing all views: Regression testing allows an extensive testing of all the three views viz, conceptual, logical and physical.
4) Testing of all data constraints: Regression testing allows testing of all data constraints like null values, handling single quote in a string field, handling comma in an integer field, handling wrong data types, large size value, large size string etc.
5) Improving the quality of data: Data quality may range from syntactic mistakes to undetectable dirty data. Data quality involves four C’s i.e., Correctness, Completeness, Comprehension and Consistency.
Correctness of Database: Regression testing provides a correct database by removing the following errors
a) Incorrect manipulation through the use of views.
b) Incorrect joins performed using non-key attributes.
c) Integrity constraints incorrectly used.
d) CHECK, UNIQUE and NULL constraints, which cause problems with data insertion, updations and deletions.