What & How to test a Database – The Basics

Today I’ll discuss about some basic techniques of the database testing. A Test Engineer needs to test DB on the testing phase of software. Database is the most important part of any software. It is impossible for us to develop software without Database. 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 back-end.

Currently, several database tools are available in the market like MS-Access, MS SQL Server, Oracle, 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 the What & How topic, I would like to share some common characteristics of Database. The end user mainly operates the CRUD (Create, Retrieve, Update, and Delete) operations when the application is under execution. Every Test Engineer needs to know at least the basic about the CRUD operations. These are:

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 is performed.
D: Delete – when user ‘Remove’ any record from the system, ‘Delete’ operation is performed.

It does not matter at all, which DB is used and how the operation is performed. End users performed lot of DB operations. But the end users are not concerned about any join or sub-query, trigger or stored-procedure, query or function was used to do what they wanted.

 

What we need to test in database testing:

We need to ensure some DB features/functions  for Database testing like Data Mapping, ACID test, Data Integrity, Implementation of Business role etc.

Ensure data mapping

We need to make sure that the mapping between different forms/screens of the application and the relations of its DB is not only accurate but is also according to design documents. We need to verify that respective tables and records are updated when user clicks ‘Save’, ‘Update’, ‘Search’ or ‘Delete’ from GUI of the application.

 

Ensure the 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.

Ensure Data Integrity

We need to consider that different modules of application use the same data in different ways and perform all the CRUD operations on the data. In that case, we need to 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.

Ensure Accuracy of implemented Business Rules:

Databases have been evolved into extremely powerful tools that provide sufficient 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. Test Engineers must ensure that the implemented business logic is correct and works accurately. They may execute the stored procedures, views to compare the result with real business.

Above points describe the four most important ‘What To’ of database testing. Now, I will briefly describe about ‘How To’ of DB Testing. But, first of all I like to strongly say that 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:

We can perform the Database testing by the following below listed ways as a Software Test Engineer:

Create own Queries

First of all Test Engineer should have very good knowledge of SQL and specially DML statements in order to test the DB properly and accurately. Secondly, the tester should acquire good understanding of internal DB structure of application. If these two pre-requisites are fulfilled, then the Test Engineers are ready to test DB with complete confidence.  

We will perform any CRUD operation from the UI of application and we 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 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 (or DB Specialist) 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.

Observe data table by table

The Test Engineers can verify the CRUD operation result by viewing the tables (relations) of DB, if they are not good at SQL. This way may be annoying and bulky (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 the application database.

Get query from developer/DB Specialist

Test engineer may collect the query from the developer or DB specialist.  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.

Tester will get knowledge about SQL by using this method. 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.

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 application and proper training.

I Love Poka-Yoke – A New SQA Process/Technique

Poka-Yoke is a quality assurance process introduced by Japanese engineer Shigeo Shingo. This term is used in Japanese language as Poka means mistake and Yoke means prevent i.e. mistake preventing. So we can also call the Poka-Yoke as mistake proofing technique.

The purpose of Poka-Yoke is to develop processes to reduce defects by avoiding or correcting (design to show alerts or warning messages to user) mistakes in early design and development phases. This technique is mostly used in manufacturing industries but now this effective technique is also adapted in software development processes as well. I’ve described two (2) examples below about the Poka-Yoke process in manufacturing industries and software development processes.

Example 1 - Manufacturing Industry: The cell phone company designed the Sim card slot by using the Poka-Yoke technique; it is an implementation of manufacturing level Poka-Yoke. Cell phones are designed in such a way that user is allowed to insert SIM card in correct way only. There is no chance for user to make a mistake while putting SIM card in a cell phone. This makes the design mistake proof. 

Some cell phones allow the reverse way insert of Sim card; but those are not working. System display message about Sim card availability. 

Example 2 - Software development: Poka-Yoke is used for software application. Google used Poka-Yoke more. The perfect example of Poka-yoke process in software application is – Gmail email attachments feature – when we type word ‘find attached’ while composing a new email and try to send it without attaching a file Google shows a popup reminder saying that you used words “find attached” in your email but did not attach any files, do you still want to continue sending?

  

Also, Poka-Yoke has been used for the Password strength, spelling correction on search etc. on Google.

Poka-Yoke technique is using for:

• Defect Prevention
• Defect Detection

Defect prevention is the most important activity in SDLC. This method is used to identify all possible issues and actions needed to eliminate those issues. Many software defects can be prevented in design phase itself. Quality assurance team can help to prevent these defects by reviewing the software requirement specification (SRS) documents. All issues identified in this stage are addressed in software coding phase and prevented from carrying to later stages. The manufacturing and software industry examples provided above are the good examples of defect prevention technique.

Defect detection is the most common task for quality assurance teams. QA teams use various approaches and strategies for executing test cases effectively. Defects are detected by many other testing methods like smoke and exploratory testing.

How Poka-Yoke Technique Works?

Below are few steps to design and implement a process to prevent software defects:

1.  List all user scenarios or end-to-end test cases for the application.
2.  Analyze all these user scenarios by asking 5-Whys questions to understand the ways these scenarios can fail.  For example: The problem is: The vehicle will not start.

·  1^st Why? - The battery is dead.

·  2^nd Why? - The alternator is not functioning.

·  3^rd Why? - The alternator belt has broken.

·  4^th Why? - The alternator belt was well beyond its useful service life and not replaced.

·  5^th Why? - The vehicle was not maintained according to the recommended service schedule. (fifth why, a root cause)

3. Once you identify the ways these user scenarios can be wrong, design and apply a Poka-Yoke technique to avoid the possible problems (e.g. this design could be a simple unit test to check if any function written is working properly or not).
4. Make sure the technique designed to avoid the defect is working properly by giving errors or warning message for incorrect input or handling of the user scenario.
5. Once the trial is passed add this technique in the list of Poka-Yoke processes to be performed each time on new release/build. 
6. Measure the success of this Poka-Yoke process. Check if this technique has really prevented or caught defects when happening.

The Qualities of a Good Poka-Yoke Process

• Poka-Yoke should be simple to create and maintain. It should be easy to handle and cost-effective. Maintaining a complex Poka-Yoke is time consuming and often results in issues if not maintained properly.
• Poka-Yoke should be designed early in SDLC so that it can detect issues quickly.
• A good Poka-Yoke should be accurate enough to find the issues when they occur.
• A good Poka-Yoke should be designed in such a way that it should stop most common issues occurring in the software.
• It should be part of software design and coding process.

Making mistakes is OK; just don’t make the same mistake again and again. And to avoid making same mistakes again there should be some checks or processes in place. The Poka-Yoke techniques are developed to solve this problem.