Как спроектировать базу данных
Перейти к содержимому

Как спроектировать базу данных

  • автор:

Пример проектирования базы данных 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 Составление и нормализация реляционных отношений

    Таблица 1 — Схема отношения «Администраторы» (administrators):

    Первичный ключ, уникальный

    Таблица 2 — Схема отношения «Читатели» (readers):

    Первичный ключ, уникальный

    Таблица 3 — Схема отношения «Библиотекари — Читальные залы» (librarian_rooms):

    Первичный ключ (составной), внешний ключ к librarians, обязательное поле

    Первичный ключ (составной), внешний ключ к rooms, обязательное поле

    Таблица 4 — Схема отношения «Карточки выдачи книг» (booking_cards):

    Первичный ключ, уникальный

    Внешний ключ к readers, обязательное поле

    Внешний ключ к books, обязательное поле

    Внешний ключ к librarians, обязательное поле

    Таблица 5 — Схема отношения «Карточки бронирования книг» (issue_cards):

    Первичный ключ, уникальный

    Внешний ключ к readers, обязательное поле

    Внешний ключ к books, обязательное поле

    Таблица 6 — Схема отношения «Читальные залы» (rooms):

    Первичный ключ, уникальный

    Таблица 7 — Схема отношения Библиотекари (librarians):

    Первичный ключ, уникальный

    Таблица 8 — Схема отношения Книги (books):

    Первичный ключ, уникальный

    Таблица 9 — Схема отношения «Размещение книг» (book_places):

    Внешний ключ к books, обязательное поле

    Внешний ключ к books, обязательное поле

    При разработке реляционных отношений, было обнаружено, что в ряд таблиц базы стоит добавить ряд новых полей — выделены в таблицах курсивом.
    Для хранения даты в 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.

    Список использованной литературы

    1. Основы UML — диаграммы использования (use-case). URL: https://pro-prof.com/archives/2594
    2. Технологии баз данных. Лекция 3. Модель «Сущность-связь». URL: https://docplayer.ru/27886777-Model-sushchnost-svyaz-tehnologii-baz-dannyh-lekciya-3.html
    3. Entity Relationship Diagram. URL: https://plantuml.com/ru/ie-diagram
    4. Overview of the high availability configuration. URL: https://cloud.google.com/sql/docs/sqlserver/high-availability
    5. YandexCloud. URL: https://console.cloud.yandex.ru/ [режим доступа: требуется регистрация].
    6. Рассуждение на тему, какую базу данных выбирать. URL: https://habr.com/ru/post/348220/
    7. Календарные типы данных в MySQL: особенности использования. URL: https://habr.com/ru/post/69983/
    8. Основы работы с MySQL Workbench: быстрый старт, управление схемой данных. URL: https://mithrandir.ru/professional/soft-and-hardware/mysql-workbench-basics.html
    9. Нормализация отношений. Шесть нормальных форм. URL: https://habr.com/ru/post/254773/

    Как научиться проектировать базы данных и остаться в живых

    Также данная книга доступна ещё в библиотеке. Запишись сразу в несколько библиотек и получай книги намного быстрее.

    Как читать книгу после покупки

    Посоветуйте книгу друзьям! Друзьям – скидка 10%, вам – рубли

    По вашей ссылке друзья получат скидку 10% на эту книгу, а вы будете получать 10% от стоимости их покупок на свой счет ЛитРес. Подробнее

    Стоимость книги: 200 ₽
    Ваш доход с одной покупки друга: 20 ₽
    Чтобы посоветовать книгу друзьям, необходимо войти или зарегистрироваться Войти

    • Объем: 34 стр. 25 иллюстраций
    • Жанр:к ниги о компьютерахРедактировать

    Как научиться проектировать базы данных и остаться в живых
    Шрифт: Меньше Аа Больше Аа

    © Елена Литвак, 2021

    Создано в интеллектуальной издательской системе Ridero

    Введение

    Вопросы проектирования реляционных баз данных поднимаются во множестве книг, написанных корифеями компьютерных наук. Я бы разделила эти книги на два типа: взрывающая мозг математика и интуитивное описание для чайников.

    Почему математика? Да потому, что базы данных – это чисто математический объект, который называется «реляционная алгебра». И тут, поверьте, есть от чего взорваться мозгу. В книге известного исследователя в области информационных технологий Джеффри Ульмана «Системы баз данных: полный курс» целых 1088 страниц. Проектированию на основе математического аппарата и описания реляционной алгебры посвящена примерно половина книги. Надо ли говорить, что студент второго курса обычно засыпает уже на десятой странице? А взрослый работающий человек, который хочет повысить свой скил по проектированию, просто отчаивается: «Когда я все это освою, если мне нужно уже через неделю спроектировать базу данных и показать результаты?!».

    «Интуитивное» описание процесса проектирования намного проще, но оно не дает универсального подхода. Друзья, вот таблица, вот еще таблица. Эта штука называется первичный ключ. А в другой таблице есть столбец с таким же названием. Так вот их нужно соединить! Понятно? Понятно. Только вот, что делать, если у меня другая задача и другие таблицы? Кто вообще определяет, сколько у меня будет таблиц и что с чем соединять? Откуда в другой таблице взялся этот волшебный столбец? Что делать, если его там нет? На все эти вопросы «интуитивный» подход четкого ответа не дает. Он просто позволяет увидеть решение одной конкретной задачи.

    Я проходила через все это сама, когда была студенткой на специальности «Прикладная математика». Я прохожу через это уже 18 лет с каждой новой группой моих студентов, которых я обучаю проектированию баз данных. На прочтение Ульмана у них просто нет ни времени, ни энтузиазма – им пять дисциплин сдавать в сессию. Да и, что тут греха таить, математика сразу вызывает стресс. А метод «я нашел в интернете» не позволяет спроектировать любую базу данных на все случаи жизни, то есть он не универсален.

    За годы чтения курса баз данных на специальности «Прикладная информатика» я придумала методику проектирования, которая убивает двух зайцев сразу. С одной стороны совершенно не нужно продираться через несколько сотен страниц математики и падать в обморок от слов «кортеж», «алгебра», «нормальная форма», с другой стороны методика годится на все случаи жизни. Ну почти на все; -). Я даже рискну сказать ужасную для преподавателя вещь: если вы будете пользоваться этой методикой, вам не нужно будет думать, просто действуйте по алгоритму, и он сам приведет вас к результату. Ну что рискнем? Поехали!

    Почему не нужно бояться баз данных?

    Знаете почему не нужно бояться баз данных? Потому что в них нет ничего такого, чего нет в окружающем нас реальном мире. Вот смотрите, база данных проектируется для какой-то ограниченной части реального мира. Мы называем эту часть предметной областью. Например, «управление поликлиникой», «прием и отгрузка товара со склада», «управление кафе» – это все примеры предметных областей. База данных – это модель предметной области. А само слово «модель» означает, что допускаются определенные упрощения в сравнении с реальностью. В модели самолета нет таких деталей, которых нет в настоящем самолете. Наоборот, для упрощения некоторые детали в модели отсутствуют. Поэтому любая модель всегда будет проще реальности.

    Модель поликлиники всегда будет проще, чем реальная поликлиника, потому что при построении этой модель мы обязательно сочтем что-то несущественным и не станем включать в модель. Модель склада тоже будет проще реального склада. И так далее.

    Поэтому есть хорошая новость: база данных должна быть проще, чем предметная область, для которой мы ее строим. Ну а раз в реальной предметной области – в поликлинике, на складе, в кафе – работают живые люди, которые ничего не знают о проектировании баз данных, но все же как-то справляются со своими обязанностями, значит уж с упрощенной моделью этой реальности мы точно справимся. Нужно только понять, по каким правилам следует упрощать реальность.

    Урок по структуризации и проектированию баз данных

    База данных, профессионально спроектированная с помощью надежного инструмента схематизации, такого как Lucidchart, откроет своим пользователям доступ к важнейшей информации. Принципы, изложенные на этой странице, помогут вам спланировать базу данных, которая не только слаженно работает, но и отлично подстраивается под дальнейшие нужды вашего проекта. В этом уроке мы рассмотрим основные принципы построения баз данных, а также способы внесения ясности в структуру для достижения оптимальных результатов.

    Читается за 15 мин.

    Хотите создать схему базы данных самостоятельно? Попробуйте Lucidchart! Быстро, удобно и совершенно бесплатно.

    Процесс проектирования базы данных

    Хорошо структурированная база данных:

    • экономит место на диске, так как не содержит лишней информации
    • поддерживает целостность и точность данных
    • обеспечивает удобный доступ к данным

    Проектирование содержательной и эффективной базы данных — вопрос выполнения надлежащей процедуры, в которую входят следующие фазы:

    1. Анализ требований, или выявление цели базы данных
    2. Организация данных в таблицы
    3. Указание первичных ключей и анализ связей
    4. Нормализация и стандартизация таблиц

    Давайте подробнее рассмотрим каждый из этих этапов. Но сначала хотим обратить ваше внимание на то, что данный урок построен на примере модели реляционной базы данных Эдгара Кодда, написанной на SQL (в противопоставление иерархической, сетевой или информационной модели). Подробнее ознакомиться с разными моделями баз данных можно в нашем руководстве по этой ссылке.

    Анализ требований: выявление цели создания базы данных

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

    Вот несколько способов сбора информации для подготовки базы данных:

    • Опрос людей, которые будут ей пользоваться
    • Анализ стандартных деловых документов, например, счетов, табелей учета рабочего времени, заполненных анкет и так далее
    • Ознакомление с существующими системами данных (в бумажном и цифровом формате)

    Начните работу со сбора уже имеющейся информации, которая подлежит включению в базу данных. Затем составьте список всех видов данных, которые вы планируете хранить, а также сущностей или лиц, объектов, мест и событий, которые описывают эти данные, например:

    • Имя
    • Адрес
    • Город, регион, почтовый индекс
    • Адрес электронной почты
    • Имя
    • Цена
    • Количество на складе
    • Количество в заказе
    • Номер заказа
    • Консультант
    • Дата
    • Товар(ы)
    • КоличествоS
    • Цена
    • Общая стоимость

    Впоследствии эта информация войдет в состав словаря данных, где будет представлена сводка таблиц и полей внутри базы данных. Постарайтесь разбить информацию на мельчайшие функциональные фрагменты. Например, страну можно отделить от остальной части адреса, что позволит впоследствии отфильтровать людей по стране проживания. Также старайтесь не помещать одну и ту же точку данных более чем в одну таблицу, так как это излишне усложняет базу.

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

    Структура базы данных: строительные кирпичики

    На следующем этапе процесса вам предстоит составить наглядную картину своей базы данных. Для этого необходимо разобраться в том, как именно устроена реляционная база данных.

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

    Чтобы преобразовать списки данных в таблицы, в первую очередь создайте таблицу по каждому типу сущности, (товар, продажа, клиенты и так далее). Вот пример:

    Каждая строка таблицы называется записью. Записи содержат информацию о людях и объектах, например, о конкретном клиенте компании. В отличие от них, столбцы (которые также называют полями или атрибутами) содержат информацию одного типа, которая присутствует в каждой записи, например, адреса всех клиентов, перечисленных в таблице.

    Имя Фамилия Возраст Почтовый индекс
    Роджер Уильямс 43 34760
    Джеррика Йоргенсен 32 97453
    Саманта Хопкинс 56 64829

    Чтобы поддерживать постоянство формата всех записей, задайте каждому столбцу свой тип данных. Вот примеры р

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *