V současné době předmět neučím.

Smyslem předmětu je seznámit se s fungováním informačních systémů a metodami jejich návrhu. Spíš než naučit studenty podobné systémy navrhovat si klademe za cíl ukázat jim jak informační systémy vnitřeně fungují, tak aby se mohli na jejich návrhu jako zákazníci efektivně podílet. 

Zajímavé odkazy

Následující odkazy vedou na zajímavé stránky týkající se tvorby databází a informačních systémů.

Materiály ke cvičení

Požadavky na zápočet

Zápočet bude udělen za vytvoření jednoduchého informačního systému (databáze v MS Access) a odevzdání podrobné dokumentace. Detaily budou oznámeny zhruba v polovině semestru (pravděpodobně sedmé cvičení). Účast na cvičeních není povinná, ale pro zadání práce byste si rozhodně přijít měli.

Přehled kapitol a cvičení

1. Úvodní informace+

Cvičící

Ing. Richard Lipka, Ph.D.

Kancelář: UL 408 (na Borech)

Úřední hodiny: Út a St, vždy 11:00 - 12:00, po dohodě mailem možné i jindy (je lepší se domluvit předem i v úředních hodinách, pro případ že bych byl nemocný, na služební cestě a podobně).

Telefon (do kanceláře): +420 377 63 2466

Mail: lipka@fav.zcu.cz

Předběžný harmonogram cvičení

Pozor! Harmonogram je předběžný a je možné že během semestru dojde ke změnám nebo prohozením témat. 

Týden Téma
1 Úvod, oznámení podmínek pro získání zápočtu a průběhu semestru
2 Základy návrhu databáze
3 Tabulky a datové typy
4 Relace mezi tabulkami
5 Dotazy, jazyk SQL
6 Formuláře, sestavy, import a export dat
7 Zadání samostatné práce
8 Makra, úvod do Visual Basicu
9 Konzultační - zpracování samostatné práce
10 Konzultační - zpracování samostatné práce
11 1. termín odevzdání semestrálních prací - zápočet
12 2. termín odevzdání semestrálních prací - zápočet
13 3. (a poslední!!) termín odevzdání semestrálních prací - zápočet

2. Modelování databáze+

Můžete si stáhnout prezentaci k tomuto cvičení.

Dřív než se vůbec do tvorby databáze pustíte, je rozumné rozmyslet si, jaká data v ní budou uložena, jak se budou dělit do tabulek a jakého budou typu. Aby bylo možné databázi přehledně popsat, vznikla řada notací, kterými se dá znázornit (je třeba zdůraznit, že řeč je nyní o relačních databázích a notace jsou navrženy pro ně, mohou existovat i jiné druhy databází ale v rámci tohoto předmětu se s nimi nesetkáte). Jejich přehled máte na obrázku níže. Jak je vidět na obrázku, notací je několik, ale všechny umožňují zachytit některé základní objekty - entity, atributy a charakter relace mezi nimi.

Různé notace pro popis relační databáze

Během návrhu je nejprve nutné identifikovat

  • Entita popisuje nějaký reálný objekt (konkrétní entita - auto, osoba, výrobek ...) nebo abstrakci (abstraktní entita - převod, odpovědnost, vlastnictví ...), jejíž údaje mají být v DB uložené. V první fázi návrhu je dobré nalézt co největší množství potenciálních entit. Při následné analýze se může zjistit, že z některých se stanou jen atributy jiné entity, nebo se stanou základem pro návrh relací. Obvykle se označují podstatným jménem.
  • Atribut vyjadřuje jednu konkrétní vlastnost entity. Atributy by měly být z hlediska databáze atomické - to znamená že by se neměly skládat z několika údajů. Co bude považováno za atomický údaj ale záleží na návrhu databáze. Pokud je atribut složený z více hodnot (například klíčová slova ke knize v databázi knih), je vhodné vytvořit pro něj zvláštní tabulku.
  • Relace popisuje vztah mezi entitami. U vztahu lze určovat dvě základní vlastnosti - kardinalitu (četnost) a parcialitu/totalitu (povinnost výskytu ve vazbě). Může se hodit také určit role entit ve vazbě na obou stranách. Některé způsoby modelování DB (Chen, šipková notace) umožňují i k relacím přiřadit atributy, to je ale nakonec nutné realizovat vytvořením vhodné tabulky.

Úkol

Přečtěte si pozorně zadání a navrhněte ERA model pro jednotlivé databáze. Snažte se vhodně navrhnout i číselníky, pokud je to třeba. Obvykle existuje několik možností jak schéma navrhnout, je tedy třeba abyste dokázali vaše řešení zdůvodnit.

  1. Tělovýchovná jednota pořádá v průběhu týdne několik pravidelných kurzů. Má několik místností kde kurzy mohou probíhat. Kurz vede vždy jeden z několika registrovaných cvičitelů, někteří cvičitelé vedou i několik kurzů. Každý kurz má určenou kapacitu a až do jejího vyčerpání se do něj mohou hlásit lidé. Jeden člověk může chodit do libovolného počtu kurzů.
  2. V citační databázi jsou údaje o publikacích. Publikace může být buď kniha nebo článek v periodiku, každá publikace má minimálně jednoho autora. Ke každé publikaci může být přiřazena množina klíčových slov. Periodikum má jednotlivá čísla, v každém čísle může být několik publikací.
  3. V říční kilometráži jsou ke každé řece uloženy údaje o objektech, které se na ní vyskytují - jezy, přehrady, zdymadla, mosty, brody, přívozy, přístaviště. V databázi může býýt několik řek.
  4. Firma si vede inventář majetku - nábytku, počítačů, projektorů a knih. Každý kus majetku se nachází v některé z místností firmy a každý je přidělen určitému zaměstnanci. V každé místnosti je určen jeden zaměstnanec jako její správce.
  5. Databáze obsahuje údaje o projektech. V práci na projektu je zaměstnáno několik lidí. Lidé jsou rozděleni do týmů, v každém týmu je určen jeden vedoucí. Projekt je rozdělen na řadu úkolů a na zpracování každého úkolu je přidělen nejméně jeden člověk nebo tým, vždy jeden človek je určen jako vedoucí úkolu zodpovědný za jeho schválení. Každý úkol se může nacházet v několika stavech - nezahájený, rozpracovaný, dokončený, schválený.
3. Tvorba tabulek v MS Access+

Změny v menu

