Поскольку первый пост уже сорвал крышу нескольким хабражителям вообще и пошатнул карму мне в частности, решил написать перевод статьи в терминах языка SQL. Будет полезно мне и, возможно, не только мне. Вообще с детских лет я стремлюсь приземлять теорию к практике с помощью различных средств, среди которых был и алкоголь, и, мне кажется бесполезно тратить время на изучение чегото, к чему нельзя придумать пример из реальной жизни.
Забавно лишь, что вся эта белиберда под катом родилась в уме Кодда еще до возникновения SQL как языка, а теперь вот в терминах SQL все подавай…
Что же такое вторая нормальная форма или 2NF? Так чтоб трехлетний ребенок действительно понял…
Для начала разберемся в целях, которые преследует нормализация. Под катом нету терминов дискретки…
Цель приведения к первой нормальной форме (1NF) — дать возможность использовать условия WHERE при выборке данных запросом SELECT. Поскольку все значения колонки имеют одинаковый и определенный заранее тип, их можно сравнивать между собой и с константами.
Например, если в таблице ’Family’ есть колонка ’Kids’ типа VARCHAR, мы можем легко сравнить две строки ’Вася’ и ’Аня’ и определить их лексикографический порядок, например оператором >
Если в какой то строчке в поле ’Дети’ указано ’Ваня, Саша’, мы уже не можем однозначно определить порядок деток. Сравнивать строки ’Вася’ и ’Ваня, Саша’ бессмысленно в данной ситуации. поскольку первое — это строка, а второе — уже список. Допустим мы хотим найти всех детей на букву 'C'.
Запрос вида
не отработает в данной ситуации как нужно и не найдет Сашу, поскольку LIKE не умеет парсить списки, извлекать значения и трактовать их как аргументы для сравнения с шаблоном. ’Ваня, Саша’ в данном случае неатомарное значение типа список строк. Чтобы научить SQL работать с такими данными, нужно либо расширить язык, либо упростить модель до 1NF. Декомпозиция до 1NF достигается разбиением составного значения на атомарные:
То есть первая НФ имеет дело, со структурой значений колонок.
Вторая (и третья, но не о ней сегодня) НФ имеет дело уже с ключами и зависимостями между колонками таблицы. Перечислим ее цели с пояснениями.
Например, у нас есть таблица
, где первичным ключом является ID. Эта схема находится во 2NF, поскольку колонка Artist, которая не входит в ключ определяется только ключом целиком.
Таблица находится во 2NF если любая неключевая колонка определяется только целым ключом и не может быть определена его частью
Вообще ставить вопрос о несоответствии 2NF можно только в случае если в таблице есть составные ключи. Таблицы с простыми ключами, как в примере всегда имеют 2NF. Указанная таблица есть как раз пример такого случая, так как в ней оба ключа (а это ID и естественный ключ CD_name) простые, и частей у них нет.
Несоответствие 2NF рассмотрим на таблице
Одна и та же песня может входить в несколько дисков, также теоретически возможны одноименные альбомы с одноименными песнями у разных групп, например трибьюты. Поэтому ключом будет { Artist, CD_name, Track }. При этом значение колонки Lyrics, обозначающий автора слов, однозначно определяется из колонок { Artist, Track }, которые есть частью ключа. Это и есть нарушение 2NF.
Следствием этого есть избыточность значений в колонке Lyrics для каждого диска в который входит песня. В сфере музыки эти значения не меняются, но в других доменных областях неосторожное изменение таких избыточных данных может привести к противоречивому состоянию БД, когда обновлены будут не все значения. Это пример аномалии модификации.
Другим следствием есть то, что песни, которые еще не выпущены на СД-дисках, а просто транслированы по радио или выпущены на других носителях, не подходят под указанную схему данных. Соответственно мы не сможем добавить новую песню в базу данных пока она не будет выпущена на СД. Это пример аномалии вставки.
Аналогично если мы захотим удалить какой-либо диск из базы данных, мы будем вынуждены потерять информацию об авторах всех песен, которые входят только в этот диск, поскольку в данной модели нет возможности представить информацию об авторе, если песня не входит в какой-либо СД. Например желание удалить диск Six Degrees Of Inner Turbulence приведет к тому, что автор песни Misunderstood будет утерян, что непростительно. Это пример аномалии удаления.
Чтобы избежать подобных аномалий и убрать избыточность, нам нужно разделить таблицу, то есть провести ее декомпозицию на две:
В реальной базе для построения запросов нужно еще ввести смысловые связи между таблицами, например, свзать их с помощью foreign key, но для нашего примера достаточно понимать что эти таблицы связаны по смыслу.
Обе таблицы имеют 2NF, первая — поскольку у нее все колонки входят в ключ, а вторая — поскольку Lyrics определяется по ключу { Artist, Track } и не определяется однозначно по любой из колонок Artist или Track.
Про склад наверное не буду, устал таблички набирать в хтмл :)
Вот собственно и все.
Надеюсь вот щас было понятно, я же пошел разбираться с 3NF!
Забавно лишь, что вся эта белиберда под катом родилась в уме Кодда еще до возникновения 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 |
Одна и та же песня может входить в несколько дисков, также теоретически возможны одноименные альбомы с одноименными песнями у разных групп, например трибьюты. Поэтому ключом будет { 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!