НОРМАЛИЗАЦИЯ ОТНОШЕНИЙ Основные понятия и

НОРМАЛИЗАЦИЯ ОТНОШЕНИЙ

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

Зависимости между атрибутами отношений Существуют три вида зависимостей: функциональные, транзитивные и многозначные.

Функциональная зависимость– когда каждому значению атрибута А соответствует в точности только одно значение атрибута B: если каждому значению поля А соответствует только одно значение поля B, то значение поля В функционально зависит от значения поля A. Пример: Табельному номеру сотрудника соответствует только одна фамилия и одна дата рождения.

Частичной функциональной зависимостью называется зависимость неключевого атрибута от части первичного ключа. Пример: Предположим имеется таблица «Заказы» с полями «дата заказа» , «номер заказа» , «наименование поставщика» , «адрес поставщика» , «наименование товара» , «количество» , «цена» , «сумма» . Предположим первичным ключом является набор значений полей (Номер заказа, Наименование поставщика, Наименование товара). В этом случае поле «адрес поставщика» будет зависеть только от части первичного ключа – от значения поля «наименование поставщика» . Следовательно в таблице имеется частичная функциональная зависимость значения поля «адрес» от значения поля «наименование поставщика» .

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

Транзитивная зависимость – если атрибут В зависит от атрибута А, а С зависит от В, то С транзитивно зависит от А. Многозначная зависимость – зависимость многих значений поля B от одного значения поля А. Или, поле B многозначно зависит от поля А, если каждому значению поля А соответствует множество значений поля B. Многозначные зависимости бывают нескольких видов: «один ко многим» (1: М), «многие к одному» (М: 1), «многие ко многим» (М: М).

Выявление зависимостей между атрибутами отношения

1. Составить все возможные пары полей таблицы (порядок следования атрибутов в парах значения не имеет). Для таблицы «Поступление товара» пары будут следующими: 1. (Дата поступления, Номер накладной) 2. (Дата поступления, Поставщик) 3. (Дата поступления, Адрес) 4. (Дата поступления, Товар) 5. (Дата поступления, Ед. изм. ) 6. (Дата поступления, Количество) 7. (Дата поступления, Цена) 8. (Дата поступления, Сумма) 9. (Номер накладной, Поставщик) 10. (Номер накладной, Адрес) 11. (Номер накладной, Товар) 12. (Номер накладной, Ед. изм. ) 13. (Номер накладной, Количество) 14. (Номер накладной, Цена) 15. (Номер накладной, Сумма) 16. (Поставщик, Адрес) 17. (Поставщик, Товар) 18. (Поставщик, Ед. изм. ) 19. (Поставщик, Количество) 20. (Поставщик, Цена) 21. (Поставщик, Сумма)

22. (Адрес, Товар) 23. (Адрес, Ед. изм. ) 24. (Адрес, Количество) 25. (Адрес, Цена) 26. (Адрес, Сумма) 27. (Товар, Ед. изм. ) 28. (Товар, Количество) 29. (Товар, Цена) 30. (Товар, Сумма) 31. (Ед. изм. , Количество) 32. (Ед. изм. , Цена) 33. (Ед. изм. , Сумма) 34. (Количество, Цена) 35. (Количество, Сумма) 36. (Цена, Сумма)

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

Проверим наличие зависимости «номера накладной» от «даты поступления» . Если каждому значению атрибута «дата поступления» соответствует одной и только одно значение атрибута «номер накладной» , то атрибут «номер накладной» функционально зависит от атрибута «дата поступления» . Как видно из таблицы, условие функциональной зависимости «номера накладной» от «даты поступления» не выполняется, т. к. одному значению «даты накладной» (05. 2004) соответствует два значения атрибута «номер накладной» (14 и 15).

