PLEASE COMPLETE QUESTION IN DETAIL and EXPLAIN UR ANSWERS Question 4 : Consider

Question

PLEASE COMPLETE QUESTION IN DETAIL and EXPLAIN UR ANSWERS

Question 4 :

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

: Restaurant(rname, address, phone, stars)

Chef(cname, specialdish, rating)

CooksFor(cname, rname, salary)

Offers(rname, dishname, price)


a) [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 than 5.00.

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.

Explanation / Answer

In the above script a contraint have been added to the column Stars in table Restaurants, so that whenever a new value inserted or old value updated this contraint will check whether the value stay in the range that mentioned in contraint. If not then the record will not allowed to insert or updated

Question 2

Write a CHECK constraint to ensure that no restaurant offers a dish that costs less than 5.00.

SQL CODE

ALTER TABLE dbo.Offers ADD CONSTRAINT CK_Offers_DishPrice
CHECK (Price > 5.00)

In the above srcipt the column Price in table Offers is checked by adding contraint that the value of Price should not be less than 5.00, if any value less than 5.00 inserted/updated then it will not allowed

Question 3

Write an assertion to ensure that every restaurant must have at least one chef.

SQL CODE

create assertion Restaurant_Chef_Check as CHECK

(exists (select r.rname

from Restaurant r

inner join CooksFor cf on cf.rname = r.rname

inner join chef c on c.cname = cf.cname

group by r.rname

having COUNT(c.cname) > 0))

In the above sql script, assertion have been created that checks whether the restaurants have atleast one chef, this assertion makes the tables not to remove completely all the chef from restaurant. If done then it will be rolled back

Question 4

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

SQL CODE

create assertion DishOffer_Chef_Check as CHECK

(exists (select o.dishname

from offers o

inner join CooksFor cf on cf.rname = o.rname

inner join chef c on c.cname = cf.cname

group by o.dishname

having COUNT(c.cname) > 0))

In the above sql script, assertion have been created that checks whether the dish have atleast one chef for whom this is special dish, this assertion makes the tables not to remove completely all the dishes from offers table. If done then it will be rolled back

Question 5

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.

SQL CODE

CREATE TRIGGER [dbo].[Update_CHEF_Salary] ON Restaurant

After Insert, Update

AS

BEGIN

If (Exists(Select 1 From Restaurant As R

inner join inserted as i on i.rname = r.rname and i.Stars <> R.Stars

inner join deleted as d on d.rname = r.rname

Where i.rname = R.rname and d.stars < i.stars))

Begin

Update cf

set cf.salary = cf.salary + ((cf.salary / 100) * 10)

from CooksFor as cf

inner join chef as c on c.cname = cf.cname

inner join restaurant as r on r.rname = cf.rname

inner join inserted as i on i.rname = r.rname

End

END

In the above code the trigger is created in table Restaurant so that whenever a record inserted or updated the it checks whether stars column has any change and if any increase in the Stars value then the salary of the associated chefs get increased in the update statement inside the If Exist condition.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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