• Call Now
    040 6666 6158
  • What's App
    +91 9642115000
  • Email
    info@rgsitsolutions.com

Database Architect Training – Combo Course

Database Architect Training – Combo Course

About Course

Master the skills of multiple Database Systems, Data Modeling and Data Warehousing all at once and become a Database Expert. Start your learning today.

List of courses in this combo pack:

  • Oracle PL-SQL
  • Oracle DBA
  • Data Warehousing, Data Modeling, Erwin
  • SQL Developer
  • SQL DBA

Key Features:

  • A comprehensive, in-depth combo of Oracle PL-SQL, Oracle DBA, Data Warehousing, Data Modeling, SQL Developer and SQL DBA.
  • 68 hours of Intensive High-Quality in-depth Video E-Learning Sessions
  • 136 Hours of various Lab Exercises and Project Work
  • 70% of extensive learning through Hands-on exercises, Project Work, Assignments and Quizzes.
  • The training will be followed by a Quiz and Project Assignments. Once you complete them you will be provided with Intellipaat Course Completion Certification.
  • 24*7 Lifetime Support with guaranteed assistance for resolution of every problem and technical query.
  • Lifetime Access to Videos, Tutorials and Course Material
  • Guidance to Resume Preparation and Job Assistance

About Database Architect Training Course:

The specially designed elaborative course is excellent for all Database Professionals or even for freshers who want to enter into Database field. This course provides in depth knowledge on Database Systems, Concepts of Data Modeling and Data Warehousing.

Gaining expertise in 5 different database related technologies will give you an opportunity to land in your dream job, top notch company with huge earnings. Intellipaat’sDatabase Architect Training – Combo Course is designed in a way that meets all the demands of industries, top notch companies and makes you an ideal candidate for them.

This training course is a have-it-all package to provide in-depth knowledge in Database field and thus provide a scope of being identified by the top multinationals worldwide.

Project Work:

1. Oracle PL-SQL Project – DML

Problem Statement – It includes the following actions:

  • Triggers – Definition, objective and its event type
  • Application & database triggers
  • Business application scenarios for implementing triggers
  • Define DML triggers
  • Define Non – DML triggers
  • Triggers event type & body
  • Creating DML triggers using the create triggers statement
  • Define statement level triggers v/s low level triggers
  • Triggers firing sequence: single row manipulation
  • Creating a DML statement triggers
  • Using old and new qualifiers
  • Old and new qualifiers
  • Instead of triggers
  • Managing triggers using the alter & drop SQL statement
  • Testing triggers

2. Oracle DBA Project – Database Tuning

Problem Statement – It includes the following actions:

  • Introduction to Database Tuning
  • Monitor space usage
  • Monitor SQL scripts
  • Data base tuning
  • SQL tuning
  • Table Statistics
  • Index statistics
  • Index Selectivity
  • Chained Rows
  • Locks

3. Data Warehousing, Data Modeling, Erwin Projects

1. Project – Logical & Physical Data Modelling Using ERWin (Invoice Mgmt)

Data – Sales

Problem Statement – It creates a logical and physical data model using Erwin.

2. Project – End to End implementation of Data Warehouse (Retail Store)

Data – Sales

Problem Statement – It describes that how to create and deploy a data warehouse. It also describes that how to load the data into it using ETL.

4. SQL Developer Project – Subqueries

Problem Statement – It defines about the SQL subqueries and how they are used in different conditions. It includes following sub queries:

  • Sub queries used with IN or NOT IN
  • Sub queries used ANY or ALL
  • Sub queries used with EXISTS and NOT EXISTS

5. SQL Server DBA Project – SQL Server Audit

Problem Statement – It describes about the Audit and how to implement the SQL Server Audit. For this, it includes following actions:

  • Creation of server audit
  • Creation of server audit in the master database
  • Creation of the database specification in TestDB database
  • Trigger the audit event by selecting form tables
  • Check the audit for the filtered content
  • Alter existing audit
  • Drop server audit

Curriculum

Oracle PL-SQL

Module – Oracle 11GR2 Installation

Module 1 – Basic PL/SQL Programming

  • Overview of PL/SQL
  • PL/SQL Environment
  • Benefits of PL/SQL
  • Variable uses, types and declaration

Module 2 – Syntax and Section Query

  • PL/SQL Block syntax & guideline
  • SQL functions in PL/SQL
  • Data type conversion
  • Nested blocks and variable scope
  • Retrieving and updating data in PL/SQL
  • Naming conventions

