Assignment
The Cyclone Veterinary Hospital is run by CEO, Dr. Sue Grainger, and her associate veterinarians. She has five employees in the office and has asked you to develop a database to help better track her data. Dr. Grainger 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 a customer calls to make appointments, one of the office clerks complete an appointment form. The form includes appointment number, pet parent identification number, pet identification number, appointment date, and services they require (e.g., routine exam, vaccination, a surgery, etc.) along with any specials needs for each service. If the customer is a new customer, the "parents" of the pets (hereafter, pet parent) are required to complete a customer form. This customer form includes parent identification number, parent name, address, day phone, night phone, and email. They are also required to provide the information about each pet, as some people own many pets: pet identification number, pet name, breed, color, and birth date. In addition, after the appointment process is completed, an appointment confirmation is generated and sent to the pet parents. Each pet patent can make several appointments if the parent has more than one pet. However, a separate appointment should be made for each pet. Also, an appointment can be made for more than one service. The Cyclone Veterinary Hospital allow its veterinarians to examine only one pet during each appointment. Therefore, if the parent has more than one pet, separate examination is necessary for each pet. When the pet parents and their pets visit the Cyclone Veterinary Hospital on their appointment day, the medical records of the pets (i.e., service history) are forwarded to an associate veterinarian. The veterinarian then examines each pet and, depending on the diagnosis, prescribes any number of medications to cure the pet. In addition, the veterinarian completes a pet examination form. The form includes exam number, weight, height,heartbeat, temperature, symptom, actual hours spent for each service, diagnosis results of each service, and the quantity of each medication, if any.
CnolcycAt the end of each week, an accounting clerk prepares invoices for pet parents who visited the Cyc Veterinary Hospital during the week and sends the invoices to them. Pet parents are charged $30 for each appointment, and must pay additionally for any medications prescribed for their pets. The Cyclone Veterinary Hospital requires all pets to be brought back for another examination prior to refilling any prescriptions. Pet parents have 30 days to pay for each examination, but they must pay in full for each examination. In addition, pet parents are allowed to pay off several examinations with one payment.
When a payment is received, an accounting clerk prepares a payment receipt form for amounts received from each pet parent by verifying appointment form, pet examination form, and invoice. The form includes receipt number, receipt date, and amount received.
The form is then forwarded to Dr. Grainger. When Dr. Grainger authorizes the payment receipt form, the accounting clerk deposits the checks into a bank, Cyclone Trust Bank. Finally, a monthly summary report of pet examinations and payments is prepared for each month and forwarded to Dr. Grainger.
• Prepare an REA diagram describing the Cyclone Veterinary Hospital's database. You may repeat a box for an individual entity if necessary to make your drawing neat. Dr. Grainger would like for you to consider that, eventually, as new hospitals are opened, she will have a hospital manager in each hospital that will perform the tasks she now performs. As a result, the system should be planned to always store information about pet parents, pets, and employees associated with transactions. Note: Use typical cardinalities if business policies are not clear. You also need to show the relationship between pet and the parents of the pet in your REA diagram.
• Prepare the context diagram and the level 0 DFD. Hints: There are approximately four sources/destinations (Pet Parent, Associate Veterinarian, Dr. Grainger, and Cyclone Trust Bank), four processes (Receive Appointment, Examine Pet, Receive Payment, and Generate Report), and five data stores (i.e., Pet Parent, Pet, Appointment, Service, and Cash).