Modern database management 13th edition free

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