Duration
3 days
OVERVIEW
One of the most important inventions in Information System is “Database”. A database management system stores, organizes and manages information within a single software application called Database. A database management system is important because it manages data efficiently and allows users to perform multiple tasks with ease. Today every other application in the market is running on some kind of Database. Even the media player software also using some kind of database to keep information about album and media files in the album.
Database management systems (DBMS) are important to businesses and organizations because they provide a highly efficient method for handling multiple types of data. Some of the information stored in database includes employee records, student information, payroll, accounting and inventory. Without database management, tasks have to be done manually and take more time. Multiple users can use the system at the same time in different ways. Applications use Structured Query Language (SQL) provided by DBMS for defining, protecting and accessing data. SQL (Structured Query Language) is a standardized programming language used for managing data in relational databases and performing various operations on the data in them. The uses of SQL include modifying database table and index structures; adding, updating and deleting rows of data; and retrieving subsets of information from within a database for transaction processing and analytics applications.
This course takes participants from zero to DBA, taking a very practice-orientated route. Participants will be creating and managing databases, learning the basics of table, fields and data. The program takes two open source databases such as MySQL and PostgreSQL to demonstrate the concepts of Database and SQL. Participants learn how to design and implement databases and how to work with foreign keys, how to develop SQL statements, transactions, views, stored programs and triggers for their databases in MYSQL and PostgreSQL. In addition, fundamentals of database administration are covered, including logging, configuration, security, backup, and recovery.
OBJECTIVES
After completing this program students will acquire the following learning competencies.
- Deep understanding the principles of RDBMS and their languages
- Construct advanced database queries using SQL
- Deep understanding the DBMS concepts such as database security, privilege system and database optimization
- Design and implement databases
- Work with any open source databases such as MySQL or PostgreSQL
- Use any SQL client to work with a database and run SQL statements
- Advanced SQL
- Work with summary queries, sub queries, data types, functions, stored procedures, triggers, events, manage transactions and locking
- Perform administrative tasks, including monitoring, configuring, logging, adding users, removing users, secure, back up, and restore a database
WHO SHOULD ATTEND
This course is aimed at an audience of Software Developers, Database Analysts, Data Architects, and System Architects.
PREREQUISITE
Experience in basic computer literacy with previous experience with command-line programs and some knowledge of database concepts is required. Some knowledge of data retrieval and reporting would be beneficial.
Introduction
- Data versus Information
- Record
- The File System
- The Evolution of Database
- Entities and Relationships
- Data Models
- Database Design
- Types of Databases
- Transaction Management
1.10. Introduction to SQL
- Advanced SQL
- Constrains
- Create Index
- SQL Functions
- GROUP BY Statement
- HAVING Clause
- SQL Special Functions and SQL Alias
- SQL Join
- Sub-Queries and Recursive Queries
- Data Control Language
2.10. View and Assertion
- PL / SQL
- An Introduction to PL/SQL
- Need for PL/SQL
- PL/SQL Code Structure
- Triggers
- Event Condition Action (ECA)
- Functions
- Procedures
- PL/SQL Compilation Errors
- Embedded SQL
3.10. Dynamic SQL
- Advanced Transaction Processing and Recovery
Introduction
- Defining a Transaction in DBMS
- Enhanced Lock-Based and Timestamp-Based Protocols
- Multiple Granularity
- Multi-version Schemes
- Optimistic Concurrency Control Techniques
- Deadlock of Handling
- Recovery in DBMS
- Write-Ahead Logging Protocol
4.10. Advanced Recovery Techniques
4.11. Use of SQL in Recovery
Day 2
- Data Security
- Introduction to Data Security Issues
- Discretionary Access Control
- Mandatory Access Control
- Role-Based Access Control
- SQL Injection
- Statistical Databases
- Introduction to Flow Control
- Storage and Indexing
Introduction
- Basics of Database Storage
- Operations on Files
- Hashing Techniques
- File Indexing
- Types of Single-Level Ordered Indexes
- Multi-Level Indexes
- Dynamic Multi-Level Indexes
- Indexing on Multiple Keys
- Distributed Databases
Introduction
- Difference between Centralized DBMS and Distributed DBMS
- Functionality of DDBMS, Advantages and Disadvantages of DDBMS
- Centralized vs Non-Centralized Databases
- Homogeneous and Heterogeneous DDBMS
- Architecture of DDBMS
- Distributed Data Storage
- Transparency Issue in DDBMS
- Distributed Database Design and Query Processing in DDBMS
7.10. Distributed Transaction Management
7.11. Distributed Commit Protocols
7.12. Distributed Concurrency Control
7.13. Distributed Deadlock Management
7.14. Concepts of Replication Servers
- Object Database
Introduction
- Overview of the Object-Oriented Database
- Limitations of Relational Database
- Need for Complex Data Types
- Collection Types and Structured Types
- Data Definition
- Object Identity and Reference Types
- Persistence of Objects
- Object – Relational Database
8.10. Object Database Extensions to SQL
8.11. Object Definition Language
8.12. Object Query Language
Day 3
- Introduction to Data Warehousing
Introduction
- Need for Data Warehousing
- An Information Environment
- Increased Demand for Strategic Information
- Inability of Past Decision Support System
- Operational vs Decisional Support System
- Information Flow Mechanism
- Classification of Metadata
- Data Warehouse and Data Marts
9.10. Data Warehouse Architecture
9.11. Different Types of Architecture
9.12. Data Warehouse Design Techniques
- Introduction to Dimensional Modeling
Introduction
- Data Warehouse Modeling vs Operational Database Modeling
- Dimensional Model vs the ER Model
- Features of a Good Dimensional Model
- Introducing the Fact table
- Introducing the Dimension table
- The Factless Face Table
- The Star Schema
- Snowflake Schema
- Updates to Dimension Tables
- Slowly Changing Dimensions
- Large Dimension Tables
- Rapidly Changing or Large Slowly Changing Dimensions
- Junk Dimensions
- Keys in the Data Warehouse Schema
- Aggregate Table
- Fact Constellation Schema or Family of Stars
- ETL Process
Introduction
- Overview of the ETL Process
- Need for the ETL Process
- Data Extraction
- Identification of Data Source
- Extraction Methods in Data Warehouse
- Change Data Capture
- Transportation in Data Warehouse
- Transformation
- Staging
- Loading
- Fact Tables
- Dimension Tables
- Cleaning
- Tools for ETL
- Online Analytical Processing (OLAP)
Introduction
- Introduction to OLAP
- Need for OLAP
- OLAP Models
- OLTP vs OLAP
- Multidimensional Analysis or Data Cube
- On-Line Analytical Processing (OLAP)
Programme scope, delivery arrangements and suitability are confirmed during enquiry.