Как создать er диаграмму в mysql workbench

Основы работы с MySQL Workbench: быстрый старт, управление схемой данных

Веб-разработчик растёт вместе с проектами, которые он создаёт и развивает. С ростом проектов увеличивается сложность программной части, неизбежно возрастает количество обрабатываемых ею данных, а так же сложность схемы данных. Общение с другими вебщиками показывает, что огромной популярностью среди нас пользуются базы данных MySQL, а для управления ими — небезызвестный PHPMyAdmin. Переходя от маленьких проектов к большим, от cms к фреймворкам, многие, как и я, остаются верны MySQL. Однако для проектирования сложной базы данных с большим количеством таблиц и связей, возможностей PHPMyAdmin катастрофически не хватает. Поэтому я решил написать обзор MySQL Workbench — замечательной бесплатной десктопной программы для работы с MySQL.

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

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

Должен сказать, что программа действительно великолепная. Она позволяет быстро и с удовольствием накидывать схемы данных проекта, проектировать сущности и связи между ними, безболезненно внедрять изменения в схему и так же быстро и безболезненно синхронизировать её с удалённым сервером. А графический редактор EER-диаграмм, напоминающих забавных таракашек, позволяет увидеть общую картину модели данных и насладиться её лёгкостью и элегантностью 🙂 После первой же пробы этот инструмент становится незаменимым помощником в боевом арсенале веб-программиста.

Скачать MySQL Workbench

Дистрибутив MySQL Workbench доступен на этой странице. Самая свежая версия программы на момент написания статьи — Version 6.1. Перед скачиванием требуется выбрать одну из следующих платформ:

• Microsoft Windows (доступны MSI Installer и ZIP архив)
• Ubuntu Linux
• Fedora
• Red Hat Enterprise Linux / Oracle Linux
• Mac OS X

После выбора платформы вам предлагают зарегистрироваться или авторизоваться в Oracle. Если не хотите, внизу есть ссылка «No thanks, just start my download» — жмите на неё 😉

Начало работы

Стартовый экран программы отражает основные направления её функциональности — проектирование моделей баз данных и их администрирование:

В верхней части экрана находится список подключений к MySQL серверам ваших проектов, а список последних открытых моделей данных — в нижней части экрана. Работа обычно начинается с создания схемы данных или загрузки существующей структуры в MySQL Workbench. Приступим к работе!

Создание и редактирование модели данных

Для добавления модели нажимаем плюсик рядом с заголовком «Models» или выбираем «File → New Model» (Ctrl + N):

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

Добавление и редактирование таблицы

Список баз данных проекта и список таблиц в пределах базы данных будет располагаться во вкладке «Physical Schemas». Чтобы создать таблицу, дважды кликаем на «+Add Table»:

Откроется удобный интерфейс для редактирования списка полей и их свойств. Здесь мы можем задать название поля, тип данных, а так же установить для полей различные атрибуты: назначить поле первичным ключом (PK), пометить его Not Null (NN), бинарным (BIN), уникальным (UQ) и другие, установить для поля авто-инкремирование (AI) и значение по умолчанию (Default).

Управление индексами

Добавлять, удалять и редактировать индексы таблиц можно во вкладке «Indexes» интерфейса управления таблицей:

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

Связи между таблицами

Установка внешних ключей и связывание таблиц возможно только для таблиц InnoDB (эта система хранения данных выбирается по умолчанию). Для управления связями в каждой таблице находится вкладка «Foreign Keys»:

Для добавления связи открываем вкладку «Foreign Keys» дочерней таблицы, вводим имя внешнего ключа и выбираем таблицу-родителя. Далее в средней части вкладки в графе Column выбираем поле-ключ из дочерней таблицы, а в графе Referenced Column — соответствующее поле из родительской таблицы (тип полей должен совпадать). При создании внешних ключей в дочерней таблице автоматически создаются соответствующие индексы.

В разделе «Foreign Key Options» настраиваем поведение внешнего ключа при изменении соответствующего поля (ON UPDATE) и удалении (ON DELETE) родительской записи:

• RESTRICT — выдавать ошибку при изменении / удалении родительской записи
• CASCADE — обновлять внешний ключ при изменении родительской записи, удалять дочернюю запись при удалении родителя
• SET NULL — устанавливать значение внешнего ключа NULL при изменении / удалении родителя (неприемлемо для полей, у которых установлен флаг NOT NULL!)
• NO ACTION — не делать ничего, однако по факту эффект аналогичен RESTRICT

В приведённом примере я добавил к дочерней таблице UserProfile внешний ключ для связи с родительской таблицей User. При редактировании поля userId и удалении позиций из таблицы User аналогичные изменения будут автоматически происходить и со связанными записями из таблицы UserProfile.

Наполнение таблицы базовыми данными

При создании проекта в базу данных часто нужно добавлять стартовые данные. Это могут быть корневые категории, пользователи-администраторы и т.д. В управлении таблицами MySQL Workbench для этого существует вкладка «Inserts»:

Как видно из примера, в случае, если перед записью в базу данных к данным нужно применить какую-то функцию MySQL, это делается с помощью синтаксиса \func functionName(‘data’), например, \func md5(‘password’).

После ввода данных необходимо сохранить их в локальную базу данных нажатием на кнопку «Apply Changes».

Создание EER диаграммы (диаграммы «сущность-связь»)

Для представления схемы данных, сущностей и их связей в графическом виде в MySQL Workbench существует редактор EER-диаграмм. Для создания диаграммы в верхней части экрана управления базой данных дважды кликаем на иконку «+Add Diagram»:

В его интерфейсе можно создавать и редактировать таблицы, добавлять между ними связи различных типов. Чтобы добавить уже существующую в схеме таблицу на диаграмму, просто перетащите её из панели «Catalog Tree».

Для экспорта схемы данных в графический файл выберите «File → Export», а затем один из вариантов (PNG, SVG, PDF, PostScript File).

Импорт существующей схемы данных (из SQL дампа)

Если у нас уже есть схема данных, её можно без труда импортировать в MySQL Workbench для дальнейшей работы. Для импорта модели из SQL файла выбираем «File → Import → Reverse Engineer MySQL Create Script. «, после чего выбираем нужный SQL файл и жмём «Execute >»

В MySQL Workbench так же предусмотрен импорт и синхронизация модели данных нарямую с удалённым сервером. Для этого потребуется создать подключение удалённого доступа к MySQL, о которых я расскажу в продолжении данного обзора.

Демо-проект из статьи доступен для скачивания по этой ссылке. Желаю успехов и красивых таракашек схем!

Как увидеть диаграмму в MySQL WorkBench по готовой БД со всеми связями?

Как увидеть диаграмму в MySQL WorkBench по готовой БД со всеми связями?

• Вопрос задан более трёх лет назад
• 31664 просмотра

Комментировать

Решения вопроса 1

Алексей Уколов @alexey-m-ukolov Куратор тега MySQL

Нужно создать новую модель, в меню выбрать Database -> Reverse Engineer. Дальше там более-менее понятно.

P.S. Ужасная, глючная неинтуитивная программа. По крайней мере под Линуксом.

Ответ написан более трёх лет назад

Комментировать

Нравится 13 Комментировать

Ответы на вопрос 0

Ваш ответ на вопрос

Войдите, чтобы написать ответ

• PHP
• +1 ещё

Почему постраничный вывод данных из бд не работает?

• 1 подписчик
• 11 часов назад
• 58 просмотров

Пример проектирования базы данных 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/

  Как сгененрировать "Реляционную схему базы данных" в mysql workbench?

  Пытаюсь выявить связи в существующей системе CMS для интеграции ее в стороннее решение, столкнулся с проблемой труднодоступности выстраивания связей в ACL. В связи с чем хочу узнать, как получить реляционную схему таблиц для того, чтобы быстрее ориентироваться и интегрировать данную систему. Пока нашел только вот это https://www.mysql.com/products/workbench/design/ Но там не слова о генерации на основе уже существующей базы, чтобы можно было удобнее всего отсмотреть связи.

  Отслеживать
  задан 21 авг 2017 в 9:30
  Dima Vasiluk Dima Vasiluk
  2,346 3 3 золотых знака 19 19 серебряных знаков 47 47 бронзовых знаков

  там не слова о генерации на основе уже существующей базы, чтобы можно было удобнее всего отсмотреть связи. dev.mysql.com/doc/workbench/en/wb-reverse-engineer-live.html

  21 авг 2017 в 9:58
  Печаль беда, а вообще был у кого-то подобный опыт?
  21 авг 2017 в 10:04

  В справке описывается процесс построения EER-диаграммы для учебной БД sakila. Не вижу оснований полагать, что для другой БД тот же процесс не сработает.

  Похожие публикации:

  1. Как включить проверку орфографии в аутлуке
  2. Как смотреть евроспорт через vpn
  3. Как установить программу на внешний жесткий диск
  4. Подскажите куда нажать фф