Kihagyás

20. Adatbázisok tervezése és lekérdezése

Note: A tételt NotebookLM-el dolgoztam ki (Gemini 2.5 Pro) amely megkapta az Adatbázis előadás diáit. Szóval felelősséget nem vállalok. :)

Relációs adatmodell, egyed-kapcsolat modell és átalakítása relációs adatmodellbe

1. Relációs adatmodell

A relációs adatmodell az adatok strukturált tárolására szolgáló jelölés, amelyben az adatokat kétdimenziós táblákban, azaz relációkban tároljuk [1].

Alapvető fogalmak

  • Attribútumok: A reláció fejlécében található oszlopok [2]. Minden attribútumhoz tartozik egy értékkészlet [2].

  • Relációséma: A reláció neve és az attribútumok halmaza alkotja [3]. Az attribútumok sorrendje felcserélhető [3].

  • Sorok (tuples): Egy reláció sorok halmaza [4]. Egy elem (sor) csak egyszer szerepelhet, és a sorrend nem számít (halmazról van szó) [4, 5].

  • Előfordulás (relation instance): Egy reláció sorainak halmaza [5].

  • Mező (field): Egy sor elemei, amelyeknek atomi értéket kell felvenniük [6].

  • Adatbázis séma: Az adatbázist alkotó relációsémák halmaza [6].

  • Adatbázis előfordulás: A relációs előfordulások összessége [6].

  • Kulcs (key): Attribútumok olyan halmaza, amely egyedileg azonosít minden sort a relációban, azaz nincs két olyan sor, amelyek a kulcs összes attribútumának értékein megegyeznének [7]. A kulcs lehet több attribútumból is [8].

  • Hivatkozási épség megszorítás (referential integrity constraint): Biztosítja, hogy ha egy érték megjelenik egy környezetben, akkor egy másik, az előzővel összefüggő környezetben is meg kell jelennie [9]. Például: \(\Pi_{tea} (Felszolgál) \subseteq \Pi_{név} (Tea)\) [10].

Relációs algebra

Az algebra műveleteket (operátorokat) és atomi operandusokat (változókat, konstansokat) tartalmaz [11, 12]. Minden művelet eredménye egy reláció, amin további műveletek adhatók meg [11].

  • Projekció (vetítés, \(\Pi\)): Egy adott relációt vetít le a megadott attribútumokra [12].

  • Szelekció (kiválasztás, \(\sigma\)): Kiválasztja az argumentumként megadott reláció azon sorait, amelyek eleget tesznek egy feltételnek [12, 13].

  • Halmazműveletek (unió, metszet, különbség): Mivel a sorok halmazt alkotnak, alkalmazhatók. Feltétel, hogy az operandusok attribútumai megegyezzenek és azonos sorrendben szerepeljenek [13, 14].

  • Descartes-szorzat (\(\times\)): Két vagy több reláció összes lehetséges sorpárját kombinálja; az azonos nevű attribútumokat meg kell különböztetni [15].

  • Átnevezés (\(\rho\)): Egy reláció vagy annak attribútumainak átnevezésére szolgál [15, 16].

  • Théta-összekapcsolás (\(\bowtie_F\)): Egy szelekció a Descartes-szorzat eredményén, egy \(F\) feltétel alapján [14, 16]. Az egyen-összekapcsolás (equi join) speciális esete, amikor a feltétel csak egyenlőséget tartalmaz [17].

  • Természetes összekapcsolás (\(\bowtie\)): Két reláció azon sorpárjait tartalmazza, amelyek a közös attribútumokon megegyeznek. A közös attribútumok csak egyszer jelennek meg az eredményben [17]. Asszociatív és kommutatív [18]. Kifejezhető Descartes-szorzattal, szelekcióval és projekcióval [14, 18].

2. Egyed-kapcsolat (E/K) modell

Az E/K modell egy tervezési technika az adatbázisokhoz, amely vizuálisan ábrázolja az adatokat és azok kapcsolatait [19, 20].

Alapvető elemek az E/K diagramon

  • Egyedhalmazok (Entity Sets): Téglalapokkal jelöljük, és a valós világban létező, megkülönböztethető "dolgok" gyűjteményét reprezentálják (pl. Oktatók, Tárgyak) [21, 22].

  • Attribútumok: Ellipszisekkel jelöljük, és az egyedhalmazok tulajdonságait írják le (pl. név, életkor) [23]. A kulcsattribútumokat aláhúzással jelöljük [23].

  • Kapcsolatok (Relationships): Rombuszokkal jelöljük, és az egyedhalmazok közötti összefüggéseket fejezik ki (pl. "Oktat") [22].

  • Kapcsolatok attribútumai: Olyan attribútumok, amelyek a kapcsolat egy tulajdonságára vonatkoznak, nem pedig az egyes egyedekre (pl. ár egy "Felszolgál" kapcsolatban) [24, 25].

  • Szerepek (Roles): Ha egy egyedhalmaz többször is megjelenik egy kapcsolatban, az éleket címkézzük, és ezeket a címkéket nevezzük szerepeknek [26].

Kapcsolatok típusai (számosság/multiplicitás)

  • Sok-sok (many-to-many): Mindkét résztvevő egyedhalmaz egyedei több másik egyedhez kapcsolódhatnak [19]. Nincs nyíl a diagramon.

  • Sok-egy (many-one): Az első egyedhalmaz minden egyede legfeljebb egy egyedhez kapcsolódhat a második halmazból, míg a második halmaz egyedei nullához, egyhez vagy többhöz kapcsolódhatnak az elsőből [19, 27]. Egy nyíl jelzi az "egy" oldalt [28].

  • Egy-egy (one-one): Minden egyes entitás legfeljebb egyetlen másik entitáshoz kapcsolódhat [27]. Mindkét végén nyíl van [28].

  • Lekerekített nyíl: Pontosan egyet jelent, azaz minden első halmazbeli entitásnak pontosan egy párja van a másodikból [28].

