COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
Database
COP 5725 ‐‐‐ Fall 2016
Hospital System Database and Data Interface Term Project
Due date: Nov. 3, 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 pre‐defined set of functions that will serve as the gateway to the data from the application’s data access layer.
Page 1 of 8
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen Universe of Discourse
A database represents some aspect of the real world called the mini‐world or the universe of discourse. You will design a database based on the description of the mini‐world 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 paper‐based 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 e‐mail 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 patient’s e‐mail 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 patient’s 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 patient’s insurance information for billing purposes. The receptionist is responsible for recording the date of the patient’s 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. To simplify this process, we assume the availability of doctors is defined as the date based. Also, we won’t ask you to simulate scenarios such as an appointment with a preferred doctor. You can assume that if for a particular day there’s 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 Shu‐Ching 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 patient’s medical record, including the patient’s vital information recorded by the nurses. The doctors will determine a diagnosis, write a prescription, and arrange laboratory or radiology tests as necessary. The doctor’s 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 patient’s 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 patient’s credit card. Since patients can update or
Page 3 of 8
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching 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 hospital’s 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 representative’s 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 supplier’s price for the medicine and the arranged discount, if any.
An accountant will be responsible for coordinating payments with the patient and patient’s insurance company at the same time. If a patient doesn’t 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 patient’s insurance company, you must keep a record of the patient’s 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 Shu‐Ching Chen
billing reminder. When the insurance amount is nearly used up, for that patient’s 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
The deliverable for Project 3 contains 2 parts:
1. Part 1 — You must use store procedures and embedded SQL to achieve the following functionalities:
Login: Provide the user name and password, check if the credentials are correct, and return a success or failure message accordingly.
Update_password: Provide the username and original password, check if they are valid and, if so, replace the original password with the new one and return a message confirming the update.
Retrieve_patient: Provide the patient ID and return the corresponding record from the patient table.
Set_preferred_creditCard: Provide the credit card number and patient ID. Mark the patient’s credit card as preferred and also retrieve the corresponding billing address. Return a message confirming the update and also print out the credit card number and associated billing address information.
Get_medicine_availability: Provide the medicine ID and date to check how much of it is left on the specified date. Return a message confirming the quantity available in the hospital inventory.
Charge_patient: Provide the patient ID and bill date to check the patient’s charges: total amount and the break down by source. Return a table showing the total bill amount and the breakdown of the charges by source. The return table should look like this:
Make sure that all charges are positive.
You should be able to demonstrate your result in the console by providing a sequence of functions to fulfill each use case. For example, start from the login procedure, update the user password, retrieve the patient’s information, set the patient’s preferred credit card, retrieve the stock level of a medicine, and provide the charges for the bill. You can hardcode all arguments a function expects.
In part 1, 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 project3_part1. Students should also include a readme.txt file clearly describing how to run their code and what is the expected result in a sequence.
Page 5 of 8
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
2. Part 2 — You must use store procedures and embedded SQL to achieve the expected functionality. This time we will give you an XML file named input.xml. Your Java program should read and parse this file and store the values in the corresponding tables. You will simulate a scenario in which a patient registers two credit cards and checks into a hospital. The scenario is shown below:
Your Java program will
First, parse the patient’s information from input.xml and call your maintenance function
(which you implemented in Project 2) to insert a new patient record in the existing
patient table.
Then, parse the information of two credit cards from input.xml and call your maintenance
function (which you implemented in Project 2) to insert two credit card records in the existing credit card table. The reason why we have two cards is because we want to mark one of them as the preferred credit card for this patient.
In the end, this patient will check into a hospital. A nurse will take care of this patient and record the height, weight, blood pressure, body temperature, check-in information as recorded in the input.xml file, and insert a new check-in record in the existing check-in record table.
Page 6 of 8
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
Your earlier design is probably different from the above figure. You should store declared values in input.xml into your corresponding tables. For some attributes you don’t have, you should alter your corresponding tables to add those attributes. Additionally, the maintenance functions from Project 2 need to be changed accordingly.
In the end, you are required to use store procedures and embedded SQL to join the parsed values from input.xml into one table with two records and print them out to the console.
The output table should have the following attributes:
1. SSN
2. FIRSTNAME
3. MIDDLENAME
4. LASTNAME
5. EMAIL
6. PHONENUMBER
7. PASSWORD
8. BALANCEAMOUNT
9. INSURANCEPOLICYNO 10. GENDER
11. DATEOFBIRTH
12. CREDITCARDNUMBER 13. BILLADDRESS
14. IFPREFEREDTOUSE 15. IFMAILPAPERBILL
16. NURSEID
17. HEIGHT
18. WEIGHT
19. BLOODPRESSURE
20. BODYTEMPERATURE 21. CHECKINTIME
Basically, the output table covers all of the attributes shown in input.xml. The two records are the corresponding values in the input.xml.
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.
In part 2, 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 project3_part2. 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.
The deliverable for Project 3 is one Zip file containing your project3_part1 and project3_part2 directories.
Due date: November 3, 2016
Page 7 of 8
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen 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 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 third deliverable, you should name your file Deliverable3_LebronJames_Fall2016.zip
Page 8 of 8