Что такое нормализация данных

Умная нормализация данных

Во-первых, в подавляющем большинстве книг, интернет-ресурсов и уроков по Data Science нюансы, изъяны разных типов нормализации данных и их причины либо не рассматриваются вообще, либо упоминаются лишь мельком и без раскрытия сути.

Во-вторых, имеет место «слепое» использование, например, стандартизации для наборов с большим количеством признаков — “чтобы для всех одинаково”. Особенно у новичков (сам был таким же). На первый взгляд ничего страшного. Но при детальном рассмотрении может выясниться, что какие-то признаки были неосознанно поставлены в привилегированное положение и стали влиять на результат значительно сильнее, чем должны.

И, в-третьих, мне всегда хотелось получить универсальный метод учитывающий проблемные места.

Повторение — мать учения

Нормализация — это преобразование данных к неким безразмерным единицам. Иногда — в рамках заданного диапазона, например, [0..1] или [-1..1]. Иногда — с какими-то заданным свойством, как, например, стандартным отклонением равным 1.

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

Аналитически любая нормализация сводится к формуле

где — текущее значение,
— величина смещения значений,
— величина интервала, который будет преобразован к “единице”

По сути всё сводится к тому, что исходный набор значений сперва смещается, а потом масштабируется.

Минимакс (MinMax). Цель — преобразовать исходный набор в диапазон [0..1]. Для него:
= , минимальное значение исходных данных.
= — , т.е. за “единичный” интервал берется исходный диапазон значений.

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

Для других методов всё аналогично, но со своими особенностями.

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

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

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

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

Шаг 1 — определяем смещение

Чаще всего данные центрируют — т.е. определяют, значение, которое станет новым 0 и “сдвигают” данные относительно него.

Что лучше взять за центр? Некоего «типичного представителя» Ваших данных. Так при использовании стандартизации используется среднее арифметическое значение.

Здесь проявляется проблема № 1 — различные типы распределений не позволяют применять к ним методы, созданные для нормального распределения.

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

Вот, например, различия между медианой и средним арифметическим значением для экспоненциального распределения.

А вот так выглядят эти различия при добавлении выброса:

В отличии от среднего значения медиана практически не чувствительна к выбросам и асимметрии распределения. Поэтому её оптимально использовать как “нулевое” значение при центрировании.

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

Шаг 2 — масштабируем

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

Степень возможного влияния признаков определяется величиной их диапазонов после масштабирования. Если оба признака распределены в одинаковых интервалах, например, [-1..1], то и влиять они могут одинаково. Если же изначально один из признаков лежит в диапазоне [-1..1], а второй — в [-1..100], то очевидно, что изменения второго могут оказывать существенно большее влияние. А значит он будет в привилегированном положении по сравнению с первым.

Стандартное отклонение

Вернёмся к примеру стандартизации. В её случае новый диапазон определяется величиной стандартного отклонения. Чем оно меньше, тем диапазон станет “шире”.

Посмотрим на гипотетические распределения различных признаков с одинаковыми начальными диапазонами (так будет нагляднее):

Для второго признака (бимодальное распределение) стандартное отклонение будет больше, чем у первого.

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

Итог — стандартное отклонение не удовлетворяет начальным требованиям по одинаковому влиянию признаков (величине интервала). Даже не говоря о том, что и наличие выбросов может исказить “истинную” величину стандартного отклонения.

Межквартильный интервал

Другим часто используемым кандидатом является разница между 75-м и 25-м процентилями данных — межквартильный интервал. Т.е. интервал, в котором находятся “центральные” 50% данных набора. Эта величина уже устойчива к выбросам и не зависит от “нормальности” распределения наличия/отсутствия асимметрии.

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

Проблема № 2 — большие “хвосты” распределений признаков.

Пример — два признака с нормальным и экспоненциальным распределениями. Интервалы значений одинаковы

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

В итоге интервал у признака с экспоненциальным распределением из-за большого “хвоста” стал больше. А, следовательно, и сам признак стал “влиятельнее”.

Размах значений

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

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

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

После нормализации по размаху

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

Работаем с выбросами

Решением проблемы влияния выбросов при использовании размаха является его замена на интервал, в котором будут располагаться “не-выбросы”. И дальше — масштабировать по этому интервалу.

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

Пожалуй, самым массово применяемым методом автоматического определения выбросов является межквартильный метод. Его суть заключается в том, что выбросами “назначаются” данные, которые более чем в 1,5 межквартильных диапазонах (IQR) ниже первого квартиля или выше третьего квартиля.*

* — в некоторых случаях (очень большие выборки и др.) вместо 1,5 используют значение 3 — для определения только экстремальных выбросов.

Схематично метод изображен на рисунке снизу.

