John, a recent college graduate living in Orange County, spent months earning mo

Question

John, a recent college graduate living in Orange County, spent months earning money by running errands and completing basic chores for family members and acquaintances, while looking for full-time employment. As his list of customers needing such services continued to grow, John decided to start his own business. The business, which John operates completely online from his home, offers customers a variety of services – from grocery shopping and household chores to yard work and pet care at various fees. Create a database design that will help him manage his business efficiently. Implement the database and insert a minimum of 3 records. Submit your relations in 3NF in parenthetical method, SQL statements (CREATE and INSERT INTO), database diagram, and SQL queries listing all columns and records for all tables in the database.

Explanation / Answer

CREATE TABLE recipes_tbl
(recipe_idno INT NOT NULL,
recipe_FirstName VARCHAR(30) NOT NULL,
PRIMARY KEY (recipe_idno),
UNIQUE (recipe_name));

INSERT INTO recipes_tbl (recipe_idno, recipe_FirstName) VALUES (1,"Tacos"),(2,"Tomato Soup"),(3,"Grilled Cheese");

CREATE TABLE ingredients_tbl
(ingredient_idno INT NOT NULL,
ingredient_FirstName VARCHAR(30) NOT NULL,
ingredient_price INT NOT NULL,
PRIMARY KEY (ingredient_idno),
UNIQUE (ingredient_FirstName));

INSERT INTO ingredients_tbl(ingredient_idno, ingredient_FirstName, ingredient_price)VALUES(1, "juice", 5),(2, "Lettuce", 1),(3, "Tomatoes", 2),(4, "Taco Shell", 2),(5, "Cheese", 3),(6, "Milk", 1),(7, "Bread", 2);

CREATE TABLE recipe_ingredients_tbl
(recipe_idno int NOT NULL,
ingredient_idno INT NOT NULL,
amount INT NOT NULL,
PRIMARY KEY (recipe_idno,ingredient_idno));

INSERT INTO recipe_ingredients_tbl (recipe_idno, ingredient_idno, amount)VALUES(1,1,1),(1,2,2),(1,3,2),(1,4,3),(1,5,1),(2,3,2),(2,6,1),(3,5,1),(3,7,2);

Related Questions

Navigate

• Browse (All)
• Browse J
• Subjects

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