Its NOT an essay, just answer the question as it shows below:
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)) ∩ ([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.
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!