Как-то раз я бродил по интернету и наткнулся на главу «Первичный ключ и интуитивная интерпретация реляционных понятий». Это был толстый нудный учебник для ВУЗов. Недолго думая, я решил написать то же самое, но оставить только самое нужное.
В этом выпуске — таблицы, индексы, ключи, нормальные формы и немного практики. Целевая аудитория — интересующиеся школьники или IT-студенты, прогулявшие лекцию по БД.
Матчасть
В реляционной
СУБД хранится
таблица (теоретики называют
«отношение», relation — отсюда и «реляционная модель»), у нее есть колонки (они же
«атрибуты») и строки (или
«кортежи»).
Для ускорения производительности по одному или нескольким столбцам создаются
индексы, которые работают как алфавитный указатель в книге.
Если значения в столбцах индекса приводит к одной-единственной строке таблицы, как номер паспорта приводит к одному человеку, то по таким столбцам логично создать
первичный ключ (
primary key, PK). Обычно бывает наборот: по PK базы данных сами строят
уникальный индекс (бывают
неуникальные), если на этапе создания таблицы объявить первичный ключ [1].
Таблицы могут быть связаны между собой: «один к одному», «один ко многим», «многие ко многим» [2]. Для связей понадобятся ключи: первичный ключ (Primary Key, PK) одной таблицы связан с тем же значением
внешнего ключа (Foreign Key, FK) другой таблицы.
Наглядно таблицы, ключи и связи рисуются на диаграммах
IDEF1x.
Практика
Представьте, что нужно положить в реляционную базу данных информацию по пользователям и их ролям. У каждого пользователя может быть много ролей, например, Василий Вазгенович — учитель физики и математики одновременно.
Можно создать таблицу пользователей (users) и таблицу ролей (roles). Согласно условию, пользователь и его роли связаны «один ко многим». Понятие
«кардинальность связи» показывает, сколько именно ролей у конкретного пользователя.
Что с этим знанием можно сделать? Подключиться к БД и воспользоваться
SQL!
-- создать таблицы:
CREATE TABLE users (
user_id int NOT NULL PRIMARY KEY, -- суррогатный первичный ключ
user_name char (30) NOT NULL
);
CREATE TABLE roles (
user_id integer int FOREIGN KEY REFERENCES users (user_id), -- внешний ключ
role_name char (30) NOT NULL
);
-- заполнить
INSERT INTO users (user_id, user_name) values (1, 'Вася');
INSERT INTO roles (user_id, role_name) values (1, 'Администратор');
-- в зависимости от вашей IDE (если autocommit не включен)
-- может понадобиться явно завершить транзакцию
COMMIT;
-- посмотреть, все ли на месте
SELECT u.user_name, r.role_name
FROM users u, roles r
WHERE u.user_id = r.user_id;
Эти же данные можно было сложить в одну таблицу или в три, что упростило или усложнило бы запрос на выборку. База данных должна быть в
нормальной форме — их много, но на практике используются первые три НФ [3].
Забегая вперед, скажу: если для реализации этой модели нам понадобилась одна таблица, значит база данных находится в 1НФ. Две таблицы — 2НФ. Три — 3НФ.
Для тех, кто дочитал до конца
[1] Больше одного PK на той же таблице создавать нельзя, но можно несколько FK.
[2] Связь «один к одному» — редкий случай (когда широкую таблицу из многих колонок разорвали на несколько мелких). Связь «многие ко многим» в чистом виде не встречается — её делают через три таблицы и две связи «один ко многим».
[3] Хотя на самом деле так никогда не бывает — обязательно кто-то возьмет и сделает журнал аудита в XML или JSON, но про проектирование и бизнес-сценарии мы поговорим в следующих сериях.
комментарии (19)