Beginning Database Design: From Novice to Professional

Cover......Page 1
Contents at a Glance......Page 6
Contents......Page 8
Foreword......Page 14
About the Author......Page 16
About the Technical Reviewer......Page 18
Acknowledgments......Page 20
Introduction......Page 22
Analysis Techniques......Page 23
Create a Data Model......Page 24
Database Implementation......Page 25
Objective of This Book......Page 26
Mishandling Keywords and Categories......Page 28
Repeated Information......Page 32
Designing for a Single Report......Page 35
Summary......Page 36
2 Guided Tour of the Development Process......Page 38
Initial Problem Statement......Page 39
Analysis and Simple Data Model......Page 41
Classes and Objects......Page 42
Relationships......Page 43
Further Analysis: Revisiting the Use Cases......Page 46
Design......Page 50
Implementation......Page 51
Interfaces for Input Use Cases......Page 52
Reports for Output Use Cases......Page 53
Summary......Page 55
3 Initial Requirements and Use Cases......Page 58
Real and Abstract Views of a Problem......Page 60
Task Automation......Page 61
What Does the User Do?......Page 63
What Data Is Involved?......Page 64
What Is the Objective of the System?......Page 65
What Data Is Required to Satisfy the Objective?......Page 67
What Are the Input Use Cases?......Page 69
What Is the First Data Model?......Page 71
What Are the Output Use Cases?......Page 72
Actors......Page 74
Use Cases for Maintaining Data......Page 75
Finding Out More About the Problem......Page 76
Meals That Are Discontinued......Page 77
Summary......Page 78
4 Learning from the Data Model......Page 80
Review of Data Models......Page 81
Student Course Example......Page 84
Customer Order Example......Page 85
Insect Example......Page 86
Insect Example......Page 87
Sports Club Example......Page 89
Sports Club Example......Page 90
Departments Example......Page 91
Insect Example......Page 92
A Many–Many: Are We Missing Anything?......Page 93
Sports Club Example......Page 94
Student Course Example......Page 96
Meal Delivery Example......Page 97
Summary......Page 99
Attribute, Class, or Relationship?......Page 102
Two or More Relationships Between Classes......Page 105
Redundant Information......Page 108
Routes Providing Different Information......Page 110
False Information from a Route (Fan Trap)......Page 111
Gaps in a Route Between Classes (Chasm Trap)......Page 112
Relationships Between Objects of the Same Class......Page 114
Relationships Involving More Than Two Classes......Page 116
Summary......Page 119
Classes or Objects with Much in Common......Page 122
Specialization......Page 124
Generalization......Page 125
Inheritance in Summary......Page 127
Confusing Objects with Subclasses......Page 129
Confusing an Association with a Subclass......Page 130
When Is Inheritance Worth Considering?......Page 131
Should the Superclass Have Objects?......Page 132
Objects That Belong to More Than One Subclass......Page 134
It Isn’t Easy......Page 137
Summary......Page 138
7 From Data Model to Relational Schema......Page 140
Representing the Model......Page 141
Representing Classes and Attributes......Page 142
Creating a Table......Page 143
Choosing Data Types......Page 145
Adding Constraints on Data Values......Page 147
Checking Character Fields......Page 148
Determining a Primary Key......Page 149
Concatenated Keys......Page 150
Representing Relationships......Page 153
Foreign Keys......Page 154
Referential Integrity......Page 155
Representing 1–Many Relationships......Page 156
Representing Many–Many Relationships......Page 158
Representing 1–1 Relationships......Page 160
Representing Inheritance......Page 161
Summary......Page 163
8 Normalization......Page 166
Insertion Problems......Page 167
Dealing with Update Anomalies......Page 168
Definition of a Functional Dependency......Page 169
Functional Dependencies and Primary Keys......Page 170
First Normal Form......Page 172
Second Normal Form......Page 174
Third Normal Form......Page 176
Boyce-Codd Normal Form......Page 177
Data Models or Functional Dependencies?......Page 178
Fourth and Fifth Normal Forms......Page 180
Summary......Page 182
More About ID Numbers......Page 184
An ID Number or a Concatenated Key?......Page 186
Unique Constraints......Page 189
Using Constraints Instead of Category Classes......Page 191
Deleting Referenced Records......Page 194
Summary......Page 197
Simple Queries on One Table......Page 198
The Project Operation......Page 199
The Select Operation......Page 200
Aggregates......Page 201
Queries with Two or More Tables......Page 203
The Join Operation......Page 204
Set Operations......Page 208
Indexes and Simple Queries......Page 210
Disadvantages of Indexes......Page 212
Indexes and Joins......Page 213
Types of Indexes......Page 214
Uses for Views......Page 215
Summary......Page 217
Input Forms......Page 218
Data Entry Forms Based on Several Tables......Page 220
Constraints on a Form......Page 223
Web Forms......Page 225
Basing Reports on Views......Page 226
Main Parts of a Report......Page 227
Grouping and Summarizing......Page 229
Summary......Page 231
Object-Oriented Implementation......Page 232
Classes and Objects......Page 233
Complex Types and Methods......Page 235
Collections of Objects......Page 237
Representing Relationships......Page 238
OO Environments......Page 241
Implementing a Data Model in a Spreadsheet......Page 242
1–Many Relationships......Page 243
Many–Many Relationships......Page 246
Spreadsheets......Page 249
Understanding the Objective and Requirements......Page 252
Representing Your Model in a Relational Database......Page 253
Using Your Database......Page 254
And So . . .......Page 255
Index......Page 256