Databased schema
All of the questions in this assignment use the following database schema, in which the keys of each relation are underlined:
Restaurant(rname: string, address: string, phone: string, stars: integer)
Chef(cname: string, specialdish: string, rating: integer)
CooksFor(cname: string, rname: string, salary: integer)
Offers(rname: string, dishname: string, price: real)

Question 1 [4×2 marks]
For each of the following pairs of queries, explain whether or not the queries are equivalent. You must explain your answer. Be careful – to be equivalent, the queries must provide the same answer for every possible set of valid data.

[2 marks]
[Symbol]cname(([Symbol]stars>3 Restaurant) ? CooksFor)))

SELECTC.cname
FROMRestaurant R, CooksFor C
WHERER.rname = C.rname AND R.stars > 3

[3 marks]
[Symbol]dishname(([Symbol]rname,dishname([Symbol]price>50 Offers)) n ([Symbol]rname,dishname([Symbol]price<75 Offers)))
[Symbol]dishname(([Symbol]rname,dishname([Symbol]price>50 ^ price<75 Offers))

[3 marks]
SELECTC.cname, AVG(C.rating)
FROMChef C, CooksFor F
WHEREF.cname = C.cname AND F.salary > 10000
GROUP BYC.cname

SELECTC.cname, AVG(C.rating)
FROMChef C
WHEREC.cname NOT IN (SELECTF.cname
FROMCooksFor F
WHERER.salary <= 10000)
GROUP BYC.cname

Page Break

Question 2 [14×3 marks] Consider the database schema defined in question 1 above.
Express each of queries (a)-(f) in both (i) Relational Algebra, and (ii) SQL:
Find the special dish(es) of chefs who cook for “Curry in a Hurry”.
Find the name(s) of the least expensive dish(es).
Find the addresses and phone numbers of restaurants employing all chefs who have a rating of 10.
Find the names of restaurants, and number of stars, for restaurants that do not offer poutine.
Find all pairs of restaurant names (rname1, rname2) such that the restaurant named rname1 offers the same dish as the restaurant named rname2, but at a higher price.
Find the names and ratings of chefs who cook for at least 2 different restaurants.
Express queries (g) and (h) in SQL:
For each chef who works for a 5-star restaurant, find their name and total salary.
(Note that a chef can work for more than one restaurant, so we want the total salary from all of their different jobs, including those not at a 5-star restaurant).
For each different level (as determined by number of stars), find the average price of dishes offered by restaurants with that number of stars.

Databased schema
All of the questions in this assignment use the following database schema, in which the keys of each relation are underlined:
Restaurant(rname: string, address: string, phone: string, stars: integer)
Chef(cname: string, specialdish: string, rating: integer)
CooksFor(cname: string, rname: string, salary: integer)
Offers(rname: string, dishname: string, price: real)

Question 1 [4×2 marks]
For each of the following pairs of queries, explain whether or not the queries are equivalent. You must explain your answer. Be careful – to be equivalent, the queries must provide the same answer for every possible set of valid data.

[2 marks]
[Symbol]cname(([Symbol]stars>3 Restaurant) ? CooksFor)))

SELECTC.cname
FROMRestaurant R, CooksFor C
WHERER.rname = C.rname AND R.stars > 3

[3 marks]
[Symbol]dishname(([Symbol]rname,dishname([Symbol]price>50 Offers)) n ([Symbol]rname,dishname([Symbol]price<75 Offers)))
[Symbol]dishname(([Symbol]rname,dishname([Symbol]price>50 ^ price<75 Offers))

[3 marks]
SELECTC.cname, AVG(C.rating)
FROMChef C, CooksFor F
WHEREF.cname = C.cname AND F.salary > 10000
GROUP BYC.cname

SELECTC.cname, AVG(C.rating)
FROMChef C
WHEREC.cname NOT IN (SELECTF.cname
FROMCooksFor F
WHERER.salary <= 10000)
GROUP BYC.cname

Page Break

Question 2 [14×3 marks] Consider the database schema defined in question 1 above.
Express each of queries (a)-(f) in both (i) Relational Algebra, and (ii) SQL:
Find the special dish(es) of chefs who cook for “Curry in a Hurry”.
Find the name(s) of the least expensive dish(es).
Find the addresses and phone numbers of restaurants employing all chefs who have a rating of 10.
Find the names of restaurants, and number of stars, for restaurants that do not offer poutine.
Find all pairs of restaurant names (rname1, rname2) such that the restaurant named rname1 offers the same dish as the restaurant named rname2, but at a higher price.
Find the names and ratings of chefs who cook for at least 2 different restaurants.
Express queries (g) and (h) in SQL:
For each chef who works for a 5-star restaurant, find their name and total salary.
(Note that a chef can work for more than one restaurant, so we want the total salary from all of their different jobs, including those not at a 5-star restaurant).
For each different level (as determined by number of stars), find the average price of dishes offered by restaurants with that number of stars.

Databased schema
All of the questions in this assignment use the following database schema, in which the keys of each relation are underlined:
Restaurant(rname: string, address: string, phone: string, stars: integer)
Chef(cname: string, specialdish: string, rating: integer)
CooksFor(cname: string, rname: string, salary: integer)
Offers(rname: string, dishname: string, price: real)

Question 1 [4×2 marks]
For each of the following pairs of queries, explain whether or not the queries are equivalent. You must explain your answer. Be careful – to be equivalent, the queries must provide the same answer for every possible set of valid data.

[2 marks]
[Symbol]cname(([Symbol]stars>3 Restaurant) ? CooksFor)))

SELECTC.cname
FROMRestaurant R, CooksFor C
WHERER.rname = C.rname AND R.stars > 3

[3 marks]
[Symbol]dishname(([Symbol]rname,dishname([Symbol]price>50 Offers)) n ([Symbol]rname,dishname([Symbol]price<75 Offers)))
[Symbol]dishname(([Symbol]rname,dishname([Symbol]price>50 ^ price<75 Offers))

[3 marks]
SELECTC.cname, AVG(C.rating)
FROMChef C, CooksFor F
WHEREF.cname = C.cname AND F.salary > 10000
GROUP BYC.cname

SELECTC.cname, AVG(C.rating)
FROMChef C
WHEREC.cname NOT IN (SELECTF.cname
FROMCooksFor F
WHERER.salary <= 10000)
GROUP BYC.cname

Page Break

Question 2 [14×3 marks] Consider the database schema defined in question 1 above.
Express each of queries (a)-(f) in both (i) Relational Algebra, and (ii) SQL:
Find the special dish(es) of chefs who cook for “Curry in a Hurry”.
Find the name(s) of the least expensive dish(es).
Find the addresses and phone numbers of restaurants employing all chefs who have a rating of 10.
Find the names of restaurants, and number of stars, for restaurants that do not offer poutine.
Find all pairs of restaurant names (rname1, rname2) such that the restaurant named rname1 offers the same dish as the restaurant named rname2, but at a higher price.
Find the names and ratings of chefs who cook for at least 2 different restaurants.
Express queries (g) and (h) in SQL:
For each chef who works for a 5-star restaurant, find their name and total salary.
(Note that a chef can work for more than one restaurant, so we want the total salary from all of their different jobs, including those not at a 5-star restaurant).
For each different level (as determined by number of stars), find the average price of dishes offered by restaurants with that number of stars.