Дополнительные операции реляционной алгебры

 

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

1. Переименование ([RENAME] AS) – операция, не имеющая самостоятельного применения, а использующаяся внутри других операций для переименования полей, включаемых в результат их выполнения. Например, реляционное выражение: АУДИТОРИЯ RENAME Вместимость AS КолМест возвращает таблицу АУДИТОРИЯ, в которой поле Вместимость переименовано в поле КолМест.

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

2. Расширение (EXTENDED ADD) – операция, расширяющая список полей результирующей таблицы полями, не хранящимися в исходной таблице, а вычисляемыми в данной операции. Расширение позволяет выполнить различные скалярные вычисления и включить их результат в список полей результирующего множества основной реляционной операции. Операцию расширения можно назвать также операцией вычисления по горизонтали. Например, чтобы вычислить процент коммерческих студентов в каждой группе, применяем операцию расширения к таблице ГРУППА.

EXTENDED ГРУППА ADD (КоличКоммерчСтуд / КоличСтудентов * 100) AS ПроцКомм.

3. Подведение итогов (GROUP BY) – операция, предназначенная для вычисления некоторой агрегатной функции для каждой группы записей результирующего множества. К типичным агрегатным функциям относятся такие функции, как Сумма ( SUM), Количество (COUNT), Среднее (AVG), Минимум (MIN), Максимум (MAX) и другие. Подведение итогов позволяет вычислить значение какой-либо агрегатной функции как для всех записей результирующей таблицы, так и для отдельных групп записей, объединенных по одному и тому же значению какого-либо поля или группы полей или выражению, построенному на совокупности полей. Операцию подведения итогов можно назвать также операцией вычисления по вертикали. Например, чтобы вычислить количество студентов – мужчин на каждом факультете, требуется выполнить следующий запрос с применением операции подведения итогов.

GROUP ГРУППА BY (ШифрФак) ADD SUM(КоличМуж) AS КолРебят.

В данном примере все записи таблицы ГРУППА с одинаковым значением поля ШифрФак образуют одну запись результата, в которую помимо поля ШифрФак добавляется также поле с результатом вычисления общего количества студентов – мужчин (агрегатная функция SUM). Очевидно, что операция подведения итогов содержит в себе и операцию расширения (ADD), и операцию переименования (AS).

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

4. Реляционное присваивание (:=) – операция, предназначенная для запоминания результата запроса в постоянной таблице для последующего ее применения в других операциях. Например, чтобы создать постоянную таблицу со списком дисциплин, изучаемых группой 243, следует выполнить следующий запрос с сохранением результата в таблице ДИСЦ_ГР_243:

ДИСЦ_ГР_243:= ((РАСПИСАНИЕ where НомерГруппы = 243) join ДИСЦИПЛИНА) [НомерГруппы, НазваниеДисц].

В общем случае операцию присваивания можно представить в виде: Приемник:= Источник.

5. Добавление (INSERT) – операция, предназначенная для добавления записей Источника в указанную таблицу Приемник: INSERT ИСТОЧНИК INTO ПРИЕМНИК. Например, добавим к таблице-результату предыдущего примера ДИСЦ_ГР_243 еще одну дисциплину:

INSERT {243, 'Структурное программирование'} INTO ДИСЦ_ГР_243; или

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

INSERT ((РАСПИСАНИЕ where НомерГруппы = 244) join ДИСЦИПЛИНА) [НомерГруппы, НазваниеДисц] INTO ДИСЦ_ГР_243.

Очевидно, что операция добавления может быть реализована с помощью операции объединения (UNION).

6. Изменение (UPDATE) – операция, предназначенная для обновления данных в указанных записях таблицы Приемника: UPDATE ПРИЕМНИК SET (Список выражений присваивания). Например, заменим в расписании аудиторию 310 на 110:

UPDATE РАСПИСАНИЕ where НомерАуд = 310 SET (НомерАуд = 110).

7. Удаление (DELETE) – операция, предназначенная для удаления записей из таблицы Приемника: DELETE ПРИЕМНИК. Например, удалим из расписания все занятия, проводимые в аудитории 410:

DELETE РАСПИСАНИЕ where НомерАуд = '410'.

Очевидно, что операция удаления может быть реализована с помощью операции вычитания (MINUS).

 

Примеры запросов на языке реляционной алгебры

 

Рассмотрим ряд примеров записи запросов к базе данных РАСПИСАНИЕ на языке реляционной алгебры.

1. Получить список названий дисциплин, изучаемых на специальности 2204:

((РАСПИСАНИЕ JOIN ДИСЦИПЛИНА) WHERE ШифрСпец = '2204') [НазваниеДисц] или

p _{НазваниеД} (s _{ШифрСпец = '2204'} (РАСПИСАНИЕ «ДИСЦИПЛИНА)).

В этом запросе выполняются следующие операции:

· соединение таблиц РАСПИСАНИЕ и ДИСЦИПЛИНА по полю ШифрДисц, так как в таблице РАСПИСАНИЕ нет поля НазваниеДисц;

· затем над результатом соединения выполняется операция селекции, т.е. отбираются только те записи, поле ШифрСпец которых имеет значение 2204;

· и, наконец, выполняется операция проекции, которая оставляет в результирующем множестве записей единственное поле НазваниеДисц.

2. Получить список аудиторий, свободных в понедельник в 15 ч. 20 мин. и вмещающих не менее 100 человек:

((АУДИТОРИЯ where Вместимость >=100) [НомерАуд]) except ((((РАСПИСАНИЕ join ЗАНЯТИЕ) join ПАРА) where ВремяНач = '15:20' and НомерДня = 1) [НомерАуд]).

В этом запросе выполняются следующие операции:

· селекция из таблицы АУДИТОРИЯ тех записей, поле Вместимость которых имеет значение не менее 100;

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

· соединение таблицы РАСПИСАНИЕ с таблицей ЗАНЯТИЕ по полю НомерЗан, в результате чего получается список всех строк расписания, в которых присутствуют поля НомерПары и НомерДня;

· соединение предыдущего результата с таблицей ПАРА по полю НомерПары, в результате чего получается множество строк расписания с полем ВремяНач;

· селекция предыдущего результата, в результате которой из него исключаются все строки, не удовлетворяющие предикату ВремяНач = '15:20' and НомерДня = 1, то есть список занятий в указанное время;

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

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

3. Получить список номеров и старост групп, занятия которых проходят хотя бы раз в заданной аудитории, например 346:

((ГРУППА join РАСПИСАНИЕ) where НомерАуд = '346') [НомерГруппы, Староста].

В этом запросе выполняются следующие операции:

· соединение таблицы ГРУППА с таблицей РАСПИСАНИЕ, в результате которого получается множество строк занятий, расширенных полями из таблицы ГРУППА;

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

· проекция предыдущего результата на поля НомерГруппы и Староста, таким образом, получается искомое множество групп и их старост.

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

((((((ПРЕПОДАВАТЕЛЬ where ИмяПрепод = 'Макаров Н.П.') [ШифрПрепод]) join РАСПИСАНИЕ) join ЗАНЯТИЕ) join ПАРА) join ДЕНЬ) [НазваниеДня, ВремяНач, НомерАуд, НомерГруппы].

В этом запросе выполняются следующие операции:

· селекция таблицы ПРЕПОДАВАТЕЛЬ по фамилии преподавателя, в результате чего выбирается одна запись, соответствующая заданной фамилии (если нет однофамильцев);

· проекция предыдущего результата на поле ЩифрПрепод, в результате получается единственное значение шифра преподавателя;

· последовательное соединение предыдущего результата с таблицами РАСПИСАНИЕ по полю ШифрПрепод, ЗАНЯТИЕ по полю НомерЗан, ПАРА по полю НомерПары, ДЕНЬ по полю НомерДня, в результате получается множество строк расписания занятий для заданного преподавателя;

· проекция предыдущего результата на указанные в запросе поля.

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

(((((РАСПИСАНИЕ join ВИДЗАН) where НазваниеВидаЗан = 'Лекции')[НомерАуд, ШифрПрепод, ШифрДисц]) join ПРЕПОДАВАТЕЛЬ) join ДИСЦИПЛИНА)[НомерАуд, ИмяПрепод, НазваниеДисц].

В этом запросе выполняются следующие операции:

· соединение таблицы РАСПИСАНИЕ с таблицей ВИДЗАН по полю ШифрВидЗан, в результате получается множество строк расписания с добавленными полями таблицы ВИДЗАН;

· селекция предыдущего результата по значению поля НазваниеВидаЗан, равного 'Лекции', в результате получается множество лекционных занятий;

· проекция предыдущего результата на поля, требующиеся на выходе запроса, то есть НомерАуд, ШифрПрепод, ШифрДисц;

· соединение проекции с таблицами ПРЕПОДАВАТЕЛЬ и ДИСЦИПЛИНА соответственно по полям ШифрПрепод и ШифрДисц с целью получения доступа к полям ИмяПрепод и НазваниеДисц;

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

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

((((РАСПИСАНИЕ join (ВИДЗАН where НазваниеВидаЗан = 'Лекции'))[НомерАуд, ШифрПрепод, ШифрДисц]) join ПРЕПОДАВАТЕЛЬ) join ДИСЦИПЛИНА)[НомерАуд, ИмяПрепод, НазваниеДисц].

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

7. Подсчитать общее количество часов в расписании для каждого преподавателя:

GROUP ((РАСПИСАНИЕ join ПРЕПОДАВАТЕЛЬ) [ИмяПрепод]) BY (ИмяПрепод) ADD COUNT(*)*2 AS ВсегоЧасов.

В этом запросе выполняются следующие операции:

· соединение таблицы РАСПИСАНИЕ с таблицей ПРЕПОДАВАТЕЛЬ по полю ШифрПрепод;

· проекция предыдущего результата на поле ИмяПрепод;

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

Таким образом, получается таблица, состоящая из двух столбцов: ИмяПрепод и ВсегоЧасов и количества записей, равного количеству преподавателей занесенных в таблицу РАСПИСАНИЕ. В запросе использованы вычисления по вертикали и по горизонтали.

8. Подсчитать среднее количество студентов в группе для каждой специальности:

GROUP ГРУППА BY (ШифрСпец) ADD AVG (КоличСтудентов) AS СредКоличСтуд.

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

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

АУДИТОРИЯ where Вместимость >= ((ГРУППА where НомерГруппы = 144)[КоличСтудентов]).

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