Services Offered at Get Well Soon Hospital

Get well soon is a hospital offering different types of services to patients. The hospital allows patients to make appointments for various services using phone calls and the appointment is set. The patient visits the hospital and is treated by the doctor who is qualified for the service they had booked for. After the treatment the patients are given a prescription which consist of one or more types of drugs. The business rules of the business are;

• The hospital has many doctors.
• Each doctor is qualified in one or more fields.
• A patient can make one or more appointments with a doctor who is qualified with the type of service the patient wants.
• Each appointment results to one and only one treatment session with the doctor.
• A treatment can result to a prescription or not thus it’s not mandatory for a patient to get a prescription after getting treated.
• The hospital has many types of drugs in its pharmacy.
• A prescription can have one or more types of drugs.
• Each treatment results to one and only one payment. The payment amount includes the doctor’s fee and the total amount for the drugs if the patient got a prescription.

The current method used by Get Well Soon to book appointments and to maintain records of patients involve use of methods like phone calls, use of spreadsheets to save data and calendars to record appointments. This methods are proofing to be ineffective as the number of customers and appointments made are increasing every day thus there is need for a database management system that will make it easy to record and access data efficiently. The dataset will be implemented to replace the current system in use.

To implement the system a relational database management system will be used. The selected DBMS for this project is MySQL which will be accessed using PhpMyAdmin using a browser.

• Appointment table

mysql> describe appointment;

+—————–+————-+——+—–+———+—————-+

| Field           | Type        | Null | Key | Default | Extra          |

+—————–+————-+——+—–+———+—————-+

| appointmentID   | int(11)     | NO   | PRI | NULL    | auto_increment |

| patientID       | int(11)     | NO   | MUL | NULL    |                |

| doctorID        | int(11)     | NO   | MUL | NULL    |                |

| appointmentDate | date        | NO   |     | NULL    |                |

| status          | varchar(25) | NO   |     | NULL    |                |

+—————–+————-+——+—–+———+—————-+

• Doctor table

mysql> describe doctor;

+———–+—————+——+—–+———+—————-+

| Field     | Type          | Null | Key | Default | Extra          |

+———–+—————+——+—–+———+—————-+

| doctorID  | int(11)       | NO   | PRI | NULL    | auto_increment |

| firstName | varchar(50)   | NO   |     | NULL    |                |

| lastName  | varchar(50)   | NO   |     | NULL    |                |

| email     | varchar(100)  | NO   |     | NULL    |                |

| state     | varchar(50)   | NO   |     | NULL    |                |

| street    | varchar(50)   | NO   |     | NULL    |                |

| zipCode   | int(4)        | NO   |     | NULL    |                |

| type      | varchar(50)   | NO   |     | NULL    |                |

| salary    | decimal(10,0) | NO   |     | NULL    |                |

+———–+—————+——+—–+———+—————-+

9 rows in set (0.03 sec)

• Drugs table

mysql> describe drugs;

+—————–+—————+——+—–+———+—————-+

| Field           | Type          | Null | Key | Default | Extra          |

+—————–+—————+——+—–+———+—————-+

| drugID          | int(11)       | NO   | PRI | NULL    | auto_increment |

| name            | varchar(100)  | NO   |     | NULL    |                |

| description     | varchar(500)  | NO   |     | NULL    |                |

| um              | varchar(25)   | NO   |     | NULL    |                |

| pricePerUnit    | decimal(10,0) | NO   |     | NULL    |                |

| quantityInStock | decimal(10,0) | NO   |     | NULL    |                |

+—————–+—————+——+—–+———+—————-+

6 rows in set (0.05 sec)

• Patient table

mysql> describe patient;

+———–+————–+——+—–+———+—————-+

| Field     | Type         | Null | Key | Default | Extra          |

Appointment Booking System

+———–+————–+——+—–+———+—————-+

| patientID | int(11)      | NO   | PRI | NULL    | auto_increment |

| firstName | varchar(50)  | NO   |     | NULL    |                |

| lastName  | varchar(50)  | NO   |     | NULL    |                |

| email     | varchar(100) | NO   |     | NULL    |                |

| state     | varchar(50)  | NO   |     | NULL    |                |

| street    | varchar(50)  | NO   |     | NULL    |                |

| zipCode   | int(4)       | NO   |     | NULL    |                |

