Database testing

The interface in most functions is the focus of the testers and the programmers, because the user interface is the most visible part of the application. However, it is important not only that the interface but the information validation shown can be considered the heart of the application, also known as DATABASE – the database.

Let’s consider a Banking application, when users make transactions. From the database Check the following important views:

1. The application stores transaction information in the application database and displays them correctly to the user.
2. No information is lost in the process.
3. When the transaction is not completed or canceled, no operational information will be performed
4. No unauthorized individuals are allowed to access user information.

To ensure all of the above, we need to use data validation or data validation.

What is database testing?

Database testing is checking the schema, tables, triggers, etc. of the database to be checked. It may involve creating complex queries to test the database and check its responsiveness when loading / stress testing. It checks the integrity and consistency of data.

The basic difference between User Interface Testing and Database Testing

Types of database checks

Database testing consists of 3 types of tests:

• Structural Testing (Structural Testing)
• Functional Testing (Functional Testing)
• Non-functional Testing (Non-functional Testing)

1. Structural Testing

Checking the data structure involves the authentication of all elements inside the data warehouse used, mainly for data storage and is not allowed to be used directly by the end users. Validating database servers is also important in these types of tests. The completion of this phase is done by testers involved in mastering SQL queries.

a. Schema testing

The main aspect of schema checking is to ensure that the schema mapping between the Front-end and Back-end is similar. Therefore, we can also consider schema testing as a mapping test.

Specifically need:

• Confirm the different schema formats related to the database. Many times the table’s mapping format may not be compatible with the mapping format found in the application’s user interface.
• Verification is needed in case the table / views / columns are not yet mapped.
• It is necessary to verify whether the heterogeneous databases in an environment are suitable for the overall application mapping. We also look at some tools for validating database schemas such as:

• DBUnit, integrated with Ant, is well suited for mapping testing.
• SQL Server allows testers to check and query the schema of the database by writing simple and non-code queries.

For example, if developers want to change the table structure or delete it, testers will want to make sure that all Processes and Views stored using that table are compatible with specific changes. Another example might be if testers want to test schema changes between two databases, they can do it by using simple queries.

b. Database table, column testing (database tables, columns)

• Is the mapping of database fields and columns in the Back-end compatible with those in the Front-end?
• Confirm the convention of the length and naming of database fields and columns as required by the requirements.
• Confirm the presence of any unused / unmapped database tables / columns.
• Verify compatibility of auxiliary database columns with the columns in the application’s Front-end: Data type and Field length
• Whether database fields allow users to provide the desired input in the business requirements of specification documents.
• Check keys and indexes, including: Primary key, foreign key, constraints created on request tables
• Check that the references for foreign key are valid.
• Check whether the data types of the primary and foreign keys are the same in the two tables.
• Check that the necessary naming conventions have been followed for all keys and indexes
• Check the size and length of the fields and indexes needed.
• Whether required
• Whether the request has been created on tables as specified by the business requirements: Clusster index, No Clusster index

c. Stored procedures testing (Stored procedures)

Need to check the following:

• The requirement is applied to all stored procedures for all modules for the application under test including coding standard conventions, exception and error handling.
• Whether all conditions / loops are included by applying the required input data to the application under test.
• Is TRIM operations applied correctly whenever data is fetched from necessary tables in the Database?
• Manual implementation Does the stored procedure provide end users with accurate results?
• Does the manual implementation of the Stored Procedure ensure that the fields in the table are updated according to the requirements of the application under test?
• Whether or not the execution of the stored procedure will allow invoking the requested trigger implicitly.
• Confirm the presence of any unused storage procedure.
• Authentication for Null permission conditions can be done at the database level.
• Validation is the fact that all stored Procedures and Functions were successfully performed when the Database was checked to be empty.
• Confirm the overall integration of stored procedure modules according to the requirements of the application under test.

Some of the tools used to check stored procedures are LINQ, SP Test tool, etc.

d. Trigger testing

• Are the necessary coding conventions followed during Triggers’ coding phase?
• Check if the activations that are executed for the corresponding DML transactions meet the required conditions.
• Does it trigger accurate data updates once they have been done?
• Validation requires updating Update / Insert / Delete in the field of the application being tested.

