third normal form
according to Codd’s original definition, a relation schema
is in 3NF if it satisfies 2NF and no nonprime attribute of
is transitively dependent on the primary key.
[cite:;from @elmasri_db_2015 chapter 14.4.2 general definition of third normal form]
[cite:;from @elmasri_db_2015 chapter 14.4.2 general definition of third normal form]
a relation satisfies the 3nf normalization degree if:
the process of 3nf normalization is as follows. incase there is a transitive dependency, we denote it by
, where
is the key:
- it satisfies the 2nf normalization degree;
- every field that isnt part of a key isnt functionally dependent on any field that isnt part of the key, i.e. every field that isnt in a key is directly dependent on the key (not transitively).
the process of 3nf normalization is as follows. incase there is a transitive dependency, we denote it by
- we copy the dependency
to a separate relation, in which
is the key;
- we delete
from the original relation.
consider the following relation:
(student id, student name, department code, department budget)
in this relation, each of the fields that arent part of the key are functionally dependent on it, but the department budget isnt dependent on the key in a transitive manner. in this case we have a redundant relation: when we know the id of a student, we also know their department's budget. to fix this we copy the department code and budget fields into a separate relation, and delete the department budget field from the original relation. the resulting schema is:
(student id, student name, department code, department budget)
in this relation, each of the fields that arent part of the key are functionally dependent on it, but the department budget isnt dependent on the key in a transitive manner. in this case we have a redundant relation: when we know the id of a student, we also know their department's budget. to fix this we copy the department code and budget fields into a separate relation, and delete the department budget field from the original relation. the resulting schema is:
- students (_student id_, student name, department code)
- departments (_department code_, department budget)