Welcome to Debtech International


Onsite Seminar

Database Design: Transforming A Logical Data Modeling to a Physical Design


Database design should accurately translate the logical data model into a physical data model and database that is robust, user-friendly, optimized, and maintainable. Design builds directly upon the deliverables from analysis. This course discusses the tasks, constraints, factors and deliverables of database design. It presents a step-by-step process for designing a database based on the business requirements represented in a logical data model.

The workshop provides a full discussion of and experience with physical modeling and database design. Three types of trade-offs to the data will be applied - technology, safe and aggressive tradeoffs. Safe compromises are trade-offs that will optimize the data model without compromising integrity or redundancy. An example of a safe trade-off is to partition a table. Aggressive compromises will optimize the data but may comprise data integrity. An example of this is to store redundant data. Technology trade-offs use overhead features of the DBMS, such as indices. Indices can improve query performance but can compromise maintenance performance. You will learn how to apply safe, aggressive and technology trade-offs for optimizing the data.

The workshop divides design into Preliminary Design, High Level Design and Detailed Level Design. Preliminary Design accomplishes the transition from analysis and determines the technical architecture of the database. It also decides what data will reside on which processors. Initially, the data model is implemented as close to the logical data model as possible. Integrity of the data is a main concern.

The truly unique contributions of this workshop are:

  • The identification of the seven major factors needed to do good database design.
  • The differentiation of three types of trade-offs, namely, safe, aggressive and technology trade-offs.
  • The tangible use of these factors in deciding how to apply the trade-offs
  • Criteria for handling difficult design problems such as hierarchies and recursion.

This workshop uses a case study. You complete the course with experience in design.

Objectives
Upon completion of this course attendees will:

  • Understand a step by step way to do design
  • Understand the use of logical model in design
  • Learn how to do a design that meets business requirements
  • Learn how to do a design that meets service level requirements
  • Learn how to optimize (de-normalize) data
  • Learn how to index databases
  • Learn how to ensure database integrity

Prerequisite
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.

Course Outline

  • Introduction to Design
  • Goals of Database Design
  • Deliverables of Database Design
  • Context of Design
  • Overview of Design
  • Review of Database Concepts
  • Summary of Database Design Steps
  • Simple Sample Database Design
  • 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

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

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

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

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

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

Duration
2 days

Course Format
Lecture, group discussions and exercises

Instructor
Tom Haughey

To request a quote for this in-house seminar
Please call (561) 218-4752 or email info@debtechint.com

Return to Onsite Seminars Table of Contents