Как стать автором
Обновить

Вторая нормальная форма (в терминологии SQL)

Время на прочтение 4 мин
Количество просмотров 13K
Поскольку первый пост уже сорвал крышу нескольким хабражителям вообще и пошатнул карму мне в частности, решил написать перевод статьи в терминах языка 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
Ивановы Вася
Петровы Аня
Сидоровы Ваня
Сидоровы Саша


То есть первая НФ имеет дело, со структурой значений колонок.

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

  1. Главной целью приведения ко второй нормальной форме есть желание избавиться от избыточности хранения данных и как следствие избежать аномалий модификации этих данных (аномалий изменения, вставки и удаления)
  2. Второй по порядку, но не по значению, целью нормализации в 2NF есть максимально разбить модель данных на отдельные таблицы, чтобы их можно было комбинировать и использовать в запросах новыми, не предусмотренными изначально способами.
  3. Минимизировать усилия по изменению таблиц в случае необходимости. Чем меньше зависимостей между колонками таблицы, тем меньше изменений в ней потребуется при изменении модели данных.
  4. Понятность таблиц для пользователя. Чем держать все данные в одной большой таблице, проще представить данные как несколько связанных и логически разделенных табличек. Это проще читать, воспринимать, проектировать и поддерживать. В конце концов, любая модель данных начинается на доске или бумаге в виде кружочков, блоков и линий, которые так любят рисовать дети и программисты.


Например, у нас есть таблица

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


Одна и та же песня может входить в несколько дисков, также теоретически возможны одноименные альбомы с одноименными песнями у разных групп, например трибьюты. Поэтому ключом будет { Artist, CD_name, Track }. При этом значение колонки Lyrics, обозначающий автора слов, однозначно определяется из колонок { Artist, Track }, которые есть частью ключа. Это и есть нарушение 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 } и не определяется однозначно по любой из колонок Artist или Track.

Про склад наверное не буду, устал таблички набирать в хтмл :)

Вот собственно и все.
Надеюсь вот щас было понятно, я же пошел разбираться с 3NF!
Теги:
Хабы:
+24
Комментарии 74
Комментарии Комментарии 74

Публикации

Истории

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн