Database modeling tools

To make the life of the Data Base Administrator easier, various commercial methodologies and tools have been developed, and there is a large industry promoting different variations on "boxology" (how to draw suitable box-and-pointer diagrams) to represent the semantic questions that arise in creating a database. The typical issues addressed with these tools are:

1. What are the primary, secondary and foreign keys in each relation. This manages the connectivity of the database, and addresses issues such as whether (say) there is some relation whose entries hold all possible values of a primary key. This is an instance of the closed world assumption.
2. Whether rows in tables correspond to individual entities in a universe or if it is possible to have multiple rows describe multiple relationships. Usually, this is addressed from the opposite direction. Namely, draw a diagram including boxes for each type of entity, and lines among the boxes reflecting relationships. Annotations usually describe whether the relationship is
   • one-to-one, such as perhaps a SSN to a unit record number at an institution
   • one-to-many, such as the link from a parent to children
   • many-to-one, such as links from children to parents or
   • many-to-many, such as the relationship between people and the doctors who have cared for them.
3. Whether it makes sense to have blank entries in various column positions in a row, and how to interpret these.
4. Integrity constraints among different tables in a data base. These may be structural (e.g., you cannot have an entry in the payroll table if you are not in the employee table) or may encode domain-specific rules (e.g., your salary cannot exceed your boss').

Names such as Chen, Yourdon, Bachman, etc., are associated with (what to me are) minor variations on the above themes, and there are many books, training sessions and conferences exploring these modeling issues. Recently, there has begun an attempt, led mainly by Jim Sowa, to integrate some of this interest with knowledge representation research from AI, which has tended to address more sophisticated versions of these problems.

A number of database modeling tools are provided by commercial vendors. These are examples of so-called CASE (Computer Aided Software Engineering) tools, which began life as design methodologies as early as the 1970's and became computer-based tools that incorporate some automatic programming techniques in the 1980's. One such tool with a reasonable Web-based description is:

• Silverrun

(This is not an endorsement; I have never used it, and in fact long ago helped to develop one of its competitors, which does not have very good Web descriptions.)