Как избавляются от аномалий в бд
Перейти к содержимому

Как избавляются от аномалий в бд

  • автор:

Нормальные формы: первая и вторая

Первая и вторая нормальные формы разработаны Эдгаром Коддом и являются достаточно «самоочевидными». Самоочевидность заключается в том, что отношения в первой и второй нормальной формах обладают интуитивно понятными базовыми свойствами, которые логично требовать от отношений, используемых в рамках баз данных.

Первая нормальная форма

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

Запрещенные конструкции

Повторяющиеся группы
CourseId Lecturer Phone (1) Phone (2)
1 Корнеев Г. А. 111-11-11
2 Киракозов А. Х. 222-22-22 333-33-33
3 Кудряшов Б. Д. 444-44-44 555-55-55
4 Сегаль А. С. 666-66-66

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

Неатомарные атрибуты
CourseId Lecturer Phones
1 Корнеев Г. А. 111-11-11
2 Киракозов А. Х. 222-22-22
333-33-33
3 Кудряшов Б. Д. 444-44-44
555-55-55
4 Сегаль А. С. 666-66-66

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

Отсутствие ключа

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

Приведение в 1НФ

Для того, чтобы привести произвольное отношение [math]R[/math] в 1НФ, достаточно:

  1. Рассмотреть все наборы атрибутов, имеющих одинаковый смысл
  2. Для каждого фиксированного значения оставшихся атрибутов сделать по записи на каждое значение выбранных:
    • рассмотрим повторяющиеся атрибуты [math]A_1, \ldots, A_k[/math]
    • рассмотрим оставшиеся атрибуты [math]B_1, \ldots, B_n[/math]
    • построим такое отношение [math]T[/math] на атрибутах [math]B_1, \ldots, B_n, A[/math] , что [math]\ \in T \Longleftrightarrow \ \in \pi_R \land a \in \bigcup\limits_^k \pi_R[/math]
  3. Аналогичную процедуру повторить для всех неатомарных атрибутов

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

CourseId Lecturer Phone
1 Корнеев Г. А. 111-11-11
2 Киракозов А. Х. 222-22-22
2 Киракозов А. Х. 333-33-33
3 Кудряшов Б. Д. 444-44-44
3 Кудряшов Б. Д. 555-55-55
4 Сегаль А. С. 666-66-66

Аномалии

Переход в 1НФ не уменьшает выразительную способность «разрешенных» отношений, но при этом исправляет только самые простые аномалии, поэтому в отношениях в 1НФ, не приведенных хотя бы во 2НФ, могут возникать аномалии более сложного вида.

Определение:
Аномалия вставки – зависимость возможности записать обладающие собственным независимым смыслом данные от наличия другой связанной информации.

В рассмотренном выше примере невозможно записать информацию о телефоне конкретного преподавателя, если он не читает ни один курс (таким образом, возможность записать [math]\mathrm[/math] для конкретного [math]\mathrm[/math] зависит от наличия соответствующего [math]\mathrm[/math] , хотя напрямую они не зависят друг от друга).

Определение:
Аномалия удаления – невозможность удалить часть данных, не удалив никакую связанную с ней информацию.

В рассмотренном выше, опять же, примере невозможно удалить информацию о том, что конкретный преподаватель читает конкретный курс, не потеряв его номер телефона (как и в случае с аномалией вставки, возможность хранить [math]\mathrm[/math] зависит от существования соответствующего [math]\mathrm[/math] ).

Определение:
Аномалия изменения – ситуация, в которой частичное изменение данных нарушает целостность базы данных.

В рассмотренном примере если один преподаватель ведет один курс и имеет два телефона, при изменении [math]\mathrm[/math] в одной из соответствующих ему записей будет невозможно восстановить какой курс на самом деле ведет преподаватель (записи с разными [math]\mathrm[/math] , но одинаковыми [math]\mathrm[/math] и [math]\mathrm[/math] , должны всегда поддерживаться в таком же состоянии).

Вторая нормальная форма

Вторая нормальная форма позволяет избавиться от некоторых аномалий, возникающих в отношениях в 1НФ.

Запрещенные конструкции

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

CourseId Year Lecturer Exam
1 2020 Корнеев Г. А. yes
2 2019 Киракозов А. Х. no
2 2020 Киракозов А. Х. no
3 2019 Левина А. Б. yes
3 2020 Чепурной А. И. yes

