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 cabinbased 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 resorts 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 managers name and the managers 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 its rating is used to update the resorts 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 schemainsert le for creating this model is available in the archive ass3student.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 todate and tochar 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 ass3student.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 plagiarismcollusion 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 mcass3schminsert.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 Q1amcinsert.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 tablesdata 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 scriptsqueries 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 highestlowest 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 is 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 Q1bmcdm.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 Postcode
Town Latitude Town Longitude
: 50 Awesome Road : 4830
: 20.7256
: 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 wiFi. 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 wiFi. 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 hasnt 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 Q2mcqueries.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 managers name and the managers 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 totalbookingcharges. The output must be ordered by resortid. 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 resorts based on the number of bookings for a resort.
For the most popular resorts list the resort id, resort name, if the resort has a live in manager display Yes, if not No in a column headed liveinmanager, the resorts star rating if the resort has no ratings show No Ratings, the managers 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 latcity1, longcity1, latcity2, longcity2.
As an example, given two towns:
The straight line distance between these two towns can be calculated via:
select geodistance28.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 pois 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 SEPARATIONINKMS. 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.
Town
Latitude
Longtitude
Surfers Paradise
28.000767
153.429642
Currumbin
28.135731
153.486923
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 Q3mcmods.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 PLSQL
i Monash Cabins would like to add an attribute to the booking table which they can use to flag the state of a booking this 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 resorts 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. PLSQL 20 marks:
Your answers for these tasks must be placed in the supplied SQL Script Q4mcplsql.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 empdepttesttrigger.sql from the empdept trigger lab exercise these tests are part of the awardedmarksforeachquestion. Placethesecommandsbelowyourtriggerdefinitionforeachof 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 Q3ii.
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 overlapsconflicts 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 overlapsconflicts occuring here you will need to carefully consider which situations could cause such overlapsconflicts.
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 c annot 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:
Q1amcinsert.sql Q1bmcdm.sql
Q2mcqueries.sql Q3mcmods.sql Q4mcplsql.sql
If you need to make any comments to your markertutor 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 a3yourauthcateid.zip e.g., a3xyz123.zip before the assignment due datetime. Submit the a3xyz123.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 datetime.
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