Databases: A Beginner's Guide

497 Pages·2009·15.798 MB·English

by  Andy Oppel

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 [email protected].
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
for Computing Services at Ball State University in Muncie, Indiana. He lives in central
Indiana with his wife, Kimberly, and their four incredible children. Contact Todd at
[email protected].
Databases:
A Beginner’s Guide
Andrew J. Oppel
New York Chicago San Francisco
Lisbon London Madrid Mexico City
Milan New Delhi San Juan
Seoul Singapore Sydney Toronto
Copyright © 2009 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States
Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in
a database or retrieval system, without the prior written permission of the publisher.
ISBN: 978-0-07-160847-3
MHID: 0-07-160847-8
The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-160846-6, MHID: 0-07-160846-X.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a
trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of
infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in cor-
porate training programs. To contact a representative please visit the Contact Us page at www.mhprofessional.com.
Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of
human or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy,
or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of
such information.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and
to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to
store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create
derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without
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.
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
The Hierarchical Model ....................................................... 13
The Network Model ........................................................... 15
The Relational Model ......................................................... 17
The Object-Oriented Model ................................................... 19
The Object-Relational Model .................................................. 21
A Brief History of Databases ........................................................ 22
Why Focus on Relational? .......................................................... 24
2 Exploring Relational Database Components .............................. 29
Conceptual Database Design Components ........................................... 30
Entities ....................................................................... 30
v
vi Databases: A Beginner’s Guide
Attributes ..................................................................... 32
Relationships ................................................................. 32
Business Rules ................................................................ 38
Try This 2-1: Exploring the Northwind Database .................................... 38
Logical/Physical Database Design Components ..................................... 42
Tables ......................................................................... 42
Columns and Data Types ...................................................... 44
Constraints .................................................................... 46
Integrity Constraints .......................................................... 53
Views ......................................................................... 56
3 Forms-based Database Queries ........................................... 63
QBE: The Roots of Forms-based Queries ............................................ 64
Getting Started in Microsoft Access ................................................. 65
The Microsoft Access Relationships Panel ........................................... 73
The Microsoft Access Table Design View ........................................... 75
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
Contents vii
Joining Tables ................................................................. 143
Aggregate Functions .......................................................... 150
Data Manipulation Language (DML) ................................................ 154
Transaction Support (COMMIT and ROLLBACK) ............................ 154
The INSERT Statement ....................................................... 155
The UPDATE Statement ...................................................... 157
The DELETE Statement ...................................................... 158
Data Definition Language (DDL) Statements ........................................ 159
The CREATE TABLE Statement .............................................. 160
The ALTER TABLE Statement ................................................ 161
The CREATE VIEW Statement ............................................... 162
The CREATE INDEX Statement .............................................. 163
The DROP Statement ......................................................... 163
Data Control Language (DCL) Statements ........................................... 164
The GRANT Statement ....................................................... 164
The REVOKE Statement ...................................................... 165
PART II Database Development
5 The Database Life Cycle .................................................. 171
The Traditional Life Cycle .......................................................... 172
Planning ...................................................................... 174
Requirements Gathering ...................................................... 175
Conceptual Design ............................................................ 178
Logical Design ................................................................ 178
Physical Design ............................................................... 179
Construction .................................................................. 179
Implementation and Rollout ................................................... 180
Ongoing Support .............................................................. 181
Nontraditional Life Cycles .......................................................... 182
Prototyping ................................................................... 182
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
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
Process Models ..................................................................... 236
The Flowchart ................................................................ 236
The Function Hierarchy Diagram .............................................. 239
The Swim Lane Diagram ...................................................... 240
The Data Flow Diagram ....................................................... 240
Relating Entities and Processes ...................................................... 245
Try This 7-1: Draw an ERD in Information Engineering (IE) Format ................. 246
8 Physical Database Design ................................................. 253
Designing Tables .................................................................... 254
Implementing Super Types and Subtypes ...................................... 259
Naming Conventions .......................................................... 262
Integrating Business Rules and Data Integrity ....................................... 265
NOT NULL Constraints ....................................................... 267
Primary Key Constraints ...................................................... 267
Referential (Foreign Key) Constraints ......................................... 268
Unique Constraints ............................................................ 269
Check Constraints ............................................................. 270
Data Types, Precision, and Scale .............................................. 270
Triggers ....................................................................... 270
Designing Views .................................................................... 271
Adding Indexes for Performance .................................................... 272
Try This 8-1: Mapping a Logical Model to a Physical Database Design .............. 274
PART III Database Implementation
9 Connecting Databases to the Outside World .............................. 281
Deployment Models ................................................................. 282
Centralized Model ............................................................ 282
Distributed Model ............................................................. 284
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
Contents ix
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
11 Deploying Databases ...................................................... 329
Cursor Processing ................................................................... 330
Transaction Management ............................................................ 332
What Is a Transaction? ........................................................ 332
DBMS Support for Transactions ............................................... 333
Try This 11-1: SQL Transaction Support ............................................. 335
Locking and Transaction Deadlock ............................................ 337
Performance Tuning ................................................................. 342
Tuning Database Queries ...................................................... 342
Tuning DML Statements ...................................................... 345
Change Control ..................................................................... 346
12 Databases for Online Analytical Processing ............................... 353
Data Warehouses .................................................................... 355
OLTP Systems Compared with Data Warehouse Systems ...................... 356
Data Warehouse Architecture .................................................. 356
Data Marts .......................................................................... 363
Data Mining ........................................................................ 364
Try This 12-1: Design Star Schema Fact and Dimension Tables ...................... 365