НОРМАЛИЗАЦИЯ ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА Отношение R находится в первой нормальной форме (1НФ), если значения каждого его атрибута являются атомарными, т.е. - презентация

1

2 НОРМАЛИЗАЦИЯ ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА Отношение R находится в первой нормальной форме (1НФ), если значения каждого его атрибута являются атомарными, т.е. такими значениями, которые не являются множеством значений или повторяющейся группой. В определении реляционной модели Кодда все отношения всегда находятся в 1НФ. Отношение R находится в первой нормальной форме (1НФ), если значения каждого его атрибута являются атомарными, т.е. такими значениями, которые не являются множеством значений или повторяющейся группой. В определении реляционной модели Кодда все отношения всегда находятся в 1НФ. каждый атрибут отношения должен хранить одно-единственное значение и не являться ни списком, ни множеством значений.

3 НОРМАЛИЗАЦИЯ ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА НаименованиеГородАдрес Эл. почта WWWВид Конт. лица Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Иванов И.И., зам. дир., тел (3254) Петров П.П., нач. отд. сбыта, тел (3254) ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С., директор, тел. (7634) ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В., директор, тел (3254) Этот атрибут не является атомарным, поскольку в нем содержатся списки из нескольких лиц. Разделим эти кортежи таким образом, чтобы каждый кортеж содержал данные только об одном лице Наим.ГородАдрес Эл. почта WWWВид Конт. лица Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Иванов И.И., зам. дир., тел (3254) Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Петров П.П., нач. отд. сбыта,тел (3254) ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С., директор, тел. (7634) ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В., директор, тел (3254)

4 НОРМАЛИЗАЦИЯ ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА Наим.ГородАдрес Эл. почта WWWВид Конт. лица Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Иванов И.И., зам. дир., тел (3254) Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Петров П.П., нач. отд. сбыта,тел (3254) ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С., директор, тел. (7634) ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В., директор, тел (3254) Атрибут «Конт. лица» снова может быть назван атомарным лишь с натяжкой, поскольку содержит разнородные данные, хотя и об одном лице

5 НОРМАЛИЗАЦИЯ ПЕРВАЯ НОРМАЛЬНАЯ ФОРМА Наим.ГородАдрес Эл. почта WWWВид Конт. лица Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Иванов И.И., зам. дир., тел (3254) Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Петров П.П., нач. отд. сбыта,тел (3254) ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С., директор, тел. (7634) ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В., директор, тел (3254) Наим.ГородАдрес Эл. почта WWWВидДолжностьФ.И.О. Код города Тел. Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 зам. дир. Иванов И.И Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 нач. отд. сбыта Петров П.П., ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В

6 НОРМАЛИЗАЦИЯНаим.ГородАдрес Эл. почта WWWВидДолжностьФ.И.О. Код города Тел. Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 зам. дир. Иванов И.И Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 нач. отд. сбыта Петров П.П., ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В избыточность

7 НОРМАЛИЗАЦИЯ Наим.ГородАдрес Эл. почта WWWВид Должнос ть Ф.И.О.Тел. Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 зам. дир. Иванов И.И Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 нач. отд. сбыта Петров П.П., ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В Город Код города Владимир3254 Курск7634 Мы избавились от частичной зависимости атрибута «Код-города» от составного ключа, переместив коды городов в отдельное отношение с ключом «Город». Таким образом, теперь мы получили два отношения, каждое из которых находится во 2НФ.

8 НОРМАЛИЗАЦИЯ Наим.ГородАдрес Эл. почта WWWВид Должнос ть Ф.И.О.Тел. Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 зам. дир. Иванов И.И Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 нач. отд. сбыта Петров П.П., ООО «Вымпел» Курск Ул. Гоголя, 25 Сидоров С.С ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Васильев В.В Несмотря на предпринятые усилия, таблица все еще содержит изрядную избыточность – достаточно взглянуть на повторяющиеся значения в столбцах «Адрес», «Эл.почта» и «WWW». Значит, процесс нормализации еще не завершен, и пора переходить к его следующей стадии.

9 НОРМАЛИЗАЦИЯ Наим.ГородАдресWWWВид Поршневой з-д Владимир Ул. 2-я Кольцевая, 17 Поставщи к ООО «Вымпел» Курск Ул. Гоголя, 25 Клиент ИЧП «Альфа» Владимир Ул. Пушкинская, 37, оф. 565 Клиент Наим.Ф.И.О.ДолжностьТел. Эл. почта Поршневой з-д Иванов И.И. зам. дир. Поршневой з-д Петров П.П., нач. отд. сбыта ООО «Вымпел» Сидоров С.С. ИЧП «Альфа» Васильев В.В. Город Код города Владимир3254 Курск7634

