для чего нужна нормализация данных

Нормализация отношений. Шесть нормальных форм

В данной теме я затрону 6 нормальных форм и методы приведения таблиц в эти формы.

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

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

Атрибут — свойство некоторой сущности. Часто называется полем таблицы.

Домен атрибута — множество допустимых значений, которые может принимать атрибут.

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

Отношение — конечное множество кортежей (таблица).

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

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

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

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

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

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

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

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

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

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

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

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

Например, есть таблица «Автомобили»:

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

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

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

Например, дана таблица:

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

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

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

Таблица находится во 2НФ, но не в 3НФ.
В отношении атрибут «Модель» является первичным ключом. Личных телефонов у автомобилей нет, и телефон зависит исключительно от магазина.
Таким образом, в отношении существуют следующие функциональные зависимости: Модель → Магазин, Магазин → Телефон, Модель → Телефон.
Зависимость Модель → Телефон является транзитивной, следовательно, отношение не находится в 3НФ.
В результате разделения исходного отношения получаются два отношения, находящиеся в 3НФ:

Нормальная форма Бойса-Кодда (НФБК) (частная форма третьей нормальной формы)

Определение 3НФ не совсем подходит для следующих отношений:
1) отношение имеет два или более потенциальных ключа;
2) два и более потенциальных ключа являются составными;
3) они пересекаются, т.е. имеют хотя бы один общий атрибут.

Для отношений, имеющих один потенциальный ключ (первичный), НФБК является 3НФ.

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

Предположим, рассматривается отношение, представляющее данные о бронировании стоянки на день:

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

Недостатком данной структуры является то, что, например, по ошибке можно приписать тариф «Бережливый» к бронированию второй стоянки, хотя он может относиться только к первой стоянки.

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

Четвертая нормальная форма

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

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

Такая переменная отношения не соответствует 4НФ, так как существует следующая многозначная зависимость:
<Ресторан>→ <Вид пиццы>
<Ресторан>→

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

Для предотвращения аномалии нужно декомпозировать отношение, разместив независимые факты в разных отношениях. В данном примере следует выполнить декомпозицию на <Ресторан, Вид пиццы>и <Ресторан, Район доставки>.

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

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

Отношения находятся в 5НФ, если оно находится в 4НФ и отсутствуют сложные зависимые соединения между атрибутами.
Если «Атрибут_1» зависит от «Атрибута_2», а «Атрибут_2» в свою очередь зависит от «Атрибута_3», а «Атрибут_3» зависит от «Атрибута_1», то все три атрибута обязательно входят в один кортеж.

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

Например, некоторая таблица содержит три атрибута «Поставщик», «Товар» и «Покупатель». Покупатель_1 приобретает несколько Товаров у Поставщика_1. Покупатель_1 приобрел новый Товар у Поставщика_2. Тогда в силу изложенного выше требования Поставщик_1 обязан поставлять Покупателю_1 тот же самый новый Товар, а Поставщик_2 должен поставлять Покупателю_1, кроме нового Товара, всю номенклатуру Товаров Поставщика_1. Этого на практике не бывает. Покупатель свободен в своем выборе товаров. Поэтому для устранения отмеченного затруднения все три атрибута разносят по разным отношениям (таблицам). После выделения трех новых отношений (Поставщик, Товар и Покупатель) необходимо помнить, что при извлечении информации (например, о покупателях и товарах) необходимо в запросе соединить все три отношения. Любая комбинация соединения двух отношений из трех неминуемо приведет к извлечению неверной (некорректной) информации. Некоторые СУБД снабжены специальными механизмами, устраняющими извлечение недостоверной информации. Тем не менее, следует придерживаться общей рекомендации: структуру базы данных строить таким образом, чтобы избежать применения 4НФ и 5НФ.

Пятая нормальная форма ориентирована на работу с зависимыми соединениями. Указанные зависимые соединения между тремя атрибутами встречаются очень редко. Зависимые соединения между четырьмя, пятью и более атрибутами указать практически невозможно.

Доменно-ключевая нормальная форма

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

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

Любая переменная отношения, находящаяся в ДКНФ, обязательно находится в 5НФ. Однако не любую переменную отношения можно привести к ДКНФ.

Шестая нормальная форма

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

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

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

Переменная отношения «Работники» не находится в 6НФ и может быть подвергнута декомпозиции на переменные отношения «Должности работников» и «Домашние адреса работников».

