Hospital Management Database — Himath Chandrasena

Database Schema — 7 Tables

PKPatient

patient_idINT PK
full_nameVARCHAR
dobDATE

PKDoctor

doctor_idINT PK
full_nameVARCHAR
specializationVARCHAR
consultation_feeDECIMAL

FKAppointment

appointment_idINT PK
patient_idFK
doctor_idFK
app_dateDATE
app_timeTIME
statusVARCHAR

PKTestType

test_type_idINT PK
test_nameVARCHAR

FKTestTaken

test_taken_idINT PK
patient_idFK
test_type_idFK
date_performedDATE
outcomeVARCHAR
charge_appliedDECIMAL

FKPatient_Phone

phone_idINT PK
patient_idFK CASCADE
phone_numberVARCHAR

FKPatient_Allergy

allergy_idINT PK
patient_idFK CASCADE
allergy_nameVARCHAR

Relationships

📅 Patient → Appointment — a patient can schedule many appointments 1 : M

🩺 Doctor → Appointment — a doctor conducts many appointments 1 : M

🧪 Patient → TestTaken — a patient undergoes many tests 1 : M

🔬 TestType → TestTaken — a test type defines many test records 1 : M

📱 Patient → Phone / Allergy — multi-valued attributes as separate tables with CASCADE delete 1 : M

ER Diagram (Hand-drawn)

Entities & Attributes

Hospital Database — Entities and Attributes

Full ER Diagram

Entities: Patient, Doctor, Appointment, TestType, TestTaken · Relationships: Schedules, Conducts, Undergoes, Defined-by, Includes

Bugs Found & Fixed

FIXED SELECT result shows Bob missing — the SELECT demo deletes patient 2 (Bob) but the table still shows him in the INSERT section. Result table now reflects post-DELETE state consistently.

FIXED Tab JS uses implicit event global — showTab() relied on the global event object which is non-standard and fails in Firefox. Fixed by passing the button element explicitly.

NOTE status column should be ENUM — using VARCHAR(20) for status allows any string. In production this should be ENUM('pending','completed','cancelled') for data integrity.

SQL Operations

create_tables.sql

CREATE DATABASE hospital;
USE hospital;

CREATE TABLE Patient (
    patient_id  INT PRIMARY KEY AUTO_INCREMENT,
    full_name   VARCHAR(100),
    dob         DATE
);

CREATE TABLE Doctor (
    doctor_id        INT PRIMARY KEY AUTO_INCREMENT,
    full_name        VARCHAR(100),
    specialization   VARCHAR(100),
    consultation_fee DECIMAL(10,2)
);

CREATE TABLE Appointment (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id     INT,
    doctor_id      INT,
    app_date       DATE,
    app_time       TIME,
    -- BUG FIX: use ENUM for data integrity
    status         ENUM('pending','completed','cancelled'),
    FOREIGN KEY (patient_id) REFERENCES Patient(patient_id),
    FOREIGN KEY (doctor_id)  REFERENCES Doctor(doctor_id)
);

CREATE TABLE TestType (
    test_type_id INT PRIMARY KEY AUTO_INCREMENT,
    test_name    VARCHAR(100)
);

CREATE TABLE TestTaken (
    test_taken_id  INT PRIMARY KEY AUTO_INCREMENT,
    patient_id     INT,
    test_type_id   INT,
    date_performed DATE,
    outcome        VARCHAR(255),
    charge_applied DECIMAL(10,2),
    FOREIGN KEY (patient_id)   REFERENCES Patient(patient_id),
    FOREIGN KEY (test_type_id) REFERENCES TestType(test_type_id)
);

CREATE TABLE Patient_Phone (
    phone_id     INT PRIMARY KEY AUTO_INCREMENT,
    patient_id   INT,
    phone_number VARCHAR(15),
    FOREIGN KEY (patient_id) REFERENCES Patient(patient_id) ON DELETE CASCADE
);

CREATE TABLE Patient_Allergy (
    allergy_id   INT PRIMARY KEY AUTO_INCREMENT,
    patient_id   INT,
    allergy_name VARCHAR(50),
    FOREIGN KEY (patient_id) REFERENCES Patient(patient_id) ON DELETE CASCADE
);

insert_data.sql

INSERT INTO Patient (full_name, dob) VALUES
    ('Alice Silva',      '1995-04-12'),
    ('Bob Perera',       '1988-11-23'),
    ('Charlie Fernando', '2001-02-15');

INSERT INTO Doctor (full_name, specialization, consultation_fee) VALUES
    ('Dr. Sunil',     'Cardiologist',    3000.00),
    ('Dr. Priyantha', 'General Surgeon', 2500.00);

INSERT INTO Appointment (patient_id, doctor_id, app_date, app_time, status) VALUES
    (1, 1, '2026-01-28', '10:30:00', 'pending'),
    (2, 2, '2026-01-28', '14:00:00', 'completed'),
    (3, 1, '2026-01-29', '09:15:00', 'pending'),
    (1, 2, '2026-01-30', '11:30:00', 'pending');

INSERT INTO Patient_Phone   (patient_id, phone_number) VALUES (1, '0771234567');
INSERT INTO Patient_Allergy (patient_id, allergy_name) VALUES (1, 'Penicillin');

SELECT * FROM Patient;

patient_id	full_name	dob
1	Alice Silva	1995-04-12
3	Charlie Fernando	2001-02-15

SELECT * FROM Doctor;

doctor_id	full_name	specialization	consultation_fee
1	Dr. Sunil	Cardiologist	3000.00
2	Dr. Priyantha	Senior General Physician	2500.00

SELECT * FROM Appointment;

appointment_id	patient_id	doctor_id	app_date	app_time	status
1	1	1	2026-01-28	10:30:00	pending
3	3	1	2026-01-29	09:15:00	completed
4	1	2	2026-01-30	11:30:00	pending

update.sql

-- Mark appointment 3 as completed
UPDATE Appointment
SET   status = 'completed'
WHERE appointment_id = 3;

-- Update Dr. Priyantha's specialization
UPDATE Doctor
SET   specialization = 'Senior General Physician'
WHERE doctor_id = 2;

delete.sql

-- Remove a single appointment
DELETE FROM Appointment WHERE appointment_id = 2;

-- Full patient removal (cascade handles phone/allergy)
DELETE FROM TestTaken   WHERE patient_id = 2;
DELETE FROM Appointment WHERE patient_id = 2;
DELETE FROM Patient     WHERE patient_id = 2;
-- Patient_Phone and Patient_Allergy auto-delete via CASCADE

Operations Covered

🏗️DDL — CREATE TABLE with constraints

➕DML — INSERT records

🔍SELECT with column filtering

✏️UPDATE existing rows

🗑️DELETE with FK awareness

🔗Foreign Keys & CASCADE