Database Design, Application Development, And Administration, 5th Edition

Part 1
Introduction to Database Environments

Chapter 1 - Introduction to Database Management - 3
1.1 Database Characteristics - 4
1.2 Features of Database Management Systems - 6
1.2.1 Database Definition
1.2.2 Nonprocedural Access
1.2.3 Application Development and Procedural Language Interface
1.2.4 Features to Support Database Operations
1.2.5 Third-Party Features
1.3 Development of Database Technology and Market Structure - 12
1.3.1 Evolution of Database Technology
1.3.2 Current Market for Database Software
1.4 Architectures of Database Management Systems - 15
1.4.1 Data Independence and the Three Schema Architecture
1.4.2 Parallel and Distributed Database Processing
1.5 Organizational Impacts of Database Technology - 20
1.5.1 Interacting with Databases
1.5.2 Information Resource Management

Chapter 2 - Introduction to Database Development - 27
2.1 Information Systems - 27
2.1.1 Components of Information Systems
2.1.2 Information Systems Development Process
2.2 Goals of Database Development - 30
2.2.1 Develop a Common Vocabulary
2.2.2 Define the Meaning of Data
2.2.3 Ensure Data Quality
2.2.4 Find an Efficient Implementation
2.3 Database Development Process - 32
2.3.1 Phases of Database Development
2.3.2 Skills in Database Development
2.4 Tools of Database Development - 37
2.4.1 Diagramming
2.4.2 Documentation
2.4.3 Analysis
2.4.4 Prototyping Tools
2.4.5 Commercial CASE Tools

Part 2
Understanding Relational Databases

Chapter 3 - The Relational Data Model - 47
3.1 Basic Elements - 47
3.1.1 Tables
3.1.2 Connections among Tables
3.1.3 Alternative Terminology
3.2 Integrity Rules - 51
3.2.1 Definition of the Integrity Rules
3.2.2 Application of the Integrity Rules
3.2.3 Graphical Representation of Referential Integrity
3.3 Delete and Update Actions for Referenced Rows - 56
3.4 Operators of Relational Algebra - 58
3.4.1 Restrict (Select) and Project Operators
3.4.2 Extended Cross Product Operator
3.4.3 Join Operator
3.4.4 Outer Join Operator
3.4.5 Union, Intersection, and Difference Operators
3.4.6 Summarize Operator
3.4.7 Divide Operator
3.4.8 Summary of Operators
Appendix 3.A: CREATE TABLE Statements for the
University Database Tables - 78
Appendix 3.B: SQL:2008 Syntax Summary - 79
Appendix 3.C: Generation of Unique Values for Primary
Keys - 81

Chapter 4 - Query Formulation with SQL - 83
4.1 Background - 83
4.1.1 Brief History of SQL
4.1.2 Scope of SQL
4.2 Getting Started with the SELECT Statement - 85
4.2.1 Single Table Problems
4.2.2 Joining Tables
4.2.3 Summarizing Tables with GROUP BY and HAVING
4.2.4 Improving the Appearance of Results
4.3 Conceptual Evaluation Process for SELECT Statements - 100
4.4 Critical Questions for Query Formulation - 104
4.5 Refining Query Formulation Skills with Examples - 106
4.5.1 Joining Multiple Tables with the Cross Product Style Table of Contents
4.5.2 Joining Multiple Tables with the Join Operator Style
4.5.3 Self-Joins and Multiple Joins between Two Tables
4.5.4 Combining Joins and Grouping
4.5.5 Traditional Set Operators in SQL
4.6 SQL Modification Statements - 116
Appendix 4.A: SQL:2008 Syntax Summary - 131
Simplified SELECT Syntax
INSERT Syntax
UPDATE Syntax
DELETE Syntax
Appendix 4.B: Syntax Differences among Major DBMS
Products - 134

Part 3
Data Modeling

Chapter 5 - Understanding Entity Relationship Diagrams - 137
5.1 Introduction to Entity Relationship Diagrams - 138
5.1.1 Basic Symbols
5.1.2 Relationship Cardinality
5.1.3 Comparison to Relational Database Diagrams
5.2 Understanding Relationships - 142
5.2.1 Identification Dependency (Weak Entities and Identifying Relationships)
5.2.2 Relationship Patterns
5.2.3 Equivalence between 1-M and M-N Relationships
5.3 Classification in the Entity Relationship Model - 149
5.3.1 Generalization Hierarchies
5.3.2 Disjointness and Completeness Constraints
5.3.3 Multiple Levels of Generalization
5.4 Notation Summary and Diagram Rules - 152
5.4.1 Notation Summary
5.4.2 Diagram Rules
5.5 Comparison to Other Notations - 158
5.5.1 ERD Variations
5.5.2 Class Diagram Notation of the Unified Modeling Language

