Normalize following table up to BCNF. In this example we extend the PropertyInspection database to include property inspection by members of staff. When staffs are required to undertake the inspections, they are allocated a company car for use on the day of the inspections. However, a car may be allocated to several members of staff as required throughout the working day. A member of staff may inspect several properties on a given date, but a property is only inspected once on a given date. StaffPropertyInspection
propertyNo iDate iTime pAddress comments staffNo sName carReg
PG4 18-Oct-00 10.00 6 Lawrence St Glasgow Need to replace crockery SG37 Ann Beech M231JGR
PG4 22-Apr-01 09.00 6 Lawrence St Glasgow In good order SG14 David Ford M533 HDR
PG4 1-Oct-01 12.00 6 Lawrence St Glasgow Damp rot in bathroom SG14 David Ford N721 HFR
PG16 22-Apr-01 13.00 6 Novar Dr. Glasgow Need to replace crockery SG14 David Ford M533 HDR
PG16 24-Oct-01 14.00 6 Novar Dr. Glasgow Good condition SG37 Ann Beech N721 HFR
a) Find all the candidate keys.
b) Determine the primary key
c) Find all the functional dependencies.
d) Get the first normal form
e) Get the second normal form
f) Get the third normal form g) Gent the BCNF normal form.