Hospital System Database Management: Staff Records, Patient Admissions, And Billing

Business Rules for the Hospital System Database

The proposed database is for a hospital system that needs a system to manage its records starting with all the records of all its staff. The hospital has different classifications of staff; doctors, nurses and ward boys. The database should be able to maintain records of all types of staff and keep a record of all their data that is relevant to the hospital. The hospital accepts patients who get an appointment with one doctor. After getting treated, the patient can be admitted. For each admission, the patient is assigned a room and a nurse to take care of the patient. The hospital has many rooms which are maintained by the ward boys working in the hospital. After the treatment is over, a bill is generated for the patient and the patient is supposed to make a one off payment to clear the bill. The proposed database should be able to hold all the data generated by the hospital while doing the same in an efficient fashion.

Based on these requirements a list of business rules that can be used to model the database can be defined as follows;

• The hospital has one or more staff.
• A staff is either a doctor, a nurse, or a ward boy.
• The treats one or more patients. A patient gets an appointment with a doctor.
• An appointment with a doctor can result to an admission. If the appointment results to an admission, the patient is assigned to one and only room.
• An admitted patient is assigned to a nurse who is supposed to track the progress of the patient and perform other necessary procedures for example administering medications.
• The hospital has many rooms. Each room is allocated to a ward boy who is responsible of maintaining and making sure the room is clean at all times.
• An appointment results to diagnosis for a certain disease.
• Every appointment results to a bill.
• The patient makes a one off payment to clear the bill.

The proposed database should be able to hold all the data generated by the hospital while doing the same in an efficient fashion by ensuring no redundancies in the data. The database should be modelled by following the standard normalization process to ensure all the tables are in third normal form. Normalizing the tables to 3NF will help enforce integrity and consistency in the data.

The selected database management system for the proposed database is MySQL which will be accessed using MySQL Workbench.  

• Admission table

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

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

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

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

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

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

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

| startDate     | datetime | NO   |     | NULL    |                |

| endDate       | datetime | NO   |     | NULL    |                |

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

6 rows in set (0.07 sec)

• Appointment table

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

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

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

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

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

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

| startingTime    | time    | NO   |     | NULL    |                |

| endingTime      | time    | NO   |     | NULL    |                |

| appointmentDate | date    | NO   |     | NULL    |                |

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

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

7 rows in set (0.04 sec)

• Bill table

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

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

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

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

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

Selected Database Management System: MySQL

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

| status        | varchar(10)   | NO   |     | PENDING |                |

| paidOn        | date          | YES  |     | NULL    |                |

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

5 rows in set (0.01 sec)

• Disease table

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

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

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

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

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

| details   | varchar(250) | NO   |     | NULL    |                |

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

3 rows in set (0.05 sec)

• Doctor table

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

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

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

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

| qualification | varchar(50) | NO   |     | NULL    |       |

| experience    | int(11)     | NO   |     | NULL    |       |

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

3 rows in set (0.04 sec)

• Nurse table

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

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

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

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

| shift   | varchar(25) | NO   |     | NULL    |       |

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

2 rows in set (0.01 sec)

• Patient table

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

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

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

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

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

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

| dob         | date         | NO   |     | NULL    |                |

| phoneNO     | varchar(15)  | NO   |     | NULL    |                |

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

| homeAddress | varchar(50)  | NO   |     | NULL    |                |

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

7 rows in set (0.02 sec)

• Room table

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

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

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

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

| type    | int(11) | NO   |     | NULL    |                |

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

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

3 rows in set (0.02 sec)

• Staff table

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

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

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

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

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

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

| email     | varchar(50)   | NO   |     | NULL    |                |

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

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

5 rows in set (0.03 sec)

• Wordboys table

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

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

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

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

| shift   | varchar(30) | NO   |     | NULL    |       |

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

2 rows in set (0.03 sec) 

• Admission table

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

| admissionID | appointmentID | nurseID | roomNO | startDate           | endDate             |

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

|           1 |             1 |       3 |      1 | 2018-09-12 00:00:00 | 2018-09-27 00:00:00 |

Staff Records

|           2 |             2 |       3 |      2 | 2018-09-13 00:00:00 | 2018-09-28 18:44:00 |

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

2 rows in set (0.00 sec)

• Appointment table

mysql> select * from appointment;

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

| appointmentID | doctorID | patientID | startingTime | endingTime | appointmentDate | daignsosis |

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

|             1 |        1 |         2 | 05:15:15     | 10:27:24   | 2018-09-26      |          1 |

