Univerzita Pardubice Fakulta elektrotechniky a informatiky
SEMESTRÁLNÍ PRÁCE PRO PŘEDMĚT IDAS2
Jan Bartocha 2012 / 2013
IT
1. Základní charakteristika Téma mé semestrální práce se zaměřuje na vypůjčování vozidel na internetu. Firma nabízí zákazníkům vozidla nižší, nižší střední a střední třídy. Jelikož se firma zaměřuje pouze neživnostenské osoby je umožněno zákazníkovi půjčit pouze jedno auto na jednu objednávku. K objednávkám jsou nabízeny také specifické služby jako jsou úrazové pojištění, pojištění proti živelným pohromám, krádeži, atd... U všech slev si při vytvoření může uživatel (zaměstnanec nebo administrátor) vybrat, jestli se bude jednat o jednorázovou slevu, tj. Sleva se aplikuje pouze jednou – nehledě na počet dní, kdy bude mít zákazník vypůjčené auto se slevou, nebo zda bude sleva započítaná na každý den objednávky. Sleva může být buď specifikována konkrétní hodnotou, nebo může být vyjádřena procentuálně. Stránky umožňují zaměstnancům vytvářet slevy na jednotlivé typy vozidel, konkrétní vozidla nebo služby. Zaměstnanec nejprve vytvoří slevu a později jí může přiřadit ke specifické entitě (vozidlo, typ vozidla, služba). Stránka rozeznává 4 typy uživatelských přístupů : • neregistrovaný uživatel – může si prohlížet základní nabídku firmy, slev, vozidel, chystaných akcí, odebírání novinek, atd... • registrovaný zákazník – stejné jako neregistrovaný zákazník, ale může si také vytvořit výpůjčku na vozidlo od firmy. • zaměstnanec – přístup do zaměstnanecké sekce. Kontroluje stav vozidel po navrácení vozidel od zákazníků a případně vyřizuje způsobené škody. • administrátor – má všechna práva výše zmíněných a také může registrovat zákazníky. Aplikace je určena pro malé firmy bez poboček se zaměřením pouze na běžnou klientelu ( nenabízí své služby firmám ).
2. Fyzický model
3. Use case diagram
4. Popis jednotlivých tabulek 1. Adresy
Tabulka obsahuje adresační údaje uživatele.
Popis atributů : • • • • •
ID_adresa : používá sekvenci ID_adresa_inc (inkremetuje po každé m vložení o jedničku) Mesto : název města kde uživatel bydlí PSC : PSC daného města. Tato hodnota není kontrolována Ulice : Cislo popisne : očekáváme vstup jednoduchého čísla nebo čísla s lomítkem (např. 13 nebo 200/12)
2. Uzivatele
Tabulka obsahuje údaje o registrovaných zákaznících(kontaktní údaje, email, přihlašovací údaje a práva). Uživatel může mít více adres. Při objednávce si zvolí adresu na kterou mu mají být po zaplacení poslány fakturační údaje a případné další dokumenty. Nad touto tabulkou je vytvořen index „Prijmeni_index“ pro rychlejší vyhledávání podle příjmení.
Popis atributů : • • • • • • • • • •
ID_uzivatele : používá sekvenci ID_uzivatele_inc (inkremetuje po každé m vložení o jedničku). email : email uživatele, na který mu budou odesílány údaje o objednávka, novinky, atd... jmeno : prijmeni : uziv_jmeno : jinak taky login. Jedná se o atribut, který uživatel potřebuje pro přihlášení na svůj učet. Heslo : Obsahuje zakryptované heslo uživatele. Nutné pro přihlášení do systému. rodne_cislo : opravneni : Uživatel může nabívat různých oprávnění. Jsou očekávany vstupy 111, 331 nebo 767. datum_narozeni : telefon : Pro kontaktní údaje k uživateli.
3. Barvy
Tabulka do které ukládáme názvy barev pro vozidla.
Popis atributů : • •
ID_barvy : primární klíč tabulky. Používá sekvenci ID_barvy_inc ( opět inkrementace o jedničku při každém vkládání). nazev : slovní pojmenování pro název barvy.
4. Znacky
Slovní popis pro id značky přes neidentifikující relaci s tabulkou Typy_aut
Popis atributů : • •
ID_znacky : Primární klíč tabulky. Obsahuje sekvenci ID_znacky_inc. nazev_znacky : Slovní pojmenování pro název značky (Ford, Peugot, atd...)
5. Typy_aut
Tabulka slouží k ukládání jednotlivých typů aut dané značky a také obsahuje cenu za kterou se budou tyto typy aut nabízet na den.
Popis atributů : • • • •
ID_auta : Primární klíč tabulky. Obsahuje sekvenci ID_znacky_inc. nazev_auta : Slovní pojmenování pro název značky (Ford, Peugot, atd...). cena_na_den : Půjčovní cena auta daného typu na den ( bez jakýchkoliv slev ). ID_znacky : cizí klíč pro propojení s tabulkou Znacky.
6. Vozidla
Tabulka obsahuje informace o konkrétních vozidlech, které se přes tuto aplikaci budou nabízet.
Popis atributů : • • • • • • • •
ID_vozidla : Primární klíč tabulky. Využívá sekvence ID_vozidla_inc. Seriove_cislo : Sériové číslo vozidla. Může obsahovat libovolné znaky a může být libovolné délky najeto : Stav o najetých kilometrech u vozidla. doba_zaruky : Délka záruky od zakoupení vozidla. zakoupeno : Datum, odkdy se počítá záruka. Jinak řečeno se jedná o datum kdy bylo auto zakoupeno jako nové. ID_auta : cizí klíč tabulky. Pro spojení s tabulkou Typy_aut. ID_barvy : cizí klíč tabulky. Pro spojení s tabulkou Barvy. ID_stavu : cizí klíč tabulky. Pro spojení s tabulkou Stavy_aut.
7. Stavy_vozidel
Tabulka pro popis stavu vozidel.
Popis atributů : • • •
ID_stavu : Primární klíč tabulky. Na tento atribut je použita sekvence ID_stavu_inc. popis : Pro ukládání podrobných informací o vadách na vozidle, které byly opraveny a případných dalších informací. stav : Informace pro zařazení vozidla do těchto skupin : ◦ OK : auto je možné půjčovat. ◦ UPRAVA : na auto se něco provádí ( krom oprav ). ◦ OPRAVA : auto je v opravě. ◦ PUJCENO : auto je pro daný termín vypůjčeno.
8. Stavy_aut
Tabulka která vznikla vazbou M:N. Obsahuje nepovinný atribut ID_vypujcky, který případně spojuje tabulku s tabulkou Vypujcky. Slouží hlavně k ukládání historie úprav, oprav, výpůjček, atd... nad konkrétním vozidlem.
Popis atributů : • • • • •
ID_stavu : ID_vozidla : odkdy : Datum, kdy daný stav nastane. Dokdy : Datum, kdy daný stav skončil, popřípadě určitě skončí. ID_vypujcky : Atribut je nepovinný protože se ne vždy může vztahovat k výpůjčce.
9. Vypujcky
Tabulka pro ukládání informacích o vytvořených objednávkách od uživatelů. Mimo jiné jsou v ní také informace o jednotlivých škodách, celkové ceně, způsobených škodách během vypůjčení a požadovaném odškodnění.
Popis atributů : • • • • • • •
• • •
10.
ID_vypujcky : Primární klíč tabulky. Pro inkrementaci je použita sekvence ID_vypujcky_inc. cena_vypujcky : Celková cena za výpůjčku(včetně slev, ceny služby a ceny_auta) datum_vypujceni : Datum, kdy byla objednávka vytvořena. cena_na_auto : Cena půjčovaného vozidla na jeden den během vytvoření objednávky pro vybraný časový interval. odkdy : Datum odkdy má být auto vypůjčeno. pocet_dni : Délka objednávky ve dnech. zaplaceno : Může nabývat dvou stavů : • 0 : nezaplaceno • 1 : zaplaceno Zda byla uhrazena částka. ( v případě, kdy uživatel navrátí auto poničené se tato hodnota nastavuje na 0). vraceno : Informace, zda bylo auto vráceno. popis_skody : Vyplňuje se pouze v případě že nastala škoda. Logicky po navrácení vozidla a odhalení nové vady. doplatek : Částka, která bude po uživateli vyžadována za způsobenou škodu.
Vybaveni
Tabulka obsahuje specifické označení vybavení v konkrétním vozidle.
Popis atributů : • •
ID_vybaveni : primární klíč tabulky. Je na něj aplikovaná sekvence ID_vybaveni_inc. nazev_vybaveni : Označení pro dané zařízení pod kterým bude evidováno.
11.
Vybaveni_auta
Popis atributů : • • •
12.
ID_vybaveni : ID_vozidla : specificke_oznaceni : obsahuje seriove cislo vybaveni.
Sluzby
Služby, které budou firmou nabízeny.
Popis atributů : • • • •
•
ID_sluzby : Primarni klic. Pouziva sekvenci ID_sluzby_inc nazev : Hlavni popisek pro název služby. cena : Cena, za kterou bude služba nabízena. Jednorazova : nad tímto atributem je Check Constraint sluzby_jednorazova_CC, který dohlíží aby bylo možné uložit pouze stavy 0 a 1. ◦ 0 – cena je pro 1 den ◦ 1 - cena je pro celou dobu výpůjčky vozidla, ke kterému je služba vybraná. popis : Detailní popis služby.
13.
Stavy_vozidel
Tabulka vzniká pro každou službu při každé výpůjčce.
Popis atributů : • • •
14.
ID_sluzby : ID_vypujcky : cena_sluzby : Cena služby pro čas kdy si uživatel zařizoval objednávku.
Slevy
Tabulka pro vytváření chystaných slev. Tyto slevy se budou spojovat pomocí vazeb M:N k tabulkám vozidel, služeb, a typech_aut. Nad touto tabulkou pracuje Check constraint který vyžaduje vyplnění buď sleva_procent nebo sleva_kc.
Popis atributů : • • •
•
ID_slevy : Primarni klic. Pouziva sekvenci ID_sluzby_inc nazev : Název slevy. Bude se zobrazovat jako titulek. jednorazova : nad tímto atributem je Check Constraint slevy_jednorazova_CC, který dohlíží aby bylo možné uložit pouze stavy 0 a 1. ◦ 0 – cena je pro 1 den ◦ 1 - cena je pro celou dobu výpůjčky vozidla, ke kterému je služba vybraná. sleva_procent : Udává procentuální hodnotu pro slevu. V případě, že tento atribut
•
15.
není vyplněn je očekáváno vyplnění slevy na sleva_kc. sleva_kc : Udává hodnotu o kterou bude daný element slevněn. V případě, že tento atribut není vyplněn je očekáváno vyplnění slevy na sleva_procent.
Slevy_sluzby
Obsahuje časový interval, ve kterém se sleva bude na danou službu aplikovat. Řádek tabulky vzniká až po vytvoření dané služby a slevy spojením. Nad tabulkou je Check Constraint slevy_sluzby_od_do_CC který zajišťuje že dokdy bude v čase později než odkdy.
Popis atributů : • • • • •
16.
ID_slevy : ID_sluzby : odkdy_sluzby : Odkdy daná sleva na službu platí. dokdy_sluzby : Dokdy daná sleva na službu platí. popis : Detailní popis služby. Je zobrazován uživatelům jako obsah článku.
Slevy_typy_aut
Tabulka se chová stejně jako tabulka Slevy_sluzby akorát se vztahuje k typy_aut. Nad tabulkou je Check Constraint slevy_typy_aut_od_do_CC který zajišťuje že dokdy bude v čase později než odkdy.
Popis atributů : • •
ID_auta : ID_slevy :
• • •
17.
odkdy_typy_aut : Odkdy daná sleva na vybraný typ aut platí. dokdy_typy_aut : dokdy daná sleva na vybraný typ aut platí. popis : Detailní popis služby. Je zobrazován uživatelům jako obsah článku.
Slevy_vozidla
Tabulka se chová stejně jako tabulka Slevy_sluzby akorát se vztahuje k typy_aut. Nad tabulkou je Check Constraint slevy_vozidla_od_do_CC který zajišťuje že dokdy bude v čase později než odkdy.
Popis atributů : • • • • •
ID_slevy : ID_vozidla : odkdy_vozidla : Odkdy daná sleva na vybrané vozidlo platí. dokdy_vozidla : Odkdy daná sleva na vybrané vozidlo platí. popis : Detailní popis služby. Je zobrazován uživatelům jako obsah článku.
5. Pohledy, procedury, funkce a triggery 1. Funnkce pro zjištění půjčení vozidla v daném intervalu CREATE OR REPLACE FUNCTION je_volny(idcko NUMBER, od DATE, do DATE) RETURN VARCHAR2 AS vysl VARCHAR2(10) DEFAULT 'NE'; --cursors CURSOR hledej_kolizi(id NUMBER, odk DATE, dok DATE) IS SELECT id_vozidla FROM vozidla JOIN Stavy_aut ON vozidla.ID_vozidla = Stavy_aut.ID_vozidla WHERE ((od BETWEEN stavy_aut.odkdy AND Stavy_aut.dokdy) OR (do BETWEEN stavy_aut.odkdy AND Stavy_aut.dokdy) OR (od > stavy_aut.odkdy AND do < dokdy)
OR (od < stavy_aut.odkdy AND do > dokdy)) AND Vozidla.id_vozidla = id; BEGIN FOR res IN hledej_kolizi(idcko, od, do) LOOP vysl := 'ANO'; END LOOP; return vysl; END je_volny ; Funkce zjistí zda dané auto ve zvoleném časovém intervalu je zarezervované. Vrácí 'ANO' v případě že auto zarezervované je, jinak vrací 'NE'.
2. procedura pro výpis vozidel ve zvoleném stavu CREATE OR REPLACE PROCEDURE volna_auta(stav VARCHAR2) CURSOR vyhledej_auta_stavu(st VARCHAR2) IS SELECT id_vozidla FROM Vozidla JOIN Stavy_vozidel ON Vozidla.ID_stavu = Stavy_vozidel.ID_stavu WHERE Stavy_vozidel.stav = st; CURSOR vypis_vsechna_auta IS SELECT id_vozidla FROM Vozidla; BEGIN CASE WHEN stav = 'OK' THEN FOR vysl IN vypis_vsechna_auta LOOP IF hledej_kolizi(vysl.id_vozidla, sysdate, sysdate) = 'NE' THEN dbms_output.put_line(vysl.id_vozidla); END IF; END LOOP; WHEN stav = 'OPRAVA' THEN FOR vysl IN vyhledej_auta_stavu(stav) LOOP dbms_output.put_line(vysl.id_vozidla); END LOOP; WHEN stav = 'UPRAVA' THEN FOR vysl IN vyhledej_auta_stavu(stav) LOOP dbms_output.put_line(vysl.id_vozidla); END LOOP; WHEN stav = 'PUJCENO' THEN FOR vysl IN vypis_vsechna_auta LOOP IF hledej_kolizi(vysl.id_vozidla, sysdate, sysdate) = 'ANO' THEN dbms_output.put_line(vysl.id_vozidla); END IF; END LOOP; ELSE RAISE_APPLICATION_ERROR(-20118, 'Neplatný stav vozidla !'); END CASE;
END volna_auta; Procedura vypíše ID všech vozidel které jsou momentálně ve zvoleném stavu( stavy jsou : OK, PUJCENO, OPRAVA, UPRAVA).
3. Trigger nad tabulkou uživatel pro validaci vstupů CREATE OR REPLACE TRIGGER Uzivatele_trigger_validace BEFORE INSERT OR UPDATE ON Uzivatele FOR EACH ROW DECLARE vek NUMBER; BEGIN IF ((sysdate - :old.datum_narozeni)/365.25) < 18 THEN raise_application_error (-20100,'Osoba je mladší 18 let !'); END IF; IF NOT REGEXP_LIKE(:new.telefon, '(^[0-9]{9,})') THEN raise_application_error (-20101,'Telefonní číslo je špatně !'); END IF; IF NOT REGEXP_LIKE(:new.email, '^[a-zA-Z0-9\_\.\-]+@[a-zA-Z0-9\_\.\-]+\.[a-zA-Z0-9] {2,3}$') THEN raise_application_error (-20102,'Email je špatně !'); END IF; IF NOT REGEXP_LIKE(:new.rodne_cislo, '^[0-9]{10}$') THEN raise_application_error (-20102,'Rodne číslo je špatně !'); END IF; END; Jedná se o trigger, který kontroluje vstup pro atributy datum_narození, telefon, email a rodné číslo. Jedná se o jednoduchou kontrolu, která neřeší korektnost vstupů na 100%!
6. Přehled databázových dotazů 1. Výpis všech aktuálně nabízených slev SELECT nazev, slevy_typy_aut.popis, FROM Slevy JOIN Slevy_typy_aut ON Slevy.ID_slevy = Slevy_typy_aut.ID_slevy WHERE sysdate BETWEEN Slevy_typy_aut.odkdy_typy_aut AND Slevy_typy_aut.dokdy_typy_aut UNION ALL SELECT nazev, Slevy_sluzby.popis,
FROM Slevy JOIN Slevy_sluzby ON Slevy.ID_slevy = Slevy_sluzby.ID_slevy WHERE sysdate BETWEEN Slevy_sluzby.odkdy_sluzby AND Slevy_sluzby.dokdy_sluzby UNION ALL SELECT nazev, Slevy_vozidla.popis, FROM Slevy JOIN Slevy_vozidla ON Slevy.ID_slevy = Slevy_vozidla.ID_slevy WHERE sysdate BETWEEN Slevy_vozidla.odkdy_vozidla AND Slevy_vozidla.dokdy_vozidla; Dotaz vypíše všechny slevy, které jsou aktuálně nabízeny.
2. Výpis všech nezaplacených položek objednávek daného uživatele SELECT id_vypujky, datum_vypujceni, odkdy, pocet_dni, nvl2(doplatek,'nezaplaceno za skodu : ' || doplatek, 'není') AS 'DOPLATEK ZA ŠKODU' FROM Vypujcky; Dotaz vypíše všechny uživatele co si objednali výpůjčku a dosud nezaplatili a výpůjčku, kde způsobili škodu a za danou škodu ještě nezaplatili.
3. Výpis všech vozidel, které jsou nabízeny k vypůjčení SELECT id_vozidla, nazev_auta, nazev_znacky, Barvy.nazev FROM Vozidla JOIN Typy_aut ON Vozidla.id_vozidla = Typy_aut.id_vozidla JOIN Znacky ON Typy_aut.id_znacky = Znacky.id_znacky JOIN Barvy ON Vozidla.id_barvy = Barvy.id_barvy JOIN Stavy_vozidel ON Vozidla.id_stavu = Stavy_vozidel.id_stavu WHERE stav = 'OK'; Výpis všech vozidel, které jsou v půjčitelném stavu. Jinak řečeno na nich není závada, událost, kvůli které by vozidlo nemohlo být vypůjčeno.
7. Závěr a návrh na vylepšení Při zpracování této práce jsem se snažil podat vyčerpávající informace o mém databázovém modelu. Nicméně z hlediska délky některých částí jsem se rozhodl uvést pouze pár hlavních příkladů, dotazů, nebo omezení, protože uvedení všech by zabralo mnohem více stránek. Jak již bylo řečeno v charakteristice – tato aplikace není tvořena pro větší zakázky. Jednorázové výpůjčky více vozidel firmám by byly výrazným zvýšením zisků pro firmu, která danou databázovou aplikaci využívá a proto by bylo dobré do budoucna rozšířit funkcionalitu modelu, aby byla schopna zpracovávat tyto požadavky a výrazně zlepšila vyhlídky firmy do budoucnosti.
