Методические указания к выполнению лабораторных работ Омск 2006 омский государственный университет факультет компьютерных наук

Лабораторная работа № 3. Основы структурированного языка запросов (SQL)

Цель работы: получить практический опыт написания SQL запросов.

Задачи:

изучить предложенную структуру БД;
написать 5 запросов, демонстрирующих знание SQL.

Требования:

СУБД – Oracle;
инструментарий PL/SQL Developer, SQL Plus;
из 5 запросов SQL: должны иметь подзапросы не менее 2 запросов, с функциями агрегирования не менее 2 запросов.

Варианты задания по лабораторной работе № 3

Структура таблиц

ЛАБОРАТОРИИ

(Код лаборатории: Текстовый,

Наименование лаборатории: Текстовый,

Код руководителя: Текстовый,

Дата организации лаборатории: Дата,

Дата закрытия лаборатории: Дата)
СПЕЦИАЛЬНОСТИ

(Код специальности: Текстовый,

Наименование специальности: Текстовый

Дата открытия специальности: Дата,

Дата закрытия специальности: Дата)
СПИСОК СЛУЖАЩИХ

(Табельный номер: Текстовый,

Фамилия: Текстовый,

Имя: Текстовый,

Отчество: Текстовый,

Пол: Текстовый (возможные значения М, Ж),

Семейное положение: (возможные значения Ж, Х, Р, З),

Код лаборатории: Текстовый,

Телефон: Текстовый,

Код специальности: Текстовый,

Оклад: Числовой,

День рождения: Дата,

Адрес: Текстовый,

Характеристика: Текстовый)
ПРЕМИИ

(Табельный номер: Текстовый,

Размер премии: Числовой,

Номер приказа: Текстовый,

Дата приказа: Дата)
ДЕТИ СОТРУДНИКОВ

(Табельный номер: Текстовый,

Фамилия ребенка: Текстовый,

Имя ребенка: Текстовый,

Дата рождения: Дата)

Варианты возможных SQL-запросов

Список сотрудников, работающих в действующей лаборатории с минимальным размером фонда заработной платы по лаборатории.
Список всех служащих с максимального для сотрудника размера премии, если служащий не получал премий, то значение NULL.
Список руководителей действующих лабораторий с указанием числа служащих в лабораториях
Список сотрудников, работающих в действующих лабораториях, где число служащих превышает 10 человек.
Список сотрудников, работающих по специальностям, по которым число служащих не превышает 5 человек.
Список сотрудников, имеющих максимальный общий объем премий.
Создать запрос, позволяющий получить следующую информацию о сотруднике: ФИО, Дата рождения, Оклад, Надбавка (для родившихся до 1950 г. – 20% от оклада, после – 15% оклада). Данные упорядочить по полю Фамилия.
Список всех служащих с указанием количества детей, если служащий не имеет детей, то количество детей NULL.
Список сотрудников, работающих в действующей лаборатории, в которой наибольший размер средней заработной платы по лаборатории в целом.
Список руководителей лабораторий с указанием количества детей для каждого, если детей нет, то выводить NULL.
Список всех служащих с указанием размеров премий, получаемых ими, если служащий не получал премию ни разу, то размер его премии указать как NULL.
Список сотрудников, получающих оклад больше среднего по организации в целом.
Список лаборатории с указанием количества служащих в каждой.
Список действующих лабораторий с указанием объема премии, полученной каждой лабораторией.
Список руководителей лабораторий с указанием лаборатории.
Список лабораторий с указанием средней, максимальной и минимальной заработной платы по каждой лаборатории.
Найти самого молодого руководителя действующей лаборатории.
Найти самого молодого сотрудника, имеющего детей.
Найти сотрудника с максимальным объемом премии.
Список детей, у которых родители получают заработную плату ниже среднего по организации в целом.
Список сотрудников ни разу не получавших премии.
Список сотрудников имеющих более 3 детей и получающих заработную плату ниже среднего по организации в целом.
Создать запрос, позволяющий получить следующую информацию по детям: ФИО ребенка, Дата рождения, ФИО одного из родителей. Информацию выводить по детям, родившимся с 1990 по 2006 года. Данные упорядочить по полю Фамилии родителя.
Список разведенных служащих с указанием количества детей.
Список служащих с указанием суммарного размера премии сотрудника, полученного им за весь период работы, и отклонения суммарного размера премии сотрудника от максимального суммарного размера премии для сотрудников по организации в целом.

