SDLC/Documentation Techniques

​

SDLC Methodologies

​ 

Meeting Types
o Open
o Closed
o Joint Application Development (JAD)
o Joint Requirements Development (JRD)

​ 

Requirement Gathering Documentation
o Brokers Requirement Document (BRD)
o Software Requirement Specifications (SRS)
o High Level Design Document (HLDD)
o Low Level Design Document (LLDD)

​
 Data Modeling

​
Introduction to Data Bases
o Data Base Management System
o Relational Data Base Management System
o End-to-end Data Base System
o Flat Files, Excel Files and other storage modes

​ 

Introduction to Data Base Design
o Identifying Entities (Shape, Definition, Types)
o Identifying Attributes (Shapes, Definition, Types)
o Identifying Relationships

​
Cardinality
1 to Many (1 to M)
Many to Many (M to M)
Many to 1 (M to 1)
o Integrity

​ 

Introduction to Logical and Physical Design

​
o Purpose of ER Diagrams
o Creating Paper design of ER Diagrams

​ 

 Data Modeling

​ 

​Data Base Normalization
o Purpose, Usage, Advantages, Disadvantages, Application1

​ 

o Various normalization Forms/Types
1NF
2NF
3NF
​ 

Converting Logical Design to Physical Design
o Concepts
o Table Definitions
o Differences

​ 

Designing Data Bases with ERWIN Software Tool
o Introduction to ERWIN
o Explanation of various features in ERWIN
o Forward/Reverse Engineering
o Connections
o Usage

​
 T-SQL

​
MS SQL Server (Transact SQL)
o Introduction and overview of all Data Base Objects
o Constraints
Primary
Foreign
Composite
Check​
o DDL SQL Statements
CREATE
ALTER
DROP
o DML SQL Statements
INSERT
UPDATE
DELETE/TRUNCATE
o Read Statements with Clauses
SELECT (aliases, into)
WHERE
GROUP BY
HAVING
ORDER BY (ascending, descending)
COMPUTE BY (sub/ grand total)

o Operators
IN, BETWEEN, NOT IN, LIKE, (equality =)

​

T-SQL

​

o DCL SQL Statements Overview
GRANT

REVOKE
o Aggregate Functions
MIN, MAX, AVG, SUM, COUNT
o Introduction to Dynamic SQL
Explanation of functionality
Various Statements (DECLARE, EXECUTE, type of variables)
Usage and Application
o System Functions, Explanation of these system functions along with its syntax and its usage in scenario
o System Stored Procedures
Explanation of most of the common system stored procedures along with its syntax
and usage

​
T-SQL

​
o Joins in SQL Server
Purpose of Joins, Usage, Advantages/Disadvantages and differences along with syntax
INNER JOIN
OUTER JOIN (Left Outer Join, Right Outer Join, Full Outer Join)
CROSS JOIN
SELF JOIN
o Sub-queries and Co-related Sub-queries
Explanation with examples and syntax of each of these techniques by usage, definition
and advantages/disadvantages
Explanation of scenarios where either sub-queries or co-related sub-queries should be
used
Advantages and Disadvantages of using Sub-queries over Joins and vice versa
o Set Operators
UNION
UNION ALL
EXCEPT
INTERSECT
Advantages/Disadvantages of each of these Operators

​

T-SQL

​
o Views
Creation and Management of Views
Explanation of Scenarios in which views should be recommended along with its
Common Table Expressions (CTE’s)
Explanation of its need and its role in optimization of SQL Queries along with syntax
Overview of scenarios in which CTE’s must be used
Introduction to Recursive CTE’s
Management of temporary data base objects
Local temporary table
Global temporary table
Advantages and Disadvantages along with applicable scenarios

​
T-SQL

​
Indexes
Types of Indexes (Clustered, Non-Clustered, Unique, Covering, Index with included
columns, Indexed Views) along with syntax
Concept of Non-Clustered Index on Heap and Non-Clustered Index on Clustered Index
along with its functionality
Role of Indexes in SQL Optimization
Execution Plans
 

