Объединение нескольких атрибутов в одно отношение выполняется не случайным образом. Данные, которые будут храниться в этом отношении, взаимосвязаны между собой. Эта взаимосвязь определяется множеством функциональных зависимостей между атрибутами отношения. Это означает, что значения одного атрибута зависят от значений других атрибутов, т. е. допустимы не любые сочетания значений атрибутов. Зависимости эти вытекают из ограничений предметной области. Например, в отношении Поставки существуют следующие ограничения:

· каждый поставщик имеет только один адрес,

· каждый поставщик поставляет товар по определенной цене,

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

· каждый склад имеет свой объем.

Эти ограничения являются зависимостями, которые можно сформулировать следующим образом:

· адрес функционально зависит от поставщика,

· цена функционально зависит от товара и поставщика,

· номер склада функционально зависит от товара и поставщика,

· объем функционально зависит от номера склада.

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

Пусть отношение r имеет схему R , X и Y – подмножества R . Отношение r удовлетворяет функциональной зависимости X→Y , если π Y (σ X=x (r)) имеет не более чем один кортеж для каждого значения xÎX , т. е. значения атрибутов X однозначно определяют значения атрибутов Y.

Функциональную зависимость будем обозначать следующим образом:

· Поставщик → Адрес,

· {Товар, Поставщик}→ Цена,

· {Товар, Поставщик}→ Склад,

· Склад → Объем.

А читаются они так:

· Поставщик определяет Адрес,

· Товар и Поставщик определяют Цену,

· Товар и Поставщик определяют Склад,

· Склад определяет Объем.

На языке функциональных зависимостей ключ для схемы R – это подмножество KÍR , такое, что K →R , и никакое собственное подмножество K¢ÍK этим свойством не обладает.

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

Сформулируем правила, по которым следует проводить де­компо­­зицию отношения. Этот процесс называется нормализацией, т. е. при­­ведением отношения к нормальной форме.

Нормальные формы представляют собой ограничения на схему отношения, избавляющие ее от нежелательных свойств, которые были перечислены выше. Прежде чем приводить отношения к нор­мальной форме, следует построить все функциональные зависимости между атрибутами, которые существуют в предметной области.

Схема отношения R находится в первой нормальной форме (1НФ ), если значения всех атрибутов являются атомарными (не составными), т. е. значение каждого атрибута не является ни списком, ни множеством значений.

Например, атрибут ФИО является составным, состоит из трех данных: фамилии, имени и отчества.

Чтобы привести схему в 1НФ, нужно все составные атрибуты заменить простыми.

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

Схема отношения R находится во второй нормальной форме (2НФ ), если она находится в первой нормальной форме, и каждый непервичный атрибут функционально полно зависит от первичного ключа.

Что такое неполная функциональная зависимость от ключа? Такая зависимость присутствует в отношении, если какой-либо атрибут, не входящий в ключ, функционально зависит от части атрибутов, входящих в ключ. Любой непервичный атрибут обязательно функционально зависит от всех первичных атрибутов по определению ключа отношения. А если какой-либо непервичный атрибут, кроме того, функционально зависит не от всех, а от части первичных атрибутов, то это и есть неполная функциональная зависимость.

Например, в отношении Поставка первичными атрибутами являются Товар и Поставщик . Атрибут Цена функционально полно зависит от ключа, а атрибут Адрес зависит от части ключа, т. е. только от атрибута Поставщик , это неполная функциональная зависимость. Значит, схема Поставки не находится во 2НФ.

Чтобы привести схему, находящуюся в 1НФ, ко 2НФ, нужно разбить ее на несколько схем:

· выполнить проекцию схемы R на первичные атрибуты и атрибуты, функционально полно зависящие от ключа, т. е. исключить непервичные атрибуты, которые неполно зависят от ключа,

· для каждой неполной функциональной зависимости выполнить проекцию схемы R на атрибуты, входящие в эту зависимость, т. е. оставить часть ключа отношения R и атрибуты, функционально зависящие от этой части.

В примере с отношением Поставки в результате приведения схемы ко 2НФ получатся два отношения:

Поставки_1 (Товар , Поставщик , Цена, Склад, Объем ),

Поставки_2 (Поставщик , Адрес ).

Однако информация об объеме склада продолжает дублироваться. Для устранения этого недостатка схемы существует третья нормальная форма.

Схема отношения R находится в третьей нормальной форме (3НФ ), если она находится во второй нормальной форме и в ней отсутствуют транзитивные зависимости непервичных атрибутов от ключа.

Что такое транзитивные зависимости? Транзитивная зависимость имеет место, если какой-либо непервичный атрибут функционально зависит от другого непервичного атрибута, а тот в свою очередь функционально зависит от ключа.

Схема отношения Поставки_1 (Товар , Поставщик , Цена, Склад, Объем ) не находится в 3НФ, так как в ней присутствует транзитивная зависимость:

{Товар, Поставщик } → Склад , Склад → Объем .

Чтобы привести схему, находящуюся во 2НФ, в 3НФ, нужно:

· выполнить проекцию схемы R на первичные атрибуты и атрибуты, транзитивно не зависящие от ключа, т. е. исключить непервичные атрибуты, которые транзитивно зависят от ключа,

· для каждого транзитивно зависимого непервичного атрибута выполнить проекцию схемы R на атрибуты, входящие во вторую часть транзитивной зависимости, т. е. оставить только непервичные атрибуты отношения R , между которыми имеется функциональная зависимость.

В примере с отношением Поставки_1 в результате приведения схемы к 3НФ получатся два отношения:

Поставки_1_1 (Товар , Поставщик , Цена, Склад ),

Поставки_1_2 (Склад , Объем ).

