Database Processing Fundamentals, Design, and Implementation EDITION 13 This page intentionally left blank Database Processing Fundamentals, Design, and Implementation E D I TION 1 3 David M. Kroenke David J. Auer Western Washington University Boston Columbus Indianapolis New York San Francisco Upper Saddle River 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 Editor in Chief: Stephanie Wall Senior Art Director: Janet Slowik Executive Editor: Bob Horan Interior and Cover Designer: Karen Quigley Program Manager: Kelly Loftus Cover Images: Shutterstock Inc. Editorial Assistant: Kaylee Rotella Media Project Manager, Editorial: Denise Vaughn Director of Marketing: Maggie Moylan Media Project Manager, Production: Lisa Rinaldi Executive Marketing Manager: Anne Fahlgren Composition/Full-Service Project Management: Marketing Assistant: Gianna Sandri Integra Senior Managing Editor: Judy Leale Printer/Binder: R.R. Donnelley/Willard Production Project Manager: Jane Bonnell Cover Printer: Lehigh-Phoenix Color/Hagerstown Operations Specialist: Michelle Klein Text Font: 10/12 Kepler Standard Light 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. Microsoft- and 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. MySQL-, the MySQL GUI Tools- (MySQL Query Browser- and MySQL Administrator-), the MySQL Command Line Client-, the MySQL Workbench-, and the MySQL Connector/ODBC- are registered trademarks of Sun Microsystems, Inc./Oracle Corporation. Screenshots and icons reprinted with permission of Oracle Corporation. This book is not sponsored or endorsed by or affiliated with Oracle Corporation. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Eclipse- and The Eclipse PHP Development Tools (PDT) Project- are trademarks of the Eclipse Foundation, Inc. The Eclipse platform is copyright Eclipse copyright holders and others, 2000, 2007. Screenshots reprinted under the terms of the Eclipse Public License v1.0 available at www.eclipse.org/legal/epl-v10.html. This book is not sponsored or endorsed by or affiliated with the Eclipse Foundation, Inc. PHP is copyright The PHP Group 1999–2008, and is used under the terms of the PHP Public License v3.01 available at http://www.php.net/license/3_01.txt. This book is not sponsored or endorsed by or affiliated with The PHP Group. Copyright © 2014, 2012, 2010 by Pearson Education, Inc., One Lake Street, Upper Saddle River, New Jersey 07458. 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. To obtain permission(s) to use material from this work, please submit a written request to Pearson Education, Inc., Permissions Department, One Lake Street, Upper Saddle River, New Jersey 07458, or you may fax your request to 201-236-3290. Many of the designations by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations have been printed in initial caps or all caps. Library of Congress Cataloging-in-Publication Data Kroenke, David M. Database processing : fundamentals, design, and implementation.—Edition 13/David M. Kroenke, David J. Auer, Western Washington University. pages cm ISBN 978-0-13-305835-2 1. Database management. I. Auer, David J. II. Title. QA76.9.D3K76 2013 005.74—dc23 2013020780 10 9 8 7 6 5 4 3 2 1 ISBN 10: 0-13-305835-2 ISBN 13: 978-0-13-305835-2 Brief Contents Part 1 Getting Started 1 ● Chapter 1 Introduction 2 Chapter 2 Introduction to Structured Query Language 33 Part 2 Database Design 117 ● Chapter 3 The Relational Model and Normalization 118 Chapter 4 Database Design Using Normalization 160 Chapter 5 Data Modeling and the Entity-Relationship Model 179 Chapter 6 Transforming Data Models into Database Designs 230 Part 3 Database Implementation 277 ● Chapter 7 SQL for Database Construction and Application Processing 278 Chapter 8 Database Redesign 366 Part 4 Multiuser Database Processing 393 ● Chapter 9 Managing Multiuser Databases 394 Chapter 10 Managing Databases with SQL Server 2012, Oracle Database 11g Release 2, and MySQL 5.6 428 Online Chapter: See Page 433 for Instructions Chapter 10A Managing Databases with SQL Server 2012 Online Chapter: See Page 433 for Instructions Chapter 10B Managing Databases with Oracle Database 11g Release 2 Online Chapter: See Page 433 for Instructions Chapter 10C Managing Databases with MySQL 5.6 Part 5 Database Access Standards 435 ● Chapter 11 The Web Server Environment 436 Chapter 12 Big Data, Data Warehouses, and Business Intelligence Systems 534 Online Appendices: See Page 580 for Instructions Appendix A Getting Started with Microsoft Access 2013 Appendix B Getting Started with Systems Analysis and Design Appendix C E-R Diagrams and the IDEF1X Standard Appendix D E-R Diagrams and the UML Standard Appendix E Getting Started with MySQL Workbench Data Modeling Tools Appendix F Getting Started with Microsoft Visio 2013 Appendix G Data Structures for Database Processing Appendix H The Semantic Object Model Appendix I Getting Started with Web Servers, PHP, and the Eclipse PDT Appendix J Business Intelligence Systems v This page intentionally left blank Contents Preface xix Part 1 ● Getting Started 1 Chapter 1: Introduction 2 Chapter Objectives 2 The Characteristics of Databases 3 A Note on Naming Conventions 4 • A Database Has Data and Relationships 4 • Databases Create Information 5 Database Examples 6 Single-User Database Applications 6 • Multiuser Database Applications 6 • E-Commerce Database Applications 7 • Reporting and Data Mining Database Applications 7 The Components of a Database System 8 Database Applications and SQL 9 • The DBMS 11 • The Database 12 Personal Versus Enterprise-Class Database Systems 14 What Is Microsoft Access? 14 • What Is an Enterprise-Class Database System? 15 Database Design 17 Database Design from Existing Data 17 • Database Design for New Systems Development 18 • Database Redesign 19 What You Need to Learn 20 A Brief History of Database Processing 21 The Early Years 21 • The Emergence and Dominance of the Relational Model 23 • Post-Relational Developments 24 Summary 26 • Key Terms 27 • Review Questions 27 • Project Questions 29 Chapter 2: Introduction to Structured Query Language  33 Chapter Objectives 33 Components of a Data Warehouse 34 Cape Codd Outdoor Sports 35 The Extracted Retail Sales Data 35 • RETAIL_ORDER Data 36 • ORDER_ITEM Data 37 • SKU_DATA Table 38 • The Complete Cape Codd Data Extract Schema 38 • Data Extracts Are Common 39 SQL Background 39 The SQL SELECT/FROM/WHERE Framework 40 Reading Specified Columns from a Single Table 40 • Specifying Column Order in SQL Queries from a Single Table 41 • Reading Specified Rows from a Single Table 43 • Reading Specified Columns and Rows from a Single Table 44 Submitting SQL Statements to the DBMS 45 Using SQL in Microsoft Access 2013 45 • Using SQL in Microsoft SQL Server 2012 50 • Using SQL in Oracle Database 11g Release 2 53 • Using SQL in Oracle MySQL 5.6 55 vii viii Contents SQL Enhancements for Querying a Single Table 58 Sorting the SQL Query Results 58 • SQL WHERE Clause Options 60 • Combing the SQL WHERE Clause and the SQL ORDER BY Clause 65 Performing Calculations in SQL Queries 65 Using SQL Built-in Functions 65 • SQL Expressions in SQL SELECT Statements 68 Grouping in SQL SELECT Statements 70 Looking for Patterns in NASDAQ Trading 74 Investigating the Characteristics of the Data 74 • Searching for Patterns in Trading by Day of Week 75 Querying Two or More Tables with SQL 77 Querying Multiple Tables with Subqueries 77 • Querying Multiple Tables with Joins 80 • Comparing Subqueries and Joins 85 • The SQL JOIN ON Syntax 85 • Outer Joins 87 Summary 91 • Key Terms 91 • Review Questions 92 • Project  Questions 97 • Case Questions 101 • The Queen Anne Curiosity  Shop 106 • Morgan Importing 113 Part 2 ● Database Design 117 Chapter 3: The Relational Model and Normalization  118 Chapter Objectives 118 Relational Model Terminology 120 Relations 120 • Characteristics of Relations 121 • Alternative Terminology 123 • Functional Dependencies 124 • Finding Functional Dependencies 126 • Keys 129 Normal Forms 131 Modification Anomalies 131 • A Short History of Normal Forms 132 • Normalization Categories 133 • From First Normal Form to Boyce-Codd Normal Form Step by Step 134 • Eliminating Anomalies from Functional Dependencies with BCNF 137 • Eliminating Anomalies from Multivalued Dependencies 147 • Fifth Normal Form 151 • Domain/Key Normal Form 151 Summary 151 • Key Terms 152 • Review Questions 153 • Project  Questions 155 • Case Questions 156 • The Queen Anne Curiosity  Shop 157 • Morgan Importing 159 Chapter 4: Database Design Using Normalization  160 Chapter Objectives 160 Assess Table Structure 161 Designing Updatable Databases 162 Advantages and Disadvantages of Normalization 162 • Functional Dependencies 163 • Normalizing with SQL 163 • Choosing Not to Use BCNF 164 • Multivalued Dependencies 165 Designing Read-Only Databases 165 Denormalization 166 • Customized Duplicated Tables 167 Common Design Problems 167 The Multivalue, Multicolumn Problem 168 • Inconsistent Values 170 • Missing Values 171 • The General-Purpose Remarks Column 172 Summary 173 • Key Terms 173 • Review Questions 174 • Project  Questions 176 • Case Questions 176 • The Queen Anne Curiosity  Shop 177 • Morgan Importing 178 Contents ix Chapter 5: Data Modeling with the Entity-Relationship Model  179 Chapter Objectives 179 The Purpose of a Data Model 180 The Entity-Relationship Model 180 Entities 181 • Attributes 181 • Identifiers 181 • Relationships 182 • Maximum Cardinality 184 • Minimum Cardinality 185 • Entity- Relationship Diagrams and Their Versions 186 • Variations of the E-R Model 186 • E-R Diagrams Using the IE Crow’s Foot Model 187 • Strong Entities and Weak Entities 189 • ID-Dependent Entities 189 • Non-ID-Dependent Weak Entities 190 • The Ambiguity of the Weak Entity 191 • Subtype Entities 191 Patterns in Forms, Reports, and E-R Models 193 Strong Entity Patterns 194 • ID-Dependent Relationships 196 • Mixed Identifying and Nonidentifying Patterns 203 • The For-Use-By Pattern 205 • Recursive Patterns 207 The Data Modeling Process 209 The College Report 210 • The Department Report 211 • The Department/Major Report 213 • The Student Acceptance Letter 214 Summary 216 • Key Terms 217 • Review Questions 217 • Project  Questions 219 • Case Questions 225 • The Queen Anne Curiosity  Shop 228 • Morgan Importing 229 Chapter 6: Transforming Data Models into Database Designs  230 Chapter Objectives 230 The Purpose of a Database Design 231 Create a Table for Each Entity 231 Selecting the Primary Key 232 • Specifying Candidate (Alternate) Keys 233 • Specify Column Properties 234 • Verify Normalization 236 Create Relationships 236 Relationships Between Strong Entities 237 • Relationships Using ID- Dependent Entities 240 • Relationships with a Weak Non-ID-Dependent Entity 245 • Relationships in Mixed Entity Designs 245 • Relationships Between Supertype and Subtype Entities 247 • Recursive Relationships 247 • Representing Ternary and Higher-Order Relationships 249 • Relational Representation of the Highline University Data Model 252 Design for Minimum Cardinality 253 Actions When the Parent Is Required 255 • Actions When the Child Is Required 256 • Implementing Actions for M-O Relationships 256 • Implementing Actions for O-M Relationships 257 • Implementing Actions for M-M Relationships 258 • Designing Special Case M-M Relationships 258 • Documenting the Minimum Cardinality Design 259 • An Additional Complication 261 • Summary of Minimum Cardinality Design 261 The View Ridge Gallery Database 261 Summary of Requirements 261 • The View Ridge Gallery Data Model 262 • Database Design with Data Keys 263 • Minimum Cardinality Enforcement for Required Parents 264 • Minimum Cardinality Enforcement for the Required Child 265 • Column Properties for the View Ridge Gallery Database Design Tables 268 Summary 269 • Key Terms 270 • Review Questions 270 • Project  Questions 272 • Case Questions 273 • The Queen Anne Curiosity  Shop 275 • Morgan Importing 275