General Manager (GM) of Austin Hospital, Inc (AHI) has recruited your team as consultant to design and document a database to manage hospital data AHI has 5 hospitals in Texas that employs physicians who provide consultation to patients After meeting with the GM, hospital administrators and employees your team developed below given specifications and business rules to develop the ERD model and documentation Specifications and business rules For each hospital, the following data needs to be recorded Hospital Number, Location (Street Address, City, State, Zip Code), Phone Number Each hospital is assigned a unique hospital number A patient is an individual who visits a physician Each patient has a patient ID (unique identifier), first name, middle name, last name, gender, contact phone number, social security number, health insurance ID, and health insurance company (e g , Blue Cross Blue Shield Cigna) A patient may have no health insurance or have health insurance from at most one insurance company Each health insurance company can be used by one or more patients For each health insurance company, AHI records company ID, name, phone number, fax number, and e mail Each health insurance company is identified by its company ID A physician provides consultation to patients during visits and may diagnose medical conditions and prescribe medical treatments Each physician has a physician ID (unique identifier), first name, middle name, last name, gender, contact phone number, and specialty Each physician works in one hospital However, each hospital can have more than one physician A patient can make one or more appointments with one or more physicians at a hospital However, each appointment is associated with one physician and one patient For each appointment, AHI records appointment date, time, patient, physician, and hospital Each appointment is identified by its appointment ID Each physician can be associated with zero or many appointments and each patient can be associated with one or many appointments If kept, an appointment yields a visit with the physician specified in the appointment For each patient visit with a physician, the hospital records the examination room number, visit start time, and visit end time Each visit is identified by visit ID The visit with physician may yield zero or more condition diagnosis and, when appropriate, orders (treatment) from physician Although, nurse is not needed during most of the patient visits if necessary, a nurse can assist the physician during a visit Each nurse can assist during zero or many visits Each nurse has a nurse ID (unique identifier), first name, middle name, last name, gender, and contact phone number During a visit with a patient, a physician may diagnose zero or more conditions affecting the patient and a diagnosis may apply to many patients The hospital requires recording of the following information for each condition date and time of diagnosis, diagnosis code, and a brief description Diagnosis is a patients medical condition diagnosed by a physician Each diagnosis has a diagnosis code (unique identifier) and diagnosis name AHI is using ICD 10 diagnosis codes for patient conditions (e g , 00 50 is code for Staph Food Poisoning 173 3 is code for Basal Cell Carcinoma 776 5 is code for Congenital Anemia) For each condition diagnosed during a visit, physician may write zero or more orders Order is any treatment prescribed by a physician such as diagnostic tests (radiology, laboratory), therapeutic procedures (physical therapy, diet restrictions) or drugs (prescriptions) Each order has an order ID (unique identifier), date and time, and a prescribed treatment Treatment is any test, procedure or drug ordered by a physician for a patient to treat condition diagnosed during a visit Each treatment has a treatment ID (unique identifier) and treatment name using standardized names Based on this information, create an ERD model Clearly show all relationships, primary key attributes, and foreign key attributes in the ERD model All attributes in the entities must be in their atomic form You may define surrogate keys and additional attributes needed for entities Please indicate any additional assumptions your team has made (Note Comparative grading will be used to assess the quality of the ERD model )

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 24, 2024

General Manager (GM) of Austin Hospital, Inc. (AHI) has recruited your team as consultant to design and document a database to manage hospital data. AHI

General Manager (GM) of Austin Hospital, Inc. (AHI) has recruited your team as consultant to design and document a database to manage hospital data. AHI has 5 hospitals in Texas that employs physicians who provide consultation to patients. After meeting with the GM, hospital administrators and employees your team developed below given specifications and business rules to develop the ERD model and documentation.

Specifications and business rules:

For each hospital, the following data needs to be recorded: Hospital Number, Location (Street Address, City, State, Zip Code), Phone Number. Each hospital is assigned a unique hospital number.
A patient is an individual who visits a physician. Each patient has a patient ID (unique identifier), first name, middle name, last name, gender, contact phone number, social security number, health insurance ID, and health insurance company (e.g., Blue Cross Blue Shield; Cigna). A patient may have no health insurance or have health insurance from at most one insurance company. Each health

insurance company can be used by one or more patients. For each health insurance company, AHI records company ID, name, phone number, fax number, and e-mail. Each health insurance company is identified by its company ID.
A physician provides consultation to patients during visits and may diagnose medical conditions and prescribe medical treatments. Each physician has a physician ID (unique identifier), first name, middle name, last name, gender, contact phone number, and specialty.
Each physician works in one hospital. However, each hospital can have more than one physician.
A patient can make one or more appointments with one or more physicians at a hospital. However, each appointment is associated with one physician and one patient. For each appointment, AHI records appointment date, time, patient, physician, and hospital. Each appointment is identified by

its appointment ID.
Each physician can be associated with zero or many appointments and each patient can be associated

with one or many appointments.
If kept, an appointment yields a visit with the physician specified in the appointment. For each

patient visit with a physician, the hospital records the examination room number, visit start time, and visit end time. Each visit is identified by visit ID. The visit with physician may yield zero or more condition diagnosis and, when appropriate, orders (treatment) from physician.
Although, nurse is not needed during most of the patient visits; if necessary, a nurse can assist the physician during a visit. Each nurse can assist during zero or many visits. Each nurse has a nurse ID

(unique identifier), first name, middle name, last name, gender, and contact phone number.
During a visit with a patient, a physician may diagnose zero or more conditions affecting the patient and a diagnosis may apply to many patients. The hospital requires recording of the following information for each condition: date and time of diagnosis, diagnosis code, and a brief description.
Diagnosis is a patients medical condition diagnosed by a physician. Each diagnosis has a diagnosis code (unique identifier) and diagnosis name. AHI is using ICD-10 diagnosis codes for patient conditions (e.g., 00.50 is code for Staph Food Poisoning; 173.3 is code for Basal Cell Carcinoma; 776.5 is code for Congenital Anemia).
For each condition diagnosed during a visit, physician may write zero or more orders. Order is any treatment prescribed by a physician such as diagnostic tests (radiology, laboratory), therapeutic procedures (physical therapy, diet restrictions) or drugs (prescriptions). Each order has an order ID (unique identifier), date and time, and a prescribed treatment.
Treatment is any test, procedure or drug ordered by a physician for a patient to treat condition diagnosed during a visit. Each treatment has a treatment ID (unique identifier) and treatment name using standardized names.

Based on this information, create an ERD model. Clearly show all relationships, primary key attributes, and foreign key attributes in the ERD model. All attributes in the entities must be in their atomic form. You may define surrogate keys and additional attributes needed for entities. Please indicate any additional assumptions your team has made. (Note Comparative grading will be used to assess the quality of the ERD model.)