Таким образом, последовательно выполняя разделение исходной схемы отношения на несколько других схем согласно рассмотренным правилам, получаем схему в 3НФ, свободную от аномалий обновления и дублирования информации, о чем говорилось в начале раздела.

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

В рассмотренном примере в результате декомпозиции вместо одного отношения Поставки мы получили три новых отношения:

Поставки_1_1 (Товар , Поставщик , Цена, Склад ),

Поставки_1_2 (Склад , Объем ),

Поставки_2 (Поставщик , Адрес ).

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

Как вы заметили, схема в 3НФ избавляет базу данных от дублирования информации и аномалий обновления, но не всегда.

Рассмотрим отношение Лекции (Студент , Предмет , Преподаватель ), которое хранит информацию о том, какие предметы изучают студенты и кто ведет эти предметы. Предметная область накладывает следующие ограничения:

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

· каждый преподаватель ведет только один предмет, но каждый предмет может вести несколько преподавателей.

Из этих ограничений вытекают следующие функциональные зависимости:

· {Студент, Предмет} → Преподаватель;

· Преподаватель → Предмет.

Из функциональных зависимостей вытекает, что ключом отношения Лекции будет набор атрибутов {Студент , Предмет }.

Отношение Лекции находится в 3НФ. Но оно страдает аномалиями обновления. Если требуется удалить информацию о том, что Петров изучает Физику, то утратится информация о том, что профессор Серов преподает Физику. В то же время информация о том, что профессор Белый ведет Алгебру, дублируется.

Эти трудности вызваны тем, что существует функциональная зависимость первичного атрибута от непервичного. Эта проблема решается в нормальной форме Бойса–Кодда.

Отношение находится в нормальной форме Бойса–Кодда (НФБК) , если оно находится в 3НФ и в нем отсутствуют зависимости первичных атрибутов от непервичных. Эквивалентное определение требует, чтобы все левые части функциональных зависимостей были потенциальными ключами.

Приведя отношение к НФБК, мы получим два отношения: Лекции_1 (Студент, Преподаватель ) и Лекции_2 (Преподаватель, Предмет ).

Многозначные зависимости

Атрибут X многозначно определяет атрибут Y в R (или Y многозначно зависит от X ), если каждому значению атрибута X соответствует множество (возможно, пустое) значений атрибута Y , никак не связанных с другими атрибутами R . То есть для наличия в отношении многозначной зависимости необходимо иметь как минимум три атрибута.

Многозначная зависимость обозначается двойной стрелкой: X→→Y .

Рассмотрим отношение Преподаватель (Номер , Имя_ребенка , Предмет , Должность ). Предметная область накладывает следующие ограничения:

· каждый преподаватель может иметь несколько детей,

· каждый преподаватель может вести несколько предметов,

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

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

Тогда отношение Преподаватель имеет две многозначные зависимости и одну функциональную:

· Номер→→Имя_ребенка,

· Номер→→Предмет,

· Номер→Должность.

Отношение Преподаватель , во-первых, содержит избыточную информацию – должность преподавателя повторяется несколько раз. Во-вторых, оно не свободно от аномалий обновления: если у преподавателя появляется еще один ребенок, необходимо добавить в отношение не один кортеж, а столько, сколько предметов ведет этот преподаватель. Аналогично, при добавлении еще одного предмета требуется добавить столько кортежей, сколько детей имеет преподаватель. А если преподаватель не имеет детей, то информацию о том, какие предметы он ведет, вообще нельзя занести в отношение.

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

Отношение находится в четвертой нармальной форме (4НФ ), если оно находится в нормальной форме Бойса–Кодда и в нем отсутствуют многозначные зависимости, которые не являются функциональными.

После приведения отношения Преподаватель к 4НФ мы получим три отношения:

Преподаватель_1 (Номер , Должность ),

Преподаватель_2 (Номер , Имя_ребенка ),

Преподаватель_3 (Номер , Предмет ).

Свойства декомпозиции

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

Атрибут В функционально зависит от атрибута А, если одно значение А определяет точно одно значение В.

В том случае, если для данного отношения все его атрибуты функционально зависят от одного атрибута, то этот атрибут – потенциальный простой ключ, если его значения в пределах отношения уникальны. Какой-то из потенциальных ключей назначается ключом отношения.

В отношении иногда можно выделить совокупность нескольких атрибутов, от которой функционально зависят все остальные атрибуты. Если ее значения уникальны в совокупности в пределах отношения, то эта совокупность – сверхключ отношения ,

Если атрибут В функционально зависит от сверхключа, но нет функциональной зависимости от любого подмножества сверхключа, то имеет место полная функциональная зависимость В от сверхключа.

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

Составной ключ отношения выбирается из потенциальных ключей.

Обратите внимание, что термин функциональная зависимость соответствует понятию функции в математике. Если неключевой атрибут зависит от всего составного ключа и не зависит от его частей, то говорят о полной функциональной зависимости атрибута от составного ключа.

Если атрибут А зависит от атрибута В, а В зависит от атрибута С, но обратная зависимость отсутствует, то говорят, что атрибут С зависит от А транзитивно.

Типы связей в реляционных базах

Связываются на самом деле записи разных отношений БД, однако принято говорить о связывании этих отношений. При связывании устанавливаются ссылки кортежей одного отношения на кортежи другого отношения, принадлежащих одной БД.

Всего поддерживается четыре типа связей (ссылок): «один к одному», «много к одному», «один ко многим», «много ко многим».

Связь «один ко многим»

ОтношениеХ связано с отношением У «один ко многим», если каждому кортежу из Х соответствует несколько кортежей из У . При этом указывается, на какое поле х из Х ссылается поле у из У .

