Глава 1. Проектирование баз данных

1.1 Этапы проектирования базы данных

При проектировании базы данных решаются три основных проблемы:

1.2. Инфологическое проектирование

Одной из наиболее популярных семантических моделей данных на этапе инфологического проектирования является неформальная модель "Сущность-Связь" (Entity-Relationship - ER-модель). Модель была предложена Ченом (Chen) в 1976 г. Моделирование предметной области базируется на использовании графических диаграмм, включающих небольшое число разнородных компонентов. В связи с наглядностью представления концептуальных схем баз данных (и не только их) ER-модели получили широкое распространение в CASE-системах (Computer Aided Software Engineering - программные средства, поддерживающие процессы автоматизированного проектирования баз данных, создания и сопровождения ПО (приложений) и баз данных, генерацию кода, тестирование, документирование и управление проектом).

Замечание. Существует большое число нотаций ER-модели, несущественно отличающихся между собой:

Основными понятиями ER-модели являются сущность, связь атрибут.

1.2.1. Сущности и атрибуты

Сущность (объект) - это реальный или представляемый объект предметной области, информация о котором должна сохраняться и быть доступна. Различают такие понятия как тип сущности и экземпляр сущности. Понятие тип сущности относится к набору однородных предметов, событий, личностей, выступающих как единое целое. Экземпляр сущности относится к конкретной вещи в наборе. В диаграммах ER-модели сущность представляется в виде прямоугольника, содержащего имя сущности. При этом имя сущности - это имя типа, а не некоторого конкретного экземпляра этого типа. Для большей выразительности и лучшего понимания имя сущности может сопровождаться примерами конкретных сущностей этого типа.

Ниже изображена сущность АЭРОПОРТ (тип сущности) с примерными экземплярами сущностей Шереметьево и Толмачево:

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

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

(<имя сущности>.<Имя атрибута>).

Например,

Самолет.Размах крыла

Кошка.Вес

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

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

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

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

Описательные атрибуты представляют характеристики, внутренне присущие каждому экземпляру сущности:

Счет.Сальдо

Источник элекроснабжения.Полярность

Кошка.Вес

Если значение описательного атрибута изменяется, то это говорит о том, что некоторый аспект экземпляра сущности изменился, но сам экземпляр остался прежним (вес Кошки изменился, сама Кошка осталась прежней).

Указывающие атрибуты используются для присвоения имени или обозначения экземпляров сущности:

Счет.Номер

Груз.Номер накладной

Город.Название

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

Вспомогательные атрибуты используются для связи экземпляра одного сущности с экземпляром другого:

Кошка.Имя хозяина

Счет.ID клиента

Магнит.Источник электроснабжения

Если значение вспомогательного атрибута меняется, это означает, что теперь другие экземпляры связаны между собой (изменение источника электроснабжения PS10 PS12 определяет, что на магнит связан с другим источником).

Правила атрибутов :

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

2. Атрибут не должен содержать никакой внутренней структуры

3. Когда сущность имеет составной идентификатор, каждый атрибут, не являющийся частью идентификатора, представляет характеристику всей сущности, а не ее части, а тем более не характеристику чего-либо другого.

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

1.2.2. Связи

Связь (Relationship) - это поименованная графически изображаемая ассоциация, устанавливаемая между сущностями и представляющая собой абстракцию набора отношений, которые систематически возникают между различными видами предметов в реальном мире. При анализе связей между сущностями могут иметь место бинарные связи (между двумя сущностями или между сущностью и ей же самой - рекурсивная связь), тренарные связи (между тремя сущностями), в общем случае - n-арные связи. В ER-диаграммах связь обозначается либо направленными ребрами с соответствующими надписями (нотация IDEF1), либо ромбом или шестигранником, связанным ребрами с каждой из сущностей (нотация Yourdona).

Среди бинарных связей существуют три фундаментальных вида связи: один к одному (1:1), один ко многим (1:M), многие ко многим (M:N). Эти фундаментальные виды связей относятся к числу безусловных связей и требующих участия каждого экземпляра сущности.

