Databázové systémy 2

Databázové systémy 2 Cvičení č. 10 RNDr. David Žák, Ph.D. Fakulta elektrotechniky a informatikydavid.zak@upce.cz

Obsah cvičení • Spojení tabulky sama se sebou (selfjoin) • Hierarchické dotazy dle syntaxe Oracle Databázové systémy 2 – cvičení 10

Hierarchické dotazy v Oracle 9i,10 • SELECTsloupceFROMtabulka[WHEREpodmínka3]START WITH podmínka1CONNECT BYpodmínka2[ORDER BY…] • Řádky vyhovující podmínce ve START WITH jsou považovány za kořenové řádky na první úrovni vnoření • Pro každou řádku na úrovni i se rekurzivně hledají přímí potomci vyhovující podmínce v klauzuli CONNECT BY na úrovni i+1 • Řádka předka se v podmínce označuje klíčovým slovem PRIOR • Na závěr jsou odstraněny řádky nevyhovující podmínce ve WHERE • Pokud není definováno třídění, odpovídá pořadí průchodu pre-order • Každý řádek obsahuje pseudo-sloupec LEVEL, obsahující úroveň řádku v hierarchii Databázové systémy 2 – cvičení 10

Příklad hierarchického dotazu Příklad: SELECT PRIJMENI, JMENO,level-- pseudosloupec označující úroveň FROM A_HR.ZAMESTNANCI CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID -- MANAZER_ID se rovná ZAMESTNANEC_ID u předchůdce START WITH MANAZER_ID is null; -- začni od zaměstnance, který nemá nadřízeného Databázové systémy 2 – cvičení 10

Doplnění hierarchických dotazů • Pro jednotlivé záznamy můžete také získat cestu od nejvyššího záznamu (jak to znáte třeba ze souborového systému) nebo řadu dalších informací: • Funkce SYS_CONNECT_BY_PATH vrací cestu v hierarchii k aktuálnímu záznamu. • Klauzule CONNECT_BY_ROOT vrací hodnotu z příslušného záznamu nejvyšší úrovně (tj. například nejvyššího manažera). • Pokud byste chtěli výstup z dotazu použít pro zobrazení ve formě rozbalovací hierarchie tak, jak to třeba dělá u souborů Windows Explorer, bude se vám hodit i pseudosloupec CONNECT_BY_ISLEAF, který určuje, zda je aktuální záznam na poslední úrovni hierarchie (CONNECT_BY_ISLEAF=1) nebo zda má podřízené záznamy (CONNECT_BY_ISLEAF=0). Databázové systémy 2 – cvičení 10

Příklad hierarchických dotazů • SELECT lpad(' ',level*3)||PRIJMENI||' '||JMENO name, • SYS_CONNECT_BY_PATH(PRIJMENI, '/') path, • CONNECT_BY_ROOT PRIJMENItopmgr, • CONNECT_BY_ISLEAF isleaf, • level • FROM A_HR.ZAMESTNANCI • CONNECT BY MANAZER_ID = PRIOR ZAMESTNANEC_ID • START WITH MANAZER_ID is null • ORDER SIBLINGS BY PRIJMENI; Databázové systémy 2 – cvičení 10

Příklady – Self Join Máme dánu ve schématu A_CLOVEK tabulku: Lide (Id, jmeno, prijmeni, narozen, pohlavi, Id_otce, Id_matky) Zjistěte, kolik dětí má otce s ID=27 a matku s ID=30. Dotaz upravte pro zjištění počtu vnoučat a následně pravnoučat. Vytvořte pohled CV10_VNOUCATA_BM (Id_babicky, prijmeni, jmeno) prozjištění jmen a příjmení všech vnoučat osoby(osob) Božena Malá. Z výsledku musí být zřejmé i to, zda všechny Boženy Malé mají nějaká vnoučata. Databázové systémy 2 – cvičení 10

Příklady – Self Join Vytvořte pohled CV10_VLASTNI_SOUROZENCI (id, jmeno, prijmeni, pocet),který zobrazí ke každé osobě Id, jméno a příjmení a počet vlastních sourozenců (společní oba rodiče).Poznámka: Neuvažujte osoby, u nichž jeden či oba rodiče jsou neznámí. Vytvořte pohled CV10_PRUMER_PL_PARY (pocet) prozjištění průměrného počtu dětí, které mají „plodné“ páry (za pár uvažujte stejné oba rodiče dítěte, „plodný“ znamená, že tito lidé mají potomka/y), výsledek zaokrouhlete na 2 desetinná místa. Výsledkem bude tedy jedno jediné číslo. Poznámka: Neuvažujte osoby, u nichž jeden či oba rodiče jsou neznámí. Databázové systémy 2 – cvičení 10

Příklady – hierarchické dotazy Vytvořte pohled CV10_NASLEDNICI_BM (Id_bm, vztah, jmeno, prijmeni) prozjištění jmen a příjmení všech dětí, vnoučat a pravnoučat osoby(osob) Božena Malá. Z výsledku musí být zřejmé i to, zda všechny Boženy Malé mají nějaké potomky. U každé osoby uveďte kromě jména a příjmení i vztah – konkrétní hodnoty z oboru hodnot (dítě, vnouče, pravnouče). Úkol řešte pomocí příkazu SELECT s klauzulí START WITH pro rekurzivní spojení. Databázové systémy 2 – cvičení 10

Příklady – hierarchické dotazy Vytvořte funkci CV10_POCET_NASLEDNIKU (id_osoby NUMBER, urovenNUMBER), která vrátí počet následníků na dané úrovni pro konkrétní osobu (úroveň 1 .. Děti, 2 .. Vnoučata atd.), pokud id_osoby nebude existovat, vrátí funkce hodnotu NULL. Vytvořte pohled CV10_NASL_POCTY (Id, jmeno, prijmeni, deti, vnoucat, pravnoucat), který pro všechny ženy starší 20-ti let vypíše id osoby, její jméno a příjmení a počty dětí, vnoučat a pravnoučat. Databázové systémy 2 – cvičení 10

Příklady – hierarchické dotazy Vytvořte pohledCV10_LINIE_JMEN (prijmeni_ditete, retezec_jmen), který pro všechny osoby mladší 10-ti let zobrazí ve sloupci retezec_jmen všechna křestní jména v linii dle pohlaví u dané osoby a jejích předchůdců navzájem oddělená čárkami (nejmladší vlevo, nejstarší vpravo), zobrazte pouze řádky zobrazující celé známé linie (tedy až po posledního známého předchůdce). Nápověda: použijte SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT a CONNECT_BY_ISLEAF Příklad Horký Martin, Petr, Martin Plachá Lucie, Monika Databázové systémy 2 – cvičení 10

A_CLOVEK Databázové systémy 2 – cvičení 10

Cvičení 10 – kontrola Všechny databázové objekty z tohoto cvičení vytvořte se svém schématu. Pro splnění cvičení je nutné, aby všechny objekty byly správně vytvořeny a funkční do půlnoci dne před následujícím cvičením. Databázové systémy 2 – cvičení 10

Databázové systémy 2