- PVSM.RU - https://www.pvsm.ru -

TL;DR — реляционные СУБД для тех, кто не смог

Как-то раз я бродил по интернету и наткнулся на главу «Первичный ключ и интуитивная интерпретация реляционных понятий». Это был толстый нудный учебник для ВУЗов. Недолго думая, я решил написать то же самое, но оставить только самое нужное.

TL;DR — реляционные СУБД для тех, кто не смог - 1

В этом выпуске — таблицы, индексы, ключи, нормальные формы и немного практики. Целевая аудитория — интересующиеся школьники или IT-студенты, прогулявшие лекцию по БД.

Матчасть

В реляционной СУБД [1] хранится таблица [2] (теоретики называют «отношение», relation — отсюда и «реляционная модель»), у нее есть колонки (они же «атрибуты») и строки (или «кортежи»).

Для ускорения производительности по одному или нескольким столбцам создаются индексы [3], которые работают как алфавитный указатель в книге.

Если конкретное значение в столбце (-ах) индекса приводят к конкретной единственной строке таблицы (например, столбец «номер паспорта» приводит к одному человеку), то по таким столбцам логично создать первичный ключ [4] (primary key, PK). Обычно бывает наборот: СУБД сами покрывают первичный ключ уникальным индексом (бывают неуникальные), если на этапе создания таблицы объявить primary key [1].

Таблицы могут быть связаны между собой: «один к одному», «один ко многим», «многие ко многим» [2]. Для связей понадобятся ключи: первичный ключ одной таблицы связан с тем же значением внешнего ключа [5] (foreign key, FK) другой таблицы.

Наглядно таблицы, ключи и связи рисуются на диаграммах IDEF1x [6].

Практика

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

Можно создать таблицу пользователей (users) и таблицу ролей (roles). Согласно условию, пользователь и его роли связаны «один ко многим». Понятие «кардинальность связи» показывает, сколько именно ролей у конкретного пользователя.

Что с этим знанием можно сделать? Подключиться к БД и воспользоваться SQL [7]!

-- создать таблицы:
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;

Эти же данные можно было сложить в одну таблицу или в три, что упростило или усложнило бы запрос на выборку. База данных должна быть в нормальной форме [8] — их много, но на практике используются первые три НФ [3].

Для тех, кто дочитал до конца

[1] Больше одного PK на той же таблице создавать нельзя, но можно несколько FK.
[2] Связь «один к одному» — редкий случай (когда широкую таблицу из многих колонок разорвали на несколько мелких). Связь «многие ко многим» в чистом виде не встречается — её делают через три таблицы и две связи «один ко многим».
[3] В реальных приложениях идеальной нормализации не бывает — например, обязательно кто-то возьмет и сделает журнал аудита с колонкой в XML или JSON… но про проектирование и бизнес-сценарии мы поговорим в следующих сериях.

Автор: zip_zero

Источник [9]


Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/programmirovanie/253401

Ссылки в тексте:

[1] СУБД: https://ru.wikipedia.org/wiki/Система_управления_базами_данных

[2] таблица: https://ru.wikipedia.org/wiki/Отношение_(реляционная_модель)

[3] индексы: https://ru.wikipedia.org/wiki/Индекс_(базы_данных)

[4] первичный ключ: https://ru.wikipedia.org/wiki/Первичный_ключ

[5] внешнего ключа: https://ru.wikipedia.org/wiki/Внешний_ключ

[6] IDEF1x: https://en.wikipedia.org/wiki/IDEF1X

[7] SQL: https://ru.wikipedia.org/wiki/SQL#.D0.9E.D0.BF.D0.B5.D1.80.D0.B0.D1.82.D0.BE.D1.80.D1.8B

[8] нормальной форме: https://ru.wikipedia.org/wiki/Нормальная_форма

[9] Источник: https://habrahabr.ru/post/327136/