The preceding lessons discussed the limitations of 2NF with regard to transitive dependencies and outlined the process of removing transitive dependencies to put the relation into 3NF. To summarize, follow these steps to translate relations into 3NF:
- Identify repeating groups.
- If there are repeating groups, separate the groups into relations of their own. The relations will then be in 1NF.
- Identify the functional dependencies in the relations.
- If one or more fields in a relation are not determined by the primary-key fields, move the undetermined fields to new relations. The relations will then be in 2NF.
- Identify any transitive dependencies in the relations.
- If there are transitive dependencies (if more than one entity is represented in a relation), break the relation into smaller relations that represent each entity in the larger relation. The relations will then be in 3NF.
The next lesson describes Codd's 12 criteria for a fully relational database.
To understand
third normal form, you must first understand
transitive dependency.
An attribute that depends on another attribute that is not the primary key of the relation is said to be
transitively dependent.
A relation is said to be in third normal form if it meets both the following criteria:
- The relation is in second normal form.
- There is no transitive dependence (that is, all the non-key attributes depend only on the primary key).
To transform a second normal form relation into
third normal form, simply move any transitively dependent attributes to relations where they depend only on the primary key. Be careful to leave the attribute on which they depend in the original relation as a
foreign key. You will need it to reconstruct the original user view via a join. When it is in third normal form, that tells you to remove easily calculated attributes.
