Based on your start schema in a. , write a SQL query displaying the pet's name, pet type, treatment name, visit day and visit fees for all the pets who visited the hospital in October 2020.
A local veterinarian hospital keeps record about the different pets they see over time, as well as information about the owners and treatment provided.
The following tables represent a sample of the information recorded about owners, pets, and treatments.
Owners
|
LastName |
FirstName |
Phone |
|
Downs |
Marsha |
555-537-8765 |
|
James |
Richard |
555-537-7654 |
|
Frier |
Liz |
555-537-6543 |
Pets
|
PetName |
Type |
Breed |
Weight |
|
King |
Dog |
Std. Poodle |
25.5 |
|
Teddy |
Cat |
Cashmere |
10.5 |
|
Fido |
Dog |
Std. Poodle |
28.5 |
|
King |
Horse |
Border Collie |
25.0 |
Treatments
|
Name |
Anesthesia Required |
|
Vaccine |
No |
|
Dental |
Yes |
|
Surgery |
Yes |
A veterinarian hospital wants to set up a data warehouse containing various information about the business.
One section of this data warehouse should focus on recording information related to each pet visit to the hospital.
For each of the visit, it is essential to record the various information represented in the previous tables about the pet, owner, and treatment.
It is also essential to record data about the date of the visit, including the day, month, and year.
Finally, the visit fee should be recorded. Note that there is no flat fee for each treatment, as the treatment cost can vary depending on the pet type.
Based on all this information, draw a star schema for this specific part of data warehouse, focusing on recording data related to each visit.
About four hundred days of visit data are stored in the data warehouse, relating to at most one hundred pets and a maximum of eighty owners. Knowing that the veterinarian hospital has twenty different treatments, estimate the maximum number of rows in the fact table of the star schema in question a.
Based on your start schema in a. , write a SQL query displaying the pet's name, pet type, treatment name, visit day and visit fees for all the pets who visited the hospital in October 2020.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
