Database Management System (DBMS)
Module 1: Introduction to Databases
- Definition of Data, Information, Database, DBMS
- Advantages and disadvantages of DBMS over file systems
- Data Independence and Data Abstraction – Physical, Logical, View
- Data Abstraction and Views Explained with Examples
- DBMS Architecture – 1-tier, 2-tier, 3-tier
- Data Models – Hierarchical, Network, Relational, Object-oriented
SQL Exercise
SQL Exercise
Database Languages –
SQL Exercise
SQL Exercise
Module 2: Entity–Relationship (ER) Model
ER Diagrams and EER Concepts –Keys:
SQL Exercise
Exercise : Schema and Cardinalities
Module 3: Relational Model & Relational Algebra
- Relational Data Model – Schema, Tuples, Attributes, Domains
- Integrity Constraints – Domain, Key, Entity, Referential
- Relational Algebra Operations – Selection, Projection, Join, Union, Difference
- Extended Operations – Division, Aggregation
SQL Exercise
Module 4: Structured Query Language (SQL)
Joins
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- CROSS JOIN
Advanced SQL Concepts
- Subqueries
- Views
Aggregate Functions
- COUNT
- SUM
- AVG
- MIN
- MAX
Data Grouping and Filtering
- GROUP BY
- HAVING
Sorting Results
- ORDER BY
Data Control Language (DCL)
- GRANT
- REVOKE
Module 5: Normalization and Database Design
- Functional Dependencies
- 1NF, 2NF, 3NF, BCNF, 4NF, 5NF
- Decomposition – Lossless Join and Dependency Preservation
- Denormalization – Concepts and Trade-offs
Module 6: Transaction Management & Concurrency Control
- Concept and Properties of Transactions (ACID)
- Transaction States and Schedules
- Serializability – Conflict and View
- Lock-based and Timestamp-based Concurrency Control
- Deadlock Handling
Module 7: Recovery System
- Failure Types – Transaction, System, Media
- Recovery Techniques – Deferred and Immediate Updates
- Checkpoint, Shadow Paging, Log-based Recovery
Module 8: File Organization & Indexing
- Sequential, Indexed, and Hashed File Organization
- Indexing – Primary, Secondary, Dense, Sparse
- B-Tree, B+ Tree, and Hash-based Indexing
Module 9: Database Security
- Database Security Issues – Authentication, Authorization
- Access Control – DAC, MAC, RBAC
- Encryption and Data Privacy
Module 10: Advanced Topics
- Distributed Databases and Data Warehousing
- Data Mining and OLAP Concepts
- NoSQL and Cloud Databases
- Object-relational Databases
DBMS Lab Syllabus
- Using MySQL / Oracle / PostgreSQL
- Creating Databases, Tables, and Schemas
- Insert, Update, Delete, and Retrieve Data
- Join, Subquery, Aggregate Function Implementation
- Views, Index, Trigger, and Stored Procedure Creation
- Transaction Control – COMMIT, ROLLBACK
- Normalization and Mini Project Implementation
Recommended Books
- Silberschatz, Korth & Sudarshan – Database System Concepts
- Raghu Ramakrishnan & Gehrke – Database Management Systems
- Elmasri & Navathe – Fundamentals of Database Systems
- Groff & Weinberg – SQL: The Complete Reference