Database Systems: Design, Implementation, & Management, 14th Edition
By Carlos Coronel and Steven Morris
Contents:
Preface, xiv
Text Features, xx
Additional Features, xxii
Acknowledgments, xxiv
Part 1
Database Concepts
Chapter 1
Database Systems 3
1-1 Why Databases? 4
1-2 Data versus Information 5
1-3 Introducing the Database 8
1-3a Role and Advantages of the DBMS 8
1-3b Types of Databases 10
1-4 Why Database Design Is Important 12
1-5 Evolution of File System Data Processing 15
1-5a Manual File Systems 16
1-5b Computerized File Systems 16
1-5c File System Redux: Modern End-User Productivity
Tools 18
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 27
1-8 Preparing for Your Database Professional Career 28
Summary 29
Key Terms 29
Review Questions 30
Problems 31
Chapter 2
Data Models 33
2-1 Data Modeling and Data Models 34
2-2 The Importance of Data Models 35
2-3 Data Model Basic Building Blocks 36
2-4 Business Rules 37
2-4a Discovering Business Rules 37
2-4b Translating Business Rules into Data Model
Components 38
2-4c Naming Conventions 39
2-5 The Evolution of Data Models 39
2-5a Hierarchical and Network Models 39
2-5b The Relational Model 41
2-5c The Entity Relationship Model 43
2-5d The Object-Oriented Model 45
2-5e Object/Relational and XML 47
2-5f Emerging Data Models: Big Data and NoSQL 48
2-5g Data Models: A Summary 51
2-6 Degrees of Data Abstraction 52
2-6a The External Model 55
2-6b The Conceptual Model 56
2-6c The Internal Model 57
2-6d The Physical Model 58
Summary 59
Key Terms 60
Review Questions 60
Problems 61
Part 2
Design Concepts
Chapter 3
The Relational Database Model 65
3-1 A Logical View of Data 67
3-1a Tables and Their Characteristics 67
3-2 Keys 69
3-2a Dependencies 69
3-2b Types of Keys 70
3-3 Integrity Rules 73
3-4 Relational Algebra 75
3-4a Formal Definitions and Terminology 75
3-4b Relational Set Operators 76
3-5 The Data Dictionary and the
System Catalog 84
3-6 Relationships within the Relational Database 86
3-6a The 1:M Relationship 86
3-6b The 1:1 Relationship 88
3-6c The M:N Relationship 90
3-7 Data Redundancy Revisited 94
3-8 Indexes 96
3-9 Codd’s Relational Database Rules 98
Summary 98
Key Terms 99
Review Questions 99
Problems 102
Chapter 4
Entity Relationship (ER) Modeling 108
4-1 The Entity Relationship Model 109
4-1a Entities 110
4-1b Attributes 110
4-1c Relationships 116
4-1d Connectivity and Cardinality 116
4-1e Existence Dependence 119
4-1f Relationship Strength 119
4-1g Weak Entities 122
4-1h Relationship Participation 124
4-1i Relationship Degree 126
4-1j Recursive Relationships 128
4-1k Associative (Composite) Entities 132
4-2 Developing an ER Diagram 134
4-3 Database Design Challenges:
Conflicting Goals 142
Summary 146
Key Terms 147
Review Questions 147
Problems 149
Cases 155
Chapter 5
Advanced Data Modeling 162
5-1 The Extended Entity Relationship Model 163
5-1a Entity Supertypes and Subtypes 163
5-1b Specialization Hierarchy 164
5-1c Inheritance 165
5-1d Subtype Discriminator 167
5-1e Disjoint and Overlapping Constraints 167
5-1f Completeness Constraint 169
5-1g Specialization and Generalization 170
5-2 Entity Clustering 170
5-3 Entity Integrity: Selecting Primary Keys 171
5-3a Natural Keys and Primary Keys 172
5-3b Primary Key Guidelines 172
5-3c When to Use Composite Primary Keys 172
5-3d When to Use Surrogate Primary Keys 174
5-4 Design Cases: Learning Flexible Database Design 175
5-4a Design Case 1: Implementing 1:1
Relationships 176
5-4b Design Case 2: Maintaining History of Time-Variant
Data 177
5-4c Design Case 3: Fan Traps 180
5-4d Design Case 4: Redundant Relationships 181
Summary 182
Key Terms 182
Review Questions 182
Problems 183
Cases 185
Chapter 6
Normalization of Database Tables 192
6-1 Database Tables and Normalization 193
6-2 The Need for Normalization 194
6-3 The Normalization Process 197
6-3a Conversion to First Normal Form (1NF) 198
6-3b Conversion to Second Normal Form (2NF) 202
6-3c Conversion to Third Normal Form (3NF) 204
6-4 Improving the Design 206
6-5 Surrogate Key Considerations 210
6-6 Higher-Level Normal Forms 212
6-6a The Boyce-Codd Normal Form 212
6-6b Fourth Normal Form (4NF) 215
6-6c Fifth Normal Form (5NF) 217
6-7 Normalization and Database Design 218
6-8 Denormalization 222
6-9 Data-Modeling Checklist 225
Summary 227
Key Terms 227
Review Questions 228
Problems 229
Part 3
Advanced Design and Implementation
Chapter 7
Introduction to Structured Query
Language (SQL) 239
7-1 SQL Basics 240
7-1a Data Types 241
7-1b SQL Queries 242
7-1c The Database Model 243
7-2 Basic SELECT Queries 244
7-3 SELECT Statement Options 245
7-3a Using Column Aliases 246
7-3b Using Computed Columns 247
7-3c Arithmetic Operators:
The Rule of Precedence 248
7-3d Date Arithmetic 249
7-3e Listing Unique Values 250
7-4 FROM Clause Options 251
7-5 ORDER BY Clause Options 252
7-6 WHERE Clause Options 254
7-6a Selecting Rows with Conditional Restrictions 255
7-6b Using Comparison Operators on Character
Attributes 257
7-6c Using Comparison Operators on Dates 258
7-6d Logical Operators: AND, OR, and NOT 258
7-6e Special Operators 260
7-7 JOIN Operations 264
7-7a Natural Join 265
7-7b JOIN USING Syntax 267
7-7c JOIN ON Syntax 268
7-7d Common Attribute Names 269
7-7e Old-Style Joins 269
7-7f Outer Joins 271
7-7g Cross Join 274
7-7h Joining Tables with an Alias 275
7-7i Recursive Joins 275
7-8 Aggregate Processing 277
7-8a Aggregate Functions 277
7-8b Grouping Data 281
7-8c HAVING Clause 284
7-9 Subqueries 286
7-9a WHERE Subqueries 287
7-9b IN Subqueries 288
7-9c HAVING Subqueries 289
7-9d Multirow Subquery Operators: ALL and ANY 289
7-9e FROM Subqueries 290
7-9f Attribute List Subqueries 291
7-9g Correlated Subqueries 293
7-10 SQL Functions 296
7-10a Date and Time Functions 297
7-10b Numeric Functions 300
7-10c String Functions 300
7-10d Conversion Functions 302
7-11 Relational Set Operators 304
7-11a UNION 305
7-11b UNION ALL 306
7-11c INTERSECT 307
7-11d EXCEPT (MINUS) 308
7-11e Syntax Alternatives 309
7-12 Crafting SELECT Queries 310
7-12a Know Your Data 310
7-12b Know the Problem 310
7-12c Build One Clause at a Time 311
Summary 312
Key Terms 313
Review Questions 314
Problems 315
Chapter 8
Advanced SQL 351
8-1 Data Definition Commands 352
8-1a Starting Database Model 352
8-1b Creating the Database 354
8-1c The Database Schema 354
8-1d Data Types 355
8-2 Creating Table Structures 358
8-2a CREATE TABLE command 358
8-2b SQL Constraints 362
8-2c Creating a Table with a SELECT Statement 365
8-2d SQL Indexes 366
8-3 Altering Table Structures 367
8-3a Changing a Column’s Data Type 368
8-3b Changing a Column’s Data Characteristics 368
8-3c Adding a Column 368
8-3d Adding Primary Key, Foreign Key, and Check
Constraints 369
8-3e Dropping a Column 369
8-3f Deleting a Table from the Database 370
8-4 Data Manipulation Commands 370
8-4a Adding Table Rows 370
8-4b Inserting Table Rows with a SELECT Subquery 372
8-4c Saving Table Changes 374
8-4d Updating Table Rows 374
8-4e Deleting Table Rows 377
8-4f Restoring Table Contents 378
8-5 Virtual Tables: Creating a View 378
8-5a Updatable Views 380
8-6 Auto Increment, Identity, and Sequences 381
8-7 Procedural SQL 387
8-7a Stored Procedures 389
8-7b Working with Variables 391
8-7c Conditional Execution 392
8-7d Iteration or Looping 393
8-7e SELECT Processing with Cursors 396
8-7f Stored Procedures with Parameters 399
8-7g Triggers 401
8-7h User Defined Functions 412
8-8 Embedded SQL 412
Summary 417
Key Terms 418
Review Questions 418
Problems 418
Cases 425
Chapter 9
Database Design 431
9-1 The Information System 432
9-2 The Systems Development Life Cycle 434
9-2a Planning 434
9-2b Analysis 436
9-2c Detailed Systems Design 436
9-2d Implementation 437
9-2e Maintenance 437
9-3 The Database Life Cycle 437
9-3a The Database Initial Study 438
9-3b Database Design 442
9-3c Implementation and Loading 444
9-3d Testing and Evaluation 445
9-3e Operation 448
9-3f Maintenance and Evolution 448
9-4 Conceptual Design 448
9-4a Data Analysis and Requirements 450
9-4b Entity Relationship Modeling and
Normalization 452
9-4c Data Model Verification 455
9-4d Distributed Database Design 458
9-5 DBMS Software Selection 458
9-6 Logical Design 459
9-6a Map the Conceptual Model to the Logical Model
Components 459
9-6b Validate the Logical Model Using Normalization 461
9-6c Validate the Logical Model Integrity Constraints 461
9-6d Validate the Logical Model against User
Requirements 462
9-7 Physical Design 462
9-7a Define Data Storage Organization 463
9-7b Define Integrity and Security Measures 463
9-7c Determine Performance Measurements 464
9-8 Database Design Strategies 464
9-9 Centralized versus Decentralized Design 465
Summary 468
Key Terms 468
Review Questions 468
Problems 469
Part 4
Advanced Database Concepts
Chapter 10
Transaction Management and
Concurrency Control 473
10-1 What Is a Transaction? 474
10-1a Evaluating Transaction Results 476
10-1b Transaction Properties 478
10-1c Transaction Management with SQL 479
10-1d The Transaction Log 480
10-2 Concurrency Control 481
10-2a Lost Updates 481
10-2b Uncommitted Data 482
10-2c Inconsistent Retrievals 483
10-2d The Scheduler 484
10-3 Concurrency Control with Locking Methods 486
10-3a Lock Granularity 486
10-3b Lock Types 489
10-3c Two-Phase Locking to Ensure Serializability 490
10-3d Deadlocks 491
10-4 Concurrency Control with Time Stamping
Methods 492
10-4a Wait/Die and Wound/Wait Schemes 493
10-5 Concurrency Control with Optimistic Methods 494
10-6 ANSI Levels of Transaction Isolation 494
10-7 Database Recovery Management 496
10-7a Transaction Recovery 497
Summary 501
Key Terms 501
Review Questions 502
Problems 502
Chapter 11
Database Performance Tuning and
Query Optimization 506
11-1 Database Performance-Tuning Concepts 507
11-1a Performance Tuning: Client and Server 508
11-1b DBMS Architecture 509
11-1c Database Query Optimization Modes 511
11-1d Database Statistics 512
11-2 Query Processing 513
11-2a SQL Parsing Phase 514
11-2b SQL Execution Phase 515
11-2c SQL Fetching Phase 515
11-2d Query Processing Bottlenecks 515
11-3 Indexes and Query Optimization 516
11-4 Optimizer Choices 518
11-4a Using Hints to Affect Optimizer Choices 520
11-5 SQL Performance Tuning 521
11-5a Index Selectivity 521
11-5b Conditional Expressions 522
11-6 Query Formulation 524
11-7 DBMS Performance Tuning 525
11-8 Query Optimization Example 527
Summary 533
Key Terms 534
Review Questions 534
Problems 535
Chapter 12
Distributed Database Management
Systems 539
12-1 The Evolution of Distributed Database Management
Systems 540
12-2 DDBMS Advantages and Disadvantages 542
12-3 Distributed Processing and Distributed
Databases 543
12-4 Characteristics of Distributed Database Management
Systems 545
12-5 DDBMS Components 546
12-6 Levels of Data and Process Distribution 547
12-6a Single-Site Processing, Single-Site Data 547
12-6b Multiple-Site Processing, Single-Site Data 548
12-6c Multiple-Site Processing, Multiple-Site Data 549
12-7 Distributed Database Transparency Features 550
12-8 Distribution Transparency 551
12-9 Transaction Transparency 553
12-9a Distributed Requests and Distributed Transactions 553
12-9b Distributed Concurrency Control 556
12-9c Two-Phase Commit Protocol 557
12-10 Performance and Failure Transparency 558
12-11 Distributed Database Design 559
12-11a Data Fragmentation 559
12-11b Data Replication 563
12-11c Data Allocation 565
12-12 The CAP Theorem 565
12-13 C. J. Date’s 12 Commandments for Distributed
Databases 567
Summary 568
Key Terms 568
Review Questions 569
Problems 570
Chapter 13
Business Intelligence and Data
Warehouses 573
13-1 The Need for Data Analysis 574
13-2 Business Intelligence 574
13-2a Business Intelligence Architecture 576
13-2b Business Intelligence Benefits 580
13-2c Business Intelligence Evolution 580
13-2d Business Intelligence Technology Trends 582
13-3 Decision Support Data 583
13-3a Operational Data versus Decision Support Data 583
13-3b Decision Support Database Requirements 586
13-4 The Data Warehouse 588
13-4a Data Marts 590
13-4b Twelve Rules That Define a Data Warehouse 591
13-5 Star Schemas 592
13-5a Facts 592
13-5b Dimensions 592
13-5c Attributes 593
13-5d Attribute Hierarchies 595
13-5e Star Schema Representation 596
13-5f Performance-Improving Techniques for the Star
Schema 598
13-6 Online Analytical Processing 602
13-6a Multidimensional Data Analysis Techniques 602
13-6b Advanced Database Support 604
13-6c Easy-to-Use End-User Interfaces 604
13-6d OLAP Architecture 604
13-6e Relational OLAP 607
13-6f Multidimensional OLAP 608
13-6g Relational versus Multidimensional OLAP 609
13-7 Data Analytics 610
13-7a Data Mining 610
13-7b Predictive Analytics 613
13-8 SQL Analytic Functions 614
13-8a The ROLLUP Extension 615
13-8b The CUBE Extension 616
13-8c Materialized Views 618
13-9 Data Visualization 621
13-9a The Need for Data Visualization 622
13-9b The Science of Data Visualization 624
13-9c Understanding the Data 626
13-10 Data Lake 627
Summary 628
Key Terms 628
Review Questions 629
Problems 630
Chapter 14
Big Data and NoSQL 640
14-1 Big Data 641
14-1a Volume 643
14-1b Velocity 644
14-1c Variety 645
14-1d Other Characteristics 646
14-2 Hadoop 647
14-2a HDFS 648
14-2b MapReduce 650
14-2c Hadoop Ecosystem 652
14-2d Hadoop Pushback 654
14-3 NoSQL 654
14-3a Key-Value Databases 655
14-3b Document Databases 656
14-3c Column-Oriented Databases 657
14-3d Graph Databases 660
14-3e Aggregate Awareness 662
14-4 NewSQL Databases 662
14-5 Working with Document Databases
Using MongoDB 663
14-5a Importing Documents in MongoDB 664
14-5b Example of a MongoDB Query Using find() 665
14-6 Working with Graph Databases Using Neo4j 666
14-6a Creating Nodes in Neo4j 667
14-6b Retrieving Node Data with MATCH and WHERE 668
14-6c Retrieving Relationship Data with MATCH and
WHERE 669
Summary 670
Key Terms 672
Review Questions 673
Part 5
Databases and the Internet
Chapter 15
Database Connectivity and Web Technologies 675
15-1 Database Connectivity 676
15-1a Native SQL Connectivity 677
15-1b ODBC, DAO, and RDO 678
15-1c OLE-DB 680
15-1d ADO.NET 683
15-1e Java Database Connectivity (JDBC) 685
15-2 Database Internet Connectivity 686
15-2a Web-to-Database Middleware: Server-Side
Extensions 687
15-2b Web Server Interfaces 689
15-2c The Web Browser 690
15-2d Client-Side Extensions 691
15-2e Web Application Servers 692
15-2f Web Database Development 692
15-3 Extensible Markup Language (XML) 696
15-3a Document Type Definitions (DTD) and XML
Schemas 698
15-3b XML Presentation 700
15-3c XML Applications 702
15-4 Cloud Computing Services 703
15-4a Cloud Implementation Types 706
15-4b Characteristics of Cloud Services 706
15-4c Types of Cloud Services 707
15-4d Cloud Services: Advantages and Disadvantages 708
15-4e SQL Data Services 709
Summary 710
Key Terms 711
Review Questions 712
Problems 713
Part 6
Database Administration
Chapter 16
Database Administration and
Security 715
16-1 Data as a Corporate Asset 716
16-2 The Need for a Database and Its Role in an
Organization 718
16-3 Introduction of a Database: Special Considerations 719
16-4 The Evolution of Database Administration 721
16-5 The Database Environment’s Human Component 724
16-5a The DBA’s Managerial Role 726
16-5b The DBA’s Technical Role 731
16-6 Security 737
16-6a Security Policies 738
16-6b Security Vulnerabilities 738
16-6c Database Security 740
16-7 Database Administration Tools 741
16-7a The Data Dictionary 742
16-7b Case Tools 744
16-8 Developing a Data Administration Strategy 746
16-9 The DBA’s Role in the Cloud 748
16-10 The DBA at Work: Using Oracle for Database
Administration 749
16-10a Oracle Database Administration Tools 750
16-10b Ensuring That the RDBMS Starts Automatically 751
16-10c Creating Tablespaces and Datafiles 751
16-10d Managing Users and Establishing Security 753
16-10e Customizing the Database Initialization
Parameters 756
Summary 757
Key Terms 758
Review Questions 758
Glossary 760
Index 775
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