UMBC Training Centers logo

Database Design

 

Course Description | Course Outline | Data Warehousing | IT Training

1. Introduction to Design

  • Goals of Database Design
  • Deliverables of Database Design
  • Context of Design

2. Overview of Design

  • Review of Database Concepts
  • Summary of Database Design Steps
  • Simple Sample Database Design

3. Preliminary Design

  • The data automation boundary
  • Factors Affecting Design:
    • Volumetrics
    • Number of Columns
    • Number of Rows
    • Table Ratio
    • Query and data complexity
    • Data stability
    • Query Data Usage
  • Data Complexity
  • Query Complexity
  • Concurrency
  • Transition from Logical to Physical

4. High Level Design

  • Definition of First-Cut Physical Model
  • Applying Safe Trade-Offs
    • Resolving Subtypes
    • Partitioning Tables
    • Combining One-to-Ones
    • Splitting Wide Tables
    • Violating First Normal Form
    • Collapsing Trivial Code Tables
    • Collapsing Similar Tables

5. Detailed Level Design

  • Definition of the implementation model
  • Applying Aggressive Trade-Offs
    • Storing Derived Data Elements
    • Creating Summary Tables
    • Adding Redundant Data
    • Adding Redundant Relationships
    • Criteria for Denormalizing
    • Using Surrogate Keys

6. Specialized Optimizations

  • Designing Hierarchies and Rollups
  • Designing Recursions
    • Standard "BOM" Recursion
    • Flattened Recursions
    • Fixed Hierarchies
    • Descendent or Speed Tables
  • Dealing with High Interest/Changeable Data
  • Full vs. Partial History
  • Hot Attributes

7. Indexing

  • B-tree Indices
  • Criteria for Index Selection, such as:
    • Initial Selection of a Thin Slice
    • Avoidance of Sorts or Cartesian Product
    • Guidelines for Selection of Indices
    • Index-only access and joins
    • Bitmapped Indices
    • Star join Indices

8. Integrity

  • User vs. System Applied Integrity
    • Entity Integrity
    • Referential Integrity
    • Integrity Constraints
  • Update/Delete Integrity Enforcement
  • Self-Referencing Constraints
  • Extending Integrity:
    • System Enforced
    • Triggers
    • Stored Procedures
    • Column Constraints

9. Access Path Analysis

  • Individual Transaction Load Analysis
  • Composite Load Analysis