Model relacyjny i podstawy SQL

Analiza ewidencji magazynowej

Magazyn „Baza Brzoza” prowadzi ewidencję wydań wyposażenia. Dane przeniesiono z dwóch arkuszy do SQLite. Sprzeczne wydania R-01 pozostają w ewidencji: przeniesienie danych do bazy nie rozstrzyga, który dokument opisuje rzeczywistość.

Polecenia CREATE TABLE definiują strukturę bazy, a INSERT dodaje dane. SELECT pozwala je odczytać: od pojedynczych wierszy po raport ujawniający sprzeczności w ewidencji.

Tabela Znaczenie
items Egzemplarze wyposażenia: R-01, L-01 oraz niewydany R-02
teams Zespoły Alfa i Bravo
loans Wydania: egzemplarz, zespół, czas wydania i opcjonalny zwrot

W bazie dzień 1 ma umowną datę 2027-02-01. Brak zwrotu jest reprezentowany przez NULL, nie jako myślnik z tabeli na stronie. Zwrócony egzemplarz może później zostać wydany ponownie; powtórzenie identyfikatora w historii nie jest samo w sobie błędem.

Odczyt danych i identyfikacja niezakończonych wydań

SELECT * FROM loans ORDER BY loan_id;
SELECT * FROM loans WHERE returned_at = NULL;
SELECT * FROM loans WHERE returned_at IS NULL;

Dwa ostatnie warunki mają różne znaczenie. NULL oznacza brak wartości; porównanie przez = nie daje tu TRUE. IS NULL wybiera niezakończone wydania.

Raport z nazwami zamiast samych identyfikatorów

SELECT l.loan_id, i.item_id, i.name AS item,
       t.name AS team, l.issued_at
FROM loans AS l
JOIN items AS i ON i.item_id = l.item_id
JOIN teams AS t ON t.team_id = l.team_id
WHERE l.returned_at IS NULL
ORDER BY i.item_id, l.loan_id;

Raport zestawia istniejące zapisy dotyczące R-01, lecz nie rozstrzyga ich prawdziwości. W przypadku sprzecznych danych zachowanie wszystkich powiązanych wierszy umożliwia identyfikację konfliktu. Arbitralne ograniczenie wyniku do jednego wiersza ukryłoby problem ewidencji.

Wykrywanie naruszeń reguły pojedynczego wydania

SELECT item_id, COUNT(*) AS open_loans
FROM loans
WHERE returned_at IS NULL
GROUP BY item_id
HAVING COUNT(*) > 1;

WHERE ogranicza wejście do niezakończonych wydań. GROUP BY tworzy grupę dla każdego egzemplarza. HAVING wybiera grupy przekraczające dozwoloną liczbę. To zapytanie wykrywa istniejący problem; samo nie blokuje następnego zapisu.

Uwzględnienie egzemplarzy bez aktywnych wydań

SELECT i.item_id, COUNT(l.loan_id) AS open_loans
FROM items AS i
LEFT JOIN loans AS l
  ON l.item_id = i.item_id AND l.returned_at IS NULL
GROUP BY i.item_id
ORDER BY i.item_id;

Tabela items stanowi podstawę raportu, dzięki czemu wynik obejmuje również R-02 bez wydań. Agregacja zlicza niepuste identyfikatory wydań. Zastosowanie COUNT(*) oznaczałoby zliczanie wszystkich wierszy wyniku złączenia.

Weryfikacja źródłowa i korekta danych

Sama sprzeczność w ewidencji nie wskazuje, który zapis jest błędny. Korekta wymaga dodatkowej informacji, np. dokumentu potwierdzającego czas zwrotu R-01. Dopiero na tej podstawie można zaktualizować konkretne wydanie i ponowić raport konfliktów. Numer ani kolejność wiersza nie rozstrzygają prawdziwości danych.

Zadanie: analiza ewidencji magazynu.

Materiał referencyjny

