Relační databáze

Relační schéma

Relační model databáze je založen na konceptu relací, které představují tabulky s daty. Relační schéma je plán, který definuje strukturu tabulek – jaké atributy obsahují, jaké mají datové typy a jak jsou mezi sebou propojené.

Klíče

Vztahy mezi entitami

Při modelování vztahů mezi tabulkami řešíme dvě otázky: kolik entit se účastní vztahu (kardinalita) a zda musí se entita vztahu účastnit (parcialita).

Kardinalita

Kardinalita určuje maximální počet entit na obou stranách vztahu:

Parcialita

Parcialita (participační omezení) určuje, zda je účast entity ve vztahu povinná nebo volitelná:

• Povinná účast (||): Entita se musí vztahu účastnit. Každý záznam v tabulce A musí mít odpovídající záznam v tabulce B. V SQL: sloupec cizího klíče je NOT NULL.
• Volitelná účast (|o): Entita se vztahu účastnit může, ale nemusí. Sloupec cizího klíče může být NULL.

erDiagram
    ZAMESTNANEC ||--o| PROJEKT : "má"

Integritní omezení

Integritní omezení (IO) jsou pravidla, která zajišťují správnost, přesnost a konzistenci dat. Bez nich by databáze mohla obsahovat nesmyslná data – záporný věk, neexistující cizí klíče, nebo prázdné primární klíče.

Doménová integrita

Každá hodnota v daném sloupci spadá do definované domény – tedy má správný datový typ, spadá do povoleného rozsahu a splňuje případná další omezení.

CREATE TABLE Zamestnanci (
    id INT PRIMARY KEY,
    jmeno VARCHAR(100) NOT NULL,
    vek INT CHECK (vek >= 18 AND vek <= 65),
    email VARCHAR(100) UNIQUE NOT NULL,
    plat DECIMAL(10,2) DEFAULT 0
);

Entitní integrita

Každá tabulka musí mít primární klíč, který je jednoznačný a neprázdný (NOT NULL). Důsledek: v tabulce nemohou existovat dva řádky se stejným primárním klíčem a žádný řádek nesmí mít primární klíč NULL.

CREATE TABLE Studenti (
    student_id INT PRIMARY KEY,   -- entitní integrita: unikátní, NOT NULL
    jmeno VARCHAR(100) NOT NULL
);

Referenční integrita

Cizí klíče musí odkazovat na existující hodnoty primárního klíče v referenční tabulce. Zajišťuje, že vztahy mezi tabulkami zůstávají konzistentní.

