I have been working on an open source Museum Collections Management web application that is to be used to keep track of a museum's accessioned, donated, loaned or otherwise acquired artefacts.
This involved designing and creating a rather large database (in relation to my previous experiences), which stores all kinds of varying information (artefact information, changing location information, personal contact information, pictures, etc), that needs to be flexible and easily extendible.
I am just finishing my university degree and I am not a professional when it comes to database design and so I am really wanting to create a thorough test suite to ensure that what I have in place "works".
I have read up on database testing and have come across a few articles that mention Regression Testing in regards to databases but I do not fully understand what this all involves. From reading this article at Dr.Dobbs I understand that one sort of testing that I will need to do is validate that the logic in the database is correct. So I would create tests that insert certain data into the database and then follow it up with a query to ensure that I get the correct data back from the database (ensuring that all the appropriate triggers or views are working).
The confusion comes in with the mention of testing for "Data Quality". In the article above the author makes mention that you want to validate the following with tests:
- Column domain value rules
- Column default value rules
- Value existence rules
- Row value rules
- Size rules
What types of tests would this involve and how would they be implemented? Also this is my first time writing a test suite for a database, are there any good guidelines on how/where to begin or any processes that I could follow to guide my test development?
Best Answer
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:
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?
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.
As I understand this, you verify that data inserted or updated shows as expected in the database.
I am not clear on what this one means exactly.
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.
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.