Access 2007 poskytuje téměř stejnou funkcionalitu jako Access 2003, ale jednotlivé funkce jsou přeskládané do nového menu, tzv. "ribbonu". Pokud už s Access 2003 zacházet umíte, je nejsnazší použít průvodce, který vám ukáže kde se nachází nové funkce. Je také možné projít si úvod do rozhraní MS Access 2007.

Ribbon je rozdělený do 4 základních sekcí, několik dalších se jich ale může objevit podle kontextu. Další funkce, zejména uložení a otevření souboru jsou schovány pod tlačítkem s logem MS vlevo nahoře.

  • Domů - Obsahuje základní nástroje pro formátování textu (hodí se při tvorbě formulářů a sestav), umožňuje zobrazovat formuláře, sestavy a tabulky. Zde najdete také základní nástroje pro práci s daty - přidávání a mazání záznamů z tabulek, řazení a filtrování.
  • Vytvořit - v této kartě lze zahájit tvorbu všech prvků databáze - tabulek, formulářů a sestav. V ribonnu jsou jak tlačítka pro vytvoření v návrhovém zobrazení, tak pro spuštění průvodců.
  • Externí data - obsahuje sadu nástrojů pro import a export dat z jiných zdrojů - jiné databáze MS Access, excelovské tabulky, XML soubory a další systémy pro řízení báze dat (Oracle, Paradox, dBase, rozhraní ODBC). Zde jsou také nástroje pro práci se SharePointem.
  • Databázové nástroje - nástroje pro správu databáze. Tady se nastavují relace mezi tabulkami, lze odsud spouštět nahrání dat na server nebo naopak jejich stažení. Pokud umíte Visual Basic, tady můžete psát nové funkce nebo alespoň připravit makra.
Ukázka GUI Access 2007

Pokud si zkusíte spuštět jednotlivé funkce, určitě si všimnete že velká část je (včetně GUI) přímo převzatá ze starších verzí MS Access a používá se stejně. Také si můžete všimnout, že na pravé straně ribbonu se při vytváření nových objektů (tabulek, formulářů) objevují nové prvky menu.

Práce s tabulkou

V případě že vám nebudou uváděné informace stačit, podívejte se na podrobné návody k práci s tabulkami na stránkách MS.

Novou tabulku vytvoříte na záložce "vytvořit" tlačítkem Tabulka. Nová tabulka se pak objeví jako datový list, do kterého lze jak vkládat data, tak přidávat nové sloupce. Kromě toho máte možnost vytvářet tabulky podle šablony, získat je ze SharePointu a nebo pomocí tlačítka "Návrh tabulky", které rovnou vytvoří prázdnou tabulku a zobrazí ji v návrhovém zobrazení.

Po vytvoření tabulky si můžete vybrat jestli s ní chcete pracovat jako s Datovým listem nebo v návrhovém zobrazení (kontingenční graf a tabulku zatím budeme ignorovat). Přepnout zobrazení můžete v kartě "Datový list" nebo "Návrh", která se objevuje úplně napravo.

Pokud vytváříte novou tabulku nebo chcete upravit její strukturu, je snazší pracovat v návrhovém zobrazení. Po přepnutí do něj uvidíte v pracovní ploše tabulku, ve které můžete vytvářet sloupce, nastavit jejich datové typy a případně k nim napsat poznámky. Podrobnější nastavení každého sloupce lze provést ve spodní části, v sekci označené Vlastnosti pole.

Důležité datové typy jsou:

  • Text - Pro krátké texty, lze nastavit délku od 1 do 255 znaků. Hodí se pro jméno a podobné údaje.
  • Memo - Pro dlouhé texty.
  • Číslo - Pro zadávání obecných čísel, lze nastavit pro celá nebo desetinná čísla. Data se ukládají jako číslo, takže se nehodí třeba pro telefon, pro ten je lepší použít text.
  • Datum a čas - Ukládání data a času, je možné nastavit co všechno se má ukládat (roky, měsíce, dny, hodiny, minuty, vteřiny).
  • Měna - Ukládání měny, umí i zobrazit v jaké měně je uložená hodnota.
  • Automatické číslo - Hodí se pro tvorbu automaticky vytvářených klíčů, Access sám zajistí vložení hodnoty do takového pole.

U každého pole lze nastavit několik vlastností. Ty se mění podle datového typu, některé zůstávají společné. Důležitá je vlastnost Formát, umožňující nastavit jak bude pole zobrazeno. Často se hodí možnost nastavit výchozí hodnotu, která je rovnou předvyplněna v každé nové položce tabulky. Je také možné nastavit podmínku, kterou musí data splňovat, pomocí Ověřovacího pravidla. Ověřovací text je hlášení, které se objeví při pokusu zadat data, která podmínku nesplňují. Důležité jsou vlastnosti povinnost zadání a možnost indexování. Pokud je nějaké pole povinné, Access neumožní uložit záznam, dokud nebude vyplněno. Další vlastnosti jsou zvávislé na datovém typu.

Primární klíč lze nastavit pomocí menu návrh, pokud vyberete pole, která mají být součástí primárního klíče, stačí kliknout na ikonu "Primární klíč".

Přidávat data do tabulky můžete v pohledu Datový list. I tady máte možnost pomocí menu "Datový list" upravovat strukturu tabulky, včetně nastavení typu a formátu dat. Navíc v tomto zobrazení je možné nastavit i relace mezi tabulkami.

Cvičení

Zkuste si vytvořit jednoduchou databázi s informacemi o lodích, jejich domovských přístavech a jejich majitelích. Jedna loď může mít jen jednoho majitele (a naopak majitel může mít více lodí), a jen jeden domovský přístav (opět, jeden přístav může sloužit jako domovský pro několik lodí). V každé tabulce vytvořte alespoň 5 polí s různými datovými typy a vyzkoušejte si chování pro různá nastavení jejich vlastností. Zajistěte propojení tabulek, ale nečeště zatím referenční integritu.

Jak na první tabulku

Návod na tvorbu tabulky v MS Access 2007
 
4. Relace a integrita dat+

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í:

Ukázka tabulky 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.

Nastavení relací

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:

Ukázka automatického dělení

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.

Ukázka vazby M:N

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.

Ukázka dialogu pro nastavení relace

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ší).

5. Dotazy a SQL+

