Posted: December 8th, 2013
Database ERD
This assignment is to be done in groups of 2 or 3 students. Your group is to prepare a conceptual data model (consisting of an Entity Relationship Diagram and attribute lists) for the RxPlus Pharmacy described below.
All Many-to-Many relationships must be shown as two 1-to-Many relationships using a bridge table. Bridge tables are also called composite tables and concatenated tables. For Primary Keys please write (PK) after the attribute name. For Foreign Keys please write (FK) after the attribute name.
Here is an example of a M:N relationship from a previous assignment: In this example we have DRIVER to CAR is a Many-to-Many.
|
DRIVER
|
|
LicenseNo(PK)
|
|
DriverName
|
 Address |
 |
|||
|
|||
|
|||
 |
|||
|
CAR
|
|
CarNumber(PK)
|
|
Make
|
|
EngineSize
|
|
Kilometers
|
|
DRIVER-CAR
|
|
LicenseNo(PK)(FK)
|
|
CarNumber(PK)(FK)
|
RxPlus Pharmacies Prescription Database
RxPlus is a pharmacy that has offered to give you a $15000 to design a database for them. You are not too busy so you agree.
Here is the information that you gather:
Patient information includes names, addresses, and birth dates. A patient must exist in order to add a prescription to the database. Doctor information includes name and phone number. Each doctor has at least one specialty(such as pediatrics or orthopedics) but some doctors have more than one specialty.
Each prescription is for one or more drugs.All prescriptions must be kept on the database forever for history and tracking purposes. For each drug the prescription is for one or more drugs and has the following information: the name of the drug, the form, the milligrams per unit and the instructions for use. For example: the drug name might be Celebrex and the form might be pills and the dose might be 50 milligrams per pill and the doctor’s instructions might be: “Take two pills before bed”. Other forms are capsules, liquid or lozenges. There is a different drug# for each form of any particular drug. Some of the drugs in stock have never been dispensed in prescriptions. The store has a name, an address, and a phone number.
The pharmacy sells many drugs. Please ensure that your database can support a drug in more than one form. For example: Buckley’s Cough Medicine is sold as a liquid or lozenges. For each form the database must track the unit_price and the quantity in stock.
Doctors prescribe drugs for patients. Each prescription has a unique number called the RxNumber, a patient name, a date, number of refills allowed and a prescribing doctor. If the number of refills is 3, the patient can get a fresh supply of the prescribed drugs 3 times without getting another prescription from the doctor.
1- Prepare a data model (consisting of an Entity Relationship Diagram with attribute lists) for RxPlus. The ERD must be drawn using Access, Visio, Word or some other software product. List the names of all group members on the front page of the assignment. Remember: many-to-many relationships must be shown using three tables like the example on the preceding page of this assignment.
Expert paper writers are just a few clicks away
Place an order in 3 easy steps. Takes less than 5 mins.