What is Normalization ?
Normalization is a process of eliminating Redundant data and storing the related information in a table.
1. Eliminating Redundant data.
2. Faster update
3. Improve performance
4. Performance in indexes
Let we see different Normalization forms
1. First Normal Form (1NF)
If a Table is said to be 1NF then it should satisfy following rules.
- Each cell must have one value
- Eliminating Duplicate Columns
- Create a separate table for group of related data and each row must be identify by primary key.
That means each cell must have single value and each row should be uniquely identified by Primary key
For Example :
Name
|
Department
|
Phone Number
|
Rajesh
|
Computer
|
3452342,1234563,2345612
|
Suresh
|
Electronics
|
2398521,2323177,5302994
|
Praba
|
Civil
|
3958218
|
In the above we can see the duplicate columns phone numbers have more than one value , we have to eliminate that and create a group of related data with Unique row identification by specifying a primary key for the table
Rule 1. By applying above rule each cell must have one value above table changes like below
Name
|
Department
|
Phone Number
|
Phone Number
|
Phone Number
|
Rajesh
|
Computer
|
3452342
|
1234563
|
2345612
|
Suresh
|
Electronics
|
2398521
|
2323177
|
5302994
|
Praba
|
Civil
|
3958218
|
Rule 2 & 3 . By applying second rule and third rule no more duplicate columns and each row must be unique is applied to above table.
Id
|
Name
|
Department
|
Phone Number
|
1
|
Rajesh
|
Computer
|
3452342
|
2
|
Rajesh
|
Computer
|
1234563
|
3
|
Rajesh
|
Computer
|
2345612
|
4
|
Suresh
|
Electronics
|
2398521
|
5
|
Suresh
|
Electronics
|
2323177
|
6
|
Suresh
|
Electronics
|
5302994
|
7
|
Praba
|
Civil
|
3958218
|
2. Second Normal Form (2NF)
The Table must be in second normal form , Then it should satisfy the following rules.
- It should satisfy first normal form
- Separate the particular columns ,values are duplicated in each row should be place in separate table
- Create the relationship between the tables
From the above table we can see the column name and department are repeated in each row ,This two columns can be maintained in another table and make a relationship between these two tables
EmpId
|
Name
|
Department
|
1
|
Rajesh
|
Computer
|
2
|
Suresh
|
Electronics
|
3
|
Praba
|
Civil
|
Id
|
EmpId
|
PhoneNumber
|
1
|
1
|
3452342
|
2
|
1
|
1234563
|
3
|
1
|
2345612
|
4
|
2
|
2398521
|
5
|
2
|
2323177
|
6
|
2
|
5302994
|
7
|
3
|
3958218
|
In the above table Empid is played as Primary key for the first table and foreign key for the second table.
3. Third Normal Form (3NF)
The table must be in 3NF,if it is satisfying the following rules
- Must be in 2NF
- Separate the columns that are not dependent upon the primary key of the table.
Product
|
Price
|
Tax
|
LED
|
23000
|
20%
|
AC
|
15000
|
10%
|
Fridge
|
12000
|
15%
|
From the above table you can see that Tax Column is not dependent on Product Primary key column, It is dependent on Price so we separate that in to two different table.
Product
|
Price
|
LED
|
23000
|
AC
|
15000
|
Fridge
|
12000
|
Price
|
Tax
|
23000
|
20%
|
15000
|
10%
|
12000
|
15%
|
4. Fourth Normal Form (4NF)
- It should be in 3NF
- The non key columns should be dependent on full primary key instead of partial key , If then separate it.
From the following table "EmployeeName" Non-Key column not dependent on full primary key "ManagerId,EmployeeId,TaskID" it depends upon the EmployeeId Partial Key so it can be separated.
ManagerId
|
EmployeeId
|
TaskID
|
EmployeeName
|
M1
|
E1
|
T1
|
Rajesh
|
M2
|
E1
|
T1
|
Rajesh
|
ManagerId
|
EmployeeId
|
TaskID
|
M1
|
E1
|
T1
|
M2
|
E1
|
T1
|
EmployeeId
|
EmployeeName
|
E1
|
Rajesh
|
That's it from this article we can see the normalization and there concepts Fully.
No comments:
Post a Comment