Максимов Н.В., Голицына О.Л., Васина Е.Н. Управление данными

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

141

3. Компоненты уникального идентификатора сущности превра-

щаются в первичный ключ. Если имеется несколько возможных уни-

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

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

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

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

продолжаться рекурсивно). Для именования этих столбцов используют-

ся имена концов связей и/или имена сущностей.

4. Связи «многие к одному» и «один к одному» становятся внеш-

ними ключами. Т.е. создается копия уникального идентификатора с кон-

ца связи "один", и соответствующие столбцы составляют внешний ключ.

5. Индексы создаются для первичного ключа (уникальный ин-

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

использоваться в запросах.

6. Если в концептуальной схеме присутствуют подтипы, то воз-

можны два варианта.

Все подтипы хранятся в одной таблице, которая создается для са-

мого внешнего супертипа, а для подтипов создаются представления. В

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

ТИПА, и он становится частью первичного ключа.

Во втором случае для каждого подтипа создается отдельная таб-

лица и для каждого подтипа первого уровня (для более нижних - пред-

ставления) супертип воссоздается с помощью представления UNION (из

всех таблиц подтипов выбираются общие столбцы - столбцы супертипа).

7. Если остающиеся внешние ключи все принадлежат одному до-

мену, т.е. имеют общий формат, то создаются два столбца: идентифика-

тор связи и идентификатор сущности. Столбец идентификатора связи

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

используется для хранения значений уникального идентификатора сущ-

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

Если результирующие внешние ключи не относятся к одному до-

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

явные столбцы внешних ключей.

5.6. Физические модели

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

включает:

- выбор способа организации базы данных, основные из которых

были рассмотрены ранее в главе 4;

- разработку спецификации внутренней схемы средствами модели

данных ее внутреннего уровня;

- описание отображения концептуальной схемы во внутреннюю.

Важно заметить, что в отличие от ранних СУБД, многие совре-

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

142

этой стадии. Реально к вопросам проектирования физической модели

можно отнести выбор схемы размещения данных (разделение по файлам

или тип RAID-массива) и определение числа и типа индексов (например,

кластеризованный или некластеризованный в случае MS SQL Server).

Способ хранения базы данных определяется механизмами СУБД

автоматически “по умолчанию” на основе спецификаций концептуаль-

ной схемы базы данных, и внутренняя схема в явном виде в таких сис-

темах не используется.

Следует также отметить, что внешние схемы базы данных обычно

конструируются на стадии разработки приложений.

Примеры типичных способов реализаций физических моделей,

воплощенных в промышленных СУБД, приведены в Приложении.

Контрольные вопросы

1. Перечислите основные этапы проектирования БД

2. Перечислите задачи этапа системного анализа

3. Определите понятие «парадигма моделирования»

4. Что называется инфологической моделью

5. Какие бывают типы сложных объектов

6. Определите типологию связей

7. Определите соотношение понятий «сущность» и «связь»

8. Какими свойствами должен обладать индентификатор объекта

9. Проанализируйте соотношение ER-модели и ER-диаграммы

10. В чем состоит сходство и отличие даталогической и физиче-

ской модели

11. Как отображается сложный объект в реляционной модели.

12. Как отображаются в реляционной модели отношения типа М:М

13. В чем заключается даталогическое проектирование для кон-

кретной СУБД.

143

Глава 6. Проектирование реляционной базы данных

Задача проектирования БД для предметной области состоит в том,

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

будущих приложений. Таким образом, БД создают основу для обработ-

ки неформализованных, изменяющихся и неизвестных запросов и созда-

ния приложений, для которых невозможно заранее определить требова-

ния к данным. Это позволяет в дальнейшем строить на основе предмет-

ных БД достаточно стабильные информационные системы, т.е. системы,

в которых большинство изменений можно осуществить без переписыва-

ния старых приложений.

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

текущих и видимых задач, можно существенно ускорить создание ин-

формационной системы, структура которой учитывает наиболее часто

встречающиеся пути доступа к данным. Однако по мере количества та-

ких информационных систем быстро увеличивается число прикладных

БД и, соответственно, резко возрастает уровень дублирования данных и

повышается стоимость их ведения.

Желание достичь одновременно гибкости и эффективности приво-

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

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

– для ее совершенствования с целью повышения эффективности обра-

ботки данных.

При проектировании информационной системы (см. гл. 5) необхо-

димо провести анализ целей этой системы и выявить требования к ней

отдельных пользователей. Сбор данных начинается с выявления и изу-

чения объектов информационной среды и процессов, в которых эти объ-

екты участвуют. Объекты (сущности) группируются по типу и по мощ-

ности связей между ними (студент – сессия, преподаватель – дисципли-

на и т.д.).

Дальнейшая задача проектирования БД – это сокращение избы-

точности хранимых данных, а следовательно, экономия объема исполь-

зуемой памяти, уменьшение затрат на многократные операции обновле-

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

тиворечий из-за хранения в разных местах сведений об одном и том же

объекте. Такой проект БД можно создать, используя методологию нор-

мализации отношений.

6.1. Универсальное отношение

Рассмотрим задачу проектирования БД на базе сводной таблицы,

пример которой приведен на рис. 6.1. Предложенная таблица отражает

результаты сдачи сессии (шкала оценок: 0 – незачет; 1 – зачет; 2, 3, 4, 5

– экзаменационная оценка).

144

Этот вариант таблицы «Сессия» не является отношением, так как

большинство ее столбцов не атомарны. Атомарными являются лишь

значения столбцов «ФИО студента», «Семестр». Остальные столбцы

таблицы – множественные.

Для преобразования данных в отношение необходимо реконструи-

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

зультат которой показан на рис. 6.2.

Однако очевидно, что такое преобразование приводит к возникно-

вению большого объема избыточных данных.

ФИО

студен-

та

Семестр Дисциплина

Форма

отчет-

ности

Оценка

Кол-

во

часов

ФИО

препода-

вателя

Английский язык зачет 1 60 Цветкова

А. Ю.

Математический ана-

лиз

зачет 1 28 Рыбин К. К.

Математический ана-

лиз

экза-

мен

5 32 Раков И. И.

Программирование зачет 1 36 Незабудки-

на З. П.

Программирование экза-

мен

5 32 Зайчиков

А. А.

Линейная алгебра зачет 1 24 Волков Г.

И.

Линейная алгебра экза-

мен

4 28 Волков Г.

И.

Иванов

В. П.

История Отечества экза-

мен

5 24 Москвин А.

П.

Английский язык зачет 1 60 Цветкова

А. Ю.

Математический ана-

лиз

зачет 1 28 Рыбин К. К.

Математический ана-

лиз

экза-

мен

3 32 Раков И. И.

Программирование зачет 1 36 Незабудки-

на З. П.

Программирование экза-

мен

4 32 Зайчиков

А. А.

Линейная алгебра зачет 1 24 Волков Г.

И.

Линейная алгебра экза-

мен

4 28 Волков Г.

И.

Петрова

А.П.

История Отечества экза-

мен

5 24 Москвин А.

П.

Английский язык зачет 1 60 Цветкова

А. Ю.

Сидоров

К.К.

Математический ана-

лиз

зачет 1 20 Карпов К.

Ю.

145

Математический ана-

лиз

экза-

мен

5 28 Раков И. И.

Алгоритмы и струк-

туры данных

экза-

мен

5 32 Зайчиков

А. А.

Теория вероятностей

и мат. статистика

экза-

мен

4 32 Соболев И.

Г.

Операционные сис-

темы, среды и обо-

лочки

зачет 1 36 Незабудки-

на З. П.

Операционные сис-

темы, среды и обо-

лочки

экза-

мен

4 32 Незабудки-

на З. П.

Экономическая тео-

рия

зачет 1 24 Лабиринтов

Е. Н.

Рис. 6.1. Исходные данные для создания БД «Сессия»

Таблица на рис. 6.2 представляет собой корректное отношение.

Такое отношение называют универсальным отношением проектируемой

БД. В одно универсальное отношение включаются все представляющие

интерес атрибуты, и оно может содержать все данные, которые предпо-

лагается размещать в БД в будущем. При проектировании некоторых БД

универсальное отношение может использоваться в качестве отправной

точки.

ФИО

сту-

дента

Се-

местр

Дисциплина

Форма

отчетности

Оцен

ка

Кол-

во

часов

ФИО

преподава-

теля

Иванов

В. П.

1 Английский

язык

зачет 1 60 Цветкова

А.Ю.

Иванов

В. П.

1 Математиче-

ский анализ

зачет 1 28 Рыбин К.К.

Иванов

В. П.

1 Математиче-

ский анализ

экзамен 5 32 Раков И.И.

Иванов

В. П.

1 Программиро-

вание

зачет 1 36 Незабудки-

на З.П.

Иванов

В. П.

1 Программиро-

вание

экзамен 5 32 Зайчиков

А.А.

Иванов

В. П.

1 Линейная ал-

гебра

зачет 1 24 Волков Г.И.

Иванов

В. П.

1 Линейная ал-

гебра

экзамен 4 28 Волков Г.И.

Иванов

В. П.

1 История Оте-

чества

экзамен 5 24 Москвин

А.П.

Петро-

ва А. П.

1 Английский

язык

Зачет 1 60 Цветкова

А.Ю.

Петро-

ва А. П.

1 Математиче-

ский анализ

Зачет 1 28 Рыбин К.К.

Петро-

ва А. П.

1 Математиче-

ский анализ

экзамен 3 32 Раков И.И.

146

Петро-

ва А. П.

1 Программиро-

вание

Зачет 1 36 Незабудки-

на З.П.

Петро-

ва А. П.

1 Программиро-

вание

экзамен 4 32 Зайчиков

А.А.

Петро-

ва А. П.

1 Линейная ал-

гебра

Зачет 1 24 Волков Г.И.

Петро-

ва А. П.

1 Линейная ал-

гебра

экзамен 4 28 Волков Г.И.

Петро-

ва А. П.

1 История Оте-

чества

экзамен 5 24 Москвин

А.П.

Сидо-

ров К.

К.

3 Английский

язык

Зачет 1 60 Цветкова

А.Ю.

Сидо-

ров К.

К.

3 Математиче-

ский анализ

Зачет 1 20 Карпов

К.Ю.

Сидо-

ров К.

К.

3 Математиче-

ский анализ

экзамен 5 28 Раков И.И.

Сидо-

ров К.

К.

3 Алгоритмы и

структуры

данных

экзамен 5 32 Зайчиков

А.А.

Сидо-

ров К.

К.

3 Теория вероят-

ностей и мат.

статистика

экзамен 4 32 Соболев

И.Г.

Сидо-

ров К.

К.

3 Операционные

системы, среды

и оболочки

зачет 1 36 Незабудки-

на З.П.

Сидо-

ров К.

К.

3 Операционные

системы, среды

и оболочки

экзамен 4 32 Незабудки-

на З.П.

Сидо-

ров К.

К.

3 Экономическая

теория

зачет 1 24 Лабиринтов

Е.Н.

Рис. 6.2. Универсальное отношение «Сессия»

Однако при использовании универсального отношения возникают,

по крайней мере, две проблемы:

1. Избыточность данных. Значения столбцов таблицы многократ-

но повторяются. Повторяются также и некоторые наборы значений

столбцов, например, данные о дисциплине.

2. Потенциальная противоречивость. Если при вводе данных, на-

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

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

содержащие сведения об этой дисциплине, и во всех этих строках про-

вести изменения. Более того, при заполнении такой таблицы могут быть

использованы различные формы записи одного и того же значения – на-

147

пример: «Англ. язык» и «Английский язык»; «Мат. анализ» и «Матема-

тический анализ».

Решение этих проблем состоит в разделении данных и связей, т.е.

в выделении в отдельные таблицы сведений о студентах, преподавате-

лях, дисциплинах и результатах сдачи экзаменов (рис. 6.3).

Студенты Преподаватели Дисциплины

№ ФИО

студента

№ ФИО препо-

давателя

№ Дисциплина

1 Иванов

В. П.

1 Волков Г. И. 1 Алгоритмы и структуры дан-

ных

2 Петрова

А. П.

2 Зайчиков А.

А.

2 Английский язык

3 Сидоров

К. К.

3 Карпов К. Ю. 3 История Отечества

4 Лабиринтов

Е. Н.

4 Линейная алгебра

5 Москвин А.

П.

5 Математический анализ

6 Незабудкина

З. П.

6 Операционные системы, сре-

ды и оболочки

7 Раков И. И. 7 Программирование

8 Рыбин К. К. 8 Теория вероятностей и мат.

статистика

9 Соболев И. Г. 9 Экономическая теория

10 Цветкова А.

Ю.

Учебный план Результаты сессии

№ Дис

ци-

пли

на

Се

ме

стр

Кол

-во

ча-

сов

Форма

отчет-

ности

Пре-

по-

дава-

тель

Студент Учебный

план

Оценка

1 2 1 60 зачет 10 1 1 1

2 3 1 24 экзамен 5 1 2 5

3 4 1 24 зачет 1 1 3 1

4 4 1 28 экзамен 1 1 4 4

5 5 1 28 зачет 8 1 5 1

6 5 1 32 экзамен 7 1 6 5

7 7 1 36 зачет 6 1 7 1

8 7 1 32 экзамен 2 1 8 5

9 2 3 60 зачет 10 2 1 1

10 5 3 20 зачет 3 2 2 5

11 5 3 28 экзамен 7 2 3 1

12 1 3 32 экзамен 2 2 4 4

13 8 3 32 экзамен 9 2 5 1

14 6 3 36 зачет 6 2 6 3

15 6 3 32 экзамен 6 2 7 1

16 9 3 24 зачет 4 2 8 4

Рис. 6.3. Разделение универсального отношения «Сессия»

148

Заменим в таблицах «Результаты сессии» и «Учебный план» кон-

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

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

дополнительные возможности по включению строк в таблицы «Студен-

ты», «Преподаватели», «Дисциплины», что значительно расширяет воз-

можности БД.

Теперь при изменении названия «Математический анализ» на

«Мат. анализ» исправляется единственное значение в таблице «Дисцип-

лины». И даже если оно вводится с ошибкой, то это не может повлиять

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

таблице «Результаты сессии» используются номера дисциплин учебного

плана, а не их названия).

