Cengagebrain com student data files

DATABASE SYSTEMS

Carlos Coronel | Steven Morris

Design, Implementation, and Management

Australia • Brazil • Mexico • Singapore • United Kingdom • United States

12e

Copyright 2017 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 2017 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.

© 2017, 2015 Cengage Learning®

ALL RIGHTS RESERVED. No part of this work covered by the copyright herein may be reproduced or distributed in any form or by any means, except as permitted by U.S. copyright law, without the prior written permission of the copyright owner.

Screenshots for this book were created using Microsoft Access® and Visio® and were used with permission from Microsoft. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

Oracle is a registered trademark, and Oracle12 c and MySQL are trade- marks of Oracle Corporation.

iPhone, iPad, and iPod are registered trademarks of Apple Inc.

Library of Congress Control Number: 2015955694

Student Edition ISBN: 978-1-305-62748-2

Loose Leaf Edition ISBN: 978-1-305-86679-9

Cengage Learning 20 Channel Center Street Boston, MA 02210 USA

Cengage Learning 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.

Cengage Learning products are represented in Canada by Nelson Education, Ltd.

To learn more about Cengage Learning Solutions, visit www.cengage.com

Purchase any of our products at your local college store or at our preferred online store www.cengagebrain.com

Database Systems: Design, Implementation, and Management, 12th Edition

Carlos Coronel and Steven Morris

Vice President, General Manager: Science, Math & Quantitative Business: Balraj S. Kalsi

Product Director: Mike Schenk

Sr. Product Team Manager: Joe Sabatino

Content Development Manager: Jennifer King

Content Developer: Ted Knight

Product Assistant: Adele Scholtz

Marketing Director: Michele McTighe

Content Project Manager: Nadia Saloom

Media Developer: Chris Valentine

Manufacturing Planner: Ron Montgomery

Marketing Communications Manager: Dan Murphy

Production Service: Cenveo Publisher Services

Senior Art Director: Michelle Kunkler

Cover and Internal Designer: Tippy McIntosh

Cover Art Credit: agsandrew/iStock/ Getty Images Plus/Getty Images

Internal Design Image: silver tiger/ Shutterstock

Intellectual Property

Analyst: Christina Ciaramella

Project Manager: Kathryn Kucharek

For product information and technology assistance, contact us at Cengage Learning Customer & Sales Support, 1-800-354-9706

For permission to use material from this text or product, submit all requests online at www.cengage.com/permissions

Further permissions questions can be emailed to permissionrequest@cengage.com

Printed in the United States of America Print Number: 01 Print Year: 2016

Copyright 2017 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.

WCN: 02-200-203

Dedication To the treasures in my life: To Victoria, for 26 wonderful years. Thank you for your un- ending support, for being my angel, my sweetie, and most importantly, my best friend. To Carlos Anthony, who is an awesome older brother to all. 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 Gabriela Victoria, who is the image of bril- liance, beauty, and faithfulness. Thank you for being the sunshine in my cloudy days. Your future is bright and endless. To Christian Javier, who is smarter than of all of us. Thank you for being the youthful reminder of life’s simple beauties. Keep challenging yourself to new highs. 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 26 years of marriage, you are the beau- tiful 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 Logan, your depth of character is without measure. You are my pride and joy. To my daughter, Lauren Elizabeth, your beauty and intensity take my breath away. You are my heart and soul. Thank you all for the sacrifices you have made that enabled me to pur- sue this dream. I love you so much more than I can express. To my mother, Florence Maryann, and to the memory of my father, Alton Lamar, together they instilled in me the desire to learn and the passion to achieve. To my mother-in-law, Connie Duke, and to the memory of my father-in-law, Wayne Duke, 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 succeed. 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

Dedication iii

Copyright 2017 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, xiv

Text Features, xix

Additional Features, xxi

Acknowledgments, xxiii

Part 1: Database Concepts 1 1. Database Systems, 2

2. Data Models, 35

Part 2: Design Concepts 71 3. The Relational Database Model, 72

4. Entity Relationship (ER) Modeling, 117

5. Advanced Data Modeling, 169

6. Normalization of Database Tables, 201

Part 3: Advanced Design and Implementation 245 7. Introduction to Structured Query Language (SQL), 246

8. Advanced SQL , 340

9. Database Design, 439

Part 4: Advanced Database Concepts 481 10. Transaction Management and Concurrency Control, 482

11. Database Performance Tuning and Query Optimization, 515

12. Distributed Database Management Systems, 553

13. Business Intelligence and Data Warehouses, 589

14. Big Data Analytics and NoSQL, 648

Part 5: Databases and the Internet 679 15. Database Connectivity and Web Technologies, 680

Part 6: Database Administration 721 16. Database Administration and Security, 722

Glossary, 769

Index, 783

Brief Contents

iv Brief Contents

Copyright 2017 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.

The following appendixes are included on the Instructor and Student Companion Sites at www.cengagebrain.com.

Appendix A1: Designing Databases with Visio Professional 2010: A Tutorial Appendix A2: Designing Databases with Visio 2013: 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 Model 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 12c Appendix O: Data Warehouse Implementation Factors

Brief Contents v

Copyright 2017 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.

vi Contents

Part 1: Database Concepts 1 Chapter 1: Database Systems 2 1-1 Why Databases? 3 1-2 Data versus Information 4 1-3 Introducing the Database 6

1-3a Role and Advantages of the DBMS 6 1-3b Types of Databases 8

1-4 Why Database Design is Important 11 1-5 Evolution of File System Data Processing 14

1-5a Manual File Systems 14 1-5b Computerized File Systems 15 1-5c File System Redux: Modern End-User Productivity Tools 17

1-6 Problems with File System Data Processing 18 1-6a Structural and Data Dependence 19 1-6b Data Redundancy 20 1-6c Data Anomalies 21

1-7 Database Systems 21 1-7a The Database System Environment 22 1-7b DBMS Functions 24 1-7c Managing the Database System: A Shift in Focus 28

1-8 Preparing for Your Database Professional Career 28 Summary 30 • Key Terms 31 • Review Questions 32 • Problems 32

Chapter 2: Data Models 35 2-1 Data Modeling and Data Models 36 2-2 The Importance of Data Models 37 2-3 Data Model Basic Building Blocks 37 2-4 Business Rules 39

2-4a Discovering Business Rules 39 2-4b Translating Business Rules into Data Model Components 40 2-4c Naming Conventions 41

2-5 The Evolution of Data Models 41 2-5a Hierarchical and Network Models 41 2-5b The Relational Model 43 2-5c The Entity Relationship Model 45 2-5d The Object-Oriented (OO) Model 48 2-5e Object/Relational and XML 49 2-5f Emerging Data Models: Big Data and NoSQL 50 2-5g Data Models: A Summary 56

2-6 Degrees of Data Abstraction 57 2-6a The External Model 60 2-6b The Conceptual Model 61 2-6c The Internal Model 62 2-6d The Physical Model 63

Summary 64 • Key Terms 65 • Review Questions 65 • Problems 66

Part 2: Design Concepts 71 Chapter 3: The Relational Database Model 72 3-1 A Logical View of Data 73

3-1a Tables and Their Characteristics 73

Contents

Copyright 2017 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-2 Keys 76 3-2a Dependencies 76 3-2b Types of Keys 77

3-3 Integrity Rules 80 3-4 Relational Algebra 82

3-4a Formal Definitions and Terminology 82 3-4b Relational Set Operators 83

3-5 The Data Dictionary and the System Catalog 91 3-6 Relationships within the Relational Database 93

3-6a The 1:M Relationship 93 3-6b The 1:1 Relationship 95 3-6c The M:N Relationship 97

3-7 Data Redundancy Revisited 101 3-8 Indexes 103 3-9 Codd’s Relational Database Rules 104

Summary 106 • Key Terms 107 • Review Questions 107 • Problems 110

Chapter 4: Entity Relationship (ER) Modeling 117 4-1 The Entity Relationship Model (ERM) 118

4-1a Entities 118 4-1b Attributes 118 4-1c Relationships 124 4-1d Connectivity and Cardinality 125 4-1e Existence Dependence 126 4-1f Relationship Strength 126 4-1g Weak Entities 129 4-1h Relationship Participation 131 4-1i Relationship Degree 134 4-1j Recursive Relationships 136 4-1k Associative (Composite) Entities 138

4-2 Developing an ER Diagram 140 4-3 Database Design Challenges: Conflicting Goals 147

Summary 152 • Key Terms 153 • Review Questions 153 • Problems 156 • Cases 161

