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

Содержание:

2НФ — вторая нормальная форма

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

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

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

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

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

Чтобы привести базу к третьей нормальной форме, надо:

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

2. Создайте соответствующие таблицы. Если есть проблемный столбец в шаге 1, создавайте раздельные таблицы для него. Как города и штаты, в примере с клиентами.

3. Создайте или выделите первичные ключи. Каждая таблица должна иметь первичный ключ. Для примера с клиентами это будут city ID и state ID.

4. Создайте необходимые внешние ключи, которые образуют любое из отношений. В нашем примере нужно добавить state ID в таблицу городов и city ID в таблицу клиентов. Это свяжет каждого клиента с городом и штатом, где они живут.

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

Подсказки:

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

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

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

Описание шестой нормальной формы (6NF)

Шестая нормальная форма (6NF) была введена при работе с хронологическими базами данных.

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

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

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

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

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

Теперь стоит напомнить, что пятая нормальная форма основана на так называемых зависимостях соединения.

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

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

Шестая нормальная форма вводит такое понятие как «Декомпозиция до конца», т.е. максимально возможная декомпозиция таблиц.

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

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

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

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

Зачем выполнять нормализацию Нормативно-справочной информации (НСИ)

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

  • Контрагенты. Этот домен данных может включать НСИ: Клиенты, Сотрудники, Поставщики, Пациенты, Партнеры, Потенциальные клиенты, Арендаторы, Заказчики, Компании в управлении.
  • Номенклатура. Этот домен данных может включать НСИ: Материально-технические ресурсы (МТР), Товарно-материальные ценности (ТМЦ), Материально-производственные запасы (МПЗ), Продукция.

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

Сначала поймите разницу между дисперсией, стандартным отклонением и среднеквадратичной ошибкой.

Дисперсия (дисперсия)

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

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

E

(

x

)

E(x)
E(x), Тогда его дисперсия выражается как данные и

E

(

x

)

E(x)
E(x)Сумма квадратов разностей

x

E

(

x

)

2

\sum^2
∑x−E(x)2, А затем найти его ожидание (среднее), чтобы получить

D

(

x

)

=

x

E

(

x

)

2

D(x)=\sum^2
D(x)=∑x−E(x)2

Зачем использовать стандартное отклонение

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

σ

=

D

(

x

)

\sigma = \sqrt {D(x)}
σ=D(x)​, Дисперсия и стандартное отклонение имеют общее свойство: чем больше значение, тем более пологая кривая распределения, то есть более разбросанная. Поскольку данные являются случайными, предполагая, что такое же распределение основано на центральной предельной теореме, данные подчиняются распределению Гаусса (нормальному) (типичным примером является ошибка). Давайте посмотрим на область распределения. При использовании стандартного отклонения мы можем четко увидеть вероятность того, что данные принадлежат определенному значению. (Когда мы обрабатываем функции, мы можем отфильтровать выбросы на основе этого)

MinMaxScaler: приведение к диапазону [0,1]

MinMaxScaler в PySpark применяется для шкалирования в диапазоне . Рассчитывается как

X_std = (X - X.min(axis=0)) / (X.max(axis=0) - X.min(axis=0))
X_scaled = X_std * (max - min) + min
# при min=0, max=1 => X_std = X_scaled

где min и max задаются как минимальное и максимальное допустимое значение, по умолчанию min=0, max=1. Вот так выглядит Python-код для такого вида нормализации:

from pyspark.ml.feature import MinMaxScaler


scaler = MinMaxScaler(inputCol="features", outputCol="scaledFeatures")
scalerModel = scaler.fit(dataFrame)
scaledData = scalerModel.transform(dataFrame)
print("Features scaled to range: " % (scaler.getMin(), scaler.getMax()))
scaledData.select("features", "scaledFeatures").show(truncate=False)

Результат нормализации данных в PySpark:

Features scaled to range: 
+--------------+-----------------------------------------------------------+
|features      |scaledFeatures                                             |
+--------------+-----------------------------------------------------------+
||                                              |
| ||
||                                              |
+--------------+-----------------------------------------------------------+

Цели

Основная цель первой нормальной формы, определенной Коддом в 1970 году, состояла в том, чтобы разрешить запрашивать данные и манипулировать ими с использованием «универсального подъязыка данных», основанного на логике первого порядка . ( SQL является примером такого подъязыка данных, хотя Кодд считал его серьезно несовершенным.)

Цели нормализации за пределами 1NF (первая нормальная форма) были сформулированы Коддом следующим образом:

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

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

Удаления аномалия . Вся информация о докторе Гидденсе теряется, если он или она временно перестают участвовать в каких-либо курсах.

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

  • Аномалия обновления. Одна и та же информация может быть представлена ​​в нескольких строках; поэтому обновления отношения могут привести к логическим несоответствиям. Например, каждая запись в отношении «Навыки сотрудников» может содержать идентификатор сотрудника, адрес сотрудника и квалификацию; таким образом, изменение адреса для конкретного сотрудника может потребоваться применить к нескольким записям (по одной для каждого навыка). Если обновление было успешным только частично — адрес сотрудника обновляется в некоторых записях, но не в других — тогда отношение остается в несогласованном состоянии. В частности, отношение дает противоречивые ответы на вопрос о том, каков адрес конкретного сотрудника. Это явление известно как аномалия обновления.
  • Аномалия вставки. Есть обстоятельства, при которых некоторые факты вообще не могут быть зафиксированы. Например, каждая запись в отношении «Факультет и их курсы» может содержать идентификатор факультета, название факультета, дату найма преподавателя и код курса. Следовательно, сведения о любом преподавателе, который преподает хотя бы один курс, могут быть записаны, но только что нанятый преподаватель, которому еще не назначено преподавать какие-либо курсы, не может быть записан, кроме как путем установки кода курса равным нулю . Это явление известно как аномалия вставки.
  • Аномалия удаления. При определенных обстоятельствах удаление данных, представляющих определенные факты, влечет за собой удаление данных, представляющих совершенно разные факты. Отношение «Преподаватели и их курсы», описанное в предыдущем примере, страдает от аномалии этого типа, поскольку, если преподаватель временно перестает быть назначенным на какие-либо курсы, последняя из записей, в которых появляется этот преподаватель, должна быть фактически удалена. также удаление преподавателя, если в поле Код курса не задано значение null. Это явление известно как аномалия удаления.

Минимизация редизайна при расширении структуры базы данных

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

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

Взаимодействие с клиентами при выполнении проектов

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

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

Если же в силу каких-то причин использование WEB-интерфейсов нежелательно, то при выполнении проектов взаимодействие может вестись с применением табличных файлов Excel и электронной почты.

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

Нормализация НСИ Контрагенты

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

1. Все карточки Контрагентов заполнены полно и без ошибок в реквизитах

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

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


Рис. 5. Карточка контрагента до и после проведения нормализации.

2. Проведена дедубликация в карточках контрагентов

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

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

Эффекты от нормализации домена данных Контрагенты

Наши клиенты отмечают следующие позитивные эффекты:

  • возможность ведения централизованной истории взаимодействия по каждому контрагенту без потерь информации;
  •  безошибочное построение любых отчетов, использующих информацию о контрагентах;
  • избежание негативных ситуаций и попадания в «черные списки», например, при работе (e-mail рассылка, обзвон) с существующей клиентской базой, в которой один и тот же клиент, ввиду ошибок, присутствует n-ое количество раз.

Инфологическое проектирование

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

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

Один из популярных способов построения инфологической модели – построение ER-диаграмм.

ER-диаграммы

В отличие от диаграмм атрибутов, ER-диаграммы, кроме непосредственно атрибутов, включают так же в явном виде “сущности” и “связи” между ними, откуда, собственно, и происходит название: entity-relationship diagram, или диаграмма сущности-связи.

И сущности, и связи могут обладать набором атрибутов. Сущности без атрибутов – явление достаточно бессмысленное, как Кантовская “вещь в себе”. Связи без атрибутов – явление, напротив, весьмя распространенное.

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

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

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

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

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

Рассмотрим ER-диаграмму для примера с теннисными кортами.

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

Чтобы стало еще понятней, давайте закрепим знания и рассмотрим классический пример, который обычно используется в литературе для пояснения четвертой нормальной формы.

Таблица связей студентов, курсов и хобби.

Студент Курс Хобби
Иванов И.И. SQL Футбол
Иванов И.И. Java Хоккей
Сергеев С.С. SQL Волейбол
Сергеев С.С. SQL Теннис
John Smith Python Футбол
John Smith Java Теннис

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

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

Первичный ключ здесь также составной и состоит он из всех трех столбцов.

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

Таким образом, мы можем наблюдать в этой таблице нетривиальную многозначную зависимость

Студент ->-> Курс

Студент ->-> Хобби

Поэтому эта таблица не находится в четвертой нормальной форме.

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

Допустим, нам необходимо получить информацию о хобби студентов, которые посещают курс по SQL. Очевидным действием станет выборка с условием Курс = SQL, в результате мы получим 3 хобби: футбол, волейбол и теннис.

