CSCI 6838.01
Capstone Projects Fall 2001
Guidelines for Database Design and Documentation

by K. Yue, August 23, 2001

Nearly all serious IT/Web applications use a sophisticated database to support their functionalities. A well design backend database is critical for the success of these applications. This document specifies the minimum requirements of the database design and documentation of your capstone projects and it should be strictly followed. It is assumed that you are using a relational database. If you are using other data models, discuss with your faculty supervisor and mentor for the requirements.

1. UML Class Diagrams

UML class diagrams should be used to clearly specify all persistent classes of the projects. All data members of the classes should be specified. All associations should be clearly stated with their cardinality. Constraints and special associations (such as generalization/specialization, superclass/subclass, etc) should also be specified.

With the permission of the faculty supervisor, you may use an extended entity-relationship diagrams to replace the UML class diagram.

2. Data Dictionary

You should maintain an up to dated data dictionary.

Relations

Relation names should be meaningful and follow a reasonable naming convention.

For each relation: the following should be clearly listed:

• meaning of the relation
• the class or association that the relation implements
• all full column names
• the primary key
• all candidate keys
• all foreign keys
• all indices (no need to mention the primary key since the primary key is automatically indexed)
• all functional dependencies
• the highest normal form (up to 5NF)

If a relation is not in BCNF, the rationale behind the decision should be discussed.

Example:

Supplies(SuppliesId, SupplierNum, PartNum, Quantity):

• Meaning: stores the quantity of a part supplied by a supplier.
• Implement: the association {Suppliers, Parts}.
• Primary key: {SuppliesId}
• Other candidate keys: {SupplierNum, PartNum}
• Foreign keys:
  1. SupplierNum of Suppliers
  2. PartNum of Parts.
• Indices:
  1. SupplierNum,
  2. PartNum
• FD:
  • SuppliesId -> SupplierNum, PartNum, Quantity
  • SupplierNum, PartNum -> SuppliesId, Quantity
• Highest Normal Form: 5NF

You may want to use a more tabular format.

Columns

Column names must be meaningful and follow a reasonable naming convention.

Columns should be listed under relations. For each column, the followings should be listed:

• column name
• accurate data type with constraint
• meaning
• accept null value or not
• default value
• other constraints
  

Example:

Supplies:

PartNum:

• Data type: varchar(8); a 8 digit string
• A unique number stored as a string to identify a part.
• Null value: no.
• Default value: no.
• Other constraints:
  • a foreign key of PartNum of the relation Part.

You may select to store these information in a more tabular format.

Example

Read the project example of the Fall 1998 section of the course Applied Database Development to get an idea of OO modeling using Rational's Rose. Beware that it is not exactly the same as the requirement here. If turquiose.rocks.uhcl.edu is down, you can also get it here.