Далее необходимо провести такую же проверку для всех пар атрибутов. Условие наличия функциональной зависимости удовлетворяется для следующих пар: 1. (Номер накладной, Дата поступления) - Номер накладной -> Дата поступления 9. (Номер накладной, Поставщик) - Номер накладной - > Поставщик 16. (Поставщик, Адрес) - Поставщик -> Адрес 27. (Товар, Ед. изм. ) - Товар -> Ед. изм. Выявленные функциональные зависимости используются в процессе приведения таблиц БД к третьей нормальной форме.

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

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

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

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

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

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

Пример. Предположим имеется таблица «Поставщики»

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

С другой стороны, комбинации значений полей «Наименование организации» , «Адрес» можно считать уникальным, т. к. вероятность того, что в одной таблице встретятся записи с одинаковыми значениями этих полей ничтожно мала. Аналогичным образом, возможно предположить, что уникальными в пределах таблицы также будут комбинации значений полей: - «Наименование организации» , «Ф. И. О. руководителя» ; - «Наименование организации» , «Телефон» и др. Все эти комбинации и будут являться альтернативными ключами.

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

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

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

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

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

2. В соответствии с определением 2 НФ попытаемся установить, существуют ли в рассматриваемой таблице поля: - не зависящие от ключа; - зависящие от части ключа (от значения одного или нескольких, но не всех полей ключа).

2. 1. Найдем поля, значения которых не зависят от ключа ( «Номер накладной» , «Товар» ). Значения полей «Город» , «Улица» , «Дом» , «Корпус» , не зависят от комбинации значений полей «Номер накладной» , «Товар» . Это означает, что значения этих полей являются избыточными для данной таблицы. Избыточность приводит к неоднократному повтору одной и той же информации в рамках таблицы.

2. 2. Найдем поля, значения которых не зависят от части ключа ( «Номер накладной» , «Товар» ). Значения полей «Дата поступления» , «Поставщик» не зависят от значения поля «Товар» , являющегося частью первичного ключа. Поля «Город» , «Улица» , «Дом» , «Корпус» не рассматриваются т. к. не зависят ни от ключа, ни от его части. Зависимость полей от части ключа также приводит к неоднократному повтору информации в этих полях для разных записей таблицы.

Где: 1 – Ключевые поля; 2 – Поля, не полностью зависящие от ключа 3 – Поля, независящие от ключа; 4 – Поля, полностью зависящие от ключа

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

4. Устранение неполных функциональных зависимостей 4. 1. Выносим из таблицы «Поступление товара» поля, значения которых не зависят от значения ключа, т. е. значений полей «Номер накладной» и «Товар» . Получим таблицу Таблица в таком виде утрачивает смысл, т. к. непонятно к чему относится значения полей город, улица и т. д. Т. к. значения этих полей представляют собой адрес поставщика (функционально полно зависят от значения поля «Поставщик» ), то в данную таблицу необходимо скопировать поле «Поставщик» .

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

Исходная таблица «Поступление товара» , после выноса полей теперь будет выглядеть следующим образом:

4. 2. Выносим из таблицы «Поступление товара» поля, значение которых зависит от части ключа, т. е. либо от поля «Номер накладной» , либо от поля «Товар» . Таких полей в таблице два: «Дата поступления» и «Поставщик» . Поле «Дата поступления» зависит только от значения поля «Номер накладной» , т. к. одному документу накладная соответствует только одно значение поля «Дата поступления» : документ оформляется только на одну определенную дату. Поле «Поставщик» также зависит только от значения поля «Номер накладной» , т. к. одному номеру накладной может соответствует строго один поставщик: накладная, как документ, оформляется только на одного поставщика

В результате выноса полей «Дата поступления» и «Поставщик» из таблицы «Поступление товара» получим следующую таблицу: Таблица в таком виде не имеет никакого реального содержания, т. к. непонятно что именно поставлялось и как это может быть связано с другой информацией. Так в одну и ту же дату может прийти несколько партий товара от одного и того же поставщика. Учитывая, что в предыдущей таблице (таблице «Поступление товара» ) значение этих полей зависело только от значения поля «Номер накладной» , то это поле необходимо скопировать в ту же таблицу.