В данном отношении можно выделить следующие функциональные зависимости: [math]\mathrm \rightarrow \mathrm[/math] (наличие экзамена зависит только от предмета) и [math]\mathrm, \mathrm \rightarrow \mathrm[/math] (каждый год только один преподаватель читает конкретный предмет). Таким образом, ключ в данном отношении – [math]\mathrm, \mathrm[/math] , но при этом [math]\mathrm[/math] зависит только от части ключа.

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

Приведение во 2НФ

Отношение в 1НФ приводится к 2НФ декомпозицией по «мешающим» функциональным зависимостям. На примере выше такая зависимость только одна – [math]\mathrm \rightarrow \mathrm[/math] .

Определение:
Декомпозиция отношения [math]R[/math] , состоящего из наборов атрибутов [math]A, B, C[/math] , по функциональной зависимости [math]A \rightarrow B[/math] – пара отношений [math]\pi_ R[/math] и [math]\pi_ R[/math] .

Декомпозиция рассмотренного примера по «лишней» функциональной зависимости дает следующий результат:

CourseId Year Lecturer
1 2020 Корнеев Г. А.
2 2019 Киракозов А. Х.
2 2020 Киракозов А. Х.
3 2019 Левина А. Б.
3 2020 Чепурной А. И.
CourseId Exam
1 yes
2 no
3 yes

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

Аномалии

Аномалия, свойственная 2НФ, возникает, когда какой-то атрибут зависит от ключа транзитивно через множество неключевых атрибутов. Рассмотрим следующий пример:

CourseId Year Lecturer Phone
1 2020 Корнеев Г. А. 111-11-11
2 2019 Киракозов А. Х. 222-22-22
2 2020 Киракозов А. Х. 222-22-22
3 2019 Левина А. Б. 333-33-33
3 2020 Чепурной А. И. 444-44-44

В нем есть две базовые функциональные зависимости: [math]\mathrm, \mathrm \rightarrow \mathrm[/math] и [math]\mathrm \rightarrow \mathrm[/math] . Несмотря на то, что данное отношение находится во 2НФ, в нем все еще имеют место все три аномалии 1НФ – аномалии вставки, удаления и изменения (информация о телефонах и о преподавании никак не разделена). Для исправления аномалий 2НФ отношение переводят в третью нормальную форму и выше.

Избыточное дублирование и аномалии

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

Принципы нормализации

  • В каждой таблице БД не должно быть повторяющихся полей;
  • В каждой таблице должен быть уникальный идентификатор (первичный ключ);
  • Каждому значению первичного ключа должна соответствовать достаточная
    информация о типе сущности или об объекте таблицы (например,
    информация об успеваемости, о группе или студентах);
  • Изменение значений в полях таблицы не должно влиять на информацию
    в других полях (кроме изменений в полях ключа).

Избыточность данных

Избыточность данных в БД относится к нежелательным явлениям,
поскольку ведет к увеличению объема памяти, необходимого для
физического хранения отношений. Избыточность вызывается, прежде
всего, дублированием данных.
Вот характерный пример отношения (табл. 6.1), содержащего
нежелательную избыточность:

Таблица 6.1. Отношение СТУДЕНТ

Номзачкн ФИОстудента Кодгруппы ФИО_старосты Куратор
20-Т-201 Иванов С.И. 20-Т-11 Рябов В.С. Доц. Фок И.И.
20-Т-215 Петров Я.Р. 20-Т-12 Сизов М.М. Доц. Докин С.С.
20-Т-217 Рябов В.С 20-Т-11 Рябов В.С. Доц. Фок И.И.
20-Т-211 Сенова А.Л. 20-Т-11 Рябов В.С. Доц. Фок И.И.

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

Виды аномалий данных

Различают три вида аномалий в базе данных:

  • аномалии включения;
  • аномалии удаления;
  • аномалии модификации.

Аномалии включения
В приведенном выше отношении аномалии включения возникают
при попытке создать новую группу и ввести ее в отношение при том
условии, что в нее еще не зачислен ни один студент. Ввод такой
информации в подобной ситуации требует присвоения значения NULL
всем атрибутам описания студента, в том числе и атрибуту Номзачкн,
который является первичным ключом данного отношения. Но реализация
такой попытки приведет к нарушению категорней целостности, а значит,
система ее обязана отклонить.
Результатом анализа является вывод о том, что в отношении табл. 6.1
присутствуют аномалии включения, а, следовательно, это отношение
должно быть преобразовано таким образом, чтобы от них избавиться.
Структура отношений, содержащая ту же информацию, что и
отношение СТУДЕНТ, но лишенная аномалий включения, представлена в
табл. 6.2 и 6.3.

