Database Normalization - Normal Forms

The normal forms (abbrev. NF) of relational database theory provide criteria for determining a table's degree of vulnerability to logical inconsistencies and anomalies. The higher the normal form applicable to a table, the less vulnerable it is. Each table has a "highest normal form" (HNF): by definition, a table always meets the requirements of its HNF and of all normal forms lower than its HNF; also by definition, a table fails to meet the requirements of any normal form higher than its HNF.

The normal forms are applicable to individual tables; to say that an entire database is in normal form n is to say that all of its tables are in normal form n.

Newcomers to database design sometimes suppose that normalization proceeds in an iterative fashion, i.e. a 1NF design is first normalized to 2NF, then to 3NF, and so on. This is not an accurate description of how normalization typically works. A sensibly designed table is likely to be in 3NF on the first attempt; furthermore, if it is 3NF, it is overwhelmingly likely to have an HNF of 5NF. Achieving the "higher" normal forms (above 3NF) does not usually require an extra expenditure of effort on the part of the designer, because 3NF tables usually need no modification to meet the requirements of these higher normal forms.

The main normal forms are summarized below.

Normal form Defined by In Brief definition
1NF First normal form Two versions: E.F. Codd (1970), C.J. Date (2003) 1970 and 2003 Table faithfully represents a relation and has no repeating groups
2NF Second normal form E.F. Codd 1971 No non-prime attribute in the table is functionally dependent on a proper subset of any candidate key
3NF Third normal form Two versions: E.F. Codd (1971), C. Zaniolo (1982) 1971 and 1982 Every non-prime attribute is non-transitively dependent on every candidate key in the table. The attributes that do not contribute to the description of the primary key are removed from the table. In other words, no transitive dependency is allowed.
EKNF Elementary Key Normal Form C. Zaniolo 1982 Every non-trivial functional dependency in the table is either the dependency of an elementary key attribute or a dependency on a superkey
BCNF Boyce–Codd normal form Raymond F. Boyce and E.F. Codd 1974 Every non-trivial functional dependency in the table is a dependency on a superkey
4NF Fourth normal form Ronald Fagin 1977 Every non-trivial multivalued dependency in the table is a dependency on a superkey
5NF Fifth normal form Ronald Fagin 1979 Every non-trivial join dependency in the table is implied by the superkeys of the table
DKNF Domain/key normal form Ronald Fagin 1981 Every constraint on the table is a logical consequence of the table's domain constraints and key constraints
6NF Sixth normal form C.J. Date, Hugh Darwen, and Nikos Lorentzos 2002 Table features no non-trivial join dependencies at all (with reference to generalized join operator)

Read more about this topic:  Database Normalization

Famous quotes containing the words normal and/or forms:

    The word career is a divisive word. It’s a word that divides the normal life from business or professional life.
    Grace Paley (b. 1922)

    Two forms move among the dead, high sleep
    Who by his highness quiets them, high peace
    Upon whose shoulders even the heavens rest,
    Two brothers. And a third form, she that says
    Good-by in the darkness, speaking quietly there,
    To those that cannot say good-by themselves.
    Wallace Stevens (1879–1955)