Связь один к одному (1:1) существует, когда один экземпляр одной сущности связан с единственным экземпляром другой сущности.

(Связь R1 - традиционный брак: каждый муж имеет одну жену и каждая жена имеет одного мужа).

Связь один ко многим (1:M) существует, когда один экземпляр одной сущности связан с одним или более экземпляром другой сущности и каждый экземпляр второй сущности связан только с одним экземпляром первой сущности.

(Связь R2 - владение собаками: каждый владелец может иметь одну или несколько собак, но каждая собака принадлежит только одному владельцу).

Связь многие ко многим (М:N) существует, когда один экземпляр одной сущности связан с одним или более экземпляром другой сущности и каждый экземпляр второй сущности связан с одним или более экземпляром первой сущности.

(Связь R3 - домовладение: каждый владелец может иметь один или несколько домов, и каждый дом является собственностью одного или несколько владельцев).

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

(Связь R4 - биусловная, так как некоторые служащие не работают ни в одном из офисов и некоторые офисы не предназначены ни для одного из служащих).

Суммируя вышесказанное, можно выделить 10 форм для связей, включающих две сущности.

Безусловные формы (учавствует каждый экземпляр)

Условные формы (с одной стороны не все экземпляры участвуют в связи)

Биусловные формы (участвуют не все экземпляры с двух сторон)

Характер связей между сущностями не ограничивается перечисленными - существуют и более сложные связи:

1. Множество связей между одними и теми же сущностями

Пациент, имея одного лечащего врача, может иметь также несколько консультантов; врач может быть лечащим врачом нескольких пациентов и может одновременно консультировать нескольких пациентов.

2. Тренарные связи

Представленная выше диаграмма определяет, что врач может назначить несколько пациентов на несколько анализов; анализ может быть назначен несколькими врачами нескольким пациентам и пациент может быть назначен на несколько анализов несколькими врачами.

3. Связь может быть рекурсивной, если сущность (Мужчина) связывается сам с собой.

Устной трактовкой изображенной диаграммы является следующая:

Замечание. В случае очень большого числа сущностей и связей между ними, применяется менее наглядный, чем язык ER-диаграмм, но более содержательный язык инфологического моделирования (ЯИМ), в котором сущности и связи представляются предложениями вида:

СУЩНОСТЬ (атрибут 1, атрибут 2 , ..., атрибут n)

СВЯЗЬ [СУЩНОСТЬ S1, СУЩНОСТЬ S2, ...] (атрибут 1,..., атрибут n),

где S - степень связи (ср. с текстовым способом представления сущностей). ER-диаграммы в этом случае используются для иллюстрации отдельных фрагментов инфологической модели.

Так рассмотренный выше пример множества связей между сущностями может быть описан на ЯИМ следующим образом:

Врач (Номер_врача, Фамилия, Имя, Отчество, Специальность)

Пациент (Регистрационный_номер, Номер койки, Фамилия, Имя, Отчество, Адрес, Дата рождения, Пол)

Лечащий_врач [Врач 1, Пациент M] (Номер_врача, Регистрационный_номер)

Консультант [Врач M, Пациент N] (Номер_врача, Регистрационный_номер)

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

Пример 1. Отдел записей актов гражданского состояния (ЗАГС) имеет дело не со всеми людьми, а только с теми, кто обратился с просьбой о регистрации брака, рождения или смерти. Поэтому в странах, где допускаются лишь традиционные браки, отделы ЗАГСа могут размещать сведения о регистрируемых браках в единственной сущности:

Брак (Номер_свидетельства, Фамилия_мужа, Имя_мужа, Отчество_мужа, Дата_рожд_мужа, Фамилия_жены, ... ,Дата_регистр, Место_регистр, ...),

Пример 2. Теперь рассмотрим ситуацию, когда отдел ЗАГСа расположен в стране, допускающей многоженство. Если для регистрации браков использовать сущность Брак предыдущего примера, то в экземплярах сущности будут дублироваться сведения о мужьях, имеющих несколько жен.

Дублирование можно исключить созданием дополнительной сущности

Мужья (Код_М, Фамилия, Имя, Отчество, Дата рождения, Место рождения),

связанной с несколько измененной сущностью

Брак (Номер свидетельства, Код_М, Фамилия жены, ...,Дата регистрации, ...)

ER-диаграмма связи этих сущностей представлена ниже

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

Сотрудники (Табельный_номер, Фамилия, Имя, ...).

Использование рассмотренной в примере 1 сущности Брак нецелесообразно, так как в сущности Сотрудники уже есть фамилии, имена, отчества супругов. Поэтому достаточно определить связь

Брак [Сотрудник 1, Сотрудник 1] (Таб_номер_мужа, таб_номер_жены, ...),

связывающую между собой определенные экземпляры сущности Сотрудники.

1.2.3. Формализация связей

Все связи требуют описания. Описание должно обеспечивать:

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

Для формализации связи "один к одному" вспомогательные атрибуты могут быть добавлены к любой сущности (но не к обоим).

Для формализации связи "один ко многим" вспомогательные атрибуты должны быть добавлены к сущности на стороне "многого", поскольку размещение такого вспомогательного атрибута на стороне "один" будет нарушать третье правило атрибутов.

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

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

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

Стержневая сущность (стержень) - это независимая сущность.

В рассмотренных выше примерах стержни - это Муж (но не Жена),Владелец собаки, владелец дома, ранее Врач, Пациент, Анализ, Брак (из примера 1, п. 1.2.2).

Ассоциативная сущность (ассоциация) - это сущность, формализующая связь вида "многие ко многим" ("... ко многим" и т.д.) между двумя или более сущностями или связь вида "один к одному" между экземплярами сущностей.

В рассмотренных выше примерах ассоциации - это Владение, ранее Консультант, Назначенный анализ, Брак (из примера 3, п 1.2.2 - формализует связи между экземплярами одной сущности).

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

В расширенной ER-модели ассоциация изображается шестигранником, а на языке инфологического моделирования записывается так же, как связь (ассоциация сущности):

АССОЦИАЦИЯ [СУЩНОСТЬ S1, СУЩНОСТЬ S2, ...] (атрибут1, ...,атрибут n)

Владение [дом, владелец дома] (адрес, квартира, :)

Характеристическая сущность (характеристика) - это сущность, формализующая связь вида "многие к одной" или "одна к одной". Единственная цель характеристики в рамках рассматриваемой предметной области состоит в описании или уточнении некоторой другой сущности. Xарактеристика Жена характеризует стержневую сущность Муж (характеризуемая сущность), аналогично характеристика Брак из примера 2, п 1.2.2 характеризует стержневую сущность Муж, характеристика Позиции_наряда описывает стержневую сущность Наряды, характеристика Вид_издания (исправленное, дополненное, переработанное, . . .) уточняет стержневую сущность Книга и т.д.

Существование характеристики полностью зависит от характеризуемой сущности: при удалении экземпляра характеризуемой сущности удаляется экземпляр сущности-характеристики (женщины лишаются статуса жен, если умирает их муж).

В расширенной ER-модели характеристика изображается трапецией, на языке инфологического моделирования выглядит:

ХАРАКТЕРИСТИКА(атриб1,...){СПИСОК ХАРАКТЕРИЗУЕМЫХ СУЩНОСТЕЙ}.

Жена (Имя_жены, :) {Муж}

Обозначающая сущность или обозначение - это сущность, формализующая связь вида "многие к одной" или "одна к одной" между двумя сущностями и отличающаяся от характеристики тем, что не зависит от обозначаемой сущности.

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

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

В расширенной ER-модели обозначение изображается параллелепипедом, а на языке инфологического моделирования записывается:

ОБОЗНАЧЕНИЕ (атриб1, атриб2,...)[СПИСОК ОБОЗНАЧАЕМЫХ СУЩНОСТЕЙ].

