|
Home > Training >
Data Warehousing
>
|
WA1824 Database Design Training and Courseware
|
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 |
|
|
Course Outline
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
- 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.
|
|
 |
|
Buy on-site and customized class
Attend a public class
08/13/2012 -
Vancouver
$1,575.00
Enroll
08/13/2012 -
Montreal
$1,575.00
Enroll
08/27/2012 -
Ottawa
$1,575.00
Enroll
09/24/2012 -
Toronto
$1,575.00
Enroll
09/24/2012 -
Calgary
$1,575.00
Enroll
10/15/2012 -
Vancouver
$1,575.00
Enroll
10/22/2012 -
Toronto
$1,575.00
Enroll
10/22/2012 -
Montreal
$1,575.00
Enroll
10/22/2012 -
Ottawa
$1,575.00
Enroll
11/26/2012 -
Calgary
$1,575.00
Enroll
|
|