10 АНОМАЛИИ Избыточность. Данные практически всех столбцов многократно повторяются. Повторяются и некоторые наборы данных. Потенциальная противоречивость (аномалии обновления). Вследствие избыточности можно обновить группу атрибутов в одной строке, оставляя её неизменной в других. Следовательно, при обновлениях необходимо просматривать всю таблицу для нахождения и изменения всех подходящих строк. Аномалии включения. В БД не может быть записан новый элемент, если его некоторому атрибуту (или нескольким атрибутам) не сопоставлено определённого значения. Можно поместить неопределенные значения в этот атрибут, но если появится нужный элемент, не забудем ли мы удалить строку с неопределенными значениями? Аномалии удаления (исключения). Обратная проблема возникает при необходимости удаления всех значений, связанных с. При таких удаленьях будут утрачены все сведения.

11 НОРМАЛИЗАЦИЯ Каждый факт, хранимый в БД, должен храниться один-единственный раз, поскольку дублирование может привести (и на практике непременно приводит, как только проект приобретает реальную сложность) к несогласованности между копиями одной и той же информации. Следует избегать любых неоднозначностей, а также избыточности хранимой информации.

12 Каждое отношение состоит из: первичного ключа, представляющего уникальный идентификатор некоторого конкретного типа сущностей нуля или более дополнительных полей, представляющих дополнительные свойства типа сущностей, идентифицируемого данным первичным ключом, а не некоторым другим типом сущностей. Каждое отношение состоит из: первичного ключа, представляющего уникальный идентификатор некоторого конкретного типа сущностей нуля или более дополнительных полей, представляющих дополнительные свойства типа сущностей, идентифицируемого данным первичным ключом, а не некоторым другим типом сущностей. НОРМАЛЬНЫЕ ФОРМЫ ОПРЕДЕЛЕНИЯ

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

14 НОРМАЛЬНЫЕ ФОРМЫ ОПРЕДЕЛЕНИЯ Функциональная зависимость В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X (r) R.Y. Функциональная зависимость В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X (r) R.Y.

15 ФУНКЦИОНАЛЬНАЯ ЗАВИСИМОСТЬ ОПРЕДЕЛЕНИЕ Говорят, что атрибут B отношения R функционально зависит от атрибута A того же отношения, если в каждый момент времени t каждому значению атрибута A соответствует не более чем одно значение атрибута B, связанного с A в отношении R.

16 ФУНКЦИОНАЛЬНАЯ ЗАВИСИМОСТЬ ОПРЕДЕЛЕНИЕ Определение Пусть R - отношение. Множество атрибутов Y функционально зависимо от множества атрибутов X (X функционально определяет Y) тогда и только тогда, когда для любого состояния отношения R для любых кортежей r 1,r 2 R из того, что r 1 X=r 2 X следует что r 1 Y=r 2 Y (т.е. во всех кортежах, имеющих одинаковые значения атрибутов X, значения атрибутов Y также совпадают в любом состоянии отношения R). Символически функциональная зависимость записывается X Y. Определение Пусть R - отношение. Множество атрибутов Y функционально зависимо от множества атрибутов X (X функционально определяет Y) тогда и только тогда, когда для любого состояния отношения R для любых кортежей r 1,r 2 R из того, что r 1 X=r 2 X следует что r 1 Y=r 2 Y (т.е. во всех кортежах, имеющих одинаковые значения атрибутов X, значения атрибутов Y также совпадают в любом состоянии отношения R). Символически функциональная зависимость записывается X Y. Множество атрибутов X называется детерминантом функциональной зависимости, а множество атрибутов Y называется зависимой частью.

17 ПРИМЕР ФУНКЦИОНАЛЬНОЙ ЗАВИСИМОСТИ A1B1 A2B2 A3B3 A4B4 A5B5 A1B1 A2B2 A3B2 A4B2 A5B5 A1B1 A2B2 A3B3 A1B4 A5B5 A1B1 A2B2 A3B3 A3B3 A5B5 R1R2R3R4

18 ФУНКЦИОНАЛЬНЫЕ ЗАВИСИМОСТИ ОТНОШЕНИЙ И МАТЕМАТИЧЕСКОЕ ПОНЯТИЕ ФУНКЦИОНАЛЬНОЙ ЗАВИСИМОСТИ Функциональная зависимость атрибутов отношения напоминает понятие функциональной зависимости в математике. Но это не одно и то же. Для сравнения напомним математическое понятие функциональной зависимости: Определение Функциональная зависимость (функция) - это тройка объектов {X,Y,f}, где X- множество (область определения), Y- множество (множество значений), f- правило, согласно которому каждому элементу x X ставится в соответствие один и только один элемент y Y (правило функциональной зависимости). Функциональная зависимость обычно обозначается как f:X Y или y=f(x). Правило f может быть задано любым способом - в виде формулы, при помощи таблицы значений, при помощи графика, текстовым описанием и т.д.

