Лекция 4. Реляционная модель

Реляционная модель опирается на:

• реляционную алгебру;
• реляционное исчисление.

Один из первых языков, основанных на реляционной алгебре, — ISBL.

Эдгар Франк «Тед» Кодд (1923–2003) — британский учёный, работавший в IBM. Его работы заложили основы теории реляционных баз данных: именно он создал реляционную модель данных.

Кристофер Дейт дал наиболее распространённую трактовку реляционной модели. По его мнению, модель состоит из трёх частей: структурной, манипуляционной и целостной.

Типы операций

• Теоретико-множественные — UNION, INTERSECT, разность, декартово произведение.
• Специальные реляционные — выборка, проекция, соединение, деление.

Выборка (унарная операция)

Применяется к одному отношению и определяет результирующее отношение, содержащее только те кортежи, которые удовлетворяют заданному условию (предикату).

SELECT * FROM "Персоны" WHERE "Возраст" >= 34;

Проекция (унарная операция)

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

SELECT "Возраст", "Вес" FROM "Персоны";

Объединение

Объединение двух отношений $R$ и $S$ определяет новое отношение, включающее все кортежи, содержащиеся в $R$, все кортежи, содержащиеся в $S$, и кортежи, содержащиеся одновременно в обоих (с исключением дубликатов).

$R$ и $S$ должны быть совместимы по объединению: одинаковое количество атрибутов и одинаковые домены соответствующих пар. Имена атрибутов могут не совпадать — главное, чтобы совпадали домены. Для совместимости можно предварительно применить проекцию.

SELECT * FROM "Персоны" UNION SELECT * FROM "Сотрудники";

Пересечение

Отношение, содержащее кортежи, присутствующие как в $R$, так и в $S$. Отношения должны быть совместимы по объединению.

Разность

Кортежи, имеющиеся в $R$, но отсутствующие в $S$. Отношения должны быть совместимы по объединению.

Декартово произведение

Новое отношение, являющееся результатом конкатенации (сцепления) каждого кортежа из $R$ с каждым кортежем из $S$.

В чистом виде применяется редко, но служит основой для построения соединений.

Деление

Определяет отношение из множества кортежей $R$, определённых на атрибуте $C$, соответствующих комбинации всех кортежей $S$, где $C$ — множество атрибутов, имеющихся в $R$, но отсутствующих в $S$.

Соединение

• Тета-соединение ($\theta$-join). Определяет отношение, содержащее кортежи из декартова произведения $R$ и $S$, удовлетворяющие предикату $F$ вида $R.a_i\ \theta\ S.b_i$, где $\theta$ — операция сравнения (<, <=, =, и т.д.).

  $$R \bowtie_F S$$

• Эквисоединение (Equi-join). Если используются только сравнения по равенству.

• Естественное соединение (Natural join). Эквисоединение двух отношений, выполненное по всем общим атрибутам, из результата которого исключается по одному экземпляру каждого общего атрибута.

  $$R \bowtie S$$

Критика Дейта

Избыточность

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

Недостаточность

• Не хватает операций:
  • переименования атрибутов;
  • вычисления атрибутов;
  • агрегирующих функций (SUM, AVG, и т.д.);
  • присваивания результатов временным отношениям.

Синтаксический порядок SELECT

(Порядок написания и порядок логического исполнения отличаются — это важно помнить.)

Выбор типа данных для времени

• TIMESTAMPTZ (timestamp with time zone)
  • Если важны события и их последовательность — логи, аудиты.
  • При запросе смотрит на клиентскую тайм-зону.
  • Сам не хранит тайм-зону — хранит UTC, постоянно конвертирует.
• TIMESTAMP (без зоны)
  • Подходит для дат из прошлого, для которых не важен часовой пояс.

Пример. Сервер на UTC-3, клиент на UTC+3. Открываем 3 разных клиента (в разных часовых поясах) и запрашиваем текущее время. С TIMESTAMPTZ каждый клиент увидит время в своей локальной зоне — момент времени один и тот же, представление разное.

INSERT

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ];

-- conflict_target:
--   ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] )
--       [ WHERE index_predicate ]
--   ON CONSTRAINT constraint_name
--
-- conflict_action:
--   DO NOTHING
--   DO UPDATE SET { column_name = { expression | DEFAULT } | ... } [, ...]
--               [ WHERE condition ]

При конфликте обработка идёт в ON CONFLICT:

• ON CONFLICT DO NOTHING — игнорируем конфликт, ничего не вставляем и не обновляем.
• ON CONFLICT DO UPDATE SET ... — обновляем существующую запись (паттерн UPSERT).

UPDATE

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } | ... } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ];