Database Processing: Fundamentals, Design, and Implementation

Title Page
Copyright Page
Brief Contents
Contents
Foreword to the 40th Anniversary Edition
Preface
About the Authors
PART 1 Getting Started
	Chapter 1: Introduction
		Chapter Objectives
		The Importance of Databases in the Internet and Mobile App World
		The Characteristics of Relational Databases
			A Note on Naming Conventions
			A Database Has Data and Relationships
			Databases Create Information
		Database Examples
			Single‐User Database Applications
			Multiuser Database Applications
			E‐Commerce Database Applications
			Reporting and Data Mining Database Applications
		The Components of a Database System
			Database Applications and SQL
			The DBMS
			The Database
		Personal Versus Enterprise‐Class Database Systems
			What Is Microsoft Access?
			What Is an Enterprise‐Class Database System?
		Database Design
			Database Design from Existing Data
			Database Design for New Systems Development
			Database Redesign
		What You Need to Learn
		A Brief History of Database Processing
			The Early Years
			The Emergence and Dominance of the Relational Model
			Post‐Relational Developments
		Summary
		Key Terms
		Review Questions
		Exercises
	Chapter 2: Introduction to Structured Query Language
		Chapter Objectives
		Cape Codd Outdoor Sports
		Business Intelligence Systems and Data Warehouses
			The Cape Codd Outdoor Sports Extracted Retail Sales Data Database
			The RETAIL_ORDER Table
			The ORDER_ITEM Table
			The SKU_DATA Table
			The BUYER Table
			The CATALOG_SKU_20## Tables
			The Complete Cape Codd Data Extract Schema
			Data Extracts Are Common
		SQL Background
		The SQL SELECT/FROM/WHERE Framework
			Reading Specified Columns from a Single Table
			Specifying Column Order in SQL Queries from a Single Table
		Submitting SQL Statements to the DBMS
			Using SQL in Microsoft Access 2019
			Using SQL in Microsoft SQL Server 2019
			Using SQL in Oracle Database
			Using SQL in Oracle MySQL 8.0
		SQL Enhancements for Querying a Single Table
			Reading Specified Rows from a Single Table
			Reading Specified Columns and Rows from a Single Table
			Sorting the SQL Query Results
			SQL WHERE Clause Options
		Performing Calculations in SQL Queries
			Using SQL Built‐in Aggregate Functions
			SQL Expressions in SQL SELECT Statements
		Grouping Rows in SQL SELECT Statements
		Querying Two or More Tables with SQL
			Querying Multiple Tables with Subqueries
			Querying Multiple Tables with Joins
			Comparing Subqueries and Joins
			The SQL JOIN ON Syntax
			SQL Queries on Recursive Relationships
			Outer Joins
			Using SQL Set Operators
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
PART 2 Database Design
	Chapter 3: The Relational Model and Normalization
		Chapter Objectives
		Relational Model Terminology
			Relations
			Characteristics of Relations
			Alternative Terminology
			To Key or Not to Key—That Is the Question!
			Functional Dependencies
			Finding Functional Dependencies
			Keys
		Normal Forms
			Modification Anomalies
			A Short History of Normal Forms
			Normalization Categories
			From First Normal Form to Boyce‐Codd Normal Form Step‐by‐Step
			Eliminating Anomalies from Functional Dependencies with BCNF
			Eliminating Anomalies from Multivalued Dependencies
			Fifth Normal Form
			Domain/Key Normal Form
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
	Chapter 4: Database Design Using Normalization
		Chapter Objectives
		Assess Table Structure
		Designing Updatable Databases
			Advantages and Disadvantages of Normalization
			Functional Dependencies
			Normalizing with SQL
			Choosing Not to Use BCNF
			Multivalued Dependencies
		Designing Read‐Only Databases
			Denormalization
			Customized Duplicated Tables
		Common Design Problems
			The Multivalue, Multicolumn Problem
			Inconsistent Values
			Missing Values
			The General‐Purpose Remarks Column
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questionsn
	Chapter 5: Data Modeling with the Entity‐Relationship Model
		Chapter Objectives
		The Purpose of a Data Model
		The Entity‐Relationship Model
			Entities
			Attributes
			Identifiers
			Relationships
			Maximum Cardinality
			Minimum Cardinality
			Entity‐Relationship Diagrams and Their Versions
			Variations of the E‐R Model
			E‐R Diagrams Using the IE Crow’s Foot Model
			Strong Entities and Weak Entities
			ID‐Dependent Entities
			Non‐ID‐Dependent Weak Entities
			The Ambiguity of the Weak Entity
			Subtype Entities
		Patterns in Forms, Reports, and E‐R Models
			Strong Entity Relationship Patterns
			ID‐Dependent Relationship Patterns
			Mixed Identifying and Nonidentifying Relationship Patterns
			The For‐Use‐By Subtype Pattern
			Recursive Relationship Patterns
		The Data Modeling Process
			The College Report
			The Department Report
			The Department/Major Report
			The Student Acceptance Letter
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
	Chapter 6: Transforming Data Models into Database Designs
		Chapter Objectives
		The Purpose of a Database Design
		Create a Table for Each Entity
			Selecting the Primary Key
			Specifying Alternate Keys
			Specifying Column Properties
			Verify Normalization
		Create Relationships
			Relationships Between Strong Entities
			Relationships Using ID‐Dependent Entities
			Relationships with a Weak Non‐ID‐Dependent Entity
			Relationships in Mixed Entity Designs
			Relationships Between Supertype and Subtype Entities
			Recursive Relationships
			Representing Ternary and Higher‐Order Relationships
			Relational Representation of the Highline University Data Model
		Design for Minimum Cardinality
			Actions When the Parent Is Required
			Actions When the Child Is Required
			Implementing Actions for M‐O Relationships
			Implementing Actions for O‐M Relationships
			Implementing Actions for M‐M Relationships
			Designing Special Case M‐M Relationships
			Documenting the Minimum Cardinality Design
			An Additional Complication
			Summary of Minimum Cardinality Design
		The View Ridge Gallery Database
			View Ridge Gallery Database Summary of Requirements
			The View Ridge Data Model
			Database Design with Data Keys
			Minimum Cardinality Enforcement for Required Parents
			Minimum Cardinality Enforcement for the Required Child
			Column Properties for the View Ridge Database Design Tables
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
PART 3 Database Implementation
	Chapter 7: SQL for Database Construction and Application Processing
		Chapter Objectives
		The Importance of Working with an Installed DBMS Product
		The View Ridge Gallery Database
		SQL DDL and DML
		Managing Table Structure with SQL DDL
			Creating the VRG Database
			Using SQL Scripts
			Using the SQL CREATE TABLE Statement
			Variations in SQL Data Types and SQL/PSM
			Creating the VRG Database ARTIST Table
			Creating the VRG Database WORK Table and the 1: N ARTIST‐to‐WORK Relationship
			Implementing Required Parent Rows
			Implementing 1:1 Relationships
			Casual Relationships
			Creating Default Values and Data Constraints with SQL
			Creating the VRG Database Tables
			The SQL ALTER TABLE Statement
			The SQL DROP TABLE Statement
			The SQL TRUNCATE TABLE Statment
			The SQL CREATE INDEX Statement
		SQL DML Statements
			The SQL INSERT Statement
			Populating the VRG Database Tables
			The SQL UPDATE Statement
			The SQL MERGE Statement
			The SQL DELETE Statement
		Using SQL Views
			Using SQL Views to Hide Columns and Rows
			Using SQL Views to Display Results of Computed Columns
			Using SQL Views to Hide Complicated SQL Syntax
			Layering Built‐in Functions
			Using SQL Views for Isolation, Multiple Permissions, and Multiple Triggers
			Updating SQL Views
		Embedding SQL in Program Code
			SQL/Persistent Stored Modules (SQL/PSM)
			Using SQL User‐Defined Functions
			Using SQL Triggers
			Using Stored Procedures
			Comparing User‐Defined Functions, Triggers, and Stored Procedures
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
	Chapter 8: Database Redesign
		Chapter Objectives
		The Need for Database Redesign
		SQL Statements for Checking Functional Dependencies
			What Is a Correlated Subquery
		How Do I Analyze an Existing Database?
			Reverse Engineering
			Dependency Graphs
			Database Backup and Test Databases
		Changing Table Names and Table Columns
			Changing Table Names
			Adding and Dropping Columns
			Changing a Column Data Type or Column Constraints
			Adding and Dropping Constraints
		Changing Relationship Cardinalities
			Changing Minimum Cardinalities
			Changing Maximum Cardinalities
		Adding and Deleting Tables and Relationships
		Forward Engineering
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
PART 4 Enterprise Database Processing
	Chapter 9: Managing Enterprise Databases
		Chapter Objectives
		The Importance of Working with an Installed DBMS Product
		Database Administration
			Managing the Database Structure
			Physical Database Design and Optimization
		Concurrency Control
			The Need for Atomic Transactions
			Resource Locking
			Optimistic Versus Pessimistic Locking
			SQL Transaction Control Language and Declaring Lock Characteristics
			Implicit and Explicit COMMIT TRANSACTION
			Consistent Transactions
			Transaction Isolation Level
			SQL Cursors
		Database Security
			Processing Rights and Responsibilities
			DBMS Security
			DBMS Security Guidelines
			Application Security
			The SQL Injection Attack
		Database Backup and Recovery
			Recovery via Reprocessing
			Recovery via Rollback/Rollforward
		Managing the DBMS
			Maintaining the Data Repository
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
	Chapter 10: Managing Databases with Microsoft SQL Server 2019, Oracle Database, MySQL 8.0, and Arang
		Chapter Objectives
		Installing the DBMS
		Using the DBMS in the Cloud
		Using the DBMS Database Administration and Database Development Utilities
		Creating a Database
		Creating and Running SQL Scripts
		Reviewing the Database Structure in the DBMS GUI Utility
		Creating and Populating the View Ridge Gallery VRG Database Tables
		Creating SQL Views for the View Ridge Gallery VRG Database
		Importing Microsoft Excel Data into a Database Table
		Database Application Logic and SQL/Persistent Stored Modules (SQL/PSM)
		DBMS Concurrency Control
		DBMS Security
		DBMS Database Backup and Recovery
		Other DBMS Topics Not Discussed
		Choose Your DBMS Product(s)!
		Summary
		Key Terms
		Exercises