19 ФУНКЦИОНАЛЬНЫЕ ЗАВИСИМОСТИ ОТНОШЕНИЙ И МАТЕМАТИЧЕСКОЕ ПОНЯТИЕ ФУНКЦИОНАЛЬНОЙ ЗАВИСИМОСТИ Функциональная зависимость атрибутов отношения тоже напоминает это определение. Действительно: В качестве области определения выступает домен, на котором определен атрибут X (или декартово произведение доменов, если X является множеством атрибутов). В качестве множества значений выступает домен, на котором определен атрибут Y (или декартово произведение доменов). Правило f реализуется следующим алгоритмом - 1) по данному значению атрибута X найти любой кортеж отношения, содержащий это значение, 2) значение атрибута Y в этом кортеже и будет значением функциональной зависимости, соответствующим данному X. Определение функциональной зависимости в отношении гарантирует, что найденное значение Y не зависит от выбора кортежа, поэтому правило f определено корректно.

20 ФУНКЦИОНАЛЬНЫЕ ЗАВИСИМОСТИ ОТНОШЕНИЙ И МАТЕМАТИЧЕСКОЕ ПОНЯТИЕ ФУНКЦИОНАЛЬНОЙ ЗАВИСИМОСТИ Отличие от математического понятия отношения состоит в том, что, если рассматривать математическое понятие функции, то для фиксированного значения x X соответствующее значение функции y=f(x) всегда одно и то же. Например, если задана функция y=x 2, то для значения x=2 соответствующее значение y всегда будет равно 4. В противоположность этому в отношениях значение зависимого атрибута может принимать различные значения в различных состояниях базы данных.

21 ФУНКЦИОНАЛЬНЫЕ ЗАВИСИМОСТИ ОТНОШЕНИЙ И МАТЕМАТИЧЕСКОЕ ПОНЯТИЕ ФУНКЦИОНАЛЬНОЙ ЗАВИСИМОСТИ Таким образом, понятие функциональной зависимости атрибутов нельзя считать полностью эквивалентным математическому понятию функциональной зависимости, т.к. значение этой зависимости различны при разных состояниях отношения, и, самое главное, эти значения могут меняться непредсказуемо. Функциональная зависимость атрибутов утверждает лишь то, что для каждого конкретного состояния базы данных по значению одного атрибута (детерминанта) можно однозначно определить значение другого атрибута (зависимой части). Но конкретные значение зависимой части могут быть различны в различных состояниях базы данных.

22 НОРМАЛЬНЫЕ ФОРМЫ ОПРЕДЕЛЕНИЯ Взаимно независимые атрибуты Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других. Взаимно независимые атрибуты Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других.

23 ВТОРАЯ НОРМАЛЬНАЯ ФОРМА Отношение R находится во второй нормальной форме (2НФ), если никакие неключевые атрибуты не являются функционально зависимыми лишь от части ключа. Таким образом, 2НФ может оказаться нарушена только в том случае, когда ключ составной.

24 НОРМАЛИЗАЦИЯ ВТОРАЯ НОРМАЛЬНАЯ ФОРМА Каждый неключевой столбец в таблице должен уникально идентифицироваться по первичному ключу

25 НОРМАЛЬНЫЕ ФОРМЫ ОПРЕДЕЛЕНИЯ Полная функциональная зависимость Функциональная зависимость R.X (r) R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X. Полная функциональная зависимость Функциональная зависимость R.X (r) R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X.

26 НОРМАЛЬНЫЕ ФОРМЫ ОПРЕДЕЛЕНИЯ Транзитивная функциональная зависимость Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z --> R.X. (При отсутствии последнего требования мы имели бы "неинтересные" транзитивные зависимости в любом отношении, обладающем несколькими ключами.) Транзитивная функциональная зависимость Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z --> R.X. (При отсутствии последнего требования мы имели бы "неинтересные" транзитивные зависимости в любом отношении, обладающем несколькими ключами.) R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z --> R.X. (При отсутствии последнего требования мы имели бы "неинтересные" транзитивные зависимости в любом отношении, обладающем несколькими ключами.)">

