N-ticový (řádkový) relační kalkul

1. N-ticový (řádkový) relační kalkul Hodnoty proměnných n-tice relací (řádky) Konstanty hodnoty atributů Unární funkční symboly atributová jména Predikátové symboly jména relací Termy konstanty, proměnné, indexované n-tice x[A] Atomické formule P(x) – rozsahové formule, dále a  b, kde a, b jsou indexované n-tice Formule jako výše, ale s využitím omezených kvantifikátorů Databázové systémy UIN010

2. Tvar zápisu v NRK • {(t1,…tk): P1(r1) & … Pn(rn) & V(r1,…rn)}, kde • k n 1 • Pi(ri) jsou rozsahové formule pro právě všechny volné proměnné formule V • V neobsahuje žádné rozsahové formule • Tj jsou proměnné nebo indexové proměnné pokrývající všechny volné proměnné formule V Databázové systémy UIN010

3. Příklady v NRK Příjmení všech učitelů {(t[PŘ]): U(t)} Učitelé katedry algebry {(t[KU], t[PŘ], t[KA]): U(t) & t[KA] = “Algebra”} Příjmení učitelů katedry algebry {(t[PŘ]): U(t) & t[KA] = “Algebra”} Názvy vyučovaných předmětů {(t[NP]): P(t) & R(u)(t[KP] = u[KP])} Učitelé, kteří nic neučí {(t[KU],t[PŘ],t[KA]) : U(t)&Ø$R(u)(t[KU]=u[KU])} Databázové systémy UIN010

