Third normalization (3 NF) :
A database is in third normal form if it satisfies the following conditions:
1) Itis in 2NF and
2) There is no transitive(সকর্মক) functional dependency.
Let’s consider :
X => Y => Z
Concept 01 :
X is functionally dependent on Y,
Y is functionally dependent on Z so
Z is transitively dependent on X via Y.
Concept 02 :
Z is transitively dependency if,
a) Y is not a super key or primary key and
b) Z is not part of primary key.
In this table Student_id is Primary key.
But street, city and state depends upon Zip.
The dependency between zip and other fields is called transitive dependency. Because street, city and state is transitively dependent on Student_id via Zip.
To apply 3NF, we need to move the street, city and state to new table, with Zip. In new table Zip will be a primary key.
1) Amount of data duplication is reduced.
2) Data integrity achieved.