What is normalization? Abhishek Srivasatava1091 27-Sep-2016databasedatabase sql server Updated on 19-Sep-2020
Abhishek Srivasatava
27-Sep-2016Normalization :
Normalization is a process of effectively organizing the column and table of a database to reduce data redundancy and improve data integrity.
Normal form of a table
Prsn_empl_id
Prsn_dpnd_id
Plan opted
669911
123456
Health , Dental
669901
987415
Vision
First Normal Form (1NF)
All attributes in a relation must have atomic domains.
In this scheme there should be no repeated element in single block.
For example:
Prsn_empl_id
Prsn_dpnd_id
Plan opted
669911
123456
Health
669911
123456
Dental
669901
987415
Vision
Second Normal Form (2NF)
for implementing this scheme it must satisfied 1st scheme.
Before moving further one should know about the prime attribute and non-prime attribute.
Prime attribute: the entire element which is a part of the prime key.
Non-prime attribute: the entire element which is not part of the prime key.
For this scheme, non prime attribute should be functionally dependent on prime key.
1NF table
Prsn_empl_id
Prsn_dpnd_id
Plan opted
669911
123456
Health
669911
123456
Dental
669901
987415
Vision
Modified to 2NF
Prsn_empl_id
Prsn_dpnd_id
669911
123456
669901
987415
Prsn_empl_id
Plan opted
669911
Health
669911
Dental
669901
Vision
3NF
It must satisfy 2NF criteria and there should no relationship between primary key and non primary element data.
For example we have table like this
Name
Addr_id
Address type
Address
A
10
Permanent
Abc
A
5
Temp
Pqr
B
10
Permanent
Xyz
B
5
temp
Efg
We can divide above table in two
Name
Addr_id
Address
A
10
Abc
A
5
Pqr
B
10
Xyz
B
5
Efg
Addr_id
Address type
5
Temp
10
Permanent
Boyce-Codd Normal Form
It is developed by the Raymond F Boyce.
X → Y is a trivial functional dependency (Y ⊆ X)
X is a super key for schema R
Normal table
Employee_id
Name
DOB
Car
Car_id
price
1001
A
12/01/1993
U
997
11M
1002
B
15/01/1993
X
997
12M
1003
C
17/01/1993
Y
997
13M
1004
D
20/01/1993
Z
997
14M
Converting to Boyce-Codd Normal Form
Employee_id
Name
DOB
1001
A
12/01/1993
1002
B
15/01/1993
1003
C
17/01/1993
1004
D
20/01/1993
Car
Car_id
price
U
997
11M
X
996
12M
Y
995
13M
Z
994
14M
Employee_id
Car_iD
1001
997
1002
996
1003
995
1004
994