首页 > 其他分享 >FIT5137 M-Stay Residential service

FIT5137 M-Stay Residential service

时间:2024-09-14 18:45:53浏览次数:10  
标签:task service Residential Number ID table FIT5137 data your

FIT5137 Assignment 2 -S2 2024  (Weight = 40%)

Due - Friday, 20 September 2024, 4:30 PM

General Information and Submission

o This is an individual assignment.

Submission method: Submission is online through Moodle.

Penalty for late submission: 5% deduction for each day.

Assignment FAQ: There is an Assignment Frequently Asked Questions page set up for the Assignment 2 on EdStem Forum.

Problem Description

M-Stay is a residential service that offers homestay and rental services to Monash students and staff around Melbourne. The company has an existing operational database that maintains and stores all of the business transactions information (e.g. properties, hosts, listings, booking, etc.) required for the management's daily operation. As the business grows, M-Stay has decided to build a Data Warehouse to improve their analysis and work efficiency. However, since the staff at M-Stay have limited Business Intelligence and Data Warehouse knowledge, they have decided to hire you to design, develop and quickly generate BI reports from a Data Warehouse.

The operational database tables can be found at the MStay account. You can, for example, execute the following query:

select * from MStay.<table_name>;

The data definition of each table in MStay is as follows:

Table Name

Attributes,Data Types and Key Constraints

Notes

REVIEW

Review_ID

Number

(PK)

The table stores review information of the related booking order.

Review_Date

Date

Review_Comment

Varchar2

Booking_ID

Number

(FK)

BOOKING

Booking_ID

Number

(PK)

The table stores booking information.

Booking_Date

Date

Booking_Stay_Start_Date

Date

Booking_Duration

Number

Booking_Cost

Number

Booking_Num_Guests

Number

Listing_ID

Number

(FK)

Guest_ID

Number

(FK)

GUEST

Guest_ID

Number

(PK)

The table stores all guest information.

Guest_Name

Varchar2

LISTING

Listing_ID

Number

(PK)

The table stores all listing information. Each listing has one property and one host information.

Listing_Date

Date

Listing_Title

Varchar2

Listing_Price

Number

Listing_Min_Nights

Number

Listing_Max_Nights

Number

Prop_ID

Number

(FK)

Type_ID

Number

(FK)

Host_ID

Number

(FK)

HOST

Host_ID

Number

(PK)

The table stores all host information.

Host_Name

Varchar2

Host_Since

Date

Host_Location

Varchar2

Host_About

Varchar2

Host_Listing_Count

Number

HOST_VERIFICATION

Host_ID

Number

(PF)

The table stores the verification information between host and channel.

Channel_ID

Number

(PF)

CHANNEL

Channel_ID

Number

(PK)

The table stores the channel of verification for the hosts.

Channel_Name

Varchar2

LISTING_TYPE

Type_ID

Number

(PK)

The table stores all listing types.

Type_Description

Varchar2

A. Transformation Stage

The first stage of this assignment is divided into TWO main tasks:

1. Design a data warehouse for the above M-Stay database.

You are required to create a data warehouse for the M-Stay database.

The management is especially interested in the following indicators :

● Number of reviews

● Number of listings

● Average booking cost

The following is a list of dimension attributes that you should include in your data warehouse:

● Listing type

● Listing time [Month, Year]

● Listing season

o (Spring:9 to 11, Summer: 12 to 2, Autumn: 3 to 5 and Winter: 6 to 8)

● Listing maximum stay duration [short-term: less than 14 nights, medium-term: 14 to 30 nights, long-term: more than 30 nights]

● Listing price range [low: less than $100, medium: $100 to $200, high: more than $200]

● Channels

● Booking duration [short-term: less than 30 nights, medium-term: 30 to 90 nights, long-term: more than 90 nights]

● Review time [Month, Year]

● Booking cost range [low: less than $5000, medium: $5000 to $10000, high: more than $10000]

For the attribute, ensure that it meets the requirements of the range or group specified in your submission, if required in the specification.

Preparation stage.

Before you start designing the data wa代写FIT5137  M-Stay Residential servicerehouse, you have to ensure that you have explored the operational database and have done sufficient data cleaning. Once you have done the data cleaning process, you are required to explain what strategies you have taken to explore and clean the data.

The outputs of this task for Report are:

a) If you have done the data cleaning process, explain the strategies you used in this process (you need to show the SQL to explore the operational database and SQL of the data cleaning, as well as the screenshot of data before and after data cleaning).

