BITEDU.PL

Rodzaje relacji (1:1, 1:N, N:M)

Wyobraź sobie szkolną bazę: uczniowie, klasy, przedmioty, legitymacje.
Nie wszystkie dane trzymamy w jednej tabeli — rozdzielamy je i łączymy relacjami.

Co to jest relacja

Relacja to powiązanie między tabelami. Dzięki niej baza „wie”, które rekordy do siebie pasują.

Trzy podstawowe typy:

  • 1:1 (jeden do jednego) – jednemu rekordowi A odpowiada dokładnie jeden rekord B.
    Przykład: uczeń ↔ legitymacja.
  • 1:N (jeden do wielu) – jednemu rekordowi A odpowiada wiele rekordów B.
    Przykład: klasa → uczniowie.
  • N:M (wiele do wielu) – wielu rekordom A odpowiada wiele rekordów B.
    Przykład: uczeń ↔ przedmiot (uczeń ma wiele przedmiotów, przedmiot ma wielu uczniów).
    Tę relację zawsze realizujemy przez tabelę pośredniczącą.

Nasza baza do ćwiczeń: szkola_relacje

Cele:

  • pokazać wszystkie trzy typy relacji,
  • mieć na czym pisać JOIN-y i zadania.

1. Tworzenie bazy i tabel (CREATE)

-- 1) Baza
CREATE DATABASE IF NOT EXISTS szkola_relacje
  CHARACTER SET utf8mb4 COLLATE utf8mb4_polish_ci;
USE szkola_relacje;

-- 2) Tabela KLASY (1 klasa -> wielu uczniów)
CREATE TABLE Klasy (
  id_klasy INT AUTO_INCREMENT PRIMARY KEY,
  nazwa VARCHAR(10) NOT NULL,
  wychowawca VARCHAR(60) NOT NULL
) ENGINE=InnoDB;

-- 3) Tabela UCZNIOWIE (wiele rekordów przypiętych do jednej klasy)
CREATE TABLE Uczniowie (
  id_ucznia INT AUTO_INCREMENT PRIMARY KEY,
  imie VARCHAR(50) NOT NULL,
  nazwisko VARCHAR(60) NOT NULL,
  id_klasy INT NOT NULL,
  FOREIGN KEY (id_klasy) REFERENCES Klasy(id_klasy)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

-- 4) Tabela LEGITYMACJE (relacja 1:1 z uczniem)
CREATE TABLE Legitymacje (
  id_legitymacji INT AUTO_INCREMENT PRIMARY KEY,
  nr_legitymacji VARCHAR(20) NOT NULL UNIQUE,
  id_ucznia INT NOT NULL UNIQUE,  -- UNIQUE gwarantuje 1:1
  data_wydania DATE NOT NULL,
  FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

-- 5) Tabela PRZEDMIOTY (lista przedmiotów)
CREATE TABLE Przedmioty (
  id_przedmiotu INT AUTO_INCREMENT PRIMARY KEY,
  nazwa VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

-- 6) Tabela pośrednicząca UCZEN_PRZEDMIOT (relacja N:M)
CREATE TABLE Uczen_Przedmiot (
  id_ucznia INT NOT NULL,
  id_przedmiotu INT NOT NULL,
  rok_szkolny VARCHAR(9) NOT NULL,       -- np. 2024/2025
  PRIMARY KEY (id_ucznia, id_przedmiotu, rok_szkolny),   -- brak duplikatów
  FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia)
    ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (id_przedmiotu) REFERENCES Przedmioty(id_przedmiotu)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

Co tu widać:

  • 1:N: Klasy(id_klasy) → Uczniowie(id_klasy)
  • 1:1: Uczniowie(id_ucznia) ↔ Legitymacje(id_ucznia) (UNIQUE)
  • N:M: Uczniowie ↔ Przedmioty przez tabelę Uczen_Przedmiot

2. Dane przykładowe (INSERT)

-- KLASY
INSERT INTO Klasy (nazwa, wychowawca) VALUES
('1A', 'Agnieszka Mazur'),
('2B', 'Tomasz Lis');

-- UCZNIOWIE
INSERT INTO Uczniowie (imie, nazwisko, id_klasy) VALUES
('Anna', 'Nowak', 1),
('Piotr', 'Malinowski', 1),
('Julia', 'Kwiatkowska', 2);

-- LEGITYMACJE (1:1)
INSERT INTO Legitymacje (nr_legitymacji, id_ucznia, data_wydania) VALUES
('DL-2024-0001', 1, '2024-09-02'),
('DL-2024-0002', 2, '2024-09-02'),
('DL-2024-0003', 3, '2024-09-03');

-- PRZEDMIOTY
INSERT INTO Przedmioty (nazwa) VALUES
('Matematyka'),
('Informatyka'),
('Język polski');

-- UCZEN_PRZEDMIOT (N:M)
INSERT INTO Uczen_Przedmiot (id_ucznia, id_przedmiotu, rok_szkolny) VALUES
(1, 1, '2024/2025'),  -- Anna - Matematyka
(1, 2, '2024/2025'),  -- Anna - Informatyka
(2, 1, '2024/2025'),  -- Piotr - Matematyka
(3, 1, '2024/2025'),  -- Julia - Matematyka
(3, 3, '2024/2025');  -- Julia - J.polski

Krótko: jak czytać te relacje

  • 1:1 (Uczniowie ↔ Legitymacje):
    w Legitymacje.id_ucznia jest UNIQUE – nie da się dodać dwóch legitymacji dla tego samego ucznia.
  • 1:N (Klasy → Uczniowie):
    w Uczniowie trzymamy id_klasy – wielu uczniów może wskazywać na jedną klasę.
  • N:M (Uczniowie ↔ Przedmioty):
    mamy tabelę łączącą Uczen_Przedmiot z parą (id_ucznia, id_przedmiotu) i rokiem szkolnym.

