Chapter 1 Introduction 1
1．1 Fundamental Database Concepts 1
History of Database Systems 2
The Relational and Object-Relational Models 2
The Database Systems Covered 3
A Relational Database Example 3
An Object-Relational Database Example 5
1．2 Database Users 6
1．3 Overview of Relational and Object-Relational DBMS 8
Chapter 2：The Relational Model 8
Chapter 3：Basic SQL Query Language 9
Chapter 4：The Object-Relational Model 11
Chapter 5：Programs to Access a Database 12
Chapter 6：Database Design 14
Chapter 7：Integrity，Views，Security，and Catalogs 16
Chapter 8：Indexing 19
Chapter 9：Query Processing 20
Chapter 10：Update Transactions 21
Chapter 11：Parallel and Distributed Databases 22
1．4 Putting It All Together 23
Chapter 2 The Relational Model 25
2．1 The CAP Database 26
2．2 Naming the Parts of a Database 29
Domains and Datatypes 30
Tables and Relations 30
2．3 Relational Rules 32
2．4 Keys，Superkeys，and Null Values 40
Null Values 40
2．5 Relational Algebra 41
Fundamental Operations of Relational Algebra 41
2．6 Set-Theoretic Operations 42
The Union，Intersection，and Difference Operations 42
Assignment and Alias 44
The Product Operation 46
2．7 Native Relational Operations 47
The Projection Operation 48
The Selection Operation 49
Precedence of Relational Operations 52
The Join Operation 53
The Division Operation 57
2．8 The Interdependence of Operations 61
2．9 Illustrative Examples 63
2．10 Other Relational Operations 67
Outer Join 68
Theta Join 71
3．1 Introduction 79
Chapter 3 Basic SQL Query Language 79
SQL Capabilities 80
SQL History-Standards and Dialects 81
3．2 Setting Up the Database 84
Standard Typographical Conventions 85
A Practical Exercise 86
3．3 Simple Select Statements 87
3．4 Subqueries 96
The IN Predicate 96
The Quantified Comparison Predicate 101
The EXISTS Predicate 102
A Weakness of SQL：Too Many Equivalent Forms 106
3．5 UNION Operators and FOR ALL Conditions 107
The UNION Operator 107
Division：SQL“FOR ALL...”Conditions 108
3．6 Some Advanced SQL Syntax 114
The INTERSECT and EXCEPT Operators in Advanced SQL 114
Join forms in Advanced SQL 117
OUTER JOIN 120
Join Forms Implemented in Database Systems 122
3．7 Set Functions in SQL 123
Handling Null Values 127
3．8 Groups of Rows in SQL 129
3．9 A Complete Description of SQL Select 134
Identifiers 136
Expressions，Predicates，and the search_condition 137
Scalar Subqueries as Expressions：Advanced SQL 141
Basic SQL versus Advanced SQL：Summary 142
A Discussion of the Predicates 142
3．10 Insert，Update，and Delete Statements 148
The Insert Statement 148
The Update Statement 149
The Delete Statement 150
3．11 The Power of the Select Statement 151
The Non-Procedural Select Statement 152
Turing Power 154
Limited Power of the Basic SQL Select Statementk 155
Chapter 4 Object-Relational SQL 173
4．1 Introduction 173
ORSQL Capabilities 174
Form of Presntation for This Chapter 175
Object-Relational History 176
4．2 Objects and Tables 177
4.2.1 Object Types in ORACLE 177
Definition of the REF Object Reference 185
4.2.2 INFORMIX Row Types for Objects 191
Absence of REFs in INFORMIX 196
Type Inheritance in INFORMIX 196
4.2.3 Objects and Tables：Summary 197
Object Orientation 198
4．3 Collection Types 199
4．3．1 Collection Types in ORACLE 200
Table Types and Nested Tables 200
Two Techniques for Retrieving from a Table of Tables 205
Unnesting via Table Products 206
Nested Cursors 206
Array Types for VARRAYs 210
SQL Syntax for Collections in ORACLE 212
Inserts and Updates in ORACLE 213
4．3．2 Collection Types in INFORMIX 215
Sets in INFORMIX 215
Lists in INFORMIX 218
SQL Syntax for Collections in INFORMIX 220
Inserts and Updates in INFORMIX 221
4．3．3 Collection Types：Summary 222
4．4 Procedural SQL，User-Defined Functions（UDFs），and Methods 223
4．4．1 ORACLE PL/SQL Procedures，UDFs，and Methods 224
PL/SQL：ORACLE s Procedural SQL Language 224
Using PL/SQL to Implement Methods in ORACLE 229
Update Methods 237
4．4．2 INFORMIX User-Defined Functions 239
SPL：INFORMIX s Procedural SQL Language 239
Using SPL to Implement UDFs in INFORMIX 244
Update Functions 247
4．4．3 User-Defined Functions：Summary 248
4．5 External Functions and Packaged user-Defined Types（UDTs） 249
Binary Data and BLOBs 250
BLOB Objects 251
Distinct Types 251
Encapsulation 251
External Functions 251
Packaged UDTs and Other Encapsulated UDTs 252
Summary 253
Chapter 5 Programs to Access a Database 259
5．1 Introduction to Embedded SQL in C 262
A Simple Program Using Embedded SQL 262
Selecting Multiple Rows with a Cursor 270
5．2 Condition Handling 272
Whenever Statement：Scope and Flow of Control 274
Explicit Error Checking 276
Handling Errors：Getting Error Messages from the Database 277
Indicator Variables 278
5．3 Some Common Embedded SQL Statements 281
The Select Statement 281
The Declare Cursor Statement 282
The Delete Statement 283
The Update Statement 285
The Insert Statement 286
Cursor Open，Fetch，and Close 286
Other Embedded SQL Operations 287
5．4 Programming for Transactions 287
The Concept of a Transaction 288
How Transactions Are Specified in Programs 290
A Transaction Example 292
The Transaction Isolation Guarantee and Locking 294
Special Considerations in Transactions 296
5．5 The Power of Procedural SQL Programs 303
Customized Set Functions 303
5．6 Dynamic SQL 305
Execute Immediate 305
Prepare，Execute，and Using 309
Dynamic Select：The Describe Statement and the SQLDA 310
5．7 Some Advanced Programming Concepts 317
Scrollable Cursors 317
Cursor Sensitivity 319
Other Development Environments for Database Programming 320
Chapter 6 Database Design 329
6．1 Introduction to E-R Concepts 331
Entities，Attributes，and Simple E-R Diagrams 331
Transforming Entities and Attributes to Relations 334
Relationships among Entities 335
6．2 Further Details of E-R Modeling 339
Cardinality of Entity Participation in a Relationship 339
One-to-One，Many-to-Many，and Many-to-One Relationships 342
Transforming Binary Relationships to Relations 343
Cardinality of Attributes 346
6．3 Additional E-R Concepts 346
Weak Entities 347
Generalization Hierarchies 348
6．4 Case Study 349
6．5 Normalization：Preliminaries 353
A Running Example：Employee Information 354
Anomalies of a Bad Database Design 356
6．6 Functional Dependencies 358
Logical Implications among Functional Dependencies 361
Armstrong s Axioms 362
Closure，Cover，and Minimal Cover 366
6．7 Lossless Decompositions 374
6．8 Normal Forms 380
A Succession of Decompositions to Eliminate Anomalies 381
Normal Forms：BCNF，3NF，and 2NF 387
An Algorithm to Achieve Well-Behaved 3NF Decomposition 393
A Review of Normalization 394
6．9 Additional Design Considerations 396
Database Design Tools 397
Chapter 7 Integrity，Views，Security，and Catalogs 409
7．1 Integrity Constraints 410
Integrity Constraints in the Create Table Statement 411
Primary Keys，Foreign Keys，and Referential Integrity 417
Foreign Key Constraints：Product Variations 421
The Alter Table Statement 422
Non-Procedural and procedural Integrity Constraints：Triggers 424
7．2 Creating Views 433
Updatable and Read-Only views 438
The Value of Views 440
7．3 Security：The Grant Statement in SQL 442
Variations in Database Products 445
7．4 System Catalogs and Schemas 445
Catalog Variations in Database Products 447
Schemas 447
The INFORMIX System Catalog 451
Catalog Tables for Object-Relational Constructs：ORACLE and INFORMIX 452
Chapter 8 Indexing 465
8．1 The Concept of Indexing 465
8．2 Disk Storage 470
Disk Access Is Excruciatingly Slow 470
The DBA and Disk Resource Allocation in ORACLE 474
Data Storage Pages and Row Pointers：ORACLE and DB2 UDB 479
8．3 The B-Tree Index 484
Dynamic Changes in the B-Tree 490
Properties of the B-Tree 493
Index Node Layout and Free Space 494
The Create Index Statement in ORACLE and DB2 UDB 496
Duplicate Key Values in an Index 497
The ORACLE Bitmap Index 500
8．4 Clustered and Non-Clustered Indexes 502
Clustering Indexes in DB2 UDB 506
ORACLE Special Indexing Features 507
8．5 A Hash Primary Index 511
Tuning HASHKEYS and SIZE in a Hash Cluster 514
No Incremental Changes in the Number of Slots Used 517
Advantages and Disadvantages of a Hash Primary Index 518
Unlimited Slot Occupancy：How Many Slots Are Occupied？ 520
8．6 Throwing Darts at Random Slots 520
Slot Occupancy of One：Number of Retries（Rehash Chain） 522
When Do Hash Pages Fill Up 525
Chapter 9 Query Processing 533
9．1 Introductory Concepts 535
Query Resource Utilization 535
Gathering Statistics 537
Retrieving the Query Plan 538
9．2 Tablespace Scans and I/O 540
Assumptions about I/O 541
9．3 Simple Indexed Access in DB2 546
Equal Unique Match Index Access 549
Index-Only Retrieval 554
9．4 Filter Factors and Statistics 556
DB2 Statistics 557
Filter Factors in DB2 559
9．5 Matching Index Scans，Composite Indexes 560
Definition of a Matching Index Scan 563
Predicate Screening and Screening Predicates 564
Indexable Predicates and Performance 568
9．6 Multiple Index Access 570
List Prefetch and the RID Pool 574
Point of Diminishing Returns in Multiple Index Access 578
9．7 Methods for Joining Tables 579
Nested-Loop Join 580
Merge Join 583
Hybrid Join 587
Multiple Table Joins 588
Transforming Nested Queries to Joins 589
9．8 Disk Sorts 591
The N-Way Merge Disk Sort Algorithm 592
9．9 Query kPerformance Benchmarks：A Case Study 597
The BENCH Table 599
Load measurements 601
9．10 Query Performance Measurements 602
Query Q1 603
Query Q2A 604
Query Q2B 607
Query Q3A 608
Query Q3B 609
Query Q4A and Q4B 610
Query Q5 613
Query Q6A 615
Query Q6B 616
9．11 Cost-Performance Assessment 617
Elapsed Time versus CPU Time Rating 618
Customizing the Rating 619
Variations in Indexing Use between DB2 and ORACLE 620
Chapter 10 Update Transactions 633
10．1 Transactional Histories 638
Fundamental Atomic Read and Write Actions in the Database 638
Predicate Read Actions 639
Transactional Histories with Reads and Writes 639
10．2 Interleaved Read-Write Operations 643
10．3 Serializability and the Precedence Graph 648
The Precedence Graph 653
10．4 Locking to Ensure Serializability 655
The waits-For Graph 658
10．5 Levels of Isolation 660
The Read Uncommitted Isolation Level 662
The Read Committed Isolation Level and Cursor Stability 663
Repeatable Read Isolation Level 666
Serializability and Phantom Updates 666
10．6 Transactional Recovery 669
10．7 Recovery in Detail：Log Formats 671
Guarantees That Needed Log Entries Are on Disk 675
10．8 Checkpoints 677
The Commit-Consistent Checkpoint 678
Motivation for Other Kinds of Checkpoints 679
The Cache-Consistent Checkpoint 680
The Fuzzy Checkpoint 682
10．9 Media Recovery 683
Stable Storage 684
10．10 Performance：The TPC-A Benchmark 684
The TPC-A Benchmark Specification 685
Lessons from the TPC-A Benchmark 689
Chapter 11 Parallel and Distributed Databases 699
11．1 Some Multi-CPU Architectures 700
Client-Server Architectures 703
11．2 The Curve of CPU Cost versus Power 704
11．3 Shared-Nothing Database Architecture 706
Two-Phase Commit 707
Further Problems with Shared-Nothing Architecture 713
11．4 Query Parallelism 715
Intra-Query Parallelism 715
Appendix A Introductory Tutorial 721
A．1 Setting Up the CAP Database in ORACLE 721
Creating the CAP Database 721
Using the SQL *Loader 722
Using SQL *Plus 725
A．2 Setting Up the CAP Database in INFORMIX 728
Creating the CAP Database（in UNIX） 728
Using DB-Access（UNIX Systems） 732
A．3 Datatypes 733
Appendix B Programming Details 735
B．1 The prompt（）Function 735
Use of Numeric Constants in Code Examples 736
B．2 The print_dberror（）Function 737
B．3 Building Embedded C Programs 737
Precompilation and Compilation Procedure Using ORACLE/UNIX 737
Precompilation and Compilation Procedure Using DB2 UDB/UNIX 740
Appendix C SQL Statement Syntax 743
C．2 Close Cursor statement 745
C．1 Alter Table Statement 745
C．3 Commit Work Statement 746
C．4 Connect Statement 747
C．5 Create Function Statement（UDF） 748
C．6 Create Index Statement 749
C．7 Create Row Type Statement（O-R） 751
C．8 Create Schema Statement 751
C．9 Create Table Statement 752
C．10 Create Tablespace Statement in ORACLE and DB2 UDB 755
C．11 Create Trigger Statement 756
C．12 Create Type Statement（O-R） 757
C．13 Create View Statement 758
C．14 Declare Cursor Statement 759
C．15 Delete Statement 760
C．16 Describe Statement 761
C．17 Disconnect Statement 761
C．18 Drop Function Statement 762
C．19 Drop Index Statement 762
C．20 Drop Trigger Statement 763
C．21 Drop （Row）Type Statement（O-R） 763
C．22 Drop {Schema|Table|View}Statement 764
C．23 Execute Statement 764
C．25 Fetch Statement 765
C．24 Execute Immediate Statement 765
C．26 Grant Statement 766
C．27 Insert Statement 767
C．28 Open Crsor Statement 768
C．29 Prepare Statement 768
C．30 Revoke Statement 768
C．31 Rollback Statement 769
C．32 Select Statement 769
C．33 Update Statement 773
Appendix D Set Query Counts 775
Solutions to Selected Exercises 779
Index 823