Pro snazší práci s daty v databázi slouží dotazy. V prostředí MS Access je možné data v tabulkách řadit a filtrovat podle zvolených kritérií, což nejjednodušší dotazy může nahradit, ale v okamžiku kdy potřebujete pracovat s více tabulkami naráz se bez dotazů neobejdete. Ve větších DB systémech jako je Oracle jsou dotazy základní možností přístupu k datům. V případě že vám nebudou stačit informace na této stránce, určitě se podívejte na návody na práci s dotazy.

Dotaz je požadavek na zobrazení určitých dat, případně i na jejich změnu. Běžně se zapisuje v jazyce SQL, ale v Access je možnost vytvořit ho pomocí návrhového zobrazení, stejně jako ostatní části databáze. Výsledkem dotazu je vždy tabulka s daty, která následně může sloužit jako zdroj dat pro další dotaz, formulář nebo sestavu. Umožňuje získat informace z jedné tabulky, z několika tabulek najednou, vypočítat nové hodnoty ze stávajících dat, počítat souhrnné hodnoty nebo hledat extrémy. Dá se také použít k automatizované změně většího množství dat nebo k jejich odstranění.

Za určitých okolností lze data v dotazu i upravovat. Chová se tedy jako pohled na data, výsledky v dotazu nejsou oddělené od dat v tabulce. Bez problémů to funguje pokud je dotaz jen nad jendou tabulkou, nebo nad dvěma tabulkami s vazbou 1:1. Pokud je nad tabulkami s vazbou 1:N, je možné upravovat jen sloupce které nemají nic společného s vazbou. Pozor, je možné upravit i pole, které je součástí kritéria dotazu, dokud ale nebude dotaz spuštěn znovu, nebude z něj výsledek odstraněn. Například pokud vytvořím dotaz vyhledávající lodě s výtlakem nad 10 000 tun a u jedné z nalezených lodí její výtlak změním na 5000 tun, pořád ji ve výsledných datech uvidím, pokud ale dotaz spustím podruhé, už tam nebude.

Tvorba dotazu

K tvorbě dotazu se dostanete v kartě "Vytvořit", máte k dispozici možnost využít průvodce nebo jen navrhnout dotaz v návrhovém zobrazení.

Kde najdete tvorbu dotazu

Průvodce snadno použijete sami, takže se podíváme na návrhové zobrazení. Hned po jeho otevření se zobrazí výběr tabulek, nad kterými dotaz chcete pokládat - pokud je mezi nimi vazba, Access sám zajistí, aby výsledky splňovaly její podmínky, není je tedy nutné vkládat ručně. Následně si můžete v rozbalovacích seznamech vybrat, která pole mají být zobrazena. Je možné vybírat si jednotlivá pole, nebo použít symbol "*" pro všechna pole z tabulky. U každého pole je také možné zvolit směr řazení které bude použito a jestli má být viditelné.

Přehled návrhu dotazu

Podmínka v dotazu

Takto vytvořený dotaz jen zobrazí data z jedné nebo více tabulek, obvykle chceme trochu víc. Nejčastěji je to využití podmínky, tedy nastaveníkritérií vyhledávání. Podmínky se dají psát do spodního řádku, označeného "Kritéria". Nejsnazší je podmínka založená na porovnávání - čísel, dat nebo slov, porovnání je vždy stejné. Pokud budu chtít například najít lodě s výtlakem přes 10 000 tun, stačí přidat sloupec "Výtlak" z tabulky "Loď" a do řádku "Kritéria" napsat ">= 10 000". Dotaz najde všechny záznamy u kterých je ve sloupci Výtlak hodnota vyšší nebo rovna 10 000. Podmínky je možné spojovat logickými spojkami. Pokud tedy chci najít lodě s výtlakem v rozmezí 7 000 - 12 000 tun, podmínka vypadá následovně: ">= 7000 and <=12000". Lze používat spojky "and", "or" a "not", tedy obě podmínky platí zároveň, stačí že je splněna jedna z podmínek a podmínka není splněna.

Při práci s textem je to podobné. Pokud chcete hledat nějaký konkrétní text, stačí ho do položky "Kritérium" jen napsat. Pokud tedy do sloupce "Jméno" z tabulky "Loď" napíšu "Poseidon", najde Access všechny záznamy, kde je jméno lodi přesně Poseidon. Můžu chtít hledat jen podle části slova, například všechny lodě začínající na P - v takovém případě bude podmínka "Like P*". Výsledkem pak budou všechny lodě, jejichž jméno začíná na "P". Znak * zastupuje libovolný počet písmen. Základ práce se zástupnými znaky je shrnut v tabulce:

PodmínkaZáznamy které najde
"Slovo" Jen položky kde je přesně Slovo, nic víc ani méně.
Not "Slovo" Všechny položky s jinou hodnotou než Slovo.
Like A* Všechny položky začínající na písmeno A.
Like "*Slovo*" Všechny položky které obsahují Slovo - před tím i za tím může být něco dalšího.
Like "*A" Všechny položky, které končí na písmeno A.
Like "[A-F]*" Všechny položky které začínají na jedno z písmen z rozsahu A-F.
Like "A???" Všechny položky začínající na A, za kterým následují právě 3 znaky.
Is Null Všechny záznamy, které nemají položku vyplněnu - má hodnotu Null.

Existují i další druhy podmínek které se mohou objevit, jejich úplný přehled najdete tady. Základní jsou shrnuty v tabulce dole. Dávejte pozor na datový typ - nelze porovnávat čísla se slovy a tak dále, kritérium musí pracovat se stejným datovým typem jako sloupec nad kterým je.

PodmínkaZáznamy které najde
Between 50 and 60 Záznamy, jejichž hodnota je vyšší než 50 a nižší než 100.
In (10, 20, 30, 40) Záznamy které mají jednu z hodnot uvedenou v seznamu (nemusí to být jen čísla, může jít i o text, pak musí být každá hodnota v uvozovkách).
#1/1/2005# Pro prohledávání sloupce s datem, datum musí být z obou stran označeno znakem #. Najde záznamy které mají hodnotu 1.1.2005.
Date() Vrátí záznamy obsahující aktuální datum.
Date() + 5 Vrátí záznamy obsahující datum 5 dní po aktuálním datu.
DatePart("m", [Datum]) = 12 Vrátí záznamy, jejichž datum obsahuje 12. měsíc - tedy všechna prosincová data. [Datum] je jméno sloupce, ve kterém jsou data uložena, v tomto případě je nutné ho do podmínky napsat explicitně.

Zejména práce s datem je komplikovaná a umožňuje spoustu triků, podívejte se na ní v MS návodu věnovanému datu a času v dotazech.

Dotaz lze z návrhového zobrazení přepnout do zobrazení výsledků (datového listu) nebo SQL. SQL je možné modifikovat podle libosti, pokud ale dotaz příliš zkomplikujete, už nepůjde přepnout zpátky do návrhového zobrazení. Obecně platí, že SQL má větší "sílu" - umožňuje konstruovat víc dotazů, než je možné pomocí návrhového zobrazení

Přehled návrhu dotazu

Při psaní dotazů majá zvláštní význam některé znaky. Uvozovky označují řětězce textu, takže "slovo" nebo "123" je vždy chápáno jako text a tak se s tím pracuje, není tedy možné k "123" přičítat a podobně. Do hranatých závorek se píší jména sloupců - jak je to vidět třeba při dotazu na prosincová data výše. Jestliže se pracuje s několika tabulkami, je nutné odlišit do jaké tabulky který sloupec patří, jeho jméno tedy tvoří jméno tabulky a jméno sloupce oddělené tečkou - např. [Loď.Postavena]. To je možné využít při sestavování podmínek k vlastnímu propojení tabulek. Kdyby mezi tabulkami Loď a majitel nebyla relace (kterou Access automaticky spravuje), bylo by tak možné tabulky propojit ručně přímo v dotazu. To se může hodit v případě, že mezi tabulkami žádná relace není, ale pro nějaký konkrétní úkol by se hodila. V našem případě by bylo do dotazu třeba přidat sloupce Majitel_ID z tabulky Loď a ID z tabulky Majitel a do sloupce Majitel_ID vložit podmínku [Majitel.ID] - viz obrázek.

Přehled návrhu dotazu

Agregační dotazy

Agregační dotazy slouží k výpočtům souhrnných informací (jako je třeba průměr) nad vyhledanými daty. Podrobně jsou popsány v příslušném návodu. Můžete s nimi zjistit informace jako "Jaký je průměrný výtlak všech tankerů zadaného majitele" nebo "Jako celkovou tonáží (výtlakem) disponuje zadaný majitel", "Jaká je nejstarší loď registrovaná v zadaném přístavu" a podobně. Abyste mohli se souhrny pracovat v návrhovém zobrazení musíte je zapnout - v kartě "Dotazy-nástroje" a "Návrh" úplně vpravo najdete symbol velké sigma označený "Souhrny". Po jeho zapnutí se mezi řádky "Tabulka" a "Řadit" objeví nová položka - "Souhrn". V tomto řádku si pak můžete vybrat z několika agregačních funkcí. Základní jsou shrnuty v tabulce:

FunkceK čemu je
Sum Součet všechn hodnot
Avg Průměrná hodnota
Min Nejmenší nalezená hodnota
Max Největší nalezená hodnota
Count Počet všech záznamů

Nejsnáze si agregační funkci vyzkoušíte, když do dotazu dáte jen jeden sloupec a necháte spočítat jeho agregační funkci - například průměr. Výsledkem bude jedna hodnota, samozřejmě je nutné aby sloupec měl datový typ použitelný pro výpočet průměru. Často je ale úloha složitější. Typicky jsou data rozdělená na skupiny a agregační funkci je třeba spočítat pro každou skupinu zvlášť. Například zjistit kolik lodí má který majitel. Potřebujeme funkcu "Count", ale nedá se použít pro celou tabulku, musí být použita zvlášť pro lodě každého majitele. K tomu slouží funkce "Seskupit", kterou mezi agregačními funkcemi také najdete

K vytvoření takového dotazu potřebujete znát majitele a jejich lodě, do dotazu tedy vložímee jak tabulku lodí tak majitelů. Relace mezi nimi opět zajistí že budou data správně propojena. Do dotazu vložtě sloupec "Majitel" (ve kterém je jméno majitele) a jako agregační funkci zvolte "Seskupit". Data budou nyní seskupována podle jména majitele. Druhý sloupec bude obsahovat počet lodí v každé skupině. Je nutné sčítat nějaká data, nejlepší bude ID lodě, ale je možné použít jakýkoliv sloupec. Aby ve výsledku bylo vidět o co jde, využijeme alias - nazveme sloupec "Počet:ID" - Access ví že má pracovat se sloupcem "ID" z tabulky "Loď", ale bude zobrazovat název Počet. Agregační funkce - nastavení v poli Seskupit - bude Count.

I v agregačním dotazu může být podmínka - řekněme že bychom chtěl v našem příkladu spočítat jen lodě s výtlakem nad 10 000 tun. V takovém případě normálně přidejte sloupec s výtlakem a napište do něj podmínku. Je ale nutné aby tento sloupec nebyl viditlěný a také aby v řádkou "Souhrn" bylo zvoleno "Kde" - podle toho Access pozná že jde o podmínku. Pozor, nesmí tam být "Seskupit"! Jiank podmínka nebude fungovat.

Přehled návrhu dotazu s agregační funkcí

Vypočtená pole

Poslední užtečnou možností je využití vypočítávaných polí. Výpočet musí být celý napsaný v názvu pole, v případě že by byl složitější, klikněte do jména vypočítávaného pole pravým tlačítkem a zvolte "Sestavit" - to otevře okno, ve kterém se dá snáze pracovat s vytvářeným výrazem. Výraz musí vždy začínat jménem sloupce, výpočet je za dvojtečkou. Například pokud by v databázi byla cena bez DPH, můžete vytvořit v dotazu sloupec "Cena s DPH" - výraz bude následující: "Cena s DPH: [Cena] * 1.2" - v novém sloupci tedy bude hodnota o 20% vyšší než v původním sloupci "Cena". Názvy sloupců musí být v hranatých závorkách. V případě že se používá víc tabulek, je třeba aby byl název o se jménem tabulky. V tomto případě je oddělovačem vykřičník, nikoliv tečka - tedy [Jméno tabulky]![Jméno sloupce].

Návrh výrazu pro vypočtený sloupec

Cvičení

Vaším úkolem je do vaší tabulky lodí doplnit několik dotazů - zkuste si následující možnosti:

  • Dotaz nad jednou tabulkou, který vynechá některé sloupce
  • Dotaz spojující dvě nebo více tabulek
  • Dotaz s jednou nebo více podmínkami
  • Dotaz s agregační funkcí - zkuste k němu i přidat nějakou podmínku
  • Dotaz s vypočteným sloupcem, vyzkoušejte si i sestavení výrazu v Tvůrci výrazů

Zkuste se podívat na SQL vytvořených dotazů a porovnat si ho s tím co má dotaz dělá - snažte se identifikovat jednotlivé prvky SQL, kde jsou jména sloupců a tabulek, jak je udělaná vazba, kde je podmínka a podobně.

6. Formuláře a sestavy+

Pro přehledné zobrazování a vkládání dat slouží v MS Access formuláře. Umožňují snadné vytvoření jednoduchého informačního systému, kde uživatel nemusí přímo pracovat s tabulkami, ale může používat formulářové prvky, které zná z běžných aplikací. Sestavy se hodí k vytváření formátovaných tiskových výstupů.

Formuláře

Formuláře slouží k zobrazení dat, je tedy možné je vytvářet nad dotazy nebo nad tabulkami.

Existují dva základní typy formulářů - první slouží k zobrazení jendoho záznamu, druhý k zobrazení celé tabulky. V obou je možné používat stejné ovládací prvky, ale hodí se k jiným účelům. První typ se hodí k zadávání údajů o jednom objektu, zejména pokud je jich větší množství. Pokud má tabulka třeba 20 sloupců, bylo by nepraktické je vyplňovat přímo do tabulky, lepší je když jsou vidět všechny najednou. Druhý se hodí k zobrazení většího množství dat - třeba výsledku nějakého dotazu nebo celé tabulky. Je vhodné, aby se výsledek vždy vešel na obrazovku, tedy aby nebylo nutné používat horizontální posuvník, takže u rozsáhlých tabulek je lepší zobrazit jen důležité sloupce a v případě že je třeba pracovat i s dalšími daty, umožnit zobrazení podrobného formuláře pro jeden záznam.

GUI pro vytváření formulářů
Formulář pro práci s jedním záznamem

Stejně jako u osttaních objektů v DB, i tvorba formuláře je v kartě "Vytvořit". K tvorbě jednoho formuláře slouží hned první tlačítko, "Formulář". Před tím než ho zmáčknete, vyberte si v seznamu objektů pro jakou tabulku chcete formulář vytvářet, Access zajistí vložení základních ovládacích prvků a propojení s tabulkou - abyste nemuseli dělat celý formulář ručně.

Práce v "Zobrazení rozložení"

Nyní máte možnost formulář upravavovat, k tomu slouží 2 režimy. K jejich přepnutí se dostanete přes záložku "Nástroje návrhu formuláře" - "Formát" a tlačítko "Zobrazení" na levé straně ribbonu. Pokud jste vytvořili formulář nad tabulkou nebo dotazem, zobrazí se v režimu "Zobrazení rozložení". V něm můžete snadno měnit pozice všech objektů ve formuláři, nastavit jejich vzhled a upravovat zobrazené texty, ale nemůžete nic přidat nebo ubrat. Stejně jako v ostatních částech MS Office máte možnost použít předdefinované formáty nebo nastavit jednotlivé prvky formuláře ručně. Dávejte pozor, aby kolonky které může uživatel vyplnit byly nastaveny tak, aby se do nich data vešla, nechte jim dost prostoru.

Základní vlastnosti můžete nastavit přes záložky "Nástroje rozložení formuláře" a "Formát" (kde jsou věci týkající se vzhledu, fontů, barev a podobně) a "Uspořádat" (kde jsou nástroje k ovlivnění rozložení prvků). Pokud chcete nastavovat prvky detailněji, můžete upravit jejich libovolnou vlastnost v seznamu vlastností. Dostanete se k němu tak, že vyberete nastavovaný prvek, a v záložce "Nástroje rozložení formuláře" - "Uspořádat" stisknete "Seznam vlastností" (úplně vpravo). Seznam se objeví na pravé straně. Je možné v něm nastavit řadu věcí ohledně vzledu, ale i způsob zobrazení dat a zdroj odkud se data berou nebo jak má prvek reagovat na události jako je přejetí myši nebo stisknutí tlačítka.

V zobrazení rozložení můžete také přidávat nebo ubírat pole. Vymazání pole je snadné, stačí ho označit a smazat klávesou DELETE. Pro přidání se podívejte do ribbonu na záložku "Nástroje rozložení formuláře" a "Formát", na pravé straně se ojbeví seznam dostupných polí která můžete vložit. Je generován automaticky podle toho, nad jakým objektem je formulář vytvářen - nemusí jít jen o sloupce jedné tabulky, pokud je tabulka v nějaké relaci, nabídne vám i relevatní pole vycházející z relace.

Práce v "Návrhovém zobrazení"

Návrhové zobrazení umožňuje plně editovat chování formuláře - dá se v něm tedy dělat všechno co v zobrazení rozložení a ještě několik věcí navíc. Můžete do formuláře vkládat libovolné ovládací prvky a sami nastavit jejich chování a zdroje dat, nemusíte pracovat jen s tím co vám Access automaticky nabízí. Většina ovládacích prvků se po vložení nastavuje průvodcem, takže je jejich použití snadné a nevyžaduje hlubší proniknutí do jejich nastavování. Protože datová pole vytváří Access automaticky, popíšu jen některé základní prvky které se mohou hodit. Všechny prvky je možné nastavit i ručně bez použití průvodce, ale pro naše potřeby budou průvodci dostateční.

Umístění ovládacích prvků v ribbonu

Tlačítko najdete v záložce "Nástroje návrhu formuláře" a "Návrh". Můžete ho využít k vyvolání různých akcí, zejména otevření jiných formulářů, sestav, spuštění dotazů a podobně. Při vytváření tlačítka vás povede průvodce podle akce, kterou na začárku zvolíte. Všimněte si, že když zvolíte otevření jiného formuláře, máte možnost nastavit, jaká data se v něm mají zobrazit. Je třeba jen vhodně zvolit, která pole ve formuláři s tlačítkem a v zobrazovaném formuláři mají být svázána. Řekněme že máme formulář pro loď a pro přístav, v už známé databázi lodí. Do formuláře s přístavem je možné dát tlačítko, které zobrazí formuláře lodí, které v daném přístavu kotví. V průvodci je tedy nutné nastavit podle jakého pole data filtrovat, v našem případě podle ID přístavu.

Průvodce pro tvorbu tlačítka

