Track
Software Development
Duration
120 hours
Skill Level
Foundation
Language
English
About this Course
This course provides a comprehensive introduction to database management systems (DBMS), covering both theoretical concepts and practical skills. …
Learning Mode: Learn at ALC or at Home
Detailed Course Curriculum
Hands-on module breakdown aligned with MKCL production standards and industry requirements.
- Introduction
- History of DBMS
- Purpose of Database Systems
- Advantages of using the DBMS approach
- Disadvantages of using the DBMS approach
- DBMS and its applications: Enterprise Information & Banking and Finance
- Example of a Database
- Architecture of DBMS
- Data Models, Schemas and Instances
- Categories of Data Models
- Database Schema vs Database State
- Data-Manipulation Language (DML)
- Data-Definition Language (DDL)
- Database Administrators and Database Users
- Database Users and User Interfaces
- The Database System environment
- Database System Utilities
- Centralized and Client/Server Architecture for DBMS
- High-Level Conceptual Data Models
- Introduction to ER Model
- Symbols in ER Diagram
- Entity types
- Entity sets
- Attributes
- Entity-Set and Keys
- Relationship
- Relational Model Integrity
- CONSTRAINTS
- Keys in DBMS
- ER DIAGRAM
- Naming Conventions
- ER Design Issues
- Structural constraints Weak entity types
- Generalization
- Specialization
- Relational Model Concepts
- Informal Definitions
- Formal Definitions
- Structure of Relational Databases
- Database Schema
- Characteristics of Relations
- Relational Integrity Constraints
- Other Types of Data Constraints
- Domains, Attributes, Tuples and Relations
- Represent all the Entities and Relationships in Tabular Fashion
- PRELIMINARIES
- Selection and Projection
- Unary Operations
- The SELECT Operation
- PROJECT Operation
- Sequences of Operations and the RENAME Operation with an Example
- Set Theory Operations
- The UNION, INTERSECTION and MINUS Operations - Examples
- The CARTESIAN PRODUCT (CROSS PRODUCT) Operation
- Binary Relational Operations and additional relational operations
- List of Operators with Purpose
- Additional Relational Operations and Generalized Projection
- Relational Database Design using ER-to-Relational mapping
- Procedure to Create a Relational Schema from an Entity-Relationship (ER)
- Mapping of Binary 1: N Relationship Types
- Introduction
- SQL Data Definition and commands
- Operators and Expressions
- MYSQL Installation - Demo
- Schema and Catalog Concepts in SQL
- The CREATE TABLE Command in SQL
- Attribute Data Types and Domains in SQL
- Specifying Attribute Constraints and Attribute Defaults
- Basic Retrieval Queries in SQL
- Ambiguous Attribute Names, Aliasing, Renaming and Tuple Variables
- Tables as Sets in SQL
- Order of Query Execution
- Ordering of Query Results
- Error Codes
- INSERT, DELETE, and UPDATE Statements in SQL
- Additional Features of SQL
- Create a Table Called Employee with the 2-Column Table Structure
- Create Department Table with the 2-Column Table Structure
- Queries Using DDL and DML
- Design SQL Queries for Suitable Database Application
- Using SQL DML Statements
- Comparisons Involving NULL and Three-Valued Logic
- Nested Queries, Tuples and Set or Multi-Set Comparisons
- Correlated Nested Queries
- Functions
- The EXISTS and UNIQUE Functions in SQL
- Explicit Sets and Renaming of Attributes in SQL
- Join - Introduction
- Cross Join
- INNER Join
- SELF Join
- LEFT OUTER Join
- RIGHT OUTER Join and FULL OUTER Join
- Join - Examples
- Join - Alternate Syntax
- Multi Way Join
- Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View, Index, Sequence
- Example of a simple query on one relation- Retrieve the birth date and address of the employee
- Consider the schema for a Library Database: Write SQL queries to Retrieve details of all books in the library
- Consider the schema for an Order Database of Salesman’s: Create a view that finds the salesman who has the customer with the highest order of a day.
- Querying (using ANY, ALL, IN, Exists, NOT EXISTS, UNION, INTERSECT, Constraints etc.) - for railway ticketing
- Using NESTING OF QUERIES- Retrieve the name and address of all employees who work for the ‘Research’ department.
- Design at least 10 SQL queries for suitable database application using SQL DML statements: all types of Join, Sub-Query and View.
- Aggregate Functions in SQL
- Order by Clause
- Grouping
- Specifying General Constraints as Assertions in SQL
- Perform Query Using Aggregate Function
- Write a PL/SQL Block of code
- Introduction to Triggers in SQL_1
- Assertions vs Triggers
- Views in SQL
- The DROP Command
- The ALTER Command
- List Data Definition Languages (DDL) Commands of Base Table and View
- List Data Manipulation Language (DML) Of Base Tables and Views
- SQL Query Using Grouping Functions
- Insert, Delete and Update Triggers
- Database Trigger on Library Table
- Accessing Databases from Applications
- Cursors
- Introduction to JDBC
- 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
- SQL in Java (SQLJ)
- Stored procedures
- SQL/PSM
- Write a SQL block
- Introduction and Objectives to DB Design
- Informal Design Guidelines for Relation Schema
- Redundant Information in Tuples and Update Anomalies
- Generation of Spurious Tuples
- Functional Dependencies - Definition and Diagrammatic Notations
- Normal Forms based on Primary Keys
- Boyce-Codd Normal Form
- Multivalued Dependency and Fourth Normal Form
- Join Dependencies and Fifth Normal Form
- Inference Rules, Equivalence and Minimal Cover
- Inference Rules for Functional Dependencies
- Equivalence of Sets of Functional Dependencies
- Properties of Relational Decompositions
- Algorithms for Relational Database Schema Design
- Nulls, Dangling Tuples and Alternative Relational Designs
- Inclusion and Template Dependencies
- Functional Dependencies Based on Arithmetic Functions and Procedures
- Domain-Key Normal Form
- Features and Types of Transaction Processing
- Single-User vs Multiuser Systems
- Transactions, Database Items, Read and Write Operations and DBMS Buffers
- Transaction and System Concepts
- ACID Properties
- Commit Point of a Transaction
- DBMS Specific Buffer Replacement Policies
- Characterizing Schedules Based on Recoverability & Serializability
- Transaction Support in SQL
- Introduction to Concurrency Control
- Two-Phase Locking Techniques for Concurrency Control
- Variations of Two-Phase Locking
- Dealing with Deadlock and Starvation
- Deadlock Detection
- Concurrency Control Based on Timestamp Ordering
- Multiversion Concurrency Control Techniques
- Granularity of Data Items and Multiple Granularity Locking
- Recovery Concepts
- NO - UNDO or REDO Recovery Based on Deferred Update
- Recovery Techniques Based on Immediate Update
- Shadow Paging
- Database Backup and Recovery from Catastrophic Failures
- Working of Key-Value Databases
- Features and Functions of Key-Value Databases
- Schema Design to Support Key Value
- Key - Value Database Vs Cache
- The Property Graph Model
- Why Data Relationships Matter
- Why Other NoSQL Databases don’t Fix the Problem Either
- Relational vs Graph Data Modelling Match-Up
- Example Data Model - Fraud Detection in Email Communications
- Database Query Language
- 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
- Advantages of Sharding
- Shared - Nothing Architecture
- Shared - Disk Architecture
- Sharding
- Sharded Cluster Components
- Considerations Before Sharding
- Sharded and Non-Sharded Collections and Connecting to a Sharded Cluster
- Introduction, Benefits of Business Intelligence Tools
- Best data analytics and BI tools
- Microsoft Power BI
- Tableau
- Qlik Sense
- Sisense
- Best BI tool for developing and deploying analytics apps
- Looker – data analytics and BI tool for start-ups and mid-sized businesses
- Oracle
- Domo BI
- SAS Analytics
- 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
- MongoDB Statistics
- CRUD Operations in MongoDB
- Data Modelling
- Some Considerations While Designing Schema in MongoDB
- Create Database
- Drop Database
- Create Collection
- Data Types
- Insert Document
- Query Document
- Update Document
- Delete Document
- Design and Develop MongoDB Queries using CRUD operations
- Projection
- Limiting Records
- Sorting Records
- Aggregation - Examples
- Authentication Mechanisms in MongoDB
- Bulk Operations with Examples
- Indexing
- Replication
- Program to Implement Aggregation and Indexing in MongoDB
- Create Backup of Sharded Cluster
- SQL vs Mongo DB Entities
- Consistency of Data
- Query through API
- Exercise on Student Database Agenda
- MongoDB – Deployment
- Typical MongoDB Deployment, Read Preference and Write Concern_1
- Operation Performance and Partition
- Relationships
- Database References
- List of Drivers Supported by DBRefs
- MongoDB - Map Reduce
- Using MongoDB as a Service: mLab
- Execute the Query and Study its Execution Plan
- Careers in Database Technology
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 Database Management System (DBMS)?
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