A Guide to Developing Client - Server SQL Applications / Edition 1

Table of Contents

Preface
1: An Introduction to Client-Server Technology
1.1 Trends and Forces at Work in the Database Market
1.2 A Historical Perspective
1.3 Client-Database-Server Architecture
1.4 SQL and the Relational DBMS
1.5 Opportunities and Obstacles for Client-Server Technology
1.6 The Database Server
1.7 Evaluating Features of Individual Database SERvers
1.7.1 Implementation of Standard SQL
1.7.2 Connectivity and Remote Database Access
1.7.3 Database Objects and Data Dictionary
1.7.4 Optimization and Performance
1.7.5 Data Integrity and Transaction Management
1.7.6 Concurrency Control
1.7.7. Security and Authorization Checking
1.7.8 Database Administration Facilities
1.8 Survey of Currently Available Database Servers
1.8.1 IMB OS/2 Extended Edition Database Manager
1.8.2 ORACLE Server
1.8.3 DEC Rdb/VMS
1.9 Summary

2: Relational Database Design
2.1 Introduction
2.1.1 Top-down Design
2.2 Integrity Constraints
2.2.1 Key Constraints
2.2.2 Referential/Foreign Key Constraints
2.2.3 NOT NULL Constraints
2.2.4 Domain Constraints
2.2.5 Integrity Rules
2.2.6 Triggers
2.3 Normalization
2.3.1 Dependency Relationships
2.3.2 First Normal Form
2.3.3 Second Normal Form
2.3.4 Third Normal Form
2.4 Entity-relationship Data Modeling
2.4.1 Alternative Consturcts and Extensions
2.4.2 Mapping ER Diagrams Onto Relational Schemata
2.5 When Is a DBMS Relational?
2.5.1 Foundational Rules: Rule 0 and Rule 12
2.5.2 Structural Rules: Rule 1 and Rule 6
2.5.3 Integrity Rules: Rule 3 and Rule 10
2.5.4 Data Manipulation Rules: Rules 2, 4, 5, and 7
2.5.5 Physical Data Independence: Rule 8
2.5.6 Logical Data Independence: Rule 9
2.5.7 Distribution Independence: Rule 11
2.5.8 Advantages of Conforming to Strict Relational Criteria
2.6 Summary

3: Data Definition
3.1 An Overview of Data Definition in SQL
3.2 Data Types
3.2.1 Character Data Type
3.2.2 ANSI SQL89 Number Data Type
3.2.3 SAG Data Types
3.2.4 SAA Data Types
3.3 Databases and Schemata
3.4 Tables
3.5 Views
3.6 Privileges
3.7 Integrity Constraints
3.7.1 NULL Constraints
3.7.2 Unique and Primary Key Constraints
3.7.3 Foreign Keys and Referential Integrity Constraints
3.7.4 CHECK Constraint
3.8 System Changes
3.9 Case Studies
3.9.1 IBM OS/2 Extended Edition Database Manager
3.9.2 ORACLE Server
3.9.3 DEC Rdb/VMS
3.9.4 Microsoft SQL Server
3.10 Summary

4: Data Manipulation
4.1 An Overview of Data Manipulation in SQL
4.1.1 Cursor Operations
4.1.2 Noncursor Operations
4.1.3 Transaction Termination Operations
4.2 Noncursor Operation Statements
4.2.1 INSERT Statement
4.2.2 SELECT Statement
4.2.3 DELETE Statement
4.2.4 UPDATE Statement
4.3 The Treatment of Nulls
4.3.1 Three-valued Logic
4.3.2 SQL Access Implicit (System) Comparisons
4.4 Case Studies
4.4.1 IBM OS/2 Extended Edition Database Manager
4.4.2 ORACLE Server
4.4.3 DEC Rdb/VMS
4.4.4 Microsoft SQL Server
4.5 Summary

5: Transaction Processing
5.1 Transaction Definition and Example
5.1.1 Atomicity
5.1.2 Consistency
5.1.3 Isolation
5.1.4 Durability
5.1.5 Scope of a Transaction
5.2 Concurrency Contorl
5.2.1 Locking
5.2.2 Muliversion Concurrency Control
5.2.3 Optimistic Concurrency Control
5.3 Recovery Management
5.4 Case Studies
5.4.1 IBM OS/2 Extended Edition Database Manager
5.4.2 ORACLE Server
5.4.3 DEC Rdb/VMS
5.4.4 Microsoft SQL Server
5.5 Summary

6: Database Administration and Maintenance
6.1 Installation
6.2 Database Startup and Shutdown
6.3 Enrolling and Dropping Users
6.4 Checkpointing
6.5 Backup
6.6 Recovery
6.7 Data Import and Export
6.8 Authorization Administration
6.9 Auditing
6.10 Case Studies
6.10.1 IBM OS/2 Extended Edition Database Manager
6.10.2 ORACLE Server
6.10.3 DEC Rdb/VMS
6.10.4 Microsoft SQL Server
6.11 Summary

