Adatbázis_I

7.gyak. SQL DDL, DML áttekintése, nézetek, inline nézetek

Feladatok: E/K modell, alapfogalmak, E/K diagram leképezése relációkra,
táblák és megszorítások megvalósítása: SQL DDL megszorítások megadása,
egyénileg: DML feladatok, közben személyes konzultáció: I.ZH megbeszélése.

-- A gyakorlat táblás része: Magasszintű adatmodellek:
> E4. E/K diagram és leképezése relációs modellre

-- A gyakorlat gépes része: Oracle: SQL teljes áttekintés
> 2.1. Relációsémák és megszorítások definiálása SQL-ben
> 2.2. DML: insert, delete, update -- create table-no-constraints
> 2.3. Feladatok nézetekre, inline nézetre és WITH utasításra
> 2.4. Felső-N elemzés ROWNUM segítségével
> 2.5. Hierarchikus lekérdezések (CONNECT BY)

E4. 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

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

ORACLE SQL ÁTTEKINTÉSE:
SQL DDL (CREATE TABLE, CREATE VIEW)
-- Táblák és megszorítások (constraints) létrehozása.
-- Egyszerű és összetett nézettáblák létrehozása.
SQL tranzakciókezelés
-- tranzakciókezelés alapjai, érvényesítés: COMMIT,
és visszagörgetés: ROLLBACK
és mentési pontok: SAVEPOINT
SQL DML (INSERT, DELETE, UPDATE)
-- 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 a másik irányban,
az adatok módosítása hogyan jelenik meg a nézetben?
-- Eközben mentési pontokat adjunk meg,  ahova folyton
visszagörgetjük (visszaállítjuk a táblák eredeti állapotát).
SQL lekérdezések: Oracle specialitások
-- Felső-N elemzés ROWNUM segítségével
-- Hierarchikus lekérdezések CONNECT BY PRIOR
Ennyi! -- Jön: Oracle PL/SQL (8-12. gyakorlatokon)

2.1. Relációsémák és megszorítások definiálása SQL-ben

CREATE TABLE - Relációsémák és megszorítások megadása

Példák és feladatok:
>> Dolgozo és Osztaly táblák (megszorításokkal való) létrehozása
>> SQL_21_Tablak.pdf példái -- Ehhez create_synonym_HR
>> Táblák létrehozása, Oracle típusok
>> Megszorítások példák
>> Ullman (Stanford): Constraints and Triggers

1.) Írjon szkript programot, amely új adattáblát hoz létre az emp és dept táblákból
myemp és mydept néven. Ha már léteznek ilyen nevű táblák, akkor előbb
azokat törölje. Véglegesítse az adattáblákat (commit).
A létrehozott táblákat lássa el az alábbi megszorításokkal:
- Legyen az empno elsődleges kulcs a myemp táblában, és
- legyen a deptno elsődleges kulcs a mydept táblában és idegen kulcs a myemp
táblában, amely a hivatkozási épséget biztosítja oly módon, hogy egy osztály
törlése esetén törlődjenek ennek az osztálynak a dolgozói is.
- Egy új dolgozó csak az adott 700 és 7000 USD értéktartománybeli fizetést
kaphasson.
2.) Ellenőrizze ezeket a megszorításokat sikeres (megfelelő rekord felvitele)
és sikertelen (hibás rekord) adatbeviteli kísérletekkel, majd állítsa vissza
az eredeti táblatartalmakat (rollback). Véglegesítse az adattáblákat (commit).

További gyakorló feladatok: lásd Példatár "5.fejezet. Adattáblák létrehozása,
módosítása, tranzakciók, megszorítások" DML- és DDL- utasításokra feladatok

2.2. Adattáblák tartalmának módosítása (DML)

Feladatok: DML_feladatok.txt -- Előkészítés: Ezekhez a DML feladatokhoz
átmenetileg most hivatkozási épség megadása nélkül hozzuk létre a táblákat:
>> Módosított Dolgozo és Osztaly táblák létrehozása (no constraints)

DELETE
-- Töröljük azokat a dolgozókat, akiknek jutaléka NULL.
-- Töröljük azokat a dolgozókat, akiknek a belépési dátuma 1982 előtti.
-- Töröljük azokat a dolgozókat, akik osztályának telephelye DALLAS.
-- Töröljük azokat a dolgozókat, akiknek a fizetése kisebb, mint az átlagfizetés.
-- Töröljük a jelenleg legjobban kereső dolgozót.
-- Töröljük ki azokat az osztályokat, akiknek van olyan dolgozója, aki a 2-es
  fizetési kategóriába esik (lásd FizFokozat táblát, adjuk meg azon osztályok
nevét, amelyeknek van olyan dolgozója, aki a 2-es fizetési kategóriába esik)
-- Töröljük ki azon osztályokat, amelyeknek 2 olyan dolgozója van, aki a 2-es
  fizetési kategóriába esik.

INSERT
- Vigyünk fel egy 'Kovacs' nevű új dolgozót a 10-es osztályra a következő
értékekkel: dkod=1, dnev='Kovacs', oazon=10, belépés=aktuális dátum,
fizetés=a 10-es osztály átlagfizetése. A többi oszop legyen NULL.

UPDATE
-- Növeljük meg a 20-as osztályon a dolgozók fizetését 20%-kal.
-- Növeljük meg azok fizetését 500-zal, akik jutaléka NULL vagy a fizetésük
kisebb az átlagnál.
-- Növeljük meg mindenkinek a jutalékát a jelenlegi maximális jutalékkal.
(NULL tekintsük 0-nak)
-- Módosítsuk 'Loser'-re a legrosszabbul kereső dolgozó nevét.
-- Növeljük meg azoknak a dolgozóknak a jutalékát 3000-rel, akiknek
legalább 2 közvetlen beosztottjuk van.
Az ismeretlen (NULL) jutalékot vegyük úgy, mintha 0 lenne.
-- Növeljük meg azoknak a dolgozóknak a fizetését, akiknek van beosztottja
a minimális fizetéssel
-- Növeljük meg a nem főnökök fizetését a saját osztályuk átlagfizetésével

További gyakorló feladatok: lásd Példatár "5.fejezet. Adattáblák létrehozása,
módosítása, tranzakciók, megszorítások" DML-utasításokra feladatok

2.3. Nézetek, inline nézet, WITH utasítás az Oracle-ben (ez nem a szabvány!)

CREATE VIEW - Egyszerű és összetett nézettáblák létrehozása és használata.
Kipróbálni. Mikor lehetséges az alaptáblák karbantartása a nézeteken keresztül?
További részletek, lásd SQL Language Reference -> Nézettáblák létrehozása

Feladatok:
1.feladat: (Nikovits Tibor) A lekérdezésekhez NIKOVITS felhasználó
tulajdonában levő táblákat használjuk! Elérése: tulajdonos.táblanév

NIKOVITS.CIKK (ckod, cnev, szin, suly)
NIKOVITS.PROJEKT (pkod, pnev, helyszin)
NIKOVITS.SZALLITO (szkod, sznev, statusz, telephely)
NIKOVITS.SZALLIT (szkod, ckod, pkod, mennyiseg, datum)

-- Melyik cikket szállítják a legtöbb projekthez?

A lekérdezést adjuk meg nézettáblák létrehozásával valamint úgy is, hogy
ne kelljen nézetet létrehozni, a "nézet" csak a lekérdezés idejére jön létre:
adjuk meg a lekérdezést INLINE nézettel, majd ugyanez WITH-del is.

Megoldása: Nezet_feladatok.txt (WITH munkatáblák használatára is példa)
Figyelem! Ez az Oracle WITH utasítás nem ugyanaz, mint az előadáson
és a kék-Tk 10.2 fejezetében szereplő WITH RECURSIVE utasítás!
Rekurzió az SQL-ben feladatokra a 11.gyakorlaton nézzünk példákat!

2.feladat:
DOLGOZO (dkod, dnev, foglalkozas, fonoke, belepes, fizetes, jutalek, oazon)
OSZTALY (oazon, onev, telephely)

Képezzük osztályonként az összfizetést, vegyük ezen számok átlagát, és adjuk
meg, hogy mely osztályokon nagyobb ennél az átlagnál az összfizetés.

További gyakorló feladatok: lásd Példatár "6.fejezet. Nézettáblák" feladatai
valamint "3.fejezet. Allekérdezések a FROM utasításrészben" feladatai illetve
összetett feladatai és a "7.fejezet. Összetett SQL-feladatsorok" feladatai

2.4. Felső-N elemzés ROWNUM segítségével ("toplista" készítése)

Felső-N elemzés (például a ZH-n is hasznos, amikor túl nagy az eredménytábla
és csak az első 5 sorát kell beküldeni). a felső 5 sor kiíratása az alábbi módon:

  SELECT * FROM
(SELECT... FROM ...WHERE... ORDER BY <oszlop> DESC)
   WHERE ROWNUM <= 5

További részletek, lásd Oracle doksiban SQL Language Reference
2 Pseudocolumns fejezetben (44/1508) ROWNUM Pseudocolumn

ROWNUM - pszeudooszlop a kiválasztásra kerülő sorokhoz hozzárendeli
a kiválasztásuk sorszámát, de a WHERE feltételben nem állhat fent egyenlőség
(ez nem sorszám, hanem a lekérdezés eredményének a sorrendjét adja meg).
WHERE feltételben ROWNUM < N illetve ROWNUM <= N használható!
Hibás! WHERE feltételben ROWNUM > N illetve ROWNUM = N (hibás!)
(nem ad vissza sort)

Megj: Példatár 6.fejezet elméleti összefoglalóban szerepel: "felső-N analízis"

2.5. Hierarchikus lekérdezések az Oracle-ben (CONNECT BY PRIOR)
Családfák. SFW START WITH ... CONNECT BY PRIOR ...
--- Ez az SQL áttekintés teljessége miatt itt van, a 11.gyakorlaton visszatérünk erre.

Segédanyagok:
>> SQL Lang.Ref. >> 9 SQL Queries >> Hierarchical Queries
>> Oracle: Hierarchikus lekérd.pdf (jelszavas anyag) példáit próbáljuk ki az Oracle-ben!
-- ehhez a HR séma tábláihoz készítsük el a szinonimákat create_synonym
>> hiera_lekerd.txt (Nikovits Tibor)

Feladatok a hierarchikus lekérdezésekhez
- Táblák és feladatok: dolgozo_tabla.txt Ehhez a táblák létrehozása: create_dolgozo.txt
- Listázzuk ki az dolgozo tábla alapján a főnökökhöz tartozó beosztottak nevét és osztályukat.
   a.) A dolgozo tábla önamagára való hivatkozásával (többtáblás lekérdezés sorváltozókkal).
   b.) A CONNECT BY utasításrész használatával, a hierarchikus szerkezetet 'KING'-től
   felülről lefelé bejárva.
   c.) Alulról felfelé járjuk be a hierarchikus szerkezet egy ágát 'SMITH'-től kezdve.

Megj: Példatár 3.fejezet elméleti összefoglalóban "Hierarchikus adatszerkezet megjelenítése"

Fel a lap tetejére

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