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

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

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

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

Выполнил:

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

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

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

Попов С.Г.

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

Оглавление

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

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

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

ER-диаграмма

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

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

Запросы

Заключение

Приложения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

а) Завтрак

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			первичный ключ
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;database Recipe_book;Recipe_book database;

Cuisine

create table Cuisine(_ID int NOT NULL auto_increment PRIMARY KEY,_name char(60) default NULL)ENGINE=InnoDB;

Category

create table Category(_ID int NOT NULL auto_increment PRIMARY KEY,_name char(50) default NULL)ENGINE=InnoDB;

Cooking_method

create table Cooking_method(_method_ID int NOT NULL auto_increment PRIMARY KEY,_name char(50) default NULL)ENGINE=InnoDB;

Conditions

create table Conditions(_ID int NOT NULL auto_increment PRIMARY KEY,_name char(50) default NULL)ENGINE=InnoDB;

Unit_measure

create table Unit_measure(_measure_ID int NOT NULL auto_increment PRIMARY KEY,_measure_name char(50) default NULL)ENGINE=InnoDB;

Basis

create table Basis(_ID int NOT NULL auto_increment PRIMARY KEY,_name char(50) default NULL)ENGINE=InnoDB;

Author

create table Author(_ID int NOT NULL auto_increment PRIMARY KEY,char(50) default NULL)ENGINE=InnoDB;

Ingredient

create table Ingredient(_ID int NOT NULL auto_increment PRIMARY KEY,_name char(50) default NULL)ENGINE=InnoDB;

User

create table User(_ID int NOT NULL auto_increment PRIMARY KEY,_name char(50) default NULL,_surname char(50) default NULL,char(50) default NULL,char(50) default NULL)ENGINE=InnoDB;

Reference

create table Reference(_ID int NOT NULL auto_increment PRIMARY KEY,_User_ID int NOT NULL,user_ind (Ref_User_ID),_Recipe_ID int NOT NULL,Ref_Recipe_ind (Ref_Recipe_ID),tinytext default NULL,datetime default NULL,KEY (Ref_User_ID)User(User_ID)DELETE NO ACTIONUPDATE NO ACTION)ENGINE=InnoDB;

Composition

create table Composition(_ID int NOT NULL auto_increment PRIMARY KEY,_Ingredient_ID int NOT NULL,Comp_Ingredient_ind (Comp_Ingredient_ID),_Recipe_ID int NOT NULL,Comp_Recipe_ind (Comp_Recipe_ID),_Unit_measure_ID int NOT NULL,Comp_Unit_measure (Comp_unit_measure_ID),_Condition_ID int NOT NULL,Comp_Condition_ind (Comp_Condition_ID),int default NULL,KEY (Comp_Ingredient_ID)Ingredient(Ingredient_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Comp_Unit_measure_ID)Unit_measure(Unit_measure_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Comp_Condition_ID)Conditions(Condition_ID)DELETE NO ACTIONUPDATE NO ACTION

)ENGINE=InnoDB;

Book

create table Book(_ID int NOT NULL auto_increment PRIMARY KEY,char(50) default NULL,char(50) default NULL,tinytext default NULL)ENGINE=InnoDB;

Recipe

create table Recipe(_ID int NOT NULL auto_increment PRIMARY KEY,_Cuisine_ID int NOT NULL,Rec_Cuisine_ind (Rec_Cuisine_ID),_Category_ID int NOT NULL,Rec_Category_ind (Rec_Category_ID),_Cooking_method_ID int NOT NULL,Rec_Cooking_method_ind (Rec_Cooking_method_ID),_User_ID int NOT NULL,Rec_User_ind (Rec_User_ID),_Book_ID int NOT NULL,Rec_Book_ind (Rec_Book_ID),_Basis_ID int NOT NULL,Rec_Basis_ind (Rec_Basis_ID),_Author_ID int NOT NULL,Rec_Author_ind (Rec_Author_ID),_cooking_method tinytext default NULL,_name char(100) default NULL,_content int default NULL,_weight char(100) default NULL,KEY (Rec_Cuisine_ID)Cuisine(Cuisine_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Rec_Category_ID)Category(Category_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Rec_Cooking_method_ID)Cooking_method(Cooking_method_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Rec_User_ID)User(User_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Rec_Book_ID)Book(Book_ID)DELETE NO ACTIONUPDATE NO ACTION,KEY (Rec_Basis_ID)Basis(Basis_ID)DELETE NO ACTIONUPDATE NO ACTION,

FOREIGN KEY (Rec_Author_ID)Author(Author_ID)DELETE NO ACTIONUPDATE NO ACTION

)ENGINE=InnoDB;

Relation

ALTER TABLE CompositionFOREIGN KEY (Comp_Recipe_ID)Recipe(Recipe_ID)DELETE NO ACTIONUPDATE NO ACTION;TABLE ReferenceFOREIGN KEY (Ref_Recipe_ID)Recipe(Recipe_ID)DELETE NO ACTIONUPDATE NO ACTION;

Запросы

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

Запрос 1.

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

SQL код:

SELECT Recipe_ID, Recipe_name, Method_name, count(Recipe_ID=Ref_recipe_ID)RecipeCooking_method ON Rec_cooking_method_ID=Cooking_method_IDReference ON Recipe_ID=Ref_Recipe_IDmethod_name='Вареное'by Recipe_IDcount(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, QuantityRecipeBook ON Rec_book_ID=Book_IDComposition ON Recipe_ID=Comp_recipe_IDIngredient ON Comp_Ingredient_ID=Ingredient_IDUnit_measure ON Comp_Unit_measure_ID=Unit_measure_ID(book_ID=4)(comp_Ingredient_ID=343 AND Quantity<10 AND Unit_measure_ID=5)Recipe_ID IN (Recipe_IDCompositionRecipe ON Recipe_ID=Comp_Recipe_IDComp_Ingredient_ID=210 OR Comp_Ingredient_ID=439BY Recipe_IDcount(Comp_ingredient_ID)=2BY Recipe_ID)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)RecipeComposition ON Recipe_ID=Comp_recipe_IDUser ON Rec_user_ID=User_IDRec_Author_ID=1BY User_IDcount(Comp_Ingredient_ID)=(Select count(Comp_Ingredient_ID)CompositionRecipe ON Comp_Recipe_ID=Recipe_IDBY Recipe_IDBY count(Comp_Ingredient_ID)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		recipe_book.Recipe. Recipe_ID	2

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

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

Запрос 4

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

SQL код:

SELECT User_ID, Login, User_Surname, User_Name, count(Ref_User_ID)UserReference ON User_ID=Ref_User_IDBY User_IDcount(Ref_user_ID)=(SELECT count(Ref_user_ID)ReferenceUser ON Ref_user_ID=User_IDBY User_IDBY 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(_ID int (11) unsigned,C_count int (11) unsigned

);INTO same_rec_count SELECT Recipe_ID,(Comp_ingredient_ID)CompositionRecipe ON Recipe_ID=Comp_recipe_IDBY Recipe_IDBY count(Comp_ingredient_ID);C_count, count(number_ID)same_rec_countBY 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)RecipeBook ON Rec_book_ID=Book_IDRec_author_ID=2BY Book_IDBY 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)ReferenceRecipe ON Ref_recipe_ID=Recipe_ID

where Rec_author_ID=2;count(Reference_ID)ReferenceRecipe ON Ref_recipe_ID=Recipe_IDRec_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)UserReference ON User_ID=Ref_user_IDRecipe ON Ref_recipe_ID=Recipe_IDCategory ON Category_ID=Rec_category_IDBY 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)RecipeCuisine ON Cuisine_ID=Rec_cuisine_IDCategory ON Category_ID=Rec_category_IDby 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>namespace std;names

{string s1;l;*next,*prev;

};surnames

{string s2;k;*next,*prev;

};Cname

{private:g;:(void);create(void);

};::Cname(void)

{create();

}Cname::create(void)

{float c1,h1,t1;book_name, author, descr;char c,h,t;(time(NULL));g("book.txt");(int i=0;i<50;i++)

{author.clear();_name.clear();.clear();

author.push_back(c);_name.push_back(h);.push_back(t);(int j=0; j < 7; j++)

{c1=rand();=rand();=rand();=224+(c1/32767)*31;=224+(h1/32767)*31;=224+(t1/32767)*31;

author.push_back(c);_name.push_back(h);.push_back(t);

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

}main (void)

{setlocale(LC_ALL,"Russian");g;

_getch();

}

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

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>namespace std;names

{string s1;l;*next,*prev;

};surnames

{string s2;k;*next,*prev;

};Cname

{private:f,sur,g;a;*nnew,*tec,*start;*nnew_s,*tec_s,*start_s;

int max_names,max_surnames;

public:(void);build_names(void);build_surnames(void);create(void);

};::Cname(void)

{max_names=1;_surnames=1;_names();_surnames();();.close();.close();.close();

}Cname::build_names(void)

{string s; int i=0;f("name.txt");=new names;>prev=NULL;>next=NULL;=tec;(f.good())

{getline(f,s);>s1=s;>l=i;++;_names++;=new names;>prev=tec;>next=NULL;>next=nnew;=nnew;

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

}=start;.close();

}Cname::build_surnames(void)

{string s; int i=0;sur("surname.txt");_s=new surnames;_s->prev=NULL;_s->next=NULL;_s=tec_s;(sur.good())

{getline(sur,s);_s->s2=s;_s->k=i;++;_surnames++;_s=new surnames;_s->prev=tec_s;_s->next=NULL;_s->next=nnew_s;_s=nnew_s;

//cout<<tec_s->s2;

}_s=start_s;.close();

}Cname::create(void)

{int c=500,q,w,j,password;double q1,w1,p1;(time(NULL));g("zapros_500.txt");(int i=0;i<500;i++)

{q1=rand();=1+(q1/32767)*(max_names-1);=q1/32767.0;

cout<<q1<<"\n";=rand();

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

//cout<<q<<" "<<w<<" "<<password<<endl;=start;_s=start_s;(j=0;j<q;j++)=tec->next;(j=0;j<w;j++)_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";<<"Insert into User SET User_name='"<<tec->s1<<"', User_surname='"<<tec_s->s2<<"', Login='"<<i<<"', Password='"<<password<<"', User_ID="<<i+1<<";\n";

}.close();

}main (void)

{setlocale(LC_ALL,"Russian");g;

_getch();

}

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

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>

#include <math.h>namespace std;Ccomp

{private:g;:(void);create(void);

};::Ccomp(void)

{create();

};Ccomp::create(void)

{double num,ingr,cond,measure,quant;comp_ingr, comp_num, comp_unit, comp_cond,quantity;(time(NULL));g("6_composition.sql");(int i=0;i<1000;i++)

{num=(rand()/double(32767));_num=1+num*10;(int j=0;j<comp_num;j++)

{ingr=rand();_ingr=1+(ingr/double(32767))*452;=rand();_cond=1+(cond/double(32767))*56;=rand();_unit=1+(measure/double(32767))*14;=rand();=1+(quant/double(32767))*20;<<"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";

}

}.close();

}main (void)

{setlocale(LC_ALL,"Russian");g;

_getch();

}

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

#include <iostream>

#include <conio.h>

#include <stdio.h>

#include <time.h>

#include <fstream>

#include <string>namespace std;Cref

{private:g;:(void);create(void);

};::Cref(void)

{create();

}Cref::create(void)

{double num,user,simv;ref_num,user_id,y,m,d,h,min,s;c;message;(time(NULL));g("8_reference.sql");(int i=0;i<1000;i++)

{num=rand();_num=1+(num/32767)*5;

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

{user=rand();_id=1+(user/double(32767))*500;=rand();=192+(simv/double(32767.0))*31;.clear();.push_back(c);(int k=0;k<20;k++)

{simv=rand();=224+(simv/double(32767.0))*31;.push_back(c);

}=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;<<"Insert into Reference SET Ref_User_ID="<<user_id<<", Ref_Recipe_ID="<<i+1<<", Message='"<<message<<"', Date='"<<y<<"-"<<m<<"-"<<d<<" "<<h<<":"<<min<<":"<<s<<"';\n";

}

}.close();

}main (void)

{setlocale(LC_ALL,"Russian");g;

_getch();

}

Приложение 2

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

Кухня

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

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

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

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

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

Категория

закуска

суп

салат

десерт

пицца

второе блюдо

гарнир

каша

напитки

Автор

авторское

из книги

Основа

мясное

рыбное

овощное

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

Вареное

Тушеное

Жареное

Сырое

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

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

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

Состояние

размороженное замороженное свежепросольное свежее консервированный соленое маринованное

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

Ингредиенты

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

Приложение 3

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

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

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

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

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

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

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

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

INSERT INTO Cooking_method SET Method_name='Вареное', Cooking_method_ID=1;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;INTO Conditions SET Conditions_name='замороженое', Condition_ID=2;

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

Insert into Book SET Book_ID='1', Title='Мхррецьы', Author='Члуюабюо', Description='Пчигдфеь';into Book SET Book_ID='2', Title='Цбакючоь', Author='Ювьйдюма', Description='Ьемгммзл';

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

Insert into User SET User_name='Василиа', User_surname='Шамигулова', Login='0', Password='6712', User_ID=1;into User SET User_name='Борис', User_surname='Пшенина', Login='1', Password='1273', User_ID=2;

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

INSERT INTO Ingredient SET Ingredient_name='абрикосы', Ingredient_ID=1;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;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';