FIT5137Assignment2-S22024(Weight=40%)

Due-Friday,20September2024,4:30PM

Version:3.0–14/08/2024

GeneralInformationandSubmission

oThisisanindividualassignment.

oSubmissionmethod:SubmissionisonlinethroughMoodle.

oPenaltyforlatesubmission:5%deductionforeachday.

oAssignmentFAQ:ThereisanAssignmentFrequentlyAskedQuestionspagesetupfor

theAssignment2onEdStemForum.

ProblemDescription

M-StayisaresidentialservicethatoffershomestayandrentalservicestoMonashstudents

andstaffaroundMelbourne.Thecompanyhasanexistingoperationaldatabasethat

maintainsandstoresallofthebusinesstransactionsinformation(e.g.properties,hosts,

listings,booking,etc.)requiredforthemanagement'sdailyoperation.Asthebusiness

grows,M-StayhasdecidedtobuildaDataWarehousetoimprovetheiranalysisandwork

efficiency.However,sincethestaffatM-StayhavelimitedBusinessIntelligenceandData

Warehouseknowledge,theyhavedecidedtohireyoutodesign,developandquickly

generateBIreportsfromaDataWarehouse.

TheoperationaldatabasetablescanbefoundattheMStayaccount.Youcan,forexample,

executethefollowingquery:

select*fromMStay.;

ThedatadefinitionofeachtableinMStayisasfollows:

TableNameAttributes,DataTypesandKey

Constraints

Notes

REVIEWReview_IDNumber

(PK)

Thetablestores

reviewinformation

oftherelated

bookingorder.

Review_DateDate

Review_CommentVarchar2

Booking_IDNumber

(FK)

BOOKINGBooking_IDNumber

(PK)

Thetablestores

booking

information.

Booking_DateDate

Booking_Stay_Start_DateDate

Booking_DurationNumber

Booking_CostNumber

Booking_Num_GuestsNumber

Listing_IDNumber

(FK)

Guest_IDNumber

(FK)

GUESTGuest_IDNumber

(PK)

Thetablestoresall

guestinformation.

Guest_NameVarchar2

LISTINGListing_IDNumber

(PK)

Thetablestoresall

listinginformation.

Eachlistinghasone

propertyandone

hostinformation.

Listing_DateDate

Listing_TitleVarchar2

Listing_PriceNumber

Listing_Min_NightsNumber

Listing_Max_NightsNumber

Prop_IDNumber

(FK)

Type_IDNumber

(FK)

Host_IDNumber

(FK)

HOSTHost_IDNumber

(PK)

Thetablestoresall

hostinformation.

Host_NameVarchar2

Host_SinceDate

Host_LocationVarchar2

Host_AboutVarchar2

Host_Listing_CountNumber

HOST_VERIFICA

TION

Host_IDNumber

(PF)

Thetablestoresthe

verification

information

betweenhostand

channel.

Channel_IDNumber

(PF)

CHANNELChannel_IDNumber

(PK)

Thetablestoresthe

channelof

verificationforthe

hosts.

Channel_NameVarchar2

LISTING_TYPEType_IDNumber

(PK)

Thetablestoresall

listingtypes.

Type_DescriptionVarchar2

PROPERTYProp_IDNumber

(PK)

Thetablestoresall

property

information.

Prop_DescriptionVarchar2

Prop_Neighbourhood_Overv

iew

Varchar2

Prop_Num_BedsNumber

Prop_Num_BedroomsNumber

Prop_Num_BathroomsNumber

Prop_Num_ReviewsNumber

Prop_Rating_LocationNumber

Prop_Rating_CleanlinessNumber

Prop_Rating_ValueNumber

Prop_Average_RatingNumber

PROPERTY_AMM

ENITY

Prop_IDNumber

(PF)

Thetablelinks

propertyand

amenitytables

Amm_IDNumber

(PF)

AMENITYAmm_IDNumber

(PK)

Thetablestoresall

amenities

information

Amm_DescriptionVarchar2

A.TransformationStage

ThefirststageofthisassignmentisdividedintoTWOmaintasks:

