1. CREATE TABLE AUTHOR(AUTHOR_ID INT PRIMARY KEY, AUTHOR_NAME VARCHAR(30)); 2. C
ID: 3872672 • Letter: 1
Question
1. CREATE TABLE AUTHOR(AUTHOR_ID INT PRIMARY KEY, AUTHOR_NAME VARCHAR(30));
2. CREATE TABLE INGREDIENT(INGRED_ID INT(5) PRIMARY KEY,INGRED_NAME VARCHAR2(30));
3. CREATE TABLE RECIPE (REC_ID INT PRIMARY KEY,REC_NAME VARCHAR2(40),AUTHOR_ID INT,DIRECTIONS VARCHAR2(255),FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ON DELETE SET NULL ON UPDATE NO ACTION);
4. CREATE TABLE COOK(REC_ID INT , RECDATE DATETIME,COMMENT VARCHAR2(255), FOREIGN KEY(REC_ID) REFERENCES RECIPE(REC_ID) ON DELETE CASCADE ON UPDATE CASCADE);
5. CREATE TABLE REQUIRES(REC_ID INT,INGRED_ID,AMOUNT FLOAT, FOREIGN KEY(REC_ID) REFERENCES RECIPE(REC_ID) ON DELETE CASCADE ON DELETE UPDATE, FOREIGN KEY(INGRED_ID) REFERENCES INGREDIENT(INGRED_ID) ON DELETE CASCADE ON UPDATE CASCADE), UNIQUE(REC_ID,INGRED_ID));
Write the SQL DDL to perform the following modifications to the database created above..
Insert (6 marks) Insert the following records into the appropriate tables. 1. Author (1, Joe Smith') ; (2, 'Fred Funk') 2. IngredientBUTTR, 'Butter') ; ('FLOUR', 'Flour') ; MILK , 'MilkECGS, EggsSUGAR', Sugar 3. Recipe - (100, 'Cookies'.1, 'Mix butter, flour, milk, eggs, and ougar. Then hope for the best (200, 'Bread,2, Knead lour with milk and eggs. Bake at 450F or until brown.') 4. Requires- You figure it out based on both recipes (choose your own amount). 5. Cook - Recipe for bread was made on July 1st, 2015 (no comment). Recipe for cookies was made on September 23rd, 2015 at 1:35:45 p.m. Comment: 't actually worked! Delete (2 marks) Delete all recipes written by 'Fred Funk'.How many rows are deleted when this statement is run? (1 mark) Note: Recommend you try the DELETE when the foreign key on Recipe is either SET NULL or NO ACTION Update (2 marks) Change the name of the ingredient with id . MILK ' to ' Skim MilkExplanation / Answer
Insert statements:-
Delete Query:-
It will delete only one Recipe becaue at present there is only one row entry of recipe made by Fred Funk.
UPDATE Query:-
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.