Question: Need help with question C,D and E. Consider the following database sch

Question

Question: Need help with question C,D and E.

Consider the following database schema, in which the keys of each relation are underlined:

Restaurant(rname, address, phone, stars)

Chef(cname, specialdish, rating)

CooksFor(cname, rname, salary)

Offers(rname, dishname, price)

For each of the following general constraints, create CHECK constraints, assertions or triggers (as appropriate) to ensure that the constraint holds. Note: in the case of CHECK constraints, you do not need to provide the entire set of statements required to create the appropriate table, just the CHECK constraint itself

c) [3 marks] Write an assertion to ensure that every restaurant must have at least one chef.

d) [3 marks] Write an assertion to ensure that for every dish, there is at least one chef for whom that is their special dish.

e) [5 marks] When the number of stars assigned to a restaurant increases, then all chefs who cook for that restaurant must receive a 10% raise. Write a trigger that will modify salary accordingly

Consider the following database schema, in which the keys of each relation are underlined- Restaurant(rname, address, phone, stars) Chef cname, specialdish, rating) CooksFor(cname.rname, salary) Offers(rname dishname, price) For each of the following general constraints, create CHECK constraints, assertions or triggers (as appropriate) to ensure that the constraint holds. Note: in the case of CHECK constraints, you do not nend to nroxide the emtire set of statementsetle,just the CHECK constraint itself. ) 2 marks] Write a CHECK constraint to ensure that every restaurant has between 0 and 5 stars (inclusive). b) [2 marks] Write a CHECK constraint to ensure that no restaurant offers a dish that costs less c) [3 marks] Write an assertion to ensure that every restaurant must have at least one chef. d) [3 marks] Write an assertion to ensure that for every dish, there is at least one chef for whom that is their special dish. [5 marks] When the number of stars assigned to a restaurant increases, then all chefs who cook for that restaurant must receive a l 0% raise. Write a trigger that will modify salary accordingly e)

Explanation / Answer

c) For this we check if we have the exact same number of restaurant names in table CooksFor. If we have the same number of restaurants, the condition will evaluate to true and that will means every restaurant has at least one cook.

If the condition curtails to being false,i.e, if there are less restaurant names in CooksFor than in Restaurant,which means that a restaurant doesn't have a cook, then the assertion also becomes false.

This will ensure that we have at least one cook for that restaurant.

Thus the conditions for assertion would be:

CREATE ASSERTION chef_check

CHECK (1<=ALL

SELECT COUNT(*)

FROM Restaurant R, CooksFor C

WHERE R.rname= C.rname

GROUP BY rname);

d.) This question will ask for the same strategic deployment as the previous question.

CREATE ASSERTION dish_check

CHECK (1<=ALL

SELECT COUNT(*)

FROM Offers O, Chef C

WHERE O.dishname= C.specialdish

GROUP BY dishname);

e.) In this problem we will employ the use of trigger and increase the salary of the cooks as follows:

CREATE TRIGGER chefsal

AFTER UPDATE ON Restaurant /* when the star rating of a restaurant is updated, then do the commands*/

FOR EACH ROW /* granularity should be for each row as we check each row for cooks of the desired restaurant*/

BEGIN

If(:new.stars> (:old.stars)) THEN

CooksFor.salary= CooksFor.salary+(CooksFor.salary*0.1)

from CooksFor C, Restaurant R

Where R.rname= C.rname;

END IF;

END;

/

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.