Test 2021 - 1

-- a) Najděte unikátní příjmení čtenářů starších 30 let, kteří si nikdy nepůjčili žádnou knihu patřící do žánru detektivka nebo kuchařka.
SELECT DISTINCT c.prijmeni FROM Ctenar c
	JOIN Vypujcka v ON c.rodne_cislo = v.rodne_cislo
	JOIN Kniha k ON v.kniha = k.signature
WHERE   k.zanr NOT IN ('detektivka', 'kuchařka')
	AND c.vek > 30

-- b) Najděte rodná čísla, jména a příjmení čtenářů, kteří alespoň jednou měli ve stejný okamžik vypůjčeny alespoň dvě knihy najednou.

SELECT DISTINCT c.rodne_cislo, c.jmeno, c.prijmeni
FROM Ctenar celkový
	JOIN Vypujcka v1 ON c.rodne_cislo = v1.rodne_cislo
	JOIN Vypujcka v2 ON c.rodne_cislo = v2.rodne_cislo
WHERE v1.datum_pujceni <= v2.datum_pujceni
	AND v1.datum_vraceni >= v2.datum_pujceni
	AND v1.id <> v2.id
	
-- c) Pro každé město zjistěte celkový počet realizovaných výpůjček libovolných knih od autora Karel Čapek. Omezte se však jen na výpůjčky uskutečněné čtenáři s křestním jménem jiným než Karel. Ve výsledku uveďte jen ta města, kde takový počet je roven alespoň 10.

SELECT c.mesto, COUNT(v.id) AS pocet_vypujcek
FROM Vypujcka v
	JOIN Ctenar c ON c.rodne_cislo = v.rodne_cislo
	JOIN Kniha k ON k.signatura = v.kniha
WHERE 	c.jmeno <> 'Karel'
AND 	k.autor = 'Karel Čapek'
HAVING pocet_vypujcek > 9

/* d) Předpokládejme relační schéma z předcházejícího příkladu. Vytvořte výraz pro SQL/XML dotaz, kde pro každou realizovanou výpůjčku (na samostatném řádku výsledné tabulky) uvedete v prvním sloupci její id a ve druhém zkonstruujte XML hodnotu podle následujícího vzoru:
<vypujcka pujceno="datum půjčení" vraceno="datum vrácení">
	<kniha>název knihy</kniha>
	<autor>autor knihy</autor>
</vypujcka>*/
SELECT v.id, 
	XMLFOREST(
		v.datum_pujceni AS "@pujceno",
		v.datum_vraceni AS "@vraceno",
		k.nazev AS "kniha",
		k.autor AS "autor"
	) AS "vypujcka"

Test Řimnáč

-- (4) Vytvořte SQL dotaz, který u každého měření uvede SPZ, naměřenou rychlost, a odchylku rychlosti od průměrné rychlosti v dané oblasti

SELECT m.spz, m.rychlost, m.rychlost - prumery.prumer AS odchylka
FROM Mereni m
	JOIN (
		SELECT AVG(rychlost) AS prumer, id_senzor
		FROM Mereni
		GROUP BY id_Senzor
	) AS prumery ON m.id_senzor = prumery.id_senzor

-- (5) Vytvořte SQL dotaz, který u každé obce vypíše počet místně zaevidovaných řidičů, kteří za duben 2022 na libovolném senzoru nepřekročili nejvyšší povolenou rychlost. Vypsat jen obce, kde počet řidičů je větší než 100
SELECT majitele.mesto, COUNT(majitele.majitel)
FROM (
	SELECT DISTINCT v.majitel AS majitel, s.mesto AS mesto
	FROM Mereni m
	JOIN Vozidlo v ON v.SPZ = m.SPZ
	JOIN Senzor s ON s.id_senzor = m.id_senzor
	JOIN Kamera k ON k.id_senzor = m.id_senzor

	WHERE 	m.rychlost <= k.max_rychlost
		AND m.cas BETWEEN '2022-04-01' AND '2022-04-30'
	) AS majitele GROUP BY majitele.mesto

HAVING COUNT(majitele.majitel) > 100

-- (6) Vytvořte adekvátní dotaz, který hodnotu primárního klíče identifikovaného měření rychlosti vyhodnotí,
--zda došlo ke spáchání přestupku či ne (pokud ne, dotaz nic nevrátí).
--Ke stanovení výše pokuty použijte funkci vyse_pokuty_rychlost() se dvěma parametry - naměřená rychlost, a nejvyšší povolená rychlost

SELECT id_mereni, vyse_pokuty_rychlost(m.rychlost, k.max_rychlost)
FROM Mereni m
JOIN Kamera k ON m.id_senzor = k.id_senzor
WHERE 	m.rychlost IS NOT NULL
AND 	m.rychlost > k.max_rychlost

-- (7)
-- Za pomocí triggeru a user funkce, po přidání měření by se zkontrolovalo, zda nebyla překročena rychlost (s nějakou tolerancí), poté se AFTER INSERT přidá NEW záznam do tabulky Prestupek.

Teorie

Armstrongovy axiomy

Vyvozují všechny funkční závislosti na relační databázi.

Primární

Reflexivita $Y \subseteq X \Rightarrow X \rightarrow Y$

Tranzitivita $X \rightarrow Y, Y \rightarrow Z \Rightarrow X \rightarrow Z$

Rozšíření $X \rightarrow Y \Rightarrow XZ \rightarrow YZ \forall Z$
Sekundární

Dekompozice $X \rightarrow YZ \Rightarrow X \rightarrow Y$ a $X \rightarrow Z$

Kompozice $X \rightarrow Y, Z \rightarrow W \Rightarrow XZ \rightarrow YW$

Union $X \rightarrow Y, X \rightarrow Z \Rightarrow X \rightarrow YZ$

Pseudotransitivita $X \rightarrow Y, WY \rightarrow Z \Rightarrow WX \rightarrow Z$

Sebeurčení $I \rightarrow I$

Extensita $X \rightarrow Y \Rightarrow X \rightarrow XY$

Transakce

Sekvence operací považována jako celek, vytvořená pro účel zajistit správnost vytvoření / smazání / editace dat (během transakce může dojít k selhání). Transakce přistupuje ke kopii dat, změny se potvrdí / zahodí při COMMIT / ROLLBACK.

Transakce splňuje

Atomicity - buď všechny změny, nebo žádná
Consistency - před a po transakci jsou data v konzistentním stavu
Isolation - změny transakce nejsou viditelné jiným transakcím
Durability - změny jsou trvalé

Coffmanovy podmínky

Při splnění všech podmínek najednou dojde k deadlocku.

Vzájemné vyloučení (mutual exclusion) - každý prostředek může v danou chvíli používat pouze jeden proces
Drž a čekej (hold and wait) - proces může zažádat o další prostředky, i když už nějaké drží
Neodnímatelnost (no preemption) - jen proces samotný se prostředku může vzdát
Cyklické čekání (circular wait) - procesy čekají na sebe navzájem v kruhu

Persistentní kontext

Funguje jako taková "cache" pro data mezi aplikací a databází. Všechny operace jsou prováděné v paměti a až po commitu se změny propíšou do databáze.

Operace: persist (insert), merge (update), remove (delete), find (select), detach (odstranění z lokálního kontextu), refresh, flush (pull změn z databáze), commit, rollback

ORM

Slouží jako middleware mezi databází a aplikací v objektově orientovaném jazyku. Umožňuje mapování objektů na tabulky a zjednodušuje tak práci s databází.

OLAP vs. OLTP

OLAP
- Online Analytical Processing
- hlavní účel je analýza dat
OLTP
- Online Transaction Processing
- hlavní účel je bezchybné a efektivní zpracování transakc

Data: OLTP systémy pracují s aktuálními daty a vyžadují časté aktualizace, zatímco OLAP systémy pracují s historickými daty a jsou aktualizovány pravidelně, ale ne neustále.

Dotazy: OLTP dotazy jsou jednoduché a často modifikují data, zatímco OLAP dotazy jsou složité a slouží k čtení a analýze dat.

Uživatelé: OLTP systémy jsou typicky používány velkým počtem uživatelů pro každodenní operace, zatímco OLAP systémy jsou používány méně uživateli, ale těmi, kteří provádějí hloubkové analýzy.

NoSQL

Typ databáze, který nepracuje s relačními daty. Není potřeba schema a data jsou uložena v nestrukturované podobě, to umožňuje rychlejší zpracování dat a snadnější škálování.

Například MongoDB, Cassandra, Redis, CouchDB.