Basics of Database testing

The tool allows the user to commit all the operations from the starting of data definition which involves Creating, Dropping, Altering, and Truncate of the table to the data manipulation which takes in Selecting, Updating, Inserting, and deleting of data. Database testing executes the tests to check the exact data values that have been restored from the database. Testing of the Database is one of the vital testings which requires the testers to be experienced in writing SQL queries and also methods to check the table as well.

Need for database testing 

• Data mapping: Most of the data transits from the User interface to the backend database and vice versa, therefore we must consider these points, First of all, we need to verify if the fields in the frontend are planned/mapped frequently with the parallel fields in the database. Normally these mapping particulars are defined in the requirements script. Secondly, at any moment a certain action is executed at the frontend of the application, a simultaneous CRUD (Create, Retrieve, Update, and Delete) action takes place at the backend. Hence A tester checks if the right action is called and the action taken is successful or not. 

• ACID: Atomicity, Consistency, Isolation, and Durability. Databases have to comply with these properties whenever it executes an action. 

Atomicity:- It means if even a single part of proceedings fails then the entire transaction suffers therefore failing the entire proceedings. 

Consistency:- maintaining the consistency of the database i.e entire transaction always results in the valid state of the DB. 

Isolation:- If there are multiple actions and all are executing all at once then the Database should be the same as they were executed consequently. 

Durability:- No power loss and crash should be able to alter the table once it is committed. 

•  Data Integrity

It is necessary to have the updated DB or the recent values for the CRUD operations to take place. The value should be consistent, that is it should be updated on all screens, it should not display older values on one and updated values on others. 

End-User uses the CRUD operation when the application is under implementation. CRUD stands for:- 

C/ Create – “ Create “ action is performed when the user wants to save any new data

R/ Retrieve – “retrieve” action is executed when the user wants to “view” any saved data. 

U/ Update – “Update” action is executed when the user wants to “modify” the pre-existing data in the table.

D/ Delete – “Delete” action is performed when the user wants to remove any record from the table. 

Any database operation performed by the end-user is always one of the above four.

• Business Rule Conformity

The more complex the database means the more complicated modules like procedures, limitations, etc. Hence testers would use more appropriate SQL queries to verify these complex databases. 

Database Testing Outline

Transactions 

These are the 2 commonly used statements:

1. BEGIN TRANSACTION TRANSACTION#
2. END TRANSACTION TRANSACTION#

Also Read: Why do we use performance testing

The Rollback statement ensures that the database stays in a consistent state. (ROLLBACK TRANSACTION#)

Once these statements are executed we use the “ Select “ command to ensure that the said changes are reflecting in the table. SELECT * FROM TABLENAME <tables which involve the transactions>.

Database Schemas: A schema is a representation of how the data will look inside a database. Therefore to test it 

• We identify the requirements on which the database operates. Such as : 
• Before creating any field primary keys to be created first.
• For easy pursuit foreign keys must be indexed properly. 
• Identify the field names where they are starting and ending with certain characters.
• A constraint is used in the fields where values can or cannot get inserted.
• Using the following methods as per there relevance:-
• SQL Query DESC<table name> to validate the table.
• Reg Expressions for checking the names of the fields and their respective values.

• Triggers

When a certain transaction takes place in a table, a piece of code is instructed to be initiated automatically, For example, if an employee joins Oodles technologies. He is working on 2 projects such as Envision and Bhasha. The employee is added to the table “employees Oodles”. A trigger would add the same employee to the corresponding projects table once added to the employees' oodles table. 

Also Read: Protocols to be referred to before Testing

The Basic method to test is to run the SQL query with the trigger independently first and then track the result. Followed this up with running the Trigger as a whole. Then, Comparing the results

• Stored Procedures

They are somewhat similar to the user-defined functions. Stored Procedures are called by Call Procedure/Execute Procedure statements and the result is in the form of sets. These are stored in the Relational database management system and are accessible for applications. 

• Field Constraints

The foreign key, default value, and Unique value:

• Executing a FE operation that practices the database object condition.
• Checking the results with a SQL Query.

Step to Test Databases 

The common test process for testing databases is similar to every other application. The following are the steps:- 

• Preparing the environment.
• Followed by a test run.
• Checking the test result.
• Validating according to the expected results.
•  Reporting the findings to the concerned stakeholders. 

Mostly, the SQL queries are used to build the tests, the commonly used command is “Select”.

Select * from <tablename> where <query>

SQL has 3 more important commands:-

1. DDL: Data definition language: CREATE, ALTER, RENAME, DROP, and TRUNCATE to handle tables (and indexes).
2. DCL: Data controller language: Grant and Revoke are used. Provides authorization to users with manipulation and access to data. 

Grant syntax:

Grant select/update

On <TableName>

To <emp id1, emp id2…empidn>;

Revoke syntax:

Revokeselect/update

on <table name>

from<emp id1, emp id2…empidn>

•  DML: Data manipulation language : statements to add, update and delete tables. 

Among all these features and factors to test on databases, there is an escalation among the testers to be technically expert on the key concepts of database testing. Hope this blog helps to focus on and provides a basic idea of how to test a database. 

We, at Oodles Technologies, provide end-to-end SaaS app development services to address varied project requirements of enterprises. Our end-to-end software development services include quality assurance and software testing services. Explore our quality assurance services for enterprises. For more information, reach us out at [email protected].