Лабораторная работа: Создание логической модели БД

Цель работы:

• ознакомиться с технологией построения логической модели в ERWin,

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

• освоить метод проверки адекватности логической модели,

• изучить типы связей между сущностями.

Основные понятия модели «Сущность-связь». ER-модели.
Инфологическая модель применяется на втором этапе проектирования БД, после словесного описания предметной области. Она должна включать такое формализованное описание предметной области, которое легко будет «читаться» как специалистами по базам данных, так и всеми пользователями. И это описание должно быть настолько емким, чтобы можно было оценить глубину и корректность проработки проекта БД, и конечно, оно не должно быть привязано к конкретной СУБД. Выбор СУБД — это отдельная задача, для корректного ее решения необходимо иметь проект, который не привязан ни к какой конкретной СУБД.
Инфологическое проектирование прежде всего связано с попыткой представления семантики предметной области в модели БД, которая слабо отражается в сетевых, иерархических моделях данных.
Было предложено несколько моделей данных, названных семантическими моделями. У всех этих моделей были свои положительные и отрицательные стороны, но испытание временем выдержала только модель «сущность—связь», или Entity Relationships, которая стала фактическим стандартом при инфологическом моделировании баз данных. Общепринятым стало сокращенное название ER-модель, а большинство современных CASE-средств содержат инструментальные средства для описания данных в формализме этой модели. Кроме того, разработаны методы автоматического преобразования проекта БД из ER-модели в реляционную БД, при этом одновременно выполняется преобразование в модель конкретной СУБД. Все CASE-системы имеют развитые средства документирования процесса разработки, автоматические генераторы отчетов позволяют подготовить отчет о текущем состоянии проекта с подробным описанием объектов БД и их отношений, что существенно облегчает ведение проекта.
В настоящий момент не существует единой общепринятой системы обозначений для ER – модели, и разные CASE-системы используют разные графические нотации, но разобравшись с одной, можно легко понять и другие.
Как любая модель, модель «сущность—связь» имеет несколько базовых понятий, которые образуют исходные кирпичики, из которых строятся уже более сложные объекты по заранее определенным правилам.
Эта модель в наибольшей степени согласуется с концепцией объектно-ориентированного проектирования, которая несомненно является базовой для разработки сложных программных систем, поэтому многие понятия могут показаться знакомыми, и если это так, то тем проще вам будет освоить технологию проектирования баз данных, основанную на ER-модели.
Базовые понятия ER-модели.
Рассмотрим базовые понятия, лежащие в основе ER-модели
1. Сущность, с помощью которой моделируется класс однотипных объектов. Сущность имеет имя, уникальное в пределах моделируемой системы. Так как сущность соответствует некоторому классу однотипных объектов, то предполагается, что в системе существует множество экземпляров данной сущности. Объект, которому соответствует понятие сущности, имеет свой набор атрибутов — характеристик, определяющих свойства данного представителя класса. При этом набор атрибутов должен быть таким, чтобы можно было различать конкретные экземпляры сущности. Например, у сущности Сотрудник может быть следующий набор атрибутов: Табельный номер, Фамилия, Имя, Отчество, Дата рождения, Количество детей, Наличие родственников за границей. Набор атрибутов, однозначно идентифицирующий конкретный экземпляр сущности, называют ключевым. Для сущности Сотрудник ключевым будет атрибут Табельный номер, поскольку для всех сотрудников данного предприятия табельные номера различны. Экземпляром сущности Сотрудник будет описание конкретного сотрудника предприятия. Одно из общепринятых графических обозначений сущности — прямоугольник, в верхней части которого записано имя сущности, а ниже перечисляются атрибуты, причем ключевые атрибуты помечаются,например, подчеркиванием или специальным шрифтом, как показано ниже:

СОТРУДНИК

Табельный номер Фамилия Имя Отчество Количество детей Должность

