Table of Contents

Part I: Introduction to Databases and Database Design

Chapter 1: Goals of Effective Database Design 3

Understanding the Importance of Design 4

Information Containers 5

Strengths and Weaknesses of Information Containers 7

Desirable Database Features 8

CRUD 9

Retrieval 9

Consistency 10

Validity 10

Easy Error Correction 11

Speed 11

Atomic Transactions 12

ACID 13

Persistence and Backups 14

Low Cost and Extensibility 16

Ease of Use 16

Portability 16

Security 17

Sharing 18

Ability to Perform Complex Calculations 19

Consequences of Good and Bad Design 19

Summary 21

Exercises 22

Chapter 2: Database Types 23

Why Bother? 24

Flat Files 24

INI Files 26

Windows System Registry 26

Relational Databases 27

Spreadsheets 30

Hierarchical Databases 31

XML 34

XML Basics 34

XML Structures 37

XML Summary 39

Network 40

Object 42

Object-Relational 43

Exotic 44

Document-Oriented 44

Deductive 44

Dimensional 44

Temporal 45

Summary 45

Exercises 47

Chapter 3: Relational Database Fundamentals 49

Relational Points of View 49

Table, Rows, and Columns 50

Relations, Attributes, and Tuples 52

Keys 52

Indexes 54

Constraints 55

Basic Constraints 55

Check Constraints 55

Primary Key Constraints 55

Unique Constraints 56

Foreign Key Constraints 56

Database Operations 57

Summary 59

Exercises 59

Part II: Database Design Process and Techniques

Chapter 4: Understanding User Needs 65

Make a Plan 66

Bring a List of Questions 67

Functionality 67

Data Needs 67

Data Integrity 68

Security 68

Environment 69

Meet the Customers 69

Learn Who’s Who 70

Pick the Customers’ Brains 73

Walk a Mile in the User’s Shoes 73

Study Current Operations 74

Brainstorm 75

Look to the Future 76

Understand the Customers’ Reasoning 76

Learn What the Customers Really Need 77

Prioritize 78

Verify Your Understanding 79

Write the Requirements Document 80

Make Use Cases 81

Decide Feasibility 85

Summary 85

Exercises 86

Chapter 5: Translating User Needs into Data Models 89

What Are Data Models? 90

User Interface Models 92

Semantic Object Models 96

Classes and Objects 96

Cardinality 97

Identifiers 97

Putting It Together 97

Semantic Views 99

Class Types 100

Comments and Notes 106

Entity-Relationship Models 106

Entities, Attributes, and Identifiers 107

Relationships 107

Cardinality 108

Inheritance 109

Additional Conventions 112

Comments and Notes 113

Relational Models 113

Converting Semantic Object Models 113

Converting ER Diagrams 116

Summary 117

Exercises 118

Chapter 6: Extracting Business Rules 121

What Are Business Rules? 121

Identifying Key Business Rules 123

Extracting Key Business Rules 128

Multi-Tier Applications 129

Summary 133

Exercises 134

Chapter 7: Normalizing Data 137

What Is Normalization? 137

First Normal Form (1NF) 138

Second Normal Form (2NF) 146

Third Normal Form (3NF) 150

Stopping at Third Normal Form 154

Boyce-Codd Normal Form (BCNF) 154

Fourth Normal Form (4NF) 158

Fifth Normal Form (5NF) 162

Domain/Key Normal Form (DKNF) 165

Essential Redundancy 167

The Best Level of Normalization 168

Summary 169

Exercises 170

Chapter 8: Designing Databases to Support Software Applications 173

Plan Ahead 173

Document Everything 174

Consider Multi-Tier Architecture 175

Convert Domains into Tables 175

Keep Tables Focused 176

Use Three Kinds of Tables 176

Use Naming Conventions 178

Allow Some Redundant Data 179

Don’t Squeeze in Everything 180

Summary 181

Exercises 182

Chapter 9: Common Design Patterns 185

Associations 185

Many-to-Many Associations 186

Multiple Many-to-Many Associations 186

Multiple-Object Associations 188

Repeated Attribute Associations 191

Reflexive Associations 193

Temporal Data 200

Effective Dates 200

Deleted Objects 202

Deciding What to Temporalize 203

Logging and Locking 203

Audit Trails 203

Turnkey Records 204

Summary 205

Exercises 205

Chapter 10: Common Design Pitfalls 207

Lack of Preparation 207

Poor Documentation 208

Poor Naming Standards 208

Thinking Too Small 210

Not Planning for Change 210

Too Much Normalization 213

Insufficient Normalization 213

Insufficient Testing 214

Performance Anxiety 214

Mishmash Tables 215

Not Enforcing Constraints 217

Obsession with IDs 218

