После несколько затянувшегося перерыва (который, правда, был употреблен на благое дело - создания инструмента для более легкой и быстрой публикации статей, в том числе и этой), мы продолжаем знакомиться с теорией реляционных баз данных.
Подобно другим отраслям информатики, в реляционной теории нет универсальных рецептов для проектирования надежной и эффективной в использовании базы данных. Разработчик волен выбирать различные инструменты и методы проектирования. Некоторые полагаются исключительно на интуицию и здравый смысл, другие используют различные вспомогательные средства, порой довольно изощренные.
Однако при всем разнообразии подходов все же есть некоторые каноны, нарушение которых весьма отрицательно скажется как при проектировании базы данных, так и при ее эксплуатации. Так, например, весьма актуальной является проблема нормализации баз данных. Пренебрежение нормализацией делает структуру базы данных запутанной, а саму базу - ненадежной в работе.
Итак, основная тема очередной статьи - нормализация баз данных. Однако перед тем как приступить к ее изучению, нам придется разобраться с некоторыми важными понятиями реляционной теории. Одно из них - понятие ключа.
Ключ
Раз уж мы взялись за изучение реляционной теории, а теория эта, как и информатика в целом, базируется на математике, то нам не обойтись без некоторого формализма, даже если кому-то формальный подход покажется излишне усложненным. После формальных определений я постараюсь прокомментировать их и изложить то же самое более доступным языком. Если же моя попытка не увенчается успехом и какие-то моменты останутся неясными - милости просим в мой раздел форума, разберем их подробнее.
Вспомним ранее пройденный материал. Мы уже знаем, что отношение состоит из кортежей (или, более простыми словами, таблица состоит из строк, хотя это не вполне корректно). Естественно, что функции системы управления базами данных не исчерпываются простым накоплением данных; мы хотим также манипулировать содержимым БД. Для этого необходимо иметь возможность каким-то образом отличать кортежи друг от друга.
Приходим к выводу, что кортеж должен содержать в себе какую-то уникальную метку, однозначно идентифицирующую его среди всех остальных кортежей отношения. Такой меткой и является ключ.
Определение. Пусть r - отношение, R - его схема. Ключом K отношения r называется подмножество атрибутов {A1, A2, , Am} <= R, обладающее следующим свойством: для любых двух различных кортежей t1 и t2 из r существует A€K такое, что t1(A) != t2(A). Т.е. не существует двух кортежей, которые бы имели одно и то же значение на всех атрибутах из K. Таким образом, K-значение кортежа однозначно идентифицирует кортеж в r. (Из-за ограничений символьного набора, доступного при публикации статьи на сайте, мне пришлось воспользоваться следующими обозначениями: здесь и далее символ "<=" означает "является несобственным подмножеством", символ "€" "означает принадлежит множеству", а "!=" означает "не равно").
То же самое неформальным языком: ключом отношения называется набор атрибутов отношения, однозначно определяющий кортеж. Или еще проще: ключ - это набор столбцов таблицы, значения которых уникально определяют строку.
Так это выглядит в теории. На практике возникает довольно каверзный вопрос: как убедиться, что данный набор атрибутов действительно является ключом? Казалось бы, проверить это элементарно: взять и отсортировать строки таблицы лексикографически, а потом пройтись по всем строкам и проверить, не имеют ли соседние строки одинаковых значений ключа?
Рецепт и вправду прост, но осложняется тем, что состояние отношения обычно не является константой: кортежи постоянно добавляются, удаляются или меняют значения атрибутов. В результате набор, который является ключом в данный момент, в общем случае вовсе не обязательно будет таковым через некоторое время.
Простой пример. Владелец маленькой фирмы заказал нам систему для ведения кадров и зарплаты. В ходе анализа выясняется, что у него в данный момент работают 3 человека: Иванов, Петров и Сидоров. Мы убеждаемся, что фамилия однозначно определяет работника, и принимаем решение: сделать атрибут "Фамилия" ключом в таблице, где хранится перечень работников.
Система работает некоторое время без проблем. Благодаря наведенному порядку фирма процветает все больше, и в конечном итоге персонал вырастает до сотни человек. Внезапно при приеме очередного работника система дает сбой. Выясняется, что его фамилия тоже Иванов. Таким образом, нарушается уникальность ключа, что делает невозможным дальнейшую работу системы.
Мы пытаемся спасти положение, расширяя ключ на столбцы "Имя" и "Отчество". Программа вновь оживает, но при этом мы знаем, что это всего лишь заметание мусора под ковер: проблема не решена окончательно, а всего лишь уменьшена вероятность очередного сбоя по причине нарушения уникальности ключа.
Итак, оказывается, одной математики здесь недостаточно. Нужно применить знание предметной области, чтобы гарантировать уникальность ключа. В данном случае опыт подсказывает нам, что однофамильцы - далеко не редкое явление в коллективе, а совпадение имени-отчества - тоже вполне предсказуемая ситуация.
Зачастую проблема решается введением так называемого суррогатного ключа: к имеющимся атрибутам, которые отражают свойство реального объекта, добавляется еще один (или более), искусственного происхождения, сам способ получения которого гарантирует уникальность. К суррогатным ключам относятся табельные номера сотрудников, регистрационные номера автомобилей, серийные номера изделий и т.д. Да и номерок, который вы получаете в гардеробе, также можно отнести к суррогатным ключам, которым гардеробщик однозначно помечает вашу персону, чтобы отличить ее от сотен подобных при выдаче вещей.
Здесь возникает довольно интересный вопрос. Предположим, что наше отношение r имеет в качестве ключа множество атрибутов {A1, A2, , Am}. Добавим к этому множеству еще один атрибут, Am+1. Является ли множество {A1, A2, , Am, Am+1} ключом r? Формально - безусловно является, поскольку ключ по-прежнему однозначно идентифицирует кортежи r. Однако чувствуется какой-то подвох. Во-первых, добавленный атрибут в ключе явно лишний, поскольку ключ справлялся с задачей идентификации и без него. Во-вторых, есть опасность, что одним атрибутом это добавление не ограничится, и нам придется иметь дело с целым множеством ключей. Как же ограничить ключевое множество атрибутов действительно необходимым минимумом?
Чтобы выкрутиться из данной ситуации, сделаем определение ключа несколько более строгим:
Ключом K отношения r называется подмножество атрибутов {A1, A2, , Am} <= R, такое, что для любых двух различных кортежей t1 и t2 из r выполняется t1(K) != t2(K), и при этом ни одно собственное подмножество K<K не обладает этим свойством.
Множество SK называется суперключом отношения r, если K - ключ r и K<=SK. Очевидно, что первоначально введенное определение ключа на самом деле относится к суперключу.
Функциональные зависимости
Наберемся еще немного терпения перед тем, как приступить к основной теме статьи - нормализации. Нам предстоит ознакомиться с весьма важным понятием функциональной зависимости (ФЗ).
Как неоднократно упоминалось ранее, хранение данных в реляционных базах данных преследует две цели: понизить избыточность данных и повысить их достоверность. Для этого необходимо иметь возможность накладывать некоторые ограничения как на сами хранимые данные, так и на взаимосвязи и взаимозависимости между ними, т.к. в противном случае вместо базы данных мы получим просто свалку, лишенную всякой структуры. Обычно эти ограничения вытекают из анализа предметной области, которая моделируется нашей информационной системой. Одним из средств формализации информации, полученной в результате такого анализа, являются зависимости между данными.
Существует несколько разновидностей зависимостей, которые рассматриваются реляционной теорией: F-зависимости, MV-зависимости и J-зависимости. В настоящий момент наибольший интерес среди них для нас представляют функциональные зависимости, или F-зависимости.
Перед тем, как дать формальное определение функциональной зависимости, приведу пример (заимствованный из книги: Д.Мейер. Теория реляционных баз данных. - М: Мир, 1987). В этом примере используется отношение График(ПИЛОТ РЕЙС ДАТА ВРЕМЯ-ВЫЛЕТА):
Табл.1
ПИЛОТ РЕЙС ДАТА ВРЕМЯ-ВЫЛЕТА
Кушинг 83 09 авг 10:15
Кушинг 116 10 авг 13:25
Кларк 281 08 авг 05:50
Кларк 301 12 авг 18:35
Кларк 83 11 авг 10:15
Чин 83 13 авг 10:15
Чин 116 12 авг 13:25
Коупли 281 09 авг 05:50
Коупли 281 13 авг 05:50
Коупли 412 15 авг 13:25
Данное отношение содержит информацию о полетах. Каждая строка таблицы расписания представляет собой один вылет и указывает, какой пилот поведет самолет какого рейса в данный день, а также в какое время совершается вылет.
Предположим, что не каждое сочетание данных является допустимым. На совокупность данных накладывается ряд ограничений:
* Каждый рейс имеет определенное время вылета.
* Данный пилот в данные день и время может участвовать только в одном рейсе.
* Для данного рейса и даты назначается только один пилот.
Рассмотрев исходные данные с учетом этих ограничений, мы можем сделать ряд выводов:
* ВРЕМЯ-ВЫЛЕТА функционально зависит от РЕЙСА.
* РЕЙС функционально зависит от {ПИЛОТ, ДАТА, ВРЕМЯ-ВЫЛЕТА}.
* ПИЛОТ функционально зависит от {РЕЙС, ДАТА}.
Итак, если значения кортежа на некотором множестве атрибутов единственным образом определяют значения на другом множестве атрибутов, говорят, что имеет место функциональная зависимость или, короче, F-зависимость.
Если на интуитивном уровне понятно, что представляет собой F-зависимость, попробуем дать ее более строгое определение на основе реляционной алгебры.
Пусть r - отношение со схемой R, X и Y - подмножества R. Отношение r удовлетворяет функциональной зависимости X->Y, если (r WHERE (X=x))[Y] имеет не более одного кортежа для каждого X-значения x. Другими словами, возьмем любые два кортежа t1 и t2. Если X->Y и t1(X)=t2(X), то t1(Y)=t2(Y).
Не правда ли, данное определение весьма сходно с определением ключа из предыдущей главы? Это сходство не случайно. Фактически X является ключом отношения r[XY] (попробуйте убедиться в этом самостоятельно).
Теперь мы, пожалуй, достаточно подкованы теоретически, чтобы приступить к рассмотрению нашей основной темы - нормализации отношений.
Нормализация отношений
Под нормализацией отношения подразумевается процесс приведения отношения к одной из так называемых нормальных форм (или в дальнейшем НФ). Однако перед рассмотрением НФ следует сказать несколько слов, зачем нужна нормализация.
Как уже неоднократно упоминалось в данном цикле, база данных - это не просто хранилище фактов (с этой задачей способны справиться и незатейливые плоские файлы). При проектировании баз данных упор в первую очередь делается на достоверность и непротиворечивость хранимых данных, причем эти свойства не должны утрачиваться в процессе работы с данными, т.е. после многочисленных изменений, удалений и дополнений данных по отношению к первоначальному состоянию БД.
Для поддержания БД в устойчивом состоянии используется ряд механизмов, которые получили обобщенное название средств поддержки целостности. Эти механизмы применяются как статически (на этапе проектирования БД), так и динамически (в процессе работы с БД). Динамические средства поддержки целостности мы рассмотрим в следующих статьях, а сейчас обратим внимание на те ограничения, которым должна удовлетворять БД в процессе создания, независимо от ее наполнения данными. Приведение структуры БД в соответствие этим ограничениям - это и есть нормализация.
В целом суть этих ограничений весьма проста: каждый факт, хранимый в БД, должен храниться один-единственный раз, поскольку дублирование может привести (и на практике непременно приводит, как только проект приобретает реальную сложность) к несогласованности между копиями одной и той же информации. Следует избегать любых неоднозначностей, а также избыточности хранимой информации.
С этими требованиями трудно не согласиться, выглядят они вполне разумно. Но еще труднее следовать им на практике, если они сформулированы столь туманно и неопределенно.
Впрочем, для устранения всяческих неопределенностей и неоднозначностей традиционно используется формальный язык математики. Воспользуемся им и мы, тем более что для реляционных БД, построенных на солидном математическом фундаменте, формализмы достаточно хорошо проработаны.
Итак, наша цель - приведение отношений к НФ. Следует заметить, что в процессе нормализации постоянно встречается ситуация, когда отношение приходится разложить на несколько других отношений. Поэтому более корректно было бы говорить о нормализации не отдельных отношений, а всей их совокупности в БД. Однако в примерах для простоты я по возможности постараюсь иметь дело с отдельными отношениями, если это не приведет к неясностям. Нормализация реальных баз данных - гораздо более трудоемкий процесс.
Впрочем, не все обстоит так плохо. Нормализация может не представлять такую уж проблему, если БД проектируется сразу по определенным канонам. Другими словами, можно сначала сделать БД как попало, а потом нормализовать ее, или же с самого начала строить ее по правилам, чтобы в дальнейшем не пришлось переделывать. Сейчас мы пойдем первым путем, т.е. возьмем в качестве примеров никуда не годные БД и попытаемся привести их в порядок. При этом будем иметь в виду, что при профессиональном подходе к проектированию БД используется одна из хорошо проработанных технологий (я предпочитаю IDEF1X), и, возможно, какие-либо инструментальные средства, поддерживающие эту технологию (мой выбор - AllFusion Data Modeler производства Computer Associates).
Итак, что же представляет собой процесс нормализации? Фактически это не что иное, как последовательное преобразование исходной БД к НФ, при этом каждая следующая НФ обязательно включает в себя предыдущую (что, собственно, и позволяет разбить процесс на этапы и производить его однократно, не возвращаясь к предыдущим этапам). Всего в реляционной теории насчитывается 6 НФ:
* 1-я НФ (обычно обозначается также 1НФ).
* 2НФ.
* 3НФ.
* НФ Бойса-Кодда (НФБК).
* 4НФ.
* 5НФ.
На практике, как правило, ограничиваются 3НФ, ее оказывается вполне достаточно для создания надежной схемы БД. НФ более высокого порядка представляют скорее академический интерес из-за чрезмерной сложности. Более того, при реализации абстрактной схемы БД в виде реальной базы иногда разработчики вынуждены сделать шаг назад - провести денормализацию с целью повышения эффективности, ибо идеальная с точки зрения теории структура может оказаться слишком накладной на практике.
Рассмотрим подробнее три первые НФ.
1НФ
Сначала, как всегда, формальное определение.
Схема отношения R находится в 1НФ, если значения в dom(A) являются атомарными для каждого атрибута A в R.
Другими словами, каждый атрибут отношения должен хранить одно-единственное значение и не являться ни списком, ни множеством значений.
Следует заметить, что, несмотря на внешнюю строгость данного определения, однозначно определить понятие атомарности зачастую оказывается довольно затруднительно, если заранее неизвестны семантика атрибута и его роль в обработке хранимых данных. Атрибут, который является атомарным в одном приложении, может оказаться составным в другом.
Простейший пример: в БД отдела кадров предприятия в таблице, хранящей личные сведения о сотрудниках, имеется атрибут "домашний-адрес", в котором адрес хранится в формате: город, улица, дом[/корпус], [квартира] (следуя общепринятой нотации, здесь в квадратных скобках указаны опциональные фрагменты адреса, которые могут отсутствовать). В данном случае адрес хранится в виде единой текстовой строки, поскольку маловероятно, чтобы потребовалось выбрать сотрудников, скажем, по номеру квартиры. Таким образом, в контексте БД отдела кадров адрес является атомарным понятием, и его деление на составные части не имеет смысла, т.к. только внесет в БД излишнюю громоздкость.
Однако тот же адрес для приложения, предназначенного для сортировки почты в почтовом отделении, атомарным не является, поскольку желательно сгруппировать конверты в отдельные стопки по улицам, так как каждую улицу обслуживает свой почтальон. Кроме того, с целью оптимизации перемещений почтальона в пределах улицы, каждую стопку желательно отсортировать по номерам домов, чтобы сделать возможным разнести почту за один проход по улице без возврата.
Итак, очевидно, что в отрыве от контекста затруднительно дать строгое определение атомарности, за исключением самых простых случаев (например, домен натуральных чисел). В большинстве случаев необходимо располагать сведениями о предметной области, а также о том, каким образом планируется обрабатывать хранимые в БД данные.
Приведение отношения к 1НФ - довольно простая операция. Мы должны просмотреть схему отношения и разделить составные атрибуты на различные строки/столбцы. Возможно, эту операцию придется повторить несколько раз до тех пор, пока каждый из атрибутов не станет атомарным (с учетом сказанного в предыдущем абзаце).
Приведу небольшой пример. (Этот пример, как и остальные в данной статье, любезно предоставила мне Never, за что я ей сердечно благодарен, поскольку это позволило мне сэкономить массу времени и избежать выдумывания достаточно правдоподобных примеров).
В БД имеется таблица контрагентов, в которой хранятся следующие сведения:
* наименование агента;
* город;
* адрес;
* электронный адрес;
* веб-страница;
* вид агента (поставщик или клиент);
* контактные лица (может быть несколько), должность контактного лица, телефон контактного лица.
Табл. 2
Наим. Город Адрес Эл. почта WWW Вид Конт. лица
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик Иванов И.И., зам. дир., тел (3254)76-15-95
Петров П.П., нач. отд. сбыта, тел (3254)76-15-35
ООО Вымпел Курск Ул. Гоголя, 25
[email protected] Клиент Сидоров С.С., директор, тел. (7634)66-65-38
ИЧП Альфа Владимир Ул. Пушкинская, 37, оф. 565
[email protected] Клиент Васильев В.В., директор, тел (3254)74-57-45
Очевидно, что в данном случае атрибут "конт-лица" не является атомарным, поскольку в нем попадаются списки из нескольких лиц. Разделим эти кортежи таким образом, чтобы каждый кортеж содержал данные только об одном лице:
Табл.3
Наим. Город Адрес Эл. почта WWW Вид Конт. лица
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик Иванов И.И., зам. дир., тел (3254)76-15-95
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик Петров П.П., нач. отд. сбыта, тел (3254)76-15-35
ООО Вымпел Курск Ул. Гоголя, 25
[email protected] Клиент Сидоров С.С., директор, тел. (7634)66-65-38
ИЧП Альфа Владимир Ул. Пушкинская, 37, оф. 565
[email protected] Клиент Васильев В.В., директор, тел (3254)74-57-45
Несколько лучше, хотя при ближайшем рассмотрении оказывается, что атрибут "конт-лица" снова может быть назван атомарным лишь с натяжкой, поскольку содержит разнородные данные, хотя и об одном лице.
Разобьем его на несколько атрибутов:
Табл. 4
Наим. Город Адрес Эл. почта WWW Вид Должность Ф.И.О. Код города Тел.
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик зам. дир. Иванов И.И. 3254 76-15-95
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик нач. отд. сбыта Петров П.П., 3254 76-15-35
ООО Вымпел Курск Ул. Гоголя, 25
[email protected] Клиент директор Сидоров С.С. 7634 66-65-38
ИЧП Альфа Владимир Ул. Пушкинская, 37, оф. 565
[email protected] Клиент директор Васильев В.В. 3254 74-57-45
Теперь можем считать, что каждое значение каждого из атрибутов нашего отношения является атомарным. Следовательно, отношение находится в 1НФ.
2НФ
Теперь наше отношение выглядит несколько корректнее. Однако даже беглый взгляд на него говорит о том, что оно пока еще далеко от совершенства. Очевидно, что повторяющиеся значения, которых немало в предыдущей таблице, являются потенциальным источником проблем.
Во-первых, при вводе их значений легко ошибиться. Например, достаточно изменить всего одну букву в графе "Адрес", и формально это будет уже совершенно другой адрес, не имеющий ничего общего с первым. Найти подобные опечатки в объемной таблице - задача не из простых.
Во-вторых, название улицы может измениться, хоть это происходит и не так часто (тем не менее пренебрегать такой возможностью не стоит). Может измениться и адрес веб-сайта предприятия, что гораздо вероятнее. Тогда придется опять же шерстить всю таблицу и изменять соответствующие значения.
В-третьих, нельзя пренебрегать и эффективностью хранения информации. Из нашей таблицы можно как минимум дважды узнать адрес поршневого завода, хотя нам хватило бы и одного раза. В данном примере это мелочи, но на практике встречаются базы пообъемнее. Например, лично мне приходится иметь дело с таблицами, которые за месяц подрастают до нескольких сотен миллионов записей. Как нетрудно прикинуть, лишнее поле размером всего-навсего в 10 байт раздует объем файла, в котором физически размещена таблица, на несколько гигабайт. Помимо того, что эти гигабайты нужно хранить на диске, при операциях с базой их приходится прокачивать из дисковой памяти в оперативную, а порой и в локальную сеть.
Таким образом, борьба с избыточностью данных выгодна со всех сторон - как со стороны повышения удобства пользования данными и поддержания их в целостном виде, так и со стороны эффективности обработки и хранения данных аппаратными средствами сервера баз данных. Именно на устранение этой избыточности и направлена дальнейшая нормализация отношений.
Однако перед тем, как дать определение 2НФ, нам необходимо познакомиться с понятием полной и частичной функциональной зависимостей. По традиции сначала - формальное определение.
Пусть F - множество функциональных зависимостей, при этом ФЗ X->Y F+. Множество Y называется частично зависимым от X относительно F, если ФЗ X->Y не является редуцированной слева (т.е. существует собственное подмножество X множества X, такое, что ФЗ X'->Y F+. Если же ФЗ X->Y является редуцированной слева, то множество Y называется полностью зависимым от X относительно F.
Схема отношения R находится во 2НФ относительно множества функциональных зависимостей F, если она находится в 1НФ и каждый неключевой атрибут полностью зависит от каждого ключа для R.
Теперь для тех, кто предпочитает неформальные определения, попытаюсь дать интуитивно понятный аналог вышесказанного. Другими словами, отношение находится во 2НФ, если оно находится в 1НФ, и при этом все неключевые атрибуты зависят только от ключа целиком, а не от какой-то его части.
Вернемся к нашей табл. 4, которая представляет пример нашего отношения, приведенного к 1НФ. Предположим, что при постановке задачи заказчик сообщил нам, что в пределах каждого города наименование предприятия является уникальным, но в разных городах названия могут совпадать. Таким образом, предприятие характеризуется составным ключом "Наим+Город". Посмотрим на наше отношение с точки зрения того, что мы только что узнали про 2НФ.
Очевидно, что телефонный код города зависит исключительно от самого города и никак не связан с названием предприятия. Отсюда и один из источников избыточных данных - сколько раз в таблице встречается строка, содержащая сведения о контактном лице какого-либо предприятия, находящегося в данном городе, столько раз и повторяется информация о коде города.
Чтобы устранить эту избыточность, нам придется разбить наше отношение на несколько (в данном случае - 2, хотя для более сложных отношений их может быть и гораздо больше):
Табл. 5а
Наим. Город Адрес Эл. почта WWW Вид Должность Ф.И.О. Тел.
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик зам. дир. Иванов И.И. 76-15-95
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик нач. отд. сбыта Петров П.П., 76-15-35
ООО Вымпел Курск Ул. Гоголя, 25
[email protected] Клиент директор Сидоров С.С. 66-65-38
ИЧП Альфа Владимир Ул. Пушкинская, 37, оф. 565
[email protected] Клиент директор Васильев В.В. 74-57-45
Табл. 5б
Город Код города
Владимир 3254
Курск 7634
Итак, мы избавились от частичной зависимости атрибута "Код-города" от составного ключа, переместив коды городов в отдельное отношение с ключом "Город". Таким образом, теперь мы получили два отношения, каждое из которых находится во 2НФ.
Следует обратить внимание на факт, который далеко не всегда явно отражен в литературе по базам данных. Поскольку в данном случае речь идет о частичной зависимости атрибутов от ключа, то все вышесказанное относится исключительно к отношениям с составным ключом. Отношение с простым, или атомарным, ключом (состоящим из единственного атрибута), приведенное к 1НФ, находится во 2НФ по определению и в данном этапе нормализации не нуждается.
Несмотря на предпринятые усилия, таблица 5а определенно все еще содержит изрядную избыточность - достаточно взглянуть на повторяющиеся значения в столбцах "Адрес", "Эл.почта" и "WWW". Значит, процесс нормализации еще не завершен, и пора переходить к его следующей стадии.
3НФ
Для того, чтобы формально определить 3НФ, нам придется предварительно познакомиться с понятием транзитивной зависимости атрибутов, от которой мы попытаемся избавиться на этом этапе.
Обозначим: R - схема отношения, X - подмножество R, A - атрибут в R, F - множество функциональных зависимостей. A называется транзитивно зависимым от X в R, если существует такое Y, являющееся подмножеством R, что:
* X->Y
* ~(Y->X)
* Y->A
* ~(A XY)
Теперь можно дать собственно определение 3НФ:
Схема отношения R находится в 3НФ относительно множества функциональных зависимостей F, если она находится в 1НФ и ни один из непервичных атрибутов в R не является транзитивно зависимым от ключа для R.
Вольное изложение определения: чтобы привести отношение к 3НФ, необходимо устранить функциональные зависимости между неключевыми атрибутами отношения. Другими словами, факты, хранимые в таблице, должны зависеть только от ключа.
В реляционной теории имеется лемма, которая гласит, что любая схема отношения, находящаяся в 3НФ относительно F, находится в 2НФ относительно F.
В нашем случае присутствует функциональная зависимость между атрибутами "Ф.И.О.", "Должность" и "Тел." Очевидно, что на предприятии некий человек занимает определенную должность и располагает определенным рабочим телефоном. Обратное в общем случае неверно - на предприятии может быть несколько аналогичных штатных единиц, например, менеджеры по сбыту, и несколько человек могут пользоваться одним рабочим телефоном.
Чтобы избавиться от данной функциональной зависимости, , проведем декомпозицию таблицы 5а на две таблицы.
Первая из них хранит факты, относящиеся непосредственно к самому предприятию:
Табл. 6а
Наим. Город Адрес Эл. почта WWW Вид
Поршневой з-д Владимир Ул. 2-я Кольцевая, 17
[email protected] www.plunger.ru Поставщик
ООО Вымпел Курск Ул. Гоголя, 25
[email protected] Клиент
ИЧП Альфа Владимир Ул. Пушкинская, 37, оф. 565
[email protected] Клиент
Вторая таблица хранит факты, относящиеся к конкретному лицу, исполняющему некоторые обязанности на данном предприятии:
Табл. 6б
Наим. Город Ф.И.О. Должность Тел.
Поршневой з-д Владимир Иванов И.И. зам. дир. 76-15-95
Поршневой з-д Владимир Петров П.П., нач. отд. сбыта 76-15-35
ООО Вымпел Курск Сидоров С.С. директор 66-65-38
ИЧП Альфа Владимир Васильев В.В. директор 74-57-45
Вместе с таблицей 5б данный набор таблиц представляет собой нашу исходную базу данных, приведенную к 3НФ.
Заключение
Надеюсь, у читателей сложилось определенное представление о процессе нормализации отношений и о его роли при проектировании баз данных. Должен заметить, что для простоты изложения я выбрал несколько надуманные примеры и специально предварительно их "испортил", чтобы в дальнейшем подвергнуть нормализации.
На практике дело обычно обстоит не так. Во-первых, кроме совсем уж зеленых новичков, вряд ли кому придет в голову начинать проектирование базы данных с таблиц, нарушающих 1НФ (возможно, за исключением случаев, когда нужно занести в БД данные, подготовленные табличным процессором вроде Excel).
С составными ключами на практике тоже доводится встречаться не так уж часто. Обычно составной ключ подменяют так называемым суррогатным ключом, т.е. ключом искусственного происхождения, который обычно генерируется самой СУБД, которая и гарантирует его уникальность. Составной ключ в этом случае становится альтернативным ключом, а суррогатный - первичным. Исключением, пожалуй, является ситуация с идентифицирующими зависимостями, когда имеет место миграция первичного ключа родительской сущности в составной первичный ключ дочерней. Но рассмотрение подобных случаев выходит за рамки данной статьи. Надеюсь, что совместными усилиями в свое время мы доберемся и до этого вопроса.
Таким образом, на практике приводить отношения к 1НФ и 2НФ приходится не столь уж часто. Скорее вполне достаточно оказывается проглядеть отношения, чтобы убедиться, что они не нарушают эти нормальные формы.
С 3НФ ситуация несколько сложнее, так как транзитивная зависимость не всегда так явно бросается в глаза. Поэтому нарушение 3НФ - наиболее частая проблема, с которой приходится иметь дело.
Впрочем, нормализация - это фактически исправление огрехов, допущенных при проектировании БД. Как известно, частенько оказывается проще не допускать этих огрехов с самого начала, нежели спроектировать БД кое-как, а потом оптимизировать (чем мы, собственно, и занимались на протяжении всей статьи). Неоценимую помощь в проектировании БД оказывают соответствующие методологии (их несколько, я предпочитаю IDEF1X, разработанную для NASA), а также инструментальные средства для поддержки этих методологий (тут опять же есть выбор, лично я по ряду причин выбрал для себя AlFusion Data Modeler производства Computer Associates).
В следующей статье цикла я намереваюсь отложить на время теорию и спроектировать небольшую, но вполне реальную базу данных для решения задачи, которую мне прислал Lionzi.
Alf.