Шехтман В.Е. Базы данных, SQL и все такое. Курс лекций

Подождите немного. Документ загружается.

читать ни одной лекции в текущем семестре. А также, что конкретный студент может

не слушать ни одного курса (например, быть в академическом отпуске). Кроме того, эта

связь имеет ―лапы‖ с обеих сторон и поэтому называется ―многие ко многим‖. В самом

деле, конкретный преподаватель может читать лекции многим студентам, а конкретный

студент может слушать лекции многих преподавателей.

4.3 Рекурсивная связь. Роли

Порой одна сущность используется на обоих концах связи. Чтобы лучше

понимать суть взаимоотношений разных сущностей, концы связи можно наделить

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

называется рекурсивной. На рисунке 4.4 представлен пример такой связи. Его следует

понимать как совокупность следующих утверждений:

 у сотрудника может быть начальник (но не более одного), а может и не быть

(например, у директора начальника нет);

 у начальника может быть несколько подчиненных;

 сотрудник – начальник может быть подчинен другому, вышестоящему

начальнику (поэтому в приведенной схеме можно отразить многоуровневую

структуру организации) .

Сотрудники

Таб. Номер

Фамилия

Имя

Отчество

...

Руководит

Начальник

Подчиненный

Рисунок 4.4 – Рекурсивная связь с ролями

4.4 Множественные связи

До сих пор были рассмотрены только ER–диаграммы, в которых встречались

двухсторонние связи. Однако модель ―сущность–связь‖ способна представить и связи с

большим числом сторон. Такие связи иногда стоит ввести ввиду того, что они

позволяют отразить существо взаимоотношений между понятиями моделируемой

предметной области.

Диаграмма на рисунке 4.5 представляет факт заключения договора между

Предприятиями (заказчиками и исполнителями) на выполнение работ или оказание

услуг, являющихся предметом договора. При этом назначается Ответственный за

договор. Заметим, что сущность Предприятия наделена ролями (Заказчик,

Исполнитель). Заметим также, что многостороннюю связь иногда обозначают ромбом.

Ответственный

Предмет договора

Предприятие

Договор

ЗаказчикИсполнитель

Рисунок 4.5 – Четырехсторонняя связь

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

которых являются сущности (точнее ссылки на них) из наборов сущностей,

соединяемых этой связью. Тогда связь Договор может быть описана кортежем

<Предприятие 1, Предприятие 2, Предмет договора 1, Ответсвенный 1>

Любая многосторонняя связь может быть преобразована в некоторую

совокупность двухсторонних связей типа ―многие к одному‖. С этой целью следует

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

многостороннюю связь – в нашем случае Договоры (см. рисунок 4.6). Новое

множество сущностей Договоры участвует в четырех связях. Можно считать, что оно

содержит сущность, соединенную связью Отвечает с сущностью ответственный 1

набора сущностей Ответсвенные, связью Состоит с сущностью предмет договора 1

набора сущностей Предметы договора, связью Заказывает с сущностью

предприятие 1 набора сущностей Предприятия, связью Исполняет с сущностью

предприятие 2 набора сущностей Предприятия.

Ответсвенный

П р е д п р и я т и я

Предметы договора

ИсполняетЗаказывает

Договоры

Отвечает

Состоит в

Рисунок 4.6 – Замена многосторонней связи набором

сущностей и двухсторонними связями

После того как был введен новый, связующий набор сущностей Договоры,

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

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

договору и т. д.

4.5 Суперсущности и производные сущности

Иногда полезно ввести понятие суперсущности. Это позволяет в меньшем

количестве наборов сущностей хранить больше атрибутов. Например, введем набор

суперсущностей Сотрудники, множество атрибутов которого представлено на

рисунке 4.7 . Тогда Преподаватели – набор сущностей, производный от набора

суперсущностей Сотрудники. Каждый преподаватель – сотрудник, но не каждый

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

атрибутов сотрудника, имеет такие атрибуты: кафедра, на которой он работает и

возможность иметь ученую степень. Отношение между наборами сущностей и

суперсущностей изображаются с помощью треугольника с надписью Is a (по английски

- … является …).

Сотрудники

Табельный номер

Фамилия

Имя

Отчество

Адрес

…

Преподаватели

Каферда

Ученая степень

Isa

Рисунок 4.7 – Иерархия набор сущностей – набор суперсущностей

Как известно, набор атрибутов, однозначно идентифицирующий конкретный

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

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

сущностей следует указать ключ. Для иерархии суперсущность–сущность только

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

