Advanced DBMS Concepts

Study transactions, indexing, normalization, and database design.

Search Centers Visit Site
Advanced DBMS Concepts showcase image
Track
Software Development
Level
Advanced
Language
English
Duration
60 hours
Learning Mode
Learn at ALC or at Home
Introduction
What you'll learn ?
Syllabus
Work-Centric Approach

Introduction

  • SQL Proficiency: Master SQL queries, triggers, cursors, and stored procedures.
  • Database Design: Learn normalization, schema design, and anomaly prevention.
  • Transaction Management: Understand ACID properties, concurrency control, and recovery techniques.
  • NoSQL Skills: Explore key-value, graph, and MongoDB databases, including CRUD, indexing, and aggregation.
  • Cloud Integration: Learn cloud database deployment, clustering, and big data visualization tools.
  • Practical Application: Build real-world database applications and complete mini-projects.
  • Career Preparation: Gain skills for roles in database technology and analytics.
  • This concise version highlights the core outcomes of the course.

What you'll learn ?

  • SQL Mastery
  • Ability to write complex SQL queries, including aggregate and grouping functions.
  • Proficiency in using triggers, cursors, and stored procedures for database operations.
  • Database Design Expertise
  • Design efficient relational schemas using normalization and functional dependencies.
  • Prevent anomalies and ensure data integrity in relational databases.
  • Transaction Management Skills
  • Manage transactions with ACID properties, concurrency control, and recovery mechanisms.
  • Handle deadlocks, timestamp ordering, and multi-version concurrency control.
  • NoSQL Database Proficiency
  • Work with NoSQL databases, including MongoDB, for CRUD operations, indexing, and aggregation.
  • Apply advanced concepts like replication, sharding, and MapReduce.
  • Cloud and Big Data Knowledge
  • Understand cloud database deployment and utilize BI tools like Power BI and Tableau for analytics.
  • Implement clustering and sharding techniques for scalable databases.
  • Practical Experience
  • Develop database-driven applications using SQL, JDBC, and MongoDB.
  • Apply knowledge to real-world projects and mini-case studies.
  • Career Readiness
  • Gain the technical expertise needed for roles in database management, analytics, and cloud solutions.
  • Be prepared to work as a database administrator, data analyst, or application developer.
  • These outcomes reflect the comprehensive skill set and industry readiness gained from the course.

Syllabus

Session 01
  • PL/SQL Block of Code
  • SQL Query Using Aggregate Functions_1
  • SQL Query Using Aggregate Functions_2
  • SQL Query Using Aggregate Functions_3
  • SQL Query Using Aggregate Functions_4
  • Introduction to Triggers in SQL_1
    • Introduction to Triggers in SQL_2
  • Assertions vs Triggers
  • The DROP Command
  • The ALTER Command
  • Example on Assertions
    • List Data Definition Languages (DDL) Commands of Base Table and View
  • List Data Manipulation Language (DML) Of Base Tables and Views_1
  • List Data Manipulation Language (DML) Of Base Tables and Views_2
    • Exercise on Data Manipulation Language (DML) Of Base Tables and Views_1
    • Exercise on Data Manipulation Language (DML) Of Base Tables and Views_2
    • Examples Using Grouping Functions
  • SQL Query Using Grouping Functions
  • Insert, Delete and Update Triggers
  • Database Trigger on Library Table
  • Database Application Development
    • Accessing Databases from Applications
    • Cursors
  • Basic Cursor Definition and Usage
  • Basic Cursor Definition and Usage Examples
  • Properties of Cursors
  • Dynamic SQL
  • Introduction to JDBC_1
  • Introduction to JDBC_2
  • JDBC Classes and Interfaces
    • JDBC Driver Management
  • Connections
  • Executing SQL Statements
  • Result Sets
  • Matching Java and SQL Data Types
  • Exceptions and Warnings
  • Examining Database Metadata_1
  • Examining Database Metadata_2
  • SQL in Java (SQLJ)
    • Stored procedures
  • Stored Procedures - Steps to Call and Execute
  • Example - Creation of Stored Procedures
  • SQL/PSM
  • SQL/PSM Example
    • Write a SQL block
  • SQL Block to Create Trigger on Library Table
  • Introduction and Objectives to DB Design
  • Informal Design Guidelines for Relation Schema
  • Imparting Clear Semantics to Attributes in Relations, Guidelines and Example
  • Redundant Information in Tuples and Update Anomalies
    • Insertion, Deletion and Modification Anomalies
  • Generation of Spurious Tuples
  • Functional Dependencies - Definition and Diagrammatic Notations
  • Normalization of Relations
  • Practical Use of Normal Forms
  • Definitions of Keys and Attributes Participating in Keys
  • First Normal Form (1NF)
    • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce-Codd Normal Form
  • Multivalued Dependency and Fourth Normal Form
    • Formal Definition of Multivalued Dependency
  • Join Dependencies and Fifth Normal Form
  • Normalization Algorithms
    • Inference Rules, Equivalence and Minimal Cover
    • Inference Rules for Functional Dependencies
  • Equivalence of Sets of Functional Dependencies
    • Sets of Functional Dependencies with Algorithms
  • Properties of Relational Decompositions
    • Universal Relation Schema
    • Attribute Preservation Condition of a Decomposition
    • Dependency Preservation Property
    • Lossless (Non-additive) Join Property
    • Testing Binary Decompositions for the Nonadditive Join Property
  • Algorithms for Relational Database Schema Design
    • Dependency Preservation and Nonadditive Join Decomposition into 3NF Schemas
    • Nonadditive Join Decomposition into BCNF Schemas
  • Nulls, Dangling Tuples and Alternative Relational Desig
    • Problems with NULL Values and Dangling Tuples
  • Inclusion Dependencies
  • Template Dependencies
  • Functional Dependencies Based on Arithmetic Functions and Procedures
    • Domain-Key Normal Form
  • Transaction Processing
  • Features and Types of TPS
  • Single-User vs Multiuser Systems
  • Transactions, Database Items, Read and Write Operations and DBMS Buffers_1
  • Transactions, Database Items, Read and Write Operations and DBMS Buffers_2
  • Why Concurrency Control Is Needed_1
  • Why Concurrency Control Is Needed_2
    • Why Recovery Is Needed
  • Transaction and System Concepts
    • Transaction States and Additional Operations
    • The System Log
  • ACID Properties_1
    • ACID Properties_2
  • Commit Point of a Transaction
  • DBMS Specific Buffer Replacement Policies
    • Desirable Properties of Transactions_1
    • Desirable Properties of Transactions_2
  • Characterizing Schedules Based on Recoverability
  • Characterizing Schedules Based on Serializability
    • Testing Conflict Serializability of a Schedule S-1
  • End Test
    • Credit 3 End Test
  • Transaction Support in SQL_1
    • Transaction Support in SQL_2
  • Introduction to Concurrency Control_1
    • Introduction to Concurrency Control_2
  • Two-Phase Locking Techniques for Concurrency Control
    • Types of Locks and System Lock Tables_1
    • Types of Locks and System Lock Tables_2
    • Guaranteeing Serializability by Two-Phase Locking
  • Variations of Two-Phase Locking_1
  • Variations of Two-Phase Locking_2
    • Dealing with Deadlock and Starvation_1
  • Dealing with Deadlock and Starvation_2
  • Deadlock Detection_1
    • Deadlock Detection_2
  • Concurrency Control Based on Timestamp Ordering
    • Timestamps
  • Multiversion Concurrency Control Techniques
  • Multiversion Technique Based on Timestamp Ordering
  • Multiversion Two-Phase Locking Using Certify Locks
    • Validation Concurrency Control Techniques
  • Granularity of Data Items and Multiple Granularity Locking
  • Recovery Concepts
    • Recovery Outline and Categorization of Recovery Algorithms
    • Caching (Buffering) of Disk Blocks
    • Write-Ahead Logging, Steal/No-Steal and Force/No-Force_1
    • Write-Ahead Logging, Steal/No-Steal and Force/No-Force_2
    • Checkpoints in the System Log and Fuzzy Checkpointing
    • Transaction Rollback and Cascading Rollback
    • Transaction Actions That Do Not Affect the Database
  • NO - UNDO or REDO Recovery Based on Deferred Update
  • Recovery Techniques Based on Immediate Update
  • Shadow Paging
  • Database Backup and Recovery from Catastrophic Failures
  • Key value Database
  • Introduction
  • Working of Key-Value Databases
  • Features and Functions of Key-Value Databases
  • Schema Design to Support Key Value_1
  • Schema Design to Support Key Value_2
  • Key - Value Database Vs Cache
  • Graph Database
  • The Property Graph Model
  • Why Data Relationships Matter
  • Why Other NoSQL Databases don’t Fix the Problem Either
  • Relational vs Graph Data Modeling Match-Up
  • Example Data Model - Fraud Detection in Email Communications
  • Database Query Language
  • Cloud Databases
  • Difference Between Cloud and Database
  • Cloud Database Deployment Models
  • Key Benefits of Cloud Databases
  • When to Use a Cloud Database
  • Additional Considerations of Cloud Database
  • Some Large Cloud Providers and Databases
  • Database Clustering and Sharding
  • Shared - Nothing Architecture
  • Shared - Disk Architecture
  • Sharding
  • Sharded Cluster Components
  • Shard Keys
  • Advantages of Sharding
  • Considerations Before Sharding_1
  • Considerations Before Sharding_2
  • Considerations Before Sharding_3
  • Sharded and Non-Sharded Collections and Connecting to a Sharded Cluster_1
  • Sharded and Non-Sharded Collections and Connecting to a Sharded Cluster_2
  • Data Analytics and BI Tools
  • Introduction, Benefits of Business Intelligence Tools
  • Best data analytics and BI tools
  • Microsoft Power BI_1
  • Microsoft Power BI_2
  • Tableau_1
  • Tableau_2
  • Qlik Sense_1
  • Qlik Sense_2
  • Sisense_1
  • Sisense_2
  • Best BI Tool for Developing and Deploying Analytics Apps
  • Looker
  • Oracle_1
  • Oracle_2
  • Domo BI_1
  • Domo BI_2
  • SaS Analytics_1
  • SaS Analytics_2
  • MongoDB - Overview
  • Relationship of RDBMS Terminology with MongoDB
  • Sample Document
  • Key Components of MongoDB Architecture
  • MongoDB - Advantages
  • Functionality of MongoDB
  • MongoDB - CAP Approach
  • MongoDB - Hierarchical Objects
  • MongoDB - Environment
  • MongoDB Processes and Configuration
  • Install MongoDB on Windows
  • Install MongoDB on Ubuntu_1
  • Install MongoDB on Ubuntu_2
  • MongoDB Statistics
  • CRUD Operations in MongoDB_1
  • CRUD Operations in MongoDB_2
  • CRUD Operations in MongoDB_3
  • CRUD Operations in MongoDB_4
  • MongoDB - Data Modelling and Documentation
  • Data Modelling
  • Some Considerations While Designing Schema in MongoDB
  • Some Considerations While Designing Schema in MongoDB_1
  • Some Considerations While Designing Schema in MongoDB_2
  • Create Database
  • Drop Database
  • Create Collection
  • Data Types
  • Insert Document
  • Query Document
  • Update Document
  • Delete Document
  • Design and Develop MongoDB Queries using CRUD operations
  • Design and Develop MongoDB Queries using CRUD operations
  • Projection
  • Limiting Records
  • Sorting Records
  • Aggregation
  • Aggregation - Examples
  • Authentication Mechanisms in MongoDB
  • Backing up and Restoring Data
  • Backing up and Restoring Data- Examples
  • Bulk Operations with Examples
  • Indexing_1
  • 2D - Sphere Index
  • Indexing_2
  • Replication_1
  • Replication_2
  • Program to Implement Aggregation and Indexing in MongoDB_1
  • Program to Implement Aggregation and Indexing in MongoDB_2
  • Program to Implement Aggregation and Indexing in MongoDB_3
  • Create Backup of Sharded Cluster
  • SQL vs Mongo DB Entities
  • Consistency of Data_1
  • Consistency of Data_2
  • Query through API_1
  • Query through API_2
  • Exercise – 1
  • Exercise – 2
  • Exercise – 3
  • Advanced MongoDB
  • MongoDB – Deployment
  • Typical MongoDB Deployment, Read Preference and Write Concern_1
  • Operation Performance and Partition
  • MongoDB - Covered Queries_1
  • MongoDB - Covered Queries_2
  • Relationships
  • Embedded Relationships in MongoDB
  • One to One Relationships in MongoDB
  • One to Many Relationships in MongoDB
  • Document Referenced Relationships in MongoDB_1
  • Document Referenced Relationships in MongoDB_2
  • Database References_1
  • Database References_2
  • List of Drivers Supported by DBRefs
  • MongoDB - Map Reduce
  • Syntax
  • Examples
  • Using MapReduce_1
  • Using MapReduce_2
  • Using MongoDB as a Service: mLab
  • Implement MapReduce Operation using MongoDB_1
  • Implement MapReduce Operation using MongoDB_2
  • Implement MapReduce Operation using MongoDB_3
  • Execute the Query and Study its Execution Plan_1
  • Execute the Query and Study its Execution Plan_2
  • Careers in Database Technology_1
  • Careers in Database Technology_2
  • Careers in Database Technology_3
  • Mini Project 1
  • Mini Project 2
  • Mini Project 3
  • Mini Project 4
  • Mini Project 5
  • Credit 4 End Test
    • Credit 4 End Test

Work-Centric Approach

The academic approach of the course focuses on ‘work-centric’ education. With this hands-on approach, derive knowledge from and while working to make it more wholesome, delightful and useful. The ultimate objective is to empower learners to also engage in socially useful and productive work. It aims at bringing learners closer to their rewarding careers as well as to the development of the community.

  • Step 1: Learners are given an overview of the course and its connection to life and work
  • Step 2: Learners are exposed to the specific tool(s) used in the course through the various real-life applications of the tool(s).
  • Step 3: Learners are acquainted with the careers and the hierarchy of roles they can perform at workplaces after attaining increasing levels of mastery over the tool(s).
  • Step 4: Learners are acquainted with the architecture of the tool or tool map so as to appreciate various parts of the tool, their functions, utility and inter-relations.
  • Step 5: Learners are exposed to simple application development methodology by using the tool at the beginner’s level.
  • Step 6: Learners perform the differential skills related to the use of the tool to improve the given ready-made industry-standard outputs.
  • Step 7: Learners are engaged in appreciation of real-life case studies developed by the experts.
  • Step 8: Learners are encouraged to proceed from appreciation to imitation of the experts.
  • Step 9: After the imitation experience, they are required to improve the expert’s outputs so that they proceed from mere imitation to emulation.
  • Step 10: Emulation is taken a level further from working with differential skills towards the visualization and creation of a complete output according to the requirements provided. (Long Assignments)
  • Step 11: Understanding the requirements, communicating one’s own thoughts and presenting are important skills required in facing an interview for securing a work order/job. For instilling these skills, learners are presented with various subject-specific technical as well as HR-oriented questions and encouraged to answer them.
  • Step 12: Finally, they develop the integral skills involving optimal methods and best practices to produce useful outputs right from scratch, publish them in their ePortfolio and thereby proceed from emulation to self-expression, from self-expression to self-confidence and from self-confidence to self-reliance and self-esteem!