Module 3 – Cursor/Conditional Statement

  • SQL Cursor
  • SQL Cursor Attributes.
  • Controlling PL/SQL flow of executions
  • IF Statement
  • Simple IF Statement
  • IF-THAN-ELSE Statement Execution Flow
  • IF-THAN-ELSE Statement
  • IF-THAN-ELSIF Statement
  • Logic Tables
  • Boolean Conditions.
  • Iterative controlling loop statement
  • Nested Loops and Labels.

Module 4 – Advance Cursor

  • Writing Explicit cursors
  • About cursors
  • Explicit cursor functions
  • Controlling explicit cursor
  • Opening the cursor
  • Fetching Data cursor
  • Cursor and records
  • Cursor for loop using sub queries

Module 5 – Exceptions

  • Advance Explicit cursor
  • Cursor with parameters
  • For update Clause
  • Where current of Clause
  • Cursor with sub queries
  • Handling Exception
  • Handling Exception with PL/SQL
  • Predefined Exception

Module 6 – Advance Exceptions

  • Predefined Exceptions
  • User Defined Exceptions
  • Non-Predefined Error
  • Function for trapping Exception
  • Trapping user-defined Exception
  • Raise Application Error Procedure

Module 7 – Subprogram-Procedure/Function

  • Overview of subprograms
  • PL/SQL Subprograms
  • What is Procedure
  • Syntax for creating Procedure
  • Creating Procedure with parameter
  • Example of Passing parameters
  • Declaring Subprogram

Module 8-Procedures/Function

  • Procedures – Developing procedures, removing procedures
  • Benefits of subprograms
  • Procedural Parameter Modes
  • Actual vs. Formal Parameters
  • Methods for passing parameters
  • Function – Creation, overview, syntax
  • Restrictions on calling from SQL
  • Advantages of user defined functions in SQL expressions
  • Comparing procedures and functions
  • Benefits of stored procedures and functions
  • Detecting compilation errors

Module 9 – Package

  • PL/SQL records
  • Using Pl/SQL Table method and example
  • Creating PL/SQL Table
  • Packages – Objective, overview, component, developing, removing, advantages
  • Creating the package specification/example
  • Declaring Public construct
  • Public and private construct
  • Invoking package construct
  • Referencing a public variable from a standalone procedure
  • Guide lines for deploying packages

Module 10 – Advance Package

  • Overloading
  • Using forward declaration
  • One time only procedure
  • Package functions
  • User define package function
  • Persistent state of package function
  • Persistent state of package variable
  • Controlling the persistent state of package cursor
  • Purity end
  • Using supplied package
  • Using native dynamic SQL
  • Execution flow
  • Using DBMS-SQL package
  • Using DBMS-DDL package
  • Submitting jobs
  • Interacting with operating system links

Module11 – Project – DML

  • Triggers – Definition, objective and its event type
  • Application & database triggers
  • Business application scenarios for implementing triggers
  • Define DML triggers
  • Define Non – DML triggers
  • Triggers event type & body
  • Creating DML triggers using the create triggers statement
  • Define statement level triggers v/s low level triggers
  • Triggers firing sequence: single row manipulation
  • Creating a DML statement triggers
  • Using old and new qualifiers
  • Old and new qualifiers
  • Instead of triggers
  • Managing triggers using the alter & drop SQL statement
  • Testing triggers

Module 12 – Advance Level -DML

  • Testing triggers
  • Viewing trigger information
  • Describe user triggers
  • What is a compound trigger and working with it
  • Compound trigger structure for tables
  • Timing-point sections of a table compound trigger
  • Compound trigger structure for views
  • Trigger restrictions on mutating tables
  • Compound trigger restrictions
  • Using a compound trigger to resolve the mutating table error
  • Creating triggers on system events
  • LOGON and LOGOFF triggers example
  • Call statements in triggers
  • Benefits of database-event triggers
  • System privileges required to manage triggers

Module13 – Advance Level -DML

  • Dynamic SQL-objectives
  • Describe execution flow of SQL statements
  • Dynamic SQL with a DDL statement-example
  • Working with dynamic SQL
  • Native Dynamic SQL(NDS)
  • Using the executive immediate statement-example
  • Using native dynamic SQL to compile PL/SQL code
  • Using DBMS-SQL with a DML statement
  • Using the DBMS-SQL package subprograms
  • Parameterized DML statement

Module 14 – Advance level- Scripting

  • Managing Dependencies
  • Objectives, overview of schema object dependencies
  • Direct local dependencies
  • Querying direct object dependencies
  • Displaying direct and indirect dependencies
  • Fine-Grained dependency management
  • Changes to synonym dependencies
  • Maintaining valid PL/SQL program units and views
  • Object revalidation
  • Concepts of remote dependencies
  • Setting the remote dependencies mode parameter
  • Recompiling PL/SQL program unit
  • Packages and Dependencies
  • Successful and unsuccessful recompilation
  • Recompiling procedures

Oracle DBA

Module – Oracle 11GR2 Installation

Module 1 – Part 1 – Introduction to Database

  • Introduction to Database
  • How ORACLE DB does it
  • Unix kernal

Module 1 – Part 2 – Practical Session

Module 2 – Part 1 – Physical Database Structure

  • Physical Database Structure
  • Control files
  • Key information of files
  • Redo log files

Module 2 – Part 2 – Practical Session

Module 3 – Part 1 – Oracle Storage structures

  • Oracle Storage structures
  • Table statement
  • How to check “create table”
  • Schemas and schema objects
  • Data blocks
  • Extents
  • Segments

Module 3 – Part 2 – Practical Session

Module 4 – Part 1 – Memory & Process Architecture

  • Memory & Process Architecture
  • Instance/Memory structures
  • Shared pool
  • Buffer Cache
  • Redo Log Buffer
  • Process Architecture
  • Background process

Module 4 – Part 2 – Practical Session

Module 5 – Part 1 – Background Process, Alert & Trace files

  • Background Process, Alert & Trace files
  • Alert
  • Trace files

Module 5 – Part 2 – Practical Session

Module 6 – Part 1 – Database Startup & Serving User Requests

  • Database Startup & Serving User Requests
  • Data Base Startup
  • User process, Server process

Module 6 – Part 2 – Practical Session

Module 7 – Part 1 – Database Security

  • Database Security
  • Process of “Create User”
  • Alter & Drop User
  • Resource Limits & profiles
  • Auditing

Module 7 – Part 2 – Practical Session

Module 8 – Part 1 – Schema Objects

  • Schema Objects
  • Types of schema objects
  • How table data is stored
  • Temporary Tables
  • External Tables

Module 8 – Part 2 – Practical Session

Module 9 – Part 1 – Schema Object Continued

  • Schema Object Continued
  • Materialized View
  • Sequence Generator
  • Indexes
  • B-Tree index structure
  • Cluster/Hash Cluster
  • Data concurrency & consistency
  • Locking
  • Deadlocks

Module 9 – Part 2 – Practical Session

Module 10 – Part 1 – Oracle Network Environment

  • Oracle Network Environment
  • How to connect your database
  • Network environment of ORACLE
  • Database link

Module 10 – Part 2 – Practical Session

Module 11 – Part 1 – Oracle Backup & Recovery Concepts

  • Oracle Backup & Recovery Concepts
  • Standby database
  • Testing
  • Media recovery options
  • Offline backup
  • Standby Database

Module 11 – Part 2 – Practical Session

Module 12 – Oracle Recovery Manager (RMAN)

  • Oracle Recovery Manager (RMAN)
  • Data pump export & import
  • SQL loader
  • External table

Module 13 – Data Dictionary & Dynamic Performance Tables

  • Data dictionary & Dynamic Performance Tables
  • Dynamic performance Tables
  • Typical day ORACLE DBA

Module 13 – Part 2 – Practical Session

Module 14 – Introduction to Database Tuning

  • Introduction to Database Tuning
  • Monitor space usage
  • Monitor SQL scripts
  • Data base tuning
  • SQL tuning
  • Table Statistics
  • Index statistics
  • Index Selectivity
  • Chained Rows
  • Locks

Module 14 – Part 2 – Practical Session

Module 15 – Introduction to Database Tuning Continued

  • Introduction to Database Tuning Continued
  • Tuning Shared pool
  • Data dictionary performance
  • Data dictionary tuning
  • PL/SQL code
  • Code reuse
  • Data base Buffer
  • Buffer cache hit Ratio
  • Code reuse

Project –

Database Tuning

Problem Statement –

