🏥 Hospital Management System — Data Mining & Analytics

A complete healthcare data project combining SQL Server database design, data cleaning, exploratory data analysis, Orange Data Mining, and Power BI / HTML dashboards.

📌 Project Overview

This project was developed as part of a Database Systems / Data Mining coursework project by the S.H.E.K.M Group.

The goal was to build a structured Hospital Management System from raw CSV data, identify and resolve data-quality issues, design a relational SQL Server database, perform exploratory data analysis, and present actionable insights through dashboards.

The workflow follows a practical analytics pipeline:

Raw Data → Data Quality Audit → Cleaning & Validation → SQL Database → EDA → Visualization → BI Dashboard

---

🎯 Project Objectives

• Design a relational Hospital Management System database in SQL Server.
• Clean and validate healthcare datasets before database import.
• Identify missing values, duplicates, schema mismatches, and date-format problems.
• Perform exploratory data analysis using Python.
• Explore the dataset using Orange Data Mining.
• Analyze appointments, no-shows, treatment costs, revenue, lab tests, invoices, and prescriptions.
• Build interactive dashboards using Power BI and HTML.
• Document the complete data pipeline and database design.

---

🛠️ Technologies & Tools

Area	Tools
Database	SQL Server 2022, T-SQL, BULK INSERT
Data Analysis	Python, Pandas, NumPy
Visualization	Matplotlib
Data Mining	Orange Data Mining
Business Intelligence	Power BI
Database Modeling	ER Diagram
Documentation	Microsoft Word, PDF, PowerPoint
Data Formats	CSV, XLSX, SQL, HTML, PBIX, PBIT, IPYNB

---

📊 Dataset

The project contains healthcare data covering multiple operational areas:

• Patients
• Doctors
• Departments
• Appointments
• Treatments
• Prescriptions
• Laboratory Tests
• Patient Check-ins
• Billing
• Invoices

Cleaned Dataset Summary

Table	Rows
Departments	10
Patients	496
Doctors	28
Appointments	1,980
Treatments	2,003
Prescriptions	1,683
Lab Tests	665
Patient Check-ins	1,725
Billing	1,950
Invoices	1,650

The final database contains 10 core tables and approximately 16K+ records across the cleaned datasets.

---

🔍 Data Cleaning & Quality Checks

The project compares the raw data with the cleaned data and addresses several issues discovered during validation, including:

• Incorrect / inconsistent row counts.
• Missing values.
• Duplicate records.
• DD/MM/YYYY date-format handling.
• Schema mismatches between CSV files and the initial SQL design.
• Missing columns required by the final database schema.
• Data-type conversion problems during SQL import.

The final SQL implementation uses staging tables and typed final tables to make the import process safer and more reliable.

---

🗄️ Database Design

The database uses Appointments as the central fact table, connected to operational entities such as Treatments, Prescriptions, Lab Tests, Billing, and Patient Check-ins.

The final schema includes:

Departments
     │
     ├── Doctors
     │
     └── Appointments ──┬── Treatments
                        ├── Prescriptions
                        ├── Lab Tests
                        ├── Billing
                        └── Patient Check-ins
     │
     └── Patients

SQL Highlights

• Primary keys and foreign keys.
• CHECK constraints.
• UNIQUE constraints.
• Staging/import tables.
• TRY_CONVERT for safe date conversion.
• BULK INSERT for loading CSV data.
• Referential integrity validation.

The final refined SQL script is available in database/Hospital_Management_System_FinalV3.sql.

---

📈 Exploratory Data Analysis

The Python notebook performs EDA in two major phases:

Before Cleaning

• Row-count validation.
• Missing-value audit.
• Date-format inspection.
• Duplicate checks.
• Schema mismatch detection.

After Cleaning

The analysis explores:

• Patient demographics.
• Appointment trends.
• No-show behavior.
• Treatment costs.
• Revenue by department.
• Laboratory test activity and turnaround.
• Invoice and payment information.
• Prescription / medicine patterns.
• Doctor-level summaries.

