Consider the following relation with structure PROJECT(ProjectID, EmployeeName, EmployeeSalary). ProjectID EmployeeName EmployeeSalary 100A Eric Jones 64,000 100A...

Free

70.2K

Verified Solution

Question

Programming

Consider the following relation with structurePROJECT(ProjectID, EmployeeName, EmployeeSalary). ProjectIDEmployeeName EmployeeSalary 100A Eric Jones 64,000 100A Donna Smith70,000 100B Donna Smith 70,000 200A Eric Jones 64,000 200B EricJones 64,000 200C Eric Parks 58,000 200C Donna Smith 70,000 200DEric Parks 58,000 Suppose that the following functionaldependencies exist: (ProjectID, EmployeeName) â†’ EmployeeSalaryEmployeeName â†’ EmployeeSalary Normalize this relation into BCNF.For this problem you only need to include table names, primarykeys, and attributes as part of your solutions.

Answer & Explanation Solved by verified expert

4.2 Ratings (709 Votes)

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

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member