www.dbebooks.com - Free Books & magazines
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DATABASES DEMYSTIFIED
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
This page intentionally left blank.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DATABASES DEMYSTIFIED
ANDREW J. OPPEL
McGraw-Hill/Osborne
New York Chicago San Francisco Lisbon London
Madrid Mexico City Milan New Delhi San Juan
Seoul Singapore Sydney Toronto
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Copyright © 2004 by The McGraw-Hill Companies. All rights reserved. Manufactured in the United States of
America. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be

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 therefrom. McGraw-Hill has no responsibility for the content of any information accessed
through the work. Under no circumstances 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 contract, tort or otherwise.

DOI: 10.1036/0071469605
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
To everyone from whom I have learned so
much about so many things, including the
many teachers, students, and co-workers
I have had the pleasure of knowing.
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
ABOUT THE AUTHOR
Andrew J. (Andy) Oppel is a proud graduate of The Boys’ Latin School of Mary-
land and of Transylvania University (Lexington, KY) where he earned a BA in com-
puter 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, and data architect. In addition, he has
been a part-time instructor with the University of California (Berkeley) Extension
for over 20 years, and received the Honored Instructor Award for the year 2000. His
teaching work has included developing two courses for UC Extension, “Concepts of

CHAPTER 10 Database Security 247
CHAPTER 11 Database Implementation 273
CHAPTER 12 Databases for Online Analytical Processing 293
Final Exam 307
Answers to Quizzes and Final Exam 325
Index 329
vii
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
This page intentionally left blank.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
CONTENTS
Acknowledgments xvii
Introduction xix
CHAPTER 1 Database Fundamentals 1
Properties of a Database 1
The Database Management System (DBMS) 2
Layers of Data Abstraction 3
Physical Data Independence 5
Logical Data Independence 6
Prevalent Database Models 7
Flat Files 7
The Hierarchical Model 9

CHAPTER 3 Forms-Based Database Queries 51
QBE: The Roots of Forms-Based Queries 52
Getting Started in Microsoft Access 52
The Microsoft Access Relationships Panel 55
The Microsoft Access Table Design View 57
Creating Queries in Microsoft Access 59
Example 3-1: List All Customers 62
Example 3-2: Choosing Columns to Display 63
Example 3-3: Sorting Results 64
Example 3-4: Advanced Sorting 66
Example 3-5: Choosing Rows to Display 66
Example 3-6: Compound Row Selection 68
Example 3-7: Using Not Equal 70
Example 3-8: Joining Tables 70
Example 3-9: Limiting Join Results 72
Example 3-10: Outer Joins 75
Example 3-11: Multiple Joins;
Calculated Columns 77
Example 3-12: Aggregate Functions 80
Example 3-13: Self-Joins 82
Quiz 85
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Demystified / Databases Demystified / Oppel/ 225364-9 / FM
CHAPTER 4 Introduction to SQL 89
The History of SQL 90
Getting Started with Oracle SQL 91

P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xii
Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / FM
CHAPTER 5 The Database Life Cycle 129
The Traditional Method 130
Planning 130
Requirements Gathering 132
Conceptual Design 135
Logical Design 136
Physical Design 136
Construction 137
Implementation and Rollout 138
Ongoing Support 138
Nontraditional Methods 139
Prototyping 139
Rapid Application Development (RAD) 140
Quiz 141
CHAPTER 6 Logical Database Design Using
Normalization 145
The Need for Normalization 147
Insert Anomaly 148
Delete Anomaly 148
Update Anomaly 148
Applying the Normalization Process 148
Choosing a Primary Key 151

Designing Tables 204
Implementing Super Types and Subtypes 208
Naming Conventions 211
Integrating Business Rules and Data Integrity 214
NOT NULL Constraints 216
Primary Key Constraints 216
Referential (Foreign Key) Constraints 216
Unique Constraints 217
Check Constraints 218
Data Types, Precision, and Scale 218
Triggers 219
Designing Views 220
Adding Indexes for Performance 221
Quiz 222
CHAPTER 9 Connecting Databases to the Outside World 227
Deployment Models 228
Centralized Model 228
CONTENTS
xiii
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:24 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xiv
Databases Demystified
Demystified / Databases Demystified / Oppel/ 225364-9 / FM
Distributed Model 229
Client/Server Model 231
Connecting Databases to the Web 235

Color profile: Generic CMYK printer profile
Composite Default screen
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Transaction Management 276
What Is a Transaction? 276
DBMS Support for Transactions 276
Locking and Transaction Deadlock 278
Performance Tuning 283
Tuning Database Queries 284
Tuning DML Statements 286
Change Control 287
Quiz 288
CHAPTER 12 Databases for Online Analytical Processing 293
Data Warehouses 294
OLTP Systems Compared
with Data Warehouse Systems 295
Data Warehouse Architecture 296
Data Marts 301
Data Mining 302
Quiz 303
Final Exam 307
Answers to Quizzes and Final Exam 325
Chapter 1 325
Chapter 2 325
Chapter 3 326
Chapter 4 326
Chapter 5 326
Chapter 6 326
Chapter 7 326
Chapter 8 327

Tuesday, February 10, 2004 9:57:25 AM
Color profile: Generic CMYK printer profile
Composite Default screen
Copyright © 2004 by The McGraw-Hill Companies. Click here for terms of use.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
P:\010Comp\DeMYST\364-9\fm.vp
Tuesday, February 10, 2004 9:57:25 AM
Color profile: Generic CMYK printer profile
Composite Default screen
This page intentionally left blank.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INTRODUCTION
Thirty years ago, databases were found only in special research laboratories where
computer scientists struggled with ways to make them efficient and useful, and pub-
lished their findings in countless research papers. Today databases are a ubiquitous
part of the information technology (IT) industry and business in general. We directly
and indirectly use databases every day—banking transactions, travel reservations,
employment relationships, web site searches, purchases, and most other transac-
tions are recorded in and served by databases.
As with many fast-growing technologies, industry standards have lagged behind
the development of database technology, resulting in a myriad of commercial prod-
ucts, each following a particular software vendor’s vision. Moreover, a number of
different database models have emerged, with the relational model being the most
prevalent. Databases Demystified examines all of the major database models, in
-
cluding hierarchical, network, relational, object-oriented, and object-relational.
However, Databases Demystified concentrates heavily upon the relational and ob
-
ject-relational models because these are the mainstream of the IT industry and will
likely remain so in the foreseeable future.