Michal Rjaško

Kontakt:

Praktické cvičenia z databáz 2016/2017 ZS

Ján Mazák, M255, mazak at dcs.fmph.uniba.sk
Michal Rjaško, rjasko at dcs.fmph.uniba.sk

Domáce ulohy

Budú 3 a budú zverejnené na tomto mieste

Cvičenie 12 (12.12.2016)

EXPLAIN: query planner

Some useful links:
  1. Stiahnite si na server cvika súbor explain.sql a spusťte psql -f explain.sql. Potom spusťte psql, postupne vykonávajte nasledujúce príkazy a analyzujte plány, ktoré vytvoril plánovač. Skúste pochopiť, prečo bol daný plán zvolený.

    
    explain analyze select * from ab;
    explain analyze select * from ab where b > 4 order by b;
    create index i1 on ab (b);
    explain analyze select * from ab where b > 4 order by b;
            

    Vykonajte posledný príkaz desaťkrát po sebe a sledujte, ako sa mení reálny spotrebovaný čas.

            
    explain select * from ab, bc;
    insert into bc select x.id, x.id + 1 from generate_series(1, 1000000) as x(id);
    explain select * from ab, bc;
            

    Pozor, ak v poslednom príkaze použijete aj analyze, bude to trvať veľmi dlho (koľko riadkov by malo byť vo výsledku?).

  2. Spusťte psql -f explain.sql a analyzujte plány, ktoré vytvorí plánovač pri nasledujúcich príkazoch.

    
    explain select * from ab, bc where ab.b = bc.b;
    create index i1 on ab (b);
    explain select * from ab, bc where ab.b = bc.b;
    explain select * from ab, bc where ab.b = bc.b order by ab.b;
    create index i2 on bc (b);
    explain select * from ab, bc where ab.b = bc.b;
    explain select * from ab, bc where ab.b = bc.b order by ab.b;
    drop index i2;
    create index i22 on bc (b, c);
    explain select * from ab, bc where ab.b = bc.b order by ab.b;
            

    Všimnite si, ako "čas" vypočítaný plánovačom rastie, ak index obsahuje nepotrebné stĺpce.

  3. Spusťte psql -f explain.sql a analyzujte plány, ktoré vytvorí plánovač pri nasledujúcich príkazoch.

    
    explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
    explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c order by bc.b;
    create index i3 on cd(c);
    explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c order by bc.b;
    create index i22 on bc (b, c);
    explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c order by bc.b;
    insert into ab select x.id, x.id + 1 from generate_series(1, 100000) as x(id);
    insert into bc select x.id, x.id + 1 from generate_series(1, 100000) as x(id);
    insert into cd select x.id, x.id + 1 from generate_series(1, 100000) as x(id);
    explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c;
    explain select * from ab, bc, cd where ab.b = bc.b and bc.c = cd.c order by cd.c;
    explain select cd.c, count(*) from ab, bc, cd where ab.b = bc.b and bc.c = cd.c group by cd.c order by cd.c;
        

Cvičenie 11 (5.12.2016)

SQLLite
krátke slajdy
SQLLite je databázový systéme, ktorý nevyžaduje samostatný server, beží v rámci výpočtového procesu Vašej aplikácie. Ak chcete vo Vašej aplikácii využiť relačnú databázu a nepotrebujete centralizované úložisko (ktoré by zbieralo dáta z viacerých aplikácií), SQLLite je vhodné riešenie. Úlohy:
  1. Prepíšte Váš program z predchádzajúceho cvičenia do SQL lite

Cvičenie 10 (28.11.2016)

