Метод нормальных форм. Решение задач

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

Решение. Нормализация отношений — это процесс построения оптимальной структуры таблиц и связей в реляционной БД (процесс уменьшения избыточности информации).

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

1) обеспечить быстрый доступ к данным;

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

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

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

 

1. Первая нормальная форма (1НФ). Таблица, находящаяся в первой нормальной форме должна отвечать следующим требованиям:

1) таблица не должна иметь повторяющихся записей;

2) в таблице должны отсутствовать повторяющиеся группы полей.

Для приведения к 1НФ можно использовать следующий алгоритм:

1. Определить поле, которое можно назначить первичным ключом. Если такого поля нет, то добавить новое уникальное ключевое поле;

2. Определить группы повторяющихся полей;

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

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

5. Определить тип отношения между таблицами.

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

1.Определяем поле, которое должно быть первичным ключом: Гос. номер

2.Определяем группы повторяющихся полей:

2.1. Марка, тип ТС, категория

2.2. Модель двигателя, мощность двигателя, тип двигателя

2.3.Наименование организации, выдавшей паспорт, адрес организации, выдавшей паспорт

2.4.Цвет кузова

2.5.Наименование собственника ТС

3.Выносим повторяющиеся группы полей в отдельные таблицы:

 

 

Марки

Марка ТС	Тип ТС	Категория

 

Двигатели

Модель	Мощность	Тип

 

Организации по выдаче ПТС

Наименование	Адрес

 

Цвета

Наименование

 

Собственники

Наименование

 

 

4.Назначаем первичные ключи в новых таблицах:

4.1.Марки: Код_марки

4.2.Двигатели: Код_двигателя

4.3.Организации по выдаче ПТС: Код_организации

4.4.Цвета: Код_цвета

4.5.Собственники: Код_собственника

5.В результате получится модель, представленная на рисунке 6.1.

 

Рис. 6.1. Модель, после приведения отношения «Паспорт транспортного средства» к 1 НФ

 

Отношения «Марки» и «Двигатели» являются ненормализованными. А следовательно их также необходимо привести к 1 НФ:

1.Определяем поле, которое должно быть первичным ключом: Код_марки (для отношения «Марки»), Код_двигателя (для отношения «Двигатели»)

2.Определяем группы повторяющихся полей:

2.1.Тип ТС, категория (для отношения «Марки»)

2.2.Модель, тип (для отношения «Двигатели»)

3.Выносим повторяющиеся группы полей в отдельные таблицы:

 

 

Типы ТС

Наименование	Категория

 

Модели двигателей

Наименование	Тип

 

4.Назначаем первичные ключи в новых таблицах:

4.1.Типы ТС: Код_типа

4.2.Модели двигателей: Код_модели

 

В результате получится следующая взаимосвязь отношений (рисунок 6.2):

 

 

Рис. 6.2. Приведение отношений «Марки» и «Двигатели» к 1 НФ

 

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

Частично зависимое поле — поле, зависящее только от части ключа.

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

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

Дальнейший процесс продолжить самостоятельно и построить 3-уровневую информационно – логическую модель.

Ниже рассмотрим решение задачи о формировании портфеля заказов на выполнение определенного вида работ для частного предприятия.

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

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

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

Задача. Список заказов – документ, содержащий все произведённые заказы (таблица 6.1).

 

Таблица 6.1

заказчик		сотрудник					услуга				Кол-во	Затраты	Дата заказа	Дата выполнения
ФИО	Адрес	Ф И о	Должность	Адрес	Дата рождения	Номер паспорта	Наименование	Подразделение	Ед. измерения	Стоимость за 1-цу
1	2	3	4	5	6	7	8	9	10	11	12	13	14	15

 

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

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

После приведения к первой нормальной форме, список заказов примет вид, показанный в таблице 6.5. На рисунке 6.3 представлены результаты первой нормальной формы

Таблица 6.2

Код заказчика	ФИО	Адрес
1	2	3

 

 

Таблица 6.3

Код сотрудника	Ф	И	О	Дата рождения	Серия паспорта	Номер паспорта	должность	Адрес
1	2	3	4	5	6	7	8	9

 

Таблица 6.4

Код услуги	подразделение	Наименование услуги	Кол-во	Ед. измерения	Стоимость за ед.
1	2	3	4	5	6

 

Таблица 6.5

Код заказчика	Код сотрудника	Код услуги	Кол-во	затраты	Дата заказа	Дата выполнения
1	2	3	4	5	6	7

 

 

Рисунок 6.3.  «Связи между сущностями – первая нормальная форма»

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

Отношения уже приведены ко второй нормальной форме.

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

Для приведения к третей нормальной форме добавим ещё три сущности: «Адреса» (таблица 6.6), «Должности» (таблица 6.7), «Подразделения» (таблица 6.8).

Таблица 6.6

Код адреса	Город	Улица	Дом	Квартира	Телефон (дом.)	Телефон (моб.)
1	2	3	4	5	6	7

 

Таблица 6.7

Код должности	должность
1	2

 

 

Таблица 6.8

Код подразделения	Подразделение
1	2

 

Таким образом, сущности «Заказчики», «Сотрудники», «Услуги» примут вид (таблицы 6.9, 6.10, 6.11 соответственно):

Код заказчика	ФИО	Код адреса
1	2	3

Таблица 6.9

 

Таблица 6.10

Код сотрудника	Ф	И	О	Дата рождения	Серия паспорта	Номер паспорта	Код адреса	Код должности
1	2	3	4	5	6	7	8	9

 

Таблица 6.11

Код услуги	Код подразделения	Наименование услуги	Кол-во	Ед. измерения	Стоимость
1	2	3	4	5	6

 

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

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

Для приведения к четвёртой и пятой нормальным формам, добавим в сущность «Заказы» поле «код заказа», которое будет содержать уникальные номера всех заказов. Таким образом, сущность «Заказы» примет вид (таблица 6.12):

Таблица 6.12

Код заказа	Код заказчика	Код сотрудника	Код услуги	Кол-во	затраты	Дата заказа	Дата выполнения
1	2	3	4	5	6	7	8

 

Кроме информационно – логического моделирования в данной задаче показан процесс логического и физического проектирования на основе CASE – средств (рисунок 6.4).

Нотация IDEF1X позволяет построить прототип модели. Для проектирования использовано ERWIN (рисунок 6.5).

 

 

Рис. 6.4. «Связи между сущностями – пятая нормальная форма»

 

Рис. 6.5- «Информационно-логическая модель»