Database Systems: Design, Implementation and Management, Twelve Edition
By Carlos Coronel and Steven Morris
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
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
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
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
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
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
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
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