Проектирование БД начинается с определения всех объектов, сведения о которых будут включены в базу, и определения их атрибутов. Затем атрибуты записываются в виде одной таблицы - исходное отношение.
Пример. Сформировать исходное отношение для БД «Преподаватель_колледжа». Для этого определить какая информация должна храниться в БД, и какую необходимо получать в процессе работы с БД. Получим атрибуты:
ФИО – фамилия и инициалы преподавателя (нет совпадающих).
Должн – должность, занимаемая преподавателем,
Оклад – оклад преподавателя,
Стаж –преподавательский стаж,
Н_Стаж –надбавка за стаж,
Отдел – отдел, в котором числится преподаватель,
Предм – название предмета, читаемого преподавателем,
Группа – номер группы, в которой преподаватель проводит занятия,
ВидЗан- вид занятий, проводимых преподавателем.
Все атрибуты отношения должны иметь простые (атомарные) значения.
ФИО
Долж
Оклад
Стаж
Н_Стаж
Отдел
Предм
Группа
ВидЗан
Иванов
Преп
БД
П-249
Практ
Иванов
Преп
ОС
П-250
Практ
Петров
Преп
ИС
П-249
Практ
Петров
Преп
ЧМ
П-250
Практ
Седов
Ст.пр.
ЧМ
П-250
Лекция
Это отношение содержит избыточное дублирование, которое приводит к аномалии редактирования. Явная избыточность заключается в том, что данные о преподавателе приводятся столько раз, в скольких группах этот преподаватель ведет занятия. Неявная избыточность проявляется в одинаковых окладах и надбавках у всех преподавателей. Если изменить оклад не у всех преподавателей, то БД станет противоречивой.
Исключение избыточности, а значит аномалий, выполняется путем нормализации отношений.
Атрибут В функционально зависит от атрибута А, если каждому значению А соответствует в точности одно значение В(А®В).Атрибуты А и В могут быть составными. Например, ФИО®Отдел, ФИО®Должн.
Функциональная взаимозависимость между А и В(А«В) существует, если существует функциональная зависимость вида А®В и В®А. Например, номер паспорта и ФИО.
Частичная функциональная зависимость– это зависимость неключевого атрибута от части составного ключа. Например, атрибут Долж находится в частичной зависимости от составного ключа отношения (ФИО, Предм, Группа)
Полная функциональная зависимость – зависимость неключевого атрибута от всего составного ключа. Например, атрибут ВидЗан находится в полной зависимости от составного ключа.
Атрибут С транзитивно зависит от атрибута А, если для атрибутов А, В, С выполняются условия А®В и В®С, но обратная зависимость отсутствует. Например, ФИО®Долж®Оклад.
Взаимно независимыеатрибуты – это такие атрибуты, ни один из которых не является функционально зависимым от других атрибутов.
Нормальные формы. Процесс проектирования БД с использованием нормальных форм является итерационным и заключается в последовательном переводе отношений из первой нормальной формы в нормальные формы более высокого порядка по определенным правилам. Выделяют следующую последовательность нормальных форм:
- первая нормальная форма (1НФ);
- вторая нормальная форма (2НФ);
- третья нормальная форма (3НФ);
- усиленная третья нормальная форма Бойса-Кодда (БКНФ);
- четвертая нормальная форма (4НФ);
- пятая нормальная форма (5НФ).
Первая нормальная форма.Отношение находится в первой нормальной форме, если все его атрибуты являются простыми (имеют единственное значение). Исходное отношение строится таким образом, чтобы оно находилось в 1НФ. Например, исходное отношение БД «Преподаватель_колледжа».
ФИО
Долж
Оклад
Стаж
Н_Стаж
Отдел
Предм
Группа
ВидЗан
Иванов
Преп
БД
П-249
Практ
Иванов
Преп
ОС
П-250
Практ
Петров
Преп
ИС
П-249
Практ
Петров
Преп
ЧМ
П-250
Практ
Седов
Ст.пр.
ЧМ
П-250
Лекция
Исходное отношение имеет составной ключ ФИО, Предмет, Группа. В этом отношении имеется явное и неявное избыточное дублирование данных. Часть избыточности устраняется при переводе отношения в 2НФ.
Вторая нормальная форма. Отношение находится в 2НФ, если:
1) отношение находится в 1НФ,
2) каждый не ключевой атрибут функционально полно зависит от первичного ключа (составного).
Или 2) любое неключевое поле должно однозначно идентифицироваться ключевыми полями.
Для перевода отношения в 2НФ используется операция проекции, то есть разложения отношения на несколько отношений. Для БД «Преподаватель_колледжа» получим два отношения:
R1:
ключ составной (ФИО,Предмет,Группа)
ФИО
Предм
Группа
ВидЗан
Иванов
БД
П-249
Практ
Иванов
ОС
П-250
Практ
Петров
ИС
П-249
Практ
Петров
ЧМ
П-250
Практ
Седов
ЧМ
П-250
Лекция
R2:
ключ (ФИО)
ФИО
Долж
Оклад
Стаж
Н_Стаж
Отдел
Иванов
Преп
Петров
Преп
Седов
Ст.пр.
Исключена явная избыточность данных (повторение сведений о преподавателе), но осталась неявное дублирование (повторение оклада для должности, повторение сведений о надбавке за стаж).
Третья нормальная форма. Отношение находится в 3НФ, если:
1) отношение находится в 2НФ,
2) каждый неключевой атрибут нетранзитивно зависит от первичного ключа.
Или
2) ни одно из неключевых полей не должно однозначно идентифицироваться значением другого неключевого поля (полей).
Например, в БД «Преподаватель_колледжа» в R1 отсутствуют транзитивные зависимости, но в R2 они имеются:
Транзитивные зависимости приводят к избыточному дублированию информации в отношении, которое можно устранить операцией проекции на атрибуты, которые являются причиной транзитивных зависимостей. Преобразуем отношение R2 в R3, R4, R5, каждое из которых находится в 3НФ:
R3
ФИО
Долж
Стаж
Отдел
Иванов
Преп
Петров
Преп
Седов
Ст.пр.
R4
Долж
Оклад
Преп
Ст.пр.
R5
Стаж
Н_Стаж
Избыточное дублирование устранено. Если имеется зависимость атрибутов составного ключа от неключевых атрибутов, то нужно перейти к усиленной 3НФ.
Усиленная третья нормальная форма Бойса-Кодда. Отношение находится в БКНФ, если
- оно находится в 3НФ,
- отсутствуют зависимости ключей (атрибутов составного ключа) от неключевых атрибутов.
В примере БД «Преподаватель_колледжа» таких зависимостей нет, и процесс проектирования заканчивается. Таким образом, БД «Преподаватель_колледжа» состоит из таблиц R1, R3, R4, R5. В полученной БД имеется необходимое дублирование данных, но отсутствует избыточное.
R1:
ФИО
Предм
Группа
ВидЗан
Иванов
БД
П-249
Практ
Иванов
ОС
П-250
Практ
Петров
ИС
П-249
Практ
Петров
ЧМ
П-250
Практ
Седов
ЧМ
П-250
Лекция
R3
ФИО
Долж
Стаж
Отдел
Иванов
Преп
Петров
Преп
Седов
Ст.пр.
R4
Долж
Оклад
Преп
Ст.пр.
R5
Стаж
Н_Стаж
На практике построение 3НФ схем отношений в большинстве случаев является достаточным, и на этом процесс проектирования заканчивается.
Пример 1. Сформировать отношения, находящиеся в 3НФ для концептуальных требований БД «Библиотека»:
Книга (название, автор, издательство, дата издания, залоговая цена, наличие в библиотеке)
Библиотекарь (ФИО, дата приема на работу, образование)
Читатель (ФИО, паспорт, телефон, дом.адрес)
Дата выдачи и возврата книги, кем выдана.
s
План проектирования БД:
Формирование исходного отношения, Нормализация отношений.