Методические рекомендации
по выполнению лабораторной работы № 1

Описание предметной области

Рассмотрим пример базы данных «Видеопрокат».

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

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

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

Выделение элементов данных по группам пользователей

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

Клиент:
1. Наименование фильма (1)
2. Продолжительность фильма (2)
3. Режиссер фильма (3)
4. Актеры, занятые в фильме (4)
5. Год выхода фильма в прокат (5)
6. Рента за сутки (10)
7. Тип носителя (9)
8. Дата выдачи носителя клиенту (12)
9. Дата возврата носителя (13)
Работник проката:
1. Наименование фильма (1)
2. Продолжительность фильма (2)
3. Режиссер фильма (3)
4. Актеры, занятые в фильме (4)
5. Год выхода фильма в прокат (5)
6. Метка носителя (7)
7. Время добавления информации о носителе (8)
8. Тип носителя (9)
9. Рента за сутки (10)
10. Дата порчи/потери носителя (11)
11. Дата выдачи носителя клиенту (12)
12. Дата возврата носителя (13)
13. ФИО клиента (14)
14. Адрес электронной почты клиента (15)
15. Контактный телефон клиента (16)

Сведем все в общий список со сквозной нумерацией:

Наименование фильма
Продолжительность фильма
Режиссер фильма
Актеры, занятые в фильме
Год выхода фильма в прокат
Идентификатор носителя
Метка носителя
Время добавления информации о носителе
Тип носителя
Рента за сутки
Дата порчи-потери носителя
Дата выдачи носителя клиенту
Дата возврата носителя
ФИО клиента
Адрес электронной почты клиента
Контактный телефон клиента

Неплохим способом проверить атомарность атрибутов является определение домена: множества значений, принимаемых атрибутом. Домен определяется типом данных и ограничениями, накладываемыми на множество возможных значений этого типа. В основном используются следующие типы данных: ЛОГИЧЕСКИЙ, ЧИСЛО, СТРОКА, ДАТА, ВРЕМЯ (современные СУБД так или иначе поддерживают все эти типы).

Атрибут №9 «Тип носителя» имеет строковый тип и содержит название типа носителя: «кассета VHS», «диск DVD» — возможно, в ходе дальнейшего технического прогресса он будет дополнен; фактически, домен этого атрибута состоит только из этих двух строк. Вообще говоря, при работе с БД необходимо иметь полный список точных значений этого атрибута в прикладной программе, что может представлять собой проблему и нарушать принцип независимости данных. Поэтому вводится новый атрибут №17 «Идентификатор типа носителя».

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

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

Определение множества функциональных зависимостей

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

Рассмотрим атрибуты, характеризующие фильм.

Наименование фильма пока никаким атрибутом однозначно не определяется. Такую ситуацию мы будем условно обозначать следующим образом: 0→1.

Рассмотрим зависимость 1 → 7 («Наименование фильма»→«Метка носителя»). Мы вынуждены отбросить ее, т.к. возможна ситуация когда существует два фильма с одинаковым названием, но записанные на разные носители (пункт проката имеет два носителя идентичного содержания). Аналогичная ситуация возникает с зависимостями, где определяющая часть это «Наименование фильма», а определяемая – остальные атрибуты характеризующие фильм.

Вообще говоря, тройка 1,3,5 («Наименование фильма», «Режиссер фильма», «Год выхода фильма в прокат») однозначно определяет фильм (одни и те же режиссеры в один год редко снимают и картины, и их «ремейки»), и, следовательно, все атрибуты, касающиеся его. В дальнейшем эта тройка будет ключевыми атрибутами.

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

