Hospital Management System — CTEs & SQL Window Functions

Course: C11665 - DPR400210: Database Programming Instructor: Eric Maniraguha Assignment: Group Assignment — CTEs & SQL Window Functions Project Database Engine: MySQL 8.0+

1. Business Problem

Our hospital's front desk, finance office, and administration currently rely on manual spreadsheets to track patient visits, doctor workload, and billing. This makes it hard to answer basic operational questions quickly, for example:

Which departments generate the most revenue?
Which doctors are the top performers, and how does that vary across departments?
How is a patient's billing history trending over time?
What is the hospital's management/reporting structure?

This project models a Hospital Management System with a relational database and uses Common Table Expressions (CTEs) and Window Functions to answer these questions directly in SQL.

2. Database Schema

The database consists of 5 related tables:

Table	Description	Primary Key	Foreign Keys
`departments`	Hospital departments (Cardiology, Neurology, etc.)	`department_id`	—
`doctors`	Doctors, including a self-referencing supervisor relationship	`doctor_id`	`department_id` → departments, `supervisor_id` → doctors
`patients`	Registered patients	`patient_id`	—
`appointments`	Patient visits to a doctor	`appointment_id`	`patient_id` → patients, `doctor_id` → doctors, `department_id` → departments
`billing`	Charges generated per appointment	`bill_id`	`appointment_id` → appointments

Full DDL and sample data: 01_schema_and_data.sql

3. ER Diagram

erDiagram
    DEPARTMENTS ||--o{ DOCTORS : employs
    DEPARTMENTS ||--o{ APPOINTMENTS : hosts
    DOCTORS ||--o{ DOCTORS : supervises
    DOCTORS ||--o{ APPOINTMENTS : conducts
    PATIENTS ||--o{ APPOINTMENTS : books
    APPOINTMENTS ||--o| BILLING : generates

    DEPARTMENTS {
        int department_id PK
        string department_name
        string location
    }
    DOCTORS {
        int doctor_id PK
        string first_name
        string last_name
        string specialization
        int department_id FK
        int supervisor_id FK
        decimal salary
        date hire_date
    }
    PATIENTS {
        int patient_id PK
        string first_name
        string last_name
        date date_of_birth
        string gender
        string phone
    }
    APPOINTMENTS {
        int appointment_id PK
        int patient_id FK
        int doctor_id FK
        int department_id FK
        date appointment_date
        string status
    }
    BILLING {
        int bill_id PK
        int appointment_id FK
        decimal amount
        string payment_status
        date bill_date
    }

(GitHub renders this Mermaid diagram automatically. If you also need a static image, export it via the Mermaid Live Editor and add it to a /screenshots folder.)

4. CTE Implementations

File: 02_cte_queries.sql

#	Type	Business Question	Business Value
A1	Simple CTE	Which patients had a completed visit and what were they billed?	Quick, readable visit list without repeating JOIN logic
A2	Multiple CTEs	Which department earns the most revenue relative to its staffing?	Supports resource-allocation decisions
A3	Recursive CTE	What is the full doctor reporting hierarchy (org chart)?	Visualizes management structure for HR
A4	CTE with Aggregation	What is the total/average billing generated per doctor?	Supports performance reviews
A5	CTE + JOINs	Full patient visit history across doctor, department, billing	Single audit trail for billing inquiries

Screenshots — CTE Results

A1. Simple CTE — Completed visits with billing

A2. Multiple CTEs — Department revenue vs. staffing

A3. Recursive CTE — Doctor reporting hierarchy

A4. CTE with Aggregation — Billing summary per doctor

A5. CTE combined with JOINs — Full patient visit history

5. Window Function Implementations

File: 03_window_functions.sql

#	Category	Functions Used	Business Question
B1	Ranking	`ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `PERCENT_RANK()`	Rank doctors by revenue, overall and within department
B2	Aggregate	`SUM() OVER()`, `AVG() OVER()`, `MIN() OVER()`, `MAX() OVER()`	Running revenue total and department benchmarks per bill
B3	Navigation	`LAG()`, `LEAD()`	Compare each patient's bill to their previous/next bill
B4	Distribution	`NTILE()`, `CUME_DIST()`	Split doctors into performance quartiles

Screenshots — Window Function Results

B1. Ranking Functions — Doctor revenue rank (overall & by department)

B2. Aggregate Window Functions — Running total & department benchmarks

B3. Navigation Functions — Previous/next bill per patient

B4. Distribution Functions — Doctor performance quartiles

6. Analysis and Findings

Descriptive Analysis — What happened? Cardiology generated the highest total department revenue, followed by Orthopedics and Neurology. Individually, orthopedic surgeon Claude Mugisha generated the most revenue of any doctor, placing him in the top performance quartile.

Diagnostic Analysis — Why did it happen? Cardiology has the largest number of doctors (3) and the most completed appointments, which naturally increases its total billed revenue. At the individual level, Claude Mugisha's high average bill amount (rather than volume of visits alone) drove his ranking, suggesting orthopedic procedures carry higher average charges than routine consultations in other departments.

Prescriptive Analysis — What actions should be taken?

Consider adding another doctor to Pediatrics and Neurology, which have fewer completed visits, to balance patient load across departments.
Investigate the Pending payment-status bills identified in the CTE and window function queries and follow up on collections.
Use the quartile ranking from NTILE(4) as an input to a fair, data-driven performance/bonus review process rather than relying on anecdotal impressions of doctor performance.

7. How to Run

mysql -u root -p < 01_schema_and_data.sql
mysql -u root -p hospital_management < 02_cte_queries.sql
mysql -u root -p hospital_management < 03_window_functions.sql

All scripts were tested end-to-end on MySQL-compatible syntax (MariaDB 10.11) and execute without errors.

8. References

MySQL 8.0 Reference Manual — Window Functions
MySQL 8.0 Reference Manual — WITH (Common Table Expressions)
Course lecture materials, C11665 - DPR400210: Database Programming

9. Academic Integrity Statement

This work was produced by our group specifically for this assignment. Where external references were consulted (see References above), the queries and schema themselves are our own design and were not copied from classmates or online repositories, in accordance with UNILAK's academic integrity policy.

Repository Structure

database_programming_assignment1_[studentID]_[firstname]/
├── 01_schema_and_data.sql
├── 02_cte_queries.sql
├── 03_window_functions.sql
├── README.md
└── screenshots/
    ├── er_diagram.png
    ├── a1_simple_cte.png
    ├── a2_multiple_ctes.png
    ├── a3_recursive_cte.png
    ├── a4_cte_aggregation.png
    ├── a5_cte_joins.png
    ├── b1_ranking_functions.png
    ├── b2_aggregate_functions.png
    ├── b3_navigation_functions.png
    └── b4_distribution_functions.png

FilesExpand file tree

README.md

Latest commit

History