Speciális egyedhalmazok és kapcsolatok

  • Alosztályok (subclasses): Az "az-egy" kapcsolat (specializáció) jelzi, ahol az alosztály egy speciális esete az ősosztálynak, kevesebb egyedet tartalmaz, de több tulajdonsággal rendelkezhet [29, 30]. Az ősosztály kulcsa a hierarchia minden alosztályának is kulcsa lesz [23].

  • Gyenge egyedhalmazok (weak entity sets): Azon egyedhalmazok, amelyek egyedeit csak egy vagy több, tőlük induló sok-egy támogató kapcsolaton keresztül, a kapcsolódó egyedek kulcsértékeinek segítségével lehet egyértelműen azonosítani [31]. Dupla téglalap jelzi a gyenge egyedhalmazt, dupla rombusz a támogató kapcsolatot [22]. A gyenge egyedhalmaz kulcsa a saját aláhúzott attribútumait és a támogató egyedhalmaz(ok) aláhúzott attribútumait is tartalmazza [32].

3. E/K modell átalakítása relációs adatmodellbe

Az E/K diagramok alapján könnyen létrehozhatók a relációs sémák, figyelembe véve bizonyos tervezési elveket.

Tervezési elvek (design techniques)

  • Redundancia elkerülése: Ugyanazt az információt ne tároljuk többször, mert helypazarló és növeli az inkonzisztencia kockázatát [20, 33].

  • Gyenge egyedhalmazok óvatos használata: Csak akkor használjuk, ha feltétlenül szükséges, azaz nincs önállóan értelmes globális kulcs [34, 35].

  • Egyedhalmaz vs. attribútum: Akkor használjunk egyedhalmazt, ha a modellezni kívánt dolognak több, számunkra fontos tulajdonsága van, vagy ha egy sok-egy kapcsolat "sok" oldalán szerepel. Ellenkező esetben elegendő egy attribútum [36, 37]. Semmi esetre se tegyük mindkettőt egyszerre [38].

Átalakítási szabályok (E/K diagramból relációs sémává)

  • Egyedhalmaz: Minden egyedhalmazhoz egy reláció (tábla) tartozik. A reláció neve megegyezik az egyedhalmaz nevével, attribútumai pedig az egyedhalmaz attribútumai [35, 38].

  • Kapcsolat: Minden kapcsolathoz egy reláció tartozik. A reláció neve a kapcsolat neve, attribútumai pedig a kapcsolatban részt vevő egyedhalmazok kulcsattribútumai és a kapcsolat saját attribútumai [38, 39]. Az azonos nevű kulcsattribútumokat át kell nevezni [38].

  • Relációk összevonása:

    • Az egyedhalmazokból kapott reláció összevonható azokkal a sok-egy kapcsolatokkal, ahol az egyedhalmaz a "sok" oldalon szerepel. Például: Vendégek(név, cím) és Kedvenc(vendég, tea) összevonható Vendégek1(név, cím, kedvencTea) relációvá [39, 40].

    • A sok-sok kapcsolatok nem vonhatók össze, mert redundanciához vezetne [40].

    • Gyenge egyedhalmazok: A gyenge egyedhalmazból kapott relációnak tartalmaznia kell a teljes kulcsot (beleértve a támogató egyedhalmaz kulcsattribútumait is) és a saját attribútumait [40, 41]. A támogató kapcsolatot külön relációként nem írjuk át [42].

  • Alosztályok (hierarchiák): Három fő megközelítés van [42]:

    • Objektumorientált stílus: Külön reláció az ősosztálynak (közös attribútumokkal) és minden alosztálynak (csak az alosztályra jellemző attribútumokkal és az ősosztály kulcsával) [43].

    • E/K stílus: Külön reláció az ősosztálynak és minden alosztálynak. Az alosztály reláció tartalmazza a specifikus attribútumokat és az ősosztály kulcsát [43].

    • Null értékek használata: Egyetlen relációba szervezzük az ősosztály és összes alosztály attribútumait, és NULL értékekkel jelöljük azokat, amelyek az adott sorra nem értelmezhetők. Ez helytakarékos lehet, ha nem túl sok a NULL érték, és elkerülhetők az összekapcsolások [43].

2. Relációs algebra, SQL. Az SQL procedurális kiterjesztése (PL/SQL vagy PSM)

2.1. Relációs algebra

