Database(Data) Testing Tutorial with Sample TestCases
So devise your DB test cases in a way to include checking the data in all the places it in databases means more complicated components like relational constraints, SQL Query DESCtable name> to validate the schema. Relational databases are tabular databases that are used to store target related data that We can refactor our database tables into structures, which are more performant. Tutorial-2 To Generate BVA Test Cases-For the Triangle Problem. Yet of them only 40% had a database test suite in place to validate the data Table 1 lists the issues which you should consider testing for both internally . Each individual test case puts the database into a known state required for the test.
It's in the darker corners that bugs are more likely to live and thrive; within unusual code paths that triggered by specific inputs that the code can't handle, but which should in theory, at least never arise in everyday use. Code coverage gives the developers a measure of how effectively they're delving into these areas. This article describes the basics of how SQL Test's code coverage can help uncover the darker, untested paths in your database code.
This is a testing tool only; don't do this in a production environment! In this example, we'll also install the SQLCop static analysis tests.
These will check properties of the database and its objects for consistency and correctness, but that don't execute any user code, and so will not have any effect on our code coverage. Enabling code coverage on a suite of tests is simple; just turn it on, as shown in Figure 1. Figure 1 Then, just click Run Tests. This function pads an integer value out to an eight-character string with leading zeroes.
We'll add a straightforward test to execute a successful case.
Why should we test a Relational Database
This will create a basic test template with links and advice for writing the various sections of the tests and you can simply add the required test logic to the procedure body. Whether the data has been stored as per as the requirement with respect to data which has been updated from the user interface. Whether the TRIM operations performed on the data before inserting data into the database under test. Whether the transactions have been performed according to the business requirement specifications and whether the results are correct or not.
Whether the data has been properly committed if the transaction has been successfully executed as per the business requirements. Whether the data has been roll backed successfully if the transaction has not been executed successfully by the end user. Whether the data has been roll backed at all in the condition that the transaction has not been executed successfully and multiple heterogeneous databases have been involved in the transaction in question.
Whether all the transactions have been executed by using the required design procedures as specified by the system business requirements. Login and user security The validations of the login and user security credentials need to take into consideration the following things.
Whether the application prevents the user to proceed further in the application in case of a invalid username but valid password valid username but invalid password.
Whether the user is allowed to perform only those specific operations which are specified by the business requirements. Whether the data secured from unauthorized access Whether there are different user roles created with different permissions Whether all the users have required levels of access on the specified Database as required by the business specifications.
Check that sensitive data like passwords, credit card numbers are encrypted and not stored as plain text in database.
It is a good practice to ensure all accounts should have passwords that are complex and not easily guessed. Non-functional testing Nonfunctional testing in the context of database testing can be categorized into various categories as required by the business requirements.
The load testing as well as stress testing which can be grouped under the gamut of Performance Testing serves two specific purposes when it comes to the role of nonfunctional testing. Risk quantification- Quantification of risk actually helps the stakeholders to ascertain the various system response time requirements under required levels of load.
This is the original intent of any quality assurance task. We need to note that load testing does not mitigate risk directly, but through the processes of risk identification and of risk quantification, presents corrective opportunities and an impetus for remediation that will mitigate risk.
Minimum system equipment requirement- The understanding which we observe through formal testing, the minimum system configuration that will allow the system to meet the formal stated performance expectations of stakeholders. So that extraneous hardware, software and the associated cost of ownership can be minimized. This particular requirement can be categorized as the overall business optimization requirement. Load testing The purpose of any load test should be clearly understood and documented.
Database Testing: How to Regression Test a Relational Database
The following types of configurations are a must for load testing. The most frequently used user transactions have the potential to impact the performance of all of the other transactions if they are not efficient. At least one non-editing user transaction should be included in the final test suite, so that performance of such transactions can be differentiated from other more complex transactions.
The more important transactions that facilitate the core objectives of the system should be included, as failure under load of these transactions has, by definition, the greatest impact. At least one editable transaction should be included so that performance of such transactions can be differentiated from other transactions. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure.
In other words, schema is the structure of the database that defines the objects in the database. Each single piece of data is a field in the table. A column consists of all the entries in a single field, such as the telephone numbers of all the customers.
Fields are organized as records, which are complete sets of information such as the set of information about a particular customereach of which comprises a row. Stored Procedures A stored procedure is a series of SQL statements stored in the database in a compiled form and multiple programs can share it.
The use of stored procedures can be helpful in maintaining data integrity, data control access and improving productivity. Triggers A database trigger is code that is executed in response to certain events on a particular table or view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.
It includes data validation before insertion, updates, and deletion. Triggers must be in place to validate reference table records. Characters in name field, negative percentage, etc. Find out inconsistent data and insert them into relevant tables and see if any failure occurs.
Try to delete a record that is still referenced by the data in another table. If a data in a table is updated, check whether the other relevant data is updated as well. You need to ensure that replicated servers or databases are in sync and contain consistent information.
Database Testing — Data Mapping Data mapping in a database is one of the key concept that needs to be validated by every tester. Usually the testers have to verify the user interface front end field mapping with the corresponding back end database field. If mapping is not provided, then you need to check the coding part. When you take any action in the front end application, there is a corresponding CRUD action get invoked, and tester have to check the every invoked action is successful or not.
This mapping information is defined in the requirements documents as mentioned above.
Database Testing Quick Guide
A tester will have to check if the right action is invoked and the invoked action in itself is successful or not. Database Testing — Performance An application with more response time and poor performance can lead to huge problems. Database Load Testing is used to find any performance issues before you deploy your database applications for end users. Database Load Testing helps you design database application for performance, reliability and scalability. Load Testing of Database applications involves testing the performance and scalability of your Database application with varying user load.
Database Load testing involves simulating real-life user load for the target Database application. It helps you determine how your Database application behaves when multiple users hits it simultaneously. Load Testing The primary target of Load Testing is to check if most running transactions have performance impact on the database.
With normal transactions, you should include one editable transaction to check the performance of the database for these type pf transactions. With normal transactions, you should include one non-editing transaction to check performance of database for these type of transactions. Time taken by database to fetch specific records should be checked.
Stress Testing Stress testing is performed to identify the system breakpoint. Here the application is loaded in such a way that the system fails at one point.