Overview of Database Testing

With a mushrooming number of internet users, the data creation, storage, and retrieval has reached new heights and so has the number of issues arising due to these activities. The major source of origin of these failures/defects lies in minimal attention paid towards database handling and testing.

So what do we understand by Database and Database Testing?

Database: It is a systematically framed repository of indexed information (basically as a bunch of inter-related data files) that provides an ease of updating, retrieving and analyzing the data. In layman terms, it is defined as a storage structure for data in the form of rows and columns.

Database Testing: It is the process of testing database functions, triggers, and SQL queries. Thereby validating database tables, data models, and database schema as per information provided at the User Interface.

“Man believes in what he sees, but a coin has two sides; similar is the case with UI and database.”

Major misconceptions about testing are that it is constrained to Front-End (User Interface) only, but each and every bit of information entered by the user is stored at the Back-End (Database) which is equally respected while testing.

Although user interface and database testing go hand-in-hand, they have diverse testing aspects.

Need for Database Testing

As there are a lot of user activities happening over the Internet every second and lots of data is being generated in the form of data browsing, filling registration forms, online shopping, and marking attendance, etc. The need for database testing plays a vital role in maintaining data consistency, integrity, and security. So the following measures are adopted for data validation:

Fig. 1: Validations for Database Testing

1. Data Mapping: In the software systems, data travels back and forth from Front-End (user interface) to Back-End (Database) and vice versa. So the following aspects of data should be looked for:

i. Information/data entered by the user at the front end is mapped to correct tables in the database.

ii. When data is updated from UI, the same should be updated to the database immediately.

Fig. 2: Data Mapping

2. ACID Properties Validation: ACID stands for Atomicity, Consistency, Isolation and Durability. Validation of these four properties helps in maintaining stability, steadiness, and privacy of the data. Thus making it more reliable and easy to retrieve.

i. Atomicity: It lays down the fact that a transaction is either “PASS” or “FAIL”; there is no intermediate state like partially updated in the data transaction. It is also referred to as “All or Nothing” rule.

ii. Consistency: Any transaction performed at UI should be conceived into a valid state in the DB.

Fig. 3: ACID Rule

iii. Isolation: In case multiple transactions are executed at once, then the state of the DB should be consistent.

iv. Durability: External factors like system crash, power failure should not impact the data once the transaction is completed.

3. Data Integrity: Whenever the data is modified by any of the CRUD (Create, Retrieve, Update, and Delete) operations, then the latest information should display on each and every User Interface. So the database test cases should be designed in a way that validates the consistency of data at every screen/display.

Fig. 4: Data Integrity

4. Business Rule Conformance: Complex database refers to complicated components such as constraints, stored procedures, triggers, etc. So it is the duty of tester to enhance SQL queries to validate these complex components.

How to Perform Database testing?

While testing a database, the most important part is how we proceed with the testing process as it is rightly said that “well begun is half done.” Following steps are involved in database testing:

1. Prepare Test Environment: The environment on which a user has to perform database tests should be a stand-alone database or a replica of production database so that actual results can be achieved, and appropriate changes can be incorporated in database code (as per test results).

Fig. 5: Steps Involved in Database Testing

2. Run a Test: CRUD operations can be performed on the application, and related changes can be seen in the database OR execute SQL queries (prepared by Tester/Developer), stored procedures, views, and triggers directly. Devise positive and negative test cases for every stored procedure, pass values to desired parameters, and verify the results. Also, each stored procedure can include many blocks or functions which have to be tested individually.

3. Verify Test Results: It involves checking the results of the SQL that we fired on the database.

4. Validate: Validate if right triggers (which are event-specific) are fired on the execution of SQL queries or stored procedures associated with the events, and correct tables/columns are populated (prepared as per client requirements).

5. Report the Findings: Share ‘Actual’ and ‘Expected’ results with the Business Analyst aligned to project or client team and raise bugs if expected results are not met.

With a steep rise in the number of users connected to the World Wide Web, the data is being stored, transferred and analyzed every second, hence it is extremely important to maintain consistency and accuracy of data. Therefore, database testing should be given utmost importance.

Hope you find this piece of information helpful to get started with database testing. There is more to come about database testing in subsequent blogs.

Stay tuned for more updates!