Chapter 5: Advanced Data Modeling 169 5-1 The Extended Entity Relationship Model 170

5-1a Entity Supertypes and Subtypes 170 5-1b Specialization Hierarchy 171 5-1c Inheritance 172 5-1d Subtype Discriminator 174 5-1e Disjoint and Overlapping Constraints 174 5-1f Completeness Constraint 175 5-1g Specialization and Generalization 176

5-2 Entity Clustering 176 5-3 Entity Integrity: Selecting Primary Keys 177

5-3a Natural Keys and Primary Keys 178 5-3b Primary Key Guidelines 178 5-3c When To Use Composite Primary Keys 178 5-3d When To Use Surrogate Primary Keys 180

5-4 Design Cases: Learning Flexible Database Design 182 5-4a Design Case 1: Implementing 1:1 Relationships 182 5-4b Design Case 2: Maintaining History of Time-Variant Data 183 5-4c Design Case 3: Fan Traps 186 5-4d Design Case 4: Redundant Relationships 187

Summary 188 • Key Terms 189 • Review Questions 189 • Problems 190 • Cases 192

Chapter 6: Normalization of Database Tables 201 6-1 Database Tables and Normalization 202 6-2 The Need For Normalization 202 6-3 The Normalization Process 206

6-3a Conversion To First Normal Form 208 6-3b Conversion To Second Normal Form 211 6-3c Conversion To Third Normal Form 213

Copyright 2017 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

6-4 Improving the Design 215 6-5 Surrogate Key Considerations 219 6-6 Higher-Level Normal Forms 220

6-6a The Boyce-Codd Normal Form 221 6-6b Fourth Normal Form (4NF) 224

6-7 Normalization and Database Design 226 6-8 Denormalization 229 6-9 Data-Modeling Checklist 232

Summary 234 • Key Terms 235 • Review Questions 235 • Problems 237

Part 3: Advanced Design and Implementation 245 Chapter 7: Introduction to Structured Query Language (SQL) 246 7-1 Introduction to SQL 247 7-2 Data Definition Commands 249

7-2a The Database Model 249 7-2b Creating The Database 251 7-2c The Database Schema 251 7-2d Data Types 252 7-2e Creating Table Structures 255 7-2f SQL Constraints 259 7-2g SQL Indexes 263

7-3 Data Manipulation Commands 264 7-3a Adding Table Rows 264 7-3b Saving Table Changes 266 7-3c Listing Table Rows 266 7-3d Updating Table Rows 268 7-3e Restoring Table Contents 269 7-3f Deleting Table Rows 269 7-3g Inserting Table Rows with a Select Subquery 270

7.4 SELECT Queries 271 7-4a Selecting Rows with Conditional Restrictions 271 7-4b Arithmetic Operators: The Rule of Precedence 276 7-4c Logical Operators: AND, OR, and NOT 277 7-4d Special Operators 279

7-5 Additional Data Definition Commands 283 7-5a Changing a Column’s Data Type 284 7-5b Changing a Column’s Data Characteristics 284 7-5c Adding a Column 284 7-5d Dropping a Column 285 7-5e Advanced Data Updates 285 7-5f Copying Parts of Tables 287 7-5g Adding Primary and Foreign Key Designations 289 7-5h Deleting a Table from the Database 290

7-6 Additional SELECT Query Keywords 290 7-6a Ordering a Listing 290 7-6b Listing Unique Values 292 7-6c Aggregate Functions 292 7-6d Grouping Data 297

7-7 Joining Database Tables 300 7-7a Joining Tables with an Alias 303 7-7b Recursive Joins 303

Summary 305 • Key Terms 306 • Review Questions 306 • Problems 307 • Cases 331

Chapter 8: Advanced SQL 340 8-1 SQL Join Operators 341

8-1a Cross Join 342 8-1b Natural Join 343 8-1c JOIN USING Clause 344 8-1d JOIN ON Clause 345 8-1e Outer Joins 347

8-2 Subqueries and Correlated Queries 349 8-2a WHERE Subqueries 351 8-2b IN Subqueries 352

Copyright 2017 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-2c HAVING Subqueries 353 8-2d Multirow Subquery Operators: ANY and ALL 353 8-2e FROM Subqueries 355 8-2f Attribute List Subqueries 356 8-2g Correlated Subqueries 358

8-3 SQL Functions 361 8-3a Date and Time Functions 361 8-3b Numeric Functions 366 8-3c String Functions 366 8-3d Conversion Functions 368

8-4 Relational Set Operators 371 8-4a UNION 371 8-4b UNION ALL 373 8-4c INTERSECT 373 8-4d EXCEPT (MINUS) 375 8-4e Syntax Alternatives 377

8-5 Virtual Tables: Creating a View 377 8-5a Updatable Views 379

8-6 Sequences 382 8-7 Procedural SQL 387

8-7a Triggers 392 8-7b Stored Procedures 401 8-7c PL/SQL Processing with Cursors 407 8-7d PL/SQL Stored Functions 409

8-8 Embedded SQL 410 Summary 415 • Key Terms 416 • Review Questions 417 • Problems 418 • Cases 435

Chapter 9: Database Design 439 9-1 The Information System 440 9-2 The Systems Development Life Cycle 442

9-2a Planning 442 9-2b Analysis 443 9-2c Detailed Systems Design 444 9-2d Implementation 444 9-2e Maintenance 445

9-3 The Database Life Cycle 445 9-3a The Database Initial Study 445 9-3b Database Design 450 9-3c Implementation and Loading 451 9-3d Testing and Evaluation 454 9-3e Operation 456 9-3f Maintenance and Evolution 457

9-4 Conceptual Design 457 9-4a Data Analysis and Requirements 459 9-4b Entity Relationship Modeling and Normalization 461 9-4c Data Model Verification 464 9-4d Distributed Database Design 467

9-5 DBMS Software Selection 467 9-6 Logical Design 468

9-6a Map the Conceptual Model to the Logical Model 468 9-6b Validate the Logical Model Using Normalization 470 9-6c Validate Logical Model Integrity Constraints 470 9-6d Validate the Logical Model Against User Requirements 471

9-7 Physical Design 471 9-7a Define Data Storage Organization 472 9-7b Define Integrity and Security Measures 472 9-7c Determine Performance Measures 473

9-8 Database Design Strategies 473 9-9 Centralized Versus Decentralized Design 474

Summary 477 • Key Terms 477 • Review Questions 477 • Problems 478

Copyright 2017 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

Part 4: Advanced Database Concepts 481 Chapter 10: Transaction Management and Concurrency Control 482 10-1 What Is a Transaction? 483

10-1a Evaluating Transaction Results 484 10-1b Transaction Properties 487 10-1c Transaction Management with SQL 488 10-1d The Transaction Log 489

10-2 Concurrency Control 490 10-2a Lost Updates 490 10-2b Uncommitted Data 491 10-2c Inconsistent Retrievals 492 10-2d The Scheduler 493

10-3 Concurrency Control with Locking Methods 495 10-3a Lock Granularity 496 10-3b Lock Types 498 10-3c Two-Phase Locking to Ensure Serializability 500 10-3d Deadlocks 500

10-4 Concurrency Control with Time Stamping Methods 502 10-4a Wait/Die and Wound/Wait Schemes 502

10-5 Concurrency Control with Optimistic Methods 503 10-6 ANSI Levels of Transaction Isolation 504 10-7 Database Recovery Management 506

10-7a Transaction Recovery 506

Summary 510 • Key Terms 511 • Review Questions 511 • Problems 512

Chapter 11: Database Performance Tuning and Query Optimization 515 11-1 Database Performance-Tuning Concepts 516

11-1a Performance Tuning: Client and Server 517 11-1b DBMS Architecture 518 11-1c Database Query Optimization Modes 520 11-1d Database Statistics 521

11-2 Query Processing 522 11-2a SQL Parsing Phase 523 11-2b SQL Execution Phase 524 11-2c SQL Fetching Phase 525 11-2d Query Processing Bottlenecks 525

11-3 Indexes and Query Optimization 526 11-4 Optimizer Choices 528

11-4a Using Hints to Affect Optimizer Choices 530 11-5 SQL Performance Tuning 531

11-5a Index Selectivity 531 11-5b Conditional Expressions 533

11-6 Query Formulation 534 11-7 DBMS Performance Tuning 536 11-8 Query Optimization Example 538

Summary 546 • Key Terms 547 • Review Questions 547 • Problems 548

