S řidičákem do Evropy - 5. modul ECDL: jak porozumět základům práce s databázemi

Kdy nestačí tabulkový kalkulátor a musíme sáhnout po databázovém programu?
Předpokládejme, že musíme zpracovávat spousty dat. V minulém pokračování jsme

si řekli, že data můžeme ukládat do tabulek na listech sešitů tabulkových

procesorů. Proč tedy naše data nezpracovávat pomocí těchto programů, zvláště

když jsme se již s nimi naučili zacházet. Vždyť na listu Excelu lze vytvořit

tabulku až o 65 536 řádcích a 256 sloupcích. Víme dále, že tabulkové procesory

mají velké množství speciálních metod pro práci se seznamy, neboli databázemi

tabulkového procesoru. Zopakujme, že nejdůležitější z nich je třídění seznamů,

filtrování seznamů, tvorba různých souhrnů v seznamech a schopnost vytvářet z

našich seznamů souhrnné (kontingenční) tabulky.

Přesto nám někdy tento způsob práce s daty nestačí a musíme se poohlédnout po

nějaké databázové aplikaci. Máme-li tolik dat, že je nám počet řádků listu

tabulkového kalkulátoru málo, zcela určitě sáhneme po databázovém programu.

Schopnosti tabulkového procesoru nám však mohou přestat vyhovovat z jiného

důvodu, než je vyčerpání kapacity (nestačí počet řádků) listu sešitu

tabulkového procesoru. Vysvětleme si nyní, kdy nastane tento zlomový moment,

kdy je tedy nutno nezávisle na množství dat odvrhnout tabulkový procesor a

začít naše data zpracovávat databázovou aplikací.

Začněme příkladem. Je nutné ukládat si data o zaměstnancích naší firmy. Je to

např. jméno, příjmení, rodné číslo a plat. K tomu nám stačí excelovský seznam,

jehož sloupce nazveme jmény ukládaných položek (Jméno, Příjmení, RodnéČíslo a

Plat). Za nějaký čas bude nutné začít ukládat též informace o dětech našich

zaměstnanců. Neustále budeme tvrdošíjně používat Excel a k našemu seznamu

přidáme nové sloupce nazvané JménoD, PříjmeníD (dítě může mít obecně jiné

příjmení než jeho rodič), atd. Jak to ale uděláme, má-li člověk více dětí?

Osvítí nás, jak se nám zdá, vpravdě geniální myšlenka. Do rodičova řádku

napíšeme data o jeho prvním dítěti. Pak pod tento řádek vložíme řádek nový a

sloupce, do nichž ukládáme data o dětech, vyplníme položkami druhého dítěte.

Políčka týkající se zaměstnance zůstanou v tomto řádku prázdná. Tento postup

opakujeme při zadávání dat o každém dalším dítěti zaměstnance.

Za nějaký čas však budeme muset naše zaměstnance setřídit např. podle výše

platu. Po setřídění zjistíme, že nám Excel řádek pro druhé a další dítě každého

zaměstnance umístí na konec seznamu (ve sloupečku, podle něhož třídíme, není v

řádcích pro děti hodnota), čímž odtrhne data o těchto dětech od dat o jejich

rodičích, takže přestaneme vědět, ke kterému rodiči tyto děti patří. Náš seznam

je zcela znehodnocen, děti v něm ztratily své rodiče. Ještě že má Excel příkaz

Zpět. Ihned jej použijeme. Potom musíme tabulku upravit tak, že ke každému

druhému a dalšímu dítěti našeho zaměstnance doplníme též zaměstnancova data, a

původně tak „geniální“ myšlenka prázdných řádků v našem seznamu vezme za své. V

tabulce se však objeví velké množství nadbytečných (redundantních) dat. Každý

zaměstnanec je zde uveden tolikrát, kolik má dětí (viz obr. 1).

Osvítí nás geniální myšlenka číslo dvě. Uděláme si tabulky dvě. Jednu pro

zaměstnance, nazveme ji tLidi a druhou pro děti, kterou nazveme tDěti. Jak však

poznáme, kterému rodiči z tabulky tLidi patří každé dítě z tabulky tDěti?

Poznamenejme, že dítě v naší databázi může patřit jen jednomu rodiči, tomu na

kterého je definicí „napsáno“, i když biologicky má samozřejmě rodiče dva. Do

tabulky pro děti přidáme další sloupeček nazvaný Rodič a do něj uvedeme nějakou

jednoznačnou charakteristiku rodiče, nejlépe rodné číslo. Tím vytvoříme mezi

našimi dvěma tabulkami vztah (relaci), v němž ke každému řádku z tabulky tLidi

může příslušet více řádků z tabulky tDěti. Jsou to řádky s rodným číslem řádku

z tabulky tLidi. Naopak každému řádku z tabulky tDěti přísluší právě jeden

řádek z tabulky tLidi. Obě tabulky jsou znázorněny na obr. 2.

Tvorba tabulky tDěti však nebude jednoduchá. Rodné číslo rodiče nutno najít v

tabulce tLidi a opsat či zkopírovat do tabulky tDěti.

Další problémy vzniknou při vlastní práci s těmito dvěma tabulkami. Chceme

např. vytisknout seznam zaměstnanců a jejich dětí. Ke každému zaměstnanci z

tabulky tLidi vyfiltrujeme na základě jeho rodného čísla děti z tabulky tDěti.

Za účelem tisku si asi pořídíme speciální tabulku, v níž pod každého

zaměstnance zkopírujeme jeho děti, které jsme vyfiltrovali z tabulky tDěti.

Jistě si umíme představit, jakou práci nám zhotovení této tabulky dá. Tabulkové

procesory totiž nemají prostředky k tomu, aby s takovouto datovou strukturou

(tabulky, mezi kterými je výše popsaný typ vztahu) efektivně pracovaly. To

dovedou až databázové programy.

Na obr. 3 jsou tabulky obsahující stejná data, ale v databázi Accessu. Ve

sloupečku Rodič tabulky tDěti můžeme mít zobrazeno navíc k rodnému číslu i

příjmení rodiče. Klepneme-li v accessovské tabulce tLidi na znaménko plus před

řádkem s daty určitého zaměstnance, zobrazí se záznamy (řádky) s daty jeho dětí

(viz obr. 7, kde je podobná situace znázorněna). Porovnáme-li obr. 2 a 3, jistě

dáme přednost pohledu na obě tabulky v Accessu. A to jsme ještě zdaleka

nepoznali všechny možnosti databázových programů.

Vezměme další problém. Máme si ukládat data o dodavatelích a výrobcích, které

tito dodavatelé dodávají. Uvědomme si, že každý dodavatel může dodávat více

výrobků a každý výrobek, může být dodáván více dodavateli. Uložíme-li naše

informace do jednoho seznamu Excelu, bude se nám zde každý dodavatel se všemi

jeho daty opakovat tolikrát, kolik dodává výrobků a každý výrobek, opět se

všemi daty o něm, tolikrát, kolik jej dodává dodavatelů. Celou situaci vidíme

na obr. 4.

Jak odstranit toto obrovské množství redundance v našem seznamu? Databázový

odborník nám poradí, že si máme udělat dokonce tři tabulky. Každý řádek v každé

tabulce budeme přitom nějak jednoznačně charakterizovat. Může to být pořadové

číslo řádku či jiná charakteristika (rodné číslo apod.). Tuto charakteristiku

budeme nazývat primární klíč. Do první tabulky, kterou nazveme tDodavatelé,

uvedeme pouze dodavatele. Jako primární klíč zde může sloužit např. pořadové

číslo dodavatele. Do druhé tabulky tVýrobky uvedeme výrobky. Primárním klíčem

může být např. pořadové číslo výrobku v tabulce. V žádné z těchto dvou tabulek

nebude redundance. Na druhé straně však z nich nebude jasné, který dodavatel

dodává jaké výrobky, ani jakými dodavateli je dodáván každý výrobek. Proto

musíme sestavit třetí tabulku tRozpis, z níž tyto vztahy budou vyplývat. Stačí

jí dva sloupce. V prvním, který nazveme PORCISDOD (jako Pořadové číslo

dodavatele), uvedeme primární klíč z tabulky dodavatelů. Každé toto číslo se

bude v tabulce opakovat tolikrát, kolik dodavatel dodává výrobků. Ke každému

pořadovému číslu dodavatele přitom uvedeme do druhého sloupce nazvaného CISVYR

(jako Číslo výrobku) pořadové číslo výrobku, který dodavatel dodává. V tabulce

tRozpis nebude žádný ze sloupců primární klíč, neboť v každém mohou být

duplicity. Primárním klíčem zde mohou být oba dva sloupce společně, neboť

kombinace pořadové číslo dodavatele a pořadové číslo výrobku nemá v této

tabulce duplicity. Na obr. 5 jsou však všechny tři tabulky již jako součást

databáze Accessu znázorněny.V tabulce tRozpis je opět schopnostmi Accessu k

pořadovému číslu dodavatele zobrazeno i jeho příjmení a k pořadovému číslu

výrobku zase jeho název.

Současně s poskytnutou radou na rozdělení našeho problému na tři tabulky s

popsanou strukturou se nás databázový odborník zeptá, kterým databázovým

systémem chceme naše data zpracovávat, popřípadě nám nějaký vhodný doporučí. O

tabulkovém procesoru nepadne z jeho strany ani zmínka.

Možná jste z předchozích příkladů poznali, v které chvíli přijde ten zlomový

moment, kdy musíme odhodit tabulkový procesor a zpracovávat naše data

databázovou aplikací. Řekněme to nyní zcela jasně. Pokud bychom si vedli

