Overview
In this section, we will introduce an example relational data model for a hotel reservation system, and then explain how we may query this model to retrieve specific results.
Example Data Model: Hotel Reservation System
A Hotel Reservation System can be modeled witht the following sets of relational tables.
-
HOTELS
: This table would store information about each hotel, such as its name, address, and phone number. It might have the following columns:hotel_id
: A unique identifier for each hotel.name
: The name of the hotel.address
: The street address of the hotel.phone
: The phone number of the hotel.
-
ROOMS
: This table would store information about the rooms in each hotel, such as the room number and type (e.g. single, double, suite). It might have the following columns:room_id
: A unique identifier for each room.hotel_id
: The identifier for the hotel that the room belongs to. This column would be used to link the ROOMS table to the HOTELS table.room_number
: The number assigned to the room.room_type
: The type of room (e.g. single, double, suite).
-
GUESTS
: This table would store information about each guest, such as their name and contact information. It might have the following columns:guest_id
: A unique identifier for each guest.first_name
: The guest's first name.last_name
: The guest's last name.email
: The guest's email address.phone
: The guest's phone number.
-
RESERVATIONS`: This table would store information about each reservation, including the dates of the reservation, the room booked, and the guest making the reservation. It might have the following columns:
reservation_id
: A unique identifier for each reservation.guest_id
: The identifier for the guest who made the reservation. This column would be used to link the RESERVATIONS table to the GUESTS table.room_id
: The identifier for the room that was reserved. This column would be used to link the RESERVATIONS table to the ROOMS table.check_in
: The date the guest is scheduled to check in.check_out
: The date the guest is scheduled to check out.
This is just one example of how you might design a relational data model for a hotel reservation system. There are many other ways you could structure the data, depending on the specific needs and requirements of your system.
Example SQL Queries
Here are some example SELECT queries using the data model I described above:
To retrieve the names and addresses of all hotels:
SELECT name, address
FROM HOTELS;
To retrieve the names and reservation dates of all guests who have made a reservation at a specific hotel:
SELECT G.first_name, G.last_name, R.check_in, R.check_out
FROM GUESTS G
INNER JOIN RESERVATIONS R ON G.guest_id = R.guest_id
INNER JOIN ROOMS RO ON R.room_id = RO.room_id
INNER JOIN HOTELS H ON RO.hotel_id = H.hotel_id
WHERE H.name = 'Hotel Name';
To retrieve the names and room numbers of all guests currently checked into a specific hotel:
SELECT G.first_name, G.last_name, RO.room_number
FROM GUESTS G
INNER JOIN RESERVATIONS R ON G.guest_id = R.guest_id
INNER JOIN ROOMS RO ON R.room_id = RO.room_id
INNER JOIN HOTELS H ON RO.hotel_id = H.hotel_id
WHERE H.name = 'Hotel Name'
AND R.check_in <= CURRENT_DATE
AND R.check_out > CURRENT_DATE;
To retrieve the names and room types of all guests who have stayed at a specific hotel:
SELECT G.first_name, G.last_name, RO.room_type
FROM GUESTS G
INNER JOIN RESERVATIONS R ON G.guest_id = R.guest_id
INNER JOIN ROOMS RO ON R.room_id = RO.room_id
INNER JOIN HOTELS H ON RO.hotel_id = H.hotel_id
WHERE H.name = 'Hotel Name';