Database management system by jeffrey hoffer pdf

Pearson RENTAL EDITION - RENTAL ONLY, NOT FOR SALE I

THIRTEENTH EDITION

MODERN DATABASE

JEFFREY A. HOFFER

V. RAMESH

HEIKKI TOPI

Complete Listing of Chapter Opening Cases, Insight Cases, E-commerce in Action Cases, and Case Studies

CHAPTER 1 THE REVOLUTION IS JUST BEGINNING Opening Case: Everything on Demand: The "Uberization" of E-commerce

Insight on Technology: Will Ap ps Make the Web Irrelevant?

Insight on Business: Startup Boot Camp

Insight on Society: Facebook and the Age of Privacy

Case Study: Pinterest: A Picture Is Worth a Thousand Words

CHAPTER 2 E-COMMERCE BUSINESS MODELS AND CONCEPTS Opening Case: Tweet Tweet: Will Twitter Ever Find a Business Model that Works?

Insight on Society: Foursquare: Check Your Privacy at the Door

Insight on Business: Crowdfunding Takes Off

Insight on Technology: Will the Connected Car Become the Next Hot Ente rtainment Vehicle?

Case Study: Dollar Shave Club: From Viral Video to $I Bill ion in Just Five Years

CHAPTER 3 E-COMMERCE INFRASTRUCTURE: THE INTERNET, WEB, AND MOBILE PLATFORM

Opening Case: Voice-Controlled Intell igent Digital Ass istants: Will They Revolutionize E-commerce?

Insight on Society: Government Regu lation and Surveillance of the Internet

Insight on Technology: The Rise ofHTMLS

Insight on Business: The Apple \.Vatch: Bringing The Internet of Things to Your Wrist

Case Study: Akamai Technologies: Attempting to Keep Supply Ahead of Demand

CHAPTER 4 BUILDING AN E-COMMERCE PRESENCE: WEBSITES, MOBILE SITES, AND APPS Opening Case: The Wall Street Jou rnal: Redesign ing for Today's Platforms

Insight on Business: Weebly lviakes Creating \.Yebsites Easy

Insight on Society: Designing for Accessibility

Insight on Technology: Carnival Cruise Ships Go Mobi le

Case Study: Dick's Sporting Goods: Taking Control of Its E-commerce Operations

CHAPTER 5 E-COMMERCE SECURITY AND PAYMENT SYSTEMS Opening Case: Cyberwar: MAD 2.0

Insight on Society: Equifax: Really Big Data Hacked

Insight on Technology: Think Your Smartphone Is Secure?

Insight on Business: Bitcoin

Case Study: The Mobi le Payment lviarketplace: Goat Rodeo

CHAPTER 6 E-COMMERCE MARKETING AND ADVERTISING CONCEPTS Opening Case: Video Ads: Shoot, Click, Buy

Insight on Business: Are the Very Rich Different From You and lvie?

Insight on Technology: The Long Ta il: Big Hits and Big Misses

Insight on Society: Every Move You Take, Every Click You Make, We'll Be Tracking You

Case Study: Programmatic Advertising: Real-Time lviarketing

CHAPTER 7 SOCIAL, MOBILE, AND LOCAL MARKETING

Opening Case: Facebook: Putting Social Marketing to Work

Insight on Technology: Optimizing Social Marketing with Simply Measured

Insight on Society: Marketing to Children of the Web in the Age of Social Networks

Insight on Business: Mobile Marketing Goes 3-D

Case Study: ExchangeHunte rJumper.com: Bui lding a Brand with Social lvlarketing

CHAPTER 8 ETHICAL, SOCIAL, AND POLITICAL ISSUES IN E-COMMERCE

Opening Case: The Right To Be Forgotten: Europe Leads on Internet Privacy

Insight on Technology: Apple: Defender of Privacy?

Insight on Business: Internet Sales Tax Battle

Insight on Society: The Internet Drug Bazaar

Case Study: The Pirate Bay: Searching for a Safe Haven

CHAPTER 9 ONLINE RETAILING AND SERVICES

Opening Case: Blue Nile Sparkles for Your Cleopatra

E-commerce in Action: Amazon

Insight on Technology: Big Data and Predictive Marketing

Insight on Society: Phony Reviews

Insight on Business: Food on Demand: Instacart and GrubHub

Case Study: OpenTable: Your Reservation Is Waiting

CHAPTER 10 ONLINE CONTENT AND MEDIA

Opening Case: Cord Cutte rs and Cord Shavers: The Emerging Internet Broadcasting System (!BS)

Insight on Society: Are lvli llennials Really All That Different?

Insight on Business: Vox: Native Digita l News

Insight on Technology: Hollywood and the Internet: Let's Cut a Deal

Case Study: Netfl ix: How Does This Movie End?

CHAPTER 11 SOCIAL NETWORKS, AUCTIONS, AND PORTALS

Opening Case: Social Network Fever Spreads to the Professions

Insight on Society: The Dark Side of Social Networks

Insight on Technology: Trapped Inside the Facebook Bubble?

Insight on Business: Verizon Doubles Down on Portals

Case Study: eBay Evolves

CHAPTER 12 828 E-COMMERCE: SUPPLY CHAIN MANAGEMENT AND COLLABORATIVE COMMERCE

Opening Case: Amazon Takes on B2B with Amazon Business

Insight on Society: Where's My !Pad? Supply Chain Risk and Vulne rability

Insight on Technology: Your Shoes Are in the Cloud

Insight on Business: Walmart Develops a Private Industrial Network

Case Study: Elemica: Cooperation, Collaboration, and Community

OTHER MIS TITLES OF INTEREST

Introductory MIS

Experienc ing MIS, 8/e Kroenke & Boyle ©2019

Using MIS, 10/e Kroenke & Boyle ©2018

Management Information Systems, 15/e Laudon & Laudon ©2018

Essentials of MIS, 13/e Laudon & Laudon ©2019

Processes, Systems, and Information: An Introduction to MIS, 3/e McKinney & Kroenke ©2019

Information Systems Today, 8/e Valacich & Schneider ©2018

Introduction to Information Systems, 3/ e Wallace ©2018

Database

Hands-on Database, 2/e Conger ©2014

Modern Database Management, 13/e Hoffer, Ramesh & Topi ©2019

Database Concepts, 8/e Kroenke, Auer, Vandenburg, Yoder ©2018

Database Processing, 15/e Kroenke & Auer ©2019

Systems Analysis and Design

Modern Systems Analysis and Design, 8/e Hoffer, George & Valacich ©2017

Systems Analysis and Design, 10/e Kendall & Kendall ©2019

Decision Support Systems

Business Intelligence, Analytics, and Data Science, 4/e Sharda, Delen & Turban ©2018

Business Intelligence and Analytics: Systems for Decision Support, 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, 11/e Panko & Panko ©2019

Electronic Commerce

E-commerce 2018: Business. Technology. Society, 14/e Laudon & Traver ©2019

Enterprise Resource Planning

Enterprise Systems for Management, 2/e Motiwalla & Thompson ©2012

Project Management

Project Management: Process, Technology and Practice Vaidyanathan ©2013

THIRTEENTH EDITION

MODERN DATABASE MANAGEMENT

TH I RTEENTH EDITION

MODERN DATABASE MANAGEMENT

1) Pearson •

Jeffrey A. H offer University of Dayton

V. Ramesh Indiana University

Heikki Topi Bentley University

330 Hudson Street, NY NY 10013

Vice Pres ident, IT & Careers: Andrew Gilfillan Senior Portfolio Man ager: Samantha Lewis Managing Prod ucer: Laura Burgess Associate Content Producer: Stephany Harrington Portfolio Management Assis tant: Madeline Houpt Director of Prod uct Marketing: Brad Parkins Product Marketing Man ager: Heather Taylor Product Marketing Assistant Jesika Bethea Field Marketing Manager: Molly Schmid t Field Marketing Assistant: Kelli Fisher Cover Im age: VICTOR HABBICK VISIONS/ Getty Images

Vice President, Prod uct Model Management: Jason Fournier Senior Product Model Manag er: Eric Hakanson Lead, Production and Dig ital Studio: Heather Darby Digital Studio Course Prod ucer: Jaimie Noy Program Monitor: Danica Monzor, SPi Global Full-Service Project Manaf ment:

Neha Bhargava, Cenveo Publisher Services Comp os ition: Cenveo Publisher Services Printer/ Binder: LSC Communications Cover Printer: Phoenix Color Text Font Palatino LT Pro

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 g raphics are provided "as is" without warranty of any kind. M;crosoft and / or its respective suppliers hereby disclaim all warranties and conditions w ith regard lo this information, including aU warranties and conditions of merchantability, w hether express, implied or s tatutory, fitness for a particular purpose, title and non- infringemenl. 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 o f contract, neg ligence or other tortious action, arising out of or in connection w ith the use or performance o f information available from the senrices.

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 al 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© 2019, 2016, 2013 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 lo 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 I 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 proper ty 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

Names: Hoffer, Jeffrey A., author. I Ramesh, V. (Venkataraman), author. I Topi, Heikki, author.

Title: Modern database management I Jeffrey A. Hoffer, University of Dayton, V. Ramesh, Indiana University, Heikki Topi, Bentley University.

Description: Thirteenth edition. I Boston: Pearson Education, [2017) I Includes bibliographical references and index.

