Obsah

1. Obsah • Relační algebra • Operace relační algebry • Rozšíření relační algebry • Hodnoty null • Úpravy relací • Stručný úvod do SQL • SQL a relace • Základní příkazy SQL • Hodnoty null a tříhodnotová logika v SQL • Příkazy SQL pro modifikaci obsahu databází Téma 10 – Relační model dat a jazyk SQL Relační model dat a jazyk SQL

2. K čemu relace? • Viděli jsme tabulky. Pročpotřebujeme něco jiného? • Je k tomu řada důvodů: • Potřeba rigorózníhomatematického modelu • Model umožní formalizacidatabázových operací • Přesný model je potřebný k tvorbě deklarativně formulovaných dotazů a k optimalizaci jejich provádění • Hlavní myšlenkou je popsat databázi jako souhrn logických predikátů nad konečnou množinou predikátových proměnných a definovat tak omezení na přípustné hodnoty a kombinace hodnot Relační model dat a jazyk SQL

3. Co to je relace? • Matematicky: Jsou dány množiny D1, D2, …, Dn, pak relací R rozumíme podmnožinu kartézského součinu D1xD2x … xDn. Relace tedy je množina n-tic (a1, a2, …, an), kde aiDi • Příklad: • klient_jmeno = {Novák, Mates, Braun, Novotný …} /* množna jmen klientů */ • klient_ulice = {Spálená, Hlavní, Horní, …} /* množina jmen ulic*/ • klient_mesto = {Praha, Brno, Nymburk, …} /* množina jmen měst */ • pak r = { (Novák, Spálená, Praha), (Mates, Horní, Brno), (Braun, Hlavní, Brno), (Novotný, Horní, Nymburk) } je relace, tj. podmnožina klient_jmenoxklient_ulicexklient_mesto • Vzhledem k tomu, že jde vždy o konečné množiny, lze je vyjádřit výčtem, tedy tabulkami Relační model dat a jazyk SQL

4. Relace je podmnožina kartézského součinu Bush Carter Clinton Jefferson Příjmení Kenedy Lincoln Obama Roosevelt Washington Bill John Barac Jimmy Jména George Franklin Thomas Vybraní američtí prezidenti Abraham Theodore • V množinách neexistuje duplicita • Velmi důležité pro databázové aplikace • Prvky množiny mohou být v jakémkoliv pořadí • neexistuje uspořádání Relační model dat a jazyk SQL

5. Typy atributů • Každý atribut v relaci má své jméno • Množina přípustných hodnot atributu je definiční doménou atributu • Hodnoty atributu jsou (téměř vždy) atomické, tj. dále nedělitelné • Např. hodnotou atributu „číslo_účtu“ smí být číslo jednoho účtu, nikoliv množina čísel účtů • Speciální hodnota null patří do každé domény • prázdná (nezadaná) hodnota • null značně komplikuje definici mnoha množinových operací, a proto zpočátku tuto hodnotu budeme ignorovat • důsledky uvedeme později Relační model dat a jazyk SQL

6. Relační schéma a instance atributy (tj. sloupce) klient_jmeno klient_ulice klient_mesto Novák Novotný Braun Mates Spálená Horní Hlavní Horní Praha Nymburk Brno Brno n-tice (řádky) klient • Relační schéma • A1, A2, …, Anjsou atributy • R = (A1, A2, …, An ) je relační schéma Příklad: Klient_schema = (klient_jmeno, klient_ulice, klient_mesto) • r(R) značí relacir nad relačním schématem R Příklad: klient (Klient_schema) • Instance relace (relační instance) • Skutečné hodnoty (relační instance) jsou definovány výčtem, tj. tabulkou • Prvek t relace r je n-tice, reprezentovaná řádkem tabulky Relační model dat a jazyk SQL

7. Klíče (znovu) • Nechť K R. K je superklíčschématu R, když hodnoty K stačí k jednoznačné identifikaci r(R) • Např. {klient_jmeno, klient_mesto} je superklíčem pro schéma Klient_schema. Superklíčem je však i {klient_jmeno} • K je kandidát na klíč jestliže K je minimální superklíč • Např. {klient_jmeno} je kandidátem na klíč pro schéma Klient_schema, neboť je to superklíč a žádná „podmnožina“ již superklíčem není • Primární klíčje vybrán mezi kandidátními klíči tak, aby se během „života“ příslušné relace neměnil • Např. {klient_jmeno} může sloužit jako primární klíč pro naši instanci relace, avšak když přijde další Novák, všechno bude špatně • e-mailová adresa může být primárním klíčem, avšak lidé svůj e-mail občas mění (což je jiný typ komplikace) Relační model dat a jazyk SQL

8. Cizí klíče customer depositor customer_name customer_name account_number customer_street customer_city branch account branch_name account_number branch_city assets branch_name balance loan borrower loan_number customer_name loan_number branch_name amount • Relační schéma může obsahovat atribut, který koresponduje s primárním klíčem v jiné relaci. Takový atribut se nazývá cizí klíč • Např. atributy customer_name a account_number relačního schématu depositor jsou cizí klíče do customer a account • Hodnotami cizího klíče v referencující (odkazující) relaci smí být jen ty hodnoty, které se vyskytují jako primární klíč v relaci referencované (odkazované) • Důležitý typ omezení – referenční integrita Relační model dat a jazyk SQL

9. Relační algebra • Relační algebra je vlastně procedurální jazyk • Šest základních operátorů • Selekce (restrikce)  • Výběr jen některých prvků relace • Projekce:  • Výběr jen určitých atributů • Sjednocení:  • Spojení několika relací v jednu (spojované relace musí mít stejné schéma) • Rozdíl (množin): – • Výběr těch prvků první relace, které nejsou obsaženy v druhé relaci • Kartézský součin: x • Klasická množinová operace • Přejmenování:  • Změna jména jednoho či více atributů • Všechny tyto operátory pracují s jednou nebo dvěma relacemi a vytváří relaci novou Relační model dat a jazyk SQL

10. Selekce A B C D         1 5 12 23 7 7 3 10 A B C D     1 23 7 10 r A=B  D > 5(r) • Zápis p(r) • p je selekční predikát • Definicep(r) = {t | t  rp(t)} Selekční predikát p je výroková formule složená z termů propojených logickými operátory:  (and),  (or),  (not)Každý term má tvar: atribut op atribut nebo konstanta, kde op je jeden z =, , >, , <,  • Příklad selekce: klient_mesto=“Praha” (klient) Relační model dat a jazyk SQL

11. Projekce A B C     10 20 30 40 1 1 1 2 A C A C     1 1 1 2    1 1 2 A,C (r) r = • Zápis:kde A1, A2 jsou jména atributů a r je jméno relace • Výsledek je definován jako relace s k atributy („sloupci“) vytvořená z relace r výběrem pouze vyjmenovaných atributů • Tedy vynecháním zbývajících (neuvedených) atributů • Duplicitní prvky (řádky) jsou odstraněny – relace jsou množiny! • Příklad: V relaci klient nás nezajímá atribut klient_uliceklient_jmeno, klient_mesto (klient) Relační model dat a jazyk SQL

12. Sjednocení A B   2 3 s A B     1 2 1 3 A B    1 2 1 Relace r, s: r s: r • Zápis: r s • Definice: r s = {t | t  r t  s} • Relacerasmusí být kompatibilní, tj 1. rasmusí mít stejnou aritu (počet atributů) 2. Domény atributů musí být po řadě shodné • Např. druhý atribut relace r a druhý atribut relace s musí mít shodný datový typ (definiční doménu) • Příklad: • najít všechny zákazníky banky, kteří mají vklad nebo půjčku customer_name (depositor)  customer_name (borrower) Relační model dat a jazyk SQL

13. Rozdíl A B A B    1 2 1   2 3 s r A B   1 1 • Zápis: r – s • Definice: r – s = {t | t r  t  s} • Relace vstupující do množinového rozdílu musí opět být vzájemně kompatibilní Relace r, s: r – s: Relační model dat a jazyk SQL

14. Kartézský součin A B C D E   1 2     10 10 20 10 a a b b r s A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b Relace r, s: rx s: • Zápis: r x s • Definice: r x s = {t q | t  r q  s} Předpokládejme, že atributy r(R) a s(S) jsou disjunktní tj., R  S = . • Lze použít i na více než dvě relace • Nejsou-li atributy disjunktní, tzn. některé atributy r(R) mají stejné jméno jako jména atributů v s(S), musí se použít operace přejmenování  • POZOR: Mohou vznikat tabulkygigantické velikosti Relační model dat a jazyk SQL

15. Operace přejmenování • Pomocná operace • Fakticky nejde o pravou operaci relační algebry, zavádí se z pragmatických důvodů • Umožňuje nově pojmenovat (a tím i referencovat) výsledek jiné relační operace • Umožňuje též pojmenovat relaci více jmény • Příklad: vrátí výsledek výrazu E pod jménem X • Jestliže relační výraz E má aritu n, pak vrátí výsledek výrazu E pod jménem X s atributy přejmenovanými na A1, A2, …., An. Relační model dat a jazyk SQL

16. Skládání operací A B C D E A B C D E         1 1 1 1 2 2 2 2         10 10 20 10 10 10 20 10 a a b b a a b b    1 2 2 a a b    10 10 20 A=C(r x s): rx s: • Skutečně užitečné relační operace vzniknou teprve skládáním operací základních Relační model dat a jazyk SQL

17. Příklad bankovní databáze • Relace • branch(branch_name, branch_city, assets) • customer(customer_name, customer_street,customer_city) • account(account_number, branch_name, balance) • loan(loan_number, branch_name, amount) • depositor(customer_name, account_number) • borrower(customer_name, loan_number) • Příklady dotazů • Najdi všechny půjčky (loan) přes 1200 • Najdi čísla půjček vyšších než 1200 • Najdi jména zákazníků majících vkladový účet v pobočce Nymburk Relační model dat a jazyk SQL

18. Příklad bankovní databáze (2) • Další příklady dotazů • Najdi jména zákazníků majících půjčku v pobočce ‘Nymburk’ a přitom nemají vkladový účet v žádné pobočce • Najdi jména zákazníků, kteří mají půjčku vedenou v pobočce Nymburk • 1. možnost • 2. možnost Relační model dat a jazyk SQL

19. Příklad bankovní databáze (3) • Příklady dotazu (použití operace přejmenování) • Najdi největší zůstatek vkladového účtu • Strategie: • Najdi zůstatky, které nejsou největší • K tomu účelu přejmenuj relaci account na temp, abychom mohli porovnávat jednotlivé zůstatky se všemi ostatními • Použij množinový rozdíl k nalezení těch zůstatků, které nejsou mezi těmi, které jsme určili v předchozím kroku • Dotaz pak vypadá takto: Пbalance(account) – Пaccount.balance (σaccount.balance < temp.balance(account xρtemp(account) ) ) Relační model dat a jazyk SQL

20. Doplňkové operace, průnik A B rs:  2 A B A B Relace r, s:    1 2 1   2 3 s r • Z praktických důvodů se definují další operátory, které umožňují zjednodušení častých dotazů do databáze • Průnik • Přirozené spojení (spojení přes rovnost) • Dělení • Přiřazení • Průnik • Zápis: r s • Definice: rs = { t | trts } • Předpoklad: Relace r a s jsou vzájemně kompatibilní • Poznámka: rs = r – (r – s) Relační model dat a jazyk SQL

21. Přirozené spojení • Zápis: r ⋈ s • Nechť r a s jsou relace podle schémat R a S. r⋈s je pak relace podle schématu R S vytvořená jako: • Uvažme všechny páry n-tic tr z r a ts z s • Jestliže tr a ts mají stejné hodnoty všech atributů z RS, pak n-tice t se objeví ve výsledku, přičemž t má stejné hodnoty atributů jako tr na r a t má stejné hodnoty atributů jako ts na s • Výsledek přirozeného spojení je tedy množina všech kombinací „řádků“ z R a S, které mají shodné hodnoty stejnojmenných atributů • Příklad: R = (A, B, C, D) S = (E, B, D) • Výsledné schéma = (A, B, C, D, E) • r⋈s pak je: Relační model dat a jazyk SQL

22. Přirozené spojení – příklad B D E A B C D 1 3 1 2 3 a a a b b           1 2 4 1 2      a a b a b r s A B C D E      1 1 1 1 2      a a a a b      • Relace r, s: r⋈s: • Praktický příklad Relační model dat a jazyk SQL

23. Operace dělení • Zápis: r  s • Určeno pro dotazy obsahující frázi „pro všechny“ • Nechť r a s jsou relace podle schémat R a S, kde R = (A1, …, Am , B1, …, Bn ) a S = (B1, …, Bn) • Výsledkem r  s je relace dle schématu R – S = (A1, …, Am) r  s = { t | t   R-S (r)   u  s (tu  r) }, kde tu značí zřetězení „řádků“ t a u chápané jako jediná n-tice • Vlastnost • Nechť q = r  s, pak q je největší relace splňující qxs r • Definice pomocí základních operací relační algebry • Nechť r(R) a s(S) jsou relace a nechť S  Rr  s = R-S (r) – R-S (( R-S(r) x s) – R-S,S(r)) • R-S,S (r) přeuspořádá atributy r • R-S (R-S(r) x s ) – R-S,S(r)) dá ty n-tice t z R-S(r), pro které platí, že některá n-tice u  s je taková, že tu  r Relační model dat a jazyk SQL

24. Operace dělení – příklad A B B         ε ε  1 2 3 1 1 1 3 4 6 1 2 1 2 s A r   • Relacer, s: rs: • Praktický příklad Relační model dat a jazyk SQL

25. Přiřazovací operace • Přiřazovací operace () umožňuje pohodlný zápis složitých výrazů • Dovoluje zapisovat „dotazy“ ve formě sekvence programových příkazů ve tvaru série přiřazení následovaných snáze čitelnými výrazy • Přiřazuje se vždy vhodné pracovní „proměnné typu relace“ • Pracovní proměnné jsou pak dostupné v dalších výrazech • Příklad: Operaci dělení r  s lze zapsat jako temp1 R-S (r) temp2 R-S ((temp1 x s) – R-S,S (r))vysledek = temp1 – temp2 Relační model dat a jazyk SQL

26. Příklad bankovní databáze – další dotazy • Najdi jména všech zákazníků, kteří mají současně vkladový účet a půjčku customer_name (borrower)  customer_name(depositor) • Najdi jména zákazníků, kteří mají půjčku, a výši této půjčky customer_name, loan_number, amount(borrower ⋈ loan) • Najdi jména všech zákazníků, kteří mají vkladový účet v pobočce Nymburk nebo Benešov • Možnost 1 customer_name (branch_name = “Nymburk” (depositor⋈account))  customer_name(branch_name = “Benešov” (depositor⋈ account)) • Možnost 2 customer_name, branch_name (depositor⋈account)  temp(branch_name)({ (“Nymburk”), (“Benešov”)}) • Všimněme si, že Možnost 2 používá „konstantní relaci“ temp ve funkci dělitele při dělení – ptáme se totiž „pro všechny uvedené pobočky“ Relační model dat a jazyk SQL

