Нормализация

Построение информационно-логической модели реляционной базы данных методом нормализации

ПРАКТИЧЕСКАЯ РАБОТА

Цель: Получение практических навыков проектирования таблиц с использованием метода нормальных форм

Теоретическая информация

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

Исходное отношение строится таким образом, чтобы оно было в 1НФ. Отношение ПРЕПОДАВАТЕЛЬ, используемое для иллюстрации метода нормализации, находится в 1НФ, поскольку все его атрибуты простые.

ФИО	Должн	Оклад	Стаж	Д_Стаж	Кафедра	Предмет	Группа	ВидЗан
ИвановА.	ассистент	1000	5	100	ИВТ	ПО ЭВМ	256	Практ
ИвановА.	ассистент	1000	5	100	ИВТ	Пролог	123	Практ
ПетровМ.	доцент	1400	7	100	ИВТ	ПО ЭВМ	256	Лекция
ПетровМ.	доцент	1400	7	100	ИВТ	Паскаль	256	Практ
СидоровН.	ассистент	1000	10	150	ИВТ	Пролог	123	Лекция
СидоровН.	ассистент	1000	10	150	ИВТ	Паскаль	256	Лекция
ЕгоровВ.	ассистент	1000	5	100	Физики	Информ.	244	Лекция

Первичный ключ этого отношения получается в предположении, что каждый преподаватель в одной группе по одному предмету может либо читать лекции, либо проводить практические занятия. Поэтому ключ будет составным ФИО, Предм, Группа.

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

В отношении ПРЕПОДАВАТЕЛЬ можно выделить частичную зависимость атрибутов Стаж, Д_Стаж, Каф, Должн, Оклад от ключа - указанные атрибуты находятся в функциональной зависимости только от атрибута ФИО, являющегося частью составного ключа.

Эта частичная зависимость от ключа приводит к следующему:

1. В отношении присутствует явное и неявное избыточное дублирование данных, например: повторение сведений о стаже, должности и окладе преподавателей, проводящих занятия в нескольких группах и/или по разным предметам; повторение сведений об окладах для одной и той же должности или о надбавках за одинаковый стаж.

2. Следствием избыточного дублирования данных является проблема их редактирования. Например, изменение должности у преподавателя Иванова И.М. потребует просмотра всех кортежей отношения и внесения изменений в те из них, которые содержат сведения о данном преподавателе.

Часть избыточности устраняется при переводе отношения во вторую нормальную форму (2НФ).

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

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

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

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

R1:

ФИО	Предмет	Группа	ВидЗан
ИвановА.	ПО ЭВМ	256	Практ
ИвановА.	Пролог	123	Практ
ПетровМ.	ПО ЭВМ	256	Лекция
ПетровМ.	Паскаль	256	Практ
СидоровН.	Пролог	123	Лекция
СидоровН.	Паскаль	256	Лекция
ЕгоровВ.	Информ.	244	Лекция

R2:

ФИО	Должн	Оклад	Стаж	Д_Стаж	Кафедра
ИвановА.	ассистент	1000	5	100	ИВТ
ПетровМ.	доцент	1400	7	100	ИВТ
СидоровН.	ассистент	1000	10	150	ИВТ
ЕгоровВ.	ассистент	1000	5	100	Физики

Рис. 1 – Пример перехода исходного отношения в 2НФ

В отношении R1 первичный ключ является составным и также состоит из атрибутов ФИО. Предм, Группа.. В отношении R2 ключ простой - ФИО.

Исследование отношений R1 и R2 показывает, что переход к 2НФ позволил исключить явную избыточность данных в таблице R1 - повторение строк со сведениями о преподавателях. В R2 по-прежнему имеет место неявное дублирование данных. Оно связано с повторением значения Оклад для одинаковых значений атрибута Должн. и Д_Стаж для одинаковых значений атрибута Стаж.

Для дальнейшего совершенствования отношения необходимо преобразовать его к третьей нормальной форме (3НФ).

Третья нормальная форма.

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

Существует и альтернативное определение.

Определение 2. Отношение находится в ЗНФ в том и только в том случае, если все неключевые атрибуты отношения взаимно независимы и полностью зависят от первичного ключа.

В отношении R1 транзитивные зависимости отсутствуют, в отношении R2 они есть:

ФИО ->Должн->Оклад, ФИО->Oклад->Должн, ФИО->Стаж->Д_Стаж

Транзитивные зависимости также порождают избыточное дублирование информации в отношении. Устраним их. Для этого, используя операцию проекции на атрибуты, являющиеся причиной транзитивных зависимостей, преобразуем отношение R2, получив при этом отношения R3, R4 и R5, каждое из которых находится в ЗНФ. Графически эти отношения представлены на Рисунке 2. Заметим, что отношение R2 можно преобразовать по-другому, а именно: в отношении R3 вместо атрибута Должн взять атрибут Оклад.

R3:

ФИО	Должн	Стаж	Кафедра
ИвановА.	ассистент	5	ИВТ
ПетровМ.	доцент	7	ИВТ
СидоровН.	ассистент	10	ИВТ
ЕгоровВ.	ассистент	5	Физики

R4: R5:

Должн	Оклад
ассистент	1000
доцент	1400

Стаж	Д_Стаж
5	100
7	100
10	150

Рис. 2 – Представление 3НФ

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

Состав атрибутов:

1. FIO - фамилия и инициалы;

2. God - год рождения;

3. Dolgn - должность занимаемая;

4. 0_Dolgn - оклад по должности;

5. Stag - стаж работы;

6. D_Stag - надбавка за стаж (свыше 5, 10, 15, 20, 25, 30, 35, 40 лет);

7. Udal - удаленность (средняя, большая, очень большая);

8. D_Udal - надбавка за удаленность;

9. Slogn - сложность (средняя, высокая, очень высокая);

10. D_Slogn - надбавка за сложность;

11. Vredn - вредность (по категориям: 1, 2, 3, 4, 5);

12. D_Vredn - надбавка за вредность;

13. Clasn - классность (мастер, первая, вторая, третья);

14. D_ Clasn - надбавка за классность;

15. U_Zvan - ученое звание (доцент, профессор);

16. D_Uzvan - надбавка за ученое звание;

17. U_Step - ученая степень (ктн, дтн);

18. D_UStep - надбавка за ученую степень;

19. Zvanie - воинское звание (мл. лейтенант, лейтенант, ст.лейтенант, капитан, майор, подполковник, полковник);

20. D _Zvanie - надбавка за воинское звание.

Необходимо в соответствии с номером варианта:

1. Составить исходное отношение в 1НФ

2. Выявить зависимости между атрибутами отношения

3. Перевести исходное отношение к 3НФ

4. Определить ключевые поля в отношениях, основные и дополнительные таблицы, поля связи, типы связей

Таблица 1- Распределение атрибутов по вариантам

Вариант

Номер атрибута

7

8

9

10

11

12

13

14

15

16

17

18

19

20

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

*

*

*

*