Training

 

 

Popular Courses

Browse Our Free Resources

  • whitepapers
  • whitepapers
  • webinars
  • blogs

Our Locations

Training Centres

Vancouver, BC
Calgary, AB
Edmonton, AB
Toronto, ON
Ottawa, ON
Montreal, QC
Hunt Valley
Columbia

locations map

Calgary

550 6th Av SW
Suite 475
Calgary, AB
T2P 0S2

Toronto

821A Bloor Street West
Toronto, ON
M6G 1M1

Vancouver

409 Granville St
Suite 902
Vancouver, BC
V6C 1T2

U.S. Office

436 York Road
Suite 1
Jenkintown, PA
19046

Other Locations

Dallas, TX
Miami, FL

Home > Training > Data Warehousing > Database Design Training

Database Design Training

Course#: WA1824

The workshop provides a full discussion of and experience with 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.

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 of WA1824 Database Design 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

10. Explanatory Texts

We regularly offer classes in these and other cities. Atlanta, Austin, Baltimore, Calgary, Chicago, Cleveland, Dallas, Denver, Detroit, Houston, Jacksonville, Miami, Montreal, New York City, Orlando, Ottawa, Philadelphia, Phoenix, Pittsburgh, Seattle, Toronto, Vancouver, Washington DC.
*Your name:

*Your e-mail:

*Phone:

*Company name:

Additional notes:

We have received your message. A sales representative will contact you soon.

Thank you!.

more details
buy this course

Register for a courseware sample

It's simple, and free.

 

Thank You!

You will receive an email shortly containing a link to download the requested sample of the labs for this course.