Database

Foreword --Jim Gray, MicrosoftPreface Chapter 1 IntroductionCHANGES- presents an all-new introductory discussion of the object-relational model. 1.1 Fundamental Database ConceptsHistory of Database SystemsThe Relational and Object-Relational ModelsThe Database Systems CoveredA Relational Database ExampleAn Object-Relational Database Example1.2 Database Users1.3 Overview of Relational and Object-Relational DBMSChapter 2: The Relational ModelChapter 3: Basic SQL Query LanguageChapter 4: The Object-Relational ModelChapter 5: Programs to Access a DatabaseChapter 6: Database DesignChapter 7: Integrity, Views, Security, and CatalogsChapter 8: IndexingChapter 9: Query ProcessingChapter 10: Update TransactionsChapter 11: Parallel and Distributed Databases1.4 Putting It All TogetherChapter 2 The Relational ModelCHANGES- strategically clarifies details presented in the first edition. 2.1 The CAP Database2.2 Naming the Parts of a DatabaseDomains and DatatypesTables and Relations2.3 Relational Rules2.4 Keys, Superkeys, and Null ValuesNull Values2.5 Relational AlgebraFundamental Operations of Relational Algebra2.6 Set-Theoretic OperationsThe Union, Intersection, and Difference OperationsAssignment and AliasThe Product Operation2.7 Native Relational OperationsThe Projection OperationThe Selection OperationPrecedence of Relational OperationsThe Join OperationThe Division Operation2.8 The Interdependence of Operations2.9 Illustrative Examples2.10 Other Relational OperationsOuter JoinTheta JoinSuggestions for Further ReadingExercisesChapter 3 Basic SQL Query LanguageCHANGES- examines features added to basic query language SQL, including advanced syntax supported by specific products. 3.1 IntroductionSQL CapabilitiesSQL History-Standards and Dialects3.2 Setting Up the Database—Typographical ConventionsStandard Typographical ConventionsA Practical Exercise3.3 Simple Select Statements3.4 SubqueriesThe IN PredicateThe Quantified Comparison PredicateThe EXISTS PredicateA Weakness of SQL: Too Many Equivalent Forms3.5 UNION Operators and FOR ALL ConditionsThe UNION OperatorDivision: SQL "FOR ALL . . ." Conditions3.6 Some Advanced SQL SyntaxThe INTERSECT and EXCEPT Operators in Advanced SQLJoin Forms in Advanced SQLOuter JoinJoin Forms Implemented in Database Systems3.7 Set Functions in SQLHandling Null Values3.8 Groups of Rows in SQL3.9 A Complete Description of SQL SelectIdentifiersExpressions, Predicates, and the search_conditionScalar Subqueries as Expressions: Advanced SQLBasic SQL versus Advanced SQL: SummaryA Discussion of the Predicates3.10 Insert, Update, and Delete Statements The Insert Statement The Update StatementThe Delete Statement3.11 The Power of the Select StatementThe Non-Procedural Select StatementTuring PowerLimited Power of the Basic SQL Select StatementSuggestions for Further ReadingExercisesChapter 4 Object-Relational SQLCHANGES: an entirely new chapter on object-relational SQL, including a side-by-side comparison of features supported by various products. 4.1 IntroductionORSQL CapabilitiesForm of Presentation for This ChapterObject Relational History4.2 Objects and Tables4.2.1 Object Types in ORACLEDefinition of the REF Object reference4.2.2 INFORMIX Row Types for ObjectsAbsence of REFs in INFORMIXType Inheritance in INFORMIX4.2.3 Objects and Tables: SummaryObject-Orientation4.3 Collection Types4.3.1 Collection Types in ORACLETable Types and Nested TablesNested Cursors for Retrieving from a Table of TablesArray Types for VARRAYsSQL Syntax for Collections in ORACLEInserts and Updates in ORACLE4.3.2 Collection Types in INFORMIXSQL Syntax for Collections in INFORMIX Inserts and updates in INFORMIX.4.3.3 Collection Types: Summary4.4 Procedural SQL, User-Defined Functions (UDFs), and Methods4.4.1 ORACLE PL/SQL Procedures, UDFs and MethodsPL/SQL: ORACLE's Procedural SQL LanguageUsing PL/SQL to implement Methods in ORACLEUpdate Methods4.4.2 INFORMIX User-Defined FunctionsSPL: INFORMIX's Procedural SQL LanguageUsing SPL to Implement UDFs in INFORMIXUpdate Functions4.4.3 User-Defined Functions: Summary4.5 External Functions and Packaged User-Defined Types (UDTs)Binary Data and BLOBSExternal FunctionsEncapsulationDistinct TypesBLOB ObjectsPackaged UDTs and Other Encapsulated UDTsSummarySuggestions for Further ReadingExercisesChapter 5 Programs to Access a DatabaseCHANGES- adds examples illustrating transaction processing techniques and presents improved coverage of error-handling. 5.1 Introduction to Embedded SQL in CA Simple Program Using Embedded SQLSelecting Multiple Rows with a Cursor5.2 Condition HandlingWhenever Statement: Scope and Flow of ControlExplicit Error CheckingHandling Errors: Getting Error Messages from the DatabaseIndicator Variables5.3 Some Common Embedded SQL Statements The Select Statement The Declare Cursor StatementThe Delete StatementThe Update StatementThe Insert StatementCursor Open, Fetch, and CloseOther Embedded SQL Operations5.4 Programming for TransactionsThe Concept of a TransactionHow Transactions Are Specified in ProgramsA Transaction ExampleThe Transaction Isolation Guarantee and LockingSpecial Considerations in Transactions5.5 The Power of Procedural SQL ProgramsCustomized Set FunctionsDynamic SQL5.6 Execute ImmediatePrepare, Execute, and UsingDynamic Select: The Describe Statement and the SQLDA5.7 Some Advanced Programming ConceptsScrollable CursorsCursor SensitivityOther Development Environments for Database ProgrammingSuggestions for Further ReadingExercisesChapter 6 Database DesignCHANGES- clarifies a number of points regarding logical database design and adds a number of examples. 6.1 Introduction to E-R ConceptsEntities, Attributes, and Simple E-R DiagramsTransforming Entities and Attributes to RelationsRelationships among Entities6.2 Further Details of E-R ModelingCardinality of Entity Participation in a RelationshipOne-to-One, Many-to-Many, and Many-to-One RelationshipsTransforming Binary Relationships to Relations6.3 Additional E-R ConceptsCardinality of AttributesWeak EntitiesGeneralization Hierarchies6.4 Case Study6.5 Normalization: PreliminariesA Running Example: Employee InformationAnomalies of a Bad Database Design6.6 Functional DependenciesLogical Implications among Functional DependenciesArmstrong's AxiomsClosure, Cover, and Minimal Cover6.7 Lossless Decompositions6.8 Normal FormsA Succession of Decompositions to Eliminate AnomaliesNormal Forms: BCNF, 3NF, and 2NFAn Algorithm to Achieve Well-Behaved 3NF DecompositionA Review of Normalization6.9 Additional Design ConsiderationsDatabase Design ToolsSuggestions for Further ReadingExercisesChapter 7 Integrity, Views, Security, and CatalogsCHANGES- updates coverage and examples relating to integrity, views, security, and catalogs. 7.1 Integrity ConstraintsIntegrity Constraints in the Create Table StatementPrimary Keys, Foreign Keys, and Referential IntegrityForeign Key Constraints: Product VariationsThe Alter Table StatementNon-Procedural and Procedural Integrity Constraints: Triggers7.2 Creating ViewsUpdatable and Read-Only ViewsThe Value of Views7.3 Security: The Grant Statement in SQLVariations in Database Products7.4 System CatalogsCatalog Variations in Database ProductsThe INFORMIX System CatalogCatalog Tables for Object-Relational Constructs: ORACLE and INFORMIXSuggestions for Further ReadingExercisesChapter 8 IndexingCHANGES-updates discussion and examples relating to a wide range of new indexing techniques. 8.1 The Concept of Indexing8.2 Disk StorageDisk Access Is Excruciatingly SlowThe DBA and Disk Resource Allocation in ORACLEData Storage Pages and Row Pointers: ORACLE and DB2 UDB8.3 The B-Tree IndexDynamic Changes in the B-TreeProperties of the B-TreeIndex Node Layout and Free SpaceThe Create Index Statement in ORACLE and DB2 UDBDuplicate Key Values in an IndexThe ORACLE Bitmap Index8.4 Clustered and Non-Clustered IndexesClustering Indexes in DB2 UDBORACLE Special Indexing Features8.5 A Hash Primary IndexTuning HASHKEYS and SIZE in a Hash ClusterNo Incremental Changes in the Number of Slots UsedAdvantages and Disadvantages of a Hash Primary Index8.6 Throwing Darts at Random SlotsUnlimited Slot Occupancy: How Many Slots Are Occupied?Slot Occupancy of One: Number of Retries (Rehash Chain)When Do Hash Pages Fill UpSuggestions for Further ReadingExercisesChapter 9 Query ProcessingCHANGES- 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 ConceptsQuery Resource UtilizationGathering StatisticsRetrieving the Query Plan9.2 Tablespace Scans and I/OAssumptions about I/O9.3 Simple Indexed Access in DB2Equal Unique Match Index AccessIndex-Only Retrieval9.4 Filter Factors and StatisticsDB2 StatisticsFilter Factors in DB29.5 Matching Index Scans, Composite Indexes Definition of a Matching Index Scan Predicate Screening and Screening PredicatesIndexable Predicates and Performance9.6 Multiple Index AccessList Prefetch and the RID PoolPoint of Diminishing Returns in Multiple Index Access9.7 Methods for Joining TablesNested Loop JoinMerge JoinHybrid JoinMultiple Table JoinsTransforming Nested Queries to Joins9.8 Disk SortsThe N-Way Merge Disk Sort Algorithm9.9 Query Performance Benchmarks: A Case StudyThe BENCH TableLoad Measurements9.10 Query Performance MeasurementsQuery Q1Query Q2AQuery Q2BQuery Q3AQuery Q3BQueries Q4A and Q4BQuery Q5Query Q6AQuery Q6B9.11 Cost-Performance AssessmentElapsed Time versus CPU Time RatingCustomizing the RatingVariations in Indexing Use between DB2 and ORACLESuggestions for Further ReadingExercisesChapter 10 Update TransactionsCHANGES- presents improved definitions and proofs, particularly those relating to levels of isolation. 10.1 Transactional HistoriesFundamental Atomic Read and Write Actions in the DatabasePredicate Read ActionsTransactional Histories with Reads and Writes10.2 Interleaved Read/Write Operations10.3 Serializability and the Precedence GraphThe Precedence Graph10.4 Locking to Ensure SerializabilityThe Waits-For Graph10.5 Levels of IsolationThe Read Uncommitted Isolation LevelThe Read Committed Isolation Level and Cursor StabilityRepeatable Read Isolation LevelSerializability and Phantom Updates10.6 Transactional Recovery10.7 Recovery in Detail: Log FormatsGuarantees That Needed Log Entries Are on Disk10.8 CheckpointsThe Commit-Consistent CheckpointMotivation for Other Kinds of CheckpointsThe Cache-Consistent CheckpointThe Fuzzy Checkpoint10.9 Media RecoveryStable Storage10.10 Performance: The TPC-A BenchmarkThe TPC-A Benchmark SpecificationLessons from the TPC-A BenchmarkSuggestions for Further ReadingExercisesChapter 11 Parallel and Distributed Databases11.1 Some Multi-CPU ArchitecturesClient-Server Architectures11.2 The Curve of CPU Cost versus Power11.3 Shared-Nothing Database ArchitectureTwo-Phase CommitFurther Problems with Shared-Nothing Architecture11.4 Query Parallelism Intra-Query ParallelismSuggestions for Further ReadingExercisesAppendix A Introductory TutorialA.1 Setting Up the CAP Database in ORACLECreating the CAP DatabaseUsing the SQLLoaderUsing SQLPlusA.2 Setting Up the CAP Database in INFORMIXCreating the CAP Database (in UNIX)Using DB-Access (UNIX systems)A.3 DatatypesAppendix B Programming DetailsB.1 The prompt() functionUse of Numeric Constants in Code ExamplesB.2 The print_dberror() functionB.3 Building Embedded C ProgramsPrecompilation and Compilation Procedure Using ORACLE/UNIXPrecompilation and Compilation Procedure Using DB2 UDB/UNIXAppendix C SQL Statement SyntaxC.1 Alter Table StatementC.2 Close Cursor StatementC.3 Commit Work StatementC.4 Connect StatementC.5 Create Function Statement (UDF)C.6 Create Index StatementC.7 Create Row Type Statement (O-R)C.8 Create Schema StatementC.9 Create Table StatementC.10 Create Tablespace Statement in ORACLE and DB2 UDBC.11 Create Trigger StatementC.12 Create Type Statement (O-R)C.13 Create View StatementC.14 Declare Cursor StatementC.15 Delete StatementC.16 Describe StatementC.17 Disconnect StatementC.18 Drop Function StatementC.19 Drop Index StatementC.20 Drop Trigger StatementC.21 Drop (Row) Type Statement (O-R)C.22 Drop {Schema