Тема №18

18 ПРОЕКТИРОВАНИЕ БАЗ ДАННЫХ

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

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

· целостность базы данных — требование полноты и непротиворечивости данных;

· многократное использование данных;

· быстрый поиск и получение информации по запросам пользователей;

· простота обновления данных;

· минимизация избыточности данных;

· защита данных от несанкционированного доступа, искажения и уничтожения.

18.1 Этапы жизненного цикла базы данных

Жизненный цикл базы данных (ЖЦБД) — это процесс проектирования, реализации и поддержки базы данных, состоящий из семи этапов:

1) этап предварительного планирования;

2) этап проверки осуществимости;

3) этап определения требований;

4) этап концептуального проектирования;

5) этап логического проектирования;

6) этап физического проектирования;

7) этап оценки работы и поддержки базы данных.

Опишем главные задачи каждого этапа.

1. Предварительное планирование базы данных — важный этап в процессе перехода от разрозненных данных к интегрированным. На этом этапе собирается информация об используемых и находящихся в процессе разработки прикладных программах и файлах, связанных с ними. Она помогает установить связи между текущими приложениями. Кроме того, позволяет определить будущие требования к базе данных. Информация документируется в виде обобщенной концептуальной модели данных.

2. Проверка осуществимости предполагает подготовку ответов по трем вопросам:

· есть ли технология — необходимое оборудование и программное обеспечение для реализации запланированной базы данных (технологическая осуществимость);

· имеются ли персонал, средства и эксперты для успешного осуществления плана создания базы данных (операционная осуществимость);

· окупится ли запланированная база данных (экономическая эффективность).

3. Определение требований. На этом этапе формулируются:

• цели базы данных;

• информационные потребности различных структурных подразделений и их руководителей;

• требования к оборудованию;

• требования к программному обеспечению.

4. Концептуальное проектирование. На этом этапе создаются подробные модели пользовательских представлений данных предметной области. Затем они интегрируются в концептуальную модель, которая фиксирует все элементы корпоративных данных, подлежащих загрузке в базу данных. Эту модель еще называют концептуальной схемой базы данных.

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

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

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

18.2 Этапы проектирования базы данных и их процедуры

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

1) концептуальное проектирование;

2) логическое проектирование;

3) физическое проектирование.

18.2.1 Процедуры концептуального проектирования

Цель этапа концептуального проектирования — создание концептуальной модели данных исходя из представлений пользователей о предметной области. Для ее достижения выполняется ряд последовательных процедур.

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

2.Определение связей между сущностями и их документирование. Определяются только те связи между сущностями, которые необходимы для удовлетворения требований к проекту базы данных. Устанавливается тип каждой из них. Выявляется класс принадлежности сущностей. Связям присваиваются осмысленные имена, выраженные глаголами. Развернутое описание каждой связи с указанием ее типа и класса принадлежности сущностей, участвующих в связи, заносится в словарь данных.

3.Создание ER-модели предметной области. Для представления сущностей и связей между ними используются ER-диаграммы. На их основе создается единый наглядный образ моделируемой предметной области — ER-модель предметной области.

4.Определение атрибутов и их документирование. Выявляются все атрибуты, описывающие сущности созданной ER-модели. Каждому атрибуту присваивается осмысленное имя, понятное пользователям. О каждом атрибуте в словарь данных помещаются следующие сведения:

• имя атрибута и его описание;

• тип и размерность значений;

• значение, принимаемое для атрибута по умолчанию (если такое имеется);

• может ли атрибут иметь Null-значения;

• является ли атрибут составным, и если это так, то из каких простых атрибутов он состоит. Например, атрибут «Ф.И.О. клиента» может состоять из простых атрибутов «Фамилия», «Имя», «Отчество», а может быть простым, содержащим единые значения, например, «Сидорский Евгений Михайлович». Если пользователь не нуждается в доступе к отдельным элементам «Ф.И.О.», то атрибут представляется как простой;

• является ли атрибут расчетным, и если это так, то как вычисляются его значения.

5.Определение значений атрибутов и их документирование. Для каждого атрибута сущности, участвующей в ER-модели, определяется набор допустимых значений, которому присваивается имя. Обновляются записи словаря данных, относящиеся к атрибутам, — в них заносятся имена наборов значений атрибутов.

