Новости

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

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






К вопросу нормализации базы данных на 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-ый вагон:

                       Тип вагона

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

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

                       Места:

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

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

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

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

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

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

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

                          35-е место:

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

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

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

                   10-ый вагон:

                       Тип вагона

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

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

                       Места:

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

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

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

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

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

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

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

                          35-е место:

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

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

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

(код рейса, номер остановки) -> наименование остановки;

(код рейса, номер остановки) -> тип остановки;

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

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

(код рейса, номер остановки) -> код остановки;

код остановки -> наименование остановки

код остановки -> тип остановки;

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

Рассмотрим приведение таблицы к 1NF.

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

Шаг 1:

ТаблицаRejs:

intKodRejs; //Номеррейса

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

ТаблицаOst:

intKodRejs; //Номеррейса

intNomOst;  //Номеростановки

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

stringTypO;//тип населенногопункта-остановки //(областной центр, райцентр, город, поселок //городского типа, поселок, станица, станция)

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

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

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

Таблица DetSved:

intKodRejs; //Номеррейса

intNomNed;  //Номернедели

intNomDen;  //номер дня недели

structSрb;  //сведения о проданных билетах  -      структура

Детальные сведения относятся к определенному дню года, каждый день кодируется сложным атрибутом (NomNed,NomDen). Поскольку таблицаDetSved содержит сложный атрибут(Sрb), выделим подчиненное отношение, спускаясь на один уровень вниз описанного выше дерева:

Шаг 2:

Таблица DetSved:

intKodRejs; //Номер рейса

intNomNed;  //Номер недели

intNomDen;  //номер дня недели

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

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

struct Svg;   //список вагонов - структура

Шаг 3:

Продолжая декомпозицию таблицыDetSved, получим:

Таблица DetSved:

intKodRejs; //Номер рейса

intNomNed;  //Номер недели

intNomDen;  //номер дня недели

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

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

Таблица Svg (список вагонов):

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;//номер дня недели

intNomVagon;//номер вагона

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

intKolMest;    //Количество мест

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

structZvg; //заполненность вагона - структура

Вводится атрибут номер вагона (NomVagon)

Шаг 4:

Поскольку таблицаSved_Vagon содержит сложные атрибуты, выделим подчиненное отношение, спускаясь на один уровень описанного выше дерева:

Таблица Svg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;//номер дня недели

intNomVagon;//номер вагона

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

intKolMest;    //Количество мест

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

ТаблицаZvg (Заполненность вагона):

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;//номер дня недели

intNomVagon; //номер вагона

intNomMest;  //номерместа

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

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

Вводится атрибут номер места в вагоне (NomMest)

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

ТаблицаRejs:

intKodRejs; //Номеррейса

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

ТаблицаOst:

intKodRejs; //Номеррейса

intNomOst;  //Номеростановки

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

stringTypO;//тип населенногопункта-остановки //(областной центр, райцентр, город, поселок //городского типа, поселок, станица, станция)

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

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

Таблица DetSved:

intKodRejs; //Номер рейса

intNomNed;  //Номер недели

intNomDen;  //номер дня недели

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

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

Таблица Svg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;//номер дня недели

intNomVagon;//номер вагона

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

intKolMest;    //Количество мест

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

ТаблицаZvg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;   //номер дня недели

intNomVagon; //номер вагона

intNomMest;  //номерместа

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

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

Сведения об остановках встречаются в нескольких таблицах. Поскольку список остановок меняется от рейса к рейсу, то вполне резонно выделить этот список в отдельную таблицу. Введем в таблицеOst не ключевое полеintKodO {код остановки} (Заметим, чтоKodO не совпадает с атрибутомNomOst, поскольку атрибутKodO идентифицирует остановку, а атрибутNomOst является порядковым номером остановки для данного рейса);

ТаблицаOst:

intKodRejs; //Номеррейса

intNomOst;  //Номеростановки

intKodO; //код остановки

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

stringTypO;//тип населенногопункта-остановки //(областной центр, райцентр, город, поселок //городского типа, поселок, станица, станция)

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

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

Изучая функциональные зависимости в таблицеOst, получим следующие функциональные зависимости:

(KodRejs,NomOst)KodOst

(KodRejs,NomOst)NamO

(KodRejs,NomOst)TypO

KodOstNamO (неключевой атрибут функционально зависит от неключевого)

KodOstTypO

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

ТаблицаOst:

intKodRejs; //Номеррейса

intNomOst;  //Номеростановки

intKodO; //код остановки

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

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

ТаблицаSpOst:

intKodO;   //код остановки

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

stringTypO;//тип населенногопункта-остановки //(областной центр, райцентр, город, поселок //городского типа, поселок, станица, станция)

Можно также предположить, что проводники из рейса выбираются из некоторого общего списка. Добавим в таблицуSvgатрибутintKodProv {Код проводника}.

Таблица Svg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;//номер дня недели

intNomVagon;//номер вагона

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

intKolMest;    //Количество мест

int KodProv;    //Кодпроводника

string FioProv; //ФИОпроводника

Для данного отношения имеем следующие функциональные связи:

(KodRejs,NomNed,NomDen,NomVagon) KodProv

(KodRejs,NomNed,NomDen,NomVagon) FioProv

KodProvFioProv

Такжезаметим,что

(KodRejs,NomNed,NomDen,NomVagon) Tvg

(KodRejs,NomNed,NomDen,NomVagon) KolMest

TvgKolMest

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

Таблица Svg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;   //номер дня недели

intNomVagon; //номер вагона

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

intKodProv;    //Код проводника

ТаблицаProv:

intKodProv;    //Кодпроводника

string FioProv; //ФИОпроводника

ТаблицаTVag:

stringTvg; //тип вагона

intKolMest;    //Количество мест

В результате декомпозиции поучается следующая схема БД:

Этот вариант декомпозиции использовал правила приведения к 1 и 3 нФ, поскольку результаты полученные при приведении к 1 НФ, автоматически имели форму 2НФ.

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

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;   //номер дня недели

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

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

intNomVagon; //номервагона

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

intKolMest;  //Количество мест

intKodProv;  //Код проводника

intNomMest;  //номер места

stringFIO;   //ФИО пассажира

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

string FioProv; //Фамилия проводника

Ключевые поля (KodRejs,NomNed,NomDen,NomVagon,NomMest) в таблице подчеркнуты. Для данного отношения имеем следующие функциональные отношения:

(KodRejs,NomNed,NomDen) (Vot,FIOnp)

(KodRejs,NomNed,NomDen, NomVagon) (Tvg,KodProv,FioProv)

(KodRejs,NomNed,NomDen, NomVagon, NomMest) (Fio,Npv)

KodProvFioProv

Tvg -> KolMest

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

Таблица DetSved:

intKodRejs; //Номер рейса

intNomNed;  //Номер недели

intNomDen;  //номер дня недели

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

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

Таблица Svg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;   //номер дня недели

intNomVagon; //номер вагона

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

intKolMest;    //Количество мест

intKodProv;    //Код проводника

string FioProv; //ФИО проводника

ТаблицаZvg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;   //номер дня недели

intNomVagon; //номер вагона

intNomMest;  //номерместа

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

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

Учитывая транзитивность,

(KodRejs,NomNed,NomDen,NomVagon) (Tvg,KodProv,FioProv)

KodProvFioProv

а также

TvgKolMest

При приведении к 3NF, таблицаSvgразобьется на три таблицы:

Таблица Svg:

intKodRejs;  //Номер рейса

intNomNed;   //Номер недели

intNomDen;   //номер дня недели

intNomVagon; //номер вагона

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

intKodProv;    //Код проводника

ТаблицаProv:

intKodProv;    //Кодпроводника

string FioProv; //ФИОпроводника

ТаблицаTVag:

stringTvg; //тип вагона

intKolMest;    //Количество мест

В целом получается такое же разбиение, но при этом используется правила приведения к 1NF ,2NF и 3NF.

Задача: Вывести ФИО проводников, которые работали на всех рейсах своего маршрута.

SELECT DISTINCT MAX(FioProv) FROM Prov, Svg

WHERE Prov.KodProv = Svg.KodProv

GROUP BY Svg.KodProv, Svg.KodRejs

HAVING COUNT(*) = (SELECT COUNT(*) FROM DetSved

               WHERE Svg.KodRejs = DetSved.KodRejs)

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

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


Похожие рефераты, которые будут Вам интерестны.

1. Табличные базы данных (БД): основные понятия (поле, запись, первичный ключ записи); типы данных. Системы управления базами данных и принципы работы с ними. Поиск, удаление и сортировка данных в БД

2. Терапевтическая биотехническая система нормализации нормализации состояния биологического объекта

3. Создание реляционной базы данных и формы ввода данных

4. Изучение информационной технологии создания базы данных в системе управления базами данных (СУБД) MS Access

5. К ВОПРОСУ ОБ ЭФФЕКТИВНОСТИ ПРИМЕНЕНИЯ БАЗ ДАННЫХ В ТУРИЗМЕ

6. Понятие базы данных

7. Базы данных шпаргалка

8. Создание базы данных на языке C

9. Создание базы данных в MS Excel

10. Создание таблиц базы данных