Adatbázis_I

11.gyak. E/K diagram és átalakítása relációkká, PL/SQL-3

Következő 12.gyak: II.ZH

> 1.TÉMA: E/K diagram és leképezése relációs modellre
> 2.Téma: Oracle PL/SQL 3.rész gépes gyakorlat
>> PL/SQL feladatok (9-12.gyakorlatra)
> 3.Téma: Az Eljut feladat megvalósítása PL/SQL-ben is

1.TÉMA: E/K diagram és leképezése relációs modellre
Ullman-Widom: Adatbázisrendszerek. Alapvetés (Második, átdolg.kiad), 2009.
-- E/K modell és leképezése relációs modellre, [UW1] 4.1-4.6.fej., 133-181.o.
-- II.ZH-án várható feladat: adott E/K diagram átírása relációs modellé, vagyis
1.) egyedhalmazok és 2.) kapcsolatok átírása relációkká, 3.) összevonások,
4.) gyenge egyedhalmazok kezelése, 5.) osztályhierarchia ("isa") átalakítása
relációkká (a három megközelítés: E/K típusú, obj.orientált, nullértékekkel).

Példák: E/K diagram leképezése relációs modellre, lásd az előadás anyagot!
Feladat: E/K diagram leképezésére, lásd EKpelda.png illetve EKpelda.pdf

--- Gyakorlás: papíros feladatok: Tk.4.1.-4.6. Adatmodellezési feladatok
További feladatok a Tankönyv 4.5-4.6 fejezetei és feladatai alapján:
-- lásd E/K_pl1.pdf és E/K_pl2.pdf
További irodalom (elektronikusan is elérhető magyar nyelvű könyvek)
--- MEK könyvtár: Halassy Béla: Adatmodellezés, elmélet és gyakorlat
--- MEK könyvtár: Halassy Béla: Az adatbázistervezés alapjai és titkai

2.TÉMA: ORACLE PL/SQL 3.rész
(ez az előző heti Oracle PL/SQL alapok folytatása)
> 3.6. PL/SQL tárolt alprogramok -->> plsql_06_alprogramok.pdf
> 3.7. PL/SQL hiba- és kivételkezelés -->> plsql_07_kivetelek.pdf
> 3.8. PL/SQL triggerek -->> plsql_09_triggerek.pdf
és plsql_10_rendszertriggerek.pdf

PL/SQL FELADATOK-3
-- Az előző heti PL/SQL FELADATOK-2 folytatása
(aki azt még nem fejezte be, előbb azokat írja meg és utána ezeket).
>> PL/SQL feladatok (9-12.gyakorlatra)

10.) Módosítsuk a fizetéseket egy kurzorral végighaladva rajtuk!
Adjunk hozzá mindenki fizetéséhez n*10 ezret, ahol n a nevében
levő magánhangzók száma (a, e, i, o, u)!
(módosítás kurzorral, beépített függvények)

11.) Írjunk meg egy függvényt, ami az azonosító alapján visszaadja a nevet.
Írjunk meg egy eljárást, ami az azonosító alapján egy OUT változóban
visszaadja a nevet. (alprogramok, függvény és procedúra)

12.) Írjunk meg egy függvényt, ami visszaadja a paraméterében levő
magánhangzók számát! Majd módosítsuk a fizetéseket ennek a
függvénynek a segítségével. (UPDATE utasítással, nem plsql-ben)

3.6. PL/SQL tárolt eljárások, függvények és csomagok

-- Példák PL/SQL függvények és eljárások használatára plsql_proc_fv.sql
-- Példák csomag használatára: plsql_csomag.sql és plsql_package.sql (N.T.)
-- PL/SQL Language Reference 11gR2 >> 8.fej. PL/SQL Subprograms
-- PL/SQL Language Reference 11gR2 >> 10.fej. PL/SQL Packages

Gyakorló feladatok az Oracle Példatárból -- Ehhez: cr_dept_emp.sql
Lásd Példatár "10.fejezet. Kivételkezelés, alprogramok, triggerek" feladatai

3.7. PL/SQL hiba- és kivételkezelés

-- Példák hiba- és kivételkezelésre: plsql_exception.sql (Nikovits Tibor)
-- PL/SQL Language Reference 11gR2 >> 11.fej. PL/SQL Error Hangling

Gyakorló feladatok az Oracle Példatárból -- Ehhez: cr_dept_emp.sql
Lásd Példatár "10.fejezet. Kivételkezelés, alprogramok, triggerek" feladatai

5a_kivetelkez.sql - Feladat kivételkezelésre
- Írjunk egy olyan eljárást, amely kivételkezelést is tartalmaz és a jutalmat az
emp táblából létrehozott dolgozo tábla jutalék (comm) értékéhez adja hozzá!
A jutalom a dolgozó fizetésének 10%-a, feltéve, ha a fizetés 3000 dollár
alatt van, egyébként csak egy "Boldog Karácsonyt!" üdvözletet kap.

3.8. PL/SQL triggerek

-- Ez a rész csak a keddi csoportban van a 10. gyakorlaton.
-- PL/SQL Language Reference 11gR2 >> 9.fej. Triggers

Gyakorló feladatok az Oracle Példatárból -- Ehhez: cr_dept_emp.sql
Lásd Példatár "10.fejezet. Kivételkezelés, alprogramok, triggerek" feladatai

6a_trigger.sql - triggerek
- Hozzunk létre BEFORE triggert, amely megakadályozza a munkaidőn
kívüli adatmanipulációkat az emp táblán! Írassuk ki, milyen műveleteket
kíséreltek meg végrehajtani munkaidőn kívül!

6b_triggerek.sql - triggerek
- Írjunk triggert (és ellenőrizzük is a működését), amely megakadályozza
az elnökre (president) vonatkozó törlő, beszúró és adatmódosító
DML utasítások működését! (a tesztelő script programban a hivatkozási
megszorítás felfüggesztése illetve a végén az újbóli engedélyeztetése)

3.TÉMA: ELJUT feladat megvalósítása PL/SQL-ben is
> Rekurzió az SQL3-ban WITH RECURSIVE utasítás
> Eljut feladat megvalósítása Oracle PL/SQL programmal

Rekurzió SQL3 szabványban WITH RECURSIVE

Tankönyvben:
- Rekurzió: [Ullman-Widom] 10.2. Rekurzió az SQL-ben (466-474.o.)

Az Eljut feladat:
- Az alábbi feladat a tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül.
Jaratok(legitarsasag, honnan, hova, koltseg, indulas, erkezes)
táblában repülőjáratok adatait tároljuk. Azt keressük, hogy Dallasból mely városokba
tudunk eljutni (átszállás nélkül közvetlenül vagy egy vagy több átszállással).
- (Papíros feladat) Fejezzük ki az SQL3 szabványban szereplő WITH RECURSIVE
utasítással, hogy mely (x,y) város párokra lehetséges közvetlenül, egy vagy több
átszállással eljutni x városból y városba? ! (Csak papíron! Oracle nem támogatja).
- Megoldás:
WITH RECURSIVE eljut AS
(SELECT honnan, hova FROM jaratok
UNION
      SELECT eljut.honnan, jaratok.hova
      FROM eljut, jaratok
      WHERE eljut.hova = jaratok.honnan)
SELECT hova FROM eljut WHERE honnan='DAL';
- Fontos: UNION (halmaz) és UNION ALL (multihalmaz) közötti különbség!!!

Az Eljut feladat Oracle 11gR2-ben lekérdezéssel WITH utasítással

-- Oracle 11gR2 megoldás:
with eljut (honnan, hova) as
(select honnan, hova from jaratok
union all
select jaratok.honnan, eljut.hova
from jaratok, eljut
where jaratok.hova=eljut.honnan
)
SEARCH DEPTH FIRST BY honnan SET SORTING
CYCLE honnan SET is_cycle TO 1 DEFAULT 0
select distinct honnan, hova from eljut order by honnan;

Az "Eljut feladat" PL/SQL-ben (ez PSM-ben volt az előadáson!)

Rek1.feladat: Segédanyag, lásd az előadás anyagát >> Rekurzió-PSM-ben.pdf
- Az alábbi feladat a Tankönyv (Ullman-Widom kék könyv) 10.2 szakaszára épül.
   Jaratok(legitarsasag, honnan, hova, koltseg, indulas, erkezes)
táblában repülőjáratok adatait tároljuk. A tábla létrehozása, lásd jaratok_tabla.txt

