Вторая нормальная форма — Основы реляционных баз данных
В этом уроке мы разберем вторую нормальную форму реляционной модели, а также два ее требования.
Будем работать с таблицей, которая уже соответствует первой нормальной форме:
order_items
| id | first_name | last_name | address | item | price |
|---|---|---|---|---|---|
| 8 | Сергей | Иванов | Москва, ул. Промышленная | утюг | 1000.00 |
| 2 | Иван | Петров | Самара, ул. Энгельса | кофеварка | 5000.00 |
| 7 | Виктор | Сидоров | Омск, ул. Дворцовая | утюг | 1000.00 |
| 4 | Виктор | Сидоров | Омск, ул. Дворцовая | телевизор | 6500.00 |
| 9 | Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
| 6 | Сергей | Иванов | Москва, ул. Матросова | ноутбук | 20000.00 |
Вторая нормальная форма
Вторая нормальная форма включает в себя два требования:
- Таблица должна быть в первой нормальной форме
- Все неключевые атрибуты таблицы должны зависеть от первичного ключа
Первое требование уже выполнено, так как в таблице:
- Каждая ячейка хранит только одно значение
- Все данные в одной колонке одного типа
- Каждая запись отличается от других записей
Поэтому разберем подробнее второе требование.
Зависимость от первичного ключа
Зависимость атрибута от первичного ключа — это ситуация, при которой ключ имеет значение, зависимое от конкретного контекста. Предположим, что в таблице, Сергей — это всегда один и тот же человек, который делает заказ на разные адреса. В таком случае видно, что адрес привязан к конкретному заказу. Это и есть зависимость от первичного ключа. А вот имя пользователя и его фамилия с заказом никак не связано. Оно имеет отношение к самому пользователю.
Согласно второй форме, атрибуты first_name и last_name необходимо вынести в свою таблицу, которая будет отвечать за пользователей:
users
| id | first_name | last_name |
|---|---|---|
| 2 | Сергей | Иванов |
| 3 | Иван | Петров |
| 5 | Виктор | Сидоров |
В этой таблице всего три записи, потому что у нас три уникальных пользователя. Каждому из этих пользователей присваивается первичный ключ.
Теперь нужно связать таблицу order_items с таблицей users. Делается это через указание первичных ключей в зависимых таблицах:
order_items
| id | user_id | address | item | price |
|---|---|---|---|---|
| 8 | 2 | Москва, ул. Промышленная | утюг | 1000.00 |
| 2 | 3 | Самара, ул. Энгельса | кофеварка | 5000.00 |
| 7 | 5 | Омск, ул. Дворцовая | утюг | 1000.00 |
| 4 | 5 | Омск, ул. Дворцовая | телевизор | 6500.00 |
| 9 | 2 | Москва, ул. Матросова | ноутбук | 20000.00 |
| 6 | 2 | Москва, ул. Матросова | ноутбук | 20000.00 |
Мы удалили first_name, last_name и добавили user_id. В этом поле хранятся идентификаторы пользователей, а само поле называется внешним или вторичным ключом.
Такую же операцию нужно произвести и с товаром. Вынесем item в свою таблицу:
goods
| id | name |
|---|---|
| 50 | утюг |
| 30 | кофеварка |
| 20 | телевизор |
| 33 | ноутбук |
Теперь свяжем эти данные с таблицей order_items:
order_items
| id | user_id | address | good_id | price |
|---|---|---|---|---|
| 8 | 2 | Москва, ул. Промышленная | 50 | 1000.00 |
| 2 | 3 | Самара, ул. Энгельса | 30 | 5000.00 |
| 7 | 5 | Омск, ул. Дворцовая | 50 | 1000.00 |
| 4 | 5 | Омск, ул. Дворцовая | 20 | 6500.00 |
| 9 | 2 | Москва, ул. Матросова | 33 | 20000.00 |
| 6 | 2 | Москва, ул. Матросова | 33 | 20000.00 |
Другой пример внешнего ключа в таблице покупателя и города можно схематично показать так:
Внешний ключ — это не ссылка. Таблицы существуют сами по себе, и во внешнем ключе указывается конкретное значение, которое должно совпадать с первичным ключом другой таблицы.
Так выглядит синтаксис определения вторичного ключа:
-- Внешних ключей может быть любое количество: сколько ссылок — столько и ключей CREATE TABLE orders ( id bigint PRIMARY KEY, -- Тип внешнего ключа должен быть такой же, -- как у первичного в той таблице, куда ссылается внешний user_id bigint REFERENCES users (id), -- остальные поля ); Благодаря вторичному ключу поддерживаются гарантии корректности данных. Например, невозможно удалить запись из основной таблицы, если на нее есть ссылки из внешних ключей в другой таблице. Так не получится случайно завести базу в неконсистентное состояние — когда данные ссылаются на несуществующие данные.
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
Как осуществляется приведение ко второй нормальной форме
Warning: mysqli_connect(): (HY000/1040): Too many connections in /home/c/co37471/eljob/public_html/core/main/Msql.php on line 49
Warning: mysqli_connect(): (HY000/1040): Too many connections in /home/c/co37471/eljob/public_html/core/main/Msql.php on line 53
Warning: mysqli_connect(): (HY000/1040): Too many connections in /home/c/co37471/eljob/public_html/core/main/Msql.php on line 53
Warning: mysqli_connect(): (HY000/1040): Too many connections in /home/c/co37471/eljob/public_html/core/main/Msql.php on line 53
Some mysqli shit happened: 1040 Too many connections
Вторая нормальная форма (2NF) базы данных
Всем привет! Сегодня мы с Вами подробно рассмотрим вторую нормальную форму (2NF) базы данных, в частности Вы узнаете, какие требования предъявляются к таблицам, чтобы база данных находилась во второй нормальной форме, и для наглядности мы как всегда рассмотрим несколько примеров.
Перед тем как переходить к процессу приведения таблиц базы данных до второй нормальной формы, необходимо чтобы эти таблицы уже находились в первой нормальной форме, подробно процесс приведения таблиц базы данных до первой нормальной формы, а также все требования, предъявляемые к первой нормальной форме, мы рассматривали в предыдущей статье – первая нормальная форма (1NF).
После того как таблицы базы данных находятся в первой нормальной форме, мы можем начинать приводить базу данных ко второй нормальной форме и рассматривать соответствующие требования.
Требования второй нормальной формы (2NF)
Чтобы база данных находилась во второй нормальной форме (2NF), необходимо чтобы ее таблицы удовлетворяли следующим требованиям:
- Таблица должна находиться в первой нормальной форме
- Таблица должна иметь ключ
- Все неключевые столбцы таблицы должны зависеть от полного ключа (в случае если он составной)
Ключ – это столбец или набор столбцов, по которым гарантировано можно отличить строки друг от друга, т.е. ключ идентифицирует каждую строку таблицы. По ключу мы можем обратиться к конкретной строке данных в таблице.
Если ключ составной, т.е. состоит из нескольких столбцов, то все остальные неключевые столбцы должны зависеть от всего ключа, т.е. от всех столбцов в этом ключе. Если какой-то атрибут (столбец) зависит только от одного столбца в ключе, значит, база данных не находится во второй нормальной форме.
Иными словами, в таблице не должно быть данных, которые можно получить, зная только половину ключа, т.е. только один столбец из составного ключа.
Главное правило второй нормальной формы (2NF) звучит следующим образом
Таблица должна иметь правильный ключ, по которому можно идентифицировать каждую строку.
Пример приведения таблицы ко второй нормальной форме
Представим, что нам нужно хранить список сотрудников организации, и для этого мы создали следующую таблицу.
Таблица сотрудников в первой нормальной форме.
| ФИО | Должность | Подразделение | Описание подразделения |
| Иванов И.И. | Программист | Отдел разработки | Разработка и сопровождение приложений и сайтов |
| Сергеев С.С. | Бухгалтер | Бухгалтерия | Ведение бухгалтерского и налогового учета финансово-хозяйственной деятельности |
| John Smith | Продавец | Отдел реализации | Организация сбыта продукции |
Мы видим, что она удовлетворяет условиям первой нормальной формы, т.е. в ней нет дублирующих строк и все значения атомарны.
Теперь мы можем начать процесс нормализации этой таблицы до второй нормальной формы.
Что для этого нам нужно сделать? Нам нужно внедрить первичный ключ.
Поработав немного с предметной областью, мы выясняем, что в этой организации каждому сотруднику присваивается уникальный табельный номер, который никогда не будет изменен.
Поэтому очевидно, что для таблицы, которая будет хранить список сотрудников, первичным ключом может выступать табельный номер, зная который мы можем четко идентифицировать каждого сотрудника, т.е. каждую строку нашей таблицы. Если бы такого табельного номера у нас не было или в рамках организации он мог повторяться (например, сотрудник уволился, и спустя время его номер присвоили новому сотруднику), то для первичного ключа мы могли бы создать искусственный ключ с целочисленным типом данных, который автоматически увеличивался бы в случае добавления новых записей в таблицу. Тем самым мы бы точно также четко идентифицировали каждую строку в таблице.
Таким образом, чтобы привести эту таблицу ко второй нормальной форме, мы должны добавить в нее еще один атрибут, т.е. столбец с табельным номером.
Таблица сотрудников во второй нормальной форме с простым первичным ключом.
| Табельный номер | ФИО | Должность | Подразделение | Описание подразделения |
| 1 | Иванов И.И. | Программист | Отдел разработки | Разработка и сопровождение приложений и сайтов |
| 2 | Сергеев С.С. | Бухгалтер | Бухгалтерия | Ведение бухгалтерского и налогового учета финансово-хозяйственной деятельности |
| 3 | John Smith | Продавец | Отдел реализации | Организация сбыта продукции |
В результате, так как наш первичный ключ является простым, а не составным, наша таблица автоматически переходит во вторую нормальную форму.
Иными словами, если первичный ключ простой (не составной, т.е. состоящий из одного столбца), второе требование, которое предъявляется к таблицам для перехода во вторую нормальную форму, выполнять не требуется, так как оно относится только к таблицам, у которых первичный ключ составной.
Пример приведения таблицы ко второй нормальной форме (первичный ключ составной)
А теперь давайте рассмотрим другую ситуацию, в которой первичный ключ у нас будет составным.
Представим, что наша организация выполняет несколько проектов, в которых может быть задействовано несколько участников, и нам необходимо хранить информацию об этих проектах. В частности мы хотим знать, кто участвует в каждом из проектов, продолжительность этого проекта, ну и возможно какие-то другие сведения. При этом мы понимаем, что отдельно взятый сотрудник может участвовать в нескольких проектах.
Для хранения таких данных мы создали следующую таблицу.
Таблица проектов организации в первой нормальной форме.
| Название проекта | Участник | Должность | Срок проекта (мес.) |
| Внедрение приложения | Иванов И.И. | Программист | 8 |
| Внедрение приложения | Сергеев С.С. | Бухгалтер | 8 |
| Внедрение приложения | John Smith | Менеджер | 8 |
| Открытие нового магазина | Сергеев С.С. | Бухгалтер | 12 |
| Открытие нового магазина | John Smith | Менеджер | 12 |
Как видим, она в первой нормальной форме, значит, мы можем пытаться приводить ее ко второй нормальной форме.
Как Вы помните, чтобы привести таблицу ко второй нормальной форме, необходимо определить для нее первичный ключ.
Посмотрев на эту таблицу, мы понимаем, что четко идентифицировать каждую строку мы можем только с помощью комбинации столбцов, например, «Название проекта» + «Участник», иными словами, зная «Название проекта» и «Участника», мы можем четко определить конкретную запись в таблице, т.е. каждое сочетание значений этих столбцов является уникальным.
Таким образом, мы определили первичный ключ и он у нас составной, т.е. состоящий их двух столбцов.
Таблица проектов организации. Внедрен составной первичный ключ.
| Название проекта | Участник | Должность | Срок проекта (мес.) |
| Внедрение приложения | Иванов И.И. | Программист | 8 |
| Внедрение приложения | Сергеев С.С. | Бухгалтер | 8 |
| Внедрение приложения | John Smith | Менеджер | 8 |
| Открытие нового магазина | Сергеев С.С. | Бухгалтер | 12 |
| Открытие нового магазина | John Smith | Менеджер | 12 |
Так как первичный ключ составной, нам необходимо проверить еще и второе требование, которое гласит, что «Все неключевые столбцы таблицы должны зависеть от полного ключа».
Другими словами, остальные столбцы, которые не входят в первичный ключ, должны зависеть от всего первичного ключа, т.е. от всех столбцов, а не от какого-то одного.
Чтобы это проверить, мы можем задать себе несколько вопросов.
Можем ли мы определить «Должность», зная только название проекта? Нет. Для этого нам необходимо знать и участника. Значит, пока все хорошо, по этой части ключа мы не можем четко определить значение неключевого столбца. Идем дальше и проверяем другую часть ключа.
Можем ли мы определить «Должность» зная только участника? Да, можем. Значит наш первичный ключ плохой, и требование второй нормальной формы не выполняется.
Что делать в этом случае?
В этом случае мы будем выполнять действие, которое выполняется, наверное, в 99% случаев на протяжении всего процесса нормализации базы данных – это декомпозиция.
Декомпозиция – это процесс разбиения одного отношения (таблицы) на несколько.
Чтобы декомпозировать нашу таблицу и привести базу данных к нормализованной форме, мы должны создать следующие таблицы.
| Идентификатор проекта | Название проекта | Срок проекта (мес.) |
| 1 | Внедрение приложения | 8 |
| 2 | Открытие нового магазина | 12 |
| Идентификатор участника | Участник | Должность |
| 1 | Иванов И.И. | Программист |
| 2 | Сергеев С.С. | Бухгалтер |
| 3 | John Smith | Менеджер |
Связь проектов и участников этих проектов.
| Идентификатор проекта | Идентификатор участника |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
Заметка! Если Вас интересует язык SQL, то рекомендую почитать книгу «SQL код» – это самоучитель по языку SQL для начинающих программистов. В ней очень подробно рассмотрены основные конструкции языка.
Мы создали 3 таблицы:
- Проекты, в нее мы добавили искусственный первичный ключ
- Участники, в нее мы также добавили искусственный первичный ключ
- Связь между проектами и участниками, она нужна для реализации связи «Многие ко многим», так как между этими таблицами связь именно такая
После того как мы привели таблицы базы данных ко второй нормальной форме, мы можем переходить к приведению таблиц до третьей нормальной формы (3NF). Описание, требования и пример приведения таблиц до третьей нормальной формы мы рассмотрим в следующем материале.
На сегодня это все, надеюсь, материал был Вам полезен, пока!
Вторая нормальная форма (в терминологии SQL)
Поскольку первый пост уже сорвал крышу нескольким хабражителям вообще и пошатнул карму мне в частности, решил написать перевод статьи в терминах языка SQL. Будет полезно мне и, возможно, не только мне. Вообще с детских лет я стремлюсь приземлять теорию к практике с помощью различных средств, среди которых был и алкоголь, и, мне кажется бесполезно тратить время на изучение чегото, к чему нельзя придумать пример из реальной жизни.
Забавно лишь, что вся эта белиберда под катом родилась в уме Кодда еще до возникновения SQL как языка, а теперь вот в терминах SQL все подавай…
Что же такое вторая нормальная форма или 2NF? Так чтоб трехлетний ребенок действительно понял…
Для начала разберемся в целях, которые преследует нормализация. Под катом нету терминов дискретки…
Цель приведения к первой нормальной форме (1NF) — дать возможность использовать условия WHERE при выборке данных запросом SELECT. Поскольку все значения колонки имеют одинаковый и определенный заранее тип, их можно сравнивать между собой и с константами.
Например, если в таблице ’Family’ есть колонка ’Kids’ типа VARCHAR, мы можем легко сравнить две строки ’Вася’ и ’Аня’ и определить их лексикографический порядок, например оператором >
| Family | Kids |
|---|---|
| Ивановы | Вася |
| Петровы | Аня |
Если в какой то строчке в поле ’Дети’ указано ’Ваня, Саша’, мы уже не можем однозначно определить порядок деток. Сравнивать строки ’Вася’ и ’Ваня, Саша’ бессмысленно в данной ситуации. поскольку первое — это строка, а второе — уже список. Допустим мы хотим найти всех детей на букву ‘C’.
| Family | Kids |
|---|---|
| Ивановы | Вася |
| Петровы | Аня |
| Сидоровы | Ваня, Саша |
SELECT Kids FROM Family WHERE kids LIKE 'С%' не отработает в данной ситуации как нужно и не найдет Сашу, поскольку LIKE не умеет парсить списки, извлекать значения и трактовать их как аргументы для сравнения с шаблоном. ’Ваня, Саша’ в данном случае неатомарное значение типа список строк. Чтобы научить SQL работать с такими данными, нужно либо расширить язык, либо упростить модель до 1NF. Декомпозиция до 1NF достигается разбиением составного значения на атомарные:
| Family | Kids |
|---|---|
| Ивановы | Вася |
| Петровы | Аня |
| Сидоровы | Ваня |
| Сидоровы | Саша |
То есть первая НФ имеет дело, со структурой значений колонок.
Вторая (и третья, но не о ней сегодня) НФ имеет дело уже с ключами и зависимостями между колонками таблицы. Перечислим ее цели с пояснениями.
- Главной целью приведения ко второй нормальной форме есть желание избавиться от избыточности хранения данных и как следствие избежать аномалий модификации этих данных (аномалий изменения, вставки и удаления)
- Второй по порядку, но не по значению, целью нормализации в 2NF есть максимально разбить модель данных на отдельные таблицы, чтобы их можно было комбинировать и использовать в запросах новыми, не предусмотренными изначально способами.
- Минимизировать усилия по изменению таблиц в случае необходимости. Чем меньше зависимостей между колонками таблицы, тем меньше изменений в ней потребуется при изменении модели данных.
- Понятность таблиц для пользователя. Чем держать все данные в одной большой таблице, проще представить данные как несколько связанных и логически разделенных табличек. Это проще читать, воспринимать, проектировать и поддерживать. В конце концов, любая модель данных начинается на доске или бумаге в виде кружочков, блоков и линий, которые так любят рисовать дети и программисты.
Например, у нас есть таблица
| ID | CD_name | Artist |
|---|---|---|
| 10 | Six Degrees Of Inner Turbulence | Dream Theater |
| 20 | Metropolis, pt. 2: Scenes From A Memory | Dream Theater |
| 30 | Master of Puppets | Dream Theater |
, где первичным ключом является ID. Эта схема находится во 2NF, поскольку колонка Artist, которая не входит в ключ определяется только ключом целиком.
Таблица находится во 2NF если любая неключевая колонка определяется только целым ключом и не может быть определена его частью
Вообще ставить вопрос о несоответствии 2NF можно только в случае если в таблице есть составные ключи. Таблицы с простыми ключами, как в примере всегда имеют 2NF. Указанная таблица есть как раз пример такого случая, так как в ней оба ключа (а это ID и естественный ключ CD_name) простые, и частей у них нет.
Несоответствие 2NF рассмотрим на таблице
| Artist | CD_name | Track | Lyrics |
|---|---|---|---|
| Dream Theater | Six Degrees Of Inner Turbulence | Misunderstood | Petrucci |
| Dream Theater | Metropolis, pt. 2: Scenes From A Memory | Overture 1928 | (instrumental) |
| Dream Theater | Master of Puppets | Battery | Неtfield |
| Metallica | Master of Puppets | Battery | Неtfield |
| Ensiferum | Tale of Revenge | Battery | Неtfield |
Одна и та же песня может входить в несколько дисков, также теоретически возможны одноименные альбомы с одноименными песнями у разных групп, например трибьюты. Поэтому ключом будет . При этом значение колонки Lyrics, обозначающий автора слов, однозначно определяется из колонок , которые есть частью ключа. Это и есть нарушение 2NF.
Следствием этого есть избыточность значений в колонке Lyrics для каждого диска в который входит песня. В сфере музыки эти значения не меняются, но в других доменных областях неосторожное изменение таких избыточных данных может привести к противоречивому состоянию БД, когда обновлены будут не все значения. Это пример аномалии модификации.
Другим следствием есть то, что песни, которые еще не выпущены на СД-дисках, а просто транслированы по радио или выпущены на других носителях, не подходят под указанную схему данных. Соответственно мы не сможем добавить новую песню в базу данных пока она не будет выпущена на СД. Это пример аномалии вставки.
Аналогично если мы захотим удалить какой-либо диск из базы данных, мы будем вынуждены потерять информацию об авторах всех песен, которые входят только в этот диск, поскольку в данной модели нет возможности представить информацию об авторе, если песня не входит в какой-либо СД. Например желание удалить диск Six Degrees Of Inner Turbulence приведет к тому, что автор песни Misunderstood будет утерян, что непростительно. Это пример аномалии удаления.
Чтобы избежать подобных аномалий и убрать избыточность, нам нужно разделить таблицу, то есть провести ее декомпозицию на две:
| Artist | CD_name | Track |
|---|---|---|
| Dream Theater | Six Degrees Of Inner Turbulence | Misunderstood |
| Dream Theater | Metropolis, pt. 2: Scenes From A Memory | Overture 1928 |
| Dream Theater | Master of Puppets | Battery |
| Metallica | Master of Puppets | Battery |
| Ensiferum | Tale of Revenge | Battery |
| Artist | Track | Lyrics |
|---|---|---|
| Dream Theater | Misunderstood | Petrucci |
| Dream Theater | Overture 1928 | (instrumental) |
| Metallica | Battery | Неtfield |
В реальной базе для построения запросов нужно еще ввести смысловые связи между таблицами, например, свзать их с помощью foreign key, но для нашего примера достаточно понимать что эти таблицы связаны по смыслу.
Обе таблицы имеют 2NF, первая — поскольку у нее все колонки входят в ключ, а вторая — поскольку Lyrics определяется по ключу и не определяется однозначно по любой из колонок Artist или Track.
Про склад наверное не буду, устал таблички набирать в хтмл 🙂
Вот собственно и все.
Надеюсь вот щас было понятно, я же пошел разбираться с 3NF!