​
T-SQL

​
Stored Procedures
Creation and Management of Stored Procedures
Input and Output Parameters, Default Stored Procedures
Advantages/Disadvantages and Issues regarding optimizing Stored Procedures
Security Issues(Encryption) and explanation of various scenarios in which Stored
Discussion on optimizing Stored Procedures
Triggers
Creation and Management of Triggers
Types of Triggers (DDL Triggers, DML Triggers and Log-on Triggers) along with syntax
Types of Triggers according to functionality (Before Trigger and After/For Trigger)
Use of INSERTED and DELETED (Magic) Tables in Trigger body
Impact of triggers on Data Bases along with its advantages and disadvantages
Explanation of different scenarios in which triggers can be used

T-SQL
 

User-Defined Functions
Creation and Management of UDF’s
Types of UDF’s (In-line, Multi-line or Table Valued function, Scalar, Deterministic and Non-deterministic functions) along with syntax
Difference between Stored Procedure and User Defined Functions
Advantages and Disadvantages of using UDF’s
Various scenarios in which UDF’s can be a better fit

o Cursors
 

​
Test & Review​

​
Evaluating candidates concepts on Data Modeling and T-SQL
Oral/Mocks - Evaluating candidates communication skills along with balance to
their technical knowledge
Review Session

​ 

SQL Server Data Base Administration (DBA)

Link Server
Data Base Backups
Recovery Models
Partitioning
Table Partitioning (Partition Scheme and Partition Function)
Index Partitioning
Partitioning File Groups

Query and Server Optimization Techniques
Discussion on various Query Optimization Techniques (SQL Profiler, DTA, Joins,
Rebuild and Reorganizing Indexes)
Locks
 

DBA

​
Dynamic Management Views (DMV’s)
Dynamic Management Functions (DMF’s)
​

Data Warehousing​
​

Data Warehouse/Data Marts Implementation
o Dimension Modeling (OLAP)/Data Warehouse Implementation
Discussion on need for Data Warehouse, its structure and various other
scenario aspects
Differences between Online Transaction Processing (OLTP) and Online
Analytical
Processing (OLAP) with few examples
Discussion on Business Process, Business Key, Alternate Key, Dimensions,
different types of Dimensions, Facts, Measures, Measure Group(s), Hierarchies,
Fact-less-fact, granularity level
OLTP to OLAP transformation
o Schemas
Star Schema (Advantages/Disadvantages, need)

Snowflake Schema (Advantages/Disadvantages, need)
Various deciding factors that tends developers to choose either of these schemas
o Scheduling Time constraints Issues

Data Warehousing

o Data Mart Design
Structural differences between Data Warehouse and Data Marts
Functionality of Data Marts over Data Ware House
Discussion on need for Data Marts and their design issues
Bottom Up and Top Down OLAP Design Approach
o Data Ware House/Data Mart Design Methodology
Kimball Methodology
Inmon Methodology

o Advantages/Disadvantages and issues related to these design
methodologies

​ 

SQL Server Integration Services (SSIS)

MS SQL Server Integration Services (SSIS)
o Introduction to SSIS (SQL Server 2005)
o Discussion on various components in SSIS, its use and application in data base design
o Control Flow and Data Flow (Structural/Functionality differences, purpose, various properties)
o Over view of Packages, Containers and Tasks in Control Flow and Transformations in Data Flow
o SSIS Import/Export Wizard
o Executables in SSIS
o Introduction to Data Transformation Services, DTS (SQL Server 2000)
Advantages/Disadvantages of DTS over SSIS
DTS package to SSIS package migration techniques (Package migration wizard,
legacy system, re-writing code from scratch)
o Data Flow Tasks, Adapters and Transformations

​ 

SQL Server Integration Services (SSIS)

o Data Flow Tasks, Adapters and Transformations (continued…)
o Event Handlers
o Loggings, Error Handling mechanisms in SSIS
Discussion on various types of events in Event Handlers Tab
o Package Deployment Methods
SQL Server Deployment/File System Deployment techniques

SQL Server Integration Services (SSIS)

o Package Optimization Techniques
o Data Sources and Data Source Views (Purpose and its role in SSIS)
o Expressions and Functions
o Use of Containers
o Creating Master-Child Packages (Advantages/Disadvantages, its role in designing SSIS packages)
o Implementing Data Base Maintenance Tasks

​

SQL Server Integration Services (SSIS) with ETL Strategy​
 

Extract, Transform and Load (ETL) Strategy
Need for ETL Strategy
Detailed discussion on the steps to be followed while designing an ETL Strategy
Discussion on various heterogeneous sources that might be a part of ETL Strategy (Flat Files, Excel Files, Oracle DB, DB2, Sybase etc)
Concept, Use and Purpose of Pre-Staging and Staging Databases in populating Data Warehouse and Data Marts
Concept of Data Mapping Documentation, Data Profiling, Data Verification, Data Crunching
Purpose, need and usage of Data Dictionary in DB design Way of designing packages for a particular ETL strategy depending upon organizations need
Populating DW (Initial Load and Incremental Load)
Discussion on various techniques to grab incremental data from OLTP
Triggers
Left-Outer Joins
Time Stamps
Check Sum using XML Sources and writing output to XML Files

SQL Server Integration Services (SSIS) with ETL Strategy

Different ways of executing SSIS Packages
Debugging option, SQL Server Agent, dtexecui, dtexec /f, Import/Export wizard
Differences and most optimized way of executing a package considering above mentioned execution techniques
Essence of Populating AdventureWorksDW/Data Mart using ETL packages
Overview and general comparison between SSIS technology with Cognos, Microstrategy and Pentaho ETL tools.

Test & Review

​ 

Using Script Component and VB.Net Scripts to perform various ETL possibilities

​Evaluating candidates concepts on T-SQL, SSIS and DBA
Oral/Mocks - Evaluating candidates communication skills along with balance to their technical knowledge Review Session

​
SQL Server Analysis Services (SSAS)​
 

MS SQL Server Analysis Services (SSAS)
Introduction to SQL Server Analysis Services
Purpose, Usages and Advantages of using SSAS tool
An abstract overview of where SSAS fits in entire database design
Discussion on various features of analysis services along with its capabilities
(advantages and disadvantages)
o Data Source and Data Source View (Creating first cube)
o Relationships/ Hierarchies
o Calculations
o Multi-Dimensional Expressions (MDX) Intro

​ 

SQL Server Analysis Services (SSAS)​

Cube Optimization Techniques
Key Performance Indicators (KPI’s)
Calculations/Roles
Aggregations
Set Count Aggregations
Usage Based Aggregations
Cube Partitioning (Table bound, Query bound)
Cube Storage Mode (MOLAP, HOLAP, ROLAP)
Updating and Refreshing Cube Techniques
Pro-active caching
SSAS Cube Processing Task in SSIS
Cube Processing Options
Full Process
Incremental Update
Refresh Data
 

SQL Server Analysis Services (SSAS)​
 

Dimension Usage
Actions
Perspective (Usage, Purpose, Advantages/Disadvantages)
Conditional Formatting (Introduction and Need)
Translations
 

SQL Server Reporting Services (SSRS)​
 

MS SQL Server Reporting Services (SSRS)
Introduction to Microsoft Reporting tool SSRS
Purpose, Need and application of SSRS in business world
Basic framework of SSRS (Reporting Life Cycle)
Types of reports
 

​ 

SQL Server Reporting Services (SSRS)​
 

Creating Reports with Parameters from Data Sources using BI Studio
Creating various types of reports (Basic differences between these reports)
Click through reports
Drill down reports
Multi-valued reports
Cascaded reports
Security in SSRS

​ 

SQL Server Reporting Services (SSRS)​
 

Creating Linked Reports
Developing reports using MDX
Deploying and scheduling reports
Managing subscriptions of reports
End-to-end Reporting Server Administration

​
 

Final Project and Complete Review Session

​