СУБД ПЗ Аврамец. Курсовая работа по дисциплине Базы и хранилища данных Разработка базы данных для предметной области Библиотека
 Скачать 0.54 Mb.
|
|
МИНОБРНАУКИ РОССИИ Федеральное государственное бюджетное образовательное учреждение высшего образования «Челябинский государственный университет» (ФГБОУ ВО «ЧелГУ») Институт информационных технологий Кафедра информационных технологий и экономической информатики КУРСОВАЯ РАБОТА по дисциплине «Базы и хранилища данных» __________«Разработка базы данных для предметной области «Библиотека»_________ (тема) Выполнил студент__ Аврамец С.А. ______ (Ф.И.О.) группы_<номер группы>_____ заочной формы обучения направления подготовки _____<Название направления обучения> ____________________________________ (подпись) «____» ____________ 20___г.
Челябинск 2020 Задание Провести анализ предметной области по следующему описанию: Существует база данных, которая содержит информацию о работе библиотеки. Библиотека состоит из нескольких читальных залов (зал художественной литературы, зал технической литературы, зал иностранной литературы). Библиотекари выдают книги читателям. Необходимо вести учет книг, имеющихся в библиотеке и книг, выданных читателям. Необходимо вести учет посещений библиотеки читателями. Перечень входных (первичных) документов.
Продолжение таблицы:
Ограничения предметной области: Одна книга (Название, Автор, год издания) может быть в нескольких экземплярах. Один читатель не может взять более 5 книг. Один читатель не может взять 2 экземпляра (одинаковым названием, автором, год издания) одной книги. Дата выдачи, дата возврата и дата рождения хранятся в виде DD/MM/YYYY Телефон имеет вид +7 (111) 1765880 Области знаний бывают 3-х типов (любые). Читальных залы бывают 3-х типов (зал художественной литературы, зал технической литературы, зал иностранной литературы). Дата возврата книги не может быть раньше даты получения! Выполнить концептуальное (инфологическое) проектирование. Выполнить даталогическое проектирование для реляционной базы данных. Выбрать СУБД (обосновав выбор) и выполнить физическое проектирование. Создать базу данных в выбранной СУБД с учетом ограничений предметной области. Реализовать следующие отчеты (запросы): Посчитать за каждый месяц года, определенного пользователем, количество выдач книг. Вывести название и возраст книги самой старой книги в каждом из залов. Вывести читальный зал, в котором содержаться книги только заданных пользователем типов (типов при поиске может быть определено несколько) Вывести 5 лучших книг, которые за прошедший месяц пользовались наибольшим спросом. Выбрать язык программирования и разработать приложение для работы с БД (формы ввода/редактирования данных и отчеты). Оформить пояснительную записку. Содержание Задание 2 Содержание 4 Концептуальная модель БД 6 Логическая модель БД 8 Физическая модель БД 12 Описание приложения для работы с БД 15 Заключение 24 Библиография 25 Приложение 1. SQL скрипты 26 Приложение 2. Код веб-приложения 30 Заказчик использует базу данных, которая содержит информацию о работе библиотеки. Библиотека состоит из нескольких читальных залов (зал художественной литературы, зал технической литературы, зал иностранной литературы). Библиотекари выдают книги читателям. Требуемые функции системы, использующей базу данных: Ведение учёта посетителей библиотеки Ведение учёта книг, имеющихся в библиотеке Ведение учёта посещений читателями библиотеки (регистрация выдачи книг на руки и их возврата) База данных проектируется с учётом следующих особенностей: Одна книга (Название, Автор, год издания) может быть в нескольких экземплярах. Один читатель не может взять более 5 книг. Один читатель не может взять 2 экземпляра (одинаковым названием, автором, год издания) одной книги. Дата выдачи, дата возврата и дата рождения хранятся в виде DD/MM/YYYY Телефон имеет вид +7 (111) 1765880 Области знаний бывают 3-х типов (любые). Читальных залы бывают 3-х типов (зал художественной литературы, зал технической литературы, зал иностранной литературы). Дата возврата книги не может быть раньше даты получения! Концептуальная модель БД На этом этапе необходимо выделить основные сущности, атрибуты сущностей и связи между сущностями, а так же построить ER-диаграмму в нотации Питера Чена. Базовые сущности предметной области: Таблица 1. Таблица сущностей
Теперь определим атрибуты для каждой сущности: Таблица 2. Таблица атрибутов
Между сущностями определены следующие связи: Таблица 3. Таблица связей
Подобно тому, как выше рассмотрены атрибуты сущностей, рассмотрим атрибуты связей. Таблица 4. Атрибуты связей
  Рисунок 1. Инфологическая диаграмма На основе приведенного выше описания сущностей и связей составим ER-диаграмму. Логическая модель БД Основная задача данного этапа проектирования - переложить концептуальную модель на реляционную. Для этого нужно определить все информационные единицы и связи между ними, задать их имена, а также некоторые количественные характеристики, например, длину элементов. Отношение представляется как определенным образом организованная таблица. Результатом работы на этом этапе будет ER-диаграмма в нотации «воронья лапка». Перед тем, как превращать модель базы в перечень таблиц в СУБД, необходимо избавиться от избыточности информации и аномалий исходных схем БД. Частично, эта работы была уже проведена на этапе инфологического проектирования - в отдельные сущности были выделены читальный зал и область знаний, которые изначально могли быть представлены в виде атрибутов книги. Составим таблицы, соответствующие сущностям. Таблица 5. readers - таблица с данными о читателях
