Как привести таблицу к 3 нормальной форме

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

Таблица находится в третьей нормальной форме, если она находится во второй нормальной форме, и при этом любой её неключевой атрибут функционально зависит только от первичного ключа. Или что тоже самое — «нет зависимостей неключевых атрибутов от других неключевых атрибутов + 2НФ».

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

Пример приведения таблицы к третьей нормальной форме

В результате приведения к 3NF получим две таблицы:

Эта нормальная форма вводит дополнительное ограничение по сравнению с 3НФ. Определение нормальной формы Бойса-Кодда:

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

Ситуация, когда отношение будет находится в 3NF, но не в BCNF, возникает при условии, что отношение имеет два (или более) возможных ключа, которые являются составными и имеют общий атрибут. Заметим, что на практике такая ситуация встречается достаточно редко, для всех прочих отношений 3NF и BCNF эквивалентны.

Многозначные зависимости (МЗ). Определение. Свойства и аксиомы МЗ. Четвертая нормальная форма (4НФ) отношения. Характеристика отношения в 4НФ.

Многозначные зависимости и четвертая нормальная форма (4nf).

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

Многозначная зависимость является обобщением функциональной зависимости и рассматривает соответствия между множествами значений атрибутов.

В качестве примера рассмотрим отношение ПРЕПОДАВАТЕЛЬ (ИМЯ, КУРС, УЧЕБНОЕ_ПОСОБИЕ), хранящее сведения о курсах, читаемых преодавателем, и написанных им учебниках. Пусть профессор N читает курсы «Теория упругости» и «Теория колебаний» и имеет соответствующие учебные пособия, а профессор K читает курс «Теория удара» и является автором учебников «Теория удара» и «Теоретическая механика». Тогда наше отношение будет иметь вид:

| ИМЯ | КУРС | УЧЕБНОЕ_ПОСОБИЕ |

| N | Теория упругости | Теория упругости |

| N | Теория колебаний | Теория упругости |

| N | Теория упругости | Теория колебаний |

| N | Теория колебаний | Теория колебаний |

| K | Теория удара | Теория удара |

| K | Теория удара | Теоретическая механика |

| K | Теория упругости | Теория удара |

| K | Теория упругости | Теоретическая механика |

Это отношение имеет значительную избыточность и его использование приводит к возникновению аномалии обновления. Например, добавление информации о том, что профессор K будет также читать лекции по курсу «Теория упругости» приводит к необходимости добавить два кортежа (по одному для каждого написанного им учебника) вместо одного. Тем не менее, отношение ПРЕПОДАВАТЕЛЬ находится в NFBC (ключевой атрибут — ИМЯ).

Заметим, что указанные аномалии исчезают при замене отношения ПРЕПОДАВАТЕЛЬ его проекциями:

| ИМЯ | КУРС | | ИМЯ | УЧЕБНОЕ_ПОСОБИЕ |

| N | Теория упругости | | N |Теория упругости |

| N | Теория колебаний | | N |Теория колебаний |

| K | Теория удара | | K |Теоретическая механика |

| K | Теория упругости | | K |Теория удара |

Аномалия обновления возникает в данном случае потому, что в отношении ПРЕПОДАВАТЕЛЬ имеются:

зависимость множества значений атрибута КУРС от множества значений атрибута ИМЯ

зависимость множества значений атрибута УЧЕБНОЕ_ПОСОБИЕ от множества значений атрибута ИМЯ.

Такие зависимости и называются многозначными и обозначаются как

ИМЯ ->> КУРС ИМЯ ->> УЧЕБНОЕ_ПОСОБИЕ

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

ИМЯ ->> КУРС | УЧЕБНОЕ_ПОСОБИЕ

Очевидно, что каждая функциональная зависимость является многозначной, но не каждая многозначная зависимость является функциональной.

Определение четвертой нормальной формы:

Отношение находится в 4NF если оно находится в BCNF и в нем отстутсвуют многозначные зависимости, не являющиеся функциональными зависимостями.

