Healthcare Database Design and Queries

This assignment presents a designed healthcare database schema encompassing various entities like patients, doctors, medications, orders, and payments. It outlines the structure of each table with attributes and provides SQL queries to demonstrate data extraction from the database. The queries cover retrieving details about doctors at Knox Private Hospital, identifying patients over 60 years old, and accessing prescription information.

Running head: DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
Knox Private Hospital Database Design and Implementation
in MS Access
Name of the Student
Name of the University

1DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
Table of Contents
1. Information Level Design:...........................................................................................................2
a. Entity Identification:................................................................................................................2
b. Attribute Identification:...........................................................................................................2
c. Relationship among the Entities:.............................................................................................2
d. Functional Dependencies:........................................................................................................2
e. Normalization:.........................................................................................................................3
2. Entity Relationship Diagram:......................................................................................................5
3. MS Access Model building:........................................................................................................6
4. SQL Queries:...............................................................................................................................9
4.a. Details of doctors registered at Knox Private Hospital:........................................................9
4.b. The management is interested in knowing the names and addresses of patients who are
older than 60 years.....................................................................................................................10
4.c.Prescription:.........................................................................................................................11
Bibliography:.................................................................................................................................12

2DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
1. Information Level Design:
a. Entity Identification:
The entities of the Knox Private Hospital database are patients, doctors, drugs and
pharmacies.
b. Attribute Identification:
The attributes of the entities has been described in the below.
Patient (PatientID, name, address, contactNumber, age, sex, appointmentID,
appointmentdate, time, prescriptionID, prescriptiondate, drugName, drugQuantity, doctorName,
costOfDrug)
Doctor (DoctorID, name, specialty, experience)
Drug (DrugID, name, formula, MRP, pharmacyName, cost, quantity, deliveryDate)
Pharmacy (PharmacyID, name, address, phone_number)
c. Relationship among the Entities:
i. One patient will see a primary doctor but a doctor will see many patients – One-
to-Many
ii. Various drugs can be sold by various pharmacies – Many-to-Many
iii. Many patients can consume may drugs – May-to-Many
iv. Many doctor can prescribe various drugs – Many-to-Many

3DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
d. Functional Dependencies:
PatientID  name, address, contactNumber, age, sex, AppointmentID, appointmentdate,
time, PrescriptionID, prescriptiondate, drugName, drugQuantity, doctorName, costOfDrug
AppointmentID  appointmentDate, doctorName, time
PrescriptionID  prescriptiondate, drugName, drugQuantity, doctorName
drugName, drugQuantity  costOfDrug
DoctorID  name, specialty, experience
DrugID  name, formula, MRP, pharmacyName, cost, quantity, OrderID, orderDate
OrderID  orderDate, pharmacyName, quantity
pharmacyName, quantity  cost
PharmacyID  name, address, phone_number
e. Normalization:
1st Normal Form: The 1st normal form states that each of the attribute in the table has to
consist of an atomic value. Attributes in the tables does not contain more than one value. Thus
the table is in the first normal form.
2nd Normal Form: The second normal form defines that the tables has to be in first
normal form and there must not be any partial dependency between the column and primary key.
Patient (PatientID, name, address, contactNumber, age, sex)
Appointment (AppointmentID, PatientID, DoctorID, appointmentDate, time)

4DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
Prescription (PrescriptionID, PatientID, DoctorID, DrugID, prescriptiondate,
drugQuantity)
Payment (PrescriptionID, DrugID, drugQuantity, cost)
Doctor (DoctorID, name, specialty, experience)
Drug (DrugID, name, formula, MRP, quantity)
Medicine_Payment (PharmacyID, DrugID, quantity, cost)
Pharmacy (PharmacyID, name, address, phone_number)
Order (PharmacyID, DrugID, quantity, deliveryDate)
3rd Normal Form: The tables is in the third normal form as the tables are in 2nd normal
form and there is not transitive dependency in the table.

5DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
2. Entity Relationship Diagram:
Figure 1: Entity Relationship Diagram of Knox Private Hospital
(Source: Created by Author)

6DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
To create the above diagram it has been assumed that the patients may purchase medicine
from the hospital as mentioned in prescription. The patients attend appointment with the doctor.
The hospital will order medicines from various pharmacies and all the order related data will be
recorded. As each prescription will hold various drugs and various drugs will be prescribed to
various patients may-to-many relationship occurs between drug and prescription. For this reason
Prescription_Drug entity has been created to divide the many-to-many relation into one-to-may
and many-to-one.
3. MS Access Model building:
Patient
PatientID name address contactNumber age sex
1 Savannah Flinn 44 Gadd Avenue 83828517 67 Female
2 Ebony Whitfeld 58 Derry Street 30399311 45 Female
3 Leo Hood 34 Crofts Road 53876333 78 Male
Doctor
DoctorID name specialty experience
1 Ashton Goe Cardiologist 23
2 David Newcomb Anesthesiologist 45
3 Jayden Brophy Cardiovascular surgeon 34

7DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
Appointment
AppointmentID PatientID DoctorID appointmentDate time
1 1 2 24-Jan-18 2:00 PM
2 2 3 02-Jan-18 11:30 AM
3 3 2 05-Jan-18 3:00 PM
Payment
PrescriptionID DrugID drugQuantity cost
1 2 3 $1.80
2 3 5 $5.85
1 3 5 $5.85
3 1 10 $30.00
3 2 7 $4.20
Drug
DrugID name formula MRP quantity
1 Statins ACC/AHA $3.00 40
2 Aspirin C9H8O4 $0.60 120
3 Beta $1.17 320

8DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
Pharmacy
PharmacyID name address phone_number
1 Adbiotec 39 Winwood St, Thebarton SA 5031 410795448
2 Adelaide Compression
Product
50 Marshall Tce, Brooklyn Park SA
5032
83527336
3 Advantage Pharmacy
Warradale
109-111A Diagonal Rd, Warradale
SA 5046
82951873
Prescription_Drug
PrescriptionID DrugID drugQuantity
1 2 3
2 3 5
1 3 5
3 1 10
3 2 7
Order
PharmacyID DrugID quantity deliveryDate
1 2 200 31-Jan-18
2 2 150 03-Feb-18
3 2 300 09-Feb-18

9DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
Order
PharmacyID DrugID quantity deliveryDate
2 3 200 15-Feb-18
1 3 400 10-Feb-18
Prescription
PrescriptionID PatientID DoctorID prescriptiondate
1 1 2 24-Jan-18
2 2 3 02-Jan-18
3 3 2 05-Jan-18
Medicine_Payment
PharmacyID DrugID quantity cost
1 1 400 $1,200.00
1 2 300 $180.00
2 3 500 $585.00
4. SQL Queries:
4.a. Details of doctors registered at Knox Private Hospital:
SELECT Doctor.name, Doctor.specialty, Doctor.experience FROM Doctor;

10DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
4.b. The management is interested in knowing the names and addresses of patients
who are older than 60 years.

11DATABASE DESIGN AND IMPLEMENTATION IN MS ACCESS
4.c.Prescription: