Question

1. The following table stores details on employees and the projects they have worked on and the hours they spent on the project each week:                                      [15 Marks]

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

1. Explain why the above table is not in 2^nd normal form. Then normalise it to 2^nd normal form.
2. 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

Given table workload is in the first normal form.This table needs to normalize into second normal form.

Second Normal Form (2NF) :

• Table workload needs to normalize into second normal form to remove partial dependancy because some of the columns like
  • empname is depends upon column empID
  • likewise column projName is depends upon projID

hence below are tables in 2NF.

1.Table Name :Employee

Description :This table stores employee details like empID and empName

Schema :Employee(EmpID , EmpName)

FD :EmpID ==>EmpName

Below is the table data

EmpID	EmpName
E01	Smith
E02	Smith
E03	Robinson

2.Table Name :Project

Description :This table stores project details like projID and projName

Schema :Project(projID , projName)

FD :projID ==>projName

Below is the table data

projID	projName
P02	Database
P01	Web Portal

Third Normal Form (3NF) :

• Above table needs to normalize into 3NF to remove transitive dependancy.

Below are tables in 3NF.

1.Table Name :Employee

Description :This table stores employee details like empID and empName

Schema :Employee(EmpID , EmpName)

FD :EmpID ==>EmpName

Below is the table data

EmpID	EmpName
E01	Smith
E02	Smith
E03	Robinson

2.Table Name :Project

Description :This table stores project details like projID and projName

Schema :Project(projID , projName)

FD :projID ==>projName

Below is the table data

projID	projName
P02	Database
P01	Web Portal

3.Table Name :WORKLOAD

Description :This table stores project details like projID and employee working on that project like empID along with HoursPerWeek

Schema :WORKLOAD(EmpID,projID , HoursPerWeek)

FD :EmpID,projID ==>HoursPerWeek

Below is the table data

EmpID	projID	HoursPerWeek
E01	P02	10
E02	P01	5
E03	P02	20