NORMALIZATION, Need for normalization-34slides.PPT

Normalization

1. NORMALIZATION

2. NORMALIZATION
Normalization is a method of breaking
down complex table structures into
simple table structures by using
certain rules.
It is a process of refining data model
built by E-R diagram.
We can reduce redundancy in a table.
Eliminate problems of inconsistency.

3. NEED FOR NORMALIZATION
Improves database design.
Ensure minimal redundancy.
To make relation simple and easier.
To increase performance of a system.

4. TYPES OF NORMALIZATION
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce – codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
First , Second and Third Normal Forms were
defined by Dr. E.F.Codd.

5. FIRST NORMAL FORM
It contain no repeating groups.
 A table is said to be in 1NF when each cell
of table contain only one value.
The Definition of 1NF has been expressed in
following two terms:
In every tuple of relation R, no attribute
should have repeating groups.
In every tuple of relation R , each attribute
must have a value and that too atomic value.

6. TABLE PROJECT
ECODEDEPTPROJ
CODE
HOUR
S
E 101 SystemP27
P51
P20
90
101
60
E 305 SalesP27 109
E 508 AdminP51
P27
NULL
72

7. TO BRING AN UNNORMALIZED
RELATION INTO 1NF,PERFORM
FOLLOWING STEPS:
Remove all the repeating groups from
the relation.
Decompose non-atomic attributes to
atomic attributes.

8. By applying 1NF, we arrive the
following table.
ECODE DEPT PROJCODE HOU
RS
E 101 SystemsP 27 90
E 101 SystemsP 51 101
E 101 SystemsP 20 60
E 305 Sales P 27 10
E 508 Admin P 51 Null
E 508 Admin P 27 72

9. ANOMALIES IN 1NF
INSERT: we can’t insert the information about
any projcode until the person join any dept.
UPDATE : If the ECODE is to be modified,it
has to be changed throughout the
table.Missing out even single correction
would result inconsistency.
DELETE: If information related to a specific
column is to be deleted, the entire row has to
be deleted which results in loss of required
information.

10. SECOND NORMAL FORM
Second normal form is based in
concept of fully functional dependency.
A relation that is in first normal form and
all it’s non key fields are fully dependent
upon whole key.

11. Consider a table :
ECodeProjcodedept hours
E 101P27 System90
E 305P27 Finance10
E 508P51 AdminNull
E 101P51 System101
E 101P20 System60
E 508P27 admin72

12. This could lead to following
problems:
INSERT : The dept. of a particular employee
can’t be recorded until the employee is assigned
a project.
UPDATE : The employee code and dept. are
repeated many times.hence if employee is
transferred to another dept. this change will have
to recorded in every row of table.
DELETE : If employee complete work on a
project ,the employee’s record will be
deleted.The information related to the dept. to
which the employee belongs will also be lost.

13. Guidelines for converting a table
into 2NF
Find and remove attributes that are
functionally dependent on only a part of
a key not on whole key.place them in a
different table.
Group the remaining attributes.

14. Employee Dept
ECODE DEPT
E101 Systems
E305 Sales
E508 Admin

15. Project
E code Proj codehours
E101 P27 90
E101 P51 101
E101 P20 60
E305 P27 10
E308 P51 NULL
E308 P27 72

16. THIRD NORMAL FORM
A relation that is in 1
st
and 2
nd
normal
form and every non key attributes are
functionally dependent only on primary
key.
Ecod
e
Dept DeptHead
E101 Systems E901
E305 Finance E905
E402 Sales E906
E508 Admin E908
E807 Finance E909
E608 Finance E909

17. The following problem arises:
INSERT : The dept head of a new dept that does
not have any employee at present cannot be
entered in dept head column because primary key
is unknown.
UPDATE : The code for a particular dept head is
repeated several times.if dept head is moved to
another dept ,the change will have to made
consistently across the table.
DELETE : If record of employee is deleted ,the
information regarding head will also be
deleted.There will be loss of information.

18. Guidelines for converting table
into 3NF:
Find and remove non-key attribute that
are functionally dependent on attributes
that are not the primary key.place them
in a different table.
Group the remaining attributes.

19. Employee
Ecode Dept
E101 System
E305 Finance
E402 Sales
E508 Admin
E607 Finance
E608 finance

20. Department
Dept Dept Head
Systems E901
Sales E906
Admin E908
finance E909

21. BOYCE-CODD NORMAL
FORM
The 3NF was not satisfactory for some
tables:
That had multiple candidate keys.
Where the multiple candidate keys were
composite.
Where multiple candidate keys
overlapped(one attribute common).

22. BCNF:
A relation is in the BCNF if and only if
every determinant is a candidate
key.Here determinant is a single
attribute or composite attribute on which
some other attribute is fully functionally
dependent.

23. Guidelines for converting a table
into BCNF:
Find and remove overlapping candidate
keys.place the part of candidate key
and the attribute it is functionally
dependent.
Group the remaining items into a table.

24. STUDENT,SUBJECT,TEACHER
STUDENT SUBJECT TEACHER
Ajay
Ajay
Kumar
Kumar
Math
Physics
Math
Physics
Prof.White
Prof.Green
Prof.White
Prof.Neha

25. ANOMOLIES OF
BCNF:
If we wish to delete the
information that kumar is
study physics, we can’t do
so without loosing the
information that prof. Neha
teaches English.

26. Removal of
anomalies of BCNF
In order to make candidate
key teacher must be
candidate key.so original table
is break down to two table
1.(Student,Teacher)
2.(Teacher,subject)

27. FOURTH NORMAL FORM
A relation is said to be fourth normal
form(4NF) if and only if it is in third
normal or Boyce-Code Normal form and
it must have almost one or it should not
have more than one multivalued
dependency.

28. COURSE-STUDENT-BOOK
COURSE STUNAMETEXT-BOOK
PHYSICS
PHYSICS
PHYSICS
PHYSICS
CHEMISTRY
CHEMISTRY
ENGLISH
ENGLISH
ANKIT
ANKIT
RAHAT
RAHAT
ANKIT
ANKIT
RAJ
RAJ
MECHANICS
OPTICS
MECHANICS
OPTICS
O.C
I.C
ENGLISH
ENGLISH

29. RULE TO TRANSFER A RELATION
INTO FOURTH NORMAL FORM

30. COURSE-STUDENT
COURSE-
STUDENT
STUDENT-
NAME
PHYSICS
PHYSICS
CHEMISTRY
ENGLISH
ANKIT
RAHAT
ANKIT
RAJ

31. COURSE-BOOK
SUBJECT-
NAME
TEXT-BOOK
PHYSICS
PHYSICS
CHEMISTRY
CHEMISTRY
ENGLISH
ENGLISH
MECHANICS
OPTICS
O.CHEMISTRY
ING CHEMISTRY
ENGLISH
ENGLISH

32. FIFTH NORMAL FORM
A relation is said to be in 5
th
normal form
if it is in 4nf .
It cannot be further non-loss
decomposed.

33. AGENT_COMPANY_PRODUCT
AGENT COMPANY PRODUCT
SUNEET
RAJ
RAJ
SUNEET
SUNEET
ABC
ABC
ABC
CDE
ABC
NUT
BOLT
NUT
BOLT
BOLT

34. THANK YOU.