Chapter 6 - Developing Data Models for Business Databases - 173
6.1 Analyzing Business Data Modeling Problems - 173
6.1.1 Guidelines for Analyzing Business Information Needs
6.1.2 Information Requirements for the Water Utility Database
6.2 Refinements to an ERD - 179
6.2.1 Expanding Attributes
6.2.2 Splitting Compound Attributes
6.2.3 Expanding Entity Types
6.2.4 Transforming a Weak Entity into a Strong Entity
6.2.5 Adding History
6.2.6 Adding Generalization Hierarchies
6.2.7 Summary of Transformations
6.3 Finalizing an ERD - 186
6.3.1 Documenting an ERD
6.3.2 Detecting Common Design Errors
6.4 Converting an ERD to Relational Tables - 191
6.4.1 Basic Conversion Rules
6.4.2 Converting Optional 1-M Relationships
6.4.3 Converting Generalization Hierarchies
6.4.4 Converting 1-1 Relationships
6.4.5 Comprehensive Conversion Example
6.4.6 Conversion Practices in Commercial CASE Tools

Part 4
Relational Database Design

Chapter 7 - Normalization of Relational Tables - 229
7.1 Overview of Relational Database Design - 229
7.1.1 Avoidance of Modification Anomalies
7.1.2 Functional Dependencies
7.2 Normal Forms - 234
7.2.1 First Normal Form
7.2.2 Second and Third Normal Forms
7.2.3 Boyce-Codd Normal Form
7.2.4 Simple Synthesis Procedure
7.3 Refining M-Way Relationships - 243
7.3.1 Relationship Independence
7.3.2 Multivalued Dependencies and Fourth Normal Form
7.4 Higher Level Normal Forms - 247
7.4.1 Fifth Normal Form
7.4.2 Domain Key Normal Form
7.5 Practical Concerns about Normalization - 249
7.5.1 Role of Normalization in the Database Development Process
7.5.2 Analyzing the Normalization Objective

Chapter 8 - Physical Database Design - 265
8.1 Overview of Physical Database Design - 266
8.1.1 Storage Level of Databases
8.1.2 Objectives and Constraints
8.1.3 Inputs, Outputs, and Environment
8.1.4 Difficulties
8.2 Inputs of Physical Database Design - 269
8.2.1 Table Profiles
8.2.2 Application Profiles
8.3 File Structures - 273
8.3.1 Sequential Files
8.3.2 Hash Files
8.3.3 Multiway Tree (Btrees) Files
8.3.4 Bitmap Indexes
8.3.5 Summary of File Structures
8.3.6 Oracle Storage Concepts and File Structures
8.4 Query Optimization - 287
8.4.1 Translation Tasks
8.4.2 Improving Optimization Decisions
8.5 Index Selection - 295
8.5.1 Problem Definition
8.5.2 Trade-offs and Difficulties
8.5.3 Selection Rules
8.6 Additional Choices in Physical Database Design - 301
8.6.1 Denormalization
8.6.2 Record Formatting
8.6.3 Parallel Processing
8.6.4 Other Ways to Improve Performance

Part 5
Application Development with Relational Databases

Chapter 9 - Advanced Query Formulation with SQL - 319
9.1 Outer Join Problems - 320
9.1.1 SQL Support for Outer Join Problems
9.1.2 Mixing Inner and Outer Joins
9.2 Understanding Nested Queries - 325
9.2.1 Type I Nested Queries
9.2.2 Limited SQL Formulations for Difference Problems
9.2.3 Using Type II Nested Queries for Difference Problems
9.2.4 Nested Queries in the FROM Clause
9.3 Formulating Division Problems - 336
9.3.1 Review of the Divide Operator
9.3.2 Simple Division Problems
9.3.3 Advanced Division Problems
9.4 Null Value Considerations - 341
9.4.1 Effect on Simple Conditions
9.4.2 Effect on Compound Conditions
9.4.3 Effect on Aggregate Calculations and Grouping
Appendix 9.A: Usage of Multiple Statements in Microsoft Access - 354
Appendix 9.B: SQL:2008 Syntax Summary - 355
Appendix 9.C: Oracle 8i Notation for Outer
Joins - 356

Chapter 10 - Application Development with Views - 359
10.1 Background - 359
10.1.1 Motivation
10.1.2 View Definition
10.2 Using Views for Retrieval - 362
10.2.1 Using Views in SELECT Statements
10.2.2 Processing Queries with View References
10.3 Updating Using Views - 366
10.3.1 Single-Table Updatable Views
10.3.2 Multiple-Table Updatable Views
10.4 Using Views in Hierarchical Forms - 372
10.4.1 What Is a Hierarchical Form?
10.4.2 Relationship between Hierarchical Forms and Tables
10.4.3 Query Formulation Skills for Hierarchical Forms
10.5 Using Views in Reports - 378
10.5.1 What is a Hierarchical Report?
10.5.2 Query Formulation Skills for Hierarchical Reports
Appendix 10.A: SQL:2008 Syntax Summary - 395
Appendix 10.B: Rules for Updatable Join Views in Oracle - 395

Chapter 11 - Stored Procedures and Triggers - 397
11.1 Database Programming Languages and PL/SQL - 398
11.1.1 Motivation for Database Programming Languages
11.1.2 Design Issues
11.1.3 PL/SQL Statements
11.1.4 Executing PL/SQL Statements in Anonymous Blocks
11.2 Stored Procedures - 409
11.2.1 PL/SQL Procedures
11.2.2 PL/SQL Functions
11.2.3 Using Cursors
11.2.4 PL/SQL Packages
11.3 Triggers - 422
11.3.1 Motivation and Classification of Triggers
11.3.2 Oracle Triggers
11.3.3 Understanding Trigger Execution
Appendix 11.A: SQL:2008 Syntax Summary - 453

Part 6
Advanced Database Development

Chapter 12 - View Design and Integration - 457
12.1 Motivation for View Design and Integration - 457
12.2 View Design with Forms - 459
12.2.1 Form Analysis
12.2.2 Analysis of M-Way Relationships Using Forms
12.3 View Integration - 469
12.3.1 Incremental and Parallel Integration Approaches
12.3.2 View Integration Examples

Chapter 13 - Database Development for Student Loan Limited - 479
13.1 Case Description - 480
13.1.1 Overview
13.1.2 Flow of Work
13.2 Conceptual Data Modeling - 485
13.2.1 ERD for the Loan Origination Form
13.2.2 Incremental Integration after Adding the Disclosure Letter
13.2.3 Incremental Integration after Adding the Statement of Account
13.2.4 Incremental Integration after Adding the Loan Activity Report
13.3 Refining the Conceptual Schema - 490
13.3.1 Schema Conversion
13.3.2 Normalization
13.4 Physical Database Design and Application Development - 494
13.4.1 Application and Table Profiles
13.4.2 Index Selection
13.4.3 Derived Data and Denormalization Decisions
13.4.4 Other Implementation Decisions
13.4.5 Application Development
Appendix 13.A: Glossary of Form and Report Fields - 502
Appendix 13.B: CREATE TABLE Statements - 505

Part 7
Managing Database Environments

Chapter 14 - Data and Database Administration - 509
14.1 Organizational Context for Managing Databases - 509
14.1.1 Database Support for Management Decision Making
14.1.2 Information Resource Management to Knowledge Management
14.1.3 Responsibilities of Data Administrators and Database Administrators
14.2 Tools of Database Administration - 514
14.2.1 Security
14.2.2 Integrity Constraints
14.2.3 Management of Triggers and Stored Procedures
14.2.4 Data Dictionary Manipulation
14.3 Processes for Database Specialists - 526
14.3.1 Data Planning
14.3.2 Selection and Evaluation of Database Management Systems
14.4 Managing Database Environments - 533
14.4.1 Transaction Processing
14.4.2 Data Warehouse Processing
14.4.3 Distributed Environments
14.4.4 Object Database Management
Appendix 14.A: SQL:2008 Syntax Summary - 542

Chapter 15 - Transaction Management - 545
15.1 Basics of Database Transactions - 546
15.1.1 Transaction Examples
15.1.2 Transaction Properties
15.2 Concurrency Control - 550
15.2.1 Objective of Concurrency Control
15.2.2 Interference Problems
15.2.3 Concurrency Control Tools
15.3 Recovery Management - 558
15.3.1 Data Storage Devices and Failure Types
15.3.2 Recovery Tools
15.3.3 Recovery Processes
15.4 Transaction Design Issues - 565
15.4.1 Transaction Boundary and Hot Spots
15.4.2 Isolation Levels
15.4.3 Timing of Integrity Constraint Enforcement
15.4.4 Save Points
15.5 Workflow Management - 574
15.5.1 Characterizing Workflows
15.5.2 Enabling Technologies
Appendix 15.A: SQL:2008 Syntax Summary - 588

