Transcript chapter05 - Using the Staff Site

Concepts of Database
Management, Fifth Edition
Chapter 5:
Database Design 1:
Normalization
Objectives
 Discuss
functional dependence
 Discuss
primary keys
5
 Define
first normal form, second normal form,
and third normal form
 Describe
the problems associated with tables
(relations) that are not in first normal form,
second normal form, or third normal form along
with the mechanism for converting to all three
Concepts of Database Management
2
5
Objectives
 Discuss
the problems associated with
incorrect conversions to third normal form
 Define
fourth normal form
 Describe
the problems associated with tables
(relations) that are not in fourth normal form
and describe the mechanism for converting
to fourth normal form
 Understand
how normalization is used in the
database design process
Concepts of Database Management
3
5
Normalization
process – enables you to identify
the existence of potential problems, called
updating anomalies, in the design of a relational
database
 Normalization
form – possesses a certain desirable
collection of properties
 Normal
Concepts of Database Management
4
5
Figure 5.1:
Premiere Products Data
Concepts of Database Management
5
5
Functional Dependence
 Column
B is functionally dependent on Column
A if A’s value determines a single value for B at
a given time
 Given A,
Concepts of Database Management
a single value for B can be determined
6
5
Functional Dependence
Concepts of Database Management
7
Figures 5.3-5.4:
Functional Dependence Example
5
Rep Table Where LastName can determine record
Rep Table Where LastName cannot determine record
Concepts of Database Management
8
Keys
 Column(s)
5
C is primary key for table T if:
Property 1: All columns in T are functionally
dependent on C
Property 2: No subcollection of columns in C
(assuming C is a collection of columns and not
just a single column) also has Property 1
 Candidate

Column(s) on which all other columns in table are
functionally dependent
 Alternate

Keys
Keys
Candidate keys not chosen as primary keys
Concepts of Database Management
9
First Normal Form (1NF)
 Unnormalized

table
Contains a repeating group
 Table

5
in 1NF
Contains no repeating groups
 Removal
of repeating groups is starting point in
quest for problem-free tables
Concepts of Database Management
10
5
Figure 5.5: 1NF Example
Unnormalized Table
Concepts of Database Management
11
5
Figure 5.6: 1NF Example (con’t.)
Conversion to 1NF
Concepts of Database Management
12
Second Normal Form (2NF)
 1NF
5
Tables may contain problems

Redundancy

Update Anomalies
 Update,
inconsistent data, additions, deletions
 Occur
because a column is dependent on a
portion of a multi-column primary key
 2NF

Table
In 1NF and no nonkey column is dependent on
only a portion of the primary key
Concepts of Database Management
13
5
Figure 5.7:
Second Normal Form
Concepts of Database Management
14
5
Update Anomalies
 Update

Information is in multiple rows, difficult to update
 Inconsistent

data
Because of the duplication, a row that is not updated
causes inconsistency
 Additions

Dummy records are required to add new unused
dependent rows
 Deletions
column (nonkey attribute) – when a
column is not a part of the primary key
 Nonkey
Concepts of Database Management
15
5
Dependency Diagram
diagram – uses arrows to indicate
all the functional dependencies present in a
table
 Dependency
dependencies – dependencies only on
a portion of the primary key
 Partial
Concepts of Database Management
16
Figure 5.8:
Dependency Diagram for Orders
Concepts of Database Management
5
17
Third Normal Form (3NF)
 2NF
5
Tables may still contain problems

Redundancy and wasted space

Update Anomalies
 Update,
inconsistent data, additions, deletions
 Occur
because a column is dependent on a
portion of a multi-column primary key
 3NF

Table
In 2NF and the only determinants contained are
candidate keys
Concepts of Database Management
18
5
Figure
5.9: 2NF
Example
Concepts of Database Management
19
Figure 5.10: Sample Customer Data
Concepts of Database Management
5
20
Figure 5.11:
Customers Dependency Diagram
Concepts of Database Management
5
21
5
Incorrect Decomposition
 Decomposition
must take place according to
that described for 3NF
 Even
though you may decompose a table, you
run the risk of splitting the functional
dependence across different tables
Concepts of Database Management
22
5
Figure 5.12:
3NF Example
Concepts of Database Management
23
5
Figure 5.13:
Incorrect
Decomposition
Example
Concepts of Database Management
24
Fourth Normal Form (4NF)
 3NF
Tables may still contain problems

Dependencies

Update Anomalies
 Update,
 Occur
 4NF

5
additions, deletions
because of multivalued dependencies
Table
In 3NF and has no multivalued dependencies
Concepts of Database Management
25
5
Figure 5.15:
Incorrect
4NF Example
Concepts of Database Management
26
5
Figure 5.16a:
4NF Example
Concepts of Database Management
27
5
Figure 5.17: Normal Forms
Concepts of Database Management
28
5
Summary
 Normalization
is a process of optimizing
databases to prevent update anomalies
 Normalization
attempts to correct update issues
by eliminating duplication
 Duplication
also creates inconsistency
 Insertions
can violate database integrity if the
database is not normalized
 Deletions
can violate database integrity if the
database is not normalized
Concepts of Database Management
29
5
Summary (con’t.)
Forms – First (1NF), Second (2NF),
Third(3NF), and Fourth(4NF)
 Normal
 1NF
has no repeating groups
 2NF
is in 1NF and no non-key column is
dependent on only a portion of the primary key
 3NF
is in 2NF and the only determinants are
candidate keys
 4NF
is in 3NF and has no multivalued
dependencies
Concepts of Database Management
30