Track
Software Development
Duration
60 hours
Skill Level
Advanced
Language
English
About this Course
Study transactions, indexing, normalization, and database design.
Learning Mode: Learn at ALC or at Home
Detailed Course Curriculum
Hands-on module breakdown aligned with MKCL production standards and industry requirements.
- 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
- 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 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_1
- Exercise on Data Manipulation Language (DML) Of Base Tables and Views_2
- Exercise on Data Manipulation Language (DML) Of Base Tables and Views_2
- Examples Using Grouping Functions
- 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
- Accessing Databases from Applications
- Cursors
- 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
- 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
- Stored Procedures - Steps to Call and Execute
- Example - Creation of Stored Procedures
- SQL/PSM
- SQL/PSM Example
- Write a SQL block
- 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
- 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)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form
- Multivalued Dependency and Fourth Normal Form
- Formal Definition of Multivalued Dependency
- Formal Definition of Multivalued Dependency
- Join Dependencies and Fifth Normal Form
- Normalization Algorithms
- Inference Rules, Equivalence and Minimal Cover
- Inference Rules, Equivalence and Minimal Cover
- Inference Rules for Functional Dependencies
- Inference Rules for Functional Dependencies
- Equivalence of Sets of Functional Dependencies
- Sets of Functional Dependencies with Algorithms
- Sets of Functional Dependencies with Algorithms
- Properties of Relational Decompositions
- Universal Relation Schema
- Universal Relation Schema
- Attribute Preservation Condition of a Decomposition
- Attribute Preservation Condition of a Decomposition
- Dependency Preservation Property
- Dependency Preservation Property
- Lossless (Non-additive) Join Property
- Lossless (Non-additive) Join Property
- Testing Binary Decompositions for the Nonadditive 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
- Dependency Preservation and Nonadditive Join Decomposition into 3NF Schemas
- Nonadditive Join Decomposition into BCNF Schemas
- Nonadditive Join Decomposition into BCNF Schemas
- Nulls, Dangling Tuples and Alternative Relational Desig
- Problems with NULL Values and Dangling Tuples
- Problems with NULL Values and Dangling Tuples
- Inclusion Dependencies
- Template Dependencies
- Functional Dependencies Based on Arithmetic Functions and Procedures
- Domain-Key Normal Form
- 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
- Why Recovery Is Needed
- Transaction and System Concepts
- Transaction States and Additional Operations
- Transaction States and Additional Operations
- The System Log
- The System Log
- ACID Properties_1
- ACID Properties_2
- ACID Properties_2
- Commit Point of a Transaction
- DBMS Specific Buffer Replacement Policies
- Desirable Properties of Transactions_1
- Desirable Properties of Transactions_1
- Desirable Properties of Transactions_2
- Desirable Properties of Transactions_2
- Characterizing Schedules Based on Recoverability
- Characterizing Schedules Based on Serializability
- Testing Conflict Serializability of a Schedule S-1
- Testing Conflict Serializability of a Schedule S-1
- End Test
- Credit 3 End Test
- Credit 3 End Test
- Transaction Support in SQL_1
- Transaction Support in SQL_2
- Transaction Support in SQL_2
- Introduction to Concurrency Control_1
- Introduction to Concurrency Control_2
- 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_1
- Types of Locks and System Lock Tables_2
- Types of Locks and System Lock Tables_2
- Guaranteeing Serializability by Two-Phase Locking
- 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_1
- Dealing with Deadlock and Starvation_2
- Deadlock Detection_1
- Deadlock Detection_2
- Deadlock Detection_2
- Concurrency Control Based on Timestamp Ordering
- Timestamps
- Timestamps
- Multiversion Concurrency Control Techniques
- Multiversion Technique Based on Timestamp Ordering
- Multiversion Two-Phase Locking Using Certify Locks
- Validation Concurrency Control Techniques
- Validation Concurrency Control Techniques
- Granularity of Data Items and Multiple Granularity Locking
- Recovery Concepts
- Recovery Outline and Categorization of Recovery Algorithms
- Recovery Outline and Categorization of Recovery Algorithms
- Caching (Buffering) of Disk Blocks
- 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_1
- Write-Ahead Logging, Steal/No-Steal and Force/No-Force_2
- Write-Ahead Logging, Steal/No-Steal and Force/No-Force_2
- Checkpoints in the System Log and Fuzzy Checkpointing
- Checkpoints in the System Log and Fuzzy Checkpointing
- Transaction Rollback and Cascading Rollback
- Transaction Rollback and Cascading Rollback
- Transaction Actions That Do Not Affect the Database
- 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
- Credit 4 End Test
Eligibility Criteria
• Basic knowledge of computers and keen desire to build skills in this field.
• Open to students, job seekers, and working professionals.
• Open to students, job seekers, and working professionals.
Official Certification
• Official MKCL KLiC Certificate upon successful completion of the course and evaluations.
Work-Centric Learning Approach
• 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!
• 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!
Ready to start Advanced DBMS Concepts?
Join our upcoming batch at ZICA Kalyani center with certified instructors.
Other MKCL Courses
-
Python
120 hours -
Mobile App Development
120 hours -
Advanced C# Programming Techniques
30 hours -
Advanced Concepts of C#
60 hours