4. Příklady v NRK Předměty vyučované KSI i KSVI {(t[KP]) : R(t)&$R(u)(t[KP]=u[KP])&$U(x)(t[KU]=x[KU]&x[KA]='KSI')&$U(y)(u[KU]=y[KU]&y[KA]='KSVI')} Kódy učitelů, kteří učí každý den rozvrhu {(t[KU]) : R(t)& "R(u)$R(v) (v[DT]=u[DT]& t[KU]=v[KU])} Kódy učitelů, kteří učí každý den, kdy učí Novák (kód 358) {(t[KU]) : R(t)& "R(u)$R(v) (u[KU]=358 Þ (v[DT]=u[DT]& t[KU]=v[KU])} Zaměstnanci s platem vyšším než plat jejich vedoucího {(t[ČZ]) : ZAM(t)&$ZAM(u)(t[ČV]=u[ČZ]&t[Plat]>u[Plat])} Databázové systémy UIN010

5. Doménový relační kalkul Rozdíly oproti NRK: • proměnné nemají za hodnoty n-tice, ale jednotlivé prvky z domén, tj. jednoduché hodnoty atributů • nepoužívají se rozsahové formule, ale tzv. modelové predikáty, kde predikátový symbol odpovídá vždy nějaké relaci/tabulce a umístěním proměnné v příslušné pozici „deklarujeme“ její typ Databázové systémy UIN010

6. Příklady v DRK Učitelé katedry algebry {(r,s): $t U(KU:r,PŘ:s,KA:t)& t='Algebra'} Příjmení učitelů katedry algebry {(s): U(PŘ:s,KA:'Algebra') } Názvy vyučovaných předmětů {(n): $k(P(KP:k,NP:n)&R(KP:k))} Zaměstnanci s platem vyšším než plat jejich vedoucího {(z): $p $č (ZAM(ČZ:z,Plat:p,ČV:č) &$q(ZAM(ČZ:č,Plat:q) & p>q))} Učitelé, kteří nic neučí {(r,s,t) : U(KU:r,PŘ:s,KA:t)&ØR(KU:r)} Databázové systémy UIN010

7. Příklady v DRK Předměty vyučované KSI i KSVI {(k): $u(R(KP:k,KU:u)&U(KU:u,KA:'KSI'))& $v(R(KP:k,KU:v)&U(KU:v,KA:'KSVI')) } Kódy učitelů, kteří učí každý den rozvrhu {(k):"d (R(DT:d) Þ R(KU:k,DT:d))} Kódy učitelů, kteří učí každý den, kdy učí Novák (kód 358) {(k): "d (R(KU:358, DT:d) Þ R(KU:k,DT:d))} Databázové systémy UIN010

8. Připomenutí Systém řízení bází dat (SŘBD) - umožňuje definovat a udržovat data v databázi • jazyk pro definici dat (JDD) • běžné typy dat (viz. programovací jazyku) + možnost vytváření složitějších struktur • využívá typy dat vycházející z databázového modelu • jazyk pro manipulaci dat (JMD) • konstrukty pro vkládání, odstraňování a modifikaci dat • prostředky pro tvorbu dotazů (obvykle označovány jako dotazovací jazyky) DBS = databáze + SŘBD Databázové systémy UIN010

9. Jazyk SQL • neprocedurální jazyk (popisuje co požadujeme a nikoli jak to získat) • je něčím více než pouhým dotazovacím jazykem Bude nás zajímat: • definice dat v SQL • manipulace dat v SQL • integritní omezení v SQL • pohledy • systémový katalog • přístupová práva • relační úplnost SQL Databázové systémy UIN010

10. Definice dat v SQL Deklarace relačního datového typu odpovídá vlastně popisu dvourozměrné tabulky s označenými sloupci. Definice schématu CREATE SCHEMA Příkazy pro práci se schématy CREATE TABLE ALTER TABLE DROP TABLE Databázové systémy UIN010

11. Definice dat v SQL CREATE TABLE • definujeme seznam jmen sloupců a odpovídajících typů dat CREATE TABLE Filmy (k_filmu CHAR(4) NOT NULL název_f CHAR(40) NOT NULL rok_v NUMBER NOT NULL režisér CHAR(20)); Po vyvolání tohoto příkazu se vytvoří prázdná tabulka Filmy a schéma se zaznamená do tzv. systémového katalogu. Databázové systémy UIN010

12. Typy dat v SQL (SQL92) • datové typy numerické, znakové řetězce, bitové řetězce, temporální data a časové intervaly Numerické typy INTEGER, SMALLINT, NUMERIC, DECIMAL FLOAT, REAL, DOUBLE PRECISION Znakové řetězce CHARACTER (CHAR), CHARACTER VARYING (VARCHAR) Temporální data DATE (rrrr-mm-dd) Integritní omezení NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK Databázové systémy UIN010

13. Definice dat v SQL ALTER TABLE • příkaz umožňující změnu již vytvořené tabulky ADD, DROP, ALTER ALTER TABLE Filmy ADD Herec CHAR(40) • DROP TABLE • příkaz pro odstranění tabulky z databáze • CASCADE, RESTRICT DROP TABLE Filmy CASCADE • CREATE SCHEMA • pro definici databázového schématu • je obvykle následován definicí tabulek, IO, práv přístupu atd. CREATE SCHEMA Půjčovna_filmů Databázové systémy UIN010

14. Indexy v SQL = struktury umožňující rychlejší přístup k řádkům tabulek při zpracování uživatelského požadavku • index lze definovat pro jeden či kombinaci sloupců • pro schéma jedné tabulky lze definovat libovolné množství indexů • pouze jeden je definován jako CLUSTER, podle něj jsou data uspořádána (i fyzicky na disku) CREATE [UNIQUE] INDEX jméno_indexu ON jméno_tabulky (jméno_atr1 [uspořádání][, jméno_atr2 [uspořádání]] …) [CLUSTER] CREATE INDEX Prvni ON Filmy (k_filmu) DESC CLUSTER DROP INDEX Prvni Databázové systémy UIN010

15. Příklad CREATE TABLE Rezervace (R_nazev_f VARCHAR(20) NOT NULL, R_rod_c NUMBER NOT NULL, R_datum_rez DATE NOT NULL, PRIMARY KEY (R_nazev_f, R_rod_c), FOREIGN KEY (R_nazev_f) REFERENCES Filmy, FOREIGN KEY (R_rod_c) REFERENCES Zakaznici); • Integritní omezení • NOT NULL – test na neprázdnost • UNIQUE – test na jedinečnost • CHECK – test (sloupce či tabulky) na zvolenou podmínku • DEFAULT – defaultní hodnota pro daný sloupec • PRIMARY KEY, FOREIGN KEY – definice prim. a ciz. klíčů Databázové systémy UIN010

16. Manipulace dat v SQL • příkazy pro výběr dat (SELECT) • příkazy aktualizační (INSERT, DELETE, UPDATE) • další speciální příkazy Příkaz SELECT • základním konstruktem pro výběr dat je SELECT-FROM-WHERE blok • klauzuleSELECT • obsahuje seznam sloupců vytvářejících schéma tabulky • klauzule FROM • obsahuje seznam tabulek, nad kterými je dotaz definován • klauzule WHERE • obsahuje podmínku, kterou musí splňovat vyhledávaná data • neeliminuje z výsledku duplicitní hodnoty! (ALL, DISTINCT) SELECT [{DISTINCT|ALL}][{*|jméno_atr1[,jméno_atr2]…}|] FROM jméno_rel1[, jméno_rel2] … [WHERE podmínka] [ORDER BY specifikace_řazení] Databázové systémy UIN010

17. Příklady Názvy kin SELECT nazev_k FROM Kina; Názvy kin bez duplicity SELECT DISTINCT nazev_k FROM Kina; Dvojice jaké filmy, které kino promítá? SELECT DISTINCT k_filmu, k_kina FROM Program ORDER BY k_filmu ASC; Všechny informace o promítáních po 5.4.2002 SELECT * FROM Program WHERE datum_prom > ‘5.4.2002’; Databázové systémy UIN010

18. Příklady Všechny informace o promítáních od 1.4.2002 do 5.4.2002 SELECT * FROM Program WHERE datum BETWEEN ‘1.4.2002’ AND ‘5.4.2002’; Názvy filmů, které jsou promítány SELECT DISTINCT nazev_f FROM Filmy, Program WHERE Filmy.k_filmu = Program.k_filmu; Dvojice filmů se stejným režisérem? SELECT X.k_filmu AS První, Y.k_filmu AS Druhý FROM Filmy X, Filmy Y WHERE X.reziser = Y.reziser AND X.k_filmu <> Y.k_filmu; Databázové systémy UIN010

19. Aritmetika • jednoduché výpočty na hodnotách vstupujících do výsledné tabulky Jaké budou ceny promítání při navýšení o 20%? SELECT k_kina, k_filmu, cena*1.2 FROM Promítání; Pro kino Blaník kódy filmů, které promítá a cenu promítání v eurech. SELECT k_filmu, cena/31.2 FROM Promítání X, Kina Y WHERE X.k_kina = Y.k_kina AND X.nazev_k = ‘Blaník’; Pozn. je-li jeden z operandů NULL, je výsledkem operace opět NULL Databázové systémy UIN010

20. Agregační funkce • funkce, které množině specifických prvků přiřadí reálné číslo (např. funkce COUNT umožňující získat počet prvků množiny) • argumentem agregační funkce je obecně relace, ve skutečnosti se však jedná pouze o množinu hodnot, tedy unární relaci COUNT, SUM, MAX, MIN, AVG Syntaxe aplikace agregační funkce na sloupec: agregační_funkce([{ALL|DISTINCT}] jméno_sloupce) • Pozn. SUM() = NULL • AVG() = NULL • COUNT() = NULL • Pozor! Použití agregační funkce za SELECT vylučuje použití dalšího sloupce. Databázové systémy UIN010

21. Příklady Počet kin SELECT COUNT (*) AS počet_kin FROM Kina; Kolik je promítáno filmů? SELECT COUNT (DISTINCT k_filmu) AS počet_promítaných_filmů FROM Promítání; Počet kin s kapacitou větší než 200 SELECT COUNT (*) AS počet_kin_s_kapacitou FROM Kina WHERE kapacita > 200; Jaká je průměrná cena za vstupenku na film Apollo 13? SELECT AVG(Cena) AS průměrná_cena FROM Promítání X, Filmy Y WHERE X.k_filmu = Y.k_filmu AND Y.nazev_f = ‘Apollo 13’; Databázové systémy UIN010

22. Konstrukt GROUP BY • použití agregačních funkcí lze rozšířit na možnost aplikace na podmnožiny tabulky zkonstruované dle výběrového kritéria • tabulka se konceptuálně rozdělí na skupiny, pro které je hodnota zvoleného sloupce konstantní • řádky obsahující v tomto sloupci hodnotu NULL se seskupí do jedné skupiny • získáváme tzv. seskupenou tabulku • každá skupina přispívá do výsledné tabulky jedním řádkem • jména sloupců, podle nichž se seskupení provádí jsou dána za vyhrazeným slovem GROUP BY SELECT … FROM … WHERE … GROUP BY jméno_atr1[{,[jméno_atr2]…}|] Databázové systémy UIN010

23. Příklady Kolik filmů natočili jednotliví režiséři? SELECT reziser, COUNT(k_filmu) FROM Filmy GROUP BY reziser; Kteří režiséři natočili alespoň tři filmy? SELECT reziser FROM Filmy GROUP BY reziser HAVING COUNT(k_filmu) > 2; Jaká je průměrná cena vstupenek u jednotlivých filmů? SELECT X.k_filmu, nazev_f, AVG(Cena) FROM Filmy X, Promítání Y WHERE X.k_filmu = Y.k_filmu GROUP BY k_filmu; Databázové systémy UIN010

24. Další predikáty SQL Predikát LIKE • umožňuje pracovat s podřetězci hodnot sloupců typu CHAR a VARCHAR jméno_sloupce LIKE znaková_konstanta • Hodnoty sloupce nazev_f libovolné délky začínající na PS • nazev_f LIKE ‘PS%’ • Dvoupísmenové názvy kin začínající písmenem A • nazev_k LIKE ‘K_’ • Minimálně třípísmenové názvy filmů s písmenem ‘a‘ na druhém místě • nazev_f LIKE ‘_a_%’ • Adresy kin obsahující slovo Praha • adresa LIKE ‘%Praha%’ Databázové systémy UIN010

25. Další predikáty v SQL Predikát IN • predikát pro práci s množinami jméno_sloupce [NOT] IN poddotaz nebo jméno_sloupce [NOT] IN (seznam hodnot) • Adresy kin, ve kterých dávají film s kódem 6524 • SELECT Adresa FROM Kina • WHERE k_kina IN (SELECT k_kina FROM Promítání WHERE k_filmu = ‘6524’); • Názvy filmů, které natočili Hřebejk, Zelenka nebo Svěrák • SELECT nazev_f FROM Filmy • WHERE reziser IN (’Hřebejk’,’Zelenka’,’Svěrák’); Databázové systémy UIN010

26. Další predikáty v SQL Pozn. Poddotaz můžeme užít i s porovnávacími operátory, máme-li zaručeno, že výsledkem poddotazu je právě jedna hodnota. Adresy kin, ve kterých dávají film Gladiátor natočený roku 2000 SELECT Adresa FROM Kina WHERE k_kina IN (SELECT k_kina FROM Promítání P WHERE P.k_filmu = (SELECT F.k_filmu FROM Filmy F WHERE F.nazev_f = „Gladiátor“ AND rok_v = 2000)); Pozn. jméno_atributu IN () vrací false jméno_atributu IN (tabulka obsahující prázdné řádky) vrací unknown Databázové systémy UIN010

27. Další predikáty v SQL Konstrukt > ALL ~ „větší něž všechny prvky ze specifikované množiny“ • k dispozici jsou i další porovnávací predikáty před ALL Jaké je nejdražší promítání dne 2.4.2002? SELECT * FROM Promítání WHERE Cena > All (SELECT Cena FROM Promítání WHERE datum = ‘2.4.2002’); Pozn. jméno_atributu  ALL () vrací TRUE jméno_atributu  ALL (tabulka obsahující prázdné řádky) vrací unknown Databázové systémy UIN010

28. Kvantifikace v SQL • SQL nemá univerzální kvantifikátor, obsahuje však konstrukt EXISTS, který simuluje existenční kvantifikátor! • EXISTS je test na neprázdnost množiny specifikované za EXISTS. [NOT] EXISTS poddotaz • Jména filmů, kteří se v některém kině promítají • SELECT nazev_f FROM Filmy F • WHERE EXISTS (SELECT * FROM Promítání • WHERE k_filmu = F.k_filmu); • Názvy kin, která nic nehrají • SELECT nazev_k FROM Kina K • WHERE NOT EXISTS (SELECT * FROM Promítání • WHERE k_kina = K.k_kina); Databázové systémy UIN010

29. Množinové operace UNION, INTERSECT, EXCEPT • tabulky musí být kompatibilní (stejný počet sloupců, stejný typy dat odpovídajících si sloupců) Výraz_dotazu UNION [ALL] výraz_dotazu [ORDER BY specifikace_třídění] - UNION eliminuje duplikáty z výsledku bez zadání DISTINCT • Filmy, které natočil režisér Hřebejk nebo jsou promítány 2.4.2002 • (SELECT k_filmu FROM Filmy • WHERE reziser = ‘Hřebejk’) • UNION • (SELECT k_filmu FROM Promítání • WHERE datum = ‘2.4.2002’); Databázové systémy UIN010

30. Použití prázdných hodnot Filmy, u nichž chybí záznam o režisérovi SELECT k_filmu FROM Filmy WHERE reziser IS NULL; Jaký zaměstnanec žijící v Praze má nejvyšší plat? (s pomocí ALL) SELECT k_zamestnance FROM Zamestnanci WHERE plat > ALL (SELECT Z.Plat FROM Zamestnanci Z WHERE Z.Adresa LIKE ‘%Praha%’); Jaký zaměstnanec žijící v Praze má nejvyšší plat? (s pomocí MAX) SELECT k_zamestnance FROM Zamestnanci WHERE plat > (SELECT MAX(Z.Plat) FROM Zamestnanci Z WHERE Z.Adresa LIKE ‘%Praha%’); Databázové systémy UIN010

31. Spojení tabulek • přirozené spojení SELECT * FROM R NATURAL JOIN S; • spojení křížem (kartézský součin) SELECT * FROM R CROSS JOIN S; • spojení přes podmínku SELECT * FROM R JOIN S ON A > B; • spojení přes vyjmenované sloupce SELECT * FROM R JOIN S USING (A, B); • vnitřní vs. vnější spojení SELECT * FROM R INNER(LEFT, RIGHT) JOIN S USING (A, B); • vnější spojení slouží k přidání některých řádků, které se s ničím nespojily, do výsledku Databázové systémy UIN010

32. Aktualizace v SQL INSERT, DELETE, UPDATE • INSERT INTO Filmy (k_filmu, nazev_f) • VALUES (‘B04’,’Jáchyme, hoď ho do stroje’); • CREATE TABLE Pocet_filmu • (reziser CHAR(20) • počet INT); • INSERT INTO TABLE Počet_filmu • SELECT reziser, COUNT(k_filmu) • FROM Filmy • GROUP BY reziser; • DELETE FROM Filmy WHERE reziser = ‘Hřebejk’; • UPDATE Filmy SET reziser ‘Zelenka’ • WHERE k_filmu = ‘B12’; Databázové systémy UIN010

33. Referenční integrita = logické vztahy mezi dvěma tabulkami – hlavní a vedlejší • sloupec ve vedlejší tabulce = cizí klíč – sloupec odkazující do hlavní tabulky INSERT • omezené vložení řádku do vedlejší tabulky DELETE, UPDATE • kaskádové odstranění řádků (ON DELETE CASCADE) • nahrazení cizího klíče prázdnou hodnotou (SET NULL) • nahrazení cizího klíče implicitní hodnotou (SET DEFAULT) • odstranění řádku z hlavní tabulky s upozorněním (NO ACTION) FOREIGN KEY (k_filmu) REFERENCES Filmy ON UPDATE CASCADE Databázové systémy UIN010

34. Pohledy = virtuální tabulky • slouží především pro dotazování – umožňuje značné zjednodušení zápisu dotazů (obdobně jako rozčlenění programu do procedur) CREATE VIEW Prazaci AS SELECT k_zamestnance, jmeno_z FROM Zamestnanci WHERE Adresa LIKE ‘%Praha%’; SELECT k_zamestnance FROM Prazaci WHERE Plat > 10000; DROP VIEW Prazaci; Databázové systémy UIN010

35. Systémový katalog • slouží k uchovávání informací o relačním schématu databáze, o indexech, o pohledech apod. • opět přístupný s pomocí SQL SYSTABLES(name, creator, colcount, …) SYSCOLUMNS(name, tbname, coltype, …) SYSINDEXES(name, tbanem, creator, …) Které tabulky obsahujíc sloupec k_filmu? SELECT tbname FROM SYSCOLUMNS WHERE name = ‘k_filmu’; Jaké sloupce má tabulka Kina? SELECT name FROM SYSCOLUMNS WHERE tbname = ‘Kina’; Databázové systémy UIN010

36. Přístupová práva Ochrana dat pro • výběr dat (SELECT) • modifikaci (INSERT, DELETE, UPDATE) • odkazování (REFERENCES) GRANT {ALL PRIVILEGES | privilegium1[, privilegium2, …]} ON objekt TO {PUBLIC identifikátor1[,identifikátor2, …]} [WITH GRANT OPTION] Privilegia SELECT | DELETE | INSERT | UPDATE |REFEREBCES [(jméno_atr1 [, jméno_atr2]…)] • klauzule WITH GRANT OPTION umožňuje udílet privilegia uživatelům od těch, kteří jsou uvedeni za TO • při definici práv je možné využívat také pohledy – přidělovat práva až na úrovni prvků tabulky Databázové systémy UIN010

37. Příklady Odebrání práv • pomocí REVOKE a zadání požadavků podobně jako u GRANT REVOKE SELECT ON Filmy TO Jana; GRANT UPDATE (adresa) GRANT SELECT ON Filmy ON Filmy TO Lenka; TO PUBLIC; GRANT ALL PRIVILEGES ON Prazaci TO Jana; Databázové systémy UIN010