Таблица 6.2 Отношение СТУДЕНТ
Ном.зач.кн. ФИОстудента Кодгруппы
20-Т-201 Иванов С.И. 20-Т-11
20-Т-215 Петров Я.Р. 20-Т-12
20-Т-217 Рябов В.С. 20-Т-11
20-Т-211 Сенова А.Л. 20-Т-11

Таблица 6.3 Отношение ГРУППА
Кодгруппы ФИОстаросты Куратор
20-Т-11 Рябов В.С. Доц. Фок И.И.
20-Т-12 Сизов М.М. Доц. Докин С.С.

Аномалии удаления
Вернемся к анализу отношения, представленного в табл. 6.1. При
удалении из этого отношения кортежа:
20-Т-215 Петров Я.P. 20-T-12 Сизов М.М. Доц. Докин С.С.
из базы данных будут удалены все сведения о группе 20-Т-12. Такая
ситуация представляет собой аномалию удаления.
Для исключения из базы данных аномалии удаления это отношение
должно быть преобразовано. Причем преобразования должны быть
проведены точно такие же, какие были проведены для исключения
аномалии включения.

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

Проблема обратимости

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

Проблемы проектирования баз данных

Дублирование данных бывает: простое (неизбыточное) и избыточное. Избыточное дублирование данных может привести к проблемам при обработке данных.

Избавиться от избыточности данных поможет декомпозиция исходного отношения.

Аномалия – это такая ситуация в таблицах БД, которая приводит к противоречиям в БД либо существенно усложняет обработку данных.

Виды аномалий:

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

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

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

Формирование исходного отношения

Проектирование БД начинается с определения всех объектов, сведения о которых будут включены в базу, и определения их атрибутов. Затем атрибуту сводятся в одну таблицу – исходное отношение.

При создании исходного отношения появляются явная и неявная избыточности.

Явная избыточность заключается в том, что одни и те же значения одного или нескольких атрибутов могут появляться в отношении несколько раз.

Неявная избыточность встречается там, где есть неявная зависимость между атрибутами

Метод нормальных форм

Зависимости между атрибутами

Виды зависимостей между атрибутами: функциональные, транзитивные, многозначные.

Функциональная зависимость

Атрибут В функционально зависит от атрибута А, если каждому значению А соответствует в точности одно значение В.

Обозначение: A → B. Это значит, что во всех кортежах с одинаковым значением атрибута А атрибут В будет иметь также одно и то же значение.

Если существует функциональная зависимость вида A→B и В→А, то между А и В имеется взаимно однозначное соответствие, или функциональная зависимость. О

Обозначение: A↔B или В↔А.

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

Частичная зависимость (частичная функциональная зависимость) – зависимость неключевого атрибута от части составного ключа.

Полная функциональная зависимость – зависимость неключевого атрибута от всего составного ключа.

Транзитивная зависимость

Атрибут С зависит от атрибута А транзитивно (существует транзитивная зависимость), если для атрибута А, В, С выполняются условия A→B и В→С, по обратной зависимости отсутствуют.

Множественная зависимость

В отношении R атрибут В многозначно зависит от атрибута А, если каждому значению А соответствует множество значений В, не связанных с другими атрибутами R.

Обозначения: А=>B, AB.

3. Нормализация отношений при проектировании БД

Цель раздела — ознакомление с процедурой нормализации, её разными уровнями, теоретическим и практическим аспектами, критериями определения уровня нормализации на практике в зависимости от требований конечных пользователей.

3.1. Процедура нормализации

С одной стороны, процесс проектирования структур БД является творческим, неоднозначным, с другой стороны, его узловые моменты могут быть формализованы [4 — 7, 10, 12]. В процессе разработки логическая модель данных постоянно тестируется и проверяется на соответствие требованиям пользователей. Корректность логической модели данных обеспечивает процедура нормализации.

Процедура нормализации БД заключается в устранении избыточности данных и выявлении функциональных зависимостей. Устранение избыточности данных гарантирует компактность набора данных за счет ухода от их ненужного дублирования и исключения возможности возникновения аномалии вставки, удаления и обновления кортежей после физической реализации БД. Функциональная зависимость связывает атрибуты в одном отношении с единственным значением в другом отношении. Функциональную зависимость для отношений А и B принято обозначать как A→B. Это понятие подводит «на один шаг» к родственной концепции объединения отношений связями типа один к одному (1:1) или один ко многим (1:М).

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

