---

КОНСПЕКТ ПО ТЕОРИИ СУБД ДЛЯ ЭКЗАМЕНА

Файл сделан для быстрого поиска и копирования.
Темы идут по тем вопросам, которые встречаются в старых билетах.
Почти в каждом блоке есть пример, потому что на экзамене могут снять баллы за ответ без примера.


1. ПРЕДМЕТНАЯ ОБЛАСТЬ, ФОРМАЛИЗАЦИЯ, БИЗНЕС-ПРАВИЛА

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

Изучение предметной области обычно включает:
1. определение основных объектов;
2. определение связей между объектами;
3. определение атрибутов объектов;
4. выявление ограничений и бизнес-правил;
5. построение концептуальной модели.

Формализация - это перевод описания предметной области на строгий язык модели данных. Например, фразу "у каждого билета есть пассажир" можно формализовать как сущность tickets с атрибутами ticket_no, passenger_id, passenger_name.

Смысловое описание - обычное текстовое описание предметной области. Оно помогает понять, что именно хранится в БД и какие операции должны выполняться.

Бизнес-правила - ограничения и правила работы предметной области. Они могут быть реализованы ключами, CHECK-ограничениями, внешними ключами, триггерами или логикой приложения.

Пример для авиаперевозок:
1. пассажир может иметь несколько билетов;
2. билет может включать несколько перелетов;
3. рейс выполняется на одном самолете;
4. место в посадочном талоне должно относиться к конкретному рейсу и билету;
5. стоимость билета не должна быть отрицательной.

Пример бизнес-правила в SQL:

ALTER TABLE ticket_flights
ADD CONSTRAINT amount_positive CHECK (amount >= 0);


2. КОНЦЕПТУАЛЬНОЕ ПРОЕКТИРОВАНИЕ И ER-МОДЕЛЬ

Концептуальное проектирование - этап, на котором строится модель предметной области без привязки к конкретной СУБД.

Основные элементы ER-модели:
1. сущность - класс объектов предметной области;
2. атрибут - свойство сущности;
3. связь - отношение между сущностями;
4. ключ - атрибут или набор атрибутов, однозначно определяющий экземпляр сущности;
5. кардинальность - сколько экземпляров одной сущности связано с экземплярами другой;
6. обязательность - должна ли связь существовать всегда.

Пример:
Сущности: Самолет, Рейс, Билет, Пассажир.
Связь: один самолет выполняет много рейсов.
Кардинальность: aircrafts 1:M flights.
Обязательность: у рейса должен быть самолет, значит связь со стороны flights обязательная.

Типы связей:
1. 1:1 - один экземпляр связан максимум с одним экземпляром другой сущности;
2. 1:M - один экземпляр связан со многими;
3. M:N - многие связаны со многими, обычно преобразуется в промежуточную таблицу.

Пример M:N:
Пассажиры и рейсы связаны многие ко многим. В базе это решается через tickets и ticket_flights.


3. УТОЧНЕНИЕ, ОБОБЩЕНИЕ, КОМПОЗИЦИЯ, АГРЕГИРОВАНИЕ

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

Уточнение - обратный процесс, когда общая сущность делится на подклассы.
Пример: сущность Билет можно уточнить как Билет эконом-класса, Билет бизнес-класса, Билет комфорт-класса.

Суперкласс - общая сущность.
Подкласс - более конкретная сущность.

Композиция - сильная связь "часть-целое", где часть не имеет смысла без целого.
Пример: посадочный талон как часть перелета пассажира. Без конкретного билета и рейса такой талон не нужен.

Агрегирование - представление связи как самостоятельного объекта.
Пример: связь "пассажир летит рейсом" можно рассматривать как объект ticket_flights, у которого есть атрибут amount.


4. НОТАЦИЯ IDEF1X

IDEF1X - графическая нотация для моделирования данных. Она используется для описания сущностей, атрибутов, ключей и связей.

Основные элементы IDEF1X:
1. сущность - прямоугольник;
2. атрибуты - перечисляются внутри сущности;
3. первичный ключ - обычно отделяется верхней частью сущности;
4. связь - линия между сущностями;
5. идентифицирующая связь - дочерняя сущность зависит от родительской, ключ родителя входит в ключ потомка;
6. неидентифицирующая связь - дочерняя сущность имеет собственный ключ, а ключ родителя хранится как внешний ключ.

Пример:
aircrafts(aircraft_code) и flights(flight_id, aircraft_code).
Самолет связан с рейсами как 1:M.
Если flight_id является собственным ключом рейса, то связь неидентифицирующая: aircraft_code просто внешний ключ.

Что важно сказать на экзамене:
IDEF1X помогает перейти от предметной области к структуре таблиц. В модели сразу видно, какие таблицы нужны, где первичные ключи, где внешние ключи и какие связи между таблицами.


5. ПЕРЕХОД К ДАТАЛОГИЧЕСКОЙ И ФИЗИЧЕСКОЙ МОДЕЛИ

Даталогическая модель - логическая структура базы данных в терминах таблиц, столбцов, ключей и связей.

Физическая модель - реализация даталогической модели в конкретной СУБД. Здесь выбираются типы данных, индексы, ограничения, способы хранения.

Переход ER-модели к таблицам:
1. сущность становится таблицей;
2. атрибут становится столбцом;
3. первичный ключ становится PRIMARY KEY;
4. связь 1:M реализуется внешним ключом на стороне M;
5. связь M:N реализуется промежуточной таблицей.

Пример:
aircrafts(aircraft_code PRIMARY KEY)
flights(flight_id PRIMARY KEY, aircraft_code REFERENCES aircrafts(aircraft_code))


6. ПОДЗАПРОСЫ

Подзапрос - это запрос внутри другого запроса.

Где может использоваться подзапрос:
1. в WHERE;
2. в FROM;
3. в SELECT;
4. в HAVING;
5. вместе с EXISTS, IN, ANY, ALL.

Виды подзапросов:
1. скалярный - возвращает одно значение;
2. строковый - возвращает одну строку;
3. табличный - возвращает таблицу;
4. коррелированный - зависит от строки внешнего запроса.

Пример скалярного подзапроса:

SELECT book_ref, total_amount
FROM bookings
WHERE total_amount > (
    SELECT avg(total_amount)
    FROM bookings
);

Пример EXISTS:

SELECT t.ticket_no, t.passenger_name
FROM tickets t
WHERE EXISTS (
    SELECT 1
    FROM ticket_flights tf
    WHERE tf.ticket_no = t.ticket_no
);

Коррелированный подзапрос выполняется с учетом строки внешнего запроса. Он удобен, но иногда медленный.

Пример коррелированного подзапроса:

SELECT a.aircraft_code,
       a.model,
       (
           SELECT count(*)
           FROM flights f
           WHERE f.aircraft_code = a.aircraft_code
       ) AS flight_count
FROM aircrafts_data a;


7. CTE

CTE - common table expression, общее табличное выражение. Это временный именованный результат внутри одного SQL-запроса.

Синтаксис:

WITH name AS (
    SELECT ...
)
SELECT ...
FROM name;

Зачем нужны CTE:
1. сделать сложный запрос читаемым;
2. переиспользовать промежуточный результат;
3. разбить задачу на шаги;
4. писать рекурсивные запросы.

Пример CTE:

WITH flight_passengers AS (
    SELECT flight_id, count(*) AS cnt
    FROM boarding_passes
    GROUP BY flight_id
)
SELECT f.flight_id, f.flight_no, fp.cnt
FROM flights f
JOIN flight_passengers fp ON fp.flight_id = f.flight_id;

Пример CTE для поиска максимума по группам:

WITH delays AS (
    SELECT flight_id,
           departure_airport,
           actual_departure - scheduled_departure AS delay
    FROM flights
    WHERE actual_departure IS NOT NULL
),
max_delays AS (
    SELECT departure_airport, max(delay) AS max_delay
    FROM delays
    GROUP BY departure_airport
)
SELECT d.*
FROM delays d
JOIN max_delays m
  ON m.departure_airport = d.departure_airport
 AND m.max_delay = d.delay;

Важно:
В PostgreSQL CTE может быть раскрыто оптимизатором. Если написать MATERIALIZED, результат CTE будет сначала вычислен и сохранен, а потом прочитан.


8. ПРЕДСТАВЛЕНИЯ И МАТЕРИАЛИЗОВАННЫЕ ПРЕДСТАВЛЕНИЯ

Представление - сохраненный SQL-запрос, к которому можно обращаться как к таблице.

Синтаксис:

CREATE VIEW view_name AS
SELECT ...
FROM ...;

Пример:

CREATE VIEW flight_info AS
SELECT f.flight_id,
       f.flight_no,
       dep.airport_name AS departure_airport,
       arr.airport_name AS arrival_airport
FROM flights f
JOIN airports dep ON dep.airport_code = f.departure_airport
JOIN airports arr ON arr.airport_code = f.arrival_airport;

SELECT *
FROM flight_info
WHERE flight_no = 'PG0010';

Материализованное представление хранит результат запроса физически.

CREATE MATERIALIZED VIEW route_income AS
SELECT f.departure_airport, f.arrival_airport, sum(tf.amount) AS total_amount
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
GROUP BY f.departure_airport, f.arrival_airport;

Плюс materialized view - быстрее читать.
Минус - данные могут устаревать, нужно делать REFRESH MATERIALIZED VIEW.


9. ОКОННЫЕ ФУНКЦИИ

Оконная функция считает значение не по всей таблице, а по окну строк, связанному с текущей строкой.

Синтаксис:

function(...) OVER (
    PARTITION BY ...
    ORDER BY ...
    ROWS BETWEEN ... AND ...
)

PARTITION BY делит строки на группы.
ORDER BY задает порядок внутри группы.
ROWS или RANGE задает рамку окна.

Примеры оконных функций:
1. row_number() - номер строки;
2. rank() - ранг с пропусками;
3. dense_rank() - ранг без пропусков;
4. lag() - значение из предыдущей строки;
5. lead() - значение из следующей строки;
6. sum() over - накопительная сумма;
7. avg() over - среднее по окну.

Пример ранжирования самолетов по дальности:

SELECT aircraft_code,
       model,
       range,
       rank() OVER (ORDER BY range DESC) AS rnk,
       dense_rank() OVER (ORDER BY range DESC) AS dense_rnk
FROM aircrafts_data;

Пример накопительной выручки:

SELECT f.departure_airport,
       f.arrival_airport,
       f.scheduled_departure::date AS day,
       sum(tf.amount) AS day_amount,
       sum(sum(tf.amount)) OVER (
           PARTITION BY f.departure_airport, f.arrival_airport
           ORDER BY f.scheduled_departure::date
       ) AS running_amount
FROM flights f
JOIN ticket_flights tf ON tf.flight_id = f.flight_id
GROUP BY f.departure_airport, f.arrival_airport, f.scheduled_departure::date;

Отличие оконных функций от GROUP BY:
GROUP BY сворачивает строки.
Оконная функция сохраняет строки и добавляет вычисленное значение.


10. ФУНКЦИИ И ПРОЦЕДУРЫ PL/PGSQL

Функция возвращает значение или таблицу. Ее можно использовать в SELECT.

Синтаксис функции:

CREATE OR REPLACE FUNCTION function_name(param type)
RETURNS type AS $$
BEGIN
    ...
    RETURN ...;
END;
$$ LANGUAGE plpgsql;

Пример функции, которая возвращает рейсы из аэропорта:

CREATE OR REPLACE FUNCTION get_flights_from_airport(p_airport bpchar)
RETURNS TABLE(flight_id integer, flight_no char(6), scheduled_departure timestamptz) AS $$
BEGIN
    RETURN QUERY
    SELECT f.flight_id, f.flight_no, f.scheduled_departure
    FROM flights f
    WHERE f.departure_airport = p_airport;
END;
$$ LANGUAGE plpgsql;

SELECT *
FROM get_flights_from_airport('SVO');