záznamy pouze o zaměstnancích či dodavatelích nebo výrobcích, obecně řečeno

ukládali data pouze o jednom typu objektů, pak by nám tabulkový kalkulátor

postačoval (nepřekročíme-li s naší tabulkou počet řádků listu a není-li třeba

pro každý objekt ukládat data, která tabulkový procesor do buněk ukládat neumí

obrázky, zvuky nebo jiná binární data).

Je-li však třeba uložit data o více typech objektů spolu se vztahy mezi těmito

typy objektů (rodiče a jejich děti, dodavatelé a jimi dodávané výrobky), pak

musíme použít databázový program.

Několik základních databázových pojmů

V knize Stevena Romana nazvané Microsoft Access Návrh a programování databází

(vydal Computer Press, 1999), se dočtete následující definici. Databáze je

souhrn perzistentních dat, mezi kterými mohou existovat vzájemné vztahy. Slovo

perzistentní v definici databáze znamená, že naše data existují uložena

(obvykle na disku) nezávisle na programu, který je vytvořil a který s nimi

pracuje. Způsobů jak v databázi ukládat data (nemyslíme nyní na jakém paměťovém

nosiči, ale jakým způsobem) existuje několik. V současnosti převládají tzv.

databáze relační, o nichž si v dalším něco bližšího povíme. Náš výklad budeme

demonstrovat na aplikaci Access, která je důležitou součástí MS Office.

Databázi musíme vytvořit nějakou aplikací, jež poskytuje nástroje pro práci s

daty v ní uloženými. Pomocí této aplikace naši databázi nejen vytvoříme, ale

též s ní často pracujeme, tj. získáváme z ní potřebné výsledky. Takových

databázových aplikací je celá řada. Z nejznámějších jmenujme alespoň Visual

FoxPro, DBase a MS Access. Vlastní databázi spolu s nástroji pro práci s ní

potom nazýváme databázový systém.

Zdrojem dat v relační databázi jsou tabulky. V každé tabulce bychom měli

uchovávat informaci pouze o jednom typu objektů neboli třídě entit. Jedním

typem objektů, neboli jednou třídou entit jsou např. všechny možné knihy,

dalším typem objektů všechny možné výrobky, další třídou entit všichni možní

dodavatelé. Pro každý tento typ objektů vytvoříme novou tabulku. Vlastnosti

objektů popisovaných v tabulce ukládáme do jejich sloupců. V databázové

terminologii nazýváme sloupec pole. Data příslušející jednomu konkrétnímu

objektu umisťujeme do jednoho řádku záznamu naší tabulky. Celá tabulka musí mít

v databázi jedinečné jméno a každý její sloupec musí být opět pojmenován

jedinečným jménem v celé tabulce. Souhrn vlastností v tabulce uložených by měl

co možná nejúplněji celou třídu entit popisovat. Jinak řečeno, přidáme-li do

naší tabulky informace o novém objektu (např. nové knize), měly by k tomu

sloupce v ní obsažené postačit. Život však jde dál a může se stát, že v průběhu

doplňování naší tabulky bude třeba začít ukládat další novou vlastnost celé

třídy entit. Proto současné databázové aplikace dovolují přidávat či

odstraňovat z existující tabulky v databázi sloupce.

Zopakujme, že data v určitém sloupci tabulky ukládají hodnoty určité vlastnosti

třídy entit (rodné číslo, výše platu apod.). Proto jsou tato data též stejného

datového typu, který musíme při zakládání sloupce v tabulce specifikovat.

Běžnými datovými typy jsou např. text, číslo, datum a čas či měna. Pro některé

datové typy (např. text či memo) nutno navíc zadat dodatečnou informaci o

velikosti pole množství paměti pro toto pole vyhrazené; některé datové typy

mají toto přidělené množství paměti vždy stejné (měna, datum a čas, ano/ne),

jiným datovým typům se paměť přiděluje podle velikosti objektu (objekt OLE).

Datové typy Accessu shrnuje následující tabulka. Více informací o nich lze

získat z nápovědy Accessu.

Nutnost definovat typ hodnot ukládaných do každého sloupce tabulky a vše, co s

tím souvisí, pokládá většinou uživatel začátečník za obtěžující (vždyť v Excelu

to dělat nemusel). Omluvou pro tuto v naší uspěchané době další činnost navíc

nechť je fakt, že obrovské množství dat, která do databázových tabulek můžeme

zapsat, musí zabírat co nejméně paměti a databázová aplikace s nimi musí též co

nejefektivněji pracovat. Takto získáváme obrovské výhody, které daleko předčí

časové náklady na určování datového typu polí databázových tabulek.

Co je to normalizace tabulek

Je to sestavení tabulek databáze v takovém stavu, nebo jejich převedení do

takového stavu, který zajistí, že databázová aplikace bude s touto databází

pracovat co možná nejúčinněji. Normalizace tabulek též zabrání nadbytečnosti v

datech databáze. Normalizací se zabývá část teorie relačních databází. Zde opět

odkážeme na již citovanou knihu S. Romana, ve které se o této databázové

disciplině můžete dovědět více. Poznamenejme však, že kniha je určena pro

pokročilejší uživatele. Začátečník by si měl vybrat spíše z literatury citované

v závěru tohoto článku. V dalším si pouze řekneme základní pravidla

normalizace, jimiž bychom se při tvorbě tabulek měli řídit. Některá z nich jsme

již při popisu tabulek databáze uvedli.

- Pole tabulek by měla být atomická. To znamená, že každé pole by mělo

obsahovat pouze hodnoty jedné vlastnosti v tabulce popisované třídy entit.

Vložíme-li např. do jednoho pole tabulky kódující data zaměstnanců jméno a

příjmení, což se nám zpočátku může zdát jako úspora sloupců tabulky, mohou

později vzniknout dodatečné potíže při řazení záznamů této tabulky podle

příjmení.

- V tabulce by mělo být pole primárního klíče, jednoznačně určující každý její

záznam. Tím si připravíme vhodnou situaci pro pozdější definování vztahů mezi

tabulkami.

- Každé pole tabulky by mělo poskytovat dodatečnou informaci o objektu, který

záznam popisuje, a pouze o tomto objektu. Tato důležitá podmínka na jedné

straně praví, že v tabulce by neměla být pole obsahující duplicitní informace.

Na druhé straně však též podtrhuje výše uvedený fakt, že v každé tabulce bychom

měli uvádět informace pouze o jednom typu objektů. Z tohoto pohledu narušují

diskutovanou podmínku klasické ploché databáze tabulkových procesorů, v nichž

dáváme do jedné tabulky údaje o více objektech. V úvodních odstavcích tohoto

článku jsme si vysvětlili, k jakému obrovskému množství duplicit to vede.

Vztahy mezi tabulkami

Z naší definice databáze vyplývá, že mezi tabulkami v databázi uloženými mohou

existovat vzájemné vztahy. Slůvko mohou zde chápejme tak, že tyto vztahy spíše

existují (velice často se s nimi setkáme), i když obecně existovat nemusí. Ve

výše uvedených příkladech jsme popsali vztah rodiče jejich děti a vztah

dodavatelé jimi dodávané výrobky. Vzpomeňme si, že při popisu těchto vztahů

jsme do tabulek doplnili pole primárního klíče, jehož hodnoty jednoznačně

charakterizují každý záznam v tabulce. V tabulce tLidi je např. primární klíč

sloupeček Rodné číslo. Vztahy mezi dvěma tabulkami jsme udělali následovně

(další výklad souběžně porovnávejme se vztahem mezi tabulkami tLidi a tDěti). Z

první tabulky (tLidi) jsme do vztahu zařadili pole primárního klíče (Rodné

číslo). Do druhé jsme doplnili pole obsahující hodnoty primárního klíče první

tabulky a nazvali je Rodič. Toto pole již může obsahovat duplicity. Nazveme je

cizí klíč. Je-li jeho hodnota pro určitý záznam stejná jako hodnota primárního

klíče záznamu v první tabulce, pak je záznam z druhé tabulky ve vztahu „rodič-

dítě“ se záznamem v tabulce první (uvažované dítě z tabulky tDěti má za rodiče

člověka, jehož rodné číslo je uvedeno v poli Rodič záznamu dítěte).

Mezi dvěma tabulkami v databázi rozlišujeme tři typy vztahů (relací).

Označujeme je jako vztah 1:1, 1:N a M:N. Rozeberme si nyní každý tento vztah

podrobněji.

Vztah 1:1

Každý záznam z první tabulky je ve vztahu maximálně s jedním záznamem tabulky

druhé a naopak. Vyjádříme-li to pomocí pojmů primární a cizí klíč, potom

hodnota primárního klíče každého záznamu v první tabulce může souhlasit s

nejvýše jednou hodnotou cizího klíče v tabulce druhé a naopak. Příkladem této

relace může být vztah mezi tabulkou lidí a tabulkou občanských průkazů. Každý

člověk má nejvýše jeden platný občanský průkaz a obráceně, každý občanský

průkaz patří nejvýše jednomu člověku. Relace jedna ku jedné se v běžné praxi

vyskytují zřídka. Někdy nám však pomohou vyřešit následující problém. Počet

polí v tabulkách databázových aplikací bývá shora omezen. U Accessu je to např.

číslo 255. Je-li třeba k charakterizaci určité třídy entit více polí, musíme

udělat tabulky dvě. Vztah 1:1 lze potom realizovat mezi sloupci primárních

klíčů každé z tabulek. Každému záznamu z první tabulky potom odpovídá dokonce

právě jeden záznam z tabulky druhé a naopak.

Vztah 1:N neboli jedna ku více

Každý záznam z první tabulky (je na straně jedna, říkáme jí též mateřská

tabulka) může být ve vztahu s více záznamy z druhé tabulky (je na straně N,

říkáme jí též dceřiná tabulka), a každý záznam z druhé tabulky může být ve

vztahu s nejvýše jedním záznamem tabulky první. Je obvykle výhodné vytvořit

tento vztah jako relaci mezi primárním klíčem mateřské a cizím klíčem dceřiné

tabulky. Potom hodnota primárního klíče každého záznamu v mateřské tabulce může

souhlasit s hodnotou cizího klíče několika záznamů v tabulce dceřiné. Hodnota

cizího klíče každého záznamu v dceřiné tabulce souhlasí s hodnotou primárního

klíče nejvýše jednoho záznamu v tabulce mateřské. Jako příklad relace 1:N

můžeme uvést již diskutovaný vztah lidi a jejich děti. Jako primární klíč v

tabulce tLidi je použito rodné číslo. Každý člověk může mít více dětí a každé

dítě maximálně jednoho rodiče, na něhož je v evidenci vedeno. Tento rodič je v

záznamu dítěte, zde konkrétně v poli Rodič, určen svým rodným číslem. Pole

Rodič je v tabulce tDěti cizím klíčem (viz obr. 3).

Jako další příklad této relace můžeme uvést vztah Knihy Nakladatelství. Jedno

nakladatelství může vydat více knih, jedna kniha však může být vydána pouze

jedním nakladatelstvím.

Vztah M:N neboli více ku více

Každý záznam v první tabulce může být ve vztahu s několika záznamy v druhé

tabulce a naopak. Příkladem této relace je již diskutovaný vztah dodavatelé

výrobky.

Jako další příklad uveďme vztah autoři knihy. Jeden autor může napsat více knih

a opačně, jedna kniha může být napsána více autory.

Každý vztah typu M:N lze vytvořením vhodné spojovací tabulky převést na dva

vztahy typu 1:N. Tento důležitý fakt jsme již demonstrovali na případu

dodavatelé výrobky. Vytvořili jsme tabulku tRozpis se sloupci nazvanými

PORCISDOD a CISVYR. PORCISDOD je sloupec cizího klíče obsahující hodnoty

primárního klíče PORCIS z tabulky tDodavatelé. Vztah mezi tabulkou tDodavatelé

a tRozpis je tedy 1:N. CISVYR je sloupec cizího klíče obsahující hodnoty

primárního klíče CISLO z tabulky tVýrobky. Vztah mezi tabulkou tVýrobky a

tRozpis je tedy opět 1:N.

Každá relace typu M:N se tedy převádí na dvě relace 1:N. Existence vztahu 1:N

mezi dvěma tabulkami je nejčastější. Proto databázové aplikace poskytují pro

manipulaci s tabulkami, mezi nimiž je tento vzájemný vztah, speciální nástroje.

Hodnoty NULL

Neznáme-li při zadávání hodnotu pole záznamu, necháme ji nevyplněnou. V tom

případě má tento atribut hodnotu NULL. Hodnota NULL se může vyskytovat v cizím

klíči. Má zajímavé vlastnosti, více se o ní dočteme v citované literatuře.

Referenční integrita (RI) a její důsledky

Referenční integritu lze nastavit pro tabulky ve vzájemném vztahu 1:N (lze ji

nastavit též pro vztah 1:1, který možno chápat jako zvláštní případ relace

1:N). Zajišťuje, že se při práci se záznamy (vkládání a odstraňování záznamů)

zachovávají definované vztahy mezi tabulkami.

Každému záznamu v tabulce na straně N s hodnotou cizího klíče <> NULL musí

odpovídat právě jeden záznam v tabulce na straně 1. Jinými slovy, v tabulce na

straně N nepřipouštíme osiřelé záznamy.

Vynutíme-li referenční integritu, program Microsoft Access zabrání:

- přidat záznamy do tabulky na straně N, jestliže v první tabulce neexistuje

odpovídající záznam,

- změně hodnoty v tabulce na straně 1, která by mohla mít za následek vznik

osiřelých záznamů v druhé tabulce,

- odstranění záznamů z tabulky na straně 1, jestliže druhá tabulka obsahuje

příslušné související záznamy.

Nadefinujeme-li referenční integritu ve vztahu mezi tabulkami tLidi a tDěti,

nemůžeme potom do tabulky tDěti přidat záznam dítěte, které nemá rodiče v

tabulce tLidi. To je přínos referenční integrity, neboť takový záznam by v

tabulce tDěti (děti našich zaměstnanců) opravdu neměl být.

Představme si však tuto situaci. V tabulce tLidi je primární klíč rodné číslo.

Přijde pan Novák, který má dvě děti a ohlásí nám, že jeho rodné číslo bylo

nesprávné a přinese platné rodné číslo. Access jej však nedovolí změnit, neboť

v tabulce tDěti by se ocitli dva sirotci děti pana Nováka, které mají ve

sloupečku Rodič ještě původní nesprávné rodné číslo svého otce. Access se

samozřejmě nedá přemluvit, že tato situace je jen chvilková, neboť máme vzápětí

v úmyslu tato dvě nesprávná rodná čísla v tabulce tDěti též opravit. Abychom se

však nedostali do popsané patové situace, dovoluje Access po nadefinování

referenční integrity ještě aktivovat možnost Aktualizace souvisejících polí v

kaskádě. V našem případě nám potom nejenže nechá opravit nesprávné rodné číslo,

ale současně je ještě sám opraví u záznamů tabulky na straně N souvisejících se

záznamem v tabulce první (tj. u dětí pana Nováka, kde je uvedeno jako cizí

klíč). Referenční integrita nám též neumožní odstranit z tabulky tLidi záznam

zaměstnance, který má v tabulce tDěti odpovídající záznamy o jeho dětech. Z

těchto záznamů by se totiž staly záznamy osiřelé, což by narušilo samu podstatu

referenční integrity. Proto je po nadefinování referenční integrity ještě

dovoleno aktivovat volbu Odstranění souvisejících polí v kaskádě. Potom nám

Access nejen dovolí odstranit z tabulky tLidi záznam zaměstnance, ale navíc

ještě sám odstraní z tabulky tDěti jeho děti, čímž zde nedojde ke vzniku

osiřelých záznamů.

Dialogové okno Accessu, které dovolí nadefinovat referenční integritu a další

diskutované vlastnosti, je na obr. 8.

Důležité nástroje databázového systému Access pro práci s daty

V úvodních odstavcích tohoto článku jsme si řekli, že v tabulkových procesorech

děláme pouze ploché (anglicky flat) databáze, neboli seznamy. Taková databáze

je tvořena pouze jednou tabulkou s názvy sloupců. Dále jsme uvedli, že

tabulkové procesory nemají nástroje pro práci s klasickými relačními

databázemi, které obsahují více tabulek, mezi nimiž mohou existovat vzájemné

vztahy. Nyní si na konkrétním příkladu databáze MS Access vysvětlíme, které

důležité nástroje k tomuto účelu slouží v databázových programech.

Otevřeme-li v MS Accessu databázi, objeví se její okno (obr. 6), v němž je

možno volit ze sedmi základních skupin objektů:

- Tabulky, které tvoří datovou základnu databáze

- Dotazy, které dovolují formulovat podmínky (jimž musí vyhovět data z tabulek

získaná) a též tato data získat. Pomocí dotazů můžeme rovněž modifikovat data

umístěná v tabulkách.

- Formuláře zobrazují data získaná z tabulek či dotazů vhodnějším, pro běžnou

praxi přirozeným způsobem, a umožňují dělat s těmito daty další dodatečné

výpočty. V prostředí formuláře lze též provádět běžné úpravy dat v naší

databázi. Můžeme říci, že formuláře jsou pro naše pracovní prostředí zdrojem

oken. Poskytují okna pro zobrazování dat v databázi, pro jejich editaci, okna s

nejrůznějšími ovládacími prvky (tlačítka, seznamy apod.). Obsah a úpravu těchto

oken si může nadefinovat tvůrce formuláře. U ovládacích prvků formuláře mohou

nastat různé události. Běžnou událostí je např. klepnutí na tlačítko, volba

určité položky v seznamu, přechod na nový záznam, apod. Jako reakce na tuto

událost může začít nějaká činnost, kterou uživatel předepíše. Tuto činnost

definujeme makry či programy Visual Basicu.

- Sestavy dovolují převést data získaná z tabulek či dotazů do tištěné formy.

- Stránky dovolí prezentovat data z databáze na WWW stránkách.

- Pomocí maker lze definovat automatické provedení určitých akcí. Spuštěním

makra v reakci na určitou událost jsou potom tyto akce provedeny.

- Moduly umožňují vytvářet v Accessu jazykem Microsoft Visual Basic programy,

které po spuštění s našimi daty efektivně pracují.

Datovou základnou každé relační databáze jsou tabulky, mezi nimiž existují

vhodně nadefinované vztahy. Výkonným prostředkem pro získávání dat z tabulek a

pro jejich úpravy jsou dotazy. Proto se v dalším soustředíme na tyto dvě

součásti prostředí MS Access.

Tabulky

Děláme-li novou tabulku, opatříme ji vhodným názvem a specifikujeme každý její

sloupec pole. Jde zejména o určení názvu pole a datového typu pole. Při práci s

Accessem uvidíme, že pole mají ještě další vlastnosti.

Pro snadné vytváření pole primárního klíče poskytuje Access následující

možnosti. Zadáváme-li toto pole sami, kontroluje jeho hodnotu s ohledem na

duplicity. Záznam s duplicitní hodnotou v poli primárního klíče nám do tabulky

nepovolí zadat. Access však též obsahuje datový typ automatické číslo.

Nastavíme-li tento typ pro pole primárního klíče, generuje potom Access jeho

hodnoty pro každý záznam sám (jako přirozené číslo) a nemusíme se starat o

zajištění jeho jednoznačnosti.

Jak nám Access usnadní zadávání pole cizího klíče? Připomeňme, že pole cizího

klíče obsahuje hodnoty pole primárního klíče jiné tabulky. Počet duplicit

určité hodnoty v poli cizího klíče určuje, kolik záznamů naší tabulky na straně

N vztahu 1:N odpovídá jedinému záznamu tabulky jiné, na straně 1 vztahu 1:N,

který má tuto hodnotu primárního klíče. Představme si, že bychom do naší

tabulky museli zadávat hodnoty cizího klíče explicitně, ať to jsou např. rodná

čísla lidí, nebo prve zmíněná automatická čísla vygenerovaná Accessem. Popsané

nepříjemné práci zabrání průvodce vyhledáváním. Tuto položku volíme jako datový

typ cizího klíče. Vzápětí se průvodce spustí a ptá se, ze které tabulky chceme

čerpat hodnoty, jaké hodnoty chceme do pole cizího klíče ukládat a jaké hodnoty

zde chceme zobrazovat. Zadáváme-li např. cizí klíč Rodič do tabulky tDěti jako

primární klíč z tabulky tLidi, můžeme si místo něj nechat např. zobrazovat

příjmení lidí z této tabulky. Pro vyplnění pole Rodič pro určitý záznam

poskytuje potom průvodce vyhledávání rozbalovací seznam příjmení lidí, z něhož

si vybíráme. Hodnotou, která se do pole však ukládá, je rodné číslo člověka,

jehož příjmení jsme si vybrali. Takto se vyhneme práci s těžko

kontrolovatelnými hodnotami primárních klíčů a nahradíme je hodnotami jiného

pole, které jsou pro nás daleko přijatelnější. Z obr. 3 a 5 vidíme, že si v

poli cizího klíče můžeme dokonce zobrazit i více hodnot (např. příjmení rodiče

a jeho rodné číslo nebo název výrobku a jeho číslo).

V Accessu 2000 lze navolit takové podmínky, že po otevření tabulky, která je ve

vztahu 1:N na straně jedna, lze pro každý její záznam zobrazit odpovídající

záznamy druhé tabulky (na straně N). Před každým záznamem tabulky na straně

jedna je totiž znaménko plus. Klepnutím na něj tyto odpovídající záznamy z

dceřiné tabulky zobrazíme (obr. 7).

Definice vztahů mezi tabulkami

Je-li aktivní okno databáze, stačí otevřít okno Relace (tlačítkem na panelu

Databáze) a potom do tohoto okna umístit zástupce tabulek, mezi nimiž chceme

vztah vytvořit (po klepnutí na tlačítko Přidat tabulku panelu Relace volíme

názvy tabulek ze seznamu). Vztah tvoříme přetažením pole mateřské tabulky

(nejčastěji je to pole primárního klíče) na odpovídající pole dceřiné tabulky

(obvykle je to pole cizího klíče). Zobrazí se okno Upravit relace (obr. 8), v

němž zkontrolujeme pole pro relaci vybraná a provedeme další volby (zajištění

referenční integrity apod.).

Vytvořený vztah je v okně Relace znázorněn spojnicí zvolených polí. Chceme-li

změnit vlastnosti hotové relace, stačí poklepat v okně Relace na tuto spojnici.

Objeví se okno Upravit relace, v němž změny provedeme. Je-li třeba náš vztah

odstranit, klepneme na spojnici myší a stiskneme klávesu Delete. Na obr. 8 je

vztah typu 1:N mezi tabulkami tLidi a tDěti, na obr. 9 je vztah M:N mezi

tabulkami tDodavatelé a tVýrobky převeden na dva vztahy 1:N, tak jak jsme si

vysvětlili výše.

Dotazy

Nejmocnějším prostředkem k získávání informací z tabulek databáze a zároveň

prostředkem k dodatečné změně těchto tabulek jsou dotazy. Dotaz je vlastně

příkazem specifikujícím práci (výběr, modifikace) s daty, data samotná dotaz

neobsahuje. Pracuje vždy s aktuálními daty tabulek či dotazů a jím získané

výsledky mohou být použity jako základ pro tvorbu dalších dotazů, tabulek,

formulářů a sestav.

Rozeznáváme dva základní druhy dotazů. Pasivní neboli výběrové dotazy, které

pouze vybírají a zobrazují data. Aktivní neboli akční dotazy dělají dokonce

změny v tabulkách.

Pro tvorbu jednoduchých dotazů je v Accessu k dispozici průvodce. Složitější

dotazy děláme v návrhovém zobrazení dotazu. Každý dotaz je v něm reprezentován

samostatným oknem. V dalším výkladu popíšeme prostředí okna výběrového dotazu.

