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 some existing resources including:
•

A PDF document titled "Developing Entity Relationship Diagrams (ERDs)"

•

Powerpoint slides and accompanying MP3 audio of a lecture explaining normalisation. Both these documents are available from the Assignment 2, Part A Resources page. For
T2, 2006 the URL for this page is http://webfuse.cqu.edu.au/Courses/2006/T2/COIS20025/Assessment/Item_2/Part_A_Resources/ It is important that you become familiar with these resources 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