Вроде бы все отлично — наконец-то есть инструмент, и можно приступать к работе.

Но и здесь есть своя ложка дёгтя. В случае наличия длинных хвостов (как, например, при экспоненциальном распределении) слишком много данных попадают в такие “выбросы” — иногда достигая значений более 7%. Избирательное использование других коэффициентов (3 * IQR) опять приводит к необходимости ручного вмешательства — не для каждого признака есть такая необходимость. Их потребуется по отдельности изучать и подбирать коэффициенты. Т.е. универсальный инструмент опять не получается.

Ещё одной существенной проблемой является то, что этот метод симметричный. Полученный “интервал доверия” (1,5 * IQR) одинаков как для малых, так и для больших значений признака. Если распределение не симметричное, то многие аномалии-выбросы с “короткой” стороны просто будут скрыты этим интервалом.

Скорректированный интервал

Красивое решение этих проблем предложили Миа Хаберт и Елена Вандервирен (Mia Hubert and Ellen Vandervieren) в 2007 г. в статье “An Adjusted Boxplot for Skewed Distributions”.

Их идея заключается в вычислении границ “интервал доверия” с учетом асимметрии распределения, но чтобы для симметричного случая он был равен всё тому же 1,5 * IQR.

Для определения некоего “коэффициента асимметрии” они использовали функцию medcouple (MC), которая определяется так:

Поиск подходящей формулы для определения границ “интервала доверия” производился с целью сделать долю, приходящуюся на выбросы, не превышающей такую же, как у нормального распределения и 1,5 * IQR — приблизительно 0,7%

В конечном итоге они получили такой результат:

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

Универсальный инструмент

Теперь, объединяя все найденные плюсы и учитывая проблемы, мы получаем оптимальное решение:

1. Центрирование, если оно требуется, производить по медиане.
2. Масштабировать набор данных по величине скорректированного интервала.
3. (Опционально) — если центрирование не требуется, то смещать масштабированные данные так, чтобы границы скорректированного интервала приходились на [0..1]

Назовем его методом… скорректированного интервала — по названию статьи Mia Hubert и Ellen Vandervieren

Теперь сравним результаты обычных методов с новым. Для примера возьмем уже использовавшиеся выше три распределения с добавлением выбросов.

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

Ситуация № 1 — данные необходимо центрировать. Это используется в кластеризации и многих методах машинного обучения. Особенно, когда необходимо определять меру “близости” объектов.

Робастная нормализация (по межквартильному интервалу):

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

Ситуация № 2 — данные необходимо вписать в заданный интервал. Обычно это [0..1]. Это используется, например, при подготовке данных для входов нейронной сети.

MinMax (по размаху):

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

То, что только “нормальные” данные попадают в единичный диапазон [0..1], а выбросы не удаляются, но пропорционально выносятся за его пределы — это крайне полезное свойство, которое сильно поможет при кластеризации объектов со смешанными признаками, как числовыми, так и категорийными. Подробно об этом я напишу в другой статье.

Напоследок, для возможности пощупать руками этот метод, Вы можете попробовать демонстрационный класс AdjustedScaler из моей библиотеки AdjDataTools.

Он не оптимизирован под работу с очень большим объемом данных и работает только с pandas DataFrame, но для пробы, экспериментов или даже заготовки под что-то более серьезное вполне подойдет. Пробуйте.

• Нормализация
• Стандартизация
• Data Science
• Центрирование
• Масштабирование
• Adjusted boxplot
• Python
• AdjustedScaler

• Python
• Алгоритмы
• Машинное обучение
• Искусственный интеллект
• Data Engineering

Data Preparation: полет нормальный – что такое нормализация данных и зачем она нужна

Нормализация данных – это одна из операций преобразования признаков (Feature Transformation), которая выполняется при их генерации (Feature Engineering) на этапе подготовки данных (Data Preparation). В этой статье мы расскажем, почему необходимо нормализовать значения переменных перед тем, как запустить моделирование для интеллектуального анализа данных (Data Mining).

Что такое нормализация данных и чем она отличается от нормировки и нормирования

В случае машинного обучения (Machine Learning), нормализация – это процедура предобработки входной информации (обучающих, тестовых и валидационных выборок, а также реальных данных), при которой значения признаков во входном векторе приводятся к некоторому заданному диапазону, например, [0…1] или [-1…1] [1].

Следует отличать понятия нормализации, нормировки и нормирования.

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

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

Нормирование – это процесс установления предельно допустимых или оптимальных нормативных значений в прикладных сферах деятельности, например, нормирование труда. Как правило, нормы разрабатываются по результатам исследовательских, проектных или научных работ, а также на основе экспертных оценок [3].

Зачем нормализовать датасет для Data Mining и Machine Learning

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

Будучи разными по физическому смыслу, данные сильно различаются между собой по абсолютным величинам [4]. Работа аналитических моделей машинного обучения (нейронных сетей, карт Кохонена и т.д.) с такими показателями окажется некорректной: дисбаланс между значениями признаков может вызвать неустойчивость работы модели, ухудшить результаты обучения и замедлить процесс моделирования. В частности, параметрические методы машинного обучения (нейронные сети, растущие деревья) обычно требуют симметричного и унимодального распределения данных. Популярный метод ближайших соседей, часто используемый в задачах классификации и иногда в регрессионном анализе, также чувствителен к диапазону изменений входных переменных [5].

После нормализации все числовые значения входных признаков будут приведены к одинаковой области их изменения – некоторому узкому диапазону. Это позволит свести их вместе в одной модели Machine Learning [4] и обеспечит корректную работу вычислительных алгоритмов [1].

Практическим приемам Feature Transformation посвящена наша следующая статья, где мы рассказываем, как именно выполняется нормализация данных: формулы, методы и средства. Все эти и другие вопросы Data Preparation рассматриваются в нашем новом курсе обучения для аналитиков Big Data: подготовка данных для Data Mining. Оставайтесь с нами!

Источники

1. https://wiki.loginom.ru/articles/normalization.html
2. http://molbiol.ru/forums/lofiversion/index.php/t460759.html
3. https://btimes.ru/dictionary/normirovanie
4. https://neuronus.com/theory/nn/925-sposoby-normalizatsii-peremennykh.html
5. https://habr.com/ru/company/ods/blog/325422

Что такое нормализация данных?

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

Что такое нормализация данных?

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

Преимущества нормализации данных

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

Уменьшение размера базы данных

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

Упрощение запросов

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

Облегчение обслуживания

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

Улучшение производительности

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

Кому нужна нормализация данных?

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

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

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

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

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

• Все имена записаны в такой форме: Дурсли, Вернон.
• Все номера телефонов записываются в такой форме: 530-000-0000.
• Все адреса пишутся в такой форме: 4, Private Drive, San Francisco.

Попробуйте no-code платформу AppMaster

AppMaster поможет создать любое веб, мобильное или серверное приложение в 10 раз быстрее и 3 раза дешевле

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

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

Первая нормальная форма (1НФ)

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

Эта база данных нарушает первую нормальную форму двумя способами:

• В одном поле два значения, так как профессор Митчелл преподает два курса;
• Имеются два поля, хранящие схожую информацию: Professor ID и Professor Name оба предоставляют информацию о личности профессора.

Чтобы нормализовать нашу базу данных, нам нужно разделить ее на две части:

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

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

Вторая нормальная форма (2НФ)

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

• Каждая база данных должна иметь только один первичный ключ
• Все непервичные ключи должны полностью зависеть от первичного ключа.

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

У нас есть база данных Professor Birthday and Department, которая выглядит следующим образом:

Профессор Имя	День рождения	Кафедра
Гарри Грей	Июль, 1	Литература
Виктория Уайт	Сентябрь, 19	Литература
Павел Саул	Март, 1	Литература
Джеймс Смит	Июнь, 5	Наука

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

Попробуйте no-code платформу AppMaster

AppMaster поможет создать любое веб, мобильное или серверное приложение в 10 раз быстрее и 3 раза дешевле

Чтобы нормализовать эту базу данных, нам снова нужно разделить ее на две части:

• База данных «День рождения профессора», которая включает два поля: имя профессора и день рождения
• База данных «Кафедра профессора», которая включает два поля: имя профессора и кафедра

Третья нормальная форма (3НФ)

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

Приведем еще один пример. У нас есть база данных заказов:

ID заказа	Дата заказа	Идентификатор клиента	Почтовый индекс клиента
D001	01/3/2022	C001	97438
D002	06/15/2022	C002	08638

В этой базе данных не соблюдается третья нормальная форма, потому что у нас есть первичный ключ, ID заказа. Дата заказа и ID клиента полностью зависят от него, но Zip Code клиента зависит от ID клиента, который не является первичным ключом. Как мы уже говорили, для нормализации этой базы данных в соответствии с третьей нормальной формой нам нужно создать вторую базу данных Customer Zip Code Database, которая свяжет каждый ID клиента с его Zip Code.

Что такое SQL-ключи?

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

Существуют наиболее важные типы SQL-ключей:

• Суперключ: суперключ — это комбинация одного или нескольких столбцов в таблице, которая однозначно идентифицирует одну строку в таблице.
• Внешний ключ: он важен, когда у вас есть две связанные базы данных. В примере, который мы привели для второй нормальной формы, у нас было две нормализованные базы данных, которые «делили» поле Professor ID. Идентификатор профессора — это внешний ключ, который служит для того, чтобы сообщить базам данных, что они связаны.
• Первичный ключ: это разновидность ключа SQL . Как мы уже говорили, согласно первой нормальной форме, в каждой таблице не может быть более одного первичного ключа, и все поля должны напрямую и полностью зависеть от него.

