Adatbazisok_hasznalata_matekos

ADATBÁZISOK HASZNÁLATA GYAK. [MAT: im1c2ah5e]
ELTE, 2015/2016-os tanév I. félévében [2015.szept.]

Gyakorlat: Szerda 12:15-13:45-ig (2x45perc) D.ép. 2-123 (PC1 labor)
A gyakorlat kötelező, katalógus van, legfeljebb 4 hiányzás lehetséges
A gyakorlat célja: az előadáson szereplő alapfogalmak, algoritmusok, és
az adatbázisok-szemlélet gyakorlatban való elsajátítása. Az a cél, hogy
a hallgatók minél hatékonyabban tudjanak SQL és PL/SQL feladatokat
megoldani, hogy értsék is hogyan történik a lekérdezések kiértékelése.

Dolgozatok időpontja: 7.gyak. (2015.okt.21.) és 12.gyak.(2015.dec.2.)
Eredmények: (név-nélkül 4kar-kóddal) elérése hallgato/[jelszóval] .pdf
Pót/javZH: dec.18.P. 10h v. dec.21.H. 10h (0-818. Soó Rezső terem)
   Gyak.UV: dec.18.P. 10h (0-818.terem) vagy jan.5.K. 12h (0-823.terem)

Gyakorlatok anyaga: >> 1.GY >> 2.GY >> 3.GY >> 4.GY >> 5.GY >> 6.GY >>
>> I.ZH/7.GY >> 8.GY >> 9.GY >> 10.GY >> 11.GY >> II.ZH/12.GY >> 13.GY

A gyakorlatok gépes részéhez: Oracle adatbázisok elérése, SQL developer
Oracle Példatár feladatai: Feladatok.pdf (SQL: 1-6.fej., PL/SQL: 8-9.fej.)
-- Példatár feladataihoz scriptek: createEmpDept(en) vagy createDolg(hu)
   Tankönyv feladatai: Termékek-feladatok.pdf, Csatahajók-feladatok.pdf
   -- Tankönyv feladataihoz a scriptek: createTermékek, createCsatahajók

   Oracle SQL segédanyagok: ebben az eduSQL mappában
-- lekérdezésekhez a scriptek: createHRsyn (elég szinonimákat használni)
-- később, DML-utasításokhoz: createHRtables (itt saját tábláink legyenek)
Oracle PL/SQL segédanyagok: ebben az eduPLSQL mappában
-- itt a plsql_ fájlok forrása: innen -- és a PL/SQL könyvhöz a scriptek: itt
-- Tankönyvtár: PL/SQL programozás Oracle 10g-ben (Gábor A.-Juhász I.)
   Oracle 11gR2 Online Docu:  -- Tanszéki szerveren -- Oracle oldalán --
-- SQL Language Reference: HTML PDF (2009.10)    HTML   PDF (2014.07)
-- PL/SQL Language Refer.: HTML PDF (2010.03) HTML   PDF (2014.12)

Előadás: [az előadó weblapján ebből a mappából] [Kósa Balázs: Eloadas]
Tankönyv: Jeffrey D. Ullman, Jennifer Widom: Adatbázisrendszerek Alapvetés
Második átdolgozott kiadás, Panem, 2009. (a kék könyv)

1.GY: 2015.09.09. --- Relációs adatmodell, relációs algebra
> Ismerkedés, gyakorlat célja, menete és a követelmények
   > Relációk adatainak lekérdezése relációs algebra segítségével
Lekérdezések kifejezése természetes módon táblákkal, a táblákon
értelmezett műveletekkel gondolkodva, majd relációs algebrában:
   Relációs algebrai feladatok: 1.EA -> Relaciok.ppt feladatai

2.GY: 2015.09.16. --- Lekérdezések relációs algebrában és SQL-ben
> Gépes gyak.: Oracle adatbázisok elérése, SQL Developer használata
> SQL DDL: relációséma, adattípusok, megszorítások: create table
> SQL DML: relációelőfordulás, táblák feltöltése adatsorokkal: insert
> Előkészítés: A megadott scriptek segítségével hozzuk létre a táblákat:
createSzeret, Tankönyv feladataihoz: createTermékek, createCsatahajók
  majd az 1.GY. feladatait oldjuk meg gépnél SQL SELECT lekérdezésekkel:
