учебник БД

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

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

Для определения второй нормальной формы необходимо ввести концепцию функциональной зависимости. Это зависимость, связывающая атрибуты в одной таблице с единственным значением в другой таблице. Функциональную зависимость для таблиц А и В принято обозначать как А В. Это понятие подводит "на один шаг" к родственной концепции объединения таблиц в отношения типа 1:1 или 1:М. Приведем определение второй нормальной формы.

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

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

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

101

Цель разработки 2НФ состоит в устранении этих проблем.

Вэтих таблицах, представленных в 2НФ, аномалия удаления возникнуть не может.

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

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

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

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

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

Уместно сделать несколько замечаний о недостатках, присущих даже таблицам, представленным в 3НФ. Существуют варианты, когда имеет смысл разделить таблицу на более мелкие таблицы, если часть представленных в ней данных непостоянна и часто обновляется, а остальные данные пассивны и изменяются в редких случаях. Также есть смысл объединить таблицы, когда необходимо обеспечить высокую скорость реакции на запрос. Можно даже пойти на дублирование данных в таблицах, если это позволит снизить затраты на обработку запросов, хотя формально не следовало бы этого делать.

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

* временем выполнения запросов; * временем проведения обновлений;

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

102

3.2.6 Четвертая нормальная формы

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

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

Прежде всего, для существования многозначной зависимости требуется существование пар строк. А и В могут быть как отдельными атрибутами, так и объединением некоторого набора атрибутов. Тривиальная многозначная зависимость для А В существует в случае, когда В является подмножеством А или А объединяет B=XS (более крупная таблица содержит исходную таблицу).

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

Таблица 3.2. Обучение

Для примера рассмотрим таблицу "Обучение". В ней есть многозначная зависимость "Дисциплина-Преподаватель": дисциплина (в примере Информатика) может читаться несколькими преподавателями (в примере Шипиловым и Голованевским). Есть и другая многозначная зависимость "Дисциплина-Учебник": при изучении Информатики используются учебники "Паскаль для всех" и "Язык Си". При этом Преподаватель и Учебник не связны функциональной зависимостью, что приводит к появлению избыточности (для добавление еще одного учебника придется ввести в таблицу две новых строки). Дело улучшается при замене этой таблицы на две: (Дисциплина-Преподаватель и Дисциплина-Учебник).

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

103

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

Таблица Х представлена в 4НФ тогда и только тогда, когда она представлена в БКНФ и для любой многозначной зависимости А->В в этой таблице можно сказать, что А является первичным ключом таблицы X.

3.2.7 Пятая нормальная формы

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

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

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

Таблицы находиться в пятой нормальная форма (5НФ) тогда и только тогда, когда она представлена в 4НФ и не содержит зависимостей соединения. Таблица R с подмножеством столбцов A,B,…,Z удовлетворяет зависимости соединения, если и только если каждое допустимое значение R равно соединению его проекций на подмножество

A,B,…,Z.

3.2.8 Алгоритм нормализации (приведение к 3НФ)

Итак, алгоритм нормализации (т.е. алгоритм приведения отношений к 3НФ) описывается следующим образом.

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

104

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

Исходное отношение: .

R(K1, K2 , A1,..., An , B1,..., Bm ) .

Ключ: {K1, K2} - сложный. Функциональные зависимости:

{K1, K2} →{A1,..., An , B1,..., Bm}. - зависимость всех атрибутов от ключа отношения.

{K1} →{A1,..., An } - зависимость некоторых атрибутов от части сложного ключа.

Декомпозированные отношения:

R1(K1, K2 , B1,..., Bm ) - остаток от исходного отношения. Ключ {K1, K2}

R2 (K1, A1,..., An ) - атрибуты, вынесенные из исходного отношения вместе с частью сложного ключа. Ключ K1 .

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

Исходное отношение: R(K, A1,..., An , B1,..., Bm ) .

Ключ: K.

Функциональные зависимости:

K →{A1,..., An , B1,..., Bm} - зависимость всех атрибутов от ключа отношения.

{A1,..., An } →{B1,..., Bm} - зависимость некоторых неключевых атрибутов от других неключевых атрибутов.

Декомпозированные отношения:

R1(K, A1,..., Am ) - остаток от исходного отношения. Ключ K.

R2 ( A1,..., An , B1,..., Bm ) - атрибуты, вынесенные из исходного отношения вместе с детерминантом функциональной зависимости. Ключ {A1,..., An }.

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

105

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

3.2.9 Анализ критериев для нормализованных и ненормализованных моделей данных. Сравнение нормализованных и ненормализованных моделей

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

Таблица 3.3. Сравнительный анализ нормальных форм

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

106

У слабо нормализованных отношений единственное преимущество - если к базе данных обращаться только с запросами на выборку данных, то для слабо нормализованных отношений такие запросы выполняются быстрее.

Это связано с тем, что в таких отношениях уже как бы произведено соединение отношений и на это не тратится время при выборке данных.

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

3.2.10 OLTP и OLAP-системы

Можно выделить некоторые классы систем, для которых больше подходят сильно или слабо нормализованные модели данных.

Сильно нормализованные модели данных хорошо подходят для так называемых

