Detailed Report:
PerfectPets, a private healthcare service for domestic pets in America, operates through multiple clinics in major cities. The Director has identified a lack of communication within the practice and has requested the development of a centralized database system to enhance efficiency.
The current system includes information on clinics, staff, pet owners, pets, examinations, treatment costs, pet treatments, and pens. Each clinic has a unique clinic number and details such as address, telephone, and manager. Staff details include staff number, name, address, contact information, position, and salary. Pet owners are recorded with an owner number, name, address, and contact numbers. Pets have unique pet numbers, names, types, and details of the owner.
Examinations are conducted for sick pets, with details such as examination number, date, vet name, and results recorded. Treatments are provided at a standard rate, with unique treatment numbers and costs. Pet treatments are based on examination results and include details of treatment type, quantity, and dates. Pens in clinics have unique numbers, capacity, and status.
The database design should include an EERD diagram with all attributes, strong/weak entities, cardinality, and link relationships. Normalization up to 3NF should be achieved. SQL scripts are required to instantiate the database, populate tables, create constraints, and perform various queries such as displaying entities, generating reports on pets, staff, veterinarians, treatments, appointments, invoices, and more.
SQL Script Set:
1. Create and run script to instantiate the database
2. Create and run script to populate tables
3. Create and run constraints script
4. Load each table with at least 10 entities
5. Display all entities from each table
6. Display a report on Pet and Pet Owner
7. Display a report on Staff handling Pets
8. Display a report on Pet and Veterinarian for Examinations
9. Display a report on Pet, Veterinarian, and recommended treatments sorted by date
10. Display an appointment schedule with date, time, owner, and pet
11. Display a Pet-Owner invoice with relevant fees
12. Display Veterinarians, Pets, and Pet Owners by zipcode
13. Display Veterinarian information including name, address, DOB, medical school, years in practice, and previous clinics served
14. Display surgical, non-surgical, and pharmaceutical supplies for duplicate pet owners.