Database Systems: Design, Implementation, and Management (with Bind-In Printed Access Card)

724 Pages·2009·22.68 MB·English

by  Carlos Coronel

A_C7046_FM.4c 10/23/09 4:28 PM Page i
DATABASE S YSTEMS
DESIGN, IMPLEMENTATION, AND MANAGEMENT
CARLOS CORONEL • STEVEN MORRIS • PETER ROB
Australia • Brazil • Japan • Korea • Mexico • Singapore • Spain • United Kingdom • United States
Copyright 2010 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part.
Database Systems: Design, Implementation, © 2011Cengage Learning
and Management, Ninth Edition ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be
Carlos Coronel, Steven Morris, and Peter Rob
reproduced, transmitted, stored or used in any form or by any means graphic, electronic,
or mechanical, including but not limited to photocopying, recording, scanning, digitiz-
Vice President of Editorial, Business: Jack W. Calhoun
ing, taping, Web distribution, information networks, or information storage and retrieval
Publisher: Joe Sabatino systems, except as permitted under Section 107or 108of the 1976United States
Senior Acquisitions Editor: Charles McCormick, Jr. Copyright Act, without the prior written permission of the publisher.
Senior Product Manager: Kate Mason
Development Editor: Deb Kaufmann For product information and technology assistance, contact us at
Cengage Learning Customer &Sales Support, 1-800-354-9706
Editorial Assistant: Nora Heink
For permission to use material from this text or product, submit all
Senior Marketing Communications Manager:
requests online at www.cengage.com/permissions
LibbyShipp
Further permissions questions can be emailed to
Marketing Coordinator: Suellen Ruttkay [email protected]
Content Product Manager: Matthew Hutchinson
Senior Art Director: Stacy Jenkins Shirley Library of Congress Control Number:2009936830
Cover Designer: Itzhack Shelomi
Student Edition Package
Cover Image: iStock Images
ISBN-13:978-0-538-46968-5
Media Editor: Chris Valentine
ISBN-10:0-538-46968-4
Manufacturing Coordinator: Julio Esperas
Copyeditor: Andrea Schein Student Edition (Book Only)
Proofreader: Foxxe Editorial ISBN-13:978-0-538-74884-1
Indexer: Elizabeth Cunningham ISBN-10:0-538-74884-2
Composition: GEX Publishing Services
Instructor Edition
ISBN-13: 978-0-538-46806-0
ISBN-10: 0-538-46806-8
Cengage Learning
20 Channel Center Street
Boston, MA 02210
USA
Cengage Learning is a leading provider of customized learning solutions with office loca-
tions around the globe, including Singapore, the United Kingdom, Australia, Mexico,
Brazil, and Japan. Locate your local office at:
international.cengage.com/region
Cengage Learning products are represented in Canada by Nelson Education, Ltd.
To learn more about Course Technology, visit www.cengage.com/coursetechnology
To learn more about Cengage Learning, visit www.cengage.com.
Purchase any of our products at your local college store or at our preferred online store
www.ichapters.com
Printed in the United States of America
1 2 3 4 5 6 7 17 16 15 14 13 12 11
D
D
E
edication
To the treasures in my life:To Victoria,for 20 wonderful years.Thank you for your unending
support, for being my angel, my sweetie and most importantly, my best friend.To Carlos
Anthony whose intelligence and wit is matched only by your good looks;you show us the way.
Thank you for your words of wisdom,contagious happiness and for bringing us shining days. D
You are still young;your best times are still to come.To Gabriela Victoria who is the image of
brilliance,beauty,and faithfulness.Thank you for being the sunshine in my cloudy days.To
Christian Javier whose endless energy and delightful smiles keep us going every day.Thank you
for being the youthful reminder of life’s simple beauties.To my parents,Sarah and Carlos,thank
you for your sacrifice and example.To all of you,you are all my inspiration.“TQTATA.”
I
Carlos Coronel
C
To Pamela,from high school sweetheart through 20 years of marriage,the beautiful love of
my life who has supported,encouraged,and inspired me.More than anyone else,you are
responsible for whatever successes I have achieved.To my son,Alexander Logan,whose
depth of character is without measure.You are my pride and joy.To my daughter,Lauren
Elizabeth,whose beauty and intensity take my breath away.You are my heart and soul.
A
Thank you all for the sacrifices that you have made that enabled me to pursue this dream.
I love you so much more than I can express.To my mother,Florence Maryann,and to the
memory of my father,Alton Lamar,who together instilled in me the desire to learn and the
passion to achieve.To my mother-in-law,Connie Duke,and to the memory of my father-in-
law,Wayne Duke,who taught me to find joy in all things.To all of you,with all my love, T
I dedicate this book.
Steven Morris
I
To Anne,who remains my best friend after 48 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
making 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 O
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 SoutheastAsia would
fill a history 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
N
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

