Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky
Vyšší odborná škola informačních služeb v Praze
Zuzana Skřivánková
Tvorba výukového modulu pro SŘBD Access
Bakalářská práce 2011
Prohlášení
Prohlašuji, že jsem bakalářskou práci na téma Tvorba výukového modulu pro SŘBD Access zpracovala samostatně a použila pouze zdrojů, které cituji a uvádím v seznamu použité literatury.
V Praze dne 20. srpna 2011
………………………………
ANOTACE Tato práce je zaměřena na jednouživatelské souborové databáze a popisuje jejich nejvýznamnější zástupce od počátku vývoje databází do současnosti. Je rozčleněna na dvě části, teoretickou a praktickou. V teoretické části se zaobírá relačními databázemi a zaměřuje se detailně na Microsoft Office Access. K tomuto systému řízení báze dat je také provedena rešerše na dostupné výukové materiály. V praktické části autorka uvádí zadání příkladu, na kterém ukazuje funkcionalitu zvolených témat a umožňuje jejich procvičení.
This paper is aimed on single-file databases, and describes the most important ones from the early development of databases to the present. It is divided into two parts, theoretical and practical. The theoretical part deals with relational databases and is focused on Microsoft Office Access in detail. There is also research of materials for this database management system. In the practical part the author gives the example, which shows the functionality of the themes chosen.
OBSAH
Anotace ..................................................................................................................................3 Obsah .....................................................................................................................................5 Úvod ......................................................................................................................................7 Teoretická část .......................................................................................................................9 1
Historie ......................................................................................................................... 10 1.1
2
3
Rozdělení ............................................................................................................... 12
Rešerše jednouživatelských souborových SŘBD ........................................................... 13 2.1
dBase ..................................................................................................................... 13
2.2
FoxPro ................................................................................................................... 15
2.3
Paradox .................................................................................................................. 16
2.4
Open Office Base ................................................................................................... 18
2.5
MS Access ............................................................................................................. 19
Volba SŘBD ................................................................................................................. 23 3.1
Tabulka porovnání.................................................................................................. 23
4
Výběr MS Access ......................................................................................................... 24
5
Základní pojmy relačních databází ................................................................................ 25
6
Analýza současného stavu............................................................................................. 29
Praktická část ....................................................................................................................... 34 7
Návrh databáze ............................................................................................................. 35
8
Požadavky na řešený příklad ......................................................................................... 37
9
Definice příkladu (návrh příkladu k řešení) ................................................................... 38
10
Scénáře pro procvičení zvolených témat ....................................................................... 39 10.1
Návrh databáze ................................................................................................... 39 5
10.2
Datové typy ........................................................................................................ 40
10.3
Relace ................................................................................................................. 41
10.4
Inteligentní kontrola vstupních dat ...................................................................... 42
10.4.1 Vstupní maska ................................................................................................. 43 10.4.2 Ověřovací pravidlo .......................................................................................... 44 10.4.3 Ověřovací text ................................................................................................. 47 10.5
Dotazy a vyhledávací kritéria .............................................................................. 47
10.5.1 Dotazy s vypočítávanými položkami ............................................................... 48 10.5.2 Akční dotazy ................................................................................................... 51 10.5.3 Křížové dotazy ................................................................................................ 54 10.6
Makra ................................................................................................................. 56
Závěr .................................................................................................................................... 59 Seznam zdrojů ...................................................................................................................... 60 Seznam obrázků ................................................................................................................... 61 Příloha .................................................................................................................................. 62
6
ÚVOD Téma Tvorba výukového modulu pro SŘBD Access jsem si vybrala, protože mi práce s Accessem připadá zajímavá a již od střední školy jsem si ve starších verzích Accessu vytvářela různé databáze na knihy a CD, které mám doma. Zajímá mě využití Accessu nejen pro domácí použití, ale i pro komerční účely. Ráda bych se v budoucnu věnovala pedagogice, a proto mi tvorba výukového modulu připadá jako dobrá zkouška mých výukových schopností. Myslím a doufám, že zpracování takového tématu mě bude bavit a bude přínosem nejen pro čtenáře, ale i pro mě samotnou. V teoretické části bych čtenáře této práce ráda uvedla do problematiky relačních databází a seznámila ho s historií a dostupnými souborovými, jednouživatelskými systémy řízení báze dat. V jedné kapitole uvedu základní pojmy relačních databází pro osvěžení a doplnění znalostí čtenáře. SŘBD, které vyberu jako nejvýznamnější zástupce, bych ráda porovnala dle kritérií, aby se z tabulky dalo vyčíst, jaké možnosti nám tyto SŘBD nabízejí a jak jich můžeme využít. Pro SŘBD Access bych v další kapitole ráda našla výukové kurzy, učebnice, cvičebnice a manuály, které uživateli Accessu ukazují a učí jej, jak s Accessem pracovat. Tyto knihy a kurzy bych ráda prošla a porovnala tak, aby se po přečtení mojí práce o nich každý něco dozvěděl a z mého krátkého shrnutí by si byl schopen nějaký z nich vybrat. V praktické části uvedu základní metodiku návrhu databáze a navrhnu souvislý příklad, na kterém bude možno si návrh databáze vyzkoušet. Budu se snažit příklad navrhnout tak, abych na něm mohla realizovat ukázky ze všech témat, která jsem si vybrala na procvičení. Budeme mít jednu databázi, kterou budeme postupně upravovat podle zadání příkladů na každou část zvolených témat. Začnu tedy návrhem databáze a tabulek a jejich vzájemných relací. Příklad by měl být koncipován tak, aby bylo možno procvičit všechny dostupné relace. Dále na příkladech ukážu, jak inteligentně omezit uživatele, který data vkládá, pomocí kontroly vstupních dat a jejích částí, které Access nabízí.
7
Další kapitola se bude věnovat tvorbě různých dotazů. Access nabízí několik typů dotazů, které můžeme použít pro vyhledání a zobrazení dat podle našich potřeb. Na každý druh dotazu si ukážeme několik příkladů použití. V poslední kapitole praktické části se budu krátce věnovat makrům, jejich tvorbě a jejich použití. Opět připravím několik maker, která by se pro naši databázi hodila a v Accessu nejsou takové možnosti primárně zahrnuty. Po přečtení této práce by se měl čtenář dozvědět o relačních databázích a historii databází celkově a měl by být schopen, po vyzkoušení všech příkladů uvedených v praktické části, navrhnout a vytvořit vlastní databázi a upravit ji podle svých vlastních potřeb.
8
TEORETICKÁ ČÁST
9
1 HISTORIE V této kapitole jsem čerpala ze zdrojů: [1], [2], [12], [13], [14]. Databáze je uspořádaný soubor informací (dat) pro jeden nebo více účelů většinou uložený v digitální podobě. Pojem databáze ve správném slova smyslu odkazuje na data a jejich datové struktury. Často nesprávně, a v dnešní době, řekla bych, již obecně, je užíván nejen pro označení dat a jejich datových struktur ale i pro systém řízení báze dat. Systém řízení báze dat (dále jen SŘBD) pochází z anglického Database management systém (DBMS). Je to software, který umožňuje manipulaci s daty a přístup k nim. Prvními databázovými systémy byly ještě před počítačovou érou kartotéky. Data byla zaznamenána na papírových kartičkách, kdy vždy jedna karta patřila jedné entitě, a na této kartě byly zapsány informace týkající se této entity a její vlastnosti. S daty manipuloval přímo člověk a každá změna musela být zaznamenána ručně. Databáze jsou používány již od počátků elektronického zpracování dat. Oproti dnešním databázovým systémům, které se dají aplikovat na širokou škálu dat a potřeb, staré systémy byly šity výhradně na míru dané společnosti a jejích dat. Původní databázové systémy bychom našli pouze ve velkých organizacích, které měly možnost zakoupit potřebné hardwarové vybavení. Tento hardware byl objemný, a to hlavně fyzicky, bylo potřeba celé haly, aby v ní mohl být umístěn (jednalo se o takzvané sálové počítače). Navigační systémy Jak se počítače vyvíjely v rychlosti a kapacitě, tak se objevovalo velké množství databázových systémů. V polovině 60-tých let se databáze používaly pouze v komerční sféře. V roce 1960 byla na konferenci CODASYL zveřejněna první verze jazyka COBOL. Charles Bachman vytvořil Database Task Group, výbor, který měl za úkol standardizaci COBOLu. V roce 1971 byl představen standard, který byl obecně znám jako „Codasyl approach“, a brzy potom se objevilo množství produktů založených na tomto standardu. Výbor vydal zprávu, ve které byla popsána celá architektura síťového databázového systému. Tento standard byl založen na manuální navigaci v datech. Když se databáze otevřela, program byl nastaven na první záznam a
10
neexistovalo žádné najít nebo vyhledat, jak je známe dnes. Ve chvíli, kdy bylo potřeba vyhledat nějaká data, bylo nutné každý záznam jeden po druhém porovnat se zadanými kritérii. V dnešní době by něco takového bylo silně limitující, ale v 70-tých letech to bylo nadmíru dostačující. V roce 1968 mělo i IBM vlastní systém známý jako IMS. Tento systém byl vyvinut pro vesmírný program Apollo a byl obecně podobný konceptu CODASYL, ale užíval striktní hierarchii navigace namísto síťového konceptu CODASYL. Oba dva systémy byly nakonec považovány za navigační databáze díky způsobu, jakým se k datům přistupovalo. Relační databáze Edgar Codd pracoval v IBM v jedné z částí, která se zabývala vývojem hardwaru. Edgarovi se nelíbil systém navigačních databází a CODASYL standard, hlavně nedostatek ve funkci vyhledávání. V roce 1971 vydal článek, ve kterém zveřejňuje průlomový Relační model databází, ve kterém uvádí a popisuje úplně nový systém uchovávání a práce s rozsáhlými databázemi. V relačním modelu jsou data uchovávána v tabulkách, skládajících se z řádků a sloupců, přičemž část informace v řádku je použita jako klíč, jedinečně definující určitý záznam. Základem tohoto modelu je zpětné spojování souvisejících informací pomocí klíčů. Stejně jako v navigačních systémech byla zapotřebí smyčka, aby shromáždila záznamy, v relačních systémech byla smyčka zapotřebí, aby shromáždila informace o jakémkoliv záznamu. Coddovo řešení na nezbytnost smyček byl jazyk, který dal později vzniknout jazyku SQL (standardized query language). Objektově orientované databáze V osmdesátých letech společně se vzestupem objektově orientovaného programování začali programátoři a projektanti na data nahlížet a zpracovávat je jako objekty. Například pokud byly v databázi údaje o osobě, atributy této osoby, jako je adresa, telefonní číslo a věk, nyní patřili této osobě, namísto toho, aby to byly vnější záznamy. V této době se také hodně zaměřovalo na spolehlivost a přístupovou rychlost databází. V roce 1989 uveřejnili dva profesoři z Wisconsinské university v Madisonu článek, který popisuje metodu zvýšení výkonu databází. Nápad spočíval v tom, že by se často vyhledávaná, důležitá data uložila v menší databázi a ta by se dala rychleji prohledat.
11
Data byla napojena na hlavní databázi, ve které byla uložena všechna data. Tento nápad nakonec vedl k všeobecně známému indexování. Objektově orientované databáze měly nahradit relační, místo toho byly vytvořeny objektově relační systémy. 1.1
Rozdělení
Databáze můžeme dělit, jak je podrobněji popsáno na předchozích několika stranách na: navigační systémy, relační databáze a objektově orientované databáze. Dále se dají databázové systémy dělit podle způsobu uložení dat na souborové a systémové. Souborové databáze mají uložena všechna data v jednom (např. MS Access) nebo více souborech (např. FoxPro). Souborové systémy existovaly dříve než ty systémové a i v dnešní době spousta firem pracuje například s databázemi FoxPro. Nejznámějšími zástupci souborových databází byly ve svém čase dBase, FoxPro (později Visual FoxPro), Paradox a v dnešní době Open Office Base a MS Access. O všech se v dalších kapitolách zmíním podrobněji. Systémová databáze také používá k uložení dat soubory, ale přístup k datům v nich není tak jednoduchý, jako v případě klasických souborových databází. Nejznámějšími a nejvíce užívanými zástupci systémových databází jsou MS SQL Server a Oracle. Podle počtu uživatelů, kteří mohou k databázi v jednu chvíli přistupovat, se dělí na jednouživatelské a víceuživatelské. Jednouživatelské databáze jsou opět méně užívané než ty víceuživatelské, protože pro většinu uživatelů je důležité, aby se k databázi mohlo najednou připojit více lidí. Já se ve své práci zaměřím na jednouživatelské souborové databáze a v následujících kapitolách se budu zabývat podrobnější rešerší každé z dříve uvedených.
12
2 REŠERŠE JEDNOUŽIVATELSKÝCH SOUBOROVÝCH SŘBD 2.1
dBase
V této kapitole jsem čerpala ze zdrojů: [3], [13]. DBase byl prvním rozšířeným systémem řízení báze dat a na začátku softwarového trhu zařadil společnost Ashton-Tate (distributora dBase) mezi jednoho ze tří hlavních hráčů na tomto trhu. V polovině osmdesátých let produkovalo mnoho různých společností různé variace jazyků a systémů včetně FoxPro. Mnoho těchto systémů bylo lepších a technicky silnějších než dBase, ale nemohli proniknout na trh, dokud nebyla vydána verze dBase IV se spoustou problémů. Zrovna v této době se průmysl přesunul k užívání SQL a klient – server řešením a společně s představením Microsoft Windows to vedlo k pohřbení Ashton – Tate do odkupující společnosti Borland v roce 1991. Práva k dBase produktům byla prodána v roce 1999 do nové společnosti dBase Inc. a v roce 2004 se společnost přejmenovala na dataBased Intelligence, Inc., která funguje a vydává své produkty dodnes. DBase se vyvinul do moderního objektově orientovaného jazyka, který běží na 32 bitových Windows a podporuje chod různých aplikací včetně webových aplikací na serverech Windows. DBase využívá rozhraní ODBC k přístupu k většině moderních databází. DBase obsahuje IDE (vývojové prostředí), editor zdrojového kódu, kompilátor, interpret, debugger, nástroje pro návrh grafického rozhraní a nástroj pro prohlížení objektů. Pro práci s daty, poli, textem, čísli a daty, poskytuje procedurální příkazy, jako jsou: USE, SKIP, GO TOP, STR() a další. Také používá runtime interpreter, který umožňuje uživateli zapsat příkaz do příkazového řádku a po stisku enteru okamžitě provést příkaz. Díky tomu byly dBase programy rychlé a jednoduché na napsání a otestování. V dnešní době se implementace jazyku dBase rozrostla a zahrnuje mnoho různých prvků cíleně zaměřených na obchodní aplikace, včetně objektově orientovaného programování, užití internetu a interakce s moderními zařízeními.
13
DBase ukládá ve formátu .dbf, který je běžně užívaný v mnoha jiných aplikacích, které potřebují jednoduchý formát pro uložení strukturovaných dat. Následující příklad otevře tabulku zaměstnanec, přidá každému manažerovi, který řídí více jak jednoho pracovníka 10% k platu a vypíše jejich jména a platy. Užití velkých písmen pro příkazy je jen kvůli ilustraci. USE zamestnanec REPLACE ALL plat WITH plat * 1.1 FOR podrizeni > 0 LIST ALL jmeno, prijmeni, plat TO PRINT Všimněte si, že na rozdíl od SQL, není třeba stále zmiňovat název tabulky, předpokládá se totiž, že aktuální tabulka je stále stejná, dokud není řečeno jinak. Kvůli původu dBase v interpretovaném interaktivním jazyce, používá systém různé techniky na snížení množství napsaných znaků a jeden příkaz je použit na provedení různých požadavků. Na příklad FOR limituje rozsah daného příkazu (něco jako WHERE v SQL). Jiné příkazy jako je DELETE, REPLACE atd. používají FOR k limitování jejich aktivity. To značně ulehčuje znalost jazyka a jeho učení. Jako první jazyk zaměřený na business implementoval zhodnocení řetězců. a=2 pokus = "a + 10" a = &pokus Hodnota „a“ je tedy 12. V tomto případě „&” říká, aby ohodnotil řetězec uložený v „pokus“ tak, jako by to byl programový kód. V dnešní době nám práce s řetězci přijde velice jednoduchá a samozřejmá, ale v době kdy dBase vznikal, to nebylo vůbec obvyklé. Jako vývojová platforma aplikací vyplňuje dBase prostor mezi nižšími jazyky jako je C, C++ a Java a vyššími jazyky čtvrté generace.
14
2.2
FoxPro
V této kapitole jsem čerpala ze zdrojů: [9], [13]. Reakcí na úspěch dBase byla FoxBase (první verzi vydala společnost Fox Software v roce 1984), později přejmenovaná na FoxPro a po zakoupení společností Microsoft v roce 1992 na Visual FoxPro. Během svého vývoje FoxPro pracovala pod systémy: MS DOS, MS Windows, Macintosh, Unix, Linux. Tvrdí se, že nejúspěšnější byla FoxPro ve verzi 2.6. z roku 1994. Poslední verzí Visual FoxPro je verze 9.0. Vývoj FoxPro byl dle dostupných informací ukončen z marketingových důvodů. Systém FoxPro používá pro ukládání dat formát souborů dBase, oblíben byl především pro svoji rychlost a stabilitu a také pro to, že bylo možné šířit „runtime“ pro běh aplikací zdarma. Programy byly překládány do „mezikódu“, čímž se docílilo výrazného zrychlení běhu. Zajímavá byla jeho metoda vyhledávání, která speciálně používala indexové soubory a byla efektivní i na velkých objemech dat. Velkou výhodou FoxPro byla jeho přenositelnost mezi operačními systémy. Významné vlastností jednotlivých verzí: Visual FoxPro 3.0 – první objektová verze FoxPro. Visual FoxPro 5.0 a 6.0 se staly součástí Visual Studia včetně možnosti používat Debugger. Visual Foxpro 7.0 umožnila plnou spolupráci s XML Web Services a stala se součástí předplatného MSDN v úrovni Professional. Následovala verze 8.0 a poté verze 9.0, která je poslední - Visual FoxPro 9.0 ServisPack 2.0 je dle informací na webu Misrosoftu z října 2007.
15
2.3
Paradox
V této kapitole jsem čerpala ze zdrojů: [12], [13], [14]. Paradox pro DOS Paradox pro DOS byl relační systém řízení báze dat původně navržen Richardem Swartzem a Robertem Shostakem a vstoupil na trh v roce 1985 v jejich firmě Ansa Software. V roce 1987 společnost odkoupil Borland včetně Paradoxu. Paradox byl úspěšnou databází pro DOS na konci osmdesátých a na začátku devadesátých let minulého století. V té době dominovali trhu dBase a FoxPro. Prvky, které vyznačovaly Paradox, byly například efektivní využití paměti (obvyklé i rozšířené), „query by example“ (QBE) podporovaný systémem umělé inteligence a indexování, které umožňovalo provádět dotazy velmi rychle, inovativní programovací jazyk The Paradox Application Language (PAL) a mohl být zaznamenáván úhozy na klávesnici. Paradox pro Windows Paradox pro Windows byl úplně jiný produkt než Paradox pro DOS a byl vytvořen jiným týmem programátorů. Ačkoliv klíčové prvky DOS systému jako je například QBE, byly zachovány, nastala hlavní změna v kompatibilitě, od PAL k ObjectPAL a přechodu na graficky zpracované formuláře a sestavy. Změny byly kontroverzní, ale protože byl systém založen na zaznamenávání úhozy na klávesnici, neměl v systému Windows konkurenta. Kvůli záznamu klávesnicí byl použit objektově orientovaný jazyk založený na nápadu Hypercard (aplikační program vytvořený Billem Atkinsonem pro Apple Computers, Inc.). Formuláře a sestavy, které designéři použili, měli nezávislé přibližování a oddalování a tak mohl uživatel pracovat v přiblížené verzi. Pravý klik myši vyvolal vlastnosti, podle Xerox Alto a Smalltalk, což je dnes universální pro Windows programy. ObjectPAL byl (stejně jako Hypercard) spojen s grafickými objekty, které se objevily po pravém kliku. Nástroje mohly být „přípíchnuty“ tak, aby zůstali na obrazovce, nápad vypůjčený od NeXT, a v dnešní době široce adoptován Windows a dalšími programy. Asi první rok vývoje byl objektově orientovaný kód zapisován v C pomocí maker, dokud nebylo možno použít Turbo C ++, pak byl zbytek kódu dopsán v C++.
16
Paradox pro Windows začal svůj vývoj s použitím Windows 3.0 na jaře roku 1990. Vydání Paradoxu bylo nakonec zpožděno oproti originálnímu plánu. Důvodů bylo hodně, ale nebylo to úplně překvapující, nové nástroje, přechod na grafický systém a to vše na první verzi operačního systému. Microsoftu se nakonec podařilo vydat Access pár měsíců před Paradoxem, což byla velká marketingová výhra pro Microsoft. Microsoft Access na rozdíl od Paradoxu a ostatních ukazoval záznamy před proběhnutím příkazu. Paradox ukázal data až poté, co byly všechny záznamy shromážděny, a byla vytvořena odpovědní tabulka. Zrovna tento rozdíl byl tak markantní, že měl negativní dopad na Paradox pro Windows. Dalším zásadním rozdílem byla cena. MS Access se prodával za 99 dolarů, což bylo oproti běžné ceně za systémy řízení báze dat neuvěřitelně levné. Běžná cena Paradoxu pro DOS, Paradoxu pro Windows a dBase byla okolo 799 dolarů. Management Borlandu těžce podcenil hrozbu Microsoft a jeho Accessu. I když se Paradox pro Windows ještě chvíli prodával, společnost se dostala do vážných finančních potíží, a když v roce 1995 Microsoft přidal Access do balíků Microsoft Office, všechny desktopové databázové systémy pro koncového uživatele byly vytěsněny z trhu. Společnost se na trhu drží dodnes.
17
2.4
Open Office Base
V této kapitole jsem čerpala ze zdroje [15]. Open Office Base (OOB) je programem pro tvorbu a správu databází z kancelářského balíku OpenOffice.org. OOB podporuje některé souborové formáty databází, ale je možné ho použít i na připojení k externí relační databázi. Pomocí OOB můžeme tvořit tabulky, dotazy, sestavy a formuláře a tvořit databáze. Tvorba databáze v Open Office silně připomíná tvorbu v MS Access, jen je o něco jednodušší. OOB má omezené množství funkcí oproti Accessu. Při tvorbě tabulek v návrhovém zobrazení je na první pohled jasné a viditelné omezení nebo zjednodušení oproti Accessu. Tabulky, dotazy, sestavy i formuláře se v OOB tvoří pomocí průvodců. Vše se dá také upravovat pomocí návrhového zobrazení, které vypadá skoro stejně jako v MS Accessu. Po vytvoření vlastní databáze přímo v OOB, se použije standardizovaný formát OpenDocument database neboli .odb. OOB podporuje souborové formáty databází jako je například dBase formát. Také obsahuje možnost připojit se k externí relační databázi MySQL nebo Oracle. V OOB se dají otevřít soubory MS Access, a hned po otevření se uloží jako nová databáze .odb. OOB je určen zejména pro menší databáze pro domácnosti (seznamy knih, CD, DVD a podobně) nebo pro malé a menší firmy. S jeho pomocí se tvoří lokální databáze, ke kterým má v jednu chvíli přístup z jednoho místa pouze jeden uživatel. Databáze je uložena na jednom počítači a z toho samého počítače se k databázi přistupuje. Je to tedy jeden z jednodušších databázových programů, ve kterém se vytváří části databáze ve vstřícném grafickém prostředí, to znamená, že tvůrce databáze nemusí znát žádný speciální jazyk pro tvorbu a práci s databází. Program také obsahuje množství šablon a průvodců. OOB je sice jednodušší a nabízí méně možností než jiné databázové programy, ale na rozdíl od ostatních, obsahuje přesně, co může běžný ne moc zkušený uživatel od databázového programu očekávat. Pro takového uživatele je obsah OOB naprosto dostačující. Hlavní výhoda pro uživatele je cena, nebo spíše není. Nejen Open Office Base ale celý kancelářský balík OpenOffice.org je zcela zdarma.
18
2.5
MS Access
V této kapitole jsem čerpala ze zdrojů: [8], [12], [13], [14]. Microsoft Office Access, dříve známý jako Microsoft Access, je relační databázový systém od Microsoftu který kombinuje tvorbu databází s grafickým uživatelským rozhraním. Je součástí rodiny Microsoft Office a je zahrnut v edici Microsoft Office Profesional nebo je prodáván samostatně. Loni na jaře byla vydána současná verze MS Access 2010. MS Access ukládá data ve vlastním formátu .accdb, a může importovat data nebo na ně přímo odkazovat i do jiných aplikací a databází. Jako ostatní Office aplikace je podporován Visual Basicem pro aplikace, objektově orientovaným programovacím jazykem. Prvním pokusem Microsoftu o relační databázi byl v osmdesátých letech dvacátého století projekt s tajným názvem Omega. Měl obsahovat spoustu prvků, které byly v té době inovativní, ale na tehdejších strojích s procesorem 386 běžel pomalu a v roce 1989 byl program zrušen. Části programu byly později využity v dalším projektu s názvem Cirrus, což byl šifrovaný název pro Access, a Thunder, což byl šifrovaný název pro Visual Basic. V roce 1992 byla vydána první verze Access 1.0 a byla následována verzí 1.1 v roce 1993, která vylepšila kompatibilitu s ostatními produkty Microsoftu. Byla dodávána na sedmi 1.44MB disketách a požadovala Microsoft Windows 3.1. Původně fungoval software velmi dobře, ale testování ukázalo, že pod určitými okolnostmi se data ztrácejí. Databáze veliké přes 10MB byly problematické (většina hard disků byla veliká 500MB) a manuál „Začínáme“ informoval uživatele o množství špatného zacházení a špatných nastavení, kdy program nefungoval a způsoboval ztrátu dat. S Officem 95, Microsoft Access 7.0 (Access 95) se stal součástí balíku Microsoft Office Profesional společně s Excelem, Wordem a PowerPointem a Access Basic (programovací jazyk Accessu) se stal Visual Basicem pro aplikace (VBA). Od té doby Microsoft vydal novou verzi s každou verzí balíku Microsoft Office.
19
Formát dat Accessu se vyvíjel společně s ním a největší přechod byl z verze 97 na verzi Access 2000, která není zpětně kompatibilní se staršími verzemi. Všechny novější verze podporují formát Access 2000. Před vydáním Accessu ovládaly trh firmy Borland s Paradoxem a dBase a Fox s FoxPro. Společně se skoupením FoxPro v roce 1992 Microsoftem se Access stal velmi rychle dominantním databázovým systémem pro Windows, který efektivně eliminoval konkurenci, která neuspěla při přechodu z MS-DOS. Tabulky v Accessu nabízejí množství standardních typů polí, klíčů a referenčních integrit. Access také obsahuje rozhraní pro dotazy a formuláře pro zobrazení a zadávání dat. Má opatření při užívání více uživateli a může data zamknout. Využívá referenční integrity a mazání a aktualizování souvisejících polí v kaskádě. Opakované dotazy mohou být zautomatizovány pomocí maker. Access je velmi populární u neprogramátorů a běžných pokročilých uživatelů, kteří mohou vytvořit graficky přijatelné a relativně pokročilé řešení s malým množstvím kódu nebo žádným kódem. Umístění databáze na síť není nijak složité a dá se sdílet několika uživateli. Microsoft nabízí množství šablon databází přímo v programu nebo na stažení na jejich webové stránce. Tyto možnosti jsou nabídnuty uživateli hned na začátku a uživatel tak může svojí databázi rozšířit nebo obohatit o předdefinované tabulky, dotazy, formuláře a sestavy a makra. Oblíbené šablony zahrnují sledování kontaktů, výnosů, nákladů, akcí, projektů a úkolů. Šablony nezahrnují kód VBA, ale MS Access nabízí programátorům možnost vytvořit si vlastní řešení pomocí VBA, který je podobný Visual Basicu 6.0 a je využíván přes všechny programy balíku Office. Zkušení uživatelé a programátoři mohou rozšířit základní verzi pro koncového uživatele na profesionální řešení ušité přímo na míru s pokročilou automatizací kontroly dat a eliminace chyb. Access podporuje i připojení více uživatelů v jednu chvíli, množství připojení závisí na objemu dat a množství vykonávaných úkolů. Access funguje docela dobře s objemem dat do 2GB a množstvím současných připojení do 20. Takhle kapacita je vhodná pro oddělení ve firmě, ale pokud je Access takto využíván, měla by být databáze rozdělena. To znamená, že tabulky jsou uloženy na síti a jsou sdíleny a ostatní komponenty aplikace má každý uživatel uloženy ve své vlastní kopii u sebe.
20
Role MS Access na webu je do verze 2010 velmi limitována. Uživatelské rozhraní pro sestavy a formuláře funguje pouze ve Windows. Ve verzích 2000 a 2003 jsme mohli v Accessu vygenerovat stránku s databází publikovatelnou na webu. Tohle už není v dalších verzích umožněno. Access 2010 umožňuje zveřejnění databáze přes Share Point 2010. Webové formuláře a sestavy běží na všech webových prohlížečích a nepotřebují žádné přídavné aplikace (ActiveX, Silverlight apod.) Microsoft nabízí řešení pro uživatele, kteří chtějí vytvořit databázi v MS Acessu a dále ji šířit mezi uživatele, kteří MS Access nevlastní. Také umožňuje uživatelům rozšíření, které pomáhá šířit aplikace Access a vytvářet šablony. Jedna z výhod Accessu, z programátorské perspektivy, je relativní kompatibilita s SQL. Dotazy mohou být zobrazovány graficky a editovány jako SQL kód a SQL kód může být přímo využit v makrech a VBA modulech k manipulování s tabulkami Accessu. Uživatelé mohou spojovat VBA a makra. VBA může být obsažen v dotazech. Microsoft Access je souborová databáze a na rozdíl od systémových databází neumožňuje práci s triggery a procedurami. SharePoint Server 2010 přes Access Services umožňuje publikovat databáze Access 2010 na SharePoint, což dovoluje více uživatelům interakci s databází s použitím jakéhokoliv standardního prohlížeče. Tyto databáze mohou využívat všechny běžné objekty Accessu, jako jsou tabulky, formuláře a sestavy, dotazy a makra. Access Services ukládá tyto objekty na SharePointu. Microsoft Access může importovat nebo přímo odkazovat na data uložená v jiných aplikacích a databázích jako jsou: XML, Outlook, HTML, dBase, Paradox, FoxPro, MS SQL Server, Oracle, MySQL a další. Uživatel může v Accessu tvořit databáze, tabulky, dotazy a formuláře a sestavy bez znalosti SQL jazyka pomocí grafického prostředí, které umožňuje uživateli jednoduše navrhnout tabulky a tvořit sestavy a formuláře pomocí průvodce a jednoduchým výběrem polí a přetažením polí na místo, kde je chtějí mít. Také mohou využívat makra, která automatizují jednoduché úkoly použitím několika rozbalovacích menu. Databáze MS Access se dá sdílet po síti mezi více uživateli tak, že každý bude mít své dotazy, formuláře, sestavy a makra, a tabulky budou uloženy na jednom místě a každý k nim bude přistupovat pomocí svých dotazů atd. Tak se zajistí, že nikdo nesmaže data, která by někdo jiný potřeboval. 21
Access nabízí několik možností, jak databázi zabezpečit. Nejjednodušším je heslo k databázi. Jakmile ho uživatel zadá, má plný přístup k celé databázi. To je ale poměrně slabá ochrana. Vyšším stupněm ochrany je zadávání uživatelského jména a hesla. Skupiny uživatelů a samotní uživatelé mohou mít přístup pouze k části databáze nebo mohou někteří uživatelé databázi otevřít pouze pro čtení. Databáze může být také zašifrovaná. Formát ACCDB nabízí o dost vylepšenou verzi šifrování oproti předchozím verzím.
22
3 VOLBA SŘBD Všechny SŘBD, které jsem uvedla v kapitole 2, bych v této kapitole ráda porovnala dle vybraných kritérií. Kritéria a jejich upřesnění je následující: 1.
Typ architektury - relační, síťový, objektová.
2.
Platforma – podporované operační systémy.
3.
Cena a licence - pořizovací cena SŘBD a licence.
4.
Referenční integrita
5.
Velikost na disku – velikost, kterou SŘBD zabírá na disku.
6.
Velikost podporované databáze – velikost největší možné databáze v daném programu.
7.
Možnosti zálohování – automatické zálohování.
8.
Možnost utajení, šifrování – zda se data dají šifrovat či pokud je možnost databázi zaheslovat.
9.
Podpora v národním prostředí - školení, knihy, výukové kurzy.
10. Podpora uživatele - kvalita dokumentace. 11. Aktualita - aktuálnost poslední verze. V jakém roce byla vydána poslední verze. 12. Rychlost zpracování – rychlost zpracování dotazu. 13. Vnitřní programovací jazyk – jaký vnitřní programovací jazyk SŘBD používá. 14. Snadnost obsluhy – potřeba znalosti speciálního jazyka. Uživatelská přívětivost. 15. Po nákupní servis – pomoc a podpora ze strany prodejce. 16. Možnost online publikace – možnost vystavení databáze online. 3.1
Tabulka porovnání
Hledáním informací na internetu a pokusem o porovnání všech výše uvedených databází jsem zjistila, že konkurenceschopné a porovnatelné jsou pouze OOB a MS Access a vzhledem k tomu, že OOB obsahuje (jak je již výše zmíněno) pouze část toho, co obsahuje MS Access, rozhodla jsem se tabulku porovnání vynechat. Pro dBase, Fox pro a Paradox neexistuje dostatečné množství podkladů, ze kterých bych mohla čerpat, nemají žádnou nebo mají nedostatečnou podporu – používají se víceméně pro systémy, které existují historicky a které ještě nebyly převedeny do modernějších databází. 23
4 VÝBĚR MS ACCESS Produkt MS Access 2010 se mi jeví jako nejvhodnější z výše uvedených databází z těchto důvodů: Má silnou podporu ze strany Microsoftu. Existuje k němu česká dokumentace, nápověda a školící firmy nabízí mnoho kurzů k tomuto a předchozím verzím produktu. Obsahuje šablony databází pro použití uživatelem. Při vytváření databáze lze zvolit běžnou nebo webovou databázi. Běžnou databázi lze následně publikovat na web (kde je nutné upravit některé funkce). Pro publikování se využívá SharePoint. Velkou výhodou je, že uživatelské prostředí lze vyklikat, pomocí klikání lze sestavovat i makra. Oproti předchozím verzím má MS Access 2010 rozšířeného Tvůrce výrazů a má nového Tvůrce maker. S aplikací MS Access může pracovat i začátečník. Výhodou je jednoduchá implementace (sdílení souboru v síti), přičemž aplikační logika může, ale nemusí být na počítačích uživatelů.
24
5 ZÁKLADNÍ POJMY RELAČNÍCH DATABÁZÍ V této kapitole jsem čerpala ze zdrojů: [1], [2], [7], [8]. Databáze je soubor dat, který eviduje reálný svět (např. evidence knihovny). Entity jsou základní prvky databáze (např. autor), který má vlastnosti a ty jsou nazvány atributy (např. jméno). Základní pojmy, se kterými se setkáme v průběhu návrhu, tvoření, úprav a práce s databází. Databáze – soubor informací, které jsou uloženy v tabulkách. Údaje v tabulkách jsou propojeny společnými prvky. Společné údaje pro několik tabulek jsou uloženy v tabulce další a zabraňuje se tím duplicitním záznamům. Tabulka – V tabulkách jsou uložena data. Tabulky se skládají ze sloupců a řádků. Jeden záznam (věta) je jeden řádek. Sloupec označuje, jaké záznamy jsou v tabulce uvedeny, a každý řádek je naplněn odpovídajícími hodnotami. Pole (atribut) – je sledovaná vlastnost entity. Jsou to sloupce tabulky a určují, jaký datový typ, má být do příslušného pole vepsán. Vepsané údaje musí tomuto datovému typu odpovídat. Tyto atributy mohou nabývat různých hodnot. Primární klíč – jedinečný prvek, který určuje jednu entitu a jednoznačně ji identifikuje. Nesmí být pro dva záznamy stejný. Mohlo by to být například rodné číslo, ale pro většinu případů se používá pomocné pole identifikační číslo (ID). Cizí klíč – je pole, které je v jiné tabulce primárním klíčem. Entita – je objekt, jehož vlastnosti jsou data, která máme v databázi uloženy. Pole (atributy) jsou vlastnosti této entity, které tvoří hodnoty v tabulkách. Záznam – jeden řádek tabulky, který nese hodnoty atributů dané entity. Záznamy se musí jeden od druhého lišit. Datový typ – každý atribut, který je u dané entity uveden v tabulce má svůj datový typ. Může to být číslo, text, datum, automatické číslo atd. Jednotlivé entity spolu mají nějaké vztahy. Těmto vztahům se nesprávně říká relace. Relace je ve skutečnosti tabulka, ve které se spojují související data ze dvou tabulek. Známe tři typy těchto relací. 25
Typy relací: Relace 1:1 Relace 1:1 je nejjednodušší. Záznam je obsažen v každé ze dvou propojených tabulek jen jednou. Tedy jeden záznam z tabulky první může být ve spojení pouze s jedním záznamem tabulky druhé. Vazbu 1:1 je možné realizovat pouze jednou tabulkou a vazba není ve skutečnosti potřeba, využívá se v případě, že chceme v tabulkách mít opravdu jenom data úzce související, anebo pokud hodláme nastavit přístupová práva a tím někomu znemožnit se na část těchto dat podívat. Relace 1:N Relace 1:N je o něco složitější. Vyjadřuje vztah, kdy záznam jedné tabulky má vztah s několika záznamy v tabulce druhé. Například pokud by v jedné tabulce byli Autoři a v jiné jejich díla, byla by vazba mezi nimi 1:N. Jeden autor napsal několik děl, ale každé dílo má pouze jednoho autora. A zároveň více děl může mít stejného autora. Relace M:N Další možností je relace M:N. Tato relace znamená, že jeden záznam v první tabulce může mít několik souvisejících dat v druhé tabulce a zároveň záznam v druhé tabulce může mít několik souvisejících záznamů v tabulce první. Relaci M:N není možné vytvořit jen tak. Tato relace není primárně v nabídce, proto je třeba ji rozdělit na dvě relace 1:N a přidat do databáze tedy jednu spojovací tabulku navíc. Například pro tabulky Studenti a Kurzy bychom museli vytvořit spojovací tabulku, protože jeden student může chodit do více kurzů a na každém kurzu může (musí) být více studentů. Rekurzivní relace Všechny tři předešlé relace byly mezi entitami dvou různých typů. Tedy mezi dvěma různými tabulkami. Co kdybychom potřebovali vytvořit relaci mezi dvěma instancemi jedné entity, tak, aby tabulka odkazovala sama na sebe, aby byla ve vztahu sama se sebou? V tuto chvíli bychom použili poslední typ relací, relaci rekurzivní. Máme tři typy rekurzivních relací, stejně jako u normálních relací, 1:1, 1:N a M:N. Příkladem relace 1:1 by mohla být tabulka Zaměstnanci, kdy každý jeden zaměstnanec může mít jednoho (právě jednoho) partnera. Tedy jeden záznam z tabulky může být ve vztahu právě s jedním záznamem z téže tabulky. Pro relaci 1:N, je běžný příklad také 26
v tabulce Zaměstnanci, kdy jeden zaměstnanec může být nadřízený jednomu či více dalším zaměstnancům. Pro poslední typ rekurzivní relace M:N použijeme tabulku Součástky, když si představíme, že každá součástka může být součástí mnoha jiných součástek a zároveň může být z mnoha dalších součástek složena. Normální formy Pojem normální formy označuje dobře navržené tabulky, které splňují čtyři základní normální formy. Tyto normální formy navrhli a uvedli do praxe Edgar F. Codd a Raymond Boyce v roce 1974 0. normální forma (0NF) Tato forma se používá pouze občas a znamená, že data musí být setříděna v tabulkách a každý záznam (řádek) tabulky musí být jedinečný a jednoznačně identifikovatelný (pomocí primárního klíče). 1. normální forma (1NF) První normální, nebo také nejjednodušší, forma (1NF) podmiňuje, že všechny atributy uvedené v dané tabulce musí být dále nedělitelné (nesmí to být relace). Tedy obsahem jednoho pole v tabulce by měla být jenom jedna hodnota určitého datového typu. 2. normální forma (2NF) Druhou normální formu splňuje tabulka ve chvíli, kdy zároveň splňuje první normální formu a navíc každý záznam, který není sám primárním klíčem je na primárním klíči přímo závislý. Pokud má tabulka jako primární klíč pouze jeden sloupec je podmínka splněna primárně. Pokud je třeba označit více sloupců jako primární klíč, dochází k nesplnění 2NF. V této chvíli je řešením rozpad (dekompozice relačního schématu) na dvě tabulky, každá s daty přímo závislými na jedné části (sloupci) původního primárního klíče. Když tabulka nesplňuje tuto podmínku, doprovází ji často redundance dat. 3. normální forma (3NF) Pokud tabulka splňuje třetí normální formu, musí opět splňovat první a zároveň druhou normální formu a pokud žádný atribut, který není primárním klíčem, není tranzitivně závislý na žádném klíči. To znamená, že by jeden ze sloupců byl přímo závislý na jiném sloupci a tento sloupec by nebyl primárním klíčem.
27
Boyce-Coddova normální forma [2]Boyce/Coddova normální forma se pokládá za variaci třetí normální formy a dokonce je původní definicí 3.NF, tak jak byla publikována v 70 letech. Je vymezena stejnými pravidly jako 3.NF, říká, že musí platit i mezi hodnotami uvnitř složeného primárního klíče. Relace se nachází v BCNF, jestliže pro každou netriviální závislost X -> Y platí, že X je nadmnožinou nějakého klíče schématu R. Zní to poněkud šíleně, ale ničeho se nebojte, k tomu, aby byla porušena BCNF musí být splněno několik podmínek a to poměrně specifických:
Relace musí mít více kandidátních klíčů
Minimálně 2 kandidátní klíče musí být složené z více atributů
Některé složené kandidátní klíče musí mít společný atribut. [2]
28
6 ANALÝZA SOUČASNÉHO STAVU Pro výuku práce s MS Accessem a procvičení je v současnosti několik možností. Na výběr máme online kurzy a výukové programy, učebnice a knihy a různá školení a kurzy. Školení a kurzy, které neprobíhají online, jsem bohužel neměla možnost prozkoumat. Na internetu se dá nalézt řada takových kurzů a školení, která trvají od jednoho dne po několik týdnů a jejich cena je různá. Knihy Vzhledem k tomu, že poslední verze Accessu je z minulého roku, autoři ještě nestačili zareagovat množstvím učebnic, cvičebnic a knih o tomto programu. O Accessu 2010 a práce s ním jsem našla tři knihy. Starší verze Accessu se od nejnovější verze z roku 2010 tolik neliší a daly by se použít i učebnice na starší verze, ale já bych ráda uvedla jen ty, které se přímo týkají Accessu 2010. Josef Pecinovský: Excel a Access 2010 – Efektivní zpracování dat na počítači [10] Kniha od Josefa Pecinovského již podle názvu napovídá, že nepůjde jenom o Access. Je rozdělena na dvě poloviny a každá z nich se věnuje jednomu z programů. Na začátku knihy je seznámení s uživatelským prostředím obou programů a jsou zde uvedeny jejich společné prvky a odlišnosti. První polovina knihy se věnuje výhradně Excelu 2010. Ve druhé polovině se dozvíte vše potřebné o Accessu 2010. Autor začíná úvodem do světa databází, následuje výuka tvorby tabulek, dotazů, formulářů a používání filtrů. Po části Accessu následuje ukázka efektivní spolupráce Accessu a Excelu a v příloze seznam klávesových zkratek a odborných pojmů. I přesto, že je kniha psána o verzích Excel 2010 a Access 2010, je sestavena tak, aby ji bylo možné použít jako výukový materiál i k nižším verzím. Tedy ani majitelé starší verze Accessu se nemusí bát si tuto knihu pořídit a alespoň názorně uvidí na výkladu a příkladech jaké novinky, další možnosti a rozšíření Access 2010 nabízí oproti nižším verzím.
29
Slavoj Písek: Access 2010 [11] Tato kniha je napsána velice zkušeným autorem knih o Accessu, databázích, html a další počítačové literatury. Tato kniha učí čtenáře správně a efektivně využívat Access 2010 pro správu většího množství informací. Stejně jako v minulé knize se čtenář naučí vytvářet tabulky a pracovat s nimi, stejně tak pracovat s relacemi a vytvářet je, vytvářet formuláře, dotazy a filtry. Dále se dozvíte o všech novinkách a změnách, které proběhly mezi dvěma posledními verzemi (Access 2007 a Access 2010). Na konci každé kapitoly je několik testových otázek, které ověří, jak moc jste v kapitole dávali pozor. Na konci knihy v klíči můžete nalézt všechny správné odpovědi na tyto testové otázky. Aleš Kruczek: Microsoft Access 2010 - Podrobná uživatelská příručka [5] Pokud jste již pokročilým uživatelem a nepotřebujete vysvětlení základních pojmů, které už dávno znáte, měli byste sáhnout po této knize z pera Aleše Kruczka. V této knize se nebudete zdržovat zbytečným úvodem pro začátečníky a ukáže vám všechny funkce užitečné pro tvorbu a správu databáze stejně jako nové funkce Accessu 2010 a pomůže vám s orientací v jeho pozměněném prostředí. Tato podrobná uživatelská příručka vám detailně osvětlí všechna zákoutí této aktuální verze programu a budete jej moci používat jak na běžné uživatelské úrovni, tak budete schopni vytvořit databázovou aplikace třeba pro celou firmu. Kniha se podrobně věnuje návrhu databáze, relacím, tabulkám, datovým typům, dotazům (křížovým, parametrickým, SQL), výrazům, formulářům, sestavám, importu a exportu dat, propojení s Excelem a SharePointem, správě, sdílení a zabezpečení databáze. Zhodnocení Tyto tři publikace se přímo věnují problematice a možnostem Accessu 2010 a ani jedna z nich není výrazně lepší a obsáhlejší než ty ostatní. Pro běžného uživatele nebo začátečníka s Accessem bych doporučila jednu z prvních dvou knih. Pokud by se čtenář rád informoval a naučil se zacházet i s jiným programem balíku Microsoft Office, určitě by byla dobrá kniha první, spojení Excelu s Accessem. Pro
30
pokročilejšího uživatele, který se chce dozvědět něco navíc, nebo jen hledá knihu, která rozšíří jeho obzory, měl by sáhnout po třetí variantě od pana Kruczka. Online kurzy Na českém trhu najdeme množství online kurzů pro výuku Accessu. Do svého podrobnějšího popisu jsem vybrala dva, které jsou každému uživateli dostupné zdarma. Další kurzy, které jsou placené, jsou také k dispozici, ale k těm jsem se bohužel nedostala a myslím, že mnou vybrané kurzy budou vyhovující a informacemi, ukázkami, testy a příklady budou dostačující pro jakkoliv informovaného či neinformovaného uživatele. Na rozdíl od knih, u kterých jsem měla na výběr alespoň ze tří dobrých titulů, které jsem mohla přiblížit a ani jedna z nich mi nepřišla výrazně horší nebo lepší než druhé dvě, u online kurzů jsem to měla o něco horší. Kurzy pro verzi 2010 jsou vedeny normálním způsobem, ne online a každý má na výběr mezi verzemi 2007 a 2010, což podle mne znamená, že lektor na místě vysvětluje rozdíly mezi verzemi podle toho, co mají posluchači doma nainstalováno. Verzi 2010 se přímo věnují pouze dva ze tří mnou uvedených kurzů. Třetí kurz od GOPASu jsem se rozhodla i přes neaktuálnost (věnuje se verzi 2007) zařadit. Proč vysvětlím v popisu samotného kurzu. Kurz od pana Pavla (dostupný z:http://office.lasakovi.com/access/) Tento kurz nevypadá vůbec oficiálně, je zdarma a ani celé jméno pisatele se ze stránek nedozvíte. Ale i přesto, že stránky působí silně neuspořádaným dojmem a orientace na nich není úplně jasná a jednoduchá, vypadají informace, které pan Pavel poskytuje velmi dobře. Způsob, jakým je vše vysvětleno se mi zdá dobře pochopitelný pro všechny možné úrovně znalostí čtenáře. Výběrovým menu se dostanete přímo na část, která vás zajímá, a nemusíte projít ničím, co vás nezajímá nebo co už znáte. Sekce pro základy a tabulky je zcela zpracovaná a dozvíte se tam vše, co potřebujete znát, abyste mohli s Accessem začít a abyste byli schopni vytvořit tabulku. Další sekce jako jsou formuláře, dotazy, sestavy a ostatní jsou ještě stále ve vývoji. Pan Pavel neměl vůbec špatný nápad, pokud by se ho rozhodl dokončit, mohla by být jeho stránka velmi dobrým kurzem.
31
Kurz od Microsoftu (dostupný z: http://office.microsoft.com/cs-cz/access-help/zaciname-s-aplikaci-access2010-HA010341722.aspx?CTT=1) Na stránkách Microsoftu nalezneme všechny potřebné informace o nové verzi Accessu 2010 a o práci s ním. Ve článku nazvaném „Základní úkoly v aplikaci Access“ se dozvíme všechny základní informace o Accessu, jak s ním pracovat a krok po kroku je vysvětleno rozhraní, které Access používá. Je zde přesně popsáno jaké tlačítko na jaké kartě je potřeba stisknout, pokud potřebujete udělat zrovna tohle a tohle. Vysvětlení tohoto typu je velmi žádané spíše nezkušenějšími uživateli a myslím, že přesně pro ně je tento článek určen. Obsahuje informace o Accessu, tabulkách a importech. Další kroky jako jsou dotazy, formuláře, sestavy a makra jsou dostupné pomocí odkazů v tomto článku na články jiné a na stránce není těžké tyto další články najít. Pokud by čtenář byl uživatelem Accessu 2007 a zajímalo by ho jen a pouze, co je nového, změněného a jak s touto verzí pracovat, je pro něj ideální článek „Co je nového v aplikaci Microsoft Access“. Tam na něj nečekají žádné zbytečnosti, o které by nestál a dozví se přímo, co přesně nová verze Accessu nabízí a také jak s těmito inovacemi pracovat. Stránka nabízí spoustu článků, na kterých je názorně vysvětleno jak tvořit databáze v Accessu 2010 a jak je v něm také spravovat. Výukový kurz od firmy GOPAS (dostupný z: http://onlinecourses.langmaster.cz/gopas/MSAccess/index.htm) Výukový kurz od GOPASu je sice pro verzi Access 2007, ale i přesto jsem se ho jako jediný neaktuální kurz či knihu, rozhodla zařadit do mého přehledu. Výukový kurz od GOPASu je zdarma a je neuvěřitelně dobře zpracovaný. Pomocí ukázek, videí, obrázků, audiozáznamů a samotných databází (volně ke stažení ke každému příkladu) každému jasně vysvětlí práci s Accessem, tvorbu databáze, tabulek, dotazů a souhrnů. I když je tento kurz pro minulou verzi Accessu, zpracování je na tak vysoké úrovni a přitom tak jednoduše podáno, že bych si ho troufla doporučit i pro verzi Access 2010. Nenajdete všechno přesně tam, kde to má být, ale po chvilce hledání určitě najdete potřebný příkaz i ve verzi 2010. Microsoft dělá verze jejich programů velmi podobné, aby uživatelé neměli s přechodem na novější verzi až 32
takový problém. Kurz sice neobsahuje všechny funkce a možnosti Accessu (ani pro verzi 2007), ale pro každou část nabízí procvičení a kontrolu pomocí krátkého testu, po němž hned vidíte vyhodnocení a správnou odpověď. Zhodnocení Kurz od pana Pavla není špatný, ale bohužel není dodělaný, pokud by se ho podařilo dokončit, byl by dobrým průvodcem po světě Accessu. Kurz od GOPASu není aktuální a také neobsahuj všechny možnosti, zpracování pro začátečníka je ale lepší než na stránkách Microsoftu. Na stránkách Microsoftu najdeme vyčerpávající a úplné vysvětlení základů Accessu i všech jeho dalších možností a inovací. Pokud nejste úplným začátečníkem a o relačních databázích a o samotném Accessu máte alespoň malé povědomí, budou pro vás oficiální stránky Microsoftu a informace na nich uvedené a zpracované naprosto dostačující a najdete, dle mého názoru, vše co byste mohli při práci s Accessem 2010 potřebovat.
33
PRAKTICKÁ ČÁST
34
7 NÁVRH DATABÁZE První věcí při vytváření databáze je její návrh. Je důležité správně navrhnout tabulky, sloupce, záznamy, které budou v tabulkách a hlavně propojení tabulek. Jak správně jsme si databázi navrhli, zjistíme, až ji začneme vytvářet a budeme s ní následně pracovat. Proto není radno tuto část podcenit, nebo úplně vypustit. Největším problémem v této části je špatný návrh. Důležité je získat všechny potřebné informace o budoucí databázi. Zjistit, jaké údaje a data bude databáze obsahovat a jaké údaje budeme chtít zobrazovat. Po zjištění nebo určení účelu databáze shromáždíme všechny informace, které bude databáze obsahovat a které potřebujeme evidovat. Je vhodné se informovat u budoucích uživatelů databáze, jaké informace často vyhledávají a jaké je zajímají. Tak zjistíme spoustu o tom, jaká data bude databáze obsahovat a pomůže nám to navrhnout databázi správně. Také můžeme zjistit, jaká data se často upravují nebo mažou, pak je velmi výhodné taková data uložit do samostatné tabulky. Pamatujme, že musíme eliminovat nadbytečnost dat a každá informace by měla být v databázi pouze jedenkrát. Data by se neměla nikdy opakovat. Pokud zjistíme, že máme data na více místech, měli bychom je uvést do samostatné tabulky. Tyto informace následně rozdělíme do tabulek. V každé tabulce by měly být pouze informace, které spolu úzce souvisí. Příkladem takovéto tabulky může být tabulka Osoba či Firma. Když víme, jaké tabulky bude databáze obsahovat, rozdělíme atributy do jednotlivých polí (sloupců). Každá tabulka tak tedy bude mít názvy sloupců a taková data se tam budou ukládat. Abychom splnili první normální formu (viz kapitola 5 Základní pojmy relačních databází) musí být všechna taková pole nedělitelná. Například pole Adresa by bylo velmi nevhodné. Toto pole je totiž možné rozdělit na Ulice, Město a PSČ. Tedy Adresa by mohla být samostatnou tabulkou. Do těchto sloupců se nebudou ukládat žádné vypočítávané položky, měly by obsahovat pouze jednoznačná a nedělitelná data. Pro vypočítávané položky (například Cena objednávky = Počet kusů * Cena za kus) se používají dotazy s vypočítávanými položkami. Ve chvíli, kdy víme, jak budou vypadat tabulky, musíme v každé tabulce definovat identifikační klíč neboli primární klíč. Primární klíč musí jednoznačně definovat jeden 35
záznam (řádek tabulky). Tento údaj nemusí nést žádné faktické údaje. Nejvhodnější je použít libovolné jedinečné číslo. Access má pro tento případ datový typ Automatické číslo. Nemusíme tedy čísla sami vymýšlet a vypisovat a aplikace je sama doplňuje za nás. Pokud bychom chtěli, aby byl primární klíč tvořen jedním z faktických údajů, i když to není ani běžné, ani výhodné, musíme si být jisti, že stejná informace není uvedena nikde jinde v tabulce a zároveň tato informace musí být trvalá. Pokud tedy víme, nebo si myslíme, že by se tato informace mohla někdy změnit, nebudeme ji používat jako primární klíč. Pomocí primárního klíče na sebe mohou tabulky navzájem odkazovat, a pokud se tento klíč změní, musí se změna projevit na všech místech v databázi. Když zvolíme jako primární klíč automatické číslo (nebo nějaký jiný neměnící se atribut) zamezíme možnosti ztráty synchronizace dat. Když máme navržené tabulky, jejich atributy a primární klíče, je čas na to, abychom si rozmysleli, jak na sebe budou data navazovat, odkazovat a souviset spolu. Data, která jsme rozkouskovali do tabulek, musíme tedy v tuhle chvíli spojit. Tabulky propojíme pomocí relací. Typy relací a jejich příklady jsou uvedeny v kapitole 5 Základní pojmy relačních databází. Když máme vytvořený návrh celé databáze, tabulek a jejich propojení je čas, abychom databázi vytvořili a naplnili ji zkušebními daty. Po naplnění zkušebními daty si vyzkoušíme práci s databází. Pokud zjistíme, že nám některé tabulky nestačí, nevyhovují nebo v nich nějaká data přebývají, je v tuto chvíli místo, abychom databázi upravili podle nově zjištěných skutečností, odstranili případné duplicitní údaje a rozdělili je na více tabulek. Z první fáze návrhu databáze máme zjištěno, jaké informace jsou nejčastěji vyhledávány. Vytvoříme několik zkušebních dotazů a formulářů a zjistíme, jestli zobrazují námi očekávaná data. Pokud jsme postupovali správně, neměli bychom objevit žádná duplicitní, vypočítávaná nebo dále dělitelná data.
36
8 POŽADAVKY NA ŘEŠENÝ PŘÍKLAD V následujících dvou kapitolách (9 Definice příkladu a 10 Scénáře pro procvičení zvolených témat) najdete zadání příkladu od samotného návrhu až po tvoření maker. Příklad v následující kapitole počítá se základní znalostí Accessu uživatelem, nebo alespoň s jeho schopností si potřebné věci najít. Všechna zadání budou následovat řešení, ale každý by si měl tato zadání nejprve vyzkoušet sám, a teprve po neúspěchu se podívat na správné řešení. Začneme návrhem databáze, o jehož správnosti jsme se mohli dočíst v minulé kapitole (7 Návrh databáze). Po jasně řečeném zadání, co by databáze měla obsahovat, bychom se měli zamyslet a vyzkoušet si udělat podrobný návrh databáze, tabulek a jejich obsahů včetně datových typů všech polí. Příklad bude sestaven tak, aby obsahoval všechny typy relací, včetně tří typů relací rekurzivních, proto, abychom si na praktickém příkladu vyzkoušeli vytvoření každé relace. Rozhodla jsem se vynechat relaci 1:1, jejíž použití je v realitě velmi omezené a mnohem častěji se data dají umístit do jedné tabulky. Relace 1:1 by se použila, pokud by tabulka již existovala a my bychom ji potřebovali o nějaké sloupce rozšířit, ale zároveň bychom ji nechtěli měnit. Tak by se data dala do jiné tabulky a propojila by se pomocí relace 1:1. V další části nazvané Inteligentní kontrola vstupních dat se naučíme ve třech podkapitolách, jak udělat několik omezení tak, aby byla vkládaná data ve správném formátu a eliminovaly se tak případné chyby. Tato omezení se dají vytvořit pomocí Vstupní masky, Ověřovacího pravidla a Ověřovacího textu, a všechny tři si vyzkoušíme vytvořit. Dalším tématem jsou Vyhledávací položky neboli dotazy. Známe několik typů dotazů a v našem příkladu budeme vytvářet i jednoduché dotazy i složité. Stejně tak se naučíme, jak zacházet se speciálními typy dotazů. Jde o dotazy s vypočítávanými položkami a dotazy s vyhledávacími sloupci. V poslední kapitole praktické části se naučíme pracovat s makry a nějaké si také vytvoříme.
37
9 DEFINICE PŘÍKLADU (NÁVRH PŘÍKLADU K ŘEŠENÍ) Máme za úkol vytvořit databázi pro vysoce prestižní kosmetický a estetický salón. U každého zákazníka se eviduje i jeho adresa a každý jich může mít více, rozdělují se na korespondenční a trvalou. Všichni zaměstnanci se evidují a každý zaměstnanec může, ale nemusí, být nadřízeným jinému zaměstnanci. Každý zaměstnanec má svou specializaci v salónu, kterou vykonává a někteří jich mají i více než jednu. Protože tento salón poskytuje svým klientům velice nadstandartní služby, je třeba evidovat u každého zákazníka všechny služby a procedury, které podstoupil, kdy tyto služby podstoupil a kdo každou vykonal. Každá služba a procedura se může skládat z několika jiných služeb a procedur (salón nabízí množství různých balíčků a kompletních péčí) a zároveň může být součástí několika jiných služeb a procedur. Poslední věc, která se v salónu eviduje, jsou rezervace. Kdo rezervaci udělal, na jakou proceduru, na kdy a kým bude služba vykonána.
38
10 SCÉNÁŘE PRO PROCVIČENÍ ZVOLENÝCH TÉMAT Pro všechny následující příklady se předpokládá, že čtenář s Accessem umí pracovat alespoň uživatelsky. 10.1
Návrh databáze
Jako první si vyzkoušíme vytvořit návrh takto zadané databáze. Pozorně si přečteme zadání a podle něj si rozmyslíme, jak budeme v tomto případě postupovat. Návrh nám odhalí spoustu nedostatků, se kterými bychom se při samotném vytváření bez rozmyslu potýkali. Pokud se bude váš návrh lišit od mého, není to chyba, určitě se dá nalézt více způsobů, jak tabulky zpracovat. Tato řešení jsou jen jednou z možností, jak požadavky na databázi vyřešit. Návrh tabulek Osoba
Nadřízený
Služba_složení
ID_Osoba
ID_Nadřízený
ID_Služba_složení
Jméno
ID_Zaměstnanec_podřízený
ID_Služba_celková
Příjmení
ID_Zaměstnanec_nadřízený
ID_Služba_část
Specializace
Evidence
Adresa
ID_Specializace
ID_Evidence
ID_Adresa
Název
ID_Osoba
ID_Osoba
Popis
ID_Zaměstnanec
Rodné číslo
Ulice Město PSČ ID_Typ Typ_adresy ID_Typ Název
Specializace_Zaměstnanec ID_Specializace_Zaměstnanec ID_Zaměstnanec ID_Specializace Služba ID_Služba Název
Zaměstnanec
Popis
ID_Zaměstnanec
Cena
ID_Služba Datum Poznámka Rezervace ID_Rezervace Datum ID_Osoba ID_Zaměstnanec ID_Služba
ID_Osoba
39
Jediné, co by vás v tuhle chvíli mohlo zmást, je vytvoření tabulky Nadřízený a Služba_složení, ale jelikož v kapitole 5 Základní pojmy relačních databází, jsme se dočetli o relacích rekurzivních, víme, že pro nadřízenost a složení služeb je budeme potřebovat. V Accessu se rekurzivní relace tvoří pomocnou tabulkou, jejíž hodnoty odkazují na jednu tabulku. Například v tabulce Nadřízený odkazuje jak pole ID_Zaměstnanec_podřízený, tak pole ID_Zaměstnanec_nadřízený na stejné pole ID_Zaměstnanec v tabulce Zaměstnanec. 10.2
Datové typy
Když začneme takto navrženou databázi vytvářet, musíme si určit, jaké datové typy budou jednotlivá pole tabulek mít. Pro všechna pole ID_název_tabulky dáme datový typ Automatické číslo. Datum bude typu Datum a čas, pole Cena typu Měna a pro ostatní pole Text. Pro všechna ID, která jsou v tabulce jako cizí klíč z jiné tabulky, nastavíme datový typ Číslo. Pozor na rodné číslo a PSČ, tato dvě pole ve skutečnosti nejsou čísla, ale texty, protože se s nimi jako s čísly nebude pracovat. Když se na takto vytvořené tabulky podíváme, napadlo by nás, že typ adresy v tabulce Adresa, by se dal vybrat z rozbalovacího menu a stejně tak Zákazník, Zaměstnanec a Služba v Evidenci a Rezervaci, Specializace u Zaměstnance a v tabulce Specializace_Zaměstanec a Služba v tabulce Služba_složení. Vytvořme tedy pro tato pole pomocí průvodce Accessu datový typ Průvodce vyhledáváním. Protože pomocí průvodce vyhledáváním nám Access umožňuje vybrat pro jedno pole více hodnot najednou (což ve skutečnosti není pravda a Access vše ukládá zvlášť, jenom pro nás to vypadá jako více hodnot v jednom poli) nepotřebujeme nadále tabulku Specializace_Zaměstnanec a pomocí Průvodce vyhledáváním ji můžeme vynechat. Během spojování tabulek pomocí Průvodce vyhledáváním jste si určitě všimli, že pro vyplňování pole Zaměstnanec v několika tabulkách by bylo výhodné, aby se nezobrazovalo jeho ID, ale jeho jméno. To bohužel v tomto případě není možné a dají se vybrat údaje pouze z jedné tabulky. Pokud bychom data vkládali pomocí
40
formuláře, už by se dalo zajistit, aby se dalo zapsat nebo vybrat přímo jméno zaměstnance. 10.3
Relace
Pokud jste postupovali správně a vybírali pole, která spolu opravdu souvisí, měly by vaše relace vypadat zhruba tak, jak je vidět na následujícím obrázku. Pole s hodnotou název_pole.Value znamenají, že je tam možnost vložit více údajů. V prvním případě u Specializace může mít jeden zaměstnanec specializací více. Access tato data uloží do samostatných řádků, aby nebylo v jedné buňce více údajů, ale tyto řádky pro nás nebudou viditelné a obě (či více) specializace uvidíme najednou v jedné buňce. Stejné je to u dalších dvou případů Zaměstnanec_podřízený a Služba_část, zaměstnanec může mít více podřízených a služba se může skládat z několika služeb.
Obr.1 Relace
Pokud vás matou tabulky Služba_1 a Zaměstnanec_1, nejsou to opravdové tabulky, jsou jen pro potřeby Accessu a relace, v obou případech rekurzivní, se vytvoří druhá kopie tabulky, abychom je mohli pomocí relace spojit. Rekurzivní relace tedy vypadá jako relace normální, jen je mezi tabulkami Zaměstnanec, Nadřízený a Zaměstnanec_1 a Služba, Služba_složení a Služba_1, kde tabulky Zaměstnanec a Zaměstnanec_1 a Služba a Služba_1 odkazují na jednu a tu samou tabulku.
41
Při vytváření relací pomocí průvodce vyhledáváním máme stejně jako při vytváření normálních relací možnost zajistit referenční integritu a mazání a aktualizaci polí v kaskádě. Je to důležitá možnost, pokud smažeme jednu osobu, chtěli bychom, aby se společně s ní smazala i evidence jejích objednávek, rezervací a adresa, proto nesmíme zapomenout na nastavení těchto možností. 10.4
Inteligentní kontrola vstupních dat
V návrhovém zobrazení tabulky máme u každého pole možnost omezit vstupní data pomocí několika pravidel. Na následujícím obrázku vidíme všechna možná omezení, která se dají poli nastavit. První je velikost pole, která znemožňuje uživateli psát zbytečná data do tabulky. Například pokud omezíme počet znaků v poli Jméno na 15 znaků, zamezíme tak, aby uživatel do kolonky pro křestní jméno omylem vepsal celé své jméno včetně příjmení. Do pole Výchozí hodnota vepíšeme do uvozovek text, který chceme, aby byl primárně zobrazen, pokud by uživatel nic nezadal. Pokud nastavíme hodnotu u Je nutno zadat na Ano, musí být u každého záznamu tato položka povinně vyplněna. Nastavením Povolit nulovou délku na Ne, nedovolujeme uživateli nechat pole prázdné.
Obr. 2 Návrhové zobrazení tabulky 1
Další pole, kterého si na předchozím obrázku můžeme všimnout, je pole Indexovat. Indexování napomáhá rychlejšímu vyhledávání a řazení. Toto pole můžeme
42
nastavit na tři hodnoty. Nastavení na Ne znamená, že se data nebudou indexovat, Ano (duplicita povolena) znamená, že se pole bude indexovat a záznamy v tomto poli se mohou opakovat, Ano (bez duplicity) znamená, že se pole bude indexovat a každý záznam v tomto poli musí být jedinečný. Na předchozím obrázku jsou vidět pole, která můžeme nastavit, pokud je datovým typem Text. Na následujícím obrázku vidíme nastavení pro datový typ Číslo a pole Počet desetinných míst. Nastavit tuto možnost máme i pro datový typ Měna.
Obr. 3 Návrhové zobrazení tabulky 2
Pole Vstupní masku, Ověřovací pravidlo a Ověřovací text jsem vynechala záměrně, na několika následujících stranách se na ně zaměříme. V následujících třech podkapitolách si ukážeme, jak uživatele omezit tak, aby data, která do tabulek uživatel vepisuje, byla správná. Pro tyto potřeby si přidáme do tabulky Osoba pole Telefonní číslo a e-mail. 10.4.1 Vstupní maska Vstupní maska napovídá uživateli, který vyplňuje data do tabulek, jak má vypadat údaj, který je do pole vyplňován. Seznam zástupných znaků, a co znamenají, nalezneme v nápovědě Accessu a na webové stránce zdroje [8]. Vytvořte vstupní masky tak, aby: a) jméno, příjmení, ulice, město a názvy služeb, specializací a typů adres začínaly velkým písmenem, b) telefonní číslo obsahovalo 9 znaků a po třech byla vždy mezera, c) rodné číslo obsahovalo 9 nebo 10 znaků a po šestém bylo odděleno mezerou, d) PSČ obsahovalo 5 znaků a po třetím bylo odděleno mezerou,
43
e) a datum v evidenci a rezervacích bylo ve formátu DD.MM.RRRR HH:MM, datum odděleno od času mezerou. Řešení: a) Jestli má být písmeno velké nebo malé určíme pomocí znamének větší a menší, první písmeno je povinné a velké, další znaky už budou nepovinné (záleží na délce příjmení, jména, názvu atd.) a malé. Tedy v kolonce vstupní maska bude vepsáno: >L
44
číslice ani nepovede zapsat do takto omezených polí, protože „?“ se nastavují pouze písmena. Operátory pro zadávání ověřovacích pravidel najdeme v nápovědě Accessu a na webové stránce zdroje [8]. Pokusme se uživatele omezit co nejvíce, abychom eliminovali co největší množství chyb, překlepů a nesprávných údajů. Ověřovací pravidlo nám umožňuje tato omezení zadat tak, aby se uživateli automaticky ukázalo, že zadal nějaký nesprávný údaj a v kombinaci se vstupní maskou a ověřovacím textem, mu můžeme co nejvíce napovědět, jak má údaj vypadat. Vytvořte ověřovací pravidlo tak, aby se do následujících dat daly zadat pouze platné informace: a) telefonní číslo, b) rodné číslo, c) datum a čas (použijte 24-hodinový formát času), d) pro e-mail není vhodné zadávat vstupní masku, protože jeho formát není jedinečný. Ale ověřovací pravidlo se pro e-mail nastavit dá, protože jsou určité znaky, které musí každý e-mail obsahovat. Řešení: a) Telefonní číslo je standardizované a má vždy devět číslic. Na prvním místě nemůže být 0 a na všech ostatních pozicích mohou být číslice 0-9. Použijeme operátor LIKE a do uvozovek vepíšeme, v jakém rozsahu mají jednotlivé znaky být, tedy celé ověřovací pravidlo bude vypadat takto: Like "[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]". b) Nejprve si řekneme, jak má vypadat správné rodné číslo. Na prvních dvou pozicích je rok, tedy tu mohou být číslice od 0 do 9. Druhé dva znaky jsou měsíc, který se ale dělí na dva typy. U mužů může být na místě třetího znaku 1 nebo 2 a u žen 5 nebo 6, jelikož se u žen k třetímu číslu přičítá číslo 5. Čtvrtý znak může být mezi 0 a 9. Pátý znak je první číslo dne, tedy zde může být číslice od 0 do 3 a na šesté pozici číslice od 0 do 9. Pak následuje lomítko a za lomítkem mohou být tři nebo čtyři číslice, všechny mezi 0 až 9. Tři číslice jsou pro osoby, které se narodily dříve. Všechny tyto možnosti dáme do uvozovek a oddělíme je operátory LIKE a OR LIKE. Pro ženy a 45
pro muže máme pro každého jednu možnost a za lomítkem mohou být u každého tři nebo čtyři znaky, pokud se žena narodila v říjnu až prosinci, může být na čtvrté pozici číslice od 0 do 2 a stejně tak pro muže. Výsledné ověřovací pravidlo bude vypadat následovně: Like "[0-9][0-9][5][0-9][03][0-9]/[0-9][0-9][0-9]" Or Like "[0-9][0-9][6][0-2][0-3][0-9]/[0-9][09][0-9]" Or Like "[0-9][0-9][6][0-2][0-3][0-9]/[0-9][0-9][0-9][0-9]" Or Like "[0-9][0-9][5][0-9][0-3][0-9]/[0-9][0-9][0-9][0-9]" Or Like "[0-9][09][1][0-9][0-3][0-9]/[0-9][0-9][0-9]" Or Like "[0-9][0-9][2][0-2][0-3][09]/[0-9][0-9][0-9]" Or Like "[0-9][0-9][2][0-2][0-3][0-9]/[0-9][0-9][09][0-9]" Or Like "[0-9][0-9][1][0-9][0-3][0-9]/[0-9][0-9][0-9][0-9]". Další omezení pro rodné číslo může být jeho dělitelnost číslem 11, ale často dochází k nesprávně přiřazeným rodným číslům a chybám, proto není toto omezení v praxi pro rodné číslo velmi praktické. c) Datum a čas požadujeme ve tvaru DD.MM.RRRR HH:MM, datum od času oddělen mezerou a den, měsíc a rok tečkou, jak bylo již uvedeno u vstupní masky. Teď pomocí ověřovacího pravidla zajistíme, aby se dala vyplňovat pouze platná data a časy. První dvě čísla jsou den, tam mohou být hodnoty od 1 do 31, tedy na prvním místě mohou být číslice od 0 do 3 a na druhém od 0 do 9. Pro další číslici je to 0 nebo 1 a pro čtvrtou 0 až 9, to jsou znaky zastupující měsíc. Další čtyři znaky jsou pro rok, protože společnost neeviduje nic déle než 5 let, první znak roku bude nastaven na číslo 2 a druhý na 0, další dva budou v rozmezí 0 až 9. Pak následuje mezera a čas. Hodiny mohou být od 0 do 24, tedy na prvním místě bude číslice od 0 do 2 a na druhém od 0 do 9. Minuty mohou nabývat hodnot 0 až 59, tedy na prvním místě za dvojtečkou bude 0 až 5 a na druhém 0 až 9. Výsledné ověřovací pravidlo bude v tomto případě: Like "[0-3][0-9].[0-1][09].[2][0][0-1][0-9] [0-2][0-9]:[0-5][0-9]".
46
10.4.3 Ověřovací text Ověřovací text je na nastavení z omezovacích pravidel nejjednodušší. Pokud má uživatel zadat například číslo, můžeme do kolonky Ověřovací text napsat: „Musí být číslo!“. Ověřovací text upozorní uživatele, který vyplňuje data do tabulek, že udělal chybu a zároveň mu poví, jak tuto chybu opravit. Pro rodné číslo můžeme zadat: „Zadejte platné rodné číslo!“ a pokud zadáme neplatné rodné číslo, zobrazí se nám hláška, jako je na obrázku níže. Stejně tak můžeme postupovat u dalších polí, u kterých chceme uživateli napovědět, jaká data nebo v jakém formátu se mají do pole zapisovat. Tím jsme omezili uživatele a jeho vyplňování dat na maximum a povedlo se nám co nejvíce zamezit nesprávným nebo chybným položkám v databázi.
Obr.4 Ověřovací text
10.5
Dotazy a vyhledávací kritéria
Pro následující příklady budeme potřebovat v tabulce nějaká data, proto teď naplňte databázi zkušebními daty. Pokud se vám data nechtějí vypisovat, nalistujte nakonec na přílohu a podívejte se, jak vytvořit v Accessu makro, které vám vygeneruje náhodná data do databáze. Pomocí dotazů můžeme vybrat data z několika různých tabulek a zobrazit je v tabulce jediné.
47
10.5.1 Dotazy s vypočítávanými položkami Položky, které lze vypočítat nebo odvodit z jiných dat nebo jejich kombinací není vhodné ukládat vypočtené přímo do databáze. Docházelo by k chybám a nadbytečnosti dat. Pro tyto účely Access nabízí dotazy s vypočítávanými položkami. Vytvořte dotaz, který: a) zobrazí Příjmení a Jméno osoby z tabulky Osoba, její Rodné číslo a Datum narození, Věk a Pohlaví, b) zobrazí Příjmení a Jméno z tabulky Osoba a cenu, kterou osoba celkem v salónu už za služby utratila, c) zobrazí Příjmení a Jméno každého zaměstnance a jeho celkový výdělek, d) zobrazí celkový výdělek salónu. Řešení: a) Do návrhu dotazu si vybereme tabulku Osoba. V návrhu dotazu si do prvního sloupce zobrazíme Příjmení, do druhého Jméno a do třetího Rodné číslo. Pro čtvrtý, pátý a šestý sloupec použijeme funkce, které nám z rodného čísla zjistí Datum narození, Věk a Pohlaví každé osoby. Funkci můžeme libovolně pojmenovat a za dvojtečky vložit funkci. Do kulatých závorek vkládáme rodné číslo, ze kterého chceme věk získat. Výsledný výraz vypadá následovně: Věk: GetVekFromRC([Osoba].[Rodné číslo]). Stejně budeme postupovat u data narození a pohlaví. Tyto funkce nejsou v Accessu primárně zabudovány, proto si je musíme vytvořit sami. Jak tyto funkce vytvořit se dočtete v kapitole 10.6 Makra. Výsledek dotazu bude vypadat následovně:
Obr.5 Dotaz 1
Uložíme jako Osobní informace. 48
b) Pro druhý dotaz nebudeme potřebovat žádnou nově vytvořenou funkci a jde jen o to si správně určit, jaká data ze kterých tabulek budeme pro tento dotaz potřebovat. V návrhu dotazu zobrazíme tabulky Osoba, Evidence a Služba. Z tabulky Osoba budeme potřebovat jméno a příjmení všech zákazníků, z tabulky Evidence všechny služby, které každý z nich využil a z tabulky Služba cenu každé služby. První dva sloupce budou stejné jako v předchozím příkladu, ve třetím sloupci bude Cena z tabulky Služba, ze které pomocí souhrnu vypočítáme celkovou sumu. Pokud chceme, aby se zobrazovaný sloupec jmenoval jinak, než vybraný dotaz, uděláme to následovně: Celková útrata: Cena. Když dotaz spustíme, zobrazí se nám cena utracená každým zákazníkem. Pokud bychom chtěli zobrazit celkovou cenu utracenou všemi zákazníky bez pomoci dalšího dotazu, klikneme v záložce Domů na kartě Záznamy na možnost souhrny. Po vybrání souhrnu se nám dole zobrazí Součet a výsledná suma. Tento součet je zobrazen vždy, jak je vidět na obrázku Dotaz 2, ať jsme kurzorem na jakémkoliv záznamu v tabulce.
Obr.6 Dotaz 2
Uložíme jako Celková útrata zákazníka.
49
c) Pro tento dotaz vybereme v návrhovém zobrazení dotazu tabulky Osoba, Zaměstnanec, Evidence a Služba. Když tabulky zobrazíme v návrhovém zobrazení, budeme mít následovně nastavené relace:
Obr.7 Dotaz 3
Do prvního a druhého sloupce vybereme Jméno a Příjmení z tabulky Osoba a do třetího sloupce Cena z tabulky Služba, ze které pomocí souhrnu spočítáme celkovou sumu. V tuto chvíli ale nemáme sumu, kterou vydělal každý zaměstnanec, ale nějaká nepoužitelná data, která jsou na první pohled špatně (4 záznamy), protože zaměstnanců máme celkem 17 ale jen 9 z nich přímo vydělává salónu peníze. Tedy záznamů bychom měli mít devět. Musíme zrušit relaci ID_Osoba-Osoba mezi tabulkami Osoba a Evidence, aby se nám vybrali zaměstnanci a jejich výdělky a ne něco jiného. Po úpravě relací nám tabulka zobrazí požadované informace, které si můžeme prohlédnout na obrázku Dotaz 4. Pokud vložíme souhrn, jako u předchozího příkladu, získáme sumu, která ukazuje, kolik vydělali všichni zaměstnanci dohromady.
Obr.8 Dotaz 4
Uložíme jako Zaměstnanec výdělky. 50
d) Celkový výdělek salónu jsme si pomocí souhrnu zobrazili ve dvou předchozích příkladech. Jednou jako sumu, kolik celkem utratili zákazníci a podruhé jako sumu, kolik celkem vydělali zaměstnanci. Tyto sumy by se měly rovnat. Pokud chceme tuto sumu zobrazit pomocí samostatného dotazu, vybereme do návrhového zobrazení dotazu tabulky Evidence a Služba, a do jediného sloupce vybereme Cenu z tabulky Služba a pomocí souhrnu celkovou sumu. Po spuštění dotazu vidíme jediný záznam a to celkový výdělek salónu, jak je vidět na následujícím obrázku.
Obr.9 Dotaz 5
Uložíme jako Výdělek celkem. Vyzkoušeli jsme si několik výběrových dotazů a ukázali si způsoby, jak tyto dotazy vytvořit. Obdobně si můžete vytvořit mnoho dalších dotazů podle toho, jaká data potřebujete zobrazit. 10.5.2 Akční dotazy Před použitím akčního dotazu je dobré si tabulky zálohovat. Akční dotaz tabulky a data mění, může je mazat, aktualizovat a dokáže vytvořit i úplně novou tabulku. Každý z akčních dotazů má svoji vlastní ikonu. V návrhovém zobrazení dotazu v záložce Návrh na kartě Typ dotazu máme několik možných typů dotazu. V tuto chvíli se budeme soustředit na čtyři z nich, tedy na možné akční dotazy. Vytvářecí dotaz – může vytvořit celou tabulku. Přidávací dotaz – do stávající tabulky přidá záznamy z tabulky jiné. Struktury obou tabulek musí být stejné. Aktualizační dotaz – upravuje záznamy v dané tabulce. Mazací dotaz – smaže záznamy v dané tabulce podle zadaných kritérií. Každý z těchto akčních dotazů má vlastní ikonu, aby bylo poznat na první pohled, o jaký druh jde a co může s tabulkou udělat. Pokud poklepeme na akční dotaz
51
v seznamu vytvořených dotazů, vlevo dole v navigačním podokně, automaticky se nám zobrazí varování, že se pokoušíme spustit dotaz, který může data změnit. Vytvořte následující akční dotazy: a) vytvářecí dotaz: vytvořte tabulku, která bude zobrazovat Jméno a Příjmení zákazníka a všechny služby, které jim v salónu byly poskytnuty. Tabulka se bude jmenovat Zákazník_služba, b) aktualizační dotaz: zvedněte cenu všech služeb, které jsou nejčastěji poskytovány (prvních 10) o 10%, c) mazací dotaz: v tabulce Počet_služeb smažte všechny klienty, kteří v salónu využili méně jak 10 služeb. Tabulku Počet_služeb musíme nejdřív vytvořit. Řešení: a) V návrhovém zobrazení dotazu vybereme dotaz vytvářecí a zobrazíme si tabulky Osoba, Evidence a Služba. Do prvních dvou sloupců vybereme Příjmení a Jméno z tabulky Osoba a do třetího Název z tabulky Služba. Po spuštění dotazu se nám vytvoří nová tabulka, která se nám objeví ve výčtu tabulek v horní části navigačního podokna. Pokud budeme chtít dotaz upravit, musíme vyvolat místní nabídku a dát možnost Návrhové zobrazení. Pokud dotaz spustíme znovu s úpravou nebo bez ní, stávající tabulka Zákazník_služba se nám smaže a nahradí se tabulkou novou. Uložíme jako Vytvářecí dotaz Zákazník_služba. b) Access nám neumožňuje vytvořit tento dotaz jednoduše. Nemůžeme pomocí jednoho dotazu vybrat 10 nejvíce poskytovaných služeb a ve stejném dotazu je zdražit o 10%. Vytvoříme si tedy (vytvářecím dotazem) pomocnou tabulku Služba_použití, do které uložíme 10 nejvíce poskytovaných služeb a uložíme jako Vytvářecí dotaz: Služba_použití. V návrhovém zobrazení dotazu vyvoláme místní nabídku a zobrazíme si kód SQL a do dotazu vygenerovaného Accessem přidáme funkci TOP 10. Pak dotaz spustíme a vytvoří se nám pomocná tabulka. Teď můžeme vytvořit aktualizační dotaz pro zvednutí ceny služeb uvedených v tabulce Služba_použití o 10%. V návrhovém zobrazení dotazu vybereme aktualizační typ dotazu a zobrazíme si tabulky Služba_použití a Služba. 52
V jediném sloupci vybereme pole Cena z tabulky Služba, a do Aktualizovat do vepíšeme: Cena * 1,1. Pokud bychom teď dotaz spustili, aktualizovaly by se ceny všech služeb. Musíme vytvořit relaci mezi tabulkami Služba a Služba_použití, jako je na následujícím obrázku. Jednoduše tahem myši přesuneme pole Služba z tabulky Služba_použití na pole ID_Služba z tabulky Služba. Po vytvoření takovéto relace se po spuštění dotazu zdraží pouze 10 nejžádanějších služeb.
Obr.10 Akční dotaz 1
Uložíme jako Aktualizační dotaz Cena. c) Nejdříve si vytvoříme (vytvářecím dotazem) tabulku Služba_použití, ve které bude Příjmení a Jméno všech zákazníků a Počet služeb, které v salónu doposud využili. Pak vytvoříme mazací dotaz, který smaže všechny zákazníky, kteří využili méně než 10 služeb. Do kritérií zadáme <10 ze sloupce Počet služeb. Poté, co dotaz spustíme, uvidíme v tabulce (pokud jí máme otevřenou) následující záznamy:
Obr.11 Akční dotaz 2
53
Záznamy, které měly menší počet služeb než 10, byly odstraněny a v tabulce je to vidět. Když tabulku aktualizujeme, budou zobrazeny pouze platné záznamy. Uložíme jako Mazací dotaz Počet_služeb. 10.5.3 Křížové dotazy Křížové dotazy nám umožňují jednodušší a přehlednější zobrazení a čtení souhrnných dat. V záhlaví řádků jsou jedny hodnoty a v záhlaví sloupců druhé. Do polí se vyplňují další hodnoty pomocí některé z agregačních funkcí (Max, Count apod.) Vytvořte následující křížové dotazy, které: a) zobrazí, kolikrát jakou službu každý zaměstnanec vykonal, b) zobrazí, kolikrát každou službu využil každý zákazník. Řešení: a) V křížovém dotazu nemůžeme zobrazit údaje z více tabulek, ale nemusíme vybírat data pouze z tabulek, ale i z dotazů. Nejprve si tedy vytvoříme dotaz, který bude zobrazovat Jméno a Příjmení všech zaměstnanců (kteří provádějí nějaké služby, tedy automaticky nebudou zahrnuty recepční, vedoucí a sestry), Název služeb a počet kolikrát každou tuto službu vykonal. V návrhovém zobrazení dotazu musíme opět upravit relace, aby se nám vybíraly správné osoby, tedy zaměstnanci. Poté, co dotaz spustíme, vidíme, že výsledná tabulka není příliš ideální. Vidíme sice, kolikrát každý zaměstnanec službu vykonal, ale v každém řádku je jedna služba u jednoho zaměstnance. Máme tedy množství záznamů, kterými se musíme prokousat. Pomocí průvodce dotazem vytvoříme vhodný křížový dotaz, který nám vyhledávání, čtení a práci s takovýmito údaji velmi usnadní. Do záhlaví řádků vybereme Název (služeb) a do záhlaví sloupců Příjmení (zaměstnance), pro průnik polí použije Počet služeb z dotazu a agregační funkci použijeme Max.
54
Výsledná tabulka bude vypadat následovně:
Obr.12 Křížový dotaz 1
Sloupec Název můžeme ukotvit a dolů můžeme opět přidat pomocí souhrnu součet. Uložíme jako Zaměstnanec služby počet_křížový dotaz. b) Nejprve si musíme vytvořit vhodný dotaz, který bude zobrazovat námi požadované informace. Když se nad tím zamyslíme, máme připravený vytvářecí dotaz z minulé kapitoly, který zobrazuje zákazníky a služby které využili. Není v tuto chvíli úplně praktický, protože v dotazu chybí pole, které budeme počítat. Na opravě ale není nic složitého. Do stávajícího dotazu přidáme sloupec ID_Evidence z tabulky Evidence, spustíme a přepíšeme stávající tabulku. Pomocí průvodce dotazem vytvoříme křížový dotaz, který bude v záhlaví řádků zobrazovat Službu, v záhlaví sloupců Příjmení zákazníka a v průniku polí počet, kolikrát tento zákazník službu využil. Opět můžeme přidat souhrnné sloupce a ukotvit sloupec Název.
55
Výsledný dotaz vypadá následovně:
Obr.13 Křížový dotaz 2
Uložíme jako Zákazník_služba_křížový dotaz. Pomocí křížových dotazů tedy můžeme lépe zobrazovat data a výhodněji je seskupovat pomocí více kritérií. 10.6
Makra
MS Access 2010 umožňuje vytvořit, kromě maker v editoru kódu VBA (Visual Basic for Aplications), datová makra a makra uživatelského prostředí. V našem příkladu si ukážeme vlastní zápis maker v editoru kódu VBA. Pro tvoření těchto maker je potřebná alespoň minimální zkušenost s programováním, nejlépe ve Visual Bacisu. Pokud by měl čtenář zájem naučit se tvořit datová makra a makra uživatelského prostředí, doporučuji podrobnou nápovědu na webu Microsoft Office. Vytvořte makra, která z rodného čísla odvodí: a) datum narození, b) věk, c) pohlaví.
56
Řešení: Zvolte Databázové nástroje a na kartě Makro stiskněte tlačítko Visual Basic. Otevře se okno editoru VBA, ve kterém založte nový modul pro zápis maker. a) Rodné číslo si musíme nejprve „rozsekat“ na den, měsíc a rok. Pomocí funkce Mid (která vybírá ze stringu znaky, do závorky se zadává znak, od kterého se začne a počet znaků, které se mají vybrat) „rozsekáme“ rodné číslo na dvojice, které představují den, měsíc a rok. Pomocí funkce DatePart zjistíme jaký je aktuální rok a pomocí funkce Mid z něj získáme dvě poslední čísla: aktualniRok = DatePart("yyyy", Now) aktualniRok = Mid(aktualniRok, 3, 2) S použitím jednoduché podmínky upravíme měsíc žen do požadované formy: If mesic > 50 Then mesic = mesic - 50 End If Nakonec použijeme aktuální datum pro to, abychom osoby, které mají poslední dvojčíslí roku menší než aktuální rok, zařadili do nového tisíciletí a neudělali z nich osoby staré sto a více let:
If rok < aktualniRok Then rok = 2000 + rok Else rok = 1900 + rok End If
Nakonec spojíme všechny části do jednoho a vrátíme jako výsledek funkce GetDateFromRC:
datum = rok & "-" & mesic & "-" & den GetDatumFromRC = datum
57
Pokud pak funkci zavoláme v dotazu (jak jsme si již vyzkoušeli) vrátí nám datum narození dané osoby. b) S věkem je to ještě o něco jednodušší. Zjistíme aktuální datum a pomocí funkce DateDiff od sebe navzájem odečteme roky a funkce GetVekFromRC nám vrátí věk dané osoby:
aktualniDatum = Now datum = GetDatumFromRC(rc) Vek = DateDiff("yyyy", datum, aktualniDatum) GetVekFromRC = Vek
c) U pohlaví si z rodného čísla opět pomocí funkce Mid zjistíme měsíc a pomocí jednoduché podmínky zjistíme pohlaví dané osoby
mesic = Mid(rc, 3, 2) If mesic > 50 Then GetPohlaviFromRc = "Žena" Else GetPohlaviFromRc = "Muž" End If
Vyzkoušeli jsme si několik maker. Makra se dají použít skoro na cokoliv v Accessu (a nejen v něm, ale i v jiných programech) a použití a typy jsou nekonečné. Je na vás samotných, jaká makra se pro svoji databázi rozhodnete použít a s čím vším si ulehčíte práci.
58
ZÁVĚR Cílem mé práce bylo provést rešerši souborových, jednouživatelských systémů řízení báze dat, shrnout základní problematiku relačních databází a základní metodiku návrhu databáze, a v praktické části procvičit hlavní funkcionality Accessu na jednom souvislém příkladu. V teoretické části se dočteme o historii vývoje souborových systémů řízení báze dat a o pěti, podle mě, nejvýznamnějších zástupcích se dočteme podrobněji. Pro ne moc zručného a zkušeného uživatele Accessu je důležitá kapitola 6 Analýza současného stavu, ve které se dozví, jaké knihy a on-line kurzy jsou v dnešní době dostupné. Z těchto knih a kurzů si může na základě mého porovnání vybrat, který je pro něj nejvhodnější a seznámit se tak s Accessem sám. Praktická část je tvořena jedním souvislým příkladem na tvorbu databázového systému pro kosmetický salón. Na tomto příkladu jsou ukázána vstupní omezení pro vkládání dat do tabulek, vyhledávání a zobrazování dat pomocí několika typů dotazů a vytváření a použití maker. Po přečtení a důkladném a podrobném vyzkoušení všech uvedených příkladů bude čtenář této práce schopen navrhnout vlastní databázi a v Accessu ji také vytvořit. Bude schopen si vytvořit vlastní makra na funkce, které by mu v Accessu chyběly.
59
SEZNAM ZDROJŮ 1.
CODD, E. F. 1970 [cit. 2011-08-21]. A Relational Model of Data for Large Shared Data Banks. Dostupné z WWW:
.
2.
CODD, E. F. Relational Database: A practical Foundation for Productivity. San Jose : IBM Research Laboratory, 1981. 409 s.
3.
DataBased Intelligence, Inc. [online]. 2011 [cit. 2011-07-21]. Knowledgebase. Dostupné z WWW: .
4.
Development of an object-oriented DBMS. Portland, United States : Oregon, 1986. 472 482 s. ISBN 0-89791-204-7.
5.
KRUCZEK, Aleš. Microsoft Access 2010 : Podrobná uživatelská příručka. Brno : Computer Press, 2010. 392 s. ISBN 978-80-251-3289-0.
6.
Linuxsoft.cz [online]. 6.8.2004 [cit. 2011-07-21]. PHP (34) - Úvod do databází. Dostupné z WWW: .
7.
Managed Dedicated Servery [online]. 2009 [cit. 2011-07-21]. Typy vztahů - relací. Dostupné z WWW: .
8.
Microsoft Corporation [online]. 2011 [cit. 2011-07-21]. Access 2010. Dostupné z WWW: .
9.
PC World [online]. 1.5.2002 [cit. 2011-07-21]. Nic neběží jako liška (tm) - Čím je dnes Visual FoxPro. Dostupné z WWW: .
10. PECINOVSKÝ, Josef. Excel a Access 2010 : Efektivní zpracování dat na počítači. Vyd. 2. Praha : Grada, 2005. 200 s.
11. PÍSEK, Slavoj. Access 2010. 1. vydání. Praha : Grada, 2011. 160 s. ISBN 978-80-2473653-2. 12. Root.cz [online]. 19.10.2001 [cit. 2011-07-21]. Historie relačních databází. Dostupné z WWW: .
13. The History of FoxPro [online]. 2001 [cit. 2011-07-21]. The History of FoxPro. Dostupné z WWW: . 14. World Class Software Solutions [online]. 2011 [cit. 2011-07-21]. Microsoft Access History. Dostupné z WWW: . 15. Www.OpenOffice.cz : portál českých a slovenských uživatelů [online]. 2010 [cit. 2011-0721]. Práce s databází Base z OpenOffice.org. Dostupné z WWW: . ISSN 12149608.
SEZNAM OBRÁZKŮ Obr. 1 Relace ............................................................................................ 41 Obr. 2 Návrhové zobrazení tabulky........................................................... 42 Obr. 3 Návrhové zobrazení tabulky 3 ........................................................ 43 Obr. 4 Ověřovací text ............................................................................... 47 Obr. 5 Dotaz 6 .......................................................................................... 48 Obr. 6 Dotaz 7 .......................................................................................... 49 Obr. 7 Dotaz 8 .......................................................................................... 50 Obr. 8 Dotaz 9 .......................................................................................... 50 Obr. 9 Dotaz 10 ........................................................................................ 51 Obr. 10 Akční dotaz 3............................................................................... 53 Obr. 11 Akční dotaz 4............................................................................... 53 Obr. 12 Křížový dotaz 3 ........................................................................... 55 Obr. 13 Křížový dotaz 4 .......................................................................... 56
PŘÍLOHA Zdrojový kód všech maker použitých v ilustračním příkladu: Option Compare Database 'Procedura, která vygeneruje náhodné osoby do tabulky Osoba v momentálně otevřené databázi. Sub GenerovaniOsob() 'Deklarace proměnných. Dim jmenoM(20) As String Dim jmenoZ(20) As String Dim prijmeni(40) As String Dim emaily(5) As String 'Pole jmen, příjmení a e-mailů. jmenoM(0) = "Oskar" jmenoM(1) = "Gustav" jmenoM(2) = "Dominik" jmenoM(3) = "Kristián" jmenoM(4) = "Soběslav" jmenoM(5) = "Roman" jmenoM(6) = "Vavřinec" jmenoM(7) = "Alan" jmenoM(8) = "Jáchym" jmenoM(9) = "Ludvík" jmenoM(10) = "Bernard" jmenoM(11) = "Bohuslav" jmenoM(12) = "Bartoloměj" jmenoM(13) = "Radim" jmenoM(14) = "Luděk" jmenoM(15) = "Augustýn" jmenoM(16) = "Bronislav" jmenoM(17) = "Boris" jmenoM(18) = "Boleslav" jmenoM(19) = "Lubor" jmenoZ(0) jmenoZ(1) jmenoZ(2) jmenoZ(3) jmenoZ(4) jmenoZ(5) jmenoZ(6)
= = = = = = =
"Miluše" "Oldřiška" "Lada" "Zuzana" "Klára" "Alena" "Hana"
jmenoZ(7) = "Petra" jmenoZ(8) = "Johana" jmenoZ(9) = "Sandra" jmenoZ(10) = "Evelína" jmenoZ(11) = "Vladěna" jmenoZ(12) = "Pavlína" jmenoZ(13) = "Linda" jmenoZ(14) = "Adéla" jmenoZ(15) = "Jindřiška" jmenoZ(16) = "Regína" jmenoZ(17) = "Mariana" jmenoZ(18) = "Daniela" jmenoZ(19) = "Irma" prijmeni(0) = "Novák" prijmeni(1) = "Dvořák" prijmeni(2) = "Horák" prijmeni(3) = "Kadlec" prijmeni(4) = "Donutil" prijmeni(5) = "Kozák" prijmeni(6) = "Holub" prijmeni(7) = "Stehlík" prijmeni(8) = "Strnad" prijmeni(9) = "Kratochvíl" prijmeni(10) = "Vlasák" prijmeni(11) = "Hanák" prijmeni(12) = "Šulc" prijmeni(13) = "Dohnal" prijmeni(14) = "Nekvasil" prijmeni(15) = "Zbořil" prijmeni(16) = "Řezáč" prijmeni(17) = "Švec" prijmeni(18) = "Vodák" prijmeni(19) = "Čech" prijmeni(20) = "Abrhám" prijmeni(21) = "Dvořák" prijmeni(22) = "Ostrčil" prijmeni(23) = "Plašil" prijmeni(24) = "Skácel" prijmeni(25) = "Skopal" prijmeni(26) = "Srb" prijmeni(27) = "Rus" prijmeni(28) = "Mlynář" prijmeni(29) = "Pekař" prijmeni(30) = "Dokulil" prijmeni(31) = "Krčmář" prijmeni(32) = "Kohout" prijmeni(33) = "Pražák"
prijmeni(34) prijmeni(35) prijmeni(36) prijmeni(37) prijmeni(38) prijmeni(39) emaily(0) emaily(1) emaily(2) emaily(3) emaily(4)
= = = = =
= = = = = =
"Verner" "Zweig" "Müller" "Taufer" "Pergler" "Vlk"
"seznam.cz" "centrum.cz" "gmail.com" "email.cz" "atlas.cz"
'Deklarace proměnných Dim jmeno As String Dim prij As String Dim rc As String Dim email As String Dim telefon As String Dim pohlavi As Integer 'Deklarace proměnných rodného čísla. Dim den As String Dim mesic As String Dim rok As String Dim modulo As Integer Dim opakovani As Integer opakovani = 0 'Cyklus, který vytvoří 200 osob a vepíše je do tabulky. Do 'Generování náhodného data narození. pohlavi = GetRandom(0, 1) den = GetRandom(1, 30) mesic = GetRandom(1, 12) rok = GetRandom(1, 99) 'Korekce data narození. If mesic = 2 And den > 28 Then den = 28 End If If Len(den) = 1 Then den = "0" & den End If
If Len(mesic) = 1 Then mesic = "0" & mesic End If If Len(rok) = 1 Then rok = "0" & rok End If 'Korekce měsíce podle pohlaví a generování jména. If pohlavi = 1 Then mesic = mesic + 50 jmeno = jmenoZ(Rnd * 19) prij = prijmeni(Rnd * 39) & "ová" Else jmeno = jmenoM(Rnd * 19) prij = prijmeni(Rnd * 39) End If 'Vytoření koncovky rodného čísla, aby bylo dělitelné 11. modulo = (rok & mesic & den) Mod 1111 koncovka = (Int((Rnd * 8)) * 1111 - modulo + 1111) If (koncovka < 100) Then koncovka = koncovka * 11 End If rc = rok & mesic & den & "/" & koncovka 'Vytvoření e-mailu ze jména a příjmení. email = LCase(OdstranDiaktritiku(jmeno & "." & prij & "@" & emaily(Rnd * 4))) 'Generování náhodného telefonního čísla. telefon = Int(Rnd * 899) + 100 & " " & Int(Rnd * 899) + 100 & " " & Int(Rnd * 899) + 100 'Přidávací dotaz. Dim dotaz As String dotaz = "INSERT INTO Osoba(Jméno, [Příjmení], [Rodné číslo], [Telefonní číslo], [e-mail]) VALUES('" & jmeno & "','" & prij & "','" & rc & "','" & telefon & "','" & email & "')" CurrentDb.Execute dotaz, dbFailOnError opakovani = opakovani + 1 Loop While opakovani < 200
End Sub 'Funkce, která vrátí náhodné číslo v daném rozsahu. Public Function GetRandom(min As Long, max As Long) As Integer Randomize GetRandom = Int(Rnd * (max + 1 - min)) + min End Function 'Funkce, která odstraní diakritiku. Function OdstranDiaktritiku(ByVal text As String) 'Deklarace proměnných. Dim x As Long Dim pozice As Long Const sDiakritikou As String = "ěščřžýáíéĚŠČŘŽÝÁÍÉüůú" Const bezDiakritiky As String = "escrzyaieESCRZYAEIuuu" 'Nahrazení znaků s diakritikou znaky bez diakritiky. For x = 1 To Len(text) pozice = InStr(sDiakritikou, Mid(text, x, 1)) If pozice Then Mid(text, x) = Mid(bezDiakritiky, pozice, 1) End If Next OdstranDiaktritiku = text End Function 'Procedura, která vytváří náhodnou evidenci salónu. Sub GenerovaniEvidence() 'Deklarace proměnných. Dim zamestnanec As Integer Dim osoba As Integer Dim sluba As Integer Dim specializace As Integer Dim datum As Date Dim opakovani As Integer
Dim dotaz As String datum = "2000-01-01" 'Vytvoří 2000 záznamů. Do While opakovani < 2000
osoba = GetRandomOsoba() sluzba = GetRandomSluzba() specializace = GetSpecializaceFromSluzba(sluzba) zamestnanec = GetRandomZamestnanec(specializace) datum = DateAdd("n", GetRandom(10, 2000), datum) 'Přidávací dotaz. dotaz = "INSERT INTO Evidence(Osoba, [Zaměstnanec], [Služba], [Datum a čas]) VALUES('" & osoba & "','" & zamestnanec & "','" & sluzba & "','" & Format(datum, "dd.MM.yyyy HH:nn") & "')" CurrentDb.Execute dotaz, dbFailOnError opakovani = opakovani + 1 Loop End Sub 'Procedura, která vygeneruje náhodné rezervace do salónu. Sub GenerovaniRezervace() 'Deklarace proměnných. Dim zamestnanec As Integer Dim osoba As Integer Dim sluba As Integer Dim specializace As Integer Dim datum As Date Dim opakovani As Integer
Dim dotaz As String datum = "2000-01-01" 'Vytvoří 1000 záznamů. Do While opakovani < 1000 osoba = GetRandomOsoba() sluzba = GetRandomSluzba() specializace = GetSpecializaceFromSluzba(sluzba) zamestnanec = GetRandomZamestnanec(specializace) datum = DateAdd("n", GetRandom(10, 2000), datum) 'Přidávací dotaz. dotaz = "INSERT INTO Rezervace([Datum a čas], Osoba, [Zaměstnanec], [Služba] ) VALUES('" & Format(datum, "dd.MM.yyyy
HH:nn") & "','" & osoba & "','" & zamestnanec & "','" & sluzba & "')" CurrentDb.Execute dotaz, dbFailOnError opakovani = opakovani + 1 Loop End Sub 'Funkce, která se seznamu zaměstnanců jednoho náhodně vybere. Function GetRandomZamestnanec(ByVal specializace As Integer) As Integer 'Deklarace proměnných. Dim db As Database Dim recSet As DAO.Recordset Dim dotaz As String Dim random As Integer Dim zamestnanci As New VBA.Collection Set db = CurrentDb() dotaz = "SELECT ID_Zaměstnanec FROM Zaměstnanec WHERE Zaměstnanec.Specializace.Value = " & specializace Set recSet = db.OpenRecordset(query) Do While recSet.EOF = False zamestnanci.Add (recSet("ID_Zaměstnanec")) recSet.MoveNext Loop recSet.Close Set recSet = Nothing random = GetRandom(1, zamestnanci.Count) GetRandomZamestnanec = zamestnanci.Item(random) End Function 'Funkce, která náhodně vybere jednu službu. Function GetRandomSluzba() As Integer 'Deklarace proměnných. Dim db As Database Dim recSet As DAO.Recordset Dim query As String
Dim random As Integer Dim sluzby As New VBA.Collection Set db = CurrentDb() dotaz = "SELECT ID_Služba FROM Služba" Set recSet = db.OpenRecordset(dotaz) Do While recSet.EOF = False sluzby.Add (recSet("ID_Služba")) recSet.MoveNext Loop recSet.Close Set recSet = Nothing random = GetRandom(1, sluzby.Count) GetRandomSluzba = sluzby.Item(random) End Function 'Funkce, která vybere náhodnou osobu. Function GetRandomOsoba() As Integer 'Deklarace proměnných. Dim db As Database Dim recSet As DAO.Recordset Dim query As String Dim random As Integer Dim osoby As New VBA.Collection Set db = CurrentDb() dotaz = "SELECT ID_Osoba FROM Osoba" Set recSet = db.OpenRecordset(dotaz) Do While recSet.EOF = False osoby.Add (recSet("ID_Osoba")) recSet.MoveNext Loop recSet.Close Set recSet = Nothing
random = GetRandom(1, osoby.Count) GetRandomOsoba = osoby.Item(random) End Function 'Funkce, která ze služby dostane specializaci. Function GetSpecializaceFromSluzba(ByVal sluzba As Integer) As Integer 'Deklarace proměnných. Dim db As Database Dim recSet As DAO.Recordset Dim dotaz As String
Dim specializace As Integer Set db = CurrentDb() dotaz = "SELECT Specializace FROM Služba WHERE ID_Služba = " & sluzba Set recSet = db.OpenRecordset(dotaz) specializace = recSet("Specializace") recSet.Close Set recSet = Nothing GetSpecializaceFromSluzba = specializace End Function 'Funkce, která z rodného čísla dostane datum narození. Function GetDatumFromRC(ByVal rc As String) As Date 'Deklarace proměnných. Dim den As Integer Dim mesic As Integer Dim rok As Integer Dim akutalniRok As String Dim datum As Date aktualniRok = DatePart("yyyy", Now) aktualniRok = Mid(aktualniRok, 3, 2) rok = Mid(rc, 1, 2)
mesic = Mid(rc, 3, 2) den = Mid(rc, 5, 2)
If mesic > 50 Then mesic = mesic - 50 End If If rok < aktualniRok Then rok = 2000 + rok Else rok = 1900 + rok End If datum = rok & "-" & mesic & "-" & den GetDatumFromRC = datum End Function 'Funkce, která z rodného čísla zjistí pohlaví osoby. Function GetPohlaviFromRc(ByVal rc As String) As String 'Deklarace proměnných. Dim mesic As Integer mesic = Mid(rc, 3, 2) If mesic > 50 Then GetPohlaviFromRc = "Žena" Else GetPohlaviFromRc = "Muž" End If End Function 'Funkce, která z rodného čísla dostane věk osoby. Function GetVekFromRC(ByVal rc As String) As Integer 'Deklarace proměnných. Dim akutalniDatum As String Dim datum As Date Dim Vek As Integer aktualniDatum = Now datum = GetDatumFromRC(rc) Vek = DateDiff("yyyy", datum, aktualniDatum)
GetVekFromRC = Vek End Function
