Assignment (H) Solution

Overview
A new hospital is being constructed to service the needs of a rapidly growing retirement community. You have been contracted to develop the database system that will manage the primary care operation of the hospital, but not the financial operation. The database system must have an easy to use interface that supports all the data entry and information gathering needs of the hospital.

Deliverables
There are four checkpoints for this project before the final project is due.

IMPORTANT. When submitting your files, you must group the paths of your source files together and tell me where they are in your code. This is to help me execute your program and grade it properly. My test files will have different names and different paths than the files you test with.

Checkpoint 1 is the ER diagram for this project. This will be an image file you submit through Canvas. You may draw this diagram on paper and scan the paper or use a piece of software and save your diagram as an image.

Checkpoint 2 is the text file containing all the CREATE TABLE statements for the database. You are to translate your ER diagram from checkpoint 1 into the tables necessary for your application to provide everything detailed in the specifications.

Checkpoint 3 is the source .java files for the working “Data Importer” program. This program will take two properly formatted text files (examples at the end of this document) and import this data into the database using INSERT statements. This program will be written in Java. You are free to use whatever tools in Java you see fit. I will not specify any methods, variables, classes, etc. You can build this application as you like. Meaning, you may use any libraries included with Java. You may not use any third-party libraries other than the libraries needed for SQLite. This program must be robust and handle exceptions. I will purposefully provide invalid data to the data importer. The importer should ignore any row (line in the text file) of data in the source file that contains invalid data. This is to simulate the real world where users will accidentally attempt to put invalid data into your system. This data importer is the guard at the gate to make sure that invalid data never enters your database.

Checkpoint 4 is the text file containing the SQL statements needed to query the data from the database. See the “SQL Queries” section below. NOTE: These queries must be written 100% in SQL. If your database has changed since checkpoint 2 (and I assume it might) please also include a new copy of the CREATE TABLE statements. Likewise, for the data importer. If there is a new version, please include the .java files.

Final Project is the final version of all above, plus a simple menu written in Java to select and execute the queries (noted below) and print the result of those queries to the screen. This project must be robust and handle exceptions.

Specifications
The hospital is staffed by many types of workers. Some are volunteers, while others are employees of the hospital. Employees include doctors, nurses, technicians, staff, and administrators.
All doctors who work for the hospital have consulting privileges, which allow them to be assigned to patients, order treatments for patients, and use the hospital facilities. Some, but not all, doctors also have admitting privileges, which allow them to admit patients to the hospital.
The hospital provides both inpatient and outpatient services, but no emergency services.
Outpatient services must be ordered by a doctor with consulting privileges.
Inpatient services require a hospital stay and are thus only available to patients who are admitted to the hospital.
When a patient is admitted to the hospital by a doctor, he or she is given a unique patient identification number and assigned to a room by an administrative employee.
Each patient must provide an emergency contact and insurance policy information at the time of admission.
The hospital rooms are all private rooms; that is, each room accommodates no more than one patient.
Our hospital has 20 rooms, the room numbers will be 1-20.
The doctor assigned to a patient can order and perform treatments for that patient.
Treatments include both procedures and medication.
All treatments must be ordered by a doctor and administered by an appropriate hospital employee.
Treatments may be ordered for patients on either an inpatient or outpatient basis. A timestamp is associated with the order and all administrations.
Treatments are administered to patients by one or more doctors.
At the time of admission, a patient’s primary doctor provides an initial diagnosis. This diagnosis may change while the patient is receiving inpatient services.
When an admitted patient’s primary doctor decides that the necessary course of treatment has ended, that patient is discharged from the hospital by an administrative employee.
For the sake of this project, we will assume that all people have a different last name, i.e. last names in this system will be unique. Of course, this is not accurate in the real world, but this will make the project (relatively) simple.

 

 

 

 

 

 

SQL Queries 1.
Room Utilization

1.1. List the rooms that are occupied, along with the associated patient names and the date the patient was admitted.

1.2. List the rooms that are currently unoccupied.

1.3. List all rooms in the hospital along with patient names and admission dates for those that are occupied.

2. Patient Information

2.1. List all patients in the database, with full personal information.

2.2. List all patients currently admitted to the hospital (i.e., those who are currently receiving inpatient services). List only patient identification number and name.

2.3. List all patients who were receiving inpatient services within a given date range. List only patient identification number and name.

2.4. List all patients who were discharged in a given date range. List only patient identification number and name.

2.5. List all patients who are currently receiving outpatient services. List only patient identification number and name.

