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 (recipe_id INT NOT NULL,recipe_name VARCHAR(30) NOT NULL,PRIMARY KEY (recipe_id),UNIQUE (recipe_name));
INSERT INTO recipes (recipe_id, recipe_name) VALUES (1,"Tacos"),(2,"Tomato Soup"),(3,"Grilled Cheese");

CREATE TABLE ingredients (ingredient_id INT NOT NULL, ingredient_name VARCHAR(30) NOT NULL,ingredient_price INT NOT NULL,PRIMARY KEY (ingredient_id),UNIQUE (ingredient_name));
INSERT INTO ingredients(ingredient_id, ingredient_name, 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 (recipe_id int NOT NULL, ingredient_id INT NOT NULL, amount INT NOT NULL,PRIMARY KEY (recipe_id,ingredient_id));
INSERT INTO recipe_ingredients (recipe_id, ingredient_id, 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.