Question

Consider the following relation with structure PROJECT(ProjectID, EmployeeName, EmployeeSalary). ProjectID EmployeeName EmployeeSalary 100A Eric Jones 64,000 100A Donna Smith 70,000 100B Donna Smith 70,000 200A Eric Jones 64,000 200B Eric Jones 64,000 200C Eric Parks 58,000 200C Donna Smith 70,000 200D Eric Parks 58,000 Suppose that the following functional dependencies exist: (ProjectID, EmployeeName) → EmployeeSalary EmployeeName → EmployeeSalary Normalize this relation into BCNF. For this problem you only need to include table names, primary keys, and attributes as part of your solutions.

Answer 1

Answer:

Given table PROJECT

ProjectID	EmployeeName	EmployeeSalary
100A	Eric Jones	64,000
100A	Donna Smith	70,000
100B	Donna Smith	70,000
200A	Eric Jones	64,000
200B	Eric Jones	64,000
200C	Eric Parks	58,000
200C	Donna Smith	70,000
200D	Eric Parks	58,000

This table in the First Normal Form (1NF) and this table needs to normalize further.

Second Normal Form (2NF) :

• This table needs to normalize further to create new tables and remove partial dependancies.

1.Table Name :Employee

Schema :Employee(EmployeeName , EmployeeSalary)

FD :EmployeeName → EmployeeSalary

EmployeeName	EmployeeSalary
Eric Jones	64,000
Donna Smith	70,000
Eric Parks	58,000

2.Table Name :Project

Schema :Project(ProjectID, ProjectName)

FD:ProjectID→ ProjectName

ProjectID	ProjectName
100A	A
100B	B
200A	AA
200B	BB
200C	CC
200D	DD

Third Normal Form (3NF) :

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

1.Table Name :Employee

Schema :Employee(EmployeeName , EmployeeSalary)

FD :EmployeeName → EmployeeSalary

EmployeeName	EmployeeSalary
Eric Jones	64,000
Donna Smith	70,000
Eric Parks	58,000

2.Table Name :Project

Schema :Project(ProjectID, ProjectName)

FD :ProjectID→ ProjectName

ProjectID	ProjectName
100A	A
100B	B
200A	AA
200B	BB
200C	CC
200D	DD

3.Table Name :EmployeeProject

Schema: EmployeeProject(ProjectID, EmployeeName)

ProjectID	EmployeeName
100A	Eric Jones
100A	Donna Smith
100B	Donna Smith
200A	Eric Jones
200B	Eric Jones
200C	Eric Parks
200C	Donna Smith
200D	Eric Parks