CREATE TABLE Prihlasky (
    prihlaska_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    kurz_id INT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES Studenti(student_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (kurz_id) REFERENCES Kurzy(kurz_id)
);

Rekurzivní a ternární vztahy

• Rekurzivní vztah – relace, ve které cizí klíč odkazuje na primární klíč té samé tabulky. Např. Zamestnanec(nadrizeny_id → Zamestnanec(id)).
• Ternární vztah – vztah mezi třemi entitami. Např. Lékař–Pacient–Lék (předepisuje).

Návrh relační databáze

Návrh relační databáze je proces, ve kterém plánujeme, jakým způsobem budou data uložena, organizována a propojena. Správný návrh je klíčový pro efektivní a spolehlivé ukládání a manipulaci s daty.

Konceptuální schéma

Konceptuální schéma je model, který zobrazuje strukturu dat, jejich vlastnosti a vztahy – bez ohledu na fyzickou implementaci. Vyjadřuje co chceme ukládat, ne jak. Nejčastěji se kreslí pomocí ER diagramů (Entity-Relationship).

erDiagram
    CUSTOMER }|..|{ DELIVERY-ADDRESS : has
    CUSTOMER ||--o{ ORDER : places
    CUSTOMER ||--o{ INVOICE : "liable for"
    DELIVERY-ADDRESS ||--o{ ORDER : receives
    INVOICE ||--|{ ORDER : covers
    ORDER ||--|{ ORDER-ITEM : includes
    PRODUCT-CATEGORY ||--|{ PRODUCT : contains
    PRODUCT ||--o{ ORDER-ITEM : "ordered in"

Od konceptuálního k relačnímu schématu

Proces transformace konceptuálního modelu na relační schéma se řídí těmito kroky:

1. Přizpůsobit konceptuální schéma požadavkům relačního modelu.
2. Transformovat entity na tabulky, atributy na sloupce a vztahy na cizí klíče (případně vazební tabulky).
3. Normalizovat relační schéma (odstranit redundanci).
4. Validovat schéma vůči požadovaným transakcím.
5. Konzultovat, zda návrh odpovídá zadání.

Bottom-up přístup

Cílem bottom-up přístupu je seskupování atributů do relací tak, aby byly informace uloženy na „nejlepším možném místě". Vycházíme od jednotlivých atributů a postupně je sdružujeme podle logické sounáležitosti a funkčních závislostí.

Aktualizační anomálie

Aktualizační anomálie jsou nežádoucí vedlejší efekty operací nad databází, které vedou k nekonzistenci nebo ztrátě dat. Vznikají v důsledku redundance – opakování stejných dat na více místech.

Normální formy

Normalizace je systematický proces dekompozice relačního schématu tak, aby se eliminovala redundance a s ní spojené anomálie. Postupuje se po normálních formách – každá vyšší forma splňuje všechny požadavky forem nižších.

První normální forma (1NF)

Druhá normální forma (2NF)

Třetí normální forma (3NF)

Boyce-Coddova normální forma (BCNF)

BCNF je striktnější než 3NF – řeší anomálie, které 3NF ponechává v případech, kdy tabulka obsahuje více překrývajících se kandidátních klíčů. V praxi je většina 3NF schémat i BCNF.

Bezztrátová dekompozice

Dekompozice je proces rozdělení jedné relace na dvě nebo více menších relací tak, aby výsledné schéma eliminovalo redundanci a anomálie. Klíčová otázka zní: ztratíme při rozdělení nějaká data? Pokud ano, je to ztrátová dekompozice – a to je nepřípustné.

Indexy

Index je datová struktura, která zrychluje vyhledávání záznamů v tabulce. Místo sekvenčního procházení všech řádků se dotaz podívá do indexu, který funguje jako rejstřík – rychle najde ukazatel na data a záznam vrátí.

-- Vytvoření indexu
CREATE INDEX idx_prijmeni ON Studenti(prijmeni);

-- Unikátní index
CREATE UNIQUE INDEX idx_email ON Studenti(email);

-- Složený index
CREATE INDEX idx_jmeno_prijmeni ON Studenti(jmeno, prijmeni);

-- Smazání indexu
DROP INDEX idx_prijmeni;

Typy indexů – přehled

Klastrovaný vs. neklastrovaný index

Toto je zásadní architektonické rozhodnutí – týká se fyzického uspořádání dat na disku:

Klastrovaný index (Clustered)

Data v tabulce jsou fyzicky seřazena podle hodnot indexovaného atributu. Listové uzly \(B^+\)-stromu obsahují přímo celé řádky tabulky.

• Výhody: Extrémně rychlé pro range queries (BETWEEN, ORDER BY), data čtena sekvenčně z disku, covering queries.
• Nevýhody: Pouze jeden na tabulku (data lze fyzicky seřadit jen jedním způsobem). Při zápisu se musí přesouvat data, nejen index – dražší INSERT/UPDATE. Vhodný pro sloupce, podle kterých se často řadí nebo se dotazuje na rozsahy.
• Případ použití: Primární klíč (většina DB ho automaticky dělá klastrovaným), datum vytvoření u logovacích tabulek.

Neklastrovaný index (Unclustered)

Index vytváří samostatnou strukturu, která obsahuje jen klíč a ukazatel na řádek v tabulce. Fyzické pořadí dat se nemění.

• Výhody: Lze jich mít více na jedné tabulce (typicky desítky). Menší režie při zápisu – přesouvá se jen struktura indexu, ne data. Vhodný pro časté vyhledávací podmínky.
• Nevýhody: Pomalejší pro range queries – ukazatele odkazují na data rozptýlená po disku. Dva kroky: index → ukazatel → data.
• Případ použití: Cizí klíče, často filtrované sloupce (email, stav objednávky), fulltextové vyhledávání.

Strategie indexování

Transakce

Transakce je posloupnost SQL příkazů, která převádí databázi z jednoho konzistentního stavu do druhého. Funguje jako atomická jednotka – buď se provede celá, nebo vůbec. Bez transakčního zpracování by souběžný přístup více uživatelů vedl k chaosu – jeden by četl data uprostřed změn druhého, převody peněz by mizely, rezervace by se duplikovaly.

ACID

ACID je akronym čtyř vlastností, které každá transakce garantuje. Databázový systém je vynucuje kombinací zámků, logování a správy verzí.

-- Atomický převod peněz: oba UPDATE, nebo nic
BEGIN TRANSACTION;
    UPDATE Ucty SET zustatek = zustatek - 1000 WHERE id = 1;
    UPDATE Ucty SET zustatek = zustatek + 1000 WHERE id = 2;
COMMIT;
-- Pokud cokoliv selže:
-- ROLLBACK;

Konflikty

Když běží více transakcí současně a nejsou dostatečně izolované, vznikají následující problémy. Každý z nich reprezentuje jiný typ nekonzistence:

Stupně izolace

Standard SQL definuje čtyři úrovně izolace, které představují kompromis mezi konzistencí a výkonem. Vyšší izolace = méně konfliktů, ale více zámků a nižší propustnost.

READ UNCOMMITTED

Nejslabší úroveň – transakce může číst data, která jiná transakce ještě nepotvrdila (dirty read). Prakticky žádná izolace.

• Jak to funguje: SELECT nebere žádné zámky, ignoruje zámky ostatních.
• Výhody: Maximální výkon, žádné blokování.
• Nevýhody: Prakticky nepoužitelné pro cokoliv, kde záleží na správnosti dat.
• Použití: Pouze pro přibližné agregace, kde na přesnosti nezáleží (např. odhad počtu návštěvníků za poslední minutu).

READ COMMITTED

Transakce vidí pouze data potvrzená (COMMIT) jinými transakcemi. Dirty read není možný. Většina databází (PostgreSQL, Oracle, SQL Server) používá tuto úroveň jako výchozí.

• Jak to funguje: Každý SELECT vidí snapshot dat platný v okamžiku spuštění dotazu (ne transakce!).
• Výhody: Slušný kompromis – žádné dirty ready, ale stále vysoká propustnost. V PostgreSQL se používá MVCC – writers neblokují readers.
• Nevýhody: Non-repeatable read a phantom read jsou možné – stejný SELECT uvnitř jedné transakce může vrátit různé výsledky.
• Použití: OLTP systémy s vysokou propustností (e-shopy, bankovní dotazy na zůstatek).

REPEATABLE READ

Transakce vidí stejná data po celou dobu svého trvání – pokud přečte řádek na začátku transakce, každé další čtení stejného řádku vrátí stejnou hodnotu. Dirty read ani non-repeatable read nejsou možné.

• Jak to funguje: Při prvním čtení řádku se na něj vezme zámek (nebo se v MVCC zaznamená verze snapshotu platná pro celou transakci).
• Výhody: Konzistentní pohled na data uvnitř transakce – vhodné pro operace, které vyžadují více čtení v konzistentním stavu.
• Nevýhody: Phantom read je stále možný – nové řádky splňující podmínku WHERE se mohou objevit. Více zámků → menší propustnost.
• V MySQL InnoDB: REPEATABLE READ je výchozí úroveň a díky gap lockům blokuje i phantom ready – chová se téměř jako SERIALIZABLE.

SERIALIZABLE

Nejpřísnější úroveň – transakce se chovají, jako by běžely sériově (jedna po druhé), i když ve skutečnosti běží souběžně. Žádné konflikty nejsou možné.

• Jak to funguje: DB používá kombinaci zámků, predikátních zámků (zamyká i rozsahy hodnot, ne jen existující řádky), nebo detekci sériových konfliktů a abort transakcí (SSI – Serializable Snapshot Isolation v PostgreSQL).
• Výhody: Úplná izolace – programátor nemusí řešit žádné souběhové jevy.
• Nevýhody: Nízká propustnost, časté aborty transakcí při konfliktu. Nejhůř škáluje.
• Použití: Finanční systémy, kde je správnost kritická (převody mezi bankami, účetní uzávěrky).

Jak se izolace implementuje – zámky vs. MVCC

Triggers a uložené procedury

Triggers

Trigger je procedura uložená na databázovém serveru, která se automaticky spouští při definované události na tabulce.

CREATE TRIGGER jmeno
BEFORE | AFTER | INSTEAD OF udalost ON tabulka
[REFERENCING referencni_promenne]
[FOR EACH ROW | FOR EACH STATEMENT]
WHEN (podminka)
akce

CREATE TRIGGER tr_cascade
AFTER DELETE ON Studenti
REFERENCING old_row AS o
FOR EACH ROW
    DELETE FROM Prihlasky WHERE Prihlasky.student_id = o.student_id;

Uložené procedury

Uložená procedura je blok kódu uložený a spouštěný přímo na databázovém serveru. Používá se pro zapouzdření často používaných příkazů, validaci dat a přesun části logiky do DB.

CREATE PROCEDURE povys_plat(IN zam_id INT, IN zvyseni DECIMAL)
BEGIN
    UPDATE Zamestnanci
    SET plat = plat + zvyseni
    WHERE id = zam_id;
END;

-- Volání
EXECUTE povys_plat(42, 5000);

Pohledy

Pohled (View) je virtuální relace – uložený SQL dotaz, ke kterému se přistupuje jako k tabulce. Data fyzicky neexistují na disku, vznikají dynamicky při dotazu.

CREATE VIEW aktivni_studenti AS
SELECT s.id, s.jmeno, s.prijmeni, p.nazev AS obor
FROM Studenti s
JOIN Prihlasky pr ON s.id = pr.student_id
JOIN Programy p ON pr.program_id = p.id
WHERE pr.stav = 'aktivni';

-- Dotaz do pohledu (stejně jako do tabulky)
SELECT * FROM aktivni_studenti WHERE obor = 'IT';

SQL – přehled

Structured Query Language je deklarativní jazyk pro přístup a řízení relačních databází. Dělí se na několik podsad:

CREATE TABLE Teachers
(
    id_teacher  INTEGER NOT NULL,
    id_office   INTEGER NOT NULL,
    email       NVARCHAR(30) NOT NULL UNIQUE,
    name        NVARCHAR(30) NOT NULL,
    surname     NVARCHAR(30) NOT NULL,
    title_before NVARCHAR(10) NULL,
    title_after  NVARCHAR(10) NULL,
    dob         DATETIME NOT NULL,
    type        INTEGER NOT NULL CHECK (type > 0 AND type < 6),
    PRIMARY KEY (id_teacher),
    CONSTRAINT fk_TeachOffice FOREIGN KEY (id_office)
        REFERENCES Offices(id_office),
    CONSTRAINT uc_NameSurnameDob UNIQUE (name, surname, dob)
);