3NF – Third Normal Form

Let R be the relational schema, R is in 3NF only if :

  • R should be in 2NF.
  • R should not contain transitive dependencies.
What is a Transitive Dependency ?
Let R be a relational Schema and X,Y,Z be the attribute sets over R.
If X is functionally dependent on Y (X → Y)
and Y is functionally dependent on Z (Y → Z)
then X is transitive dependent on Z (X → Z)

Removal of Transitive Dependency 

If there is any transitive dependency in the relation, then

  • Create a separate relation and copy the dependent attribute along with a copy of its determinant. and remove these determinants from the original table.
  • Mark dependent attribute as a foreign key in the original relation  and Mark dependent attribute as a Primary key in the separate relation
Example of 3NF :

Consider the relation Sup_City(SID, Status, City) :

Sup_City :
SID Status City
S1 30 Delhi
S2 10 Karnal
S3 40 Rohtak
S4 30 Delhi
SID : Primary Key
 Supplier_City 3NF
Transitive Dependency :
SID → City  {As SID → City and City → Status}
Solution :
Removal of Transitive Dependency by creating separate table
SC :
SID City
S1 Delhi
S2 Karnal
S3 Rohtak
S4 Delhi
SID : Primary Key
CS :
City Status
Delhi 30
Karnal 10
Rohtak 40
City : Primary Key
The relations SC and CS are in 3NF as they doesn't contain any transitive dependencies.

Possibilities of Redundancy in 3NF 

However, there is less redundancy in 3NF than in 2 NF, but again 3NF is not free from redundancy.
The possibilities of redundancy in 3NF are :

Rdundancy in 3NF

Some Points regarding 3NF :

1. A table is automatically in 3NF if one of the following hold :
    (i) If relation consists of two attributes.
    (ii) If 2NF table consists of only one non key attributes.

2. If X → A is a dependency, then the table is in the 3NF, if one of the 
   following conditions exists :
    (i) If X is a superkey
    (ii) If X is a part of superkey

3. If X → A is a dependency, then the table is said to be NOT in 3NF if 
   the following :
    (i) If X is a proper subset of some key (partial dependency)
    (ii) If X is not a proper subset of key (non key)

Previous Home Next
2NF – Second Normal Form : BCNF – Boyce-Codd Normal Form

     

Incoming search terms:

  • 3nf edugrabs

Leave a Reply