Intellipaat
Intellipaat

Database Architect Training: Combo Course

Intellipaat’s Database Architect Certificate master’s training program lets you gain proficiency in the Oracle Database. We provide the best online training classes to help you learn Oracle PL/SQL scripting, data warehousing, data modeling, ERwin, SQL Developer and SQL DBA. You will work on real-world projects in this program.

This course is accredited by Microsoft. Get DWH & Erwin or MsExcel selfpaced course free. Enroll Now

Key Features

  • Instructor Led Training : 68 Hrs
  • Self-paced Videos : 68 Hrs
  • Exercises & Project Work : 136 Hrs
  • Certification and Job Assistance
  • Flexible Schedule
  • Lifetime free upgrade
  • 24 x 7 Lifetime Support & Access

About Database Architect Training Course

This is a combo training course that provides you with all necessary skills to work with database systems, data warehousing and data modeling. You will also gain considerable mastery in database administration, working with SQL and so on.

List of Courses Included in This Combo

  • Oracle PL/SQL
  • Oracle DBA
  • Data Warehousing, Data Modeling, and ERwin
  • SQL Developer
  • SQL DBA

What will you learn in this training course?

  1. Core database concepts and the role of a Database Administrator
  2. SQL, PL/SQL, client/server relation and MS SQL architecture
  3. Writing SQL to query database and modifying using T-SQL
  4. ETL life cycle, data modeling and data warehousing
  5. ERwin Design Layer architecture
  6. Working with Oracle and designing, building and managing database applications
  7. Database performance, integrity and security management
  8. PL/SQL sections like declaration, execution and exception handling
  9. SQL command major groups: DDL, DML, DCL and TCL

Who should take up this training course?

  • SQL Developers, Database Administrators, Software Developers and BI Professionals
  • Project Managers, Business Analysts, Testing, Data Warehousing and Hadoop Professionals

What are the prerequisites for taking up this training course?

There are no prerequisites for taking up this training course.

Why should you take up this training course?

This Intellipaat combo training course has been developed keeping in mind the requirements of database professionals as well as beginners. Since this is a 5-in-1 elaborate combo course, upon the completion of the course, you can look for jobs in any of the five domains. This will help you land high-paying jobs in major multinationals.

view more
Read Less

Database Architect Course Content

Oracle PL SQL Course Content

Introduction to Oracle SQL

What is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12c

Hands-on Exercise – Install Oracle 12c

Using DDL Statements to Create and Manage Tables

Categorize the main database objects, Review the table structure, List the data types that are available for columns, Create a simple table, Explain how constraints are created at the time of table creation, Describe how schema objects work

Hands-on Exercise – Create a database table Person with two column (Name, Age) with constraint on age not greater than 100, Insert records using insert query

Retrieving Data Using the SQL SELECT Statement

List the capabilities of SQL SELECT statements, Execute a basic SELECT statement

Hands-on Exercise – Use a basic select statement to retrieve all the records in the Person table

Restricting and Sorting Data

Limit the rows that are retrieved by a query, Sort the rows that are retrieved by a query, Ampersand substitution to restrict and sort output at runtime

Hands-on Exercise – Write a select query to retrieve records where age is more than 60 yrs, Write a select query to sort the records by name, Write a select query to sort the records by age in descending order, Use ampersand substitution to restrict and sort output at runtime

General functions

The general functions in SQL, working with any data type and handling Null values, using COALESCE() and Null function, Constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE()

Hands-on Exercise – Use NULL function to deal with null values in data

Using Single-Row Functions to Customize Output

Describe various types of functions (character, number, date, string etc.) available in SQL

Hands-on Exercise – Create a table with columns of type char, number and date, Use character, number, and date functions in SELECT statements

Large Object Functions

Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING Clause

Hands-on Exercise – Count records based on a condition, Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg

OLAP Functions

The various OLAP functions, cube, model clause, roll up and grouping functions

Hands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.

Using Conversion Functions and Conditional Expressions

