Database: Principles Programming Performance

Table of Contents

Preface
Chapter 1 Introduction
1.1 Fundamental Database Concepts
1.2 Database Users
1.3 Overview of a Relational DBMS
The Relational Model and Query Capabilities
Programs to Access a Database
Logical Database Design
Database Administration
Physical Database Design and Performance
Database Transactions
1.4 Putting It All Together

Chapter 2 The Relational Model
2.1 The CAP Database
2.2 Naming the Parts of a Database
Domains and Datatypes
Tables and Relations
2.3 Relational RULES
2.4 Keys, Superkeys, and Null Values
Null Values
2.5 Relational Algebra
Fundamental Operations of Relational Algebra
2.6 Set-Theoretic Operations
The Union, Intersection, and Difference Operations
Assignment and Alias
The Product Operation
2.7 Native Relational Operations
The Projection Operation
The Selection Operation
Precedence of Relational Operations
The Join Operation
The Division Operation
2.8 The Interdependence of Operations
2.9 Illustrative Examples
2.10 Other Relational Operations
Outer Join
Theta Join

Chapter 3 Query Language SQL
3.1 Introduction
SQL Capabilities
SQL History - Standards and Dialects
3.2 Setting Up the Database
A Practical Exercise
3.3 Simple Select Statements
3.4 Subselects
The In Predicate
The Quantified (Comparison) Predicate
The Exists Predicate
A Weakness of SQL: Too Many Equivalent Forms
3.5 SQL Union and "FOR ALL..." Conditions
The Union Operator
The Division Operator: SQL "FOR ALL..." Conditions
3.6 Set Functions in SQL
Handling Null Values
3.7 Groups of Rows in SQL
3.8 A Complete Description of SQL Select
Expressions, Predicates, and the search_condition
A Discussion of the Predicates
3.9 Insert, Update, and Delete Statements
The Insert Statement
The Update Statement
The Delete Statement
3.10 The Power of the Select Statement
The Non-Procedural Select Statement
The Select Statement
3.11 Object-Oriented Database Systems
Extended Relational Database Systems

Chapter 4 Programs to Access a Database
4.1 Introduction to Embedded SQL in C
A Simple Program Using Embedded SQL
Selecting Multiple Rows with a Cursor
4.2 Error Handling
Condition Handling with the Whenever Statement
Handling Error Messages
Indicator Variables
4.3 Some Common Embedded SQL Statements
The Select Statement
The Declare Cursor Statement
The Delete Statement
The Update Statement
The Insert Statement
Cursor Open, Fetch, and Close
Other Embedded SQL Operations
4.4 Programming for Transactions
The Concept of a Transaction
How Transactions Occur in Programs
The Transaction Isolation Guarantee and Locking
Special Considerations in Transactions
4.5 The Power of Procedural SQL Programs
Customized Set Functions
Other Capabilities: Transitive Closure
4.6 Dynamic SQL
Execute Immediate
Prepare, Execute, and Using
Dynamic Select: The Describe Statement and the SQLDA
4.7 Some Advanced Programming Concepts
Scrollable Cursors
Cursor Sensitivity
Fourth-Generation Languages

Chapter 5 Database Design
5.1 Introduction to E-R Concepts
Entities, Attributes, and Simple E-R Diagrams
Transforming Entities and Attributes to Relations
Relationships among Entities
5.2 Further Details of E-R Modeling
Cardinality of Entity Participation in a Relationship
One-to-One, Many-to-Many, and Many-to-One Relationships
Transforming Binary Relationships to Relations
5.3 Additional E-R Concepts
Cardinality of Attributes
Weak Entities
Generalization Hierarchies
5.4 Case Study
5.5 Normalization: Preliminaries
A Running Example: Employee Information
Anomalies of Bad Database Design
5.6 Functional Dependencies
Logical Implications among Functional Dependencies
5.7 Lossless Decompositions
5.8 Normal Forms
A Succession of Decompositions to Eliminate Anomalies
Normal Forms: BCNF, 3NF, and 2NF
An Algorithm to Achieve Well-Behaved 3NF Decomposition
A Review of Normalization
5.9 Additional Design Considerations
Database Design Tools

Chapter 6 Integrity, Views, Security, and Catalogs
6.1 Integrity Constraints
Integrity Constraints in the Create Table Statement
Primary Keys, Foreign Keys, and Referential Integrity
The Alter Table Statement
SQL-92 Integrity Constraints
Non-Procedural and Procedural Integrity Constraints
6.2 Creating Views
Restrictions on Querying and Updating a View
The Value of Views
6.3 Security: The Grant Statement in SQL
Variations in Database Products
6.4 System Catalogs
Catalog Variations in Database Products

