Quick Contact

    Boyce Codd Normal Form (BCNF)

    Boyce Codd Normal Form (BCNF) is the advanced version of the third normal form. That’s why it is called as 3.5 Normal Forms.

    A table is in BCNF if each functional dependency P→Q,P is the super key of the table.

    For Boyce Codd Normal Form, the table should be in the third normal form, and for each functional dependency, L.H.S is super key.

    Consider a relation R (A, B, C) where the given FDs are AB→C,C→B. Then, an instance of this relation can be as follows.

    A B C
    a1 b2 c1
    a4 b2 c1
    a1 b3 c2
    a3 b3 c3

    Check for these redundancies in the relation. As the dependency,C→B holds, we know from the first tuple that when C is c1,and B is b2. Therefore, we can say that this information c_1,b_2is repeated in the second tuple. These repetitions had to be made to provide information a1,b2,c1 and a4,b2,c1.

    Check out that relation is in 3NF, and as C is the only candidate key in the relation, there is no chance that a non-candidate key is dependent on another non-candidate key.

    Normalization Procedure

    If we decompose the table such that no part of the candidate key becomes functionally dependent on a non-candidate key, then the redundancy is removed. Therefore, we decompose the relation as R1 (A,C) and R2 (B,C). Now, the given instance will become as follows

    A C
    a1 c1
    a4 c1
    a1 c2
    a3 c3
    With AC as the candidate key and
    B C
    b2 c1
    b3 c2
    b3 c3
    With C as the candidate key.

    Therefore, the redundancy is no longer present, and we say that the relation is in BCNF.

    How to certify that the relation is in BCNF?

    For a relation R to be in BCNF, one of the following conditions should hold for all dependencies: A→B in a relation where A and B are a set of attributes within the relation.

    • The dependencies A→B is trivial.
    • A is a super key of the relation R.

    Now, if none of these conditions hold for at least one of the dependencies A→B in R, we say that R is not in BCNF.

    When a relation has higher than one candidate key, anomalies can influence the outcome, yet the relation is in third normal form. Anomalies that occur when a table is unable to possess the property that each determinant is a candidate key lead to the Boyce-Codd Normal Form (BCNF). The following example cities the failure to maintain this property.

    EMP-CODE PID P-LEADER
    001 P-01 MG
    002 P0-2 JDR
    003 P-03 SS
    004 P-02 JDR
    005 P-01 MG
    Where
    EMP-CODE=Employee Code
    PID=Project Identification Number
    P-LEADER= Project Leader

    The only assumption is that a project leader manages every project. This is in 3NF because it has no partial functional dependencies and no transitive dependencies. It does not have the much-needed property that each determinant be a candidate key. A pair of attributes is determinant, EMP-CODE, and PID. Every one distinctive pair of values of EMP-CODE and PID establishes a value for the attributes which are unique, P-LEADER. Another determinant is the pair EMP-CODE and P-LEADER, which creates the values of the attributes that are unique, PID. Yet, another determinant is the attribute, P-LEADER. Each different value of P-LEADER determines a unique value of the attribute, PID. These observations about the relation R correspond to the real-world facts that each employee has a single project leader (P-LEADER) for each of his or her project, and just one project leader (P-LEADER) manages each project.

    It is to be examined whether these three determinants are candidate keys or not. The response is that the pair EMP-CODE and PID is a candidate key since each of them identifies a row in R uniquely. Similarly, the pair EMP-CODE and P-LEADER is a candidate key. Since the value MG appears in two rows of the P-LEADER column, the determinant P-LEADER is not a candidate key. Therefore, the relation R is unsuccessful in fulfilling the condition that every determinant in it is a candidate key.

    Even though R is in 3NF, there are still anomalies in it.

    Insertion Anomaly

    If we want to add a new project with a project leader, we cannot until we have an employee assigned to that project.

    Deletion Anomaly

    If we delete the employee“003”, we loss all information that SS manages the project“P03”.

    Updation Anomaly

    If AC replaces MG as project leader of project“P01”, we have to update multiple rows

    The problem occurs because there is a determinant that is not a candidate key.

    Now, we formulate the whole thing as follows.

    R(EMP-CODE,PID,P-LEADER)

    FDs:EMP-CODE,PID→P-LEADER

    P-LEADER→PID.

    By decomposition, we get

    R1(EMP-CODE,PID)

    R2(P-LEADER,PID)

    R1
    EMP-CODE PID
    001 P-01
    002 P-02
    003 P-03
    004 P-02
    005 P-01
    R2
    P-LEADER PID
    MG P-01
    JDR P-02
    SS P-03
    Difference between 3NF and BCNF
    3NF BCNF
    A relation is in 3NF if it is in 2NF and no non-prime attribute transitively depends on the primary key. In other words, a relation R is in third normal form if for each functional dependency P→Q in R at least one of the following conditions are met:

    • P is a key or super key in R
    • Q is a prime attribute in R
    A relation R is in BCNF if it is in third normal form and for each functional dependency P→Q in R, P is a key or superkey in R. In other terms, the only difference between 3NF and BCNF is that in BCNF it is not performing the second circumstances of the third normal form. This creates BCNF stricter than the third normal form as any relation that is in BCNF will be in the third normal form, but not certainly every relation that is in the third normal form will be in BCNF.
    Third Normal form can be recover without sacrificing all dependencies Dependencies cannot be protected in BCNF.
    Lossless decomposition can be managed in 3NF. Lossless decomposition is tough to manage in BCNF.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.