Describe various types of conversion functions that are available in SQL, Conditional expressions in a SELECT statement

Hands-on Exercise – Group data by using the GROUP BY clause, Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

Joins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoins

Hands-on Exercise – Write SELECT statements to access data from more than one table using equijoins and non equijoins, Join a table to itself by using a self-join, View data that generally does not meet a join condition by using outer joins, Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries

Define subqueries, Describe the types of problems that the subqueries can solve, List the types of subqueries

Hands-on Exercise – Write single-row and multiple-row subqueries

Using the Set Operators

Describe set operators, UNION [ALL], INTERSECT, MINUS Operators

Hands-on Exercise – Use a set operator to combine multiple queries into a single query Control the order of rows returned

Manipulating Data using SQL

Describe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactions

Hands-on Exercise – Insert rows into a table, Update rows in a table, Delete rows from a table

Database Transactions

What is a database transaction, Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID), Avoiding error/fault in manipulating database records using transaction

Hands-on Exercise – Begin a transaction, Execute queries to update or insert or delete records, If no error, commit the transaction Else roll back the transaction and end it

Creating Other Schema Objects

Views – simple and complex, Sequences, Index, Synonym

Hands-on Exercise – Create simple and complex views, Retrieve data from views, Create, maintain, and use sequences, Create and maintain indexes, Create private and public synonyms

Writing Cursor and 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.

Hands-on Exercise – Use Boolean condition on a select query, Use logic table, Create nested loops and labels

Introduction to Explicit 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

Hands-on Exercise – Write an explicit cursor, Use cursor function, Fetch data cursor

Advance concepts of explicit Cursor

Advance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queries

Hands-on Exercise – Use cursor with subquery, Use Where Current clause to retrieve data

Exception Handling

Handling Exception, Handling Exception with PL/SQLPredefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error Procedure

Hands-on Exercise – Use Predefined exception, Write user defined exception, Generate and handle exception, Use a function for trapping an exception

Writing Subprogram, Procedure and passing parameters

Overview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Referencing a public variable from a standalone procedure, Declaring Subprogram

Hands-on Exercise – Create a parameterized procedure, Pass parameters in a procedure call, Access a public variable from a standalone procedure

Creating PL/SQL 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, Guide lines for deploying packages

Hands-on Exercise – Create a package, Deploy the created package

Advance Package Concepts and functions

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

Hands-on Exercise – Use supplied package, Use native dynamic SQL, Use DBMS-SQL package, Use DBMS-DDL package, Submit a job

Introduction and writing Triggers

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

Hands-on Exercise – Create a DML statement trigger, Use old and new qualifiers, Manage a trigger using the alter & drop SQL statement, Test the created triggers

Compound 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

Hands-on Exercise – View a trigger’s information, Use a compound trigger structure for views, Use a compound trigger to resolve the mutating table error, Create triggers on system events, Use LOGON and LOGOFF triggers, Call statements in triggers

Working with Dynamic SQL

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

Hands-on Exercise – Use the executive immediate statement, Use native dynamic SQL to compile PL/SQL code, Create DBMS-SQL with a DML statement, Create a DBMS-SQL package subprograms

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 re-validation, Concepts of remote dependencies, Setting the remote dependencies mode parameter, Recompiling PL/SQL program unit, Packages and Dependencies, Successful and unsuccessful recompilation, Recompiling procedures

Hands-on Exercise – Query direct object dependencies, Display direct and indirect dependencies, Set the remote dependencies mode parameter, Recompile PL/SQL program unit, Edit a procedure and recompile it

Oracle 12c DBA Course Content

Database Introduction

What is the Oracle database? How it functions? pre-requisites, oracle database, multiple non-CDB’s share nothing, oracle database 12c installation, multi-tenant architecture, non-CDB oracle system data mixed with user data, multitenant container database, pluggable database.

Oracle Database 12c Architecture

The Architecture of Oracle server, the key components – Oracle Instance and Oracle Database, the various file types – data files, control files, redo log files, benefits of CDB, easier management of database, support for oracle database resource manager, pluggable database (PDB), database instance, contents of CDB and pluggable DB, control files and redo log files, shared oracle supplied metadata, root container, data dictionary, creation of PDB’s, seed PDB, limit PDB

Oracle Storage Structures

The basics of Oracle Storage structures, logical storage units like tablespaces, data blocks, extents and segments, database Schemas, Schema Objects, Operating System blocks, table statement and way to check “create table”, the instance, the CDB, containers, root containers, PDB, local user and common user, local privileges and common privileges, steps to create a container database, creating a container database using sqlplus, database configuration assistance

Memory & Process Architecture

The Oracle Instance consists of background processes and memory structures, learn about the Oracle process and memory architecture, shared pool, redo log buffer, and buffer cache, different methods of creating PDB, steps involved in PDB, using PDB$SEED, without FILE_NAME_CONVERT, synchronization, plug a non-CDB to CDB, plug a non-CDB into CDB using DBMS_PDB, clone PDBS, plug and un-plug, plug unplugged CDB in to CDB

Alert & Trace files

The Oracle background process errors can be monitored using the Trace Files and Alert Logs, learn how this can be deployed for Oracle Administration and support.

Database Startup & User Requests

The methodology of starting a database instance, initializing parameter files, preparing for startup, serving user requests, understanding of the Server process and user process, administrative activities on PDB, new views associated with PDBS, determining which PDB you are in, four functions, opening, closing, and altering the open mode of PDBS, setting the default and temporary tables places for a PDB, using the alter system command from within a PDB, instance parameter change impact

Managing data with ILM

Automatic data optimization, ILM components, ILM challenges, what is automatic data optimization, heat map and ADO, DBA heat map segment view, monitoring statistics, creating compression polices, creating storage tiering policy, preparing evolution and execution

Hands on Lab :- Automatic Data Optimization, Reduction Policies, Alter Policy

Database Security

Learn about the powerful Oracle database security features, data privacy, regulatory compliance, “create user” process, altering and dropping users, generating profiles and limiting resources, auditing, activity monitoring and blocking.

Database Schema Objects

Understanding of Database Schema objects, the various types available like views, tables, clusters, indexes, sequence, database links, packages and procedures, learning how data is stored in database tables, creating of temporary tables and external tables.

Database Resource Manager

Resource manager. Resource manager and pluggable database, managing resource between PDBs, CDB resource plan basics:share, CDB resource plan basics:Limits, creating a CDB resource plan, creating a CDB resource plan:SQL example, enabling a CDBresource plan, managing PDB resource plan

Deep dive into Schema Objects

Mastering the database Schema objects, learning Materialized View, deploying user-generated schema object for generating sequence, the balanced search tree index structure for placing and locating files, learn about data concurrency and data consistency in multi-user databases, the concepts of Locking and Deadlocks.

Oracle Network Environment

Study the client/server Network Environment, configuring the network, connecting to the database, the Oracle Network Environment and database link, starting up a instance, SHOUT DOWN, NOMOUNT, MOUNT, OPEN, PDB, OPEN, clone PDBS.

Oracle Backup & Recovery

Understand how Oracle database ecosystem backup and recovery is deployed, the database testing, database standby, Media recovery options, backup in offline mode, CDB backup and recover, archiving challenges and solutions, in data base archiving, security and performance: audit architecture, consolidation: unique audit trail, extended audit information, data pump audit policy, new administrative privileges, new administrative privileges:SYSBACKUP, new administrative privileges:SYSDG, new administrative privileges:SYSKM

Oracle Recovery Manager (RMAN)

Learn the significance of Oracle Recovery Manager, how it deploys performance, management and restoration of entire databases, learn about SQL Loader, Oracle Data Pump for import and export and support for External Table.

Data Dictionary & Dynamic Performance Tables

The Oracle Data Dictionary contains information related to database privileges, objects, users, and roles, learn Dynamic Performance Table for identifying instance-level problems, get complete knowledge to work with Oracle DBA, connection access DBA, data dictionary views, after CDB creation, alert log file, provisioning new pluggable databases NOARCHIVELOG mode, media failure, flashback CDB, data dictionary views

