Vysoká škola ekonomická v Praze Fakulta informatiky a statistiky Katedra informačních technologií
Studijní program: Aplikovaná informatika Obor: Informatika
Návrh databáze a tvorba aplikace jako parametrické webové stránky pro provozovatele hotelových služeb BAKALÁŘSKÁ PRÁCE
Student
:
Aleš Menzel
Vedoucí
:
RNDr. Helena Palovská, Ph.D.
Oponent
:
Ing. David Chudán
2014
Prohlášení Prohlašuji, že jsem bakalářskou práci zpracoval samostatně a že jsem uvedl všechny použité prameny a literaturu, ze kterých jsem čerpal.
V Praze dne 12. 5. 2014
....................................................... Aleš Menzel
Poděkování Rád bych tímto poděkoval RNDr. Heleně Palovské, Ph.D. za věnovaný čas, poskytnuté rady a konstruktivní připomínky.
Abstrakt Bakalářská práce se věnuje analýze požadavků, návrhu a realizaci databáze pro potřeby rezervačního systému a jeho doplňujících částí. Práce se zaměřuje především na praktickou realizaci návrhu databáze a následnému vytvoření aplikace v podobě administračního rozhraní, jež je implementováno na databázové platformě MySQL a webových technologií. Aplikace si dává za cíl poskytnout komplexní systém, jež propojuje parametrické administrační rozhraní se stránkami tak, aby bylo možné takovouto aplikaci spravovat i bez nutnosti zásahu do zdrojového kódu. Požadavky na funkcionalitu aplikace i databáze vycházejí z analyzování konkurenčních řešení v této oblasti a osobních zkušeností s tvorbou webové aplikace pro provozovatele malého penzionu. Cíle je dosaženo navržením bezchybné databáze a realizací zmíněné aplikace. Ta následně poslouží k ověření kvality a správnosti návrhu. Ověření proběhne na testovacích datech s použitím vytvořené aplikace. Hlavním přínosem mé práce je zhotovená aplikace postavená na kvalitně zpracované databázi, která je připravena k nasazení a využití v praxi. Práce se skládá ze dvou částí, první částí jsou teoretické základy, které jsou důležité znát při návrhu databáze a krátký úvod do historie databázových modelů. V druhé, praktické části je věnován prostor samotnému návrhu databáze.
Klíčová slova Databáze, návrh databáze, MySQL, rezervace, rezervační systém, hotelový systém, parametrické řešení, konceptuální model, fyzický model, trigger.
Abstract This thesis consists of analysing database requirements, designing and creating a database for the needs of a reservation system and its corresponding parts. The purpose of this thesis is the practical implementation of the database and subsequently creating an application in the form of an administration interface, which is implemented in MySQL database platform and web technologies. Objective of the application is to provide complex system, which connects the parametric administration system with web pages in such a way, that the administrator of the application can manage the application from within the administration section. Requirements for this application and database are based on analysing the competitor´s products and my own experience with creating a web application for a small pension. The goal is achieved by designing a flawless database and implementing the application. Furthermore the application proves and validates the database implementation by using test data. Main contribution of the thesis is completed application based on a high quality database model that is ready for deployment in practice. Structure of the thesis consists of two parts. First part describes theoretical basics that are important to know before designing a database and a short introduction to history of database models. The second part is dedicated to designing the database itself.
Keywords Database, database design, MySQL, reservation, reservation system, hotel system, parametric solution, conceptual model, physical model, trigger.
Obsah 1
Úvod............................................................................................................................................... 1 1.1
Vymezení tématu práce a důvod výběru tématu ............................................................... 1
1.2
Vymezení cílů práce a metody pro jejich dosažení............................................................ 1
1.3
Struktura práce................................................................................................................................ 3
1.4
Konkurenční řešení ....................................................................................................................... 3
1.4.1
Tvorba webových stránek na zakázku .......................................................................... 3
1.4.2
Rezervační portály ................................................................................................................ 4
1.4.3
Online rezervační formuláře ............................................................................................. 4
1.5 2
3
Přínos práce ...................................................................................................................................... 4
Teoretická část .......................................................................................................................... 6 2.1
Historie databází ............................................................................................................................. 6
2.2
Průběh návrhu ................................................................................................................................. 7
2.2.1
Konceptuální model ............................................................................................................. 8
2.2.2
Logický model ......................................................................................................................... 8
2.2.3
Fyzický model ......................................................................................................................... 9
2.3
Analýza oblasti................................................................................................................................. 9
2.4
Analýza funkcionality................................................................................................................. 10
2.4.1
Bezpečnost ............................................................................................................................ 10
2.4.2
Výčet funkcí .......................................................................................................................... 11
2.4.3
Analýza entit......................................................................................................................... 12
2.4.4
Diagramy užití ..................................................................................................................... 16
Praktická část.......................................................................................................................... 21 3.1
Konceptuální model.................................................................................................................... 21
3.1.1
Vztahy mezi entitami ........................................................................................................ 21
3.1.2
Popis entit ............................................................................................................................. 23
3.2
Fyzický model ............................................................................................................................... 30
3.2.1
Referenční integrita........................................................................................................... 31
3.2.2
Pravomoci uživatelů .......................................................................................................... 31
3.2.3
Uložené procedury ............................................................................................................. 32
3.2.4
Normalizace názvů ............................................................................................................ 35
3.2.5
Vygenerování SQL kódu ................................................................................................... 35
Závěr .................................................................................................................................................. 36 Terminologický slovník .............................................................................................................. 38 Seznam použité literatury .......................................................................................................... 39 A. Seznam obrázků ..................................................................................................................... 40
B. Seznam tabulek ...................................................................................................................... 41 C.
Přílohy ....................................................................................................................................... 42 Konceptuální model ............................................................................................................................... 42 Fyzický model........................................................................................................................................... 43 Vygenerovaný SQL kód ......................................................................................................................... 44
1 Úvod 1.1 Vymezení tématu práce a důvod výběru tématu Hotelová zařízení stále v současné době používají nevyhovující, či velice náročný software pro správu jejich rezervací. Několikrát jsem se dokonce setkal i se situací, kdy hoteliéři využívají několik rozdílných aplikací, jednu pro rezervace, druhou pro tvorbu rozvrhových akcí, tkz. rezervačních štaflí a další pro záznam seznamu hostů. Některá hotelová zařízení dokonce využívají pro rezervace pokojů pouhý email, což má několik podstatných nevýhod, mezi které řadím především nepřehlednost a neefektivnost. Nepřehlednost je způsobena formátem emailových zpráv, které jsou seřazeny dle data přijetí a nikoli dle data příjezdu hostů. Neefektivita spočívá ve formátu samotného textu, který často nemá danou normu, jako formulář, ale jedná se pouze o prostý text. Zaměstnanci tudíž musí věnovat větší pozornost při zpracování rezervací, aby se vyhnuli případným chybám. Emailové schránky jsou také často podrobeny útokům spamů, které je nutné filtrovat, což je u větších ubytovacích zařízení časově náročné. A bez použití softwaru třetích stran není možné vést podrobné záznamy ať už o počtu rezervací, jejich výnosech nebo počtu hostů za určité období, či jiné statistiky. Z tohoto důvodu jsem se rozhodl vytvořit aplikaci, která by pokrývala a integrovala velkou část potřeb samotných hoteliérů a fungovala na bázi komunikačního prostředku se zákazníkem a zároveň vyhovovala legislativním požadavkům, které musí hotely splňovat. Vzhledem k mým zkušenostem s tvorbou webových stránek, pro mne bylo snadné zvolit koncept, kterým se vydám. Tímto konceptem je tvorba webové aplikace, která bude spojovat front-end, tedy pokrytí interakce s potencionálním zákazníkem a back-end, tedy parametrická administrace, jež dovolí vysokou míru customizace1. Vzhledem k použití webové platformy bude aplikace dostupná kdekoliv a bude sloužit jako informační základna pro její uživatele. Aplikace poté usnadní případnou realizaci u konkrétního zákazníka, neboť administrační rozhraní se bude shodovat u všech zákazníků a měnit se budou pouze stránky zařízení.
1.2 Vymezení cílů práce a metody pro jejich dosažení Mezi cíle mé práce jsem stanovil následující body:
1
Individuální úprava na míru zákazníka.
1
Analýza konkurenčních řešení.
Analýza funkcí, které se týkají provozování takovéto aplikace.
Navržení databáze a vytvoření fyzického modelu v prostředí MySQL.
Navržení GUI aplikace
Implementace aplikace ve webových technologiích.
Testování aplikace a databáze.
Samotná aplikace je založená především na technologiích HTML, CSS , PHP a Javascript. Kromě těchto technologií jsou využívány různorodé javascriptové knihovny třetích stran, například JQuery2, JQuery UI3 nebo Sly4. Tyto knihovny jsou běžně používány v praxi a urychlují vývoj aplikace. Jako základní předpoklad pro návrh i implementaci jsem stanovil použití v současné době nejnovější verze MySQL a PHP, tedy verze 5.6.12, respektive 5.4.16. Nejnovější verze technologií jsem se rozhodl použít především z důvodu, že MySQL do verze 5.0.2 nepodporuje skripty (TRIGGER). Plnohodnotná podpora přichází teprve od verze 5.5, kde jsou podporovány kromě skriptů i signály, které lze využít pro vyvolávání vlastních výjimek přímo z databáze. [1] [2] Konceptuální model bude vytvořen v profesionálním CASE nástroji PowerDesigner od společnosti Oracle. Tento model bude následně převeden na fyzický model a přenesen do aplikace MySQL Workbench, která kromě generování SQL kódu dále nabízí i dodatečné funkce. Tou je například možnost synchronizace databázového modelu přímo s reálnou databází bez nutnosti mazání všech záznamů. Lze tak provádět drobné změny rychle a efektivně,
což
urychluje
vývoj.
Po
vytvoření
bude
databáze
otestována
v
lokálním testovacím prostředí, kde budou odhaleny případné chyby, které musí být odstraněny. Po úspěšné tvorbě databáze dojde k samotnému návrhu aplikace respektující povahu databáze.
2
Knihovna JQuery rozšiřuje funkce javascriptu o možnosti animování HTML prvků. Dále zjednodušuje práci s elementy a událostmi. [23] K dispozici na adrese http://jquery.com/. 3 Knihovna JQuery UI přináší nové funkce a vlastní design formulářových prvků. Je k dispozici na adrese https://jqueryui.com/. 4 Knihovna Sly slouží pro lepší podporu navigačních prvků a jednostranné rolování. [22] K dispozici je na adrese http://darsa.in/sly/.
2
1.3 Struktura práce Práce je rozdělena do dvou navazujících celků. První část je věnována teoretickému základu a analýze informačních potřeb pro následující návrh databáze. Krátká část je též věnována historii databázových modelů a použitému relačnímu modelu. Také je zde uveden postup při návrhu databáze a objasněny pojmy normalizace a denormalizace. Druhou část poté věnuji samotnému návrhu databáze, na které je aplikace postavená. Praktická realizace se skládá z vytvoření konceptuálního modelu v CASE nástroji PowerDesigner, ze kterého je poté vygenerován fyzický model. Součástí kapitoly fyzického modelu uvádím příklady některých z uložených procedur (TRIGGER), postup při generování kódu, určení pravomocí a nastavení referenční integrity.
1.4 Konkurenční řešení V současné době existují na trhu tři řešení, které se věnují problematice rezervačního systému. Část z nich nabízí pouze určité prvky systému, které je nutno kombinovat s jinými produkty. Následující přehled popisuje nejpopulárnější řešení a jejich výhody a nevýhody. V rámci rešerše a hledání zdrojů jsem narazil také na závěrečnou práci, jež se věnuje podobnému tématu, avšak s různým výstupem. Práce Ing. Michala Vojtka [3], která se zabývá návrhem databáze pro hotelový rezervační systém. Tato práce postupuje stejně s návrhem databáze a pojímá aplikaci jako interakci se zákazníkem. Nicméně cílem této práce se stal pouze návrh databáze, jež je velice obsáhlý a zasahuje do několika oblastí. Má práce se zabývá užší částí a to pouze rezervačním systémem. Navíc si však dává za cíl tuto aplikaci sestrojit a připravit k nasazení. 1.4.1 Tvorba webových stránek na zakázku Nejrozšířenější volba u malých a středních ubytovacích zařízení. Webové stránky jsou vytvořeny přímo na zakázku dle potřeb a vzhledu zákazníka. Nabízejí však pouze základní funkce, mezi které patří rezervování jednotlivého pokoje, možnost úpravy ceníku a případně i určitá míra parametrizace. Ta se odvíjí především od schopností společnosti zodpovědné za implementaci. Vzhledem k faktu, že jsou tato řešení vytvářena na míru, jsou mnohem nákladnější než rezervační formuláře nebo portály. Ačkoli se jednotlivá řešení liší, mají společnou jednu věc, kvalita a způsob realizace je přímo úměrná vynaloženým finančním prostředkům.
3
Hlavní výhodou tohoto způsobu realizace jsou nízké náklady na správu po vysoké investici na začátku projektu. Tyto náklady se poté často pohybují pouze v nákladech na webový hosting a správu domény. To však nemusí být tak velkou výhodou, když zvážím, že životnost designu webové stránky se pohybuje přibližně okolo 2-3 let. Krátký životní cyklus stránek je způsoben rychlým vývojem prohlížečů a podporou nových technologií. Nevýhodou poté jsou případné změny současného návrhu. Například, pokud by se majitel zařízení rozhodl změnit účtování ceny pokoje dle typu za počet osob. V takovémto případě by bylo nutné kompletně přeformulovat návrh stránek i administrace, což by vyžadovalo dodatečné investice. 1.4.2 Rezervační portály Druhou skupinou jsou rezervační portály, jež nabízejí pouze funkcionalitu rezervací, tedy administračního rozhraní. Ubytovací zařízení tak přicházejí o návštěvníky svých stránek, které mají za cíl potencionálního zákazníka zaujmout. Webové stránky tak ztrácejí význam jako komunikační prostředek se zákazníkem. Zároveň je odstraněna originalita, kdy veškeré stránky hotelů, penzionů a jiných ubytovacích zařízení mají stejnou vizuální podobu. 1.4.3 Online rezervační formuláře Implementace se liší dle jednotlivých tvůrců. Testované řešení od společnosti Mevris5 nabízí uživatelsky příjemné prostředí s funkcemi, které pokrývají nejen rezervování, ale také další oblasti. Mezi tyto oblasti patří správa faktur, hostů, tvorba výkazů, správa ceníků a vedení statistik. Nicméně pokud se podíváme do administrace tohoto systému, nalezneme několik jeho nedokonalostí, mezi které především patří doménová integrita. Oproti tomuto řešení disponuje návrh této práce dále parametrickou správu webových stránek.
1.5 Přínos práce Cílem práce je vytvořit komplexní a parametrický systém, který bude sloužit jako základ pro jakékoli stránky ubytovacího zařízení. Jedná se tedy o typový aplikační software, kde front-end je vytvořen jako šablona, která se bude lišit u každé nové zakázky.
5
Český výrobce hotelového a recepčního systému. Více na http://www.mevris.cz/.
4
Aplikace je zaměřena především na střední a malé ubytovací zařízení, která mají roztříštěné prostředky pro správu rezervací, faktur a hostů. Aplikace by poté měla zajistit snazší práci pro hoteliéry, ale také pro zákazníky. V neposlední řadě přinese přehlednost, která spočívá v ukládání záznamů do jedné společné báze dat, ze které lze vytvářet případné statistiky.
5
2 Teoretická část 2.1 Historie databází Autorem relačního modelu je považován Edgar F. Codd [4], který v roce 1970 publikoval článek „A Relational Model of Data for Large Shared Data Banks“. Název modelu je odvozen z pojmu „relace“, který pochází z matematické disciplíny zvané teorie množin. Relační model byl velkým skokem dopředu, neboť odstranil velkou část nedokonalostí předchozích modelů a nově zavedl podporu vztahů M:N. Hlavní předností je možnost vytváření komplexních dotazů, jež se skládají z elementárních operací. Těchto základních operací je šest [5] a patří mezi ně selekce, sjednocení, kartézský součin, rozdíl, projekce a spojení. Dalším důležitým rysem je nezávislost databáze na fyzické architektuře. Díky této nezávislosti není obtížné použít stejnou logickou vrstvu databáze na různých databázových platformách, bez nutnosti starat se o skutečnou fyzickou implementaci. Charakter relačního modelu spočívá v propojení tabulek, které jsou chápány jako uspořádané n-tice prvků [4], jež obsahují vždy atributy a jejich hodnoty. Každý záznam je navíc označen jedinečným identifikátorem. Ten slouží jako index při vyhledávání a představuje primární klíč. Primárním klíčem může být jakýkoli atribut nabývající jedinečných hodnot, skupina atributů, popřípadě umělý primární klíč. Takovýto atribut zároveň nesmí nabývat hodnoty NULL [4]. Mimo primárního klíče, který může být pouze jeden pro každou tabulku, mohou tabulky obsahovat také cizí klíče. Ty představují odkaz na primární klíč v tabulce nadřazené. V reakci na vzrůstající popularitu relačních databází se v 80. letech začaly vytvářet první aplikace pro správu databází, tkz. RDBMS („Relational database management systém“). [4] Nové systémy pro správu relačních databází měli za cíl usnadnit práci s daty, ale také jejich sběr, ukládání, zpracování a správu. Vznikaly tak i komerční řešení, mezi jejichž zástupce patří systém Oracle od Oracle Corporation nebo systém DB2 od IBM. [4] Zároveň se v této době a i posléze v 90. letech začíná prosazovat nová technologie klient/server, díky které již není nutno využívat výhradně sálové databázové počítače a nastává éra osobních počítačů. Relační databáze však nejsou jediným typem, se kterým se lze ve světě databází setkat. V současné sobě se relační model používá již přes 25 let [4] a patří mezi nejrozšířenější. Tomu však tak nebylo vždy. Předchůdcem relačních databází byly databáze síťové. Ty se
6
skládají z uzlů a množinových struktur. Oproti staršímu hierarchickému modelu, který podporoval pouze vztahy 1:1 a 1:N, vztahy M:N byly problematické. [4] Síťový model částečně vyřešil tento problém zavedením struktury objektů, kde jednotlivé objekty mohou mít více rodičovských tabulek. Síťový model byl populární především v 70. letech 19. století. [5] V této době vytvářely velké společnosti jako Siemens AG nebo Computer Corporation [5] databázové produkty založené na bází Codasyl. Tento pojem představuje odbornou skupinu založenou v roce 1959 [6] složenou z firem působících v odvětví a americkou vládou. Jejich cílem bylo standardizovat programovací jazyky pro práci s databázemi. Typický příklad síťového modelu je znázorněn na následujícím obrázku (viz Obrázek 2.1.1).
Obrázek 2.1.1 - Diagram jednoduchého síťového modelu databáze
2.2 Průběh návrhu Nyní k samotnému návrhu, tomu Hernandez ve své knize přiřazuje velký význam. Na úvod zmíním jedno z Hernandezových [4] varování: „Databáze nemůže splnit požadavky na informace, které na ni organizace klade, pokud není správně strukturována.“. Hernandez navrhuje tři fáze při tvorbě databáze. První fází je provedení úvodní analýzy, kde je zjištěno co nejvíce informací o navrhované databázi a fungování současné databáze, ať už se jedná o elektronickou, či papírově vedenou. Následuje navržení konceptuálního modelu obsahující seznam tabulek, jejich polí, vytvoření primárních klíčů a určení vztahů mezi tabulkami. Po převedení na logický model jsou doplněny integritní omezení. Důležitost logického návrhu Hernandez [4] definuje jako: „Provedení logického návrhu zajišťuje, že vytvoříte smysluplná, metodická, jasná a informovaná rozhodnutí o struktuře vaší databáze.“. Tedy pro vytvoření kvalitní struktury databáze je třeba vytvořit kvalitní návrh. Poslední fází návrhu je fyzický model. Zde dochází k vytvoření datových struktur, jak byly definovány v logickém návrhu. Na fyzický návrh navazuje vytvoření aplikace nad touto databází, která slouží pro uživatele k přístupu a práci s daty v ní uložené.
7
Tradiční metody návrhu databáze mají dle Hernandeze [4] společné tři prvky. Mezi tyto prvky patří analýza požadavků, datové modelování a normalizace. 2.2.1 Konceptuální model Jedná se o informační základu databáze. V této fázi jde o získání co nejvíce relevantních dat týkající se provozu databáze a informace, které je třeba v databázi uchovávat. Cílem je především vytvoření obecné představy o fungování databáze a vytvoření možných návrhů řešení, kterých může být i více [7]. Tento model je nezávislý na databázovém prostředí a měl by vždy předcházet ostatním fázím návrhu. Často je vytvářen jedním z ER, či UML nástrojů, kde jsou vyznačeny entity, atributy entit a kardinality vztahů, jak je znázorněno dle Chenovy notace na obrázku 2.2.1.
Obrázek 2.2.1 - Diagram jednoduchého konceptuálního modelu v Chenově notaci
2.2.2 Logický model Jak uvádí Hernandez [4] nejdůležitější částí návrhu je konzistence, integrita a přesnost dat. To je zaručeno při úspěšném vytvoření logického modelu. Ten je stále nezávislý na platformě, avšak oproti konceptuálnímu modelu jsou již vytvořeny tabulky s příslušnými atributy a vazební entity určené vztahy. Cílem je tedy převést abstraktní konceptuální model na model obsahující přesně definované entity, atributy a vazby. V této fázi je též provedena normalizace databáze. 2.2.2.1 Normalizace Normalizace je tvorbou samotného autora relačního modelu Edgara F. Codda. Ten stanovil 3 základní normální formy (1NF, 2NF, 3NF), které má databáze splňovat. O
8
několik let později byla definována ještě čtvrtá normální forma nazvaná po jejích autorech, Boyce-Codd normální forma. Jedná se o proces, jak uvádí Palovská [8], kdy jsou z modelu odstraňovány údaje a fakta, které se zbytečně opakují. Druhý pohled na normalizaci nabízí Hernandez [4], který se odklání od tradičního normalizačního procesu a uvádí vlastní definici: „Normalizace je proces rozkladu velkých tabulek na menší, což slouží k eliminaci redundantních a duplicitních dat a k eliminaci budoucích problémů s vkládáním, rušením a aktualizací dat.“. Zjednodušeně lze na celou operaci pohlížet jako rozdělení více hodnotových polí na jednotlivá pole, určení jednoznačného unikátního klíče pro každou tabulku, jednotlivé atributy by měly popisovat danou tabulku a být závislé pouze na primárním klíči. 2.2.2.2 Denormalizace Přestože je snaha tabulky normalizovat, jsou případy kdy je vhodnějším přístupem opačný postup. [8] Jedná se především o snahu zvýšit výkon databáze za cenu ukládání redundantních dat. [9] To je způsobeno fyzickým ukládáním dat, která jsou roztříštěna po celém úložišti a databáze musí provést i několik desítek operací (selekty, spojování, atd.) než získá výslednou množinu prvků. To je samozřejmě náročné na výpočetní prostředky. Pokud tedy normalizace dosáhne takového stupně, kdy nelze návrh realizovat z technického hlediska, je nutné jej denormalizovat. 2.2.3 Fyzický model Poslední fází návrhu je převedení logického návrhu na fyzický model, který podléhá veškerým restrikcím databázové platformy, na které má být realizován. Ve fyzickém modelu jsou specifikovány pravidla pro referenční integritu. Dále jsou vytvořeny pohledy a byznys pravidla, které představují skripty (TRIGGER).
2.3 Analýza oblasti Nejprve bych rád věnoval chvíli pojmu „rezervační systém“, neboť existuje mnoho variant a alternativ chápání tohoto pojmu. Někteří jej pojímají velice ze široka jako kompletní systém pro podporu informačních potřeb celého ubytovacího zařízení od samotných rezervací, přes vystavování faktur, vedení a správu hostů až po recepční systém. V mém pojetí je rezervační systém brán jako modul informačního systému společnosti, který má možnost být propojen s dalšími možnými moduly, jako jsou například fakturace, nebo vystavování výkazů.
9
Z tohoto důvodu je pro mne stěžejním modulem právě rezervace. Je důležité, aby probíhaly svižně a intuitivně. Základním stavebním kamenem rezervace jsou však pokoje, bez nich by nebylo co rezervovat. Pokoje jsou rozděleny do různých kategorií pro snazší vyhledávání, přehlednost a jasnost pro zákazníka, který je na podobná značení zvyklý. Pokoje jsou u všech hotelových zařízení charakterizovány určitým názvem, popisem a obrázkovou fotogalerií, popřípadě referenční fotografií. Rezervace pokojů poté probíhá podobně jako nákup v elektronickém obchodě. Zákazník si vybere datum příjezdu a odjezdu a následně přejde na výběr pokoje, či pokojů. Zde jsou u různých konkurenčních systémů rozdíly mezi možnostmi rezervace jednoho anebo více pokojů najednou. Následuje vyplnění údajů o ubytovaných osobách a požadovaných službách. Proces končí vystavením dokladu a potvrzením o přijetí rezervace. Nyní přejdu od univerzálního pojetí rezervačního systému k pojetí tohoto systému v mé práci.
2.4 Analýza funkcionality Zvažovaná aplikace je rozdělena na dvě části. První částí je front-end, tedy rozhraní styku se zákazníkem. Zde hoteliéři předávají potencionálním zákazníkům informace o svých ubytovacích zařízení a nabízejí služby. Tato část musí být přehledná, vzhledově reprezentativní a snadná pro navigování. Druhou částí je back-end, který je součástí této práce. To je rozhraní pro zaměstnance ubytovacích zařízení, kde spravují příchozí objednávky, popřípadě upravují parametry aplikace, pakliže k tomu mají oprávnění. Cílem této práce je vytvořit back-end, též uváděný pod pojmem administrační rozhraní. Front-end aplikace je závislý na konkrétním zákazníkovi. 2.4.1 Bezpečnost Je důležité též zvážit zabezpečení vytvořené aplikace. Pro přihlašování je využíváno heslo, které se do databáze musí ukládat pomocí některé z hashovacích funkcí. V tomto konkrétním případě byla zvolena funkce SHA1, jež je v současnosti nejpoužívanější a považována za standard šifrování hesel ve webových aplikací i protokolech. [10] Zabezpečení hesla lze dále zvýšit pomocí takzvaného solení. Jedná se o proces, kdy je k heslu připojen řetězec dalších znaků předtím, než je použita hashovací funkce. Avšak solení samo o sobě nezaručuje, že heslo nelze dekódovat. Pouze znepříjemní cestu tím, že
10
uživatelé se stejným heslem nebudou mít nadále stejný hash a pakliže by došlo k prolomení jednoho hesla, ostatní stejná hesla zůstávají neprolomená. Druhou oblastí bezpečnosti je ukládání dat do databáze. Zde může dojít k SQL injection, což může mít za následek zveřejnění všech dat v databázi. Princip je následující. Pokud má aplikace jakýkoli vstup, který je využíván v dotazech na databázi, může být tento vstup zneužit pro vložení vlastního SQL kódu. Následující řádek kódu zobrazuje triviální dotaz do databáze bez ochrany proti injection v PHP. $db->query(“SELECT * FROM Tabulka WHERE Nazev = ’{$_POST[‘vstup’]}’“);
Problém nastává tehdy, když uživatel, respektive útočník zadá jako hodnotu vstupu vlastní SQL kód. Například „2 OR 1 = 1“. Tím, že je přidána druhá podmínka, která nabývá vždy hodnoty TRUE, budou vypsány všechny záznamy tabulky. Nebo v případě přihlašování lze využít stejný přístup, což by vedlo k umožnění přístupu do aplikace pro kohokoli. Tomu lze zabránit použitím funkce mysql real_escape_string a php funkce htmlspecialchars. $id_nazev = htmlspecialchars($db->real_escape_string($_POST[‘vstup’])); $db->query(“SELECT * FROM Tabulka WHERE Nazev = ‘{$id_nazev}’“);
2.4.2 Výčet funkcí Veškeré funkce popisované v této části jsou považovány z pohledu administrátora aplikace, který má nejvyšší oprávnění. Funkcionalita je rozdělena do několika následujících pilířů. Hlavním pilířem je recepce. Ta obsahuje veškeré rezervace, které zákazníci provedli a mohou zde být upravovány, mazány nebo vytvářeny nové v případě, že zákazník bude chtít rezervovat pokoj přes jiný, než standardní komunikační kanál. Například telefon nebo email. Za standardní komunikační prostředí je považována webová aplikace. Rezervace můžou být i hromadné, což je třeba v aplikaci zohlednit. Druhým prvkem recepce je správa pokojů. Zde jsou uvedeny veškeré kategorie a k ním příslušné pokoje. U všech pokojů je možné upravovat popis, přidávat, či odebírat vlastnosti, fotografie z fotogalerie, vybavení a jeho plánek. Další podkategorií jsou ceníky. Cena pokoje se odvíjí od typu pokoje. Jedna z nejdůležitějších podkategorií recepce jsou zákazníci, tedy hosté zařízení. Ti jsou rozděleni na dvě skupiny a to na právnické a fyzické osoby, respektive firmy a lidé. U hostů může docházet k vedení duplicitních záznamů, neboť uživatelé nejsou nuceni se registrovat a své údaje zadávají teprve při uskutečnění
11
rezervace. Posledním prvkem této části jsou faktury, které jsou automaticky vystavovány při každé rezervaci. Přes administrační rozhraní je možné faktury pouze upravovat, popřípadě odstranit společně s rezervací. Druhý pilíř aplikace tvoří nastavení, kde je možné customizovat parametry aplikace, spravovat uživatele a jejich pravomoci. Je zde též prostor pro úpravu textů ve front-end a nastavení zobrazení jednotlivých stránek. Poslední částí aplikace jsou statistiky, které vycházejí z vystavených faktur, uskutečněných rezervací, zaplněnosti ubytovacího zařízení a ostatních ukládaných dat. 2.4.3 Analýza entit Následující kapitola se věnuje rozboru jednotlivých entit databáze a jejich vlastnosti. Vlastnosti, které budou později zvoleny za atributy, jsou vyznačeny tučně. 2.4.3.1 Pokoj Základní entita rezervačního systému. Pokoj je charakterizován názvem, který patří mezi jeho elementární atributy. Kromě samotného názvu je dále pokoj upřesněn krátkým popisem. Tento popis slouží také pro hoteliéry kvůli případnému zpřesnění služeb. Například, pokud bude hoteliér potřebovat specifikovat, že sociální zařízení jsou společné pro více pokojů. Dále je u pokoje evidována jeho kapacita, tedy počet lůžek, a především pro přehlednost se u pokoje též uvádí jeho číslo, které nemusí být pouze číselného charakteru. Číslo pokoje zaručí, že se zaměstnanec používající aplikaci nemůže splést v případě, že více pokojů má stejný název. Detail pokoje obsahuje též odkaz na jeho půdorys. Tento odkaz není povinný a v případě, že nebude vyplněn se na stránkách ani zobrazovat nebude. 2.4.3.2 Kategorie Každé zařízení nabízí rozdílné ubytovací služby, to je nutné respektovat, a proto jsou pokoje rozděleny do kategorií. Kategorie je charakterizována výstižným a krátkým názvem. Kategorie jsou důležitým segmentem, který je využíván v aplikační logice pro nastavování ceníku. 2.4.3.3 Ceník Entita, která je zavedena z důvodu rozdílných cen služeb v různých obdobích. Ceny lze nastavit jak pro jednotlivé pokoje, tak také pro kategorii pokojů. Ceník však není platný stále, proto jsou zavedeny atributy začátek a konec platnosti. Ceníky by se samozřejmě 12
neměly překrývat, což je zajištěno procedurou (tkz. TRIGGER). Pro stanovení zodpovědnosti je k ceníku veden i jeho autor a pro potřebu kontroly též datum vytvoření. 2.4.3.4 Vybavení Pokoje mohou být různě vybaveny, proto je nutné tuto skutečnost reflektovat. U vybavení se eviduje pouze krátký název a popis, který však není povinný a ve finální aplikaci slouží jako vysvětlení, při najetí na název vybavení. 2.4.3.5 Služba Ke každému pokoji je třeba evidovat seznam služeb, které k němu přísluší. Mezi služby řadím například wi-fi, klimatizace, pokojová služba nebo připojení ke kabelové televizi. Služba má jednoznačný název a krátký popis. Dále je charakterizována ikonou, která je buďto v podobě nahraného obrázku (URL) nebo jako kód, viz Ikona. 2.4.3.6 Ikona Ikony jsou používány pro snazší rozpoznání a přehlednost. Ty jsou zadávány jako kód, který je stanovený dle písma FontAwesome6 nebo jako odkaz url. Kód odpovídá jednotlivým znakům tohoto písma (Například „fa-glass“ pro
(pohár)). Ikony jsou
též opařeny názvem, který slouží pro snazší navigaci při výběru ikon ke službě pro uživatele aplikace. 2.4.3.7 Galerie pokojů Každého zákazníka nejvíce upoutají hezké fotografie, nežli dlouhé texty. Galerie umožní hoteliérům prezentovat své pokoje v tom nejlepším světle. Galerie obsahuje seznam všech fotografií určených k danému pokoji. 2.4.3.8 Fotografie Jako u všech produktů i u rezervací je obrázek pokoje často důležitý moment při rozhodování zákazníka. Fotografie jsou ukládány do podsložek web hostingu a databáze na ně pouze odkazuje pomocí URL. Některé z fotografií budou sloužit jako ilustrace pokoje anebo jako fotografie zobrazující se v galerii daného pokoje, či ve webové galerii.
6
FontAwesome je vektorový typ písma, který nabízí širokou škálu ikon. Písmo je k dispozici na http://fontawesome.io/ a to jak pro nekomerční, tak i komerční účely. Více o licenci na http://fontawesome.io/license/.
13
2.4.3.9 Vlastnost Pakliže by stanovené atributy v entitě Pokoje nestačily, je možné přidat další atributy pomocí entity Vlastnost. Tato entita má dva atributy. Prvním z nich je název, který označuje typ dané vlastnosti (například „výhled“), zatímco druhý atribut je hodnota (například „na moře“). Několik pokojů může mít stejné vlastnosti, zatímco jiné pokoje mohou mít stejnou vlastnost, ale s různou hodnotou. 2.4.3.10 Uživatel Jedná se o webovou aplikaci, ke které má přístup kdokoli. Z tohoto důvodu je třeba omezit přístup za pomocí této entity. Uživateli jsou zaměstnanci, či majitelé ubytovacích zařízení. Kromě jména a příjmení, je evidován email, který je unikátní a slouží také jako přihlašovací jméno. Dále se uživatelé přihlašují do aplikace svým heslem, které je v rámci bezpečnosti v databázi zašifrováno šifrovacím algoritmem SHA1, neboť dosavadní MD5 je již považováno za zastaralé a nedostačující. [11] Mimo jiné u uživatele pro potřeby kontroly ukládáme datum a čas posledního přihlášení do aplikace. Posledním atributem jsou pravomoci. Jedná se o atribut obsahující textový řetězec ve formátu JSON, který obsahuje údaje, zda je daný uživatel oprávněný pro zobrazení, či editaci určitých stránek. 2.4.3.11 Článek Volitelný modul aplikace, který přináší možnost vytvářet vlastní články. U této entity se ukládají následující atributy: nadpis článku, samotný text, který může obsahovat speciální znaky, jež aplikace rozpozná (například obrázek nebo seznam). Kromě nadpisu a textu je dále evidován datum a čas přidání článku a jeho autor. 2.4.3.12 Kategorie Články lze rozdělit do kategorií. Každý článek je zařazen do přesně jedné kategorie, zatímco jednotlivé kategorie mohou obsahovat více článků. Kategorie obsahuje pouze jediný atribut, kterým je její název. Kategorie slouží pro uspořádání článků, větší přehlednost a snazší vyhledávání. 2.4.3.13 Štítek (Tag) Štítky jsou vytvořeny zejména pro účely optimalizace při vyhledávání. Je mnohem efektivnější vyhledávat články dle klíčových slov, jež představují štítky, než prohledávat všechny záznamy. Štítek obsahuje klíčové slovo.
14
2.4.3.14 Událost Volitelný modul aplikace. Přináší možnost správy událostí. U události se eviduje nadpis, text, který podobně jako v případě článku může obsahovat speciální symboly, které aplikace následně rozpozná, a dále datum, čas a místo konání. 2.4.3.15 Parametry Parametry představují různorodou skupinu atributů, které mají dvě shodné vlastnosti a těmi jsou, že každý parametr má název a hodnotu. Tyto parametry slouží pro nastavení aplikace. A to především front-end. Pro příklad se může jednat o nastavení textů nebo zobrazení určitých stránek. 2.4.3.16 Rezervace Zde jsou ukládány veškeré realizované rezervace přes webovou aplikaci. Rezervace ukládají informace o příjezdu a odjezdu ubytovaných, počet osob a případnou poznámku od zákazníka, pokud by měl speciální přání. Z důvodu kontroly se ukládá i datum provedení rezervace. Jako poslední atribut je zde uložen stav, který je uveden jako samostatná entita v podobě číselníku. 2.4.3.17 Zákazník Při rezervaci zákazníci zadávají své osobní údaje. Aplikace nabízí možnost rezervace pokojů bez nutnosti registrace zákazníků, což je pro zákazníky mnohem pohodlnější a snazší. Na druhou stranu to přináší určité nevýhody, mezi které patří vznik redundantních dat, nemožnost spravovat a prohlížet předchozí rezervace. U ubytovaných jsou zaznamenávány následující informace: jméno, příjmení, email, telefon, číslo dokladu a kontaktní adresa, která se skládá z ulice s číslem popisným, města a poštovního směrovacího čísla (PSČ). Zákazníkem může být firma nebo fyzická osoba, pakliže se jedná o firmu, je nutné dále připojit atributy identifikační číslo (IČ), daňové identifikační číslo (DIČ), název společnosti, jméno a příjmení kontaktní osoby. 2.4.3.18 Stravování Zde jsou uloženy různé typy stravování, které jsou ubytovaným nabízeny. Stravování je povinným atributem a jako výchozí hodnota stravování je nastavena „žádná penze“. Každý typ je opatřen unikátním názvem a cenou, která odpovídá jednomu dni.
15
2.4.3.19 Země Pro zachování konzistence jsou země evidovány jako číselník, který je naplněný před začátkem provozu aplikace. Číselník obsahuje všechny světové země. Konzistence je zajištěna tím, že zákazník vybírá ze seznamu předem vytvořených zemí a nemusí zemi vyplňovat ručně, kde by mohl případně udělat chybu. Zároveň se zákazníkovi usnadní samotný výběr země v aplikaci. U této entity jsou ukládány atributy název a zkratka. 2.4.3.20 Provozovatel Entita obsahující jeden záznam, který koresponduje s informacemi o ubytovacím zařízení. Tato data jsou následně využívána v ostatních entitách, například ve faktuře. Entita provozovatele obsahuje název zařízení, kontaktní adresu, identifikační číslo (IČ), emailovou adresu, telefon, jméno kontaktní osoby, GPS polohu, město, kde je zapsán v obchodním rejstříku a číslo složky, ve které je zapsán, logo v podobě URL adresy, elektronický podpis, název banky, která je uvedena při vystavení faktury, účet provozovatele, opět uveden na fakturách a další nepovinné atributy IBAN a SWIFT, týkajícího se bezhotovostních plateb ze zahraničí. 2.4.3.21 Faktura Provozovatelé potřebují zajisté k přijatým rezervacím vystavit faktury, které by mohli zákazníkovi odeslat. Cílem je centralizovat funkce, z tohoto důvodu jsou jako dodatečná funkcionalita přidány též faktury ve zjednodušené podobě. Faktura čerpá mnoho dat z ostatních entit a jsou u ní evidovány pouze výlučné hodnoty jednotlivých faktur. Těmito hodnotami jsou variabilní symbol, datum vystavení, datum splatnosti a celková cena služeb. 2.4.4 Diagramy užití Ačkoli je přístup do administračního rozhraní omezen, stále do něho bude mít přístup několik skupin uživatelů. Pro stanovení pravomocí je důležité určit, které informační zdroje jim budou k dispozici a které operace budou moci provádět. V následujících podkapitolách je vždy uveden názorný obrázek s výpisem funkcí. 2.4.4.1 Administrátor Tento účet patří majiteli ubytovacího zařízení, který má nejvyšší pravomoc. Tato skupina uživatelů může upravovat veškeré parametry, vytvářet nové rezervace a měnit části systému. Administrátor je tedy především zodpovědný za nastavení aplikace a
16
zobrazované informace. Administrátor má zároveň přístup ke všem tabulkám v databázi a ke všem datům. Obrázek 2.4.1 zobrazuje kompletní seznam funkcí.
Obrázek 2.4.1 - Use case diagram pro administrátora aplikace
V následující tabulce (viz. Tabulka 2.4.1) je k dispozici kompletní seznam všech případů užití pro skupinu uživatelů nazvanou jako „Administrátoři“. U každé akce je uveden krátký komentář. Tabulka 2.4.1 - Podrobný popis diagramu administrátora
Číslo
Název akce
1
Zobrazení parametrů
2
Úprava parametrů
Komentář Administrátor si může zobrazit soupis všech parametrů a jejich hodnot. Parametry může měnit nebo přenastavit, ale nemůže je mazat, či vytvářet. Administrátor spravuje veškeré uživatele aplikace. Může
3
Tvorba, úprava, mazání uživatelů
jim přidělovat pravomoci, vytvářet nové uživatelské účty, upravovat je nebo deaktivovat, popřípadě úplně smazat.
4
Zobrazení uživatelů
Dotaz na zobrazení všech uživatelů aplikace. Administrátor může měnit parametry týkající se entity
5
Úprava vlastností provozovatele
Provozovatele. Zde může změnit například název zařízení, či číslo účtu.
6
Zobrazení vlastností provozovatele
Dotaz na výpis informací o provozovateli. Administrátor může vytvářet nové články, upravovat je,
7
Tvorba, úprava, mazání článků
8
Zobrazení článků
9
Tvorba, úprava, mazání událostí
10
Zobrazení událostí
Administrátor si může zobrazit události.
11
Tvorba, úprava, mazání ceníků
Administrátor může spravovat ceníky.
12
Zobrazení ceníků
Administrátor může dát dotaz na výpis všech ceníků.
popřípadě je i mazat. Administrátor si může zobrazit články. Administrátor může vytvářet události, měnit je nebo je mazat.
17
13
Tvorba, úprava, mazání služeb
14
Zobrazení služeb
15
Administrátor může přidávat nové služby, měnit stávající nebo mazat ty, které již neposkytují. Administrátor může zobrazit seznam všech poskytovaných služeb.
Tvorba, úprava, mazání vlastností
Administrátor nastavuje informace o pokojích. Vlastnosti
pokojů
může přidávat, upravovat, či smazat. Administrátor může dát dotaz na vypsání vlastností
16
Zobrazení vlastností pokojů
17
Tvorba, úprava, mazání faktur
18
Zobrazení faktur
19
Tvorba, úprava, mazání ceníků
20
Zobrazení ceníků
21
Tvorba, úprava, mazání pokojů
22
Zobrazení pokojů
Administrátor si může vypsat seznam pokojů.
Nahrání, úprava, mazání
Administrátor nahrává fotografie, může upravit jejich
fotografií
vlastnosti a také je může odstranit.
23 24 25 26
Zobrazení fotografií
pokoje. Administrátor může vytvářet, upravovat, popřípadě mazat faktury. Administrátor si může zobrazit veškeré faktury. Administrátor vytváří ceníky, může je upravit a také je může odstranit. Administrátor si může vypsat seznam ceníků. Administrátor vytváří pokoje, může je upravit a také je může odstranit.
Administrátor může dát dotaz na vypsání všech fotografií.
Nahrání, úprava, mazání
Administrátor vytváří rezervace, může je upravit a také
rezervací
je může odstranit.
Zobrazení rezervací
Administrátor si může vypsat seznam rezervací.
2.4.4.2 Zaměstnanec Aby bylo určeno, ke kterým datům potřebuje mít zaměstnanec přístup, je třeba nejprve vytvořit USE CASE diagram pro používání aplikace. Z něho je vidět, že zaměstnanec využívá aplikaci k tvorbě nových rezervací a jejich správě (upravování a mazání), dále vytváří a spravuje faktury, články, události a služby. Na druhou stranu však nemá přístup k uživatelským účtům nebo k úpravě ceníků. Následující obrázek 2.4.2 zobrazuje seznam funkcí dostupných zaměstnancům, které jsou dále popsány v tabulce 2.4.2.
18
Obrázek 2.4.2 - Use case diagram pro zaměstnance aplikace Tabulka 2.4.2 - Podrobný popis diagramu zaměstnanců
Číslo
Název akce
1
Tvorba, úprava, mazání článků
2
Zobrazení článků
3
Tvorba, úprava, mazání událostí
4
Zobrazení událostí
Zaměstnanec si může zobrazit události.
5
Zobrazení ceníků
Zaměstnanec může dát dotaz na výpis všech ceníků.
6
Tvorba, úprava, mazání služeb
7
Zobrazení služeb
8
Komentář Zaměstnanec může vytvářet nové články, upravovat je, popřípadě je i mazat. Zaměstnanec si může zobrazit články. Zaměstnanec může vytvářet události, měnit je nebo je mazat.
Zaměstnanec může přidávat nové služby, měnit stávající nebo mazat ty, které již neposkytují. Zaměstnanec může zobrazit seznam všech poskytovaných služeb.
Tvorba, úprava, mazání vlastností
Zaměstnanec nastavuje informace o pokojích. Vlastnosti
pokojů
může přidávat, upravovat, či smazat. Zaměstnanec může dát dotaz na vypsání vlastností
9
Zobrazení vlastností pokojů
10
Tvorba, úprava, mazání faktur
11
Zobrazení faktur
Zaměstnanec si může zobrazit veškeré faktury.
Nahrání, úprava, mazání
Zaměstnanec vytváří rezervace, může je upravit a také je
rezervací
může odstranit.
Zobrazení rezervací
Zaměstnanec si může vypsat seznam rezervací.
12 13
pokoje. Zaměstnanec může vytvářet, upravovat, popřípadě mazat faktury.
2.4.4.3 Účetní Uživatel s tímto účtem se může přihlásit do aplikace a pouze si prohlížet vystavené faktury (Viz obrázek 2.4.3). Jedná se o externího pracovníka ubytovacího zařízení. V pravomoci účetní není jakkoli měnit, vytvářet nebo mazat faktury.
19
Obrázek 2.4.3 - Use case diagram uživatelské skupiny účetní Tabulka 2.4.3 - Popis případu užití pro účetní
Číslo
Název akce
Komentář
1
Zobrazení faktur
Účetní může dát dotaz na výpis vystavených faktur.
20
3 Praktická část 3.1 Konceptuální model Tato část práce se věnuje návrhu konceptuálnímu modelu databáze, který vychází z detailního popisu funkcí a prvků v předchozí kapitole. V této kapitole jsou uvedeny veškeré entity spolu s jejich atributy, které obsahují krátký komentář popisující data, která se do nich ukládají. Dále jsou specifikovány důležité netriviální vztahy mezi entitami s krátkým popisem vysvětlující typ a důvod vztahu. U popisu entit představuje použitý symbol (
) primární klíč. Tento model byl vytvořen v CASE nástroji
PoweDesigner od společnosti Oracle. Celý konceptuální model je poté k dispozici v příloze (viz. Konceptuální model). 3.1.1 Vztahy mezi entitami Tato podkapitola objasňuje některé vztahy, které nejsou triviální a vyžadují zdůvodnění. U těchto vztahů je specifikována jejich podstata a použitý typ. Typ vztahu může být u relačních databází 1:1, 1:N, či M:N, jak již bylo uvedeno v teoretické části práce. Vztahy jsou dále popsány krátkým názvem a pro zobrazení vztahů je použita notace E/R + Merise. 3.1.1.1 Pokoje – Vlastnosti Pokoje můžou být v případě nutnosti více upřesněny pomocí dodatečných vlastností. Ty mají některé pokoje společné. Ačkoli mohou mít stejné vlastnosti, jejich hodnota se může lišit. Počet vlastností pro pokoj není shora omezen. Například jako vlastnost lze považovat rozměr pokoje, kde se hodnota může u jednotlivých pokojů lišit, ale některé pokoje ji mají stejnou. Na obrázku 3.1.1 je zobrazena část modelu věnující se zmíněnému vztahu.
Obrázek 3.1.1 - Vztah mezi entitami Pokoje a Vlastnosti
3.1.1.2 Pokoje – Vybavení Pokoje kromě vlastností disponují určitým vybavením. To se může u různých pokojů opakovat. Pokoj může mít 0 až N kusů vybavení, a proto byl zvolen vztah M:N. Vybavení
21
slouží pro užší charakterizaci pokojů. Na obrázku 3.1.2 je zobrazena část modelu věnující se zmíněnému vztahu.
Obrázek 3.1.2 - Vztah mezi entitou Pokoje a Vybavení
3.1.1.3 Zákazníci – Rezervace Každá rezervace musí mít minimálně jednoho zákazníka. Zatímco zákazník může rezervovat maximálně jednu rezervaci. Tento vztah je záměrný, neboť aplikace nevyžaduje registraci zákazníků, což znamená, že se zákazník uloží vždy při vytvoření nové rezervace. Z tohoto důvodu nemůže mít nikdy více než jednu rezervaci, která má 1 až N rezervovaných pokojů. Druhý vztah „je odběratelem“ se vztahuje k určení odběratele vytvořené rezervace. Na obrázku 3.1.3 je zobrazena část modelu věnující se zmíněnému vztahu.
Obrázek 3.1.3 - Vztah mezi Rezervacemi a Zákazníky
3.1.1.4 Zákazníci – Hosté – Firmy Zákazníkem, který si zarezervuje pokoj, či pokoje může být buďto fyzická nebo právnická osoba, respektive host nebo firma. Tato dědičnost je výlučná znamenající, že jeden zákazník může být pouze jedním ze zmíněných typů. To je zaručeno dodatečným atributem „Typ“, jež je součástí primárního klíče.
22
Obrázek 3.1.4 - Dědičnost mezi Zákazníky, Hosty a Firmami
3.1.2 Popis entit V této kapitole jsou popsány všechny entity vyskytující se v návrhu. Datové typy jsou určeny zkratkou, jejíž celý název je uveden v terminologickém slovníku. Číslo v závorce u datového typu poté představuje maximální povolenou délku řetězce pro daný atribut. Názvy atributů jsou záměrně bez diakritiky pro snazší práci s databází a pro případnému zabránění nečitelnosti názvů, pokud použitý MySQL server nepodporuje české znaky. 3.1.2.1 Pokoje Tabulka 3.1.1 - Datové atributy u entity „Pokoje“
Název atributu
Datový typ
idPokoje
INT
Nazev
VARCHAR (64)
Popis
VARCHAR (256)
Cislo
VARCHAR (8)
Kapacita
INT
Plan
TEXT
Komentář Umělý primární klíč a zároveň identifikátor entity. Název pokoje, jež se zobrazuje u detailu pokoje. Popis pokoje s dodatečnými informacemi, jež nejsou zobrazeny ve službách nebo vlastnostech. Číslo pokoje uvedené na jeho dveřích. Počet lůžek v daném pokoji. URL odkaz na plánek pokoje. Nepovinný atribut.
3.1.2.2 Kategorie pokojů Tabulka 3.1.2 - Datové atributy u entity „Kategorie pokojů“
Název atributu
Datový typ
idKategorie
INT
Nazev
VARCHAR (64)
Komentář Umělý primární klíč a zároveň identifikátor entity. Název kategorie, jež se zobrazuje u detailu pokoje i v přehledu pokojů. 23
3.1.2.3 Ceníky Tabulka 3.1.3 - Datové atributy u entity „Ceníky“
Název atributu
Datový typ
idCeniku
INT
Nazev
VARCHAR (64)
PocatekPlatnosti
DATE
KonecPlatnosti
DATE
Vytvoreno
TIMESTAMP
Komentář Umělý primární klíč a zároveň identifikátor entity. Název ceníku, který slouží pro potřeby interních pracovníků. Tento atribut není zobrazován na stránkách ubytovacího zařízení. Datum počátku platnosti daného ceníku. Datum konce platnosti ceníku, který platí včetně do uvedeného koncového data. Datum poslední úpravy ceníku, popřípadě datum vytvoření ceníku.
3.1.2.4 Ceny pokojů Tabulka 3.1.4 - Datové atributy u vazební entity „Ceny pokojů“
Název atributu
Datový typ
Cena
DECIMAL (14,2)
Komentář Cena konkrétního pokoje. Cena se vztahuje k ceníku, proto se můžou dle jednotlivých ceníků lišit i ceny pokojů.
3.1.2.5 Vybavení Tabulka 3.1.5 - Datové atributy u entity „Vybavení“
Název atributu
Datový typ
idVybaveni
INT
Nazev
VARCHAR (64)
Popis
VARCHAR (256)
Komentář Umělý primární klíč a zároveň identifikátor entity. Název vybavení. Popis vybavení sloužící jako nápověda a zpřesnění.
3.1.2.6 Služby Tabulka 3.1.6 - Datové atributy u entity „Služby“
Název atributu
Datový typ
idSluzby
INT
Komentář Umělý primární klíč a zároveň identifikátor entity. 24
Nazev Popis
VARCHAR (64) VARCHAR (256)
Název služby. Popis služby sloužící k upřesnění.
3.1.2.7 Ikony Tabulka 3.1.7 - Datové atributy u entity „Ikony“
Název atributu
Datový typ
idIkony
INT
Kod
BOOLEAN
Hodnota
TEXT
Komentář Umělý primární klíč a zároveň identifikátor entity. Atribut určující zda je v poli „Hodnota“ uložen odkaz URL, či hodnota kódu. Odkaz URL, či hodnota kódu.
3.1.2.8 Fotografie Tabulka 3.1.8 - Datové atributy u entity „Fotografie“
Název atributu
Datový typ
idFotografie
INT
Nadpis Popis URL
VARCHAR (64) VARCHAR (256) TEXT
Typ
VARCHAR (8)
Komentář Umělý primární klíč a zároveň identifikátor entity. Nadpis fotografie. Popis fotografie. URL adresa s fotografií. Formát fotografie. Zde se ukládá přípona.
3.1.2.9 Vlastnosti Tabulka 3.1.9 - Datové atributy u entity „Vlastnosti“
Název atributu
Datový typ
idVlastnosti
INT
Nazev
VARCHAR (64)
Komentář Umělý primární klíč a zároveň identifikátor entity. Název vlastnosti, který se zobrazuje v detailu pokoje.
3.1.2.10 Seznam vlastností Tabulka 3.1.10 - Datové atributy u vazební entity „Seznam vlastností“
Název atributu
Datový typ
Hodnota
VARCHAR (64)
Komentář Hodnota vlastnosti, která zobrazuje v detailu pokoje.
25
se
3.1.2.11 Uživatelé Tabulka 3.1.11 - Datové atributy u entity „Uživatelé“
Název atributu
Datový typ
idUzivatele
INT
Email
VARCHAR (256)
Jmeno Prijmeni
VARCHAR (64) VARCHAR (64)
Heslo
VARCHAR (64)
Prihlaseni
DATETIME
Session
VARCHAR (256)
Aktivni
BOOLEAN
Komentář Umělý primární klíč a zároveň identifikátor entity. Přihlašovací jméno do administrace aplikace. Jméno majitele nebo zaměstnance. Příjmení uživatele aplikace. Heslo, sloužící pro vstup do administrace. Datum a čas posledního přihlášení. Slouží pro potřeby aplikace a pro kontrolu. Interní proměnná aplikace. Slouží pro kontrolu, zda je uživatel přihlášen. Určuje, zda je s daným uživatelským účtem možné se přihlásit do aplikace.
3.1.2.12 Skupina uživatelů Tabulka 3.1.12 - Datové atributy u entity „Skupiny uživatelů“
Název atributu
Datový typ
idSkupiny
INT
Nazev
VARCHAR (64)
Pravomoci
TEXT
Komentář Umělý primární klíč a zároveň identifikátor entity. Název skupiny uživatelů. Řetězec textu ve formátu JSON určující pravomoci k nahlížení do jednotlivých částí administrace.
3.1.2.13 Články Tabulka 3.1.13 - Datové atributy u entity „Články“
Název atributu
Datový typ
idClanku
INT
Nadpis
VARCHAR (64)
Text
TEXT
Pridano
TIMESTAMP
Komentář Umělý primární klíč a zároveň identifikátor entity. Nadpis článku. Text článku obsahující speciální tagy, které jsou rozpoznány aplikací. Datum a čas přidání článku.
26
3.1.2.14 Kategorie článků Tabulka 3.1.14 - Datové atributy u entity „Kategorie článků“
Název atributu
Datový typ
idKategorie
INT
Nazev
VARCHAR (64)
Komentář Umělý primární klíč a zároveň identifikátor entity. Název kategorie.
3.1.2.15 Štítky (Tagy) Tabulka 3.1.15 - Datové atributy u entity „Tagy“
Název atributu
Datový typ
idTagu
INT
Nazev
VARCHAR (64)
Komentář Umělý primární klíč a zároveň identifikátor entity. Název tagu.
3.1.2.16 Události Tabulka 3.1.16 - Datové atributy u entity „Události“
Název atributu
Datový typ
idUdalosti
INT
Nazev Popis Datum CasZacatku CasUkonceni Lokace
VARCHAR (64) VARCHAR (256) DATE TIME TIME VARCHAR (128)
Vytvoreno
TIMESTAMP
Komentář Umělý primární klíč a zároveň identifikátor entity. Název události. Popis události. Datum konání akce. Čas, kdy akce začíná. Čas, kdy akce končí. Místo konání. Datum a čas vytvoření události. Slouží pro kontrolu.
3.1.2.17 Parametry Tabulka 3.1.17 - Datové atributy u entity „Parametry“
Název atributu
Datový typ
idParametru
INT
Nazev
VARCHAR (64)
Hodnota
TEXT
Komentář Umělý primární klíč a zároveň identifikátor entity. Název parametru, který je unikátní. Hodnota parametru. Parametry slouží pro nastavení aplikace.
27
3.1.2.18 Rezervace Tabulka 3.1.18 - Datové atributy u entity „Rezervace“
Název atributu
Datový typ
idRezervace
INT
Od Do Rezervovano
DATE DATE TIMESTAMP
Komentář Umělý primární klíč a zároveň identifikátor entity. Datum příjezdu hostů. Datum ukončení pobytu. Datum a čas podání rezervace.
3.1.2.19 Zákazníci Tabulka 3.1.19 - Datové atributy u entity "Zákazníci"
Název atributu
Datový typ
idZakaznika
INT
Typ
ENUM
Email
VARCHAR (256)
Predvolba
VARCHAR (8)
Telefon
INT
Město
VARCHAR (128)
Adresa
VARCHAR (128)
PSC
INT
Komentář Umělý primární klíč a zároveň identifikátor entity. Výčtový typ obsahující dvě povolené hodnoty. Ty jsou „Host“ a „Firma“. Spolu s idZakaznika tvoří složený primární klíč. Kontaktní email zákazníka. Slouží pro odeslání faktury. Nepovinný atribut. Mezinárodní předvolba telefonního čísla. Nepovinný atribut. Telefonní číslo zákazníka. Město trvalé adresy zákazníka. Ulice a číslo popisné trvalé adresy zákazníka. PSČ trvalé adresy zákazníka.
3.1.2.20 Hosté Tabulka 3.1.20 – Datové atributy u entity „Hosté“
Název atributu
Datový typ
Komentář
Jmeno
VARCHAR (64)
Jméno hosta.
Prijmeni
VARCHAR (64)
CisloDokladu
VARCHAR (64)
Příjmení hosta. Číslo občanského dokladu, řidičského průkazu nebo pasu.
3.1.2.21 Firmy Tabulka 3.1.21 – Datové atributy u entity „Firmy“
Název atributu
Datový typ
Komentář
28
IC
VARCHAR (16)
Identifikační číslo společnosti.
DIC
VARCHAR (32)
Daňové identifikační číslo společnosti.
Nazev
VARCHAR (64)
Název firmy zadávající rezervaci.
Jmeno
VARCHAR (64)
Prijmeni
VARCHAR (64)
Jméno kontaktní osoby pro danou firmu. Příjmení kontaktní osoby pro danou firmu.
3.1.2.22 Stravování Tabulka 3.1.22 - Datové atributy u entity „Stravování“
Název atributu
Datový typ
idStravovani
INT
Typ Cena
VARCHAR (32) DECIMAL (14,2)
Komentář Umělý primární klíč a zároveň identifikátor entity. Typ nabízeného typu stravování. Cena nabízeného typu stravování.
3.1.2.23 Země Tabulka 3.1.23 - Datové atributy u entity „Země“
Název atributu
Datový typ
idZeme
INT
Nazev Zkratka
VARCHAR (64) VARCHAR (8)
Komentář Umělý primární klíč a zároveň identifikátor entity. Celý název státu, či země. Zkratka státu.
3.1.2.24 Provozovatelé Tabulka 3.1.24 - Datové atributy u entity „Provozovatelé“
Název atributu
Datový typ
idProvozovatele
INT
Nazev Město Adresa PSC IC
VARCHAR (128) VARCHAR (128) VARCHAR (128) INT VARCHAR (16)
Email
VARCHAR (256)
Predvolba Telefon Mobil
VARCHAR (8) INT INT
Komentář Umělý primární klíč a zároveň identifikátor entity. Název zařízení. Kde se ubytovací zařízení nachází. Ulice a číslo popisné objektu. Poštovní směrovací číslo. Identifikační číslo. Emailová adresa pro podání rezervací přes email a pro kontaktování zařízení. Mezinárodní číslo předvolby. Číslo pevné linky. Číslo mobilního telefonu. 29
KontaktniOsoba
VARCHAR (128)
Poloha
VARCHAR (64)
Zapis
VARCHAR (64)
Slozka
VARCHAR (64)
Logo
TEXT
Banka
VARCHAR (128)
Ucet
VARCHAR (32)
IBAN
VARCHAR (32)
SWIFT
VARCHAR (32)
Podpis
TEXT
UvodniObrazek
TEXT
Splatnost
INT
Jméno a příjmení kontaktní osoby. GPS lokace polohy ubytovacího zařízení. Město, ve kterém je proveden zápis do obchodního rejstříku. Spisová složka v obchodním rejstříku. URL odkaz na logo ubytovacího zařízení. Název banky, u které má provozovatel účet. Tento údaj je uveden na faktuře. Číslo účtu včetně předčíslí. Slouží pro vystavování faktur. Kód pro mezinárodní platby. Nepovinný údaj. Kód pro mezinárodní platby. Nepovinný údaj. URL adresa elektronického podpisu, který je uveden v zápatí faktury. URL adresa fotografie zobrazené na úvodní straně. Nastavení data splatnosti faktury od jejího vystavení. Počet dní.
3.1.2.25 Faktury Tabulka 3.1.25 - Datové atributy u entity „Faktury“
Název atributu
Datový typ
idFaktury
INT
Vystaveno Splatnost VariabilniSymbol CelkovaCena
DATE DATE INT DECIMAL (14,2)
Komentář Umělý primární klíč a zároveň identifikátor entity. Datum vystavení faktury. Datum splatnosti faktury. Variabilní symbol platby. Celková cena poskytnutých služeb.
3.2 Fyzický model Následující kapitola se zabývá vytvořením fyzického modelu, který vychází ze základů již detailně popsaného konceptuálního modelu. Fyzický model by však měl být vygenerován z logického modelu, jak bylo popsáno v postupu při návrhu databáze, ten však může být při práci s CASE nástrojem PowerDesigner přeskočen, neboť veškeré generování tabulek a úpravy lze provést i přímo ve fyzickém modelu.
30
3.2.1 Referenční integrita Platforma MySQL nabízí čtyři možné typy referenční integrity. A to za předpokladu použití metody ukládání InnoDB. Tato metoda pro ukládání na rozdíl od původního způsobu MyISAM podporuje cizí klíče a transakční zpracování dat. [12] Tyto čtyři referenční integrity fakticky odpovídají pouze třem typům. Jedná se integritu typu NO ACTION, SET NULL, RESCTRICT a CASCADE. Typ, který nemá faktickou implementaci je NO ACTION. Jedná se pouze o použití výchozí akce, která je v databázi nastavena. Ve většině případů se jedná o typ RESTRICT. Omezení typu SET NULL určuje akci, která nastane při smazání, či aktualizaci záznamu z tabulky rodiče. Vzhledem k tomu, že primární klíče tabulek jsou umělými atributy, není nutné uvažovat nad referenční integritou při aktualizaci, protože se změny primárních klíčů nepředpokládají. Z tohoto důvodu je integrita nastavena na RESTRICT. Co se však stane s hodnotou cizího klíče, pokud je smazán záznam z tabulky rodiče a integrita je nastavena na SET NULL? Pokud je možné přiřadit cizímu klíči hodnotu NULL, je mu tato hodnota přidělena a řádek úspěšně smazán. V opačném případě dojde k vyvolání výjimky referenční integrity. Druhým typem je hodnota RESTRICT. Tento typ nepovolí smazání záznamu z rodičovské tabulky, pokud tento záznam má jeden nebo více přiřazených záznamů v tabulce potomka. Poslední z referenčních integrit je typ CASCADE. Tento typ funguje na principu smazání všech záznamů z tabulky potomka, které odkazují na záznam rodiče, který je mazán. V určitých případech tedy dokáže uspořit čas, ale je důležité určit, zda typ CASCADE je pro daný vztah vhodný. Není nic horšího, než při smazání jednoho záznamu je smazána polovina databáze díky referenčním integritám. 3.2.2 Pravomoci uživatelů V databázi MySQL lze nastavit pravomoci uživatelům pro přístup a práci s tabulkami databáze. Pravomoc lze nastavit příkazem GRANT. Příkladem takového příkazu může být následující. GRANT ALL ON mydb.* TO ‘root‘@‘localhost‘;
Druhý příkaz slouží pro odebrání pravomoci. REVOKE DELETE ON mydb.* TO ‘root‘@‘localhost‘;
31
Takovýto příkaz GRANT přiřadí všechny pravomoci uživateli ‚root‘ a to pro všechny tabulky nacházející se v databázi „mydb“. Místo ALL lze také specifikovat pouze určité příkazy, například SELECT nebo INSERT. Vzhledem k propojení databáze s aplikací postavené na technologii PHP a implementaci vlastních uživatelských definic, jsou tyto příkazy pouze duplicitou aplikační logiky. Druhým důvodem proč přenechat pravomoci přístupu a práce s databází na aplikaci namísto databáze je fakt, že reálné nasazení aplikace nemůže spoléhat na webhostingu, který bude podporovat více uživatelských skupin. Přesto však uvedu SQL příkazy, které odpovídají USE CASE diagramům popsaným v kapitole analýza funkcionality. První uživatelskou skupinu, kterou jsem uvedl, byli administrátoři. Ti mají přístup k veškerým datům a mohou provádět veškeré operace. CREATE USER 'administrator'@'localhost' IDENTIFIED BY 'heslo'; GRANT ALL ON rezervacni_system_db.* TO 'administrator'@'localhost';
Druhou skupinou jsou zaměstnanci. Následuje SQL kód, který představuje vygenerování skupiny uživatelů a přiřazení příslušných pravomocí. CREATE USER 'zamestnanec'@'localhost' IDENTIFIED BY 'heslo'; GRANT ALL ON rezervacni_system_db.Clanky TO ' zamestnanec'@'localhost'; GRANT ALL ON rezervacni_system_db.Udalosti TO ' zamestnanec'@'localhost'; GRANT SELECT ON rezervacni_system_db.Ceniky TO ' zamestnanec'@'localhost'; GRANT ALL ON rezervacni_system_db.Rezervace TO ' zamestnanec'@'localhost'; GRANT ALL ON rezervacni_system_db.Faktury TO ' zamestnanec'@'localhost'; GRANT SELECT ON rezervacni_system_db.Pokoje TO ' zamestnanec'@'localhost'; GRANT ALL ON rezervacni_system_db.Vlastnosti TO ' zamestnanec'@'localhost';
Poslední skupinou jsou externí zaměstnanci, tedy účetní. CREATE USER 'ucetni'@'localhost' IDENTIFIED BY 'heslo'; GRANT SELECT ON rezervacni_system_db.Faktury TO ' ucetni'@'localhost';
3.2.3 Uložené procedury Od verze 5.5 plně podporují databáze MySQL použití procedur (TRIGGER) společně s možností vyvolávání vlastních výjimek za pomocí klauzule SIGNAL SQLSTATE. [13] Přesto stále existují reálné důvody proč na používání procedur výhradně nespoléhat. Těmito důvody jsou jednak implementace procedur databázovým systému MySQL, kde do verze 5.5 nelze snadno vyvolávat vlastní výjimky a je nutné vytvářet funkce, které
32
využívají neplatný SQL příkaz pro vyvolání výjimky. Dále absolutní absencí procedur, pokud web hosting disponuje pouze starší verzí MySQL, což je v praxi běžné. Proto je důležité vytvořit zálohu v podobě aplikační logiky. Následující procedura je vytvořena pro tabulku Pokoje, kde je potřeba zkontrolovat, zda zadávaná kapacita je v rozsahu povolených hodnot. První řádek určuje databázi, ve které se nachází tabulka, ke které má být TRIGGER přiřazen. Dále následuje DELIMITER, tato funkce slouží ke změně oddělovače mezi jednotlivými skripty. Pokud by nebyl DELIMITER změněn na jiný zástupný znak, který není používán v proceduře, systém MySQL by jej bral jako několik separátních příkazů, které vždy končí středníkem. Po nastavení oddělovače, je nastaveno, kdy se má skript spustit. Pro kontrolu kapacity je třeba zkontrolovat vkládanou hodnotu definovanou objektem NEW.Kapacita před vložením záznamu, tedy použít funkci BEFORE INSERT. Poslední použitou funkcí je SIGNAL SQLSTATE. Tato funkce slouží k vyvolání výjimky. Číslo výjimky poté udává typ chyby. [13] Chyba začínající „40“ je běžná chyba. Poslední je funkce SET MESSAGE_TEXT, která nastaví zprávu, jež se zobrazí při vyvolání této výjimky. USE `rezervacni_system_db`; DELIMITER $$ CREATE TRIGGER `Pokoje_BINS` BEFORE INSERT ON `Pokoje` FOR EACH ROW BEGIN IF NEW.Kapacita <= 0 THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Kapacita pokoje musí být větší než 1.'; END IF; END; $$ DELIMITER ;
TRIGGER, jež je uveden níže, zachycuje celou podstatu rezervačního systému. Jedná se o kontrolu, zda již daný pokoj není zarezervovaný. Funkce DECLARE zde slouží k definování dočasné proměnné počet, která nabývá primitivního typu INTEGER. Do této proměnné je uložen počet rezervací daného pokoje na daný termín. Tato hodnota tedy může nabývat pouze hodnot 0 nebo 1. Pokud je hodnota rovna 1, znamená to, že pokoj je již obsazen a dojde k vypsání chyby. USE `rezervacni_system_db`; DELIMITER $$
33
CREATE TRIGGER `RezervovanePokoje_BINS` BEFORE INSERT ON `RezervovanePokoje` FOR EACH ROW BEGIN DECLARE pocet INT; SELECT count(idRezervace) INTO pocet FROM Rezervace INNER JOIN RezervovanePokoje USING (idRezervace) WHERE ((Od <= (SELECT Od FROM Rezervace WHERE idRezervace = NEW.idRezervace) AND Do > (SELECT Od FROM Rezervace WHERE idRezervace = NEW.idRezervace)) OR (Od >= (SELECT Od FROM Rezervace WHERE idRezervace = NEW.idRezervace) AND Od < (SELECT Do FROM Rezervace WHERE idRezervace = NEW.idRezervace))) AND idPokoje = NEW.idPokoje; IF pocet != 0 THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'V tomto rozmezí je již tento pokoj zarezervován.'; END IF; END; $$ DELIMITER ;
Další procedura využívá funkce REGEXP. Ta slouží pro kontrolu hodnoty pomocí regulárních výrazů. V první části je kontrolována předvolba, která je mezinárodně uznávaná a začíná vždy „+“. Plus však v jazyce regulárních výrazů též znamená jeden nebo více charakterů. Proto je nutné určit, že se skutečně jedná o znak plus. To lze zařídit dvěma zpětnými lomítky. Důvodem proč je nutné uvést dvě, je protože REGEXP v MySQL je založen na jazyku C a používá speciální znaky jako „\n“. [14] USE `rezervacni_system_db`; DELIMITER $$ CREATE TRIGGER `Hoste_BINS` BEFORE INSERT ON `Hoste` FOR EACH ROW BEGIN IF (NEW.Predvolba IS NOT NULL AND NEW.Predvolba REGEXP '^\\+[0-9]{1,3}$') THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Předvolba není v platném formátu.'; END IF; IF (NEW.Telefon IS NOT NULL AND NEW.Telefon REGEXP '^[[:digit:]]{9}$') THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Telefon není zadán v platném formátu.'; END IF; END; $$ DELIMITER ;
34
3.2.4 Normalizace názvů Při generování fyzického modelu byly vtahům M:N přidány automaticky vygenerované názvy odpovídající názvu vztahu. Nicméně tyto názvy nejsou ve většině případů vhodné jako název vazební entity. Z tohoto důvodu byly všechny vazební entity přejmenovány na odpovídající generickou formulaci „Seznam[Název entity]“, kde [Název entity] je nahrazeno jménem entity, kvůli které vztah zřízen. Například pro vztah Vlastnosti a Pokoje, bude vazební entita pojmenována SeznamVlastnosti. Stejný přístup je zvolen pro názvy cizích klíčů, které mají formu FK_[Název vztahu], jež nejsou příliš přehledné a jednoznačné. Cizí klíče jsou přejmenovány tak, aby korespondovaly s názvem primárního klíče, na který odkazují. Například pro cizí klíč „FK_se_stravují“ mezi entitami Zákazník a Stravování je přejmenován na „idStravovani“. Zároveň to umožňuje zjednodušení práce s SQL, kde lze použít syntaxi USING namísto ON. 3.2.5 Vygenerování SQL kódu Vygenerování SQL kódu v MySQL Workbench není nutné, pokud není kód potřeba vypsat v textové podobě. Pro nahrání databáze na hosting lze využít několik funkcí. Jednou z nich je „forward engineering“ a druhou je synchronizace modelu. Ačkoli „forward engineering“ disponuje i druhý použitý CASE nástroj PowerDesigner, není tato funkce tak výhodná jako synchronizace, neboť smaže všechny předchozí objekty v databázi a poté vloží nové. Zatímco synchronizace pouze provede úpravy a nahraje pouze změny, popřípadě přidá nové objekty. Vždy však zachová vložené záznamy, pokud je to možné. Jedná se tak o velkou úsporu času při testování.
35
Závěr Cílem této práce bylo vytvořit databázi, která by byla schopná poskytnout datovou základnu pro postavenou aplikaci. Tato databáze byla implementována v prostředí MySQL. Poté došlo k naplnění daty a otestování. Přestože se nejednalo o konkrétní realizaci pro zákazníka, byla vytvořena PHP aplikace podporující administrační rozhraní. Tato aplikace je plně funkční. Díky tomu mohu prohlásit, že návrh databáze byl úspěšný a lze jej využít v praxi. Databáze podporuje uvedené analyzované potřeby ze čtvrté kapitoly. V teoretické části práce byly uvedeny hlavní příčiny, které vedly k vytvoření této aplikace. Po vytvoření konceptuálního modelu v CASE nástroji PowerDesigner, byl posléze vygenerován fyzický model, který byl převeden do nástroje MySQL Workbench, jež poskytuje lepší uživatelské prostředí a další užitečnou funkcionalitu. V následném fyzickém modelu poté byly doplněny uložené procedury (TRIGGER), které slouží pro kontrolu ukládaných dat. Dále byl návrh naplněn testovacími daty, což je umožněno přímo z nástroje MySQL Workbench. Hotový model databáze byl poté nahrán na testovací lokální server, kde proběhly první úspěšné testy. S hotovou databází započala práce na návrhu GUI aplikace a možného vzhledu šablony webových stránek. Aplikace byla posléze implementována v prostředí PHP a otestována na lokálním serveru WampServer7. Přínos práce spočívá v navržení databázové struktury a reálné použití této databáze v aplikaci, která může být použita v praxi. V úvahu poté připadají dva byznys modely, kterými jsou poskytování služby za jednorázový licenční poplatek, či měsíční paušální platby. Tato aplikace spojuje čtyři hlavní prvky. Mezi ně patří správa rezervací, správa webových stránek, parametrizace a posledním prvkem jsou statistiky. Každá aplikace má možnost růstu a zlepšení. Jako námět pro další rozšíření navrhuji přidání možnosti zálohování databáze, či možnost vkládání a upravování webových šablon. S tím souvisí i možný navazující projekt, který by fungoval jako internetové tržiště šablon pro tuto rozšířenou aplikaci. Aplikace má opravdu velký potenciál růstu a možností, kterými se do budoucna může vydat. Ač již se jedná o web trh šablon pro tuto
7
Lokální server WampServer je k dispozici zdarma na adrese http://www.wampserver.com/en/.
36
aplikaci nebo spolupráce s online rezervačními portály. Dalším možným rozšířením mohou být další moduly v podobě online plateb nebo reklamačního systému.
37
Terminologický slovník Název pojmu
Zkratka
Vysvětlení Je procedura, která je automaticky spuštěna
Trigger
TRIGGER
pokaždé při předem nastavené akci. [vlastní definice autora]
Entity–
„Entity-relationship
relationship
ER model
model
softwarovém
model
(ERM)
inženýrství
se
používá
v pro
abstraktní a konceptuální znázornění dat.“ [15] „Unified Modeling Language je v softwarovém
Unified Modeling
UML
Language
inženýrství grafický jazyk pro vizualizaci, specifikaci,
navrhování
a
dokumentaci
programových systémů“ [16]
Chenova notace
-
Jedná se o grafickou podobu zápisu entit a vztahů v ER modelu. [vlastní definice autora] Určuje počet záznamů, kterých může nabývat
Kardinalita
-
entita s vazbou na tuto entitu. Nabývat může hodnot 1:1, 1:N nebo M:N. [vlastní definice autora]
Graphical
user
interface PHP: Hypertext Preprocessor
GUI PHP
Uživatelské rozhraní, které slouží k interakci s elektronickým zařízením. [17] Skriptovací jazyk běžící na straně serveru určený k tvorbě webových stránek. [18] „Hašovací funkce je matematická funkce (resp.
Hash
-
algoritmus) pro převod vstupních dat do (relativně) malého čísla.“ [19]
Integer Variable character Enumerated type
INT VARCHAR
Datový typ nabývající pouze číselných hodnot z rozmezí -2147483648 do 2147483648. [20] Datový typ obsahující maximálně předem určený počet znaků. [vlastní definice autora] Výčtový datový typ. Povolené hodnoty jsou
ENUM
nataveny
při
definice autora]
38
vytvoření
atributu.
[vlastní
Seznam použité literatury [1] J. Cox, „Introduction to MySQL Triggers,“ 2010. [Online]. Available: http://code.tutsplus.com/articles/introduction-to-mysql-triggers--net-12226. [Přístup získán 17 Březen 2014]. [2] Oracle, „MySQL 5.0 Reference Manual,“ [Online]. Available: http://dev.mysql.com/doc/refman/5.0/en/triggers.html. [Přístup získán 17 Březen 2014]. [3] M. Vojtek, Návrh databáze pro hotely a penziony : bakalářská práce, Praha : Vysoká škola ekonomická v Praze, Fakulta informatiky a statistiky, 2010. [4] M. J. Hernandez, Návrh databází, První vydání editor, Praha: Grada publushing, a.s., 2006. ISBN 80-247-0900-7. [5] K. Žák, „Root.cz,“ 19 Říjen 2001. [Online]. Available: http://www.root.cz/clanky/historie-relacnich-databazi/. [Přístup získán 14 April 2014]. [6] Wikipedia, the free encyclopedia, “CODASYL,” 21 Únor 2014. [Online]. Available: http://en.wikipedia.org/wiki/Codasyl. [Accessed 23 Duben 2014]. [7] H. Palovská, „Krokodata,“ 24 Květen 2011. [Online]. Available: http://krokodata.vse.cz/DM/Principy. [Přístup získán 22 Duben 2014]. [8] H. Palovská, „Krokodata,“ 21 Prosinec 2011. [Online]. Available: http://krokodata.vse.cz/DM/Mapovani. [Přístup získán 24 Duben 2014]. [9] Wikipedia, the free encyclopedia, “Denormalization,” 8 Leden 2014. [Online]. Available: http://en.wikipedia.org/wiki/Denormalization. [Accessed 24 Duben 2014]. [10] Wikipedia, the free encyclopedia, “SHA-1,” 29 Březen 2014. [Online]. Available: http://en.wikipedia.org/wiki/SHA-1. [Accessed 5 Květen 2014]. [11] Wikipedia, the free ecyclopedia, “Wikipedia - MD5,” 2014. [Online]. Available: http://en.wikipedia.org/wiki/MD5. [Accessed 22 Březen 2014]. [12] Wikipedia, the free encyclopedia, “InnoDB,” 2 Duben 2014. [Online]. Available: http://en.wikipedia.org/wiki/InnoDB. [Accessed 26 Duben 2014]. [13] Oracle, „SIGNAL SQLSTATE,“ 2014. [Online]. Available: https://dev.mysql.com/doc/refman/5.5/en/signal.html. [Přístup získán 27 Duben 2014]. [14] Oracle, „REGEXP,“ 2014. [Online]. Available: http://dev.mysql.com/doc/refman/5.1/en/regexp.html. [Přístup získán Duben 26 2014].
39
[15] Wikipedie, otevřená encyklopedie, „Entity-relationship model,“ 16 Duben 2013. [Online]. Available: http://cs.wikipedia.org/wiki/Entity-relationship_model. [Přístup získán 30 Duben 2014]. [16] Wikipedie, otevřená encyklopedie, „Unified Modeling Language,“ 18 Březen 2014. [Online]. Available: http://cs.wikipedia.org/wiki/UML. [Přístup získán 30 Duben 2014]. [17] Wikipedia, the free encyclopedia, “Graphical user interface,” 30 Květen 2014. [Online]. Available: http://en.wikipedia.org/wiki/GUI. [Accessed 30 Duben 2014]. [18] Wikipedia, the free encyclopedia, “PHP,” 1 Duben 2014. [Online]. Available: http://en.wikipedia.org/wiki/PHP. [Accessed 30 Duben 2014]. [19] Wikipedie, otevřená encyklopedie, „Hašovací funkce,“ 22 Únor 2014. [Online]. Available: http://cs.wikipedia.org/wiki/Ha%C5%A1ovac%C3%AD_funkce. [Přístup získán 5 Květen 2014]. [20] Oracle, „Integer,“ [Online]. Available: http://dev.mysql.com/doc/refman/5.1/en/integer-types.html. [Přístup získán 5 Květen 2014]. [21] T. Sardyha, “Sly,” 2014. [Online]. Available: http://darsa.in/sly/. [Accessed 30 Duben 2014]. [22] The jQuery Foundation, “JQuery,” 2014. [Online]. Available: http://jquery.com/. [Accessed 28 Duben 2014].
A. Seznam obrázků Obrázek 2.1.1 - Diagram jednoduchého síťového modelu databáze ........................................... 7 Obrázek 2.2.1 - Diagram jednoduchého konceptuálního modelu v Chenově notaci ............. 8 Obrázek 2.4.1 - Use case diagram pro administrátora aplikace ................................................. 17 Obrázek 2.4.2 - Use case diagram pro zaměstnance aplikace ..................................................... 19 Obrázek 2.4.3 - Use case diagram uživatelské skupiny účetní .................................................... 20 Obrázek 3.1.1 - Vztah mezi entitami Pokoje a Vlastnosti.............................................................. 21 Obrázek 3.1.2 - Vztah mezi entitou Pokoje a Vybavení ................................................................. 22 Obrázek 3.1.3 - Vztah mezi Rezervacemi a Zákazníky ................................................................... 22 Obrázek 3.1.4 - Dědičnost mezi Zákazníky, Hosty a Firmami ..................................................... 23
40
B. Seznam tabulek Tabulka 2.4.1 - Podrobný popis diagramu administrátora .......................................................... 17 Tabulka 2.4.2 - Podrobný popis diagramu zaměstnanců.............................................................. 19 Tabulka 2.4.3 - Popis případu užití pro účetní .................................................................................. 20 Tabulka 3.1.1 - Datové atributy u entity „Pokoje“ ........................................................................... 23 Tabulka 3.1.2 - Datové atributy u entity „Kategorie pokojů“ ...................................................... 23 Tabulka 3.1.3 - Datové atributy u entity „Ceníky“ ........................................................................... 24 Tabulka 3.1.4 - Datové atributy u vazební entity „Ceny pokojů“ ............................................... 24 Tabulka 3.1.5 - Datové atributy u entity „Vybavení“ ...................................................................... 24 Tabulka 3.1.6 - Datové atributy u entity „Služby“ ............................................................................ 24 Tabulka 3.1.7 - Datové atributy u entity „Ikony“ .............................................................................. 25 Tabulka 3.1.8 - Datové atributy u entity „Fotografie“ .................................................................... 25 Tabulka 3.1.9 - Datové atributy u entity „Vlastnosti“ ..................................................................... 25 Tabulka 3.1.10 - Datové atributy u vazební entity „Seznam vlastností“ ................................. 25 Tabulka 3.1.11 - Datové atributy u entity „Uživatelé“ .................................................................... 26 Tabulka 3.1.12 - Datové atributy u entity „Skupiny uživatelů“ .................................................. 26 Tabulka 3.1.13 - Datové atributy u entity „Články“ ......................................................................... 26 Tabulka 3.1.14 - Datové atributy u entity „Kategorie článků“ .................................................... 27 Tabulka 3.1.15 - Datové atributy u entity „Tagy“ ............................................................................. 27 Tabulka 3.1.16 - Datové atributy u entity „Události“ ...................................................................... 27 Tabulka 3.1.17 - Datové atributy u entity „Parametry“ ................................................................. 27 Tabulka 3.1.18 - Datové atributy u entity „Rezervace“ .................................................................. 28 Tabulka 3.1.19 - Datové atributy u entity "Zákazníci" ................................................................... 28 Tabulka 3.1.20 – Datové atributy u entity „Hosté“ .......................................................................... 28 Tabulka 3.1.21 – Datové atributy u entity „Firmy“ .......................................................................... 28 Tabulka 3.1.22 - Datové atributy u entity „Stravování“ ................................................................. 29 Tabulka 3.1.23 - Datové atributy u entity „Země“ ........................................................................... 29 Tabulka 3.1.24 - Datové atributy u entity „Provozovatelé“.......................................................... 29 Tabulka 3.1.25 - Datové atributy u entity „Faktury“ ....................................................................... 30
41
C. Přílohy Konceptuální model
42
Fyzický model
43
Vygenerovaný SQL kód SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `rezervacni_system_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE `rezervacni_system_db` ;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Zeme` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Zeme` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Zeme` ( `idZeme` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Zkratka` VARCHAR(8) NOT NULL, PRIMARY KEY (`idZeme`)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Provozovatele` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Provozovatele` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Provozovatele` ( `idProvozovatele` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(128) NOT NULL, `Mesto` VARCHAR(128) NOT NULL, `Adresa` VARCHAR(128) NOT NULL, `PSC` INT NOT NULL, `IC` VARCHAR(16) NOT NULL, `Email` VARCHAR(256) NOT NULL, `Predvolba` VARCHAR(8) NULL,
44
`Telefon` INT NULL, `Mobil` INT NULL, `KontaktniOsoba` VARCHAR(128) NOT NULL, `Poloha` VARCHAR(64) NOT NULL COMMENT 'Poloha se udává ve formátu xx.xxxxx, xx.xxxxx.', `Zapis` VARCHAR(64) NOT NULL, `Slozka` VARCHAR(64) NOT NULL, `Banka` VARCHAR(64) NOT NULL, `Ucet` VARCHAR(32) NOT NULL, `KonstantniSymbol` INT NULL, `IBAN` VARCHAR(32) NULL, `SWIFT` VARCHAR(32) NULL, `idZeme` INT NOT NULL, `Podpis` TEXT NOT NULL, `Logo` TEXT NOT NULL, `UvodniObrazek` TEXT NOT NULL, `Splatnost` INT NOT NULL, PRIMARY KEY (`idProvozovatele`), INDEX `fk_Provozovatele_Zeme1_idx` (`idZeme` ASC), CONSTRAINT `fk_Provozovatele_Zeme1` FOREIGN KEY (`idZeme`) REFERENCES `rezervacni_system_db`.`Zeme` (`idZeme`) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Skupiny` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Skupiny` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Skupiny` ( `idSkupiny` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Pravomoci` TEXT NOT NULL,
45
PRIMARY KEY (`idSkupiny`), UNIQUE INDEX `Nazev_UNIQUE` (`Nazev` ASC)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Uzivatele` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Uzivatele` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Uzivatele` ( `idUzivatele` INT NOT NULL AUTO_INCREMENT, `Email` VARCHAR(256) NOT NULL, `Jmeno` VARCHAR(64) NOT NULL, `Prijmeni` VARCHAR(64) NOT NULL, `Heslo` VARCHAR(64) NOT NULL, `Prihlaseni` DATETIME NULL DEFAULT NULL, `Session` VARCHAR(256) NULL, `Aktivni` TINYINT(1) NOT NULL DEFAULT 0, `idSkupiny` INT NOT NULL, PRIMARY KEY (`idUzivatele`), INDEX `fk_Uzivatele_Skupiny1_idx` (`idSkupiny` ASC), CONSTRAINT `fk_Uzivatele_Skupiny1` FOREIGN KEY (`idSkupiny`) REFERENCES `rezervacni_system_db`.`Skupiny` (`idSkupiny`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`StavRezervace` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`StavRezervace` ;
46
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`StavRezervace` ( `idStav` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Barva` VARCHAR(6) NOT NULL, PRIMARY KEY (`idStav`), UNIQUE INDEX `Nazev_UNIQUE` (`Nazev` ASC)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Stravovani` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Stravovani` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Stravovani` ( `idStravovani` INT NOT NULL AUTO_INCREMENT, `Typ` VARCHAR(64) NOT NULL, `Cena` DECIMAL(14,2) NOT NULL, PRIMARY KEY (`idStravovani`)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Zakaznici` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Zakaznici` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Zakaznici` ( `idZakaznika` INT NOT NULL AUTO_INCREMENT, `Typ` ENUM('Firma', 'Host', 'B') NOT NULL, `Email` VARCHAR(256) NOT NULL, `Predvolba` VARCHAR(8) NULL, `Telefon` INT NULL, `Mesto` VARCHAR(128) NOT NULL, `Adresa` VARCHAR(128) NOT NULL,
47
`PSC` INT NOT NULL, `idRezervace` INT NOT NULL, `idZeme` INT NOT NULL, `idStravovani` INT NOT NULL, PRIMARY KEY (`idZakaznika`, `Typ`), INDEX `fk_Hoste_Rezervace1_idx` (`idRezervace` ASC), INDEX `fk_Hoste_Zeme1_idx` (`idZeme` ASC), INDEX `fk_Hoste_Stravovani1_idx` (`idStravovani` ASC), CONSTRAINT `fk_Hoste_Rezervace10` FOREIGN KEY (`idRezervace`) REFERENCES `rezervacni_system_db`.`Rezervace` (`idRezervace`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Hoste_Zeme10` FOREIGN KEY (`idZeme`) REFERENCES `rezervacni_system_db`.`Zeme` (`idZeme`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Hoste_Stravovani10` FOREIGN KEY (`idStravovani`) REFERENCES `rezervacni_system_db`.`Stravovani` (`idStravovani`) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Rezervace` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Rezervace` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Rezervace` ( `idRezervace` INT NOT NULL AUTO_INCREMENT, `Od` DATE NOT NULL, `Do` DATE NOT NULL,
48
`Rezervovano` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `Poznamka` VARCHAR(512) NOT NULL, `idStav` INT NOT NULL, `idZakaznika` INT NULL, PRIMARY KEY (`idRezervace`), INDEX `fk_Rezervace_StavRezervace1_idx` (`idStav` ASC), INDEX `fk_Rezervace_Zakaznici1_idx` (`idZakaznika` ASC), CONSTRAINT `fk_Rezervace_StavRezervace1` FOREIGN KEY (`idStav`) REFERENCES `rezervacni_system_db`.`StavRezervace` (`idStav`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Rezervace_Zakaznici1` FOREIGN KEY (`idZakaznika`) REFERENCES `rezervacni_system_db`.`Zakaznici` (`idZakaznika`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Faktury` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Faktury` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Faktury` ( `idFaktury` INT NOT NULL AUTO_INCREMENT, `VariabilniSymbol` INT NOT NULL, `Vystaveno` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `Splatnost` DATE NOT NULL, `CelkovaCena` DECIMAL(14,2) NOT NULL, `idProvozovatele` INT NOT NULL, `idUzivatele` INT NULL, `idRezervace` INT NOT NULL, PRIMARY KEY (`idFaktury`),
49
INDEX `fk_Faktura_Provozovatele1_idx` (`idProvozovatele` ASC), INDEX `fk_Faktura_Uzivatele1_idx` (`idUzivatele` ASC), INDEX `fk_Faktura_Rezervace1_idx` (`idRezervace` ASC), CONSTRAINT `fk_Faktura_Provozovatele1` FOREIGN KEY (`idProvozovatele`) REFERENCES `rezervacni_system_db`.`Provozovatele` (`idProvozovatele`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Faktura_Uzivatele1` FOREIGN KEY (`idUzivatele`) REFERENCES `rezervacni_system_db`.`Uzivatele` (`idUzivatele`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Faktura_Rezervace1` FOREIGN KEY (`idRezervace`) REFERENCES `rezervacni_system_db`.`Rezervace` (`idRezervace`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`KategorieClanku` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`KategorieClanku` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`KategorieClanku` ( `idKategorie` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, PRIMARY KEY (`idKategorie`), UNIQUE INDEX `Nazev_UNIQUE` (`Nazev` ASC)) ENGINE = InnoDB;
-- -----------------------------------------------------
50
-- Table `rezervacni_system_db`.`Clanky` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Clanky` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Clanky` ( `idClanku` INT NOT NULL AUTO_INCREMENT, `Nadpis` VARCHAR(64) NOT NULL, `Text` TEXT NOT NULL, `Pridano` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `idKategorie` INT NOT NULL, `idUzivatele` INT NOT NULL, PRIMARY KEY (`idClanku`), INDEX `fk_Clanky_KategorieClanku1_idx` (`idKategorie` ASC), INDEX `fk_Clanky_Uzivatele1_idx` (`idUzivatele` ASC), CONSTRAINT `fk_Clanky_KategorieClanku1` FOREIGN KEY (`idKategorie`) REFERENCES `rezervacni_system_db`.`KategorieClanku` (`idKategorie`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Clanky_Uzivatele1` FOREIGN KEY (`idUzivatele`) REFERENCES `rezervacni_system_db`.`Uzivatele` (`idUzivatele`) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Tagy` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Tagy` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Tagy` ( `idTagu` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL,
51
PRIMARY KEY (`idTagu`), UNIQUE INDEX `Nazev_UNIQUE` (`Nazev` ASC)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Parametry` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Parametry` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Parametry` ( `idParametru` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Hodnota` TEXT NOT NULL, PRIMARY KEY (`idParametru`), UNIQUE INDEX `Nazev_UNIQUE` (`Nazev` ASC)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Udalosti` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Udalosti` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Udalosti` ( `idUdalosti` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Text` TEXT NOT NULL, `Datum` DATE NOT NULL, `CasZacatku` TIME NOT NULL, `CasUkonceni` TIME NOT NULL, `Lokace` VARCHAR(128) NOT NULL, `Vytvoreno` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `idUzivatele` INT NOT NULL, PRIMARY KEY (`idUdalosti`),
52
UNIQUE INDEX `Datum_UNIQUE` (`Datum` ASC), INDEX `fk_Udalosti_Uzivatele1_idx` (`idUzivatele` ASC), CONSTRAINT `fk_Udalosti_Uzivatele1` FOREIGN KEY (`idUzivatele`) REFERENCES `rezervacni_system_db`.`Uzivatele` (`idUzivatele`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Fotografie` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Fotografie` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Fotografie` ( `idFotografie` INT NOT NULL AUTO_INCREMENT, `Nadpis` VARCHAR(64) NOT NULL, `Popis` VARCHAR(64) NULL, `URL` TEXT NOT NULL, `Typ` VARCHAR(8) NOT NULL, PRIMARY KEY (`idFotografie`)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Ikony` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Ikony` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Ikony` ( `idIkony` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Kod` TINYINT(1) NOT NULL, `Hodnota` TEXT NOT NULL,
53
PRIMARY KEY (`idIkony`)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Sluzby` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Sluzby` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Sluzby` ( `idSluzby` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Popis` VARCHAR(256) NOT NULL, `idIkony` INT NOT NULL, PRIMARY KEY (`idSluzby`), INDEX `fk_Sluzby_Ikony1_idx` (`idIkony` ASC), CONSTRAINT `fk_Sluzby_Ikony1` FOREIGN KEY (`idIkony`) REFERENCES `rezervacni_system_db`.`Ikony` (`idIkony`) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`KategoriePokoju` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`KategoriePokoju` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`KategoriePokoju` ( `idKategorie` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, PRIMARY KEY (`idKategorie`)) ENGINE = InnoDB;
54
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Pokoje` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Pokoje` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Pokoje` ( `idPokoje` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Popis` VARCHAR(256) NOT NULL, `Cislo` VARCHAR(8) NOT NULL, `Kapacita` INT NOT NULL, `idKategorie` INT NULL, `idFotografie` INT NULL, `Plan` INT NULL, PRIMARY KEY (`idPokoje`), INDEX `fk_Pokoje_KategoriePokoju1_idx` (`idKategorie` ASC), INDEX `fk_Pokoje_Fotografie1_idx` (`idFotografie` ASC), INDEX `fk_Pokoje_Fotografie2_idx` (`Plan` ASC), CONSTRAINT `fk_Pokoje_KategoriePokoju1` FOREIGN KEY (`idKategorie`) REFERENCES `rezervacni_system_db`.`KategoriePokoju` (`idKategorie`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Pokoje_Fotografie1` FOREIGN KEY (`idFotografie`) REFERENCES `rezervacni_system_db`.`Fotografie` (`idFotografie`) ON DELETE RESTRICT ON UPDATE NO ACTION, CONSTRAINT `fk_Pokoje_Fotografie2` FOREIGN KEY (`Plan`) REFERENCES `rezervacni_system_db`.`Fotografie` (`idFotografie`) ON DELETE SET NULL ON UPDATE NO ACTION) ENGINE = InnoDB;
55
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Galerie` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Galerie` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Galerie` ( `idFotografie` INT NOT NULL, INDEX `fk_Galerie_Fotografie1_idx` (`idFotografie` ASC), PRIMARY KEY (`idFotografie`), CONSTRAINT `fk_Galerie_Fotografie1` FOREIGN KEY (`idFotografie`) REFERENCES `rezervacni_system_db`.`Fotografie` (`idFotografie`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`GaleriePokoju` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`GaleriePokoju` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`GaleriePokoju` ( `idFotografie` INT NOT NULL, `idPokoje` INT NOT NULL, PRIMARY KEY (`idPokoje`, `idFotografie`), INDEX `fk_GaleriePokoju_Fotografie1_idx` (`idFotografie` ASC), INDEX `fk_GaleriePokoju_Pokoje1_idx` (`idPokoje` ASC), CONSTRAINT `fk_GaleriePokoju_Fotografie1` FOREIGN KEY (`idFotografie`) REFERENCES `rezervacni_system_db`.`Fotografie` (`idFotografie`) ON DELETE CASCADE ON UPDATE NO ACTION,
56
CONSTRAINT `fk_GaleriePokoju_Pokoje1` FOREIGN KEY (`idPokoje`) REFERENCES `rezervacni_system_db`.`Pokoje` (`idPokoje`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Vybaveni` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Vybaveni` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Vybaveni` ( `idVybaveni` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `Popis` VARCHAR(64) NULL, PRIMARY KEY (`idVybaveni`)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`SeznamVybaveni` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`SeznamVybaveni` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`SeznamVybaveni` ( `idVybaveni` INT NOT NULL, `idPokoje` INT NOT NULL, PRIMARY KEY (`idVybaveni`, `idPokoje`), INDEX `fk_SeznamVybaveni_Vybaveni1_idx` (`idVybaveni` ASC), INDEX `fk_SeznamVybaveni_Pokoje1_idx` (`idPokoje` ASC), CONSTRAINT `fk_SeznamVybaveni_Vybaveni1` FOREIGN KEY (`idVybaveni`) REFERENCES `rezervacni_system_db`.`Vybaveni` (`idVybaveni`)
57
ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_SeznamVybaveni_Pokoje1` FOREIGN KEY (`idPokoje`) REFERENCES `rezervacni_system_db`.`Pokoje` (`idPokoje`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`RezervovanePokoje` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`RezervovanePokoje` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`RezervovanePokoje` ( `idRezervace` INT NOT NULL, `idPokoje` INT NOT NULL, PRIMARY KEY (`idRezervace`, `idPokoje`), INDEX `fk_RezervovanePokoje_Rezervace1_idx` (`idRezervace` ASC), INDEX `fk_RezervovanePokoje_Pokoje1_idx` (`idPokoje` ASC), CONSTRAINT `fk_RezervovanePokoje_Rezervace1` FOREIGN KEY (`idRezervace`) REFERENCES `rezervacni_system_db`.`Rezervace` (`idRezervace`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_RezervovanePokoje_Pokoje1` FOREIGN KEY (`idPokoje`) REFERENCES `rezervacni_system_db`.`Pokoje` (`idPokoje`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- -----------------------------------------------------
58
-- Table `rezervacni_system_db`.`Hoste` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Hoste` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Hoste` ( `idZakaznika` INT NOT NULL, `Typ` ENUM('Host') NOT NULL, `Jmeno` VARCHAR(64) NOT NULL, `Prijmeni` VARCHAR(64) NOT NULL, `CisloPrukazu` VARCHAR(64) NOT NULL, PRIMARY KEY (`idZakaznika`, `Typ`), CONSTRAINT `fk_Hoste_Zakaznici1` FOREIGN KEY (`idZakaznika`) REFERENCES `rezervacni_system_db`.`Zakaznici` (`idZakaznika`) ON DELETE CASCADE ON UPDATE RESTRICT) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`UvodniStranka` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`UvodniStranka` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`UvodniStranka` ( `idSluzby` INT NOT NULL, PRIMARY KEY (`idSluzby`), INDEX `fk_Web_Sluzby1_idx` (`idSluzby` ASC), CONSTRAINT `fk_Web_Sluzby1` FOREIGN KEY (`idSluzby`) REFERENCES `rezervacni_system_db`.`Sluzby` (`idSluzby`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
59
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Ceniky` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Ceniky` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Ceniky` ( `idCeniku` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, `PocatekPlatnosti` DATE NOT NULL, `KonecPlatnosti` DATE NOT NULL, `Vytvoreno` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `Vychozi` TINYINT(1) NOT NULL, `idUzivatele` INT NOT NULL, PRIMARY KEY (`idCeniku`), INDEX `fk_Ceniky_Uzivatele1_idx` (`idUzivatele` ASC), CONSTRAINT `fk_Ceniky_Uzivatele1` FOREIGN KEY (`idUzivatele`) REFERENCES `rezervacni_system_db`.`Uzivatele` (`idUzivatele`) ON DELETE RESTRICT ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`CenyPokoju` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`CenyPokoju` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`CenyPokoju` ( `idPokoje` INT NOT NULL, `idCeniku` INT NOT NULL, `Cena` DECIMAL(14,2) NOT NULL, PRIMARY KEY (`idPokoje`, `idCeniku`), INDEX `fk_CenyPokoju_Ceniky1_idx` (`idCeniku` ASC),
60
CONSTRAINT `fk_CenyPokoju_Pokoje1` FOREIGN KEY (`idPokoje`) REFERENCES `rezervacni_system_db`.`Pokoje` (`idPokoje`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_CenyPokoju_Ceniky1` FOREIGN KEY (`idCeniku`) REFERENCES `rezervacni_system_db`.`Ceniky` (`idCeniku`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Vlastnosti` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Vlastnosti` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Vlastnosti` ( `idVlastnosti` INT NOT NULL AUTO_INCREMENT, `Nazev` VARCHAR(64) NOT NULL, PRIMARY KEY (`idVlastnosti`), UNIQUE INDEX `Nazev_UNIQUE` (`Nazev` ASC)) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`Firmy` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`Firmy` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`Firmy` ( `idZakaznika` INT NOT NULL, `Typ` ENUM('Firma') NOT NULL, `IC` VARCHAR(16) NOT NULL,
61
`DIC` VARCHAR(32) NOT NULL, `Nazev` VARCHAR(64) NOT NULL, `Jmeno` VARCHAR(64) NOT NULL, `Prijmeni` VARCHAR(64) NOT NULL, PRIMARY KEY (`idZakaznika`, `Typ`), CONSTRAINT `fk_Firmy_Zakaznici1` FOREIGN KEY (`idZakaznika`) REFERENCES `rezervacni_system_db`.`Zakaznici` (`idZakaznika`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`SeznamSluzeb` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`SeznamSluzeb` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`SeznamSluzeb` ( `idSluzby` INT NOT NULL, `idPokoje` INT NOT NULL, PRIMARY KEY (`idSluzby`, `idPokoje`), INDEX `fk_Sluzby_has_Pokoje_Pokoje1_idx` (`idPokoje` ASC), INDEX `fk_Sluzby_has_Pokoje_Sluzby1_idx` (`idSluzby` ASC), CONSTRAINT `fk_Sluzby_has_Pokoje_Sluzby1` FOREIGN KEY (`idSluzby`) REFERENCES `rezervacni_system_db`.`Sluzby` (`idSluzby`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_Sluzby_has_Pokoje_Pokoje1` FOREIGN KEY (`idPokoje`) REFERENCES `rezervacni_system_db`.`Pokoje` (`idPokoje`) ON DELETE CASCADE ON UPDATE NO ACTION) ENGINE = InnoDB;
62
-- ------------------------------------------------------ Table `rezervacni_system_db`.`SeznamTagu` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`SeznamTagu` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`SeznamTagu` ( `idClanku` INT NOT NULL, `idTagu` INT NOT NULL, PRIMARY KEY (`idClanku`, `idTagu`), INDEX `fk_Clanky_has_Tagy_Tagy1_idx` (`idTagu` ASC), INDEX `fk_Clanky_has_Tagy_Clanky1_idx` (`idClanku` ASC), CONSTRAINT `fk_Clanky_has_Tagy_Clanky1` FOREIGN KEY (`idClanku`) REFERENCES `rezervacni_system_db`.`Clanky` (`idClanku`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_Clanky_has_Tagy_Tagy1` FOREIGN KEY (`idTagu`) REFERENCES `rezervacni_system_db`.`Tagy` (`idTagu`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB;
-- ------------------------------------------------------ Table `rezervacni_system_db`.`SeznamVlastnosti` -- ----------------------------------------------------DROP TABLE IF EXISTS `rezervacni_system_db`.`SeznamVlastnosti` ;
CREATE TABLE IF NOT EXISTS `rezervacni_system_db`.`SeznamVlastnosti` ( `idPokoje` INT NOT NULL, `idVlastnosti` INT NOT NULL, `Hodnota` VARCHAR(64) NOT NULL,
63
PRIMARY KEY (`idPokoje`, `idVlastnosti`), INDEX `fk_Pokoje_has_Vlastnosti_Vlastnosti1_idx` (`idVlastnosti` ASC), INDEX `fk_Pokoje_has_Vlastnosti_Pokoje1_idx` (`idPokoje` ASC), UNIQUE INDEX `Hodnota_UNIQUE` (`idVlastnosti` ASC, `Hodnota` ASC), CONSTRAINT `fk_Pokoje_has_Vlastnosti_Pokoje1` FOREIGN KEY (`idPokoje`) REFERENCES `rezervacni_system_db`.`Pokoje` (`idPokoje`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_Pokoje_has_Vlastnosti_Vlastnosti1` FOREIGN KEY (`idVlastnosti`) REFERENCES `rezervacni_system_db`.`Vlastnosti` (`idVlastnosti`) ON DELETE CASCADE ON UPDATE RESTRICT) ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; USE `rezervacni_system_db`;
DELIMITER $$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Provozovatele_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Provozovatele_BINS` BEFORE INSERT ON `Provozovatele` FOR EACH ROW BEGIN IF (NEW.Predvolba IS NOT NULL AND NEW.Predvolba REGEXP '^\\+[0-9]{1,3}$') THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Předvolba není v platném formátu.'; END IF; IF (NEW.Telefon IS NOT NULL AND NEW.Telefon REGEXP '^[[:digit:]]{9}$') THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Telefon není zadán v platném formátu.'; END IF;
64
END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`StavRezervace_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `StavRezervace_BINS` BEFORE INSERT ON `StavRezervace` FOR EACH ROW BEGIN IF NEW.Barva REGEXP '^[0-9A-F]{6}$' THEN formátu.';
SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Barva není v platném hexadecimálním
END IF; END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Stravovani_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Stravovani_BINS` BEFORE INSERT ON `Stravovani` FOR EACH ROW BEGIN IF NEW.Cena < 0 THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Cena nemůže být záporná.'; END IF; END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Zakaznici_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Zakaznici_BINS` BEFORE INSERT ON `Zakaznici` FOR EACH ROW BEGIN IF (NEW.Predvolba IS NOT NULL AND NEW.Predvolba REGEXP '^\\+[0-9]{1,3}$') THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Předvolba není v platném formátu.'; END IF; IF (NEW.Telefon IS NOT NULL AND NEW.Telefon REGEXP '^[[:digit:]]{9}$') THEN
65
SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Telefon není zadán v platném formátu.'; END IF; END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Faktury_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Faktury_BINS` BEFORE INSERT ON `Faktury` FOR EACH ROW BEGIN IF NEW.CelkovaCena < 0 THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Cena nemůže být záporná.'; END IF; END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Pokoje_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Pokoje_BINS` BEFORE INSERT ON `Pokoje` FOR EACH ROW BEGIN IF NEW.Kapacita <= 0 THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Kapacita pokoje musí být větší než 1.'; END IF; END; DELIMITER ;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`RezervovanePokoje_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `RezervovanePokoje_BINS` BEFORE INSERT ON `RezervovanePokoje` FOR EACH ROW BEGIN DECLARE pocet INT;
66
SELECT count(idRezervace) INTO pocet FROM Rezervace INNER JOIN RezervovanePokoje USING (idRezervace) WHERE ((Od <= (SELECT Od FROM Rezervace WHERE idRezervace = NEW.idRezervace) AND Do > (SELECT Od FROM Rezervace WHERE idRezervace = NEW.idRezervace)) OR (Od >= (SELECT Od FROM Rezervace WHERE idRezervace = NEW.idRezervace) AND Od < (SELECT Do FROM Rezervace WHERE idRezervace = NEW.idRezervace))) AND idPokoje = NEW.idPokoje; IF pocet != 0 THEN zarezervován.';
SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'V tomto rozmezí je již tento pokoj
END IF; END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Hoste_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Hoste_BINS` BEFORE INSERT ON `Hoste` FOR EACH ROW begin DECLARE temp INT; (SELECT Typ INTO temp FROM Zakaznici WHERE idZakaznika = NEW.IdZakaznika); IF temp LIKE NEW.Typ THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Hosta nelze vložit.'; END IF; end;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`CenyPokoju_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `CenyPokoju_BINS` BEFORE INSERT ON `CenyPokoju` FOR EACH ROW BEGIN IF NEW.Cena < 0 THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Cena nemůže být záporná.';
67
END IF; END;$$
USE `rezervacni_system_db`$$ DROP TRIGGER IF EXISTS `rezervacni_system_db`.`Firmy_BINS` $$ USE `rezervacni_system_db`$$ CREATE TRIGGER `Firmy_BINS` BEFORE INSERT ON `Firmy` FOR EACH ROW begin DECLARE temp INT; (SELECT Typ INTO temp FROM Zakaznici WHERE idZakaznika = NEW.IdZakaznika); IF temp LIKE NEW.Typ THEN SIGNAL SQLSTATE '40000' SET MESSAGE_TEXT = 'Firmu nelze vložit.'; END IF; end; $$
DELIMITER ;
68
