Databaze_MFF

lidator/ - SQL validator
http://kocour.ms.mff.cuni.cz/~pokorny/vyuka/srbd-sql/
- slajdy SQL92
http://kocour.ms.mff.cuni.cz/~pokorny/vyuka/pokorny3/
- příklady dotazů Kódy učitelů, kteří učí každý den, kdy učí Novák (kód 358)
select x.KU from R x
where not exists
( select * from R y
where y.KU='358' and not exists
( select * from R
where (DT=y.DT)and (KU=x.KU)
)
) Spoje ("přidání jedné tranzitívní hrany")
výchozí tabulka je SPOJ (ZAC, KON)
(select * from SPOJ) union (select t.ZAC,s.KON from SPOJ t, SPOJ s where t.KON=s.ZAC)
popř. select t.ZAC,s.KON from SPOJ t, SPOJ s where t.ZAC=s.ZAC& t.KON=s.KON Ú t.KON=s.ZAC v klauzuli SELECT jsme používali zatím buď * nebo seznam jmen sloupců
ve FROM seznam jmen tabulek - nejčastěji jednoprvkový; pokud užit víceprvkový – znamená kartézský součin ve WHERE boolský výraz sestávající z podmínek pro výběr řádků: nejčastěji porovnání hodnot ze dvou sloupců nebo porovnání hodnoty ze sloupce s konstantou,
dále [not]exists+vnořený dotaz minimální dotaz v SQL musí obsahovat SELECT a FROM
postupně budeme probírat další možnosti odpověď na dotaz zapsaný v SQL na rozdíl od teorie není množina –
pokud to chceme, musíme např.
místo select PŘ from U
napsat select distinct PŘ from U agregační funkce
např. max,min,avg
maximální, minimální resp. průměrná hodnota z hodnot ve sloupci
pro tabulku ZAM s atributy ČZ,PLAT,ČV
select max(PLAT) from ZAM  
select max(PLAT), min(PLAT), avg(PLAT) from ZAM ale nelze psát select ČZ, max(PLAT) from ZAM
a očekávat, že dostaneme u maximálního platu číslo zaměstnance, který ten maximální plat má
tento příkaz je syntakticky nesprávný, nelze použít ani na tabulku o jednom řádku musíme psát 
select ČZ, PLAT from ZAM where PLAT = (select max(PLAT) from ZAM)
 
! nelze psát
select ČZ, PLAT from ZAM where PLAT = max (select PLAT from ZAM))
další přípustný zápis
select ČZ, PLAT from ZAM where PLAT >= all (select PLAT from ZAM)
jako "exists" , tak také "all" se podobá kvantifikátoru, má však množinový význam "c< >all (select..)" je ekvivalentní "c not in (select ....)" a také příslušné podmínce s "not exists", tj. "not exists ( select b from…where b=c)"
call(select..) implikuje "c not in (select ....) "
"c = all ( select ....)" by bylo true pouze pokud by select dávalo jen hodnoty c nejčastější použití je "c>=all (select ...)" místo "c=(select max( ...)….)" apod.
 
další ekvivalence jsou in  = some  = any count - počet řádků popř. počet různých hodnot atributu
 
použití v klauzuli SELECT např.
select count (*) from ZAM
což počítá počet řádků tabulky ZAM další možnost je
select count (distinct A) from TAB
v popisech se píše také
select count (A) from TAB
ale ne všude je to akceptováno
v podmínkách
not exists odpovídá 0 = (select count(*) ...)
exists odpovídá 0 < (select count(*) ...)
 
