🏥 Hospital Database Management System

📊 Project Overview

This project showcases database design and SQL query optimization for healthcare data management. Built with SQLite, it includes patient management, appointment scheduling, billing systems, and treatment tracking with proper normalization and referential integrity.

🗄️ Database Schema

Entity-Relationship Diagram

Database Structure

Complete database structure showing all tables and columns

💡 SQL Queries & Analysis

Query 1: Doctor Workload Analysis

Business Question:

Which doctors have the highest patient loads and revenue generation for capacity planning?

SELECT d.DoctorID, d.FirstName || ' ' || d.LastName AS DoctorName, d.Specialty, dept.DepartmentName, COUNT(DISTINCT p.PatientID) AS TotalPatients, COUNT(DISTINCT a.AppointmentID) AS TotalAppointments, ROUND(AVG(b.TotalAmount), 2) AS AvgBilling, SUM(b.TotalAmount) AS TotalRevenue FROM doctors d LEFT JOIN departments dept ON d.DepartmentID = dept.DepartmentID LEFT JOIN patients p ON d.DoctorID = p.DoctorID LEFT JOIN appointments a ON d.DoctorID = a.DoctorID LEFT JOIN billing b ON a.AppointmentID = b.AppointmentID GROUP BY d.DoctorID, d.FirstName, d.LastName, d.Specialty, dept.DepartmentName ORDER BY TotalRevenue DESC;

Multi-table JOINs Aggregate Functions GROUP BY NULL Handling

Key Insight:

Dr. Williams (Orthopedics) generates the highest revenue ($41,200) with surgical procedures, while Dr. Johnson (Pediatrics) maintains the highest patient volume with lower per-visit costs, revealing different specialization models.

Query 2: Patient Status Distribution

Business Question:

What's the distribution of patient health statuses and their appointment completion rates?

SELECT p.PatientStatus, COUNT(DISTINCT p.PatientID) AS NumPatients, ROUND(COUNT(DISTINCT p.PatientID) * 100.0 / SUM(COUNT(DISTINCT p.PatientID)) OVER (), 2) AS PercentageOfTotal, COUNT(DISTINCT a.AppointmentID) AS TotalAppointments, SUM(CASE WHEN a.AppointmentStatus = 'Completed' THEN 1 ELSE 0 END) AS CompletedAppointments FROM patients p LEFT JOIN appointments a ON p.PatientID = a.PatientID GROUP BY p.PatientStatus ORDER BY NumPatients DESC;

Window Functions CASE Statements Conditional Aggregation Percentage Calculations

Key Insight:

Critical patients show 100% appointment completion, while Stable patients and Recovering Patients have 94.12% and 93.75% completion rates, respectively, suggesting higher engagement when health stakes are perceived as higher.

Query 3: Department Financial Performance

Business Question:

Which departments should receive investment based on revenue performance and collection efficiency?

WITH DepartmentMetrics AS ( SELECT dept.DepartmentName, COUNT(DISTINCT d.DoctorID) AS NumDoctors, SUM(b.TotalAmount) AS TotalRevenue, ROUND(AVG(b.TotalAmount), 2) AS AvgRevenuePerAppointment, SUM(CASE WHEN b.PaymentStatus = 'Paid' THEN b.TotalAmount ELSE 0 END) AS CollectedRevenue FROM departments dept LEFT JOIN doctors d ON dept.DepartmentID = d.DepartmentID LEFT JOIN appointments a ON d.DoctorID = a.DoctorID LEFT JOIN billing b ON a.AppointmentID = b.AppointmentID GROUP BY dept.DepartmentName ) SELECT *, ROUND(CollectedRevenue * 100.0 / NULLIF(TotalRevenue, 0), 2) AS CollectionRate FROM DepartmentMetrics WHERE TotalRevenue > 0 ORDER BY TotalRevenue DESC;

CTEs Complex Aggregations Financial Metrics HAVING Clause

Key Insight:

Orthopedics leads with $12,600 revenue and a 100% collection rate, while Oncology shows $20,600 revenue but only a 66.5% collection rate, indicating a need for improved billing processes.

Query 4: High-Value Patient Analysis

Business Question:

Who are the highest-spending patients and what treatments drive their costs?

SELECT p.PatientID, p.FirstName || ' ' || p.LastName AS PatientName, p.PatientStatus, d.Specialty, COUNT(DISTINCT a.AppointmentID) AS TotalAppointments, COUNT(DISTINCT t.TreatmentID) AS TotalTreatments, SUM(b.TotalAmount) AS TotalSpending, ROUND(AVG(b.TotalAmount), 2) AS AvgSpendingPerVisit FROM patients p LEFT JOIN doctors d ON p.DoctorID = d.DoctorID LEFT JOIN appointments a ON p.PatientID = a.PatientID LEFT JOIN treatments t ON a.AppointmentID = t.AppointmentID LEFT JOIN billing b ON a.AppointmentID = b.AppointmentID GROUP BY p.PatientID, p.FirstName, p.LastName, p.PatientStatus, d.Specialty HAVING SUM(b.TotalAmount) > 0 ORDER BY TotalSpending DESC LIMIT 15;

6-Table JOINs HAVING Clause TOP N Analysis Patient Analytics

Key Insight:

The Top patient in Oncology spent $19,400 over 4 appointments with chemotherapy, while an Orthopedic patient spent $12,050 on surgical procedures, revealing different care models and financial planning needs.

🛠️ Technical Implementation

SQL Techniques Demonstrated

Database Design Principles

• Normalization: All tables follow 3rd Normal Form (3NF) to eliminate data redundancy
• Referential Integrity: Foreign key constraints ensure data consistency across tables
• Data Types: Optimized column types (VARCHAR, INT, DATE, DECIMAL) for efficiency
• Indexing: Primary keys on all tables for query performance
• Constraints: NOT NULL and UNIQUE constraints where appropriate

Technology Stack

• Database: SQLite 3.x (portable, no server required)
• Tools: DB Browser for SQLite
• Data Format: CSV files for easy portability
• Version Control: Git & GitHub

💼 Business Applications

This database supports critical healthcare operations including:

🔗 Related Projects