For this question, we extend DreamHome case study to include property inspection by members of staff....

  

Transcribed Image Text:

For this question, we extend DreamHome case study to include property inspection by members of staff. When staff are required to undertake these 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 inspected only once on a given date. property No Date PG4 PG4 PG4 PG16 PG16 18-Oct-18 (Time 10.00 22-Apr-19 09.00 1-Oct-19 12.00 22-Apr-19 13.00 24-Oct-19 14.00 PAddress 10 Main St. Houston 10 Main St. Houston 10 Main St. Houston 5 Ball Dr. Austin 5 Ball Dr. Austin comments staffNo SG37 Need to replace crockery In good order Damp rot in bathroom Replace living room carpet Good condition SG14 SG14 SG14 SG37 Name Ann Beech David Ford David Ford David Ford Ann Beech carReg M231 JGR M533 HDR N721 HFR M533 HDR N721 HFR (1) Identify the functional dependencies that exist between the columns of the table and identify the primary key for the table. (10 pts) (2) Apply normalization to 2NF and 3NF on the tables, respectively. Please identify the primary key and foreign key for each table after normalization. (18 pts) For this question, we extend DreamHome case study to include property inspection by members of staff. When staff are required to undertake these 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 inspected only once on a given date. property No Date PG4 PG4 PG4 PG16 PG16 18-Oct-18 (Time 10.00 22-Apr-19 09.00 1-Oct-19 12.00 22-Apr-19 13.00 24-Oct-19 14.00 PAddress 10 Main St. Houston 10 Main St. Houston 10 Main St. Houston 5 Ball Dr. Austin 5 Ball Dr. Austin comments staffNo SG37 Need to replace crockery In good order Damp rot in bathroom Replace living room carpet Good condition SG14 SG14 SG14 SG37 Name Ann Beech David Ford David Ford David Ford Ann Beech carReg M231 JGR M533 HDR N721 HFR M533 HDR N721 HFR (1) Identify the functional dependencies that exist between the columns of the table and identify the primary key for the table. (10 pts) (2) Apply normalization to 2NF and 3NF on the tables, respectively. Please identify the primary key and foreign key for each table after normalization. (18 pts)

Answer rating: 100% (QA)

1The functional dependencies are propertyNo time addres... View the full answer