Adatbázis_I

E/K modell leképezése relációs modellre
Rekurzív nem-negált Datalog és SQL3 WITH RECURSIVE
PL/SQL alapok, feladatok

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.
-- Megvalósítása az Oracle relációs adatbázis-kezelővel, vagyis
táblák és megszorítások létrehozása,  [UW1] 2.3., 2.5.fej és 7.1-7.4.fej.
és táblák feltöltése a megszorításoknak eleget tevő sorokkal, [UW1] 6.5.fej.
-- II.ZH-án várható feladat adott E/K modell (vagyis adott egy E/K diagram)
ezt kell leképezni relációs modellre (meg kell adni a relációs adatbázissémát)

Példák: E/K modellre és relációs sémákká való átírásukra
-- 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

Oracle alaptáblák sémái: Oracle minta alaptáblák sémái
-- Samples Schemas (10.2) HTML    PDF és itt >> 3.Diagrams

Feladat: Írd át a HR-sémát E/K diagrammá! (klasszikus Chen jelöléssel)

Rekurzív nem-negált Datalog és SQL3 WITH RECURSIVE

[Ullman-Widom] 5.3. Datalog szabályok és lekérdezések
[Ullman-Widom] 10.2. Rekurzió az SQL-ben (466-474.o.)

Rek1.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.
- Készítsetek ebből saját példányt: jaratok_tabla.txt és az alapján dolgozzatok
DROP TABLE jaratok;
CREATE TABLE jaratok(
  legitarsasag CHAR(2),
  honnan VARCHAR2(10),
  hova VARCHAR2(10),
  koltseg NUMBER,
  felszallas NUMBER,
  erkezes NUMBER);

INSERT INTO jaratok VALUES('UA', 'SF','DEN', 1000, 930,1230);
INSERT INTO jaratok VALUES('AA', 'SF','DAL', 10000, 900,1430);
INSERT INTO jaratok VALUES('UA','DEN','CHI', 500, 1500,1800);
INSERT INTO jaratok VALUES('AA','DEN','DAL', 2000, 1400,1700);
INSERT INTO jaratok VALUES('AA','DAL','CHI', 600, 1530,1730);
INSERT INTO jaratok VALUES('AA','DAL', 'NY', 2000, 1500,1930);
INSERT INTO jaratok VALUES('AA','CHI', 'NY', 3000, 1900,2200);
INSERT INTO jaratok VALUES('UA','CHI', 'NY', 2000, 1830,2130);

- (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(L, X, Y, K, F, E)
   2.    Eljut(X, Y) <- Eljut(X, Z) AND Jaratok(L, Z, Y, K, F, E)

- (Papíros feladat) A fenti rekurzív Datalog programot írd át az SQL3 szabványban
szereplő WITH RECURSIVE utasítással! (Csak papíron! Oracle nem támogatja).
- Megoldás:
WITH 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;

- (Gépes feladatok: Oracle CONNECT BY PRIOR, lásd előző órán 8.gyak#CSFA)
Amennyiben azt szeretnénk megtudni, hogy mely városokba lehet eljutni Dallasból
ezt a következő hierarchikus lekérdezéssel kapjuk meg ('DAL'='Dallas')
SELECT DISTINCT hova
FROM jaratok
WHERE HOVA <> 'DAL'
START WITH honnan = 'DAL'
CONNECT BY NOCYCLE PRIOR hova = honnan;

- Átszállásokkal mely városokba lehet eljutni San Franciscoból ('SF'='San Francisco')
SELECT LPAD(' ', 4*level) ||honnan, hova, level-1 Atszallasok
FROM jaratok
WHERE HOVA <> 'SF'
START WITH honnan = 'SF'
CONNECT BY NOCYCLE PRIOR hova = honnan;

- A hierarchikus lekérdezésben további pszeudo oszlopokat is használhatunk, amint
azt az alábbi példában láthatjuk az úzvonal megadását
SELECT hova, sys_connect_by_path(honnan||'->'||hova, '/'),
connect_by_isleaf, connect_by_iscycle
FROM jaratok
START WITH honnan = 'SF'
CONNECT BY NOCYCLE PRIOR hova = honnan;

- Szúrjunk be még egy sort, ami után már irányított kör is lesz a táblában:
INSERT INTO jaratok VALUES('LH', 'CHI', 'DEN', 2000, 1900, 2100);

- Mely (x, y) várospárokra lehetséges egy vagy több átszállással eljutni x városból y városba?
   Ezt PL/SQL programmal fogjuk Oracle-ben megoldani (folytatjuk, lásd 11.gyak#Rek1)

PL/SQL alapok, feladatok

SEGÉDANYAGOK: ORACLE PL/SQL:
Oracle® Database 10g (10.2) Documentation Library
- Oracle PL/SQL User's Guide and Ref.10g    HTML PDF
- Oracle PL/SQL Packages and Types Ref.10g HTML PDF

További segédanyagok a PL/SQL-hez:
- Nikovits Tibor (ELTE) PL/SQL összefoglaló: NT_Plsql.htm
- Kósa Balázs (ELTE) PL/SQL összefoglaló: KB_Plsql.pdf
- Ullman/Chang (Standford) Using Oracle PL/SQL
- Ullman/Chang (Standford) Constraints and Triggers
- PL/SQL Technology Center (Oracle 11g PL/SQL)

PL/SQL FELADATOK:
-- Táblák és az eddigi sql feladatok: table_dolgozo.txt
-- Ehhez a táblák létrehozása: create_dolgozo.txt

1.) Hello World program (alap program, kiírás a képernyőre)

2.) Írjuk ki KING fizetését (olvasás táblából változóba)

3.) Írjuk ki KING belépési dátumát és fizetését különböző
formátumokban 1981.11.17, 1981-november-17, stb
(dátum formátumok, több oszlopos lekérdezés)

4.) Írjuk ki a dolgozók nevét és fizetését (kurzor használata)

5.) Írjuk ki a 3. 5. és 8. legnagyobb fizetésű dolgozó nevét, fizetését
(kurzor attribútumok)

6.) Tegyük be a dolgozók nevét egy plsql tömbbe, és írjuk ki az utolsó előtti sort.
(plsql tömb használata)

(folytatása a következő 10.gyakorlaton)

További gyakorló feladatok találhatóak az Oracle Példatárban
-- Lásd Feladatok.pdf (8-9.fejezet feladatai)
-- Ehhez: a táblák létrehozása  cr_dept_emp.sql

Vissza az AB1 gyakorlat oldalára

Vissza a Kezdőlapra