# database1111

**Repository Path**: roxy3203325037/database1111

## Basic Information

- **Project Name**: database1111
- **Description**: No description available
- **Primary Language**: SQL
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-10-14
- **Last Updated**: 2025-10-26

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 🏥 Hospital Management System

🚀 **Database Final Project — Our Completed Implementation**

We developed a fully functional **Hospital Management System** from scratch, designed to manage patients, medical staff, appointments, treatments, medical records, and billing operations.  
We chose the hospital theme and implemented every component with realistic data and complete functionality.

---

## 📑 Table of Contents

- [👥 Our Team](#-our-team)
- [🩺 What We Built](#-what-we-built)
- [🌟 Bonus Features (+10%)](#-bonus-features-10)
- [🧱 Database Design & Implementation (50%)](#-database-design--implementation-50)
- [💻 Application Development (35%)](#-application-development-35)
- [🌐 REST API (10%)](#-rest-api-10)
- [🧾 Documentation (5%)](#-documentation-5)
- [⚙️ Prerequisites](#️-prerequisites)
- [🚀 Installation & Setup](#-installation--setup)
- [🧭 Usage](#-usage)
- [📂 Project Structure](#-project-structure)
- [✨ Summary](#-summary)

---

## 👥 Our Team

| English Name | Chinese Name | Student ID |
|--------------|--------------|------------|
| Leo          | 周逸朗          | 2023905934 |
| Leo          | 吴思远          | 2023905932 |
| Alex         | 牛远兮          | 2023903990 |
| Charles      | 陈韬宇          | 2023900902 |

---
### Document Navigation

---
## 🩺 What We Built

We created a comprehensive hospital management database system featuring:

- A **PostgreSQL** backend with over 10 interrelated tables  
- A **Python** application providing both GUI (Tkinter) and REST API (FastAPI) interfaces  
- Complete **CRUD operations**, **complex SQL queries**, and **data integrity enforcement**  
- Extra features such as authentication, logging, Docker deployment, and testing  

Our system enables administrators and staff to efficiently register patients, manage appointments, view records, handle billing, and analyze hospital operations.

---

## 🌟 Bonus Features (+10%)

We went beyond the base requirements and implemented all optional enhancements:

- 🔐 **Authentication** – Added JWT-based API authentication for secure access  
- 🪵 **Logging** – Implemented centralized logging of requests, operations, and SQL queries (`logs/app.log`)  
- 🐋 **Dockerization** – Fully containerized both the database and application (`docker-compose.yml`)  
- 🧪 **Testing** – Wrote unit tests for CRUD and query logic using `pytest`  

These features enhanced **security**, **debugging**, and **deployment reliability**.

---

## 🧱 Database Design & Implementation (50%)

We designed and implemented a complete ER model with **10+ normalized tables**.

### Entities
- Patients  
- Medical Staff  
- Departments  
- Appointments  
- Medical Records  
- Prescriptions  
- Medications  
- Treatments  
- Invoices  
- Rooms  

### Relationships
- One-to-Many: Department → Medical Staff, Patient → Appointments  
- Many-to-Many: Appointments ↔ Treatments, Prescriptions ↔ Medications  
- Flexible foreign key constraints for realistic design  

We ensured strong data integrity, normalization, and realistic hospital workflows.

### 🧩 Constraints & Advanced SQL

We implemented:

- ✅ **Primary / Foreign Keys**  
- ✅ **5+ CHECK constraints** (e.g., valid ages, appointment dates)  
- ✅ **3+ UNIQUE constraints** (e.g., email, phone, room number)  
- ✅ **DEFAULT** and **NOT NULL** settings  
- ✅ **3+ Triggers** – e.g., auto-update room status, prevent overlapping appointments  
- ✅ **3+ Stored Functions** – for validation and calculations  
- ✅ **Indexes** – for optimized query performance  

We inserted **20+ rows per table** of realistic sample data.

---

## 💻 Application Development (35%)

### 🔹 Technology Stack

- **Language:** Python  
- **Database Access:** `psycopg3` (no ORM used)  
- **Frontend:** Tkinter GUI  
- **Backend:** FastAPI REST API  

### 🔹 CRUD Implementation

We implemented full CRUD functionality for:  
**Patients**, **Appointments**, **Medical Records**, **Prescriptions**, and **Invoices**

Each supports:

- **Create** – Add new records with validation  
- **Read** – Retrieve data by ID, search, or filter  
- **Update** – Safely modify records  
- **Delete** – Cascade or soft-delete with integrity maintained  

All operations used **prepared statements**, **transactions**, and **robust error handling**.

### 🔹 Complex SQL Queries

We built advanced SQL queries to:

- List all appointments with patient, doctor, and department details  
- Calculate total revenue per department  
- Identify doctors with the most appointments  
- Retrieve patients with unpaid invoices  

---

## 🌐 REST API (10%)

We designed and documented a RESTful API with endpoints for key entities.  
Each endpoint returns structured JSON with proper HTTP codes.

### Example Endpoints

```
GET    /api/patients              → Retrieve all patients
POST   /api/patients              → Create a new patient
PUT    /api/patients/{id}         → Update patient details
DELETE /api/patients/{id}         → Remove a patient
```

Additional routes:

```
GET /api/appointments/available?date=2025-10-10  → Query available slots
GET /api/patients/search?name=John              → Filter patients by name
```

Full API documentation is available in:
```
docs/API_documentation.md
```

---

## 🧾 Documentation (5%)

We prepared complete project documentation, including:

- **README.md** (this file) – Setup, structure, and usage instructions  
- **Architecture Diagram** – `docs/architecture.png`  
- **ER Diagram** – `docs/ER_diagram.png`  
- **SQL Scripts:**

```
sql/
├── 01_create_tables.sql
├── 02_insert_sample_data.sql
├── 03_create_constraints.sql
└── 04_create_triggers_functions.sql
```

---

## ⚙️ Prerequisites

```
PostgreSQL 17+
Python 3.10+
Libraries: psycopg3, fastapi, uvicorn, pydantic, tkinter, pytest
Docker (optional for containerized setup)
```

---

## 🚀 Installation & Setup

### 🗄️ Database Setup

```bash
createdb hospital_db
psql -d hospital_db -f sql/01_create_tables.sql
psql -d hospital_db -f sql/02_insert_sample_data.sql
pip install -r requirements.txt
python src/main.py        # Launch GUI

# Or run API:
uvicorn src.api.main:app --reload

# Or with Docker:
docker-compose up -d
```

### 🔗 Access Points

```
Database → localhost:5432
API       → localhost:8000
```

---

## 🧭 Usage

- **GUI:** Manage patients, appointments, and billing via a user-friendly interface  
- **API:** Interact via HTTP endpoints or Swagger UI at:  
  `http://localhost:8000/docs`  

- **Testing:** Run unit tests with:

```bash
pytest tests/
```

---

## 📂 Project Structure

```
├── sql/                               # SQL scripts (table creation, triggers, sample data)
│   ├── 01_create_tables.sql
│   ├── 02_insert_sample_data.sql
│   └── 03_triggers_functions.sql
├── logs/                              # Log files for backend and frontend
│   ├── backend.log
│   └── frontend.log
├── src/
│   ├── db_setup.py                    # Initializes database, executes SQL files, provides CRUD tests
│   ├── run_server.py                  # Launches Flask backend server
│   ├── run_client.py                  # Launches Tkinter frontend client
│   │
│   ├── backend/                       # Flask backend application
│   │   ├── __init__.py                # Flask app factory: register blueprints, configure logging
│   │   ├── models/                    # Data access layer (PostgreSQL interaction)
│   │   │   ├── db.py                 # Database connection and query utilities
│   │   │   └── *model.py             # Models for entities (patients, invoices, medications, staff, etc.)
│   │   ├── api/                       # RESTful API blueprints
│   │   │   ├── admissions.py          # Admission management API
│   │   │   ├── appointments.py        # Appointment management API (create, cancel, query)
│   │   │   ├── assignments.py         # Staff-patient assignment API
│   │   │   ├── departments.py         # Department management API
│   │   │   ├── diseases.py            # Disease information API
│   │   │   ├── invoices.py            # Invoice and billing API
│   │   │   ├── medications.py         # Medication management API
│   │   │   ├── patients.py            # Patient management API (includes invoices and appointments)
│   │   │   ├── prescriptions.py       # Prescription management API
│   │   │   ├── rooms.py               # Room management API
│   │   │   ├── staff.py               # Medical staff management API
│   │   │   └── users.py               # User login and registration API
│   │   └── utils/                     # Backend utility modules
│   │       └── password.py            # Password hashing and verification
│   │
│   ├── frontend/                      # Tkinter desktop client
│   │   ├── main_app.py                # Main application logic (login, navigation, API calls)
│   │   ├── api_client.py              # HTTP client wrapper for backend API requests
│   │   └── views/                     # GUI views for different modules
│   │       ├── patient_view.py        # Patient management view
│   │       ├── staff_view.py          # Staff management view
│   │       ├── appointment_view.py    # Appointment view
│   │       ├── department_view.py     # Department view
│   │       ├── invoice_view.py        # Invoice view
│   │       ├── user_view.py           # User management view
│   │       ├── admission_view.py      # Admission view
│   │       ├── disease_view.py        # Disease view
│   │       └── medication_view.py     # Medication view
│   │
│   └── tools/                         # Maintenance and testing tools
│       ├── migrate_passwords.py       # Password migration/encryption update tool
│       ├── reset_sequences.py         # Resets PostgreSQL sequence IDs after data import
│       ├── health_check.py            # System health check (dependencies, DB, ports)
│       └── unit_test.py               # Automated backend/frontend integration testing
│
└── README.md                          # Project documentation (this file)
```

---

# database1111-master — Docker Usage Instructions

This repository includes Docker support for local development (`Dockerfile`, `docker-compose.yml`, `start.sh`) and two helper scripts for pushing the image to a registry or exporting it as a compressed file for sharing with others.

## Quick Start

1.  **Start the services**:

<!-- end list -->

```bash
cd "/Volumes/WD Blue SN5000 Media/paid/database1111-master/database1111-master5/database1111-master"
docker compose up --build -d
```

2.  **View logs/status**:

<!-- end list -->

```bash
docker compose ps
docker compose logs -f web
```

## Share Image

1.  **Export and compress the image**:

<!-- end list -->

```bash
chmod +x ./scripts/save_image.sh
mkdir -p dist
./scripts/save_image.sh database1111-master-web:latest dist/db1111_web.tar.gz
```

2.  **Send the file** `dist/db1111_web.tar.gz` to the recipient (via scp/cloud drive/USB).

3.  **Recipient loads the image and runs the container**:

<!-- end list -->

```bash
# On the recipient's machine
gunzip -c db1111_web.tar.gz | docker load
# Or directly docker load < db1111_web.tar.gz
docker images
docker run -p 5001:5000 <image:tag>
```
---

## ✨ Summary

Our **Hospital Management System** demonstrates a complete understanding of **relational database design**, **SQL programming**, and **application integration**.  
We delivered a project with both **technical depth** and **real-world usability**, achieving a robust and professional-grade system.

---