Чтобы ссылки установить, в СУБД существует режим проектирования связей. Чтобы со связанной БД корректно работала СУБД, связи должны удовлетворять условиям, которые защищают целостность БД. Ограничения устанавливаются на свойства связываемых полей. В данном случае, в отношении Х (со стороны «один») связующее поле х должно обладать уникальностью значений, а поле у из У не должно содержать значений, отсутствующих в х . Поле х называется первичным ключом , а поле у – внешним ключом . В этой связи отношение Х , в котором размещен первичный ключ, называется главным отношением , а отношениеУ , в котором находится внешний ключ, называется подчиненным отношением .

Пример связей «один ко многим»:

отношение «Заказы» (подчиненная) и отношение «Товары» (главная);

отношение «Заказы» (подчиненная) и отношение «Клиенты» (главная).

В отношении ЗАКАЗЫ внешние ключи для связи с отношениями ТОВАРЫ и КЛИЕНТЫ:Товар_зак и Клиент_зак. В отношенияхТОВАРЫ и КЛИЕНТЫ первичные ключи Товар_код и Клиент_код, на которые внешние ключи ссылаются.

Связь «один к одному»

Если в связи «один ко многим»внешний ключ у содержит только уникальные значения, то это тип связи «один к одному» - каждой записи в У соответствует одна запись в Х и каждой записи в Х соответствует не более одной записи в У . При этом внешний ключ у не является, как х , первичным ключом связи, так как в поле х могут быть значения, которых нет в у . А в поле у таких значений, которых нет в поле х , быть не может. В отношениях Х и У может быть разное число кортежей.

Связь «много к одному»

Определяется как связь «один ко многим», но отношения Х и У в определении меняются местами.

Связь «много ко многим»

Устанавливается между двумя отношениями Х и У, еслив каждом из них расположен первичный ключ связи с третьим отношением С, в котором размещены два внешних ключа связей «один ко многим» между Х иС и«один ко многим» между С иУ. ОтношениеС называют связующим. В отношении С нужно назначать составной ключ (а не простой). В этот составной ключ должны входить внешние ключи двух связей (или большего числа, если таких пар, как Х и У, связываемых черезС, имеется несколько).

Фиксируются два базовых требования (ограничения) для сохранения целостности. Ограничения целостности по сущностям и по ссылкам должны поддерживаться реляционной СУБД.

Первое целостности сущностей . Объекту предметной области (или сущности в модели предметной области) в реляционных БД соответствуют кортежи отношений. Конкретно требование состоит в том, что любой кортеж любого отношения отличим от любого другого кортежа этого же отношения. Другими словами: любое отношение должно обладать ключом. Это требование автоматически удовлетворяется, если в системе не нарушаются базовые свойства отношений.

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

Второе требование называется требованием целостности по ссылкам и является несколько более сложным. Очевидно, что сложные сущности модели предметной области представляются в реляционной БД в виде нескольких кортежей нескольких связанных отношений.

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

При обновлении подчиненного отношения (вставке новых кортежей или модификации значения внешнего ключа в существующих кортежах) СУБД достаточно следить за тем, чтобы не появлялись некорректные значения внешнего ключа (те значения, которых нет в поле первичного ключа главного отношения). При удалении кортежа из главного отношения, если на него ссылается подчиненное отношение, в СУБД есть несколько следующих приемов, каждый из которых поддерживает целостность по ссылкам.

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

2) При удалении кортежа, на который имеются ссылки, во всех ссылающихся кортежах значение внешнего ключа автоматически становится неопределенным.

3) Создается каскадное удаление, состоящее в том, что при удалении кортежа из главного отношения, из подчиненного отношения автоматически удаляются все ссылающиеся кортежи.

В развитых реляционных СУБД можно выбрать способ поддержания целостности по ссылкам для каждой отдельной ситуации. Для принятия решения необходимо анализировать требования конкретной предметной области.

Проектирование реляционных баз данных. Нормализация.

Понятие нормализации

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

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

Процесс проектирования представляет собой процесс нормализации схем отношений , приведение отношений к «нормальным формам», причем каждая следующая нормальная форма обладает свойствами лучшими, чем предыдущая. Реально процесс нормализации осуществляется через декомпозицию отношений, следуя определенным правилам, которые будут рассмотрены ниже. Именно декомпозиция приводит отношение к очередной нормальной форме.

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

Требование первой нормальной формы является общим базовым требованием классической реляционной модели данных. Существенное ограничение первой нормальной формы - атрибуты отношения атомарны, то есть атрибуты не являются сами отношениями, далее не делятся (как атомы).

В теории реляционных баз данных известно теоретически 7 нормальных форм, здесь выделяется следующая последовательность 6 нормальных форм:

· первая нормальная форма (1NF);

· вторая нормальная форма (2NF);

· третья нормальная форма (3NF);

· нормальная форма Бойса-Кодда (BCNF);

· четвертая нормальная форма (4NF);

· пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF).

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

Основные свойства нормальных форм

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

Наиболее важные на практике нормальные формы отношений основываются на фундаментальном в теории реляционных баз данных понятии функциональной зависимости. Это понятие было рассмотрено в лекции №4. Уточним определения, распространяя их на наборы полей.

Метод нормальных форм

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

ФИО	Долж	Оклад	Стаж	Надб	Каф	Предм	Группа	ВидЗан
Иванов И.М.	преп					СУБД		Лабор
Иванов И.М.	Преп					Информ		Лабор
Петров М.И.	Ст.преп					СУБД		Лекция
Петров М.И.	Ст.преп					Графика		Лабор
Сидоров Н.Г.	Преп					Информ		Лекция
Сидоров Н.Г.	Преп					Графика		Лекция
Егоров В.В.	Преп					ПЭВМ		Лекция

Рис. 6.4. Исходное отношение ПРЕПОДАВАТЕЛЬ

Неявная избыточность проявляется в одинаковых окладах у всех преподавателей и в одинаковых надбавках к окладу за одинаковый стаж. Если оклад изменится с 500 руб. до 510руб., то это значение надо изменить у всех преподавателей. Если при этом будет пропущен Сидоров, то база станет противоречивой. Это пример аномалии редактирования отношения с неявной избыточностью.

Исключение избыточности состоит в нормализации отношений.

Метод нормальных форм является классическим методом проектирования реляционных баз данных. Он основан на фундаментальном понятии зависимости между атрибутами отношений.

Атрибут В функционально зависит от атрибута А, если каждому значению А соответствует в точности одно значение В. Математически функциональная зависимость В от А обозначается записью А ® В. Это означает, что во всех кортежах с одинаковым значением атрибута а АТРИБУТ в БУДЕТ ИМЕТЬ ТАКЖЕ ОДНО И ТО ЖЕ ЗНАЧЕНИЕ. Атрибуты А и В могут быть составными – состоять из двух и более атрибутов. В отношении Преподаватель Функциональные зависимости следующие: ФИО ® Каф, ФИО ® Долж, Долж ® Оклад и др.

Функциональная взаимозависимость. Если существует функциональная зависимость вида А ® В и В ® А, то между А и В имеется взаимно однозначное соответствие, или функциональная взаимозависимость. Математически взаимозависимость обозначается как А « В или В « А.

Пример. Атрибут N (серия и номер паспорта) находится в функциональной взаимозависимости с атрибутом ФИО (фамилия, имя и отчество), если предполагается, что ситуация наличия в отношении полного совпадения фамилий, имен и отчеств у двух людей исключена.

Частичной функциональной зависимостью называется зависимость неключевого атрибута от части составного ключа. В отношении Преподаватель ключ является составным и состоит из атрибутов ФИО, Предмет и Группа. Все неключевые атрибуты функционально зависят от ключа с различной степенью зависимости. Например, атрибут Должность находится в функциональной зависимости от атрибута ФИО, являющегося частью ключа, т.е. находится в частичной зависимости от ключа.

Полная функциональная зависимость – зависимость неключевого атрибута от всего составного ключа. Например, атрибут ВидЗан находится в полной функциональной зависимости от составного ключа.

Атрибут С зависит от атрибута А транзитивно (существует транзитивная зависимость ), если для атрибутов А, В, С выполняются условия А ® В и В ® С, но обратная зависимость отсутствует. В примере транзитивной зависимостью связаны атрибуты:

ФИО ® Долж ® Оклад

В отношении R атрибут В многозначно зависит от атрибута А, если каждому значению А соответствует множество значений В, не связанных с другими атрибутами из R. Многозначные зависимости могут быть «один ко многим» (1:М), «многие к одному» (М:1) или «многие ко многим» (М:М), Обозначаемые соответственно: А Þ В, А Ü В и А Û В.

В рассматриваемом примере имеется многозначная зависимость М:М между атрибутами ФИО Û Предмет (один преподаватель может вести несколько предметов и один предмет могут вести несколько преподавателей).

Поскольку зависимость между атрибутами является причиной аномалий, то стараются такие отношения разделить на несколько отношений. В результате образуется совокупность связанных отношений (таблиц) со связями вида 1:1, 1:М, М:1 и М:М. Связи между таблицами отражают зависимости между атрибутами различных отношений.

Взаимно независимые атрибуты. Два или более атрибутов называются взаимно независимыми, если ни один из этих атрибутов не является функционально зависимым от других атрибутов. Математически отсутствие зависимости атрибута А от атрибута В обозначается как А Ø® В. Если имеет место А Ø® В и В Ø® А, то взаимная независимость обозначается А Ø= В.

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

Пример. Пусть задано отношение R со схемой R(А1, А2, А3) вида:

А1	А2	А3

Априори известно, сто существуют функциональные зависимости:

А1®А2 и А2®А3.

Из анализа видно, что в отношении существуют еще зависимости:

А1®А3, А1А2®А3, А1А2А3®А1А2, А1А2®А2А3 и т.п..

В отношении отсутствует функциональная зависимость атрибута А1 от атрибута А2 и от атрибута А3, т.е.

А2 Ø® А1, А3 Ø® А1.

Отсутствие зависимости А1 от А2 объясняется тем, что одному и тому же значению атрибута А2 (21) соответствуют разные значения атрибута А1 (12 и 17).

Все существующие функциональные зависимости в отношении составляют полное множество функциональных зависимостей , которое обозначим F + . Полное множество функциональных зависимостей может быть выведено на основе 8 аксиом вывода: рефлективности, пополнения, транзитивности, расширения, продолжения, псевдотранзитивности, объединения и декомпозиции.

В отношении Преподаватель можно вывести следующие функциональные зависимости:

ФИО ® Оклад

ФИО ® Долж

ФИО ® Стаж

ФИО ® Надб

ФИО ® Каф

Стаж ® Надб

Долж ® Оклад

Оклад ® Долж

ФИО. Предм. Группа ® Оклад

Рис. 6.5. Зависимости между атрибутами.

Предполагается, что один преподаватель в одной группе может проводить один вид занятий (лекции или лабораторные работы). ФИО – уникальны. Имеется зависимость ФИО ® Стаж, а обратное утверждение не верно, т.к. одинаковый стаж имеют несколько преподавателей. Относительно других зависимостей рассуждения аналогичны. Между должностью и окладом устанавливается взаимно однозначная зависимость.

Один преподаватель в одной группе по разным предметам может проводить разные виды занятий. Определение ВидаЗанятий связано с указанием ФИО, Предмета и Группы. Действительно, Петров М.И. в 256-й группе читает лекции и проводит лабораторные занятия, но лекции читает по СУБД, а лабораторные работы по Графике.

Зависимости между атрибутами ФИО, Предмет и Группа не выведены, т.к. они образуют составной ключ и не учитываются в процессе нормализации отношения (таблицы).

Нормальные формы. Процесс проектирования баз данных с использованием нормальных форм является итерационным и состоит в последовательном переводе отношений из первой нормальной формы в нормальные формы более высокого порядка. Каждая следующая форма ограничивает определенный тип функциональных зависимостей, устраняет соответствующие аномалии при выполнении операций над отношениями базы данных и сохраняет свойства предыдущих форм.

Выделяют следующую последовательность нормальных форм:

° Первая нормальная форма (1НФ);

° Вторая нормальная форма (2НФ);

° Третья нормальная форма (3НФ);

° Усиленная третья нормальная форма, или нормальная форма Бойса-Кодда (БКНФ);

° Четвертая нормальная форма (4НФ);

° Пятая нормальная форма (5НФ).

Первая нормальная форма Отношение находится в 1НФ, если все его атрибуты являются простыми (имеют единственное значение). Исходное отношение строится таким образом, чтобы оно было в 1НФ.

Перевод отношения в следующую нормальную форму осуществляется методом «декомпозиции без потерь», т.е. запросы (выборка данных по условию) к исходному отношению и к отношениям, полученным в результате декомпозиции, должны дать одинаковый результат.

Основной операцией метода декомпозиции является операция проекции.

Пример. Пусть в отношении R(A,B,C,D,E,…) имеется функциональная зависимость С ® D. Декомпозиция отношения R на два новых отношения R1(A, B,C,E,…) и R2(C,D) устранит функциональную зависимость атрибутов и переведет отношение R в следующую нормальную форму. Отношение R2 является проекцией отношения R на атрибуты C и D.

Исходное отношение Преподаватель имеет составной ключ ФИО, Предм, Группа и находится в 1НФ. Атрибуты Стаж, Надб, Каф, Долж, Оклад находятся в функциональной зависимости от части составного ключа – атрибута ФИО . Эта частичная зависимость приводит к явной и неявной избыточности данных, что создает проблемы их редактирования. Часть избыточности устраняется при переводе отношения во 2НФ.

Вторая нормальная форма. Отношение находится во 2НФ, если оно находится в 1НФ и каждый неключевой атрибут функционально полно зависит от первичного ключа (составного).

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

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

° Построить проекции на части составного первичного ключа и атрибуты, зависящие от этих частей.

Переведем отношение Преподаватель во 2НФ. В результате получим два отношения R1 и R2.

R1

ФИО	Предм	Группа	ВидЗан
Иванов И.М.	СУБД		Лабор
Иванов И.М.	Информ		Лабор
Петров М.И.	СУБД		Лекция
Петров М.И.	Графика		Лабор
Сидоров Н.Г.	Информ		Лекция
Сидоров Н.Г.	Графика		Лекция
Егоров В.В.	ПЭВМ		Лекция

Рис. 6.6. Отношения базы данных ПРЕПОДАВАТЕЛЬ во 2 НФ

В отношении R1 первичный ключ составной ФИО, Предм, Группа , в отношении R2 ключ – ФИО. В результате исключена явная избыточность данных о преподавателях. В R2 по-прежнему имеет место неявное дублирование данных.

Для дальнейшего совершенствования переведем отношения в 3НФ.

Аннотация: В данной лекции вводится понятие функциональной зависимости. Это понятие является основой математической теории реляционных баз данных.

Информация, данные, информационные системы

Понятие функциональной зависимости в данных

Оставим пока в стороне ответ на вопрос, почему проекты реляционных баз данных бывают плохими, т.е. зачем нужно проектировать реляционную базу данных. Попытаемся сначала ответить на вопросы "В чем заключается проектирование реляционных баз данных ? и "Что лежит в основе процедур ?"

Как известно, основной единицей представления данных в реляционной модели является отношение, которое математически задается списком имен атрибутов, иначе - схемой отношения . На стадии логического проектирования реляционной базы данных проектировщик определяет и выстраивает схемы отношений в рамках некоторой предметной области, а именно - представляет сущности, группирует их атрибуты, выявляет основные связи между сущностями. Так, в самом общем смысле проектирование реляционной базы данных заключается в обоснованном выборе конкретных схем отношений из множества различных альтернативных вариантов схем.

На практике построение логической модели базы данных, независимо от используемой модели данных, выполняется с учетом двух основных требований: исключить избыточность и максимально повысить надежность данных. Эти требования вытекают из требования коллективного использования данных группой пользователей. Формальных средств описания данных, необходимых для проверки правильности заполнения конструкций моделей, явно недостаточно. Выбор сущностей, атрибутов и фиксация взаимосвязей между сущностями зависит от семантики предметной области и выполняется системным аналитиком субъективно в соответствии с его личным пониманием специфики прикладной задачи. Разные люди определяют и представляют данные по-разному.

Поэтому любое априорное знание об ограничениях предметной области, накладываемых на взаимосвязи между данными и значения данных, и знания об их свойствах и взаимоотношениях между ними может сыграть определенную роль в соблюдении указанных выше требований. Формализация таких априорных знаний о свойствах данных предметной области базы данных нашла свое отражение в концепции функциональной зависимости данных, т.е. ограничений на возможные взаимосвязи между данными, которые могут быть текущими значениями схемы отношений .

Кортежи отношений могут представлять экземпляры сущности предметной области или фиксировать их взаимосвязь. Но даже если эти кортежи определены правильно, т.е. отвечают схеме отношения и выбраны из допустимых доменов, не всякий из них может быть текущим значением некоторого отношения. Например, возраст человека редко бывает более 120 лет, или один и тот же пилот не может одновременно выполнять два различных рейса. Такие ограничения семантики домена практически не влияют на выбор той или иной схемы отношений . Они представляют собой ограничения на типы данных.

