BAZY DANYCH · INF.03

Model relacyjny i projekt bazy

Zanim napiszesz pierwsze zapytanie, trzeba zrozumieć, jak dane są ułożone. W tym module zamienisz szkic biblioteki na tabele, klucze i relacje, a na końcu odczytasz je w phpMyAdmin.

Tabele, wiersze i encje biblioteki

W szkolnej bibliotece wszystko zaczyna się od kartki i ołówka: kto wypożyczył książkę, kiedy ma ją zwrócić i ile zapłaci za spóźnienie. Kiedy takich zapisów przybywa, zeszyt przestaje wystarczać. Relacyjna baza danych porządkuje te informacje w tabele, w których każdy wiersz opisuje jeden konkretny fakt, a każda kolumna jeden rodzaj informacji.

Model relacyjny opiera się na tabelach. Tabela to zbiór wierszy o tej samej strukturze. Wiersz nazywamy rekordem albo krotką, a kolumnę atrybutem. Każda kolumna ma określoną dziedzinę, czyli zbiór dopuszczalnych wartości: kolumna cena przechowuje liczby z dwoma miejscami po przecinku, kolumna data_urodzenia daty, a tytul tekst. Dzięki temu baza może odrzucić wpis, który nie pasuje do ustalonego porządku.

Encja to osoba, rzecz albo zdarzenie, o którym przechowujemy dane. W bibliotece encjami są uczeń, klasa, książka, autor i wypożyczenie. Encja staje się tabelą, a jej cechy kolumnami. Jeden wiersz tabeli ksiazki opisuje jedną książkę, jeden wiersz tabeli uczniowie jednego ucznia, a jeden wiersz tabeli wypozyczenia jedno konkretne wypożyczenie.

SELECT id, tytul, rok_wydania
FROM ksiazki;

To zapytanie czyta trzy kolumny z tabeli ksiazki. Wynik jest również tabelą: ma nagłówki id, tytul, rok_wydania i tyle wierszy, ile książek znajduje się w bazie. Kolejność wierszy nie jest częścią definicji tabeli. Jeśli nie poprosisz o sortowanie, baza może zwrócić je w dowolnej kolejności, choć w praktyce często są to kolejne wiersze z pliku.

SELECT nazwa, wychowawca
FROM klasy;

Druga tabela pokazuje inną encję. klasy nie przechowuje listy uczniów w jednej komórce. Zamiast tego każdy uczeń w tabeli uczniowie ma numer klasy, który wskazuje odpowiedni wiersz w tabeli klasy. To pozornie mniej wygodne niż jeden arkusz, ale chroni przed powtarzaniem nazwiska wychowawcy przy każdym uczniu i przed błędami, gdy wychowawca się zmieni.

Przykłady z życia

1. Dziennik lekcyjny. Wychowawca prowadzi listę uczniów, oceny i obecności. To trzy różne zbiory informacji, a nie jedna wielka tabela. Gdy ocena trafia do niewłaściwego ucznia, łatwiej ją poprawić, jeśli każdy uczeń ma swój identyfikator, a ocena wskazuje ten identyfikator.

2. Rejestr wypożyczeń w bibliotece. Pani bibliotekarka zapisuje datę wypożyczenia, termin zwrotu i numer książki. Ten sam uczeń może wypożyczyć kilka książek, a ta sama książka może wracać do biblioteki wielokrotnie. Każde takie zdarzenie to osobny wiersz w tabeli wypozyczenia.

3. Lista zakupów. Na kartce zapisujesz produkt, ilość i cenę. To już mała tabela: kolumny to produkt, ilość i cena, a wiersz to jedna pozycja. Jeśli dopiszesz kolumnę „sklep”, nadal pozostaje to tabela, tylko z innym zestawem atrybutów.

Matematyka: tabelę można potraktować jak zbiór uporządkowanych krotek. Każda kolumna ma swoją dziedzinę, a wiersz jest elementem iloczynu dziedzin. To matematyczne spojrzenie wyjaśnia, dlaczego liczba w wierszu musi należeć do właściwego typu i dlaczego nie mieszamy w jednej kolumnie dat z nazwiskami.

Klucze: identyfikator, obcy i złożony

Wyobraź sobie dwie uczennice o imieniu Anna Kowalska. Samo imię i nazwisko nie wystarcza, żeby wskazać właściwą osobę. Potrzebujemy numeru, który należy do dokładnie jednej osoby i nigdy się nie powtarza. W bazie danych taką rolę pełni klucz podstawowy, najczęściej kolumna id z liczbami nadawanymi automatycznie.

Klucz podstawowy (ang. primary key) jednoznacznie identyfikuje każdy wiersz tabeli. W tabeli uczniowie jest to id, w tabeli ksiazki również id, a w tabeli klasy — id klasy. Klucz musi być unikalny i nie może być pusty. W schemacie biblioteki wszystkie klucze podstawowe są liczbami całkowitymi bez znaku, z opcją AUTO_INCREMENT, więc baza sama nadaje kolejny numer, gdy dodajemy wiersz.

SELECT id, nazwa, wychowawca
FROM klasy;

Wynik pokazuje pięć wierszy i pięć różnych wartości id. To właśnie dzięki nim można bezpiecznie odwołać się do klasy numer 3, nawet jeśli w przyszłości zmieni się jej nazwa albo wychowawca. Nazwa klasy jest dobrym kandydatem na klucz tylko wtedy, gdy naprawdę nigdy się nie powtarza i nie zmienia. W praktyce wygodniejszy jest sztuczny numer, bo nie zależy od decyzji administracyjnych.

Klucz obcy (ang. foreign key) to kolumna, która wskazuje klucz podstawowy w innej tabeli. W tabeli uczniowie kolumna klasa_id wskazuje klasy.id. Dzięki temu baza może sprawdzić, że nie istnieje uczeń przypisany do klasy numer 99, jeśli takiej klasy nie ma. Klucz obcy nie musi być unikalny: wiele uczniów może należeć do tej samej klasy, więc wartość klasa_id powtarza się w wielu wierszach.

SELECT imie, nazwisko, klasa_id
FROM uczniowie
ORDER BY klasa_id, nazwisko;

To zapytanie pokazuje, jak wygląda relacja jeden-do-wielu po stronie danych. Kolumna klasa_id jest tym samym kluczem obcym w każdym wierszu, ale jej wartości się powtarzają. Powtórzenie nie jest tu błędem — przeciwnie, jest dowodem, że kilka osób należy do jednej klasy. Błędem byłoby natomiast wpisanie numeru klasy, której nie ma w tabeli klasy.

Klucz złożony powstaje z dwóch lub więcej kolumn, które razem muszą być unikalne. W tabeli ksiazki_autorzy para ksiazka_id i autor_id jest unikalna: ta sama książka nie może mieć dwa razy tego samego autora, ale może mieć wielu różnych autorów. Podobnie w tabeli opinie para uczeń–książka jest unikalna, bo jeden uczeń wystawia jednej książce tylko jedną opinię.

SELECT ksiazka_id, autor_id, rola
FROM ksiazki_autorzy
WHERE ksiazka_id = 4
ORDER BY autor_id;

Książka numer 4, „Wiedźmin: Ostatnie życzenie”, ma dwóch autorów: Andrzeja Sapkowskiego jako autora i Marcina Krajewskiego jako współautora. Para kluczy jest różna, więc oba wiersze mogą istnieć. Gdybyśmy próbowali dodać drugi raz tę samą parę, baza odrzuciłaby wpis dzięki ograniczeniu UNIQUE.

Przykłady z życia

1. Numer legitymacji szkolnej. Imię i nazwisko może się powtórzyć, ale numer legitymacji jest przypisany do jednej osoby. To naturalny odpowiednik klucza podstawowego. Gdy uczeń zmienia klasę, numer legitymacji pozostaje ten sam, więc dane w innych tabelach nadal wskazują właściwą osobę.

2. Numer zamówienia w sklepie internetowym. Każde zamówienie ma swój numer, a pozycje zamówienia wskazują ten numer jako klucz obcy. Jedno zamówienie może mieć wiele pozycji, więc numer zamówienia powtarza się w tabeli pozycji — dokładnie tak jak klasa_id w tabeli uczniów.

3. Rejestr książek według ISBN. ISBN identyfikuje wydanie książki, ale biblioteka może mieć kilka egzemplarzy tego samego tytułu. Dlatego w bazie potrzebny jest osobny id egzemplarza, a ISBN jest dodatkowym kluczem unikalnym. Dwa różne egzemplarze mają ten sam ISBN, ale różne id.

Informatyczna pracownia: w aplikacji do wypożyczeń przycisk „zapisz” wysyła do bazy numer ucznia i numer książki. Jeśli program wyśle numer ucznia, który nie istnieje, baza odrzuci rekord dzięki kluczowi obcemu. To znacznie bezpieczniejsze niż poleganie wyłącznie na sprawdzaniu w formularzu, bo formularz można obejść, a ograniczenie w bazie działa zawsze.

Relacje, normalizacja i ERD biblioteki

Tabele nie istnieją w izolacji. Uczeń należy do klasy, książka ma kategorię i wydawnictwo, a wypożyczenie łączy ucznia z książką. Te powiązania nazywamy relacjami. Najczęstsza relacja to jeden-do-wielu: jednej klasie odpowiada wielu uczniów, ale każdy uczeń należy do jednej klasy. W bazie biblioteki widać ją po kluczu obcym klasa_id w tabeli uczniowie.

