Autor: Ondřej Kořínek

Průběh výuky

Uvedení do problematiky

Daný příspěvek uvádí možnost uvedení do problematiky základních pojmů databázových systémů. Ne vše je od samého začátku navrženo správně, cílem je, i s pomocí vyučujícího, aby žáci o dané problematice přemýšleli a navrhovali sami změny. S danou problematikou jsou úzce spjaty následující pojmy: databázový systém, databázová tabulka, primární klíč, cizí klíč, kardinalitu vztahu mezi tabulkami. V programu MS ACCESS se využívají funkce import tabulky z MS Excel, návrhové zobrazení tabulky, relace, dotazy. V podkapitole Zadání pro žáky je zadání pro žáky, i s odkazem na kapitoly Organizace práce. Jako téma byla zvolena jednoduchá filmová databáze, protože obvykle žáci nějaký film/seriál sledují a toto téma by jim mohlo být blízké a i pro ně zajímavé.

Zadání pro žáky

1) Otevřete si soubor Filmová-databáze.xlsx a v něm se pokuste správně sestavit tabulku z daných údajů v jednotlivých buňkách. K vyhledání souvisejících údajů z jednotlivých buněk použijte internet. Výslednou tabulku umístěte do levého horního rohu listu v daném souboru.

Po sestavení tabulky odpovězte na následující otázky:

• Záleží v sestavené tabulce na pořadí řádků?
• Záleží v sestavené tabulce na pořadí sloupců?
• Je chyba, že jste někdo měl jiné pořadí řádků/sloupců?

2) Otevřete si program MS ACCESS a naimportujte tabulku z tabulkového kalkulátoru. Využijte nabídku Externí data – Excel. Při importu nenastavujte primární klíč. Tabulku pojmenujte Film-herec.

3) Přidejte do naimportované tabulky v programu MS ACCESS atribut/sloupec Adresa herce a naplňte ho údaji podle vzoru.

• Vlastnosti sloupce Adresa herce: Krátký text, velikost pole 70.
• Doplněné údaje:
• 
• Pozn. Doplněné údaje jsou smyšlené.

4) Změňte u záznamu 2 datum prezentace filmu Forrest Gump na 24. 6. 1994, viz vzor.

5) Společně s vyučujícím vytvořte následující dotazy:

1. 1. Vypište všechny informace z tabulky. Uložte jako Dotaz1.
   2. Vypište pouze informace o herci. Uložte jako Dotaz2.
   3. Vypište název filmu, který měl premiéru v 90. letech. Uložte jako Dotaz3.
   4. Vypište film (název filmu), který v názvu obsahuje písmeno G. Uložte jako Dotaz4.

6) Samostatně, bez vyučujícího, vytvořte následující dotazy:

1. Vypište jméno a příjmení herce, jehož číslo popisné domu je 128.
2. Vypište jméno a příjmení herce, jehož PSČ města začíná na 128.

7) Odpovězte na následující otázky:

1. 1. Číslo ID (atribut primární klíč) se vztahuje k filmu, k herci nebo k oběma dvěma?
   2. Může nastat nějaké logický problém u data premiéry filmu?
   3. Jaké chyby mohou být v atributu Adresa herce?
   4. Jaké další chyby, např. pro rychlost zpracování dat, jsou v navržené tabulce?

8) Podle vzoru uvedeného níže vytvořte místo jedné tabulky Film-herec tabulku Film a tabulku Herec. Adresu herce rozdělte do více atributů. Dané tabulky v zobrazení Datového listu si dobře prohlédněte.

Obrázek 4 – Tabulka Film

Obrázek 5 – Tabulka Herec

9. Společně s vyučujícím nastavte mezi tabulkou Film a Herec relaci 1:N. Přidejte příslušná data. Po nastavení relace si otevřete obě dvě tabulky. Došlo v tabulkách k nějakým změnám? Má dané řešení nějaké omezení?
10. Nastavte mezi tabulkami Film a Herec relaci N:1. Má dané řešení nějaké omezení? Jak se změní výsledek oproti předcházejícímu úkolu?
11. Společně s vyučujícím navrhněte případ, kdy v jednom filmu může hrát více herců a jeden herec může hrát ve více filmech.

