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

Нормализация отношений. Первая и вторая нормальные формы

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

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

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

Используемые термины

Атрибут — свойство некоторой сущности. Часто называется полем таблицы.
Домен атрибута — множество допустимых значений, которые может принимать атрибут.
Кортеж — конечное множество взаимосвязанных допустимых значений атрибутов, которые вместе описывают некоторую сущность (строка таблицы).
Отношение — конечное множество кортежей (таблица).
Схема отношения — конечное множество атрибутов, определяющих некоторую сущность. Иными словами, это структура таблицы, состоящей из конкретного набора полей.
Проекция — отношение, полученное из заданного путём удаления и (или) перестановки некоторых атрибутов.
Функциональная зависимость между атрибутами (множествами атрибутов) X и Y означает, что для любого допустимого набора кортежей в данном отношении: если два кортежа совпадают по значению X, то они совпадают по значению Y. Например, если значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom. Обозначение: -> .

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

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

Будем называть исходное отношение основным, а значение неатомарного атрибута — подчинённым.

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

Следует пояснить сказанное на примере. Рассмотрим отношение, имеющее атрибуты «Код сотрудника», «ФИО», «Должность», «Проекты». Очевидно, что один сотрудник может работать над несколькими проектами. Предположим, что проект описывается идентификатором, названием и датой сдачи.

Код сотрудника	ФИО	Должность	Проекты
1	Иванов Иван Иванович	Программист	ID: 123; Название: Система управления паровым котлом; Дата сдачи: 30.09.2011 ID: 231; Название: ПС для контроля и оповещения о превышениях ПДК различных газов в помещении; Дата сдачи: 30.11.2011 ID: 321; Название: Модуль распознавания лиц для защитной системы; Дата сдачи: 01.12.2011

Легко заметить, что не все атрибуты этого отношения атомарны (неделимы). В частности, атрибут «Проекты» можно разделить на три более простых атрибута: «Код проекта», «Название», «Дата сдачи», а значение этого атрибута для сотрудника Иван Иванович Иванов содержит несколько кортежей — информацию о трёх проектах.

Примечание: с некоторой точки зрения атрибут «ФИО» можно также считать неатомарным и в таком случае его также следует разделить на более простые, как «Фамилия», «Имя», «Отчество».

1. Создать новое отношение, схема которого будет получена путём слияния основной и подчинённой схем исходного отношения в одну.
2. Для каждого кортежа исходного отношения включить в новое столько строк, сколько кортежей содержится в подчинённом отношении этого кортежа.
3. Заполнить значения атрибутов нового отношения, соответствующих атрибутам подчинённого отношения.
4. Заполнить строки нового отношения значениями атомарных атрибутов исходного.

Результат будет выглядеть так:

Код сотрудника	ФИО	Должность	Код проекта	Название	Дата сдачи
1	Иванов Иван Иванович	Программист	123	Система управления паровым котлом	30.09.2011
1	Иванов Иван Иванович	Программист	231	ПС для контроля и оповещения о превышениях ПДК различных газов в помещении	30.11.2011
1	Иванов Иван Иванович	Программист	321	Модуль распознавания лиц для защитной системы	01.12.2011

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

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

Пусть исходное отношение содержит информацию о поставке некоторых товаров и их поставщиках.

Код поставщика	Город	Статус города	Код товара	Количество
1	Москва	20	1	300
1	Москва	20	2	400
1	Москва	20	3	100
2	Ярославль	10	4	200
3	Ставрополь	30	5	300
3	Ставрополь	30	6	400
4	Псков	15	7	100

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

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

Код поставщика	Код товара	Количество
1	1	300
1	2	400
1	3	100
2	4	200
3	5	300
3	6	400
4	7	100

Первому отношению теперь соответствуют следующие функциональные зависимости:
->

Код поставщика	Город	Статус города
1	Москва	20
2	Ярославль	10
3	Ставрополь	30
4	Псков	15

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

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

Литература

Для более глубокого и основательного изучения рассмотренной темы, рекомендуется книга «Введение в системы баз данных» Криса Дж. Дейта, на основе материалов которой и была написана данная статья.

• реляционные базы данных
• нормализация отношений
• нормальные формы
• первая нормальная форма
• 1НФ
• вторая нормальная форма
• 2НФ

Нормализация. Понятие и необходимость применения. Аномалии модификации. Примеры

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

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

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

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

• первая нормальная форма (1НФ);
• вторая нормальная форма (2НФ);
• третья нормальная форма (3НФ);
• нормальная форма Бойса-Кодда (НФБК);
• четвертая нормальная форма (4НФ);
• пятая нормальная форма (5НФ).

База данных считается правильно спроектированной (оптимальной или приближенной к оптимальной), если она отвечает требованиям нормальных форм. Не обязательно применять все 5 нормальных форм. Если количество атрибутов (столбцов) в базе данных небольшое, то достаточным есть применение первых трех нормальных форм. Взаимосвязь нормальных форм изображена на рисунке 1.

Рисунок 1. Взаимосвязь нормальных форм

2. Понятие избыточности данных. Пример

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

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

Рисунок 2. Структура таблицы базы данных учебного заведения

Для примера в таблицу внесены следующие данные (фрагмент таблицы).

Рисунок 3. Таблица с заполненными данными. Избыточность данных

В вышеприведенной таблице избыточность данных проявляется в следующих определениях:

• для идентификации студента (столбец Студент ) группа данных «Номер зачетки» — «Адрес» повторяется. Достаточно указать только поле Студент , а данные о номере зачетной книжки и адрес держать в другой связанной таблице. Тогда данные полей «Номер зачетки» и «Адрес» не будут повторяться лишний раз. Как следствие, размер таблиц базы данных будет меньше;
• лишним есть указание кафедры для преподавателя, поскольку преподаватель может быть закреплен только за одной кафедрой. Название кафедры и его связь с преподавателем целесообразно сохранять в другой (связанной) таблице.

3. Аномалия вставки. Пример

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

Пример. Задана следующая база данных, которая основана на одной таблице. В таблице определяется информация о преподавателях ( Преподаватель , Дисциплина , Кафедра ), студентах ( Студент , Номер зачетки , Адрес ), успеваемость студентов ( Оценка ).

Рисунок 4. Таблица с данными об успеваемости в учебном заведении

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

Рисунок 5. Пример аномалии вставки. Добавление преподавателя в базу данных требует указания информации о студенте

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

4. Аномалия редактирования. Пример

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

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

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

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

Рисунок 6. Аномалия редактирования. Редактирование одних и тех же данных в одной ячейке требует изменения этих данных в других ячейках

5. Аномалия удаления. Пример

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

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

Рисунок 7. Аномалия удаления. При удалении информации об оценивании студента теряется информация о преподавателе кафедры

Связанные темы

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:М в БД?

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

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

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

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

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

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

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

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

Избыточность данных в БД относится к нежелательным явлениям,
поскольку ведет к увеличению объема памяти, необходимого для
физического хранения отношений. Избыточность вызывается, прежде
всего, дублированием данных.
Вот характерный пример отношения (табл. 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.

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

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