(Solved): Title: Case Study: Pet9 Description: Pet9 is a pet services salon that offers professional, cage-f ...

Title: Case Study: Pet9

Description: Pet9 is a pet services salon that offers professional, cage-free services to domestic pets,
particularly dogs and cats. The staff at Pet9 are well-trained in different specialised services,
including washing, pet grooming, tidy-up, and massages. Pet9 is well known for using
environmentally friendly products in their services. Each visit is charged based on the
service, pet type and pet size. Customers can choose either a junior, senior, or professional
staff member to provide the services. An invoice is issued for each visit.

The following entity-relationship diagram (ERD) shows the data stored in Pet9’s operational
database:

Pet 9 is a pet services salon that offers professional, cage-free services to domestic pets, particularly dogs and cats. The staff at Pet9 are well-trained in different specialised services, including washing, pet grooming, tidy-up, and massages. Pet 9 is well known for using environmentally friendly products in their services. Each visit is charged based on the service, pet type and pet size. Customers can choose either a junior, senior, or professional staff member to provide the services. An invoice is issued for each visit. The following entity-relationship diagram (ERD) shows the data stored in Pet9's operational database: (Note: Customer_ID is the FK for the Pet table. The original typo error(PK) has been removed) After being in operation for more than 5 years, the management at Pet9 would like to build a data warehouse to analyse their business performance. The data warehouse should provide the following information: 1. The number of visits by seasons and by pet types, 2. The number of visits handled by different levels of staff expertise, (Note: level of staff expertise is based on the Staff level, e.g.: Junior,Senior, etc) 3. The number of visits by different products, 4. The total income by services, for different pet types, and 5. The total income by different products. The seasons are defined as: summer (December to February), autumn (March to May), winter (June to August) and spring (September to November). You are required to design a data warehouse for Pet 9 . The operational database can be accessed through the Pet9 account. You can, for example, execute the following query: select * from Pet9. ; Example: select * from Pet9. Product; Tasks: [1]. Develop a star schema named Pet 9 DW. Identify the fact table, dimensions, and attributes required to support the schema. The result of this task is a star schema diagram. If you are using a bridge table, make sure to include the weight factor and list aggregate attributes. You can use any drawing tool, such as Lucidchart or draw.io, to draw the star schema. [2]. Validate your star schema using the two-column table methodology. You are required to illustrate the two-column tables for relevant fact measures and dimensions in your star schema design. [3]. Write the SQL commands to create the fact and dimension tables. You must create a runnable script file containing the appropriate \( \mathrm{SQL} \) commands to create the fact and dimension tables. Each dimension must have an ID or a surrogate key. The operational database tables are accessible through the Pet 9 account. The results of this task are the SQL commands. You will also need to show the contents of the tables you have created. [4]. Write the SQL commands to answer the following queries. For each of the following queries, write the SQL and show the results: a) The number of visits by season and by pet types, b) The number of visits handled by different levels of staff expertise, (Note: level of staff expertise is based on the Staff level, e.g.: Junior,Senior, etc) c) The number of visits by different products, d) The total income by services, for different pet types, and e) The total income by different products.