Database Normalization: Third Normal Form

A record is in third normal form if:

·        it is in second normal form and,

·        every non-key attribute of the record is non-transitively dependent on the primary key of the record.

Transitive Dependency

A record in second normal form can still have an update anomaly.  It may contain an attribute that is not a key but does identify other attributes.

An attribute X within a relation may be dependent on a non-key attribute Y which in turn is dependent on the key.  Unless the reverse is true, then the attribute X is dependent on the key only through attribute Y.  Without Y, the dependence is lost.  This state is referred to as transitive dependency.
 
Removing transitive dependencies puts data in third normal form.

Fourth Rule of Normalization

Non-key attributes of a relation must be fully dependent on the key and must be independent of any other non-key attribute.

Applying the Rules to Achieve Third Normal Form

To normalize data to achieve third normal form, apply the fourth rule of normalization:

·        Identify and remove any attribute which is actually dependent on a non-key attribute in the same relation (i.e., dependent upon the key through another attribute).

·        Create a new relation for this attribute identified by the relevant key.

Attributes are now non-transitively dependent upon the key.

Third normal form is violated when a non-key field is fully functionally dependent on another non-key field.

To satisfy third normal form, the example Employee Record should be decomposed.
 
The problems with a record not in third normal form are the same as those caused by violations of second normal form:

·        The location of the department is repeated in the record of each employee assigned to that department.

·        If the location of the department changes, every such record must be updated.

·        Because of the redundancy, the data may become inconsistent, with different records showing different locations for the same department.

·        If a department has no employees, there may be no record in which to keep the department's location.

In general, a record is in third normal form if it is in second normal form and if every field is either part of the key or provides a (single-valued) fact about the whole key and nothing else.  If an entity is in third normal form, the non-key fields are said to be non-transitively dependent upon the key.