Заключение

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

Нормализация данных — это также пример того, как управление базами данных может стать чрезвычайно сложным. По этой причине важно полагаться на инструменты, которые могут максимально упростить работу. В этой связи стоит порекомендовать no-code инструмент AppMaster s, который позволяет создавать приложения и управлять их базами данных без написания кода. Возможно, вам все же придется выучить правила нормализации данных, но применять их станет намного проще!

Что такое нормализация базы данных?

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

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

Что такое нормализация базы данных?

Нормализация базы данных — это метод создания таблиц БД со столбцами и ключами путем разделения (или декомпозиции) таблицы большего размера на небольшие логические единицы. В данном методе учитываются требования, предъявляемые к среде БД.

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

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

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

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

Необязательно выполнять полную нормализацию БД. Однако она гарантирует полноценно функционирующую информационную среду. Этот метод:

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

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

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

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

Так выглядит таблица без нормализации:

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

1. Аномалия вставки. При добавлении нового «Сотрудника» (employee) в «Отдел» (sector) Finance, обязательно указывается его «Руководитель» (manager). Иначе вы не сможете вставить данные в таблицу.
2. Аномалия обновления. Когда сотрудник переходит в другой отдел, поле «Руководитель» содержит ошибочные данные. К примеру, Джейкоб (Jacob) перешел в отдел Finance, но его руководителем по-прежнему показывается Адам (Adam).
3. Аномалия удаления. Если Джошуа (Joshua) решит уволиться из компании, то при удалении строки с его записью потеряется информация о том, что отдел Finance вообще существует.

Для устранения подобных аномалий используется нормализация базы данных.

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

Простейшие понятия, используемые в нормализации базы данных:

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

Нормальные формы базы данных

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

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

Аномалии избыточности

Ненормализованная (нулевая) форма (UNF)

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

Первая нормальная форма (1NF)

Разбиваются повторяющиеся и сложные значения; все экземпляры становятся атомарными

Вторая нормальная форма (2NF)

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

Третья нормальная форма (3NF)

Транзитивные зависимости разбиваются на новые таблицы. Не ключевые атрибуты зависят от первичного ключа

Нормальная форма Бойса-Кода (BCNF)

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

Четвертая нормальная форма (4NF)

Удаляются многозначные зависимости

Пятая нормальная форма (5NF)

Удаляются JOIN-зависимости (зависимости соединения)

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

Что такое Ключ?

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

• суперключ (Super Key) — набор признаков, которые уникально определяют каждую запись в таблице;
• потенциальный ключ (Candidate Key) — выбирается из набора суперключей с минимальным количеством полей;
• первичный ключ (Primary Key) — самый подходящий кандидат из набора потенциальных ключей; служит первичным ключом таблицы;
• внешний ключ (Foreign Key) — первичный ключ другой таблицы;
• составной ключ (Composite Key) — уникальный ключ, образованный двумя и более атрибутами, каждый из которых по отдельности не является ключом.

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

Например, в следующей структуре базы данных:

Примерами суперключей являются:

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

Потенциальные ключи выбираются из набора суперключей с минимальным количеством полей. В нашем примере это:

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

Функциональные зависимости базы данных

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

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

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

Примеры нормализации базы данных. Как нормализовать базу данных?

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

Пример ненормализованной базы данных

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

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

Шаг 1: Первая нормальная форма (1NF)

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

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

Доработанная таблица соответствует первой нормальной форме.

Шаг 2: Вторая нормальная форма (2NF)

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

Чтобы таблица соответствовала критериям этой формы, ее необходимо разделить на 2 части:

Manager (managerID, managerName, area)

Employee (employeeID, employeeName, managerID, sectorID, sectorName)

Итоговая таблица во второй нормальной форме представляет собой 2 таблицы без частичных зависимостей.

Шаг 3: третья нормальная форма (3NF)

Третья нормальная форма разделяет любые транзитивные функциональные зависимости. В нашем примере транзитивная зависимость есть у таблицы Employee; она разбивается на 2 новых таблицы:

Employee (employeeID, employeeName, managerID, sectorID)

Sector (sectorID, sectorName)

Теперь таблица соответствует третьей нормальной форме с тремя взаимосвязями. Конечная структура выглядит так:

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

Заключение

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

Если же вы хотите повысить производительность доступа к данным, то воспользуйтесь денормализацией БД.

А если вы испытываете трудности с нормализацией базы данных, то рассмотрите возможность перехода на другой тип БД.