🗄️ BI-DBS – Databázové systémy

📐 Relační datový model – základní pojmy

Relační datový model (RDM) je základ relačních databází. Pracuje s daty jako s matematickými množinami — relacemi. Klíčový rozdíl oproti tabulkám v tabulkovém procesoru: v relaci nezáleží na pořadí řádků a nesmí existovat duplicitní řádky.

• Atribut — sloupec tabulky; každý atribut má svou doménu (přípustné hodnoty, např. celá čísla, řetězce).
• N-tice — jeden řádek relace; prvek kartézského součinu domén všech atributů.
• Schéma relace R(A₁:D₁, A₂:D₂, …, Aₙ:Dₙ) — záhlaví tabulky (jméno + atributy s doménami).
• Schéma relační databáze — dvojice (R, I), kde R je množina relací a I je množina integritních omezení.
• 1NF (první normální forma) — atributy musí být atomické (žádné vícehodnotové atributy, žádné vnořené tabulky).

🔑 Klíče a integritní omezení v relačním modelu

Proč minimální? Musí platit, že neexistuje žádná vlastní podmnožina K' ⊂ K, která by také jednoznačně určovala n-tice. Bez požadavku minimality by byl klíčem i celý řádek.

• Primární klíč (PK) — jeden vybraný klíč, který identifikuje n-tice. Hodnoty nesmí být NULL.
• Alternativní klíč (UK, Unique Key) — ostatní klíče relace; v SQL vyjádřeno jako UNIQUE + NOT NULL.
• Cizí klíč (FK) — odkaz z jedné relace na klíč jiné relace (nebo téže). Zajišťuje referenční integritu: MA_NA_PROGRAMU[JmenoF] ⊆ FILM[JmenoF].

Příklad: KINO(NazevK, Adresa), FILM(JmenoF, Herec, Rok), MA_NA_PROGRAMU(NazevK, JmenoF, Datum) — primární klíče podtrženy, cizí klíče odkazují na KINO a FILM.

∑ Relační algebra – přehled operací

Relační algebra byla vzorem pro návrh příkazu SELECT v SQL. SQL SELECT je relačně úplný (umí vyjádřit jakýkoli výraz relační algebry) a navíc má větší vyjadřovací sílu (agregace, vnější spojení, řazení…).

Minimální množina operací RA: {×, selekce, projekce, přejmenování →, sjednocení ∪, rozdíl \}

∀ Kvantifikátory v relační algebře a relační dělení

Nejčastější „záludné" úlohy u státnic se týkají správné formulace dotazů se všeobecným kvantifikátorem (∀ – „pro každý/všechny").

Existenční kvantifikátor (∃x)(P(x)): Přímá selekce nebo polospojení — „existuje alespoň jedna n-tice, která splňuje P."

Všeobecný kvantifikátor (∀x)(P(x)): Přímo v RA neexistuje, ale platí: ∀x.P(x) ≡ ¬∃x.(¬P(x))

Postup pro dotazy se všeobecnou kvantifikací (vzorec):

1. Zkonstruuj universum (kartézský součin toho, co chceme porovnávat).
2. Zkonstruuj reálnou množinu (co skutečně existuje).
3. Odečti reálnou od universa → získáš „nerealizované" dvojice.
4. Projekcí získáš diskvalifikované prvky.
5. Odečti diskvalifikované od kandidátů → výsledek.

Příklady typů dotazů (na státnicích je třeba umět rozlišit!):

• Kina, která hrají alespoň jeden film s Brando → polospojení (∃).
• Kina, která nehrají žádný film s Brando → rozdíl všech kin minus kina hrající Brando.
• Kina, která hrají pouze filmy s Brando → složitější dotaz (nejde přes dělení).
• Kina, která hrají každý film s Brando → relační dělení (÷) nebo vzorec se všeobecným kvantifikátorem.

📝 Jazyk SQL – úvod a SELECT

Klíčové vlastnosti SQL:

• Klíčová slova a názvy objektů nejsou case sensitive; porovnání řetězců ANO (pokud není session nastavena jinak).
• Standardizace: 1986, 1992, 1999, 2003 … Poslední čistě relační standard byl 1992.
• Implementace mají odchylky — zejm. pro NULL, OUTER JOIN, datové typy.