Существует следующие уровни нормализации: первая нормальная форма (1НФ), 2НФ, 3НФ, нормальная форма Бойса-Кодда (БКНФ), 4НФ, 5НФ. Однако, до сегодняшнего дня ни одна из реляционных СУБД не поддерживает все пять нормальных форм. Это является следствием жестких требований к производительности. Суть дела состоит в том, что в полностью нормализованой БД для выполнения запроса будет необходимо соеденить настолько много таблиц, что производительность такой системы не сможет удовлетворить пользователей. Поэтому на практике используют лишь первые три уровня нормализации — 1НФ, 2НФ, ЗНФ.

3.2. Первая нормальная форма

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

Неделимость значения атрибута говорит о том, что его нельзя разделить на более мелкие части. Например, если в атрибуте «Фамилия Имя Отчество» содержится фамилия, имя и отчество читателя, требование неделимости не соблюдается (рис. 3.1, а). Здесь необходимо выделить в отдельные атрибуты имя и отчество. В результате получится три атрибута отношения «ЧИТАТЕЛИ»: «Фамилия», «Имя» и «Отчество» (рис. 3.1, б).

Рис. 3.1. Возможные спецификации ненормализованного отношения «ЧИТАТЕЛИ»

На более мелкие части можно также разделить атрибуты: «Место рождения» («Страна», «Административное образование», «Населенный пункт»), «Место выдачи паспорта» («Страна», «Административное образование», «Населенный пункт»), «Место основной работы» («Тип предприятия», «Название предприятия»), «Место жительства» («Страна», «Административное образование», «Населенный пункт», «Жилой массив / Проспект / Улица / Переулок», «Дом», «Корпус», «Квартира») (рис. 3.1, б).

Для контакта читатель может определить один, несколько или ни одного номера телефона. Таким образом в общем случае информация в атрибуте «Номер телефона» может быть розделена на несколько частей, каждая из которых является отдельным телефонным номером (рис. 3.1, а). На первый взгляд эту проблему можно решить так же, как и для фамилии, имени и отчества, выделив для наиболее распространенных типов телефонов отдельные атрибуты (рис. 3.1, б). Однако, в этом случае, мы столкнемся с повторяющейся группой атрибутов, содержащих одинаковые по смыслу значения в пределах одного кортежа, например: «Домашний телефон», «Рабочий телефон», «Мобильный телефон».

Чтобы отношение «ЧИТАТЕЛИ» (рис. 3.1, б) соответствовало 1НФ необходимо удалить из него группу атрибутов с номерами телефонов, которые повторяются в пределах одного кортежа, в другое отношение вместе с копией ключевого атрибута «№ читательского билета» (рис. 3.2). Причем, выделим для указания номера и типа телефона отдельные атрибуты. Это позволяет: во-первых, учитывать не только три указанных типа телефона, но и добавлять новые и во-вторых, указывать для каждого читателя только те типы телефонов, которые у него имеются, в-третьих, можно указать для любого читателя несколько однотипных телефонов или вообще не указывать ни одного телефонного номера.

Рис. 3.2. Приведение отношения «ЧИТАТЕЛИ» к 1НФ

3.3. Вторая нормальная форма

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

Отношение «ЧИТАТЕЛИ» (рис. 3.2) не представлено во 2НФ. В нём каждый кортеж может быть однозначно идентифицирован следующими атрибутами: «№ читательского билета», «Серия паспорта» и «№ паспорта». Совокупность этих атрибутов является суперключом этого отношения. Он состоит из двух потенциальных ключей, которые по отдельности могут идентифицировать кортежи отношения. Из двух потенциальных ключей в качестве первичного ключа выбран тот, длина которого минимальна. Наличие обоих потенциальных ключей обусловлено требованиями пользователей.

Привести отношение «ЧИТАТЕЛИ» ко 2НФ можно, вынеся в отдельное отношение атрибуты 2 – 22, которые касаются паспортных данных, и копию первичного ключа «№ читательского билета» (рис. 3.3). Однако в результате мы получим отношение «ПАСПОРТНЫЕ ДАННЫЕ», которое имеет такой же суперключ, как и отношение «ЧИТАТЕЛИ», до приведения его ко 2НФ. В нашем случае дальнейшая нормализация отношения «ПАСПОРТНЫЕ ДАННЫЕ» невозможна.

Рис. 3.3. Отношения «ТЕЛЕФОНЫ» и «ЧИТАТЕЛИ», приведенные ко 2НФ