27 ВТОРАЯ НОРМАЛЬНАЯ ФОРМА Рассмотрим следующий пример схемы отношения: СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП; СОТР_НОМЕР -> ОТД_НОМЕР; ОТД_НОМЕР -> СОТР_ЗАРП; СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН Как видно, хотя первичным ключом является составной атрибут СОТР_НОМЕР, ПРО_НОМЕР, атрибуты СОТР_ЗАРП и ОТД_НОМЕР функционально зависят от части первичного ключа, атрибута СОТР_НОМЕР. В результате мы не сможем вставить в отношение СОТРУДНИКИ- ОТДЕЛЫ-ПРОЕКТЫ кортеж, описывающий сотрудника, который еще не выполняет никакого проекта (первичный ключ не может содержать неопределенное значение). При удалении кортежа мы не только разрушаем связь данного сотрудника с данным проектом, но утрачиваем информацию о том, что он работает в некотором отделе. При переводе сотрудника в другой отдел мы будем вынуждены модифицировать все кортежи, описывающие этого сотрудника, или получим несогласованный результат. Такие неприятные явления называются аномалиями схемы отношения. Они устраняются путем нормализации. Вторая нормальная форма Вторая нормальная форма (предполагается, что единственным ключом отношения является первичный ключ) Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда находится в 1NF, и каждый неключевой атрибут полностью зависит от первичного ключа. Можно произвести следующую декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ в два отношения СОТРУДНИКИ-ОТДЕЛЫ и СОТРУДНИКИ-ПРОЕКТЫ: СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП; СОТР_НОМЕР -> ОТД_НОМЕР; ОТД_НОМЕР -> СОТР_ЗАРП СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН Каждое из этих двух отношений находится в 2NF, и в них устранены отмеченные выше аномалии (легко проверить, что все указанные операции выполняются без проблем). Если допустить наличие нескольких ключей, то определение 6 примет следующий вид: Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда оно находится в 1NF, и каждый неключевой атрибут полностью зависит от каждого ключа R. Здесь и далее мы не будем приводить примеры для отношений с несколькими ключами. Они слишком громоздки и относятся к ситуациям, редко встречающимся на практике. Рассмотрим следующий пример схемы отношения: СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП; СОТР_НОМЕР -> ОТД_НОМЕР; ОТД_НОМЕР -> СОТР_ЗАРП; СОТР_НОМЕР, ПРО_НОМЕР -> СОТР_ЗАДАН Как видно, хотя первичным ключом является составной атрибут СОТР_НОМЕР, ПРО_НОМЕР, атрибуты СОТР_ЗАРП и ОТД_НОМЕР функционально зависят от части первичного ключа, атрибута СОТР_НОМЕР. В результате мы не сможем вставить в отношение СОТРУДНИКИ- ОТДЕЛЫ-ПРОЕКТЫ кортеж, описывающий сотрудника, который еще не выполняет никакого проекта (первичный ключ не может содержать неопределенное значение). При удалении кортежа мы не только разрушаем связь данного сотрудника с данным проектом, но утрачиваем информацию о том, что он работает в некотором отделе. При переводе сотрудника в другой отдел мы будем вынуждены модифицировать все кортежи, описывающие этого сотрудника, или получим несогласованный результат. Такие неприятные явления называются аномалиями схемы отношения. Они устраняются путем нормализации. Вторая нормальная форма Вторая нормальная форма (предполагается, что единственным ключом отношения является первичный ключ) Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда находится в 1NF, и каждый неключевой атрибут полностью зависит от первичного ключа. Можно произвести следующую декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ в два отношения СОТРУДНИКИ-ОТДЕЛЫ и СОТРУДНИКИ-ПРОЕКТЫ: СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, СОТР_ЗАРП, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> СОТР_ЗАРП; СОТР_НОМЕР -> ОТД_НОМЕР; ОТД_НОМЕР -> СОТР_ЗАРП СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Первичный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН Каждое из этих двух отношений находится в 2NF, и в них устранены отмеченные выше аномалии (легко проверить, что все указанные операции выполняются без проблем). Если допустить наличие нескольких ключей, то определение 6 примет следующий вид: Отношение R находится во второй нормальной форме (2NF) в том и только в том случае, когда оно находится в 1NF, и каждый неключевой атрибут полностью зависит от каждого ключа R. Здесь и далее мы не будем приводить примеры для отношений с несколькими ключами. Они слишком громоздки и относятся к ситуациям, редко встречающимся на практике.