Таблица 6. types - области знаний
Таблица 7. readrooms - читальные залы
Таблица 8. books - книги
Так как одна книга может находиться только в одном читальном зале, и принадлежать только одной области знаний (а иного не было определено заданием), то нет никакой необходимости выделять связь «книга : область знаний» и связь «книга : читальный зал» в отдельные отношения. В таблице «books» эта связь была отражена сопоставлением соответствующего идентификатора. Все перечисленные выше таблицы находятся в третьей нормальной форме (3НФ), что означает, что каждый атрибут отношения атомарен, неприводимо и нетранзитивно зависит от первичного ключа. Для отношения «читатель : книга» потребуется отдельная таблица, тождественная заявке на выдачу книг, или читательскому требованию в библиотечных терминах. Таблица 9. slips - читательские требования
Здесь следует обратить внимание на то, что поле «books» в таблице противоречит определению первой нормальной формы (1НФ), его значения не атомарны. Для обеспечения его атомарности вынесем соответствие читательского требования и книги в отдельное отношение. Таблица 10. slips_info - информация о соответствии книги читательскому требованию
Эта таблица тоже находится в 3НФ. В результате выделения этих данных в отдельное отношения, таблица «slips» примет следующий вид: Таблица 11. таблица slips после нормализации
И тоже будет находиться в 3НФ, так как потенциальных ключей, кроме ID, в ней не будет, каждый неключевой атрибут будет зависеть только от ключевого. В результате проектирования получится схема, изображенная на рисунке 2.  Рисунок 2. ER-диаграмма в нотации "воронья лапка" Физическая модель БД Физическое проектирование базы данных подразумевает выбор способа хранения и размещения базы на физических носителях, определение формы её хранения и доступа к ней. В современности это обозначает, в основном, лишь выбор систему управления базами данных (СУБД), так как каждая из них определяет по умолчанию свой способ хранения данных. В ряде случаев, однако, этим фактором можно управлять. Например, для данного проекта была выбрана СУБД MySQL 8.0, самая актуальная на сегодня версия. Причиной такого выбора послужила ее распространенность и большое количество справочных материалов и форумов. В этой СУБД можно выбирать способ хранения данных - в виде таблиц InnDB, MyISAM, InMemory, в одном файле на базу или по файлу на каждую таблицу. Для данной БД способ хранения выбран рекомендованный производителем - одна БД - один файл, на движке InnoDB, что открывает богатые возможности резервного копирования, кластеризации, а так же, гибкой работы с доступом к таблицах (исключение взаимных блокировок при обращении к таблице). Ниже приведены запросы на создание базы данных и таблиц спроектированной БД на синтаксисе MySQL. Создание БД: CREATE DATABASE `library`; Таблица readers: CREATE TABLE `readers` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `birthday` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Таблица types: CREATE TABLE `types` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Таблица readrooms: CREATE TABLE `readrooms` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Таблица slips: CREATE TABLE `slips` ( `id` int NOT NULL AUTO_INCREMENT, `reader` int NOT NULL, `timefrom` varchar(10) NOT NULL, `timeto` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `reader_idx` (`reader`), CONSTRAINT `reader` FOREIGN KEY (`reader`) REFERENCES `readers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; Таблица books: CREATE TABLE `books` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `author` varchar(50) NOT NULL, `year` year NOT NULL, `pubplace` varchar(200) NOT NULL, `pubhouse` varchar(200) NOT NULL, `type` int NOT NULL, `readroom` int NOT NULL, `pages` int NOT NULL, PRIMARY KEY (`id`), KEY `type` (`type`), KEY `readroom` (`readroom`), CONSTRAINT `books_ibfk_1` FOREIGN KEY (`type`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `books_ibfk_2` FOREIGN KEY (`readroom`) REFERENCES `readrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; Таблица slips_info: CREATE TABLE `slips_info` ( `id` int NOT NULL AUTO_INCREMENT, `slip` int NOT NULL, `book` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `books_in_slip` (`slip`,`book`), KEY `book` (`book`), CONSTRAINT `book` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `slip` FOREIGN KEY (`slip`) REFERENCES `slips` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; Данные запросы, в предварительно настроенной СУБД, создадут структура базы данных. Описание приложения для работы с БД Приложение для работы с БД представляет собой веб-интерфейс, осуществляющий добавление, удаление и изменение данных в базе, написанный на php. Выбор языка обусловлен его простотой и лёгкостью в обслуживании, а так же, популярностью в сообществе. На этом языке написано большое количество веб-логики. Часть функций реализует прямую вставку в таблицы (а так же изменение и удаление), например - управление перечнем книг, читальных залов, областей знаний. Однако часть из них достаточно сложна, и требует проверок перед вставкой, либо же множественных вставок, поэтому реализована в виде процедур СУБД. Это касается добавления/удаления/изменения читателей и заявок на выдачу книг - для этих функций были написаны хранимые процедуры: Процедура добавления читателя: DELIMITER $$ CREATE PROCEDURE `add_reader`(name varchar(50), phone varchar(14), birthday date) begin select phone regexp '\\+7\\([:digit:]{3}\\)[:digit:]{7}$' into @phone_in_right_format; if @phone_in_right_format = 0 then select 'error' as result, 'введите телефон в формате +7(XXX)XXXXXXX' as detail; else insert into readers values (default, name, phone, date_format(birthday, '%d/%m/%Y')); end if; end$$ DELIMITER ; Процедура изменения читателя: DELIMITER $$ CREATE PROCEDURE `edit_reader`(edit_id int, new_name varchar(50), new_phone varchar(14), new_birthday date) begin select new_phone regexp '\\+7\\([:digit:]{3}\\)[:digit:]{7}$' into @phone_in_right_format; if @phone_in_right_format = 0 then select 'error' as result, 'введите телефон в формате +7(XXX)XXXXXXX' as detail; else update readers r set `r`.`name` = new_name, `r`.`phone` = new_phone, `r`.`birthday` = date_format(new_birthday, '%d/%m/%Y') where `r`.`id` = edit_id; end if; end$$ DELIMITER ; Процедура добавления заявки: DELIMITER $$ CREATE PROCEDURE `add_slip`(reader int, timefrom date, timeto date) begin if timefrom > timeto then select 'error' as result, 'дата выдачи позднее даты возврата' as detail; else insert into slips values (default, reader, date_format(timefrom, '%d/%m/%Y'), date_format(timeto, '%d/%m/%Y')); select id from slips order by 1 desc; end if; end$$ DELIMITER ; Процедура изменения деталей заявки: DELIMITER $$ CREATE PROCEDURE `add_slip_nfo`(slip int, book int) begin select count(*) from slips_info si where si.slip = slip into @books_in_slip; if @books_in_slip >= 5 then select 'error' as result, 'одному читателю можно выдать не более 5 книг' as detail; else insert into slips_info values (default, slip, book); select 'success' as result, 'книга внесена в читательское требование' as detail; end if; end$$ DELIMITER ; Само приложение представляет собой набор веб-страниц, с доступом к этим функциям. На главной странице (рисунок 3) меню, с основными разделами и краткое описание системы. Это меню дублируется на каждой странице для удобства перемещения по страницам.   Рисунок 4. Читальные залы Рисунок 3. Главная страница Самыми простыми разделами являются «читальные залы», «области знаний», и «читатели» - в них просто можно просматривать и управлять списком залов, типов и посетителей, соответственно, с помощью соответствующих кнопок.   Рисунок 5. Редактирование зала Кнопка «редактировать» откроет форму, в которой можно изменить название зала.   Рисунок 6. Добавление нового зала Кнопка «добавить» откроет похожую форму для добавления нового. По условию задания залов три, однако, было решено добавить возможность добавлять и изменять их по своему усмотрению. Кнопка «удалить» удаляет выбранный читальный зал.  Рисунок 7. Области знаний Абсолютно так же работает форма управления типами областей знаний. Нет никакого отличия от предыдущих форм, как в виде, так и в выполняемых функциях. То же самое касается и страницы со списком читателей, с той разницей, что форма добавления и изменения посетителя работает с другими данными:  Рисунок 8. Добавление читателя  Рисунок 9. Управление читательскими требованиями Несколько сложнее устроен интерфейс управления заявками на выдачу книг.  Удаление будет работать так же, как и на других экранах, функции «добавить» и «открыть» будут открывать новое окно, с другим интерфейсом.  Рисунок 10. Добавление новой заявки Здесь для новой заявки нужно будет ввести все необходимые данные для ее регистрации. Кнопка «создать требование» создаст заявку и закроет окно. Кнопка «отмена» просто его закроет.   Рисунок 11. Просмотр и редактирование требования На предыдущей форме, так же, можно открыть интересующее читательское требование для его последующего просмотра и редактирования. Откроется форма со списком книг и кнопками для их добавления и удаления.   Рисунок 12. Добавление книги в требование Кнопка «удалить» просто удаляет книгу. Кнопка «закрыть» просто закрывает вкладку. Кнопка «добавить книгу» открывает форму добавления книги со списком доступных книг.  Рисунок 13. Отчеты Тут можно выбрать в выпадающем списке нужную и нажав кнопку «добавить» добавить её к списку. На рисунке 13 показаны формы получения отчетов, которые нужно было реализовать по заданию. Чтобы получить отчет, нужно ввести входные данные в поле, если они предусмотрены, и нажать кнопку «выгрузить».   Рисунок 14. Отчет А Отчёт с выгрузкой количества выдач по месяцам в заданном году:  Рисунок 15. Отчет B Самые старые книги по читальным залам:   Рисунок 16. Отчет C Зал, в котором есть книги только выбранных пользователем типов (ни больше, ни меньше):  Рисунок 17. Отчет D Пять самых популярных книг: Код всех процедур и самого веб-приложения приведен в приложениях 1 и 2. Заключение База данных - очень мощный инструмент, позволяющий управлять огромным количеством информации, использовать её и анализировать. Так же с помощью базы данных можно организовать доступ к информации через большое количество программных интерфейсов, устроенных совершенно по-разному, и дающих пользователю одну и ту же информацию в разных представлениях. Во время выполнения курсовой работы мною были освоены методы анализа предметной области и разные виды проектирования базы данных (инфологическое, даталогическое, физическое), был получен полезный опыт разработки приложения для работы с данными. Данная курсовая работа позволила автоматизировать учет книг и выдачу книг читателям в библиотеке. БД позволяет удобно управлять списками читателей и книг, делить книги на категории с точки зрения жанра и с точки зрения их физического расположения в библиотеке, а так же формировать различные отчеты, которые будут полезны при инвентаризациях и аудитах. Библиография Основная литература И.П. Карпова. Базы данных: Учебное пособие. СПб: Питер, 2013г. – 240с. М.П. Малыхина. Базы данных: основы, проектирование, использование. 2-е изд. СПб: БХВ-Петербург, 2007. В.В. Кирилов, Г.Ю. Громов. Введение в реляционные базы данных. – СПб.:БХВ-Петербург, 2009г. – 464с. Дополнительная литература Т. Конноли, К. Бегг. Базы данных: проектирование, реализация и сопровождение. Теория и практика. Вильямс, 2003г – 1440с. Г. Хансен, Дж. Хансен. Базы данных: разработка и управление. М: Бином, 1999г. – 704с. Г. Гарсиа-Молина, Дж. Ульман, Дж. Уидом. Системы баз данных. Полный курс. Вильямс, 2003г. – 1088с. Интернет-источники Справочник по HTML и CSS: https://www.w3.org/Style/Examples/011/firstcss.ru.html Документация MySQL: https://dev.mysql.com/doc/refman/8.0/en/ Документация PHP: https://www.php.net/manual/ru/index.php Приложение 1. SQL скрипты Создание БД: CREATE DATABASE `library`; Таблица readers: CREATE TABLE `readers` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `phone` varchar(50) NOT NULL, `birthday` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Таблица types: CREATE TABLE `types` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Таблица readrooms: CREATE TABLE `readrooms` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; Таблица slips: CREATE TABLE `slips` ( `id` int NOT NULL AUTO_INCREMENT, `reader` int NOT NULL, `timefrom` varchar(10) NOT NULL, `timeto` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `reader_idx` (`reader`), CONSTRAINT `reader` FOREIGN KEY (`reader`) REFERENCES `readers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; Таблица books: CREATE TABLE `books` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `author` varchar(50) NOT NULL, `year` year NOT NULL, `pubplace` varchar(200) NOT NULL, `pubhouse` varchar(200) NOT NULL, `type` int NOT NULL, `readroom` int NOT NULL, `pages` int NOT NULL, PRIMARY KEY (`id`), KEY `type` (`type`), KEY `readroom` (`readroom`), CONSTRAINT `books_ibfk_1` FOREIGN KEY (`type`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `books_ibfk_2` FOREIGN KEY (`readroom`) REFERENCES `readrooms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; Таблица slips_info: CREATE TABLE `slips_info` ( `id` int NOT NULL AUTO_INCREMENT, `slip` int NOT NULL, `book` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `books_in_slip` (`slip`,`book`), KEY `book` (`book`), CONSTRAINT `book` FOREIGN KEY (`book`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `slip` FOREIGN KEY (`slip`) REFERENCES `slips` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; Процедура добавления читателя: DELIMITER $$ CREATE PROCEDURE `add_reader`(name varchar(50), phone varchar(14), birthday date) begin select phone regexp '\\+7\\([:digit:]{3}\\)[:digit:]{7}$' into @phone_in_right_format; if @phone_in_right_format = 0 then select 'error' as result, 'введите телефон в формате +7(XXX)XXXXXXX' as detail; else insert into readers values (default, name, phone, date_format(birthday, '%d/%m/%Y')); end if; end$$ DELIMITER ; Процедура изменения читателя: DELIMITER $$ CREATE PROCEDURE `edit_reader`(edit_id int, new_name varchar(50), new_phone varchar(14), new_birthday date) begin select new_phone regexp '\\+7\\([:digit:]{3}\\)[:digit:]{7}$' into @phone_in_right_format; if @phone_in_right_format = 0 then select 'error' as result, 'введите телефон в формате +7(XXX)XXXXXXX' as detail; else update readers r set `r`.`name` = new_name, `r`.`phone` = new_phone, `r`.`birthday` = date_format(new_birthday, '%d/%m/%Y') where `r`.`id` = edit_id; end if; end$$ DELIMITER ; Процедура добавления заявки: DELIMITER $$ CREATE PROCEDURE `add_slip`(reader int, timefrom date, timeto date) begin if timefrom > timeto then select 'error' as result, 'дата выдачи позднее даты возврата' as detail; else insert into slips values (default, reader, date_format(timefrom, '%d/%m/%Y'), date_format(timeto, '%d/%m/%Y')); select id from slips order by 1 desc; end if; end$$ DELIMITER ; Процедура изменения деталей заявки: DELIMITER $$ CREATE PROCEDURE `add_slip_nfo`(slip int, book int) begin select count(*) from slips_info si where si.slip = slip into @books_in_slip; if @books_in_slip >= 5 then select 'error' as result, 'одному читателю можно выдать не более 5 книг' as detail; else insert into slips_info values (default, slip, book); select 'success' as result, 'книга внесена в читательское требование' as detail; end if; end$$ DELIMITER ; Запросы для отчетов используются в коде, поэтому содержат переменные, значения которых могут менять результат. Отчет А: select month(str_to_date(s.timefrom, '%d/%m/%Y')) as month, count(*) as count from slips s join slips_info si on(si.slip = s.id) join readers r on(s.reader = r.id) join books b on(si.book = b.id) where year(str_to_date(s.timefrom, '%d/%m/%Y')) = $year group by month order by 1 где $year вводится пользователем. Отчет В: select rr.name as readroom, b.author, b.name as book, year(now())-(year) as age from books b join readrooms rr on (b.readroom = rr.id) group by readroom Отчет С: select r.name as readroom from books b join readrooms r on(b.readroom = r.id) where type in ($list_of_types) and readroom not in(select readroom from books b join readrooms r on(b.readroom = r.id) where type in (select id from types where id not in($list_of_types))) group by readroom having count(distinct type) = $count_of_types где $list_of_types - перечень типов, заданных пользователем, а $count_of_types - их количество. Отчет D: select b.author, b.name, count(s.id) as count from slips s join slips_info si on s.id=si.slip join books b on si.book=b.id where month(now()) - month(str_to_date(s.timefrom, '%d/%m/%Y')) = 1 group by b.name order by 3 desc Приложение 2. Код веб-приложения index.php:
БИБЛИОТЕКАБаза данных, которая содержит информацию о работе библиотеки. Библиотека состоит из нескольких читальных залов (зал художественной литературы, зал технической литературы, зал иностранной литературы). Библиотекари выдают книги читателям. Необходимо вести учет книг, имеющихся в библиотеке и книг, выданных читателям. Необходимо вести учет посещений библиотеки читателями. |