Построение базы данных "Кулинарная книга"

Размещено на http://www.allbest.ru/

11

1

Санкт-Петербургский Государственный Политехнический Университет

Построение базы данных "Кулинарная книга"

Выполнил:

студент группы 3174/1

Моренков Е.В.

Руководитель:

Попов С.Г.

Санкт-Петербург 2011

Оглавление

Описание задачи

Иерархия объектов

ER-диаграмма базы данных

ER-диаграмма

Описание таблиц и полей

Создание таблиц в СУБД

Запросы

Заключение

Приложения

Описание задачи

Кулинарный рецепт - руководство по приготовлению кулинарного изделия. Содержит информацию о необходимых пищевых продуктах, их пропорциях и инструкциях по смешиванию и обработке. Кулинарные рецепты описывают механическую и тепловую обработку ингредиентов, способы сервировки готовых изделий.

Рецепты передаются из уст в уста, а также публикуются в кулинарных книгах и на специализированных сайтах. Существуют секретные рецепты, передающиеся кулинарами своим наследникам.

Современный кулинарный рецепт содержит:

1) Название (часто и национальную принадлежность) блюда;

2) Ориентировочное время приготовления блюда;

3) Список необходимых для приготовления блюда ингредиентов, их количество и пропорции;

4) Оборудование и условия, необходимые для приготовления блюда;

5) Количество персон, на которых рассчитано блюдо;

6) Калорийность блюда (иногда и содержание белков, жиров и углеводов);

7) Пошаговая инструкция по приготовлению блюда;

8) Метод сервировки (украшение блюда и его подача на стол).

Старые рецепты часто составлялись для тех, кто умел готовить те или иные блюда, и содержали лишь название блюда, перечень ингредиентов и их пропорции.

Виды рецептов

1) Рецепты блюд по времени приема пищи:

а) Завтрак

b) Второй завтрак

c) Обед

d) Полдник

e) Ужин

2) Рецепты по типу блюд:

a) Закуски

b) Салаты

c) Первые блюда

d) Вторые блюда

e) Десерты

3) Рецепты по способу приготовления блюд:

a) Жаренные

b) Варёные

c) Тушёные

d) Печёные

e) Резанные

4) Рецепты национальных кухонь

a) Русская кухня

b) Французская кухня

c) Итальянская кухня

d) Азиатская кухня

5) Рецепты блюд по основе:

a) Из мяса

b) Из рыбы

c) Из овощей

d) Из птицы

e) Из морепродуктов

f) Из теста

6) Рецепты блюд по событию:

a) Новый год

b) Великий пост

c) Пасха

d) День благодарения

7) Рецепты блюд по состоянию здоровья:

a) Диетические

b) Для язвенников

c) Для диабетиков

d) При ожирении

Перед собой я поставил следующую задачу: разделить множество всех рецептов по принадлежности к той или иной национальной кухне (русская, итальянская, европейская, японская), определенному виду блюда (закуска, салат, суп, паста, пицца, горячее, десерт), наличию ингредиентов (рыба, мясо, салат, помидоры и т.д.), основе (рыбная, мясная, овощная), способу приготовления блюда (жареное, вареное, тушеное, печеное). Каждый рецепт имеет своё происхождение, которое также будет храниться в базе данных. Возможно 2 варианта: рецепт будет авторским, либо добавленным из книги. Также, организована возможность комментирования рецепта пользователями с запоминанием имени этого пользователя и когда сообщение было оставлено.

Иерархия объектов

ER-диаграмма базы данных

Все рецепты принадлежат какой-либо категории, приготовлены определенным способом (жарка, варка, резка), относятся к определенной национальной кухне, добавлены из конкретной книги или определенным пользователем, созданы по определенной основе (рыба, мясо, овощи). Рецепты состоят из различных ингредиентов (от 1 до 10). Также, пользователи определяются именем, фамилией, логином и имеют пароль и могут оставлять отзывы к данному рецепту.

Ингредиенты в составе рецепта могут находиться в различных состояниях, а также могут иметь различную единицу измерения.

Каждый рецепт характеризуется временем приготовления, калорийностью, выходом блюда (в г, кг, порциях, тарелках и т.п.) - пользователь сам определяет, названием и описание способа приготовления.

ER-диаграмма

Описание таблиц и полей

Рецепт	Recipe
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_рецепта	Recipe_ID	int
2	ID_кухня	Rec_Cuisine_ID	int		Cuisine (Cuisine_ID)
3	ID_категория	Rec_Category_ID	int		Category (Category_ID)
4	ID_способа_приготовления	Rec_Cooking_method _ID	int		Cooking_method (Cooking_method_ID)
5	ID_Пользователя	Rec_User_ID	int		User (User_ID)
6	ID_Книги	Rec_Book_ID	int		Book (Book_ID)
7	Описание_способа_приготовления	Description_cooking _method	tinytext
8	Название рецепта	Recipe_name	char			100
9	Калорийность	Сaloric_content	int
10	Выход блюда	Dish_weight	char			100
11	ID_основа	Rec_Basis_ID	int			Basis (Basis_ID)
12	ID_автор	Rec_Author_ID	int			Author (Author_ID)

