数据库 原理、编程与性能 第2版 英文PDF电子书下载

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