Операции над данными в реляционной модели

⇐ ПредыдущаяСтр 23 из 32Следующая ⇒

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

К операциям обновления относятся:

1.Операция Включить – добавляет новый кортеж в таблицу-отношение. Требует задания имени таблицы и обязательного значения ключей. Выполняется при условии уникальности значения ключа. Добавить новую строку со значением ключа, которое уже есть в таблице невозможно.

2.Операция Удалить – удаляет один или группу кортежей. Требует задания имени таблицы, имени поля и параметров значения полей, кортежи с которыми должны быть удалены.

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

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

К операциям обработки относятся:

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

СКЛАД 1		СКЛАД 2
№	Код поставщика	Название материала	№	Код поставщика	Название материала
К11		МЕЛ	К21		МЕЛ
К12		ТЕТРАДЬ	К22		МЕЛ
К13		КАРАНДАШ	К23		РУЧКА

Таблица, полученная в результате операции объединения

СКЛАД
№	Код поставщика	Название материала
К11		МЕЛ
К12		ТЕТРАДЬ
К13		КАРАНДАШ
К21		МЕЛ
К23		РУЧКА

Рис.8.9 Иллюстрация операции объединения

2. Операция Пересечения (произведение множеств) выполняется над двумя односхемными таблицами-отношениями. Результатом является таблица-отношение построенная по той же схеме и содержащая только те кортежи первой таблицы, которые входят также в состав кортежей второй таблицы.

После выполнения операции пересечения над исходными таблицами-отношениями получим:

2040 МЕЛ

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

После выполнения операции вычитания над исходными таблицами-отношениями получим:

4050 ТЕТРАДЬ

3070 КАРАНДАШ

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

Сотрудники первого отдела	Прохождение обследования		Прохождение обследования сотрудниками первого отдела
ФИО	Вид обследования	Дата		ФИО	Вид обследования	Дата
Иванов И.И.	Сердечно-сосудистая система	1.12		Иванов И.И.	Сердечно-сосудистая система	1.12
Петров П.П.	Желудочно-кишечный тракт	8.12		Иванов И.И.	Желудочно-кишечный тракт	8.12
				Петров П.П.	Сердечно-сосудистая система	1.12
				Петров П.П.	Желудочно-кишечный тракт	8.12

Рис.8.10 Иллюстрация операции декартова произведения

5. Операция Выборка (горизонтальное подмножество) – выполняется над одной таблицей-отношением. Результатом является таблица-отношение той же схемы, содержащая подмножество кортежей исходной таблицы, удовлетворяющих условию выборки.

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

Служащие
Фамилия	Номер лаборатории	Должность
Иванов		инженер
Петров		инженер
Сидоров		инженер
Федоров		экономист

Берется проекция на поля «Номер лаборатории» и «Должность»

Номер лаборатории	Должность
	инженер
	инженер
	экономист

Рис.8.11 Иллюстрация выполнения операции проекция

Смысл операции проекции – переупорядочить столбцы в таблице-отношении и удалить одинаковые кортежи (кроме одного)

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

Документы с охраняемыми сведениями по ОКР		Журнал выдачи документов сотрудникам
Рег. №	Тематика		Рег. №	Дата	ФИО
13/1-С	Топливо			1.11	Иванов И.И.
251-СС	Боезаряд			1.11	Иванов И.И.
1455-С	Двигатель		251-СС	2.11	Петров П.П.
И-43С	Привод		И-43С	2.11	Сидоров С.С.
			123-4/С	3.11	Петров П.П.
				3.11	Егоров Е.Е.
			1455-С	4.11	Петров П.П.
			675-И/С	4.11	Сидоров С.С
			И-43С	3.11	Петров П.П.

Соединение по полю «Рег.№»

Сотрудники, работающие с документами, содержащими охранные сведения
Рег. №	Тематика	Дата	ФИО
251-СС	Боезаряд	2.11	Петров П.П.
1455-С	Двигатель	4.11	Петров П.П.
И-43С	Привод	2.11	Сидоров С.С.
И-43С	Привод	3.11	Петров П.П.

Рис.8.12 Иллюстрация выполнения операции соединения

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

Таблица-делимое Таблица делитель

Поездки граждан г. Урюпинска в Германию		Требуемое сочетание турфирм и городов Германии
ФИО	Турфирма	Город	Турфирма	Город
Иванов И.И.	«Евротур»	Берлин	«Евротур»	Берлин
Иванов И.И.	«Веси»	Гамбург	«Веси»	Гамбург
Петров П.П.	«Евротур»	Гамбург
Петров П.П.	«Веси»	Берлин
Сидоров С.С.	«Евротур»	Берлин
Сидоров С.С.	«Веси»	Гамбург

Рис.8.13 Иллюстрация выполнения операции деления

Гражданин с требуемым сочетание турфирм и городов

ФИО

Иванов И.И.

Сидоров С.С.

Рассмотренные операции реализуются соответствующими средствами СУБД (язык SQL).

Нормализация отношений

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

1. быстрый доступ к данным БД;

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

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

Теорию нормализации отношений в реляционной модели разработал Кодд. Сначала использовалось 3 нормальные формы, позже были выделены еще две, т.е. разработаны 5 нормальных форм таблиц. Каждая последующая (от 1 до 5) нормальная форма должна удовлетворять требованиям предыдущей и некоторым дополнительным условиям. 4 и 5 формы при практическом проектировании, как, правило, не применяются.

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

Таблица 8.1.

Пример ненормализованной БД

Работник
Код работника	ФИО	Тип специальности	Код здания
	Петров И.И.	электрик
	Петров И.И.	электрик
	Сидоров В.В.	штукатур
	Сидоров В.В.	штукатур
	Сидоров В.В.	штукатур
	Сидоров В.В.	штукатур
	Корнеев А.А.	электрик

Реляционная таблица спроектирована неудачно. В 4-х кортежах, соответствующих рабочему 1412, повторяется одно и то же имя и информация о типе специальности. Эта избыточность данных или повторение приводит не только к потере лишнего места; она может вызвать нарушение целостности данных в БД.

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

Аномалия обновления. Противоречивость данных, вызванная избыточностью и частичным обновлением.

Теперь предположим, что Сидоров в течение 3-х месяцев был на больничном, и все здания, на которые он был назначен работать, уже закончены. Если принимается решение удалить все строки о законченных зданиях из таблицы, то информация о рабочем Сидорове будет потеряна. Это называется аномалией удаления.

Аномалия удаления. Непреднамеренная потеря данных, вызванная удалением других данных.

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

Аномалия ввода. Невозможность ввести данные в таблицу, вызванная отсутствием других данных.

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

Первая нормальная форма

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

Таблица 8.2.

Таблица, нарушающая 1НФ

Работник
Код работника	ФИО	Тип специальности	Код здания
	Петров И.И.	электрик	312, 515
	Иванов И.В.	штукатур	312, 460, 510
	Сидоров В.В.	штукатур

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

Таблица 8.3.

Таблица в 1НФ

Работник
Код работника	ФИО	Тип специальности	Код здания
	Петров И.И.	электрик
	Петров И.И.	электрик
	Иванов И.В.	штукатур
	Иванов И.В.	штукатур
	Иванов И.В.	штукатур
	Сидоров В.В.	штукатур

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

Функциональные зависимости

Функциональные зависимости (ФЗ) накладывают дополнительные ограничения на реляционную схему. Основная идея состоит в том, что значение одного атрибута в кортеже однозначно определяет значение другого атрибута. В таблице Работник Код работника однозначно определяет ФИО, Тип специальности. Эти функциональные зависимости записываются так:

ФЗ: Код работника → ФИО; Код работника → Тип специальности

Символ → читается «функционально определяет»

Атрибут в левой части ФЗ называется детерминантом, т.к. его значение определяет значение атрибута в правой части. Ключ таблицы является детерминантом, т.к. его значение однозначно определяет значение каждого атрибута таблицы.

Вторая нормальная форма

Вторая и третья нормальные формы касаются отношений между ключевыми и не ключевыми атрибутами.

Вторая нормальная форма. Никакие не ключевые атрибуты не являются функционально зависимыми лишь от части ключа. Т.о. 2НФ может оказаться нарушенной, если ключ составной.

Таблица 8.4.

Таблица, нарушающая 2НФ

Назначения
Код работника	Код здания	Дата начала	ФИО
		10.10	Иванов И.И.
		01.11	Петров П.П.
		17.10	Иванов И.И.
		08.12	Петров П.П.
		15.10	Петров П.П.

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

1. Имя работника повторяется в каждой строке, относящейся к назначению этого работника;

2. при изменении имени требуется обновить все строки, содержащие записи о назначениях этого работника;

3. из-за такой избыточности может возникнуть несоответствие данных, когда в разных строках содержатся разные имена для одного и того же работника;

4. если в какой-то момент времени работник не имеет назначений, то может не оказаться строки, в которой можно хранить имя работника. Это аномалия ввода.

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

Работник (Код работника, ФИО)

Назначения (Код работника, Код здания, Дата начала).

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

Процесс разбиения состоит из следующих шагов:

1. создается новая таблица, атрибутами которой будут атрибуты исходной таблицы, входящую в противоречащую правилу ФЗ. Детерминант ФЗ становится ключом новой таблицы;

2. Атрибут, стоящий справа в ФЗ, исключается из исходной таблицы;

3. если более одной ФЗ нарушают 2НФ, то шаги 1 и 2 повторяются для каждой такой ФЗ;

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

Третья нормальная форма

Реляционная таблица имеет 3НФ, если для любой ФЗ: Х → Y детерминант Х является ключом. Из определения следует, что любая таблица, удовлетворяющая 3НФ, также удовлетворяет 2НФ. Однако обратное неверно.

Третья нормальная форма. Любой детерминант является ключом.

Таблица 8.5.

Таблица, нарушающая 3НФ

Работник
Код работника	Тип специальности	Премиальные
	электрик	3.5
	штукатур	3.0
	электрик	3.5

ФЗ: Код работника → Тип специальности

Код работника → Премиальные

Однако имеется ФЗ: Тип специальности → Премиальные

Первые две ФЗ удовлетворяют 3НФ, а последняя - нет, т.к. Тип специальности не является ключом. Таблица Работник не удовлетворяет 3НФ, однако удовлетворяет 2НФ. Недостатки таблицы, не удовлетворяющей 3НФ:

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

2. если размер премиальных изменится, то требуется обновить каждую такую строку. Если строка удаляется, мы можем потерять информацию о размере премиальных для данной специальности. Т.о. таблица подвержена аномалиям обновления и удаления;

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

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

Работник (Код работника, Тип специальности)

Премиальные (Тип специальности, Премиальные)

⇐ Предыдущая 18 19 20 21 222324 25 26 27 Следующая ⇒