SQL – Database Design

This was done for a class project on relational database design, and represents a fictional hospital database.

Design Requirements

A patient is admitted to a hospital with one or more medical conditions. The hospital maintains patients’ information (Name, age, sex, DOB, and address). The Hospital identifies each patient by a unique id and creates a patient admission record. Each admission record has an admission number, admission date, and discharge date information. The hospital assigns a doctor to treat a patient when they are admitted. The system records the doctor’s name, id, specialty, and years of experience. When admitted, a patient is admitted to a bed (which belongs to a ward). A ward is identified by ward number, name, and type (e.g., medical/surgical). Each ward contains multiple beds. Beds are tracked by a number and type (e.g., side room bed/ open ward bed) as well. If the patient needs surgery the hospital will schedule an operation. Each operation has an operation number, date, time, patient id, and may have multiple doctors.

Entity-Relationship Diagram

Tables in 3rd Normal Form

PATIENT

PatientID

PFname

PLname

PSex

PDOB

PAddress

DOCTOR

DrID

DrFname

DrLname

DrSpecialty

DrYearsExp

WARD

WardID

WardName

WardType

BED

BedID

BWardID

BedType

OPERATION

OpID

OpDate

OpTime

OpPID

DOCOPASSIGNMENT

AssOpID

DocOpID

ADMISSIONDATE

AdNum

AdmitDate

DischargeDate

ADASSIGNMENT

AdAssNum

AdPID

AdDrID

AdBedID

AdWardID

Example Queries

Table Creation:

CREATE TABLE AdAssignment (
AdAssNum INTEGER NOT NULL PRIMARY KEY,
AdPID INTEGER NOT NULL,
AdDrID INTEGER NOT NULL, 
AdBedID INTEGER NOT NULL,
AdWardID INTEGER NOT NULL,
CONSTRAINT FK_AdAssignment_AdNum FOREIGN KEY (AdAssNum) REFERENCES AdmissionDate(AdNum),
CONSTRAINT FK_AdAssignment_PID FOREIGN KEY (AdPID) REFERENCES Patient(PatientID),
CONSTRAINT FK_AdAssignment_DrID FOREIGN KEY (AdDrID) REFERENCES Doctor(DrID),
CONSTRAINT FK_AdAssignment_BedWard FOREIGN KEY (AdBedID, AdWardID) REFERENCES Bed(BedID, BWardID)
);

Write a SQL query to list all wards and how many patients are CURRENTLY in them.

SELECT WardName, COUNT(AdNum) as PatientsInWard FROM AdAssignment aa
LEFT OUTER JOIN AdmissionDate ad
ON aa.AdAssNum = ad.AdNum IN 
(SELECT AdNum FROM AdmissionDate
WHERE AdmitDate <= CURRENT_DATE()
AND DischargeDate IS NULL)
JOIN Ward w
ON aa.AdWardID = w.WardID
GROUP BY w.WardID;

SQL query to list all of a given Doctor’s patients, along with the ward and bed number.

SELECT p.PFname, p.PLname, d.DrFname, d.DrLname, b.BedID, b.BedType, w.WardName FROM AdAssignment aa
JOIN AdmissionDate ad
ON aa.AdAssNum = ad.AdNum
JOIN Patient p
ON aa.AdPID = p.PatientID
JOIN Doctor d
ON aa.AdDrID = d.DrID
JOIN Bed b
ON aa.AdBedID = b.BedID
AND aa.AdWardID = b.BWardID
JOIN Ward w
ON b.BWardID = w.WardID
WHERE ad.AdmitDate <= CURRENT_DATE()
AND ad.DischargeDate IS NULL
AND d.DrFname = 'Darius'
AND d.DrLname = 'Dodge';