# hospital-management-keep moving

**Repository Path**: maruzi-yan/hospital-management-keep-moving

## Basic Information

- **Project Name**: hospital-management-keep moving
- **Description**: This is a final project 
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-10-16
- **Last Updated**: 2025-10-24

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# Hospital-Management-keep moving

## Team Members

- student 1：Mark | 许正琦 | 2023905418
- student 2：wren |  何唯  | 2023905416
- student 3：Tom  | 靳泽宇 | 2023901563
- student 4：Kale | 王泉智 | 2023905335

### 1. Database Design Files

#### `01_create_tables.sql` - Core Table Structure Definition

**Function:** Creates complete hospital management system database table structures

**Detailed Table Description:**

**Departments Table**

sql

```
- department_id: Department ID (auto-increment primary key)
- name: Department name (unique constraint, e.g., "Cardiology")
- description: Department description
```



**Purpose:** Store hospital department information, provide basic data for staff allocation

**Medical_Staff Table**

sql

```
- staff_id: Staff ID (auto-increment primary key)
- name: Full name (Chinese-English format: Dr. Li Wei | 李伟)
- specialization: Professional field (e.g., "Cardiologist")
- department_id: Department ID (foreign key)
- contact: Contact information
- email: Email (unique constraint)
```



**Purpose:** Manage doctors, nurses, and other medical personnel information, support department-based queries

**Patients Table**

sql

```
- patient_id: Patient ID (auto-increment primary key)
- name: Patient name (Chinese-English format)
- dob: Date of birth (CHECK constraint ensures past date)
- contact: Contact number
- email: Email (unique constraint)
- medical_history: Medical history records
```



**Purpose:** Store patient basic information and personal medical history

**Rooms Table**

sql

```
- room_id: Room ID (auto-increment primary key)
- number: Room number (unique constraint)
- type: Room type (Single/Double/ICU/Ward)
- status: Status (available/occupied/maintenance)
- capacity: Capacity (CHECK constraint > 0)
```



**Purpose:** Manage hospital room resources, support queries by type and status

**Appointments Table**

sql

```
- appointment_id: Appointment ID (auto-increment primary key)
- patient_id: Patient ID (foreign key, cascade delete)
- staff_id: Medical staff ID (foreign key, restrict delete)
- room_id: Room ID (foreign key, set null delete)
- date: Appointment date (CHECK constraint for future dates)
- time: Appointment time
- status: Status (scheduled/completed/cancelled)
```



**Purpose:** Core business table connecting patients, doctors, and rooms, managing appointment workflow

**Medical_Records Table**

sql

```
- record_id: Medical record ID (auto-increment primary key)
- appointment_id: Appointment ID (foreign key, cascade delete)
- diagnosis: Diagnosis result (NOT NULL)
- symptoms: Symptom description
- observations: Observation records
```



**Purpose:** Record medical information for each visit, associated with appointments

**Treatments Table**

sql

```
- treatment_id: Treatment ID (auto-increment primary key)
- name: Treatment plan name (unique constraint)
- description: Plan description
- cost: Cost (CHECK constraint > 0)
```



**Purpose:** Store standard treatment plans and cost information

**Appointment_Treatments Association Table**

sql

```
- appointment_id: Appointment ID (composite primary key, foreign key)
- treatment_id: Treatment ID (composite primary key, foreign key)
```



**Purpose:** Many-to-many relationship table, recording treatments used in each appointment

**Prescriptions Table**

sql

```
- prescription_id: Prescription ID (auto-increment primary key)
- appointment_id: Appointment ID (foreign key, cascade delete)
- notes: Medication instructions
```



**Purpose:** Manage prescription information issued by doctors

**Medications Table**

sql

```
- medication_id: Medication ID (auto-increment primary key)
- name: Medication name (unique constraint)
- dosage: Dosage specification
- stock: Stock quantity (CHECK constraint >= 0)
```



**Purpose:** Medication inventory management, supports stock alerts

**Prescription_Medications Association Table**

sql

```
- prescription_id: Prescription ID (composite primary key, foreign key)
- medication_id: Medication ID (composite primary key, foreign key)
- quantity: Quantity (CHECK constraint > 0)
```



**Purpose:** Many-to-many relationship table, recording specific medications and quantities in prescriptions

**Invoices Table**

sql

