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

Thiết kế, truy vấn, xây dựng và quản trị cơ sở dữ liệu
Sử dụng Oracle và PostgreSQL
Trước đây được xuất bản bởi Chicago Business Press, hiện được xuất bản bởi Sage Database Design, Công thức truy vấn và Quản trị, Phiên bản thứ tám, cung cấp sự hiểu biết toàn diện về công nghệ cơ sở dữ liệu. Tác giả Michael Mannino trang bị cho sinh viên những công cụ cần thiết để nắm bắt các khái niệm cơ bản về quản lý cơ sở dữ liệu, sau đó hướng dẫn họ trau dồi kỹ năng để giải quyết những thách thức cơ bản và nâng cao trong việc xây dựng truy vấn, mô hình hóa dữ liệu và phát triển ứng dụng cơ sở dữ liệu. Các tính năng của Phiên bản thứ tám: Phạm vi bảo hiểm SQL chưa từng có về cả chiều rộng và chiều sâu Phạm vi bảo hiểm của Oracle và PostgreSQL Hướng dẫn giải quyết vấn đề Cơ sở dữ liệu mẫu và ví dụ Công cụ mô hình hóa dữ liệu Phạm vi kho dữ liệu Phạm vi bảo hiểm NoSQL Các chủ đề hiện tại và tiên tiến Đủ toàn diện cho nhiều khóa học cơ sở dữ liệu

