Adatbázis_I

Rekurzív nem-negált Datalog program kiértékelése PL/SQL-ben,
E/K diagram leképezése relációs modellre

> Gépes feladatok:
>> 3.5. PL/SQL csomagok, kivételkezelés
>> 3.6. PL/SQL triggerek (ez csak tananyagon kívül)
>> 3.7. Az "Eljut feladat" PL/SQL-ben (ez tananyag!)
-- Beküldendő nem-kötelező h.f.: plsql_feladatok2

> Papíros feladatok:
>> 4.1. E/K diagram leképezése relációs modellre

> Következő gyakorlaton:
>> II.ZH

3.5. PL/SQL csomagok, kivételkezelés

SEGÉDANYAGOK: ZH-n is használható SQL, PL/SQL Ref.
A ZH-n a tűzfalon keresztül is elérhető és használható a dokumentáció:
http://medusa.inf.elte.hu/ tanszéki honlapon alul Oracle linken található
Online documentation for Oracle 11g R2 bal oldali menüből kiválasztva
a harmadik sorban Database Administration és itt a harmadik dobozban:
SQL, PL/SQL, and PL/SQL Packages

SQL Language Reference
SQL Language Quick Reference
PL/SQL Language Reference
PL/SQL Packages and Types Reference

HTML PDF
HTML PDF
HTML PDF
HTML PDF

Itt érdemes átnézni a PL/SQL Lang.Ref. példáit, mint ahogy múlt héten néztük
6.fejezet Cursors részben "FOR UPDATE"-re kereséssel az "Example 6-43"-t.

-- Példák csomag használatára: plsql_csomag.sql és plsql_package.sql
-- Példák hiba és kivételkezelésre: plsql_exception.sql

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.6. PL/SQL triggerek (ez csak tananyagon kívül)

A triggerek témakör (Tankönyv 7.5. szakasza) az előadáson szerepelt,
de a BSc gyakorlaton nem jut rá elég idő, a II.Zh-án és a vizsgán sem lesz.
Később, az MSc gyakorlaton foglalkozunk majd részletesen a triggerekkel.

4a_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!

4b_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.7. Az "Eljut feladat" PL/SQL-ben (ez tananyag!)

Rek1.feladat: Segédanyag, lásd az előadás anyagát >> Rek.PSM (jelszóval)
- 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
- Az alapfeladat, hogy adjuk meg mely (x, y) várospárokra lehetséges egy vagy több
átszállással eljutni x városból y városba? Ezt egy relációs algebrai kifejezésként
nem tudjuk megadni zárt alakban, SQL SELECT utasítással sem tudjuk kifejezni,
csak azt tudjuk, hogy átszállás nélkül, egy átszállással, két átszállással, stb...
   select distinct honnan, hova
from jaratok
union
   select j1.honnan, j2.hova
from jaratok j1, jaratok j2
where j1.hova=j2.honnan
and j1.honnan<>j2.hova
   union
select j1.honnan, j3.hova
from jaratok j1, jaratok j2, jaratok j3
where j1.hova=j2.honnan
and j2.hova=j3.honnan
and j1.honnan<>j3.hova
union stb...

Emlékeztető, ezért vezették be az SQL3 szabványban a WITH RECURSIVE-t,
bővebben lásd a két héttel ezelőtti gyakorlaton: 9gyak#2.7.Rekurzió
- (Papíros feladat) Fejezzzük ki Datalog programmal, hogy mely (x,y) város párokra
lehetséges egy vagy több átszállással eljutni x városból y városba?
- Megoldás:
   1.    Eljut(X, Y) <- Jaratok(_, X, Y, _, _, _)
   2.    Eljut(X, Y) <- Eljut(X, Z) AND Jaratok(_, Z, Y, _, _, _)

- (Papíros feladat) A fenti rekurzív Datalog programot írjuk át az SQL3 szabványban
szereplő WITH RECURSIVE utasítással! (papíron, mert az 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
   AND eljut.honnan <> jaratok.hova)
SELECT * FROM eljut;
- Megjegyzés: A fenti megoldásban kizártuk az önmagával szerepelő várospárokat.
   Emlékeztető: UNION (halmaz) és UNION ALL (multihalmaz) közötti különbség!

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?
(Megjegyzés: Önmagával ne szerepeljen egy város sem.)

Rek2.feladat:
- (Papíros feladat) Fejezzzük ki Datalog programmal, hogy mely (x,y) város párokra
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, K) <- Jaratok(_, X, Y, K, _, _)
   2.    Eljut(X, Y, K) <- Eljut(X, Z, K1) AND Jaratok(_, Z, Y, K2, _, _) 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),
   koltseg NUMBER);

- Most úgy töltsük fel az ELJUT2 táblát, hogy a költség oszlop a minimális költséget
tartalmazza a két város között (A és B város között a lehető legolcsóbb út költsége)

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 old meg PL/SQL-ben úgy is, hogy ne csak a várospárokat, hanem
a teljes útvonalat is listázzuk ki.

PL/SQL FELADATOK-2 (BEKÜLDENDŐ NEM-KÖTELEZŐ HF)

-- Beküldendő nem-kötelező házi feladat, bemutatás esetén órai+pont,
amely csak féljegy esetén számít bele a gyak.jegybe (felfelé kerekítés),
hasonlóan, mint az I.ZH előtti belépő dolgozatoknál (cél: a gyakorlás)
-- Beküldése e-mailben sila@inf.elte.hu címre HATÁRIDŐ: május 19. 12h

Feladatok: A fenti Rek1, Rek2, Rek3 és Rek4 megoldása PL/SQL-ben.

4.1. E/K diagram 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).

Segédanyag, lásd az előadást >> E/Kdiagram átírása relációs modellé (jelszóval)
Példák: E/K diagram leképezése relációs modellre
-- E/K átírása relációkra, összevonás, lásd E/K_A.pdf (Kiss A./Ullman: Princ.of DB)
-- Gyenge egyedhz, Osztályhierarchia átalakítása relációkká, lásd E/K_A2.pdf
-- Orvosi adatbázis rendszer >> lásd E/K_B.pdf (Kósa B.- kicsit más jelöléssel)
-- Tanulmányi rendszer >> lásd E/K_C.pdf (Hajas Cs.) -- HF: átírni relációkká!
-- Gyakorló példák, lásd Tankönyv 4.5-4.6 >> E/K_pl1.pdf és HF: E/K_pl2.pdf

ZH előtti összefoglalás, kérdés-válasz.
Következő gyakorlaton: II.ZH

Vissza az AB1 gyakorlat oldalára

Vissza a Kezdőlapra