Normal Forms 

The normal forms defined in relational database theory represent guidelines for database design. The normalization rules are designed to prevent update anomalies and data inconsistencies.

Normalization rules are divided into following normal form :

  1. First Normal Form (1 NF)
  2. Second Normal Form (2 NF)
  3. Third Normal Form (3 NF)
  4. Boyce-Codd Normal Form (BCNF)
  5. Multivalued Dependencies and Fourth Normal Form (4 NF)
  6. Join Dependencies and Fifth Normal Form (5 NF)

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

First Normal Form (1 NF) :

A relation is in first Normal Form if and only if all underlying domains contain atomic values only. In other words, a relation doesn’t have multivalued attributes.

For example : Consider a STUDENT(Sid, Sname, Cname) relation

Student :
SID Sname Cname
S1 A C,C++
S2 B C++,DB
S3 A DB
SID : Primary Key
Due to occurrence of MVA, the above relation is not in 1 NF.
Solution :
Removal of MVA by inserting more rows
Student :
SID Sname Cname
S1 A C
S1 A C++
S2 B C++
S2 B DB
S3 A DB
SID : Primary Key
The relation is in 1nF
Another Example :

Consider another relation Supplier(SID, Status, City, PID, Qty).

Supplier :
SID Status City PID Qty
S1 30 Delhi P1 100
S1 30 Delhi P2 125
S1 30 Delhi P3 200
S1 30 Delhi P4 130
S2 10 Karnal P1 115
S2 10 Karnal P2 250
S3 40 Rohtak P1 245
S4 30 Delhi P4 300
S4 30 Delhi P5 315
Key : (SID, PID)

Let us assume each supplier has unique SID, and have exactly one Status code and Location(City) and further, Status is functionally dependent on City. A supplier can supply different parts(PID).
Key of Supplier relation is the combination of (SID, PID). The Functional Dependency Diagram for Supplier relation is shown as :

Supplier FDD 1NF- Normal Forms

As there are no multivalued attributes(MVA), hence the Supplier relation is already in 1 NF.
But the Supplier relation has anomalies again which are :

Drawback of 1 NF :
  1. Anomalies :
    • Deletion Anomaly – If we delete the tuple <S3,40,Rohtak,P1,245> , then we loose the information about S3 that S3 lives in Rohtak.
    • Insertion Anomaly – We cannot insert a Supplier S5 located in Karnal, until S5 supplies atleast one part.
    • Updation Anomaly – If Supplier S1 moves from Delhi to Kanpur, then it is difficult to update all the tuples containing (S1, Delhi) as SID and City respectively.
  2. Normal Forms are the methods of reducing redundancy. However, Sometimes 1 NF increases redundancy. It does not make any efforts in order to decrease redundancy.

Possibilities of Redundancy in 1 NF :

a) When LHS is not a Superkey :
Let X → Y is a non trivial FD over R with X is not a superkey of R, then redundancy exist between X and Y attribute set.
Hence in order to identify the redundancy, we need not to look at the actual data, it can be identified by given functional dependency.
Example : X →Y and X is not a Candidate Key
⇒ X can duplicate
⇒ corresponding Y value would duplicate also.
X Y
1 3
1 3
2 3
2 3
4 6
b) When LHS is a Superkey :
If X → Y is a non trivial FD over R with X is a superkey of R, then redundancy does not exist between X and Y attribute set.
Example : X →Y and X is a Candidate Key
⇒ X cannot duplicate
⇒ corresponding Y value may or may not duplicate.
X Y
1 4
2 6
3 4

Previous Home Next
Some Desirable Properties of Decomposition 2NF – Second Normal Form

     

Incoming search terms:

  • Normal forms example edu grabs

Leave a Reply