```
- invoice_id: Invoice ID (auto-increment primary key)
- appointment_id: Appointment ID (foreign key, cascade delete)
- amount: Amount (CHECK constraint > 0)
- status: Status (unpaid/paid)
- date: Invoice date (default current date)
```



**Purpose:** Financial management, recording costs and payment status

#### `02_insert_sample_data.sql` - Test Data Population

**Function:** Insert complete test data to support system demonstration and testing

**Data Characteristics:**

- **Realism:** Uses real Chinese-English names and medical terminology
- **Completeness:** All tables have interconnected test data
- **Business Logic:** Data conforms to actual business scenarios
- **Diversity:** Covers various room types, treatment plans, medication types

**Example Data Snippet:**

sql

```
-- Department data (20 real medical departments)
('Cardiology', 'Heart diseases'),
('Pediatrics', 'Child care'),

-- Medical staff data (corresponding to departments)
('Dr. Li Wei | 李伟', 'Cardiologist', 1, '123-4567890', 'liwei@hospital.com'),

-- Patient data (covering all age groups)
('Li Ming | 李明', '1985-05-15', '111-2223334', 'liming@patient.com', 'No major history'),

-- Complete business chain data
Appointments → Medical_Records → Prescriptions → Invoices
```



#### `03_create_constraints.sql` - Advanced Constraint Definitions

**Function:** Add additional data integrity constraints

**Key Constraints:**

sql

```
-- Prevent doctor scheduling conflicts
ALTER TABLE Appointments ADD UNIQUE (staff_id, date, time);

-- Basic email format validation
ALTER TABLE Patients ADD CHECK (LENGTH(email) > 5);

-- Room status enumeration constraint
ALTER TABLE Rooms ADD CHECK (status IN ('available', 'occupied', 'maintenance'));
```



#### `04_create_triggers_functions.sql` - Advanced Database Functions

**Function:** Implement stored procedures, functions, and triggers

**Stored Function Examples:**

sql

```
-- Calculate department revenue
CREATE OR REPLACE FUNCTION calculate_department_revenue(dept_id INTEGER) 
RETURNS DECIMAL AS $$
-- Used for financial statistics and reporting

-- Check doctor availability
CREATE OR REPLACE FUNCTION check_doctor_availability(doc_id INTEGER, app_date DATE, app_time TIME) 
RETURNS BOOLEAN AS $$
-- Core business logic, prevents appointment conflicts

-- Update medication stock
CREATE OR REPLACE FUNCTION update_medication_stock(med_id INTEGER, qty INTEGER) 
RETURNS VOID AS $$
-- Inventory management, supports automatic deduction when prescriptions are issued
```



**Trigger Examples:**

sql

```
-- Automatically update room status
CREATE TRIGGER update_room_status
AFTER UPDATE ON Appointments
FOR EACH ROW EXECUTE FUNCTION trigger_update_room_status();

-- Appointment validation
CREATE TRIGGER validate_appointment
BEFORE INSERT ON Appointments
FOR EACH ROW EXECUTE FUNCTION trigger_validate_appointment();

-- Automatic stock update
CREATE TRIGGER update_stock_after_prescription
AFTER INSERT ON Prescription_Medications
FOR EACH ROW EXECUTE FUNCTION trigger_update_stock();
```



### 2. Application Code Files

#### `connection.py` - Database Connection Management

**Function:** Encapsulate PostgreSQL database connection and operations

**Core Methods:**

python

```
def get_connection():
    # Establish database connection with error handling
    # Configure database parameters: database name, user, password, host, port

def execute_query(query, params=None, fetch=False, fetchone=False):
    # Unified interface for SQL query execution
    # Supports parameterized queries to prevent SQL injection
    # Provides transaction management and exception handling
    # Supports different return types: fetchall/fetchone/no return
```



#### `crud.py` - Business Logic Implementation

**Function:** Implement CRUD operations for all data tables

**Modular Design:**

- **Patient Management Module:** Complete patient lifecycle management
- **Appointment Management Module:** Appointment system with business rule validation
- **Medical Records Module:** Medical record creation and maintenance
- **Prescription Management Module:** Medication issuance and inventory management
- **Invoice Management Module:** Financial management and payment status tracking

**Advanced Features:**

python

