Нормализация. Реляционные отношения между таблицами

Реляционные отношения между таблицами. Целостность данных

Некоторые правила построения баз данных

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

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

Объектно-связный подход, положенный в основу многих реляционных СУБД, осуществляет поддержку семантической целостности данных при их обновлении на трех уровнях: уровне внешних ключей, внутреннем (первичных ключей) и полей.

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

Требование внутренней целостности есть требование уникальности первичного ключа. Проверка осуществляется обычно при вставке новой записи.

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

Остановимся подробнее на целостности на уровне внешних ключей.

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

Отношение «один-к-одному»

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

Пример отношения " один-к-одному " приведен на рисунке 1.1.

Таблица «Сотрудники» Таблица «Информация о

сотрудниках»

№	ФИО	Должность	Отдел		№	Год рожд.	Кол-во детей	…
1	Иванов В.В.	инженер						…
2	Петров П.П.	бухгалтер						…
3	Васин И.И.	прораб						…
…	…	…			…	…	…	…

Рис. 1.1. Отношение " один-к-одному "

Отношение «один-ко-многим»

Связь " один-ко-многим " является самой распространенной для реляционных баз данных. Она позволяет моделировать иерархические структуры данных. Различают две разновидности этой связи: в первом случае всякой записи в родительской таблице должны соответствовать записи в дочерней таблице, во втором случае некоторые записи в родительской таблице могут не иметь записей в дочерней таблице.

Пример отношения " один-ко-многим " приведен на рисунке 1.2.

Таблица «Товары» Таблица «Отпуск товаров»

Товар	Ед. изм.	Цена ед.		Товар	Дата	Кол-во (ед)
Сахар	Кг			Сахар	10.01.03
Макароны	Кг			Сахар	15.01.03
Куры	Кг			Сахар	12.02.03
Фанта	бут.1 л			Макароны	20.02.03
				Макароны	10.03.03
				Фанта	09.01.03

Рис. 1.2. Отношение " один-ко-многим ”

Отношение «многие-ко-многим»

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

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

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

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

Таблица «Группы» Таблица «Преподаватели и предметы»

Группа	№ предмета		№ предмета	ФИО	Предмет	Кафедра
1Ф1				Иванов В.В.	Информатика	ТИ-1
1Ф1				Иванов В.В.	Теория систем	ТИ-1
1Ф1				Петров П.П.	Теория систем	РИО
1У1				Васин И.И.	Информатика	ТИ-1
				Лавров И.И.	Философия	ЭИ-1
…	…		…	…	…	…

Рис. 1.3. Отношение " многие-ко-многим ”

Рассмотрим первый случай. Заменим в таблице «Товары» в поле «Товар» «Сахар» на «Рафинад». В результате в дочерней таблице «Отпуск товаров» для поля «Рафинад» нет сведений об его отпуске со склада, а некоторые записи таблицы «Отпуск товаров» содержат сведения об отпуске товара «Сахар», о котором нет информации в таблице «Товары».

Рассмотрим второй случай. Заменим в одной из записей таблицы «Отпуск товаров» значение поля связи «Сахар» на «Рафинад». В результате в дочерней таблице «Отпуск товаров» недостоверны сведения об отпуске товара «Сахар» и одна из записей таблицы «Отпуск товаров» содержат сведения об отпуске товара «Рафинад», данные о котором отсутствуют в таблице «Товары».

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

СУБД обычно блокирует действия, которые нарушают целостность связей между таблицами, т.е. нарушают ссылочную целостность. Когда говорят о ссылочной целостности, имеют в виду совокупность связей между отдельными таблицами во всей БД. Нарушение хотя бы одной такой связи делает информацию в БД недостоверной. Чтобы предотвратить потерю ссылочной целостности, используют механизм каскадных изменений. Например, в СУБД Access на этапе формирования связей надо для этого сначала установить флажок «Обеспечение целостности данных», а далее флажки «Каскадное обновление связанных полей» и «Каскадное удаление связанных полей».

Для правильной организации данных в таблицах используется понятие нормализации. Нормализация данных - процесс исключения избыточной информации, при котором достигается то, что каждый элемент информации запоминается только один раз. Теория и практические рекомендации по нормализации рассматриваются в книгах по реляционным базам данных, например, [3].

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

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

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

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

Неповторяемость означает, что нет повторяемых значений полей в одной записи. Имеется в виду не та ситуация, когда указано несколько полей с одинаковыми значениями. Например, рассмотрим таблицу для реализации записной книжки. Какие поля здесь необходимы? Фамилия, имя, телефон, e-mail... А может несколько телефонов, e-mail? В наше время почти у каждого есть и рабочий, и домашний, и мобильный телефоны. Так сколько таких полей ввести в таблицу? Правильнее всего будет создать еще одну таблицу с телефонами, а в ней поместить ссылку на первую (смотри рисунок 1.4). Тогда можно будет для каждого абонента указать произвольное число телефонов.

Код	Фамилия	Адрес....		Чей	Какой	Номер
1	Иванов				Рабочий 1	23-45-67
2	Петров				Рабочий 2	23-45-98
					Домашний	11-34-98
					Домашний	45-09-87
				...	...	...

Рис. 1.4. Таблицы записной книжки

Этот процесс называется разбиением таблицы на главную и подчиненную. При этом обе таблицы находятся в отношении " один-ко-многим ".

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

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

Рассмотрим приведение БД к третьей нормальной форме на примере решения задачи автоматизации процесса отпуска товаров со склада по накладной. В начале, представим необходимые для этой задачи показатели в виде одной таблицы (смотри таблицу 1.1).

Таблица 1.1. ОТПУСК-ТОВАРОВ-СО-СКЛАДА

Дата Покупатель Город Адрес Товар Ед_измерения Цена_за_ед_изм Отпущено_ед Общая_стоимость Номер_накладной

Далее осуществим ее нормализацию. На рисунке 1.5 изображена схема этой БД, приведенной к третьей нормальной форме.

ТОВАРЫ ПОКУПАТЕЛИ

НАКЛАДНЫЕ

ОТПУСК_ТОВАРОВ_СО_СКЛАДА

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

Рассмотрим все плюсы и минусы нормализации данных:

«+»:

· информация нигде не дублируется, здесь присутствует только один элемент избыточных данных – поля связи;

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

«-»:

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

· уменьшается целостное восприятие БД как системы взаимосвязанных данных;

· при составлении запросов необходимо считывать связанные данные из нескольких таблиц.

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

Дата добавления: 2014-12-27