Assignment – Level 1
Use Case: Centralized Healthcare Appointment
Management
1. Use Case Explanation & Assumptions
A multispecialty hospital chain operates across multiple branches. Currently, each branch
manages appointments independently, causing:
● Double bookings
● Missing patient histories
● Inefficiency in doctor schedules
Objective: Create a centralized relational database that manages appointments, prevents
conflicts, tracks resources, and enables reporting.
Assumptions:
● Each doctor belongs to exactly one department, but may work across multiple
branches.
● A patient can book appointments in any branch.
● Each appointment is tied to a specific doctor, patient, branch, and timeslot.
● Doctors cannot be double-booked for the same timeslot.
● Appointments can be canceled or rescheduled.
2. ER Diagram
Entities & Relationships:
● Patient (patient_id, name, contact, DOB, gender, history)
● Doctor (doctor_id, name, specialization, dept_id, branch_id, availability)
● Department (dept_id, dept_name)
● Branch (branch_id, branch_name, location)
● Appointment (appt_id, patient_id, doctor_id, branch_id, slot_id, status,
appt_date)
● Timeslot (slot_id, start_time, end_time, availability)
Relationships:
● Patient ↔ Appointment (1:M)
● Doctor ↔ Appointment (1:M)
● Department ↔ Doctor (1:M)
● Branch ↔ Doctor (1:M)
● Branch ↔ Appointment (1:M)
● Timeslot ↔ Appointment (1:M)
ER DIAGRAM
3. Normalized Relational Schema (3NF)
Patient
● patient_id (PK, INT, AUTO INCREMENT)
● name (VARCHAR(100), NOT NULL)
● dob (DATE, NOT NULL)
● gender (CHAR(1), CHECK(gender IN (‘M’,‘F’,‘O’)))
● contact (VARCHAR(15), UNIQUE, NOT NULL)
● history (TEXT)
Doctor
● doctor_id (PK, INT, AUTO INCREMENT)
● name (VARCHAR(100), NOT NULL)
● specialization (VARCHAR(100))
● dept_id (FK → Department.dept_id)
● branch_id (FK → Branch.branch_id)
● availability (BOOLEAN DEFAULT TRUE)
Department
● dept_id (PK, INT, AUTO INCREMENT)
● dept_name (VARCHAR(100), UNIQUE)
Branch
● branch_id (PK, INT, AUTO INCREMENT)
● branch_name (VARCHAR(100))
● location (VARCHAR(150))
Timeslot
● slot_id (PK, INT, AUTO INCREMENT)
● start_time (TIME)
● end_time (TIME)
● availability (BOOLEAN DEFAULT TRUE)
Appointment
● appt_id (PK, INT, AUTO INCREMENT)
● patient_id (FK → Patient.patient_id)
● doctor_id (FK → Doctor.doctor_id)
● branch_id (FK → Branch.branch_id)
● slot_id (FK → Timeslot.slot_id)
● appt_date (DATE, NOT NULL)
● status (ENUM(‘BOOKED’,
● ‘CANCELLED’, ‘COMPLETED’))
SCHEMA DIAGRAM
4. Booking Flow Logic (Conflict-Free Scheduling)
1. When booking an appointment:
○ Check if doctor is available at given slot_id and appt_date.
○ If already booked → return conflict message.
○ If free → insert record into Appointment.
2. Update [Link] to FALSE once booked.
3. On cancelation, mark status=‘CANCELLED’ and reset
[Link]=TRUE.
4. On reschedule, update slot_id and re-check conflict rules.
5. Key SQL Operations (to be done in Level 2)
● Insert new patient record.
● Add new doctor with department and branch mapping.
● Book appointment with conflict check.
● Cancel/reschedule appointment.
● Generate doctor workload report (appointments per doctor per date).
● Report no-show statistics (status check).
● Query available timeslots per branch/doctor.
6. Platform Compatibility (MySQL vs Oracle SQL)
● Use AUTO INCREMENT in MySQL vs SEQUENCE + TRIGGER in Oracle.
● Use BOOLEAN in MySQL, but in Oracle replace with CHAR(1) or NUMBER(1).
● Use ENUM in MySQL, but in Oracle replace with CHECK(status IN (....))
