Schedules:

There are currently no scheduled classes for this course.

 
  Data Modeling Essentials
 

3-Day (SQ-DMODESS-301-EN)

Description
Target Audience
Prerequisites
Course Objectives
Course Summary Outline

Description

A good application starts with a good database design. Data is nowadays the most important assets of a company. In our high competitive world, it is an advantage if decisions are supported with timely information from the data. However, low quality of data leads to unreliable information.

Data Modeling Essentials seminar is a high-end course, intended for SQL Server and other RDBMS professionals, including solution architects and IT managers. The course introduces all modern data models, including relational, dimensional and object-relational. Data quality issues are covered as well. During the course, many design myths are going to be revealed. 

Target Audience

This course is intended for solution architects, IT managers who want to understand a correct design of a database, database and other developers and advanced database administrators.

Prerequisites

Before attending this course, it is recommended that students have moderate experience with developing and/or deploying transactional and business intelligence applications.

Course Objectives

During three days of intensive work you will learn:

  • Relational model in depth, including:
    • Domains, Tuples and Relations
    • Relational algebra and calculus
    • Normalization
    • Entity-Relationship approach
    • Using super- and subtypes
    • Modeling constraints and business rules
    • Modeling graphs, trees and hierarchies
    • Using temporal and spatial data
    • Making relational schema dynamic
    • Modeling for performance
  • Modeling for Business Intelligence (BI) applications
    • Dimensional modeling for Data Warehouses (DW)
    • Advanced dimensional modeling problems solved by design
    • Unified Dimensional Model (UDM)
    • Data preparation for Data Mining
  • Data quality issues
    • Data quality dimensions
    • Modeling for data quality
    • Measuring data quality and information in data
    • Merging data from multiple sources for Operational Data Store (ODS), basis for Customer Relationship Management (CRM) applications
  • Object-Relational concepts
    • Object-Oriented Programming (OOP) basics
    • What is an object-relational database?

Course Summary Outline

Module 00: Relational Model

  • Introduction
    • Domains, Tuples and Relations
    • Data Integrity
  • Relational Algebra
    • Basic Relational Operators
    • Eight Codd's Relational Algebra Operators
    • Additional Operators
  • Relational Calculus

Module 01: Normalization

  • Normalization
    • 1st Normal Form
    • 2nd Normal Form
    • 3rd Normal Form
    • Boyce-Codd Normal Form
    • 4th Normal Form
    • 5th Normal Form
  • LAB 01: Normalization

Module 02: ER Approach

  • ER Approach
    • Entities
    • Relationships
    • Attributes
    • ER Diagrams
    • Modeling Approach
  • Super- and Sub-types
  • Missing Information
  • Naming Conventions
  • LAB 02: Entities and Relationships

Module 03: Constraints

  • Data Integrity Summary
  • Keys
  • Business Rules
  • Referential Integrity
  • Lookup Tables
  • The Design Process
  • LAB 03: Business Rules

Module 04: Advanced Modeling and Constraints

  • Modeling Graphs, Trees and Hierarchies
  • Temporal Support in Relational Databases
  • Using Spatial Data
  • Making Relational Schema Dynamic
  • LAB 04: Advanced Modeling

Module 05: Modeling for Performance

  • Using Data Types
  • Declarative Constraints
  • Designing Indexes
  • Data Partitioning
  • LAB 05: Modeling for Performance

Module 06: Dimensional Modeling

  • Introduction
  • Business View and Conceptual Schema
  • Star Schema
  • Data Warehouse and Other Terms
  • OLAP Cubes
  • Advanced Problems
  • Unified Dimensional Model
  • LAB 06: Dimensional Modeling

Module 07: Preparing Data for Data Mining

  • Introduction to Data Mining
  • Preparing the data
    • Cases and Variables
    • Ways to Measure Data Values
    • Derived Variables
    • Missing Values and Outliers
    • Time Series
    • Different Data Sets
  • LAB 07: Data Preparation for Data Mining

Module 08: Data Quality

  • Data Quality Dimensions
  • Measuring Data Quality
  • Measuring Information in Your Data
  • Measuring Improvements
  • Merging data from multiple sources for Operational Data Store
  • The Corporate information Factory (CIF)
  • LAB 08: Data Quality

Module 09: Object-Relational Databases

  • Object-Oriented Programming Basics
  • What is an Object-Relational Database?
    • Foreword
    • Logical Differences
    • Domain-Key Normal Form
    • XML Data Type
    • .NET Collections
  • LAB 09: Object-Relational Databases