Rozbalovací seznam se hodí pokud potřebujete uživateli umožnit výběr z několika položek. Hodnoty je možné načítat z jiné tabulky nebo zadat ručně, obojí se nastavuje v průvodci. Vybranou hodnotu je možné uložit do zadaného pole, pozor ale na datový typ, zejména na to jestli do pole ukládáte opravdu hodnotu nebo jen identifikátor a samotná hodnota je v jiné tabulce (Access se snaží automaticky nabídnout správnou variantu).

Podformulář se hodí pro zobrazení souvisejících záznamů, v našem případě třeba k vypsání seznamu všech lodí hlášených v jednom přístavu ve formuláři s přístavem. Opět je k dispozici průvodce, ve kterém je možné nastavit jaká data budou zobrazena. Je možné použít už existující formulář, nebo nechat Access ať vytvoří nový automaticky a vybrat si jen tabulku nebo dotaz se zdrojem dat. S podformulářem je možné pracovat stejně jako s normálním formulářem pokud jde o úravy vzhledu nebo použitých polí. Pokud je to možné, průvodce při tvorbě nabídne podle jakého klíče se mají data v podformuláři vybírat.

Průvodce pro podformulář
Formulář pro zobrazení tabulky

Pro tvorbu takového formuláře platí stejná pravidla jako pro předchozí. Tlačítko pro jeho vytvoření najdete v záložce "Vytvořit", tlačítko "Více položek". Na rozdíl od formuláře pro jeden záznam se ale tělo formuláře bude opakovat na obrazovce tolikrát, kolik záznamů existuje - všechny záznamy tedy budou vidět najednou. Je dobré vybrat jen pole, která jsou nezbytná pro smyslupné zobrazení, aby nebylo nutné použít horizontální scrollování.

Sestavy

Sestavy slouží k přípravě dat pro tisk. Umožní nastavit jaká data budou tisknuta, jaké bude jejich uspořádání a vzhled. Pro jejich tvorbu platí podobná pravidla jako pro formuláře. Pro tvorbu sestavy můžete použít průvodce ("Vytvořit" a "Průvodce sestavou"), nebo vybrat objekt (tabulku nebo dotaz) nad kterým se sestava vytváří a použít tlačítko "Sestava".

Podobně jako v případě formulářů máte možnost pracovat v zobrazení rozložení a v návrhovém zobrazení. V zobrazení rozložení vidíte náhled sestavy i s daty, v návrhovém zobrazení vidíte jednotlivé části sestavy. Velikost stránky je určena nastavením tiskárny, sestava může mít libovolný počet stránek.

  • Záhlaví sestavy - jedno pro celou sestavu
  • Záhlaví stránky - jednou na každé stránce
  • Tělo - jeden záznam, opakuje se tolikrát kolikrát je třeba
  • Zápatí stránky - jednou na každé stránce
  • Zápatí sestavy - jednou pro celou sestavu
Skupiny a souhrny

Data v sestavě můžete sdružovat do skupin a počítat jejich souhrny. K tomu je třeba zobrazit si sestavu v návrhovém zobrazení. V záložce "Nástroje návrhu sestav" a "Návrh" najdete tlačíto "Seskupit a seřadit" pro nastavení skupin a řazení a Souhrny pro výpočet statistik jako je průměr nebo součet.

Pro výpočet nějakého souhrnu označte v těle sestavy sloupec pro který se má počítat a vyberte si o jaký souhrn půjde (máte stejné možnosti jako v případě agregačních funkcí u dotazů). Všimněte si, že pole se souhrnem se vloží do zápatí sestavy, bude tedy zobrazeno jen jednou.

Máte také možnost data seskupit. Seskupování povolíte tlačítkem "Seskupit a seřadit" v záložce "Nástroje návrhu sestavy" a "Návrh". Ve spodní části okna se objeví dvě tlačítka, "Přidat skupinu" a "Přidat řazení". Můžete si vybrat podle jakého pole budou data seskupena (lodě jen jendoho majitele, lodě jen z jednoho přístavu) a případně nastavit několik úrovní seskupení (lodě jednoho majitele a uvnitř seskupit podle přístavu). Seskupení přidá ještě jednu dvě položky do struktury sestavy - záhlaví a yápatí skupny. Při použití souhnrů se pak budou souhrnné hodnoty přidávat jak na konec celé sestavy pro všechna data, tak do záhlaví každé skupiny.

Struktura sestavy se seskupením
 
7. Zadání samostatné práce+

Volba zadání

  • Tématický okruh semestrální práce si student buď zvolí sám a nechá jej schválit vyučujícím nejlépe na cvičení v 7. týdnu semestru, nejpozději však na cvičení v 11. týdnu semestru (návrh semestrální práce schválený vyučujícím bude pak odevzdán jako nedílná součást semestrální práce), nebo mu bude zadání přiděleno nejlépe na cvičení v 7. týdnu semestru, nejpozději na cvičení v 11. týdnu semestru
  • Nejsou povoleny následující tématické okruhy: knihovna, databáze CD, DVD, VHS a dalších nosičů, zoologická zahrada
  • V ideálním případě se tématický okruh semestrální práce kryje se studovaným oborem studenta či jeho osobními zájmy

Návrhem zadání se rozumí stručný popis databáze - čeho se týká, jaké údaje v ní budou ukládány a jaká data z ní bude možné získávat. Nemělo by se stát, že například z databáze "Studijní agenda" nebude možné dotazem získat rozvrh studenta a podobně.

Pokyny k realizaci

  • Aplikace bude zpracována v prostředí MS Access verze 2000 nebo vyšší, v případě zájmu je možné zvolit i jiný nástroj po dohodě se cvičícím.
  • Navržený E-R-A model bude obsahovat minimálně 4 tabulky (bez započítání tabulek rozkladových), a minimálně 1 tabulku realizující rozklad vazby M:N. Téma a ukládané údaje vybírejte tak, abyste mohli tento bod splnit, nevytvářejte tabulky zbytečně.
  • V aplikaci bude zabezpečena konzistence dat.
  • Každá tabulka bude obsahovat minimálně 10 záznamů, nižší počet záznamů je nutné zdůvodnit (platí zejména pro číselníky, kde tolik záznamů nemusí být třeba).
  • Aplikace bude obsahovat minimálně 4 dotazy v tomto složení (dotaz nad jednou tabulkou s využitím kritéria, dotaz nad více tabulkami, dotaz s parametrem, dotaz využívající agregační funkci).
  • Aplikace bude obsahovat minimálně 3 formuláře, z toho alespoň 1 formulář s podformulářem a alespoň 1 formulář nad dotazem.
  • Aplikace bude obsahovat alespoň jednu sestavu.
  • Aplikace bude obsahovat minimálně 3 makra, z toho alespoň 1 makro s podmínkou nebo akcí nastavení hodnoty.
  • Nedílnou součástí semestrální práce je vytištěná dokumentace (nikoli její elektronická podoba) skládající se z následujících částí:
    • titulní strana se jménem, osobním číslem a fakultou studenta
    • formulace zadání
    • ERA model (a případně další modely)
    • popis sémantiky realizovaných dotazů
    • zdůvodnění volby formulářů a ukázka formuláře
    • zdůvodnění výběru a popis maker
    • zhodnocení + závěr
    • seznam použité literatury

