Проектирование БД методом нормальных форм Формирование исходного

Проектирование БД методом нормальных форм

Формирование исходного отношения • Проектирование базы данных начинается с определения всех объектов, сведения о которых включены в базу, и определения их атрибутов. • На первом этапе атрибуты сводятся в одну таблицу – исходное отношение. • Формирование исходного отношения рассмотрим на примере.

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

• ФИО – фамилия и инициалы преподавателя. Для простоты предположим, что повторений нет. • Должн – должность, занимаемая преподавателем. • Оклад – оклад преподавателя. • Стаж – преподавательский стаж. • Д_Стаж – надбавка за стаж. • Кафедра – название кафедры, где числится преподаватель. • Предмет – название предмета, читаемого преподавателем. • Группа – номер группы, в которой преподаватель проводит занятия. • Вид. Зан – вид занятий, проводимых преподавателем в учебной группе. В исходном отношении каждый атрибут кортежа должен быть простым (атомарным). Пример исходного отношения Преподаватель приведён в таблице 1.

Исходное отношение Преподаватель ФИ О До лж н О кл ад С та ж Д_ Ст аж Ка фе дра Пр ед мет Гр уп па Вид Зан Ива нов А. асс ист ент 10 00 5 100 ИВ Т ПО ЭВ М 256 Пра кт Ива нов А. асс ист ент 10 00 5 100 ИВ Т Пр оло г 123 Пра кт Пет ров М. доц ент 14 00 7 100 ИВ Т ПО ЭВ М 256 Лек ция Пет ров М. доц ент 14 00 7 100 ИВ Т Пас кал ь 256 Пра кт Сид оро в. Н. асс ист ент 10 00 10 150 ИВ Т Пр оло г 123 Лек ция Сид оро в. Н. асс ист ент 10 00 10 150 ИВ Т Пас кал ь 256 Лек ция

• Данное отношение имеет следующую схему: Преподаватель (ФИО, Должн, Оклад, Стаж, Д_Стаж, Кафедра, Предмет, Группа, Вид. Зан). • Исходное отношение содержит избыточное дублирование данных, которое является (по терминологии Э. Кодда) аномалией редактирования. Различают избыточность явную и неявную.

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

• Неявная избыточность проявляется в одинаковых окладах у всех ассистентов, например, и в одинаковых надбавках за одинаковый стаж. • Поэтому, если при изменении оклада за должность с 1000 до 1200 это значение изменят у всех, кроме Сидорова, база данных станет противоречивой. Это пример аномалии редактирования для варианта с неявной избыточностью.

• Средством исключения избыточности в отношениях и аномалий редактирования является нормализация отношений.

• Проектирование БД – один из этапов жизненного цикла информационной системы. Основная задача, которая решается в процессе проектирования БД, это задача нормализации её отношений. • Рассматриваемый нами метод нормальных форм является классическим методом проектирования реляционных БД. • Этот метод основан на фундаментальном в теории БД понятии зависимости между атрибутами.

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

• • ФИО Должн ФИО Оклад ФИО Стаж ФИО Д_Стаж ФИО Кафедра Стаж Д_Стаж Должн Оклад ФИО, Предм, Группа Вид. Зан

• Фамилия, имя и отчество у преподавателей факультета уникальны. Каждому преподавателю однозначно соответствует его стаж, т. е. имеет место функциональная зависимость ФИО Стаж. Обратное утверждение неверно, так как одинаковый стаж может быть у разных преподавателей. • Каждый преподаватель имеет определённую надбавку за стаж, т. е. имеет место функциональная зависимость ФИО Д_Стаж, но обратная функциональная зависимость отсутствует, так как одну и ту же надбавку могут иметь несколько преподавателей. • Каждый преподаватель имеет определённую должность (ассистент, доцент), но одну и ту же должность могут иметь несколько преподавателей, т. е. имеет место функциональная зависимость ФИО Должн, а обратная функциональная зависимость отсутствует.

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

• Один и тот же преподаватель в одной группе по разным предметам может проводить разные виды занятий. Определение вида занятий, которые проводит преподаватель, невозможно без указания предмета и группы, поэтому имеет место функциональная зависимость ФИО, Предм, Класс Вид. Зан. Действительно, Петров М. в 256 группе читает лекции и проводит практические занятия. Но лекция он читает по ПО ЭВМ, а практику проводит по Паскалю.

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

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

Выделяют следующую последовательность нормальных форм: • • Первая нормальная форма (1 НФ); Вторая нормальная форма (2 НФ); Третья нормальная форма (3 НФ); Усиленная третья нормальная форма, или нормальная форма Бойса-Кодда (БКНФ); • Четвёртая нормальная форма (4 НФ); • Пятая нормальная форма (5 НФ).

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

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

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

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

Частичная зависимость от ключа приводит к следующему: 1. В отношении присутствует явное и неявное избыточное дублирование данных, например: • Повторение сведений о стаже, должности и окладе преподавателей, проводящих занятия в нескольких группах и/или по разным предметам; • Повторение сведений об окладах для одной и той же должности или о надбавках за одинаковый стаж. 2. Следствием избыточного дублирования данных является проблема их редактирования. Например, изменение должности у преподавателя Иванова А. потребует просмотра всех кортежей отношения и внесения изменений в те из них, которые содержат сведения о данном преподавателе. Часть избыточности устраняется при переводе отношения в 2 НФ.

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

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

В результате получили два отношения R 1 и R 2 в 2 НФ: R 1 ФИО Предмет Группа Вид. Зан Иванов. А. ПО ЭВМ 256 Практ Иванов. А. Пролог 123 Практ Петров. М. ПО ЭВМ 256 Лекция Петров. М. Паскаль 256 Практ Сидоров. Н. Пролог 123 Лекция Сидоров. Н. Паскаль 256 Лекция Егоров. В. Информ. 244 Лекция

R 2 ФИ О Дол жн Ок С Д_ лад та Ст ж аж Ка фе дра Иван асси 100 5 ов. А. стен 0 т 100 ИВ Т Петр доц ов. М. ент 100 ИВ Т 140 7 0 Сидо асси 100 10 150 ИВ ров. Н. стен 0 Т т Егор ов. В. асси 100 5 стен 0 т 100 Фи зик и

• В отношении R 1 первичный ключ является составным и состоит из атрибутов ФИО, Предмет, Группа. В отношении R 2 ключ ФИО. • Переход ко второй нормальной форме позволил исключить явную избыточность данных в таблице R 2, однако по-прежнему имеет место неявная избыточность. Устранить неявную избыточность данных можно переводом отношения в третью нормальную форму.

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

• В отношении R 1 транзитивные зависимости отсутствуют, в отношении R 2 они есть: ФИО Должн Оклад, ФИО Стаж Д_Стаж.

• Транзитивные зависимости также порождают избыточное дублирование информации в отношении. • Для устранения избыточности используем операцию проекции на атрибуты, которые являются причиной транзитивных зависимостей. • Преобразуем отношение R 2, получив при этом отношения R 3, R 4 и R 5. Эти отношения находятся в третьей нормальной форме.

R 3 ФИО Должн Стаж Кафедра Иванов. А. ассистент 5 ИВТ Петров. М. доцент 7 ИВТ Сидоров. Н. ассистент 10 ИВТ Егоров. В. ассистент 5 Физики

R 4 Дол жн Окл ад асси 1000 стент доце нт 1400

R 5 Ста ж Д_С таж 5 100 7 100 10 150

• На практике в большинстве случаев приведение отношений к третьей нормальной форме является достаточным, и процесс проектирования базы данных на этом заканчивается. • Однако, если в отношениях присутствует зависимость атрибутов составного ключа от неключевых атрибутов, необходимо перейти к усиленной третьей нормальной форме, или нормальной форме Бойса – Кодда (БКНФ).

Нормальная форма Бойса – Кодда • Отношение находится в БКНФ, если оно находится в 3 НФ и в нём отсутствуют зависимости ключей (атрибутов составного ключа) от неключевых атрибутов.

• В рассматриваемом примере подобной зависимости нет, поэтому процесс проектирования на этом заканчивается. • Результатом проектирования является база данных, состоящая из таблиц R 1, R 3, R 4, R 5. • В полученной БД имеется необходимое дублирование данных, но отсутствует избыточное.

Метод «сущность-связь» (ERдиаграмм) • Кроме метода нормальных форм Кодда, который используется при проектировании небольших БД, применяют и другие методы, например, метод ER-диаграмм (метод сущность – связь). • Этот метод используется при проектировании больших БД, на нём основан ряд CASE – систем.

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

• На последнем этапе метода ERдиаграмм отношения, полученные в результате проектирования, проверяются на принадлежность их к нормальной форме Бойса – Кодда. • После завершения проектирования БД создаётся с помощью СУБД или системы программирования.

• На практике не всегда можно получить идеально нормализованную БД. Часто к этому и не стремятся.

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

• Однако у нормализованной БД есть недостатки практического характера. • Базы данных в составе больших информационных систем, например АСУ крупных предприятий, могут содержать сотни связанных между собой таблиц. • Человек не в состоянии одновременно анализировать большое число объектов с учётом их взаимосвязей; можно утверждать, что с увеличением числа нормализованных таблиц уменьшается целостное восприятие базы данных как системы взаимосвязанных данных. • Известны случаи, когда создавались системы, принципы функционирования которых признавались вышедшими за границы понимания.

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