It includes the following actions:
  • Introduction to Database Tuning
  • Monitor space usage
  • Monitor SQL scripts
  • Data base tuning
  • SQL tuning
  • Table Statistics
  • Index statistics
  • Index Selectivity
  • Chained Rows
  • Locks

Data Warehousing, Data Modeling, Erwin

Module 1 – Need of Data Modeling

  • What is the need of data modeling?

Module 2 – Data Modeling

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

Module 3 – Multidimensional Data Modeling

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

Module 4 – 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

Module 5 – SCD Implementation methods

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

Module 6 – 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

Module 7 – 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

Module 8 – SQL Parsing

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

Module 9 – Cube

Session 1 – Types and Scope of Cube

  • What is Cube?
  • Types and Scope of Cube
  • Data warehouse vs. Cube

Session 2 – OLAP Operations

  • OLAP Operations
  • Benefits of Cube
  • Limitations of Cube and Evolution of In Memory Analytics

Session3 – Limitations of Cube

  • Limitations of Cube and Evolution of In Memory Analytics

Session4 – Creating Cube and Reports

  • How to Create a Cube?
  • How to Deploy a Cube?
  • Processing of a Cube

Session5 – Creating Data Mining Model

  • How to create a data Mining Model?
  • IN-Memory Analytics Architecture and Advantages

Module 10 – Erwin-Design Layer Architecture

  • Design Layer Architecture
  • Data Warehouse modeling
  • Creating and using ‘User Defined Domains’
  • Managing naming standards
  • Managing Data type standards

Module 11 – Forward Engineering & Reverse Engineering

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

Project Work

1. Project –

Logical & Physical Data Modelling Using ERWin (Invoice Mgmt)

Data –

Sales

Problem Statement –

It creates a logical and physical data model using Erwin.

2. Project –

End to End implementation of Data Warehouse (Retail Store)

Data –

Sales

Problem Statement –

It describes that how to create and deploy a data warehouse. It also describes that how to load the data into it using ETL.

SQL Developer

Module-1 SQL SERVER OVERVIEW, USING MANAGEMENT STUDIO

  • Types of Database
  • Client server Architecture
  • File server v/s client server DB’s
  • Client communication manager
  • Relational query processor
  • What SQL exactly is?
  • How to open SSMS
  • Introduction of SQL 2012
  • Windows authentication
  • Skima
  • T-sql
  • Keywords
  • Identification
  • System table
  • Data types
  • Functions
  • Data Manipulation
  • Data control language
  • Drop table
  • The commit/Rollback

Module-2 DESIGNING RELATIONAL DATABASES

  • Coolum
  • Select statement clauses
  • Using group by and having clauses
  • Logical and relational operators
  • Joins
  • Relational & logical opera
  • group function
  • Joins Constraints:-
  • Primary key
  • Foreign key
  • Unique key
  • check
  • Not null

Module-3 WORKING WITH SINGLE TABLE QUERIES, MERGING DATA WITH JOINS & UNIONS

  • Joins
  • Types of Joins
  • Complete explanation by using figures and query
  • Inner Join
  • Outer join
  • Self join
  • Creation of tables
  • Cross join
  • Set operators
  • Types of Set operators
  • Union
  • Union all
  • Intersect
  • Except
  • Rules of Set operators
  • Temporary Table creation
  • Facts about temporary tables
  • Table variables
  • Example of Function creation
  • Table variables limitations

Module-4 Functions

  • System function
  • Single row function
  • Number function
  • Char/String function
  • Date function
  • General function
  • Inline function
  • Duplicate function

Module-5 WORKING WITH SUBQUERIES

  • Sub queries
  • Sub queries used with IN or NOT IN
  • Sub queries used ANY or ALL
  • Sub queries used with EXISTS and NOT EXISTS
  • The update statement
  • Using the set clause to modify
  • Using the where clause
  • using the from clause
  • The DELETE statement
  • The TRUNCATE table statement
  • How to create view
  • Type of view

Module-6 SELECTING DATA THROUGH VIEWS, T-SQL & STORED PROCEDURES

  • Views
  • Usage
  • Create view
  • Alter view
  • Drop view
  • Rename view
  • Modifying data using view
  • TSQL
  • Stored procedure
  • Advantage of stored procedure
  • How to create stored procedure
  • Drop procedure
  • Error handling in stored procedure
  • User defined function (UDF)
  • Scalar UDFs
  • Inline table value UDFs
  • Multi-statement table UDFs
  • Limitation of UDFs