| dob       | date         | NO   |     | NULL    |                |

+———–+————–+——+—–+———+—————-+

8 rows in set (0.15 sec)

• Payment table

mysql> describe payment;

+————-+—————+——+—–+——————-+—————————–+

| Field       | Type          | Null | Key | Default           | Extra                       |

+————-+—————+——+—–+——————-+—————————–+

| paymentID   | int(11)       | NO   | PRI | NULL              | auto_increment              |

| treatmentID | int(11)       | NO   | MUL | NULL              |                             |

| amount      | decimal(10,0) | NO   |     | NULL              |                             |

| datePaid    | timestamp     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+————-+—————+——+—–+——————-+—————————–+

4 rows in set (0.05 sec)

• Prescription table

mysql> describe prescription;

+—————-+———+——+—–+———+—————-+

| Field          | Type    | Null | Key | Default | Extra          |

+—————-+———+——+—–+———+—————-+

| prescriptionID | int(11) | NO   | PRI | NULL    | auto_increment |

| treatmentID    | int(11) | NO   | MUL | NULL    |                |

+—————-+———+——+—–+———+—————-+

2 rows in set (0.02 sec)

• Prescription_drugs table

mysql> describe prescription_drugs;

+—————-+—————+——+—–+———+——-+

| Field          | Type          | Null | Key | Default | Extra |

+—————-+—————+——+—–+———+——-+

| prescriptionID | int(11)       | NO   | PRI | NULL    |       |

| drugID         | int(11)       | NO   | PRI | NULL    |       |

| quantity       | decimal(10,0) | NO   |     | NULL    |       |

+—————-+—————+——+—–+———+——-+

3 rows in set (0.02 sec)

• Qualifications table

mysql> describe qualifications;

+——————-+————–+——+—–+———+—————-+

| Field             | Type         | Null | Key | Default | Extra          |

+——————-+————–+——+—–+———+—————-+

| qualificationID   | int(11)      | NO   | PRI | NULL    | auto_increment |

| qualificationName | varchar(100) | NO   |     | NULL    |                |

| doctorID          | int(11)      | NO   | MUL | NULL    |                |

+——————-+————–+——+—–+———+—————-+

3 rows in set (0.04 sec)

• Treatment table

mysql> describe treatment;

+—————+————–+——+—–+———+—————-+

| Field         | Type         | Null | Key | Default | Extra          |

+—————+————–+——+—–+———+—————-+

| treatmentID   | int(11)      | NO   | PRI | NULL    | auto_increment |

| appointmentID | int(11)      | NO   | MUL | NULL    |                |

| treatmentDate | date         | NO   |     | NULL    |                |

| status        | varchar(25)  | NO   |     | NULL    |                |

| diagnosis     | varchar(250) | NO   |     | NULL    |                |

+—————+————–+——+—–+———+—————-+

5 rows in set (0.05 sec)

• Appointment table

+—————+———–+———-+—————–+———+

| appointmentID | patientID | doctorID | appointmentDate | status  |

+—————+———–+———-+—————–+———+

|             1 |         1 |        1 | 2018-09-21      | pending |

|             2 |         2 |        2 | 2018-09-21      | pending |

|             3 |         2 |        3 | 2018-09-25      | pending |

|             4 |         1 |        2 | 2018-09-29      | pending |

+—————+———–+———-+—————–+———+

4 rows in set (0.00 sec)

• Doctor table

+———-+———–+———–+—————–+—————–+———————+———+—————+——–+

| doctorID | firstName | lastName  | email           | state           | street              | zipCode | type          | salary |

Need for a Database Management System

+———-+———–+———–+—————–+—————–+———————+———+—————+——–+

|        1 | Jon       | Snow      | [email protected]   | New South Wales | mountain drive      |    1234 | gynacologist  | 120000 |

|        2 | Arya      | Stark     | [email protected]   | Victoria        | Victoria 1st street |    4321 | dermatologist | 130000 |

|        3 | Cersei    | Lannister | [email protected] | QueensLand      | queens drive        |    5431 | cardiologist  | 110000 |

+———-+———–+———–+—————–+—————–+———————+———+—————+——–+

• Drugs table

+——–+————-+————————————-+——-+————–+—————–+

| drugID | name        | description                         | um    | pricePerUnit | quantityInStock |

+——–+————-+————————————-+——-+————–+—————–+