Процедура не обязана возвращать значение. Ее вызывают через CALL. В процедуре можно делать действия с данными.

CREATE OR REPLACE PROCEDURE cancel_flights_by_aircraft(p_aircraft bpchar) AS $$
BEGIN
    UPDATE flights
    SET status = 'Cancelled'
    WHERE aircraft_code = p_aircraft;
END;
$$ LANGUAGE plpgsql;

CALL cancel_flights_by_aircraft('733');

Разница:
Функция - обычно для получения результата.
Процедура - обычно для выполнения действия.


11. ТРИГГЕРЫ И ТРИГГЕРНЫЕ ФУНКЦИИ

Триггер - объект БД, который автоматически вызывает функцию при событии INSERT, UPDATE, DELETE или TRUNCATE.

Виды по моменту выполнения:
1. BEFORE - до операции;
2. AFTER - после операции;
3. INSTEAD OF - вместо операции, обычно для представлений.

Виды по уровню:
1. FOR EACH ROW - для каждой строки;
2. FOR EACH STATEMENT - один раз для всей команды.

Триггерная функция должна возвращать trigger.
Внутри доступны OLD и NEW.
NEW - новая строка при INSERT или UPDATE.
OLD - старая строка при UPDATE или DELETE.

Пример проверки дальности самолета:

CREATE OR REPLACE FUNCTION check_aircraft_range()
RETURNS trigger AS $$
BEGIN
    IF NEW.range < 0 THEN
        RAISE EXCEPTION 'range must be positive';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_check_aircraft_range
BEFORE INSERT OR UPDATE ON aircrafts_data
FOR EACH ROW
EXECUTE FUNCTION check_aircraft_range();

Пример журнала удаления:

