database-systems-design-implementation-amp-management-14nbsped-0357673034-9780357673034-9780357673072_compress.pdf

Carlos Coronel Steven Morris Database Systems Design, Implementation, & Management 14TH Edition Carlos Coronel Steven Morris Database Systems Design, Implementation, & Management...

Carlos Coronel Steven Morris Database Systems Design, Implementation, & Management 14TH Edition Carlos Coronel Steven Morris Database Systems Design, Implementation, & Management 14TH Edition Australia Brazil Canada Mexico Singapore United Kingdom United States Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  This is an electronic version of the print textbook. Due to electronic rights restrictions, some third party content may be suppressed. Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. The publisher reserves the right to remove content from this title at any time if subsequent rights restrictions require it. For valuable information on pricing, previous editions, changes to current editions, and alternate formats, please visit www.cengage.com/highered to search by ISBN#, author, title, or keyword for materials in your areas of interest. Important Notice: Media content referenced within the product description or the product text may not be available in the eBook version. Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Database Systems: Design, Implementation, © 2023, 2019, 2015 Cengage Learning, Inc. ALL RIGHTS RESERVED. and Management, 14th Edition WCN: 02-300 Carlos Coronel and Steven Morris No part of this work covered by the copyright herein may be reproduced SVP, Higher Education Product Management: Erin or distributed in any form or by any means, except as permitted by U.S. Joyner copyright law, without the prior written permission of the copyright owner. VP, Product Management, Learning Experiences: Thais Alencar Unless otherwise noted, all content is Copyright © Cengage Learning, Inc. Product Director: Mark Santee All screenshots, unless otherwise noted, are used with permission from Microsoft Corporation. Microsoft® is a registered trademark of the Product Manager: Natalie Onderdonk Microsoft Corporation. Product Assistant: Ethan Wheel Learning Designers: Mary Clyne and Carolyn For product information and technology assistance, contact us at Mako Cengage Customer & Sales Support, 1-800-354-9706 Sr. Content Managers: Tim Bailey and Kara or support.cengage.com. DiCaterino Digital Project Manager: Jim Vaughey For permission to use material from this text or product, submit all requests online at www.copyright.com. Technical Editor: Danielle Shaw Developmental Editor: Lisa Ruffolo Library of Congress Control Screenshots for this book were created using VP, Product Marketing: Jason Sakos Microsoft Access® and Excel® and were used with permission from Director, Product Marketing: Danaë April Microsoft. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other Portfolio Marketing Manager: Mackenzie Paine countries. IP Analyst: Ann Hoffman Oracle is a registered trademark, and Oracle12 c and MySQL are trademarks IP Project Manager: Anjali Kambli, Lumina Datamatics of Oracle Corporation. IP Photo/Text Researcher: Manoranjini Library of Congress Control Number: 2022912918 Boomiappan, Lumina Datamatics ISBN: 978-0-357-67303-4 Production Service: Straive Loose Leaf Edition ISBN: 978-0-357-67307-2 Sr. Designer: Erin Griffin Cover Image Source: Cengage ConnectVector/Shutterstock.com 200 Pier 4 Boulevard Boston, MA 02210 USA Cengage is a leading provider of customized learning solutions with employees residing in nearly 40 different countries and sales in more than 125 countries around the world. Find your local representative at www.cengage.com. To learn more about Cengage platforms and services, register or access your online learning solution, or purchase materials for your course, visit www.cengage.com. Notice to the Reader Publisher does not warrant or guarantee any of the products described herein or perform any independent analysis in connection with any of the product information contained herein. Publisher does not assume, and expressly disclaims, any obligation to obtain and include information other than that provided to it by the manufacturer. The reader is expressly warned to consider and adopt all safety precautions that might be indicated by the activities described herein and to avoid all potential hazards. By following the instructions contained herein, the reader willingly assumes all risks in connection with such instructions. The publisher makes no representations or warranties of any kind, including but not limited to, the warranties of fitness for particular purpose or merchantability, nor are any such representations implied with respect to the material set forth herein, and the publisher takes no responsibility with respect to such material. The publisher shall not be liable for any special, consequential, or exemplary damages resulting, in whole or part, from the readers’ use of, or reliance upon, this material. Printed in the United States of America Print Number: 01 Print Year: 2022 Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Dedication To the treasures in my life: To Victoria, for 31 wonderful years. Thank you for your unending support and for being my angel, my sweetie, and, most importantly, my best friend. To Carlos Anthony, who has become a remarkable man, pride of his father, and husband to our beautiful, sweet, and brilliant daughter-in-law, Jered. Thank you for your words of wisdom, hard-working attitude, and for giving us reasons to be happy. You are still young; your best times are still to come. To Cerila Reyan, our beautiful granddaughter, whose smiles give us so much hope and happiness. To Gabriela Victoria, who is the image of brilliance, beauty, and faithfulness. The way you give your time and talents in the service of others is an inspiration to all of us. Thank you for being my sunshine on cloudy days. Your future is bright and endless. To Christian Javier, who is smarter than all of us. Thank you for being the youthful reminder of life’s simple beauties. Keep challenging yourself to new highs and keep working hard to achieve your dreams. To my parents, Sarah and Carlos, thank you for your sacrifice and example. To all of you, you are all my inspiration. “TQTATA.” Carlos Coronel To Pamela, from high school sweetheart through nearly 30 years of marriage, you are the beautiful love of my life who has supported, encouraged, and inspired me. More than anyone else, you are responsible for whatever successes I have achieved. To my son, Alexander, your depth of character is without measure. You are my pride and joy. To my daughter, Lauren, your beauty and intensity take my breath away. You are my heart and soul. To my daughter-in-law, Blakley, whom I could not love more if you were my flesh and blood. To my granddaughter, Daphne, a bundle of joy brought into a world that does not deserve someone so precious. To my mother, Florence, and to the memory of my father, Alton, together they instilled in me the desire to learn and the passion to achieve. To my mother-in-law, Connie, and to the memory of my father-in-law, Wayne, they taught me to find joy in all things. To all of you, with all my love, I dedicate this book. Steven Morris For Peter To longtime colleague and friend, Peter Rob: Your drive and dedication to your students started this book. Your depth of knowledge, attention to detail, and pursuit of excellence made it suc- ceed. Your patience and guidance continue to light our path. It is our sincere hope that, as we move forward, we can continue to live up to your standard. Enjoy your retirement, my friend; you have surely earned it. Carlos Coronel and Steven Morris iii Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Brief Contents Preface, xiv Text Features, xx Additional Features, xxii Acknowledgments, xxiv Part 1 Database Concepts 2 Chapter 1 Database Systems, 3 Chapter 2 Data Models, 33 Part 2 Design Concepts 64 Chapter 3 The Relational Database Model, 65 Chapter 4 Entity Relationship (ER) Modeling, 108 Chapter 5 Advanced Data Modeling, 162 Chapter 6 Normalization of Database Tables, 192 Part 3 Advanced Design and Implementation 238 Chapter 7 Introduction to Structured Query Language (SQL), 239 Chapter 8 Advanced SQL, 351 Chapter 9 Database Design, 431 Part 4 Advanced Database Concepts 472 Chapter 10 Transaction Management and Concurrency Control, 473 Chapter 11 Database Performance Tuning and Query Optimization, 506 Chapter 12 Distributed Database Management Systems, 539 Chapter 13 Business Intelligence and Data Warehouses, 573 Chapter 14 Big Data and NoSQL, 640 Part 5 Databases and the Internet 674 Chapter 15 Database Connectivity and Web Technologies, 675 Part 6 Database Administration 714 Chapter 16 Database Administration and Security, 715 Glossary, 760 Index, 775 iv Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Brief Contents v The following appendices are included on the Instructor and Student Resource Sites at www.cengage.com. Appendix A: Designing Databases with Lucidchart: A Tutorial Appendix B: The University Lab: Conceptual Design Appendix C: The University Lab: Conceptual Design Verification, Logical Design, and Implementation Appendix D: Converting an ER Model into a Database Structure Appendix E: Comparison of ER Modeling Notations Appendix F: Client/Server Systems Appendix G: Object-Oriented Databases Appendix H: Unified Modeling Language (UML) Appendix I: Databases in Electronic Commerce Appendix J: Web Database Development with ColdFusion Appendix K: The Hierarchical Database Model Appendix L: The Network Database Model Appendix M: MS Access Tutorial Appendix N: Creating a New Database Using Oracle Appendix O: Data Warehouse Implementation Factors Appendix P: Working with MongoDB Appendix Q: Working with Neo4j Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Contents Preface, xiv 2-3 Data Model Basic Building Blocks 36 2-4 Business Rules 37 Text Features, xx 2-4a Discovering Business Rules 37 Additional Features, xxii 2-4b Translating Business Rules into Data Model Acknowledgments, xxiv Components 38 2-4c Naming Conventions 39 2-5 The Evolution of Data Models 39 Part 1 2-5a Hierarchical and Network Models 39 2-5b The Relational Model 41 Database Concepts 2-5c The Entity Relationship Model 43 2-5d The Object-Oriented Model 45 Chapter 1 2-5e Object/Relational and XML 47 2-5f Emerging Data Models: Big Data and NoSQL 48 Database Systems 3 2-5g Data Models: A Summary 51 1-1 Why Databases? 4 2-6 Degrees of Data Abstraction 52 1-2 Data versus Information 5 2-6a The External Model 55 1-3 Introducing the Database 8 2-6b The Conceptual Model 56 1-3a Role and Advantages of the DBMS 8 2-6c The Internal Model 57 1-3b Types of Databases 10 2-6d The Physical Model 58 1-4 Why Database Design Is Important 12 Summary 59 1-5 Evolution of File System Data Processing 15 Key Terms 60 1-5a Manual File Systems 16 Review Questions 60 1-5b Computerized File Systems 16 Problems 61 1-5c File System Redux: Modern End-User Productivity Tools 18 1-6 Problems with File System Data Processing 18 Part 2 1-6a Structural and Data Dependence 19 1-6b Data Redundancy 20 1-6c Data Anomalies 21 Design Concepts 1-7 Database Systems 21 1-7a The Database System Environment 22 1-7b DBMS Functions 24 Chapter 3 1-7c Managing the Database System: A Shift in Focus 27 The Relational Database Model 65 1-8 Preparing for Your Database Professional Career 28 3-1 A Logical View of Data 67 Summary 29 3-1a Tables and Their Characteristics 67 Key Terms 29 3-2 Keys 69 Review Questions 30 3-2a Dependencies 69 Problems 31 3-2b Types of Keys 70 3-3 Integrity Rules 73 3-4 Relational Algebra 75 Chapter 2 3-4a Formal Definitions and Terminology 75 Data Models 33 3-4b Relational Set Operators 76 2-1 Data Modeling and Data Models 34 3-5 The Data Dictionary and the 2-2 The Importance of Data Models 35 System Catalog 84 vi Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Contents vii 3-6 Relationships within the Relational Database 86 5-1e Disjoint and Overlapping Constraints 167 3-6a The 1:M Relationship 86 5-1f Completeness Constraint 169 3-6b The 1:1 Relationship 88 5-1g Specialization and Generalization 170 3-6c The M:N Relationship 90 5-2 Entity Clustering 170 3-7 Data Redundancy Revisited 94 5-3 Entity Integrity: Selecting Primary Keys 171 3-8 Indexes 96 5-3a Natural Keys and Primary Keys 172 3-9 Codd’s Relational Database Rules 98 5-3b Primary Key Guidelines 172 Summary 98 5-3c When to Use Composite Primary Keys 172 Key Terms 99 5-3d When to Use Surrogate Primary Keys 174 Review Questions 99 5-4 Design Cases: Learning Flexible Database Design 175 Problems 102 5-4a Design Case 1: Implementing 1:1 Relationships 176 5-4b Design Case 2: Maintaining History of Time-Variant Chapter 4 Data 177 5-4c Design Case 3: Fan Traps 180 Entity Relationship (ER) Modeling 108 5-4d Design Case 4: Redundant Relationships 181 4-1 The Entity Relationship Model 109 Summary 182 4-1a Entities 110 Key Terms 182 4-1b Attributes 110 Review Questions 182 4-1c Relationships 116 Problems 183 4-1d Connectivity and Cardinality 116 Cases 185 4-1e Existence Dependence 119 4-1f Relationship Strength 119 4-1g Weak Entities 122 Chapter 6 4-1h Relationship Participation 124 Normalization of Database Tables 192 4-1i Relationship Degree 126 6-1 Database Tables and Normalization 193 4-1j Recursive Relationships 128 6-2 The Need for Normalization 194 4-1k Associative (Composite) Entities 132 6-3 The Normalization Process 197 4-2 Developing an ER Diagram 134 6-3a Conversion to First Normal Form (1NF) 198 4-3 Database Design Challenges: 6-3b Conversion to Second Normal Form (2NF) 202 Conflicting Goals 142 6-3c Conversion to Third Normal Form (3NF) 204 Summary 146 6-4 Improving the Design 206 Key Terms 147 6-5 Surrogate Key Considerations 210 Review Questions 147 6-6 Higher-Level Normal Forms 212 Problems 149 6-6a The Boyce-Codd Normal Form 212 Cases 155 6-6b Fourth Normal Form (4NF) 215 6-6c Fifth Normal Form (5NF) 217 Chapter 5 6-7 Normalization and Database Design 218 Advanced Data Modeling 162 6-8 Denormalization 222 5-1 The Extended Entity Relationship Model 163 6-9 Data-Modeling Checklist 225 5-1a Entity Supertypes and Subtypes 163 Summary 227 5-1b Specialization Hierarchy 164 Key Terms 227 5-1c Inheritance 165 Review Questions 228 5-1d Subtype Discriminator 167 Problems 229 Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. viii Contents Part 3 7-9d Multirow Subquery Operators: ALL and ANY 289 7-9e FROM Subqueries 290 Advanced Design and Implementation 7-9f Attribute List Subqueries 291 7-9g Correlated Subqueries 293 7-10 SQL Functions 296 Chapter 7 7-10a Date and Time Functions 297 Introduction to Structured Query 7-10b Numeric Functions 300 Language (SQL) 239 7-10c String Functions 300 7-1 SQL Basics 240 7-10d Conversion Functions 302 7-1a Data Types 241 7-11 Relational Set Operators 304 7-1b SQL Queries 242 7-11a UNION 305 7-1c The Database Model 243 7-11b UNION ALL 306 7-2 Basic SELECT Queries 244 7-11c INTERSECT 307 7-3 SELECT Statement Options 245 7-11d EXCEPT (MINUS) 308 7-3a Using Column Aliases 246 7-11e Syntax Alternatives 309 7-3b Using Computed Columns 247 7-12 Crafting SELECT Queries 310 7-3c Arithmetic Operators: 7-12a Know Your Data 310 The Rule of Precedence 248 7-12b Know the Problem 310 7-3d Date Arithmetic 249 7-12c Build One Clause at a Time 311 7-3e Listing Unique Values 250 Summary 312 7-4 FROM Clause Options 251 Key Terms 313 7-5 ORDER BY Clause Options 252 Review Questions 314 7-6 WHERE Clause Options 254 Problems 315 7-6a Selecting Rows with Conditional Restrictions 255 7-6b Using Comparison Operators on Character Attributes 257 Chapter 8 7-6c Using Comparison Operators on Dates 258 Advanced SQL 351 7-6d Logical Operators: AND, OR, and NOT 258 8-1 Data Definition Commands 352 7-6e Special Operators 260 8-1a Starting Database Model 352 7-7 JOIN Operations 264 8-1b Creating the Database 354 7-7a Natural Join 265 8-1c The Database Schema 354 7-7b JOIN USING Syntax 267 8-1d Data Types 355 7-7c JOIN ON Syntax 268 8-2 Creating Table Structures 358 7-7d Common Attribute Names 269 8-2a CREATE TABLE command 358 7-7e Old-Style Joins 269 8-2b SQL Constraints 362 7-7f Outer Joins 271 8-2c Creating a Table with a SELECT Statement 365 7-7g Cross Join 274 8-2d SQL Indexes 366 7-7h Joining Tables with an Alias 275 8-3 Altering Table Structures 367 7-7i Recursive Joins 275 8-3a Changing a Column’s Data Type 368 7-8 Aggregate Processing 277 8-3b Changing a Column’s Data Characteristics 368 7-8a Aggregate Functions 277 8-3c Adding a Column 368 7-8b Grouping Data 281 8-3d Adding Primary Key, Foreign Key, and Check 7-8c HAVING Clause 284 Constraints 369 7-9 Subqueries 286 8-3e Dropping a Column 369 7-9a WHERE Subqueries 287 8-3f Deleting a Table from the Database 370 7-9b IN Subqueries 288 8-4 Data Manipulation Commands 370 7-9c HAVING Subqueries 289 8-4a Adding Table Rows 370 Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Contents ix 8-4b Inserting Table Rows with a SELECT Subquery 372 9-5 DBMS Software Selection 458 8-4c Saving Table Changes 374 9-6 Logical Design 459 8-4d Updating Table Rows 374 9-6a Map the Conceptual Model to the Logical Model 8-4e Deleting Table Rows 377 Components 459 8-4f Restoring Table Contents 378 9-6b Validate the Logical Model Using Normalization 461 8-5 Virtual Tables: Creating a View 378 9-6c Validate the Logical Model Integrity Constraints 461 8-5a Updatable Views 380 9-6d Validate the Logical Model against User Requirements 462 8-6 Auto Increment, Identity, and Sequences 381 9-7 Physical Design 462 8-7 Procedural SQL 387 9-7a Define Data Storage Organization 463 8-7a Stored Procedures 389 9-7b Define Integrity and Security Measures 463 8-7b Working with Variables 391 9-7c Determine Performance Measurements 464 8-7c Conditional Execution 392 9-8 Database Design Strategies 464 8-7d Iteration or Looping 393 9-9 Centralized versus Decentralized Design 465 8-7e SELECT Processing with Cursors 396 Summary 468 8-7f Stored Procedures with Parameters 399 Key Terms 468 8-7g Triggers 401 Review Questions 468 8-7h User Defined Functions 412 Problems 469 8-8 Embedded SQL 412 Summary 417 Key Terms 418 Review Questions 418 Part 4 Problems 418 Advanced Database Concepts Cases 425 Chapter 10 Chapter 9 Transaction Management and Database Design 431 Concurrency Control 473 9-1 The Information System 432 10-1 What Is a Transaction? 474 9-2 The Systems Development Life Cycle 434 10-1a Evaluating Transaction Results 476 9-2a Planning 434 10-1b Transaction Properties 478 9-2b Analysis 436 10-1c Transaction Management with SQL 479 9-2c Detailed Systems Design 436 10-1d The Transaction Log 480 9-2d Implementation 437 10-2 Concurrency Control 481 9-2e Maintenance 437 10-2a Lost Updates 481 9-3 The Database Life Cycle 437 10-2b Uncommitted Data 482 9-3a The Database Initial Study 438 10-2c Inconsistent Retrievals 483 9-3b Database Design 442 10-2d The Scheduler 484 9-3c Implementation and Loading 444 10-3 Concurrency Control with Locking Methods 486 9-3d Testing and Evaluation 445 10-3a Lock Granularity 486 9-3e Operation 448 10-3b Lock Types 489 9-3f Maintenance and Evolution 448 10-3c Two-Phase Locking to Ensure Serializability 490 9-4 Conceptual Design 448 10-3d Deadlocks 491 9-4a Data Analysis and Requirements 450 10-4 Concurrency Control with Time Stamping 9-4b Entity Relationship Modeling and Methods 492 Normalization 452 10-4a Wait/Die and Wound/Wait Schemes 493 9-4c Data Model Verification 455 10-5 Concurrency Control with Optimistic Methods 494 9-4d Distributed Database Design 458 10-6 ANSI Levels of Transaction Isolation 494 Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. x Contents 10-7 Database Recovery Management 496 12-6 Levels of Data and Process Distribution 547 10-7a Transaction Recovery 497 12-6a Single-Site Processing, Single-Site Data 547 Summary 501 12-6b Multiple-Site Processing, Single-Site Data 548 Key Terms 501 12-6c Multiple-Site Processing, Multiple-Site Data 549 Review Questions 502 12-7 Distributed Database Transparency Features 550 Problems 502 12-8 Distribution Transparency 551 12-9 Transaction Transparency 553 12-9a Distributed Requests and Distributed Transactions 553 Chapter 11 12-9b Distributed Concurrency Control 556 Database Performance Tuning and 12-9c Two-Phase Commit Protocol 557 Query Optimization 506 12-10 Performance and Failure Transparency 558 11-1 Database Performance-Tuning Concepts 507 12-11 Distributed Database Design 559 11-1a Performance Tuning: Client and Server 508 12-11a Data Fragmentation 559 11-1b DBMS Architecture 509 12-11b Data Replication 563 11-1c Database Query Optimization Modes 511 12-11c Data Allocation 565 11-1d Database Statistics 512 12-12 The CAP Theorem 565 11-2 Query Processing 513 12-13 C. J. Date’s 12 Commandments for Distributed 11-2a SQL Parsing Phase 514 Databases 567 11-2b SQL Execution Phase 515 Summary 568 11-2c SQL Fetching Phase 515 Key Terms 568 11-2d Query Processing Bottlenecks 515 Review Questions 569 11-3 Indexes and Query Optimization 516 Problems 570 11-4 Optimizer Choices 518 11-4a Using Hints to Affect Optimizer Choices 520 Chapter 13 11-5 SQL Performance Tuning 521 Business Intelligence and Data 11-5a Index Selectivity 521 Warehouses 573 11-5b Conditional Expressions 522 13-1 The Need for Data Analysis 574 11-6 Query Formulation 524 13-2 Business Intelligence 574 11-7 DBMS Performance Tuning 525 13-2a Business Intelligence Architecture 576 11-8 Query Optimization Example 527 13-2b Business Intelligence Benefits 580 Summary 533 13-2c Business Intelligence Evolution 580 Key Terms 534 13-2d Business Intelligence Technology Trends 582 Review Questions 534 13-3 Decision Support Data 583 Problems 535 13-3a Operational Data versus Decision Support Data 583 13-3b Decision Support Database Requirements 586 Chapter 12 13-4 The Data Warehouse 588 13-4a Data Marts 590 Distributed Database Management Systems 539 13-4b Twelve Rules That Define a Data Warehouse 591 13-5 Star Schemas 592 12-1 The Evolution of Distributed Database Management Systems 540 13-5a Facts 592 12-2 DDBMS Advantages and Disadvantages 542 13-5b Dimensions 592 12-3 Distributed Processing and Distributed 13-5c Attributes 593 Databases 543 13-5d Attribute Hierarchies 595 12-4 Characteristics of Distributed Database Management 13-5e Star Schema Representation 596 Systems 545 13-5f  Performance-Improving Techniques for the Star 12-5 DDBMS Components 546 Schema 598 Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Contents xi 13-6 Online Analytical Processing 602 14-5a Importing Documents in MongoDB 664 13-6a Multidimensional Data Analysis Techniques 602 14-5b Example of a MongoDB Query Using find() 665 13-6b Advanced Database Support 604 14-6 Working with Graph Databases Using Neo4j 666 13-6c Easy-to-Use End-User Interfaces 604 14-6a Creating Nodes in Neo4j 667 13-6d OLAP Architecture 604 14-6b Retrieving Node Data with MATCH and WHERE 668 13-6e Relational OLAP 607 14-6c Retrieving Relationship Data with MATCH and 13-6f Multidimensional OLAP 608 WHERE 669 13-6g Relational versus Multidimensional OLAP 609 Summary 670 13-7 Data Analytics 610 Key Terms 672 Review Questions 673 13-7a Data Mining 610 13-7b Predictive Analytics 613 Part 5 13-8 SQL Analytic Functions 614 13-8a The ROLLUP Extension 615 13-8b The CUBE Extension 616 Databases and the Internet 13-8c Materialized Views 618 13-9 Data Visualization 621 13-9a The Need for Data Visualization 622 Chapter 15 13-9b The Science of Data Visualization 624 Database Connectivity and Web 13-9c Understanding the Data 626 Technologies 675 13-10 Data Lake 627 15-1 Database Connectivity 676 Summary 628 15-1a Native SQL Connectivity 677 Key Terms 628 15-1b ODBC, DAO, and RDO 678 Review Questions 629 15-1c OLE-DB 680 Problems 630 15-1d ADO.NET 683 15-1e Java Database Connectivity (JDBC) 685 15-2 Database Internet Connectivity 686 Chapter 14 15-2a Web-to-Database Middleware: Server-Side Extensions 687 Big Data and NoSQL 640 15-2b Web Server Interfaces 689 14-1 Big Data 641 15-2c The Web Browser 690 14-1a Volume 643 15-2d Client-Side Extensions 691 14-1b Velocity 644 15-2e Web Application Servers 692 14-1c Variety 645 15-2f Web Database Development 692 14-1d Other Characteristics 646 15-3 Extensible Markup Language (XML) 696 14-2 Hadoop 647 15-3a Document Type Definitions (DTD) and XML 14-2a HDFS 648 Schemas 698 14-2b MapReduce 650 15-3b XML Presentation 700 14-2c Hadoop Ecosystem 652 15-3c XML Applications 702 14-2d Hadoop Pushback 654 15-4 Cloud Computing Services 703 14-3 NoSQL 654 15-4a Cloud Implementation Types 706 14-3a Key-Value Databases 655 15-4b Characteristics of Cloud Services 706 14-3b Document Databases 656 15-4c Types of Cloud Services 707 14-3c Column-Oriented Databases 657 15-4d Cloud Services: Advantages and Disadvantages 708 14-3d Graph Databases 660 15-4e SQL Data Services 709 14-3e Aggregate Awareness 662 Summary 710 14-4 NewSQL Databases 662 Key Terms 711 14-5 Working with Document Databases Review Questions 712 Using MongoDB 663 Problems 713 Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. xii Contents Part 6 16-7 Database Administration Tools 741 16-7a The Data Dictionary 742 Database Administration 16-7b Case Tools 744 16-8 Developing a Data Administration Strategy 746 16-9 The DBA’s Role in the Cloud 748 Chapter 16 16-10 The DBA at Work: Using Oracle for Database Database Administration and Administration 749 Security 715 16-10a Oracle Database Administration Tools 750 16-10b Ensuring That the RDBMS Starts Automatically 751 16-1 Data as a Corporate Asset 716 16-10c Creating Tablespaces and Datafiles 751 16-2 The Need for a Database and Its Role in an Organization 718 16-10d Managing Users and Establishing Security 753 16-3 Introduction of a Database: Special Considerations 719 16-10e Customizing the Database Initialization Parameters 756 16-4 The Evolution of Database Administration 721 Summary 757 16-5 The Database Environment’s Human Component 724 Key Terms 758 16-5a The DBA’s Managerial Role 726 Review Questions 758 16-5b The DBA’s Technical Role 731 16-6 Security 737 16-6a Security Policies 738 Glossary 760 16-6b Security Vulnerabilities 738 Index 775 16-6c Database Security 740 The following appendices are included on the Instructor and Student Resource Sites at www.cengage.com. Appendix A: Designing Databases with Lucidchart: A Tutorial Appendix B: The University Lab: Conceptual Design Appendix C: The University Lab: Conceptual Design Verification, Logical Design, and Implementation Appendix D: Converting an ER Model into a Database Structure Appendix E: Comparison of ER Modeling Notations Appendix F: Client/Server Systems Appendix G: Object-Oriented Databases Appendix H: Unified Modeling Language (UML) Appendix I: Databases in Electronic Commerce Appendix J: Web Database Development with ColdFusion Appendix K: The Hierarchical Database Model Appendix L: The Network Database Model Appendix M: MS Access Tutorial Appendix N: Creating a New Database Using Oracle Appendix O: Data Warehouse Implementation Factors Appendix P: Working with MongoDB Appendix Q: Working with Neo4j Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. About the Authors Carlos Coronel is currently the IT Resources Director for the Jones College of Business at Middle Tennessee State University. He has more than 30 years of experience in various fields as a Database Administrator, Network Administrator, Web Manager, and Technology Entrepreneur and Innovator. He has taught courses in web development, database design and development, and data communications at the undergraduate and graduate levels. Steven Morris earned the Ph.D. in Management Information Systems from Auburn University. He is Professor of Information Systems and Analytics in the Jones College of Business at Middle Tennessee State University. He has over 20 years of experience working with and teaching database systems and is actively engaged with consulting and professional database training with businesses in the Middle Tennessee and Nashville areas. He primarily teaches courses at the graduate and undergraduate levels in database design, advanced database programming, and Big Data for analytics. xiii Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Preface It is our great pleasure to present the fourteenth edition of Database Systems. We are grateful and humbled that so many of our colleagues around the world have chosen this text to support their classes. We wrote the first edition of this book because we wanted to explain the complex- ity of database systems in a language that was easy for students to understand. Over the years, we have maintained this emphasis on reaching out to students to explain complex concepts in a practical, approachable manner. This resource has been successful through thirteen editions because the authors, editors, and the publisher paid attention to the impact of technology and to adopters’ questions and suggestions. We believe that the fourteenth edition successfully reflects the same attention to such factors. The Approach: A Continued Emphasis on Design As the title suggests, Database Systems: Design, Implementation, and Management cov- ers three broad aspects of database systems. However, for several important reasons, special attention is given to database design. The availability of excellent database software enables people with little experience to create databases and database applications. Unfortunately, the “create without design” approach usually paves the road to a number of database disasters. In our experience, many database system failures are traceable to poor design and cannot be solved with the help of even the best programmers and managers. Nor is better DBMS software likely to overcome problems created or magnified by poor design. Even the best bricklayers and carpenters can’t create a good building from a bad blueprint. Most vexing problems of database system management seem to be triggered by poorly designed databases. It hardly seems worthwhile to use scarce resources to develop excellent database management skills merely to use them on crises induced by poorly designed databases. Design provides an excellent means of communication. Clients are more likely to get what they need when database system design is approached carefully and thoughtfully. In fact, clients may discover how their organizations really function once a good database design is completed. Familiarity with database design techniques promotes understanding of current database technologies. For example, because data warehouses derive much of their data from oper- ational databases, data warehouse concepts, structures, and procedures make more sense when the operational database’s structure and implementation are understood. Because the practical aspects of database design are stressed, we have covered design con- cepts and procedures in detail, making sure that the numerous end-of-chapter problems and cases are sufficiently challenging, so students can develop real and useful design skills. We also make sure that students understand the potential and actual conflicts between database design elegance, information requirements, and transaction processing speed. For example, it makes little sense to design databases that meet design elegance standards while they fail to meet end- user information requirements. Therefore, we explore the use of carefully defined trade-offs to ensure that the databases meet end-user requirements while conforming to high design standards. Prerequisites Students using these materials are expected to be familiar with basic system concepts and know the difference between hardware and software. Familiarity with basic productivity software such as MS Excel and MS Access is useful. Prior programming experience is not required. xiv Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Preface xv Instructors can configure their classes as best suits their resources and pedagogical approach. Data modeling can be done with a number of computer resources, some of which are free to use, or drawn by hand. Coding problems will require a DBMS. The MindTap resources include hands-on programming activities with an embedded MySQL database sandbox for students to work in. Other instructors may choose to have each student run their own individual DBMS, while still others may choose an instructor-administered central DBMS to which all students connect to work. The text supports all of these approaches. Target Market The text is written in a comprehensive manner that allows usage at a wide range of academic levels. The skills covered in the text are in high demand and are applicable in programs from two-year degrees, four-year degrees, graduate degrees, and adult continuing education. They are even taught in some specialized high school programs. We have reports of the text being used at all of these types of academic venues. The text is written in an easily approachable style that starts with the most basic concepts and builds to advanced topics. The modular nature of the chapters makes it easy for instructors to adapt the coverage to the level of sophistication that is most appropriate for their students. Changes to the Fourteenth Edition In many respects, revising a resource like this is more difficult than writing it the first time. If the text is successful, as this one is, a major concern is that the updates, inserts, and deletions will adversely affect writing style and continuity of coverage. The combination of superb review- ers and editors, plus a wealth of feedback from adopters and students of the previous editions, helped make this new edition the best yet. In this fourteenth edition, we have responded to the requests and suggestions of numerous adopters. The database arena is vast, wonderful, and in constant change. The role of data in society and industry is ever increasing, and database and data management technologies are constantly changing to address new challenges. To help you address changing learning needs as efficiently as possible, we’ve prioritized the following changes in this edition. Streamlined Coverage Scope creep is a real challenge because the ingenuity of our industry continually finds new ways to solve data problems. In this edition, we have streamlined many tangential topics that, while important, are not suitable for the depth of coverage previously provided. Reorganized SQL We have reorganized the SQL coverage to make sure basics are covered before introducing more advanced topics. This ensures the presentation is easier to follow and avoids overwhelm- ing students. We start with simple SQL statements to familiarize students with the basic SQL syntax and environment. This approach helps students build the confidence to transition to the more advanced SQL features and commands. These changes provide a better flow of material. MySQL Examples We continue our tradition of supporting multiple DBMS products (MS Access, Oracle, SQL Server, and MySQL). In cases where code or concepts are the same in all of these products, we have shifted many of the illustrations and examples into MySQL to provide a more consistent appearance. In cases where the code is different across products, we have multiple notes and alternative examples to clarify the concepts for other DBMS products. It is our intention that faculty using any of these products, or none of them, can use this edition seamlessly in their Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. xvi Preface classes. All students will benefit from consistency in the presentation. Faculty that support the text by using the coding resources available in MindTap can also integrate the coding problems more easily with the text. Note If instructors want students to complete the hands-on data manipulation assignments provided in the text, they need to ensure that students have access to a DBMS product. Further, some exercises involving MS Excel or MS Access require a complete version of that product. The hands-on coding exercises for MongoDB and Neo4j also require students to have access to that software. Coding Labs in MindTap The fourteenth edition presents a major step forward in the integration of digital content with this resource by providing automatically graded coding labs through the MindTap available for this product. The labs allow students to write SQL code in an interactive environment that provides immediate feedback on problems. Here is a summary of key changes in the fourteenth edition: Streamlined topic coverage, including Big Data technologies Reorganization of SQL to ensure basics are covered before more advanced topics Enhanced consistency and support for multiple DBMS products Topical Organization The fourteenth edition continues to provide a solid and practical foundation for the design, implementation, and management of database systems. This foundation is built on the notion that, while databases are very practical, their successful creation depends on understanding the important concepts that define them. It’s not easy to come up with the proper mix of theory and practice, but the previously mentioned feedback suggests that we largely succeeded in our quest to maintain the proper balance. The Systems View The title for this text begins with Database ­Systems. Therefore, we examine the database and design con- cepts covered in Chapters 1–6 as part of a larger whole by placing them within the systems analysis framework of Chapter 9. Database designers who fail to understand that the database is part of a larger sys- tem are likely to overlook important design require- Part 1 ments. In fact, Chapter 9, Database Design, provides the map for the advanced database design developed in Appendices B and C. Within the larger systems framework, we can also explore issues such as trans- Database Concepts action management and concurrency control (Chap- ter 10), distributed database management systems (Chapter 12), business intelligence and data ware- houses (Chapter 13), new technologies for Big Data 1 Database Systems (Chapter 14), database connectivity and web technol- 2 Data Models ogies (Chapter 15), and database administration and security (Chapter 16). Copyright 2023 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.  Preface xvii Database Design Chapter 9 The first item in our subtitle is Design, and our examination of database design is comprehensive. For example, Chapters 1 and 2 examine the devel- opment and future of databases and data models and Database Design illustrate the need for design. Chapter 3 examines the details of the relational database model; Chap- Learning Objectives After completing this chapter, you will be able to: ter 4 provides extensive, in-depth, and practical 9-1 Describe the role of database design as the foundation of a successful information system 9-4 Conduct evaluation and revision within the SDLC and DBLC frameworks database design coverage; and Chapter 5 explores 9-2 Describe the five phases in the Systems Development Life Cycle (SDLC) 9-5 Distinguish between top-down and bottom-up approaches in database design advanced database design topics. Chapter 6 is 9-3 Design databases using the six phases in the Database Life Cycle (DBLC) framework 9-6 Distinguish between centralized and decentralized conceptual database design devoted to critical normalization issues that affect database efficiency and effectiveness. Chapter 9 PREVIEW Databases are a part of a larger picture called an information system. Database designs examines database design within the systems frame- that fail to recognize this fact are not likely to be successful. Database designers must work and maps the activities required to successfully recognize that the database is a critical means to an end rather than an end in itself. Managers want the database to serve their management needs, but too many databases design and implement the complex, real-world data- seem to force managers to alter their routines to fit the database requirements. base developed in Appendices B and C. Appendix Information systems don’t just happen; they are the product of a carefully staged development process. Systems analysis is used to determine the need for A is a good introductory tutorial on designing data- an information system and to establish its limits. Within systems analysis, the actual bases with Lucidchart. information system is created through a process known as systems development. The creation and evolution