Проектирование и реализация баз данных в СУБД MySQL с использованием MySQL Workbench

ПРОЕКТИРОВАНИЕ И РЕАЛИЗАЦИЯ
БАЗ ДАННЫХ В СУБД MYSQL
С ИСПОЛЬЗОВАНИЕМ
MYSQL WORKBENCH

МЕТОДЫ И СРЕДСТВА ПРОЕКТИРОВАНИЯ
ИНФОРМАЦИОННЫХ СИСТЕМ И ТЕХНОЛОГИЙ

ИНСТРУМЕНТАЛЬНЫЕ СРЕДСТВА 
ИНФОРМАЦИОННЫХ СИСТЕМ

С.А. Мартишин, В.Л. Симонов,
М.В. Храпченко

Рекомендовано Учебно-методическим советом СПО в качестве учебного пособия 
для студентов учебных заведений, реализующих программу среднего профессионального 
образования по специальностям 09.02.01 «Компьютерные системы и комплексы», 
09.02.02 «Компьютерные сети», 09.02.03 «Программирование в компьютерных системах», 
09.02.04 «Информационные системы (по отраслям)», 09.02.05 «Прикладная 
информатика (по отраслям)», 09.02.06 «Сетевое и системное администрирование», 
09.02.07 «Информационные системы и программирование»

УЧЕБНОЕ ПОСОБИЕ

Москва 
ИД «ФОРУМ» — ИНФРА-М
2020

УДК 004.6(075.32) 
ББК 32.973я723 
 
М29

Мартишин С.А.
М29  
Проектирование и реализация баз данных в СУБД MySQL с использованием MySQL Workbench. Методы и средства проектирования информационных систем и технологий. Инструментальные средства информационных систем : учебное пособие / С.A. Мартишин, В.Л. Симонов, М.В. Храпченко. — Москва : ИД «ФОРУМ» : ИНФРА-М, 2020. — 
160 с. — (Среднее профессиональное образование).

ISBN 978-5-8199-0811-2 (ИД «ФОРУМ»)
ISBN 978-5-16-014145-9 (ИНФРА-М, print)
ISBN 978-5-16-106960-8 (ИНФРА-М, online)

Пособие предназначено для получения практических навыков при 
изучении дисциплин «Управление данными», «Технологии обработки 
информации», «Инструментальные средства информационных систем», 
«Методы и средства проектирования информационных систем и технологий». Также может быть использовано при изучении дисциплин «Проектирование программного обеспечения автоматизированных систем обработки информации и управления», «Проектирование информационных 
систем», «Базы данных».
Для студентов учреждений среднего профессионального образования. 
Полезно для студентов вузов, слушателей курсов повышения квалификации и лиц, занимающихся самообразованием.

УДК 004.6(075.32)
ББК 32.973я723

Р е ц е н з е н т ы:
В.И. Гончаренко — кандидат технических наук, доцент, директор военного института при ФГБОУ ВО г. Москвы «Московский авиационный институт (национальный исследовательский университет)»;
В.А. Сизов — доктор технических наук, профессор кафедры моделирования информационных систем и сетей ФГБОУ ВО г. Москвы «Российский социальный университет»;
Н.Л. Прохоров — доктор технических наук, профессор, научный руководитель ОАО «Институт электронных управляющих машин имени И.С. Брука»

ISBN 978-5-8199-0811-2 (ИД «ФОРУМ»)
ISBN 978-5-16-014145-9 (ИНФРА-М, print)
ISBN 978-5-16-106960-8 (ИНФРА-М, online)

© Мартишин С.А., Симонов В.Л., 
Храпченко М.В., 2012
© ИД «ФОРУМ», 2012

Список сокращений и понятий