7: Performance Tuning and Optimization
7.1 Computational Architecture
7.2 Physical Dtabase Design
7.2.1 Indexing
7.2.2 Clustering
7.2.3 Distributing Input/Output
7.3 Query Optimization
7.3.1 Heuristics-baesd Optimization
7.3.2 Cost-based Optimization
7.3.3 Flattening of Nested SQL
7.3.4 Timing of Optimization
7.4 Minimizing Communication Overhead
7.5 Concurrency Control Tuning
7.6 Buffering Strategy
7.7 Case Studies
7.7.1 EBM OX/2 Extended Edition Database Manager
7.7.2 ORACLE Server
7.7.3 DEC Rdb/VMS
7.7.4 Microsoft SQL Server
7.8 Summary

8: Benchmarking
8.1 Why Benchmarks?
8.2 Performance Metrics
8.3 Wisconsin Benchmark
8.3.1 Benchmark Structure
8.3.2 Critique
8.4 AS3AP Benchmark
8.4.1 Test Database Generation
8.4.2 Scaling
8.4.3 Operational Issues
8.4.4 Single-user Tests
8.4.5 Multiuser Tests
8.4.6 Performance Metrics
8.5 TP1/DebitCredit Benchmark
8.5.1 Common Departures in TP1 Implementations
8.5.2 Critique
8.6 TPC Benchmarks
8.6.1 Clarifications From TP1DebitCredit
8.6.2 Differences Between TPC-A and TPC-B
8.7 Published Benchmark Results
8.7.1 ORACLE Server
8.7.2 Microsoft SQL Server
8.8 Sample TP1 Implementations
8.8.1 A TP1 Implementation on the IBM Extended Edition Database Manager
8.8.2 A TP1 Implementation on the ORACLE Server
8.8.3 A TP1 Implementation on DEC's Rdb/VMS
8.8.4 A TP1 Implementation on the Microsoft SQL Server
8.9 Summary

9: Application Programming Interface
9.1 Types of Interfaces
9.1.1 Dynamic SQL
9.1.2 Host Language Embedding
9.1.3 Module Language
9.1.4 Callable Function Library Interface
9.2 Cursor Support
9.2.1 Update Where Current of Cursor
9.2.2 Delete Where Current of Cursor
9.3 Error Handling
9.3.1 SQLCA
9.3.2 SQLCODE
9.3.3 X/Open SQL: SQLSTATE
9.3.4 WHENEVER Statement
9.3.5 X/Open SQL: GET DIAGNOSTICS
9.4 X/Open Association Management
9.4.1 CONNECT
9.4.2 SET CONNECTION
9.4.3 DISCONNECT
9.5 Case Studies
9.5.1 IBM OS/2 Extended Edition Database Manager
9.5.2 ORACLE Server
9.5.3 DEC Rdb/VMS
9.5.4 Microsoft SQL Server
9.6 Summary

10: Distributed Database Systems
10.1 Why Distributed Databases?
10.2 Approaches for Developing a Distributed Database
10.2.1 Bottom-up Integration
10.2.2 Top-down Distribution
10.3 Spectrum of Distributed Capabilities
10.3.1 Autonomy
10.3.2 Distribution Transparency
10.3.3 Heterogeneity
10.4 Technical Problems
10.4.1 Directory Management
10.4.2 Distributed Concurrency Control
10.4.3 Distributed Transaction Management
10.4.4 Distributed Database Administration
10.4.5 Distributed Query Optimization
10.4.6 Distributed Integrity Maintenance
10.5 Truly Distributed Capabilities (Date's Rules)
10.6 Case Studies
10.6.1 IBM OS?2 Extended Edition Database Manager
10.6.2 ORACLE Server
10.6.3 DEC Rdb/VMS
10.6.4 Microsoft SQL Server
10.7 Summary

11: SQL Extensions for Next Generation Applications
11.1 The Evolution of Intelligent Databases
11.2 SQL2 and SQL3 Directions
11.2.1 SQL2
11.2.2 SQL3
11.3 Control Structures in SQL
11.4 Intelligent Databases
11.4.1 The Intelligent Database Architecture
11.4.2 The Deductive Object-oriented Data Model
11.5 Summary

12: Summary
12.1 Foundation of Client-Server Computing
12.2 Server Technologies
12.3 Application Programming Interfaces
12.4 Twelve Rules for Client Applications
12.5 Future Evolution of Client-Server Computing

Appendix: Guidelines for Client Application Software
Bibliography
Index

Read More