Фундаментальный теоретический конспект

1. Реляционная таблица как основа физической модели БД

В реляционной модели данных Эдгара Кодда информация представляется в виде двумерных математических отношений, которые на физическом уровне СУБД реализуются как таблицы (Tables). Каждая строка таблицы называется записью или кортежем (Tuple), а каждый столбец — полем или атрибутом (Attribute).

К реляционной таблице предъявляются жесткие архитектурные требования:

• Атомарность значений: каждое значение в ячейке неделимо (1-я нормальная форма). Нельзя хранить список телефонов или перечень товаров через запятую в одной ячейке.
• Уникальность строк: в таблице не может существовать двух абсолютно одинаковых строк (гарантируется первичным ключом).
• Однородность столбцов: все значения в одном столбце принадлежат строго одному базовому типу данных.
• Порядок не имеет значения: порядок строк и столбцов не влияет на семантический смысл запросов.

1.1. Блок-схема логической структуры реляционной таблицы

2. Теория первичных ключей: Естественные vs Суррогатные

Первичный ключ (Primary Key, PK) — это минимальный набор атрибутов, однозначно идентифицирующий кортеж в отношении. Первичный ключ эквивалентен комбинации ограничений NOT NULL и UNIQUE, а СУБД автоматически создает под него кластеризованный или уникальный B-Tree индекс.

2.1. Естественные ключи (Natural Keys)

Естественный ключ формируется из реквизитов, уже существующих в реальном мире: номер паспорта гражданина, государственный номер автомобиля, ISBN печатного издания, номер ИНН организации.

• Плюс: Не требует выделения дополнительного столбца, данные информативны сами по себе при выборке.
• Минус 1 (Изменяемость): Человек может сменить паспорт, организация — название, автомобиль — номер при смене региона. Смена первичного ключа — крайне тяжелая операция для БД.
• Минус 2 (Производительность): Строковые естественные ключи (например, VIN 17 символов или ISBN 13 символов) требуют в 4–5 раз больше байт на запись, чем 4-байтовый INT. Поскольку первичный ключ копируется во все внешние ключи всех дочерних таблиц, размер B-Tree индексов многократно раздувается, а соединение таблиц (JOIN) замедляется.

2.2. Суррогатные ключи (Surrogate Keys)

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

3. Физические типы данных в реляционных СУБД

Выбор типа данных определяет требуемый объем оперативной памяти (RAM) для кэша страниц, размер таблицы на диске и возможность использования специализированных функций фильтрации.

4. Ограничения целостности (Integrity Constraints)

Ограничения целостности представляют собой декларативные правила, которые СУБД проверяет при каждой операции INSERT, UPDATE и DELETE. Если операция нарушает хотя бы одно ограничение, транзакция прерывается с ошибкой.

4.1. Декларативные ограничения

• NOT NULL: запрещает хранение неопределенного значения NULL в столбце. Обязательно для всех ключевых полей, фамилий, дат событий.
• UNIQUE: требует уникальности каждого непустого значения. Поддерживается уникальным B-Tree индексом.
• DEFAULT значение: подставляет указанное константное или функциональное значение, если поле пропущено в операторе INSERT.
• CHECK (логическое выражение): выполняет валидацию значений столбца или строки по произвольному предикату.

4.2. Примеры предикатных ограничений CHECK

-- Проверка диапазона значений
CHECK (god_vypuska >= 1990 AND god_vypuska <= EXTRACT(YEAR FROM CURRENT_DATE) + 1)

-- Сравнение двух атрибутов одной строки (темпоральная целостность)
CHECK (data_vyezda > data_zaezda)
CHECK (probeg_pri_vozvrashchenii >= probeg_pri_vyezde)

-- Ограничение множества допустимых статусов
CHECK (status IN ('доступен', 'занят', 'уборка', 'ремонт'))

-- Финансовая валидация (цена продажи не ниже закупки)
CHECK (cena_prodazhi >= cena_zakupki AND cena_zakupki >= 0)

5. Внешние ключи (Foreign Key) и ссылочная целостность

Внешний ключ (Foreign Key, FK) — это колонка или группа колонок дочерней таблицы, значения которых обязаны совпадать со значениями первичного (или уникального) ключа родительской таблицы.

5.1. Каскадные действия при DELETE и UPDATE

Секции ON DELETE и ON UPDATE определяют поведение реляционного движка, когда запись в родительской таблице удаляется или изменяется:

• RESTRICT / NO ACTION: СУБД блокирует удаление/изменение родительской строки и возвращает ошибку, если на нее ссылается хотя бы одна дочерняя строка. Используется в 90% связей учетных систем для защиты от случайного уничтожения истории.
• CASCADE: СУБД автоматически физически удаляет все зависимые дочерние записи. Используется строго для подчиненных спецификаций (например, пункты накладной при удалении накладной).
• SET NULL: СУБД заменяет значение внешнего ключа в дочерних строках на NULL (столбец внешнего ключа должен допускать NULL).
• SET DEFAULT: СУБД устанавливает в дочерних строках значение по умолчанию.

6. Эталонный пример физической схемы (DDL SQL)

Ниже представлен профессиональный DDL-скрипт создания связанных таблиц в СУБД PostgreSQL с соблюдением всех стандартов именования, ключей и ограничений:

-- 1. Справочник категорий номеров
CREATE TABLE kategorii_nomerov (
    id_kategorii SMALLINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nazvanie VARCHAR(50) NOT NULL UNIQUE,
    vmestimost_mest SMALLINT NOT NULL CHECK (vmestimost_mest BETWEEN 1 AND 10),
    bazovaya_cena_za_sutki NUMERIC(10,2) NOT NULL CHECK (bazovaya_cena_za_sutki > 0)
);

-- 2. Номерной фонд отеля
CREATE TABLE nomera (
    id_nomera INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nomer_komnaty VARCHAR(10) NOT NULL UNIQUE,
    etazh SMALLINT NOT NULL CHECK (etazh BETWEEN 1 AND 50),
    id_kategorii SMALLINT NOT NULL REFERENCES kategorii_nomerov(id_kategorii)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'доступен' 
        CHECK (status IN ('доступен', 'занят', 'уборка', 'ремонт'))
);

-- 3. Анкеты гостей
CREATE TABLE gosti (
    id_gostya INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pasport_seriya_nomer VARCHAR(30) NOT NULL UNIQUE,
    familiya VARCHAR(50) NOT NULL,
    imya VARCHAR(50) NOT NULL,
    otchestvo VARCHAR(50),
    telefon VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    data_rozhdeniya DATE NOT NULL 
        CHECK (data_rozhdeniya <= CURRENT_DATE - INTERVAL '18 years')
);

-- 4. Журнал бронирований
CREATE TABLE bronirovaniya (
    id_broni BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    id_nomera INT NOT NULL REFERENCES nomera(id_nomera)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    id_gostya INT NOT NULL REFERENCES gosti(id_gostya)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    data_zaezda DATE NOT NULL,
    data_vyezda DATE NOT NULL,
    itogovaya_stoimost NUMERIC(10,2) NOT NULL CHECK (itogovaya_stoimost >= 0),
    status_broni VARCHAR(20) NOT NULL DEFAULT 'создано'
        CHECK (status_broni IN ('создано', 'подтверждено', 'заселен', 'завершено', 'отменено')),
    sozdano_v TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_dates CHECK (data_vyezda > data_zaezda)
);

7. Вопросы для самопроверки