15.00 - Definition of Third Normal Form - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Definition of Third Normal Form

A relation variable R is said to be in Third Normal Form when it is in 2NF and one of the following statements is also true for every nontrivial functional dependency (assume the functional dependency X   A):

  • The attribute set x is a superkey.
  • Attribute A is part of some candidate key.
  • Another way of stating the rule is this: The relationship between any two nonkey attributes or attribute sets (excluding attributes having no duplicates allowed constraints) must not be one-to-one in either direction.

    Using the phrase coined by Kent (1983), the relation is in 3NF when all its attributes depend on the key, the whole key, and nothing but the key. This condition is met when both of the following criteria have been met:

  • Every nonkey attribute depends on all attributes of the primary key: the entire primary key.
  • Again, this is more accurately stated as the entire candidate key.

    Note: You cannot use XML, BLOB, or CLOB columns to define a key or other database constraint (see Chapter 12: “Designing for Database Integrity”).

  • No non‑key attribute is functionally dependent on another non‑key attribute of the relation.
  • The formal definition for Third Normal Form is as follows: For a relation to be in 3NF, the relationship between any two nonprimary key attributes (more accurately, between any two non‑candidate key attributes) or groups of attributes in a relation must not be one-to-one in either direction. In other words, the nonkey attributes are nontransitively dependent upon each other and the key. Having no transitive dependencies in a relation implies no mutual dependencies.

    Attributes are said to be mutually independent if none of them is functionally dependent on any combination of the others. This mutual independence ensures that individual attributes can be updated without any danger of affecting any other attribute in a tuple.

    This is an incomplete definition for 3NF that fails to account for the case where functional or transitive dependencies occur. To account for this special case, Codd and Raymond Boyce completed 3NF with the definition described in “Definition of Boyce‑Codd Normal Form” on page 87. BCNF was first worked out and reported by Ian Heath, though he is rarely given credit for the discovery.