Chapter 7 Indexing
7.1 The Concept of Indexing
7.2 Disk Storage
Disk Access Is Excruciatingly Slow
The DBA and Disk Resource Allocation in ORACLE
Data Storage Pages and Row Pointers: ORACLE, DB2, and INGRES
7.3 The B-Tree Index
Dynamic Changes in the B-Tree
Properties of the B-Tree
Index Node Layout and Free Space
Duplicate Key Values in an Index
Index Compression with Multiple Duplicates
7.4 Clustered and Non-Clustered Indexes
Clustered Index in DB2
Clustered Index in INGRES
7.5 A Hash Primary Index
No Incremental Changes in the Number of Slots Used
Collision Chain Length and Page Overflow
Disadvantages of Hash Primary Index
7.6 Throwing Darts at Random Slots
Unlimited Slot Occupancy: How Many Slots Are Occupied?

Chapter 8 Query Processing
8.1 Introductory Concepts
Query Resource Utilization
Gathering Statistics
Retrieving the Query Plan
8.2 Table Space Scans and I/O
Assumptions about I/O
8.3 Simple Indexed Access in DB2
Equal Unique Match Index Access
Index-Only Retrieval
8.4 Filter Factors and Statistics
DB2 Statistics
Filter Factors in DB2
8.5 Matching Index Scans, Composite Indexes
Definition of Matching Index Scan
Indexable Predicates and Performance
8.6 Multiple Index Access
List Prefetch and the RID Pool
Point of Diminishing Returns in Multiple Index Access
8.7 Methods for Joining Tables
Nested Loop Join
Merge Join
Hybrid Join
Multiple Table Joins
Transforming Nested Queries to Joins
8.8 Disk Sorts
The N-Way Merge Disk Sort Algorithm
8.9 Query Performance Benchmarks: A Case Study
The BENCH Table
Load Measurements
8.10 Query Performance Measurements
Query Q1
Query Q2A
Query Q2B
Query Q3A
Query Q3B
Query Q4A and Q4B
Query Q5
Query Q6A
Query Q6B
8.11 Cost Performance Assessment
Elapsed Time versus CPU Time Rating
Customizing the Rating

Chapter 9 Update Processing
9.1 Transactional Histories
9.2 Interleaved Read/Write Operations
9.3 Serializability and the Precedence Graph
The Precedence Graph
9.4 Locking to Ensure Serializability
The Waits-For Graph
9.5 Levels of Isolation
Read Uncommitted Isolation Level
Read Committed Isolation Level (Cursor Stability)
Repeatable Read Isolation Level
Serializability and Phantom Updates
9.6 Transactional Recovery
9.7 Recovery in Detail: Log Formats
Guarantees That Needed Log Entries Are on Disk
9.8 Checkpoints
The Commit-Consistent Checkpoint
Motivation for Other Kinds of Checkpoints
The Cache-Consistent Checkpoint
The Fuzzy Checkpoint
9.9 Media Recovery
Stable Storage
9.10 Performance: The TPC-A Benchmark
The TPC-A Benchmark Specification
Lessons from the TPC-A Benchmark

Chapter 10 Parallel and Distributed Databases
10.1 Some Multi-CPU Architectures
Client-Server Architectures
10.2 The Curve of CPU Cost versus Power
10.3 Shared-Nothing Database Architecture
Two-Phase Commit
Further Problems with Shared-Nothing Architecture
10.4 Query Parallelism

Appendix A Introductory Tutorial
A.1 Setting Up the CAP Database in INGRES
A.2 Setting Up the CAP Database in ORACLE
A.3 Datatypes

Appendix B Statement Syntax
B.1 Alter Table Statement
B.2 Close (Cursor) Statement
B.3 Commit Work Statement
B.4 Create Index Statement
B.5 Create Table Statement
B.6 Create Tablespace Statement in ORACLE
B.7 Create View Statement
B.8 Connenct Statement
B.9 Declare Cursor Statement
B.10 Delete Statement
B.11 Describe Statement
B.12 Disconnect Statement
B.13 Drop {Table / Veiw / Index} Statement
B.14 Execute Statement
B.15 Execute Immediate Statement
B.16 Fetch Statement
B.17 Grant Statement
B.18 Insert Statement
B.19 Modify Statement in INGRES
B.20 Open (Cursor) Statement
B.21 Prepare Statement
B.22 Revoke Statement
B.23 Rollback Statement
B.24 Select Statement
B.25 Update Statement

Appendix C Set Query Counts
Solutions to Selected Exercises
Index

Read More