Case 4 - Fall 2004

Start with the UPS ERD that you drew for Case 3. Extend the diagram to reflect the following:

• For each entity, define all attributes. Make sure that each entity has at least one identifier attribute (primary key). For all attributes, pay attention to their characteristics. That is, define  the data type, optional or mandatory, maximum length, etc.
• If two attributes are similar in name, make sure they reference the same thing.
• If two attributes have similar values, but different names, correct that.
• Once all attributes have been defined, you will then introduce intersection/joint entities wherever you have many-to-many relationships. Once all relationships are 1-to-many or 1-to-1, remember to put foreign keys in the correct places.
• Now, get all entities in third normal form. This may require you to redefine some entities and attributes, and maybe introduce more keys.

The end result should be an ERD that resembles a set of database tables in third normal form.