В отношении «ТЕЛЕФОНЫ» (рис. 3.3) на первый взгляд кажется, что если в описании логической модели добавить номер телефона читателя, включая код страны, оператора или населенного пункта, то потенциальным будет ключ, в составе которого всего один атрибут – «№ телефона». Однако, у двух разных читателей может быть один и тот же домашний или рабочий телефоны. Следовательно, однозначно идентифицировать кортежи можно по составному суперключу, в который входят атрибуты «№ читательского билета» та «№ телефона». Мы видим, что суперключ не является избыточным. Его состав совпадает с первичным ключом. Значит отношение «ТЕЛЕФОНЫ» представлено во второй нормальной форме.

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

В общем 1НФ и 2НФ рассматриваются как промежуточные этапы в процессе нормализации БД. Большая часть СУБД ориентирована на достижение следующей степени нормализации — третьей нормальной формы (ЗНФ). Это связано с тем, что представление отношений в 3НФ вполне отвечает почти всем практическим задачам. При разработке исключительно больших систем на сверхбыстродействующих компьютерах, когда необходимо обеспечить максимальное сокращение объемов данных, желательно провести дальнейшую нормализацию отношений.

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

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

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

Рассмотрим пример из области учета товара на складе. Кажется логичным, чтобы в отношение с оперативной информацией о товаре на складе входили в числе прочих три следующих атрибута: «Количество товара», «Цена за единицу товара» и «Общая стоимость товара». Они не являются ключевыми. Для получения значения «Общей стоимости товара» необходимо перемножить значения, находящиеся в атрибутах: «Количество товара» и «Цена за единицу товара». Т.е. значение поля «Общей стоимости товара» зависит от значений двух атрибутов, не входящих в состав первичного ключа. Это противоречит определению 3НФ. Чтобы данное отношение соответствовало третьей нормальной форме из него необходимо убрать атрибут «Общая стоимость товара».

Отметим, что отношения «ТЕЛЕФОНЫ» и «ЧИТАТЕЛИ» (рис. 3.3) приведены к третьей нормальной форме. Это следует из того, что они представлены во второй нормальной форме и в них нет транзитивных зависимостей.

Ниже приводится вариант определения 3НФ, называемого нормальной формой Бойса-Кодда (Воусе-Codd) – БКНФ, где устанавливаются более строгие требования.

Отношение X представлено в нормальной форме Бойса-Кодда, если в каждой нетривиальной функциональной зависимости В→А В является суперключом.

3.5. Четвертая и пятая нормальные формы

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

В отношении X существует многозначная зависимость А→В тогда, когда в нем можно обнаружить ситуации, где пара кортежей содержит дублирующееся значение А и одновременно существуют другие пары кортежей, полученные путем перестановки значений В, присутствующих в первой паре.

Прежде всего, для существования многозначной зависимости требуется существование пар кортежей. А и В могут быть как отдельными атрибутами, так и объединением некоторого набора атрибутов. Тривиальная многозначная зависимость для А→В существует в тогда, и только тогда, когда В является подмножеством А или А объединяет B = XS (более крупное отношение содержит исходное отношение).

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

Отношение X представлено в четвертой нормольной форме (4НФ) тогда и только тогда, когда оно представлено в БКНФ и для любой многозначной зависимости А→В в этом отношении можно сказать, что эта зависимость либо является тривиальной, либо А является суперключом таблицы X.

Пятая нормальная форма (5НФ) достигается в том случае, когда отношение не может далее разбиваться на более мелкие отношения посредством операции проектирования.

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

3.6. Нормализация – за и против

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

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

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

Третья нормальная форма и нормальная форма Бойса-Кодда являются теоретическими конструкциями, в то время как большинство разработчиков БД работают в реальном мире. Поэтому уместно сделать несколько замечаний о недостатках, присущих отношениям, представленным в 3НФ. Существуют варианты, когда имеет смысл разделить отношение на более мелкие, если часть представленных в нём данных непостоянна и часто обновляется (оперативная информация), а остальные данные пассивны и изменяются в редких случаях (справочная информация). Также есть смысл объединить отношения, когда необходимо обеспечить высокую скорость реакции на запрос. Можно даже пойти на дублирование данных в таблицах, если это позволит снизить затраты на обработку запросов, хотя формально не следовало бы этого делать.

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

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

