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

¨ Пусть имеется некий предварительный проект БД. Сначала сведем многотабличную БД к универсальной таблице.

Определение f-зависимостей дано для одной таблицы, но зависимости имеются и межтабличные.

¨ Пусть зафиксировано множество F функциональных зависимостей. Построим его замыкание F*.

В явном виде F может и не содержать «плохих» зависимостей. Но аномалии могут быть, если «плохие» зависимости выводимы из F.

¨ Устраняем

§ зависимости от части возможных ключей, т.е. приводим БД ко 2-й нормальной форме,

§ транзитивные зависимости, т.е. приводим БД к 3-й нормальной форме.

¨ Приведение 3-й нормальной форме не гарантирует устранения всех аномалий.

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

§ Известны и изучены зависимости более общего вида, нефункциональные.

¨ Аномалии не следует трактовать как ошибки или парадоксы. Скорее это проявление объективно существующих проблем представления предметной области. Устранение аномалий тоже не следует понимать как полное устранение проблем. Скорее это перегруппировка проблем, «перевод их в другую плоскость».

Вернемся к примеру устранения транзитивной зависимости. Пусть T – исходная таблица, а T1,T2 – полученные в результате декомпозиции.

§ В исходной таблице T были f-зависимости: T. Nзачетки ®T.Группа; T.Группа®T.Количество; T. Nзачетки ®T.Количество.

§ В базе данных с таблицами T1,T2 остались: T1. Nзачетки ®T1.Группа; T2. Группа ®T2.Количество; межтабличная связь T1.Группа=T2.Группа и межтабличная зависимость T1. Nзачетки ®T2.Количество.

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

§ Устранение аномалий, связанных с устраненной транзитивной зависимостью, породило другие проблемы: теперь труднее решать задачу «По номеру зачетки вычислить средний балл студента в текущей сессии», появилась новая задача – контроль ссылочной целостности T1.Группа=T2.Группа.

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

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

¨ Сначала проанализировать имеющиеся проблемы, для этого привести БД к достаточно сильной нормальной форме.

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

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

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

Как было отмечено выше, зависимости между атрибутами фиксируют семантику предметной области.

Когда разработчик знакомится с новой предметной областью, не столь очевиден и однозначен ответ на вопрос – что следует считать объектами этой предметной области. В первую очередь выявляются скорее списки атрибутов и зависимости между ними. Разговоры с пользователями – специалистами предметной области обычно происходят в терминах – какие документы имеются, каковы реквизиты этих документов, как они связаны между собой и как по одним реквизитам одних документов рассчитать значения других(*). Некие объекты в названиях реквизитов документов конечно присутствуют, но...

ПРИМЕР(**). Ставится задача спроектировать БД для секретаря некой игровой лиги небольшого городка.

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

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

§ Секретарю понадобятся фамилии, телефонные номера и адреса всех игроков лиги.

§ Секретарю необходимо знать состав игроков каждой из команд и фамилию капитана.

§ Для каждого игрока надо знать его результативность на начало сезона и набранные им очки в каждой из трех игр каждой еженедельной серии текущего сезона.

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

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

NКоманды: номер, однозначно идентифицирующий

команду.

НКоманды: наименование, однозначно идентифицирующее

команду.

ФИОИгрока: ФИО, однозначно идентифицирующее игрока.

NПлощадки: номер, однозначно идентифицирующий

игровую площадку.

ФИОКапитана: ФИО, однозначно идентифицирующее не только

игрока – капитана команды, но и команду.

Телефон: номер телефона игрока, несколько

игроков могут иметь один и тот же телефон.

Улица: улица, на которой проживает игрок.

NНедели: порядковый номер недели в сезоне.

Рез0: результативность игрока на начало сезона.

ОИгры1: число набранных игроком очков

в 1-й игре недели.

ОИгры2: аналогично... во 2-й игре недели.

ОИгры3:... в 3-й игре недели.

(ФИОИгрока,NНедели) – единственный возможный ключ.

НКоманды«NКоманды

НКоманды«ФИОКапитана

ФИОКапитана«NКоманды

ФИОИгрока®NКоманды,Улица,Телефон,Рез0

ФИОИгрока,NНедели®ОИгры1,ОИгры2,ОИгры3,NПлощадки

NПлощадки,NНедели®NКоманды

NКоманды,NНедели®NПлощадки

В диаграмме f-зависимостей: штрих-стрелками отмечены зависимости от части возможного ключа, белой стрелкой – транзитивная зависимость от возможного ключа.

ФИОИгрока,NНедели®NПлощадки является транзитивной зависимостью:

ФИОИгрока,NНедели®NКоманды,NНедели

и NКоманды,NНедели®NПлощадки.

Замыкание базового набора f-зависимостей содержит и другие транзитивные зависимости, например:

ФИОИгрока,NНедели®NКоманды,

т.к. ФИОИгрока,NНедели®NПлощадки,NНедели

и NПлощадки,NНедели®NКоманды.

¨ Устраним сначала транзитивную зависимость ФИОИгрока,NНедели®NПлощадки:

R1(ФИОИгрока,NНедели,NКоманды,НКоманды,ФИОКапитана,

Телефон,Улица,Рез0,ОИгры1,ОИгры2,ОИгры3)

R2(NКоманды,NНедели,NПлощадки)

¨ Теперь в R1 устраним оставшуюся зависимость от части первичного ключа:

R11(ФИОИгрока,NКоманды,НКоманды,ФИОКапитана,

Телефон,Улица,Рез0)

R12(ФИОИгрока,NНедели,ОИгры1,ОИгры2,ОИгры3)

¨ Теперь в R11 устраним транзитивную зависимость ФИОИгрока ®НКоманды,ФИОКапитана, которая была в исходной универсальной таблице, но для R11 она к тому же стала зависимостью от первичного ключа.

R111(ФИОИгрока,NКоманды,Телефон,Улица,Рез0)

R112(NКоманды,НКоманды,ФИОКапитана)

В итоге получили базу данных с 4-мя таблицами. Соответствующая ER/IDEF1X-диаграмма (DBLEC\LIGA.ER1):

5.2.2. Модель данных «сущность-связь» и применение ER-диаграмм.

Список литеpатуpы.

1. Гарсиа-Молина Г., Ульман Д., Уидом Д. Системы баз данных. М.:Вильямс,2003. - 1088 с.

2. К. Дейт. Введение в системы баз данных. М.: Вильямс, 2001.-1072 с.

3. М. Грабер М. SQL. Справочное руководство. М.: Лори, 1998. - 292 с.; 2001. - 354 с.

4. Ульман Д.Д. Введение в системы баз данных. М.: «Лори»,2000.-374 с.

5. Фаронов В. Программирование баз данных в Delphi 6. М.: Питер, 2003. - 352 с.

6. Т. Конноли, К. Берг, А. Страчан. Базы данных. М.: Вильямс, 2000. - 1120 с.

7. Дж.С. Боуман, С.Л. Эмерсон, М. Дановски. Практическое руководство по SQL. М.: Вильямс, 2001. - 336 с.

8. Черемных С.В. и др. Структурный анализ систем: IDEF-технологии. - М.: ФиС, 2001 - 208 с.

9. Черемных С.В. и др. Моделирование и анализ систем. IDEF-технологии: практикум. - М.: ФиС, 2002 - 192 с.

10.Маклаков С.В. Моделирование бизнес процессов. - М.: ДИАЛОГ-МИФИ, 2002. - 224 с.