Database Tuning

Understanding what is database tuning, skills needed to lower response time, increase throughput for time-sensitive applications, how to optimize the storage space, deploy SQL scripts, Index Statistics and Selectivity, how to tune the database, what is a Chained Row and Lock in Oracle.

Database Tuning Continued

In-depth understanding of the Oracle database tuning, what is a shared pool tuning, how to improve the performance with the help of data dictionary, tuning the data dictionary, implementing the PL/SQL code and reusing the code and database buffer.

Data Warehouse Course Content

Introduction to Data Warehouse

Introducing Data Warehouse and Business Intelligence, understanding difference between database and data warehouse, working with ETL tools, SQL parsing.

Architecture of Data Warehouse

Understanding the Data Warehousing Architecture, system used for Reporting and Business Intelligence, understanding OLAP vs. OLTP, introduction to Cubes.

Data Modeling concepts

The various stages from Conceptual Model, Logical Model to Physical Schema, Understanding the Cubes, benefits of Cube, working with OLAP multidimensional Cube, creating Report using a Cube.

Data Normalization

Understanding the process of Data Normalization, rules of normalization for first, second and third normal, BCNF, deploying Erwin for generating SQL scripts.

Dimension & Fact Table

The main components of Business Intelligence – Dimensions and Fact Tables, understanding the difference between Fact Tables & Dimensions, understanding Slowly Changing Dimensions in Data Warehousing.

SQL parsing, Cubes & OLAP

SQL parsing, compilation and optimization, understanding types and scope of cubes, Data Warehousing Vs. Cubes, limitations of Cubes and evolution of in-memory analytics.

Erwin Design Layer Architecture

Learning the Erwin model, understanding the Design Layer Architecture, data warehouse modeling, creating and designing user defined domains, managing naming and data type standards.

Forward & Reverse Engineering

Understanding of the forward and reverse engineering, comparison between the two.

SQL Course Content

Introduction to SQL

Various types of databases, introduction to Structured Query Language, distinction between client server and file server databases, understanding SQL Server Management Studio, SQL Table basics, data types and functions, Transaction-SQL, authentication for Windows, Data control language, Identification and Keywords in T-SQL, Drop Table

SQL Operators

Introduction to relational databases, basic concepts of relational tables, working with rows and columns, various operators used like logical and relational, domains, constraints, stored procedures, indexes, primary key and foreign key, understanding group functions, unique key

Working with SQL: Join, Tables, and Variables

Deep dive into SQL Tables, working with SQL functions, operators and queries, creation of tables, retrieving data from tables, combining rows from tables using Inner, Outer, Cross and Self joins, deploying Operators like Union, Intersect, Except, creation of Temporary Table, Set Operator rules, working with Table variables

Deep Dive into SQL Functions

Understanding what SQL functions do, aggregate functions, scalar functions, functions that work on different data sets like numbers, characters & strings, dates, learning Inline SQL functions, general functions and duplicate functions

Working with Subqueries

Understanding of SQL Subqueries, rules of Subqueries, the statements and operators with which Subqueries can be used, modification of sub queries using set clause, understanding the different types of Subqueries – Where, Insert, Update, Select, Delete, etc., ways to create and view sub-queries

SQL Views, Functions, and Stored Procedures

Learning about SQL Views, ways of creating, using, altering, dropping, renaming and modifying Views, understanding Stored Procedures, key benefits of it, working with Stored Procedures, error handling, studying user-defined functions

Deep Dive into User-defined Functions

Detailed study of user-defined functions, various types of UDFs like Scalar, Inline Table Value, multi-statement Table, what are Stored Procedures? when to deploy Stored Procedures? What is Rank Function? Triggers, when to execute Triggers?

SQL Optimization and Performance

Detailed understanding of SQL Server Management Studio, learning what is Pivot in Excel and SQL Server, XL path, differentiating between Char, Varchar and NVarchar, working with Indexes, creation of Index, advantages, records grouping, searching, sorting, modifying data, creation of clustered indexes, using index to cover queries, index guidelines and Common Table Expression

Managing Data with Transact-SQL

Create Transact SQL Queries, Query multiple tables by using joins, implementing functions and aggregate data, modifying data, determining the results of DDL statements on supplied tables and data, and constructing DML statements using OUTPUT statement

Querying Data with Advanced Transact-SQL Components

Query data by using subqueries and APPLY, querying data by using table expressions, group and pivot data by using queries, querying temporal data and non-relational data, constructing recursive table expressions to meet business requirements, and using Windowing functions to group and rank the results of a query

Programming Databases Using Transact-SQL

Create database programmability objects by using T-SQL, implement error handling and transactions, implement transaction control in conjunction with error handling in stored procedures, and implement data types and NULL

Designing and Implementing Database Objects

Design and implement a relational database schema, design and implement indexes, distinguish between indexed and included columns, implementing clustered index, designing and implementing views, implementing column store views

Implementing Programmability Objects

Defining table and foreign key constraints, writing Transact-SQL statement, identifying results of Data Manipulation Language (DML), designing stored procedure components, implementing input and output parameters, implementing error handling, transaction control logic in stored procedures, designing trigger logic, DDL triggers

Managing Database Concurrency

Implementing transactions, identifying DML statements based on transaction behavior, understanding explicit and implicit transactions, managing isolation levels, identifying concurrency and locking behavior, implementing memory-optimized tables

Optimizing Database Objects

Determining accuracy of statistics, designing statistics maintenance tasks, using dynamic management objects, identifying missing indexes, consolidating overlapping indexes, analyzing and troubleshooting query plans, managing performance of database instances, monitoring SQL server performance

Microsoft Courses: Study Material

  • Performance Tuning and Optimizing SQL Databases
  • Querying Data with Transact-SQL

MS SQL Server DBA Course Content

Installation and Configuration

  • Plan Installation

Evaluate installation requirements; design the installation of SQL Server and its components (drives, service accounts, etc.); plan scale-up vs. scale-out basics; plan for capacity, including if/when to shrink, grow, autogrow, and monitor growth; manage the technologies that influence SQL architecture (e.g., service broker, full text, scale out, etc.); design the storage for new databases (drives, filegroups, partitioning, etc.); design the database infrastructure; configure an SQL Server standby database for reporting purposes; Windows-level security and service-level security; core mode installation; benchmark a server before using it in a production environment (SQLIO, Tests on SQL Instance, etc.); and choose the right hardware

  • Installing SQL Server and Related Services

Test connectivity; enable and disable features; install SQL Server database engine and SSIS (but not SSRS and SSAS); and configure an OS disk

  • Implementing a Migration Strategy

Restore vs. detach/attach; migrate security; migrate from a previous version; migrate to new hardware; and migrate systems and data from other sources

  • Configuring Additional SQL Server Components

Set up and configure all SQL Server components (Engine, AS, RS, and SharePoint integration) in a complex and highly secure environment; configure full-text indexing; SSIS security; filestream; and filetable

  • Manage SQL Server Agent

Create, maintain, and monitor jobs; administer jobs and alerts; automate (setup, maintenance, monitoring) across multiple databases and multiple instances; send to “Manage SQL Server Agent jobs”

Managing Instances and Databases

  • Managing and Configuring Databases

Design multiple file groups; database configuration and standardization: autoclose, autoshrink, recovery models; manage file space, including adding new filegroups and moving objects from one filegroup to another; implement and configure contained databases; data compression; configure TDE; partitioning; manage log file growth; DBCC

  • Configuring SQL Server Instances

Configure and standardize a database: autoclose, autoshrink, recovery models; install default and named instances; configure SQL to use only certain CPUs (affinity masks, etc.); configure server level settings; configure many databases/instance, many instances/server, virtualization; configure clustered instances including MSDTC; memory allocation; database mail; configure SQL Server engine: memory, filffactor, sp_configure, default options

  • Implementing an SQL Server Clustered Instance