|      1 | Hydrocodone | combined with acetaminophen         | grams |            2 |             900 |

|      2 | simvastatin | a cholesterol-lowering statin drug  | grams |            2 |             500 |

|      3 | Lisinopril  | Blood pressure drug                 | grams |            3 |             400 |

+——–+————-+————————————-+——-+————–+—————–+

3 rows in set (0.00 sec)

• Patient table

+———–+———–+———-+—————-+—————–+——————-+———+————+

| patientID | firstName | lastName | email          | state           | street            | zipCode | dob        |

+———–+———–+———-+—————-+—————–+——————-+———+————+

|         1 | Peter     | Griffin  | peter2mail.com | Queensland      | queens avenue     |    4567 | 1990-05-15 |

|         2 | Lois      | Griffin  | [email protected]  | New South Wales | 1st treet wales   |    3232 | 1992-09-26 |

|         3 | Brian     | griffin  | [email protected] | QueensLand      | mountain drive av |    5454 | 1990-09-26 |

+———–+———–+———-+—————-+—————–+——————-+———+————+

3 rows in set (0.00 sec)

• Payment table

+———–+————-+——–+———————+

| paymentID | treatmentID | amount | datePaid            |

+———–+————-+——–+———————+

|         1 |           1 |   3454 | 2018-09-20 14:12:22 |

|         2 |           2 |   3453 | 2018-09-20 14:12:22 |

+———–+————-+——–+———————+

2 rows in set (0.00 sec)

• Prescription table

• Prescription_drugs table

+—————-+————-+

| prescriptionID | treatmentID |

+—————-+————-+

|              1 |           1 |

|              2 |           2 |

+—————-+————-+

2 rows in set (0.00 sec)

• Qualifications table

+—————–+—————————————-+———-+

| qualificationID | qualificationName                      | doctorID |

+—————–+—————————————-+———-+

|               1 | Degree in Medicine                     |        1 |

|               2 | Masters in Medicine                    |        2 |

|               3 | Degree in Medicine                     |        3 |

|               4 | Certification in Pyschological studies |        2 |

+—————–+—————————————-+———-+

4 rows in set (0.00 sec)

• Treatment table

+————-+—————+—————+———-+———–+

| treatmentID | appointmentID | treatmentDate | status   | diagnosis |

+————-+—————+—————+———-+———–+

|           1 |             1 | 2018-09-21    | complete | positive  |

|           2 |             2 | 2018-09-21    | complete | positive  |

+————-+—————+—————+———-+———–+

2 rows in set (0.00 sec)

• Appointment table

• Doctor table

• Drugs table

• Patient table

• Payment table

• Prescription table
• Prescription_drugs table
• Qualifications table

• Treatment table 

Query 1: Doctors with a salary greater than 50000

SQL:	select firstname, lastname,salary from doctor where salary>50000;
Output:	+———–+———–+——–+ | firstname | lastname  | salary | +———–+———–+——–+ | Jon       | Snow      | 120000 | | Arya      | Stark     | 130000 | | Cersei    | Lannister | 110000 | +———–+———–+——–+ 3 rows in set (0.00 sec)

Query 2: Details of doctors ordered from the highest paid to the least paid.

SQL:	select firstname, lastname,salary from doctor order by salary desc;
Output:	+———–+———–+——–+ | firstname | lastname  | salary | +———–+———–+——–+ | Arya      | Stark     | 130000 | | Jon       | Snow      | 120000 | | Cersei    | Lannister | 110000 | +———–+———–+——–+ 3 rows in set (0.00 sec)

Query 3: Query to show first name and last name of a patient with alias patient names

SQL:	select concat(firstname,’ ‘,lastname) as “patient names” from patient;
Output:	+—————-+ | patient names  | +—————-+ | Peter  Griffin | | Lois Griffin   | | Brian griffin  | +—————-+ 3 rows in set (0.00 sec)

Query 4: Query to calculate total payments that have been made

SQL:	select sum(amount) from payment;
Output:	+————-+ | sum(amount) | +————-+ |        6907 | +————-+ 1 row in set (0.00 sec)

Query 5: Doctor who have appointments

SQL:	select firstname, lastname from doctor where doctorID  in (select doctorID from appointment);
Output:	+———–+———–+ | firstname | lastname  | +———–+———–+ | Jon       | Snow      | | Arya      | Stark     | | Cersei    | Lannister | +———–+———–+ 3 rows in set (0.02 sec)