Designing the data warehouse by drawing star/snowflake schema.

Design task A:

The star schema for this data warehouse may contains multi-facts. You need to identify the fact measures, dimensions, and attributes of the star/snowflake schema. The following queries might help you to determine the fact measures and dimensions:

● How many long-term stay duration listings are listed on Facebook?

● How many listings are listed in June 2015?

● How many listings are there in summer for an “Entire home/apt” in a medium price range?

● How much is the average booking cost in March 2013?

● How many bookings were there for “Private rooms” with a short-term stay duration in 2015?

● How many high-cost bookings were made in April 2014?

● How many reviews were given in February 2016?

Note: the star schema you created in Design Task A as the highest level of aggregation

Design task B:

In this assignment, consider the star schema you created in Design Task A as the highest level of aggregation. The M-Stay company manager wants to implement a drill-down function to explore more detailed information. Your task is to suggest several ways to increase the granularity of your fact tables from Design Task A. In other words, the manager wants to decrease the aggregation level of the fact tables you created in Design Task A.

The outputs of task A & B for Report are:

b) A  star/snowflake schema diagrams for design task A. (You can use Lucidchart to draw the star schema.)

c) List suggestion of  increase the granularity of your fact tables for design task B

2. Implement design task A star/snowflake schema using SQL.

锚点You are required to implement the star/snowflake schema that you have drawn in design task A. This implies that you need to create the fact and dimension tables in SQL. The output is a series of SQL statements to perform. this task. You will also need to show that this task has been carried out successfully.

锚点Note:

锚点● If your account is full, you will need to drop all of the tables that you have previously created during the tutorials.

锚点● If you have dropped all tables in your account and you still encounter the ORA-01536: space quota exceeded for tablesace ‘TABLE_NAME’, please check your SQL code whether you have properly joined all tables. This issue was mainly caused when you did not do the table join properly as the number of records multiplied during the process.

The outputs of this task for Report are:

a) Screenshots of the table structure you created for Design Task A, including the dimension tables and fact tables.

A sample of screenshots of the table structure

 

B. Data Analytic Stage

 

Conduct a data analysis using the star schema you created in Design Task A by writing SQL queries to explore the data further. Present your findings in a clear and concise manner, demonstrating your understanding of the dataset and highlighting any noteworthy observations or patterns.

The outputs of this task for Report are:

1. Findings report: A detailed explanation of your findings, including any significant observations or patterns identified during the analysis.

Submission Checklist

 

 

Step 1: Report (25% of the total score)

 

A combined pdf file save as: YourstudentID_A2_report.pdf, containing all of the above tasks:

A. Cover page

B. If you have done the data cleaning process, explain the strategies you used in this process (you need to show the SQL to explore the operational database and SQL of the data cleaning, as well as the screenshot of data before and after data cleaning). Note that you are only required to find around 5 (five) data errors for this stage.

C. A  star/snowflake schema diagrams for design task A

D. List suggestion of  increase the granularity of your fact tables for design task B

E. Screenshots of the table structure you created for Design Task A only, including the dimension table and fact tables.

a. SQL file for creating the star/snowflake schema is NOT required in submission

F. Findings report: A detailed explanation of your findings, including any significant observations or patterns identified during the analysis.

 

Step 2: Poster (35% of the total score)

 

One page standard A4 poster in PDF format to save as: YourstudentID_A2_poster.pdf

 

Extract key information from the report you created and present it in a one-page poster. The poster must be in standard A4 size and in PDF format, which can be either landscape or portrait. The content should be clear and easy to understand. Avoid using technical jargon or complex language. Review the poster before submission to ensure it effectively communicates the key messages of your report.

Note:

Ensure the poster content is consistent with the key structure and findings of your report, and choose an appropriate layout that effectively organizes the information in a clear and logical manner. Maintain a good balance of text and visuals to enhance readability, and ensure all visuals are relevant and support the content of the poster. Label all visuals clearly and provide captions where necessary. Avoid overcrowding the poster with too much text or too many visuals, and ensure the poster is free of any grammatical or typographical errors.

Key guidance of design a poster:

● What is the main theme/objective of the poster that you want to express?

● Who is your target audience for this poster?

● Do you really need all the details from your report on this poster?

Step 3: Video presentation (40% of the total score)

A five minute video presentation in mp4 format save as: YourstudentID_A2_video.mp4

Based on the report and poster you have created, present your design and findings in a five-minute video presentation. Ensure you thoroughly understand both the report and the poster to effectively extract and communicate the key points.

