Téma 10 Relační model dat a jazyk SQL. Relace je podmnožina kartézského součinu. K čemu relace?

K čemu relace?

Téma 10 – Relační model dat a jazyk SQL

• Viděli jsme tabulky. Proč potřebujeme něco jiného? • Je k tomu řada důvodů:

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í

1

A3B33OSD (J. Lažanský) verze: Jaro 2014

• V množinách neexistuje duplicita

Bush

– Velmi důležité pro databázové aplikace

– neexistuje uspořádání

Lincoln Obama Roosevelt

Thomas

Theodore

Jména

John

Jimmy

Washington

• Vzhledem k tomu, že jde vždy o konečné množiny, lze je vyjádřit výčtem, tedy tabulkami 3

Kenedy

George

{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_jmeno x klient_ulice x klient_mesto

Clinton Jefferson

Franklin

– klient_jmeno =

• Prvky množiny mohou být v jakémkoliv pořadí

Carter

Bill

• Matematicky: Jsou dány množiny 1, 2, …, n, pak relací  rozumíme podmnožinu kartézského součinu 1 x 2 x … x n. Relace tedy je množina n-tic (a1, a2, …, an), kde ai Îi • Příklad:

Relační model dat a jazyk SQL

2

Relace je podmnožina kartézského součinu

Co to je relace?



Barac


• 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

Abraham


– Potřeba rigorózního matematického modelu – Model umožní formalizaci Tabulka klient databázových operací – Přesný model je potřebný k tvorbě deklarativně formulovaných dotazů a k optimalizaci jejich provádění

Příjmení

1. 2. 3. 4. 5. 6. 7. 8. 9. 10.

Vybraní američtí prezidenti



4

Typy atributů

Relační schéma a instance • Relační schéma

• 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é

– A1, A2, …, An jsou atributy – R = (A1, A2, …, An ) je relační schéma Příklad: Klient_schema = (klient_jmeno, klient_ulice, klient_mesto)

– r(R) značí relaci r nad relačním schématem R Příklad:

– Např. hodnotou atributu „číslo_účtu“ smí být číslo jednoho účtu, nikoliv množina čísel účtů

klient (Klient_schema)

• Speciální hodnota null patří do každé domény

• Instance relace (relační instance)

– prázdná (nezadaná) hodnota – null značně komplikuje definici mnoha množinových operací, a proto zpočátku tuto hodnotu budeme ignorovat

– Skutečné hodnoty (relační instance) jsou definovány výčtem, tj. tabulkou – Prvek t relace r je n-tice, reprezentovaná řádkem tabulky

• důsledky uvedeme později

atributy (tj. sloupce) klient_jmeno Novák Novotný Braun Mates



5

klient_ulice Spálená Horní Hlavní Horní

Praha Nymburk Brno Brno

klient


n-tice (řádky)


6

Cizí klíče

Klíče (znovu)

• 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íč

• 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}

– 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é)

• 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í

• Důležitý typ omezení – referenční integrita

• 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ě

branch

account

depositor

customer

branch_name

account_number

customer_name

branch_city assets

branch_name balance

customer_name account_number

• 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)

loan

borrower

loan_number

customer_name loan_number

branch_name amount A3B33OSD (J. Lažanský) verze: Jaro 2014

klient_mesto


7


customer_street customer_city


8

Relační algebra

Selekce

• Zápis s p(r)

• Relační algebra je vlastně procedurální jazyk • Šest základních operátorů

– p je selekční predikát

– Selekce (restrikce) s

• Definice

• Výběr jen některých prvků relace

