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) :
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 :
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)
|2NF – Second Normal Form :||BCNF – Boyce-Codd Normal Form|