CSIS 325
(KRISTEN CAMPBELL ) LAB: NORMALIZATION TEMPLATE
Question #1:
In the space below, re-write the table above so that it is in first normal form.
Question #2:
What is the primary key of the 1NF table you just constructed?
EmpID
Question #3:
Identify the functional dependencies in the 1NF table you constructed.
The following:
EmpID (FirstName, Last Name, Office, OfficeAddress, Supervisor, Project, ProjectManager, Client, Date,
WorkedHours)
Question #4:
Describe any insertion anomalies that can occur in your 1NF table.
A new employee is entered but is not assigned a supervisor.
Question #5:
Describe any deletion anomalies that can occur in your 1NF table.
A project is completed, but the client’s information remains.
Question #6:
Describe any modification anomalies that can occur in your 1NF table.
Page 1 of 5
CSIS 325
An employee gets moved to a new project, but the Project Manager doesn’t change.
Question #7:
Based on your 1NF table, in the space below, re-write the table so that it is in second normal form. You
may add ID fields to any new tables you create and use those IDs as foreign keys in other tables as
appropriate. (10 points)
Page 2 of 5
CSIS 325
Question #8:
What is the primary key of each of the 2NF tables you just constructed?
EmpID and ProjectID
Question #9:
Identify the functional dependencies in the 2NF table(s) you constructed.
EmpID - FirstName
EmpID - LastName
EmpID - Office
EmpID - OfficeAddress
EmpID - Supervisor
EmpID- Project
EmpID- ProjectManager
EmpID- Client
EmpID, ProjectID-Date
EmpID, ProjectID-HoursWorked
Question #10:
In the space below, re-write the 2NF table(s) so that they are in 3NF.
Page 3 of 5
CSIS 325
Question #11:
What is the primary key of each of the 3NF tables you just constructed?
EmpID, OfficeID, ProjectID, ClientID, ProjectID
Question #12:
Identify the functional dependencies in the 3NF table(s) you constructed.
EmpID- FirstName
EmpID- LastName
EmpID- Office
EmpID Supervisor
OfficeID- OfficeAddress
ProjectID- Project
EmpID- ProjectManager
ClientID- Client
EmpID, ProjectID- Date
EmpID, ProjectID- HoursWorked
Question #13:
Are all of your tables in BCNF? Why or why not?
Page 4 of 5
