Create an E-R diagram

Database Development Project Case The following case will be used for the project. Bear in mind that it will be used for data modeling normalization, database design, and implementation using Oracle, SQL-Developer, and Visual Basic. The segments involving modeling, normalization, and design, will be performed in a group. To benefit fully from the implementation exercise, you need to have direct hands-on experience with all the software listed earlier. Details about individual report requirements and due-dates will be handed out separately. The following description forms the only source of information for completion of the project reports. In areas that it is lacking, appropriate assumptions need to be made. The system deals with management of data at a small resort. While the data may conceptually appear simple, there is a fair amount of complexity that needs to be dealt with. Environment Midwest Resort is a medium-sized themed resort in a semi-rural setting outside a large mid-western city It is a mature enterprise, and was established in 1991. It started out as a small 30 room lodge nestled near a golf course, and has been converted to a more family oriented resort with a variety of additional activities. It is also located near a ski facility, and Midwest acquired the operation to support year-round occupancy Over the years the resort has grown in several ways. It added several tennis courts for guests to use during their stay. It added a fitness center, stocking it with a variety of stationary exercise equipment. For the more athletically inclined, it added several indoor courts for basketball, volleyball, and racquetball. Newer additions include an indoor pool and a rock climbing wall. In order to stay competitive, it also added a small indoor waterpark. Recognizing that not all guests may be knowledgeable about all sports, Midwest hired a small staff of instructors to cover these sports. The instructors are available for one-on-one instruction, and also lead group classes - offered on a set schedule The facility has grown through the addition of newer building distributed over the resort area, to the point that it now includes 275 rooms. As a result, Midwest has had to add restaurants and bars in each of the new buildings, under the assumption that if guests have to leave the building for meals, they could just as well leave the facility. Currently, the resort has three restaurants and two bars. In keeping with the overall character of the resort, the restaurants and bars all have sports themes. In an effort to increase utilization of its facilities, Midwest added a conference facility that can accommodate about 100 participants. This includes one large lecture room, several meeting rooms, and a host of breakout rooms. It also had to restructure its catering facility to support the conference operations. The conference portion of Midwest's business has had mixed success. While relatively popular for small conferences in summer, it has not been able to drum up sustained activity in winter months-trudging between buildings and conference center facilities in the Midwest has relied on a custom-developed set of programs for all its billing activities to date. However, with the growth in the number of rooms, and the explosion in the number and range of different activities it supports, the current system has been patched over several times and is starting to become a bottleneck. The owners are of the opinion that a database driven system would adequately address these problems, and have retained your services to design the database for this system. They also recognize that the sheer complexity of supporting all Midwest's operations may prove daunting, and have elected to deploy this in a phased manner. For the initial version, the conference operations will be outside the scope of the system. Reservation The current reservation process is relatively simple. People seeking to stay at the resort can make a reservation by calling Midwest Resort or filling out a form over the web. The web-based form does not interface very well with the resort's guest management system, and that is another area feel can be improved. Reservation involves selecting a room type, the dates of stay, and the number of guests that will be included - as illustrated in Figure 1. Some additional requirements, like a microwave, infant crib, ete. are available for a charge, and may be included with the request. The system checks availability for the dates in question, and returns a list of different room types available, with their that the owners rates. Midwest/ Resort 1 Resort way Resort Town, WI 99999 $159 169 $189 $229 888-123-4567 Single King Standard King Whirlpool King Suite Check in Date Check out Date 1/6/2019 1/8/2019 Room Type John Doe 21 Single King 123 Any Street None Standard v Some Town WIV53999 Find Room 414-999-9999 jqdoe@myisp.com Visa 4333-1234-5678-9012 Make Reservation Figure 1. Reservation Request When checking availability, the system ensures that a specific room is available for the entire stay. If one room is available for part of the stay, and a different room is available for the remainder, then the system indicates that a room is unavailable, i.e. mid-stay room swapping is not permitted. In effect, this means that the resort cannot achieve 100% occupancy. In addition, each room has a specific capacity, and the system also checks to make sure that the number of guests does not exceed the capacity reques it converts the request are a little more tricky to figure out. Different rates are quoted for weekday versus weekend, for in-season versus off season. In-season weekend rates are the highest; in-season w 85% of that; off-season weekend-rates are 65% thereof, and off-season weekday rates are 55% of the maximum. Weekends are considered to be Friday and Saturday nights. Holidays are treated as ted. If so, into the appropriate number of rooms required to accommodate all guests. Rates as well as eekday rates are If a stay spans two different rates, then the total cost of the stay is computed and an average rate is derived based on this. Assuming a room (or rooms) is available for an acceptable rate, the potential guest then enters demographic, contact, and credit card information. A credit card is required to make a reservation. Midwest accepts American Express, Visa, Master Card, and Discover at this point. Midwest requires that a deposit of one night is made. The system checks to make sure that the credit card is valid, makes a charge for the deposit. At this point, a room is assigned, a confirmation number generated, and the information relayed back to the guest, as illustrated in Figure 2. Midwest requires that cancellations be done at least two days prior to arrival, or the deposit is forfeited. If cancellation is per normal procedure, then a credit transaction is made out on the specific card. In the event of a deposit being forfeited, the manager, at his or her discretion, may issue a credit for a future stay at Midwest, provided it is within one year of the original request Midwest 1 Resort wa Resort Resort Town, wi 99999 888-123-4567 Itinerary Informatlon Reservation Noc MRO91234 Res Date Check In Check Out 1/8/2019 Nights: 2 Adults: 2 Room Type: Double Queen Rate Code Standard Card Type Card No: Visa 4333-1234-5678-9012 John Doe 123 Any Street Some Town, WI 53999 Expiration: 08/2020 414-999-9999 Address 12/4/2018 1/6/2019 Name on Card: lohn Q Doe Phone: Email: Rooms: 1 Children: 1 Room Information Double Queen Standard This room can hold up to 4 people. It is equipped with wireless internet access, cable TV, coffee maker, alarm clock, and a mini-bar Cost Estimate Room Total: $338.00 Fees & Taxes $32.11 Check in time is 3:00pm. Check out time is 11 a m. $370.11 $185.06 If you need to cancel, you must cancel by 3:00 p.m, local time at least 48 hours in advance, otherwise your credit card will be charged for 1 night Deposit Midwest Resort is a smoke-free and pet-free facility Figure 2. Reservation Confirmation Though the confirmation does not acknowledge it, a room is assigned to the guest at this time. This permits future reservations to be made without overbooking. In case a reservation is cancelled, then the s nassigned and is added to the inventory of available rooms. Even if a cancellation occurs after to the inventory of available rooms The check-in process is initiated upon arrival of the guest at the resort. Most check-ins are associated with a prior reservation, and occur after the 3 p.m, deadline. Preparation for check-in is initiated by the front desk at noon. This involves running a program that identifies all new guests that are due to check in for that night. An agreement is printed out for each stay. Bear in mind that a stay can involve multiple days and multiple rooms. Upon arrival at the resort, guests provide the front desk with their name to enable the front desk personnel to locate the appropriate agreement. The clerk will then collect a credit card from the not required. At this point, a hold is placed on their plus $100 per day for incidentals. Keys to the room are issued in the form of passcards. In case more than one key is needed, the front desk will also note the names of all individual who receive keys. An appropriate number of keys are programmed for room, specifying the room, the check-out date. Keys are programmed to work after 12 noon on the check-in date and until 12 noon on Most guests use the same credit card employed for the reservation, though this is credit card for the e guest. outstanding balance on their check-in date and the the check-out date. If the guest used their own transportation to get to the resort, then this information is also noted on the agreement, as illustrated in Figure 3. Upon signing the agreement, the guest is given a map of the resort, the location of facilities and restaurants, and other relevant information to make their stay at Midwest a memorable event. If guests plan to use the Internet, an access code specific to the guest is also provided. Room No: 227 Midwest 1Resort way Resort Double Queen Standard) 1/6/2019 453 pm 1/8/2019 Resort Town, WI 99999 888-123-4567 Check In Check Out: Number of Keys: Key details: K1578, K1579 Name Address: John Doe 123 Any Street Some Town, W 53999 John Doe, Jane Doe Vehide Info: Phone CardType Card No: Expiration: Name on Card John Q Doe ( make, model, color) MC 5666-9876-5432-1234 04/2020 Date Room Total: $338.00 Fees & Taxes: 32.11 Total: Deposit$370.11 Min Balance: $185.05 I understand that Midwest Resort is a smoke-free facility. Violation of this policy includingtempering with smoke detectors in the room, will result in a penalty of 5100.00. 1 agree to abide by the rules of the facility. Ialso agree not to walve my liablity in the event that any portion of the amount owed is not paid. If using a credit card, I also agree to a nominal hold for any incidental expenses during my stay $185.06 Check intime is 3.00p.m. Check out time is 11a.m Figure 3. Check-In Agreement In case a guest shows up before 3 p.m., the clerk can check if the assigned room(s) is available. If so, at his or her discretion, the check-in process can be completed, and the guest given the room(s) early Under no circumstances can the room be given out before 12 noon, since the keys would not work prior to The other exception relates to guests looking for rooms without a reservation-something that tends to occur during weekends and holidays. As long as a room is available for the entire duration of the proposed stay, the front desk clerk can go through a room assignment process, and check-in information as assi which captures reservation gns a room to the guest. The data collected is the same as in the standard case, though guests need not provide an email. Room Reassignment Once the guests inspect their room, they may decide that they wish to change the room, based on convenience, location, or a variety of other factors. In this case, they would approach the front desk with a request to change. The clerk then looks up other available rooms in the same room type, as well as other types, if appropriate. Only rooms that are available for the entire stay are listed. This information appears in Figure 4. Midwest Resort: Check Room Avalability Midwest Resort Room Availability 722- Logged nas. Check Out oun Stdd1/10/201911/2019 Room Type Check In b Find Rooma Ntemative Types 201. Kng Standard $159 Seled 202 King Standard $159 Selied 273 Rng Whirpool $189 Seledt Selected Type 229 16 Select 252 $1 Select 321 $19 Select 229 Select Figure 4. Room Availability Screen If no rooms are available that meet the guest's criteria, then the process terminates. Otherwise, the selected room is assigned to this stay. This involves tearing up the old agreement, generating a new agreement, having the guest sign that, and reprogramming all keys. Room reassignment is not permitted during the stay. If a guest absolutely needs to move from one room, then the stay is broken up into two separate stays, with a check-out performed on the first room, and a check-in on the second room. Charges Though the resort charges handsomely for rooms, a significant portion of its revenue comes from the use of its facilities. Most facilities their use to the room-since this defers the payment. Access to the waterpark and the included in the cost of the room. All other facilities have a charge associated have a charge associated with them, and guests are encouraged to charge swimming pool are with them. Guests are charged $10 per person per day to use the fitness center. In principle, there isa the fitness center, but Midwest has never encountered a situation Guests call down to the front desk, and their key is the center for that day. Use These are when this limit has been reached. made available in 1 hour blocks. When the golf course is open (summer and fall months), guests can sign up for a round of golf-9 holes, 18 holes, with or without a cart-each of which has a different charge. of the indoor courts and the rock climbing wall also involve a charge, as do the tennis courts. This is available in blocks - morning, afternoon, evening, or all day- each of which entails a different charge. In case of golf and skiing, if needed, guests can also rent out the necessary equipment, for a charge. Equipment for the indoor and outdoor courts is provided gratis. During the winter months assuming there is enough snow on the slopes, guests can sign up for skiing A related instructors availabl ed revenue stream associated with the sports facilities is instruction. Midwest has a number of le on its staff for one-on-one or group instruction. Tennis, skiing, and golf lessons proved to be popular on an individual basis. Group lessons are offered on a set schedule. They a designated instructor, location, time, and capacity, as illustrated in Figure 5. All instruction is have have per session, and is charged to the room All charges to a room require a signature and a swipe of the room key Class Schedule Winter 2019 Midwest Resort Tai Chi Tai Chi | Yoga Room1 35] | Room! p5] | Romi p5] | Room1 :00.8:45 | Room1 Room1 Aquacise Aquacise (20Pool [20 Aqucise Aquacise Aquacise Pool [201 | Pool | Pool[201 9:00.9.45 | Pool Lon Pilates Lon Aerobics 00-945 Rloom1 (20] Rloom1 (30 Room3 (25 Room1 (30] Room120] Room1 (30 Spinning Spinning Spinning Spinning Room2[10] Room2[10) Zumba 10:00-10:45 Room2 120) Room2 1201 Room2 (20] Room2120 Room2 (20) Room2 (20 9:00-9:45 Rloom2[10 Room2 (10 Cardio Zumba Cardio Zumba Cardio Lon Yoga Spinning Spinning [10)Yoga oom1 [35] Room2[10] Amanda Room1 (35) Room1 (35) Yoga 6:00-6.45 Figure 5. Group Instruction Schedule A more reliable source of revenue is food consumed on the premises. Even though there are some urants and fast food options available nearby, Midwest has found that a sizeable fraction of their nificant fraction of their meals at one of the restaurants or bars within the facility guests can pay for their meals in either of three ways-cash, charge to a credit charge to the room. Cash and credit card transactions are processed immediately. Charges to a e of the room key. In addition to the restaurants, Midwest also provides in-room dining options. All rooms include a complete menu of the items available prov service, specifying the items available, the times that they are served, and associated cost-see Figure Guest can simply call down to the catering office and specify the items that they want. Items are automatically charged to the room. In addition to the item cost, there is a flat $2.50 in-room dining 6. charge, and all items delivered to the room are subject to an 18% service and gratuity charg e. In addition, state and local taxes, currently running at 9.5% apply. These do not apply at a restaurant or a bar, though. consume items from here and note their consumption on the form provided - see Figure 7. This works on to the charges made Rooms at Midwest Resort are also equipped with a mini-bar Guests can an honor system, and Midwest has generally has a good experience with guests reporting their consumption accurately. The form is checked daily, the items restocked, and a new form left in the room. MidwestRoom Mini-Bar Service are plemedto offer in-roam dining, avalable24hoursa dy . Resort Midwest Resort BREAKFAST Guest Name AssortedCema ALL-DAY DINING 2.50 Deluse Buger 510 515 2.00 Please diail1234 toplace your order. Asenvice charge ard gratuty of 18M will be addedto your account. An in-roomcharge of$2.0 will be addedto the account. All applicable state and local Ea Figure 6. Room Service Menu Figure 7. Mini-Bar Form One last area that guests are charged for is calls made from the room. Local calls and calls to toll-free numbers do not incur any charge. Long-distance and international calls are automatically charged to the room. Check Out Upon completion of their stay, guests check out at the front desk. The clerk pulls up their account and acerues all charges made to the room during the stay. The guest is given an opportunity to look it over, and the outstanding balance is charged to the credit card on file. If needed a different credit card could be employed for this charge. Any prior hold on the credit card on file is removed. Disputed transactions are checked with the facility that originated the transaction. If an error is discovered, the charge is removed. Otherwise, the charge is considered to be incurred by the guest and retained in the bill, a copy of which appears in Figure 8. A check-out that occurs after the 11:00 a.m. deadline, is subject to a late check-out charge of $50. This is waived if the guest was present before the deadline and the front desk wasn't able to process them in time, or if check-out occurs before 12 noon. Midwest AA e Resort |Resort way Resort Town, WI 99999 888-123-4567 227 1/6/2019 4:53 pm Room No: John Doe 123 Any Street Some Town, WI 53999 Check Out: 1/8/2019 10:22 am Adult/Child: 2/1 Date Generated: 1/8/2019 Cashier: 1 of 1 charges Credits 185.06 12/04/16 01/06/17 01/06/17State/Local Tax Room Charge 169.00 16.06 01/07/17 01/07/17 01/07/17 01/07/17 01/07/17 01/07/17 01/07/17 01/07/17 01/07/17 Dinner- ski Lodge Room Charge State/Local Tax In-Room Dining In-Room Dining Service Charge In-Room Dining Charge All-Day Lift Tickets Skling lesson Gift Shop Dinner-19t+ Hole Fitness Center 169.00 40.00 18.57 MasterCard 2o00X000:00-1234 04/20 437.34 622.40 1/8/19 Guest Signature that my labiity for this bill s not waved and agee to be held pensanally liable inthe evertthat the indica ted person, company, or association fals to payfor amypart of the full amourt of these charges These charges are due and payable in fullprior to departure If paid using a reditcard, Ifurtherageeto perform the obligeorth n the cardholdr's grmentith th isr Figure 8. Bill/Receipt Guests turn in their keys at this time, which are deactivated, and can be reused later on. Any keys not turned in are treated as lost, and are promptly deactivated. Any Internet access codes are also deactivated. Additional charges may be incurred after check-out. Typically these are noted by the housekeeping staff, including but not limited to unreported mini-bar utilization, damage to items in the room, violation of the no-smoking policy, and the like. Another bill is printed up listing these charges, and mailed to the guest. The amount is charged to the credit card on file. At around 11:30 a.m. the front desk staff will generate a report indicating which rooms have not yet checked out but were due to check out. Attempts are made to call the guests. Check-outs that occur after 2 p.m. incur an additional day charge since the room cannot be turned around for the next guest by 3 p.m