2. Между сущностями могут быть установлены связи —бинарные ассоциации, показывающие, каким образом сущности соотносятся или взаимодействуют между собой. Связь может существовать между двумя разными сущностями или между сущностью и ей же самой (рекурсивная связь). Она показывает, как связаны экземпляры сущностей между собой. Еслисвязь устанавливается между двумя сущностями, то она определяет взаимосвязь между экземплярами одной и другой сущности. Например, если у насесть связь между сущностью «Студент» и сущностью «Преподаватель» и эта связь — руководстводипломными проектами, то каждый студент имеет только одного руководителя, но один и тот же преподаватель может руководить множеством студентов-дипломников. Поэтому это будет связь «один-ко-многим» (1:М), один со стороны «Преподаватель» и многие со стороны «Студент» (рис. 1.).
3. В разных нотациях мощность связи изображается по-разному. В рассмотренном примере используется нотация CASE системы POWER DESIGNER; здесь множественность изображается путем разделения линии связи на 3. Связь имеет общее имя «Дипломное проектирование» и имеет имена ролей со стороны обеих сущностей. Со стороны студента эта роль называется «Делает проект под руководством», со стороны преподавателя эта связь называется «Руководит». Графическая интерпретация связи позволяет сразу прочитать смысл взаимосвязи между сущностями, она наглядна и легко интерпретируема. Связи делятся на три типа по множественности: один-к-одному (1:1), один-ко-многим (1:М), многие-ко-многим (М:М). Связь один-к-одному означает, что экземпляр одной сущности связан только с одним экземпляром другой сущности. Связь 1: М означает, что один экземпляр сущности, расположенный слева по связи, может быть связан с несколькими экземплярами сущности, расположенными справа по связи. Связь «многие-ко-многим» (М:М) означает, что один экземпляр первой сущности может быть связан с несколькими экземплярами второй сущности, и наоборот, один экземпляр второй сущности может быть связан с несколькими экземплярами первой сущности. Например, связь типа «Изучает» между сущностями «Студент» и «Дисциплина» есть связь типа «многие-ко-многим» (М:М), т. к. каждый студент может изучать несколько дисциплин, а каждая дисциплина изучается множеством студентом. Такая связь изображена на рис. 2.

рис. 1

4. Между двумя сущностями может быть задано сколько угодно связей с разными смысловыми нагрузками. Например, между двумя сущностями «Студент» и «Преподаватель» можно установить две смысловые связи, одна — рассмотренная уже ранее «Дипломное проектирование», а вторая может быть условно названа «Лекции», и она определяет, лекции каких преподавателей слушает данный студент и каким студентам данный преподаватель читает лекции. Ясно, что это связь типа многие-ко-многим.
5. Связь любого из этих типов может быть обязательной, если в данной связи должен участвовать каждый экземпляр сущности, и необязательной — если не каждый экземпляр сущности должен участвовать в данной связи. При этом связь может быть обязательной с одной стороны и необязательной с другой стороны. Обязательность связи тоже по-разному обозначается в разных нотациях. Мы снова используем нотацию POWER DESIGNER. Здесь необязательность связи обозначается пустым кружочком на конце связи, а обязательность перпендикулярной линией, перечеркивающей связь. И эта нотация имеет простую интерпретацию. Кружочек означает, что ни один экземпляр не может участвовать в этой связи. А перпендикуляр интерпретируется как то, что по крайней мере один экземпляр сущности участвует в этой связи.

рис. 2

Рассмотрим для этого ранее приведенный пример связи «Дипломное проектирование». На рисунке эта связь интерпретируется как необязательная с двух сторон. Но ведь на самом деле каждый студент, который делает диплом, должен иметь своего руководителя дипломного проектирования, но, с другой стороны, не каждый преподаватель ведет дипломное проектирование. Поэтому в данной смысловой постановке изображение этой связи изменится и будет выглядеть таким, как представлено на рис. 3.

рис. 3

В результате построения модели предметной области в виде набора сущностей и связей получаем связный граф. В полученном графе необходимо избегать циклических связей — они выявляют некорректность модели.
Пример создания ER-модели.
Спроектируем инфологическую модель системы, предназначенной для хранения информации о книгах и областях знаний, представленных в библиотеке. Разработку модели начнем с выделения основных сущностей.
Прежде всего, существует сущность «Книги»; каждая книга имеет уникальный шифр, который является ее ключом, и ряд атрибутов, которые взяты из описания предметной области. Множество экземпляров сущности определяет множество книг, которые хранятся в библиотеке. Каждый экземпляр сущности «Книги» соответствует не конкретной книге, стоящей на полке, а описанию некоторой книги, которое дается обычно в предметном каталоге библиотеке. Каждая книга может присутствовать в нескольких экземплярах, и это как раз те конкретные книги, которые стоят на полках библиотеки. Чтобы отразить это, следует ввести сущность «Экземпляры», которая должна содержать описания всех экземпляров книг, которые хранятся в библиотеке. Каждый экземпляр сущности «Экземпляры» соответствует конкретной книге на полке. Каждый экземпляр имеет уникальный инвентарный номер, однозначно определяющий конкретную книгу. Кроме того, каждый экземпляр книги может находиться либо в библиотеке, либо на руках у некоторого читателя, и в последнем случае для данного экземпляра указываются дополнительно дата взятия книги читателем и дата предполагаемого возврата книги.
Между сущностями «Книги» и «Экземпляры» существует связь (1:М), обязательная с двух сторон. Чем определяется данный тип связи? Каждая книга может присутствовать в библиотеке в нескольких экземплярах, поэтому - связь 1:М. При этом если в библиотеке нет ни одного экземпляра данной книги, то мы не будем хранить ее описание, поэтому если книга описана в сущности«Книги», то по крайней мере один экземпляр этой книги присутствует в библиотеке.Это означает, что со стороны книги связь обязательная. Что касается сущности «Экземпляры», то не может существовать в библиотеке ни одного экземпляра, который бы не относился к конкретной книге, поэтому и со стороны «Экземпляры» связь тоже обязательная.
Теперьнеобходимо определить, как в системе будет представлен читатель. Естественно предложитьввести для этого сущность «Читатели», каждый экземпляр которой будет соответствовать конкретному читателю. В библиотеке каждому читателю присваивается уникальный номер читательского билета, который однозначно идентифицирует читателя. Номер читательского билета будет ключевым атрибутом сущности «Читатели». Кроме того, в сущности «Читатели» должны присутствовать дополнительные атрибуты, которые требуются для решения поставленных задач; этими атрибутами будут: «Фамилия Имя Отчество», «Адрес читателя», «Телефон домашний» и «Телефон рабочий». Кроме того в сущности «Читатели» следует ввести атрибут «Дата рождения», который позволитконтролировать возраст читателей.
Каждый читательможет держать на руках несколько экземпляров книг. Для отражения этой ситуации следует провести связь между сущностями «Читатели» и «Экземпляры», т. к. читатель берет из библиотеки конкретный экземпляр конкретной книги, а не просто книгу. А узнать, какая книга у данного читателя можно по дополнительной связи между сущностями «Экземпляры» и«Книги», и эта связь каждому экземпляру ставит в соответствие одну книгу, поэтому всегда можно однозначно определить, какие книги находятся на руках у читателя, хотя связываем с читателем только инвентарные номера взятых книг. Между сущностями «Читатели» и «Экземпляры» установлена связь 1:М, и при этом она не обязательная с двух сторон. Читатель в данный момент может не держать ни одной книги на руках, а с другой стороны, данный экземпляр книги может не находиться ни у одного читателя, а просто стоять на полке в библиотеке.
Теперь следует отразить последнюю сущность, связанную с системным каталогом, который содержит перечень всех областей знаний, сведения по которым содержатся в библиотечных книгах. Название области знаний может быть длинным и состоять из нескольких слов, поэтому для моделирования системного каталога введем сущность «Системный каталог» с двумя атрибутами: «Код области знаний» и «Название области знаний». Атрибут «Код области знаний» будет ключевым атрибутом сущности.
Из описания предметной областиизвестно, что каждая книга может содержать сведения из нескольких областей знаний, а с другой стороны, в библиотеке может присутствовать множество книг, относящихся к одной и той же области знаний, поэтому необходимо установить между сущностями «Системный каталог» и «Книги» связь М:М, обязательную с двух сторон. Действительно, в системном каталоге не должно присутствовать такой области знаний, сведения по которой не представлены ни в одной книге библиотеки. И обратно, каждая книга должна быть отнесена к одной или нескольким областям знаний для того, чтобы читатель мог ее быстрее найти.
ER - модель предметной области «Библиотека» представлена на рис. 4.

рис. 4

