Modern database management 13th edition 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