Quick Contact

    DBMS First Normal Form (1NF)

    A relation is sent to be in particular normal form if it satisfies a certain specified set of constraints.

    Database Normalization is classified into the following Normal forms:

    DBMS First Normal Form (1NF)
    First Normal Form (1NF)

    A relation is, in first normal form if and only if all underlying domains contain atomic values only.

    The aim of the first normal form (1NF) is to make the structure of a relation simple by making sure that it does not have data aggregates or repeating groups. This means that none of the attribute values can contain a collection of values.

    It specifies that the domains of attributes are inclusive of only atomic values and that any attribute value in a tuple must be an individual value from the domain of that attribute.

    Normalization Procedure

    The following table is 1NF.

    A B C
    x 1 {a,b}
    z 2 c
    Transformation to 1NF
    A B
    x 1
    z 2

    Method1: By splitting the relation the relations into new relations.

    B C
    1 a
    1 b
    2 c

    Method2: By adding new tuples in the same relation.

    A B C
    x 1 a
    x 1 b
    z 2 c

    Method3: By adding new attributes with NULL values.

    A B C C’
    x 1 a b
    z 2 c NULL

    In the following example, anyone employee has more than one telephone number.

    EMP
    EMP-CODE EMP-NAME TEL-NO
    001 Mohan 9324567890
    8732456210
    002 Rajesh 8234786419
    9176329056
    003 Saksham 9023431784
    8843874218

    This is thus not in 1NF. This can be managed by ensuring that every tuple defines a single entity by containing only atomic values. One can either reorganize into one relation as in:

    EMP
    EMP-CODE EMP-NAME TEL-NO
    001 Mohan 9324567890
    001 Mohan 8732456210
    002 Rajesh 8234786419
    002 Rajesh 9176329056
    003 Saksham 9023431784
    003 Saksham 8843874218

    Or split into multiple relations as in:

    EMP
    EMP-CODE EMP-NAME
    001 Mohan
    002 Rajesh
    003 Saksham
    EMP-TEL
    EMP-CODE TEL-NO
    001 9324567890
    001 8732456210
    002 8234786419
    002 9176329056
    003 9023431784
    003 8843874218

     

    Reduction to 1NF

     

    Example

    Consider the relation FIRST (S#, STATUS, CITY, P#, QTY) as shown in table.

    FIRST
    S# STATUS CITY P# QTY
    S1 40 Delhi P1 100
    S1 40 Delhi P2 125
    S1 40 Delhi P3 130
    S1 40 Delhi P4 115
    S2 10 Noida P1 200
    S2 10 Noida P2 215
    S3 20 Karnal P1 200
    S4 40 Delhi P4 200
    S4 40 Delhi P5 300

    Here we assume that each supplier has a unique supplier number (S#), precisely one status code and location. A supplier can supply different parts. Further, we assume that STATUS is functionally dependent on CITY. Therefore all Delhi suppliers must have a status of 40.

    Figure display the functional dependency structure for this relation. The key to FIRST relation is the combination of (S#, P#).

    DBMS First Normal Form (1NF)

    It is clear that the relation FIRST is in the first normal form. The relation FIRST has the following modification anomalies.

    1. Deletion Anomaly

      If we delete the FIRST tuple with S# value S3 and P# value P1, we lose the information that S3 is located in Karnal. Hence, the relation FIRST has a deletion anomaly.

    2. Insertion Anomaly

      We cannot enter the fact that a particular supplier is located in a specific supplier city until the supplier supplies at least one part. So, we cannot enter the fact that supplier S5 is located in Rohtak until S5 supplies at least one part.

    3. Updation Anomaly

      It supplies S1 moves from Delhi to Chandigarh, and we are faced with either the problem of searching the FIRST relation to find every tuple connecting S1 and Delhi and change it or the possibility of producing an inconsistent result (the city for S1 may be given as Delhi in one place and Chandigarh in another).

      The solution to these problems is to restore the relation FIRST by two relation SECOND (S#, STATUS, CITY) and SP (S#, P#, QTY). The figure shows sample tabulation corresponding to the data values.

    SECOND
    S# STATUS CITY
    S1 40 Delhi
    S2 10 Noida
    S3 20 Karnal
    S4 40 Delhi
    SP
    S# P# CITY
    S1 P1 100
    S1 P2 125
    S1 P3 130
    S1 P4 115
    S2 P1 200
    S2 P2 215
    S3 P1 200
    S4 P4 200
    S4 P5 300
    DBMS First Normal Form (1NF)
    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.