Data Modeling with SQL Server Training

sql server copy

Data Modeling:

Need of Data Modeling

  • What is data modeling and its significance?
  • What is Data Warehousing

Data Modeling

  • Conceptual Data modeling
  • Physical Data modeling
  • Logical Data modeling
  • Benefits and scope of data modeling

Multidimensional Data Modeling

  • Basic Conceptual Architecture
  • Demonstrate fact and dimension
  • Difference between facts and dimensions

Concepts/Schema of Data Modeling

  • Dimensional Model Keys
  • Types of facts
  • Conformed dimensions
  • Star schema
  • Snow flake schema
  • Difference between Star schema and Snow flake Schema
  • Limitations of data warehousing

SCD Implementation methods

  • Type 0 Fixed attributes
  • Type 1 Changing attributes
  • Type 2 Historical attributes

Introduction to RDBMS

  • Evolution of RDBMS
  • 12 Rules of Codd
  • RDMS providers
  • Parts of database
  • Relational concepts – 1
  • Relational concepts – 2
  • Relational concepts – 3
  • Relational concepts – 4

RDBMS Vs Data warehouse and ETL Process

  • OLTP vs. OLAP
  • What is ETL Process
  • Where ETL Process is used
  • Scope of ETL in Data warehouse

SQL Parsing

  • SQL Parsing-1
  • SQL Parsing-2
  • Compilation
  • Optimization-1
  • Optimization-2
  • Execution

Erwin-Design Layer Architecture

  • What is ERwin Model?
  • Design Layer Architecture
  • Data Warehouse modeling
  • Creating and using ‘User Defined Domains’
  • Managing naming standards
  • Managing Data type standards

Forward Engineering & Reverse Engineering

  • An introduction to Forward Engineering (SQL Generation)
  • An introduction to Reverse Engineering
  • An introduction to Complete Compare

SQL Server Training Content:

SQL Server Introduction & Installation

  • SQL Server 2012 & 2014 Installation
  • SQL Server Components and Features
  • Naming Conventions & Collation Settings

SQL Server Database Design

  • SQL Server Database Architecture
  • DB Design using Files and File Groups
  • DB File locations and Size Parameters
  • UI Limitations, ALTER Command Options

SQL Server Table Design

  • Table creation using T-SQL Scripts
  • SQL Server Tables &Filegroup Routing
  • INSERT, UPDATE, DELETE Operations
  • Single Row and Multi-Row Table Inserts
  • Table Aliases, Column Aliases, Usage
  • Table creation with Schemas, Filegroups
  • DELETE Versus TRUNCATE, Usage
  • SELECT Queries, Variants & Sub Queries
  • Schemas and Nested Queries, Aliases

Normal Forms, Keys & Constraints

  • Tables with Keys, Constraints (BCNF)
  • NULL and IDENTITY Properties – Usage
  • UNIQUE KEY Constraint and NOT NULL
  • PRIMARY KEY Constraint & Importance
  • FOREIGN KEY and REFERENCE Attributes
  • CHECK and DEFAULT Constraints, Usage
  • Duplicating Identity Property Values
  • Disabling Constraints & Composite Keys

Views, Joins and Sub Queries

  • SELECT Queries – IIF, SWITCH, CHOOSE
  • GROUP BY, GROUPING, HAVING, OVER
  • Sub Queries, Nested Queries and EXISTS
  • DELETE FROM SELECT, Query Variants
  • UPDATE FROM SELECT, Query Variants
  • INSERT INTO SELECT & Data Copy
  • Inner Joins – Purpose and Performance
  • Outer Joins – Advantages, Usage
  • Cross Joins – Advantages, Limitations
  • Self Joins, Merge Joins, Sub Queries
  • Working with Table Variables, Types
  • WHILE Loops and Iteration Controls
  • TARGET & NOT MATCH with MERGE
  • NOCHECK and SET Options in Joins
  • Sub Queries, Nested Queries and Usage
  • Using Sub Queries for Data INSERTS
  • Query Joins with UPDATE/DELETE

Indexes and Query Tuning

  • Indexes Architecture, Types and Purpose
  • Clustered Indexes – Architecture, Usage
  • Non Clustered Indexes & Page Usage
  • Indexes on Table Columns With Options
  • Index Scan, Index Seek and Key Lookup
  • SORT_IN_TEMPDB &FillFactor Options
  • INCLUDED Indexes & Query Optimizer
  • Tuning Materialized (Indexed) Views
  • Indexes for Joins & UNIQUE Constraints
  • Using Indexes in Views and Query Plans