Question

1. Simple Technology, a Kansas based company, is working on a project to design and implement a database for tracking employees, their personal information, and information about projects. Some of the attributes that they want to store in the database is given as a tabular format below PC de PTitle PM nager PBudget PDept PD ation l Num EN Ed ity, EZ ED pt ED ptam eHRate P1 Payroll Martin 100K Accounting 12 12 P1 Payroll Martin 100K Accounting12 16 16 16 24 24 E13 A Smith Liberty 64068 D4 E30 LJones Liberty 64068 D3 E31 P Jouet Liberty 64069 D4 E11 R Ali Liberty 64069 E13 A Smith Liberty 64068 D4 E9 S Gilbert Bucknerl 64076 D5 Management 25.5 E9 S Gilbert Buckner 64076 D5 Management 23.25 E31 P Jouet Liberty 64069 D4 E10 | K Ross Buckner 64076, D8 E27 A Smith Buckner 64076D9 Operation 16.5 R&D IT R&D R&D R&D P1 Payroll Martin 100K Accounting 18.5 21 P2 Budget Baker 780K Finance P2 Budget P2 Budget Baker 780KFinance P3 P3 D4 Baker780KFinance 18 Hirin Lewis100K Lewis 100K HR HR Hiring R&D Sales P4 Millennium Lewis 780K Finance P5 Millennium Martin 20OK HR a) b) Write all functional dependencies for the above relation. (40 points) Using step-by-step process, normalize the above relation to BCNF. Make sure you go through all normalization steps from INF to BCNF. Show your work. Your work must include the following: Indicate all anomalies lead to the violation of each normalization conditions List schemas for the final set of relations that are normalized to BCNF. Make sure to indicate all the primary, foreign, and surrogate keys (if used) in the final schemas. Indicate referential integrity constraint for the final set of schemas. i. ii. iii.

write the answer clearly thank you so much

Answer 1