Интерактивен учебник по информатика

Защо започваме с преговор

В Модул 3 научихте релационния модел, нормализацията и SQL в MS Access. Тази година базата от данни е гръбнакът на всичко: първо ще пишете по-сложни заявки, после ще свързвате базата с Python и с езиков модел, а накрая ще защитавате проект с база и AI компонент. Лоша схема в проекта означава заявки, които не могат да се напишат, и данни, които се губят. Затова първият час е за нещата, които най-често се бъркат при проектиране: връзката „много към много“ и какво става с данните, когато изтриете запис, към който сочат други.

Ключове и връзки — накратко

Първичен ключ (primary key, PK)

Колона (или комбинация от колони), чиято стойност еднозначно определя реда. Не допуска повторения и празни стойности (NULL).

Външен ключ (foreign key, FK)

Колона в една таблица, която съдържа стойност на първичния ключ на друга (или на същата) таблица. Така редът „сочи“ към родителски ред. СУБД пази референтната цялост: не може да сочите към ред, който не съществува.

Връзка	Пример	Как се реализира
1:1 (едно към едно)	ученик ↔ лична карта	външен ключ с ограничение UNIQUE в едната от двете таблици
1:M (едно към много)	клас ↔ ученици	външен ключ в таблицата „много“: `students.class_id`
M:N (много към много)	ученици ↔ клубове, книги ↔ автори	свързваща таблица с два външни ключа

В учебната база на приложението връзката classes → students е 1:M: един клас има много ученици, всеки ученик е точно в един клас. marks сочи едновременно към students и към subjects — оценката е „на ученик по предмет“.

Много към много: свързващата таблица

Един ученик участва в няколко клуба, а в един клуб има много ученици. Ако сложите club_id в students, ученикът ще може да е само в един клуб. Ако сложите student_id в clubs — клубът ще има само един член. Списък „1, 3, 9“ в една клетка нарушава първата нормална форма и не може да се търси с JOIN.

Свързващата таблица student_clubs има съставен първичен ключ от двата външни ключа — една двойка не може да се повтори.

CREATE TABLE clubs (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE student_clubs (
  student_id INTEGER NOT NULL REFERENCES students(id),
  club_id    INTEGER NOT NULL REFERENCES clubs(id),
  PRIMARY KEY (student_id, club_id)     -- съставен ключ
);

Свързващата таблица може да носи и собствени атрибути на връзката: дата на записване в клуба, роля (член, капитан). Например при книги ↔ автори — поредността на автора.
Заявката „кой в кой клуб е“ минава през два JOIN-а: students → student_clubs → clubs. Ще го видите в първия пример.
Ако ви трябва броят на членовете по клуб — GROUP BY върху свързващата таблица (урок 4).

Каскадни операции

Какво трябва да стане, ако клуб „Дебати“ се закрие, а в student_clubs има редове, които сочат към него? Има три разумни отговора и всеки е правилен в различна ситуация. Избирате го при създаване на външния ключ с клаузата ON DELETE ….

Клауза	Какво прави при изтриване на родителя	Кога е уместна
`ON DELETE CASCADE`	изтрива и всички дъщерни редове	дъщерните редове нямат смисъл без родителя: членства в закрит клуб, редове на изтрита поръчка
`ON DELETE SET NULL`	дъщерните редове остават, външният ключ става NULL	връзката е незадължителна: клуб без ръководител, задача без отговорник
`ON DELETE RESTRICT / NO ACTION`	отказва изтриването с грешка, докато има дъщерни редове	по подразбиране; за важни данни, които не бива да изчезват незабелязано: ученик с оценки

Кое да изберете

Задайте си въпроса: „Ако родителят изчезне, има ли смисъл дъщерният ред?“ Не → CASCADE. Да, но без връзката → SET NULL (колоната трябва да допуска NULL). Да и връзката е задължителна → RESTRICT.
Свързващите таблици почти винаги са с CASCADE и по двата ключа — ред „ученик 9 в клуб 3“ е безсмислен без ученик 9 или без клуб 3.
Има и ON UPDATE CASCADE — при смяна на стойността на първичния ключ тя се разпространява към външните ключове. Рядко трябва: ключовете не се променят.

Проектиране на схема — стъпките

Изброете същностите (за какво пазим данни): ученици, клубове, учители.
За всяка същност — атрибутите и първичния ключ. Изкуствен ключ id INTEGER PRIMARY KEY е винаги безопасен избор.
За всяка двойка същности определете вида на връзката: 1:1, 1:M или M:N. Задайте въпроса и в двете посоки.
1:M → външен ключ в таблицата „много“. M:N → свързваща таблица със съставен ключ.
За всеки външен ключ решете ON DELETE: CASCADE, SET NULL или RESTRICT.
Напишете CREATE TABLE в реда родител → дете, вкарайте няколко реда и проверете с SELECT … JOIN, че схемата отговаря на въпросите, които ще ѝ задавате.

Какво трябва да запомниш

Първичният ключ определя реда еднозначно; външният ключ сочи към първичен ключ на друга таблица.
1:M — външен ключ в таблицата „много“. M:N — винаги през свързваща таблица с два външни ключа и съставен първичен ключ.
ON DELETE CASCADE — изтрива децата; SET NULL — откача ги; RESTRICT — отказва изтриването.
Каскадата върви по цялата верига — проверете със SELECT преди DELETE.
В SQLite външните ключове работят само след PRAGMA foreign_keys = ON; в MySQL — само с InnoDB.