2.6. List all patients who have received outpatient services within a given date range. List only patient identification number and name.

2.7. For a given patient (either patient identification number or name), list all admissions to the hospital along with the diagnosis for each admission.

2.8. For a given patient (either patient identification number or name), list all treatments that were administered. Group treatments by admissions. List admissions in descending chronological order, and list treatments in ascending chronological order within each admission.

2.9. List patients who were admitted to the hospital within 30 days of their last discharge date. For each patient list their patient identification number, name, diagnosis, and admitting doctor.

2.10. For each patient that has ever been admitted to the hospital, list their total number of admissions, average duration of each admission, longest span between admissions, shortest span between admissions, and average span between admissions.

3. Diagnosis and Treatment Information
3.1. List the diagnoses given to admitted patients, in descending order of occurrences. List diagnosis identification number, name, and total occurrences of each diagnosis.

3.2. List the diagnoses given to outpatients, in descending order of occurrences. List diagnosis identification number, name, and total occurrences of each diagnosis.

3.3. List the diagnoses given to hospital patients (both inpatient and outpatient), in descending order of occurrences. List diagnosis identification number, name, and total occurrences of each diagnosis.

3.4. List the treatments performed at the hospital (to both inpatients and outpatients), in descending order of occurrences. List treatment identification number, name, and total number of occurrences of each treatment.

3.5. List the treatments performed on admitted patients, in descending order of occurrences. List treatment identification number, name, and total number of occurrences of each treatment.

3.6. List the treatments performed on outpatients, in descending order of occurrences. List treatment identification number, name, and total number of occurrences of each treatment.

3.7. List the diagnoses associated with the top 5 patients who have the highest occurrences of admissions to the hospital, in ascending order or correlation.

3.8. For a given treatment occurrence, list all the doctors that were involved. Also include the patient name and the doctor who ordered the treatment.

4. Employee Information

4.1. List all workers at the hospital, in ascending last name, first name order. For each worker, list their, name, and job category.

4.2. List the primary doctors of patients with a high admission rate (at least 4 admissions within a one-year time frame).

4.3. For a given doctor, list all associated diagnoses in descending order of occurrence. For each diagnosis, list the total number of occurrences for the given doctor.

4.4. For a given doctor, list all treatments that they ordered in descending order of occurrence. For each treatment, list the total number of occurrences for the given doctor.

4.5. For a given doctor, list all treatments in which they participated, in descending order of occurrence. For each treatment, list the total number of occurrences for the given doctor.

4.6. List doctors who have been involved in the treatment of every admitted patient.

Layout of Person Data File
The person data file will be a comma delimited text file. This file will contain data about people in the system. Not all data is required for all people. E.g. Workers at the hospital will not need data for attributes like a patient ID, room number, etc. The following list will detail each element.

The type of person. Legal types are a.
a. “V” – Volunteer
b. “D” – Doctor
c. “A” – Administrator
d. “N” – Nurse
e. “T” – Technician
f. “I” – Inpatient
g. “O”- Outpatient

2. First Name of Person

3. Last Name of Person (for the sake of the project, this value will be unique)
4. Type of privileges a Doctor has
a. “C” – Consulting
b. “A” – Admitting

5. Patient ID
6. Room Number
7. Emergency Contact Name (first and last name)
8. Emergency Contact Phone Number
9. Insurance Policy Number
10. Insurance Policy Company
11. Last Name of Patient’s Primary Doctor
12. Initial Diagnosis
13. Admission Date
14. Discharge Date Sample lines from the file:
D,John,Smith,C,,,,,,,,,,
I,Sam,Jones,,123,1,Liz Jones,334-555-1234,INS123,INS Company, Smith, Broken Arm,1-1-2019, 1-3-2019

 

 

 

 

 

Layout of Treatment Data File

The treatment data file will be a comma delimited text file. This file will contain data about treatments ordered/ administered in the system.

1. Patient Last Name
2. Doctor Last Name
3. Treatment Type
a. “P” – Procedure
b. “M” – Medication
4. Timestamp

For the sake of simplicity, only one timestamp for the treatments. Treat ordered and administered the same.

Sample lines from the file (assume that all the patient names and doctor names are valid in the database):

Smith, Knowles, M, Aspirin, 1-1-2019 12:14

Smith, Knowles, M, Aspirin, 1-1-2019 16:14

Smith, Knowles, M, Aspirin, 1-1-2019 20:14

Jackson, Jones, P, Set Arm, 1-2-2019 8:05