Database Normal Forms

Normalization rules are divided into the following normal forms:

  1. First Normal Form
  2. Second Normal Form
  3. Third Normal Form
  4. BCNF
  5. Fourth Normal Form

1st Normal Form

(From No Normalization)

For a table to be in the First Normal Form, it should follow the following 4 rules:

  1. It should only have single(atomic) valued attributes/columns.
  2. Values stored in a column should be of the same domain
  3. All the columns in a table should have unique names.
  4. And the order in which data is stored, does not matter.

2nd Normal Form

(From 1st)

For a table to be in the Second Normal Form:

  1. It should be in the First Normal form.
  2. And, it should not have Partial Dependency.

3rd Normal Form

(From 2nd)

A table is said to be in the Third Normal Form when:

  1. It is in the Second Normal form.
  2. And, it doesn’t have Transitive Dependency.

Boyce-Codd Normal Form (BCNF)

Boyce and Codd Normal Form is a higher version of the Third Normal form. This form deals with certain type of anomaly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF. For a table to be in BCNF, following conditions must be satisfied:

  • R must be in 3rd Normal Form
  • and, for each functional dependency ( X → Y ), X should be a super Key.

4th Normal Form

A table is said to be in the Fourth Normal Form when,

  1. It is in the Boyce-Codd Normal Form.
  2. And, it doesn’t have Multi-Valued Dependency.

5th Normal Form

Only in rare situations does a 4NF table not conform to 5NF. These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table. If such a table is not normalized to 5NF, the burden of maintaining the logical consistency of the data within the table must be carried partly by the application responsible for insertions, deletions, and updates to it; and there is a heightened risk that the data within the table will become inconsistent. In contrast, the 5NF design excludes the possibility of such inconsistencies.

A table T is in fifth normal form (5NF) or Project-Join Normal Form (PJNF) if it cannot have a lossless decomposition into any number of smaller tables. The case where all the smaller tables after the decomposition have the same candidate key as the table T is excluded.