Question
PLEASE HELP You have been asked to develop a logical data model for University Medical Center based on the information given to you by the
PLEASE HELP
You have been asked to develop a logical data model for University Medical Center based on the information given to you by the new hospital administrator and their staff. You have accumulated the following entity, attribute, and relationship information shown in the table below. The attribute list may not be complete. If you determine that additional attributes are needed to better define an entity, then you should add them.
Entities Attributes and Relationships for University Medical Center (Parallel Lab Exercise):
Entity | Attributes | Relationships |
HealthcareWorker | HealthcareWorkerID, LastName, FirstName, SSN, Address, City, State, Zip, Phone Number, HealthcareWorkerTypeID | A healthcare worker can belong to any one of the three job categories, but can belong to one and only one of the three. healthcare worker has names and other contact information. |
HealthcareWorkerType | HealthcareWorkerTypeID, HealthcareWorkerTypeDesc, HourlyBillingRate | A healthcare worker can be either a physician (diagnoses, prescribes medication), nurse (provides physician-ordered treatments, administers medications), or a pharmacist (dispenses, delivers medication). |
ClinicLog | ClinicLogID, ClinicID, Login, Logout, HealthcareWorkerID | Patients may be treated by a healthcare worker at a clinic and can be tracked by the clinic log number. A healthcare worker must sign into the clinic before he or she can serve patients, and must sign out when finished treating patients at that clinic. A healthcare worker may serve portions of a shift at more than one clinic. |
Clinic | ClinicID, ClinicLocationDesc, AMAAccredNum | The hospital operates three clinical facilities: General Hospital, Midtown Clinic, and Urgent Care. |
InHomeCare | InHomeCareID, HealthcareWorkerID, DepartTime, ReturnTime. | Relates to both the healthcare worker and the prescription entities. This entity will help track provision of home healthcare. A healthcare worker can provide many homevisits but a prescription is administered on a home visit by one and only one healthcare worker. |
Method | MethodID, Method Description | Relates to medication and identifies the method of administration, for example, oral, injection, and so on. |
Medication | MedicationID, MedicationName, Dosage, Cost, QuantityOnHand, LastPurchasedDate, MethodID, ReorderMinimum, | Identifies the medication the hospital pharmacy dispenses. One medication can be dispensed per prescription. The quantity on hand allows Pharmacists to determine the inventory levels. The reorder minimum can be used to determine when the inventory level has reached a reorder point. |
Prescription | PrescriptionID, MedicationID, BillingAmount, TransactionDateTime, QuantityDispensed, InHomeCareID, ClinicLogID, PatientID | A prescription is identified by a single prescription order. Prescriptions are made to one or more patients but only one patient at a time; made by one or more pharmacists but only one pharmacist per prescription; recorded on one or more clinics but only one clinic per prescription; and administered by one or more nurses but no one prescription can be administered by more than one nurse. |
Patient | Patient Number, First Name, Last Name, Address, City, State, Zip, Phone Number | A patient can be associated with multiple prescriptions, but any one prescription is to one and only one patient. A prescription can occur without a patient registering in the system (e.g., an unconscious patent arrives by ambulance in the emergency room and receives life-saving emergency treatment). |
|
|
|
Using an appropriate drawing/data modelling tool, develop an ERD that meets the following guidelines.
1. Draw the entities with their attributes.
2. Indicate the relationships between the entities using Crow's Foot notation. You will need to determine the cardinality and optionality for each direction of the relationships. Some of the Foreign Key relationships are identified in the graph above but not all. Be sure you identify and account for all Foreign Key relationships.
3. Add a name (in both directions) to the relationships. Remember, if you can verbalize the relationship in both directions, then you probably have a valid relationship.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started