Not Defining Natural Keys 220

Summary 221

Exercises 222

Part III: A Detailed Case Study

Chapter 11: User Needs and Requirements 227

Meet the Customers 227

Pick the Customers’ Brains 229

Determining What the System Should Do 229

Determining How the Project Should Look 231

Determining What Data Is Needed for the User Interface 232

Determining Where the Data Should Come From 233

Determining How the Pieces of Data Are Related 233

Determining Performance Needs 235

Determining Security Needs 236

Determining Data Integrity Needs 236

Write Use Cases 238

Write the Requirements Document 242

Demand Feedback 243

Summary 244

Exercises 244

Chapter 12: Building a Data Model 245

Semantic Object Modeling 245

Building an Initial Semantic Object Model 245

Improving the Semantic Object Model 248

Entity-Relationship Modeling 250

Building an ER Diagram 250

Building a Combined ER Diagram 252

Improving the Entity-Relationship Diagram 254

Relational Modeling 256

Putting It All Together 259

Summary 260

Exercises 261

Chapter 13: Extracting Business Rules 263

Identifying Business Rules 263

Courses 264

CustomerCourses 266

Customers 267

Pets 267

Employees 267

Orders 267

OrderItems 268

InventoryItems 268

TimeEntries 268

Shifts 269

Persons 269

Phones 269

Vendors 269

Drawing a New Relational Model 270

Summary 270

Exercises 271

Chapter 14: Normalization and Refinement 273

Improving Flexibility 273

Verifying First Normal Form 275

Verifying Second Normal Form 278

Pets 279

TimeEntries 280

Verifying Third Normal Form 281

Summary 283

Exercises 284

Part IV: Implementing Databases (with Examples in Access and MySQL)

Chapter 15: Microsoft Access 287

Understanding Access 287

Getting Started 288

Defining Relationships 293

Creating Field Constraints 296

Creating Table Constraints 298

Creating Queries 301

Query Design View 304

SQL View 306

Summary 308

Exercises 308

Chapter 16: MySQL 313

Installing MySQL 313

Using MySQL Command Line Client 314

Executing SQL Scripts 318

Using MySQL Query Browser 319

Executing Queries 320

Editing Data 320

Creating and Modifying Databases 321

Using Scripts 327

Getting Syntax Help 329

Using MySQL Workbench 330

Loading Scripts 330

Creating EER Diagrams 331

Editing Databases 333

Defining Triggers 334

Exporting Scripts 336

Summary 338

Exercises 338

Part V: Advanced Topics

Chapter 17: Introduction to SQL 345

Background 345

Finding More Information 346

Standards 346

Basic Syntax 347

Command Overview 348

Create Table 350

Create Index 355

Drop 356

Insert 357

Select 358

Select Clause 358

From Clause 359

Where Clause 362

Group By Clause 362

Order By Clause 363

Update 364

Delete 365

Summary 366

Exercises 366

Chapter 18: Building Databases with SQL Scripts 369

Why Bother with Scripts? 369

Script Categories 370

Database Creation Scripts 370

Basic Initialization Scripts 370

Data Initialization Scripts 370

Cleanup Scripts 371

Saving Scripts 371

Ordering SQL Commands 371

Summary 376

Exercises 377

Chapter 19: Database Maintenance 379

Backups 379

Data Warehousing 382

Repairing the Database 383

Compacting the Database 383

Performance Tuning 383

Summary 386

Exercises 387

Chapter 20: Database Security 389

The Right Level of Security 389

Passwords 390

Single-Password Databases 390

Individual Passwords 390

Operating System Passwords 391

Good Passwords 391

Privileges 392

Initial Configuration and Privileges 398

Too Much Security 398

Physical Security 399

Summary 400

Exercises 401

Appendix A: Exercise Solutions 403

Chapter 1 403

Chapter 2 408

Chapter 3 411

Chapter 4 413

Chapter 5 417

Chapter 6 424

Chapter 7 430

Chapter 8 434

Chapter 9 436

Chapter 10 440

Chapter 11 442

Chapter 12 446

Chapter 13 449

Chapter 14 451

Chapter 15 454

Chapter 16 454

Chapter 17 454

Chapter 18 457

Chapter 19 459

Chapter 20 460

Appendix B: Sample Database Designs 467

Books 468

DVD and Movies 470

Music and CDs 471

Documents 472

Customer Orders 472

Employee Shifts and Timesheets 473

Employees, Projects, and Departments 473

Employee Skills and Qualifications 475

Identical Object Rental 476

Distinct Object Rental 476

Students, Courses, and Grades 478

Teams 479

Individual Sports 480

Vehicle Fleets 480

Contacts 483

Passengers 483

Recipes 485

Glossary 487

Index 497