Od modelu ER do modelu relacyjnego

✅ Sprawdź się z wykładu 2

Od diagramu do działającej bazy

Na poprzednim spotkaniu ustaliliśmy, jakie obiekty opisuje ośrodek „Dąb”. Teraz sprawdzimy, czy potrafimy zapisać te ustalenia w bazie i rozpoznać niepoprawne operacje. Model relacyjny opisuje logiczną strukturę danych: relacje, atrybuty, klucze i ograniczenia. Nie opisuje fizycznego rozmieszczenia bajtów na dysku.

Przez cały pokaz budujemy jeden przykład w PostgreSQL, używając pgAdmin; Waszym zadaniem jest przewidywać wyniki i proponować próby naruszenia reguł. Nie trzeba jeszcze samodzielnie odtwarzać całej składni SQL.

Cztery obiekty, które zamienimy w tabele

Pojęcie Tabela Co opisuje jeden wiersz?
Uczestnik participants Jedną osobę, np. U2 Bartek
Kurs courses Temat szkolenia, np. K1
Edycja editions Konkretne przeprowadzenie kursu, np. E1
Zapis enrollments Udział jednej osoby w jednej edycji

W tym prostym modelu typy encji odwzorowujemy na tabele, a ich proste atrybuty na kolumny. Konkretny obiekt odpowiada wierszowi. Nie każdy model ER daje jednak mechanicznie jedną tabelę na każdy prostokąt: znaczenie mają również związki, atrybuty wielowartościowe i sposób modelowania specjalizacji.

Sale i prowadzący nadal należą do pełnego projektu. Dzisiejszy pokaz jest jego wycinkiem; nie implementujemy jeszcze reguł kolizji terminów.

Pusta baza i pierwsze reguły

Pokaz rozpoczyna się w pustej bazie prba3_pokaz. Środowisko Compose kursu przygotowuje ją bez tabel. W pgAdmin wybieramy tę bazę i otwieramy Query Tool. Auto commit pozostaje włączone. Komplet plików SQL — pobierz ZIP.

SELECT version();
SELECT current_database(), current_user;

Pierwsze zapytanie identyfikuje wersję serwera, drugie pomaga upewnić się, że polecenia trafią do właściwej bazy. Kluczy obcych nie włączamy osobnym poleceniem: po zadeklarowaniu są sprawdzane przez PostgreSQL. Tabele mają zdefiniowane typy kolumn; nie dodajemy słowa STRICT do CREATE TABLE. Dla terminu edycji używamy timestamp, czyli typu daty i czasu.

Uczestnik i jego tożsamość

