What you will learn

Upon successful completion of this course, the student will be able to understand:

  • A step by step way to do design
  • The use of logical model in design
  • How to do a design that meets business requirements
  • How to do a design that meets service level requirements
  • How to optimize (de-normalize) data
  • How to index databases
  • How to ensure database integrity
Audience

 

  • Database Designers
  • CASE Specialists
  • Data Analysts
  • Systems Analysts
  • Data/Database Administrators.
Prerequisites

To get the most out of design, it is advisable that you have an understanding of analysis, and specifically Logical Data Modeling. This can be achieved by attending our Logical Data Modeling workshop or having equivalent experience.

Duration
2 day

Outline for Database Design

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

10. Explanatory Texts