Přečtete si, jak udělat vazbu mezi tabulkami a spojíte vazbami tabulky databáze Knihovna. Čeká na vás i pár dalších úkolů a pro zopakování dílu otázky na závěr. Nejdřív se ale můžete podívat na odpovědi k otázkám z minulého dílu.
Odpovědi na otázky ze shrnutí třetího dílu
Práce s Base 2, Teorie 2
-
Co především zadává tvůrce databáze při založení tabulky v režimu návrhu?
Název sloupce a datový typ hodnot jeho položek. Navíc je možné zadat vysvětlující komentář a upřesnit datový typ, např. zda hodnota má být u primárního klíče systémem automaticky zadávána, jaká má být maximální délka textu nebo čísla apod.
-
Jaké datové typy použijete pro položky KřestníJméno, VěkOsoby, PoznámkaKeKnize, KnihaID?
Pro KřestníJméno zvolíme typ text [VARCHAR], který představuje text proměnné délky až do zadaného maximálního počtu znaků, u sloupce PoznámkaKeKnize vybereme typ Memo [LONGVARCHAR], což by měl být text proměnné délky až do více než dvou miliard (2 GB) znaků. Vyzkoušel jsem vložit do položky typu Memo delší text – asi 20 000 000 znaků (děkuji při té příležitosti panu Pastierikovi za rozšíření JP Lorem Ipsum generator pro Writer, to mi dost pomohlo), ale Base mě upozornil, že „Délka vloženého textu překročila maximální délku v tomto poli. Text byl zkrácen“ a vloženo bylo jen 65 455 znaků, což je necelých 64 kB znaků.
Nevím tedy, jestli jsou údaje ze zdrojů, uvedených níže, zcela spolehlivé. Stejný počet znaků se mi podařilo vložit do položky typu Varchar, i když uvedené zdroje i zde uvádějí maximální délku 2 GB znaků. Podaří se tyto nejasnosti vyřešit třeba v diskusi k článku? U pole VěkOsoby zvolíme zřejmě typ Integer (celé číslo), ale vyberme úspornější: Base nabízí Tiny Integer [TINYINT], který potřebuje pro hodnotu jen 1Byte a vyjadřuje čísla od 0 do 255. Sloupec KnihaID je zřejmě primárním klíčem tabulky. Bude pro něj vhodný typ Integer s automatickým zvyšováním hodnoty u dalšího záznamu. Podrobnější informace o datových typech v Base najdete např. na wiki stránceOpenOffice.org nebo v podrobném manuálu k Base (pdf, 189 stran, 2010).
-
Pokud bude primární klíč tabulky typu Integer, může SŘBD (např. Base) správci databáze ulehčit zadávání dat. Jak?
Automatickým zadáváním – zvyšováním hodnoty primárního klíče.
-
Jaký je úkol cizího klíče? Co musí platit pro datový typ cizího klíče a jemu odpovídajícího primárního klíče z druhé tabulky?
Cizí klíč vytváří vazbu na jinou tabulku tím, že jeho hodnoty odkazují na odpovídající hodnoty primárního klíče v druhé tabulce. Datové typy cizího klíče a souvisejícího primárního klíče musí být stejné.
-
Jaká jsou integritní omezení, která musí splňovat primární klíč tabulky?
Hodnoty primárního klíče musí být jedinečné (unikátní) – nesmí se v buňkách primárního klíče opakovat. Protože primární klíč jednoznačně určuje (identifikuje) záznamy, nesmí jeho hodnota v žádném záznamu chybět – to se často vyjadřuje podmínkou „Not NULL“, kde NULL neznamená nulu, ale chybějící, nezadanou hodnotu.
-
Jak se ve schématech databáze značí primární a cizí klíče?
Primární klíč značíme běžným podtržením, cizí klíč je podtržen přerušovanou čarou.
Poznámka redakce: Z technických důvodů budou aspoň zatím ve schématech databází na našem webu v seriálu značeny všechny výskyty primárních klíčů tučným písmem namísto standardního podtržení, cizí klíče pak kurzivou místo podtržení čárkovaného.
Teorie 3: Další vlastnosti primárního a cizího klíče, integritní omezení
Zopakujme si, že vazby se tvoří pomocí primárních a cizích klíčů. Zjistili jste už, že pro jednoznačné určení (identifikaci) záznamů v tabulce se používá primární klíč, zatímco pro vytvoření vazby mezi tabulkami slouží cizí klíč. Jaké podmínky musí splňovat tyto klíče? Již víte, že:
Hodnoty primárního klíče musí být unikátní a že u žádného záznamu položka ve sloupci primárního klíče nesmí chybět! To jsou integritní omezení pro primární klíč.
Promyslete ještě, jak je to s hodnotami cizího klíče:
-
Mohou se v tabulce opakovat?
-
Může v některé buňce ve sloupci cizího klíče hodnota chybět?
-
Jsou hodnoty ve sloupci cizího klíče nějak omezeny?
Pokud chcete s nalezením odpovědí pomoci, pročtěte si následující úvahy:
Primární klíč (např. AutorID v tabulce Spisovatel) musí být unikátní, ale v tabulce Kniha se jistě ve sloupci AutorID (který je cizím klíčem, vytvořeným zde právě kvůli vazbě s tabulkou Spisovatel) může několikrát opakovat stejná hodnota – totiž u knih stejného autora. Hodnoty cizího klíče se tedy mohou opakovat.
Je možné, že v okamžiku zadávání nové knihy do naší databáze autora knihy neznáme nebo zrovna nemáme čas jej zadávat. V tom případě zůstane políčko ve sloupci cizího klíče u tohoto záznamu prázdné. Ve sloupci cizího klíče tedy nemusí být ve všech buňkách zadané hodnoty.
Měly by být hodnoty cizího klíče něčím omezené? Pokud by ve sloupci cizího klíče AutorID v tabulce Kniha bylo číslo, které se nenachází ve sloupci odpovídajícího primárního klíče z tabulky Spisovatel, odkazovala by se tabulka Knihy na autora, který v databázi neexistuje! Další vlastností cizího klíče tedy je, že nesmí obsahovat hodnotu, která by nebyla obsažena ve sloupci odpovídajícího primárního klíče připojené tabulky. To je takzvané pravidlo referenční integrity, jeho kontrola systémem řízení báze dat je další pojistkou správnosti databáze.
Shrnutí základních vlastností cizího klíče
-
Hodnoty cizího klíče se mohou v jeho sloupci opakovat.
-
Některé buňky ve sloupci cizího klíče mohou být prázdné (nemusí obsahovat žádnou zadanou hodnotu).
-
Cizí klíč nesmí obsahovat hodnotu, která by nebyla obsažena ve sloupci odpovídajícího primárního klíče připojené tabulky (pravidlo referenční integrity).
Porovnání s vlastnostmi primárního klíče (integritní omezení pro primární klíč)
-
Hodnoty primárního klíče musí být unikátní – nesmí se v tabulce opakovat.
-
Hodnota primárního klíče nesmí v žádné buňce ve sloupci tohoto klíče chybět.
Z příkladu k cizímu klíči a z jeho vlastností vyplývá, že se obvykle k jednomu záznamu z tabulky, kde je vazba uskutečněna primárním klíčem, dá navázat více záznamů z tabulky, která je připojena cizím klíčem. Typicky jde tedy o vazbu typu 1 – N.
Řešení úkolu č. 4 z předchozího dílu
(Úkol 4: Navrhněte strukturu tabulky Obor, která bude obsahovat obory knih naší databáze, dál strukturu tabulky Ctenar, ve které budou data o čtenářích, půjčujících si knihy z knihovny. Potom obě tabulky vytvořte v Base a aspoň u jedné z nich použijte režim návrhu.)
Tabulky Obor a Ctenar by mohly mít tato schémata (primární klíče by měly být podtrženy, zde jsou psány tučně, viz poznámka redakce výše):
-
Obor (OborID, Obor)
-
Ctenar (CtenarID, Prijmeni, Jmeno, TridaPuvodni, DatumTridyPuvodni, TridaSoucasna)
Navržená struktura tabulky Ctenar odpovídá použití databáze ve škole: v záznamu čtenáře je i třída, kterou navštěvuje. V takovém případě by bylo asi dobré mít v databázi údaj o třídě, do které v aktuálním roce student skutečně patří. Toto by databáze měla počítat sama, ale jako pomocný údaj se při zápisu čtenáře zadá jeho původní třída a datum zápisu. Položky Prijmeni a Jmeno budou typu VARCHAR s maximální délkou např. třiceti znaků. Pro typ položky Trida a TridaSoucasna navrhuji Text (fix) [CHAR] s pevnou délkou tři znaky (vysvětleno o několik odstavců níže). DatumTridyPuvodni bude typu datum (Date).
U tabulky Obor jsou ale dva důvody proto, abychom ji navrhli ještě jednodušeji:
-
Bude obsahovat jen několik (čili konečný počet) hodnot, zde oborů knih. Takový sloupec tabulky se nazývá číselník. Uveďme si pro představu příklady dalších možných číselníků: barva výrobku z nabízené konečné palety barev (bílá, krémová, žlutá...), třída studentů gymnázia (1.A, 2.A...). Číselník se může vytvořit jako tabulka s jediným sloupcem hodnot číselníku, které se stanou zároveň primárním klíčem.
-
Když tabulku Obor zjednodušíme do formy číselníku s jediným sloupcem, bude se nám s ní v Base pracovat jednodušeji.
Z těchto důvodů vytvoříme tedy tabulku obor jako číselník s jediným sloupcem, který bude zároveň hodnotou i primárním klíčem. Hodnotou by měl být název oboru čili text s datovým typem text [VARCHAR] s maximální délkou třeba třiceti znaků. Schéma tabulky je pak toto:
-
Obor (Obor)
Zároveň se nabízí pro případ školní knihovny možnost vytvořit ještě další číselník – tabulku, která bude obsahovat nabídku tříd, ze které může pak knihovník vybírat při zápisu nového studenta. Mohla by mít toto schéma:
-
Trida (Trida)
Hodnoty sloupce Trida by mohly být opět texty typu text [VARCHAR] s maximální délkou např. tři znaky pro označení tříd způsobem 1A4, 2B6, 7C8 pro čtyřleté, šestileté nebo osmileté studium na gymnáziu. Pokud ale máme jistotu, že všechny hodnoty sloupce tabulky budou stejné délky, bylo by výhodnější použít typ Text (fix) [CHAR], protože odpadá možnost ušetření paměti u kratších položek a práce s hodnotami tohoto typu je přece jen pro databázi o něco snazší než s texty proměnné délky text [VARCHAR]. Navrhuji proto zvolit u jediného sloupce tabulky Trida právě tento typ Text (fix) [CHAR] s délkou tři znaky (nebo jinou dle potřeby), ať ho taky vyzkoušíte.
Změny v původních tabulkách
Z předchozích rozhodnutí plyne, že bychom měli provést změnu i v jedné starší tabulce databáze, a to v tabulce Kniha. Její současné schéma i s vyznačením typů je toto:
-
Kniha (KnihaID (Integer), Nazev (text), AutorID (Integer), OborID (Integer))
a je jasné, že poslední sloupec OborID má být cizím klíčem k tabulce Obor. Rozhodli jsme se ale tuto tabulku udělat jako číselník s jediným sloupcem, který bude typu Text [VARCHAR]. Proto i cizí klíč musí být stejného typu a pojmenujme ho raději stejně jako odpovídající primární klíč, tedy jen Obor. Schéma tabulky bude pak toto:
-
Kniha (KnihaID (Integer), Nazev (text), AutorID (Integer), Obor (text)).
Shrnutí tabulek databáze Knihovna
Uvádím nejdřív dvě tabulky, které už databáze obsahuje a přidávám schémata tabulek nových – dle předchozích odstavců. Nově jsou vyznačeny další cizí klíče.
-
Spisovatel (AutorID, Příjmení, KřestníJméno).
-
Kniha (KnihaID (Integer), Nazev (text), AutorID (Integer), Obor (text)).
-
Obor (Obor (text)) ... číselník pro obory.
-
Ctenar (CtenarID, Prijmeni, Jmeno, TridaPuvodni, DatumTridyPuvodni, TridaSoucasna).
-
Trida (Trida) ... číselník pro třídy, typu Text (fix) [CHAR], stejný typ mají dva sloupce tabulky Ctenar.
Úkol 5: Zkopírujte si nebo na papír napište uvedená schémata a vyznačte v tomto přehledu vazby mezi tabulkami. Doplňte také násobnost vazeb pomocí symbolů 1, N jako ve schématu v předchozím dílu.
Úkol 6: Vytvořte nebo upravte v databázi tabulky Obor, Ctenar a Tridy podle předchozího návrhu.
Úkol 7: Podnět k zamyšlení. Zkuste najít, mezi kterými tabulkami zřejmě ještě v databázi vazba chybí. Odpověď i s komentářem najdete na konci tohoto dílu.
Teorie 4: Druhy vazeb mezi tabulkami
Používají se tři typy vazeb:
-
Vazba 1 – 1 (vždy jeden záznam jedné tabulky k jednomu záznamu druhé), je to méně používaná vazba. Příklad: Tabulka Kniha může obsahovat základní údaje o knize (a cizí klíč na autora a obor). K některým knihám si chcete uložit delší poznámku (popis knihy, zajímavosti, možnosti využití apod.). Protože taková poznámka bude jen u některých knih, bylo by v novém sloupci Poznamka v tabulce Kniha mnoho volných buněk. Můžeme pro ni tedy vytvořit novou tabulku KnihaPoznamka. Některé záznamy tabulky Kniha pak budou spojeny se záznamy tabulky KnihaPoznamka, ale vždy jen jeden s jedním. Jiný příklad: Tabulka Zaměstnanec obsahuje základní data, k ní navážeme tabulku SoukromyUdaj, která obsahuje data citlivá, ke kterým se také asi přistupuje méně často. Toto rozdělení do dvou tabulek s vazbou 1 – 1 má dvě výhody: soukromá data mohou být oddělením od hlavní tabulky lépe skryta a navíc běžná práce se základní (o „tajná“ data zmenšenou) tabulkou bude rychlejší.
-
Vazba 1 – N (jeden záznam jedné tabulky lze spojit s více záznamy tabulky druhé), velmi častá vazba. Jak víte ze závěru třetího dílu, značí se také 1 – M (many). Příklad: Jeden záznam z tabulky Spisovatel může být jistě navázán na více záznamů z tabulky Kniha. Znamená to, že v databázi může být víc knih od jednoho autora. V čem je ale nevýhoda použití tohoto typu vazby v případě Knihy a Autora?
-
Vazba N – N (popř. M – M, jeden záznam první tabulky může být propojen s více záznamy tabulky druhé, ale i naopak: jeden záznam druhé se dá spojit s více záznamy tabulky první). Dost častá vazba, ale pro reálné použití v databázi je nutné ji převést na dva typy 1 – N. Jak převést vazbu M – M na dvě vazby 1 – M bude uvedeno v šestém dílu. Příklad: V databázi Knihovna by jeden autor mohl být spojen s více knihami, ale také jedna kniha může mít víc autorů. Je to sice trochu složitější, ale realističtější než vazba předchozího typu 1 – M mezi tabulkami Kniha a Autor. To je zároveň odpověď na otázku z konce předchozího odstavce.
Práce s Base 3.: Způsoby vytvoření vazeb v Base
Base nabízí dvě možnosti:
-
Vytvoření vazby pomocí okna Návrh relace (volba Nástroje | Vztahy...)
-
Vytvoření kombinovaného seznamu (roletky v tabulce) pro zadávání dat do sloupce cizího klíče tabulky. Přitom hodnotami kombinovaného seznamu budou všechny možné hodnoty ze sloupce primárního klíče druhé tabulky – číselníku. Tím se nejen vytvoří vazba mezi tabulkami, ale zároveň se velmi usnadní zadávání hodnot z druhé tabulky (číselníku) do tabulky první.
Následuje podrobnější popis první metody, druhou si necháme na později.
Vytvoření vazeb pomocí okna Návrh relace
Toto okno zobrazíte volbou Nástroje | Vztahy...
Okno Návrh relace – Tvoříme vazby v databázi
Příprava na tvoření vazeb: Do okna Návrh relace umístíte (přidáte) tabulky z databáze, mezi kterými chcete vazby vytvořit. Tyto tabulky vyberete ze seznamu, který se objeví automaticky po otevření okna, popř. po kliknutí na ikonu Přidat tabulky (se symbolem plus) v panelu nástrojů nebo volbou Vložit | Přidat tabulky z horní nabídky. Tip: Tabulky můžete tažením myši vhodně rozmístit, okénko s poli obsáhlejší tabulky tažením myší za okraj prodloužit nebo rozšířit, abyste dobře viděli všechna pole.
Vytvoření vazby: Nyní na tabulku, ve které je připraven cizí klíč, navážete jinou tabulku.
-
Jednodušší způsob – tažení myší: Stačí kliknout na jeden z klíčů, které tvoří vazbu (cizí v první tabulce nebo primární ve druhé), a přetáhnout ho myší nad protější klíč z navazované tabulky. Objeví se čára, která znázorní nový vztah i s popisem násobnosti (kardinality), většinou 1 – N.
-
Trochu obtížnější, novým oknem Relace, ale bude se vám také hodit: Klikněte na ikonu Nová relace (je zvýrazněna v předchozím obrázku okno Návrh relace). Otevře se další okno Relace. Zde v horní části Použité tabulky vyberte tabulky, mezi kterými vytváříte vazbu (např. Ctenar, Trida). V další části Použitá pole vyberte pole (sloupce tabulek), která vazbu vytvoří (např. TridaSoucasna, Trida). Další nastavení (přepínače na volbě Žádná akce) zatím neměňte. Popsané nastavení vazby je v dalším obrázku a můžete ho použít v Úkolu 8.
Okno Relace pro vytvoření vazby bez přetahování pole myší
Úkol 8: Vytvořte uvedené vazby mezi tabulkami pomocí okna Návrh relace. Pomůcka: Z tabulky Ctenar budou vycházet dvě vazby na tabulku Trida. První vazbu (z pole TridaPuvodni) vytvoříte snadno prvním způsobem. Druhá vazba (z pole TridaSoucasna) se však tomuto způsobu vzpírá: sice se vám ji podaří vytvořit, ale po zavření okna Návrh relace zmizí (i když byly změny uloženy, tady Base trochu zlobí). Tuto druhou vazbu z tabulky Ctenar se vám ale podaří trvale vytvořit právě výše uvedeným druhým způsobem – oknem Relace.
Pro naší databázi Knihovna by nyní vazby mohly vypadat tak, jak ukazuje první obrázek "Okno Návrh relace".
Tip: Pokud by se Base vytvoření některé vazby bránil, zkontrolujte v návrhovém zobrazení obou tabulek, jestli je správně nastaveno, že cizí klíč jedné tabulky je stejného datového typu jako primární klíč tabulky druhé! Do návrhového zobrazení tabulek přejdete po zavření okna relací tak, že kliknete pravou klávesou na tabulku a z kontextového menu vyberete volbu Upravit... Vzpomeňte si, že jsme tabulku třída zvolili jako číselník, s jediným sloupcem typu Text (fix) [CHAR].
Otázky na závěr – Shrnutí čtvrtého dílu
Práce s Base 3, Teorie 3, 4
-
Uveďte základní vlastnosti cizího klíče a porovnejte je s vlastnostmi klíče primárního. Kterou z vlastností cizího klíče nazýváme pravidlo referenční integrity?
-
Vysvětlete, pro jakou tabulku se používá název číselník, uveďte příklady.
-
Jaké jsou druhy vazeb (násobnosti vazeb) mezi tabulkami? Uveďte příklady jejich použití.
-
Ukažte v Base vytvoření vazby mezi dvěma tabulkami (jak tažením myší, tak dalším oknem Relace).
Odpověď na otázku z úkolu 7 v tomto dílu: (Podnět k zamyšlení. Zkuste najít, mezi kterými tabulkami zřejmě ještě v databázi vazba chybí.) Vazbou by měly být propojeny také tabulky Kniha a Ctenar. Tato vazba může být ale složitější. Vrátíme se k ní později, až po vytvoření důkladnějšího návrhu databáze Knihovna v dalším dílu.
Co vás čeká v dalším dílu?
V pátém dílu seriálu o databázovém programu Base se budeme věnovat důkladněji návrhu struktury databáze. Tato část může být pro někoho obtížnější a bude dost rozsáhlá, takže nic dalšího se nám do pátého dílu asi nevejde. Kdo se návrhem zabývat nechce, může tedy pátý díl klidně přeskočit, ale právě navrhování databáze je asi největší dobrodružství (a myslím, že právě návrháři a analytici jsou v procesu tvorby databází ti nejlépe placení zaměstnanci).
Výsledek pátého dílu – hotový návrh naší databáze Knihovna – bude pro řešení dalších úkolů klíčový. Připomeneme si ho proto i v dílu šestém.