First create 4 records in the MC_90_DAYS_MEAL_TYPE table completing the INSERT statements below. You MUST assign...

Transcribed Image Text:

First create 4 records in the MC_90_DAYS_MEAL_TYPE table completing the INSERT statements below. You MUST assign a specific PK value to each record. However, all your queries MUST NOT assume those IDs in any circumstances. I will test your queries in my own data, and you will NOT know which MEAL_TYPE_ID I used to create each record, i.e., all filters and condition in queries MUST use the MEAL_TYPE_NAME column. INSERT INTO MC_90_DAYS_MEAL TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) VALUES (_________, 'Breakfast'); INSERT INTO MC_90_DAYS_MEAL_TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) VALUES ( 'Lunch'); INSERT INTO MC 90 DAYS MEAL TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) 'Afternoon snack'); VALUES INSERT INTO MC_90_DAYS_MEAL_TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) VALUES ( _,'Dinner'); COMMIT; 4. MENU RECOMMENDATION (Part 2: A random full-day menu) 4.1. Create a procedure MC_CREATE_RANDOM_MENU that produces a random full-day menu, according to the following specification: Parameters: OP_DAY_NUMBER Specifications: o Remove any existing data from tables MC_90_DAYS_MENU and MC_90_DAYS_MENU_ITEM corresponding to the same "Day Number" a. Then, create a random full-day menu recommendation for that specific day, adding menu items into MC_90_DAYS_MENU_ITEM table, according to the following rules for each meal type: Breakfast: o one random item from "Breakfast" category + o one random item from "Coffee & Tea" category Lunch: O one random item from "Salads" category + o one random item from "Beef & Pork" OR "Chicken & Fish" categories (randomly choose one from the two categories above) + O one random item from "Beverages" category + o one random item from "Desserts" category (desserts must be included only in random 50% of the daily menus) Afternoon snack (randomly choose between A or B combinations below): (A) O one random item from "Smoothies & Shakes" category OR (B) o o Dinner: o o o Procedure Code (20 points) Procedure Code Trigger Code one random item from "Coffee & Tea" category + one random item from "Snacks & Sides" category one random item from "Beef & Pork" or "Chicken & Fish" categories (randomly choose one from the two categories above) + one random item from "Beverages" category + one random item from "Desserts" or "Coffee & Tea" categories (randomly choose one from the two categories above) MC_CREATE_RANDOM_MENU CREATE OR REPLACE PROCEDURE MC_CREATE_RANDOM_MENU * Test your procedure and make sure it works before moving to the next task 4.2. Create a trigger MC_90_DAYS_MENU_ITEM_TRG that cumulatively updates all the SUM_* columns in the MC_90_DAYS_MENU table, after each record is created in the MC_90_DAYS_MENU_ITEM table. How-to: a. To calculate the final "SUM_SODIUM_DAILY_PERC" for Recommendation DAY_NUMBER = 1, you must SUM the "SODIUM_PERC_DAILY_VALUE from each menu item included in that daily menu. However, think about how to design that implementation using a FOR EACH ROW trigger (AFTER INSERT) *You DO NOT need to worry about DELETE and UPDATE transaction in this trigger b. All SUM_*_DAILY_PERC column in "MC_90_DAYS MENU" table have a corresponding column in "MC_ MENU_ITEM" table named as *_ PERC_DAILY_VALUE. Trigger Code (20 points) MC_90_DAYS_MENU_ITEM_TRG CREATE OR REPLACE TRIGGER MC_90_DAYS_MENU_ITEM_TRG First create 4 records in the MC_90_DAYS_MEAL_TYPE table completing the INSERT statements below. You MUST assign a specific PK value to each record. However, all your queries MUST NOT assume those IDs in any circumstances. I will test your queries in my own data, and you will NOT know which MEAL_TYPE_ID I used to create each record, i.e., all filters and condition in queries MUST use the MEAL_TYPE_NAME column. INSERT INTO MC_90_DAYS_MEAL TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) VALUES (_________, 'Breakfast'); INSERT INTO MC_90_DAYS_MEAL_TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) VALUES ( 'Lunch'); INSERT INTO MC 90 DAYS MEAL TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) 'Afternoon snack'); VALUES INSERT INTO MC_90_DAYS_MEAL_TYPE (MEAL_TYPE_ID, MEAL_TYPE_NAME) VALUES ( _,'Dinner'); COMMIT; 4. MENU RECOMMENDATION (Part 2: A random full-day menu) 4.1. Create a procedure MC_CREATE_RANDOM_MENU that produces a random full-day menu, according to the following specification: Parameters: OP_DAY_NUMBER Specifications: o Remove any existing data from tables MC_90_DAYS_MENU and MC_90_DAYS_MENU_ITEM corresponding to the same "Day Number" a. Then, create a random full-day menu recommendation for that specific day, adding menu items into MC_90_DAYS_MENU_ITEM table, according to the following rules for each meal type: Breakfast: o one random item from "Breakfast" category + o one random item from "Coffee & Tea" category Lunch: O one random item from "Salads" category + o one random item from "Beef & Pork" OR "Chicken & Fish" categories (randomly choose one from the two categories above) + O one random item from "Beverages" category + o one random item from "Desserts" category (desserts must be included only in random 50% of the daily menus) Afternoon snack (randomly choose between A or B combinations below): (A) O one random item from "Smoothies & Shakes" category OR (B) o o Dinner: o o o Procedure Code (20 points) Procedure Code Trigger Code one random item from "Coffee & Tea" category + one random item from "Snacks & Sides" category one random item from "Beef & Pork" or "Chicken & Fish" categories (randomly choose one from the two categories above) + one random item from "Beverages" category + one random item from "Desserts" or "Coffee & Tea" categories (randomly choose one from the two categories above) MC_CREATE_RANDOM_MENU CREATE OR REPLACE PROCEDURE MC_CREATE_RANDOM_MENU * Test your procedure and make sure it works before moving to the next task 4.2. Create a trigger MC_90_DAYS_MENU_ITEM_TRG that cumulatively updates all the SUM_* columns in the MC_90_DAYS_MENU table, after each record is created in the MC_90_DAYS_MENU_ITEM table. How-to: a. To calculate the final "SUM_SODIUM_DAILY_PERC" for Recommendation DAY_NUMBER = 1, you must SUM the "SODIUM_PERC_DAILY_VALUE from each menu item included in that daily menu. However, think about how to design that implementation using a FOR EACH ROW trigger (AFTER INSERT) *You DO NOT need to worry about DELETE and UPDATE transaction in this trigger b. All SUM_*_DAILY_PERC column in "MC_90_DAYS MENU" table have a corresponding column in "MC_ MENU_ITEM" table named as *_ PERC_DAILY_VALUE. Trigger Code (20 points) MC_90_DAYS_MENU_ITEM_TRG CREATE OR REPLACE TRIGGER MC_90_DAYS_MENU_ITEM_TRG