Главная » SQL, Базы данных » ПЕРВАЯ, ВТОРАЯ И ТРЕТЬЯ НОРМАЛЬНЫЕ ФОРМЫ

0

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

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

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

Итак, ниже приведено предварительное определение ЗНФ.

■     Третья нормальная форма {очень неформальное определение). Переменная отноше ния находится в ЗНФ тогда и только тогда, когда ее неключевые атрибуты (если они вообще существуют) являются одновременно:

а)  взаимно независимыми; и

б)  неприводимо зависим ыми от первичного ключа.

Ниже приведены (неформальные) определения понятий неключевые атрибуты и

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

■     Неключевой атрибут — это атрибут, который не входит в состав первичного ключа рассматриваемой переменной отношения.

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

Например, согласно приведенному выше определению, переменная  отношения Р (переменная отношения деталей) находится в ЗНФ, а именно, атрибуты PNAME (название детали), COLOR (цвет), WEIGHT (вес) и CITY (город) являются независимыми друг от друга (скажем, цвет деталей можно менять, не изменяя их веса и т.д.) и неприводимо зависимыми по отношению к первичному ключу {Р#} (номер детали).

Такое неформальное определение ЗНФ можно представить еще более неформально,

следующим образом.

■    Третья нормальная форма {еще более неформальное определение). Переменная отношения находится в ЗНФ тогда и только тогда, когда каждый кортеж состоит из значения первичного ключа, идентифицирующего некоторую сущность, и множества взаимно независимых атрибутов, в количестве от нуля или больше, некоторым образом описывающих эту сущность.

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

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

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

Фактически в этом определении всего лишь утверждается, что все переменные отношения всегда находятся в 1НФ, что, несомненно, верно. Однако переменная отношения, которая находится только в 1НФ (т.е. не находится ни во второй, ни в третьей нормальной форме), обладает структурой, не совсем приемлемой по некоторым причинам. Для иллюстрации этого факта допустим, что информация о поставщиках и поставках содержится не в двух переменных отношения S и SP, а в одной, имеющей следующую структуру.

FIRST { S#, STATUS, CITY, P#, QTY } PRIMARY KEY { S#, P# }

Это — расширенная версия переменной отношения SCP, приведенной выше, в разделе 12.1. Ее атрибуты имеют тот же смысл, что и раньше, за исключением следующего дополнительного ограничения, специально введенного в этом примере.

CITY  → STATUS

(Иными словами, атрибут STATUS функционально зависим от атрибута  CITY, И смысл этого ограничения состоит в том, что статус поставщика определяется его местонахождением, например, все поставщики из Лондона  должны иметь статус 20.) Кроме того, для упрощения атрибут SNAME снова игнорируется. Первичным ключом переменной отношения FIRST является комбинация атрибутов {S#, Р#}, а диаграмма ее функциональных зависимостей будет иметь вид, представленный на рис. 12.5.

Рис. 12.5. Функциональные зависимости в переменной отношения FIRST

Обратите  внимание,  что  эта  диаграмма  функциональных  зависимостей  с  виду "сложнее" аналогичной диаграммы для переменной отношения в ЗНФ. Как было сказано в предыдущем разделе, на диаграмме ФЗ для переменной отношения в ЗНФ стрелки всегда выходят только из потенциальных ключей, тогда как на диаграмме ФЗ для переменной отношения, которая не находится в ЗНФ (например, на диаграмме ФЗ для переменной отношения FIRST), есть стрелки, начинающиеся с потенциальных ключей, и дополнительные стрелки, которые и  являются источником затруднений. Фактически в переменной отношения FIRST нарушаются оба условия, указанные в приведенном выше определении ЗНФ: не все неключевые атрибуты взаимно независимы, поскольку атрибут STATUS зависит от атрибута CITY (одна дополнительная стрелка), и не все неключевые атрибуты  неприводимо зависимы от первичного ключа, поскольку атрибуты STATUS И CITY, каждый в отдельности, зависимы от атрибута s# (еще две дополнительные стрелки).

Для иллюстрации некоторых трудностей, связанных с устранением этих  дополнительных стрелок, на рис. 12.6 приведен пример данных в переменной отношения FIRST. Это тот же набор значений, который обычно используется нами в примерах, но значение 30 статуса поставщика с номером S3 заменено значением 10 в соответствии с новым ограничением, согласно которому значение атрибута  CITY определяет значение атрибута STATUS. Возникшая в результате  избыточность данных вполне очевидна, поскольку в каждом кортеже для поставщика с номером S1 атрибут CITY имеет значение London и, кроме того, в каждом кортеже со значением London в атрибуте CITY указано значение 20 для атрибута STATUS.

Избыточность в переменной отношения FIRST приводит к разным аномалиям обновления, получившим такое название по историческим причинам. Под этим подразумеваются определенные трудности, появляющиеся при выполнении  операций обновления INSERT, DELETE и UPDATE. Для начала рассмотрим избыточность данных "поставщикгород", соответствующих функциональной  зависимости S# → CITY. Ниже описаны проблемы, которые возникнут при выполнении каждой из указанных операций обновления.

■  Операция INSERT. Нельзя поместить в переменную отношения FIRST информацию о том, что некоторый поставщик находится в определенном городе, до тех пор пока этот поставщик не выполнит поставку хотя бы одной детали. Действительно, в таблице на рис. 12.6 нет сведений о поставщике из Афин с номером S5, поскольку до тех пор, пока этот поставщик не начнет поставку какой-либо детали, для него невозможно будет сформировать значение первичного ключа. (Как и в разделе 10.4 главы 10, в настоящей главе принято достаточно обоснованное предположение, что атрибуты первичного ключа не могут иметь значений, применяемых по умолчанию.)

Рис. 12.6. Пример данных в переменной отношения FIRST

■  Операция DELETE. Если из переменной отношения FIRST удалить кортеж, который является единственным для некоторого поставщика, будет удалена не только информация о поставке поставщиком некоторой детали, но также информация о том, что этот поставщик находится в определенном городе. Например, если из переменной отношения FIRST удалить кортеж со значением S3 в атрибуте s# и

значением Р2 в атрибуте Р#, будет утрачена информация о том, что поставщик с номером S3 находится в Париже. (По сути, проблемы  удаления  и вставки представляют собой две стороны одной медали.)

Примечание. В действительности проблема заключается в том, что в переменной отношения FIRST содержится слишком много информации,  собранной в одном месте, потому и теряется слишком много информации при удалении некоторых кортежей. Точнее говоря, переменная отношения FIRST  одновременно содержит информацию и о поставках, и о поставщиках. В результате удаление информации о поставке вызывает также удаление информации о поставщике. Для решения этой проблемы  необходимо разделить информацию на несколько частей, т.е. собрать сведения о поставках в одной переменной отношения, а о поставщиках — в другой (именно это и будет сделано чуть ниже). Следовательно, процедуре нормализации можно дать еще одно неформальное определение: охарактеризовать ее как процеДУРУ разбиения логически несвязанной информации на отдельные переменные отношения.

■  Операция UPDATE. В общем, название города для каждого поставщика повторяется в переменной отношения FIRST несколько раз, и эта избыточность приводит к возникновению проблем при обновлении.  Например, если поставщик с номером S1 переместится из Лондона в Амстердам, то необходимо будет отыскать в переменной отношения FIRST все кортежи, в которых значения S1 и  London связаны между собой (для внесения соответствующих изменений), иначе база данных окажется в противоречивом состоянии (в одних кортежах в качестве города, в котором находится поставщик с номером S1, будет указан Лондон, а в других— Амстердам).

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

SECOND { S#, STATUS, CITY

} SP    { S#, P#, QTY }

Диаграммы функциональных зависимостей для этих двух переменных отношения показаны на рис. 12.7, а примеры значений данных — на рис. 12.8. Обратите внимание, что теперь в них имеется и информация о поставщике с номером S5 (в переменной отношения SECOND, но не в переменной отношения SP). Фактически теперь переменная отношения SP точно совпадает с нашей обычной переменной отношения поставок.

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

■     Операция INSERT. Теперь информацию о том, что поставщик с номером S5 нахо дится в Афинах, можно поместить в базу данных, вставив соответствующий кортеж в переменную отношения SECOND, причем даже в том случае, если он в настоящее время не поставляет никаких деталей.

■     Операция DELETE. Теперь вполне можно исключить информацию о поставке, в ко торой собраны сведения о поставщике с номером S3 и о детали с номером Р2. Доста точно удалить соответствующий кортеж из переменной отношения SP, причем ин формация о том, что поставщик с номером S3 находится в Париже, не теряется.

Рис. 12.7. Функциональные зависимости в переменных отношения SECOND и SP

Рис. 12.8. Примеры значений данных в переменных отношения SECOND и SP

■  Операция UPDATE. В переработанной структуре название города для  каждого  поставщика указывается всего один раз, поскольку существует только один кортеж для данного поставщика в переменной отношения  SECOND (первичным ключом этой переменной отношения является атрибут {S#}). Иначе говоря, избыточность данных S#-CITY устранена. Благодаря этому теперь можно изменить название города Лондон для поставщика с номером si на Амстердам, не рискуя привести базу данных  в  несогласованное  состояние, поскольку достаточно изменить название города в соответствующем кортеже переменной отношения SECOND.

Сравнивая рис. 12.5 и 12.7, можно заметить, что суть разбиения переменной отношения FIRST на переменные отношения SECOND и SP состояла в исключении зависимостей, которые не являлись неприводимыми. Именно благодаря этому в новом варианте удается избежать упомянутых ранее трудностей. Интуитивно ясно, что в переменной отношения FIRST атрибут CITY описывал не  сущность, которая идентифицируется первичным  ключом  (поставка),  а  поставщика,  выполнявшего  эту  поставку  (аналогичное утверждение можно  сделать и об атрибуте STATUS). Смешивание этих двух типов информации в  одной переменной отношения и было основной причиной возникновения описанных выше проблем.

Теперь можно дать определение второй нормальной формы6.

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

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

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

{S#} и комбинация атрибутов {S#, p#}), тогда как переменная отношения  FIRST не находится в этой форме. Всякую переменную отношения, которая  находится в первой нормальной форме, но не находится во второй, всегда можно свести к эквивалентному множеству переменных отношения, находящихся в  2НФ.  Этот процесс заключается в замене переменной отношения в 1НФ  подходящим набором проекций, эквивалентных исходной переменной отношения, в том смысле, что при необходимости ее всегда можно будет восстановить с помощью обратной операции соединения данных проекций. В нашем примере переменные отношения SECOND и SP — это проекции7  переменной отношения  FIRST, а переменная отношения FIRST является соединением переменных отношения SECOND и SP по атрибуту S#.

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

R { А, В, С, D } PRIMARY KEY { А, В }

/* Предполагается наличие функциональной зависимости А → D */

Процедура нормализации предусматривает замену этой переменной отношения следующими двумя проекциями, R1 и R2.

R1 { A, D }

PRIMARY KEY { A }

R2 { А, В, С }

PRIMARY KEY { А, В } FOREIGN KEY { A } REFERENCES Rl

Переменная отношения R всегда может быть восстановлена посредством соединения переменных отношения Rl и R2 по внешнему ключу и соответствующему ему первичному ключу этих переменных отношения.

Вернемся к рассматриваемому примеру. Следует отметить, что выбранная структура переменных отношения SECOND и SP все еще может вызвать  некоторые проблемы.

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

7  Если не учитывать того факта, что переменная отношения SECOND может содержать  дополнительные кортежи (например, кортеж для поставщика с номером S5), которые не будут иметь  аналога в переменной отношения FIRST (см. рис. 11.8). Иначе говоря, новая структура может содержать информацию, которую невозможно будет представить в исходной структуре. В этом смысле новую структуру можно рассматривать как более адекватное представление реального мира.

Глава 12. Дальнейшая нормализация: формы 1НФ, 2НФ, ЗНФ и НФБК    473

еще не являются взаимно независимыми. Диаграмма функциональных зависимостей для нее по-прежнему имеет вид более сложный, чем это требуется для диаграммы ФЗ переменной отношения, находящейся в ЗНФ. В частности, зависимость атрибута STATUS от атрибута s#, хотя и является функциональной и действительно неприводимой, одновременно является транзитивной (через атрибут CITY). ЭТО означает, что каждое значение атрибута s# определяет значение атрибута CITY, а значение атрибута CITY в свою очередь определяет значение атрибута STATUS. В общем случае, как уже было показано в главе 11, если имеют место две функциональные зависимости, А → в и в → с, то имеет место и транзитивная функциональная зависимость А → С. Однако наличие транзитивных зависимостей может привести к возникновению описанных ниже аномалий обновления. (В данном случае основное внимание будет сосредоточено на избыточности     данных "город—статус",    соответствующей    функциональной    зависимости

CITY  →   STATUS.)

■  Операция INSERT. Нельзя поместить в базу данных сведения о том, что опреде ленный город характеризуется некоторым статусом (например, нельзя указать, что все поставщики из Рима должны иметь статус 50), до тех пор, пока в этом городе не появится конкретный поставщик.

■  Операция DELETE. При удалении из переменной отношения SECOND кортежа для некоторого города, представленного в ней этим единственным кортежем, будут удалены не только сведения о поставщике из данного города, но и информация о том, какой статус соответствует самому городу. Например, при удалении из пере менной отношения SECOND кортежа для поставщика с номером S5 будет утрачена информация о том, что для Афин был установлен статус 30. (И в этом случае опе рации вставки и удаления фактически являются двумя сторонами одной и той же медали.)

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

■  Операция UPDATE. В переменной отношения SECOND значение статуса для каж дого города повторяется несколько раз (поэтому она все еще обладает некоторой избыточностью). Следовательно, если нужно будет изменить для Лондона значе ние статуса 20 на 30, то потребуется отыскать в переменной отношения SECOND все кортежи, в которых связаны между собой значения London и 20 (для внесения соответствующих изменений). В противном случае база данных окажется в проти воречивом состоянии (в одних кортежах статус для Лондона будет равен 20, а в других— 30).

И вновь для решения этой проблемы следует заменить исходную переменную отношения (в данном случае SECOND) следующими двумя проекциями.

SC { S#, CITY }

CS { CITY, STATUS }

Диаграммы функциональных зависимостей для этих переменных отношения показаны на рис. 12.9, а их содержимое — на рис. 12.10. Обратите внимание, что информация о статусе Рима (Rome) включена только в переменную отношения CS. Данное преобразование обратимо, поскольку переменная отношения SECOND  может быть получена посредством соединения переменных отношения SC и CS по атрибуту CITY.

Рис. 12.9. Функциональные зависимости в переменных отношения SC и CS

Рис. 12.10. Данные в переменных отношения SC и CS

И на этот раз вполне очевидно, что подобное изменение структуры переменных отношения позволяет устранить все описанные выше проблемы в операциях обновления. Читателю предлагается самостоятельно разобраться в подробностях решения этих проблем. Сравнивая рис. 12.7 и 12.9, можно заметить, что благодаря дальнейшей декомпозиции удалось исключить транзитивную зависимость атрибута STATUS ОТ атрибута s#. Это позволило избавиться от всех существовавших трудностей. Интуитивно понятное объяснение состоит в том, что в переменной отношения SECOND атрибут STATUS соответствует не той сущности, которая идентифицируется первичным ключом отношения (т.е. номером поставщика), а содержит информацию о городе, в котором в настоящее время поставщик проводит свои деловые операции. Именно смешивание этих двух типов информации в одной переменной отношения приводило к возникновению  описанных выше проблем.

Теперь можно дать определение третьей нормальной формы.

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

Переменные отношения SC и CS находятся в третьей нормальной форме, причем первичными ключами в них являются, соответственно, атрибуты {S#} и {CITY}. Переменная отношения SECOND не находится в третьей нормальной форме. Переменная отношения,

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

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

R   {   А,   В,   С   } PRIMARY  KEY  {   A  }

/*  Предполагается  наличие  функциональной  зависимости  В → */

Процедура нормализации предусматривает замену переменной отношения R следующими двумя проекциями, R1 и R2.

R1 { В, С }

PRIMARY KEY { В }

R2 { А, В }

PRIMARY KEY { A }

FOREIGN KEY { В } REFERENCES Rl

Переменная отношения R может быть восстановлена посредством соединения переменных отношения R1 и R2 по внешнему ключу и соответствующему ему первичному ключу этих переменных отношения.

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

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

Источник: Дейт К. Дж., Введение в системы баз данных, 8-е издание.: Пер. с англ. — М.: Издательский дом «Вильямс», 2005. — 1328 с.: ил. — Парал. тит. англ.

По теме:

  • Комментарии