Собака(ID_собаки, :) [Владелец собаки]

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

Первичный ключ ассоциативной сущности строится из первичных ключей сущностей, связи между которыми она формализует.

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

Анализ объектов позволяет выделить:

Запись модели на языке инфологического моделирования приведена ниже:

Блюда (D_БЛ, Описание, Вид)

Продукты (ID_ПР, Описание, Калорийность)

Поставщики (ID_ПОСТ, Поставщик, Код_города) [Город]

Состав [Блюда M, Продукты N](ID_БЛ, ID_ПР, Вес)

Поставки [Поставщики M, Продукты N] (ID_ПОСТ, ID_ПР, Дата поставки, Цена, Вес)

Города (Код, Город, Страна)

Рецепты (ID_рецепта, ID_БЛ, Рецепт) {Блюда}

Расход (ID_БЛ, Дата,Число порций) {Блюда}

Расширенная инфологическая модель ER-модель:

1.2.4.Развитые элементы ER-модели

К числу более сложных элементов ER-модели относятся:

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

Сущность может участвовать в многократных конструкциях супертип-подтип.

Иногда удобно иметь два или более разных разбиения сущности на подтипы по различным классификациям.

Некоторые связи образуются как неизбежные следствия существования других связей.

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

В университете В правила те же, за исключением того, что студент должен выбирать профессора на факультете специализации. В этом случае связь R3 - логическое следствие связей R1 и R2 и образована их композицией. Связь, образованная композицией, не может быть формализована во вспомогательных атрибутах, поскольку связь R3 уже задана связями R1 и R2. Информационная модель университета В представлена ниже.

В заключение приведем пример ER-диаграммы, описывающей сущности и связи различных типов на этапе инфологического проектирования:

Вопросы для самопроверки
  1. Каковы основные этапы проектирования базы данных?
  2. Какие задачи решаются на этапе инфологического проектирования?
  3. Какие задачи решаются на этапе логического проектирования?
  4. Какие задачи решаются на этапе физического проектирования?
  5. Каково назначение ER-модели?
  6. Каковы основные элементы ER-модели в нотации IDEF1?
  7. Каковы основные элементы ER-модели в нотации Yourdona?
  8. Дайте определение понятию сущность.
  9. Чем отличаются понятия типа сущности и элемента сущности?
  10. Дайте определение понятию атрибут.
  11. Абсолютно ли понятие сущность?
  12. Абсолютно ли понятие атрибут?
  13. Что такое идентификатор сущности?
  14. Как графически представляется сущность?
  15. Как представляется сущность в текстовом виде?
  16. Как классифицируются атрибуты?
  17. О чем говорит изменение описательного атрибута?
  18. О чем говорит изменение указывающего атрибута?
  19. О чем говорит изменение вспомогательного атрибута?
  20. Каковы основные правила атрибутов?
  21. В чем проявляется первое правило атрибутов в случае графической интерпретации сущности?
  22. В чем проявляется второе правило атрибутов в случае графической интерпретации сущности?
  23. Что вкладывается в понятие связь?
  24. Каковы фундаментальные виды связи?
  25. Что такое безусловная связь?
  26. Что такое условная связь?
  27. Что такое биусловная связь?
  28. Что такое рекурсивная связь?
  29. Перечислите элементы языка инфологического проектирования.
  30. Как изображается связь в нотации IDEF1?
  31. Как изображается связь в нотации Yourdona?
  32. Что понимается под формализацией связей?
  33. Как формализуется связь 1:1?
  34. Как формализуется связь 1:M?
  35. Как формализуется связь M:N?
  36. Что такое стержневая сущность?
  37. Что такое ассоциативная сущность?
  38. Что такое характеристическая сущность?
  39. Что такое обозначающая сущность?
  40. Из чего строится первичный ключ ассоциативной сущности?
  41. Что такое подтип и супертип?
  42. Что такое уточняющие степени связи?
  43. В чем состоит процедура каскадного удаления?
  44. Что такое композиция связей?