Home » Training » Advanced Data Modeling Training with Erwin

Learn Erwin Data Modeling Tool and create Logical, Physical, Dimensional Data Model with Real-Time examples! 

If you are interested in this Data Modeling Training, please email us or reach us over phone! Training@LearnDataModeling.Com / 91- 9080157239.


Course Duration:

  • 20 hours (18 hours class duration and 2 hours doubts clearing session)

Course Session:

  • 1 hour per session OR 2 hours per session

Mode of Training:

  • Online Training with GoToMeeting or any other online software

Course Material:

  • Recorded Videos will be provided
  • Course Material will be provided

Trainers:

  • Neelesh – Sr. Data Architect – US Employee (12 + years of Data Modeling experience in US)
  • Antony – Data Modeler (4 + years of Data Modeling experience in US)

 


SYLLABUS – RDBMS (OLTP) DATA MODEL


Part 1 – Career Path of a Data Modeler

  • What is a Data Modeling?
  • What is a Data Model?
  • Who is a data modeler?
  • What are the other alternative titles for a Data Modeler?
  • What are the duties and responsibilities of a Data Modeler?
  • What is the difference between duty and responsibility?
  • Who needs Data Modeling?
  • Career Path of a Data Modeler
  • Salary of a Data Modeler

Part 2 – Working with Erwin

  • Working and enhancing Logical/Physical/Conceptual Data Models with Erwin

Part 3 – Data Modeling Development Life Cycle

  • Conceptual Data Model
  • Logical Data Model
  • Physical Data Model
  • Dimensional Data Model
  • Enterprise Data Model

Part 4 – Oracle Database

  • CREATE, ALTER, DROP, MODIFY COMMANDS

Main Objects:

  • Table
  • Column
  • Datatype
  • Record (Row)

Constraints:

  • NULL, NOT NULL
  • Primary Key, Composite Primary Key
  • Foreign Key, Composite Foreign Key
  • Unique
  • Check

Index:

  • Non-Unique Index
  • Unique Index

INSERT statement:

  • To specific columns
  • To all columns

UPDATE statement:

  • Update all values in a column
  • Update specific values

DELETE statement:

  • Delete all records
  • Delete specific records

TRANSACTION statement:

  • Commit
  • Rollback

SELECT statement:

  • With No WHERE CLAUSE
  • With Where CLAUSE
  • With all COLUMNS
  • With few columns
  • With Sub Query
  • With join
  • To create dummy table
  • Group Functions (MIN, MAX, COUNT, SUM, AVERAGE)
  • Having Clause
  • With Insert Clause
  • NULL and NOT NULL
  • Conditional Operators (Less than, Equal to, Greater Than…)

Database statement:

  • What is an instance?
  • What is a database?
  • What is a schema?
  • How to find out the tables created?
  • How to find out the indexes created?
  • How to find out the constraints created?
  • What is cold back up?
  • What is hot back up?
  • What is recovery?
  • SQL Plus Commands
  • Other Objects

Part 5 – Data Modeling Development Life Cycle – Overview

How to create a Conceptual Data Model?

  • What is a Conceptual Data Model?
  • How to create a Logical Data Model?
  • What is a Logical Data Model?
  • Entity, Attribute, Datatype
  • Primary Key, Composite Primary Key, Alternate Key, Foreign Key, Composite Foreign Key, Null, Not Null, Check
  • Entity Definition
  • Attribute Definition
  • Index, Unique Index.
  • What is ModelMart?
  • What is Subject Area?

How to create a Physical Data Model?

  • What is a Physical Data Model
  • Table, Column, Datatype
  • Naming Primary Key, Composite Primary Key, Alternate Key, Foreign Key, Composite Foreign Key, Null, Not Null, Check
  • Naming Index, Unique Index.
  • Difference between a Logical and Physical Data Model

Part 6 – Keys

  • Entity Relationship Diagram
  • Key
  • Super Key
  • Trivial Super Key
  • Candidate Key
  • Primary Key
  • Alternate Key
  • Foreign Key
  • Natural Key
  • Surrogate Key

Part 7 – Cardinality and Optionality

Relationship

  • Identifying, Non-Identifying

Cardinality Notations

  • Exactly One, Zero or One, One or More, Zero, One or More, More than one

Cardinality Relationship

  • One to One, One to Many, Many to Many

