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.
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.
The schema/insert file for creating this model is available in the archive ass3-student.zip - this file 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 file 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 file. 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.
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.
(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 -
(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.)
(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.
(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.
(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.