ANSI (American National Standards Institute, ANSI) — американский национальный институт стандартов.
CASE (ComputerAided Software Engineering) — набор инструментов и методов программной инженерии, позволяющий автоматизировать процесс разработки программного обеспечения.
DDL (Data Definition Language — язык описания данных) — семейство компьютерных языков, используемых в компьютерных программах для описания структуры баз данных.
EERмодель (Enhanced EntityRelationship) — отличается от
ERмодели наличием понятий класса и суперкласса, специализация и
обобщение, а также понятием «объединение» или «категория», служащего для представления совокупности объектов, которая объединяет
объекты различных типов сущностей.
ERмодель (EntityRelationship) — модель сущность—связь используется при концептуальном проектировании баз данных.
FK (Foreign Key) — внешний ключ.
IDEF
(Icam
DEFinition
или
другой
вариант
—
Integrated
DEFinition) — методологии семейства ICAM (Integrated ComputerAided Manufacturing), созданные для эффективного обмена информацией между всеми специалистами — участниками программы
ICAM.
InnoDB — одна из выбираемых подсистем низкого уровня в СУБД
MySQL.
MyISAM — одна из основных (наряду с InnoDB) систем хранения
данных в СУБД MySQL.
ODBC (Open Database Connectivity) — программный интерфейс
доступа к базам данных.
PK (Primary Key) — первичный ключ.
SQL (Structured Query Language — «язык структурированных запросов») — универсальный компьютерный язык, применяемый для

создания, модификации и управления данными в реляционных базах
данных.
SSH (Secure SHell — «безопасная оболочка») — сетевой протокол
сеансового уровня, позволяющий производить удаленное управление
операционной системой и туннелирование TCPсоединений (например, для передачи файлов).
SSL (Secure Sockets Layer — уровень защищенных сокетов) —
криптографический протокол, который обеспечивает установление
безопасного соединения между клиентом и сервером.
Атрибут в ERмодели — любая характеристика сущности, значимая для рассматриваемой предметной области и предназначенная для
квалификации, идентификации, классификации, количественной характеристики или выражения состояния сущности.
БД — база данных.
Обратный инжиниринг — восстановление структуры уже существующей на сервере базы данных.
ОС — операционная система.
ПО — программное обеспечение.
Прямой инжиниринг — создание физической модели.
СПО — свободное программное обеспечение.
СУБД — система управления базами данных.
Связь (Relationship) в ERмодели — это поименованная ассоциация между двумя сущностями, значимая для рассматриваемой предметной области.
Сущность (Entity) — в ERмодели реальный либо воображаемый
объект, имеющий существенное значение для рассматриваемой предметной области, информация о котором подлежит хранению (указанное справедливо только в ERмодели).

4
Список сокращений и понятий

Предисловие

В настоящее время отсутствует методическое пособие, которое
бы полностью описывало весь процесс проектирования, реализации
и администрирования базы данных в СУБД MySQL с использованием
MySQL Workbench. Преимуществом использования СУБД MySQL в
сочетании с MySQL Workbench является, с одной стороны, простота
установки и полная согласованность программного обеспечения, используемого для проектирования и реализации баз данных. С другой
стороны, рассматриваемое ПО является свободно распространяемым
(СПО) и может функционировать как под операционной системой
Windows, так и под ОС Linux.
Заметим также, что единая среда, позволяющая проектировать,
реализовывать и администрировать базу данных, значительно упрощает процесс создания конечного продукта и снижает временные затраты разработчика, повышая наглядность его работы.
Приведенные в пособии примеры могут быть использованы для
курсового и дипломного проектирования, а также во многих разработках, связанных с созданием информационных и автоматизированных систем и интернетсайтов.

Пособие рассчитано на студентов, обучающихся по направлениям 
укрупненной группы специальностей 09.00.00 «Информатика и вычислительная техника».
Пособие предназначено для получения практических навыков 
при изучении дисциплин: «Управление данными», «Технологии обработки информации», «Инструментальные средства информационных 
систем», «Методы и средства проектирования информационных 
систем и технологий». Также может быть использовано при изучении 
дисциплин: «Проектирование программного обеспечения автоматизированных систем обработки информации и управления», «Проектирование информационных систем», «Базы данных».
Изучению материала пособия должно предшествовать знание такого материала, как основы реляционных баз данных, нотации IDEF, 

