Chapter 4: part c
Logical Database Design and the Relational
Model
Modern Database Management
12th Edition
Jeff Hoffer, Ramesh Venkataraman,
Heikki Topi
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-1
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
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-2
Functional Dependencies
•Functional Dependency: The value of
one set of attributes (the determinant)
determines the value of another
attribute.
•X functionally determines Y: X Y,
where X is called a determinant.
•Problem 4-33 (Fig. 4-33), p.195, 196.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-3 3
Figure 4-2
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-4 4
Figure 4-23
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-5 5
Candidate Keys
Candidate Key: Attribute or a set of attributes that uniquely
identifies a row.
Each non-key field is functionally dependent on every candidate key
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.
Primary vs. candidate key: Fig. 4-16, p.171
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-6 6
Figure 4.22 Steps in normalization
3rd normal form is
generally considered
sufficient
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-7
First Normal Form
•A relation is in 1NF if it contains no
multivalued attributes; i.e., every
attribute value is atomic.
•Fig. 4-25 is not in 1st Normal Form
(multivalued attributes) it is not a
relation.
•Fig. 4-26 is in 1st Normal form.
•All relations are in 1st Normal Form.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-8 8
ANOMALIES IN EMPLOYEE2 (FIG. 4-2B)
Insertion–can’t enter a new employee without having the
employee take a class (or at least empty fields of class
information)
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
unnecessary dependency between the entities.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-9 9
Second Normal Form
A relation is in 2NF if it is in 1NF and
every non-key attribute is fully
functionally dependent on the primary
key.
No partial functional dependencies (i.e.,
every non-key attribute must be defined
by the entire key, not by only part of the
key).
A B C D: A B C, A D. 2NF?
Is Fig. 4-23b (p. 181) in 2NF?
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-10 10
11
Second Normal Form (2NF)
•A relation is in 2NF if
•Primary key consists of a single
attribute; or
•Primary key consists of all attributes.
•Fig. 4-2b Fig. 4-1 + Fig. 4-7
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-11
Figure 4-1
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-12 12
Figure 4-7
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-13 13
14
Employee-Course Relations in
2NF
Both are full
functional
EmpID Name DeptName Salary
dependencies
EmpID CourseTitle DateCompleted
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-14
Third Normal Form
• A relation is in 3NF if it is in 2NF and no transitive
dependencies exists, where a transitive dependency is a
functional dependency between non-key attributes.
• A B C D: A B C, C D. 2NF? 3NF?
• 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.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-15 15
16
Relation with transitive dependency
(a) SALES relation with simple data
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-16
17
Relation with transitive dependency
CustID Name BUT
CustID Salesperson
CustID Region Salesperson Region
Transitive dependency
(not 3rd NF)
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-17
18
Removing a transitive dependency
(a) Decomposing the SALES relation
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-18
19
Relations in 3NF
Salesperson Region
CustID Name
CustID Salesperson
Now, there are no transitive dependencies…
Both relations are in 3rd NF
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-19
Table with multivalued attributes, not in 1st normal form
Note: This is NOT a relation.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-20
Table with no multivalued attributes and unique rows, in 1st
normal form
Note: This is a relation, but not a well-structured one.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-21
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 multiple 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.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-22
Figure 4-27 Functional dependency diagram for INVOICE
OrderID OrderDate, CustomerID, CustomerName, CustomerAddress
CustomerID CustomerName, CustomerAddress
ProductID ProductDescription, ProductFinish, ProductStandardPrice
OrderID, ProductID OrderQuantity
Therefore, NOT in 2nd Normal Form
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-23
Figure 4-28 Removing partial dependencies
Getting it into
Second Normal
Form
Partial dependencies are removed, but there
are still transitive dependencies
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-24
Figure 4-29 Removing partial dependencies
Getting it into
Third Normal
Form
Transitive dependencies are removed.
Chapter 4 Copyright © 2016 Pearson Education, Inc. 4-25 25