Априорные ограничения предметной области на взаимосвязь значений отдельных атрибутов оказывают наибольшее влияние на процесс проектирования схем реляционных баз данных . Соответствие по значению определенных атрибутов различных отношений базы данных, т.е. зависимость их значений друг от друга, определяет показатели надежности и корректности сохраняемых данных при их коллективном и согласованном использовании.

Вспомним определение функции как соответствия множества аргументов определенным значениям из множества определения функции и способы задания функций: формула, график и перечисление (таблица). Нетрудно понять, что функциональную зависимость (ФЗ) можно определить на довольно широком классе объектов. Определение функции не накладывает никаких ограничений на множество аргументов и множество значений функции, кроме их существования и наличия соответствия между их элементами. Поскольку ФЗ можно задать таблично, а таблица есть форма представления отношения, то становится очевидной связь между ФЗ и отношением. Отношение может задавать ФЗ. Это утверждение является первой (1) конструктивной идеей, которая положена в основу теории проектирования реляционных баз данных .

Определение 1. Пусть r (A 1 , A 2 , ..., A n) - схема отношения R , a X и Y - подмножества r . Говорят, что Х функционально определяет Y , если каждому значению атрибутов кортежа отношения из Х соответствует не более одного значения атрибутов того же кортежа отношения из Y . Такая ФЗ обозначается как .

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

Пример. Понятие функциональной зависимости Продемонстрируем понятие функциональной зависимости на примере графика полетов аэропорта. ГРАФИК_ПОЛЕТОВ (Пилот, Рейс, Дата_вылета, Время_вылета)

Иванов	100	8.07	10:20
Иванов	102	9.07	13:30
Исаев	90	7.07	6:00
Исаев	100	11.07	10:20
Исаев	103	10.07	19:30
Петров	100	12.07	10:20
Петров	102	11.07	13:30
Фролов	90	8.07	6:00
Фролов	90	12.07	6:00
Фролов	104	14.07	13:30

Известно, что:

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

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

• "Время_вылета" функционально зависим от "Рейс" : "Рейс" -> "Время_{} вылета" ;
• "Рейс" функционально зависим от {"Пилот", "Дата_вылета", "Время_вылета"} : {"Пилот", "Дата_вылета", "Время_вылета"} -> "Рейс" ;
• "Пилот" функционально зависим от {"Рейс", "Дата_вылета"} : {"Рейс", "Дата_вылета"} -> "Пилот" .

Важной задачей при выявлении функциональных зависимостей на атрибутах отношения, которое по определению является множеством, является выяснение, какой из атрибутов выступает как аргумент, а какой - как значение ФЗ. Наиболее подходящими кандидатами в аргументы ФЗ являются возможные ключи , так как кортежи представляют экземпляры сущности , которые идентифицируются значениями атрибутов своего ключа. Нестрого говоря, функциональная зависимость имеет место на отношении, когда значения кортежа на одном множестве атрибутов однозначным образом определяют значения кортежа на другом множестве атрибутов. Это рабочее определение ФЗ не содержит в себе тех формальных элементов, которые позволяют ответить на вопрос "А как проверить наличие ФЗ между атрибутами отношения?" Необходимый для этого формализм дает нам использование реляционных операций . Для получения формального (строгого) определения наличия ФЗ в отношении обратимся к реляционным операциям .

Определение 2. Пусть имеется отношение R со схемой r , X и Y - два подмножества R . ФЗ имеет место на R , если множество имеет не более одного кортежа для каждого значения х . Такая ФЗ называется также F -зависимостью.

Как видно из определения, формальная проверка наличия ФЗ в отношении R состоит в выборе ( селекции ) отношения по значениям возможного ключа и установлении наличия однозначности между его значением и значениями других атрибутов.

Алгоритм, который проверяет, удовлетворяет ли отношение R ФЗ , состоит в сортировке отношения по значениям возможного ключа и установления факта однозначности между его значением и значениями других атрибутов. Этот алгоритм полезен, но он носит вспомогательный характер.

Ясно, что если семантика предметной области базы данных сложна, то проверить кортежи на принадлежность к ФЗ достаточно сложно. Сложно вообще установить наличие самой функциональной зависимости , отвечающей природе рассматриваемых данных. С помощью такого формального метода можно выявить ФЗ, которые не являются реальными и носят случайный характер. Проектировщику реляционных баз данных следует знать о таком методе проверки наличия ФЗ, но при проектировании новой базы данных его применение малоэффективно. Он может быть полезен при реинжиниринге существующей базы данных.

Функциональные зависимости фактически представляют собой утверждения обо всех отношениях предметной области. Эти отношения могут являться значениями схемы r и, в сущности, не могут быть получены формальными методами. Единственный способ установления функциональных зависимостей для схемы отношения r - это исследование семантики атрибутов сущностей предметной области . Являясь высказываниями о сущностях предметной области , они не могут быть доказаны. Это обстоятельство по существу порождает неединственность представления предметной области отношениями реляционной БД.

Здесь уместно высказать гипотезу о том, почему бывают хорошие и плохие проекты баз данных. Во-первых, в силу субъективности подходов к анализу предметной области аналитики могут упустить важные ФЗ. Это может привести к тому, что, работая на множестве заведомо неэквивалентных схем, проектировщик создаст неудовлетворительный проект базы данных. Во-вторых, неединственность представления предметной области отношениями приводит к проблеме выбора из множества альтернатив. При этом схема базы данных, выбранная из набора эквивалентных схем, является правильной, но может организовывать данные для пользователя непривычным образом. В-третьих, можно определить ("накроить") схемы баз данных таким образом, что в результате операций с ФЗ будут потеряны и ФЗ, и сами данные.

