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

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

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.

Основы использования языка структурированных запросов SQL

Типы запросов к БД

В СУБД Access построить запрос можно в одном из двух режимов:

Ø режим конструктора (ручное конструирование, языки QBE или SQL);

Ø режим мастера (автоматизированное конструирование).

В свою очередь, в режиме конструктора можно использовать одновременно один из двух языков: QBE или SQL. Это очень удобно для изучения тех или иных элементов одного языка с помощью другого, так как при внесении изменений в запрос на одном из этих языков они немедленно вносятся и на другом. Таким образом, можно утверждать, что конструктор запросов СУБД Access содержит в себе преобразователь текста запроса с языка QBE на язык SQL и наоборот. Однако следует заметить, что язык SQL, в отличие от QBE, помимо средств манипулирования данными, предназначенных для выполнения операций выборки, удаления, изменения и добавления данных, содержит еще средства определения данных, предназначенных для создания новых структур данных (таблиц, индексов, представлений и т.д.), их модификации и удаления. В СУБД Access использование эти средств называется управляющим запросом и в данном пособии не рассматривается.

В СУБД Access используется так называемый JET SQL, который содержит некоторые отличия и ограничения по сравнению со стандартным ANSI SQL.

Запросы, связанные с манипулированием данными, можно разделить на следующие типы:

Ø запрос на выборку (SELECT) – создает динамическое отношение с использованием большинства рассмотренных выше реляционных операций, при определенных ограничениях его можно редактировать; этот тип запроса является основой для конструирования большинства других типов запросов;

Ø запрос на создание новой таблицы (SELECT … INTO) – аналогичен запросу на выборку с единственным отличием, что результат запроса запоминается в новой таблице и отрывается от своего источника, т.е. является статическим; результат этого запроса редактировать нельзя;

Ø перекрестный запрос (TRANSFORM … SELECT … PIVOT) – создает перекрестную таблицу (матрицу), отражающую информацию о взаимном сочетании значений двух полей таблицы-источника, например, количество часов в неделю у каждого преподавателя по каждому виду занятий в расписании;

Ø запрос на добавление (INSERT) – позволяет добавить в какую-либо таблицу одну или более новых записей, как с клавиатуры, так и из другого источника данных;

Ø запрос на изменение (UPDATE) – позволяет изменить значения определенных полей в определенных записях определенной таблицы;

Ø запрос на удаление (DELETE) – позволяет удалить из какой-либо таблицы определенные записи.

Как уже отмечалось, наиболее важным и наиболее часто требующимся на практике является запрос на выборку. Этот тип запроса может вкладываться как в себе подобный (при этом он называется вложенным), так и в другие типы запросов. Фактически фразы DELETE и UPDATE представляют собой то же самое, что и фраза SELECT, с той лишь разницей, что результат выборки будет использован соответственно для удаления и обновления выбранных записей. Поэтому рассмотрим более подробно возможности команды SELECT языка SQL.

Запрос на выборку

Не нашли, что искали? Воспользуйтесь поиском по сайту: