Access Database Design

This assignment focuses on designing an Access database for a hospital management system. It details the creation of tables for patients, receptionists, nurses, doctors, medication, shifts, and beds. The solution includes normalization of the tables, defining relevant relationships (one-to-many, many-to-one), and writing five SQL queries: one using a single table and four using two or more tables to retrieve specific data. Business rules governing the relationships between entities are also specified. The queries demonstrate how to retrieve patient information, medication details, relationships between staff members, patient-bed assignments, and patient information filtered by country.

(A) Access database:
1) Table Name
Patients
Receptionists
Nurses
Doctors
Medication

Shift
Bed
2) Normalisation
Patients
(PatientID,
PatientFirstName

PatientLastName
PatientMiddleName
TelephoneNumber
Email
StreetNumber
City
Province
Country
ReceptionistID
DoctorID)
Nurse
(NurseID
NurseFirstName
NurseLastName
NurseMiddleName
TelephoneNumber
Email
StreetNumber
City
Province
Country
ShiftID)
Receptionist
(ReceptionistID
ReceptionistFirstName
ReceptionistLastName

ReceptionistMiddleName
TelephoneNumber
Email
StreetNumber
City
Province
Country
ShiftID)
Doctor
(DoctorID
DoctorFirstName
DoctorLastName
DoctorMiddleName
TelephoneNumber
Email
StreetNumber
City
Province
Country
ShiftID)
Medication
(MedicationID
MedicationName
PatientID)
Shift

(ShiftID
StartTime
EndTime)
Bed
(BedID
NurseID
DoctorID
PatientID)
3) Relevant relationships
One to many relationship between receptionist and patient
One to many relationship between doctors and patient
One to many relationship between patient and medication
One to optional one between patient and bed
One to many between nurse and bed
One to one between doctor and shift
Many to one between nurse and shift
Many to one between receptionist and shift
4) 5 queries (One query using 1 table, 4 queries using 2 or more tables)
I. Using 1 table
Select * from Patient where Country="South Africa";

II. Using 2 table
Select m.patientID,m.medicationname from patient p, medication m where
p.patientID=m.patientID;
III. Using 2 table
Select r.receptionistID, d.doctorID from receptionist r, doctors d where
r.shiftID=d.shiftID;
IV. Using 2 table
SELECT p.patientID, b.doctorID FROM patient p, bed b WHERE
b.patientID=p.patientID;

V. Using 2 table
select b.bedID, b.nurseID, b.patientID from bed b, patient p where
p.patientId=b.patientID and p.country="South Africa";
5) Business rules.
Business rule
One doctor visit many patient
One nurse supervise one bed
One patient may prescribed multiple medicines
One patient may assign one bed
Many nurse in one shift
One doctor in one shift
Many receptionists in one shift