Example Case to 3NF Tables

Purpose

The COIS20025 textbook and assignments have numerous exercises that require you to take a given case and generate a data design for the case. The data design will typically consist of an ERD, 3NF table designs including keys (obviously) and sample data.

The purpose of this document is to give an example of how to do this. It walks you through the process of completing this task.

Preparation

This document draws on "Developing Entity Relationship Diagrams (ERDs)" available from the Assignment 2, Resources page. You should become familiar with that resource before going any further.

The Case

This document will use the same case as used in the "Developing Entity Relationship Diagrams (ERDs)" document. It is included below.

A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects. The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number.

You are required to:
1. Create an ERD with cardinality notation.
2. Create 3NF table designs.
3. For each of the entities identified, design tables and identify the possible candidate keys, the primary key, a probable foreign key, and potential secondary keys.
4. Use sample data to populate the fields for three records.

Create the ERD

This process is already shown in the "Developing Entity Relationship Diagrams (ERDs)" document. Please read through that document for an explanation of how it is done. The fully attributed ERD developed in that document is shown in Figure 1.

[pic]
Figure 1
Completed ERD.

Create the 3NF Tables

This process has