Dalsze sekcje rozwijają model relacyjny, ograniczenia integralności i składnię SQL, w tym różnice między dialektami oraz operacje na schemacie i danych.

Model relacyjny – przypomnienie i pojęcia podstawowe

Relacja = tabela

  • Dane przechowywane są w tabelach (ang. relations).
  • Każdy wiersz (row) to rekord / krotka.
  • Każda kolumna (column) to atrybut.
  • Dziedzina (domain) to zbiór dopuszczalnych wartości w danej kolumnie.

Przykład relacji STUDENT:

id imie nazwisko rok_urodzenia
1 Adam Mickiewicz 1798
2 Henryk Sienkiewicz 1846

Schemat relacji: tabela STUDENT z kolumnami i wierszami

Klucze i więzy integralności

Klucz główny (PRIMARY KEY)

  • Unikalnie identyfikuje każdy wiersz w tabeli.
  • Nie może przyjmować wartości NULL.
  • W tabeli może występować tylko jeden klucz główny.

Klucz obcy (FOREIGN KEY)

  • Tworzy powiązanie z inną tabelą.
  • Wymusza spójność danych – nie można wprowadzić wartości, której nie ma w tabeli nadrzędnej.

Inne więzy integralności

  • UNIQUE – zapewnia unikalność wartości w kolumnie.
  • NOT NULL – kolumna nie może przyjmować wartości pustych.
  • CHECK – sprawdza warunek logiczny (np. wiek > 0).
Rodzaj klucza Opis Przykład
Klucz główny (Primary Key) jednoznacznie identyfikuje rekord NrIndeksu w STUDENT
Klucz obcy (Foreign Key) tworzy powiązanie między tabelami KursID w tabeli ZAPIS
Klucz kandydujący (Candidate Key) atrybut, który może być kluczem głównym PESEL, NrIndeksu
Klucz złożony (Composite Key) składa się z kilku kolumn (StudentID, KursID)

Schemat: klucz główny i klucz obcy między tabelami STUDENT, KURS, ZAPIS

Klucz złożony w praktyce

Klucz złożony (composite key) jest deklarowany jako PRIMARY KEY obejmujący kilka kolumn naraz — żadna z nich osobno nie musi być unikalna, unikalna musi być dopiero ich kombinacja.

CREATE TABLE Zapis (
    NrIndeksu   INTEGER,
    KursID      INTEGER,
    DataZapisu  DATE,
    PRIMARY KEY (NrIndeksu, KursID),
    FOREIGN KEY (NrIndeksu) REFERENCES Student(NrIndeksu),
    FOREIGN KEY (KursID) REFERENCES Kurs(KursID)
);

Identyfikator studenta może występować w wielu zapisach dotyczących różnych kursów. Identyfikator kursu również może się powtarzać. Unikalność pary (NrIndeksu, KursID) wyklucza natomiast drugi zapis tego samego studenta na ten sam kurs.

Działanie klauzuli ON DELETE

Klucz obcy określa odwołanie do tabeli nadrzędnej. Zachowanie ograniczenia podczas usuwania wiersza nadrzędnego, do którego odwołują się wiersze podrzędne, określa klauzula ON DELETE lub jej ustawienie domyślne:

Opcja Zachowanie przy DELETE rodzica
(brak, domyślnie) NO ACTION / RESTRICT Baza odrzuca usunięcie, dopóki istnieją powiązane wiersze podrzędne.
ON DELETE CASCADE Usunięcie rodzica automatycznie usuwa powiązane wiersze podrzędne.
ON DELETE SET NULL Powiązane wiersze podrzędne dostają NULL w kolumnie klucza obcego. 
FOREIGN KEY (autor_id) REFERENCES Autorzy(autor_id) ON DELETE CASCADE

W przykładzie tabeli Ksiazki nie zadeklarowano klauzuli ON DELETE. Przy włączonej kontroli kluczy obcych usunięcie autora z istniejącymi odwołaniami zostanie odrzucone.

Rodzaje związków (relacji) między tabelami

W relacyjnych bazach danych tabele łączy się przez klucze obce. Na tej podstawie tworzą się różne typy relacji:

Typ relacji Opis Przykład
1:1 (jeden do jednego) jeden rekord w tabeli A odpowiada dokładnie jednemu rekordowi w tabeli B Student ↔︎️ Legitymacja
1:N (jeden do wielu) jeden rekord w tabeli A może być powiązany z wieloma rekordami w tabeli B Wykładowca ↔︎️ Kursy
N:M (wielu do wielu) wiele rekordów w A może być powiązanych z wieloma w B Studenci ↔︎️ Kursy

Relację N:M realizuje się przez tabelę pośredniczącą, np.:

STUDENT (NrIndeksu, Imie, Nazwisko)
KURS (KursID, Nazwa)
ZAPIS (NrIndeksu, KursID, DataZapisu)

Tabela ZAPIS zawiera klucze obce wskazujące na STUDENT i KURS.

Schemat: STUDENT —< ZAPIS >— KURS (relacja N:M)

Wprowadzenie do języka SQL (Structured Query Language)

SQL (Structured Query Language) jest językiem definiowania struktur, modyfikowania danych i formułowania zapytań w relacyjnych bazach danych. Podstawowe grupy poleceń obejmują DDL, DML, DCL i TCL. Poniższe przykłady dotyczą przede wszystkim definiowania struktur i operacji na danych, czyli DDL i DML.

Tworzenie tabel (DDL – Data Definition Language)

Polecenie CREATE TABLE służy do utworzenia nowej tabeli w bazie danych. Definicja zawiera nazwy kolumn, ich typy danych oraz ograniczenia integralności, takie jak PRIMARY KEY, NOT NULL i FOREIGN KEY.

Każda tabela powinna mieć kolumnę, która jednoznacznie identyfikuje każdy wiersz – to właśnie klucz główny.

Przykład obejmuje dwie tabele, Autorzy i Ksiazki, połączone odwołaniem klucza obcego do klucza głównego.

Definicja schematu w SQLite i PostgreSQL

Implementacje SQL różnią się składnią niektórych definicji, między innymi mechanizmem automatycznego nadawania identyfikatorów. Poniżej przedstawiono dwa warianty tego samego schematu, przeznaczone odpowiednio dla SQLite i PostgreSQL. Każdy wariant stanowi odrębną definicję dla wskazanego silnika.

Wariant SQLite:

CREATE TABLE Autorzy (
    autor_id     INTEGER PRIMARY KEY,   -- patrz uwaga ponizej: to NIE to samo co AUTOINCREMENT
    imie         TEXT NOT NULL,
    nazwisko     TEXT NOT NULL
);

CREATE TABLE Ksiazki (
    ksiazka_id   INTEGER PRIMARY KEY,
    tytul        TEXT NOT NULL,
    rok          INTEGER CHECK (rok > 0),
    autor_id     INTEGER,
    FOREIGN KEY (autor_id) REFERENCES Autorzy(autor_id)
);

Wariant PostgreSQL:

CREATE TABLE Autorzy (
    autor_id     SERIAL PRIMARY KEY,
    imie         TEXT NOT NULL,
    nazwisko     TEXT NOT NULL
);

CREATE TABLE Ksiazki (
    ksiazka_id   SERIAL PRIMARY KEY,
    tytul        VARCHAR(200) NOT NULL,
    rok          INTEGER CHECK (rok > 0),
    autor_id     INTEGER,
    FOREIGN KEY (autor_id) REFERENCES Autorzy(autor_id)
);
ImportantINTEGER PRIMARY KEY a AUTOINCREMENT w SQLite

W SQLite INTEGER PRIMARY KEY jest aliasem wewnętrznego identyfikatora wiersza ROWID. Przy operacji INSERT bez jawnego identyfikatora baza nadaje jego wartość automatycznie. Usunięcie wiersza o największym identyfikatorze może jednak umożliwić ponowne użycie tej wartości przy kolejnym dodaniu danych.

Deklaracja INTEGER PRIMARY KEY AUTOINCREMENT ogranicza ponowne używanie wcześniej przydzielonych identyfikatorów przez mechanizm automatycznej numeracji. SQLite utrzymuje w tym celu dodatkową tabelę sqlite_sequence, co zwiększa koszt operacji zapisu. Wybór mechanizmu zależy od wymagań dotyczących identyfikatorów; w podstawowych przykładach wystarcza INTEGER PRIMARY KEY.

PostgreSQL nie ma tego rozróżnienia — SERIAL zawsze daje rosnącą sekwencję, która (poza rzadkimi przypadkami jawnego resetu) nie powtarza wartości.

W powyższym przykładzie:

  • tabela Autorzy przechowuje dane o autorach,
  • tabela Ksiazki przechowuje dane o książkach,
  • kolumna autor_id w Ksiazki to klucz obcy, który wskazuje autora.

Dzięki więzom integralności baza pilnuje, by każda książka miała poprawnego autora. Takie powiązania są podstawą modelu relacyjnego.

W definicji FOREIGN KEY (autor_id) REFERENCES Autorzy(autor_id) pominięto klauzulę ON DELETE. Zastosowanie ma więc zachowanie domyślne opisane w sekcji dotyczącej usuwania powiązanych danych.

Modyfikacja i usuwanie tabel

Dodawanie kolumn

ALTER TABLE Ksiazki ADD COLUMN gatunek TEXT;

Zmiana typu kolumny

ALTER TABLE STUDENT ALTER COLUMN Kierunek TYPE VARCHAR(100);

Usuwanie kolumny

ALTER TABLE STUDENT DROP COLUMN Email;

Usuwanie tabeli

DROP TABLE Ksiazki;

Wstawianie danych (DML – Data Manipulation Language)

Polecenie INSERT INTO służy do wprowadzania nowych wierszy (rekordów) do tabeli. Wartości muszą być zgodne z typami kolumn oraz ograniczeniami (NOT NULL, CHECK, itp.).

Można dodać jeden lub wiele rekordów w jednym poleceniu.

INSERT INTO Autorzy (imie, nazwisko) VALUES
('Adam', 'Mickiewicz'),
('Henryk', 'Sienkiewicz'),
('Bolesław', 'Prus');

INSERT INTO Ksiazki (tytul, rok, autor_id) VALUES
('Pan Tadeusz', 1834, 1),
('Quo Vadis', 1896, 2),
('Lalka', 1890, 3);

Każdy rekord reprezentuje jedną pozycję w tabeli. Polecenie INSERT wymaga podania wartości w tej samej kolejności, w jakiej wymieniono kolumny. Pominięcie kolumny z zadeklarowaną wartością domyślną (DEFAULT) powoduje zastosowanie tej wartości przez bazę.

Wybieranie danych – SELECT

Podstawowy SELECT

SELECT * FROM Autorzy;

Powyższe zapytanie zwraca wszystkich autorów z tabeli Autorzy.

Wybór konkretnych kolumn


SELECT imie, nazwisko FROM Autorzy;

Nadawanie aliasów

SELECT imie AS "Imię", nazwisko AS "Nazwisko" FROM Autorzy;

Alias (AS) pozwala nadać kolumnom bardziej opisowe nazwy w wynikach.

Filtrowanie – WHERE

SELECT * FROM Ksiazki
WHERE rok > 1850;

Operatory logiczne:

  • = , <>, <, >, <=, >=
  • LIKE ‘Q%’ → zaczyna się na Q
  • IN (…)
  • BETWEEN 1800 AND 1900
  • IS NULL, IS NOT NULL

Przykład wyszukiwania książek zawierających literę “a” w tytule:

SELECT tytul FROM Ksiazki WHERE tytul LIKE '%a%';

Wartość NULL w warunkach filtrowania