• Příprava na výuku

1. Připravit si v programu MS Excel nebo jiném tabulkovém kalkulátoru „rozházenou tabulku“. Níže je uvedena možná rozházená tabulka i správně sestavená. Pokud budou mít žáci jiné pořadí sloupců, na výsledek to vliv mít nebude, případně se změny dají jednoduše upravit. Že se v některých buňkách nacházejí duplicitní údaje, nevadí, je to záměrně tak nastaveno.

Obrázek 6 – Zadání: rozházená tabulka

Obrázek 7 – Správné řešení: seřazená tabulka

Odpovědi na položené otázky:

• Na pořadí řádků nezáleží, ovšem, kromě záhlaví. To musí být v tabulce uvedeno jako první řádek. Je třeba žákům zdůraznit.
• Na pořadí sloupců v tabulce nezáleží.
• Chyba z pohledu databáze to není.

2. Naimportovat tabulku z tabulkového kalkulátoru do programu MS ACCESS. Zvolí se první varianta Importovat zdrojová data.
   1. V návrhovém zobrazení lze změnit strukturu tabulky, u jednotlivých atributů lze nastavit různé vlastnosti. Pomocí datového listu se do tabulky přidávají záznamy/data. Atribut/sloupec, který je primárním klíčem, jednoznačně identifikuje data v dané tabulce a nesmí obsahovat duplicitní údaje. Slouží také k jednoznačné identifikaci buňky, protože podle primárního klíče program lehce zjistí, jakou buňku, tj. pole v kterém řádku chceme např. změnit.
3. Níže je uvedeno Návrhové zobrazení dané tabulky.

Obrázek 8 – Návrhové zobrazení

Obrázek 9 – Zobrazení datového listu

4. Upravená tabulka Film-herec.

Obrázek 10 – Úprava buňky

5. Společně se žáky vytvořit např. následující výběrové dotazy, možno pomocí průvodce nebo v návrhovém zobrazení. Níže je uvedeno řešení v návrhovém zobrazení.
   1. Vypište všechny informace z tabulky. Uložte jako Dotaz1.
   2. Vypište pouze informace o herci. Uložte jako Dotaz2.
   3. Vypište název filmu, který měl premiéru v 90. letech. Uložte jako Dotaz3.
   4. Vypište film (název filmu), který v názvu obsahuje písmeno G.

• Před vytvářením dotazů je nutné žákům říci, co je dotaz, k čemu slouží apod.

Obrázek 11 – Dotaz1 – návrhové zobrazení

Obrázek 12 – Dotaz2 – návrhové zobrazení

Obrázek 13 – Dotaz3 – návrhové zobrazení

Obrázek 14 – Dotaz4 – návrhové zobrazení

6. Zadané dotazy, které mají žáci vytvořit, nemají správné řešení, protože máme více údajů, které splňují dané podmínky zadaných dotazů. Stejná čísla se vyskytují jak u PSČ, tak i u čísla popisného. Na základě navržení tabulky, budou dále následovat pro žáky dotazy, pomocí kterých by měli být schopni lépe pochopit správné navržení tabulek v relačním modelu databázových systémů.
7. Možné odpovědi na zadané otázky:
   1. Z navržené tabulky není zřejmé, jestli se ID vztahuje k údajům filmů nebo k údajům herců. Vždy by mělo být jasné i správně navržené, aby ty atributy, které nejsou primárním klíčem, se vztahovaly (byly závislé), na primárním klíči. ID se pravděpodobně vztahuje k filmům a ne k hercům, každý herec by tudíž měl mít také svoje ID, které by mělo být primárním klíčem. V tabulce ale nesmí být dva primární klíče, takže přidání dalšího ID do tabulky není možné. Tímto způsobem se dá žákům prakticky vysvětlit druhá normální forma.
   2. Problémem určitě je, že i když se jedná o stejný film (Forrest Gump), tak datum premiéry u filmu je jiný, což by mohlo dělat problémy, protože data nejsou konzistentní. Souvisí to i se špatným navržením tabulky, ale to bude uvedeno u bodu d.
   3. Chyb v atributu adresa je více:
      1. Nejsou ve všech polích zadány adresy ve stejném formátu.
      2. Nedají se vyfiltrovat správně údaje, kdy jeden údaj odpovídá více možnostem (jak bylo uvedeno, PSČ nebo číslo popisné).

