Лекция: Особенности проектирования многотабличных баз данных

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

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

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

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

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

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

В рассматриваемом примере таблица «Абонент» содержит значительное количество повторяющихся данных – сведения об абонентах повторяются для каждого междугороднего звонка. Однотабличная структура базы данных «Межгород» является причиной следующих проблем, которые возникают во время работы:

— увеличивается время на ввод повторяющихся данных;

— при изменении повторяющихся данных, необходимо корректировать все записи, содержащие данную информацию;

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

— при многократном вводе повторяющихся данных возрастает вероятность ошибки.

Таблица в первой нормальной форме должна удовлетворять следующим требованиям:

— таблица не должна иметь повторяющихся записей;

— все поля таблицы должны содержать атомарные (неделимые) данные;

— строки не должны быть упорядочены;

— столбцы не должны быть упорядочены.

Таблица находится во второй нормальной форме, если:

— она удовлетворяет условиям первой нормальной формы;

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

О таблице говорят, что она находится в третьей нормальной форме, если:

— она удовлетворяет условиям второй нормальной формы;

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

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

База данных «Межгород», состоящая из одной таблицы, содержит поля, показанные в таблице 1.

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

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

В результате приведение базы данных «Межгород» к третьей нормальной форме получим базу данных, состоящую из трех таблиц: «Абонент» «Город» «Разговор». Каждая из этих таблиц имеет уникальный индекс, однозначно определяющий записи в таблицах.

Структура созданных таблиц показана на рисунке 1.1.

Определение связей между таблицами

В MS Access можно устанавливать постоянные связи между таблицами, которые будут поддерживаться при создании форм, отчетов и запросов.

MS Access поддерживает четыре типа отношений между таблицами: «один-к-одному», «один-ко-многим», «многие-к-одному», «многие-ко-многим»

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

Связь типа «один-ко-многим» — наиболее часто используемый тип связи между таблицами. В такой связи каждой записи в таблице А могут соответствовать несколько записей в таблице В, а запись в таблице В не может иметь более одной соответствующей ей записи в таблице А.

При отношении «многие-ко-многим» одной записи в таблице А могут соответствовать несколько записей в таблице В, а одной записи в таблице В несколько записей в таблице А. Такая схема реализуется только с помощью третьей (связующей) таблицы, ключ которой состоит, по крайней мере, из двух полей, которые являются полями внешнего ключа в таблицах А и В. Например, между таблицами «Абонент» и «Город» имеется отношение «многие-ко-многим»; которое определяется путем создания двух связей с отношением «один-ко-многим» через таблицу «Разговор» (Рисунок 5.4).

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

Процедура определения связей между таблицами базы данных следующая:

1. Закрыть все открытые таблицы. Создавать или изменять связи между открытыми таблицами нельзя.

2. Переключиться на вкладку «работа с базами данных».

3. Нажать кнопку Схема данных(Relationships) на панели инструментов.

4. Если в базе данных не определено никаких связей, то на экран автоматически будет выведено окно Добавление таблицы(Show Table). Если до определения связей необходимо добавить таблицы, а диалогового окна Добавление таблицы(Show Table) на экране нет, нужно нажать кнопку Отобразить таблицу(Show Table) на панели инструментов. Если же таблицы, которые необходимо связать, уже отображены на экране, перейти к шагу 6.

5. В окне диалога Добавление таблицы(Show Table) дважды щелкнуть на именах таблиц, для которых требуется определить связи. Закрыть окно.

6. Для связывания полей выбрать поле в одной таблице и перенести его на соответствующее поле во второй таблице. Для связывания сразу нескольких полей выбрать и перенести их при нажатой клавише <Ctrl>.

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

* поле счетчика можно связывать с числовым полем, если в последнем в свойстве Размер полязадано значение «Длинное целое»;

* поле счетчика можно связать с числовым, если для обоих полей в свойстве Размер полязадано значение «Код репликации».

7. В открывшемся диалоговом окне Изменение связей(Relationships) проверить имена полей, представленные в двух списках. В случае необходимости, здесь можно внести изменения. Если необходимо, можно установить параметры связи.

8. Для создания связи нажать кнопку Создать(Create).

9. Шаги с 5 по 8 проделать для каждой связываемой пары таблиц.

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

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

Тип создаваемой связи зависит от полей, для которых определяется связь.

— Отношение «один-ко-многим» создается в том случае, когда только одно из полейявляется ключевым или имеет уникальный индекс.

— Отношение «один-к-одному» создается в том случае, когда оба связываемых поля являются ключевыми или имеют уникальные индексы.

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

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