6.2. Функциональная и многозначная зависимости

Процесс нормализации – это разбиение таблицы на две или более с

целью ликвидации дублирования данных и потенциальной их противо-

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

кого проекта базы данных, в котором «каждый факт появляется лишь в

одном месте».

Каждая таблица в реляционной модели удовлетворяет условию, в

соответствии с которым на пересечении любой строки и столбца табли-

цы всегда находится единственное атомарное значение, и никогда не

может быть множества таких значений. Говорят, что таблица, удовле-

творяющая такому условию, находится в первой нормальной форме, со-

кращенно 1НФ.

Теперь в дополнение к 1НФ можно определить дальнейшие уров-

ни нормализации – вторую нормальную форму (2НФ), третью нормаль-

ную форму (3НФ) и т.д. По существу, таблица находится во 2НФ, если

она находится в 1НФ и удовлетворяет, кроме того, некоторому дополни-

тельному условию, суть которого будет рассмотрена ниже. Таблица на-

ходится в 3НФ, если она находится в 2НФ и, помимо этого, удовлетво-

ряет еще другому дополнительному условию и т.д.

Теория нормализации основывается на наличии той или иной за-

висимости между столбцами таблицы. Рассмотрим два вида таких зави-

симостей: функциональные и многозначные.

Функциональная зависимость, по сути, является связью типа

«многие к одному» между множествами атрибутов (столбцов) рассмат-

риваемого отношения.

Например, в таблице «Учебный план» (см. рис. 6

.3) столбцы Дис-

циплина, Семестр и Форма отчетности функционально зависят от ключа

№ (порядковый номер) в учебном плане, а в таблице «Результаты сес-

сии» столбец Оценка функционально зависит от составного ключа (Сту-

дент, Учебный план).

149

Многозначная зависимость. Говорят, что один атрибут таблицы

многозначно определяет другой атрибут той же таблицы, если для каж-

дого значения первого атрибута существует хорошо определенное мно-

жество соответствующих значений второго атрибута.

В качестве примера рассмотрим фрагмент таблицы «Прием экза-

менов (зачетов)», изображенный на рис. 6.4. Таблица отражает связь

дисциплины и формы отчетности с фамилией преподавателя. В этой

таблице существует многозначная зависимость «Дисциплина - Препода-

ватель»: дисциплину «Математический анализ» ведут несколько препо-

давателей (Раков И. И., Рыбин К. К., Карпов К. Ю.) и, соответственно,

все они могут участвовать в приеме экзаменов (зачетов). Другая много-

значная зависимость – «Дисциплина – Форма отчетности»: по одной и

той же дисциплине может проводиться и экзамен, и зачет. При этом