|             2 |        1 |         2 | 04:15:20     | 12:33:35   | 2018-09-28      |          3 |

|             3 |        2 |         2 | 09:26:30     | 10:35:32   | 2018-09-27      |          3 |

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

3 rows in set (0.00 sec)

• Bill table

mysql> select * from bill;

+——–+—————+———+———+————+

| billID | appointmentID | amount  | status  | paidOn     |

+——–+—————+———+———+————+

|      1 |             1 | 4344434 | PAID    | 2018-09-27 |

|      2 |             2 |    2432 | PENDING | NULL       |

|      3 |             3 |     434 | PENDING | NULL       |

+——–+—————+———+———+————+

3 rows in set (0.00 sec)

• Disease table

+———–+———————+———————+

| diseaseID | name                | details             |

+———–+———————+———————+

|         1 | Common Cold         | flu virus           |

|         2 | High blood pressure | High blood pressure |

|         3 | Stroke              | Stroke              |

|         4 | Acne                | Problem with skin   |

|         5 | Pneumonia           | Pneumonia virus     |

+———–+———————+———————+

5 rows in set (0.00 sec)

• Doctor table

mysql> select * from doctor;

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

| doctorID | qualification | experience |

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

|        1 | cardiaologist |          5 |

|        2 | Gynacologist  |          8 |

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

2 rows in set (0.00 sec)

• Nurse table

+———+——-+

| NurseID | shift |

+———+——-+

|       3 | night |

|       4 | day   |

+———+——-+

2 rows in set (0.00 sec)

• Patient table

mysql> select * from patient;

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

| patientID | firstName | lastName | dob        | phoneNO     | email          | homeAddress           |

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

|         1 | Peter     | Harry    | 1980-09-18 | +434 433434 | [email protected] | 12th st victoria      |

|         2 | John      | Cane     | 1975-09-04 | +434343434  | [email protected]  | 3rd street av         |

|         3 | Grace     | Parker   | 1975-09-04 | +434343434  | [email protected] | 3rs av mountain drive |

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

3 rows in set (0.00 sec)

• Room table

mysql> select * from room;

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

| roomNO | type     | staffID |

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

|      1 | private  |       5 |

|      2 | public   |       5 |

|      3 | common   |       5 |

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

3 rows in set (0.00 sec)

• Staff table

mysql> select * from staff;

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

| staffID | firstName | lastName | email             | salary |

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

|       1 | Mike      | Linford  | [email protected]      | 124223 |

|       2 | Joy       | Daniels  | [email protected]      | 234233 |

|       3 | Denise    | Williams | [email protected] | 243423 |

|       4 | Melisa    | Gates    | [email protected]   | 324342 |

|       5 | Jane      | White    | [email protected]     |  23423 |

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

5 rows in set (0.00 sec)

• Wardboys table

mysql> select * from wardboys;

+———+——-+

| staffID | shift |

+———+——-+

|       5 | day   |

+———+——-+

1 row in set (0.00 sec)

• Admission table 

• Appointment tables 

• Bill table 

• Disease table 

• Doctor table 

• Nurse table 

• Patient table 

• Room table 

• Staff table 

• Wardboys table   

SQL Statements

Question 1: First name and last name combined as staff names for all staff

SQL:	select concat (firstname, ‘ ‘ , lastname ) as “staff names” from staff;
Output:	+—————–+ | staff names     | +—————–+ | Mike Linford    | | Joy Daniels     | | Denise Williams | | Melisa Gates    | | Jane White      | +—————–+ 5 rows in set (0.00 sec)

Question 2:All staff who are doctors

SQL:	select firstname, lastname from staff inner join doctor on doctor.doctorID=staff.staffID;
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | Mike      | Linford  | | Joy       | Daniels  | +———–+———-+ 2 rows in set (0.00 sec)

Question 3:All patients who were born before 1990

SQL:	select firstname, lastname, dob from patient where dob<‘1990/01/01’;
Output:	+———–+———-+————+ | firstname | lastname | dob        | +———–+———-+————+ | Peter     | Harry    | 1980-09-18 | | John      | Cane     | 1975-09-04 | | Grace     | Parker   | 1975-09-04 | +———–+———-+————+ 3 rows in set (0.00 sec)

Question 4:All diseases that have never been diagnosed

SQL:	select * from disease where diseaseID not in (select daignsosis from appointment);
Output:	+———–+———————+———————+ | diseaseID | name                | details             | +———–+———————+———————+ |         2 | High blood pressure | High blood pressure | |         4 | Acne                | Problem with skin   | |         5 | Pneumonia           | Pneumonia virus     | +———–+———————+———————+ 3 rows in set (0.02 sec)

Question 5:All patients who have been admitted

SQL:	select p.firstname, p.lastname from patient p inner join appointment a on a.patientID=p.patientID inner join admission ad on ad.appointmentID=a.appointmentID group by p.patientID;
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | John      | Cane     | +———–+———-+ 1 row in set (0.00 sec)

Question 6:Total amount of money paid to the hospital through bills

SQL:	select sum(amount) from bill where paidOn is not null;
Output:	+————-+ | sum(amount) | +————-+ |     4344434 | +————-+ 1 row in set (0.00 sec)

Question 7: patients ordered from the oldest to the youngest

SQL:	select firstname, lastname, dob from patient order by dob desc;
Output:	+———–+———-+————+ | firstname | lastname | dob        | +———–+———-+————+ | Peter     | Harry    | 1980-09-18 | | John      | Cane     | 1975-09-04 | | Grace     | Parker   | 1975-09-04 | +———–+———-+————+ 3 rows in set (0.00 sec)

Question 8: The doctor with the most appointments

SQL:	select d.doctorID, s.firstname, s.lastname, count(a.appointmentDate) from staff s inner join doctor d on d.doctorID=s.staffID inner join appointment a on a.doctorID=d.doctorID group by d.doctorID order by count(a.appointmentID) desc limit 1;
Output:	+———-+———–+———-+————————–+ | doctorID | firstname | lastname | count(a.appointmentDate) | +———-+———–+———-+————————–+ |        1 | Mike      | Linford  |                        2 | +———-+———–+———-+————————–+ 1 row in set (0.00 sec)

Question 9: All appointments between 1^st of January 2018 and 30^th of October 2018.

SQL:	select appointmentID, appointmentDate from appointment where appointmentdate between ‘2018-01-01’ and ‘2018-10-30’;
Output:	+—————+—————–+ | appointmentID | appointmentDate | +—————+—————–+ |             1 | 2018-09-26      | |             2 | 2018-09-28      | |             3 | 2018-09-27      | +—————+—————–+ 3 rows in set (0.02 sec)

Question 10: All staff who are nurses

SQL:	select firstname, lastname from staff,nurse where staff.staffID=nurse.nurseID;
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | Denise    | Williams | | Melisa    | Gates    | +———–+———-+ 2 rows in set (0.00 sec)

Question 11: Patients that have never been admitted

SQL:	select firstname, lastname from patient inner join appointment on appointment.patientID=patient.patientID where appointmentID not in (select appointmentID from admission);
Output:	+———–+———-+ | firstname | lastname | +———–+———-+ | John      | Cane     | +———–+———-+ 1 row in set (0.01 sec)

Question 12: Room details and the ward boys assigned to them

SQL:	select room.*,concat (s.firstname, ‘ ‘,s.lastname) as “names” from room inner join wardboys on wardboys.staffID=room.staffID inner join staff s on s.staffID=wardboys.staffID;
Output:	+——–+———-+———+————+ | roomNO | type     | staffID | names      | +——–+———-+———+————+ |      1 | private  |       5 | Jane White | |      2 | public   |       5 | Jane White | |      3 | common   |       5 | Jane White | +——–+———-+———+————+ 3 rows in set (0.02 sec)

Implementation of the database had some challenges which were resolved by doing research on the internet and from unit resources. Some of the challenges and their solutions encountered during the implementation of the database include;

• Verification of normalization to make sure that all the entities were in third normal form before implementing them as tables was a challenge. This was resolved by watching videos on normalization that helped us understand the concepts of normalization. The results of understanding normalization were very clear at the end as the tables that were achieved made it easier to implement the database as relational database where each table is related to at least one or more tables to form a complete relational database.

One major strength of the database is that the database followed all the steps including modelling using bottom up approach by doing normalization which helped to achieve tables that are able to hold data while maintaining integrity and consistency in the data. This is achieved by the relationships between tables where by implementing cascade update or delete or restrict delete helps achieve a very high degree of consistency in the data. For example by no appointment can exist for a patient that does not exist in the patients table.

The implemented database has met all the requirements specified by the business rules and has done it in the best way possible. Some of the improvements which can be added in the future depending on the usage of the database is creation of indexes for data that is accessed frequently and in large quantities. This would help improve the performance of the database even more.