28 ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА Рассмотрим еще раз отношение СОТРУДНИКИ-ОТДЕЛЫ, находящееся в 2NF. Заметим, что функциональная зависимость СОТР_НОМЕР -> СОТР_ЗАРП является транзитивной; она является следствием функциональных зависимостей СОТР_НОМЕР -> ОТД_НОМЕР и ОТД_НОМЕР -> СОТР_ЗАРП. Другими словами, заработная плата сотрудника на самом деле является характеристикой не сотрудника, а отдела, в котором он работает (это не очень естественное предположение, но достаточное для примера). В результате мы не сможем занести в базу данных информацию, характеризующую заработную плату отдела, до тех пор, пока в этом отделе не появится хотя бы один сотрудник (первичный ключ не может содержать неопределенное значение). При удалении кортежа, описывающего последнего сотрудника данного отдела, мы лишимся информации о заработной плате отдела. Чтобы согласованным образом изменить заработную плату отдела, мы будем вынуждены предварительно найти все кортежи, описывающие сотрудников этого отдела. Т.е. в отношении СОТРУДИКИ-ОТДЕЛЫ по-прежнему существуют аномалии. Их можно устранить путем дальнейшей нормализации. Третья нормальная форма Третья нормальная форма. (определение дается в предположении существования единственного ключа.) Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 2NF и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Можно произвести декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ в два отношения СОТРУДНИКИ и ОТДЕЛЫ: СОТРУДНИКИ (СОТР_НОМЕР, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> ОТД_НОМЕР ОТДЕЛЫ (ОТД_НОМЕР, СОТР_ЗАРП) Первичный ключ: ОТД_НОМЕР Функциональные зависимости: ОТД_НОМЕР -> СОТР_ЗАРП Каждое из этих двух отношений находится в 3NF и свободно от отмеченных аномалий. Если отказаться от того ограничения, что отношение обладает единственным ключом, то определение 3NF примет следующую форму: Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 1NF, и каждый неключевой атрибут не является транзитивно зависимым от какого-либо ключа R. На практике третья нормальная форма схем отношений достаточна в большинстве случаев, и приведением к третьей нормальной форме процесс проектирования реляционной базы данных обычно заканчивается. Однако иногда полезно продолжить процесс нормализации. Рассмотрим еще раз отношение СОТРУДНИКИ-ОТДЕЛЫ, находящееся в 2NF. Заметим, что функциональная зависимость СОТР_НОМЕР -> СОТР_ЗАРП является транзитивной; она является следствием функциональных зависимостей СОТР_НОМЕР -> ОТД_НОМЕР и ОТД_НОМЕР -> СОТР_ЗАРП. Другими словами, заработная плата сотрудника на самом деле является характеристикой не сотрудника, а отдела, в котором он работает (это не очень естественное предположение, но достаточное для примера). В результате мы не сможем занести в базу данных информацию, характеризующую заработную плату отдела, до тех пор, пока в этом отделе не появится хотя бы один сотрудник (первичный ключ не может содержать неопределенное значение). При удалении кортежа, описывающего последнего сотрудника данного отдела, мы лишимся информации о заработной плате отдела. Чтобы согласованным образом изменить заработную плату отдела, мы будем вынуждены предварительно найти все кортежи, описывающие сотрудников этого отдела. Т.е. в отношении СОТРУДИКИ-ОТДЕЛЫ по-прежнему существуют аномалии. Их можно устранить путем дальнейшей нормализации. Третья нормальная форма Третья нормальная форма. (определение дается в предположении существования единственного ключа.) Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 2NF и каждый неключевой атрибут нетранзитивно зависит от первичного ключа. Можно произвести декомпозицию отношения СОТРУДНИКИ-ОТДЕЛЫ в два отношения СОТРУДНИКИ и ОТДЕЛЫ: СОТРУДНИКИ (СОТР_НОМЕР, ОТД_НОМЕР) Первичный ключ: СОТР_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> ОТД_НОМЕР ОТДЕЛЫ (ОТД_НОМЕР, СОТР_ЗАРП) Первичный ключ: ОТД_НОМЕР Функциональные зависимости: ОТД_НОМЕР -> СОТР_ЗАРП Каждое из этих двух отношений находится в 3NF и свободно от отмеченных аномалий. Если отказаться от того ограничения, что отношение обладает единственным ключом, то определение 3NF примет следующую форму: Отношение R находится в третьей нормальной форме (3NF) в том и только в том случае, если находится в 1NF, и каждый неключевой атрибут не является транзитивно зависимым от какого-либо ключа R. На практике третья нормальная форма схем отношений достаточна в большинстве случаев, и приведением к третьей нормальной форме процесс проектирования реляционной базы данных обычно заканчивается. Однако иногда полезно продолжить процесс нормализации.

29 НОРМАЛИЗАЦИЯ ТРЕТЬЯ НОРМАЛЬНАЯ ФОРМА Кроме удовлетворения требованиям второй нормальной формы, каждый неключевой столбец таблицы должен быть независимым от других неключевых столбцов

30 НОРМАЛЬНАЯ ФОРМА БОЙСА-КОДДА Рассмотрим следующий пример схемы отношения: СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ИМЯ, ПРО_НОМЕР, СОТР_ЗАДАН) Возможные ключи: СОТР_НОМЕР, ПРО_НОМЕР; СОТР_ИМЯ, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> CОТР_ИМЯ; СОТР_НОМЕР -> ПРО_НОМЕР; СОТР_ИМЯ -> CОТР_НОМЕР; СОТР_ИМЯ -> ПРО_НОМЕР; СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН; СОТР_ИМЯ, ПРО_НОМЕР -> CОТР_ЗАДАН В этом примере мы предполагаем, что личность сотрудника полностью определяется как его номером, так и именем (это снова не очень жизненное предположение, но достаточное для примера). В соответствии с определением отношение СОТРУДНИКИ-ПРОЕКТЫ находится в 3NF. Однако тот факт, что имеются функциональные зависимости атрибутов отношения от атрибута, являющегося частью первичного ключа, приводит к аномалиям. Например, для того, чтобы изменить имя сотрудника с данным номером согласованным образом, нам потребуется модифицировать все кортежи, включающие его номер.Детерминант Детерминант - любой атрибут, от которого полностью функционально зависит некоторый другой атрибут. Нормальная форма Бойса-Кодда Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом. Очевидно, что это требование не выполнено для отношения СОТРУДНИКИ-ПРОЕКТЫ. Можно произвести его декомпозицию к отношениям СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ: СОТРУДНИКИ (СОТР_НОМЕР, СОТР_ИМЯ) Возможные ключи: СОТР_НОМЕР; СОТР_ИМЯ Функциональные зависимости: СОТР_НОМЕР -> CОТР_ИМЯ; СОТР_ИМЯ -> СОТР_НОМЕР; СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Возможный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН Возможна альтернативная декомпозиция, если выбрать за основу СОТР_ИМЯ. В обоих случаях получаемые отношения СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ находятся в BCNF, и им не свойственны отмеченные аномалии. Рассмотрим следующий пример схемы отношения: СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, СОТР_ИМЯ, ПРО_НОМЕР, СОТР_ЗАДАН) Возможные ключи: СОТР_НОМЕР, ПРО_НОМЕР; СОТР_ИМЯ, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР -> CОТР_ИМЯ; СОТР_НОМЕР -> ПРО_НОМЕР; СОТР_ИМЯ -> CОТР_НОМЕР; СОТР_ИМЯ -> ПРО_НОМЕР; СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН; СОТР_ИМЯ, ПРО_НОМЕР -> CОТР_ЗАДАН В этом примере мы предполагаем, что личность сотрудника полностью определяется как его номером, так и именем (это снова не очень жизненное предположение, но достаточное для примера). В соответствии с определением отношение СОТРУДНИКИ-ПРОЕКТЫ находится в 3NF. Однако тот факт, что имеются функциональные зависимости атрибутов отношения от атрибута, являющегося частью первичного ключа, приводит к аномалиям. Например, для того, чтобы изменить имя сотрудника с данным номером согласованным образом, нам потребуется модифицировать все кортежи, включающие его номер.Детерминант Детерминант - любой атрибут, от которого полностью функционально зависит некоторый другой атрибут. Нормальная форма Бойса-Кодда Отношение R находится в нормальной форме Бойса-Кодда (BCNF) в том и только в том случае, если каждый детерминант является возможным ключом. Очевидно, что это требование не выполнено для отношения СОТРУДНИКИ-ПРОЕКТЫ. Можно произвести его декомпозицию к отношениям СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ: СОТРУДНИКИ (СОТР_НОМЕР, СОТР_ИМЯ) Возможные ключи: СОТР_НОМЕР; СОТР_ИМЯ Функциональные зависимости: СОТР_НОМЕР -> CОТР_ИМЯ; СОТР_ИМЯ -> СОТР_НОМЕР; СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР, СОТР_ЗАДАН) Возможный ключ: СОТР_НОМЕР, ПРО_НОМЕР Функциональные зависимости: СОТР_НОМЕР, ПРО_НОМЕР -> CОТР_ЗАДАН Возможна альтернативная декомпозиция, если выбрать за основу СОТР_ИМЯ. В обоих случаях получаемые отношения СОТРУДНИКИ и СОТРУДНИКИ-ПРОЕКТЫ находятся в BCNF, и им не свойственны отмеченные аномалии.

31 НОРМАЛИЗАЦИЯ НОРМАЛЬНАЯ ФОРМА Бойса-Кодда Вариант третьей нормальной формы, в котором требуется, чтобы каждый столбец, от которого зависит другой столбец, сам должен быть уникальным ключом.

32 ЧЕТВЁРТАЯ НОРМАЛЬНАЯ ФОРМА Рассмотрим пример следующей схемы отношения: ПРОЕКТЫ (ПРО_НОМЕР,ПРО_СОТР, ПРО_ЗАДАН) Отношение ПРОЕКТЫ содержит номера проектов, для каждого проекта список сотрудников, которые могут выполнять проект, и список заданий, предусматриваемых проектом. Сотрудники могут участвовать в нескольких проектах, и разные проекты могут включать одинаковые задания. Каждый кортеж отношения связывает некоторый проект с сотрудником, участвующим в этом проекте, и заданием, который сотрудник выполняет в рамках данного проекта (мы предполагаем, что любой сотрудник, участвующий в проекте, выполняет все задания, предусмотренные этим проектом). По причине сформулированных выше условий единственным возможным ключом отношения является составной атрибут ПРО_НОМЕР, ПРО_СОТР, ПРО_ЗАДАН, и нет никаких других детерминантов. Следовательно, отношение ПРОЕКТЫ находится в BCNF. Но при этом оно обладает недостатками: если, например, некоторый сотрудник присоединяется к данному проекту, необходимо вставить в отношение ПРОЕКТЫ столько кортежей, сколько заданий в нем предусмотрено. Многозначные зависимости В отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, если множество значений B, соответствующее паре значений A и C, зависит только от A и не зависит от С. В отношении ПРОЕКТЫ существуют следующие две многозначные зависимости: ПРО_НОМЕР -> -> ПРО_СОТР; ПРО_НОМЕР -> -> ПРО_ЗАДАН Легко показать, что в общем случае в отношении R (A, B, C) существует многозначная зависимость R.A -> -> R.B в том и только в том случае, когда существует многозначная зависимость R.A -> -> R.C. Дальнейшая нормализация отношений, подобных отношению ПРОЕКТЫ, основывается на следующей теореме: Теорема Фейджина Отношение R (A, B, C) можно спроецировать без потерь в отношения R1 (A, B) и R2 (A, C) в том и только в том случае, когда существует MVD A -> -> B | C. Под проецированием без потерь понимается такой способ декомпозиции отношения, при котором исходное отношение полностью и без избыточности восстанавливается путем естественного соединения полученных отношений. Четвертая нормальная форма Отношение R находится в четвертой нормальной форме (4NF) в том и только в том случае, если в случае существования многозначной зависимости A -> -> B все остальные атрибуты R функционально зависят от A. В нашем примере можно произвести декомпозицию отношения ПРОЕКТЫ в два отношения ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-ЗАДАНИЯ: ПРОЕКТЫ-СОТРУДНИКИ (ПРО_НОМЕР, ПРО_СОТР) ПРОЕКТЫ-ЗАДАНИЯ (ПРО_НОМЕР, ПРО_ЗАДАН) Оба эти отношения находятся в 4NF и свободны от отмеченных аномалий.

