DATABASE DESIGN– GTDW7

Course Description

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.

^^

Duration

2 days

^^

Target Audience

This course is suitable for Database Designers, CASE Specialists, Data Analysts, Systems Analysts, Data/Database Administrators.

^^

Course Prerequisites

To get the most out of design, it is advisable that you have an understanding of analysis, and specifically Logical Data Modeling.

If you have any questions or doubts as to whether you meet the pre-requisites for this course, or indeed are wondering which course best suits you, please consult with us to discuss your suitability for course attendance.

^^

Suggested Follow on Courses

Please contact us for further details.

^^

Course Content

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

^^

See more Other courses