Проектирование реляционных БД на основе принципов нормализации

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

этапы жизненного цикла БД

этапы проектирования 1.Системный анализ и словесное описание информационных объектов предметной области. 2.Проектирование инфологической модели предметной области - частично формализованное описание объектов предметной области в терминах некоторой семантической модели, например, в терминах ЕR-модели. 3.Даталогическое или логическое проектирование БД, то есть описание БД в терминах принятой даталогической модели данных. 4.Физическое проектирование БД, то есть выбор эффективного размещения БД па внешних носителях для обеспечения наиболее эффективной работы приложения.

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

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

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

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

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

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

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

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

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

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

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

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

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

Определение функциональной зависимости

Транзитивная зависимость Функциональная зависимость R,A -> R.B называется транзитивной, если суще­ствует набор атрибутов С такой, что: С не является подмножеством А. С не включает в себя В. Существует функциональная зависимость R.A -> R.C. Не существует функциональной зависимости R.C -> R.A. Существует функциональная зависимость R.C -> R.B.

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

Взаимно-независимые атрибуты это такие атрибуты, которые не зависят функционально один от другого. Если в отношении существует несколько функциональных зависимостей, то каждый атрибут или набор атрибутов, от которого зависит другой атрибут, называется детерминантом отношения. Ряд зависимостей могут быть выведены из других путем применения правил, названных аксиомами Армстронга, по имени исследователя, впервые сформулировавшего их. Это три основных аксиомы: Рефлексивность: если В является подмножеством А, то А->В Дополнение: если А->В, то АС->ВС Транзитивность: если А->В и В->С, то А->С.

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

Пример Для приведения отношения «Расписание» к первой нормальной форме необходимо дополнить каждую строку фамилией преподавателя.

ОПРЕДЕЛЕНИЕ2НФ Отношение находится во второй нормальной форме тогда и только тогда, когда оно нахо­дится в первой нормальной форме и не содержит неполных функциональных зависимостей непервичных атрибутов от атрибутов первичного ключа.

Пример Рассмотрим отношение, моделирующее сдачу студентами текущей сессии. Струк­тура этого отношения определяется следующим набором атрибутов: (ФИО, Номер зач.кн, Группа, Дисциплина, Оценка)

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

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

Определение 3НФ Отношение находится в 3НФ, когда оно находится во второй нормальной форме и не содержит транзитивных зависимостей Рассмотрим отношение, связывающее студентов с группами, факультетами и специальностями (ФИО, Номер зач.кн, Группа, Факультет, Специальность, Выпускающая кафедра)

Функциональные зависимости Номер зач.кн. -> ФИО Номер зач.кн. -> Группа Номер зач.кн. -> Факультет Номер зач.кн. -> Специальность Номер зач.кн. -> Выпускающая кафедра Группа -> Факультет Группа -> Специальность Группа -> Выпускающая кафедра Выпускающая кафедра -> Факультет

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

ОПРЕДЕЛЕНИЕ Формы Бoйca Кодда Отношение находится в нормальной форме БoйcaКодда, если оно находится в третьей нормальной форме и каждый Детерминант отношения являются возможным ключом отношения.

Пример Отношение, которое моделирует сдачу текущей сессии, имеет следующую структуру: (Номер зач,кн.. Идентификатор_студента. Дисциплина, Дата, Оценка) Возможными ключами отношения являются Номер_зач.кн. Дисциплина Дата И Идентификатор_студента, Дисциплина Дата.

Какие функциональные зависимости у нас имеются? Номер_зач.кн, Дисциплина, Дата -> Оценка; Идентификатор__студента, Дисциплина, Дата -> Оценка; Номер зач.кн. -> Идентификатор_студента: Идентификатор_студента -> Номер зач.кн.

Для приведения отношения к нормальной форме БойсаКодда надо разделить отношение, например, на два со следующими схемами; (Идентификатор_студента. Дисциплина. Дата. Оценка) (Номер зач.кн., Идентификатор_студента) или наоборот: (Номер зач.кн., Дисциплина, Дата. Оценка) (Номер зач.кн., Идентификатор_студента) Номер зач.кн.. Идентификатор^студента) Эти схемы равнозначны с точки зрения теории нормализации.

Определение многозначной зависимости

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

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

ОПРЕДЕЛЕНИЕ 4НФ Отношение R находится в четвертой нормальной форме (4NF) в том и только и том случае, если в случае существования многозначной зависимости А >> В все остальные атрибуты R функционально зависят от А. В нашем примере можно произнести декомпозицию исходного отношения в два отношения: (Номер зач.кн., Группа) (Группа, Дисциплина)

PJ-зависимости и 5НФ

Пример Рассмотрим отношение R1: R1(Преподаватель, Кафедра. Дисциплина) Введем следующие обозначения наборов атрибутов: ПК (Преподаватель, Кафедра) ПД (Преподаватель. Дисциплина) КД (Кафедра, Дисциплина)

Приведение к 5НФ Допустим, что отношение R1 удовлетворяет зависимости проекции соединения (ПК, ПД, КД). Тогда отношение R1 не находится в NF/PJ, потому что единственным ключом его является полный набор атрибутов, а наличие зависимости PJ связано с наборами атрибутов, которые не составляют возможные ключи отношения R1. Для того чтобы привести это отношение к NF/PJ, его надо представить в виде трех отношений: R2 (Преподаватель. Кафедра) R3 (Преподаватель. Дисциплина) R4 (Кафедра, Дисциплина)