Database Testing

Today database becomes as engine to many of the enterprise applications. System would be affected badly if the transactions and queries produced the false conditions. Due to lack of database testing, applications may go to dead lock, data corruption and data loss conditions. These type of issues would take more time to identify and fix. Database testing includes verifying stored procedures, table indexes, exceptions, schemas and compatibility.

Different types of Database Testing

• Structural testing
• Functional testing
• Boundary testing
• Stress Testing

Few Sample Scenarios

• Creating an user account from GUI - How would you ensure the details are stored into table correctly?
• Executing stored procedures in different conditions like valid and invalid conditions.
• Varying data definitions - The data type and length for a particular attribute may vary in tables though the semantic definitions are same. Example: Account number declared as Number ( 9 ) in one table and the same as varchar2( 11 ) in another table.
• Varying data codes and values - The data representation of the same attribute may vary with and across tables. Example: Yes or No may be represented as "Y", "y", "N", "n", "1", "0".
• Misuse of integrity constraints - When referential integrity constrains are misused, foreign key values may left "dangling". Example: Employee record deleted but dependent records not deleted.
• Nulls - Null may be ignored when joining tables or doing searches on the column.
• Inaccessible data - Inaccessible data due to missing or reduntant unique identifier value. Example: Uniqueness not enforced.
• Incorrect data values - Data that is misspelled or inaccurately recorded. Example: Indra Nagar - Indra ngr.
• Inappropriate use of views - Data is updated incorrectly through views. Example: Data is properly fetched from the database but first record or last record is not displayed

SQL Basics

I thought to write few posts about SQL and Database testing. This post is the result of that interest.

SQL - Structured Query Language. SQL is used to interact with databases. SQL is a non-procedural language and has few standards. SQL is standardized by the American National Standards Institute (ANSI).

Rules for writing SQL statements

• It is not case sensitive.
• It cannot be abbreviated or split across lines.
• It can be on one or more lines.
• Indents are used to enhance readability.
• Clauses are usually placed on separate lines.

SQL's Components / Sub Languages
SQL consists of few components and given below:

• DDL - Data Definition Language - Create, Alter, Drop, Truncate - For creating, altering and dropping the database objects like tables, viewes, indexes, synonyms,sequences and contraints.
• DML - Data Manipulation Language - Insert, Update, Delete - For reading and updating data.
• DRL - Data Retrieval Language - SELECT - For querying the data. SELECT has many clauses.
• TCL - Transaction Control Language - Commit, Rollback, SavePoint - These allow to group one or more DML statements into units of work or transactions. All statements in a transaction either succeed or fail as a single group.
• DCL - Data Control Language - Grant, Revoke - For managing database security, by creating user ids and assigning or removing privileges from them.