Case Study 4 - Perfect Pets
a Practice Called Perfect $\quad$

Case Study 4 - Perfect Pets
A practice called Perfect Pets provides private health care for domestic pets throughout America. This service is provided through various clinics located in the main cities of America. The Director of Perfect Pets is concerned that there is a lack of communication within the practice and particularly in the sharing of information and resources across the various clinics. To resolve this problem the Director has requested the creation of a centralized database system to assist in the more effective and efficient running of the practice. The Director has provided the following description of the current system.
Data Requirements
Veterinary Clinics
Perfect Pets has many veterinary clinics located in the main cities of America. The details of each clinic include the clinic number, clinic address (consisting of the street, city, state, and zipcode), and the telephone and fax numbers. Each clinic has a Manager and a number of staff (for example, vets, nurses, secretaries, cleaners). The clinic number is unique throughout the practice.
Staff
The details stored on each member of staff include the staff number, name (first and last), address (street, city, state, and zipcode), telephone number, date of birth, sex, social security number (SSN), position, and current annual salary. The staff number is unique throughout the practice.
Pet Owners
When a pet owner first contacts a clinic of Perfect Pets the details of the pet owner are recorded, which include an owner number, owner name (first name and last name), address (street, city, state, and zipcode), and home telephone number. The owner number is unique to a particular clinic.
Pets
The details of the pet requiring treatment are noted, which include a pet number, pet name, type of pet, description, date of birth (if unknown, an approximate date is recorded), date registered at clinic, current status (alive/deceased), and the details of the pet owner. The pet number is unique to a particular clinic.
Examinations
When a sick pet is brought to a clinic, the vet on duty examines the pet. The details of each examination are recorded and include an examination number, the date and time of the examination, the name of the vet, the pet number, pet name, and type of pet, and a full description of the examination results. The examination number is unique to a particular clinic. As a result of the examination, the vet may propose treatment(s) for the pet.
Treatments
Perfect Pets provides various treatments for all types of pets. These treatments are provided at a standard rate across all clinics. The details of each treatment include a treatment number, a full description of the treatment, and the cost to the pet owner. For example, treatments include:
T123 $\quad$ Penicillin antibiotic course $\quad$ $50.00
T155 $\quad$ Feline hysterectomy $\quad$ $200.00
T112 $\quad$ Vaccination course against feline flu $\quad$ $70.00
T56 $\quad$ Small dog - stay in pen per day (includes feeding) $\quad$ $20.00
A standard rate of $20.00 is charged for each examination, which is recorded as a type of treatment. The treatment number uniquely identifies each type of treatment and is used by all Perfect Pets clinics.
Pet Treatments
Based on the results of the examination of a sick pet, the vet may propose one or more types of treatment. For each type of treatment, the information recorded includes the examination number and date, the pet number, name and type, treatment number, description, quantity of each type of treatment, and date the treatment is to begin and end. Any additional comments on the provision of each type of treatment are also recorded.
Pens
In some cases, it's necessary for a sick pet to be admitted to the clinic. Each clinic has 20 - 30 animal pens, each capable of holding between one and four pets. Each pen has a unique pen number, capacity, and status (an indication of availability). The sick pet is allocated to a pen and the details of the pet, any treatment(s) required by the pet, and any additional comments about the care of the pet are recorded. The details of the pet's stay in the pen are also noted, which include a pen number, and the date the pet was put into and taken out of the pen. Depending on the pet's illness, there may be more than one pet in a pen at the same time. The pen number is unique to a particular clinic.
Invoices
The pet owner is responsible for the cost of the treatment given to a pet. The owner is invoiced for the treatment arising from each examination, and the details recorded on the invoice include the invoice number, invoice date, owner number, owner name and full address, pet number, pet name, and the details of the treatment given. The invoice provides the cost for each type of treatment and the total cost of all treatments given to the pet. Additional data is also recorded on the payment of the invoice, including the date the invoice was paid and the method of payment (for example, check, cash, visa). The invoice number is unique throughout the practice.
Surgical, Non-surgical, and Pharmaceutical Supplies
Each clinic maintains a stock of surgical supplies (for example, syringes, sterile dressings, bandages) and non-surgical supplies (for example, plastic bags, aprons, litter trays, pet name tags, pet food). The details of surgical and non-surgical supplies include the item number and name, item description, quantity in stock (this is ascertained on the last day of each month), reorder level, reorder quantity, and cost. The item number uniquely identifies each type of surgical or non-surgical supply. The item number is unique for each surgical or non-surgical item and used throughout the practice.
Each clinic also maintains a stock of pharmaceutical supplies (for example, antibiotics, pain killers). The details of pharmaceutical supplies include a drug number and name, description, dosage, method of administration, quantity in stock (this is ascertained on the last day of each month), reorder level, reorder quantity, and cost. The drug number uniquely identifies each type of pharmaceutical supply. The drug number is unique for each pharmaceutical supply and used throughout the practice.
Appointments
If the pet requires to be seen by the vet at a later date, the owner and pet are given an appointment. The details of an appointment are recorded and include an appointment number, owner number, owner name (first name and last name), home telephone number, the pet number, pet name, type of pet, and the appointment date and time. The appointment number is unique to a particular clinic.
Transaction Requirements
Listed below are the transactions that should be supported by the Perfect Pets database application.
1. The database should be capable of supporting the following maintenance transactions:
a) Create and maintain records recording the details of Perfect Pets clinics and the members of staff at each clinic.
b) Create and maintain records recording the details of pet owners.
c) Create and maintain the details of pets.
d) Create and maintain records recording the details of the types of treatments available for pets.
e) Create and maintain records recording the details of examinations and treatments given to pets.
f) Create and maintain records recording the details of invoices to pet owners for treatment to their pets.
g) Create and maintain records recording the details of surgical, non-surgical, and pharmaceutical supplies at each clinic.
h) Create and maintain records recording the details of pens available at each clinic and the allocation of pets to pens.
i) Create and maintain pet owner/pet appointments at each clinic.
2. The database should be capable of supporting the following example query transactions:
a) Present a report listing the Manager's name, clinic address, and telephone number for each clinic, ordered by clinic number.
b) Present a report listing the names and owner numbers of pet owners with the details of their pets.
c) List the historic details of examinations for a given pet.
d) List the details of the treatments provided to a pet based on the results of a given examination.
e) List the details of an unpaid invoice for a given pet owner.
f) Present a report on invoices that have not been paid by a given date, ordered by invoice number.
g) List the details of pens available on a given date for clinics in the New York area, ordered by clinic number.
h) Present a report that provides the total monthly salary for staff at each clinic, ordered by clinic number.
i) List the maximum, minimum and average cost for treatments.
j) List the total number of pets in each pet type, ordered by pet type.
k) Present a report of the names and staff numbers for all vets and nurses over 50 years old, ordered by staff name.
l) List the appointments for a given date and for a particular clinic.
m) List the total number of pens in each clinic, ordered by clinic number.
n) Present a report of the details of invoices for pet owners between 1997 to 1999, ordered by invoice number.
o) List the pet number, name, and description of pets owned by a particular owner.
p) Present a report listing the pharmaceutical supplies that need to be reordered at each clinic, ordered by clinic number.
q) List the total cost of the non-surgical and surgical supplies currently in stock at each clinic, ordered by clinic number.
-(a) Create a conceptual schema for Perfect Pets using the concepts of the Enhanced Entity-Relationship (EER) model. To simplify the diagram, only show entities, relationships and the primary key attributes. Specify the cardinality ratio and participation constraint of each relationship type. State any assumptions you make when creating the EER model (if necessary).
(b) Validate the conceptual data model.
(c) Map your high-level data model to a set of relational tables that represent the entity and relationship types. Identify primary, alternate, and foreign keys.
(d) Produce a physical database design for a relational DBMS you have access to. Implement this physical database design.