podobně jako pro výše uvedené agregační funkce nelze ani pro count použít zápis
count (select ...), ale vždy jen select count ... Další dosud ne všude použitelná konstrukce je porovnání množin ve WHERE, tj. podmínka tvaru
např. (select ....) = (select ...).
Ačkoli normy to dovolují, není to povoleno např. ani v Oracle9.
Operandy v podmínkách však mohou být select dotazy s jednoprvkovým výsledkem, např. získaným agregační funkcí – ale i "obyčejným" select. důležitá konstrukce je group by
select max(PLAT), min(PLAT), avg(PLAT) from ZAM group by ČV
select ČV, max(PLAT), min(PLAT), avg(PLAT) from ZAM group by ČV nelze psát select ČZ from ZAM group by ČV
lze psát select ČZ, max(PLAT) from ZAM group by ČZ ale to ovšem není zajímavé
select ČV from ZAM group by ČV je ekvivalentní zápisu select distinct ČV from ZAM Kódy učitelů, kteří učí každý den, pro který je rozvrh:
select KU from R group by KU
having count( distinct DT ) =
( select count( distinct DT ) from R ) select rč from Návštěva , Lékař where Návštěva.Člic=Lékař.Člic group by Rč having count (distinct Návštěva.Člic)=1 and min(Jm_lékaře)='Čermák'
Návštěva(Člic,Rč); Lékař(Člic,Jm_lékaře). KINO(názevk,vedoucí, kapacita), FILM(jménof,rež,rok,země), PROG(jménof, názevk, datum)
select názevk from FILM,PROG where FILM.jménof=PROG.jménof & FILM.rež='XY'
 
select názevk from PROG where exists (select jménof from FILM where FILM.jménof=PROG.jménof & FILM.rež='XY')
 
select názevk from PROG where názevf in (select jménof from FILM where FILM.rež='XY') select názevk from PROG where názevf = some (select jménof from FILM where FILM.rež='XY')
 
