$ \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)}$

Переименование

Содержание

Переименование

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

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

  • $ρ_{FirstName=Name} ∘ ρ_{LastName=Surname}$

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

  • $ρ_{FirstName=Name} ∘ ρ_{LastName=Surname}$

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

Содержание

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

  • $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$

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

Содержание

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

  • $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 непосредственно
    • Полусоединения
    • Деления
    • Неполная поддержка соединений

Реляционная алгебра и 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
      
  • Переименование $Rename_{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 FatherBirhthday,
              m.Birthday as MotherBirhthday
          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
      
    • select Name
          from (предыдущий запрос)
          where MotherBirthday = 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)

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

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