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:
“Cant is always rather nauseating; but before we condemn political hypocrisy, let us remember that it is the tribute paid by men of leather to men of God, and that the acting of the part of someone better than oneself may actually commit one to a course of behaviour perceptibly less evil than what would be normal and natural in an avowed cynic.”
—Aldous Huxley (1894–1963)
“You may melt your metals and cast them into the most beautiful moulds you can; they will never excite me like the forms which this molten earth flows out into. And not only it, but the institutions upon it are plastic like clay in the hands of the potter.”
—Henry David Thoreau (1817–1862)