> Tankönyv feladatai: Termékek-feladatok.pdf, Csatahajók-feladatok.pdf
Lekérdezések kifejezése relációs algebrában, majd a relációs algebrai
lekérdezéseket írjuk át SQL SELECT utasításra (a gépnél futtassuk le)

3.GY: 2015.09.23. --- Egy táblára vonatkozó lekérdezések az SQL-ben
> Gépes gyak.: SELECT utasítás SELECT, WHERE és ORDER BY záradékai
> Oracle Példák: Oracle segédanyagok eduSQL mappa (ehhez: createHRsyn)
>> Bevezetés (HR séma táblái), Sample Schemas: Fig.4-1 HR és OE sémák
>> 1/1.lecke (vetítés kiterjesztése, kifejezések),
>> 1/2.lecke (kiválasztás, kiválasztott sorok rendezése),
>> 1/3.lecke (sorfüggvények használata a SELECT és WHERE záradékban)

> További példák, see Examples: SQL Language Reference >> 5 Functions:
Sorfüggvények: pl. Karakteres függvények: SUBSTR, RPAD, ..., INSTR, ...
pl. Dátum függvények: MONTHS_BETWEEN, ..., pl. Konverziós: TO_DATE, ...
   pl. Null érték kezelése, helyettesítő érték megadása: NVL, COALESTE, ...
   Figyelem! COALESTE példában: FROM product_information
mivel ez a tábla nem az HR, hanem OE sémában szerepel, ezért
itt ki kell írni a táblatulajdonost: FROM OE.product_information
(ugyanis csak a HR owner tábláihoz hoztuk létre a szinonimákat).

4.GY: 2015.09.23. --- SELECT utasítás záradékai, aggr.fv, csoportosítás
> Gépes gyak.: SELECT utasítás FROM, GROUP BY és HAVING záradékai
> Egy táblára vonatkozó lekérdezések az SQL-ben: csoportosítás és
   összesítő függvények, SELECT utasítás GROUP BY és HAVING záradéka
> Oracle Példák: -- lekérdezésekhez elég szinonimákat venni: createHRsyn
>> 1/4.lecke (csoportosítás, group by és having záradékok)

5.GY: 2015.10.07. --- Lekérdezések relációs algebrában és SQL-ben
> Több táblára vonatkozó lekérdezések, összekapcsolások, alkérdések
> Tankönyv feladatai: Termékek-feladatok.pdf, Csatahajók-feladatok.pdf
   > Tankönyv feladataihoz a scriptek: create Termékek, create Csatahajók
> Tk.2.4.14.Feladatok (Tk.54-57.o. 2.4.1.feladat) Termékek feladatai
Lekérdezések kifejezése először (papíron) természetes módon táblákkal,
a táblákon értelmezett műveletekkel gondolkodva, és relációs algebrában
kifejezőfákkal is felrajzolva, majd átírva SQL SELECT utasítással (gépnél)
többféle átírást, megoldási lehetőséget vizsgáljunk meg, vessünk össze!
>>> Megoldás (ellenőrzésre) Termékek-feladatok-megoldása.pdf

6.GY: 2015.10.14. --- Több táblára vonatkozó lekérdezések az SQL-ben
> Több táblára vonatkozó lekérdezések, összekapcsolások, alkérdések
> Gépes gyak.: Több táblára vonatkozó lekérdezések
> Oracle Példák: -- lekérdezésekhez elég szinonimákat venni: createHRsyn
  >> 1/5.lecke (összekapcsolások, külső összekapcsolások)
>> 1/6.lecke és 2/6.lecke (alkérdések)
>> 1/7.lecke (halmaz- és multihalmaz műveletek)
> ZH előtti összefoglalás, konzultáció, kérdés/válasz

7.GY: 2015.10.21. --- I.ZH: Lekérdezések relációs algebrában és SQL-ben
> I.ZH témaköre: Lekérdezések kifejezése relációs algebrában és SQL-ben

   -- -- o ~ o ~ o~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o -- --
-- --    Őszi szünet : 2015. október 26-tól (hétfő) - október 30-ig (péntek) -- --
   -- -- o ~ o ~ o~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o ~ o -- --

8.GY: 2015.11.04. --- SQL DML (insert, delete, update), SQL DDL (táblák)
> I.rész: SQL DML-utasítások: INSERT, DELETE és UPDATE utasítások
  Tk. 6.5. Táblák tartalmának módosítása. (Ullman/Tk. alapján: 5ea_SQL DML)
> Oracle Példák: -- DML-utasításokhoz saját táblák létrehozása: createHRtables
>> 1/8.lecke SQL DML-utasítások (insert, update, delete) és a tranzakciók
> Feladatok: DML_feladatok.txt --- ehhez itt: createDolg (no constraint)

> II.rész: SQL DDL-utasítások: CREATE, ALTER, DROP -> TABLE
  Tk. 7. és 8. fejezetei. (Ullman/Tk. alapján prog.inf. 09.előadás.pdf)
> Oracle Példák:
>> 1/9.lecke SQL DDL (táblák és megszorítások létrehozása)
> További segédletek: create table, típusok és megszorítások (.txt)

9.GY: 2015.11.11. --- Relációs tervezés, SQL DDL (táblák, nézetek, indexek)
   > I.rész: Relációs modellezés (Ullman/Tk. alapján prog.inf. 08.előadás.pdf)
> Feladatok: -- Az alábbi feladatok a gyakorlatra közös megbeszélésre:
-- (1) Tk.4.1.-4.4. Az egyed-kapcsolat (E/K) modell elemei
  E/K diagramok felrajzolása: Modellezési feladatok - E/K diagram
   -- (2) Tk.4.5.-4.6. E/K diagram átírása relációs modellé
  E/K-diagr. átírása relációkká: EKpelda.pdf, E/K_pl1.pdf és E/K_pl2.pdf
-- (3) Tk.3.fejezet: Függőségek elmélete és normalizálás (lásd 13.GY)
   +++ HF: beadandó E/K modellezési feladat (papíron, ceruzával, saját téma!)

> II.rész: SQL DDL-utasítások: CREATE, DROP -> TABLE, VIEW, INDEX
   Tk. 7. és 8. fejezetei. (Ullman/Tk. alapján prog.inf. 09.előadás.pdf)
> Oracle Példák: -- saját táblák létrehozása: createHRtables
>> 1/10.lecke SQL DDL (nézettáblák, indexek és szinonimák létrehozása)
>> 2/1.lecke SQL DDL (táblákhoz megszorítások, indexek létrehozása)
> További segédletek: nezettáblák (.txt)
> Feladatok: Táblák és megszorítások (constraints), nézettáblák létrehozása.
  Adatok karbantartása adattáblán illetve nézeten keresztül, és megfigyelni
ezek egymásra hatását: Hogyan hat a nézetek adatainak módosítása az
adattáblára, és az adatok módosítása hogyan jelenik meg a nézettáblába

10.GY: 2015.11.18. --- PL/SQL blokk, deklaráció, utasítások, select into
> A gyakorlatokon: Tankönyv 9.fejezete SQL-2003/PSM a gyakorlatban:
  Oracle PL/SQL, ehhez Oracle 11gR2 doc PL/SQL Language Reference
  -- Az Oracle doksikban szereplő példák kipróbálásához: createHRtables

> I.rész: PL/SQL
> PL/SQL alapok, blokk szerkezete, változóhasználat >> 2.fej. Alapok
[1_deklarációs_rész] 2_végrehajtható_rész [3_kivételkezelő_rész]
>> példa-2-24 deklarációk-és-értékadás
>> példa-2-25 select-into (itt a 2-25 példában blokk: begin ... end; /
át kell tenni az end;-et a végére!) SELECT INTO-t akkor használjuk,
  ha a lekérdezés pontosan egy sort ad, ha a lekérdezés több sorral
tér vissza, akkor kurzort kell használni, lásd köv.gyakorlat anyagát).
>> (összefoglaló) plsql_02_tipusok.pdf,  03_sql_dml.pdf, 05_valtozok.pdf

