- Различать суперключ, потенциальный, первичный и внешний ключи
- Нормализовать таблицу до 3НФ по функциональным зависимостям
- Объяснять свойства ACID и вычислять выигрыш от индекса
Если бы интернет-магазин записывал каждый заказ одной строкой в Excel, то при переезде Анара из Баку в Гянджу его адрес пришлось бы менять в сотнях строк — пропустишь одну, и в базе окажутся два разных города. Теория баз данных устраняет такие проблемы с математической точностью: она правильно раскладывает данные по таблицам, защищает их от сотен одновременных пользователей и сводит поиск среди миллиардов строк к миллисекундам.
Реляционная модель и ключи
В реляционной модели, предложенной Эдгаром Коддом в 1970 году, данные хранятся в отношениях (таблицах). Отношение — это множество кортежей (строк) с одинаковыми атрибутами; у каждого атрибута есть домен (множество допустимых значений). Раз это множество, у строк нет порядка и нет дубликатов. Запросы выражаются в реляционной алгебре, а SQL — её практический язык.
- σвыборка (selection): строки, удовлетворяющие условию, —
WHERE - πпроекция: нужные столбцы — список
SELECT - ⋈соединение (join): объединение двух отношений по общему атрибуту —
JOIN
| Ключ | Определение | Пример (students) |
|---|---|---|
| Суперключ | любой набор атрибутов, однозначно определяющий строку | {id}, {id, city} |
| Потенциальный ключ | минимальный суперключ (ни один атрибут нельзя убрать) | {id} |
| Первичный ключ | выбранный потенциальный ключ: уникален и не NULL | id |
| Внешний ключ | ссылка на первичный ключ другой таблицы (ссылочная целостность) | enrollments.student_id → students.id |
SELECT COUNT(*) AS students,
COUNT(email) AS with_email,
COUNT(DISTINCT email) AS distinct_emails,
COUNT(DISTINCT city) AS distinct_cities
FROM students;▸ Ожидаемый результат
students | with_email | distinct_emails | distinct_cities 12 | 10 | 10 | 7
city ключом быть не может (7 разных городов на 12 строк). email уникален там, где заполнен (10 = 10), но у 2 студентов равен NULL — поэтому первичным ключом он быть не может. Заметь: текущие данные никогда не доказывают ключ — ключ задаётся бизнес-правилом.Нормализация: 1НФ, 2НФ, 3НФ
Плохо спроектированная таблица порождает три вида аномалий. Аномалия обновления: один и тот же факт повторяется во многих строках, и если одну не обновить, данные начинают противоречить себе. Аномалия вставки: новый товар без заказов нельзя записать, потому что часть ключа осталась бы пустой. Аномалия удаления: удалив единственный заказ клиента, теряешь и все сведения о самом клиенте. Нормализация устраняет эти аномалии, разбивая таблицу на части без потери информации: каждый факт хранится в одном месте.
Любые две строки с одинаковыми значениями атрибутов X обязательно совпадают и по атрибутам Y. Например, customer_id → customer_city: зная клиента, знаешь и его город. Нормализация опирается именно на такие зависимости.
| Форма | Требование | Что устраняет |
|---|---|---|
| 1NF | в каждой ячейке одно атомарное значение, нет повторяющихся групп | ячейки-списки вроде «Laptop, Headphones» |
| 2NF | 1НФ + ни один неключевой атрибут не зависит от части составного ключа | частичные зависимости |
| 3NF | 2НФ + неключевые атрибуты зависят только от ключа, а не друг от друга | транзитивные зависимости |
Таблица магазина: order_id, order_date, customer_id, customer_name, customer_city, products, где в ячейке products список вида «Laptop ×1, Headphones ×2» вместе с ценами и категориями. Приведи её к 1НФ, 2НФ и 3НФ.
Показать решениеСкрыть решение
(order_id, product_id, order_date, customer_id, customer_name, customer_city, product_name, category, price, quantity), ключ (order_id, product_id).Зависимости: order_id → order_date, customer_id; customer_id → customer_name, customer_city; product_id → product_name, category, price; (order_id, product_id) → quantity.
2НФ: выносим то, что зависит от части ключа:
order_items(order_id, product_id, quantity)orders(order_id, order_date, customer_id, customer_name, customer_city)products(product_id, product_name, category, price)3НФ: в
orders order_id → customer_id → customer_city — транзитивная зависимость, выносим:orders(order_id, order_date, customer_id) + customers(customer_id, customer_name, customer_city).Итог: 4 таблицы. Теперь город Анара меняется в одной ячейке.
SELECT s.first_name, s.city, c.title, c.teacher, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses AS c ON c.id = e.course_id
WHERE c.teacher = 'Ramin Səfərov'
ORDER BY c.title, s.first_name;▸ Ожидаемый результат
first_name | city | title | teacher | score Aysel | Bakı | Algebra | Ramin Səfərov | 92 Fidan | Naxçıvan | Algebra | Ramin Səfərov | 97 Murad | Gəncə | Algebra | Ramin Səfərov | 75 Nigar | Şəki | Algebra | Ramin Səfərov | 85 Leyla | Bakı | Geometry | Ramin Səfərov | 95 Orxan | Sumqayıt | Geometry | Ramin Səfərov | 70 Səbinə | Quba | Geometry | Ramin Səfərov | 79
students, courses, enrollments. JOIN восстанавливает «плоскую» таблицу только тогда, когда она нужна, — здесь имя преподавателя повторяется 7 раз. Если бы её так и хранили, смена преподавателя потребовала бы обновить 7 строк (аномалия обновления).Транзакции и ACID
| Свойство | Смысл | Как обеспечивается |
|---|---|---|
| Атомарность (A) | всё или ничего | ROLLBACK, журнал отката |
| Согласованность (C) | база переходит из одного корректного состояния в другое | ограничения: ключи, CHECK, внешние ключи |
| Изолированность (I) | параллельные транзакции не видят незавершённую работу друг друга | блокировки, многоверсионность (MVCC) |
| Долговечность (D) | после COMMIT данные переживают даже отключение питания | журнал упреждающей записи (WAL) |
BEGIN;
UPDATE products SET stock = stock - 2 WHERE name = 'Laptop';
INSERT INTO orders (customer_id, product_id, quantity, order_date)
VALUES (2, 1, 2, '2025-08-01');
ROLLBACK;
SELECT name, stock,
(SELECT COUNT(*) FROM orders) AS orders_total
FROM products
WHERE name = 'Laptop';▸ Ожидаемый результат
name | stock | orders_total Laptop | 8 | 12
ROLLBACK). Оба изменения исчезли: остаток снова 8, заказов по-прежнему 12. С COMMIT оба сохранились бы вместе.Полная изоляция дорога, поэтому SQL предлагает четыре уровня изоляции. READ UNCOMMITTED допускает «грязное чтение» (видны чужие неподтверждённые изменения), READ COMMITTED — «неповторяющееся чтение» (одна и та же строка различается при двух чтениях), REPEATABLE READ — «фантомы» (появление новых строк), а SERIALIZABLE гарантирует тот же результат, что и последовательное выполнение транзакций.
Индексы, SQL и NoSQL
- hвысота индекса B-дерева (число читаемых страниц при поиске)
- Nчисло строк в таблице
- fветвление: сколько ключей помещается на странице
В таблице N = 10⁷ строк, на одну страницу диска помещается 100 строк. Сколько страниц прочитает запрос WHERE email = … без индекса и с индексом B-дерева с ветвлением f = 100?
Показать решениеСкрыть решение
С индексом: h = ⌈log 10⁷ / log 100⌉ = ⌈7/2⌉ = ⌈3,5⌉ = 4 уровня + 1 страница самой строки = ≈ 5 страниц.
Выигрыш ≈ 20 000 раз: O(log N) вместо O(N). Цена: индекс занимает место, и каждый INSERT/UPDATE должен его обновлять.
| Критерий | SQL (реляционные) | NoSQL |
|---|---|---|
| модель | таблицы и связи | документы, ключ–значение, семейства столбцов, графы |
| схема | строгая, задаётся заранее | гибкая |
| транзакции | полный ACID | часто ограничены, «согласованность в конечном счёте» |
| масштабирование | в основном вертикальное (мощнее сервер) | горизонтальное (много серверов) |
| примеры | PostgreSQL, MySQL, SQLite, SQL Server | MongoDB, Redis, Cassandra, Neo4j |
Главное
- Отношение — множество кортежей; потенциальный ключ — минимальный суперключ, первичный — уникален и NOT NULL, внешний защищает целостность.
- 1НФ — атомарные значения; 2НФ — нет частичных зависимостей; 3НФ — нет транзитивных зависимостей.
- Нормализация устраняет аномалии обновления, вставки и удаления.
- ACID: атомарность, согласованность, изолированность, долговечность; ROLLBACK отменяет транзакцию целиком.
- Индекс B-дерева сокращает поиск с O(N) до O(log N), но замедляет запись.
Проверь себя
Вопросов: 10. Каждый правильный ответ приносит XP.
grades(student_id, course_id, student_name, score), ключ (student_id, course_id), student_id → student_name. Какую нормальную форму нарушает таблица?