Rajarshi Janak University IT 302 DBMS
Course Contents
Unit 1: Introduction to DBMS & Data Models LH 5
- Definition, advantages, disadvantages, applications and purpose of DBMS
- History and evolution of database systems
- Characteristics of DBMS:
* Self-describing nature
* Insulation between programs and data
* Support for multiple views
* Multi-user concurrency control
* Data integrity and security
* Backup and recovery
* Reduced data redundancy
* Data independence (logical & physical)
- DBMS vs. File System
- DBMS components: DBMS engine, query processor, transaction manager, storage manager
- Data models: hierarchical, network, relational, object-oriented, entity-relationship
- Introduction to modern approaches: NoSQL, NewSQL, Datawarehouse, cloud databases
Case Study: Retail Company migrating from spreadsheets to DBMS – impact on reporting, stock management, and sales analysis.
Unit 2: Entity–Relationship (ER) Modeling LH 5
- Entities and entity sets
- Attributes: simple, composite, derived, multi-valued
- Keys: primary, candidate, superkey, foreign key, alternate key, composite key
- Relationships: types, cardinality, participation
- ER diagrams: symbols, conventions, constraints
- Weak entities, aggregation, generalization, specialization
- Enhanced ERD concepts: hierarchies, constraints, business rules
Case Study: University database – modeling students, courses, faculties, and enrollment relationships using advanced ERD concepts.
Unit 3: Relational Model & Relational Algebra LH 5
- Relational schema, tuples, domains, relations
- Constraints: primary key, foreign key, unique, check, NOT NULL
- Relational algebra: selection, projection, union, intersection, set difference, Cartesian product, division, join (inner, outer, natural)
- Aggregate functions: COUNT, SUM, AVG, MAX, MIN
- Relational calculus (overview)
Case Study: Employee database – department-wise salary aggregation and report generation using relational algebra operations.
Unit 4: SQL – Data Definition & Manipulation LH 12
- SQL overview, syntax, and command types (DDL, DML, DCL, TCL)
- DDL: CREATE, ALTER, DROP, TRUNCATE
- DML: SELECT, INSERT, UPDATE, DELETE
- Constraints in SQL
- Joins: inner, outer, cross, self
- Aggregate functions, GROUP BY, HAVING, subqueries
- Views and indexes
- Data types, NULL handling, string operations
- User-defined functions
- Transaction commands: COMMIT, ROLLBACK, SAVEPOINT
Case Study: Banking system – SQL queries for account balances, transactions, interest computation, and using views for customer reporting.
Unit 5: Advanced SQL & Procedural Extensions LH 6
- Nested and correlated subqueries
- Set operations: UNION, INTERSECT, EXCEPT
- Triggers: BEFORE, AFTER INSERT/UPDATE/DELETE
- Stored procedures and functions
- Cursors: declaration, opening, fetching, closing
- Error handling in SQL procedures
- Transaction management and control
Case Study: E-commerce platform – implementing triggers to automatically update inventory and notify stock managers.
Unit 6: Normalization & Database Design LH 6
- Redundancy, anomalies, and functional dependencies
- Normalization: 1NF, 2NF, 3NF, BCNF, 4NF
- Multivalued dependency, join dependency
- Denormalization and trade-offs for performance
- Database design methodology: conceptual, logical, and physical design
- Case-based design examples for business systems
Case Study: Inventory management system – converting unnormalized product and supplier data into 3NF and BCNF.
Unit 7: Transaction Management & Concurrency Control LH 5
- Transaction concept and ACID properties
- Transaction states and schedules
- Serializability, conflict and view serializability
- Locking protocols: shared, exclusive, two-phase locking
- Deadlock prevention and detection
- Concurrency control techniques: optimistic, pessimistic
- Recovery techniques overview
Case Study: Banking transactions – ensuring consistency during simultaneous ATM withdrawals and online transfers.
Unit 8: Recovery, Storage & Query Optimization LH 5
- Database recovery: logging, checkpoints, undo/redo
- Storage structures: files, pages, records, B-trees, hashing
- Query processing: parsing, optimization, execution plans
- Indexing: clustered, non-clustered, composite, bitmap
- Introduction to data warehousing and business intelligence
Case Study: Telecom billing system – fast query retrieval of customer call logs using indexing and query optimization techniques
Practical / Lab Work - Installation and configuration of DBMS (MySQL/PostgreSQL) - ER modeling and relational schema creation - SQL exercises: DDL, DML, joins, subqueries, triggers, views, stored procedures - Normalization exercises - Transaction and concurrency simulations - Mini projects: student database, inventory system, library management system, ecommerce order database
Suggested Readings: Alexis Leon & Mathews Leon. Database Management System. Leon Press. Date, C. J. An Introduction to Database Systems.Pearson. Elmasri, R. & Navathe, S.B. Fundamentals of Database Systems. Pearson India. Ramakrishnan, R. & Gehrke, J. Database Management Systems. McGraw-Hill. Silberschatz, A., Korth, H. F., & Sudarshan, S. Database System Concepts. McGraw-Hill.
Practical / Lab Work - Installation and configuration of DBMS (MySQL/PostgreSQL) - ER modeling and relational schema creation - SQL exercises: DDL, DML, joins, subqueries, triggers, views, stored procedures - Normalization exercises - Transaction and concurrency simulations - Mini projects: student database, inventory system, library management system, ecommerce order database
Suggested Readings: Alexis Leon & Mathews Leon. Database Management System. Leon Press. Date, C. J. An Introduction to Database Systems.Pearson. Elmasri, R. & Navathe, S.B. Fundamentals of Database Systems. Pearson India. Ramakrishnan, R. & Gehrke, J. Database Management Systems. McGraw-Hill. Silberschatz, A., Korth, H. F., & Sudarshan, S. Database System Concepts. McGraw-Hill.
Rajarshi Janak University IT 302 DBMS
New Online SQL compiler
Unit 1: Introduction to DBMS &Data Models
New Online SQL compiler
Unit 1: Introduction to DBMS &Data Models
Rajarshi Janak University
