Guide to Oracle 10g

1119 Pages·2006·53.591 MB·English

by  Rocky Conrad, Mike Morrison, Joline Morrison

what’s ahead
Take a quick look at
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
Find These Useful
Appendices
on the web site, located at
www.course.com/mis/dbs8
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
Look for these other popular
course technology
mis titles
Guide to Oracle 10g Oracle 10g: SQL Oracle 10g Developer:
PL/SQL Programming
by Joline Morrison, Mike Morrison, by Joan Casteel
and Rocky Conrad ISBN: 978-1-4188-3629-0 by Joan Casteel
ISBN: 978-0-619-21629-0 ISBN: 978-1-4239-0136-5
A Guide to MySQL A Guide to SQL, Seventh Edition Problem-Solving Cases in
Microsoft Access and Excel,
by Philip J. Pratt and Mary Z. Last by Philip J. Pratt
Sixth Annual Edition
ISBN: 978-1-4188-3635-1 ISBN: 978-0-619-21674-0
by Joseph Brady and Ellen Monk
ISBN: 978-1-4239-0213-3
View our entire collection of products online at www.course.com/mis.
DATABASE S YSTEMS
DESIGN, IMPLEMENTATION, AND MANAGEMENT
PETER ROB • CARLOS CORONEL
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: Marketing Manager: Copy Editor:
Kate Hennessy Bryant Chrzan Mary Kemper
Developmental Editor: Marketing Specialist: Proofreader:
Deb Kaufmann Vicki Ortiz Kathy Orrino
Editorial Assistant: Compositor: Indexer:
Patrick Frank GEX Publishing Services Michael Brackney
Content Project Manager: Print Buyer:
Jill Braiewa Justin Palmeiro
COPYRIGHT © 2009 Course ALL RIGHTS RESERVED. No part Disclaimer
Technology of this work covered by the copyright Course Technology reserves the right
hereon may be reproduced or used in to revise this publication and make
Printed in the United States any form or by any means—graphic, changes from time to time in its
electronic, or mechanical, including content without notice.
1 2 3 4 5 6 7 8 9 QWD 11 10 09 08 photocopying, recording, taping, Web
distribution, or information storage ISBN-13: 978-1-4239-0201-0
For more information, contact Course and retrieval systems—without the ISBN-10: 1-4239-0201-7
Technology, 25 Thomson Place,
written permission of the publisher.
Boston, Massachusetts, 02210.
For permission to use material from
Or find us on the World Wide Web at:
this text or product, contact us by
www.course.com
Tel [phone]
Fax[phone]
www.thomsonrights.com
D
D
E
edication
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
D
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
I
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
C
lessons about loyalty,uncritical acceptance,and boundless understanding.I dedicate this
book to you,with love.
Peter Rob
A
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
T
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
I
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
O
the world.To all,I dedicate the fruits of many long days and nights.Thanks for your support
and understanding.
Carlos Coronel
N
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: 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
IV
BRIEF CONTENTS
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
V
TABLE OF CONTENTS
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
VI
TABLE OF CONTENTS
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
VII