27. Pragmatická rozšíření relačních operátorů • Pro často kladené dotazy se zavádějí rozšířené operace • Zobecněná projekce • Agregátní funkce • Vnější spojení (Outer Join) • Zobecněná projekce zavádí aritmetické funkce do seznamu možných výstupních atributů • E je relační výraz a F1, F2, …, Fn jsou aritmetické výrazy zahrnující atributy ze schématu výrazu E a konstanty • Takto se získají odvozené (počítané) atributy • Příklad: • Relace credit_info(customer_name, limit, credit_balance), • Urči, kolik může každá osoba ještě utratit: customer_name, limit – credit_balance(credit_info) Relační model dat a jazyk SQL

28. Agregátní funkce a operace • Agregátní funkce pracují s kolekcí hodnot a vrací jedinou výslednou hodnotu avg: průměrná hodnotamin: minimum max: maximum sum: součet hodnotcount: počet hodnot • Agregátní operace relační algebry vytvářejí relaci se „syntetickými“ atributy a případným seskupováním prvků • E je relační výraz • G1, G2, …, Gm je seznam atributů, podle nich se má seskupovat (může být i prázdný) • Fijsou agregátní funkce • Aijsou jména atributů ze schématu, podle něhož je tvořen E Relační model dat a jazyk SQL

29. Příklad agregátních operací a funkcí A B C         7 7 3 10 branch_name account_number balance Nymburk Nymburk Praha 1 Praha 1 Benešov A-102 A-201 A-217 A-215 A-222 400 900 750 750 700 sum(C) branch_name sum(balance) 27 Nymburk Praha 1 Benešov 1300 1500 700 • Relace r: sum(C)(r): • Relace account seskupená podle branch_name: branch_name  sum(balance)(account): Relační model dat a jazyk SQL

30. Vnější spojení • Vnější spojení je operace, která rozšiřuje přirozené spojení a zamezuje „ztrátě informace“ • Určí se přirozené spojení a pak se přidají prvky z jedné ze spojovaných relací, které nesplňují požadavky na rovnost stejnojmenných atributů • Podle toho, ze které relace se přidávají prvky, rozlišuje se levé vnější spojení a pravé vnější spojení • Lze též přidat prvky z obou spojovaných relací a pak jde o plné vnější spojení • Při doplňování mohou vznikat prvky s neznámými nebo nedefinovanými hodnotami, pro jejichž reprezentaci se zavádí hodnotanull Relační model dat a jazyk SQL

31. Typy a příklady vnějšího spojení přirozené spojení ⋈ levé vnější spojení pravé vnější spojení plné vnější spojení Relační model dat a jazyk SQL

32. Hodnoty Null • null se užívá pro neznámou hodnotu nebo pro označení situace, že hodnota neexistuje • Aritmetický výraz obsahující null dává výsledek null • Agregátní funkce ignorují hodnoty null • Pro eliminaci duplikátů a seskupování se null uvažuje jako jakákoliv jiná hodnota; dvě null hodnoty se považují za identické • Predikáty zahrnující null vyžadují tříúrovňovou logiku s doplňkovou hodnotou unknown • Logika s pravdivostní hodnotou unknown: • OR: (unknownortrue) = true, (unknownorfalse) = unknown (unknown or unknown) = unknown • AND:(true and unknown) = unknown, (false and unknown) = false,(unknown and unknown) = unknown • NOT:(not unknown) = unknown • Selekční predikát vyhodnocený jako unknown se považuje za false Relační model dat a jazyk SQL

33. Modifikace relací v databázi • K modifikaci obsahu databáze potřebujeme operace • Deletion (výmaz = odstranění prvku z relace) • Insertion (vložení prvku do relace) • Updating (aktualizace – změna prvku v relaci) • Vše se realizuje operátorem přiřazení • Výmaz (deletion) r r – E kde r je relace a E je relační výraz určující mazané prvky • Příklady • Vymaž všechny záznamy v pobočce Benešov account  account – branch_name = “Benešov”(account ) • Vymaž všechny záznamy o půjčkách se zůstatkem 0 až 50 loan  loan – amount 0and amount  50(loan) Relační model dat a jazyk SQL

34. Vložení • Vložení v relační algebře je opět přiřazení r  r  E kde r je relace, do níž vkládáme a E je relační výraz • Vložení jediného prvku se realizuje tak, že E bude konstantní výraz popisující prvek • Vložit lze najednou i více prvků, pokud E bude relační výraz kompatibilní s r • Příklad • Vlož do databáze informaci, že zákazník Kovář má účet A-973 se zůstatkem 1200 v pobočce Benešov account  account  {(“A-973”, “Benešov”, 1200)} depositor  depositor  {(“Kovář”, “A-973”)} Relační model dat a jazyk SQL

35. Aktualizace • Mechanismus pro změnu hodnoty zvolených atributů, aniž by se měnily hodnoty všech atributů • Použije se zobecněná projekce • Fi je buď • i-tý atribut r, pokud i-tý atribut nemá být změněn, nebo • Fi je výraz sestavený z konstant a atributů r, který dává novou hodnotu atributu • Příklady • Připočti úrok 5% account  account_number, branch_name, balance * 1.05(account) • Přičti úrok 6% k účtům se zůstatkem přes 10.000 a 5% ke všem ostatním account  account_number, branch_name, balance * 1.06(balance10000(account )) account_number, branch_name, balance * 1.05 (balance 10000 (account)) Relační model dat a jazyk SQL

36. Strukturovaný dotazovací jazyk SQL • Structured Query Language (SQL) • jazyk pro kladení dotazů do databáze • obsahuje jak příkazy DML (manipulace s daty), tak i pro definici dat (DDL) • Svojí syntaxí připomíná přirozenou angličtinu • SQL se opírá o výrazy relační algebry • Existuje mnoho dialektů SQL • liší se různými rozšířeními či speciálními agregátními funkcemi • skladba vestavěných predikátů se rovněž může lišit • Probereme jen základní konstrukty jazyka • konkrétní varianty vždy závisí na příslušném dialektu použitého databázového systému • Poznámka k syntaxi • SQL identifikátory a jména atributů NEROZLIŠUJÍ malá a velká písmena (tj. Branch_Name ≡ BRANCH_NAME ≡ branch_name Relační model dat a jazyk SQL

37. Konstrukce create table • Relace v SQL je definována příkazem create table r (A1D1, A2D2, ..., An Dn,(integritní-omezení1), ..., (integritní-omezeník)) • r je jméno vytvářené relace • Ai jsou jména atributů schématu relace r • Di jsou příslušné datové typy hodnot domén atributů Ai • Integritní omezení jsou standardně tvaru • not null • primary key(A1, ..., AL ) • Příklad create tablebranch ( branch_name char(15) not null,branch_city char(30), assets integer, primary key(branch_name) ) Relační model dat a jazyk SQL

38. Základní struktura SQL dotazu • Typický SQL dotaz má tvar:select A1, A2, ..., Anfrom R1, R2, ..., Rmwhere p • Ai jsou atributy, Ri jsou relace a p je predikát • Tento dotaz je ekvivalentní relačnímu výrazu • Výsledek dotazu je relace • Důležité poznatky • SQL je deklarativní (dotazovací) jazyk, zatímco relační algebra je procedurální • Zobrazení SQL dotazů na relační výrazy převádí deklarativní dotazy na procedury • Provedení („výpočet výsledku“) dotazu bude implementovat procedury operací relační algebry Relační model dat a jazyk SQL

39. Klauzuleselect • Klauzule select uvádí atributy výsledné relace dotazu • odpovídá relační operaci projekce • Příklady: • Získej jména poboček z relace (tabulky) loan:selectbranch_name from loan • V relační algebře branch_name (loan) • Na rozdíl od relací SQL připouští duplikáty v relacích i ve výsledcích dotazů • To narušuje relační model, avšak může výrazně zrychlit zpracování • Eliminaci duplikátů lze vynutit použitím klíčového slova distinctza select. • Získej jména poboček z relace (tabulky) loan a odstraň duplikáty select distinctbranch_namefrom loan • Naopak klíčové slovo allexplicitně říká, aby se duplikáty ponechalyselect allbranch_name from loan Relační model dat a jazyk SQL

40. Klauzuleselect(pokr.) • Hvězdička v klauzuli select značí “všechny atributy” select  from loan • Klauzule select může obsahovat aritmetické výrazy obsahující operace +, –, , / a konstanty nebo atributy • Dotaz select loan_number, branch_name, amount  100 from loan vrátí relaci shodnou s loan až na to, hodnota atributu amount bude vynásobena 100 • Jde vlastně o zobecněnou projekci loan_number, branch_name, amount  100(loan) Relační model dat a jazyk SQL

41. Klauzulewhere • Klauzule whereurčuje podmínky, které musí splňovat výsledek • Odpovídá selekčnímu predikátu relační algebry • Příklad • Najdi čísla půjček z pobočky Benešov vyšší než 1200 select loan_numberfrom loanwhere branch_name="Benešov"andamount>1200 • Porovnání • Výsledky mohou být kombinovány logickými spojkami and, ora not • Porovnání lze aplikovat i na výsledky aritmetických výrazů • SQL zahrnuje i porovnávací operátor between • Např.: Najdi čísla půjček se zůstatky mezi 90.000 a 100.000 (tj.  90.000 a  100.000) select loan_numberfromloanwhereamountbetween90000and100000 což odpovídá relačnímu výrazuloan_number((amount ≥ 90000)(amount ≤ 100000)(loan)) Relační model dat a jazyk SQL

42. Klauzule from • Klauzule from uvádí seznam relací, kterých se dotaz týká • Odpovídá kartézskému součinu relací • Příkaz selectfrom borrower, loan vrátí kartézský součin relací borrower x loan • Najdi jména, čísla půjček a výši dluhů všech zákazníků majících půjčku v pobočce Nymburk select customer_name, borrower.loan_number, amountfrom borrower, loanwhereborrower.loan_number = loan.loan_number and branch_name = "Nymburk" odpovídá relačnímu výrazu  customer_name, borrower.loan_number, amount (  borrower.loan_number = loan.loan_number  branch_name="Nymburk" (borrower xloan)) Relační model dat a jazyk SQL

43. Operace přejmenování • SQL umožňuje relace a atributy pomocí klauzule as old-name as new-name • Najdi jména, čísla půjček a dlužné částky všech zákazníků a pojmenuj sloupec loan_number jako loan_id selectcustomer_name, borrower.loan_number asloan_id, amountfromborrower, loanwhereloan_id = loan.loan_number • Domácí úkol: • Přepište tento dotaz do formy relačního výrazu Relační model dat a jazyk SQL

44. n-tice jako proměnné • Proměnné ve tvaru n-tic se definují jako proměnné v klauzulifroms použitím klauzuleas • Příklad • Najdi jména zákazníků, čísla jejich půjček a výši dluhů přes všechny pobočky selectcustomer_name, B.loan_number, L.amountfromborrower asB, loan asLwhereB.loan_number = L.loan_number • Najdi jména poboček, které mají součet vkladů (assets) větší některá z poboček v Praze 1 select distinctT.branch_namefrombranch asT, branch asSwhereT.assets > S.assets andS.branch_city = "Praha 1" Relační model dat a jazyk SQL

45. SQL připouští duplikáty • Pro zajištění dobré analogie SQL a množinového modelu potřebujeme tzv. multisety • Multiset je množina s opakujícími se prvky • Potřebujeme multisetové verze relačních operátorů mezi relacemi r1 a r2 •  (r1): Je-li c1 kopií n-tice t1 v r1, a t1 splňuje selekční predikát ,, pak bude c1 kopií t1 v  (r1). • A (r ): Pro každou kopii t1v r1 bude kopie A (t1) i v A (r1) • r1xr2: Je-li c1 kopií t1v r1 a c2 kopií t2 v r2, pak bude c1  c2 kopií n-tice t1t2 v r1 xr2 • Příklad: • Multisetové relace r1 (A, B) a r2 (C) jsou r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)} • Pak B(r1) bude {(a), (a)}, a B(r1) xr2 dá {(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)} • SQL sémantika příkazu selectA1,, A2, ..., Anfromr1, r2, ..., rmwhereP je ekvivalentní multisetové verzi výrazu Relační model dat a jazyk SQL

