1st,2nd,3rd,BCNF and 4th normal form in one example

All-in-One Example

Many of you asked for a "complete" example that would run through all of the normal forms from beginning to end using the same tables. This is tough to do, but here is an attempt: Example relation:
EMPLOYEE ( Name, Project, Task, Office, Phone )
Note: Keys are underlined.
Example Data:

Name	Project	Task	Office	Floor	Phone
Bill	100X	T1	400	4	1400
Bill	100X	T2	400	4	1400
Bill	200Y	T1	400	4	1400
Bill	200Y	T2	400	4	1400
Sue	100X	T33	442	4	1442
Sue	200Y	T33	442	4	1442
Sue	300Z	T33	442	4	1442
Ed	100X	T2	588	5	1588

• Name is the employee's name
• Project is the project they are working on. Bill is working on two different projects, Sue is working on 3.
• Task is the current task being worked on. Bill is now working on Tasks T1 and T2. Note that Tasks are independent of the project. Examples of a task might be faxing a memo or holding a meeting.
• Office is the office number for the employee. Bill works in office number 400.
• Floor is the floor on which the office is located.
• Phone is the phone extension. Note this is associated with the phone in the given office.

First Normal Form

• Assume the key is Name, Project, Task.
• Is EMPLOYEE in 1NF ?

Second Normal Form

• List all of the functional dependencies for EMPLOYEE.
• Are all of the non-key attributes dependant on all of the key ?
• Split into two relations EMPLOYEE_PROJECT_TASK and EMPLOYEE_OFFICE_PHONE. EMPLOYEE_PROJECT_TASK (Name, Project, Task)
  

  Name	Project	Task
  Bill	100X	T1
  Bill	100X	T2
  Bill	200Y	T1
  Bill	200Y	T2
  Sue	100X	T33
  Sue	200Y	T33
  Sue	300Z	T33
  Ed	100X	T2

  EMPLOYEE_OFFICE_PHONE (Name, Office, Floor, Phone)
  
  

  Name	Office	Floor	Phone
  Bill	400	4	1400
  Sue	442	4	1442
  Ed	588	5	1588

Third Normal Form

• Assume each office has exactly one phone number.
• Are there any transitive dependencies ?
• Where are the modification anomalies in EMPLOYEE_OFFICE_PHONE ?
• Split EMPLOYEE_OFFICE_PHONE. EMPLOYEE_PROJECT_TASK (Name, Project, Task)
  
  

  Name	Project	Task
  Bill	100X	T1
  Bill	100X	T2
  Bill	200Y	T1
  Bill	200Y	T2
  Sue	100X	T33
  Sue	200Y	T33
  Sue	300Z	T33
  Ed	100X	T2

  EMPLOYEE_OFFICE (Name, Office, Floor)
  
  

  Name	Office	Floor
  Bill	400	4
  Sue	442	4
  Ed	588	5

  EMPLOYEE_PHONE (Office, Phone)
  
  

  Office	Phone
  400	1400
  442	1442
  588	1588

Boyce-Codd Normal Form

• List all of the functional dependencies for EMPLOYEE_PROJECT_TASK, EMPLOYEE_OFFICE and EMPLOYEE_PHONE. Look at the determinants.
• Are all determinants candidate keys ?

Forth Normal Form

• Are there any multivalued dependencies ?
• What are the modification anomalies ?
• Split EMPLOYEE_PROJECT_TASK. EMPLOYEE_PROJECT (Name, Project )
  
  

  Name	Project
  Bill	100X
  Bill	200Y
  Sue	100X
  Sue	200Y
  Sue	300Z
  Ed	100X

  EMPLOYEE_TASK (Name, Task )
  
  

  Name	Task
  Bill	T1
  Bill	T2
  Sue	T33
  Ed	T2

  EMPLOYEE_OFFICE (Name, Office, Floor)
  
  

  Name	Office	Floor
  Bill	400	4
  Sue	442	4
  Ed	588	5

  R4 (Office, Phone)
  
  

  Office	Phone
  400	1400
  442	1442
  588	1588

At each step of the process, we did the following:

1. Write out the relation
2. (optionally) Write out some example data.
3. Write out all of the functional dependencies
4. Starting with 1NF, go through each normal form and state why the relation is in the given normal form.