12.3: Boyce-Codd Normal Form
- Page ID
- 92194
When a table has more than one candidate key, anomalies may result even though the relation is in 3NF. Boyce-Codd normal form is a special case of 3NF. A relation is in BCNF if, and only if, every determinant is a candidate key.
BCNF Example 1
Consider the following table (St_Maj_Adv).
Student_id | Major | Advisor |
111 | Physics | Smith |
111 | Music | Chan |
320 | Math | Dobbs |
671 | Physics | White |
803 | Physics | Smith |
The semantic rules (business rules applied to the database) for this table are:
- Each Student may major in several subjects.
- For each Major, a given Student has only one Advisor.
- Each Major has several Advisors.
- Each Advisor advises only one Major.
- Each Advisor advises several Students in one Major.
The functional dependencies for this table are listed below. The first one is a candidate key; the second is not.
- Student_id, Major ——> Advisor
- Advisor ——> Major
Anomalies for this table include:
- Delete – student deletes advisor info
- Insert – a new advisor needs a student
- Update – inconsistencies
Note: No single attribute is a candidate key.
PK can be Student_id, Major or Student_id, Advisor.
To reduce the St_Maj_Adv relation to BCNF, you create two new tables:
- St_Adv (Student_id, Advisor)
- Adv_Maj (Advisor, Major)
St_Adv table
Student_id | Advisor |
111 | Smith |
111 | Chan |
320 | Dobbs |
671 | White |
803 | Smith |
Adv_Maj table
Advisor | Major |
Smith | Physics |
Chan | Music |
Dobbs | Math |
White | Physics |
BCNF Example 2
Consider the following table (Client_Interview).
ClientNo | InterviewDate | InterviewTime | StaffNo | RoomNo |
CR76 | 13-May-02 | 10.30 | SG5 | G101 |
CR56 | 13-May-02 | 12.00 | SG5 | G101 |
CR74 | 13-May-02 | 12.00 | SG37 | G102 |
CR56 | 1-July-02 | 10.30 | SG5 | G102 |
FD1 – ClientNo, InterviewDate –> InterviewTime, StaffNo, RoomNo (PK)
FD2 – staffNo, interviewDate, interviewTime –> clientNO (candidate key: CK)
FD3 – roomNo, interviewDate, interviewTime –> staffNo, clientNo (CK)
FD4 – staffNo, interviewDate –> roomNo
A relation is in BCNF if, and only if, every determinant is a candidate key. We need to create a table that incorporates the first three FDs (Client_Interview2 table) and another table (StaffRoom table) for the fourth FD.
Client_Interview2 table
ClientNo | InterviewDate | InterViewTime | StaffNo |
CR76 | 13-May-02 | 10.30 | SG5 |
CR56 | 13-May-02 | 12.00 | SG5 |
CR74 | 13-May-02 | 12.00 | SG37 |
CR56 | 1-July-02 | 10.30 | SG5 |
StaffRoom table
StaffNo | InterviewDate | RoomNo |
SG5 | 13-May-02 | G101 |
SG37 | 13-May-02 | G102 |
SG5 | 1-July-02 | G102 |