数据库代写｜FIT3003 S2 2022 Take Home Test

这是一篇来自澳洲的数据库代写

LO1. Design multi-dimensional databases and data warehouses.

LO2. Use fact and dimensional modelling.

Case Study: Pet9

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:

(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:

(Note: level of staff expertise is based on the Staff level, e.g.: Junior,Senior, etc)

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 Pet9. The operational database can be accessed through the Pet9 account. You can, for example, execute the following query:

select * from Pet9.<table_name>;

Example: select * from Pet9.Product;

Tasks:

[1]. Develop a star schema named Pet9_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 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 Pet9 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.

i.The star schema diagram – Task 1

ii.The Two-Column Table Methodology illustration – Task 2

iii.The SQL commands to create all dimension and fact tables, as well as the contents of these tables (the contents can be in the form of screenshots) – Task 3

iv.The SQL commands to answer the queries in Task 4 and the query results. You can take screenshots of the results.

i.The SQL commands to create the data warehouse.

ii.The SQL commands to answer the queries in Task 4.

(Note: The marker will run the .sql file. You need to ensure that the SQL commands are written correctly and clearly with proper comments in the .sql file and runnable without errors).

Zero marks will be given for the implementation if

i.Save all .sql files from [B] and in one folder.

ii.Name the folder THT_SQL_StudentID.

iii.ZIP the folder to THT_ SQL_StudentID.zip. This must be a ZIP file and not  other types of compressed folders. The zip file should contain the prescribed files as listed in the Submission Checklist.

Submission Method:

Submission cut-off time: Monday, 29 August 2022, 11:55 PM. The submission link will be unavailable after the cut-off date.