Database Testing & its Importance

Database is the most valuable asset for every company because it is the most critical and the backbone of any software applications. Without an appropriate database, the application system will not be able to function as per user requirements. 

There are many organizations/scenarios where many different applications use the same database at the same time and these applications upgrade time to time as per market prerequisites therefore while changing the applications, nobody throws away the database however they migrate it very carefully. Because of such significance of database, it is important to have a good database management. As this could cost a company if any factor destroy the data consistency.

An application development includes programming code, configuration files, CSS Files, JQuery files, third party tools, SQL files, and other external systems.  Testing an application means testing each part separately and testing how all the parts communicate.

Traditionally all the data related testing is done at the GUI/Client Layer or Business Logic Layer. Apart from testing the various codes and attributes, testers would need to test one of the most vital part in a system-the database.

Database testing is a process working with data that's stored in the database. It involves some in depth knowledge of the given application and requires more defined plan of approach to test the data. It is essential when application has persistent storage of data, having centralized control of data, need to control of redundancy, need to control of consistency and integrity, having multiple user support, want sharing of data, data documentation needed, data independence, control of access is required and client has concerns for security, backup and recovery

Database testing, basically, includes the following:-

Ø  Data Accuracy & Validity (Field size validation)

Ø  Data Integrity (Check constraints, insert, delete, update)

Ø  Database Objects (Stored Procedures, Views, Tables)

Ø  Data Migration (Import, Export)

Ø  Data Transaction Consistency and Concurrency (States & Locks)

Ø  Performance related to database (Indices, number of triggers and procedures)

Ø  Security related to database - Data Accessing ( unauthorized access)

There are many database tools  available like MS-Access2010, MS SQL Server 2008 r2, Oracle 10g/11g, Oracle Financial, MySQL, PostgreSQL, DB2 etc. and every software application must be built using any of these database tools.

Database designing is done in three phases: Conceptual Designs -> Logical Design-> Physical Design

When the application is under execution, the end user mainly utilizes the ‘CRUD’ operations facilitated by the Database Tool.

Ø  C: Create – When user ‘Save’ any new transaction, ‘Create’ operation is performed.

Ø  R: Retrieve – When user ‘Search’ or ‘View’ any saved transaction, ‘Retrieve’ operation is performed.

Ø  U: Update – when user ‘Edit’ or ‘Modify’ an existing record, the ‘Update’ operation of DB is performed.

Ø  D: Delete – when user ‘Remove’ any record from the system, ‘Delete’ operation of DB is performed.

Now, the question comes in mind that what to test in database testing:

1. The mapping between different forms or screens of application and the Relations of its DB is should be accurate and is also according to design documents. For all CRUD operations, verify that respective tables and records are updated when user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.
2. ACID properties of DB Transactions refer to the ‘Atomicity’, ‘Consistency’, ‘Isolation’ and ‘Durability’. Proper testing of these four properties must be done during the DB testing activity. 
3. Data integrity refers to maintaining and assuring the accuracy and consistency of data over its entire life-cycle, and is a critical aspect to the design, implementation and usage of any system which stores, processes, or retrieves data.
4. To Ensure Accuracy of implemented Business Rules.

In order to test the DB properly and accurately, first of all a tester should have very good knowledge of SQL and specially DML (Data Manipulation Language) statements. Secondly, the tester should acquire good understanding of internal DB structure of AUT. If these two pre-requisites are fulfilled, then the tester is ready to test DB with complete confidence. 

A back end is the engine of any client/server system. If the back end malfunctions, it may cause system deadlock, data corruption, data loss and bad performance. 

Hence, the importance of database testing in software testing should not be ignored as it is the data which is visible to the user as it will help the tester identify the root cause of the problem apart from detecting the hidden bugs in the database.