Products Database Book information Product description Audience Author information and services Ordering information Bibliographic and ordering information Conditions of sale Book-related information Submit your book proposal Other books in same subject area Support & contact About Elsevier Select your view

	DATABASE Principles, Programming, and Performance, Second Edition To order this title, and for more information, click here By Patrick O'Neil Elizabeth O'Neil Description This second edition relies on the same successful approach that distinguished the first: it covers the principles of database theory with unmatched thoroughness, and it rigorously links theory to the real world of database programming and administration. A careful discussion of SQL standards and a multitude of examples drawn from actual databases-Oracle, DB2, and Informix-complements the authors' concept-oriented instruction, allowing you to develop product-specific understanding and to learn the important differences between the SQL dialects that will enable you to write portable applications. Audience Database designers, implementers, and students. Contents Foreword --Jim Gray, Microsoft Preface Chapter 1 Introduction CHANGES- presents an all-new introductory discussion of the object-relational model. 1.1 Fundamental Database Concepts History of Database Systems The Relational and Object-Relational Models The Database Systems Covered A Relational Database Example An Object-Relational Database Example 1.2 Database Users 1.3 Overview of Relational and Object-Relational DBMS Chapter 2: The Relational Model Chapter 3: Basic SQL Query Language Chapter 4: The Object-Relational Model Chapter 5: Programs to Access a Database Chapter 6: Database Design Chapter 7: Integrity, Views, Security, and Catalogs Chapter 8: Indexing Chapter 9: Query Processing Chapter 10: Update Transactions Chapter 11: Parallel and Distributed Databases 1.4 Putting It All Together Chapter 2 The Relational Model CHANGES- strategically clarifies details presented in the first edition. 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 Suggestions for Further Reading Exercises Chapter 3 Basic SQL Query Language CHANGES- examines features added to basic query language SQL, including advanced syntax supported by specific products. 3.1 Introduction SQL Capabilities SQL History-Standards and Dialects 3.2 Setting Up the Database?Typographical Conventions Standard Typographical Conventions A Practical Exercise 3.3 Simple Select Statements 3.4 Subqueries The IN Predicate The Quantified Comparison Predicate The EXISTS Predicate A Weakness of SQL: Too Many Equivalent Forms 3.5 UNION Operators and FOR ALL Conditions The UNION Operator Division: SQL "FOR ALL . . ." Conditions 3.6 Some Advanced SQL Syntax The INTERSECT and EXCEPT Operators in Advanced SQL Join Forms in Advanced SQL Outer Join Join Forms Implemented in Database Systems 3.7 Set Functions in SQL Handling Null Values 3.8 Groups of Rows in SQL 3.9 A Complete Description of SQL Select Identifiers Expressions, Predicates, and the search_condition Scalar Subqueries as Expressions: Advanced SQL Basic SQL versus Advanced SQL: Summary A Discussion of the Predicates 3.10 Insert, Update, and Delete Statements The Insert Statement The Update Statement The Delete Statement 3.11 The Power of the Select Statement The Non-Procedural Select Statement Turing Power Limited Power of the Basic SQL Select Statement Suggestions for Further Reading Exercises Chapter 4 Object-Relational SQL CHANGES: an entirely new chapter on object-relational SQL, including a side-by-side comparison of features supported by various products. 4.1 Introduction ORSQL Capabilities Form of Presentation for This Chapter Object Relational History 4.2 Objects and Tables 4.2.1 Object Types in ORACLE Definition of the REF Object reference 4.2.2 INFORMIX Row Types for Objects Absence of REFs in INFORMIX Type Inheritance in INFORMIX 4.2.3 Objects and Tables: Summary Object-Orientation 4.3 Collection Types 4.3.1 Collection Types in ORACLE Table Types and Nested Tables Nested Cursors for Retrieving from a Table of Tables Array Types for VARRAYs SQL Syntax for Collections in ORACLE Inserts and Updates in ORACLE 4.3.2 Collection Types in INFORMIX SQL Syntax for Collections in INFORMIX Inserts and updates in INFORMIX. 4.3.3 Collection Types: Summary 4.4 Procedural SQL, User-Defined Functions (UDFs), and Methods 4.4.1 ORACLE PL/SQL Procedures, UDFs and Methods PL/SQL: ORACLE's Procedural SQL Language Using PL/SQL to implement Methods in ORACLE Update Methods 4.4.2 INFORMIX User-Defined Functions SPL: INFORMIX's Procedural SQL Language Using SPL to Implement UDFs in INFORMIX Update Functions 4.4.3 User-Defined Functions: Summary 4.5 External Functions and Packaged User-Defined Types (UDTs) Binary Data and BLOBS External Functions Encapsulation Distinct Types BLOB Objects Packaged UDTs and Other Encapsulated UDTs Summary Suggestions for Further Reading Exercises Chapter 5 Programs to Access a Database CHANGES- adds examples illustrating transaction processing techniques and presents improved coverage of error-handling. 5.1 Introduction to Embedded SQL in C A Simple Program Using Embedded SQL Selecting Multiple Rows with a Cursor 5.2 Condition Handling Whenever Statement: Scope and Flow of Control Explicit Error Checking Handling Errors: Getting Error Messages from the Database Indicator Variables 5.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 5.4 Programming for Transactions The Concept of a Transaction How Transactions Are Specified in Programs A Transaction Example The Transaction Isolation Guarantee and Locking Special Considerations in Transactions 5.5 The Power of Procedural SQL Programs Customized Set Functions Dynamic SQL 5.6 Execute Immediate Prepare, Execute, and Using Dynamic Select: The Describe Statement and the SQLDA 5.7 Some Advanced Programming Concepts Scrollable Cursors Cursor Sensitivity Other Development Environments for Database Programming Suggestions for Further Reading Exercises Chapter 6 Database Design CHANGES- clarifies a number of points regarding logical database design and adds a number of examples. 6.1 Introduction to E-R Concepts Entities, Attributes, and Simple E-R Diagrams Transforming Entities and Attributes to Relations Relationships among Entities 6.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 6.3 Additional E-R Concepts Cardinality of Attributes Weak Entities Generalization Hierarchies 6.4 Case Study 6.5 Normalization: Preliminaries A Running Example: Employee Information Anomalies of a Bad Database Design 6.6 Functional Dependencies Logical Implications among Functional Dependencies Armstrong's Axioms Closure, Cover, and Minimal Cover 6.7 Lossless Decompositions 6.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 6.9 Additional Design Considerations Database Design Tools Suggestions for Further Reading Exercises Chapter 7 Integrity, Views, Security, and Catalogs CHANGES- updates coverage and examples relating to integrity, views, security, and catalogs. 7.1 Integrity Constraints Integrity Constraints in the Create Table Statement Primary Keys, Foreign Keys, and Referential Integrity Foreign Key Constraints: Product Variations The Alter Table Statement Non-Procedural and Procedural Integrity Constraints: Triggers 7.2 Creating Views Updatable and Read-Only Views The Value of Views 7.3 Security: The Grant Statement in SQL Variations in Database Products 7.4 System Catalogs Catalog Variations in Database Products The INFORMIX System Catalog Catalog Tables for Object-Relational Constructs: ORACLE and INFORMIX Suggestions for Further Reading Exercises Chapter 8 Indexing CHANGES-updates discussion and examples relating to a wide range of new indexing techniques. 8.1 The Concept of Indexing 8.2 Disk Storage Disk Access Is Excruciatingly Slow The DBA and Disk Resource Allocation in ORACLE Data Storage Pages and Row Pointers: ORACLE and DB2 UDB 8.3 The B-Tree Index Dynamic Changes in the B-Tree Properties of the B-Tree Index Node Layout and Free Space The Create Index Statement in ORACLE and DB2 UDB Duplicate Key Values in an Index The ORACLE Bitmap Index 8.4 Clustered and Non-Clustered Indexes Clustering Indexes in DB2 UDB ORACLE Special Indexing Features 8.5 A Hash Primary Index Tuning HASHKEYS and SIZE in a Hash Cluster No Incremental Changes in the Number of Slots Used Advantages and Disadvantages of a Hash Primary Index 8.6 Throwing Darts at Random Slots Unlimited Slot Occupancy: How Many Slots Are Occupied? Slot Occupancy of One: Number of Retries (Rehash Chain) When Do Hash Pages Fill Up Suggestions for Further Reading Exercises Chapter 9 Query Processing CHANGES- updates query processing instruction to reflect new product features; retains coverage of IBM mainframe DB2 query features because of its usefulness in introducing query processing to students. 9.1 Introductory Concepts Query Resource Utilization Gathering Statistics Retrieving the Query Plan 9.2 Tablespace Scans and I/O Assumptions about I/O 9.3 Simple Indexed Access in DB2 Equal Unique Match Index Access Index-Only Retrieval 9.4 Filter Factors and Statistics DB2 Statistics Filter Factors in DB2 9.5 Matching Index Scans, Composite Indexes Definition of a Matching Index Scan Predicate Screening and Screening Predicates Indexable Predicates and Performance 9.6 Multiple Index Access List Prefetch and the RID Pool Point of Diminishing Returns in Multiple Index Access 9.7 Methods for Joining Tables Nested Loop Join Merge Join Hybrid Join Multiple Table Joins Transforming Nested Queries to Joins 9.8 Disk Sorts The N-Way Merge Disk Sort Algorithm 9.9 Query Performance Benchmarks: A Case Study The BENCH Table Load Measurements 9.10 Query Performance Measurements Query Q1 Query Q2A Query Q2B Query Q3A Query Q3B Queries Q4A and Q4B Query Q5 Query Q6A Query Q6B 9.11 Cost-Performance Assessment Elapsed Time versus CPU Time Rating Customizing the Rating Variations in Indexing Use between DB2 and ORACLE Suggestions for Further Reading Exercises Chapter 10 Update Transactions CHANGES- presents improved definitions and proofs, particularly those relating to levels of isolation. 10.1 Transactional Histories Fundamental Atomic Read and Write Actions in the Database Predicate Read Actions Transactional Histories with Reads and Writes 10.2 Interleaved Read/Write Operations 10.3 Serializability and the Precedence Graph The Precedence Graph 10.4 Locking to Ensure Serializability The Waits-For Graph 10.5 Levels of Isolation The Read Uncommitted Isolation Level The Read Committed Isolation Level and Cursor Stability Repeatable Read Isolation Level Serializability and Phantom Updates 10.6 Transactional Recovery 10.7 Recovery in Detail: Log Formats Guarantees That Needed Log Entries Are on Disk 10.8 Checkpoints The Commit-Consistent Checkpoint Motivation for Other Kinds of Checkpoints The Cache-Consistent Checkpoint The Fuzzy Checkpoint 10.9 Media Recovery Stable Storage 10.10 Performance: The TPC-A Benchmark The TPC-A Benchmark Specification Lessons from the TPC-A Benchmark Suggestions for Further Reading Exercises Chapter 11 Parallel and Distributed Databases 11.1 Some Multi-CPU Architectures Client-Server Architectures 11.2 The Curve of CPU Cost versus Power 11.3 Shared-Nothing Database Architecture Two-Phase Commit Further Problems with Shared-Nothing Architecture 11.4 Query Parallelism Intra-Query Parallelism Suggestions for Further Reading Exercises Appendix A Introductory Tutorial A.1 Setting Up the CAP Database in ORACLE Creating the CAP Database Using the SQL Loader Using SQL Plus A.2 Setting Up the CAP Database in INFORMIX Creating the CAP Database (in UNIX) Using DB-Access (UNIX systems) A.3 Datatypes Appendix B Programming Details B.1 The prompt() function Use of Numeric Constants in Code Examples B.2 The print_dberror() function B.3 Building Embedded C Programs Precompilation and Compilation Procedure Using ORACLE/UNIX Precompilation and Compilation Procedure Using DB2 UDB/UNIX Appendix C SQL Statement Syntax C.1 Alter Table Statement C.2 Close Cursor Statement C.3 Commit Work Statement C.4 Connect Statement C.5 Create Function Statement (UDF) C.6 Create Index Statement C.7 Create Row Type Statement (O-R) C.8 Create Schema Statement C.9 Create Table Statement C.10 Create Tablespace Statement in ORACLE and DB2 UDB C.11 Create Trigger Statement C.12 Create Type Statement (O-R) C.13 Create View Statement C.14 Declare Cursor Statement C.15 Delete Statement C.16 Describe Statement C.17 Disconnect Statement C.18 Drop Function Statement C.19 Drop Index Statement C.20 Drop Trigger Statement C.21 Drop (Row) Type Statement (O-R) C.22 Drop {Schema Bibliographic details Hardbound, 870 pages, publication date: APR-2000 ISBN-13: 978-1-55860-438-4 ISBN-10: 1-55860-438-3 Imprint: MORGAN KAUFFMAN Price and Ordering Price: USD 113 EUR 95.95 GBP 64.99 Books and book related electronic products are priced in US dollars (USD), euro (EUR), and Great Britain Pounds (GBP). USD prices apply to the Americas and Asia Pacific. EUR prices apply in Europe and the Middle East. GBP prices apply to the UK and all other countries. See also information about conditions of sale & ordering procedures, and links to our regional sales offices. 077/762 Last update: 27 Sep 2008	Book contents Table of contents Reviews View other people's reviews Submit your review Bookmark this page Recommend this publication Overview of all books