A veterinary clinic wants to create a relational database to store the details of the animal patients, their appointments, the veterinarians that treat the animals, and the treatments provided.
Functional requirements:
It will be necessary to store details about the veterinarians who staff the clinic: unique ID, name, address (composed of street address, suburb, city, and zip code), home phone, and clinic extension number.
Details are recorded about the animal patients: patient number, registration number (if known), animal type (e.g., dog, cat, fish, etc.), description, age (where known), and name.
As patient owners pay the bills, it is also necessary to store their details: unique ID, name, address (composed of street address, suburb, city, and postcode), home phone, and business phone.
Every effort is made to recognize animal patients on return visits and use the same patient number even though the owner may vary over time. Varying ownership happens since the clinic also provides pet adoption services.
Appointments are made for veterinarians to attend an animal patient. Thus, it is necessary to record the date and time of each appointment and any descriptive comments.
All appointments incur a treatment charge. The details of treatments carried out during appointments must be recorded for the patient history and charging purposes.
Treatments vary from grooming and general check-ups to complex operations. The clinic wishes to store the following information about treatments: treatment ID, description, and charge. Charges are regularly reviewed and changed over time.
Sometimes an appointment is made for a ‘follow-up’ of a previous appointment. It is necessary to be able to identify follow-up appointments and the appointment that they follow, as they are charged at a reduced rate.
Non-functional requirements:
Design your data model optimized for storage by complying with the rules of the Third Normal Form.