The cornerstone of the Inmon paradigm is Boyce-Codd Normal Form; which is effectively 3rd normal form with an extra caveat. The process of normalising data to the varying states of normal form isn’t difficult, all that’s required is an understanding of the rules and following the steps below to the desired outcome…
The Six Normal Forms:
These formal definitions are taken from the book by Chris J. Date: An Introduction to Database Systems Volume 14th edition, © 1996
First Normal Form:
‘A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only.’
The general rules are:
- Identify repeating attributes.
- Remove these repeating attributes to a new table together with a copy of the key from the UNF table.
- Assign a key to the new table (and underline it). The key from the original unnormalised table always becomes part of the key of the new table. A composite key is created. The key must be unique within the table for each entity.
Second Normal Form:
‘A relation R is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key.’
To make your table(s) into 2NF, use the following rule: Take each non-key attribute and ask the question “is this attribute dependent on one part of the key?”
- If the answer is yes, move the attribute to new table with a copy of the part of the key it is dependent upon. The part of the key it is dependent upon becomes the key for the new table.
- If the answer is no, the attribute stays in the current table.
Third Normal Form:
‘A relation R is in third normal form (3NF) if and only if it is in 2NF and every non-key attribute is not transitively dependent on the primary key.’
Remember to ignore tables with zero or only one non-key attribute (these are already in 3NF). For the rest of the tables, follow this rule: If a non-key attribute is dependent on another non-key attribute than the table key:
- Move the dependent attribute, together with a copy of the non-key attribute upon which it is dependent, to a new table.
- Make the non-key attribute, upon which it is dependent, the key in the new table. Underline the key in this new table.
- Leave the non-key attribute, upon which it is dependent, in the original table and mark it as a foreign key.
Boyce/Codd Normal Form (also known as 3.5NF):
‘A relation R is in Boyce/Codd normal form (BCNF) if and only if every determinant is a candidate key.’
Fourth Normal Form:
‘A relation R is in fourth normal form (4NF) if and only if, wherever there exists an MVD in R, say A -> -> B, then all attributes of R are also functionally dependent on A. In other words, the only dependencies (FDs or MVDs) in R are of the form K -> X (i.e. a functional dependency from a candidate key K to some other attribute X). Equivalently: R is in 4NF if it is in BCNF and all MVD’s in R are in fact FDs.’
Fifth Normal Form:
‘A relation R is in fifth normal form (5NF) – also called projection-join normal form (PJ/NF) if and only if every join dependency in R is a consequence of the candidate keys of R.’
For every normal form it is assumed that every occurrence of R can be uniquely identified by a primary key using one or more attributes in R.
FD = Functional Dependency
MVD = Multi-Valued Dependency