```
# Input validation system
def validate_email(email):  # RFC standard email validation
def validate_date(date_str):  # Date format and logical validation

# Business rule validation
def create_appointment():  # Doctor availability, room status, patient existence validation

# Transaction support
# Use database transactions to ensure data consistency
```



#### `queries.py` - Complex Query Module

**Function:** Implement business analysis and reporting functions

**Core Queries:**

python

```
def list_appointments_with_details():
    # Multi-table join query: patient + doctor + department + appointment information
    # Used for front desk display and doctor workstation

def calculate_revenue_per_department():
    # Aggregate query: revenue statistics by department
    # Supports financial analysis and performance evaluation

def find_busy_doctors(x=5):
    # Use CTE (Common Table Expressions) to query busy doctors
    # Supports human resource management and scheduling optimization
```



#### `gui.py` - Graphical User Interface

**Function:** Provide complete desktop application interface

**Interface Architecture:**

python

```
class HospitalGUI:
    # Main interface management class
    def __init__(self):  # Initialize menu and main frame
    def clear_frame():   # Dynamic interface management
    def refresh_table(): # Real-time data refresh
```



**Menu System:**

- **File Menu:** System-level operations (exit)
- **Entities Menu:** Data management (patients, appointments, medical records, prescriptions, invoices)
- **Queries Menu:** Data analysis functions

**Functional Module Interfaces:**

- **Patient Management Interface:** Search, create, update, delete patients
- **Appointment Management Interface:** Time selection, doctor selection, conflict detection
- **Data Table Component:** Universal data display and operations
- **Form Validation System:** Real-time input validation and error prompts

#### `main.py` - Application Entry Point

**Function:** Program startup entry point

python

```
# Create Tkinter root window
# Initialize HospitalGUI application
# Start main event loop
```



### 3. Configuration and Test Files

#### `requirements.txt` - Dependency Management

text

```
psycopg[binary]==3.2.3
# Specify PostgreSQL database driver version
```



#### `test_connection.py` - Connection Testing

**Function:** Verify database connection and basic functionality

python

```
# Test if database connection is normal
# Verify basic query functionality
# Provide system deployment verification tool
```



## 🔄 System Workflow

### 1. Complete Patient Visit Process

text

```
1. Patient Registration → Create record in Patients table
2. Appointment Booking → Create record in Appointments table (validate doctor availability)
3. Visit Recording → Record diagnosis information in Medical_Records table
4. Treatment Plan → Associate treatments in Appointment_Treatments
5. Medication Prescription → Record medications in Prescriptions + Prescription_Medications
6. Payment Settlement → Generate invoice in Invoices table
7. Stock Update → Automatically deduct stock in Medications table (trigger)
```



### 2. Data Consistency Assurance

- **Foreign Key Constraints:** Maintain referential integrity between tables
- **Transaction Processing:** Ensure atomicity of complex operations
- **Triggers:** Automatically maintain business rules
- **Check Constraints:** Data validity verification

## 🚀 System Special Features

### 1. Intelligent Appointment System

- Automatic doctor time conflict detection
- Real-time room status management
- Appointment status tracking (scheduled/completed/cancelled)

### 2. Complete Medical Record Chain

- Complete tracking from appointment to diagnosis to prescription
- Support historical medical record queries and analysis
- Medical data correlation queries

### 3. Stock Alert Mechanism

- Automatic medication inventory management
- Real-time deduction when prescriptions are issued
- Low stock alert support

### 4. Financial Management System

- Automatic invoice generation
- Department revenue statistics
- Payment status tracking

## 📈 Expansion Capabilities

### Immediately Expandable Functions

1. **Permission Management System:** Role-based access control
2. **Report Generation System:** Data export and statistical analysis
3. **Notification System:** Email/SMS reminders
4. **Web Service Interface:** REST API support for mobile clients

### Long-term Expansion Directions

1. **Big Data Analysis:** Patient behavior analysis and medical quality assessment
2. **AI-assisted Diagnosis:** Integrate machine learning models
3. **IoT Integration:** Medical device data access
4. **Cloud Platform Deployment:** Support multi-hospital collaboration

 # 4.Relational Schema Documentation for Hospital Management System

## 1. Overview
This document describes the relational schema for the Hospital Management System database. The schema is implemented in PostgreSQL and includes 12 tables to manage patients, medical staff, departments, appointments, medical records, treatments, prescriptions, medications, invoices, and rooms. The design ensures data integrity through primary keys, foreign keys, check constraints, unique constraints, indexes, stored functions, and triggers. All tables use SERIAL for auto-incrementing primary keys.

