Question

The Mesa Veterinary Hospital is run by Dr. Brigitte Roosevelt. She has two employees in the office and has asked you to develop a database to help better track her data. Dr. Roosevelt currently uses her personal computer only for word processing, but she is interested in also using it to maintain pet histories and accounting information. She is excited about the transition and is counting on you to help her through the process. She describes her daily activities as follows:
When new customers come to Mesa Veterinary Hospital, the “owners” of the pets are required to complete an introductory form. This form includes the following:
• Owner name
• Address
• Day phone
• Night phone
They are also required to provide the following information about each pet, as some people own many pets:
• Pet name
• Breed
• Color
• Birth date
Dr. Roosevelt would like to enter this information once and then have the system retrieve it for all subsequent visits.
When customers call to make appointments, one of the office clerks asks what kind of services they require (e.g., is it a routine exam, a surgery, etc.). Dr. Roosevelt sees only one pet during each appointment. If she is going to see one owner’s two pets, then two separate appointments are necessary (but scheduled back-to-back). For each appointment, Dr. Roosevelt records the pet’s weight, notes the reason for the appointment, and records her diagnosis. Depending on the diagnosis, the doctor will possibly prescribe any number of medications to cure the pet.
Owners are charged $25 for each appointment and must pay additionally for any medications prescribed for their pets. Dr. Roosevelt requires all pets to be brought back for another examination prior to refilling any prescriptions. Customers must pay for services and medication in full at the conclusion of their visits.
You also learn that Dr. Roosevelt orders drugs and medications from several different suppliers. She places orders weekly, on Fridays. Suppliers usually make one shipment to fill each order, but sometimes have to make additional shipments if they are currently out of stock of one or more items. In such cases, they always ship the back-ordered item as soon as they receive it from the manufacturer; they never combine such back orders with subsequent orders by Dr. Roosevelt.
Suppliers bill Dr. Roosevelt monthly and expect payment in full by the 15th of the following month. A few suppliers do permit Dr. Roosevelt to make installment payments. The prices charged by suppliers for a given product may change several times during the year, so it is important to accurately store the cost of each item each time it is purchased.
Dr. Roosevelt concludes the interview by requesting that in addition to the facts mentioned,
she wants the system to store the following attributes:
• Number of pets owned by each customer
• Total charge for the appointment
• Prescription price
• Drug name
• Length of appointment
• Diagnosis
• Date of appointment
• Service requested

REQUIRED
a. Given this brief overview, draw an integrated REA diagram for the Mesa Veterinary Hospital and include cardinalities.
b. As directed by your instructor, either draw the tables necessary to implement the integrated REA diagram you developed for the Mesa Veterinary Hospital or build the tables in a relational DBMS to which you have access. Be sure to include all attributes from the narrative plus the additional ones explicitly listed by Dr. Roosevelt at the conclusion of the interview. Create additional attributes only if necessary.



$1.99
Sales0
Views138
Comments0
  • CreatedDecember 19, 2014
  • Files Included
Post your question
5000