Quick Contact

    Second Normal Form (2NF)

    Fully Functional Dependency forms the basis of the DBMS second normal form (2NF). If each non-prime attribute in relation is dependent functionally on the candidate key of the relation, a relational schema is said to be in 2NF. Therefore, if each non-prime attribute in relation is not partially dependent on any key of the relation, the relation schema is in 2NF.

    Normalization Procedure

    Test left-hand side attributes as a component of the main key. If an attribute is dependent on parts of the multi-valued key, transfer it to various tables.

    Second Normal Form (2NF)

    Where the composite attribute (A, B) is the main key. Assume that the following functional dependencies exist:

    (A,B)→C

    i.e., complete functional dependence on the composite keys (A,B).

    B→D

    i.e., part functional dependence on the composite keys (A,B).

    The following steps are:

    Step1: A new relation R2 from R is created due to the functional dependency
    B→D, R2, which contains B and D as attributes. B, the determinant, becomes the key to R2.

    Step2: Minimize the original relation R by eliminating the attribute on the right-hand side of B→D from it. The minimized relation R1 thus, has all the original attributes. However, D is absent.

    Step3: If one relation contains a similar determinant with the other relation, the dependent attributes of the relation to be non-key attributes are placed in the other relation for whom the determinant is a key.

    Second Normal Form (2NF)

    Thus, “A relation R is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on primary key”.

    Reduction of 1NF to 2NF can be represented diagrammatically as follows.

    Second Normal Form (2NF)
    Example

    Consider the following relation

    EMP
    EMP-NO EMP-NAME PROJ-ID PROJ-NAME PROJ-LOC HRS-WORKED
    001 Rishabh P-01 HMS Mumbai 20
    002 Jyoti P-02 ERP Delhi 22
    003 Sourav P-01 HMS Mumbai 20
    004 Gautam P-03 SIS Noida 17
    005 Rohan P-02 ERP Delhi 22
    INSERT

    We introduce a new employee who has not been assigned to any project yet. We cannot do so unless he/she has been assigned to a project.

    EMP-NO EMP-NAME PROJ-ID PROJ-NAME PROJ-LOC HRS-WORKED
    001 Rishabh P-01 HMS Mumbai 20
    002 Jyoti P-02 ERP Delhi 22
    003 Sourav P-01 HMS Mumbai 20
    004 Gautam P-03 SIS Noida 17
    005 Rohan P-02 ERP Delhi 22
    006 Rajiv ? ? ? ?

    Entity Integrity Violation: PROJ-ID is a part of Primary Key

    DELETE

    If the data about a project is deleted, the information about the employees who worked on that project is also deleted. If we delete the data about the project “P01”, information about employees E001 and E003 will be deleted.

    EMP-NO EMP-NAME PROJ-ID PROJ-NAME PROJ-LOC HRS-WORKED
    001 Rishabh Deletion Anomaly
    002 Jyoti P-02 ERP Delhi 22
    003 Sourav Deletion Anomaly
    004 Gautam P-03 SIS Noida 17
    005 Rohan P-02 ERP Delhi 22
    UPDATE

    If the project location for a particular project has been changed and we have updated the change only in a single tuple, this will lead to an update anomaly due to partial update resulting in data inconsistencies.

    Although it is normalized to 1NF, the EMP relation still contains storage anomalies. Partial dependency on the primary key leads to violations of the database integrity and consistency rules.

    The determinant (EMP-NO, PROJ-ID) is the composite key of the EMP relation, and its value will establish the value of other non-key attributes in a tuple of the relation uniquely. Note that while Hrs_worked is fully functionally dependent on all of the (EMP-NO, PROJ-ID),EMP-NAME is partially functionally dependent on the composite key (as each of them depends on the EMP-NO part of the key only but not on PROJ-ID). PROJ-NAME and PROJ-LOC are only partially functionally dependent on the composite key (as they each depend on the PROJ-ID part of the key but not on EMP-NO).

    Functional Dependencies

    FD1:{EMP-NO,PROJ-ID}→HRS-WORKED &nbsp &nbsp &nbsp &nbsp PRIMARY KEY

    FD2:EMP-NO→EMP-NAME &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Partial Dependency

    FD3:PROJ-ID→PROJ-NAME,PROJ-LO &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp Partial Dependency

    These issues can be kept away by removing partial key dependence in place of full functional dependence, and this can be done by making dependencies separate.

    This is as follows

    ASSIGN: Considering FD1

    EMP-NO PROJ-ID HRS-WORKED

    EMP: Considering FD2

    EMP-NO EMP-NAME

    PROJECT: Considering FD3

    PROJ-ID PROJ-NAME PROJ-LOC

    The source relation is divided into three relations and every resultant relation is no longer dependent on partial key dependencies.

    ASSIGN
    EMP-NO PROJ-ID HRS-WORKED
    001 P-01 20
    002 P-02 22
    003 P-01 20
    004 P-03 17
    005 P-02 22
    EMP
    EMP-NO EMP-NAME
    001 Rishabh
    002 Jyoti
    003 sourav
    004 Gautam
    005 Rohan
    PROJECT
    PROJ-ID PROJ-NAME PROJ-LOC
    P-01 HMS Mumbai
    P-03 ERP Delhi
    P-03 SIS Noida

    There are two relations in the second normal form now. Test these operations on the resultant relations.

    Updation Anomaly

    Redundant/duplicate tuples are not present in the relation, and the updates are completed in one place without worrying for inconsistencies in the database.

    Insertion Anomaly

    Adding a new employee can be done in the EMP relation without being concerned with whether he/she is assigned to a project or not.

    Deletion Anomaly

    When a tuple is deleted in PROJECT, it does not lead to information loss on the details of all the employees.

    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.