46. Množinové operace v SQL • Množinové operátory union, intersect aexcept jsou SQL ekvivalentem relačních (množinových) operací  a • Najdi zákazníky mající vkladový účet nebo půjčku (nebo oboje) (selectcustomer_name fromdepositor)union(selectcustomer_name from borrower) • Najdi zákazníky mající jak vkladový účet tak půjčku (selectcustomer_name fromdepositor)intersect(selectcustomer_name from borrower) • Najdi zákazníky mající vkladový účet a nemající půjčku (selectcustomer_name fromdepositor)except(selectcustomer_name from borrower) • SQL má dále operátor in, který testuje příslušnost či členství v množině • ekvivalent ∈ Relační model dat a jazyk SQL

47. Agregátní funkce v SQL • Tyto funkce pracují s multisety hodnot a vrací hodnotu jedinou • jinak jsou shodné s dříve uvedenými agregátními funkcemi avg, min, max, sum a count • Najdi průměrný vklad v pobočce Benešov select avg(balance)from accountwherebranch_name = "Benešov" • Urči počet vkladatelů select count (distinctcustomer_name)fromdepositor Relační model dat a jazyk SQL

48. Hodnoty null v SQL • Predikát is null slouží k testu null hodnot • Např.: V relaci loan vyhledej čísla půjček s null hodnotou atributu amount select loan_number from loan whereamount is null • Aritmetické operace zahrnující null dávají null • Např.: 5 + null vrací null • Agregátní funkce null hodnoty ignorují • Je zavedena tříhodnotová logika s unknown • Např.:5 < null, null <> null nebo null = null se vždy vyhodnotí jako unknown • Konstrukt pis unknown se vyhodnotí jako pravdivý, pokud predikát p má hodnotu unknown Relační model dat a jazyk SQL

49. Vnořené dotazy Vnořený dotaz • SQL má mechanismus pro vnořování dotazů (subquery) • někdy zvané pod-dotazy • Vnořený dotazmá obvyklý tvarselect-from-where, je však zanořen do jiného dotazu • Nejčastěji se používá k realizaci testu členství v relaci, porovnávání množin a určování kardinality relací • Příklad: • Najdi zákazníky mající jak vkladový účet tak i půjčku select distictcustomer_name fromborrower where customer_name in (selectcustomer_name from depositor) Relační model dat a jazyk SQL

50. Pohledy • Často je nevhodné poskytovat uživateli všechna data • tedy celý logický model databáze a všechny uložené relace • Bankovní úředník na jisté pozici potřebuje znát jméno zákazníka a pobočku, kde má půjčku, ne však výši půjčky. (selectcustomer_name, branch_name fromborrower, loanwhereborrower.loan_number = loan.loan_number ) • Mechanismus pohledů (view) umožňuje skrýt určitá data • Lze tak vytvořit jakoukoliv relaci, která není součástí konceptuálního modelu a zpřístupnit ji uživateli jako "virtuální relaci". Taková "virtuální relace" se nazývá pohled. • Zavede se příkazem create view ve tvaru create viewv as<formulace dotazu> kde v je jméno pohledu • Jakmile je pohled definován, jeho jméno lze používat jako zkratku celého definičního dotazu Relační model dat a jazyk SQL