I need help here anyone can give me heads up will be appreciated
Hotel Room Reservation System
Westin owns a chain of hotels internationally around the world. As a world renowned hotel, it is their priority to ensure the efficiency of their services. As a means to improve their services, they hire you to develop a database for their hotel room reservation. The database system should be able to keep track on reservation details, rooms and customer information.
Each hotel of Westin offers a standardised accommodation package. Each hotel has anywhere from twenty to one hundred rooms . These rooms could be either Standard type, Deluxe type, Superior type or Family type. Some rooms are facing good views while some are without any view. All rooms are cater for two or more adults, and are charged on a room per night basis, regardless of whether they are occupied by one or two guests. The charge per room per night is standard throughout the week . However, each room may have many different room facilities. A customer can make several reservations.
Nevertheless, a room can only be subject to one reservation, on a given day, at any given time. A reservation may involve one or more rooms. However, all rooms in a s ingle reservation must have the same arrival and departure dates. If different arrival dates apply then separate reservations must be made. Each single reservation is made in a single name regardless of how many guests it actually involves. When a reservation is made the room allocated should always be the room whose vacant period best matches the requested reservation period, given that the room is of the type requested. The cus tomer needs to pay during the reservation, whereby the total prices are calculated based on number of room reserved per night. As a token of appreciation, Westin offers a 10% discount to regular customer (customer whic h has stayed in any chain of hotels for more than 10 times in a year).
Example SQL queries and statements that your databas e should be able to handle
•List the available rooms group by their room types
•List the chain of Westin hotels which has the most number of reservations.
•Create report(s) to list the total number of reservations for each day/month/year for each chain of Westin hotels .
•For each hotel, list the top five countries from where the customer originates from.
•Create a trigger that will price a room at a disc ounted rate for rooms that had their reservations cancelled. You may set the discount at different rates depending on the days (e.g. 1 week before actual date = 20% discount, 1 day before 50% discount). This is to encourage people to take up empty rooms and ensure that no room in the hotel is left empty (full occupancy = more money!)
•Create a trigger that will automatically identify which c ustomer are the regular customers which will receive coupons for special offers.
