Normalization Form

October 18, 2023

2023   ·   normalization-form   DB     ·   database  

What is Normalization Form?

Normalization form is a way of measuring the level of normalization of a relation in a database. There are five normal forms, each with their own set of rules. The normal forms are as follows:

  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce-Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  • Fifth Normal Form (5NF)
  • Domain-Key Normal Form (DKNF)

First Normal Form (1NF)

A relation is in first normal form if it satisfies the following conditions:

  • The relation has no repeating groups.
  • The relation has a primary key.
  • The relation has no composite/multi-valued attributes.

Second Normal Form (2NF)

A relation is in second normal form if it satisfies the following conditions:

  • The relation is in first normal form.
  • There are no partial dependencies between non-key attributes and the primary key.

Third Normal Form (3NF)

A relation is in third normal form if it satisfies the following conditions:

  • The relation is in first normal form.
  • There are no transitive dependencies between non-key attributes and the primary key.

3NF is a stronger form of 2NF. If a relation is in 3NF, it is also in 2NF.

Boyce-Codd Normal Form (BCNF)

A relation is in Boyce-Codd normal form if it satisfies the following conditions:

Generating 3NF Relations

  1. If \(R\in\) 3NF, then terminate.
  2. If \(R_i\subseteq R\) \(\notin\) 3NF, which means \(Y\subseteq R_i\) is a transitive dependency, \(Z\notin XY\), and \(X\rightarrow Y\), \(Y\rightarrow Z\), \(Y\nrightarrow X\), then \(R_{i,1} = YZ\), \(R_{i,2} = R_i - Z\), replace \(R_i\) with \(R_{i,1}\) and \(R_{i,2}\).
  3. repeat step 2 until all relations are in 3NF.

Generating BCNF Relations

  1. If \(R\in\) BCNF, then terminate.
  2. If \(R_i\subseteq R\) \(\notin\) BCNF, which means \(X\rightarrow Y\) and \(X\) is not a key, then \(R_{i,1} = R_i - Y\), \(R_{i,2} = XY\), replace \(R_i\) with \(R_{i,1}\) and \(R_{i,2}\).
  3. repeat step 2 until all relations are in BCNF.