Install a cluster; manage multiple instances on a cluster; set up subnet clustering; recover from a failed cluster node

  • Managing SQL Server Instances

Install an instance; manage interaction of instances; SQL patch management; install additional instances; manage resource utilization by using Resource Governor; cycle error logs

Optimizing and Troubleshooting

  • Identifying and Resolving Concurrency Problems

Examine deadlocking issues using the SQL server logs using trace flags; design reporting database infrastructure (replicated databases); monitor via DMV or other MS product; diagnose blocking, live locking and deadlocking; diagnose waits; performance detection with built in DMVs; know what affects performance; and locate and if necessary kill processes that are blocking or claiming all resources

  • Collecting, Analyzing, and Troubleshooting Data

Monitor using Profiler; collect performance data by using System Monitor; collect trace data by using SQL Server Profiler; identify transactional replication problems; identify and troubleshoot data access problems; gather performance metrics; identify potential problems before they cause service interruptions; identify performance problems;, use XEvents and DMVs; create alerts on critical server condition; monitor data and server access by creating audit and other controls; identify IO vs. memory vs. CPU bottlenecks; and use the Data Collector tool

  • Auditing SQL Server Instances

Implement a security strategy for auditing and controlling the instance; configure an audit; configure server audits; track who modified an object; monitor elevated privileges as well as unsolicited attempts to connect; and policy-based management

Managing Data

  • Configuring and Maintaining a Back-up Strategy

Manage different backup models, including point-in-time recovery; protect customer data even if backup media is lost; perform backup/restore based on proper strategies including backup redundancy; recover from a corrupted drive; manage a multi-TB database; implement and test a database implementation and a backup strategy (multiple files for user database and tempdb, spreading database files, backup/restore); back up a SQL Server environment; and back up system databases

  • Restoring Databases

Restore a database secured with TDE; recover data from a damaged DB (several errors in DBCC checkdb); restore to a point in time; file group restore; and page-level restore

  • Implementing and Maintaining Indexes

Inspect physical characteristics of indexes and perform index maintenance; identify fragmented indexes; identify unused indexes; implement indexes; defrag/rebuild indexes; set up a maintenance strategy for indexes and statistics; optimize indexes (full, filter index); statistics (full, filter) force or fix queue; when to rebuild vs. reorg and index; full text indexes; and column store indexes

  • Importing and Exporting Data

Transfer data; bulk copy; and bulk insert

Implementing Security

  • Managing Logins and Server Roles

Configure server security; secure the SQL Server using Windows Account / SQL Server accounts, server roles; create log in accounts; manage access to the server, SQL Server instance, and databases; create and maintain user-defined server roles; and manage certificate logins

  • Managing Database Security

Configure database security; database level, permissions; protect objects from being modified; auditing; and encryption

  • Managing Users and Database Roles

Create access to server / database with least privilege; manage security roles for users and administrators; create database user accounts; and contained login

  • Troubleshooting Security

Manage certificates and keys, and endpoints

Implementing High Availability

  • Implementing AlwaysOn
    • Implement AlwaysOn availability groups and AlwaysOn failover clustering
  • Implementing replication
    • Troubleshoot replication problems and identify appropriate replication strategy
view more
Read Less

Database Architect Projects

What projects I will be working on this Oracle PL SQL training?

Project : Performing DML operations on database

Industry : General

Problem Statement : How to manipulate data with DML

Topics : In this project you will learn about the Data Manipulation Language (DML), understand all about the DML triggers which are a special type of stored procedures that are automatically executed when DML operation is performed. You will learn about the various DML operations like INSERT, UPDATE, or DELETE which when fired on a Table or View can activate a DML Trigger. This project involves working with database and application Triggers, deploying the Triggers for various business applications and testing of the DML Triggers.

Highlights :

  • Manipulating data with DML
  • Working with DML Triggers
  • Various DML operations.

What projects I will be working on this Oracle DBA training?

Project 1 : Tuning of the database

Industry : General

Problem Statement :  how to optimize the Oracle database and perform database tuning on it.

Topics – This project is involved with working on the Oracle Database for deploying the database performance tuning which includes optimizing and homogenizing the database performance. It is associated with query tuning but also includes DBMS selection, and configuration of the database environment. There are multiple tasks that you will perform giving you a hands-on experience –

Highlight :

  • Space usage monitoring
  • SQL scripts monitoring
  • Database & SQL tuning
  • Table & Index Statistics
  • Chained Rows & Locks

What projects I will be working on this Data Warehouse training?

Project Work

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

Data –Sales

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

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

Data –Sales

Problem Statement – It describes how to create and deploy a data warehouse. It also involves loading the data into data warehouse using ETL.

What projects I will be working on this SQL training?

Project : Writing complex Sub Queries

Industry : General

Problem Statement : How to create sub queries using SQL

Topics : This project will give you hands-on experience in working with SQL sub-queries and utilizing it in various scenarios. Some of the sub-queries that you will be working with and gain hands-on experiences in are – IN or NOT IN, ANY or ALL, EXISTS or NOT EXISTS, and other major queries.

Highlight :

  • Accessing and manipulating databases
  • Operators & Control Statements in SQL
  • Executing queries in SQL against databases.

What projects I will be working on this MS SQL Server DBA training?

Project : SQL Server Audit

Industry : General

Problem Statement : How to track and log events happening on the database engine

Topics : This project is involved with implementing an SQL Server audit that includes creating of the TestDB database, triggering audit events from tables, altering audit, checking, filtering, etc. You will learn to audit an SQL Server instance by tracking and logging the events on the system. You will work with SQL Server Management; learn about database level and Server level auditing.

Highlight :

  • SQL Server Management Studio
  • Expanding SQL Server Log Folder
  • Database & Server audit specification.
view more
Read Less Project

Sample Database Architect Video Tutorials

view more
View Less Sample Videos

Database Architect Certification

This course is designed for clearing two certification exams.

  • Oracle PL/SQL Developer Certified Associate
  • Oracle Database Administrator Certified Associate

The entire course content is in line with these certification exams and helps you clear them with ease and get the best jobs in top MNCs.

As part of this training, you will be working on real-time projects and assignments that have immense implications in the real-world industry scenarios, thus helping you fast track your career effortlessly.

At the end of this training program, there will be quizzes that perfectly reflect the type of questions asked in the respective certification exams and help you score better marks.

Intellipaat Course Completion Certification will be awarded upon the completion of the project work (after expert review) and upon scoring at least 60% marks in the quiz. Intellipaat certification is well recognized in top 80+ MNCs like Ericsson, Cisco, Cognizant, Sony, Mu Sigma, Saint-Gobain, Standard Chartered, TCS, Genpact, Hexaware, etc.

view more
Read Less Certification

Database Architect Review

view more
View Less Reviews Video
  1. Profile photo of Saurabh Saxena Saurabh Saxena 

    Reasonable and well organised

    The course was reasonably priced and the classes were highly interactive and also practical oriented. Every teaching session was recorded each day and was put on-line by the institute which was really helpful. The trainer was very patient and able to solve all the questions posed to him.

  2. Profile photo of Anjali Srivastava Anjali Srivastava 

    Wonderful learning

    The course content is great, and the trainer is eloquent and does an excellent job not just explaining concepts but also demonstrating them. The lectures are thorough. I think this course is of great value and appreciate the course material being accessible in perpetuity.

Frequently Asked Questions on Database Architect

Why should I learn Database Architect from Intellipaat?

Intellipaat offers a comprehensive Database Architect training. This is an industry-designed training course that includes Oracle Pl/SQL, DBA, data warehousing and data modeling, among other things. You will gain proficiency in database administration and SQL development. The entire course is in line with clearing two certifications such as Oracle PL/SQL Developer Certified Associate and Oracle Database Administrator Certified Associate.

