🏥 Hospital Database System

Complete relational database design for a hospital management system.
Includes schema design, 50+ rows sample data, complex queries, stored procedures, views, and a Python CLI.

---

📋 Table of Contents

• Database Schema
• ER Diagram
• SQL Features
• Project Structure
• How to Run
• Key Learnings

---

🗄️ Database Schema

8 tables with proper relationships and constraints:

Table	Rows	Description
departments	6	Hospital departments (Cardiology, Neurology, etc.)
doctors	8	Doctor profiles with specialization and salary
patients	10	Patient records with medical info
rooms	8	Room inventory (General, Private, ICU, Emergency)
staff	8	Non-doctor hospital staff
appointments	10	Patient-doctor appointment scheduling
prescriptions	8	Medications prescribed per appointment
bills	6	Patient billing and payment tracking

---

📐 ER Diagram

See ER_diagram.md for the full Mermaid entity-relationship diagram.

---

🔍 SQL Features Demonstrated

Feature	File
CREATE TABLE with constraints	schema/01_create_tables.sql
50+ rows INSERT data	schema/02_sample_data.sql
Performance indexes	schema/03_indexes.sql
SELECT, INSERT, UPDATE, DELETE	queries/basic_queries.sql
INNER, LEFT, RIGHT JOIN	queries/joins.sql
GROUP BY, HAVING, subqueries	queries/aggregations.sql
3 stored procedures	queries/stored_procedures.sql
3 database views	queries/views.sql

---

📁 Project Structure

hospital-database-sql/
├── schema/
│   ├── 01_create_tables.sql
│   ├── 02_sample_data.sql
│   └── 03_indexes.sql
├── queries/
│   ├── basic_queries.sql
│   ├── joins.sql
│   ├── aggregations.sql
│   ├── stored_procedures.sql
│   └── views.sql
├── python_interface/
│   ├── db_connect.py
│   ├── queries.py
│   └── main.py
├── ER_diagram.md
├── requirements.txt
└── README.md

---

🚀 How to Run

SQL Only (MySQL)

mysql -u root -p < schema/01_create_tables.sql
mysql -u root -p hospital_db < schema/02_sample_data.sql
mysql -u root -p hospital_db < queries/joins.sql

Python CLI

pip install -r requirements.txt
python python_interface/main.py

---

💡 Key Learnings

1. Normalization reduces data redundancy — each table has a single purpose
2. Foreign keys enforce referential integrity between related tables
3. Indexes dramatically speed up queries on large tables
4. JOINs are essential for combining data across normalized tables
5. Stored procedures encapsulate complex business logic in the database
6. Views simplify common queries and provide a layer of abstraction

---

📄 License

MIT License

Built by Imran Ali — MUET Jamshoro, CS 2026