FIT9132 Introduction to Databases 2019 Semester 2 Assignment 3 – SQL – Monash Cabins (MC) Assignment weighting 20% – Lecturer in Charge: Manoj Kathpalia Monash Cabins (MC) is a chain of resorts (holiday destinations) located around Australia. At each of these resorts MC provides cabin-based accommodation for its guests – any given resort consists of several independent cabins which guest may holiday in. MC record details of point of interest that guest might wish to visit during their stay, such as parks, museums etc which may be in the same town as the resort or in other close by towns. For each town in which a resort is located, or which has a point of interest, MC record a unique town id to identify the town. The town name, state of Australia, the average summer and winter day temperatures and its population are also recorded. The latitude and longitude of the centre of the town are also recorded. For points of interest MC record a unique identifier, the street address and town in which the point of interest is located, the name of the point of interest (eg. Merimbula Aquarium), its opening hours, if appropriate, and a brief description of the point of interest. Each resort is assigned a unique resort id. MC has several resorts in some towns and only a single resort in others depending on the locations popularity. Each resort has a name (eg. Merimbula Beachside Cabins). A resort’s street address, town and postcode it is located in are recorded. MC also record for each resort the Guest Star Rating of the resort, which is determined from the guest reviews. A resort is managed by a manager. MC assign a manager id to each manager and record the manager’s name and the manager’s contact phone number. Some managers live on site (ie. at the resort), others live at their own private residence. MC wish to record if a manager is living on site or not for each resort. A manager can only live in one particular resort. A manager may manage several different resorts. Some managers manage several resorts which may be quite some distance apart. The management role is such that the manager can employ locals to run the day to day activities and oversee the resort via electronic means. Each resort consists of a number of cabins – the cabins are numbered starting from cabin 1 at each resort. MC records how many rooms are in a cabin, the sleeping capacity of the cabin (how many people it can sleep) and a description of the cabin to provide potential guests with some details to assist their decision making. Page 1 of 13 MC guests, those staying at the resorts, are assigned a unique guest number when they first register with MCC. The guest name, address, email and contact phone number are recorded. A guest makes a booking with MC by choosing the resort they wish to stay at and the cabin they wish to stay in. Guests are required to provide the date they wish to book from and the date they wish to stay to. They must also supply MC with the number of adults and the number of children who will be staying. Each booking is assigned a unique booking id. All cabin bookings are for entire days ie. when a guest vacates a cabin at the end of their stay a new booking cannot occur until the next day, allowing Monash Cabins time to clean and restock cabins. When a booking is placed MC calculate the total booking charge and record this as part of the booking details. Guests are offered the opportunity to provide a review of the resort, they are not required to do so, but if they do they provide a comment and a rating from 1 (poor) to 5 (outstanding). Each review is assigned a unique review id. Once a review is entered it’s rating is used to update the resort’s star rating. A data model has been created for Monash Cabins and is shown below (it is also available from Moodle): For this assignment, you will populate these tables with appropriate test data and write the SQL queries and triggers specied below. You must ensure that any activities you carry out in the database conform to the requirements of the model displayed above. Page 2 of 13 The schema/insert le for creating this model is available in the archive ass3-student.zip – this le creates the Monash Cabins tables and populates several of the tables – you should read this schema carefully and be sure you understand the various features. You must not alter the schema le in any manner, it must be used as supplied. Penalties will apply to queries that use subqueries and views unnecessarily. In handling dates, default date format must not be assumed; you must make use of the to_date and to_char functions in all date situations. Failure to do so will incur a 50% grade penalty for questions involving dates. You may need to rerun the schema, especially when you have been experimenting with your solutions and may have corrupted the database unintentionally. If you suspect that there might be such problems, simply rerun the schema. The schema includes the appropriate drop commands at the head of the le. The ass3-student.zip archive also contains five SQL scripts for you to code your answers in, you should ensure these files are regularly pushed to Git so a clear development history is available. In each file fill in the header details with you name and student ID before beginning any work. REMEMBER you must keep up to date with the Moodle assignment 3 forum where further clarifications may be posted (this forum is to be treated as your client). Please be careful to ensure you do not post anything which includes your reasoning, logic or any part of your work to this forum, doing so violates Monash plagiarism/collusion rules. You are free to make assumptions if needed however they must align with the details here and in the assignment forums and must be clearly documented (see the required submission files). Page 3 of 13 Assignment Tasks Using the supplied schema le (mc_ass3_schm_insert.sql) create the tables for the Monash Cabins system and insert the supplied values. This provides a starting point for the following activities. Q1. Data Manipulation (20 marks): (a) Load selected tables with your own additional test data: using the supplied Q1a-mc-insert.sql script file, and the SQL commands which will insert, as a minimum, the following sample data – ● 5 RESORTS, ● 20 CABINS, ● 25 BOOKINGS ● 15 REVIEWS Please note, these are the minimum number of entries you must insert; you are encouraged to insert more to provide a richer data set to draw from. The primary key values for this data must be hardcoded values (ie. not make use of sequences) and consist of values below 100. Dates used should be chosen after the 1st January 2019. For this task ONLY, you may lookup and include values for the loaded tables/data directly where required. The script must contain a single COMMIT statement as the last line of the script, ie. all listed actions should be treated as a single transaction. In carrying out this task you must not add any further data to tables which were previously populated by the supplied schema le. [10 marks] Design your test data so that you get output for the SQL scripts/queries specied below – this may require you to add further data as you move through completing the required tasks. Queries that are correct and do not produce output using your test data will lose 50% of the marks allocated, so you should carefully check your test data and ensure it thoroughly validates your SQL queries. For all subsequent questions (Q1b onwards) you are not permitted to manually: ● lookup a value in the database, obtain its primary key or the highest/lowest value in a column, or ● calculate values external to the database eg. on a calculator and then use such values in your answers. You must ONLY use the data as provided in the text of the questions. Where a particular case for a word i
s provided you must use that case. You may divide names such as Garrot Gooch into a first name of Garrot and a last name of Gooch, if required. Failure to adhere to this requirement will result in a mark of 0 for the relevant question. Page 4 of 13 (b) For the following tasks, your SQL must correctly manage transactions and use sequences to generate new primary keys for numeric primary key values (under no circumstances may a new primary key value be hard coded as a number or value). Your answers for these tasks must be placed in the supplied SQL Script Q1b-mc-dm.sql (i) Create a sequence which will allow entry of data into the RESORT table – the sequence must begin at 100 and go up in steps of 1 (i.e., the first value is 100, the next 101, etc.) [1 mark] (ii) Monash Cabins is doing very good business these days because of a booming economy and their highly competitive rates. They have now opened a new resort called Awesome Resort to catch up with the demand. The details of the new resort to be added to the MC database are as follows: Street Address : 50 Awesome Road Postcode : 4830 Town Latitude : -20.7256 Town Longitude : 139.4927 The manager of Awesome Resort will be Garrott Gooch (Ph: 6002318099) who will not be a live in manager. To start with, there will only be two cabins at Awesome Resort. The details of the two cabins are as follows: Cabin 1 Bedrooms 3 Cabin 1 Sleeping Capacity 6 Cabin 1 Description Free wi-Fi. kitchen with 400 ltr refrigerator, stove, microwave, pots, pans, silverware, toaster, electric kettle, TV and utensils Cabin 2 Bedrooms 2 Cabin 2 Sleeping Capacity 4 Cabin 2 Description Free wi-Fi. kitchen with 280 ltr refrigerator, stove, pots, pans, silverware, toaster, electric kettle, TV and utensils You should use appropriate new cabin numbers for this resort when adding the cabins to the MC database. [5 marks] Page 5 of 13 (iii) A few weeks after opening the new resort, MC has decided to replace the manager. The new resident manager of Awesome Resort will be Fonsie Tillard (Ph: 9636535741). During this time other new resorts may have been added to the system. [2 marks] (iv) After several months, the newly opened resort hasn’t had any bookings and MC has now decided to close this resort. Remove this resort from the database. Note that more cabins may have been added to this resort since it was opened. [2 marks] Page 6 of 13 Q2. SQL Queries (40 marks): Your answers for these tasks must be placed in the supplied SQL Script Q2-mc-queries.sql ANSI joins must be used where two or more tables are to be joined, under no circumstances can “implicit join notation” be used – see the week 7 lecture slide 22. (i) Show the resort and managers details for those resorts which do not currently have a resort star rating and which have a live in manager. The output must list the resort name, the resort address as a single column called ‘RESORT ADDRESS’ (made up of street address, town name and post code), the manager’s name and the manager’s phone number. The output must be ordered by resort postcode descending, where two resorts are in the same post code order them by the resort name. Your output must have the form shown below (your data will clearly be different). [4 marks] (ii) Show those resorts which have charged more in booking charges (the resorts total booking charges) than the average total booking charge made by all resorts. The output must list the resort id, resort name, resort street address, resort town, resort state, resort postcode and total booking charges for the resort in a column called “total_booking_charges’. The output must be ordered by resort_id. Your output must have the form shown below (your data will clearly be different). [6 marks] (iii) Reviews which are provided by guests were intended to be a review by a guest who has completed their stay at the resort (ie. the review is entered after they have left the resort). Unfortunately, the current Monash Cabins system allows guests, once registered in the system, to add a review for a resort even if they have not stayed at the resort (they may have a future booking or may not even have a booking for the resort they are rating with their review). To assess the issues with the current review data provide a list of all invalid reviews (ie. reviews made by a guest who has not completed their stay at the resort they are reviewing or a guest who does not have any booking for the resort they are reviewing). The output should list the review id, guest no and name, resort id and name, review comment and the date the review was completed. The output must be in ascending date reviewed order. Your output must have the form shown below (your data will clearly be different). In arriving at your solution you may assume that there have been no booking cancellations and that no guest left the resort before their booking to date. [6 marks] Page 7 of 13 (iv) Show the total number of cabins at those resorts which have cabins with more than two bedrooms and indicate how many cabins at the resort have more than two bedrooms. The output must include the resort id, the resort name and the accommodation details in the format as indicated below (your data will clearly be different). The output must be in resort name order. [6 marks] (v) Show the most popular resort(s) based on the number of bookings for a resort. For the most popular resort(s) list the resort id, resort name, if the resort has a live in manager display ‘Yes’, if not ‘No’ in a column headed ‘live_in_manager’, the resorts star rating (if the resort has no ratings show ‘No Ratings’), the manager’s name and phone number and the count of bookings which are in the system for the resort. The output must be ordered by the resort id.Your output must have the form shown below (your data will clearly be different). [8 marks] Page 8 of 13 (vi) You have been provided with a special function (geodistance) in your Monash Oracle account which returns the straight line distance between two points expressed in latitude and longitude. The function is called via geodistance (lat_city1, long_city1, lat_city2, long_city2). As an example, given two towns: Town Latitude Longtitude Surfers Paradise -28.000767 153.429642 Currumbin -28.135731 153.486923 The straight line distance between these two towns can be calculated via: select geodistance(-28.000767, 153.429642, -28.135731, 153.486923) from dual; which yields a straight line (or air) distance of 16.03 Km Based on the Town and POI data you have been supplied with, Monash Cabins would like to create a list of points of interest (poi’s) close to their various resorts to help guests who would like to tour the local area. The output must show the resort id, resort name and for each point of interest within 100 Km straight line distance of the resort; the poi name, poi street address, poi town, poi state, poi opening time and the straight line distance to the POI in a column called ‘SEPARATION_IN_KMS’. Within 100 Km is inclusive (ie 100 Km from the resort or less). The output must be ordered by the resort name and for a given resort by the separation in kms. Your output must have the form shown below (your data will clearly be different). [10 marks] Page 9 of 13 Q3. Design Modifications (20 marks): Your answers for these tasks must be placed in the supplied SQL Script Q3-mc-mods.sql These tasks should be attempted only after Q1 and Q2 have been successfully completed. They are to be completed on the “live” database ie. the database with the data loaded from your previous work. For this question you must not make use of any PL/SQL (i) Monash Cabins would like to add an attribute to the booking table which they can use to flag the state of a booking – t
his attribute will have a value of either (C, D, F or P) where these mean: C Completed Booking, guest has completed their stay and left the resort D Booking has been cancelled F Future Booking P Guest is on the resort premises (ie currently staying at the resort) This attribute must be initialised based on the data which is currently stored in the system. You may assume that none of the current booking entries represent cancelled bookings. All new bookings after implementing this requirement must automatically be assigned a value of ‘F’. [6 marks] (ii) Monash Cabins would like to be able to easily determine the number of completed bookings any particular guest has had with the company. A completed booking is one in which the guest has booked and stayed at an MC resort and the stay is over, as listed in (i) above. Add a new attribute which will record the number of completed bookings a guest has had with the company. This attribute must be initialised to the correct current number of completed stays based on the data which is currently stored in the system. [6 marks] Page 10 of 13 (iii) Monash Cabins has found that having a single manager for each resort is causing problems for the larger popular resorts. As a consequence they have decided to appoint multiple managers to a resort, where necessary. Where multiple managers are assigned, the company wishes to designate a role for each of the different managers such as Bookings Manager (BM), Cleaning Manager (CM) and Maintenance Manager (MM) – this range of manager roles will be added to as the need arises and is intended to be able to be changed easily. Each role code will have exactly 2 letters. Where a resort has only a single manager, a role will not be assigned. The popular resort(s) that will require more than manager is only one at this stage which is Byron Bay Exclusive Resort with town latitude: -28.6474 and town longitude: 153.6020. This resort will have the current manager manage bookings and Garrott Gooch (Ph: 6002318099) will manage cleaning and Fonsie Tillard (Ph: 9636535741) will manage maintenance. Change the database to satisfy this requirement. Note: You should aim to use as few DDL and DML statements as possible to achieve this requirement. [8 marks] Page 11 of 13 Q4. PL/SQL (20 marks): Your answers for these tasks must be placed in the supplied SQL Script Q4-mc-plsql.sql For each of these questions, as part of your answer, you must create a set of SQL commands which will demonstrate the successful operation of your trigger (as an example see the file emp_dept-test-trigger.sql from the emp-dept trigger lab exercise) – these tests are part of the awarded marks for each question. Place these commands below your trigger definition for each of the tasks. Ensure your trigger definition finishes with a slash(/) and blank following line as detailed in the lab 11 notes. (i) Write a trigger which will, from this point forward, automatically maintain the completed bookings attribute you added in Q3(ii). [4 marks] (ii) Write a trigger which will prevent a review from being entered if the guest has not stayed at the resort they are adding a review for (ie. the stay has not been completed). Note that this trigger only needs to prevent the review from being added, it does not need to calculate the new resort star rating if the guest has stayed at the resort. [6 marks] (iii) Monash Cabins has discovered a major weakness with their database design – it is possible for a booking to be added which overlaps/conflicts with a current booking. For example a booking may be made for a particular resort and cabin which starts before a currently recorded booking and ends in the middle of a current booking. Write a trigger which will prevent all overlaps/conflicts occuring (here you will need to carefully consider which situations could cause such overlaps/conflicts). [10 marks] Page 12 of 13 SUBMISSION REQUIREMENTS Due Date: Friday 25th October 2019 at 6 PM (Week 12) Please note, if you need to resubmit, you cannot depend on your tutors availability, for this reason please be VERY CAREFUL with your submission. It is strongly recommended that you submit several hours before this time to avoid such issues. For this assignment there are five files you are required to submit: ● Q1a-mc-insert.sql ● Q1b-mc-dm.sql ● Q2-mc-queries.sql ● Q3-mc-mods.sql ● Q4-mc-plsql.sql If you need to make any comments to your marker/tutor please place them at the head of each of your solution scripts in the “Comments for your marker:” section. These files must be zipped into a single zip file named a3-.zip e.g., a3-xyz123.zip before the assignment due date/time. Submit the a3-xyz123.zip file to Moodle before the due date. The individual files must also have been pushed to the FIT Git server with an appropriate history as you developed you solutions. Late submission will incur penalties as outlined in the unit guide (5 marks deduction per day or part thereof). Please note we cannot mark any work on the Git Server, you need to ensure that you submit correctly via Moodle since it is only in this process that you complete the required student declaration without which work cannot be assessed. It is your responsibility to ENSURE that the files you submit are the correct files – we strongly recommend after uploading a submission, and prior to actually submitting, that you download the submission and double check its contents. Your assignment MUST show a status of “Submitted for grading” before it will be marked. If your submission shows a status of “Draft (not submitted)” it will not be assessed and will incur late penalties after the due date/time. Please carefully read the documentation under the “Assignment Submission” on the Moodle Assessments page which covers things such as extensions and resubmission. Page 13 of 13