A relációs algebra egy absztrakt kezelő nyelv, amely műveleteket és atomi operandusokat tartalmaz, és az adatbázis-kezelő rendszerek (DBMS) automatikus és hatékony lekérdezés-optimalizálásának elméleti alapjául szolgál [1-3]. A relációs algebrai műveletek eredménye mindig egy reláció, amelyen további műveletek végezhetők [1]. Atomi operandusai a relációkhoz tartozó változók és konstansok [1]. Alapműveletek:

  • Halmazműveletek: Reláció előfordulások véges sok sorból álló halmazokként kezelhetők [4].

  • Unió (∪): Két azonos típusú reláció sorait egyesíti, az ismétlődéseket kiszűrve [4].

  • Különbség (-): Az első reláció azon sorait tartalmazza, amelyek nem szerepelnek a másodikban [4].

  • Metszet (∩): Azon sorokat adja vissza, amelyek mindkét relációban szerepelnek (származtatott művelet: R ∩ S = R - (R - S)) [4].

  • Vetítés (Projekció, Π): Adott relációt vetít le a megadott attribútumokra, csökkentve az attribútumok számát. Az eredményből a duplikátumok kiszűrődnek [4, 5].

  • Kiválasztás (Szűrés, σ): Kiválasztja az argumentumban szereplő reláció azon sorait, amelyek eleget tesznek az alsó indexben megadott feltételnek. A feltétel lehet elemi (pl. összehasonlítás) vagy összetett (logikai műveletekkel) [5].

  • Természetes összekapcsolás (Natural Join, ⋈): A szorzás jellegű műveletek közül alapművelet. Két relációt kapcsol össze a közös attribútumneveik alapján. Az eredmény azon sorpárokat tartalmazza, amelyek a közös attribútumokon megegyeznek [6].

  • Átnevezés (ρ): Lehetővé teszi relációk vagy attribútumok átnevezését [6]. Ezt használjuk például, ha egy relációt önmagával szeretnénk összekapcsolni, de el kell különíteni az attribútumneveket [6].

Kiegészítő fogalmak és műveletek:

  • Direkt-szorzat (Descartes-szorzat, ×): Az R és S minden sorát párban összefűzi, az első tábla minden sorához hozzáfűzi a második tábla minden sorát. Az azonos nevű attribútumokat meg kell különböztetni [7].

  • Monotonitás: A legtöbb relációs algebrai művelet monoton, azaz egy bővebb bemeneti reláció bővebb eredményt ad. A kivonás kivétel, nem monoton [8].

  • Osztás (÷): A maradékos osztás mintájára működik, egy reláció A attribútumainak azon értékeit adja vissza, amelyekhez a B attribútumokban minden S-beli sorban szereplő érték létezik [8].

  • Multihalmaz szemantika: Az SQL-ben alapértelmezetten sorok ismétlődhetnek, ellentétben a relációs algebra halmaz szemantikájával [9, 10]. Azonban az unió, metszet, különbség műveleteknél az SQL alapértelmezetten kiszűri az ismétlődéseket, kivéve ha az ALL kulcsszót használjuk [9-11].

  • Ismétlődések kiküszöbölése (δ): Átalakítja a multihalmazt halmazzá (SQL-ben SELECT DISTINCT) [12].

  • Összesítő műveletek és csoportosítás (γ): GROUP BY záradékhoz kapcsolódó aggregáló függvények (SUM, COUNT, MIN, MAX, AVG) [12].

  • Vetítési művelet kiterjesztése: Lehetővé teszi kifejezések használatát és oszlopátnevezést (AS) a SELECT záradékban [13, 14].

  • Rendezési művelet (τ): ORDER BY záradéknak felel meg, rendezi a sorokat. Az eredménye nem halmaz, sem multihalmaz [14].

  • Külső összekapcsolások: LEFT, RIGHT, FULL OUTER JOIN megőrzi a "lógó sorokat" (dangling tuples) NULL értékekkel kiegészítve a hiányzó részeket [10, 14-16].

2.2. SQL

Az SQL (Structured Query Language) egy szabványos adatbázis-kezelő nyelv, amely négy fő komponensből áll:

  • Adatleíró nyelv (DDL - Data Definition Language): Adatbázis objektumok (pl. táblák, nézetek) létrehozására, módosítására, törlésére szolgál (CREATE, ALTER, DROP) [10, 17].

  • Táblák létrehozása:CREATE TABLE <név> (<elemek listája>). Attribútumok típussal (pl. INT, REAL, CHAR(n), VARCHAR(n), DATE, TIME), és NULL értékek engedélyezettek [18, 19].

  • Kulcsok deklarálása: PRIMARY KEY (egy táblában csak egy, nem lehet NULL értékű, egyedi értékeket követel meg) vagy UNIQUE (több is lehet, megengedheti a NULL értékeket)

  • Idegen kulcsok (FOREIGN KEY): Egy reláció attribútumainak értékeinek egy másik relációban is meg kell jelenniük. Deklarálása REFERENCES kulcsszóval történik [23, 24]. Kezelési stratégiák a hivatkozott kulcs törlése/módosítása esetén: ON DELETE SET NULL, ON DELETE CASCADE, ON UPDATE SET NULL, ON UPDATE CASCADE.

  • Megszorítások: Az adatbázis rendszernek fenntartania kell az adatelemek közötti kapcsolatot. Lehetnek attribútum alapú (CHECK(<feltétel>)), sor alapú (CHECK(<feltétel>) a séma elemeként), vagy globális (CREATE ASSERTION <név> CHECK(<feltétel>)) megszorítások.

Adatkezelő nyelv (DML - Data Manipulation Language): Adatok beillesztésére, törlésére, módosítására és lekérdezésére szolgál

