This is a group assignment with a maximum of 3-4 students per growp, Assessment Value =20% for your total marks for this subject Due date/ time: Please see Canvas, This case study project requires you to construct a database for a given scenario and develop same queries using SQL to find answers for given problems. There are specific business rules that you need to reflect on your database. Submission Recquirements - All submissions must be made bya team (even if your team size is one person) Files required: - A single MS WORD document named INF10004_Case study.DOCX that contains: A fully expanded and complete ERD diagram. The diagram must be an image that has been generated in www.diagrams.net. The diagram must follow the ERD symbol conventions used in lectures and labs. The relational schema. Network diagram (follow the same drawing guidelines from lectures). - A text file named INF1004_Case study_SQL.TXT that contains all of the 5Q L statements required to create, populate and query tables used in this assignment. - Your script must work with Oracle isQu. Junior - A WORD Document named INF10004_Case Study_OUTPUT.DOCX that contains the oufput generated by ISQL. JR after all of the statements in your script file [INF1004_Case study_SQL.TXT) been executed. Note: After you submit your assignment, a tutor will copy and paste the entire contents of the SQl. file into isQL. Jr. The tutor will press the Execute button in 15QL. Ir. This will execute the entire script. The script must work as expected without the tutor having to make any changes to contents of the script. Please easure that all comment symbols txt and end of line characters 2,t hare been included as necessary. Please test your script (as described above) in your own account prior to uploading your assignment. Late submissions will attract a penalty Page 1 of 7 Melbourne Adventure Hub (MAH) Business Narrative Melbourne Adventure Hub (MAH) runs camping and motel adventures around Australla. The adventures originate in Melbourne and go to places such as Philip Island, Great Ocean Road and Central Australia. Each adventure is run on a number of occasions each year (each occasion is known as an event). Each event has one tour leader. Each event may have many tour assistants. All leaders and assistants are employees of MAH. An employee who is a leader of one event may be an assistant of a different event. All emplovees possess a number of skills (such as cooking. first aid, bus driver). No two events for the same adventure code ever commence on the same date. People interested in participating in an adventure usually write to or ring MAH requesting an adventure brochure. MAH immediately assigns that person a customer identification code and also records the name, address and phone number of the person making the brochure request. MAal also records that date the request was made. A printed brochure is normally sent out the same day. At some pcint of every event, MAH has a special activity evening. Activities may include shark feeding, Shopping. Rock Climbing. Toga Party, Attending a Movie etc. Each activity has an activity code and an activity description. MAH administration specify which activities are available for each event (e.g., this will change from event to event depending upon weather conditions, access to resources, time of year etc.). For each customer attending an event, MAH assigns the customer a customer identification code funless the person has previously been assigned a customer identification code via a brochure request or a previous booking) and records the name, address and phone number. MAH also records the fee charged (same value as the current event price) to the customer*. They also record the total payment that has been made by the participant. (A customer may pay half the money at the time of the booking and then pay the remainder at a later date). MAH also records which activities the participant has a preference for iparticipants are encouraged to nominate for more than one activity in case one or more activities are cancelled due to unforeseen circumstances). Obviously, customers can only choose activities that are available for the event they have selected. - The current price of eoch odventure is stored in the dotabase. However, this price does change from time to time (due to inflotion and increases in fuel costs etc). Therefore, MAH need recard the price of an adventure for every booking that is mode (this is called a fee). Note: You are not expected to attempt to validate data such as: - To ensure customer books on two tours that overlap - To ensure staff members are no allocated to two tours that overlap Page 2 of 7 5. Populate your tables with the data provided. In some circurnstances you may need to add your own data. 6. Testing Create your own SQL statements that test all your primary key and foreign key constraints. Each of the these insert statements must fail, due to constraint errors such as duplicate primary key, invalid key, parent key not found ect. 7. Queries Part 1 Create a 5QL queries to do the following: 7.1. List Customer No, Name and Address all customers. The list must be in ascending Customer No sequence. 7.2. List adventure code, tour date, allocated staff names of all tours. The list must be in ascending adventure code, tour date, staff name sequence, 7.3. List adventure code, tour date, customer no and customer name for every booking made. The list must be in ascending adventure code, tour date, customer no sequence. 7.4. List adventure code, tour date, activity code and activity description for all activities offered on all tours. The list must be in ascending adventure code, tour date, activity code sequence. 7.5. List adventure code, tour date, customer no, customer name, activity code and activity description for all activities selected by customers on all tour events. The list must be in ascending tour code, event date, customer no, gift code sequence. 8. Queries Part 2 Create a SQL queries to do the following: 8.1. List all customers. Show the Customer No, Customer Name, total payments made by the customer Include customers that have never made a booking. The list must be in ascending Custamer No sequence 8.2. List Tour Code and total payments made for all tours. The list must be in ascending Tour Code sequence 8.3. List activity code and activity description and total number of times that a activity has been selected by a booked customer. Include all activities that may never have been selected. The list must be in descending total sequence. 8.4. List the adventure code, descriptions, and the most recent price for each adventure. E.g. 8.5. Uist Customer No, Customer Name, adventure code, tour date for all customers who requested a brochure for an adventure and then made a booking for that adventure. Appendix: Information derived from existing documents Below are copies of various word-processed reports/documents that are manually prepared by staff. Adventures Event (tour) Calendar Page 5 of 7 Prepared by Ravinda Wijesinghe 2/10/2022 Activitios Curtomer details Prepared by Ravinda Wiesinghe Brochure Repuects