Лекция
Сразу хочу сказать, что здесь никакой воды про реляционные бд, и только нужная информация. Для того чтобы лучше понимать что такое реляционные бд, нормальный вид, нормализация, нормальная форма, кортеж , аномалии-добавления, аномалии-удаления, аномалии-модификации , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.
нормальная форма — свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, потенциально приводящей к логически ошибочным результатам выборки или изменения данных. Нормальная форма определяется как совокупность требований, которым должно удовлетворять отношение.
Процесс преобразования отношений базы данных к виду, отвечающему нормальным формам, называется нормализацией. нормализация предназначена для приведения структуры БД к виду, обеспечивающему минимальную логическую избыточность, и не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение физического объема базы данных. Конечной целью нормализации является уменьшение потенциальной противоречивости хранимой в базе данных информации. Как отмечает К. Дейт, общее назначение процесса нормализации заключается в следующем:
Устранение избыточности производится, как правило, за счет декомпозиции отношений таким образом, чтобы в каждом отношении хранились только первичные факты (то есть факты, не выводимые из других хранимых фактов).
Содержание
Атрибут — свойство некоторой сущности. Часто называется полем таблицы.
Домен атрибута — множество допустимых значений, которые может принимать атрибут.
кортеж — конечное множество взаимосвязанных допустимых значений атрибутов, которые вместе описывают некоторую сущность (строка таблицы).
Отношение — конечное множество кортежей (таблица).
Схема отношения — конечное множество атрибутов, определяющих некоторую сущность. Иными словами, это структура таблицы, состоящей из конкретного набора полей.
Проекция — отношение, полученное из заданного путем удаления и (или) перестановки некоторых атрибутов.
Функциональная зависимость между атрибутами (множествами атрибутов) X и Y означает, что для любого допустимого набора кортежей в данном отношении: если два кортежа совпадают по значению X, то они совпадают по значению Y. Например, если значение атрибута «Название компании» — Canonical Ltd, то значением атрибута «Штаб-квартира» в таком кортеже всегда будет Millbank Tower, London, United Kingdom. Обозначение: {X} -> {Y}.
Нормальная форма — требование, предъявляемое к структуре таблиц в теории реляционных баз данных для устранения из базы избыточных функциональных зависимостей между атрибутами (полями таблиц).
Метод нормальных форм (НФ) состоит в сборе информации о объектах решения задачи в рамках одного отношения и последующей декомпозиции этого отношения на несколько взаимосвязанных отношений на основе процедур нормализации отношений.
Цель нормализации: исключить избыточное дублирование данных, которое является причиной аномалий, возникших при добавлении, редактировании и удалении кортежей(строк таблицы).
Аномалией называется такая ситуация в таблице БД, которая приводит к противоречию в БД либо существенно усложняет обработку БД. Причиной является излишнее дублирование данных в таблице, которое вызывается наличием функциональных зависимостей от не ключевых атрибутов.
аномалии-модификации проявляются в том, что изменение одних данных может повлечь просмотр всей таблицы и соответствующее изменение некоторых записей таблицы.
аномалии-удаления — при удалении какого либо кортежа из таблицы может пропасть информация, которая не связана на прямую с удаляемой записью.
аномалии-добавления возникают, когда информацию в таблицу нельзя поместить, пока она не полная, либо вставка записи требует дополнительного просмотра таблицы.
При том, что идеи нормализации весьма полезны для проектирования баз данных, они отнюдь не являются универсальным или исчерпывающим средством повышения качества проекта БД. Это связано с тем, что существует слишком большое разнообразие возможных ошибок и недостатков в структуре БД, которые нормализацией не устраняются. Несмотря на эти рассуждения, теория нормализации является очень ценным достижением реляционной теории и практики, поскольку она дает научно строгие и обоснованные критерии качества проекта БД и формальные методы для усовершенствования этого качества. Этим теория нормализации резко выделяется на фоне чисто эмпирических подходов к проектированию, которые предлагаются в других моделях данных. Более того, можно утверждать, что во всей сфере информационных технологий практически отсутствуют методы оценки и улучшения проектных решений, сопоставимые с теорией нормализации реляционных баз данных по уровню формальной строгости.
Нормализацию иногда упрекают на том основании, что «это просто здравый смысл», а любой компетентный профессионал и сам «естественным образом» спроектирует полностью нормализованную БД без необходимости применять теорию зависимостей. Однако, как указывает К. Дейт, нормализация в точности и является теми принципами здравого смысла, которыми руководствуется в своем сознании зрелый проектировщик, то есть принципы нормализации — это формализованный здравый смысл. Между тем, идентифицировать и формализовать принципы здравого смысла — весьма трудная задача, и успех в ее решении является существенным достижением.
Нормальные формы
В создании и развитии теории нормализации принимали участие многие ученые. Однако первые три нормальные формы и концепцию функциональной зависимости предложил Э. Кодд.
Первая нормальная форма (1NF)
Первая нормальная форма
Переменная отношения находится в первой нормальной форме (1НФ) тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.
В реляционной модели отношение всегда находится в первой нормальной форме по определению понятия отношение. Что же касается различных таблиц, то они могут не быть правильными представлениями отношений и, соответственно, могут не находиться в 1НФ.
Вторая нормальная форма (2NF)
Вторая нормальная форма
Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме, и каждый неключевой атрибут неприводимо (функционально полно) зависит от ее потенциального ключа.
Третья нормальная форма (3NF)
Третья нормальная форма
Переменная отношения находится в третьей нормальной форме тогда и только тогда, когда она находится во второй нормальной форме, и отсутствуют транзитивные функциональные зависимости неключевых атрибутов от ключевых.
Нормальная форма Бойса — Кодда (BCNF)
Нормальная форма Бойса — Кодда
Переменная отношения находится в нормальной форме Бойса — Кодда (иначе — в усиленной третьей нормальной форме) тогда и только тогда, когда каждая ее нетривиальная и неприводимая слева функциональная зависимость имеет в качестве своего детерминанта некоторый потенциальный ключ.
Четвертая нормальная форма (4NF)
Четвертая нормальная форма
Переменная отношения находится в четвертой нормальной форме, если она находится в нормальной форме Бойса — Кодда и не содержит нетривиальных многозначных зависимостей.
Пятая нормальная форма (5NF)
Пятая нормальная форма
Переменная отношения находится в пятой нормальной форме (иначе — в проекционно-соединительной нормальной форме) тогда и только тогда, когда каждая нетривиальная зависимость соединения в ней определяется потенциальным ключом (ключами) этого отношения.
Доменно-ключевая нормальная форма (DKNF)
Доменно-ключевая нормальная форма
Шестая нормальная форма (6NF)
Шестая нормальная форма
Введена К. Дейтом в его книге, как обобщение пятой нормальной формы для темпоральной базы данных.
Переменная отношения находится в первой нормальной форме тогда и только тогда, когда в любом допустимом значении отношения каждый его кортеж содержит только одно значение для каждого из атрибутов.
В реляционной модели отношение всегда находится в первой нормальной форме по определению понятия отношение.
Что же касается различных таблиц, то они могут не быть правильными представлениями отношений и, соответственно, могут не находиться в 1NF. В соответствии с определением К. Дж. Дейта для такого случая, таблица нормализована (эквивалентно — находится в первой нормальной форме) тогда и только тогда, когда она является прямым и верным представлением некоторого отношения. Конкретнее, рассматриваемая таблица должна удовлетворять следующим пяти условиям:
«Обычность» всех столбцов таблицы означает, что в таблице нет «скрытых» компонентов, которые могут быть доступны только в вызове некоторого специального оператора взамен ссылок на имена регулярных столбцов, или которые приводят к побочным эффектам для строк или таблиц при вызове стандартных операторов. Таким образом, например, строки не имеют идентификаторов кроме обычных значений потенциальных ключей (без скрытых «идентификаторов строк» или «идентификаторов объектов»). Они также не имеют скрытых временных меток .
Исходная ненормализованная (то есть не являющаяся правильным представлением некоторого отношения) таблица:
| Сотрудник | Номер телефона |
|---|---|
| Иванов И. И. | 283-56-82 390-57-34 |
| Петров П. П. | 708-62-34 |
Таблица, приведенная к 1NF (являющаяся правильным представлением некоторого отношения):
| Сотрудник | Номер телефона |
|---|---|
| Иванов И. И. | 283-56-82 |
| Иванов И. И. | 390-57-34 |
| Петров П. П. | 708-62-34 |
Вопрос об атомарности атрибутов решается на основе семантики данных, то есть их смыслового значения. Атрибут атомарен, если его значение теряет смысл прилюбом разбиении на части или переупорядочивании. Следовательно, если какой-либо способ разбиения на части не лишает атрибут смысла, то атрибут неатомарен.
Одно и то же значение может быть атомарным или неатомарным в зависимости от смысла этого значения. Например, значение «4286» является
Хорошим способом принятия решения о необходимости разбиения атрибута на части является вопрос: «будут ли части атрибута использоваться по отдельности?». Если да, то атрибут следует разделить (но так, чтобы сохранились осмысленные части атрибута). Далее необходимо снова задаться тем же вопросом для новой структуры и так до тех пор, пока не останется атрибутов, допускающих разбиение.
Примеры неатомарного атрибута, часто встречающиеся на практике: составные поля в виде строки идентификаторов, разделенных, скажем, запятыми: 100, 32, 168, 1045.
Исходное назначение 1NF, которую предложил Э. Ф. Кодд в статье «Реляционная модель данных для больших совместно используемых банков данных» («A Relational Model of Data for Large Shared Data Banks» ), вообще не было связано с борьбой с аномалиями или избыточностью. Кодд предложил использовать «простые домены» (simple domains) только для облегчения будущей программной реализации, а именно:
Отношение, все домены которого являются простыми, может быть представлено при хранении двухмерным массивом с однородными столбцами.
Оригинальный текст (англ.) [показать]
Простота представления отношений массивами, осуществимая в случае приведения всех отношений в нормальную форму, предоставляет преимущества не только при хранении, но также при передаче больших объемов данных между системами, использующими во многом отличные представления данных.
Оригинальный текст (англ.)
Переменная отношения находится во второй нормальной форме тогда и только тогда, когда она находится в первой нормальной форме и каждый неключевой атрибутнеприводимо зависит от ее потенциального ключа.
Неприводимость означает, что в составе потенциального ключа отсутствует меньшее подмножество атрибутов, от которого можно также вывести данную функциональную зависимость. Для неприводимой функциональной зависимости часто используется эквивалентное понятие «полная функциональная зависимость».
Если потенциальный ключ является простым, то есть состоит из единственного атрибута, то любая функциональная зависимость от него является неприводимой (полной). Если потенциальный ключ является составным, то согласно определению второй нормальной формы в отношении не должно быть неключевых атрибутов, зависящих от части составного потенциального ключа.
Вторая нормальная форма по определению запрещает наличие неключевых атрибутов, которые вообще не зависят от потенциального ключа. Таким образом, вторая нормальная форма в том числе запрещает создавать отношения как несвязанные (хаотические, случайные) наборы атрибутов.
Пример приведения отношения ко второй нормальной форме
Пусть в следующем отношении первичный ключ образует пара атрибутов {Сотрудник, Должность}:
| Сотрудник | Должность | Зарплата | Наличие компьютера |
|---|---|---|---|
| Гришин | Кладовщик | 20000 | Нет |
| Васильев | Программист | 40000 | Есть |
| Иванов | Кладовщик | 25000 | Нет |
Зарплату сотруднику каждый начальник устанавливает сам (хотя ее границы зависят от должности). Наличие же компьютера у сотрудника зависит только от должности, то есть зависимость от первичного ключа неполная.
В результате приведения к 2NF исходное отношение следует декомпозировать на два отношения:
| Сотрудник | Должность | Зарплата |
|---|---|---|
| Гришин | Кладовщик | 20000 |
| Васильев | Программист | 40000 |
| Иванов | Кладовщик | 25000 |
| Должность | Наличие компьютера |
|---|---|
| Кладовщик | Нет |
| Программист | Есть |
Третья нормальная форма (англ. Third normal form; сокращенно 3NF) — одна из возможных нормальных форм отношения в реляционной базе данных. 3NF была изначально сформулирована Э. Ф. Коддом в 1971 году.
Переменная отношения R находится в 3NF тогда и только тогда, когда выполняются следующие условия:
Пояснения к определению:
Неключевой атрибут отношения R — это атрибут, который не принадлежит ни одному из потенциальных ключей R.
Функциональная зависимость множества атрибутов Z от множества атрибутов X (записывается X → Z, произносится «икс определяет зет») является транзитивной, если существует такое множество атрибутов Y, что X → Y и Y → Z. При этом ни одно из множеств X, Y и Z не является подмножеством другого, то есть функциональные зависимости X → Z, X → Y и Y → Z не являются тривиальными.
Определение 3NF, эквивалентное определению Кодда, но по-другому сформулированное, дал Карло Заниоло в 1982 году. Об этом говорит сайт https://intellect.icu . Согласно ему, переменная отношения находится в 3NF тогда и только тогда, когда для каждой из ее функциональных зависимостей X → A выполняется хотя бы одно из следующих условий:
Определение Заниоло четко определяет разницу между 3NF и более строгой нормальной формой Бойса-Кодда (НФБК): НФБК исключает третье условие («А — ключевой атрибут»).
Запоминающееся и, по традиции, наглядное резюме определения 3NF Кодда было дано Биллом Кентом: каждый неключевой атрибут «должен предоставлять информацию о ключе, полном ключе и ни о чем, кроме ключа».
Условие зависимости от «полного ключа» неключевых атрибутов обеспечивает то, что таблица находится во второй нормальной форме; а условие зависимости их от «ничего, кроме ключа» — то, что они находятся в третьей нормальной форме.
Крис Дэйт говорит о резюме Кента как о «интуитивно привлекательной характеристике» 3NF, и замечает, что с небольшим изменением она может служить и как определение более строгой нормальной формы Бойса-Кодда: «Каждый атрибут должен предоставлять информацию о ключе, полном ключе и ни о чем, кроме ключа». Вариант определения 3NF Кента является менее строгим, чем вариант НФБК Дэйта, поскольку первая утверждает только, что неключевые атрибуты зависят от ключей. Первичные атрибуты (которые являются ключами или их частями) вовсе не должны быть функционально зависимыми; каждый из них предоставляет информацию о ключе предоставлением самого ключа или его части. Здесь следует отметить, что это правило справедливо только для неключевых атрибутов, так как применение его ко всем атрибутам будет полностью запрещать все сложные альтернативные ключи, поскольку каждый элемент такого ключа будет нарушать условие «полного ключа».
Рассмотрим в качестве примера отношение, которое находится во 2NF, но не соответствует 3NF:
| Сотрудник | Отдел | Телефон |
|---|---|---|
| Гришин | Бухгалтерия | 11-22-33 |
| Васильев | Бухгалтерия | 11-22-33 |
| Петров | Снабжение | 44-55-66 |
В отношении атрибут «Сотрудник» является первичным ключом. Личных телефонов у сотрудников нет, и телефон сотрудника зависит исключительно от отдела.
Таким образом, в отношении существуют следующие функциональные зависимости: Сотрудник → Отдел, Отдел → Телефон, Сотрудник → Телефон.
Зависимость Сотрудник → Телефон является транзитивной, следовательно, отношение не находится в 3NF.
В результате разделения отношения R1 получаются два отношения, находящиеся в 3NF:
| Отдел | Телефон |
|---|---|
| Бухгалтерия | 11-22-33 |
| Снабжение | 44-55-66 |
| Сотрудник | Отдел |
|---|---|
| Гришин | Бухгалтерия |
| Васильев | Бухгалтерия |
| Петров | Снабжение |
Исходное отношение R1 при необходимости легко получается в результате операции соединения отношений R2 и R3.
Нормальная форма Бойса-Кодда (англ. Boyce-Codd normal form; сокращенно BCNF) — одна из возможных нормальных форм отношения в реляционной модели данных.
Иногда нормальную форму Бойса-Кодда называют усиленной третьей нормальной формой, поскольку она во всех отношениях сильнее (строже) по сравнению с ранее определенной 3НФ .
Названа в честь Рэя Бойса и Эдгара Кодда, хотя Кристофер Дейт указывает, что на самом деле строгое определение «третьей» нормальной формы, эквивалентное определению нормальной формы Бойса-Кодда, впервые было дано Иэном Хитом (англ. Ian Heath) в 1971 году, поэтому данную форму следовало бы называть «нормальной формой Хита» .
Переменная отношения находится в BCNF тогда и только тогда, когда каждая ее нетривиальная и неприводимая слева функциональная зависимость имеет в качестве своего детерминанта некоторый потенциальный ключ .
Менее формально, переменная отношения находится в нормальной форме Бойса-Кодда тогда и только тогда, когда детерминанты всех ее функциональных зависимостей являются потенциальными ключами.
Для определения BCNF следует понимать понятие функциональной зависимости атрибутов отношения.
Пусть R является переменной отношения, а X и Y — произвольными подмножествами множества атрибутов переменной отношения R. Y функционально зависимоот X тогда и только тогда, когда для любого допустимого значения переменной отношения R, если два кортежа переменной отношения R совпадают по значению X, они также совпадают и по значению Y. Подмножество X называют детерминантом, а Y — зависимой частью.
Функциональная зависимость тривиальна тогда и только тогда, когда ее правая (зависимая) часть является подмножеством ее левой части (детерминанта).
Ситуация, когда отношение будет находиться в 3NF, но не в BCNF, возникает, например, при условии, что отношение имеет два (или более) потенциальных ключа, которые являются составными и имеют общий атрибут. На практике такая ситуация встречается достаточно редко, для всех прочих отношений 3NF и BCNF эквивалентны.
Предположим, создается таблица бронирования для теннисных кортов на день: {Номер корта, Время начала, Время окончания, Тариф, Член клуба}. Тариф зависит от выбранного корта и членства в клубе, для каждого из кортов имеется тариф для членов теннисного клуба и для сторонних клиентов. Тарифы для кортов не повторяются.
Таким образом, возможны следующие составные первичные ключи: {Номер корта, Время начала}, {Номер корта, Время окончания}, {Тариф, Время начала},{Тариф, Время окончания}.
Таблица соответствует второй и третьей нормальной форме. Требования второй нормальной формы (2NF) выполняются, так как все атрибуты входят в какой-то из потенциальных ключей, а неключевых атрибутов в отношении нет. Также нет и транзитивных зависимостей, что соответствует требованиям третьей нормальной формы (3NF).
Тем не менее, существует функциональная зависимость тарифа от номера корта. То есть, по ошибке можно нарушить логическую целостность и, например, приписать тариф Premium для первого корта, хотя тариф Premium может относиться только ко второму корту.
Можно улучшить структуру, разбив таблицу на две: {Номер корта, Время начала, Время окончания} и {Тариф, Номер корта, Член клуба}. Данное отношение будет соответствовать BCNF.
Четвертая нормальная форма (4NF) — одна из возможных нормальных форм отношения реляционной базы данных.
Переменная отношения R находится в четвертой нормальной форме, если она находится в НФБК и все нетривиальные многозначные зависимости фактически являются функциональными зависимостями от ее потенциальных ключей.
Эквивалентная формулировка определения:
Переменная отношения R находится в четвертой нормальной форме тогда и только тогда, когда в случае существования таких подмножеств A и B атрибутов этой переменной отношения R, для которых выполняется нетривиальная многозначная зависимость A →→ B, все атрибуты переменной отношения R также функционально зависят от А .
Предположим, что рестораны производят разные виды пиццы, а службы доставки ресторанов работают только в определенных районах города. Составной первичный ключ соответствующей переменной отношения включает три атрибута: {Ресторан, Вид пиццы, Район доставки}.
Такая переменная отношения не соответствует 4НФ, так как существует следующая многозначная зависимость:
{Вид пиццы} {Район доставки}То есть, например, при добавлении нового вида пиццы придется внести по одному новому кортежу для каждого района доставки. Возможна логическая аномалия, при которой определенному виду пиццы будут соответствовать лишь некоторые районы доставки из обслуживаемых рестораном районов.
Для предотвращения аномалии нужно декомпозировать отношение, разместив независимые факты в разных отношениях. В данном примере следует выполнить декомпозицию на {Ресторан, Вид пиццы} и {Ресторан, Район доставки}.
Однако если к исходной переменной отношения добавить атрибут, функционально зависящий от потенциального ключа, например цену с учетом стоимости доставки ({Ресторан, Вид пиццы, Район доставки} → Цена), то полученное отношение будет находиться в 4НФ и его уже нельзя подвергнуть декомпозиции без потерь.[источник не указан 691 день] Указанные выше многозначные зависимости в данном случае называются внедренными зависимостями.
Для определения пятой нормальной формы следует предварительно ввести понятие зависимости соединения, которое, в свою очередь основано на понятиидекомпозиции без потерь.
Декомпозицией отношения R называется замена R на совокупность отношений {R1, R2,... , Rn} такую, что каждое из них есть проекция R, и каждый атрибут R входит хотя бы в одну из проекций декомпозиции.
Например, для отношения R с атрибутами {a, b, c} существуют следующие основные варианты декомпозиции:
Рассмотрим теперь отношение R', которое получается в результате операции естественного соединения (NATURAL JOIN), примененной к отношениям, полученным в результате декомпозиции R.
Декомпозиция называется декомпозицией без потерь, если R' в точности совпадает с R.
Неформально говоря, при декомпозиции без потерь отношение «разделяется» на отношения-проекции таким образом, что из полученных проекций возможна «сборка» исходного отношения с помощью операции естественного соединения.
Далеко не всякая декомпозиция является декомпозицией без потерь. Проиллюстрируем это на примере отношения R с атрибутами {a, b, c}, приведенном выше. Пусть отношение R имеет вид:
| a | b | c |
|---|---|---|
| Москва | Россия | столица |
| Томск | Россия | не столица |
| Берлин | Германия | столица |
Декомпозиция R1 = {a}, R2 = {b, c} имеет вид:
| a |
|---|
| Москва |
| Томск |
| Берлин |
| b | c |
|---|---|
| Россия | столица |
| Россия | не столица |
| Германия | столица |
Результат операции соединения этих отношений:
| a | b | c |
|---|---|---|
| Москва | Россия | столица |
| Москва | Россия | не столица |
| Москва | Германия | столица |
| Томск | Россия | столица |
| Томск | Россия | не столица |
| Томск | Германия | столица |
| Берлин | Россия | столица |
| Берлин | Россия | не столица |
| Берлин | Германия | столица |
Очевидно, что R' не совпадает с R, а значит такая декомпозиция не является декомпозицией без потерь. Рассмотрим теперь декомпозицию R1 = {a, b}, R2 = {a, c}:
| a | b |
|---|---|
| Москва | Россия |
| Томск | Россия |
| Берлин | Германия |
| a | c |
|---|---|
| Москва | столица |
| Томск | не столица |
| Берлин | столица |
Такая декомпозицией является декомпозицией без потерь, в чем читатель может убедиться самостоятельно.
В некоторых случаях отношение вовсе невозможно декомпозировать без потерь. Существуют также примеры отношений, для которых нельзя выполнить декомпозицию без потерь на две проекции, но которые можно подвергнуть декомпозиции без потерь на три или большее количество проекций .
Пусть R — переменная отношения, а A, B, ..., Z — некоторые подмножества множества ее атрибутов.
Если декомпозиция любого допустимого значения R на отношения, состоящие из множеств атрибутов A, B, ..., Z, является декомпозицией без потерь, говорят, что переменная отношения R удовлетворяет зависимости соединения *{А, В, . . . , Z} .
Иными словами, переменная отношения R удовлетворяет зависимости соединения *{А, В, . . . , Z} тогда и только тогда, когда любое допустимое значение переменной отношения R эквивалентно соединению ее проекций по подмножествам A, B, ..., Z множества атрибутов.
Подобно тому, как функциональная зависимость есть частный случай многозначной зависимости, многозначная зависимость является частным случаемзависимости соединения. Зависимость соединения является предельным обобщением понятий многозначной и функциональной зависимости, то есть это наиболее общая форма зависимости между атрибутами отношения.
Важно понимать, что зависимость соединения определяется не для конкретного значения переменной отношения в тот или иной момент времени, а по всем возможным значениям. Поэтому понятие зависимости соединения определено не для отношения (конкретного значения), а для переменной отношения. Зависимость соединения определяется не механически по текущим значениям, а следует из внешнего знания о природе и закономерностях данных, которые могут находиться в переменной отношения. То же самое относится к многозначной и функциональной зависимостям.
Зависимость соединения *{A, B,..., Z} является тривиальной тогда и только тогда, когда по крайней мере одно из подмножеств A, B, ..., Z является множеством всех атрибутов отношения (включает все атрибуты). В противном случае зависимость соединения является нетривиальной.
Отношение находится в пятой нормальной форме (иначе — в проекционно-соединительной нормальной форме) тогда и только тогда, когда каждая нетривиальнаязависимость соединения в нем определяется потенциальным ключом (ключами) этого отношения .
Зависимость соединения *{A, B,..., Z} определяется потенциальным ключом (ключами) тогда и только тогда, когда каждое из подмножеств A, B, ..., Z множества атрибутов является суперключом отношения .
Условие «каждое из подмножеств A, B,..., Z множества атрибутов является суперключом отношения» можно эквивалентно сформулировать так: «каждое из подмножеств A, B, ..., Z множества атрибутов включает некоторый потенциальный ключ отношения».
Любое отношение в 5НФ автоматически находится также в 4НФ и, следовательно, во всех других нормальных формах. 5НФ является окончательной нормальной формой (по крайней мере в контексте операций проекции и соединения).
Рональд Фейгин в 1979 г. показал, что любая переменная отношения может быть подвергнута декомпозиции без потерь на эквивалентный набор переменных отношения в 5НФ, т.е. 5НФ всегда достижима. Однако К. Дейт отмечает, что процедура определения того, что некоторая переменная отношения находится в 4НФ, а не в 5НФ, и, таким образом, существует возможность ее дальнейшей выгодной декомпозиции, все еще остается не вполне ясной. Это связано с тем, что задача определения всех зависимостей соединения для отношения может оказаться очень сложной, а по поводу отношения можно утверждать, что оно находится в 5НФ, только при условии известности всех его потенциальных ключей и всех его зависимостей соединения.
Очень редко отношение, находящееся в 4НФ, не соответствует 5НФ. Это те ситуации, в которых реальные правила, ограничивающие допустимые комбинации атрибутов, никак не выражены в структуре отношения (см. пример ниже). В таком случае, если отношение не приведено к 5НФ, бремя обеспечения логической целостности данных отчасти перекладывается на приложение, отвечающее за добавление, удаление и изменения данных. В этом случае существует риск возникновения ошибок. Пятая нормальная форма исключает возникновение таких аномалий.
Предположим, что нужно хранить данные об ассортименте нескольких продавцов, торгующих продукцией нескольких фирм (номенклатура товаров фирм может пересекаться):
| Продавец | Фирма | Товар |
|---|---|---|
| Иванов | Рога и Копыта | Пылесос |
| Иванов | Рога и Копыта | Хлебница |
| Петров | Безенчук&Ко | Сучкорез |
| Петров | Безенчук&Ко | Пылесос |
| Петров | Безенчук&Ко | Хлебница |
| Петров | Безенчук&Ко | Зонт |
| Сидоров | Безенчук&Ко | Пылесос |
| Сидоров | Безенчук&Ко | Телескоп |
| Сидоров | Рога и Копыта | Пылесос |
| Сидоров | Рога и Копыта | Лампа |
| Сидоров | Геркулес | Вешалка |
Если дополнительных условий нет, то данное отношение, которое находится в 4-ой нормальной форме, является корректным и отражает все необходимые ограничения.
Теперь предположим, что нужно учесть следующее ограничение: каждый продавец имеет в своем ассортименте ограниченный список фирм и ограниченный список типов товаров и предлагает товары из списка товаров, производимые фирмами из списка фирм.
То есть продавец не имеет право торговать какими угодно товарами каких угодно фирм. Если продавец П имеет право торговать товарами фирмы Ф, и если продавецП имеет право торговать товарами типа Т, то в этом случае в ассортимент продавца П входят товары типа Т фирмы Ф при условии, что фирма Ф производит товары типа Т.
Такое ограничение может быть вызвано, например, тем, что список типов товаров продавца ограничен имеющимися у него лицензиями, либо знаниями и квалификацией, необходимыми для их продажи, а список фирм каждого продавца определен партнерскими соглашениями.
В рассматриваемом примере, в частности, предполагается, что продавец Иванов имеет право торговать товарами только фирмы «Рога и Копыта», продавец Петров — товарами только фирмы «Безенчук&Ко», зато продавец Сидоров не имеет право торговать хлебницами и сучкорезами и т.д.
Предложенное выше отношение не может исключить ситуации, при которых данное ограничение будет нарушено. Ничто не препятствует занести данные о торговле товаром, который данная фирма вообще не выпускает, либо данные о торговле товарами той фирмы, которую данный продавец не обслуживает, либо данные о торговле таким типом товара, который данный продавец не имеет право продавать.
Отношение не находится в 5NF, поскольку в нем есть нетривиальная зависимость соединения *{{Продавец, Фирма}, {Фирма, Товар}, {Продавец, Товар}}, однако подмножества {Продавец, Фирма}, {Фирма, Товар}, {Продавец, Товар} не являются суперключами исходного отношения.
В данном случае для приведения к 5NF отношение должно быть разбито на три: {Продавец, Фирма}, {Фирма, Товар}, {Продавец, Товар}.
| Продавец | Товар |
|---|---|
| Иванов | Пылесос |
| Иванов | Хлебница |
| Петров | Сучкорез |
| Петров | Пылесос |
| Петров | Хлебница |
| Петров | Зонт |
| Сидоров | Телескоп |
| Сидоров | Пылесос |
| Сидоров | Лампа |
| Сидоров | Вешалка |
| Продавец | Фирма |
|---|---|
| Иванов | Рога и Копыта |
| Петров | Безенчук&Ко |
| Сидоров | Безенчук&Ко |
| Сидоров | Рога и Копыта |
| Сидоров | Геркулес |
| Фирма | Товар |
|---|---|
| Рога и Копыта | Пылесос |
| Рога и Копыта | Хлебница |
| Рога и Копыта | Лампа |
| Безенчук&Ко | Сучкорез |
| Безенчук&Ко | Пылесос |
| Безенчук&Ко | Хлебница |
| Безенчук&Ко | Зонт |
| Безенчук&Ко | Телескоп |
| Геркулес |
Вешалка
|
Доменно-ключевая нормальная форма (DKNF) — одна из возможных нормальных форм таблицы реляционной базы данных. Ее предложил Рональд Фагин в 1981 году.
Переменная отношения находится в ДКНФ тогда и только тогда, когда каждое наложенное на нее ограничение является логическим следствием ограничений доменов и ограничений ключей, наложенных на данную переменную отношения.
Ограничение домена – ограничение, предписывающее использовать для определенного атрибута значения только из некоторого заданного домена. Ограничение по своей сути является заданием перечня (или логического эквивалента перечня) допустимых значений типа и объявлением о том, что указанный атрибут имеет данный тип.
Ограничение ключа – ограничение, утверждающее, что некоторый атрибут или комбинация атрибутов является потенциальным ключом.
Любая переменная отношения, находящаяся в ДКНФ, обязательно находится в 5НФ. Однако не любую переменную отношения можно привести к ДКНФ.
Шестая нормальная форма (6NF) — одна из возможных нормальных форм таблицы реляционной базы данных.
Введена К. Дейтом как обобщение пятой нормальной формы для хронологической базы данных .
Переменная отношения находится в шестой нормальной форме тогда и только тогда, когда она удовлетворяет всем нетривиальным зависимостям соединения. Из определения следует, что переменная находится в 6НФ тогда и только тогда, когда она неприводима, то есть не может быть подвергнута дальнейшей декомпозиции без потерь. Каждая переменная отношения, которая находится в 6НФ, также находится и в 5НФ.
Идея «декомпозиции до конца» выдвигалась до начала исследований в области хронологических данных, но не нашла поддержки. Однако для хронологических баз данных максимально возможная декомпозиция позволяет бороться с избыточностью и упрощает поддержание целостности базы данных.
Для хронологических баз данных определены U_операторы, которые распаковывают отношения по указанным атрибутам, выполняют соответствующую операцию и упаковывают полученный результат. В данном примере соединение проекций отношения должно производится при помощи оператора U_JOIN.
| Таб. № | Время | Должность | Домашний адрес |
|---|---|---|---|
| 6575 | [01-01-2000:10-02-2003] | слесарь | ул. Ленина, 10 |
| 6575 | [11-02-2003:15-06-2006] | слесарь | ул. Советская, 22 |
| 6575 | [16-06-2006:05-03-2009] | бригадир | ул. Советская, 22 |
Переменная отношения «Работники» не находится в 6НФ и может быть подвергнута декомпозиции на переменные отношения «Должности работников» и «Домашние адреса работников».
|
|
Вкратце, что такое нормализация. Большинство современных субд разработаны на основе реляционной алгебры, которая появилась раньше самих реляционных субд под авторством некого доктора Кодда. Он же вывел несколько правил, или форм, по упорядочиванию данных и их отношений. Всего таких форм 6 + две вне , Бойса-Кодда и доменно-ключевая.
На практике редко нормализуют дальше 3-ей нормальной формы. Поподробнее узнать обо всех нормальных формах и теории по ссылкам внизу поста.
Все примеры из книги показаны на бд для форума с обычной для форумов структурой — посты, авторы, время и т.п.
Вот так выглядит схема до преобразования.
Ключи являются составляющей частью нормализованных таблиц. Бывают двух видов — внешние и первичные.
Первичный ключ — это уникальный идентификатор, отвечающий следующим условиям:
Внешние ключи — это ссылки на первичные ключи других таблиц, которые удовлетворяют условиям выше.
Для начала нормализации следует указать хотя бы один первичный ключ. В примере это будет message ID.
Для указания первичного ключа, надо найти поле, которое будет подходить под все три условия. Если такого поля нет, его надо создать. В идеале, поле должно иметь тип integer.
Отношения — это указатели, которые показывают, как соотносятся данные в одной таблице с данными в другой. Проще говоря, ссылка с одного столбца первой таблицы на другой столбец второй таблицы. Бывают трех видов — один-к-одному, один-к-многим, многие-к-многим.
Отношение один-к-одному означает что поле1 соотносится с полем2. Пример — у каждого человека свой номер паспорта. 1 человек- 1 паспорт. Тут отношение один-к-одному.
Один-к-многим указывает, что поле1 может соотносится как с полем2, так и с полем3, полемN… В книге приведен пример — у одного мужчины может быть много женщин и наоборот Один-к-многим самая распространенная связь между таблицами в нормализованных базах.
Отношение многие-к-многим бывает, когда нескольким значениям из одной таблицы соответствует несколько значений другой таблицы. Например, в категории блога может быть много постов, а у блога может быть много категорий. Еще такое отношение может встречаться в составных ключах. Такой связи следует избегать, поскольку она ведет к избыточности данных. В том-же вордпрессе категории и посты соотносятся через третью таблицу — wp_relationships.
На рисунке приведены условные обозначения всех трех отношений в нотации UML.
Создание структуры базы данных (схема) и отношений между таблицами можно ускорить, использую различные CASE-средства. В конце будет ссылка на mysql workbench, бесплатную кроссплатформенную программу.
Как писалось выше, нормализация — это приведение структуры бд в порядок в соответствии с несколькими правилами. Правилам нужно следовать точно, и приводить к формам нужно в порядке их следования.
Чтобы привести таблицу к 1НФ, нужно соблюсти два правила:
Например, если таблица содержит в одном поле полный адрес человека (улица, город, почтовый код), не будет отвечать правилам 1НФ, поскольку будет содержать различные значения в одном столбце, что будет нарушением правила об атомарности. Или если бд содержит данные о фильмах и в ней есть столбцы актер1, актер2, актер3, также не будет отвечать правилам, поскольку будет иметь место повторению данных.
Начинать нормализацию следует с проверки структуры бд на совместимость с 1НФ. Все столбцы, которые не являются атомарными, должны быть разбиты на составляющие их столбцы. Если в таблице есть повторяющиеся столбцы, то им нужно выделить отдельную таблицу.
проблема избыточных полей
решение проблемы избыточных полей
Подсказки
Следующий пост будет посвящен второй нормальной форме — 2НФ.
Для приведения таблиц ко второй нормальной форме (2НФ), приводимые таблицы должны быть уже в 1НФ. Нормализация должна проходить по порядку.
Теперь, во второй нормальной форме, должно быть соблюдено условие — любой столбец, который не является ключом (в том числе внешним), должен зависеть от первичного ключа. Обычно такие столбцы, имеющие значения, который не зависят от ключа, легко определить. Если данные, содержащиеся в столбце, не имеют отношения к ключу, который описывает строку, то их следует отделять в свою отдельную таблицу. В старую таблицу надо возвращать первичный ключ.
На рисунке выше и названия фильмов и имена актеров нарушают правила 2НФ (сами не являются ключами и не зависят от первичного ключа).
После всех преобразований, база данных с фильмами будет иметь минимум 4 таблицы.
Чтобы привести бд ко второй нормальной форме, потребовалось 4 таблицы. Режиссеры (directors), представлены в фильмах (movies) через внешний ключ director ID, фильмы в таблице фильмы-актеры (movies-actors) через movie ID, актеры через actor ID
Каждое имя режиссера, название картины и имя актера хранится только один раз и все неключевые поля зависят от первичного ключа их собственной таблицы.
По факту, нормализация может быть утрированно названа процессом создания все новых и новых таблиц до тех пор, пока избыточность и повторения не будут полностью уничтожены.
Чтобы привести эту базу к 2НФ, нужно 3 таблицы минимум
Для каждой таблицы нужен свой первичный ключ
Чтобы соотнести три таблицы, в messages добавлено два внешних ключа, ведущих на первичные ключи в своих таблицах
База данных будет находиться в третьей нормальной форме, если она приведена ко второй нормальной форме и каждый не ключевой столбец независим друг от друга. Если следовать процессу нормализации правильно до этой точки, с приведением к 3НФ может и не возникнуть вопросов. Следует знать, что 3НФ нарушается, если изменив значение в одном столбце, потребуется изменение и в другом столбце. В примере с форумом (рисунок вверху), проблем с приведением к 3НФ не возникнет, но можно рассмотреть как образец гипотетическую ситуацию, где это может произойти.
Возьмем, как образец, одиночную таблицу, которая хранит некую информацию о бизнес клиентах: имя, фамилию, телефон, адрес, город, штат, почтовый индекс и все в этом духе. Такая таблица не будет находится в 3НФ, поскольку тут много полей будет взаимозависимо — улица будет зависеть от города, город от штата, почтовый индекс тоже под вопросом. Все эти поля будут подчинены друг другу, а не человеку, к которому относится эта запись.
Чтобы нормализовать такую базу, нужно создать по таблице для штатов, городов (с внешним ключом, ведущим в таблицу штатов) и для почтовых кодов. Все они будут ссылаться назад на клиентскую таблицу.
Если вы чувствуете, что все эти действия могут быть излишними, вы правы. Честно, в верхних уровнях нормализации часто нет необходимости. Смысл в том, что нужно стараться нормализовать базу данных, но иногда приходиться идти на уступки ради того, чтобы не допустить чрезмерного усложнения. Потребности приложения и структура данных в базе подскажут, насколько потребуется проводить процесс нормализации.
Как уже говорилось, пример с форумом уже достаточно нормализован, но все равно опишем шаги для нормализации для третьей нормальной формы, показав как исправить пример с клиентами.
1. Определить, в каких полях каких таблиц имеется взаимозависимость. Как только что говорилось, поля, которые зависят больше друг от друга (как город от штата), чем от ряда в целом. В базе форума такой проблемы нет. Взглянув на таблицу сообщений, увидите, что каждый заголовок, каждое тело сообщения относится к своему message ID.
2. Создайте соответствующие таблицы. Если есть проблемный столбец в шаге 1, создавайте раздельные таблицы для него. Как города и штаты, в примере с клиентами.
3. Создайте или выделите первичные ключи. Каждая таблица должна иметь первичный ключ. Для примера с клиентами это будут city ID и state ID.
4. Создайте необходимые внешние ключи, которые образуют любое из отношений. В нашем примере нужно добавить state ID в таблицу городов и city ID в таблицу клиентов. Это свяжет каждого клиента с городом и штатом, где они живут.
Рассмотренная в качестве примера база данных с записями о клиентах, созданы две новых таблицы для хранения информации о городах и штатах.
Вообще, можно было бы и не нормализовывать базу с клиентами до такой степени. Если оставить города и штаты в таблице клиентов, самое страшное, что могло бы случиться — если бы город изменил название, нужно было бы менять его во всех записях о клиентах, которые живут в этом городе. Но города редко меняют свои имена.
Несмотря на то, что имеются правила как нормализовывать базы данных, разные люди сделают это разными способами. Проектирование баз данных допускает личные предпочтения и интерпретации. Важно, чтобы в базе не было явных нарушений нормальных форм, которые могут привести в дальнейшем к проблемам.
Убедившись, что база данных в 3НФ поможет гарантировать надежность и жизнеспособность, не нужно полностью нормализовывать все базу, с которыми вы работаете. Перед тем, как использовать эти методы, имейте ввиду, что это может иметь долгосрочные разрушающие последствия.
Две основных причины, чтобы нарушить правила нормализации — удобство и быстродействие. Меньшим число таблиц проще управлять, чем большим. Кроме того, из-за более сложного характера, нормализованные таблицы более медленные для обновления, изменения и выдачи данных. Вкратце, нормализация это сделка между целостностью/расширяемостью и простотой/скоростью. С другой стороны, есть достаточно способов чтобы улучшить производительность базы данных, но не так много способов чтобы исправить поврежденные данные, возникшие из-за плохого дизайна структуры.
Практика и опыт подскажут, как сделать модель базы данных, но лучше совершайте ошибки пробуя нормальные формы, хотя бы до тех пор, пока не поймете принцип.
А как ты думаешь, при улучшении реляционные бд, будет лучше нам? Надеюсь, что теперь ты понял что такое реляционные бд, нормальный вид, нормализация, нормальная форма, кортеж , аномалии-добавления, аномалии-удаления, аномалии-модификации и для чего все это нужно, а если не понял, или есть замечания, то не стесняйся, пиши или спрашивай в комментариях, с удовольствием отвечу. Для того чтобы глубже понять настоятельно рекомендую изучить всю информацию из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Комментарии
Оставить комментарий
Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL
Термины: Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL