For this assignment, you will populate these tables with appropriate test data and write the SQL queries and triggers

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

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.

For this assignment, you will populate these tables with appropriate test data and write the SQL queries and triggers specified 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.

 

In each file fill in the header details with you name and student ID before beginning any work.

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).

 

Assignment Tasks

Using the supplied schema file(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):

• 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 speci ed 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

 

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.

 

• 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

  

• 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, )

[1 mark]

• 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

 

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]

 

• 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]

 

• 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

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of