Database Design, Query Formulation, and Administration

Using Oracle and PostgreSQL
Ninth Auflage
ISBN: 979-8-3488-5764-6
Verlag: SAGE Publications

Buch, Englisch, 936 Seiten, Format (B × H): 216 mm x 279 mm

Author Michael Mannino equips students with the necessary tools to grasp the fundamental concepts of database management and guides them in honing their skills to solve both basic and advanced problems.

Autoren/Hrsg.

Mannino, Michael

Inhaltsverzeichnis

Preface
About the Author
Part 1: INTRODUCTION TO DATABASE ENVIRONMENTS
Chapter 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 Tools
1.3 Development of Database Technology and Market Structure
1.3.1 Evolution of Database Technology
1.3.2 Popularity of DBMSs
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
Questions
Problems
References for Further Study
Chapter 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
Questions
Problems
References for Further Study
Part 2: DEFINING AND USING SQL DATABASES
Chapter 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
Questions
Problems
References for Further Study
Appendix 3.A: CREATE TABLE Statements for the University Database Tables
Appendix 3.B: SQL Syntax Summary
Appendix 3.C: Generation of Unique Values for Primary Keys
Chapter 4: Basic 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 Simplified Syntax and University Database
4.2.1 Simplified Syntax of the SQL SELECT Statement
4.2.2 University Database for Examples
4.3 SELECT Statement Examples for Single Table Problems
4.3.1 Initial Examples
4.3.2 Expression Examples
4.3.3 Examples with Date Comparisons
4.3.4 Examples with Null Values and Logical Expressions
4.4 SELECT Statement Examples for Joins, Grouping, and Ordering
4.4.1 Joining Tables
4.4.2 Summarizing Tables with GROUP BY and HAVING
4.4.3 Improving the Appearance of Results
4.5 Conceptual Evaluation Process for SELECT Statements
4.5.1 Demonstration of the Conceptual Evaluation Process
4.5.2 Insights from the Conceptual Evaluation Process
4.6 Traditional Set Operators and Modification Statements
4.6.1 Traditional Set Operators in the SELECT Statement
4.6.2 SQL Modification Statements
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 4.A: SQL Syntax Summary
Appendix 4.B: Syntax Differences among Major DBMS Products
Appendix 4.C: NL2SQL for Chapter 4 Examples
Chapter 5: Intermediate Query Formulation with SQL
Learning Objectives
Overview
5.1 Problem-Solving Guidelines for Query Formulation
5.1.1 Query Formulation Process
5.1.2 Critical Questions
5.2 Join Examples for Typical Join Patterns
5.2.1 Joining Multiple Tables with the Cross Product Style
5.2.2 Joining Multiple Tables with the Join Operator Style
5.2.3 Combining Joins and Grouping
5.3 Atypical Join Patterns
5.3.1 Entity Matching with a Common Identifier
5.3.2 Self-Joins with Exception Conditions
5.3.3 Join Cycles
5.4 Errors, Coding Practices, and Refinements to the Query Formulation Process
5.4.1 Overview of Query Formulation Errors
5.4.2 Examples with Syntax Errors
5.4.3 Examples with Redundancy Errors
5.4.4 Examples with Semantic Errors
5.4.5 Poor Coding Practices
5.4.6 Refinements to the Query Formulation Process
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 5.A: SQL Syntax Summary
Appendix 5.B: University Database Tables
Appendix 5.C: Oracle Syntax Errors on Self-Join Problems
Appendix 5.D: NL2SQL for Chapter 5 Examples
Part 3: DATA MODELING
Chapter 6: Understanding Entity Relationship Diagrams
Learning Objectives
Overview
6.1 Introduction to Entity Relationship Diagrams
6.1.1 Basic Symbols
6.1.2 Relationship Cardinality
6.1.3 Comparison with Oracle Database Diagrams
6.2 Understanding Relationships
6.2.1 Identification Dependency (Weak Entity Types and Identifying Relationships)
6.2.2 Relationship Patterns
6.2.3 Equivalence between 1-M and M-N Relationships
6.3 Classification in the Entity Relationship Model
6.3.1 Generalization Hierarchies
6.3.2 Disjointness and Completeness Constraints
6.3.3 Multiple Levels of Generalization
6.4 Notation Summary and Diagram Rules
6.4.1 Notation Summary
6.4.2 Diagram Rules
6.5 Comparison with Other Notations
6.5.1 Range of ERD Variations in Data Modeling Tools
6.5.2 Aqua Data Studio
6.5.3 ER/Studio Data Architect
6.5.4 Visual Paradigm
6.5.5 Class Diagram Notation of the Unified Modeling Language
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Chapter 7: Developing Data Models for Business Databases
Learning Objectives
Overview
7.1 Analyzing Business Data Modeling Problems
7.1.1 Conceptual Data Modeling
7.1.2 Narrative Problem Analysis
7.1.3 Analysis of Problem Narrative for the Water Utility Database
7.2 Refinements to an ERD
7.2.1 Expanding Attributes
7.2.2 Splitting Compound Attributes
7.2.3 Expanding Entity Types
7.2.4 Transforming a Weak Entity Type into a Strong Entity Type
7.2.5 Adding History
7.2.6 Adding Generalization Hierarchies
7.2.7 Summary of Transformations
7.3 Finalizing an ERD
7.3.1 Documenting an ERD
7.3.2 Detecting Common Design Errors
7.4 Converting an ERD to a Table Design
7.4.1 Basic Conversion Rules
7.4.2 Converting Optional 1-M Relationships
7.4.3 Converting Generalization Hierarchies
7.4.4 Converting 1-1 Relationships
7.4.5 Comprehensive Conversion Example
7.4.6 Conversion Practices in Commercial CASE Tools
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Part 4: SQL DATABASE DESIGN
Chapter 8: Normalization Concepts and Processes
Learning Objectives
Overview
8.1 Overview of Relational Database Design
8.1.1 Avoidance of Modification Anomalies
8.1.2 Functional Dependencies
8.1.3 Falsification of FDs Using Sample Data
8.2 Basic Normal Forms
8.2.1 First Normal Form
8.2.2 Boyce-Codd Normal Form
8.2.3 Simple Synthesis Procedure
8.2.4 Applying the Simple Synthesis Procedure
8.2.5 Another Example Using the Simple Synthesis Procedure
8.3 Refining M-Way Relationships
8.3.1 Relationship Independence
8.3.2 Multivalued Dependencies and Fourth Normal Form
8.4 Higher-Level Normal Forms
8.4.1 Fifth Normal Form
8.4.2 Domain Key Normal Form
8.5 Practical Concerns about Normalization
8.5.1 Role of Normalization in the Database Development Process
8.5.2 Analyzing the Normalization Objective
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 8.A: Second and Third Normal Forms
Chapter 9: Physical Database Design
Learning Objectives
Overview
9.1 Overview of Physical Database Design
9.1.1 Storage Level of Databases
9.1.2 Objectives and Constraints
9.1.3 Inputs, Outputs, and Environment
9.1.4 Difficulties
9.2 Inputs of Physical Database Design
9.2.1 Table Profiles
9.2.2 Application Profiles
9.3 File Structures
9.3.1 Sequential Files
9.3.2 Hash Files
9.3.3 Multiway Tree (Btree) Files
9.3.4 Bitmap Indexes
9.3.5 Column Store Indexes
9.3.6 Summary of File Structures
9.3.7 Oracle Storage Concepts and File Structures
9.4 Query Optimization
9.4.1 Translation Tasks
9.4.2 Improving Optimization Decisions
9.5 Index Selection
9.5.1 Problem Definition
9.5.2 Trade-offs and Difficulties
9.5.3 Selection Rules
9.6 Additional Choices in Physical Database Design
9.6.1 Denormalization
9.6.2 Record Formatting
9.6.3 Parallel Processing
9.6.4 Other Ways to Improve Performance
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Part 5: ADVANCED QUERY FORMULATION
Chapter 10: Advanced Matching Problems with SQL
Learning Objectives
Overview
10.1 Outer Join Variations and SQL Extensions
10.1.1 Outer Join Problems
10.1.2 Mixing Inner and Outer Joins
10.2 Nested Queries
10.2.1 Type I Nested Queries
10.2.2 Type II Nested Queries
10.2.3 Nested Queries in the FROM Clause
10.3 Membership Exceptions Based on the Difference Operator
10.3.1 SQL Formulations for Membership Exceptions
10.3.2 NOT IN Statement Pattern for Membership Exceptions
10.3.3 Alternative Formulations for Difference Problems
10.4 Containment Exceptions Based on the Division Operator
10.4.1 SQL Formulations for Containment Exceptions
10.4.2 COUNT Statement Pattern for Containment Exceptions
10.4.3 Extensions of the COUNT Statement Pattern
10.5 Summary and Common Table Expressions
10.5.1 Convenient Summaries of Text and Statement Patterns
10.5.2 Alternative Formulations with Common Table Expressions
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 10.A: SQL Syntax Summary
Appendix 10.B: NL2SQL for Chapter 10 Examples
Chapter 11: Derived Tables, Null Values, and Hierarchical Queries
Learning Objectives
Overview
11.1 Background for Views
11.1.1 Motivation
11.1.2 Definition of Views
11.2 Using Views for Retrieval and Update
11.2.1 Using Views in SELECT Statements
11.2.2 View Processing
11.2.3 Single-Table Updatable Views
11.2.4 Updatable Join Views in Oracle
11.3 Materialized Views
11.3.1 Motivation and Definition of Materialized Views
11.3.2 Query Rewriting
11.4 Null Value Considerations
11.4.1 Effect on Simple Conditions
11.4.2 Effect on Compound Conditions
11.4.3 Effect on Aggregate Calculations and Grouping
11.5 Hierarchical Queries
11.5.1 Hierarchical Data Example
11.5.2 Proprietary Oracle Extensions for Hierarchical Queries
11.5.3 Extensions in the SQL Standard for Hierarchical Queries
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 11.A: SQL Syntax Summary
Appendix 11.B: SQL Statements for the Employee2 Table
Appendix 11.C: Null-Aware Query Practices
Appendix 11.D: NL2SQL for Chapter 11 Examples
Chapter 12: Stored Procedures and Triggers
Learning Objectives
Overview
12.1 Database Programming Languages and PL/SQL
12.1.1 Motivation for Database Programming Languages
12.1.2 Design Issues
12.1.3 PL/SQL Statements
12.1.4 Executing PL/SQL Statements in Anonymous Blocks
12.2 Stored Procedures
12.2.1 PL/SQL Procedures
12.2.2 PL/SQL Functions
12.2.3 Using Cursors
12.2.4 PL/SQL Packages
12.3 Triggers
12.3.1 Motivation and Classification of Triggers
12.3.2 Trigger Development Using Oracle PL/SQL
12.3.3 Trigger Formulation Guidelines
12.3.4 Specialized Oracle Triggers Using the INSTEAD OF Event
12.3.5 Understanding Trigger Execution
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 12.A: SQL Syntax Summary
Appendix 12.B: PostgreSQL Triggers and Trigger Functions
Appendix 12.C: NL2SQL for Chapter 12 Trigger Examples
Part 6: DATA WAREHOUSE PROCESSING
Chapter 13: Data Warehouse Concepts and Management
Learning Objectives
Overview
13.1 Basic Concepts
13.1.1 Transaction Processing versus Business Intelligence
13.1.2 Characteristics of Data Warehouses
13.1.3 Applications of Data Warehouses
13.2 Management of Data Warehouse Development
13.2.1 Development Challenges and Learning Effects
13.2.2 Architectures for Data Warehouse Deployment
13.2.3 Approaches for Managing Development of Data Warehouses
13.2.4 Business Strategy Game for Data Warehouse Development
13.3 Data Warehouse Examples
13.3.1 Data Warehouses in Retail
13.3.2 Data Warehouses in Education
13.3.3 Data Warehouses in Health Care
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Chapter 14: Conceptual Design of Data Warehouses
Learning Objectives
Overview
14.1 Multidimensional Representation of Data
14.1.1 Example of a Multidimensional Data Cube
14.1.2 Multidimensional Terminology
14.1.3 Data Cube Operators
14.1.4 Online Analytic Processing for Data Cubes
14.2 SQL Data Modeling Patterns for Data Warehouses
14.2.1 Schema Patterns
14.2.2 Example Table Designs for Data Warehouses
14.2.3 Time Representation and Historical Integrity
14.2.4 Extensions for Dimension Representation
14.3 Summarizability Problems and Patterns
14.3.1 Dimension Summarizability Problems and Patterns
14.3.2 Dimension-Fact Summarizability Problems and Patterns
14.4 Schema Integration and Design Methodologies
14.4.1 Schema Integration Process
14.4.2 Design Transformations for Schema Integration
14.4.3 Mini Case Study for Schema Integration
14.4.4 Data Warehouse Design Methodologies
Closing Thoughts
Review Concepts
Questions
Problems
Practice Mini Case Study for Schema Integration
References for Further Study
Appendix 14.A: Details of the Schema Integration Problem
Appendix 14.B: Solution for the Schema Integration Problem
Chapter 15: Data Integration Concepts, Practices, and Emerging Alternatives
Learning Objectives
Overview
15.1 Data Integration Concepts
15.1.1 Sources of Data
15.1.2 Workflow for Maintaining a Data Warehouse
15.1.3 Managing the Refresh Process
15.2 Data Cleaning Techniques
15.2.1 Parsing with Regular Expressions
15.2.2 Correcting and Standardizing Values
15.2.3 Entity Matching
15.3 Data Integration Tools
15.3.1 Architectures and Features of Data Integration Tools
15.3.2 Talend Open Studio
15.3.3 Pentaho Data Integration
15.3.4 Oracle Data Integrator
15.3.5 SQL Statements for Data Integration
15.4 Data Integration for Data Lakes
15.4.1 Data Integration Strategies
15.4.2 Enabling Technologies for Alternative Data Integration Strategies
15.4.3 Comparative Evaluation of Tools for Alternative Data Integration Strategies
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 15.A: CREATE TABLE Statements for Section 15.3.5
Appendix 15.B: CREATE TABLE Statements for Chapter Problems
Chapter 16: Query Formulation for Data Warehouses and Data Lakes
Learning Objectives
Overview
16.1 SQL Extensions for Subtotal Operators
16.1.1 CUBE Operator
16.1.2 ROLLUP Operator
16.1.3 GROUPING SETS Operator
16.1.4 Variations of Subtotal Operators
16.2 SQL Extensions for Analytic Functions
16.2.1 Motivation and Processing Overview
16.2.2 Query Formulation for Relative Performance
16.2.3 Query Formulation for Trend Analysis
16.2.4 Query Formulation for Ratio Comparisons
16.3 Query Formulation to Generate Data Mining Input
16.3.1 Overview of Prominent Data Mining Algorithms and Input Formats
16.3.2 Query Formulation for Association Rule Mining
16.3.3 Query Formulation to Generate Training Data with Top Event History
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 16.A: Data Warehouse Examples for Section
Appendix 16.B: NL2SQL for Chapter 16 Examples
Part 7: MANAGING DATABASE ENVIRONMENTS
Chapter 17: Data and Database Administration
Learning Objectives
Overview
17.1 Organizational Context for Managing Databases
17.1.1 Database Support for Management Decision Making
17.1.2 Approaches for Managing Data Resources
17.1.3 Responsibilities of Data Specialists
17.1.4 Challenges of Big Data
17.2 Tools of Database Administration
17.2.1 Security
17.2.2 Integrity Constraints
17.2.3 Management of Triggers and Stored Procedures
17.2.4 Data Dictionary Manipulation
17.3 Processes for Database Specialists
17.3.1 Data Planning
17.3.2 Data Governance Processes and Tools
17.3.3 Selection and Evaluation of Database Management Systems
17.4 Managing Database Environments
17.4.1 Transaction Processing
17.4.2 Data Warehouse Processing
17.4.3 Distributed Environments
17.4.4 Object Databases and NoSQL Databases
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 17.A: SQL Syntax Summary
Chapter 18: Transaction Management
Learning Objectives
Overview
18.1 Basics of Database Transactions
18.1.1 Transaction Examples
18.1.2 Transaction Properties
18.2 Concurrency Control
18.2.1 Objective of Concurrency Control
18.2.2 Interference Problems
18.2.3 Concurrency Control Tools
18.3 Recovery Management
18.3.1 Data Storage Devices and Failure Types
18.3.2 Recovery Tools
18.3.3 Recovery Processes
18.4 Transaction Design Issues and Examples
18.4.1 Transaction Boundary and Hot Spots
18.4.2 Transaction Boundary Examples
18.4.3 Tennis Court Reservation Case
Tools for Transaction Design
18.5.1 Isolation Levels
18.5.2 Timing of Integrity Constraint Enforcement
18.5.3 Save Points
18.5.4 Relaxed Transaction Consistency Model
18.6 Workflow Management
18.6.1 Workflow Properties
18.6.2 Enabling Technologies for Workflows
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 18.A: SQL Syntax Summary
Chapter 19: Distributed Processing, Parallel Database Processing, and Distributed Databases
Learning Objectives
Overview
19.1 Overview of Distributed Processing and Distributed Data
19.1.1 Motivation for Distributed Processing with Databases
19.1.2 Motivation for Parallel Database Processing
19.1.3 Motivation for Distributed Data
19.1.4 Motivation for Cloud-Based Computing
19.1.5 Summary of Advantages and Disadvantages
19.2 Database Architectures for Distributed Processing
19.2.1 Design Issues
19.2.2 Basic Architectures
19.2.3 Advanced Architectures
19.2.4 Cloud Computing Architectures
19.3 Parallel Database Processing
19.3.1 Architectures and Design Issues
19.3.2 Commercial Parallel Database Technology
19.3.3 Big Data Parallel Processing Architectures
19.4 Architectures for Distributed Database Management Systems
19.4.1 Component Architecture
19.4.2 Schema Architectures
19.5 Transparency for Distributed Database Processing
19.5.1 Motivating Example
19.5.2 Fragmentation Transparency
19.5.3 Location Transparency
19.5.4 Local Mapping Transparency
19.5.5 Transparency and Scalability in Oracle Distributed Databases
19.6 Distributed Database Processing
19.6.1 Distributed Query Processing
19.6.2 Distributed Transaction Processing
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Chapter 20: DBMS Extensions for Object-Relational and NoSQL Databases
Learning Objectives
Overview
20.1 Motivation for Object Database Management
20.1.1 Complex Data
20.1.2 Type System Mismatch
20.1.3 Application Examples
20.2 Object-Relational Features in the SQL Standard
20.2.1 User-Defined Types
20.2.2 Table Definitions
20.2.3 Subtable Families
20.2.4 Manipulating Complex Objects and Subtable Families
20.3 Object-Relational Features in Oracle
20.3.1 Defining User-Defined Types and Typed Tables in Oracle
20.3.2 Using Typed Tables in Oracle
20.3.3 Dependencies among Types and Typed Tables
20.3.4 Other Object-Relational Features in Oracle
20.4 Overview of NoSQL Database Management
20.4.1 Motivation and Features
20.4.2 Data Models in NoSQL DBMSs
20.5 Database Definition and Manipulation with Couchbase N1QL
20.5.1 JavaScript Object Notation (JSON)
20.5.2 Couchbase N1QL Statements
Closing Thoughts
Review Concepts
Questions
Problems
References for Further Study
Appendix 20.A: INSERT Statements for N1QL
Appendix 20.B: Object-Relational Features in PostgreSQL
Glossary
Bibliography
Index

Über Autor(innen)

Mannino, Michael
Michael V. Mannino has been involved with database technology since 1978, when he began graduate studies at the University of Arizona. He has taught database management since 1983 at several major universities (University of Florida, University of Texas at Austin, University of Washington, and University of Colorado Denver). His audiences included undergraduate information technology students, graduate information technology students, MBA students, and doctoral students, as well as hundreds of thousands of Coursera learners. He has also been active in database research with publications in major journals of the IEEE (Transactions on Knowledge and Data Engineering and Transactions on Software Engineering), ACM (Communications, Journal of Data and Information Quality, Transactions on Management Information Systems, and Computing Surveys), and INFORMS (INFORMS Journal on Computing and Information Systems Research). His research includes several popular survey and tutorial articles as well as many papers presenting original research. Practical results of his research have been incorporated into Chapter 13 on management of data warehouse development, especially the design and implementation of the educational game, Emerge2Maturity, for providing insights about data warehouse maturity in organizations.