BRIEF CONTENTS
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: Advanced Data Modeling
Chapter 6: Normalization of Database Tables
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
IV
BRIEF CONTENTS
The following appendixes and answers to selected questions and problems are included in the Premium Website for
this text, found at cengage.com/mis/coronel.
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
® ®
Appendix M: Microsoft Access Tutorial
Appendix N: Creating a New Database Using Oracle 11g
Answers to Selected Questions and Problems
V
TABLE OF CONTENTS
PART I DATABASE CONCEPTS
Business Vignette:The Relational Revolution 3
Chapter 1 Database Systems 4
1.1 Why Databases? 5
1.2 Data vs.Information 5
1.3 Introducing the Database 7
1.3.1 Role and Advantages of the DBMS 7
1.3.2 Types of Databases 9
1.4 Why Database Design is Important 10
1.5 Evolution of File System Data Processing 11
1.5.1 Manual File Systems 11
1.5.2 Computerized File Systems 11
1.5.3 File System Redux:Modern End-User Productivity Tools 14
1.6 Problems with File System Data Processing 14
1.6.1 Structural and Data Dependence 15
1.6.2 Data Redundancy 16
1.6.3 Lack of Design and Data-Modeling Skills 17
1.7 Database Systems 17
1.7.1 The Database System Environment 18
1.7.2 DBMS Functions 20
1.7.3 Managing the Database System: A Shift in Focus 23
Summary 25
Key Terms 25
Review Questions 26
Problems 26
Chapter 2 Data Models 29
2.1 Data Modeling and Data Models 30
2.2 The Importance of Data Models 30
2.3 Data Model Basic Building Blocks 31
2.4 Business Rules 32
2.4.1 Discovering Business Rules 33
2.4.2 Translating Business Rules into Data Model Components 33
2.4.3 Naming Conventions 34
2.5 The Evolution of Data Models 34
2.5.1 Hierarchical and Network Models 35
2.5.2 The Relational Model 36
2.5.3 The Entity Relationship Model 38
2.5.4 The Object-Oriented (OO) Model 40
2.5.5 Newer Data Models:Object/Relational and XML 42
2.5.6 The Future of Data Models 43
2.5.7 Data Models: A Summary 43
2.6 Degrees of Data Abstraction 46
2.6.1 The External Model 46
2.6.2 The Conceptual Model 48
2.6.3 The Internal Model 49
2.6.4 The Physical Model 49
VI
TABLE OF CONTENTS
Summary 51
Key Terms 51
Review Questions 52
Problems 53
PART II DESIGN CONCEPTS
Business Vignette:BP’s Data Modeling Initiative 61
Chapter 3 The Relational Database Model 58
3.1 A Logical View of Data 59
3.1.1 Tables and Their Characteristics 59
3.2 Keys 62
3.3 Integrity Rules 66
3.4 Relational Set Operators 68
3.5 The Data Dictionary and the System Catalog 74
3.6 Relationships within the Relational Database 76
3.6.1 The 1:M Relationship 76
3.6.2 The 1:1 Relationship 78
3.6.3 The M:N Relationship 78
3.7 Data Redundancy Revisited 84
3.8 Indexes 86
3.9 Codd’s Relational Database Rules 88
Summary 89
Key Terms 89
Review Questions 90
Problems 92
Chapter 4 Entity Relationship (ER) Modeling 99
4.1 The Entity Relationship Model (ERM) 100
4.1.1 Entities 100
4.1.2 Attributes 101
4.1.3 Relationships 105
4.1.4 Connectivity and Cardinality 107
4.1.5 Existence Dependence 108
4.1.6 Relationship Strength 108
4.1.7 Weak Entities 110
4.1.8 Relationship Participation 113
4.1.9 Relationship Degree 116
4.1.10 Recursive Relationships 117
4.1.11 Associative (Composite) Entities 121
4.2 Developing an ER Diagram 123
4.3 Database Design Challenges:Conflicting Goals 128
Summary 134
Key Terms 134
Review Questions 135
Problems 137
Cases 140
VII
TABLE OF CONTENTS
Chapter 5 Advanced Data Modeling 147
5.1 The Extended Entity Relationship Model 148
5.1.1 Entity Supertypes and Subtypes 148
5.1.2 Specialization Hierarchy 149
5.1.3 Inheritance 150
5.1.4 Subtype Discriminator 151
5.1.5 Disjoint and Overlapping Constraints 151
5.1.6 Completeness Constraint 153
5.1.7 Specialization and Generalization 154
5.2 Entity Clustering 154
5.3 Entity Integrity:Selecting Primary Keys 155
5.3.1 Natural Keys and Primary Keys 156
5.3.2 Primary Key Guidelines 156
5.3.3 When to Use Composite Primary Keys 157
5.3.4 When to Use Surrogate Primary Keys 158
5.4 Design Cases:Learning Flexible Database Design 159
5.4.1 Design Case #1:Implementing 1:1 Relationships 160
5.4.2 Design Case #2:Maintaining History of Time-Variant Data 161
5.4.3 Design Case #3:Fan Traps 162
5.4.4 Design Case #4:Redundant Relationships 164
Summary 165
Key Terms 165
Review Questions 166
Problems 167
Cases 168
Chapter 6 Normalization of Database Tables 174
6.1 Database Tables and Normalization 175
6.2 The Need for Normalization 175
6.3 The Normalization Process 179
6.3.1 Conversion to First Normal Form 181
6.3.2 Conversion to Second Normal Form 184
6.3.3 Conversion to Third Normal Form 185
6.4 Improving the Design 187
6.5 Surrogate Key Considerations 191
6.6 Higher-Level Normal Forms 192
6.6.1 The Boyce-Codd Normal Form (BCNF) 192
6.6.2 Fourth Normal Form (4NF) 196
6.7 Normalization and Database Design 197
6.8 Denormalization 200
6.9 Data-Modeling Checklist 204
Summary 206
Key Terms 206
Review Questions 207
Problems 208
VIII