Результат выборки. Хобби студентов, которые посещают курс по SQL.

Студент Курс Хобби
Иванов И.И. SQL Футбол
Сергеев С.С. SQL Волейбол
Сергеев С.С. SQL Теннис

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

Чтобы нормализовать эту таблицу, мы должны точно так же, как и в предыдущем примере, разбить ее на две.

 Связь студентов и курсов.

Студент Курс
Иванов И.И. SQL
Иванов И.И. Java
Сергеев С.С. SQL
John Smith Python
John Smith Java

Связь студентов и хобби.

Студент Хобби
Иванов И.И. Футбол
Иванов И.И. Хоккей
Сергеев С.С. Волейбол
Сергеев С.С. Теннис
John Smith Футбол
John Smith Теннис

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

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

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

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

Обычно во всех источниках приводится два основных глобальных преимущества:

  • Устранение аномалий
  • Повышение производительности

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

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

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

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

Полностью нормализованная база данных – это плохая база данных.

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

На сегодня это все, надеюсь, материал был Вам полезен, пока!

Нравится48Не нравится

Проецирование функциональных зависимостей

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

Предположим, что имеется исходное отношение \(R\) с множеством ФЗ \(F\), и пусть \(S\) – некая проекция отношения \(R\): \ где A – некое множество атрибутов.

Тогда, множество \(G\) ФЗ, которые останутся в \(S\), это ФЗ, которые:

  1. Следуют из \(F\)
  2. Включают только атрибуты, принадлежащие \(A\)

Вполне вероятно, что множество всех ФЗ такого рода избыточно (не минимально). Сложность алгоритма поиска ФЗ отношения \(S\) в худшем случае экспоненциально зависит от количества атрибутов в \(A\).

Для нахождения всех ФЗ можно применять замыкание атрибутов из \(A\) по \(F\). Следует сделать два достаточно очевидных замечания:

  • Замыкания пустого множества и множества всех атрибутов не приводят к получению нетривиальных ФЗ
  • Если \(A \subset X^+\), то построение замыканий для надмножеств \(X\) не даст новых нетривиальных ФЗ в силу правила дополнения.

Так же понятно, что для любого замыкания \(X^+\), существуют ФЗ вида \(X \to B\), где \(B \subset X^+\).

Таким образом, мы можем начать с построения замыканий для единичных множеств атрибутов, и добавить все следующие из них ФЗ к множеству ФЗ \(G\), если они содержат только атрибуты из \(A\), а затем, при необходимости, построить замыкания для множеств атрибутов большей размерности.

Пример:

Пусть отношение \(R(A,B,C,D)\) имеет следующие ФЗ:

  • \(A \to B\)
  • \(B \to C\)
  • \(C \to D\)

Пусть теперь мы получаем проекцию \(S = \pi_{A,C,D} R\). Найдем ФЗ \(G\) отношения \(S\).

Для этого, построим замыкания для всех атрибутов отношения \(S\) по \(F\). Поскольку \(B\) не входит в отношение \(S\), его замыкание не даст нам ФЗ, входящих в \(G\). \ \ \

Можем заметить, что \({A,C,D} \subset {A}^+\), соответственно, рассмотрение надмножеств \({A}\) не имеет смысла. Следовательно, единственное неединичное множество атрибутов, требующее рассмотрения это \

Запишем множество нетривиальных ФЗ \(S\), получающиеся из этих замыканий: \ \ \

Теперь найдем минимальное множество ФЗ. По правилу транзитивности, ФЗ \(A \to D\) следует из двух других, поэтому его можно исключить. В итоге, получаем минимальное множество ФЗ \(S\): \ \

Алгоритм Бернштейна построения схемы БД в НФЭК по множеству ФЗ

Филип Бернштейн предложил алгоритм построения схемы в 3НФ по ФЗ в 1976 г. Позже, Заниоло показал, что схема, построенная по этому алгоритму так же находится в НФЭК.

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

Алгоритм может быть описан следующим образом:

Пусть дано множество \(F\) нетривиальных ФЗ. Тогда:

  1. Удалить избыточные атрибуты в детерминантах (левых частях) каждой ФЗ. Получить множество ФЗ \(G\).
  2. Построить неизбыточное покрытие \(H\) для \(G\) (минимизировать \(G\))
  3. Разбить \(H\) на группы таким образом, чтобы левые части ФЗ в каждой группе имели одинаковые левые части.
  4. Объединить эквивалентные ключи. Для каждых двух групп \(H_i\) и \(H_j\), имеющих левыми частями соответственно \(X_i\) и \(X_j\), объединить их, если в \(H^+\) существуют ФЗ \(X_i \to X_j\) и \(X_j \to X_i\).
  5. Составить отношения. Для каждой группы, составить отношение, содержащее атрибуты этой группы. Ключом каждого отношения будут атрибуты детерминанта группы.

Избыточным атрибутом в детерминанте ФЗ \(g \in G\), \(g = X_1, \ldots, X_p \to Y\), является атрибут \(X_i\), если \(G^+\) содержит ФЗ \(X_1, \ldots,X_{i-1}, X_{i+1}, \ldots, X_p \to Y\).

Иначе, для ФЗ \((A \to B) \in G\), атрибут \(a\in A\) является избыточным, если \(a\in (A-\{a\})^+_ G\).

Виды связей между отношениями

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

Внешний ключ
Набор атрибутов \(A\) отношения \(R\) называется внешним ключом, если тот же набор атрибутов \(A\), либо некое переименование \(\rho_B A\), является суперключом некого другого отношения \(S\), причем множество значений \(A\) по всем записям \(R\) в любой момент времени является подмножеством значений \(\rho_B A\) по всем записям \(S\).

Выделяется четыре типа связей:

Один к одному (1:1)

Каждой записи отношения \(R\) соответствует одна и только одна запись отношения \(S\), и наоборот.

Нередко оказывается, что отношения \(R\) и \(S\) можно объединить без каких-либо потерь. В таких случаях, единственной причиной сохранять два отношения может быть связь этих отношений с различными сущностями.

Один ко многим (1:M)
Каждой записи отношения \(R\) соответствует \(M \geq 0\) записей отношения \(S\), но каждой записи отношения \(S\) соответствует только одна запись отношения \(R\).
Многие к одному (M:1)
Каждой записи отношения \(R\) соответствует только одна запись отношения \(S\), но каждой записи отношения \(S\) соответствует \(M \geq 0\) записей отношения \(R\)
Многие ко многим (M:N)
Каждой записи отношения \(R\) соответствует \(M \geq 0\) записей отношения \(S\), и каждой записи отношения \(S\) соответствует \(N \geq 0\) записей отношения \(R\)

Связи так же делятся на идентифицирующие и не идентифицирующие.

Идентифицирующая связь

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

Например, даны отношения Directory = (Id, Name) и File = (Id, Name, DirectoryId), связанные 1:М через внешний ключ File.DirectoryId ⇆ Directory.Id. В данной модели, файл не может существовать без директории, и эта связь является идентифицирующей, поскольку требует существования значения Directory.Id, равного File.DirectoryId.

Не идентифицирующая связь

связь, не являющаяся идентифицирующей.

Например, даны два отношения Account = (Id, Type) и AccountType = (Type, Description), связанные M:1 через внешний ключ Account.Type ⇆ AccountType.Type. В данной модели Type может быть не задан. Такое отношение не будет идентифицирующим, поскольку записи в Account и AccountType могут существовать независимо друг от друга.

Проектирование баз данных

Проектирование баз данных – это процесс концептуализации и реализации базы данных, описывающих некую предметную область, для встраивания в конкретную СУБД.

Обычно выделяют следующие этапы проектирования БД:

  1. Концептуальное (инфологическое) проектирование.
  2. Логическое (даталогическое) проектирование
  3. Физическое проектирование

Рассмотрим эти этапы более подробно.

Требования пятой нормальной формы (5NF)

Это стандартное определение для пятой нормальной формы.

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

Однако на основе этого определения мы можем сделать следующий вывод

Как видите, здесь вводится новое понятие «Зависимость соединения».

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

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

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

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

И здесь вводится еще одно новое понятие «Декомпозиция без потерь».

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

Схематично это выглядит примерно следующим образом.

Допустим, существует таблица T (C1, C2, C3) где C1, C2, C3 – столбцы и вместе они являются составным первичным ключом. Таблица находится в четвертой нормальной форме. В соответствии с требованиями предметной области у нас проявляется зависимость соединения:

{C1, C2}, {C1, C3}, {C2, C3}

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

T1 (C1, C2)

T2 (C1, C3)

T3 (C2, C3)

При этом, если мы соединим (JOIN) эти три новые таблицы (T1, T2, T3) и получим исходную таблицу (T), то это будет означать, что декомпозицию мы выполнили без потерь.

Демормализация в базе данных: «звезда» и «снежинка»

Как можно понять из вышеприведённых примеров, основными целями нормализации являются:

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

Но список заявленных целей касается приложений транзакционных.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock
detector