Практическое занятие №1: ключи, объекты и проект БД, таблицы

1. От концепции к физической реализации

2. Архитектура реляционной таблицы

Реляционная таблица — это физическое воплощение математического отношения:

• Кортеж (Запись / Row): конкретный экземпляр объекта (например, один автомобиль или один номер отеля).
• Атрибут (Колонка / Column): строго типизированное свойство объекта.
• Атомарность значений: в ячейке хранится строго одно неделимое значение (требование 1NF).
• Уникальность кортежей: строки обязаны различаться хотя бы значением первичного ключа.

3. Первичный ключ (Primary Key)

Три обязательных реляционных аксиомы первичного ключа:

1. Уникальность: исключает существование двух строк с совпадающим ключом.
2. Непустота (Non-nullability): первичный ключ никогда не может принимать значение NULL.
3. Неизменяемость (Immutability): значение ключа объекта не должно изменяться на протяжении всей его жизни в БД.

По первичному ключу СУБД строит кластеризованный или уникальный B-Tree индекс.

4. Естественные vs Суррогатные ключи

5. Генерация суррогатных ключей

Современные реляционные СУБД (PostgreSQL, MySQL, Oracle, MSSQL) поддерживают три основных стандарта:

• GENERATED ALWAYS AS IDENTITY — стандарт SQL:2003 (предпочтительнее устаревшего SERIAL). Безопасен при явных вставках.
• BIGINT (BIGSERIAL) — 8 байт, обязателен для журналов транзакций, путевых листов, чеков, где счетчик быстро превысит 2.14 млрд.
• UUID (Universally Unique Identifier) — 16 байт, генерируется клиентом автономно. Незаменим в распределенных и оффлайн-приложениях.

-- Стандарт SQL:2003 (PostgreSQL)
id_avto INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
vin VARCHAR(17) NOT NULL UNIQUE

6. Типы данных: Числа и Деньги

Двоичное представление чисел с плавающей точкой (IEEE 754) дает ошибку округления: 0.1 + 0.2 = 0.30000000000000004.

• Для цен и валют: строго NUMERIC(10,2) или DECIMAL(12,2) (точные десятичные дроби).
• Для счетчиков и ID: INTEGER (до 2.14 млрд) или BIGINT.
• Для малых величин: SMALLINT (до 32 767 — этажи, вместимость, стаж).

7. Строки, Даты и Булевы флаги

• VARCHAR(n) — строка переменной длины с ограничением (ФИО, email, название). Защищает от переполнения памяти.
• CHAR(n) — строка строго фиксированной длины (VIN ровно 17 символов, код валюты ISO ровно 3 символа).
• DATE и TIMESTAMP — календарные даты и временные метки. Хранить дату строкой VARCHAR(10) — грубая ошибка!
• BOOLEAN — логический флаг (TRUE, FALSE, NULL) для статусов активности и архивности.

is_aktivnyy BOOLEAN NOT NULL DEFAULT TRUE,
data_vyezda TIMESTAMP NOT NULL,
vin CHAR(17) NOT NULL UNIQUE

8. Декларативная защита данных

Ограничения целостности выполняются на уровне ядра СУБД и не зависят от ошибок в коде веб-сервера или мобильного клиента:

• NOT NULL: запрещает неопределенные значения. Обязателен для всех смысловых атрибутов.
• UNIQUE: предотвращает логическое дублирование записей (госномера, паспорта, логины).
• DEFAULT: значение по умолчанию при создании записи (например, CURRENT_TIMESTAMP или 0).

9. Ограничения CHECK (Бизнес-правила)

Ограничение CHECK проверяет истинность логического выражения при каждой вставке или обновлении строки:

-- 1. Валидация жизненного цикла (перечисление)
CHECK (status IN ('доступен', 'занят', 'уборка', 'ремонт'))

-- 2. Темпоральная целостность (дата выезда позже заезда)
CHECK (data_vyezda > data_zaezda)

-- 3. Защита от отрицательных остатков и цен
CHECK (probeg_pri_vozvrashchenii >= probeg_pri_vyezde)
CHECK (cena_prodazhi >= cena_zakupki AND cena_zakupki >= 0)

10. Внешний ключ (Foreign Key)

Внешний ключ физически связывает таблицы при реализации отношений 1:M и 1:1:

• Тип данных внешнего ключа обязан в точности совпадать с типом первичного ключа родителя (например, INT ссылается на INT).
• Если связь обязательная — колонка FK снабжается ограничением NOT NULL.
• Для ускорения JOIN на внешние ключи обязательно создаются индексы.

11. Каскадные политики: ON DELETE

ON DELETE SET NULL зануляет ссылку при удалении родителя (например, уволен мастер-приемщик, но заказ сохранился).

12. Анатомия DDL: Полный пример

CREATE TABLE putevye_listy (
    id_putevogo_lista BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nomer_dokumenta   VARCHAR(30) NOT NULL UNIQUE,
    id_avto           INT NOT NULL REFERENCES avtomobili(id_avto)
                          ON UPDATE CASCADE ON DELETE RESTRICT,
    id_voditelya      INT NOT NULL REFERENCES voditeli(id_voditelya)
                          ON UPDATE CASCADE ON DELETE RESTRICT,
    probeg_pri_vyezde INT NOT NULL CHECK (probeg_pri_vyezde >= 0),
    probeg_pri_vozvrashchenii INT,
    CONSTRAINT chk_probeg CHECK (probeg_pri_vozvrashchenii >= probeg_pri_vyezde)
);

13. Топ-3 ошибок начинающих инженеров

1. «Универсальный VARCHAR»: хранение чисел, дат и цен в строках переменной длины. Ломает сортировку, поиск диапазонов и индексы.
2. «Слепой CASCADE»: установка каскадного удаления на справочники и клиентов, приводящая к уничтожению связанной истории.
3. «Суррогатный ключ без UNIQUE»: создание id SERIAL без ограничения уникальности на паспорт или VIN, что допускает дублирование реальных объектов.