SELECT záradékok (lekérdezések): Az SQL elsődlegesen lekérdező nyelv, fő utasítása a SELECT

  • SELECT: Az eredményül kapott sor attribútumait adja meg. A * az összes attribútumot jelöli [32, 33]. Lehetőséget ad attribútumok átnevezésére (AS) és kifejezések használatára. ű

  • FROM: Egy vagy több táblát ad meg, amelyekből az adatokat lekérdezzük. Több tábla esetén a Descartes-szorzatukon értelmezi a lekérdezést, majd szűri.

  • WHERE: Feltételeket ad meg a táblák soraira. Használhatóak logikai (AND, OR, NOT) és összehasonlító operátorok [37]. Sztringekre LIKE operátorral illeszthetünk mintákat (% akármennyi karakter, _ tetszőleges egy karakter) [38]. A NULL értékek összehasonlítása UNKNOWN eredményt ad, és csak a TRUE értékű sorok kerülnek az eredménybe.

  • Több relációt tartalmazó lekérdezések: A FROM záradékban felsorolt relációk attribútumait reláció.attribútum formában kell hivatkozni, ha az attribútum neve több relációban is előfordul.

  • Alkérdések (Subqueries): Zárójelezett SELECT-FROM-WHERE utasítások, amelyek szerepelhetnek a FROM záradékban (ideiglenes táblaként, ekkor gyakran explicit sorváltozó nevet igényelnek) [42, 43] vagy a WHERE záradékban [43]. Egy sort visszaadó alkérdések: Konstans értékhez hasonlóan használhatók. Hiba, ha több vagy nulla sort ad vissza [43]. IN művelet: IN () igaz, ha a sor eleme az alkérdés eredményének.

  • EXISTS művelet: EXISTS() igaz, ha az alkérdés eredménye nem üres.

  • ANY és ALL műveletek: Összehasonlítást végeznek az alkérdés eredményének valamelyik (ANY) vagy összes (ALL) sorával.

  • Halmazműveletek: UNION, INTERSECT, MINUS (vagy EXCEPT) két lekérdezés eredményének egyesítésére, metszetére vagy különbségére. Alapértelmezés szerint halmaz szemantikát használnak, azaz kiszűrik az ismétlődéseket. Az ALL kulcsszóval megőrizhetők az ismétlődések.

  • Aggregációk (Összesítések): SUM, AVG, COUNT, MIN, MAX függvények egy-egy oszlopra. COUNT(*) az eredmény sorainak számát adja meg. A NULL értékek nem számítanak az összesítésben, kivéve COUNT(*) esetében.

  • Csoportosítás (GROUP BY): Az eredményt a megadott attribútumok értékei szerint csoportosítja, és az összesítéseket minden csoportra külön alkalmazza.

  • Csoportok szűrése (HAVING): A GROUP BY záradékot követi, és a feltétel az egyes csoportokra vonatkozik. Csak azok a csoportok kerülnek az eredménybe, amelyek teljesítik a feltételt.

  • Összekapcsolás (JOIN): R NATURAL JOIN S,R CROSS JOIN S, R JOIN S ON <feltétel>.

  • Külső összekapcsolások (OUTER JOIN): LEFT, RIGHT, FULL OUTER JOIN, melyek megőrzik a nem illeszkedő (lógó) sorokat NULL értékekkel.

  • Beszúrás (INSERT): Sorok beszúrására szolgál. Lehet egyetlen sor beszúrása (INSERT INTO <reláció> VALUES (...)), vagy egy lekérdezés eredményének beszúrása (INSERT INTO <reláció> (<alkérdés>)).

  • Törlés (DELETE): Sorok törlésére szolgál egy megadott feltétel alapján (DELETE FROM <reláció> WHERE <feltétel>). Ha nincs feltétel, az összes sort törli [58].

  • Módosítás (UPDATE): Létező sorok értékeinek módosítására szolgál (UPDATE <reláció> SET <attribútum értékadások listája> WHERE <sorokra vonatkozó feltétel>).

Adatvezérlő nyelv (DCL - Data Control Language): Jogosultságok kezelése (GRANT, REVOKE) [60]. Meghatározza, ki és milyen műveletet végezhet el adatbázis objektumokon.

Tranzakció-kezelés: A tranzakciók adatbázis lekérdezéseket és módosításokat tartalmazó "értelmes egészek".

  • ACID tulajdonságok: * Atomiság (Atomicity): "Minden vagy semmi" – vagy az összes utasítás végrehajtódik, vagy egyik sem.

  • Konzisztencia (Consistency): "Helyesnek tűnik" – az adatbázis megszorítások megőrződnek [63, 64].

  • Elkülönítés (Isolation): "Mintha egyedül lenne" – a felhasználók számára úgy tűnik, mintha a folyamatok elkülönítve, egymás után futnának le.

  • Tartósság (Durability): "Túléli a hibákat" – egy befejeződött tranzakció módosításai nem vesznek el [63, 64].

  • COMMIT: Véglegesíti a tranzakció módosításait..

-ROLLBACK: Visszagörgeti a tranzakciót, törölve minden módosítást.

Elkülönítési szintek: Az SQL négy elkülönítési szintet definiál, amelyek szabályozzák az egyidejű tranzakciók interakcióit

  • READ UNCOMMITTED: Láthatók a még nem véglegesített (piszkos) adatok [66, 70].

  • READ COMMITTED: Csak véglegesített adatokat láthat, de nem feltétlenül mindig ugyanazt az adatot (nem ismételhető olvasás lehetséges).

  • REPEATABLE READ: Ugyanazt az adatot, amit először beolvasott, másodszor is látni fogja, de új sorok (fantomadatok) megjelenhetnek.

  • SERIALIZABLE: Teljesíti az ACID tulajdonságokat. Az adatbázis konzisztens állapotát látja, mintha a tranzakciók egymás után futnának.

2.3. Az SQL procedurális kiterjesztése (PL/SQL vagy PSM)

Amikor SQL utasításokat egy alkalmazás részeként, programban használunk, felmerülhetnek problémák az osztott változók használatával (az SQL és a gazdanyelv között) és a típuseltéréssel (a relációs adatmodell táblái gyűjtemények, ami nem fordul elő a magasszintű nyelvekben) [75, 76]. Több soros lekérdezés eredményének kezelésére kurzorra van szükség .

Három fő megközelítés létezik az SQL és a procedurális programozás integrálására:

  1. SQL kiterjesztése procedurális eszközökkel: Az adatbázisséma részeként tárolt kódblokkok (pl. Oracle PL/SQL, vagy szabványos PSM - Persistent Stored Modules).

  2. Beágyazott SQL (Embedded SQL): SQL utasítások beágyazása egy gazdanyelvbe (pl. C), amit egy előfordító alakít át [77].

  3. Hívásszintű felület (Call-level interface): Függvénykönyvtárak használata a gazdanyelvből az adatbázishoz való hozzáféréshez (pl. JDBC, PHP/DB).

PL/SQL (Procedural Language/SQL) A PL/SQL az Oracle adatbázis-kezelő rendszerek procedurális kiterjesztése, az Ada programozási nyelv alapján fejlesztették ki. Lehetővé teszi SQL utasítások és konvencionális programozási elemek (pl. IF, WHILE ciklusok) keverékét [78].

PL/SQL szerkezet:Egy PL/SQL blokk a következő részekből áll:

[DECLARE
    -- Változók, konstansok, kurzorok, kivételkezelők deklarálása
]
BEGIN
    -- Procedurális és SQL utasítások
[EXCEPTION
    -- Kivételkezelők
]
END;
/

  • A DECLARE rész opcionális, és a deklarációkat egyszer a rész elején kell megadni, nem minden változó előtt.

  • Értékadás a := operátorral történik [79].

  • Paraméterek név-mód-típus sorrendben adhatók meg, a módok: IN, OUT, IN OUT [78, 79]. Függvények csak IN paramétereket fogadhatnak el [78].

  • Adattípusoknál hivatkozhatunk tábla oszlopainak típusára (R.x%TYPE) vagy egy egész sor típusára (R%ROWTYPE).

  • Feltételes vezérlési szerkezetek: IF ... THEN ... [ELSIF ... THEN ...] [ELSE ... END IF;].

  • Ciklusok: LOOP ... END LOOP;, WHILE ... LOOP ... END LOOP;, FOR ... IN ... LOOP ... END LOOP;.

    • Kilépés a ciklusból: EXIT - WHEN <feltétel>;.

  • Kivételek kezelése: EXCEPTION WHEN <kivétel> THEN <utasítás>;.

  • Kurzorok: Több soros lekérdezés eredményének bejárására szolgálnak. Deklarálása:CURSOR <név> IS <lekérdezés>;.

    • Használata: OPEN <kurzor_név>;, FETCH <kurzor_név> INTO <változó(k)>;, CLOSE <kurzor_név>;.

    • Kilépés a kurzorciklusból:EXIT WHEN <kurzor_név>%NOTFOUND;.

PL/SQL Kódpéldák

Az alábbi példákhoz feltételezzük, hogy az Felszolgál, Látogat és Szeret táblák a forrásokban leírt sémával rendelkeznek, és Oracle SQL*Plus környezetben futtatva a kimenet megjelenítéséhez a SET SERVEROUTPUT ON; parancs szükséges.

1. Változók és SELECT...INTO használata: Ez a példa kiszámítja egy adott vendég által látogatott teázókban felszolgált Brisk teák átlagárát.

DECLARE
    v_vendeg_neve VARCHAR2(50) := 'Fred';  -- Változó deklaráció és értékadás
    v_brisk_atlag_ar NUMBER;
BEGIN
    -- Egyetlen soros lekérdezés eredményének változóba töltése
    SELECT AVG(Felszolgál.ár)
    INTO v_brisk_atlag_ar  -- Az eredményt ebbe a változóba töltjük
    FROM Látogat, Felszolgál
    WHERE Látogat.teázó = Felszolgál.teázó
      AND Látogat.vendég = v_vendeg_neve
      AND Felszolgál.tea = 'Brisk';

    -- Kiíratás
    IF v_brisk_atlag_ar IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('A(z) ' || v_vendeg_neve || ' által látogatott teázókban a Brisk átlagára: ' || v_brisk_atlag_ar || ' dollár.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Nincs adat a Brisk teáról ' || v_vendeg_neve || ' számára.');
    END IF;
END;
/

2. Kurzor használata több soros eredmény bejárására: Ez a példa kilistázza Fred összes kedvelt teáját a Szeret táblából.

DECLARE
    -- Kurzor deklarálása a Fred által kedvelt teák lekérdezéséhez
    CURSOR c_fred_kedvelt_teak IS
        SELECT tea
        FROM Szeret
        WHERE vendég = 'Fred';

    v_tea_nev Teák.név%TYPE; -- Változó deklarálása a tea nevének tárolására, a Teák.név típusával
BEGIN
    DBMS_OUTPUT.PUT_LINE('Fred kedvelt teái:');

    OPEN c_fred_kedvelt_teak; -- Megnyitja a kurzort, végrehajtja a lekérdezést

    LOOP
        FETCH c_fred_kedvelt_teak INTO v_tea_nev; -- Egy sort kiolvas a kurzorból a változóba
        EXIT WHEN c_fred_kedvelt_teak%NOTFOUND; -- Kilép a ciklusból, ha nincs több sor

        DBMS_OUTPUT.PUT_LINE('- ' || v_tea_nev); -- Kiírja a tea nevét
    END LOOP;

    CLOSE c_fred_kedvelt_teak; -- Bezárja a kurzort