You will be working on real-time projects and step-by-step assignments that have high relevance in the corporate world, and the course curriculum is designed by industry experts. Upon the completion of the training course, you can apply for some of the best jobs in top MNCs around the world at top salaries. Intellipaat offers lifetime access to videos, course materials, 24/7 support and course material upgrading to the latest version at no extra fees. Hence, it is clearly a one-time investment.

What are the different modes of training that Intellipaat provides?
At Intellipaat you can enroll either for the instructor-led online training or self-paced training. Apart from this Intellipaat also offers corporate training for organizations to upskill their workforce. All trainers at Intellipaat have 12+ years of relevant industry experience and they have been actively working as consultants in the same domain making them subject matter experts. Go through the sample videos to check the quality of the trainers.
Can I request for a support session if I need to better understand the topics?
Intellipaat is offering the 24/7 query resolution and you can raise a ticket with the dedicated support team anytime. You can avail the email support for all your queries. In the event of your query not getting resolved through email we can also arrange one-to-one sessions with the trainers. You would be glad to know that you can contact Intellipaat support even after completion of the training. We also do not put a limit on the number of tickets you can raise when it comes to query resolution and doubt clearance.
Can you explain the benefits of the Intellipaat self-paced training?
Intellipaat offers the self-paced training to those who want to learn at their own pace. This training also affords you the benefit of query resolution through email, one-on-one sessions with trainers, round the clock support and access to the learning modules or LMS for lifetime. Also you get the latest version of the course material at no added cost. The Intellipaat self-paced training is 75% lesser priced compared to the online instructor-led training. If you face any problems while learning we can always arrange a virtual live class with the trainers as well.
What kind of projects are included as part of the training?
Intellipaat is offering you the most updated, relevant and high value real-world projects as part of the training program. This way you can implement the learning that you have acquired in a real-world industry setup. All training comes with multiple projects that thoroughly test your skills, learning and practical knowledge thus making you completely industry-ready. You will work on highly exciting projects in the domains of high technology, ecommerce, marketing, sales, networking, banking, insurance, etc. Upon successful completion of the projects your skills will be considered equal to six months of rigorous industry experience.
Does Intellipaat offer job assistance?
Intellipaat actively provides placement assistance to all learners who have successfully completed the training. For this we are exclusively tied-up with over 80 top MNCs from around the world. This way you can be placed in outstanding organizations like Sony, Ericsson, TCS, Mu Sigma, Standard Chartered, Cognizant, Cisco, among other equally great enterprises. We also help you with the job interview and résumé preparation part as well.
Is it possible to switch from self-paced training to instructor-led training?
You can definitely make the switch from self-paced to online instructor-led training by simply paying the extra amount and joining the next batch of the training which shall be notified to you specifically.
How are Intellipaat verified certificates awarded?
Once you complete the Intellipaat training program along with all the real-world projects, quizzes and assignments and upon scoring at least 60% marks in the qualifying exam; you will be awarded the Intellipaat verified certification. This certificate is very well recognized in Intellipaat affiliate organizations which include over 80 top MNCs from around the world which are also part of the Fortune 500 list of companies.
Will The Job Assistance Program Guarantee Me A Job?
In our Job Assistance program we will be helping you land in your dream job by sharing your resume to potential recruiters and assisting you with resume building, preparing you for interview questions. Intellipaat training should not be regarded either as a job placement service or as a guarantee for employment as the entire employment process will take part between the learner and the recruiter companies directly and the final selection is always dependent on the recruiter.
view more
Read Less FAQ
Self-paced
$460
Lifetime Access and 24/7 Support
You have of in your cart.
Online Classroom
$965

28

Sep
Sat & Sun
8 PM IST (GMT +5:30)

05

Oct
Sat & Sun
8 PM IST (GMT +5:30)

12

Oct
Sat & Sun
8 PM IST (GMT +5:30)
Drop Us a Query

Training in Cities: Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

Training in Cities: Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

Sign Up or Login to view the Free Database Architect Training: Combo Course course.