Notebook: eda/python/Hospital_EDA.ipynb

---

📊 Key Visualizations

Appointment No-Show Analysis

Revenue Analysis

EDA Summary

Additional EDA figures are available in eda/figures.

---

🟠 Orange Data Mining

The project also includes an Orange workflow for exploratory analysis:

• Workflow file: eda/orange/HMS_EDA.ows
• Supporting screenshots are included in the same folder.

---

📊 Power BI & HTML Dashboards

The repository includes the Power BI dashboard files:

• HMS_Dashboard.pbix
• HMS_Dashboard.pbit

An HTML dashboard is also included:

• HMS_Dashboard.html

Note: PBIX/PBIT files are intended to be opened with Power BI Desktop. The HTML dashboard can be opened directly in a browser.

---

🧩 ER Diagram

The ER diagram files are available under er-diagram.

They document the relationships between the Hospital Management System entities and help explain the relational database structure.

---

📁 Repository Structure

hospital-management-data-mining/
│
├── README.md
├── requirements.txt
├── .gitignore
│
├── data/
│   ├── raw/
│   └── cleaned/
│
├── database/
│   └── Hospital_Management_System_FinalV3.sql
│
├── eda/
│   ├── python/
│   │   └── Hospital_EDA.ipynb
│   ├── orange/
│   │   ├── HMS_EDA.ows
│   │   └── screenshots
│   └── figures/
│
├── dashboards/
│   ├── powerbi/
│   │   ├── HMS_Dashboard.pbix
│   │   └── HMS_Dashboard.pbit
│   └── html/
│       └── HMS_Dashboard.html
│
├── er-diagram/
│
├── reports/
│   ├── HMS_Project_Report.pdf
│   └── HMS_Project_Report.docx
│
├── presentation/
│   └── HMS_Final_Presentation.pptx
│
└── assets/

---

🚀 How to Run the Project

1. Clone the repository

git clone https://github.com/mohamedkarem20/hospital-management-data-mining.git
cd hospital-management-data-mining

2. Python EDA

Create a virtual environment and install the required packages:

python -m venv .venv

Windows:

.venv\Scripts\activate

Linux / macOS:

source .venv/bin/activate

Install dependencies:

pip install -r requirements.txt

Launch Jupyter:

jupyter notebook

Then open:

eda/python/Hospital_EDA.ipynb

3. SQL Server

Open:

database/Hospital_Management_System_FinalV3.sql

in SQL Server Management Studio (SSMS) and execute it in an environment where the required CSV import paths can be configured.

The SQL script recreates the Hospital_Management_System database, so review the script before running it on an existing database.

4. Power BI

Open dashboards/powerbi/HMS_Dashboard.pbix using Power BI Desktop.

5. HTML Dashboard

Open:

dashboards/html/HMS_Dashboard.html

in a modern web browser.

---

👥 Team

S.H.E.K.M Group

• Mohamed Seleem — Team Leader & Database Architect
• Hazem Khaled — Data Engineer
• Mohamed Essam — Data Analyst
• Mohamed Kamal — BI Developer
• Mohamed Karem — Documentation Lead

---

📚 Project Documentation

For detailed methodology, database design, data-quality issues, and project evaluation, see:

• HMS_Project_Report.pdf
• HMS_Project_Report.docx
• HMS_Final_Presentation.pptx

---

💡 Skills Demonstrated

This project demonstrates practical experience in:

• Data Cleaning & Validation
• Exploratory Data Analysis (EDA)
• SQL Server & Relational Database Design
• T-SQL
• Data Import & Staging
• Data Visualization
• Power BI Dashboard Development
• Data Mining with Orange
• Healthcare Data Analysis
• Data Quality Auditing
• Technical Documentation

---

📄 License

This repository is intended primarily as an educational and portfolio project. Please contact the repository owner before reusing project-specific materials or datasets for commercial purposes.

---

⭐ If you find this project useful, feel free to explore the notebooks, SQL implementation, and dashboards.