Database Management System
The Database Management System (DBMS) syllabus from the Maharashtra Knowledge Corporation Limited (MKCL) is distributed across specific structured course modules under their KLiC (Knowledge Lit Careers) program.
The detailed syllabus is divided into foundational, intermediate, and advanced modules:
1. Basics of DBMS & Fundamentals
- Introduction: History, purpose, and examples of database systems.
- Architecture: DBMS architecture, schemas, instances, and database states.
- Data Models: Categories of data models, Relational model, Entity-Relationship (ER) model, Object-based, and Semi-structured data models.
- Languages & Roles: Data-Definition Language (DDL), Data-Manipulation Language (DML), and roles of Database Administrators (DBA) and users.
- Pros & Cons: Evaluation of using the DBMS approach across banking, finance, and enterprise systems.
2. Database Design & Relational Mastery
- ER Diagrams: High-level conceptual data models, ER symbols, entity types/sets, attributes, structural constraints, weak entity types, generalization, and specialization.
- Keys & Constraints: Relational model integrity, primary keys, foreign keys, candidate keys, and naming conventions.
- Relationship Mapping: Mapping of binary 1:N, M:N relationship types, multivalued attributes, and N-ary relationship types.
- Relational Schema: Cardinality, anomalies, Codd Rules, logical views of data, tuples, and characteristics of good design.
- Relational Algebra: Selection, projection, unary operations, sequences of operations, RENAME operation, and set theory operations (UNION, INTERSECTION, MINUS, CARTESIAN PRODUCT).
3. SQL Commands & Queries
- SQL Essentials: Introduction to SQL, data types, operators, and expressions.
- Installation: MySQL environment installation and setup demo.
- DDL Commands: Schema and catalog concepts,
CREATE TABLE, attribute constraints, default values, ALTER, and DROP commands.
- DML Commands: Data manipulation execution via
INSERT, DELETE, and UPDATE statements.
- Data Retrieval: Basic retrieval queries, handling ambiguous names, aliasing, renaming, and tuple variables.
- Query Execution: Tables as sets, order of execution, ordering results, and dealing with error codes.
4. Advanced DBMS Concepts
- Aggregations & Programming: Complex SQL queries utilizing built-in aggregate functions, and introductory PL/SQL blocks of code.
- Database Objects: Data abstraction, Views in SQL, and introduction to Triggers versus Assertions.
- Data Integrity: Normalization rules, transaction control, and locking mechanics.
- Concurrency Control: Two-phase locking techniques, variations of locking, dealing with deadlock/starvation, deadlock detection, timestamp ordering, and multiversion concurrency control.
- Recovery & Backup: Recovery concepts, deferred update (NO-UNDO/REDO), immediate update recovery, shadow paging, and handling catastrophic failures.
- Security: Security policies and user authorization protocols in SQL.
