CSCI 6838.01
Capstone Projects
Guidelines for Database Design and Documentation

by K. Yue, last modified: July 2010

Many IT/Web applications use a sophisticated database to support their functionality. 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 if they use databases. If you are using other data models, discuss with your instructor and mentor for the requirements.

If your team works on a component of an existing database drive application, it is not necessary to document the existing database. You will only need to document the relations you have created or modified.

1. UML Modeling

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

You may use an extended entity-relationship (ER) diagrams instead of 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 BCNF): yes, you will need to perform normalization analysis.

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.
• Implementation of: 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: BCNF

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)
• Meaning: 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, grouping columns under relations.