Новости

К вопросу нормализации базы данных

Работа добавлена:






К вопросу нормализации базы данных на http://mirrorref.ru

К вопросу нормализации базы данных.

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

  • быстрый доступ к данным;
  • отсутствие дублирования данных;
  • целостность данных.

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

Функциональные зависимости.

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

Если даны два атрибута X и Y некоторого отношения, то говорят, что Y функционально зависит от X, если в любой момент времени каждому значению X соответствует ровно одно значение Y.

Функциональная зависимость обозначаетсяX -> Y.X иY могут представлять собой не только единичные атрибуты, но и группы, составленные из нескольких атрибутов одного отношения.

Различают частичную и полную функциональные зависимости. Под частичной функциональной зависимостью понимают зависимость не ключевого параметра от части составного ключа.

Под полной зависимостью понимают зависимость не ключевого параметра от всего составного ключа.

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

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

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

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

Процесс проектирования БД с использованием метода нормальных форм содержит последовательность шагов построения схем отношений, удовлетворяющих определенным правилам. Существует несколько нормальных форм:1NF,2NF,3NF,4NF,5NF, нормальная форма Бойса-Кодда. На практике обычно процесс нормализации завершается построением  3 нормальной формы.

1NF - первая нормальная форма.

Для обсуждения первой нормальной формы необходимо дать два определения:

Простой атрибут - атрибут, значения которого атомарны (неделимы).

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

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

Неделимость атрибута означает, что содержащиеся в нем значения, не должны делиться на более мелкие. Рассмотрим пример, заимствованный изстатьи Е.Ф.Кодда:

В базе данных отдела кадров предприятия необходимо хранить сведения о служащих, которые можно попытаться представить в отношении

СЛУЖАЩИЙ(НОМЕР_СЛУЖАЩЕГО, ИМЯ, ДАТА_РОЖДЕНИЯ, ИСТОРИЯ_РАБОТЫ, ДЕТИ).

Из внимательного рассмотрения этого отношения следует, что атрибуты"история_работы" и"дети" являются сложными, более того, атрибут"история_работы"  включает еще один сложный атрибут"история_зарплаты".Данные агрегаты выглядят следующим образом:

 

ИСТОРИЯ_РАБОТЫ (ДАТА_ПРИЕМА, НАЗВАНИЕ, ИСТОРИЯ_ЗАРПЛАТЫ),

ИСТОРИЯ_ЗАРПЛАТЫ (ДАТА_НАЗНАЧЕНИЯ, ЗАРПЛАТА),

ДЕТИ (ИМЯ_РЕБЕНКА, ГОД_РОЖДЕНИЯ).

Связь межу ними представлена графически на рис 1.

Рис1. Исходное отношение

Для приведения исходного отношения СЛУЖАЩИЙ к первой нормальной форме необходимо декомпозировать его на четыре отношения, так как это показано на следующем рисунке:

Рис2. Нормализованное множество отношений.

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

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

2NF - вторая нормальная форма.

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

Пример:

Поставщик может поставлять различные товары, а один и тот же товар может поставляться разными поставщиками. Тогда"N_поставщика + товар"является составным ключом отношения. Пусть все поставщики поставляют определенный товар по одной и той же цене. Тогда имеем следующие функциональные зависимости:

  • N_поставщика, товар ->цена
  • товар ->цена

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

  • ПОСТАВКИ (N_ПОСТАВЩИКА, ТОВАР)
  • ЦЕНА_ТОВАРА (ТОВАР, ЦЕНА)

3NF - третья нормальная форма.

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

Пусть X, Y, Z - три атрибута некоторого отношения. При этом X --> Y и Y --> Z, но обратное соответствие отсутствует, т.е. Z -/-> Y и Y -/-> X. Тогда Z транзитивно зависит от X.

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

Пусть имеется отношение ХРАНЕНИЕ (ФИРМА, СКЛАД, ОБЪЕМ), которое содержит информацию о фирмах, получающих товары со складов, и объемах этих складов. Ключевой атрибут -"фирма". Если каждая фирма может получать товар только с одного склада, то в данном отношении имеются следующие функциональные зависимости:

  • фирма ->склад
  • склад ->объем

При этом возникают аномалии:

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

Для устранения этих аномалий необходимо декомпозировать исходное отношение на два:

  • ХРАНЕНИЕ (ФИРМА, СКЛАД)
  • ОБЪЕМ_СКЛАДА (СКЛАД, ОБЪЕМ)

Рассмотрим задачу:

Файл содержит сведения о рейсах поездов на железнодорожном вокзале. Каждый компонент файла содержит сведения об одном рейсе.

struct FRejs{

   int KRejs; //кодрейса

   stringGp; //город прибытия (конец маршрута)

struct{    //сведения об остановках

string NamO; //наименование остановки

double DlO;  //длительность проезда до неё

int DlSt;    //длительность стоянки

   }Sost[50];      //для каждой из 50-ти остановок

struct{    //для каждой из 52-х недель текущего года

struct{ //для каждого из 7-ми дней этой недели

string  Vot; //время отправления

struct{      //для каждого из 10 вагонов

stringTvg;   //тип вагона (плацкарт, купе, мягкий)

struct{       //для каждого из 35-ти мест вагона

string FIO; //ФИО пассажира

int  Npv;   //порядковый номер остановки - пункта высадки

  }Zvg[35]; //заполненность вагона

         }Svg[10];    //список вагонов

     }Spb[7];   //сведения о проданных билетах

   }DSv[52];    //детальные сведения

stringFIOnp; //ФИО начальника поезда

stringFIOpvg[10]; //список проводников для каждого из 10 вагонов

}

Необходимо провести заданное отношение к 3-ей нормальной форме.

***************************************************************************

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

struct FRejs{

   int KRejs; //кодрейса

   stringGp; //город прибытия (конец маршрута)

struct{    //сведения об остановках

string NamO; //наименование остановки

double DlO;  //длительность проезда до неё

int DlSt;    //длительность стоянки

   }Sost[50];      //для каждой из 50-ти остановок

struct{    //для каждой из 52-х недель текущего года

struct{ //для каждого из 7-ми дней этой недели

string  Vot; //время отправления

stringFIOnp;//ФИО начальника поезда

struct{      //для каждого из 10 вагонов

stringTvg;   //тип вагона (плацкарт, купе, мягкий)

stringFIOpvg;//ФИО проводника вагона

struct{       //для каждого из 35-ти мест вагона

string FIO; //ФИО пассажира

int  Npv;   //порядковый номер остановки - пункта высадки

  }Zvg[35]; //заполненность вагона

         }Svg[10];    //список вагонов

     }Spb[7];   //сведения о проданных билетах

   }DSv[52];    //детальные сведения

}

Рассмотрим один из вариантов декомпозиции данной структуры в множество таблиц реляционной базы данных

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

Номер рейса:

 Пункт назначения

 Сведения об остановках:

    1-я остановка:

        Наименование остановки

        Тип остановки

        Длительность проезда

        Длительность стоянки

    2-я остановка:

        Наименование остановки

        Тип остановки

        Длительность проезда

        Длительность стоянки

    . . . . . . . . . . . . .

    50-я остановка:

        Наименование остановки

        Тип остановки

        Длительность проезда

        Длительность стоянки

 Номер недели:

    1-я неделя:

        Номер дня недели:

             1 день:

                Время отправления

                Фамилия начальника поезда

                Список вагонов:

                   1-ый вагон:

                       Тип вагона

                       Количество мест в вагоне

                       ФИО проводника

                       Количество проданных билетов

                       Места:

                          первое место:

                             ФИО пассажира

                             Порядковый номер остановки

                          второе место:

                             ФИО пассажира

                             Порядковый номер остановки

                          . . . . . . . . . . . . . . .

                          35-е место —При отсутствии места в вагоне данного типа информация по месту не заполняется:

                             ФИО пассажира

                             Порядковый номер остановки

                    . . . . . . . . . . . . . .

                   10-ый вагон:

                       Тип вагона

                       ФИО проводника

                       Количество проданных билетов

                       Места:

                          первое место:

                             ФИО пассажира

                             Порядковый номер остановки

                          второе место:

                             ФИО пассажира

                             Порядковый номер остановки

                          . . . . . . . . . . . . . . .

                          35-е место:

                             ФИО пассажира

                             Порядковый номер остановки

             . . . . . . . . . .

             7 день:

                Время отправления

                Список вагонов:

                   1-ый вагон:

                       Тип вагона

                       ФИО  проводника

                       Количество проданных билетов

                       Места:

                          первое место:

                             ФИО пассажира

                             Порядковый номер остановки

                          второе место:

                             ФИО пассажира

                             Порядковый номер остановки

                          . . . . . . . . . . . . . . .

                          35-е место:

                             ФИО пассажира

                             Порядковый номер остановки

                    . . . . . . . . . . . . . .

                   10-ый вагон:

                       Тип вагона

                       ФИО  проводника

                       Количество проданных билетов

                       Места:

                          первое место:

                             ФИО пассажира

                             Порядковый номер остановки

                          второе место:

                             ФИО пассажира

                             Порядковый номер остановки

                          . . . . . . . . . . . . . . .

                          35-е место:

                             ФИО пассажира

                             Порядковый номер остановки

             . . . . . . . . . . . . . . .

    52-я неделя:

           День недели:

             1 день:

                Время отправления

                Список вагонов:

                   1-ый вагон:

                       Тип вагона

                       ФИО  проводника

                       Количество проданных билетов

                       Места:

                          первое место:

                             ФИО пассажира

                             Порядковый номер остановки

                          второе место:

                             ФИО пассажира

                             Порядковый номер остановки

                          . . . . . . . . . . . . . . .

                          35-е место:

                             ФИО пассажира

                             Порядковый номер остановки

                    . . . . . . . . . . . . . .

                   10-ый вагон:

                       Тип вагона

                       ФИО  проводника

                       Количество проданных билетов

                       Места:

                          первое место:

                             ФИО пассажира

                             Порядковый номер остановки

                          второе место:

                             ФИО пассажира

                             Порядковый номер остановки

                          . . . . . . . . . . . . . . .

                          35-е место:

                             ФИО пассажира

                             Порядковый номер остановки

             . . . . . . . . . .

             7 день:

                Время отправления

                Список вагонов:

                   1-ый вагон:

                       Тип вагона

                       ФИО  проводника

          &