иерархии. Например, Табельный номер сортудника – ключ для набора сущностей

Сотрудники и для набора сущностей Преподаватели.

На диаграммах ―сущность-связь‖ ключевые атрибуты подчеркиваются (рисунок

4.8).

Поставки

PK Код товара

PK Код поставщика

PK Дата поставки

Вес

Цена

Рисунок 4.8 – Диаграмма с подчеркнутыми ключами набора сущностей

4.6 Слабые наборы сущностей

Случается, что набору сущностей для уникальной идентификации конкретной

сущности (объекта) требуется ―помощь‖. Говорят, что набор сущностей слабый (или

подчиненный), если для уникальной идентификации объекта из этого набора требуется

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

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

сущностей Сотрудники (например, при условии, что в этом наборе сущностей

хранятся данные о сотрудниках только одного предприятия). Однако если мы в наборе

сущностей Сотрудники захотим хранить данные о сотрудниках нескольких

предприятий, то табельный номер перестает быть ключом. Но табельный номер вместе

с именем предприятия, на котором работает сотрудник, уже образуют ключ (рисунок

4.9). Слабый набор сущностей принято отмечать жирной или двойной рамкой.

Сотрудники

Табельный номер

Фамилия

Имя

Отчество

дата рождения

Предприятия

Наименование

Юридический адрес

Работает

Рисунок 4.9 – Подчиненный набор сущностей Сотрудники

4.7 Борьба с избыточностью

При проектировании структур данных следует избегать ситуации, когда один

факт выражается двумя разными способами. Будем называть это избыточностью.

Избыточность может привести к несогласованности данных и к перерасходу

пространства на диске.

На рисунке 4.10 каждый поставщик присутствует лишь однажды – это

приемлемое решение. На рисунке 4.11 поставщик упоминается дважды – как атрибут

набора сущностей Товар и как связанная сущность из набора сущностей Поставщики.

Это пример плохой структуры данных.

Товар

PK Наименование

НДС

Поставщик

PK Наименование

Адрес

Поставляет

Рисунок 4.10 – Пример отсутствия избыточности

Товар

PK Наименование

НДС

Поставщик

Адрес поставщика

Поставщик

PK Наименование

Адрес

Поставляет

Рисунок 4.11 – Пример избыточности (плохое решение)

На рисунке 4.12 представлено еще одно неудачное решение: для каждого товара

снова повторяется его поставщик и адрес, хотя связанной сущности Поставщики

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

не удастся зафиксировать имя и адрес поставщика в такой базе данных.

Товар

PK Наименование

НДС

Поставщик

Адрес поставщика

Рисунок 4.12 – Еще одно плохое решение

Можно задаться вопросом: что лучше – создавать набор сущностей или

создавать дополнительные атрибуты в исходном наборе сущностей? Ответ зависит от

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

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

набор сущностей должен играть роль стороны ―многие‖ в связи типа ―один ко многим‖

или ―многие ко многим‖. Достаточно выполнения одного из условий. Возвращаясь к

рисунку 4.10 можно сказать:

1. Поставщики стоит выделить в набор сущностей, т. к. каждая сущность имеет

неключевой атрибут Адрес.

2. Товары стоит выделить в набор сущностей, потому что они находятся на

стороне ―многие‖ в отношении ―один ко многим‖. По этой причине их стоило

бы выделить в отдельный набор сущностей, даже если бы не было неключевого

атрибута НДС.

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

Тогда на рисунке 4.13 представлено приемлемое решение – нет необходимости

создавать отдельный набор сущностей Поставщик т. к. нам, по смыслу задачи, не надо

хранить какую-либо характеристику поставщика, кроме его имени.

Товар

PK Наименование

НДС

Имя поставщика

Рисунок 4.13 – Пример условно приемлемого решения

В этих условиях решение, представленное на рисунке 4.14 может оказаться

неприемлемым. Хотя однозначно этого утверждать нельзя – все решают требования и

характер использования данных.

Товар

PK Наименование

НДС

Поставщики

PK Имя поставщика

Поставляет

Рисунок 4.14 – Пример неоднозначного решения

Говоря в общем, не стоит всегда создавать отдельные наборы сущностей.

Зачастую можно обходиться без них. Не стоит злоупотреблять слабыми наборами

сущностей.

Зачастую можно использовать собственные (естественные) ключи – ИНН,

государственный номер автомобиля, а можно и искусственный ключ (например, поле

типа COUNTER (Счетчик) в СУБД Microsoft Access, поле целого типа со свойством

IDENTITY в Microsoft SQL Server 2000 и т. д.).

