Question

In: Computer Science

Database You have just started a new position on the database design staff at Gizmonic Consultants,...

Database

You have just started a new position on the database design staff at Gizmonic Consultants, Inc.

Your first project is to translate the database requirements for Continental Hotels into an ER representation. In the next project, you will derive a relational schema from an ER diagram and implement the schema, populate it, and query over it.

Requirements:

Develop an ERD to capture the entities and relationships specified in the requirements documentation below. Use only the conventions covered in class.

  1. Show keys [partial keys] for each entity [weak entity].
  2. Give cardinality (1:1, 1:m, m:n) and participation constraints (partial, total) for each binary relationship.
  3. Your whole ERD should not be more than one page in length, although you can show complete attributes in a separate diagram and just keys/partial keys on the main diagram.
  4. You should have at least 4 entities and you may have quite a few relationships between them.
  5. Keep a list of assumptions that represent any modeling decisions you have to make.
    • Your assumptions should not contradict any information given in the requirements, and you should strive to make as few additional assumptions as possible.
    • Anything you model that is not explicitly stated in the requirements should be stated in your assumptions.

Phase 2

  1. Submit one well-formed, consensus ER diagram using Dia with the ER sheet that satisfies the requirements
  2. A list of your design decisions (any additional assumptions beyond what is given explicitly in the problem). Organize your assumptions in 3 categories:
    1. related to primary keys,
    2. related to cardinality/participation constraints for relationships, and
    3. miscellaneous (anything else).
  3. Translation of the team ERD into RDB tables using the algorithm and conventions

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Continental Hotels Requirements

  • General Description

Continental Hotels is a chain of hotels that operates in several cities. There are currently 10 locations, and each hotel has about 100 guest rooms. The rooms are normally 80% occupied, with an average length of stay of three days. The hotels are frequented by both business travelers and vacationers. The chain is expanding to additional locations. The current guestroom reservation system is slow and unreliable and a new system is needed to handle reservations and associated guest billing.

  • Basic Operations

Guests normally use the chain’s website to make a room reservation, supplying the dates requested, the number of guests, the number of rooms needed, and the area desired. The site shows the locations that have availability for the dates requested that are in the desired area. The user then chooses a location and a summary of the available rooms for that site is displayed, showing a brief description of the room type and the standard rate for each available room. Locations have unique names like “Columbus Downtown” and “Cincinnati North.”

The user can see a more detailed description of each room by clicking on a button. The user has the option to request a special rate due to membership in a group, military status, or age. The list of available rooms and their cost is updated to reflect the discounted price, if any. The chain also offers a rewards program for frequent guests. The website allows the user to enter or retrieve his or her membership number and password, and the site displays the number of rewards points the user has accumulated.

Whether the user belongs to the rewards program or not, he or she can reserve up to three rooms, and must choose a room type and accept the cost for each one from the list displayed, as well as entering the number of the proposed occupants and any special requests for each room, from a menu of special requests. The customer provides a credit card number to guarantee the reservation, and may choose to use some or all of his or her rewards points, if any, towards the cost of the rooms.

At the conclusion of the process, a confirmation number is assigned for the reservation. If a customer prefers, this same process can be done by telephone or even by mail.

A customer can cancel a reservation up to the day before he or she is due to check in with no penalty. If the customer does not cancel and is a no-show, the room cost(s) for one day will be charged to the credit card account provided in the reservation. A fictitious room number (-1) is used for this purpose. In addition to reservations, the hotel can accommodate walk-in guests, provided there are rooms available. In that case the customer information is taken, and a reservation is made for the same day for the period desired. Guests can also extend their stays past the reserved date, provided there are rooms available. The ending date of the current reservation is updated accordingly.

When a guest checks in with or without a reservation, guest IDs are checked, rooms are assigned, and an imprint of the credit card that will be used for billing is taken. If separate bills are requested for rooms at check-in, additional credit card imprints are taken, and basic information about the credit card holder, who is now considered the customer holding a reservation for that room, is taken. Miscellaneous charges for such items as room service, meals in the hotel restaurants or coffee shop, movie rentals, and telephone calls, as well as the basic room charges, will be billed to the credit card account for each associated room. Guests can access their room account information to see a summary of the charges for each room each day. At the end of the stay, guests are requested to fill out an evaluation form for each room, either on paper or online.

  • Information Needs

Some of the reports that the system should be able to produce include the following, for each of the hotels in the chain:

Guest Bill – This should include, for each room:

Invoice number, room number, guest name, guest address, guest telephone, credit card number, number of persons. For each day of the stay, it should show the date, room charge, room tax, and a list of additional charges - room service charges (date, time, amount), hotel restaurant charges (restaurant name, date, time, amount), telephone charges (date, number called, length, cost) and any other items. At the end of the bill, the total charges, any discount for rewards points, and the total paid or charged to the credit card are given. If additional charges are found once the guest has checked out, a revised bill is prepared and sent to the guest.

Weekly Room Utilization Report – This report is normally produced at the end of each week, showing the utilization of rooms during the week. Note that some of the hotel’s guest rooms may not be available for rental because of damages, renovations, or other reasons. For each day, the report shows date, number of rooms available to be rented, number occupied, number unoccupied, number of rooms reserved, number of no-shows, number of walk-ins.

