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

Тип работы:
Курсовая
Предмет:
Программирование


Узнать стоимость

Детальная информация о работе

Выдержка из работы

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

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

Выполнил:

студент группы 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/32 767)*31;

h=192+(h1/32 767)*31;

t=192+(t1/32 767)*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/32 767)*31;

h=224+(h1/32 767)*31;

t=224+(t1/32 767)*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 («zapros500. txt»);

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

{q1=rand ();

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

q1=q1/32 767. 0;

cout< <q1<<"n";

w1=rand ();

w=1+(w1/32 767)*(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 (32 767));

comp_num=1+num*10;

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

{ingr=rand ();

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

cond=rand ();

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

measure=rand ();

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

quant=rand ();

quantity=1+(quant/double (32 767))*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/32 767)*5;

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

{user=rand ();

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

simv=rand ();

c=192+(simv/double (32 767. 0))*31;

message. clear ();

message. push_back (c);

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

{simv=rand ();

c=224+(simv/double (32 767. 0))*31;

message. push_back (c);

}

y=2008+rand ()/(32 767. 0)*3;m=rand ()/(double (32 767))*12;d=1+rand ()/(double (32 767))*30;h=rand ()/(double (32 767))*24;min=rand ()/(32 767. 0)*60;s=rand ()/(double (32 767))*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';

ПоказатьСвернуть
Заполнить форму текущей работой