e. Database server validation (Database server validation)

• Check the database server configuration in accordance with the business requirements.
• User authorization checks are required to perform only the levels of action required by the application.
• Check if the database server can accommodate the maximum number of user transactions allowed according to technical specifications.

2. Functional Testing (Functional Testing)

Testing the functional database in accordance with the requirements specification needs to ensure that most of the transactions and operations performed by the end users conform to the required specifications.

The following are the basic conditions that need to be observed for database validation:

• Whether the field is required while allowing the NULL value on that field.
• Is the length of each field sufficient size?
• Does all similar fields have the same name on the tables?
• Are there any calculated fields in the Database?

This specific process is to validate the field mapping from an end-user perspective. In this particular scenario, testers will perform an operation at the database level and then navigate to the relevant user interface item to observe and validate if field validation is appropriate. have been made or not.

The reverse condition whereby an operation is first performed by a tester at the user interface and then the same thing verified from the back is also considered a valid option.

a. Check the integrity and consistency of data

The following checklist items are important to note:

• Is the data organized or not?
• Is the data stored in the tables accurate and required by business?
• Is there any unnecessary data currently in the application under test?
• Whether data has been archived on demand for data that has been updated from the user interface.
• Are TRIM operations performed on data before inserting data into the database to be tested?
• The transactions have been carried out according to the requirements and whether the results are accurate or not?
• Whether the data is correct or not when the transaction is successfully carried out according to business requirements.
• Whether the data was successfully restored if the transaction was not successfully performed by the end user.
• Whether the data has been completely restored in the condition that the transaction has not been successfully implemented and many heterogeneous databases related to the transaction in question.
• Have the transactions been made using the design processes required by the system business requirements?

b. Login and user security

User credentials and security credentials need to consider the following:

• Does the application prevent users from continuing the application in the case of: -The username is invalid but the password is valid -The username is valid but the password is not valid. -The username and password are invalid. -The valid username and password are valid.
• Whether or not users are allowed to perform specific operations specified by business requirements (authorization)
• Does the data be secured from unauthorized access
• Whether different user roles are created with different permissions
• Whether all users require access levels on the specified Database as required by business specifications.
• Check sensitive data such as passwords, credit card numbers are encrypted and not stored as plain text in the database. Make sure all accounts should have complex and unpredictable passwords.

3. Non-functional Testing

Non-functional tests in the context of database testing can be classified into different categories according to business requirements. These can be load test, Stress test, Security check, Usability check and Compatibility check, etc. Load test as well as stress test can be grouped as Performance Check. serves two specific purposes when it comes to the role of non-functional testing.

Quantitative risk – Quantifying risk actually helps stakeholders identify different system response time requirements according to required load levels. This is the original purpose of any quality assurance task. We should note that load testing does not minimize risks directly, but through risk identification and risk quantification processes, providing remedial opportunities and remedial dynamics will minimize risks. .

Minimum system equipment requirements – The minimum system configuration will allow the system to meet the official stated performance expectations of the stakeholders. Therefore, hardware, external software and associated cost of ownership can be minimized. This specific requirement can be categorized as an overall business optimization requirement.

Non-functional tests usually include: Load test, Stresstest To perform load and stress test can use the following tools to perform: load runner, win runner and JMeter

Misconceptions or misunderstandings related to Database Testing.

• Examining the database requires a lot of expertise and it is a very tedious task

Reality: Efficiency and Effective Database Testing provide long-term functional stability for the overall application, so the hard work behind it needs to be done.

• Check the database for additional job bottlenecks

Reality: On the contrary, examining the database adds value to the whole job by finding potential problems and thus proactively helping to improve the overall application.

• Checking the database slows down the overall development process

Reality: Checking the database helps improve the overall quality of the database application.

• Checking the database can be too costly

Reality: Any database inspection cost is a long-term investment that leads to long-term stability and robustness of the application. Therefore, the cost of checking the database is necessary.

Summary :

Above is the brief content about Database Testing that I have learned, simple to understand is like testing the UI of the system, it is slightly different from checking on the database system, and a part interactively hold UI and data. Hopefully help a lot for those who are inexperienced in this area

Reference : https://www.guru99.com/data-testing.html