PostgreSQL的数据库作业。作业给了一个庞大的医院的数据模型，包括病人，医生，护士，供应商，各科室，医药厂，药品库，实验室等等场景。
Part1是画这个数据模型的E-R图，Part2是用DDL把这二十多张表编写出来，工作量非常庞大。
Database
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.
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 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.
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
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:
- Indicate the primary key in each table.
- Define the data type for each attribute.
- Show the one‐to‐one, one‐to‐many, and many‐to‐many relationships using notation from class notes among all the tables.
- Each table should have at least five records.
- 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.
