Sql третья нормальная форма
Третья нормальная форма предполагает, что каждый столбец, не являющийся ключом, должен зависеть только от столбца, который является ключом, то есть должна отсутствовать транзитивная функциональная зависимость (transitive functional dependency)
Транзитивная функциональная зависимость выражается следующим образом: А → В и В → С. То есть атрибут С транзитивно зависит от атрибута А, если атрибут С зависит от атрибута В, а атрибут В зависит от атрибута А (при условии, что атрибут А функционально не зависит ни от атрибута В, ни от атрибута С).
Если столбец зависит не только от первичного ключа, то данный столбец находится не в той таблице, в которой он должен находиться, либо же является производным от других столбцов.
Для нормализации из исходной таблицы те атрибуты, которые находятся в транзитивной зависимости от ключа, выносятся в отдельную таблицу с копией того атрибута, от которого они непосредственно зависят.
При применении третьей нормальной формы таблица должна находиться во второй нормальной форме. 3NF позволяет значительно снизить избыточность данных.
Для примера возьмем сформированную в прошлой теме таблицу Courses, которая содержит информацию о курсах и которая находится во второй нормальной форме:
| CourseId | Course | TeacherId | Teacher |
| 1 | Математика | 1 | Смит |
| 2 | JavaScript | 2 | Адамс |
| 3 | Алгоритмы | 2 | Адамс |
Какие функциональные зависимости здесь можно выделить:
CourseId → Course, TeacherId, Teacher
Course → CourseId, TeacherId, Teacher
Вторая зависимость фактически аналогична первой и говорит о том, что атрибут Course является потенциальным ключом.
Третья зависимость говорит о том, что, зная идентификатор преподавателя, мы можем узнать его фамилию и должность. То есть через атрибут TeacherId атрибут Teacher зависит от CourseId (CourseId→ TeacherId и TeacherId → Teacher). И в данном случае мы можем говорить о транзитивной зависимости Teacher, Position от CourseId.
Для нормализации необходимо вынести в отдельную таблицу атрибуты TeacherId и Teacher. Для этого пусть будет отдельная таблица Teachers:
| TeacherId | Teacher |
| 1 | Смит |
| 2 | Адамс |
А таблица Courses сократится следующим образом:
Описание основ нормализации базы данных
Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.
Исходный номер КБ: 283878
В этой статье объясняется терминология нормализации баз данных для начинающих. Базовое понимание этой терминологии полезно при обсуждении разработки реляционной базы данных.
Описание нормализации
Нормализация — это процесс организации данных в базе данных. Это включает создание таблиц и установление связей между этими таблицами в соответствии с правилами, предназначенными как для защиты данных, так и для того, чтобы сделать базу данных более гибкой за счет устранения избыточности и непоследовательной зависимости.
Избыточные данные пустая трата дискового пространства и создает проблемы с обслуживанием. Если данные, которые существуют в нескольких местах, должны быть изменены, данные должны быть изменены точно так же во всех расположениях. Изменение адреса клиента гораздо проще реализовать, если эти данные хранятся только в таблице Клиентов и нигде в базе данных.
Что такое «непоследовательная зависимость»? Хотя пользователю интуитивно понятно искать в таблице Клиенты адрес конкретного клиента, не имеет смысла искать там зарплату сотрудника, который вызывает этого клиента. Заработная плата сотрудника связана с сотрудником или зависит от него, и поэтому его следует перенаселять в таблицу «Сотрудники». Несовместимые зависимости могут затруднить доступ к данным, так как путь к поиску данных может быть пропущен или нарушен.
Существует несколько правил нормализации базы данных. Каждое правило называется «нормальной формой». Если первое правило соблюдается, база данных, как сообщается, находится в «первой нормальной форме». Если соблюдаются первые три правила, база данных рассматривается как «третья нормальная форма». Хотя возможны другие уровни нормализации, третья нормальная форма считается наивысшим уровнем, необходимым для большинства приложений.
Как и во многих формальных правилах и спецификациях, сценарии реального мира не всегда позволяют обеспечить идеальное соответствие требованиям. Как правило, для нормализации требуются дополнительные таблицы, и некоторые клиенты считают это громоздким. Если вы решите нарушить одно из первых трех правил нормализации, убедитесь, что ваше приложение предвосхищает возможные проблемы, такие как избыточные данные и несовместимые зависимости.
Ниже описаны примеры.
Первая нормальная форма
Не используйте несколько полей в одной таблице для хранения аналогичных данных. Например, для отслеживания элемента инвентаризации, который может приходить из двух возможных источников, запись инвентаризации может содержать поля для кода поставщика 1 и кода поставщика 2.
Что происходит при добавлении третьего поставщика? Добавление поля не является ответом; она требует изменений программы и таблицы и не позволяет плавно разместить динамическое число поставщиков. Вместо этого поместите всю информацию поставщика в отдельную таблицу под названием Поставщики, а затем увязыв инвентаризацию с поставщиками с ключом номера элемента, или поставщики для инвентаризации с ключом кода поставщика.
Вторая нормальная форма
Записи не должны зависеть от чего-либо, кроме основного ключа таблицы (сложный ключ, если это необходимо). Например, рассмотрим адрес клиента в системе учета. Адрес необходим в таблице Клиенты, а также таблицами «Заказы», «Доставка», «Счета-фактуры», «Отчеты о счетах» и «Коллекции». Вместо того, чтобы хранить адрес клиента как отдельную запись в каждой из этих таблиц, храните его в одном месте, в таблице Клиенты или в отдельной таблице Адресов.
Третья нормальная форма
Значения в записи, которая не входит в ключ этой записи, не относятся к таблице. В общем, в любое время содержимое группы полей может применяться к более чем одной записи в таблице, рассмотрите возможность размещения этих полей в отдельной таблице.
Например, в таблице набора сотрудников может быть включено имя и адрес университета кандидата. Но для групповой рассылки необходим полный список университетов. Если сведения о университетах хранятся в таблице Candidates, нет возможности перечислять университеты без текущих кандидатов. Создайте отдельную таблицу университетов и привяжете ее к таблице Кандидаты с ключом кода университета.
ИСКЛЮЧЕНИЕ: применение третьей обычной формы, хотя теоретически желательно, не всегда является практическим. Если у вас есть таблица Клиентов и вы хотите устранить все возможные зависимости между полями, необходимо создать отдельные таблицы для городов, почтовых индексов, представителей продаж, классов клиентов и любого другого фактора, который может быть дублирован в нескольких записях. В теории, нормализация стоит очистки. Однако многие небольшие таблицы могут ухудшать производительность или превышать возможности открытого файла и памяти.
Возможно, более целесообразно применять третью нормальную форму только к данным, которые часто меняются. Если остаются некоторые зависимые поля, спроектировать приложение, чтобы потребовать от пользователя проверить все связанные поля при их смене.
Другие формы нормализации
Четвертая нормальная форма, также называемая «Обычная форма Бойс Кодд» (BCNF), и пятая нормальная форма существуют, но редко рассматриваются в практическом дизайне. Игнорирование этих правил может привести к менее совершенному дизайну базы данных, но не должно влиять на функциональные возможности.
Нормализация таблицы примеров
Эти действия демонстрируют процесс нормализации фиктивной студенческой таблицы.
| Student # | Советник | Adv-Room | Класс 1 | Class2 | Class3 |
|---|---|---|---|---|---|
| 1022 | Джонс | 412 | 101-07 | 143-01 | 159-02 |
| 4123 | Smith | 216 | 101-07 | 143-01 | 179-04 |
Первая нормальная форма: нет повторяюющихся групп
Таблицы должны иметь только два измерения. Так как у одного учащегося несколько классов, эти классы должны быть указаны в отдельной таблице. Поля Class1, Class2 и Class3 в вышеуказанных записях указывают на проблемы с дизайном.
Таблицы часто используют третье измерение, но таблицы не должны. Другой способ взглянуть на эту проблему — это отношение между одним и большим количеством, не помещая одну сторону и множество сторон в одну таблицу. Вместо этого создайте другую таблицу в первой обычной форме, устранив группу повторяющихся (Класс#), как показано ниже:
| Student # | Советник | Adv-Room | Класс # |
|---|---|---|---|
| 1022 | Джонс | 412 | 101-07 |
| 1022 | Джонс | 412 | 143-01 |
| 1022 | Джонс | 412 | 159-02 |
| 4123 | Smith | 216 | 101-07 |
| 4123 | Smith | 216 | 143-01 |
| 4123 | Smith | 216 | 179-04 |
Вторая нормальная форма: устранение избыточных данных
Обратите внимание на несколько значений Класса#для каждого значения Student# в вышеуказанной таблице. Класс# функционально не зависит от student# (основной ключ), поэтому эта связь не находится во второй нормальной форме.
В следующих таблицах демонстрируется вторая нормальная форма:
| Student # | Советник | Adv-Room |
|---|---|---|
| 1022 | Джонс | 412 |
| 4123 | Smith | 216 |
| Student # | Класс # |
|---|---|
| 1022 | 101-07 |
| 1022 | 143-01 |
| 1022 | 159-02 |
| 4123 | 101-07 |
| 4123 | 143-01 |
| 4123 | 179-04 |
Третья нормальная форма: устранение данных, не зависящих от ключа
В последнем примере Adv-Room (номер офиса советника) функционально зависит от атрибута Advisor. Решение заключается в том, чтобы переместить этот атрибут из таблицы Студенты в таблицу факультета, как показано ниже:
Нормализация базы данных и ее формы
Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.
Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.
Материал этой статьи напрямую не относиться к изучению языка SQL, так как имеет отношение к проектированию баз данных (БД), но для общего понимания взаимосвязи хранимой в системе информации она будет полезна.
По поводу того, как должна быть спроектирована база нет 100% решения, потому что конкретный вариант может удовлетворять либо не удовлетворять различным бизнес-процессам и целям. Но не принимать во внимание элементарные правила нельзя, так как их соблюдение сохранит много времени, нервов и денег при работе с данными.
Нормализация баз данных заключается в приведении структуры хранения данных к нормальным формам (NF). Всего таких форм существует 8, но часто достаточным является соблюдение первых трех. Рассмотрим их более подробно на примере учебной базы данных. Примеры будут строится по принципу «что было бы, если было иначе, чем сейчас».
Первая нормальная форма
Основным правилом первой формы является необходимость неделимости значения в каждом поле (столбце) строки – атомарность значений.
Рассмотрим таблицы сотрудников и телефонных линий.
Чтобы избавиться от связывающей таблицы «Сотрудники_Линии», мы могли бы записать идентификаторы сотрудников для каждой линии в виде перечня в дополнительном столбце:
Но подобная структура не является надежной. Представьте, что Вам необходимо поменять некоторым сотрудникам подключенные линии. Потребуется осуществить разбор составного поля, чтобы определить наличие id сотрудника в каждой записи линий, затем скорректировать перечень. Получается слишком сложный и долгий процесс для такой простой операции.
Организации структуры таблиц с применением дополнительной связывающей избавляет от подобных проблем.
Помимо атомарности к первой нормальной форме относятся следующие правила:
Вторая нормальная форма
Условием этой формы является отсутствие зависимости неключевых полей от части составного ключа.
Так как составной ключ в учебной базе наблюдается только в таблице «Сотрудники_Линии», то рассмотрим пример на ней.
На представленной диаграмме столбцы описания и приоритета зависят от столбца «Линия», входящего в составной ключ. Это значит, что для каждой линии, подключенной разным сотрудникам, потребуется повторно указывать описание и приоритетность. Подобная структура приводит к избыточности данных.
Также велика вероятность возникновения противоречивой информации. Изменяя приоритет или описание для линии, можно по ошибке оставить некоторые строки не обработанными. В таком случае, для одного и того же идентификатора линии значения зависимых полей будут различными.
Если соблюдены правила первой нормальной формы, то создание таблицы «Линии» и перенос в нее зависимых столбцов удовлетворяет второй нормальной форме.
Третья нормальная форма
3NF схожа по логике с 2NF, но с некоторым отличием. Если 2 форма ликвидирует зависимости неключевых полей от части ключа, то третья нормальная форма исключает зависимость неключевых полей от других неключевых полей.
На приведенном примере таблицы сотрудников видно, что столбец «Супервайзер» имеет зависимость от столбца «Группа», а это значит, что при изменении значения поля группы, потребуется изменить значение поля супервайзера.
Все риски, которые были рассмотрены для 2NF, так же относятся к 3NF и устраняются переносом зависимых полей в отдельную таблицу.
Денормализация базы данных
Теория нормальных форм не всегда применима на практике. Например, неатомарные значения не всегда являются «злом», а иногда наоборот. Связано это с необходимостью дополнительного объединения (следовательно, затрат производительности системы) при выполнении запросов, особенно когда производится обработка большого массива информации.
Для баз данных, предназначенных для аналитики, часто выполняют денормализацию, чтобы укорить выполнение запросов.
Руководство по проектированию реляционных баз данных (10-13 часть из 15) [перевод]
Продолжение.
Предыдущие части: 1-3, 4-6, 7-9
10. Нормализация баз данных
Указания для правильного проектирования реляционных баз данных изложены в реляционной модели данных. Они собраны в 5 групп, которые называются нормальными формами. Первая нормальная форма представляет самый низкий уровень нормализации баз данных. Пятый уровень представляет высший уровень нормализации.
Вот некоторые из основных пунктов, которые связаны с нормализацией баз данных:
Очень малое количество баз данных следуют всем пяти нормальным формам, предоставленным в реляционной модели данных. Обычно базы данных нормализуются до второй или третьей нормальной формы. Четвертая и пятая формы используются редко. Поэтому я ограничусь тем, чтобы рассказать вам лишь о первых трех.
11. Первая нормальная форма (1НФ)
Первая нормальная форма гласит, что таблица базы данных – это представление сущности вашей системы, которую вы создаете. Примеры сущностей: заказы, клиенты, заказ билетов, отель, товар и т.д. Каждая запись в базе данных представляет один экземпляр сущности. Например, в таблице клиентов каждая запись представляет одного клиента.
Первичный ключ.
Правило: каждая таблица имеет первичный ключ, состоящий из наименьшего возможного количества полей.
Как вы знаете, первичный ключ может состоять из нескольких полей. Вы, к примеру, можете выбрать имя и фамилию в качестве первичного ключа (и надеяться, что эта комбинация будет уникальной всегда). Будет намного более хорошим выбором номер соц. Страхования в качестве первичного ключа, т.к. это единственное поле, которое уникальным образом идентифицирует человека.
Еще лучше, когда нет очевидного кандидата на звание первичного ключа, создайте суррогатный первичный ключ в виде числового автоинкрементного поля.
Атомарность.
Правило: поля не имеют дубликатов в каждой записи и каждое поле содержит только одно значение.
Возьмем, например, сайт коллекционеров автомобилей, на котором каждый коллекционер может зарегистрировать его автомобили. Таблица ниже хранит информацию о зарегистрированных автомобилях.
Горизонтальное дублирование данных – плохая практика.
С таким вариантом проектирования вы можете сохранить только пять автомобилей и если у вас их менее 5, то вы тратите впустую свободное место в базе данных на хранение пустых ячеек.
Другим примером плохой практики при проектировании является хранение множественных значений в ячейке.
Множественные значения в одной ячейке.
Верным решением в данном случае будет выделение автомобилей в отдельную таблицу и использование внешнего ключа, который ссылается на эту таблицу.
Порядок записей не должен иметь значение.
Правило: порядок записей таблицы не должен иметь значения.
Вы можете быть склонны использовать порядок записей в таблице клиентов для определения того, какой из клиентов зарегистрировался первым. Для этих целей вам лучше создать поля даты и времени регистрации клиентов. Порядок записей будет неизбежно меняться, когда клиенты будут удаляться, изменяться или добавляться. Вот почему вам никогда не следует полагаться на порядок записей в таблице.
В следующей части рассмотрим вторую нормальную форму (2НФ).
12. Вторая нормальная форма.
Для того, чтобы база данных была нормализована согласно второй нормальной форме, она должна быть нормализована согласно первой нормальной форме. Вторая нормальная форма связана с избыточностью данных.
Избыточность данных.
Правило: поля с не первичным ключом не должны быть зависимы от первичного ключа.
Может звучать немного заумно. А означает это то, что вы должны хранить в таблице только данные, которые напрямую связаны с ней и не имеют отношения к другой сущности. Следование второй нормальной форме – это вопрос нахождения данных, которые часто дублируются в записях таблицы и которые могут принадлежать другой сущности.
Дублирование данных среди записей в поле store.
Таблица выше может принадлежать компании, которая продает автомобили и имеет несколько магазинов в Нидерландах.
Если посмотрите на эту таблицу, то вы увидите множественные примеры дублирования данных среди записей. Поле brand могло бы быть выделено в отдельную таблицу. Также, как и поле type (модель), которое также могло бы быть выделено в отдельную таблицу, которая бы имела связь многие-к-одному с таблицей brand потому, что у бренда могут быть разные модели.
Колонка store содержит наименование магазина, в котором в настоящее время находится машина. Store – это очевидный пример избыточности данных и хороший кандидат для отдельной сущности, которая должна быть связана с таблицей автомобилей связью по внешнему ключу.
Ниже пример того, как бы вы моги смоделировать базу данных для автомобилей, избегая избыточности данных.
В примере выше таблица car имеет внешний ключ – ссылку на таблицы type и store. Столбец brand исчез потому, что на бренд есть неявная ссылка через таблицу type. Когда есть ссылка на type, есть ссылка и на brand, т.к. type принадлежит brand.
Избыточность данных была существенным образом устранена из нашей модели базы данных. Если вы достаточно придирчивы, то вы, возможно, еще не удовлетворены этим решением. А как насчет поля country_of_origin в таблице brand? Пока дубликатов нет потому, что есть только четыре бренда из разных стран. Внимательный разработчик базы данных должен выделить названия стран в отдельную таблицу country.
И даже сейчас вы не должны быть удовлетворены результатом потому, что вы также могли бы выделить поле color в отдельную таблицу.
Насколько строго вы подходите к созданию ваших таблиц – решать вам и зависит от конкретной ситуации. Если вы планируете хранить огромное количество единиц автомобилей в системе и вы хотите иметь возможность производить поиск по цвету (color), то было бы мудрым решением выделить цвета в отдельную таблицу так, чтобы они не дублировались.
Существует другой случай, когда вы можете захотеть выделить цвета в отдельную таблицу. Если вы хотите позволить работникам компании вносить данные о новых автомобилях вы захотите, чтобы они имели возможно выбирать цвет машины из заранее заданного списка. В этом случае вы захотите хранить все возможные цвета в вашей базе данных. Даже если еще нет машин с таким цветом, вы захотите, чтобы эти цвета присутствовали в базе данных, чтобы работники могли их выбирать. Это определенно тот случай, когда вам нужно выделить цвета в отдельную таблицу.
13. Третья нормальная форма.
Третья нормальная форма связана с транзитивными зависимостями. Транзитивные зависимости между полями базы данных существует тогда, когда значения не ключевых полей зависят от значений других не ключевых полей. Чтобы база данных была в третьей нормальной форме, она должна быть во второй нормальной форме.
Транзитивные зависимости.
Правило: не может быть транзитивных зависимостей между полями в таблице.
Таблица клиентов (мои клиенты – игроки немецкой и французской футбольной команды) ниже содержит транзитивные зависимости.
В этой таблице не все поля зависят исключительно от первичного ключа. Существует отдельная связь между полем postal_code и полями города (city) и провинции (province). В Нидерландах оба значение: город и провинция – определяются почтовым кодом, индексом. Таким образом, нет необходимости хранить город и провинцию в клиентской таблице. Если вы знаете почтовый код, то вы уже знаете город и провинцию.
Такая транзитивной зависимости следует избегать, если вы хотите, чтобы ваша модель базы данных была в третьей нормальной форме.
В данном случае устранение транзитивной зависимости из таблицы может быть достигнуто путем удаления полей города и провинции из таблицы и хранение их в отдельной таблице, содержащей почтовый код (первичный ключ), имя провинции и имя города. Получение комбинации почтовый код-город-провинция для целой страны может быть весьма нетривиальным занятием. Вот почему такие таблицы зачастую продаются.
Другим примером для применения третьей нормальной формы может служить (слишком) простой пример таблицы заказов интернет-магазина ниже.
НДС (value added tax) – это процент, который добавляется к цене продукта (19% в данной таблице). Это означает, что значение total_ex_vat может быть вычислено из значения total_inc_vat и vice versa. Вы должны хранить в таблице одно из этих значений, но не оба сразу. Вы должны возложить задачу вычисления total_inc_vat из total_ex_vat или наоборот на программу, которая использует базу данных.
Третья нормальная форма гласит, что вы не должны хранить данные в таблице, которые могут быть получены из других (не ключевых) полей таблицы. Особенно в примере с таблицей клиентов следование третьей нормальной форме требует либо большого объема работы, либо приобретения коммерческой версии данных для такой таблицы.
Третья нормальная форма не всегда используется при проектировании баз данных. Когда разрабатываете базу данных вы всегда должны сравнивать преимущества от более высокой нормальной формы в сравнении с объемом работ, которые требуются для применения третьей нормальной формы и поддержания данных в таком состоянии. В случае с клиентской таблицей лично я бы предпочел не нормализовать таблицу до третьей нормальной формы. В последнем примере с НДС я бы использовал третью нормальную форму. Хранение данных, воспроизводимых из существующих, обычно плохая идея.