Lab 2 Functional dependencies and Normal forms Exercise 1. You have been given a form which shows the details of technical jobs carried out for different schools by members of staff in the local college. You will note that one job may require input from more than one member of staff. Each member of staff belongs to a department within the college and the number of hours worked on each job along with the date the job was undertaken are noted. A member of staff may work on more than one job on any given date. The college would like to set up a relational database to manage the allocation of staff to jobs and keep a more efficient record of the number of hours worked for each school within the college a. Give the list of functional dependencies b. Assume that at the beginning, the database contain only one relation R(TechNo, TechName, DeptID, Department, JobNo, Date, SchoolID, School, Hoursworked). Normalize the database to INF, 2NF and 3NF (specify your responses step by step, not only give the results). c. Assume that at the beginning, the database contain only one relation R(TechNo, TechName, DeptID, Department, JobNo, Date, SchoolID, School, Hoursworked). Normalize the database to BCNF by using the BCNF decomposition algorithm. TechNo TechName Deptid Department JobNo Date SchoolD School Hoursworked 298 Walls, lan 1 Engineering 1002 12/9/07 192 Beauty 2.5 1 298 Walls, lan 1 Engineering 1003 12/9/07 125 Computing 5.5 345 Smith, Alan 2 Helpdesk 1002 12/9/07 192 Beauty 3 345 Smith, Alan 2 Helpdesk 3440 18/9/07 346 Bus & Man 1.5 345 Smith, Alan 2 Helpdesk 1480 15/9/07 192 Beauty 3 380 60 3 Labs 3670 19/9/07 560 Jackson, Bob Design 4 380 3 Labs 3440 18/9/07 346 Bus & Man Jackson, Bob 2 300 Jones, Joe 1450 Computing 1 Helpdesk Page 11 2 Helpdesk 16/9/07, 125 + 15/9/07 192 300 Jones, Joe 1480 Beauty 3 320 Young, Jill 3 Labs 3440 18/9/07 346 Bus & Man 4 320 Young, Jill 3 3 Labs 1002 12/9/07 192 Beauty 2.5 320 Young, Jill 3 Labs 3670 19/9/07 560 Design 2