A complete answer to this question would be very long. I will try to mention the main points.

To separate concerns, you may be looking at tests to:

A – Validate the database design.

B – Validate that the program(s) are interacting correctly with the database.

The database design validation should be performed by the person(s) who designed the database. The developers (while unit testing) should be concerned more with part (B). The primary difference I see between the two types of tests is the tools used. For (A), you’d use an environment independent of the project code, whereas on (B) you’d of course use the project’s code. In the following text, I will mix both.

To answer your specif questions:

Column domain value rules

Each column has an associated data type. Each column must be validated against business rules to prove that it represents the correct data type. Problems may arrise if the column data type is not compatible with business requirements or if the code uses a data type different from how it is defined in the database.

For example:

• If the column is defined as small int, you should not be able to store text in it. This is an important test specially when the columns are optional, since it could go unnoticed until someone actually enters some data in it.

• Could you store a negative value in a column where the business requires that to happen?

Column default value rules

Some columns are associated with a default value specification in the DDL (Data Def. Language) where if during insert the insert does not provide a value, the database will assume the default value. This can be tested by not passing the value and observing the result value the database stores. This test may also include checking for Nullable columns. This rarely requires a test since it can be verified from DDL unless an unique index is built on the column.

Value existence rules

As I understand this, you verify that data inserted or updated shows as expected in the database.

Row value rules

I am not clear on what this one means exactly.

Size rules

Each column has a size in the database based on how it is defined in DDL. you want to make sure that any value that fits requirements(either entered form GUI or resulting as an output of a computation) would be stored correctly in the column. For example a Small Integer data type does not allow you to store a value of 5 billions. Also, a name defined as VARCHAR2(30) will not accommodate 40 characters, so business rules must be very clear here, specially when the column is used for aggregating data. You want to test what happens in such situations.

guidelines on how/where to begin

One way of doing this is to come up with a testing plan. You want to ensure you are using the correct version of specifications (such as requirements documents and Use Cases) and of the software. You also need to coordinate thes tests with tests done by Unit Testing (if any). You may find duplicate tests that you don’t need to perform again. You want to take a copy of the database before testing so that you could repeat a specific test if you ever needed to. The DBA may help you with this. You also need to check with your team how do they document theses tests and verify the testing scope with them. You can break your database into logical parts and begin the testing of each logical part separately. The testing process could begin by studying the DDL of the database and verifying that the columns are defined correctly as required by the business. You should be using the application’s software and not any other tool to perform the majority of the tests. For example question the following:

• Is the column supposed to be of the defined type (no point in making a Name as Int).

• Is the size compatible with business requirements?

• Are all the columns in business requirements found in the database?

• Are null columns really optional?

• etc.

Next, you could design test cases to test the above. You could use the GUI to do most of the tests.

There are other important database tests that you have not mentioned. Those deal with:

1 – Testing that the primary keys are really unique from a business perspective.

2 – Testing that unique indexes (other than the PK) are really unique from business perspective.

3 – Testing Foreign Key constraints work.

4 – Testing what happens when a rows is deleted and its effect on related rows.

5 – Other tests regarding special database constructs such as CHEKC, Triggers if they exist.

6 – Correct table normalization and that normalized columns hold correct values.

The above is not a complete list but it should get you started.

I think you are approaching this the wrong way.

Any database I know verifies data before inserting it into tables – it validates it against the definition of each column. You can’t enter a 80 character string into a SMALLINT(3) column – the database will fail that attempt and let you know you’ve made an error. You don’t need to test for that yourself by inserting the data and then retrieving it.

What you do want to have are validation / filtering rules on data before it is sent to the database.

• Making sure the data fits the accepted types and range for each column, and filtering unwanted content
• Making sure to properly escape it to avoid errors (and possible SQL injections if you have a public interface)

Those validation / filtering rules should run on the data in your actual application. You can then setup tests to make sure they are correct, by feeding it with correct and incorrect data to make sure it passes or fails validation accordingly.

As far as database design goes, you can’t really verify it with tests – since many designs will work even if they are not ideal (and the definition of ideal changes between different people). Proper database design comes with experience and knowledge, not with automated tests.