Relacyjne bazy danych — pierwsza lekcja na przykładzie szkolnej biblioteki

Dane w bazie nie leżą w jednym wielkim arkuszu: są podzielone na tabele i powiązane kluczami. W bibliotece osobno zapisuje się książki, czytelników i wypożyczenia — dzięki temu dane każdej książki wpisuje się raz, a baza potrafi odpowiedzieć na pytania „kto co wypożyczył?” albo „których książek nikt nie bierze?”. Na lekcji uczniowie poznają te pojęcia, oglądają trzy tabele i zadają bazie cztery pytania.

Scenariusz jest przeznaczony na 45 minut dla klasy rozpoczynającej naukę SQL. Nie wymaga znajomości JOIN: pierwsze zapytanie to zwykły SELECT, a pozostałe są podane w rozwiązaniu i omawiane krok po kroku. Kolejne lekcje: SELECT, JOIN i klucze.

Przygotowanie stanowisk

Potrzebna jest baza MySQL 8 lub MariaDB (na przykład w XAMPP) i narzędzie do wykonywania zapytań, np. phpMyAdmin. Utwórz pustą bazę ćwiczeniową i zaimportuj raz plik baza.sql z pakietu. Plik nie usuwa tabel, więc ponowny import zgłosi błąd istniejącej tabeli. Zapytania zapisuj w start.sql; rozwiązania są w rozwiazania.sql.

Cele i plan lekcji

Po zajęciach uczeń potrafi:

• wyjaśnić pojęcia: baza danych, tabela, wiersz (rekord), kolumna (pole), klucz główny i obcy;
• wskazać w przykładzie encje (książki, czytelnicy) i związek między nimi (wypożyczenia);
• odczytać zapytanie SELECT, JOIN, GROUP BY i LEFT JOIN i przewidzieć jego wynik;
• uzasadnić, dlaczego dane są w kilku tabelach, a nie w jednej.

Co jest w bazie

Baza danych to uporządkowany zbiór danych, którym zarządza system (np. MySQL, SQLite, PostgreSQL). W bazie relacyjnej dane leżą w tabelach — jak arkusze, w których wiersz (rekord) opisuje jeden obiekt, a kolumna (pole) — jedną jego cechę.

Tabela wypozyczenia łączy je: każdy wiersz mówi, kto (czytelnik_id), co (ksiazka_id) i kiedy wypożyczył. W bazie jest pięć takich wierszy: Anna wypożyczyła trzy książki, Bartek — dwie, a Celina żadnej.

Klucze

• Klucz główny (id) jednoznacznie identyfikuje wiersz tabeli.
• Klucz obcy (czytelnik_id, ksiazka_id) wskazuje wiersz w innej tabeli i pilnuje, aby nie powstało wypożyczenie nieistniejącej książki.

Dlaczego kilka tabel?

W jednym arkuszu z kolumnami „imię, tytuł, autor, data” autor i tytuł powtarzałyby się przy każdym wypożyczeniu, a literówka w jednym miejscu dawałaby dwie „różne” książki. Kilka tabel oznacza, że dane o książce zapisano raz, a wypożyczenia tylko do niej odwołują się numerem. Zapytania pytają o dane z wielu tabel naraz: JOIN łączy wiersze po kluczach, GROUP BY grupuje i liczy, LEFT JOIN zachowuje także wiersze bez dopasowania (książki, których nikt nie wypożyczył).

Zadania

Zadanie 1. SELECT. Wypisz tytuł i autora wszystkich książek, alfabetycznie według tytułu.

Zadanie 2. JOIN. Wypisz imię czytelnika, tytuł książki i datę dla każdego wypożyczenia, według numeru wypożyczenia.

Zadanie 3. GROUP BY. Policz, ile książek wypożyczył każdy czytelnik, który cokolwiek wypożyczył — od największej liczby.

Zadanie 4. LEFT JOIN. Wypisz tytuły książek, których nikt nie wypożyczył.

Dla chętnych: dodaj czytelniczkę Celina do wyniku zadania 3 z liczbą 0 (wskazówka: odwróć kolejność tabel i użyj LEFT JOIN).

Rozwiązanie i wyniki

-- Zadanie 1
SELECT tytul, autor
FROM ksiazki
ORDER BY tytul;

-- Zadanie 2
SELECT c.imie, k.tytul, w.data_wypozyczenia
FROM wypozyczenia w
JOIN czytelnicy c ON w.czytelnik_id = c.id
JOIN ksiazki k ON w.ksiazka_id = k.id
ORDER BY w.id;

-- Zadanie 3
SELECT c.imie, COUNT(*) AS ile
FROM wypozyczenia w
JOIN czytelnicy c ON w.czytelnik_id = c.id
GROUP BY c.id, c.imie
ORDER BY ile DESC, c.imie;

-- Zadanie 4
SELECT k.tytul
FROM ksiazki k
LEFT JOIN wypozyczenia w ON w.ksiazka_id = k.id
WHERE w.id IS NULL;

Wyniki (sprawdzone na SQLite z tymi samymi tabelami):

Zadanie 1 — cztery książki w kolejności alfabetycznej (SQLite porządkuje bajtami, więc kolejność polskich liter w innych bazach może się różnić):

Zadanie 2 — pięć wypożyczeń:

Zadanie 3 — dwóch czytelników (Celina nie wypożyczyła niczego, więc nie występuje w wyniku):

Zadanie 4 — jedna książka: Tajemnica mapy.

Typowe błędy

Pytania kontrolne i punktacja

1. Czym różni się klucz główny od klucza obcego?
2. Po co baza ma kilka tabel zamiast jednej?
3. Dlaczego w zadaniu 3 nie ma Celiny?

Odpowiedzi: Klucz główny identyfikuje wiersz w swojej tabeli, klucz obcy wskazuje wiersz w innej. Aby dane zapisać raz, uniknąć powtórzeń i błędów oraz łączyć je zapytaniami. Bo nie ma żadnego jej wypożyczenia, a zwykły JOIN pokazuje tylko czytelników z dopasowanymi wierszami.

Proponowana punktacja (razem 10 punktów): zadanie 1 — SELECT z ORDER BY 2 pkt; zadanie 2 — dwa JOIN z ON 3 pkt; zadanie 3 — GROUP BY z COUNT 2 pkt; zadanie 4 — LEFT JOIN z IS NULL 2 pkt; wyjaśnienie pojęć klucza 1 pkt. Zadanie dla chętnych nie jest wymagane do pełnej oceny.

Materiały do pobrania

• Pakiet SQL: materiały do lekcji, rozwiązanie i karta pracy
• Karta pracy ucznia — otwórz i wydrukuj

Rozpakuj ZIP przed otwarciem plików. Materiały są dostępne bez logowania; publiczne rozwiązanie służy nauce i samodzielnemu sprawdzeniu, a nie poufnemu sprawdzianowi.

Co dalej?

Następne kroki to filtrowanie (WHERE), sortowanie i projektowanie własnych tabel. Ten materiał otwiera dział o bazach danych w kursie SQL w TablicaPro, gdzie znajdziesz prezentacje, ćwiczenia i testy do prowadzenia lekcji.

Źródła techniczne: relacyjne bazy danych w Wikipedii oraz wprowadzenie do SQL w dokumentacji MySQL. Zobacz też lekcję o kluczach.