Transcript Chapter 5: The Relational Model and Normalization

Database Processing
Chapter 5
The Relational Model and
Normalization
David M. Kroenke
© 2000 Prentice Hall
Chapter 5
The Relational Model
• Broad, flexible model
• Basis for almost all DBMS products
• E.F. Codd defined well-structured
“normal forms” of relations,
“normalization”
Page 113
© 2000 Prentice Hall
Chapter 5
Relation
• Two-dimensional table
• Rows are tuples
• Columns are attributes
Page 113
© 2000 Prentice Hall
Equivalent Relational Terms
Page 114
Figure 5-1
© 2000 Prentice Hall
Functional Dependency
“relationship between or among
attributes”
Page 114
Figure 5-2
© 2000 Prentice Hall
Chapter 5
Functional Dependency
Notation
SID  Major
ComputerSerialNumber  MemorySize
(SID, ClassName)  Grade
Page 115
© 2000 Prentice Hall
Key
“a group of one or more attributes that
uniquely identifies a row”
Page 116
Figure 5-3
© 2000 Prentice Hall
Combination Key
Page 117
Figure 5-4
© 2000 Prentice Hall
Chapter 5
Normalization
“the process of evaluating and
converting a relation to reduce
modification anomalies”
Page 118
© 2000 Prentice Hall
Chapter 5
Anomaly
“an undesirable consequence of data
modification in which two or more
different themes are entered
(insertion anomaly) in a single row or
two or more themes are lost if the
row is deleted (deletion anomaly)”
Page 118
© 2000 Prentice Hall
Chapter 5
Normal Forms
“classes of relations and techniques for
preventing anomalies”
DK/NF = Domain Key Normal Form
(free of modification anomalies)
Page 118
© 2000 Prentice Hall
First Normal Form
“any table of data that meets the
definition of a relation”
Figure 5-3
© 2000 Prentice Hall
Second Normal Form
“when all of a relation’s nonkey attributes
are dependent on all of the key”
Figure 5-5
© 2000 Prentice Hall
Third Normal Form
“if it is in second normal form and has
no transitive dependencies”
Figure 5-7
© 2000 Prentice Hall
Boyce-Codd Normal Form
“if every determinant is a candidate key”
Figure 5-8
© 2000 Prentice Hall
Fourth Normal Form
“if in BCNF and has no multi-value
dependencies”
Figure 5-11
© 2000 Prentice Hall
Chapter 5
Fifth Normal Form
?
Page 125
© 2000 Prentice Hall
Chapter 5
Domain Key Normal Form
“if every constraint on the relation is a
logical consequence of the definition
of keys and domains”
Page 125
© 2000 Prentice Hall
Chapter 5
DK/NF Terms
• Constraint “a rule governing static
values of attributes”
• Key “unique identifier of a tuple”
• Domain “description of an
attribute’s allowed values”
Page 126
© 2000 Prentice Hall
DK/NF Example
Figure 5-13
© 2000 Prentice Hall
DK/NF Example
Figure 5-15
© 2000 Prentice Hall
DK/NF Example
Figure 5-16
© 2000 Prentice Hall
Summary of Normal Forms
Figure 5-18
© 2000 Prentice Hall
Chapter 5
A  B relationships
A  B and B  A
A  B but B not  A
A not  B and B not  A
Page 131
one-to-one
many-to-one
many-to-many
© 2000 Prentice Hall
Summary of Relationships
Figure 5-19
© 2000 Prentice Hall
Chapter 5
Optimization
• De-Normalization
• Controlled Redundancy
Page 135
© 2000 Prentice Hall