6.Определение первичных ключей для сущностей и их документирование. На этом шаге руководствуются определением первичного ключа — как атрибута или набора атрибутов сущности, позволяющего уникальным образом идентифицировать ее экземпляры. Сведения о первичных ключах помещаются в словарь данных.

7. Обсуждение концептуальной модели данных с конечными пользователями. Концептуальная модель данных представляется ER-моделью с сопроводительной документацией, содержащей описание разработанной модели данных. Если будут обнаружены несоответствия предметной области, то в модель вносятся изменения до тех пор, пока пользователи не подтвердят, что предложенная им модель адекватно отображает их личные представления.

18.2.2 Процедуры логического проектирования

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

1. Выбор модели данных. Чаще всего выбирается реляционная модель данных, которая наиболее распространена благодаря наглядности табличного представления данных и удобству работы с ними.

2. Определение набора таблиц исходя из ER-модели и их документирование. Для каждой сущности ER-модели создается таблица. Имя сущности — имя таблицы. Осуществляется формирование структуры таблиц. Устанавливаются связи между таблицами посредством механизма первичных и внешних ключей. Структуры таблиц и установленные связи между ними документируются.

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

4. Проверка логической модели данных на предмет возможности выполнения транзакций.

Транзакция — это набор действий, выполняемых отдельным пользователем или прикладной программой с целью изменения содержимого базы данных так, чтобы она осталась в непротиворечивом состоянии. Так, примером транзакции в проекте БАНК может быть передача права распоряжаться счетами некоторого клиента другому клиенту. В этом случае в базу данных потребуется внести сразу несколько изменений. Если во время выполнения транзакции произойдет сбой в работе компьютера, то база данных окажется в противоречивом состоянии, так как некоторые изменения уже будут внесены, а остальные еще нет. Поэтому все частичные изменения должны быть отменены для возвращения базы данных в прежнее непротиворечивое состояние.

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

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

• обязательные данные. Выясняется, есть ли атрибуты, которые не могут иметь Null-значений;

• ограничения для значений атрибутов. Определяются допустимые значения для атрибутов;

• целостность сущностей. Она достигается, если первичный ключ сущности не содержит Null-значений;

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

• ограничения, накладываемые бизнес-правилами. Бизнес-правила — это установки, правила и технологические приемы, принятые в предметной области. Например, в случае с проектом БАНК может быть принято правило, запрещающее клиенту распоряжаться, скажем, более чем тремя счетами.

Сведения обо всех установленных ограничениях целостности данных помещаются в словарь данных.

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

18.2.3 Процедуры физического проектирования

Цель этапа физического проектирования — описание конкретной реализации базы данных, размещаемой во внешней памяти компьютера. Это описание структуры хранения данных и эффективных методов доступа к данным базы. При логическом проектировании отвечают на вопрос, что надо сделать, а при физическом выбирается способ, как это сделать. Рассмотрим процедуры физического проектирования.

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

2. Реализация бизнес-правил в среде выбранной СУБД. Обновление информации в таблицах может быть ограничено бизнес-правилами. Способ их реализации зависит от выбранной СУБД. Одни системы для реализации требований предметной области предлагают больше возможностей, другие — меньше. В некоторых системах вообще отсутствует поддержка реализации бизнес-правил. В таком случае разрабатываются приложения для реализации их ограничений.

Все решения, принятые в связи с реализацией бизнес-правил предметной области, подробно описываются в сопроводительной документации.

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

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

4. Разработка стратегии защиты базы данных. База данных представляет собой ценный корпоративный ресурс, и организации ее защиты уделяется большое внимание. Для этого проектировщики должны иметь полное и ясное представлениеобо всех средствах защиты, предоставляемых выбранной СУБД.

5. Организация мониторинга функционирования базы данных и ее настройка. После создания физического проекта базы данных организуется непрерывное слежение за ее функционированием. Полученные сведения об уровне производительности базы данных используются для ее настройки. Для этого привлекаются и средства выбранной СУБД.

18.3 Модель «сущность – связь»

Средством моделирования предметной области на этапе концептуального проектирования является модель «сущность — связь», которую часто называют ER-моделью (ventity — сущность, relation — связь). Моделирование структуры данных предметной области в ней базируется на использовании графических средств — ER-диаграмм (диаграмм «сущность—связь»). В наглядном виде они представляют связи между сущностями. Основные понятия ER-диаграммы: сущность, атрибут, связь.

Сущность — это некоторый объект реального мира, который может существовать независимо. Сущность имеет экземпляры, отличающиеся друг от друга значениями атрибутов и допускающие однозначную идентификацию. Атрибут — это поименованная характеристика сущности. Например, сущность КНИГА характеризуется такими атрибутами, как автор, наименование, цена, издательство, тираж, количество страниц. Конкретные книги являются экземплярами сущности КНИГА. Они отличаются значениями указанных атрибутов и однозначно идентифицируются атрибутом «наименование». Атрибут, который уникальным образом идентифицирует экземпляры сущности, называется ключом. Ключ может быть составным, т.е. представляющим комбинацию нескольких атрибутов.

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

На ER-диаграмме сущность изображается прямоугольником, в котором указывается ее имя. Например,

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

В рассматриваемой предметной области БАНК можно выделить три связи:

1. МЕНЕДЖЕР — УПРАВЛЯЕТ — ФИЛИАЛ

2. ФИЛИАЛ — ОБРАБАТЫВАЕТ — СЧЕТ

3. КЛИЕНТ — ИМЕЕТ — СЧЕТ

На ER-диаграмме связь изображается ромбом. Например,

Важной характеристикой связи является тип связи (кардинальность). Рассмотрим типы вышеуказанных связей 1—3.

Так как менеджер управляет только одним филиалом, то каждый экземпляр сущности МЕНЕДЖЕР может быть связан не более чем с одним экземпляром сущности ФИЛИАЛ. В этом случае связь 1 имеет тип «один-к-одному» (1:1). На рисунке 18.1 представлена ER-диаграмма для связи типа 1:1.

Рисунок 18.1 – ER-диаграмма связи типа 1:1

Так как филиал обрабатывает несколько счетов, а счет обрабатывается только одним филиалом, то каждый экземпляр сущности ФИЛИАЛ может быть связан более чем с одним экземпляром сущности СЧЕТ, а каждый экземпляр сущности СЧЕТ — не более чем с одним экземпляром сущности ФИЛИАЛ. В этом случае связь 2 имеет тип «один-ко-многим» (1 :М). На рисунке 18.2 представлена ER-диаграмма для связи типа 1:М.

Рисунок 18.2 – ER-диаграмма связи типа 1:М

Так как счет может совместно использоваться несколькими клиентами и клиент может иметь несколько счетов, то каждый экземпляр сущности СЧЕТ может быть связан с несколькими экземплярами сущности КЛИЕНТ, а каждый экземпляр сущности КЛИЕНТ — с несколькими экземплярами сущности СЧЕТ. В этом случае связь 3 имеет тип «многие-ко-многим» (M: N). На рисунке 18.3 представлена ER-диаграмма для связи типа M: N.

Рисунок 18.3 – ER-диаграмма связи типа M: N

Рассмотрим понятие «класс принадлежности сущности». Если каждый экземпляр сущности А связан с экземпляром сущности В, то класс принадлежности сущности А является обязательным. Этот факт отмечается на ER-диаграмме черным кружком, помещенным в прямоугольник, смежный с прямоугольником сущности А.

Если не каждый экземпляр сущности А связан с экземпляром сущности В, то класс принадлежности сущности А является необязательным. Этот факт отмечается на ER-диаграмме черным кружком, помещенным на линии связи возле прямоугольника сущности А.

В качестве примера на рисунке 18.4 изображены возможные ER-диаграммы для связи M: N с учетом класса принадлежности сущности.

Рисунок 18.4 – ER-диаграммы связи M: N с учетом класса

принадлежности сущности

На ER-диаграмме 1 класс принадлежности обеих сущностей необязательный. На ER-диаграмме 2 класс принадлежности сущности КЛИЕНТ обязательный, а сущности СЧЕТ — необязательный. На ER-диаграмме З класс принадлежности сущности КЛИЕНТ необязательный, а сущности СЧЕТ — обязательный. На ER-диаграмме 4 класс принадлежности обеих сущностей обязательный.

Предположим, что в рассматриваемой предметной области БАНК класс принадлежности всех четырех сущностей является обязательным. Тогда ER-модель предметной области БАНК будет иметь вид, представленный на рисунке 18.5.

Рисунок 18.5 – Пример ER-модели предметной области БАНК

Каждая из четырех сущностей приведенной ER-модели может быть описана своим набором атрибутов (рисунок 18.6).

Рисунок 18.6 – Наборы атрибутов сущностей предметной области БАНК

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

18.4 Преобразование ER-модели в реляционную

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

Правила генерации таблиц из ER-диаграмм опираются на два основных фактора — тип связи и класс принадлежности сущности.

Правило 1

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

На ER-диаграмме связи 1:1, представленной на рисунке 18.5, класс принадлежности сущностей МЕНЕДЖЕР, ФИЛИАЛ является обязательным. Тогда согласно правилу 1 должна быть сгенерирована одна таблица следующей структуры:

МЕНЕДЖЕР —ФИЛИАЛ

НМ | СТАЖ | СПЕЦ | НФ | АДР_Ф

Первичным ключом этой таблицы может быть и первичный ключ сущности МЕНЕДЖЕР — НМ.

Правило 2

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

Представим, что на ER-диаграмме связи 1:1, изображенной на рисунке 18.5, класс принадлежности сущности МЕНЕДЖЕР будет обязательный, а сущности ФИЛИАЛ — необязательный. Тогда согласно правилу 2 должны быть сгенерированы две таблицы следующей структуры:

МЕНЕДЖЕР — ФИЛИАЛ

НМ | СТАЖ | СПЕЦ | НФ

ФИЛИАЛ

НФ | АДР_Ф

Сущность с необязательным классом принадлежности (ФИЛИАЛ) именуется родительской, а с обязательным (МЕНЕДЖЕР) — дочерней. Первичный ключ родительской сущности (НФ), помещаемый в таблицу, представляющую дочернюю сущность, называется внешним ключом родительской сущности. Связь между указанными таблицами устанавливается путем связи первичного и внешнего ключа и имеет вид

Правило 3

Если связь типа 1:1 и класс принадлежности обеих сущностей является необязательным, то необходимо построить три таблицы — по одной для каждой сущности и одну для связи. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Таблица для связи среди своих атрибутов должна иметь ключи обеих сущностей.

Представим, что на ER-диаграмме связи 1:1, изображенной на рисунке 18.5, класс принадлежности сущностей МЕНЕДЖЕР, ФИЛИАЛ будет необязательный. Тогда согласно правилу 3 должны быть сгенерированы три таблицы следующей структуры:

При этом осуществляется декомпозиция связи 1:1 на две связи 1:1 следующим образом:

Для связи типа 1:М существуют только два правила. Выбор одного из них зависит от класса принадлежности сущности на стороне М. Класс принадлежности сущности на стороне 1 не влияет на выбор.

Правило 4

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

На ER-диаграмме связи 1:М, представленной на рисунке 18.5, класс принадлежности сущности СЧЕТ является обязательным. Тогда согласно правилу 4 должны быть сгенерированы две таблицы следующей структуры:

Связь между указанными таблицами будет иметь вид

Правило 5

Если связь типа 1:М и класс принадлежности сущности на стороне М является необязательным, то необходимо построить три таблицы — по одной для каждой сущности и одну для связи. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Таблица для связи среди своих атрибутов должна иметь ключи обеих сущностей.

Представим, что на ER-диаграмме связи 1:М, изображенной на рисунке 18.5, класс принадлежности сущности СЧЕТ является необязательным. Тогда согласно правилу 5 должны быть сгенерированы три таблицы следующей структуры:

При этом осуществляется декомпозиция связи 1:М на две связи — 1:М и 1:1 следующим образом:

Для связи типа M: N класс принадлежности сущности не имеет значения.

Правило 6

Если связь типа M: N, то необходимо построить три таблицы — по одной для каждой сущности и одну для связи. Первичный ключ сущности должен быть первичным ключом соответствующей таблицы. Таблица для связи среди своих атрибутов должна иметь ключи обеих сущностей.

ER-диаграмма связи M: N имеется на рисунке 18.5. Согласно правилу 6 на основании этой ER-диаграммы должны быть сгенерированы три таблицы следующей структуры:

При этом осуществляется декомпозиция связи M: N на две связи 1:М следующим образом:

В таблице КЛИЕНТ — СЧЕТ клиенту, имеющему, например, три счета, будут соответствовать три строки с одним и тем же номером клиента. А счет, у которого, например, два владельца, представляется двумя строками с различными номерами клиентов, владеющих этим счетом.

К ER-модели предметной области БАНК, представленной на рисунке 18.5, применимы правила 1, 4, 6. Связь МЕНЕДЖЕР — ФИЛИАЛ (согласно правилу 1) представляется одной таблицей

Связь ФИЛИАЛ – СЧЕТ (согласно правилу 4) представляется связью

Связь КЛИЕНТ — СЧЕТ (согласно правилу 6) представляется связью

Анализ состава атрибутов полученных таблиц А, В, С, D, Е, F показывает, что таблица В является составной частью таблицы А, таблица Е — составной частью таблицы С. Поэтому таблицы В, Е можно исключить из рассмотрения. Оставшиеся таблицы А, С, D, F можно связать посредством связи первичных и внешних ключей (рисунок 18.7).

Рисунок 18.7 – Реляционная модель предметной области БАНК

В результате получим реляционную модель для ER-модели предметной области БАНК.

18.5 Нормализация таблиц

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

1. Минимизация избыточности данных. В базе данных присутствует избыточность, если одни и те же данные находятся в нескольких местах. Вследствие этого память компьютера используется неэкономно и времени на корректировку данных тратится больше.

2. Минимальное использование отсутствующих значений (Null-значений). Из-за неопределенности интерпретации Null-значений их использование желательно свести к минимуму.

3. Предотвращение потери информации.

Минимизировать избыточность данных позволяет процесс, называемый нормализацией таблиц. Нормализацию можно продемонстрировать на примере таблиц реляционной модели предметной области БАНК (см. рисунок 18.7). Но чтобы изложить этот процесс полнее, будем исходить из предположения, что данные этой предметной области представлены в следующих двух таблицах:

Филиал

НФ | АДР_Ф | НМ | НС | ОСТ | ТИП
511 | Ванеева, 6 | 7 | 1111 2222 3333 | 200 350 1000 | Д т т
513 | Солтыса, 3 | 9 | 5555 6666 | 800 14 | т Д

Примечание. Д — депозитный счет, Т — текущий счет.

Клиент

НК | ФИО_К | СОЦ_П | АДР_К | НС
23 | Сокол С.С. | Служащий | Садовая, 1 | 1111 3333
34 | Брас Б.Б. | Рабочий | Гая, 9 | 5555
45 | Лань Л.Л. | Служащий | Лесная, 4 | 2222 6666 1111

Примечание. Ключ — комбинация НК, НС.

Методика нормализации таблиц сводится к приведению таблиц путем их декомпозиции к той или иной нормальной форме. Были выделены три нормальные формы — 1НФ, 2НФ, ЗНФ. Позже стали выделять нормальную форму Бойса—Кодда (НФБК), а затем 4НФ и 5НФ. Каждая последующая нормальная форма вводит определенные ограничения на хранимые в базе данные.

Реляционная база данных считается эффективной, если все ее таблицы находятся как минимум в ЗНФ.

Определение 1НФ

Таблица находится в 1НФ, если все ее поля содержат только неделимые значения.

Таблицы ФИЛИАЛ и КЛИЕНТ не удовлетворяют требованиям 1НФ. Для приведения их к 1НФ надо вставить новые записи следующим образом:

Филиал

НФ | АДР_Ф | НМ | НС | ост | тип
511 | Ванеева, 6 | 7 | 1111 | 200 | Д
511 | Ванеева, 6 | 7 | 2222 | 350 | т
511 | Ванеева, 6 | 7 | 3333 | 1000 | т
513 | Солтыса, 3 | 9 | 5555 | 800 | т
513 | Солтыса, 3 | 9 | 6666 | 14 | Д

Клиент

НК | ФИО_К | СОЦ_П | АДР_К | НС
23 | Сокол ОС. | Служащий | Садовая, 1 | 1111
23 | Сокол С.С. | Служащий | Садовая, 1 | 3333
34 | Брас Б.Б. | Рабочий | Гая, 9 | 5555
45 | Лань Л.Л. | Служащий | Лесная, 4 | 2222
45 | Лань Л.Л. | Служащий | Лесная, 4 | 6666
45 | Лань Л.Л. | Служащий | Лесная, 4 | 1111

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

Изложение последующих нормальных форм базируется на понятии функциональной зависимости. Функциональная зависимость — это семантическое понятие, отображающее определенную семантическую связь между атрибутами сущности. Пусть сущность характеризуется множеством атрибутов X. Некий атрибут или подмножество атрибутов данного множества — А является функционально зависимым от X тогда и только тогда, когда каждой комбинации значений X соответствует одно и только одно значение А. Это обозначается как

Х → А

Определение 2НФ

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

Пусть {Xi, X2, ..., Xk} — множество полей, образующих первичный ключ. Неключевое поле А функционально полно зависит от первичного ключа, если:

• оно функционально зависит от первичного ключа, т.е. {Х1,Х2, ...,Xk}→A;

• не существует функциональной зависимости А ни от какого подмножества полей первичного ключа (в противном случае А находится в частичной функциональной зависимости от первичного ключа).

В таблице КЛИЕНТ неключевые поля ФИО_К, СОЦ_П, АДР__К функционально зависят от ключа (НК, НС), это запишем как

НК, НС→ ФИО_К, СОЦ_П, АДР_К.

Кроме того, они функционально зависят от подмножества ключа — НК, что запишем

НК → ФИО_К, АДР_К, СОЦ_П.

Следовательно, неключевые поля ФИО_К, СОЦП, АДР_К находятся в частичной функциональной зависимости от первичного ключа (НК, НС) и нарушаются требования 2НФ. Эти поля надо удалить из таблицы КЛИЕНТ. Полученную в результате таблицу назовем КЛИЕНТ — СЧЕТ (таблица 1), которая имеет вид

КЛИЕНТ —СЧЕТ

НК | НС
23 | 1111
23 | 3333
34 | 5555
45 | 2222
45 | 6666
45 | 1111

Эта таблица удовлетворяет требованиям 2НФ.

Удаленные неключевые поля помещаются в новую таблицу совместно с подмножеством НК, от которого они зависят. И это подмножество будет первичным ключом новой таблицы КЛИЕНТ (таблицы 2) вида

КЛИЕНТ

НК | ФИО К | СОЦ_П | АДР_К
23 | Сокол С.С. | Служащий | Садовая, 1
34 | Брас Б.Б. | Рабочий | Гая, 9
45 | Лань Л.Л. | Служащий | Лесная, 4

Новая таблица КЛИЕНТ также удовлетворяет требованиям 2НФ. Ее неключевые поля функционально полно зависят от первичного ключа.

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

Таблица ФИЛИАЛ удовлетворяет требованиям 2НФ, так как ее неключевые поля НФ, АДРФ, НМ, ОСТ, ТИП функционально полно зависят от первичного ключа

НС → НФ, АДР_Ф, НМ, ОСТ, ТИП

Но в таблице ФИЛИАЛ повторяется информация о филиале для всех счетов, обрабатываемых им. Поэтому ее надо привести к ЗНФ.

Определение ЗНФ

Таблица находится в ЗНФ, если она удовлетворяет требованиям 2НФ и не содержит транзитивных зависимостей.

Транзитивной зависимостью называется функциональная зависимость между неключевыми полями. В таблице ФИЛИАЛ она наблюдается

НФ → АДР_Ф, НМ

Следовательно, нарушаются требования ЗНФ. Из таблицы ФИЛИАЛ надо удалить поля, участвующие в этой транзитивной зависимости, — АДРФ, НМ. Получится таблица, характеризующая счет (таблица 3), вида

СЧЕТ

НС | ОСТ | ТИП | НФ |
1111 | 200 | Д | 511
2222 | 350 | Т | 511
3333 | 1000 | Т | 511
5555 | 800 | Т | 513
6666 | 14 | _Д | 513

Затем создается новая таблица, в которую помещаются удаленные поля и поле, от которого они зависят (таблица 4). Она имеет вид

ФИЛИАЛ

НФ | АДР_Ф | НМ
511 | Ванеева, 6 | 7
513 | Солтыса, 3 | 9

Полученные таблицы 3, 4 приведены к ЗНФ. В них каждая запись есть отдельное независимое утверждение. Повторяются только значения внешнего ключа НФ в таблице СЧЕТ, что неизбежно, так как одним филиалом могут обрабатываться несколько счетов.

Как видим, нормализация приводит к фрагментации исходных таблиц. В нашем примере таблица КЛИЕНТ разбивается на таблицы 1, 2, а таблица ФИЛИАЛ — на таблицы 3, 4. Осуществив связь этих таблиц посредством связи первичных и внешних ключей, получим реляционную модель данных предметной области БАНК, в которой минимизирована избыточность данных. Эта модель представлена на рисунке 18.8.

Рисунок 18.8 – Реляционная модель предметной области БАНК после нормализации