Create a procedure MC_CREATE_RANDOM_MENU that produces a random full-day menu, according to the following specification: Parameters:...

   

Transcribed Image Text:

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 Lunch: o o one random item from "Breakfast" category + one random item from "Coffee & Tea" category one random item from "Salads" 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" category (desserts must be included only in random 50% of the daily menus) Afternoon snack (randomly choose between A or B combinations below): 00 (A) o one random item from "Smoothies & Shakes" category OR (B) o o Dinner: o 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) + O one random item from "Beverages" category + o one random item from "Desserts" or "Coffee & Tea" categories (randomly choose one from the two categories above) Procedure Code (20 points) Procedure MC _CREATE_RANDOM_MENU Code Trigger Code (20 points) 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 MC_90_DAYS_MENU_ITEM_TRG Code CREATE OR REPLACE TRIGGER MC_90_DAYS_MENU_ITEM_TRG *** MC_MENU_CATEGORY CATEGORY_ID integer CATEGORY_NAME varchar2(30) MC_MENU_ITEM MENU ITEM_ID CATEGORY_ID MENU ITEM SERVING_SIZE_OZ SERVING SIZE_G SERVING_SIZE_FL_OZ SERVING_SIZE_ML CALORIES CALORIES FROM_FAT TOTAL FAT TOTAL FAT_PERC_DAILY_VALUE SODIUM CHOLESTEROL_PERC_DAILY_VALUE SATURATED FAT number(6,1) SATURATED FAT_PERC_DAILY VALUE number(6,1) TRANS FAT number(6,1) CHOLESTEROL SODIUM_PERC_DAILY VALUE CARBOHYDRATES CARBOHYDRATES_PERC_DAILY_VALUE DIETARY FIBER DIETARY FIBER PERC_DAILY_VALUE SUGARS PROTEIN PK VITAMIN_A_PERC_DAILY VALUE VITAMIN C PERC DAILY VALUE CALCIUM PERC_DAILY VALUE IRON_PERC_DAILY_VALUE integer integer varchar2(255) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) PK FK MC_90_DAYS MENU DAY NUMBER SUM_FAT DAILY PERC SUM SATURATED FAT_DAILY_PERC SUM CHOLESTEROL_DAILY_PERC SUM SODIUM_DAILY_PERC SUM CARBOHYDRATES_DAILY_PERC SUM DIETARY FIBER_DAILY_PERC SUM VITAMIN A DAILY PERC SUM VITAMIN_C_DAILY_PERC SUM_CALCIUM_DAILY_PERC SUM_IRON_DAILY_PERC MC_90_DAYS_MENU_ITEM DAY NUMBER MEAL_TYPE_ID MENU_ITEM_ID integer number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N PK integer PK FK integer PK FK integer PK FK MC_90_DAYS MEAL_TYPE MEAL_TYPE_ID integer MEAL_TYPE_NAME varchar2(30) PK Vertabelo 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 Lunch: o o one random item from "Breakfast" category + one random item from "Coffee & Tea" category one random item from "Salads" 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" category (desserts must be included only in random 50% of the daily menus) Afternoon snack (randomly choose between A or B combinations below): 00 (A) o one random item from "Smoothies & Shakes" category OR (B) o o Dinner: o 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) + O one random item from "Beverages" category + o one random item from "Desserts" or "Coffee & Tea" categories (randomly choose one from the two categories above) Procedure Code (20 points) Procedure MC _CREATE_RANDOM_MENU Code Trigger Code (20 points) 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 MC_90_DAYS_MENU_ITEM_TRG Code CREATE OR REPLACE TRIGGER MC_90_DAYS_MENU_ITEM_TRG *** MC_MENU_CATEGORY CATEGORY_ID integer CATEGORY_NAME varchar2(30) MC_MENU_ITEM MENU ITEM_ID CATEGORY_ID MENU ITEM SERVING_SIZE_OZ SERVING SIZE_G SERVING_SIZE_FL_OZ SERVING_SIZE_ML CALORIES CALORIES FROM_FAT TOTAL FAT TOTAL FAT_PERC_DAILY_VALUE SODIUM CHOLESTEROL_PERC_DAILY_VALUE SATURATED FAT number(6,1) SATURATED FAT_PERC_DAILY VALUE number(6,1) TRANS FAT number(6,1) CHOLESTEROL SODIUM_PERC_DAILY VALUE CARBOHYDRATES CARBOHYDRATES_PERC_DAILY_VALUE DIETARY FIBER DIETARY FIBER PERC_DAILY_VALUE SUGARS PROTEIN PK VITAMIN_A_PERC_DAILY VALUE VITAMIN C PERC DAILY VALUE CALCIUM PERC_DAILY VALUE IRON_PERC_DAILY_VALUE integer integer varchar2(255) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) number(6,1) PK FK MC_90_DAYS MENU DAY NUMBER SUM_FAT DAILY PERC SUM SATURATED FAT_DAILY_PERC SUM CHOLESTEROL_DAILY_PERC SUM SODIUM_DAILY_PERC SUM CARBOHYDRATES_DAILY_PERC SUM DIETARY FIBER_DAILY_PERC SUM VITAMIN A DAILY PERC SUM VITAMIN_C_DAILY_PERC SUM_CALCIUM_DAILY_PERC SUM_IRON_DAILY_PERC MC_90_DAYS_MENU_ITEM DAY NUMBER MEAL_TYPE_ID MENU_ITEM_ID integer number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N number(6,1) N PK integer PK FK integer PK FK integer PK FK MC_90_DAYS MEAL_TYPE MEAL_TYPE_ID integer MEAL_TYPE_NAME varchar2(30) PK Vertabelo