CookbookDB.sql

-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Sep 30, 2014 at 05:40 AM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `cookbook`
--

--
-- Create schema cookbook
--
DROP DATABASE IF EXISTS cookbook;
CREATE DATABASE cookbook;
USE cookbook;


-- --------------------------------------------------------

--
-- Table structure for table `recipe`
--

CREATE TABLE IF NOT EXISTS `recipe` (
  `recipe_id` smallint(2) NOT NULL,
  `recipe_name` varchar(50) NOT NULL,
  `description` varchar(150) NOT NULL,
  `portion_size` smallint(2) NOT NULL,
  PRIMARY KEY (`recipe_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `recipe`
--

INSERT INTO `recipe` (`recipe_id`, `recipe_name`, `description`, `portion_size`) VALUES
(01, 'Pizza Margharita', 'A classic and easy-to-make cheese pizza.', 1),
(02, 'Spaghetti and Meatballs', 'The most iconic pasta dish, topped with sauce and meatballs.', 2),
(03, 'Smash Burger', 'A fast-food-famous meat sandwich with plenty of options.', 1),
(04, "Mac 'n Cheese", 'A satisfyingly cheesy pasta dish.', 1),
(05, 'Chili', 'A spicy bean and meat dish that can be used as a topping.', 2),
(06, 'Burrito', 'A large sandwich filled with rice, meat, and whatever your heart desires.', 2),
(07, 'Kibbeh', 'Meat-rich Bite-sized fried handpies.', 3),
(08, 'Shawerma', 'A sandwich filled with fried savory yoghurt-marinated chicken.', 2),
(09, 'Koshary', 'A satisfying mix of pasta, lentils, and rice, topped with fried onions.', 2),
(10, 'Falafel', 'Fried hummus-based nuggets perfect with tahini sauce.', 1);

-- --------------------------------------------------------

--
-- Table structure for table `measurement_units`
--

CREATE TABLE IF NOT EXISTS `measurement_units` (
  `measurement_id` smallint(2) NOT NULL,
  `measurement_description` varchar(50) NOT NULL,
  PRIMARY KEY (`measurement_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `measurement_units`
--

INSERT INTO `measurement_units` (`measurement_id`, `measurement_description`) VALUES
(01, 'tsp'),
(02, 'Tbsp'),
(03, 'cup'),
(04, 'g'),
(05, 'ml'),
(06, 'pinch'),
(07, 'drop'),
(08, 'pack');

-- --------------------------------------------------------

--
-- Table structure for table `measurement_qty`
--

CREATE TABLE IF NOT EXISTS `measurement_qty` (
  `measurement_qty_id` smallint(2) NOT NULL,
  `qty_amount` DECIMAL(6,2) NOT NULL,
  PRIMARY KEY (`measurement_qty_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `measurement_qty`
--

INSERT INTO `measurement_qty` (`measurement_qty_id`, `qty_amount`) VALUES
(01, 0.25),
(02, 0.5),
(03, 1),
(04, 2),
(05, 3),
(06, 4),
(07, 5),
(08, 6),
(09, 7),
(10, 8),
(11, 9),
(12, 10),
(13, 11),
(14, 12),
(15, 13),
(16, 14),
(17, 15),
(18, 20),
(19, 25),
(20, 30),
(21, 50),
(22, 75),
(23, 100),
(24, 125),
(25, 150),
(26, 200),
(27, 250),
(28, 300),
(29, 500),
(30, 750),
(31, 1000);

-- --------------------------------------------------------

--
-- Table structure for table `ingredients`
--

CREATE TABLE IF NOT EXISTS `ingredients` (
  `ingredient_id` smallint(2) NOT NULL,
  `ingredient_name` varchar(30) NOT NULL,
  PRIMARY KEY (`ingredient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ingredients`
--

INSERT INTO `ingredients` (`ingredient_id`, `ingredient_name`) VALUES
(01, 'water'),
(02, 'salt'),
(03, 'sugar'),
(04, 'tomato'),
(05, 'flour'),
(06, 'onion'),
(07, 'black pepper'),
(08, 'thyme'),
(09, 'pasta'),
(10, 'chicken breast'),
(11, 'ground beef'),
(12, 'lentil'),
(13, 'rice'),
(14, 'yoghurt'),
(15, 'paprika'),
(16, 'chilli flakes'),
(17, 'bouillon cube'),
(18, 'hummus'),
(19, 'parsley'),
(20, 'coriander'),
(21, 'basil'),
(22, 'burger buns'),
(23, 'flat bread'),
(24, 'olive oil'),
(25, 'red beans'),
(26, 'mozzarella cheese'),
(27, 'milk'),
(28, 'cream cheese'),
(29, 'garlic clove'),
(30, 'garlic powder'),
(31, 'onion powder'),
(32, 'ginger powder'),
(33, 'oregano'),
(34, 'tahini'),
(35, 'ketchup'),
(36, 'mustard'),
(37, 'lettuce'),
(38, 'cheddar cheese'),
(39, 'lemon'),
(40, 'bell pepper'),
(41, 'bulgur wheat');

-- --------------------------------------------------------

--
-- Table structure for table `recipe_ingredients`
--

CREATE TABLE IF NOT EXISTS `recipe_ingredients` (
  `recipe_id` smallint(2) NOT NULL,
  `measurement_id` smallint(2) NOT NULL,
  `measurement_qty_id` smallint(2) NOT NULL,
  `ingredient_id` smallint(2) NOT NULL,
  PRIMARY KEY (`recipe_id`, `measurement_id`, `measurement_qty_id`, `ingredient_id`),
  FOREIGN KEY (`recipe_id`) REFERENCES recipe(recipe_id),
  FOREIGN KEY (`measurement_id`) REFERENCES measurement_units(measurement_id),
  FOREIGN KEY (`measurement_qty_id`) REFERENCES measurement_qty(measurement_qty_id),
  FOREIGN KEY (`ingredient_id`) REFERENCES ingredients(ingredient_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `recipe_ingredients`
--

INSERT INTO `recipe_ingredients` (`recipe_id`, `measurement_id`, `measurement_qty_id`, `ingredient_id`) VALUES
-- Pizza
(1, 3, 3, 5),     -- 1 cup flour
(1, 5, 23, 1),    -- 100 ml water
(1, 1, 3, 2),     -- 1 tsp salt
(1, 4, 23, 4),    -- 100 g tomato
(1, 4, 23, 26),   -- 100 g mozzarella
(1, 6, 3, 21),    -- pinch basil
(1, 2, 3, 24),    -- 1 Tbsp olive oil
-- Spaghetti & Meatballs
(2, 8, 3, 9),     -- 1 pack pasta
(2, 4, 25, 11),   -- 150 g beef
(2, 4, 23, 4),    -- 100 g tomato
(2, 4, 12, 6),    -- 10 g onion
(2, 1, 3, 29),    -- 1 tsp garlic
(2, 1, 3, 2),     -- salt
(2, 1, 3, 7),     -- pepper
-- Burger
(3, 4, 25, 11),   -- 150 g beef
(3, 6, 3, 2),     -- pinch salt
(3, 6, 3, 7),     -- pinch pepper
(3, 8, 3, 22),    -- 1 pack buns
(3, 4, 12, 38),   -- 10 g cheddar
(3, 4, 5, 37),    -- 3 g lettuce
(3, 2, 3, 35),    -- 1 Tbsp ketchup
(3, 2, 3, 36),    -- 1 Tbsp mustard
-- Mac 'n Cheese
(4, 8, 3, 9),     -- 1 pack pasta
(4, 5, 25, 27),   -- 150 ml milk
(4, 4, 23, 38),   -- 100 g cheddar
(4, 1, 3, 2),     -- salt
(4, 1, 3, 7),     -- pepper
(4, 3, 1, 5),     -- 0.25 cup flour
-- Chili
(5, 4, 25, 25),   -- 150 g red beans
(5, 4, 25, 11),   -- 150 g beef
(5, 4, 12, 6),    -- 10 g onion
(5, 4, 23, 4),    -- 100 g tomato
(5, 1, 3, 15),    -- paprika
(5, 1, 3, 16),    -- chili flakes
(5, 1, 3, 2),     -- salt
-- Burrito
(6, 4, 23, 13),   -- 100 g rice
(6, 4, 23, 25),   -- 100 g beans
(6, 4, 25, 10),   -- 150 g chicken
(6, 4, 12, 6),    -- 10 g onion
(6, 1, 3, 29),    -- garlic
(6, 1, 3, 2),     -- salt
(6, 1, 3, 7),     -- pepper
(6, 1, 3, 15),    -- paprika
-- Kibbeh
(7, 4, 23, 11),   -- 100 g beef
(7, 4, 12, 6),    -- 10 g onion
(7, 4, 23, 41),   -- 100 g bulgur wheat
(7, 1, 3, 2),     -- salt
(7, 1, 3, 7),     -- pepper
(7, 2, 3, 24),    -- 1 Tbsp oil
-- Shawerma
(8, 4, 25, 10),   -- 150 g chicken
(8, 5, 12, 14),   -- 10 ml yoghurt
(8, 1, 3, 29),    -- garlic
(8, 1, 3, 39),    -- lemon
(8, 1, 3, 15),    -- paprika
(8, 1, 3, 2),     -- salt
(8, 1, 3, 7),     -- pepper
-- Koshary
(9, 4, 23, 13),   -- 100 g rice
(9, 4, 23, 12),   -- 100 g lentils
(9, 8, 3, 9),     -- 1 pack pasta
(9, 4, 12, 6),    -- 10 g onion
(9, 4, 23, 4),    -- 100 g tomato
(9, 1, 3, 2),     -- salt
(9, 1, 3, 7),     -- pepper
-- Falafel
(10, 4, 23, 18),  -- 100 g hummus base
(10, 4, 12, 6),   -- 10 g onion
(10, 1, 3, 29),   -- garlic
(10, 4, 5, 19),   -- 3 g parsley
(10, 4, 5, 20),   -- 3 g coriander
(10, 1, 3, 2),    -- salt
(10, 1, 3, 7);    -- pepper

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_name` varchar(30) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`user_name`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------


/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;