Ćwiczenie – Tworzenie struktury bazy danych SQL (INF.03)

1. Tworzenie tabeli uczniów

Cel: Uczeń potrafi utworzyć prostą tabelę z kolumnami i kluczem głównym.
Opis: Stwórz tabelę uczniowie z kolumnami: id_ucznia, imie, nazwisko, klasa.
Po co to robimy?
Większość zadań INF.03 startuje od tabeli z danymi osobowymi, takimi jak uczniowie lub pracownicy.
Jeżeli tabela jest zaprojektowana dobrze (ma klucz główny i odpowiednie typy), późniejsze zapytania SELECT i JOIN są dużo prostsze. 
CREATE TABLE uczniowie (
    id_ucznia INT AUTO_INCREMENT PRIMARY KEY,
    imie VARCHAR(30),
    nazwisko VARCHAR(40),
    klasa VARCHAR(10)
);
Jak o tym myśleć?
– id_ucznia to unikalny numer ucznia – dzięki AUTO_INCREMENT baza sama nadaje kolejne wartości.
– VARCHAR wybieramy dla tekstu, a długość (np. 30) powinna być rozsądna, ale nie przesadzona.
– Gdy później pojawi się tabela oceny lub uczniowie_przedmioty, to właśnie id_ucznia będzie tam kluczem obcym.
Typowy błąd na egzaminie:
Pomylenie CREATE TABLE z INSERT INTO, albo brak PRIMARY KEY w tabeli, mimo że opis bazy jasno wymaga unikalnego identyfikatora.

2. Ograniczenia (constraints) i walidacja danych

Cel: Uczeń stosuje ograniczenia NOT NULL, UNIQUE, CHECK i DEFAULT.
Opis: Dodaj ograniczenia do tabeli, aby dane były zawsze poprawne.
Co daje constraint?
Ograniczenia wymuszają poprawność danych na poziomie bazy, a nie tylko w programie.
Dzięki temu nie zapiszesz przedmiotu bez nazwy, liczby godzin mniejszej lub równej zero, ani dziwnego typu zajęć. 
CREATE TABLE przedmioty (
    id_przedmiotu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa VARCHAR(40) NOT NULL UNIQUE,
    godziny INT CHECK (godziny > 0),
    typ ENUM('obowiązkowy', 'dodatkowy') DEFAULT 'obowiązkowy'
);
Dlaczego tak?
– NOT NULL pilnuje, żeby nazwa nie była pusta.
– UNIQUE gwarantuje, że ten sam przedmiot nie pojawi się dwa razy pod tą samą nazwą.
– CHECK (godziny > 0) blokuje błędne wartości typu 0 lub -5.
– DEFAULT 'obowiązkowy' ustawia domyślny typ, gdy nic nie podasz.
Uwaga egzaminacyjna:
Jeżeli w treści zadania pojawia się zakres („liczba godzin większa od zera”, „średnia od 1 do 6”), to zwykle da się za to dostać punkt za dobrze dobrane CHECK.

3. Tworzenie relacji między tabelami (FOREIGN KEY)

Cel: Uczeń tworzy relację jeden–wiele (1:N) pomiędzy tabelami.
Opis: Połącz uczniów z ich klasami za pomocą klucza obcego.
Najpierw zadaj sobie pytanie:
Czy nazwa klasy (np. „1TI”, „4B”) będzie powtarzać się w wielu rekordach uczniów? Jeśli tak, powinna trafić do osobnej tabeli, a w uczniowie zostawiasz tylko powiązanie. 
CREATE TABLE klasy (
    id_klasy INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    wychowawca VARCHAR(40)
);

ALTER TABLE uczniowie
ADD COLUMN id_klasy INT,
ADD CONSTRAINT fk_uczen_klasa
FOREIGN KEY (id_klasy) REFERENCES klasy(id_klasy);
Jak to działa?
– Każda klasa ma swój numer id_klasy i symbol, np. „3TI”.
– W tabeli uczniowie dopisujemy kolumnę id_klasy i robimy z niej klucz obcy.
– Dzięki temu baza nie pozwoli przypisać ucznia do klasy, która nie istnieje w tabeli klasy.
Częsty błąd:
Pomyłka w nazwie kolumny w kluczu obcym (np. id_klasa zamiast id_klasy) powoduje błąd wykonania skryptu, a w arkuszu INF.03 – utratę punktów za cały podpunkt.

4. Tworzenie tabeli z relacją wiele–wiele (N:M)

Cel: Uczeń potrafi tworzyć tabelę pośredniczącą w relacjach N:M.
Opis: Stwórz relację pomiędzy uczniami a przedmiotami (np. zapis ucznia na przedmiot).
Kiedy mamy relację N:M?
Gdy jeden uczeń może mieć wiele przedmiotów, a jeden przedmiot może być nauczany wielu uczniom.
Same klucze obce w dwóch tabelach nie wystarczą – potrzebna jest dodatkowa tabela pośrednicząca. 
CREATE TABLE uczniowie_przedmioty (
    id_ucznia INT,
    id_przedmiotu INT,
    PRIMARY KEY (id_ucznia, id_przedmiotu),
    FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id_ucznia),
    FOREIGN KEY (id_przedmiotu) REFERENCES przedmioty(id_przedmiotu)
);
Dlaczego klucz główny jest podwójny?
– PRIMARY KEY (id_ucznia, id_przedmiotu) oznacza, że ten sam uczeń nie może być dwa razy zapisany na ten sam przedmiot.
– Każdy rekord opisuje jedną parę: konkretny uczeń – konkretny przedmiot.
Uwaga egzaminacyjna:
W pytaniach testowych odpowiedź o tabeli pośredniczącej (z dwoma kluczami obcymi) jest poprawna, natomiast „połączenie dwóch tabel samymi kluczami obcymi” opisuje tylko relację 1:N, a nie N:M.

5. Zadanie końcowe – Projekt bazy „Szkoła”

Polecenie:
  • Utwórz bazę danych szkola.
  • Dodaj tabele: uczniowie, klasy, nauczyciele, przedmioty, oceny.
  • Zdefiniuj odpowiednie klucze główne i obce.
  • Zastosuj ograniczenia NOT NULL, CHECK, UNIQUE.
Podpowiedź do projektu:
– uczniowie: klucz główny id_ucznia, klucz obcy do klasy.
– klasy: klucz główny id_klasy, unikalny symbol klasy.
– nauczyciele: klucz główny id_nauczyciela, dane osobowe, np. imię, nazwisko, specjalność.
– przedmioty: klucz główny id_przedmiotu, nazwa, liczba godzin, klucz obcy do nauczyciela prowadzącego.
– oceny: klucz główny (np. id_oceny) lub para (id_ucznia, id_przedmiotu, data), plus klucze obce do ucznia i przedmiotu oraz CHECK na wartość oceny (np. 1–6).
Po wykonaniu tego ćwiczenia:
Uczeń potrafi zaprojektować kompletną strukturę relacyjnej bazy danych dla szkoły, odwzorowując najczęstszy typ zadania projektowego spotykany w arkuszach INF.03.