Форма отчетности и Преподаватель не связны функциональной зависи-

мостью, что приводит к появлению избыточности (чтобы добавить фа-

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

строки).

Дисциплина

Преподаватель Форма отчет-

ности

Математический

анализ

Раков И. И. экзамен

Математический

анализ

Рыбин К. К. экзамен

Математический

анализ

Карпов К. Ю. экзамен

Математический

анализ

Раков И. И. зачет

Математический

анализ

Рыбин К. К. зачет

Математический

анализ

Карпов К. Ю. зачет

Рис. 6.4. Фрагмент таблицы «Прием экзаменов (зачетов)»

6.3. Нормальные формы

Мы уже говорили о первой нормальной форме (1НФ). Теперь при-

ведем ее более строгое определение, а также определения других нор-

мальных форм.

Таблица находится в первой нормальной форме (1НФ) тогда и

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

ее строка содержит только одно значение для каждого атрибута (столб-

ца).

150

Из таблиц, рассмотренных ранее, не удовлетворяет этим требова-

ниям (т.е. не находится в 1НФ) только таблица рис. 6.1.

Таблица находится во второй нормальной форме (2НФ), если она

удовлетворяет определению 1НФ и все ее атрибуты (столбцы), не вхо-

дящие в первичный ключ, связаны полной функциональной зависимо-