Optionality

  • Degree of Relationship
  • Unary, Binary, Ternary
  • Associative Entity
  • Recursive Relationship

Part 8 – Supertype and Subtype

  • Supertype
  • Subtype

Part 9 – Normalization

  • What is Normalization?
  • Normalization – 1NF
  • Normalization – 2NF
  • Normalization – 3NF
  • Normalization Explained with the following OLTP examples:
  • Clinic
  • Student

Part 10 – Data Modeling Standards

  • Table Naming Convention
  • Column Naming Convention
  • Constraints Naming Convention
  • Index Naming Convention
  • Why Naming Convention is very important

Part 11 – Subject Area

  • What is Subject Area?
  • Why do you need Subject Area?

Part 12 – Create Logical/Physical Data Model and implement in a Database

  • Clinic Version2 Data Model
  • Create Conceptual/Logical/Physical Data Model
  • What is Forward Engineering?
    • How to generate scripts from a data model and share it with DBA?
  • What is Reverse Engineering?
    • How to create Data Model from a script?
    • How to create a data model from a database?
  • How to compare data models?
  • How to compare a database and a data model?

Part 13 – Reports

  • What is Metadata?
  • How to generate reports from a Data Model?
  • Why is reports important?

Part 14 – Create Logical/Physical Data Models for a Training Institute

  • How to create Logical/Physical Data Models for a Training Institute from business rules?

Part 15 – OLTP Exercise with Solutions

Normalization

  • Order-Line Item

OLTP Data Model

  • Banking Data Model
  • Mortgage Data Model
  • Staffing Data Model
  • Business Rules for forms and solutions will be also provided.

SYLLABUS – DIMENSIONAL OLAP DATA MODEL


Part 1 – Business Intelligence Intro

  • What is Business Intelligence
  • Examples of Business Objectives
  • Characteristics of Information

Part 2 – OLAP and OLTP

  • Operational Systems – OLTP
  • Examples of Operational Systems
  • Decision Support Systems – OLAP
  • Characteristics of Decision Support Systems(OLAP)
  • Uses of Decision Support Systems(OLAP)
  • OLTP versus OLAP

Part 3 – Introduction to ETL, Data Warehouse and Datamart

  • ETL (Extraction, Transformation and Loading)
  • Data Warehouse
  • Datamart
  • Pioneer of Data Warehousing – Bill Inmon
  • Bill Inmon – Hub and Spoke Architecture
  • Pioneer of Data Warehousing – Ralph Kimball
  • Ralph Kimball – Bus Architecture

Part 4 – BI and Dimensional Data Modeling

  • Business Intelligence
  • OLAP
  • Dimensional Data Modeling
  • Fact Table
  • Dimension Table
  • Dimension Model
  • Dimension Star Schema
  • Dimensional Model Design Process
  • Identify Grain
  • Identify Dimension
  • Identify Measures
  • Identify the Facts
  • Retail POS

Part 5 – Business Case – Retail POS

  • Dimension Design – Date Dimension
  • Dimension Design – Product Dimension
  • Dimension Design – Store Dimension
  • Dimension Design – Promotion Dimension
  • Degenerate Dimension
  • Star Schema
  • Sales Fact Sample Data
  • Star Schema in Action
  • Star Schema Queries
  • Star Schema Extensibility
  • Fact-less Fact Table – No measurement – Only keys

Part 6 – Dimensions

  • Datawarehouse Surrogate Keys
  • Hierarchies in Dimension
  • Conformed Dimensions
  • Slowly Changing Dimensions (Type 0, Type 1, Type 2, Type 3)

Part 7 – Snowflake and Start

  • Snowflake Design
  • Star Schema versus Snowflake Design

Part 8 – Business Matrix

  • Datawarehouse Business Matrix

Part 9 – Facts

  • Semi Additive Facts
  • Non Additive Facts
  • Conformed Facts
  • Fact Table Types
  • Periodic Snap Shot Fact Tables
  • Accumulating Snap Shot Fact Tables

Part 10 – OLAP Exercise with Solutions

  • US Mortgage
  • Retail Banking

Part 11 –  Interview Question and Answers

  • Interview Questions and Answers

Part 12 – Wrap-up

  • Doubts Clearing Session

 


If you are interested in this Data Modeling Training, please reach Training@LearnDataModeling.Com / 91- 9080157239.


 

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*