1. [15 points] Consider the HOTEL database used in the SQL in-class discussions

Question

1. [15 points] Consider the HOTEL database used in the SQL in-class discussions

Hotel        (hotelNo, hotelName, city)

Room        (roomNo, H_hotelNo, roomType, price)

Booking     (H_hotelNo, G_guestNo, dateFrom, dateTo, R_roomNo)

Guest        (guestNo, guestName, guestAddress)

a. Give a relational algebra expression for the following English statement:

"List the names of all the guests currently staying at the Days Inn."

Hint: Look at the queries from SQL Hotel Queries practice

b. Give a relational algebra expression and a SQL query for the following English statement:  

List all the room numbers along with the hotel name they belong to for each hotel in Las Vegas

c. Give a relational algebra expression and a SQL query for the following English statement:  

List the room number and corresponding room type for rooms available in June 13th or August 23rd of this year at the Hilton in Seattle.

d. Come up with an example that uses the intersection and union operations in the same expression/statement. Provide:  

The natural language (English) statement

The relational algebra expression

The corresponding SQL query

Explanation / Answer

a)

PROJECT guestName (SELECT hotelName="DaysInnn" (Hotel) JOIN hotelNo=H_hotelNo(Hotel × Booking) JOIN G_guestNo=guestNo (Guest × Booking))

b) SQL query

SELECT roomNo,hotelName FROM Room,Hotel WHERE city="Las Vegas" AND H_hotelNo=hotelNo.

Relational algebra expression

PROJECT roomNo,hotelName (SELECT city ="Las vegas" (Hotel) JOIN H_hotelNo=hotelNo(Room × Hotel ))

*************End******************PLS GIVE ME GOOD RATING***************************************

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.