## 2. Table Schemas
Each table is described with its fields, data types, constraints, and notes.

### 2.1 Departments
- **Purpose**: Stores hospital departments.
- **Fields**:
  - `department_id`: SERIAL (PRIMARY KEY) - Auto-incrementing department ID.
  - `name`: VARCHAR(100) (UNIQUE, NOT NULL) - Department name (e.g., 'Cardiology').
  - `description`: TEXT - Department description.
- **Constraints**:
  - PRIMARY KEY on `department_id`.
  - UNIQUE on `name`.
- **Notes**: Sample data includes 20 departments like 'Cardiology', 'Pediatrics'.

### 2.2 Medical_Staff
- **Purpose**: Stores medical staff information.
- **Fields**:
  - `staff_id`: SERIAL (PRIMARY KEY) - Auto-incrementing staff ID.
  - `name`: VARCHAR(100) (NOT NULL) - Staff name (e.g., 'Dr. Li Wei | 李伟').
  - `specialization`: VARCHAR(100) - Staff specialization (e.g., 'Cardiologist').
  - `department_id`: INTEGER (FOREIGN KEY REFERENCES Departments(department_id) ON DELETE CASCADE) - Reference to department.
  - `contact`: VARCHAR(50) - Contact information (e.g., '123-4567890').
  - `email`: VARCHAR(100) (UNIQUE, NOT NULL) - Email address (e.g., 'liwei@hospital.com').
- **Constraints**:
  - PRIMARY KEY on `staff_id`.
  - UNIQUE on `email`.
  - FOREIGN KEY on `department_id` with CASCADE delete.
- **Notes**: Sample data includes 20 staff members linked to departments.

### 2.3 Patients
- **Purpose**: Stores patient information.
- **Fields**:
  - `patient_id`: SERIAL (PRIMARY KEY) - Auto-incrementing patient ID.
  - `name`: VARCHAR(100) (NOT NULL) - Patient name (e.g., 'Li Ming | 李明').
  - `dob`: DATE (NOT NULL, CHECK (dob < CURRENT_DATE)) - Date of birth.
  - `contact`: VARCHAR(50) - Contact information (e.g., '111-2223334').
  - `email`: VARCHAR(100) (UNIQUE, NOT NULL, CHECK (LENGTH(email) > 5)) - Email address (e.g., 'liming@patient.com').
  - `medical_history`: TEXT - Medical history description.
- **Constraints**:
  - PRIMARY KEY on `patient_id`.
  - UNIQUE on `email`.
  - CHECK on `dob` (must be before current date).
  - CHECK on `email` (length > 5).
- **Notes**: Sample data includes 20 patients with realistic details.

### 2.4 Rooms
- **Purpose**: Stores hospital room information.
- **Fields**:
  - `room_id`: SERIAL (PRIMARY KEY) - Auto-incrementing room ID.
  - `number`: VARCHAR(50) (UNIQUE, NOT NULL) - Room number (e.g., '101').
  - `type`: VARCHAR(50) - Room type (e.g., 'Single').
  - `status`: VARCHAR(20) (DEFAULT 'available', CHECK (status IN ('available', 'occupied', 'maintenance'))) - Room status.
  - `capacity`: INTEGER (NOT NULL, CHECK (capacity > 0)) - Room capacity.
- **Constraints**:
  - PRIMARY KEY on `room_id`.
  - UNIQUE on `number`.
  - CHECK on `capacity` (> 0).
  - CHECK on `status` (restricted values).
- **Notes**: Sample data includes 20 rooms.

### 2.5 Appointments
- **Purpose**: Stores appointment schedules.
- **Fields**:
  - `appointment_id`: SERIAL (PRIMARY KEY) - Auto-incrementing appointment ID.
  - `patient_id`: INTEGER (FOREIGN KEY REFERENCES Patients(patient_id) ON DELETE CASCADE) - Reference to patient.
  - `staff_id`: INTEGER (FOREIGN KEY REFERENCES Medical_Staff(staff_id) ON DELETE RESTRICT) - Reference to staff.
  - `room_id`: INTEGER (FOREIGN KEY REFERENCES Rooms(room_id) ON DELETE SET NULL) - Reference to room.
  - `date`: DATE (NOT NULL, CHECK (date >= CURRENT_DATE)) - Appointment date.
  - `time`: TIME (NOT NULL) - Appointment time.
  - `status`: VARCHAR(20) (DEFAULT 'scheduled', NOT NULL) - Appointment status.
