Realizace databáze pro nový registr vozidel České republiky

Mendelova univerzita v Brně Provozně ekonomická fakulta

Realizace databáze pro nový registr vozidel České republiky Bakalářská práce

Vedoucí práce:

Petr Nohavica

Ing. Michael Štencl, Ph. D.

Brno 2011

Prohlašuji, že jsem tuto bakalářskou práci vytvořil samostatně dle pokynů vedoucího a za použití zdrojů uvedených v seznamu literatury.

V Brně, dne 22. května 2011

....................................................

Především chci poděkovat vedoucímu mé práce Ing. Michaelu Štenclovi, Ph. D., za poskytnuté rady, motivaci, nápady a neutuchající podporu. Mé rodině za podporu nejen při zpracování této práce, ale i při studiu.

4

Abstract Nohavica, P. Implementation of the new registry database for vehicles of the Czech Republic. Bachelor thesis. Brno, 2011. My thesis focuses on connection of the Czech vehicle registry file with the British vehicle registry file. Introduction is conceived as a general overview of Czech legislation concerning road traffic environment. The following description of chosen procedure for developing a database system is supplemented by proposal of advantages arising from realization made in a form of data warehouse.

Abstrakt Nohavica, P. Realizace databáze pro nový registr vozidel České republiky. Bakalářská práce. Brno, 2011. Ve své práci se věnuji problematice spojení souboru českého registru vozidel se souborem registru vozidel Velké Británie. Úvod je koncipován jako obecný náhled do problematiky stavu legislativních nařízení České republiky týkající se ekologie silniční dopravy. Následný popis zvoleného postupu tvorby databázového systému je na závěr doplněn návrhem výhod, které plynou z realizace formou datového skladu.

5

OBSAH

Obsah 1 Úvod a cíl práce 1.1 Cíl práce . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

6 8

2 Aktuální stav 2.1 Vstupní data českého souboru registru vozidel . . . . . . . . . . . . .

9 9

3 Metodika 3.1 Zvolená databázová platforma . . . 3.1.1 Oracle Database . . . . . . 3.1.2 Microsoft SQL Server . . . . 3.2 Relační databáze . . . . . . . . . . 3.3 Porovnání OLAP a OLTP systémů 3.4 Data Warehouse (DWH) . . . . . . 3.5 Dimenzionální modelování . . . . . 3.6 Modelování datového skladu . . . . 3.7 Business Intelligence . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

. . . . . . . . .

4 Vlastní práce 4.1 Stav zdrojového souboru . . . . . . . . . . . . . . . . . 4.2 Úprava dat pro spojení . . . . . . . . . . . . . . . . . . 4.2.1 Český registr vozidel . . . . . . . . . . . . . . . 4.2.2 Anglický registr vozidel . . . . . . . . . . . . . . 4.3 Entitně Relační Diagram (ERD) . . . . . . . . . . . . . 4.3.1 Data Definition Language (DDL) . . . . . . . . 4.3.2 Tvorba relační databáze . . . . . . . . . . . . . 4.4 Tvorba aplikace . . . . . . . . . . . . . . . . . . . . . . 4.4.1 Analýza souborů . . . . . . . . . . . . . . . . . 4.5 Alternativní aplikované CO2 daně v oblasti dopravy . . 4.5.1 Švédský model zdanění . . . . . . . . . . . . . . 4.5.2 Funkce pro výpočet daní dle švédského modelu

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

. . . . . . . . . . . .

. . . . . . . . .

10 10 11 13 14 15 15 16 17 18

. . . . . . . . . . . .

20 20 21 21 24 27 28 28 30 30 33 33 34

5 Diskuze 35 5.1 Problémy řešení relační databáze . . . . . . . . . . . . . . . . . . . . 35 5.2 Varianta řešení formou datového skladu . . . . . . . . . . . . . . . . . 35 6 Závěr

37

OBSAH

6

7 Literatura

39

Přílohy

40

A Ukázka zdrojových kódů

41

B Ukázka prostředí aplikace

46

1

1

ÚVOD A CÍL PRÁCE

7

Úvod a cíl práce

Nejdříve je důležité zhodnotit, jaké legislativní nástroje jsou již v České republice pro podporu ekologie používány. Vláda má již od roku 2007 v plánu řadu úkolů na celkové snížení emisí v zemi. Jeden z nich je také postupná obměna vozového parku za ekologická vozidla. V lednu roku 2009 vešla v platnost novela zákona o silniční dani, která osvobozuje od silniční daně automobily na plyn, elektromobily, vozidla s hybridním pohonem či využívající biopalivo. To platí i pro nové vozidla určená pro přepravu osob používající zemní plyn. Také vozidla na alternativní pohon mladší devíti let jsou zvýhodněny, a to snížením sazby daně o: • 48 % pro automobily mladší 36 měsíců. • 40 % po dobu následujících 36 měsíců. • 25 % po dobu dalších 36 měsíců (ČDS, 2008). Sazba daně se snižuje o 25 % i u vozidel, která jsou určená pro činnosti výrobní povahy v rostlinné výrobě, naopak u vozidel registrovaných v České republice do 31. prosince 1989 se sazba daně zvyšuje o 25 %. Tento způsob daňového zatížení starších automobilů již stimuluje majitele starších automobilů ke zbavování svých vozidel, ovšem nepokrývá ještě celou strukturu vozového parku, a lze jej brát spíše jako přechodnou fázi k razantnějším normám zdanění (ČDS, 2008). Další ekologickou pohnutkou je povinnost uvádět zvláštní technické podmínky ve veřejných zakázkách o nákupech automobilů, a to při jak nákupů automobilů osobních a nákladních, tak při nákupu autobusů. Technické požadavky postihují energetické a ekologické provozní dopady na prostředí týkající se především výše spotřeby, emisní zplodiny CO2 , oxidu dusíků, pevných částic a nemetanových uhlovodíků. Jako další z nástrojů používaných v České republice by mohly být chápány ekologické daně při přepisu automobilů závislé na výši plnění standardu Euro. Ovšem tento poplatek na podporu využití a odstranění autovraků, který se platí pouze jednou při prvním převodu, jednorázově postihne vlnu kupujících zastaralých automobilů, splní svůj účel, ale jako takový nepůsobí jako dlouhodobý stimul rozhodování pro koupi nových vozidel. V současné době se uvažuje o posunu cenové hranice pro jednotlivé Euro normy, a stejně to má být i v následujících letech, což může jako stimul ve výsledku působit. Snaha o ekologizaci dopravy je v nemalé míře i ze strany Evropské unie. Automobilová doprava představuje v Evropě 25 % podíl na celkové tvorbě CO2 . Jde

1

ÚVOD A CÍL PRÁCE

8

například o podporu rozvoje sítě veřejně dostupných vysokonapěťových nabíjecích stanic pro elektromobily po celé Evropě. Myšlenkou je zvýšení podílu ekologických vozidel, ať už jde o elektromobily, vozidla na zemní plyn, biopaliva či vodík. Jedna z dalších metod, kterou již aplikovala řada Evropských států, je zatížení automobilů ekologickou daní, jejíž výše se odvíjí od emisí, které automobil plodí. Způsob výpočtu znevýhodňuje automobily, které zatěžují životní prostředí vysokými emisemi, a na druhé straně ulevuje na daních držitelům ekologicky přátelským vozidlům. Výše škodlivin tedy ovlivňuje výši silniční daně při jejím výpočtu. Tato metoda je tedy schopna pružně postihnout celkový stav vozového parku, a ovlivňovat tak rozhodování při koupi a užívání vozidel. Konkrétní implementaci a způsob výpočtu silničních daní je pro jednotlivé země v Evropské unii různý. Pokud by měla být takto ovlivněna i silniční daň v České republice, bylo by třeba posoudit, zda je již nějaký fungující model v jiné zemi vhodný pro naše podmínky, či se při výpočtu inspirovat více způsoby, a přizpůsobit je na stav vozového parku a daňové soustavy České republiky. Právě touto studií se zabývá diplomová práce Bc. Jany Pospíšilové, která navazuje na tuto bakalářskou práci. Půjde o určení daňového výnosu, který by plynul ze zavedení příslušného modelu zdanění osobních automobilů, stanovení optimální daňové sazby a stanovení kritérií výběru daně. Aby bylo možné studie provádět, je nutné zjistit, zda se v Česku nachází potřebná data, která by poskytovala dostatečné informace pro realizaci tohoto cíle. Data by měla obsahovat všechny automobily, které jsou evidovány na katastrálním území České republiky. Měla by jasně stanovit konkrétní typ automobilu se všemi podrobnostmi, které jej identifikují, a počet kusů od každého typu. Spolu s tímto jsou důležitá také data o emisích, která jednotlivé automobily plodí, aby bylo možné stanovit optimální způsob zdanění na současnou situaci stavu vozidel v Česku. Relevantní informace pro současný stav v České republice se nachází na více místech, z nichž veřejně dostupné jsou pouze dvě. Jsou to: • Svaz dovozců automobilů (SDA), • Centrální registr vozidel (CRV). Statistiky registrací SDA jsou velmi přehledné. Obsahují informace o počtu kusů, druhu paliva a kraje registrace vozidel určité značky za dané období registrovaných od roku 2004. Kritickým nedostatkem je omezení kritérií pouze na výrobce,

1.1

Cíl práce

9

a ne na konkrétní model. Statistiky, které jsou vedeny až od roku 2004, jsou také nedostačující, protože zde starší vozy nejsou zahrnuty. Upřesnění, že požadovaná data v potřebném rozsahu u této organizace neexistují, a kdyby ano, nemohou nám je poskytnout, bylo potvrzeno telefonátem s pracovnicí ústavu. Naopak csv soubor automobilů Centrálního registru vozidel obsahuje kompletní přehled vozového parku v Česku bez časového omezení. Ovšem tento fakt s sebou přináší řadu chyb a omezení, která v daném registru postupem času vznikla, a v nezměněné podobě se uchovávala do dnešní doby. Přesto se zdají být data CRV pro potřeby projektu vhodnější, a následná analýza bude věnována jim.

1.1

Cíl práce

Cílem této bakalářské práce bude výzkumná databáze, pro potřeby ústavu účetnictví a daní PEF. Databáze bude obsahovat kompletní informace o registrovaných vozidlech do 3,5 tuny v České republice. Smyslem je získat adekvátní informace o emisích pro jednotlivé automobily a přiřadit je k již existujícímu souboru českých vozidel, který bude nutné patřičně upravit, aby bylo spojení uskutečnitelné, a aby následné analýzy nad databází neztěžovaly chyby, které původní soubor obsahoval. Fakticky se jedná o simulaci registru vozidel v ČR v podobě, která zohledňuje ekologický dopad. Návrh databáze pro simulaci registru vozidel, obsahující základní údaje o typu vozidla, roce výroby, objemu motoru a množství jednotlivých registrovaných vozidel, je nutné rozšířit o atributy zahrnující škodlivé zplodiny. Obsah rozšířených atributů je klíčový pro výpočet silniční daně automobilů na základě výše emisí. Na závěr je také zhodnocena alternativa realizace databáze formou datového skladu. Tedy zanalyzování přínosů, výhod a omezení, které s sebou oba přístupy přináší, a jejich výsledné ovlivnění projektu a splnění požadavků. Přístup k databázi bude realizován aplikací vytvořenou v on-line vývojovém prostředí Oracle Application Express (APEX). Tvorba této aplikace je v APEXu integrována a umožňuje její výstavbu přímo nad databází, která byla v APEXu vytvořena. Pro samotný výpočet daně budou integrovány funkce či procedury, které mohou být v budoucnu přidávány, rozšiřovány či jakkoliv modifikovány.

2

AKTUÁLNÍ STAV

2 2.1

10

Aktuální stav Vstupní data českého souboru registru vozidel

Podoba dat v souboru odpovídá stavu, pod jakým jsou zavedena v databázi příslušné pověřené obce. Majitelem dat v souboru je tato obec, která v tomto směru gesčně podléhá ministerstvu dopravy, a to je správcem dat. Ministerstvo vnitra není oprávněno tato data jakkoliv upravovat, tedy ani opravit překlepy. Proto se v datech vyskytuje řada chyb, které není možné nijak odstranit. Například je v datech častý výskyt neznámých roků výroby. Rok je nahrazen nulou, což znamená, že rok vůbec není uveden. Z těchto hodnot je tedy zavádějící počítat jakékoliv statistické analýzy. Proto důležitou součástí mé práce je ošetřit data, která negativně ovlivňují, či zkreslují jakékoliv analýzy souboru, a zjistit stav chybně zadaných údajů, které bude třeba zanalyzovat a následně ošetřit například tříděním do skupin. V současnosti je celkem 18 663 vozidel starších roku 1945, z nichž je minimálně 8 569 uloženo zaručeně chybně. Dále 1744 vozidel má jako rok výroby uveden rok 0, a celkem 1763 vozidel je starších než 1700, což je hlavní zdroj chyb. K dalším historickým vozidlům patří 402 vozidel z roku 1880 a 6 318 vozidel z roku 1900 a dále výskyt zhruba 600 záznamů, kde je typu vozidla přiřazen pouze 1 kus. Chyba by šla alespoň částečně eliminovat tím, že by se nulové roky výroby nahradily průměrným rokem pro danou značku a typ. Ovšem právě tento zásah je ministerstvu zakázán, a může si jej provést pouze uživatel s upozorněním, že je takto databáze upravena (MVČR, 2010). Poměrně mnoho řádek (přibližně polovina až dvě třetiny) při podrobnějším členění obsahuje chybu. V datech je údaj buď neúplný, nepřesný, chybějící nebo prokazatelně chybný (nesmyslný) údaj, a je na takový popis jediné vozidlo nebo jen velmi malý počet vozidel. Z hlediska počtu vozidel jsou tyto řádky zanedbatelné (jen zlomky promile), ovšem z hlediska počtu řádek jde o poměrně vysoké procento výskytu chyb. Tento fakt velmi podstatně omezuje možnosti dalšího členění statistik nad tímto souborem (MVČR, 2010).

3

METODIKA

3

11

Metodika

3.1

Zvolená databázová platforma

Před samotnou tvorbou aplikace, je klíčovým krokem volba vhodného databázového systému. Je třeba zhodnotit výhody a nevýhody, které s sebou volba dané alternativy přináší. Zejména se jedná o podporu konkrétního problému vhodnými nástroji, obtížnost a nákladnost s jakou lze databázový systém získat a provozovat (dostupnost), a možnosti rozsahu použití výsledné aplikace v provozu. Na úvod je nutné vysvětlit pojmy, které se týkají dané problematiky. • Databáze jsou perzistentní či strukturovaná data, která využívají aplikační zařízení institucí. Databáze obsahuje vlastnosti jako integrovanost, bezpečnost a snadnější zajištění integrity dat. Perzistentní data jsou data, která přetrvávají i po vypnutí počítače či překračují běh aplikace (Zendulka, 2011). • Systém řízení báze dat je programová vrstva, která řeší operace nad databází jako: vytvoření databáze, vkládání či vyhledávání dat v databázi, atd.(Zendulka, 2011) • Databázový systém obsahuje jak data (databázi), tak systém řízení báze dat (SŘBD), který je součástí celkového programového vybavení, a v širším smyslu i technické prostředky či uživatele databáze (Zendulka, 2011). • Databázový server obsahuje soubor programových prostředků určených pro práci s daty, a to jak organizování, tak realizaci přístupu klientů k těmto datům (Lacko, 2007). • Konkrétní databázová platforma je nejobsáhlejší pojem, zahrnující databázi, databázový server a nástroje pro správu a zabezpečení dat v databázi (Lacko, 2007). • Structured Query Language (SQL) je standardizovaný jazyk, který se používá pro práci s daty v relačních databázích. Databázových platforem je v oblasti databází dnes již celá řada. Z desítek, které jsou celosvětově rozšířeny, lze nejčastěji v prostředí internetu narazit na Oracle Database, Microsoft SQL Server, MySQL a PostgreSQL (Procházka, 2009). MySQL je multiplatformní databáze původem ze Švédska, která je k dispozici, jak bezplatně, tak komerční placenou licencí. Jako všechny ostatní zde uvedené platformy podporuje přístup k databázi pomocí jazyka SQL a přidává ke standardu své jazykové rozšíření. V současnosti je MySQL velmi rozšířený software díky své

3.1


12

bezplatné licenci, a v dubnu 2009 byl spolu se společností Sun odkoupen firmou Oracle, pod jejímž vedením je nadále vyvíjen. PostgreSQL je open source relační databázový systém, který podporuje řadu nástrojů zahrnující uložené procedury, cizí klíče, operace JOIN, pohledy a triggery. Open source v PostgreSQL znamená, že platformní verze jsou volně šiřitelné a jeho zdrojové kódy lze neomezeně používat, modifikovat a distribuovat. Jeho působnost je rozšířena na všechny dostupné operační systémy a obsahuje většinu datových typů, které jsou v normách SQL92 a SQL99 (Procházka, 2009). Platformy Oracle Database a Microsoft SQL Server tvoří v současnosti největší konkurenty na trhu, proto je výčtu jejich vlastností věnován samostatný oddíl pro každou zvlášť. 3.1.1

Oracle Database

Systém striktně podporuje standard SQL92 a SQL99 dotazovacího jazyka SQL, což přináší univerzalitu pro znovupoužití kódu. Výhoda Oracle Database je možnost využití procedurálního rozšíření jazyka SQL (PL/SQL) kódu. PL/SQL je nadstavba jazyka SQL, obsahující prvky procedurálního programovacího jazyka, které dovolují uživateli tvořit uložené procedury, funkce, programové balíky a triggery (spouště). Poprvé se objevuje od verze 6, kde dovoluje psát kusy tzv. ”anonymních bloků kódu”, ovšem bez možnosti uložení procedur či spouští. Tato možnost byla obsažena až od verze 7 z roku 1992. Je považován za tzv. ”Third-generation programming language”(3GL), což znamená pokročilý programovací jazyk zahrnující například lepší podporu souhrnných datových typů, či koncentraci práce programátora na podstatné problémy díky zpracování rutinních záležitostí počítačem (Kyte, 2003). Stejně jako SQL je PL/SQL kompletně přenositelné mezi všemi platformami a verzemi Oraclu. Stejně tak jsou všechny funkce PL/SQL kompatibilní tzv. ”nahoru”, tedy starší verze fungují i na novější výpočetní technice. Lze vytvářet aplikace obsahující PL/SQL bez ohledu na typu platformy (PC, Sun, IBM atd.) (Gurry, Corrigan, 1996). Od verze 10g obsahuje Oracle tzv. zpracování ve ”gridu” (od toho písmeno ”g” v názvu verzí 10g, 11g). Jedná se o princip zvyšování výpočetního výkonu existující informační infrastruktury bez dalších investic do stále výkonnějšího hardwaru. Díky této myšlence, dokáže nakonec Oracle snížit celkové náklady. Princip funguje na rezervě výkonu (procesorů), který si zákazník za příslušný poplatek aktivuje až ve

3.1


13

chvíli, kdy jej potřebuje, a to buď dočasně, nebo dlouhodobě. Za jiné situace jím není výkon využíván (Lacko, 2007). Dalším prvek je architektura Real Application Clusters (RAC). Velká výhoda je, že namísto jednoho vysoce výkonného a nákladného počítače pracuje více méně výkonných a výrazně levnějších počítačů nad jednou velkou databází. Což umožňuje horizontální škálování databázových vrstev, podle toho, jaké nároky má daná aplikace. Systém se také může zvětšovat a zmenšovat podle současných požadavků na výkon díky architektuře sdílených disků. Důležitým aspektem databáze je její bezpečnost. Oracle disponuje tzv. ”transparentním šifrováním dat”, díky němuž je možné šifrovat citlivé soubory na disku, jako jsou čísla kreditních karet a podobně. Použitím Oracle Secure Backup lze šifrovat i zálohy (Lacko, 2007). Při implementaci datového skladu poskytuje firma Oracle řadu nástrojů a produktů, a nelze opomenout, že podíl implementace datových skladů od Oraclu v podnicích je největší na trhu. Oracle Database 11g umožňuje analýzy a dolování dat při minimalizaci nákladů a podporuje vysokou škálovatelnost sítí. Obsahuje rozsáhlou podporu a vysoký výkon pro tvorbu a práci s datovými sklady. Použití Oracle Data Integrator Enterprise Edition dovoluje spravovat integraci a načítání údajů. Což usnadňuje vkládání souborů do databáze. Zvyšuje výkon, dostupnost a ovladatelnost datového skladu pomocí dělení obsáhlých tabulek. Je schopen najít skryté vzory a informace v datovém skladu použitím nástroje Oracle Data Mining. Podporuje rychlejší analytické pohledy pomocí tzv. ”cube-organized pohledů”. Tedy více rozměrného pohlížení na data. Nástroje, které Oracle nabízí pro podporu tvorby datových skladů, jsou zejména: • Oracle OLAP - nástroje, které podporují celé spektrum pokročilých analytických aplikací, včetně plánování, rozpočtování, prognózování, prodeje a marketingu. OLAP také pomáhá identifikovat klíčové trendy v podniku a modelování složitých podnikových scénářů. Možnost použití tvorby ”cube” pohledů rapidně zvyšuje provádění analytických dotazů a dovoluje komplexní přístup k tzv. ”multidimenzionálnímu” pohledu na data. Při použití Oracle OLAP jsou všechna multidimenzionální data uložena přímo do relační Oracle databáze, namísto kopírování dat do proprietárního OLAP serveru. Oracle OLAP dovoluje pohled SQL na data jako multidimenzionální, což umožňuje použití již existujících SQL nástrojů, aplikací a schopností programátorů.

3.1


14

• Oracle Exadata Storage Server - v srdci každého Oracle Database Machine Exadata jsou Oracle Exadata Storage servery, které kombinují chytře uskladněný softwaru a hardware splňující průmyslový standard, aby byl umožněn nejvyšší výkon pro uchování dat v průmyslu. • Oracle Data Mining • Oracle Business Inteligence • Oracle Data Integrator Enterprise Edition Více o problematice Data Warehouse je rozebíráno v následujících oddílech. 3.1.2

Microsoft SQL Server

Je komplexní, výkonná, spolehlivá a bezpečná serverová platforma pro ukládání a správu údajů v databázích a datových skladech. Obsahuje řadu nástrojů pro Business Intelligence (tento pojem je vysvětlen v části níže) včetně pokročilého reportování. Platforma MsSQL Serveru 2008 obsahuje i vývojové prostředí Visual Studio 2008 spolu s technologickou platformou .NET Framework 3.5. Umožňuje také zpracování dat ve vlastních aplikacích díky architektuře orientované na služby - tzv. SOA, zatímco běžní pracovníci používají k získání dat každodenní aplikace typu MS Office. Pro zabezpečení chybného zadání dat uživatelem do systému je obsažen Declarative Management Framework. Tento systém monitoruje změny a brání neautorizovaným zásahům do systémů. Flexibilní alokování systémových zdrojů podle zátěže, limitů a priorit, které může organizace nastavit, zabezpečuje Resource Governor. MS SQL obsahuje také prediktivní systém na optimalizaci výkonu (Lacko, 2008). Pro bezpečnost jsou využívány transparentní šifry, které umožňují zašifrování celé databáze a datových souborů. Pomocí auditování může organizace sledovat, co se s daty děje (např. zda byla přečtena). Obdobou PL/SQL je databázový jazyk LINQ (Language Integrated Query), který dovoluje při vývoji používat programovací jazyk, v jakém je programátor zvyklý pracovat, při znalosti základní konstrukce LINQ. Tento jazyk je zakomponovaný přímo do programovacích jazyků C# a Visual Basic 2008. Dále obsahuje také řadu příkazů pro práci s datovými sklady, jako je MERGE pro hledání existence záznamů, jejich vkládání a aktualizaci, či STAR JOIN pro zkrácení času odezvy při vykonávání dotazu nad datovým skladem (Lacko, 2008). Při výběru je nutné také přihlížet k možnostem, jakými univerzita disponuje. Velkou výhodou je fungující verze Oracle Database 11g spolu s vývojovým

3.2

Relační databáze

15

prostředím APEX, které jsou na univerzitě instalovány a jsou k dispozici studentům. Vývojové prostředí APEX značně ulehčuje přístup a práci s databází. V budoucnu je plánována také instalace Microsoft SQL Serveru, a bude možné i následné porovnání efektivnosti variant. Obě platformy obsahují silné nástroje, a to i pro Business Intellinge a spolu s tím spojenou tvorbu Data Warehouse. Proto volba Oracle Database určitě nebude volbou špatnou, ovšem v praxi by se jistě přihlíželo i k pořizovacím nákladům, které jsou u Oraclu vyšší než u Microsoft SQL Serveru.

3.2

Relační databáze

Při zpracování registru formou relační databáze je kladen důraz na odstranění duplicit v datech. Množství redundancí, které se nachází v původním zdrojovém souboru, by značně zpomalovala rychlost dotazů nad databází. Pro zvýšení rychlosti provádění dotazů, se nabízí několik možností, jak způsob uložení dat upravit, aby nedocházelo ke ztrátám výkonu systému a tím přímo úměrně času potřebného k získání výsledku při prohledávání dat s duplicitami. První možnost je vytvoření schématu, které bude plnit normálové formy, a jednotlivé klíčové atributy budou v oddělených tabulkách spojených cizími klíči. Bude tím zajištěna unikátnost dat o typu vozidla v tabulce obsahující cizí klíč, který odkazuje na tabulku jednotlivých okresů. Ovšem tento způsob snižuje čas vyhodnocení, který je způsoben prohledáváním více tabulek, které jsou spolu spojeny, i jejich samotným spojením. Rozdělení dat do více tabulek je také omezující při tvorbě dotazů nad databází, kdy je nutné, mimo jiné, myslet i na jejich vzájemné provázání. Druhý, méně šťastný způsob, je vypuštění dat, která nepřináší žádnou informační hodnotu danému projektu, a způsobují vznik redundancí. Konkrétně je to informace o okrese, který údaje o vozidle do databáze zadal. Tento fakt nemá pro výzkum celostátního daňového výnosu z výpočtu silniční daně na základě emisních zplodin žádnou přínosnou hodnotu, ovšem znemožňuje vytvoření databáze s unikátními údaji o konkrétních vozidlech. Proto je možné údaj do nově vytvořené databáze nezahrnout a počty jednotlivých vozů v okresech zahrnout do unikátního záznamu o konkrétním vozidle. Tato varianta s sebou přináší značné negativum, které by se mohlo projevit v budoucnu, pokud bude třeba zjistit, či analyzovat data týkající se konkrétních okresů. Protože byl údaj z databáze vynechán, není možné jakékoliv jeho využití v budoucnu. Vynechání jednotlivých atributů a globalizování dat do větších celků

3.3

Porovnání OLAP a OLTP systémů

16

má navíc charakter analytického pohledu na data, a tedy použití datového skladu. Právě v Data Warehouse jde především o agregovaná data, která jsou relevantní pro analýzy.

3.3

Porovnání OLAP a OLTP systémů

Nyní je klíčové porovnat oba přístupy pohledu na data. Klasická relační databáze je koncipována pro pořizování dat. Tomu odpovídá její architektura, a následně databázové schéma, který je silně ovlivněn plněním třetí normální formy. Ve schématu se nachází řada tabulek, které jsou mezi sebou vzájemně propojeny, a ve kterých je, pokud možno, úplně potlačena redundance dat. Proto je použití indexu v datech omezeno na nejnutnější případy. Běžně se OLTP systémy používají v praxi pro získávání údajů při denním provozu, a tedy v reálném čase, kdy je výkon využíván průběžně, a často i na 100%. Naproti tomu analytické systémy jsou určeny především pro optimalizaci dotazování nad databází, což je znatelné na architektuře, která obsahuje nižší počet tabulek, které jsou oproštěny od normálních forem. Typické jsou indexy v datech, často i několikanásobné, duplicity v datech a podobně. Kritický rozdíl je v časových intervalech vkládání dat do databáze. Zatímco u OLTP se data vkládají průběžně, v analytických systémech jsou ukládána data za určité období - v periodách (např. měsíčních, ročních apod.) Lze tedy říci, že data, která jsou shromážděna v OLTP systémech, tvoří zdroj dat pro analytické systémy (Novotný, Pour, Slánský, 2004). Další výhodou datového skladu je sledování historických dat a zavedení pohledu času do databáze. V OLTP se totiž historické změny nedají sledovat. Změní-li se například cena produktu, OLTP nám neumožní získat cenu, která byla před změnou. Přesně tento problém Data Warehouse řeší. Zavedení dimenze času do schématu dovoluje sledovat například vývoj cen za určité období. Pokud zdrojová data registru vozidel obsahují velké množství duplicit a zadání projektu má ryze analytický charakter, odpovídá forma datového skladu situaci více. I kritérium času, které je v datech obsaženo, mají analytické systémy zvládnuto lépe. Umožňují například sledování vývoje emisí, které automobily plodí v jednotlivých letech, či zvýšení počtu nových automobilů zavedením kritických mezníků v čase (například zavedení výpočtu silniční daně dle emisí).

3.4

3.4

Data Warehouse (DWH)

17

Data Warehouse (DWH)

Datový sklad je charakterizován, jako integrovaný, subjektově orientovaný, stálý a časově rozlišený souhrn dat, uspořádaný pro podporu potřeb managementu. Definici lze podrobně rozvést a chápat následovně: • Integrovaný - vkládaná data odpovídají situaci v celém podniku, a ne pouze dílčím oddělením. • Subjektově orientovaný - rozdělení dat se provádí dle jejich typu, a ne dle aplikací, ve kterých byla data pořízena. To znamená, že konkrétní data se vyskytují v databázi pouze jednou, naproti tomu v produkčních systémech se vyskytují na více místech, aby je mohly příslušné aplikace používat. • Stálý - v datových skladech jsou data ukládána z externích zdrojů (například OLTP databází), a ta jsou primárně určena pro čtení a analýzu. Uživatelé je tedy nemohou vkládat ani nijak modifikovat. • Časově rozlišený - dispozice dimenze času v datech umožňuje historické analýzy za určitá období (Novotný, Pour, Slánský, 2004). Současně se zde objevuje pojem Integrovaný datový sklad, který je vystavěn jako centrální datový sklad v podniku, nad tímto skladem jsou následně budována datová tržiště. Datová tržiště mají obdobný koncept, jako DWH, ovšem soustředí se na užší okruh uživatelů v podniku. Lze je tedy chápat jako mezistupeň při budování celopodnikového datového skladu, který je vytvořen z dílčích datových tržišť.

3.5

Dimenzionální modelování

S analytickým přístupem tvorby databáze je úzce spjat pojem dimenzí a tzv. multi-dimenzionálního pohledu na data vůbec. Smyslem dimenzionálního modelu je vytvoření základní logiky uložení nebo uspořádání dat tak, aby splňovala požadavky na analytické a plánovací aplikace v rámci podnikového řízení (Novotný, Pour, Slánský, 2004). Při navrhování jednotlivých dimenzí se volí především: • Identifikace dimenze - podle standardů, které jsou projektu vlastní (např. r vyrobce) • Plný název dimenze - např. Značka výrobce v době vzniku automobilu • Hierarchická struktura dimenze - např. Dimenze Čas, Hierarchie Kalendářní rok - úrovně rok, měsíc, den; Hierarchie Fiskální rok - úrovně rok, čtvrtletí, měsíc, týden, den.

3.6

Modelování datového skladu

18

• Prvky dimenze, které jsou vyjádřeny zejména jejich základní hierarchickou strukturou, např.: 1. Výrobci 1.1. Zahraniční výrobci 1.2. Tuzemští výrobci • Zdroj dat pro dimenzi - zdrojem dat může být např. tabulka číselníků, tabulka v databázi či textový soubor.

Obr. 1: Schéma hvězdy

3.6

Modelování datového skladu