sp(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 =, ¹, >, ³, <, £

– 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

• Příklad selekce: sklient_mesto=“Praha” (klient)

• Klasická množinová operace

– Přejmenování: r

A B C D

• 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 A3B33OSD (J. Lažanský) verze: Jaro 2014


r

9

a a b b

a b b b

1 5 12 23

A B C D

7 7 3 10

sA=B Ù D > 5 (r)



Projekce

• Zápis: r È s • Definice: r È s = {t | t Î r Ú t Î s} • Relace r a s musí být kompatibilní, tj

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ů

1. r a s musí mít stejnou aritu (počet atributů) 2. Domény atributů musí být po řadě shodné

– Tedy vynecháním zbývajících (neuvedených) atributů – Duplicitní prvky (řádky) jsou odstraněny – relace jsou množiny!

• Např. druhý atribut relace r a druhý atribut relace s musí mít shodný datový typ (definiční doménu)

• Příklad:

• Příklad: V relaci klient nás nezajímá atribut klient_ulice Õklient_jmeno, klient_mesto (klient)

r

a a b b


10 20 30 40

1 1 1 2

ÕA,C (r)

10

Sjednocení

• Zápis: Õ A1 , A2 ,K, Ak ( r )

A B C

a a 1 7 b b 23 10

A

C

a a b b

1 1 1 2

=

A

C

a b b

1 1 2


– najít všechny zákazníky banky, kteří mají vklad nebo půjčku Õcustomer_name (depositor) È Õcustomer_name (borrower)

Relace r, s:

A

B

A

B

a a b

1 2 1

a b

2 3

r 11


s

r È s:

A

B

a a b b

1 2 1 3


12

Rozdíl

Kartézský součin

• Zápis: r – s • Definice: r – s = {t | t Î r Ù t Ï 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í

• Relace vstupující do množinového rozdílu musí opět být vzájemně kompatibilní

Relace r, s:

A

B

A

B

a a b

1 2 1

a b

2 3

A

r – s:

s

r

A

B

a b

1 1

Relace r, s:

B

a b

1 2 r

E

a b b g

10 10 20 10

a a b b

r x s:

s


13


r X (E )

r x s:

vrátí výsledek výrazu E pod jménem X – Jestliže relační výraz E má aritu n, pak 2

,..., A n )

(E )

vrátí výsledek výrazu E pod jménem X s atributy přejmenovanými na A1 , A2 , …., An .


C

D

E

a a a a b b b b

1 1 1 1 2 2 2 2

a b b g a b b g

10 10 20 10 10 10 20 10

a a b b a a b b

14

• Skutečně užitečné relační operace vzniknou teprve skládáním operací základních

– 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

1

B

Skládání operací

• Pomocná operace

r X ( A ,A

A


Operace přejmenování


D

• POZOR: Mohou vznikat tabulky gigantické velikosti


• Příklad:

C

sA=C(r x s):

15


A

B

C

D

E

a a a a b b b b

1 1 1 1 2 2 2 2

a b b g a b b g

10 10 20 10 10 10 20 10

a a b b a a b b

A

B

C

D

E

a b b

1 2 2

a b b

10 10 20

a a b


16

Příklad bankovní databáze

Příklad bankovní databáze (2)

• Relace – – – – – –

• Další příklady dotazů

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)

– 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 P customer_name (s branch_name="Nymburk" (s borrower.loan_number = loan.loan_number (borrower ´ loan))) - P customer_name (depositor )

• Příklady dotazů

– Najdi všechny půjčky (loan) přes 1200 s amount>1200 (loan)

– Najdi jména zákazníků, kteří mají půjčku vedenou v pobočce Nymburk • 1. možnost

– Najdi čísla půjček vyšších než 1200 P loan _ number (s amount >1200 (loan))

P customer _ name (s branch _ name = "Nymburk " (s borrower.l oan _number

– Najdi jména zákazníků majících vkladový účet v pobočce Nymburk P customer _ name (s branch _ name ="Nymburk"


P customer _ name (s borrower.loan_number = loan.loan_number (

17

(s branch _ name="Nymburk" (borrower)) ´ loan)) A3B33OSD (J. Lažanský) verze: Jaro 2014

Příklad bankovní databáze (3)


• Z praktických důvodů se definují další operátory, které umožňují zjednodušení častých dotazů do databáze

– 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

Průnik Přirozené spojení (spojení přes rovnost) Dělení Přiřazení

• Průnik

– Zápis: r Ç s – Definice:

– Dotaz pak vypadá takto: Пbalance(account) – Пaccount.balance (σaccount.balance < temp.balance (account x ρtemp(account) ) )

rÇs={t|tÎrÙtÎs}

– Předpoklad: Relace r a s jsou vzájemně kompatibilní – Poznámka: r Ç s = r – (r – s) A

B

A

B

Relace r, s: a

1 2 1

a b

2 3

a b r


18

Doplňkové operace, průnik

• Příklady dotazu (použití operace přejmenování)


(borrower ´ loan )))

• 2. možnost

(s depositor. account_nu mber = account.a ccount_num ber ( depositor ´ account ))) A3B33OSD (J. Lažanský) verze: Jaro 2014

= loan.loan _number

19


r Ç s:

A

B

a

2

s Relační model dat a jazyk SQL

20

Přirozené spojení

Přirozené spojení – příklad

• 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

• Relace r, s:

D

B

D

E

a b g a d

1 2 4 1 2

m g b g b

a a b a b

1 3 1 2 3

a a a b b

a b g d Î

A

B

C

D

E

a a a a d

1 1 1 1 2

m a g g b

a a a a b

a g a g d

Oddeleni Odddel Manager Finance Jirka Obchod Petr Vyroba Karel

Zamestnanec Oddeleni Jmeno ZamId Oddel Manager Franta 1235 Finance Jirka Pavla 2241 Obchod Petr Josef 3401 Výroba Karel Petr 2202 Výroba Karel 21


Operace dělení


22

Operace dělení – příklad • Relace 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

B

a a a b g d d d

1 2 3 1 1 1 3 4 6 1 2

b

– Nechť q = r ¸ s, pak q je největší relace splňující q x s Í r

r¸s: B

A

a b

1 2

s

r

• Definice pomocí základních operací relační algebry

• Praktický příklad

– Nechť r(R) a s(S) jsou relace a nechť S Í R r ¸ s = ÕR-S (r) – ÕR-S (( ÕR-S (r) x s) – ÕR-S,S(r))

Pracuje_pro Jmeno Manager Franta Jirka Pavla Petr Josef Karel Petr Karel

• Õ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


A

ε ε

• Vlastnost


r s:

s

Zamestnanec Jmeno ZamId Oddel Franta 1235 Finance Pavla 2241 Obchod Josef 3401 Výroba Petr 2202 Výroba

P r . A, r . B , r .C , r . D , s. E (s r . B =s. B Ùr . D =s. D (r ´ s ))

• Zápis: r ¸ s

C

r

R = (A, B, C, D) S = (E, B, D) – Výsledné schéma = (A, B, C, D, E) s pak je: – r


B

• Praktický příklad

• 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:


A

23


Šéf Manager Jirka Karel

Pracuje_pro ¸ Šéf Jmeno Franta Josef Petr


24

Přiřazovací operace

Příklad bankovní databáze – další dotazy

• 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

• 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 loan) Õcustomer_name, loan_number, amount (borrower • Najdi jména všech zákazníků, kteří mají vkladový účet v pobočce Nymburk nebo Benešov – Možnost 1 Õcustomer_name (sbranch_name = “Nymburk” (depositor account)) È Õcustomer_name (sbranch_name = “Benešov” (depositor account)) – Možnost 2 Õcustomer_name, branch_name (depositor account) ¸ rtemp(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“



25



26

Pragmatická rozšíření relačních operátorů

Agregátní funkce a operace

• Pro často kladené dotazy se zavádějí rozšířené operace

• Agregátní funkce pracují s kolekcí hodnot a vrací jedinou výslednou hodnotu

– Zobecněná projekce – Agregátní funkce – Vnější spojení (Outer Join)

avg: průměrná hodnota min: minimum max: maximum sum: součet hodnot count: počet hodnot

• Zobecněná projekce zavádí aritmetické funkce do seznamu možných výstupních atributů

ÕF1,F2 ,L, Fn (E)

• 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 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:

G1 ,G2 ,K,Gm

– 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)



JF ( A ), F ( A ), K, F ( A ) ( E ) 1

1

2

2

n

n

– E je relační výraz – G1, G2, …, Gm je seznam atributů, podle nich se má seskupovat (může být i prázdný) – Fi jsou agregátní funkce – Ai jsou jména atributů ze schématu, podle něhož je tvořen E 27



28

Příklad agregátních operací a funkcí • Relace r:

A

B

a a b b

a 7 b 7 b 3 b 10

Jsum(C)(r):

C

Vnější spojení • Vnější spojení je operace, která rozšiřuje přirozené spojení a zamezuje „ztrátě informace“

sum(C)

– 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í

27

• Relace account seskupená podle branch_name: branch_name account_number balance Nymburk A-102 400 Nymburk A-201 900 Praha 1 A-217 750 Praha 1 A-215 750 Benešov A-222 700

branch_name J sum(balance)(account):


– Při doplňování mohou vznikat prvky s neznámými nebo nedefinovanými hodnotami, pro jejichž reprezentaci se zavádí hodnota null

branch_name sum(balance) Nymburk Praha 1 Benešov

1300 1500 700


29


přirozené spojení

levé vnější spojení

pravé vnější spojení

plné vnější spojení A3B33OSD (J. Lažanský) verze: Jaro 2014

borrower customer_name loan_number Jonáš L-170 Kovář L-230 Sláma L-155

• 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é

loan borrower loan_number branch_name amount customer_name L-170 Praha 1 3000 Jonáš L-230 Nymburk 4000 Kovář loan borrower loan_number branch_name amount customer_name L-170 Praha 1 3000 Jonáš L-230 Nymburk 4000 Kovář L-260 Benešov 1700 null

• Predikáty zahrnující null vyžadují tříúrovňovou logiku s doplňkovou hodnotou unknown – Logika s pravdivostní hodnotou unknown: • OR:

(unknown or true) (unknown or false) (unknown or unknown) • AND: (true and unknown) (false and unknown) (unknown and unknown) • NOT: (not unknown)

loan borrower loan_number branch_name amount customer_name L-170 Praha 1 3000 Jonáš L-230 Nymburk 4000 Kovář L-155 null null Sláma loan borrower loan_number branch_name amount customer_name L-170 Praha 1 3000 Jonáš L-230 Nymburk 4000 Kovář L-260 Benešov 1700 null L-155 null null Sláma


30

Hodnoty Null

Typy a příklady vnějšího spojení

loan loan_number branch_name amount L-170 Praha 1 3000 L-230 Nymburk 4000 L-260 Benešov 1700


= true, = unknown = unknown = unknown, = false, = unknown = unknown

– Selekční predikát vyhodnocený jako unknown se považuje za false 31



32

Modifikace relací v databázi

Vložení • Vložení v relační algebře je opět přiřazení

• K modifikaci obsahu databáze potřebujeme operace

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

– 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

• 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

• Vymaž všechny záznamy v pobočce Benešov

account ¬ account – sbranch_name = “Benešov”(account )

account ¬ account È {(“A-973”, “Benešov”, 1200)} depositor ¬ depositor È {(“Kovář”, “A-973”)}

• Vymaž všechny záznamy o půjčkách se zůstatkem 0 až 50

loan ¬ loan – samount ³ 0 and amount £ 50(loan)



33

Aktualizace – Použije se zobecněná projekce

r ¬ Õ F ,F ,K,F , (r ) 1

2

34

– jazyk pro kladení dotazů do databáze – obsahuje jak příkazy DML (manipulace s daty), tak i pro definici dat (DDL)

– 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

• Příklady

– Připočti úrok 5%

• Probereme jen základní konstrukty jazyka

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 (sbalance>10000 (account )) È Õaccount_number, branch_name, balance * 1.05 (sbalance£ 10000 (account))


• Structured Query Language (SQL)

• Svojí syntaxí připomíná přirozenou angličtinu • SQL se opírá o výrazy relační algebry • Existuje mnoho dialektů SQL

l

• 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



Strukturovaný dotazovací jazyk SQL

• Mechanismus pro změnu hodnoty zvolených atributů, aniž by se měnily hodnoty všech atributů – Fi je buď


35

– 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



36

Konstrukce create table

Základní struktura SQL dotazu • Typický SQL dotaz má tvar: select A1, A2, ..., An from R1, R2, ..., Rm where p

• Relace v SQL je definována příkazem

create table r (A1 D1, A2 D2, ..., 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

– Ai jsou atributy, Ri jsou relace a p je predikát

• Tento dotaz je ekvivalentní relačnímu výrazu Õ A1 , A2 ,K, An (s p ( R1 ´ R2 ´ K´ Rm ))

• Integritní omezení jsou standardně tvaru – not null – primary key(A1, ..., AL )

– Výsledek dotazu je relace

• Příklad

• Důležité poznatky

create table branch ( branch_name char(15) not null, branch_city char(30), assets integer, primary key(branch_name) ) A3B33OSD (J. Lažanský) verze: Jaro 2014


– 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

37



Klauzule select

Klauzule select (pokr.)

• Klauzule select uvádí atributy výsledné relace dotazu

• Hvězdička v klauzuli select značí “všechny atributy”

• Příklady:

• Klauzule select může obsahovat aritmetické výrazy obsahující operace +, –, *, / a konstanty nebo atributy • Dotaz

– odpovídá relační operaci projekce

38

select * from loan

– Získej jména poboček z relace (tabulky) loan: select branch_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 distinct za select.

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 Ploan_number, branch_name, amount * 100(loan)

– Získej jména poboček z relace (tabulky) loan a odstraň duplikáty select distinct branch_name from loan – Naopak klíčové slovo all explicitně říká, aby se duplikáty ponechaly select all branch_name from loan



39



40

Klauzule where

Klauzule from • Klauzule from uvádí seznam relací, kterých se dotaz týká

• Klauzule where určuje podmínky, které musí splňovat výsledek

– Odpovídá kartézskému součinu relací – Příkaz

– Odpovídá selekčnímu predikátu relační algebry

• Příklad

select * from borrower, loan

– Najdi čísla půjček z pobočky Benešov vyšší než 1200 select loan_number from loan where branch_name="Benešov" and amount>1200

vrátí kartézský součin relací borrower x loan

• Porovnání

– Výsledky mohou být kombinovány logickými spojkami and, or a 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_number from loan where amount between 90000 and 100000 což odpovídá relačnímu výrazu Ploan_number(s(amount ≥ 90000)Ù(amount ≤ 100000)(loan))



41

– 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, amount from borrower, loan where borrower.loan_number = loan.loan_number and branch_name = "Nymburk" odpovídá relačnímu výrazu P customer_name, borrower.loan_number, amount ( s borrower.loan_number = loan.loan_number Ù branch_name="Nymburk" (borrower x loan)) A3B33OSD (J. Lažanský) verze: Jaro 2014

• SQL umožňuje relace a atributy pomocí klauzule as old-name as new-name

• Proměnné ve tvaru n-tic se definují jako proměnné v klauzuli from s použitím klauzule as • Příklad

– Najdi jména, čísla půjček a dlužné částky všech zákazníků a pojmenuj sloupec loan_number jako loan_id

– Najdi jména zákazníků, čísla jejich půjček a výši dluhů přes všechny pobočky select customer_name, B.loan_number, L.amount from borrower as B, loan as L where B.loan_number = L.loan_number

select customer_name, borrower.loan_number as loan_id, amount from borrower, loan where loan_id = loan.loan_number

– Najdi jména poboček, které mají součet vkladů (assets) větší některá z poboček v Praze 1 select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and S.branch_city = "Praha 1"

• Domácí úkol:

– Přepište tento dotaz do formy relačního výrazu


42

n-tice jako proměnné

Operace přejmenování



43



44

SQL připouští duplikáty

Množinové operace v SQL

• 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

– sq (r1): Je-li c1 kopií n-tice t1 v r1, a t1 splňuje selekční predikát

q,, pak bude c1 kopií t1 v sq (r1).

– PA (r ): Pro každou kopii t1 v r1 bude kopie PA (t1) i v PA (r1) – r1 x r2: Je-li c1 kopií t1 v r1 a c2 kopií t2 v r2, pak bude c1 * c2

– Najdi zákazníky mající vkladový účet a nemající půjčku

– Multisetové relace r1 (A, B) a r2 (C) jsou r1 = {(1, a) (2,a)} r2 = {(2), (3), (3)} – Pak PB(r1) bude {(a), (a)}, a PB(r1) x r2 dá {(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}

(select customer_name from depositor) except (select customer_name from borrower)

• SQL sémantika příkazu select A1,, A2, ..., An from r1, r2, ..., rm where P je ekvivalentní multisetové verzi výrazu Õ A ,A ,K,A (s P (r1 ´ r2 ´ K ´ rm )) 2

n


(select customer_name from depositor) union (select customer_name from borrower) (select customer_name from depositor) intersect (select customer_name from borrower)

• Příklad:

1

– Najdi zákazníky mající vkladový účet nebo půjčku (nebo oboje)

– Najdi zákazníky mající jak vkladový účet tak půjčku

kopií n-tice t1¼t2 v r1 x r2


• Množinové operátory union, intersect a except jsou SQL ekvivalentem relačních (množinových) operací È, Ç a -

45

• SQL má dále operátor in, který testuje příslušnost či členství v množině – ekvivalent


Agregátní funkce v SQL


Hodnoty null v SQL

• Tyto funkce pracují s multisety hodnot a vrací hodnotu jedinou

• Predikát is null slouží k testu null hodnot

• Najdi průměrný vklad v pobočce Benešov

• Aritmetické operace zahrnující null dávají null

– Např.: V relaci loan vyhledej čísla půjček s null hodnotou atributu amount select loan_number from loan where amount is null

– jinak jsou shodné s dříve uvedenými agregátními funkcemi avg, min, max, sum a count

– Např.: 5 + null vrací null

select avg(balance) from account where branch_name = "Benešov"

• 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

• Urči počet vkladatelů

select count (distinct customer_name) from depositor


46


• Konstrukt p is unknown se vyhodnotí jako pravdivý, pokud predikát p má hodnotu unknown

47



48

Vnořené dotazy

Pohledy

• SQL má mechanismus pro vnořování dotazů (subquery)

• Často je nevhodné poskytovat uživateli všechna data

– někdy zvané pod-dotazy

• Vnořený dotaz má obvyklý tvar select-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í

(select customer_name, branch_name from borrower, loan where borrower.loan_number = loan.loan_number )

• Příklad:

• Mechanismus pohledů (view) umožňuje skrýt určitá data

– Najdi zákazníky mající jak vkladový účet tak i půjčku select distict customer_name from borrower where customer_name in (select customer_name from depositor) Vnořený dotaz



– 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.

– 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 view v as 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

49



50

Spojení relací v SQL

SQL příkazy pro modifikaci databáze

• Základní syntaxe je r1 join r2 on <podmínka> using (A1, ...)

• Výmaz (deletion)

– Příkaz má strukturu delete-from-where s argumenty analogickými konstruktu select-from-where – Vymaž všechny vkladové účty v pobočce Nymburk

– Úplná SQL syntaxe je popsána v příslušných dialektech a standardizace je jen částečná – Typicky se používá jako součást pod-dotazu v klauzuli from.

delete from account where branch_name = ‘Nymburk‘

• Typ spojení – "přívlastek" klíčového slova join

• Vložení (insertion)

– insert into relace values – Přidej záznam do tabulky account insert into account (branch_name, balance, account_number) values ('Beroun', 1200, 'A-9732') • Aktualizace (update) – update relace set atribut = výraz where podmínka – Přidej 6% prémie ke vkladovým účtům přes 1000 update account set balance = balance * 1.06 where balance > 1000

– Jde o úplnou ekvivalenci se spojeními z relační algebry – Typy: inner join, left outer join, right outer join, full outer join

• Spojovací podmínka

– určuje, na základě čeho má dojít ke spojení a které atributy budou ve výsledném spojení

• Příklad

– Najdi všechny zákazníky, kteří mají buď půjčku nebo vkladový účet, ale ne oboje select customer_name from (depositor full outer join borrower ) where account_number is null or loan_number is null



51



52

Dotazy A3B33OSD (J. Lažanský) verze: Jaro 2014


53

Téma 10 Relační model dat a jazyk SQL. Relace je podmnožina kartézského součinu. K čemu relace?

Recommend Documents