INF.03 · Bazy danych i SQL

Trzy relacje w jednej bazie: jak je zapisać w SQL

Dziennik elektroniczny ma wszystkie trzy typy relacji naraz: uczeń ma jedną legitymację (1:1), należy do jednej klasy (1:N), ale ma oceny z wielu przedmiotów, a każdy przedmiot ma ocenianych wielu uczniów (N:M). Na rysunku E-R różnią się symbolami. W kodzie SQL wyglądają bardzo podobnie, a jeden dodatkowy detal robi całą różnicę.

około 35 minut
1:1 · 1:N · N:M w SQL
baza: szkola_relacje

Po tej lekcji: rozpoznasz typ relacji po fragmencie CREATE TABLE, zapiszesz każdą z trzech relacji i pokażesz, że baza faktycznie pilnuje jej reguł.

1 · Czytaj kod

Jaką relację tworzy ten fragment?

Wszystkie trzy relacje używają FOREIGN KEY, więc kod wygląda podobnie. Patrz uważnie na ograniczenia kolumny z kluczem obcym i na to, ile kluczy obcych ma tabela. Rozstrzygnij pięć fragmentów.

2 · Dowód w bazie

Wersja bez UNIQUE: relacja 1:1 tylko z nazwy

Tu tabela Legitymacje ma klucz obcy do uczniów, ale bez UNIQUE na id_ucznia. Spróbuj dodać drugą legitymację temu samemu uczniowi.

3 · Ta sama tabela

Wersja z UNIQUE: relacja naprawdę 1:1

Teraz id_ucznia ma też ograniczenie UNIQUE. Powtórz te same dwa polecenia i porównaj odpowiedzi bazy.

Jeden dodatkowy wyraz, UNIQUE, zmienia relację 1:N w 1:1. Baza zaczyna wtedy pilnować reguły biznesowej: jeden uczeń, jedna legitymacja.

4 · Wiele do wielu

Tabela pośrednicząca ma własne dane

Przy relacji N:M klucze obce nie trafiają do żadnej z łączonych tabel, tylko do osobnej tabeli pośredniczącej. W dzienniku ta tabela ma jeszcze własne dane: ocenę i datę.

oceny.sqlsql

CREATE TABLE Oceny (
    id_oceny INT AUTO_INCREMENT PRIMARY KEY,
    id_ucznia INT NOT NULL,
    id_przedmiotu INT NOT NULL,
    ocena TINYINT NOT NULL CHECK (ocena BETWEEN 1 AND 6),
    data_oceny DATE NOT NULL,
    FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia),
    FOREIGN KEY (id_przedmiotu) REFERENCES Przedmioty(id_przedmiotu)
);

Dwa klucze obce każdy wiersz wiąże jednego ucznia z jednym przedmiotem.
Własny klucz główny id_oceny uczeń może dostać z tego samego przedmiotu wiele ocen, więc para (uczeń, przedmiot) nie jest unikalna.
Kiedy klucz złożony? gdy para ma być unikalna, na przykład w tabeli zapisów na przedmiot. Wtedy PRIMARY KEY (id_ucznia, id_przedmiotu).

5 · Zanim się pomylisz

Trzy pomyłki przy zapisie relacji

Rozwiń każdą z nich.

Relacja 1:1 bez UNIQUE

Tak nie

CREATE TABLE Legitymacje (
    id_legitymacji INT PRIMARY KEY,
    id_ucznia INT NOT NULL,
    FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia)
);

Jeden uczeń może mieć w tabeli kilka legitymacji. Baza nie zgłosi błędu, bo formalnie to poprawna relacja 1:N.

Tak

CREATE TABLE Legitymacje (
    id_legitymacji INT PRIMARY KEY,
    id_ucznia INT NOT NULL UNIQUE,
    FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia)
);

UNIQUE przy kluczu obcym wymusza relację 1:1.

Klucz obcy po stronie „jeden” w relacji 1:N

Tak nie

CREATE TABLE Klasy (
    id_klasy INT PRIMARY KEY,
    id_ucznia INT,
    FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia)
);

Klasa mogłaby wskazać tylko jednego ucznia. A klasa ma wielu uczniów.

Tak

CREATE TABLE Uczniowie (
    id_ucznia INT PRIMARY KEY,
    id_klasy INT NOT NULL,
    FOREIGN KEY (id_klasy) REFERENCES Klasy(id_klasy)
);

Klucz obcy zawsze trafia do tabeli po stronie „wielu”.

N:M zapisane listą

Tak nie

CREATE TABLE Uczniowie (
    id_ucznia INT PRIMARY KEY,
    przedmioty VARCHAR(100)  -- '1,2,3'
);

Lista w jednej komórce łamie pierwszą postać normalną. Nie połączysz jej przez JOIN i nikt nie sprawdza, czy przedmioty istnieją.

Tak

CREATE TABLE Oceny (...);  -- tabela pośrednicząca z dwoma kluczami obcymi

Relacja N:M zawsze wymaga osobnej tabeli pośredniczącej.

6 · Sprawdź sam siebie

Zanim oddasz schemat z relacjami

Na egzaminie rozpoznasz typ relacji z symbolu na diagramie E-R, ale w SQL musisz go jeszcze poprawnie zapisać.

Przy każdej relacji 1:N klucz obcy jest w tabeli po stronie „wielu”.
Przy relacji 1:1 klucz obcy ma UNIQUE.
Przy relacji N:M jest tabela pośrednicząca z dwoma kluczami obcymi.
Tabele tworzę w kolejności: najpierw nadrzędne, potem podrzędne.
Sprawdziłem w bazie, że reguła faktycznie działa (próba dodania duplikatu kończy się błędem).

Podsumowanie

Co warto zapamiętać

1:N: zwykły klucz obcy w tabeli po stronie „wielu”.
1:1: klucz obcy z UNIQUE.
N:M: tabela pośrednicząca z dwoma kluczami obcymi.
Brak UNIQUE zmienia 1:1 w 1:N i baza tego nie zgłosi.
Zawsze sprawdzaj w bazie, czy reguła działa.

Dalej na swoim koncie

Rozgrzewki, quiz i zadania są w biurku ucznia.

Teoria jest otwarta dla wszystkich. Żeby rozwiązywać zadania, zapisywać szkice i widzieć swój postęp, zaloguj się.

Zaloguj się →

Źródła i dalsza nauka

Dokumentacja MariaDB: polecenia SQL