1. Homepage
  2. Programming
  3. FIT5137 Advanced Database Technology Assignment 2: MStay

FIT5137 Advanced Database Technology Assignment 2: MStay

Engage in a Conversation
MonashFIT5137Advanced Database TechnologyMStayDatabaseData Warehouse

FIT5137 Assignment 2 -S2 2024 (Weight = 40%) Due - Friday, 20 September 2024, 4:30 PM CourseNana.COM

Version: 3.0 – 14/08/2024 CourseNana.COM

General Information and Submission CourseNana.COM

o This is an individual assignment.
o Submission method: Submission is online through Moodle.
o Penalty for late submission: 5% deduction for each day.
o Assignment FAQ: There is an Assignment Frequently Asked Questions page set up for CourseNana.COM

the Assignment 2 on EdStem Forum. CourseNana.COM

Problem Description CourseNana.COM

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. CourseNana.COM

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

select * from MStay.<table_name>; CourseNana.COM

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

Table Name CourseNana.COM

Attributes,Data Types and Key Constraints CourseNana.COM

Review_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores review information of the related booking order. CourseNana.COM

Review_Date CourseNana.COM

Review_Comment CourseNana.COM

Varchar2 CourseNana.COM

Booking_ID CourseNana.COM

Number (FK) CourseNana.COM

BOOKING CourseNana.COM

Booking_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores booking information. CourseNana.COM

Booking_Date CourseNana.COM

Booking_Stay_Start_Date CourseNana.COM

Booking_Duration CourseNana.COM

Booking_Cost CourseNana.COM

Booking_Num_Guests CourseNana.COM

Listing_ID CourseNana.COM

Number (FK) CourseNana.COM

Guest_ID CourseNana.COM

Number (FK) CourseNana.COM

Guest_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores all guest information. CourseNana.COM

Guest_Name CourseNana.COM

Varchar2 CourseNana.COM

LISTING CourseNana.COM

Listing_ID CourseNana.COM

Number (PK) CourseNana.COM

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

Listing_Date CourseNana.COM

Listing_Title CourseNana.COM

Varchar2 CourseNana.COM

Listing_Price CourseNana.COM

Listing_Min_Nights CourseNana.COM

Listing_Max_Nights CourseNana.COM

Prop_ID CourseNana.COM

Number (FK) CourseNana.COM

Type_ID CourseNana.COM

Host_ID CourseNana.COM

Number (FK) CourseNana.COM

Host_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores all host information. CourseNana.COM

Host_Name CourseNana.COM

Varchar2 CourseNana.COM

Host_Since CourseNana.COM

Host_Location CourseNana.COM

Varchar2 CourseNana.COM

Host_About CourseNana.COM

Varchar2 CourseNana.COM

Host_Listing_Count CourseNana.COM

HOST_VERIFICA TION CourseNana.COM

Host_ID CourseNana.COM

Number (PF) CourseNana.COM

The table stores the verification information between host and channel. CourseNana.COM

Channel_ID CourseNana.COM

Number (PF) CourseNana.COM

CHANNEL CourseNana.COM

Channel_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores the channel of verification for the hosts. CourseNana.COM

Channel_Name CourseNana.COM

Varchar2 CourseNana.COM

LISTING_TYPE CourseNana.COM

Type_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores all listing types. CourseNana.COM

Type_Description CourseNana.COM

Varchar2 CourseNana.COM

PROPERTY CourseNana.COM

Prop_ID CourseNana.COM

Number (PK) CourseNana.COM

The table stores all property information. CourseNana.COM

Prop_Description CourseNana.COM

Varchar2 CourseNana.COM

Prop_Neighbourhood_Overv iew CourseNana.COM

Varchar2 CourseNana.COM

Prop_Num_Beds CourseNana.COM

Prop_Num_Bedrooms CourseNana.COM

Prop_Num_Bathrooms CourseNana.COM

Prop_Num_Reviews CourseNana.COM

Prop_Rating_Location CourseNana.COM

Prop_Rating_Cleanliness CourseNana.COM

Prop_Rating_Value CourseNana.COM

Prop_Average_Rating CourseNana.COM

PROPERTY_AMM ENITY CourseNana.COM

Prop_ID CourseNana.COM

Number (PF) CourseNana.COM

The table links property and amenity tables CourseNana.COM

Number (PF) CourseNana.COM

AMENITY CourseNana.COM

Number (PK) CourseNana.COM

The table stores all amenities information CourseNana.COM

Amm_Description CourseNana.COM

Varchar2 CourseNana.COM

A. Transformation Stage CourseNana.COM

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

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 : CourseNana.COM

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

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

  • ●  Channels CourseNana.COM

  • ●  Booking duration [short-term: less than 30 nights, medium-term: 30 to CourseNana.COM

    90 nights, long-term: more than 90 nights] CourseNana.COM

  • ●  Review time [Month, Year] CourseNana.COM

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

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

- Preparation stage. CourseNana.COM

Before you start designing the data warehouse, 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. CourseNana.COM