стью с первичным ключом.

Не удовлетворяют этим требованиям таблицы, представленные на

рис. 6.1 и на рис. 6.2. Таблица 6.2 имеет составной первичный ключ

(ФИО студента, Семестр, Дисциплина, Форма отчетности) и содержит

множество не ключевых атрибутов (Оценка, Количество часов, ФИО

преподавателя), зависящих лишь от той или иной части первичного

ключа. Так, атрибуты Количество часов и ФИО преподавателя зависят

только от атрибутов Семестр, Дисциплина, Форма отчетности. Следова-

тельно, эти атрибуты не связаны с первичным ключом полной функцио-

нальной зависимостью.

Ко второй нормальной форме приведены все таблицы рис. 6.3.

Таблица находится в третьей нормальной форме (3НФ), если она

удовлетворяет определению 2НФ и ни один из ее не ключевых атрибу-

тов не связан функциональной зависимостью с любым другим не клю-

чевым атрибутом.

Таблица «Учебный план» (рис. 6.3), очевидно, не находилась бы в

третьей нормальной форме, если включала бы в себя столбец Должность

преподавателя. В этом случае необходимо было бы провести декомпо-

зицию таблицы «Учебный план» и в результате получить таблицы до-

полнительную таблицу «Кадровый состав» с атрибутами №, ФИО пре-

подавателя, Должность преподавателя.

Следует отметить, что в таблице «Учебный план» на самом деле

существует функциональная зависимость между атрибутами «Кол-во

часов» и «ФИО преподавателя», с одной стороны, и совокупностью ат-

рибутов «Семестр», «Дисциплина» и «Форма отчетности» - с другой.

Однако тройка атрибутов («Семестр», «Дисциплина» и «Форма отчетно-

сти») в свою очередь может выступать в качестве первичного ключа, ко-

торый представлен в таблице атрибутом «Порядковый номер». Чтобы

избегать в процессе нормализации подобных противоречий, Кодд и Бойс

обосновали и предложили более строгое определение для 3НФ, которое

учитывает, что в таблице может быть несколько первичных ключей.

Таблица находится в нормальной форме Бойса-Кодда (НФБК) то-

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

атрибутами сводится к полной функциональной зависимости от воз-

можного первичного ключа.

В соответствии с этой формулировкой таблица «Учебный план»

находится в НФБК или в 3НФ.

В следующих нормальных формах (4НФ и 5НФ) учитываются не

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