4.8 Преобразование инфологической модели в даталогическую

После того как инфологическая модель готова, необходимо преобразовать ее в

даталогическую модель. Это преобразование можно выполнить в соответствии со

следующим (несколько упрощенным по сравнению с ―настоящим‖) алгоритмом.

Алгоритм преобразования инфологической модели к реляционной

(даталогической) модели данных:

1. Каждому набору сущностей поставить в соответствие отношение (таблицу) в

базе данных.

2. Каждый атрибут набора сущностей соотнести с атрибутом отношения

(определив его тип и дополнительные условия для обеспечения поддержания

целостности данных).

3. Ключ набора сущности превращается в первичный ключ отношения.

4. В каждое отношение подчиненного набора сущностей добавляется набор

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

набор атрибутов становится внешним ключом отношения (FOREIGN KEY).

5. При необязательности связи для атрибутов внешнего ключа устанавливается

допустимость значения NULL (пустого значения), при обязательной связи – NOT

NULL (недопустимость пустого значения атрибутов).

6. Для связи ―многие ко многим‖ вводится дополнительное отношение, связанное с

исходными отношениями связью ―один ко многим‖. Его атрибуты – первичные

ключи исходных отношений.

4.9 Пример инфологического моделирования

Представим, что нам требуется разработать инфологическую модель для

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

которые в нее входят: Отделы–Сотрудники–Проекты–Зарплата. Сотрудник может

получать зарплату как за выполнение конкретных проектов, так и за выполнение

―рутинных‖ обязанностей по работе в соответствующем (единственном) отделе.

Возможные решения этой задачи приведены на рисунках 4.15 и 4.16 . При

анализе различий между этими решениями обратите внимание на характер связей и

продумайте последствия решений, т. е. то, как соответсвующие структуры будут

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

Зарплата (в обеих реализациях) не связан с набором сущностей Отделы.

Отделы

Отдел

ЗарплатаПоШтатномуРасписанию

Месяц

Сумма

Сотрудники

ФИО

Проекты

Проект

ЗарплатаЗаПроект

Зарплата

Рисунок 4.15 – Одно из возможных решений

Отделы

Отдел

Зарплата

Месяц

Сумма

Сотрудники

ФИО

Проекты

Проект

Рисунок 4.16 – Другое возможное решение

5 Нормализация данных

К проектированию БД можно подойти и более формально, превратив процесс

проектирования в математическую процедуру. Цель этой процедуры одна –

спроектировать оптимальную в некоторм смысле структуру данных. Рассмотрим

предпосылки такого подхода.

5.1 Декомпозиция отношений

Пусть S = (МАГАЗИН, ТОВАР, НДС):

МАГАЗИН ТОВАР НДС

ПРИВЕТ БАНАН 20

ПРИВЕТ КАПУСТА 10

РЯБИНКА АРБУЗ 20

РЯБИНКА КАПУСТА 10

proj

МАГАЗИН, ТОВАР

МАГАЗИН ТОВАР

ПРИВЕТ

БАНАН

ПРИВЕТ

КАПУСТА

РЯБИНКА

АРБУЗ

РЯБИНКА

КАПУСТА

proj

ТОВАР, НДС

ТОВАР НДС

БАНАН 20

КАПУСТА 10

АРБУЗ 20

Можно убедиться, выполнив соответствующие вычисления, что

proj

МАГАЗИН, ТОВАР

S join proj

ТОВАР, НДС

S = S

Введем определение:

Полная декомпозиция отношения – это совокупность произвольного числа его

проекций, соединение которых идентично исходному отношению.

Поэтому совокупность проекций proj

МАГАЗИН, ТОВАР

S и proj

ТОВАР, НДС

S можно назвать

полной декомпозицией.

Рассмотрим другие проекции того же отношения S:

proj

МАГАЗИН, НДС

МАГАЗИН НДС

ПРИВЕТ 20

ПРИВЕТ 10

РЯБИНКА 20

РЯБИНКА 10

proj

ТОВАР, НДС

ТОВАР НДС

БАНАН 20

КАПУСТА 10

АРБУЗ 20

Выполнив необходимые вычисления, получим:

proj

МАГАЗИН, НДС

S join proj

ТОВАР, НДС

S :

МАГАЗИН ТОВАР НДС

ПРИВЕТ БАНАН 20

ПРИВЕТ АРБУЗ 20 Лишняя строка

ПРИВЕТ КАПУСТА 10

РЯБИНКА БАНАН 20

