The Theory, Practice & Methodology of
Relational Database Design and Programming

OpenLine Consulting
Lecture Slides
Jan 2008

This document and the corresponding slides are made available under a
Creative Commons Attribution-NonCommercial-ShareAlgike 2.5 License,
and are available at http://www.openlineconsult.com/db
Copyright © Ellis S. Cohen, 2008

SUMMARY OF LECTURES

SQL

Introduction to Relational Databases and SQL
Basic SQL
Grouping
Natural Joins
Relational State Constraints
Relational State Assertions
Inner Joins
Outer Joins
Views
Collection Operators
Subqueries
Extended SQL & the Relational Calculus
SQL for Data Warehouses
Object-Relational Database Programming
Databases and XML

Conceptual & Relational Design

Introduction to Conceptual Database Design
Conceptual State Constraints
The Relational Model & Relational Mapping
Relational State Constraints
Relational Mapping with Constraints & Domains
Dependent & Identifying Relationships
M:N Relationships & Bridge Classes
Multivalued Attributes & Keys
Factored Relationships
Relationship Classes & N-ary Relationships
1-1 Relationships
Subclasses
Union Subclasses & Disjoint 1:M Relationships
Functional Dependencies & Normalization
Basic Normal Forms
Advanced Normalization
Introduction to Data Warehouse Design

Designing & Programming Database Applications

Introduction to Designing & Programming Database Applications
Conditions & Roles
Designing & Programming Database Applications
Roles & Constraints
Embedded Database Programming using PL/SQL
Implementing the Middle-Tier
Maintaining Session State in the Data-Tier
Transactions
Database Representation & Indexing
Query & Application Performance
Specifying and Enforcing Transition Constraints
Enforcement of State Constraints
Consistency & Initialization
Enforcing State & Transition Constraints with Triggers
Row Triggers
Specifying & Enforcing Access Constraints
Cursors
Security and Data-Tier Implementations
Enforcing Access Constraints with Privileges
Using Data Structures in Embedded Programs
Organization of Large DB Applications

Relational Database Theory

Extended SQL & the Relational Calculus
Basic Relational Algebra
Advanced Relational Algebra
Functional Dependencies & Normalization
Basic Normal Forms
Advanced Normalization

Advanced Topics

Introduction to Data Warehouse Design
SQL for Data Warehousing
Introduction to Data Mining
Introduction to Objects & Databases
Object-Relational Database Programming
Databases and XML

DETAILED LECTURE CONTENTS


1. Introduction to Relational Databases & SQL (slides)
Overview of Databases
Introduction to SQL
Oracle SQL Tools

2. Introduction to Conceptual Database Design (slides)
Entity Classes
Relationships & ER Diagrams
1:M Relationship Design
Multiple Relationships & the Fan Traps
Conceptual Design
Other ER Models
Mandatory Participation
Reflexive 1:M Relationships
Class Identification & Surrogate Keys
Redundancy & Anomalies
Simple Functional Dependencies
Simple Conceptual Normalization

3. Introduction to Designing & Programming Database Applications (slides)
User Operations in Database Applications
Job Board Database Design
Identifying User Operations
Queries Following Actions
Operation Parameters
User Operations and Conceptual ER Models
Database Application Programming & Design

4. Conditions & Roles (slides)
Pre-Conditions
Post-Conditions
Using Operation Conditions
User Roles
Roles & Entity Classes

5. Basic SQL (slides)
The SELECT Statement
Creating Tables
Performance and Indexing
Duplicate Elimination
Aggregate Functions
Distinct Aggregation

6. Conceptual State Constraints (slides)
Conceptual State Constraints
Entity Constraints
Representing Entity Constraints Visually
Functional Dependencies as Entity Class Constraints
Relationship Constraints
Mandatory Partiicpation in Other ER Models
Optional Participation
Participation & Cardinality
More About Relationship Constraints
General Conceptual State Constraints
The Chasm Trap & Redundancy
Factored Relationships

7. Grouping (slides)
Grouped Aggregation
Restriction and Grouping
Group Restriction

8. The Relational Model & Relational Mapping (slides)
Representing Relationships as Bridge Tables
Relational Models & Referential Integrity
Foreign Keys
Relational Mapping of 1:M Relationships
Relational Mapping Exercises
Mapping Reflexive Relationships
Conceptual & Relational Models
Defining and Changing Attributes
Assigning Sequential & Unique Values to Attributes
Metadata & System Data

9. Natural Joins (slides)
Natural Joins
Natural Join of 1:M Relationships
Natural Joins and Participation
Natural Joins and Attributes
Joining Multiple Tables

10. Relational State Constraints (slides)
Relational State Constraints
Check Constraints
Not Null Constraints
Simple Uniqueness Constraints
Composite Primary Keys
Composite Uniqueness Constraints
Simple Foreign Key Constraints
Deletion Integrity
Composite Foreign Key Constraints
Foreign Keys for Unique Attributes
Defining and Changing Constraints

11. Relational State Assertions (slides)
Relational State Assertions
Assertions with EXISTS
Comparing Vector Relations & Scalar Values
Comparing Relations
Uniqueness Assertions

12. Relational Mapping with Constraints & Domains (slides)
Relational Models and Mappings
Adding Constraints & Surrogate Keys During Mapping
Mapping Participation and Cardinality Constraints
Mapping Conceptual to Relational State Constraints
Conceptual Domains
Composite Attribute Domains
Entity Class Upgrades & Downgrades

13. Dependent & Identifying Relationships (slides)
Dependent Relationships and Entity Classes
Dependent Relationship Issues
Composition & Containment
Discriminating Dependent Instances
Discriminator-Based Identification
Identifying Relationships and Weak Entity Classes
ER Models for Identifying Relationship
Additional Relationships with Weak Entity Classes
Weak Cascades
Weak Entity Class Design Exercises

14. Inner Joins (slides)
Cross Joins and Natural Joins
Join Diagrams
Restrictions and SemiJoins
Self Joins
More Complex Joins
Joins with Duplicate Elimination
Joins with Counting & Grouping
State Assertions with Joins

15. Outer Joins (slides)
Natural Outer Joins
1:M Outer Joins
Un-Natural Outer Joins
Outer Join Conditions

16. Views (slides)
Introduction to Views
View-Based Problem Solving
Views and Inspctability
Table Expressions, Inline Views & Factoring
Using Views with Grouping
Views and Outer Joins
State Assertions Using Manifest Views
Complex State Assertions
View-Based Modification
Materialized Views

17. M:N Relationships & Bridge Classes (slides)
M:N Relationships
M:N Relationships and Cascading Delete
Joins Involving M:N Relationships
Bridge Entity Classes
Bridge Class Attributes
Partial Associations
Using Dependent & Identifying Relationships
Uniqueness
Weak Bridge Classes
Weak Bridge Classes with Attributes
Weak Bridge Classes with Participation & Cardinality
Discriminated Weak Bridge Classes
Relationships with Bridge Entity Classes

18. Multivalued Attributes & Keys (slides)
Multivalued Attributes & Conceptual Domains
Mapping Multivalued Attributes to Weak Entity Classes
Single Table Multivalued Attribute Mappings
Mapping M:N Relationships Using Multivalued Keys

19. Collection Operators (slides)
Union
Intersect & Except
Collection Operators & Joins
Transitive Closure and Recursive Views

20. Factored Relationships (slides)
Factoring Relationships
Factored 1:M Relationship with a Parent Weak Entity Class
Dependent & Contained Relationships
Factored 1:M Relationship between Weak Entity Classes
Factored Dependent Relationship between Weak Entity Classes
Inclusion Constraints and Factored 1:M Relationships with a Child Weak Entity Class
Factored M:N Relationships

21. Relationship Classes & N-ary Relationships (slides)
Relationship Classes
Discriminated Relationship Classes
1:M Discriminated Relationships
Relationships with Relationship Classes using Link Extensions
N-ary Relationships
Participation in N-ary Relationships
Partial N-ary Relationships
Comparing Relationship Models
1:M:N Relationships
1:1:M Relationships
1:1:1 Relationships

22. 1:1 Relationships (slides)
1:1 Relationships & Mappings
1:1 Mappings & Performance
Modeling, Transforming & Mapping Parallel Relationships
1:1 Dependent Relationships
1:1 Identifying Relationships

23. Subqueries (slides)
Scalar Subqueries
Vector Subqueries
Subqueries and View Modification
Correlated Scalar Subqueries
Complex Correlated Subqueries
Correlated Subqueries and Grouping
Correlated Updates & Deletes
ANY & ALL
Existence & Non-Existence

24. Subclasses (slides)
Identified Subclasses
Multiple & Multi-Level Subclasses
Disjoint Subclasses
Complete Disjoint Subclasses
Subclass Discriminators
Subclasses & Relational Integrity
Relational Mapping of Subclasses
Relationships with Subclasses
Relationships with Superclasses
Dependent Subclasses
Multiple Inheritance
Subclassing Relationships

25. Union Subclasses & Disjoint 1:M Relationships (slides)
Rooted Union Subclasses
Non-Rooted Union Subclasses
Disjoint 1:M Relationships
1:M Relationships with Union Subclasses

26. Extended SQL & the Relational Calculus (slides)
Extending SQL with Existential Subqueries
Extending SQL with Universal Subqueries
State Assertions with Qualifier Subqueries
Extending SQL with Attribute Filtering
The Tuple Relational Calculus
The Domain Relational Calculus

27. Basic Relational Algebra (slides)
Relational Algebra using REAL
Operator Composition
Extended Projection
Comparisons
Case Expressions
Duplicate Elimination
Aggregate Functions
Distinct Aggregation

28. Advanced Relational Algebra (slides)
Cross Joins & Natural Joins
Joins & Renaming
Natural Outer Joins
Un-Natural Outer Joins
Views
Collection Operators
Assignment & Reassignment
Relational Equivalence & Completeness
Relational Division

29. Functional Dependencies & Normalization (slides)
Normalization & Redundancy
Simple Functional Dependencies
Simple Conceptual Normalization
Redundancy & FD Constraints
Simple Functional Dependencies and Normalization
Simple Relational Normalization
The Reverse Fan Trap
Composite Functional Dependencies
Composite Functional Dependencies & Normalization

30. Basic Normal Forms (slides)
Normal Forms
1st Normal Form
2nd Normal Form
Conceptual 2NF Normalization
PURE 3rd Normal Form
MIXED 3rd Normal Form
Factoring & MIXED 3rd Normal Form
Normalization with Nested Determinants
Normalization with Overlapping Determinants

31. Advanced Normalization (slides)
Lossless & Dependency-Preserving Decomposition
Boyce-Codd Normal Form
Standard Decomposition of Non-Symmetric BCNF Violations
Decomposition of Non-Symmetric BCNF Violations Using Inclusion Constraints
Conceptual BCNF Resolution
4th Normal Form & Multivalued Dependencies
Pseudo-4NF Violations
5th Normal Form
Eliminating Assertions with Referential Integrity

32. Designing & Programming Database Applications (slides)
Database Application Architecture
User Operations in Database Applications
Session-Based Implementation of User Operations
Database Client-Side Execution
Server-Side Execution & Stored DB Operations
Implementing Database Applications

33. Roles & Constraints (slides)
Role Models and Application-Based Access Control
Multiple Role Models
Transition Constraints & Post-Conditions
Access Constraints & Pre-Conditions
Characterizing Access Constraints
System Constraints
Behavior Constraints Associated with Potential State Constraint Violations

34. Embedded Database Programming using PL/SQL (slides)
Basic PL/SQL
Conditional Statements
CASE Expressions & Statements
Loops
Stored Functions
Stored Procedures
Stored Packages
Sequential Values
Exceptions
Record Types
Query-Based FOR Loops
Dynamic SQL

35. Implementing the Middle Tier (slides)
GUI-Based User Interfaces
Command-Based User Interfaces
Client vs. Server-Side Execution
Middle-Tier Data & Cross-Tier Communication

36. Maintaining Session State in the Data-Tier (slides)
Temporary Session Tables
Package Variables
Maintaining Session State in the Data Tier
Multi-User & Virtuak Conncetions
Shared Multi-User Connections
Adding Security to Multi-User Connections

37. Transactions (slides)
The ACID Properties
Transactions and Commit
Abort and Rollback
Statement Failure & Nested Rollback
SavePoints and Nested Transactions
Autonomous Transactions
Concurrency Problems
Locking
Deadlock
Read-Consistent Concurrency
Read-Committed Concurrency

38. Database Architecture & Performance (slides)
Introduction
Representing Tables in Memory
Indexing
B+ Trees
Composite Indexing
Representing Tables on Disk
Disk-Based Scanning
Disk-Based Indexing
Index-Only Scanning
Specialized Indexing Methods

39. Query & Application Performance (slides)
Optimization & Query Planning
Ordering, Grouping & Sorting
Simple SQL Tuning
Implementing Joins as Nested Loops
Join Implementation and Tuning
Other Performance Issues

40. Specifying and Enforcing Transition Constraints (slides)
Transition Constraints
Transitionalized Relations
Transition Assertions
Specialized Transition Predicates
Relational Transition Constraints
Enforcing Transition Constraints
Application-Enforced Rejection
Rejection and Rollback
Application-Enforced Correction
Deletion Integrity Constraints
Two-Phase Enforcement
Enforcing Operation-Independent Transition Constraints
Cascading Enforcement
Specifying Output-Related Post-Conditions
Full Transition Assertions

41. Enforcing State Constraints (slides)
State Constraint Enforcement Approaches
Using Manifest Views
Continuous State Constraint Enforcement
Operation-Dependent Post-Enforcement
Operation-Dependent Pre-Enforcement
Using Summary Tables to Increase Performance
Enforcing State Constraints by Correction
Enforcing Mandatory Parent Participation

42. Consistency & Initialization (slides)
Consistency Constraints
Derivation Constraints
Initialization Constraints
Initial Default Constraints

43. Enforcing State & TransitionConstraints with Triggers (slides)
Table-Based Constraint Contexts
Triggers
Statement Triggers
AFTER Statement Triggers
Implementing Derivation Using AFTER Statement Triggers
Application-Based vs Trigger-Based State Constraint Enforcement
Two-Phase Trigger-Based Enforcement
Transactions, Triggers & Deferred Constraints

44. Row Triggers (slides)
Row Triggers
Using Row Triggers for Enforcing State Constraints
BEFORE Row Triggers
Collecting Modifications for AFTER Statement Ttriggers
Querying Triggered Tables with Row Triggers
Modifying Triggered Tables with Row Triggers
Cascading Row Triggers
INSTEAD OF Triggers
Implementing Materialized Views

45. Specifying & Enforcing Access Constraints (slides)
Access Constraints
Relatonal Access Constraints
Enforcing Causal Access Constraints
Using Reusable Check Procedures
Enforcing Identity-Based Access Constraints
Post-Enforcement of Access Constraints
Access-Limiting Modification
Relational User Access Constraints
Relatonal Informational Access Constraints
Enforcing Informational Access Constraints
Access Restriction
Middle-Tier Identity
Data-Tier Identity
Access-Limiting Views
Trigger-Based Enforcement

46. Cursors (slides)
PL/SQL Cursors
Cursor-Based Fetch & Scrollability
Cursor Sensitivity
Cursor-Based Update
Cursor Variables & Parameters
Nested Cursors

47.Secuity and Data-Tier Implementations (slides)
Application-Based Security
Authentication & Encryption
Data-Tier Implementations
Data-Tier Implementation of Query Operations
Parameterized Queries Using Package Variables
Parameterized Queries Using Cursors
Parameterized Queries Using REF Cursors
Secure Data-Tier Implementations

48. Enforcing Access Constraints with Privileges (slides)
Discretionary Access Control Mechanisms (DAC)
Secure Updates with Views & Procedures
Dynamic Access Control using Database Identity
Database Applications Using Database Identity
Grant-Based Enforcement
Database vs Data-Tier Identity for Secure Gateways
Role-Based Data-Tier Implementation
Dynamic Role Assignment
Role Grant Based Enforcement Using RBAC (Role-Based Access Control)
Mapping Between Database & Application Identities

49. Using Data Structures in Embedded Programs (slides)
Intermediate Data Structure
PL/SQL Varying Arrays
PL/SQL Associative Arrays

50. Organization of Large DB Applications (slides)
Subsystems
Subsystem Design Problems
Subsystems & Information Hiding
Database-Oriented Subsystems

51. Introduction to Data Warehouse Design (slides)
Overview
Star Schema: Fact & Dimension Tables
The Star Schema & Denormalization
The Data Cube
ETL: Extraction, Transformation & Loading

52. SQL for Data Warehouses (slides)
Overview
The Star Schema
Viewing the Data Cube
Drill Down & Rollup
Cross Tabulations
Data Visualization
Trend & Rank Analysis
Materialized Views & Query Rewriting
Indexing for Data Warehouses

53. Introduction to Data Mining (slides)
Types of Data Mining
Data Mining Activities
Estimation: Classification & Approximation
Decision Trees
Clustering
Targeted Clustering & Association Rules
Market Basket Analysis
Scoring Market Basket Analysis

54. Introduction to Objects & Databases (slides)
Object Mapping and ODL
Object Query Languages
Object-Relational Mapping
Object-Oriented Databases (OODB's)
Summary

55. Object-Relational Database Programming (slides)
Object-Relational Databases (ORDB's)
Joins & Navigation
Cell Objects
Nested Tables

56. Databases and XML (slides)
Introduction to XML
DTD's: Document Type Definitions
XPath 1.0
Predicates
XPath Nodes & Axes
XPath 2.0
XQuery
Element Construction with XQuery
XML-Relational Databases