Relational Database Design and Implementation

Part I: Introduction

Introduction

Chapter 1: The Database Environment

• Abstract
• Defining a Database
• Systems that Use Databases
• Data “Ownership”
• Database Software: DBMSs
• Database Hardware Architecture
• Other Factors in the Database Environment
• Open Source Relational DBMSs

Chapter 2: Systems Analysis and Database Requirements

• Abstract
• Dealing with Resistance to Change
• The Structured Design Life Cycle
• Conducting the Needs Assessment
• Assessing Feasibility
• Generating Alternatives
• Evaluating and Choosing an Alternative
• Creating Design Requirements
• Alternative Analysis Methods

Part II: Relational database design theory

Introduction

Chapter 3: Why Good Design Matters

• Abstract
• Effects of Poor Database Design
• Unnecessary Duplicated Data and Data Consistency
• Data Insertion Problems
• Data Deletion Problems
• Meaningful Identifiers
• The Bottom Line

Chapter 4: Entities and Relationships

• Abstract
• Entities and Their Attributes
• Domains
• Basic Data Relationships
• Documenting Relationships
• Dealing with Many-to-Many Relationships
• Relationships and Business Rules
• Data Modeling Versus Data Flow
• Schemas

Chapter 5: The Relational Data Model

• Abstract
• Understanding Relations
• Primary Keys
• Representing Data Relationships
• Views
• The Data Dictionary
• A Bit of History

Chapter 6: Relational Algebra

• Abstract
• The Relational Algebra and SQL Example Database: Rare Books
• The Sample Data
• Making Vertical Subsets: Project
• Making Horizontal Subsets: Restrict
• Choosing Columns and Rows: Restrict and Then Project
• Union
• Join
• Difference
• Intersect

Chapter 7: Normalization

• Abstract
• Translating an ER Diagram into Relations
• Normal Forms
• First Normal Form
• Second Normal Form
• Third Normal Form
• Boyce–Codd Normal Form
• Fourth Normal Form
• Fifth Normal Form
• Sixth Normal Form

Chapter 8: Database Design and Performance Tuning

• Abstract
• Indexing
• Clustering
• Partitioning

Chapter 9: Codd’s Rules for Relational DBMSs

• Abstract
• Rule 0: The Foundation Rule
• Rule 1: The Information Rule
• Rule 2: The Guaranteed Access Rule
• Rule 3: Systematic Treatment of Null Values
• Rule 4: Dynamic Online Catalog Based on the Relational Model
• Rule 5: The Comprehensive Data Sublanguage Rule
• Rule 6: The View Updating Rule
• Rule 7: High-Level Insert, Update, Delete
• Rule 8: Physical Data Independence
• Rule 9: Logical Data Independence
• Rule 10: Integrity Independence
• Rule 11: Distribution Independence
• Rule 12: Nonsubversion Rule

Part III: Relational database design practice

Introduction

Chapter 10: Introduction to SQL

• Abstract
• A Bit of SQL History
• Conformance Levels
• SQL Environments
• Elements of a SQL Statement

Chapter 11: Using SQL to Implement a Relational Design

• Abstract
• Database Structure Hierarchy
• Schemas
• Domains
• Tables
• Modifying Database Elements
• Deleting Database Elements

Chapter 12: Using CASE Tools for Database Design

• Abstract
• CASE Capabilities
• ER Diagram Reports
• Data Flow Diagrams
• The Data Dictionary
• Code Generation
• Sample Input and Output Designs
• The Drawing Environment

Chapter 13: Database Design Case Study #1: Mighty-Mite Motors

• Abstract
• Corporate Overview
• Designing the Database

Chapter 14: Database Design Case Study #2: East Coast Aquarium

• Abstract
• Organizational Overview
• The Volunteers Database
• The Animal Tracking Database

Chapter 15: Database Design Case Study #3: SmartMart

• Abstract
• The Merchandising Environment
• Putting Together an ERD
• Creating the Tables
• Generating the SQL

Part IV: Using interactive SQL to manipulate a relational database

Introduction

Chapter 16: Simple SQL Retrieval

• Abstract
• Revisiting the Sample Data
• Choosing Columns
• Ordering the Result Table
• Choosing Rows
• Nulls and Retrieval: Three-Valued Logic

Chapter 17: Retrieving Data from More Than One Table

• Abstract
• SQL Syntax for Inner Joins
• Finding Multiple Rows in One Table: Joining a Table to Itself
• Outer Joins
• Table Constructors in Queries
• Avoiding Joins with Uncorrelated Subqueries

Chapter 18: Advanced Retrieval Operations

• Abstract
• Union
• Negative Queries
• The EXISTS Operator
• The EXCEPT and INTERSECT Operators
• Performing Arithmetic
• String Manipulation
• Date and Time Manipulation
• CASE Expressions

Chapter 19: Working With Groups of Rows

• Abstract
• Set Functions
• Changing Data Types: CAST
• Grouping Queries
• Windowing and Window Functions

Chapter 20: Data Modification

• Abstract
• Inserting Rows
• Updating Data
• Deleting Rows
• Inserting, Updating, or Deleting on a Condition: MERGE

Chapter 21: Creating Additional Structural Elements

• Abstract
• Views
• Temporary Tables
• Common Table Expressions (CTEs)
• Creating Indexes

Part V: Database implementation issues

Introduction

Chapter 22: Concurrency Control

• Abstract
• The Multiuser Environment
• Problems with Concurrent Use
• Solution #1: Classic Locking
• Solution #2: Optimistic Concurrency Control (Optimistic Locking)
• Solution #3: Multiversion Concurrency Control (Timestamping)
• Transaction Isolation Levels
• Web Database Concurrency Control Issues
• Distributed Database Issues

Chapter 23: Database Security

• Abstract
• Sources of External Security Threats
• Sources of Internal Threats
• External Remedies
• Internal Solutions
• Backup and Recovery
• The Bottom Line: How Much Security Do You Need?

Chapter 24: Data Warehousing

• Abstract
• Scope and Purpose of a Data Warehouse
• Obtaining and Preparing the Data
• Data Modeling for the Data Warehouse
• Data Warehouse Appliances

Chapter 25: Data Quality

• Abstract
• Why Data Quality Matters
• Recognizing and Handling Incomplete Data
• Recognizing and Handling Incorrect Data
• Recognizing and Handling Incomprehensible Data
• Recognizing and Handling Inconsistent Data
• Employees and Data Quality

Part VI: Beyond the relational data model

Introduction

Chapter 26: XML Support

• Abstract
• XML Basics
• SQL/XML
• The XML Data Type

Chapter 27: Object-Relational Databases

• Abstract
• Getting Started: Object-Orientation without Computing
• Basic OO Concepts
• Benefits of Object-Orientation
• Limitations of Pure Object-Oriented DBMSs
• The Object-Relational Data Model
• SQL Support for the OR Data Model
• An Additional Sample Database
• SQL Data Types for Object-Relational Support
• User-Defined Data Types and Typed Tables
• Methods

Chapter 28: Relational Databases and “Big Data”: The Alternative of a NoSQL Solution

• Abstract
• Types of NoSQL Databases
• Other Differences Between NoSQL Databases and Relational Databases
• Benefits of NoSQL Databases
• Problems with NoSQL Databases
• Open Source NoSQL Products

Part VII: Appendices

Appendix A: Historical Antecedents

Appendix B: SQL Programming

Appendix C: SQL Syntax Summary