$\newcommand\joineq{\raise{-0.05ex}{\unicode{xa78a}}} \newcommand{\rjoin}{\mathbin{⋉\mkern-6mu\joineq}} \newcommand{\ljoin}{\mathbin{\joineq\mkern-6mu⋊}} \newcommand{\ojoin}{\mathbin{\joineq\mkern-10mu×\mkern-10mu\joineq}}$

# Запросы

• Выборка данных
• Область действия обновлений
• Ограничения целостности
• Ограничения доступа

# Реляционная алгебра

• Императивный язык для работы с отношениями
• Указывает как именно получить требуемый результат
• Что такое алгебра в математике?
• Множество (носитель)
• Набор операций
• Операции замкнуты
• Что может быть носителем?
• Множество всех отношений

# Выражения

• Операции
• $π_A(R)$
• $R_1 ⋈ R_2$
• ...
• Замкнутость реляционной алгебры
• Операции преобразуют отношения в отношения
• Операции можно комбинировать
• Пример выражения
• $π_A(R_1 ⋈ π_B(R_2)) ⋈ R_3$

# Операции

• Отношение
• Заголовок
• Тело
• Операция
• Правило построения заголовка
• Правило построения тела
• Что забыто?
• Возможность выполнения операции

# Проекция

• $π_{A_1, A_2, …, A_n}$ – проекция на атрибуты $A_1, A_2, …, A_n$
• Заголовок – пересечение заголовка с $\{A_1, A_2, …, A_n\}$
• Тело – пересечение кортежей с $\{A_1, A_2, …, A_n\}$ # Пример проекции (1)

• $π_{FirstName, LastName}$ # Пример проекции (1)

• $π_{FirstName, LastName}$ # Пример проекции (2)

• $π_{FirstName}$ # Пример проекции (2)

• $π_{FirstName}$ # Фильтрация (сечение, выборка)

• $σ_{condition}$ – фильтрация по условию
• Заголовок – сохраняется
• Тело – кортежи, удовлетворяющие условию # Пример фильтрации (1)

• $σ_{Id > 2}$ # Пример фильтрации (1)

• $σ_{Id > 2}$ # Пример фильтрации (2)

• $σ_{Id > 2 ∧ FirstName = \text{Иван}}$ # Пример фильтрации (2)

• $σ_{Id > 2 ∧ FirstName = \text{Иван}}$ # Пример фильтрации (3)

• $σ_{length(FirstName) + 2 ≥ length(LastName)}$ # Пример фильтрации (3)

• $σ_{length(FirstName) + 2 ≥ length(LastName)}$ # Переименование

• $ρ_{NewName_i=OldName_i}$ – изменение имени атрибута
• Заголовок – название $OldName_i$ меняется на $NewName_i$
• Тело – название $OldName_i$ меняется на $NewName_i$

# Пример переименования

• $ρ_{Name=FirstName, Surname=LastName}$ # Пример переименования

• $ρ_{Name=FirstName, Surname=LastName}$ # Операции над множествами

• $R_1 ∪ R_2$ – объединение
• $R_1 ∩ R_2$ – пересечение
• $R_1 ∖ R_2$ – разность
• Заголовок – сохраняется
• Применяются для отношений с одинаковыми заголовками
• Тела – соответствующая операция над множествами

# Пример объединения

• $R_1 ∪ R_2$ # Пример объединения

• $R_1 ∪ R_2$ # Пример пересечения

• $R_1 ∩ R_2$ # Пример пересечения

• $R_1 ∩ R_2$ # Пример разности

• $R_1 ∖ R_2$ # Пример разности

• $R_1 ∖ R_2$ # Соединения

• Соединение – объединяет данные двух отношений # Полное соединение

• $R_1 × R_2$ – декартово произведение двух отношений
• Заголовок – объединение заголовков
• Различные имена атрибутов
• Тело – декартово произведение тел отношений

# Пример полного соединения