Základní syntaxe SELECT:

SELECT [DISTINCT | ALL] specifikace_sloupců
FROM specifikace_zdroje
[WHERE podmínka]
[GROUP BY seznam_sloupců]
[HAVING podmínka_skupiny]
[ORDER BY specifikace_řazení];

Pořadí vyhodnocení klauzulí (velmi důležité!):

1. FROM — zdroj dat
2. WHERE — filtrování řádků
3. GROUP BY — seskupení
4. Agregační funkce (COUNT, SUM, AVG, MAX, MIN) dle skupin
5. HAVING — filtrování skupin
6. SELECT — výběr / výpočet sloupců
7. ORDER BY — řazení výsledku

Operátory v klauzuli WHERE: =, <>, !=, <, >, <=, >=, BETWEEN, IN, LIKE, EXISTS, IS NULL, IS NOT NULL, AND, OR, NOT, ANY, ALL, SOME, UNIQUE.

Zástupné znaky v LIKE: % = libovolná skupina znaků (i prázdná), _ = právě jeden znak. Escape: LIKE '%AAA\%BBB%' ESCAPE '\'

NULL hodnota: Má význam „neznámá", „neuvedená", „N/A". Není to nula ani prázdný řetězec! SQL používá tříhodnotovou logiku: TRUE, FALSE, NULL (UNKNOWN). Porovnání s NULL dává NULL, proto se používá IS NULL / IS NOT NULL místo = NULL.

🔗 SELECT – spojení tabulek, agregace, poddotazy

Typy spojení (JOIN):

• CROSS JOIN — kartézský součin (odpovídá R × S v RA).
• JOIN … ON (podmínka) — Θ-spojení (obecné).
• JOIN … USING (sloupce) — spojení přes rovnost zadaných sloupců.
• NATURAL JOIN — přirozené spojení (automaticky přes všechny shodně pojmenované sloupce — pozor na nečekané shody!).
• LEFT [OUTER] JOIN — levé vnější spojení: zachová všechny řádky z levé tabulky, chybějící hodnoty z pravé doplní NULL.
• RIGHT [OUTER] JOIN — analogicky pro pravou tabulku.
• FULL [OUTER] JOIN — zachová řádky z obou stran.

Agregační funkce: COUNT, SUM, AVG, MAX, MIN.

• COUNT(*) — počítá všechny řádky včetně NULL.
• COUNT(A) — ignoruje NULL hodnoty atributu A.
• Ostatní agregační funkce (SUM, AVG) ignorují NULL hodnoty.
• SUM(∅) = NULL (ne 0!) — proto pozor na COALESCE.
• COUNT(∅) = 0.

GROUP BY seskupuje řádky. V SELECT lze uvést pouze seskupovací atributy nebo agregační funkce. HAVING filtruje skupiny podle výsledků agregací.

Poddotazy (vnořené dotazy):

• Nevztažený (uncorrelated) — vnitřní dotaz lze vyhodnotit samostatně; vrací skalár, seznam hodnot nebo tabulku.
• Vztažný (correlated) — vnitřní dotaz se odvolává na vnější; vyhodnocuje se pro každý řádek vnějšího dotazu (dražší).
• Použití: v WHERE (= (SELECT …), IN (SELECT …), EXISTS (SELECT …)), v FROM (inline view), v SELECT (skalární poddotaz).

Kvantifikace v SQL:

• EXISTS (SELECT …) — existenční kvantifikátor: TRUE pokud poddotaz vrátí alespoň jeden řádek.
• NOT EXISTS (SELECT …) — negace existenčního kvantifikátoru → implementace všeobecného (∀).
• IN (…) ≡ = SOME; NOT IN (…) ≡ <> ALL.
• ANY ≡ SOME — alespoň pro jednu hodnotu.
• ALL — pro všechny hodnoty.

Příkaz WITH (Common Table Expression – CTE): Definice dočasných pohledů pro aktuální dotaz. Umožňuje přehlednou formulaci složitých dotazů a nahrazení relačního dělení. Klíčové pro dotazy se všeobecnou kvantifikací.

⚙️ Rozdělení SQL: DDL, DML, DCL, TCL

🏗️ DDL – Definice struktury databáze

CREATE TABLE:

CREATE TABLE tabulka (
  sloupec datovy_typ [io_sloupce ...],
  ...
  [io_tabulky ...]
);

Příklad:

CREATE TABLE VYPUJCKY (
  c_kopie  CHAR(3)        NOT NULL,
  c_zak    CHARACTER(6)   NOT NULL,
  cena     DECIMAL(5,2),
  rod_c    CHARACTER(10)  NOT NULL,
  datum_v  DATE
);

ALTER TABLE: Mění strukturu existující tabulky.

ALTER TABLE KINA ADD pocet_mist INTEGER;           -- přidání sloupce
ALTER TABLE KINA DROP COLUMN pocet_mist;           -- odebrání sloupce
ALTER TABLE t ADD CONSTRAINT io_name CHECK (...);  -- přidání IO
ALTER TABLE t DROP CONSTRAINT io_name;             -- odebrání IO

DROP TABLE:

DROP TABLE KINA CASCADE;  -- smaže tabulku a závislé objekty

Datové typy v SQL: numerické (INTEGER, DECIMAL, FLOAT…), textové (CHAR, VARCHAR, TEXT…), datum a čas (DATE, TIME, TIMESTAMP…). NULL je prvkem každého datového typu (pokud nespecifikujeme NOT NULL).

🛡️ Integritní omezení (IO) v SQL DDL

Druhy integritních omezení v SQL:

• NOT NULL — atribut nesmí být NULL (povinnost).
• DEFAULT hodnota — výchozí hodnota atributu.
• UNIQUE — hodnota atributu (nebo kombinace atributů) musí být jedinečná. V SQL může být NULL (PostgreSQL: jen jedna NULL; Oracle: více NULL).
• PRIMARY KEY — primární klíč: UNIQUE + NOT NULL. Tabulka může mít jen jeden.
• FOREIGN KEY … REFERENCES — cizí klíč: zajišťuje referenční integritu.
• CHECK (podmínka) — libovolná podmínka na hodnotu atributu nebo kombinace atributů.

IO lze definovat na úrovni sloupce (pro jednoduchá IO) nebo na úrovni tabulky (pro složená IO, např. složený primární klíč). Pojmenování IO (CONSTRAINT jméno) je doporučené.

CREATE TABLE PREDSTAVENI (
  NAZEV_K  CHAR VARYING(20)  NOT NULL,
  NAZEV_F  CHAR VARYING(20)  NOT NULL,
  DATUM    DATE               NOT NULL,
  CONSTRAINT PREDSTAVENI_PK
    PRIMARY KEY (NAZEV_K, NAZEV_F),
  CONSTRAINT PREDSTAVENI_KINA_FK
    FOREIGN KEY (NAZEV_K) REFERENCES KINA,
  CONSTRAINT PREDSTAVENI_FILMY_FK
    FOREIGN KEY (NAZEV_F) REFERENCES FILMY
);

Referenční integrita – kaskádní reakce (co se stane při DELETE/UPDATE rodičovského záznamu):

Odložitelné IO (DEFERRED): SQL umožňuje nastavit kontrolu IO jako odloženou až na konec transakce (DEFERRED). Užitečné např. při přehlašování na termín — lze dočasně porušit IO uvnitř transakce. Oracle umí také dočasně vypnout IO příkazem ALTER TABLE … DISABLE/ENABLE CONSTRAINT.

✏️ DML – Manipulace s daty

INSERT: Vložení řádku nebo řádků.

-- Vložení jednoho řádku (výčet sloupců je doporučen)
INSERT INTO Zakaznici (rod_c, jmeno)
  VALUES ('4804230160', 'Novák');

-- Vložení více řádků pomocí SELECT
INSERT INTO Kolik_kopii
  SELECT rod_c, COUNT(c_kopie) FROM Vypujcky
  GROUP BY rod_c;

UPDATE: Změna hodnot.

UPDATE Zakaznici SET jmeno = 'Gotzová'
WHERE rod_c = '4655292130';

-- UPDATE pomocí vnořeného dotazu
UPDATE Zakaznici Z
SET Pocet_pujcek = (SELECT COUNT(*) FROM Vypujcky V
                    WHERE V.rod_c = Z.rod_c);