- **Constraints**:
  - PRIMARY KEY on `appointment_id`.
  - FOREIGN KEY on `patient_id` with CASCADE delete.
  - FOREIGN KEY on `staff_id` with RESTRICT delete.
  - FOREIGN KEY on `room_id` with SET NULL delete.
  - CHECK on `date` (>= current date).
  - UNIQUE on (staff_id, date, time) to prevent doctor overlap.
- **Notes**: Sample data includes 20 appointments. Trigger validates doctor availability.

### 2.6 Medical_Records
- **Purpose**: Stores medical records for appointments.
- **Fields**:
  - `record_id`: SERIAL (PRIMARY KEY) - Auto-incrementing record ID.
  - `appointment_id`: INTEGER (FOREIGN KEY REFERENCES Appointments(appointment_id) ON DELETE CASCADE) - Reference to appointment.
  - `diagnosis`: TEXT (NOT NULL) - Diagnosis description.
  - `symptoms`: TEXT - Symptoms description.
  - `observations`: TEXT - Observations.
- **Constraints**:
  - PRIMARY KEY on `record_id`.
  - FOREIGN KEY on `appointment_id` with CASCADE delete.
- **Notes**: Sample data includes 20 records linked to appointments.

### 2.7 Treatments
- **Purpose**: Stores treatment types.
- **Fields**:
  - `treatment_id`: SERIAL (PRIMARY KEY) - Auto-incrementing treatment ID.
  - `name`: VARCHAR(100) (UNIQUE, NOT NULL) - Treatment name (e.g., 'Basic Checkup').
  - `description`: TEXT - Treatment description.
  - `cost`: DECIMAL(10,2) (NOT NULL, CHECK (cost > 0)) - Cost.
- **Constraints**:
  - PRIMARY KEY on `treatment_id`.
  - UNIQUE on `name`.
  - CHECK on `cost` (> 0).
- **Notes**: Sample data includes 20 treatments.

### 2.8 Appointment_Treatments
- **Purpose**: Junction table for many-to-many relationship between appointments and treatments.
- **Fields**:
  - `appointment_id`: INTEGER (FOREIGN KEY REFERENCES Appointments(appointment_id) ON DELETE CASCADE) - Reference to appointment.
  - `treatment_id`: INTEGER (FOREIGN KEY REFERENCES Treatments(treatment_id) ON DELETE RESTRICT) - Reference to treatment.
- **Constraints**:
  - PRIMARY KEY on (appointment_id, treatment_id).
  - FOREIGN KEY on `appointment_id` with CASCADE delete.
  - FOREIGN KEY on `treatment_id` with RESTRICT delete.
- **Notes**: Sample data includes 20+ links.

### 2.9 Prescriptions
- **Purpose**: Stores prescriptions for appointments.
- **Fields**:
  - `prescription_id`: SERIAL (PRIMARY KEY) - Auto-incrementing prescription ID.
  - `appointment_id`: INTEGER (FOREIGN KEY REFERENCES Appointments(appointment_id) ON DELETE CASCADE) - Reference to appointment.
  - `notes`: TEXT - Prescription notes.
- **Constraints**:
  - PRIMARY KEY on `prescription_id`.
  - FOREIGN KEY on `appointment_id` with CASCADE delete.
- **Notes**: Sample data includes 20 prescriptions.

### 2.10 Medications
- **Purpose**: Stores medication inventory.
- **Fields**:
  - `medication_id`: SERIAL (PRIMARY KEY) - Auto-incrementing medication ID.
  - `name`: VARCHAR(100) (UNIQUE, NOT NULL) - Medication name (e.g., 'Aspirin').
  - `dosage`: VARCHAR(50) - Dosage information.
  - `stock`: INTEGER (NOT NULL, DEFAULT 0, CHECK (stock >= 0)) - Stock quantity.
- **Constraints**:
  - PRIMARY KEY on `medication_id`.
  - UNIQUE on `name`.
  - CHECK on `stock` (>= 0).
- **Notes**: Sample data includes 20 medications.

### 2.11 Prescription_Medications
- **Purpose**: Junction table for many-to-many relationship between prescriptions and medications.
- **Fields**:
  - `prescription_id`: INTEGER (FOREIGN KEY REFERENCES Prescriptions(prescription_id) ON DELETE CASCADE) - Reference to prescription.
  - `medication_id`: INTEGER (FOREIGN KEY REFERENCES Medications(medication_id) ON DELETE RESTRICT) - Reference to medication.
  - `quantity`: INTEGER (NOT NULL, CHECK (quantity > 0)) - Quantity prescribed.
- **Constraints**:
  - PRIMARY KEY on (prescription_id, medication_id).
  - FOREIGN KEY on `prescription_id` with CASCADE delete.
  - FOREIGN KEY on `medication_id` with RESTRICT delete.
  - CHECK on `quantity` (> 0).
- **Notes**: Sample data includes 20+ links. Trigger updates stock after insert.

### 2.12 Invoices
- **Purpose**: Stores billing information for appointments.
- **Fields**:
  - `invoice_id`: SERIAL (PRIMARY KEY) - Auto-incrementing invoice ID.
  - `appointment_id`: INTEGER (FOREIGN KEY REFERENCES Appointments(appointment_id) ON DELETE CASCADE) - Reference to appointment.
  - `amount`: DECIMAL(10,2) (NOT NULL, CHECK (amount > 0)) - Invoice amount.
  - `status`: VARCHAR(20) (DEFAULT 'unpaid', NOT NULL) - Invoice status.
  - `date`: DATE (DEFAULT CURRENT_DATE) - Invoice date.
- **Constraints**:
  - PRIMARY KEY on `invoice_id`.
  - FOREIGN KEY on `appointment_id` with CASCADE delete.
  - CHECK on `amount` (> 0).
- **Notes**: Sample data includes 20 invoices.

## 3. Relationships
- **Departments 1:N Medical_Staff**: One department has many staff (foreign key in Medical_Staff).
- **Patients 1:N Appointments**: One patient has many appointments (foreign key in Appointments, CASCADE delete).
- **Medical_Staff 1:N Appointments**: One staff has many appointments (foreign key in Appointments, RESTRICT delete).
- **Rooms 1:N Appointments**: One room has many appointments (foreign key in Appointments, SET NULL delete).
- **Appointments 1:N Medical_Records**: One appointment has many records (foreign key in Medical_Records, CASCADE delete).
- **Appointments 1:N Prescriptions**: One appointment has many prescriptions (foreign key in Prescriptions, CASCADE delete).
- **Appointments 1:N Invoices**: One appointment has many invoices (foreign key in Invoices, CASCADE delete).
- **Appointments N:M Treatments**: Many-to-many via Appointment_Treatments (CASCADE/RESTRICT delete).
- **Prescriptions N:M Medications**: Many-to-many via Prescription_Medications (CASCADE/RESTRICT delete).

## 4. Indexes
- `idx_patient_name` ON Patients(name): For fast patient name searches.
- `idx_appointment_date` ON Appointments(date): For date-based queries.
- `idx_staff_department` ON Medical_Staff(department_id): For department-based staff searches.

## 5. Stored Functions
- `calculate_department_revenue(dept_id INTEGER) RETURNS DECIMAL`: Calculates total paid revenue for a department.
- `check_doctor_availability(doc_id INTEGER, app_date DATE, app_time TIME) RETURNS BOOLEAN`: Checks if a doctor is available at the given time.
- `update_medication_stock(med_id INTEGER, qty INTEGER) RETURNS VOID`: Updates medication stock and raises exception if insufficient.

## 6. Triggers
- `update_room_status` AFTER UPDATE ON Appointments: Updates room status based on appointment status (available or occupied).
- `validate_appointment` BEFORE INSERT ON Appointments: Validates doctor availability using stored function.
- `update_stock_after_prescription` AFTER INSERT ON Prescription_Medications: Updates medication stock using stored function.

## 7. Sample Data
- The database is populated with at least 20 records per main table for testing (e.g., 20 departments, 20 patients, 20 appointments).

## 8. Notes
- The schema ensures data integrity and efficiency for hospital operations.
- All queries in the application use parameterized statements to prevent SQL injection.
- This schema can be extended for additional features like user authentication.
# 5.API Implementation
The system includes a REST API using FastAPI for external access to CRUD operations on key entities (Patients, Appointments, Medical Records, Prescriptions) and complex queries (e.g., department revenue). No ORM is used; raw SQL via psycopg ensures direct database interaction.

