Tips & Resources on Database Testing
1
Database testing involves some in-depth knowledge of the given application and requires a more defined plan of approach to test the data. At minimum, the tester must have the ability to query the database consistently at specified points in the workflow. It is important for the tester to be aware of the database design concepts and implementation rules.
Key issues for database testing include:
a. Data Integrity;
b. Data Validity (input into database in proper form);
c. Data manipulation and updates (updating the number of books sold, books available etc).
2
The following checklist helps to formulate many additional administrative test cases:
1. Understand the database design.
2. Identify the major risks.
3. Understand the security controls of the design; understand which user IDs have read/write access to the database controls.
4. Understand the procedures of the database maintenance updates and upgrades.
5. Ensure the performance requirements are being met.
6. Ensure operation-ability and performance when several users conduct the same task or query simultaneously (and include maximum simultaneous or concurrent users per specifications).
7. Ensure that backup and recovery procedures work as designed and don't impact availability requirements.
8. Ensure that the database allows the maximum number of connections that the system is designed to handle.
9. Ensure that database operations have enough space and memory for the amount of data. Include expansion of the system when these physical limitations of space and memory are exceeded. [Via: Introducing Software Testing By Tamres]
3
To test a database, you must have a database to test against. On a project, each developer should have his or her own local database, which the developer uses to run the tests against. This approach has multiple benefits. For one, database testing is naturally slow even when you run tests against a local copy of the database; you don’t add network latency into the equation by testing against a remote database, which would make it slower still. Second, and more important, it’s safer because you’re isolated from other database operations. This means you can do whatever is necessary to the database without worrying about affecting production data or other developers.
As an additional step, you may even want to have a second local database, specifically for automated testing. This allows for a populated local database to perform manual tests without having to worry about them affecting the automated tests. [Via: Hibernate Quickly by Patrick Peak, Nick Heudecker]
4
A tester has to use database design basics and apply those to find out common bugs in database systems. He has to identify poor database designs and common design flaws. Tester has to design test cases so as to find out common database problems and also to apply database normalization principles on a test project. Testers should have the knowledge of SQL and basics of relation databases.
5
Relational Database Basics for Testing
Grab a book or go to internet and polish your database basics e.g. What’s a relational database, Types of Data Integrity, Lack of data integrity introduces bugs, Identifying Design Defects, Inspecting table structures to reveal design problems, Exploratory Testing: Reading an ERD, Table Relationships: 1-1, 1-many, many-to-many, What to look for when Testing Relational Databases etc.
6
What to Test in Database & What Kind of Testing Is Important for a Database?
Following items should be tested:
Data Validity
Data Integrity
Data Format - types, size
Data Mappings
Referential Integrity,
Triggers
Stored Procedures
Views
Performance
Security
Backup & Recovery etc.
From the perspective of a database developer, only a few types of tests are really necessary in the majority of cases.
Databases should be tested for the following issues:
a. Interface consistency should be validated in order to guarantee that applications have a stable structure for data access.
b. Data availability and authorization tests are similar to interface consistency tests, but more focused on who can get data from the database than how the data should be retrieved.
c. Authentication tests verify whether valid users can log in, and whether invalid users are refused access. These kinds of tests are only important if the database is being used for authenticating users.
d. Performance tests are important for verifying that the user experience will be positive, and that users will not have to wait longer than necessary for data. Performance testing may involve load tests, which monitor the performance of the database under a given load; saturation tests, which attempt to overwhelm the system by constantly adding load and/or removing resources from it until it breaks; and, endurance tests, which place a continuous demand on the database over a sustained period of time.
e. Regression testing covers every other type of test, but generally focuses on uncovering issues that were previously fixed. A regression test is a test that validates that a fix still works.
[Via: Expert SQL Server 2008 Development By Alastair Aitchison, Adam Machanic]
7
Modern Webapps do much more than present static content objects. In many applicaiton domains, Webapps interface with sophisticated database management systems and build dynamic content objects that are created in real time using the data acquired from a database.
For example, a financial services Webapp can produce complex text-based, tabular and graphical information about a specific equity (e.g. a stock, bond, or mutual fund). The composite content object that presents this information is created dynamically after the user has made a request for information about the specific equity. To accomplish this, the following steps are required: 1) a large equities database is queried. 2) relevant data are extracted from the database 3) the extracted data must be organised as a content object 4) this content object (representing customized information requested by an end user) is transmitted to the client environment for display. Errors can and do occur as a consequence of each of these steps. The objective of database testing is to uncover these errors. [Via: Web Engineering: A Practitioner S Approach (sie) By Pressman]
More database testing resources:
SQL Server Database Testing Exercises: Comparing objects in two databases, Testing for SQL Injection, Testing a stored procedure, Testing a trigger (optional), Finding and diagnosing a bad query plan etc.
Link
Why Test an RDBMS?, What Should We Test?, When Should We Test?
Link
Database Testing: How to Regression Test a Relational Database
Why test an RDBMS? , What should we test?, When should we test? , How should we test?, Database sandboxes, Writing database tests, Setting up database tests, Database testing tools, Who should test?, Introducing database testing into your organization, Database testing and data inspection, Best practices
Link
The goal of this chapter is to develop and test a database project using Microsoft Visual Studio 2005 Team Edition for Database Professionals (VSTEDP). Most applications interact with a database that also requires testing. This chapter explains how to test a SQL Server database using VSTEDP.
Link
Tutorial: Strategies in testing database application with TTCN-3, by Bernard Stepien, Liam Peyton,Grant Middleton
Link
Tutorial Database Testing using SQL
Link
Data and Database Integrity Testing
Link
Close These Loopholes in Your Database Testing
Link
Close These Loopholes - Testing Stored Procedures
Link
Close These Loopholes - Testing Database Modifications
Link
Close Those Loopholes: Stress-Test those Stored Procedures
Link
Close Those Loopholes: Reproduce Database Errors
Link