Datový sklad lze realizovat také jako relační databázi, pokud při tvorbě využijeme jedno z databázových schémat hvězdy či sněhové vločky, které jsou uvedeny na obrázcích 1 a 2. Hlavní rozdíl mezi těmito dvěma schématy je vnitřní uspořádání prvků, a to především jejich normalizace. Schéma sněhové vločky (SNOWFLAKE) totiž v případě existence hierarchií v dimenzích uspořádává tabulky do normalizovaných spojení. Mezi souvisejícími úrovněmi v hierarchii tedy vzniká spojení s kardinalitou 1:N. To s sebou přináší značné zhoršení přehlednosti schématu, ovšem na druhé straně usnadňuje práci, pokud jsou dimenze často měněny. Koncepce schématu hvězdy (Star) je naopak značně přehlednější a snazší na tvorbu. Hlavní výhodnou tohoto schématu je výrazně nižší doba odezvy díky denormalizaci tabulek a odpadnutí nutnosti spojení tabulek. Ovšem nevýhoda se projeví, je-li třeba často měnit tabulky či dimenze. Při modelování DWH se objevují dva způsoby chápání tabulek:

3.7

Business Intelligence

19

1. a) tabulka faktů a 2. b) tabulka dimenzí. Tabulka faktů se nachází jako srdce schématu uprostřed, a obsahuje cizí klíče na tabulky dimenzí. Tabulky dimenzí lze chápat jako indexované číselníky, či nositele textových informací o určitých hodnotách, které jsou uloženy v tabulce faktů. Rozhodnutí, zda patřičné pole bude obsaženo v tabulce faktů, či naopak, není jednoduché. Obecně lze pozorovat, zda se veličina mění v čase, a zda jí můžeme měřit, potom patří do tabulky faktů. Pokud se jedná o konstantu, náleží do dimenzionální tabulky (Novotný, Pour, Slánský, 2004).

Obr. 2: Schéma sněhové vločky

Pokud budou správně uspořádány data do dimenzí, lze nad nimi provádět aritmetické a množinové operace, či využívat agregační a statistické funkce (např. AVG, SUM, MAX, STDDEV - pro zjištění směrodatné odchylky).

3.7


Definice je velmi obsáhlá, a přesné chápání pojmu záleží na jednotlivcích a jejich vlastním výkladu. Například v knize Business Intelligence for the Enterprise od Mika Biereho je pro definici tohoto pojmu nechán prázdný kus stránky na začátku knihy s konstatováním, ať si jej čtenář doplní sám, ale doplní jej s rozvahou, protože sám uvidí, jak se mu pro prostudování knihy změní počáteční pohled na věc.

3.7


20

Podle Novotného, Poura a Slánského je BI termín, označující celý komplex činností, úloh a technologií, které dnes stále častěji tvoří běžnou součást řízení podniků a jejich informačních systémů (Novotný, Pour, Slánský, 2004). Podle serveru i OLAP lze termín charakterizovat, jako sběr a analýzu dat, jejímž cílem je lepší porozumění a reakce na změny, kterým organizace čelí, či dle serveru searchCRM.com je Business Intelligence určitá kategorie aplikací a technologií pro sběr, skladování, analyzování a zpřístupňování dat, jejichž účelem je pomoci podnikovým uživatelům dělat lepší rozhodnutí. Vznik tohoto odvětví, a spolu s ním i vznik řady nástrojů a aplikací, lze chápat jako reakci na rapidně rostoucí množství dat v podnicích. Ovšem nejde pouze o samotný nárůst objemu dat, ale také o nekonzistenci a redundanci v datech, která podstatně ztěžuje analýzu a následné rozhodování vedoucích pracovníků v podnicích. Právě tyto problémy, které dnes řeší většina společností, se stávají velkým artiklem. Cena firem, které jsou schopny tyto redundance a jiné kolize odstranit, a nastolit v datech pořádek, aby přinášela organizacím hodnotné a přínosné informace v adekvátních časech, na trhu rapidně roste. Jedním z klíčových nástrojů, které BI využívá je právě datový sklad.

4

VLASTNÍ PRÁCE

4 4.1

21

Vlastní práce Stav zdrojového souboru

Velikost českého registru vozidel neumožňuje jeho import do APEXu vcelku, proto je nutné ji rozdělit. Pro účely této práce přichází v úvahu rozdělit data podle roku první registrace vozidla, protože databáze, ze kterých data získáváme, jsou aktualizovány v jednoročních intervalech. Protože jsou ve zdrojovém souboru data, která jsou vkládána z různých okresů (obcí), nachází se zde duplicity u stejných typů vozidel. Tyto vozidla je třeba vybrat a jejich počty sečíst dohromady a vytvořit jeden identický záznam pro každý konkrétní typ automobilu. Omezující podmínky, které značně brání efektivnímu sumarizování dat, je kritérium území vkládaných dat a podrobný popis řádku. Tento popis by se dal považovat za velice přínosný a upřesňující, pokud by informace z něj byly omezeny a stanoveny pouze na existující a realitě odpovídající fakta o vozidle, a jeho podoba by byla přesně určena například pomocí výběru z nabídky, která by při vkládání byla k dispozici na základě předem vložených údajů o vozidle. Donutí tak uživatele držet se přesných forem zpracování dat, a neumožňuje mu odbočovat od standardu, a znehodnocovat tak snahu o sledování reálného stavu vozového parku či statistických přehledů nad těmito údaji. Nestačí tedy pouze integritní omezení či doplňující podmínka pro kontrolu vkládaných hodnot (např. podmínka pro splnění minimální velikosti čísla). Tato metoda není nijak komplikovaná na realizaci, a ve značné míře se v praxi používá. Ovšem momentální podoba způsobu vkládání informací o přesnějším popisu vozidla umožňuje uživateli zadávat libovolný text do této položky, a proto se u stejného typu automobilů může objevit, a taky objevuje, různá definice typu. Tento důvod brání jednoznačnému sjednocení údajů, a jejich sumarizace se tak stává komplikovanější. Zahrnutí atributu s podrobným popisem s sebou přináší zvýšení počtu záznamů přibližně o jednu třetinu, tedy snížení efektivity, rychlosti a zvýšení duplicit o 30%. Ovšem při rozhodování o zahrnutí či nezahrnutí musí být brán na zřetel další nedostatek zdrojových dat, a to nejednoznačnost uložených údajů o výrobci. U názvů výrobců, které jsou složeny z více než jednoho slova, je způsob ukládání různý. Například značka ”Alfa Romeo” je uložena jako název ”Alfa” u výrobce a ”Romeo” u modelu, což posunuje stěžejní informaci pro třídění o sloupec dál, a to do kri-

4.2

Úprava dat pro spojení

22

tického sloupce s podrobným popisem. Nyní se tedy v místě pro bližší specifikaci vozu, kde u jiných modelů jsou evidovány motorové verze, či typy karoserie, nachází základní název modelu, což přináší nejen zmatek v datech a snížení konkretizace modelu, ale i nutnost tento omezující atribut přiřadit do nové databáze, aby nebyla ztracena možnost pro alespoň obecné určení druhu vozidla. Způsob uložení názvu výrobce do dvou sloupců, se vyskytuje ve více případech. Aby bylo možné data z anglického csv souboru napojit na český registr, je vhodné tyto údaje spojit do jednoho sloupce, a informace o typu automobilu přenést z podrobností do atributu informací o modelu. To lze ošetřit regulárními výrazy a použitím klauzule UPDATE v databázi.

4.2


4.2.1

Český registr vozidel

Nejdříve je třeba zdrojový csv soubor rozdělit na dílčí soubory dle roku první registrace vozidla. Lze to učinit například příkazem grep, kterému jako parametry náleží požadovaný řetězec v záznamech, zdrojový soubor a výsledný soubor. grep 2010 zvstdata.csv > 2010.csv Výsledný soubor je třeba podrobit kontrole, zda nebyl obsažen hledaný řetězec i v jiném sloupci. Pokud je vše v pořádku, je možné nyní upravit csv soubor, aby strukturou odpovídal podobě anglického souboru, a jeho import do APEXu se stal snazším. Názvy sloupců z anglického souboru je tedy možné vložit do souboru českého, a společným atributům přiřadit společné názvy. Pokud budou nyní doplněny přidané sloupce o emisích v českém csv souboru za implicitní hodnoty, které se nemohou ve výsledných datech nacházet, budou automaticky při načítání souboru do APEXu sloupcům přiřazeny správné datové typy. Pokud bude zvolena dostačující velikost implicitních hodnot, přiřadí se automaticky dostatečná velikost i sloupcům, které jsou vytvářeny. Existence implicitních hodnot také umožní následné hledání hodnot, které nebyly nahrazeny, a tedy neúspěšně spojeny, a celkově tak snazší hledání chyb v datech a jejich odstranění. Jako implicitní hodnota byla zvolena číslice ”1111111”. Provedené úpravy dovolují upload csv souboru do databáze. Celý proces uploadu souborů má v sobě zahrnut APEX, a proto hledání kolizí, nekonzistencí a vůbec celková manipulace s csv souborem při nahrávání se stává přehlednější

4.2


23

a snazší. V úspěšně vložené tabulce přichází na řadu odstranění kolizí v zobrazení znaků v názvech. Například: UPDATE ceska 2009 set MANUFACTURER=’SKODA’ WHERE MANUFACTURER LIKE ’%KODA’. Dalším krokem je vytvoření výsledné tabulky pro dílčí léta. Do této tabulky se vloží data z načteného zdrojového souboru, která jsou oproštěna od sloupců, které způsobují duplicity v datech, a jednotlivé typy vozidel jsou spojeny do jediného záznamu, podle pěti kritických kritérií, která jsou jim společná. Jsou to: • • • • •

MANUFACTURER, MODEL, DESCRIPTION, ENGINE CAPACITY, FUEL TYPE.

Počty kusů u záznamů, které jsou těmito atributy shodné, se sečtou, a vzniknou tím unikátní záznamy o vozidlech. Tímto agregačním krokem je snížen počet řádků přibližně na 10% původního souboru. Příklad sumarizace českého souboru: INSERT INTO vysledna 2009 (vehicle type, vehicle category, year, fuel type, engine capacity, manufacturer, model, description, model count, metric urban cold, metric extra urban, metric combined, imperial cold, imperial extra urban, imperial combined, co2 gkm, fuel cost 6000 miles, euro standard, noise level dba, emissions co, emissions hcnox, emissions hc, emissions nox, emissions particulates) SELECT type, vehicle category, year, fuel type, engine capacity, manfacturer, model, description, sum (model count), metric urban cold, metric extra urban, metric combined, imperial cold, imperial extra urban, imperial combined,

4.2


24

co2 gkm, fuel cost 6000 miles, euro standard, noise level dba, emissions co, emissions hcnox, emissions hc, emissions nox, emissions particulates FROM ceska 2009 WHERE fuel type=fuel type AND engine capacity=engine capacity AND manufacturer=manufacturer AND model=model AND description=description GROUP BY type, vehicle category, year, fuel type, engine capacity, manufacturer, model, description, metric urban cold,metric extra urban, metric combined, imperial cold, imperial extra urban,imperial combined, co2 gkm, fuel cost 6000 miles, euro standard, noise level dba, emissions co, emissions hcnox, emissions hc, emissions nox, emissions particulates Následujícím krokem je úprava řetězců ve sloupcích, aby obsah české databáze odpovídal obsahu anglické. Standardně lze tento problém odstranit za pomoci regulárních výrazů a integrovaných funkcí jazyka SQL. Například: UPDATE vysledna 2010 SET model=model ||’ ’|| description WHERE manufacturer LIKE ’mercedesbenz’ and (model LIKE ’a’ OR model LIKE ’b’ OR model LIKE ’c’ OR model LIKE ’cl’ OR model LIKE ’cls’ OR model LIKE ’e’ OR model LIKE ’ml’ OR model LIKE ’s’ OR model LIKE ’sl’ OR model LIKE ’slk’ OR model LIKE ’gl’

4.2


25

OR model LIKE ’sls’)

4.2.2

Anglický registr vozidel

Obdobně je třeba upravit data i ve zdrojovém anglickém souboru. Zde se vyskytuje problém již při importu csv souboru do databáze. Vstupní data z anglického souboru obsahují číselné záznamy, které databáze není schopna importovat jako datový typ ”number”, ale pouze jako ”varchar”. To znamená, že na importovaná data nelze bez úprav vhodně použít funkce MIN, MAX, STDDEV apod., které jsou klíčové pro zjištění odchylky vytvořeného průměru a jiné statické analýzy nad daty v budoucnu. Funkce MIN a MAX lze sice použít pro jakýkoliv datový typ, ovšem pro čísla vložená jako text se chovají tyto funkce nestandardně, a vracejí první hodnotu pro funkci MIN a poslední hodnotu pro funkci MAX, nikoli nejnižší a nejvyšší. Pro funkci STDDEV musí být vstupní hodnoty pouze číselného datového typu. Aby bylo možné s číselnými hodnotami pracovat, je třeba je mít uloženy pod číselným datovým typem ”number”. To ovšem není možno provést nad sloupcem, ve kterém se vyskytují mimo číselných hodnot i hodnoty prázdné, a čísla s desetinným místem, které jsou odděleny tečkou. Je proto třeba upravit zdrojový soubor regulárními výrazy do podoby, která umožňuje všechny sloupce, obsahující číselné hodnoty, uložit jako číselný datový typ. Pro ošetření prvního případu lze například prázdné hodnoty nahradit za číslo ”0”, ovšem je třeba myslet na fakt, že výskyt čísla nula vede při používání agregačních funkcí k odlišným výsledkům od hodnoty ”null”, která není při výpočtu brána v potaz. Proto je po úspěšném importu do databáze nezbytné změnit tyto hodnoty zpět na ”null”. Omezující je také existence znaménka ”.” oddělující desetinnou část číslic. Číslo obsahující tečku se také pro typ ”number” neimportuje úspěšně. Proto jej lze například při importu vynechat, a dané sloupce následně vydělit požadovaným řádem, o který se data liší od originálu. Možná varianta, jako je například změna tečky na čárku, by byla problematická, protože čárka slouží v souboru jako oddělovač, a pokud by byl tento výraz vložen do uvozovek, aby čárka uvnitř čísla nebyla brána jako řídící znak, nastává při importu problém také. Podobná kolize nastává také, pokud řádky ve vstupním souboru obsahují v jednom sloupci znak chápaný, jako oddělovač a celý tento atribut je vložen do uvozovek. Takovéto řádky se sice importují, ovšem jejich celý obsah včetně uvozovek je vložen do jediného sloupce, tedy chybně. Je třeba se uvozovek zbavit, a upravit formu, aby daný údaj odpovídal danému sloupci. Obdobný problém tvoří znaky ”é” a jiné

4.2


26

specifické symboly, které bezprostředně za sebou obsahují čárku. Tento znak, který není schopna databáze zobrazit správně, je prezentován jako nezobrazitelný symbol, ovšem spolu s čárkou, která od něj není zobrazena mezerou. To způsobí nepřečtení čárky, jako dělícího znaku, a spojení dvou sloupců do jednoho, a tím i posunutí všech ostatních sloupců. Vynechání diakritiky u znaků, tedy jejich nahrazení, ušetří mnoho starostí. K souborům je třeba přistupovat individuálně, protože v každém z nich se objevují jiné syntaktické problémy, které znemožňují perfektní nahrání. Jsou to například nelogické čárky v názvech některých atributů, nekonzistentní určení, když není hodnota definována (např. n/a, -, *). Všechny výše uvedené operace jsou realizovatelné pomocí regulárních výrazů a nástrojů, kterými disponují tabulkové procesory, jako například MS Excel, či kancelářský balík OpenOffice. Tyto úpravy umožňují import anglického csv souboru do APEXu. Podrobná specifikace modelu (description) je v anglickém souboru obsažena ve sloupci model, proto je třeba vyextrahovat z ní informaci o modelu a doplňující informace a rozdělit je do dvou sloupců. To lze realizovat pomocí funkcí a regulárních výrazů. Je třeba také ošetřil výraz ”New” na začátku určitých modelů. Tento problém se týká pouze modelů s více názvy, proto nemusíme funkci používat na jednoslovné názvy modelů. Využít lze funkci replace, která vyhledá řetězec vložený jako druhý parametr v textu, který převezme v parametru prvním, a nahradí jej textem, který je vložen jako parametr třetí. replace(lower(model),’new ’,’’) Tento kus kódu je možné využít, pro případné úpravy atributů v databázi, a použít jej jako parametr namísto modelu, s ošetřením slova ”new” na začátku. Specifickým problémem je výrobce značky MINI. Relevantní informace o typu vozu, jsou celé umístěny ve sloupci podrobného popisu (”description”), což je špatně, protože všechny ostatní jsou ve sloupci ”model”. Tento popis je navíc uvozen slovem ”MINI”, za kterým až posléze následuje klíčová specifikace vozidla. Pro přehlednost je tedy výhodnější jej z dat vypustit, nežli při spojení tabulek vytvářet složitou podmínku, která bude zahrnovat regulární výraz, ošetřující tuto nesrovnalost. Příkazy pro úpravy anglické dílčí tabulky: begin UPDATE vysledna 2010 ang SET description =

4.2


27

substr(replace(lower(description), ’MINI ’, ’’), (replace(lower(description),’MINI ’,’’),’ ’)+1, length (replace(lower(description),’MINI ’,’’))) WHERE instr(replace(lower(description),’MINI ’,’’),’ ’)<>0 AND manufacturer = ’MINI’ UPDATE vysledna 2010 ang SET model= (substr(replace(lower(description),’mini ’,’’), 0, (replace(lower(description),’mini ’,’’),’ ’))) WHERE manufacturer=’MINI’ UPDATE vysledna 2010 ang SET description= (substr(description, instr(description,’ ’)+1, length(description))) WHERE manufacturer=’MINI’ UPDATE vysledna 2010 ang SET description=(substr(description, 0,instr(description,’ ’)-1)) WHERE manufacturer=’MINI’ end; Tabulku je třeba seskupit do nově připravené tabulky, aby se nevyskytovaly duplicity v řádcích, omezených potřebnými kritérii. Těmi jsou, stejně jako u databáze české, MANUFACTURER, MODEL, DESCRIPTION, ENGINE CAPACITY a FUEL TYPE. Při vkládání je nutné myslel i na odstranění tečky z původního souboru, a příslušné emise dělit 10, případně 1000. Matematické úpravy atributů do původní podoby: INSERT INTO... SELECT fuel type,engine capacity,manufacturer,model, ROUND(AVG(metric urban cold),1) /10, ROUND(AVG(metric extra urban),1) /10, ROUND(AVG(metric combined),1) /10, ROUND(AVG(imperial extra urban),1) /10, ROUND(AVG(imperial combined),1) /10, ROUND(AVG(co2 gkm),1), ROUND(AVG(fuel cost 12000 miles),1),

4.3

Entitně Relační Diagram (ERD)

28

ROUND(AVG(noise level dba),1) /10, ROUND(AVG(emissions co),3) /1000, ROUND(AVG(emissions hcnox),3) /1000, ROUND(AVG(emissions hc),3) /1000, ROUND(AVG(emissions nox),3) /1000, ROUND(AVG(emissions particulates),3) /1000 FROM... Následuje řada úprav nad gramatickými a stylistickými neshodami zahrnující sjednocení velikosti písmen a ustanovení shodných názvů pro paliva a automobily u obou databází. Při spojování je třeba ošetřil i situace, kdy se na anglickém trhu objevují auta od stejné automobilky pod jiným firemním názvem, a v Česku se vyskytují automobily pod oběma značkami. Pokud se kritérium ve výsledné databázi omezí pouze na výrobce, model, typ pohonné hmoty a objem motoru, stává se přínosnou informací i odchylka dat od průměru sloupců s emisemi. Půjde tedy o velikost průměrného rozdílu přesnosti v datech, o který jsme přišli snížením přísnosti výběrových kritérií o podrobnou specifikaci. Tento údaj je možný získat pomocí funkce STDDEV, která počítá směrodatnou odchylku z hodnot, které můžeme stanovit podle požadovaných omezení. Důvod neúspěšného napojení záznamů s největším počtem kusů je neexistence tohoto modelu na anglickém trhu (např. Renault Thalia, či benzínová verze Škody Octavia 1,6). Podobný problém tvoří také rok první registrace vozidla v České republice, které je v doprodeji z minulého roku či jiné aspekty, které zapříčiní pozdější prodej vozidla. Údaje o emisích se pro tento konkrétní typ vyskytují pouze v souboru vozidel z předešlého roku, a pro úspěšné spojení je třeba využít právě těchto historických dat. Tato operace je korektní i přes to, že rok výroby se neshoduje s rokem první registrace, protože emise modelu, u kterého se takovýto problém vyskytne, odpovídají realitě. Tato situace se ve automobilovém průmyslu vyskytuje velmi často a dotýká se nemalé části vozidel, proto je vhodné ji patřičně ošetřit.

4.3


Protože obrázek hovoří tisíci slovy, je vhodné, a také v praxi velmi často užívané, návrh myšlenky modelované databáze reprezentovat pomocí diagramu. ERD podporuje grafickou reprezentaci všech objektů, jejich atributů a primárních klíčů. Často bývají na diagramu zobrazeny pouze primární klíče tabulek, a dílčí atributy jsou

4.3


29

vynechány. Důvodem je zabránění případnému zmatku na diagramu a ucelení celkové přehlednosti. Myšlenka ERD je přenesení požadavků do reality. Pokud je model příliš velký, je vhodné jej rozdělit do více dílčích diagramů dle jejich předmětu (Ensor, Stevenson, 1997). 4.3.1

Data Definition Language (DDL)

Před přistoupením k samotné tvorbě tabulek, a vkládání dat do nich, je vhodné se seznámit s pojmem Data Definition Language, který je užíván pro potřeby manipulace se strukturou databáze Oracle. Pomocí DDL jsou vytvářeny, upravovány či odstraňovány všechny typy objektů, které mohou v databázi existovat. Pro tyto operace je k dispozici mnoho odlišných funkcí, které obsahují širokou škálu možností a druhů syntaxe (např. ALTER TABLE, ALTER TABLESPACE, ALTER CLUSTER, CREATE TABLE, CREATE DATABASE, CREATE PACKAGE, DROP TABLE, DROP USER, DELETE, atd.) (Kreines, 2000).