Part I Introduction to Database Environments
1 Introduction to Database Management
Learning Objectives
Overview
1.1 Database Characteristics
1.2 Features of Database Management Systems
1.2.1 Database Definition
1.2.2 Nonprocedural Access
1.2.3 Procedural Language Interface
1.2.4 Features to Support Database Operations
1.2.5 Third-Party Features
1.3 Development of Database Technology and Market Structure
1.3.1 Evolution of Database Technology
1.3.2 Current Market for Database Software
1.4 Architectures of Database Management Systems
1.4.1 Data Independence and the Three Schema Architecture
1.4.2 Parallel and Distributed Database Processing
1.5 Organizational Impacts of Database Technology
1.5.1 Interacting with Databases
1.5.2 Managing Data Resources in Organizations
Closing Thoughts
Review Concepts
Problems
2 Introduction to Database Development
Learning Objectives
Overview
2.1 Information Systems
2.1.1 Components of Information Systems
2.1.2 Information Systems Development Process
2.2 Goals of Database Development
2.2.1 Develop a Common Vocabulary
2.2.2 Define Business Rules
2.2.3 Ensure Data Quality
2.2.4 Find an Efficient Implementation
2.3 Database Development Process
2.3.1 Phases of Database Development
2.3.2 Skills in Database Development
2.4 Tools for Database Development
2.4.1 Diagramming
2.4.2 Documentation
2.4.3 Analysis
2.4.4 Prototyping Tools
2.4.5 Commercial CASE Tools
Closing Thoughts
Review Concepts
Problems
Part II Defining and Using Relational Databases
3 The Relational Data Model in the SQL Standard
Learning Objectives
Overview
3.1 Basic Elements
3.1.1 Tables
3.1.2 Connections among Tables
3.1.3 Alternative Terminology
3.2 Integrity Rules
3.2.1 Definition of the Integrity Rules
3.2.2 Application of the Integrity Rules
3.2.3 Graphical Representation of Referential Integrity
3.2.4 Delete and Update Actions for Referenced Rows
3.2.5 CHECK Constraints
3.3 Operators of Relational Algebra
3.3.1 Restrict (Select) and Project Operators
3.3.2 Extended Cross Product Operator
3.3.3 Join Operator
3.3.4 Outer Join Operator
3.3.5 Union, Intersection, and Difference Operators
3.3.6 Summarize Operator
3.3.7 Division Operator
3.3.8 Summary of Operators
Closing Thoughts
Review Concepts
Problems
4 Query Formulation with SQL
Learning Objectives
Overview
4.1 Background
4.1.1 Brief History of SQL
4.1.2 Scope of SQL
4.2 Getting Started with the SELECT Statement
4.2.1 Single Table Problems
4.2.2 Joining Tables
4.2.3 Summarizing Tables with GROUP BY and HAVING
4.2.4 Improving the Appearance of Results
4.3 Conceptual Evaluation Process for SELECT Statements
4.3.1 Demonstration of the Conceptual Evaluation Process
4.3.2 Insights from the Conceptual Evaluation Process
4.4 Critical Questions for Query Formulation
4.5 Refining Query Formulation Skills with Examples
4.5.1 Joining Multiple Tables with the Cross Product Style
4.5.2 Joining Multiple Tables with the Join Operator Style
4.5.3 Atypical Join Patterns
4.5.4 Combining Joins and Grouping
4.5.5 Traditional Set Operators in the SQL SELECT Statement
4.6 SQL Modification Statements
4.7 Query Formulation Errors and Coding Practices
Closing Thoughts
Review Concepts
Problems
Part III Data Modeling
5 Understanding Entity Relationship Diagrams
Learning Objectives
Overview
5.1 Introduction to Entity Relationship Diagrams
5.1.1 Basic Symbols
5.1.2 Relationship Cardinality
5.1.3 Comparison to Relational Database Diagrams
5.2 Understanding Relationships
5.2.1 Identification Dependency (Weak Entity Types and Identifying Relationships)
5.2.2 Relationship Patterns
5.2.3 Equivalence between 1-M and M-N Relationships
5.3 Classification in the Entity Relationship Model
5.3.1 Generalization Hierarchies
5.3.2 Disjointness and Completeness Constraints
5.3.3 Multiple Levels of Generalization
5.4 Notation Summary and Diagram Rules
5.4.1 Notation Summary
5.4.2 Diagram Rules
5.5 Comparison to Other Notations
5.5.1 Range of ERD Variations in Data Modeling Tools
5.5.2 ERD Notation in Aqua Data Studio
5.5.3 ERD Tool in pgAdmin
5.5.4 Entity Relationship Stencil in Visio Professional
5.5.5 ERD Notation in Visual Paradigm
5.5.6 Class Diagram Notation of the Unified Modeling Language
Closing Thoughts
Review Concepts
Problems
6 Developing Data Models for Business Databases
Learning Objectives
Overview
6.1 Analyzing Business Data Modeling Problems
6.1.1 Conceptual Data Modeling
6.1.2 Narrative Problem Analysis
6.1.3 Analysis of Problem Narrative for the Water Utility Database
6.2 Refinements to an ERD
6.2.1 Expanding Attributes
6.2.2 Splitting Compound Attributes
6.2.3 Expanding Entity Types
6.2.4 Transforming a Weak Entity Type into a Strong Entity Type
6.2.5 Adding History
6.2.6 Adding Generalization Hierarchies
6.2.7 Summary of Transformations
6.3 Finalizing an ERD
6.3.1 Documenting an ERD
6.3.2 Detecting Common Design Errors
6.4 Converting an ERD to a Table Design
6.4.1 Basic Conversion Rules
6.4.2 Converting Optional 1-M Relationships
6.4.3 Converting Generalization Hierarchies
6.4.4 Converting 1-1 Relationships
6.4.5 Comprehensive Conversion Example
6.4.6 Conversion Practices in Commercial CASE Tools
Closing Thoughts
Review Concepts
Problems
Part IV Relational Database Design
7 Normalization Concepts and Processes
Learning Objectives
Overview
7.1 Overview of Relational Database Design
7.1.1 Avoidance of Modification Anomalies
7.1.2 Functional Dependencies
7.1.3 Falsification of FDs using Sample Rows
7.2 Basic Normal Forms
7.2.1 First Normal Form
7.2.2 Boyce-Codd Normal Form
7.2.3 Simple Synthesis Procedure
7.3 Refining M-Way Relationships
7.3.1 Relationship Independence
7.3.2 Multivalued Dependencies and Fourth Normal Form
7.4 Higher Level Normal Forms
7.4.1 Fifth Normal Form
7.4.2 Domain Key Normal Form
7.5 Practical Concerns about Normalization
7.5.1 Role of Normalization in the Database Development Process
7.5.2 Analyzing the Normalization Objective
Closing Thoughts
Review Concepts
Problems
8 Physical Database Design
Learning Objectives
Overview
8.1 Overview of Physical Database Design
8.1.1 Storage Level of Databases
8.1.2 Objectives and Constraints
8.1.3 Inputs, Outputs, and Environment
8.1.4 Difficulties
8.2 Inputs of Physical Database Design
8.2.1 Table Profiles
8.2.2 Application Profiles
8.3 File Structures
8.3.1 Sequential Files
8.3.2 Hash Files
8.3.3 Multiway Tree (Btrees) Files
8.3.4 Bitmap Indexes
8.3.5 Columnstore Indexes
8.3.6 Summary of File Structures
8.3.7 Oracle Storage Concepts and File Structures
8.4 Query Optimization
8.4.1 Translation Tasks
8.4.2 Improving Optimization Decisions
8.5 Index Selection
8.5.1 Problem Definition
8.5.2 Trade-offs and Difficulties
8.5.3 Selection Rules
8.6 Additional Choices in Physical Database Design
8.6.1 Denormalization
8.6.2 Record Formatting
8.6.3 Parallel Processing
8.6.4 Other Ways to Improve Performance
Closing Thoughts
Review Concepts
Problems
Part V Advanced Query Formulation
9 Advanced Matching Problems
Learning Objectives
Overview
9.1 Outer Join Problems
9.1.1 Outer Join Problems
9.1.2 Mixing Inner and Outer Joins
9.2 Nested Queries
9.2.1 Type I Nested Queries
9.2.2 Type II Nested Queries
9.2.3 Nested Queries in the FROM Clause
9.3 Difference Problems
9.3.1 Memberships Exceptions as Difference Problems
9.3.2 NOT IN Statement Pattern for Difference Problems
9.3.3 Alternative Formulations for Difference Problems
9.4 Division Problems
9.4.1 Overview of Division Problems
9.4.2 COUNT Statement Pattern for Division Problems
9.4.3 Extensions of the COUNT Statement Pattern
9.5 Summary of Advanced Matching Problems
Closing Thoughts
Review Concepts
Problems
10 Views, Null Values, and Hierarchical Queries
Learning Objectives
Overview
10.1 Background for Views
10.1.1 Motivation
10.1.2 View Definition
10.2 Using Views for Retrieval and Update
10.2.1 Using Views in SELECT Statements
10.2.2 Processing Queries with View References
10.2.3 Single-Table Updatable Views
10.2.4 Updatable Join Views in Oracle
10.3 Null Value Considerations
10.3.1 Effect on Simple Conditions
10.3.2 Effect on Compound Conditions
10.3.3 Effect on Aggregate Functions and Grouping
10.4 Hierarchical Queries
10.4.1 Hierarchical Data Example
10.4.2 Proprietary Oracle Extensions for Hierarchical Queries
10.4.3 Extensions in the SQL Standard for Hierarchical Queries
Closing Thoughts
Review Concepts
Problems
11 Stored Procedures and Triggers
Learning Objectives
Overview
11.1 Database Programming Languages and PL/SQL
11.1.1 Motivation for Database Programming Languages
11.1.2 Design Issues
11.1.3 PL/SQL Statements
11.1.4 Executing PL/SQL Statements in Anonymous Blocks
11.2 Stored Procedures
11.2.1 PL/SQL Procedures
11.2.2 PL/SQL Functions
11.2.3 Using Cursors
11.2.4 PL/SQL Packages
11.3 Triggers
11.3.1 Motivation and Classification of Triggers
11.3.2 Basic Trigger Development using Oracle PL/SQL
11.3.3 Specialized Oracle Triggers using the INSTEAD OF Event
11.3.4 Understanding Trigger Execution
Closing Thoughts
Review Concepts
Problems
Part VI Data Warehouse Processing
12 Data Warehouse Concepts and Management
Learning Objectives
Overview
12.1 Basic Concepts
12.1.1 Transaction Processing versus Business Intelligence
12.1.2 Characteristics of Data Warehouses
12.1.3 Applications of Data Warehouses
12.2 Management of Data Warehouse Development
12.2.1 Development Challenges and Learning Effects
12.2.2 Architectures for Data Warehouse Deployment
12.2.3 Approaches for Managing Development of Data Warehouses
12.2.4 Business Strategy Game for Data Warehouse Development
12.3 Data Warehouse Examples
12.3.1 Data Warehouses in Retail
12.3.2 Data Warehouses in Education
12.3.3 Data Warehouses in Health Care
Closing Thoughts
Review Concepts
Problems
13 Conceptual Design of Data Warehouses
Learning Objectives
Overview
13.1 Multidimensional Representation of Data
13.1.1 Example of a Multidimensional Data Cube
13.1.2 Multidimensional Terminology
13.1.3 Time-Series Data
13.1.4 Data Cube Operators
13.2 Relational Data Modeling Patterns for Data Warehouses
13.2.1 Schema Patterns
13.2.2 Example Table Designs for Data Warehouses
13.2.3 Time Representation and Historical Integrity
13.2.4 Extensions for Dimension Representation
13.3 Summarizability Problems and Patterns
13.3.1 Dimension-Fact Summarizability Problems and Patterns
13.3.2 Dimension-Fact Summarizability Problems and Patterns
13.4 Schema Integration and Design Methodologies
13.4.1 Schema Integration Process
13.4.2 Design Transformations for Schema Integration
13.4.3 Mini Case Study for Schema Integration
13.4.4 Data Warehouse Design Methodologies
Closing Thoughts
Review Concepts
Problems
Practice Mini Case Study for Schema Integration
14 Data Integration Concepts and Practices
Learning Objectives
Overview
14.1 Data Integration Concepts
14.1.1 Sources of Data
14.1.2 Workflow for Maintaining a Data Warehouse
14.1.3 Managing the Refresh Process
14.2 Data Cleaning Techniques
14.2.1 Parsing with Regular Expressions
14.2.2 Correcting and Standardizing Values
14.2.3 Entity Matching
14.3 Data Integration Tools
14.3.1 Architectures and Features of Data Integration Tools
14.3.2 Talend Open Studio
14.3.3 Pentaho Data Integration
14.3.4 Oracle Data Integrator
14.3.5 SQL Statements for Data Integration
Closing Thoughts
Review Concepts
Problems
15 Query Formulation for Data Warehouses
Learning Objectives
Overview
15.1 Online Analytic Processing (OLAP)
15.1.1 Microsoft Multidimensional Expressions (MDX)
15.1.2 Pivot Table Tools for OLAP Queries
15.2 SQL Extensions for Subtotal Calculations
15.2.1 CUBE Operator
15.2.2 ROLLUP Operator
15.2.3 GROUPING SETS Operator
15.2.4 Variations of Subtotal Operators
15.3 SQL Extensions for Analytic Functions
15.3.1 Motivation and Processing Overview
15.3.2 Query Formulation for Relative Performance
15.3.3 Query Formulation for Trend Analysis
15.3.4 Query Formulation for Ratio Comparisons
15.4 Summary Data Management and Optimization
15.4.1 Materialized Views in Oracle
15.4.2 Query Rewriting Principles
15.4.3 Storage and Optimization Technologies
Closing Thoughts
Review Concepts
Problems
Part VII Managing Database Environments
16 Data and Database Administration
Learning Objectives
Overview
16.1 Organizational Context for Managing Databases
16.1.1 Database Support for Management Decision Making
16.1.2 Approaches for Managing Data Resources
16.1.3 Responsibilities of Data Specialists
16.1.4 Challenges of Big Data
16.2 Tools of Database Administration
16.2.1 Security
16.2.2 Integrity Constraints
16.2.3 Management of Triggers and Stored Procedures
16.2.4 Data Dictionary Manipulation
16.3 Processes for Database Specialists
16.3.1 Data Planning
16.3.2 Data Governance Processes and Tools
16.3.3 Selection and Evaluation of Database Management Systems
16.4 Managing Database Environments
16.4.1 Transaction Processing
16.4.2 Data Warehouse Processing
16.4.3 Distributed Environments
16.4.4 Object Databases and NoSQL Databases
Closing Thoughts
Review Concepts
Problems
17 Transaction Management
Learning Objectives
Overview
17.1 Basics of Database Transactions
17.1.1 Transaction Examples
17.1.2 Transaction Properties
17.2 Concurrency Control
17.2.1 Objective of Concurrency Control
17.2.2 Interference Problems
17.2.3 Concurrency Control Tools
17.3 Recovery Management
17.3.1 Data Storage Devices and Failure Types
17.3.2 Recovery Tools
17.3.3 Recovery Processes
17.4 Transaction Design Issues
17.4.1 Transaction Boundary and Hot Spots
17.4.2 Isolation Levels
17.4.3 Timing of Integrity Constraint Enforcement
17.4.4 Save Points
17.4.5 Relaxed Transaction Consistency Model
17.5 Workflow Management
17.5.1 Characterizing Workflows
17.5.2 Enabling Technologies
Closing Thoughts
Review Concepts
Problems
18 Client-Server Processing, Parallel Database Processing, and Distributed Databases
Learning Objectives
Overview
18.1 Overview of Distributed Processing and Distributed Data
18.1.1 Motivation for Client-Server Processing
18.1.2 Motivation for Parallel Database Processing
18.1.3 Motivation for Distributed Data
18.1.4 Motivation for Cloud-Based Computing
18.1.5 Summary of Advantages and Disadvantages
18.2 Client-Server Database Architectures
18.2.1 Design Issues
18.2.2 Basic Architectures
18.2.3 Specialized Architectures
18.3 Parallel Database Processing
18.3.1 Architectures and Design Issues
18.3.2 Commercial Parallel Database Technology
18.3.3 Big Data Parallel Processing Architectures
18.4 Architectures for Distributed Database Management Systems
18.4.1 Component Architecture
18.4.2 Schema Architectures
18.5 Transparency for Distributed Database Processing
18.5.1 Motivating Example
18.5.2 Fragmentation Transparency
18.5.3 Location Transparency
18.5.4 Local Mapping Transparency
18.5.5 Transparency in Oracle Distributed Databases
18.6 Distributed Database Processing
18.6.1 Distributed Query Processing
18.6.2 Distributed Transaction Processing
Closing Thoughts
Review Concepts
Problems
19 DBMS Extensions for Object and NoSQL Databases
Learning Objectives
Overview
19.1 Motivation for Object Database Management
19.1.1 Complex Data
19.1.2 Type System Mismatch
19.1.3 Application Examples
19.2 Object Database Features in the SQL Standard
19.2.1 User-Defined Types
19.2.2 Table Definitions
19.2.3 Subtable Families
19.4.4 Manipulating Complex Objects and Subtable Families
19.3 Object Database Features in Oracle
19.3.1 Defining User-Defined Types and Typed Tables in Oracle
19.3.2 Using Typed Tables in Oracle
19.3.3 Dependencies among Types and Typed Tables
19.3.4 Other Object Features in Oracle
19.4 Overview of NoSQL Database Management
19.4.1 Motivation and Features
19.4.2 Data Models in NoSQL DBMSs
19.5 Database Definition and Manipulation with Couchbase N1QL
19.5.1 JavaScript Object Notation (JSON)
19.5.2 Couchbase N1QL Statements
Closing Thoughts
Review Concepts
Problems
Indexes