Database

PrefaceChapter 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 TogetherChapter 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 JoinChapter 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-Orientation in Database Systems Object-Oriented Database Systems Extended Relational Database SystemsChapter 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 LanguagesChapter 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 a 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 ToolsChapter 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 ProductsChapter 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 a 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 a 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 Queries Q4A and Q4B Query Q5 Query Q6A Query Q6B 8.11 Cost-Performance Assessment Elapsed Time Versus CPU Time Rating Customizing the RatingChapter 9 Update Transactions 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 BenchmarkChapter 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 Intra-Query ParallelismAppendix A Introductory Tutorial A.1 Setting Up the CAP Database in INGRES A.2 Setting Up the CAP Database in ORACLE A.3 DatatypesAppendix 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 Connect Statement B.9 Declare Cursor Statement B.10 Delete Statement B.11 Describe Statement B.12 Disconnect Statement B.13 Drop {Table I View I Index} Statement B.14 Execute Statement B.15 Execute Immediate Statement B.16 Fetch Statement B.I7 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 StatementAppendix C Set Query CountsSolutions to Selected ExercisesIndex