标签:task,service,Residential,Number,ID,table,FIT5137,data,your
From: https://www.cnblogs.com/qq---99515681/p/18414542

相关文章

  • 使用 Microsoft.Extensions.ServiceDiscovery 进行服务发现并调用
    简介在现代微服务架构中,服务发现(ServiceDiscovery)是一项关键功能。它允许微服务动态地找到彼此,而无需依赖硬编码的地址。以前如果你搜.NETServiceDiscovery,大概率会搜到一大堆Eureka,Consul等的文章。现在微软为我们带来了一个官方的包:Microsoft.Extensions.ServiceDiscovery......
  • k8s 中的 Service 简介【k8s 系列之二】
    〇、前言k8s集群中的每一个Pod都有自己的IP地址,那么是不是有IP了,访问起来就简单了呢,其实不然。因为在k8s中Pod不是持久性的,摧毁重建将获得新的IP,客户端通过会变更IP来访问显然不合理。另外Pod还经常会通过多个副本来实现负载均衡,客户端如何高效的访问哪个副本的......
  • Android Service服务使用方法
    启动服务的方法我们要隐式启动一个Service,首先我们需要配置AndroidMainfest.xml<serviceandroid:name=".MyAsdlService"><intent-filter><actionandroid:name="com.example.myasdlservice"/></int......
  • Zabbix-Scheduled reports - Cannot connect to web service
    最近使用zabbix创建SchedulReport,完成相关配置进行测试时,总是提示创建失败:Cannotconnecttowebservice:couldn'tconnecttoserverCannotconnecttowebservice:couldn'tconnecttoserver而且执行cat/var/log/zabbix/zabbix_web_service.log查看zabbix_web_ser......
  • 【Azure Service Bus】批量处理Service Bus Topic 中的死信消息(dead-lettered messag
    问题描述在Azure的门户页面上,因为ServiceBusTopic中有很多dead-letteredmessage,而这些消息占用了大量的存储空间,通过门户上的ServiceBusExplorer每次只能消费一条消息。虽然可以通过修改代码来指定消费私信队列中消息,但是需要修改代码,需要一些工作量。 有没有现成的工具可以......
  • 【Azure Service Bus】批量处理Service Bus Topic 中的死信消息(dead-lettered messag
    问题描述在Azure的门户页面上,因为ServiceBusTopic中有很多dead-letteredmessage,而这些消息占用了大量的存储空间,通过门户上的ServiceBusExplorer每次只能消费一条消息。虽然可以通过修改代码来指定消费私信队列中消息,但是需要修改代码,需要一些工作量。 有没有现成的工具......
  • 某智能网关 list_service_manage存在命令执行漏洞
    漏洞描述瑞斯康达多业务智能网关list_service_manage.php存在未授权命令注入漏洞,攻击者利用可获取服务器权限。资产信息FOFA:body="/images/raisecom/back.gif"漏洞复现pocPOST/vpn/list_service_manage.php?template=%60whoami%3E%2Fwww%2Ftmp%2F1.txt%60HTTP/1.1H......
  • SOMEIP_ETS_105: SD_ClientServiceGetLastValueOfEventUDPUnicast
    测试目的:验证DUT在客户端服务模式下能够订阅事件组,接收UINT8UDP单播事件,并在触发clientServiceGetLastValueOfEventUDPUnicast方法后返回该事件的值。描述本测试用例旨在确保DUT能够在客户端服务模式下正确地处理订阅和单播事件接收流程,并且能够通过特定的方法返回最近......
  • 5G Multicast/Broadcast Services(MBS) (三)Broadcast
    这篇是Broadcast的overview,正文开始。值得注意的是,对于5MBSbroadcast,UE处于RRCidle/RRC connected/Inactive时,网络侧都可以通过MRB将MBS广播数据传输到UE。对于Broadcast涉及的RNTI有G-RNTI以及MCCH-RNTI。1SessionManagement对于特定服务,将执行以下阶段:(1)MBS......
  • 【Azure Cloud Service】在Azure云服务中收集CPU监控指标和IIS进程的DUMP方法
    问题描述在使用CloudService服务时,发现服务的CPU占用很高,在业务请求并不大的情况下,需要直到到底是什么进程占用了大量的CPU资源,已经如何获取IIS进程(w3wp.exe)的DUMP文件? 问题解答一:收集云服务中CPU的性能数据远程登录(RDP)到云服务的实例上,使用管理员身份运行以下命令:Lo......