Database Design, Query, Formulation, and Administration 8th Edition - Using Oracle and PostgreSQL

Part I Introduction to Database Environments1 Introduction to Database ManagementLearning ObjectivesOverview1.1 Database Characteristics1.2 Features of Database Management Systems1.2.1 Database Definition1.2.2 Nonprocedural Access1.2.3 Procedural Language Interface1.2.4 Features to Support Database Operations1.2.5 Third-Party Features1.3 Development of Database Technology and Market Structure1.3.1 Evolution of Database Technology1.3.2 Current Market for Database Software1.4 Architectures of Database Management Systems1.4.1 Data Independence and the Three Schema Architecture1.4.2 Parallel and Distributed Database Processing1.5 Organizational Impacts of Database Technology1.5.1 Interacting with Databases1.5.2 Managing Data Resources in OrganizationsClosing ThoughtsReview ConceptsProblems2 Introduction to Database DevelopmentLearning ObjectivesOverview2.1 Information Systems2.1.1 Components of Information Systems2.1.2 Information Systems Development Process2.2 Goals of Database Development2.2.1 Develop a Common Vocabulary2.2.2 Define Business Rules2.2.3 Ensure Data Quality2.2.4 Find an Efficient Implementation2.3 Database Development Process2.3.1 Phases of Database Development2.3.2 Skills in Database Development2.4 Tools for Database Development2.4.1 Diagramming2.4.2 Documentation2.4.3 Analysis2.4.4 Prototyping Tools2.4.5 Commercial CASE ToolsClosing ThoughtsReview ConceptsProblemsPart II Defining and Using Relational Databases3 The Relational Data Model in the SQL StandardLearning ObjectivesOverview3.1 Basic Elements3.1.1 Tables3.1.2 Connections among Tables3.1.3 Alternative Terminology3.2 Integrity Rules3.2.1 Definition of the Integrity Rules3.2.2 Application of the Integrity Rules3.2.3 Graphical Representation of Referential Integrity3.2.4 Delete and Update Actions for Referenced Rows3.2.5 CHECK Constraints3.3 Operators of Relational Algebra3.3.1 Restrict (Select) and Project Operators3.3.2 Extended Cross Product Operator3.3.3 Join Operator3.3.4 Outer Join Operator3.3.5 Union, Intersection, and Difference Operators3.3.6 Summarize Operator3.3.7 Division Operator3.3.8 Summary of OperatorsClosing ThoughtsReview ConceptsProblems4 Query Formulation with SQLLearning ObjectivesOverview4.1 Background4.1.1 Brief History of SQL4.1.2 Scope of SQL4.2 Getting Started with the SELECT Statement4.2.1 Single Table Problems4.2.2 Joining Tables4.2.3 Summarizing Tables with GROUP BY and HAVING4.2.4 Improving the Appearance of Results4.3 Conceptual Evaluation Process for SELECT Statements4.3.1 Demonstration of the Conceptual Evaluation Process4.3.2 Insights from the Conceptual Evaluation Process4.4 Critical Questions for Query Formulation4.5 Refining Query Formulation Skills with Examples4.5.1 Joining Multiple Tables with the Cross Product Style4.5.2 Joining Multiple Tables with the Join Operator Style4.5.3 Atypical Join Patterns4.5.4 Combining Joins and Grouping4.5.5 Traditional Set Operators in the SQL SELECT Statement4.6 SQL Modification Statements4.7 Query Formulation Errors and Coding PracticesClosing ThoughtsReview ConceptsProblemsPart III Data Modeling5 Understanding Entity Relationship DiagramsLearning ObjectivesOverview5.1 Introduction to Entity Relationship Diagrams5.1.1 Basic Symbols5.1.2 Relationship Cardinality5.1.3 Comparison to Relational Database Diagrams5.2 Understanding Relationships5.2.1 Identification Dependency (Weak Entity Types and Identifying Relationships)5.2.2 Relationship Patterns5.2.3 Equivalence between 1-M and M-N Relationships5.3 Classification in the Entity Relationship Model5.3.1 Generalization Hierarchies5.3.2 Disjointness and Completeness Constraints5.3.3 Multiple Levels of Generalization5.4 Notation Summary and Diagram Rules5.4.1 Notation Summary5.4.2 Diagram Rules5.5 Comparison to Other Notations5.5.1 Range of ERD Variations in Data Modeling Tools5.5.2 ERD Notation in Aqua Data Studio5.5.3 ERD Tool in pgAdmin5.5.4 Entity Relationship Stencil in Visio Professional5.5.5 ERD Notation in Visual Paradigm5.5.6 Class Diagram Notation of the Unified Modeling LanguageClosing ThoughtsReview ConceptsProblems6 Developing Data Models for Business DatabasesLearning ObjectivesOverview6.1 Analyzing Business Data Modeling Problems6.1.1 Conceptual Data Modeling6.1.2 Narrative Problem Analysis6.1.3 Analysis of Problem Narrative for the Water Utility Database6.2 Refinements to an ERD6.2.1 Expanding Attributes6.2.2 Splitting Compound Attributes6.2.3 Expanding Entity Types6.2.4 Transforming a Weak Entity Type into a Strong Entity Type6.2.5 Adding History6.2.6 Adding Generalization Hierarchies6.2.7 Summary of Transformations6.3 Finalizing an ERD6.3.1 Documenting an ERD6.3.2 Detecting Common Design Errors6.4 Converting an ERD to a Table Design6.4.1 Basic Conversion Rules6.4.2 Converting Optional 1-M Relationships6.4.3 Converting Generalization Hierarchies6.4.4 Converting 1-1 Relationships6.4.5 Comprehensive Conversion Example6.4.6 Conversion Practices in Commercial CASE ToolsClosing ThoughtsReview ConceptsProblemsPart IV Relational Database Design7 Normalization Concepts and ProcessesLearning ObjectivesOverview7.1 Overview of Relational Database Design7.1.1 Avoidance of Modification Anomalies7.1.2 Functional Dependencies7.1.3 Falsification of FDs using Sample Rows7.2 Basic Normal Forms7.2.1 First Normal Form7.2.2 Boyce-Codd Normal Form7.2.3 Simple Synthesis Procedure7.3 Refining M-Way Relationships7.3.1 Relationship Independence7.3.2 Multivalued Dependencies and Fourth Normal Form7.4 Higher Level Normal Forms7.4.1 Fifth Normal Form7.4.2 Domain Key Normal Form7.5 Practical Concerns about Normalization7.5.1 Role of Normalization in the Database Development Process7.5.2 Analyzing the Normalization ObjectiveClosing ThoughtsReview ConceptsProblems8 Physical Database DesignLearning ObjectivesOverview8.1 Overview of Physical Database Design8.1.1 Storage Level of Databases8.1.2 Objectives and Constraints8.1.3 Inputs, Outputs, and Environment8.1.4 Difficulties8.2 Inputs of Physical Database Design8.2.1 Table Profiles8.2.2 Application Profiles8.3 File Structures8.3.1 Sequential Files8.3.2 Hash Files8.3.3 Multiway Tree (Btrees) Files8.3.4 Bitmap Indexes8.3.5 Columnstore Indexes8.3.6 Summary of File Structures8.3.7 Oracle Storage Concepts and File Structures8.4 Query Optimization8.4.1 Translation Tasks8.4.2 Improving Optimization Decisions8.5 Index Selection8.5.1 Problem Definition8.5.2 Trade-offs and Difficulties8.5.3 Selection Rules8.6 Additional Choices in Physical Database Design8.6.1 Denormalization8.6.2 Record Formatting8.6.3 Parallel Processing8.6.4 Other Ways to Improve PerformanceClosing ThoughtsReview ConceptsProblemsPart V Advanced Query Formulation9 Advanced Matching ProblemsLearning ObjectivesOverview9.1 Outer Join Problems9.1.1 Outer Join Problems9.1.2 Mixing Inner and Outer Joins9.2 Nested Queries9.2.1 Type I Nested Queries9.2.2 Type II Nested Queries9.2.3 Nested Queries in the FROM Clause9.3 Difference Problems9.3.1 Memberships Exceptions as Difference Problems9.3.2 NOT IN Statement Pattern for Difference Problems9.3.3 Alternative Formulations for Difference Problems9.4 Division Problems9.4.1 Overview of Division Problems9.4.2 COUNT Statement Pattern for Division Problems9.4.3 Extensions of the COUNT Statement Pattern9.5 Summary of Advanced Matching ProblemsClosing ThoughtsReview ConceptsProblems10 Views, Null Values, and Hierarchical QueriesLearning ObjectivesOverview10.1 Background for Views10.1.1 Motivation10.1.2 View Definition10.2 Using Views for Retrieval and Update10.2.1 Using Views in SELECT Statements10.2.2 Processing Queries with View References10.2.3 Single-Table Updatable Views10.2.4 Updatable Join Views in Oracle10.3 Null Value Considerations10.3.1 Effect on Simple Conditions10.3.2 Effect on Compound Conditions10.3.3 Effect on Aggregate Functions and Grouping10.4 Hierarchical Queries10.4.1 Hierarchical Data Example10.4.2 Proprietary Oracle Extensions for Hierarchical Queries10.4.3 Extensions in the SQL Standard for Hierarchical QueriesClosing ThoughtsReview ConceptsProblems11 Stored Procedures and TriggersLearning ObjectivesOverview11.1 Database Programming Languages and PL/SQL11.1.1 Motivation for Database Programming Languages11.1.2 Design Issues11.1.3 PL/SQL Statements11.1.4 Executing PL/SQL Statements in Anonymous Blocks11.2 Stored Procedures11.2.1 PL/SQL Procedures11.2.2 PL/SQL Functions11.2.3 Using Cursors11.2.4 PL/SQL Packages11.3 Triggers11.3.1 Motivation and Classification of Triggers11.3.2 Basic Trigger Development using Oracle PL/SQL11.3.3 Specialized Oracle Triggers using the INSTEAD OF Event11.3.4 Understanding Trigger ExecutionClosing ThoughtsReview ConceptsProblemsPart VI Data Warehouse Processing12 Data Warehouse Concepts and ManagementLearning ObjectivesOverview12.1 Basic Concepts12.1.1 Transaction Processing versus Business Intelligence12.1.2 Characteristics of Data Warehouses12.1.3 Applications of Data Warehouses12.2 Management of Data Warehouse Development12.2.1 Development Challenges and Learning Effects12.2.2 Architectures for Data Warehouse Deployment12.2.3 Approaches for Managing Development of Data Warehouses12.2.4 Business Strategy Game for Data Warehouse Development12.3 Data Warehouse Examples12.3.1 Data Warehouses in Retail12.3.2 Data Warehouses in Education12.3.3 Data Warehouses in Health CareClosing ThoughtsReview ConceptsProblems13 Conceptual Design of Data WarehousesLearning ObjectivesOverview13.1 Multidimensional Representation of Data13.1.1 Example of a Multidimensional Data Cube13.1.2 Multidimensional Terminology13.1.3 Time-Series Data13.1.4 Data Cube Operators13.2 Relational Data Modeling Patterns for Data Warehouses13.2.1 Schema Patterns13.2.2 Example Table Designs for Data Warehouses13.2.3 Time Representation and Historical Integrity13.2.4 Extensions for Dimension Representation13.3 Summarizability Problems and Patterns13.3.1 Dimension-Fact Summarizability Problems and Patterns13.3.2 Dimension-Fact Summarizability Problems and Patterns13.4 Schema Integration and Design Methodologies13.4.1 Schema Integration Process13.4.2 Design Transformations for Schema Integration13.4.3 Mini Case Study for Schema Integration13.4.4 Data Warehouse Design MethodologiesClosing ThoughtsReview ConceptsProblemsPractice Mini Case Study for Schema Integration14 Data Integration Concepts and PracticesLearning ObjectivesOverview14.1 Data Integration Concepts14.1.1 Sources of Data14.1.2 Workflow for Maintaining a Data Warehouse14.1.3 Managing the Refresh Process14.2 Data Cleaning Techniques14.2.1 Parsing with Regular Expressions14.2.2 Correcting and Standardizing Values14.2.3 Entity Matching14.3 Data Integration Tools14.3.1 Architectures and Features of Data Integration Tools14.3.2 Talend Open Studio14.3.3 Pentaho Data Integration14.3.4 Oracle Data Integrator14.3.5 SQL Statements for Data IntegrationClosing ThoughtsReview ConceptsProblems15 Query Formulation for Data WarehousesLearning ObjectivesOverview15.1 Online Analytic Processing (OLAP)15.1.1 Microsoft Multidimensional Expressions (MDX)15.1.2 Pivot Table Tools for OLAP Queries15.2 SQL Extensions for Subtotal Calculations15.2.1 CUBE Operator15.2.2 ROLLUP Operator15.2.3 GROUPING SETS Operator15.2.4 Variations of Subtotal Operators15.3 SQL Extensions for Analytic Functions15.3.1 Motivation and Processing Overview15.3.2 Query Formulation for Relative Performance15.3.3 Query Formulation for Trend Analysis15.3.4 Query Formulation for Ratio Comparisons15.4 Summary Data Management and Optimization15.4.1 Materialized Views in Oracle15.4.2 Query Rewriting Principles15.4.3 Storage and Optimization TechnologiesClosing ThoughtsReview ConceptsProblemsPart VII Managing Database Environments16 Data and Database AdministrationLearning ObjectivesOverview16.1 Organizational Context for Managing Databases16.1.1 Database Support for Management Decision Making16.1.2 Approaches for Managing Data Resources16.1.3 Responsibilities of Data Specialists16.1.4 Challenges of Big Data16.2 Tools of Database Administration16.2.1 Security16.2.2 Integrity Constraints16.2.3 Management of Triggers and Stored Procedures16.2.4 Data Dictionary Manipulation16.3 Processes for Database Specialists16.3.1 Data Planning16.3.2 Data Governance Processes and Tools16.3.3 Selection and Evaluation of Database Management Systems16.4 Managing Database Environments16.4.1 Transaction Processing16.4.2 Data Warehouse Processing16.4.3 Distributed Environments16.4.4 Object Databases and NoSQL DatabasesClosing ThoughtsReview ConceptsProblems17 Transaction ManagementLearning ObjectivesOverview17.1 Basics of Database Transactions17.1.1 Transaction Examples17.1.2 Transaction Properties17.2 Concurrency Control17.2.1 Objective of Concurrency Control17.2.2 Interference Problems17.2.3 Concurrency Control Tools17.3 Recovery Management17.3.1 Data Storage Devices and Failure Types17.3.2 Recovery Tools17.3.3 Recovery Processes17.4 Transaction Design Issues17.4.1 Transaction Boundary and Hot Spots17.4.2 Isolation Levels17.4.3 Timing of Integrity Constraint Enforcement17.4.4 Save Points17.4.5 Relaxed Transaction Consistency Model17.5 Workflow Management17.5.1 Characterizing Workflows17.5.2 Enabling TechnologiesClosing ThoughtsReview ConceptsProblems18 Client-Server Processing, Parallel Database Processing, and Distributed DatabasesLearning ObjectivesOverview18.1 Overview of Distributed Processing and Distributed Data18.1.1 Motivation for Client-Server Processing18.1.2 Motivation for Parallel Database Processing18.1.3 Motivation for Distributed Data18.1.4 Motivation for Cloud-Based Computing18.1.5 Summary of Advantages and Disadvantages18.2 Client-Server Database Architectures18.2.1 Design Issues18.2.2 Basic Architectures18.2.3 Specialized Architectures18.3 Parallel Database Processing18.3.1 Architectures and Design Issues18.3.2 Commercial Parallel Database Technology18.3.3 Big Data Parallel Processing Architectures18.4 Architectures for Distributed Database Management Systems18.4.1 Component Architecture18.4.2 Schema Architectures18.5 Transparency for Distributed Database Processing18.5.1 Motivating Example18.5.2 Fragmentation Transparency18.5.3 Location Transparency18.5.4 Local Mapping Transparency18.5.5 Transparency in Oracle Distributed Databases18.6 Distributed Database Processing18.6.1 Distributed Query Processing18.6.2 Distributed Transaction ProcessingClosing ThoughtsReview ConceptsProblems19 DBMS Extensions for Object and NoSQL DatabasesLearning ObjectivesOverview19.1 Motivation for Object Database Management19.1.1 Complex Data19.1.2 Type System Mismatch19.1.3 Application Examples19.2 Object Database Features in the SQL Standard19.2.1 User-Defined Types19.2.2 Table Definitions19.2.3 Subtable Families19.4.4 Manipulating Complex Objects and Subtable Families19.3 Object Database Features in Oracle19.3.1 Defining User-Defined Types and Typed Tables in Oracle19.3.2 Using Typed Tables in Oracle19.3.3 Dependencies among Types and Typed Tables19.3.4 Other Object Features in Oracle19.4 Overview of NoSQL Database Management19.4.1 Motivation and Features19.4.2 Data Models in NoSQL DBMSs19.5 Database Definition and Manipulation with Couchbase N1QL19.5.1 JavaScript Object Notation (JSON)19.5.2 Couchbase N1QL StatementsClosing ThoughtsReview ConceptsProblemsIndexes