Need help with the following questions. Consider the following database schema,

Question

Need help with the following questions.

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

A) Ans:-

CREATE TABLE restaurant
(
rname nchar(10) primary key NOT NULL,
address nchar(10) NOT NULL,
phone int,
star int CHECK (star>=0 and star<=5)
);

B) Ans:-

CREATE TABLE offfers
(
rname nchar(10) primary key NOT NULL,
dishname nchar(10) NOT NULL,
price int check(price >=5)

);

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

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