Obr. 3: ERD vozového parku

4.3.2

Tvorba relační databáze

Výsledné tabulky s napojenými daty za jednotlivá léta je vhodné sloučit do jednotné relační databáze, která bude plnit normální formy. Dle daných parametrů je vytvořen Entitně Relační Diagram. Následuje tvorba jednotlivých tabulek ERD se sekvencemi

4.3


30

pro primární klíče. Pro tento model lze tabulky chápat jako číselníky (například tabulka výrobce, typu pohonné hmoty a typu dopravního prostředku). Tabulky ERD jsou vytvořeny podle struktury výsledných tabulek za každý rok, a daty (řádky) z těchto tabulek jsou také naplněny. Plnění číselníku probíhá následujícím postupem: 1. Výběr atributu z tabulek za jednotlivá léta, ze kterého jsou relevantní pouze neduplicitní záznamy. To lze ošetřit např. užitím klíčového slova DISTINCT za příkazem SELECT v části zdrojové projekce. 2. Atribut je nahrán do příslušné tabulky (číselníku) tak, aby při vkládání neduplicitních dat za jednotlivé roky nedocházelo k tvorbě duplicit ve výsledné tabulce v důsledku existencí stejných záznamů v tabulkách odlišných let. 3. To lze elegantně vyřešit příkazem MERGE, kdy pouze v nepovinné části WHEN NOT MATCHED proběhne vložení záznamu do nové tabulky. Přesněji řečeno, pokud neexistuje záznam v nově vzniklé tabulce, do které jsou hodnoty vkládány, vloží se, v opačném případě se neprovede nic. Situace, kdy je již obsažen daný záznam, by se také dala ošetřit, ovšem v tomto případě momentálně není alternativa, kde by se této výhody dalo využít. 4. Před vkládáním údajů do tabulky je automaticky volána další hodnota, kterou vrací sekvence, která je vytvořena nad patřičnou tabulkou. Tuto operaci zajišťuje uložený TRIGGER pro jednotlivé tabulky. 5. Tento postup funguje bez problémů i v případě, pokud by se do nově vytvořené tabulky vkládaly například hodnoty z výsledku dotazu, ve kterém by byly v projekci vybrány všechny atributy ze zdrojové tabulky, tedy i sloupec s primárním klíčem (např. ID, které se může shodovat s primárním klíčem v nově vzniklé tabulce). Je to zapříčiněno faktem, že takto vytvořený dotaz při použití klauzule INSERT INTO, nezahrnuje do jeho výstupu právě tento primární klíč (ID). 6. Jako centrální prvek databázového schématu vystává tabulka MODEL, která obsahuje v podstatě všechny atributy původních tabulek, ovšem shromážděny do jedné. Do této tabulky jsou vkládány všechny záznamy z výsledných tabulek jednotlivých let. 7. Na rozdíl od předchozího případu zde není nutné použití příkazu MERGE, a tedy celkové usnadnění provedení požadavku, z důvodu zaručeně rozdílných záznamů pro jednotlivá léta (už jen samotný rok tvoří rozlišovací kritérium). Pro vložení záznamů do tabulky slouží příkaz INSERT INTO za užití výsledku vnořeného dotazu SELECT, který vrací konkrétní data ze zdrojových tabulek.

4.4

Tvorba aplikace

31

8. Po vložení všech záznamů patřičných atributů do nově vzniklým tabulek databáze nastává proces přiřazení identifikátorů namísto konkrétních hodnot uložených v atributech právě do tabulky MODEL. Tato tabulka již byla opatřena přiřazením cizích klíčů k patřičným atributům při vytváření. Nahrazení patřičným klíčem namísto řetězcové hodnoty má smysl v závislosti na rychlosti vykonávání dotazu i přes to, že se musí provádět spojení tabulek. Je to i díky tomu, že výraz JOIN nepředstavuje pro databázi kritickou zátěž, a jeho provedení probíhá rychle.

4.4

Tvorba aplikace

4.4.1

Analýza souborů

Důležitou informaci tvoří již dříve zmiňované analýzy směrodatných odchylek, které vznikají spojením dat o emisích (jejich zprůměrováním) při snížení přesnosti omezovacích kritérií o podrobnou specifikaci modelu, či typu převodovky z důvodu neexistence či nedůvěryhodné existence těchto dat v souboru českého registru vozidel. Na váze tyto analýzy získají zejména při následném výzkumu ústavu účetnictví a daní, kdy je nutné uvádět s jakou přesností či odchylkou se dané způsoby výpočtu silniční daně nad souborem vyskytují. Prvním krokem při tvorbě analýz je zjištění směrodatných odchylek jednotlivých modelů. Odchylky lze získat pomocí již dříve zmiňované funkce STDDEV. Tyto dílčí výsledky ještě samy o sobě nemají příliš velkou vypovídají schopnost. Požadovanou informační hodnotu z těchto dat zle získat například vypočítáním celkové průměrné hodnoty jednotlivých odchylek. Tímto je k dispozici celková odchylka v měrných jednotkách patřičné veličiny. Ovšem i tato informace nemá úplně jasnou vypovídací hodnotu. Bude-li například celková odchylka ”10”, nemusí být uživateli zcela jasné, co si pod tímto pojmem představit. Ovšem srovnání této hodnoty např. vůči celkové průměrné hodnotě veličiny (tedy její podíl na celku) přináší výsledky výrazně jasnější a čitelnější. Například výsledek, že odchylka emisí CO2 tvoří 3 % na celku, lze využít podstatně lépe. Postup pro získání této hodnoty je obdobný, jako při získání odchylek. Rozdíl je v užití funkce AVG namísto STDDEV pro dílčí výsledky u modelů. Dotaz vracející odchylky a průměry: SELECT ROUND(STDDEV(co2 gkm),3) as co 2 odch,

4.4

Tvorba aplikace

32

ROUND(AVG(co2 gkm),3) as co 2 pru FROM ang may 2010 euro 5 5 WHERE (fuel type=fuel type AND engine capacity =engine capacity AND manufacturer=manufacturer AND model=model) GROUP BY fuel type,engine capacity, manufacturer,model Pro získání podílu se celkové odchylky vydělí celkovým průměrem. Tuto hodnotu lze vynásobit stem pro získání procent, a následně ji vhodně zaokrouhlit. round((prumerne odchylky CO 2/co 2 pru celk*100),2)||’%’ as "Podíl změny CO2/Km" Dále lze statistiku rozšiřovat například výpočtem těchto hodnot pouze u záznamů, které byly podmínkou omezeny, a které bylo nutné spojovat. Tedy pouze u těch záznamů, které obsahují odlišné názvy bližších specifikací či odlišné hodnoty emisí u stejného názvu modelu. Jedná se o případy, kde došlo ke spojení více záznamů do jednoho, a kde je z těchto hodnot vytvářen průměr. Jedna z variant možného postupu je rozdělení jednotlivých dotazů na části, a s jejich dílčími výsledky pak pracovat. Pro dotazy tohoto typu je vhodný např. příkaz WITH, který je přizpůsoben rychlejšímu provádění takovýchto rozsáhlých úloh, a snahu o minimální zatížení databáze. Vzhledem k rozsáhlému počtu tabulek je vhodné, aby byla tabulka, ze které se průměry počítají, například parametrem funkce. Název tabulky se tedy přiřadí do parametru až při samotném konkrétním dotazu, kde je tato funkce volána, a nemusí se opisovat celý dotaz pro jednotlivé případy. Alternativní možnost, jako je například tvorba pohledu nad více tabulkami, je sice snadná na aplikaci, ovšem při nepřiměřeném vytížení databáze. Všeobecným pravidlem je, že není vhodné volit postup, který usnadňuje práci programátora, a zpomaluje práci uživatele. Zejména při tvorbě analýz nad obsáhlými soubory dat se zvolená metodika a použité prostředky na rychlosti výpočtu projeví nejvíce. Proto byl zvolen postup pomocí tvorby funkcí s parametrem přebírajícím název tabulky. Aby bylo možné použít tabulku jako parametr, je třeba myslet na to, že jde o dynamický typ funkce. Pro parametr lze například deklarovat vlastní typ tabulky (např. v rámci balíku - Package). Tento typ je následně použit pro parametr

4.4

Tvorba aplikace

33

v hlavičce vytvářené funkce. Operace, které jsou při takto deklarovaném parametru prováděny v rámci bloku ”begin” a ”end”, ovšem neproběhnout v pořádku. Tyto chyby se dají ošetřit použitím kurzoru, kde se jako název zdrojové tabulky pro dotaz použije parametr z hlavičky funkce. Tento parametr bude ovšem znakového typu VARCHAR2, a bude v dotazu volán pomocí operátoru zřetězení. Protože je název zdrojové tabulky volán až za ”běhu”, je nutné, aby byla funkce dynamická (resp. kurzor v ní obsažený). To znamená deklaraci typu kurzoru jako dynamický REF CURSOR, který se následně v sekci ”begin” a ”end” otevírá pro dotaz, ve kterém je volána tabulka, jako parametr. Funkce pro analýzu odchylek: CREATE OR REPLACE FUNCTION vypis odch(tname varchar2) RETURN number AS TYPE dynamic cursor IS REF CURSOR; refcur dynamic cursor; v co number:=0; BEGIN OPEN refcur for ’SELECT ROUND(AVG(STDDEV(co2 gkm)),3) AS co 2 odch FROM ’||tname ||’ WHERE (fuel type=fuel type AND engine capacity=engine capacity AND manufacturer=manufacturer AND model=model) GROUP BY fuel type,engine capacity, manufacturer,model’; FETCH refcur INTO v co; RETURN(v co); END; Při použití v aplikaci vytvářené v APEXu je nutné při výpisu dotazu, aby vracel hodnoty. Tím pádem není možné v rámci listu REPORT užití uložené procedury, která smí vypisovat pouze na standardní výstup. A protože funkce vrací pouze jednu hodnotu, není možné pomocí ní hromadně vypsat jednotlivé odchylky pro všechny tabulky, aby se s údaji dalo následně vhodně pracovat.

4.5

Alternativní aplikované CO2 daně v oblasti dopravy

4.5

34


Studie, navazující na tuto práci, bude vycházet ze specifických národních daní jednotlivých členských států Evropské unie, které zdanění CO2 u motorových vozidel již používají. V současné době se nejvíce uvažuje o aplikaci obdobných typů zdanění, jaké jsou používány ve Švédsku, případně ve Velké Británii. Následující stručný popis způsobu výpočtu daní je uveden z důvodu jejich vložení do výsledné aplikace. Pomocí výsledků získaných z aplikace těchto zdanění bude možné následně analyzovat dopad zdanění na aktuální stav vozového parku v Česku. 4.5.1

Švédský model zdanění

Ve Švédsku funguje tzv. systém roční cirkulační daně založené na emisích CO2 , kde základ pro sazbu daně je 360 švédských korun (SEK), ke kterému jsou přičítány další hodnoty převyšující stanovenou mez. Tato celková hodnota daně může být následně násobena koeficientem podle typu vozidla či roku jeho první registrace. V zákoně o silničních daních došlo od 1. ledna 2011 k následujícím novelám, které platí pro vozidla splňující alespoň Euro 4: • k základu 360 SEK se přičítá za každý gram CO2 20 SEK převyšujících 120 g/km, • pro dieselová vozidla se základ násobí koeficientem 2,55, • pro dieselová vozidla s datem první registrace po 1. lednu 2008 je dodatečná daň ve výši 250 SEK, • pro dieselová vozidla s datem první registrace před 1. lednem 2008 je dodatečná daň ve výši 500 SEK. (ACEA, 2010) Pro vozidla na alternativní pohon se daň 10 SEK za každý gram CO2 nad 120 g/km nemění, a zůstává ve stejné výši. Pro vozidla, která nesplňují normu Euro 4 a vyšší, se počítá daň podle jejich váhy. Vozidla jsou rozstřižena do tří skupin: • do 900 kg SEK 720 SEK 2,290 • 901-1000 kg SEK 903 SEK 2,870 • za každých dalších 100 kg + SEK 183 + SEK 580.

4.5


35

Při nákupu tzv. environmentálně šetrných vozidel je ve Švédsku poskytována prémie 10 000 švédských korun. Platí to pro vozidla: • vozidla s emisemi CO2 nižšími než 120 g/km; • vozidla na alternativní pohon s maximální spotřebou 9,2 l/100 km u benzínových, 8,4 l/100 km u naftových a 9,7 cm/100 km u plynových pohonů; • elektromobily s maximální spotřebou 37 kWh/100 km. (ACEA, 2010) 4.5.2

Funkce pro výpočet daní dle švédského modelu

Při tvorbě funkce tohoto typu nedochází k žádným zásadním komplikacím. Švédský model je velmi dobře navržen pro podporu eGovermentu a pravidla jeho výpočtu jsou velmi dobře algoritmizovatelná. Ve své podstatě se jedná o standardní funkci, která v parametrech přebírá proměnné, a na základě podmínek, které splňují, vrací příslušné numerické hodnoty. V tomto konkrétním případě jde o čtyři hodnotící kritéria: • • • •

rok, typ pohonné hmoty, euro standard a zplodiny CO2 .

Na základě vyhodnocení skutečností, jaké tyto vstupní parametry mají, jsou z nich v rámci jednotlivých větví podmínky ”IF THEN” počítány výsledné daně. Při výpisu daní je pro lepší přehlednost a představu vhodné začlenit také výši daně přepočítanou aktuálním kurzem na české koruny (CZK).

5

DISKUZE

5 5.1

36

Diskuze Problémy řešení relační databáze

Velké množství duplicit a nejasností v registru závislých na způsobu vkládání dat se již dávno stalo kritickým pro jakékoliv relevantní statistiky. Soubor obsahující přes tři čtvrtě druhého milionu záznamů nabírá rapidně na rozměrech bez žádných zásadních změn v systematice postupu tvorby nových záznamů. Nastolení řádu pro použití v relační databázi, jak jej řeší tato bakalářská práce, s sebou nese řadu komplikací. Hlavním rysem je odstranění duplicit na základě vynechání kritérií, které nepřináší patřičnou informační hodnotu pro potřeby navazující studie. Ovšem týká se to pouze této konkrétní situace, nikoliv globálního pokrytí tohoto problému. V praxi bývají inovace reagující na změny ve společnosti doprovázeny dlouhým obdobím přechodu, ve kterém neustále dochází k výzkumu a hodnocení alternativ, a vědomé vypuštění či ztráta jakékoliv informace, která je již v datech uložena, není v rámci univerzality často přípustné. Drobné změny, či úpravy v systému mohou být přínosné, ovšem ne vždy jsou efektivní a jsou schopny obstát i v budoucnu. Variantou může být investice většího rozsahu do kompletní změny systému evidence registru. Ovšem prostředky státního rozpočtu jsou omezené, a příliš mnoho faktorů napomáhá odkladu podobných rapidních zásahů do systémů, a bývají voleny úspornější dočasné úpravy. V takovém případě se nadále budou hromadit data obsahující nemalou část chybných údajů, které znemožňují jejich zpracování, a přinášejí neustálou nutnost jejich úprav do použitelné podoby. Pokud by nedošlo k úpravách se snahou o minimalizaci duplicit a sumarizaci dat z důvodu zachování všech kritérií souboru, a atributy s emisemi by byly připojeny ke každému jednotlivému řádku původního souboru, staly by se postupem času analýzy časově neúnosné. Skutečnost, že v rámci bakalářské práce je ošetřena pouze skupina osobních automobilů, nastiňuje časovou náročnost zpracování tohoto souboru. Při zpracování všech skupin obsahujících mimo jiné i motocykly, které jsou obsahem následující práce nad souborem, by potřebný čas neúměrně narůstal.

5.2

Varianta řešení formou datového skladu

Na návrh registru vozidel se dá dívat také z alternativního pohledu modelování databáze. Struktura a datové složení relační databáze ve své podstatě odpovídají myšlenkám dříve zmíněného datového skladu v kapitole Metodika a sekcích Data Warehouse a Modelování datového skladu.

5.2

Varianta řešení formou datového skladu

37

Data Warehouse má k dispozici řadu nástrojů, kterými Oracle disponuje. Jedná se zejména o OLAP nástroje, které jsou popsány v sekci Zvolená databázová platforma a části věnované platformě Oracle. Ovšem nejde pouze o nástroje a nadstavby, které s sebou datový sklad přináší. Hlavní výhoda je schována ještě hlouběji v samotné myšlence a důvodu vzniku datového skladu, který tvoří nedílnou součást vyššího celku Bussines Intelligence. Tento pojem je také zmiňován v kapitole Metodika, a jeho vznik je spojen s neúnosným hromaděním dat v podnicích a kumulující se nepřehledností a nekonzistencí v datech. Jeho koncepce a nástroje tomu přizpůsobené optimalizují práci s takovými daty, nastolují v nich pořádek a umožňují provádět nad nimi operace týkající se analýzy či dolování informací z dat. Právě v takové situaci se nachází stav českého registru vozidel, a použití těchto nástrojů pro analýzu a zpracování se s rostoucím počtem dat stává čím dál nutnější. Při podrobnějším pohledu na formu, jakou je datový sklad koncipován, lze poznat řadu shod se schématem nynější relační databáze. Například centrální tabulka faktů, která by se v relačním schématu registru vozidel dala chápat jako tabulka MODEL s vazbami na ostatní tabulky. Právě na tyto tabulky, které nyní tvoří číselníky ukládající datové hodnoty, se dá dívat jako na dimenze datového skladu, a při zavedení dimenze času je vytvořeno základní schéma Hvězdy (Star). Výhodou realizace formou datového skladu by bylo jak zrychlení vykonávání dotazů, tak možnost kompletního zahrnutí všech údajů, i redundantních, ze zdrojových souborů, aniž by došlo v čase zpracování dotazů nad DW k závažným zvýšením. Částečně by tato varianta řešila i problém zmiňovaný výše, o neustálém růstu dat a problému jejich zpracování. Datové sklady jsou totiž koncipovány ke zpracování v řádech milionů záznamů, a to především pro provádění komplikovaných analytických dotazů namísto každodenního plnění databáze údaji. Proto vkládání dat na jednotlivých okrscích by mohlo zůstat formou relační databáze, a za patřičné období by se tato data souhrnně vložila do datového skladu určeného pro jejich statistické zpracování. Celým tímto postupem ”datových pump”, které plní daty datové sklady, se mimo jiné zabývá problematika Business Intelligence.

6

6

ZÁVĚR

38

Závěr

Společnost věnuje čím dál větší pozornost ekologickým dopadům produkce statků, které nám usnadňují a zpříjemňují život již po mnoho let. Tlak na snižování škodlivin, které jsou jako nežádoucí jev plozeny jak při výrobě, tak při samotném používání výrobků, roste přímo úměrně se schopností člověka sledovat dopad jejich působení na jeho okolí. Touha člověka po hmotných statcích s sebou přináší tvrdou daň, která dopadá ze všeho nejvíc na naše okolí. Dopadá na místa, která člověk nedokáže vytvořit ani nahradit. Proto dostává přednost úsilí o zachování životního prostředí a omezení našeho blaha bezmyšlenkovým plýtváním a ničením přírodních zdrojů. Tento fakt si uvědomuje i Evropská unie a dlouhodobě trend snížení vypouštěných škodlivin do ovzduší podporuje, ať už legislativně tak v podobě tučných dotací do všech sfér ekologických aktivit. Vize uspokojení lidských potřeb v co nejlepším souladu s přírodou se promítá do všech odvětví a na automobilový průmysl, patřící k největším zátěžím současného životního prostředí Evropy i světa, jsou kladena čím dál přísnější kritéria omezující jeho negativní dopad. Pozůstatky z dřívější doby, kdy vývoji automobilového průmyslu nebyly kladeny žádné významné omezení, a pozornost výrobců i společnosti se upínala především na přínosy inovačních technologií a rostoucích výkonů, zasahují značnou měrou i do doby současné, ve které se dostala do popředí společenských hodnot ochrana přírody. Existence zastaralých automobilů ve vozových parcích všech států značně snižuje efektivnost ekologického snažení vlád i občanů. Současným cílem je odstranění co možná největšího počtu neekologických automobilů a obměnu vozových parků novými, které plní čím dál více přísnější ekologické normy. Tato úloha není jednoduchá. Koupěschopnost a ochota obyvatelstva dávat přednost dražším a ekologicky šetrnějším automobilům není v situaci, aby z vlastní vůle chtěli břímě ekologické zátěže nést, a omezovat tak vlastní komfort. Zájem vlády je proto pomocí prostředků, kterými disponuje, stimulovat jednání obyvatel a znevýhodnit vlastnictví a koupi zastaralých a neekologických vozidel. Cílem této práce bylo vytvoření výzkumné databáze simulující nový registr vozidel ČR reflektující údaje o emisích jednotlivých vozidel, aby umožnil odvození nového výpočtu daňového výnosu zohledňujícího emise. Tento cíl byl splněn pro rozsah dodaných dat. Experimnetální registr tak obsahuje data pro vozidla v rozsahu deseti let po rok 2010. Konkrétně to znamená přiřazení atributů o emisích přib-

6

ZÁVĚR

39

ližně milionu a půl kusům vozidel. Celkový počet kusů osobních automobilů je čtyři a půl milionu, z nichž relevantní záznamy (záznamy s počtem kusů vyšším než dva) obsahují přibližně 3,7 milionů kusů vozidel. Úspěšně spojeno bylo přibližně 34 % záznamů (42 % v relevantních datech). Tato úspešně napojená data znamenají přibližně 81 % vozidel registrovaných v posledních deseti letech, která se týkala spojení s anglickým registrem. Důvody neúspěšného napojení jsou zmiňovány výše v závěru sekce Úprava dat pro spojení. Řešením tohoto problému může být získání dat z německého registru vozidel, která jsou volně přístupná pouze ve formátu pdf, proto by manipulace s takovými soubory znamenala volbu odlišné metodiky zpracování a její následný popis, což by přesáhlo rozsah bakalářské práce. Soubory s německými daty ovšem budou zpracovány v navazujícím plánu pokračování projektu, kdy pro účely výzkumu ústavu účetnictví a daní PEF se do již vytvořené databáze přidají data pro motocykly, kde údaje tohoto typu obsahuje pouze německý registr. Vozidla, pro která neexistují atributy o emisích, budou tříděna do skupin a následně bude výpočet silniční daně upraven podle atributů, které jsou k dispozici. Tento fakt je ovlivněn pozvolným nástupem legislativních stanov v minulém desetiletí, kdy jednotlivé země dle těchto nařízení přizpůsobovaly ukládání registrů vozidel. Pokud by se myšlenka zdanění vozidel dle emisí inspirovaná státy Evropské unie, kde již tato zdanění několik let fungují, prosadila i v naší republice, a byly by pro její kvalitní zpracování vytvořeny dostačující prostředky, mohla by být také tímto přerozdělovacím stimulem pro omlazení vozového parku. Vše, co je k tomu třeba, jsou příznivé výsledky zkušebních analýz inovativních silničních daní, které budou podloženy zaručenými údaji, zprostředkovanými aplikací umožňující zpracování všech relevantních údajů, které jsou k dispozici, v adekvátních časových odezvách.

7

7

LITERATURA

40

Literatura

ACEA. ACEA Tax Guide 2010. Dokument ve formátu PDF. ACEA, [cit. 2011-5-12]. URL: http://www.acea.be/index.php/news/news detail/acea tax guide 2011/. Enson, D., Stevenson, I. Oracle Design. 1. vyd. Beijeing: O’Reilly, 1997, ISBN 156592-268-9. Gurry, M., Corrigan, P. Oracle Performance Tuning. 2. vyd. Beijing: O’Reilly, 1996, ISBN 1-56592-237-9. Kreines, D. C. Oracle SQL : The Essential Reference. 1. vyd. Beijing: O’Reilly, 2000, ISBN 1-56592-697-8. Kyte, T. Effective Oracle by Design. 1. vyd. New York: McGraw-Hill/Osborne, 2003, ISBN 0-07-223065-7. Lacko, L. Oracle: Správa, programování a použití databázového systému. Brno: Computer press, 2007, 576 s. ISBN 80-251-1490-2. Lacko, L. Jak vyzrát na Microsoft SQL Server 2008. 1. vyd. [s.l.]: Computer press, 2009, 469 s. ISBN 978-80-251-2101-6. Ministerstvo financí České republiky. Česká daňová správa [online]. MFČR, 2006 [cit. 2011-4-12]. URL: http://cds.mfcr.cz/cps/rde/xchg/SID-3EA9846D16DB1782/cds/xsl/9 8521.html. MVČR. Centrální registr vozidel [online]. MVČR, 1. února 2011 [cit. 20114-28]. URL: http://www.mvcr.cz/clanek/centralni-registr-vozidel865510.aspx?q=Y2hudW09NA%3d%3d. Novotný, O., Pour, J., Slánský, D. Business Intelligence : Jak využít bohatství ve vašich datech. Vyd.1. Praha: Grada Publishing, 2004, 256 s. ISBN 80-2471094-3. Procházka, D. Oracle: průvodce správou, využitím a programováním nad databázovým systémem. Praha: Grada Publishing, 2009, 168 s. ISBN 978-80-247-27622. Zendulka, J. Databázové systémy 1 Úvod [online]. VUT v Brně, 10. května 2011 [cit. 2011-5-10]. URL: http://www.fit.vutbr.cz/study/courses/DSI/public/pdf/nove/1 uvod.pdf.

Přílohy

A

A

UKÁZKA ZDROJOVÝCH KÓDŮ

42

Ukázka zdrojových kódů

Příkaz na spojení atributů z výsledné anglické tabulky do výsledné české za jednotlivé roky: MERGE INTO vysledna 2009 c USING (SELECT euro standard, fuel type, engine capacity ,manufacturer ,model ,metric urban cold, metric extra urban,metric combined,imperial extra urban, imperial combined,co2 gkm,fuel cost 6000 miles, noise level dba,emissions co,emissions hcnox, emissions hc,emissions nox,emissions particulates FROM vysledna 2009 ang eu4 3) a ON (c.manufacturer =a.manufacturer AND c.fuel type=a.fuel type AND c.engine capacity=a.engine capacity AND c.model =a.model ) WHEN MATCHED THEN UPDATE SET c.metric urban cold=a.metric urban cold, c.metric extra urban=a.metric extra urban, c.metric combined=a.metric combined, c.imperial extra urban=a.imperial extra urban, c.imperial combined=a.imperial combined, c.co2 gkm=a.co2 gkm, c.fuel cost 6000 miles=a.fuel cost 6000 miles, c.noise level dba=a.noise level dba, c.emissions co=a.emissions co, c.emissions hcnox=a.emissions hcnox, c.emissions hc=a.emissions hc, c.emissions nox=a.emissions nox, c.emissions particulates=a.emissions particulates, c.euro standard=a.euro standard; Tvorba tabulek: CREATE TABLE manufacturers

A


43

(id man number not null primary key, man name varchar2(100) not null); Tvorba sekvencí: CREATE SEQUENCE manufacturer seq MINVALUE 1 START WITH 1 INCREMENT by 1; Trigger pro automatické vkládání ID při vložení záznamů do tabulky: CREATE OR REPLACE trigger sekvence man BEFORE INSERT ON manufacturers FOR EACH ROW BEGIN SELECT manufacturer seq.nextval into :new.id man FROM dual; END; Příkaz na vkládání unikátních dat do číselníků: MERGE into manufacturers m USING (SELECT distinct manufacturer FROM vysledna 2006 ORDER BY manufacturer) puv ON (puv.manufacturer=m.man name) WHEN NOT MATCHED THEN INSERT (m.man name) VALUES (puv.manufacturer); Operace pro úpravu výsledných tabulek pro relační databázi: UPDATE vysledna 2006 SET manufacturer= (SELECT id man FROM manufacturers WHERE manufacturer=man name); Funkce na výpočet celkového průměru v datech: CREATE OR REPLACE function vypis prum(tname varchar2)

A


44

RETURN number AS TYPE dynamic cursor IS ref cursor; refcur dynamic cursor; v prum number:=0; BEGIN OPEN refcur for ’SELECT round(avg(avg(co2 gkm)),3) AS co 2 pru FROM ’||tname ||’ WHERE (fuel TYPE=fuel TYPE AND engine capacity =engine capacity AND manufacturer=manufacturer AND model=model) GROUP BY fuel TYPE,engine capacity, manufacturer,model ’; FETCH refcur into v prum; RETURN(v prum); END; Tato funkce je použita ve výsledné aplikaci pro výpis relativního podílu odchylek na celku: CREATE OR REPLACE function vypis prum(tname varchar2) SELECT ’2010’ AS "year", ’5’ AS "euro standard", vypis odch(’ang may 2010 euro 5 5’) AS "odchylka", round(vypis odch(’ang may 2010 euro 5 5’)/ vypis prum(’ang may 2010 euro 5 5’)*100,2) || ’ %’ AS "poměr na celku" FROM dual;

Příkaz pro výpočet napojených záznamů z celku: WITH napojeno as (SELECT sum(model count) AS sum nap FROM model WHERE metric urban cold<>1111111 ), celkove as

A


45

(SELECT sum(model count) AS sum celk FROM model) SELECT round((sum nap/ (SELECT sum celk FROM celkove)*100),2) ||’%’ AS "celkově napojeno" FROM napojeno; Funkce pro výpočet daní podle švédského modelu: CREATE OR REPLACE function swedish tax (p year in number,p fuel TYPE in varchar2, p euro in varchar2, p co2 in number) RETURN number IS v tax number:=0; BEGIN IF (p euro>=4) AND (p fuel TYPE=’nm’) AND (p year >=2008) AND (p co2>120) THEN v tax:=2.55*(250 + (20*(p co2 - 120))); ELSIF (p euro>=4) AND (p fuel TYPE=’nm’) AND (p year >=2008) AND (p co2<120) THEN v tax:=2.55*250; ELSIF (p euro>=4) AND (p fuel TYPE=’nm’) AND (p year <2008) AND (p co2>120) THEN v tax:=2.55*(500 + (20*(p co2 - 120))); ELSIF (p euro>=4) AND (p fuel TYPE=’nm’) AND (p year <2008) AND (p co2<120) THEN v tax:=2.55*500; ELSIF (p euro>=4) AND (p co2>120) AND ((p fuel TYPE=’el’) or (p fuel TYPE=’cn’) or (p fuel TYPE=’ep’)or (p fuel TYPE=’zp’)) THEN v tax:=360 + (10*(p co2 - 120)); ELSIF (p euro>=4) THEN v tax:=360 + (20*(p co2 - 120)); ELSE v tax:=0; END IF; RETURN v tax; END;

A


46

Příkaz s využitím funkce swedish tax: SELECT mr.man name "manufacturer", m.model "model", f.TYPE name "fuel type", m.engine capacity "engine capacity (ccm)", m.year "year", m.model count "model count", m.euro standard "euro standard",m.co2 gkm "co2 (g/km)", swedish tax(year,f.TYPE name, euro standard, co2 gkm)|| ’ (’ || round(swedish tax(year,f.TYPE name, euro standard, co2 gkm)*2.73,2) || ’ czk)’ AS "swedish tax (sek)" FROM model m JOIN vehicle TYPE v ON m.vehicle TYPE=v.id veh TYPE JOIN manufacturers mr ON m.manufacturer=mr.id man JOIN fuel type f ON m.fuel type=f.id fuel WHERE metric urban cold<>1111111 ORDER BY mr.man name, m.model, f.TYPE name,m.engine capacity; Příkaz pro průměrné taxy za jednotlivá léta zobrazený v grafu: SELECT null, year, avg(swedish tax(year,f.TYPE name, euro standard, co2 gkm)) AS sw tax FROM model m JOIN fuel type f ON m.fuel type=f.id fuel WHERE metric urban cold<>1111111 AND euro standard >=4 GROUP BY year ORDER BY year;

B

B

UKÁZKA PROSTŘEDÍ APLIKACE

Ukázka prostředí aplikace

Obr. 4: Ukázka prostředí aplikace

47

B

UKÁZKA PROSTŘEDÍ APLIKACE

Obr. 5: Ukázka prostředí aplikace 2

Obr. 6: Ukázka prostředí aplikace 3

48

Realizace databáze pro nový registr vozidel České republiky

Recommend Documents