Question

The following table stores details on employees and the projects they have worked on and the hours they spent on the project each week:

WORKLOAD
EmpID	EmpName	ProjID	ProjName	HoursPerWeek
E01	Smith	P02	Database	10
E02	Smith	P01	Web Portal	5
E03	Robinson	P02	Database	20

Question: Explain why the above table is not in 2^nd normal form. Then normalise it to 2^nd normal form. Once the table is in 2^nd normal form, test if the result is in 3^rd normal form and explain why you think it is or is not in 3NF.

Answer 1

Answer:

2NF Check: In the given table, non-prime attribute ProjName is dependent on ProjID which is a proper subset of a candidate key. That's why it violates the rule for 2NF.

To convert the given table in 2NF, we decompose it into two tables:

WORKLOAD_EMP

EmpID	EmpName	ProjID	HoursPerWeek
E01	Smith	P02	10
E02	Smith	P01	5
E03	Robinson	P02	20

WORKLOAD_PROJECT

ProjID	ProjName
P01	Web Portal
P02	Database

3NF Check: The result is already in 3NF because there is no transitive dependecy in our result.