ния системы или анализа мнений пользователей о работе системы.

Целью этого этапа является оптимизация функционирования существующей системы путем реорганизации базы данных и/или внесения изменений в программное обеспечение.

7.2. Функциональные зависимости

По словам Хью Дарвена, функциональные зависимости является «если не совсем фундаментальной, то очень близкой к таковой», которые лежат в основе проектирования базы данных.

Понятие функциональной зависимости

По сути, функциональная зависимость является связью типа «многие к одному» между множествами атрибутов внутри данного отношения.

Пусть R - семейство всевозможных отношений с одинаковым заголовкомH R (можно называть

переменной типа отношения, а всякое r P R - значением этой переменной(или допустимым отношением)). Пусть A Ď H R и B Ď H R - некоторые подмножества атрибутов заголовка переменной отношения R .

Определение 1. Множество атрибутовB функционально зависимо отA (и обозначаютA Ñ B ) тогда и только тогда, когда каждое значение атрибутовA любого допустимого отношенияr связано ровно с одним значением атрибутовB отношенияr , т. е. если два кортежа совпадают по значению атрибутовA , то они совпадают и по значению атрибутовB . Формально:

pA ÑB q ô @r HR ; Br P R @T 1 ;T 2 P Br p@a PA T 1 :aT 2 :aq Ñ p@b PB T 1 :bT 2 :bq:

Замечание. Аналогично определяется как частный случай понятие функциональной зависимости и для отдельного обычного отношенияr .

Определение 2. ЕслиA Ñ B , то множество атрибутовA называютдетерминантом , аB -зави-

симой частью.

Заметим, что если A являетсяпотенциальным ключом отношенияr , то из определения потенциального ключа следует, что все атрибуты отношенияr должны обязательно быть функционально зависимыми отA .

циональных зависимостей до некоторых допустимых размеров. Почему эта цель важна? Одна из причин состоит в том, что многие функциональные зависимости являются ограничениями целостности , поэтому желательно, чтобы СУБД обеспечивала их соблюдение. Следовательно, для каждого заданного множества функциональных зависимостей S желательно найти такое множество T , которое(в идеальной ситуации) было бы существенно меньше множества S по мощности и при этом каждая функциональная зависимость в множестве S могла бы быть заменена функциональной зависимостью из множества T . Если бы такое множество T было найдено, то СУБД достаточно было бы контролировать выполнение функциональных зависимостей из множества T , что автоматически обеспечивало бы соблюдение всех функциональных зависимостей из множества S . Именно поэтому задача поиска подходящего множества T представляет большой практический интерес.

Тривиальные и нетривиальные зависимости

Определение 3. Функциональная зависимость называетсятривиальной , если она не может не выполняться, т. е. справедлива при любых условиях.

Определение 3’. Функциональная зависимостьA Ñ B называетсятривиальной тогда и только тогда, когдаB Ď A , иначе она называетсянетривиальной .

Как подразумевается самим их названием, тривиальные зависимости не представляют особого интереса с точки зрения практики; обычно в процессе проектирования намного важнее определить нетривиальные зависимости, поскольку именно они представляют ограничения целостности для отношения. Поэтому очевидным способом сократить множество функциональных зависимостей - это устранение тривиальных зависимостей .

Замыкание множества зависимостей

Из одних функциональных зависимостей могут следовать другие функциональные зависимости. Пусть есть переменная отношения R , аA Ď H R ,B Ď H R иC Ď H R - некоторые подмножества

его атрибутов.

Определение 4. Функциональная зависимостьA Ñ C называетсятранзитивной (илипроходящей через B ), если существуют функциональные зависимостиA Ñ B иB Ñ C .

Определение 5. МножествоS всех функциональных зависимостей, которые следуют из заданного множества функциональных зависимостейS , называетсязамыканием множестваS .

Из приведенного определения следует, что для решения сформулированной задачи (сокращение множества зависимостей) необходимо найти алгоритм вычисления S на основеS .

Первая попытка решить эту проблему была предпринята Армстронгом : он предложил набор правил вывода (называемыеаксиомами Армстронга 1 ) новых функциональных зависимостей на основе заданных.

Пусть A Ď H R ,B Ď H R ,C Ď H R - некоторые подмножества атрибутов переменной отношенияR .

Базовые аксиомы Армстронга:

1. Правило рефлексивности (reflexivity) : если B Ď A , то A Ñ B .

2. Правило дополнения (augmentation) : если A Ñ B , то A Y C Ñ B Y C .

3. Правило транзитивности (transitivity) : если A Ñ B и B Ñ C , то A Ñ C .

Доказательство: Первая аксиома справедлива, т. к.A Ñ B приB Ď A является тривиальной функ-

циональной зависимостью по определению.
Докажем аксиому дополнения от противного. Предположим, что при A		Ñ B неверно
A Y C Ñ B Y C . Это значит, что найдутся два кортежаT 1 P B r иT 2 P B r (B r		Тело некото-
рого допустимого отношения r ) такие, что
T 1 :acT 2 :ac	@ac P A YC ;
но при этом
Dbc P B YC :T 1 :bcT 2 :bc
Так как A Ď A Y C , то по аксиоме рефлексивностиA Y C Ñ A , а значит, из (7.1) следует, что
T 1 :aT 2 :a	@a P A :
Поскольку задано A Ñ B , то из (7.3) следует
T 1 :bT :b	@b P B :
Но тогда из неравенства (7.2) и (7.4) следует, что упоминаемый bc (7.2) принадлежитC , т. е.
Dc P C: T 1 :cT 2 :c
С другой стороны, в силу наличия тривиальной зависимости A Y C Ñ C и (7.1) получаем
T 1 :cT 2 :c @c PC
что противоречит (7.5). Следовательно, исходное предположение было неверным.

