Database Systems: Design, Implementation, and Management, Eighth Edition

1119 Pages·2010·53.59 MB·English

by  Peter Rob

Part I
Database Concepts

Chapter 1
Database Systems

Chapter 2
Data Models

Part II
Design Concepts

Chapter 3
The Relational Database Model

Chapter 4
Entity Relationship (ER) Modeling

Chapter 5
Normalization of Database Tables

Chapter 6
Advanced Data Modeling

Part III
Advanced Design and Implementation

Chapter 7
Introduction to Structured Query Language (SQL)

Chapter 8
Advanced SQL

Chapter 9
Database Design

Part IV
Advanced Database Concepts

Chapter 10
Transaction Management and Concurrency Control

Chapter 11
Database Performance Tuning and Query Optimization

Chapter 12
Distributed Database Management Systems

Chapter 13
Business Intelligence and Data Warehouses

Part V
Databases and the Internet

Chapter 14
Database Connectivity and Web Technologies

Part VI
Database Administration

Chapter 15
Database Administration and Security

Take a quick look at what’s ahead

Appendix
Title

A
Designing Databases with Visio Professional: A Tutorial

B
The University Lab: Conceptual Design

C
The University Lab: Conceptual Design Verifi cation, Logical Design, and Implementation

D
Converting the ER Model into a Database Structure

E
Comparison of EF Model Notations

F
Client/Server Systems

G
Object-Oriented Databases

H
Unifi ed Modeling Language

I
Databases in Electronic Commerce

J
Web Database Development with ColdFusion

K
The Hierarchical Database Model

L
The Network Database Model

Find These Useful
Appendices on the web site, located at

www.course.com/mis/dbs8

mis titles

Look for these other popular
course technology

View our entire collection of products online at www.course.com/mis.

A Guide to MySQL

by Philip J. Pratt and Mary Z. Last

ISBN: 978-1-4188-3635-1

Oracle 10g: SQL

by Joan Casteel

ISBN: 978-1-4188-3629-0

A Guide to SQL, Seventh Edition

by Philip J. Pratt

ISBN: 978-0-619-21674-0

Oracle 10g Developer:
PL/SQL Programming

by Joan Casteel

ISBN: 978-1-4239-0136-5

Problem-Solving Cases in
Microsoft Access and Excel,
Sixth Annual Edition

by Joseph Brady and Ellen Monk

ISBN: 978-1-4239-0213-3

Guide to Oracle 10g

by Joline Morrison, Mike Morrison,
and Rocky Conrad

ISBN: 978-0-619-21629-0

DATABASE SYSTEMS

PETER ROB • CARLOS CORONEL

DESIGN, IMPLEMENTATION, AND MANAGEMENT

Australia • Canada • Mexico • Singapore • Spain • United Kingdom • United States

Database Systems: Design, Implementation, and Management, Eighth Edition

by Peter Rob and Carlos Coronel

Product Manager:
Kate Hennessy

Developmental Editor:
Deb Kaufmann

Editorial Assistant:
Patrick Frank

Content Project Manager:
Jill Braiewa

Marketing Manager:
Bryant Chrzan

Marketing Specialist:
Vicki Ortiz

Compositor:
GEX Publishing Services

Print Buyer:
Justin Palmeiro

Copy Editor:
Mary Kemper

Proofreader:
Kathy Orrino

Indexer:
Michael Brackney

COPYRIGHT © 2009 Course
Technology

Printed in the United States

1 2 3 4 5 6 7 8 9 QWD 11 10 09 08

For more information, contact Course
Technology, 25 Thomson Place,
Boston, Massachusetts, 02210.

Or find us on the World Wide Web at:
www.course.com

ALL RIGHTS RESERVED. No part
of this work covered by the copyright
hereon may be reproduced or used in
any form or by any means—graphic,
electronic, or mechanical, including
photocopying, recording, taping, Web
distribution, or information storage
and retrieval systems—without the
written permission of the publisher.

For permission to use material from
this text or product, contact us by
Tel [phone]
Fax [phone]
www.thomsonrights.com

Disclaimer
Course Technology reserves the right
to revise this publication and make
changes from time to time in its
content without notice.

ISBN-13: 978-1-4239-0201-0
ISBN-10: 1-4239-0201-7

Dedication
To Anne, who remains my best friend after 46 years of marriage.To our son, Peter William,

who turned out to be the man we hoped he would be and who proved his wisdom by mak-

ing Sheena our treasured daughter-in-law.To Sheena, who stole our hearts so many years

ago.To our grandsons,Adam Lee and Alan Henri, who are growing up to be the fine human

beings their parents are.To my mother-in-law,Nini Fontein,and to the memory of my father-

in-law, Henri Fontein—their life experiences in Europe and Southeast Asia would fill a his-

tory book and they enriched my life by entrusting me with their daughter’s future.To the

memory of my parents, Hendrik and Hermine Rob, who rebuilt their lives after World War

II’s horrors,who did it again after a failed insurgency in Indonesia,and who finally found their

promised land in these United States.And to the memory of Heinz, who taught me daily

lessons about loyalty, uncritical acceptance, and boundless understanding. I dedicate this

book to you, with love.

Peter Rob

To Victoria, my beautiful and wonderful wife of 18 years, who does the hard work of keep-

ing up with me and is a living example of beauty and sweetness.Thanks for being so caring.

To Carlos Anthony, my son, who is his father’s pride, always teaching me new things and

growing up to be an intelligent and talented gentleman.To Gabriela Victoria, my daughter

and the princess of the house,who is growing like a rose and becoming a gracious and beau-

tiful angel.To Christian Javier, our little bundle of joy, who is learning and growing, always

with so much energy and happiness and is like his father in more ways than one.To all my

children,thanks for your laughs,your sweet voices,beautiful smiles,and frequent hugs.I love

you; you are my Divine treasure.To my parents for their sacrifice and encouragement.And

to Smokey, the laziest one in the family, with no cares, no worries, and with all the time in

the world.To all, I dedicate the fruits of many long days and nights.Thanks for your support

and understanding.

Carlos Coronel

D E D I C A T I O N

BRIEF CONTENTS

IV

PART I:
Database Concepts

Chapter 1: Database Systems

Chapter 2: Data Models

PART II:
Design Concepts

Chapter 3: The Relational Database Model

Chapter 4: Entity Relationship (ER) Modeling

Chapter 5: Normalization of Database Tables

Chapter 6: Advanced Data Modeling

PART III:
Advanced Design and Implementation

Chapter 7: Introduction to Structured Query Language (SQL)

Chapter 8: Advanced SQL

Chapter 9: Database Design

PART IV:
Advanced Database Concepts

Chapter 10: Transaction Management and Concurrency Control

Chapter 11: Database Performance Tuning and Query Optimization

Chapter 12: Distributed Database Management Systems

Chapter 13: Business Intelligence and Data Warehouses

PART V:
Databases and the Internet

Chapter 14: Database Connectivity and Web Technologies

PART vi:
Database Administration

Chapter 15: Database Administration and Security

GLOSSARY

INDEX

BRIEF CONTENTS

V

The following appendixes and answers to selected questions and problems are included in the Student Online
Companion for this text, found at oc.course.com/mis/dbs8. CoursePort registration and login are required, using
the keycode provided with this book.

Appendix A:
Designing Databases with Visio Professional: A Tutorial

Appendix B:
The University Lab: Conceptual Design

Appendix C:
The University Lab: Conceptual Design Verification, Logical
Design, and Implementation

Appendix D:
Converting an ER Model into a Database Structure

Appendix E:
Comparison of ER Model Notations

Appendix F:
Client/Server Systems

Appendix G:
Object-Oriented Databases

Appendix H:
Unified Modeling Language (UML)

Appendix I:
Databases in Electronic Commerce

Appendix J:
Web Database Development with ColdFusion

Appendix K:
The Hierarchical Database Model

Appendix L:
The Network Database Model

Answers to Selected Questions and Problems

TABLE OF CONTENTS

VI

PART I DATABASE CONCEPTS

Business Vignette: The Relational Revolution
3

Chapter 1 Database Systems
4

1.1 Data vs. Information
5
1.2 Introducing the Database and the DBMS
6
1.2.1
Role and Advantages of the DBMS
7
1.2.2
Types of Databases
8
1.3 Why Database Design Is Important
10
1.4 Historical Roots: Files and File Systems
10
1.5 Problems with File System Data Management
14
1.5.1
Structural and Data Dependence
15
1.5.2
Field Definitions and Naming Conventions
15
1.5.3
Data Redundancy
17
1.6 Database Systems
18
1.6.1
The Database System Environment
19
1.6.2
DBMS Functions
21
1.6.3
Managing the Database System:A Shift in Focus
25
Summary
26
Key Terms
26
Review Questions
27
Problems
27

Chapter 2 Data Models
30

2.1 Data Modeling and Data Models
31
2.2 The Importance of Data Models
31
2.3 Data Model Basic Building Blocks
32
2.4 Business Rules
33
2.4.1
Discovering Business Rules
34
2.4.2
Translating Business Rules into Data Model Components
34
2.5 The Evolution Of Data Models
35
2.5.1
The Hierarchical Model
36
2.5.2
The Network Model
37
2.5.3
The Relational Model
38
2.5.4
The Entity Relationship Model
40
2.5.5
The Object-Oriented (OO) Model
43
2.5.6
The Convergence of Data Models
44
2.5.7
Database Models and the Internet
45
2.5.8
Data Models:A Summary
45
2.6 Degrees of Data Abstraction
48
2.6.1
The External Model
48
2.6.2
The Conceptual Model
50
2.6.3
The Internal Model
51
2.6.4
The Physical Model
52
Summary
53
Key Terms
54
Review Questions
54
Problems
55

TABLE OF CONTENTS

VII

PART II DESIGN CONCEPTS

Business Vignette: Database Modeling Supporting Communities
61

Chapter 3 The Relational Database Model
62

3.1 A Logical View of Data
63
3.1.1
Tables and Their Characteristics
63
3.2 Keys
66
3.3 Integrity Rules
71
3.4 Relational Set Operators
72
3.5 The Data Dictionary and the System Catalog
78
3.6 Relationships within the Relational Database
80
3.6.1
The 1:M Relationship
80
3.6.2
The 1:1 Relationship
82
3.6.3
The M:N Relationship
84
3.7 Data Redundancy Revisited
88
3.8 Indexes
90
3.9 Codd’s Relational Database Rules
91
Summary
93
Key Terms
93
Review Questions
94
Problems
96

Chapter 4 Entity Relationship (ER) Modeling
104

4.1 The Entity Relationship Model (ERM)
105
4.1.1
Entities
105
4.1.2
Attributes
105
4.1.3
Relationships
111
4.1.4
Connectivity and Cardinality
111
4.1.5
Existence Dependence
113
4.1.6
Relationship Strength
113
4.1.7
Weak Entities
116
4.1.8
Relationship Participation
118
4.1.9
Relationship Degree
120
4.1.10
Recursive Relationships
122
4.1.11
Associative (Composite) Entities
125
4.2 Developing an ER Diagram
127
4.3 Database Design Challenges: Conflicting Goals
135
Summary
139
Key Terms
139
Review Questions
140
Problems
141

TABLE OF CONTENTS

VIII

Chapter 5 Normalization of Database Tables
152

5.1 Database Tables and Normalization
153
5.2 The Need for Normalization
153
5.3 The Normalization Process
157
5.3.1
Conversion to First Normal Form
158
5.3.2
Conversion to Second Normal Form
161
5.3.3
Conversion to Third Normal Form
163
5.4 Improving the Design
164
5.5 Surrogate Key Considerations
168
5.6 Higher-Level Normal Forms
169
5.6.1
The Boyce-Codd Normal Form (BCNF)
170
5.6.2
Fourth Normal Form (4NF)
173
5.7 Normalization and Database Design
174
5.8 Denormalization
178
Summary
182
Key Terms
184
Review Questions
184
Problems
185

Chapter 6 ADVANCED DATA MODELING
193

