Name |
Description
|
First Normal Form |
An entity is in First Normal Form (1NF) when all tables are two-dimensional with no repeating groups.
A row is in first normal form (1NF) if all underlying domains contain atomic values only. 1NF eliminates repeating groups by putting each into a separate table and connecting them with a one-to-many relationship.
Make a separate table for each set of related attributes and uniquely identify
each record with a primary key.
- Eliminate duplicative columns from the same table.
- Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
|
Second Normal Form |
An entity is in Second Normal Form
(2NF) when it meets the requirement of being in First Normal Form
(1NF) and additionally:
- Does not have a composite primary
key. Meaning that the primary key can not be subdivided into
separate logical entities.
- All the non-key columns are functionally dependent on
the entire primary key.
- A row is in second normal form if, and only if, it is in first normal form and every non-key attribute is fully dependent on the key.
-
2NF eliminates functional dependencies on a partial key by putting the fields in a separate table from those that are dependent on the whole key.
An example is resolving many:many relationships using an intersecting entity.
|
Third Normal Form |
An entity is in Third Normal Form
(3NF) when it meets the requirement of being in Second Normal Form
(2NF) and additionally:
- Functional dependencies on non-key fields are eliminated by putting them in a separate table. At this
level, all non-key fields are dependent on the primary key.
-
A row is in third normal form if and only if it is in second normal form and if attributes
that do not contribute to a description of the primary key are move into a separate table.
An example is creating look-up tables.
|
Boyce-Codd Normal Form |
Boyce Codd Normal Form (BCNF) is a further refinement of 3NF.
In his later writings Codd refers to BCNF as 3NF. A row is in Boyce Codd normal form if, and only if, every determinant is a candidate key. Most entities in 3NF are already in BCNF.
BCNF covers very specific situations where 3NF misses inter-dependencies between non-key (but candidate key) attributes. Typically, any relation that is in 3NF is also in BCNF. However, a 3NF relation won't be in BCNF if (a) there are multiple candidate keys, (b) the keys are composed of multiple attributes, and (c) there are common attributes between the keys.
|
Fourth Normal Form |
An entity is in Fourth Normal Form (4NF)
when it meets the requirement of being in Third Normal Form (3NF) and
additionally:
- Has no multiple sets of multi-valued dependencies. In other words, 4NF states that no entity can have more than a single one-to-many relationship within an entity if the one-to-many attributes are independent of each other.
- Many:many relationships are resolved
independently.
|
Fifth Normal Form |
An entity is in Fifth Normal Form (5NF)
if, and only if, it is in 4NF and every join dependency for the entity is a consequence of its candidate keys.
|