Нормализация бд
Нормализация является ответственной частью стадии проектирования ИС, поскольку позволяет привести исходные таблицы к виду для наиболее эффективной обработки в будущем. Нормализация — это пошаговый процесс разбиения исходных, таблиц на более простые, которые должны удовлетворять двум основным требованиям:
-
между полями таблицы не должно быть нежелательных функциональных зависимостей;
-
группировка полей в таблицах должна обеспечивать минимальное дублирование данных, эффективный (без трудностей) поиск, обработку и обновление данных.
Сегодня определено 5 основных нормальных форм (НФ).; Каждая НФ снимает определенные зависимости между полями и устраняет определенные трудности обработки данных.
Процесс нормализации разберем на следующем примере Этот пример будет реализован.далее средствами СУБД Ассеss в виде ИС «Заказы». Пусть существует некоторая фирма, торгующая некоторым товаром по заказам. Составим таблиц (рис. 1.6) для размещения заказа от клиента (как это обычно делается в электронной таблице Ехсе1).
При всей полноте данных о заказе, использование такого состава полей таблицы вызывает множество проблем:
-
в каждый новый заказ (новую запись) необходимо вводить, повторяющиеся значения, что трудоемко и возможны ошибки ввода;
-
невозможно упорядочить данные, например, по «городу»;
-
структура таблицы вмещает только 2 товара, при большем числе товаров нет колонок для их помещения, а при меньшем — ячейки (т.е память) расходуются впустую;
-
н
Таблица заказ
е отражается (не виден) список всех имеющихся на фирме товаров, их цен и т.д.
|
Фамилия И. 0. |
Адрес Телефон |
Дата Заказа |
ФИО менеджера |
Телефон менеджера |
|
Иванов И.И. |
г. Москва ул. Ленина 7-11 т.321-55-55 |
19.11.99 |
Ребров П.И. |
3-86 |
|
Иванов И.И. |
г. Москва ул. Ленина 7-11 т. 321-55-55 |
19.11.99 |
Ребров П.И. |
3-86 |
|
Петров Н.Н. |
г. Калуга ул. Мира 25 т.77-21-11 |
20.11.99 |
Шутов М.Н. |
3-83 |
|
Петров С.С. |
г. Москва ул. Ленина 8-15 т. 327-16-66 |
20.11.99 |
Ребров П.И. |
3-86 |
Продолжение
|
Товар |
Количество |
Цена |
Сумма |
Товар |
Количество |
Цена |
Сумма |
Общая сумма |
|
Плеер |
2 |
200 |
400 |
TV |
1 |
250 |
250 |
650 |
|
Миксер |
2 |
50 |
100 |
|
|
|
|
100 |
|
Миксер |
1 |
50 |
50 |
|
|
|
|
50 |
|
Кофемолка |
3 |
70 |
270 |
TV |
3 |
250 |
750 |
960 |
Рис 1.6
Для устранения некоторых из этих проблем в нашей таблице используем т.н. первую НФ (1НФ). Для ее приведения к 1НФ необходимо выполнение следующих правил:
1. Каждое, поле должно быть атомарным, т.е. содержать единственный элемент данных. У нас нарушение правила в поле АдресТелефон.
2. Поля в таблице не должны повторяться. У нас нарушение правила с полями Товар, Кол, Цена, Сумма.
Устранив эти нарушения, получим 2 отдельные таблицы в 1НФ, в которые введены первичные ключи (рис. 1.7, 1.8).
|
|
|
|
|
|
|
Таблица Заказ |
||
|
Код Заказа (пера, ключ) |
Ф.И.О. клиента |
Город |
Улицы |
Телефон |
Дата Заказа |
ФИО менеджера |
Телефон менеджера |
Общая сумма |
|
1 |
Иванов И.И. |
г. Москва |
ул. Ленина 7-11 |
321-55-55 |
79.11.99 |
Ребров П.И. |
3-86 |
550 |
|
2 |
Иванов И.И. |
г. Москва |
ул. Ленина 7-11 |
321-55-55 |
19.11.99 |
Ребров П.И. |
3-86 |
100 |
|
3 |
Петров Н.Н. |
г. Калуга |
ул. Мир 2-5' |
77-21-11 |
20.11.99 |
Шутов М.Н. |
3-83 |
50 |
|
4 |
Петров С.С. |
г. Москва |
ул. Ленина 8-15 |
327-16-66 |
20.11.99 |
Ребров П.И. |
3-86 |
960 |
|
|
|
|
Таблица Заказано товара |
||
|
НомерСтрокиЗаказа в Таблице (первичный ключ) |
КодЗаказа (внешний ключ) |
Товар |
Количество |
Цена |
Сумма |
|
1 |
1 |
Плеер |
2 |
200 |
400 |
|
2 |
1 |
TV |
1 |
250 |
250 |
|
3 |
2 |
Миксер |
2 |
50 |
100 |
|
4 |
3 |
Миксер |
1 |
50 |
50 |
|
5 |
4 |
Кофемолка |
3 |
70 |
210 |
|
6 |
4 |
ТV |
3 |
250 |
750 |
Рис 1.8.
Здесь также существуют проблемы, связанные с дублированием данных о клиенте и контролем избыточности данных. В частности, изменение телефона клиента влечет за собой необходимость поиска и изменения телефона во всех записях об этом клиенте.
Приведение таблиц ко 2НФ требует соответствия следующим правилам.
1. Каждая таблица содержит данные об одном предмете (объекте).
2. Каждая таблица должна содержать поле первичного ключа, который является уникальным идентификатором для каждой записи (строки).
3. Остальные (неключевые) поля должны относиться к первичному ключу, т.е. зависеть от него.
С учетом этих правил 2НФ разбивка таблиц и полей в них выглядит как показано на рис. 1.9, 1.10.
|
|
|
|
|
Таблица Заказ |
|
|
НомерСтрокиЗака за в Таблице (первичный ключ) |
КодЗаказа (внешний ключ) |
Дата Заказа |
ФИО менеджера |
Телефон менеджера |
Общая Сумма |
|
1 |
1 |
19.11.99 |
Ребров П. И. |
3-86 |
650 |
|
2 |
1 |
19.11.99 |
Ребров П. И. |
3-86 |
100 |
|
3 |
2 |
20.11.99 |
Шутов М.Н. - |
3-83 |
50 |
|
4 |
3 |
20.11.99 |
Ребров П. И. |
3-86 |
960 |
Рис 1.9
|
|
|
Таблица Клиент |
|||
|
Код Клиента (первич. ключ) |
ФИО |
Город |
Улицы№ |
Телефон |
|
|
1 |
Иванов И. И. |
Москва |
ул. Ленина 7-11 |
321-55-55 |
|
|
2 |
Петров Н.Н. |
Калуга |
ул. Мира 2-5 |
77-21-11 |
|
|
3 |
Петров С.С. |
Москва |
ул. Ленина 8-15 |
327-16-66 |
|
Рис 1.10.
Таблица ЗаказаноТовара пока остается без изменения.
Как видно, и здесь есть дублирование данных (о менеджере, о товаре) и проблема с изменением данных (телефон менеджера, цена товара). Таким образом, таблицы во 2НФ могут требовать дальнейших преобразований в ЗНФ. ЗНФ освобождает от:
-
дублирования (избыточности) данных;
-
аномалий выполнения операций добавления, удаления и обновления (изменения) данных.
Правило: таблица находится в ЗНФ, если она находится во 2НФ и в ней отсутствуют т.н. транзитивные зависимости неключевых полей от первичного ключа. Например, здесь поле ТелефонМенеджера зависит от первичного ключа через поле ФиоМенеджера:
ТелефонМенеджера ->ФиоМенеджера -> КодЗаказа
И
Таблица Заказ
|
КодЗаказа (внешний ключ) |
КодКлиенга - (внешний ключ) |
Дата Заказа |
КодМенеджера (внешний ключ) |
Общая Сумма |
|
1 |
1 |
19.11.99 |
1 |
650 |
|
2 |
1 |
19.11.99 |
1 |
100 |
|
3 |
2 |
20.11.99 |
2 |
50 |
|
4 |
3 |
20.11.99. |
1 |
960 |
Таблица Менеджер
|
Код Менеджера (первичный ключ) |
ФИО |
Телефон |
|
1 |
Ребров П.И. |
3-86 |
|
2 |
Шутов М.Н. |
3-83 |
Таблица Товары
|
КодТовар (первич ключ) |
Товар |
Цена, р. |
|
1 |
Плейер |
200 |
|
2 |
TV |
250 |
|
3 |
Миксер |
50 |
|
4 |
Кофемолка |
70 |
Рис. 1.11.
|
|
ТаблицаЗаказаноТовара |
||
|
КодЗаказа (внешний ключ) |
КодГовара (внешний ключ) |
Кол |
Сумма |
|
1 |
1 |
2 |
200 |
|
1 |
2 |
1 |
250 |
|
2 |
3 |
2 |
100 |
|
3 |
3 |
1 |
50 |
|
4 |
4 |
3 |
210 |
|
4 |
2 |
3 |
750 |
Рис. 1.12.
В таблице ЗаказаноТовара два внешних ключа образуют составной ключ, который идентифицирует данные о составе, (количество, сумма) конкретного заказа. В СУБД Ассеss состав и связи наших таблиц схематично показаны на рис. 1.13.
Такое представление называется схемой данных. Как видно, все связи между таблицами в нашей БД имеют тип один-ко-многим. Связующая таблица ЗаказаноТовара фактически обеспечивает между заказами и товарами отношение многие-ко-многим. Все последующие. этапы разработки выполняются на компьютере в среде СУБД Ассеss.
Рис. 1.13.