CREATE TABLE participants (
    participant_id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO participants VALUES
    ('U1', 'Ada'), ('U2', 'Bartek'), ('U3', 'Celina');
SELECT * FROM participants ORDER BY participant_id;

PRIMARY KEY (klucz główny) identyfikuje wiersz. NOT NULL zabrania braku wartości; TEXT określa jej typ. Imię nie jest identyfikatorem: wiele osób może mieć to samo imię. W tym projekcie nie potrzebujemy PESEL-u.

Przewidźcie: co się stanie, gdy dodamy Daniela jako U1? Czy wynik zależy od tego, że ma inne imię? Sprawdźcie też różnicę między NULL i pustym tekstem: NOT NULL nie zakazuje automatycznie pustego napisu.

Kurs i edycja: odwzorowanie 1:N

CREATE TABLE courses (
    course_id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO courses VALUES
    ('K1', 'Obsluga wyposazenia'),
    ('K2', 'Pierwsza pomoc');
CREATE TABLE editions (
    edition_id TEXT NOT NULL PRIMARY KEY,
    course_id TEXT NOT NULL REFERENCES courses(course_id),
    starts_at TIMESTAMP NOT NULL,
    seat_limit INTEGER NOT NULL CHECK (seat_limit > 0)
);
INSERT INTO editions VALUES
    ('E1', 'K1', '2027-02-01 08:00', 2),
    ('E2', 'K1', '2027-02-01 10:00', 4),
    ('E3', 'K2', '2027-02-01 12:00', 4);

FOREIGN KEY (klucz obcy), tutaj zapisany przez REFERENCES, wymaga poprawnego odwołania do kursu. Dla tego związku 1:N umieszczamy je po stronie wielu, czyli w edycji. E1 i E2 wskazują K1, lecz pozostają różnymi edycjami.

NOT NULL wymaga przypisania kursu każdej edycji. Nie wynika z tego, że każdy kurs musi już mieć edycję. To różnica między maksymalną licznością a obowiązkowym udziałem.

Przewidźcie: czy baza przyjmie edycję nieistniejącego K99? Czy przyjmie limit 0? Zwróćcie uwagę, że są to dwie różne reguły: klucz obcy i CHECK.

Zapis: odwzorowanie N:M

Jedna osoba może uczestniczyć w wielu edycjach, a edycja przyjmuje wiele osób. Jedna kolumna edition_id w uczestniku nie opisze tego bez utraty informacji. Tworzymy tabelę łączącą, która przechowuje również cechy związku.

CREATE TABLE enrollments (
    participant_id TEXT NOT NULL
        REFERENCES participants(participant_id),
    edition_id TEXT NOT NULL REFERENCES editions(edition_id),
    status TEXT NOT NULL
        CHECK (status IN ('confirmed', 'cancelled')),
    PRIMARY KEY (participant_id, edition_id)
);
INSERT INTO enrollments VALUES
    ('U1', 'E1', 'confirmed'),
    ('U2', 'E1', 'confirmed'),
    ('U3', 'E2', 'confirmed'),
    ('U2', 'E2', 'cancelled');

Klucz złożony obejmuje parę identyfikatorów. U2 może wystąpić w wielu wierszach; E2 również. Nie wolno jednak powtórzyć tej samej pary U2–E2. Dwa klucze obce same nie zapewniają unikalności pary.

Status opisuje zapis, nie całego uczestnika ani całą edycję. Bartek może mieć confirmed na E1 i cancelled na E2, podczas gdy Celina ma confirmed na E2. Ponowne zgłoszenie Bartka aktualizuje istniejący zapis po kontroli miejsc.

erDiagram
    courses ||--o{ editions : ma
    participants ||--o{ enrollments : posiada
    editions ||--o{ enrollments : obejmuje

Diagram przedstawia powiązania czterech tabel. Nie pokazuje wszystkich ograniczeń, np. limitu liczby potwierdzonych zapisów. W pgAdmin strukturę oglądamy pod Schemas → public → Tables, a polecenia wykonujemy w Query Tool. Wyniki zapytań pojawiają się w Data Output, błędy w Messages. Po utworzeniu tabel może być potrzebne Refresh w drzewie obiektów. Ten rysunek jest ilustracją modelu, nie automatycznym widokiem programu.

Próby naruszenia reguł

Przed każdą operacją rozstrzygamy: przyjęta czy odrzucona, i dlaczego? Pliki 02, 03, 06, 07, 10, 11, 12 i 13 celowo zgłaszają błędy. Wykonujemy je osobno, przy włączonym Auto commit. Błąd wewnątrz jawnej transakcji wymaga ROLLBACK przed dalszą pracą — nie oznacza uszkodzenia bazy.

  1. Nowa osoba z istniejącym identyfikatorem U1.
  2. Edycja wskazująca nieistniejący kurs.
  3. Drugi zapis tej samej osoby do tej samej edycji.
  4. Zapis nieistniejącego uczestnika.
  5. Status banana zamiast jednej z dozwolonych wartości.
  6. Usunięcie Bartka, kiedy istnieją jego zapisy.

Powiązania mają tu domyślne zachowanie przy usuwaniu. Nie deklarujemy ON DELETE CASCADE. PostgreSQL odrzuci usunięcie uczestnika, które pozostawiłoby zapisy bez poprawnego odwołania.

Zmiana jednego zapisu

UPDATE enrollments SET status = 'cancelled'
WHERE participant_id = 'U2' AND edition_id = 'E1';

Dlaczego potrzebujemy obu warunków? Ograniczenie tylko do U2 zmieniłoby wszystkie zapisy Bartka. Brak WHERE zmieniłby wszystkie wiersze. Sam uczestnik i jego zapis na E2 pozostają bez zmian.

Jedno pole statusu przechowuje aktualny stan. Po sekwencji potwierdzenie, rezygnacja, ponowne potwierdzenie nie odtworzymy dat i liczby zmian bez osobnego rejestru zdarzeń. Pełna historia nie jest obecnie wymaganiem.

Czy można przekroczyć limit mimo poprawnych kluczy?

Przywracamy podstawowy stan: na E1 są Ada i Bartek, limit wynosi 2. Następnie próbujemy dopisać Celinę:

INSERT INTO enrollments VALUES ('U3', 'E1', 'confirmed');

Ta operacja zostanie przyjęta przez pokazany schemat. Wszystkie odwołania są poprawne, para jest nowa, status dozwolony. CHECK (seat_limit > 0) sprawdza wartość limitu, a nie liczbę zapisów w innej tabeli.

To nie jest kompletny system przyjmowania zgłoszeń. Ochrona limitu wymaga dodatkowego mechanizmu, który obejmie sprawdzenie i modyfikację również przy równoczesnych operacjach. W PostgreSQL zwykły CHECK nie może zawierać podzapytania. Dzisiaj rozpoznajemy brakującą ochronę, zamiast udawać, że sam diagram ją zapewnia.

Uzupełnienie reguł mapowania

Atrybut złożony i wielowartościowy

Adres może zostać rozbity na ulicę, kod pocztowy i miasto, jeżeli te części mają samodzielne znaczenie w wymaganiach. Nie twierdzimy, że z tekstowego adresu nie da się nic wyszukać; oddzielne kolumny dają bardziej jednoznaczną strukturę.

Wiele telefonów uczestnika odwzorowujemy w osobnej tabeli, np. participant_phones(participant_id, phone), z kluczem pary i odwołaniem do osoby. Nie dodajemy kolumn phone1, phone2, phone3, gdy liczba telefonów nie jest stałym wymaganiem. Obsługa JSON w niektórych bazach nie zastępuje automatycznie tego sposobu modelowania.

Związek 1:1

Jeżeli w uproszczonym systemie jedna osoba ma najwyżej jedną aktualną kartę wstępu, tabela kart może mieć participant_id NOT NULL UNIQUE oraz klucz obcy do uczestnika. Klucz obcy chroni istnienie osoby; UNIQUE ogranicza liczbę kart na osobę. Nie wymusza to, aby każda osoba miała kartę. Obowiązkowy udział po obu stronach jest dodatkowym wymaganiem.

Co zabieramy na następne zajęcia?

Potrafimy wyjaśnić, dlaczego istnieje każda tabela, gdzie znajdują się klucze oraz jaki błąd blokuje dane ograniczenie. Na wykładzie 4 rozwiniemy SQL: odczytywanie, filtrowanie, łączenie i zmienianie danych. Dzisiejszy pokaz służy sprawdzeniu projektu; nie wymaga zapamiętania całej składni.

Zadanie towarzyszące pokazowi.

Źródła techniczne: PostgreSQL: ograniczenia tabel, typy daty i czasu, transakcje.