1 Но справедливость «аксиом» Армстронга доказывается с помощью определения функциональной зависимости.

Аксиому транзитивности тоже докажем от противного. Предположим, что A Ñ B иB Ñ C , ноA ­Ñ C . ТогдаDr P R: D T 1 ; T 2 P B r такие, что

T 1 :aT 2 :a @a PA

Данная система правил является:

Полной - для заданного множества функциональных зависимостейS минимальный набор функциональных зависимостей, которые подразумевают все зависимости из множестваS , может быть выведен из зависимостей множестваS на основе только этих правил.

Непротиворечивой - с помощью этих правил не могут быть выведены никакие дополнительные функциональные зависимости (т. е. зависимости, которые не обусловлены функциональными зависимостями множестваS ).

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

В целях упрощения нахождения S можно ввестидополнительные правила вывода (D Ď H R ):

4. Правило самоопределения : A Ñ A .

5. Правило декомпозиции : если A Ñ B Y C , то A Ñ B и A Ñ C .

6. Правило объединения : если A Ñ B и A Ñ C , то A Ñ B Y C .

7. Правило композиции : если A Ñ B и C Ñ D , то A Y C Ñ B Y D .

8. Общая теорема объединения (Дарвен): если A Ñ B и C Ñ D , то A Y p C z B q Ñ B Y D .

Замыкание S для заданного множества функциональных зависимостейS можно вычислить тривиальным образом: повторно применять правила вывода до тех пор, пока остается возможным создание новых функциональных зависимостей.

Неприводимые множества зависимостей

Пусть S 1 иS 2 - два множества функциональных зависимостей.

Определение 6. МножествоS 2 называетсяпокрытием для множестваS 1 , если любая функциональная зависимость, которая следует из множества зависимостейS 1 , следует также из множества зависимостейS 2 , т. е.S 1 Ď S 2 .

Замечание. Это означает, что если СУБД обеспечит соблюдение ограничений, представленных зависимостями множестваS 2 , то автоматически будут соблюдены и все ограничения, устанавливаемые зависимостями множестваS 1 .

Определение 7. Множества зависимостейS 1 иS 2 называютсяэквивалентными , еслиS 1 является покрытием дляS 2 иS 2 является покрытием дляS 1 , т. е.S 1 S 2 .

Определение 8. Множество функциональных зависимостейS называетсянеприводимым (минимальным) тогда и только тогда, когда оно обладает всеми тремя свойствами:

1. Зависимая часть каждой функциональной зависимости из S содержит только один атрибут.

2. Детерминант каждой зависимости из S являетсянеприводимым , т. е. ни один атрибут из де-

терминанта не может быть опущен без изменения замыкания S 1 (т. е. без преобразованияS

в неэквивалентное множество зависимостей).

3. Ни одна функциональная зависимость из множества S не может быть удалена без изменения его замыканияS (т. е. без преобразования множестваS в неэквивалентное множество зависимостей).

1 Такая функциональная зависимость называетсянеприводимой слева .

Иначе называется приводимым .

Утверждение. Для любого множества функциональных зависимостей существует по крайней мере одно эквивалентное множество, которое является неприводимым.

Доказательство: Пусть дано исходное множество зависимостейS .

1. В силу правила декомпозиции можно без утраты общности предположить, что каждая функциональная зависимость в этом множестве S имеет одноэлементную зависимую часть.

2. Далее для каждой зависимости f P S следует проверить каждый атрибутa в детерминанте зависимостиf : если удаление атрибутаa из левой части зависимостиf не приводит к изменению замыканияS , то этот атрибут следует удалить.

3. Затем для каждой зависимости f , оставшейся в множествеS , необходимо проверить, приводит ли ее удаление из множестваS к изменению замыканияS : в случае отрицательного ответа следует удалить зависимостьf из множестваS .

Получившееся в результате таких действий множество S 1 является неприводимым и эквивалентным исходному множествуS .l

Определение 9. Множество функциональных зависимостейT , которое неприводимо и эквивалентно другому множеству функциональных зависимостейS , называетсянеприводимым эквивалентом множестваS .

Таким образом, в системе вместо исходного множества функциональных зависимостей S может использоваться его неприводимый эквивалентT . Однако для заданного множества функциональных зависимостей не всегда существуетуникальный неприводимый эквивалент.

Декомпозиция без потерь и функциональные зависимости

Процедура нормализации предусматривает разбиение, или декомпозицию , данной переменной отношения на другие переменные отношения, причем декомпозиция должна быть обратимой, т. е. выполняться без потерь информации. Иначе говоря, интерес представляют только те операции, которые выполняются без потерь информации.

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

операция соединения, что показывается теоремой Хита:

Теорема (Хит; Heath) . Пусть задана переменная отношенияR с заголовкомH R A Y B Y C , гдеA ; B ; C - попарно непересекающиеся множества атрибутов переменной отношенияR . ЕслиR удовлетворяет функциональной зависимостиA Ñ B , то можно провести декомпозицию без потерь в виде

R1 A Y B pRq; R2 A Y C pRq;

которая обратима с помощью естественного соединения: R R 1 " R 2 .

В качестве следствия-обобщения можно (неформально) отметить, что декомпозиция переменной отношенияR на проекцииR l ; R 2 ; : : : ; R n выполняетсябез потерь , еслиR R l " R 2 " : : : " R n .

Диаграммы функциональных зависимостей

Пусть R - переменная отношения, аT -неприводимое множество его функциональных зависимостей. МножествоT можно визуально представить в видедиаграммы функциональных зависимо-

стей:

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

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

Функциональная связь изображается в виде стрелки от домена (всегда является потенциальным ключом) к множеству атрибутов зависимой части.