Database - 1st Edition - ISBN: 9781483184043


1st Edition

Principles Programming Performance

Authors: Patrick O'Neil
eBook ISBN: 9781483184043
Imprint: Morgan Kaufmann
Published Date: 12th May 2014
Page Count: 888
Sales tax will be calculated at check-out Price includes VAT/GST
15% off
15% off
15% off
Price includes VAT/GST
× DRM-Free

Easy - Download and start reading immediately. There’s no activation process to access eBooks; all eBooks are fully searchable, and enabled for copying, pasting, and printing.

Flexible - Read on multiple operating systems and devices. Easily read eBooks on smart phones, computers, or any eBook readers, including Kindle.

Open - Buy once, receive and download all available eBook formats, including PDF, EPUB, and Mobi (for Kindle).

Institutional Access

Secure Checkout

Personal information is secured with SSL technology.

Free Shipping

Free global shipping
No minimum order.


Database: Principles Programming Performance provides an introduction to the fundamental principles of database systems. This book focuses on database programming and the relationships between principles, programming, and performance.

Organized into 10 chapters, this book begins with an overview of database design principles and presents a comprehensive introduction to the concepts used by a DBA. This text then provides grounding in many abstract concepts of the relational model. Other chapters introduce SQL, describing its capabilities and covering the statements and functions of the programming language. This book provides as well an introduction to Embedded SQL and Dynamic SQL that is sufficiently detailed to enable students to immediately start writing database programs. The final chapter deals with some of the motivations for database systems spanning multiple CPUs, including client-server and distributed transactions.

This book is a valuable resource for database administrators, application programmers, specialist users, and end users.

Table of Contents


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-Orientation in Database Systems

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 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 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 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 Rating

Chapter 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 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

Intra-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 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 Statement

Appendix C Set Query Counts

Solutions to Selected Exercises



No. of pages:
© Morgan Kaufmann 1994
Morgan Kaufmann
eBook ISBN:

About the Author

Patrick O'Neil

Patrick O'Neil is a professor of computer science at the University of Massachusetts at Boston. He is responsible for a number of important research results in transactional performance and disk access algorithms, and he holds patents for his work in these and other database areas. Author of "The Set Query Benchmark" (in The Benchmark Handbook for Database and Transaction Processing Systems, also from Morgan Kaufmann) and an area editor for Information Systems, O'Neil is also an active industry consultant who has worked with a number of prominent companies, including Microsoft, Oracle, Sybase, Informix, Praxis, Price Waterhouse, and Policy Management Systems Corporation.

Ratings and Reviews