RoadKill Café has a variety of tasty dishes that have, up until now, all been ma

Question

RoadKill Café has a variety of tasty dishes that have, up until now, all been made by the owner. But with the new freeway recently built nearby, business is booming, and he has to hire more cooks. In order to continue to offer the same selections, he wants to get all of his recipes into a database so that the other cooks can use them. He has hired you to build the database for him.

You sit down with him to get a sense of what he wants you to build. You come away with the following notes:

• Each Recipe has a name, a description, the number of people it serves, and the steps that must be followed.

•Each step has an instruction, an ingredient, and the amount of the ingredient needed. The amount usually includes a unit of measure, but it doesn’t have to, i.e. 2 cups, 1 tbsp, or simply 2.

•Some steps do not have an ingredient, i.e. stir for ten minutes or let stand for an hour.

•The owner wants to make sure he doesn’t run low on any of the ingredients. He wants to track the name of each ingredient he has in the store room, a description of each ingredient, the location of the ingredient in the storage room, and the amount on hand, i.e. 5 pounds, 2 bags, or simply 10.

SQL Statements

— Provide a series of SQL statements that can be passed to the SQL Fiddle Web site (www.sqlfiddle.org) and successfully executed. All entities with their attributes as determined in the E-R diagram must exist in the SQL statements, and primary and foreign key constraints must be added to create the relationships between the tables determined by the model.

Explanation / Answer

we need to create various tables in order to meet the requirements of the Roadkill cafe.

1) Table name Recipe with Recipe name as PrimaryKey having attributes like description, number of people it serves and the steps to create the recipe.

2)We need to create one ingredient tables with containing attributes and amount of ingredients in the store,recipe name. Ingredient name will be the primary key here and recipe name will be the foriegn key.

3) We need to create a table for taking orders where Name of the person will be there, order number (primary key) , order date and recipe ordered.

Now based on the order we can check the ingredients required from the ingredients table and can subtract the required amount from the total quantity in the ware house.

sql tables will be

create table recipe (recipename varchar 50 not null primary key, description varchar 200,numberofpeople int , steps varchar 500)

create table ingredient (name varchar 50 not null primary key, amount int, recipename varchar 50 not null)

create table orders (name varchar 50, orderno not null primary key, orderdate date , recipe name varchar 100)

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

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