Проектирование нормализованных баз данных

Лабораторная работа №3

Построение физической модели базы данных

Цель занятия

1. Получение необходимых сведений о реляционных базах данных.

2. Приобретение практического навыка проектирования нормализованных баз данных.

3. Приобретение практического навыка связывания таблиц и определения условий целостности данных в MS Access.

Общие сведения

Реляционные базы данных

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

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

Реляционная БД содержит множество таблиц, связи между которыми устанавливаются с помощью атрибутов, совпадающих по смыслу, по типу и размеру данных. В каждой из таблиц содержится информация о каких-либо объектах одного типа (группы). Каждая запись в таблицах идентифицирует один объект группы. Отношение между объектами определяет отношение между таблицами. В MS Access имеют место следующие типы отношений между таблицами: «один-к-одному», «один-ко-многим» («многие-к-одному»), «многие-ко-многим».

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

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

Отношение «многие-к-одному» аналогично рассмотренному типу «один-ко-многим». Тип отношения зависит от того, со стороны какой таблицы мы будем смотреть на это отношение. Например, если в предыдущем примере рассматривать отношение таблицы «Заказы» к таблице «Сотрудники», то получим отношение «многие-к-одному».

Отношение «многие-ко-многим» возникает между двумя таблицами в тех случаях, когда:

1. Одна запись первой таблицы может быть связана более чем с одной записью из второй таблицы.

2. Одна запись из второй таблицы может быть связана более чем с одной записью из первой таблицы.

Например, в БД «Борей» каждый заказ из таблицы «Заказы» может включать несколько товаров из таблицы «Товары», в свою очередь, каждый товар может входить в несколько заказов. Таким образом, между таблицами «Заказы» и «Товары» устанавливается тип отношения «многие-ко-многим».

В MS Access отношение «многие-ко-многим» напрямую не реализуется. Для установления такого типа связи между таблицами необходимо использование дополнительной таблицы. В данном случае - это таблица «Сведения о заказе». Связь между «вспомогательной» таблицей и каждой из исходных имеет тип «многие-к-одному» («один-ко-многим»).

Нормальные формы

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

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

2. Исключить избыточность данных, которая может являться источником ошибок в БД и неэкономного использования дискового пространства.

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

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

Таблица находится в первой нормальной форме (1НФ), если она удовлетворяет следующим условиям:

1. Она не имеет повторяющихся записей.

2. В ней отсутствуют повторяющиеся группы полей.

3. Строки таблицы не упорядочены.

4. Столбцы таблицы не упорядочены.

Для удовлетворения первого условия каждая таблица должна иметь, по крайней мере, одно ключевое поле. Если таблица имеет ключевое поле, то третье условие в MS Access не может быть удовлетворено, так как записи в этом случае оказываются автоматически упорядоченными в соответствии со значениями ключа. Однако, на практике это обстоятельство не вызывает серьезных ограничений.

Таблица находится во второй нормальной форме (2НФ), если она удовлетворяет следующим условиям:

1. Она находится в 1НФ.

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

Из приведенного определения следует, что понятие 2НФ применимо только к таблицам, имеющим составной индекс.

Таблица находится в третьей нормальной форме (3НФ), если она удовлетворяет следующим условиям:

1. Она находится во 2НФ.

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

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

Проектирование нормализованных баз данных

Первый шаг в создании нормализованной многотабличной БД заключается в создании универсального отношения - ненормализованной таблицы, содержащей все необходимые элементы данных. Проиллюстрируем процесс нормализации на примере, использующем данные из БД «Борей». Предположим, что мы регистрируем все заказанные продукты в таблице 3.1:

Таблица 3.1.

Код заказа	Код товара	Код клиента	Адрес	Количество	Дата исполнения
		BLONP	24, place Kleber		25.07.06
		BLONP	24, place Kleber		25.07.06
		BERGS	Berguvsvagen 8		12.08.06
		BERGS	Berguvsvagen 8		12.08.06
		BERGS	Berguvsvagen 8		12.08.06
		BERGS	Berguvsvagen 8		12.08.06
		BSBEV	Fauntleroy Circus		26.08.06
		BLONP	24, place Kleber		04.09.06
		BLONP	24, place Kleber		04.09.06
		ANATR	Avda. de la Constitucion 22		18.09.06
		ANATR	Avda. de la Constitucion 22		18.09.06

Структура этой ненормализованной таблицы имеет вид (таблица 3.2):

Таблица 3.2.

Заказы
Код заказа (PK)	PK- Primary Key (первичный ключ)
Код товара (PK)
Код клиента
Адрес
Количество
Дата исполнения

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

Для того чтобы таблица удовлетворяла условию 1НФ, она должна содержать уникальный индекс, что не допускает в таблице повторяющихся записей. Для таблицы «Заказы» определим составной индекс из полей Код заказа и Код товара.

Второе условие для 1НФ указывает на необходимость устранения повторяющихся групп полей. Таблица «Заказы» содержит избыточные данные, например, одни и те же сведения о клиенте повторяются в записи о каждом заказанном продукте.

Для устранения повторяющихся групп поля, соответствующие им, выделяют в отдельную таблицу (таблицы). Для новой таблицы (новых таблиц) определяются уникальные индексы. Для устранения повторяющихся групп нужно переместить поля Код клиента, Адрес и Дата исполнения в новую таблицу (назовем ее «Данные о заказах»), при этом поле Код заказа станет первичным ключом новой таблицы (таблица 3.3).

Таблица 3.3.

Данные о заказах	Заказы
Код заказа (PK)	Код заказа (PK, FK)	PK - Primary Key (первичный ключ)
Код клиента	Код товара (PK)	FK - Foreign Key (внешний ключ)
Адрес	Количество
Дата исполнения

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

Из определения 2НФ следует, что понятие 2НФ применимо только к таблицам, имеющим составной индекс. Таблица «Заказы» в ее первоначальном виде (таблица 3.2) имеет составной индекс, включающий поля Код заказа и Код товара. Поля Код клиента, Адрес и Дата исполнения зависят только от поля Код заказа, являющегося частью первичного ключа, поэтому таблица «Заказы» не находится во 2НФ. После разбиения таблицы «Заказы» на две (таблица 3.3) таблицы «Данные о заказах» и «Заказы» находятся во 2НФ.

В результате новые таблицы будут выглядеть:

Таблица 3.4.

Данные о заказах

Код заказа	Код клиента	Адрес	Дата исполнения
	BLONP	24, place Kleber	25.07.06
	BERGS	Berguvsvagen 8	12.08.06
	BSBEV	Fauntleroy Circus	26.08.06
	BLONP	24, place Kleber	04.09.06
	ANATR	Avda. de la Constitucion 22	18.09.06

Таблица 3.5.

Заказы

Код заказа	Код товара	Количество

Однако таблицы, находящиеся во второй, но не в третьей нормальной форме, по-прежнему содержат аномалии модификации данных. Например, в таблице «Данные о заказах» удаление записи о заказе приведет к удалению записи о самом клиенте.

Таблица «Заказы» уже находится в 3НФ, т.к. неключевое поле Количество полностью зависит от составного первичного ключа (Код заказа, Код товара). Однако, таблица «Данные о заказах» в 3НФ не находится, т.к. содержит зависимость между неключевыми полями: поле Адрес зависит от поля Код клиента.

Для приведения таблицы «Данные о заказах» к 3НФ создадим новую таблицу «Клиенты» и переместим в нее поля Код клиента и Адрес. Поле Адрес из исходной таблицы удалим. А поле Код клиента оставим в качестве внешнего ключа (таблица 3.6).

Таблица 3.6.

Клиенты	Заказано	Заказы
Код клиента (PK)	Код заказа (PK)	Код заказа (PK, FK)
Адрес	Код клиента (FK)	Код товара (PK)
	Дата исполнения	Количество

Итак, после приведения исходной таблицы к 3НФ таблиц стало три:

Таблица 3.7.

Клиенты

Код клиента	Адрес
ANATR	Avda. de la Constitucion 22
BERGS	Berguvsvagen 8
BLONP	24, place Kleber
BSBEV	Fauntleroy Circus

Таблица 3.8.

Заказано

Код заказа	Код клиента	Дата исполнения
	BLONP	25.07.06
	BERGS	12.08.06
	BSBEV	26.08.06
	BLONP	04.09.06
	ANATR	18.09.06

Таблица 3.9.

Заказы

Код заказа	Код товара	Количество

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

В реляционных БД, в том числе и MS Access, устанавливаются постоянные связи между таблицами, которые поддерживаются при создании форм, отчетов, запросов. При установлении связи между двумя таблицами выбираются поля, которые содержат одну и ту же информацию. Чаще всего связывается первичный ключ одной таблицы с совпадающими полями другой таблицы.

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

Создание связей между таблицами в MS Access осуществляется в окне диалога Схема данных. Для определения связей между таблицами необходимо выполнить следующие действия:

1. Открыть окно Схема данных, нажав кнопку Схема данных на ленте инструментов на вкладке Работа с базами данных. На экране откроется окно Схема данных и окно диалога Добавление таблицы (рис. 3.1).

рис. 3.1. Окно Схема данных и окно диалога Добавление таблицы

2. Добавить в окно Схемы данных две связываемые таблицы, например, «Сотрудники» и «Заказы». Если окно диалога Добавление таблицы будет случайно закрыто, то вернуть его на экран можно, нажав кнопку Отобразить таблицу на контекстной вкладке Работа со связями – Конструктор ленты инструментов.

3. В списке таблиц выделить добавляемые таблицы и нажать кнопку Добавить (рис. 3.1). В окне Схема данных появятся связываемые таблицы (рис. 3.2).

рис. 3.2. Связываемые таблицы в окне Схема данных

4. Для связывания таблиц выбрать поле в первой связываемой таблице и переместить его с помощью мыши на соответствующее поле второй таблицы. Для связывания сразу нескольких полей выбрать эти поля при нажатой клавише Ctrl и переместить во вторую таблицу группу выделенных полей. На экране откроется окно диалога Изменение связей (рис. 3.3). Это окно также можно открыть, щелкнув по кнопке Изменить связи в группе Сервис контекстной вкладки Работа со связями – Конструктор ленты инструментов.

рис. 3.3. Окно диалога Изменение связей

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

6. Обратить внимание на тип отношения создаваемой связи. При необходимости выбрать другие имена. Затем нажать кнопку Создать. После этого происходит возврат в окно Схема данных, которое примет вид (рис. 3.4).

рис. 3.4. Окно Схема данных со связанными таблицами

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

рис. 3.5. Установка связи типа «многие-ко-многим»

Для создания подобного отношения достаточно добавить в окно Схема данных таблицы «Заказы», «Товары» и «Сведения о заказе» и создать связи «один-ко-многим» между этими таблицами (рис. 3.5).

Для изменения или удаления связей достаточно установить указатель мыши на линию связи и щелкнуть правой кнопкой. Будут предложены соответствующие действия в контекстном меню.

Для удаления таблицы из макета Схемы данных (окна Схема данных) надо выбрать эту таблицу и нажать клавишу Delete на клавиатуре.