The company undertakes regular inspections of the property by members of staff'

1
Property Inspection Report

• The company undertakes regular inspections of the
  property by members of staff.
• The staff are allocated a company car for use for
  the inspection periods on the day of the
  inspection.
• A car may be allocated to several members of
  staff on a given date.
• A staff member may inspect several properties on
  a given date.
• A property is only inspected once on a given date.

2
The Attributes
pno, paddress, idate, itime, comments, sno,
sname, car_reg
3
Functional Dependencies
pno, idate ? itime, comments, sno, sname,
car_reg pno ? paddress sno ? sname sno, idate ?
car_reg car_reg, idate, itime ? pno, paddress,
comments, sno, sname sno, idate, itime ?pno,
paddress, comments
4
Candidate Keys

• (pno, idate)
• (sno, idate, itime)
• (car_reg, idate, itime)

5
From 1NF to 2NF

• Remove partial dependencies on the primary key.
• pno ? paddress Property Table
• sno, idate ? car_reg
• car_reg, idate, itime ? pno, paddress, comments,
  sno, sname
• sno, idate, itime ?pno, paddress, comments

6
From 2NF to 3NF

• Remove transitive dependencies
• pno, idate ? sno ? sname Staff Table
• sno, idate ? car_reg
• car_reg, idate, itime ? pno, paddress, comments,
  sno, sname
• sno, idate, itime ?pno, paddress, comments

7
From 3NF to BCNF

• All determinants need to be candidate keys.
• sno, idate ? car_reg Staff_Car Table

8
ER Diagram
staff
Inspect
property
car
9
ER Diagram
staff
uses
Inspect
property
car
Drives to