Pokyny k odevzdání

  • Nedílnou součástí odevzdání semestrální práce je její osobní prezentace (předvedení a vysvětlení funkčnosti programu) a odevzdání vytištěné dokumentace v určených termínech - poslední tři cvičení.
  • Semestrální práce neobsahující všechny náležitosti popsané výše nebude hodnocena (hodnocení se netýká rozsahu, ale kvality řešení)
  • Semestrální práce nebude hodnocena, pokud ji student zjevně nerozumí a neumí ji odprezentovat
  • Důraz je kladen na analýzu problému a návrh databáze
  • Zapsání zápočtu do indexu proběhne ihned po úspěšném odevzdání semestrální práce

Hodnocení semestrální práce

Semestrální práce nebude hodnocena body, hodnocení je jen v rovině přijata-nepřijata. Hotové řešení bude s každým probráno na některém z posledních cvičení.

8. Makra v MS Access+

Makra umožňují automatizovat řadu činností, které MS Access vykonává. Každé makro obsahuje seznam akcí, které se po jehu spuštění provedou. Může jít o otevírání formulářů, nastavování hodnot polí, spouštění dotazů, nastavení velikosti a pozice okna a řadu dalších. V případě potřeby se můžete podívat na jejich úplný přehled. Makra mohou v některých případech snadno nahradit proramování funkcí, které MS Access nenabízí přes své základní ovládací prvky.

Tvorba makra

Jako u ostatních objektů, tvorbu makra zahájíte v liště "Vytvořit" tlačítkem "Makro" na pravé straně. Objeví se návrhové zpbrazení makra. V horní části máte možnost zapnout používání podmínek, nastavení argumentů a také možnost spouštění a krokování makra. V hlavním okně je "tabulka", do které se vkládají jednotlivé akce makra.

Lišta k vytvoření nového makra

Pokud už makro máte vytvořené, poklepáním na něj v seznamu objektů ho spustíte, ale neotevřete v návrhovém zobrazení. Abyste ho mohli upravovat, musíte nad jeho názvem stisknout pravé tlačítko myši a vybrat "Návrhové zobrazení".

V návrhovém zobrazení je možné makro spustit červeným vykřičníkem na levé straně lišty "Makra-nástroje" -> "Návrh". Všimněte si malé ikonky označené "Krok". Pokud je stisknutá, makro se spustí v krokovacím režimu, jednotlivé akce se budou provádět postupně až po potvrzení a po každé se zobrazí výsledek. To je užitečné pro testování funkce makra, pokud potřebujete zjistit která akce způsobuje nějaké problémy.

Makro samotné vytvoříte vybíráním akcí, které se mají vykonat. Po otevření návrhového zobrazení máte k dispozici pravděpodobně jen některé akce, potřebujete-li nějakou která v seznamu akcí není, stiskněte tlačítko "Zobrazit všechny akce" v liště. Pokud je povolené zobrazení argumentů, je vidět ve vedlejším sloupci. Jinak jsou zobrazené pod seznamem akcí vždy pro aktuální řádek. Argumenty vám umožňují přizpůsobit chování akce vašim potřebám - nastavit jakých formulářů se akce týká, jak má pracovat s daty a tak dále, jsou vždy závislé na konkrétní akci.

Seznam akcí makra

Důležité akce

Access nabízí několik desítek akcí, pokud potřebujete vyhledat nějakou konkrétní, podívejte se na kompletní přehled. V následující části popíši ty nejdůležitejší.

Makro je obvykle spouštěné pomocí tlačítka ve formuláři (i když je samozřejmě možné spouštět ho ručně). Pokud formulář obsahuje nějaká data, je makro spuštěno v jejich kontextu. To je důležité při nastavení některých argumentů a podmínek. Pokud je v argumentu makra například výraz [Formuláře]![Loď]![Majitel_ID] (tedy pole Majitel_ID ve formuláři Loď), je jeho hodnota ID majitele ve formuláři ze kterého bylo makro tlačítkem voláno. Pokud budete procházet data z tabulky lodí a pro každou loď volat makro s tímto výrazem, bude se jeho hodnota měnit podle toho kdo je zrovna majitelem lodě.

Nadpisy jsou odkazy na podrobné popisy akcí na stránkách MS, kde najdete i dobře popsané příklady použití, rozhodně doporučuji se na ně podívat.

Otevření formuláře

Tato akce slouží k otevření některého z připravených formulářů. Na obrázku se můžete podívat na její argumenty.

Argumenty akce Otevření formuláře

Akce otevře formulář vybraný v poli "Název formuláře". Pole "Zobrazení" umožňuje nastavit v jakém režimu se formulář objeví, obvykle bude stačit režim "formulář". Další dvě pole slouží k omezení dat, pro která bude formulář otevřen. Bez dalšího nastavení budou ve formuláři zobrazena všechna data z příslušné tabulky nebo dotazu. Je ale možné nastavit filtr nebo podmínku, která data omezí. "Filtr" se musí zadat ve formě SQL dotazu na data nebo jména už vytvořeného dotazu, do "Podmínka Where" můžete zadat jen podmínku omezení zobrazovaná data, stejně jako byste psali podmínku v dotazu typu SELECT. Nezapomeňte, že v podmíce se můžete odkazovat i na pole existujícího formuláře.

Důležitý je režim dat, kde můžete nastavit jestli je formulář zobrazen jen pro čtení, nebo je možné data i měnit. Režim okna umožňuje nastavit jak se má nový formulář zobrazit - můžete ho nechat jako "normální", nebo jako "dialogové okno", pak bude nový formulář ve vlastním okně.

