Normalization

• Normalization is a process of analyzing a

relation to ensure it is well formed

• More specifically, if a relation is normalized,

rows can be added, removed, or updated
without creating exceptions
 Normalization

The process of decomposing relations with

anomalies to produce smaller well-structured
relations.
Anomalies: Errors or inconsistencies that may
result when user attempts to update a table
that contains redundant data.
Well-structured relations contains minimal
redundancy and allows users to insert, modify,
and delete the rows in a table without errors or
inconsistencies.
 Anomalies

• Relations that are not normalized will experience

issues known as anomalies

– Insertion anomaly
• Difficulties inserting data into a relation

– Modification anomaly
• Difficulties modifying data into a relation

– Deletion anomaly
• Difficulties deleting data from a relation
 Stages of Normal Forms

• 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)
• Domain/Key Normal Form (DK/NF)
 First Normal Form

A relation is in first normal form when it contains

no multi-valued attributes.
The value at the intersection of each row and
column must be atomic.
 TABLE WITH REPEATING GROUPS
EMPLOYEE

Emp_ID Name Dept_Nam Salar Course_Nam Date_Comple

e y e ted
1233 Andrew Market 48,00 SSAD 06/12/1999
0 MS-Office 12/05/1998
1245 James Accountin 52,00 Taxation 09/07/1998
g 0
1456 Mary MIS 80,00 C++ 03/03/2000
0 D2000 12/01/1998
Java Basics 14/06/1999

1789 Robert MIS 90,00 DB2 03/03/1998

0 CICS 29/10/1999
SSAD 15/05/1999
 TABLE IN FIRST NORMAL FORM

EMPLOYEE

Emp_ID Name Dept_Name Salary Course_Nam Date_Comple

e ted
1233 Andrew Marketing 48,000 SSAD 06/12/1999

1233 Andrew Marketing 48,000 MS-Office 12/05/1998

1245 James Accounting 52,000 Taxation 09/07/1998

1456 Mary MIS 80,000 C++ 03/03/2000

1456 Mary MIS 80,000 D2000 12/01/1998

1456 Mary MIS 80,000 Java Basics 14/06/1999

 Project
Proj_Nu Proj_ Emp_n Emp_ Job_Cl Chg_h Hours
m Nam um Name ass our
e
15 Everg 103 June E Elec_En $84.50 23.8
reen Arbou gineer
gh
101 John G Databas $105.0 19.4
News e 0
Designe
r
105 Alice K Databas $105.0 35.7
Johnso e 0
n Designe
r
 Project

Proj_Nu Proj Emp_n Emp_ Job_Cl Chg_h Hours

m _Na um Name ass our
me
15 Everg 103 June E Elec_E $84.50 23.8
reen Arbou ngineer
gh
15 Everg 101 John Databa $105.0 19.4
reen G se 0
News Design
er
15 Everg 105 Alice K Databa $105.0 35.7
a ta ion :
D at al
reen Johns se 0 n iz rm
ga o
on Design Or irst N rm
F Fo
er
Example for 1NF
 Functional Dependencies And Keys

Normalization is based on the analysis of

functional dependencies.
Functional dependency A constraint between
two attributes or two sets of attributes.

Emp_ID, Course_Name
Date_Completed

The date a course is completed is determined by

the identity of the employee and the name of
the course.
 Second Normal Form

A relation that is in first normal form and has

every non-key attribute functionally dependent
on the primary key.
 Second Normal Form

A relation that is in first normal form is in second

normal form if and only if
2. The primary key consists of only one attribute.
3. No non-key attribute exists in the relation.
4. Every no-key attribute is functionally
dependent on the primary key.

To convert relation into second normal form, we

decompose the relation into new relationships.
 Second Normal Form
EMPLOYEE is decomposed into two relations
EMPLOYE1
Emp_ID Name Dept_Name Salary
1233 Andrew Marketing 48,000
1245 James Accounting 52,000
1456 Mary MIS 80,000
EMPLOYE2

Emp_ID Course_Name Date_Completed

1233 SSAD 06/12/1999
1233 MS-Office 12/05/1998
1456 C++ 03/03/2000
1456 D2000 12/01/1998
1456 Java Basics 14/06/1999
After applying 2NF
 Third Normal Form

Transitive dependency
Functional dependency between two nor more
non-key attributes.

A relation is in third normal form (3NF), if it is in

second normal form and no transitive
dependencies exist.
 Third Normal Form

Relation with transitive dependency

SALES
CustID Name Salesperson Region

Cust_ID is the primary key. All of the remaining attributes are

functionally dependent on this attribute

However, region is functionally dependent on sales

person and salesperson is functionally dependent on Cust_ID.
Salesperson Region

After applying
Third Normal
al
Form, Remov
of Transitive
Dependency

CustID Name Salesperson

Example for 3NF
Solution to Previous Example
 Boyce/Codd normal form

Boyce / Codd normal form Any remaining

anomalies that result from functional
dependencies have been removed