Знакомимся с нормализацией баз данных

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

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

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

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

Лекция № 10. Нормальные формы

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

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

Зависимость между атрибутами А и В можно схематически представить в виде диаграммы, показанной на рисунке 5.

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

Атрибут В функционально

зависит от атрибута А

Рисунок 5 — Диаграмма функциональной зависимости

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

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

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

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

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

Ссылки:

Введение
Первая нормальная форма
Вторая нормальная форма
Вики о нормализации
Mysql Workbench — программа для проектирования баз данных

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

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

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

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

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

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

Примеры нарушения

(Подчеркнутые атрибуты обозначают первичный ключ)

1ФН — первая нормальная форма :

любой атрибут содержит атомарное значение

ID КЛИЕНТА	…
Дюпон Париж
Дюран Марсель

Атрибут CLIENT_ID состоит из двух атомарных атрибутов.

ID КЛИЕНТА	ФАМИЛИЯ
1	Жерар Дюпон
2	Леон Дюран

Атрибут NOM состоит из двух атомарных атрибутов.

все атрибуты не повторяются

ИДАНТИФИКАЦИОННЫЙ НОМЕР ПРОДУКТА	ОПИСАНИЕ	ПОСТАВЩИКАМ
1	Телевизор	Sony, Sharp, LG

Атрибут SUPPLIERS — это список.

все атрибуты постоянны во времени.

ID КЛИЕНТА	ФАМИЛИЯ	ИМЯ	ВОЗРАСТ
1	Дюпон	Джерард	35 год

Атрибут AGE не является постоянным во времени.

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

все неключевые атрибуты функционально полностью зависят от всего первичного ключа.

НОМЕР ЗАКАЗА	ARTICLE_ID	ARTICLE_DESCRIPTION
1	15	Телевизор высокой четкости с усилителем Dolby 5.1

Атрибут DESCRIPTION_ARTICLE зависит только от части первичного ключа.

3FN — третья нормальная форма

любой атрибут, не принадлежащий ключу, не зависит от неключевого атрибута

НОМЕР ЗАКАЗА	ID КЛИЕНТА	ИМЯ ПОКУПАТЕЛЯ
1	1	Дюран

Атрибут NOM_CLIENT зависит от CLIENT_ID.

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

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

TEACHER_ID	MATERIAL_ID	ROOM_ID
ДУРАНД	ХИМИЯ	ХИМИЧЕСКАЯ ЛАБОРАТОРИЯ 3
ДЮПОН	АНГЛИЙСКИЙ	6А

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

FNDC — домен ключа нормальной формы

Отношение находится в FNDC тогда и только тогда, когда все ограничения являются логическим следствием ограничений домена и ключевых ограничений, которые применяются к отношению.

Либо отношение ТРАНСПОРТ со следующими атрибутами:

ПРОИЗВОДИТЕЛЬ	МОДЕЛЬ	ТИП	PTAC (кг)
Renault	Диспетчерская	VL	2500
Iveco	Евростар 440	PL	19000
Berliet	GDM 1934 г.	PL	15000
Фольксваген 2900	комби	VL	2 900

Обратите внимание, что тип VL (легковой автомобиль) или PL (тяжелый автомобиль) определяется значением PTAC. Таким образом, автомобиль массой более 3,5 тонн является PL

Ниже находится VL… Имеется избыточность данных типа, о которой можно судить по чтению значения PTAC. В случае изменения правил (для изменения может потребоваться стержень 3,5 тонны) необходимо обновить несколько кортежей.
— Чтобы устранить эту аномалию обновления, мы должны разделить отношение на два следующим образом:

1 ° АВТОМОБИЛЬ со следующими характеристиками:

ПРОИЗВОДИТЕЛЬ	МОДЕЛЬ	PTAC (кг)
Renault	Диспетчерская	2500
Iveco	Евростар 440	19000
Berliet	GDM 1934 г.	15000
Фольксваген 2900	комби	2 900

Типа транспортного средства больше нет в списке. Она будет вычтена из стоимости PTAC: более 3,5 тонн транспортное средство является грузовым автомобилем. Ниже это ВЛ.

2 ° ТИП АВТОМОБИЛЯ со следующими характеристиками:

ТИП	PTAC (кг)
VL
PL	3500

Неравномерное соединение будет необходимо для восстановления исходной связи.

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

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

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

Нормализация базы данных – это рекомендации по проектированию.

Преимущества нормализованной базы данных:

Возможность существенно упростить выборки. Получение данных из базы относительно простыми запросами.
Целостность данных. Избежание потерь или искажения информации в базе данных.
Отсутствие избыточности. Данные в таблице не дублируются, что существенно снижает её размер.
Благоприятные предпосылки к росту базы.

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

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

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

Существует 3 нормальные формы базы данных:

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

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

id	name	languages
1	Иван	Java, C++, PHP
2	Пётр	PHP, JavaScript
3	Михаил	C#, JavaScript

В примере №1. Представлена не удачная структура таблицы, где в поле languages указано перечисление.

id	name	languages1	languages2	languages3
1	Иван	Java	C++	PHP
2	Пётр	PHP	JavaScript	NULL
3	Михаил	C#	JavaScript	NULL

В примере №2 тоже не верная структура таблицы для поля languages.

Правильная структура таблиц для решения данной задачи:

id	name	languages
1	Иван	Java, C++, PHP
2	Пётр	PHP, JavaScript
3	Михаил	C#, JavaScript

Таблица языков программирования

id	language
1	Java
2	PHP
3	C#
4	JavaScript
5	Java

Таблица связей между пользователями и языками программирования

id	language_id	user_id
1	2	1
2	1	1
3	5	1
4	1	2

id	make	model
1	bmw	X5
2	bmw	X6
3	audi	A4
4	audi	Q5
5	toyota	corolla

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

Первая, вторая, третья нормальные формы. Нормальная форма Бойса-Кодда.

Графическое изображение функциональной зависимости реквизитов.

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

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

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

Пример.Отношение Студент = (Номер, Фамилия, Имя, Отчество, Дата, Группа) находится в первой и во второй нормальной форме одновременно, так как описательные реквизиты однозначно определены и функционально зависят от ключа Номер. Отношение Успеваемость = (Номер, Фамилия, Имя, Отчество, Дисциплина, оценка) находится в первой нормальной форме и имеет составной ключ Номер + Дисциплина. Это отношение не находится во второй нормальной форме, так как атрибуты Фамилия, Имя, Отчество не находятся в полной функциональной зависимости с составным ключом отношения.

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

Понятие третьей нормальной формы основывается на понятии нетранзитивной зависимости.

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

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

Пример.Если в состав описательных реквизитов информационного объекта Студент включить фамилию старосты

группы (Староста), которая определяется только номером группы, то одна и та же фамилия старосты будет многократно повторяться в разных экземплярах данного информационного объекта.

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

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

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

Рис. Пример «расщепления» структуры информационного объекта.

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

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

Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.

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

По поводу того, как должна быть спроектирована база нет 100% решения, потому что конкретный вариант может удовлетворять либо не удовлетворять различным бизнес-процессам и целям

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

Нормализация баз данных заключается в приведении структуры хранения данных к нормальным формам (NF). Всего таких форм существует 8, но часто достаточным является соблюдение первых трех. Рассмотрим их более подробно на примере учебной базы данных. Примеры будут строится по принципу «что было бы, если было иначе, чем сейчас».

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

Основным правилом первой формы является необходимость неделимости значения в каждом поле (столбце) строки – атомарность значений.

Рассмотрим таблицы сотрудников и телефонных линий.

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

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

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

Помимо атомарности к первой нормальной форме относятся следующие правила:

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

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

Условием этой формы является отсутствие зависимости неключевых полей от части составного ключа.

Так как составной ключ в учебной базе наблюдается только в таблице «Сотрудники_Линии», то рассмотрим пример на ней.

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

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

Если соблюдены правила первой нормальной формы, то создание таблицы «Линии» и перенос в нее зависимых столбцов удовлетворяет второй нормальной форме.

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

3NF схожа по логике с 2NF, но с некоторым отличием. Если 2 форма ликвидирует зависимости неключевых полей от части ключа, то третья нормальная форма исключает зависимость неключевых полей от других неключевых полей.

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

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

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

Теория нормальных форм не всегда применима на практике. Например, неатомарные значения не всегда являются «злом», а иногда наоборот. Связано это с необходимостью дополнительного объединения (следовательно, затрат производительности системы) при выполнении запросов, особенно когда производится обработка большого массива информации.

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

Пример

Рассмотрим следующий пример:

Доступность продуктов для коммивояжеров по брендам
Коммивояжер	Марка	Тип продукта
Джек Шнайдер	Acme	Пылесос
Джек Шнайдер	Acme	Хлебница
Мэри Джонс	Робусто	Секаторы
Мэри Джонс	Робусто	Пылесос
Мэри Джонс	Робусто	Хлебница
Мэри Джонс	Робусто	Стойка для зонтов
Луи Фергюсон	Робусто	Пылесос
Луи Фергюсон	Робусто	Телескоп
Луи Фергюсон	Acme	Пылесос
Луи Фергюсон	Acme	Лавовая лампа
Луи Фергюсон	Нимбус	Вешалка для галстуков

Предикат таблицы: Продукты типа, обозначенного Тип продукта, произведенный брендом, обозначенным Марка, можно получить у коммивояжера, назначенного Коммивояжер.

Первичный ключ — это совокупность всех трех столбцов

Также обратите внимание, что таблица находится в 4НФ, так как нет многозначные зависимости ( ) в таблице: ни один столбец (который сам по себе не является ключом-кандидатом или суперключом) не является определяющим для двух других столбцов

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

Однако предположим, что применяется следующее правило: В репертуаре коммивояжера есть определенные бренды и определенные типы продуктов. Если бренд B1 и бренд B2 входят в их репертуар, а тип продукта P находится в их репертуаре, то (при условии, что бренд B1 и бренд B2 оба составляют продукт типа P), коммивояжер должен предлагать продукты типа продукта P, произведенные брендом B1. и произведенные торговой маркой B2.

В этом случае можно разделить таблицу на три части:

Типы продуктов от коммивояжера
Коммивояжер	Тип продукта
Джек Шнайдер	Пылесос
Джек Шнайдер	Хлебница
Мэри Джонс	Секаторы
Мэри Джонс	Пылесос
Мэри Джонс	Хлебница
Мэри Джонс	Стойка для зонтов
Луи Фергюсон	Телескоп
Луи Фергюсон	Пылесос
Луи Фергюсон	Лавовая лампа
Луи Фергюсон	Вешалка для галстуков

Бренды от коммивояжера
Коммивояжер	Марка
Джек Шнайдер	Acme
Мэри Джонс	Робусто
Луи Фергюсон	Робусто
Луи Фергюсон	Acme
Луи Фергюсон	Нимбус

Типы продуктов по брендам
Марка	Тип продукта
Acme	Пылесос
Acme	Хлебница
Acme	Лавовая лампа
Робусто	Секаторы
Робусто	Пылесос
Робусто	Хлебница
Робусто	Стойка для зонтов
Робусто	Телескоп
Нимбус	Вешалка для галстуков

В этом случае Луи Фергюсон не может отказаться предлагать пылесосы, произведенные ACME (при условии, что ACME производит пылесосы), если он продает что-нибудь еще, произведенное Acme (лавовая лампа), а также он продает пылесосы, произведенные любым другим брендом (Robusto ).

Обратите внимание, как эта настройка помогает устранить избыточность. Предположим, Джек Шнайдер начинает продавать продукты Robusto — Хлебницы и пылесосы

В предыдущей настройке нам пришлось бы добавить две новые записи по одной для каждого типа продукта (, ). В новой настройке нам нужно добавить только одну запись () в «Бренды от коммивояжера».

использование

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

Таблица T находится в пятой нормальной форме (5NF) или в нормальной форме Project-Join (PJNF), если она не может иметь декомпозицию без потерь на любое количество меньших таблиц. Случай, когда все меньшие таблицы после декомпозиции имеют тот же ключ-кандидат, что и таблица T, исключается.

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

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

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

Ассоциативная таблица — таблица, имеющая ключевые связи с двумя и более таблицами

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

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

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

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