DELETE: Smazání řádků.

DELETE FROM Filmy
WHERE jmeno_f = 'Puška';  -- POZOR na správnou podmínku!

MERGE (UPSERT): Kombinace INSERT a UPDATE — pokud záznam existuje, provede se UPDATE, jinak INSERT. PostgreSQL používá UPSERT místo MERGE.

🔐 DCL – Přístupová práva; TCL – Řízení transakcí

DCL — GRANT a REVOKE:

• Každý objekt má vlastníka (ten, kdo ho vytvořil).
• GRANT — přidělí práva; REVOKE — odebere práva.
• Přidělitelná práva (dle typu objektu): SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, INDEX, REFERENCES.
• Práva lze přidělovat uživatelům, rolím nebo PUBLIC (všem).

GRANT SELECT ON V_Filmy TO XNOVAKJ3;
GRANT ALL PRIVILEGES ON V_filmy TO PUBLIC;
REVOKE INSERT ON Filmy FROM XNOVAKJ3;

Schéma vs. uživatel: V Oracle jsou schema = uživatel. V PostgreSQL jsou to oddělené koncepty (schéma = namespace pro databázové objekty).

TCL — COMMIT, ROLLBACK, SAVEPOINT:

• COMMIT — potvrdí všechny změny provedené v aktuální transakci; změny jsou perzistentně uloženy a viditelné ostatním session.
• ROLLBACK — odvolá všechny změny provedené v aktuální transakci; databáze se vrátí do stavu před transakcí.
• SAVEPOINT jméno — definuje záchytný bod uvnitř transakce; ROLLBACK TO SAVEPOINT jméno odvolá jen část transakce.

AUTOCOMMIT: Pokud je ON, každý DML příkaz je automaticky potvrzen. Pokud je OFF, musí přijít explicitní COMMIT nebo ROLLBACK. Doporučení: nespoléhat na AUTOCOMMIT ON; v insert skriptech vždy používat explicitní COMMIT.

Pohledy (VIEW): Virtuální relace — v systémovém katalogu uložen SELECT příkaz. Pohled se chová jako tabulka pro dotazování. DML nad pohledy je možné jen pro „simple views" (bez JOIN, agregací, výrazů). Pohledy nepřinášejí výkonnostní zrychlení (k tomu slouží materializované pohledy – MATERIALIZED VIEWS).

📋 Shrnutí okruhu 1

• Relační databáze je sada relací (množin n-tic) + sada integritních omezení. Klíče (PK, UK) a cizí klíče (FK) jsou základní IO.
• Relační algebra je formální dotazovací jazyk (selekce, projekce, spojení, množinové operace, polospojení, antijoin, dělení). Je vzorem pro SQL SELECT.
• SQL se dělí na DDL, DML, DCL, TCL. Každá skupina má specifické příkazy a účel.
• Integritní omezení v SQL: NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY (REFERENCES), CHECK — lze definovat na úrovni sloupce nebo tabulky.
• Referenční integrita: cizí klíč odkazuje na primární klíč jiné tabulky; reakce na DELETE/UPDATE: NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT.

🔄 Co je transakce a proč je potřeba

Klasický příklad: převod peněz z účtu A na účet B. Transakce se skládá ze dvou UPDATE příkazů:

1. Odečti X z účtu A.
2. Přičti X na účet B.
3. COMMIT nebo ROLLBACK.

Pokud by po kroku 1 došlo k pádu systému bez transakčního zpracování, peníze by „zmizely". Transakce zajistí, že buď proběhnou oba kroky, nebo ani jeden.

Dva základní požadavky na DBMS:

• Ochrana dat — odolnost vůči haváriím serveru (chyba HW, SW, výpadek proudu).
• Korektní souběžný přístup — více uživatelů pracuje současně se stejnými daty bez konfliktů.

Moduly DB stroje zajišťující transakční zpracování:

• Concurrency control — řízení souběžného zpracování: každý uživatel vidí konzistentní stav DB bez ohledu na souběžné přístupy.
• Recovery — zotavení z chyb: stav DB nesmí být narušen při havárii.

⚗️ ACID vlastnosti transakce

📊 Stavový diagram transakce

Transakce prochází těmito stavy:

• Aktivní (Active) — transakce probíhá, provádějí se DML příkazy.
• Částečně potvrzená (Partially Committed) — provedena poslední operace transakce, změny jsou zatím jen v paměti (bufferu).
• Potvrzená (Committed) — COMMIT byl úspěšně proveden; změny jsou trvale uloženy na disk. Stav je nevratný.
• Chybná (Failed) — transakce narazila na chybu; v normálním průběhu nelze pokračovat.
• Zrušená (Aborted) — po provedení ROLLBACK; databáze vrácena do stavu před transakcí.

Hranice transakce:

• Konec: explicitní COMMIT nebo ROLLBACK; implicitní ukončení session (chování závisí na klientovi).
• Začátek: skončením předchozí transakce nebo vznikem session. Pozor na AUTOCOMMIT!

📓 Transakční žurnál (Log) a obnova po pádu

Žurnál obsahuje speciální záznamy pro COMMIT, ROLLBACK a checkpoint (synchronizační bod — SCN v Oracle).

Operace při obnově:

• UNDO — odvolání nepotvrzených změn (po pádu): obnoví old data z žurnálu.
• REDO — opakování potvrzených změn (po pádu): znovu aplikuje new data z žurnálu.

Postup obnovy po pádu systému:

1. Roll Forward (REDO) — přehraní žurnálu od posledního checkpointu; obnoví vyrovnávací paměť.
2. Roll Back (UNDO) — odvolání transakcí, které nebyly v době pádu dokončeny.

Třídy chyb:

• Globální — pád systému (výpadek proudu → ztráta bufferu), uváznutí, chyba komunikace, chyba média.
• Lokální — logická chyba v jedné transakci (porušení IO, dělení nulou) → ošetřuje se explicitním ROLLBACK.

⚡ Problémy souběžného zpracování

Při souběžném zpracování transakcí mohou nastat tyto anomálie:

🎚️ Stupně izolace transakcí

• SQL standard nařizuje implicitně stupeň SERIALIZABLE.
• V praxi se nejčastěji setkáte s READ COMMITTED (Oracle, PostgreSQL — nižší stupeň neumožňují).
• MySQL s InnoDB engine umí totéž co PostgreSQL/Oracle.

🔒 Uspořádatelnost rozvrhů a uzamykací protokoly

Uspořádatelnost (serializability): Paralelní rozvrh je uspořádatelný, pokud jeho výsledek je ekvivalentní výsledku nějakého sériového rozvrhu. Tato vlastnost zaručuje korektnost paralelního zpracování.

Konfliktní operace: Dvě operace na stejném objektu A jsou konfliktní, pokud jejich pořadí ovlivňuje výsledek. WRITE vs. READ nebo WRITE vs. WRITE jsou konfliktní; READ vs. READ jsou kompatibilní.

Precedenční graf: Orientovaný graf transakcí; hrana T_i → T_k pokud T_i má konfliktní operaci před T_k. Rozvrh je uspořádatelný, pokud precedenční graf neobsahuje cyklus.

Uváznutí (Deadlock): T1 čeká na zámek drženém T2, T2 čeká na zámek drženém T1 → kruhové čekání. Řešení: ROLLBACK jedné transakce (obvykle té „mladší" nebo té, která provedla méně změn). Detekce: graf závislostí nebo timeout.

Dobře formovaná transakce:

• Zamyká objekt, chce-li k němu přistupovat.
• Nezamyká objekt, který již zamkla.
• Neodemyká objekt, který nezamkla.
• Na konci transakce nezůstane žádný objekt zamčený.

📋 Shrnutí okruhu 2

• Transakce je logická jednotka práce (sekvence DML příkazů); buď proběhne celá, nebo vůbec.
• ACID: Atomicity (celá nebo nic), Consistency (zachování IO), Independence (izolace od ostatních), Durability (perzistentní po COMMIT).
• Transakční žurnál zajišťuje Atomicity (UNDO) a Durability (REDO). Obnova po pádu: Roll Forward + Roll Back.
• Stupně izolace (0–3): READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. V praxi nejčastěji READ COMMITTED.
• Dvoufázový uzamykací protokol (2PL): fáze uzamykání + fáze odemykání; zaručuje uspořádatelnost rozvrhů.