Jak utworzyć relacje w Designerze (phpMyAdmin, XAMPP)

  1. Uruchom XAMPP → włącz Apache i MySQL → kliknij Admin przy MySQL.
  2. W phpMyAdmin wybierz bazę szkola_relacje.
  3. Utwórz wszystkie tabele (jak wyżej) lub z poziomu GUI (Nowa tabela → kolumny).
  4. Wejdź w zakładkę Relacje / Designer (czasem „Projektant”).
  5. Przeciągnij tabelę Uczniowie i Klasy na płótno.
  6. Kliknij kolumnę Uczniowie.id_klasy i połącz z Klasy.id_klasy.
    Zatwierdź – phpMyAdmin doda FOREIGN KEY.
  7. Zrób to samo dla:
    • Legitymacje.id_ucznia → Uczniowie.id_ucznia (zaznacz UNIQUE na id_ucznia w Legitymacje, żeby uzyskać 1:1),
    • Uczen_Przedmiot.id_ucznia → Uczniowie.id_ucznia,
    • Uczen_Przedmiot.id_przedmiotu → Przedmioty.id_przedmiotu.
  8. Zapisz. W Designerze zobaczysz schemat połączeń jak na diagramie.

Uwaga: jeśli przycisk Designer jest wyszarzony, włącz „Przechowuj relacje” (tworzy tabelę phpmyadmin.pma__relation). phpMyAdmin sam to zaproponuje.

Kilka prostych zapytań sprawdzających relacje

-- Uczniowie z nazwą klasy (1:N)
SELECT u.imie, u.nazwisko, k.nazwa AS klasa
FROM Uczniowie u
JOIN Klasy k ON u.id_klasy = k.id_klasy;

-- Uczeń z numerem legitymacji (1:1)
SELECT u.imie, u.nazwisko, l.nr_legitymacji
FROM Uczniowie u
JOIN Legitymacje l ON l.id_ucznia = u.id_ucznia;

-- Uczniowie i ich przedmioty (N:M)
SELECT u.imie, u.nazwisko, p.nazwa AS przedmiot, up.rok_szkolny
FROM Uczen_Przedmiot up
JOIN Uczniowie u ON up.id_ucznia = u.id_ucznia
JOIN Przedmioty p ON up.id_przedmiotu = p.id_przedmiotu
ORDER BY u.nazwisko, p.nazwa;

10 zadań do relacji

Zadanie 1.
Dodaj do tabeli Klasy nową klasę 3C z wychowawcą „Ewa Król”. Dodaj dwóch uczniów do tej klasy.
Sprawdź zapytaniem, czy wyświetlają się poprawnie wraz z nazwą klasy.

Zadanie 2.
Spróbuj dodać drugą legitymację temu samemu uczniowi. Co się stanie? Wyjaśnij, dlaczego.
(podpowiedź: UNIQUE na Legitymacje.id_ucznia)

Zadanie 3.
Dopisz nowe przedmioty: „Fizyka”, „Biologia”. Dopisz Annę Nowak do obu w Uczen_Przedmiot.
Wypisz listę przedmiotów Anny.

Zadanie 4.
Wypisz wszystkich uczniów bez żadnego przypisanego przedmiotu (LEFT JOIN + IS NULL).

Zadanie 5.
Dla każdego ucznia wypisz liczbę jego przedmiotów (GROUP BY).
Posortuj malejąco po liczbie.

Zadanie 6.
Zmień klasę Piotra Malinowskiego z 1A na 2B.
Pokaż zapytaniem, że zmiana widoczna jest w wynikach.

Zadanie 7.
Usuń przedmiot „Język polski”. Co się stanie z rekordami w Uczen_Przedmiot?
Wyjaśnij działanie opcji ON DELETE RESTRICT.

Zadanie 8.
Dodaj indeks na Uczniowie(nazwisko) i pokaż, jak go usunąć.
(Po co? Szybsze wyszukiwanie przy dużej liczbie uczniów.)

CREATE INDEX idx_uczniowie_nazwisko ON Uczniowie(nazwisko);
DROP INDEX idx_uczniowie_nazwisko ON Uczniowie;

Zadanie 9.
Dodaj kolumnę data_urodzenia do Uczniowie i ustaw CHECK tak, by data była < CURRENT_DATE.
Sprawdź, czy baza broni przed błędnymi danymi.

ALTER TABLE Uczniowie
  ADD data_urodzenia DATE,
  ADD CONSTRAINT chk_data_urodzenia CHECK (data_urodzenia < CURRENT_DATE);

Zadanie 10.
Zaprojektuj własną relację N:M: „Nauczyciele” ↔ „Przedmioty” (tabela pośrednia Nauczyciel_Przedmiot z kolumną rok_szkolny).
Utwórz tabele, dodaj kilka rekordów i napisz zapytanie pokazujące, który nauczyciel prowadzi jaki przedmiot w danym roku.

Podsumowanie

  • 1:1 – rzadziej spotykana, ale przydaje się, gdy „dodatkowe dane” chcemy trzymać osobno (np. legitymacje).
  • 1:N – najczęstsza relacja (klasa → uczniowie, klient → zamówienia).
  • N:M – zawsze robimy przez tabelę pośredniczącą (uczeń ↔ przedmiot).
  • Designer w phpMyAdmin pozwala te relacje narysować i od razu utworzyć klucze obce.