Quick Contact

    Third Normal Form (3NF)

    A relation will be in the DBMS Third Normal Form (3NF) if it is in the 2NF and not include some transitive partial dependency.

    3NF is used to manage data integrity and reduce the duplication of data.

    A relation is in third normal form if and only if some of the subsequent conditions will satisfy every non-trivial functional dependency P→Q.

    • P is a super key or candidate key.
    • Q is a prime attribute, i.e., Q is a part of the candidate key.

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

    A B C
    a1 b1 c1
    a2 b1 c1
    a3 b2 c2

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

    Check out that relation is in 2NF, and both B and C are fully functionally dependent on the candidate key A.

    Normalization Procedure

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

    A B
    a1 b1
    a2 b1
    a3 b2
    With A as the candidate key and
    B C
    b1 c1
    b2 c2
    With B as the candidate key.

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

    Example

    Consider the following EMP relation

    EMP-NO EMP-NAME DESG SALARY DEPT-NO DEPT-NAME
    001 Roshni Manager 25000 D01 Administration
    002 Gaurav Programmer 15000 D03 Project
    003 Raman Finance Officer 12000 D02 Personnel
    004 Amit System Analyst 20000 D03 Project
    UPDATE

    Can we change the employee servicing in department “D03” in the PROJECT department. In this case, many employees belong to department “D03” (e.g., Gaurav and Amit). Thus, we must make sure that all tuples are updated, or there will be a problem with database inconsistency.

    INSERT

    Suppose we want to add a new employee in the EMP relation with EMP-NO as its primary key. The new tuple must also include correct information about the department where he will be working. If not, the data will become inconsistent.

    EMP-NO EMP-NAME DESG SALARY DEPT-NO DEPT-NAME
    001 Roshni Manager 25000 D01 Administration
    002 Gaurav Programmer 15000 D03 Project
    003 Raman Finance Officer 12000 D02 Personnel
    004 Amit System Analyst 20000 D03 Project
    005 Janvi Programmer 22000 NULL NULL

    If we want to add a new department and no employees are assigned there, then as we do not know the information about the employees, we will have to put the NULLs as their attributes, including the primary key, which is not permitted.

    EMP-NO EMP-NAME DESG SALARY DEPT-NO DEPT-NAME
    001 Roshni Manager 25000 D01 Administration
    002 Gaurav Programmer 15000 D03 Project
    003 Raman Finance Officer 12000 D02 Personnel
    004 Amit System Analyst 20000 D03 Project
    NULL NULL NULL NULL D01 Sales
    DELETE

    If we want to delete information from the employee whose employee code is “003” in the EMP instance as shown, then we would loss information concerning the department “D02”.

    EMP-NO EMP-NAME DESG SALARY DEPT-NO DEPT-NAME
    001 Roshni Manager 25000 D01 Administration
    002 Gaurav Programmer 15000 D03 Project
    003 Raman Finance Officer 12000 D02 Personnel
    004 Amit System Analyst 20000 D03 Project

    Instance of EMP after deleting the tuple of EMP-“003”

    The given an example makes it clear that despite of a relation existing in 2NF, the issue can still occur, and they should be removed. They need to be normalized further, i.e., there is a need for a third normal form to remove these anomalies.

    Examine the functional dependencies of the above example, the presence of “transitive” dependencies are evident.

    Here, EMP-NO→DEPT-NAME is only a transitive or an indirect dependency. It is treated as indirect as EMP-NO→DEPT-NO and DEPT-NO→DEPT-NAME and thus EMP-NO→DEPT-NAME.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.