NULL oznacza brak znanej wartości. Testowanie jej obecności wymaga operatora IS NULL, zamiast porównania przez =.

-- ŹLE — nigdy nie zwróci wierszy, nawet jeśli autor_id jest puste:
SELECT * FROM Ksiazki WHERE autor_id = NULL;

-- DOBRZE:
SELECT * FROM Ksiazki WHERE autor_id IS NULL;

Wyrażenie autor_id = NULL ma wynik UNKNOWN. Klauzula WHERE zachowuje wyłącznie wiersze, dla których warunek daje TRUE. Analogicznie porównanie autor_id <> NULL nie wybiera wierszy; obecność znanej wartości sprawdza operator IS NOT NULL.

Sortowanie – ORDER BY

SELECT * FROM Ksiazki ORDER BY rok DESC;

Sortowanie wyników można wykonać według dowolnej kolumny. Domyślnie ORDER BY sortuje rosnąco (ASC). Dla kilku kolumn można napisać np.:

SELECT * FROM Ksiazki ORDER BY autor_id ASC, rok DESC;

Grupowanie i agregacja – GROUP BY, HAVING

SELECT autor_id, COUNT(*) AS liczba_ksiazek
FROM Ksiazki
GROUP BY autor_id
HAVING COUNT(*) > 1;

Klauzula GROUP BY grupuje dane, a HAVING filtruje grupy po agregacji. Funkcje agregujące: COUNT(), SUM(), AVG(), MIN(), MAX().

Różnica między WHERE i HAVING

WHERE filtruje pojedyncze wiersze, zanim zostaną zgrupowane — w momencie jego działania COUNT(*) jeszcze nie istnieje. HAVING filtruje gotowe grupy, już po agregacji. Dlatego WHERE COUNT(*) > 1 jest błędem składniowym, a HAVING COUNT(*) > 1 — poprawne.

Logiczna kolejność przetwarzania zapytania różni się od kolejności zapisu klauzul:

FROM  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY

Lista wynikowa SELECT występuje na początku zapisu zapytania, lecz w modelu logicznego przetwarzania następuje po filtrowaniu i agregacji. Alias nadany w SELECT ... AS liczba_ksiazek może być użyty w ORDER BY. Dostępność aliasów w innych klauzulach zależy od dialektu SQL.

1️⃣ Łączenie tabel – JOIN

INNER JOIN

SELECT k.tytul, a.imie, a.nazwisko
FROM Ksiazki k
JOIN Autorzy a ON k.autor_id = a.autor_id;

LEFT JOIN

SELECT k.tytul, a.imie, a.nazwisko
FROM Ksiazki k
LEFT JOIN Autorzy a ON k.autor_id = a.autor_id;

INNER JOIN zwraca tylko dopasowane rekordy, LEFT JOIN – wszystkie rekordy z lewej tabeli (nawet bez dopasowania).

2️⃣ Aktualizacja i usuwanie danych

Aktualizacja danych

UPDATE Ksiazki SET rok = 1836 WHERE tytul = 'Pan Tadeusz';

Usuwanie danych

DELETE FROM Ksiazki WHERE rok < 1850;

Zawsze należy używać WHERE, aby nie zmodyfikować lub nie usunąć wszystkich rekordów.

Podsumowanie

SQL jest językiem deklaratywnym: zapytanie określa oczekiwany wynik, a silnik bazy dobiera sposób jego uzyskania. Dzięki temu użytkownik nie musi znać szczegółów działania bazy danych.

Podstawowe polecenia i klauzule:

CREATE, INSERT, SELECT, UPDATE, DELETE, JOIN, GROUP BY

to fundament pracy z bazami danych. Opanowanie ich pozwala budować i analizować nawet bardzo złożone systemy informacyjne.

Źródła i literatura:

•   Dokumentacja SQLite
•   Dokumentacja PostgreSQL
•   A. Silberschatz, H. Korth, S. Sudarshan, Database System Concepts