END;
/

3. Feltételes logika (IF-ELSIF-ELSE): Ez a példa egy tea árát értékeli ki és ír ki üzenetet.

DECLARE
    v_ar NUMBER := 3.75;
    v_uzenet VARCHAR2(100);
BEGIN
    IF v_ar <= 2.00 THEN
        v_uzenet := 'Nagyon olcsó tea.';
    ELSIF v_ar > 2.00 AND v_ar <= 4.00 THEN
        v_uzenet := 'Átlagos árú tea.';
    ELSE
        v_uzenet := 'Drága tea.';
    END IF;

    DBMS_OUTPUT.PUT_LINE('A tea ára ' || v_ar || ' dollár. ' || v_uzenet);
END;
/

**4. Ciklus (WHILE LOOP):**Ez a példa egy egyszerű ciklust mutat be.
DECLARE
    i NUMBER := 1;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Ciklus futása:');
    WHILE i <= 3 LOOP
        DBMS_OUTPUT.PUT_LINE('  - Iteráció: ' || i);
        i := i + 1; -- Növeli a ciklusváltozót
    END LOOP;
END;
/

5. Eljárás létrehozása és hívása:: Ez a példa egy eljárást hoz létre, amely a fenti SELECT...INTO logikát foglalja magába, majd meghívja azt.

CREATE OR REPLACE PROCEDURE GetAvgBriskPriceForGuest (
    p_vendeg_neve IN VARCHAR2
)
IS
    v_brisk_atlag_ar NUMBER;
BEGIN
    SELECT AVG(Felszolgál.ár)
    INTO v_brisk_atlag_ar
    FROM Látogat, Felszolgál
    WHERE Látogat.teázó = Felszolgál.teázó
      AND Látogat.vendég = p_vendeg_neve
      AND Felszolgál.tea = 'Brisk';

    IF v_brisk_atlag_ar IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('A(z) ' || p_vendeg_neve || ' által látogatott teázókban a Brisk átlagára: ' || v_brisk_atlag_ar || ' dollár.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Nincs adat a Brisk teáról ' || p_vendeg_neve || ' számára.');
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Nincs adat a Brisk teáról ' || p_vendeg_neve || ' számára (NO_DATA_FOUND).');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Hiba történt: ' || SQLERRM);
END;
/

-- Eljárás hívása
BEGIN
    GetAvgBriskPriceForGuest('Fred');
    GetAvgBriskPriceForGuest('Sally');
    GetAvgBriskPriceForGuest('NemLétezőVendég'); -- Példa nem létező vendégre
END;
/

Példa egy függvényre:

CREATE OR REPLACE FUNCTION calculate_square (
    p_number IN NUMBER
)
RETURN NUMBER
IS
    v_result NUMBER;
BEGIN
    v_result := p_number * p_number;
    RETURN v_result;
END;
/

Ezek a példák illusztrálják a PL/SQL alapvető procedurális képességeit, amelyekkel összetett adatbázis-logika valósítható meg.

Adatbázis-sémák Tervezése, Normálformák és Dekompozíciók

Relációs adatbázis-sémák tervezése, normálformák, dekompozíciók

A relációs adatbázis-sémák tervezésének célja, hogy elkerüljük a redundanciát és az anomáliákat [1-4]. Egyetlen nagy táblába rendezni minden adatot felhasználói szempontból kényelmes lehet, de sok felesleges adatot tartalmazhat, ami rossz tárolási hatékonyságot és az adatbázis ellentmondásosságát eredményezheti [1].

Funkcionális Függőségek (FF)

A tervezés alapvető fogalma a funkcionális függőség (FF) [1, 5, 6].

  • Definíció: Egy \(X \rightarrow Y\) funkcionális függőség egy \(R\) relációra azt a megszorítást jelenti, hogy ha két sor megegyezik \(X\) összes attribútumán, akkor \(Y\) attribútumain is meg kell egyezniük [5-7]. Másképp fogalmazva, az \(X\) attribútumok értékei egyértelműen meghatározzák az \(Y\) attribútumok értékeit [7].

  • Jelölés: \(X, Y, Z\) attribútumhalmazokat, míg \(A, B, C\) attribútumokat jelöl. Az \({A, B, C}\) attribútumhalmaz helyett gyakran \(ABC\)-t írunk [5, 7].

  • Példa: A Főnökök(név, cím, kedveltTeák, gyártó, kedvencTea) relációban feltételezhetően teljesülnek az alábbi FF-ek [8-10]:

    • név -> cím kedvencTea [8, 9] (ami ekvivalens név -> cím és név -> kedvencTea függőségekkel [8, 10]).

    • kedveltTeák -> gyártó [9, 10].

  • Jobboldalak szétvágása: Az \(X \rightarrow A_1A_2\dots A_n\) függőség akkor és csak akkor teljesül egy \(R\) relációra, ha \(X \rightarrow A_1, X \rightarrow A_2, \dots, X \rightarrow A_n\) is teljesülnek \(R\)-en [7, 8, 10]. Baloldalak szétvágására nincs általános szabály [8, 10].

Kulcsok és Szuperkulcsok

