5. Датологическое проектирование реляционных бд

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

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

Очевидно, что для одной и той же предметной области реляционные отношения можно спроектировать множеством различных способов. Например, можно спроектировать несколько отношений с большим количеством атрибутов, или наоборот, разнести все атрибуты по большому числу мелких отношений. В общем случае проектирование схемы БД может быть выполнено двумя путями: путем декомпозиции (разбиения), когда исходное множество отношений, входящих в схему БД заменяется другим множеством отношений (число их при этом возрастает), являющихся проекциями исходных отношений; или путем синтеза, то есть путем компоновки из заданных исходных элементарных зависимостей между объектами предметной области схемы БД.

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

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

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

Рассмотрим в качестве предметной области некоторую организацию, выполняющую некоторые проекты. Модель предметной области опишем следующим неформальным текстом: Сотрудники организации выполняют проекты; Проекты состоят из нескольких заданий; Каждый сотрудник может участвовать в одном или нескольких проектах, или временно не участвовать ни в каких проектах; Над каждым проектом может работать несколько сотрудников, или временно проект может быть приостановлен, тогда над ним не работает ни один сотрудник; Над каждым заданием в проекте работает ровно один сотрудник; Каждый сотрудник числится в одном отделе; Каждый сотрудник имеет телефон, находящийся в отделе сотрудника.

В ходе дополнительного уточнения того, какие данные необходимо учитывать, выяснилось следующее: О каждом сотруднике необходимо хранить табельный номер и фамилию. Табельный номер является уникальным для каждого сотрудника; Каждый отдел имеет уникальный номер; Каждый проект имеет номер и наименование. Номер проекта является уникальным; Каждая работа из проекта имеет номер, уникальный в пределах проекта. Работы в разных проектах могут иметь одинаковые номера.

Предположим, что в ходе логического моделирования на первом шаге предложено хранить данные в одном отношении:

СОТР_ОТД_ПР (Н_СОТР, ФАМ, Н_ОТД, ТЕЛ, Н_ПРО, ПРОЕКТ, Н_ЗАДАН),

где: Н_СОТР - табельный номер сотрудника; ФАМ - фамилия сотрудника; Н_ОТД - номер отдела, в котором числится сотрудник; ТЕЛ - телефон сотрудника; Н_ПРО - номер проекта, над которым работает сотрудник; ПРОЕКТ - наименование проекта, над которым работает сотрудник; Н_ЗАДАН - номер задания, над которым работает сотрудник.

Так как каждый сотрудник в каждом проекте выполняет ровно одно задание, то в качестве потенциального ключа отношения необходимо взять пару атрибутов {Н_СОТР, Н_ПРО}. А состояние предметной области отражается следующими фактами: Сотрудник Иванов, работающий в 1 отделе, выполняет в первом проекте "Космос" задание 1 и во втором проекте "Климат" задание 1; Сотрудник Петров, работающий в 1 отделе, выполняет в первом проекте "Космос" задание 2; Сотрудник Сидоров, работающий во 2 отделе, выполняет в первом проекте "Космос" задание 3 и во втором проекте "Климат" задание 2. Это состояние отражается в таблице 5. (курсивом выделены ключевые атрибуты):

Таблица 5. Отношение СОТР_ОТД_ПР

Н_СОТР	ФАМ	Н_ОТД	ТЕЛ	Н_ПРО	ПРОЕКТ	Н_ЗАДАН
1	Иванов	1	11-22-33	1	Космос	1
1	Иванов	1	11-22-33	2	Климат	1
2	Петров	1	11-22-33	1	Космос	2
3	Сидоров	2	33-22-11	1	Космос	3
3	Сидоров	2	33-22-11	2	Климат	2

Даже одного взгляда на таблицу отношения СОТР_ОТД_ПР достаточно, чтобы увидеть, что данные хранятся в ней с большой избыточностью. Во многих строках повторяются фамилии сотрудников, номера телефонов, наименования проектов. Кроме того, в данном отношении хранятся вместе независимые друг от друга данные - и данные о сотрудниках, и об отделах, и о проектах, и о работах по проектам. Пока никаких действий с отношением не производится, это не страшно. Но как только состояние предметной области изменяется, то, при попытках соответствующим образом изменить состояние базы данных, возникает большое количество проблем.

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

Так как аномалии проявляют себя при выполнении операций, изменяющих состояние базы данных, то различают следующие виды аномалий: Вставки; Обновления; Удаления.

В отношении СОТРУ_ОТД_ПР можно привести примеры следующих аномалий.

Вставки: В отношение СОТРУДНИКИ_ОТДЕЛЫ_ПРОЕКТЫ нельзя вставить данные о сотруднике, который пока не участвует ни в одном проекте. Действительно, если, например, во втором отделе появляется новый сотрудник, скажем, Пушников, и он пока не участвует ни в одном проекте, то мы должны вставить в отношение кортеж (4, Пушников, 2, 33-22-11, null, null, null). Это сделать невозможно, так как атрибут Н_ПРО (номер проекта) входит в состав потенциального ключа, и, следовательно, не может содержать null-значений. Точно также нельзя вставить данные о проекте, над которым пока не работает ни один сотрудник.

Причина аномалии - хранение в одном отношении разнородной информации (и о сотрудниках, и о проектах, и о работах по проекту).

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

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

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

Вывод - увеличивается сложность разработки базы данных. База данных, основанная на такой модели, будет работать правильно только при наличии дополнительного программного кода.

Удаления: При удалении некоторых данных может произойти потеря другой информации. Например, если закрыть проект "Космос" и удалить все строки, в которых он встречается, то будут потеряны все данные о сотруднике Петрове. Если удалить сотрудника Сидорова, то будет потеряна информация о том, что в отделе номер 2 находится телефон 33-22-11. Если по проекту временно прекращены работы, то при удалении данных о работах по этому проекту будут удалены и данные о самом проекте (наименование проекта). При этом если был сотрудник, который работал только над этим проектом, то будут потеряны и данные об этом сотруднике.

Причина аномалии - хранение в одном отношении разнородной информации (и о сотрудниках, и о проектах, и о работах по проекту).

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

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

Пусть имеется следующее отношение: Поставка (Название_фирмы, Адрес, Товар, Кол-во, цена).

Рассмотрим возможные аномалии этого отношения:

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

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

Аномалии удаления: удаление всех кортежей с поставками от некоторого поставщика приведет к потере адреса и других реквизитов фирмы.

Аномалии включения: предположим, что заключен договор, но еще нет поставок от некоторой фирмы: следует ли включать кортежи с пустым (NULL) значением количества? А не забудем ли мы впоследствии удалить строку с неопределенным значением?

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

Для удовлетворения вышеотмеченных требований Э.Коддом предложен аппарат нормализации отношений.

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

В теории реляционных баз данных обычно выделяется следующая последовательность нормальных форм: первая нормальная форма (1NF); вторая нормальная форма (2NF); третья нормальная форма (3NF); нормальная форма Бойса-Кодда (BCNF); четвертая нормальная форма (4NF); пятая нормальная форма, или нормальная форма проекции-соединения (5NF или PJ/NF).

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

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

Функциональная зависимость. В отношении R атрибут Y функционально зависит от атрибута X (X и Y могут быть составными) в том и только в том случае, если каждому значению X соответствует в точности одно значение Y: R.X-->R.Y. Например, Табельный номер ->Фамилия; Должность ->Зарплата.

Полная функциональная зависимость. Функциональная зависимость R.X -> R.Y называется полной, если атрибут Y не зависит функционально от любого точного подмножества X.

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

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

Чтение лекций (Таб_номер, Название_курса, Кол-во_часов) Название_курса ->Кол-во_часов

Зависимость неключевого атрибута Кол-во_часов от части составного ключа говорит о частичной зависимости.

Транзитивная функциональная зависимость. Функциональная зависимость R.X -> R.Y называется транзитивной, если существует такой атрибут Z, что имеются функциональные зависимости R.X -> R.Z и R.Z -> R.Y и отсутствует функциональная зависимость R.Z ->R.X.

Фамилия ->Офис ->Телефон.

Взаимно независимые атрибуты. Два или более атрибута взаимно независимы, если ни один из этих атрибутов не является функционально зависимым от других.

В отношении Чтение лекций: Кол-во_часов ->->Таб_номер; Таб_номер ->-> Кол-во_часов.

Отношение находится в 1NF тогда и только тогда, когда все входящие в него атрибуты являются атомарными (неделимыми).

Рассмотрим отношение R1

Первичный ключ: Таб_ном, Имя ребенка;

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

Таб_номер -ФИО; Таб_номер -> Оклад; Таб_номер ->Офис; Офис ->Телефон; Таб_номер, Имя_ребенка-> Возраст_ребенка.

Атрибуты ФИО, Оклад, Офис не находятся в полной функциональной зависимости от ключа, поскольку функционально зависят от части ключа (Таб_номер). Следствием это является: дублирование информации; нет возможности занести кортеж с сотрудником без детей (ключ не может содержать неопределенного значения); при удалении кортежа теряем не только информацию о ребенке сотрудника, но, возможно, о месте работы сотрудника, телефоне офиса и т.д.); при переводе сотрудника в другой офис вынуждены модифицировать все кортежи, описывающие этого сотрудника, иначе получим несогласованный результат.

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

Наличие транзитивной зависимости: Таб_номер->Офис; Офис ->Телефон

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

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

Для исключения транзитивной зависимости нужно произвести декомпозицию отношения R4 в два отношения R5 и R6.

В результате преобразований имеем три отношения в 3NF, свободные от отмеченных аномалий:

Отношение R3	Первичный ключ:	Таб_номер, Имя ребенка
	Функциональные зависимости:	Таб_номер, Имя_ребенка ->Возраст
Отношение R5	Первичный ключ:	Таб_номер
	Функциональные зависимости:	Таб_номер -> ФИО Таб_номер -> Оклад Таб_номер -> Офис
Отношение R6	Первичный ключ:	Офис
	Функциональные зависимости:	Офис -> Телефон

Если отказаться от того ограничения, что отношение обладает единственным ключом, то определение 3NF примет следующую форму:

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