FormulSiov6 dotazy nad databilzi Podle teorie, ie tE1ko mtrZeme zkonstruovat auto, pokud jsme nikdy Zddn6 neiidili, se nyni strudnd podivime na databLzov€ dotazy, abychom se n6slednE mohli podrobndji zamEiit na vlastn( n6vrh datab6zi. Y t€to kapitole nds dekd piehled vyw6ieni. a spouStdni datal:Lzovich dotazi pomoci formuldiovd orientovan6ho dotazovaciho ndstroje v Microsoft Accessu. RozhodnE nemdm v rimyslu ps6t nEjah.i rryierpdvaiici ndvod k Microsoft Accessu; tento databezornl produkt vyuLivdm jen lako prostiedek pro n6zorndj5i rliklad principir databdzovich dotazi, kter€ budou ziHadem teorie ndvrhu datab6zi, probiran€ v dal5ich ddstech knihy. Piesto se ale pokusim podat alespofi zikladni informace o prdci v prostiedi Microsoft Access,podle nichZ mtrZetelednotliv€ piiklady formuldio'r."fchdotazt prochlzet sami, na sv6m vlastnim poditadi.
QBE:
poEiitky formulSfovich dotazfi
Formuldioud orientouany (formuliiov'y') dotazovaci iazyk umoZiluje vywdieni dotazb pomoci panelu s grafickym uZivatelsh/m rozhranim (Graphical User Interface, GUI). UZivateli databitze stati pro definovdni dot^zu zadat piimo do Sablony dotazu vzorov€ hodnory dat a ryjddiit tak v'.isledky, kter6 od dotazu oieklp6. Druhy piistup k vrTtvdienidotaztt v;tifu5, piihazoud orientouanj dotazovaci jazyk, v ndmZ zapisujeme dotazy lako textov6 pika4t. NejdtleZitdf5impia
poaitaat byly v t€ dobd zcela neznem1, a\e rozhrani poiitadu iiL bylo grafick€. Systr:n zobrazil uriity formr.rlli a databizovy uZivatel do jeho pol( zapsalvzorovd data a jednorl..rch€ pikazy; dnes pouze namisto toho klepneme my5i na n€iak€ tladitko. Takd jazyk SQL letech novinkou. Spolein, : zaiehoi r"lvojem st6larovndZfirma IBM, byl v sedmdesdcych IBM vedla ihen'i wizkum, ktery mdl zjistit, lestli tehdejSiuZivatel€ databiz( preferuji deii:::ci dotazir s ndstrojem QBE, nebo s jazykem SQL. Zdvdr byl piekvapiv6 takor".i, Ze uZilr':c16mdli raddfi ten zptrsob,ktery se naudili jako prvni. Inu, takovd je asi lidskd povahl.
E AJ
q.) L.) !t-
rl L
Zku5enostinicrndn€ uk6zaly, ie je dobr€ znfu obe metody. Formul:iiov€ dotazy se vci:.': dobie hodi pro nahodil6, piileZitostn€poulfuSLnia pro fy uZivatele,kteii jsou zryklejii:--r grafick6 rozhrani neZ na,,buSeni"pikazh z kldvesnice.Ti uZivatel6,kteii se umi v svr.'.,. xi piftaztr orientovat a kteryrn ziryis piikazu nedini probl6.my, dokiiou ale piikaz, .'. dotaz zformulovat rychleji neZ pii ekvivalentnim postupu v grafick€m rozhrani; nar''ic.:-' kazovd orientovan€ dotazy se daji piimo vy,rZitve vhodn6m programovacimjazyce.r.,,. je napiiklad C.
5 q)
E
-fi
c rg N
ZaCin6mes Microsoft Accessem V teto kapitole budeme pracovat s dot^zy nad ukdzkovou databizi Northwind, ktrr., . souddstiinstalaceMicrosoft Accessu.Ldtku tak nefl€pe pochopfte, pokud si vSechnr':--. klady dotazu ihned lyzkou5ite. Potiebujete k tomu samoziejrndzmfndnou ukr'rzk,r" datablzi - tu ale docela snadno nainstalujetez CD-ROM produktr-rMicrosoft Officc. I' paclnou pomoc s instalacinajdete v dokumentaci vyrobce (Microsoftu).Po instalaci\i rosoft Accessu rozbodnd doporu.iuji zkopirovat soubor databLze Northwincl.mdb ckr r::. l-roadresiiiea dotazy si zkouSetnad touto druhotnou kopii. V Microsoft Accessuje vu.: , snadn6 prov€st do datab'izeiak6koli z:asahy,piidernZ zde neni k dispozici Zidnh ict',: souboru, je z,: duchd funkce,,Zpdt". JestliZebudete pracovat nad kopii databS,zov€|lo pirvodni po piipadnd znovu zkopirovat kopii datalr:r,, veni chybE docela snadn6 stadi Ve vEt5indsystdmuMicrosoftWindows se soubor Northwind.rndb nachizi v adresuiii\ I': gram Files\Microsoft Office\Office\Samples nebo \Program Files\Microsoft Office\t l:: ce11\Samples,podle konkr€tni verze MicrosoftAccessu.Poznamenejmeie5te,Ze je r h' ' n€ zkopirovat tak€ tii soubory, jejichZ ndzvy zaihaii na NWIND9 (nebo je5t6 sndze . r. adresdi Samples,ktery neni niiak velky). Tyo soubory sice pro pr6ci s databAzipotiri' vat nebudeme,ale jsou potfeba pro spouSt€niruznych ddstiukdzkovd aplikacea pro \'\'\ ldni piizpirsobenych informaci ndpovddy. Nejsnaz5icestou ke spu5t6niMicrosoft Accessua otevieni databdzeNorthwind je pr, -' poklepdni my5i nad souborem Northwind.mdb v Pruzkumniku Vindon's. Druh'i moTn spodivd v samostatnemspuStdniMicrosoft Accessu z nabidky Start a v zadLoi piikazr.rSoubor - Otevfft; v podoknd riloh Zaiinrime, kter6 vidime na obrdzku J.1, rlyvoldme stc : piikaz klepnutim na poloZku Dal5f. Pot6 ryhleddme adresdi,kde je umistdnakopie.l.,' bdze Northwind.mdb, a soubor otevieme poklepdnim na jeho ndzev. Podokno Li. z obrdzku 3.1 se zobrazuje prdvd pii samostatn€mspuStdn(Microsoft Accessu z nabi.:.. Start; obrazovky v t6to kapitole pochlzeji z deskd verze Microsoft Access 2003, tek. pokud m6te na pod(tadinainstalovanoujinou verzi, mrjrZese jejich piesnd podoba nri::' lisit. V Microsoft Accessu 2000 se napifl
54
a t
I
I
'1I Lt-
rL. )st ri-
l-1. - *
iOffieonline Piiprjii k ruehul'licrolr,fi OfFiceOnline Zkkat nejnrv6jY infofrn€ce6 peu:iti iplikire Arresg
le
Autrmatifky aklualizsyattEnts se!nam n vieBu
]:l
i.,rrlli...
a,-
,.\'
Hledat:
'l-
:o
Plildad:Vytidvro-*vte kopii
_u
'' j,{tt*$i;Ft
trl 'ry
G ftr %
fn g f-.j fQ v
0tevF*t [:opie- N6fthr'rind,ritdb
T
l.lofthwind prd.oyni,mdb
.(.1
N{rthwind,ridb Itlorthwind,mdb
e i-
'{t
NorthwindCS.adp
a
datab6ze?.rrdb
g.
Northwind.ndb
u
databEzel,mdb
i-
tl-
Drmdtnost,mdb
ji oatii..,
:e
novrisoubor,,. *) VytvoHt
)Obr5zek3.1: Podoknoriloh Zaiindme z MicrosoftAccessu2003 -lakmile v Microsoft Accessu otevieme jeden nebo vice databdzovlich souboru, obfevi se v podoknd floh (a take na spodnim konci nabidky Soubor) seznam naposledy otevfenych souboru. Pro op€tovne otevieni stejn6ho souboru stadi klepnout na ieho ndzev v ddsti Otevfft. AZ konednd '"ybereme poZadovanou databdzi a otevieme ii, uvidime r. Microsoft Accessu obrazovku podobnou obrdzku 3.2. Toto je tedy hlavni panel aplikace MicrosoftAccess.Ve sloupci po lev6 stranCokna, pod nadpisem Objekty, mirZeme piep(nat typ databdzovych objekttr, zobnzenych uprostied panelu. Pii prvotnim spu5tEni Microsoft Accessu bude jako vichozf. rybrdn typ objekttr Tabulky; od tohoto okamZiku si irl ale Access pamatuje, jaki typ jsme m6li v databtzi vybrany naposledy, a po nov6m otevien( databize se jiZ vriti k ndmu. Konkr6tnd zde mtrZeme pracovat s tdmito typy objektu: I
Tabulky. Reladni tabulky, kterd obsahuji vlastn( data datal>62e,uspoiddand do i:idkt a sloupcfi.
Z Dotazy. Zde jsou uloZeny databiizov€ dotazy, kter,.im se t6mdi ve v5ech ostatnich reladnich databdzovych syst6mech ikd poh ledy. fi
Formuliie. Accessu.
Grafick6 formuldie pro poiizovdn[ anebo zobrazovini dat v Microsoft
X Sestavy. Piehledn6 sesta\y pro zobrazeni ii tisk, zaloien€ na databdzovych dotazech.
65
{rr V &: {iJ
iJ i
Lli
4ot""*
i
s:
{J dJ
r60rH(y
Ls, 5 i
romultre
i l.! s"rt*v i *d stranru | .1 r,utt" I
fi
"{ut r:i
Uisrote gkE
I-l,iFHEda
i r:i!:
i.:.:i::,.,...a.::j:::..\,.:ii, lrrii:
r
:.F
X
i-lf,aj"€:,g,
,jot"un q41N6vrh lUovi X
i ,-Sl oot".y
!
lto*
tt#.:.,.?. __l
:
l..j
l4xryy
i
i"\ Li Lji
{j
gn*
=' ;. lil= Vytvoiltttsbulku v n6vrhovFm zobrazeni Vytvoilttabulkupomociprivodce
vytvoiittabulkrvloZenim dat ] i {l *rrf f ril!illll
ti : ffi *" i' j ll
ouj"g*utv
I
Preprdvcl
J
Rozpisobjedn6vek
Modulv
!
Obrdzek3.2: Hlavni panel aplikaceMicrosoftAccess S Strdnky. \7ebov6 strdnky pro poiizovdni a zobrazov/anidat z prosfedi webov€ho prohliZeie. , :
S Makra. Skupiny akci, kter€ spoledn€ prov:id6ji urditou operaci, napiftlad otevieni formuliie nebo tisk sestavy. fi Moduly. Kolekce komponent programovacihoiazyka Visr-ralBasic, uloZenfcl-tdo ucelene jeclnotky. produktt pro osobni poc=itade, neni Podobnd,yakoje tomu i u rnnoha yinychdatabdzor'^/ch ani Nliclosoft Access pouhou databtzl nybrZ kompletnim vlvojor"fm prostiedim pro \_l'n'ilienia spou5tdniaplikaci. DatabiLzov€produkry provozovane na velkich, sdilenych poditac=or,ych systdmech,nazyr"anycl-tbd.Znd.seruery,ale takov€to prostrYedipro v1'vo1aplikaci zpravidla nemaji. V,iv'ofaplikainich program{rje vehni sloZit/m t6matem a je jednoznadndmimo rdmec teto knihy; s obiekry typtr Formuldi, Sestava,Strdnka,Makro a Modul tudiZ pracovat r,ubec nebudernea zamdi(mese jen na Tabulky aDot^zy. Z popsan6ho hlavniho panelu muZeme prov6d€t tak€ zdkladni (rdribu datab/azovich objektu, tedy konkr6tnd:
,
X Piidat nor,y obfekt zobrazen€ho rypu. K tomu stadi klepnout na odpovidajici zdstupcena za(6tku seznamu;souddstiseznamuTabulky je napiftlad zdstupceVywofit tabulku v ndvrhov€mzobrazeni.
:
m Odstranit stiivajici objekt: klepnutim na oAzev objekt rlzbereme a pot6 stiskneme
I I
I
: : : I 66
r ',
kldvesr-rDelete.
ffi Zobrazit oblekt (jel"roobsah): pokieperne mySi na jeho ndzev. X
Zobrazit definici neboli ndvrh objekru: klepnutirn oa n:azevobjekt r,ybereme a pot6 klepneme na tladitko Ndvrh v panelu nesroiir hlavniho panelu (je u ndj ikona s pravitkem, tuZkou a trojrihelnftem).
Dialog Relace v Microsoft Accessu Definici a 6drlbu referendnich omezen( mezi reladnimi tabulkami obsluhujeme v Microsoft Accessu z panelu (dialogu) Relace, ktery je zachycen oa obrlzku J.J. Tento panel zobrazime budto klepnutim na tlaiitko Relace na hlavnim panelu nastroifi (ie tieti zpruva a jsou na n1m zn:azorndny tii tabuikT se spojovacimi ddrami), nel:;o zadianim pikazu N6strofe - Relace zi6dku nabidek.
ri{ ; .nl i ,i) di if+
i* -rJ '# 11)
;h, \l
ri] *t -{;
,eJ
> i:
'{* -:l
jI rL,
.: t{; )
ri o h
Obr5zek 3.3: Panel Relacev Microsoft Accessu
l-
).rl h
V panelu Relace se graficky zol>razujf.tabulky (a to v podobd obd6lnftir) a relace typu jednakvice(ryisouzndzorn6nyjakod6ryveden6meziobd6lniky).Forwi1nEvzatojsou n'to relace ve skutednosti refereninimi omezenimi (protoZe relace je sprdvnd pojem jen zkonceptudlnihomode1u),a1eMicrosoftjezdeoznadujejako,,re1ace..'aprotoSeStein6ho pojmu budeme drZet i my. Symbol ,,1" ryznaduje stranu ,,jedna"kaZd€ z relaci, zatimco symbol nekonedna (leLatd osmidka) zndzoriuje stranu ,,vice". Pii irdrZbd relaci mdme , k dispozici ndsledujicioperace: Pro piiddni tabulek, kterd moment6lnd ve sch6matu nejsou zol>razeny,klepneme na tladitko Pfidat tabulku z panelu ndstrojfr (oachdzf se zhruba uprostied a na jeho ikondjezo5'zorn€natabulkaseZlutymznam€nkemplus)azdialoguZobtazit tabulku rybereme piisluSn€ tabulky. i *Potiebujemelitabulkuodstranitzezobtazeni,nejprvejiklepnutimvybereme apot€StiSknemekl6vesuDe1ete'Poznamenejme,Zetimtokrokemsefyzickyneodstrani ani tabulka, ani relace, kterych se tabulka ridastni; trybrani tabulka pouze i zmizi ze zobrazen€ho panelu. 67 t
a
I
Novou relaci piiddme tak, ie mySi pietdhneme prim6mi kli( z yedn6 tabulky nad odpovidaj(ci ciz( klii ve druh6 tabulce. U rekurzivnich relaci musime stejnou tabulku piidat do zobrazeni je5t€ podruh€ a relaci pak definovat mezi obEma zobrazenfmi kopiemi. Na prvni pohled se tento postup mLie zd6t podivnf, ale fakticky je plnd v souladu s technikou pietahovdni poli. I kdyZ je tabulka v panelu relaci zobrazena n€kolikrdt, fyzicky ie v databdzi uloZena stdle jen jednou.
I
Pro odstranEnirelace klepneme na tenkou 6'st jeji spojovaci d6ry a stiskneme Delete. Vybrat relaci v Microsoft Accessu neni riplnE jednoduchd, protoZe zde musirne klepnout skutedn€ ien na tenkou irist iciry ndkdy proto musime nejprve odtdhnout tabulku na jin€ misto panelu, aby se vtrbec tenkd ddst reladni ddry objevila.
I
K ripravd lybrand relace staif poklepat na tenkou ddst jeji spojovac( ddry. Pot€ se objevi dialog, v ndmZ mtrZeme zmdnit rtrzn6 moZnosti relace, napiiklad zapnout nebo lypnout jefi r,ynucovlni jako referendni omezeni (a tim zajistit piislu5n€ omezen( referendni integrify). Pokud je urditd omezeni r.ypnut6, nebude databdzovy system br6nit v ,,nesprdvn€m"vklddln! aktualizaci ani odstra[ov6ni dat, piestoZe tak mtZeme vytvoiit ,,osiiel6"hodnoty ciziho kliie (,,sirotek"je takov| hodnota ciziho kl(de, ke kter€ neexistuje piisluSnd hodnota primdrniho klide v rodidovsk€ tabulce). Jakmile se ale v dceiind tabulce nachdzeji osiiel€ zeznamy, nedovoli jil datab6zovy syst6m op€tovn6 z pil)t( referendniho omezeni.
u \F
5 -v :l a)
o (' c.) N L
-o N '0) cL \d
z
JestliZepanel relaci uzavieme nebo minimalizujeme, uvid(me opdt hlavn( panel Microsoft Accessu.
Ndvrhov6 zobrazeni tabulky v Microsoft Accessu V hlavnfm panelu mirZemetabulku vybtata otevfft prosn/m poklepdn(m naiejin6zev.T(m se tabulka zobrazi ve wichozim zolxazeni, naz'inao€m Datovy list. V n€m se data tabulky - jak vidime na obfirzku 3.4 - zolxazuji ve zndm€m tabulkov€m formdtu a mtrZeme je odtud tak€ piimo aktualizovat. Nen( zde ale 26dnd funkce pro vrdceni zpdt - jakmile piesuneme kurzor z jednoho iddku do jin6ho, zm6ny se definitivn1 zapiii do databdze a nedaii se snadno vrdtit.
Obchodniz6stupce pro obchod Andrew Viceprezident 3 Lewding Janel 0bchodnizistupce 4 Peacock Margarel Obchodni_zdstupce 5 Buchanan Stryn 0bchodni iedatel
S!9inq Doktor
6 7 I 9
Pan
2 Fuller
Suyama l4ls Callahan Dods;odh
lvichael Obchodnlzistupce Roberi Obchodnizastupce Referenttuzems*6ho obchodu Laura Anne 0bchodniz6slupce
Pali Pan Pal. Sledna Sledna
08 12l96q 1 9 . 0 2 12 30.08.1963 19109 1958: &.m 1955 tS63 OZ.OZ 2905.1950 09.0'1.1958 0207.1969
Obr6zek3.4:Zobrazeni v datov6mlistu tabulkyZam6stnanci
01 05.1992 507- 20thAve.E 1 4 0 8 . 1 9 9 2 9 0 8CWa p { a l W a y 722MossBayBlvd 01U4.1992 41'10 0305.19€3 OldRedmond 17.10199314GaretlHill 17101993Coventry House 02.011994EdgehamHollow 4725- 11thAve.N.E 05.8.1994 15.11.1994 7 Houndslooth Rd.
na(: Lbul'Ltzc-
rcflnici tabulky- zobrazime klepnutfm na tlaaitko Ndvrhov€ zobrazeni v panelu ndstrojir rpdt piipominlrm, 2e jeho ikona obsahuje pravitko, tuZku a trojrihelnik). Toto ndvrhov€ tl';razen(pro tabulku Zamdstnanci vidime na obrS.zku3.5.
i)-j' :llc: ilipi
rrr'
-j'i:',,i.f.,:
.
-
d
x
telc-
N '(a
;inrc cexc lexr text (ex( Datum d aas Datum a ias cerc cexr texl cexc texl cexc text texb memo d6lo
) o L lt
isc IOLIT Ilteo\1' o2c :izi )Lll-
.IJ
ro
PrEcovnizaiazenizamditnance, Oslovenipii pozdraw.
fi 7
(s c
Adreia nebo P,o. box. Regionbydliitd zamEstnance.
N
ro
TeleFonnii-6lovietnd smdrovdhofsle zem6a n6rodnihosmErovdhodsla. aislo interni telefonni linky. Fotografie zamEstnan.e. ZSldadniinformaceo zamEstnanci, Nadi'Eeni zamEitnance.
lta-
VldtNsti
.qJ
'ro 5
pole
il dlouh€cel6 irlo plrirtek
;oft
P
rr u
CislozamEstnance dno (bez duplicity) Nd?er pole nitrie bit dlouhi nejvi;e 64 znakl vieln; m8zer. ah(ete-li zis[it infornace o ndzvechs!li, rtiskndte ld;vesu Fl,
t/6vrhov6 zobrazeni. F6 - Piepnutlrcdoken. Fl - Noov;dc.
'fnt
Obrdzek3.5: Ndvrhov6zobrazenitabulky Zamdstnanci
1.,.
te
7,e
d
i
I)opisovan€ ndvrhov6 zobrazeni tabulky uvddi v Microsoft Accessu rirzn€ informace r tabulce: I
Ndzev pole. Pojmenovdni piislu5n6ho datov€ho sloupce.
I
Datov'f typ. Typ dat ukl:idanych do sloupce.
I
Popis. Y'istiin'i popis sloupce, zpravidla definovanf datat>Lzovimadministrdtorem.
I
Velikost pole. Podryp ,,hlavniho" datov€ho rypu. Obecni datovi typ eislo mtZe bft napiiklad upiesn€n jako Dlouh€ cel€ dislo nebo Cel6 dislo.
I
Je nutno zadat. Indikuje, lestli je danf sloupec povinny, nebo nepovinny (v rakovdm piipadd smi obsahovat hodnoty NULL).
I
Indexovat. Vyznaduje, jestli je nad sloupcem definovdn index.
f
Prirnirni klii. Je zndzoro€n pomoci mal6 ikony klide vedle ndzr.rr pole (nebo ndzr.u pol0, kter6 rvoff primdrni klid.
Je vdm ale doufdm jasn€,Ze vSechno,co na tomto panelu vidite, jsou metadata.Definice tabulky se ski6dd z mnohem vice voleb, nel na kterd jsme se podivali, piidemZ Microsoft -{,ccessumi docela ,,chytie" sk4ryat nepotfebnd volby a zobrazovat jen ry podstatnd. V5imndte si tak€, Ze s pohybem kurzoru z jedn€ volby na druhou se v poii u prav6ho dolniho rohu automaticky m6ni modie r,ypsany text ndpovEdy.
69
ri
VytvSieni dotazfi v Microsoft Accessu
n: qJi vi -\l
,' Ff ] *l u1 4:i
>l ..: l*l
r
Jak isme si yiZiekli, uloZend dotazy se ve vEt5ind databiz( naz'i-vajipobledy, ale protoZe pohled je fakticky clefinor,:in jako uloZenf databS.zotydotaz, je i oznaieni,,dotaz' v NIicrosoft Accessufonndlnd spr6r,n€.KaZclopddndale nezapomeite. Ze v dotazu nejsou nikdr trloZenai6llo5, data - ta jsou umfstdna poLlzev tabulk;ich. Sezn:rmv5ech clotazt uloZenyclr v databdzir,y'oldrne klepnutirl.rna tlaiitko Dotarzy po levdrn okraji hlavniho panclu; rysledek je zachycenna obr:izkr.r3.6.
nl Tll fl $l
'ai i"i )\! /l
Obrdzek3.6: Okno Dotazv v MicrosoftAccessu V MicrosoftAccessumdme sice k dispozici hned ndkolik moZnosti\yrvofeni nov€ho dotazu, ale pro zaidtedniky je nejieclnodu55fzaaft poklepirnfm na poloZku Vywoiit dotaz v ndvrhov€rnzobrazeni. Tim otevierne ndvrhov€ zobrazeni nov6ho dotazu, kter6 je na obrdzku 3.7 znlzorndno i s dialogem Zobrazit tabulku. Dialog Zobrazit tabulku otevird Nlicrosoft Access u kaZcl€ho nov€ho dotazu a nal>fui v ndnr rnoZnost\ybreni tabulek anebo dotazo, na kterlich bude norli dotaz zaloien (neboli tabulek a dotazi. z nichl bude noW dotaz derpat podkladovd data). Po piidani tabulek a zavien( dialogu Zobruzit tabulku jiZ mfrZeme v panelu nivrhovdho zobrazeni zapsat specifikaci poZadovan6ho dotazu. Na obr;izku 3.8 vidime ndvrhovd zobrazen(dotazu s piidanou tabulkou Zikaznici.
70i l
S4*!qF Ake n+cs+ -:t : *1 ', :.1]'.-,
oze trc
r* t.} {$ iu
rcte: Tabulka: RAOt:
Zobrazitl Krt€ria: nebo;
X
c t; fr 'gJ ;>
: u
Obr6zek 3.7:Ndvrhov6 zobrazeni dotazu, Oi"ffi
A-
tz
):k u Obrdzek3.8: Ndvrhov6zobrazenidotazu po zobrazenitabulky
71
a t^ Q) L' (J
P 9-
Panel ndvrhov€ho zobrazeni dotazu se sklid4 z ndsleduiicich ddsti: t Na volnd ploSe v homi d6sti panelu (se Sedfm pozadim) lsou graficky znlnomEny jednotlivd zdrojov1 tabulky anebo dotazy a tak6. relace definovan€ v dotazu. Mezi tyto relace se automaticky kopirulf (dddO tak€ v5echny relace vytvoien6 mezi vlastnimi tabulkami.
vt L
.v '5
Jednotlivd sloupce v miiice ze spodni idsti panelu reprezentuji datov6 sloupce, kter€ se budou vracet do v.isledn6 mnoZiny, generovan€ po spu5t6ni dotazu. i.adky v t€to miizce pak ryjadiuji ruzn€ volby, aplikovand na odpov(dajici sloupce (piiklady qmZiti si uk6Zeme v ndsledujicich d6stechtextu): Pole. Specifikace zdrojovdho sloupce. Sem se normdlnd promitd ndzev sloupce z tabulky nebo dotazu, ale mtZe zde b'it uvedena tak6 konstanta nebo viraz, podobnd jako ve vypodtech zn6m'ich tieba z tabulkov6ho listu.
N P
'o .(o P
i-'l
n :-l
Tabulka- Ndzev zdrojov€ tabulky nebo dotazu pro danf sloupec. fr.aait Urdeni typuiazeni sloupce (VzestupnE, Sestupn€,Neiadit). Zo.bftzilt ZaikrtAvaci polidko, kter6 ovlddd zobrazeni sloupce. Nenfli toto polidko za5krtnuto, mfiZeme sloupec pouiivat pii formulovdni dotazu, ale do vysledkir dotazu se nepromitne. Kriteria" Tato specifikace urduje, jake iedl
Po dokondeni specifikace dotazu mirZeme klepnout na tladitko Spusttt s dervenfm vykiidnftem (je zhruba uprostied hlavniho panelu ndstrojir) a zobrazit tak jeho yfsledky ve formdru datov6ho listu, podobnd jako tomu bylo na obrdzku 3.4. Do ndvrhov€ho zolxazeni dotazu se mtrZeme vrdtit prost'y'm klepnutim na tladitko Nrivrhovd zobtazeni (hddejte, jak vypadS,jeho ikona? prav(tko, ruZka, trojfhelnft). U vEt5iny dotazir mtrZemepifmo v zobrazeni datovdho lisru provdddt i aktualizace dat, kter6 se okamZit6 promitaji do zdrojou.ich tabulek dotazu. JestliZesloupec ve v'fsledcich dotazu nelze piimo promitnout do jedin6ho sloupce zdrojov€ tabulky - napiiklad se lednd o vypodteny sloupec - pak jej ve v'y'sledcich dotazu nemirZeme ani aktualizovat. Ve zbytku teto desti kapitoly si na s6rii piikladir ukdZeme rtznd moZnosti prdce s dotazy v Microsoft Accessu. U kaZd6ho z piikladfi si piitom iekneme, jalcfch r{sledktr chceme dosdhnout a v jakich krocich mtZeme vyrvofit specifikaci dotazu v ndvrhov6m zobrazeni. Na konci piikladu nds dekd v.islednd podoba dota^) v ndvrhovdm zobrazeni a tak€ jeho vfsledky v datovdm listu, vznikl€m po spuStdni dotazu.
Piiklad 3.1: Vfpis viech ziikaznik0 Nrisledujicim zpirsobem vytvoffme vypis cel6 tabulky Zitkazntci (tedy v5ech iddkir i v5ech sloupct):
1 . V hlavnim panelu klepndte na levdm okraji na poloZku Dotar.y a pot6 poklepejte na poloZku Vytvoiit dotazv nivrhov€m zobrazeni.
lteny Mezi .last-
Nyn( v dialogu Zobrazit tabulku provedte ryro operace: -"1 Klepnutim na poloZku ZLkaznki r.yberte tabulku Z|kaznici. -1 Klepndte na tlaiitko Prfidat. -'l Klepndte na tladitko Zaviit.
pce, idky
N
3. Nakonec v panelu ndvrhov6ho zobrazeni poklepejte na hvdzdidku v Sablond tabulky Ztrkaznici(nachl,zi se pii hornim okraji tabulky).
rPfl-
rpce 'faz,
ilotovy panel s ndvrhem dotazu a r.ysledkyspuSt€ndhodotazu vidirne na obrdzku 3.9
-c {]J 7 7
c N (! # fi
oto do
o
'o (1
:6 E Lt-
iJ
fs}fr
tff,!ry
\66at Fffilt
Zobrd
;4- d if :3 )?
rlaqy
U6@
g{F
lrholrd!
Ana TruJillo Emparedadosy helados Ana Tru.Jillo AnlonroMorenoTaqueria Aroundlhe Hom Berglundssnabbkop BlauerSee Delikatessen Blondelpereet lils BtilidoComidaspreparadas Madin Sommer
v t-
CehtrocomercialMoctezuma
zaffi: l{l ,lJ-----T le$Ebafni
pdttmkovi
i::;.:,:ri-.:trr:,ri,jr,ii:
. - {i x
6 + -r ' e,,itrili'{,;'6: lfi ) ,^:J..3' s
} lxl}*l
lod odwzsi
od new
Forst' 24.p| C/ Ar 1 2 ,t u 23 Ts Faunl Cerit Siena Haupl 8e*€
-rJ
zs1 firy
FranciscoChanq
Obchodniz6slupce lvlaJrte{ lvlaJitel Obchodnizastupce Vedoucindkupu Obchodniz6stupce Vedoucima*elingu l\,lalilel MaJilel Hldni Iaetni Obchodnizdstupce Prodelce Vedo!ci maftelingu lvlaJilel Obchodnizastupce Obchodni?6slupce Vedo!ci n;kup!
z6lazr*a.
Obrdzek3.9: Piiklad 3.'1,rnipisviech zdkaznik0:nahoie ndvrhov6zobrazeni,dole uisledky dotazu
:f
; u
Piiklad 3.2: Kter6 sloupce zobrazit V dalSim piikladu iiZ nebuderne chtft zobrazot'at vSechny sloupce tabulky, ale vybererne ien ndkter€. Konkr6tnd budeme chtit pro vSechny zlakaznky (tedy pro v5echny i:idkyl zobrazit sloupce K6d zlkaznka, Firrna, Mdsto, Oblast a Zemd; dotaz r,1,tv-oiimeniZe r-rvedenyrn postupel.rl.
-r
Vyjdeme z ndvrhor'6ho zobrazeoi dotazu podle obrdzku 3.9 a dotaz d:ile upravirne: 1. Nejprve odstranime stdvaficf specifikace dotazu, podle nichZ se maji zobrazit vSechny sloupce: klepnerne na rnaly Sedy obddlnik nad nizvern pole ,,Zlkaznici1' (tim se pod celym sloupcem rykresli iernd pozadi) a potd stisken"rklir.esy Delete cely sloupec oclstranfme.
a:
N fr f:
E
r
10(b0.
UFgry
eobilrt
Vlo&
q*a
-.d1
Shot
qas
$fpq!rd!
't !r
tr.
u+
'
''| ": ::' .f...
'r J'
dx v
f
6dZ6hzn*a Tah-d€l Eadt: g
Zobrdtl Krtdtul
:j1
:dno.
:*4' dA
ANTON BERGS BLAUS BLONP BOLID BONAP
TU
0rtvy
lobrc
Jf?
tto+
Eh*
6 i,r.,,
VJ
Z&te.
i:llil
U6*o,.
qbD
xdpor6d.
t; Q ! A:r
Alfreds Futterkisle Berlin Ana TrujilloEmparedadosy helados M e x i c oD F AntonroN4oreno Taqueria M e x i c o0 F . Londin Bergllndssnabbkop LUIEA BlauerSee oelikalesseil N'lannheim Blondelpere et fils Strasburg B6lidoComidaspreparadas tvladrid Bon app Marserlle Tsawassen Londin CactusComidaspara llevar BuenosAires Mexico D F. Ben Seo Paulo Londin C,chy Nanles
::- ....::
:,|:.
::r,:
. - dl x
r'..3,3. NEmecko N4Pxiko Mexrko Velk, Brilanie Svddsko Ndmecko Francre Spandlsko Francre Kanada Velk, Brit6nie Argentina Mexrko Svfcarsko Erazilie Velka Bnlanre Ndmecko Francre
obr6zek 3.10: Piiklad 3.2, zobrazenivybranlch sloupc0:nahoie ndvrhov6zobrazeni, dole uisledky dotazu
74
3me lky)
azll :i.*"
2. Nyni pro kaid.i z poiadovanych sloupcu (K6d zLkaznika, Firma, M€sto, Oblast a Zemd) poklepejte na n:azevsloupce v tabulce, zn1,zorndn€v horni ddsti n:ivrhov6ho panelu. Druhd moZnostje pietdhnout jednotliv€ sloupce ze Sablonytabulky do miiiky ve spodnf d6sti panelu pomoci mySi. Upraveny panel s ndvrhem dotazu a rysledky spu5tdn6ho dotazu lsou zndzorndny na obr6zku3.10. t\
Piiklad 3.3: Razenivisledki Ridky dat se z dotazu v reladn(ch databS.zovychsystdmech nevraceji v iidn€m definovandm poiadi, pokud si iazeni r.lrsledkt zvl65t nevylAddrne. Microsofr Access definuje poiadi pro seiazeni r,..isledkirdotazu pomoci specifikace i.adit. 3
ry*"r J-d:l
q**
er&d
ft*
q*a
S,*!t€
6--:.34-
'...,, r'r.
Akb it6po!6da -r..
J'1".'1E';.
-* fi: .{*/ fI -f:a f$
r.,f .,.r::,:J.,r.: r - 6r x
J-l'"9
* * t .(, O E r
(
c
*
ilTabulkal Raditr Zob.azitl Krit6rlal nebol
:J
Sg,iv
fu6.t
e*
'db:F
Ew{t
Z&rAy
U6@
gbE
NdFlgdi
:i:i.
:.r:;",.r:-:iii,i,,,''
r - at x
: S , - - , . d _ . 1 - : i - i ? - d . - r * : - : - _ . , j , . ! .111 : t S . ' j t i r a r , ! , i . , 9 ,
CACTJ
Old Wofld Delicatessen Vafeljernet Galefa del gasir6nomo LILA-Supermercado MaqazziniAlimentariRiuniti Alfreds Fulte&isle Chop-sueyChinese Save-a-lolfvla*ets Folk och fa HB KdniglichEssen NlaisonDewey CactusComiias paralleuar
OCEAN
Oc6anoAllanlicoLtda
Albuquerque Anchorage Arhus Barcelona Barquisimeto Bergamo Berlin Bern Boise Bfacke Brandenburg Brusel BuenosAires BuenosAires BuenosAires
USA USA D6nsko >panersK0 Venezuela helie Ndmecko bvyt arsh0 USA N d m e . ko Belgie Argentina Argentrna Argentrna
Obrdzek3.11: Piiklad 3.3, iazeni rnisledk0:nahoie nSvrhov6zobrazeni,dole r4isledky dotazu
o \J .|J
H-
.(l
'.:
poi: Budeme tedy pokradovat v piftladu 3.2 a rysledky dotazu seiadime ve vzestupnem kroky: prov€st ndsleduiici znamenl to konkr6tn€ a zem1; oblasti di podle mEita, 1. Na i6dku oznadenemi.adit, ve sloupci pod poloZkou Mdsto, klepndte do ptizc n6ho mista a z rozeviraci nabidky vyberte poloZku Vzestupni' 2. Tot€Z provedte pro sloupec Oblast; namisto klepnuti do rozeviraciho seznanrt stadi piitom napsat p(smeno V (jako Vzestupn6) a stisknout Enter' 3. A potieti opakuite steini postup i pro sloupec ZemE' 11 Panel s ndvrhoufm zobrazenim dotazu a vfsledky po spust€ni isou uvedeny na obrdzku 3
N P
'ql
.(o P
Piiklad 3.4: Pokroiil6 iazeni
podivdme-li se nyni na ufsledky z obrlzku 3.3, vidime, Ze pokud isme zadali iazeni neiprve podle n6zvu m€sta a pote podle oblasti, nem6 to t€m€i Zddn'y'smysl, protoZe druhe ndzvtt iazeoi by se uplatnilo jen tehdy, kdyby ndhodou existovala dv€ mEsta stejndho piesny' piiliS logicky neb'fu5. ie piirozenli protoZe A iazyk v ruzn1;choblastech di zemich. (v tourt
stisknEtekldvesu Delete). jednou pii2. V tabulce Z1tkaznicipoklepejte na poloZku Oblast a tim sloupec ieStd dejte do dotazu. 3. Tot€l ud€leite pro poloZku Misto' (tedy ve sloupcich uprauo o<) 4. Nyn( v novE piidanych sloupcich oblast a Misto iazeni' vzestupo€ uraete ZemE) sloupce polidko 5. U obou novd piidanych sloupcir (Oblast a Misto) vymaite zalkttS'vaci Zobrazft. upraveny panel dotazu a jeho vysledky si mirZete prohl6dnout na obrlnk]u 3.12.
>fa-
a\j t
11
a:
{::
Tabulbl Raditl
Y1
Zobradl Krit6!ial
"?"1
c h€
I't {U
Vu
4 n
lto tzJir
L'
:j
gdld
6Fgrt
- . 1. J : I tle rft va to 10
tit li-
o
rdoq
Eiln&
ztaEy
Ranchogrande
pEtiznakovi kld odwffii
!6fuF
glsE
il@Ed.
n
OCEAN Oc6anoAllanticoLlda. CACTU Caclus Comidasparallevar MAISD lvlaisonDewey SUPRO Supremesdelices HANAR HanariCarnes OUEDE Oue Delicia R CAR R cardoAdocicados GOURL Golrmet Lanchonetes WELLI Wellingtonlmpodadora OUEEN O u e e nC o z i n h a FAIiIA FamiliaArquibaldo COMMI ComErciolvlineiro TRADH Tradigio Hipermercados VAFFE Vafeljemei SIN,lOB Simonsbrstro :&Rm, l{ | l[-------T t | ]l |txl, er t&nffi61i
d
!.brd
J-l?rd
.ru :] r L
BuenosAires BuenosAifes BuenosAires Brusel Charleroi Rio de Janeiro Rio de Janeiro Rio de Janeiro Campinas Resende S e oP a u l o Seo Paulo 560 Paulo Seo Paulo Arhus Kodari
RJ RJ RJ SP SP SP SP SP SP
Argenlrha Argenltna Argenlna Belgie Belqie Eezilie Brazilie Bfauilie Bezi|e Bra?ihe BraziIe Brazilie Bra?ilie Brazilie Dansko D6nsko
LL
od nezvu F my 2iikaz*a-
Obrdzek3.12:Piiklad 3.4, pokroii16iazeni: nahoie n5vrhov6zobrazeni,dole vfsledky dotazu Poznamenejmeje5t6, Ze vdt5ina svdtovych jazykil se zapisuje a tte zleva doprava - proto i u tabulkovych rlrpisu piirozend oiekdvdme iazeni zleva doprava, tedy nejprve podle nejlevdj5ihosloupce. JaklTkolijiny zpusob iazeni je neobrykly, nepiirozenf, a dasto bjv1, projer-em nevhodndho ndvrhu. Kdybyste ale ndkdy potiebovali rysledky seiadit jinak, budere uZ vdd€t. iak na to.
Piiklad 3.5: Kter6 iiidky zobrazit Zatim jsme v kaldlm z dotaz'brypisovali vSech 91 ziznam'b tabulky Zitkazoici. Pokud ale r'Sechny najednou zobrazovat nepofiebujeme, je takor,fto r"/pis zbl,teinym pl'iw1'n(m systemovimi prostiedky, zeim€na pokud u r./sledku navk poZadujeme iazeni. Dejme tomu, Ze budeme chtit r,1wo1at zLznamyjen tdch ztrkaznkb, jejichZsidlo se nachdziv Lon-
77
a
L'
F
= .)) N d F
,L
P
dyn6, ve Velk€ Brilinii. K tomu v panelu n6vrhov€ho zobrazeni dotazu qnrZijeme iddek Krit6ria. Vyjdeme opdt z piedchdzeiicitro piildadu, iislo 3.4, v ndmZ mnoZinu vypisovanfch iddkir omezime takto: 1. Na i6dku oznaden6m Krit€ria do nejlevdj5iho sloupce M€sto napi5te texr Londfn. Poznamenejme,Ze Microsoft Access pfi vybdru dat v dotazu nerozlisule velikost pismen, takZe stejn6ho vysledku dosdhneme i ztrpisemtextu LONDfN nebo londfn. 2. Nyni ve stejnem i:idku do sloupce ZemE napi5te Velk6 Britr{nie. Ddvejte pozor - krit6ria u sloupcir MEsto a 7*mE musite zapsat do stejn€ho iddku, protoZe poZadujeme navrS.cenijen t6ch iddkir, u nichZ je mdsto rovno ,,Londyn,,a zdrouert zemE ie rovna ..Velkd Britdnie". Panel s upravenym dotazem a v'.islednou mnoZinu iddktr vidime na obr6zku 3.13. VjimnEte si, Ze Microsoft Access zapisuje u hodnot krit€rii uvozovky - piestoZe je pii prvotnim zdpisu lynechdme, Access je doplni, nebod se jednri o sloupce znakov€ho form6tu.
Obrdzek 3.13: Pifklad 3.5, zobrazeni vybranyichiiidk0: nahoie nSvrhov6 zobrazeni, dole rnisledkydotazu
idek
Piikfad 3.6: SloZen! uib6r f6dk&
idkfi
Dejme tomu, Ze se nyni rozhodneme kromd viech zLkaznftt z Londyna lybrat je5tE r-5echnyzikazniky z americk€ho st:itu Washington. Takto definovand krit€ria jiZ musime r-panelu n6vrhov6ho zobrazeni zaps t na notf,iddek.
I)nvelirebo
Iako obrykle pokradujeme v piedchdzejicim piikladu a tentokrdt rozSiiime vysledky o dal5i zdkaznil
)zQf
toZe dro5imnim
1. Na nory iridek v panelu krit€rii napi5te pod nejlevdjS(sloupec Oblast text WA. 2. Ye stejn€m (tedy druh6m) i6dku krit€rii pod sloupec Zem6 napiSte text USA. Celou skupinu krit6rii mtZeme nynf piedist takto: Vyber iddky v5ech ztrkaznki, u kterych se M6sto rovnd ,,Lond'in" a zLrovei zemd je rovna .,Velk{ Britdnie", a navic i:idky vSech ztrkaznk'b, u kter,.ichse Oblast rovn6 ,,VA" a zdrovei zemd je rovna ,,USA". Hotovy panel s ndvrhem dotazu a datovi list s vysledky jsou zn;izomEny na obr/rzku 3.14.
'tt '$ f1
rs
#
i{
nl
c
3
tl-
ObrSzek3.14: Piiklad 3.6, sloienf v'ib6r i5dk0: nahoie ndvrhov6 zobrazeni, dole v'isledkydotazu
;
E
E 5
Piiklad 3.7: Operdtor Neni rovno IJ
v
!F
a)
Zatim isme pracovali jen s takol".imi lyhleddvac(mi krit6rii, kterd uvaiovala operdtor porovndni ,,jerovno" (=). K dispozici mdme ale i n€kolik dalSichoperdtoru - podivejme se do ndsledujic(tabulky:
Oper5tor .q :
MenSl neZ Meniineborovno
<=
.:
Vdtii nei
N
"ts
Popis
>=
VdtS[neborovno podleANSI) Nerovnd se(standardniform6t
n
c
CJ
Nynise tedy pokusime vyhledat zdkazniky,kteif nejsou ani ze Spojenychstrittr(USA),ani z Velkd Britdnie. Tady jsrne opdt s piirozenym jazykem na Stiru: kdybychom r,ySli z piedchoziho dotazu a oplt zadali logicky operdror OR (nebo), tedy <>,,USA"OR <>,,VelkdBritdnie", rrybrali bychom riplnd v5echny iidky tabulky - kaZdy iddek se totiZ nerovnd budto iedn€ z uvedenych hodnot, nebo alespoi t€ druh6. Musime proto zapsatoperdtor AND, protoZe u.isledn€ iddky se maji ve sloupci Zemd nerovnat textu ,,USA"a zdrouefi se mali nerovnat textu ..Velk6Brit6nie". Upravfme tedy dot^z z piikladu 3.6 a ryhled'ime zitkazniky, kteif nepochdzeji ani z iedn€ uvaZovan6zemE: 1. Z ptvodn( verze dotazu odstraite v5echnadosavadnikrit6ria. 2. Do i:idku Krit€ria Britinie".
pod sloupcem Zem€ napi5te text <>,USA" AND <>,Velki
Pozrndn€nyndvrh dotazu a jeho vysledky jsou uvedeny oa obrdzku 3.75.
Piiklad 3.8: Spojovdni tabulek Sludovdn( dat z vice neZ jedn6 tabulky se v reladnich datab6,zichoznaduje iako spojoueini tabulekQOIN). V tomto piikladu budeme chtit zobrazittii sloupcez tabulky Zdkaznicia tir sloupce z tabulky Obfedn:ivky - a to ke kaZd€ objedndvce, kterou si zfrkaznk podal u spolednostiNorthwind. ProtoZemezi tabulkou zdkaznkti a objedndvek je definovdna relace rypu jedna k vice, znamentr to, 2e se u zitkaznktr s vice podanymi objedndvkami budou stejn6 informace opakovat na ndkoiika iddcich r.".isledku. Pochopit spojovdni tabulek je velmi dirleZit6 pro pochopeni dinnosti reladnich datab6z( jako takovych. Stejndjako jsou relace typu jedna k vice (implernentovan6jako referendni omezenO zirkladoim stavebnim kamenem samotnych reladnich databLzl fe i spojovdni tabulek v relainich databdzich zlkladnim stavebnim kamenem dotazt,. Nyn( bude nejlep5i zadit s fplnd nor"/m dotazem, na kter€m si ukdZeme,jak se vlastnd dotazy se spojenim tabulek vywdieii. Uzaviete v5echna oteviend okna dotaztr a vradtese zp€t do hlavnifro panelu Microsoft Accessu, kde budou zobrazenl dotazy jako na obrdzku 3.6 v piftladu 3.1. Pii vl.tvoieni nov6ho dotazu se spojenim tabulek budeme postupovat ndsledovnE: 1. Poklepdnim na poloZku Vytvoiit dotaz.
80
dotazv ndvrhov€m zobrazeni lywoite nol"/
rAtot rjme
ru
'sil a1
re
ft *a
7
r r,l anl ecl3ridto {D, taii
ne
ke
v
n
X ^{J
:-i
d.
f"A*
il:l
rirary An"n* $d
Z&By
Ranchogrande Oc6anoAilintico Llda. CactusComidaspara llevar l\4aisonDewey Suprqmesd6lices Hanar Carnes Qle oelic a RrcardoAdocrcados GoufmelLanchoneles Wellingtonlmpodadora QueenCozinha FamiliaArquibaldo C o n e r c r oM , n e l o TradiQeo Hipermercados VafieJernel Simonsbistro le&Fmaini pdtlznakovikii odv@i
r::ri .'i: -i...;i.;:ri:
Ua6bd. gbE {dpogr&
j.r lllLf;t!
Jl?rii.i..r,i...
z,iznm,r{ I' ll------J..1l1ll5j
n, ii al
eil{S
. - tt x
j-,44i, )^i15,!-,
'1]'
's a
BuenosAlres BuenosArrcs BuenosAires Erusel Chaieroi Rio de Janeiro Rio de Janeiro Rto de Jane[o Camphas Resende Slo Paulo Seo Paulo S,o Paulo Sao Paulo Arhus Kodali
Argenltna Argenlrna Argentrna BeJgie Belqie Brazilie Brazile Brazilie Bfazilie Brazilie Brazilie Erazilie Brazilie Brazilie D6nsko Densko
L
.",
z sr
od n6?vufimy z6ksrdG.
ObrSzek3.15:Piiklad 3.7, operdtor Neni rovno: nahoie nSvrhov6zobrazeni,dole 4isledkydotazu
ta 11
al
ri |l
2. Z dialogu Zobrazit tabulku piidejte do dotazu tabulky Zdkaznf.cia Objedndvky. ProtoZejsme si iekli, Ze v dotazechpopisujeme,co potiebujeme ud€lat, a ne jak, ani zde nez6leZ( na poiad( obou piiddvanfch tabulek. Po dokondeni klepndte v dialogu na tlaaitko Zavfit. 3. Nyni si v panelu n6vrhov6ho zobrazeni dotazu vSimndte, Ze se zde krom€ obou tabulek objevila tak6 spojovac( i5,ra mezi nimi. To znamentr,Ze Microsoft Access jii vi, iak spolit f:idky obou tabulek - podle hodnog K5dZdkaznka zapsan€ v tabulce Objedn6vky lyhledd odpovidajici zS.znamtabulky Zdkaznio. Dd se iici, ie dotaz zdddil relaci, kterd ie mezi ob6ma tabulkami definovdna v panelu Relace. Bez t€to specifikace spojeni tabulek bychom ve vysledku dostali kandzsky souiin obou tabuiek, tedy vSechny moZnd kombinace iidko z jedn€ tabulky a i1.dku z druhd tabulky (tak je definovdn ,,soudin" dvou tabulek - podet prvktr vysledktr je mimochodem skutednd roven soudinu obou poaffi fedkt). My ale
81
:}
rysledky, podle nichZ by kaldi' z'trkaznk zdAnlivE poddval kaZdou jednotlivou obledndvku,samozieimdnechceme,a proto si rddi nechime od MicrosoftAccessu .,poradit".Vlastnostispofenitabulek rozebirdtre podrobndji v ndsledujicichpii kladecl-r.
s.l : Lll
\]
4:
.r-, :
-:
g l
4. V tabulce Ztrkaznki poklepejte posrupnd na poloZkv K6dZdkaznfl<4 Firma a ZemE, a t(m je piidejte do zobrazenych rysledku dorazu.
L * :
5. Z tabulky Objedndvky piidejte do dotazu (poklepdnim my5i) sloupce DatumObiednr4vky, DodatDne a Dopravn€. Hotovy panel s ndvrhem dotazu a rysledky spu5t€ndhoclotazuvidime na obrS.zku3.16.
<"'
e'
.*"1 N F.
o
nl
C .fi; V)
>l
/ l i
Alfreds Futlerkrste AlfredsFutlerkiste AlfredsFutlerkiste Ana TrujilloEmparedadosy Ana TruJillo y Emparedados Ana Tru.lillo Emparedadosy Ana TrujilloEmparedadosy AntonroMorenoTaqueria AntonioMofenoTaqueria AntonioMorenoTaqueria AntonioMorenoTaqueria AntonroMorenoTaqueria AntoniolvlofenoTaquerie AntoniolYloreno Taqueria AntonioMorenoTaqueila Aroundthe Hom
26:@r r. I Jedwnainf
lT---T
p&lzn**/
Ndmecko Ndmecko Ndmecko helados lrlexiko helados Mexiko helados l\4exiko helados I/exrko Mexrko Mexrko Mexiko lvlexrko l"lexiko lvlexiko M e x r ko M e x i ko Velkd Brilrnre
12021994 1 738.25KE 16.03.1998 2704 1998 1 010,$ Kd @.04.199807 t]5 1998 30,25Ka 08.08.1997 0509.1997 1 097,5U Kd 1310.19972411.1997 5S,50Kd 28.11.1997 26121997 2W,75Ki 04.03.1998 0104.1998 99800 Ka 18.09.1996 151019% 40,25Ka 27.111996 N 12.1996 550,00Kd 1504 1997 13.05.1997I 186.25 Kd 1305 1997 10.m.1997 391,00 Ka l905.1997:17E71997 1 121,08 V.a 22891gg7,20 10.1997 1Lr0 75 l.id 2509199723101997 9u325l,:a 28 01 1998 2502 '1998 1 460,75ka 151119 131219 104875ki
) l)l l>*1, ffi
hid cdv@i
od n;e
fhy
z6*azr*a
Obr6zek3.15: Piiklad 3.8, spojovSnftabulek: nahofe ndvrhov6zobrazeni,dole vlisledky dotazu
s2i t j
_.j
livou ccesr pffrllvt rOb76.
Piiklad 3.9: Omezeni visledkri spojeni tabulek .l.rk je na obfitzku 3.16 uvedeno pii spodnim okraji r".isledktrdotazu, vrdtilo se z piftladu -1.8celkem 830 zdznamit Podivdmeli se do tabulky Objedndvky, zjistime, Ze je tento poiet shodnf s celkov,.impodtem objedn:ivek. Je lasn€, Ze tolik informaci najednou nedokiZeme vstiebat; iii z piedchozich piikladt ale vime, Ze mnoZswi navrdcenfch i:idkir dat m0Zeme snadno omezit pomoci podminek, definovanych v panelu Krit€ria. Navic, proIoZe v dotazu spojujeme i6dl
N .(a
m F
f-
N
? '€J
..o "1
cL LL
2. Ve stejndm iridku krit6rii do sloupce DatumObjednrivky napiite viraz >1.1.1998. faal rypu datum vloZi Microsoft Access automaticky mezi symboly kiiZku (*). V5imndte si, Ze jsme do dotazu zapsali operdtor vdtii neZ (>) - to znamen6, Ze objedndvky podan6 plesnd 1. ledna 1998 se ve v'y'sledcichneoblevi. Pokud bychom chtdli vypisovat objedndvky od 1. ledna 1998 vdetnd, bylo by l€pe zapsat operitor v€tii nebo rovno (>=); protoZe ale 1. ledna je svdtek, v tomto konkr€tnim pifldadu je to jedno. Pozm6n6nf ndvrh dotazu a jeho r"i;sledky jsou uvedeny na olxlzku 3.77.
Piiklad 3.10: VnEj5i spoieni V piedchdzejicim piftladu 3.9 lsme si iekli, 2e zatim pracujeme s takzvan'im uniti"nim spojen{m tabulek. Ve Span6lsku mdme pfitom jednoho zdkaznika, ktenf nemd podan€ i6dn€ objedndvky - jeho ridaje se proto ve vysledcich pffkladu 3.9 neobjevi. Pokud se rozhodneme vypsat do vysledktr rlpln€ v5echny zdkaznil
r
ql (J \+*
.Y .: N al n
v
C '(0 Y
'jrF*4q'jirrf"
e'l.4
**
Fqr* zir'ia,
{ !. .,- ,-,
GOOOS GODOS GODOS GODOS GODOS GOOOS GODOS
BdlidoComidaspreparadas Galeriadel gastr6nomo Galeriadel gastr6nomo Godos CocinaTipica Godos CocinaTipica Godos CocinaTipica GodosCocinaTipica Godos CocinaTipica Godos CocinaTipica GodosCocinaTipica Romeroy tomillo Romeroy lomillo
ui"","
tlfl
( h I h,
SpanElsko Spandlsko Spandlsko Spandlsko Span!lsko Span6lsko Spandlsko spanetsh0 Spandlsko Spandlsko Spandlsko
,^ .3/j24.031998 07 04 1998 404,00Kd 1302.19981303.1998 31,25Kd 0503.19980204.1998 34,00Kd 05 02 1998 05 03.1998 4 383,00Kd 06.021998 06.031998 489,50 Kd 16.01 2 9 9 81 6 0 3 . t 9 9 8 1 2 9 6 , 7 5 K t 2602 1998 26.031998 954,75Kd '13 03.19981004.1998 584,75 Ka 08.041998 0605.1998 1 477,75Ka 2104 1998 1905 1998 80,00Kd 02.031998 30.03.1998 207,25Ki 0904.19980705199A 824,75KE
Obrdzek3'17: Piiklad 3.9, omezenirnisledk0spojenitabulek: nahore n5vrhov6 zobtazenL dole rrrisledky dotazu klad omezili rnypis1en na zdkaznky ze Spandlska a na objednrtvky podand po 1. leclnu 1998);jestli se ale roro filtrovdni provede pied spojenim tabulek .r.to po nem, na tom nezaleZL protoZe nechtend iSdky z lrrisledku vZdy zmizi. Opdt si piipomenme , Ze popi_ suieme,jak€tjsledkv chceme ziskat, nikoli jakk nim m:i syst6mdospet. Vndjsich spole_ nf existuji celkem tii ruzn€ typy a v datal:dzovlm oboru se pro nc bohuzel poulivaii tak trochu nesrozumiteln6 oznad.eni: r rev€ vndisi spoienf (LEFT ourrR JorN). Takov6 vndjii spojeni, ve krer6m se z tabulky po leve strand spojeni vraceji viechny i6dky a z tabulky po prav6 strand ier' data ve shodujicich se iddcich. I Prav6 vn€isi spofenf (RTGHT ourERJorN). u rohoro 'n€jsiho spojeni se vraci z tatnlky po prav6 strand spojeni vSechny iidky, zatimco z tabulky po lev€ stran€ jen data ve shodnfch i6dcich.
I
Pln€ vndjSi spoieni (FULL OUIERJOIN). Zde se do vysledkt vrdti vSechnyiddky z obou tabulek bez ohledu na to, testli se mezi nimi nachdzejishodnd data. Tento ryp spofeni Microsoft Access momentdlnd nepodporuje.
-tr' probl6m ve srozumitelnosti tdchto pojmir piedstarufi vyrazy leu€a prau6. V lak6mkoli 'iistujicirn dotazu stadi totiZ prohodit poiadi dvou tabulek a lev6 vn€jSi spojeni zmdnime . ., pravd nebo naopak. Microsoft Access mezi nimi ov5em nerozliSuje, takZe se v ndm icclrna podobnd spojeni nazfraii zkr6tka undjii spojeni. \r.ni zmdnime dotaz z piikladrt 3.9 na vndj5i spojeni. K romu poklepeme na tenkou idsr -:)()iovaci(iry mezi ob6ma tabulkami (Zlkaznki a Obfedndvky) a vyvol6me tak dialog \-lastnosti spojeni, zachycen'i na ot>r6zku3.18. Podobnd yako pii prdci v panelu Relace ::cni fipln€ jednoduch6 ,,trefit"se ukazatelemmy5i piesnd na tenkou (ist Utry, ale pii tro5e ':pdlivosti a cviku to zvl6dnetedocela hrav6. Ve starSichverz(chMicrosoft Accessulrypa.:-rltento dialog malinko jinak.
N6zev[*€ tabulky Zakazntri
ru;*iuuctogoup."
r
raaZgf.anifa
1
C;t;h"
:
.'xOaz;U-iL
!i ; lii zahrnoutviechny zSznamyz t$ulky Z6kazrilcia z tabulkyobje+dvky " - pouzety z6zmmy, ve ldery'chjsou spoFf,5 pob shodn6, 3: Zatmot viahny z5marry z tah.dkyOb!:*r5yky a z tabrky Zakaaii poue ty z6znamy,€ lderfch Jsouspoim6 poleshodr6.
stomo I
l!
ro rtr fr N
|o
'q
dsp."
Zahrnoutpouzei6dky,v nichi;souspolenjpoe . oUortiOuef rnoane,
l-- oK I
l-l
E .cJ
prav6 N6z* -OU;"4*uly labulky '
t\,i '(!
5 g L
LL
Neu6 I
Obrdzek3.18: Dialog Vlastnostispojeni A takto zmdnime typ spojeni v naSemcvideni
e-
u n l-
:k
ProtoZepoZadujemenavr6cenivSech iddkir z tabulky Zdkaznici, bez ohledu na n lezeoi shodnych zdznamb v tabulce Obyedndvky, rybereme tladitko piepinade dfslo 2 (iak je naznadenona obrdzku). Pot6 klepndte na tladitko OK. V iddku krit€rii pod sloupcem DatumObiedndvky doplite do textu podrninky vyraz OR IS NULL. Pokud bychom to neud€lali, zikaznfi
i
85
:l {J ..f
.p !F
..:l Ft
ru
# n
e
OJ .40 v
$|tri-'r{+.!,t
,A!b.d
' d jl J LiZ
rh4
r -.l
En*t
zetryy
.illl
EolidoComidaspreparadas FISSA FabncaInter SalchichasS A Galeriadel gast16nomo Galeriadel gastr0nomo Godos CocrnaTipica Godos CocinaTipica Godos CocinaTipica Godos CocrnaTiprca Godos CocinaTipica Godos CocrnaTiprca Godos CocrnaTiprca Romeroy lomillo Romeroy lomil'o
U6*ota OkE r{dpqrEd.
(
$ ,' :ll .r
Spanalsko Snandlsko Spandlsko Spanets[0 Spandlsko 5 p an e t s x 0 Spandlsko Spandlsko Jpaner9ho Spandlsko Spandlsko Spandlsko Spandlsko
:r:
'
: - .. -,r:: , - !| x
r^ .3'!.:tr, 2403 1998 0704 1998
404,00 ka
1302.19981303 1998 31,25Ki 0503.199802.041998 34,00Kd 05 02 1998 05 03.1998 4 383.00Ki 06 02 1998 0503 1998 489,50Ki 16021998 1603 1998 1 296,75 Ki 26 02 1998 2603 1998 954,75Ka 1303.19981004 1998 584,75 Kd tE 04 1998 0605 1998 1 477,75Kd 2104 1998 1905 1998 80,00Kd 0 2 0 3 1 9 9 83 0 0 3 1 9 9 8 2 0 7 , 2 5 K a 0904.19980705 1998 82475Ki:
Obr6zek3.19: Pifklad3.10,vndj5fspojeni:nahofe ndvrhov6 zobrazeni,dole vyisledky dotazu Porovndme-linyn( r".isledkyna obrdzku 3.19 s rysledky z obrizku 3.17 (piiklad 3.9), uvidime zde jeden i:idek navic. Je to zdkaznk s k6dem FISSA,ktery v tabulce Objecln;ivky nemd definovS,ni6dn'i, zaznam.V tomto iddku zirstalyproto sloupce DaturnObjedn.jvky, Dodat dne i Dopravn€ prazdne - piesndji obsahuji hoclnoty NULL. Zaiimavou vlastnosti Microsoft Accessu je, 2e pro kaZd'i dttabizo\,.i dotaz vy-generuiepfislu5ny text ve formdtu SQL. A i kdyl verze lazyku SQL v NlicrosoftAccessune\yhovuie normem ANSI, moZnost zobrazit dotaz z panelu ndvrl'ror'6hozol)razenido podoby piikazu SQL je piesto velmi uZiteanii- hodnE se tirr nalrainre..fazlku SQL se podrobnE vdnujeme v ndsledujici kapitole, a proto se nelekndte. Zc r'Ser.nuna pn-ni pohled nerozumfte. Na piepis dotazu z piikladu 3.10 do formdtu jazvka SQL.seo.,'Sempodivat mirZeme; k tomu staai v panelu nistroju klepnout na l'uel()uiipku u pr1-nihotladftka, Zobrazit, a z rozeviraci nabidky lybrat tlac=itko Zobrazerri SQL. jak r-idime v horni idsti obrlzkrr 3.20. Poznamenejme, Ze varianty Zobrazeni kontingenini tabulky ^ Zolbra-
86
tcni kontingeniniho grafrr jsou k dispozici ien v novdj5ich verzfch Microsoft Accessu. .c ale dostanemek textu v fazyceSQL, kterf ie zachycenve spoclni polovin€ obrdz. -1.20.Zde miZerne \ygenerovan,i pikaz SQL dokonce rudnd upravir a por€ jej pomorcverzniho inZenfrsrvi" vritit zpdt do panelu ndvrhov6ho zobrazeni.Docela uZitednd :rxce. nemvslite?
5cbq
_
|jprAry
-- t-t
eob.d
Ybd*
gote
$6*ojo
!$F
j'
.,:9-
"
t{6Frad! !'--1:
|\,l .ra "+
uvlhov6 zobrami
I , {t , .d
-a 'T n
lobrareniddtov€hol6tu ZobrazeniSQL
n
:obrazed bntingenini labulky :obra2eni kodhFniniho
Qrafu
C >a $J fr a] ?\
* T J
L
L
j
:".tor
t1t*
Zob.d
vl4
Qpte
!ii@
AkF
ilEFlEd.
,
..:,, ..'-
i;1'-iH:*ii,i',i,,ii,, ' ,',, ;,,,J;,,,|*, ,,,,,;i,,,*,,, ,,,,,): ,-ro,.;'!'-,) l.#:;fi#
:1,:
-
-
d
X
*'---.,,,
5ELECTZ6ka?nici.K6dz6knrnik, Z6k ?nti.Ftrma, Z6ka2niri.Zema,Objedn6vky.Datlmobjedn6vky, Objedniivky.DodatDne, Ob'edniivky,Dopravn6 FROM ZSlaznki LEFTJOIN ObEdn6vky ON Z6lazdci.K6dziikahikn = Obiedn6vkv.K6dz6b2ni*a {HERE (((z6karnici.ZemE)="SFdltko") AND ((Objedn5vky.Odmobjednivky)>slll/19981 or (ob'edn6yky.Oatumob)edn6vky) Ir Null));l
v ObrSzek3.20: Vlibdr zobrazeni v jazyku SQL(nahoie) a r4islednyitext dotazu SQLz piik l a d u3 . 1 0( d o l e )
Piiklad 3.11: NSsobnii spojeni a vipoitov6 sloupce Pokud pofebuieme mit ve r.fsledcich jedin6ho dotazu informace z vice nei dvou tabulek, neni nic snazSiho,neZ pi(sluSnoutabulku (a tfm pddem i pffsluSnouoperaci spojenOpiidat do definice dotazu. Pirvabem reladnich datab/azipiitom je, Ze se v takor'6m piipadd nemusimeptit, kter6 ze spojeni bude zpracovdnojako prvni, ani na dalSiimplementaini detaily. V tomto se mtrZemena reladni databdzoty system spolehnout - vSechnaporiebnd rozhodnuti provede za n6s.
: q) \J
v h
.v "5
N ag
!-
a) ..o
UvaZujrnenyni jinli piiklad: dejme tomu, Ze potiebujeme zjistit celkovou penlZni hodnotu vSech objedndvek podanfch zlkaznky ze SpojenlTchstdttr americlqich. Podivdme-li se na tabulky, kter€ mdme v databdzik dispozici,le ndm iasn€,Ze budeme potiebovat tabulku Zitkaznici (v niL provedeme filtroviLni podle poloZky Zemd) a d6ie tabulku Rozpis objedndvek (protoZe v ni se nachdzeli data, ze kterych zjistime celkovou hodnotu obiedn6vek, tedy konkr€tnd objednand mnoZswi a lednotkovd cena kaid€ poloZlry). Tyto dvd tabulky nemtrZeme ale LlLdn'imsmysluplnfm zpirsobem propolit piimo. Re5enise snadno nabi.zLpokud se vr6time k panelu relaci z obrdzku 3.3: potiebujeme je5t6 tabr,rlkuObjedndvky. Jakmile ji dopln(me do dotazu, muZemejiZ v tabulce Z6rkazniciryhledat zdkazniky ze Spojenychstdttr,tyto iddky pomoci pole K6dZdkaznikapropoiit s tabulkou Obledndvky a ryhledat obledn:ivky, kter€ tito z'irkaznki podali, a konednd piipojit i tabulku Rozpis objedndvek,v niZ najdeme jednotlivd iddky objednlvek a spoditdmejejich pen€Zni hodnotu. (Samoziejmd nemirZeme zaru(it, ie rela(n( databLzovi syst6m zpracujev5echna spojenf piesnd v tomto poiadi, ale koneiny vysledek bude stejnf, bez ohledu na konkr€tni postup.) Z tohoto piftladu by nav(c m6lo byt jasn€,prod je diagram vSechtabulek a relac( naprosto zrikladnim dokumentem - piedstavuje jakousi ,,mapu", podle niZ mtrZeme v databdzi formulovat dot^zy. V tomto piikladu budeme navic pracovat s uypoitenym nel>oli oduozenym sloupcem bude jim soudin poloZekJednotkovdCenaa MnoZswi.Do dotazu v reladnidatabizi mbieme zapsat prakticky fakykoli normdlnd myslitelnf wiraz, jak.i zn6me napiftlad z tabulkov€ho procesoru. Dotaz pro ieSenipiikladu J.11 vyfvoiime podle ndsledujicfchkroku: 1. Vyrvoite novi dotaz v ndvrhovdm zobrazeni. 2. Do dotazu piidelte tabulky Zdkaznici,Objedndvky a Rozpis objednuivek. ZLkaznkt ryberte sloupce Firma a ZemE. 3. Z tal>rlJrky 4. Z tabulky Rozpis obiednrivek r,yberte sloupce JednotkovdCerra a MnoZstvf. 5. Nyni nadefinujeme r.ypodteny sloupec; do iddku Pole v panelu kritdrii, do prizdn6ho sloupce vpravo od sloupce MnoZstvf napiSte proto vynz: Roz5Cena: JednotkovdCena t Mnoistvf . Zde prvni i'ist vyrazu woii popisek neboli titulek nov€ho sloupce. Kaidy ze sloupcir ve r,jsledcich dotazu mus( mit svtri vlastni, iedinednynizev, a pokud jef neurdime sami, pojmenuje jej automatickyMicrosoft Access.Yf'choz( ndzry sloupcfr nejsou ovSempifliS vystiZn6a iasto ani smyslupln6, a proto ie nejlepSiu rypodtenych sloupcu uZdy zapsatexplicitni ndzev (titulek). Poznamenejme, ie na mezerich ve specifikaci vyrazu PoLe neztrleZi, takie bychom fe mohli stefnd dobie r,ynechat. Microsoft Access zapsanyvyraz nakonec piepi5e, nadbytednd mezery odstrani a nlzvy sloupcir vloZi do hranaqTchzdvorek, takZe se nedilte, pokud se po piechodu kurzoru do jin6ho pole miiZky zmdni vyslednd podoba vyrazu oproti pirvodnd zapsan6mu tvaru. b.
ProtoZe chceme vysledky dotazu omezit jen na zAkaznky ze Spojenfch stdtu, napi5te do iddku Krit6ria pod sloupec Zem€ text USA.
7 . Nakonec doplite vzestupne iazeni podle sloupce Firma. Hotovy panel s ndvrhem dotazu a datovi list s rlsledkr- jsou zachyceny na obr1zku 3.21.
88
rdno-li se abul)zpts rjeddvd rdno
5diir "{i
-j --
d.J.
. a!k!* t{* tFE !*tpqEda ?t- .*+* ;-52 * " * i - , , : : , . . 1 . . 4 . i? - : . : i . : r - S ' : l "+
,:; -:r ::.-,'-r:ir: '
-f i\
j.j3:
- - !r x
, " ,I
N
'ried-
.fu a)
rznirjedrlku tdi-'chronrlek
o n
.a
r
N {1)
o
-O
'(i) .J
ntZeko-
3f*
<-
0F$ry
3ob.d
d -f J,i?
@
F*n&
d (+ . ,
GreatLakesFood lvlarket GreatLakes Food lvlarket GreatLakes Food N,larkel GreatLakes Food l,4arket GreatLakes Food lvlarket GreatLakes Food lvlarket GreatLakes Food Markel GreatLakes Food lvlarkel GreatLakes Food lvlarkel Greal Lakes Food lvlarkel Greal Lakes FoodMarket Greal LakesFood Markel GreatLakesFood Market Greal LakesFood lvla.ket Greal LakesFood lvlarket
zb@,
: USA USA USA USA USA USA USA USA USA USA USA USA USA USA USA
U6@
OkE
|ILI ,9'tj
"
': .: :: v - 6r x
t|f0o!U.
lSlr r^'J
450,00Ki 525,00Kd 850,00Kd 3 094,75Kd 475,00 Kd 1 232,50Ka 6 587,50Kd 831,25Ki 250,00Kd 230.00Kd 237,5DKE 62.50Kd 250,00Ka 48625Ki 870,00Kd 450,00Kd
B 3 15 2 30 20 30 28 l2 30 6 I 20 28 9 4
3-
s,
3 600,00Kd 1 575,00Kd 12750,00 Ka Ka 6 189.50 14250,00Ka 24 550.00Kd 197625,00Kd 23 275,00Ki 3 000,00Ka 6 900,00Kd 1 425,00Kd 500,00Kd 5 000,00Ki 13615,00 Kd 7 830,00Kd 1 800,00l
ObrSzek3.21: Piiklad 3.11,ndsobn6spojenfa vlipottov6 sloupce:nahoie nSvrhov6 zobrazeni,dole vlsledky dotazu
Piiklad 3.12=Agregaini funkce k 1f
l,
IestliZese nynivrdtime k vysledkfim piikladu 3.11,je jasn€,Ze ru ndco neniv poi:idku dotazn6m vrdtil plnlTch 352iidkn dat (v 1in€ verzi Accessu, respektive iin6 verzi datab,aze Northwind, to mirZe b1;t 1in6 dislo). I ke kaZddmu ztrkaznkovi se vrdtilo mnoho i:idku dat. Samoziejmd Ze kaid!' z:akaznikmiZe mit poddno ndkolik objedndvek a kaidl objedndvka obsahujendkolik i6dku rozpisu, my ale nechcemeviddt v5echnyiejich detaily, ale celkououd6stku,za kterou si u nds ten ktery zLkaznk objednal zboZi. Potiebujernetedy sedistvSechny hodnoty sloupce Roz5Cenapro kaZd6ho zLkaznka. V reladnich databizich lyie5ime tento probl6m snadno, a to pomoci funkce SUM. Funkce je zvlSitni fyp programu (programov€ho k5du), ktery pii kald€m rryvoldni vrdti jednu jednotlivou hodnotu; je tak realizacimatematickefunkce. ProtoZemy chceme prov:idEt u.ipodet (soudet)nad fednim ze sloupcfr, musime do funkce piedat vSechnyiddky dat a pot6 z ni vrStit po1adovanl jednotliv6 hodnoty. Ndkdy hovoiime o slottpcoue.funk-
n :il
; C a_l IL
:)
ci, protole ji aplikuleme na cely sloupec tabulky ii pohledu. Pilkladem obyiejn€ sloupcov6 funkce muZe byt zaokrouhlenf,ROUND. V na5empiiklaclu budeme ov5em potiebovat iini, speciiilni ryp funkce, kterd pii r,jpodtu sloudi ndkolik iddktr do ledin6ho: ry se naz'fuaj(agregainifunkce a n€kter6 z nich si ukdZemev ndsledujicitabulce:
rr*
': = ;" .tr N #
*0 C ai
lx
Ndzevfunkce
Popis
AVG
gy!q{19!,9l9upce, Yfp-og. erutctn
COUNT
Zjistipoiethodnotv dan6msloupci.
MAX
Vyhled6 maxim6lni hodnotu sloupce.
MIN
hodnotu sloupce. Vyhledd minim6lni
SUM
Vypoitesouietviechhodnotve sloupci.
Pokud bychorn ale do dotazu zapsalivolini agregaini funkce samo o sobd, vrdtil by se pro cely dotaz ien ieden leclinyi6dek. To je v poi6dku, protoZereladni clat.tbizovysystem v takov6m piipadd nevi, jaky jinli r,ysledekby rndl zjistit.JestliZeale potiebujerner.ypoiitat agregovany r,lzsledekpro urdit6 skupiny iddku dat ve r,ysledcich dotazt, rnusirne do jeho definice doplnit klauzuli GROUPBY, na z:ikladdkter€ reladni databize seskupiiddky podle hodnot jednoho nebo vice sloupcu a aZ pot€ proveclevypodet agregadnifunkce nad kaZdou ze skupin. Dd se iici, Ze je to podobn6, ;ako kdyZ si v tabulkov€m procesoru nanrfstocelkov€ho souitu *ryiidLme je5td zobrazeni urezisouitu skupin. V piikladu 3.12 tak poZadujeme, aby reladni datablze zjistila soudet hodnot rypodten€ho sloupce Roz5Cenapro kaZddho ztrkaznka. Jinymi slovy rnusfrne nejprve seskupit idclky poctle zikaznkir a pot6 pro kaZdou skupinu zobrazit jedinf iddek se jm€nern finny, zemi a celkovou diistkou obiednan€hozboii.
Vypis zemd je zde fakticky zbyeinfi, protoZe jsme pomocf filtru ornezili vysledky jen na zdkaznky ze Spojenfch sdtu. My ale tento ridaj ve v..isledcichponech/rrne,a to pro ilustraci jednoho dirleZit€hoprincipu, ktery ie pro noviidky v reladnichdat,i>izich aastoobtiZnd pochopitelny: iestliZerybereme sloupce Firma, Zernd a vypodteny sloupec Roz5Cena, piidemZ reladnimu databizov€mu syst€mu iekneme, podle jakdho vzorce m6 r,ypoditat podle sloupce celkovou r"i;Siobjedn:ivek,a z:irovei jej poZdddmeo seskupenir,"risledkrh Finna, dopustime se jedn6 logick6 chyby, dfty kterd vriti i samotnd reladni datab'ize chybu. V podstatd jsme totiZ po26dalio navrdcenihodnoty Zernd pro kaZdy ildek dotazu, ale souiasnd tak6 o agregaciiiidku podle sloupce Finla a o vypodet soudtu pro kaZdou agregovanou skupinu. Bylo by nelogickd 26dat ien o agregaci ndkterych i6dkn a 1ii ne o agregaciiinlich. Navic, lyslednl chybovd zpriva je pomdrnd nesrozumitelol, takle mdme sto chuti misto ,,agregadni"funkce iikat ,,agresivni".Zapamatuitesi proto jedno jednoduch6 pravidlo: jestliZedotaz obsahujendjakou agregaini funkci, pak musime kaidy ze sloupctr rfsledkir dotazu zapsat budto do voldni agregadni funkce, nebo musi blt uveden v seznamu GROUP BY. V Microsoft Accessu zapneme agregovany r,..ipodetpomoci tladitka Souhrny z panelu ndstrojrh(nach6zi se zhruba ve dvou tietinlch feho d6lky a je na ndm sumadnisymbol, tedy velkd ieckd pisrneno sigma). Po jeho z pnuti se v panelu kritdrii oblevi iddek Souhrn, v ndmZ definujeme potiebn6 seskupeni dat i agregadni v,.ipodet; druhym klepnutim na stejn€ tladitko souhrny zase \ypneme. Konednd tedy piistoupime k fpravd piikladu J.77 oa poZadovaoy agregovany vypodet: L Z dotazu odstraite sloupce JednotkovdCefla ^ MnoZstvi: klepndte na SedE zlhlavi oiislu5n6ho slouoce nad nlzvem a stiskndte kldvesu Deiete.
90
loupiebo-
2. Zmditte nadpis sloupce RozSCena na CelkemObjednivky. na5e potieby rozhodnd v,iTstiZnEj5i.
Vs.
3. Nyni v panelu ndstrojri klepndte na tlaiitko Souhrny (ikona md zmindnou podobu ieck6ho pismene sigma) a v n6vrhov€m panelu rryvolejte zobrazeni iddku Souhrn. Na tomto iddku maii zpoYttku vSechny sloupce uveden text ,,Seskupit".
Tento nadpis je pro
4. Nakonec ve sloupci CelkemObiednivky klepndte do i6dku Souhrn a z rozevirac(ho seznamu lyberte poloZku Sum. ,zmdndnf ndvrh dotazu i leho vysledky si mirZeme prohl€dnout na obr6.zku3.22.
'il .fi
r!
,p 7
ro )y se stenr >oii e dcr idkl' nad soru 3.72 rpce cdle cel-
N P
.o
's : c L II
Ina lus)tiz)na, litat pce 6ze )ta'.a2iLz <2e 'ze ren na rri)o: AA uc
Obrdzek 3.22: Pifklad 3.12, agregaini funkce: nahoie ndvrhovd zobrazeni,dole u.isledky dotazu
Piiklad 3.13: Spojeni na sebe sama w .H +
.Ll
N m F Z
c P
Je-li nad ndkterou z tabulek definovdna rekurzivni relace, mus(me ji implementovat pomoci spojeni tabulky na sebesama. Typickym pifldadem je tabulka Zamdstnanci,kde je sloupec Nadiizenf cizim kl(dem a ie sv|zdn se sloupcem eisloZamdstnanceve stejndtabulce; tato relace ukazuje nadiizen6ho, pod kterym dany zamEstnanecpracufe. Nyni budeme cht(t ke kaZd6mu zamdstnanci \ypsat ifsloZamdstnance, Jm6no, Piijmeni a lmdno nacliizen6ho.V tabulce bude samoziejmdalespoi jeden zamdstnanec,ktery nemd piiiazen€ho Lddnlho nadiizen6ho (ieditel spolednosti), a proto musime v dotazu definovat vndjSi spojeni, jinak se do r"fsledkt nemohou promitnout vSichni zamdstnanci. Dotaz, kter'.i bude ie5enim piikladu 3.L3, vwoiime
podle ndsledujicich kroktr:
1. Vytvoite novy dotaz v ndvrhov€m zobrazeni. 2. Z dialogu Zobrazit tabulku piidejte do definice dotazu duakrdt tabulku Zamlstnanci. V5imndte si, Ze teto druh6 ,,kopii" tabulky piiiadi Microsoft Access automaticky iiny n6zev, obvykle Zamdstnanci-l. Po dokonien( klepn6te na tlaaftko Zaviit. Zatim ale Microsoft Accessnevi, jak tabulku samu se sebou propojit, a proto mus(me iici, kterf sloupec ciziho klide se md shodovat s primdrnfm klfdem. Pietdhndte tedy sloupec Nadiizenf z tabulky Zamdstnanci a pusdteie1nad sloupec iisloZamEstnance v tabulce Zamdstnanci_1. Microsoft Access vytvoii pro novou relaci ddru, kterd ale bude lypadat trochu jinak - neni to zddddn| relace, nybrZ rudn€ definovan€spofeni tabulek. 4. ProtoZenovd relacem6blt definovdna jako vndySfspojeni, poklepejte ndkde doprostied tenke spojovaci Urry a v dialogu Vlastnosti spoienf lyberte variantu 2. Nyni ryberte sloupce dislozam€stnance, Jm€no a PFfmeni z tabulky ZamEstnanci. 6 . Dfle vyberte sloupec PFfmeni z tabulky ZamEstnanci_l; to je pffjmeni nadfizen€bo. 7 . Zmdite titulek sloupce s piijmenim nadiizen€ho a napiStetext Nadfizenf; nakonec feSt€ zadejtevzestupnd fazenipodle sloupce ilsloZamistnanc€. Ndvrhovd zobrazeni hotov€ho dot^zu a lysledky jeho spu5tdni jsou zachyceny na obr6zku 3.23. Doufdm, Ze jste tento rivod do Microsoft Accessu zvlldli s piehledem. Cel€ problematiky fforby dot^zb jsme se zde ien letmo dotknuli a rozhodnd se fe5td mdte co uiit a s dim experimentovat. JestliZe napiftlad hotovy dotaz v databLzi Microsoft Accessu uloZfte, mtrZete iej vyuZit jako ztrklad pro jind dotazy. Podet ,,rirovni abstrakce" pii t6to tvorbd ndslednfch dotaz'bneni nijak pevnd omezen a sami uvid(te, Ze pii vhodn6m rozddleni do idsti neboli ,,podprobl6mir" snadno vyie5ite i zdinlivd neie5itelnd, velmi sloZitd dotazy.
92
molourlce; LOfl-
6ho
N
ipo-
(s # fi
.*
Jst-
N
lto-
ro
tko .(t) 1S1-
:rdtf,sou )rZ
.(u :l
Ako r4abdr
stto,
.v It
o itdo orlmatkky
piiqren€ rcvdm! qdn&hdnci,
obrdzek 3.23: Piiklad 3.13,spojenina sebesama:nahoie ndvrhov6zobrazeni,dole vfsledky dotazu
c '{
Otazky ke cviEeni
o
U kaid6 z otiLzeks {b6rem ndkolika moZnosti vyberte sprdvn€ odpovddi. Nezapomeite, Le otS.zkymohou mft i vice neZ jednu sprdvnou odpovEd.
.Y
-")a N .G
n
1. Formulliovd orientovan,i dotazovaci iazyk: a) Vywinulapoprv6 spoleinost IBM v osmdesdrychletech. b) Popisuje zpfisob zpracov1.ni dotazu, nikoli oiekdvan6 r,".isledky. c) Piipomin6 jazyk SQL. d) Pracujev grafick€m uZivatelskdmrozhrani (GUI). e) Y iizenych studiich se jasnd uklzal lako vhodn€1S(. 2. Typy oblektir v Microsoft Accessu,kterd se tikaii vihradnd sprdr,y datablze (nikoli v..ivole aplikaci), jsou: a) Tabulky b) Dotazy c) Pohledy d) Formuldie e) Strdnky 0 Makra g) Moduly 3. JestliZez panelu Relace v Microsoft Accessu odstranime urditou tabulku, pak: a) OkamZitdse odstrani i z databLze. b) Je v databdzi ozoaiena k odstran6ni. c) Zirstane v databLzi zachovdna, ale vymalou se z ni veSker€ iddky dat. d) Odstrani se z6rovei i ve5ker€ relace, kter6 k tabulce ndleiely. e) Zirstane v datab6zi naprosto nezmdn€nd a zmizi povze z panelu Relace. 4. Relacev panelu Relace pod Microsoft Accessem: a) Vyiadiuli referendnf omezen( v databdzi. b) Jsou definovdny mezi primdrnimi klidi a alternativnimi klidi. c) Nemohou blit definovriny jako rekurzivn( relace. d) Dedi se v dotazech jako spojeni rabulek. e) Mohou bf't typu jedna k vice, jedna k jedn6 nebo vice k vice. 5. Dialog Zobrazit tabulku v Microsoft Accessu: a) Uv'ldi ve5kerd tabulky v databdzi a umoZfiuje piid6v6ni, odstraiovdni a zm€nu metadat o tabulk6ch. b) Uvddi pouze tabulky uloZen6 v databS:zi. c) Uvddi tabulky a dotazy uloZen€ v datab6.zi. d) Uv:idi pouze dotazy uloZend v dataY:62i. e) Nabiz( moZnost zolxazeni nebo skryti jednotliv'.ich tabulek.
94
lt
rite,
Sloupec ve r,"./sledc(chdotaztt Microsoft Access mnZe bjrt r,ytvoien z: a) Sloupcetabulky b) Sloupcepohledu c) Konstanty d) VlTpodtu e) iehokoli, z deho se mirZe sklddat viraz (vzorec)
,\
Vysledkem spu5tdni dotazu, kten/ neobsahuje i6dnd kritdria, je:
"1] t\
a) Chybovd zprdva.
# (g
rs
b) Nezobrazise 1trdn€i6'dky. %
c) Zobraz( se vSechny i6dl
.-
d) Kart€zsl
fs
e) Nic z v'fSeuveden€ho.
v
JestliZedatabizovi dotaz neobsahtje i6dn€ iazeni vysledktr, pak iddky ve vysledcich dotazu:
'0J
a) Nejsou v Z6dn€m konkr€tnim poiadi. b) Jsou seiazeny v tom poiadi, v iak€m byly do tabulky (tabulek) piidSrydny. c) Jsou seiazeny podle hodnot primdrnfto klide. d) Jsou seiazeny vzestupn€ podle prvniho sloupce vysledkir. e) Jsou seiazeny vzestupnd podle prvniho indexu v rabulce (tabulk6ch). 9. Ve r".isledcichdotazu s ryhleddvacim krit6riem OBLAST NOT = ,,CA"OR OBLAST NOT = ,,NV" se zobrazi: a) Chybovri zpriva. b) V5echny iddky tabulky. c) Jen takov€ iddky, ve kter,.ichse sloupec Oblast rovnd ,,CA" nebo ,,NV". d) VSechny i:idky tabulky s v".ijimkou tdch, kde se sloupec Oblast rovn.i .,CA" nebo ..NV".
d Zaaneradky. 10. Kritdria v dotazu Microsoft Access jsou: a) Propojena logickou spojkou AND, pokud se nachlzeii ve steln6m iddku. b) Propojena logickou spojkou OR, pokud se nachdzejive stejn€m iddku. c) Propojena logickou spojkou AND, pokud se nachdzeji ve sloupci stejndho pole. d) Propojena logickou spojkou OR, pokud se nachSzejive sloupci stejn6ho pole. e) Ve stejn6m iddku propojena logickou spojkou AND, piidemZ jednotlivd i?idky kritdrii jsou propojeny logickou spojkou OR. 11. Spojovaci d6ru mezi tabulkami v dotavr Microsoft Access: a) MriZeme vrfrvoiit rudn6, jestliZe pierShneme sloupec z iedn€.tabulky di pohiedu nad sloupec ve druhd tabulce di pohledu. b) MriZe b'it zdEdlna z metadat, definovanfch v panelu Relace.
-q
E
c) MtrZe byt pozmEndna na definici lev6ho, prav€ho a pln€ho vn€jSfto spojen(. d) Pii nesprdvn6 definici mirZe definovat kartezs$ souiin. dr
il J N P
e) Nenili definovdna mezi dv6ma tabulkami nebo pohledy v dotazu, definuje kart€zslci soudin. 12.JestliLev dotaz! definujeme vn€fSi spojeni, pak se data sloupctr z tabulek (nebo pohledfi), ke kterym nebyly nalezeny shodn€ zLznamyl a) Zobrazi ve sloupcich diselndho typu jako nuly. b) Zotxazi ve sloupcich znakov6ho typu jako prdzdn6.ietdzce. c) Zobrazi Sedoubarvou. d) Zobrazi lako text ,,NULI". e) Dostanou piiiazenu hodnotu NULL. 13.Agregadnifunkce: a) Spojujedata z n€kolika sloupcu. b) Spojujedata z ndkolika iddktr. c) MtrZeby aplikovrinana sloupce tabulky, nikoli v5ak na qzpodten6sloupce. d) Je speciflnfm rypem funkce pro databdzovd dotazy. e) Pii iejim zdpisu v dotazrr mus( byt kaZd17sloupec budto zahrnut do vypodtu agregadni funkce, nebo musi blit uveden v klauzuli GROUP BY. 14. Mezi bdlnd agregaini funkce patii: a) AVG b) COUNT c) ROUND d) SUM e) MIX 15.Spojenitabulek na sebe sama: a) NemriZe nikdy v6st k vywoieni kart6zsk€ho soudinu, protoZe oba zdroje dat poch|zeji ze stejn6 tabulky. b) VZdy vede k rytvoieni kart€zsk€ho soudinu. c) Je metodou pro ieSen( rekurzivni relace. d) Zoamend v Microsoft Accessu iakdkoli rudn6 vywoien€ spojeni tabulek. e) Znamend spojeni tabulky s druhou ,,kopif' stejnd tabulky. 16. Ndzev sloupce u rypodten€ho sloupce ve r,fsledcich dotazu: a) Nenili v definici dotazu uveden, obsahule hodnotu NULL. b) Nenili v definici dotazu uveden, piiiadi jej automaticky Microsoft Access. c) Nenili v definici dotazu uveden, pievezme se jako ndzev prvn(ho sloupce pouZiteho ve vzorci. d) MirZe blit definovdn pomoci titulku, kter'.i se v definici pole zapisuje na prvnim mistd. e) MirZe bft definov6n pomoc( titulku, kterf se v definici pole zapisuje na poslednim mistE.
3ni. nu,e :ebo
17.Tabulky je moZn€ spojovat: a) Jen pomoci primdrnfto klide jedn€ tabulky a ciziho klide druh€ tabulky. b) Pomoci libovoln€ho ze sloupctr v kaid€ z tabuiek (teoreticky). c) Jen na sebe sama. d) Jen s iinymi tabulkami. e) Jen pomoci operace kart6zsk€ho soudinu. 78.Dotazy v Microsoft Accessu: a) Ve vdtSin€ostatnichrelainich databdzise naz'fuajipobledy.
N
,6 P
b) Ve vdt5ind ostatnich reladnich databLzi.se nazyvaji entity. c) Mohou bft uloZeny do databLze, kde je mtrZeme ndsledn€ vyttlit.
(o -
d) Jsou vysoce flexibilni pikazy pro na(itdni dat z databdze. e) Nabizeji moZnost generovdni piftazfi SQL.
v
19.JestliZez dotazu Microsoft Access odstranime urdir'/ sloupec: a) Odstrani se tento sloupec ien z aktullniho dotazu. b) Odstrani se tento sloupec i ze v5ech dotazfi, kter€ se na tento dotaz odkazuji. dtu
c) Odstrani se tento sloupec tak6 z tabulky a ze vSech dotaz'ir,kter€ se na ni odkazuii. d) Pokud je sloupec pouZfvin v ndkter€m jin6m dotazu, zobrazi se chybovd zpriva. e) Sloupec ztstane v dotazu zachov6n, ale je oznaien jako odstrandnlT,takZe jeho data se nepromitali do ',r'.isledktdotazu. 20.Kart€zslci soudin: a) Je vysledkem dotazu, pokud v n€m neni definovdno spojeni mezi dvdma tabulkami. b) Je u.isledkem dotazu, pokud v ndm neni sprdvnE definovlno spojeni mezi dvdma tabulkami. c) Je rysledkem kaZd€ho spoleni tabulky na sebe sama. d) Je vfsledkem operace, pii nii kaZdj' i6dek z jednd tabulky spojime s kaid-i,m iddkem druh6 tabulky. e) NemriZe v dotazu pod Microsoft Accessemnikdy vzniknout. Spr6vn€ odpovddi najdete v Pifloze B v z6vdru knihy.
N fi
%
.(u 'q 5
IL