1.DesignadatawarehousefortheaboveM-Staydatabase.

YouarerequiredtocreateadatawarehousefortheM-Staydatabase.

Themanagementisespeciallyinterestedinthefollowingindicators:

●Numberofreviews

●Numberoflistings

●Averagebookingcost

Thefollowingisalistofdimensionattributesthatyoushouldincludeinyourdata

warehouse:

●Listingtype

●Listingtime[Month,Year]

●Listingseason

o(Spring:9to11,Summer:12to2,Autumn:3to5andWinter:6

to8)

●Listingmaximumstayduration[short-term:lessthan14nights,

medium-term:14to30nights,long-term:morethan30nights]

●Listingpricerange[low:lessthan$100,medium:$100to$200,high:

morethan$200]

●Channels

●Bookingduration[short-term:lessthan30nights,medium-term:30to

90nights,long-term:morethan90nights]

●Reviewtime[Month,Year]

●Bookingcostrange[low:lessthan$5000,medium:$5000to$10000,

high:morethan$10000]

Fortheattribute,ensurethatitmeetstherequirementsoftherangeorgroupspecified

inyoursubmission,ifrequiredinthespecification.

-Preparationstage.

Beforeyoustartdesigningthedatawarehouse,youhavetoensurethatyouhave

exploredtheoperationaldatabaseandhavedonesufficientdatacleaning.Onceyou

havedonethedatacleaningprocess,youarerequiredtoexplainwhatstrategiesyou

havetakentoexploreandcleanthedata.

TheoutputsofthistaskforReportare:

a)Ifyouhavedonethedatacleaningprocess,explainthestrategiesyouusedinthis

process(youneedtoshowtheSQLtoexploretheoperationaldatabaseandSQL

ofthedatacleaning,aswellasthescreenshotofdatabeforeandafterdata

cleaning).

-Designingthedatawarehousebydrawingstar/snowflakeschema.

DesigntaskA:

Thestarschemaforthisdatawarehousemaycontainsmulti-facts.Youneedto

identifythefactmeasures,dimensions,andattributesofthestar/snowflakeschema.

Thefollowingqueriesmighthelpyoutodeterminethefactmeasuresanddimensions:

●Howmanylong-termstaydurationlistingsarelistedonFacebook?

●HowmanylistingsarelistedinJune2015?

●Howmanylistingsarethereinsummerforan“Entirehome/apt”inamedium

pricerange?

●HowmuchistheaveragebookingcostinMarch2013?

●Howmanybookingsweretherefor“Privaterooms”withashort-termstay

durationin2015?

●Howmanyhigh-costbookingsweremadeinApril2014?

●HowmanyreviewsweregiveninFebruary2016?

Note:thestarschemayoucreatedinDesignTaskAasthehighestlevelofaggregation

DesigntaskB:

Inthisassignment,considerthestarschemayoucreatedinDesignTaskAasthe

highestlevelofaggregation.TheM-Staycompanymanagerwantstoimplementa

drill-downfunctiontoexploremoredetailedinformation.Yourtaskistosuggest

severalwaystoincreasethegranularityofyourfacttablesfromDesignTaskA.In

otherwords,themanagerwantstodecreasetheaggregationlevelofthefacttables

youcreatedinDesignTaskA.

TheoutputsoftaskA&BforReportare:

b)Astar/snowflakeschemadiagramsfordesigntaskA.(YoucanuseLucidchartto

drawthestarschema.)

c)ListsuggestionofincreasethegranularityofyourfacttablesfordesigntaskB

2.ImplementdesigntaskAstar/snowflakeschemausingSQL.

Youarerequiredtoimplementthestar/snowflakeschemathatyouhavedrawnin

designtaskA.Thisimpliesthatyouneedtocreatethefactanddimensiontablesin

SQL.TheoutputisaseriesofSQLstatementstoperformthistask.Youwillalsoneed

toshowthatthistaskhasbeencarriedoutsuccessfully.

Note:

●Ifyouraccountisfull,youwillneedtodropallofthetablesthatyouhave

previouslycreatedduringthetutorials.

●Ifyouhavedroppedalltablesinyouraccountandyoustillencounterthe

ORA-01536:spacequotaexceededfortablesace

‘TABLE_NAME’,pleasecheckyourSQLcodewhetheryouhaveproperly

joinedalltables.Thisissuewasmainlycausedwhenyoudidnotdothetablejoin

properlyasthenumberofrecordsmultipliedduringtheprocess.

TheoutputsofthistaskforReportare:

a)ScreenshotsofthetablestructureyoucreatedforDesignTaskA,includingthe

dimensiontablesandfacttables.

Asampleofscreenshotsofthetablestructure

B.DataAnalyticStage

ConductadataanalysisusingthestarschemayoucreatedinDesignTaskAby

writingSQLqueriestoexplorethedatafurther.Presentyourfindingsinaclearand

concisemanner,demonstratingyourunderstandingofthedatasetandhighlightingany

noteworthyobservationsorpatterns.

TheoutputsofthistaskforReportare:

1.Findingsreport:Adetailedexplanationofyourfindings,includingany

significantobservationsorpatternsidentifiedduringtheanalysis.

SubmissionChecklist

Step1:Report(25%ofthetotalscore)

Acombinedpdffilesaveas:YourstudentID_A2_report.pdf,containingallofthe

abovetasks:

A.Coverpage

B.Ifyouhavedonethedatacleaningprocess,explainthestrategiesyouusedinthis

process(youneedtoshowtheSQLtoexploretheoperationaldatabaseandSQL

ofthedatacleaning,aswellasthescreenshotofdatabeforeandafterdata

cleaning).Notethatyouareonlyrequiredtofindaround5(five)dataerrorsfor

thisstage.

C.Astar/snowflakeschemadiagramsfordesigntaskA

D.ListsuggestionofincreasethegranularityofyourfacttablesfordesigntaskB

E.ScreenshotsofthetablestructureyoucreatedforDesignTaskAonly,including

thedimensiontableandfacttables.

a.SQLfileforcreatingthestar/snowflakeschemaisNOTrequiredin

submission

F.Findingsreport:Adetailedexplanationofyourfindings,includinganysignificant

observationsorpatternsidentifiedduringtheanalysis.

Step2:Poster(35%ofthetotalscore)

OnepagestandardA4posterinPDFformattosaveas:

YourstudentID_A2_poster.pdf

Extractkeyinformationfromthereportyoucreatedandpresentitina

one-pageposter.ThepostermustbeinstandardA4sizeandinPDFformat,

whichcanbeeitherlandscapeorportrait.Thecontentshouldbeclearandeasy

tounderstand.Avoidusingtechnicaljargonorcomplexlanguage.Reviewthe

posterbeforesubmissiontoensureiteffectivelycommunicatesthekey

messagesofyourreport.

Note:

Ensurethepostercontentisconsistentwiththekeystructureandfindingsof

yourreport,andchooseanappropriatelayoutthateffectivelyorganizesthe

informationinaclearandlogicalmanner.Maintainagoodbalanceoftextand

visualstoenhancereadability,andensureallvisualsarerelevantandsupport

thecontentoftheposter.Labelallvisualsclearlyandprovidecaptionswhere

necessary.Avoidovercrowdingtheposterwithtoomuchtextortoomany

visuals,andensuretheposterisfreeofanygrammaticalortypographical

errors.

Keyguidanceofdesignaposter:

●

Whatisthemaintheme/objectiveoftheposterthatyouwanttoexpress?

●

Whoisyourtargetaudienceforthisposter?

●

Doyoureallyneedallthedetailsfromyourreportonthisposter?

Step3:Videopresentation(40%ofthetotalscore)

Afiveminutevideopresentationinmp4formatsaveas:

YourstudentID_A2_video.mp4

Basedonthereportandposteryouhavecreated,presentyourdesignandfindingsina

five-minutevideopresentation.Ensureyouthoroughlyunderstandboththereport

andthepostertoeffectivelyextractandcommunicatethekeypoints.

AssignmentSubmission

TheassignmentmustbesubmittedelectronicallythroughMoodle.Pleaseensurethe

following:

1.Step1output:Acombinedpdffilesaveas:YourstudentID_A2_report.pdf

2.Step2output:OnepagestandardA4posterinPDFformattosaveas:

YourstudentID_A2_poster.pdf

3.Step3output:Afiveminutevideopresentationinmp4formatsaveas:

YourstudentID_A2_video.mp4

Zipallabovefilesfromstep1to3,andnametheZIPfolderasA2_YourstudentID.zip.

Theduedate:Due-Friday,20September2024,4:30PM

●ThesubmissionofthisassignmentmustbeintheformofasingleZIPfile.Only

PDFand.mp4fileswillbeacceptedwithinthezipfile.Nootherformatswillbe

accepted.

●Youmustensurethatyouhaveallthefileslistedinthischecklistbefore

submittingyourassignmenttoMoodle.Failuretosubmitacompletelistoffiles

willleadtomarkpenalties.

●Itisimportanttonotethatoursupporthoursarelimitedandwedon'thavethe

capacitytodealwithsubmissionissuesoutsideofworkinghours.Youmust

ensurethatyouhaveallthefileslistedinthischecklistbeforesubmittingyour

assignmenttoMoodle.Failuretosubmitacompletelistoffileswillresultina

markpenalty.

●Penaltyforlatesubmission:5%deductionforeachday,includingweekends.

●SubmissionCut-offtime:27September2024,4:30PM(Submissionlinkwillbe

unavailableafterthecut-offdate).

Authorship

Thisassignmentisanindividualassignmentandthefinalsubmissionmustbeidentifiably

yourownwork.Breachesofthisrequirementwillresultinanassignmentnotbeingaccepted

forassessmentandmayresultindisciplinaryaction.

LatePenalty:

Lateassignmentssubmittedwithoutanapprovedextensionmaybeaccepteduptoa

maximumofsevendayswiththeapprovaloftheChiefExaminerand/orLecturerbutwillbe

penalisedattherateof5%perday(includingweekendsandpublicholidays).

Assignmentssubmittedmorethansevendaysaftertheduedatewillreceiveazeromarkfor

thatassignmentandmaynotreceiveanyfeedback.

Pleasenote:

●Aninabilitytomanageyourtimeorcomputingresourceswillnotbeacceptedasa

validexcuse.(Severalassignmentsbeingdueatthesametimeareafactofuniversity

life.)

●Hardwarefailures,whetherofpersonaloruniversityequipment,arenotnormally

recognisedasvalidexcuses.Failuretobackupassignmentfilesisalsonotrecognised

asavalidexcuse.

SpecialConsideration:

Allextensions/specialconsiderationswillnowbehandledbythecentralSpecConteam.

Pleasedonotemailteachingstafftorequestanextensionorspecialconsideration.

Extensionsandotherindividualalterationstotheassessmentregimewillonlybe

consideredusingtheUniversitySpecialConsiderationPolicy.Studentsshouldcarefully

readtheSpecialConsiderationwebsite,especiallythedetailsaboutwhatformal

documentationisrequired.

AllspecialconsiderationrequestsshouldbemadeusingtheSpecialConsideration

Application.

PleasedonotassumethatsubmissionofaSpecialConsiderationapplicationguarantees

thatitwillbegranted–youmustreceiveanofficialconfirmationthatithasbeen

granted.

Gettinghelpandsupport:

Whatcanyougethelpfor?

●ConsultationswiththeTeachingTeam

TalktotheTeachingTeam:

https://learning.monash.edu/course/view.php?id=19675§ion=5

●Englishlanguageskills

TalktoEnglishConnect:https://www.monash.edu/english-connect

●Studyskills

Talktoalearningskillsadvisor:https://www.monash.edu/library/skills/contacts

●Counselling

Talktoacounsellor:https://www.monash.edu/health/counselling/appointments

PlagiarismandCollusion:

MonashUniversityiscommittedtoupholdingstandardsandacademicintegrityandhonesty.

Pleasetakethetimetoviewtheselinks.

AcademicIntegrityModule

StudentAcademicIntegrityPolicy

Testyourknowledge,collusion(FITNoCollusionModule)

AllthebestforyourAssignment!