Database Technologies Question

OTHER MIS TITLES OF INTEREST
Introductory MIS:
Systems Analysis and Design:
Managing Information Technology, 7/e
Brown, DeHayes, Hoffer, Martin & Perkins ?2012
Modern Systems Analysis and Design, 7/e
Hoffer, George & Valacich ?2014
Experiencing MIS, 6/e
Kroenke & Boyle ?2016
Systems Analysis and Design, 9/e
Kendall & Kendall ?2014
Using MIS, 8/e
Kroenke & Boyle ?2016
Essentials of Systems Analysis and
Design, 6/e
Valacich, George & Hoffer ?2015
MIS Essentials, 4/e
Kroenke ?2015
Management Information Systems, 14/e
Laudon & Laudon ?2016
Essentials of Management Information
Systems, 11/e
Laudon & Laudon ?2015
IT Strategy, 3/e
McKeen & Smith ?2015
Processes, Systems, and Information: An
Introduction to MIS, 2/e
McKinney & Kroenke ?2015
Information Systems Today, 7/e
Valacich & Schneider ?2016
Introduction to Information Systems, 2/e
Wallace ?2015
Decision Support Systems:
Business Intelligence, 3/e
Sharda, Delen & Turban ?2014
Decision Support and Business Intelligence
Systems, 10/e
Sharda, Delen & Turban ?2014
Data Communications & Networking:
Applied Networking Labs, 2/e
Boyle ?2014
Digital Business Networks
Dooley ?2014
Business Data Networks and Security, 10/e
Panko & Panko ?2015
Database:
Hands-on Database, 2/e
Conger ?2014
Modern Database Management, 12/e
Hoffer, Ramesh & Topi ?2016
Database Systems: Introduction to Databases
and Data Warehouses
Jukic, Vrbsky & Nestorov ?2014
Essentials of Database Management
Hoffer, Topi & Ramesh ?2014
Database Concepts, 7/e
Kroenke & Auer ?2015
Database Processing, 14/e
Kroenke & Auer ?2016
Electronic Commerce:
E-Commerce: Business, Technology,
Society, 11/e
Laudon & Traver ?2015
Enterprise Resource Planning:
Enterprise Systems for Management, 2/e
Motiwalla & Thompson ?2012
Project Management:
Project Management: Process, Technology
and Practice
Vaidyanathan ?2013
This page intentionally left blank
Twelfth Edition
Modern database ManageMent
This page intentionally left blank
Twelfth Edition
Modern database ManageMent
Jeffrey A. Hoffer
University of Dayton
V. Ramesh
Indiana University
Heikki Topi
Bentley University
Boston Columbus Indianapolis New York San Francisco Hoboken
Amsterdam Cape Town Dubai London Madrid Milan Munich Paris Montr?al Toronto
Delhi Mexico City S?o Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo
Vice President, Business Publishing: Donna Battista
Editor-in-Chief: Stephanie Wall
Acquisitions Editor: Nicole Sam
Program Manager Team Lead: Ashley Santora
Program Manager: Denise Weiss
Editorial Assistant: Olivia Vignone
Vice President, Product Marketing: Maggie Moylan
Director of Marketing, Digital Services and Products:
Jeanette Koskinas
Field Marketing Manager: Lenny Ann Raper
Senior Strategic Marketing Manager: Erin Gardner
Product Marketing Assistant: Jessica Quazza
Project Manager Team Lead: Jeff Holcomb
Project Manager: Ilene Kahn
Operations Specialist: Diane Peirano
Creative Director: Blair Brown
Senior Art Director: Janet Slowik
Interior and Cover Designer: Emily/Integra Software Solutions
Cover Image: Dreaming Andy/Fotolia
Vice President, Director of Digital Strategy & Assessment:
Paul Gentile
Manager of Learning Applications: Paul Deluca
Digital Editor: Brian Surette
Digital Studio Manager: Diane Lombardo
Digital Studio Project Manager: Robin Lazrus
Digital Studio Project Manager: Alana Coles
Digital Studio Project Manager: Monique Lawrence
Digital Studio Project Manager: Regina DaSilva
Full-Service Project Management and Composition:
George Jacob/Integra Software Solutions Pvt., Ltd
Printer/Binder: Edwards Brothers
Cover Printer: Phoenix Color
Text Font: 10/12 PalatinoLTStd Roman
Credits and acknowledgments borrowed from other sources and reproduced, with permission, in this textbook appear on the appropriate
page within text.
Microsoft and/or its respective suppliers make no representations about the suitability of the information contained in the documents and
related graphics published as part of the services for any purpose. All such documents and related graphics are provided ?as is? without
warranty of any kind. Microsoft and/or its respective suppliers hereby disclaim all warranties and conditions with regard to this information,
including all warranties and conditions of merchantability, whether express, implied or statutory, fitness for a particular purpose, title and
non-infringement. In no event shall Microsoft and/or its respective suppliers be liable for any special, indirect or consequential damages
or any damages whatsoever resulting from loss of use, data or profits, whether in an action of contract, negligence or other tortious action,
arising out of or in connection with the use or performance of information available from the services.
The documents and related graphics contained herein could include technical inaccuracies or typographical errors. Changes are periodically
added to the information herein. Microsoft and/or its respective suppliers may make improvements and/or changes in the product(s)
and/or the program(s) described herein at any time. Partial screen shots may be viewed in full within the software version specified.
Trademarks
Microsoft? Windows?, and Microsoft Office? are registered trademarks of the Microsoft Corporation in the U.S.A. and other countries.
This book is not sponsored or endorsed by or affiliated with the Microsoft Corporation.
Copyright ? 2016, 2013, 2011 by Pearson Education, Inc. All rights reserved. Manufactured in the United States of America. This publication
is protected by Copyright, and permission should be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval
system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding
permissions, request forms and the appropriate contacts within the Pearson Education Global Rights & Permissions department, please visit
www.pearsoned.com/permissions/.
Acknowledgements of third party content appear on the appropriate page within the text, which constitutes an extension of this copyright page.
Unless otherwise indicated herein, any third-party trademarks that may appear in this work are the property of their respective owners and
any references to third-party trademarks, logos or other trade dress are for demonstrative or descriptive purposes only. Such references are
not intended to imply any sponsorship, endorsement, authorization, or promotion of Pearson’s products by the owners of such marks, or any
relationship between the owner and Pearson Education, Inc. or its affiliates, authors, licensees or distributors.
Library of Congress Cataloging-in-Publication Data
Hoffer, Jeffrey A.
Modern database management/Jeffrey A. Hoffer, University of Dayton,
V. Ramesh, Indiana University, Heikki Topi, Bentley University. ?Twelth edition.
pages cm
ISBN 978-0-13-354461-9 ? ISBN 0-13-354461-3
1. Database management. I. Ramesh, V. (Venkataraman) II. Topi, Heikki. III. Title.
QA76.9.D3M395 2015
005.74?dc23
2015003732
10 9 8 7 6 5 4 3 2 1
ISBN 10: 0-13-354461-3
ISBN 13: 978-0-13-354461-9
To Patty, for her sacrifices, encouragement, and support for more than 30 years
of being a textbook author widow. To my students and colleagues, for being
receptive and critical and for challenging me to be a better teacher.
?J.A.H.
To Gayathri, for her sacrifices and patience these past 25 years. To my parents, for
letting me make the journey abroad, and to my cat, Raju, who was a part of our
family for more than 20 years.
?V.R.
To Anne-Louise, for her loving support, encouragement, and patience.
To Leila and Saara, whose laughter and joy of life continue to teach me about
what is truly important. To my teachers, colleagues, and students, from whom
I continue to learn every day.
?H.T.
This page intentionally left blank
Brief Contents
Part I
The Context of Database Management
Chapter 1
The Database Environment and Development Process
Part II Database Analysis
Chapter 2
Chapter 3
Chapter 6
Chapter 7
Chapter 8
Chapter 9
53
153
Logical Database Design and the Relational Model
Physical Database Design and Performance 206
Part IV Implementation
2
51
Modeling Data in the Organization
The Enhanced E-R Model 114
Part III Database Design
Chapter 4
Chapter 5
1
155
241
Introduction to SQL 243
Advanced SQL 289
Database Application Development
Data Warehousing 374
Part V Advanced Database Topics
337
417
Chapter 10 Data Quality and Integration 419
Chapter 11 Big Data and Analytics 445
Chapter 12 Data and Database Administration 485
Glossary of Acronyms 534
Glossary of Terms 536
Index 544
Available Online at www.pearsonhighered.com/hoffer
Chapter 13 Distributed Databases 13-1
Chapter 14 Object-Oriented Data Modeling
14-1
Appendices
Appendix A Data Modeling Tools and Notation
Appendix B Advanced Normal Forms B-1
Appendix C Data Structures C-1
A-1
vii
This page intentionally left blank
Contents
Preface
Part I
xxv
The Context of Database Management
1
An Overview of Part One 1
Chapter 1 the Database environment and Development Process
2
Learning Objectives 2
Data Matter! 2
Introduction 3
Basic Concepts and Definitions 5
Data 5
Data Versus Information 5
Metadata 6
Traditional File Processing Systems 7
File Processing Systems at Pine Valley Furniture Company 8
Disadvantages of File Processing Systems 8
Program-Data DePenDence 8
DuPlication of Data 9
limiteD Data Sharing 9
lengthy DeveloPment timeS 9
exceSSive Program maintenance 9
The Database Approach 9
Data Models 9
entitieS 10
relationShiPS 11
Relational Databases 11
Database Management Systems 11
Advantages of the Database Approach 11
Program-Data inDePenDence 11
PlanneD Data reDunDancy 12
imProveD Data conSiStency 12
imProveD Data Sharing 12
increaSeD ProDuctivity of aPPlication DeveloPment 13
enforcement of StanDarDS 13
imProveD Data Quality 13
imProveD Data acceSSibility anD reSPonSiveneSS 14
reDuceD Program maintenance 14
imProveD DeciSion SuPPort 14
Cautions About Database Benefits 14
Costs and Risks of the Database Approach 14
new, SPecializeD PerSonnel 15
inStallation anD management coSt anD comPlexity 15
converSion coStS 15
neeD for exPlicit backuP anD recovery 15
organizational conflict 15
Components of the Database Environment 15
ix
x
Contents
The Database Development Process 17
Systems Development Life Cycle 18
Planning?enterPriSe moDeling 18
Planning?concePtual Data moDeling 18
analySiS?concePtual Data moDeling 18
DeSign?logical DatabaSe DeSign 19
DeSign?PhySical DatabaSe DeSign anD Definition 20
imPlementation?DatabaSe imPlementation 20
maintenance?DatabaSe maintenance 20
Alternative Information Systems (IS) Development
Approaches 21
Three-Schema Architecture for Database Development 22
Managing the People Involved in Database Development 24
Evolution of Database Systems 24
1960s 26
1970s 26
1980s 26
1990s 26
2000 and Beyond 27
The Range of Database Applications 27
Personal Databases 28
Multitier Client/Server Databases 28
Enterprise Applications 29
Developing a Database Application for Pine Valley Furniture
Company 31
Database Evolution at Pine Valley Furniture Company 32
Project Planning 33
Analyzing Database Requirements 34
Designing the Database 36
Using the Database 39
Administering the Database 40
Future of Databases at Pine Valley 41
Summary 41 ? Key Terms 42 ? Review Questions 42 ?
Problems and Exercises 44 ? Field Exercises 45 ?
References 46 ? Further Reading 46 ? Web Resources 47
? Case: Forondo Artist Management Excellence Inc. 48
Part II Database Analysis
51
An Overview of Part Two 51
Chapter 2 Modeling Data in the organization
53
Learning Objectives 53
Introduction 53
The E-R Model: An Overview 56
Sample E-R Diagram 56
E-R Model Notation 58
Modeling the Rules of the Organization 59
Overview of Business Rules 60
the buSineSS ruleS ParaDigm 60
Contents
Scope of Business Rules 61
gooD buSineSS ruleS 61
gathering buSineSS ruleS 62
Data Names and Definitions 62
Data nameS 62
Data DefinitionS 63
gooD Data DefinitionS 63
Modeling Entities and Attributes 65
Entities 65
entity tyPe verSuS entity inStance 65
entity tyPe verSuS SyStem inPut, outPut, or uSer 65
Strong verSuS weak entity tyPeS 66
naming anD Defining entity tyPeS 67
Attributes 69
reQuireD verSuS oPtional attributeS 69
SimPle verSuS comPoSite attributeS 70
Single-valueD verSuS multivalueD attributeS 70
StoreD verSuS DeriveD attributeS 71
iDentifier attribute 71
naming anD Defining attributeS 72
Modeling Relationships 74
Basic Concepts and Definitions in Relationships 75
attributeS on relationShiPS 76
aSSociative entitieS 76
Degree of a Relationship 78
unary relationShiP 78
binary relationShiP 80
ternary relationShiP 81
Attributes or Entity? 82
Cardinality Constraints 84
minimum carDinality 84
maximum carDinality 84
Some Examples of Relationships and Their Cardinalities 85
a ternary relationShiP 86
Modeling Time-Dependent Data 86
Modeling Multiple Relationships Between Entity Types 89
Naming and Defining Relationships 90
E-R Modeling Example: Pine Valley Furniture Company 92
Database Processing at Pine Valley Furniture 94
Showing Product Information 95
Showing Product Line Information 95
Showing Customer Order Status 96
Showing Product Sales 97
Summary 98 ? Key Terms 99 ? Review Questions 99 ?
Problems and Exercises 100 ? Field Exercises 110 ?
References 110 ? Further Reading 111 ? Web Resources
? Case: Forondo Artist Management Excellence Inc. 112
Chapter 3 the enhanced e-r Model 114
Learning Objectives 114
Introduction 114
111
xi
xii
Contents
Representing Supertypes and Subtypes 115
Basic Concepts and Notation 116
an examPle of a SuPertyPe/SubtyPe relationShiP 117
attribute inheritance 118
when to uSe SuPertyPe/SubtyPe relationShiPS 118
Representing Specialization and Generalization 119
generalization 119
SPecialization 120
combining SPecialization anD generalization 121
Specifying Constraints in Supertype/Subtype Relationships 122
Specifying Completeness Constraints 122
total SPecialization rule 122
Partial SPecialization rule 122
Specifying Disjointness Constraints 123
DiSjoint rule 123
overlaP rule 123
Defining Subtype Discriminators 124
DiSjoint SubtyPeS 124
overlaPPing SubtyPeS 125
Defining Supertype/Subtype Hierarchies 125
an examPle of a SuPertyPe/SubtyPe hierarchy 126
Summary of SuPertyPe/SubtyPe hierarchieS 127
EER Modeling Example: Pine Valley Furniture Company 128
Entity Clustering 131
Packaged Data Models 134
A Revised Data Modeling Process with Packaged
Data Models 136
Packaged Data Model Examples 138
Summary 143 ? Key Terms 144 ? Review Questions 144 ?
Problems and Exercises 145 ? Field Exercises 148 ?
References 148 ? Further Reading 148 ? Web Resources 149
? Case: Forondo Artist Management Excellence Inc. 150
Part III Database Design
153
An Overview of Part Three 153
Chapter 4 Logical Database Design and the relational Model
Learning Objectives 155
Introduction 155
The Relational Data Model 156
Basic Definitions 156
relational Data Structure 157
relational keyS 157
ProPertieS of relationS 158
removing multivalueD attributeS from tableS 158
Sample Database 158
Integrity Constraints 160
Domain Constraints 160
Entity Integrity 160
Referential Integrity 162
155
Contents
Creating Relational Tables 163
Well-Structured Relations 164
Transforming EER Diagrams into Relations 165
Step 1: Map Regular Entities 166
comPoSite attributeS 166
multivalueD attributeS 167
Step 2: Map Weak Entities 167
when to create a Surrogate key 169
Step 3: Map Binary Relationships 169
maP binary one-to-many relationShiPS 169
maP binary many-to-many relationShiPS 170
maP binary one-to-one relationShiPS 170
Step 4: Map Associative Entities 171
iDentifier not aSSigneD 172
iDentifier aSSigneD 172
Step 5: Map Unary Relationships 173
unary one-to-many relationShiPS 173
unary many-to-many relationShiPS 174
Step 6: Map Ternary (and n-ary) Relationships 175
Step 7: Map Supertype/Subtype Relationships 176
Summary of EER-to-Relational Transformations 178
Introduction to Normalization 178
Steps in Normalization 179
Functional Dependencies and Keys 179
DeterminantS 181
canDiDate keyS 181
Normalization Example: Pine Valley Furniture Company 182
Step 0: Represent the View in Tabular Form 182
Step 1: Convert to First Normal Form 183
remove rePeating grouPS 183
Select the Primary key 183
anomalieS in 1nf 184
Step 2: Convert to Second Normal Form 185
Step 3: Convert to Third Normal Form 186
removing tranSitive DePenDencieS 186
Determinants and Normalization 187
Step 4: Further Normalization 187
Merging Relations 188
An Example 188
View Integration Problems 188
SynonymS 189
homonymS 189
tranSitive DePenDencieS 189
SuPertyPe/SubtyPe relationShiPS 190
A Final Step for Defining Relational Keys 190
Summary 192 ? Key Terms 194 ? Review Questions 194 ?
Problems and Exercises 195 ? Field Exercises 204 ?
References 204 ? Further Reading 204 ? Web Resources 204
? Case: Forondo Artist Management Excellence Inc. 205
xiii
xiv
Contents
Chapter 5 Physical Database Design and Performance
206
Learning Objectives 206
Introduction 206
The Physical Database Design Process 207
Physical Database Design as a Basis for Regulatory Compliance 208
Data Volume and Usage Analysis 209
Designing Fields 210
Choosing Data Types 211
coDing techniQueS 212
hanDling miSSing Data 213
Denormalizing and Partitioning Data 213
Denormalization 213
oPPortunitieS for anD tyPeS of Denormalization 214
Denormalize with caution 216
Partitioning 217
Designing Physical Database Files 219
File Organizations 221
heaP file organization 221
SeQuential file organizationS 221
inDexeD file organizationS 221
haSheD file organizationS 224
Clustering Files 227
Designing Controls for Files 227
Using and Selecting Indexes 228
Creating a Unique Key Index 228
Creating a Secondary (Nonunique) Key Index 228
When to Use Indexes 229
Designing a Database for Optimal Query Performance 230
Parallel Query Processing 230
Overriding Automatic Query Optimization 231
Summary 232 ? Key Terms 233 ? Review Questions 233 ?
Problems and Exercises 234 ? Field Exercises 237 ?
References 237 ? Further Reading 237 ? Web Resources 238
? Case: Forondo Artist Management Excellence Inc. 239
Part IV Implementation
241
An Overview of Part Four 241
Chapter 6 introduction to sQL 243
Learning Objectives 243
Introduction 243
Origins of the SQL Standard 245
The SQL Environment 247
Defining a Database in SQL 251
Generating SQL Database Definitions 252
Creating Tables 253
Creating Data Integrity Controls 255
Changing Table Definitions 256
Removing Tables 257
Contents
Inserting, Updating, and Deleting Data 257
Batch Input 259
Deleting Database Contents 259
Updating Database Contents 259
Internal Schema Definition in RDBMSs 260
Creating Indexes 260
Processing Single Tables 261
Clauses of the SELECT Statement 262
Using Expressions 264
Using Functions 265
Using Wildcards 267
Using Comparison Operators 267
Using Null Values 268
Using Boolean Operators 268
Using Ranges for Qualification 271
Using Distinct Values 271
Using IN and NOT IN with Lists 273
Sorting Results: The ORDER BY Clause 274
Categorizing Results: The GROUP BY Clause 275
Qualifying Results by Categories: The HAVING Clause 276
Using and Defining Views 277
materializeD viewS 281
Summary 281 ? Key Terms 282 ? Review Questions 282 ?
Problems and Exercises 283 ? Field Exercises 286 ?
References 287 ? Further Reading 287 ? Web Resources 287
? Case: Forondo Artist Management Excellence Inc. 288
Chapter 7 Advanced sQL 289
Learning Objectives 289
Introduction 289
Processing Multiple Tables 290
Equi-join 291
Natural Join 292
Outer Join 293
Sample Join Involving Four Tables 295
Self-Join 297
Subqueries 298
Correlated Subqueries 303
Using Derived Tables 305
Combining Queries 306
Conditional Expressions 308
More Complicated SQL Queries 308
Tips for Developing Queries 310
Guidelines for Better Query Design 312
Ensuring Transaction Integrity 314
Data Dictionary Facilities 315
Recent Enhancements and Extensions to SQL 317
Analytical and OLAP Functions 317
New Data Types 319
xv
xvi
Contents
New Temporal Features in SQL 319
Other Enhancements 320
Triggers and Routines 321
Triggers 321
Routines and other Programming Extensions 323
Example Routine in Oracle?s PL/SQL 325
Embedded SQL and Dynamic SQL 327
Summary 329 ? Key Terms 330 ? Review Questions 330 ?
Problems and Exercises 331 ? Field Exercises 334 ?
References 334 ? Further Reading 334 ? Web Resources 335
? Case: Forondo Artist Management Excellence Inc. 336
Chapter 8 Database Application Development
337
Learning Objectives 337
Location, Location, Location! 337
Introduction 338
Client/Server Architectures 338
Databases in a Two-Tier Architecture 340
A VB.NET Example 342
A Java Example 344
Three-Tier Architectures 345
Web Application Components 347
Databases in Three-Tier Applications 349
A JSP Web Application 349
A PHP Example 353
An ASP.NET Example 355
Key Considerations in Three-Tier Applications 356
Stored Procedures 356
Transactions 359
Database Connections 359
Key Benefits of Three-Tier Applications 359
Cloud Computing and Three-Tier Applications 360
Extensible Markup Language (XML) 361
Storing XML Documents 363
Retrieving XML Documents 363
Displaying XML Data 366
XML and Web Services 366
Summary 369 ? Key Terms 370 ? Review Questions 370 ?
Problems and Exercises 371 ? Field Exercises 372 ?
References 372 ? Further Reading 372 ? Web Resources 372
? Case: Forondo Artist Management Excellence Inc. 373
Chapter 9 Data Warehousing
374
Learning Objectives 374
Introduction 374
Basic Concepts of Data Warehousing 376
A Brief History of Data Warehousing 377
The Need for Data Warehousing 377
neeD for a comPany-wiDe view 377
neeD to SeParate oPerational anD informational SyStemS 379
Contents
Data Warehouse Architectures 380
Independent Data Mart Data Warehousing Environment 380
Dependent Data Mart and Operational Data Store Architecture:
A Three-Level Approach 382
Logical Data Mart and Real-Time Data Warehouse
Architecture 384
Three-Layer Data Architecture 387
role of the enterPriSe Data moDel 388
role of metaData 388
Some Characteristics of Data Warehouse Data 388
Status Versus Event Data 388
Transient Versus Periodic Data 389
An Example of Transient and Periodic Data 389
tranSient Data 389
PerioDic Data 391
other Data warehouSe changeS 391
The Derived Data Layer 392
Characteristics of Derived Data 392
The Star Schema 393
fact tableS anD DimenSion tableS 393
examPle Star Schema 394
Surrogate key 395
grain of the fact table 396
Duration of the DatabaSe 397
Size of the fact table 397
moDeling Date anD time 398
Variations of the Star Schema 399
multiPle fact tableS 399
factleSS fact tableS 400
Normalizing Dimension Tables 401
multivalueD DimenSionS 401
hierarchieS 402
Slowly Changing Dimensions 404
Determining Dimensions and Facts 406
The Future of Data Warehousing: Integration with Big Data
and Analytics 408
Speed of Processing 409
Cost of Storing Data 409
Dealing with Unstructured Data 409
Summary 410 ? Key Terms 410 ? Review Questions 411 ?
Problems and Exercises 411 ? Field Exercises 415 ?
References 415 ? Further Reading 416 ? Web Resources 416
Part V Advanced Database Topics
417
An Overview of Part Five 417
Chapter 10 Data Quality and integration 419
Learning Objectives 419
Introduction 419
Data Governance 420
xvii
xviii
Contents
Managing Data Quality 421
Characteristics of Quality Data 422
external Data SourceS 423
reDunDant Data Storage anD inconSiStent metaData 424
Data entry ProblemS 424
lack of organizational commitment 424
Data Quality Improvement 424
get the buSineSS buy-in 424
conDuct a Data Quality auDit 425
eStabliSh a Data StewarDShiP Program 426
imProve Data caPture ProceSSeS 426
aPPly moDern Data management PrinciPleS anD technology 427
aPPly tQm PrinciPleS anD PracticeS 427
Summary of Data Quality 427
Master Data Management 428
Data Integration: An Overview 429
General Approaches to Data Integration 429
Data feDeration 430
Data ProPagation 431
Data Integration for Data Warehousing: The Reconciled
Data Layer 431
Characteristics of Data After ETL 431
The ETL Process 432
maPPing anD metaData management 432
extract 433
cleanSe 434
loaD anD inDex 436
Data Transformation 437
Data Transformation Functions 438
recorD-level functionS 438
fielD-level functionS 439
Summary 441 ? Key Terms 441 ? Review Questions 441 ?
Problems and Exercises 442 ? Field Exercises 443 ?
References 443 ? Further Reading 444 ? Web Resources 444
Chapter 11 Big Data and Analytics
445
Learning Objectives 445
Introduction 445
Big Data 447
NoSQL 449
Classification of NoSQL Database Management Systems 450
key-value StoreS 450
Document StoreS 450
wiDe-column StoreS 451
graPh-orienteD DatabaSeS 451
NoSQL Examples 452
reDiS 452
mongoDb 452
aPache caSSanDra 452
neo4j 452
Impact of NoSQL on Database Professionals 452
Contents
Hadoop 453
Components of Hadoop 454
the haDooP DiStributeD file SyStem (hDfS)
maPreDuce 455
Pig 456
hive 456
hbaSe 457
454
Integrated Analytics and Data Science Platforms 457
hP haven 457
teraData aSter 457
ibm big Data Platform 457
Putting It All Together: Integrated Data Architecture 458
Analytics 460
Types of Analytics 461
Use of Descriptive Analytics 462
SQl olaP Querying 463
online analytical ProceSSing (olaP) toolS 465
Data viSualization 467
buSineSS Performance management anD DaShboarDS 469
Use of Predictive Analytics 470
Data mining toolS 470
examPleS of PreDictive analyticS 472
Use of Prescriptive Analytics 473
Data Management Infrastructure for Analytics 474
Impact of Big Data and Analytics 476
Applications of Big Data and Analytics 476
buSineSS 477
e-government anD PoliticS 477
Science anD technology 478
Smart health anD well-being 478
Security anD Public Safety 478
Implications of Big Data Analytics and Decision Making 478
PerSonal Privacy vS. collective benefitS 479
ownerShiP anD acceSS 479
Quality anD reuSe of Data anD algorithmS 479
tranSParency anD valiDation 480
changing nature of work 480
DemanDS for workforce caPabilitieS anD eDucation 480
Summary 480 ? Key Terms 481 ? Review Questions
Problems and Exercises 482 ? References 483 ?
Further Reading 484 ? Web Resources 484
Chapter 12 Data and Database Administration 485
Learning Objectives 485
Introduction 485
The Roles of Data and Database Administrators 486
Traditional Data Administration 486
Traditional Database Administration 488
Trends in Database Administration 489
Data Warehouse Administration 491
Summary of Evolving Data Administration Roles 492
481 ?
xix
xx
Contents
The Open Source Movement and Database Management 492
Managing Data Security 494
Threats to Data Security 495
Establishing Client/Server Security 496
Server Security 496
network Security 496
Application Security Issues in Three-Tier Client/Server
Environments 497
Data Privacy 498
Database Software Data Security Features 499
Views 500
Integrity Controls 500
Authorization Rules 502
User-Defined Procedures 503
Encryption 503
Authentication Schemes 504
PaSSworDS 505
Strong authentication 505
Sarbanes-Oxley (SOX) and Databases 505
IT Change Management 506
Logical Access to Data 506
PerSonnel controlS 506
PhySical acceSS controlS 507
IT Operations 507
Database Backup and Recovery 507
Basic Recovery Facilities 508
backuP facilitieS 508
journalizing facilitieS 508
checkPoint facility 509
recovery manager 509
Recovery and Restart Procedures 510
DiSk mirroring 510
reStore/rerun 510
maintaining tranSaction integrity 510
backwarD recovery 512
forwarD recovery 513
Types of Database Failure 513
aborteD tranSactionS 513
incorrect Data 513
SyStem failure 514
DatabaSe DeStruction 514
Disaster Recovery 514
Controlling Concurrent Access 515
The Problem of Lost Updates 515
Serializability 515
Locking Mechanisms 516
locking level 516
tyPeS of lockS 517
DeaDlock 518
managing DeaDlock 518
Contents
Versioning 519
Data Dictionaries and Repositories 521
Data Dictionary 521
Repositories 521
Overview of Tuning the Database for Performance 523
Installation of the DBMS 523
Memory and Storage Space Usage 523
Input/Output (I/O) Contention 524
CPU Usage 524
Application Tuning 525
Data Availability 526
Costs of Downtime 526
Measures to Ensure Availability 526
harDware failureS 527
loSS or corruPtion of Data 527
human error 527
maintenance Downtime 527
network-relateD ProblemS 527
Summary 528 ? Key Terms 528 ? Review Questions
Problems and Exercises 530 ? Field Exercises 532 ?
References 532 ? Further Reading 533 ?
Web Resources 533
Glossary of Acronyms
Glossary of Terms
Index
544
536
534
529 ?
xxi
xxii
Online Chapters
onLine ChAPters
Chapter 13 Distributed Databases 13-1
Learning Objectives 13-1
Introduction 13-1
Objectives and Trade-offs 13-4
Options for Distributing a Database 13-6
Data Replication 13-6
SnaPShot rePlication 13-7
near-real-time rePlication 13-8
Pull rePlication 13-8
DatabaSe integrity with rePlication 13-8
when to uSe rePlication 13-8
Horizontal Partitioning 13-9
Vertical Partitioning 13-10
Combinations of Operations 13-11
Selecting the Right Data Distribution Strategy 13-11
Distributed DBMS 13-13
Location Transparency 13-15
Replication Transparency 13-16
Failure Transparency 13-17
Commit Protocol 13-17
Concurrency Transparency 13-18
time-StamPing 13-18
Query Optimization 13-19
Evolution of Distributed DBMSs 13-21
remote unit of work 13-22
DiStributeD unit of work 13-22
DiStributeD reQueSt 13-23
Summary 13-23 ? Key Terms 13-24 ? Review Questions
Problems and Exercises 13-25 ? Field Exercises 13-26 ?
References 13-27 ? Further Reading 13-27 ?
Web Resources 13-27
Chapter 14 object-oriented Data Modeling
14-1
Learning Objectives 14-1
Introduction 14-1
Unified Modeling Language 14-3
Object-Oriented Data Modeling 14-4
Representing Objects and Classes 14-4
Types of Operations 14-7
Representing Associations 14-7
Representing Association Classes 14-11
Representing Derived Attributes, Derived Associations,
and Derived Roles 14-12
Representing Generalization 14-13
Interpreting Inheritance and Overriding 14-18
Representing Multiple Inheritance 14-19
Representing Aggregation 14-19
13-24 ?
www.pearsonhighered.com/hoffer
Business Rules 14-22
Object Modeling Example: Pine Valley Furniture Company 14-23
Summary 14-25 ? Key Terms 14-26 ? Review Questions
Problems and Exercises 14-30 ? Field Exercises 14-37 ?
References 14-37 ? Further Reading 14-38 ?
Web Resources 14-38
Appendix A Data Modeling tools and notation A-1
Comparing E-R Modeling Conventions A-1
Visio Professional 2013 Notation A-1
entitieS a-5
relationShiPS a-5
CA ERwin Data Modeler 9.5 Notation A-5
entitieS a-5
relationShiPS a-5
SAP Sybase PowerDesigner 16.5 Notation A-7
entitieS a-8
relationShiPS a-8
Oracle Designer Notation A-8
entitieS a-8
relationShiPS a-8
Comparison of Tool Interfaces and E-R Diagrams A-8
Appendix B Advanced normal forms B-1
Boyce-Codd Normal Form B-1
Anomalies in Student Advisor B-1
Definition of Boyce-Codd Normal Form (BCNF) B-2
Converting a Relation to BCNF B-2
Fourth Normal Form B-3
Multivalued Dependencies B-5
Higher Normal Forms B-5
Key Terms
B-6 ? References
B-6 ? Web Resource
Appendix C Data structures C-1
Pointers C-1
Data Structure Building Blocks C-2
Linear Data Structures C-4
Stacks C-5
Queues C-5
Sorted Lists C-6
Multilists C-8
Hazards of Chain Structures C-8
Trees C-9
Balanced Trees C-9
Reference
C-12
B-6
14-26 ?
xxiii
This page intentionally left blank
PrefaCe
This text is designed to be used with an introductory course in database management.
Such a course is usually required as part of an information systems curriculum in
business schools, computer technology programs, and applied computer science
departments. The Association for Information Systems (AIS), the Association for
Computing Machinery (ACM), and the International Federation of Information
Processing Societies (IFIPS) curriculum guidelines (e.g., IS 2010) all outline this type
of database management course. Previous editions of this text have been used successfully for more than 33 years at both the undergraduate and graduate levels, as well as in
management and professional development programs.
What?s NeW iN this editioN?
This 12th edition of Modern Database Management updates and expands materials in
areas undergoing rapid change as a result of improved managerial practices, database
design tools and methodologies, and database technology. Later, we detail changes to
each chapter. The themes of this 12th edition reflect the major tre