Vztahy mezi tabulkami

Cvičení je zaměřeno hlavně na to jak v MS Access něco udělat, otázku jak navrhnout strukturu databáze budeme řešit později. Při návrhu databáze celkem rychle narazíte na situaci, kdy se v tabulce některá data opakují. V příkladu z minulého cvičení je třeba vytvořit databázi s údaji o lodích, jejich majitelích a přístavech ve kterých jsou lodě registrované. Je možné navrhnout například následující tabulku lodí:

Je vidět, že údaje o majiteli, přístavu a typu lodě se opakují. Je samozřejmě možné nechat je v jendé tabulce, ale bylo by rozumnější tabulku rozdělit a vytvořit další, ve kterých budou potřebné údaje uložené jen jednou. Už z logiky zadání vyplývá, že je třeba udělat samostatnou tabulku s údaji o majitelích a přístavech (jinak by se u každé lodě uchovával záznam i o adrese majitele, velikosti přístavu a dalších věcech, které může být třeba do databáze vložit). To má několik výhod. Jednak se v databázi neopakují zbytečné věci, jednak je možné údaje snadno upravovat. Řekněme, že je třeba ukládat adresu majitele lodě. Pokud je uložena u každé lodi zvlášť, bude nutné ji při změně upravit u všech položek a na žádnou nezapomenout. Pokud je ale ve zvláštní tabulce s údaji o majiteli, stačí ji změnit jen jendou a nehrozí, že někde zůstane chybná.

V našem příkladu nyní máme tři sloupce, které jsou kandidáty na nové tabulky. Jde o majitele, domovský přístav a typ lodě. První dva slouží k popisu skutečných existujících objektů (lidí a přístavů), třetí slouží spíš jako nový datový typ - dá nám skupinu možností ze kterých bude možné vybírat typ lodě. Taková tabulka se označuje jako číselník. Typický číselník je tabulka jen se dvěma sloupci, jeden slouží jako umělý klíč (viz níže), druhý obsahuje nějakou informaci. Typickým příkladem by mohl být seznam států, seznam typů vozidel a podobně.

Tabulky je nutné propojit, aby bylo poznat který majitel ke které lodi patří, toto propojení se označuje jako relace. K vytvoření propojení slouží primární klíč tabulky.

Primární klíč

Primární klíč slouží k jendoznačné identifikaci záznamu v tabulce. Musí mít dvě vlastnosti - je jedinečný v celé tabulce a vždy musí být vyplněn. Může jít o přirozený klíč (něco co je každé položce tabulky vlastní a zároveň jedinečné, například rodné číslo) nebo o umělý klíč, který přiděluje sama databáze (k tomu slouží automatické číslo). V případě potřeby je možné, aby byl klíč tvořen několika sloupci tabulky, ale to komplikuje jeho použití v relacích, proto se v takovém případě obvykle používá umělý klíč.

Cizí klíč

Cizí klíč slouží k propojení tabulek. Představuje odkaz vedoucí k záznamu v jiné tabulce. Použití je nejlépe vidět na příkladu. Mějme tabulku lodí a tabulku jejich majitelů. Každý majitel je jednoznačně identifikován umělým klíčem. V tabulce lodí je sloupec majitel, nyní se do něj ukládá číslo majitele lodi - tedy primární klíč z tabulky majitelů. To umožní jak dohledat majitele každé lodi, tak najít všechny lodě jednoho majitele.

Dělení tabulky

Při dělení tabulky (nebo rovnou při jejím návrhu) je třeba si dobře rozmyslet, která data k sobě patří. V našem případě vytvoříme nové tabulky s údaji o majitelích, přístavech a typu lodě (případně použijeme už hotvé). Doplníme další údaje, které chceme o majiteli a přístavu uchovávat. Do tabulky s loděmi přidáme tři nová pole, která budou sloužit jako cizí klíče pro majitele, přístavy a typ. Jejich datový typ musí být "Číslo" ve formátu "Dlouhé celé číslo".

Propojení tabulek 1:N

Když jsou tabulky hotové, můžeme se pustit do jejich propojení. Jaké relace máme? Každá loď má právě jednoho majitele, jeden majitel může mít několik lodí (kdyby mohl mít jen jednu loď, dalo by se uvažovat o spojení majitelů a lodí v jedné tabulce). Loď může být registována právě v jednom přístavu, ale samozřejmě v přístavu může být několik lodí. A konečeně, každá loď je jen jendoho typu. Takový druh vazeb označujeme jako 1:N a realizujeme pomocí cizího klíče. Označení 1:N naznačuje, že na jedné straně relace je jen jeden prvek (v našem případě třeba majitel) a na druhém jich může být několik (v našem případě lodě). Do tabulky lodí tedy přidáme 3 cizí klíče - jeden pro majitele, jeden pro přístavy a jeden pro typ, pojmenujte je tak, ať je snadno poznáte.

Teď je nutné nastavit relace, najdete je v záložce "Databázové nástroje", pod ikonou "Vztahy". Objeví se okno pro vytváření relací a měla by se objevit i tabulka se seznamem všech tabulek které v databázi máte. Pokud je nevidíte, můžete je otevřít pomocí ikonky "Zobrazit tabulku ..." v záložce "Návrh". Vložte všechny tabulky, které v databázi máte (v příapadě potřeby je možné vkládat i dotazy). K propojení majitelů a jejich lodí přetáhněte primární klíč z tabulky majitelů do tabulky lodí, nejlépe na pozici s cizím klíčem určeným pro majitele, který jste si připravili. Objeví se okno, ve kterém můžete nastavit které položky se spojí - zkontrolujte, jestli je na straně tabulky majitelů vybrán primární klíč a na straně lodí správný cizí klíč. Zaškrtněte políčko "zajistit referenční integritu", ale nic dalšího.

Jen pro zajímavost, pokud máte tabulku naplněnou daty, Access je schopen navrhnout její rozdělení na základě toho jaká data v ní jsou. Podívejte se na ukázku:

Funkce je schovaná v záložce "Databázové nástroje", pod ikonou "Analyzovat tabulku", ale funguje jen pokud v tabulce jsou nějaká datalí tabulku na základě jejich analýzy. Ikonou spustíte průvodce, kterým se musíte proklikat tlačítkem "Další". Průvodce vás požádá o výběr tabulky k rozdělení a v dalším kroku musíte vybrat, že chcete, aby rozdělení proběhlo automaticky. Access vám nabídne strukturu tabulek, propojených novými cizími klíči a které je nutno pojmenovat. Pozor na to, dělení není dokonalé. Access neví nic o vašich datech, jen se snaží hledat která by mohla patřit k sobě na základě mechanismu normálních forem (budeme se mu věnovat později). Nemusí fungovat dokonale, v ukázce si všimněte, že vznikla tabulka obsahující státy, ale ne domovské přístavy, přitom jde o data která patří k sobě. Čím víc je v tabulce dat, tím větší je šance že bude automatické dělení fungovat (samozřejmě pokud v datech nejsou chyby).

Propojení tabulek M:N

Může nastat i trochu složitější situace. Řekněme, že bychom náš příklad chtěli upravit tak, aby jedna loď mohla mít víc vlastníků, kteří se na jejím prvozu podílí. V takovém případě nemůžeme situaci vyřešit tak, že bychom přidali cizí klíč i k lodi i k vlastníkovi a zároveň nestačí aby byl jen ja jedné straně - chceme aby jedna loď měla víc vlastníků a zároveň jeden vlastník mohl vlastnit víc lodí. Jde o druh vazby M:N a Access (ani jiné databázové stroje) ji neumožňuje vytvořit přímo. Musíme vytvořit něco, čemu se říká "rozkladová tabulka", která vazbu popisuje. V našem případě bude popisovat relaci "Vlastnictví" a bude mít dvě položky, dva cizí klíče - ID lodě a ID majitele.

Tabulku vytvoříte stejně jako v předchozích případech přes nástroje pro tvorbu tabulky. Obě položky musí být typu "Dlouhé celé číslo", aby se mohly svázat s automatickým číslem. Všimněte si, že pole "Majitel" v tabulce "Lodě" je nyní zbytečné a můžeme ho odstranit. Všimněte si také kardinality vazeb u rozkladové tabulky - kardinalita 1 je vždy na tom konci vazby, kde je primátní klíč.

Rozkladová tabulka může samozřejmě obsahovat i další informace, nemusí v ní být jen cizí klíče. V našem případě by mohlo jít třeba o procento jakým se jednotliví vlastníci na lodi podílejí.

Kromě uvedého může existovat i vazba 1:1, kdy jsou relací propojeny primární klíče obou tabulek. V takovém případě jednomu záznamu z jedné tabulky odpovídá práve jeden záznam z druhé tabulky. Taková vazba se nepoužívá příliš často, je zřejmé, že stejně dobře může většinou posloužit i vložení všech takových údajů do jedné tabulky. Může se ale hodit v případě že by na jendé tabulce záviselo i něco jiného, nebo je nevhodné sloučit data o dvou různých objektech reálného světa do jedné tabulky.

Integritní omezení

Integritní omezení jsou dalším způsobem jak ohlídat, aby v tabulce nebyly nesmyslné údaje. Zatím jste viděli možnost nastavit omezení kladená na jednotlivá pole v tabulce, integritní omezení umožňují nastavit chování vazeb.

