home / study / engineering / computer science / computer science questions and a

Question

home / study / engineering / computer science / computer science questions and answers / write the sql ddl to create the following 5 tables describing cooking: recipe, ingredient, ...

Question: Write the SQL DDL to create the following 5 tables describing cooking: recipe, ingredient, author...

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

1. CREATE TABLE AUTHOR(AUTHOR_ID INT PRIMARY KEY, AUTHOR_NAME VARCHAR(30));

2. CREATE TABLE INGREDIENT(INGRED_ID CHAR(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 CHAR(5),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));

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.