Полученная таблица фактически содержит информацию о произведенной хозяйственной операции – поступлении товаров на склад. Как видно из таблицы, определяющим полем является поле «Номер накладной» . Следовательно, эту таблицу можно определить как таблицу, содержащую информацию о накладных» . Назовем эту таблицу «Накладные» . Первичным ключом таблицы является поле «Номер накладной» . Полученная таблица «Накладные» уже находится во второй нормальной форме (2 НФ), т. к. все значения полей атомарны (требование 1 НФ) и все неключевые атрибуты функционально полно зависят значения ключа – значения поля «Номер накладной» (требование 2 НФ).

Исходная таблица «Поступление товара» , после выноса полей теперь будет выглядеть следующим образом:

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

Полученные таблицы находятся в 2 НФ за исключением таблицы «Состав накладной» . В этой таблице имеется зависимость между значением поля «товар» и значением поля «Ед. изм» (единица измерения). Как следует из смысла этих полей, единица измерения – это атрибут товара а не операции. Следовательно значение поля «Ед. изм» , будет зависеть только от части ключа – от поля «Товар» , что противоречит условию второй нормальной формы.

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

Исходная таблица «Состав накладной» теперь выглядеть так:

И таблица «Товары» и таблица «Состав накладной» теперь во второй нормальной форме. Единственным сомнительным моментом в этой структуре является то, что во всех записях таблицы «Состав накладной» необходимо будет указывать значение поля «Товар» , которое по сути представляет собой наименование товара. Кроме того, если изменится наименование товара необходимо будет вносить изменения во все записи таблицы «Состав накладной» , где упоминался этот товар.

Итак, 1. Введем в таблицу товары поле «Код товара» . 2. Сделаем поле «Код товара» первичным ключом.

3. Заменим использовавшееся ранее поле «Товар» на поле «Код товара» во всех таблицах БД. В нашем примере это таблица «Состав накладной»

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

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

Третья нормальная форма таблица базы данных (3 НФ) Таблица находится в 3 НФ, если она удовлетворяет требованиям 2 НФ и ни одно из ее неключевых полей функционально не зависит от любого другого неключевого поля. Таким образом, после того, как мы убедились, что таблица находится во второй нормальной форме (т. е. все поля таблицы функционально полно зависят от составного первичного ключа), необходимо перейти к следующей проверке – проверке наличия зависимостей между неключевыми атрибутами. При обнаружении такой зависимости необходимо так разделить исходную таблицу на две и более таблицы, чтобы в полученных таблицах зависимостей между неключевыми атрибутами не было. Рассмотрим процесс приведения к третей нормальной форме на примере полученной ранее таблицы «Состав накладной» . Таблица «Состав накладной» после приведения ко второй нормальной форме выглядит следующим образом:

Рассмотрим неключевые поля таблицы на предмет наличия между ними какой-либо зависимости: - от поля «Количество» зависит значение поля «Сумма» ; - от поля «Цена» зависит значение поля «Сумма» ; - от поля «Сумма» не зависит ни одно другое поле таблицы. Следовательно, имеются две зависимости между неключевыми полями таблицы: Количество → Сумма и Цена → Сумма. Для устранения этой зависимости согласно правилу необходимо разбить таблицу на две и более, однако в данном случае этого не требуется, т. к.

поле «Сумма» является арифметически вычисляемым из значений полей «Количество» и «Цена» : Количество * Цена = Сумма. В связи с этим поле «Сумма» можно вообще исключить из таблицы базы данных как избыточное, с точки зрения хранимой в таблице базы данных информации. Действительно, зачем хранить в базе данных информацию, которая получается путем простейших вычислений из других полей базы данных?

Исключив информационно-избыточное поле «Сумма» получим следующую таблицу: Таблица «Состав накладной» находится в третьей нормальной форме.

Проверив все остальные таблицы (Товары, Накладные, Поставщики) на условие 3 НФ аналогичным образом можно легко убедиться, что они удовлетворяют этому условию и находятся в 3 НФ.