1. O/R mappings (including the meta data)
2. Incoming data values
3. Outgoing data values (from queries, stored functions, views ...)
White/Clear-Box Testing Internally Within the Database
1. Scaffolding code (e.g. triggers or updateable views) which support refactorings
2. Typical unit tests for your stored procedures, functions, and triggers
3. Existence tests for database schema elements (tables, procedures, ...)
4. View definitions
5. Referential integrity (RI) rules
6. Default values for a column
7. Data invariants for a single column
8. Data invariants involving several columns
Steps to writing test cases for database testing
1) Learn the functional requirement of the application (SRS) completely
2) Find out the back end tables used, joined used between the tables, cursors used (if any), triggers used(if any), stored procedures used (if any), input parameter used and output parameters used for developing that requirement.
3) By knowing all these things write the test case with different input values for checking and comparing the actual results with the expected results for the application.
I would like to add one more test scenario;
a simplest test case for the data base would be adding the data from GUI or web or desktop apps (depending in app type) and check whether the appropriate data has been fetched...............
another scenario would be try to do some negative testing in the database fields like;
add some chars if the column field data type is int.. see what comes as result....like wise....check here that database should not crash.....
I am totally agree with Prashanth and shirish here ... and i would like to add importance of DB testing ...
Databases are an integral part of any software developed today. Whether you are a Software Tester or an owner of a web site, it is of an utmost importance to know about the underlying database.
The simplest task in dealing with databases is to write queries in order to communicate with a database. Web masters owning web sites or database administrators for complex or large software need certain level of expertise to perform complex tasks such as database monitoring, database auditing, database optimization, database models (also known as database schema), to name a few. This level of expertise calls for undergoing a comprehensive course.
For beginners, there is loads of online information about databases available on the Internet, like DatabaseGuides. It's a good idea for web masters to know about how their database works so that they can troubleshoot their own systems or know what is being done if a professional is hired.
Database Testing is an important aspect that a versatile Software Tester should be aware of. We'll discuss a few aspects of database testing here.
Why do we test database?
We all know that it's important to test the database our software uses. Your database holds confidential and valuable information which you would not like to be compromised. Testing the database provides us with a solid feedback essential for identifying defects.
What to test in database testing?
We need to consider the threats within the database (similar to White box Testing) as well as at the interface level (Similar to Black Box Testing).
Black Box testing might include, but not limited to:
Output Data (from queries, views, stored procedures)
White Box testing might include, but not limited to:
Unit tests for Stored Procedures / functions
Triggers / Views code
How to test?
When you want to test your database, you would need test databases that are replica of the original database. These are sometimes called as 'sandboxes' in agile terms. In this test database, you can experiment with data, develop new functionality, validate the changes and then integrate it with the project if satisfactory.
You'll need create database tests based on either rebuilding the existing database or starting afresh with creation of database and related schema. Identifying Test Data is an important task here. Once the tests are ready, you would execute them and check the results.
Database Testing is an elaborate topic that can't be fit in a single post. Would try and write more posts on the same.