Источник

Описание основ нормализации базы данных

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Исходный номер КБ: 283878

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

Описание нормализации

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

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

Что такое «непоследовательная зависимость»? Хотя пользователю интуитивно понятно искать в таблице Клиенты адрес конкретного клиента, не имеет смысла искать там зарплату сотрудника, который вызывает этого клиента. Заработная плата сотрудника связана с сотрудником или зависит от него, и поэтому его следует перенаселять в таблицу «Сотрудники». Несовместимые зависимости могут затруднить доступ к данным, так как путь к поиску данных может быть пропущен или нарушен.

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

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

Ниже описаны примеры.

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

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

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

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

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

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

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

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

ИСКЛЮЧЕНИЕ: применение третьей обычной формы, хотя теоретически желательно, не всегда является практическим. Если у вас есть таблица Клиентов и вы хотите устранить все возможные зависимости между полями, необходимо создать отдельные таблицы для городов, почтовых индексов, представителей продаж, классов клиентов и любого другого фактора, который может быть дублирован в нескольких записях. В теории, нормализация стоит очистки. Однако многие небольшие таблицы могут ухудшать производительность или превышать возможности открытого файла и памяти.

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

Другие формы нормализации

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

Нормализация таблицы примеров

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

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

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

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

Вторая нормальная форма: устранение избыточных данных

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

В следующих таблицах демонстрируется вторая нормальная форма:

Третья нормальная форма: устранение данных, не зависящих от ключа

В последнем примере Adv-Room (номер офиса советника) функционально зависит от атрибута Advisor. Решение заключается в том, чтобы переместить этот атрибут из таблицы Студенты в таблицу факультета, как показано ниже:

Источник

Зачем нужна нормализация БД MySQL?

При разработке нормализованной или денормализованной базы размер HDD играет последнюю роль.

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

Нормализуя данные вы перекладываете часть логики проверки данных на СУБД, такие как консистентность и (частично) корректность. То есть уже на уровне вставки/изменения данных вы не можете не заполнить обязательное к заполнению поле или изменить поле в одной таблице, но забыть (или неправильно изменить) в другой (консистентность), вы не можете в поле date внести не дату, а в в поле int не int (корректность) и вы не можете удалить запись, если есть записи, которые на неё ссылаются (консистентность).

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

Поэтому оптимальность где то посередине.

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

Источник

Для чего нужна нормализация данных

Мы уже рассказали, что такое нормализация данных и зачем она нужна при подготовке выборки (Data Preparation) к машинному обучению (Machine Learning) и интеллектуальному анализу данных (Data Mining). Сегодня поговорим о том, как выполняется нормализация данных: читайте в нашем материале о методах и средствах преобразования признаков (Feature Transmormation) на этапе их генерации (Feature Engineering).

Нормализация данных: методы и формулы

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

Графическая интерпретация линейной и нелинейной нормализации

На практике наиболее распространены следующие методы нормализации признаков [1]:

На практике минимакс и Z-масштабирование имеют похожие области применимости и часто взаимозаменяемы. Однако, при вычислении расстояний между точками или векторами в большинстве случае используется Z-масштабирование. А минимакс полезен для визуализации, например, чтобы перенести признаки, кодирующие цвет пикселя, в диапазон [0..255] [2].

Как нормализовать данные для машинного обучения и Data Mining

Чтобы выполнить нормализацию данных, нужно точно знать пределы изменения значений признаков: минимальное и максимальное теоретически возможные значения. Этим показателям будут соответствовать границы интервала нормализации. Когда точно установить пределы изменения переменных невозможно, они задаются с учетом минимальных и максимальных значений в имеющейся выборке данных [3].

На практике data scientist нормализует данные с помощью уже готовых функций интегрированных сред для статистического анализа, например, IBM SPSS, SAS или специальных библиотек: Scikit-learn, Auto-sklearn, pandas и т.д. Кроме того, аналитик данных может написать собственный код на языке R или Python для почти любой операции Data Preparation [4].

Выборку нужно подготовить, чтобы моделирование прошло нормально

Подробно о том, как нормализовать данные и другие аспекты Data Preparation в нашем новом образовательном курсе для аналитиков Big Data в Москве: подготовка данных для Data Mining. Присоединяйтесь!

Источник