Identifiers: LCCN 2017048422 1 ISBN 9780134773650 (alk. paper) I ISBN 0133544613 (alk. paper)

Subjects: LCSH: Database management. Classification: LCC QA76.9.D3 M395 2017 I DOC 005.7~c23 LC record available al https:/ / lccn.Joc.gov / 2017048422

10 9 8 7 6 5 4 3 2 1

1) Pearson • ISBN 10: 0-13-477365-9 ISBN 13: 978-0-13-477365-0

To Patty, for her sacrifices, encouragement, and support for more than 35 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.

BRIEF CONTENTS

Part I The Context of Database Management 1 Chapter 1 The Database Environment and Development Process 3

Part II Database Analysis and Logical Design 53

Chapter 2 Modeling Data in the Organization 55 Chapter 3 The Enhanced E-R Model 115

Chapter 4 Logical Database Design and the Relational Model 153

Part Ill Database Implementation and Use 205

Chapter 5 Introduction to SQL 207

Chapter 6 Advanced SQL 251

Chapter 7 Databases in Applications 297

Chapter 8 Physical Database Design and Database Infrastructure 333

Part IV Advanced Database Topics 385

Chapter 9 Data Warehousing and Data Integration 387

Chapter 10 Big Data Technologies 444 Chapter 11 Analytics and Its Implications 474

Chapter 12 Data and Database Administration w ith Focus on Data Quality 503

Glossary of Acronyms 529 Glossary of Terms 531

Index 539

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 A-1 Appendix B Advanced Normal Forms B-1

Appendix C Data Structures C-1

VII

CONTENTS

Preface xxv

Part I The Context of Database Management 1 An Overview of Part I 1

Chapter 1 The Database Envi ronment and Development Process 3 Learning Objectives 3

Data Matter! 4

Introduction 5

Basic Concepts and Definit ions 6

Data 6

Data versus Informat ion 7

Metadat a 8

Traditional File Processing Systems 9

Fi le Processing Systems at Pine Valley Furniture Company 9

Disadvantages of Fi le Processing Systems 10 PROGRAM· DATA DEPENDENCE 10

DUPLICATION OF DATA 10 LIMITED DATA SHARING 10

LENGTHY DEVELOPMENT T IMES 10

EXCESSIVE PROGRAM MAINTENANCE 11

The Database Approach 11

Data Models 11 ENTITIES 11 RELATIONSH IPS 11

Relat ional Databases 12

Database Management Systems 13

Advant ages of t he Database Approach 13 PROGRAM-DATA INDEPENDENCE 13 PLANNED DATA REDUNDANCY 14

IMPROVED DATA CONSISTENCY 14

IMPROVED DATA SHARING 14

INCREASED PRODUCTIVITY OF APPLICATION DEVELOPMENT 14

ENFORCEMENT OF STANDARDS 15

IMPROVED DATA QUALITY 15 IMPROVED DATA ACCESSIBILITY AND RESPONSIVENESS 15

REDUCED PROGRAM MAINTENANCE 16

IMPROVED DECISION SUPPORT 16 CAUTIONS ABOUT DATABASE BENEFITS 16

COSTS AND RISKS OF THE DATABASE APPROACH 16

New, SPECIALIZED PERSONNEL 16 INSTALLATION AND MANAGEMENT (OST AND COMPLEXITY 17

CONVERSION COSTS 17

N EED FOR EXPLICIT BACKUP AND RECOVERY 17 ORGANIZATIONAL CONFLICT 17

Integrated Data Management Framework 17

Components of t he Dat abase Environment 18

IX

x Contents

The Database Development Process 20

Systems Development Life Cycle 21 PLANNING- ENTERPRISE MODELING 21

PLANNING-CONCEPTUAL DATA MODELING 21 ANALYSIS-CONCEPTUAL DATA MODELING 22

DESIGN- LOGICAL DATABASE DESIGN 23 D ESIGN- PHYSICAL DATABASE D ESIGN AND DEFINITION 23 IMPLEMENTATION- DATABASE IMPLEMENTATION 23 MAINTENANCE-DATABASE MAINTENANCE 24

Alternative Information Systems Development Approaches 24

Three-Schema Architecture for Database Development 25

Managing the People Involved in Database Development 27

Evolution of Database Systems 27

1960s 29

1970s 29

1980s 29

1990s 30

2000 and Beyond 30

The Range of Database Applications 30

Personal Databases 31

Departmental Multi-Tiered Client/Server Databases 31

Enterprise Applications 32 ENTERPRISE SYSTEMS 32 DATA WAREHOUSES 33 DATA LAKE 34

Developing a Database Application for Pine Valley Furniture Company 35

Database Evolution at Pine Valley Furniture Company 36

Project Planning 36

Analyzing Database Requirements 37

Designing the Database 40

Using the Database 42

Administering the Database 43

Future of Databases at Pine Valley 43 Summary 44 • Key Terms 45 • Review Questions 45 • Problems and Exercises 46 • Field Exercises 48 • References 49 • Further Reading 49 • Web Resources 50

... CASE: Forondo Artist Management Excellence Inc. 51

Part II Database Analysis and Logical Design 53 An Overview of Part II 53

Chapter 2 Modeling Data in the Organization 55 Learning Objectives 55

Introduction 55

The E-R Model: An Overview 58

Sample E-R Diagram 58

E-R Model Notation 60

Modeling the Rules of the Organization 61

Overview of Business Rules 62 THE BUSINESS RULES PARADIGM 62

Scope of Business Rules 63 GOOD BUSINESS RULES 63

GATHERING BUSINESS RULES 64

Data Names and Definitions 64 DATA N AMES 64 DATA DEFINITIONS 65

GOOD DATA DEFINITIONS 65

Modeling Entit ies and Attributes 67

Ent ities 67 ENTITY TYPE VERSUS ENTITY INSTANCE 67 ENTITY TYPE VERSUS SYSTEM INPUT, OUTPUT, OR USER 67

STRONG VERSUS WEAK ENTITY TYPES 68

NAMING AND DEFINING ENTITY TYPES 69

Attributes 71 REQUIRED VERSUS OPTIONAL ATTRIBUTES 7 1

SIMPLE VERSUS COMPOSITE ATTRIBUTES 72

SINGLE-VALUED VERSUS M ULTIVALUED ATTRIBUTES 72 STORED VERSUS DERIVED ATTRIBUTES 73

IDENTIFIER ATTRIBUTE 73

NAMING AND DEFINING ATTRIBUTES 74

Modeling Relationships 76

Basic Concepts and Def init ions in Relat ionships 77 ATTRIBUTES ON RELATIONSHIPS 78

AsSOCIATIVE ENTITIES 78

Degree of a Relationship 80 UNARY RELATIONSHIP 81 B INARY RELATIONSHIP 82

TERNARY RELATIONSHIP 82

Attributes or Entity? 83

Cardinality Constraints 85 M INIMUM CARDINALITY 85 MAXIMUM CARDINALITY 86

Some Examples of Relationships and Their Cardinalities 86 A TERNARY RELATIONSHIP 87

Modeling Time-Dependent Data 88

Modeling Mult iple Relationships Between Entity Types 90

Naming and Defining Relationships 92 E-R Modeling Example: Pine Val ley Furniture Company 93

Database Processing At Pine Valley Furniture 96 Showing Product Information 96

Showing Product Line Information 96

Showing Customer Order Status 97

Showing Product Sales 98 Summary 99 • Key Terms 100 • Review Questions 100 • Problems and Exercises 101 • Field Exercises 111 • References 112 • Further Reading 112 • Web Resources 112

.,. CASE: ForondoArtist Management Excellence Inc. 11 3

Contents xi

PINE VALLEY FURNITURE

xu Contents

Chapter 3 The Enhanced E-R Model 115 Learning Objectives 115

Introduction 115

Representing Supertypes and Subtypes 116

Basic Concepts and Notation 117 AN EXAMPLE OF A SUPERTYPEISUBTYPE RELATIONSHIP 118

ATTRIBUTE INHERITANCE 119

WHEN TO USE SuPERTYPEISUBTYPE RELATIONSHIPS 119

Representing Specialization and Generalizat ion 120 GENERALIZATION 120

SPECIALIZATION 121

COMBINING SPECIALIZATION AND GENERALIZATION 122

Specifying Constraints in Supertype/Subtype Relationships 123

Specifying Completeness Constraints 123 TOTAL SPECIALIZATION RULE 123

PARTIAL SPECIALIZATION RULE 123

Specifying Disjointness Const raints 124 D ISJOINT RULE 124

OVERLAP RULE 125

Defining Subtype Discriminators 125 D ISJOINT SUBTYPES 12 5

OVERLAPPING SUBTYPES 126

Defining Supertype/Subtype Hierarchies 127 AN EXAMPLE OF A SUPERTYPEISUBTYPE HIERARCHY 128

SUMMARY OF SUPERTYPE/SUBTYPE H IERARCHIES 128

EER Modeling Example: Pine Valley Furniture Company 128

Entity Clustering 132

Packaged Data Models 135

A Revised Data Modeling Process with Packaged Data Models 137

Packaged Data Model Examples 139 Summary 144 • Key Terms 145 • Review Questions 145 • Problems and Exercises 146 • Field Exercises 149 • References 149 • Further Reading 150 • Web Resources 150

.,. CASE: Forondo Artist Management Excellence Inc. 151

Chapter 4 Logical Database Design and the Relat ional Model 153 Learning Objectives 153

Introduction 153

The Relational Data Model 154

Basic Definitions 154 RELATIONAL D ATA STRUCTURE 155

RELATIONAL KEYS 155

PROPERTIES OF RELATIONS 156

REMOVING MULTIVALUED ATTRIBUTES FROM TABLES 156

Sample Database 157

Integrity Constraints 158

Domain Constraints 158

Entity Integrity 158

Referent ial Integrity 160

Creating Relational Tables 161

Well-Structured Relations 162

Transforming EER Diagrams into Relations 163

Step 1: Map Regular Entities 164 COMPOSITE AITRIBUTES 164

MULTIVALUED AITRIBUTES 165

Step 2: Map Weak Entities 165 WHEN TO CREATE A SURROGATE KEY 166

Step 3: Map Binary Relationships 167 MAP BINARY ONE-TO-MANY RELATIONSHIPS 167

MAP BINARY M ANY-TO· MANY RELATIONSHIPS 168

MAP BINARY 0NE-T0· 0NE RELATIONSHIPS 168

Step 4: Map Associative Entities 169 IDENTIFIER NOT ASSIGNED 169

IDENTIFIER AsSIGNED 170

Step 5: Map Unary Relationships 171 UNARY 0NE·TO· M ANY RELATIONSHIPS 171

UNARY M ANY-TO· MANY RELATIONSHIPS 172

Step 6: Map Ternary (and n-ary) Relationships 173

Step 7: Map Supertype/Subtype Relationships 174

Summary of EER-to-Relational Transformations 176

Introduction to Normalization 176

Steps in Normalization 177

Functional Dependencies and Keys 177 DETERMINANTS 179

CANDIDATE KEYS 179

Normalization Example: Pine Val ley Furniture Company 180

Step 0: Represent the View in Tabular Form 180

Step 1: Convert to First Normal Form 181 REMOVE REPEATING GROUPS 181

SELECT THE PRIMARY KEY 182

ANOMALIES IN 1 NF 182

Step 2: Convert to Second Normal Form 183

Step 3: Convert to Third Normal Form 184 REMOVING TRANSITIVE DEPENDENCIES 184

Determinants and Normalization 185

Step 4: Further Normalization 185

Merging Relations 186

An Example 186

View Integration Problems 186 SYNONYMS 187

HOMONYMS 187

TRANSITIVE D EPENDENCIES 187

SuPERTYPE/SuSTYPE RELATIONSHIPS 188

A Final Step for Defining Relational Keys 188 Summary 191 • Key Terms 191 • Review Questions 191 • Problems and Exercises 192 • Field Exercises 201 • References 202 • Further Reading 202 • Web Resources 202

.,. CASE: Forondo Artist Management Excellence Inc. 203

1'

Contents XIII

PINE VALLEY FURNITURE

,- ·-=-- . I I'~

xiv Contents

1' ·--·-C'- • I r~

Part Ill Database Implementation and Use 205 An Overview of Part Ill 205

Chapter 5 Introduction to SQL 207 Learning Objectives 207

Introduction 207

Origins of t he SQL Standard 209

The SQL Environment 211

SQL Data Types 213

Def ining A Dat abase in SQL 216

Generating SQL Database Definitions 216

Creating Tables 217

Creating Data Integrity Controls 220

Changing Table Definitions 221

Removing Tables 221

Inserting, Updating, and Deleting Data 222

Bat ch Input 223

Deleting Database Contents 223

Updating Database Contents 224

Internal Schema Definition in RDBMSs 225

Creating Indexes 225

Processing Single Tables 226

Clauses of the SELECT Statement 226

Using Expressions 228

Using Functions 229

Using Wildcards 232

Using Comparison Operators 232

Using Null Values 233

Using Boolean Operators 233

Using Ranges for Qualif ication 236

Using Distinct Values 236

Using IN and NOT IN w ith Lists 238

Sorting Results: The ORDER BY Clause 239

Cat egorizing Results: The GROUP BY Clause 240

Qualifying Results by Cat egories: The HAVING Clause 241 Summary 243 • Key Terms 243 • Review Questions 243 • Problems and Exercises 244 • Field Exercises 248 • References 248 • Further Reading 249 • Web Resources 249

"" CASE: Forondo Artist Management Excellence Inc. 250

Chapter 6 Advanced SQL 251 Learning Objectives 251

Introduction 251

Processing Multiple Tables 252

Equi-Join 253

Natura l Join 254

Outer Join 255

Sample Join Involving Four Tables 257

Self-Join 258

Subqueries 260

Correlated Subqueries 265

Using Derived Tables 267

Combinings Queries 267

Conditional Expressions 269

More Complicated SQL Queries 270

Tips for Developing Queries 272

Guidelines for Better Query Design 274

Using and Defining Views 275

Materialized Views 279

Triggers and Routines 279

Triggers 280

Routines and Other Programming Extensions 282

Example Routine in Oracle's PUSQL 284

Data Dictionary Faci lities 285

Recent Enhancements and Extensions to SQL 287

Analytical and OLAP Functions 287

New Temporal Features in SQL 288

Other Enhancements 288 Summary 289 • Key Terms 290 • Review Questions 290 • Problems and Exercises 291 • Field Exercises 294 • References 294 • Further Reading 295 • Web Resources 295

.,. CASE: Forondo Artist Management Excellence Inc. 296

Chapter 7 Databases in Applications 297 Learning Objectives 297

Location, Location, Location! 297

Introduction 298

Cl ient/Server Architectures 298

Databases in Three-Tier Applications 302

A Java Web Application 303

A Python Web Application 307

Key Considerations in Three-Tier Applications 313

Stored Procedures 313

Transactions 313

Database Connections 315

Key Benefits of Three-Tier Applications 31 S

Transaction Integrity 316

Controlling Concurrent Access 318

The Problem of Lost Updates 318

Serial izabi lity 319

Locking Mechanisms 319 LOCKING LEVEL 319 TYPES OF LOCKS 320

DEADLOCK 321

MANAGING DEADLOCK 321

Versioning 322

Contents xv

xvi Contents

Managing Data Security in an Application Context 324

Threats t o Data Security 324

Est ablishing Client/Server Security 325 SERVER SECURITY 326

NETWORK SECURITY 326

Application Security Issues in Three-Tier Client/Server Environments 326 DATA PRIVACY 327

Summary 329 • Key Terms 329 • Review Questions 329 • Problems and Exercises 330 • Field Exercises 331 • References 331 • Further Reading 331 • Web Resources 331

,.. CASE: Forondo Artist Management Excellence Inc. 332

Chapter 8 Physical Database Design and Database Infrastructure 333 Learning Objectives 333

Introduction 334

The Physical Database Design Process 335

Who Is Responsible for Physical Database Design? 335

Physical Database Design as a Basis for Regulatory Compliance 336

SOX and Databases 337 IT CHANGE MANAGEMENT 337

LOGICAL ACCESS TO DATA 337

IT OPERATIONS 338

Data Volume and Usage Analysis 338

Designing Fields 340

Choosing Data Types 340 CODING TECHN IQUES 341

CONTROLLING DATA INTEGRITY 342

HANDLING M ISSING DATA 343

Denormalizing and Partitioning Data 343

Denormalization 343 OPPORTUNITIES FOR AND TYPES OF DENORMALIZATION 344

DENORMALIZE W ITH CAUTION 345

Partitioning 347

Designing Physical Database Files 348

Fi le Organizations 350 HEAP FILE ORGANIZATION 350

SEQUENTIAL FILE ORGANIZATIONS 350

INDEXED FILE ORGANIZATIONS 352

HASHED f lLE ORGANIZATIONS 353

Clustering Files 353

Designing Controls for Files 354

Using and Selecting Indexes 354

Creating a Unique Key Index 354

Creating a Secondary (Nonunique) Key Index 355

When t o Use Indexes 355

Designing a Database for Opt imal Query Performance 356

Parallel Query Processing 357

Overriding Automatic Query Optimizat ion 358

Data Dictionaries and Repositories 358

Data Dictionary 359

Repositories 359

Database Software Data Security Features 361

Views 361

Integrity Controls 362

Authorization Rules 363

User-Defined Procedures 365

Encryption 365

Authent ication Schemes 365 PASSWORDS 366 STRONG AUTHENTICATION 366

Database Backup and Recovery 367

Basic Recovery Facilities 367 BACKUP FACILITIES 367

JOURNALIZING FACILITIES 368 CHECKPOINT FACILITY 368

RECOVERY MANAGER 369

Recovery and Rest art Procedures 369 D ISK M IRRORING 369

RESTORE/RERUN 370

BACKWARD RECOVERY 370

FORWARD RECOVERY 371

Types of Database Failure 371 ABORTED TRANSACTIONS 372 INCORRECT DATA 372 SYSTEM FAILURE 372

DATABASE DESTRUCTION 372

Disaster Recovery 373

Cloud-Based Database Infrastructure 373

Cloud-Based Models for Providing Data Management Services 373

Benefits and Downsides of Using Cloud-Based Data Management Services 374 Summary 375 • Key Terms 376 • Review Questions 377 • Problems and Exercises 378 • Field Exercises 382 • References 383 • Further Reading 383 • Web Resources 383

... CASE: Forondo Artist Management Excellence Inc. 384

Part IV Advanced Database Topics 385 An Overview of Part IV 385

Chapter 9 Data Warehousing and Data Integration 387 Learning Objectives 387

Introduction 387

Basic Concepts of Data Warehousing 390

A Brief History of Data Warehousing 390

The Need for Data Warehousing 390 N EED FOR A COMPANY- W IDE V IEW 390

N EED TO SEPARATE OPERATIONAL AND INFORMATIONAL SYSTEMS 393

Data Warehouse Architectures 393

;::-!. . ' • •

rz

Contents xv11

xv111 Contents

Independent Data Mart Data Warehousing Environment 394

Dependent Data Mart and Operational Data Store Architecture: A Three-Level Approach 395

Logical Data Mart and Real-Time Data Warehouse Architecture 397

Three-Layer Data Architecture 400 ROLE OF THE ENTERPRISE DATA MODEL 400 ROLE OF METADATA 400

Some Characteristics of Dat a Warehouse Data 401

Status versus Event Data 401

Transient versus Periodic Data 402

An Example of Transient and Periodic Data 402 TRANSIENT DATA 404

PERIODIC DATA 404 OTHER DATA WAREHOUSE CHANGES 404

The Derived Data Layer 405

Characteristics of Derived Data 405

The Star Schema 406 FACT TABLES AND D IMENSION TABLES 406

EXAMPLE STAR SCHEMA 407 SURROGATE Kev 408

GRAIN OF THE FACT TABLE 409

DURATION OF THE DATABASE 410 SIZE OF THE FACT TABLE 410

MODELING DATE AND T IME 41 1

Variations of the St ar Schema 412 MULTIPLE FACT TABLES 412 FACTLESS FACT TABLES 413

Normalizing Dimension Tables 414 MULTIVALUED DIMENSIONS 414

HIERARCHIES 415

Slowly Changing Dimensions 417

Determining Dimensions and Facts 420

Data Integration: An Overview 422

General Approaches to Dat a Integration 422 DATA FEDERATION 423 DATA PROPAGATION 423

Data Integration for Data Warehousing: The Reconci led Dat a Layer 424

Characteristics of Data after ETL 424

The ETL Process 425 MAPPING AND METADATA MANAGEMENT 425 EXTRACT 426

CLEANSE 427 LOAD AND INDEX 429

Data Transformation 430

Data Transformation Functions 431 RECORD- LEVEL FUNCTIONS 431 FIELD-LEVEL FUNCTIONS 432

Data Warehouse Administrat ion 434

The Future of Data Warehousing: Integration with Other Forms of Data Management and Analytics 434

Speed of Processing 435 Moving the Data Warehouse into the Cloud 435

Dealing w ith Unstructured Data 436 Summary 436 • Key Terms 437 • Review Questions 437 • Problems and Exercises 438 • Field Exercises 442 • References 442 • Further Reading 443 • Web Resources 443

Chapter 10 Big Data Technologies 444 Learning Objectives 444

Introduction 444

Moving Beyond Transactional and Data Warehousing Databases 446

Big Data 446

NoSQL 448 Classif ication of NoSQL DBMSs 450

KEY·VALUE STORES 450

DOCUMENT STORES 451

WIDE· COLUMN STORES 451

G RAPH-O RIENTED DATABASES 451

NoSQL Examples 451 REDIS 451

MONGODB 452

A PACHE CASSANDRA 452

NE04J 452

A NoSQL Example: MongoDB 452 DOCUMENTS 452

COLLECTIONS 454

RELATIONSH IPS 454

QUERYING MONGODB 455

Impact of NoSQL on Database Professionals 456

Hadoop 458

Components of Hadoop 459 THE HADOOP DISTRIBUTED FILE SYSTEM (HDFS) 459

MAPREDUCE 459

PIG 461

HIVE 461

HBASE 462

A Practical Introduction to Pig 462 LOADING DATA 462

TRANSFORMING DATA 463

A Practical Introduction to Hive 465 CREATING A TABLE 465

LOADING DATA INTO THE TABLE 465

PROCESSING THE DATA 466

Integrated Analytics and Data Science Platforms 466 HP H AVEN 466

TERADATA ASTER 467

IBM BIG DATA PLATFORM 469

Contents xix

xx Contents

Putting It Al l Together: Int egrated Data Architecture 469 Summary 471 • Key Terms 471 • Review Questions 471 • Problems and Exercises 472 • References 472 • Further Reading 473 • Web Resources 473

Chapter 11 Analytics and Its Implications 474 Learning Objectives 474

Introduction 474

Analytics 475

Types of Analytics 475

Use of Descript ive Analytics 477 SQL OLAP QUERYING 478 OLAP TOOLS 480 DATA V ISUALIZATION 482

BUSINESS PERFORMANCE MANAGEMENT AND DASHBOARDS 483

Use of Predictive Analytics 484 DATA M INING TOOLS 485 EXAMPLES OF PREDICTIVE ANALYTICS 486

Use of Prescript ive Analytics 487

Key User Tools for Analytics 488 ANALYTICAL AND OlAP FUNCTIONS 489

R 490 PYTHON 491

APACHE SPARK 492

Data Management Inf rastructure for Analytics 493

Impact of Big Data and Analytics 495

Applications of Big Data and Analytics 495 BUSINESS 496 E-GOVERNM ENT AND POLITICS 496

SCIENCE AND TECHNOLOGY 496

SMART HEALTH AND WELL-B EING 497 SECURITY AND PUBLIC SAFETY 497

Implicat ions of Big Data Analytics and Decision Making 497 PERSONAL PRIVACY VERSUS COLLECTIVE BEN EFITS 498

OWNERSHIP AND Access 498

QUALITY AND R EUSE OF DATA AND A LGORITHMS 498 TRANSPARENCY AND VALIDATION 498

(HANGING N ATURE OF WORK 499 DEMANDS FOR WORKFORCE CAPABILITIES AND EDUCATION 499

Summary 499 • Key Terms 500 • Review Questions 500 • Problems and Exercises 500 • References 501 • Further Reading 502

Chapter 12 Data and Database Administration w ith Focus on Data Quality 503

Learning Objectives 503

Introduction 503

Overview of Data and Database Administration 505

Data Administ rat ion 505

Database Administration 506

TRADITIONAL DATABASE ADMINISTRATION 506

TRENDS IN DATABASE ADMINISTRATION 508

Evolving Data Administration Roles 510

The Open Source Movement and Database Management 511 Data Governance 512

Managing Data Quality 513 Characteristics of Quality Data 514

EXTERNAL DATA SOURCES 515 REDUNDANT D ATA STORAGE AND INCONSISTENT METADATA 516 DATA ENTRY PROBLEMS 516 LACK OF ORGANIZATIONAL COMMITMENT 516

Data Quality Improvement 516 GET THE BUSINESS 8UY· IN 516 CONDUCT A DATA QUALITY AUDIT 517 ESTABLISH A DATA STEWARDSHIP PROGRAM 518

IMPROVE DATA CAPTURE PROCESSES 518 APPLY MODERN DATA MANAGEMENT PRINCIPLES AND TECHNOLOGY 519

APPLY TQM PRINCIPLES AND PRACTICES 519

Summary of Data Quality 519

Data Availabil ity 520

Costs of Downtime 520 Measures to Ensure Availabil ity 521

HARDWARE FAILURES 521 Loss OR CORRUPTION OF D ATA 521

HUMAN ERROR 521 MAINTENANCE DOWNTIME 521 N ETWORK-RELATED PROBLEMS 521

Master Data Management 521 Summary 523 • Key Terms 523 • Review Questions 524 • Problems and Exercises 524 • Field Exercises 526 • References 526 • Further Reading 527 • Web Resources 527

Glossary of Acronyms 529

Glossary of Terms 531

Index 539

Contents 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

N EAR-REAL-TIME REPLICATION 13·8

PULL REPLICATION 13-8

DATABASE INTEGRITY W ITH REPLICATION 13-8

WHEN TO Use REPLICATION 13-9

Horizontal Partitioning 13-9

Vertical Partitioning 13-10

Combinations of Operations 13-11

Selecting the Right Data Distribution Strategy 13-12

Distributed DBMS 13-13

Location Transparency 13-1 S

Replication Transparency 13-16

Failure Transparency 13-17

Commit Protocol 13-17

Concurrency Transparency 13-18 TIME STAMPING 13-19

Query Optimization 13-19

Evolution of Distributed DBMSs 13-22 REMOTE UNIT OF WORK 13-22

D ISTRIBUTED UNIT OF WORK 13-22

D ISTRIBUTED REQUEST 13-23

Summary 13-23 • Key Terms 13-24 • Review Questions 13-24 • Problems and Exercises 13-25 • Field Exercises 13-27 • 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

Interpret ing Inheritance and Overriding 14-18

Representing Multiple Inheritance 14-19

Representing Aggregation 14-19

Business Rules 14-22

www.pearsonhighered.com/ hoffer xx111

Object Modeling Example: Pine Valley Furniture Company 14-23 Summary 14-25 • Key Terms 14-26 • Review Questions 14-26 • 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 Professiona I 2016 Notation A-1 ENTITIES A-5