[SOLVED] PostgreSQL: COP 5725 project 4

COP 5725 Fall 2016
Hospital System Database and Data Interface Term Project

Due date: Nov. 24, 2016 (THU)

The database contains most of the information used by the web application. A database is a collection of related data in the form of schemas, tables, views, stored procedures, triggers, indexes, and other objects.

Data Interface

The data interface is the point of contact between the application data access layer and the database. Similar to the software package interfaces you already know, the data interface provides a predefined set of functions that will serve as the gateway to the data from the applications data access layer.

Page 1 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen Universe of Discourse

A database represents some aspect of the real world called the miniworld or the universe of discourse. You will design a database based on the description of the miniworld contained in this section. The subject matter of this project was selected to increase the likelihood of students being familiar with its main concepts and make it easier to find relevant information online to guide the design decisions. However, when explicit requirements given in this description contradict with the information you find from other sources regarding this subject matter, your solution must satisfy the requirements given here.

You must explicitly state and justify all assumptions that are not part of this description.

Your task consists of the design of the database and data interface of a hospital system. As such, you will deal with patients, doctors, nurses, receptionists, accountants, diagnoses, drugs, laboratory, radiology, bills, payments, suppliers, insurance companies, and so on.

You will need to store information on the full name, gender, and date of birth of your patients and one of the addresses to be used for mailing the paperbased bill. Each patient may have one or more credit cards on file, each one associated with a billing address. Each patient may have several addresses and several credit cards, but each credit card must only have one billing address. You must allow patients to specify their preferred credit card associated with the billing address for each payment. In order to communicate with patients and send a billing reminder, you must store their email address and have the option of adding their phone number.

You will control access to patients accounts with credentials consisting of a username and a password. The username will be the patients email address and the password will be stored encrypted in the database. You must allow patients to change their e-mail address and password at any time. At the same time, you may also want to store the patients insurance policy ID as a reference to facilitate your project development.

A patient will first be registered by a receptionist. The receptionist will keep a record of the patient and create or locate the patients insurance information for billing purposes. The receptionist is responsible for recording the date of the patients first visit, checking the doctors availability, making an appointment with a doctor on a particular date, and informing the patient to come on that date. Also, we wont ask you to simulate scenarios such as an appointment with a preferred doctor. You can assume that if for a particular day theres a doctor available, then the patient will be assigned to that doctor. If there are many doctors available, the patient will be randomly assigned to one doctor. You need to keep a list of receptionists names and contact information such as address and phone number.

Page 2 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen

Before the patient sees the doctor, a nurse will record the check-in time and measure some vital information such as height, weight, blood pressure, and body temperature. The nurse is also responsible for room-related issues such as assigning rooms to patients, recording the usage of rooms, recording patients admission date and date of discharge. You need to keep a list of nurses names, gender, and contact information such as address and phone number.

The doctors will have access to the patients medical record, including the patients vital information recorded by the nurses. The doctors will determine a diagnosis, write a prescription, and arrange laboratory or radiology tests as necessary. The doctors name, specialty, gender, and contact information such as address and phone number should be stored in your system.

You need to store the prescription medicine information. The medicine must have a name, description, picture, and price. Each medicine must be associated with one category. You will design a way of storing trees of categories. Even though you should think of the category hierarchy as trees, you will store them as a table in the database. Populate the table with a set of categories of your choice. Your chosen category hierarchy must be at least three levels deep. Medicines may be associated with any category, not just with the leaves of your trees. The following are two very basic trees:

The rooms designated by the nurses as well as the patients laboratory and radiology tests should all be kept on record for billing purposes, together with the date. The charges for a room are by date and the charges for radiology and laboratory tests are by usage count.

All expenses incurred by a patient (prescription drugs, lab tests, X-rays, consultation, etc.) will be combined into a final bill. Each payment created from the bill must be associated with exactly one credit card. You may assume that cards are valid and have enough funds to cover the transaction if your insurance policy will not cover all the expenses. The patients system balance will be automatically applied to payments when a payment is created; only the outstanding balance after using up the system balance is charged to the patients credit card. Since patients can update or

Page 3 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen

delete their credit cards and addresses on their accounts, you must store a copy of the payment information for each patient per visit so that the system can generate invoices in the future (you will not model invoices, though). It must be possible to determine the amount of system balance, insurance coverage, and credit card charge that are paid within one payment.

When a patient completes the purchase of the medicine, the hospitals inventory is updated accordingly. Since medicine pricing may change at any time, you must store the price of each medicine at the time the bill is generated so that you know what price to charge. Each medicine will have a low inventory threshold. When a payment brings the count of one medicine below the threshold, a re-stocking reminder must be generated by the database.

The hospital does not manufacture any medicines. Instead, it purchases all medicines from suppliers. In order to expedite orders, the hospital maintains a stock of all the medicines it offers. You need to keep a list of suppliers and store the company name, business address, sales representatives contact information such as name, address, email address, phone number, etc., and discount percentage, i.e., a fixed percent discount applied to all purchases from this supplier. Each medicine must have at least one supplier. Each supplier in the database must supply at least one medicine. Each supplier may supply medicines at different prices.

Re-stocking reminders are used by the hospital to place orders with medicine suppliers. You will not model the orders to the suppliers beyond the details of re-stocking reminders. A re-stocking reminder must have information about the medicine name, category, and the best supplier to order from. The system will determine the best supplier at the time of the creation of the reminder by comparing the final cost of the medicine to the hospital taking into account the suppliers price for the medicine and the arranged discount, if any.

An accountant will be responsible for coordinating payments with the patient and patients insurance company at the same time. If a patient doesnt pay his or her share with a credit card on the due date, a billing reminder must be generated by the database. To implement the billing reminder, you need to store information on the current date and compute the time that has passed between the current date and the date the bill was generated. For consistency, the first due date is 3 months after the bill is generated. You need to keep a list of accountants name, gender and contact information such as address and phone number.

Regarding a patients insurance company, you must keep a record of the patients insurance policy number, insurance status, coverage amount, coverage percentage per visit, etc. To simplify this process, you may assume that each patient will get a constant coverage amount in total and per visit. The coverage percentage is a fixed constant regardless of the services the patient gets billed by the hospital.

For each visit to the doctor, the insurance will cover a certain percentage of the cost and the rest must be paid entirely by the patient with a credit card on file before the due date in order to avoid a

Page 4 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen

billing reminder. When the insurance amount is nearly used up, for that patients visit the insurance coverage will be possibly less than the percentage it should cover. Obviously, for those patients with an invalid insurance status, the insurance company will not cover any of the expenses they incur. This case also applies at the time when the coverage amount reduces to 0.

Deliverables

For Project 4 deliverable you need to implement triggers that will update the flag of a particular medicine in the medicine inventory table and insert a billing reminder record with a fixed format in the billing reminder table. Also, you need to demonstrate some tables in the console as required.

You must use store procedures and embedded SQL to achieve the expected functionality.
Similar to Project 3, we will give you an XML file named input.xml. Your Java program should read and parse this file and store the values in your corresponding tables. You will simulate the following scenario:

A patient registers his/her personal information and credit card information first. This patient will interact with the Nurse and Doctor entities. The patient will need to stay in the assigned hospital room during the prescribed period and will have laboratory and radiology tests done during the stay. The patient will purchase medicines from the hospital and, as a result, the hospitals medicine inventory must be updated accordingly. There will be a bill generated for the patients visit. The bill will contain the following information:

Table 1 Bill Table

1. First, your Java program will parse all the information from input.xml and call your

   maintenance function (which you implemented in Project 2) to insert/update values in the corresponding tables of your database. Especially for the Bill table, you need to insert the information the input.xml file specifies (e.g., BillDate, CreditCardNumber, BillAddress, IfPaid, etc.).

2. Then, calculate the following costs: DoctorCost, NurseCost, RoomCost, LabCost, RadiologyCost, and MedicineCost based on the information we provided in the input.xml file and fill the corresponding attributes in the Bill table. It is worth mentioning that the PatientID should be a sequence attribute that is automatically added since we are simulating the visit of a new patient. The same applies to the BillID attribute. You need to write a function to calculate the FirstDueDate (3 months after the BillDate) and fill the value of the FirstDueDate attribute in the Bill table. For instance, as input.xml indicates, the FirstDueDate should be 3 months after 2016-09-28, that is, 2016-12-28 in this case.
3. Thirdly, as shown in the above Bill table, we require that for this bill record, the flag of Ifpaid is set to NO (or N). We further require you to have a table called Inquiry with the following attributes: InquiryID, PatientID, InquiryDate, BillID (see below).

   Table 2 Inquiry Table Page 5 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen

The purpose of the Inquiry table is to make a date comparison and trigger the billing reminder if a condition is satisfied. As input.xml specifies, the InquiryDate to be filled in the record is 2017-01-01, which is after 2016-12-18. At the same time, part of this bill still has not been paid by the patient (Assume that the insurance part will be paid immediately when the bill is generated). Then a billing reminder will be triggered and you need to have a billing reminder table and to insert a record according to the following requirement:

In the billing reminder table, theres only one attribute Billing Reminder Record. You need to follow the following format exactly when you insert a new record into this table. Record Example:
Patient 1 arrears X dollars on bill generated on 2016-09-28 with inquiryID 1

In this example, Patient 1 is retrieved by PatientID in the Inquiry table; 2016-09-28 is the corresponding BillDate in the Bill table; inquiryID 1 is the corresponding InquiryID in the Inquiry table. X will be calculated as the project description indicates:
If this patients insurance status is Active:

X = TotalCost InsuranceCoverage
= DoctorCost + NurseCost + RoomCost + LabCost + RadiologyCost + MedicineCost If

(TotalCost * coverage percent coverage amount) ? (coverage amount) : (TotalCost * coverage percent)
As input.xml specifies in this case, the insurance status is Active, thus X is calculated as follows:

X = 200 +200 + 100 *2 +100 +100 + 55*5 (200 +200 + 100 *2 +100 +100 + 55*5)*0.8 = 215

The corresponding table is shown below:

Table 3 Billing Reminder Table If this patients insurance status is Inactive:

X = TotalCost
You need to write a function to calculate X and insert the record in the billing reminder table when the corresponding trigger fires.

4. Additionally, when the patient purchases several medicines, as the input.xml file specifies, the medicine inventory should be updated accordingly. For instance, the current stock level of the medicine with ID 5 should be reduced by 5 in this case. Its CurrentAmount should be 94 after the purchase, dropping below the thresholdInventory, which is 97. That will trigger a restoreReminder update. Before the patient purchases this medicine, the flag restoreReminder in the Medicine table is set to No as input.xml specifies. After the purchase, the Restore trigger fires and the value of the restoreReminder flag in the medicine table will be updated to Yes. As a result, the hospital system administrator will know that the medicine needs to be restocked.

Table 4 Medicine Table
In summary, you should deliver 3 triggers to fulfill the requirements:

Page 6 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen

1. BillingReminder trigger: compare the InquiryDate in the Inquiry Table and the corresponding FirstDue Date in the Bill Table. If the InquiryDate is late than FirstDue Date, and the corresponding Ifpaid flag for this bill is NO (or N), a billing reminder will be triggered and one new record needs to be inserted in the Billing Reminder Table.
2. MedicineAmount update trigger: when one patient purchases y units of a specific medicine with ID x. Correspondingly, the CurrentAmount in the medicine table of ID x needs to be updated with y reduction;
3. MedicineRestore reminder trigger: compare the CurrentAmount in the medicine table of MedicineID x with the ThresholdInventory in the medicine table of MedicineID x. If CurrentAmount < ThresholdInventory, a MedicineRestore reminder will be triggered and the RestoreReminder value of MedicineID x needs to be updated from No to Yes, such that the hospital system administrator knows that medicine x needs to be replenished.

Your output result should include 4 tables after you process input.xml:

1. Bill Table with one corresponding record inserted and all attributes filled (see Table 1).
2. Inquiry Table with one corresponding record inserted and all attributes filled (see Table 2).
3. Billing Reminder Table with one corresponding record inserted and all attributes filled

   (see Table 3).

4. The RestoreReminder attribute in the Medicine table is updated when the medicine

   restore reminder is triggered (see Table 4).

You should be able to demonstrate your result in the console by running your Java program with

input.xml and any other input file, e.g., your database configuration file, as arguments.

Students should organize their Java project files including the source code, any external jar library files, and any database configuration files into a folder called project4. Students should also include a readme.txt file clearly describing how to run their code. We will use a different input file (verify_input.xml) with the same structure as input.xml to test your code.

Students must deliver a runnable program. If the TA has trouble running your program by following your readme file instruction, the TA will contact you through email and you need to come to the TAs office in person to give a demonstration of your program. If at that time your program is still not runnable, the TA will have no choice but to grade it as zero.

The deliverable for Project 4 is one Zip file containing your project4 directory. Due date: November 24, 2016

Important Note

As the deliverables are related, you will be given the opportunity to improve your previous work so that your future work does not carry over mistakes. In this regard, when a deliverable is due, you will be given the opportunity to turn in the previous deliverable if you revised it based on the feedback you received when it was graded. The next due deliverable will then be graded considering its consistency with the revised deliverable, not with the original one. Note that this is optional and that

Page 7 of 8

COP 5725 Principles of Database Management Systems Professor ShuChing Chen it always applies to the previous deliverable only, not to all previous deliverables. Additionally, once a

deliverable is graded, the score will not be changed even if you improve it afterwards.

Submission

Submit your zip file with the format Deliverable#_YourName_Fall2016 as the filename and upload it to Moodle.

For example, for the fourth deliverable, you should name your file Deliverable4_LebronJames_Fall2016.zip

Page 8 of 8