6.1 The Extended Entity Relationship Model
194
6.1.1
Entity Supertypes and Subtypes
194
6.1.2
Specialization Hierarchy
195
6.1.3
Inheritance
196
6.1.4
Subtype Discriminator
197
6.1.5
Disjoint and Overlapping Constraints
197
6.1.6
Completeness Constraint
199
6.1.7
Specialization and Generalization
199
6.2 Entity Clustering
200
6.3 Entity Integrity: Selecting Primary Keys
201
6.3.1
Natural Keys and Primary Keys
202
6.3.2
Primary Key Guidelines
202
6.3.3
When to Use Composite Primary Keys
203
6.3.4
When to Use Surrogate Primary Keys
204
6.4 Design Cases: Learning Flexible Database Design
206
6.4.1
Design Case #1: Implementing 1:1 Relationships
206
6.4.2
Design Case #2: Maintaining History of Time-Variant Data
207
6.4.3
Design Case #3: Fan Traps
209
6.5.4
Design Case #4: Redundant Relationships
210
6.5 Data Modeling Checklist
211
Summary
213
Key Terms
213
Review Questions
214
Problems
214

TABLE OF CONTENTS

IX

PART III ADVANCED DESIGN AND IMPLEMENTATION

Business Vignette: Using Queries to Score Runs
223

Chapter 7 Introduction to Structured Query Language (SQL)
224

7.1 Introduction to SQL
225
7.2 Data Definition Commands
226
7.2.1
The Database Model
227
7.2.2
Creating the Database
229
7.2.3
The Database Schema
229
7.2.4
Data Types
230
7.2.5
Creating Table Structures
232
7.2.6
SQL Constraints
235
7.2.7
SQL Indexes
239
7.3 Data Manipulation Commands
240
7.3.1
Adding Table Rows
240
7.3.2
Saving Table Changes
242
7.3.3
Listing Table Rows
242
7.3.4
Updating Table Rows
244
7.3.5
Restoring Table Contents
244
7.3.6
Deleting Table Rows
245
7.3.7
Inserting Table Rows with a Select Subquery
245
7.4 SELECT Queries
247
7.4.1
Selecting Rows with Conditional Restrictions
247
7.4.2
Arithmetic Operators: The Rule of Precedence
251
7.4.3
Logical Operators: AND, OR, and NOT
251
7.4.4
Special Operators
253
7.5 Advanced Data Definition Commands
257
7.5.1
Changing a Column’s Data Type
257
7.5.2
Changing a Column’s Data Characteristics
257
7.5.3
Adding a Column
258
7.5.4
Dropping a Column
258
7.5.5
Advanced Data Updates
259
7.5.6
Copying Parts of Tables
261
7.5.7
Adding Primary and Foreign Key Designations
262
7.5.8
Deleting a Table from the Database
263
7.6 Advanced SELECT Queries
263
7.6.1
Ordering a Listing
263
7.6.2
Listing Unique Values
265
7.6.3
Aggregate Functions
265
7.6.4
Grouping Data
270
7.7 Virtual Tables: Creating a View
273
7.8 Joining Database Tables
274
7.8.1
Joining Tables with an Alias
277
7.8.2
Recursive Joins
277
7.8.3
Outer Joins
278
Summary
280
Key Terms
281
Review Questions
281
Problems
285

TABLE OF CONTENTS

X

Chapter 8 Advanced SQL
297

8.1 Relational Set Operators
298
8.1.1
UNION
299
8.1.2
UNION ALL
300
8.1.3
INTERSECT
301
8.1.4
MINUS
301
8.1.5
Syntax Alternatives
303
8.2 SQL Join Operators
304
8.2.1
Cross Join
306
8.2.2
Natural Join
307
8.2.3
Join USING Clause
308
8.2.4
JOIN ON Clause
309
8.2.5
Outer Joins
310
8.3 Subqueries and Correlated Queries
313
8.3.1
WHERE Subqueries
314
8.3.2
IN Subqueries
315
8.3.3
HAVING Subqueries
316
8.3.4
Multirow Subquery Operators: ANY and ALL
317
8.3.5
FROM Subqueries
318
8.3.6
Attribute List Subqueries
319
8.3.7
Correlated Subqueries
321
8.4 SQL Functions
324
8.4.1
Date and Time Functions
325
8.4.2
Numeric Functions
327
8.4.3
String Functions
328
8.4.4
Conversion Functions
330
8.5 Oracle Sequences
331
8.6 Updatable Views
335
8.7 Procedural SQL
338
8.7.1
Triggers
342
8.7.2
Stored Procedures
351
8.7.3
PL/SQL Processing with Cursors
357
8.7.4
PL/SQL Stored Functions
359
8.8 Embedded SQL
359
Summary
365
Key Terms
366
Review Questions
366
Problems
367

Chapter 9 Database Design
372

9.1 The Information System
373
9.2 The Systems Development Life Cycle (SDLC)
375
9.2.1
Planning
376
9.2.2
Analysis
376
9.2.3
Detailed Systems Design
377
9.2.4
Implementation
377
9.2.5
Maintenance
378

TABLE OF CONTENTS

XI

9.3 The Database Life Cycle (DBLC)
378
9.3.1
The Database Initial Study
379
9.3.2
Database Design
383
9.3.3
Implementation and Loading
397
9.3.4
Testing and Evaluation
401
9.3.5
Operation
401
9.3.6
Maintenance and Evolution
401
9.4 Database Design Strategies
402
9.5 Centralized vs. Decentralized Design
403
Summary
407
Key Terms
407
Review Questions
407
Problems
408

PART IV ADVANCED DATABASE CONCEPTS

Business Vignette: JetBlue’s Database Crisis
411

Chapter 10 Transaction Management and Concurrency Control
412

10.1 What Is a Transaction?
413
10.1.1
Evaluating Transaction Results
414
10.1.2
Transaction Properties
417
10.1.3
Transaction Management with SQL
418
10.1.4
The Transaction Log
418
10.2 Concurrency Control
420
10.2.1
Lost Updates
420
10.2.2
Uncommitted Data
421
10.2.3
Inconsistent Retrievals
422
10.2.4
The Scheduler
423
10.3 Concurrency Control with Locking Methods
424
10.3.1
Lock Granularity
425
10.3.2
Lock Types
428
10.3.3
Two-Phase Locking to Ensure Serializability
429
10.3.4
Deadlocks
430
10.4 Concurrency Control with Time Stamping Methods
431
10.4.1
Wait/Die and Wound/Wait Schemes
432
10.5 Concurrency Control with Optimistic Methods
433
10.6 Database Recovery Management
433
10.6.1
Transaction Recovery
434
Summary
438
Key Terms
439
Review Questions
439
Problems
440

TABLE OF CONTENTS

XII

chapter 11 DATABASE PERFORMANCE TUNING AND
QUERY OPTIMIZATION
442

11.1 Database Performance-Tuning Concepts
443
11.1.1
Performance Tuning: Client and Server
444
11.1.2
DBMS Architecture
444
11.1.3
Database Statistics
446
11.2 Query Processing
448
11.2.1
SQL Parsing Phase
449
11.2.2
SQL Execution Phase
450
11.2.3
SQL Fetching Phase
450
11.2.4
Query Processing Bottlenecks
450
11.3 Indexes and Query Optimization
451
11.4 Optimizer Choices
453
11.4.1
Using Hints to Affect Optimizer Choices
455
11.5 SQL Performance Tuning
456
11.5.1
Index Selectivity
456
11.5.2
Conditional Expressions
457
11.6 Query Formulation
459
11.7 DBMS Performance Tuning
460
11.8 Query Optimization Example
462
Summary
471
Key Terms
472
Review Questions
472
Problems
473

chapter 12 Distributed Database Management Systems
477

12.1 The Evolution of Distributed Database Management Systems
478
12.2 DDBMS Advantages and Disadvantages
480
12.3 Distributed Processing and Distributed Databases
481
12.4 Characteristics of Distributed Database Management Systems
483
12.5 DDBMS Components
484
12.6 Levels of Data and Process Distribution
485
12.6.1
Single-Site Processing, Single-Site Data (SPSD)
486
12.6.2
Multiple-Site Processing, Single-Site Data (MPSD)
487
12.6.3
Multiple-Site Processing, Multiple-Site Data (MPMD)
488
12.7 Distributed Database Transparency Features
489
12.8 Distribution Transparency
490
12.9 Transaction Transparency
492
12.9.1
Distributed Requests and Distributed Transactions
492
12.9.2
Distributed Concurrency Control
496
12.9.3
Two-Phase Commit Protocol
496
12.10 Performance Transparency and Query Optimization
497
12.11 Distributed Database Design
499
12.11.1
Data Fragmentation
499