database

Базы данных

О БД

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

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

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

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

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

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) - тип объектов, которые должны храниться в базе данных

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

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

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

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

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

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

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

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

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

1. Собираются все необходимые атрибуты
2. Атрибуты группируются по смыслу
3. Группы преобразуются в таблицы
4. Определяются связи между таблицами

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

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

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

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

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

Плохо:

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

Хорошо:

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

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

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

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

Домен

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

1. Тип данных

   • Числовые: INTEGER, DECIMAL, FLOAT
   • Строковые: VARCHAR, TEXT, CHAR
   • Дата/время: DATE, TIMESTAMP, TIME
   • Логические: BOOLEAN
   • И другие специфические типы

2. Диапазон значений

   • Возраст: от 0 до 150
   • Оценка: от 1 до 5
   • Год: от 1900 до текущего

3. Формат

   • Email: должен содержать @ и домен
   • Телефон: определенное количество цифр
   • ИНН: 10 или 12 цифр

4. Ограничения

   • CHECK constraints для проверки условий
   • DEFAULT значения
   • UNIQUE для уникальности

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

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_id}
• {email}
• {student_id, first_name}
• {student_id, first_name, last_name}

Потенциальный ключ (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.

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,        -- Первичный ключ
    email VARCHAR(100) UNIQUE NOT NULL, -- Альтернативный ключ
    phone VARCHAR(20) UNIQUE            -- Альтернативный ключ
);

Рекомендации по выбору первичного ключа:

• Предпочитайте суррогатные ключи для таблиц сущностей
• Используйте составные ключи для таблиц связей many-to-many
• Избегайте первичных ключей, которые могут измениться
• Используйте короткие ключи для лучшей производительности индексов

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

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

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

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

Типы связей

1. Один-к-одному (One-to-One)

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

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

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)
);

2. Один-ко-многим (One-to-Many)

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

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

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)
);

3. Многие-ко-многим (Many-to-Many)

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

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

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)  -- Студент не может записаться на курс дважды
);

Ссылочная целостность

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

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

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)
• Четвертая нормальная форма (4NF)
• Пятая нормальная форма (5NF)

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

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

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

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

Примеры:

StudentID → StudentName, BirthDate, Email

Зная ID студента, можно однозначно определить его имя, дату рождения и email.

ISBN → BookTitle, Author, Publisher, PublicationYear

Зная ISBN книги, можно однозначно определить её название, автора, издателя и год издания.

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

1. Полная функциональная зависимость Атрибут зависит от всего составного ключа, а не от его части.

   {StudentID, CourseID} → Grade
   

   Оценка зависит от комбинации студента И курса.

2. Частичная функциональная зависимость Атрибут зависит только от части составного ключа.

   {StudentID, CourseID} → StudentName
   

   Имя студента зависит только от StudentID, а не от всего ключа.

3. Транзитивная функциональная зависимость A → B и B → C, следовательно A → C

   StudentID → DepartmentID → DepartmentName
   

   ID студента определяет факультет, факультет определяет название факультета.

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

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

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

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

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

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

Приведение к 1NF (вариант 1 — разделение на строки):

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

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

Приведение к 1NF (вариант 2 — создание отдельной таблицы):

Таблица Students:

┌────────────┬──────────────┐
│ StudentID  │ StudentName  │
├────────────┼──────────────┤
│ 1          │ Иван Иванов  │
│ 2          │ Петр Петров  │
└────────────┴──────────────┘

Таблица Enrollments:

┌────────────┬───────────┐
│ StudentID  │ CourseID  │
├────────────┼───────────┤
│ 1          │ 101       │
│ 1          │ 102       │
│ 2          │ 103       │
└────────────┴───────────┘

Таблица Courses:

┌───────────┬─────────────┐
│ CourseID  │ CourseName  │
├───────────┼─────────────┤
│ 101       │ Математика  │
│ 102       │ Физика      │
│ 103       │ Химия       │
└───────────┴─────────────┘

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

Таблица находится во второй нормальной форме (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:

Таблица Students:

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL
);

Таблица Courses:

CREATE TABLE Courses (
    course_id INTEGER PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

Таблица Enrollments:

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),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

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

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

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

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

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

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

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

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

Проблемы:

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

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

Таблица Students:

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)
);

Таблица Departments:

CREATE TABLE Departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

Теперь:

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

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

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

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

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

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