Общая характеристика языка SQL. Стандарты SQL, способы его реализации. Структура языка SQL.

Язык SQL, предназначенный для взаимодействия с базами данных, появился в середине 70-х гг. и был разработан в компании IBM в рамках проекта экспериментальной реляционной СУБД System R. Исходное название языка SEQUEL (Structured English Query Language) только частично отражало суть этого языка. Конечно, язык был ориентирован главным образом на удобную и понятную пользователям формулировку запросов к реляционным БД. Но, в действительности, он почти с самого начала являлся полным языком БД, обеспечивающим помимо средств формулирования запросов и манипулирования БД следующие возможности:

средства определения и манипулирования схемой БД;

средства определения ограничений целостности и триггеров;

средства определения представлений БД;

средства определения структур физического уровня, поддерживающих эффективное выполнение запросов;

средства авторизации доступа к отношениям и их полям;

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

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

Как привести таблицу к 3 нормальной форме

Третья нормальная форма предполагает, что каждый столбец, не являющийся ключом, должен зависеть только от столбца, который является ключом, то есть должна отсутствовать транзитивная функциональная зависимость (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:

Процедура нормализации данных и нормальные формы данных (НФБК, 3НФ).

Продолжим нормализацию и попробуем привести некоторые таблицы к 3НФ, для начала приведу определение 3НФ, НФБК а также те определения и примеры которые считаю важными (далее цитаты из книги Дж. Дейта):

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

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

(в определении передполагается наличие только одного потенциального ключа, который является первичным ключом)

Переменная отношения находится в 3нф тогда , когда она находится во 2й нф и ни один неключевой атрибут не является транзитивно зависимым от ее первичного ключа (под эти подразумевается отсутствие в переменной отношения транзитивных зависимостей). Это означает что в ней отсутствуют какие либо взаимные зависимости в указанном выше смысле

Нужно стремиться к независимости отдельных проекций, т.е R1 должна не зависеть от R2 (мы могли бы разделить R1, R2, но в таком случае это были бы не независмые отношенияб так как мы потеряем зависимость что B -> C)

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

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

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

(более строгая чем 3НФ, для случаев составных ключей)

Она определяется для данных для которых верны следующие условия:

переменная отношения имеет 2 и больше потенциальных ключа, таких что
Эти ключи являются составными
Два или больше составных ключей перекрываются, т.е. имеют 1 общий атрибут.

Переменная отношения находится в нормальной форме Бойса-Кодда тогда и только тогда, когда детерминанты всех ее функц зависимостей являются потенциальными ключами. НФБК позволяют избавиться от проблем присущим в 3НФ (может присутствовать некоторая избыточность, которая приводит к проблемам insert/delete/update) и плюс то что определение не содержит ссылок на 1 и 2 нф

Например, как показано в книге, пример:

SP ключи и

находится в 3НФ, но присутствует избыточность,

если надо обновить имя Smith то придется найти все вхождения, или же база придет в противоречивое состояние, когда в одной строке будет S1 = Smith, а в другой S1 != Smith

лучше разбить SP на 2 проекции

SS

SP

SS

SP

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

Итак, после всего можно привести определение 3НФ (без ограничения) и НФБК Предположим что есть переменная отношения R , что Х является некоторым подмножество атрибутов этой переменной отношения R и что А является некоторым отдельным атрибутом переменной отношения R. Переменная отношения R находится в 3НФ тогда и только тогда, когда для каждой функциональной зависимости X -> A в переменной отношения R верно по крайней мере одно из следующих высказываний:

Подмножество Х включает атрибут А (т.е функц связ тривиальна)

Подмножество Х является суперключом переменной отношения R1

Атрибут А входит в состав некоторого потенциального ключа переменной отношения R.

Если исключить 3 утверждение получится НФБК, которая является более строгим ограничением по сравнению с 3НФ, и является причиной ввода НФБК.

Вернемся к нашему примеру. К данному моменту мы имеем уже 8 следующиx таблиц:

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

Добавим столбец address.city_id как внешний ключ ссылку на city.city_id. Обновим записи таблицы ADDRESS и удалим затем столбец CITY .

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

Это я думаю наглядный пример приведения к 3НФ, мы нашли транзитивную связь и пытаемся избавиться от нее (поправьте если я неправ).

Создадим таблицу почтовых индексов и справочник почтовых индексов:

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

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

Теперь поправим таблицу ADDRESS :

Удалим столбец zip_code, он нам уже не нужен:

Добавим ограничение на столбец street пусть он будет внешним ключом ссылающимся на первичный ключ zip_code_catalog.street

Выглядит теперь таблица ADDRESS так:

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

Мы создали справочник улиц принадлежащих какому то из почтовых индексов, опять же, контролируем название улицы, мы не сможем добавить 2 одинаковые улицы с какими то индексами в справочник zip_code_catalog (мы условились, что 1 улице можно присвоить 1 уникальный индекс)

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

Tаблицу SALON которая находится в НФБК в дальнейшем можно декомпозировать к 1 столбцу, который и будет являться первичным ключом, маловерятно что может понадобиться иметь 2 одинаковых имен салонов, но я оставляю уникальность по цифровому первичному ключу, так как удобнее работать. Хотя это и некоторая избыточность.

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

Я попробовал несколько продуктов, например draw.io, но остановился на DBDesigner, мне понравилось, бесплатный, быстро и красиво.

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

Немного жаль что никак специально не отображается визуально связь ОДИН К ОДНОМУ, например у нас такая связь обозначена между таблицами PERSON и ADDRESS для поля address_id в таблице PERSON мы включили уникальность, и в редакторе тоже это указано, но графически это ничем не отличается от изображения FOREIGN KEY .

Напомню как выглядит связь ОДИН К ОДНОМУ:

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

Связь ОДИН КО МНОГИМ

В нашем случае связи созданные как внешний ключ ссылающийся на первичный ключ и являются отражением связи ОДИН КО МНОГИМ. Например поле zip_code в zip_code_catalog является внешним ключом ссылающимся на уникальный ключ (первичный ключ) таблицы zip_code. Чем мы собственно хотим указать в таблице zip_code_catalog, что несколько уникальных улиц могут ссылаться на один и тот же индекс. (один и тот же индекс -> много улиц).

Связь МНОГИЕ КО МНОГИМ

В нашем случае это таблицы PARTNER и ORDERS . В таблице PARTNER мы таким образом выражаем ситуацию что много PERSON могут быть поставщиками SALON , т.е. другими более человечными словами — любой человек может поставлять товары в любой салон. И в этой таблице мы отображаем что человек с PERSON_ID является поставщиком какого то салона с SALON_ID, т.е фактически партнер.

Cвязь МНОГИЕ КО МНОГИМ образуется через связующую таблицу, которой является в данном случае partner.

Выводы

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

Каждая переменная отношения на некотором уровне нормализации соответствует условиям более низких уровней нормализации. (переход ко 2й форме возможен если отношение приведено к 1й форме)

Всегда можно выполнить приведение к НФБК.

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

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

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

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

Related Posts:

Comments

About Alexander Kamyanskiy
I love Python and happy to use it everyday for develop Web applications, writing tests and others cool things.

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

Набрёл на перевод про третью нормальную форму (ссылка в конце). Перевод вроде бы неплохой.
Далее цитата.

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

Попала в руки одна замечательная книжка — PHP 6 and MySQL 5 for Dynamic Web Sites , за авторством Larry Ulman. В целом, книга расчитана на новичков — середнячков, но затрагиваются и довольно серьёзные вещи, при чем объясняется весьма доходчивым языком.

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

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

На практике редко нормализуют дальше 3-ей нормальной формы. Поподробнее узнать обо всех нормальных формах и теории по ссылкам внизу поста.

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

Вот так выглядит схема до преобразования.

Ключи

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

Первичный ключ — это уникальный идентификатор, отвечающий следующим условиям:

Он должен иметь значение, не NULL.

Быть неизменным — значение ключа не должно меняться.

Иметь уникальное значение для каждой строки.

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

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

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

Отношения

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

Отношение один-к-одному означает что поле1 соотносится с полем2. Пример — у каждого человека свой номер паспорта. 1 человек- 1 паспорт. Тут отношение один-к-одному.

Один-к-многим указывает, что поле1 может соотносится как с полем2, так и с полем3, полемN… В книге приведён пример — у одного мужчины может быть много женщин и наоборот Автор юморист Один-к-многим самая распространённая связь между таблицами в нормализованных базах.

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

На рисунке приведены условные обозначения всех трех отношений в нотации UML.

Создание структуры базы данных (схема) и отношений между таблицами можно ускорить, использую различные CASE-средства. В конце будет ссылка на mysql workbench, бесплатную кроссплатформенную программу.

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

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

Чтобы привести таблицу к 1НФ, нужно соблюсти два правила:

Атомарность или неделимость. Каждая колонка должна содержать одно неделимое значение.

Таблица не должна содержать повторяющихся колонок или групп данных.

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

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

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

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

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

Выносите повторяющиеся данные в отдельную таблицу. В примере с форумом такой проблемы нет, поэтому возьмем в качестве примера таблицу, содержащую информацию о фильмах. Там есть несколько полей actor, которые являются повторяемыми. Повторяемые поля тут несут две проблемы. Если хранить информацию об актерах таким образом, то их число будет лимитировано числом таблиц. Даже если их будет 100, то все равно это будет пределом для некоторых фильмов. И вторая проблема — будет большое количество пустых(NULL) ячеек для большинства остальных записей, чего также следует избегать. Решением этой проблемы станет создание отдельной таблицы для актеров, куда будет заносится информация обо всех необходимых фильмах. Имена актеров также разбиты, чтобы соблюсти атомарность. Также в этой таблице присутствует свой первичный ключ, что является необходимым условием для нормализации.

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

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

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

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

Для приведения таблиц ко второй нормальной форме (2НФ), приводимые таблицы должны быть уже в 1НФ. Нормализация должна проходить по порядку.

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

На рисунке выше и названия фильмов и имена актеров нарушают правила 2НФ (сами не являются ключами и не зависят от первичного ключа).

После всех преобразований, база данных с фильмами будет иметь минимум 4 таблицы.

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

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

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

Определить все столбцы, которые не находятся в прямой зависимости от первичного ключа этой таблицы. На рисунке выше у таблиц users и forums нет первичного ключа. У таблицы messages первичный ключ — message ID, от которого зависят все остальные поля этой таблицы.

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

Создаем внешние ключи и обозначаем их отношения между таблицами. Конечным шагом нормализации до 2НФ будет являться выделение внешних ключей для связи с ассоциированными таблицами. Первичный ключ одной таблицы должен быть внешним ключом в другой. На рисунке снизу показана связь между ключами трех таблиц. Поле user ID таблицы messages является первичным ключом поля user ID таблицы users. Тип связи между ними — один ко многим. Один пользователь может оставить много сообщений, но у сообщения может быть только один пользователь. Такая же связь соединяет таблицы forums и messages через forum ID. У форума может быть много сообщений, но сообщение может находиться только в одном форуме.

Подсказки:

Другой способ приведения схемы к 2НФ — посмотреть на отношения между таблицами. Идеальный вариант — создать все отношения вида один-к-многим. Отношения вида многие-к-многим нуждаются в реструктуризации.

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

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

Чтобы упростить нормализацию, помните, что при приведении к 1НФ вы ищете дубли горизонтально (дубли столбцов), а при приведении к 2НФ — вертикально (дубли рядов).

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

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

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

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

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

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

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

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

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

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

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

Подсказки:

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

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

Нарушения правил нормализации

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

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

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