Monash Cabins 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. A resort is located in a particular town. MC maintain details of points of interest (POI) in the local area that guests might wish to visit. Each POI is associated with only one town.
For each town in which a resort or point of interest is located MC records the town name, the state the town is located in, the latitude and longitude of the centre of the town, the average summer and winter day temperatures, the town’s elevation above sea level and its population. MC only records details of towns which have a resort or point of interest. MC does not record a town’s postcode since they are only interested in it as a location, not as a postal location.
For points of interest MC records the street and town in which it is located, the name of the point of interest (eg. Merimbula Aquarium), a brief description of the point of interest, its opening hours (if appropriate), and the type of point of interest (café, museum, restaurant, national park etc). The opening hours are recorded as the time at which the POI opens and the time at which it closes, only one set of these times are recorded (ie the POI is assumed to open and close at the same time all year). Each POI is only classified as one type of point of interest, for example, a cafe in a national park will be classified as a cafe. A scan of the potential MC data has indicated that some towns contain two POIs of the same name.
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 location’s popularity. Each resort has a name (eg. Merimbula Beach Cabins). A resort’s street address and town name are recorded. MC also records for each resort the star rating of the resort, which is determined from the average of all guest reviews, whether guests may bring their pet dog/s while staying at the resort and if the resort manager lives at the resort. Each resort has a single manager. MC assigns a manager id to each manager and records the manager’s name, postal address (including town and postcode) and the manager’s contact phone number. Some managers live on site (ie. at the resort); others live at their own private residence, however, MC does not need to record the manager’s residential address, only their postal address. Where a location has several resorts, a manager may manage several different resorts.
Each resort consists of a number of cabins – the cabins are numbered starting from cabin 1 at each resort. MC records how many bedrooms there are in each 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 MC. The guest name, postal address, country, 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, the number of children and the number of pets (if applicable) who will be staying. MC record the total charge for the particular booking, based on the cabin rate and the number of days of the guests stay.
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). These reviews are treated as general reviews of the resort rather than being related to a particular cabin or stay. A guest may complete many reviews of a particular resort during their stay. The review may also be made after the guest has left the resort. The date of the review is recorded (a guest is not permitted to complete two reviews on the same day).
The rates charged for a cabin depend on the cabin itself (some cabins have special features such as a spa) and the time of the year in which the guest is staying. For example, for seaside resorts, the highest rates are charged in the peak summer period (eg. Jan – Feb) when demand is at its highest. The rate for each cabin is recorded for each of these charge periods – rate periods may vary between different locations. Where a booking spans several rate periods, the booking is charged at the rate which applies on the first day of the booking.
When a guest vacates a cabin, MC use contract cleaners to clean the cabin. MC maintain a running sheet, for each resort, to record cleaning activity
A cleaning contractor is assigned a unique contractor number when first taking up work with MC. Contract cleaners may work as casual staff or fixed term staff. Fixed-term staff sign a contract to clean for a set period of time such as three or six months. Casual cleaners are not locked into any fixed period to clean, they are available on a weekly basis as work is available or suits them. Casual cleaners are contacted when work is available to clean a particular cabin and may accept or reject the job. Contract cleaners may shift between these two modes depending on what suits them better.
MC maintain a record of the contract history for all cleaners (a small sample of this record is shown below):
Please ENSURE your name and ID are shown on every page of any document you submit. If a document is a multipage document, such as for the normalisation, please also make sure you include page numbers on every page.
Moodle Part A Submission:
Using LucidChart, prepare an?INITIAL conceptual model? (Entity Relationship Diagram) for Monash Cabins (MC).
For this initial conceptual model, ?include what you see as identifiers (keys) for each entity only (other attributes are ?not required?) and all relationships.
This initial conceptual model must be submitted to Moodle as Assignment 1 Part A by 5 PM Monday of week 6. If this submission is not made by this date you will not be able to submit Assignment 1 Part
No marks are awarded for this submission, your tutor will provide feedback and guidance based on your submitted initial model which should be integrated into your continuing work in Part B (which is graded).
Perform?normalisation to 3NF? for the data depicted in the sample Cleaning Running Sheet (Document A) and the sample Contract History (Documents B and C – note that documents B and C are two samples of the same document).
During normalisation, you must:
You should include all attributes (not remove any attribute as derivable) ? Clearly show UNF, 1NF, 2NF and 3NF.
Clearly identify the Primary Key in all relations.
Clearly identify the partial and transitive dependencies (if they exist) in all 1NF relations. You may use a dependency diagram or alternative notation (see the normalisation tutorial sample solution for a possible alternative representation).
Your attribute names as used in your normalisation and those on your conceptual/logical models must be consistent i.e. the same name used on each for the same property.
Using LucidChart, prepare a?FULL conceptual model? (Entity Relationship Diagram) for Monash Cabins (MC).
For this FULL conceptual model, ?include what you see as identifiers (keys) for each entity, ?all required attributes?and all relationships.? This full model will be based on your feedback from your Part A submission, the normalisation above and further reading of the case study. It may be necessary to revise/update this model while developing your logical model in part 4 below.
Surrogate keys must not be added to this model?. Participation and connectivity? for all relationships must be shown on the diagram?.
Based on the final full version of your conceptual model, ?prepare a logical level design?for the Monash Cabins database.
The logical model must be drawn using the Oracle Data Modeler. The information engineering or Crow’s foot notation must be used in drawing the model.
All entities depicted must be in 3NF
Sequences must be used to generate numeric primary keys and check clauses must be applied to attributes where appropriate.
Be sure to include the legend as part of your model.
Generate the schema for the database?in Oracle Data Modeler and use the schema to create the database in your Oracle account. The? only? edit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (student name/id) and drop sequence commands.
Capture the output of the schema statements using the spool command.
Ensure your script includes drop table and sequence statements at the start of the script.
Name the schema file as ?mc_schema.sql.
The following files are to be submitted:
This model must be able to be opened by your marker and contain your full model otherwise your task 4 will not be marked?. For this reason, you should carefully check that your model is complete – you should take your submission archive, copy it to a new temporary folder, extract your submission parts, extract your model and ensure it opens correctly? ?before? submission.
|
Outstanding (Range D – HD) |
Adequate (Range P – C) |
Not Adequate (N) |
|
|
Identify the data requirements to support an organisations operations from the supplied case study and expresses these via a database conceptual model. (50) |
All MC operations are supported. ? Required number of entities are present ? All/most required attributes and keys have been captured ? Surrogate keys have not been added ? All/most required relationships have been captured ? All/most required cardinality and participation constraints have been captured |
Some MC operations are not supported. ? Majority of required entities are present ? Majority of required attributes and keys have been captured ? Surrogate keys have not been added ? Majority of required relationships have been captured ? Majority of required cardinality and participation constraints have been captured |
Many of the MC operations are not supported. ? None or few of the required entities are present ? None or few of the required attributes and keys have been captured ? Surrogate keys have been added ? None or few of the required relationships have been captured ? None or few of the required cardinality and participation constraints have been captured |
|
Understand and follow a database design methodology.(25) |
All/majority of the design processes have been correctly followed: ? All/most Normalisation processes are correct ? Dependency diagrams have been provided and match normalisation. ? ER diagram mapped to logical model with only minor errors/omissions. ? SQL Developer Relational model correctly generated from the logical model ? Sequences have been created to provide numeric primary keys where required |
Some of the design processes have been correctly followed: ? Majority of Normalisation processes are correct ? Dependency diagrams have been provided and match normalisation in majority of situations. ? ER diagram mapped to logical model with only small number of errors/omissions. ? SQL Developer Relational model correctly generated from the logical model ? Sequences have been created to provide numeric primary keys where required in the majority of situations |
Few of the design processes have been correctly followed: ? Significant errors during the Normalisation processes ? Dependency diagrams not provided or have major errors ? ER diagram mapped to logical model with errors/omissions. ? SQL Developer Relational model not correctly generated from the logical model ? Sequences have not been created to provide numeric primary keys where required |
|
Outstanding (Range D – HD) |
Adequate (Range P – C) |
Not Adequate (N) |
|
|
Understand and apply the relational model principles into practise. (15) |
All relational model principles have been followed: ? All/most entities are in third normal form. ? All/most Primary and Foreign keys are correctly identified. ? All/most data integrity requirements (Entity, Referential, Domain) have been correctly identified. |
Most relational model principles have been followed: ? Majority of entities are in third normal form. ? Majority of Primary and Foreign keys are correctly identified. ? Majority of data integrity requirements (Entity, Referential, Domain) have been correctly identified. |
Few of the relational model principles have been followed: ? None or few of the entities are in third normal form. ? None or few of the Primary and Foreign keys are correctly identified. ? None or few of the data integrity requirements (Entity, Referential, Domain) have been correctly identified. |
|
Able to generate and modify a schema given a logical model in SQL Developer. (5) |
The DDL script was executed without errors. |
The DDL script was executed with errors. |
|
|
Able to correctly use the required notation convention and be consistent in its usage. (5) |
All notations in the model are consistent. |
Some notations in the model are consistent. |
Few notations in the model are consistent. |
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download