Pdf on database testing concepts

This tutorial on database is an introductory tutorial which discusses about all the fundamentals of Database testing. This chapter on database tetsing has been created for all those readers who want to attain basic knowledge of Database testing.

It is mainly going to be useful for all those software testing professionals who are required to test the applications in order to find out how they will be affecting the database performance. We pre assume that the readers of this chapter should have hands-on experience in handling a database using certain SQL queries. In addition to this, it is going to help readers if they have any elementary knowledge of basic database concepts. The goal of Database Regression testing is to identify any issues that might occur due to changes in the database metadata, procedures or system upgrades.

Automating the Database testing is the key for regression testing of the Database particularly more so in an agile development environment. Organizing test cases into test plans or test suites and executing them automatically as and when needed can reduce the time and effort needed to perform the regression testing.

Automating Database testing can also eliminate any human errors while performing manual checks. Track changes to table metadata such as adding or dropping of columns, new constraints and tables.

Often database metadata changes are not communicated to the QA and Development teams resulting in Application failures. Maintaining a history of DDL changes will help narrow down the tests that need to be run. Example 1: The length of a comments column was increased in the application UI but the corresponding increase was not made in the database table. Example 2: One of the index in the database was dropped accidentally which resulted in application performance issues.

This helps ensure that the QA and development teams are aware of the changes to table metadata. ETL Validator also has the capability to Baseline and Compare the output of database procedures so that any changes in the output can be validated. When an application is tested from the UI, the database procedures and tables are accessed and tested as well.

The goal of integration testing is to validate that the database tables are getting populated with the expected data based on input provided in the application.

Example: One of the optional postal address field in the UI was not getting saved to the database because of an application defect. The issue was only identified after the application went live when some of the mails were returned. The focus of this test is to validate that the data is not getting truncated while being stored in the database:. Any user comments longer than characters were getting truncated by the database procedure during the insert. Database performance is an important aspect of the application performance testing.

Often development environments do not have enough data for performance testing of the database. This could be because the project has just started and the database only has small amount of test data or production data has PII information which cannot be loaded into the test database without scrubbing. The application can behave differently with different volumes of data.

Setting up realistic test data using one of the following strategies is a key to performance testing :. Example 1: An update might perform well when the data is small but it can run very slow with larger data volume because of a missing index. Example 2: The application runs fine with a single user but performs badly when multiple users are using it due to a connection pool issue. Database Testing. Challenges in Database Testing Databases are key component of most software applications.

Database Testing Categories. Data Type Check Verify that the table and column data type definitions are as per the data model design specifications. Data Length Check Verify that the length of database columns are as per the data model design specifications. Verify that the unique key and foreign key columns are indexed as per the requirement. Verify that the table was named according to the table naming convention.

Metadata Naming Standards Check Verify that the names of the database metadata such as tables, columns, indexes are as per the naming standards. Metadata Check Across Environments Compare table and column metadata across environments to ensure that changes have been migrated appropriately. Track changes to Table metadata over a period of time. This helps ensure that the QA and development teams are aware of the changes to table metadata in both Source and Target systems.

Compare table metadata across environments to ensure that metadata changes have been migrated properly to the test and production environments. Compare column data types between source and target environments. Validate Reference data between spreadsheet and database or across environments.

Duplicate Data Checks Look for duplicate rows with same unique key column or a unique combination of columns as per business requirement.

Count of records with null foreign key values in the child table. Count of invalid foreign key values in the child table that do not have a corresponding primary key in the parent table. Data Rules Test Plan: Define data rules and execute them on a periodic basis to check for data that violates them. Verify that Data confirms to reference Data Standards Data model standards dictate that the values in certain columns should adhere to a values in a domain.

Example: Compare Country Codes between development, test and production environments. Track Reference Data Changes Baseline reference data and compare it with the latest reference data so that the changes can be validated. Baseline reference data and compare with the latest copy to track changes to reference data. Define data rules to verify that the data conform to the domain values. The steps to be followed are listed below: Review Design: Review the database procedures to understand the code and design specifications to come up with the Test Cases.

Setup Test Data: Insert test data into the database tables for the test. Execute Test: Run the database procedure passing the appropriate input parameters. Compare data in tables and procedure output with the expected results.