• $R_1 × R_2$ # Пример полного соединения

• $R_1 × R_2$ # Естественное соединение

• $R_1 ⋈ R_2$ – соединяются кортежи, имеющие равные значения одинаковых атрибутов
• Заголовок – объединение заголовков

# Пример естественного соединения

• $R_1 ⋈ R_2$ # Пример естественного соединения

• $R_1 ⋈ R_2$ # Размер естественного соединения

• Пусть $n=|R_1|$, $m=|R_2|$
• Максимальный $|R_1 ⋈ R_2|$
• $n×m$ – нет общих атрибутов
• Минимальный $|R_1 ⋈ R_2|$
• 0 – нет равных атрибутов

# Внешнее соединение

• $R_1 \ojoin R_2$ – соединяются кортежи, имеющие равные значения одинаковых атрибутов
• Если в другом отношении нет соответствующего кортежа, берется пустой
• Заголовок – объединение заголовков

# Пример внешнего соединения

• $R_1 \ojoin R_2$ # Пример внешнего соединения

• $R_1 \ojoin R_2$ # Левое и правое соединения

• $R_1 \ljoin R_2$ – левое соединение
• Все строки слева, дополненные информацией справа
• $(R_1 ⋈ R_2) ∪ (R_1 ∖ π_{R_1}(R_1 ⋈ R_2))$
• $R_1 \rjoin R_2$ – правое соединение
• Все строки справа, дополненные информацией слева
• $(R_1 ⋈ R_2) ∪ (R_2 ∖ π_{R_2}(R_1 ⋈ R_2))$
• Как сделать внешнее соединение?
• $R_1 \ojoin R_2 ≡ (R_1 \ljoin R_2) ∪ (R_1 \rjoin R_2)$

# Примеры косых соединений

• $R_1 \ljoin R_2$ и $R_1 \rjoin R_2$ # Примеры косых соединений

• $R_1 \ljoin R_2$ и $R_1 \rjoin R_2$ # Полусоединения

• $R_1 ⋉ R_2$ – левое полусоединение
• Строки слева, для которых есть соответствующие строки справа
• $R_1 ⋉ R_2 ≡ π_{R_1}(R_1 ⋈ R_2)$
• $R_1 \ljoin R_2 = (R_1 ⋈ R_2) ∪ (R_1 ∖ R_1 ⋉ R_2)$
• $R_1 ⋊ R_2$ – правое полусоединение
• строки справа, для которых есть соответствующие строки слева
• $R_1 ⋊ R_2 ≡ π_{R_2}(R_1 ⋈ R_2)$
• $R_1 \rjoin R_2 = (R_1 ⋈ R_2) ∪ (R_2 ∖ R_1 ⋊ R_2)$

# Примеры полусоединений

• $R_1 ⋉ R_2$ и $R_1 ⋊ R_2$ # Примеры полусоединений

• $R_1 ⋉ R_2$ и $R_1 ⋊ R_2$ # Условные соединения

• $R_1 ×_θ R_2$ – условное соединение
• $σ_θ(R_1 × R_2)$
• $R_1 \ljoin_θ R_2$ – левое условное соединение
• $J ∪ (R_1 ∖ π_{R_1}(J))$, где $J = σ_θ(R_1 × R_2)$
• $R_1 \rjoin_θ R_2$ – правое условное соединение
• $J ∪ (R_2 ∖ π_{R_2}(J))$, где $J = σ_θ(R_1 × R_2)$
• $R_1 \ojoin_θ R_2$ – внешнее условное соединение
• $(R_1 \ljoin_θ R_2) ∪ (R_1 \rjoin_θ R_2)$

# Пример левого условного соед.

• $R_1 \ljoin_{length(FirstName) + 2 < length(LastName)} R_2$ # Пример левого условного соед.

• $R_1 \ljoin_{length(FirstName) + 2 < length(LastName)} R_2$ # Деление

