RJU IT 302 Database Management

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.
Rajarshi Janak University Faculty of Management
Model Question -2025
Bachelor of Business Administration Course: Data Base Management Full Marks: 60 Course Code: IT 302 Pass Marks: 24 Year/Semester: Second/III Time: 2:30 hours
Candidates are required to give their answers in their own words as far as practicable. The figures in the margin indicate full marks. Group A: Short Answer Questions Attempt all questions [5 × 2 = 10 marks] 1. List any four components of a DBMS. 2. Define entity and attribute with an example. 3. Define relation and tuple in the relational model. 4. What is the difference between WHERE and HAVING clauses in SQL? 5. What is functional dependency? Give an example. Group B: Long Answer Questions Attempt any seven questions [7 × 5 = 35 marks] 6. Describe the key characteristics of DBMS. 7. A retail company is migrating from spreadsheets to a DBMS. Explain how this migration will improve (i) reporting, (ii) stock management, and (iii) sales analysis. 8. Design an ER model for a university database involving Students, Courses, Faculties, and Enrollments. Explain cardinality, participation, and keys used. 9. Explain 1NF, 2NF, and 3NF with suitable examples. 10. Explain modern database approaches such as NoSQL, NewSQL, and cloud databases with examples. 11. What are triggers, procedures, and cursors? Write suitable examples of each in SQL. 12. Explain transaction states, serializability, and recovery techniques used to maintain consistency. 13. Explain the ACID properties of transactions. 14. Briefly explain deadlock and its prevention techniques. Group C: Comprehensive question /Case analysis /Situation analysis question 15. Read the following case and answer the questions that follow: (15 Marks) A growing retail company has been managing its product inventory, sales records, and purchase data using spreadsheets. As the volume of transactions increased, the company faced issues such as inconsistent product codes, duplicate entries, slow reporting, and difficulty in tracking stock levels. Management decided to migrate to a DBMS-based solution to improve stock management, reporting accuracy, and sales analysis. The new system is expected to: - Maintain accurate stock levels - Generate daily/weekly sales reports Reduce duplicate and inconsistent data - Improve decision-making using real-time data - Support multi-user access Questions: a. Identify the limitations of the spreadsheet-based system and explain how DBMS features resolve them. b. Suggest an appropriate ER model for the retail system, describing major entities, attributes, and relationships. c. Recommend suitable SQL operations or features (e.g., constraints, joins, views) that can help improve reporting and stock management. ****The End****