Нормальные формы. Избыточность данных в базе данных. Транзитивная зависимость. Проектирование баз данных

Здравствуйте, уважаемые посетители моего скромного блога для начинающих вебразработчиков и web мастеров ZametkiNaPolyah.ru. Продолжим рубрику Заметки о MySQL, в прошлой публикации я начал тему проектирования баз данных и затронул вопрос целостности данных и информационной избыточности, то есть избыточности данных. До того, как я начал тему проектирования баз данных были публикации посвященные: видам и типам баз данных, настройки mysql сервера и файлу my.ini, а также установки MySQL сервера.

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

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

Нормальная форма. Избыточность данных в базе данных. Транзитивная зависимость. Проектирование баз данных.

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

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

Приведение базы данных к нормальной форме – это вопрос проектирования баз данных. И приводить к требуемой нормальной форме базу данных следует до того, как вы начали ее реализовывать программно, то есть, до того как начали создавать базу данных в той или иной СУБД, в нашем случае СУБД MySQL.

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

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


Проектирование баз данных. Что такое нормальная форма. Какие нормальные формы бывают(NF).

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

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

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

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

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

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

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

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

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

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

Первая нормальная форма(1NF). Проектирование баз данных. Нормализация отношений в базе данных.

Приступим к разговору о нормальных формах. Первое, что обсудим – первая нормальная форма(1NF), определение первой нормальной формы и то, как привести базу данных к первой нормальной форме. То есть, как нормализовать отношения до первой нормальной формы.

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

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

Самое главное правило первой нормальной формы – атомарность.

Атрибуты – это столбцы таблицы базы данных, а кортежи – это строки таблицы, вы можете встретить термин значение. Помните пример из предыдущей публикации? У нас была таблица, в которой был список преподавателей и список предметов, которые они ведут.

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

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

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

Таблица не была бы в первой нормальной форме, если бы мы написали в столбце предметы для преподавателя Иванова: мат. ан., дискретная математика. Тогда таблица не была бы в первой нормальной форме – это было бы нарушение атомарности. Атомарность – это самое главное правило первой нормальной формы.

Мы знаем, что Гоголь написал «Ревизор» и «Мертвые души», как это записать в таблицу, чтобы она находилась в первой нормальной форме? Да очень просто – создаем отдельную запись для «Ревизора» и отдельную для «Мертвых душ», но тем самым мы дублируем Гоголя Н.В. и намеренно создаем избыточность данных в базе данных. Избавиться от избыточности данных нам поможет вторая нормальная форма.

Вторая нормальная форма(2NF). Проектирование баз данных. Нормализация отношений в базе данных. 

Идем далее и поговорим о второй нормальной форме(2NF) и о том, как нормализовать отношение до второй нормальной формы, как привести базу данных ко второй нормальной форме.

Вторая нормальная форма(2NF). Для начала напишу академическое определение. Таблица находится во второй нормальной форме, ели она находится в первой нормальной форме и при этом любой ее атрибут, не входящий в состав первичного ключа, функционально полно зависит от первичного ключа. Функционально полно означает, что атрибут зависит от всего первичного ключа, но не зависит от его какой-либо части.

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

Теперь по-русски. Правила второй нормальной формы(2NF): у любой таблице есть ключ (ключевой атрибут), любая база данных будет работать без ключей и ключевых атрибутов, но она не будет находиться во второй нормальной форме.

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

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

Для ясности перейдем к более конкретному примеру:

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

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

Данная таблица находится в первой нормальной форме, в этой таблице есть два поля, которые находятся между собой в логической зависимости: Преподаватель и Дата рождения. Глядя на таблицу, мы не можем сказать, что преподаватель Сидоров родился в 1960 году, так как он родился в 1940.

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

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

Нормализация базы данных. Отношение находится во второй нормальной форме.

Нормализация базы данных. Отношение находится во второй нормальной форме.

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

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

Грубо говоря, коды, записанные в таблице, являются ссылками на справочники. Мы как бы говорим СУБД: вот она ссылка перейди по ней в справочник и ищи нужное значение в справочнике.

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

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

Третья нормальная форма(3NF). Транзитивная зависимость. Проектирование баз данных. Нормализация отношений в базе данных.

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

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

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

В таблице преподавателей есть транзитивная зависимость, это атрибуты индекс и город, если индекс 127 – это Москва и никак по-другому быть не может!  Эти два поля не являются первичным ключом, и они зависят друг от друга – это нарушение третьей нормальной формы и называется транзитивная зависимость.

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

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

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

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

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

Нормализация отношений. Третья нормальная форма.

Нормализация отношений. Третья нормальная форма.

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

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

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

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

На этом всё, спасибо за внимание, надеюсь, что был хоть чем-то полезен и до скорых встреч на страницах блога для начинающих вебразработчиков и вебмастеров ZametkiNaPolyah.ru. Не забываем комментировать и делиться с друзьями;)

8 комментариев к записи Нормальные формы. Избыточность данных в базе данных. Транзитивная зависимость. Проектирование баз данных

Веб-Розраб!

Какой же ты лентяй!  А слабо не на чужих примерах объяснять? Не копируя примеры моего вебинар учителя?

КОПИПАСТЕРЫ! Создавайте свои примеры и не подавайте вид столь шарящих в этой области!

Кирилл

Какой вебинар, вы о чем?

Жека

Почему индекс и город в разных таблицах.

Кирилл

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

Ван Дер Саар

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

Виталий

Скажите а описанные здесь нормальные формы это все что есть или есть еще какие-то другие нормальные формы отношений таблиц в базе данных?

Кирилл

Здравствуйте, Виталий!

Нет, в данной статье описаны не все нормальные формы. Вообще, что такое нормальная форма? По сути, это математическое отношение, которое позволяет рационально хранить данные. Вообще, в природе насчитывается восемь нормальных форм, последняя из них — шестая нормальная форма. После третьей нормальной формы идет нормальная форма Бойса-Кодда или улучшенная третья нормальная форма, чаще всего, приводя отношения между таблицами к третьей нормальной форме вы получаете именно нормальную форму Бойса-Кодда. После пятой нормальной формы идет DCNF или так называемая доменно-ключевая нормальная форма. В теории отношения между таблицами можно свести до шестой нормальной формы, но это только в теории, на самом деле, как показывает практика, отношение между таблицами сводится только до BCNF (нормальная форма Бойса-Кодда). Более того: при проектирование баз данных бывают случаи, когда избыточность данных вносится намеренно, намеренно создается транзитивная зависимость между данными, все зависит от поставленных целей, иногда целостность данных удобнее поддерживать при помощи программного кода (не разрушая транзитивной зависимости), а объем настолько не велик, что на избыточность данных можно не смотреть. По этой теме есть масса литературы, стоит только поискать.

Татьяна

Спасибо большущее! Не описать, как Вы помогли — это наверно единственная статья с понятно написанным описанием первых трех нормальных форм. Теперь и с Бойсом-Коддом разберусь, а то уже думала, что я просто неспособна в силу умственных способностей понять, что к чему.

Текст комментария: