What is Database Normalization?

Share this Story:

Database Normalization is a technique of organizing the data in the database. Database Normalization is a systematic approach of decomposing tables to eliminate data redundancy(repetition) and undesirable characteristics. For example, Insertion, Update and Deletion anomalies. It is a multi-step process that puts data into tabular form, removing duplicated data from the relation tables.

Normalization is used for mainly two purposes.

  • Eliminating redundant(useless) data.
  • Ensuring data dependencies make sense i.e. data is logically stored.
First Normal Form (1NF)

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.
Second Normal Form (2NF)

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.
Third Normal Form (3NF)

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.

                                        Here is an example of a denormalized table

Film Agents Info

AgentId Agent
Name
Agency
Name
Office
Location
Office
Contact
Customer1 Custo2
A1 A&I Gold New Cine Los Angels 111222333 BreadPtch Brian
             
             
             

Let’s Take This table to 1NF (First Normal Form).

We shall Split the above table into two tables.

Agent Info

AgentId Agent
Name
Agency
Name
Office Location Office Contact
         

Customer Info

CId AgentId Customer
Name
Customer
City
CustomerPincode
         

Let’s Take the above tables to 2NF (Second Normal Form) We Shall split the above two tables into four tables.

Agent info

AgentId AgentName AgencyId
     

Agency Info

AgencyId AgencyName OfficeLocation Office Location
       

Customer Info

CId CustomerName CustomerCity CustomerPincode
       

CustomerAgents

AgentId CustomerId

Let’s Take the above tables to 3NF (Third Normal Form).

HOW TO INSTALL WORDPRESS ON YOUR PC 2019

We Shall split the above two tables into five tables.

Agent info

AgentId AgentName AgencyId
     

Agency Info

AgencyId AgencyName OfficeLocation Office Location
       

Customer Info

CId CustomerName CustomerPincode-FK
     

CustomerPinCodes

CustomerPinCodes CustomerCity
   

CustomerAgents

AgentId CustomerId

1,410 total views, 2 views today

Leave a Reply

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