Большинство существующих СУБД поддерживают такой тип, как ID (счетчик) – он автоматически обслуживается СУБД. На физическом уровне основой индексных файлов являются именно ключевые поля отношений, поэтому крайне желательно использовать как можно короткие ключи фиксированной длины. ФИО и наименования организаций для этого очевидно не подходят.

Поэтому мы введем новый элемент данных: №18 «ИДЕНТИФИКАТОР ФИЛЬМА». По этим же соображениям введем атрибут №19 «ИДЕНТИФИКАТОР КЛИЕНТА».

Теперь функциональные зависимости примут следующий вид:

«Идентификатор фильма» → «Наименование фильма» (18→1)

«Идентификатор фильма» → «Продолжительность фильма» (18→2)

«Идентификатор фильма» → «Режиссер фильма» (18→3)

«Идентификатор фильма» → «Актеры, занятые в фильме» (18→4)

«Идентификатор фильма» → «Год выхода фильма в прокат» (18→5)
Ранее уже был определен атрибут «Идентификатор носителя». Рассмотрим теперь остальные атрибуты, касающиеся носителей.

«Идентификатор носителя» → «Метка носителя» (6→7)

«Идентификатор носителя» → «Рента за сутки» (6→10)

«Идентификатор носителя» → «Время добавления информации о носителе» (6→8)

«Идентификатор носителя» → «Идентификатор типа носителя» (6→17)

«Идентификатор носителя» → «Дата порчи-потери носителя» (6→11)

Обратим внимание на зависимость «Идентификатор носителя» → «Дата порчи-потери носителя» (6→11). Не все носители повреждены или украдены, а, следовательно, эта зависимость будет выполняться не для всех носителей, и в этих случаях атрибут из правой части будет принимать NULL значение. Использование NULL требует поддержки данного типа со стороны СУБД, а также, например, учета правил непривычной трехзначной логики при построении запросов.

Замечание1. Если предполагается, что неопределенные значения будут встречаться относительно редко (например, для документальных фильмов нельзя указать список актеров), то использование NULL-значений оправдано. В обратной ситуации, когда большинство записей будут содержать именно NULL-значение (именно дата порчи/потери носителя), целесообразно использовать понятие области определения функциональной зависимости.
Рассмотрим остальные зависимости.

«Идентификатор типа носителя» → «Тип носителя» (17→9)

Для этой зависимости и вводился атрибут «Идентификатор типа носителя».
«Идентификатор клиента» → «ФИО клиента» (19→14)

«Идентификатор клиента» → «Адрес электронной почты клиента» (19→15)

«Идентификатор клиента» → «Контактный телефон клиента» (19→16)
Объект выдача носителя клиенту характеризуется всеми своими атрибутами: носителем, клиентом, датой выдачи. По вышеописанным причинам необходимо ввести еще один атрибут для внутреннего пользования: №20 «ИДЕНТИФИКАТОР ВЫДАЧИ».

«Идентификатор выдачи» → «Идентификатор носителя» (20→6)

«Идентификатор выдачи» → «Идентификатор клиента» (20→19)

«Идентификатор выдачи» → «Дата выдачи носителя клиенту» (20→12)

«Идентификатор выдачи» → «Дата возврата носителя» (20→13)
Поскольку носитель каждый конкретный раз выдается только одному клиенту, это необходимо отразить в функциональных зависимостях:

«Идентификатор выдачи» → «ФИО Клиента», «Адрес электронной почты клиента», «Контактный телефон клиента»
Выше, при неформальном описании области отмечалась несколько нетривиальная связь фильма и носителя. Например, нельзя выделить зависимости «Идентификатор фильма» → «Идентификатор носителя» (18→6) и «Идентификатор носителя» → «Идентификатор фильма» (6→18): один фильм может располагаться на нескольких носителях, равно как и на одном носителе может быть несколько фильмов. Поэтому имеет место некоторое распределение фильмов по носителям.

