Contents
SQL Enhancements for Querying a Single Table 56
Sorting the SQL Query Results 56 • SQL WHERE Clause Options 58
• Combining the SQL WHERE Clause and the SQL ORDER BY Clause 63
Performing Calculations in SQL Queries 63
Using SQL Built-in Functions 63 • SQL Expressions in SQL SELECT
Statements 66
Grouping in SQL SELECT Statements 68
Looking for Patterns in NASDAQ Trading 72
Investigating the Characteristics of the Data 72 • Searching for Patterns in
Trading by Day of Week 73
Querying Two or More Tables with SQL 75
Querying Multiple Tables with Subqueries 75 • Querying Multiple Tables
with Joins 78 • Comparing Subqueries and Joins 82
Summary 82 • Key Terms 82 • Review Questions 83 • Project
Questions 88 • Marcia’s Dry Cleaning 92 • Morgan Importing 94
PART 2 • DATABASE DESIGN 99
Chapter 3: The Relational Model and Normalization 100
Chapter Objectives 99
Relational Model Terminology 102
Relations 103 • Characteristics of Relations 103 • Alternative
Terminology 105 • Functional Dependencies 106 • Finding Functional
Dependencies 107 • Keys 110
Normal Forms 112
Modification Anomalies 112 • A Short History of Normal Forms 113
• Normalization Categories 113 • From First Normal Form to Boyce-Codd
Normal Form Step-By-Step 114 • Eliminating Anomalies from Functional
Dependencies with BCNF 118 • Eliminating Anomalies from Multivalued
Dependencies 126 • Fifth Normal Form 130 • Domain/Key Normal
Form 130
Summary 131 • Key Terms 131 • Review Questions 132 • Project
Questions 134 • Marcia’s Dry Cleaning 135 • Morgan Importing 136
Chapter 4: Database Design Using Normalization 137
Chapter Objectives 137
Assess Table Structure 138
Designing Updatable Databases 139
Advantages and Disadvantages of Normalization 139 • Functional
Dependencies 139 • Normalizing with SQL 140 • Choosing Not to Use
BCNF 141 • Multivalued Dependencies 142
Designing Read-Only Databases 142
Denormalization 142 • Customized Duplicated Tables 144
Common Design Problems 145
The Multivalue, Multicolumn Problem 145 • Inconsistent Values 147
• Missing Values 148 • The General-Purpose Remarks Column 148
Summary 149 • Key Terms 150 • Review Questions 150 • Project
Questions 152 • Marcia’s Dry Cleaning 152 • Morgan Importing 153
Chapter 5: Data Modeling with the Entity-Relationship Model 155
Chapter Objectives 155
The Purpose of a Data Model 156