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:
“Insecurity, commonly regarded as a weakness in normal people, is the basic tool of the actor’s trade.”
—Miranda Richardson (b. 1958)
“Ye know why the forms are fair, ye hear how the tale is told:
It is all triumphant art, but art in obedience to laws,”
—Robert Browning (1812–1889)