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 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.
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 | |
+—————–+————-+——+—–+———+—————-+
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)
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)
mysql> describe patient;
+———–+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+————–+——+—–+———+—————-+
| 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)
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)
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)
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)
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)
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)
+—————+———–+———-+—————–+———+
| 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)
+———-+———–+———–+—————–+—————–+———————+———+—————+——–+
| doctorID | firstName | lastName | email | state | street | zipCode | type | salary |
+———-+———–+———–+—————–+—————–+———————+———+—————+——–+
| 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 |
+———-+———–+———–+—————–+—————–+———————+———+—————+——–+
+——–+————-+————————————-+——-+————–+—————–+
| 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)
+———–+———–+———-+—————-+—————–+——————-+———+————+
| 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)
+———–+————-+——–+———————+
| 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)
+—————-+————-+
| prescriptionID | treatmentID |
+—————-+————-+
| 1 | 1 |
| 2 | 2 |
+—————-+————-+
2 rows in set (0.00 sec)
+—————–+—————————————-+———-+
| 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)
+————-+—————+—————+———-+———–+
| 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)
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;
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].
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download