Base

database

время чтения ~16 мин.

Базы данных

О БД

Базы Данных - БД - это организованная коллекция информации и данных.

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

Базы данных решают следующие проблемы:

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

Типы баз данных

  1. Реляционные БД (SQL) Данные организованы в виде таблиц с установленными связями между ними. Используют язык запросов SQL. Примеры: PostgreSQL, MySQL, SQLite, Oracle, Microsoft SQL Server.
  2. Документо-ориентированные БД (NoSQL) Хранят данные в виде документов (обычно JSON). Примеры: MongoDB, CouchDB. Хорошо подходят для работы с полуструктурированными данными.
  3. Ключ-значение Простейшая модель, где каждый элемент хранится как пара ключ-значение. Примеры: Redis, Memcached, localStorage. Используются для кэширования и быстрого доступа.
  4. Графовые БД Оптимизированы для работы со связанными данными. Примеры: Neo4j, ArangoDB.

Популярные СУБД

  1. PostgreSQL - мощная объектно-реляционная СУБД с открытым исходным кодом. Поддерживает сложные запросы, транзакции, расширяемость. Хорошо подходит для enterprise-приложений.
  2. SQLite - легковесная встраиваемая СУБД. Не требует отдельного сервера, хранит данные в одном файле. Идеальна для мобильных приложений, прототипов и небольших проектов.
  3. MongoDB - документо-ориентированная NoSQL база данных. Гибкая схема, хорошая масштабируемость. Подходит для приложений с быстро меняющейся структурой данных.
  4. Prisma - современный ORM (Object-Relational Mapping) для Node.js и TypeScript. Упрощает работу с базами данных, предоставляя типобезопасный API.

Проектирование реляционных БД

Основы проектирования

Создание БД и таблиц

Введение

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

Качественное проектирование БД обеспечивает:

  • Минимизацию избыточности данных
  • Предотвращение аномалий при вставке, обновлении и удалении
  • Эффективность выполнения запросов
  • Простоту сопровождения и развития системы

Основные этапы проектирования:

  1. Анализ предметной области и выделение сущностей
  2. Определение атрибутов сущностей (атомизация атрибутов, и выделение хранимых сущностей)
  3. Установление связей между сущностями (идентификация первичных ключей)
  4. Определение отношений между таблицами (внешние ключи)
  5. Нормализация структуры

Сущность (entity) - тип объектов, которые должны храниться в базе данных

Атрибут - это свойство, которое описывает некоторую характеристику объекта.

Восходящий и нисходящий подходы

Существует два основных подхода к проектированию баз данных:

Восходящий подход (Bottom-Up)

Начинается с деталей и движется к общей структуре:

  1. Собираются все необходимые атрибуты (например: имя студента, название курса, учебная должность преподавателя, имя преподавателя, электронный адрес студента)
  2. Атрибуты группируются по смыслу (например: Студент, Курс, Преподаватель)
  3. Группы преобразуются в таблицы
  4. Определяются связи между таблицами
СтудентПреподавательКурс
Имя студентаИмя преподавателяИмя студента
Название курсаДолжность преподавателяИмя преподавателя
Дата рождения студентаНазвание курсаНазвание курса
Электронный адрес студента
Телефон студента

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

Нисходящий подход (Top-Down)

Начинается с общей картины и постепенно детализируется:

  1. Определяются основные сущности (например: Студент, Курс, Преподаватель)
  2. Для каждой сущности определяются атрибуты (для студента например: имя студента, его адрес, телефон, год его рождения, рост, вес - последние два можно вычеркнуть, так как они в рамках описываемой системы не имеют роли)
  3. Устанавливаются связи между сущностями
  4. Применяется нормализация

Преимущества: естественный для понимания, хорошо подходит для сложных систем.

На практике часто используется комбинированный подход, когда некоторые части системы формируются по bottom-up, а другие по top-down.

Атомизация атрибутов

Атомизация - это разбиение сложных атрибутов на простые, неделимые компоненты. Это важный принцип реляционных баз данных.

Примеры атомизации:

Плохо:

  • ФИО → “Иванов Иван Иванович”
  • Адрес → “г. Москва, ул. Ленина, д. 10, кв. 5”

Хорошо:

  • Фамилия, Имя, Отчество → отдельные поля
  • Город, Улица, Дом, Квартира → отдельные поля

Преимущества атомизации:

  • Возможность поиска по отдельным компонентам (например, все люди с фамилией Иванов)
  • Возможность сортировки по компонентам
  • Простота валидации данных
  • Избежание проблем с разным форматом записи

Когда не нужна избыточная атомизация:

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

Домен

Домен - это набор допустимых значений для атрибута. Определение домена включает:

Например, выше были определены атрибуты сущности “Студент”. Определим используемые домены:

  • Имя. Домен представляет все возможные имена, которые могут использоваться. Каждое имя представляет строку длиной максимум 20 символов (маловероятно, что нам могут встретиться имена свыше 20 символов).
  • Фамилия. Домен представляет все возможные фамилии, которые могут использоваться. Каждая фамилия представляет строку длиной максимум 20 символов.
  • Год рождения. Домен представляет все года рождения. Каждый год является числовым значением от 1950 до 2017.

Такие домены можно разделить на группы:

  • Тип данных
    • Числовые: INTEGER, DECIMAL, FLOAT
    • Строковые: VARCHAR, TEXT, CHAR
    • Дата/время: DATE, TIMESTAMP, TIME
    • Логические: BOOLEAN
    • И другие специфические типы
  • Диапазон значений
    • Возраст: от 0 до 150
    • Оценка: от 1 до 5
    • Год: от 1900 до текущего
  • Формат
    • Email: должен содержать @ и домен
    • Телефон: определенное количество цифр
    • ИНН: 10 или 12 цифр
  • Ограничения
    • CHECK constraints для проверки условий
    • DEFAULT значения
    • UNIQUE для уникальности

Пример определения домена в коде SQL:

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INTEGER CHECK (age >= 16 AND age <= 100),
    email VARCHAR(100) UNIQUE,
    gpa DECIMAL(3,2) CHECK (gpa >= 0.0 AND gpa <= 5.0),
    enrollment_date DATE DEFAULT CURRENT_DATE
);
Определитель NULL

NULL - это специальное значение, обозначающее отсутствие данных. Важно понимать, что NULL ≠ 0 и NULL ≠ пустая строка.

Когда использовать NULL:

  • Данные еще не известны (например, дата окончания для текущего проекта)
  • Данные неприменимы (например, девичья фамилия для мужчины)
  • Данные опциональны (например, второй номер телефона или в рамках нашей системы номер телефона просто не обязателен)

Ограничения с NULL:

  • Первичный ключ не может содержать NULL
  • Атрибуты, помеченные как NOT NULL, обязательны для заполнения
  • Операции с NULL возвращают NULL (NULL + 5 = NULL)
  • Для проверки NULL используется IS NULL, а не = NULL

Пример:

CREATE TABLE Employees (
    employee_id INTEGER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,  -- Обязательное поле
    last_name VARCHAR(50) NOT NULL,    -- Обязательное поле
    middle_name VARCHAR(50),           -- Может быть NULL
    phone VARCHAR(20) NOT NULL,        -- Обязательное поле
    alternate_phone VARCHAR(20)        -- Может быть NULL
);

Рекомендации:

  • Минимизируйте использование NULL где возможно
  • Используйте NOT NULL для критически важных атрибутов
  • Документируйте семантику NULL для каждого атрибута
  • Рассмотрите использование значений по умолчанию вместо NULL там, где это уместно

Ключи

Ключи - это атрибуты или комбинации атрибутов, которые уникально идентифицируют записи в таблице и устанавливают связи между таблицами.

Суперключ (Superkey)

Любой набор атрибутов, который однозначно идентифицирует каждую запись в таблице.

Например, у нас есть сущность Student, которая имеет следующие атрибуты:

  • FirstName (имя)
  • LastName (фамилия)
  • Year (год рождения)
  • Phone (номер телефона)

Из этого набора мы можем составить такие суперключи:

  • {first_name, last_name, year, phone}
  • {first_name, year, phone}
  • {last_name, year, phone}
  • {first_name, phone}
  • {last_name, phone}
  • {year, phone}
  • {phone}

Из этого большого набора мы можем выбрать один номер телефона, так как он всегда будет уникален.

А набор {first_name, last_name, year} нам не подойдёт, так как может быть несколько студентов с одинаковой датой рождения и ФИО.

Потенциальный ключ (Candidate Key)

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

Требования:

  • Уникальность - каждая запись имеет уникальное значение
  • Минимальность - удаление любого атрибута нарушает уникальность
  • Наличие значения - не содержит NULL значений

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

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

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

Характеристики:

  • Уникальность - нет двух записей с одинаковым значением
  • Обязательность - не может быть NULL
  • Неизменность - значение не должно меняться со временем
  • Простота - желательно использовать минимальное количество атрибутов

Типы первичных ключей:

  1. Естественный ключ - использует существующие атрибуты (email, ИНН, номер паспорта) 
    CREATE TABLE Users (
    email VARCHAR(100) PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
  2. Суррогатный ключ - искусственно созданный идентификатор (обычно автоинкремент) 
    CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL,
    username VARCHAR(50) NOT NULL
);
  3. Составной ключ - комбинация нескольких атрибутов 
    CREATE TABLE Enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);
Альтернативный ключ (Alternate Key)

Потенциальные ключи, которые не были выбраны в качестве первичного. Обычно помечаются как UNIQUE.

Например, в нашей системе для аутентификации используются email и phone. Это два уникальных значения, которые можно использовать, как первичный ключ. Но мы выбрали всё равно суррогатный ключ. Остальные уникальные значения мы пометим, как Unique, так как они всегда будут уникальными.

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,        -- Первичный ключ
    email VARCHAR(100) UNIQUE NOT NULL, -- Альтернативный ключ
    phone VARCHAR(20) UNIQUE            -- Альтернативный ключ
);
Рекомендации по выбору первичного ключа:
  • Предпочитайте суррогатные ключи для таблиц сущностей
  • Используйте составные ключи для таблиц связей many-to-many
  • Избегайте первичных ключей, которые могут измениться
  • Используйте короткие ключи для лучшей производительности индексов

Внешние ключи и связи

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

Основные термины:

  • Родительская (главная) таблица - таблица, на которую ссылаются
  • Дочерняя (зависимая) таблица - таблица, содержащая внешний ключ
Типы связей
Один-к-одному (One-to-One / 1:1)

Одна запись в таблице А связана максимум с одной записью в таблице Б.

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

Пример: Пользователь и его профиль

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
 
CREATE TABLE UserProfiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE NOT NULL,  -- UNIQUE делает связь 1:1
    bio TEXT,
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
Один-ко-многим (One-to-Many / 1:N)

Одна запись в таблице А может быть связана с множеством записей в таблице Б. Самый распространенный тип связи.

Например, у нас есть один футболист, который играет в составе команды. Команда - будет таблицей с отношением к группе футболистов. Тут применяется отношение 1 ко многим.

Пример: Автор и его статьи

CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
 
CREATE TABLE Articles (
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);
Многие-ко-многим (Many-to-Many / N:N)

Множество записей в таблице А связаны с множеством записей в таблице Б. Реализуется через промежуточную таблицу (таблицу связей).

Например, у нас есть поток, в рамках которого есть огромное количество курсов, на которые ходят большое количество студентов. Студенты могут ходить на несколько курсов сразу.

Пример: Студенты и курсы

CREATE TABLE Students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
 
CREATE TABLE Courses (
    course_id SERIAL PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);
 
-- Промежуточная таблица
CREATE TABLE Enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    grade DECIMAL(3,2),
    FOREIGN KEY (student_id) REFERENCES Students(student_id), -- внешняя связь на таблицу студентов
    FOREIGN KEY (course_id) REFERENCES Courses(course_id), -- внешняя связь на таблицу курсов
    UNIQUE(student_id, course_id)  -- Студент не может записаться на курс дважды
);
Ссылочная целостность

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

Есть несколько аномалий, которые нужно решать в коде:

  • Аномалия удаления (deletion anomaly) - аномалия, которая возникает при удалении строки из главной таблицы, когда внешний ключ из зависимой таблицы продолжает ссылаться на удаленную строку из главной таблицы
  • Аномалия вставки (insertion anomaly) - аномалия, которая возникает при вставке строки в зависимую таблицу, когда внешний ключ из зависимой таблицы не соответствует первичному ключу ни одной из строк из главной таблицы.
  • Аномалии обновления (update anomaly) - это аномалия, когда несколько строк одной таблицы могут содержать данные, которые принадлежат одному и тому же объекту. При изменении данных в одной строке они могу прийти в противоречие с данными из другой строки.

Действия при удалении/обновлении:

  1. CASCADE - каскадное изменение/удаление

    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

    При удалении автора удаляются все его статьи.

  2. SET NULL - установка NULL

    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
    ON DELETE SET NULL

    При удалении автора поле author_id в статьях становится NULL.

  3. SET DEFAULT - установка значения по умолчанию

    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
    ON DELETE SET DEFAULT

  4. RESTRICT / NO ACTION - запрет операции

    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
    ON DELETE RESTRICT

    Нельзя удалить автора, пока есть его статьи.

Аномалии данных:

  1. Аномалия вставки - невозможно добавить запись с несуществующим внешним ключом

    -- Ошибка: автора с id=999 не существует
INSERT INTO Articles (title, author_id) VALUES ('Статья', 999);

  2. Аномалия удаления - удаление записи может привести к потере связанных данных

    -- Что делать со статьями при удалении автора?
DELETE FROM Authors WHERE author_id = 1;

  3. Аномалия обновления - изменение первичного ключа требует обновления всех ссылок

    -- Требуется обновить все внешние ключи
UPDATE Authors SET author_id = 100 WHERE author_id = 1;

Рекомендации:

  • Нужно всегда определять внешние ключи явно через FOREIGN KEY
  • Выбирайте подходящее действие ON DELETE в зависимости от бизнес-логики
  • Используйте индексы на столбцах с внешними ключами для производительности
  • Документируйте связи между таблицами

Нормализация

Нормализация - это процесс организации данных в базе данных для минимизации избыточности и предотвращения аномалий при операциях с данными. Нормализация разделяет большие таблицы на меньшие и связывает их через внешние ключи.

Цели нормализации:

  • Устранение избыточности данных (дублирования)
  • Предотвращение аномалий вставки, обновления и удаления
  • Обеспечение целостности данных
  • Упрощение поддержки базы данных

Основные нормальные формы:

  • Первая нормальная форма (1NF) - сохраняемые данные на пересечении строк и столбцов должны представлять скалярное значение, а таблицы не должны содержать повторяющихся строк
  • Вторая нормальная форма (2NF) - предполагает, что каждый столбец, не являющийся ключом, должен зависеть от первичного ключа
  • Третья нормальная форма (3NF) - предполагает, что каждый столбец, не являющийся ключом, должен зависеть ТОЛЬКО от первичного ключа
  • Нормальная форма Бойса-Кодда (BCNF) - более строгая версия 3NF
  • Четвертая нормальная форма (4NF) - применяется для устранения многозначных зависимостей (multivalued dependencies), где столбец с первичным ключом имеет связь один-ко-многим со столбцом, который не является ключом (устраняет только некорректные N:N)
  • Пятая нормальная форма (5NF) - разделяет таблицы на более малые таблицы для устранения избыточности данных. Разбиение идет до тех пор, пока нельзя будет воссоздать оригинальную таблицу путем объединения малых таблиц.
  • Шестая нормальная форма (domain key normal form / 6NF) - каждое ограничение в связях между таблицами должно зависеть только от ограничений ключа и ограничений домена, где домен представляет набор допустимых значений для столбца. Это ограничение добавления недопустимных данных на уровне отношений между таблицами, а НЕ на уровне таблиц или столбцов. Не применима напрямую в БД.

Для большинства практических применений достаточно приведения к 3NF.

Функциональная зависимость

Функциональная зависимость - это отношение между атрибутами таблицы, где значение одного атрибута (или набора атрибутов) однозначно определяет значение другого атрибута.

Обозначение: A → B (читается: "B функционально зависит от A" или "A функционально определяет B")

Примеры:

  • StudentID → StudentName, BirthDate, Email - StudentName функционально зависит от StudentID. Зная ID студента, можно однозначно определить его имя, дату рождения и email.
  • ISBN → BookTitle, Author, Publisher, PublicationYear - зная ISBN книги, можно однозначно определить её название, автора, издателя и год издания.

Типы функциональных зависимостей:

  1. Полная функциональная зависимость
    1. Атрибут зависит от всего составного ключа, а не от его части.
    2. {StudentID, CourseID} → Grade - оценка зависит от комбинации студента И курса.
  2. Частичная функциональная зависимость
    1. Атрибут зависит только от части составного ключа.
    2. {StudentID, CourseID} → StudentName - имя студента зависит только от StudentID, а не от всего ключа.
  3. Транзитивная функциональная зависимость
    1. A → B и B → C, следовательно A → C
    2. StudentID → DepartmentID → DepartmentName - ID студента определяет факультет, факультет определяет название факультета.

Первая нормальная форма

Таблица находится в первой нормальной форме (1NF), если:

  1. Все атрибуты содержат только атомарные (неделимые) значения
  2. В таблице нет повторяющихся групп или массивов
  3. Каждая запись уникальна (есть первичный ключ)

Пример нарушения 1NF:

┌────────────┬──────────────┬─────────────────────────┐
│ StudentID  │ StudentName  │ Courses                 │
├────────────┼──────────────┼─────────────────────────┤
│ 1          │ Иван Иванов  │ Математика, Физика      │
│ 2          │ Петр Петров  │ Химия                   │
└────────────┴──────────────┴─────────────────────────┘

Проблема: атрибут Courses содержит несколько значений.

Приведение к 1NF:

┌────────────┬──────────────┬─────────────┐
│ StudentID  │ StudentName  │ Course      │
├────────────┼──────────────┼─────────────┤
│ 1          │ Иван Иванов  │ Математика  │
│ 1          │ Иван Иванов  │ Физика      │
│ 2          │ Петр Петров  │ Химия       │
└────────────┴──────────────┴─────────────┘

Проблема этого подхода: дублирование данных.

У нас увеличилась избыточность данных, но у нас сейчас отсутствуют повторяющиеся столбцы и сырые значения.

Решает описанную проблему приведение к 2NF.

Вторая нормальная форма

Таблица находится во второй нормальной форме (2NF), если:

  1. Она находится в 1NF
  2. Все неключевые атрибуты полностью функционально зависят от первичного ключа (нет частичных зависимостей)

2NF актуальна только для таблиц с составным первичным ключом.

Пример нарушения 2NF:

┌────────────┬───────────┬──────────────┬─────────────┬────────┐
│ StudentID  │ CourseID  │ StudentName  │ CourseName  │ Grade  │
├────────────┼───────────┼──────────────┼─────────────┼────────┤
│ 1          │ 101       │ Иван Иванов  │ Математика  │ 5      │
│ 1          │ 102       │ Иван Иванов  │ Физика      │ 4      │
│ 2          │ 101       │ Петр Петров  │ Математика  │ 5      │
└────────────┴───────────┴──────────────┴─────────────┴────────┘

Тут первичный ключ: {StudentID, CourseID}

Проблемы:

  • StudentName зависит только от StudentID (частичная зависимость)
  • CourseName зависит только от CourseID (частичная зависимость)
  • Grade зависит от полного ключа (полная зависимость) ✓

