Базы данных, СУБД и реляционная модель

Как хранят и связывают данные миллиарды приложений мира — от школьного журнала до социальной сети.

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

Зачем это нужно

Любое серьёзное приложение хранит данные: пользователей, заказы, сообщения, оценки. Складывать их в текстовые файлы быстро становится мучением — нет быстрого поиска, легко нарушить целостность, невозможно безопасно работать вдвоём. Базы данных решают это раз и навсегда. Реляционная модель и язык SQL, которым с ними общаются, — один из самых востребованных навыков в IT и важная тема старшей школы. Здесь вы начнёте говорить с базой данных на её языке — в живой песочнице прямо в браузере.

Чтобы оценить, насколько это фундаментальная вещь, представьте любой сервис, которым вы пользуетесь: социальную сеть, интернет-магазин, школьный электронный дневник, банковское приложение. За каждым из них стоит база данных, и зачастую именно она — самая ценная часть системы. Программу можно переписать, дизайн поменять, но данные пользователей накапливаются годами и невосполнимы. Реляционная модель, которую мы изучаем, появилась ещё в 1970 году в работе математика Эдгара Кодда — и за полвека не была вытеснена ничем, несмотря на бурное развитие технологий. Причина её долголетия в том, что она опирается на строгую математику (теорию отношений) и при этом интуитивно понятна: данные в таблицах, связи через общие ключи. Освоив её, вы получаете навык, который не устареет, и общий язык с миллионами разработчиков по всему миру. А язык SQL, на котором мы будем задавать вопросы данным, понимают практически все базы — выучив его однажды, вы сможете работать почти с любой из них.

Реляционная модель: всё хранится в таблицах

Реляционная база данных хранит данные в таблицах. Таблица — как лист электронной таблицы: столбцы (поля) задают, какие сведения мы храним, а строки (записи) — конкретные объекты. Таблица «Ученики» может иметь столбцы «имя», «класс», «возраст», а каждая строка — это один ученик. Создадим такую таблицу и наполним её. Нажмите «Запустить» — код выполнится в настоящей SQLite-песочнице:

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    class TEXT,
    age INTEGER
);

INSERT INTO students (name, class, age) VALUES
    ('Анна',   '10А', 16),
    ('Борис',  '10А', 17),
    ('Вера',   '10Б', 16),
    ('Глеб',   '11А', 17);

SELECT * FROM students;

Вывод:

id | name  | class | age
1  | Анна  | 10А   | 16
2  | Борис | 10А   | 17
3  | Вера  | 10Б   | 16
4  | Глеб  | 11А   | 17

Ключи: как отличать записи

Двух разных учеников могут звать одинаково. Чтобы каждую запись можно было однозначно опознать, вводят первичный ключ (primary key) — поле, уникальное для каждой строки. Чаще всего это числовой id, который база назначает автоматически (AUTOINCREMENT). Первичный ключ — паспорт записи: по нему её всегда можно найти и на неё можно ссылаться из других таблиц.

Связи между таблицами и внешний ключ

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

CREATE TABLE subjects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL
);

INSERT INTO subjects (title) VALUES
    ('Информатика'),
    ('Математика'),
    ('Физика');

CREATE TABLE grades (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER,        -- внешний ключ на students.id
    subject_id INTEGER,        -- внешний ключ на subjects.id
    mark INTEGER
);

INSERT INTO grades (student_id, subject_id, mark) VALUES
    (1, 1, 5), (1, 2, 4),
    (2, 1, 3), (3, 1, 5),
    (4, 3, 4);

SELECT * FROM grades;

Вывод:

id | student_id | subject_id | mark
1  | 1          | 1          | 5
2  | 1          | 2          | 4
3  | 2          | 1          | 3
4  | 3          | 1          | 5
5  | 4          | 3          | 4

Заметьте: в таблице оценок нет ни имён, ни названий предметов — только ссылки-номера. Имя ученика хранится ровно в одном месте (таблица students). Если ученик сменит фамилию, мы поправим её в одной строке, и все оценки автоматически «увидят» новое имя.

Почему это лучше одной большой таблицы

Можно было бы свалить всё в одну таблицу: имя ученика, его класс, название предмета, оценка — в каждой строке. Но тогда имя «Анна 10А» повторялось бы в каждой её оценке. Это называется избыточностью и ведёт к аномалиям: опечатка в одном месте, рассинхронизация, раздувание базы. Разбиение на связанные таблицы (нормализация) убирает дублирование. Покажем разницу на «плохой» таблице:

-- Так делать НЕ надо: данные дублируются
CREATE TABLE bad_design (
    student_name TEXT,
    student_class TEXT,
    subject TEXT,
    mark INTEGER
);

INSERT INTO bad_design VALUES
    ('Анна', '10А', 'Информатика', 5),
    ('Анна', '10А', 'Математика',  4),
    ('Анна', '10А', 'Физика',      5);

-- имя и класс Анны повторились трижды!
SELECT student_name, student_class FROM bad_design;

Вывод:

student_name | student_class
Анна         | 10А
Анна         | 10А
Анна         | 10А

Попробуй сам

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

CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);
INSERT INTO authors (name) VALUES ('Пушкин'), ('Толстой'), ('Чехов');

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    author_id INTEGER,        -- внешний ключ на authors.id
    year INTEGER
);
INSERT INTO books (title, author_id, year) VALUES
    ('Евгений Онегин', 1, 1833),
    ('Война и мир',    2, 1869),
    ('Вишнёвый сад',   3, 1904);

SELECT title, author_id, year FROM books;

Вывод:

title          | author_id | year
Евгений Онегин | 1         | 1833
Война и мир    | 2         | 1869
Вишнёвый сад   | 3         | 1904

Частые ошибки

• Хранят всё в одной таблице. Дублирование данных ведёт к ошибкам и аномалиям при изменении.
• Забывают первичный ключ. Без него нельзя надёжно отличить и адресовать записи.
• Путают поле и запись. Поле — это столбец (характеристика), запись — строка (объект).
• Дублируют название вместо ссылки. На связанные данные ссылаются по id (внешний ключ), а не копируют текст.

Итоги

• База данных хранит взаимосвязанные данные; СУБД управляет ими и выполняет запросы.
• В реляционной модели данные лежат в таблицах: столбцы — поля, строки — записи.
• Первичный ключ однозначно опознаёт запись; внешний ключ — ссылка на запись другой таблицы.
• Разбиение на связанные таблицы устраняет дублирование и аномалии.