Feladat PL/SQL-ben:
- A fenti nemnegált rekurzív Datalog programot írjuk át Oracle PL/SQL programra!
- Ehhez előbb hozzuk létre egy alábbi szerkezetű ELJUT1 táblát:
DROP TABLE Eljut1;
CREATE TABLE Eljut1(
   honnan VARCHAR2(10),
   hova VARCHAR2(10));

- Írjunk egy olyan PL/SQL programot, ami feltölti az ELJUT1 táblát a megfelelő
város párokkal, ahol az első városból el lehet jutni a másodikba. Mely (x, y)
várospárokra lehetséges egy vagy több átszállással eljutni x városból y városba?

Megoldás vázlata (az előadáson szerepelt ez a vázlat és a vizsgára is kell!)
-- Eljut tábla növelése ciklusban, a ciklus során ellenőrizni kell, hogy történt-e
változás, növekszik-e a sorok száma (Számláló), duplikátumokra figyelni kell!
-- A ciklus előtt kezdeti értékek beállítása
delete from eljut1;
RegiSzamlalo:= 0;
insert into eljut1
(select distinct honnan, hova from jaratok);
  select count(*) into UjSzamlalo from eljut1;
-- A ciklust addig kell végrehajtani, ameddig növekszik az eredmény, fontos,
hogy csak olyan várospárokat vegyünk az eredményhez, ami még nem volt!
Ezt többféleképpen szűrhetjük, az alábbi megoldásban NOT IN alkérdést
használunk. Próbáljuk ki más megoldásokkal is, például NOT EXISTS
alkérdéssel vagy a MINUS halmazművelettel illetve egyéb megoldásokkal!
LOOP
insert into eljut1
(select distinct eljut1.honnan,jaratok.hova
from eljut1, jaratok
where eljut1.hova = jaratok.honnan
and (eljut1.honnan,jaratok.hova)
NOT IN (select * from eljut1));
  select count(*) into UjSzamlalo from eljut1;
EXIT WHEN UjSzamlalo = RegiSzamlalo;
RegiSzamlalo := UjSzamlalo;
END LOOP;
-- A program végrehajtása után ellenőrizzük le, kérdezzük le az eljut1 táblát:
select * from eljut1 order by honnan, hova;

Rek2.feladat:
- (Papíros feladat) Fejezzzük ki Datalog programmal, hogy 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?
- Megoldás:
   1.    Eljut(x, y, n, k) <- Jaratok(_, x, y, k, _, _) AND n=0
   2.    Eljut(x, y, n, k) <- Eljut(x, z, n1, k1) AND Jaratok(_, z, y, k2, _, _) AND
AND n=n1+1 AND k=k1+k2

- Most hozzuk létre egy alábbi szerkezetű ELJUT2 táblát, amely a költséget is tartalmazza:
DROP TABLE Eljut2;
CREATE TABLE Eljut2(
   honnan VARCHAR2(10),
   hova VARCHAR2(10),
atszallas NUMBER,
   koltseg NUMBER);

- Most úgy töltsük fel az Eljut2 táblát, hogy az átszállás oszlop tartalmazza hányszor
kellett átszállni és a költség oszlop az utazás költséget (repülőjegyek árának összegét)
tartalmazza a két város között (A és B város között). Ennek az Eljut2 táblának a
segítségével keressük ki a két város között a lehető legolcsóbb út költségét.

Rek3.feladat:
- Tegyük fel, hogy nemcsak az érdekel, hogy el tudunk-e jutni az egyik városból a másikba,
   hanem az is, hogy utazásunk során az átszállások is ésszerűek legyenek, vagyis ha több
   járattal  utazunk és átszállásra van szükségünk, akkor az érkező járatnak legalább egy
   órával a rá következő indulás előtt meg kell érkeznie.
   (Feltehetjük, hogy nincs egy napnál hosszabb utazás)
- Ezt a feladatot is fejezzzük ki előbb Datalog programmal, majd hozzuk létre az ELJUT3
táblát a megfelelő szerkezettel, és most úgy töltsük fel az ELJUT3 táblát, hogy a minimális
időt tartalmazza két város között (A és B város között a lehető leggyorsabb út és ideje)

Rek4.feladat:
- A fenti feladatokat oldjuk meg PL/SQL-ben úgy is, hogy ne csak a várospárokat,
hanem a teljes útvonalat is listázzuk ki.

Fel a lap tetejére

Vissza az AB1gyak oldalára (főmenü)