Thursday, April 21, 2011

Database Testing - Conceptual Understanding



Have you ever think about what happens when you deposit any amount in your bank account?
when you deposit amount, an event occurs, a program is executed and changes the database values according to event occurred in a corresponding way. It should not only add deposit to one table and not affect other relational tables.
Basic theory is ACID, basic properties of a database transaction:

Automaticity:
An action must be either completed or aborted. The transaction cannot be partially successful. It must do ALL what it supposed to do or NOTHING. If one part of transaction fails entire the database transaction must be aborted.
If the transaction successfully completes it is said to commit. The system is responsible for ensuring that all changes to the database have been saved. If the transaction does not successfully complete, it is said to abort. The system is responsible for undoing, or rolling back, all changes the transaction has made.

Consistency:
The transaction takes the resources from one consistent state to another. Application developer is also responsible who must make sure that all known integrity constraints are enforced by the application level as well. All integrity check, constraints, Rules, and triggers must be applied during transaction.

Isolation:
Transactions execute in sequence. Each one starts after the previous one completes. Execution of one transaction is not affected by the operations of another since they do not overlap in time. The execution of each transaction is isolated from all others. (Serial execution is inadequate from a performance perspective.)

Durability:
Changes made by the committed transaction are permanent, data should be changed permanently.

A tester needs to think about capacity of database; size while deploying the QA box, how much data is there and how system is behaving with different size of data. Data mapping and Default data integrity are also important. (consistency). Now a days, Big business Software developments use OOP (Object Oriented Programming). Front end JAVA, C#, and back end Oracle, SQL Server. Another important aspect of RDBMS and OOP is related ‘object’. Mapping Objects to the relational DB based on relationship is one of the major tedious, but sensitive tasks. (Just a gist of data mapping, I am stopping this topic right here, because data mapping is a heavy weight topic and more relevant to talk within ETL).
Compatibility is another big issue. Tester is not the one to decide about what is compatible with what; it should be more on project level than test level. But, a tester can do parallel testing, migration testing, etc. after migration to new version or after adding new system.

Stored Procedure Testing:
A SP needs to broken into action based on functionality and needs to be tested separately. A tester can write negative and positive test cases based on number of arguments, data types, orders of arguments, and return value and data type of return value being passed. People often consider it is difficult (being white box type) but. If there is an existing document explaining all these, SP test is not that complex. Of course knowledge of SQL language is vital.
Other important aspects of Database testing are data type. Sometime developers mess up using same kind of data-type in front end and back end. A DB tester should make sure about it. Data type, size is also a performance related issue. It always make your marks as good tester if you to bring those issues during meetings. (if you are sure about what kind of Data-type  is right from business, performance and other technical point of view).
Data-Driven Testing is a method to test many data with same script. Creating a script to provide data is an important task of a tester. There are many automation tools to create a capture and playback the script.