Meals DB

Hello, today we will do a database design together with you.

Design a Meals_DB database for cookery book according to the following:

• create the following tables:
  Ingredients, Dishes, Categories, Meals
• Database should contain the following relationships:
  Prepared_by, Meal_Contains
• Dishes are prepared with specified ingredients in specified amounts and each dish has calories details.

Sample Data (from more than one table):

Lentil Soup: (1 onion, 2 carrots, 2 cloves garlic, one cup of lentil), 139 calories

Mushroom Soup: (2 onion, 4 cloves garlic, 750-gram mushroom, one cup cream), 96 calories

Green Salad: (1 tomato, one cucumber, 1 lettuce, 0.5 onion, 0.5 cup olive oil, half limon), 9 calories

Roka Salad: (2 onion, 2 cups of roka, 1 tomato, 0.5 cup parmesan cheese, 0.5 cup olive oil, half limon, 0.25 cup balsamic vinegar), 120 calories

Chicken Rice: (1 cup rice, 600-gram chicken, 0.5 cup chickpeas), 607 calories

Spaghetti bolognese: (400 gram spaghetti, 500 gram beef mince, 100 gram tomato paste, 2 onion, 2 cloves garlic, 0.5 cup parmesan cheese), 667 calories

Dishes have got well-defined categories (e.g. soups, main dish, etc.)

Meals have a certain degree of difficulty, preparation time.

Meal 1: (Lentil soup, Green salad, Chicken Rice), Difficult, 60 minutes

Meal 2: (Mushroom soup, Roka salad, Spaghetti Bolognese), Easy, 45 minutes

Meal 3: (Lentil soup, Roka salad, Chicken Rice), Difficult, 75 minutes

Meal 4: (Lentil soup, Green salad, Spaghetti bolognese), Medium, 60 minutes

SQL command to create the database:

create database Meals_DB;

SQL command to create one of the tables:

create table Ingredients (Ingredients_id int,Ingredients_name varchar(15),Ingredients_calories int);

create table dishes(dishes_id int,dishes_name varchar(15),ingredients1 int,ingredients2 int,ingredients3 int,ingredients4 int,ingredients5 int,ingredients6 int,calories int);

create table categories(category_id int,category_name varchar(15));

create table meals(meals_id int,soups int,salad int,difficulty varchar(15),times int);

create table meal_contains(m_id int,meal_id int,dish_id int);

create table prepared_by(prep_id int,dish_id int,ing_id int,amount varchar(30));

Screenshot of the relationships diagram

SQL commands to insert data into one of the tables:

INSERT INTO categories(category_name)values('Soup');

Insert the sample data (mentioned in the first section) in the appropriate tables.

Screenshot:

SQL command to list all salads in descending order according to calories.

select dishes.dishes_name,dishes.calories,categories.category_id From dishes inner join categories on dishes.category_id=categories.category_id order by category_id DESC

SQL command to list all the ingredients that contains the letter “g”

select * from Ingredients where Ingredients_name LIKE '%g%';

SQL command to find the number of ingredients for each Dish.

select dishes.dishes_name,count(prepared_by.dish_id) from prepared_by inner join dishes on dishes.dishes_id = prepared_by.dish_id GROUP BY dishes.dishes_name,dishes_id;

SQL command to find the difficulty of the meal that has the maximum preparation time.

select max(times) from meals

SQL command to find the dishes which calories is less than 100 or is a main dish.

select * from dishes where calories<100 or category_id=4

SQL command to find the meals that take 60 minutes to pe prepared and are easy or medium.

select * from meals where difficulty='Medium' or difficulty='Easy' or times=60

SQL command that modify the Dishes table by adding the “Origin” attribute.

alter table dishes add Origin varchar(15)

SQL command that modify the Origin of Spaghetti bolognese to ‘Italy’

update dishes set origin='Italy' where dishes_name='Spaghetti Bolognese'

SQL command that display the dishes and details of the third meal.

select dishes.dishes_name,meals.difficulty,dishes.calories,categories.category_name from meal_contains
inner join meals on meals.meals_id=meal_contains.meal_id
inner join dishes on dishes.dishes_id=meal_contains.dish_id
inner join categories on categories.category_id = dishes.category_id where
meal_contains.meal_id=3

SQL command that display the preparation details of Green Salad.

select dishes.dishes_name,Ingredients.Ingredients_name,prepared_by.amount from prepared_by
inner join dishes on dishes.dishes_id=prepared_by.dish_id
inner join Ingredients on Ingredients.Ingredients_id=prepared_by.ing_id

SQL command that modifies the tomato amount of the Green salad to be two

update prepared_by set amount='two' where dish_id=3 and ing_id=7

SQL command that displays the Category names of dishes that contain parmesan cheese

select categories.category_name from prepared_by inner join
dishes on dishes.dishes_id=prepared_by.dish_id
inner join categories on categories.category_İd=dishes.category_id
where prepared_by.ing_id=13