Postgres a JAVA - pokračovanie
Na cvičení budeme používať databázu známok, ktorú sme vytvárali na predchádzajúcich cvičeniach.
  1. Napíšte program, ktorý sa pripojí na databázu a vypíšte na konzolu mená študentov.
  2. Naplňte tabuľky náhodnými dátami. Vytvorte si sadu niekoľkých mien a niekoľkých priezvisk (napr. 20-30 mien a 20-30 priezvisk). Nezdržujte sa dlho vymýšľaním mien (radšej použite "AAA", "BBB",...)
  3. Pri napĺňaní tabuliek vyskúšajte rôzne spôsoby optimalizácie a porovnajte ich rýchlosti (viď http://www.postgresql.org/docs/9.1/static/populate.html:
    1. Každý riadok najprv vkladajte jedným insertom bez použitia prepared statementu
    2. Každý riadok najprv vkladajte jedným insertom s použitím prepared statementu
    3. Spojte niekoľko riadkov do jedného insertu - pravdepodobne nebudeme môcť urobiť INSERT so všetkými riadkami, ktoré chcete vložiť kvôli obmedzaniam na maximálnu veľkosť dotazu (cca niekoľko MB). Vhodné je preto spojiť do jedného INSERT dotazu od 100 do 1000 riadkov. Môžete vyskúšať, koľko Vám systém dovolí
    4. Vykonajte všetky INSERT dotazy v jednej transakcii
    5. Pred vkladaním zrušte všetky indexy tabuľky a po vložení ich vytvorte
    6. Pred vkladaním zrušte všetky CONSTRAINTy tabuľky a po vložení ich vytvorte
  4. Napíšte program, ktorý z konzoly načíta Meno a Priezvisko a triedu študenta a vypíšte jeho známky (nazov predmetu: známky z predmetu oddelené čiarkov). Ak je študentov s daným menom a triedou viac, vypíšte prvého z nich. Použite prepared statement. Zabezpečte, aby pri písaní mena nezáležalo na veľkých a malých písmenách.
  5. Upravte program z predchádzajúcej úlohy tak, aby systém vyhľadal študenta, ak používateľ napíšte len časť jeho mena / priezviska. Ak sa nájde viac študentov spĺňajúcich vyhľadávacie kritéria, dajte používateľovi možnosť vybrať si (napr. vypísaním zoznamu a používateľ bude musieť zadať por. číslo / ID, ktorého študenta myslel).

Cvičenie 9 (21.11.2016)

Pripojenie na Postgres z JAVY

Prezentácia [pdf]

Z JAVY sa budeme na databázu Postgres pripájať cez TCP/IP. Aby to však fungovalo, každý si musí nastaviť svoje Postgres heslo a vytvoriť SSH tunel na cvika.dcs.fmph.uniba.sk
  1. Pripojte sa cez SSH na cvika.dcs.fmph.uniba.sk
  2. Spustite postgres konzolu príkazom psql
  3. Nastavte si heslo pomocou: ALTER USER {vase_prihlasovacie_meno} WITH PASSWORD '{vase_nove_heslo}'
  4. Aby sme sa mohli pripájať na postgres bežiaci na cvika.dcs.fmph.uniba.sk z vývojového prostriedia Eclipse (príp. iného), je potrebné nastaviť SSH tunel:
  5. Vytvorte si nový JAVA projekt
  6. Stiahnite si Postgres JDBC driver: https://jdbc.postgresql.org/download.html a nakopírujte ho do adresára projektu
  7. Pridajte si stiahnutý JAR súbor do "build path". V eclipse je to v menu "Project" -> "properties...":
  8. Tutoriál, ako sa pripojiť na postgres z JAVY: http://www.tutorialspoint.com/postgresql/postgresql_java.htm
  9. Prípadne postupujte podľa prezentácie
  10. Na cvičení budeme používať databázu známok, ktorú sme vytvárali na predchádzajúcich cvičeniach.
Úlohy:
  1. Napíšte program, ktorý sa pripojí na databázu a vypíšte na konzolu mená študentov.
  2. Naplňte tabuľky náhodnými dátami. Vytvorte si sadu niekoľkých mien a niekoľkých priezvisk (napr. 20-30 mien a 20-30 priezvisk). Nezdržujte sa dlho vymýšľaním mien (radšej použite "AAA", "BBB",...)
  3. Pri napĺňaní tabuliek vyskúšajte rôzne spôsoby optimalizácie a porovnajte ich rýchlosti (viď http://www.postgresql.org/docs/9.1/static/populate.html:
    1. Každý riadok najprv vkladajte jedným insertom bez použitia prepared statementu
    2. Každý riadok najprv vkladajte jedným insertom s použitím prepared statementu
    3. Spojte niekoľko riadkov do jedného insertu - pravdepodobne nebudeme môcť urobiť INSERT so všetkými riadkami, ktoré chcete vložiť kvôli obmedzaniam na maximálnu veľkosť dotazu (cca niekoľko MB). Vhodné je preto spojiť do jedného INSERT dotazu od 100 do 1000 riadkov. Môžete vyskúšať, koľko Vám systém dovolí
    4. Vykonajte všetky INSERT dotazy v jednej transakcii
    5. Pred vkladaním zrušte všetky indexy tabuľky a po vložení ich vytvorte
    6. Pred vkladaním zrušte všetky CONSTRAINTy tabuľky a po vložení ich vytvorte
  4. Napíšte program, ktorý z konzoly načíta Meno a Priezvisko a triedu študenta a vypíšte jeho známky (nazov predmetu: známky z predmetu oddelené čiarkov). Ak je študentov s daným menom a triedou viac, vypíšte prvého z nich. Použite prepared statement. Zabezpečte, aby pri písaní mena nezáležalo na veľkých a malých písmenách.
  5. Upravte program z predchádzajúcej úlohy tak, aby systém vyhľadal študenta, ak používateľ napíšte len časť jeho mena / priezviska. Ak sa nájde viac študentov spĺňajúcich vyhľadávacie kritéria, dajte používateľovi možnosť vybrať si (napr. vypísaním zoznamu a používateľ bude musieť zadať por. číslo / ID, ktorého študenta myslel).

Cvičenie 8 (14.11.2016)

Dokončenie minulého cvičenia GRANT/REVOKE:
  1. Pripojte sa na databázu "test" (psql test), vytvorte novú tabuľku "test_vasemeno" so stĺpcami i INTEGER a t TEXT a doplňte do nej niekoľko riadkov. Prideľte právo na SELECT z tejto tabuľky role "test" (ďalej testovacia rola) a overte pomocou \z, či je naozaj pridelené. Spustite psql -U test test, skúste si prezrieť obsah tabuľky test_vasemeno a skúste do nej vložiť nový riadok.
  2. Upravte testovacej role práva na SELECT tak, aby mala možnosť prezerať si v tabuľke test_vasemeno len obsah stĺpca t.
  3. Povoľte spolužiakovi vkladať do tabuľky test_vasemeno riadky tak, aby mohol toto oprávnenie prideliť iným. Vyskúšajte: nech pridelí toto oprávnenie role test. Potom mu odoberte možnosť prideliť toto oprávnenie iným (REVOKE GRANT OPTION FOR) tak, aby stále sám mohol vkladať riadky. Požiadajte ho, nech vyskúša, či to funguje.
  4. Vyskúšajte možnosť kaskádovitého odobratia oprávnenia (pozri CASCADE na stránke http://www.postgresql.org/docs/9.1/static/sql-revoke.html).
  5. Odoberte spolužiakom a testovacej role všetky oprávnenia, ktoré ste im udelili (REVOKE ALL PRIVILEGES FROM).

Cvičenie 7 (7.11.2016)

Constraints:

Prezentácia [pdf]

Pokračujeme v práci s tabuľkami vytvorenými minule (cvičenie 6).

  1. Pre každú tabuľku zvoľte primárny kľúč.
  2. Pomocou UNIQUE zabezpečte, aby trieda mohla mať predmet pridelený len raz (vyhýbame sa duplicitným záznamom).
  3. Zakážte NULL v stĺpcoch, kde je nutné evidovať hodnotu (napr. nie je nutné, aby študent mal evidované pohlavie, ale musí mať meno).
  4. Obmedzte pomocou CHECK možné hodnoty pre pohlavie a dátum narodenia (zvoľte si nejaký zmysluplný rozsah). Vyskúšajte, či vaše obmedzenie funguje pri INSERT aj pri UPDATE.
  5. Doplňte cudzie kľúče do tabuľky známok: hodnoty v stĺpcoch musia odkazovať na existujúceho študenta, učiteľa a predmet. Overte funkčnosť pri INSERT, kde odkaz na predmet je neexistujúci alebo NULL.
  6. Ku všetkým cudzím kľúčom doplňte zmysluplné hodnoty pre ON DELETE: pri zmazaní študenta treba zmazať záznamy o jeho známkach; učiteľa alebo predmet nie je možné zmazať, ak sa ich týkajú nejaké záznamy o známkach. Overte, či vaše nastavenia fungujú pri pokuse o zmazanie všetkých učiteľov, všetkých predmetov či jednotlivých študentov.
  7. Ku všetkým cudzím kľúčom doplňte zmysluplné hodnoty pre ON UPDATE a nastavte okamžité vyhodnocovanie s možnosťou zmeniť ho v rámci transakcie (DEFERRABLE INITIALLY IMMEDIATE).
  8. Napíšte dotaz, ktorý presunie všetky známky z biológie pre študentov z 1.A z jedného učiteľa na iného. Preverte, že dotaz funguje správne.
  9. Vymažte z databázy všetkých učiteľov, ktorí udelili viac ako 5 známok. (Možno budete potrebovať viac ako 1 dotaz.)
  10. Pripojte sa na databázu "test" (psql test), vytvorte novú tabuľku "test_vasemeno" so stĺpcami i INTEGER a t TEXT a doplňte do nej niekoľko riadkov. Prideľte právo na SELECT z tejto tabuľky role "test" (ďalej testovacia rola) a overte pomocou \z, či je naozaj pridelené. Spustite psql -U test test, skúste si prezrieť obsah tabuľky test_vasemeno a skúste do nej vložiť nový riadok.
  11. Upravte testovacej role práva na SELECT tak, aby mala možnosť prezerať si v tabuľke test_vasemeno len obsah stĺpca t.
  12. Povoľte spolužiakovi vkladať do tabuľky test_vasemeno riadky tak, aby mohol toto oprávnenie prideliť iným. Vyskúšajte: nech pridelí toto oprávnenie role test. Potom mu odoberte možnosť prideliť toto oprávnenie iným (REVOKE GRANT OPTION FOR) tak, aby stále sám mohol vkladať riadky. Požiadajte ho, nech vyskúša, či to funguje.
  13. Vyskúšajte možnosť kaskádovitého odobratia oprávnenia (pozri CASCADE na stránke http://www.postgresql.org/docs/9.1/static/sql-revoke.html).
  14. Odoberte spolužiakom a testovacej role všetky oprávnenia, ktoré ste im udelili (REVOKE ALL PRIVILEGES FROM).

Cvičenie 6 (24.10.2016)

SQL, DDL, DML:

Cvičenie 5 (17.10.2016)

Agregácia v Datalogu: Agregácia v SQL II

Cvičenie 4 (10.10.2016)

Agregácia v SQL

Cvičenie 3 (3.10.2016)

SQL
Práca s SQL prostredím Zadanie Práca s konzolov PostgreSQL

Cvičenie 2 (26.09.2016)

Ešte raz datalog.

Cvičenie 1 (19.9.2016)

Dotazy nad databázov EMP.
krátke slajdy

Hodnotenie

Body sa dajú získať len za domáce úlohy. Tie budú tri, a to za 30, 30 a 40 bodov.