Курсовая_ОТЧЁТ
.docxМИНОБРНАУКИ РОССИИ
федеральное государственное автономное образовательное учреждение высшего образования «Санкт-Петербургский государственный электротехнический университет «ЛЭТИ» им. В.И. Ульянова (Ленина)»
(СПбГЭТУ «ЛЭТИ»)
Кафедра Информационных систем
ОТЧЁТ
по курсовой работе
по дисциплине «Теория баз данных»
Тема: разработка базы данных информационной системы.
Студенты гр. 9373 |
|
Петрова С.В., Заболотников М.Е. |
Преподаватель |
|
Савосин С.В. |
Санкт-Петербург
2020
Цель работы.
Создание базы данных, предназначенной для отдела питания санатория. В ней должна храниться информация о блюдах, продуктах, из которых блюда состоят, наличии продуктов на складе, а также о меню санатория на каждый день (план питания).
Ход работы.
Исходя из анализа сферы деятельности, по которой будет строиться база данных, были выделены основные сущности, такие как: блюда, продукты, меню, склады и накладная. Данные сущности были представлены в виде таблиц с соответствующими названиями. Далее более подробно о содержании этих таблиц.
Таблица «Dishes», которая воплощает сущность «блюда», содержит первичный ключ id, наименование блюда, весовую долю жиров, белков и углеводов (относительно веса порции), а также вес одной порции и её калорийность (также относительно её веса):
Рис. 1. Таблица «Dishes»
Сущность «продукты» воплощает таблица «Products». Таблица имеет такие поля, как: id (первичный ключ), наименование продукта и единицу измерения его количества:
Рис. 2. Таблица «Products»
Таблица «Menu», воплощающая сущность «меню», содержит первичный ключ id и точную дату его использования:
Рис. 3. Таблица «Menu»
За сущность «склады» отвечает таблица «Storage» и включает в себя id склада (первичный ключ) и его наименование. Под наименованием подразумевается назначение того или иного склада:
Рис. 4. Таблица «Storage»
В таблице «Waybill», воплощающей сущность «накладная», содержится первичный ключ id накладной (номер документа), дата оформления документа, id склада, на который были завезены продукты, контрагент и общая сумма продуктов по накладной:
Рис. 5. Таблица «Waybill»
Так как нужно как-то взаимодействовать с данными, представленными в вышеперечисленных таблицах, необходимо было создать некоторые связи между ними. В результате получилось выявить связи «один-ко-многим» и «многие-ко-многим». Чтобы осуществить получившиеся связи «многие-ко-многим», были созданы так называемые таблицы-посредники. Теперь об их содержании.
Таблицы «Dishes» и «Products» имеют связь «многие-ко-многим», так как понятно, что в составе одного блюда может быть несколько продуктов, так же как и один продукт может входить в состав нескольких блюд одновременно. Для реализации связи между этими таблицами была создана таблица-посредник «Dish_product». Эта таблица показывает, сколько и какого продукта содержится в составе того или иного блюда: поля id_dish и id_prod отвечают за блюдо и продукт соответственно и являются внешними ключами
Рис. 6. Таблица-посредник «Dish_product»
Список блюд на очередной период времени фиксируется в меню, поэтому необходимо было связать таблицы «Dishes» и «Menu». Эти таблицы тоже имеют связь «многие-ко-многим», так как одно блюдо может включаться в меню на разные дни и меню на один день состоит из нескольких блюд: на завтрак, на обед и на ужин. В связи с этим создана таблица посредник «Menu_cont»:
Рис. 7. Таблица-посредник «Menu_cont»
В данной таблице поля id_dish и id_menu являются внешними ключами и ссылаются на поля «id_dish» и «id_menu» соответственно в таблицах «Dishes» и «Menu».
В накладных хранится информация о том, какие продукты и в каком количестве были привезены на склады, а также информация о том складе, на который продукты были завезены. С таблицей «Products» таблица «Waybill» также имеет связь «многие-ко-многим», потому что в одной накладной может перечисляться какое-то количество продуктов, но и один продукт может упоминаться в нескольких накладных. Вследствие этого была создана таблица-посредник «Waybill_prod»:
Рис. 8. Таблица-посредник «Waybill_prod»
Продукты необходимо хранить на складах. Поэтому были связаны таблицы «Products» и «Storage». Связь – «многие-ко-многим», так как один продукт может храниться на разных складах, а на одном складе, как правило, хранится несколько продуктов. Получилась таблица-посредник «Storage_product»:
Рис. 9. Таблица-посредник «Storage_product».
Ну и последняя связь – связь между таблицами «Waybill» и «Storage». Так как одна накладная отвечает за один склад, но на один склад могут поступать продукты по нескольким накладным, то данная связь – «один-ко-многим».
Рис. 10. Иллюстрация связи таблиц «Waybill» и «Storage».
В итоге получилась общая схема базы данных. ER-диаграмма данной базы данных представлена в виде схемы, построенной в компьютерной программе «SQL Power Architect»:
Рис. 11. ER-диаграмма полученной базы данных
После построения диаграммы БД все таблицы были заполнены данными. Чтобы показать, что с такой базой можно работать, были сделаны запросы, позволяющие получить какую-то информацию, используя данные таблиц. Чтобы это сделать, аналогичная база данных была построена в программе «DataGrip 2020.2.3 x64». Там же и прописывался код для запросов. Для заполнения БД, собственно, самими данными, использовался приложенный к настоящему отчёту документ – листинг данных. Далее об этих запросах.
Вывести название блюд на определённый день (попросту говоря, вывести состав меню на один день:
select name from menu_cont left join Menu as m on m.id = menu_cont.id_menu left join dishes as d on menu_cont.id_dish = d.id where m.date = '2020-02-11';
Результатом данного запроса является состав меню на 11-е февраля 2020-го года:
Рис. 12. Результат выполнения первого запроса
Вывести среднее количество различных блюд, подаваемых за один день:
select avg(count_dish) from ( select count(id_dish) as count_dish from Menu_cont group by Menu_cont.id_menu ) as x;
Результат запроса:
Рис. 13. Результат выполнения второго запроса
Действительно, в среднем за один день подаётся три различных вида блюд: на завтрак один, на обед – другой, и на ужин – третий.
Название блюда, встречающегося чаще всего за определённый период времени. В нашем случае будем считать этим периодом неделю:
select name from ( select id_dish, count(id_menu) as a from menu_cont as mc group by id_dish ) as y left join dishes as d on d.id = id_dish where a = (select max(a) from ( select count(id_menu) as a from menu_cont as mc group by id_dish ) as z);
Результат:
Рис. 14. Результат выполнения третьего запроса
Действительно, за всю неделю все блюда встречались лишь по два раза и лишь сырники со сгущёнкой выдавались три раза.
Количество блюд, порций которых на определённый день нужно приготовить меньше 100 (в данном примере берётся 14-е февраля 2020-го года):
select count(case when q.date = '2020-02-14' and q.portion_amount < 100 then '1' end) from (select date, portion_amount from menu_cont as mc left join menu as m on m.id = mc.id_menu) as q;
Результат:
Рис. 15. Результат выполнения четвёртого запроса
Результат верен, так как каждого блюда 14-го февраля 2020-го года необходимо приготовить в количестве 40 порций.
Вывести называние продукта и его количество, необходимое на неделю (в граммах). Это полезно знать, чтобы продуктов хватило на приготовление необходимого количества блюд:
select name, sum(prod_amount * portion_amount) from dish_product as dp left join menu_cont as mc on dp.id_dish = mc.id_dish left join products as p on dp.id_prod = p.id where id_menu between 1 and 7 group by name;
Результат:
Рис. 16. Результат выполнения пятого запроса.
Результат запроса верен. Это можно проверить, произведя соответствующие расчёты вручную.
Выводы.
В ходе выполнения данной работы были освоены навыки создания простой базы данных, а также изучены способы взаимодействия с информацией, представленной в БД посредством написания запросов. Изучены такие понятия, как сущности, связи, типы связей и т.д. Также была построена ER-диаграмма БД в «SQL Power Architect», а таблицы – заполнены данными в программе «DataGrip 2020.2.3 x64». Для проверки взаимодействия с данными базы были написаны запросы и получены результаты этих запросов.