• Více údajů je navíc značně nepřehledné a snadno může dojít k chybám, např. stejné město a zadané jiné PSČ.

1. V tabulce může být opět více chyb:
   1. Jsou v ní duplicitní údaje (jeden název filmu zadán vícekrát, i jméno herce zadáno vícekrát)
   2. Evidentně, herci se opakují na základě filmů, v kterých hráli.

• Při opakování údajů může dojít k nekonzistenci dat, např. stejný herec a jiné datum narození, případně, pokud se herečka vdá, tak se její příjmení změní jen někde.
• Toto se napraví tak, aby žáci sami vytvořili místo jedné tabulky 2 tabulky podle vzoru.

8. U nových dvou tabulek si mohou žáci nastavit datové typy/jejich rozsahy podle jejich uvážení, případně může stanovit vyučující. Doporučuji, aby u PSČ byl nastaven datový typ Krátký text a nastavena Vstupní maska na PSČ. Níže jsou uvedena návrhová zobrazení daných dvou tabulek.

Obrázek 15 – Návrhové zobrazení tabulky Herec

Obrázek 16 – Návrhové zobrazení tabulky Film

9. Do tabulky Herec se přidá cizí klíč, tj. přidá se primární klíč z tabulky Film, který ale v tabulce Herec již nemůže být primárním klíčem. Název cizího klíče je libovolný, musí mít však stejný datový typ jako v tabulce, kde je primárním klíčem. Po přidání příslušného atributu doporučuji nejdříve vytvořit relaci a až potom naplnit příslušnými daty.

Obrázek 17 – Přidání cizího klíče do tabulky Herec

Obrázek 18 – Vytvoření relace

Obrázek 19 – Výsledná relace

Obrázek 20 – Přidání dat do atributu ID film v tabulce Herec

• Na tabulky se můžeme podívat z pohledu, že tabulka Film je tzv. tabulka nadřazená (primární klíč, hodnota 1) a tabulka Herec tzv. tabulka podřízená (cizí klíč, hodnota nekonečno). TJ. při zadávání dat do přidaného atributu, který je cizím klíčem, nemůže být v podřízené tabulce (u ní je znak nekonečna) něco navíc, než je v tabulce nadřazené. Např. v tabulce Film mám Id hodnoty 1 až 3, tj. v tabulce Herec nemohu přidat hodnotu jinou, např. 4, protože by nebyl odpovídající záznam v nadřazené tabulce. Žáci mohou vyzkoušet. Proto je důležité žákům říci, že nejdříve musí naplnit data v nadřazené tabulce/tabulkách, pokud je jich více a až poté v podřízené/podřízených tabulkách.
• Na danou otázku by měli žáci odpovědět, že v nadřazené tabulce v zobrazení datového listu přibyly +, to znamená, že jednomu záznamu v tabulce Film může odpovídat více záznamů v tabulce Herec. Ve druhé tabulce ke změnám nedošlo.
• Obrázek 26 – Odpovídající si záznamy v tabulkách po nastavení relace
• Jaké je ale omezení tohoto řešení? V jednom filmu sice může hrát více herců, ale daný herec může hrát pouze v jednom filmu. Tomuto závěru odpovídají i symboly relace u jednotlivých tabulek. Konkrétně, jednomu záznamu z tabulky Film odpovídá více záznamů v tabulce Herec (to je u relace symbol nekonečna u tabulky Herec). Naopak, jednomu záznamu z tabulky Herec odpovídá pouze jeden záznam v tabulce Film (to je u relace symbol jednička u tabulky Film).

10. U druhé možnosti relace N:1 mezi tabulkami Herec a Film musíme v daném návrhu udělat několik změn. Je potřeba odstranit relaci, z tabulky Herec odstranit atribut Id film a do tabulky Film přidat cizí klíč Id herec. Potom se musí nastavit nová relace a zadat příslušná data.