Query 6: Doctors with the number of appoitments

SQL:	select firstname, lastname,count(appointmentID) from doctor inner join appointment on appointment.doctorID=doctor.doctorID group by doctor.doctorID;
Output:	+———–+———–+———————-+ | firstname | lastname  | count(appointmentID) | +———–+———–+———————-+ | Jon       | Snow      |                    1 | | Arya      | Stark     |                    2 | | Cersei    | Lannister |                    1 | +———–+———–+———————-+ 3 rows in set (0.00 sec)

Query 6: Amount paid for each appointment

SQL:	select a.appointmentId, a.appointmentDate,p.amount from appointment a inner join treatment t on t.appointmentID=a.appointmentID inner join payment p on p.treatmentID=t.treatmentID;
Output:	+—————+—————–+——–+ | appointmentId | appointmentDate | amount | +—————+—————–+——–+ |             1 | 2018-09-21      |   3454 | |             2 | 2018-09-21      |   3453 | +—————+—————–+——–+ 2 rows in set (0.00 sec)

Query 7: Total amount paid for each drug

SQL:	select d.name,sum(d.priceperunit) from drugs d inner join prescription_drugs pd on pd.drugID=d.drugID inner join prescription p on p.prescriptionID=pd.prescriptionID group by d.drugID;
Output:	+————-+———————+ | name        | sum(d.priceperunit) | +————-+———————+ | Hydrocodone |                   2 | | simvastatin |                   2 | | Lisinopril  |                   6 | +————-+———————+ 3 rows in set (0.00 sec)

Query 8: All patients with lastname griffin

SQL:	select firstname, lastname from patient where lastname like ‘%griffin%’;
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | Peter     | Griffin  | | Lois      | Griffin  | | Brian     | griffin  | +———–+———-+ 3 rows in set (0.00 sec)

Query 9: All patients with lastname griffin

SQL:	select firstname, lastname from patient where lastname like ‘%griffin%’;
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | Peter     | Griffin  | | Lois      | Griffin  | | Brian     | griffin  | +———–+———-+ 3 rows in set (0.00 sec)

Query 10: Patient who has brought most money to the hospital

SQL:	select p.firstname,p.lastname, sum(pt.amount) from patient p inner join appointment a on a.patientID=p.patientID inner join treatment t on t.appointmentID=a.appointmentID inner join payment pt on pt.treatmentID=t.treatmentID group by p.patientID order by sum(pt.amount) desc limit 1;
Output:	+———–+———-+—————-+ | firstname | lastname | sum(pt.amount) | +———–+———-+—————-+ | Peter     | Griffin  |           3454 | +———–+———-+—————-+ 1 row in set (0.01 sec)

Query 11: patients who have had an appointment

SQL:	select firstname, lastname from patient where patientID in (select patientID from appointment);
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | Peter     | Griffin  | | Lois      | Griffin  | +———–+———-+ 2 rows in set (0.00 sec)

Query 12: Count of the total appointments

SQL:	select count(patientID) as “total number of appointments” from appointment;
Output:	+————————–+ | total number of appointments | +————————–+ |                        4 | +————————–+ 1 row in set (0.01 sec)

Although the assignment was very informative from start to end, some parts of the assignment were challenging and required a lot of research in order to accomplish. Some of the challenges experienced while undertaking the tasks are;

• Determining the type of constraints when defining relationships between tables. This required us to understand what effect cascade update and cascade delete would have on the table and where each of the options or both options were applicable. By studying the entity relationship diagram we were able to determine which option is applicable to which entity in the table.
• Writing complex queries was challenging especially queries that involved joins of multiple tables. By doing research were able to understand the concept of primary keys and foreign keys which helped us to solve the queries.

Kaula, R. (2007). Normalizing with Entity Relationship Diagramming. [online] The Data Administration Newsletter. Available at: https://tdan.com/normalizing-with-entity-relationship-diagramming/4583 [Accessed 20 Sep. 2018].

Millamila, M. (2014). Entity Relationship Diagrams and Normalization. [online] prezi. Available at: https://prezi.com/tunrndt0nin4/entity-relationship-diagrams-and-normalization/ [Accessed 20 Sep. 2018].