Предисловие

протокол TCP/IP, изучаемых в курсах «Информационные технологии», «Инфокоммуникационные системы и сети».
Пособие может быть с успехом использовано аспирантами, специалистами в области автоматизированной обработки информации 
и управления, а также лицами, занимающимися самообразованием.

Авторы

Введение

СУБД MySQL — это система управления реляционными базами
данных. Программное обеспечение MySQL — это ПО с открытым кодом. На вебсайте MySQL (http://www.mysql.com) представлена самая
последняя информация о программном обеспечении MySQL. Код написан на C и C++. СУБД может быть установлена на все операционные системы с работающими потоками Posix и компилятором C++.
MySQL поддерживает большое число типов данных столбцов, практически все стандарты функций ANSI SQL, а также программный интерфейс доступа к базам данных ODBC. MySQL позволяет управлять
очень большими базами данных (более 50 миллионов записей). Система основана на привилегиях и паролях, за счет чего обеспечивается
гибкость и безопасность, а также возможность верификации с удаленного компьютера. Пароли являются защищенными, поскольку
при передаче по сети при соединении с сервером они шифруются.
Очевидно, что в настоящее время создание баз данных невозможно без их тщательного проектирования с применением CASEсредств.
Использование современных CASEсредств для проектирования различного рода программных продуктов на сегодняшний день связано
не просто с необходимостью облегчить труд разработчика ПО, но и с
необходимостью иметь инструмент, позволяющий также стандартизировать процесс разработки, получить наглядные графические модели разрабатываемого ПО, повторно использовать разработки и иметь
возможность разделения огромных по объему задач между разработчиками.
При разработке баз данных часто в связи с большим объемом информации, необходимой для хранения, возникают сложности даже с
построением логической модели, не говоря уже о физической модели, в которой необходимо учесть возможности конкретной системы
управления баз данных (СУБД). В связи с этим многие СУБД создают
свои CASEсредства, позволяющие разработчику в значительной степени автоматизировать процессы моделирования, проектирования и

реализации баз данных. В настоящий момент в состав пакета разработчика помимо СУБД MySQL входит CASEсредство Workbench, которое также может быть получено на сайте (http://www.mysql.com) и
установлено. Его использование в значительной мере упрощает и делает более наглядным процесс проектирования баз данных.
MySQL Workbench — инструмент для визуального проектирования баз данных, который позволяет проектировать, моделировать,
создавать и эксплуатировать БД. С его помощью можно представить
модель базы данных в графическом виде, устанавливать связи между
таблицами, восстанавливать структуры уже существующей на сервере
БД (обратный инжиниринг), использовать редактор SQL запросов,
позволяющий сразу же отправлять их на сервер и получать ответ в
виде таблицы, редактировать данные в таблице в визуальном режиме.
Следует заметить, что основные принципы работы с MySQL
Workbench остаются неизменными вне зависимости от того, установлена ли СУБД на ОС Windows или ОС Linux. Различия в большей степени касаются коммерческой и свободно распространяемой версий.
Некоторые возможности доступны только для коммерческого варианта. Также более новые версии могут незначительно отличаться от
предыдущих, например иметь небольшие расхождения в наименовании пунктов меню или экранов. Однако основные подходы разработки и реализации не изменяются.
В цикле лабораторных работ будут рассмотрены основные возможности использования MySQL Workbench для администрирования, проектирования и реализации баз данных, в том числе реализация баз данных традиционным способом при помощи создания
таблиц и связей между ними, а также при помощи построения
ERмоделей.

8
Введение

Общие сведения о ERмоделях

При построении баз данных CASEсредства чаще всего ориентированы на два уровня представления модели — логический и физический. Логическая модель данных является универсальной и никак не
связана с конкретной реализацией СУБД.
Физическая модель данных, напротив, зависит от конкретной
СУБД, фактически являясь отображением системного каталога.
В физической модели содержится информация обо всех объектах БД.
Поскольку стандартов на объекты БД не существует (например, хотя
имеется стандарт основных типов данных, в каждой конкретной
СУБД могут использоваться дополнительно и свои типы данных),
физическая модель зависит от используемой СУБД. Следовательно,
одной и той же логической модели могут соответствовать несколько
различных физических моделей.
Создание модели данных, как правило, начинается с проектирования логической модели. Для проектирования могут использоваться различные инструменты, однако в последнее время наиболее часто применяются CASEсредства (Computer Aided Software Engineering) —
средства для автоматизации проектирования программных продуктов.
Как правило, CASEсредства предлагают построение ERмоделей. ERмодель или модель «Сущность—связь» — это аббревиатура от
английских слов Entity—Relationship. Приведем определения основных понятий модели, как они даны в [2].
Сущность (Entity) — реальный либо воображаемый объект, имеющий существенное значение для рассматриваемой предметной области, информация о котором подлежит хранению.
В ERмодели каждая сущность должна обладать уникальным идентификатором. Каждый экземпляр сущности должен однозначно идентифицироваться и отличаться от всех других экземпляров данного типа
сущности. Каждая сущность должна обладать некоторыми свойствами:
• каждая сущность должна иметь уникальное имя, и к одному и
тому же имени должна всегда применяться одна и та же интерпретация. Одна и та же интерпретация не может применяться к
различным именам, если только они не являются псевдонимами;
• сущность обладает одним или несколькими атрибутами, которые либо принадлежат сущности, либо наследуются через связь;
• сущность обладает одним или несколькими атрибутами, которые однозначно идентифицируют каждый экземпляр сущности;
• каждая сущность может обладать любым количеством связей с
другими сущностями модели.
Атрибут — любая характеристика сущности, значимая для рассматриваемой предметной области и предназначенная для квалификации, идентификации, классификации, количественной характеристики или выражения состояния сущности. Атрибут представляет тип
характеристик или свойств, ассоциированных с множеством реальных или абстрактных. Экземпляр атрибута — это определенная характеристика отдельного элемента множества. В ERмодели атрибуты ассоциируются с конкретными сущностями. Таким образом, экземпляр
сущности должен обладать единственным определенным значением
для ассоциированного атрибута.
Связь (Relationship) — поименованная ассоциация между двумя
сущностями, значимая для рассматриваемой предметной области.
Связь — это ассоциация между сущностями, при которой, как правило, каждый экземпляр одной сущности, называемой родительской
сущностью, ассоциирован с произвольным (в том числе нулевым) количеством экземпляров второй сущности, называемой сущностьюпотомком, а каждый экземпляр сущностипотомка ассоциирован в точности с одним экземпляром сущностиродителя. Таким образом, экземпляр сущностипотомка может существовать только при
существовании сущности родителя.
Связи может даваться имя, выражаемое грамматическим оборотом глагола и помещаемое возле линии связи. Имя каждой связи между двумя данными сущностями должно быть уникальным, но имена
связей в модели не обязаны быть уникальными. Имя связи всегда
формируется с точки зрения родителя, так что предложение может
быть образовано соединением имени сущностиродителя, имени связи, выражения степени и имени сущностипотомка.
Существуют следующие типы связи: одинкодному, одинкомногим и многиекомногим. Связь одинкодному отображает такой характер связей между сущностями, когда каждому экземпляру
одной сущности соответствует только один экземпляр другой, и на10
Введение

оборот. Наиболее часто встречающимся типом связи в реляционных
СУБД является связь одинкомногим. Данный тип связи возникает,
когда один экземпляр первой сущности может быть связан с одним
или большим количеством экземпляров второй сущности. Связь многиекомногим описывает случай, когда экземпляры одной сущности
связаны с одним или большим количеством экземпляров второй сущности, и наоборот, экземпляры второй сущности связаны с одним и
большим количеством экземпляров первой сущности. Связь многиекомногим чаще всего используется на начальной стадии проектирования. В дальнейшем при нормализации она чаще всего разбивается при помощи вспомогательных таблиц на несколько связей
одинкомногим.
Для создания ERдиаграмм обычно используют одну из двух наиболее распространенных нотаций.
• IDEF1X — усовершенствованная версия IDEF1 (методологии
структурного анализа для проектирования сложных ИС, разработанный Т. Рэмей (T. Ramey)) и позволяющая разрабатывать
концептуальную модель предметной области системы баз данных в форме одной или нескольких ERдиаграмм, эквивалентных отношениям в третьей нормальной форме. Помимо того,
что эта нотация стала федеральным стандартом США, она также является стандартом в ряде международных организаций
(например, Международный валютный фонд и др.) [3, 4];
• Information Engineering (IE). Нотация, разработанная Мартином
(Martin), Финкельштейном (Finkelstein) и др., используется
преимущественно в промышленности.
Заметим, что IDEF1X (см. приложение С) является методом для
разработки реляционных баз данных и использует нотацию, специально разработанную для удобного построения концептуальной схемы — универсального представления структуры данных в рамках рассматриваемой предметной области, независимого от конечной реализации базы данных и аппаратной платформы. Использование метода
IDEF1X наиболее целесообразно для построения логической структуры базы данных.
Основным преимуществом IDEF1X, по сравнению с другими
многочисленными методами разработки реляционных баз данных,
является жесткая и строгая стандартизация моделирования.
Как было сказано выше, IDEF1X используется для моделирования реляционных баз данных и имеет в США статус федерального
стандарта. Стандарт входит в семейство методологий IDEF (методоОбщие сведения о ERмоделях
11

логии семейства ICAM (Integrated ComputerAided Manufacturing) для
решения задач моделирования сложных систем), позволяющих исследовать структуру, параметры и характеристики производственнотехнических и организационноэкономических систем. Методология IDEF1X адаптирована для совместного использования с IDEF0
(моделирование бизнеспроцессов) в рамках единой технологии моделирования. То есть в рамках IDEF0 детализируются функциональные блоки, а в рамках IDEF1X детализируются стрелки, взаимодействующие с функциями.
Сущность описывается в диаграмме IDEF1X графическим объектом в виде прямоугольника. Каждый прямоугольник, отображающий
сущность, разделяется горизонтальной линией на часть, в которой
расположены ключевые поля, и часть, где расположены остальные
атрибуты, не являющиеся ключевыми полями. Верхняя часть называется ключевой областью, а нижняя часть — областью данных. В ключевую область помещают первичный ключ сущности, т. е. атрибуты
первичного ключа располагаются над линией в ключевой области.
Напомним, что первичный ключ — это набор атрибутов, выбранных
для идентификации уникальных экземпляров сущности. Остальные
атрибуты (неключевые) помещают в области данных (под чертой).
В нотации IDEF1X сущности всегда имеют ключевую область, соответственно, в каждой сущности должны быть определены первичные ключи (ключевые атрибуты).
Определение первичного ключа для сущности при проектировании
модели данных является очень важным, поскольку, помимо идентификации сущности, от его выбора зависит удобство дальнейшей работы с данными (вопросы нормализации здесь обсуждаться не будут).
В качестве первичных ключей могут быть использованы один атрибут
или группа атрибутов. В начале процесса проектирования, как правило, имеется несколько атрибутов, которые могут быть выбраны первичными ключами. Их называют потенциальными ключами. К потенциальным ключам предъявляется требование уникальной идентификации каждого экземпляра сущности. Соответственно, ни одна из
частей ключа не может принимать неопределенное значение (NULL).
В некоторых случаях при разработке модели удобно использовать
так называемый суррогатный ключ, т. е. вводят искусственно некоторый атрибут, который уникальным образом определяет экземпляр
сущности. Примером такого искусственно введенного ключа может
являться атрибут «Номер по порядку» или «Номер записи». Именно такой ключ лучше всего подходит на роль первичного ключа, потому что

12
Введение