Замечание 2. Распределение задается парой атрибутов «Идентификатор носителя», «Идентификатор фильма» (6, 18). Поскольку на один носитель два одинаковых фильма не пишут, эта пара также является составным ключом, и в дополнительном идентификаторе необходимости не возникает. Для фильмов, занимающих несколько носителей, необходимо хранить порядковый номер разбиения (например, номер части). Однако столь длинные картины все же редкость для кинопроката вообще, а, следовательно, для большинства пар фильм-носитель значение атрибута «Номер тома» будет равно одному и тому же, например, единице. Целесообразно следующее решение: ввести искусственный «Идентификатор распределения» (атрибут №21) и атрибут №22 «Номер тома».

Тогда получаем следующие зависимости:

«Идентификатор распределения» → «Идентификатор носителя» (21→6)

«Идентификатор распределения» → «Идентификатор фильма» (21→18)

«Идентификатор распределения» → «Номер тома» (21→22)

Построение минимального покрытия

Для построения минимального покрытия необходимо применить алгоритм его построения.

Первым шагом является преобразование множества зависимостей ко множеству зависимостей с определяемой частью из одного атрибута. На основании правила декомпозиции, зависимость вида X → A₁A₂…A_k разбивается на множество зависимостей X → A₁, X → A_s, … X → A_k. В нашем случае подлежащей такому преобразованию является зависимость

«Идентификатор выдачи» → «ФИО Клиента», «Адрес электронной почты клиента», «Контактный телефон клиента».

По правилу декомпозиции получаем:

«Идентификатор выдачи» → «ФИО Клиента»;

«Идентификатор выдачи» → «Адрес электронной почты клиента»;

«Идентификатор выдачи» → «Контактный телефон клиента».

Второй шаг алгоритма заключается в удалении избыточных зависимостей. Зависимость X→Y удаляется из множества зависимостей F, если множество F \ {X→Y} эквивалентно исходному множеству F.

Для проверки эквивалентности множеств функциональных зависимостей используется понятие замыкания множества атрибутов. Замыканием X⁺ множества атрибутов X зовется множество атрибутов {A_i} таких, что X→A_i  F⁺, где F⁺ – замыкание множества функциональных зависимостей.

Существует алгоритм проверки эквивалентности двух множеств функциональных зависимостей F и G. Если для каждой зависимости X→Y  F выполнено Y  X⁺_G (X⁺_G – замыкание X, построенное для множества зависимостей G), то имеет место F  G. Для проверки G  F используется симметричное утверждение. Если одновременно выполнены F  G и G  F, то множества эквивалентны.

Возвращаясь ко второму шагу алгоритма, существует утверждение, что для проверки эквивалентности указанных множеств (F \ {X→Y} и F) достаточно построить X⁺_G, где G = F \ {X→Y}, и проверить выполнение Y  X⁺_G. Если утверждение истинно, то множества F и G эквивалентны, и зависимость может быть удалена.

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

Рассмотрим зависимость: «Идентификатор выдачи» → «Идентификатор клиента».

Для построения замыкания X⁺множества атрибутов X применяется свой алгоритм (X⁽ⁱ⁾ – замыкание, построенное на очередном шаге):

Шаг 0. X⁽⁰⁾ := X;

Шаг i. Пусть в результате последовательного просмотра множества функциональных зависимостей для зависимости Y→Z выполнено Y  X⁽ⁱ^-1). Тогда X⁽ⁱ⁾ := X^(i-1)  {Z}.

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

Построим замыкание атрибута «Идентификатор выдачи» для множества зависимостей G:

«Идентификатор выдачи»⁺ = {

«Идентификатор выдачи»,
«Идентификатор носителя», «Дата выдачи носителя клиенту», «Дата возврата носителя», «ФИО клиента», «Адрес электронной почты клиента», «Контактный телефон клиента»,
«Метка носителя», «Время добавления информации о носителе», «Идентификатор типа носителя», «Дата порчи-потери носителя», «Рента за сутки»,
«Тип носителя» }

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

Как можно заметить, атрибут «Идентификатор клиента» в построенное замыкание не входит. Зависимость остается.

