NORMALIZATION
Normalization is process of
splitting the base table into multiple tables based on the theory of Functional
Dependency.
OR
Normalization is repetitive
process in order to identify the functional dependencies among the columns and
to remove them. If any functional dependency is occurred after the
normalization process again we have to start the same process until all
functional dependencies have been removed.
To do this Normalization we
have to follow rules or conditions called Normal Forms.
Un-Normalized Table
EMPNO PROJNO ENAME
PNAME SAL BUD
DEPTNO DNAME LOC
11 (P1, P2) ----
(Pn1, Pn2) ----- -----
10 -------- -----
22 (P2, P3) ----
(Pn2, Pn3) ----- -----
10 -------- -----
33 (P1, P3) ----
(Pn1, Pn3) ----- -----
20 -------- -----
EMPNO and PROJNO are Primary
Keys called ‘COMPOSITE PRIMARY KEY’
FIRST NORMAL
FORM (1NF): According to first normal form table should contain only single
values columns. But in the above un-normalized table the columns PROJNO and
PNAME contains multiple values.
To make the table into first
normal form we should have to split the multiple values into single values.
EMPNO PROJNO ENAME
PNAME SAL BUD
DEPTNO DNAME LOC
11 P1 ---- Pn1 -----
----- 10 -------- -----
11 P2 ---- Pn2 -----
----- 10 -------- -----
22 P2 ---- Pn2 -----
----- 10 -------- -----
22 P3 ---- Pn3 -----
----- 10 -------- -----
33 P1 ---- Pn1 -----
----- 20 -------- -----
33 P3 ---- Pn3 -----
----- 20 -------- -----
SECOND NORMAL
FORM (2NF):
According to second normal
form table should be in 1NF and we should have to remove Partial Functional
Dependency.
In the above table DEPTNO
non-key column dependent part of the Primary key column i.e.EMPNO. It means
there existed Partial functional dependency.
To make the table into
second normal form we have to divide the table into multiple tables.
PROJ-INFO
PROJNO PNAME BUD
P1 Pn1 ------
P2 Pn2 ------
P3 Pn3 ------
EMP-INFO
EMPNO ENAME
SAL DEPTNO DNAME
LOC
11 ---- ---- 10 ------ -----
22 ---- ---- 10 ------ -----
33 ---- ---- 20 ------ -----
THIRD NORMAL
FORM (3NF):
According to second normal
form table should be in 2NF and we should have to remove Transitive Functional
Dependency.
In the above EMP-INFO table
non-key column DNAME dependent part on the other non- key column i.e.DEPTNO. It
means there existed Transitive functional dependency.
To make the table into third
normal form we have to divide the table into multiple tables.
PROJ-INFO
PROJNO PNAME BUD
P1 Pn1 ------
P2 Pn2 ------
P3 Pn3 ------
EMP-INFO DEPT-INFO
EMPNO ENAME
SAL DEPTNO DNAME
LOC
11 ---- ---- 10 ------ -----
22 ---- ---- 20 ------ -----
33 ---- ----
No comments:
Post a Comment