CS-A1153 - Databases (summer course), 04.06.2020-03.09.2020
This course space end date is set to 03.09.2020 Search Courses: CS-A1153
Question 2
Consider the following database schema, which contains information about customers, hotels, their rooms and
reservations of a hotel chain. For the sake of simplicity, we assume that each reservation contains only one room
and the room is always assigned immediately when the reservation is made. We also assume that the price of
a certain room does not vary, i.e. the same room has always the same price.
The relation Hotels contains information about the hotels belonging to the chain. The hotels are identified
by the attribute ID. Relation Customers contains information about the customers of the hotel chain. The
customers are identified by unique customer number (attribute CID). The relation may also contain customers
who have made no reservation yet, for example customers who have taken part in some advertising campaign.
The relation Rooms contains information about the rooms in the hotels of the chain. Attribute number is the
number of the room, hotelID is the ID of the hotel, beds is the number of beds in the room and price is the
price of the room for one night.
The relation Reservations contains information about the reservations. The reservations are identified by
unique RID. The attributes of the relation also tell the customer number of the customer who has made the
reservation, the room number and the ID of the hotel, the date of check-in and the number of the consecutive
nights included in the reservation. Each reservation contains always only one room.
The number of the room, the number of the beds and the number of the nights included in the reservation are
integers and the price is a decimal number. The values of all other attributes are strings. You may assume that
the attributes of the tuples do not have NULL values.
Database schema:
Hotels(ID, name, city, address, country)
Customers(CID, name, email, phone)
Rooms(number, hotelID, beds, price)
Reservations(RID, customerID, hotelID, roomno, date, numberofnights)
Write the following SQL queries:
a) (2 p) The IDs and names of the hotels which have at least one room with price under 60 euros.
b) (2 p) The customer numbers and names of those customers who have made at least one reservation which
has the total price (the number of nights times the price of the room) of over 1000 euros.
c) (2 p) The IDs and names of those hotels which have at least one room with 3 beds, but no rooms with 4
or more beds.
d) (2 p) We want to find the hotels in Finland which can accommodate at least 100 guests (consider the total
number of beds in the rooms of the hotel). For each such hotel, the query must produce the hotel ID, the
name and the average of the prices of all rooms in the hotel. Note that the average price and the total
number of the beds are calculated separately for each hotel.
e-f: Explain for which query expressions of Relational Algebra in the attached file ra-problems-030920.pdf produce the answer (for
example: "The expression produces names and IDs of hotels which are located in Finland”.)
e) (2 p) See PDF file ra-problems-030920.pdf below, or original exam PDF.
f) (2 p) See PDF file ra-problems-030920.pdf below, or original exam PDF.
- 1 September 2020, 3:56 PM