### Running the API
1. Install dependencies: `pip install -r requirements.txt`
2. Start the server: `cd src/api; uvicorn app:app --reload`
3. Access interactive docs: http://localhost:8000/docs (test endpoints with forms)
4. Full endpoint details, examples, and status codes: [doc/api_documentation.md](doc/api_documentation.md)

### Key Features
- **CRUD Support**: Full create/read/update/delete for 4 entities with validation (e.g., email/date checks).
- **Filtering**: Search parameters (e.g., ?search=Li for patients, ?by_date=2025-10-22 for appointments).
- **Error Handling**: HTTP codes (200 OK, 201 Created, 400 Bad Request, 404 Not Found, 500 Error).
- **Complex Query**: GET /api/revenue for aggregated department revenue.

Example cURL: `curl http://localhost:8000/api/patients` (lists all patients in JSON).

# 6.Hospital Management System - Setup Guide  

## Prerequisites  
Ensure the following tools/libraries are installed before starting:  
- **pgAdmin**: With a running PostgreSQL server (default port: 5432).  
- **Python 3.x**: Installed on your local machine.  
- **Python Dependencies**: Install required packages via pip:  
  ```bash
  pip install psycopg2-binary tkinter
  ```  
  (Note: `tkinter` is usually included with standard Python installations; install separately if missing.)  


## 1. Database Setup in pgAdmin  
Follow these steps to create the `hospital-management` database and import SQL files:  

1. Open pgAdmin and log in to your PostgreSQL server (enter your server credentials if prompted).  
2. In the left navigation panel, **right-click on "Databases"** → Select **Create > Database**.  
3. In the "Create - Database" window:  
   - Under the "General" tab, enter `hospital-management` in the "Database name" field (exact match required).  
   - Keep default values for "Owner", "Encoding", "Collation", and "Ctype" unless you have custom requirements.  
4. Click **Save** to create the database.  
5. Locate the SQL files for the system (e.g., `schema.sql` for table structure, `data.sql` for sample data). Ensure you know the correct execution order (run **schema files first**, then data files).  
6. **Right-click on the `hospital-management` database** (in the left panel) → Select **Query Tool**.  
7. In the Query Tool:  
   - Click the **Open File** icon (or press `Ctrl+O`), select your first SQL file, and click **Open**.  
   - Click the **Run** icon (or press `F5`) to execute the SQL file.  
   - Repeat this process for all remaining SQL files (follow the correct order).  


## 2. Test Database Connection  
After setting up the database, verify the connection between the Python project and PostgreSQL:  

1. Navigate to the `database/connection` directory in your Python project folder.  
2. Open the connection configuration file (typically named `connection.py` or `db_config.py`).  
3. Find the line defining the PostgreSQL password (e.g., `password = "your_pgadmin_password"`). Replace `"your_pgadmin_password"` with your **actual pgAdmin password** (the one used to log in to your PostgreSQL server).  
4. Save the configuration file.  
5. Locate the connection test script (named `test_connection.py` in the project).  
6. Run the test script using one of the following methods:  
   - **Command Line**: Open a terminal, navigate to the project root directory, and run:  
     ```bash
     python test_connection.py
     ```  
   - **IDE**: Open the project in your IDE (e.g., VS Code, PyCharm), right-click `test_connection.py`, and select **Run**.  
7. Check the output:  
   - **Success**: A message like "Database connection successful!" will appear.  
   - **Failure**: Verify your password, PostgreSQL server status (ensure it’s running), and port number (default: 5432) in the configuration file. Retry until the connection succeeds.  


## 3. Launch the Interactive Interface  
Once the database connection is confirmed, run the main application:  

1. Ensure the connection test (Step 2) was successful.  
2. Navigate to the **root directory** of your Python project (where `main.py` is located).  
3. Launch the application:  
   - **Command Line**: Run the following command in the terminal:  
     ```bash
     python main.py
     ```  
   - **IDE**: Right-click `main.py` in your IDE and select **Run**.  
4. If setup is correct, the **Hospital Management System interactive interface** will open automatically. You can now use features like patient management, appointment scheduling, and data queries.