Is it possible to assist with the answers?
Microsoft Word - COMP1350_Assignment1_FinalVersion.docx COMP13502020-Assignment1 1 DEPARTMENTOFCOMPUTING COMP13502020–ASSIGNMENTONE Due:9pmFriday11September2020 IntroductiontoDatabaseDesignandManagement DatabaseDesignAssignment (Worth20%ofyourgrade,markedoutof100) COMP13502020-Assignment1 2 TableofContents CASEBACKGROUND .................................................................................................................... 3 TASKDESCRIPTION .................................................................................................................... 4 TASK 1- EER DIAGRAM (50 MARKS) ......................................................................................................... 4 TASK 2- LOGICAL TRANSFORMATION (30 MARKS) ........................................................................................ 4 TASK 3- NORMALISATION (20 MARKS) ....................................................................................................... 4 WHATTOINCLUDEINYOURREPORT(ASAPDF) ................................................................... 5 WHATTOSUBMITFORTHEASSIGNMENT ............................................................................... 5 MARKINGRUBRIC ....................................................................................................................... 6 COMP13502020-Assignment1 3 CaseBackground RemoteIslandResort,Fijihasbeeninthehospitalitybusinessforthelast80years.Theystarted storing data in a file-based system and then transitioned into using spreadsheets. As years progressed, their business has grown exponentially leading to an increase in the number of customersandvolumeofdata.Youareemployed todevelopadatabasemodel to replace the currentspreadsheets. YouhavebeenprovidedwiththefollowingbusinessrulesaboutRemoteIslandResort. Customers:Everycustomerneedstobeuniquelyidentifiedinthesystem.Otherdetailswhich thesystemneedstostoreaboutacustomerincludetheirname,addressandphonenumber. Villas:EveryVillaisprovidedwithauniqueidentifier.Otherdetailsofavillaincludethenameof thevilla,thetypeofthevilla,anditssize(insqm).Somevillascouldbeinterconnected.Ifvillas areinterconnected,theycanbeconnectedtooneothervilla.VillasareofaparticularVillaType. EveryVillaTypeisprovidedwithauniqueidentifierDescriptionandfeaturesarestoredforeach villatype. VillaReservation:Everyreservationhasauniquecode.Areservationalsohasotherinformation tobecapturedsuchasthedatethereservationwasmade,thecustomerit isreservedfor,the villasincludedinthereservation(Acustomercanmakeareservationformultiplevillas),dates each of the villas are reserved for, names of the guests staying in each of the villas, and its correspondingcostisstored.Thetotalcostisstoredwiththerestofthereservationdata.The totalcostwouldincludethecostofvillareservations,thecostofallactivitiesfromtheactivities booking,andthecostofthediningfromallthedinnerbookings.Paymentforthereservationcan bemadeassplitpayments.AuniqueIDisgeneratedforeverypaymentmade.Thereservation thepaymentismadefor,dateofthepaymentandpaymentamountisstored. Activities:Everyactivityisprovidedwithauniqueidentifier.Otherdetailsofactivityincludethe name of the activity, description, and its cost. There are two types of activities- Indoor and Outdooractivities.Indooractivitieshavedifficultylevelstored.Someactivitiesaremadeupof sub-activities(Thinkofsomethinglikeadaytour-whichcouldincludetwohalf-daytours;Ahalf- daytourcouldbemadeupofYogaandKayaking.Thismeansthatsomeactivitiescouldbeboth indoorandoutdooratthesametime).Outdooractivitiesusedifferenttypesofequipment.Every equipmentisprovidedwithauniqueidentifier.Otherdetailsoftheequipmentincludedetailslike the nameof the equipment, the supplier that supplied the equipment, and quantity on hand. Equipmentissuppliedbyasupplier.Everysupplierisprovidedwithauniqueidentifier.Other details of a supplier include the nameof the supplier, the nameof the contact person, and a contactnumber.A suppliermaysupplymultiple typesof equipment.Onarrival at the resort, customerscanregistertoavarietyofexperiences(activities)thattheywouldbeinterestedto partake.Thedateoftheactivity,thenumberofpersons,andanyspecialrequirementsarealso stored. DinnerReservations:RemoteIslandResortoffersavarietyofdinneroptionsthatcustomers canchoosefrom. Everydinneroptionis identifiedbyadinnercode.Otherdetailsofadinner include a dinner description and cost. Since the resort has beautiful locations customers can choose to dine at, every dinner venue is identified by a venue code.Other details of a venue includeavenuedescriptionandcapacity.Therearetwodifferenttypesofvenues- indoorand COMP13502020-Assignment1 4 outdoor.Outdoorvenuesusemultipleequipmentandanequipmentcouldbeusedformultiple outdoorvenues.Sizeoftheindoorvenuesisalsoregistered.Whilemakingdinnerreservations, customerswillhavetochooseavenueandadinneroption.Dateofreservation,dateofdinner, andanyspecialdietaryrequestsarerecorded. Staffing:Everystaffmemberisprovidedwithauniquestaffnumberattheresort.Theresortalso needstokeeptrackofotherdetailsabouttheirstaffmembersliketheirnameanddateofbirth. Therearetwotypesofstaffthatneedtobetrackedinthesystem:SeniorandJuniorStaff.For Seniorstaff,theirsenioritylevelandyearofcertificationneedtoberecorded.ForJuniorstaff, theiryearofemploymentneedstoberecorded.Everyactivityreservationincludesonetourguide (who is a senior staff), onehelper (who is a junior staff) and additional support staff (this is optionalandcanbemultiple)asamatterofpolicy. TaskDescription Task1-EERDiagram(50marks) Basedonthebusinessrules,youareexpectedtoconstructanEnhanced-ER(EER)diagram.The EERdiagramshouldincludeentities,attributes,andidentifiers.Youarealsoexpectedtoshow the relationships among entities using cardinality and associative entities (if any). You may choosetoaddattributesontherelationships(ifthereareany)orcreateanassociativeentitywith theseattributes.Yourdiagramshouldalsospecifythecomplete(total)anddisjoint(mutually- exclusive)constraintsonthegeneralization/specialization. Task2-LogicalTransformation(30marks) BasedonyourEER,forthe“Dinnerreservations”excerptfromyourcasestudy,performalogical transformation.Anyentitiesmentionedintheexcerptmustbeusedinthetransformation.You willhavetouseStep8aforconversion. Also,use8b,8cand8dandshowtheprocessoftransformationforthesesteps.Youdonothave torepeatsteps2-7forthese. Task3-Normalisation(20marks) Normalisethetableinto3NFrelationswithPrimaryandForeignkeys DinnerCode MenuItemID MenuItemName DinnerCost PortionSize DressCode DressCode Description DIN1 ENTR1 SpringRoll $125 1 D1 Formal Attire DIN1 MAINS1 Pumpkin QuinoaSalad $125 2 D1 Formal Attire DIN1 DESS1 BanoffeePie $125 2 D1 Formal Attire DIN2 ENTR1 SpringRoll $75 1 D2 Smart Casual COMP13502020-Assignment1 5 DIN2 MAINS1 Pumpkin QuinoaSalad $75 1 D2 Smart Casual DIN2 DESS1 BanoffeePie $75 1 D2 Smart Casual Whattoincludeinyourreport(asaPDF) • To present your answers to these tasks, please use the template provided in the assignmentfolder. • Filloutthedetailsprovidedonthefirstpageofthetemplate. • ForTask1,attachtheimageinthedocument(eitherhand-drawn/system-generated)of theEERmodelyouhavecreated.ForTask2,3writetheanswersundertherightspaces inthetemplate. • Assumptions:Ifyouhaveanyassumptions,pleaselistthemdowninthelastpageofthe report.(e.g.aboutrelationshipsbetweenentitiesinyourEERModel). • Ifyourimageslackclarity,youwillnotbegivenanymarks.Tutorscanzoomintocheck thediagrambutshouldnothavetodealwithimagesthatareblurry/fuzzy.Whateveris submittedisthefinalsubmission.So,pleasemakesureyourimageisreadable. WhattoSubmitfortheAssignment • Onepdfdocumentwithalltheanswersincluded.Pleasedonotsubmitmultiplecopies. • Youcansubmitmanytimesupuntilthedeadline(sogetanearlyversioninjustincase) YouareallowedtouseeitheraprofessionalCASEtool,suchasPowerDesigner,asimpleonline toollikewww.draw.io,orclearlyhand-drawn(withlegiblehandwriting)thedatamodel. Please submit your work on iLearn (there is a submission Turnitin link in the Assignments section)asareport(asa.pdfONLY)by9pmFriday11September2020.Youneedtorenamethe templateprovidedusingthisformat(e.g.StudentName_StudentID.pdf). Anoteaboutthesubmissiondeadline: Thesubmissiondeadlineisadeadline,notagoal!Youwillhaveotherassignmentsduearound thesametime...andthisisnotanassignmentyouwillbeabletocompleteatthelastminute! COMP13502020-Assignment1 6 MarkingRubric Marks Topic Explanation 50 marks Entities Existenceofcorrectentitieswhichcoverthedetails inthespecification? Attributes Correctattributes? Correctidentificationofprimaryidentifier? Relationships Existenceofappropriaterelationships? Appropriatecardinalities? Appropriateoptional/mandatoryvalues? Existenceofmultiplerelationships? CorrectSuper types/Subtypes CorrectSupertypes/Subtypeentities? Validreuseofattributes? Relationships Validrelationships? SpecializationConstraints Correctdisjointness/completeness? 30 marks ValidTransformationSteps(Steps1-7,8a,Repeatsteps2-7,8b/c/d) 20 marks Correctprocessofnormalisationinto3NF COMP1350 2020 – ASSIGNMENT ONE Student ID: Student Name: Tutor’s Name: Class Number Task 1: EER Diagram --Remove this line. add a screenshot (must be legible, when zoomed in) of your EER diagram Assumptions, if any: · · · Task 2: Logical Transformation Task 3: Normalisation