INF.03 · Bazy danych i SQL

Zanim napiszesz SQL: jak zaprojektować bazę na papierze

Dziennik elektroniczny, sklep internetowy, rezerwacja biletów: każda taka aplikacja gdzieś trwale trzyma dane i musi wiedzieć, jak się ze sobą łączą. Błąd w projekcie bazy kosztuje potem dużo więcej niż błąd w zapytaniu. Dlatego zaczynamy od rysunku, a nie od kodu.

  • około 30 minut
  • projektowanie, nie programowanie
  • model E-R

Po tej lekcji: rozpoznasz encje, atrybuty i klucze, określisz typ relacji (1:1, 1:N, N:M) i będziesz wiedzieć, kiedy potrzebna jest tabela pośrednicząca.

1 · Słownik projektanta

Cztery słowa, których używa się na egzaminie

Relacyjna baza danych to zbiór tabel. Dane z różnych tabel łączą się ze sobą przez klucze. Zanim zaprojektujesz własną bazę, poznaj cztery pojęcia, a rysunek, który z nich powstaje, nazywa się modelem E-R (od angielskich słów entity i relationship).

Encjarzecz, o której zbieramy dane

Obiekt lub rzecz, o której przechowujemy dane. W SQL zwykle zostaje tabelą.

Na przykład UCZEŃ, KLASA, PRODUKT.

Atrybutcecha encji

Cecha encji. W SQL zostaje kolumną.

Na przykład imię, nazwisko, cena.

Klucz głównyPRIMARY KEY

Kolumna (czasem kilka), która jednoznacznie rozpoznaje wiersz w swojej tabeli. Wartości nie mogą się powtarzać ani być puste.

Na przykład id_ucznia w tabeli Uczniowie.

Klucz obcyFOREIGN KEY

Kolumna, która wskazuje na klucz główny w innej tabeli. To klucze obce tworzą relacje między tabelami.

Na przykład id_klasy w tabeli Uczniowie wskazuje wiersz w tabeli Klasy.

2 · Klucz główny

Która kolumna może być kluczem?

Klucz główny musi spełniać dwa warunki: jego wartości się nie powtarzają i nie ma w nim NULL. Zaznacz kolumny, które według Ciebie nadają się na klucz, i sprawdź, co o tym powie baza. Podświetlone wiersze to powtórzenia, a pomarańczowe komórki to NULL.

Ta interakcja wymaga włączonego JavaScriptu.

3 · Relacje

1:1, 1:N czy N:M?

Typ relacji ustalasz z opisu sytuacji. Zadaj dwa pytania: ile wierszy z tabeli B może przypadać na jeden wiersz z tabeli A, i odwrotnie. Rozstrzygnij sześć sytuacji. Po każdej zobaczysz, jak to wygląda w tabelach.

Ta interakcja wymaga włączonego JavaScriptu.

4 · Z rysunku do tabel

Tak wygląda relacja 1:N w SQL

Model E-R to rysunek: encje, atrybuty i połączenia. Dopiero potem tłumaczysz go na tabele. Na rysunku widzisz Klasy i Uczniów. W SQL robisz z tego dwie tabele i jeden klucz obcy.

szkola.sqlsql
CREATE TABLE Klasy (
    id_klasy INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(10) NOT NULL,
    wychowawca VARCHAR(80)
);

CREATE TABLE Uczniowie (
    id_ucznia INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(50),
    nazwisko VARCHAR(80),
    id_klasy INT NOT NULL,
    FOREIGN KEY (id_klasy) REFERENCES Klasy(id_klasy)
);
  1. Kolejność tabel. Klasy tworzysz przed Uczniowie, bo klucz obcy może wskazywać tylko tabelę, która już istnieje.
  2. id_klasy w Uczniowie. To klucz obcy. Zamiast przepisywać nazwę klasy przy każdym uczniu, zapisujesz jej numer. Dane się nie powtarzają, tylko odwołują do siebie.
  3. NOT NULL. Oznacza, że każdy uczeń musi należeć do jakiejś klasy.

5 · Zanim się pomylisz

Trzy pomyłki w projekcie bazy

Rozwiń każdą z nich i zobacz, co powie baza albo dlaczego projekt się nie sprawdzi.

Klucz obcy zapisany jako klucz główny
Tak nie
CREATE TABLE Uczniowie (
    id_ucznia INT PRIMARY KEY,
    id_klasy INT PRIMARY KEY
);

Błąd: Multiple primary key defined. Tabela może mieć tylko jeden klucz główny, a id_klasy powinno wskazywać inną tabelę.

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

Klucz główny jest jeden i rozpoznaje wiersz we własnej tabeli. id_klasy jest kluczem obcym.

Relacja N:M bez tabeli pośredniczącej
Tak nie
CREATE TABLE Uczniowie (
    id_ucznia INT PRIMARY KEY,
    przedmioty VARCHAR(100)  -- np. '1,2,3'
);

Baza nie zgłosi błędu, ale to błąd projektu. Lista w jednej komórce nie tworzy relacji: nie połączysz jej przez JOIN i nikt nie pilnuje, czy przedmiot istnieje.

Tak
CREATE TABLE Uczen_Przedmiot (
    id_ucznia INT NOT NULL,
    id_przedmiotu INT NOT NULL,
    PRIMARY KEY (id_ucznia, id_przedmiotu),
    FOREIGN KEY (id_ucznia) REFERENCES Uczniowie(id_ucznia),
    FOREIGN KEY (id_przedmiotu) REFERENCES Przedmioty(id_przedmiotu)
);

Każdy zapis „uczeń chodzi na przedmiot” to jeden wiersz. Klucz złożony pilnuje, żeby nikt nie zapisał się dwa razy na to samo.

Tabela podrzędna utworzona przed nadrzędną
Tak nie
CREATE TABLE Uczniowie (
    id_ucznia INT PRIMARY KEY,
    id_klasy INT,
    FOREIGN KEY (id_klasy) REFERENCES Klasy(id_klasy)
);
-- tabeli Klasy jeszcze nie ma

Błąd: baza nie może otworzyć tabeli, do której wskazuje klucz obcy (w MySQL ERROR 1824, w MariaDB ERROR 1005).

Tak
CREATE TABLE Klasy (...);
CREATE TABLE Uczniowie (...);

Najpierw tabele, na które inne tabele wskazują. Dopiero potem tabele z kluczami obcymi.

6 · Sprawdź swój projekt

Czy projekt bazy jest gotowy?

Na egzaminie INF.03 pojawiają się pytania o symbole na diagramach E-R i typ relacji z opisu sytuacji. W części praktycznej możesz dostać opis (na przykład biura turystycznego) i zaprojektować na jego podstawie bazę. Przejdź przez tę listę, zanim oddasz projekt.

Podsumowanie

Co warto zapamiętać

  1. Encja to tabela, atrybut to kolumna.
  2. Klucz główny rozpoznaje wiersz we własnej tabeli, a klucz obcy wskazuje wiersz w innej.
  3. 1:1 i 1:N zapisujesz kluczem obcym (przy 1:N po stronie „wielu”), a N:M wymaga tabeli pośredniczącej.
  4. Tabele z kluczami obcymi tworzysz po tabelach, na które wskazują.
  5. Najpierw projekt na papierze, potem SQL.

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