BUSA8090 Data and Visualisation for Business Final Exam sample paper(S1 2022) Section A

: Multiple Choice Questions (1*10= 10 marks) Note: All MCQs will only have 1 correct answer to choose,

 the MCQs will cover the content from Week 1 to Week 12, both practical and theoretical. Section B: ERD (10 marks) Sketch the ERD using Chen’s notation to describe the database component of the mobile application Amazon. Make sure that the ERD supports the transactions between the buyer and seller. Please provide explanations for your design. Section C: SQL (10 marks) The following tables form part of a database held in a relational DBMS:- Member (MemberID, Surname, Firstname, Address, Suburb, State, Postcode, Phone, DOB) Exercise (ExerciseID, Name, Category, Equipment) SessionDetails (MemberID, ExerciseID, SessionType, NumberOfSessions) Appointment (PTID, MemberID, Time, AppointmentDate, Status) PersonalTrainerAvailability (PTID, PTFirstName, PTSurname, AvailableTime, AvailableDate, FeesPerHour) where • Member contains member personal details; • Exercise contains exercise details; • SessionDetails contains details of each session. One member can have multiple sessions and one exercise can have multiple occurrences in the sessions; • Appointment contains details of the appointments taken by member for personal trainer. One member can make multiple appointments; • PersonalTrainerAvailability contains details of availability of the Personal trainer. One personal trainer can give multiple appointments. Given the above relational schema, write SQL statement to answer the following questions. a) Find all the Session details (MemberID [in ascending order], ExerciseID [in ascending order]) and session type for those Sessions that have number of sessions more than 20. [2 marks] b) Find all the Surnames and firstnames of members and their trainers whose appointment is pending. [2 marks] c) Find the appointment date, time and Member ID of all members who are with the trainer John. [2 marks] d) For this query, we would like you to calculate two additional fields. The first one should be named “TaxApplied”. This field should apply 20% to the FeesPerHour. The second calculated field should be named “FeesAfterTax”. This field should calculate the Fees per hour after deducting tax. Include Trainer ID, Trainer firstname, Trainer Surname and FeesPerHour in the query output. [2 marks] e) Create a query that will select the Trainer firstname, Trainer surname, Member firstname, Member surname, trainer availability days and exercise category for the trainers where the fees per hour is $50 or less. [2 marks] Section C: Data Visualization (10 marks) 1)Provide your critiques of a given data visualization (4 marks) 2)Make an implementation plan to resolve the issues that you have outlined in question 1 and choose either Tableau or R (ggplot 2) to provide the actual solutions. Please note, you are not required to write the actual codes if you are using ggplot 2, but the structure of the codes is mandatory. E.g. which libraries will be used, the codes structure and codes logic. If you are using Tableau, please clarify the basic steps of completing the relevant visualization.