Při vytváření relace můžete zvolit tři základní možnosti: Zajistit referenční integritu a v případě že je zajištěna také aktualizaci a mazání souvisejících polí v kaskádě. Nastavení těchto možností donutí Access zajistit, aby v databázi nedošlo k některým druhům nekonzistence.

Pozor na to, že pokud nastavujete relace a referenční integritu u tabulek obsahujících nějaká data, budete mít problémy v okamžiku, kdy data v tabulkách nesplňují podmínky referenční integrity - Access v takovém případě nedovolí její nastavení dokud data neupravíte. Jinak řečeno, referenční integrita má zabránit vzniku chyb v tabulkách. Pokud v nich už chyby jsou, nepůjde nastavit.

Zajistit referenční integritu

Tato volba nutí uživatele, aby do pole cizího klíče na straně tabulky v relaci vyplňoval jen takové hodnoty které už existují jako primární klíč v druhé tabulce. V případě tabulky "Lodě" a "Přístavy" to znamená, že nebude možné do tabulky lodí vyplnit ID přístavu, který zatím neexistuje. Je tedy nutné nejprve vytvořit přístav, podívat se na jeho ID a to pak vložit do tabulky lodí.

Referenční integrita tedy zabraňuje tomu, aby loď měla neexistujího majitele, byla nahlášena v neexistujícím přístavu a tak dále. Také ale zabrání tomu smazat přístav, ve kterém jsou hlášeny nějaké lodě. Je tedy nejprve nutné lodě vymazat, nebo zařadit do jiného přístavu, a teprve pak je možné smazat přístav.

Aktualizace souvisejících polí v kaskádě

V příkladech zatím jako klíč sloužila vždy automaticky určená hodnota, která se nemění. Co ale dělat v případě, že je možné ručně změnit primární klíč? V případě že je nastaveno zachování referenční integrity a v druhé tabulce exituje související záznam, Access to nepovolí. Pokud bychom totiž změnily číslo přístavu, lodě co jsou v něm zařazené by najednou nebyly zařazené nikde (respektive byly by v neexistujícícm přístavu) a naopak by se mohlo stát že se do něj najednou zařadí jiné lodě. Primární klíč se naštěstí většinou nemění, ale co když chceme třeba opravit chybu?

Nastavení Aktualizace zajistí, že změna primárního klíče v tabulce se přenese do všech tabulek se kterými je svázaný - pokd změníme ID přístavu, změní se automaticky i u lodí (a změna by se případně propagovala i do dalších vazeb). Tato volba je obvykle bezpečná a nejsou s ní problémy.

Mazání souvisejících polí v kaskádě

Druhá situace nastane, pokud chceme údaje mazat. Při nastavení referenční integrity Access nepovolí smazání položky ke které existují související záznamy. pokud tedy zkusíme smazat přístav ve kterém jsou nějaké lodě, nebude taková operace povolena a lodě je nutné nejdříve ručně přesunout do jiného přístavu nebo je smazat.

Nastavení Mazání souvisejících polí v kaskádě zajistí smazání všech údajů, které s mazaným záznamem souvisely. To může být užitečné, ale ne vždy je to rozumné. Třeba při smazání přístavu by to vyvolalo také smazání všech lodí, takže povolení takové operace není právě bezpečné ani rozumné. Ale na druhé straně, při smazání lodě by bylo dobré, kdyby zmizel záznam z tabulky vlastnictví, takže u takové tabulky se mazání v kaskádě může hodit.

Všimněte si, že kaskáda funguje jen jedním směrem - pokud se maže z tabulky na straně "1", ovlivněné záznamy jsou na straně "N" (nebo nekonečno v Accessu). Ty se budou mazat nebo aktualizovat, naopak to nefunguje.

Jiné DB systémy umožňují při mazání nebo aktualizaci záznamu nastavit hodnoty v souvisejících polích na NULL (pokud je to pro ně přípustná hodnota), což je užitečné, ale v Accessu obtížněji dosažitelné. V případě zájmu se můžete podívat na anglický návod jak v kaskádě nastavit NULL.

Cvičení

Podívejte se na své databáze lodí. Najděte v nich potenciální číselníky a vytvořte jim příslušné tabulky. Zajistěte propojení lodí, přístavů a majitelů, taky aby loď mohla být jen v jendom přístavu, ale mohla mít několik majitelů. nastavte vhodně referenční integritu. Při úpravách tabulek a práci s referenční inegritou je nejlepší z nich testovací data vymazat a pak je znovu naplnit (pokud tedy nejde o data která potřebujete doopravdy uchovat, pak je to o něco složitejší).