> Vezérlési szerkezetek, utasítások a PL/SQL-ben >> 4.fej. Utasítások
>> példa-4-5 if-then-elsif utasítás,
>> példa-4-6 egyszerű case utasítás, és itt írjuk át grade := 'B' -> '&B'
helyettesítési változóra, aminek a felhasználó adja meg az értékét!
>> példa-4-10 alap LOOP ciklus utasítás EXIT WHEN kilépés a ciklusból
>> példa 4-14 FOR ciklus utasítás
>> példa 4-27 WHILE ciklus utasítás
>> (összefoglaló) plsql_01_bevezetes.pdf, pl02_vezerlo_utasitasok.txt

> PL/SQL feladatok: --- ehhez, mint a DML-hez volt: createDolg (no constraint)
-- A PL/SQL blokk előtt minden alkalommal állítsuk be: SET SERVEROUTPUT ON
1.) Az első feladat: Írjuk ki PL/SQL blokkból: 'Szia Világ!', majd egészítsük ki,
   kérjen be egy nevet, számot, dátumot és ezeket is írassuk ki a programból!
   2.) Írjuk ki KING fizetését (olvasás táblából változóba), abban az esetben,
ha ismert, hogy pontosan egy KING nevű dolgozó szerepel a táblában!

  > II.rész: Rekurzió. Az Eljut-feladat. Tankönyv 10.2. (Ullman-Widom kék könyv)
-- Segédanyag: 7.előadás.pdf -- Tk.10.2. Az Eljut-feladat. Rekurzió az SQL-ben
Az "Eljut feladat" megvalósítása PL/SQL-ben
   -- Az Eljut-feladat a Tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül
   -- Adott Jaratok(legitarsasag, honnan, hova, koltseg, indulas, erkezes) táblában
  repülőjáratok adatait tároljuk (honnan-hova várospárok). Azt keressük, hogy
Dallasból mely városokba tudunk eljutni (közvetlenül vagy egy/több átszállással).
   -- Ezzel a scripttel jaratok_tabla.txt készítsünk saját táblát, ami alapján dolgozunk.

> Rek1.feladat: Mely (x, y) várospárokra lehetséges egy vagy több átszállással
eljutni x városból y városba? -- Ehhez készítsünk egy Eljut(honnan, hova) táblát,
a sorait a járatok tábla alapján PL/SQL programmal töltsük fel (ciklust szervezni,
  az insert 2.alakja: több sor felvitele alkérdéssel/járatok és eljut táblák alapján).
>>> (csak ha kell, egy kis segítség, további ötletek és a megoldás vázlata: itt)

> Rek2.feladat: Mely (x,y) város párokra hány átszállással és milyen költségekkel
lehetséges egy vagy több átszállással eljutni x városból y városba? -- Ehhez is
készítsünk Eljut2(honnan, hova, atszallas, koltseg) táblát, a sorait programmal.
>> Papíron megoldandó feladat: Fejezzük ki az SQL-1999-es szabvány SELECT
WITH RECURSIVE utasítással, hogy mely mely városokba (hova) tudunk eljutni
'DAL' (Dallas)-ból legfeljebb 3 átszállással és legfeljebb 5000 költségből.
>>> (csak ha kell, egy kis segítség WITH RECURSIVE papíros részéhez itt)

11.GY: 2015.11.25. --- Lekérdezések, kurzorok használata a PL/SQL-ben
> A gyakorlatokon: Tankönyv 9.fejezete SQL-2003/PSM a gyakorlatban:
  Oracle PL/SQL, ehhez Oracle 11gR2 doc PL/SQL Language Reference

> DML utasítások a PL/SQL-ben, kurzorok >> 6.fej. Static SQL/Cursors
  -- Az Oracle doksikban szereplő példák kipróbálásához: createHRtables
>> példa6-1 DML utasítások a programban, implicit kurzor
>> példa-6-4 implicit kurzor attribútumok,
>> példa-6-7 explicit kurzorok deklarálása és használata, lásd 5.feladat,
>> példa-6-11-től 6-14 explicit kurzor attribútumok, lásd 6.feladat,
    >> példa-6-17 paraméteres kurzorok, lásd 7-8.feladat,
   >> példa-6-43 for update kurzor módosításhoz, lásd 9-10.feladat.
>> (összefoglaló) (volt:03_sql_dml.pdf), 13_kurzorok.pdf, pl03_cursor.txt

> PL/SQL feladatok: --- ehhez, mint a DML-hez volt: createDolg (no constraint)
-- A PL/SQL blokk előtt minden alkalommal állítsuk be: SET SERVEROUTPUT ON
     1-2.) Lásd az előző heti gyakorlaton az első PL/SQL feladatokat!
3-4.) Lásd az Eljut-feladat megvalósítását PL/SQL programmal: Rek1 és Rek2.
5.) Írjuk ki a dolgozók nevét és fizetését!
(több sort visszaadó lekérdezés, kurzor használata, lásd  példa-6-7 )
6.) Írjuk ki a 3. 5. és 8. legnagyobb fizetésű dolgozó nevét, fizetését!
-- kurzor attribútumok, lásd példa-6-14
7.) Írjuk ki azon dolgozók nevét és fizetését, akik fizetése nagyobb mint
a felhasználó által megadott szám!
-- felhasználói/helyettesítő változók: &numerikus_valt, '&karakteres_valt'
8.) Írjuk ki azon dolgozók nevét, fizetését és osztálykódját, akik a felhasználó
által megadott osztályon dolgoznak! A felhasználó által megadott betű
legyen A, R, S (Accounting ...) a 10, 20, 30-as osztály esetén.
    -- paraméteres kurzor, lásd példa-6-17
9.) Növeljük meg a hivatalnokok (CLERK) fizetését a saját fizetésük 20%-ával!
-- for update kurzor módosításhoz, lásd ugyanez a feladat: példa-6-43
   10.) Módosítsuk a dolgozók nevét írjuk át, hogy csak a kezdőbetű legyen nagy,
a többi betű kicsi, továbbá a dolgozók fizetését is növeljük meg 2 %-kal!
-- for update kurzor módosításhoz, lásd példa-6-43

További (szorgalmi) témakörök, de ez már nem szerepel a dolgozatban:
   > PL/SQL eljárások és függvények >> 8.fej. PL/SQL Subprograms
>> példa2-19 Alprogramok (már itt is volt: 2.fej. Alapok)
>> példa4-1 IF-THEN utasítás (már itt is volt: 4.fej. Utasítások)
>> (összefoglaló) plsql_06_alprogramok.pdf, pl06_procedura_fv.txt
   > PL/SQL kivétel- és hibakezelés >> 11.fej. PL/SQL Error Handling
   >> példa-11-5 és tábla-11-2 előre definiált kivételek
>> példa-11-8 felhasználó által definiált kivételek
>> (összefoglaló) plsql_07_kivetelek.pdf, pl05_exception.txt
> (összefoglalásként) plsql_13_kurzorok.pdf

12.GY: 2015.12.02. --- II.ZH: SQL DDL, DML utasítások és PL/SQL
> GÉPES II.ZH témaköre:
   8.gyak. SQL DML feladatai (select, insert, delete, update),
   9.gyak. SQL DDL (create view, és a nézettáblák használata),
10.gyak.PL/SQL feladatok (egy sort visszaadó lekérdezésekre select into),
  11.gyak.PL/SQL feladatok (több sort visszaadó lekérdezésekhez kurzorok),
-- ehhez 1-2 oldalas segédanyagot is lehet készíteni, amit a ZH-án használhatnak,
(de mindenki csak a sajátját), továbbá elérhető az Online Oracle PL/SQL doksi:
http://medusa.inf.elte.hu/oradoc11/appdev.112/e10472/toc.htm (2, 4, 6.fej.)

13.GY: 2015.12.09. --- Összefoglalás, feladatok függőségekre, normalizálásra
> A gyakorlat célja az volt, hogy a hallgatók minél hatékonyabban tudjanak
  SQL és PL/SQL feladatokat megoldani, és azt is értsék mi-hogyan történik.
> Összefoglalás, áttekintés az előadásanyagról, fuggoseg_feladat.pdf (forrás: itt)