33 НОРМАЛИЗАЦИЯ ЧЕТВЁРТАЯ НОРМАЛЬНАЯ ФОРМА Четвёртая нормальная форма запрещает существование многозначных зависимостей между столбцами. Если столбец вместо того, чтобы уникально идентифицировать другой столбец, ограничивает его значения некоторым предопределённым множеством значений – это означает, что между ними существует многозначная зависимость.

34 Во всех рассмотренных до этого момента нормализациях производилась декомпозиция одного отношения в два. Иногда это сделать не удается, но возможна декомпозиция в большее число отношений, каждое из которых обладает лучшими свойствами. Рассмотрим, например, отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, ОТД_НОМЕР, ПРО_НОМЕР) Предположим, что один и тот же сотрудник может работать в нескольких отделах и работать в каждом отделе над несколькими проектами. Первичным ключем этого отношения является полная совокупность его атрибутов, отсутствуют функциональные и многозначные зависимости. Поэтому отношение находится в 4NF. Однако в нем могут существовать аномалии, которые можно устранить путем декомпозиции в три отношения. Зависимость соединения Отношение R (X, Y,..., Z) удовлетворяет зависимости соединения * (X, Y,..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y,..., Z. Пятая нормальная форма Отношение R находится в пятой нормальной форме (нормальной форме проекции-соединения - PJ/NF) в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R. Введем следующие имена составных атрибутов: СО = {СОТР_НОМЕР, ОТД_НОМЕР} СП = {СОТР_НОМЕР, ПРО_НОМЕР} ОП = {ОТД_НОМЕР, ПРО_НОМЕР} Предположим, что в отношении СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения: * (СО, СП, ОП) На примерах легко показать, что при вставках и удалениях кортежей могут возникнуть проблемы. Их можно устранить путем декомпозиции исходного отношения в три новых отношения: СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, ОТД_НОМЕР) СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР) ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМЕР, ПРО_НОМЕР) Пятая нормальная форма - это последняя нормальная форма, которую можно получить путем декомпозиции. Пятая нормальная форма - это последняя нормальная форма, которую можно получить путем декомпозиции. Ее условия достаточно нетривиальны, и на практике 5NF не используется. Заметим, что зависимость соединения является обобщением как многозначной зависимости, так и функциональной зависимости. Во всех рассмотренных до этого момента нормализациях производилась декомпозиция одного отношения в два. Иногда это сделать не удается, но возможна декомпозиция в большее число отношений, каждое из которых обладает лучшими свойствами. Рассмотрим, например, отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ (СОТР_НОМЕР, ОТД_НОМЕР, ПРО_НОМЕР) Предположим, что один и тот же сотрудник может работать в нескольких отделах и работать в каждом отделе над несколькими проектами. Первичным ключем этого отношения является полная совокупность его атрибутов, отсутствуют функциональные и многозначные зависимости. Поэтому отношение находится в 4NF. Однако в нем могут существовать аномалии, которые можно устранить путем декомпозиции в три отношения. Зависимость соединения Отношение R (X, Y,..., Z) удовлетворяет зависимости соединения * (X, Y,..., Z) в том и только в том случае, когда R восстанавливается без потерь путем соединения своих проекций на X, Y,..., Z. Пятая нормальная форма Отношение R находится в пятой нормальной форме (нормальной форме проекции-соединения - PJ/NF) в том и только в том случае, когда любая зависимость соединения в R следует из существования некоторого возможного ключа в R. Введем следующие имена составных атрибутов: СО = {СОТР_НОМЕР, ОТД_НОМЕР} СП = {СОТР_НОМЕР, ПРО_НОМЕР} ОП = {ОТД_НОМЕР, ПРО_НОМЕР} Предположим, что в отношении СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ существует зависимость соединения: * (СО, СП, ОП) На примерах легко показать, что при вставках и удалениях кортежей могут возникнуть проблемы. Их можно устранить путем декомпозиции исходного отношения в три новых отношения: СОТРУДНИКИ-ОТДЕЛЫ (СОТР_НОМЕР, ОТД_НОМЕР) СОТРУДНИКИ-ПРОЕКТЫ (СОТР_НОМЕР, ПРО_НОМЕР) ОТДЕЛЫ-ПРОЕКТЫ (ОТД_НОМЕР, ПРО_НОМЕР) Пятая нормальная форма - это последняя нормальная форма, которую можно получить путем декомпозиции. Пятая нормальная форма - это последняя нормальная форма, которую можно получить путем декомпозиции. Ее условия достаточно нетривиальны, и на практике 5NF не используется. Заметим, что зависимость соединения является обобщением как многозначной зависимости, так и функциональной зависимости. ПЯТАЯ НОРМАЛЬНАЯ ФОРМА

35 НОРМАЛИЗАЦИЯ ПЯТАЯ НОРМАЛЬНАЯ ФОРМА Пятая нормальная форма предусматривает, что если таблица имеет три или более альтернативных (возможных) ключей и можно провести их декомпозицию без потери данных, эти ключи должны быть разбиты на отдельные таблицы.

36 Первая нормальная форма (обычно обозначается также 1НФ).Первая нормальная форма (обычно обозначается также 1НФ). Вторая нормальная форма.Вторая нормальная форма. Третья нормальная форма.Третья нормальная форма. нормальная форма Бойса-Кодда (НФБК).нормальная форма Бойса-Кодда (НФБК). Четвёртая нормальная форма.Четвёртая нормальная форма. Пятая нормальная форма, или нормальная форма проекции- соединения (5NF или PJ/NF).Пятая нормальная форма, или нормальная форма проекции- соединения (5NF или PJ/NF). Процесс нормализации это последовательное преобразование исходной БД к НФ, при этом каждая следующая НФ обязательно включает в себя предыдущую (что, собственно, и позволяет разбить процесс на этапы и производить его однократно, не возвращаясь к предыдущим этапам). Всего в реляционной теории насчитывается 6 НФ: На практике, как правило, ограничиваются 3НФ, ее оказывается вполне достаточно для создания надежной схемы БД. НФ более высокого порядка представляют скорее академический интерес из-за их сложности. Более того, при реализации абстрактной схемы БД в виде реальной базы иногда разработчики вынуждены сделать шаг назад – провести денормализацию с целью повышения эффективности, т.к. идеальная, с точки зрения теории, структура может оказаться слишком накладной на практике.

37 ПРОЦЕСС НОРМАЛИЗАЦИИ Ненормализованная схема Первая нормальная форма Вторая нормальная форма Третья нормальная форма Один экземпляр объема имеет единственное значение. При необходимости расщепить схемы, чтобы не было повторяющихся групп. Объявить первичный ключ Все не ключевые атрибуты полностью функционально зависимы от ключа при необходимости, расщепить схему Все не ключевые атрибуты являются взаимно зависимыми. При необходимости расщепить схему