то исходное отношение будет восстановлено. При этом следует помнить, что проведенная операция даст истинный результат только в том случае, если один из операндов участвующих в операции, является в отношении-операнде реляционным ключом, а не просто входит в него. В нашем случае атрибут №_зачетки является первичным ключом в отношении R1 (рис. 47).

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

Отношение R1 находится в первой нормальной форме, первичным ключом является атрибут №_зачетки. Каждый непервичный атрибут отношения R1 функционально полно зависит от ключа, следовательно, оно находится во второй нормальной форме. Заметим, что отношение всегда находится во второй нормальной форме, если первичный ключ этого отношения атомарен.

Отношение R2 (рис. 48) находится в 1НФ и его ключом является набор атрибутов {№_зачетки, Семестр, Предмет}. Однако оно не находится в 2НФ в силу неполной функциональной зависимости непервичного атрибута Преподаватель от ключа отношения (функциональная зависимость F₅ (рис. 46) привносит неполноту) и неполной функциональной зависимости атрибута Вид_Работы от ключа отношения (функциональная зависимость F₆ (рис. 46)). Учитывая указанные функциональные зависимости, отношение со схемой R2 должно быть, представлено в виде трех проекций R3 (рис. 49), R4 (рис. 50) и R5 (рис. 51):

№_зачетки	Семестр	Предмет	Оценка
01	1	Химия	Отл
01	1	Физика	Отл
01	1	История	Отл
02	1	Химия	Хор
02	1	Физика	Отл
02	1	История	Хор

Предмет Преподаватель Физика Петров История Львов Химия Сомов

Семестр Предмет Вид_работы 1 Физика Экз 1 История Экз 1 Химия Экз

Рис. 50. Проекция R4

Рис. 51. Проекция R5

Из полученной совокупности проекций R3, R4, R5, может быть восстановлено отношение R2 (рис. 48), если эквисоединение будет производится по ключам указанных отношений (рис. 52).

R3 (№_зачетки, Семестр, Предмет, Оценка),
R4 (Предмет, Преподаватель),
R5 (Семестр, Предмет, Вид_Работы).

Рис. 52. Промежуточный набор отношений

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

Результатом этапа будет совокупность отношений во второй нормальной форме (рис. 53):

R1 (№_зачетки, Фамилия, Группа, Факультет),
R3 (№_зачетки, Семестр, Предмет, Оценка),
R4 (Предмет, Преподаватель),
R5 (Семестр, Предмет, Вид_Работы).

Рис. 53. Набор отношений в 2НФ

Этап 2. Приведение отношения к 3НФ

Определение 3НФ запрещает наличие транзитивных зависимостей между непервичными атрибутами, поэтому анализу подлежит только отношение R1 (№_зачетки, Фамилия, Группа, Факультет), в котором присутствует несколько непервичных атрибутов. Все оставшиеся отношения по определению находятся в 3НФ.

Наличие функциональной зависимости F₇ = Группа Факультет в отношении R1, приводит к транзитивной зависимости №_зачетки Группа Факультет, от которой следует избавиться, оставив атрибут Группа в качестве внешнего ключа в отношении R1. В результате получатся две проекции (рис. 54):

R1₁ ( №_зачетки, Фамилия, Группа),
R1₂ ( Группа, Факультет).

Рис. 54. Устранение транзитивной зависимости

Этап 3. Приведение к БКНФ

Все полученные отношения (рис. 55):

R1₁ (№_зачетки, Фамилия, Группа),
R1₂ (Группа, Факультет),
R3 (№_зачетки, Семестр, Предмет, Оценка),
R4 (Предмет, Преподаватель),
R5 (Семестр, Предмет, Вид_Работы).

Рис. 55. Состав отношений в БКНФ

находятся в нормальной форме Бойса-Кодда, потому что каждая функциональная зависимость из множества F = {F₁, . . . , F₇} (рис. 46) является (подразумевается) потенциальным ключом.

Дальнейшая нормализация (т.е. приведение отношений к 4НФ и 5НФ) для нашего примера не требуется, так как в нем отсутствуют зависимости соединения и многозначные зависимости. Поэтому расширим отношение R атрибутами, позволяющими показать дальнейшие этапы схемы нормализации.

Пусть объект ПРЕПОДАВАТЕЛЬ наделяется администратором базы следующими свойствами ПРЕПОДАВАТЕЛЬ (Шифр, Фамилия, Должность, Оклад) (рис. 57) и для него фиксируются следующие зависимости (рис. 56):

F₈: Шифр Фамилия,
F₉: Шифр Должность,
F₁₀: Шифр Оклад,
F₁₁: Должность Оклад.

Рис. 56. Функциональные зависимости в объекте ПРЕПОДАВАТЕЛЬ

Отметим, что зависимость F₁₀, является очень сильным ограничением для рассматриваемой предметной области (подумайте почему ?), но к этому вопросу мы вернемся в п. 3.3.3, при рассмотрении проблем, связанных с модификацией хранимых данных, и способов выделения объектов, подлежащих хранению в базе данных.

Для преподавателя также вводится отношение истории его работы:

ИСТОРИЯ_РАБОТЫ (Шифр, Предмет, Год, Должность),

учитывающее какие предметы обслуживал преподаватель в разные годы и какую должность при этом он занимал.

Шифр Фамилия Должность Оклад 01 Петров Препод. 2100 02 Львов Ассистент 1700 03 Сомов Доцент 2500 04 Иванов Доцент 2500 05 Сидоров Доцент 2500

Шифр Предмет Должность Год 01 Числ.методы Доцент 2000 01 Теория алгор. Доцент 2000 02 Числ.методы Ассистент 2000 02 Теория алгор. Ассистент 2000 01 Числ.методы Доцент 2001 01 Теория алгор. Доцент 2001 02 Числ.методы Препод. 2001 02 Теория алгор. Препод. 2001

Рис. 57. Проекция R6 - ПРЕПОДАВАТЕЛЬ

Рис. 58. Проекция R7 - ИСТОРИЯ_РАБОТЫ

В отношении R6 (рис. 57) в качестве ключа используется атрибут Шифр и оно имеет транзитивную зависимость между непервичными атрибутами:

SELECT *
FROM R8, R9
WHERE R8.Шифр = R9.Шифр;

При этом с помощью языка описания схем DDL [30] построенные проекции должны получить в описании соответствующие определения ключей;

CREATE	TABLE	R8
Шифр	CHAR	(2)	NOT NULL,
Фамилия	CHAR	(17)	NOT NULL,
Должность	CHAR	(15)	NOT NULL,

 PRIMARY KEY (Шифр);

и

CREATE	TABLE	R9
Шифр	CHAR	(2)	NOT NULL,
Оклад	DECIMAL	(8,2)	NOT NULL,

 PRIMARY KEY (Шифр);

Рис. 59. Описание схемы на SQL

Напомним, что язык DDL является составной частью языка SQL.

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

Таким образом, исходное отношение ПРЕПОДАВАТЕЛЬ будет представлено двумя отношениями R8 (рис. 60) и R9 (рис. 61):

Шифр Фамилия Должность 01 Петров Препод. 02 Львов Ассистент 03 Сомов Доцент 04 Иванов Доцент 05 Сидоров Доцент

Шифр Оклад 01 2100 02 1700 03 2500 03 2500 03 2500

Рис. 60. Проекция R8

Рис. 61. Проекция R9

Отметим, что исходное отношение ПРЕПОДАВАТЕЛЬ (рис. 57) может быть представлено и другой группой проекций (см. рис. 62 и 63), из которых также восстановимо исходное отношение.

Шифр Фамилия Должность 01 Петров Препод. 02 Львов Ассистент 03 Сомов Доцент 04 Иванов Доцент 05 Сидоров Доцент

Должность Оклад Преподаватель 2100 Ассистент 1700 Доцент 2500

Рис. 62. Проекция R10

Рис. 63. Проекция R11

Полученная совокупность проекций позволяет восстановить исходное отношение на основе следующего предложения языка SQL:

SELECT *
FROM R10, R11
WHERE R10.Должность = R11.Должность;

Результат разложения должен быть зафиксирован в описании схем отношений (рис. 64, а и 64, б):

CREATE	TABLE	R8
Шифр	CHAR	(2)	NOT NULL,
Фамилия	CHAR	(17)	NOT NULL,
Должность	CHAR	(15)	NOT NULL,

PRIMARY KEY (Шифр),
FOREIGN KEY (Должность) REFERENCES R11
ON UPDATE CASCADE;

Рис. 64, а. Схема отношения R10

CREATE	TABLE	R9
Должность	CHAR	(15)	NOT NULL,
Оклад	DECIMAL	(8,2)	NOT NULL,

 PRIMARY KEY (Шифр);

Рис. 64, б. Схема отношения R11

Какое же решение должен принять алгоритм теории нормализации: включить в результирующую схему отношения R8, R9 или R10, R11 (указанные группы отношений находятся в 3НФ и нетрудно увидеть, что они находятся и в 4НФ).

Рассмотрим более подробно декомпозицию R10 и R11. Указанные проекции независимы друг от друга в следующем смысле: операции обновления данных в каждой из проекций могут быть выполнены совершенно независимо друг от друга [3] (отметим, что на практике это не так, но мы рассматриваем традиционную трактовку). Если такое обновление допустимо только в контексте данной проекции, т.е. не нарушается уникальность ключа в этой проекции, то соединение проекций R10 и R11 после обновления будет восстанавливать исходное отношение в новом его состоянии. Т.е. при соединении не будут нарушены ограничения, наложенные на ФЗ в отношении представленном на рис. 57.

Анализ декомпозиции R8 и R9 показывает, что обновление любой из этих проекций должно контролироваться системой, чтобы отслеживать нарушение зависимости ДолжностьОклад. Другими словами, обе проекции в декомпозиции не являются независимыми одна от другой и, кроме того, зависимость ДолжностьОклад становится ограничением между отношениями, хранимыми в базе данных. Такое ограничение уже явно не поддерживается в стандарте SQL и приходится писать программный код (триггеры, хранимые процедуры и т. п.).

Для декомпозиции R10, R11 такая необходимость отсутствует (не надо писать самим программный код), т.к. это становится прерогативой самой СУБД (для этого и используется ссылка REFERENCES R11 с опцией ON UPDATE CASCADE, см. рис. 64), которая опирается на правило уникальности первичных ключей отношения.

Заметим, что алгоритмы теории нормализации – это чисто синтаксические правила и поэтому они не могут "чувствовать" семантику любых видов зависимостей (подобное замечание было высказано достаточно давно, см. например [1, 6].

Традиционный реляционный подход, опираясь на концепцию независимых проекций, предлагает использовать для нашего примера декомпозицию R10 и R11, исходя из следующего определения независимых отношений [3].

Определение. Проекции R1 и R2 независимы тогда и только тогда, когда:

1. Каждая ФЗ в отношении R является логическим следствием функциональных зависимостей в проекциях R1 и R2;
2. Общие атрибуты проекций R1 и R2 образуют потенциальный ключ, по крайней мере, в одной из них.

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

Все ниже приведенные отношения (рис. 65) находятся в БКНФ.

R1₁ (№_зачетки, Фамилия, Группа),
R1₂ ( Группа, Факультет),
R3 ( №_зачетки, Семестр, Предмет, Оценка),
R4 (Предмет, Шифр_Преподавателя),
R5 (Семестр, Предмет, Вид_Работы),
R10 (Шифр, Фамилия, Должность),
R11 (Должность, Оклад).

Рис. 65. Состав отношений в БКНФ   (Докажите!)

Этап 3. Приведение отношения к 4НФ

В качестве примера для рассмотрения данного этапа воспользуемся отношением ИСТОРИЯ_РАБОТЫ, представленным на рисунке 58. Для данного отношения существует следующий набор многозначных зависимостей:

F₁₂:   Шифр Предмет,
F₁₃:   Шифр Год, Должность.

Согласно требованиям теории нормализации, многозначные зависимости выделяются в отдельные схемы отношений (с соблюдением общих правил декомпозиции) до тех пор, пока в полученных проекциях многозначных зависимостей не останется, при этом проецирование должно осуществляться без потерь. Напомним, что под проецированием без потерь понимается такой способ декомпозиции отношения, при котором исходное отношение полностью и без избыточности восстанавливается путем естественного (эквисоединения) соединения полученных проекций. Для нашего примера получим следующие проекции R13 (рис. 66) и R14 (рис. 67):

Шифр Предмет 01 Числ. методы 01 Теория алгор. 02 Числ. методы 02 Теория алгор.

Шифр Должность Год 01 Доцент 2000 02 Ассистент 2000 01 Доцент 2001 02 Преподаватель 2001

Рис. 66. Проекция R13

Рис. 67. Проекция R14

Проекции R13 и R14 находятся в 4НФ, так как отношения содержат только тривиальные многозначные зависимости. Исходное отношение, представленное на рис. 58, полностью восстановимо с помощью следующей конструкции языка SQL:

SELECT *
FROM   R13, R14
WHERE   R13.Шифр = R14.Шифр;

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

В качестве упражнения ответьте на вопросы:

1. Можно ли добавить кортеж вида: <02, Доцент, 2003> в отношение R14 (рис. 67)?
2. Можно ли добавить кортеж вида: <02, Схемотехника> в отношение R13 (рис. 66)?

Таким образом, схема базы данных будет состоять из следующих отношений (рис. 68):

R1₁ (№_зачетки, Фамилия, Группа),
R1₂ ( Группа, Факультет),
R3 ( №_зачетки, Семестр, Предмет, Оценка),
R4 (Предмет, Шифр_Преподавателя),
R5 (Семестр, Предмет, Вид_Работы),
R10 (Шифр, Фамилия, Должность),
R11 (Должность, Оклад).
R13 (Шифр, Предмет),
R14 (Шифр, Должность, Год).

Рис. 68. Состав отношений в 4НФ (5НФ)

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

R1₁ (№_зачетки, Фамилия, Группа),
R1₂ ( Группа, Факультет),
R3 ( №_зачетки, Семестр, Предмет, Оценка),
R4 (Предмет, Шифр_Преподавателя),
R5 (Семестр, Предмет, Вид_Работы),
R10 (Шифр_Преподавателя, Фамилия, Должность),
R11 (Должность, Оклад).
R13 (Шифр_Преподавателя, Предмет),
R14 (Шифр_Преподавателя, Должность, Год).

Рис. 69. Уточненные имена атрибутов

[ Назад  Начало раздела  Далее  Содержание]