Module-7 USER-DEFINED FUNCTIONS, TRIGGERS & AUDIT TRAILS

  • Stored procedure:-
  • Try catch block
  • User stored procedure (UDFs)
  • Inline table value UDFs
  • Limitation of UDFs
  • User stored procedure (USP)
  • Rank function:-
  • Rank function:-
  • DENSE Rank
  • Row Number
  • Triggers
  • How to create Triggers

Module-8 INTRODUCTION TO OPTIMIZATION THEORY, TRANSACTIONS, LOCKING & BLOCKING, INDEXING FOR PERFORMANCE

  • Pivot
  • SQL server management studio
  • Pivot in excel
  • Pivot in SQL server
  • Stuff
  • For XML path
  • Example for XML path
  • Difference between CHAR, Varchar and NVarchar
  • How to change values
  • Indexes
  • With no Indexes defined
  • How to create an Index
  • How Indexes works
  • Index advantages
  • Searching for records
  • Sorting records
  • Grouping records
  • Index drawbacks
  • Indexes and Data modification
  • Clustered Indexes
  • How to create a Clustered Index
  • Composite Indexes
  • Covering queries with an index
  • Additional Index guidelines
  • Common Table Expression
  • Example of CTE

Project –

Subqueries

Problem Statement –

It defines about the SQL subqueries and how they are used in different conditions. It includes following sub queries:
  • Sub queries used with IN or NOT IN
  • Sub queries used ANY or ALL
  • Sub queries used with EXISTS and NOT EXISTS

SQL DBA

Module 1: Starting with SQL Server

  • Responsibilities of Database Administrator
  • Types of DBAs
  • History of SQL Server
  • Editions of SQL Server
  • Tools of SQL Server
  • Differences between Standard and Enterprise editions
  • Instances types in SQL Server
  • Default Instance
  • Named Instance
  • SQL Server Services
  • Instance aware services
  • Instance unaware services

Module 2: Installing SQL Server

  • Pre-requisites
  • Installation
  • Post Installation verification Session

Module 3: Functioning of Databases

  • Database
  • Types of Database and Brief explanation
  • System Databases
  • User Database
  • Database Architecture
  • Pages
  • Extents
  • File groups
  • Database
  • Transaction Architecture
  • Creating Database
  • Modifying Database
  • Adding Files
  • Moving and renaming of Database files
  • Database modes
  • Real time Scenario

Module 4: Security

  • Authentication Types in SQL Server
  • Types of Login
  • Windows Login
  • SQL Login
  • Creating Users and Logins
  • Server roles
  • Password policy
  • Understanding Database and Server Roles
  • Permissions
  • Working on access level issues
  • Orphan users Finding and Fixing
  • Important Queries

Module 5: Database Backups and Restoration

  • Database Backups
  • Why we need backups
  • Types of Backup
  • Full Backup
  • Differential Backup
  • Transaction Log Backup
  • Copy-only, Mirrored, Split and Tail log Backups
  • Differences between backups
  • Backup Strategy
  • Understanding how the data moving from Log to Data File,CHECKPOINT
  • Monitoring the space usage of Log File and fixing
  • Checking the backup files VALID or CORRUPTED
  • Backup storage tables
  • Important Queries
  • Performing Restorations
  • Types of Restoration
  • Attach and Detach
  • Shrinking files

Module 6: Recovery Models of Database

  • Types of Recovery Models
  • Full
  • Bulk Logged
  • Simple
  • Differences between Recovery Models
  • Setting Recovery Models according to the Scenarios with examples

Module 7: Automation of SQL SERVER

  • Working with SQL SERVER Agent
  • Creating Jobs
  • Managing Jobs and Resolving issues
  • Monitoring Jobs
  • Monitoring Tables
  • Log Shipping
  • Mirroring
  • Replication
  • Clustering

Project –

SQL Server Audit

Problem Statement –

It describes about the Audit and how to implement the SQL Server Audit. For this, it includes following actions:
  • Creation of server audit
  • Creation of server audit in the master database
  • Creation of the database specification in TestDB database
  • Trigger the audit event by selecting form tables
  • Check the audit for the filtered content
  • Alter existing audit
  • Drop server audit
 
Sample Description

Certification

At the end of the course there will be a quiz and project assignments once you complete them you will be awarded with Intellipaat Course Completion certificate.

Enquiry Form


 

 

 

 

classroom_training
corporate-training
realtime_projects