PART 5 Database Access Standards and Technology
	Chapter 11: The Web Server Environment
		Chapter Objectives
		A Web Database Application for the View Ridge Gallery
		Before You Read the Rest of Chapter!
		The Web Database Processing Environment
		Database Server Access Standards
		The ODBC Standard
			ODBC Architecture
			Conformance Levels
			Creating an ODBC Data Source Name
		The Microsoft .NET Framework and ADO.NET
			OLE DB
			ADO and ADO.NET
			The ADO.NET Object Model
		The Java Platform
			JDBC
			Java Server Pages (JSP) and Servlets
			Apache Tomcat
		Web Database Processing with PHP
			Web Database Processing with PHP and the NetBeans IDE
			Getting Started with HTML Web Pages
			The index.html Web Page
			Creating the index.html Web Page
			Using PHP
		Web Page Examples with PHP
			Example 1: Updating a Table
			Example 2: Using PHP Data Objects (PDO)
			Example 3: Invoking a Stored Procedure
			Challenges for Web Database Processing
			SQL Injection Attacks
		Extensible Markup Language (XML)
			The Importance of XML
			XML as a Markup Language
		Creating XML Documents from Database Data
			Using the SQL SELECT . . . FOR XML Statement
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
	Chapter 12: Data Warehouses and Business Intelligence Systems
		Chapter Objectives
		Business Intelligence Systems
		The Relationship Between Operational and BI Systems
		Reporting Systems and Data Mining Applications
			Reporting Systems
			Data Mining Applications
		Data Warehouses and Data Marts
			Problems with Operational Data
			Components of a Data Warehouse
			Data Warehouses Versus Data Marts
			Dimensional Databases
		Reporting Systems
			RFM Analysis
			OLAP
			Reporting System Components
			Reporting System Functions
		Data Mining
			Unsupervised Versus Supervised Data Mining
			Four Popular Data Mining Techniques
			Market Basket Analysis
			Decision Trees
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
	Chapter 13: Big Data, NoSQL, and the Cloud
		Chapter Objectives
		What Is Big Data?
			The Three Vs Plus One
			Big Data and NoSQL Systems
			The CAP Theorem
		Distributed Database Processing
			Types of Distributed Databases
			Challenges of Distributed Databases
		Object‐Relational Databases
		Big Data Processing Models
			MapReduce
			Hadoop
		Non‐Relational Database Management Systems
			Key‐Value Databases
			Document Databases
			Column Family Databases
			Graph Databases
		Virtualization
		Cloud Computing
		Using a Cloud Database Management System
			Connecting to Microsoft Azure
			Migrating Our Existing Local HSD_DW Database to Azure
			Creating a New Relational Database on Microsoft Azure
			Creating and Using a NoSQL Document Database in Azure
			Cloud Reflections
		Big Data, NoSQL Systems, and the Future
		Summary
		Key Terms
		Review Questions
		Exercises
		Case Questions
		The Queen Anne Curiosity Shop Project Questions
		Morgan Importing Project Questions
Appendices
Bibliography
Glossary
Index
	A
	B
	C
	D
	E
	F
	G
	H
	I
	J
	K
	L
	M
	N
	O
	P
	Q
	R
	S
	T
	U
	V
	W
	X