I have to build a database using MQSQL Workbench
This is not a writing Assignment it is an SQL Coding Assignment
Microsoft Word - Assignment-2Version2.docx COMP13502020-Assignment2Part1 1 DEPARTMENTOFCOMPUTING COMP13502020–ASSIGNMENTTWO Due:9pmFriday30October2020 IntroductiontoDatabaseDesignandManagement DatabaseImplementationAssignment (Worth20% Gradedoutof100) COMP13502020-Assignment2Part1 2 TableofContents CASEBACKGROUND ....................................................................................................... 3 UNDERSTANDINGHOWTHEASSIGNMENTWORKS .................................................... 6 TASKDESCRIPTIONS ...................................................................................................... 7 SECTION-ONE ........................................................................................................................... 7 SECTION-TWO ........................................................................................................................... 8 SECTION-THREE ......................................................................................................................... 9 SECTION-FOUR .......................................................................................................................... 9 COMP13502020-Assignment2Part1 3 CaseBackground RemoteIslandResorthasnowmovedtorelationaldatabases(Thankstoyourdesign). Theyarehavingtroublewithcreatingtablesandwritingafewqueries.Youhavebeen employedtoconstructandimplementthedatabaseandwritequeriesforsomecommon searchesonthedatabase.Commentsaregivenforyoutounderstandthecolumnanddo nothavetobeaddedtothedatabase. Donotchangethecolumnnameswhilstcreatingthetables Table:VillaType Column-Name Comments/Description SampleData VillaTypeID[PK] AuniqueidentifierforthetypeofVilla T1 VillaTypeName Ashortname/descriptionforthetypeof Villa One-bedroom Villa withoutPool Table:Villa Column-Name Comments/Description SampleData VillaID[PK] AuniqueidentifierfortheVilla V14 VillaName Ashortname/descriptionfortheVilla SereneBliss VillaCostPerDay Cost of the Villa for a night of reservation(Decimalwithtwoplaces) Maxcostofavillais$2500.00/night 785.95 VillaTypeID[FK] The id of the type of villa [FK] referencing VillaTypeID in VillaType table T1 Table:Customer Column-Name Comments/Description SampleData CustomerID[PK] Auniqueidentifierforthecustomer C345 CustomerName Thenameofcustomerstoredinformat “FirstName LastName”. For ease, assume no middle names, initials are stored HughJackman CustomerPhNum The phone number of customer. Only mobilephonenumbersarestored 0415871256 Table:Reservation Column-Name Comments/Description SampleData ReservationID[PK] AuniqueidentifierfortheReservation R321 ReservationDate Thedateofthereservation 12thofJune,2020 CustomerID[FK] TheidoftheCustomer[FK]referencing CustomerIDinCustomertable C345 COMP13502020-Assignment2Part1 4 Table:Villa_Reservation Column-Name Comments/Description SampleData SampleData ReservationID [PK,FK] A unique identifier for the Reservation referencing ReservationID in Reservation table R321 R321 VillaID[PK,FK] A unique identifier for the Villa referencing VillaID in Villatable V14 V15 (assuming V15isaVilla) DateFrom Check-in date of the reservationofthevilla 1st of December, 2020 4th of December, 2020 DateTo Check-out date of the reservationofthevilla 10th of December, 2020 10th of December,2020 Note: Multiple villas can be reserved under the same reservation code. So, two sample recordsareprovided. Table:Payment Column-Name Comments/Description SampleData PaymentID[PK] AuniqueidentifierforthePayment P300 ReservationID[FK] Aunique identifier for theReservation referencing ReservationID in Reservationtable R321 PaymentDate Thedateofthepayment 13thofJune,2020 PaymentAmount The amount paid under a reservation (Decimalwithtwoplaces) 3000.00 Note:Multiplepaymentscanbemadeforareservation. Table:Activity Column-Name Comments/Description SampleData ActivityID[PK] Auniqueidentifierfortheactivity A01 ActivityName Ashortnamefortheactivity Kayaking ActivityCost Cost of the Activity for a person (Decimalwithtwoplaces) Maxcostofanactivityis$999.99 120.00 ActivityType ThetypeoftheActivity.Valuesare‘I’for indoor activity, ‘O’ for an outdoor activityand‘B’foranactivitythatcould bebothIndoorandOutdoor. B Note:Activitiescouldbeapackageactivityinwhichcase,ActivityNamecouldbesomething like“Package-1”. COMP13502020-Assignment2Part1 5 Table:Staff Column-Name Comments/Description SampleData SampleData StaffID[PK] Auniqueidentifierforthestaff S1 S2 StaffName Thenameofstaff Chris Hemsworth Richard Dawkins ManagerID[FK] A unique identifier for theManager referencingStaffIDinStafftable null S1 Note: Some Staff may work independently, in which case they will not be managed by anyone.Astaff,ifmanaged-canbemanagedbyonlyamanager. Table:ActivityBooking Column-Name Comments/Description SampleData ActivityID[PK,FK] A unique identifier for the Activity referencingActivityIDinActivitytable A01 ReservationID [PK,FK] Aunique identifier for theReservation referencing ReservationID in Reservationtable R321 ActivityTime[PK] Thedateandtimeoftheactivitystored asadatetimevalue 5thofDecember,2020at 13:30 NumPeople The number of people that would partakeintheactivity 4 GuideID[FK] A unique identifier for the Guide/Staff referencingStaffIDinStafftable S2 Table:SupportStaff Column-Name Comments/Description SampleData RosterID[PK] A unique identifier for the Roster of a StafftoanActivityBooking R2351 ActivityID[FK] A unique identifier for the Activity referencing ActivityID in ActivityBookingtable A01 ReservationID[FK] Aunique identifier for theReservation referencing ReservationID in ActivityBookingtable R321 ActivityTime[FK] Thedateandtimeoftheactivitystored as a datetime value referencing ActivityTimeinActivityBookingtable 5thofDecember,2020at 13:30 StaffID[FK] AuniqueidentifierfortheSupportStaff referencingStaffIDinStafftable S1 HoursNeeded Thenumberofhoursthesupportstaffis neededfortheactivitybooking 4 Note:SupportStaffarerosteredtoanActivityBooking.Referencingmustbedonewithcare. COMP13502020-Assignment2Part1 6 Table:Package Column-Name Comments/Description SampleData SampleData PackageActivityID [PK,FK] AuniqueidentifierforthePackage Activity referencing ActivityID in Activitytable A01 A01 ChildActivityID [PK,FK] A unique identifier for the ChildActivityreferencingActivityID inActivitytable A06 (assuming A06 is an activity) A12 (assuming A12 is an activity) Note:Asub-activitycanbemadeupofsub-subactivitiesaswell. Understandinghowtheassignmentworks Please read these instructions carefully to understand how the assignment works. A sampleschema(asapdffile)isprovidedintheAssignment-2folder.Itshouldgiveyou anideaofwhichtablesareconnectedtowhichothertables Theassignmentisbrokendowninto4sections 1. SectionOne:Pass(upto65marks) 2. SectionTwo:Credit(65-75marks) 3. SectionThree:Distinction(75-85marks) 4. SectionFour:HighDistinction(upto100marks) Youwillhavetoscorefullmarks/haveareasonableattempttobeeligibleforthenext section.Let’stakeacoupleofscenarios Student 1 has received 63marks with a couple of tiny errors and has proceeded to attemptallthequestionsinSectionTwo.SectionTwowillbegraded. Student 2 has received 52marks withmultiple errors, failure to attempt a query in SectionOne.Studenthasattemptedallsections.Inthiscase,furthersectionswillnotbe considered,onlyreceivingSectionOnemarkof52. Tonote: 1. Creationof tables inSectionsTwo toFourhavenomarks.Thismeansyouare expectedtocreatetablesandinsertdataforyourqueriestowork.Youwillhave toprovidethecodesfortablecreationanddatainsertion 2. At least5 recordsmustbe inserted inevery table that is created inanyof the sections. 3. Executionofeveryquerymustcontainatleast2resultingrows.Thismeansyou willhavetogobackandinsertmoredata,ifrequired. 4. Allcolumnnamesmusthaveaproperalias(iftheyarecomputedfields/fieldswith somecalculation) 5. Removeduplicateresults,whereverapplicable 6. Youshouldbeaddingcommentswhereverapplicable,especiallyifyouhavemade assumptions 7. NaturalJoinshallnotbeusedwithintherealmoftheassignment. COMP13502020-Assignment2Part1 7 TaskDescriptions Section-One Thissectionhas13questions.Eachofthequestionsareworth5marks. Task1(5marks): Create these tables based on the schemaprovided: VillaType, Villa, Villa_Reservation, Reservation,PaymentandCustomer. Insertatleast5recordsintoeachofthetables.Noextramarkswillbeprovidedforadding morerecords in,butmorerecordsmaybeneededdependingonthequeryresults for differentquestions. Task2(5marks): Writeaquerytoprintallthedetails(ID,Name,Cost)ofthevilla.Notethatthecostmust beprefixedwitha'$'sign.Sorttherecordsinorderofpricewiththemostexpensivevilla atthetopofthelist. Task3(5marks): Writeaquerytoprintthereservationdetails(VillaID,checkinandoutdates,alongwith thenumberofdays)thateachofthevillasinthereservationarereservedfor. Task4(5marks): Writeaquerytoprintthealldetails(Name,Cost)ofthevillaiftheyaretwo-bedroom villas.Youwillneedtohavethephrase‘two-bedroom’inthedescriptionofvillatype. Task5(5marks): WriteaquerytoprinttheReservationIDifthereservationwaspaidforwithin5days fromthedateofreservation.Pleaseensurenoduplicateresultsareincluded. Task6(5marks): Using a subquery, print Customer names and phone numbers, if they have made a reservation(reservationdate)withinthelast6monthscalculatedfromtoday (Today here implies the date the query is run. Must not hardcode the date) Task7(5marks): RewriteTask6usingaJoin. COMP13502020-Assignment2Part1 8 Task8(5marks): Writeaquerytoprintallthenamesofcustomerswhohavebookedvillasthatcostsless than$1000perday.Pleaseensurenoduplicateresultsareincludedintheresult. Task9(5marks): Write a query to print the total amount of payments that have been made for each reservation. Sort the records inorderof the totalpaymentsmadewith themostpaid reservationsatthetopofthelist.(UPDATED) Task10(5marks): Writeaquerytoprintthereservationdetails(ID,Date)alongwiththenumberofvillas thathavebeenbookedforeachreservation,butonlyshowthereservationdetailsifthe numberofvillasreservedaremorethanone. Task11(5marks): Writeaquerytoprintthedetailsofallvillaswhichhaveneverbeenbooked. Task12(5marks): Writeaquerytoprintthedetailsofanypaymentthatismore$1500.Onlyincludethe paymentsthathavebeenmadeineitherJanuaryofanyyearorinanymonthsintheyear of2020ortheyearof2018.Sorttheresultsbypaymentamountindescendingorder. Task13(5marks): Writeaquerytoprintthedetailsofanypaymentthathasbeenmadeonareservationof aone-bedroomvillabyacustomerwhosesurnamebeginswithJ. Section-Two Thissectionhas2questions.Eachofthequestionsareworth5marks.Youmaybeeligible forpartialmarksifthereareerrorsinyouranswers.Tobeabletoanswerthequestions, youwillhavetocreateandpopulatethefollowingtablesbasedontheschemaprovided: Activity,Staff,ActivityBooking Task14(5marks): WriteaquerytoprinttheReservationIDandthetotalamountthatithascosted(Costof villapernight*numberofdaysithasbeenreservedfor).Onlyincludereservationsthat exceedatotalamountof$10,000. Task15(5marks): Writeaquerytoprintthenamesofthecustomerswhohavemadebookingsofoutdoor activitiesthoseofwhichhaveacostthatisstrictlylessthantheaveragecostofoutdoor activities.Theaverageshouldincludebothoutdoorandpackageactivities. COMP13502020-Assignment2Part1 9 Section-Three Thissectionhas2questions.Eachofthequestionsareworth5marks.Youmaybeeligible forpartialmarksifthereareerrorsinyouranswers.Tobeabletoanswerthequestions, you will have to create and populate the ‘SupportStaff’ table based on the schema provided. Task16(5marks): Writeaquerytoprintthenamesofthecustomersandalltheactivitiestheyhavebooked intheafternoon(aftermid-dayandbefore4pm)alongwiththenamesoftheguides.Only includeguideswhoareManagers. Task17(5marks): WriteaquerytoprintthenamesofStaffandtheirmanagers,onlyifthemanagersmanage 2stafformore Section-Four Thissectionhas2questions.Eachofthequestionsareworth5marks.Youmaybeeligible forpartialmarksifthereareerrorsinyouranswers.Tobeabletoanswerthequestions, youwillhavetocreatethe‘Package’tablebasedontheschemaprovided Task18(5marks): Writeaquerytoprintthedetailsofactivitybooking(ActivityID,ReservationID,Timeof theActivityReservation,Nameof theActivity) and thenamesof staff involved in the activities.Thisshouldinvolvetheguideandallthesupportstaffinvolved Task19(5marks): Listthedetailsofpackageactivities(id,nameandcost)alongwiththedetails(id,name andcost)ofitsleastexpensivesubactivities(UPDATED) Task20(5marks): Writeaquerytolistthedetailsofreservation(id)alongwiththetotalcost(incurrency format)ofbothactivitiescalculatedfromthebooking(numberofpeople*thecostofan activity)andvillareservation(numberofdays*Costpernight)foreachofthevilla)for eachof the reservation. If the reservationdoesn't involve activitybooking, 0mustbe displayedthen0mustbedisplayedasthecost.Lookattheexamplebelowofasample output. -- Please rename this file to your StudentID_StudentName -- SECTION-ONE -- Task 1 /* Insert your codes in the correct order of execution. If Table A needs to be created before Table B, add codes in cthe orrect order */ -- Table creation CREATE TABLE VillaType( VillaTypeID varchar(20), VillaTypeName varchar(50), PRIMARY KEY(VillaTypeID) ); CREATE TABLE Customer( CustomerID varchar(20), CustomerName varchar(250), CustomerphNum varchar(20), PRIMARY KEY(CustomerID) ); CREATE TABLE Villa( VillaID varchar(20) PRIMARY KEY, VillaName varchar(250), VillaCostPerDay VarChar(10), VillaTypeID varchar(20), INDEX villa_in (VillaID), FOREIGN KEY (VillaTypeID) REFERENCES VillaType(VillaTypeID) ); CREATE TABLE Reservation( ReservationID varchar(10), ReservationDate date, CustomerID varchar(20), PRIMARY KEY(ReservationID), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ); CREATE TABLE Villa_Reservation( ReservationID varchar(10), VillaID varchar(20), DateFrom date NOT NULL, DateTo date NOT NULL, PRIMARY KEY(ReservationID,VillaID), index(ReservationID,VillaID,DateFrom,DateTo), FOREIGN KEY (ReservationID)