Hospital Database Design
Yuning Zhang
Introduction
After learning database concepts and tools, I wanted to build a database in the real scenario. I found a doctor as my client, and interviewed him for the requirements of the hospital database. I drafted a requirement statement and drew an ERD based on it, and then implemented my ERD using MySQL DBMS, and entered enough sample data into my MySQL so I could conduct my SQL commands. Finally I wrote SQL statements that were lined with the sample queries that allow one to retrieve key info. from the database I designed.
Interview Notes
Assumption
Assume a doctor can only belong to one department.
Assume a doctor can earn more than BM degree, like they can also earn Phd etc.
Assume the patient rating is from 0 to 5.
Assume a timeslot is 30 min (i.e. 30 min per patient)
Assume the general work time for a doctor is 8.a.m-11.a.m and 2.p.m-5.p.m., so a patient can know the available time slot by figuring out which time slots have been booked.
Requirement statement
This database is designed for a hospital, both patients and doctors can use it. We want patients to be able to find the suitable doctor they want to make appointments with. They figure out whether the doctor is suitable based on the information of doctor like department, name, title, birthdate, education background, rating from other patients, booking fee, and available timeslot. Patients also want to track appointment records they have made in a department or with a doctor etc. They should be possible to accumulate statistics about medical expenditure to decide which medical insurance they want to choose. We want doctors to be able to track the medical history of the patients, which time slot has been booked by patients, how many beds the department currently have for inpatient etc. The doctors can do statistics on the data of patients for the research purpose if they get the permission from the patients.
Source Data
Since patient data relates to privacy, I couldn't get the real data. So I faked the data here, but they were enough to show the whole structure and function of my database.
educationbackground: https://docs.google.com/spreadsheets/d/1mcDHjcQihIlfuprawKTH8pZy7JDUT5sL/edit?usp=sharing&ouid=108324592981296782129&rtpof=true&sd=true
doctoreducationbackground: https://docs.google.com/spreadsheets/d/1odxl1WtBxuXe0OBXgKzjxLggJBaVfE5c/edit?usp=sharing&ouid=108324592981296782129&rtpof=true&sd=true
ER Diagram
When there exists a many-to-many relationship, we use a bridge table, which usually contains many foreign keys from the table it connects, and these foreign keys are also primary keys . To release these primary keys, we can generate a new unique attribute which can represent the table and set this unique attribute as the only primary key instead. For instance, 'Appointment' table had 3 foreign keys which were 'doctorNo', 'identityNo' and 'timeslot'. Instead of typing all 3 values when retrieving the data from this table, I created a new primary key 'appointmentId' instead since id was normally unique. The client only needed to use 'appointmentId' to get info. from this table then.
MySQL Transformation
Transform ER Diagram into MySQL
Creaçt table (example: Educationbackground)
Connect tables
Transform ER diagram to database
(i.e. generate true tables in database)
Import csv files into database
Requirement Queries
Retrieve all departments in the hospital.
select name from department;
2. Retrieve the booking fee of Associate Chief Physician.
select bookingfee from title where title='Associate Chief Physician';
3. Retrieve all universities of doctors in Traditional Chinese Medicine department and their majorrating.
select university, majorrating
from ((department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join doctoreducationbackground d2 on d1.doctorNo=d2.doctorNo) inner join educationbackground e on d2.studentId=e.studentId
where d.name='Traditional Chinese Medicine';
4. Retrieve all doctors in Cardiology department who has a phd degree from a top 5 university.
select d1.name
from ((department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join doctoreducationbackground d2 on d1.doctorNo=d2.doctorNo) inner join educationbackground e on d2.studentId=e.studentId
where d.name='Cardiology' and majorrating<=5 and degreeplan like 'Phd%';
5. Retrieve all doctors in Traditional Chinese Medicine department who has a rating from patient better than 4.
select d1.name
from (department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join rating r on d1.doctorNo=r.doctorNo
where d.name='Traditional Chinese Medicine' and rating>4;
6. Retrieve all doctors in who has a rating from patient no less than 3.5 order by doctorNo.
select d1.name
from (department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join rating r on d1.doctorNo=r.doctorNo
where rating>=3.5
order by d1.doctorNo;
7. Retrieve all doctors who are older than 50, their birthdates and the departments they belong (i.e. born after 1972) order by birthdates (from old to young).
select d1.name, d1.birthdate, d.name
from department d inner join doctor d1 on d.departmentId=d1.departmentId
where d1.birthdate<1972
order by d1.birthdate;
8. Retrieve all booked timeslots of doctor Yuhao Wang in Gynaecology department on April 15th.
select timeslot
from (department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join appointment a on d1.doctorNo=a.doctorNo
where d1.name='Yuhao Wang' and d.name='Gynaecology' and timeslot like '04-15%';
9. Retrieve timeslot, diagnosis and prescription records of patient Chuqian Liu’s appointment with doctor Qiu Yu.
select timeslot, diagnosis, prescription
from (patient p inner join appointment a on p.identityNo=a.identityNo) inner join doctor d1 on d1.doctorNo=a.doctorNo
where p.firstname='Chuqian' and p.lastname='Liu' and d1.name='Qiu Yu';
10. Retrieve doctor name, timeslot, diagnosis and prescription records of patient Jing Zhang’s appointment in Cardiology department
select d1.name,timeslot, diagnosis, prescription
from ((patient p inner join appointment a on p.identityNo=a.identityNo) inner join doctor d1 on d1.doctorNo=a.doctorNo) inner join department d on d.departmentId=d1.departmentId
where p.firstname='Jing' and p.lastname='Zhang' and d.name='Cardiology';
11. Calculate the total medical expenditure of patient Jing Zhang in April.
SELECT SUM(medicalexpenditure) AS Total_medical_expenditure
from patient p inner join appointment a on p.identityNo=a.identityNo
where p.firstname='Jing' and p.lastname='Zhang' and a.timeslot like '04%';
12. Retrieve how many beds of Neurosurgery department are left for inpatient.
select currentNo
from bed b inner join department d on d.departmentId=b.departmentId
where d.name='Neurosurgery';
13. Retrieve all diagnosis from Traditional Chinese Medicine department.
select diagnosis
from (department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join appointment a on d1.doctorNo=a.doctorNo
where d.name='Traditional Chinese Medicine';
14. Calculate average medical expenditure of each appointment in Cardiology department.
SELECT AVG(medicalexpenditure) AS Avg_medical_expenditure
from (department d inner join doctor d1 on d.departmentId=d1.departmentId) inner join appointment a on d1.doctorNo=a.doctorNo
where d.name='Cardiology';
Reflection
I realized it was important to fully understand the client's requirements. When asking questions, I should not use jargons since they might not understand Database terms. The key in this project was designing the ER Diagram, which was the foundation for the following steps. Everything was easy after finishing the ER Diagram as I only needed to utilize the tool MySQL and write quires. I was also surprised and excited to see how a simple database functioned so well, it was strong enough to answer almost any questions related to this hospital system by effectively retrieving the records.