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 |
|
 |
| 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 :
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)
]]>