Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky Katedra informačních technologií
Student Vedoucí bakalářské práce
: Tomáš Grombíř : RNDr. Helena Palovská, Ph.D.
Oponent bakalářské práce
: Ing. Petr Macák
TÉMA BAKALÁŘSKÉ PRÁCE
NÁVRH DATABÁZE PRO MŠ PASTELKA
ROK : 2010
Čestné prohlášení: Prohlašuji, že jsem tuto bakalářskou práci vypracoval samostatně. Veškeré použité podklady, ze kterých jsem čerpal informace, jsou uvedeny v seznamu použité literatury a citovány v textu podle normy ČSN ISO 690. V Praze dne 11.5. 2010
Podpis: .................................................
Poděkování: Chtěl bych poděkovat RNDr. Heleně Palovské, Ph.D. za poskytnuté rady a připomínky. Dále pak mojí rodině za podporu během studií.
Abstrakt Tato práce se zabývá analýzou a návrhem databáze pro mateřskou školku Pastelka. Dále pak instalací databázového systému MySQL včetně jeho nastavení. Ověření funkčnosti navržené databáze a databázového systému proběhlo prostřednictvím aplikace vytvořené v jazyce PHP.
Abstract This bachelor thesis deals with analysis, creation of database for a kindergarten and installation of the designed database into the database system MySQL. Functionality of the proposed database was verified through an application written in PHP.
OBSAH 1.
Úvod .............................................................................................................................. 7
2.
Analyzovaná instituce ..................................................................................................... 9
3.
2.1.
Základní informace ..............................................................................................9
2.2.
Analýza aktuálního stavu uchovávání dat .............................................................9
2.3.
Požadavky na databázi .......................................................................................10
2.4.
Požadavky na aplikaci ........................................................................................11
2.5.
Navrhnuté technologické řešení ........................................................................11
Návrh relační databáze ................................................................................................. 12 3.1.
Relační databáze ................................................................................................12
3.2.
Proces návrhu relační databáze .........................................................................13
3.3.
Tabulky ..............................................................................................................14
3.3.1.
Identifikace tabulek při návrhu databáze ....................................................15
3.3.2.
Normalizace ................................................................................................15
3.4.
Pole ...................................................................................................................16
3.4.1.
Obecné vlastnosti polí .................................................................................17
3.4.2.
Fyzické vlastnosti polí .................................................................................17
3.4.3.
Logické vlastnosti polí .................................................................................18
3.4.4.
Definice polí při návrhu databáze................................................................18
3.5.
Vztahy................................................................................................................19
3.5.1.
Typ vztahu ..................................................................................................20
3.5.2.
Způsob a stupeň účasti ...............................................................................21
3.5.3.
Vytváření vztahů při návrhu databáze .........................................................23
3.6.
Klíče ...................................................................................................................23
3.7.
Datové typy .......................................................................................................24
3.7.1.
Číselné datové typy.....................................................................................25
3.7.2.
Datové typy pro datum a čas ......................................................................25
3.7.3.
Řetězcové datové typy ................................................................................26
3.7.4.
Použité atributy datových typů ...................................................................27
3.8.
Integritní omezení ..............................................................................................27
3.8.1.
Integrita tabulek .........................................................................................28
3.8.2.
Integrita polí ...............................................................................................28
3.8.3.
Integrita vztahů...........................................................................................28
3.8.4.
Business pravidla ........................................................................................29
3.9.
Konceptuální modelování ..................................................................................30
3.9.1.
4.
Popis konceptuálního modelu .....................................................................30
3.10.
Fyzický model .................................................................................................32
3.11.
Vygenerovaný SQL skript ................................................................................35
3.11.1.
Popis skriptu............................................................................................35
3.11.2.
Úpravy skriptu .........................................................................................36
3.11.3.
Vytvoření pohledů ...................................................................................37
Instalace, nastavení a otestování databázového systému ............................................. 38 4.1.
MySQL ...............................................................................................................38
4.2.
Instalace MySQL ................................................................................................38
4.3.
Prvotní konfigurace MySQL ................................................................................39
4.4.
Nastavení MySQL ...............................................................................................39
4.5.
Vytvoření DB ......................................................................................................40
4.6.
Zabezpečení MySQL ...........................................................................................41
4.6.1.
Přístupová oprávnění ..................................................................................41
4.6.2.
Limity na konzumaci prostředků .................................................................42
4.7.
Zálohování databáze ..........................................................................................42
4.8.
Import dat .........................................................................................................42
4.9.
Aplikace ke správě databáze ..............................................................................43
4.9.1.
Webový server ............................................................................................43
4.9.2.
PHP .............................................................................................................43
4.9.3.
Komunikace s databází................................................................................44
4.10.
Otestování funkčnosti aplikace a databáze .....................................................44
5.
Závěr ............................................................................................................................ 45
6.
Citovaná literatura ....................................................................................................... 46
7.
Terminologický slovník ................................................................................................. 47
8.
Přílohy .......................................................................................................................... 49 8.1.
Upravený SQL skript ...........................................................................................49
8.2.
Nastavení výchozích identifikačních čísel ...........................................................53
8.3.
Pohledy ..............................................................................................................54
8.4.
Vytvoření uživatelů ............................................................................................54
8.5.
Přidělení práv.....................................................................................................54
8.6.
Vytvořená databázová struktura v mysql ...........................................................55
8.7.
Otestování aplikace a databáze ..........................................................................55
8.8.
Seznam tabulek..................................................................................................56
1. ÚVOD Ačkoliv se vznik relačních databází datuje již od roku 1970, kdy Edgar Frank Codd publikoval článek o možnostech relačního datového modelu[2], stále se v dnešní době najdou instituce, vývojáři a aplikace, které přínosu tohoto typu databáze nevyužívají nebo s ním nejsou dostatečně seznámeni. A právě s nástupem relačních databází mnohonásobně vzrostl v 80. a 90. letech počet uživatelů databází.[1] K podpoře tohoto boomu bylo nutné přijít s RDBMS určeným pro osobní počítače. Netrvalo dlouho a společnosti začaly vyvíjet databázové systémy, které zpřístupnily databáze širším masám, usnadnily implementaci správy dat a umožnily klient/server komunikaci. Z těchto důvodů, jsem si vybral bakalářskou práci na téma návrh databáze, neboť chci využít znalostí, které jsem načerpal studiem předmětů „Databáze“ a „Tvorba webových stránek a aplikací“ na Vysoké škole ekonomické a získat další informace a zkušenosti ohledně návrhu databáze, manipulace s databázovými systémy a vývoje aplikací. Doufám, že nabyté zkušenosti při psaní této bakalářské práce následně využiji ve firemní praxi. Cílem této bakalářské práce je projít celým procesem návrhu databáze pro mateřskou školku, který zahrnuje analýzu aktuálního stavu nakládání s daty, včetně provádění rozhovorů za účelem zjištění požadavků na funkčnost navrhované databáze. Dále na základě získaných informací vytvořím konceptuální a fyzický model databáze, který následně zkonzultuji se zaměstnanci školky a poupravím. Diagramy budu modelovat v programu PowerDesigner 12.5 od společnosti Sybase, se kterým jsem se seznámil během studia na vysoké škole. Tento program mi umožní dle navrženého modelu vygenerovat i SQL kódy, které urychlí následnou tvorbu databáze. Navrženou databázi otestuji v jednom z vybraných databázových systémů, jehož instalaci a nastavení zde též popíši. Posledním úkolem bude vytvořit aplikaci, která bude s navrženou databází komunikovat a umožní zaměstnancům školky základní manipulaci s daty bez znalosti jazyka SQL.
7
Zvolená metodika návrhu designu databáze je kombinací zkušeností získaných z předmětů na Vysoké škole ekonomické a knihy Michaela J. Hernandéze Návrh databází.[5] Hlavním přínosem této práce by mělo být vytvoření takového databázového řešení, které usnadní zaměstnancům mateřské školky manipulaci s daty nutnými pro fungování této instituce. Rozsah bakalářské práce mi nedovoluje vytvořit kompletní řešení, a proto se zaměřím pouze na podporu hlavních procesů v mateřské školce. Rozhodující pro mě budou zejména požadavky vedení MŠ Pastelky.
8
2. ANALYZOVANÁ INSTITUCE 2.1. Základní informace Jedná se o typickou mateřskou školku se sídlem v Praze, která se zabývá výchovou předškolních dětí. Školka má 4 třídy, kde každou vyučují 2 učitelky. Děti jsou rozděleny podle věku a vyspělosti. Školka poskytuje výuku cizích jazyků, pořádá školky v přírodě a poskytuje i několik zájmových kroužků, např. plavání, keramiku. Dalšími zaměstnanci ve školce jsou uklízečky a kuchařky, které se starají o provoz jídelny, skladu a o úklid. Potencionálními uživateli databáze jsou ředitelka a zástupkyně školky, které mají na starost komunikaci se státní správou a personální zajištění chodu školky. Na druhé straně ekonomka a hospodářka školky mají na starost finanční a účetní aspekty ve školce jako je uchovávání faktur, informace o hmotném majetku a o stavu zásob na skladě. Tito zaměstnanci jsou klíčoví při zjišťování aktuálního stavu nakládání s daty ve školce a následných požadovaných funkčnostech aplikace a databáze.
2.2. Analýza aktuálního stavu uchovávání dat Analýzu jsem provedl na základě několika návštěv dané školky, kde mi byly poskytnuty veškeré materiály, které jsem si vyžádal. Provedl jsem též oddělené rozhovory s několika klíčovými zaměstnanci mateřské školky. Pokládal jsem otázky, které jsem si dopředu připravil a dbal jsem na to, aby měly otevřený konec, abych svou otázkou nijak neovlivnil odpověď dotazovaného. Ptal jsem se na způsob jejich práce, na údaje, které ke své práci potřebují, operace, které nejčastěji provádějí a dále na věci, které jim při práci s daty nevyhovují. Hlavním úkolem bylo vypátrat všechny budoucí entity, dle kterých budu modelovat konceptuální model databáze v nástroji PowerDesigner. Též mi bylo umožněno pořídit snímky výstupů z používaných programů, což mi velmi usnadnilo identifikovat tabulky a pole (atributy), které v navrhované databázi nesmí chybět. Na obrázku je uveden jeden z mnoha pořízených snímků a zapůjčených papírových vzorků databáze.
9
Obr. 1 Snímek stávající DB
Ve školce panuje značná nejednotnost ve formátu ukládaných dat. Mnoho informací, zejména pak o rodičích je ukládáno pouze v listinné formě v kartotékách, což znemožňuje jejich efektivní vyhledávání. Tyto data se získávají vyplněním formuláře, který je pak pouze vložen do desek a uskladněn v archivu. Důležitější data jsou uchovávány na počítačích ředitelky, zástupkyně a ekonomky a to nejčastěji v tabulkových procesorech nebo již v datových souborech. Data jsou následně čteny z nejrůznějších softwarových produktů, mezi kterými není žádná vazba. Školka tak nakupuje a instaluje pro každý typ dat (pro každou entitu) jiný počítačový program, který umožňuje jejich čtení a manipulaci. Toto řešení ve výsledku zvyšuje pořizovací náklady a zároveň zvyšuje redundanci dat a nepodporuje žádané vazby mezi ukládanými daty.
2.3. Požadavky na databázi Hlavním cílem bude navržení jednotné databáze, která bude obsahovat tabulky vycházející ze stávajících databázových struktur. Dále by měla obsahovat nové tabulky pro uchovávání dat, které byly do této doby ukládány pouze v papírové podobě. Jedná se o informace o rodičích dětí, doplňující informace o zaměstnancích, komunikace se státní správou (podací deník) a probíhajících školeních zaměstnanců. Výsledná databáze by dále měla uchovávat komplexnější informace o všech osobách v rámci školky (zaměstnanci, děti, rodiče), údaje o majetku, fakturách a zásobách na skladě a v neposlední řadě doplňující údaje jako je seznam tříd a programy pro děti.
10
2.4. Požadavky na aplikaci Úkolem aplikace je jednoduchá administrace navržené databáze. Jde především o možnost zobrazování, vkládání a mazání dat v databázi bez znalosti jazyka SQL. Tato navržená aplikace poslouží i k otestování funkčnosti navržené databáze a nainstalovaného databázového systému. Uživatelské rozhraní aplikace by mělo být intuitivní a nenáročné. Jednoduchost ovládání a přehlednost je podmínkou.
2.5. Navrhnuté technologické řešení Na základě provedené analýzy a zjištěných požadavků jsem navrhl technologické řešení, známé pod zkratkou AMP. Jde o kombinaci serveru Apache, databázového systému MySQL a použití jazyka PHP pro aplikaci, tedy PHP skripty.
Obr. 2 Technologické řešení
Rozhodl jsem se zvolit relační typ databáze. Jedná se o momentálně nejpoužívanější typ, který odstraňuje nedokonalosti předešlé síťové a hierarchické struktury. Pro MŠ ho považuji za nejvhodnější. Následuje samotný proces návrhu.
11
3. NÁVRH RELAČNÍ DATABÁZE V této kapitole jsou rozebrány hlavní prvky relační databáze, jako jsou tabulky, pole, vazby a klíče, jejichž znalost je nezbytná pro konzistentní návrh databáze. Na konci každé podkapitoly je uveden konkrétní postup, který byl použit v rámci návrhu databáze pro MŠ Pastelka. Aby se předešlo budoucím problémům v databázi, je u každého z pojmů uveden také seznam doporučených vlastností, jež by jednotlivé pojmy měly splňovat. Nejprve uvedu stručnou charakteristiku relační databáze, následně rozeberu postup při jejím návrhu.
3.1. Relační databáze Relační databáze je implementace modelů reálného světa, vytvořeného podle pravidel relačního modelu. Relační model navrhnul a jeho pravidla publikoval Dr. E. F. Codd (pracovník firmy IBM) v r. 1970 v článku: "A relational model of data for large shared databanks."1 Vychází z matematické teorie množin a predikátové logiky. Základním kamenem relačního modelu je databázová relace (množina) obsahující data. Hlavním prvkem relační databáze jsou pak databázové tabulky. Jejich sloupce se nazývají pole (atributy) a řádky jsou záznamy tabulky. Tabulka pak realizuje množinu kartézského součinu všech dat ve všech sloupcích, tedy relaci. V článku o relačním modelu byly mimo jiné specifikovány tyto ideje, jež uvádí nejvýznamnější rozdíl oproti předchozím datovým modelům[2][3]: •
Relační model odděluje data, která jsou chápana jako relace od jejich implementace.
•
Pro práci s daty je k dispozici relační kalkul a algebra. Neboli matematické aparáty k popisu sémantiky relačních jazyků.
•
Pro omezení redundance dat jsou k dispozici pojmy pro normalizaci relací. 2
•
Všechny hodnoty v databázi jsou skalární. 3
1
Dostupný z WWW:
Podkapitola normalizace. 3 Obsahují jednu jedinou hodnotu, kterou může být číslo, řetězec. 2
12
3.2. Proces návrhu relační databáze Následující Business Process model popisuje můj postup při návrhu databáze. Namodelován byl v programu PowerDesigner. Při modelování procesu jsem se musel řídit jistou mírou abstrakce, neboť ve skutečnosti se jedná o činnost skládající se z mnoha podprocesů. Ve stručnosti by se dal rozložit na 3 části. Analýzu stávající databáze a vytvoření požadavků na novou. Identifikace všech entit, včetně polí a způsobu propojení. Následné modelování v CASE nástroji, volba datových typů a nastavení integrit.
Obr. 3 Proces návrhu relační databáze
13
3.3. Tabulky Tabulky jsou základní strukturou v databázi, které reprezentují entity reálného světa. Entita reprezentovaná tabulkou může být buď objekt, nebo událost. V případě entity typu objekt se jedná o něco hmatatelného. Příkladem mohou být údaje o osobě či věci, jejichž vlastnosti se dají uložit jako data, která pak následně slouží jako zdroj informací. Příkladem entit, které popisují nějakou událost, která nastala v určitém čase, jsou například výsledky analýz, testů a nejrůznější průzkumy. Nehledě na výše zmíněné druhy entit, které tabulky reprezentují, rozlišujeme 4 typy tabulek na základě povahy ukládaných dat[5]: •
Datová tabulka, která ukládá dynamická data určená k získávání informací a reprezentuje entity důležité pro organizaci. Jedná se o nejčastější typ tabulky v prostředí relačních databází.
•
Vazební tabulka, která slouží k vytvoření vazby mezi tabulkami spojenými relací M:N. Obsahuje kopie primárních klíčů z tabulek, které propojuje.
•
Podmnožinová tabulka reprezentuje podřízenou entitu některé z datových tabulek. Obsahuje pole, která jsou podřízena vlastní entitě, a zároveň obsahuje pole z datové tabulky, které slouží k vytvoření vazby.
•
Validační tabulka často označovaná jako číselník uchovává statická data, která slouží k implementaci integrity dat. Reprezentuje entity, jako například jména měst a jejich poštovní směrovací čísla.
Při prvotním návrhu databáze byla většina tabulek datových, neboť ostatní typy vznikaly až na základě nastavování integrity nebo při řešení problémů s redundantními daty 4. O tvorbě vazebních tabulek se zmiňuji v kapitole věnující se vazbám a k nahlédnutí jsou ve fyzickém modelu databáze pro MŠ.
4
Redundantní data: Hodnota, která je opakovaná v poli jako výsledek spoluúčasti pole při spojování dvou tabulek, nebo jako výsledek anomálie některého pole či tabulky. [5]
14
3.3.1. Identifikace tabulek při návrhu databáze Proces nalezení tabulek začíná identifikováním všech entit v organizaci. Entitou bývají z pravidla podstatná jména, která se v prostředí dané firmy nejčastěji vyskytují nebo vyplynou z prováděných rozhovorů. V mém příkladě mateřské školky jimi jsou děti, učitelé, rodiče, třídy. Pokud navrhujeme databázi, jak se říká, na zelené louce, je potřeba identifikovat co největší počet podstatných jmen (budoucích entit), a to na základě vlastních domněnek o organizaci a z informací poskytnutých vedením. Já jsem již při návrhu vycházel z jednodušších databázových struktur, takže identifikace nebyla problém. Všechny stávající entity je potřeba překontrolovat, neboť pokud jen slepě použijeme starou databázi jako zdroj pro tu novou, nejspíše přeneseme všechny chyby a omezení staré databáze i do té nové. Výsledný seznam entit, na základě kterých se vytvoří tabulky je potřeba zkonzultovat s vedením a také s budoucími uživateli databáze. Důležitým nástrojem při návrhu a konzultaci tabulek s klientem je vytvoření seznamu takových tabulek 5, včetně jejich typu a stručného popisu. Můj seznam je ve formátu jméno tabulky, typ tabulky a stručný popis tabulky. Navrhované tabulky by měly splňovat několik vlastností[5]: •
Jedna tabulka reprezentuje jednu entitu, kterou může být věc nebo událost
•
Obsahuje primární klíč, který identifikuje všechny hodnoty tabulky.
•
Neobsahuje vícehodnotová, vypočítaná a vícesložková pole.
•
Obsahuje nejmenší možné množství redundantních dat. 6
3.3.2. Normalizace Navrženou strukturu tabulek a všech jejich polí lze zkontrolovat i na základě procesu normalizace, kdy se navržená tabulka podrobí tzv. normálním formám. Obecně platí, že čím je tabulka ve vyšší normální formě, tím kvalitněji je navržena.
5
Viz. Přílohy V relační databázi se v několika případech redundaci nevyhneme, proto je zmíněno nejmenší možné množství.
6
15
Většina navržených tabulek splňuje 3. normální formu, pro kterou je podmínkou „nezávislost neklíčových dat mezi sebou.“ Tím je zajištěna postačující úroveň konzistence a zároveň nedochází k rozkladu tabulek do příliš složitých databázových struktur, které považuji za nevhodné pro databázi MŠ. Důvodem byla i stávající databáze, ve které se tabulky nacházely mnohdy v nižší normální formě, a přechod k příliš dekomponovaným tabulkám by jistě zaměstnancům školky nevyhovoval. Obr. 4 Nedodržení NF
Často je výhodnější použít nižší normální formu z důvodu menší zátěže pro server a přehlednější struktury celé databáze. Na obrázku tabulky FAKTURA je prezentováno nerespektování 3NF, kdy by údaje ohledně dodavatele a symbolů měly být v nové tabulce. To jsem neudělal z toho důvodu, že jsou ve školce již zvyklí na tuto strukturu tabulky FAKTURA a nemají potřebu evidovat další informace o dodavatelích.
3.4. Pole Jedná se o nejmenší strukturu v databázi, která se též označuje jako atribut. Reprezentuje určitou vlastnost entity, do které patří. Jsou to struktury, které slouží k uchovávání dat. Pokud je databáze správně navržena, tak pole ukládá pouze jednu hodnotu a název pole přesně identifikuje povahu této hodnoty. Pokud je název pole například jméno zaměstnance, je hned jasná povaha hodnoty tohoto pole a uživateli databáze nedělá problém zadat správnou hodnotu. To vše usnadňuje následné vyhledávání, třídění a veškeré ostatní úkony prováděné nad daty v databázi. Veškeré vlastnosti polí lze zařadit do 3 kategorií. Jedná se o obecné, logické a fyzické vlastnosti pole. Každé z těchto kriterií hraje svou roli v jiné fázi procesu návrhu databáze.
16
3.4.1. Obecné vlastnosti polí Mezi obecné vlastnosti patří název daného pole, ale i například jeho popis, který jasně definuje, co se do pole ukládá za hodnoty a jaké nabývají povahy. Obzvláště popis pole je velmi důležitý při komunikaci se zákazníkem, který by z popisu měl ihned pochopit, jaká data jsou zde ukládána. Já jsem v případě vytváření názvů polí vycházel co nejvíc ze stávající databáze a snažil se pro pojmenování nepoužít více jak dvě slova. Zde jsou uvedena doporučení tykající se pojmenovávání polí (atributů) [5]: •
Jedinečná jména srozumitelná všem v organizaci.
•
Jasně charakterizuje obsah ukládaných dat.
•
Co nejkratší název, ale vyvarovat se zkratkám a akronymům.
•
Používat jednotné číslo.
3.4.2. Fyzické vlastnosti polí Tato kategorie obsahuje strukturální vlastnosti pole. Nelze přesně určit, které aspekty pole spadají pod fyzické vlastnosti, neboť se to liší v implementacích jednotlivých databázových systémů. Správný návrh těchto vlastností při procesu návrhu databáze je nesmírně důležitý vzhledem k výsledné konzistenci dat. Neboť sem spadají vlastnosti, jako jsou: •
Datový typ pole
•
Délka pole
•
Formát, povolené znaky
O datových typech se zmiňuji v samotné kapitole 3.7.1. Další důležitou vlastností pole je délka. Ta udává celkový počet povolených znaků v rámci pole, čímž zabraňuje, aby databáze při nevhodné manipulaci zabírala enormní místo na discích a klesal výkon databáze. Délka pole je často závislá na zvoleném datovém typu, a tak zvolený databázový systém nemusí uživateli tuto volbu nabízet k dispozici.
17
Mezi vlastnosti dále patří povolené znaky a formát hodnoty pole. Jejich správné nadefinování zvyšuje stupeň integrity na úrovni pole, o které se zmiňuji dále v práci. Dále zabraňuje uživatelům databáze, aby ukládali nesmyslné a nevhodné hodnoty, které by celkovou integritu databáze snižovaly. Všechny fyzické vlastnosti polí jdou dnes jednoduše navrhnout v modelovacích programech, ať už se jedná o datový typ, limitní délku nebo povolené znaky. Dané vlastnosti jsou k nahlédnutí v podkapitolách věnovaným konceptuálnímu modelu a datovým typům.
3.4.3. Logické vlastnosti polí Kategorie se zabývá převážně vlastnostmi hodnot polí. Zda je ukládaná hodnota unikátní, nebo se dokonce jedná o primární klíč tabulky a je tedy unikátnost přímo podmínkou. Nebo jestli na druhou stranu může hodnota pole nabývat prázdných hodnot a nemusí být tedy vůbec vyplněna, případně je zobrazená hodnota null. Stejně tak sem patří povolený rozsah hodnot a tím nemám na mysli počet znaků. Rozsahem se zamýšlí předem definované hodnoty, kterých dané pole podle návrhu musí nabývat. Pokud například při návrhu databáze nastavím rozsah hodnot na vytvořenou množinu (modrá, červená, zelená), tak dané pole může nabývat pouze jednu z těchto tří hodnot a pokud se uživatel pokusí vložit jinou hodnotu, tak mu databázový systém nevyhoví.
3.4.4. Definice polí při návrhu databáze Při návrhu polí bychom se měli v první řadě vyvarovat vytvoření jakéhokoliv z následujících polí, neboť ve velké míře ovlivňují výslednou celkovou integritu databáze. Jedná se o vícesložkové, vícehodnotové a vypočítané pole. Příkladem vícesložkového pole může být celý název zaměstnance. Jednalo by se o pole, které by jako hodnotu ukládalo jednak jméno, ale i příjmení namísto použití dvou polí pro jednotlivé hodnoty. Vícehodnotové pole podobně jako předešlé obsahuje několik hodnot v rámci jednoho pole, ale nemusí se jednat o hodnoty, které spolu nějak souvisí, jako tomu je u jména. Příkladem může být pole, které obsahuje výčet všech cizích jazyků, kterými se zaměstnanec dorozumí.
18
Poslednímu ze zmíněných polí bychom se při návrhu měli vyvarovat z toho důvodu, že se k výpočtu jeho hodnot používá jazyka SQL a je tedy zbytečné provádět výpočty manuálně a taková pole vůbec definovat. Vícesložkových polí se zbavíme tak, že pro jednotlivé složky daného pole vytvoříme nové pole. V mém případě je adresa všech osob ve školce rozdělena na ulici, město a směrovací číslo. Správně bych měl od názvu ulice separovat i číslo bytu, ale to jsem považoval již za zbytečné. Vícehodnotových polí se ve stávajících databázích zbavuje obtížněji. Jednou z možností je vytvoření úplně nové tabulky, se kterou je původní tabulka ve vztahu. Taková tabulka se nazývá vazební a je výsledkem vztahu M:N mezi dvěma tabulkami. Při samotném návrhu databáze se vytvoření takových polí vyhneme, pokud se budeme řídit doporučenými vlastnostmi ideálního pole podle [5]: •
Obsahuje jen jednu hodnotu
•
Není možné jej dekomponovat do menších částí
•
Je unikátní v rámci celé databázové struktury
•
Reprezentuje charakter tabulky
•
Neobsahuje vypočítaná data
Ačkoliv jsou v tomto případě veškeré vlastnosti polí nastavovány v modelovacím programu, který pak následně vygeneruje SQL skript, stejně je nezbytné si promyslet všechny pole ještě před samotným modelováním a zkonzultovat je s klientem. V případě školky jsem veškeré mnou navržené tabulky, pole a vazby dopředu konzultoval s vedením školky, než jsem se pustil do finálního návrhu výsledné databáze, kde už jsem pouze dolaďoval veškerá integritní omezení.
3.5. Vztahy Právě vztahy jsou tou jedinečnou vlastností relační databáze, které ji odlišují od předešlých typů databází. Umožňují uživateli vytvářet pohledy na několik tabulek najednou a jsou též nezbytné pro zajištění integrity dat, protože napomáhají redukovat nadbytečná data a eliminovat data duplicitní.[5]
19
Ve stručnosti se pojmem vztah myslí propojení záznamů mezi dvěma tabulkami. Zřizuje se prostřednictvím množiny primárních a cizích klíčů, nebo v ojedinělých případech za použití podmnožinové vazební tabulky. Každý vztah je možno definovat na základě 3 vlastností. Typem vztahu, který existuje mezi tabulkami. Dále pak způsobem a stupněm účasti tabulky ve vztahu.
3.5.1. Typ vztahu Definujeme 4 typy vztahů (často označované jako kardinality) mezi tabulkami: •
Vztah 1:1
•
Vztah 1:N
•
Vztah M:N
•
Samoreferenční vztah [5]
Vztahem 1:1 definujeme takový typ vztahu, ve kterém je záznam v první tabulce ve vztahu pouze s jedním záznamem v druhé tabulce a naopak. Nejčastěji se jedná o tabulky, kde jedna funguje jako rodič a druhá jako potomek.
Obr. 5 Vztah 1:1
Vztah 1:N existuje mezi dvěma tabulkami v případě, že záznam z jedné tabulky odkazuje na více záznamů v druhé tabulce a všechny tyto záznamy odkazují pouze na jeden záznam v první tabulce. Jedná se o nejčastější typ vztahu v relačních databázích. Klíčovým je i z pohledu datové integrity, protože napomáhá minimalizovat nadbytečná data. Výstupem takového vztahu je vytvoření cizího klíče v tabulce s N prvky odkazujících na primární klíč v protější tabulce.
Obr. 6 Vztah 1:N
20
Vztah M:N je nejsložitější, neboť záznamy z obou tabulek jsou ve vztahu s libovolným počtem záznamů z protější tabulky. Tento typ vztahu se následně řeší za pomoci vazební tabulky, která tento vztah rozdělí na dva vztahy typu 1:N. Vazební tabulka poté obsahuje kopie primárních klíčů z obou tabulek a dohromady tvoří složený primární klíč. Zvlášť pak fungují jako cizí klíče odkazující na primární klíče v tabulkách, mezi kterými byla vytvořena vazební tabulka.
Obr. 7 Původní vztah M:N
Obr. 8 Rozložení vztahu M:N
Samoreferenční typ vztahu nespojuje dvě tabulky, neboť spojuje záznamy z téže tabulky. Tabulka je sama se sebou svázána tímto typem vztahu, pokud je záznam ve vztahu (1:1, 1:N, M:N) s jiným záznamem téže tabulky.
3.5.2. Způsob a stupeň účasti Účast tabulky ve vztahu není povinná, proto definujeme dva typy účasti, též nazývány jako parcialita vztahu: •
Povinná účast
•
Volitelná účast
Tabulka má povinnou účast ve vztahu, pokud před vložením záznamu musí být v druhé tabulce alespoň jeden záznam, se kterým vytvoří vazbu.
21
V rámci modelovacího programu PowerDesigner je tato vlastnost řešena pomocí upravení základních 3 typů vztahů. Výsledkem jsou pak další kombinace vztahů, které definují způsob účasti jako volitelný, pokud na straně vybraného záznamu začíná interval, určující počet odkazovaných záznamů, nulou. Lze si tedy u každého z dvou záznamů vytvářejících vazbu vybrat mezi kardinalitami (0,1), (1,1), (0,N) a (1,N). Výsledná vazba je pak vypočítána na základě kardinalit obou záznamů.
Obr. 9 Nastavování způsobu účasti v PD
Stupeň účasti definuje minimální a maximální počet záznamů tabulky, které mohou být ve vztahu se záznamem z tabulky druhé. Stupně účasti v rámci návrhu databáze jsou spíše doplňující. Při špatném nastavení by znemožňovali například přidání dalšího záznamu, jež by byl ve vztahu, neboť by už byl překročen maximální povolený počet odkazujících záznamů na daný záznam z druhé tabulky. Pro databázi mateřské školky jsem nedefinoval u žádných vztahů stupeň účasti, ačkoliv každou třídu smějí vyučovat nanejvýš dvě učitelky.
22
3.5.3. Vytváření vztahů při návrhu databáze Pokud jsme se při návrhu databáze dostali do fáze, kdy vytváříme vztahy mezi tabulkami, je důležité si dát pozor na několik věcí, neboť špatně navržené vztahy v modelech následně povedou k větším problémům s integritou ve vytvořené databázi. Při určování typu vztahu je důležité si položit správně formulované otázky, díky kterým lze typ zjistit. Pokud mám například tabulku Děti a tabulku Třídy, můžou otázky znít následovně. „Do kolika tříd může chodit jedno dítě?“ „Kolik dětí může navštěvovat jednu třídu?“ Z takto položených otázek vyplívá, že typ vztahu bude N:1, neboť dítě může chodit pouze do jedné třídy, ale do třídy dochází hned několik dětí. Poté, co máme určen typ vztahu, je důležité určit ještě způsob účasti tabulky Děti. Pokud každé dítě evidované v databázi musí chodit do nějaké třídy, pak se jedná o typ vztahu (1,N):1 a je tedy způsob účasti povinný, ale pokud v databázi potřebuji evidovat i děti, co už do žádné třídy nechodí, poté musím způsob účasti označit jako volitelný a vztah nastavit jako (0,N):1. Stejně tak je potřeba určit povinnost účasti záznamu ve vztahu pro tabulku třídy, kde je na výběr mezi (0,1) a (1,1).
3.6. Klíče Klíče v relačních databázích vykonávají hned několik funkcí. Zabezpečují, že každý záznam v tabulce je přesně identifikován. Napomáhají stanovit a dodržovat různé druhy integrit a hlavně slouží jako prostředek pro vytváření vztahů mezi tabulkami. Rozlišujeme dva základní typy klíče: •
Primární klíč (PK) je pole, nebo soustava polí, jejichž hodnoty tvoří jednoznačnou identifikaci řádků relace.
•
Cizí klíč (FK), jež se odkazuje na primární klíč v druhé tabulce a vytváří tak spojení (vazbu) mezi tabulkami.
Při návrhu pole, které má zastat funkci primárního klíče musíme dodržovat několik pravidel, které zabezpečí, že primární klíč bude identifikovat hodnotu všech ostatních polí a zabezpečovat integritu na úrovni tabulky. Doporučené vlastnosti primárního klíče podle[5]:
23
•
Nesmí to být vícesložkové pole.
•
Musí obsahovat jedinečné hodnoty.
•
Hodnota musí být v rámci celé tabulky jedinečná.
•
Skládá se z nejmenšího možného počtu polí.
•
Nesmí obsahovat hodnoty null.
•
Hodnota PK musí jednoznačně identifikovat hodnoty všech polí dané tabulky.
Poslední zmíněná vlastnost nám též umožňuje identifikovat nadbytečná pole v tabulce, na která přijdeme, pokud si pro každé další pole v tabulce položíme otázku, zda je toto pole jednoznačně identifikováno primárním klíčem. Pokud tomu tak není, je potřeba se zamyslet, zda je vhodné mít takové pole v tabulce a není-li lepší ho vyřadit, či přemístit do jiné tabulky.[5] Při návrhu databáze jsem u některých tabulek nebyl schopen vybrat vhodné pole pro primární klíč, a proto jsem vytvořil umělé pole (identifikační číslo), které tyto vlastnosti splní. Ke vkládání hodnot do těchto polí se nejčastěji používají sekvence 7 (v případě DBMS Oracle) nebo další funkce, které automaticky vkládají číselné hodnoty. Na druhé straně cizí klíče (FK) se při konceptuálním návrhu databáze nenavrhují. Ty vznikají až na základě vytvořených vazeb mezi tabulkami, kde primární klíč jedné tabulky odkazuje na cizí klíč tabulky druhé a tím zajišťuje jejich vazbu.
3.7. Datové typy Ačkoliv je datový typ fyzickou vlastností databázového pole, o kterém jsem se již zmínil, přesto jsem pro datové typy vyhradil vlastní podkapitolu, neboť jejich správné zvolení považuji za velmi důležitý aspekt v rámci návrhu designu databáze, protože zajišťuje konzistentnost definice polí v rámci celé databáze. Z důvodu odlišné implementace datových typů v prostředí jednotlivých databázových systému, budu nadále zmiňovat datové typy používané v databázovém systému MySQL, který jsem si vybral pro použití na příkladu MŠ Pastelka, a o kterém se zmiňuji v kapitole 4. 7
Sekvence je objektem aplikace sloužícím ke generování posloupnosti celočíselných hodnot (v rozsahu 64 bitů), zejména unikátních klíčů. [7]
24
Datový typ popisuje povahu dat, které pole ukládá. Určuje tedy, jak bude sloupec v tabulce interpretovat svůj obsah. V MySQL definujeme tři skupiny datových typů podobně jako u většiny databázových systémů.[4]
3.7.1. Číselné datové typy MySQL umí pracovat jak s celými čísly, tak i s čísly, které mají desetinnou čárku. Proto je důležité si při návrhu databáze uvědomit, zda v daném sloupci budeme pracovat s celými čísly, nebo budeme požadovat čísla desetinná. Celočíselné datové typy se často používají pro ukládání identifikačních čísel, které tvoří primární klíče tabulek, a jsou přiřazovány sekvenčně nebo v případě MySQL funkcí AUTO_INCREMENT[4]. Název
Použití
Rozsah čísel
Místo v paměti
Malá celá čísla.
−32768 až 32767 nebo 0 až 65535 (16 bitů)
2 bajty
Středně velká celá čísla.
−2147483648 až 2147483647 nebo 0 až 4294967295 (32 bitů)
4 bajty
BIGINT
Velká celá čísla.
-9223372036854775808 až 9223372036854775807 (64 bitů)
8 bajtů
FLOAT
Plovoucí desetinná čárka.
±1,175494351E−38; ±3,402823466E+38
4 bajty
±2,2250738585072014E-308; ±1,17976931348623157E+308
8 bajtů
±2,2250738585072014E-308; ±1,17976931348623157E+308
10 bajtů + 2 (čárka, znaménko)
SMALLINT INT
Velká čísla v pohyblivé řádové čárce; větší přesnost než u FLOAT. Velká čísla v pohyblivé DECIMAL řádové čárce ukládané jako řetězec Tab. 1 Číselné datové typy [4][5] DOUBLE
3.7.2. Datové typy pro datum a čas Slouží k uchovávání dat, časů nebo jejich kombinací. Existuje i speciální typ časové značky, který si do databáze vždy ukládá aktuální datum a čas, kdy byl daný řádek aktualizován. K nahlédnutí je v následující tabulce pod názvem TIMESTAMP.[4] Já jsem pro většinu polí, které mají uchovávat časový údaj, použil datový typ DATE, neboť nebylo zapotřebí uchovávat i konkrétní čas. Pouze v případě kroužků je použit typ TIME, protože je zapotřebí evidovat konkrétní rozvrh a čas, kdy kroužek začíná a končí. Časová značka je použita v tabulce s materiálem na skladě, kdy je potřeba evidovat, ke kterému datu je množství potravin v databázi aktuální.
25
Název
Použití
Rozsah
Nulová hodnota
Místo v paměti
DATE
Datum ve formátu 'CCYY-MM-DD'
1000-01-01 až 999912-31
0000-00-00
3 bajty
TIME
Čas ve formátu 'hh:mm:ss'
-838:59.59 až 838:59:59
00:00:00
3 bajty
1000-01-01 00:00:00 až 9999-12-31 23:59:59
0000-00-00 00:00:00
8 bajtů
19700101000000 až do roku 2037
00000000000000
4 bajty
Datum a čas ve formátu 'CCYYMM-DD hh:mm:ss' Časová TIMESTAMP značka ve formátu DATETIME. Tab. 2 Datové typy pro datum a čas [4][5] DATETIME
3.7.3. Řetězcové datové typy Tato skupina datových typů se používá převážně pro ukládání textu, ale je možné do nich ukládat i čísla. Všechny mají stanovenou maximální délku řetězce a při návrhu databáze je důležité si tuto vlastnost pohlídat, neboť by následně mohly nastat problémy s konzistencí.[4] Název
CHAR VARCHAR
TEXT
BLOB
Použití
Řetězec pevně dané délky. Řetězec měnící se délky. Při ukládání do DB se odstraňují koncové mezery. Textový typ, který se na rozdíl od CHAR a VARCHAR ukládá do jednotlivých souborů. Binárně citlivá obdoba TEXT.
Povolená délka
Místo v paměti
0 až 255 znaků
Co znak to 1 bajt
0 až 255 znaků
Co znak to 1 bajt + 1 bajt pro zaznamenání délky
0 až 65535 bajtů
Co znak to 1 bajt + 2 bajty pro zaznamenání délky
0 až 65535 bajtů
Co znak to 1 bajt + 2 bajty pro zaznamenání délky
Výčet hodnot; hodnoty ze sloupce mohou mít přiřazeny právě jednu hodnotu ze seznamu Tab. 3 Řetězcové datové typy [4][5]
1 bajt pro výčty s členy 1 až 255; 2 bajty pro výčty s členy 256 až 65535
ENUM
Obzvláště poslední typ v tabulce velmi dobře posloužil při návrhu databáze, neboť mi pomohl vyřešit problém, kdy DBMS MySQL ignoruje funkci CHECK v definici tabulky. Pro pole, které mají mít výčet specifických hodnot, se jednoduše tyto hodnoty vloží do množiny přípustných.
26
3.7.4. Použité atributy datových typů U každého z datových typů lze nastavit atributy, které jim přidávají další vlastnosti. Konkrétní atribut nelze použít pro všechny datové typy. Proto uvádím pouze atributy, kterých jsem využil pro navrženou databázi. AUTO_INCREMENT – Díky tomuto atributu, který se přiřazuje k číselným datovým typům, lze zautomatizovat proces přidělování identifikačních čísel. Ke každému novému záznamu je automaticky přidělena do určitého sloupce následující celočíselná hodnota v pořadí. V navrhované databázi jsem tento atribut použil u všech identifikačních čísel. Při vkládání hodnoty null či 0 do pole s tímto atributem se automaticky přiřadí počáteční hodnota nebo hodnota o jedno vyšší než ta u minulého záznamu. NOT NULL – Tímto atributem se zabraňuje do konkrétního pole vložení hodnoty null, případně nevyplnění tohoto pole. To nutí uživatele vkládat pouze relevantní a úplné údaje. PRIMARY KEY – Jak již napovídá název, jedná se o atribut, který definuje primární klíč tabulky. Tím se zaručuje jedinečnost hodnot. Polím s atributem PRIMARY KEY se často přiřazuje i atribut AUTO_INCREMENT a vzniká tak jednoznačný identifikátor.
3.8. Integritní omezení V terminologii relačních databází se rozdělují integritní omezení na entitní, doménové a referenční. Já však v této práci popisuji omezení z pohledu vycházejícího z názvu předešlých kapitol a místo těchto názvů tedy uvádím integritu tabulek, polí a vztahů, což je vlastně to samé. V následujících podkapitolách zmiňuji doplňující omezení a nastavení databáze, která nejsou patrná z konceptuálního a fyzického modelu. U každé z integrit je uveden pouze jeden příklad vycházející z návrhu databáze pro MŠ, ačkoliv je v navrhované databázi integritních omezení více.
27
3.8.1. Integrita tabulek V první řadě se kontrola integrity tabulek neboli entitní omezení kontroluje na základě vlastností ideální tabulky, které zmiňuji v kapitole, která se zabývala specifikací tabulek. Důležité je prověřit i primární klíč tabulky, zda též splňuje doporučené vlastnosti a identifikuje všechny hodnoty polí v dané tabulce. Jinak řečeno, každý řádek v tabulce musí obsahovat primární klíč, jenž bude nabývat unikátních hodnot a bude tak rozlišovat jednotlivé záznamy (řádky).[3]
3.8.2. Integrita polí Rovněž nazývaná jako doménová integrita se zabývá ideálními vlastnostmi polí. Integritu polí lze též zkontrolovat na základě procesu normalizace, kdy tabulku podrobíme normálním formám, jež jsou uvedeny v podkapitole normalizace. Důležité je pro jednotlivá pole při návrhu nastavit vhodný datový typ, který umožňuje ukládat data v požadovaném formátu. Výčet možných datových typů je k nahlédnutí v kapitole o datových typech. Použité datové typy pro MŠ jsou zobrazeny v konceptuálním modelu. Ke kontrole doménové integrity v rámci SQL kódů se používá funkce CHECK při tvorbě tabulky, která zajišťuje, že vkládané hodnoty do polí jsou správného formátu, či případně spadají do povolené množiny hodnot. Avšak v rámci databázového systému MySQL není funkce CHECK podporována, a tak nezbývá nic jiného než použít při složitější definici doménové integrity Triggery 8 nebo vhodně nastavit datový typ pole. [10]
3.8.3. Integrita vztahů K aplikování referenčních omezení dochází v případě, kdy manipulujeme se záznamy, které jsou ve vztahu se záznamy s jiné tabulky. K bezchybnému nastavení integrity na úrovni vztahů je tedy zapotřebí správně nastavit typy vztahů a způsob jejich účasti. Momentálně jsou nejpoužívanější 3 druhy omezení, které se používají při manipulaci se záznamy, které odkazují na záznamy z jiné tabulky.
8
TRIGGER - Podmínka definující v databázi činnosti, které se mají provést v případě definované události nad databázovou tabulkou. Definovanou událostí může být například vložení nebo smazání dat.[4]
28
•
Restriktivní omezení – Pokud při pokusu o smazání záznamu z rodičovské tabulky dojde k zjištění, že odkazuje na záznam v tabulce potomka, tak RDBMS odmítne provést tuto akci. Nejprve je nutné smazat záznam z tabulky potomka a poté je možno smazat záznam z rodičovské tabulky.
•
Kaskádové omezení – V tomto případě proběhne při smazání záznamu v rodičovské tabulce zároveň k výmazu záznamu z tabulky potomka. Smazání záznamu v tabulce potomka dojde automaticky.
•
Set Null – V případě, že dojde ke smazání záznamu z rodičovské tabulky, nastaví se hodnota cizího klíče v podřízené tabulce na null. To lze pouze v případě, že tento cizí klíč může nabývat prázdné hodnoty.
Veškerou integritu vztahů jsem nastavoval v rámci fyzického modelu databáze v programu PowerDesigner. Je důležité si integritu vztahů rozmyslet dopředu, neboť při špatném nastavení by mohlo následně dojít k fatálním následkům. Například při nastavení všech vztahů na CASCADE (kaskádové omezení) by po smazání některého záznamu mohlo dojít k výmazu mnoha jiných důležitých záznamů, což by vedlo k neúmyslné ztrátě dat.
3.8.4. Business pravidla Jedná se o formulace, které určují dodatečná omezení na určité součásti databáze. Tyto pravidla se vytvářejí na základě způsobu, jakým daná organizace pracuje. Rozlišují se 2 typy takových pravidel. První z nich jsou orientovaná čistě na databázi, druhá jsou orientovaná aplikačně.[5] Databázově orientovaná pravidla určují omezení, které lze zavést v rámci návrhu databáze. Příkladem takového business pravidla může být nastavení rozsahu hodnot některého pole na určitou množinu povolených. Například do pole s funkcí zaměstnance lze zadat hodnoty z množiny (kuchařka, uklízečka, učitelka, ředitelka). Aplikačně orientovaná pravidla určují omezení, která nejsou nastavena v rámci procesu návrhu databáze, ale nastavují se až při návrhu databázové aplikace, která je též předmětem této bakalářské práce.
29
3.9. Konceptuální modelování Poté, co jsem zkonzultoval seznam tabulek a všech jeho polí s vedením, je na řadě modelování samotné databáze a tedy možnost využití předešlých teoretických poznatků ohledně relačních databází. K samotnému modelování je vhodné použít některý z dostupných CASE nástrojů. Zde je použit a popsán modelovací nástroj PowerDesigner. Konceptuální model je databázový model, který umožňuje popsat objekty v databázi a vztahy mezi nimi. Výhodou konceptuálního modelu je jeho implementační nezávislost, tudíž by se v této fázi návrhu databáze nemělo přihlížet na budoucí zvolený databázový systém, případně aplikace.[5] V průběhu konceptuálního modelování se vybírají vhodné datové typy pro jednotlivá pole, vytváří se vazby mezi tabulkami včetně způsobu účasti a nastavují se primární klíče jednotlivých tabulek. Cizí klíče, jak již bylo zmíněno v kapitole jim určené, se nemodelují, neboť vznikají na základě nadefinovaných vazeb. V konceptuálním modelu se též uplatňují entitní a doménová integritní omezení.
3.9.1. Popis konceptuálního modelu V záhlaví každé tabulky je její název. První sloupec obsahuje názvy všech polí. V druhém sloupci (pouze první řádek) je hodnotou definován primární klíč tabulky. Následují zkratky používaných datových typů pro jednotlivé atributy. Značka <M> v posledním sloupci označuje atribut jako povinný, nemůže tedy nabývat hodnoty null. Každá z vazeb mezi tabulkami má svůj název a symboliku. Za zmínku stojí tabulka DENIK, která od prvního pohledu nemusí naznačovat svůj účel. Jak je zmíněno v požadavcích na budoucí databázi, jedná se o tabulku, která uchovává komunikaci se státní správou neboli podací deník, který musí všechny školky ze zákona spravovat. Konkrétní popis veškerých tabulek a jejich typ je k nahlédnutí v příloze 8.8. Obr. 10 Podací deník
30
Obr. 11 Konceptuální model
Všechny osoby v rámci školky mají stejné základní údaje (jméno, příjmení, adresu), a proto jsem využil dědičnosti (specializace) a vytvořil rodičovskou tabulku, do které jsem vložil všechny společné údaje a vytvořil identifikační číslo. Jelikož rodičovská tabulka není ve vztahu s žádnou jinou tabulkou, je typ specializace nastaven pouze na GENERATE CHILDREN. Při generování fyzického modelu tak dojde k zániku tabulky OSOBA a k přenesení všech údajů z této tabulky do podmnožinových.
31
Obr. 12 Využití dědičnosti
V případě, kdy by tabulka OSOBA byla zároveň ve vztahu s jinou tabulkou, bylo by vhodné generovat i tuto tabulku a dědit pouze primární klíče. K zobrazení kompletních údajů o osobách by poté posloužily pohledy. Speciální druh vztahu byl použit i mezi tabulkou FAKTURA a POLOZKA (faktury), kde jsou veškeré položky identifikovány číslem, ale zároveň dědí primární klíč z tabulky FAKTURA, což všechny položky propojuje s konkrétní vystavenou fakturou. Tím je vytvořena závislost jedné entity na druhé, neboť položky nemohou existovat bez faktur.
Obr. 13 Závislost na 2 PK
3.10. Fyzický model Fyzický model databáze se získá jednoduchým vygenerováním z konceptuálního modelu, kdy všechny hlavní kroky provede CASE nástroj automaticky, avšak je nezbytné na modelu provést další úpravy, které jsou zmíněny dále. Tento model je již ovlivněn databázovým systémem, který je zapotřebí vybrat ve fázi generování. Ten je nutno dopředu rozmyslet, neboť databázové systémy často podporují rozlišné funkce. V mém případě je použit DBMS MYSQL 5.0, proto SQL skripty neobsahují CHECK konstrukce a indexy. Tento problém jsem nucen řešit dodatečnými úpravami kódů. 32
Při vygenerování modelu dojde automaticky k vytvoření cizích klíčů a vazebních tabulek na základě nadefinovaných vztahů v konceptuálním modelu. Ačkoliv se jedná o automatický proces, neznamená to, že tím všechna práce končí. Často je například nezbytné přejmenování cizích klíčů a vazebních tabulek, jež mají nevhodný název. Nejdůležitějším krokem, ke kterému nedochází automaticky, je nastavení integrit veškerých vztahů, neboť výchozí volba RESTRICT není ve všech případech vhodná, protože mnohdy zpomaluje a znesnadňuje manipulaci s databází. V případě vazeb směřujících k vazebním tabulkám jsem zvolil typ CASCADE a zautomatizoval tak manipulaci s daty ve vazebních tabulkách, při změnách vykonaných v tabulkách, které propojuje.
Obr. 14 Nastavení referenční integrity
V případě tabulek s nepovinnou účastí ve vztahu jsem volil omezení SET NULL, čímž je automaticky nastavena tato hodnota, pokud dojde ke smazání odkazovaného záznamu. Při smazání některého z kroužků tak automaticky dojde k nastavení cizího klíče na prázdnou hodnotu u všech dětí, které jej navštěvovaly. To na jednu stranu zrychluje manipulaci, ale na druhou zvyšuje riziko ztráty údajů, pokud se kroužek smaže nechtěně. Na fyzickém modelu je již patrný vznik cizích klíčů označených značkou , která je v některých tabulkách hned několikrát. Označení se týká primárních klíčů, které jsou zároveň cizími klíči. Ty vznikají v případech dědičnosti, řešení vztahů M:N a závislosti jedné tabulky na druhé.
33
Obr. 15 Fyzický model DB
34
Z modelu je patrné vytvoření dvou nových tabulek, které realizují vztahy M:N. Jedná se o tabulky DOCHAZI a ZASTUPUJE, které obsahují pouze zděděné primární klíče z rodičovských tabulek. V upraveném fyzickém modelu poté v CASE nástroji stačí najít volbu, která automaticky vygeneruje SQL kód. Ten je popsán v následující podkapitole a celý je k nahlédnutí v přílohách.
3.11. Vygenerovaný SQL skript 3.11.1.
Popis skriptu
Výsledný SQL skript vytvořený na základě notace MYSQL 5.0 obsahuje příkazy na vytvoření tabulek včetně primárních klíčů, nastavení vztahů a následně příkazy pro smazání tabulek. Nejprve je tedy popsán SQL kód vytvářející tabulky. Z kódu je patrný název vytvářené tabulky a polí, kde u každého pole je dále zmíněn datový typ (INT, CHAR, VARCHAR…). Dále je určen primární klíč ID_OS a též je pro některá pole specifikováno, jakých musí nabývat hodnot. /*==============================================================*/ /* Table: ZASTUPCE */ /*==============================================================*/ CREATE TABLE ZASTUPCE ( ID_OS INT NOT NULL, JMENO VARCHAR(30) NOT NULL, PRIJMENI VARCHAR(30) NOT NULL, MESTO VARCHAR(30) NOT NULL, ULICE VARCHAR(40) NOT NULL, PSC CHAR(5) NOT NULL, TELEFON CHAR(9) NOT NULL, NAROZEN DATE, ZAST_ZAMESTNANI VARCHAR(30), TYP ENUM('otec', 'matka', 'jiné') NOT NULL, PRIMARY KEY (ID_OS) );
Pro tvorbu vztahů je použit příkaz začínající ALTER TABLE, jehož součástí je i definování referenční integrity v případě mazání a upravování záznamů v tabulkách. Dále je součástí kódu název cizího klíče jedné tabulky, který odkazuje na primární klíč druhé tabulky, a dochází tedy k propojení tabulek a ke vzniku vazby.
35
ALTER TABLE DITE ADD CONSTRAINT FK_VYUCOVANO FOREIGN KEY (OZNACENI) REFERENCES TRIDA (OZNACENI) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE DOCHAZI ADD CONSTRAINT FK_DOCHAZISKOL FOREIGN KEY (SKOLENI_NAZEV) REFERENCES SKOLENI (SKOLENI_NAZEV) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE DITE ADD CONSTRAINT FK_NAVSTEVUJE FOREIGN KEY (KROUZEK_NAZEV) REFERENCES KROUZEK (KROUZEK_NAZEV) ON DELETE SET NULL ON UPDATE RESTRICT;
Pro smazání vytvořených databázových struktur slouží příkaz DROP TABLE. DROP TABLE IF EXISTS DITE;
Administrační SQL příkazy jsou k nahlédnutí v přílohách 8.4 a 8.5.
3.11.2.
Úpravy skriptu
Nepodařilo se mi najít v CASE nástroji přidělování atributu AUTO_INCREMENT, proto jsem jej musel ke všem identifikačním číslům přidělit ručně podle manuálu [9]. CREATE TABLE DITE ( ID_OS
INT NOT NULL,
->
ID_OS
INT NOT NULL AUTO_INCREMENT,
Dále jsem musel pro jednotlivá identifikační čísla s tímto atributem nastavit počáteční hodnoty, neboť všechny začínají od jedné. ALTER TABLE DITE AUTO_INCREMENT = 2000; ALTER TABLE FAKTURA AUTO_INCREMENT = 10000;
K vyřešení složitější doménové integrity jsem byl nucen pro některá pole změnit ve fyzickém modelu datový typ na ENUM a obejít tak neschopnost DBMS MYSQL aplikovat funkci CHECK. Tudíž v konceptuálním modelu mají nastavený jiný datový typ (CHAR, VARCHAR), než který je následně použit. Takto nadefinovaná pole zabraňují uživatelům vkládat libovolné hodnoty a zvyšují relevantnost ukládaných dat.
36
POHLAVI ENUM ('M', 'Ž') OZNACENI ENUM ('SKLAD1', 'SKLAD2', 'ŘEDITELNA', 'KUMBÁL1', 'KUMBÁL2', 'KUCHYŇ', 'KUCHYŇKA1', 'KUCHYŇKA2', 'BERUŠKY', 'KUŘÁTKA', 'MEDVĚDI', 'MYŠKY', 'KANCELÁŘ1', 'KANCELÁŘ2', 'DÍLNA', 'ZAHRADA', 'PRÁDELNA') POJISTOVNA_KOD ENUM ('207', '213', '201', '209', '227', '211', '217', '228', '222', '205', '111') KROUZEK_NAZEV ENUM ('KERAMIKA','ANGLIČTINA','PLAVÁNÍ') JEDNOTKA ENUM ('kg','l','ks') DOCHAZKA ENUM ('CELO', 'POLO') TYP ENUM ('otec', 'matka', 'jiné') FUNKCE ENUM ('učitelka', 'uklízečka', 'kuchařka', 'hospodářka', 'ekonomka', 'zástupkyně', 'ředitelka')
V případě, že v budoucnu dojde k vytvoření např. dalšího kroužku, je možné změnit hodnoty jakéhokoliv datového typu ENUM prostřednictvím příkazu ALTER TABLE MODIFY COLUMN. Stejně tak se dá k jakékoliv tabulce dodatečně přidat sloupec, pokud bude chtít školka uchovávat doplňující informace.
3.11.3.
Vytvoření pohledů
Jedná se o stejné objekty, jako jsou tabulky, s tím rozdílem, že neobsahují skutečná data, nýbrž odkazy na tabulky, ze kterých data získávají. Pohledy mohou zobrazovat údaje z několika tabulek zároveň, čímž usnadňují manipulaci s databází. Pro mateřskou školku jsem vytvořil pohled, který bude zobrazovat děti, včetně jejich zástupců a kontaktu v případě úrazu. CREATE VIEW DITE_KONTAKT as SELECT DITE.ID_OS, DITE.JMENO, DITE.PRIJMENI, TRIDA, ALERGIE, PECE, TELEFON, TYP from DITE join ZASTUPUJE on (DITE.ID_OS = DITE) join ZASTUPCE on (ZASTUPCE = ZASTUPCE.ID_OS);
Obr. 16 Pohled propojující 3 tabulky
37
4. INSTALACE, NASTAVENÍ A OTESTOVÁNÍ DATABÁZOVÉHO SYSTÉMU V tuhle chvíli je již navržena budoucí databáze včetně vygenerovaných SQL skriptů na základě vytvořeného fyzického modelu databáze v PowerDesigneru. V této kapitole je popsán následující krok nezbytný pro splnění cílů bakalářské práce a naplnění požadavků mateřské školky. Následující podkapitoly se zabývají instalací mnou vybraného databázového systému MySQL, který považuji vhodný k použití v tomto konkrétním příkladě. Po instalaci se dále zmiňuji o doplňujících nastaveních, která jsou nutná pro správné fungování DBMS. Do připraveného databázového systému jsou následně vkládány vygenerované SQL příkazy, které vytvoří databázové struktury, včetně kódů zajišťující integritu databáze a propojení tabulek. Nejprve jsou uvedeny stručné informace o vybraném systému.
4.1. MySQL MySQL je databázový systém, vytvořený švédskou firmou MySQL AB, nyní vlastněný společností Sun Microsystems. Jedná se o multiplatformní databázi, komunikace s ní probíhá prostřednictvím jazyka SQL. Tento databázový systém je volně ke stažení a je k dispozici pod licencí GPL. [4][8]
4.2. Instalace MySQL Databázový systém byl nainstalován na notebook Sony Vaio model VGN-CR21Z/R s následujícím nastavením: •
Operační systém: Windows Vista Home Premium Service Pack 2
•
Operační paměť: DDR2 2 GB RAM
•
Procesor: Intel Core Duo 2,00 GHz
38
Instalační soubor je volně přístupný na domovských webových stránkách 9. V tomto případě je použit instalátor mysql-essential-5.1.46-win32.msi. Instalace dále probíhá v dialogovém okně, kde je nejprve zvolen typ instalace TYPICAL a následně vybráno místo na disku kam se má DBMS nainstalovat. Instalace probíhá automaticky.
4.3. Prvotní konfigurace MySQL Po samotné instalaci následuje proces prvotní konfigurace MySQL. Tato konfigurace též probíhá v dialogovém okně. V počáteční volbě je na výběr mezi detailní a standardní konfigurací, která provede všeobecně používané nastavení. Já jsem zvolil detailní konfiguraci, abych mohl vše nastavit na míru pro moji databázi a pro použití v mateřské školce. Následné volby ovlivňují, kolik paměti bude MySQL využívat, jaký ukládací „engine“ se bude používat a kolik simultánně připojených uživatelů se předpokládá. Šesté dialogové okno dává vybrat mezi znakovými sadami, které se mají použít. Z důvodu podpory českého jazyka jsem nucen zvolit znakovou sadu UTF8 jako výchozí, případně lze zvolit i latin2. Posledním krokem je nastavení hesla pro root 10 uživatele. Následnou volbou EXECUTE je proces spuštěn. V případě, že pro konfiguraci není použit MySQL Configuration Wizard, je nezbytné alespoň nastavit heslo pro účet root. To lze příkazem[4]: %>mysql –u root mysql %>SET PASSWORD FOR root@localhost=PASSWORD(“heslo”);
Po úspěšném připojení skrze klienta mysql, ve kterém jsem provedl většinu administračních úloh, můžu příkazem SHOW VARIABLES zjistit veškerá aktuální nastavení.
4.4. Nastavení MySQL V této podkapitole jsou rozebrány některé důležité konfigurační a optimalizační parametry, jež mají klíčový vliv na fungování MySQL.[4] Parametr back_log určuje počet připojení, kterým je povoleno řadit se do fronty za obzvláště zatěžujícím novým připojením. Parametr data_dir určuje adresář dat MySQL. 9
Dostupných z WWW: Hlavní administrátorský účet
10
39
Parametr default_table_type určuje typ tabulek (MyISAM, InnoDB a další.) Parametr key_buffer_size určuje množství paměti, které se alokuje pro ukládání bloků indexu. Parametr max_connections určuje maximální počet simultánních připojení. Výchozí hodnota je 100 a pro případ školky, ale i z výkonnostních důvodů jsem počet snížil. Pro nastavení jazykových sad slouží parametry character_set_ , jež jsou nastavené na UTF8 (na základě prvotních konfigurací) z důvodu podpory češtiny. Většina parametrů se dá změnit příkazem „SET GLOBAL parametr = hodnota“. Použitím příkazu SHOW VARIABLES bylo zjištěno přes 240 takových parametrů, které se dají různě nastavovat a tak není možné rozebrat všechny. Parametry lze konfigurovat i prostřednictvím konfigurační šablony my.ini, která se nachází v adresáři s MySQL.
4.5. Vytvoření DB Nejprve byla v MySQL příkazem CREATE DATABASE vytvořena databáze PASTELKA, ke které jsem se následně připojil a začal vytvářet databázové struktury.
Obr. 17 Vytvoření a zvolení DB
Ačkoliv vkládat data přes klienta mysql lze, není možné tak činit ve znakové sadě UTF-8, protože příkazový řádek tuto znakovou sadu nepodporuje. Z důvodu možnosti užití diakritiky je pro vkládání testovacích dat použita až vytvořená aplikace.
40
4.6. Zabezpečení MySQL 4.6.1. Přístupová oprávnění Základní přístupová oprávnění se specifikují na základě dvou pojmů. Prvním z nich je autentizace neboli ověřování totožnosti, která určuje, zda bude uživateli vůbec zpřístupněn server. Druhým pojmem je autorizace, jež určuje, zda má uživatel dostatečná práva k vykonávání požadavků.[4] Veškeré informace o uživatelích a jejich právech se nacházejí v databázi mysql v tabulce USER. K zobrazení stávajících uživatelů jsem použil příkaz „select * from user“, kde se zatím vypsal pouze uživatel root, který má všechny práva. Nového uživatele jsem jednoduše vytvořil přes SQL příkaz CREATE USER a jak je vidět na dalším obrázku nový uživatel nemá z počátku žádné práva. Heslo je automaticky zakódováno 41 bitovou funkcí.
Obr. 18 Vytvoření uživatele
Obr. 19 Seznam uživatelských práv
Práva jsem následně přidělil pomocí příkazu GRANT, kdy jsem se řídil všeobecně známou poučkou o přidělování nejmenších možných práv. Práva jdou přidělovat na jednotlivé databáze, ale i na jednotlivé tabulky či sloupce. Což je velmi užitečné, neboť například uživatel HOSPODÁŘKA by neměl mít možnost zobrazovat informace z tabulek určených zejména pro ředitelku školky (např. ZAMĚSTNANCI a výše jejich platů).
41
Veškeré informace o právech na činnosti nad databázemi, tabulkami a sloupci jsou ukládány ve speciálních tabulkách, jako je tomu v případě tabulky USER. Jedná se o tabulky DB, HOST, TABLES_PRIV, COLUMNS_PRIV a PROCS_PRIV. Co která obsahuje, se dá dobře pochopit už podle názvu.
4.6.2. Limity na konzumaci prostředků MySQL od čtvrté verze umožňuje nastavovat pro jednotlivé uživatele limitované prostředky, jež mohou využívat. To je velmi užitečné v případě méně výkonnostních serverů a počítačů, neboť to znemožňuje uživatelům nadměrně je zatěžovat. Všechny tyto limity jsou též v tabulce USER.[4] max_connections – Určuje, kolikrát za hodinu se může uživatel připojit. max_questions – Počet dotazů za hodinu, které může uživatel položit. max_updates – Maximální počet aktualizací (UPDATE, INSERT) za hodinu. max_user_connections – Maximální počet simultánních připojení.
4.7. Zálohování databáze Zálohování je v dnešní době nezbytnou součástí pro každou databázi, neboť ke ztrátě dat nevedou pouze hardwarové problémy, ale i nevhodná manipulace ze strany uživatelů. K otestování zálohování jsem využil klienta mysqldump, který umožňuje exportovat data existujících tabulek a jejich struktury. Příkazem lze jednoduše určit, které databáze a případně které tabulky se mají zálohovat. Zálohu lze vyexportovat do nového souboru, který obsahuje SQL kódy umožňující databázi obnovit. %>mysqldump –u root –p databáze > cesta/soubor.sql
4.8. Import dat Jelikož v případě přechodu na novou databázi bude muset školka přenést data ze stávající databáze do nové, uvádím zde jednu z alternativ importu dat. Za pomoci utility mysqlimport lze do databáze importovat data z textových souborů, které obsahují určitý typ oddělovače sloupců a řádků. Import můžeme rovněž provést prostřednictvím klienta mysql použitím příkazu LOAD DATA. 42
4.9. Aplikace ke správě databáze V této kapitole je ve stručnosti popsán jazyk PHP, díky němuž jsem vytvořil požadovanou aplikaci, která zajistila komunikaci s databázovým serverem a zároveň umožnila zkontrolovat integritu a funkčnost navržené databáze. V textu jsou uvedeny nejdůležitější kódy, které zajišťují zejména spojení aplikace a databáze včetně kódů spouštějících SQL dotazy. Veškeré funkce navržené aplikace vycházejí z požadavků vedení MŠ Pastelka.
4.9.1. Webový server K fungování aplikace je zapotřebí webového serveru, který zpracuje PHP skripty a vrátí uživateli výsledek. Já jsem zvolil Apache, neboť je nejpoužívanější a jeho instalace a nastavení není nijak složité. [13]
4.9.2. PHP Zkratka vychází z anglického Hypertext Preprocessor. Jedná se o hojně využívaný skriptovací jazyk, jehož první verze vznikla již v roce 1994. Od té doby prošel mnoha změnami a aktuální verze 11je dostupná na webových stránkách [12]. Tento jazyk slouží zejména k vytváření dynamických internetových stránek a webových aplikací. PHP je nezávislý na platformě, skripty fungují bez větších úprav na mnoha různých operačních systémech. PHP je počítačový software s otevřeným zdrojovým kódem, neboli Open source software, což umožňuje legální dostupnost tohoto softwaru při dodržení určitých podmínek.[6] Důvodem pro výběr daného jazyka, určeného k tvorbě aplikace byl fakt, že PHP umožňuje přístup ke většině databázových systémů a to poměrně jednoduše pomocí rozhraní ODBC 12 či funkcí MySQL_.
11 12
K 17.4. 2010 je to verze 5.3.2 Open DataBase Connectivity, standardní aplikační rozhraní pro přístup k datům.
43
4.9.3. Komunikace s databází Jazyk PHP5 při aktivování rozšíření extension=php_mysql.dll podporuje následující mysql funkce. Základní funkcí je mysql_connect(), která slouží k připojení k databázovému serveru. Následně už jen stačí vybrat databázi pomocí funkce mysql_select_db(název databáze) a po připojení ke konkrétní databázi lze jednoduše zadávat SQL příkazy pomocí funkce mysql_query. K načtení dat z dotazu slouží např. mysql_fetch_row() či mysql_fetch_array(), který získá celý řádek z výsledku a umístí jej do pole.[6]
4.10. Otestování funkčnosti aplikace a databáze Navržená aplikace umožňuje zobrazit veškeré tabulky včetně dodatečně vytvořených pohledů. Dále pak zprostředkovává základní manipulaci se záznamy, což zahrnuje jejich mazání, vkládání a úpravu a to vše bez znalosti jazyka SQL.
Obr. 20 Vkládání záznamů prostřednictvím vytvořené aplikace
Dodatečné snímky aplikace a průběhu testování databáze jsou k nahlédnutí v příloze 8.7. Vytvořenou aplikaci lze umístit například na webové stránky školky a umožnit tak zaměstnancům vzdálený přístup do databáze. S tím je na druhou stranu spojeno nebezpečí přesunu důležitých dat mimo prostory mateřské školky a možnosti jejich zneužití.
44
5. ZÁVĚR V úvodní části práce je popsána analýza současného stavu nakládání s daty v MŠ Pastelka, jež měla za cíl určit budoucí požadavky na navrhovanou databázi a stejně tak měla za úkol identifikovat stávající problémy v DB, které se neměly opakovat. Do jisté míry popisuje způsob provádění analýzy výchozí databáze, jež byla základem pro nově navrhovanou. V této části práce se též definovaly požadavky na budoucí databázi a aplikaci. V další, teoretické části práce je čtenář seznámen s podrobným popisem procesu návrhu relační databáze, který následuje bezprostředně po provedené analýze. V úvodu jsou zmíněny vlastnosti relační databáze, modelu a databázového systému, jež poskytují ucelený pohled na výhody a nedostatky tohoto typu databáze. Na teoretickou část práce, kde jsou rozebrány všechny významné aspekty relační databáze (tabulka, pole, relace…), volně navazuje praktická část, která začíná konceptuálním modelováním databáze v programu PowerDesigner a aplikováním veškerých teoretických poznatků z předešlých kapitol. Výstupem je navržená databáze včetně aplikovaných integritních omezení a vygenerovaných SQL skriptů, na základě kterých je vytvořena databáze v mnou zvoleném databázovém systému, který jsem vybral na základě ceny, dostupnosti a vhodnosti užití v daném případě. Posledním úkolem bylo vytvoření aplikace pro administraci navržené databáze. Posloužila i ke kontrole této databáze při použití testovacích dat, což bylo rovněž úkolem této práce. Za hlavní přínos práce považuji popsání procesu návrhu relační databáze, včetně následné praktické ukázky od samotného návrhu databáze až po instalaci systému a zprovoznění fungující aplikace. Mateřské školce Pastelka bylo prezentováno řešení a přislíbena další práce na aplikaci v případě budoucí implementace této databáze.
45
6. CITOVANÁ LITERATURA [1] BASL, Josef a BLAŽÍČEK, Roman. Podnikové informační systémy. Praha : Grada Publishing, a.s., 2008. ISBN 978-80-247-2279-5. [2] CODD, E.F. A Relational Model of Data for Large Shared Databanks. Dostupný z WWW: [3] CODD, E.F. The Relational Model for Database Management, Version 2. AddisonWesley, 1990. ISBN 0-201-14192-2. [4] GILMORE, W. Jason. Velká kniha PHP a MySQL 5. Brno : Zoner Press, 2007. ISBN 8086815-53-6. [5] HERNANDEZ, Michael J. Návrh databází. Praha : Grada Publishing, a.s., 2006. ISBN 80247-0900-7. [6] KOSEK, Jiří. PHP Tvorba interaktivních internetových aplikací. Praha : Grada Publishing, a.s., 1999. ISBN 80-7169-373-1. [7] MERUŇKA, Vojtěch a VOSTROVSKÝ, Václav. Databázové systémy. Praha : ČZU,PEF, 1998. ISBN 80-213-0543-6. [8] MySQL: The world's most popular open source database. [Online] Oracle Corporation, 2010. [Citace: 1. 5. 2010.] Dostupný z WWW: . [9] MYSQL 5.5: Referece Manual CREATE TABLE Synxtax. [Online] Oracle Corporation, 2010. [Citace: 3. 5. 2010.] Dostupný z WWW: . [10] MYSQL 5.5: Referece Manual CREATE TRIGGER Synxtax. [Online] Oracle Corporation, 2010. [Citace: 3. 5. 2010.] Dostupný z WWW: . [11] MYSQL 5.5: Referece Manual ALTER TABLE Synxtax. [Online] Oracle Corporation, 2010. [Citace: 3. 5. 2010.] Dostupný z WWW: . [12] PHP: Hypertext Preprocessor. [Online] The PHP Group, 2010. [Citace: 2. 5. 2010.] Dostupný z WWW: . [13] The Apache http Server Project. [Online] The Apache Software Foundation, 2010 [Citace: 4. 5. 2010] Dostupný z WWW: .
46
7. TERMINOLOGICKÝ SLOVNÍK ATRIBUT – Ekvivalent pole v relačním modelu. BUSINESS PRAVIDLO – Doplňující omezení databáze založené na způsobu jak organizace chápe data. ČÍSELNÍK – Validační tabulka. DATA – Údaje v databázi. DATOVÁ TABULKA – Tabulka uchovávající data k získávání základních informací. DBMS (DATABASE MANAGEMENT SYSTEM) – Systém pro správu databáze. Softwarový program používaný ke správě databáze. DOMÉNOVÁ INTEGRITA – Integrita na úrovni polí. DUPLICITNÍ DATA – Takové hodnoty polí, jež se objevují několikrát v databázi a nejsou primárními klíči. DUPLICITNÍ POLE – Takové pole, jež se objevuje více jak v jedné tabulce. INDEX – Struktura v rámci relačních databází, která slouží ke zvýšení výkonu při zpracování dat. INFORMACE – Data srozumitelná a užitečná určité osobě. INTEGRITA DAT – Množina pravidel, jež řídí konzistentnost a přesnost dat v databázi. KARDINALITA – Typ vztahu, který existuje mezi dvojicí tabulek. KLÍČ – Druh pole, jež plní v databázi speciální úlohu. V Relační databázi identifikujeme 2 druhy klíčů. Primární klíč a cizí klíč. KONZISTENTNOST DAT – Všechny výskyty dané hodnoty pole jsou vždy stejné. NORMALIZACE – Proces rozkladu tabulek za účelem snížení redundantních a duplicitních dat. NORMÁLNÍ FORMA – Množina pravidel sloužící k otestování kvality návrhu tabulky. PODMNOŽINOVÁ TABULKA – Tabulka reprezentující podřízenou entitu datové tabulky.
47
POHLED – Virtuální tabulka složená z polí z jedné či více tabulek. POLE – Nejmenší struktura v databázi, též nazývaná atribut. Uchovává data. POPIS TABULKY- Tvrzení poskytující jasnou definici entity reprezentované tabulkou. PRIMÁRNÍ KLÍČ – Pole, ale i skupina polí, která identifikuje každý záznam v tabulce. PROGRAMOVACÍ JAZYK – Softwarový program, který je použit k definování instrukcí, které jsou následně vykonány počítačem. RDBMS (RELATIONAL DATABASE MANAGEMENT SYSTEM) – Systém pro správu relační databáze. REDUNDANTNÍ DATA – Údaj, který se v poli opakuje v důsledku účasti tabulky ve vztahu. REFERENČNÍ INTEGRITA – Integrita na úrovni vztahů. RELAČNÍ DATABÁZE – Typ databáze ukládající data v relacích. Každý vztah je složen ze záznamů a polí. SQL (STRUCTURED QUERY LANGUAGE) – Standardizovaný dotazovací jazyk používaný pro práci s daty v relačních databázích. TRIGGER – Automatizovaná událost vyvolaná definovanou akcí nad tabulkou. VZTAH – Slouží ke svázání dat, která spolu souvisejí a jsou umístěny v různých databázových tabulkách. ZÁZNAM – Struktura složená z úplné množiny jednotlivých hodnot pro každé pole v tabulce.
48
8. PŘÍLOHY 8.1. Upravený SQL skript /*==============================================================*/ /* DBMS name: MySQL 5.0 */ /* Created on: 1.5.2010 17:50:39 */ /*==============================================================*/ DROP TABLE IF EXISTS DENIK; DROP TABLE IF EXISTS DITE; DROP TABLE IF EXISTS DOCHAZI; DROP TABLE IF EXISTS FAKTURA; DROP TABLE IF EXISTS KROUZEK; DROP TABLE IF EXISTS MAJETEK; DROP TABLE IF EXISTS MISTNOST; DROP TABLE IF EXISTS POLOZKA; DROP TABLE IF EXISTS SKLAD; DROP TABLE IF EXISTS SKOLENI; DROP TABLE IF EXISTS TRIDA; DROP TABLE IF EXISTS ZAMESTNANEC; DROP TABLE IF EXISTS ZASTUPCE; DROP TABLE IF EXISTS ZASTUPUJE; /*==============================================================*/ /* Table: DENIK */ /*==============================================================*/ CREATE TABLE DENIK ( P_CISLO SMALLINT NOT NULL AUTO_INCREMENT, VYRIZUJE INT NOT NULL, DORUCENO DATE NOT NULL, DORUCENO_OD VARCHAR(30) NOT NULL, P_LIST SMALLINT NOT NULL, P_PRILOHA SMALLINT, OBSAH VARCHAR(50) NOT NULL, ADRESAT VARCHAR(40) NOT NULL, ODESLANO DATE, ZNAK VARCHAR(3) NOT NULL, PRIMARY KEY (P_CISLO) ); /*==============================================================*/ /* Table: DITE */ /*==============================================================*/ CREATE TABLE DITE ( ID_OS INT NOT NULL AUTO_INCREMENT, JMENO VARCHAR(30) NOT NULL, PRIJMENI VARCHAR(30) NOT NULL, MESTO VARCHAR(30) NOT NULL, ULICE VARCHAR(40) NOT NULL, PSC CHAR(5) NOT NULL, KROUZEK_NAZEV ENUM ('KERAMIKA','ANGLIČTINA','PLAVÁNÍ',''), TRIDA ENUM ('SKLAD1', 'SKLAD2', 'ŘEDITELNA', 'KUMBÁL1', 'KUMBÁL2', 'KUCHYŇ', 'KUCHYŇKA1', 'KUCHYŇKA2', 'BERUŠKY', 'KUŘÁTKA', 'MEDVĚDI', 'MYŠKY', 'KANCELÁŘ1', 'KANCELÁŘ2') NOT NULL, RC CHAR(11) NOT NULL,
49
POHLAVI ENUM('M', 'Ž') NOT NULL, POJISTOVNA_KOD ENUM('207', '213', '201', '209', '227', '211', '217', '228', '222', '205', '111') NOT NULL, OBCANSTVI VARCHAR(20) NOT NULL, JAZYK VARCHAR(20), NASTUP DATE NOT NULL, SKOLNE INT NOT NULL, ALERGIE VARCHAR(30), PECE VARCHAR(30), DOCHAZKA ENUM('CELO', 'POLO') NOT NULL, PRIMARY KEY (ID_OS) ); /*==============================================================*/ /* Table: DOCHAZI */ /*==============================================================*/ CREATE TABLE DOCHAZI ( ZAMESTNANEC INT NOT NULL, SKOLENI VARCHAR(40) NOT NULL, PRIMARY KEY (ZAMESTNANEC, SKOLENI) ); /*==============================================================*/ /* Table: FAKTURA */ /*==============================================================*/ CREATE TABLE FAKTURA ( ID_FAKTURY INT NOT NULL AUTO_INCREMENT, PRIJEMCE INT, VYSTAVENI DATE NOT NULL, SPLATNOST DATE NOT NULL, ZAPLACENO DATE, DODAVATEL VARCHAR(30) NOT NULL, KS CHAR(4), VS VARCHAR(10), UCET VARCHAR(15) NOT NULL, PRIMARY KEY (ID_FAKTURY) ); /*==============================================================*/ /* Table: KROUZEK */ /*==============================================================*/ CREATE TABLE KROUZEK ( KROUZEK_NAZEV ENUM ('KERAMIKA','ANGLIČTINA','PLAVÁNÍ') NOT NULL, TERMIN TIME NOT NULL, POPIS VARCHAR(50), PRIMARY KEY (KROUZEK_NAZEV) ); /*==============================================================*/ /* Table: MAJETEK */ /*==============================================================*/ CREATE TABLE MAJETEK ( INVENTAR INT NOT NULL AUTO_INCREMENT, MISTNOST ENUM('SKLAD1', 'SKLAD2', 'ŘEDITELNA', 'KUMBÁL1', 'KUMBÁL2', 'KUCHYŇ', 'KUCHYŇKA1', 'KUCHYŇKA2', 'BERUŠKY', 'KUŘÁTKA', 'MEDVĚDI', 'MYŠKY', 'KANCELÁŘ1', 'KANCELÁŘ2', 'DÍLNA', 'ZAHRADA', 'PRÁDELNA') NOT NULL, NAZEV VARCHAR(40) NOT NULL, CENA INT NOT NULL,
50
PRIMARY KEY (INVENTAR) ); /*==============================================================*/ /* Table: MISTNOST */ /*==============================================================*/ CREATE TABLE MISTNOST ( OZNACENI ENUM ('SKLAD1', 'SKLAD2', 'ŘEDITELNA', 'KUMBÁL1', 'KUMBÁL2', 'KUCHYŇ', 'KUCHYŇKA1', 'KUCHYŇKA2', 'BERUŠKY', 'KUŘÁTKA', 'MEDVĚDI', 'MYŠKY', 'KANCELÁŘ1', 'KANCELÁŘ2', 'DÍLNA', 'ZAHRADA', 'PRÁDELNA') NOT NULL, POPIS VARCHAR(60), PATRO SMALLINT NOT NULL, PRIMARY KEY (OZNACENI) ); /*==============================================================*/ /* Table: POLOZKA */ /*==============================================================*/ CREATE TABLE POLOZKA ( ID_FAKTURY INT NOT NULL, ID_POLOZKY INT NOT NULL AUTO_INCREMENT, ID_MATERIALU INT, POPIS VARCHAR(30), MNOZSTVI INT NOT NULL, CENA INT NOT NULL, JEDNOTKA ENUM ('kg','l','ks',''), PRIMARY KEY (ID_FAKTURY, ID_POLOZKY) ); /*==============================================================*/ /* Table: SKLAD */ /*==============================================================*/ CREATE TABLE SKLAD ( ID_MATERIALU INT NOT NULL AUTO_INCREMENT, NAZEV VARCHAR(40) NOT NULL, JEDNOTKA ENUM ('kg','l','ks') NOT NULL, DPH SMALLINT, MNOZSTVI INT NOT NULL, AKTUALIZACE TIMESTAMP, PRIMARY KEY (ID_MATERIALU) ); /*==============================================================*/ /* Table: SKOLENI */ /*==============================================================*/ CREATE TABLE SKOLENI ( SKOLENI_NAZEV VARCHAR(40) NOT NULL, MISTO VARCHAR(40) NOT NULL, PORADATEL VARCHAR(40) NOT NULL, TERMIN_OD DATE NOT NULL, TERMIN_DO DATE NOT NULL, CENA INT NOT NULL, PRIMARY KEY (SKOLENI_NAZEV) ); /*==============================================================*/ /* Table: TRIDA */ /*==============================================================*/
51
CREATE TABLE TRIDA ( OZNACENI VARCHAR(30) NOT NULL, KAPACITA SMALLINT NOT NULL, ZAMERENI VARCHAR(30), PRIMARY KEY (OZNACENI) ); /*==============================================================*/ /* Table: ZAMESTNANEC */ /*==============================================================*/ CREATE TABLE ZAMESTNANEC ( ID_OS INT NOT NULL AUTO_INCREMENT, JMENO VARCHAR(30) NOT NULL, PRIJMENI VARCHAR(30) NOT NULL, MESTO VARCHAR(30) NOT NULL, ULICE VARCHAR(40) NOT NULL, PSC CHAR(5) NOT NULL, VYUCUJE ENUM('SKLAD1', 'SKLAD2', 'ŘEDITELNA', 'KUMBÁL1', 'KUMBÁL2', 'KUCHYŇ', 'KUCHYŇKA1', 'KUCHYŇKA2', 'BERUŠKY', 'KUŘÁTKA', 'MEDVĚDI', 'MYŠKY', 'KANCELÁŘ1', 'KANCELÁŘ2', 'DÍLNA', 'ZAHRADA', 'PRÁDELNA', ''), RC CHAR(11) NOT NULL, OBCANKA CHAR(9) NOT NULL, NASTUP DATE NOT NULL, PP VARCHAR(30) NOT NULL, PLAT INT NOT NULL, PRIPLATKY INT, FUNKCE ENUM('učitelka', 'uklízečka', 'kuchařka', 'hospodářka', 'ekonomka', 'zástupkyně', 'ředitelka') NOT NULL, NAPLN VARCHAR(60), UCET VARCHAR(15) NOT NULL, POJISTOVNA_KOD ENUM('207', '213', '201', '209', '227', '211', '217', '228', '222', '205', '111') NOT NULL, VZDELANI VARCHAR(30) NOT NULL, TELEFON CHAR(9) NOT NULL, PRIMARY KEY (ID_OS) ); /*==============================================================*/ /* Table: ZASTUPCE */ /*==============================================================*/ CREATE TABLE ZASTUPCE ( ID_OS INT NOT NULL AUTO_INCREMENT, JMENO VARCHAR(30) NOT NULL, PRIJMENI VARCHAR(30) NOT NULL, MESTO VARCHAR(30) NOT NULL, ULICE VARCHAR(40) NOT NULL, PSC CHAR(5) NOT NULL, TELEFON CHAR(9) NOT NULL, NAROZEN DATE, ZAST_ZAMESTNANI VARCHAR(30), TYP ENUM('otec', 'matka', 'jiné') NOT NULL, PRIMARY KEY (ID_OS) ); /*==============================================================*/ /* Table: ZASTUPUJE */ /*==============================================================*/ CREATE TABLE ZASTUPUJE
52
( DITE INT NOT NULL, ZASTUPCE INT NOT NULL, PRIMARY KEY (DITE, ZASTUPCE) ); ALTER TABLE DENIK ADD CONSTRAINT FK_VYRIZUJE FOREIGN KEY (VYRIZUJE) REFERENCES ZAMESTNANEC (ID_OS) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE DITE ADD CONSTRAINT FK_NAVSTEVUJE FOREIGN KEY (KROUZEK_NAZEV) REFERENCES KROUZEK (KROUZEK_NAZEV) ON DELETE SET NULL ON UPDATE RESTRICT; ALTER TABLE DITE ADD CONSTRAINT FK_VYUCOVANO FOREIGN KEY (TRIDA) REFERENCES TRIDA (OZNACENI) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE DOCHAZI ADD CONSTRAINT FK_DOCHAZISKOL FOREIGN KEY (SKOLENI) REFERENCES SKOLENI (SKOLENI_NAZEV) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE DOCHAZI ADD CONSTRAINT FK_DOCHAZIZAM FOREIGN KEY (ZAMESTNANEC) REFERENCES ZAMESTNANEC (ID_OS) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE FAKTURA ADD CONSTRAINT FK_PRIJAL FOREIGN KEY (PRIJEMCE) REFERENCES ZAMESTNANEC (ID_OS) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE MAJETEK ADD CONSTRAINT FK_OBSAHUJE FOREIGN KEY (MISTNOST) REFERENCES MISTNOST (OZNACENI) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE POLOZKA ADD CONSTRAINT FK_DOPLNUJE FOREIGN KEY (ID_MATERIALU) REFERENCES SKLAD (ID_MATERIALU) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE POLOZKA ADD CONSTRAINT FK_SOUCAST FOREIGN KEY (ID_FAKTURY) REFERENCES FAKTURA (ID_FAKTURY) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE TRIDA ADD CONSTRAINT FK_TYP_MISTNOSTI FOREIGN KEY (OZNACENI) REFERENCES MISTNOST (OZNACENI) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE ZAMESTNANEC ADD CONSTRAINT FK_VYUCUJE FOREIGN KEY (VYUCUJE) REFERENCES TRIDA (OZNACENI) ON DELETE SET NULL ON UPDATE RESTRICT; ALTER TABLE ZASTUPUJE ADD CONSTRAINT FK_ZASTUPUJE FOREIGN KEY (DITE) REFERENCES DITE (ID_OS) ON DELETE CASCADE ON UPDATE CASCADE; ALTER TABLE ZASTUPUJE ADD CONSTRAINT FK_ZASTUPUJEZAST FOREIGN KEY (ZASTUPCE) REFERENCES ZASTUPCE (ID_OS) ON DELETE CASCADE ON UPDATE CASCADE;
8.2. Nastavení výchozích identifikačních čísel ALTER TABLE DITE AUTO_INCREMENT = 2000; ALTER TABLE ZAMESTNANEC AUTO_INCREMENT = 1000; ALTER TABLE ZASTUPCE AUTO_INCREMENT = 3000; ALTER TABLE FAKTURA AUTO_INCREMENT = 10000; ALTER TABLE DENIK AUTO_INCREMENT = 1; ALTER TABLE SKLAD AUTO_INCREMENT = 5000; ALTER TABLE MAJETEK AUTO_INCREMENT = 8000; ALTER TABLE POLOZKA AUTO_INCREMENT = 20000;
53
8.3. Pohledy CREATE VIEW DITE_KONTAKT as SELECT DITE.ID_OS, DITE.JMENO, DITE.PRIJMENI, TRIDA, ALERGIE, PECE, TELEFON, TYP from DITE join ZASTUPUJE on (DITE.ID_OS = DITE) join ZASTUPCE on (ZASTUPCE = ZASTUPCE.ID_OS);
8.4. Vytvoření uživatelů CREATE USER REDITELKA@localhost identifiend by ‘abcd1234’; CREATE USER HOSPODARKA@localhost identified by ‘efgh5678’; CREATE USER ZASTUPKYNE@localhost identified by ‘ijkl9101’; CREATE USER EKONOMKA@localhost identified by ‘mnop1213’;
8.5. Přidělení práv GRANT select, insert, update, delete on PASTELKA.* to REDITELKA@localhost identified by ‘abcd1234’; GRANT select, insert, update, delete on PASTELKA.faktura to HOSPODARKA@localhost identified by ‘efgh5678’; GRANT select, insert, update, delete on PASTELKA.polozka to HOSPODARKA@localhost identified by ‘efgh5678’; GRANT select, insert, update, delete on PASTELKA.sklad to HOSPODARKA@localhost identified by ‘efgh5678’; GRANT select, insert, update, delete on PASTELKA.majetek to HOSPODARKA@localhost identified by ‘efgh5678’; GRANT select, insert, update, delete on PASTELKA.dite to ZASTUPKYNE@localhost identified by ‘ijkl9101’; GRANT select, insert, update, delete on PASTELKA.zastupuje to ZASTUPKYNE@localhost identified by ‘ijkl9101’; GRANT select, insert, update, delete on PASTELKA.zastupce to ZASTUPKYNE@localhost identified by ‘ijkl9101’;
Obr. 21 Práva přidělená na tabulky
54
8.6. Vytvořená databázová struktura v mysql
Obr. 22 Přehled tabulek
8.7. Otestování aplikace a databáze
Obr. 23 Zobrazení vytvořeného pohledu
Obr. 24 Zobrazení tabulky
55
Obr. 25 Uživatelské rozhraní
8.8. Seznam tabulek NÁZEV TABULKY
TYP TABULKY
POPIS TABULKY ZAMESTNANEC
DATOVÁ
Obsahuje informace o všech zaměstnancích ve školce. DITE
DATOVÁ
Obsahuje informace o všech dětech ve školce. ZASTUPCE
DATOVÁ
Informace o zástupcích dětí. Kontaktní údaje na zástupce. DENIK
DATOVÁ
Podací deník. Udržuje informace o uskutečněné komunikaci se státní správou. FAKTURA
DATOVÁ
Základní informace o přijatých fakturách mateřské školky. Též obsahuje stručné informace o dodavateli. POLOZKA
PODMNOŽINOVÁ
56
Jednotlivé položky faktury včetně ceny a množství. SKLAD
DATOVÁ
Popis veškerého uloženého materiálu na skladě, přehled o DPH, množství a měrných jednotkách. KROUZEK
DATOVÁ
Extra aktivity pořádané mateřskou školkou včetně jejich termínu. SKOLENI
DATOVÁ
Školení, kterých se účastní zaměstnanci školky. MISTNOST
DATOVÁ
Všechny místnosti v budově školky. Třídy jsou též určitým typem místnosti. MAJETEK
DATOVÁ
Veškerý hmotný majetek, který je součástí jednotlivých místností. TRIDA
PODMNOŽINOVÁ
Podmnožinou místnosti. Prostory, ve kterých probíhá výuka dětí. DOCHAZI
VAZEBNÍ
Přehled učitelů, kteří docházejí na jednotlivá školení. Propojuje učitele a konkrétní školení. ZASTUPUJE
VAZEBNÍ
Přehled o osobách, jež jsou zákonnými zástupci dětí a mají možnost je vyzvedávat.
57