Obrázek 21 – Relace N: 1 mezi tabulkami Film a Herec

Obrázek 22 – Přidání cizího klíč, atributu Id herec a naplnění hodnotami

Obrázek 23 – Odpovídající si záznamy v tabulkách po nastavení relace

• Opět, jako v předcházejícím případu, dojde k jistému omezení. Jeden herec může hrát ve více filmech, ale v daném filmu může hrát pouze jeden herec. Tomuto závěru odpovídají i symboly relace u jednotlivých tabulek. Konkrétně, jednomu záznamu z tabulky Herec odpovídá více záznamů v tabulce Film (to je u relace symbol nekonečna u tabulky Film). Naopak, jednomu záznamu z tabulky Film odpovídá pouze jeden záznam v tabulce Herec (to je u relace symbol jednička u tabulky Herec).

11. K vytvoření relace M:N mezi tabulkami Film a Herec je potřeba nejdříve z předešlého zadání odstranit relaci, z tabulky Film odstranit atribut Id herec a navrhnout novou tabulku, tzv. spojovací, která bude mít vlastní primární klíč a dále bude obsahovat cizí klíče z obou tabulek, tj. z tabulky Film i z tabulky Herec.

Obrázek 24 – Spojovací tabulka – návrhové zobrazení

Obrázek 25 – Relace M:N mezi tabulkami Film a Herec

Obrázek 26 – Naplnění spojovací tabulky odpovídajícími si daty

Obrázek 27 – V jednom filmu může hrát více herců

Obrázek 28 – Jeden herec může hrát v několika filmech

• Opět je potřeba do spojovací tabulky, např. při jiném návrhu databázového systému, data zadat až jako poslední, protože spojovací tabulka má z obou stran symbol nekonečna.
• Z tohoto návrhu tabulek plyne, že v jednom filmu může hrát více herců. Naopak, jeden herec může hrát ve více filmech.
• Organizace práce

1. Zobrazit žákům příslušnou rozházenou tabulku, např. Filmová-databáze.xlsx a nechat je danou tabulku sestavit a umístit do levého horního rohu. Žáci odpovídají na otázky, případně zdůvodňují svojí odpověď.
2. Žáci importují tabulku z tabulkového kalkulátoru do programu MS ACCESS. Správně jí pojmenují.
   1. Vyučující žákům vysvětlí návrhové zobrazení tabulky, zobrazení datového listu tabulky, k čemu je dobrý primární klíč a jak ho nastavit. Vysvětlí základní pojmy atribut/záznam a pole.
3. Žáci přidají atribut do tabulky a naplní ho daty. Využijí Návrhové zobrazení tabulky i zobrazení Datového listu tabulky.
4. Žáci aktualizují příslušné pole v tabulce podle zadání.
5. Společně s vyučujícím vytváří příslušné výběrové dotazy (4 dotazy).
6. Žáci samostatně vytváří výběrové dotazy (2 dotazy).
7. Žáci se zamýšlejí, argumentují a odpovídají na zadané otázky.
8. Žáci podle vzoru místo jedné tabulky navrhnou dvě tabulky Film a Herec.
9. Společně s vyučujícím nastaví mezi tabulkami relace a naplní přidaný atribut daty.
10. Nastaví opačnou relaci mezi tabulkami Film a Herec, než byla nastavena v předcházejícím úkolu.
11. Společně s vyučujícím nastaví mezi tabulkami Film a Herec nastaví relaci M:N.

• Na co si dát pozor
• Program MS ACCESS podle mých zkušeností žákům dělá trochu potíže. Odlišuje se od standardních programů z kancelářského balíku MS OFFICE. Z tohoto důvodu je potřeba, aby s i žáci vše pečlivě vyzkoušeli, proto je lepší látku radši procvičit na více příkladech.
• Alternativní řešení
• Vše lze žákům demonstrovat na příkladech s jakýmkoliv tématem. Podle mých zkušeností by ale mělo téma žáky bavit.