Шаг 107 - Oracle - Реляционная модель - НОРМАЛИЗАЦИЯ

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

Звучит примерно так: Один столбец или множество столбцов Y функционально зависят от одного или множества столбцов X, если данное множество значений для X определяет единственное множество значений для Y. Утверждение "Y" функционально зависит от "X" равносильно утверждению "X" определяет "Y", которое записано в форме X->Y. Что, поделать, математика и есть математика! Отсюда исходит определение, что основная цель нормализации - избавить реляционную таблицу от зависимостей не связанных с первичными ключами! Если говорить проще, то это приведение к концепту связи типа один-ко-многим, которая является самой фундаментальной в реляционной модели данных и занимает, как правило, 80% всех видов связей в таблицах БД. Что ж, давайте попробуем разобрать это все на практическом примере! Сразу ясно не будет, но я думаю вы справитесь! Итак, возьмем за основу складской учет. Собственно, нам нужно будет взять некие данные и привести их к виду 3НФ (третья нормальная форма данных). Допустим мы имеем накладную на отпущенный товар вот такого вида:

107_1.gif (3473 b)

На первом этапе мы приведем ее к 1НФ (первая нормальная форма - к слову скажу на своей практике, я в 90% случаев видел таблицы именно в 1НФ, с чем это связано расскажу ниже!) Определение 1НФ записывается так:

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

Вернемся к нашей накладной, приводя ее к 1НФ сделаем заметку на то, что далее потребуется, анализ продаж по городам. Поэтому из поля "Адрес" выделим часть данных "Город" в отдельное поле. Учтем, что каждый покупатель может закупить в один день различное количество товаров. Для исключения повторений, фиксируем факт отпуска товара в отдельной записи. В результате получаем нашу накладную в 1НФ:

107_2.gif (2166 b)

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

Идем далее по примеру. Для приведения к 2НФ, выделим поля, которые входят в первичный ключ. Дата и номер накладной не подходят. Более применим первичный ключ "Товар". При этом исходим из того, что по одной накладной может быть отпущено одно наименование конкретного товара. Т.е. нет ситуации когда, один и тот же товар, занимает две строки в накладной, исключая повторение. Получаем четыре поля в составе первичного ключа следующего вида:

107_3.gif (2177 b)

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

107_4.gif (2170 b)

Вот теперь первое требование 2НФ выполнено. Но нет независимости поля от части первичного ключа. Ведь, там два поля! В нашем случае поле "Единица измерения" и "Цена за единицу измерения" зависят от значения поля "Товар", входящего в первичный ключ. Тогда давайте выделим эти поля в самостоятельную таблицу "Товары" и определим связь один-ко-многим! Вот теперь это похоже на 2НФ:

107_5.gif (3188 b)

Казалось бы и хватит, но идем дальше! Анализируя структуру нашего склада можно заметить, что значение поля "Покупатель" не зависит от пары "Номер накладной" и "Товар", а зависит только от поля "Номер накладной" по этому выделяем "Город" и "Адрес" в таблицу "Покупатели"! Вот так:

107_6.gif (3468 b)

Ведем анализ таблицы "Отпуск товаров со склада" далее. Хорошо видно, поле "Дата" зависит только от значения поля "Номер накладной" по этому выдели дату и номер накладной в самостоятельную таблицу "Накладные".

107_7.gif (3883 b)

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

107_8.gif (4879 b)

Ура! Вот мы, наконец и подходим к триумфу, ой не к 3НФ! А, она требует чтобы, в таблице не имелось транзитивных зависимостей между не ключевыми полями, т.е. чтобы значение любого поля не входящего в первичный ключ не зависело от значения другого поля, так же не входящего в первичный ключ! Надеюсь теперь хоть что-то понятно? Продолжим наш анализ, из которого так же видно, что таблица "Отпуск товаров со склада" имеет зависимость значения поля "Общая стоимость" от значения поля "Количество". Значение поля "Общая стоимость" может вычисляться как значение поля "Количество" умноженное на значение поля "Цена за единицу" из таблицы "Товары". Исключаем поле "Общая стоимость" из таблицы "Отпуск товаров со склада" вот теперь мы пришли к совершенной 3НФ! Что, собственно и требовалось получить:

107_9.gif (4618 b)

Вот мы и получили нормализованный складской учет! Но! У нормализации есть и свои недостатки! Прежде всего это большее количество сущностей БД. Чем это может грозить? Представьте себе нормализованную БД, масштаба крупного предприятия содержащую, сотни таблиц и тысячи связей между ними! Сопровождение и поддержка такой БД, превращается в достаточно не простую задачу, а если на предприятии текучка кадров программистов, то просто не возможно представить как все это можно осмыслить! Например, известны случаи эволюционного развития систем БД предприятий, функционирование которых впоследствии признавалось вышедшим за границы понимания! (!!!) Представили? Еще одним недостатком, можно определить трудности построения запросов к таким БД, так как необходимо связывать несколько таблиц! А границы понимания не безграничны особенно у человека! Так, что делайте выводы сами! Стоит ли идти по пути нормализации или свалить все в одну максимум две таблицы, что за частую все и делают, зато не нужно долго думать! Но я все же советую использовать хотя бы 2НФ - это не так сложно! Добавлю еще кое что в заключении. По определению, нормальной формы Бойса-Кодда (НФБК) - отсутствие инверсной частичной зависимости или ни первичный ключ, ни какая-либо его часть не должны зависеть от не ключевого атрибута. Эту форму еще шутливо называют 3S - нормальной формой. В теории нормализации рассматриваются НФ и более высокого порядка 4НФ, 5НФ и т.д. но они представляют собой чисто теоретический интерес и используются крайне редко! 7НФ и 8НФ встречаются только в диссертациях, может кто-то из вас ее напишет! :) Но главной задачей администратора БД, является достичь наибольшего уровня нормализации БД при наименьшем количестве объектов в ней! Вот вам не тривиальная задачка! А продвинуться до уровня 4НФ (multivalued dependence), а еще круче 5НФ (join dependence), это уже высший пилотаж! Пробуйте! Все в ваших руках!


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей - 14.03.2004