What is Database Testing?

A database is one of the essential parts of a software application. As the complexity of an application increases, the need for a powerful and secure Database emerges. Database Testing – Testing database is a type of software testing to test the tables, schemas of the Database (Database). It also checks the integrity and consistency of the data, checks the database operation against abnormal input data flows.

Importance of Database Testing

Database Testing is extremely important in software testing because:

• Make sure that the input data is properly stored in the database or not.
• Helps to avoid data loss, save canceled transaction data
• Prevent unauthorized access to Database

Database testing objectives

1. Data connection

In the software system, data will be passed from the UI to the backend and vice versa. Therefore, we need to check some aspects of data connection such as:

• Check that the fields in the data submission form are linked to the correct fields in the Database. Usually this information will be identified in the project documentation file. What QA needs to do is make sure everything is clear and correct.
• When a CRUD (Create / Create – Read / Get – Update / Update – Delete / Delete) action is performed at the fron-end of the application, a corresponding action is passed to the back-end. The QA will check to see if the operation was successful and save the results in the database

2. Check ACID properties

Every operation in the database needs to comply with 4 properties:

• Atomicity: means a successful or failed operation. This property guarantees that when an action involves two or more processes, even if one part of the action fails – it means that the whole action has failed. Usually, this is called the “all or nothing” rule.
• Consistancy: Ensures that an action is never passed to the database in the unfinished camp i.e. an action always results in a valid state in the database.
• Isolation: Ensures that if there are multiple transactions and they are all executed at the same time, the result in the database should be the same as when it was executed one by one.
• Durability – Sustainability: ensuring that after a transaction is completed, no external factor such as a power outage or incident can change it.

3. Data Integrity

For any CRUD action, the changed data must be executed and displayed on all screens or data forms. Do not just update the data on one screen and show older values ​​on other monitors. When an application is in development, the end-user CRUD actions are assumed and tested using database tools.

• Create: When a user performs “Save” any new transaction
• Read / Retrieve: When user performs “Search” or “View” actions of any saved transaction
• Update: When the user performs “Edit” a data already exists
• Delete: When the user performs the “Delete” data from the system Any database operation performed by the end user is always one of the four above. So create DB test cases by including checking data in all places where it appears to see if it’s consistent.

4. Compliance with professional rules

Some of the more complex components can be stored in the database, such as relational constraints, stored procedures, which require appropriate SQL queries to retrieve this data. What QA needs to do is ensure the business logic is correctly executed and functioning correctly

Data Testing Implementation Process

Data testing process is quite similar for different types of applications:

Step 1: Prepare test environment

Step 2: Run the test

Step 3: Check implementation results

Step 4: Confirm the results

Step 5: Report execution

Some ways to perform data testing

1. Write the queries yourself:

In order to test Database correctly, the tester must have very good knowledge of SQL and DML (Structured Query Language) statements. The tester also needs to know the system’s internal DB structure. The tester can combine the GUI and verify the data in the respective tables to get accurate conclusions. If the tester is using SQL Server then SQL Query Analyzer can be used to write the queries, execute them, and retrieve the results. If the application is very complex then the tester may find it difficult or impossible to write all of the required SQL queries. For complex queries, the tester will need help from developers who have a deeper understanding of the database.

2. Observe the data in each table

Tester can perform data verification using the result of CRUD operation. This can be done manually using the application UI once you know database integration. But this can be a tedious and cumbersome task when there is huge data in different database tables. When performing manual data testing, the tester must have good knowledge of database table structure.

3. Get the queries from the developer

This is a simple way to do data testing. The tester can perform any CRUD operation in the UI and verify its impact by executing the corresponding SQL queries from the developers. It will not require the testers to have good knowledge of SQL nor knowledge of database structure

4. Use automated Data Testing tools

A tester can use some appropriately available tools to perform data testing. Some popular tools such as: SolarWind, Data Factory, MockupData, …

Reference : https://www.softwaretestinghelp.com/database-testing-process/