Database Processing Chapter 5 The Relational Model and

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 Computer. Serial. Number Memory. Size (SID, Class. Name) 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 one-to-one A B but B not A many-to-one A not B and B not A many-to-many Page 131 © 2000 Prentice Hall

Summary of Relationships Figure 5 -19 © 2000 Prentice Hall

Chapter 5 Optimization • De-Normalization • Controlled Redundancy Page 135 © 2000 Prentice Hall