Databased schemaAll 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.cnameFROMRestaurant R, CooksFor CWHERER.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 FWHEREF.cname = C.cname AND F.salary > 10000GROUP BYC.cnameSELECTC.cname, AVG(C.rating)FROMChef CWHEREC.cname NOT IN (SELECTF.cnameFROMCooksFor FWHERER.salary <= 10000)GROUP BYC.cnamePage BreakQuestion 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.
Order Management
Premium Service
- 100% Custom papers
- Any delivery date
- 100% Confidentiality
- 24/7 Customer support
- The finest writers & editors
- No hidden charges
- No resale promise
Format and Features
- Approx. 275 words / page
- All paper formats (APA, MLA, Harvard, Chicago/Turabian)
- Font: 12 point Arial/Times New Roman
- Double and single spacing
- FREE bibliography page
- FREE title page
0% Plagiarism
We take all due measures in order to avoid plagiarisms in papers. We have strict fines policy towards those writers who use plagiarisms and members of QAD make sure that papers are original.