Лекция 8

организация данных

проектирование приводит к разработке схемы БД, то есть совокупности схем

схемы. Процесс получения требуемой схемы состоит из следующих шагов:
1) Каждая

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

называемые функциональные зависимости между атрибутами БД. Некоторые зависимости между атрибутами

применяется процесс нормализация отношений.
Теория нормализации отношений основана на анализе функциональных

нормализации схем отношений, при этом каждая последующая итерация соответствует нормальной

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

следующая последовательность нормальных форм:
первая нормальная форма (НФ1);
вторая

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

исходной БД может быть получено путем естественного соединения отношений, входящих

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

зависит от атрибута A
(A и B могут быть составными),

Определение 2. Полная функциональная зависимость.
Атрибут (набор атрибутов) B полностью зависит от другого набора атрибутов A отношения R, если B функционально зависит от всего множества A, но не зависит ни от какого подмножества A.
т.е. для любого А1, являющегося подмножеством A, R.B функционально не зависит от R.A, в противном случае зависимость R.A -> R.B называется неполной.

называется транзитивной, если в отношении R существует такой атрибут B,

полностью и однозначно (функционально полно) определяет значения всех остальных атрибутов

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

Определение 6. Если в отношении существует несколько функциональных зависимостей, то каждый атрибут или набор атрибутов, от которого зависит другой атрибут, называтся детерминантом отношения.

только тогда, когда па пересечении каждого столбца и каждой строки

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

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

только тогда, когда оно находится в первой нормальной форме и

Если в отношении R ключ содержит один атрибут, то это отношение уже задано в НФ-2.

может быть (НомерЗач , Дисциплина)
С другой стороны, атрибуты ФИО и

(ФИО, НомерЗач, Группа)

(НомерЗач, Дисциплина, Оценка)

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

форме? ФИО, НомерЗач, Группа, Дисциплина, Оценка)

Ситуация - студент переведен из одной

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

КОНСУЛЬТАЦИИ (Таб_Ном_преп, Ном_зач_кн, Дата, Время, Аудитория, Вместимость)

отношение содержит транзитивную зависимость: (Таб_Ном_преп, Ном_зач_кн, Дата) → Аудитория → Вместимость.

Определение 3. Транзитивная функциональная зависимость.
Функциональная зависимость R.A  R.C называется транзитивной, если в отношении R существует такой атрибут B, что имеют место зависимости R.A  R.B и R.B  R.C.

транзитивную зависимость: (Таб_Ном_преп, Ном_зач_кн, Дата) → Аудитория → Вместимость.
Следовательно, это отношение

транзитивную зависимость: (Таб_Ном_преп, Ном_зач_кн, Дата) → Аудитория → Вместимость.
Для устранения транзитивной

КОНСУЛЬТАЦИИ (Таб_Ном_преп, Ном_зач_кн, Дата, Время, Аудитория) АУДИТОРИЯ (Аудитория, Вместимость)

любая БД должна находиться как минимум в НФ-3. На практике третья

находится в третьей нормальной форме и каждый Детерминант отношения является

Определение 6. Если в отношении существует несколько функциональных зависимостей, то каждый атрибут или набор атрибутов, от которого зависит другой атрибут, называтся детерминантом отношения.

Определение для НФ-3 было дано Коддом для ситуаций с упрощающим картину допущением того, что отношение имеет только один потенциальный ключ, который, естественно, и является первичным ключом. Естественно, что не все отношения могут быть уложены в данные довольно жесткие рамки. Более обобщающими являются случаи, когда в наличии имеются следующие условия:
ƒ отношение имеет два (или более) потенциальных ключа;
ƒ два потенциальных ключа являются составными;
ƒ два потенциальных ключа перекрываются, т. е. имеют, по крайней мере, один общий атрибут.

ключа: (Индекс поставщ, Индекс_товара); (Имя_поставщ, Иидекс_товара).
Отношение находится в нормальной форме Бойса—Кодда, если

ПОСТАВКА (Индекс_поставщ, Имя_поставщ, Индекс_товара, Колич_товара).

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

находящейся в НФБК, можно провести декомпозицию в схему БД в НФБК.

Первый вариант: если учитывается зависимость Индекс_поставщ → Имя_поставщ, в результате чего имеем следующих два отношения: ПОСТАВКА (Иидекс_поставщ, Индекс_товара, Колич_товара); ПОСТАВЩИК (Индекс_поставщ, Имя_поставщ),

Второй вариант исходит из зависимости Имя _поставщ → Индекс_поставщ
ПОСТАВКА (Имя_поставщ, Индекс_товара, Колич_товара); ПОСТАВЩИК (Индекс_поставщ, Имя_поставщ).

зависимость (multi valid dependence, MVD) R.A → R,B в том

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

Пусть дано отношение, которое .моделирует предстоящую сдачу экзаменов на сессии. Допустим, оно имеет вид:

(НомерЗач , Группа, Дисциплина)

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

две многозначные зависимости: Группа -» Дисциплина Группа -» НомерЗач,
В теории реляционных баз

ТЕОРЕМА ФЕЙДЖИНА
Отношение R (А, В, С) можно спроецировать без потерь в отношения R1 (А, В) и R2 (А, С) в том и только в том случае, когда существует MVD A -» В | С
( что равнозначно наличию двух зависимостей A -» В и A -» С),

Проецирование без потерь, значит исходное отношение полностью восстанавливается и без избыточности

том и только и том случае, если в случае существования

(НомерЗач , Группа, Дисциплина)

В нашем примере можно произнести декомпозицию исходного отношения в два отношения: (НомерЗач , Группа) (Группа, Дисциплина)

зависимости соединения (X, Y Z) в том и только в

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

Важно понимать, что зависимость соединения определяется не для конкретного значения переменной отношения в тот или иной момент времени, а по всем возможным значениям. Поэтому понятие зависимости соединения определено не для отношения (конкретного значения), а для переменной отношения.

проекционно-соединительной нормальной форме, в том и только в том случае,

том и только в том случае, когда любая зависимость соединения

Рассмотрим отношение R1:

R1 (Преподаватель, Кафедра, Дисциплина)

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

Введем следующие обозначения наборов атрибутов: ПК (Преподаватель, Кафедра)
ПД (Преподаватель. Дисциплина) КД (Кафедра, Дисциплина)

ПД, КД). Тогда отношение R1 не находится в НФ5, потому

R2 (Преподаватель. Кафедра) R3 (Преподаватель. Дисциплина) R4 (Кафедра, Дисциплина)

большей степени она является теоретическим исследованием. Очень тяжело определить само

операция удаления записей — ей соответствует оператор DELETE
операция добавления или

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

столбцов>) ] VALUES ()

Подобный синтаксис позволяет ввести только одну

INSERT INTO BOOKS (ISBN,TITL,AUTOR,CQAUTOR,YEARIZD,PAGES) VALUES ("5-88782-290-2","Аппаратные средства IBM PC. Энциклопедия “Гук М“,””,2000,816)

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

INTO BOOKS VALUES ("5-88782-290-2","Аппаратные средства IBM PC. Энциклопедия “Гук М“,””,2000,816)
неполный перечень значений
INSERT

Столбцу COAUTOR будет присвоено в этом случае значение NULL.

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

таблицы в соответствии с условиями, которые задаются для удаляемых строк.

Синтаксис

Например, если нам надо удалить результаты прошедшей сессии, то мы можем удалить все строки из отношения R1 командой DELETE FROM R1

и условия фильтрации в опе- раторе SELECT. Эти условия определяют, какие

надо исключить неуспевающих студентов, в условиях отбора надо найти студентов,

DELETE FROM R2 WHERE R2.ФИО IN (SELECT R1.ФИО FROM Rl WHERE Оценка = 2 OR Оценка IS NULL GROUP BY Rl.ФИО HAVING COUNT(*) >= 2)

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

внешнем мире и их надо адекватно отразить в базе данных
UPDATE

Например, студент Степанова К. Е. пересдала экзамен по дисциплине «Базы данных» с двойки на четверку. В этом случае нам надо выполнить соответствующую корректировку таблицы R1. Операция обновления имеет следующий формат;

Если условие отбора не задается, то операция модификации будет применена ко всем строкам таблицы.

R1.Дисциплина = "Базы данных"
Например, студент Степанова К. Е. пересдала экзамен

учебною базу данных еще одним отношением, которое содержит перечень курсов,

В этом случае перевод па следующий курс можно выполнить следующей операцией обновления: UPDATE R4 SET R4.Kypc = R4.Kypc + 1

сложные подзапросы. Расширим нашу базу еще одним отношением, которое будет

+ 50% к основной, наличие двух пятерок из сданных экзаменов

сессии признаком повышенной стипендии, + 50% к основной
наличие двух пятерок

Назначение повышенной
стипендии:
UPDATE R5 SET R5.Стипендия = 50% WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1 WHERE R1.Оценка = 5 GROUP BY R1.ФИО HAVING COUNT(*) =3 )

Назначение стипендии с надбавкой 25%:
UPDATE R5 SET R5.Стипендия = 25% WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1 WHERE R1.Оценка=5 AND R1.ФИО NOT IN
(SELECT A.ФИО
FROM R1 A
WHERE А.Оценка <= 3
OR А.Оценка IS NULL) GROUP BY R1.ФИО HAVING COUNT(*) >=2 )

(SELECT R1.ФИО FROM R1 WHERE R1.Оценка >=4 AND

Снятие стипендии: UPDATE R5 SET R5.Стипендия = -100% WHERE R5.ФИО IN (SELECT R1.ФИО FROM R1 WHERE R1.Оценка<=3
OR R1.ОЦЕНКА IS NULL )

Для сессии в которой 3 экзамена.
наличие трех пятерок по сессии признаком повышенной стипендии, + 50% к основной
наличие двух пятерок из сданных экзаменов и отсутствие двоек и троек на сданных экзаменах - признаком повышения стипендии на25%,
наличие хотя бы одной тройки среди сданных экзаменов - признаком снятия или отсутствия стипендии вообще, то есть -100% надбавки.
При отсутствии троек назначим обычную стипендию с надбавкой 0%

любом количестве сданных экзаменов.
Назначение повышенной стипендии для сессии из трех

В конечном счете нам все равно надо знать, сколько экзаменов должен сдавать каждый конкретный студент, поэтому сначала сосчитаем количество экзаменов, которые должна сдавать группа, в которой учится этот студент

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

SELECT R3.Группа, Число_экзаменов = COUNT(*) FROM R3 GROUP BY R3.Группа

SELECT COUNT(*) FROM R3 WHERE R2.Группа = R3,Группа GROUP BY R3.Группа

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

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

SELECT R1.ФИО FROM R1.R2 WHERE R1. ФИО = R2.ФИО AND R1.Оценка = 5 GROUP BY R1.ФИО HAVING COUNT(*) = (SELECT COUNT(*) FROM R3 WHERE R2.Группа = R3.Группа
GROUP BY R3.Группа)

на сессии, на новый универсальный запрос:
UPDATE R5 SET R5.Стипендия = 50% WHERE