Затонский А.В. Пособие по полному жизненному циклу информационных систем
Подождите немного. Документ загружается.
241
Перевозка
Дата отправления
Общая масса
Пункт отправления
Пункт назначения
Требуется ли декларирование
Груз
Номер
Масса нетто
Масса брутто
Содержимое
Корабль
Номер корабля в регистре
Название корабля
Грузоподъемность корабля
Рис. 76. ERD морской перевозки в 2НФ
Если корабль все-таки переоборудуется и получает новую
грузоподъемность, как зарегистрировать этот простой факт в 2НФ? Обновить
все записи таблицы «Корабль», включая те, которые были созданы до
переоборудования? Но в этом случае окажется, что корабль ранее плавал
недогруженным, тогда как на самом деле он был полон. Если же мы решим
обновить только те записи «Корабль», которые появляются после
переоборудования, то тоже возникнут проблемы, так как корабль не
эксплуатируется и, вероятно, грузы для него отсутствуют. Существуют и
многие другие проблемы, с которыми мы можем столкнуться
Третья нормальная форма
Сущность находится в третьей нормальной форме, если она находится во
второй нормальной форме и все ее неключевые атрибуты зависят только от
первичного ключа. То есть при этом они не должны зависеть и от других
неключевых атрибутов.
В наших примерах, приведенных на рис. 75 и 76, атрибут «Требуется ли
декларирование» является, по сути, свойством атрибутов «Пункт отправления»
и «Пункт назначения». Если корабль плавает между двумя портами в одной
стране или между двумя странами существует режим свободной торговли, то
таможенной очистке груз не подлежит. Таким образом, пример на рис. 76 явно
нарушает 3НФ – исправим его.
Решение показано на рис. 77. Переносим нарушающие форму атрибуты в
новую дочернюю сущность (в нашем примере она называется «Маршрут»).
Чтобы преобразовать информационную модель в третью нормальную
форму, нужно просто руководствоваться здравым смыслом, призвать на
помощь интуицию и помнить старую пословицу: «Все атрибуты сущности
должны зависеть от ключа, только от ключа и ни от чего, кроме ключа».
242
Перевозка
Дата отправления
Общая масса
Груз
Номер
Масса нетто
Масса брутто
Содержимое
Корабль
Номер корабля в регистре
Название корабля
Грузоподъемность корабля
Маршрут
Пункт отправления
Пункт назначения
Требуется ли декларирование
Рис. 77. ERD морской перевозки в 3НФ
Некоторые опытные люди с подготовкой в области нереляционных
систем баз данных (например, иерархических и сетевых) смотрят на процесс
нормализации с определенной долей скептицизма. Они считают, что мы
создаем при этом большое количество таблиц. Действительно, переходя от рис.
74 к рис. 77, мы получили вместо одной сущности четыре. Эти критики также
трактуют низкую производительность периода выполнения как результат
соединения таблиц с целью перегруппирования данных в первоначальную
форму.
С одной точки зрения (но только с одной), они правы: если у нас есть
уникальный ключ, по которому можно выбрать отдельную ненормализованную
запись, то размещение всех данных в общем котле даст нам самое короткое
время выборки. С другой стороны, современные серверы баз данных не так уж
медленно выполняют соединение четырех таблиц, когда это нужно. Однако
даже при этом несколько утрачивается суть: ведь большинство запросов,
которые придется написать по нормализованным структурам данных, не будут
пытаться полностью восстановить исходную плоскую модель, с которой мы
начали на рис. 74, и вначале у них не будет столь удобного уникального
идентификатора. Многие будут соединять четыре нормализованные таблицы с
помощью ключей «Пункт отправления» и «Дата убытия». А такие
нерегламентированные запросы исключительно дороги по сравнению с
ненормализованными структурами, не говоря уже о всех остальных
недостатках, которые мы уже обсудили.
Третья нормальная форма не является лучшим выходом для всех
приложений. В частности, это не самый удачный метод представления данных
для проектирования хранилища данных
Концептуальные и физические ER-модели
Разработанный выше пример ER-диаграммы складского учета является
примером концептуальной диаграммы. Это означает, что диаграмма не
учитывает особенности конкретной СУБД. По данной концептуальной
диаграмме можно построить физическую диаграмму, которая уже будут
243
учитываться такие особенности СУБД, как допустимые типы и наименования
полей и таблиц, ограничения целостности и т.п. Физический вариант
диаграммы, приведенной на рис. 73, может выглядеть, например, следующим
образом (рис. 78).
Рис. 78. Физический вариант диаграммы на рис. 73
На данной диаграмме каждая сущность представляет собой таблицу базы
данных, каждый атрибут становится колонкой соответствующей таблицы.
Обращаем внимание на то, что во многих таблицах, например,
«CUST_DETAIL» и «PROD_IN_SKLAD», соответствующих сущностям
«Запись списка накладной» и «Товар на складе», появились новые атрибуты,
которых не было в концептуальной модели – это ключевые атрибуты
родительских таблиц, мигрировавших в дочерние таблицы для того, чтобы
обеспечить связь между таблицами посредством внешних ключей.
Полученные таблицы сразу находятся в 3НФ.
Дальнейшие преобразования 3НФ
Дойдя до третьей нормальной формы, не обязательно нужно остановиться
на достигнутом. За третьей нормальной формой следуют:
• нормальная форма Бойса-Кодда (НФБК);
• четвертная нормальная форма (4НФ);
• пятая нормальная форма (5НФ).
Дадим их краткое описание.
Нормальная форма Бойса-Кодда – это, фактически, несколько
улучшенный вариант 3НФ. Аналитикам и программистам нет необходимости
244
использовать четвертую и пятую нормальные формы, а вот проектировщики
должны знать, какие проблемы эти формы решают. Эти проблемы –
неизбежный результат применения составных первичных ключей, в которых
лишь часть ключа сама по себе содержит информацию. Любая структура,
находящаяся в ЗНФ и не имеющая составных ключей, должна также
находиться в 5НФ.
НФБК
Нормальная форма Бойса-Кодда устанавливает дополнительное правило:
все транзитивные зависимости должны быть удалены. Таблица R находится в
нормальной форме Бойса-Кодда, если для каждой нетривиальной ФЗХ -> AX –
суперключ. Что это означает на практике?
Продолжая мореходную тему, предположим, что экипаж судна разделен
на группы, отвечающие за разные виды работ. Член экипажа может входить в
несколько групп, но в каждую группу входит только один руководитель.
Группа, в свою очередь, может иметь несколько руководителей. Кроме того,
член экипажа может руководить только одной группой. Это очень сложный
сценарий. Табл. 9, представленная ниже, находится в третьей нормальной
форме, но противоречит НФБК.
Таблица 9
Распределение экипажа: таблица в ЗНФ, противоречащая НФБК
Член экипажа Название группы Фамилия
руководителя
Джоуор Наблюдение Уэбб
Джоуор Питание Кауи
Уэллс Наблюдение Реймуол
Купер Наблюдение Уэбб
Дерхэм Питание Хардисти
Дерхэм Обслуживание Каулард
Поуд Обслуживание Каулард
Проблема здесь заключается в том, что хотя эта таблица дана в третьей
нормальной форме, все равно имеет место аномалия удаления. Если Уэллса
убрать из группы наблюдения, то мы потеряем дополнительный элемент
несвязанного знания, а именно информацию о том, что Реймуол – руководитель
группы наблюдения. Когда в группу наблюдения будет назначен новый член
экипажа и мы возьмем список руководителей групп, в подчинение которым
можно его назначить, Реймуола в этом списке не будет.
В отличие от предыдущих нормальных форм, где мы могли разбить
таблицу без создания избыточности, здесь мы вынуждены хранить некоторую
245
избыточную информацию, иначе эту головоломку не решить. Исходная таблица
распределения экипажа остается как есть, но дополняется таблицей
руководителей групп (табл. 10).
Таблица 10
Руководители групп: новая таблица, введенная для обеспечения соответствия
НФБК
Название группы Фамилия руководителя
Наблюдение Уэбб
Питание Кауи
Наблюдение Реймуол
Питание Хардисти
Обслуживание Каулард
Теперь, если Уэллса сбросят в море, мы все равно узнаем, что Реймуол
руководит группой наблюдения (несмотря на то, что число членов данной
группы будет равно нулю). Нового члена группы теперь можно назначить в
подчинение Реймуолу.
Четвертая нормальная форма
Эта форма оперирует многозначными зависимостями (МЗЗ). Она решает
проблему, вызванную наличием в таблице более одной МЗЗ. Давайте
рассмотрим таблицу, содержащую сведения о кораблях, совершаемых ими
рейсах и капитанах, которые управляют кораблями в этих рейсах. В качестве
иллюстрации послужит ER-диаграмма, показанная на рис. 79.
Корабль
Название
Капитан
ФИО
Маршрут
Маршрут
Откуда
Куда
Перевозка
Название (FK)
Маршрут (FK)
ФИО (FK)
Рис. 79. Пример, иллюстрирующий нарушение 4НФ
Что здесь неправильно? В сущности «Перевозка» регистрируется
слишком много подробностей. Полученная из нее таблица показана ниже (табл.
11). Здесь нет ФЗ, поэтому она соответствует НФБК. Тем не менее, аномалия
удаления сохранилась. Если Эйрен уволится, и мы каскадно уничтожим во всех
246
таблицах записи о нем, то потеряем все сведения о том, что корабль «Флейта»
плавал между Брюгге и Бостоном. Более того, если удалить новый рейс, то
может понадобиться ввести в табл.11 не одну, а несколько строк.
Таблица 11
Пример таблицы данных в 4НФ
Корабль Капитан Маршрут
Темная лошадка Голл Саутгемптон – Нью-Йорк
Темная лошадка Эйрен Саутгемптон – Н ью-Йорк
Темная лошадка Фальконер Саутгемптон – Нью-Йорк
Темная лошадка Стоун Саутгемптон – Нью-Йорк
Темная лошадка Ла Спина Портсмут – Гавр
Темная лошадка Стоун Портсмут – Гавр
Флейта Эйрен Брюгге_Бостон
Флейта Данфорт Нью-Хейвен – Дьепп
Флейта Фальконер Нью-Хейвен – Дьепп
Эта проблема решается путем разбиения таблицы «Перевозка» на две: в
одной будут столбцы «Корабль» и «Перевозка», a во второй – «Капитан» и
«Перевозка» (рис. 80):
Корабль
Название
Капитан
ФИО
Маршрут
Маршрут
Откуда
Куда
Перевозка
Перевозка
Название (FK)
Маршрут (FK)
Командует
ФИО (FK)
Перевозка (FK)
Рис. 80. Модель, корректная с точки зрения 4НФ (показаны только важные
здесь атрибуты сущностей)
Пятая нормальная форма
Пятую нормальную форму иногда называют нормальной формой с
проецированием, соединений (НФПС). Она может использоваться при
разрешении трех и более сущностей, которые связаны друг с другом
отношениями «многие ко многим». В некоторых случаях разрешение этих
отношений при помощи связующей сущности может привести к получению
дефектной модели, в которой могут появиться несуществующие отношения
(известные как аномалия проецирования соединений). Эта проблема может
247
возникнуть, если CASE-инструмент попытается разрешить за вас отношения
«многие ко многим», поскольку большинство автоматизированных методов
здесь не выявило бы никаких зависимостей. Пример показан на рис. 81.
Автомобиль
Цвет
Название цвета
Модель
Название модели
Рис. 81. Трехсторонняя структура «многие ко многим»
Каждый автомобиль имеет определенный цвет кузова, и каждый
автомобиль имеет определенную модель. Некоторые цвета характерны только
для определенных моделей (например, цвет «металлик» встречается только
моделей более высокого класса).
Если мы разобьем эту проблему на составляющие и будем рассматривать
ее, как три изолированных отношения «многие ко многим», то ее нужно будет
решать путем ввода трех связующих сущностей, которые мы назовем «Цвета
автомобилей», «Цвета моделей» и «Модели автомобилей» (рис. 50).
Полученные в результате таблицы базы данных с примерными данными
представлены в табл. 12.
Автомобиль
Название автомобиля
Цвет
Название цвета
Модель
Название модели
Цвета моделей
Название цвета (FK)
Название модели (FK)
Цвета автомобилей
Название цвета (FK)
Название автомобиля (FK)
Модели автомобилей
Название модели (FK)
Название автомобиля (FK)
Рис. 82. Определения таблиц, которые нарушают 5НФ
248
Таблица 12
Пример содержания таблиц данных
Таблица Поле Значение
Автомобиль Название автомобиля Escort
Автомобиль Название автомобиля Probe
Цвет Название цвета Зеленый металлик
Цвет Название цвета Красный
Цвет Название цвета Серебристый металлик
Цвет Название цвета Синий
Модель Название модели L
Модель Название модели GL
Модель Название модели Ghia
Модели автомобилей Название модели L
Модели автомобилей Название автомобиля Probe
Модели автомобилей Название модели GL
Модели автомобилей Название автомобиля Probe
Цвета автомобилей Название цвета Красный
Цвета автомобилей Название автомобиля Escort
Цвета автомобилей Название цвета Зеленый металлик
Цвета автомобилей Название автомобиля Escort
Цвета автомобилей Название цвета Синий
Цвета автомобилей Название автомобиля Probe
Цвета автомобилей Название цвета Серебристый металлик
Цвета автомобилей Название автомобиля Probe
Цвета моделей Название модели L
Цвета моделей Название цвета Красный
Цвета моделей Название модели L
Цвета моделей Название цвета Синий
Цвета моделей Название модели GL
Цвета моделей Название цвета Красный
Цвета моделей Название модели GL
Цвета моделей Название цвета Синий
Цвета моделей Название модели Ghia
Цвета моделей Название цвета Красный
Цвета моделей Название модели Ghia
Цвета моделей Название цвета Зеленый металлик
Цвета моделей Название модели Ghia
Цвета моделей Название цвета Серебристый металлик
Предположим, что к нам заходит клиентка и хочет заказать автомобиль.
Она предпочитает голубой Ghia, причем модель роли не играет. Поэтому мы
выдаем запрос к базе данных, чтобы посмотреть, из каких автомобилей она
может выбрать. Ниже приведен SQL-запрос к базе данных, используемый для
решения этой задачи:
249
SELECT саr.name
FROM cars car
, car_color_links ccl
, car_model_links cml
WHERE car.name = ccl.name
AND car.name = cml.name
AND ccl.color = 'Blue'
AND cml.model = 'Ghia'
Данный запрос возвращает следующее:
Name
Escort
Probe
Это удивительный результат, так как из таблицы «Цвета моделей» мы
видим, что моделей Ghia голубого цвета нет. Вывод: возвращена ложная
запись. Эта ложная строка – аномалия проецирования соединений и пример
нарушения пятой нормальной формы.
К счастью, правильное решение найти очень просто. Нужно ввести одну
промежуточную сущность, связывающую остальные три, а именно «Цвета
моделей автомобилей» (рис. 83). Правильная форма реализации этой таблицы
представлена в табл. 13.
Автомобиль
Название автомобиля
Цвет
Название цвета
Модель
Название модели
Цвета моделей автомобилей
Название цвета (FK)
Название модели (FK)
Название автомобиля (FK)
Рис. 83. Определения таблиц, которые не нарушают 5НФ
Необходимо отметить, что информационные модели, в которых имеются
нарушения четвертой и пятой нормальной форм, встречаются относительно
редко.
250
Таблица 13
Связующая таблица, иллюстрирующая 5НФ
Автомобиль Цвет Модель
Escort Красный GL
Escort Красный Ghia
Escort Зеленый металлик GL
Escort Зеленый металлик Ghia
Probe Синий L
Probe Синий GL
Недопустимые и необычные структуры в информационной модели
Подавляющее большинство отношений в ERD – это отношения «один ко
многим», причем большей частью с необязательной стороной «много» и
обязательной стороной «один». Отношение такого типа представлено на рис.
84. Его следует читать так: «Каждый отдел может состоять из одного или
более служащих, и каждый служащий должен работать только в одном
отделе».
Рис. 84. Самая распространенная форма отношений
Отношения другого типа стоит рассмотреть более подробно. В частности,
ниже описаны отношения, которые либо недопустимы, либо нелогичны.
Некоторые из них не являются недопустимыми, однако они необычны и
встречаются редко. Одни из них нужно только разъяснить, а другие для
реализации в виде таблиц требуют нетривиальных проектных решений.
«Свиные уши», имеющие сторону с обязательным участием
«Свиное ухо» – это рефлексивное (или рекурсивное) отношение
сущности с другим экземпляром такой же сущности. Если какая-либо из сторон
отношения обязательна, то в результате получается бесконечная иерархия.
Например, отношение, изображенное на рис. 85, подразумевает, что у
служащего должен быть только один начальник. Если это верно, то кто
начальник президента или иного высшего должностного лица компании? В
равной степени недопустимо делать другую сторону отношения обязательной –
в этом случае каждый должен кем-то руководить, что порождает проблемы в