Chapter 16 - Data Warehouse Concepts and Design - 591
16.1 Basic Concepts - 592
16.1.1 Transaction Processing versus Decision Support
16.1.2 Characteristics of Data Warehouses
16.1.3 Architectures for Data Warehouses
16.1.4 Data Mining
16.1.5 Applications of Data Warehouses
16.2 Multidimensional Representation of Data - 598
16.2.1 Example of a Multidimensional Data Cube
16.2.2 Multidimensional Terminology
16.2.3 Time-Series Data
16.2.4 Data Cube Operators
16.3 Relational Database Design for Data Warehouses - 605
16.3.1 Relational Data Modeling Patterns
16.3.2 Dimension Summarizability Problems and Patterns
16.3.3 Dimension-Fact Summarizability Problems and Patterns
16.3.4 Time Representation in Star Schemas
16.3.5 Dimension Representation
16.4 Enterprise Data Warehouse Development - 619
16.4.1 Data Warehouse Design Methodologies
16.4.2 Colorado Education Data Warehouse

Chapter 17 - Data Integration Practices and Relational DBMS Extensions for Data Warehouses - 633
17.1 Data Integration Concepts - 634
17.1.1 Sources of Data
17.1.2 Workflow for Maintaining a Data Warehouse
17.1.3 Data Cleaning Techniques
17.4.4 Data Integration Architectures and Tools
17.1.5 Managing the Refresh Process
17.2 Extensions to SQL for Multidimensional Data - 648
17.2.1 CUBE Operator
17.2.2 ROLLUP Operator
17.2.3 GROUPING SETS Operator
17.2.4 GROUP BY Operator Variations and Extensions for Decision Support
17.3 Summary Data Storage and Optimization - 658
17.3.1 Materialized Views in Oracle
17.3.2 Query Rewriting Principles
17.3.3 Storage and Optimization Technologies

Chapter 18 - Client-Server Processing, Parallel Database Processing, and Distributed Databases - 673
18.1 Overview of Distributed Processing and Distributed Data - 674
18.1.1 Motivation for Client-Server Processing
18.1.2 Motivation for Parallel Database Processing
18.1.3 Motivation for Distributed Data
18.1.4 Motivation for Cloud Based Computing
18.1.5 Summary of Advantages and Disadvantages
18.2 Client-Server Database Architectures - 678
18.2.1 Design Issues
18.2.2 Description of Architectures
18.3 Parallel Database Processing - 686
18.3.1 Architectures and Design Issues
18.3.2 Commercial Parallel Database Technology
18.4 Architectures for Distributed Database Management Systems - 691
18.4.1 Component Architecture
18.4.2 Schema Architectures
18.5 Transparency for Distributed Database Processing - 694
18.5.1 Motivating Example
18.5.2 Fragmentation Transparency
18.5.3 Location Transparency
18.5.4 Local Mapping Transparency
18.5.5 Transparency in Oracle Distributed Databases
18.6 Distributed Database Processing - 703
18.6.1 Distributed Query Processing
18.6.2 Distributed Transaction Processing

Chapter 19 - Object Database Management Systems - 715
19.1 Motivation for Object Database Management - 715
19.1.1 Complex Data
19.1.2 Type System Mismatch
19.1.3 Application Examples
19.2 Object-Oriented Principles - 718
19.2.1 Encapsulation
19.2.2 Inheritance
19.2.3 Polymorphism
19.2.4 Programming Languages versus DBMSs
19.3 Architectures for Object Database Management - 723
19.3.1 Large Objects and External Software
19.3.2 Specialized Media Servers
19.3.3 Object Database Middleware
19.3.4 Object-Relational Database Management Systems for User-Defined Types
19.3.5 Object-Oriented Database Management Systems
19.3.6 Summary of Object Database Architectures
19.4 Object Database Features in SQL:2008 - 729
19.4.1 User-Defined Types
19.4.2 Table Definitions
19.4.3 Subtable Families
19.4.4 Manipulating Complex Objects and Subtable Families
19.5 Object Database Features in Oracle 11g - 737
19.5.1 Defining User-Defined Types and Typed Tables in Oracle 11g
19.5.2 Using Typed Tables in Oracle 11g
19.5.3 Dependencies among Types and Typed Tables
19.5.4 Other Object Features in Oracle 11g