hotels

users

flights

Attributes with black background are the primary key attributes of a relation, e$.$g$.,$ usercode is the primary key

of the relation users.

The attribute usercode of relation hotels and flights is a foreign key to the attribute usercode of relation users.

Q$1$: Write the SQL query that will generate each of the tables and the relations between the

tables.

Q$1\mathrm{.}1$: Write an SQL query that returns the average hotel price per user. The hotel price is the sum

of priceper

Q$1\mathrm{.}2$: Write a relational $($bag semantics$)$ algebra for Question $1\mathrm{.}1.$

Question $1\mathrm{.}3.$ Write an SQL that returns double bookings, i$.$e$.,$ pairs of travelcode and usercode for the

same hotel where the dates overlap.

Question $1\mathrm{.}4(10$ Points$).$ Write a relational $($bag semantics$)$ algebra for Question $1\mathrm{.}3.$

Question $1\mathrm{.}5(5$ Points$).$ Classify travels into the following categories based on the total costs of flight and hotel $($i$.$e$.,$

total price for flight $+$ total price for hotel$)$: $($i$)$ If the total costs for flight and hotel are above $3000,$ then $$Luxury$$ trip;

$($ii$)$ If the total costs for flight and hotel are below $1000,$ then $$Cheap$$ trip; $($iii$)$ Otherwise, the trip is $$Regular$.$ The

query result should include the travelcode for the hotel and flight and tripclass that determines the class of the trip.

Question $1\mathrm{.}6(5$ Points$).$ Write a relational $($bag semantics$)$ algebra that computes the total number of flights and the

total price of flights for each company and flighttype.