Пример проектирования базы данных MySQL
Блог программистаС точки зрения клиента — библиотека является местом, где можно получить книгу, а затем сдать ее. Некоторые клиенты пользуются возможностью самостоятельного подбора литературы в информационной системе библиотеки. Пользователь не задумывается о том, откуда в системе появляются новые книги, но их туда вносит библиотекарь. Также, информационная система позволяет ему находить читателей с задолженностями и маловостребованные книги. От обычного посетителя библиотеки полностью скрыта роль администратора информационной системы.
Реальная информационная библиотечная система представляет собой большую и сложную систему, предусматривающую возможность параллельной работы тысяч пользователей и интегрирующуюся с другими библиотечными системами.
В статье рассмотрим процесс разработки простой информационной системы, однако, предусматривающей роли библиотекаря, посетителя и администратора.
Содержание:
Инфологическое проектирование:
- Выбор СУБД и других программных средств;
- Составление и нормализация реляционных отношений;
- Нормализация полученных отношений;
- Определение требований к операционной обстановке;
- Описание групп пользователей и прав доступа;
1 Инфологическое проектирование
1.1 Анализ предметной области и информационных задач пользователей
Основная задача любой библиотеки — обработка книжного фонда. Нетрудно выделить три основные группы пользователей системы: читатель, библиотекарь, администратор. Деятельность каждого из них показана на диаграмме вариантов использования [1] (рисунок 1).
Уже сейчас можно выделить некоторые сущности и отношения будущей базы данных (рисунок 2).
При таком подходе не понятно как именно связать читателя с книгой (у читателя не проставлена арность в отношении «выдача/прием». Если книга имеет несколько экземпляров — то она может быть выдана нескольким читателям. Даже если же под книгой понимать один экземпляр — то при сохранении в таблице книг текущего читателя приведет к невозможности получения информации о том, кто (и сколько раз) брал эту книгу ранее.
Решением может быть введение дополнительной сущности — карточки о выдаче книги. При выдаче книги читателю заводится карточка, а при сдаче книги — в нее ставится соответствующая пометка. С помощью этих карточек определяются задолженности каждого пользователя и вычисляется статистика использования книг.
При бронировании литературы читателем — также заводится карточка, если забронированная литература не взята читателем в определенный срок — карточка уничтожается. Существует ограничение на количество книг, которые может забронировать читатель.
При подборе литературы пользователь просматривает каталог литературы с возможностью фильтрации результатов поиска по автору, названию, году издания.
Есть возможность расчета статистики по всем книгам библиотеки, при этом количество выданных экземпляров книги за заданный период времени. Также можно задать минимальное число экземпляров книг, для которых выполняется расчет. На основании этой статистики производится списание неиспользуемых книг из библиотеки.
Можно выделить следующие основные сущности предметной области:
- пользователь (библиотекари и администраторы);
- читатель;
- читальный зал;
- книга;
- карточка выдачи книги;
- карточка бронирования книги.
Доработанная ER- диаграмма базы данных приведена на рисунке 3.
Рисунок 3 — ER диаграмма база данных (вариант 2)
В соответствии с прецедентами, показанными на рисунке 1, база данных должна реализовывать, следующие запросы (не полный перечень):
- отобразить книги, соответствующие заданным условиям;
- отобразить пользователей, имеющих незакрытые вовремя карточки выдачи книг (библиотекарь ищет должников);
- отобразить все книги, соответствующие незакрытым вовремя карточкам выдачи книг заданного пользователя (пользователь пришел в библиотеку за новыми книгами — надо посмотреть является ли он должником и сообщить ему об этом);
- удалить все карточки бронирования, созданные более чем N секунд назад;
- отобразить все книги, соответствующие незакрытым карточкам бронирования книг заданного пользователя (читатель заказал книги и пришел в библиотеку за ними — библиотекарю надо получить этот список чтобы выдать).
1.2 Формирование схемы данных
Для формирования схемы данных необходимо сначала дополнить ER-диаграмму реквизитами сущностей (уточнить ее). Иногда, при этом удается найти ошибки построения ER-диаграммы — в этой задаче было обнаружено, что книгу необходимо «как-то» связать с залом библиотеки. Сделать это можно поместив в книгу реквизит «номер зала», однако при таком подходе одну и ту же книгу придется описывать в базе несколько раз (если она встречается в разных залах). Более правильный подход заключается во введении дополнительной сущности «размещение книги». На рисунке 4 показана ER-диаграмма с добавленной сущностью и реквизитами.
Приведенная ER-диаграмма отражает основные таблицы, связи и атрибуты, на ее основе можно построить модель БД. На ER-диаграммы нет стандарта, но есть ряд нотаций (Чена, IDEFIX, Мартина и т.п.), но на модель предметной области не удалось найти ни стандарта, ни нотаций. Однако, в ходе построения такой диаграммы обязательно выделяются ключевые поля (внешние и внутренние), иногда — индексы и типы данных. Схема базы данных, приведенная на рисунке 5, выполнена с использованием открытого инструмента plantuml [3], при этом:
- для связей используется нотация Мартина («вороньи лапки»);
- таблицы изображены прямоугольниками, разделенными на 3 секции:
- имя таблицы;
- внутренние ключи (помечаются маркером);
- остальные поля, при этом обязательные помечаются маркером.
При разработке этой модели возникало желание объединить таблицу администраторов с таблицей библиотекарей — добавить таблицу users, однако:
- администратор не связан с конкретным залом (пришлось бы заполнять соответствующее поле null-значениями);
- вероятно, это осложнило бы распределение прав доступа — сейчас доступ к таблице administrators имеет только администратор базы данных (работающий через специальную панель СУБД и не имеющий учетной записи в разрабатываемой системе). Однако при соединении таблиц пользовательские запросы требовали бы доступа к новой таблице.
При построении этой диаграммы был найден и исправлен недочет ER-диаграммы — добавлена таблица
librarians_rooms, объединяющая библиотекарей и залы. Это нужно, так как один библиотекарь может работать в нескольких залах, но несколько библиотекарей могут работать в одном и том же зале.
2 Физическое проектирование
2.1 Выбор СУБД и других программных средств
Реализовать разрабатываемую систему можно с использованием любой СУБД, в том числе — нереляционной (NoSQL). NoSQL базы данных, в свою очередь делятся на несколько типов:
- колоночные базы и базы «ключ-значение» призваны ускорить обработку данных за счет реализации особых схем хранения данных в памяти;
- документные базы позволяют хранить данные с разными полями (у разных объектов) и лучше подходят для параллельной обработки данных. Однако, медленно выполняют обновление данных.
В нашем случае база будет использоваться внутри библиотеки и, скорее всего, не будет требовать очень высокой производительности. Кроме того, структура таблиц меняться не должна. Поэтому будем использовать реляционные базы данных.
В ряде статей, посвященных выбору СУБД рекомендуется выбирать СУБД работающие в облаке если сложно предсказать будущую нагрузку, однако, в настоящее время почти все популярные СУБД доступны в качестве облачного сервиса. Так например, Google Cloud SQL предоставляет PostgreSQL, SQL Server и MySQL [4]. Яндекс предоставляет такой же функционал, а также облачный доступ к ClickHouse, Redis, Kafka, MongoDB [5].
В статье [6] отмечается, что MySQL хорошо подходит если объем данных не превышает 2Гб, иначе — лучше взять более сложный в настройке PostgreSQL. Если бы речь шла о крупной библиотеке — то MySQL не подошел бы, например библиотека МГУ хранит более 10 миллионов книг, если предположить, что одна книга в нашей базе описывается 200 байтами (хранит строки) — то только таблица с описанием книг заняла бы 1,86 Гб и MySQL не справился бы. Однако, в более простой базе, как наша — его вполне хватит. Для разработки будет использована MySQL 8, т.к. это самая свежая версия, которую предоставляет YandexCloud.
2.2 Составление и нормализация реляционных отношений
При разработке реляционных отношений, было обнаружено, что в ряд таблиц базы стоит добавить ряд новых полей — выделены в таблицах курсивом.
Для хранения даты в MySQL используется тип данных
DATETIME, объект которого занимает 8 байт [7].Схема базы данных была создана в среде MySQL Workbench [8], в результате получена схема, показанная на рисунке 6.
2.3 Нормализация полученных отношений
Разработанная схема БД находится в:
- первой нормальной форме, так как в качестве доменов выступают только скалярные значения и информация в таблицах не дублируется. Почти во всех таблицах есть идентификатор (id), а в остальных — librarian_rooms и book_places в качестве первичного ключа выступает пара полей, так как нет смысла добавлять одного и того же библиотекаря или книгу дважды в один зал. При повторном добавлении книги (если произошла приемка точно таких же книг) — надо выполнить поиск и изменить число экземпляров в существующей записи;
- второй и третьей нормальных формах, каждый не ключевой атрибут неприводимо и нетранзитивно зависит от первичного ключа. Для всех таблиц нашей БД это очевидно — Логин и Пароль зависят от Id и их нельзя вывести иным образом; количество книг и номер полки зависят от id книги и id комнаты и их тоже нельзя вывести никак иначе.
Таким образом, схема базы данных показанная на рисунках 5 и 6 находится в нормальной форме Бойса-Кодда [9], а приведение к ней (выделение дополнительных таблиц) было произведено уже в разделе 1.2.
2.4 Определение требований к операционной обстановке
В разделе 2.1 выполнялся выбор СУБД, однако при этом мы точно не знали объем памяти, необходимый для хранения таблиц. Очевидно, в библиотеке основной объем памяти будут занимать книги, пользователи и карточки выдачи/бронирования книг.
Предположим, в библиотеку в месяц будет поступать 100 новых (разных) книг и записываться 200 пользователей. Тысяча пользователей возьмет по 3 книги. Сколько книг будет забронировано — не важно, т.к. карточки бронирования уничтожаются. Учитывая, что для хранения записи об одной книге требуется
45*4+4*2 = 188 байт, для хранения читателя 184 байта, а одна карточка выдачи книги занимает 32 байта можно определить примерный объем памяти, необходимый для базы данных библиотеки в течении одного месяца работы:
100*188 + 200*184 + 1000*3*32 = 18800 + 36800 + 96000 = 151600 байт = 148 Кб
Значит, за год объем базы не должен превысить 1,73Мб.2.5 Описание групп пользователей и прав доступа
Администратор базы данных взаимодействует с базой посредством исполнения SQL-запросов. При этом он имеет доступ ко всем данных, может изменять структуру БД, устанавливает права доступа для остальных групп.
Администратор зала библиотеки имеет доступ по чтению и записи к отношениям
librarians, rooms, librarians_rooms. При необходимости работы с фондами библиотеки администратор входит в систему с учетной записью библиотекаря.
Библиотекарь имеет доступ:
• по чтению к отношениям:
readers, issue_cards, librarians_rooms и rooms;
• по чтению и записи к отношениям: readers, booking_cards, book_places, books, issue_casrds.
Читатель библиотеки может взаимодействовать с системой через программу-клиент, установленную в зале библиотеки или извне библиотеки через веб-интерфейс. При этом, он имеет доступ по чтению к отношениям:
books, book_places, rooms, booking_cards, issue_cards. При работе через программу-клиент читатель имеет также доступ по записи к отношению issue_cards — он может из читального зала забронировать книгу.
3 Формирование запросов к СУБД
3.1 Создание таблиц в базе данных и установка индексов
Для создания таблиц в соответствии с заданной схемой БД в СУБД MySQL можно использовать запросы, сгенерированные автоматически по схеме базы данных в среде MySQL Workbench (тут база данных называется
library):
DROP SCHEMA IF EXISTS `library` ;
CREATE SCHEMA IF NOT EXISTS `library` DEFAULT CHARACTER SET utf8 ;
SHOW WARNINGS;
USE `library` ;
DROP TABLE IF EXISTS `library`.`administrators` ;
CREATE TABLE IF NOT EXISTS `library`.`administrators` (
`id` INT NOT NULL,
`logins` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`readers` ;
CREATE TABLE IF NOT EXISTS `library`.`readers` (
`id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
`passport` VARCHAR(45) NOT NULL,
`address` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`rooms` ;
CREATE TABLE IF NOT EXISTS `library`.`rooms` (
`id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`librarians` ;
CREATE TABLE IF NOT EXISTS `library`.`librarians` (
`id` INT NOT NULL,
`login` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`books` ;
CREATE TABLE IF NOT EXISTS `library`.`books` (
`id` INT NOT NULL,
`author` VARCHAR(45) NOT NULL,
`publication_year` INT NOT NULL,
`publisher` VARCHAR(45) NOT NULL,
`name` VARCHAR(45) NOT NULL,
`isbn` VARCHAR(45) NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`librarian_rooms` ;
CREATE TABLE IF NOT EXISTS `library`.`librarian_rooms` (
`id_room` INT NOT NULL,
`id_librarian` INT NOT NULL,
PRIMARY KEY (`id_room`, `id_librarian`),
INDEX `id_librarian_idx` (`id_librarian` ASC),
CONSTRAINT `id_lr_room`
FOREIGN KEY (`id_room`)
REFERENCES `library`.`rooms` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `id_lr_librarian`
FOREIGN KEY (`id_librarian`)
REFERENCES `library`.`librarians` (`id`)
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = tis620
COLLATE = tis620_bin;
DROP TABLE IF EXISTS `library`.`booking_cards` ;
CREATE TABLE IF NOT EXISTS `library`.`booking_cards` (
`id` INT NOT NULL,
`id_reader` INT NOT NULL,
`id_book` INT NOT NULL,
`id_librarian` INT NOT NULL,
`time` DATETIME NOT NULL,
`period` DATETIME NULL,
PRIMARY KEY (`id`),
INDEX `id_reader_idx` (`id_reader` ASC),
INDEX `id_book_idx` (`id_book` ASC),
INDEX `id_librarian_idx` (`id_librarian` ASC),
CONSTRAINT `id_bc_reader`
FOREIGN KEY (`id_reader`)
REFERENCES `library`.`readers` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_bc_book`
FOREIGN KEY (`id_book`)
REFERENCES `library`.`books` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_bc_librarian`
FOREIGN KEY (`id_librarian`)
REFERENCES `library`.`librarians` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`issue_cards` ;
CREATE TABLE IF NOT EXISTS `library`.`issue_cards` (
`id` INT NOT NULL,
`id_reader` INT NOT NULL,
`id_book` INT NOT NULL,
`time` DATETIME NOT NULL,
`period` DATETIME NULL,
`issue_cardscol` VARCHAR(45) NULL,
PRIMARY KEY (`id`),
INDEX `id_reader_idx` (`id_reader` ASC),
INDEX `id_book_idx` (`id_book` ASC),
CONSTRAINT `id_ic_reader`
FOREIGN KEY (`id_reader`)
REFERENCES `library`.`readers` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_ic_book`
FOREIGN KEY (`id_book`)
REFERENCES `library`.`books` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
DROP TABLE IF EXISTS `library`.`book_places` ;
CREATE TABLE IF NOT EXISTS `library`.`book_places` (
`id_book` INT NULL,
`id_room` INT NOT NULL,
`quantity` INT NOT NULL,
`shell_number` INT NOT NULL,
PRIMARY KEY (`id_book`, `id_room`),
INDEX `id_room_idx` (`id_room` ASC),
CONSTRAINT `id_bp_book`
FOREIGN KEY (`id_book`)
REFERENCES `library`.`books` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `id_bp_room`
FOREIGN KEY (`id_room`)
REFERENCES `library`.`rooms` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Приведенный запрос успешно выполнен на сервере, в результате — созданы таблицы. Тут стоит обратить внимание на именование ограничений для внешний ключей — они должны быть уникальными в базе данных, поэтому в имени ограничения кодируется имя отношения, для которого оно описано, например
CONSTRAINT `id_ic_reader` задает ограничение на поле id_reader в отношении issue_cards.Видно, что для всех ключевых полей в базе данных проставлены индексы — за счет этого записи упорядочиваются по этим полям и поиск выполняется быстрее (бинарный поиск вместо линейного).
3.2 Проектирование наиболее востребованных запросов
Перед созданием запросов был установлен и запущен MySQL Server, настроено подключение к этому серверу среды MySQL Workbench. В базу были добавлены данные для проверки корректности выполнения запросов. Добавление производилось с помощью MySQL Workbench, в результате были сгенерированы следующие запросы:
INSERT INTO `library`.`administrators` (`id`, `logins`, `password`) VALUES (1, 'lena', '12345'); INSERT INTO `library`.`administrators` (`id`, `logins`, `password`) VALUES (2, 'petya', '54321'); INSERT INTO `library`.`readers` (`id`, `name`, `passport`, `address`, `phone`) VALUES (1, 'vasya', '0402 892322', 'Moskva, Kreml', '214 34 12'); INSERT INTO `library`.`readers` (`id`, `name`, `passport`, `address`, `phone`) VALUES (2, 'kostya', '4561 455311', 'Spb, Mira 11', '8 909 999 99 99'); INSERT INTO `library`.`rooms` (`id`, `name`) VALUES (1, 'Зал С++'); INSERT INTO `library`.`rooms` (`id`, `name`) VALUES (2, 'Зал проектирование'); INSERT INTO `library`.`librarians` (`id`, `login`, `password`) VALUES (1, 'vova', '11111'); INSERT INTO `library`.`librarians` (`id`, `login`, `password`) VALUES (2, 'sveta', '22222'); INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (1, ' Э. Гамма, Р. Хелм, Р. Джонсон', 2009, 'СПб.: Питер', 'Приемы ОО- проектирования', NULL); INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (2, 'Джейсон Мак-Колм Смит', 2013, 'Вильямс', 'Элементарные шаблоны проектирования', NULL); INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (3, 'Стивен Прата', 2020, 'Вильямс', 'Язык программирования C++ (C++11). ', NULL); INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (4, 'Мейерс С.', 2014, 'ДМК Пресс', 'Эффективное использование С++', NULL); INSERT INTO `library`.`books` (`id`, `author`, `publication_year`, `publisher`, `name`, `isbn`) VALUES (5, 'Андрей Александреску', 2002, 'Вильямс', 'Современное проектирование на C++.', NULL); INSERT INTO `library`.`librarian_rooms` (`id_room`, `id_librarian`) VALUES (1, 2); INSERT INTO `library`.`librarian_rooms` (`id_room`, `id_librarian`) VALUES (2, 1); INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (1, 1, 10, 555); INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (1, 2, 5, 333); INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (2, 1, 4, 111); INSERT INTO `library`.`book_places` (`id_book`, `id_room`, `quantity`, `shell_number`) VALUES (3, 2, 60, 222); INSERT INTO `library`.`booking_cards` (`id`, `id_reader`, `id_book`, `id_librarian`, `time`, `period`) VALUES (1, 1, 1, 2, '2019-10-20', '2019-11-20');
При подготовке этих данных выявилась проблема разработанной базы — не все названия книг умещаются в 45 символов. Пришлось сокращать названия. Также, из-за того, что пользователи распределены по трем таблицам — то при создании нового пользователя надо выполнять проверку отсутствия логина во всех таблицах.
Для получения книг по фильтру должны выполняться запросы на подобии такого:
select * from books where name like '%C++%'В данном случае выводятся книги, в названии которых есть подстрока «С++». Результат выполнения запроса приведен на рисунке 7.
Для поиска должников можно выполнить такой запрос:
select rd.* from readers rd, booking_cards bc where rd.id = bc.id_reader and bc.period < '2021-10-20';
Вместо константы должна поставляться текущая дата.
Для отображения книг, которые задолжал конкретный пользователь можно выполнить такой запрос:
select bk.* from booking_cards bc, books bk
where
bk.id = bc.id and
bc.period < '2021-10-20' and
bc.id_reader = 1;
Для этого запроса информационная система должна сначала находить пользователя в базе (получать его id) и подставлять это значение вместо 1, вместо константы даты должна подставляться текущая дата. Результат выполнения запроса приведена на рисунке 8.
Список использованной литературы
- Основы UML — диаграммы использования (use-case). URL: https://pro-prof.com/archives/2594
- Технологии баз данных. Лекция 3. Модель «Сущность-связь». URL: https://docplayer.ru/27886777-Model-sushchnost-svyaz-tehnologii-baz-dannyh-lekciya-3.html
- Entity Relationship Diagram. URL: https://plantuml.com/ru/ie-diagram
- Overview of the high availability configuration. URL: https://cloud.google.com/sql/docs/sqlserver/high-availability
- YandexCloud. URL: https://console.cloud.yandex.ru/ [режим доступа: требуется регистрация].
- Рассуждение на тему, какую базу данных выбирать. URL: https://habr.com/ru/post/348220/
- Календарные типы данных в MySQL: особенности использования. URL: https://habr.com/ru/post/69983/
- Основы работы с MySQL Workbench: быстрый старт, управление схемой данных. URL: https://mithrandir.ru/professional/soft-and-hardware/mysql-workbench-basics.html
- Нормализация отношений. Шесть нормальных форм. URL: https://habr.com/ru/post/254773/