Advanced Scenario-Based Questions for
Database Design and Normalization
1. Advanced Library Management System
• **Scenario:**
You are tasked with designing a comprehensive Library Management System.
The system must manage books, members, borrowings, staff, and reservations.
• **Requirements:**
• Books can have multiple authors and editions.
• Each member can borrow up to 5 books at a time.
• Track borrowing history with dates, due dates, return dates, and overdue fines.
• Members can reserve books; if a book is returned, it's allocated to the next
reservation.
• Staff manage borrowings, returns, fines, and reservations.
• Include login credentials and roles (Admin, Librarian, Member).
• **Tasks:**
• Draw an extended ER Diagram with weak entities, multi-valued attributes, and role-
based relationships.
• Convert to a normalized relational schema (3NF).
• Identify candidate keys and foreign keys.
2. School/University Management System with Exams
• **Scenario:**
Build a system for a university to manage students, courses, instructors, enrollments,
exams, and grades.
• **Requirements:**
• Each student belongs to a department and enrolls in multiple courses.
• Each course can have multiple instructors (e.g., theory and lab).
• The system must handle midterms, finals, quizzes, and assignments separately.
• Grades must be weighted (e.g., 30% midterm, 50% final, 20% assignments).
• Include attendance tracking and student warnings for low attendance.
• Each instructor logs into the system with credentials.
• **Tasks:**
• Design a detailed ER Diagram.
• Build a normalized schema to 3NF.
3. Hospital Management System with Billing and History
• **Scenario:**
Design a robust Hospital Management System for a general hospital.
• **Requirements:**
• Patients, Doctors, Appointments, and Medical Records as before.
• Add support for:
- Room Assignments: Patients can be admitted and assigned rooms (with room
type and charges).
• - Billing System: Each bill includes consultation, lab tests, medicines, and room
charges.
• - Discharge Summary: With diagnosis, procedures, follow-ups.
• - Staff roles: Admin, Nurse, Receptionist, Doctor.
• **Tasks:**
• Draw a comprehensive ER Diagram.
• Convert it to a 3NF relational schema.
• Define relationships among staff, patients, and departments.
• SQL queries:
- Generate a bill for a patient.
- List doctors with the most number of admitted patients.
4. Online Shopping & Inventory Management System
• **Scenario:**
Build a full-featured E-Commerce Platform database.
• **Requirements:**
• Customers can place multiple orders.
• Each order has order date, status, shipping details, and payment method.
• Products belong to categories and are supplied by vendors.
• Track inventory with stock levels and re-order thresholds.
• Add a product rating/review system.
• Support multiple payment modes and order cancellation/refund logic.
• Admin users can manage products, categories, and vendors.
• **Tasks:**
• Create an ER diagram with complex relationships (many-to-many with attributes).
• Normalize to 3NF.
• Include integrity constraints (e.g., quantity >= 0).
