Get 20% Off + $20 Signup Bonus ! Limited Time, Hurry !
Get 20% Off + $20 Signup Bonus ! Limited Time, Hurry !
Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Stuck on Your Question?

Get 24x7 live help from our Top Tutors. All subjects covered.

loader
250 words

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Students Who Viewed This Also Studied

150 Pages
IS1S466 Information Management Assurance And Security

Question: Extenuating Circumstances If there are any exceptional circumstances that may have affected your ability to undertake or submit this assignment, make sure you contact the Advice Centre on ...

Course

IS1S466

Type

Programming

Subject

Database

University

University of South wales

13 Pages
Management

Task Prod_code Prod_Name Purchase_date Qty_sold Prod_price Inv_No Inv_Date Vendor_ID Vendor_Name ES12345 Esky Soft Cooler 12/7/2016 60 19.50 B-5565 17/7/2016 112 Innisfail MAK2347 Angle Grinder 15/9/2 ...

Type

Programming

Subject

Database

5 Pages
Management

Assignment Details You are required to create a draft scope document for the project described above. Your document must include the followings:1.    A title page with your name as auth ...

Type

Programming

Subject

Database

4 Pages
WCOM1350 Introduction to Database Design and Management

Questions: 1. Discuss what is star schema, fact table and dimension tables. When do we need to use star schema? 2. What is Hadoop? What are the components of Hadoop? What is MapReduce technique and ...

Course

WCOM1350

Type

Assignment

Subject

Database

University

Macquarie University

FIT9132-Introduction to Databases

Question

Answered

Purpose

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 specied 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.

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.

Data Manipulation

(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 - 

a.5 RESORTS,

b.20 CABINS, 

c.25 BOOKINGS

d.15 REVIEWS

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

FIT9132-Introduction to Databases

Answer in Detail

Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

32 More Pages to Come in This Document. Get access to the complete answer.

MyAssignmenthelp.com provides students with the chance to solve their academic problems at once by offering best quality coursework help online services. Our services include coursework writing help, coursework editing and proofreading help. So students who often wonder, who can do my coursework online without any error or can expert write my coursework online, find our services most effective, convenient and worthwhile. This is why students prefer to buy coursework price from us.

More FIT9132 FIT9132-Introduction to Databases: Questions & Answers

Q
icon

We aren't endorsed by this University

IS1S466 Information Management Assurance And Security

Question: Extenuating Circumstances If there are any exceptional circumstances that may have affected your ability to undertake or submit this assignment, make sure you contact the Advice Centre on your campus prior to your submission deadline. Fit to sit policy: The University operates a fit t ...

View Answer
Q

Management

Task Prod_code Prod_Name Purchase_date Qty_sold Prod_price Inv_No Inv_Date Vendor_ID Vendor_Name ES12345 Esky Soft Cooler 12/7/2016 60 19.50 B-5565 17/7/2016 112 Innisfail MAK2347 Angle Grinder 15/9/2016 10 85 B-5570 18/9/2016 106 RC Tools KAR62705 High Pressure Cleaner 13/10/2016 50 190 B-5572 18/9 ...

View Answer
Q

Management

Assignment Details You are required to create a draft scope document for the project described above. Your document must include the followings:1.    A title page with your name as author, project name, client name, date and a version control table stating this as ‘Draft 1&rsquo ...

View Answer
Q
icon

We aren't endorsed by this University

WCOM1350 Introduction to Database Design and Management

Questions: 1. Discuss what is star schema, fact table and dimension tables. When do we need to use star schema? 2. What is Hadoop? What are the components of Hadoop? What is MapReduce technique and how It works? 3. Define what is data management and discuss two ethical issues in two aspects of da ...

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

Choose Our Best Expert to Help You

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?