The outputs of this task for Report are: CourseNana.COM

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). CourseNana.COM

- Designing the data warehouse by drawing star/snowflake schema. CourseNana.COM

Design task A: CourseNana.COM

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: CourseNana.COM

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

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. CourseNana.COM

The outputs of task A & B for Report are: CourseNana.COM

  1. b)  A star/snowflake schema diagrams for design task A. (You can use Lucidchart to CourseNana.COM

    draw the star schema.) CourseNana.COM

  2. c)  List suggestion of increase the granularity of your fact tables for design task B CourseNana.COM

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. CourseNana.COM

Note: CourseNana.COM

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

If you have dropped all tables in your account and you still encounter the ORA-01536: space quota exceeded for tablesace CourseNana.COM

‘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. CourseNana.COM

The outputs of this task for Report are: CourseNana.COM

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

A sample of screenshots of the table structure CourseNana.COM

B. Data Analytic Stage CourseNana.COM

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. CourseNana.COM

The outputs of this task for Report are: CourseNana.COM

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

Submission Checklist CourseNana.COM

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

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

  1. Cover page CourseNana.COM

  2. If you have done the data cleaning process, explain the strategies you used in this CourseNana.COM

    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. CourseNana.COM

  3. A star/snowflake schema diagrams for design task A CourseNana.COM

  4. List suggestion of increase the granularity of your fact tables for design task B CourseNana.COM

  5. Screenshots of the table structure you created for Design Task A only, including CourseNana.COM

    the dimension table and fact tables.
    a. SQL file for creating the star/snowflake schema is
    NOT required in CourseNana.COM

    submission CourseNana.COM

  6. Findings report: A detailed explanation of your findings, including any significant CourseNana.COM

    observations or patterns identified during the analysis. CourseNana.COM

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

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

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. CourseNana.COM

Note: CourseNana.COM

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. CourseNana.COM

Key guidance of design a poster: CourseNana.COM

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

  • ●  Who is your target audience for this poster? CourseNana.COM

  • ●  Do you really need all the details from your report on this poster? CourseNana.COM

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

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

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. CourseNana.COM

Assignment Submission CourseNana.COM

The assignment must be submitted electronically through Moodle. Please ensure the following: CourseNana.COM

  1. Step 1 output: A combined pdf file save as: YourstudentID_A2_report.pdf CourseNana.COM

  2. Step 2 output: One page standard A4 poster in PDF format to save as: YourstudentID_A2_poster.pdf CourseNana.COM

  3. Step 3 output: A five minute video presentation in mp4 format save as: YourstudentID_A2_video.mp4 CourseNana.COM

Zip all above files from step 1 to 3, and name the ZIP folder as A2_YourstudentID.zip. CourseNana.COM

The due date: Due - Friday, 20 September 2024, 4:30 PM CourseNana.COM

  • ●  The submission of this assignment must be in the form of a single ZIP file. Only PDF and .mp4 files will be accepted within the zip file. No other formats will be accepted. CourseNana.COM

  • ●  You must ensure that you have all the files listed in this checklist before submitting your assignment to Moodle. Failure to submit a complete list of files will lead to mark penalties. CourseNana.COM

  • ●  It is important to note that our support hours are limited and we don't have the capacity to deal with submission issues outside of working hours. You must ensure that you have all the files listed in this checklist before submitting your assignment to Moodle. Failure to submit a complete list of files will result in a mark penalty. CourseNana.COM

  • ●  Penalty for late submission: 5 % deduction for each day, including weekends. CourseNana.COM

  • ●  Submission Cut-off time: 27 September 2024, 4:30 PM (Submission link will be CourseNana.COM

    unavailable after the cut-off date). CourseNana.COM

Get in Touch with Our Experts

WeChat (微信) WeChat (微信)
Whatsapp WhatsApp
Monash代写,FIT5137代写,Advanced Database Technology代写,MStay代写,Database代写,Data Warehouse代写,Monash代编,FIT5137代编,Advanced Database Technology代编,MStay代编,Database代编,Data Warehouse代编,Monash代考,FIT5137代考,Advanced Database Technology代考,MStay代考,Database代考,Data Warehouse代考,Monashhelp,FIT5137help,Advanced Database Technologyhelp,MStayhelp,Databasehelp,Data Warehousehelp,Monash作业代写,FIT5137作业代写,Advanced Database Technology作业代写,MStay作业代写,Database作业代写,Data Warehouse作业代写,Monash编程代写,FIT5137编程代写,Advanced Database Technology编程代写,MStay编程代写,Database编程代写,Data Warehouse编程代写,Monashprogramming help,FIT5137programming help,Advanced Database Technologyprogramming help,MStayprogramming help,Databaseprogramming help,Data Warehouseprogramming help,Monashassignment help,FIT5137assignment help,Advanced Database Technologyassignment help,MStayassignment help,Databaseassignment help,Data Warehouseassignment help,Monashsolution,FIT5137solution,Advanced Database Technologysolution,MStaysolution,Databasesolution,Data Warehousesolution,