Concepts of Database Management, Fifth Edition

1
Concepts of Database Management, Fifth Edition

• Chapter 5
• Database Design 1
• Normalization

2
Objectives

• Discuss functional dependence
• Discuss primary keys
• 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

3
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

4
Normalization

• Normalization process enables you to identify
  the existence of potential problems, called
  updating anomalies, in the design of a relational
  database
• Normal form possesses a certain desirable
  collection of properties

5
Figure 5.1Premiere Products Data
6
Functional Dependence

• Column B is functionally dependent on Column A if
  As value determines a single value for B at a
  given time
• Given A, a single value for B can be determined

7
Functional Dependence
8
Figures 5.3-5.4 Functional Dependence Example
Rep Table Where LastName can determine record
Rep Table Where LastName cannot determine record
9
Keys

• Column(s) 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 Keys
• Column(s) on which all other columns in table are
  functionally dependent
• Alternate Keys
• Candidate keys not chosen as primary keys

10
First Normal Form (1NF)

• Unnormalized table
• Contains a repeating group
• Table in 1NF
• Contains no repeating groups
• Removal of repeating groups is starting point in
  quest for problem-free tables

11
Figure 5.5 1NF Example
Unnormalized Table
12
Figure 5.6 1NF Example (cont.)
Conversion to 1NF
13
Second Normal Form (2NF)

• 1NF 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

14
Figure 5.7Second Normal Form
15
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
• Nonkey column (nonkey attribute) when a column
  is not a part of the primary key

16
Dependency Diagram

• Dependency diagram uses arrows to indicate all
  the functional dependencies present in a table
• Partial dependencies dependencies only on a
  portion of the primary key

17
Figure 5.8 Dependency Diagram for Orders
18
Third Normal Form (3NF)

• 2NF 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

19
Figure 5.9 2NF Example
20
Figure 5.10 Sample Customer Data
21
Figure 5.11 Customers Dependency Diagram
22
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

23
Figure 5.12 3NF Example
24
Figure 5.13 Incorrect Decomposition Example
25
Fourth Normal Form (4NF)

• 3NF Tables may still contain problems
• Dependencies
• Update Anomalies
• Update, additions, deletions
• Occur because of multivalued dependencies
• 4NF Table
• In 3NF and has no multivalued dependencies

26
Figure 5.15 Incorrect 4NF Example
27
Figure 5.16a 4NF Example
28
Figure 5.17 Normal Forms
29
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

30
Summary (cont.)

• Normal Forms First (1NF), Second (2NF),
  Third(3NF), and Fourth(4NF)
• 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