Тема 4.1. Проектирование баз данных

4.1.1. Проблемы проектирования

Логическое проектирование заключается в определении числа и структуры таблиц, формировании запросов к БД, определении типов отчетных документов, разработке алгоритмов обработки информации, создании форм для ввода и редактировании данных в базе и решении ряда других задач.

При проектировании структур данных для автоматизированных систем выделяют три подхода:

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

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

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

Избыточное дублирование данных и аномалия

Избыточное дублирование данных может привести к проблемам при обработке данных. Простое (неизбыточное) дублирование допускается в базах данных.

Аномалия – такая ситуация, которая приводит к противоречиям в БД либо существенно усложняет обработку данных.

Формирование исходного отношения

Проектирование БД начинается с определения всех объектов, сведения о которых будут включены в БД, и определения атрибутов. Затем атрибуты сводятся в одну таблицу - исходное отношение.

Пример. Формирование исходного отношения.

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

·         ФИО – фамилия и инициалы преподавателя.

·         Должн – должность, занимаемая преподавателем.

·         Оклад – оклад преподавателя.

·         Стаж – преподавательский стаж.

·         Д_Стаж – надбавка за стаж.

·         Каф – номер кафежры.

·         Предм – название предмета.

·         Группа – номер группы, в которой преподаватель ведет занятия.

·         ВидЗан – вид занятий.

Одно из требований к отношениям заключается в том, что все атрибуты отношения имели простые (атомарные) замечания. В исходном отношении каждый атрибут кортежа также должен быть простым. Пример исходного отношения ПРЕПОДАВАТЕЛЬ:

Указанное отношение имеет следующую схему ПРЕПОДАВАТЕЛЬ (ФИО, Должн, Оклад, Д_Стаж, Каф, Предм, Группа, ВидЗан).

Исходное отношение АРЕПОДАВАТЕЛЬ содержит избыточное дублирование данных, которое и является причиной аномалий редактирования. Различают избыточность явную и неявную.

Явная избыточность заключается в том, что в отношении ПРЕПОДАВАТЕЛЬ строки с данными о преподавателях, проводящих занятия в нескольких группах, повторяются соответствующее число раз.

Неявная избыточность в отношении ПРЕПОДАВАТЕЛЬ проявляется в одинаковых окладах у всех преподавателей и в одинаковых надбавках за одинаковый стаж. Поэтому, если при изменении окладов за должность с 500 на 510 это значение изменят у всех преподавателей, кроме, например, Сидорова, то база станет противоречивой.

4.1.2. Метод нормальных форм

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

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

Выделяют следующую последовательность нормальных форм:

·         первая нормальная форма (1НФ);

·         вторая нормальная форма (2НФ);

·         третья нормальная форма (3НФ);

·         усиленная третья нормальная форма, или форма Бойса-Кодда (БКНФ);

·         четвертая нормальная форма (4НФ);

·         пятая нормальная форма (5НФ).

Первая нормальная форма.

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

Основной операцией метода является операция проекции. Предположим, что в отношении R(A, B, C, D, E,…) устранение функциональной зависимости C→D  позволит перевести его в следующую нормальную форму. Для решения этой задачи выполним декомпозицию отношения R на два новых отношения R1(A, B, C, E,…) и R 2(C, D). Отношение R2 является проекцией отношения R на атрибуты C и D.

Исходное отношение ПРЕПОДАВАТЕЛЬ имеет составной ключ ФИО, Предм, Группа и находится в 1НФ, поскольку все атрибуты простые.

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

Эта частная зависимость приводит к следующему:

1.      В отношении присутствует явное и неявное избыточное дублирование данных.

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

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

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

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

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

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

В результате получим два отношения R1 и R2.

R1

R2

В отношении R1 первичный ключ является составным и состоит из атрибутов ФИО, Предм, Группа. В отношении R2 ключ ФИО.

Исследование отношений R1 и R2 показывает, что переход к 2НФ позволяет исключить явную избыточность данных. В R2 по-прежнему имеется неявное дублирование.

Для дальнейшего совершенствования отношения необходимо преобразовать его в 3НФ.

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

Определение 1. отношение находится в 3НФ, если оно находится в 2НФ и каждый неключевой атрибут нетранзитивно зависит от первичного ключа.

Существует и альтернативное определение.

Определение2. отношение находится в 3НФ в том и только в том случае, если все неключевые атрибуты отношения взаимно независимы и полностью зависят от первичного ключа.

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

Если в отношении R1 транзитивные зависимости отсутствуют, то в отношении R2 они есть:

ФИО → Должн → Оклад,

ФИО → Оклад → Должн,

ФИО → Стаж → Д_Стаж

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

R3

R4                                                                            R5

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

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

Усиленная 3НФ или нормальная форма Бойса-Кодда (БКНФ).

Отношение находится в БКНФ, если оно находится в 3НФ и в нем отсутствуют зависимости ключей от неключевых атрибутов.

В нашем примере подобной зависимости нет, поэтому процесс проектирования на этом заканчивается.

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

Рассмотрим пример нового отношения ПРОЕКТЫ, схема которого выглядит следующим образом: ПРОЕКТЫ (Номер_проекта, Код_сотрудника, Задание_сотрудника). Первичным ключом отношения является вся совокупность атрибутов: Номер_проекта, Код_сотрудника, Задание_сотрудника.

Пусть исходная информация в этом отношении выглядит следующим образом:

В отношении содержаться номера проектов, для каждого проекта – список кодов сотрудников-исполнителей, а также список заданий. Единственным возможным ключом отношения является составной атрибут Номер_проекта, Код_сотрудника, Задание_сотрудника. Он и стал первичным ключом отношения.

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

В отношении ПРОЕКТЫ существуют две многозначные зависимости:

Номер_проекта  Код_сотрудника

Номер_проекта  Задание_сотрудника

В произвольном отношении R(A, B, C) может одновременно существовать многозначная зависимость A  B, A  C. Это обстоятельство обозначим как A B | C.

Поясним проецирование без потерь на примере.

Пусть имеется некоторое отношение R(A, B, C), имеющее вид:

Построим проекции R1 и R2 на атрибуты А, В, С соответственно. Они будут выглядеть так:

R1                                                            R2

Результатом операции соединения бинарных отношений R1(A, B) и R2(A,C) по атрибуту А является тернарное отношение с атрибутами А, В и С, кортежи которого получаются путем связывания отношений R1 и R2 по типу 1:М на основе совпадения значений атрибута А.

Определение четвертой нормальной формы. Отношение R находится в четвертой нормальной форме (4НФ) в том и только том случае, когда существует многозначная зависимость А  В, а все остальные атрибуты R функционально зависят от А.

Приведенное отношение ПРОЕКТЫ можно представить в виде двух отношений: ПРОЕКТЫ-СОТРУДНИКИ и ПРОЕКТЫ-ЗАДАНИЯ. Структура этих отношений и содержимое соответствующих таблиц выглядит следующим образом:

ПРОЕКТЫ-СОТРУДНИКИ (Номер_проекта, Код_сотрудника).

Первичный ключ отношения: Номер_проекта, Код_сотрудникаэ

ПРОЕКТЫ-СОТРУДНИКИ

ПРОЕКТЫ-ЗАДАНИЯ (Номер_проекта, Задание_сотрудника).

Первичный ключ отношения: Номер_проекта, Задание_сотрудника.

ПРОЕКТЫ-ЗАДАНИЯ

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

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

Образуем составные атрибуты отношения СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ:

СО = {Код_сотрудника, Код_отдела}

СП = {Код_сотрудника, Номер_проекта}

ОП ={Код_отдела, Номер_проекта}.

Если отношение СОТРУДНИКИ-ОТДЕЛЫ-ПРОЕКТЫ спроецировать на составные атрибуты СО, СП, ОП, то соединение этих проекций дает исходное отношение.

Замечание

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

4.1.3. Обеспечение целостности

Под целостностью понимают свойство БД, означающее, что она содержит полную, непротиворечивую и адекватно отражающую предметную область информацию.

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

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

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

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

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

Формулировка требования целостности ссылок тесно связана с понятием внешнего ключа.

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

Вернутся в содержание.

Скачано с www.znanio.ru