u r j i SCI Databázové systémy v datové vědě Veronika EcLerová eclerova@math.muni.cz Přírodovědecká fakulta, Masarykova Universita 6. května 2024 Úvod do databázových systémů Úvod do datové vědy Entity-relationship modeL Relační databáze Úvod do jazyka SOL Základní příkazy jazyka SOL DDL - vytváření tabulek, mazání tabulek, úprava tabulek pohledy, funkce, větvení Systémové databáze a jejich funkce Návrh datových skladů Základní pojmy Dimensionální databáze Databáze a aplikace Velká data Balíček DBI a odbc Client-server architecture Balíček dplyr Modelování v R V.Eclerová • Databáze • 6. května 2024 2/84 Úvod do databázových systémů Datová věda Jsou soubory diskrétních nebo spojitých hodnot nesoucí elementární význam. Sdělují informace, popisují kvalitu, kvantitu nebo fakta. Metadata Jsou data o datech. Poskytují informace o různých aspektech dat, jako je jejich struktura, formát, zdroj, kvalita a význam. Databáze Databáze umožňují Lidem sledovat a shromažďovat data. V.Eclerová • Databáze • 6. května 2024 3/84 Úvod do databázových systémů Datová věda Datová věda Data Science Věda založená na datech Data intensive science metody pro sběr dat zpracování dat interpretace dat O Sběr dat Ukladaní dat astronomie, molekulární biologie, epidemiologie... objev jsou podpořeny nasbíranými a zpracovanými daty C3 Interpretace o A Zpracování dat V.Eclerová • Databáze • 6. května 2024 4/84 Uvod do databázových systémů Datová veda WISDOM KNOWLEDGE INFORMATION DATA Úvod do databázových systémů Datová věda Postup při návrhu operační databáze Operační systémy: někdy nazývány online transaction processing (OLTP) slouží k získávání, změně a ukládání dat (typicky prostřednictvím relační databáze). ■ analýza ■ tvorba datového modelu ■ návrh designu databáze ■ implementace ' Cíle 1. uklání dat 2. změna dat 3. vyhodnocení dat V.Eclerová • Databáze • 6. května 2024 6/84 Úvod do databázových systémů E-R model Tvorba datového modelu Entity-relationship model = E-R model I ■ entita = konkrétní věc, kterou chce uživatel sledovat (například konkrétní územní celky: okres Benešov, Středočeský kraj) ■ třída entit = soubor všech entit (např. všech okresů, krajů) ■ atribut = charakteristika dané entity Speciálními atributy jsou identifikátory. Jedná se například o atributy příjmení, název, kód (např. CZNUTS). Kód CZNUTS je jednoznačný identifikátor. Naopak příjmení obecně není jednoznačný identifikátor. Jméno a příjmení je složený identifikátor. V.Eclerová • Databáze • 6. května 2024 7/84 Úvod do databázových systémů E-R model Tvorba datového modelu Entity-relationship model = E-R model II ■ vztah (relationship) = vazba entity na jinou entitu Binární vztah Vztah mezi dvěma třídami entit. Existuje několik typů binárních vztahů ■ 1:1 - one-to-one ■ 1:N - one-to-many ■ N:M - many-to-many Speciálním typem vztahů jsou rekurzivní vztahy, které mohou sloužit například k definování stromových struktur. ■ ke grafické reprezentaci E-R modelu slouží entity-relationship (E-R) diagramy V.Eclerová • Databáze • 6. května 2024 8/84 Úvod do databázových systémů E-R model V.Eclerová • Databáze • 6. května 2024 9/84 Úvod do databázových systémů Relační databáze Relační databáze ■ základním stavebním blokem jsou datové tabulky neboli relace ■ v datové tabulce jsou uložena data týkající se jedné oblasti ■ řádky datové tabulky = záznamy: obsahují data týkající se konrétního případu/výskytu = instance ■ sloupce datové tabulky = pole: obsahují konkrétní charakteristiku sledovanou pro všechny řádky ■ pokud pro některé záznam není uvedena hodnota některého pole, je v databázi reprezentována NULL hodnotou ■ záznamy v datové tabulce je možné jednoznačně identifikovat pomocí pole tzv. primárního klíče ■ kandidátní klíč je atribut (skupina atributů), kterým jsou jednoznačně určeny řádky v tabulce Primární klíč Často je realizován pomocí sloupce ID - sloupec nenese žádný význam, ale zajišťuje jednoznačnou identifikaci. V.Eclerová • Databáze • 6. května 2024 10/84 Uvod do databázových systémů Relační databáze Příklad - datová tabulka okres sloupce >S-H ok_kod ok_nazev_kratky ok_nazev_dlouhy ok_cznuts ok_rozloha ok_pocet_obci kr_kod 1 I Benešov Benešov CZ0201 1474.69 114 2 2 Beroun Beroun CZ0202 661.91 85 2 3 Blansko Blansko CZ0641 862.65 116 11 4 Bmo-město Bmo-město CZ0642 230.22 1 11 5 Bmo-venkov Bmo-venkov CZ0643 1498.95 187 11 6 Bruntál Bruntál CZ0801 1536.06 67 14 7 Břeclav Břeclav CZ0644 1048.91 63 11 8 česká Lípa česká Lípa CZ0511 1072.91 57 7 9 č. Budějovice české Budějovice CZ0311 1638.30 109 3 10 český Krumlov český Krumlov CZ0312 1615.03 46 3 11 Děčin Děčin CZ0421 908.58 52 6 12 Domažlice Domažlice CZ0321 1123.46 85 4 13 Frýdek-M istek Frýdek-M istek CZ0802 1208.49 72 14 14 Havlíčkův Brod Havlíčkův Brod CZ0631 1264.95 120 10 15 Hodon in Hodon in CZ0645 1099.13 82 11 16 Hradec Králové Hradec Králové CZ0521 891.62 104 8 17 Cheb Cheb CZ0411 1045.94 40 5 18 Chomutov Chomutov CZ0422 935.30 44 6 19 Chrudim Chrudim CZ0531 992.62 108 9 V.Eclerová • Databáze • 6. května 2024 11/84 Úvod do databázových systémů Relační databáze ■ vztahy mezi tabulkami jsou realizovány prostřednictví primárního klíče ■ primární klíč jedné tabulky je uložen jako pole druhé tabulky, toto pole se nazývá cizí klíč Primární a cizí klíč Tabulka okres obsahuje pole kr_kod, které je cizím klíčem do tabulky kraj. kr_kod kr_nazev kr_cznuts ob_kod 1 Hlavn' město Praha CZ010 1 2 Středočeský kraj CZ020 2 3 Jihočesky kraj CZ031 3 4 Plzeňský kraj CZ032 3 5 Karlovarský kraj CZ041 4 e Ústecký kraj CZ042 4 7 Liberecký kraj CZ051 5 3 Královéhradecký kraj CZ052 5 9 Pardubický kraj CZ053 5 10 Kraj Vysočina cz::;? 6 11 Jihomoravský kraj CZ0G4 6 12 Olomoucký kraj CZ071 7 13 Zlínský kraj CZ072 7 Moravskoslezský kraj CZ080 B V.Eclerová • Databáze • 6. května 2024 12/84 Úvod do databázových systémů Relační databáze Základní charakteristiky relačního modelu 1. Řádky obsahují informace o entitách 2. SLoupce obsahují hodnoty atributů vztahující se k dané entitě 3. Každé poLe tabulky obsahuje jedinou hodnotu 4. Všechny údaje v jednom sloupci mají stejný typ (domain integrity constraint) 5. Každý sloupec má v rámci tabulky jednoznačné jméno 6. Na pořadí řádků nezáleží 7. Na pořadí sloupců nezáleží 8. Žádné dva řádky neobsahují shodná data V.Eclerová • Databáze • 6. května 2024 13/84 Úvod do databázových systémů Relační databáze Tabulka nesplňující relační schéma Kód Jméno jPříjmení jOddělení i Email i Komentář 1 Jan i Nová k (Kancelář ředitele jnovak(S)spolecnost.cz i Ředitel společnosti. Převzal řízení v roce 1998. 2 Petr i Nový | Lidské zdroje : nowfSJspolecnost.cz : Zaměstnanec měsíce. 3 Alena i Zelená | Právní oddělení I zelena (5) s po lecnost.cz 1 Vedoucí oddělení. lze len a (ô) so u krom v email.cz i 4 Petra |Světlá |Lidské zdroje I svetla (S)s p olecnost.cz i Vedouc' oddělení. V.Eclerová • Databáze • 6. května 2024 14/84 Úvod do databázových systémů Relační databáze Tabulka nesplňující relační schéma Kód Jméno jPříjmení jOddělení i Email i Komentář 1 Jan i Nová k (Kancelář ředitele jnovak(S)spolecnost.cz i Ředitel společnosti. Převzal řízení v roce 1998. 2 Petr i Nový | Lidské zdroje : nowfSJspolecnost.cz : Zaměstnanec měsíce. 3 Alena i Zelená | Právní oddělení I zelena (5) s po lecnost.cz Vedouc' oddělení. lze len a (ô) so u krom v email.cz i 4 Petra |Světlá |Lidské zdroje I svetla (S)s p olecnost.cz i Vedouc' oddělení. ■ komentář v řádku s kódem 1 obsahuje více než jednu informaci ■ u paní Zelené v řádku 3 jsou uvedeny dva emaily V.Eclerová • Databáze • 6. května 2024 14/84 Úvod do databázových systémů Relační databáze Databázový diagram Grafická reprezentace databáze, která obsahuje názvy jednotlivých datových tabulek a jejich polí. Označuje primární klíče datových tabulek a znázorňuje relace mezi datovými tabulkami. oblast $ ob_kod ob_nazev ob cznuts -CC kraj $ kr_kod kr_nazev kr_cznuts ob kod okres $ ok_kod ok_nazev_kratky ok_nazev_dlouhy ok_cznuts ok_rozloha ok_pocet_obci kr kod V.Eclerová • Databáze • 6. května 2024 15/84 Úvod do databázových systémů Relační databáze Příklad - reprezentace binárních vztahů Uvažujte následující tři situace a rozhodněte, která nejlépe odpovídá binárnímu one-to-one, one-to-many, many-to-many: ■ Uvažujte skupinu vysokoškolských studentů, kteří si zapisují různé předměty. U studentů sledujeme atributy: jméno, příjmení, ročník, typ studia. U předmětů sledujeme atributy: název, vyučující, rozvrhovaný čas začátku a konce. ■ Uvažujte skupinu tanečnic a tanečníků. U tanečnic sledujeme: jméno, příjmení, tělesné míry a výšku. U tanečníků sledujeme: jméno, příjmení, výšku a váhu. Dále sledujeme informaci o vytvořených tanečních párech. ■ Uvažujte skupinu zaměstnanců, kteří jsou zaměstnání na různých odděleních dané firmy. U zaměstnanců sledujeme: jméno, příjmení, pracovní pozici. U oddělení sledujeme: název, jméno ředitele, adresu sídla. Navrhněte různé způsoby ukládání těchto dat. V.Eclerová • Databáze • 6. května 2024 16/84 Úvod do databázových systémů Relační databáze Příklad - uložení dat o organizačním členění v České republice Uvažujme data, která obsahují různé organizační celky v České republice (okresy, kraje, oblasti). U každého s těchto celků známe ■ zkrácený název ■ název ■ kód CZNUTS. V případě okresu víme, do kterého patří kraje. V případě kraje víme, do které patří oblasti. U okresu známe jeho počet obcí a rozlohu. Navrhněte různé způsoby ukládání těchto dat. V.Eclerová • Databáze • 6. května 2024 17/84 Úvod do databázových systémů Relační databáze Příklad - uložení dat o hlášených případech COVID 19 Uvažujme data, která obsahují různé hlášení o událostech (nahlášení, úmrtí, vyléčení) v souvislosti s nemocí COVID 19 v České republice. U každé události známe ■ datum, kdy nastala ■ věk osoby, u které nastala ■ pohlaví osoby, u které nastala ■ okres, ve kterém nastala. Navrhněte různé způsoby ukládání těchto dat. V.Eclerová • Databáze • 6. května 2024 18/84 Úvod do databázových systémů Relační databáze Příklad - uložení dat o historii bydliště Uvažujme data, která obsahují pro různé osoby informace o jejich bydlišti. Chcete ukládat nejen aktuální data, ale i všechna předešlá bydliště dané osoby, konkrétně: ■ jméno a příjmení osoby ■ rok, kdy osoba začala bydlet na daném místě ■ okres, ve kterém osoba bydlí/bydlela ■ zpřesňující informace k adrese (přesná adresa, město ...). Navrhněte různé způsoby ukládání těchto dat. V.Eclerová • Databáze • 6. května 2024 19/84 Úvod do databázových systémů Relační databáze Princip návrhu relačních databází, normalizace kandidátní klíč je atribut (skupina atributů), který jednoznačně identifikuje řádek relace (tabulky) funkční závislost je vztah mezi dvěma atributy v rámci jedné tabulky Atribut Y je funkčně závislý na atributu X, pokud každá platná hodnota X určuje Y jednoznačně. X se nazývá určující atribut (determinant), Y se nazývá závislý atribut (dependent). Funkční závislost Atributy jméno, příjmení, věk, pohlaví jsou funkčně závislé na atributu rodné číslo. V.Eclerová • Databáze • 6. května 2024 20/84 Úvod do databázových systémů Relační databáze Princip návrhu relačních databází 1. Relace (tabulka) se nazývá správně vytvořená (well-formed), jestli je každý její určující atribut zároveň kandidátním klíčem. 2. Každá relace, která není správně vytvořená, by měla být rozdělena na jednu nebo více dobře vytvořených relací. ■ normalizace proces zkoumání a přetváření relací tak, aby byly správně vytvořeny (dle principu návrhu relačních databází) ■ normální formy jsou sady pravidel, jejichž splnění vyžadujeme v relačním databázovém návrhu V.Eclerová • Databáze • 6. května 2024 21/84 Úvod do databázových systémů Relační databáze Proces normalizace 1. Identifikujte všechny kandidátní klíče. 2. Identifikujte všechny funkční závislosti. 3. Zjistěte určující atributy všech funkčních závislostí a ověřte, že jsou kandidátními klíči. Pokud nejsou: 3.1 Vytvořte novou relaci (tabulku), do které umístěte všechny určující i závislé atributy problematické funkční závislosti. 3.2 Jako primární klíč nové relace zvolte určující atribut/y sledované funkční závislosti. 3.3 Ponechte kopii určujícího/ch atributu/ů v původní relaci a označte je jako cizí klíč. 4. Opakujte krok 3. V.Eclerová • Databáze • 6. května 2024 22/84 Úvod do databázových systémů Relační databáze Příklad - uložení dat o ubytování studentů na kolejích Uvažujme data o studentech a kolejích, na kterých jsou ubytování. Příklad takto získaných dat je vidět na obrázku: Student Koleje/Privát Jméno Příjmení Věk Adresa Okres Kraj Ročník Spolubydlící Název Adresa Okres Kraj Cena za pokoj za měsíc Petr Novák 21 Nová ulice 1, Opava Opava Moravskoslezský 2 Martin Starý Koleje Kounicova Kounicova 507/50 Brno Jihomoravský kraj 15 000 Matin Starý 22 Moje ulice 2, Zlín Zlín Zlínkský kraj 3 Petr Novák Koleje Kounicova Kounicova 507/50 Brno Jihomoravský kraj 15 000 Alena Bílá 23 Domov 1, Plzeň Plzeň-město Plzeňský 3 Sandra Studená Koleje Komárov Bří Žůrků 591/5, Brno Brno Jihomoravský kraj 12 000 Sandra Studená 23 Hlavní ulice 3, Ústí nad Labem Ústí nad Labem Ústecký 3 Alena Bílá Koleje Komárov Bří Žůrků 591/5, Brno Brno Jihomoravský kraj 12 000 Pavel Nový 21 Hlavní ulice 5, Ostrava Ostrava Moravskoslezský 1 Koleje Kounicova Kounicova 507/50 Brno Jihomoravský kraj 15 000 Alexandra Novotná 21 Vedlejší ulice 12, Karlovy Vary Karlovy Vary Karlovarský 1 Koleje Kounicova Kounicova 507/50 Brno Jihomoravský kraj 15 000 Pavlína Nová 23 Novobranská 2, Pardubice Pardubice Pardubický 3 Petra Levá, Alena Pravá Koleje Komárov Bří Žůrků 591/5, Brno Brno Jihomoravský kraj 12 000 Petra Levá 23 Hlavní ulice 3, Hodonín Hodonín Jihomoravský 3 Pavlína Nová, Alena Pravá Koleje Komárov Bří Žůrků 591/5, Brno Brno Jihomoravský kraj 12 000 Alena Pravá 23 Domov 1, České Budějovice České Budějovice Jihočeský 3 Pavlína Nová, Petra Levá Koleje Komárov Bří Žůrků 591/5, Brno Brno Jihomoravský kraj 12 000 Upravte uložení dat tak, aby respektovalo principy návrhu relačních databází. V.Eclerová • Databáze • 6. května 2024 23/84 Úvod do databázových systémů Relační databáze Hodnocení studentů Uvažujme data o známkách studentů získaných v jednotlivých předmětech. Příklad takto získaných dat je vidět na obrázku: Jméno studenta Příjmení studenta Předmět Známka Datum hodnocení Jméno vyučujícího Příjmení vyučujícího Petr Novák Lineární algebra A 05.05.2021 Petr Herman Matin Starý Lineární algebra B 15.05.2021 Petr Herman Alena Bílá Lineární algebra B 05.05.2021 Petr Herman Sandra Studená Lineární algebra C 15.05.2021 Petr Herman Pavel Nový Lineární algebra A 05.05.2021 Petr Herman Alexandra Novotná Lineární algebra C 15.05.2021 Petr Herman Pavlína Nová Lineární algebra D 05.05.2021 Petr Herman Petra Levá Lineární algebra E 15.05.2021 Petr Herman Alena Pravá Lineární algebra FE 15.05.2021 Petr Herman Petr Novák Matematický analýza B 02.05.2021 Alžeběta Poslední Matin Starý Matematický analýza B 02.05.2021 AI že beta Poslední Alena Bílá Matematický analýza FC 02.05.2021 Alžeběta Poslední Sandra Studená Matematický analýza C 14.05.2021 Alžeběta Poslední Pavel Nový Matematický analýza B 02.05.2021 Alžeběta Poslední Alexandra Novotná Matematický analýza C 02.05.2021 Alžeběta Poslední Pavlína Nová Matematický analýza D 02.05.2021 Alžeběta Poslední Petra Levá Matematický analýza D 02.05.2021 Alžeběta Poslední Alena Pravá Matematický analýza FFC 20.05.2021 Alžeběta Poslední Petr Novák Statistika A 01.06.2021 Alexandr Trojan Matin Starý Statistika B 01.06.2021 Alexandr Trojan Alena Bílá Statistika C 01.06.2021 Alexandr Trojan Sandra Studená Statistika C 01.06.2021 Alexandr Trojan Pavel Nový Statistika D 01.06.2021 Alexandr Trojan Alexandra Novotná Statistika C 01.06.2021 Alexandr Trojan Pavlína Nová Statistika D 01.06.2021 Alexandr Trojan Petra Levá Statistika C 01.06.2021 Alexandr Trojan Alena Pravá Statistika E 01.06.2021 Alexandr Trojan Upravte uložení dat tak, aby respektovalo principy návrhu relačních databází. V.Eclerová • Databáze • 6. května 2024 24/84 Úvod do databázových systémů Relační databáze Integritní omezení Pravidla/systémy pravidel, která zaručují integritu databáze. Příklady ■ unikátnost zadaných hodnot v rámci datových tabulek ■ daný datový typ nebo rozsah hodnot v tabulkách ■ primární a cizí klíče DATOVÉ TYPY ■ řetězec: char, varchar ■ číslo: int, numeric, float ■ datum: datetime ■ identifikátory: uniqueidentifier Hodnocení studentů Uvažujme data o známkách studentů v jednotlivých předmětech (viz předchozí příklad). Vymyslete vhodná integritní omezení. V.Eclerová • Databáze • 6. května 2024 25/84 SOL Základní příkazy Základy jazyka SOL SOL = Structured Query Language Základní součásti jazyka SOL: ■ Data definition language (DDL): vytváření tabulek, vztahů a dalších struktur datbáze ■ Data manipulation language (DML): pohledy na data, ukládání, změna a mazání dat ■ SOL/Persistent stored modules (SOL/PSM): příkazy, které umožňují procedurální programování v SOL ■ Transaction control language (TCL): příkazy, které umožňují definování transakcí a jejich řízení ■ Data control language (DCL): příkazy umožňují zprávu přístupu k databázi V.Eclerová • Databáze • 6. května 2024 26/84 SOL Základní příkazy DML - pohledy na data Základní syntaxe select výběr polí tabulky from výběr tabulky where podmínky na zobrazované záznamy KLíčové sLovo AS slouží k přejmenování sloupců tabuLky/ceLých tabulek v rámci dotazů. Je vhodné při použití klausule SELECT a při spojování tabulek. V.Eclerová • Databáze • 6. května 2024 27/84 SOL Základní příkazy Příklad - výpis dat z tabulky okresů Vypište dlouhý název a CZNUTS okresů za následujících podmínek: ■ všech okresů ČR ■ pouze okresů v Jihomoravském kraji ■ pouze okresů v Jihomoravském kraji nebo Zlínském kraji ■ pouze okresů, které nejsou v Jihomoravském kraji ani Zlínském kraji ■ okresů, kde počet obcí převyšuje 100 ■ okresů, jejichž rozloha je mezi 700 a 800 km2 Výpis opakujte, ale tentokrát zobrazte všechny sloupce tabulky. V.Eclerová • Databáze • 6. května 2024 28/84 SOL Základní příkazy ORDER BY slouží k seřazení záznamů ve výpisu dat, zapisuje se za klausuli „where" klíčová slova ASC a DESC slouží k určení vzestupného resp. sestupného pořadí záznamů (vzestupné řazení je výchozí) Příklad - výpis dat z tabulky okresů Vypište dlouhý název a CZNUTS okresů za následujících podmínek: ■ všech okresů ČR seřazených abecedně podle dlouhého názvu ■ pouze okresů v Jihomoravském kraji seřazených abecedně podle dlouhého názvu ■ všech okresů ČR seřazených vzestupně/sestupně podle počtu obcí ■ všech okresů ČR seřazených nejdříve podle počtu obcí vzestupně a v případě stejného počtu obcí podle rozlohy sestupně V.Eclerová • Databáze • 6. května 2024 29/84 SOL Základní příkazy Rozšířená syntaxe select výběr polí tabulky from výběr tabulky where podmínky aplikované na tabulku před agregací group by seznam sloupců, podle kterých se agreguje having podmínky na zobrazené záznamy order by sloupce, podle kterých se provádí řazení Příklady agregačních funkcí MAX, MIN, COUNT, COUNT + DISTINCT, SUM, AVG, VAR další agregační funkce Lze najít na tomto odkazu V.Eclerová • Databáze • 6. května 2024 30/84 SOL Základní příkazy Příklad - výpis dat z tabulky o případech nákazy COVID-19 ■ Vypište celkový počet nově nakažených, úmrtích a vyléčených v celé tabulce. ■ Kolik řádků v tabulce nemá vyplněný okres? ■ Vypište celkový počet nově nakažených, úmrtích a vyléčených po jednotlivých dnech v dubnu a květnu 2020. Data vhodně seřaďte. ■ Vypište celkový počet nově nakažených, úmrtích a vyléčených po jednotlivých měsících. Data vhodně seřaďte. ■ Vypište průměrný, maximální a minimální počet nově nakažených mužů za den. Jakou má výpis Limitaci? ■ Vypište průměrný počet nově nakažených mužů za den po měsících. ■ Vypište měsíc, ve kterém byl nejvyšší počet nově nakažených a jejich počet. V.Eclerová • Databáze • 6. května 2024 31/84 SOL Základní příkazy Spojování tabulek ■ cross join: jedná se o operaci, která zprostředkovává kartézský součin tabulek; syntaxe v jazyce SOL je oddělení jednotlivých tabulek čárkou ■ (inner) JOIN: je podmnožinou kartézského součinu tabulek; podmnožina je definována Logickým výrazem uvedeným za klíčovým slovem ON ■ LEFT JOIN: výsledná tabulka obsahuje (i) všechny záznamy „Levé" tabulky, (ii) podmnožinu kartézského součinu, která je definována Logickým výrazem uvedeným za klíčovým slovem ON; pokud záznam z „Levé" tabulky nesplňuje podmínky na přiřazení žádného záznamu „pravé" tabulky je řádek doplněn hodnotami NULL ■ RIGHT JOIN: Lze jej definovat podobně jako LEFT JOIN ■ FULL JOIN: Lze jej definovat podobně jako LEFT JOIN nebo RIGHT JOIN; výsledná tabulka obsahuje (i) všechny záznamy „Levé" i „pravé" tabulky V.Eclerová • Databáze • 6. května 2024 32/84 SOL Základní příkazy Příklad - výpis dat z tabulky okresů a krajů ■ Vypište tabulku obsahující všechny okresy společně s příslušnými kraji a oblastmi. Užijte různé varianty datových návrhů. ■ Vypište všechny okresy v oblasti Severozápad, které mají více než 100 obcí. ■ Vypište průměrný počet obcí v okrese pro všechny okresy v oblasti Severozápad. ■ Vypište celkovou rozlohu jednotlivých oblastí ČR. V.Eclerová • Databáze • 6. května 2024 33/84 SOL Základní příkazy Příklad - výpis dat z tabulky o případech nákazy COVID-19 ■ Vypište celkový počet nově nakažených mužů po dnech v Jihomoravském kraji. ■ Vypište průměrný počet nově nakažených mužů za den po měsících a krajích. S použitím spojování tabulek odstraňte problém diskutovaný na slidu 31. ■ Vypište dny, kdy nebyl žádný nově nakažený od počátku března 2020 do konce roku 2020. V.Eclerová • Databáze • 6. května 2024 34/84 SOL Základní příkazy Pořadí prováděných operací a vliv na rychlost dotazů U komplikovanějších dotazů je vhodné dotazy optimalizovat za účelem zvýšení rychlosti nebo snížení užitích zdrojů. Jeden z nástrojů, který se dá využít jsou tzv. „execution plans",více informací zde. Nástroje používané ke zvýšení rychlosti ■ indexy - struktura uložená na disku, která umožňuje rychlé vyhledávání v tabulkách a spojování tabulek. Automaticky se vytváří pro primární klíče. ■ dočasné tabulky - umožňují dočasně uložit část dat (například agregovaných) potřebných pro běh dotazu. V.Eclerová • Databáze • 6. května 2024 35/84 SOL Základní příkazy Příklad - execution plans, indexy Užijte příkazy: set statistics time on na začátku skriptu, set statistics time of f na konci skriptu. Zkorodujte si SQL Server Execution Times na záložce Messages. Zobrazte předpokládaný a reálný execution pian. Vytvořte vhodné indexy. ■ Z tabulky nákaza vypište celkový počet případů nákaz zaznamenaných v Jihomoravském kraji. Dotaz proveďte (i) s použitím příkazu join, (ii) s použitím vnořeného dotazu. ■ Viz slide 31: Vypište měsíc, ve kterém byl nejvyšší počet nově nakažených a jejich počet. Výpis proveďte (1) s použitím příkazu TOP 1, (2) s vnořeným dotazem bez použití dočasné tabulky, (3) a s použitím dočasné tabulky. ■ Viz slide 31, 34: Uvažujte dvě varianty dotazu pro výpis průměrného počtu nakažených mužů za den ((i) nejdříve vypočtěte počet nakažených po dnech a pak průměr, (ii) nejdříve vypočítejte počet nakažených za měsíc a pak vydělte počtem dní) a porovnejte. V.Eclerová • Databáze • 6. května 2024 36/84 SOL Základní příkazy Ukládání dat INSERT INTO název tabulky(název sloupce 1, název sloupce 2,...) VALUES {hodnota 1, hodnota 2...) INSERT INTO název tabulky(název sloupce 1, název sloupce 2,...) SELECT... SELECT hodnota 1, hodnota 2... INTO název tabulky (nová tabulka) FROM název tabulky (tabulka nebo spojení několika tabulek) WHERE podmínky V.Eclerová • Databáze • 6. května 2024 SOL Základní příkazy Změna dat a mazání dat UPDATE název tabulky SET název sloupce l=hodnota 1, název sloupce 2=hodnota 2 WHERE podmínky DELETE FROM název tabulky (tabulka nebo spojení několika tabulek) WHERE podmínky V.Eclerová • Databáze • 6. května 2024 38/84 SOL DDL - vytváření tabulek, mazání tabulek, úprava tabulek Vytváření tabulek, mazání tabulek, úprava tabulek Vytváření tabulky: CREATE TABLE název tabulky (název sloupce 1, název sloupce 2,...) Přidání sloupce: ALTER TABLE název tabulky ADD název sloupce [datový typ](délka datového typu); Smazání všech záznamů z tabulky: TRUNCATE TABLE název tabulky Smazání tabulky: DROP TABLE název tabulky V.Eclerová • Databáze • 6. května 2024 39/84 SOL DDL - vytváření tabulek, mazání tabulek, úprava tabulek Příklad - data o onemocnění COVID-19 ■ Vytvořte novou tabulku, do které vložte agregovaná data obsahující celkový počet nově nakažených osob pro každý den od začátku pandemie do posledního data uvedeného v tabulce nákaza (tj. 4.5.2021). ■ Na webu najděte údaje pro 5.5.2021 a vložte je ručně do tabulky. ■ Záznamy v tabulce aktualizujte. Vypočtěte rozlohu ČR a data přepočtěte na km2. ■ Smažte z tabulky všechny záznamy, které se netýkají roku 2020. ■ Přidejte do tabulky nový sloupec určující den v týdnu. ■ Naplňte korektně sloupec „den v týdnu". ■ Smažte z tabulky všechny záznamy. ■ Vložte do tabulky záznamy, ale nyní data mají obsahovat pouze celkový počet nově nakažených žen nemocí COVID-19 pro každý den. Nevyplňujte sloupec „den v týdnu". ■ Odstraňte celou tabulku. SOL DM L - pohledy, T-SOL - funkce, větvení Pohledy, uložené funkce Pohled na data ■ slouží k uložení často využívaného dotazu ■ Lze se na něj dotazovat pomocí pojmenování název pohledu a používat stejně jako běžné tabulky ■ vracená data se neukládají, aLe počítají se pokaždé znovu ■ není možné předávat přímo parametry Syntaxe: CREATE VIEW název pohledu AS SELECT... V.Eclerová • Databáze • 6. května 2024 41/84 SOL DML - pohledy, T-SOL - funkce, větvení Pohledy, uložené funkce Funkce vracející tabulku ■ slouží k uložení často využívaného dotazu ■ Lze se na něj dotazovat pomocí pojmenování název funkce a používat stejně jako běžné tabuLky ■ vracená data se neukLádají, aLe počítají se pokaždé znovu ■ je možné předávat přímo parametry CREATE FUNCTION název funkce ( název parametru 1 datový typ, název parametru 2 datový typ, ...) RETURNS TABLE AS RETURN ( SELECT... ) V.Eclerová • Databáze • 6. května 2024 42/84 SOL DM L - pohledy, T-SOL - funkce, větvení Pohledy, uložené funkce Funkce vracející jednu hodnotu CREATE FUNCTION název funkce ( název parametru 1 a datový typ, název parametru 2 a datový typ, ...) RETURNS datový typ výsledku AS BEGIN DECLARE názvy lokálních proměnných a datový typ SELECT... RETURN ... END V.Eclerová • Databáze • 6. května 2024 43/84 SOL DM L - pohledy, T-SOL - funkce, větvení Programování v SOL (T-SOL) Větvení: IF logický výraz BEGIN Sekvence příkazů END ELSE BEGIN Sekvence příkazů END CASE vstupní výraz WHEN výraz k porovnání THEN výstupní výraz [ ELSE výstupní výraz ] END CASE WHEN logický výraz THEN výstupní výraz [ ELSE výstupní výraz ] END V.Eclerová • Databáze • 6. května 2024 44/84 SOL DML - pohledy, T-SOL - funkce, větvení Programování v SOL (T-SOL) Větvení: ■ Větvení typu IF-ELSE Lze využívat při programování funkcí v sqL ■ Větvení typu CASE Lze využívat uvnitř dotazů V.Eclerová • Databáze • 6. května 2024 45/84 SOL DM L - pohledy, T-SOL - funkce, větvení Příklad - uložené funkce se skalárním výstupem ■ Vytvořte funkci, která pro zadané datum mezi Lety 2010 a 2030 vrátí den v týdnu, který v zadaný den byl/bude. ■ Vytvořte funkci, která pro dané rozmezí věků a rok vrátí počet úmrtí na COVID-19. ■ Vytvořte funkci, která pro zadaný CZNUTS (okresu, kraje nebo oblasti) vrátí název. V.Eclerová • Databáze • 6. května 2024 46/84 SOL DM L - pohledy, T-SOL - funkce, větvení Příklad - pohledy na data ■ Vytvořte pohled, který vytvoří tabulku obsahující ve sloupcích název oblasti, kraje, okresu. Užijte různé datové návrhy. ■ Vytvořte pohled, který vytvoří tabulku obsahující ve sloupcích název oblasti, kraje, rozlohu kraje a počet obcí v kraji. Užijte různé datové návrhy. ■ Viz slide 36: Vypište měsíc, ve kterém byl nejvyšší počet nově nakažených a jejich počet. ■ Vytvořte pohled, který vrátí počet pracovních a nepracovních dní v každém měsíci po Letech za roky 2022-2024. V.Eclerová • Databáze • 6. května 2024 47/84 SOL DM L - pohledy, T-SOL - funkce, větvení Příklad - uložené funkce ■ Vytvořte funkci, která vrátí počet pracovních a nepracovních dní pro všechny měsíce zvoleného roku. ■ Vytvořte funkci, která pro zvolenou organizační jednotku dle CZNUTS (pro Libovolnou úroveň klasifikace) vrátí název této jednotky a všech podřízených. V.Eclerová • Databáze • 6. května 2024 48/84 SOL DML - pohledy, T-SOL - funkce, větvení Programování v SOL (T-SOL) Cykly: WHILE logický výraz BEGIN Sekvence příkazů END BREAK a CONTINUE - umožňují v kombinaci z příkazem IF předčasně ukončit cyklus WHILE V.Eclerová • Databáze • 6. května 2024 49/84 SOL DML - pohledy, T-SOL - funkce, větvení Programování v SOL (T-SOL) Kursor - umožňuje spouštět cyklus přes výsledek příkazu SELECT DECLARE název proměnné datový typ DECLARE název kurzoru CURSOR FOR Příkaz select OPEN název kurzoru FETCH NEXT FROM název kurzoru INTO název proměnné WHILE @@FETCH_STATUS = 0 BEGIN Sekvence příkazů FETCH NEXT FROM název kurzoru INTO název proměnné END CLOSE název kurzoru DEALLOCATE název kurzoru V.Eclerová • Databáze • 6. května 2024 50/84 SOL DM L - pohledy, T-SOL - funkce, větvení Programování v SOL (T-SOL) Uložená procedura: Umožňuje provádět sekvence příkazů nad SOL serverem, které spouští uživatel nebo automat (hodí se například pro pravidelné aktualizace dat) SET NOCOUNT ON - umožňuje pozastavit výpis počtu provedených operací v SOL, je doporučeno ho použít u uložených procedur CREATE PROCEDUŘE název uložené procedury (název proměnné datový typ) AS BEGIN SET NOCOUNT ON Sekvence příkazů END V.Eclerová • Databáze • 6. května 2024 51/84 SOL DM L - pohledy, T-SOL - funkce, větvení Příklad - programování v SOL ■ Užijte příkaz CASE k vytvoření funkce na získání celkového počtu nákaz, úmrtí nebo vyléčení (parametr funkce) pro každý den zvoleného měsíce (druhý parametr funkce) z tabulky nákaza. ■ Užijte příkaz CASE k vytvoření funkce na získání celkového počtu nákaz, úmrtí a vyléčení pro každý den zvoleného měsíce z tabulky nákaza bez použití vnořeného SELECT a příkazu JOIN. ■ Vytvořte kursor přes všechny měsíce roku 2020. Pro každý s těchto měsíců proveďte příkaz z předchozího bodu. ■ Vytvořte novou prázdnou tabulku o čtyřech sloupcích (datum, počet nákaz, úmrtí a vyléčení). Vytvořte uloženou proceduru, která naplní tabulku pro zvolený měsíc pomocí dotazu z druhého bodu v této úloze. ■ Užijte data z tabulky vytvořené v předchozím bodu a vypočtěte kumulativní počet nákaz, úmrtí a uzdravení pro všechny dny v březnu 2021. Užijte příkaz JOIN. V.Eclerová • Databáze • 6. května 2024 52/84 SOL DM L - pohledy, T-SOL - funkce, větvení Příklad - programování v SOL ■ Nepovinné: Vytvořte uloženou funkci, která bude pro zvolený časový úsek (vstupem je datum od a datum do) vypisovat (i) celkový počet nových nákaz, úmrtí a vyléčení, které nastaly před vybraným měsícem (1 řádek); (ii) celkový počet nových nákaz, úmrtí a vyléčení pro každý den zvoleného měsíce; (iii) celkový počet aktivních případů nákazy pro každý den zvoleného měsíce. Data organizujte do jedné datové tabulky. Užijte příkaz UNION. V.Eclerová • Databáze • 6. května 2024 53/84 SOL DM L - pohledy, T-SOL - funkce, větvení Trigger Trigger je databázový objekt, který zahajuje svoji činnost ve chvíli, kdy se v databázi stane nějaká událost. Triggery dělíme na ■ DDL Trigger - spouští se po zavolání příkazu CREATE, DROP nebo ALTER ■ DML Trigger spouští se po zavolání (AFTER/FOR Triggery) nebo místo (INSTEAD Of Triggery) příkazu INSERT, UPDATE, DELETE V.Eclerová • Databáze • 6. května 2024 54/84 SOL Systémové databáze a jejich funkce Systémové databáze a jejich funkce Katalog objektů systémové databáze Příklad Vypište všechny tabulky a sloupce tabulek, které byly vytvořeny v březnu. select o.name,c.name from sys.columns c join sys.objects o on o.object_id=c.object_id where o.type_desc='USER_TABLE' and month(o.create_date)=3 order by o.objectjd, c.columnjd V.Eclerová • Databáze • 6. května 2024 55/84 Návrh datových skladů Základní pojmy Návrh datových skladů ■ Business intelligence (BI) systémy: slouží ke zhodnocení aktuálních a minulých aktivit, umožňují predikci budoucích aktivit, jsou podporou v pro rozhodování. ■ Operační systémy: někdy nazývány online transaction processing (OLTP) slouží k získávání, změně a ukládání dat (typicky prostřednictvím relační databáze). ■ Reportovací systémy: jako například online analytical processing (OLAP) umožňují rychlé filtrování a agregování dat. ■ Data-miningové systémy: umožňují provádět komplexní analýzy nad daty. ■ Datové sklady = Data warehouses: je speciální typ databázového systému, který obsahuje řídící údaje (data, programy, osobní údaje) potřebné k tvorbě a správě systému Bl. ■ Data marts: je seskupení dvou a více datových skladů. V.Eclerová • Databáze • 6. května 2024 56/84 Návrh datových skladů Základní pojmy The Relationship Between Operational and Bl Applications Functional Users Management and Management Support Users Operational Applications (Order Entry, Manufacturing, Purchasing, Inventory, etc.) Operational DBMS Reporting Data Mining Business Intelligence Applications Bl DBMS Extract of Operational Database Purchased Data ZDROJ: Database concepts. KROENKE, David M., et at. Upper Saddle River, NJ: Prentice HaLL, 2010. V.Eclerová • Databáze • 6. května 2024 57/84 Návrh datových skladů Základní pojmy Extract, transform, and Load (ETL) systém Automatický systém definovaný v rámci datového skladu, který umožňuje tvorbu konzistentní datové sady. ■ čištění dat, řešení chybějících hodnot (např. nesmyslné údaje o věku, pohlaví) ■ nekonzistence dat (např. telefonní čísla s předvolbou a bez ní) ■ kombinování více datových zdrojů ■ změna formátu dat ■ nahrazování zkratek celými názvy a další transformace dat Specifikace pro provádění těchto operací jsou v data warehouse metadata databází. V.Eclerová • Databáze • 6. května 2024 58/84 Návrh datových skladů Dimensionální databáze Dimensionální databáze Databázový návrh užívaný pro datové sklady. Cíle ■ efektivní tvorba dotazů a analýz ■ historické srovnání dat ■ dimenze: sloupec nebo sada sloupců, který popisují jednu společnou oblast ■ časová nebo datová dimenze: umožňuje ukládat a zobrazovat na historická data ■ tabulka faktů: slouží k uložení měřitelných hodnot (obvykle číselných), které Lze v rámci dimenzionální databáze agregovat ■ míry: jednotlivé měřitelné hodnoty v tabulce faktů V.Eclerová • Databáze • 6. května 2024 59/84 Návrh datových skladů Dimensionální databáze Datový návrh dimenzionální databáze STAR SCHEMA r \ r \ Dimension Dimension Fact table MEASURES f \ Dimension V.Eclerová • Databáze • 6. května 2024 60/84 Návrh datových skladů Dimensionální databáze Datový návrh dimenzionální databáze SNOW-FLAKE SCHEMA Dimension Dimension Fact table MEASURES Dimension Dimension J ^ Dimension Dimension V.Eclerová • Databáze • 6. května 2024 61/84 Návrh datových skladů Dimensionální databáze Dimenzionální databáze - vizualizace a operace ■ datové matice: tabulky obsahující v řádcích hodnoty jedné dimenze, ve sloupcích hodnoty druhé dimenze, a v polích tabulky agregované hodnoty ■ datové kostky: vícerozměrné datové matice ■ výstupy z dimenzionálních databází nejsou statické, ale uživatel může jejich podobu měnit (proto OLAP) = vybírat dimenzi, kterou chce zobrazit a jakou hodnotu z tabulky faktů chce sledovat ■ další operací je "driLLdown", která umožňuje uživateli navigování v rámci hierarchických struktur v dimenzích V.Eclerová • Databáze • 6. května 2024 62/84 Návrh datových skladů Dimensionální databáze Příklad - dimenzionální databáze Uvažujte následující databázové diagramy. Identifikujte tabulky faktů a dimenze. Identifikujte míry. okresy ľ ur r./ profese ,■ kod_i>rof«e < 3 název ! ; obce F kotLůbec nszsv idokres prace ? IP mEsicniMzdaOd pnrEtMiEľt obec icůZameetnjvatůl vzděláni S kad_indElarii & 13 firm v ř ICO V.Eclerová • Databáze • 6. května 2024 63/84 Návrh datových skladů Dimensionální databáze PRODUCT 9 ProductNumbcr ProducHype ProductName PRODUCT_SALE5 T Timel D í CustomerlD PrcductNumber Quantity UnitPrice Total CUSTOMER ? Curt[>m=rlD CuíttřmcrNsmc EmmlDomain Phc-neArcaCo-de City fote ZIP «0= — SALES FOR RFM T TimdD GjstoimerlD í InvciceNurnkiEr PreTařTotalialE TIMELINE ? TimdD Month ID Monthľott QuartcrlD Qy arterľcxt Year V.Eclerová • Databáze • 6. května 2024 64/84 Návrh datových skladů Dimensionální databáze Příklad - dimenzionální databáze ■ Na následujícím odkazu naleznete report obsahující data o zaměstnanosti v Evropské unii. Zkuste se zamyslet nad tím: (i) jak vypadají primární data pro tento report a jak jsou sbírána, (ii) kde v procesu zpracování takového reportu potřebujete dimenzionální databázi a jak může vypadat její návrh. V.Eclerová • Databáze • 6. května 2024 65/84 Návrh datových skladů Dimensionální databáze Příklad - dimenzionální databáze Uvažujte data on nákaze COVID-19 (uložené v tabulce nákaza). Jaké všechny dimenze je možné u těchto dat sledovat? Jaká data může obsahovat tabulka faktů? Načrtněte schéma dimenzionální databáze. Načtěte data o nákaze COVID-19 za rok 2020 společně se všemi identifikovanými dimenzemi do aplikace MS-Excel jako aktivní datové připojení. Vytvořte nad daty vhodnou kontingenční tabulku. A B E F G H 1 J i K L I M I N 1 nak_pohlavi (Vše) E 2 nakvek (Vše) 3 4 Součet z počet Popisky sloupců ' 5 BV V Celkem - N N Celkem 3U U Celkem e + Celá ČR + Celá ČR + Celá ČR 7 8 9 Popisky řádků r 10 Slil 125 125 3 305 3 305 35 35 11 ■ IV 4 8S7 4 887 4 357 4 357 207 207 12 fflV Z 780 2 780 1 594 1 594 76 76 13 ■ VI 1499 1 499 2 530 2 530 29 29 14 BVII 3 659 3 659 4 469 4469 35 35 15 SVIII 6 366 6 366 7 972 7 972 45 45 16 SIX 27 057 27 057 45 990 45 990 248 248 17 SX 165 970 165 970 263 847 263 847 2 929 2 929 18 SXI 264 404 264 404 187 990 187 990 5 005 5 005 19 SXII 147 916 147 916 209149 209 149 3 407 3 407 20 Celkový součet 624 663 624 663 731 203 731 203 12 016 12 016 21 22 23 24 25 26 27 28 29 j arialytical_source | original_data | © i < 1 Pole kontingenční tabulky Vyberte pole, která chcete přidat do sestavy: 3 0 nak_datum_hlaseni 0 nakvek 0 nak_pah.avi 0 tyo_hlaseni 0 ob název 0 kr_ název 0 ok_nazev_kratky 0 stat 0 počet Přetáhněte pole do jedné z následujících oblastí: T Filtry lili Sloupce nak_pohlavi nak_vek Radky Měsíce nak_daturn_hlaseni I I Odložit aktualizaci rozloženi' typ_hla5Eni stat 2 Hodnoty Součet z počet V.Eclerová • Databáze • 6. května 2024 66/84 Návrh datových skladů Dimensionální databáze Příklad - dimenzionální databáze Databáze SQL Serveru Server 0 REX\SQLEXPRESS Databáze VÝUKA DATABÁZE ^ Upřesnit možnosti Časový limit příkazu v minutách (volitelné) Příkaz SQL (nepovinný, vyžaduje databázi) select nak_datLimhlaseni, nakvek, nak_pohlavi, typ_hlaseni, ob_nazeVj krnazev, ok_nazev_kratky from nákaza n join okresystrom os on n.ok_kod=os.okkod where year(nak_datum_hlaseni)=2023 group by nak_datum_hlaserii, nakvek, nak_pohlavij typhlaseni, objnazev, kr_nazeVj oknazevkrat s Zahrnout sloupce relací □ Navigovat pomocí celé hierarchie O Povolit pro SQL Server podporu převzetí služeb při selhání OK Z'L.ái: Soubor Domů Vložen' Rozloženi'stránky Vzorce Data Revize Zobrazeni' Nápověda Acrobat C" Řekněte mi, co chcete udělat. r~l Dotazy a připojeni' [D Vlastnosti Načíst Z textu/ Z Z tabulky Poslední Existující Aktualizovat data- CSV webu nebo oblasti zdroje připojení vše- G> Upravit propojení Načíst a transformovat data Dotazy a připojení useradít A2 Vymazat S Použít znovu p Upřesnit Seřadit a filtrovat m o/ -*rm *-rm Filtr i r 14.10.2020 Text do Dynamické Odebrat Ověření Sloučit sloupců doplňování duplicity dat- Datové nástroje Spravovat Citlivostní List Seskupit Oddělit datový model analýza - prognózy Prognóza Přehled Zobrazit podrobnosti Skryt podrobnosti A _A_ B C D 1_E 1 F G H J K i nak_vek Q nak_pohlavi Q typ_hlaseni Q ob_nazev Q kr_nazev Q ok_nazev_kratky Q počet Q 2 14.1Q.2Q2G 28 Z N Strední Čechy Středočeský kraj Nymburk 1 3 25.10.2020 iÍ[m lN Severovýchod Liberecky kraj Liberec 1 4 06.10.2020 13 M V Severovýchod Pardubický kraj Pardubice 1 5 25.11.2020 5s|m |v Jihozápad Jihočeský kraj Jind. Hradec 1 e 03.11.2020 58 M v Jihozápad Plzeňský kraj Domažlice 1 7 20.1D.2D2D 3l|z N Severozápad Ústecký kraj Litoměřice 1 8 1D.11.2D2D 52 Z N Jihovýchod Jihomoravský kraj Blansko 2 Dotazy a pripojení Dotazy Pripojení Dotazů: 1 □ Dotazl Počet načtených řádků: 378 550 V.Eclerová • Databáze • 6. května 2024 67/84 Databáze a aplikace Velká data Distribuované databáze ■ serverový cluster: propojení několika databázových instancí ■ distribuovaná databáze: je databáze uložená a spravovaná na více než jednom počítači ■ typy distribuovaných databází: 1. rozdělení databáze na části a uložení těchto částí na více počítačích 2. replikace = uchování kopií databáze na více počítačích Transakce ■ operace: Locking, rollback, comitting ■ Transaction control language (TCL): příkazy, které umožňují definování transakcí a jejich řízení VEclerová • Databáze • 6. května 2024 68/84 Databáze a aplikace Balíček DBI a odbc Balíček DBI a odbc ODBC = Open Database Connectivity (konumikacni standard) alternativa ODBC je Java Database Connectivity (JDBC) Počítač/Sever Systémový ODBC Driver Databáze Užitečné odkazy: db.rstudio.com/r-packages/odbc/ https://db.rstudio.com/r-packages/dbi/ Zdroj: https : //db. rstudio. com/best-practices/drivers/ V.Eclerová • Databáze • 6. května 2024 69/84 Databáze a aplikace Balíček DBI a odbc ODBC drivery ■ Pro připojení externí aplikace k databázi slouží tzv. ODBC drivery - v našem případě například ODBC Driver 17 for SOL Server ■ Nastavení připojení se provádí pomocí tzv. připojovacího řetězce neboli connection string (viz www. connectionstrings . com/sql-server) Příklad syntaxe v knihovně odbc v R Library (odbc) con <- dbConnect(odbc(), Driver = "ODBC Driver 17 for SOL Server", Server = "REX\\SOLEXPRESS", Database = "VYUKA_DATABAZE", Trusted_Connection="yes", encoding = "CP1250") V.Eclerová • Databáze • 6. května 2024 70/84 Databáze a aplikace Balíček DBI a odbc Načtení okresů do RStudia Načtěte tabulku okres do R studia. Vykreslete bodový graf jednotlivých okresů v závislosti na rozloze a počtu obcí. Škálujte data o rozloze a počtu obcí. Škálovaná data vložte do tabulky okres. Vytvořte clustery okresů v závislosti na rozloze a počtu obcí. Užijte proceduru kmeans pro 3 clustery (https : //cs . wikipedia. org/wiki/K-means). Clustery vizualizujte v bodovém grafu. Přidejte údaj do získaném clusteru do tabulky okresů. Zapište novou tabulku okresů do databáze. Vytvořte report v Rmarkdown obsahující získané výsledky. V.Eclerová • Databáze • 6. května 2024 71/84 Databáze a aplikace Balíček DBI a odbc Načtení dat o nákaze COVID-19 do RStudia ■ Využijte uloženou funkci nakaza_po_dnech vytvořenou v rámci sLidu 53, která umožňuje zobrazovat počet nákaz, vyléčení a úmrtí na COVID-19 za sledované období po dnech. Data sledujte od začátku srpna 2020 do konce března 2021. Data vhodně časově seřaďte. ■ Data načtěte jako datovou tabulku do R. ■ Tabulku doplňte o počet aktivních případů v jednotlivých dnech (pracujte v programu R). Porovnejte se skriptem nakaza_po_dnech_active_cases (vytvořený v rámci slidu 53), který dělá tu samou věc. Vykresle graf počtu aktivních případů v závislosti na čase. ■ Uvažujte pouze data od l.srpna do 1.Listopadu 2020. Dá se říct, že počet aktivních případů exponenciálně roste? Ověřte prostřednictvím Lineární regrese. ■ Vytvořte report v Rmarkdown obsahující získané výsledky. V.Eclerová • Databáze • 6. května 2024 72/84 Databáze a aplikace Client-server architecture Client-server architecture počítačový model, který rozděluje úkoly nebo zátěž mezi poskytovatele prostředků (servery) a žadatele o službu (klienty). V.Eclerová • Databáze • 6. května 2024 73/84 Databáze a aplikace Client-server architecture Client-server architecture počítačový model, který rozděluje úkoly nebo zátěž mezi poskytovatele prostředků (servery) a žadatele o službu (klienty). ■ Klient: zařízení nebo aplikace, které žádají o služby nebo zdroje, např. počítače, mobilní zařízení nebo softwarové aplikace. ■ Server: poskytuje služby nebo zdroje, např. výkonné počítače nebo specializovaný hardware ■ Request-Response Model= „model žádost-odpověď" ODBC Three-Tier Web Server Architecture Tier 1 Tier 2 Tier 3 User Client Web Server Database Server Application Driver Manager DBMS Driver DBMS Database V.Eclerová • Databáze • 6. května 2024 73/84 Databáze a aplikace Client-server architecture Další zdroje dat ■ NoSOL databáze, neboli nereLační databáze, se od tradičních relačních databází odlišují tím, že nabízejí mechanismy pro ukládání a získávání dat, které nejsou vázány na tabulkové vztahy a SOL dotazy. ■ Typy NoSOL databází zahrnují: ■ Dokumentové databáze (např. MongoDB, Couchbase) ■ Sloupcové databáze (např. Apache Cassandra, HBase) ■ Grafové databáze (např. Neo4j, Amazon Neptune) ■ File-based data, např. Microsoft Excel. ■ Email V.Eclerová • Databáze • 6. května 2024 74/84 Databáze a aplikace Balíček d ply r Balíček dplyr ■ využívá se v situaci, kdy potřebujete zpracovávat pří Liš mnoho dat naráz a potřebujete využít externí paměť ■ využívá balíčky DBI a odbc ■ dplyr generuje příkazy SELECT a posílá je na SOL server (příkazy SOL se přímo nepíšou, ale dplyr má vlastní syntaxi) ■ balíček je vhodné kombinovat s knihovnou ggplot21 ^ttps://datacarpentry.org/R-ecology-lesson/ 04-visualization-ggplot2.html 2rstudio-education.github.io/tidyverse-cookbook/ how-to-use-this-book.html Program 2 V.Eclerová • Databáze • 6. května 2024 75/84 Databáze a aplikace Balíček d ply r Základní příkazy5 Načtení jedné datové tabulky (data se nenačítají přímo do R, aLe vytvoří se na ni odkaz): data<-tbl(con , "nákaza") select(název_sloupce_l,název_sloupce_2,...) - ekvivalent SELECT (bez možnosti dopočtených sloupců) \nutate(název_nového_sloupce=vzorec,...) - dopočtené sloupce filter(pod]mínka_l,pod]mínka_2,...) - ekvivalent příkazu WHERE su\ri\riať\se(název_nového_sloupce=agregačníJunkce,...) - ekvivalent agregačních funkcí qroup_by(název_sloupce_l,název_sloupce_2,...) - ekvivalent GROUP BY awanqe(název_sloupce_l,název_sloupce_2,...) - ekvivalent ORDER BY \nnerjo\n(název_tabuLky_l, název_tabutky_2) - ekvivalent JOIN Příklad syntaxe: data %>% select(nak_datum_hlaseni) as . data. frame - převede výsledek na datový typ „data frame" show_query - zobrazí SOL dotaz, který se spouští nad databází https://dplyr.tidyverse.org/ V.Eclerová • Databáze • 6. května 2024 76/84 Databáze a aplikace Balíček dplyr Nalíček ggplot2, příkaz ggplot Základní syntaxe: ggplot(data=)+ Cmapping=aesC)) v kombinaci s dpLyr pro práci s aktuálním zdrojem dat specifikuje použití sloupců z dat pro výstup na osách x, y- specifikuje typ grafu Příklady: geom_point(), geom_Line(), geom_histogramO, geom_boxplot(), geom_smooth(), geom_bar(). V.Eclerová • Databáze • 6. května 2024 77/84 Databáze a aplikace Balíček dplyr Práce s knihovnou dplyr ■ Pracujte s tabulkou nákaza v knihovně dplyr. Data načtěte. ■ Vypište celkový počet nově nakažených pro jednotlivé dny v datech. Data seřaďte. Výsledek vykreslete do grafu. ■ Zobrazte si SOL dotaz, který používá balíček dplyr. ■ Přidejte do graf křivku, která umožní data vyhladit. ■ Pracujte s tabulkou okres v knihovně dplyr. Data načtěte. ■ Vypište celkový počet nově nakažených pro jednotlivé dny v datech v Praze (okresu Praha). Data seřaďte. Výsledek vykreslete do grafu. ■ Jaký byl maximální počet nově nakažených za den ve sledovaném období v Praze? ■ Nakreslete histogram pro počet nově nakažených za den v Praze (pro jednoduchost neuvažujte dny, kdy nebyl žádný nově nakažený). V.Eclerová • Databáze • 6. května 2024 78/84 Databáze a aplikace Balíček dplyr Práce s knihovnou dplyr ■ Pracujte s tabulkou kraj v knihovně dplyr. Data načtěte. ■ Nakreslete sloupcový graf pro počet okresů v kraji. Graf obarvěte podle počtu obcí v okresu. Okresy vhodně rozdělte do tří kategorií podle počtu obcí. ■ Nakreslete boxplot pro počet nově nakažených za den ve Jihomoravském kraji po okresech (pro jednoduchost neuvažujte dny, kdy nebyl žádný nově nakažený). V.Eclerová • Databáze • 6. května 2024 79/84 Databáze a aplikace Modelování v R Balíček corrr Agregovaná data correlateQ corrr Tibbie shaveO focusO rplotO network_plotQ https : //corrr. tidymodels . org/index ."html V.Eclerová • Databáze • 6. května 2024 80/84 Databáze a aplikace Modelování v R Modelování v R Tabulka sectors obsahuje informace o podílu osob pracujících v jednotlivých sektorech ekonomiky v Evropě. ■ Je tabulka typickou ukázkou tabulky v relační databázi? Pokud ne, jak by data z této tabulky byla uložena. ■ Studujte funkce PIVOT a UNPIVOT na odkazu. Porovnejte s obdobnými funkcemi v R například na odkazu. ■ Vytvořte novou tabulku sektorů, která obsahuje pouze 3 sloupce a ukládá stejná data, jako původní tabulka. ■ Z nově vytvořené tabulky sektorů opětovně vytvořte tu původní. ■ Nalezněte korelační matici pro proměnné specifikované v tabulce sectors. Vhodně korelačním matici vizualizujte. Prezentace tabulkových dat Dvěma variantám ukládání dat popsaných v předchozím příkladu se říká: Wide a narrow, un-stacked a stacked, nebo wide a tali V.Eclerová • Databáze • 6. května 2024 81/84 Databáze a aplikace Balíček tidypredict Modelování v R V.Eclerová • Databáze • 6. května 2024 Databáze a aplikace Modelování v R Balíček modeldb Aggregate Results https://www.rstudio.com/resources/webinars/ modeling-in-databases-with-r/ V.Eclerová • Databáze • 6. května 2024 83/84 Databáze a aplikace Modelování v R Modelování v R Tabulka Computers obsahuje ceně počítačů v 90.Letech a parametry, které cenu ovlivňovaly. Z tabulky vyberte náhodně 25 % dat a sestavte nad nimi vhodný Lineární regresní model. Pro každý z náhodně vybraných počítačů určete cenu, kterou jim přisoudil model a porovnejte ji s opravdovou cenu, vypočtěte residua. Vykreslete graf závislosti residuí na modelované ceně. Vypočtěte R2. Pro všechny počítače v tabulce Computers predikujte cenu pomocí modelu vytvořeného v předchozím bodu. Zhodnoťte kvalitu modelu. V.Eclerová • Databáze • 6. května 2024 84/84 MASARYKOVA UNIVERZITA