Как сделать нормализацию базы данных

Добавил пользователь Дмитрий К.
Обновлено: 05.10.2024

Нормализация представляет процесс разделения данных по отдельным связанным таблицам. Нормализация устраняет избыточность данных (data redundancy) и тем самым избежать нарушения целостности данных при их изменении, то есть избежать аномалий изменения (update anomaly).

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

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

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

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

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

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

Нормальная форма Бойса-Кодда (BCNF) является немного более строгой версией третьей нормальной формы.

Четвертая нормальная форма (4NF) применяется для устранения многозначных зависимостей (multivalued dependencies) - таких зависимостей, где столбец с первичным ключом имеет связь один-ко-многим со столбцом, который не является ключом. Эта нормальная форма устраняет некорректные отношения многие-ко-многим.

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

Шестая нормальная форма (domain key normal form / 6NF). Каждое ограничение в связях между таблицами должно зависеть только от ограничений ключа и ограничений домена, где домен представляет набор допустимых значений для столбца. Эта форма предотвращает добавление недопустимых данных путем установки ограничения на уровне отношений между таблицами, но не на уровне таблиц или столбцов. Данная форма, как правило, не применима на уровне СУБД, в том числе и в SQL Server.

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

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

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

Course	Teacher	Position
Математика	Смит	Профессор
Алгориты	Адамс	Ассистент
JavaScript	Адамс	Ассистент

Здесь атрибут Teacher функционально зависит от атрибута Course (Course → Teacher). То есть зная название курса, мы можем определить его преподавателя. И в этом случае можно говорить, что между атрибутами Course и Teacher есть связь 1:1, а между Teacher и Course связь 1:N, так как есть несколько курсов, которые может вести один преподаватель. При этом атрибут Course функционально не зависит от атрибута Teacher.

Кроме того, здесь можно проследить еще две функциональных зависимости. В частности, атрибут Position зависит от атрибута Teacher (Teacher → Position). Зная имя преподавателя, мы можем определить его должность.

И также атрибут Position функционально зависит от атрибута Course - зная название курса, мы можем сказать должность преподавателя.

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

Например, в данном случае мы можем взять в качестве первичного ключа название курса с учетом, что курсы могут иметь только уникальные названия. Однако должность преподавателя в данном случае будет зависеть сразу от двух атрибутов - от Course и Teacher. И подобные зависимости могут свидетельствовать о том, что база данных и конкретно таблица курсов имеет недостатки в проектировании и может быть источником аномалий обновления.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.

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

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

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

Основным правилом первой формы является необходимость неделимости значения в каждом поле (столбце) строки – атомарность значений.

Рассмотрим таблицы сотрудников и телефонных линий.

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

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

Помимо атомарности к первой нормальной форме относятся следующие правила:

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

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

Условием этой формы является отсутствие зависимости неключевых полей от части составного ключа.

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

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

3NF схожа по логике с 2NF, но с некоторым отличием. Если 2 форма ликвидирует зависимости неключевых полей от части ключа, то третья нормальная форма исключает зависимость неключевых полей от других неключевых полей.

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

Денормализация базы данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: