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: Sep. 27, 2016 (TUE)
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
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, diagnosis, medicine, laboratory, radiology, bills, payment, and insurance company entity, 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 billing address and also the credit card that will be used for 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 username and 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 address and password at any time. At the same time, you may also want to store patient’s insurance policy information to facilitate your project development.
The patient will first be registered from a receptionist. The receptionist will keep a record of this patient and create or locate the patient’s insurance information for later billing purpose. The receptionist is responsible for recording services’ date and time, and checking the doctors’ availabilities and schedules to make an appointment with a doctor. You need to keep a list of receptionist’s name and contact information such as address and phone number.
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 maintaining room related issues, such as assigning rooms to patients, recording the usage of rooms, recording patient’s discharge date, and offering other services if applicable. You need to keep a list of nurse’s name, gender, and contact information such as address and phone number.
The doctors will have the access to all the patient’s medical record, including patient’s vital information from the nurses. The doctors will prescribe a diagnosis, write a prescription, arrange laboratory or
Page | 2
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
radiology checks if necessary. Doctor’s name, discipline, gender, and contact information should be stored in your system.
You need to store 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 for the nurses, laboratory check, and radiology check for the patients should all be kept in the record for billing purposes with the date.
Charges created by doctors, nurses, medicine and laboratory service, radiology service, and room service (if applicable) will be combined into a final bill. Each bill must be associated with exactly one credit card. You can assume that cards are valid and have enough funds to cover the transaction if your insurance policy cannot cover all the charges. 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 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 stipend, 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.
Page | 3
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
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, 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.
The accountant will be responsible for coordinating with the patient and patient’s insurance company at the same time. If a patient doesn’t pay his or her part by the credit card on the first due day, a billing reminder must be generated by the database. For the consistency, the first due day is set as the same date when the bill is generated but after 3 months. You may use the amount of system balance as an indicator to trigger the billing reminder at the due date. You need to keep a list of accountants’ name, gender and contact information such as address and phone number.
For patient’s insurance company, you must keep a record of patient’s unique information, insurance policy number, insurance status, coverage amount, coverage percentage per visit, etc. To simplify this process, we assume each patient will accordingly get a constant coverage amount regarding whatever services he or she gets billed by the hospital. You can treat the coverage amount offered by the insurance company as an extra balance in the hospital system.
For patient’s per visit, the insurance will cover certain percentage and the rest of bill must be paid by the patient with a credit card before the due date in order not to trigger the 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 invalid insurance status, the insurance company will not cover any of the charges for them. This case also applies at the time when the coverage amount reduces to 0.
Deliverables
Part 1:
ER Diagram: Turn in as a PDF document. State and explain all assumptions. Use the notation from the class notes for the ER diagram.
Part 2:
Page | 4
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
Depict relations as tables and specify the constraints for each table.
Please note that you will have to fulfill the following requirements in your relational data model:
1. Indicate the primary key in each table.
2. Define the data type for each attribute.
3. Show the one‐to‐one, one‐to‐many, and many‐to‐many relationships using notation from
class notes among all the tables.
4. Each table should have at least five records.
5. Indicate other constraints (such as not null, or greater than zero).
You should carefully arrange your PDF document such that part 1 and part 2 is clearly divided. For your reference, please see the sample deliverable ‐‐ Deliverable1_ShengGuan_Fall2016.pdf.
Important Note
As 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 revise 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. Also for the deliverable, once is graded, the score will not be changed even later you improve the previous deliverable.
Submission
Submit your PDF / sql file with the format Deliverable#_YourName_Fall2016 as the filename and upload it to Moodle system.
For example, for first deliverable, you should name your file asDeliverable1_LebronJames_Fall2016.pdf
pgAdmin Instruction
•• You have to first download the software called pgAdmin from http://www.pgadmin.org/ •• Choose the latest version from the link PostgreSQL mirror network and download it
•• Double click the execution file (msi or dmg).
•• Keep all the default setup and finish the installation process
Page | 5
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
Log in to pgAdmin Server
• On-Campus Instruction
If you are connecting to the servers using FIU IP, please start by doing the following steps,
1. Open your pgAdmin
2. Click on the top left most button called “Add a connection to a server”
3. Click on the link on top called “Properties”
4. Fill up the information as follows: Name: COP5725
Host: cop5725‐‐‐postgresql.cs.fiu.edu Maintenance DB: postgresql
Username: fall16_FIUAccount (For instance, fall16_sguan005) Password: Your pantherID (For instance, 1234567)
• Off-Campus Instruction
If you are connecting to the servers using IP outside the campus, please start by doing the following
steps,
1. Open your pgAdmin
2. Click on the top left most button called “Add a connection to a server”
3. Click on the link on top called “SSH Tunnel”
4. Check the box called “Use SSH Tunneling” and choose password for Authentication.
5. Fill up the information as follows: Tunnel Host: ocelot.aul.fiu.edu
Username: Your FIU Account (For instance, sguan005)
Password: first name initial, pantherid, last name initial (For instance, s1234567g)
Page | 6
COP 5725 – Principles of Database Management Systems Professor Shu‐Ching Chen
Once the above‐‐‐mentioned steps are done, please follow the same steps mentioned in the On‐‐‐ Campus Instruction.
Page | 7