Базы данных

Конспект лекций | назад | содержание

Раздел 6. Нормализация При проектировании базы данных в реляционной СУБД основной целью разработки логической модели данных является создание точного представления данных, связей между ними и требуемых ограничений. Для достижения этой цели необходимо прежде всего определить подходящий набор отношений. Метод, который используется для решения последней задачи, называется нормализацией. Нормализация представляет собой вариант восходящего подхода к проектированию базы данных, который начинается с установления связей между атрибутами. Однако в методологии проектирования баз данных часто используется нисходящий подход, который начинается с выявления основных сущностей и связей, а нормализация используется лишь в качестве метода проверки корректности полученного решения. В любом случае очень важно правильно понимать назначение методов нормализации и способы их эффективного применения на практике. Тема 6.1. Цель нормализации Нормализация. Метод создания набора отношений с заданными свойствами на основе требований к данным, установленным в некоторой организации. Процесс нормализации был впервые предложен Э.Ф.Коддом. Нормализация часто выполняется в виде последовательности тестов для некоторого отношения с целью проверки его соответствия (или несоответствия) требованиям заданной нормальной формы. Сначала были предложены только три вида нормальных форм: первая (1НФ), вторая (2НФ) и третья (ЗНФ). Затем Р.Бойсом и Э.Ф.Коддом (1974) было сформулировано более строгое определение третьей нормальной формы, которое получило название нормальной формы Бойса-Кодда (НФБК). Все эти нормальные формы основаны на функциональных зависимостях, существующих между атрибутами отношения. Вслед за НФБК появились определения четвертой (4НФ) и пятой (5НФ) нормальных форм (1977, 1979). Однако на практике эти нормальные формы более высоких порядков используются крайне редко. Ранее отношение было представлено состоящим из некоторого количества атрибутов, а реляционная схема — из некоторого количества отношений. Атрибуты могут группироваться в отношения с образованием реляционной схемы на основе либо здравого смысла разработчика базы данных, либо посредством вывода реляционной схемы из разработанной ER-диаграммы. При использовании любого из этих двух подходов часто требуется применять некоторый формальный метод, который поможет проектировщику базы данных найти оптимальную группировку атрибутов для каждого отношения в данной схеме. Процесс нормализации является формальным методом, который позволяет идентифицировать отношения на основе их первичных ключей (или потенциальных ключей, как в случае НФБК) и функциональных зависимостей, существующих между их атрибутов. Проектировщики баз данных могут использовать нормализацию в виде наборов тестов, применяемых к отдельным отношениям с целью нормализации реляционной схемы до заданной конкретной формы, что позволит предотвратить возможное возникновение аномалий обновления. Тема 6.2. Избыточность данных и аномалии обновления Основная цель проектирования реляционной базы данных заключается в группировании атрибутов в отношения так, чтобы минимизировать избыточность данных и таким образом сократить объем памяти, необходимый для физического хранения отношений, представленных в виде таблиц. Проблемы, связанные с избыточностью данных, можно проиллюстрировать, сравнив отношения Персонал и Филиал, показанные в таблицах 6.1 и 6.2, с отношением Персонал_Филиал, показанным в табл.6.3. Таблица 6.1
П_Ном | ПФИО | ПАдрес | Должность | Оклад | Филиал_Ном
С21 | Иванов А.К. | Новосибирск | Менеджер | 30000 | В5
С37 | Петрова П.И. | Томск | Ст. инженер. | 12000 | В3
С14 | Зайцев В.Е. | Томск | Заместитель | 18000 | В3
С9 | Волков Н.И. | Барнаул | Инженер | 9000 | В7
С5 | Захарова Н.Г. | Томск | Менеджер | 24000 | В3
С41 | Устинов В.С. | Новосибирск | Инженер | 9000 | В5
Таблица 6.2
Филиал_Ном | ФАдрес | Телефон
В5 | Новосибирск | 66-33-22
В7 | Томск | 44-45-67
В3 | Барнаул | 22-55-34
Таблица 6.3
П_Ном | ПФИО | ПАдрес | Должность | Оклад | Филиал_Ном | ФАдрес | Телефон
С21 | Иванов А.К. | Новосибирск | Менеджер | 30000 | В5 | Новосибирск | 66-33-22
С37 | Петрова П.И. | Томск | Ст. инженер. | 12000 | В3 | Томск | 44-45-67
С14 | Зайцев В.Е. | Томск | Заместитель | 18000 | В3 | Томск | 44-45-67
С9 | Волков Н.И. | Барнаул | Инженер | 9000 | В7 | Барнаул | 22-55-34
С5 | Захарова Н.Г. | Томск | Менеджер | 24000 | В3 | Томск | 44-45-67
С41 | Устинов В.С. | Новосибирск | Инженер | 9000 | В5 | Новосибирск | 66-33-22
Отношение Персонал_Филиал является альтернативной формой представления отношений Персонал и Филиал. Упомянутые отношения описываются следующим образом: Персонал (П_Ном, ПФИО, ПАдрес, Должность, Оклад, Филиал_Ном) Филиал (Филиал_Ном, ФАдрес, Телефон) Персонал _Филиал(П_Ном, ПФИО, ПАдрес, Должность, Оклад, Филиал_Ном, Фадрес, Телефон)В отношении Персонал_Филиал содержатся избыточные данные, поскольку сведения об отделении компании повторяются в записях, относящихся к каждому сотруднику данного отделения. В противоположность этому, в отношении Филиал сведения об отделении содержатся только в одной строке, а в отношении Персонал повторяется только номер отделения компании (Филиал_Ном), который представляет собой место работы каждого сотрудника. При работе с отношениями, содержащими избыточные данные, могут возникать проблемы, которые называются аномалиями обновления и подразделяются на аномалии вставки, удаления и модификации. 6.2.1. Аномалии вставки Существует два основных типа аномалий вставки, которые иллюстрируются с помощью отношения Персонал_Филиал, показанного в табл. 6.3. При вставке сведений о новых сотрудниках в отношение Персонал_Филиал необходимо указать и сведения об отделении компании, в котором эти сотрудники работают. Например, при вставке сведений о новом сотруднике отделения “В7” потребуется ввести сведения о самом отделении “В7”, которые должны соответствовать сведениям об этом же отделении в других строках отношения Персонал_Филиал. Отношения, показанные в табл. 6.1 и 6.2, не могут пострадать от такого потенциального несоответствия данных, поскольку для каждого сотрудника в отношение Персонал потребуется ввести только соответствующий номер отделения компании. Кроме того, сведения об отделении компании с номером “В7” заносятся в базу данных однократно, в виде единственной строки отношения Филиал. Для вставки сведений о новом отделении компании, которое еще не имеет собственных сотрудников, потребуется присвоить значение NULL всем атрибутам описания персонала отношения Персонал_Филиал, включая и личный номер сотрудника П_Ном. Однако, поскольку атрибут П_Ном является первичным ключом отношения Персонал_Филиал, то попытка ввести значение NULL в атрибут П_Ном вызовет нарушение целостности сущностей и потому будет отклонена. Следовательно, в отношение Персонал_Филиал невозможно ввести строку о новом отделении компании, содержащую определитель NULL в атрибуте П_Ном. Структура отношений, представленных в табл. 6.1 и 6.2, позволяет избежать возникновения этой проблемы, поскольку сведения об отделениях компании вводятся в отношение Филиал независимо от ввода сведений о сотрудниках. Сведения о сотрудниках, которые будут работать в новом отделении компании, могут быть введены в отношение Персонал позже. 6.2.2. Аномалии удаления При удалении из отношения Персонал_Филиал строки с информацией о последнем сотруднике некоторого отделения компании, сведения об этом отделении будут полностью удалены из базы данных. Например, после удаления из отношения Персонал_Филиал строки для сотрудника “Волков Н.И.” с личным номером “С9” из базы данных неявно будут удалены все сведения об отделении с номером “В7”. Однако структура отношений, показанных в табл. 6.1 и 6.2, позволяет избежать возникновения этой проблемы, поскольку строки со сведениями об отделениях компании хранятся отдельно от строк со сведениями о сотрудниках. Связывает этих два отношения только общий атрибут Филиал_Ном. При удалении из отношения Персонал строки с номерам сотрудника “С9” сведения об отделении “В7” в отношении Филиал останутся нетронутыми. 6.2.3. Аномалии обновления При попытке изменения значения одного из атрибутов для некоторого отделения компании в отношении Персонал_Филиал (например, номера телефона отделения “В3”) необходимо обновить соответствующие значения в строках для всех сотрудников этого отделения. Если такой модификации будут подвергнуты не все требуемые строки отношения Персонал_Филиал, то в этом случае база данных будет содержать противоречивые введения. В частности, в нашем примере для отделения компании с номером “В3” в сроках, относящихся к разным сотрудникам, ошибочно могут быть указаны разные значения номера телефона этого отделения. Все приведенные выше примеры иллюстрируют то, что представленные в табл. 6.1 и 6.2 отношения Персонал и Филиал обладают более приемлемыми свойствами, чем отношение Персонал_Филиал, представленное в табл. 6.3. Ниже рассматриваются способы применения процесса нормализации для получения правильно спроектированных отношений. Однако вначале следует познакомиться с концепцией функциональной зависимости, которая положена в основу всего провеса нормализации. Свойства соединения без потерь и сохранения зависимости. Выше было продемонстрировано, что отношение Персонал_Филиал подвержено аномалиям обновления и, дабы избежать этих аномалий, лучше всего выполнить его декомпозицию на отдельные отношения Персонал и Филиал. Однако процесс декомпозиции имеет два важных свойства, которые следует учитывать. Первое из них — это свойство соединения без потерь, которое позволяет восстановить любой кортеж исходного отношения, используя соответствующие кортежи меньших отношений, полученных в результате декомпозиции. Второе — свойство сохранения зависимости, которое позволяет сохранять ограничения, наложенные на исходное отношение, посредством наложения некоторых ограничений на каждое из меньших отношений, полученных после декомпозиции. Иначе говоря, в результате отпадает необходимость выполнения соединения результирующих отношений с целью проверки того, не нарушается ли ограничение, наложенное на исходное отношение. Тема 6.3. Функциональные зависимости Функциональная зависимость описывает связь между атрибутами и является одним из основных понятий нормализации. В этом разделе приводится определение данного понятия, а в следующих — описание его взаимосвязи с процессами нормализации отношений базы данных. Функциональная зависимость. Описывает связь между атрибутами отношения. Например. если в отношении R, содержащем атрибуты А и В. атрибут В функционально зависит от атрибута и (что обозначается как A® В), то каждое значение атрибута А связано только с одним значением атрибута В. (Причем каждый из атрибутов А и В может состоять из одного или нескольких атрибутов.) Функциональная зависимость является смысловым (или семантическим) свойством атрибутов отношения. Семантика отношения указывает, как его атрибуты могут быть связаны друг с другом, а также определяет функциональные зависимости между атрибутами в виде ограничений, наложенных на некоторые атрибуты, Рассмотрим отношение с атрибутами А и В, где атрибут В функционально зависит от атрибута A. Если нам известно значение атрибута А, то при рассмотрении отношения с такой зависимостью, в любой момент времени во всех строках этого отношения, содержащих указанное значение атрибута А, мы найдем одно и то же значение атрибута В. Таким образом, если две строки имеют одно и то же значение атрибута А, то они обязательно имеют одно и то же значение атрибута В. Однако для заданного значения атрибута В может существовать несколько различных значений атрибута А, Зависимость между атрибутами А и В можно схематически представить в виде диаграммы, показанной на рис. 6.1. Рис.6.1. Диаграмма функциональной зависимости Детерминант. Детерминантом функциональной зависимости называется атрибут или группа атрибутов, расположенная на диаграмме функциональной зависимости слева от символа стрелки. Например, атрибут А является детерминантом атрибута В. Пример 1 . Функциональные зависимости Рассмотрим атрибуты П_Ном и Должность отношения Персонал, представленного в табл. 6.1. Зная значение атрибута П_Ном (например, “С21”), можно определить должность, занимаемую этим сотрудником (“Менеджер”). Иначе говоря, атрибут Должность функционально зависит от атрибута П_Ном, как показано на рис. 6.2.а. Однако из рис. 6.2.б видно, что обратное утверждение неверно, поскольку атрибут П_Ном функционально не зависит от атрибута Должность. Другими словами, каждый сотрудник может занимать только одну должность, однако может быть несколько сотрудников с одинаковыми должностями. Связь между атрибутами П_Ном и Должность относится к типу 1:1, поскольку для каждого номера сотрудника имеется только одна должность. А связь между атрибутами Должность и П_Ном имеет тип 1:М, так как существует сразу несколько номеров сотрудников (т.е. сотрудников), занимающих одну и ту же должность. В данном примере атрибут П_Ном является детерминантом функциональной зависимости П_Ном—> Должность. Рис. 6.2.а,б. Пример функциональной зависимости Пример 2. Функциональные зависимости отношения Персонал_Филиал Рассмотрим функциональные зависимости отношения Персонал_Филиал, представленного в табл. 6.3. П_Ном ® ПФИО П_Ном ® ПАдрес П_Ном ® Должность П_Ном ® Оклад П_Ном ® Филиал_Ном П_Ном ® Фадрес П_Ном ® Телефон Филиал_Ном ® ФАдрес Филиал_Ном ® Телефон Фадрес ® Филиал_Ном Фадрес ® Телефон Телефон ® Фадрес Телефон ® Филиал_Ном В отношении Персонал_Филиал существует 13 функциональных зависимостей, в которых атрибуты П_Ном, Филиал_Ном, Фадрес, Телефон играют роль детерминантов. (В данном случае предполагается, что каждое отделение компании обладает только одним телефоном.) Ниже показан альтернативный формат представления этих же функциональных зависимостей. П_Ном ® ПФИО, ПАдрес, Должность, Оклад, Филиал_Ном, Фадрес, Телефон Филиал_Ном ® ФАдрес, Телефон ФАдрес ® Филиал_Ном, Телефон Телефон ® Филиал_Ном, ФАдрес Для выявления потенциальных ключей отношения Персонал_Филиал необходимо найти атрибут (или группу атрибутов), который уникальным образом идентифицирует каждую строку этого отношения. Если отношение обладает несколькими потенциальными ключами, то нужно выбрать среди них тот, который станет первичным ключом отношения. Все атрибуты, которые не являются частью первичного ключа должны функционально зависеть от него. Единственным потенциальным ключом отношения Персонал_Филиал, а значит, и его первичным ключом, является атрибут П_Ном, так как все другие атрибуты этого отношения функционально зависят от П_Ном. Хотя атрибуты Филиал_Ном, Фадрес, Телефон являются детерминантами этого отношения, они не являются его потенциальными ключами. Концепция функциональной зависимости является центральным понятием процесса нормализации, который рассматривается в следующих разделах. Тема 6.4. Процесс нормализации Нормализация — это формальный метод анализа отношений на основе их первичного ключа (или потенциальных ключей, как в случае НФБК) и существующих функциональных зависимостей. Он включает ряд правил, которые могут использоваться для проверки отдельных отношений таким образом, чтобы вся база данных могла быть нормализована до желаемой степени нормализации. Если некоторое требование не удовлетворяется, то нарушающее данное требование отношение должно быть декомпозировано на отношения, каждое из которых (в отдельности) удовлетворяет всем требованиям нормализации. Зачастую нормализация осуществляется в несколько последовательно выполняющихся этапов, каждый из которых соответствует некоторой нормальной форме, обладающей известными свойствами. В ходе нормализации формат отношений становится все более строгим и менее уязвимым по отношению к аномалиям обновления. При работе с реляционной моделью данных важно понимать, что только удовлетворение требований первой нормальной формы (1НФ) обязательно для создания отношений приемлемого качества. Все остальные формы могут использоваться по желанию проектировщиков. Однако, для того чтобы избежать аномалий обновления, описанных в разделе 2, нормализацию рекомендуется выполнять как минимум до ЗНФ. На рис. 6.3 показана схема процесса нормализации и продемонстрирована взаимосвязь между разными нормальными формами. Видно, что одни 1НФ-отношения могут находиться во 2НФ, другие 2НФ-отношения — в ЗНФ и т.д Рис 6.3. Схема взаимосвязей между отдельными нормаль-ными формами В следующих разделах процесс нормализации показан на примере преобразования данных, которые в исходном состоянии имели вид формы табличного формата со строками и столбцами. Однако в этой главе технология ЕR-моделирования не используется, а достижение понимания смысла данных в обсуждаемой форме происходит непосредственно в процессе нормализации. Тема 6.5. Первая нормальная форма (1 НФ) Перед обсуждением первой нормальной формы целесообразно предварительно дать определение того состояния, которое предшествует ей. Ненормализованная форма (ННФ) . Таблица, содержащая одну или несколько повторяющихся групп данных. Первая нормальная форма (1НФ ). Отношение, в котором на пересечении каждой строки каждого столбца содержится только одно значение На исходном этапе таблица находится в ненормализованной форме (ННФ) и часто называется ненормализованной таблицей. Для преобразования ненормализованной таблицы в первую нормальную форму (1НФ) в исходной таблице следует найти и устранить все повторяющиеся группы данных. Повторяющейся группой называется группа, состоящая из одного и более атрибутов таблицы, в которой возможно наличие нескольких значений для единственного значения ключевого атрибута таблицы. Существует два подхода исключения повторяющихся групп из ненормализованных таблиц. В первом подходе повторяющиеся группы устраняются путем ввода соответствующих данных в пустые столбцы строк с повторяющимися данными. Иначе говоря, пустые места при этом наполняются дубликатами неповторяющихся данных. Этот подход часто называют "выравниванием" таблицы. Полученная в результате этих действий таблица, которая теперь будет называться отношением, содержит атомарные (или единственные) значения на пересечении каждой строки с каждым столбцом, а потому находится в первой нормальной форме. В результате такого подхода в полученное отношение вносится некоторая избыточность данных, которая в ходе дальнейшей нормализации будет устранена. Во втором подходе один атрибут или группа атрибутов назначаются ключом ненормализованной таблицы, а затем повторяющиеся группы изымаются и помещаются в отдельные отношения вместе с копиями ключа исходной таблицы. Далее в новых отношениях устанавливаются первичные ключи. Иногда ненормализованные отношения могут содержать одну или несколько повторяющихся групп внутри повторяющихся групп первого порядка. В таких случаях данный прием применяется до тех пор, пока повторяющихся групп совсем не останется. Полученный набор отношений будет находиться в первой нормальной форме только тогда, когда ни в одном из них не будет повторяющихся групп атрибутов. Хотя оба этих подхода одинаково корректны, следует отметить, что при использовании второго подхода полученные отношения находятся как минимум в 1НФ и обладают меньшей избыточностью данных. При выборе первого подхода выровненное 1НФ-отношение декомпозируется в ходе дальнейшей нормализации на те же отношения, которые могли бы быть получены с помощью второго подхода. В этом разделе использование обоих этих подходов демонстрируется на примере учебного проекта Мечта. Пример 3. Первая нормальная форма (1НФ) Показанная на рис. 6.4 форма “Арендованные объекты” из учебного проекта Мечта содержит сведения об объектах недвижимости, арендованных клиентом по имени “Иванов А.К.”. Для упрощения этого примера предположим, что клиент арендует некоторый объект только один раз и не может арендовать одновременно сразу несколько объектов.
Мечта Арендованные объекты Дата 1-09-01
Имя клиента Иванов А.К Номер клиента С76
Номер объ- екта | Адрес объекта | Начало аренды | Конец аренды | Аренд-ная плата | Номер вла-дельца | Имя вла- дельца
К4 | Москва, Лосева 12, 34 | 1-07-96 | 31-08-98 | 350 | В040 | Жарова В.С.
К16 | Москва, Кирова 12, 34 | 1-09-98 | 1-09-00 | 450 | В093 | Воронин П.И.
Рис. 6.4. Форма “Арендованные объекты” из учебного проекта Мечта Данные об объектах, арендованных двумя клиентами, “Иванов А.К.” и “Петров В,В,”, преобразуются из формы “Арендованные объекты” в таблицу со строками и столбцами, формат которой представлен в табл. 6.4. Эта исходная таблица данных является примером ненормализованной таблицы (ННФ). Таблица 6.4. Ненормализованная таблица Клиенты_Арендаторы
Но-мер клие-нта | Имя кли-ента | Но-мер объ- екта | Адрес объекта | Начало аренды | Конец аренды | Арен-дная плата | Но-мер вла-дель-ца | Имя вла- дельца
С76 | Иванов А.К. | К4 | Москва, Лосева 12, 34 | 1-07-96 | 31-08-98 | 350 | В040 | Жарова В.С.
|  | К16 | Москва, Кирова 12, 34 | 1-09-98 | 1-09-00 | 450 | В093 | Воронин П.И.
С56 | Волков В.В. | К4 | Москва, Лосева 12, 34 | 1-09-94 | 10-06-96 | 350 | В040 | Жарова В.С.
|  | К36 | Москва, Ленина 44, 87 | 10-10-94 | 1-12-97 | 375 | В093 | Воронин П.И.
|  | К16 | Москва, Кирова 12, 34 | 1-01-98 | 10-08-98 | 450 | В093 | Воронин П.И.
В качестве ключевого атрибута ненормализованной таблицы Клиенты_Арендаторы выберем атрибут Номер клиента. Дальше найдем в ней группы сведений об объектах, которые могут повторяться у разных клиентов. Структура повторяющейся группы имеет следующий вид: Повторяющаяся группа = (Об_Ном, Об_Адрес, Н_Аренды, К_Аренды, Ц_Аренды, Вл_Ном, Вл_Имя) Из-за наличия этой повторяющейся группы на пересечении некоторых строк и столбцов таблицы находится сразу несколько значений. Например, два значения К4 и К16, номера объекта (атрибут Об_Ном) присутствуют в строке клиента Иванов А.К. Чтобы преобразовать ненормализованную таблицу в первую нормальную форму необходимо добиться того, чтобы на пересечении каждой строки и каждого столбца находилось единственное значение. Эта цель достигается путем устранения повторяющихся групп. При использовании первого подхода повторяющаяся группа (сведения об объекте недвижимости) устраняется с помощью ввода в каждую строку с описанием объекта недвижимости соответствующих сведений о клиенте. Полученное в результате этих действий отношение Клиенты_Арендаторы, находящееся в первой нормальной форме, представлено в табл.6.5. Потенциальные ключи этого отношения являются составными и включают следующие группы атрибутов: (Кл_Ном, Об_Ном), (Кл_Ном, Н_Аренды), (Об_Ном, Н_Аренды). В качестве первичного ключа этого отношения выберем группу (Кл_Ном, Об_Ном) и для большей ясности разместим атрибуты данного первичного ключа рядом, с левой стороны отношения. (В нашем примере предполагается, что атрибут К_Аренды не может быть использован в качестве компонента потенциального ключа.) Таблица 6.5. 1НФ отношения Клиенты_Арендаторы
Но-мер клие-нта | Имя кли-ента | Но-мер объ екта | Адрес объекта | Начало аренды | Конец аренды | Арен-дная плата | Но-мер вла-дель-ца | Имя владельца
С76 | Иванов А.К. | К4 | Москва, Лосева 12, 34 | 1-07-96 | 31-08-98 | 350 | В040 | Жарова В.С.
С76 | Иванов А.К. | К16 | Москва, Кирова 12, 34 | 1-09-98 | 1-09-00 | 450 | В093 | Воронин П.И.
С56 | Волков В.В. | К4 | Москва, Лосева 12, 34 | 1-09-94 | 10-06-96 | 350 | В040 | Жарова В.С.
С56 | Волков В.В. | К36 | Москва, Ленина 44, 87 | 10-10-94 | 1-12-97 | 375 | В093 | Воронин П.И.
С56 | Волков В.В. | К16 | Москва, Кирова 12, 34 | 1-01-98 | 10-08-98 | 450 | В093 | Воронин П.И.
Отношение Клиенты_Арендаторы определяется следующим образом:Клиенты_Арендаторы (Кл_Ном, Об_Ном, Кл_Имя, Об_Адрес, Н_Аренды, К_Аренды, Ц_Аренды, Вл_Ном, Вл_Имя) Отношение Клиенты_Арендаторы находится в первой нормальной форме, поскольку на пересечении каждой строки и каждого столбца имеется единственное значение. Это отношение содержит данные о клиентах, арендованных объектах недвижимости и их владельцах, которые несколько раз повторяются. Таким образом, отношение Клиенты_Арендаторы характеризуется значительной избыточностью данных. При физической реализации это 1НФ-отношение будет подвержено аномалиям обновления, описанным ранее. Во избежание этого данное отношение нужно преобразовать во |вторую нормальную форму, как описывается в следующем разделе. При использовании второго подхода повторяющаяся группа (сведения об арендованных объектах недвижимости) удаляется из данного отношения и помещается в другое отношение вместе с копией исходного ключевого атрибута Кл_Ном, как показано в табл. 6.7. (Остаток исходного отношения представлен в табл. 6.6). Затем для нового отношения выбирается собственный первичный ключ. Формат двух вновь созданных 1НФ-отношений будет следующим: Клиенты (Кл_Ном, Кл_Имя)Объекты_Арендаторы (Кл_Ном, Об_Ном, Об_Адрес, Н_Аренды, К_Аренды, Ц_Аренды, Вл_Ном, Вл_Имя) Таблица 6.6. Альтернативное представление первой нормальной формы - отношение Клиенты
Кл_Ном | Кл_Имя
С76 | Иванов А.К.
С56 | Волков В.В.
Таблица 6.7. Альтернативное представление первой нормальной формы - отношение Объекты_Арендаторы
Кл_Ном | Об_Ном | Об_ Адрес | Н_ Аренды | К_ Аренды | Ц_ Аренды | Вл_Ном | Вл_ Имя
С76 | К4 | Москва, Лосева 12, 34 | 1-07-96 | 31-08-98 | 350 | В040 | Жарова В.С.
С76 | К16 | Москва, Кирова 12, 34 | 1-09-98 | 1-09-00 | 450 | ВС093 | Воронин П.И.
С56 | К4 | Москва, Лосева 12, 34 | 1-09-94 | 10-06-96 | 350 | В040 | Жарова В.С.
С56 | К36 | Москва, Ленина 44, 87 | 10-10-94 | 1-12-97 | 375 | В093 | Воронин П.И.
С56 | К16 | Москва, Кирова 12, 34 | 1-01-98 | 10-08-98 | 450 | В093 | Воронин П.И.
Оба отношения - и Клиенты, и Объекты_Арендаторы - находятся в первой нормальной форме, поскольку на пересечении каждой строки и каждого столбца находится единственное значение. Отношение Клиенты содержит данные о клиентах, а отношение Объекты_Арендаторы - об арендованных объектах недвижимости и их владельцах. Однако, как видно из табл. 6.7, последнее отношение также обладает некоторой избыточностью данных, а потому может пострадать от аномалий обновления. Для демонстрации дальнейшего процесса нормализации отношений с переходом от 1НФ к 2НФ будет использоваться только отношение Клиенты_Арендаторы, представленное в табл. 6.5. Однако следует еще раз напомнить, что оба приведенных подхода корректны и в конечном итоге - при продолжении процесса нормализации вплоть до НФБК — приведут к созданию одинаковых отношений. Тема 6.6. Вторая нормальная форма (2НФ) Вторая нормальная форма (2НФ) основана на понятии полной функциональной зависимости, которая описывается ниже. 6.6.1. Полная функциональная зависимость Полная функциональная зависимость . В некотором отношении атрибут В называется полностью функционально зависимым от атрибута А, если атрибут В функционально зависит от значения атрибута А и не зависит ни от какого подмножества значения атрибута А. Функциональная зависимость А® В является полной функциональной зависимостью, если удаление какого-либо атрибута из А приводит к утрате этой зависимости Частичной функциональной зависимостью называется такая зависимость А® В, если в А есть некий атрибут, при удалении которого эта зависимость сохраняется. Например, рассмотрим следующую функциональную зависимость: П_Ном, ПФИО ® Филиал_Ном Здесь каждая пара значений (П_Ном, ПФИО) связана с единственным значением Филиал_Ном. Однако эта функциональная зависимость не является полной, поскольку Филиал_Ном также функционально зависит от подмножества (П_Ном, ПФИО), т.е. от атрибута П_Ном. Другие примеры полной и частичной функциональной зависимостей описываются в следующих разделах. 6.6.2. Определение второй нормальной формы Вторая нормальная форма применяется к отношениям с составными ключами, т.е. к таким отношениям” первичный ключ которых состоит из двух или больше атрибутов. Дело в том, что отношение с первичным ключом на основе единственного атрибута всегда находится, по крайней мере, в 2НФ. Отношение, которое не находится в 2НФ может страдать от аномалий обновления. Например, предположим, что необходимо изменить арендную плату для объекта недвижимости с номером “К4”. Для этого потребуется обновить две строки отношения Клиенты_Арендаторы. Если значение арендной платы будет обновлено только в одной строке, то в результате база данных будет приведена в противоречивое состояние. Вторая нормальная форма (2НФ). Отношение, которое находится в первой нормальной форме и каждый атрибут которого, не входящий в состав первичного ключа, характеризуется полной функциональной зависимостью от этого первичного ключа. Нормализация 1НФ-отношений с образованием 2НФ-отношений включает устранение частичных зависимостей, что демонстрируется на примере отношения Клиенты_Арендаторы, представленного в табл. 6.5. Если в отношении между атрибутами существует частичная зависимость, то функционально-зависимые атрибуты удаляются из него и помещаются в новое отношение вместе с копией их детерминанта. Пример 4. Вторая нормальная форма (2НФ) На рис. 6.5 показаны функциональные зависимости (от ФЗ 1 до ФЗ 6) для отношения Клиенты_Арендаторы с парой атрибутов (Кл_Ном, Об_Ном) в качестве первичного ключа. Отношение Клиенты_Арендаторы обладает следующими функциональными зависимостями: ФЗ 1: Кл_Ном, Об_Ном ® Н_Аренды, К_Аренды (Первичный ключ) ФЗ 2: Кл_Ном ® Кл_Имя (Частичная зависимость) ФЗ 3: Об_Ном ® Об_Адрес, Ц_Аренды, Вл_Ном, Вл_Имя (Частичная зависимость) ФЗ 4: Вл_Ном ® Вл_Имя (Транзитивная зависимость) ФЗ 5: Кл_Ном, Н_Аренды ® Об_Ном, Об_Адрес, К_Аренды, Ц_Аренды, Вл_Ном, Вл_Имя (Потенциальный ключ) ФЗ 6: Об_Ном, Н_Аренды ® Кл_Ном, Кл_Имя, К_Аренды (Потенциальный ключ) После выявления функциональных зависимостей процесс нормализации отношения Клиенты_Арендаторы продолжается проверкой его принадлежности ко второй нормальной форме. Для этого требуется найти хотя бы один случай частичной зависимости от первичного ключа. Нетрудно заметить, что атрибут имени клиента Кл_Имя частично зависит от первичного ключа, иначе говоря, он зависит только от атрибута Кл_Ном (эта зависимость представлена выше как ФЗ 2). Кроме того, атрибуты объекта недвижимости также частично зависят от первичного ключа, но на этот раз только от атрибута Об_Ном (эта зависимость представлена выше как ФЗ 33). В свою очередь, атрибуты арендованных объектов недвижимости (Н_Аренды и К_Аренды) полностью функционально зависят от первичного ключа в целом, т.е. от атрибутов Кл_Ном и Об_Ном. (эта зависимость представлена выше как ФЗ 1). Обратите внимание на то, что на рис. 6.5 показано наличие транзитивной зависимости от первичного ключа (эта зависимость представлена выше как ФЗ 4). Хотя транзитивная зависимость также может послужить причиной аномалий обновления, тем не менее ее присутствие в отношении не нарушает ограничений для 2НФ. Такие зависимости будут устранены при переходе к ЗНФ. Рис. 6.5. Функциональные зависимости отношения Клиенты_Арендаторы Обнаружение частичных зависимостей внутри отношения Клиенты_Арендаторы обозначает, что данное отношение не находится во второй нормальной форме. Для преобразования этого отношения в 2НФ необходимо создать новые отношения, причем так, чтобы атрибуты, не входящие в первичный ключ, были перемещены в них вместе с копией части первичного ключа, от которой они функционально зависят. Применение этого правила в нашем случае приведет к созданию трех новых отношений — Клиенты, Аренда. и Объекты_Владельцы, которые представлены в табл. 6.8, 6.9 и 6.10 соответственно. Теперь эти три отношения находятся во второй нормальной форме, поскольку каждый атрибут, не входящий в первичный ключ, полностью функционально зависит от первичного ключа отношения. Эти отношения имеют следующий вид: Клиенты (Кл_Ном, Кл_Имя) Аренда (Кл_Ном, Об_Ном, Н_Аренды, К_Аренды) Объекты_Владельцы (Об_Ном, Об_Адрес, Ц_Аренды, Вл_Ном, Вл_Имя) Таблица 6.8. Отношение Клиенты
Кл_Ном | Кл_Имя
С76 | Иванов А.К.
С56 | Волков В.В.
Таблица 6.9. Отношение Аренда
Кл_Ном | Об_Ном | Н_ Аренды | К_ Аренды
С76 | К4 | 1-07-96 | 31-08-98
С76 | К16 | 1-09-98 | 1-09-00
С56 | К4 | 1-09-94 | 10-06-96
С56 | К36 | 10-10-94 | 1-12-97
С56 | К16 | 1-01-98 | 10-08-98
Таблица 6.10. Отношение Объекты_Владельцы
Об_Ном | Об_Адрес | Ц_Аренды | Вл_Ном | Вл_Имя
К4 | Москва, Лосева 12, 34 | 350 | В040 | Жарова В.С.
К16 | Москва, Кирова 12, 34 | 450 | В093 | Воронин П.И.
К36 | Москва, Ленина 44, 87 | 375 | В093 | Воронин П.И.
Рис. 6.6а. Функциональные зависимости отношения Клиенты Тема 6.7. Третья нормальная форма (ЗНФ) Хотя 2НФ-отношения в меньшей степени обладают избыточностью данных, чем 1НФ-отношения, они все еще могут страдать от аномалий обновления. Так, при попытке обновления имени владельца недвижимости (например. Воронин П.И. с номером С093 (атрибут Вл_Ном) потребуется обновить две строки отношения Объекты_Владельцы , представленного в табл. 6.10. Если обновить только одну из этих двух строк, база данных попадет в противоречивое состояние. Эта аномалия обновления вызывается транзитивной зависимостью, присутствующей в данном отношении. Она может быть устранена путем приведения данного отношения к третьей нормальной форме. В этом разделе транзитивные зависимости рассматриваются вместе с третьей нормальной формой. Рис.6. 6б. Функциональные зависимости отношения Аренда Рис. 6.6в. Функциональные зависимости отношения Объекты_Владельцы 6.7.1. Транзитивная зависимость Транзитивная зависимость. Если для атрибутов А, В и С некоторого отношения существуют зависимости вида А® В и В® С, то говорят, что атрибут С транзитивно зависит от атрибута А через атрибут В (при условии, что атрибут А функционально не зависит ни от атрибута В, ни от атрибута С). Транзитивная зависимость является описанием такого типа функциональной зависимости, которая возникает при наличии следующих функциональных зависимостей между атрибутами А, В и С: А® В и В® С В данном случае транзитивная зависимость А® С осуществляется через атрибут В. Это утверждение справедливо только в том случае, если атрибут А функционально не зависит от атрибутов В и С. Например, рассмотрим следующие функциональные зависимости внутри отношения Персонал_Филиал, представленного в табл. 6.3. П_Ном ® Филиал_Ном и Филиал_Ном ® Фадрес В этом случае транзитивная зависимость П_Ном ® Филиал_Ном осуществляется через атрибут Филиал_Ном. Данное утверждение справедливо, поскольку атрибут П_Ном не зависит функционально от атрибутов Филиал_Ном и Фадрес. Другие примеры транзитивных зависимостей приводятся в следующих разделах. 6.7.2. Определение третьей нормальной формы Третья нормальная форма. Отношение, которое находится в первой и второй нормальных формах и не имеет не входящих в первичный ключ атрибутов, которые находились бы в транзитивной функциональной зависимости от этого первичного ключа. Нормализация 2НФ-отношений с образованием ЗНФ-отношений включает устранение транзитивных зависимостей. Если в отношении существует транзитивная зависимость между атрибутами, в таком случае транзитивно-зависимые атрибуты удаляются из него и помещаются в новое отношение вместе с копией их детерминанта. Пример 5. Третья нормальная форма (3НФ) Сначала рассмотрим функциональные зависимости, существующие в отношениях Клиенты, Аренда и Объекты_Владельцы. Отношение Клиенты: ФЗ 2 Кл_Ном ® Кл_Имя Отношение Аренда: ФЗ 1 Кл_Ном, Об_Ном ® Н_Аренды, К_Аренды ФЗ 5 Кл_Ном, Н_Аренды ® Об_Ном, К_Аренды ФЗ 6 Об_Ном, Н_Аренды ® Кл_Ном, К_Аренды Отношение Объекты_Владельцы: ФЗ 3 Об_Ном ® Об_Адрес, Ц_Аренды, Вл_Ном, Вл_Имя ФЗ 4 Вл_Ном ® Вл_Имя Все не входящие в первичный ключ атрибуты отношений Клиенты и Аренда функционально зависимы только от их первичных ключей. Следовательно, отношения Клиенты и Аренда не имеют транзитивных зависимостей, а потому они уже находятся в третьей нормальной форме (ЗНФ). Все не входящие в первичный ключ атрибуты отношения Объекты_Владельцы функционально зависят от первичного ключа, за исключением атрибута Вл_Имя, который также зависит и от атрибута Вл_Ном (зависимость ФЗ 4). Это типичный пример транзитивной зависимости, которая имеет место при наличии зависимости не входящего в первичный ключ атрибута (Вл_Имя) от одного или нескольких других атрибутов, также не входящих в первичный ключ (Вл_Ном). Данная транзитивная зависимость схематически показана на рис. 6.5. Для преобразования отношения Объекты_Владельцы в третью нормальную форму необходимо прежде всего удалить упомянутую выше транзитивную зависимость путем создания двух новых отношений Объекты и Владельцы, которые представлены в табл. 6.11 и 6.12 соответственно. Новые отношения имеют следующий вид: Объекты (Об_Ном, Об_Адрес, Ц_Аренды, Вл_Ном) Владельцы ( Вл_Ном, Вл_Имя) Рис. 6.7а. Функциональные зависимости отношения Владельцы Рис. 6.7б. Функциональные зависимости отношения Объекты Таблица 6.11. Отношение Объекты
Об_Ном | Об_Адрес | Ц_Аренды | Вл_Ном
К4 | Москва, Лосева 12, 34 | 350 | С040
К16 | Москва, Кирова 12, 34 | 450 | С093
К36 | Москва, Ленина 44, 87 | 375 | С093
Таблица 6.12. Отношение Владельцы
Вл_Ном | Вл_Имя
В040 | Иванов А.К.
В093 | Волков В.В.
Отношения Объекты и Владельцы находятся в третьей нормальной форме, поскольку в них нет никаких транзитивных зависимостей от первичного ключа. В результате выполнения нормализации представленное в табл. 6.5 исходное отношение Клиенты_Арендаторы было преобразовано в четыре отдельных отношения, каждое из которых находится в третьей нормальной форме. На рис. 6.8 приведена схема данного процесса, поясняющая, как 1НФ-отношение было преобразовано в четыре ЗНФ-отношения, имеющие следующий вид: Клиенты (Кл_Ном, Кл_Имя) Аренда (Кл_Ном, Об_Ном, Н_Аренды, К_Аренды) Объекты (Об_Ном, Об_Адрес, Ц_Аренды, Вл_Ном) Владельцы ( Вл_Ном, Вл_Имя) Рис. 6.8. Схема декомпозиции 1НФ-отношения Клиенты_Арендаторы Исходное отношение Клиенты_Арендаторы, представленное в табл.6.5, может быть восстановлено путем соединения отношений Клиенты, Аренда, Объекты и Владельцы. Данная цель достигается за счет использования первичных и внешних ключей. Например, атрибут Вл_Ном является первичным ключом отношения Владельцы и, кроме того, присутствует в отношении Объекты как его внешний ключ. Атрибут Вл_Ном, используемый для создания пары из первичного и внешнего ключей, позволяет связать отношения Объекты и Владельцы с целью определения имен владельцев объектов недвижимости, сдаваемых в аренду. Атрибут Кл_Ном является первичным ключом отношения Клиенты и, дополнительно, внешним ключом отношения Аренда. Обратите внимание на то, что в отношении Аренда атрибут Кл_Ном выполняет функции как внешнего, так и части первичного ключа. Аналогичным образом, атрибут Об_Ном является первичным ключом отношения Объекты и, дополнительно, в отношении Аренда выполняет функции как внешнего ключа, так и части первичного ключа. Иначе говоря, процесс нормализации заключается в декомпозиции исходного отношения Клиенты_Арендаторы посредством последовательного выполнения нескольких операций проекции реляционной алгебры. Полученные в результате декомпозиции отношения обеспечивают выполнение их соединения без потерь, поэтому данную процедуру иначе называют беспроигрышной декомпозицией. Результаты декомпозиции можно обратить посредством операции естественного соединения. Тема 6.8. Нормальная форма Бойса-Кодда (НФБК) Отношения базы данных проектируются таким образом, чтобы исключить в них присутствие частичных или транзитивных зависимостей, поскольку эти зависимости приводят к появлению аномалий обновления. До сих пор мы использовали определения второй и третьей нормальных форм, для получения которых требуется найти и исключить частичные и транзитивные зависимости от первичного ключа. Однако в этих определениях не рассматриваются такие же зависимости от потенциальных ключей отношения, если таковые имеются. 6.8.1. Определение нормальной формы Бойса-Кодда Нормальная форма Бойса-Кодда (НФБК) учитывает функциональные зависимости, в которых участвуют все потенциальные ключи отношения, а не только его первичный ключ. Для отношения с единственным потенциальным ключом его ЗНФ и ФБК являются эквивалентными. Нормальная форма Бойса-Кодда (НФБК). Отношение находится в НФБК тогда и только тогда, когда каждый его детерминант является потенциальным ключом. Для проверки принадлежности отношения к НФБК необходимо найти все его детерминанты и убедиться в том, что они являются потенциальными ключами. Напомним, что детерминантом является один атрибут или группа атрибутов, от которой полностью функционально зависит другой атрибут. Различие между 3НФ и НФБК заключается в том, что функциональная зависимость А ® В допускается в 3НФ-отношении, если атрибут В является первичным ключом, а атрибут А не обязательно является потенциальным ключом. Тогда как в НФБК-отношении эта зависимость допускается только тогда, когда атрибут А является потенциальным ключом. Следовательно нормальная форма Бойса-Кодда является жесткой версией формы 3НФ, поскольку каждое НФБК-отношение является 3НФ-отношением, но не всякое 3НФ-отношение является НФБК-отношением. Перед тем как обратиться к очередному примеру, еще раз рассмотрим отношения Клиенты, Аренда, Объекты и Владельцы. Отношения Клиенты, Объекты и Владельцы являются НФБК-отношениями, так как каждое из них имеет только один детерминант, который в то же время является потенциальным ключом этого отношения. Тут следует напомнить, что отношение Аренда содержит три детерминанта - (Кл_Ном, Об_Ном), (Кл_Ном, Н_Аренды) и (Об_Ном, Н_Аренды), — которые были выявлены нами в примере 5 и имеют вил, показанный ниже. ФЗ 1 Кл_Ном, Об_Ном ® Н_Аренды, К_Аренды ФЗ 5 Кл_Ном, Н_Аренды ® Об_Ном, К_Аренды ФЗ 6 Об_Ном, Н_Аренды ® Кл_Ном, К_Аренды Поскольку эти три детерминанта отношения Аренда являются также потенциальными ключами, то отношение Аренда находится в НФБК. Нарушения требований НФБК происходят крайне редко, поскольку это может случиться только тогда, когда: имеются два (или более) составных потенциальных ключа; эти потенциальные ключи перекрываются, т.е. ими совместно используется, по крайней мере, один общий атрибут. В следующим примере описывается ситуация, когда отношение нарушает требования НФБК, и представлен метод преобразования этого отношения в НФБК. Пример 6. Нормальная форма Бойса-Кодда (НФБК) В этом примере мы рассмотрим отношение Собеседования, которое содержит сведения о собеседованиях клиентов с сотрудниками компании Мечта. Для проведения собеседования в тот день, на который назначена встреча с клиентом, в распоряжение сотрудника предоставляется особая комната. Однако в течение рабочего дня эта комната может использоваться несколькими разными сотрудниками. С клиентом проводится только одно собеседование в день, но он может участвовать в нескольких собеседованиях в разные дни. Обсуждаемое отношение имеет три потенциальных ключа: (Кл_Ном. Дата_Беседы), (Сотр_Ном, Дата_Беседы) и (Комната_Ном, Дата_Беседы, Время_Беседы). Следовательно, отношение Собеседования обладает тремя составными потенциальными ключами, которые перекрываются, т.е. ими совместно используется один общий атрибут Дата_Беседы. В качестве первичного ключа данного отношения выбрана комбинация атрибутов (Кл_Ном. Дата_Беседы). Это отношение представлено в табл. 6.13 и имеет следующий вид: Собеседования (Кл_Ном, Дата_Беседы, Время_Беседы, Сотр_Ном,) Таблица 6.13. Отношение Собеседования
Кл_Ном | Дата_Беседы | Время_ Беседы | Сотр_Ном | Комната_Ном
С76 | 13.05.00 | 10.30 | П5 | К101
С56 | 13.05.00 | 12.00 | П5 | К101
С74 | 13.05.00 | 12.00 | П37 | К102
С56 | 1.07.00 | 10.30 | П5 | К102
Оно содержит следующие функциональные зависимости: ФЗ 1: Кл_Ном, Дата_Беседы ® Время_Беседы, Сотр_Ном, Комната_Ном (Первичный ключ) ФЗ 2: Сотр_Ном, Дата_Беседы, Время_Беседы ® Кл_Ном (Потенциальный ключ) ФЗ 3: Комната_Ном, Дата_Беседы, Время_Беседы ® Сотр_Ном, Кл_Ном (Потенциальный ключ) ФЗ 4: Сотр_Ном, Дата_Беседы ® Комната_Ном Рис. 6.9. Функциональные зависимости отношения Собеседования Рассмотрим эти функциональные зависимости для определения нормальной формы отношения Собеседования. Поскольку функциональные зависимости ФЗ 1, ФЗ 2 и ФЗ 3 являются потенциальными ключами этого отношения, то они не вызовут никаких проблем. Нам потребуется рассмотреть только функциональную зависимость ФЗ 4. Даже если комбинация атрибутов (Сотр_Ном, Дата_Беседы) не является потенциальным ключом отношения Собеседования, эта функциональная зависимость допускается в ЗНФ, потому что атрибут Комната_Ном является атрибутом первичного ключа и частью потенциального ключа (Комната_Ном, Дата_Беседы, Время_Беседы. Поскольку в этом отношении нет никаких частичных или транзитивных зависимостей от первичного ключа (Кл_Ном, Дата_Беседы ) и допускается наличие функциональной зависимости ФЗ 4, можно считать, что отношение Собеседования находится в 3НФ. Однако это отношение не находится в НФБК (более строгий вариант 3НФ), так как в нем присутствует детерминант (Сотр_Ном, Дата_Беседы), который не является потенциальным ключом этого отношения. В НФБК требуется, чтобы все детерминанты отношения были его потенциальными ключами. В противном случае отношение Собеседования может страдать от аномалий обновления. Например, 13 мая 2000 года при изменении номера комнаты, выделенной сотруднику “П5”, потребуется обновить значения в двух строках. Если при этом будет обновлена только одна строка, то база данных будет приведена в противоречивое состояние. Для преобразования отношения Собеседования в форму НФБК необходимо устранить нарушающую ее ограничения функциональную зависимость посредством создания двух новых отношений — Беседы и Персонал_Комнаты, — представленных в табл. 6.14 и 6.15 соответственно. Отношения Беседы и Персонал_Комнаты имеют следующий вид: Таблица 6.14. Отношение Беседы
Кл_Ном | Дата_Беседы | Время_ Беседы | Сотр_Ном
С76 | 13.05.00 | 10.30 | П5
С56 | 13.05.00 | 12.00 | П5
С74 | 13.05.00 | 12.00 | П37
С56 | 1.07.00 | 10.30 | П5
Таблица 6.15. Отношение Персонал_Комнаты
Сотр_Ном | Дата_Беседы | Комната_Ном
П5 | 13.05.00 | К101
П37 | 13.05.00 | К102
П5 | 1.07.00 | К102
Беседы (Кл_Ном, Дата_Беседы , Время_Беседы, Сотр_Ном) Персонал_Комнаты (Сотр_Ном, Дата_Беседы, Комната_Ном) Функциональные зависимости отношений Беседы и Персонал_Комнаты приведены на рис. 6.10 и 6.11. Рис. 6.10. Функциональные зависимости отношения Беседы Рис. 6.11. Функциональные зависимости отношения Беседы Замечание. Любое отношение, которое не находится в НФБК, можно декомпозировать с образованием НФБК-отношений, однако делать это не всегда желательно. Например, декомпозиция будет нежелательна, если в результате ее выполнения утрачивается некоторая функциональная зависимость (т.е. детерминант и определяемые им атрибуты помещаются в разные отношения). В этой ситуации будет трудно обеспечить исходную функциональную зависимость отношения и важное ограничение может быть утрачено. Если имеет место упомянутая ситуация, то лучше закончить процесс нормализации на этапе образования 3НФ-отношений, в которых все требуемые зависимости всегда сохраняются. Обратите внимание на то, что в примере 6 при создании двух новых НФБК-отношений на основе исходного отношения Собеседования утрачивается следующая функциональная зависимость: Комната_Ном, Дата_Беседы, Время_Беседы ® Сотр_Ном, Кл_Ном (зависимость ФЗ 3), поскольку детерминант этой зависимости больше не будет находиться в том же отношении, что и определяемые им атрибуты. Однако следует признать, что если не устранить функциональную зависимость Сотр_Ном, Дата_Беседы ® Комната_Ном (зависимость ФЗ4), то отношение Собеседования будет обладать избыточностью данных. Тема 6.9. Обзор процесса нормализации (от 1НФ до НФБК) Основная цель этого раздела — представить краткий обзор всего процесса нормализации, который был подробно описан в предыдущих разделах. В качестве иллюстрации воспользуемся еще одним примером из учебного проекта Мечта, что позволит нам еще раз внимательно изучить весь процесс преобразования ненормализованнях данных, вплоть до приведения их к нормальной форме Бойса-Кодда. Пример 7. Нормализация от 1НФ до НФБК Компания Мечта выполняет операции с объектами недвижимости по поручению их владельцев. Как часть этой деятельности, предусматриваются регулярные проверки состояния объектов недвижимости, выполняемые сотрудниками компании. Для проведения таких проверок в назначенный день сотруднику компании предоставляется автомобиль. Причем в течение дня один автомобиль может предоставляться нескольким сотрудникам. Сотрудник может провести несколько сверок в один день, однако любой объект недвижимости в течение дня может сверяться только один раз. На рис. 6.12 показан пример страницы отчета “Отчет проверки объектов недвижимости” из учебного проекта Мечта, содержащего сведения о выполненных проверках состояния объектов недвижимости. В данном случае отчет содержит сведения о проверках объекта недвижимости под номером К4.
Стр. 1 МЕЧТА 1 августа 2003 Отчет проверки объектов недвижимости
Номер объекта: Н4 Адрес: Новосибирск, Лазурная 7
Дата проверки | Время проверки | Комментарий | Номер сотрудника | ФИО сотрудника | Номер автомобиля
18.10.01 | 10.00 | Требуется замена ламп | СН37 | Иванов А.К. | 111АВ54
22.04.02 | 9.00 | В хорошем состоянии | СН14 | Волкова А.Е. | 232КЕ54
1.10.03 | 12.00 | Неисправность крана | СН14 | Волкова А.Е. | 445НВ54
Рис. 6.12. Отчет проверки объектов недвижимости из учебного проекта Мечта Первая нормальная форма (1НФ). Сначала мы преобразуем в формат таблицы со строками и столбцами некоторые данные, выбранные из двух отчетов о проверке объектов недвижимости под номерами 'Н4' и 'Н16'. Полученной ненормализованной таблице присвоим имя Недвижимость_Проверка — ее содержимое представлено в табл. 6.16. В качестве ключа для этой ненормализованной таблицы выберем атрибут Недв_Ном. Таблица 6.16. Ненормализованная таблица Недвижимость_Проверка
Н_ Ном | Н_Адрес | Пров_ Дата | Пров_Вр | Коммент | Сотр_ Ном | Сотр_ФИО | Авт_Ном
Н4 | Новоси_ бирск, Лазурная 7 | 18.10.01 | 10.00 | Требуется замена ламп | СН37 | Иванов А.К. | 111АВ54
22.04.02 | 9.00 | В хорошем состоянии | СН14 | Волкова А.Е. | 232КЕ54
1.10.03 | 12.00 | Неисправ-ность в ванной | СН14 | Волкова А.Е. | 445НВ54
Н16 | Новоси_ бирск, Морская 12 | 22.04.01 | 13.00 | Заменить ковер | СН14 | Волкова А.Е. | 232КЕ54
24.10.02 | 14.00 | В хорошем состоянии | СН37 | Иванов А.К. | 445НВ54
В этой ненормализованной таблице легко можно обнаружить повторяющуюся группу о сведениями о проверке объекта недвижимости и сотрудниках, которые их проводили. Структура повторяющейся группы выглядит так, как показано ниже.Повторяющаяся группа = (Пров_Дата, Пров_Вр, Коммент, Сотр_Ном, Сотр_ФИО, Авт_Ном)В результате этого на пересечении строки и столбца можно найти сразу несколько значений. Например, для одного значения атрибута Н_Ном ('Н4') приводятся сразу три значения атрибута Пров_Дата ('18.10.01', '22.04.02', '1.10.03'). Необходимо преобразовать ненормализованную таблицу в первую нормальную форму, для чего воспользуемся первым подходом, описанным ранее. В соответствии с этим подходом устранение повторяющихся групп (сведения о проверке объекта недвижимости и сотрудниках) будет осуществляться посредством ввода в каждую строку требуемых сведений об объектах недвижимости (неповторяющиеся данные). В результате этих действий будет получена первая нормальная форма отношения Недвижимость_Проверка, представленная в табл. 6.17. Таблица 6.17. Отношение Недвижимость_Проверка в 1 НФ
Н_ Ном | Н_Адрес | Пров_ Дата | Пров_Вр | Коммент | Сотр_ Ном | Сотр_ФИО | Авт_Ном
Н4 | Новоси_ бирск, Лазурная 7 | 18.10.01 | 10.00 | Требуется замена ламп | СН37 | Иванов А.К. | 111АВ54
Н4 | Новоси_ бирск, Лазурная 7 | 22.04.02 | 9.00 | В хорошем состоянии | СН14 | Волкова А.Е. | 232КЕ54
Н4 | Новоси_ бирск, Лазурная 7 | 1.10.03 | 12.00 | Неисправ-ность в ванной | СН14 | Волкова А.Е. | 445НВ54
Н16 | Новоси_ бирск, Морская 12 | 22.04.02 | 13.00 | Заменить ковер | СН14 | Волкова А.Е. | 232КЕ54
Н16 | Новоси_ бирск, Морская 12 | 24.10.02 | 14.00 | В хорошем состоянии | СН37 | Иванов А.К. | 445НВ54
Отношение Недвижимость_Проверка имеет три потенциальных ключа: (Н_Ном, Пров_Дата), (Сотр_Ном, Пров_Дата, Пров_Вр) и (Авт_Ном, Пров_Дата, Пров_Вр). В качестве первичного ключа этого отношения выбрана комбинация атрибутов (Н_Ном, Пров_Дата). Для большей ясности разместим рядом атрибуты, образующие первичный ключ, причем с левой стороны отношения. Отношение Недвижимость_Проверка определяется следующим образом: Недвижимость_Проверка (Н_Ном, Пров_Дата, Пров_Вр, Н_Адрес, Коммент, Сотр_Ном, Сотр_ФИО, Авт_Ном) Отношение Недвижимость_Проверка находится в первой нормальной форме, поскольку на пересечении каждой строки и каждого столбца находится только одно значение. Это отношение содержит данные о проверке объектов недвижимости, причем сведения об объекте недвижимости и сотрудниках повторяются в них по нескольку раз. В результате отношение Недвижимость_Проверка характеризуется значительной избыточностью данных. Кроме того, при использовании на практике это 1НФ-отношение будет подвержено аномалиям обновления. Для устранения некоторых из них необходимо преобразовать данное отношение во вторую нормальную форму. Вторая нормальная форма (2НФ). Нормализация 1НФ-отношений с образованием 2НФ-отношений включает устранение частичных зависимостей от первичного ключа. Если в данном отношении существуют частичные зависимости, то нужно удалить функционально зависимые атрибуты из этого отношения и поместить их в новое отношение, вместе с копией их детерминанта. Функциональные зависимости (от ФЗ1 до ФЗ6) отношения Недвижимость_Проверка с первичным ключом Н_Ном, Пров_Дата) можно представить в следующем виде:
ФЗ1 | Н_Ном, Пров_Дата→Пров_Вр, Н_Адрес, Коммент, Сотр_Ном, Сотр_ФИО, Авт_Ном | Первичный ключ
ФЗ2 | Н_Ном→ Н_Адрес | Частичная зависимость
ФЗ3 | Сотр_Ном→ Сотр_ФИО | Транзитивная зависимость
ФЗ4 | Сотр_Ном, Пров_Дата→ Авт_Ном |
ФЗ5 | Авт_Ном, Пров_Дата, Пров_Вр→ Н_Ном, Н_Адрес, Коммент, Сотр_Ном, Сотр_ФИО | Потенциальный ключ
ФЗ6 | Сотр_Ном, Пров_Дата, Пров_Вр→ Н_Ном, Н_Адрес, Коммент, Авт_Ном, Сотр_ФИО | Потенциальный ключ
После выявления функциональных зависимостей процесс нормализации может быть продолжен. Сначала следует найти имеющиеся частичные зависимости от первичного ключа и тем самым проверить, не находится ли это отношение во второй нормальной форме (2НФ). Сразу же можно заметить, что атрибут объекта недвижимости Н_Адрес частично зависит от части первичного ключа, а именно от атрибута Н_Ном (эта зависимость обозначена как ФЗ2). Атрибуты Пров_Дата, Коммент, Сотр_Ном, Сотр_ФИО, Авт_Ном полностью функционально зависят от всего первичного ключа в целом (атрибуты Н_Ном, Пров_Дата) (эта зависимость обозначена как ФЗ1). Обратите внимание на то, что, хотя для детерминанта функциональной зависимости Сотр_Ном, Пров_Дата→Авт_Ном (эта зависимость обозначена как ФЗ4) достаточно использовать только атрибут Пров_Дата первичного ключа, на данном этапе нормализации эта зависимость еще не устраняется, поскольку указанный детерминант также включает другой атрибут, не входящий в состав первичного ключа, имеется в виду атрибут Сотр_Ном. Иначе говоря, эта зависимость не полностью зависит от части первичного ключа, а потому не нарушает требований 2НФ. Наличие частичной зависимости (Н_Ном→ Н_Адрес) указывает на то, что отношение Недвижимость_Проверка не находится во второй нормальной форме. Для преобразования этого отношения во вторую нормальную форму потребуется создать новые отношения так, чтобы атрибуты, которые не полностью функционально зависят от первичного ключа, были связаны только с соответствующей частью ключа. Отношение Недвижимость_Проверка может быть преобразовано во вторую нормальную форму путем удаления частичной зависимости из этого отношения посредством создания двух новых отношений Недвижимость и Недвиж_Проверка. Оба этих отношения находятся во второй нормальной форме, так как атрибуты, не входящие в первичный ключ, функционально зависят от первичного ключа этих отношений. Содержимое этих отношений представлено в табл. 6.18 и 6.19 соответственно, а описание имеет следующий вид: Таблица 6.18. Отношение Недвижимость
Н_Ном | Н_Адрес
Н4 | Новосибирск, Лазурная 7
Н16 | Новосибирск, Морская 12
Таблица 6.19. Отношение Недвиж_Проверка
Н_Ном | Пров_ Дата | Пров_Вр | Коммент | Сотр_ Ном | Сотр_ФИО | Авт_Ном
Н4 | 18.10.01 | 10.00 | Требуется замена ламп | СН37 | Иванов А.К. | 111АВ54
Н4 | 22.04.02 | 9.00 | В хорошем состоянии | СН14 | Волкова А.Е. | 232КЕ54
Н4 | 1.10.03 | 12.00 | Неисправ-ность в ванной | СН14 | Волкова А.Е. | 445НВ54
Н16 | 22.04.02 | 13.00 | Заменить ковер | СН14 | Волкова А.Е. | 232КЕ54
Н16 | 24.10.02 | 14.00 | В хорошем состоянии | СН37 | Иванов А.К. | 445НВ54
Недвижимость (Н_Ном, Н_Адрес) Недвиж_Проверка (Н_Ном, Пров_Дата, Пров_Вр, Коммент, Сотр_Ном, Сотр_ФИО, Авт_Ном) Третья нормальная форма (ЗНФ). Нормализация 2НФ-отношений с получением ЗНФ-отношений включает устранение транзитивных зависимостей. При наличии транзитивных зависимостей следует удалить транзитивно зависимые атрибуты из этого отношения и поместить их новое отношение вместе с копией их детерминанта. В отношениях Недвижимость и Недвиж_Проверка следует выявить и проанализировать имеющиеся функциональные зависимости. Результаты этой работы показаны ниже. Отношение Недвижимость ФЗ2 Н_Ном→ Н_Адрес Отношение Недвиж_Проверка ФЗ1 Н_Ном, Пров_Дата→Пров_Вр, Коммент, Сотр_Ном, Сотр_ФИО, Авт_Ном ФЗ3 Сотр_Ном→ Сотр_ФИО ФЗ4 Сотр_Ном, Пров_Дата→ Авт_Ном ФЗ5 Авт_Ном, Пров_Дата, Пров_Вр→ Н_Ном, Коммент, Сотр_Ном, Сотр_ФИО ФЗ6 Сотр_Ном, Пров_Дата, Пров_Вр→ Н_Ном, Коммент, Авт_Ном, Сотр_ФИО Поскольку отношение Недвижимость не содержит транзитивных зависимостей от первичного ключа, оно уже находится в третьей нормальной форме (ЗНФ). Однако, хотя все не входящие в первичный ключ атрибуты отношения Недвиж_Проверка функционально зависят от первичного ключа, атрибут Сотр_ФИО также зависит от атрибута Сотр_Ном (эта зависимость обозначена как ФЗ3). Это пример транзитивной зависимости, которая возникает в случае, если не входящий в первичный ключ атрибут зависит от другого атрибута, который тоже не входит в первичный ключ. Следует также заметить, что в случае функциональной зависимости Сотр_Ном, Пров_Дата→Авт_Ном (эта зависимость обозначена как ФЗ4) не входящий в первичный ключ атрибут Авт_Ном частично зависит от не входящего в первичный ключ атрибута Сотр_Ном. На этой стадии нормализации мы не будем устранять эту зависимость, поскольку часть детерминанта этой зависимости содержит атрибут, входящий в первичный ключ Пров_Дата. Иначе говоря, эта зависимость не полностью транзитивно зависит от атрибутов, не входящих в первичный ключ, а потому она не нарушает требований ЗНФ. (Иначе говоря, как описано в разделе 6.8.1, при рассмотрении всех потенциальных ключей отношения наличие зависимости Сотр_Ном, Пров_Дата→Авт_Ном в ЗНФ допускается, поскольку атрибут Авт_Ном входит в первичный ключ, так как он является частью потенциального ключа (Авт_Ном, Пров_Дата, Пров_Вр) исходного отношения Недвиж_Проверка). Для преобразования отношения Недвиж_Проверка в ЗНФ необходимо устранить транзитивную зависимость Сотр_Ном→ Сотр_ФИО. Транзитивная зависимость устраняется путем создания двух новых отношений Персонал и Недвиж_Пров: Персонал(Сотр_Ном, Сотр_ФИО) Недвиж_Пров (Н_Ном, Пров_Дата, Пров_Вр, Коммент, Сотр_Ном, Авт_Ном) Отношения Персонал и Недвиж_Пров находятся в третьей нормальной форме, поскольку ни один их атрибут, не входящий в первичный ключ, не зависит полностью от другого атрибута, не входящего в первичный ключ. Содержимое отношений Персонал и Недвиж_Пров представлено в табл.6.20 и 6.21 соответственно. Таблица 6.20. Отношение Персонал
Сотр_Ном | Сотр_ФИО
СН37 | Иванов А.К
СН14 | Волкова А.Е
Таблица 6.21. Отношение Недвиж_Пров
Н_Ном | Пров_Дата | Пров_Вр | Коммент | Сотр_Ном | Авт_Ном
Н4 | 18.10.01 | 10.00 | Требуется замена ламп | СН37 | 111АВ54
Н4 | 22.04.02 | 9.00 | В хорошем состоянии | СН14 | 232КЕ54
Н4 | 1.10.03 | 12.00 | Неисправ-ность в ванной | СН14 | 445НВ54
Н16 | 22.04.02 | 13.00 | Заменить ковер | СН14 | 232КЕ54
Н16 | 24.10.02 | 14.00 | В хорошем состоянии | СН37 | 445НВ54
Таким образом, исходное отношение Недвижимость_Проверка в процессе нормализации было преобразовано в три ЗНФ-отношения, имеющие следующий вид: Недвижимость (Н_Ном, Н_Адрес) Персонал(Сотр_Ном, Сотр_ФИО) Недвиж_Пров (Н_Ном, Пров_Дата, Пров_Вр, Коммент, Сотр_Ном, Авт_Ном) Нормальная форма Бойса-Кодда (НФБК). Теперь проанализируем отношения Недвижимость, Персонал и Недвиж_Пров для выяснения их принадлежности к нормальной форме Бойса-Кодда. Напомним, что отношение находится в этой форме, если каждый детерминант отношения является потенциальным ключом. Следовательно, для проверки принадлежности к НФБК нужно просто выделить все детерминанты отношения и убедиться в том, что они являются потенциальными ключами.Функциональные зависимости отношений Недвижимость, Персонал и Недвиж_Пров показаны ниже. Отношение Недвижимость ФЗ2 Н_Ном→ Н_АдресОтношение ПерсоналФЗ3 Сотр_Ном→ Сотр_ФИО Отношение Недвиж_Пров ФЗ1 Н_Ном, Пров_Дата→Пров_Вр, Коммент, Сотр_Ном, Авт_Ном ФЗ4 Сотр_Ном, Пров_Дата → Авт_Ном ФЗ5 Авт_Ном, Пров_Дата, Пров_Вр→ Н_Ном, Коммент, Сотр_Ном ФЗ6 Сотр_Ном, Пров_Дата, Пров_Вр→ Н_Ном, Коммент, Авт_Ном Можно заметить, что отношения Недвижимость и Персонал уже находятся в НФБК, поскольку детерминант каждого их этих отношений является потенциальным ключом. Единственным ЗНФ-отношением, которое не находится в НФБК является отношение Недвиж_Пров, так как оно содержит детерминант (Сотр_Ном, Пров_Дата), который не является потенциальным ключом (эта зависимость обозначена как ФЗ4). Вследствие этого отношение Недвиж_Пров может страдать от аномалий обновления. Например, для изменения данных об автомобиле, заказанном для сотрудника с номером 'СН14' на 22 апреля 2002 года, потребуется выполнить соответствующее обновление значений сразу в двух строках. Но если номер автомобиля будет изменен только в одной строке, то это приведет базу данных в противоречивое состояние. Для преобразования отношения Недвиж_Пров в НФБК необходимо устранить зависимость, которая нарушает требования НФБК, что достигается путем создания двух новых отношений Сотр_Авто и Проверка, содержимое которых представлено в таблицах 6.22 и 6.23 соответственно. Новые отношения имеют следующий вид: Сотр_Авто(Сотр_Ном, Пров_Дата, Авт_Ном) Проверка(Н_Ном, Пров_Дата, Пров_Вр, Коммент, Сотр_Ном) Таблица 6.22. Отношение Сотр_Авто
Сотр_Ном | Пров_Дата | Авт_Ном
СН14 | 22.04.02 | 232КЕ54
СН14 | 1.10.03 | 445НВ54
СН37 | 18.10.01 | 111АВ54
СН37 | 24.10.02 | 445НВ54
Таблица 6.23. Отношение Проверка
Н_Ном | Пров_Дата | Пров_Вр | Коммент | Сотр_Ном
Н4 | 18.10.01 | 10.00 | Требуется замена ламп | СН37
Н4 | 22.04.02 | 9.00 | В хорошем состоянии | СН14
Н4 | 1.10.03 | 12.00 | Неисправ-ность в ванной | СН14
Н16 | 22.04.02 | 13.00 | Заменить ковер | СН14
Н16 | 24.10.02 | 14.00 | В хорошем состоянии | СН37
Отношения Сотр_Авто и Проверка находятся в НФБК, поскольку детерминант каждого их этих отношений является потенциальным ключом. В итоге декомпозиция показанного в табл. 6.17 исходного отношения Недвижимость_Проверка (вплоть до НФБК-отношений) будет иметь схематический вид, показанный на рис. 6.12. В результате выполнения всех этапов нормализации, декомпозиция исходного отношения Недвижимость_Проверка с образованием НФБК-отношений приводит к утрате функциональной зависимости Авт_Ном, Пров_Дата, Пров_Вр→Н_Ном, Коммент, Сотр_Ном (эта зависимость обозначена как ФЗ5). Однако следует признать, что если не устранить функциональную зависимость Сотр_Ном, Пров_Дата → Авт_Ном (эта зависимость обозначена как ФЗ4), то отношение Недвиж_Пров будет характеризоваться некоторой избыточностью данных. Рис. 6.12. Декомпозиция отношения Недвижимость_Проверка на четыре НФБК-отношения Полученные в результате выполненной нормализации отношения будут иметь следующий вид: Отношение Недвижимость_Проверка, представленное в табл. 6.17, может быть восстановлено в исходном состоянии посредством соединения отношений Недвижимость, Персонал, Проверка и Сотр_Авто с помощью механизма первичных/внешних ключей. Например, атрибут Сотр_Ном является первичным ключом отношения Персонал, а в отношении Проверка он играет роль внешнего ключа. Наличие этого внешнего ключа позволяет связать отношения Персонал и Проверка с целью определения имени сотрудника, выполняющего проверку состояния данного объекта недвижимости. Вопросы для самопроверки 1. Чем опасны аномалии обновления, вставки и удаления? 2. Чем функциональная зависимость отличается от полной функциональной зависимости? 3. Что такое детерминант функциональной зависимости? 4. Чем первая нормальная форма отличается от второй нормальной формы? 5. В чем отличие третьей нормальной формы от второй нормальной формы? 6. Какая нормальная форма, из описанных в лекциях, является наиболее строгой? 7. Что такое транзитивная зависимость? 8. Дайте определение нормальной формы Бойса-Кодда. 9. В чем заключается цель проведения нормализации? 10. Исключается ли избыточность данных в результате правильно проведенной нормализации? Литература 1. Дейт К.Дж Введение в системы баз данных Пер. с англ. 6-е изд. К.: Диалектика, 1998 2. Дрибас В.П Реляционные модели баз данных Минск: БГУ, БССР, 1982 3. Йордан Э., Аргила К Структурные модели в объектно-ориентированном анализе и проектировании Пер. с англ. М.: ЛОРИ, 1999 4. Коберн А Современные методы описания функциональных требований к системам Пер. с англ. М.: ЛОРИ, 2002 5. Коннолли Т., Бегг К Базы данных. Проектирование, реализация и сопровождение. Теория и практика М.: Издательский дом "Вильямс", 2003, 1440 с 6. Корнеев В.В., Гараев А.Ф., Васютин С.И., Райх В.В Базы данных. Интеллектуальная обработка информации М.: 7. Туманов В.Е., Гайфуллин Б.Н., Сгибнев В.Я Введение в SQL для баз данных в архитектуре "клиент-сервер" М.: Интерфейс Пресс, 2000 8. Харрингтон Д.Л Проектирование реляционных баз данных. Просто и доступно М.: ЛОРИ, 2000 9. Четвериков В.Н., Ревунков Г.И., Самохвалов Э.Н Банки и базы данных. Учебник для вузов М.: Высшая школа, 1987

назад | содержание