Boyce-Codd Normal Form

Let's consider a relation StaffInterview that contains information of client interview by a member of staff. The members of staff involved in interviewing clients are allocated to a specific room on the day of interview. However, a roomm may be allocated to several members of staff as required throughout a working day.A client is only interviewed once on a given date, but may be requested to attend further interviews at later days.

Sample data:
clientNo iDate iTime staffNo roomNo
CR76 13-May-01 10:30 SG05 G101
CR56 13-May-01 12:00 SG05 G101
CR74 13-May-01 12:00 SG37 G102
CR56 01-Jul-01 10:30 SG05 G102

Functional Dependencies:

  1. clientNo, iDate -> iTime, staffNo, roomNo
  2. staffNo, iDate, iTime -> clientNo
  3. roomNo, iDate, iTime -> staffNo, clientNo
  4. staffNo, iDate -> roomNo
or if we choose shorter notation
  • A = clientNo
  • B = iDate
  • C = iTime
  • D = staffNo
  • E = roomNp,
    then the FDs will be
    1. A, B -> C, D, E
    2. D, B, C -> A
    3. E, B, C -> D, A
    4. D, B -> E
    Let's choose the primary key. First we need to find all candidate keys:
  • (A,B)+= A, B, C, D, E
  • (D, B, C)+= A, B, C, D, E
  • (E, B, C)+= A, B, C, D, E
  • (D, B)+= B, E, D
    as you can see we have three possible candidate keys:
  • A,B
  • D, B, C
  • E, B, C
    we'll choose A,B to be the primary key.

    Now, having the primary key we can check that this relation is in 3NF. However, even being in 3NF this relation has update anomalies (Ex: try to change a room number). The reason for this is this relation is not in BCNF because FD 4 violates its requirements.

    To transform the ClientInterview relation into BCNF, we must remove the violating FD by creating new realtions:

  • R1 = (D, B)+
  • R2 = (A, B, C, D)
    that is we decompose the original table into two tables:

    Interview:
    clientNo iDate iTime staffNo
    CR76 13-May-01 10:30 SG05
    CR56 13-May-01 12:00 SG05
    CR74 13-May-01 12:00 SG37
    CR56 01-Jul-01 10:30 SG05
    StaffRoom:
    iDate staffNo roomNo
    13-May-01 SG05 G101
    13-May-01 SG37 G102
    01-Jul-01 SG05 G102

    Warning: It may not always be desirable to transform a relation into BCNF, for example, if there is a Functional Dependency that is not presented when we perform the decomposition (that is the determinant and the attributes it determines ar eplaced in different relations).
    Note: we lost FD 3 after the decomposition.