Приведение к 2NF:

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY, -- ключ для таблицы студентов 
    student_name VARCHAR(100) NOT NULL
);
[Students]
┌────────────┬──────────────┐
│ StudentID  │ StudentName  │
├────────────┼──────────────┤
│ 1          │ Иван Иванов  │
│ 2          │ Петр Петров  │
└────────────┴──────────────┘

CREATE TABLE Courses (
    course_id INTEGER PRIMARY KEY, -- ключ для таблицы курсов
    course_name VARCHAR(100) NOT NULL
);
[Courses]
┌───────────┬─────────────┐
│ CourseID  │ CourseName  │
├───────────┼─────────────┤
│ 101       │ Математика  │
│ 102       │ Физика      │
│ 103       │ Химия       │
└───────────┴─────────────┘

CREATE TABLE Enrollments (
    student_id INTEGER,
    course_id INTEGER,
    grade INTEGER,
    PRIMARY KEY (student_id, course_id), -- составной ключ для их связи 
    FOREIGN KEY (student_id) REFERENCES Students(student_id), -- FK на студентов 
    FOREIGN KEY (course_id) REFERENCES Courses(course_id) -- FK на курсы
);
[Enrollments]
┌────────────┬───────────┐
│ StudentID  │ CourseID  │
├────────────┼───────────┤
│ 1          │ 101       │
│ 1          │ 102       │
│ 2          │ 103       │
└────────────┴───────────┘

Теперь каждый неключевой атрибут зависит от полного ключа своей таблицы.

Это уже более правильный вариант относительно 1NF за счёт того, что тут наши данные не переиспользуются, а применяются N:N связи через отдельную таблицу с FK.

Третья нормальная форма

Таблица находится в третьей нормальной форме (3NF), если:

  1. Она находится в 2NF
  2. Нет транзитивных функциональных зависимостей между неключевыми атрибутами

То есть каждый столбец, не являющийся ключом, должен зависеть только от столбца, который является ключом.

Пример: А → В и В → С. То есть атрибут С транзитивно зависит от атрибута А, если атрибут С зависит от атрибута В, а атрибут В зависит от атрибута А.

Пример нарушения 3NF:

┌────────────┬──────────────┬───────────────┬──────────────────┐
│ StudentID  │ StudentName  │ DepartmentID  │ DepartmentName   │
├────────────┼──────────────┼───────────────┼──────────────────┤
│ 1          │ Иван Иванов  │ 10            │ Математический   │
│ 2          │ Петр Петров  │ 10            │ Математический   │
│ 3          │ Мария Сидор  │ 20            │ Физический       │
└────────────┴──────────────┴───────────────┴──────────────────┘

Функциональные зависимости:

  • StudentID → StudentName, DepartmentID, DepartmentName
  • DepartmentID → DepartmentName (транзитивная зависимость!)

Проблемы:

  • Избыточность: название факультета повторяется для каждого студента
  • Аномалия обновления: при переименовании факультета нужно обновить все записи студентов
  • Аномалия удаления: удаление последнего студента факультета приведет к потере информации о факультете
  • Аномалия вставки: нельзя добавить факультет, пока нет хотя бы одного студента

Приведение к 3NF:

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    department_id INTEGER NOT NULL,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);
CREATE TABLE Departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Что мы сделали: разделили таблицу на две отдельные, где находятся свои значения и отношения DepartmentID и StudentID и оставили только DepartmentID в таблице Students.

Теперь:

  • Нет избыточности данных
  • Название факультета хранится в одном месте
  • Можно добавлять факультеты независимо от студентов
  • Обновление названия факультета требует изменения только одной записи

Преимущества нормализации до 3NF:

  • Минимальная избыточность данных
  • Предотвращение большинства аномалий
  • Упрощение поддержки целостности данных
  • Гибкость при изменении структуры

Когда можно отступить от 3NF:

  • Оптимизация производительности (денормализация)
  • Хранилища данных (data warehouses)
  • Отчетные таблицы с предрассчитанными значениями
  • Кэширование часто запрашиваемых данных

Денормализация должна быть осознанным решением с пониманием компромиссов.

Вид графа

Обратные ссылки