Нормализация отношений
презентация к уроку на тему

Слайд 1

Слайд 2

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

Слайд 3

Первая нормальная форма На первом этапе моделирования данные хранятся в одном отношении, имеющем следующие атрибуты: Проектные работы (ном_сотр, фамилия, ном_отд, ном_проекта, наименование, ном_задания, телефон) Таблица 1. Проектные работы Т. к. сотрудник в каждом проекте выполняет ровно одно задание, то в качестве потенциального ключа отношений, необходимо взять пару атрибутов { ном_сотр, ном_проекта } Ном_сотр Фамилия Ном_отд Ном_проекта Наименоваине Ном_задания Телефон 1 Иванов А. А. 11 212 Чистый город 01 11-22-33 1 Иванов А. А. 11 222 Волонтер 01 11-22-33 2 Петров Н. Н. 11 222 Волонтер 02 11-22-33 3 Сидорова И. Н. 12 232 Реклама 01 22-33-44 3 Сидорова И. Н. 12 212 Чистый город 02 22-33-22 4 Солнцева О. С. 13 222 Волонтер 02 33-44-55

Слайд 4

Аномалии обновления Данные в отношении «Проектные работы» хранятся с большой избыточностью (повторяются фамилии, телефоны, названия проектов). Кроме того, в отношении хранятся независимые друг от друга данные (о сотрудниках, о проектах, об объектах). Аномалия вставки: Нельзя добавить строку 4, Ромашкин, null, null, null, null , 33-44-55 , т. к. атрибут ном_проекта входит в состав ключа и поэтому не может иметь нулевое значение. Аномалия обновления: фамилии сотрудников, названия проектов повторяются многократно, поэтому если например изменить название проекта Волонтер , то такие изменения необходимо выполнить одновременно во всех местах, где встречается данное название. Аномалия удаления: если уволить сотрудницу Сидорову И. Н ., то будет потеряна информация о проекте Реклама . Вывод: Логическая модель данных не адекватна модели предметной области. БД основанная на такой модели будет работать некорректно.

Слайд 5

Функциональные зависимости Отношение «Проектные работы» находятся в 1НФ, при этом логическая модель данных неадекватна модели предметной области. Таким образом первой нормальной формы недостаточно для правильного моделирования данных. Зависимость атрибутов от ключа отношения: { ном_сотр, ном_проекта } - > Фамилия { ном_сотр, ном_проекта } - > ном_отдела { ном_сотр, ном_проекта } - > наименование { ном_сотр, ном_проекта } - > ном_задания { ном_сотр, ном_проекта } - > телефон Зависимость атрибутов, характеризующих сотрудника от табельного номера сотрудника: { ном_сотр } - > фамилия { ном_сотр } - > ном_отдела { ном_сотр } - > телефон Зависимость наименования проекта от номера проекта: { ном_проекта } - > наименование

Слайд 6

Вторая нормальная форма Отношение «Проектные работы» не находятся во 2НФ, т. к. есть неключевые атрибуты, зависящие от части сложного ключа. { ном_сотр } - > фамилия { ном_сотр } - > ном_отдела { ном_сотр } - > телефон { ном_проекта } - > наименование Для того, чтобы устранить зависимость атрибутов от части сложного ключа, нужно произвести декомпозицию отношения на несколько отношений, причем те атрибуты, которые зависят от части сложного ключа выносятся в отдельные отношения. Таблица 2. «Сотрудники» Ном_сотр Фамилия Ном_отдела Телефон 1 Иванов И. И. 11 11-22-33 2 Петров Н. Н. 11 11-22-33 3 Сидорова И. Н. 12 22-33-44 4 Солнцева О. С. 13 33-44-55

Слайд 7

Ном_проекта Наименование 212 Чистый город 222 Волонтер 232 Реклама Таблица 3. «Проекты» Ном_сотр Ном_проекта Ном_задания 1 212 01 1 222 01 2 222 02 3 232 01 3 212 02 4 222 02 Таблица 4. «Задания» Аномалия удаления: Если уволится сотрудница Солнцева О. С., будет потеряна информация о 13 отделе. Вывод: Логическая модель данных неадекватна модели предметной области.

Слайд 8

Третья нормальная форма Отношение «Сотрудники» не находится в 3НФ, т. к. имеется функциональная зависимость неключевых атрибутов: ном_отд - > телефон. Для того, чтобы устранить зависимость неключевых атрибутов нужно произвести декомпозицию отношения на несколько отношений, причем те неключевые атрибуты, которые являются зависимыми, выносятся в отдельные отношения. Таблица 5. «Отделы» Таблица 6. «Сотрудники» Ном_отдела Телефон 11 11-22-33 12 22-33-44 13 33-44-55 Ном_сотр Фамилия Ном_отдела 1 Иванов И. И. 11 2 Петров Н. Н. 11 3 Сидорова И. Н. 12 4 Солнцева О. С. 13

Слайд 9

Атрибут номер отдела не является ключевым в отношении «Сотрудники», но становится потенциальным ключом в отношении «Отделы». Именно за счет этого устраняется избыточность связанная с многократным хранением одних и тех же номеров телефона. Вывод: Т. о. все обнаруженные аномалии устранены. Реляционная модель состоит из четырех отношений: Сотрудники, Проекты, Отделы, Задания, которые находятся в 3НФ, являются адекватной описанной модели предметной области. Ном_проекта Наименование 212 Чистый город 222 Волонтер 232 Реклама Ном_сотр Ном_проекта Ном_задания 1 212 01 1 222 01 2 222 02 3 232 01 3 212 02 4 222 02 Ном_отдела Телефон 11 11-22-33 12 22-33-44 13 33-44-55 Ном_сотр Фамилия Ном_отдела 1 Иванов И. И. 11 2 Петров Н. Н. 11 3 Сидорова И. Н. 12 4 Солнцева О. С. 13