Databases:
A Beginner’s Guide
About the Author
Andrew J. (Andy) Oppel is a proud graduate of The Boys’ Latin School of Maryland and of
Transylvania University (Lexington, Kentucky) where he earned a BA in computer science
in 1974. Since then, he has been continuously employed in a wide variety of information
technology positions, including programmer, programmer/analyst, systems architect, project
manager, senior database administrator, database group manager, consultant, database
designer, data modeler, and data architect. In addition, he has served as a part-time instructor
with the University of California, Berkeley, Extension for more than 20 years and received
the Honored Instructor Award for the year 2000. His teaching work included developing three
courses for UC Extension, “Concepts of Database Management Systems,” “Introduction to
Relational Database Management Systems,” and “Data Modeling and Database Design.” He
also earned his Oracle 9i Database Associate certification in 2003. He is currently employed
as a senior data modeler for Blue Shield of California. In addition to computer systems, Andy
enjoys music (guitar and vocals), amateur radio (Pacific Division Vice Director, American
Radio Relay League), and soccer (Referee Instructor, U.S. Soccer).
Andy has designed and implemented hundreds of databases for a wide range of
applications, including medical research, banking, insurance, apparel manufacturing,
telecommunications, wireless communications, and human resources. He is the author
of Databases Demystified (McGraw-Hill Professional, 2004) and SQL Demystified
(McGraw-Hill Professional, 2005), and is co-author of SQL: A Beginner’s Guide
(McGraw-Hill Professional, 2009). His database product experience includes IMS, DB2,
Sybase ASE, Microsoft SQL Server, Microsoft Access, MySQL, and Oracle (versions 7,
8, 8i, 9i, and 10g).
If you have any comments, please contact Andy at
About the Technical Editor
Todd Meister has been developing using Microsoft technologies for more than ten years.
He’s been a Technical Editor on more than 50 books with topics ranging from SQL Server
to the .NET Framework. In addition to technical editing, he serves as an Assistant Director

McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work
is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WAR-
RANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM
USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA
HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUD-
ING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR
PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your
requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to
you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting there-
from. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circum-
stances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar
damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such
damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in con-
tract, tort or otherwise.
v
Contents
ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
PART I
Database Concepts
1
Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Properties of a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
The Database Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Layers of Data Abstraction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Physical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Logical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Prevalent Database Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Flat Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Creating Queries in Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Try This 3-1: List All Customers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Try This 3-2: Choose Columns to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Try This 3-3: Sorting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Try This 3-4: Advanced Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Try This 3-5: Choosing Rows to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Try This 3-6: Compound Row Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Try This 3-7: Using Not Equal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Try This 3-8: Joining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Try This 3-9: Limiting Join Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Try This 3-10: Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Try This 3-11: Microsoft Access SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Try This 3-12: Multiple Joins and Calculated Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Try This 3-13: Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Try This 3-14: Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
4
Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
A Brief History of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Getting Started with Oracle SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Try This 4-1: Unlock the HR Account and Log in as HR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Where’s the Data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Finding Database Objects Using Catalog Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Try This 4-2 Using the Application Express Object Browser . . . . . . . . . . . . . . . . . . . . . . . . . 130
Viewing Database Objects Using the Object Browser . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Data Query Language (DQL): The SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Listing All Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Limiting Columns to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Sorting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Choosing Rows to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Joining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143

Rapid Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
The Project Triangle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Try This 5-1: Project Database Management Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
6
Database Design Using Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
The Need for Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Insert Anomaly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Delete Anomaly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Update Anomaly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Applying the Normalization Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Choosing a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
First Normal Form: Eliminating Repeating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Second Normal Form: Eliminating Partial Dependencies . . . . . . . . . . . . . . . . . . . . . . 200
Third Normal Form: Eliminating Transitive Dependencies . . . . . . . . . . . . . . . . . . . . . 203
Beyond Third Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Contents
vii

viii
Databases: A Beginner’s Guide
Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Try This 6-1: UTLA Academic Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Practice Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Try This 6-2: Computer Books Company . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
7
Data and Process Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Entity Relationship Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
ERD Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Super Types and Subtypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Guidelines for Drawing ERDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235

Client/Server Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285
Connecting Databases to the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Introduction to the Internet and the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Components of the Web “Technology Stack” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Invoking Transactions from Web Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Connecting Databases to Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Connecting Databases via ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295
Connecting Databases via OLE DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Connecting Databases to Java Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Try This 9-1: Exploring the World Wide Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
10
Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Why Is Security Necessary? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Database Server Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Physical Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Network Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
System-Level Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Database Client and Application Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Login Credentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Data Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312
Other Client Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
Database Access Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Database Security Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Schema Owner Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320
Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Security Monitoring and Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322
Try This 10-1: Database Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323

SQL/XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
SQL/XML Mapping Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
Try This 13-1: Using SQL/XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Object-Oriented Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Object-Oriented Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Object-Oriented Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Object Persistence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387
Object-Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392
PART IV
Appendices
A
Answers to Self Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Chapter 1: Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402
Chapter 2: Exploring Relational Database Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Chapter 3: Forms-based Database Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Chapter 4: Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410
Chapter 5: The Database Life Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
Chapter 6: Database Design Using Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416
Chapter 7: Data and Process Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Chapter 8: Physical Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
Chapter 9: Connecting Databases to the Outside World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426
Chapter 10: Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
Chapter 11: Deploying Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
Chapter 12: Databases for Online Analytical Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Chapter 13: Integrating XML Documents and Objects into Databases . . . . . . . . . . . . . . . . 441
B
Solutions to the Try This Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Try This 5-1 Solution: Project Database Management Tasks . . . . . . . . . . . . . . . . . . . . . . . . . 448
Try This 6-1 Solution: UTLA Academic Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
Try This 6-2 Solution: Computer Books Company . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452