At the bottom of the report, the totals of each of these numbers for the week is shown.

Housekeeping Daily Room Requests Report – A report is created daily showing any special requests for guests who are checking in that day or who are already registered that must be filled by the housekeeping staff, such as extra pillows, rollaway beds, and so forth.

Daily Checkout Report - The report lists the rooms that will be vacated that day so that the housekeeping staff can prepare them for new guests after current guests depart.

  • Query Checklist

□ List all the hotels (names) in the Cincinnati area.

□ List all the areas that do not have a hotel with a room with the type “queen size, handicapped accessible.”

□   List names of guests who watched the movie “Buckaroo Banzai” at any hotel.

□   List names of guests who were charged for a no-show reservation and who have a rewards membership.

□   List the sum of all miscellaneous charges by guest name and room number at the “Downtown Columbus” hotel in June 2019.

Solutions

Expert Solution


Related Solutions

1. You just started at a new position at a large investment bank. You have been...
1. You just started at a new position at a large investment bank. You have been assigned to help a senior analyst, Ms. Jones, on a Dutch Auction of shares for Thornton & Danaher Inc. The equity of the firm is currently closely held by the founding families and the senior managers, so there is no available ma rket price. They are hoping to raise about $300,000,000. This represents about 20% of the fi rm’s current equity value. Ms. Jones...
You have just joined a company as a new staff accountant. Your company is in an...
You have just joined a company as a new staff accountant. Your company is in an acquisition mode (acquiring 5 to 10 smaller companies each of the last 4 years). You are excited to hear that you are going with an acquisition team to facilitate another acquisition (Company X). You have been instructed to sit down with Company X’s controller and explain some pre-acquisition (before the acquisition is finalized) accounting expectations. Expectations for Company X before the acquisition is finalized....
You have just started a new job and are thrilled to learn that your new employer...
You have just started a new job and are thrilled to learn that your new employer offers a 401(k) retirement plan to its employees. Your annual salary is $40,000. Assume the IRS allows you to contribute up to $24,000 to your 401(k). You’ve decided to contribute 7% of your annual salary to the plan. Questions: How much more money would you need to contribute to meet the maximum allowable contribution set forth by the IRS? The company offers you a...
You have just started a new job that offers a retirement savings account. You have two...
You have just started a new job that offers a retirement savings account. You have two options: You can invest 5% of your monthly wages at 2% OR You can invest 4% of your monthly wages at 4%. Both are compounded monthly. b. Assume that you will always make $45,000 annually, how much will you have saved with the better plan after 15 years? c.Assume that you will always make $45,000 annually, how much will you have saved with the...
Assume that you have just been promoted to a new position where you have leadership responsibility....
Assume that you have just been promoted to a new position where you have leadership responsibility. You have never had formal leadership responsibilities before, and you wish to maximize the possibility that you will be successful with this new responsibility. Reflect upon the major leadership theories and models that we have learned about in this unit. Develop a Pro and Con list involving at least three of these theories/models. Reflect on each theory and discuss how the theory fits with...
You have just started a new job and your employer has enrolled you in KiwiSaver. This...
You have just started a new job and your employer has enrolled you in KiwiSaver. This is the first time you have been enrolled in KiwiSaver and you decide not to “opt out”. You are interested in estimating how much your KiwiSaver fund could be worth when you retire. You make the following assumptions: • You have just turned 30 and will retire in exactly 35 years when you are 65. • Your salary is $50,000 this year and you...
You have just started a new job and your employer has enrolled you in KiwiSaver. This...
You have just started a new job and your employer has enrolled you in KiwiSaver. This is the first time you have been enrolled in KiwiSaver and you decide not to “opt out”. You are interested in estimating how much your KiwiSaver fund could be worth when you retire. You make the following assumptions: • You have just turned 30 and will retire in exactly 35 years when you are 65. • Your salary is $50,000 this year and you...
You have just started a new job and your employer has enrolled you in KiwiSaver. This...
You have just started a new job and your employer has enrolled you in KiwiSaver. This is the first time you have been enrolled in KiwiSaver and you decide not to “opt out”. You are interested in estimating how much your KiwiSaver fund could be worth when you retire. You make the following assumptions: • You have just turned 30 and will retire in exactly 35 years when you are 65. • Your salary is $50,000 this year and you...
You have just started a new job with a significant increase in salary above what you...
You have just started a new job with a significant increase in salary above what you were earning when you originally negotiated your student loan repayment. The salary increase affords you the opportunity of increasing your monthly loan payments, thereby allowing you to retire the debt sooner than originally planned. You have six years remaining in the original payback plan on a loan of $55,000, with an interest rate of 2.4% and a monthly payment of $566.74. With your new...
You have just started a new job and your employer has enrolled you in KiwiSaver. This...
You have just started a new job and your employer has enrolled you in KiwiSaver. This is the first time you have been enrolled in KiwiSaver and you decide not to “opt out”. You are interested in estimating how much your KiwiSaver fund could be worth when you retire. You make the following assumptions: • You have just turned 30 and will retire in exactly 35 years when you are 65. • Your salary is $50,000 this year and you...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT