The Modern Hotel Management Database System is designed to streamline the operations and enhance the efficiency of a luxury hotel named "Skyview Hotel." This case study explores the various entities within the database system, highlighting their relationships and functionalities. Skyview Hotel, a 5-star establishment, offers various services to its guests, including room bookings, dining, spa, and event management. This database system ensures that Skyview Hotel can manage its resources effectively, provide top-notch services, and maintain a competitive edge in the hospitality industry. The head office of Skyview is in London. The company was using basic database system to maintain their data, but due to technological advancements and lack of capturing the market, they have to expand their setup. In addition to London, they recently opened two more branches in New York and Tokyo. Assume they hire you as a technical consultant for replacing the old-fashioned file system with new database system. The Modern Hotel Management Database System automates and optimizes various hotel operations, including guest check-ins and check-outs, room reservations, dining reservations, spa treatments, event bookings, and inventory management. It facilitates data-driven decision-making, improves guest satisfaction, and enhances the overall efficiency of Skyview Hotel. In each Hotel, the company has a receptionist, whose duties include greeting the customers and booking their rooms and services. The customers can book their requirements either online/phone or in personYou are required to design ERD and Use Case) and implement the system using SQL. The hotel company have couple of Agents, who assists customers in their queries and transactions. The system will have the Agent details where each Agent has different levels of dealer namely bronze, silver and gold. There are total 6 Agents, where 2 deals with London and 4 with rest of the cities. The Agent's level is determined based on the number of bookings, where if an Agent have (equal or) more than 15 bookings, it is categorized as Gold, more than 10 but less than 15 years as Silver, and Bronze otherwise. These Agents give recommendations to customers to book hotel rooms with additional services where customers have right to accept/reject them. The Skyview company has got security protocols, such as antivirus and firewall, which are handled by an IT practitioner. The company also hire a security guard in each location to safeguard the premises. There is a technical consultant, who deals with prediction modelling and they will develop a prediction system in future but for now the system will be able to provide some suggestions based on the stored data Queries: 1. Retrieve the names of all guests who have made dining reservations at Skyview Hotel in London, along with the reservation dates and total costs (1 mark) 2. List all staff members at Skyview Hotel, including their names, positions, and salaries, sorted by their job positions (1 mark) 3. Retrieve the total cost and reservation details for all spa reservations made by a specific guest (identified by their Guest/customer id). (1 mark) 4Find the most frequently booked room type at Skyview Hotel and the total number of reservations for that room type (2 mark) 5. Display the inventory items used in dining reservations (including item names, categories, quantities, and unit prices) for a specific dining reservation (identified by ReservationID) (2 mark) 6. Calculate the total revenue generated from room reservations for a specific date range, along with the number of reservations made during that period along with the agent details (2 mark) 7. Develop a recommendation system that suggests room upgrades to guests based on their past reservation history and preferences, with the goal of increasing revenue and guest satisfaction (3 marks) 8. Show the maximum price sold from all Gold and Bronze Agents, for all hotel bookings (3 marks) Trigger (5 marks) Create a trigger which places the customers which have been deleted into a customer/client table. This allows records to be maintained while improving query times for existing customers. Integrity Constraints 1. Only Numeric data in price 2. Appointment cannot be booked in past 3. No numeric numbers in Names 4Email should have @ symbol for Agents and customers 5. When adding/updating any price; it should not allow us to update the record if the value is not in (min, max) of respective constraint for that property 6. If the customer email is not given, it should be '..w@gmail.com Guest is related to Reservation, Dining Reservation, Spa Reservation, and Event Booking through GuestID. Reservation is related to Room through Room Type. Dining Reservation and Spa Reservation are related to Guest through GuestID. Event Booking is related to Guest through GuestID. Inventory is related to Dining Reservation and Spa Reservation through Item Name