Chapter 5

Normalization of Database Tables

Prepared By :
Nor Intan Shafini Binti Nasaruddin
College of Computing, Informatics and Mathematics
UiTM Malacca, Jasin Campus

1
In this chapter, you will learn:
What normalization is and what role it plays in the database design process

About the normal forms 1NF, 2NF and 3NF.

How normal forms can be transformed from lower normal forms to higher
normal forms

That normalization and ER modeling are used concurrently to produce a

good database design

That some situations require denormalization to generate information

efficiently

2
 General Steps in
Normalization Process

First Normal Form Second Normal Form Third Normal Form

(1NF) (2NF) (3NF)

3
 Conversion to First Normal Form

• Relational table must not contain repeating

groups
• Normalizing the table structure will reduce
these data redundancies
• Normalization is three-step procedure
Step 1
• Eliminate the Repeating Groups

Step 2
• Identify the Primary Key

Step 3
• Identify all Dependencies
NIS 4
 Step 1: Eliminate the Repeating
Groups
Repeating groups
Derives its name from the fact that a group of multiple (related) entries
can exist for any single key attribute occurrence

• A relational table must not contain repeating groups

NIS 5
 Step 1: Eliminate the Repeating
Groups

• Present data in a tabular format, where each cell has a

single value and there are no repeating groups

• Eliminate repeating groups by eliminating

nulls, making sure that each repeating group attribute
contains an appropriate data value

NIS 6
 Data Organization:
An Unnormalized Table

NIS 7
 Data Organization:
After Removing Repeating Groups

NIS 8
 Step 2: Identify the Primary Key

• Primary key must uniquely identify attribute

value

Combination of Proj_Num and Emp_Num

• New key must be composed

NIS 9
 Step 3: Identify all Dependencies
• Dependencies can be depicted with the help of a
diagram
• Dependency diagram:
• Depicts all dependencies found within a given
table structure
• Helpful in getting bird’s-eye view of all
relationships among a table’s attributes
• Use makes it much less likely that an important
dependency will be overlooked

NIS 10
 Step 3: Identify all Dependencies

Functional Dependency: The value of one attribute

(the determinant) determines the value of another
attribute.
A → B reads “Attribute B is functionally dependent
on A”
A → B means if two rows have same value of A
they necessarily have same value of B

NIS 11
 Step 3: Identify all Dependencies

Determinant

Any attribute whose value determines other values

within a row

A determinant in a database table is an attribute that can be

used to determine the values assigned to other attributes in the
same row.
By this definition, any primary key or candidate key is a
determinant, but there may be determinants that are not
primary or candidate keys.

NIS 12
 Step 3: Identify all Dependencies

The left side of the above FD diagram is called

the determinant, and the right side is the dependent.
Here are a few examples.

For the second example, SIN and Course determine

the date completed (DateCompleted). This must also
work for a composite PK.

NIS 13
 A Dependency Diagram:
First Normal Form (1NF)

PD : Dependencies on only a part of PK

TD : nonprime attribute -> nonprime attribute

Prime Attribute = Key attribute

Nonprime attribute = Nonkey attribute

NIS 14
 A Dependency Diagram:
First Normal Form (1NF)
Emp_Num -> Emp_Name, Job_Class, Chg_Hour (PD)
Proj_Num -> Proj_Name (PD)
Job_Class -> Chg_Hour (TD)

NIS 15
 First Normal Form

• Tabular format in which:

• All key attributes are defined
• There are no repeating groups in the table
• All attributes are dependent on primary key
• All relational tables satisfy 1NF requirements
• Some tables contain partial dependencies
• Dependencies based on only part of the primary key
• Sometimes used for performance reasons, but should be used
with caution
• Still subject to data redundancies

NIS 16
 Conversion to Second Normal
Form
• Relational database design can be improved by
converting the database into second normal form (2NF)

• Two steps
Step 1: Identify All Key Components

Step 2: Identify the Dependent Attributes

NIS 17
 Step 1: Identify All Key Components

• Write each key component on separate line, and then

write the original (composite) key on the last line
Proj_Num
Emp_Num
Proj_Num, Emp_Num
• Each component will become the key in a new table

NIS 18
 Step 2: Identify the Dependent
Attributes
• Determine which attributes are dependent on which other
attributes
PROJECT (Proj_Num, Proj_Name)
EMPLOYEE (Emp_Num, Emp_Name, Job_Class,
Chg_Hour)
ASSIGN (Proj_Num, Emp_Num, Hours)
• At this point, most anomalies have been eliminated

NIS 19
 Second Normal Form (2NF)
Conversion Results

NIS 20
 Second Normal Form

• Table is in second normal form (2NF) if:

• It is in 1NF and

• It includes no partial dependencies:

• No attribute is dependent on only a portion of the primary key

NIS 21
 Conversion to Third Normal Form
Data anomalies created are easily eliminated by completing
three steps.
Step 1: Identify Each New Determinant
• For every transitive dependency, write its determinant
as a PK for a new table
• Job_Class
Step 2: Identify the Dependent Attributes
• Identify the attributes dependent on each determinant
identified in Step 1 and identify the dependency
• Job_Class -> Chg_Hour
• Name the table to reflect its contents and function
NIS 22
 Step 3: Remove the Dependent Attributes from Transitive
Dependencies
• Eliminate all dependent attributes in transitive
relationship(s) from each table that has such a transitive
relationship
• Draw a new dependency diagram to show all tables
defined in Steps 1–3
• Check new tables and modified tables from Step 3 to
make sure that each has a determinant and does not
contain inappropriate dependencies

PROJECT (Proj_Num, Proj_Name)

EMPLOYEE (Emp_Num, Emp_Name, Job_Class*)
JOB (Job_Class, Chg_Hour)
ASSIGN (Proj_Num, Emp_Num, Hours)
NIS 23
 Third Normal Form (3NF)
Conversion Results

NIS 24
 Third Normal Form

• A table is in third normal form (3NF) if:

• It is in 2NF and

• It contains no transitive dependencies

NIS 25
 Summary
• Normalization is a table design technique aimed at
minimizing data redundancies
• First three normal forms (1NF, 2NF, and 3NF) are
most commonly encountered
• Normalization is an important part—but only a
part—of the design process
• Continue the iterative ER process until all entities
and their attributes are defined and all equivalent
tables are in 3NF

26
 Summary
• A table in 3NF may contain multivalued
dependencies that produce either numerous null
values or redundant data
• It may be necessary to convert a 3NF table to the
fourth normal form (4NF) by
• splitting such a table to remove multivalued
dependencies
• Tables are sometimes denormalized to yield less I/O
which increases processing speed
27
 References

• Database Systems: Design, Implementation, &

Management, 6th Edition, Rob & Coronel
• Database Systems: Design, Implementation, and
Management, 13E, Coronel & Morris
• Damion Parmeter, [online], Available :
[Link]

28