Hospital Management System — Mariano Cánovas

01

About this project

A coursework project for Database Applications and Information Systems. The brief was to design and document a relational schema for a hospital management system, then describe the application functions that would operate on top of it. Eight tables cover patient admission, room allocation, diagnoses, medicament inventory, and billing.

MySQL 8 tables CHECK constraints Foreign keys

02

The data model

Eight tables with foreign-key relationships. Click any table to see what it connects to. Click again to clear.

Click a table to explore its relationships

HOSPITAL

Hospital_IDINT

HnameTEXT

Zip_CodeTEXT

AddressTEXT

DOCTOR

Doctor_noINT

DnameTEXT

GenderM/F

PATIENT

Patient_noINT

PnameTEXT

Age0-110

Date_of_birthDATE

Room_noFK

ROOM

Room_noINT

AvailabilityY/N

TypeTEXT

CapacityINT

DIAGNOSIS

Diagnosis_noINT

Diagnosis_nameTEXT

Doctor_noFK

BILLS

Bills_noINT

AmountDECIMAL

Patient_noFK

MEDICAMENTS

MnameTEXT

Quantity_in_inventoryINT

Expiration_dateDATE

PATIENT_ROOM

Patient_noFK

Room_noFK

03

A patient's journey

The schema in action. Four stages of a typical hospital visit, with the tables and queries that support each one.

1

Admission

A patient arrives at the hospital. They are registered with a unique identifier, name, age, and date of birth, then assigned to one of the available rooms.

PATIENT ROOM PATIENT_ROOM

-- Find available rooms before assigning one
SELECT Room_no, Type, Capacity
FROM ROOM
WHERE Availability = 'Y';

2

Diagnosis

A doctor examines the patient and records a diagnosis. Each diagnosis is linked to the attending physician, so the system can later report which doctor is treating which patient.

DOCTOR DIAGNOSIS

-- Match each doctor with the diagnoses they have made
SELECT D.Dname, DG.Diagnosis_name
FROM DOCTOR D
JOIN DIAGNOSIS DG ON D.Doctor_no = DG.Doctor_no
ORDER BY D.Dname;

3

Treatment

Treatment requires checking the medicament inventory. The hospital tracks stock levels and expiration dates so that expired or out-of-stock items are not prescribed.

MEDICAMENTS

-- Medicaments that are in stock and not yet expired
SELECT Mname, Quantity_in_inventory, Expiration_date
FROM MEDICAMENTS
WHERE Quantity_in_inventory > 0
  AND Expiration_date > '2024-12-31'
ORDER BY Expiration_date;

4

Billing

Once treatment is complete, a bill is generated and attached to the patient. The total amount covers the treatment and any medications used.

BILLS PATIENT

-- Total billed per patient
SELECT P.Pname, ROUND(SUM(B.Amount), 2) AS Total
FROM PATIENT P
JOIN BILLS B ON P.Patient_no = B.Patient_no
GROUP BY P.Patient_no
ORDER BY Total DESC;

04

Playground

The schema is loaded with the sample data already inserted. Write your own query or pick one from the samples below, then use Cmd/Ctrl + Enter to run.

Loading database…

Run a query to see results.

05

Sample queries

Five queries that exercise the schema across joins, aggregations, and conditional expressions.

SELECT
    P.Pname,
    P.Age,
    R.Room_no,
    R.Type
FROM
    PATIENT P
    JOIN ROOM R ON P.Room_no = R.Room_no
ORDER BY
    P.Pname;

SELECT
    P.Pname,
    ROUND(SUM(B.Amount), 2) AS Total
FROM
    PATIENT P
    JOIN BILLS B ON P.Patient_no = B.Patient_no
GROUP BY
    P.Patient_no
ORDER BY
    Total DESC;

SELECT
    D.Dname,
    D.Gender,
    DG.Diagnosis_name
FROM
    DOCTOR D
    JOIN DIAGNOSIS DG ON D.Doctor_no = DG.Doctor_no
ORDER BY
    D.Dname;

SELECT
    Mname,
    Quantity_in_inventory,
    Expiration_date,
    CASE
        WHEN Expiration_date < '2025-01-01' THEN 'Expiring soon'
        WHEN Expiration_date < '2026-01-01' THEN 'This year'
        ELSE 'Future'
    END AS Status
FROM
    MEDICAMENTS
ORDER BY
    Expiration_date;

SELECT
    COUNT(*) AS Bills,
    ROUND(SUM(Amount), 2) AS Total,
    ROUND(AVG(Amount), 2) AS Average,
    ROUND(MIN(Amount), 2) AS Lowest,
    ROUND(MAX(Amount), 2) AS Highest
FROM
    BILLS;