Normalization

Hello, and welcome to the blog. Today we are going to talk about "Normalization". In previous blog we understood "Anomalies in Database" now let's understand how to remove these anomalies and how you can make your database better.

Today we are going to talk about:

1. What is Normalization
2. Different Levels of Normalization
1. 1 NF
2. 2 NF

1) What is Normalization :

1. Normalization is the process of removing anomalies from our database.
2. What is anomaly that we have already discussed in previous part.
3. With normalization we can also reduce redundancy in our database.
4. Redundancy means having multiple copies of same data.
5. There are different levels of Normalization that we will discuss now

2) Different Levels of Normalization

1 NF (First Normal Form):

1. First Normalization Form states that "a relational table should not have any composite values or multi-valued attributes."
2. If a table has a multi-valued attribute then the database not in 1 NF.
3. Multi-valued attribute are the cells or fields of table which contains multiple values.
4. For example we have a table like this:

   Id	Employee Name	Phone Number	Designation
   1	John	9876543210,9876543201	Sales Manager
   2	Layla	9876543120	Sales Person
   3	Lecia	9876543213,9876543214	Analyst

5. Now in this table there are multiple values in a column called "Phone Number"
6. You might ask what's the problem in having multiple values
7. Now imagine if you want to update phone number where Id=1
8. There are 2 phone numbers for this record when we want to update a number we will unnecessarily have to retrieve both the numbers
9. You might say its okay we will retrieve the data and store it again
   Now let's think a step further , if you want "Phone Number" column to be unique and you are adding multiple values in that column then "Unique Constraint" will not work properly.
10. Now as we have understood the problem let's focus on solving it.
11. So to solve this issue what we can do is refactoring our tables i.e. creating a separate table for storing phone numbers

    Id	Employee Name	Designation
    1	John	Sales Manager
    2	Layla	Sales Person
    3	Lecia	Analyst

    Employee Id	Phone Number
    1	9876543210
    1	9876543201
    2	9876543120
    3	9876543213
    4	9876543214

12. Now we can say that our database tables are in 1 NF.

2 NF (Second Normal Form):

1. Second Normalization Form states that "database tables should be in 1 NF and all the attributes should only be dependent on Primary Key".
2. Let's see a example :

   Id	Employee Name	Department	Head Of Department
   1	John	Sales	Raye
   2	Layla	Marketing	Joleen
   3	Lecia	IT	Lan
   4	Daisy	Sales	Raye
   5	Sara	IT	Lan

3. Now in this "Employee" table our main aim is to store employee details and Id is our primary key, if you can observe column "Head Of Department" is dependent on column "Department" which is not a "Primary Key".
4. So now to solve this problem what we can do is: we can create 2 tables 1 will have Employees detail and other will have Department details.

   Id	Employee Name	Department
   1	John	Sales
   2	Layla	Marketing
   3	Lecia	IT
   4	Daisy	Sales
   5	Sara	IT

   Department Id	Department	Head Of Department
   1	Sales	Raye
   2	Marketing	Joleen
   3	IT	Lan

5. Now we satisfy 2nd Normal Form. Both Table are in 1st Normal Form and in Employee table non-key value attributes depend on "Id" attribute and in Department table non-key value attributes depend on "Department Id".

NOTE: For Now to keep it simple I am using Department Name in the Employee table example but in real life scenario you will have Department Id instead of  Department Name. We will see it our upcoming blogs.

There are more normalization forms we will see them later. Okay, that's it for now we will learn how to remove this anomalies. See you in some other blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"