Q5 SQL 30 Points This question is based on the following set of tables modeling the operations of a restaurant Menu (item-ld, Price, Description) Ingredient (Item-ld, Food-Id, Quantity) Food (Food-Id,Description Category, Amount-remaining) Order (Order-Id, Item-Id, Number-ordered) The ingredient table shows which foods are in the menu items. For example, the menu item for "French fries" might correspond to the food-ids of potato, oil and salt. The category of a Food could be vegetable, meat, dairy, etc. Quantity and Amount- remaining are measured in grams. Every order in the restaurant has a unique Order-ld which is associated with all of the ordered items, in the Order table. Q5.4 6 Points Every time somebody places an order, the restaurant has to know that it has enough of the ingredients on-hand to make the Item. Explain how you would use triggers to both (a) check there is enough food on hand, rejecting an order if there isn't enough, and (b) keep the Amount-remaining column of the Food table up to date. You don't need to write the trigger(s) out in full, just to describe the essential features of what the trigger does, when it is invoked, and how it achieves the stated goals. Q5.5 6 Points Suppose that a new restaurant staff member Joe is hired to do marketing. Joe wants to create a Special-deals table that refers to the Items in the menu. In order to implement this table, Joe requests that the database administrator (Alice) GRANT him a REFERENCES privilege on the Menu table. (a) Describe what Joe would be able to do with the privilege that he could not do before. (b) Give one appropriate reason why Alice might decide to refuse Joe's request. Q5 SQL 30 Points This question is based on the following set of tables modeling the operations of a restaurant Menu (item-ld, Price, Description) Ingredient (Item-ld, Food-Id, Quantity) Food (Food-Id,Description Category, Amount-remaining) Order (Order-Id, Item-Id, Number-ordered) The ingredient table shows which foods are in the menu items. For example, the menu item for "French fries" might correspond to the food-ids of potato, oil and salt. The category of a Food could be vegetable, meat, dairy, etc. Quantity and Amount- remaining are measured in grams. Every order in the restaurant has a unique Order-ld which is associated with all of the ordered items, in the Order table. Q5.4 6 Points Every time somebody places an order, the restaurant has to know that it has enough of the ingredients on-hand to make the Item. Explain how you would use triggers to both (a) check there is enough food on hand, rejecting an order if there isn't enough, and (b) keep the Amount-remaining column of the Food table up to date. You don't need to write the trigger(s) out in full, just to describe the essential features of what the trigger does, when it is invoked, and how it achieves the stated goals. Q5.5 6 Points Suppose that a new restaurant staff member Joe is hired to do marketing. Joe wants to create a Special-deals table that refers to the Items in the menu. In order to implement this table, Joe requests that the database administrator (Alice) GRANT him a REFERENCES privilege on the Menu table. (a) Describe what Joe would be able to do with the privilege that he could not do before. (b) Give one appropriate reason why Alice might decide to refuse Joe's request