Podobnie jedna kategoria obejmuje wiele książek, a jedna książka ma jedną kategorię. Relację jeden-do-wielu zapisujemy po stronie „wielu”: kolumna kategoria_id znajduje się w tabeli ksiazki, a nie odwrotnie. Gdybyśmy w tabeli kategorie wpisali listę książek, musielibyśmy upchnąć wiele tytułów w jednej komórce albo dodawać kolejne kolumny, co szybko stałoby się nieczytelne.

SELECT tytul, kategoria_id, wydawnictwo_id
FROM ksiazki
ORDER BY kategoria_id, tytul;

Ten wynik pokazuje same numery, a nie nazwy kategorii i wydawnictw. To celowe: nazwa kategorii jest przechowywana tylko w tabeli kategorie, a nazwa wydawnictwa tylko w tabeli wydawnictwa. Jeśli zmienisz nazwę kategorii, poprawiasz jeden wiersz, a wszystkie książki nadal wskazują właściwy numer. To zasada jednego źródła prawdy.

Nie każdą relację da się przedstawić kluczem obcym w jednej z dwóch tabel. Książka może mieć kilku autorów, a jeden autor może napisać wiele książek. To relacja wiele-do-wielu. Rozwiązujemy ją tabelą pośrednią ksiazki_autorzy, która zawiera parę kluczy: ksiazka_id i autor_id. Podobnie uczeń i książka spotykają się w tabeli wypozyczenia, która dodatkowo przechowuje daty, karę i status — informacje należące do samego zdarzenia, a nie do osoby ani do książki.

Normalizacja to proces porządkowania tabel tak, aby dane nie były przechowywane wielokrotnie i aby nie powstały sprzeczności. Pierwsza postać normalna wymaga wartości atomowych: w kolumnie telefon wpisujemy jeden numer, a nie listę numerów rozdzielonych przecinkami. Druga postać normalna zakazuje zależności od tylko części klucza złożonego. W tabeli ksiazki_autorzy rola autora zależy od pary książka–autor, a nie od samego numeru książki, więc jest poprawnie umieszczona.

Trzecia postać normalna usuwa zależności przechodnie. Gdyby tabela ksiazki zawierała także nazwa_kategorii i miasto_wydawnictwa, nazwa kategorii zależałaby od kategoria_id, a nie bezpośrednio od książki. Zmiana nazwy kategorii wymagałaby poprawienia wielu wierszy. Dlatego trzymamy tylko klucze obce, a nazwy i miasta w tabelach słownikowych. To nie jest „nadmiar tabel”, lecz sposób na uniknięcie nadmiaru błędów.

Przykłady z życia

1. Plan lekcji. Jeden nauczyciel uczy wielu klas, a jedna klasa ma wielu nauczycieli. Gdyby plan zapisywać w jednej tabeli z powtarzającą się nazwą klasy przy każdym nauczycielu, literówka w nazwie rozbiłaby porządek. Tabela pośrednia „nauczyciel–klasa–przedmiot” rozwiązuje ten problem podobnie jak ksiazki_autorzy.

2. Zamówienie w stołówce. Jedno zamówienie może zawierać kilka dań, a jedna potrawa może wystąpić w wielu zamówieniach. Potrzebna jest tabela pozycji zamówienia z kluczami zamowienie_id i danie_id. Sam numer zamówienia w tabeli dań nie wystarczy, bo danie nie należy do jednego zamówienia.

3. Katalog biblioteczny. Czytelnik szuka książki po autorze, a autor może mieć wiele książek. Ekran katalogu pokazuje autora i tytuły, ale w bazie są to dwie tabele połączone tabelą ksiazki_autorzy. Dzięki temu nie trzeba wpisywać nazwiska autora przy każdym tytule.

Matematyka: relację można opisać jak funkcję. Numer klasy prowadzi do jednego wychowawcy, numer książki do jednej kategorii. Normalizacja pilnuje, aby każda informacja zależała od właściwego klucza, a nie od przypadkowej kopii. To ta sama zasada, którą w matematyce nazywamy jednoznacznością przyporządkowania.

Ćwiczenia SQL

Zadania do samodzielnego wykonania w edytorze SQL.

• 1Pierwsze zapytanie do bibliotekiWyświetl tytuły i ceny pięciu najdroższych dostępnych książek, od najdroższej.Zadanie i podpowiedzi →
• 2Klucze w tabeli klasOdczytaj identyfikatory, nazwy i wychowawców wszystkich klas w kolejności rosnącej po id.Zadanie i podpowiedzi →
• 3Klucz obcy uczniaPokaż uczniów wraz z numerem klasy, sortując po klasie i nazwisku.Zadanie i podpowiedzi →
• 4Para kluczy w tabeli autorówPrzejrzyj powiązania książek i autorów, sortując po książce oraz autorze.Zadanie i podpowiedzi →