Due Date Is Over
Due Date: 2026-01-30
University Student Information System
Scenario
A university wants to digitize its student records, including personal details, course enrollment, attendance, and examination results.
Tasks
Identify the entities, attributes, and relationships.
Draw an ER Diagram.
Convert the ER diagram into relational tables.
Apply normalization up to 3NF.
Write SQL queries for:
Student-wise result report
Course-wise student count
Students with attendance below 75%. CO1, APP
Due Date Is Over
Due Date: 2026-01-30
Hospital Management System
Scenario
A hospital manages patients, doctors, appointments, treatments, and billing through a centralized database.
Tasks
Design the database schema.
Explain views of data (Doctor view, Patient view, Admin view).
Identify integrity constraints.
Write SQL queries to:
Find patients treated by a specific doctor
Generate daily billing reports
List patients admitted for more than 5 days CO1, APP
Due Date Is Over
Due Date: 2026-01-30
Online Shopping Application
An e-commerce company stores customer data, products, orders, payments, and delivery details.
Tasks
Create an ER diagram showing relationships.
Explain how DBMS reduces data redundancy compared to file systems.
Normalize the order-related tables.
Write SQL queries for:
Top-selling products
Customers with maximum purchases
Orders not yet delivered CO1, APP
Due Date Is Over
Due Date: 2026-01-30
Library Management System
Scenario
A college library wants to automate book issue, return, fine calculation, and member management.
Tasks
Identify entities and relationships.
Design tables with primary and foreign keys.
Write SQL queries to:
Find overdue books
Calculate fine for delayed returns
Display books issued by a specific student
Explain transaction management during book issue/return. CO1, APP
Due Date Is Over
Due Date: 2026-01-30
Banking System
Scenario
A bank maintains customer accounts, transactions, loans, and branch information.
Tasks
Design the database schema.
Explain the role of ACID properties in transaction processing.
Write SQL queries to:
Display account balance
Find customers with loans above a given amount
Generate monthly transaction statements
Discuss security and authorization mechanisms in DBMS. CO1, APP
Due Date Is Over
Due Date: 2026-01-30
Railway Reservation System
Scenario
The railway system handles train schedules, seat availability, bookings, cancellations, and passenger details.
Tasks
Draw ER diagram.
Explain concurrency issues and how DBMS handles them.
Write SQL queries for:
Available seats for a train
Passenger list for a specific journey date
Cancelled ticket history
Discuss the importance of backup and recovery. CO1, APP
Due Date Is Over
Due Date: 2026-01-30
Social Media Platform
Scenario
A social media app stores user profiles, posts, likes, comments, and followers.
Tasks
Identify many-to-many relationships.
Design relational tables.
Write SQL queries to:
Find most active users
Retrieve posts with maximum likes
List followers of a user
Explain how indexes improve query performance. CO1, APP
