Database Testing – Practical Tips and
Insight on How to Test Database:
Database is one of the
inevitable parts of a software application these days. It does not matter at
all whether it is web or desktop, client server or peer to peer, enterprise or individual
business, database is working at backend. Similarly, whether it is healthcare
of finance, leasing or retail, mailing application or controlling spaceship,
behind the scene a database is always in action.
Moreover, as the
complexity of application increases the need of stronger and secure database
emerges. In the same way, for the applications with high frequency of
transactions (e.g. banking or finance application), necessity of fully featured
DB Tool is coupled.
Currently, several database tools are available in the market e.g.
MS-Access2010, MS SQL Server 2008 r2, Oracle 10g, Oracle Financial, MySQL,
PostgreSQL, DB2 etc. All of these vary in cost, robustness, features and
security. Each of these DBs possesses its own benefits and drawbacks. One thing
is certain; a business application must be built using one of these or other DB
Tools.
Before I start digging into the topic, let me comprehend the
foreword. When the application is under execution, the end
user mainly utilizes the ‘CRUD’ operations facilitated by the DB 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.
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.
It does not matter at all, which DB is used and how the
operation is preformed. End user has no concern if any join or sub-query,
trigger or stored-procedure, query or function was used to do what he wanted.
But, the interesting thing is that all DB operations performed by user, from UI
of any application, is one of the above four, acronym as CRUD.
As a database tester one should be focusing on following DB
testing activities:
What to test in database
testing:
1) Ensure data mapping:
Make sure that the mapping between different forms or screens of
AUT and the Relations of its DB is not only accurate but 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) Ensure ACID
Properties of Transactions:
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. This area demands more
rigorous, thorough and keen testing when the database is distributed.
3) Ensure Data
Integrity:
Consider that different modules (i.e. screens or forms) of
application use the same data in different ways and perform all the CRUD
operations on the data. In that case, make it sure that the latest state of data
is reflected everywhere. System must show the updated and most recent values or
the status of such shared data on all the forms and screens. This is called the
Data Integrity.
4) Ensure Accuracy of
implemented Business Rules:
Today, databases are not meant only to store the records. In
fact, DBs have been evolved into extremely powerful tools that provide ample
support to the developers in order to implement the business logic at DB level.
Some simple examples of powerful features of DBs are ‘Referential Integrity’,
relational constrains, triggers and stored procedures. So, using these and many
other features offered by DBs, developers implement the business logic on DB
level. Tester must ensure that the implemented business logic is correct and
works accurately.
Above points describe the four most important ‘What Tos’ of
database testing. Now, I will put some light on ‘How Tos’ of DB Testing. But,
first of all I feel it better to explicitly mention an important point. DB
Testing is a business critical task, and it should never be assigned to a fresh
or inexperienced resource without proper training.
How To Test Database:
1. Create your own Queries
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. (S)He
will perform any CRUD operation from the UI of application, and will verify the
result using SQL query.
This is the best and robust way of DB testing especially for
applications with small to medium level of complexity. Yet, the two
pre-requisites described are necessary. Otherwise, this way of DB testing
cannot be adopted by the tester.
Moreover, if the application is very complex then it may be hard
or impossible for the tester to write all of the needed SQL queries himself or
herself. However, for some complex queries, tester may get help from the
developer too. I always recommend this method for the testers because it does
not only give them the confidence on the testing they have performed but, also
enhance their SQL skill.
2. Observe data table by
table
If the tester is not good in SQL, then he or she may verify the
result of CRUD operation, performed using GUI of the application, by viewing
the tables (relations) of DB. Yet, this way may be a bit tedious and cumbersome
especially when the DB and tables have large amount of data.
Similarly, this way of DB testing may be extremely difficult for
tester if the data to be verified belongs to multiple tables. This way of DB
testing also requires at least good knowledge of Table structure of AUT.
3. Get query from
developer
This is the simplest way for the tester to test the DB. Perform
any CRUD operation from GUI and verify its impacts by executing the respective
SQL query obtained from the developer. It requires neither good knowledge of
SQL nor good knowledge of application’s DB structure.
So, this method seems easy and good choice for
testing DB. But, its drawback is havoc. What if the query given by
the developer is semantically wrong or does not fulfill the user’s requirement
correctly? In this situation, the client will report the issue and will demand
its fix as the best case. While, the worst case is that client may refuse to
accept the application.
Conclusion:
Database is the core and critical part of almost every software
application. So DB testing of an application demands keen attention, good SQL
skills, proper knowledge of DB structure of AUT and proper training.
In order to have the confident test report of this activity,
this task should be assigned to a resource with all the four qualities stated
above. Otherwise, shipment time surprises, bugs identification by the client,
improper or unintended application’s behavior or even wrong outputs of business
critical tasks are more likely to be observed. Get this task done by most
suitable resources and pay it the well-deserved attention.
Database Testing Tools
Tool/Product Name:
Post a Comment
Post a Comment