CS460 - Modeling with EER

Project #1 - Pharmacy

Due on Feb 27

Small pharmacy hired you to model their business. They want you to track patients that fill their prescriptions, the insurance cards that patients have, doctors who wrote the prescriptions, medications that were prescribed, and pharmacists who filled/refilled prescriptions. Each prescription is written for one medication, by one physician, and given to one patient. It is filled in by one pharmacist when there is no refill. Some prescriptions have specified number of refills. Each time a different pharmacist may refill the prescription in such case. The date and time of the filling / refilling must be recorded. The label and receipt are printed during each fill/refill. Label is attached to the medication that is issued to patient and receipt is given to him/her for insurance or tax purposes. Label should include RX number, Doctor Name, Patient Name, Patient Address, Directions (how to use the medication), Strength in mg or ml, Quantity, Pharmacist Name, Date Filled, Original Date of Issuing prescription, Previous Date for refills, and #Refills (Number of Refills Remaining). Occasionally, pharmacist has to call doctor to consult him about filling or refilling the prescription and that must also be recorded as “Doctor Consulted”. Each patient has an insurance card issued specifically for him/her. It provides information about CardID, Insurance Company, Insurance Type, and Copay. For patients, keep track of their ID, First and Last Name, Gender and Date of birth. Each prescription has RX number, Date, Drug name, Strength, Dosage, Number of Refills, and SubstitutionAllowed ( with values true or false that indicate whether a substitution of brand name drug with generic one is allowed. ) A prescription is for medication that can be brand name or generic drug. Drugs come in various forms such as pills, tablets, capsules, liquid or cream. For drug record its Name, Form, Strength. It is also important to prevent allergic reactions that are caused by drug interactions. When one drug may cause allergic reaction if taken concurrently with another drug, such interaction is recorded in a database, so that pharmacist can prevent filling a prescriptions if patient already takes another drug that may provoke an allergic reaction. Pharmacist may also consult doctor who prescribed a medication to determine if such fill or refill is still O.K despite the possible or actual allergic reaction and act upon doctors advice. One drug may have interaction with several other drugs. For physician record his First and Last Name, Physician’s Specialization, Phones (office phone, home phone and cellular phone), Office address, and HMO.

The following entities should be included in your model:

PHYSICIAN, PATIENT, PRESCRIPTION, DRUG, PHARMACIST, INSURANCE CARD

Track the following binary relationships:

• PATIENT has been issued a PRESCRIPTION
• PRESCRIPTION has DRUG prescribed
• PRESCRIPTION is filled/refilled by PHARMACIST
• PRESCRIPTION is prescribed by PHYSICIAN
• PATIENT has INSURANCE CARD
• DRUG has interaction that may cause an allergic reaction with another DRUG

For each of the above relationships specify English sentences that provide information about the business and justify the minimum and maximum cardinalities on the model. This should be consistent with what is told about the Pharmacy business and with the way how your E-R diagram looks like. Some cardinalities may not be evident from the above description. In such a case you may specify the business rules for this PHARMACY miniworld.

Design EER diagram. Show the entities as rectangles, attributes as ovals and relationships as diamonds. Relationships should be represented with solid lines with English words specifying the meaning (semantics) of the relationship. To specify the cardinalities use (min, max) way of modeling.

Submit

• Business rules that justify the choice of cardinalities.
• E-R diagram

Diagram and business rules must be typed. The software used to produce the diagrams is your choice. It can be plain Paint or Word or Excel drawing tools or ERWin, Visio, Designer.

Cover page should include the following table

CS460 Database Spring 2008	Project#1
First and last name
Date due
Date submitted
Grade
Design 80%
Business Rules (cardinalities) 20%