• $Q(XY) ÷ S(Y)$ – деление
• Найти максимальное $X$, такое что $X × S ⊆ Q$
• $Q ÷ S ≡ \{x | x ∈ π_X(Q), \{x\} × S ⊆ Q \}$
• $Q ÷ S ≡ π_X(Q) ∖ π_X(π_X(Q) × S ∖ Q)$
• Интуиция
• запрос «для всех»
• $x$, для которых есть пара для каждого $y$
• $x ∈ π_X(Q): ∀ y ∈ S: (x, y) ∈ Q$
• Заголовок – $X$
• $S ⊆ Q$

# Пример деления

• $Q ÷ S$ # Пример деления

• $Q ÷ S$ # Большое деление

• $Q(XY) ⋇ S(YZ)$ – большое деление
• $Q ⋇ S ≡ \{(x, z) | \{x\} × π_Y(σ_{Z=z}(S)) ⊆ Q\}$
• $Q ⋇ S ≡ π_X(Q) × π_Z(S) ∖ π_{XZ}(π_X(Q) × S ∖ Q ⋈ S)$
• Интуиция
• запрос «для всех связанных»
• деление для каждого $z$
• для каждого $z$ такие $x$, что есть пара для всех $y$, связанных с $z$
• $(x, z) ∈ π_X(Q) × π_Z(S): ∀ y ∈ π_Y(σ_{=z}(S)): (x, y) ∈ Q$
• Заголовок – $XZ$

# Пример большого деления

• $Q ⋇ S$ # Пример большого деления

• $Q ⋇ S$ # Расширение

• $ε_{A=expression}(R)$
• Добавляет вычисляемый атрибут
• Заголовок
• Заголовок $R ∪ \{A\}$
• Тело
• К каждому кортежу добавляется вычисленное значение
• Выражение
• Атрибуты одного кортежа
• Функции и операции

# Пример расширения

• $ε_{Tax=tax10(Total)} ∘ ε_{Total=Price · Items}$ # Пример расширения

• $ε_{Tax=tax10(Total)} ∘ ε_{Total=Price · Items}$ # Агрегирование

• $Function_{Q, A}(R)$ – обработка набора значений
• Функция: count, sum, avg, max, min, all, any
• $Q$ – агрегируемый атрибут
• $A$ – сохраняемые атрибуты
• Каждый $r ∈ π_A(R)$ расширяется атрибутом $Q = Function(π_Q\{r' ∈ R | π_A(r') = r\})$
• Интуиция
• Разбить на корзины по значениям $A$
• Для каждой корзины посчитать функцию над $Q$

# Пример агрегирования (1)

• $sum_{Total,\{Supplier\}} ∘ ε_{Total=Price · Items}$ # Пример агрегирования (1)

• $sum_{Total,\{Supplier\}} ∘ ε_{Total=Price · Items}$ # Пример агрегирования (2)

• $sum_{Total,∅} ∘ ε_{Total=Price · Items}$ # Пример агрегирования (2)

• $sum_{Total,∅} ∘ ε_{Total=Price · Items}$ # Свойства операций

• Идемпотентность
• $π$, $σ$
• $⋈$, $∪$, $∩$, $\ojoin$, $\ljoin$, $\rjoin$, $⋉$ (справа), $⋊$ (слева), $∖$ (справа)
• Коммутативность
• $∪$, $∩$, $×$, $⋈$, $\ojoin$
• Ассоциативность
• $∪$, $∩$, $×$, $⋈$, $\ojoin$, $\ljoin$, $\rjoin$

# Базис операций

• Унарные операции
• Объединение и разность
• Декартово произведение
• Операции над данными

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

• Не все «естественные» запросы могут быть выражены
• Невозможно выразить транзитивное замыкание
• Но можно найти пути длины не больше $L$
• Значение выражение может быть вычислено за полиномиальное время
• Реляционная алгебра не эквивалентна машине Тьюринга
• Эквивалентность выражений алгоритмически не разрешима

# Зачем реляционная алгебра

• Преобразование запросов
• Упрощение запроса
• Оптимизация плана выполнения
• Запросы, невыразимые в SQL непосредственно
• Полусоединения
• Деления
• Неполная поддержка соединений

# Унарные операции

• Проекция $π_{A_1, A_2, …, A_n}(R)$
• select distinct A1, A2, …, An from R

• select A1, A2, …, An from R -- с повторениями

– с повторениями
• Фильтрация $σ_{Condition}(R)$
• select * from R where Condition

• Переименование $ρ_{a=b}(R)$
• select …, a as b, … from R


# Операции над множествами

• Объединение $R_1 ∪ R_2$
• select * from R1 union select * from R2

• select * from R1 union all select * from R2
-- с повторениями

• Пересечение $R_1 ∩ R_2$
• select * from R1 intersect select * from R2

• select * from R1 intersect all select * from R2
-- с повторениями

• Разность $R_1 ∖ R_2$
• select * from R1 except all select * from R2

• select * from R1 except select * from R2
-- с повторениями


# Операции с данными

• Расширение $ε_{A=expr}(R)$
• select *, expr as A from R

• Агрегирование $Function_{Q, A}(R)$
• select A, func(Q) as Q from R group by A

• select count(*) … -- подсчет всех

• select count(distinct *) … -- подсчет различных

• select count(q) … -- подсчет не null

• … having condition -- фильтрация после агрегации

• … order by attrs -- сортировка


# Соединения

• Полное $R_1 × R_2$
• select * from R1 cross join R2

• select * from R1, R2

• Естественное $R_1 ⋈ R_2$
• select * from R1 natural join R2

• select * from R1 inner join R2 using (A)

• select * from R1 inner join R2 on R1.A = R2.A

• Внешние $R_1 \ojoin_θ R_2$, $R_1 \ljoin_θ R_2$, $R_1 \rjoin_θ R_2$
• select * from R1 [full] outer join R2 on θ

• select * from R1 left [join] R2 on θ

• select * from R1 right [join] R2 on θ

• Полусоединений нет

# Переименование таблиц

• Таблица
• Persons (Id, Name, Birthday, MotherId, FatherId)
• Получить дни рождения родителей
• (Name, FatherBirthday, MotherBirthday)
• select
p.Name as Name,
f.Birthday as FatherBirthday,
m.Birthday as MotherBirthday
from
Persons p
inner join Persons f on p.FatherId = f.Id
inner join Persons m on p.MotherId = m.id


# Подзапросы

• Таблица
• Persons (Id, Name, Birthday, MotherId, FatherId)
• Имена людей, у которых родители родились в один день
• (Name)
• select p.Name as Name
from (предыдущий запрос)
where p.MotherBirthday = p.FatherBirthday


# Сложный запрос

• Таблица
• Persons (Id, Name, MotherId, FatherId)
• Получить для каждого человека
• (Name, ParentName)
• select p.Name as Name, f.Name as ParentName
from Persons p
inner join Persons f on p.FatherId = f.Id
union
select p.Name as Name, m.Name as ParentName
from Persons p
inner join Persons m on p.MotherId = m.Id


# Структура SQL-запроса

• SQL-запрос имеет вид
• $ρ(π(σ(R_1 ⊗ R_2 ⊗ ··· ⊗ R_n)))$
• select
… as …, … as … -- π и ρ
from
R1
ххх join R2 on …   -- ⊗
…
ххх join Rn on …   -- ⊗
where
… -- σ

• $R_1, R_2, \dots, R_n$ – таблицы или подзапросы

# Основная литература

• Дейт К. Введение в системы баз данных (глава 7)
• Уидом Д., Ульман Д. Основы реляционных баз данных (главы 4 и 5)
• Gulutzan P., Pelzer T. SQL-99 complete, really

# Дополнительная литература

• Codd E.F. Relational completeness of data base sublanguages