Skip to main content
Engineering LibreTexts

12.3: Boyce-Codd Normal Form

  • Page ID
    92194
  • \( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \) \( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)\(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\) \(\newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\) \( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\) \( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\) \( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\) \( \newcommand{\Span}{\mathrm{span}}\)\(\newcommand{\AA}{\unicode[.8,0]{x212B}}\)

    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:

    1. Each Student may major in several subjects.
    2. For each Major, a given Student has only one Advisor.
    3. Each Major has several Advisors.
    4. Each Advisor advises only one Major.
    5. 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.

    1. Student_id, Major ——>  Advisor
    2. Advisor  ——>  Major

    Anomalies for this table include:

    1. Delete – student deletes advisor info
    2. Insert – a new advisor needs a student
    3. 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:

    1. St_Adv (Student_id, Advisor)
    2. 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

    12.3: Boyce-Codd Normal Form is shared under a not declared license and was authored, remixed, and/or curated by LibreTexts.

    • Was this article helpful?