Инфологическая модель «Библиотека» разработана под задачи, перечисленные ранее. В них нет условия хранения истории чтения книги, например, с целью поиска того, кто раньше держал книгу и мог нанести ей вред. Если бы была поставлена задача хранения и этой информации, то инфологическая модель была бы другой.
Нормализация ER-диаграмм.
Инфологическая модель используется на ранних стадиях разработки проекта. Если понимать язык условных обозначений, которые соответствуют категориям ER-модели, то ее можно легко «читать», следовательно, она доступна для анализа программистам-разработчикам, которые будут разрабатывать отдельные приложения. Она имеет однозначную интерпретацию, в отличие от некоторых предложений естественного языка, и поэтому здесь не может быть никакого недопонимания со стороны разработчиков.
Специалисты всегда предпочитают выражать свои мысли на некотором формальном языке, который обеспечивает однозначную их трактовку. Таким языком для программистов является язык алгоритмов. Любой алгоритм имеет однозначную интерпретацию. Он реализуется на разных языках программирования, но сам алгоритм остается неизменным. Для описания алгоритмов используются разные формализмы.
Условным общепринятымязыком описания базы данных стал язык ER-модели. Для ER-модели существует алгоритм однозначного преобразования ее в реляционную модель данных, что позволило в дальнейшем разработать множество инструментальных систем, поддерживающих процесс разработки информационных систем, базирующихся на технологии баз данных. И во всехэтихсистемах существуют средства описания инфологической модели разрабатываемой БД с возможностью автоматической генерации той даталогической модели, на которой будет реализовываться проект в дальнейшем.
Правила преобразования ER-модели в реляционную БД
Рассмотрим правила преобразования ER-модели в реляционную БД.
1. Каждой сущности ставится в соответствие отношение реляционной модели данных. При этом имена сущности и отношения могут быть различными, т. к. на имена сущностей могут не накладываться дополнительные синтаксические ограничения, кроме уникальности имени в рамках модели. Имена отношений могут быть ограничены требованиями конкретной СУБД, чаще всего эти имена являются идентификаторами в некотором базовом языке, они ограничены по длине и не должны содержать пробелов и некоторых специальных символов. Например, сущность может быть названа«Книжный каталог», а соответствующее ей отношение желательно назвать, например, BOOKS (без пробелов и латинскими буквами).
2. Каждый атрибут сущности становится атрибутом соответствующего отношения. Переименование атрибутов должно происходить в соответствии с теми же правилами, что и переименование отношений в п.1. Для каждого атрибута задается конкретный допустимый в СУБД тип данных и обязательность или необязательность данного атрибута.
3. Первичный ключ сущности становится PRIMARY KEY соответствующего отношения. Атрибуты, входящие в первичный ключ отношения, автоматически получают свойство обязательности.
4. В каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов основной сущности, являющейся первичным ключом основной сущности. В отношении, соответствующем подчиненной сущности, этот набор атрибутов становится внешним ключом.
5. Для моделирования необязательного типа связи нафизическом уровне у атрибутов, соответствующих внешнему ключу, устанавливается свойство допустимости неопределенных значений. При обязательном типе связи атрибуты получают свойство отсутствия неопределенных значений.
6. Для отражения категоризации сущностей при переходе к реляционной модели возможно несколько вариантов представления.
Возможно создать только одно отношение для всех подтипов одного супертипа. В него включают все атрибуты всех подтипов.Однако тогда для ряда экземпляров ряд атрибутов не будет иметь смысла. И даже если они будут иметь неопределенные значения, потребуются дополнительные правила различения одних подтипов от других. Достоинством такого представления является то, чтосоздается всего одно отношение.
При втором способе для каждого подтипа и для супертипа создаются свои отдельные отношения. Недостатком такого способа представления является то, что создается много отношений, однако достоинств у такого способа больше, так как вы работаете только со значимыми атрибутами подтипа. Кроме того, для возможности переходов к подтипам от супертипа необходимо в супертип включить идентификатор связи.
7. Дополнительно при описании отношения между типом и подтипами необходимо указать тип дискриминатора. Дискриминатор может быть взаимоисключающим или нет. Если установлен данный тип дискриминатора, то это значит, что один экземпляр сущности супертипа связан только с одним экземпляром сущности подтипа и для каждого экземпляра сущности супертипа существует потомок. Кроме того, необходимо указать для второго способа, наследуется ли только идентификатор супертипа в подтипы, или наследуются все атрибуты супертипа.
8. Если в ER- схеме имеется связь (связи) М:М, которые реляционная модель не поддерживает, вводится специальное связующее отношение, которое связано с каждым исходным отношением связью 1:М. Атрибутами этого отношения являются первичные ключи связываемых отношений.
Алгоритм приведения семантической модели к 3НФ.
Алгоритм приведения семантической модели к 3-й нормальной форме может быть следующим:
1. Проанализировать схему на присутствие сущностей, которые скрыто моделируют несколько разных взаимосвязанных классов объектов реального мира (именно это соответствует ненормализованным отношениям). Если такое выявлено, разделить каждую из этих сущностей на несколько новых сущностей и установить между ними соответствующие связи; полученная схема будет находиться в первой нормальной форме.
2. Проанализировать все сущности, имеющие составные первичные ключи, на наличие неполных функциональных зависимостей непервичных атрибутов от атрибутов возможного ключа. Если такие зависимости обнаружены, разделить данные сущности на 2, определить для каждой сущности первичные ключи и установить между ними соответствующие связи. Полученная схема будет находиться во второй нормальной форме.
3. Проанализировать неключевые атрибуты всех сущностей на наличие транзитивных функциональных зависимостей. При обнаружении таковых расщепить каждую сущность на несколько таким образом, чтобы ликвидировать транзитивные зависимости. Схема находится в третьей нормальной форме.
Используя рассмотренные положения, нормализуем ER- схему рис. 5. Результат нормализации приведен на рис. 5. При нормализации схемы в нее введено отношение «Связи Книги – каталог», содержащее атрибуты «ISBN» и «Код области знаний», служащие для реализации связи М:М «Книги – систематический каталог», а в отношение «Экземпляры» для его связи с отношениями «Книги» и «Читатели» введены атрибуты «№ читательского билета» и «ISBN». Стрелки указывают направление связей.
Можно показать, что схема рис. 5 удовлетворяет требованиям 3-й нормальной формы.

рис. 5

.