As we know Database is a precious asset and acts as a backbone for all enterprises where Data is electronically managed. No organisation can imagine the application systems to function as per user requirements without a suitable database.

Database testing mainly deals with identifying errors in the database like database system crashes, broken inserts, updates or deletions so as to eliminate them before deliver application to client. As we know that Database testing includes harmonizing of UI data along with the backend data records. This includes internal process like views, joins, triggers execution, data imports and exports, testing the procedures, indexing, locks checking etc., storage like assembly and DBMS like Oracle, SQL Server, MYSQL, etc.

Just like any other testing, Database testing follows the general test processes:

• Prepare the test environment
• Execute the test
• Prove test results
• Validate according to the expected results
• Report the findings to the respective stakeholders

The most important feature for database testing is the SELECT statement, which fetches data rows from certain tables that fulfills an agreed set of benchmarks. Apart from SELECT, SQL has three major types of commands:

• DDL (Data Definition Language): Uses CREATE, RENAME, MODIFY, REMOVE and TRUNCATE to handle tables (and indexes).Example: CREATE TABLE, ALTER TABLE, and DROP TABLE to manage the test tables.
• DML (Data Manipulation Language): Includes statements to add, update or delete records/statements. Example: INSERT, UPDATE or DELETE to manage your test data.
• DCL (Data Control Language): Deals with giving authorization rights to users for manipulation and access to the data. Example: Some other commands to view table structures, indexes, constraints, column definitions, and store procedures.

A tester may possibly perform Database testing using the following categories:

1. Structural Testing

• Schema Testing: It is also referred to as mapping testing, used to ensure that if there is any change in existing table structure or deletion in it, then all the Stored Procedures and Views of the table should be compatible with the particular change. A testing to ensure compatibility of the front end and back end.
• Database Table/Column Testing: It checks compatibility of the data type and field length of the backend database columns with that of the front end of the application. It also examines for any unused/unmapped database tables/columns. It performs various validations for database, column testing, keys and indexes testing.
• Stored Procedure Testing: Perform various validations for the stored procedures like whether the development team has adopted the requisite coding standards and conventions, exception and error handling for the stored procedures and modules of the application under test. It also validates the overall integration of the procedure modules according to the client requirements.
• Trigger Testing: It verifies performance process and expected results of trigger execution.
• Database server validations: It checks the database server configurations, authorization and transactions as specified by the business requirements.

2. Functional Testing: It is held by the specifications required to ensure most of those transactions and operations executed by the end users are consistent or not. It also includes checking data integrity and consistency, login and user security among the other dimensions.

3. Non-Functional Testing: In the context of database testing, non-functional can be considered as required by the business requirements. It can be load, stress, usability, security, and compatibility testing and many more. Mainly non-functional testing is done in database to identify risk quantification and determine the system response time requirements under the required levels of load.

Before ending my article, I would like to summarize steps about how to write Test cases for database testing:

• Initially, a tester has to understand the functional and non-functional requirements of the application thoroughly.
• After going through the requirement documents, write the test objectives.
• Next we need to find out the Tables created/used, joint between tables, cursors, triggers, stored procedures, input parameters, output parameters (if any) for specific requirement development.
• Then write the database test cases with queries and different input values used at the backend while testing in order to get the desired results. Example: field size validation, check constraints, indexes for performance related issues, stored procedure etc. defined in the application are in line with those in the database records.
• Also we need to ensure whether the stored procedure test follows the standard nomenclature for comments, updated by, check the given procedure calling name, parameters, and expected result for input parameters and others etc. Run the procedure with client database tools like MySQL, TOAD or Query Analyser and re-test it with different parameters. Review the results against expected values.

While doing Database testing, all data including the metadata as well as functional and Non-functional data needs to be validated as per the defined mapping by the software requirement specification documents. Testers also need to make a practice to validate the output data by using both manual Testing procedures as well as with automation tool support (if requisite by client). Effective and efficient Database testing provides enduring functional constancy to the entire application therefore it is essential to put in thoroughness behind it.
Image Courtesy: www.canstockphoto.comAs we know Database is a precious asset and acts as a backbone for all enterprises where Data is electronically managed. No organisation can imagine the application systems to function as per user requirements without a suitable database.
Database testing mainly deals with identifying errors in the database like database system crashes, broken inserts, updates or deletions so as to eliminate them before deliver application to client. As we know that Database testing includes harmonizing of UI data along with the backend data records. This includes internal process like views, joins, triggers execution, data imports and exports, testing the procedures, indexing, locks checking etc., storage like assembly and DBMS like Oracle, SQL Server, MYSQL, etc.
Just like any other testing, Database testing follows the general test processes:

1. Prepare the test environment
2. Execute the test
3. Prove test results
4. Validate according to the expected results
5. Report the findings to the respective stakeholders

The most important feature for database testing is the SELECT statement, which fetches data rows from certain tables that fulfils an agreed set of benchmarks. Apart from SELECT, SQL has three major types of commands:

1. DDL (Data Definition Language): Uses CREATE, RENAME, MODIFY, REMOVE and TRUNCATE to handle tables (and indexes).Example: CREATE TABLE, ALTER TABLE, and DROP TABLE to manage the test tables.
2. DML (Data Manipulation Language): Includes statements to add, update or delete records/statements. Example: INSERT, UPDATE or DELETE to manage your test data.
3. DCL (Data Control Language): Deals with giving authorization rights to users for manipulation and access to the data. Example: Some other commands to view table structures, indexes, constraints, column definitions, and store procedures.

A tester may possibly perform Database testing using following categories:
1. Structural Testing

• • Schema Testing: It is also referred as mapping testing, used to ensure that if there is any change in existing table structure or deletion in it, then all the Stored Procedures and Views of the table should be compatible with the particular change. A testing to ensure compatibility of the front end and back end.
  • Database Table/Column Testing: It checks compatibility of the data type and field length of the backend database columns with that of the front end of the application. It also examines for any unused/unmapped database tables/columns. It performs various validations for database, column testing, keys and indexes testing.
  • Stored Procedure Testing: Perform various validations for the stored procedures like whether the development team has adopted the requisite coding standards and conventions, exception and error handling for the stored procedures and modules of the application under test. It also validates the overall integration of the procedure modules according to the client requirements.
  • Trigger Testing: It verifies performance process and expected results of trigger execution.
  • Database server validations: It checks the database server configurations, authorization and transactions as specified by the business requirements.

2. Functional Testing: It is held by the specifications required to ensure most of those transactions and operations executed by the end users are consistent or not. It also includes checking data integrity and consistency, login and user security among the other dimensions.
3. Non-Functional Testing: In the context of database testing, non-functional can be considered as required by the business requirements. It can be load, stress, usability, security, and compatibility testing and many more. Mainly non-functional testing is done in database to identify risk quantification and determine the system response time requirements under the required levels of load.
Before ending my article, I would like to summarize steps about how to write Test cases for database testing:

1. Initially, a tester has to understand the functional and non-functional requirements of the application thoroughly.
2. After going through the requirement documents, write the test objectives.
3. Next we need to find out the Tables created/used, joint between tables, cursors, triggers, stored procedures, input parameters, output parameters (if any) for specific requirement development.
4. Then write the database test cases with queries and different input values used at the backend while testing in order to get the desired results. Example: field size validation, check constraints, indexes for performance related issues, stored procedure etc. defined in the application are in line with those in the database records.
5. Also we need to ensure whether the stored procedure test follows the standard nomenclature for comments, updated by, check the given procedure calling name, parameters, and expected result for input parameters and others etc. Run the procedure with client database tools like MySQL, TOAD or Query Analyser and re-test it with different parameters. Review the results against expected values.

While doing Database testing, all data including the metadata as well as functional and Non-functional data needs to be validated as per the defined mapping by the software requirement specification documents. Testers also need to make a practice to validate the output data by using both manual Testing procedures as well as with automation tool support (if requisite by client). Effective and efficient Database testing provides enduring functional constancy to the entire application therefore it is essential to put in thoroughness behind it.
Image Courtesy: www.canstockphoto.com[:]