CSIS 325
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.
EmpID - (First Name, Last Name, Office, Office Address)
Project – (Supervisor, Project Manager, Client, Date, Hours Worked)
Question #4:
Describe any insertion anomalies that can occur in your 1NF table.
A few I think can occur which are employees being able to be added without a project needing to be
required to be assigned to them. On top of this since so much is dependent on the EmpID it could lead
to inconsistent data in the tables.
Question #5:
Describe any deletion anomalies that can occur in your 1NF table.
If you were to delete an employee or manager, you would lose all the data related to the employee who
left. The same would happen if you deleted a project too.
Page 1 of 4
CSIS 325
Question #6:
Describe any modification anomalies that can occur in your 1NF table.
If any of the employees changed their office or the name of it multiple tables of data would need to be
updated.
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)
Question #8:
What is the primary key of each of the 2NF tables you just constructed?
EmpID, ProjectID, ClientID
Question #9:
Identify the functional dependencies in the 2NF table(s) you constructed.
EmpID – (First Name, Last Name, Office, Office Address, Supervisor)
EmpID, ProjectID – (Date, Hours)
ProjectID, ClientID – (Project, Project Manager, Client)
Page 2 of 4
CSIS 325
Question #10:
In the space below, re-write the 2NF table(s) so that they are in 3NF.
Question #11:
What is the primary key of each of the 3NF tables you just constructed?
Employee & Supervisor Table: EmpID
Office Table: OfficeID
Project Table: ProjectID
Client Table: ClientID
Project & Time Table: EmpID, ProjectID
Question #12:
Identify the functional dependencies in the 3NF table(s) you constructed.
EmpID, ProjectID – (Date, Hours)
EmpID – (First Name, Last Name, Supervisor)
ProjectID – (Project, Project Manager)
ClientID – (Client)
Page 3 of 4
CSIS 325
OfficeID – (Office, Office Address)
Question #13:
Are all of your tables in BCNF? Why or why not?
Yes, Because all of the tables I create are standalone from each other due to their unique keys meaning
that they do not require any information from the other tables to be dependent on.
Page 4 of 4
Powered by TCPDF (www.tcpdf.org)