РЯБИНКА АРБУЗ 20

РЯБИНКА КАПУСТА 10

Следовательно

proj

МАГАЗИН, НДС

S join proj

ТОВАР, НДС

S <> S

т. е. данная совокупность проекций не образует полную декомпозицию!

Отсюда можно заключить, что не всякая декомпозиция допустима, если имеется

ввиду восстановление исходного отношения по его декомпозиции.

5.2 Проблема дублирования данных

Пусть P = (КодТовара, Поставщик, Тел). Допустим, что каждый вид товара

поставляется только одной фирмой, все фирмы имеют разные названия:

КодТовара Поставщик Тел

1 ГЛОБУС 33-33-33

2 ГЛОБУС 33-33-33

3 МИР 99-99-99

4 ОКЕАН 77-77-77

5 МИР 99-99-99

В данном отношении поставщики и их телефоны многократно дублируются.

Если взять проекцию proj

Поставщик, Тел

P, то можно убедиться, что в ней дублей нет:

Поставщик Тел

ГЛОБУС 33-33-33

МИР 99-99-99

ОКЕАН 77-77-77

Рассмотрим и проекцию proj

КодТовара, Поставщик

КодТовара Поставщик

1 ГЛОБУС

2 ГЛОБУС

3 МИР

4 ОКЕАН

5 МИР

Попробуйте убедиться, что proj

КодТовара, Поставщик

P join proj

Поставщик, Тел

P = P – т. е. что

эти две проекции образуют полную декомпозицию.

С другой стороны, если использовать проекцию proj

КодТовара, Тел

P, то дублирования

избежать не удастся:

КодТовара Тел

1 33-33-33

2 33-33-33

3 99-99-99

4 77-77-77

5 99-99-99

Итак мы убедились, что иногда, заменив отношение его полной декомпозицией,

можно избежать дублирования данных. Но в других случаях такая замена не удается.

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

декомпозиции отношения. Заметим, что проекции proj

Поставщик,Тел

P и

proj

КодТовара,Поставщик

P (образующие полную декомпозицию) не содержат общего ключа

исходного отношения, в то время как проекции proj

КодТовара,Тел

P и proj

КодТовара,Поставщик

(не образующие полную декомпозицию) обе содержат ключ исходного отношения –

КодТовара.

5.3 Висячие записи

Важно не только устранить дублирование, но и обеспечить хранение висячих

(иначе, присоединенных) записей. Пусть в отношение P из предыдущего пункта

требуется ввести телефон фирмы, еще не поставлявшей товара. Но КодТовара – ключ,

его нельзя оставить пустым! Значит невозможно ввести такой телефон! Но в проекцию

proj

Поставщик,Тел

P его ввести можно.

proj

Поставщик, Тел

P :

Поставщик Тел

ГЛОБУС 33-33-33

МИР 99-99-99

ОКЕАН 77-77-77

КОНТИНЕНТ 55-55-55

Правда, запись о фирме ―КОНТИНЕНТ‖ не войдет в

proj

КодТовара, Поставщик

P join proj

Поставщик,Тел

P. Такая запись называется висячей (или

присоединенной). Отношение, включающее висячие записи, перестает быть точной

проекцией P.

С другой стороны, если использовать проекции proj

КодТовара, Поставщик

P и

proj

КодТовара, Тел

P, то снова не удастся вставить телефон поставщика, не поставившего еще

ни одного товара.

Заметим, что декомпозиция proj

Поставщик, Тел

P и proj

КодТовара, Поставщик

P (позволяющая

записать сведения о поставщике, не осуществившего еще ни одной поставки)

составлена из проекций, не содержащих общего ключа исходного отношения. В то же

время декомпозиция proj

КодТовара, Тел

P и proj

КодТовара, Поставщик

P (не позволяющая записать

данные такого поставщика) составлена из проекций, содержащих ключ исходного

отношения – КодТовара.

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

той или иной структуры таблиц может привести к несовершенствам или наоборот

обеспечить удобство использования данных. Почему же вообще может быть плох

проект базы данных?

5.4 Проблемы, возникающие из-за неудачной структуры данных

1. Аномалия изменения (избыточность и потенциальная противоречивость).

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

в отношении, тем больше дублируется информации и тем больше

непроизводительных затрат. Из-за наличия многих дублей одних и тех же

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

изменена, а другая – нет. Если меняется имя поставщика (или оно было внесено

с ошибкой), то придется изменить много строк, в которых упоминается этот

поставщик – иначе получится, что имеется два разных поставщика.