A funkcionális függőségek segítségével definiálhatók a kulcsok [9, 10].

  • Szuperkulcs: Egy \(K\) attribútumhalmaz szuperkulcs az \(R\) relációra, ha \(K\) funkcionálisan meghatározza \(R\) összes attribútumát (\(K \rightarrow U\) teljesül) [9, 10].

  • Kulcs: Egy \(K\) attribútumhalmaz kulcs \(R\)-en, ha \(K\) szuperkulcs, de egyetlen valódi részhalmaza sem szuperkulcs (azaz minimális szuperkulcs) [9, 11, 12].

  • Példa: A Főnökök relációban {név, kedveltTeák} egy szuperkulcs, mivel meghatározza a maradék attribútumokat (név -> cím kedvencTea és kedveltTeák -> gyártó) [11]. Ez a halmaz egyben kulcs is, mert sem {név}, sem {kedveltTeák} nem szuperkulcs [11].

Funkcionális Függőségek Kikövetkeztetése (Armstrong-axiómák)

Adott FF-ek halmazából következtethetünk-e más FF-ekre? Ennek ellenőrzésére szolgálnak az Armstrong-axiómák [13-15]:

  • (A1) Reflexivitás (Reflexivity): Ha \(Y \subseteq X\), akkor \(X \rightarrow Y\). Ezeket triviális függőségeknek nevezzük [13, 15]. Példa: (név, születés) -> név [14].

  • (A2) Bővítés (Augmentation): Ha \(X \rightarrow Y\) teljesül, akkor tetszőleges \(Z\) attribútumhalmazra \(XZ \\rightarrow YZ\) is teljesül [13, 15]. Példa: ha születés -> kor, akkor (születés, név) -> (kor, név) [14].

  • (A3) Tranzitivitás (Transitivity): Ha \(X \rightarrow Y\) és \(Y \rightarrow Z\), akkor \(X \rightarrow Z\) [14, 15]. Példa: ha TAJ -> születés és születés -> kor, akkor TAJ -> kor [14]. Az Armstrong-axiómarendszer helyes és teljes, ami azt jelenti, hogy minden levezethető függőség implikálódik, és minden implikált függőség levezethető az axiómákból [15, 16].

Attribútumhalmaz Lezárása (\(Y^+\))

Az attribútumhalmaz lezárása (closure) egy fontos fogalom az FF-ek kikövetkeztetésében [17, 18].

  • Definíció: Adott \(R\) reláció és \(F\) FF halmaz mellett az \(Y\) lezárja (\(Y^+\)) az összes olyan \(A\) attribútum halmaza, amire \(Y \rightarrow A\) következik \(F\)-ből [17, 18].

  • Algoritmus (vázlat):

    1. Kiindulás: \(Y^+ = Y\) [17].

    2. Ismétlés: Keresünk olyan FF-eket (\(X \rightarrow A\)), melyek baloldala (\(X\)) már benne van \(Y^+\)-ban. Ha ilyet találunk, hozzáadjuk \(A\)-t \(Y^+\)-hoz [17].

    3. Ha \(Y^+\)-hoz már nem lehet további attribútumot adni, akkor az algoritmus véget ér [19].

Ez az algoritmus helyes, azaz pontosan \(Y^+\)-t számítja ki [19-22]. Az FF-ek implikációs problémájának eldöntéséhez elegendő az attribútumhalmaz lezártját hatékonyan kiszámolni [18].

Egyszerűsítés: Formálisan, adott egy \(R\) relációs séma, egy \(F\) funkcionális függőség halmaz és egy \(Y \subseteq R\) attribútumhalmaz. Az \(Y\) attribútumhalmaz lezártja (\(Y^+\)) az \(F\) függőségekre nézve a következőképpen definiálható:

\[ Y^+ = \\{ A \mid Y \to A \text{ levezethető } F\text{-ből} \\} \]

Normálformák

A normálformák segítenek a redundancia csökkentésében és az anomáliák kiküszöbölésében [4, 23]. Három fő anomália típust különböztetünk meg [4, 24]:

  • Módosítási anomália: Egy adat egy előfordulását megváltoztatjuk, de más előfordulásait nem, ami inkonzisztenciához vezet [4].

  • Törlési anomália: Adat törlésekor olyan információt is elveszítünk, amit nem szeretnénk [4].

  • Beillesztési anomália: Adatok felvitelekor nehézségekbe ütközünk, ha bizonyos más adatok még nem állnak rendelkezésre (pl. egy gyártót nem tudunk felvinni, ha még nincs olyan sör, amit gyártana) [24].

Boyce-Codd Normálforma (BCNF)

A BCNF a legszigorúbb a funkcionális függőségeken alapuló normálformák közül [25].

  • Definíció: Egy \(R\) reláció BCNF-ben van, ha minden \(X \rightarrow Y\) nem-triviális FF-re \(R\)-ben \(X\) szuperkulcs [25]. (Nem-triviális FF: \(Y\) nem részhalmaza \(X\)-nek [25]).

  • Probléma: Bizonyos FF halmazok esetén a BCNF-re bontáskor elveszíthetünk függőségeket [23, 26, 27].

    • Példa: Legyenek adottak az AB -> C és C -> B FF-ek. Ha ezt a sémát BCNF-re bontjuk (pl. AC és BC sémákra), az eredeti AB -> C függőség nem lesz kikényszeríthető a felbontott relációkban [23, 28]. (A mozi -> város megsérti a BCNF-et, mivel a mozi nem szuperkulcs [23]).

Harmadik Normálforma (3NF)

A 3NF enyhébb feltételt szab, mint a BCNF, éppen a függőségek megőrzésének problémájára reflektálva [28].

  • Prím attribútum: Egy attribútum prím, vagyis elsődleges attribútum, ha legalább egy kulcsnak eleme [29, 30].

  • Definíció: Egy \(X \rightarrow A\) nem-triviális FF akkor és csak akkor sérti a 3NF-et, ha \(X\) nem szuperkulcs ÉS \(A\) nem prím [29, 30].

  • Különbség a BCNF-hez: A 3NF feltételében szerepel a "vagy jobb oldala csak elsődleges attribútumokat tartalmaz" kiegészítés a BCNF-hez képest [29].

  • Példa: A problémás AB -> C és C -> B esetben a kulcsok {A, B} és {A, C}. Emiatt A, B és C mindegyike prím attribútum [26, 29]. Habár C -> B megsérti a BCNF-et (mert \(C\) nem szuperkulcs), nem sérti a 3NF feltételét, mivel \(B\) prím attribútum [26]. Így ebben az esetben nem kell dekomponálni 3NF szerint [28].

3NF és BCNF összehasonlítása

A dekompozícióknak három fontos tulajdonsága lehet [24, 26]:

  1. Veszteségmentes összekapcsolás (lossless join): Az információ visszaállítható a felbontott táblák összekapcsolásával [24, 26].

  2. Függőségek megőrzése (dependency preservation): Az eredeti sémára kirótt összes függőség levezethető a vetületi függőségekből [26, 31, 32].

  3. Anomáliák kiküszöbölése (elimination of anomalies) [4, 26].

  4. A BCNF mindig teljesíti a veszteségmentes összekapcsolás és az anomáliák kiküszöbölése tulajdonságot (az FF-ek okozta anomália nem lehet benne) [26, 27, 33]. Azonban a függőségek megőrzése sérülhet [27].

  5. A 3NF mindig teljesíti a veszteségmentes összekapcsolás és a függőségek megőrzése tulajdonságokat [26, 33, 34]. Maradhat benne anomália, de ez általában nem okoz komoly problémát [27].

Negyedik Normálforma (4NF)

A 4NF a többértékű függőségeket (TÉF) kezeli [30, 35].

  • Definíció: Egy \(R\) reláció 4NF-ben van, ha minden \(X \rightarrow \rightarrow Y\) nem-triviális többértékű függőség esetén \(X\) szuperkulcs [36].

  • A TÉF-ek okozta redundanciát a BCNF nem szünteti meg, ehhez szükséges a 4NF [36]. Ha egy reláció 4NF-ben van, akkor BCNF-ben is van [33].

Dekompozíciók

A dekompozíció (felbontás) az a folyamat, amikor egy relációsémát több sémára bontunk szét, általában projekcióval (vetítéssel) [24]. A dekompozíció céljai megegyeznek a normálformák tulajdonságaival [24, 31]:

  1. Veszteségmentesség: A felbontott relációk összekapcsolásával visszanyerhető az eredeti információ [24, 31]. Ezt a CHASE algoritmussal lehet ellenőrizni [31, 34].

  2. Függőségőrzés: Az eredeti reláció FF-jei levezethetők a felbontott relációk FF-jeiből [31, 32].

  3. Jó tulajdonságú vetületek: A felbontás eredménye a kívánt normálformában van (pl. 3NF, BCNF) [31].

3NF-re bontás algoritmusa (Minimalizált bázis alapján)

Egy általánosan alkalmazott módszer a 3NF-re való bontáshoz a minimális bázis használata [27, 37]:

  1. Jobboldalak szétvágása: Az összes \(X \rightarrow Y\) függőséget \(X \rightarrow A\) alakú függőségekre bontjuk, ahol \(A\) egyetlen attribútum [27, 34].

  2. Redundáns FF-ek törlése: Próbáljuk törölni az FF-eket egymás után. Ha a megmaradó FF-halmaz nem ekvivalens az eredetivel, akkor az épp aktuális FF nem törölhető [27, 38].

  3. Baloldalak minimalizálása: Próbáljuk csökkenteni a baloldalakat attribútumok elhagyásával, és megnézzük, hogy az eredetivel ekvivalens FF-halmazt kapunk-e [27, 38].

A minimalizált bázis előállítása után [34, 38]:

  • A minimális bázis minden \(X \rightarrow A\) FF-re létrehoz egy sémát: \(XA\) (azaz a bal- és jobboldal uniója) [37].

  • Kulcs séma hozzáadása: Ha a minimális bázisból származó sémák között nincs olyan, ami maga is kulcs lenne az eredeti relációra, akkor hozzáadunk a felbontáshoz egy olyan sémát, amely maga egy kulcs az \(R\) relációra [37]. Ez biztosítja a veszteségmentes összekapcsolást [34].

Példa 3NF felbontásra

  • Adott reláció: \(R = ABCD\).

  • Adott FF-ek: \(A \rightarrow B\) és \(A \rightarrow C\).

  • Minimalizált bázis (ebben az esetben megegyezik az adottakkal): \({A \rightarrow B, A \rightarrow C}\).

  • Sémák létrehozása az FF-ekből: \(AB\) és \(AC\) [37].

  • Mivel sem \(AB\), sem \(AC\) nem kulcs \(R\)-re (pl. \(A \rightarrow D\) nem teljesül), hozzá kell adni egy sémát, ami kulcs. \(AD\) például egy lehetséges kulcs [37].

  • Végső felbontás: \({AB, AC, AD}\) [37].

Ez az algoritmus biztosítja a függőségőrzést (minden FF megmarad a minimális bázisból) és a veszteségmentes összekapcsolást (a kulcsból létrehozott séma és a CHASE algoritmus garantálja), valamint azt, hogy a kapott relációk 3NF-ben legyenek [34].