Design a database for a hotel reservation business (e. g. hotels.com) using the below data requirements:...

 
 

Transcribed Image Text:

Design a database for a hotel reservation business (e. g. hotels.com) using the below data requirements: The hotels are identified by a unique hotel number and each hotel has a name and city where it is located. The hotels have rooms with room number and information related to rooms such as the type of room (single, double, etc.) and price need to be stored. Information of guests (customers) such as the guest name and address has to be maintained. Each guest is uniquely identified by a guest number. Hotel room bookings by guests will include information of the arrival and departure dates. (Guests may book the same room as part of different bookings and hence the unique identifier of a booking should include the arrival date instead of the room number.) 1) Design the conceptual schema (using ER Diagrams). 2) Design the relational database schema based on the conceptual schema. 3) Design an Oracle/MySQL database using SQL DDL based on the relational schema. (10) (15) (25) 4) Populate the database using SQL DML with at least five records in each table and with data appropriate for the below queries. 5) Write SQL queries for the following: (20) (30) a) List full details of all hotels in London. b) List the names and addresses of all guests in London, alphabetically ordered by B name. c) List all double or family rooms with a price below $ 40.00 per night, in ascending order of price. d) List the bookings for which no departure date has been specified. e) How many hotels are there? f) What is the average price of a room? g) What is the total revenue per night from all double rooms? h) How many different guests have made bookings for August? i) List all guests currently staying at the Grosvenor Hotel. j) List the number of rooms in each hotel. Design a database for a hotel reservation business (e. g. hotels.com) using the below data requirements: The hotels are identified by a unique hotel number and each hotel has a name and city where it is located. The hotels have rooms with room number and information related to rooms such as the type of room (single, double, etc.) and price need to be stored. Information of guests (customers) such as the guest name and address has to be maintained. Each guest is uniquely identified by a guest number. Hotel room bookings by guests will include information of the arrival and departure dates. (Guests may book the same room as part of different bookings and hence the unique identifier of a booking should include the arrival date instead of the room number.) 1) Design the conceptual schema (using ER Diagrams). 2) Design the relational database schema based on the conceptual schema. 3) Design an Oracle/MySQL database using SQL DDL based on the relational schema. (10) (15) (25) 4) Populate the database using SQL DML with at least five records in each table and with data appropriate for the below queries. 5) Write SQL queries for the following: (20) (30) a) List full details of all hotels in London. b) List the names and addresses of all guests in London, alphabetically ordered by B name. c) List all double or family rooms with a price below $ 40.00 per night, in ascending order of price. d) List the bookings for which no departure date has been specified. e) How many hotels are there? f) What is the average price of a room? g) What is the total revenue per night from all double rooms? h) How many different guests have made bookings for August? i) List all guests currently staying at the Grosvenor Hotel. j) List the number of rooms in each hotel.

Answer rating: 100% (QA)

The question at hand involves several steps to design implement and query a database for a hotel reservation system This is a complex task that usually requires extensive planning and handson database ... View the full answer