OLTP-приложений (On-Line Transaction Processing (OLTP)- оперативная обработка транзакций). Типичными примерами OLTP-приложений являются системы складского учета, системы заказов билетов, банковские системы, выполняющие операции по переводу денег, и т.п. Основная функция подобных систем заключается в выполнении большого количества коротких транзакций. Сами транзакции выглядят относительно просто, например, "снять сумму денег со счета А, добавить эту сумму на счет В". Проблема заключается в том, что, во-первых, транзакций очень много, во-вторых, выполняются они одновременно (к системе может быть подключено несколько тысяч одновременно работающих пользователей), в-третьих, при возникновении ошибки, транзакция должна целиком откатиться и вернуть систему к состоянию, которое было до начала транзакции (не должно быть ситуации, когда деньги сняты со счета А, но не поступили на счет В). Практически все запросы к базе данных в OLTP-приложениях состоят из команд вставки, обновления, удаления. Запросы на выборку в основном предназначены для предоставления пользователям возможности выбора из различных справочников. Большая часть запросов, таким образом, известна заранее еще на этапе проектирования системы. Таким образом, критическим для OLTP-приложений является скорость и надежность выполнения коротких операций обновления данных. Чем выше уровень нормализации данных в OLTPприложении, тем оно, как правило, быстрее и надежнее. Отступления от этого правила могут происходить тогда, когда уже на этапе разработки известны некоторые часто возникающие запросы, требующие соединения отношений и от скорости выполнения которых существенно зависит работа приложений. В этом случае можно пожертвовать нормализацией для ускорения выполнения подобных запросов.

Другим типом приложений являются так называемые OLAP-приложения (On-Line Analitical Processing (OLAP) - оперативная аналитическая обработка данных). Это

107

обобщенный термин, характеризующий принципы построения систем поддержки принятия решений (Decision Support System - DSS), хранилищ данных (Data Warehouse), систем интеллектуального анализа данных (Data Mining). Такие системы предназначены для нахождения зависимостей между данными (например, можно попытаться определить, как связан объем продаж товаров с характеристиками потенциальных покупателей), для проведения анализа "что если…". OLAP-приложения оперируют с большими массивами данных, уже накопленными в OLTP-приложениях, взятыми их электронных таблиц или из других источников данных. Такие системы характеризуются следующими признаками:

Добавление в систему новых данных происходит относительно редко крупными блоками (например, раз в квартал загружаются данные по итогам квартальных продаж из OLTP-приложения).

•Данные, добавленные в систему, обычно никогда не удаляются.

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

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

•Скорость выполнения запросов важна, но не критична.

Данные OLAP-приложений обычно представлены в виде одного или нескольких гиперкубов, измерения которого представляют собой справочные данные, а в ячейках самого гиперкуба хранятся собственно данные. Например, можно построить гиперкуб, измерениями которого являются: время (в кварталах, годах), тип товара и отделения компании, а в ячейках хранятся объемы продаж. Такой гиперкуб будет содержать данных о продажах различных типов товаров по кварталам и подразделениям. Основываясь на этих данных, можно отвечать на вопросы вроде "у какого подразделения самые лучшие объемы продаж в текущем году?", или "каковы тенденции продаж отделений Юго-Западного региона в текущем году по сравнению с предыдущим годом?"

Физически гиперкуб может быть построен на основе специальной многомерной модели данных (MOLAP - Multidimensional OLAP) или построен средствами реляционной модели данных (ROLAP - Relational OLAP).

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

108

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

3.2.11 Нормализация на практике

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

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

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

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

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

Определение второй нормальной формы (2НФ) выглядит следующим образом:

отсутствие частичной зависимости. Каждый неключевой столбец зависит от полного первичного ключа, то есть от всех столбцов, из которых он состоит (если первичный ключ составной). Таблица не удовлетворяет этому определению. Информация о городе не зависит от информации о штате. В частности, столбцы CITY, LPOP, СРОР и PICTING не зависят от столбца названия штата STATE. Следовательно, нужно разбить ее на две таблицы. Смысл этого разбиения в том, что теперь штаты и города становятся отдельными

109

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

Определение третьей нормальной формы (ЗНФ) выглядит следующим образом:

отсутствие транзитивной зависимости. Ни один неключевой столбец не должен зависеть от другого неключевого столбца. Можно сказать, что таблица находится в ЗНФ, если все ее неключевые столбцы зависят только от ключа. Если после удаления повторяющихся групп неключевой столбец зависит от ключа, мы имеем таблицу 2НФ. А если сохраняется зависимость от ключа, то получится ЗНФ. Наша таблица городов не удовлетворяет этому требованию, поскольку столбец PCTINC (относительный прирост) зависит от столбцов СРОР (текущее количество жителей) и LPOP (количество жителей в предыдущем году). То есть этот столбец является функцией от двух других. Такой столбец называется производным (derived). Но заметим, что все три столбца являются неключевыми. Само собой напрашивается решение: исключить из таблицы столбец PCTINC и вычислять соответствующее значение "по ходу", то есть во время работы с таблицей. При частом обращении можно использовать для этого представление (view). В таблице штатов столбец SPOP (население штата) зависит от столбца ABBREV (аббревиатура штата), поскольку последний является потенциальным ключом, хотя и не первичным. В результате мы получим три таблицы ЗНФ.

110