CREATE TABLE airports_delete_log (
    airport_code bpchar,
    deleted_at timestamptz DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_airport_delete()
RETURNS trigger AS $$
BEGIN
    INSERT INTO airports_delete_log(airport_code)
    VALUES (OLD.airport_code);
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_airport_delete
AFTER DELETE ON airports_data
FOR EACH ROW
EXECUTE FUNCTION log_airport_delete();


12. ТРАНЗАКЦИИ И ACID

Транзакция - логическая единица работы с базой данных. Она либо выполняется полностью, либо не выполняется.

ACID:
1. Atomicity - атомарность. Все действия транзакции выполняются как единое целое.
2. Consistency - согласованность. После транзакции БД остается в корректном состоянии.
3. Isolation - изолированность. Параллельные транзакции не должны некорректно влиять друг на друга.
4. Durability - долговечность. После COMMIT изменения сохраняются.

Основные команды:

BEGIN;
UPDATE bookings
SET total_amount = total_amount + 100
WHERE book_ref = '00000F';
COMMIT;

Если нужно отменить:

BEGIN;
UPDATE bookings
SET total_amount = total_amount + 100
WHERE book_ref = '00000F';
ROLLBACK;

Сериализация транзакций - такой результат параллельного выполнения, как будто транзакции выполнялись последовательно.


13. УРОВНИ ИЗОЛЯЦИИ ТРАНЗАКЦИЙ

Уровень изоляции определяет, какие изменения других транзакций видит текущая транзакция.

Основные уровни:
1. READ UNCOMMITTED;
2. READ COMMITTED;
3. REPEATABLE READ;
4. SERIALIZABLE.

В PostgreSQL READ UNCOMMITTED фактически работает как READ COMMITTED.

READ COMMITTED:
Каждый оператор видит только зафиксированные данные на момент начала оператора. Один и тот же SELECT в одной транзакции может увидеть разные данные, если другая транзакция сделала COMMIT.

REPEATABLE READ:
Транзакция видит снимок данных на момент начала транзакции. Повторный SELECT дает тот же результат.

SERIALIZABLE:
Самый строгий уровень. СУБД старается обеспечить результат, эквивалентный последовательному выполнению транзакций. Возможны ошибки сериализации, тогда транзакцию нужно повторить.

Пример:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT total_amount FROM bookings WHERE book_ref = '00000F';
SELECT total_amount FROM bookings WHERE book_ref = '00000F';
COMMIT;


14. БЛОКИРОВКИ

Блокировка нужна, чтобы параллельные транзакции не портили данные друг другу.

Блокировки бывают:
1. строковые;
2. табличные;
3. явные;
4. неявные.

Пример строковой блокировки:

BEGIN;
SELECT *
FROM bookings
WHERE book_ref = '00000F'
FOR UPDATE;
COMMIT;

FOR UPDATE блокирует выбранные строки для изменения другими транзакциями.

Пример табличной блокировки:

BEGIN;
LOCK TABLE bookings IN SHARE MODE;
COMMIT;

Важно:
Строковая блокировка обычно точнее и лучше, потому что не мешает работе со всей таблицей.
Табличная блокировка сильнее и может заблокировать больше операций.


15. ИНДЕКСЫ И ИНДЕКСНЫЕ СТРУКТУРЫ

Индекс - структура данных, которая ускоряет поиск строк, но требует места и замедляет INSERT, UPDATE, DELETE.

Основные виды индексов:
1. B-tree;
2. Hash;
3. GiST;
4. GIN;
5. BRIN;
6. bitmap-индексы как подход в некоторых СУБД.

B-tree - основной индекс в PostgreSQL. Хорошо подходит для:
1. =;
2. <, >, <=, >=;
3. BETWEEN;
4. ORDER BY;
5. LIKE 'abc%'.

Пример:

CREATE INDEX ticket_flights_amount_idx
ON ticket_flights(amount);

SELECT *
FROM ticket_flights
WHERE amount > 200000;

Bitmap Scan:
Планировщик может сначала найти подходящие строки по индексу, построить bitmap, а потом прочитать страницы таблицы. Это удобно, когда строк не одна-две, но и не вся таблица.

Index Only Scan:
Если все нужные данные есть в индексе и видимость строк известна, PostgreSQL может не читать таблицу.

Пример:

SELECT amount
FROM ticket_flights
ORDER BY amount DESC
LIMIT 1;

После индекса по amount план может использовать Index Only Scan Backward.


16. EXPLAIN И ЧТЕНИЕ ПЛАНА ЗАПРОСА

EXPLAIN показывает, как PostgreSQL планирует выполнить запрос.

Основной синтаксис:

EXPLAIN
SELECT ...

EXPLAIN ANALYZE реально выполняет запрос и показывает фактическое время.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM ticket_flights
WHERE amount > 200000;

Что смотреть:
1. тип сканирования: Seq Scan, Index Scan, Bitmap Heap Scan;
2. тип соединения: Nested Loop, Hash Join, Merge Join;
3. Sort, HashAggregate, GroupAggregate;
4. cost - оценочная стоимость;
5. rows - оценка строк;
6. actual time - фактическое время;
7. loops - сколько раз узел выполнялся;
8. Buffers - чтение из памяти и с диска;
9. Batches и Memory Usage у Hash.

Пример пояснения:
Если в плане есть Parallel Seq Scan, Gather, Partial Aggregate и Finalize Aggregate, запрос выполняется параллельно. Рабочие процессы считают частичные результаты, а главный процесс собирает итог.


17. СОЕДИНЕНИЯ ТАБЛИЦ

Основные алгоритмы соединений:
1. Nested Loop;
2. Hash Join;
3. Merge Join.

Nested Loop:
Для каждой строки внешней таблицы ищутся строки во внутренней таблице. Хорошо работает, если внешняя таблица маленькая или есть индекс.
Сложность без индекса примерно O(n * m).

Пример:

SELECT *
FROM flights f
JOIN airports a ON a.airport_code = f.departure_airport
WHERE f.flight_id = 1;

Hash Join:
Для одной таблицы строится hash-таблица, потом строки второй таблицы ищутся по hash. Хорошо для больших неотсортированных наборов.
Сложность примерно O(n + m).

Пример:

SELECT f.flight_id, a.airport_name
FROM flights f
JOIN airports a ON a.airport_code = f.arrival_airport;

Merge Join:
Обе таблицы должны быть отсортированы по ключу соединения. Затем данные сливаются в одном проходе.
Сложность примерно O(n + m), но может добавиться стоимость сортировки.

Пример:

SELECT t.ticket_no, tf.flight_id
FROM tickets t
JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
ORDER BY t.ticket_no;


18. РАСПРЕДЕЛЕННЫЕ СУБД

Распределенная СУБД хранит данные на нескольких узлах, но пользователь работает с ними как с единой базой.

Зачем нужны:
1. масштабирование;
2. отказоустойчивость;
3. распределение нагрузки;
4. хранение данных ближе к пользователям.

Основные способы организации:
1. репликация - копии данных на нескольких узлах;
2. фрагментация - разные части данных на разных узлах;
3. шардирование - горизонтальное разделение таблиц по ключу;
4. федерация - несколько БД объединяются общим интерфейсом.

Плюсы:
1. можно обрабатывать больше данных;
2. система устойчивее к отказам;
3. можно распределять нагрузку.

Минусы:
1. сложнее обеспечивать согласованность;
2. сложнее транзакции;
3. возможны сетевые задержки;
4. сложнее администрирование.

Примеры: PostgreSQL с репликацией и шардингом, CockroachDB, Cassandra, MongoDB cluster.


19. СИСТЕМЫ ХРАНЕНИЯ ДАННЫХ И ЗНАНИЙ

Основные типы систем хранения:
1. реляционные БД;
2. key-value хранилища;
3. документоориентированные БД;
4. колоночные БД;
5. графовые БД;
6. объектные БД;
7. хранилища знаний.

Реляционные БД:
Данные хранятся в таблицах. Есть SQL, ключи, ограничения, транзакции.
Примеры: PostgreSQL, MySQL, Oracle.

Key-value:
Данные хранятся как ключ и значение. Очень быстрый доступ по ключу.
Примеры: Redis, Riak, DynamoDB.

Документоориентированные:
Данные хранятся документами, обычно JSON или BSON. Удобно для гибкой структуры.
Примеры: MongoDB, CouchDB.

Колоночные:
Данные хранятся по столбцам. Хорошо для аналитики.
Примеры: ClickHouse, Cassandra, HBase.

Графовые:
Данные хранятся как вершины и ребра. Хорошо для связей.
Примеры: Neo4j.

Хранилища знаний:
Хранят не только данные, но и связи, правила, факты, онтологии.
Примеры: RDF-хранилища, OWL-онтологии, экспертные системы.


20. KEY-VALUE ХРАНИЛИЩА

Key-value хранилище хранит данные в виде ключ - значение.

Пример:
ключ: passenger:123
значение: JSON с данными пассажира

Особенности:
1. быстрый доступ по ключу;
2. простая модель данных;
3. хорошо подходит для кеша, сессий, счетчиков;
4. плохо подходит для сложных JOIN и аналитических запросов.

Пример Redis:

SET passenger:123 "Ivan Petrov"
GET passenger:123

Плюсы:
1. высокая скорость;
2. простота;
3. горизонтальное масштабирование.

Минусы:
1. нет полноценной реляционной модели;
2. сложные выборки нужно делать в приложении;
3. меньше ограничений целостности.


21. ДОКУМЕНТООРИЕНТИРОВАННЫЕ БАЗЫ ДАННЫХ

Документоориентированная БД хранит данные как документы, чаще всего JSON или BSON.

Пример документа:

{
  "ticket_no": "0005432000987",
  "passenger_name": "Ivan Petrov",
  "flights": [
    {"flight_id": 10, "amount": 12500},
    {"flight_id": 11, "amount": 9300}
  ]
}

Особенности:
1. гибкая структура;
2. удобно хранить вложенные данные;
3. можно хранить разные документы в одной коллекции;
4. нет обязательной жесткой схемы как в реляционной БД.

Плюсы:
1. удобно для JSON-данных;
2. быстро разрабатывать;
3. хорошо масштабируется горизонтально.

Минусы:
1. сложнее поддерживать целостность;
2. JOIN ограничены или отсутствуют;
3. дублирование данных встречается чаще.

Пример: MongoDB.