Операции расширения и подведения итогов
Лекция 9. Операторы реляционной алгебры
В лекции рассматриваются основные операторы реляционной алгебры и приводятся примеры их реализации на языке SQL.
Цель: познакомить с основными реляционными операторами.
Понятие реляционной алгебры
Реляционная алгебра – это набор операций, которые принимают отношения в качестве операндов и возвращают отношение в качестве результата. Первая версия этой алгебры была определена Э. Коддом.
В основе всех реляционных БД лежит использование реляционной алгебры, которая обеспечивает запись выражений для реализации на некотором языке, например, SQL. Если возможности языка как минимум соответствуют возможностям, обеспеченным алгебраическими операциями, то его называют реляционно полным.
Обычно выделяют 8 основных операторов реляционной алгебры и несколько дополнительных (их количество меняется со временем). Мы рассмотрим два дополнительных оператора: расширения и подведения итогов.
Основные операторы реляционной алгебры
Реляционная алгебра включает восемь основных операций, которые подразделяются на две группы, в каждую из которых входит четыре операции.
1. Традиционные операции с множествами, модифицированные для таблиц (отношений).
1) Объединение: A UNION B
Результатом операции объединения является отношение, содержащее все кортежи, принадлежащие одному из двух или обоим отношениям.
|
|
|
В отличие от объединения множеств, результатом объединения отношений должно стать отношение, а не набор разнородных кортежей.
При объединении должны соблюдаться два условия:
· отношения должны быть совместимы по типу, т.е. иметь одно и то же множество атрибутов, определённых на одних и тех же доменах;
· результатом каждой операции должно быть также отношение (свойство замкнутости).
Пусть имеется отношение Students:
| StudentID | Name | GroupID | BirthDate |
| 1 | Казаков Петр | 2 | 23.04.1990 |
| 2 | Васильев Иван | 1 | 11.05.1991 |
| 4 | Шишкина Дарья | 2 | 23.09.1991 |
И отношение Teachers:
| TeacherID | Name | BirthDate |
| 1 | Кислицын О.П. | 1.2.1970 |
| 2 | Царев С.М. | 10.03.1964 |
| 4 | Пестов Д.Н. | 2.05.1980 |
Нам требуется вывести список всех преподавателей и студентов с указанием их дня рождения. Для этого можно использовать следующие операторы SQL:
SELECT Name, BirthDate FROM Students
UNION
SELECT Name, BirthDate FROM Teachers
Результатом выполнения будет следующее отношение:
| Name | BirthDate |
| Казаков Петр | 23.04.1990 |
| Васильев Иван | 11.05.1991 |
| Шишкина Дарья | 23.09.1991 |
| Кислицын О.П. | 1.2.1970 |
| Царев С.М. | 10.03.1964 |
| Пестов Д.Н. | 2.05.1980 |
|
|
|
2) Пересечение: A INTERSECT B
Результатом операции пересечения является отношение, содержащее кортежи, которые принадлежат обоим отношениям.
Для операции пересечения необходимы те же два условия, что и для объединения: совместимость по типу и замкнутость.
Пример: рассмотрим отношение Teachers (преподаватели) и Supervisors (кураторы, могут руководить преподавателями):
Teachers:
| TeacherID | Name | BirthDate |
| 1 | Кислицын О.П. | 1.2.1970 |
| 2 | Царев С.М. | 10.03.1964 |
| 4 | Пестов Д.Н. | 2.05.1980 |
Supervisors:
| SupervisorID | Name | BirthDate |
| 1 | Кислицын О.П. | 1.2.1970 |
| 2 | Царев С.М. | 10.03.1964 |
| 4 | Нечаев Н.В. | 12.08.1970 |
Требуется вывести всех преподавателей (из таблицы Teachers), которые одновременно являются кураторами.
SELECT Name FROM Teachers
INTERSECT
SELECT Name FROM Supervisors
Результатом выполнения запроса будет:
| Name |
| Кислицын О.П. |
| Царев С.М. |
3) Вычитание: A MINUS B
Результатом операции вычитания является отношение, которое содержит все кортежи, принадлежащие первому отношению и не принадлежащие второму отношению.
Условия необходимы те же: совместимость по типу и замкнутость.
|
|
|
Пример: рассмотрим отношение Teachers (преподаватели) и Supervisors (кураторы, могут руководить преподавателями):
Teachers:
| TeacherID | Name | BirthDate |
| 1 | Кислицын О.П. | 1.2.1970 |
| 2 | Царев С.М. | 10.03.1964 |
| 4 | Пестов Д.Н. | 2.05.1980 |
Supervisors:
| SupervisorID | Name | BirthDate |
| 1 | Кислицын О.П. | 1.2.1970 |
| 2 | Царев С.М. | 10.03.1964 |
| 4 | Нечаев Н.В. | 12.08.1970 |
Требуется вывести всех преподавателей (из таблицы Teachers), которые одновременно являются кураторами.
SELECT Name FROM Teachers
EXCEPT
SELECT Name FROM Supervisors
Результатом выполнения запроса будет:
| Name |
| Пестов Д.Н. |
4) П роизведение: A TIMES B
Результатом является отношение, содержащее все возможные кортежи, которые являются сочетанием двух кортежей, принадлежащих двум отношениям.
Для произведения необходимо свойство замкнутости, т.е. результатом является не просто множество пар кортежей, а множество целых кортежей. В реляционной алгебре каждая пара кортежей заменяется одним благодаря сцеплению (сцепление – это объединение в смысле теории множеств, а не реляционной алгебры).
При произведении может возникнуть проблема одинаковых имён атрибутов. В этом случае одно из конфликтующих полей необходимо переименовать.
|
|
|
Пример: рассмотрим отношения Students и Courses (учебные курсы):
Students:
| StudentID | Name | GroupID | BirthDate |
| 1 | Казаков Петр | 2 | 23.04.1990 |
| 2 | Васильев Иван | 1 | 11.05.1991 |
| 4 | Шишкина Дарья | 2 | 23.09.1991 |
Courses:
| CourseID | Name |
| 1 | Базы данных |
| 2 | Технологии программирования |
| 3 | Программирование в среде С++ |
Требуется для каждого студента вывести список всех доступных учебных курсов. Составим следующий код:
SELECT Students.Name AS ‘Student’, Courses.Name AS ‘CourseName’
FROM Students
CROSS JOIN Courses
Результат выполнения запроса:
| Name | Name |
| Казаков Петр | Базы данных |
| Васильев Иван | Базы данных |
| Шишкина Дарья | Базы данных |
| Казаков Петр | Технологии программирования |
| Васильев Иван | Технологии программирования |
| Шишкина Дарья | Технологии программирования |
| Казаков Петр | Программирование в среде С++ |
| Васильев Иван | Программирование в среде С++ |
| Шишкина Дарья | Программирование в среде С++ |
2. Специальные реляционные операции.
1) В ыборка (или ограничение)
Результатом выборки является отношение, содержащее все кортежи из исходного отношения, которые удовлетворяют определённому условию.
Пусть дано следующее отношение Students:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 1 |
| 4 | Шишкина Дарья | 2 |
| 5 | Драгомиров Евгений | 1 |
| 6 | Васнецова Евгения | 2 |
Выберем лишь тех студентов, которые принадлежат группе 2:
SELECT * FROM Students
WHERE GroupID = 2
Результатом выполнения будет отношение:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 4 | Шишкина Дарья | 2 |
| 6 | Васнецова Евгения | 2 |
Проекция
Результатом проекции является отношение, содержащее все кортежи после удаления из них некоторых атрибутов. В этом случае результирующие кортежи называются подкортежами.
Обратим внимание на два момента:
1) возможно указание всех атрибутов исходного отношения для проекции – получится тождественная проекция;
2) возможно указать пустой список атрибутов – получится нулевая проекция.
Осуществить проекцию можно указанием после SELECT списка необходимых атрибутов.
Пусть дано следующее отношение Students:
| StudentID | Name | GroupID | BirthDate |
| 1 | Казаков Петр | 2 | 23.04.1990 |
| 2 | Васильев Иван | 1 | 11.05.1991 |
| 4 | Шишкина Дарья | 2 | 23.09.1991 |
Выберем из таблицы только имена и даты рождений:
SELECT Name , BirthDate FROM Students
Результатом выполнения будет отношение:
| Name | BirthDate |
| Казаков Петр | 23.04.1990 |
| Васильев Иван | 11.05.1991 |
| Шишкина Дарья | 23.09.1991 |
Соединение
Результат соединения – это отношение, кортежи которого являются сочетанием двух кортежей, принадлежащих двум начальным отношениям и имеющих общие значения для одного или нескольких атрибутов (общее значение в результирующем отношении появляется только один раз).
Пусть дано следующее отношение Students:
| StudentID | Name | GroupID | BirthDate |
| 1 | Казаков Петр | 2 | 23.04.1990 |
| 2 | Васильев Иван | 1 | 11.05.1991 |
| 4 | Шишкина Дарья | 2 | 23.09.1991 |
и отношение Groups:
| GroupID | GroupName |
| 1 | ПМ-11 |
| 2 | ПМ-12 |
| 3 | ПМ-21 |
Соединим эти таблицы по полю GroupID и выведем имя студента и название учебной группы:
SELECT Name, GroupName FROM Students
INNER JOIN Groups ON Students.GroupID = Groups.GroupID
Результатом выполнения будет отношение:
| Name | GroupName |
| Казаков Петр | ПМ-12 |
| Васильев Иван | ПМ-11 |
| Шишкина Дарья | ПМ-12 |
Это соединение обладает свойствами ассоциативности и коммутативности.
4) Деление: A DIVIDEBY B
Результатом деления двух отношений (бинарного и унарного) является отношение, содержащее все значения атрибута первого бинарного отношения, которые соответствуют всем значениям унарного отношения.
Эта операция не имеет аналога в MS SQL Server 2008, поэтому рассмотрим на примере деления отношения R1 на R2:
R1:
| A | X |
| A | Y |
| B | Z |
| B | X |
| C | Y |
R2:
| X |
| Y |
В результате получится отношение:
R:
| A |
Операции расширения и подведения итогов
После того, как Э. Кодд предложил восемь основных операций, многочисленные авторы предложили новые алгебраические операции. Мы рассмотрим лишь две из них – расширение и подведение итогов. Эти операции удачно дополняют основной набор и являются наиболее востребованными.
Расширение
С помощью операции расширения из отношения создаётся новое отношение, содержащее новый атрибут, значения которого получены посредством скалярных вычислений.
Пусть дано следующее отношение Teachers:
| TeacherID | Name | BirthDate |
| 1 | Кислицын О.П. | 1.2.1970 |
| 2 | Царев С.М. | 10.03.1964 |
| 4 | Пестов Д.Н. | 2.05.1980 |
Добавим новый атрибут Age, который будет показывать, сколько полных лет исполнилось преподавателю:
SELECT TeacherID, Name, BirthDate, DATEDIFF(YEAR, BirthDate, GetDate()) AS ‘Age’ FROM Teachers
Результатом выполнения будет отношение:
| TeacherID | Name | BirthDate | Age |
| 1 | Кислицын О.П. | 1.2.1970 | 39 |
| 2 | Царев С.М. | 10.03.1964 | 45 |
| 4 | Пестов Д.Н. | 2.05.1980 | 29 |
Подведение итогов
Операция подведения итогов даёт возможность "вертикальных" вычислений. Для этого используются агрегатные функции, которые для набора значений возвращают одно единственное. Наиболее распространенные функции: Sum, Count, Avg, Min, Max.
Пусть дано следующее отношение Students:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 1 |
| 4 | Шишкина Дарья | 2 |
| 5 | Драгомиров Евгений | 1 |
| 6 | Васнецова Евгения | 2 |
Требуется подсчитать, сколько студентов в каждой группе:
SELECT GroupID, Count(StudentID) as ‘StudentCount' FROM Students
GROUP BY GroupID
Результатом выполнения будет отношение:
| GroupID | StudentCount |
| 1 | 2 |
| 2 | 3 |
Операторы обновления
Предназначены для управления данными в таблицах. Существует три операции обновления.
Вставка записи
Позволяет добавлять одну или несколько новых записей в отношение.
Пусть имеется отношение Students:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 1 |
| 4 | Шишкина Дарья | 2 |
Чтобы добавить в него две новые записи, можно использовать следующие операторы SQL:
INSERT INTO Students (Name, GroupID) VALUES (‘Драгомиров Евгений’, 1)
INSERT INTO Students (Name, GroupID) VALUES (‘Васнецова Евгения’, 2)
После выполнения, отношение Students будет выглядеть следующим образом:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 1 |
| 4 | Шишкина Дарья | 2 |
| 5 | Драгомиров Евгений | 1 |
| 6 | Васнецова Евгения | 2 |
Удаление записи.
Удаляет все записи из отношения, или только записи, удовлетворяющие заданному критерию.
Пусть имеется отношение Students:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 1 |
| 4 | Шишкина Дарья | 2 |
| 5 | Драгомиров Евгений | 1 |
| 6 | Васнецова Евгения | 2 |
Чтобы удалить из него студентов, входящих в группу с номером 1, можно использовать следующие операторы SQL:
DELETE Students
WHERE GroupID = 1
После выполнения, отношение Students будет выглядеть так:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 4 | Шишкина Дарья | 2 |
| 6 | Васнецова Евгения | 2 |
Если не указать раздел WHERE в операторе DELETE, то будут удалены все записи из таблицы.
Обновление записи
Позволяет обновлять значения указанных полей всех или только определенных записей.
Пусть имеется отношение Students:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 1 |
| 4 | Шишкина Дарья | 2 |
| 5 | Драгомиров Евгений | 1 |
| 6 | Васнецова Евгения | 2 |
Чтобы перевести всех студентов, входящих в группу с номером 1, в группу с номером 2, можно использовать следующие операторы SQL:
UPDATE Students SET GroupID = 2
WHERE GroupID = 1
После выполнения, отношение Students будет выглядеть следующим образом:
| StudentID | Name | GroupID |
| 1 | Казаков Петр | 2 |
| 2 | Васильев Иван | 2 |
| 4 | Шишкина Дарья | 2 |
| 5 | Драгомиров Евгений | 2 |
| 6 | Васнецова Евгения | 2 |
Краткие итоги. Рассмотрены основные операции над таблицами, а также были приведены примеры использования этих операций на языке SQL.
Дата добавления: 2021-03-18; просмотров: 646; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!