Normalization in SQL | Types of normalization

0
93
keys dbms

Normalization :

Normalization can be defined as the process of eliminating the redundancy present in the data from relational tables by decomposing or splitting a relational table into small size tables with the help of projection. The ultimate aim to do this is to have only primary keys on the left-hand side of a functional dependency.

Benefits Of Using Normalization :

  • Normalization can avoid repetitive entries.
  • It helps to reduce required storage space.
  • Normalization can prevent the need to restructure existing tables to accommodate new data.
  • It increased speed and flexibility of queries, sorts and summaries.
normal forms in dbms
Fig : Types of Normalization

Types Of Normalization :

  1. First Normal Form (1NF)
  2. Second Normal Form (2NF)
  3. Third Normal Form (3NF)
  4. Boyce-Codd Normal Form (BCNF)

First Normal Form (1NF):

By definition every relational table is in first normal form. All values present in the columns are atomic.

That mean’s they contain no repeating values. First normal form is minimal requirement.

  • 1NF eliminating repeating groups in each table.
  • It creates a separate table for each set of related data in a table.
  • It Identifies each set of related data with a primary key.

Second Normal Form (2NF):

According to the definition of the Second Normal Form, tables with composite primary keys can be present in 1NF but not in 2NF. A table to be in 2NF it should be present in 1NF.

  • In 2NF there should not have any partial dependency.
  • Every non-key column must be dependent upon the entire primary key.

Third Normal Form (3NF) :

The third normal form can be described as “Each an every column in a relational table is dependent only upon the primary key.

” A relational table is a present third normal form if it’s in 2NF and also every Non-key column is non-transitively dependent upon its the primary key.

  • All non-key attributes should be functionally dependent only upon the primary key.

Boyce-Codd Normal Form (BCNF):

Boyce-Codd normal form is a more precise or specific version of the third normal form. BCNF deals with relational tables that are having many candidate key, overlapping candidate keys and also composite candidate keys.

  • BCNF is mainly based on the concept of Determinants.
  • A relational table is said to be BCNF if every determinant is candidate key.

Anomalies :

  • Redundancy causes are called update anomalies.
  • Update anomalies are arise whenever information inserted, deleted and updated.