Philippine Science High School
Bito-on, Jaro, Iloilo City
Database
Normalization
MARCH 22, 2023
Example
Examine the following table:
Question–Is this a relation? Answer–Yes: Unique rows and no
multivalued attributes
Question–What’s the primary key? Answer–Composite: Emp_ID, Course_Title
2
Example
Let’s take a closer look…
Question–What’s the primary key? Answer–Composite: Emp_ID, Course_Title
3
Anomalies may happen… 4
What are
anomalies?
How we do check
for anomalies?
Anomalies can be discovered by
analyzing the data in the table in 3
different scenarios:
Adding a new record in the table
Updating a data in a record in the table
Deleting a record in the table
Example
Let’s take a closer analysis…
What happens when we add a new employee?
What happens when we update the salary of a new employee?
What happens when we delete an employee who has resigned?
6
Sample Scenario
Add a new employee:
160 Charles David Info. Systems 45,000 ? ?
Add a new employee course:
190 Lorenzo Davis - Microsoft 1/15/2023
Sample Scenario
Update an employee record:
110 Christian Lucero instead of Chris Lucero
Sample Scenario
Delete an employee record:
Margaret Simpson
10
What is the solution?
Data Normalization 11
Primarily a tool to validate and improve a logical design so
avoid
that it satisfies certain constraints that
unnecessary duplication of data
The process of decomposing relations with anomalies to
produce smaller, well-structured relations
Well-Structured Relations 12
A relation that contains minimal data redundancy and
allows users to insert, delete, and update rows without
causing data inconsistencies
Goal is to avoid anomalies
Insertion Anomaly–adding new rows forces user to create
duplicate data
Deletion Anomaly–deleting rows may cause a loss of data that
would be needed for other future rows
Modification Anomaly–changing data in a row forces changes
to other rows because of duplication
General rule of thumb: A table should not pertain to
more than one entity type
13
Database Normalization
• the process of structuring
a relational database in
accordance with a series of
so-called normal forms in
order to reduce data
redundancy and
improve data integrity
• was first proposed by British
computer scientist Edgar F.
Codd as part of his relational
model.
14
The objectives of normalization beyond 1NF (first normal form) were
stated by Codd as:
1. To free the collection of relations from undesirable insertion,
update and deletion dependencies.
2. To reduce the need for restructuring the collection of relations, as
new types of data are introduced, and thus increase the life span
of application programs.
3. To make the relational model more informative to users.
4. To make the collection of relations neutral to the query statistics,
where these statistics are liable to change as time goes by.
Steps in normalization
16
First Normal Form 17
No multivalued attributes
Every attribute value is atomic
Fig. 5-25 in the next slides is not in 1st
Normal Form (multivalued attributes) it
is not a relation
Fig. 5-26 in the succeeding slide is in 1st
Normal form
All relations are (or should be) in 1st
Normal Form
18
Table with multivalued attributes, not in 1st normal form
Note: this is NOT a relation
Table with no multivalued attributes and unique rows, in 1 st 19
normal form
Note: this is a relation, but not a well-structured one
Anomalies in this Table 20
Insertion–if new product is ordered for order
1007 of existing customer, customer data must
be re-entered, causing duplication
Deletion–if we delete the Dining Table from
Order 1006, we lose information concerning this
item's finish and price
Update–changing the price of product ID 4
requires update in several records
Why do these anomalies exist?
Because there are multiple themes (entity types)
in one relation. This results in duplication and an
unnecessary dependency between the entities
Second Normal Form 21
1NF PLUS every non-key attribute is
fully functionally dependent on the
ENTIRE primary key
Everynon-key attribute must be defined
by the entire key, not by only part of the
key
No partial functional dependencies
Functional dependency diagram for INVOICE 22
Order_ID Order_Date, Customer_ID, Customer_Name, Customer_Address
Customer_ID Customer_Name, Customer_Address
Product_ID Product_Description, Product_Finish, Unit_Price
Order_ID, Product_ID Order_Quantity
Therefore, NOT in 2nd Normal Form
Removing partial dependencies
Getting it into
Second Normal
Form
Partial dependencies are removed, but there
are still transitive dependencies
23
Third Normal Form 24
2NF PLUS no transitive dependencies (functional
dependencies on non-primary-key
attributes)
Note: This is called transitive, because the
primary key is a determinant for another
attribute, which in turn is a determinant for a
third
Solution: Non-key determinant with transitive
dependencies go into a new table; non-key
determinant becomes primary key in the
new table and stays as foreign key in the old
table
25
Removing partial dependencies
Getting it into
Third Normal
Form
Transitive dependencies are removed
Merging Relations 26
View Integration–Combining entities from
multiple ER models into common relations
Issues to watch out for when merging entities
from different ER models:
Synonyms–two or more attributes with different names
but same meaning
Homonyms–attributes with same name but different
meanings
Transitive dependencies–even if relations are in 3NF
prior to merging, they may not be after merging
Supertype/subtype relationships–may be hidden prior
to merging
Dyad Activity 27
Restructure the following table so that it is normalized to at
least 3NF:
