Obsah

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Obsah

Télécharger la présentation

Plus de contenu associé