Project ID EmployeeName EmployeeSalary 100a Jones 64K 100a Smith 51K 100b Smith

Question

Project ID
EmployeeName
EmployeeSalary
100a Jones 64K 100a Smith 51K 100b Smith 51K 200a Jones 64K 200b Jones 64K 200c parks 28K 200c Smith 51K 200d Parks 28K PROJECT relation.
Assuming that all the functional dependencies and constraints areapparent in this data....

In what Normal form is Project?
Describe 2 modification anomalies from which Project suffers?
Does the relation contain a transitive dependency? If so what isit?
Redesign this relation to eliminate the modification anomalies

Thanks

Project ID
EmployeeName
EmployeeSalary
100a Jones 64K 100a Smith 51K 100b Smith 51K 200a Jones 64K 200b Jones 64K 200c parks 28K 200c Smith 51K 200d Parks 28K

Explanation / Answer

->Here consider ProjectID is the name of a workproject.     EmployeeName is the name of an employee whoworks on that project.     EmployeeSalary is the salary of theemployee whose name is EmployeeName.     Project is in first normalform because it has no multi-valued attributes. It is notin second normal form because it has a partial dependency. Key:ProjectID+EmployeeName but EmployeeName-> EmployeeSalary.     Project is in first normalform because it has no multi-valued attributes. It is notin second normal form because it has a partial dependency. Key:ProjectID+EmployeeName but EmployeeName-> EmployeeSalary. -> Modification anomalies are      InsertionAnomaly: You cannot add an Employee until the Employee isassigned to a Project. Likewise, you cannot add a Project until andEmployee is assigned to the Project.      Update Anomaly: Ifyou want to change Smith’s Salary you will need to changethree rows of data in order to change one Employee’ssalary.    Deletion Anomaly: If Parks didnot work on Project 200C and worked in Project 200D only, deletionof ProjectC would delete the fact that Park’s salary was28K.
->YES, the relation does contain a transitivedependency.
The key is ProjectID+EmployeeName butEmployeeName->EmployeeSalary.
->Redesign this relation to eliminate the modificationanomalies is PROJECT (ProjectID, EmployeeName) EMPLOYEE (EmployeeName, EmployeeSalary) ITS HELPFUL TO YOU......... ITS HELPFUL TO YOU.........

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.