Если пользователю нет необходимости детализировать атрибуты «Место рождения», «Место выдачи паспорта», «Место основной работы» и «Место жительства», то поиск компромисса между требованиями нормализации и быстродействием для отношения «ЧИТАТЕЛИ» (рис. 3.1, а) приводит к тому, что, по сути, это отношение может не соответствовать даже 1НФ (рис. 3.4). Обратите внимание, что паспортные данные читателей также не вынесены в отдельную таблицу. Это позволяет сократить время выполнения запроса, который выдает все имеющиеся данные о читателях, за счет отсутствия необходимости соединения отношений «ПАСПОРТНЫЕ ДАННЫЕ» и «ЧИТАТЕЛИ».

Рис. 3.4. Результат нормализации отношения «ЧИТАТЕЛИ»

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

В пользу реализации связи 1:1 между субъектами учета более чем в одном отношении говорит наличие в требованиях пользователей необходимости получения обобщающей информации по значению какого-либо атрибута. Именно это оправдывает вынесение данных о типе телефона и должности читателя в отдельные таблицы (рис. 3.4). В этом случае название должности или типа телефона указывается один раз.

Если пользователи будут вносить название должности или тип телефона непосредственно в соответствующие атрибуты отношений «ЧИТАТЕЛИ» и «ТЕЛЕФОНЫ» (рис. 3.3), то разработчики не смогут гарантировать, что из БД информация по запросам будет извлекаться корректно. Это связано с тем, что название одной и той же должности оператор может внести с синтаксической ошибкой. В этом случае стандартные алгоритмы обработки запросов пользователей к БД будут интерпретировать одинаковые по сути значения атрибута как различные.

Запрос пользователей на получение обобщающей информации может быть сформулирован так: «вывести на экран рабочие номера телефонов читателей с указанием их фамилий, имен, отчеств, мест работы и должностей», или так: «вывести на экран контактные номера телефонов всех ассистентов с указанием их фамилий, имен и отчеств».

Ввод инкрементных атрибутов «Код» позволил полностью освободиться от необходимости изменения свойств ключевых атрибутов в связи изменениями правил учета читателей в библиотеки (рис. 3.4). Они позволили несколько компенсировать увеличение необходимого объема памяти для реализации БД за счет сокращения места для атрибута, связывающего отношения «ЧИТАТЕЛИ» и «ТЕЛЕФОНЫ» с отношениями «ДОЛЖНОСТИ» и «ТИП ТЕЛЕФОНА» соответственно. Эти отношения можно было бы связать по значениям атрибутов «Наименование должности» и «Наименование типа телефона» вместо атрибутов «Код должности» и «Код типа телефона». Экономия зависит от количества знаков этих наименований, которая заложена в требованиях пользователей. Результат нормализации отношения «ЧИТАТЕЛИ» (рис. 3.4) может быть и другим. Он зависит от требований пользователей, от опыта и взгляда разработчика на процедуру нормализации отношений, необходимых для решения поставленной задачи. В нашем примере мы получили из одного отношения «ЧИТАТЕЛИ» (рис. 3.1, а) четыре отношения: «ЧИТАТЕЛИ», «ТЕЛЕФОНЫ», «ТИПЫ ТЕЛЕФОНОВ» и «ДОЛЖНОСТИ» (рис. 3.4). Отношение «ЧИТАТЕЛИ» ненормализовано. Отношение «ТЕЛЕФОНЫ» в 3НФ. Отношения «ТИПЫ ТЕЛЕФОНОВ» и «ДОЛЖНОСТИ» представлены в 1НФ.

Конечнім результатом нормализации отношений БД «БИБЛИОТЕКА» является диаграмма связей между отношениями (приложение А). Для компактности в ней указываются лишь ключевые атрибуты отношений. Связь между логической и физической моделью удобно показывать после этапа физического проектирования БД.

3.7. КОНТРОЛЬНЫЕ ВОПРОСЫ

  1. Что обеспечивает и гарантирует процедура нормализации?
  2. Какие нормальные формы отношений БД Вам известны?
  3. Когда отношение представлено в первой нормальной форме?
  4. Когда отношение представлено во второй нормальной форме?
  5. Когда отношение представлено в третьей нормальной форме?
  6. Какие нормальные формы отношений используются на практике?
  7. Какие нормальные формы отношений редко используются на практике?
  8. В каких случаях в БД можно оставить отношение, которое не приведено к 1НФ?
  9. Как избавиться от симантической зависимости в связях между отношениями?
  10. Какие существуют способы реализации связей 1:1 и 1:М в БД?

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

Все права защищены.

Вся информация, размещенная на данном веб-сайте, предназначена только для персонального использования и не подлежит дальнейшему воспроизведению и/или распространению в какой-либо форме, иначе как с письменного разрешения Автора.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *