Logical Database Design & the
Relational Model
(Normalization)
Chapter 4:
Logical Database Design & the Relational Model,
Jeffrey A. Hoffer, Mary B. Prescott, Fred R. McFadden
TODAY'S LECTURE: OBJECTIVES
Well structured relation
Normalization
Use normalization to convert anomalous
tables to well-structured relations
2
NORMALIZATION
3
DATA NORMALIZATION
Primarily a tool to validate and improve a logical
design so that it satisfies certain constraints that
avoid unnecessary duplication of data
The process of decomposing relations with
anomalies to produce smaller, well-structured
relations
4
WELL-STRUCTURED RELATIONS
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 5
EXAMPLE
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
6
ANOMALIES IN THIS TABLE
Insertion–can’t enter a new employee without having
the employee take a class
Deletion–if we remove employee 140, we lose
information about the existence of a Tax Acc class
Modification–giving a salary increase to employee
100 forces us to update multiple records
Why do these anomalies exist?
Because there are two themes (entity types) in this
one relation. This results in data duplication and an
7
unnecessary dependency between the entities
FUNCTIONAL DEPENDENCIES AND KEYS
Functional Dependency: The value of one
attribute (the determinant) determines the value
of another attribute
Candidate Key:
A unique identifier.
One of the candidate keys will become the primary key
E.g. perhaps there is both credit card number and SS# in a
table…in this case both are candidate keys
Each non-key field is functionally dependent on every
candidate key
8
FUNCTIONAL DEPENDENCIES AND KEYS (CONT…)
a) EmpID → Name, DeptName, Salary
b) EmpID, CourseTitle → DateCompleted
9
Determinant: The attribute on the left side of the arrow in
a functional dependency.
Steps in normalization
10
PRELIMINARY TO NORMALIZATION
The first step (preliminary to normalization) is to represent the user view
(in this case, an invoice) as a single table, or relation, with the attributes
recorded as column headings. 11
Sample data should be recorded in the rows of the table, including any
repeating groups that are present in the data.
Table with multivalued attributes, not in 1st normal form
Note: this is NOT a relation
12
FIRST NORMAL FORM
Arelation that has a primary key and in
which there are no repeating groups.
No multivalued attributes
Every attribute value is atomic
All relations are in 1st Normal Form
13
Table with no multivalued attributes and unique rows, in 1st
normal form
Note: this is relation, but not a well-structured one 14
ANOMALIES IN THIS TABLE
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 15
SECOND NORMAL FORM
A relation in first normal form in which every non-
key attribute is fully functionally dependent on
the primary key.
1. 1NF
+
2. No partial functional dependencies
Every non-key attribute must be defined by the entire key, not by
only part of the key
Partial Functional Dependencies: A functional dependency in
which one or more non-key attributes are functionally
dependent on part (but not all) of the primary key.
16
Functional dependency diagram for INVOICE
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
17
Therefore, NOT in 2nd Normal Form
REMOVING PARTIAL DEPENDENCIES
To convert a relation with partial dependencies to
second normal form, the following steps are
required:
1) Create a new relation for each primary key attribute
(or combination of attributes) that is a determinant in
a partial dependency. That attribute is the primary
key in the new relation.
2) Move the non-key attributes that are dependent on
this primary key attribute (or attributes) from the old
relation to the new relation.
18
Removing partial dependencies
Getting it into
Second Normal
Form
Partial dependencies are removed, but there
are still transitive dependencies
19
THIRD NORMAL FORM
A relation that is in second normal form and has
no transitive dependencies.
1. 2NF
+
2. No transitive dependencies
No functional dependencies on non-primary-key attributes
Transitive dependency: A functional dependency
between the primary key and one or more non-key
attributes that are dependent on the primary key
via another non-key attribute.
20
Removing partial dependencies
Getting it into
Second Normal
Form
Partial dependencies are removed, but there
are still transitive dependencies
21
REMOVING TRANSITIVE DEPENDENCIES
You can easily remove transitive dependencies from a
relation by means of a three-step procedure:
1) For each non-key attribute (or set of attributes) that
is a determinant in a relation, create a new relation.
That attribute (or set of attributes) becomes the
primary key of the new relation.
2) Move all of the attributes that are functionally
dependent on the primary key of the new relation
from the old to the new relation.
3) Leave the attribute that serves as a primary key in
the new relation in the old relation to serve as a
foreign key that allows you to associate the two
22
relations.
Removing Transitive dependencies
Getting it into
Third Normal
Form
Transitive dependencies are removed
23
RELATIONAL SCHEMA FOR INVOICE DATA
(MICROSOFT VISIO NOTATION)
24