Vraťme se k databázi lodí - máme v ní formulář pro loď a pro majitele. Do fromuláře pro loď chceme přidat tlačítko, které zobrazí formulář s daty majitele lodě. Je několik způsobů jak to udělat, my využijeme makro. Použijeme akci "OtevřítFormulář". Název formuláře bude jméno formuláře s daty o majiteli. Zobrazíme ho v režimu formulář. Na nastavení režimu příliš nezáleží, ale důležitá je podmínka. Chceme aby data v novém formuláři byla o majiteli lodě z formuláře, kde na tlačítko klikáme. Podmínka musí být ve tvaru "[pole zobrazovaného formuláře]=[pole formuláře odkud voláme makro]". Například "[Majitel]![ID]=[Formuláře]![Loď]![Majitel_ID]". Protože si pravděpodobně málokdo pamatuje potřebné názvy polí, lze použít tvůrce výrazů - kliknutím na tři tečky na pravé straně pole s výrazem. V něm lze prohledávat všechny objekty a vybrat si pole, která potřebujete.

Ukázka tvůrce výrazů

Podobně fungují akce Otevřít ... i pro další objekty v DB - tabulky, dotazy, sestavy diagramy atd.

Nastavení hodnoty

Akce umožní nastavit hodnotu pole aktuálně otevřeného formuláře. Jméno nastavovaného pole je argument "Položka", pokud ho nedokážete napsat ručně, je nejlepší dohledat je pomocí tvůrce výrazů. Hodnotu nastavte v poli Výraz. Můžete sem napsat konkrétné hodnotu, nebo výraz pro její výpočet. Jde o jednu z nejužitečnějších funkcí, která se hodí pro přidávání dat do podřízené tabulky ve vazbě N:1. Zobrazíte například formulář s údaji o majiteli lodě.

Pokud chcete přidat novou loď tomuto majiteli, vytvoříte si makro které se bude opět spouštět tlačítkem z tohoto formuláře. Toto makro bude mít tři akce: nejprve otevřete formulář zobrazující data lodě. Do podmínky nic nepiště, jen otevřete formulář. Další akce je PřejítNaZáznam - tato akce umožňuje přecházet mezi záznamy podobně jako šipky - můžete přejít na předchozí, následující, první, poslední nebo vkládat nová data. Nastavit co chcete udělat můžete v argumentu Záznam. zvolíte tedy "nový". Protože předchozí akcí jste otevřeli formulář s lodí, týká se tato akce už jeho a formulář bude otevřen pro vložení nového zázánamu. Teď chcete, aby loď patřila majiteli z formuláře odkud je makro voláno. Použijete akci NastavitHodnotu. První argument, "Položka" je jméno pole, které budete nastavovat. Druhý je hodnota jakou do pole chcete vložit. Bude zde tedy jméno pole s ID majitele z formuláře odkud je makro voláno. Nový formulář se pak otevře s už nastaveným jménem majitele.

Nastavení hodnoty v jednom formuláři podle pole v jiném formuláři
Okno se zprávou

Pokud potřebujete uživatele informovat o průběhu makra nebo o nějaké činnosti, můžete použít Okno se zprávou. Tato akce vytvoří informační okno s textem zadaným v argumentu. Můžete si také nastavit jestli má vydat i zvukový signál nebo charakter okna (informace, upozornění, varování). Hodí se hlavně v kombinaci s podmínkami, můžete ho využít k oznámení že nějaká podmínka není splněna a makro nemůže pokračovat.

Spustit SQL

Pokud ovládáte jazyk SQL, můžete toto makro použít ke spuštění libovolného příkazu. SQL umožňuje kromě hledání dat také vytváření a rušení tabulek, změny stávajících dat, mazání a přidávání záznamů, tato akce tedy může výrazně měnit strukturu databáze, pokud je to třeba.

Příklady

Na stránkách s podrobným popisem akcí jsou ukázková makra a popis jejich činnosti, doporučuji tedy abyste se na ně podívali a inspirovali se k vlastní tvorbě.

Použití podmínek

Podmínky vám umožňují vytvořit v makru větve, kdy při splnění podmínky dojde k jiné akci než při jejím nesplnění. Lze tak třeba reagovat na chybové stavy, nebo rozlišit jaký formulář se má otevřít podle nastavení polí ve výchozím formuláři.

Použití podmínek umožníte stisknutím ikony "Podmínky" v liště "Makra-nástroje" a "Návrh". Objeví se nový sloupec, do kterého je možné psátpodmínku. Podmínka je opět výraz, jehož hodnotou musí být logická 0 nebo 1 (pravda nebo nepravda) - typicky je to tedy porovnání dvou hodnot nebo test jestli nějaká data existují.

Pokud chcete aby několik akcí sdílelo stejnou podmínku, můžete do řádky podmínka napsat ..., pak se použije podmínka z řádku výše. Je tedy možné udělat v makru 2 nebo více částí, kdy pod sebou jsou vždy akce jedné větve, ale v závislosti na výsledku podmínky se provede jen některé z nich.

PodmínkaVýsledek - kdy je podmínka splněna
[Jmeno]="Pepa" V poli jméno je nastavena hodnota Pepa
IsNull([Jmeno]) V poli Jmeno není nastavena žádná hodnota, je nulové (ke kontrole chyb, které nechcete nastavit jako omezení při tvorbě tabulek)
Forms![Obednavka]![kusy] < 0 Ve formuláři Obednavka obsahuje pole kusy hodnotu menší než 0
DCount("[ČísloObjednávky]", "Objednávky")>35 V tabulce Objednávky je v poli ČísloObjednávky více než 35 položek

Makro na obrázku se spouští z formuláře s údaji o lodi. Pokud je loď těžší než 10 000 tun, zobrazí se okno se zprávou "Těžká loď", jinak se nic nestane.

Ukázka makra s podmínkou

Při tvorbě výrazu můžete používat funkce, podívejte se na jejich úplný přehled. Pokud potřebujete nějaké speciální chování (výpočty nad hodnotami v tabulkách, práce s datem), může se hodit.

Cvičení

Zkuste si vytvořit několik maker, vyzkoušejte si možnosti nastavení hodnoty a podmínky. Podívejte se na příklad k akci Nastavení hodnoty a zkuste ho implementovat v MS Access. Zkuste si měnit nastavení argumentů, nebo využít akce v jiném makru.