RZ952kY9

1-- start the server: $ mysqld --console
2-- login:            $ mysql -u root --password=wxyz
3-- run the script:   mysql> source /Users/javapro/dev/src/sql/Cookbook.sql
4-- the script: 
5
6drop database if exists Cookbook;
7
8create database Cookbook; 
9
10connect Cookbook; 
11	
12create table Recipe (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
13	name VARCHAR(25), 
14	description VARCHAR(50), 
15	instructions VARCHAR(500)) 
16	ENGINE=InnoDB DEFAULT CHARSET=utf8;
17
18create table Ingredient (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
19	name VARCHAR(50)) 
20	ENGINE=InnoDB DEFAULT CHARSET=utf8; 
21
22create table Measure (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, 
23	name VARCHAR(30)) 
24	ENGINE=InnoDB DEFAULT CHARSET=utf8; 
25
26create table RecipeIngredient (recipe_id INT NOT NULL, 
27	ingredient_id INT NOT NULL, 
28	measure_id INT, 
29	amount INT, 
30	CONSTRAINT fk_recipe FOREIGN KEY(recipe_id) REFERENCES Recipe(id), 
31	CONSTRAINT fk_ingredient FOREIGN KEY(ingredient_id) REFERENCES Ingredient(id), 
32	CONSTRAINT fk_measure FOREIGN KEY(measure_id) REFERENCES Measure(id)) 
33	ENGINE=InnoDB DEFAULT CHARSET=utf8; 
34
35INSERT INTO Measure (name) VALUES('CUP'), ('TEASPOON'), ('TABLESPOON');
36
37INSERT INTO Ingredient (name) VALUES('egg'), ('salt'), ('sugar'), ('chocolate'), ('vanilla extract'), ('flour');
38
39INSERT INTO Recipe (name, description, instructions) VALUES('Boiled Egg', 'A single boiled egg', 'Add egg to cold water. Bring water to boil. Cook.');
40
41INSERT INTO Recipe (name, description, instructions) VALUES('Chocolate Cake', 'Yummy cake', 'Add eggs, flour, chocolate to pan. Bake at 350 for 1 hour');
42
43INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount) VALUES (1, 1, NULL, 1);
44
45INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (2, 1, NULL, 3);
46
47INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (2, 2, 2, 1);
48
49INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (2, 3, 1, 2);
50
51INSERT INTO RecipeIngredient (recipe_id, ingredient_id, measure_id, amount)  VALUES (2, 4, 1, 1);
52
53SELECT r.name AS 'Recipe', 
54	r.instructions, 
55	ri.amount AS 'Amount', 
56	mu.name AS 'Unit of Measure', 
57	i.name AS 'Ingredient' 
58FROM Recipe r 
59JOIN RecipeIngredient ri on r.id = ri.recipe_id 
60JOIN Ingredient i on i.id = ri.ingredient_id 
61LEFT OUTER JOIN Measure mu on mu.id = measure_id;