select názevk from PROG where 0 < (select count (*) from FILM where (FILM.jménof=PROG.jménof)& (FILM.rež='XY')) select názevk from KINO k where 0 = (select count(*) from FILM, PROG where rež='XY' and FILM.jménof=PROG.jménof and názevk=k.názevk)
select názevk from KINO k where 0 = (select count(*) from FILM, PROG where rež< >'XY' and FILM.jménof=PROG.jménof and názevk=k.názevk) select názevk k from PROG where not exists (select * from FILM where rež='XY' and not exists (select * from PROG where názevk=k.názevk and jménof=film.jménof))
select jménof from PROG group by jménof having count(distinct názevk) = (select count(distinct názevk) from PROG) select jménof,sum(kapacita) from KINO,PROG where KINO.názevk=PROG.názevk group by jménof having sum(kapacita)
>= all (select jménof,sum(kapacita) from KINO,PROG where KINO.názevk=PROG.názevk group by jménof ) Objednávka(čís_o, rodč, …), Zákazník(rodč, …)
select rodč, count(distinct čís_o) from O x group by rodč having count(*) > all (select count(distinct čís_o) from O where rodč< >x.rodč group by rodč)
za dotaz lze napsat order by
což znamená uspořádání dle jednoho či více atributů vzestupně či sestupně
lze i různě pro různé atributy; ASC je default;
manipulace s prázdnými množinami,
prázdnými hodnotami
a řádky tvořenými prázdnými hodnotami
vede k porušení ekvivalence konstrukcí =ANY() false =ANY(řádky z null) UNKNOWN
IN() false IN(řádky z null) UNKNOWN
=ALL () true =ALL(řádky z null) UNKNOWN Select čz From zam Where plat not in (Select plat From zam Where adresa=‘Praha’)
Select čz From zam z Where not exists (Select plat From zam Where adresa=‘Praha’ and plat=z.plat) Select čz From zam Where plat not in (Select plat From zam Where adresa=‘Praha’)
při existenci jediného pražského zaměstnance s platem=null:
vnitřní dotaz dá null; porovnání not in null dá unknown;
výsledek je  Select čz From zam z Where not exists (Select plat From zam Where adresa=‘Praha’ and plat=z.plat)
ve vnořeném dotaze porovnávám plat s null ..to bude unknown
tedy vnořený dotaz dá  a not exists dá true  celkový výsledek jsou všichni MAX(), MIN(), SUM(),AVG() = null  porovnání s nimi dá UNKNOWN
stejně tak pro množiny z prázdných řádků další podobný případ je
select čz from zam where plat>=all (select z.plat from zam z where z.jm='XY')
select čz from zam where plat=(select max(z.plat) from zam z where z.jm='XY')
pokud neexistuje žádný zaměstnanec jménem 'XY', dá první zápis všechny zaměstnance
kdežto druhý zápis prázdnou množinu COUNT()=0
COUNT(*) řádky prázdných hodnot počítá + ještě podle počtu výskytů
select COUNT(*) from tab where is null skutečně spočítá řádky s hodnotou null v
COUNT(atrib) má dát počet neprázdných hodnot ve sloupci atrib
Databázové systémyDBI025 Antonín Říha
KSI MFF UK
http://www.ms.mff.cuni.cz/~riha/dbs1.htm
KINO(názevk,vedoucí, kapacita), FILM(jménof,rež,rok,země), PROG(jménof, názevk, datum)
select názevk k from PROG where not exists (select * from FILM where rež='XY' and not exists
(select * from PROG where názevk=k.názevk
and jménof=film.jménof) ) KINO(názevk,vedoucí, kapacita), FILM(jménof,rež,rok,země), PROG(jménof, názevk, datum)
select jménof from PROG group by jménof having count(distinct názevk) =
(select count(distinct názevk) from PROG) KINO(názevk,vedoucí, kapacita), FILM(jménof,rež,rok,země), PROG(jménof, názevk, datum)
select jménof,sum(kapacita) from KINO,PROG where KINO.názevk=PROG.názevk group by jménof having sum(kapacita) >=
all (select sum(kapacita) from KINO,PROG where KINO.názevk=PROG.názevk group by jménof ) Objednávka(čís_o, rodč, …), Zákazník(rodč, …)
select rodč, count(distinct čís_o) from O x group by rodč having count(*) >
all ( select count(distinct čís_o) from O
where rodč< >x.rodč group by rodč ) za dotaz lze napsat order by
což znamená uspořádání dle jednoho či více atributů vzestupně či sestupně
lze i různě pro různé atributy; ASC je default;
manipulace s prázdnými množinami, s prázdnými hodnotami a řádky tvořenými prázdnými hodnotami
vede k porušení ekvivalence konstrukcí =ANY() false =ANY(řádky z null) UNKNOWN
=ALL () true =ALL(řádky z null) UNKNOWN
IN() false IN(řádky z null) UNKNOWN Zam(čz, plat, adresa)
select čz from Zam where plat not in (select plat from Zam where adresa=‘Praha’)
select čz from Zam z where not exists (select plat from Zam where adresa=‘Praha’ and plat=z.plat) Zam(čz, plat, adresa)
select čz from Zam where plat not in (select plat from Zam where adresa=‘Praha’)
při existenci jediného pražského zaměstnance s platem = null
vnitřní dotaz dá null; porovnání not in null dá unknown;
celkový výsledek je  Zam(čz, plat, adresa)
select čz from Zam z where not exists (select plat from Zam where adresa=‘Praha’ and plat=z.plat)
ve vnořeném dotaze porovnávám plat s null ..to bude unknown
tedy vnořený dotaz dá  a not exists dá true  celkový výsledek jsou všichni zaměstnanci max(), min(), sum(), avg() = null  porovnání s nimi dá unknown
stejně tak pro množiny z prázdných řádků další podobný případ je
select čz from zam where plat>=all (select z.plat from zam z where z.jm='XY')
select čz from zam where plat=(select max(z.plat) from zam z where z.jm='XY')
pokud neexistuje žádný zaměstnanec jménem 'XY', dá první zápis všechny zaměstnance
kdežto druhý zápis prázdnou množinu count()=0
count(*) řádky prázdných hodnot počítá + ještě podle počtu výskytů
např. pro Tab()
select count(*) from Tab where is null skutečně spočítá řádky s hodnotou null v
count() má dát počet neprázdných hodnot ve sloupci příklad relace:
Jestliže M1={1,2,...,9}, M2={a,b,...,z}, M3={1.5,7.2,..}, pak 5-ární relace nad M1, M2, M1, M2, M3 je ( každá ) podmnožina kartézského součinu M1xM2xM1xM2xM3 , tedy množina uspořádaných pětic
např.{ , , … }.
algebraický pojem relace - modifikován podle tabulek a podle datového typu záznam,
tj. místo číslování sloupců tabulky ( složek kartéz-ského součinu ) - jak tomu bylo na začátku i v relač-ním datovém modelu
se používá selektorů, kterým se zde říká atributová jména každá n-tice ( řádek ) je místo např. < 1, z, 2, q, 7.2 > chápána jako
> či ještě lépe {,,,,},
tj. jako množina uspořádaných dvojic – tedy funkce přizpůsobení jde ještě dále - budeme rozlišovat "deklaraci typu" a "hodnotu daného typu" - tj. v databázové termi-nologii schéma n-ární relace a n-ární relaci nad schématem.
Schéma n-ární relace je konečná množina tzv. atributů, např. S = { ( A1 : M1), ( A2: M2), ... , ( An: Mn) }, kde Ai jsou atributová jména a Mi domény.
(N-ární) relace nad schématem S  (nebo instance relace) je {t; t = { < A1 , m1>, < A2, m2>, ... , < An, mn> }, miÎMi , i=1,...,n}. Relační schémata však musíme upřesnit tzv. závislostmi.
Závislosti jsou obecně formule predikátové logiky prvního řádu vypovídající o přípustných hodnotách instancí nad daným schématem.
Zde se však omezíme na tzv. funkční závislosti. Nechť A= { A1,...,An } je relační schéma, X,Y podmnožiny A. Řekneme, že Y funkčně závisí na X, značíme X->Y, jestliže pro každou instanci r nad A platí:
t1,t2  r: (t1[X]= t2[X]  t1[Y]= t2[Y]). Nechť A={ A1,...,An } je relační schéma.
Nadklíčem schématu A rozumíme každou podmnožinu K množiny A, na níž A funkčně závisí, tj. platí K -> A.
Klíčem schématu A je takový nadklíč schématu A, jehož žádná vlastní podmnožina není nadklíčem A (tj. minimální vzhledem k inkluzi). Každý atribut, který je obsažen v alespoň jednom (potenciálním) klíči, se nazývá klíčový, ostatní atributy jsou neklíčové atributy.
 
Relační schéma je v první normální formě (1NF), jestliže všem atributovým jménům jsou jako domény přiřazeny jednoduché datové typy (hodnoty atributů jsou v rámci databázového systému vnitřně nedělitelné). Relační schéma A je ve třetí normální formě, jestliže pro každou funkční závislost Y->x, kde Y je podmnožina A a x atribut z A, platí alespoň jedna z následujících podmínek:
1)závislost je triviální, tj. atribut x je obsažen v Y
2)Y je nadklíč schématu A
3)x je klíčový atribut. Motivace normálních forem relací
relace zobrazující dodavatelsko-odběratelské vztahy
DODAVATEL_ODBĚRATEL(IdDodavatele | …Data o dodavateli… |
IdOdběratele | …Data o odběrateli…)
kardinality vztahu mezi dodavateli a odběrateli (1,n):(1,n)
množiny dodavatelů a odběratelů nejsou navzájem disjunktní;
relace je pouze v 1. normální formě Poněkud "jemnější" problém představuje relace
ZAMĚSTNANEC(Čís_zam,Jméno_zam,Zařazení, Hodinová_mzda, …),
|_______________| |_________|
kde klíčem je číslo zaměstnance, ale hodinová mzda závisí (funkčně) pouze na zařazení zaměstnance.
Opět dochází k redundanci zápisu, k nebezpečí chyb při aktualizaci a nemožnosti zapsat hodinovou mzdu pro zařazení, které aktuálně nikdo nemá.
relace je ve 2. normální formě Relační schéma A je ve třetí normální formě, jestliže pro každou funkční závislost Y->x, kde Y je podmnožina A a x atribut z A, platí alespoň jedna z následujících podmínek:
1)závislost je triviální, tj. atribut x je obsažen v Y
2)Y je nadklíč schématu A
3)x je klíčový atribut.
motivace normálních forem relací
relace zobrazující dodavatelsko-odběratelské vztahy
DODAVATEL_ODBĚRATEL (IdDodavatele | Data o dodavateli | IdOdběratele | Data o odběrateli)
kardinality vztahu mezi dodavateli a odběrateli (1,n):(1,n)
množiny dodavatelů a odběratelů nejsou navzájem disjunktní
relace je pouze v 1. normální formě poněkud "jemnější" problém představuje relace
ZAMĚSTNANEC(Čís_zam,Jméno_zam,Zařazení, Hodinová_mzda, …),
|_______________| |_________|
kde klíčem je číslo zaměstnance, ale hodinová mzda závisí (funkčně) pouze na zařazení zaměstnance
opět dochází k redundanci zápisu, k nebezpečí chyb při aktualizaci a nemožnosti zapsat hodinovou mzdu pro zařazení, které aktuálně nikdo nemá
relace je ve 2. normální formě Třetí normální formu splňuje např. schéma
ŠKOLA(Profesor,Hodina,Předmět)
kde platí (Profesor,Hodina)->Předmět
(může ještě platit např. Předmět-> Profesor tj. zákonitost stanovená např. školou - takové zákonitosti se často nazývají business rules).
každý atribut, který je obsažen v alespoň jednom (potenciálním) klíči, se nazývá klíčový, ostatní atributy jsou neklíčové atributy
k určení normální formy tedy nutně potřebujeme znát všechny klíče daného schématu a tedy všechny platné (funkční) závislosti
množina všech závislostí logicky plynoucích z mno-žiny závislostí F je uzávěr F, který značíme F+ axiomatizace funkčních závislostí:
W.W.Armstrong (1974)
–“Armstrongovy axiomy“ –
úplný a korektní systém pravidel (tj. lze odvodit všechny závislosti a každá odvozená závislost platí).
minimální množina pravidel
např. Dribas (1982):
reflexivita Y -> X pro X Í Y
pseudotranzitivita X->Y,YW->Z ŢXW->Z
Další minimální množina
reflexivita Y -> X pro X Í Y
kompozice X -> Y, X -> Z Ţ X -> YZ
transitivita X -> Y, Y -> Z Ţ X -> Z
(X -> C)F+  C X+F - algoritmus příslušnosti
Dána množina atributů R a množina závislostí F, XR, CR.
XF+ := X;
Opakovaně (pokud se XF+ zvětšuje) probíráme všechna pravidla z F a vždy, když levá strana některého pravidla je podmožinou dosud vytvořeného XF+, přidáme pravou stranu toho pravidla do XF+;
Pokud C je prvkem výsledného XF+, je odpověď na otázku příslušnosti (X -> C)F+ kladná, jinak záporná.
pro jednoduché schéma můžeme hledat klíče přímo použitím algoritmu příslušnosti –
vytváříme uzávěry podmnožin dané množiny atributů R počínaje od jednoprvkových a sle-dujeme, které uzávěry jsou rovny množině R
výhodou je, že mnohdy nemusíme testovat všechny podmnožiny (např. rozhodně netestujeme nadmnožiny již nalezených klíčů)
R={A,B,C,D}, F={BC->D, A->D, BA->C, CA->B, BD->C}.
A+F={A,D}, B+F={B}, C+F={C}, D+F={D} a tedy jedno-atributový klíč pro toto schéma není. AB+F= {A,B,C,D}, AC+F={A,B,C,D}, a proto AB,AC jsou dva dvouatri-butové klíče
AD+F={A,D}, BC+F={B,C,D}=BD+F, CD+F={C,D}, a tedy další dvouatributové klíče nejsou. Ze tříatributových podmnožin pouze BCD není nadmnož