Кухня	Cuisine
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_кухня	Cuisine_ID	int			первичный ключ
2	Название_кухни	Cuisine_name	char			60 знаков



Категория	Category
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_категории	Category_ID	int			первичный ключ
2	Название_категории	Category_name	char			50 знаков

Способ приготовления	Cooking_method
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_способа приготовления	Cooking_method_ID	int			первичный ключ
2	Название_способа	Method_name	char			50 знаков

Состав	Composition
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_состав	Composition_ID	int			первичный ключ
2	ID_ ингредиент	Comp_Ingredient_ID	int		Ingredient (Ingredient_ID)	ссылка на ингредиент
3	ID_рецепт	Comp_recipe_ID	int		Recipe (Recipe_ID)	ссылка на блюдо
4	ID_мера_измерения	Comp_Unit_measure_ID	Int		Unit_measure (Unit_measure_ID)	Ссылка на меру измерения
5	ID_состояние	Comp_condition_ID	Int		Condition (Condition_ID)	Ссылка на состояние
6	Количество	Quantity	char			60 знаков

Состояние	Condition
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_состояния	Condition_ID	int			первичный ключ
2	Название	Condition_name	char			50 знаков



Единица измерения	Unit_measure
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_единицы _измерения	Unit_measure_ID	int			первичный ключ
2	Название	Unit_measure_name	char			50 знаков

Ингредиент	Ingredient
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_ингредиента	Ingredient_ID	int			первичный ключ
2	Название	Ingredient_name	char			50 знаков

Отзыв	Reference
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_отзыва	Reference_ID	int			первичный ключ
2	ID_пользователя	Ref_User_ID	int		ссылка на User (User_ID)	ссылка на пользователя
3	ID_рецепта	Ref_Recipe_ID	Int		Recipe (Recipe_ID)
4	Сообщение	Message	tinytext
5	Дата	Date	datetime

Автор	Author
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	ID_авторство	Author_ID	int			первичный ключ
2	Флаг	Flag	int



Пользователь	User
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	пользователь_ID	user_ID	int			первичный ключ
2	Имя	Name	char			50 символов
3	Фамилия	Surname	char			50 символов
4	Логин	Login	char			20 символов
5	Пароль	Password	char			20 символов

Книга	Book
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	книга_ID	Book_ID	int			первичный ключ
2	Название	Tittle	char			50 символов
3	Автор	author	char			50 символов
4	Описание	description	tinytext

Основа	Basis
№	Название по-русски	Название по-английски	Тип переменных	Примечание	Ссылка	Обоснование типа
1	Основа_ID	Basis_ID	Int			Первичный ключ
2	Название	Basis_name	char			50 символов

Создание таблиц в СУБД

drop database if exists Recipe_book;

Create database Recipe_book;

Use Recipe_book database;

Cuisine

create table Cuisine(

Cuisine_ID int NOT NULL auto_increment PRIMARY KEY,

Cuisine_name char(60) default NULL)ENGINE=InnoDB;

Category

create table Category(

Category_ID int NOT NULL auto_increment PRIMARY KEY,

Category_name char(50) default NULL)ENGINE=InnoDB;

Cooking_method

create table Cooking_method(

Cooking_method_ID int NOT NULL auto_increment PRIMARY KEY,

Method_name char(50) default NULL)ENGINE=InnoDB;

Conditions

create table Conditions(

Condition_ID int NOT NULL auto_increment PRIMARY KEY,

Conditions_name char(50) default NULL)ENGINE=InnoDB;

Unit_measure

create table Unit_measure(

Unit_measure_ID int NOT NULL auto_increment PRIMARY KEY,

Unit_measure_name char(50) default NULL)ENGINE=InnoDB;

Basis

create table Basis(

Basis_ID int NOT NULL auto_increment PRIMARY KEY,

Basis_name char(50) default NULL)ENGINE=InnoDB;

Author

create table Author(

Author_ID int NOT NULL auto_increment PRIMARY KEY,

Flag char(50) default NULL)ENGINE=InnoDB;

Ingredient

create table Ingredient(

Ingredient_ID int NOT NULL auto_increment PRIMARY KEY,

Ingredient_name char(50) default NULL)ENGINE=InnoDB;

User

create table User(

User_ID int NOT NULL auto_increment PRIMARY KEY,

User_name char(50) default NULL,

User_surname char(50) default NULL,

Login char(50) default NULL,

Password char(50) default NULL)ENGINE=InnoDB;

Reference

create table Reference(

Reference_ID int NOT NULL auto_increment PRIMARY KEY,

Ref_User_ID int NOT NULL,

INDEX user_ind (Ref_User_ID),

Ref_Recipe_ID int NOT NULL,

INDEX Ref_Recipe_ind (Ref_Recipe_ID),

Message tinytext default NULL,

Date datetime default NULL,

FOREIGN KEY (Ref_User_ID)

REFERENCES User(User_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION)ENGINE=InnoDB;

Composition

create table Composition(

Composition_ID int NOT NULL auto_increment PRIMARY KEY,

Comp_Ingredient_ID int NOT NULL,

INDEX Comp_Ingredient_ind (Comp_Ingredient_ID),

Comp_Recipe_ID int NOT NULL,

INDEX Comp_Recipe_ind (Comp_Recipe_ID),

Comp_Unit_measure_ID int NOT NULL,

INDEX Comp_Unit_measure (Comp_unit_measure_ID),

Comp_Condition_ID int NOT NULL,

INDEX Comp_Condition_ind (Comp_Condition_ID),

Quantity int default NULL,

FOREIGN KEY (Comp_Ingredient_ID)

REFERENCES Ingredient(Ingredient_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Comp_Unit_measure_ID)

REFERENCES Unit_measure(Unit_measure_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Comp_Condition_ID)

REFERENCES Conditions(Condition_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION

)ENGINE=InnoDB;

Book

create table Book(

Book_ID int NOT NULL auto_increment PRIMARY KEY,

Title char(50) default NULL,

Author char(50) default NULL,

Description tinytext default NULL)ENGINE=InnoDB;

Recipe

create table Recipe(

Recipe_ID int NOT NULL auto_increment PRIMARY KEY,

Rec_Cuisine_ID int NOT NULL,

INDEX Rec_Cuisine_ind (Rec_Cuisine_ID),

Rec_Category_ID int NOT NULL,

INDEX Rec_Category_ind (Rec_Category_ID),

Rec_Cooking_method_ID int NOT NULL,

INDEX Rec_Cooking_method_ind (Rec_Cooking_method_ID),

Rec_User_ID int NOT NULL,

INDEX Rec_User_ind (Rec_User_ID),

Rec_Book_ID int NOT NULL,

INDEX Rec_Book_ind (Rec_Book_ID),

Rec_Basis_ID int NOT NULL,

INDEX Rec_Basis_ind (Rec_Basis_ID),

Rec_Author_ID int NOT NULL,

INDEX Rec_Author_ind (Rec_Author_ID),

Description_cooking_method tinytext default NULL,

Recipe_name char(100) default NULL,

Caloric_content int default NULL,

Dish_weight char(100) default NULL,

FOREIGN KEY (Rec_Cuisine_ID)

REFERENCES Cuisine(Cuisine_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Rec_Category_ID)

REFERENCES Category(Category_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Rec_Cooking_method_ID)

REFERENCES Cooking_method(Cooking_method_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Rec_User_ID)

REFERENCES User(User_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Rec_Book_ID)

REFERENCES Book(Book_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Rec_Basis_ID)

REFERENCES Basis(Basis_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION,

FOREIGN KEY (Rec_Author_ID)

REFERENCES Author(Author_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION

)ENGINE=InnoDB;

Relation

ALTER TABLE Composition

ADD FOREIGN KEY (Comp_Recipe_ID)

REFERENCES Recipe(Recipe_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

ALTER TABLE Reference

ADD FOREIGN KEY (Ref_Recipe_ID)

REFERENCES Recipe(Recipe_ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

Запросы

база данные кулинарный книга

Запрос 1.

Вывести все рецепты, написанные пользователями, у которых 5 отзывов и метод приготовления ='вареное'

SQL код:

SELECT Recipe_ID, Recipe_name, Method_name, count(Recipe_ID=Ref_recipe_ID)

FROM Recipe

JOIN Cooking_method ON Rec_cooking_method_ID=Cooking_method_ID

JOIN Reference ON Recipe_ID=Ref_Recipe_ID

where method_name='Вареное'

Group by Recipe_ID

having count(Recipe_ID=Ref_recipe_ID)=5;

Результат:

Recipe_ID	Recipe_name	Method_name	count(Recipe_Id=Ref_recipe_ID
22	Зпыщецжпгъщ	Вареное	5
31	Юкогохигыпч	Вареное	5

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Cooking_method	ALL	PRIMARY	NULL	NULL	NULL	4	Using where; Using temporary; Using filesort
1	SIMPLE	Recipe	ref	Primary, Rec_cooking_method_ind	Rec_Cooking_method	4	recipe_book.Cooking_ method_ID	119
1	SIMPLE	Reference	ref	Ref_Recipe_ind	Ref_Recipe_ind	4	recipe_book.Recipe_ID	1	Using index

Интерпретация:

Объединяются таблицы: метод приготовления, рецепт, отзыв. Затем выбираются рецепты, в которых метод приготовления =вареному. Далее выбираются рецепты с числом отзывов=5.

Запрос 2

Вывести все рецепты, взятые из книги = «Рпысндън», в которой одновременно присутствуют 2 ингредиента: мороженое сливочное И шпроты, количество соли <10 граммов.

SQL код:

SELECT Recipe_ID, Recipe_name, Book_ID, Title, Ingredient_ID, Quantity

FROM Recipe

JOIN Book ON Rec_book_ID=Book_ID

JOIN Composition ON Recipe_ID=Comp_recipe_ID

JOIN Ingredient ON Comp_Ingredient_ID=Ingredient_ID

JOIN Unit_measure ON Comp_Unit_measure_ID=Unit_measure_ID

where (book_ID=4)

AND (comp_Ingredient_ID=343 AND Quantity<10 AND Unit_measure_ID=5)

AND Recipe_ID IN (

SELECT Recipe_ID

FROM Composition

JOIN Recipe ON Recipe_ID=Comp_Recipe_ID

Where Comp_Ingredient_ID=210 OR Comp_Ingredient_ID=439

GROUP BY Recipe_ID

having count(Comp_ingredient_ID)=2

ORDER BY Recipe_ID)

Group by Recipe_ID;

Результат:

recipe_ID	Recipe_name	Book_ID	Title	Ingredient_ID	Quantity
1	Роцъчрлщбшч	Рпасндън	Рпысндън	343	3

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	Book	const	PRIMARY	PRIMARY	4	const	1	Using temporary; Using filesort
1	PRIMARY	Ingredient	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	Unit_measure	const	PRIMARY	PRIMARY	4	const	1	Using index
1	PRIMARY	Composition	index_merge	Comp_Ingredient_ind, Comp_Recipe_ind, Comp_Unit_measure	Comp_Ingedient_ind, Comp_ Unit_measure	4,4	NULL	1	Using intersect(Comp_Ingredient_ind,Comp_Unit_measure
1	PRIMARY	Recipe	eq_ref	PRIMARY, Rec_Book_ind	PRIMARY	4	recipe_book.Composition.Comp_Recipe_ ID	1	Using wher
2	DEPENDENT SUBQUERY	Composition	range	Comp_Ingredient_ind, Comp_recipe_ind	Comp_Ingredient_ind	4	NULL	32	Using where; Using temporary; Using filesort
2	DEPENDENT SUBQUERY	Recipe	eq_ref	PRIMARY	PRIMARY	4	Recipe_book.Compostion.Comp_Recipe_ID	1	using index

Интерпретация:

Объединяются таблицы: книга, ингредиент, единица измерения, состав и рецепт. Выполняется отбор рецептов, которые взяты из данной книги, в которых есть соль и её содержание меньше 10 г. Далее выполняется вложенный запрос, выдающий список рецептов, в которых есть необходимые нам ингредиенты.

Запрос 3

Найти имена пользователей, которые поместили рецепты с минимальным числом ингредиентов

SQL код:

SELECT User_ID, User_name, count(Comp_Ingredient_ID)

FROM Recipe

JOIN Composition ON Recipe_ID=Comp_recipe_ID

JOIN User ON Rec_user_ID=User_ID

WHERE Rec_Author_ID=1

GROUP BY User_ID

having count(Comp_Ingredient_ID)=(Select count(Comp_Ingredient_ID)

FROM Composition

JOIN Recipe ON Comp_Recipe_ID=Recipe_ID

GROUP BY Recipe_ID

ORDER BY count(Comp_Ingredient_ID)

LIMIT 1 ORDER BY User_ID;

Результат:

User_ID	User_name	count(Comp_Ingredient_ID)
37	Джинат	1
95	Афин	1
116	Асмик	1

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	User	index	PRIMARY	PRIMARY	4	NULL	477
1	SIMPLE	Recipe	ref	Primary, Rec_user_ind,Rec_author_ind	Rec_User_ind	4	recipe_book.User_ID	1	Using where
1	SIMPLE	Composition	ref	Comp_Recipe_ind	Comp_Recipe_ind	4	recipe_book.Recipe. Recipe_ID	2
2	SUBQUERY	Recipe	index	PRIMARY	PRIMARY	4	NULL	953	Using index; Using temporary; Using filesort
2	SUBQUERY	Composition	ref	Comp_Recipe_ind	Comp_Recipe_ind		recipe_book.Recipe. Recipe_ID	2

Интерпретация:

Объединяются таблицы: Пользователь, Рецепт, Состав. Выбираются авторские рецепты и запросом having отбираются рецепты с числом ингредиентов равным минимальному.

Запрос 4

Найти имена пользователей, которые оставили максимальное число отзывов на рецепты

SQL код:

SELECT User_ID, Login, User_Surname, User_Name, count(Ref_User_ID)

FROM User

JOIN Reference ON User_ID=Ref_User_ID

GROUP BY User_ID

HAVING count(Ref_user_ID)=(SELECT count(Ref_user_ID)

FROM Reference

JOIN User ON Ref_user_ID=User_ID

GROUP BY User_ID

ORDER BY count(Ref_user_ID) DESC

LIMIT 1);

Результат:

User_ID	Login	User_Surname	User_Name	count(Ref_User_ID)
448	447	Вострова	Виталий	15

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	User	index	PRIMARY	PRIMARY	4	NULL	477
1	PRIMARY	Reference	ref	User_ind	User_ind	4	recipe_book.User.User_ID	3	Using index
2	SUBQUERY	User	index	PRIMARY	PRIMARY	4	NULL	477	Using index; Using temporary; Using filesort
2	SUBQUERY	Reference	ref	User_ind	User_ind	4	recipe_book.User.Recipe_ID	3	Using index

Интерпретация:

Объединяются таблицы: Пользователь и отзыв. В having отбираются пользователи с максимальным числом оставленных пользователей.

Запрос 5

Посчитать количество рецептов с одинаковым числом ингредиентов

SQL код:

CREATE TEMPORARY TABLE same_rec_count(

number_ID int (11) unsigned,C_count int (11) unsigned

);

INSERT INTO same_rec_count SELECT Recipe_ID,

count(Comp_ingredient_ID)

FROM Composition

JOIN Recipe ON Recipe_ID=Comp_recipe_ID

GROUP BY Recipe_ID

ORDER BY count(Comp_ingredient_ID);

SELECT C_count, count(number_ID)

FROM same_rec_count

GROUP BY C_count;

Результат:

C_count	count(number_ID)
1	186
2	198
3	206
4	212
5	190
6	220
7	194
8	182
9	200
10	212

Запрос 6

Построить график распределения рецептов по книгам, посчитать теоретическую/практическую MX, DX,

SQL код:

SELECT Book_ID,count(Recipe_ID)

FROM Recipe

JOIN Book ON Rec_book_ID=Book_ID

WHERE Rec_author_ID=2

GROUP BY Book_ID

ORDER BY Book_ID;

Результат:

1 9 2 17 3 7 4 6 5 6 6 12 7 8 …		MXтеор=10 MХпракт=10.54 Теоретически, из книг должно быть 500 рецептов, практически 527 527/50=10.54=>распределение равномерное DXпракт=11,85

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Book	index	PRIMARY	PRIMARY	4	NULL	50	Using index
1	SIMPLE	Recipe	ref	Primary, Rec_Book_ind,Rec_ author_ind	Rec_Book_ind	4	recipe_book.Book.Book_ID	9	Using where

Объединяются таблицы: Пользователь, Рецепт, Состав. Выбираются авторские рецепты и запросом having отбираются рецептыс числом ингредиентов равным минимальному.

Запрос 7

7.1 Посчитать число отзывов пользователей на рецепты из книг, число отзывов на рецепты не из книг

SQL код:

SELECT count(Reference_ID)

From Reference

JOIN Recipe ON Ref_recipe_ID=Recipe_ID

where Rec_author_ID=2;

SELECT count(Reference_ID)

From Reference

JOIN Recipe ON Ref_recipe_ID=Recipe_ID

where Rec_author_ID=1;

Результат:

Из книг count(Reference_ID) 1605	От пользователя count(Reference_ID) 1418

Проверка: Select count(Reference_ID) FROM Reference;

count(Reference_ID) 3023	605+1418=3023

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Recipe	ref	PRIMARY,Re_Author_ind	Rec_Author_ind	4	const	476	Using index
1	SIMPLE	Reference	ref	Ref_Recipe_ind	Ref_Recipe_ind	4	recipe_book.Recipe.Recipe_ID	1	Using where

7.2 Посчитать число пользователей, которые оставили отзывы на рецепты по категориям.

SQL код:

SELECT Category_ID, Category_name, count(User_ID)

FROM User

JOIN Reference ON User_ID=Ref_user_ID

JOIN Recipe ON Ref_recipe_ID=Recipe_ID

JOIN Category ON Category_ID=Rec_category_ID

GROUP BY Category_ID;

Результат:

Category_ID	Category_name	Count(User_ID)
1	закуска	275
2	суп	260
3	салат	332
4	десерт	325
5	пицца	390
6	второе блюдо	366
7	гарнир	349
8	каша	304
9	напитки	322

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Category	index	PRIMARY	PRIMARY	4	NULL	9
1	SIMPLE	Recipe	ref	Primary, Rec_Category_ind	Rec_Category_ind	4	recipe_book.Category.Category_ID	52	Using index
1	SIMPLE	Reference	ref	user_ind,Ref_Recipe_ind	Ref_Recipe_ind	4	recipe_book.Recipe.Recipe_ID	1
1	SIMPLE	User	eq_ref	PRIMARY	PRIMARY	4	recipe_book.Reference.Ref_User_ID	1	Using index

Интерпретация:

Объединяются таблицы: Категории, Рецепт, Отзыв и Пользователь. Рецепты группируются по категориям и ведется подсчет рецептов в каждой группе.

Запрос 8

Посчитать количество рецептов по каждой кухне и по каждой категории

SQL код:

explain SELECT Cuisine_name, Category_name, count(Recipe_ID)

FROM Recipe

JOIN Cuisine ON Cuisine_ID=Rec_cuisine_ID

JOIN Category ON Category_ID=Rec_category_ID

Group by Cuisine_ID,Category_ID;

Результат:

Cuisine_name	Category_name	count(Recipe_ID)
Европейская кухня	закуска	21
Европейская кухня	суп	22
Европейская кухня	салат	24

Эффективность выполнения запроса:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Cuisine	ALL	PRIMARY	NULL	NULL	NULL	5	Using temporary; Using filesort
1	SIMPLE	Recipe	ref	Rec_Cuisine_ind,Rec_Category_ind	Rec_Cuisine_ind	4	recipe_book.Cuisine.Cuisine_ID	95
1	SIMPLE	Category	eq_ref	PRIMARY	NULL	4	recipe_book.Recipe.Rec_Category_ID	1

Интерпретация:

Объединяются таблицы: Пользователь, Рецепт, Состав. Выбираются авторские рецепты и запросом having отбираются рецептыс числом ингредиентов равным минимальному.

Заключение

В результате выполнения данной курсовой работы была разработана и создана база данных, содержащая кулинарные рецепты. В базу данных были занесены названия ингредиентов, единицы измерений, состояние ингредиентов, категории, названия национальных кухонь, метод приготовления, основа блюда. Остальные таблицы заполнялись равномерно распределенными данными. После чего были выполнены SQL запросы. Из полученных результатов и построенных по ним графиков видно, что данные действительно распределены равномерно.

В результате проделанной работы мы получили базу данных, способную заменить книгу кулинарных рецептов. Данная база может быть использована для создания сайта или определенного приложения.

Приложение 1

Программа заполнения базы данных.

Заполнение таблицы «Книга»

#include "stdafx.h"

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>

using namespace std;

struct names

{string s1;

int l;

names *next,*prev;

};

struct surnames

{string s2;

int k;

surnames *next,*prev;

};

class Cname

{private:

ofstream g;

public:

Cname(void);

void create(void);

};

Cname::Cname(void)

{create();

}

void Cname::create(void)

{float c1,h1,t1;

string book_name, author, descr;char c,h,t;

srand(time(NULL));

ofstream g("book.txt");

for(int i=0;i<50;i++)

{author.clear();

book_name.clear();

descr.clear();

c1=rand();

h1=rand();

t1=rand();

c=192+(c1/32767)*31;

h=192+(h1/32767)*31;

t=192+(t1/32767)*31;

author.push_back(c);

book_name.push_back(h);

descr.push_back(t);

for(int j=0; j < 7; j++)

{c1=rand();

h1=rand();

t1=rand();

c=224+(c1/32767)*31;

h=224+(h1/32767)*31;

t=224+(t1/32767)*31;

author.push_back(c);

book_name.push_back(h);

descr.push_back(t);

}

cout<<"Insert into Book SET Book_ID='"<<i+1<<"', Title='"<< book_name<<"', Author='"<<author<<"', Description='"<<descr<<"';\n";

g<<"Insert into Book SET Book_ID='"<<i+1<<"', Title='"<< book_name<<"', Author='"<<author<<"', Description='"<<descr<<"';\n";}

g.close();

}

int main (void)

{setlocale(LC_ALL,"Russian");

Cname g;

_getch();

}

Заполнение таблицы Пользователь

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>

using namespace std;

struct names

{string s1;

int l;

names *next,*prev;

};

struct surnames

{string s2;

int k;

surnames *next,*prev;

};

class Cname

{private:

ofstream f,sur,g;

string a;

names *nnew,*tec,*start;

surnames *nnew_s,*tec_s,*start_s;

int max_names,max_surnames;

public:

Cname(void);

void build_names(void);

void build_surnames(void);

void create(void);

};

Cname::Cname(void)

{max_names=1;

max_surnames=1;

build_names();

build_surnames();

create();

f.close();

sur.close();

g.close();

}

void Cname::build_names(void)

{string s; int i=0;

ifstream f("name.txt");

tec=new names;

tec->prev=NULL;

tec->next=NULL;

start=tec;

while(f.good())

{getline(f,s);

tec->s1=s;

tec->l=i;

i++;

max_names++;

nnew=new names;

nnew->prev=tec;

nnew->next=NULL;

tec->next=nnew;

tec=nnew;

//cout<<s<<"\n";

}

tec=start;

f.close();

}

void Cname::build_surnames(void)

{string s; int i=0;

ifstream sur("surname.txt");

tec_s=new surnames;

tec_s->prev=NULL;

tec_s->next=NULL;

start_s=tec_s;

while(sur.good())

{getline(sur,s);

tec_s->s2=s;

tec_s->k=i;

i++;

max_surnames++;

nnew_s=new surnames;

nnew_s->prev=tec_s;

nnew_s->next=NULL;

tec_s->next=nnew_s;

tec_s=nnew_s;

//cout<<tec_s->s2;

}

tec_s=start_s;

sur.close();

}

void Cname::create(void)

{int c=500,q,w,j,password;double q1,w1,p1;

srand(time(NULL));

ofstream g("zapros_500.txt");

for(int i=0;i<500;i++)

{q1=rand();

q=1+(q1/32767)*(max_names-1);

q1=q1/32767.0;

cout<<q1<<"\n";

w1=rand();

w=1+(w1/32767)*(max_surnames-1);

password=rand();

//cout<<q<<" "<<w<<" "<<password<<endl;

tec=start;

tec_s=start_s;

for(j=0;j<q;j++)

tec=tec->next;

for(j=0;j<w;j++)

tec_s=tec_s->next;

//cout<<"Insert into User SET User_name='"<<tec->s1<<"', User_surnames='"<<tec_s->s2<<"', Login='"<<i<<"', Password='"<<password<<"', User_ID="<<i+1<<";\n";

g<<"Insert into User SET User_name='"<<tec->s1<<"', User_surname='"<<tec_s->s2<<"', Login='"<<i<<"', Password='"<<password<<"', User_ID="<<i+1<<";\n";

}

g.close();

}

int main (void)

{setlocale(LC_ALL,"Russian");

Cname g;

_getch();

}

Заполнение таблицы Состав

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>

#include <math.h>

using namespace std;

class Ccomp

{private:

ofstream g;

public:

Ccomp(void);

void create(void);

};

Ccomp::Ccomp(void)

{create();

};

void Ccomp::create(void)

{double num,ingr,cond,measure,quant;

int comp_ingr, comp_num, comp_unit, comp_cond,quantity;

srand(time(NULL));

ofstream g("6_composition.sql");

for(int i=0;i<1000;i++)

{num=(rand()/double(32767));

comp_num=1+num*10;

for(int j=0;j<comp_num;j++)

{ingr=rand();

comp_ingr=1+(ingr/double(32767))*452;

cond=rand();

comp_cond=1+(cond/double(32767))*56;

measure=rand();

comp_unit=1+(measure/double(32767))*14;

quant=rand();

quantity=1+(quant/double(32767))*20;

g<<"Insert into Composition SET Comp_Ingredient_ID="<<comp_ingr<<", Comp_Recipe_ID="<<i+1<<", Comp_Unit_measure_ID="<<comp_unit<<", Comp_Condition_ID="<<comp_cond<<", Quantity="<<quantity<<";\n";

}

}

g.close();

}

int main (void)

{setlocale(LC_ALL,"Russian");

Ccomp g;

_getch();

}

Заполнение таблицы Отзыв

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>

using namespace std;

class Cref

{private:

ofstream g;

public:

Cref(void);

void create(void);

};

Cref::Cref(void)

{create();

}

void Cref::create(void)

{double num,user,simv;

int ref_num,user_id,y,m,d,h,min,s;

char c;

string message;

srand(time(NULL));

ofstream g("8_reference.sql");

for(int i=0;i<1000;i++)

{num=rand();

ref_num=1+(num/32767)*5;

for(int j=0;j<ref_num;j++)

{user=rand();

user_id=1+(user/double(32767))*500;

simv=rand();

c=192+(simv/double(32767.0))*31;

message.clear();

message.push_back(c);

for(int k=0;k<20;k++)

{simv=rand();

c=224+(simv/double(32767.0))*31;

message.push_back(c);

}

y=2008+rand()/(32767.0)*3;m=rand()/(double(32767))*12;d=1+rand()/(double(32767))*30;h=rand()/(double(32767))*24;min=rand()/(32767.0)*60;s=rand()/(double(32767))*60;

g<<"Insert into Reference SET Ref_User_ID="<<user_id<<", Ref_Recipe_ID="<<i+1<<", Message='"<<message<<"', Date='"<<y<<"-"<<m<<"-"<<d<<" "<<h<<":"<<min<<":"<<s<<"';\n";

}

}

g.close();

}

int main (void)

{setlocale(LC_ALL,"Russian");

Cref g;

_getch();

}

Приложение 2

Данные заполнения словарей

Кухня

Европейская кухня

Русская кухня

Японская кухня

Американская кухня

Китайская кухня

Категория

закуска

суп

салат

десерт

пицца

второе блюдо

гарнир

каша

напитки

Автор

авторское

из книги

Основа

мясное

рыбное

овощное

Метод приготовления

Вареное

Тушеное

Жареное

Сырое

Единица измерения

щепотка зубчик мл л г ложка/ложек штука/штук	пакетик упаковка кубик/кубиков картофелин ломтик кг зонтик банка/банок

Состояние

размороженное замороженное свежепросольное свежее консервированный соленое маринованное	засоленное обезжиренное нежареный классическое перченое г/копчения х/копчения

Ингредиенты

абрикосы авокадо айва алыча американский орех ананас апельсины арахис арбуз артишоки аспарагус атлантическая треска	Баклажаны бананы баранина баранки батончики на гидрожире белая капуста белая фасоль белые грибы бобы брокколи брусника брынза коровья

Приложение 3

Примеры заполнения базы данных

Таблица «Национальная кухня»

INSERT INTO Cuisine SET Cuisine_name='Европейская кухня', Cuisine_ID=1;

INSERT INTO Cuisine SET Cuisine_name='Русская кухня', Cuisine_ID=2;

Таблица «Категория»

Insert into Category SET Category_name='закуска', Category_ID=1;

Insert into Category SET Category_name='суп', Category_ID=2;

Таблица «Основа»

INSERT INTO Basis SET Basis_name='мясное', Basis_ID=1;

INSERT INTO Basis SET Basis_name='рыбное', Basis_ID=2;

Таблица «Метод приготовления»

INSERT INTO Cooking_method SET Method_name='Вареное', Cooking_method_ID=1;

INSERT INTO Cooking_method SET Method_name='Тушеное', Cooking_method_ID=2;

Таблица «Единица измерения»

INSERT INTO Unit_measure SET Unit_measure_name='щепотка', Unit_measure_ID=1;

INSERT INTO Unit_measure SET Unit_measure_name='зубчик', Unit_measure_ID=2;

Таблица «Состояние»

INSERT INTO Conditions SET Conditions_name='размороженое', Condition_ID=1;

INSERT INTO Conditions SET Conditions_name='замороженое', Condition_ID=2;

Таблица «Книга»

Insert into Book SET Book_ID='1', Title='Мхррецьы', Author='Члуюабюо', Description='Пчигдфеь';

Insert into Book SET Book_ID='2', Title='Цбакючоь', Author='Ювьйдюма', Description='Ьемгммзл';

Таблица «Пользователь»

Insert into User SET User_name='Василиа', User_surname='Шамигулова', Login='0', Password='6712', User_ID=1;

Insert into User SET User_name='Борис', User_surname='Пшенина', Login='1', Password='1273', User_ID=2;

Таблица «Ингредиент»

INSERT INTO Ingredient SET Ingredient_name='абрикосы', Ingredient_ID=1;

INSERT INTO Ingredient SET Ingredient_name='авокадо', Ingredient_ID=2;

Таблица «Состав»

Insert into Composition SET Comp_Ingredient_ID=343, Comp_Recipe_ID=1, Comp_Unit_measure_ID=5, Comp_Condition_ID=18, Quantity=3;

Insert into Composition SET Comp_Ingredient_ID=210, Comp_Recipe_ID=1, Comp_Unit_measure_ID=13, Comp_Condition_ID=25, Quantity=7;

Таблица «Рецепт»

Insert into Recipe SET Recipe_ID=1, Rec_Cuisine_ID=1, Rec_Category_ID=5, Rec_Cooking_method_ID=2, Rec_Book_ID=4, Rec_User_ID=258, Description_cooking_method='Ъцэцэшъуиьзтцппебцичъъмжчгутечогьтулиюспэ', Recipe_name='Роцъхрлщбшч', Rec_author_ID=1, Caloric_content='168', Dish_weight='бийхф', Rec_Basis_ID='2';

Insert into Recipe SET Recipe_ID=2, Rec_Cuisine_ID=1, Rec_Category_ID=8, Rec_Cooking_method_ID=4, Rec_Book_ID=18, Rec_User_ID=166, Description_cooking_method='Стуаютгжъчаощпюшэкцруйщхэждэсьыццттебнйый', Recipe_name='Чтьмъвгкюсж', Rec_author_ID=2, Caloric_content='580', Dish_weight='имлгт', Rec_Basis_ID='2';

Таблица «Отзыв»

Insert into Reference SET Ref_User_ID=302, Ref_Recipe_ID=1, Message='Йтичцгыомзывпьрепинии', Date='2009-8-12 9:27:40';

Insert into Reference SET Ref_User_ID=414, Ref_Recipe_ID=1, Message='Гдсхъьрлбмыпэыцщнетнп', Date='2009-11-19 22:11:35';

Размещено на Allbest.ru