Write the SQL DDL to create the following 5 tables describing cooking: recipe, i
ID: 3872230 • Letter: W
Question
Write the SQL DDL to create the following 5 tables describing cooking: recipe, ingredient, author, cook, requires:
1. An Author table where each author is identified by an integer id and has a name (up to 30 characters).
2. An Ingredient table where each ingredient has an id of exactly 5 characters and a name (up to 30 characters).
3. A Recipe table where each recipe is identified by a field called id that is an integer. Other attributes include name (string up to 40 characters), author id (integer), and directions (string up to 255 characters). Make all foreign keys set to null on delete and no action (generate error) on update.
4. A Cook table where each time a recipe is made it is identified by a date/time (DATETIME). The table also has a recipe id and a comment (string up to 255 characters). Make all foreign keys set to perform cascade on delete and cascade on update.
5. A Requires table that stores what ingredients are needed in a recipe. This table has a recipe id, ingredient id, and amount (floating point number). Make all foreign keys set to cascade on both update and delete.
Explanation / Answer
5.Requires Table
create table Requires(
recp_id number(20),
ing_id number(20),
amount float,
CONSTRAINT Recp_id_req_fk FOREIGN KEY (recp_id) REFERENCES Cook (Recp_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT ing_id_req_fk FOREIGN KEY (ing_id) REFERENCES Ingredient (ing_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
***********************************************************************
4.Cook Table
create table Cook
(
recp_id number(20),
recipe_date DATETIME,
comment varchar2(255),
CONSTRAINT Recp_id_cook_fk FOREIGN KEY (Recp_id) REFERENCES Recipe (Recp_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
***********************************************************************
3.Reciepe Table
create table Recipe
(
Recp_id number(20),
name varchar2(40),
author_id number(20),
directions varchar2(255),
CONSTRAINT Recp_id_recp_fk FOREIGN KEY (Recp_id) REFERENCES Cook (Recp_id)
ON DELETE SET NULL
ON UPDATE NO ACTION
);
***********************************************************************
2.Author Table
create table Author
(
auth_id number(20),
name varchar2(30),
CONSTRAINT auth_id_pk PRIMARY KEY (auth_id)
);
***********************************************************************
1.INgredient Table
create table Ingredient
(
ing_id varchar(2),
name varchar2(30),
CONSTRAINT Ingre_id_pk PRIMARY KEY (ing_id)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.