Review of Normalization (1NF to BCNF)

We consider a relation that contains information about property inspection by a member of staff. That relation sutisfies the following conditions:
StaffPropertyInspection
propNo propAddr {iDate, iTime, comments, staffNo, sName, carReg}
PG4 6 Lawrence St, Glasgow {18-Oct-00, 10:00, Need to replace crockery, SG37, Ann Beech, M231 JGR}
{22-Apr-01, 09:00, In good order, SG14, David Ford, M533 HDR}
{01-Oct-01, 12:00, Damp rot in bathroom, SG14, David Ford, N721 HFR}
PG16 5 Novar Dr, Glasgow {22-Apr-01, 13:00, Replace living room carpet, SG14, David Ford, M533 HDR}
{24-Oct-01, 14:00, Good condition, SG37, Ann Beech, N721 HFR}

1NF

This relation is obviously not in 1NF. To transform it to 1Nf we have to get rid ov multi-valued cells and split composite attributes into atomic attributes. The result of this process is shown in the table:
StaffPropertyInspection
propNo propAddr iDate iTime comments staffNo sName carReg
PG4 6 Lawrence St, Glasgow 18-Oct-00 10:00 Need to replace crockery SG37 Ann Beech M231 JGR
PG4 6 Lawrence St, Glasgow 22-Apr-01 09:00 In good order SG14 David Ford M533 HDR
PG4 6 Lawrence St, Glasgow 01-Oct-01 12:00 Damp rot in bathroom SG14 David Ford N721 HFR
PG16 5 Novar Dr, Glasgow 22-Apr-01 13:00 Replace living room carpet SG14 David Ford M533 HDR
PG16 5 Novar Dr, Glasgow 24-Oct-01 14:00 Good condition SG37 Ann Beech N721 HFR

2NF

In order to find out is the relation in 2NF we first need to find functional dependencies of the relation:
  1. propNo, iDate -> iTime, comments, staffNo, sName, carReg
  2. propNo -> propAddr
  3. staffNo -> sName
  4. staffNo, iDate -> carReg
  5. carReg, iDate, iTime -> propNo, propAddr, comments, staffNo, sName
  6. staffNo, iDate, iTime -> propNo, propAddr, comments
Knowing functional dependencies we can find all candidate keys:
  • propNo, iDate
  • carReg, iDate, iTime
  • staffNo, iDate, iTime
    and choose the primary key propNo, iDate.

    Having the primary key we can see that FD 2 violates 2NF. We need to remove attribute propAddr from the relation to satisfy 2NF. Please note that although the determinant of FD 4 only requires the iDate attribute of the primary key, we do not remove this dependency at this stage because the determinant also includes another non-primary-key attribute.

    The StaffPropertyInspection relation is transformed into 2NF by decomposing into two new relations:

  • Property (propNo, propAddr)
  • PropertyInspection (propNo, iDate, iTime, comments, staffNo, sName, carReg)

    PropertyInspection
    propNo iDate iTime comments staffNo sName carReg
    PG4 18-Oct-00 10:00 Need to replace crockery SG37 Ann Beech M231 JGR
    PG4 22-Apr-01 09:00 In good order SG14 David Ford M533 HDR
    PG4 01-Oct-01 12:00 Damp rot in bathroom SG14 David Ford N721 HFR
    PG16 22-Apr-01 13:00 Replace living room carpet SG14 David Ford M533 HDR
    PG16 24-Oct-01 14:00 Good condition SG37 Ann Beech N721 HFR
    Property
    propNo propAddr
    PG4 6 Lawrence St, Glasgow
    PG16 5 Novar Dr, Glasgow

    3NF

    The normalization of 2NF relations to 3NF involves the removal of transitive dependencies. The functional dependencies whithin the Property and PropertyInspection relations are as follows: Property relation does not have any transitive dependencies and therefore it is already in 3NF. However, sName is also transitively dependent on staffNo (represented as FD 2). We also note the functional dependency FD 3, which has a non-primary-key attribute carReg partially dependent on a non-primary-key attribute, staffNo. We do not remove that dependency at this stage as part of the determinant for this dependency includes a primary-key attribute, namely iDate. In other words, this dependency is not wholly transitively dependent on non-primary-key attributes and therefore does not violate 3NF.

    To transform the PropertyInspection relation into 3NF, we remove the transitive dependency (staffNo -> sName) by creating two new relations:

  • Staff (staffNo, sName)
  • PropertyInspec (propNo, iDate, iTime, comments, staffNo, carReg)
    Thus, the initial relation StaffPropertyInspection has been transformed into three relations in 3NF with the following form:
  • Property (propNo, propAddr)
  • Staff (staffNo, sName)
  • PropertyInspect (propNo, iDate, iTime, comments, staffNo, carReg)

    PropertyInspect
    propNo iDate iTime comments staffNo carReg
    PG4 18-Oct-00 10:00 Need to replace crockery SG37 M231 JGR
    PG4 22-Apr-01 09:00 In good order SG14 M533 HDR
    PG4 01-Oct-01 12:00 Damp rot in bathroom SG14 N721 HFR
    PG16 22-Apr-01 13:00 Replace living room carpet SG14 M533 HDR
    PG16 24-Oct-01 14:00 Good condition SG37 N721 HFR
    Property
    propNo propAddr
    PG4 6 Lawrence St, Glasgow
    PG16 5 Novar Dr, Glasgow
    Staff
    staffNo sName
    SG37 Ann Beech
    SG14 David Ford

    BCNF

    To examine the PropertyInspect, Staff, and Property relations to determine whether they are in BCNF, we simply identify all determinants and make sure they are candidate keys: We can see that the only 3NF relation that violates BCNF is PropertyInspect because of the FD 2. As a consequence the PropertyInspect relation can suffer from update anomalies. Let's remove this dependency:
  • StaffCar (staffNo, iDate, carReg)
  • Inspection (propNo, iDate, iTime, comments, staffNo)
    The StaffCar and Inspection relations are in BCNF.

    The resulting BCNF relations have the following form:

  • Property (propNo, propAddr)
  • Staff (staffNo, sName)
  • StaffCar (staffNo, iDate, carReg)
  • Inspection (propNo, iDate, iTime, comments, staffNo)

    Inspection
    propNo iDate iTime comments staffNo
    PG4 18-Oct-00 10:00 Need to replace crockery SG37
    PG4 22-Apr-01 09:00 In good order SG14
    PG4 01-Oct-01 12:00 Damp rot in bathroom SG14
    PG16 22-Apr-01 13:00 Replace living room carpet SG14
    PG16 24-Oct-01 14:00 Good condition SG37
    Property
    propNo propAddr
    PG4 6 Lawrence St, Glasgow
    PG16 5 Novar Dr, Glasgow
    Staff
    staffNo sName
    SG37 Ann Beech
    SG14 David Ford
    StaffCar
    staffNo iDate carReg
    SG37 18-Oct-00 M231 JGR
    SG14 22-Apr-01 M533 HDR
    SG14 01-Oct-01 N721 HFR
    SG37 24-Oct-01 N721 HFR