V záhlaví okna dotazu je uveden název a typ dotazu. Do tabulkového panelu pod

titulním pruhem umisťujeme zpracovávané tabulky. Stačí vykonat příkaz Zobrazit

tabulku z místní nabídky panelu (získáme ji klepnutím pravým tlačítkem myši na

panelu). Každá tabulka je na panelu reprezentována obdélníkem obsahujícím její

pole. Pod tabulkovým panelem je panel obsahující kriteriální mřížku. Do sloupců

této mřížky umisťujeme pole zpracovávaných tabulek, která chceme zobrazit jako

výsledek dotazu, nebo na která klademe nějaké kriterium. Chceme-li pole

kriteriální mřížky zobrazit, necháme jeho řádek Zobrazit zaškrtnutý. Klademe-li

na určité pole mřížky kriterium, zapisujeme jej do řádků Kritéria a nebo tohoto

pole. Pro tvorbu kritérií platí přitom dvě důležitá pravidla. Kriteriální

podmínky, které mají platit, zároveň zapisujeme do téhož řádku mřížky.

Kriteriální podmínky spojené spojkou „nebo“ kódujeme naopak na různé řádky

mřížky. Pro zajímavost uveďme, že lze vytvářet i parametrické dotazy. Po

spuštění takového dotazu se objeví okénko, do něhož zadáme aktuální hodnotu

parametru.

Na obr. 10 je návrhové zobrazení výběrového dotazu s parametrem. Dotaz zobrazí

z tabulky Zákazníci pole Firma a Země, z tabulky objednávky pole

DatumObjednávky, z tabulky Výrobky pole NázevVýrobku a konečně z tabulky Rozpis

objednávek pole Množství. Hodnotu pole Země zadáváme jako parametr po spuštění

dotazu. Na pole DatumObjednávky klademe kriterium, že objednávka musí být z

roku 1998.

Literatura:

Jan Pokorný: Velká kniha základů Microsoft Access 2000 vydal Unis 2000.

Exaktně napsaná publikace o 336 stranách. Je psána srozumitelně, bez zbytečných

slov. Všechny důležité informace pro začínající i pokročilé uživatele jsou v

knize obsaženy.

John Viescas: Mistrovství v Microsoft Access 2000 vydal Computer Press 2000.

Velice obšírný výklad na 836 stranách, proložený řadou příkladů. Knihu

doprovází CD.

Edward Jones, Jarel M. Jones: Access 2000 odpovědi na nejčastější otázky vydal

Grada Publishing, 1999.

Psáno stylem vhodně volených otázek a odpovědí na ně. Vše podáváno

srozumitelným způsobem. K tomu, aby kniha byla dokonalá, však z ní zbývá

odstranit některé chyby.

Modul 5 databáze

Modul vyžaduje, aby kandidát porozuměl základní koncepci databází a předvedl

schopnost využívat těchto znalostí při práci na osobním počítači.

Je rozdělen do dvou částí; první část testuje kandidátovu schopnost návrhu a

naplánování jednoduché databáze s využitím standardních databázových postupů;

druhá část vyžaduje, aby kandidát předvedl, že umí získávat a zpracovávat

informace z již existující databáze (konkrétně s využitím funkcí dotaz, výběr a

třídění). Pro druhou část testu testovací středisko poskytuje kandidátovi

hotovou databázi ECDL, předem připravenou příslušným softwarem. Databáze se

vždy skládá ze dvou tabulek a dvou formulářů a obsahuje dostatečný počet

věrohodných dat. V rámci testu je na kandidátovi požadováno i předvedení práce

s tiskovými sestavami, vytvořenými na základě dotazů z databáze.

Témata modulu jsou rozdělena do těchto hlavních oblastí:

- filosofie práce s databází

- vytvoření seznamu

- definice textových a číselných polí

- výběr položek a jejich zpracování

- ukládaní dat

Test 5.1

5.1.1 Jednoduché úlohy

Vaším úkolem je vytvořit databázovou tabulku výrobků.

1.Vytvořte tabulku s 5 poli. Použijte odpovídající datové typy. Rozlišujte

text, čísla a měnu. Použijte odpovídající velikost pole.

- Název výrobku

- Dodavatel

- Množství v jednotkách

- Jednotková cena

- Počet na skladě

2.Do své nové databáze vložte nejméně pět kompletních záznamů.

3.Setřiďte sestupně tabulku podle pole Počet na skladě.

4.Uložte svou tabulku pod názvem Výrobky.

5.1.2 Složitější úlohy

1.Vytvořte přehled všech výrobků bez ostatních informací. Uložte dotaz pod

názvem Přehled

2.Nalezněte všechny výrobky s nadprůměrnou cenou. Uložte dotaz pod názvem

Nadprůměrná cena.

3.Nalezněte nejlevnější a nejdražší výrobek. Uložte dotaz pod názvem MiniMax.