Normalization in SQL

Normalization is the processes of reducing the redundancy of data in the table and also improving the data integrity.

Normalization emphasizes how to organize a database’s columns and tables to ensure that their dependencies are properly enforced by database integrity constraints.

Normalization Types

First Normal Form (1NF) 

  • It simplifies each attribute in a relation.
  • In 1NF, there should not be any repeating group of data.
  • Each set of column must have a unique value.
  • It contains atomic values because the table cannot hold multiple values.

Second Normal Form (2NF) 

  • In 2NF, the table is required in 1NF.
  • The main rule of 2NF is, ‘No non-prime attribute is dependent on the proper subset of any candidate key of the table.’
  • An attribute which is not part of candidate key is known as non-prime attribute.

Function in SQL

Third Normal Form (3NF)

  • Third Normal Form (3NF) is used to minimize the transitive redundancy.
  • In 3NF, the table is required in 2NF.
  • While using the 2NF table, there should not be any transitive partial dependency.
  • 3NF reduces the duplication of data and also achieves the data integrity.

BCNF (Boyce – Code Normal Form)

  • BCNF which stands for Boyce – Code Normal From is developed by Raymond F. Boyce and E. F. Codd in 1974.
  • BCNF is a higher version of 3NF.
  • It deals with the certain type of anomaly which is not handled by 3NF.
  • If every determinant is a candidate key, then it is said to be BCNF.
  • Candidate key has the ability to become a primary key. It is a column in a table.

Data Definition Language (DDL)

Fourth Normal Form (4NF)

  • Fourth Normal Form (4NF) does not have non-trivial multivalued dependencies other than a candidate key.
  • 4NF builds on the first three normal forms (1NF, 2NF and 3NF) and the BCNF.
  • It does not contain more than one multivalued dependency.
  • This normal form is rarely used outside of academic circles.

Fifth Normal Form (5NF)

  • 5NF is also knows as Project-Join Normal Form (PJ/NF).
  • It is designed for reducing the redundancy in relational databases.
  • 5NF requires semantically related multiple relationships, which are rare.
  • In 5NF, if an attribute is multivalued attribute, then it must be taken out as a separate entity.
  • While performing 5NF, the table must be in 4NF.

Normalization in SQL

Leave a Comment

Your email address will not be published. Required fields are marked *