Chapter 12: Distributed Database Management Systems 553 12-1 The Evolution of Distributed Database Management Systems 554 12-2 DDBMS Advantages and Disadvantages 556 12-3 Distributed Processing and Distributed Databases 556 12-4 Characteristics of Distributed Database Management Systems 559 12-5 DDBMS Components 560 12-6 Levels of Data and Process Distribution 561

12-6a Single-Site Processing, Single-Site Data 561 12-6b Multiple-Site Processing, Single-Site Data 562 12-6c Multiple-Site Processing, Multiple-Site Data 563

12-7 Distributed Database Transparency Features 564 12-8 Distribution Transparency 565

Copyright 2017 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

12-9 Transaction Transparency 568 12-9a Distributed Requests and Distributed Transactions 568 12-9b Distributed Concurrency Control 571 12-9c Two-Phase Commit Protocol 571

12-10 Performance and Failure Transparency 573 12-11 Distributed Database Design 575

12-11a Data Fragmentation 575 12-11b Data Replication 578 12-11c Data Allocation 580

12-12 The CAP Theorem 581 12-13 C. J. Date’s 12 Commandments for Distributed Databases 583

Summary 584 • Key Terms 585 • Review Questions 585 • Problems 586

Chapter 13: Business Intelligence and Data Warehouses 589 13-1 The Need for Data Analysis 590 13-2 Business Intelligence 590

13-2a Business Intelligence Architecture 592 13-2b Business Intelligence Benefits 598 13-2c Business Intelligence Evolution 598 13-2d Business Intelligence Technology Trends 601

13-3 Decision Support Data 602 13-3a Operational Data Versus Decision Support Data 602 13-3b Decision Support Database Requirements 605

13-4 The Data Warehouse 607 13-4a Data Marts 610 13-4b Twelve Rules That Define a Data Warehouse 610

13-5 Star Schemas 610 13-5a Facts 611 13-5b Dimensions 611 13-5c Attributes 612 13-5d Attribute Hierarchies 614 13-5e Star Schema Representation 616 13-5f Performance-Improving Techniques for the Star Schema 617

13-6 Online Analytical Processing 621 13-6a Multidimensional Data Analysis Techniques 621 13-6b Advanced Database Support 623 13-6c Easy-to-Use End-User Interfaces 623 13-6d OLAP Architecture 623 13-6e Relational OLAP 626 13-6f Multidimensional OLAP 628 13-6g Relational versus Multidimensional OLAP 628

13-7 SQL Extensions for OLAP 629 13-7a The ROLLUP Extension 630 13-7b The CUBE Extension 631 13-7c Materialized Views 633

Summary 636 • Key Terms 637 • Review Questions 637 • Problems 639

Chapter 14: Big Data Analytics and NoSQL 648 14-1 Big Data 649

14-1a Volume 651 14-1b Velocity 652 14-1c Variety 653 14-1d Other Characteristics 654

14-2 Hadoop 655 14-2a HDFS 655 14-2b MapReduce 658 14-2c Hadoop Ecosystem 660

14-3 NoSQL 662 14-3a Key-Value Databases 663 14-3b Document Databases 664 14-3c Column-Oriented Databases 665 14-3d Graph Databases 668 14-3e NewSQL Databases 669

Copyright 2017 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

14-4 Data Analytics 670 14-4a Data Mining 671 14-4b Predictive Analytics 673

Summary 675 • Key Terms 676 • Review Questions 677

Part 5: Databases and the Internet 679 Chapter 15: Database Connectivity and Web Technologies 680 15-1 Database Connectivity 681

15-1a Native SQL Connectivity 682 15-1b ODBC, DAO, and RDO 683 15-1c OLE-DB 685 15-1d ADO.NET 687 15-1e Java Database Connectivity (JDBC) 691

15-2 Database Internet Connectivity 692 15-2a Web-to-Database Middleware: Server-Side Extensions 693 15-2b Web Server Interfaces 695 15-2c The Web Browser 696 15-2d Client-Side Extensions 697 15-2e Web Application Servers 698 15-2f Web Database Development 699

15-3 Extensible Markup Language (XML) 702 15-3a Document Type Definitions (DTD) and XML Schemas 704 15-3b XML Presentation 706 15-3c XML Applications 708

15-4 Cloud Computing Services 709 15-4a Cloud Implementation Types 712 15-4b Characteristics of Cloud Services 712 15-4c Types of Cloud Services 713 15-4d Cloud Services: Advantages and Disadvantages 714 15-4e SQL Data Services 716

Summary 717 • Key Terms 718 • Review Questions 718 • Problems 719

Part 6: Database Administration 721 Chapter 16: Database Administration and Security 722 16-1 Data as a Corporate Asset 723 16-2 The Need for a Database and its Role in an Organization 724 16-3 Introduction of a Database: Special Considerations 726 16-4 The Evolution of Database Administration 727 16-5 The Database Environment’s Human Component 731

16-5a The DBA’s Managerial Role 733 16-5b The DBA’s Technical Role 738

16-6 Security 745 16-6a Security Policies 746 16-6b Security Vulnerabilities 746 16-6c Database Security 748

16-7 Database Administration Tools 749 16-7a The Data Dictionary 750 16-7b Case Tools 752

16-8 Developing a Data Administration Strategy 755 16-9 The DBA’s Role in the Cloud 756 16-10 The DBA at Work: Using Oracle for Database Administration 757

16-10a Oracle Database Administration Tools 758 16-10b Ensuring that the RDBMS Starts Automatically 758 16-10c Creating Tablespaces and Datafiles 760 16-10d Managing Users and Establishing Security 762 16-10e Customizing the Database Initialization Parameters 763

Summary 765 • Key Terms 766 • Review Questions 767 Glossary 769 Index 783

Copyright 2017 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 xiii

The following appendixes are included on the Instructor and Student Companion Sites at www.cengagebrain.com.

Appendix A1: Designing Databases with Visio Professional 2010: A Tutorial Appendix A2: Designing Databases with Visio 2013: 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 Model 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 12c Appendix O: Data Warehouse Implementation Factors

Copyright 2017 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.

xiv Preface

It is our great pleasure to present the twelfth 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 complexity 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 book has been successful through eleven editions because the au- thors, editors, and the publisher paid attention to the impact of technology and to adopter ques- tions and suggestions. We believe that this twelfth edition successfully reflects the same attention to such factors.

In many respects, rewriting a book is more difficult than writing it the first time. If the book 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 reviewers and editors, plus a wealth of feedback from adopters and students of the previous editions, helped make this new edition the best yet.

Changes to The Twelfth Edition In this twelfth edition, we added some new features and reorganized some coverage to provide a better flow of material. Aside from enhancing the already strong coverage of database design, we made other improvements in the topical coverage. In particular, the continued growth of Big Data and NoSQL technologies have challenged the status quo in the database industry. Therefore, we created an entire new chapter, Big Data Analytics and NoSQL, to help students grasp the key aspects of these complex new technologies and challenges. The twelfth edition also presents a ma- jor step forward in the integration of digital content with the text through online, automatically graded exercises to improve student outcomes. Here are a few of the highlights of changes in the twelfth edition: • New coverage of Big Data challenges beyond the traditional 3Vs • Expanded coverage of Hadoop, the Hadoop Distributed File System (HDFS), and MapReduce • Updated coverage of cloud data services and their impact on DBAs • Expanded coverage of NoSQL databases, including key-value databases, document databases,

column-oriented database, and graph databases • New coverage of the emerging NewSQL technologies • Improved coverage of data visualization • Added coverage of new sequence and identity capabilities in Oracle and SQL Server • Complete redesign of the look and feel of the text and layout to improve readability and visual

appeal • Embedded key term definitions within the text

This twelfth edition continues to provide a solid and practical foundation for the design, im- plementation, 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 im- portant 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.

Preface

Copyright 2017 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

The Approach: A Continued Emphasis On Design As the title suggests, Database Systems: Design, Implementation, and Management covers 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 cre-

ate 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 de- signed databases. It hardly seems worthwhile to use scarce resources to develop excellent da- tabase 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 opera- tional 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 concepts 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 informa- tion 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.

Topical Coverage The Systems View The book’s title begins with Database Systems. There- fore, we examine the database and design concepts 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 system are likely to overlook important design requirements. In fact, Chapter 9, Database Design, provides the map for the advanced database design developed in Appendixes B and C. Within the larger systems framework, we can also explore issues such as transaction management and concurrency control (Chapter 10), distributed da- tabase management systems (Chapter 12), business in- telligence and data warehouses (Chapter 13), database connectivity and web technologies (Chapter 15), and database administration and security (Chapter 16).