Рассмотрим зависимость

«Идентификатор выдачи» → «ФИО Клиента»

Построим замыкание атрибута «Идентификатор выдачи» для множества зависимостей без данной:

«Идентификатор выдачи»⁺ = {

«Идентификатор выдачи»,
«Идентификатор носителя», «Идентификатор клиента», «Дата выдачи носителя клиенту», «Дата возврата носителя»,
«Метка носителя», «Время добавления информации о носителе», «Идентификатор типа носителя», «Дата порчи-потери носителя», «ФИО клиента», «Адрес электронной почты клиента», «Рента за сутки», «Контактный телефон клиента»,
«Тип носителя» }

Здесь, несмотря на отсутствие в G зависимости «Идентификатор выдачи» → «ФИО Клиента», атрибут «ФИО Клиента» появляется в построенном замыкании. Данная зависимость избыточная и должна быть удалена.

Аналогичная проверка приводит к удалению следующих зависимостей:

«Идентификатор выдачи» → «Адрес электронной почты клиента»;

«Идентификатор выдачи» → «Контактный телефон клиента».

На третьем шаге алгоритма происходит удаление избыточных атрибутов из определяющих частей. На этом шаге выполняется последовательный просмотр множества зависимостей. Для каждой зависимости X→Y, где X = X₁X₂…X_k, выполняется следующая проверка. Из X удаляется очередной атрибут X_i, после чего проверяется эквивалентность F \ {X→Y}  {X₁X₂…X_i_-1X_i₊₁…X_n→Y} и F. Если эквивалентность имеется, то атрибут не возвращается.

Как и на втором шаге, проверка эквивалентности сводится к построению одного замыкания: (X₁X₂…X_i_-1X_i₊₁…X_n)⁺_F — и проверки Y на принадлежность ему (в силу первого шага это один атрибут). Принадлежность означает эквивалентность множеств, и в этом случае атрибут не возвращается.

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

Построение канонической модели реляционного типа

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

Рассмотрим группу зависимостей, определяемых «Идентификатором фильма»:

«Идентификатор фильма» → «Наименование фильма» (21→1)

«Идентификатор фильма» → «Продолжительность фильма» (21→2)

«Идентификатор фильма» → «Режиссер фильма» (21→3)

«Идентификатор фильма» → «Качество фильма» (21→4)

«Идентификатор фильма» → «Актеры, занятые в фильме» (21→5)

«Идентификатор фильма» → «Год выхода фильма в прокат» (21→6)

По ним строится следующее отношение:

Фильмы (Идентификатор фильма, Наименование фильма, Продолжительность фильма, Режиссер фильма, Качество фильма; Актеры, занятые в фильме; Год выхода фильма в прокат)

Аналогично формируются остальные отношения.

В итоге получаем следующую структуру записей:

R1 – Фильмы (Идентификатор фильма, Наименование фильма, Продолжительность фильма, Режиссер фильма, Актеры, занятые в фильме; Год выхода фильма в прокат)
R2 – Многотомные распределения (Идентификатор распределения, Номер тома)
R3 – Распределение фильмов (Идентификатор распределения, Идентификатор фильма, Идентификатор носителя)
R4 – Носители (Идентификатор носителя, Метка носителя, Рента за сутки, Время добавления информации о носителе, Идентификатор типа носителя)
R5 – Испорченные носители (Идентификатор носителя, Дата порчи-потери носителя)
R6 – Справочник типов носителей (Идентификатор типа носителя, Тип носителя)
R7 – Клиенты (Идентификатор клиента, ФИО клиента, Адрес электронной почты клиенты, Контактный телефон клиента)
R8 – Выдачи (Идентификатор выдачи, Идентификатор носителя, Идентификатор клиента, Дата выдачи носителя клиенту)
R9 – Возвраты (Идентификатор выдачи, Дата возврата носителя)

Проверка выполнения свойства соединения без потерь информации

+₍₁₎

+₍₁₁₎

+₍₉₎

+₍₁₂₎

+₍₁₄₎

+₍₁₂₎

+₍₁₃₎

+₍₁₂₎

+₍₉₎

+₍₁₎

+₍₂₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₄₎

+₍₃₎

+₍₁₎

+₍₁₀₎

+₍₁₎

+₍₅₎

+₍₁₎

+₍₄₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₁₎

+₍₃₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₄₎

+₍₁₎

+₍₈₎

+₍₆₎

+₍₃₎

+₍₁₎

+₍₇₎

+₍₁₂₎

+₍₁₄₎

+₍₁₂₎

+₍₁₃₎

+₍₇₎

+₍₁₎

+₍₁₅₎

+₍₁₂₎

+₍₇₎

+₍₁₎

Шаг 1: Строится таблица, из 9 строк и 22 столбцов. На пересечении i-ой строки и j-го столбца ставится «+₍_k₎», где k – номер шага, если атрибут с соответствующим номером принадлежит заданному отношению. Ключевые атрибуты отношений выделены «фоном».

Шаг 2: Просматриваем список функциональных зависимостей.

Рассмотрим зависимость 18→1, а также и все остальные с определяющим атрибутом 18 (18→2, 3, 4, 5). В строке, соответствующей R3, также присутствует атрибут 18. Поэтому мы доставляем в этой строке «2» в те колонки, которые соответствуют атрибутам из определяемой части зависимостей.

Шаг 3: Рассматриваем зависимости 6→7, 8, 10, 17

Шаг 4: Рассматриваем зависимости 6→11

Шаг 5: Рассматриваем зависимости 17→9

Шаг 6: Рассматриваем зависимости 19→14, 15, 16

Шаг 7: Рассматриваем зависимости 20→6, 12, 19

Шаг 8: Рассматриваем зависимости 20→13

Шаг 9: Рассматриваем зависимости 21→6,18

Шаг 10: Рассматриваем зависимости 21→22

Шаг 11: Рассматриваем зависимости 18→1, 2, 3, 4, 5

Шаг 12: Рассматриваем зависимости 6→7, 8, 10, 17

Шаг 13: Рассматриваем зависимости 6→11

Шаг 14: Рассматриваем зависимости 17→9

Шаг 15: Рассматриваем зависимости 19→14, 15, 16

Шаг 16: Рассматриваем зависимости 20→6, 12, 19

Шаг 17: Рассматриваем зависимости 20→13

Шаг 18: Рассматриваем зависимости 21→6,18

Шаг 19: Рассматриваем зависимости 21→22

Уже на 16-м шаге не происходит каких-либо изменений. В тоже время строка, состоящая из ‘+’, не была получена, а это значит, что свойство соединения без потерь информации не выполнено.
Составим обобщенный ключ: «Идентификатор фильма», «Идентификатор распределения», «Идентификатор носителя», «Идентификатор типа носителя», «Идентификатор клиента», «Идентификатор выдачи» (18, 21, 6, 19, 20). Из обобщенного ключа удалим те атрибуты, которые функционально выводятся из остающихся, и получим следующее отношение: «Идентификатор распределения» и «Идентификатор выдачи».

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

Чтобы решить проблему многозначной зависимости, вернем в обобщенный ключ атрибут «Идентификатор носителя». Тогда в явном виде имеется многозначная зависимость: «Идентификатор носителя» →→ «Идентификатор выдачи» («Идентификатор распределения»).

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

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

+₍₁₎

+₍₁₁₎

+₍₉₎

+₍₁₂₎

+₍₁₄₎

+₍₁₂₎

+₍₁₃₎

+₍₁₂₎

+₍₉₎

+₍₁₎

+₍₂₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₄₎

+₍₃₎

+₍₁₎

+₍₁₀₎

+₍₁₎

+₍₅₎

+₍₁₎

+₍₄₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₁₎

+₍₃₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₄₎

+₍₁₎

+₍₈₎

+₍₆₎

+₍₃₎

+₍₁₎

+₍₇₎

+₍₁₂₎

+₍₁₄₎

+₍₁₂₎

+₍₁₃₎

+₍₇₎

+₍₁₎

+₍₁₅₎

+₍₁₂₎

+₍₇₎

+₍₁₎

R3 ∞ R8

+₍₂₎

+₍₁₎

+₍₃₎

+₍₅₎

+₍₃₎

+₍₄₎

+₍₁₎

+₍₈₎

+₍₆₎

+₍₃₎

+₍₁₎

+₍₁₀₎

Как видно из таблицы, уже на 10-м шаге была получена строка, состоящая из “+”. Таким образом, после добавления обобщенного ключа, наша схема обладает свойством соединения без потерь информации.

Модификация структуры данных БД

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

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

Введем новые элементы данных «Другое название фильма» и «Идентификатор другого названия фильма». Для них выпишем функциональные зависимости

«Идентификатор другого названия фильма» → «Другое название фильма»;

«Идентификатор другого названия фильма» → «Идентификатор фильма».

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

R10 – Другие названия фильмов (Идентификатор другого названия фильма, Другое название фильма, Идентификатор фильма)

Для новой схемы обобщенный ключ имеет вид: «Идентификатор другого названия фильма», «Идентификатор распределения», «Идентификатор выдачи». В нем, как и прежде, неявно присутствует многозначная зависимость, борьба с которой рассмотрена выше. После некоторой практики не составляет труда заметить еще одну, столь же неявную зависимость: «Идентификатор фильма» →→ «Идентификатор другого названия фильма» («Идентификатор распределения»). Избавление от нее происходит теми же средствами. Получаем два отношения: («Идентификатор фильма», «Идентификатор другого названия фильма») и («Идентификатор фильма», «Идентификатор распределения», «Идентификатор выдачи»). Первое отношение уже присутствует в схеме в виде отношения «Другие названия фильмов». Второе отношение и его декомпозиция рассматривались выше. Схема удовлетворяет свойству соединения без потерь информации.

Операции реляционной алгебры

Операндами реляционной алгебры являются отношения R_i, содержащие k_i столбцов и n_i строк.

Базисный набор операций

1. Объединение. R = R₁  R₁. Ограничения: k₁=k₂ (формально) и заголовки должны быть равны (содержательно). Результирующее отношение содержит картежи обоих операндов, исключая дублирующие друг друга.

2. Вычитание. R = R₁ \ R₂. Ограничения: k₁=k₂ (формально) и заголовки должны быть равны (содержательно). Из R₁ удаляются картежи, встречающиеся в R₂.

3. Декартово произведение. R = R₁  R₂. Ограничений нет. Получаемое отношение имеет все атрибуты своих операндов (при этом одноименные заменяются на пары вида <Имя отношения>.<Имя атрибута>), и каждый картеж первого отношения сопоставляется каждому картежу второго отношения. Появление дублирующих картежей исключено – если их нет в исходных отношениях.

4. Селекция. _F(R), где F – логическое выражение, заданное на атрибутах из R и имеет вид: <атрибут><значение>, где  – операция из набора {<, ≤,=, ≠, ≥ ,>}, связанных посредством операций

(дизъюнкция),

(конъюнкция), (отрицание). Картеж из R попадает в результат, если при подстановке его содержимого в F получается истинное выражение. Проекция – это вырезка из таблицы по строкам, соответствующим логическому выражению F.

5. Проекция. _X(R), где X – множество атрибутов, подмножество атрибутов из R. Результатом является отношение, состоящее из атрибутов X, содержимым которого будут соответствующие части картежей операнда с удаленными дублями. Проекция – это вырезка из таблицы по столбцам.

Дополнительные операции

1. Пересечение.

.

2. Соединение.

.

3. Эквисоединение. Соединение, только F содержит выражения со знаком равенства.

4. Естественное соединение. Каждый картеж из R₁ сопоставляется с каждым картежом из R₂, и если значения всех одноименных атрибутов совпадают, то формируется новый картеж без дублирующихся атрибутов – он-то и помещается в результат. Обозначается он