Consider the following tables with the primary key of each table

underlined, and a few sample tuples shown. The columns cid and rid in bookings are

foreign keys referencing customers and resorts respectively.

$(($R $$ S$)$ R$)$ S $=$ R $$ S

cid name city country age

c$10$ Davood Edmonton Canada $20$

c$20$ John Victoria Canada $65$

cid rid arrival departure

c$10$ r$3002021/12/172021/12/27$

c$20$ r$2002021/12/242022/01/03$

resorts

rid resort city country suites

r$200$ Andaz Guanacaste Costa Rica $80$

r$300$ Dreams Sands Cancun Mexico $60$

r

Express the following queries in SQL$.$ In each case, write a single SQL statement.

a$)[4$ marks$]$ Find the name and the city of customers who have booked a resort in

Mexico.

b$)[4$ marks$]$ Find the rid of resorts with at least $80\%$ of their bookings made by

customers from Canada.

c$)[4$ marks$]$ Find the names of all pairs of different customers from Edmonton such

that both customers stay in the same resort and the days of their stays overlap.

rid resort city country suites

r$200$ Andaz Guanacaste Costa Rica $80$

r$300$ Dreams Sands Cancun Mexico $60$

resorts

CMPUT $291/$ A$1$ Final Exam $-$ Fall Page $4$ of $9$

d$)[4$ marks$]$ For each country and city that has a resort, list the country and the city,

the number of resorts and the number of resorts with a customer booking from

Canada.

Question $3$

$[4$ marks $]$ Based on the schema in Question $2,$ write a relational algebra or a relational

calculus query $($but not both$)$ to find the rid of resorts that have no booking for customers

of age $30$ or less.

Question $4$

$[4$ marks $]$ Based on the schema in Question $2,$ write a SQL assertion to enforce that

no resort can have more bookings $($ignoring the dates$)$ than the number of suites in the

resort.