Adatbázis alapjai Készítette: Siki Zoltán 1. Bevezetés 1.1. Adatbázis fogalma 1.2. Történelmi áttekintés 1.3. Adatbáziskezelők szerepe, célja 1.4. Különböző adatbázis modellek 1.4.1. Hierarchikus adatbázis modell 1.4.2. Hálós adatbázis modell 1.4.3. Relációs adatbázis modell 1. Bevezetés Az adatbáziskezelés szervezés tantárgy keretén belül az adatbázis tervezés elméletét, a relációs algebra alapjait, illetve a relációs adatbáziskezelők lekérdező nyelvét (SQL) és az Oracle adatbáziskezelő használatát (a teljesség igénye nélkül) ismertetjük. A könnyebb megértést számos minta példával illetve gyakorló feladattal igyekszünk megkönnyíteni. 1.1. Adatbázis fogalma Adatbázison köznapi értelemben valamely rendezett, valamilyen szisztéma szerint tárolt adatokat értünk, melyek nem feltétlenül számítógépen kerülnek tárolásra. Képzeljük el, hogy egy céghez naponta átlagban 20 levél érkezik. A cég irattárosa kellő adattárolási tapasztalat hiján a leveleket az irattár ajtajára vágott lyukon keresztül bedobja. Elképzelhető, hogy pár év eltelte után milyen reménytelen vállalkozás egy levelet megtalálni az irattárban. Ez az adathalmaz nem tekinthető adatbázisnak, ahhoz hogy adatbázis legyen nem elegendő a nagyszámú adat. Az adathalmaz csak akkor válik adatbázissá, ha az valamilyen rend szerint épül fel, mely lehetővé teszi az adatok értelmes kezelését. Természetesen ugyanazon adathalmazból többféle rendszerezés alapján alakíthatunk ki adatbázist. Például egy könyvtárban a könyveket rendezhetnénk a könyvek mérete vagy akár a szerző vagy szerzők testsúlya alapján. Ez már egy rendszert ad az adatok tárolásához. Íly módon minden könyv helye meghatározott. De bizonyára nehéz helyzetben lennénk, ha szerző és cím alapján próbálnánk meg előkeresni egy könyvet. Az adatok tárolásába bevitt rendszernek alkalmasnak kell lennie a leggyakrabban előforduló igények hatékony kielégítésére. Az adatbázisok mellé egy adatbáziskezelő rendszer (DBMS) is járul, mely az adatbázis vagy adatbázisok üzemeltetését biztosítja. Hagyományos adatbázis esetén ez a kezelő személyzet intelligenciájának része, elektronikus adatbázisok esetén pedig valamilyen szoftver.
1.2. Történelmi áttekintés Azóta rendelkezünk adatbázisokkal, mióta írásban vagyunk képesek rögzíteni adatokat. Ez az ókorban történhetett akár kőtáblákra vagy papirusz tekercsekre. Az adatbázisok fejlettebb formái később a kartoték rendszerek lettek, melyek a számítógépek megjelenéséig az alapvető adatbázis rendszerek voltak. A számítástechnika hőskorában az 50-es 60-as években az adatok tárolása még lyukszalagon, lyukkártyán történt, az adatok közvetlenül nem voltak elérhetők a számítógép számára. A mágneses háttértárolók elterjedésével az adatok tárolása egyszerűbbé, elérésük hatékonyabbá vált. Ezekben az időkben még nem léteztek univerzális módszerek illetve rendszerek, melyek segítségével az adatbázisokkal kapcsolatos problémák nagy része általánosan megoldható lett volna. A számítógépek fejlődésével együtt fejlődtek a programozói lehetőségek is. Az első számítógépeken csak a gépi kód (a bináris formában kiadott utasítások a mikroprocesszornak) állt rendelkezésre. Ezt első generációs programnyelvnek nevezzük. Ezt követték a második generációs (assembler) nyelvek, melyekben a gépi kód helyett úgynevezett mnemonikok és szimbólumok alkalmazhatók. Az első illetve második generációs programnyelvekben még nem készültek komoly adatbáziskezelő alkalmazások. Ezekre egyrészt a magas szintű nyelvek (3. generációs program nyelvek) COBOL, FORTRAN stb., másrészről a lemezes operációs rendszerek kialakulásáig kellett várni. Ekkor már komoly adatbázis alkalmazások születtek, melyek egyedi problémák megoldására voltak alkalmasak. Az adatbázisok méretének és számának gyors növekedése következtében az egyedi alkalmazások létrehozása fárasztó és időrabló feladattá vált, ezért a programfejlesztők törekedtek az adatbáziskezelés általános formában történő megfogalmazására. Ennek eredményeként jöttek létre az adatbázis kezelő rendszerek (DBMS) és a negyedik generációs nyelvek (4GL). Az adatbázis kezelő rendszerek számos eszközt nyújtanak az interaktív adatbevitel, menük létrehozása terén, melyek kialakítása a harmadik generációs nyelvekben sok sok oldal kód leírásával lenne csak lehetséges. A szabványos eszközök bevezetésével nem csak a programozói munka csökkent le, hanem az egységes felhasználói felület kialakítására késztetik a programozókat. Az objektum orientált programozási nyelvek térhódításával az adatbázis kezelő rendszerekkel kapcsolatos kutatások is az objektum orientált megközelítés irányába nyitott. A kereskedelmi forgalomban kapható adatbázis kezelők azonban még nem az objektumorientált megközelítésre épülnek.
1. generációs nyelvek Gépi kódú programozás
8C C0 8E D8
MOV AX,ES MOV DS,AX var i, s:integer; Imperatív nyelvek: Algol, s := 0; 3. generációs nyelvek FORTRAN, C, Pascal, Basic for i:=1 to 10 do s := s + i;
2. generációs nyelvek Assembler nyelvek
4. generációs nyelvek Logikai programozási 5. generációs nyelvek nyelvek: Prolog, Lisp
Objektum orientált nyelvek
(+ (cadr p1) (cadr p2) (/ 750 2))
class Complex { protected: double real, img; public: SmallTalk, C++, Java, Visual Complex(); Basic ~Complex(); Complex& operator+(Complex& a); }
1.3. Adatbáziskezelők szerepe, célja Manapság nem elégszünk meg egy adatbázissal, mely az adatokat rendszerezve tárolja, hanem az adatok kezeléséhez szükséges eszközöket is az adatbázis mellé képzeljük. Az így kialakult program rendszert adatbázis kezelő rendszernek (DBMS Database Management System) nevezzük. Egy DBMS egyszerűbb és gyorsabb megoldást kínál az űrlapokon alapuló alkalmazások kidolgozásában, az adatbázis adatokon alapuló jelentések készítésében. A DBMS-ek megváltoztatták a végfelhasználók adatnyerési lehetőségeit az egyszerű lekérdezési nyelvek bevezetésével. A lekérdező nyelvek lehetőséget nyújtanak a nem számítógépes szakemberek számára is tetszőleges lekérdezés gyors végrehajtására. A programozási eszközök mellett az operációs rendszerek illetve azoknak a háttértárakat kezelő része is komoly fejlődésen ment keresztül. Nem volt már szükség a fizikai fájlszerkezet pontos ismeretére, ezt az operációs rendszer illetve az adatbáziskezelő rendszer elfedte a felhasználó és a programozó elől is. Ma már az operációs rendszerek is lehetőséget nyújtanak szekvenciális, indexelt és közvetlen elérésű adatállományok létrehozására. (Ez nem igaz a PC DOS és UNIX operációs rendszerekre.) Az operációs rendszerek fájl kezelői azonban nem értelmezik a fájlok tartalmát, a fájlokat kezelő programoknak kell ismernie az adatok szerkezetét és az adatszerkezetben bekövetkezett változás, akár csak bővülés, esetén a változást a programokon is át kell vezetni. Az adatbázisokban gyakran előfordulnak olyan típusú adatok, melyeket az operációs rendszer vagy a harmadik generációs programnyelvek közvetlenül nem kezelnek, például dátum, időpont, pénzegység stb.
Az adatbáziskezelők az operációs rendszerekhez hasonlóan számos eszközt szolgáltatnak a gyakran előforduló problémák megoldására, de míg az operációs rendszerek a perifériák kezelésére és a fájlok használatára adnak lehetőséget, addig a DBMS-ek eszközei egy magasabb szintű absztrakcióra épülve az adatok logikai szintű elérését támogatják a rekordokon belüli bájt poziciók helyett, továbbá eszközöket tartalmaznak az adatok űrlap szintű kezelésére illetve jelentések és menük készítésére. Az adatbáziskezelők három alapvető feladat körre alapozódnak, melyek mindegyike a számítógépes hardvertől és környezettől való függetlenséggel kapcsolatos. Az általános cél az, hogy inkább az ember gondolkodásához, munkastílusához hozza közelebb az információs rendszer kidolgozását, minthogy az embereket kényszerítse a számítógép stílusú gondolkozásra. ·
·
·
Függetlenség az aktuális hardver konfigurációtól Az adatbáziskezelő rendszer rejtse el a felhasználó és a fejlesztő elől is a számítógépek és azok perifériái között jelentkező különbségeket. Például a fejlesztőnek se kelljen törődnie a fizikai szintű adattárolással, ne kelljen közvetlenül lemez blokkokra, cilinderekre hivatkoznia. Az egyes megjelenítő eszközökön (képernyő, nyomtató) azok típusától függetlenül - az alkalmazás ugyanúgy használható legyen és azonos eredményt szolgáltasson. Ily módon az egyik géptípusra kifejlesztett alkalmazás bármelyik, az adatbáziskezelő által támogatott hardver illetve szoftver környezetben módosítás nélkül használható. Függetlenség az adatelérés módjától Az egyes operációs rendszerek a fájlokra többfajta adatelérési módot (szekvenciális, indexelt, véletlen) kínálnak az alkalmazások készítőinek. Ez azonban maga után vonja, hogy a fejlesztőnek ezt figyelembe kell vennie és az egyes adatállományokat tárolási módjuknak megfelelően kell kezelni. Az adatbáziskezelőtől viszont elvárjuk, hogy az adatok tárolásáról és elérési módjáról maga rendelkezzék. A felhasználó vagy a fejlesztő számára csak a kérdés megfogalmazása és nem az eredmény előállítási módja legyen a feladat. Egy példával ezt úgy szemléltethetnénk, hogy a főnök is csak azt mondja a titkárnőjének, hogy kéri a múlt havi jelentéseket a raktárkészletről, de nem ad útmutatást az adatok elérési módjáról, mivel azt a titkárnő ismeri. Az adatbáziskezelőtől nem csak azt várjuk el, hogy önállóan gondoskodjék az adatok eléréséről, hanem azt is hogy ha több alternatíva is létezik azok közül az optimálisat válassza ki. Függetlenség az adatstruktúráktól Az adatbázisok szerkezetében beálló változások minél kevesebb módosítást okozzanak az alkalmazásokban. Például, ha az adatbázisokat új adatokkal kell bővíteni, akkor azokat a régebben elkészített alkalmazásokat, melyek ezeket az adatokat nem használják, változtatás nélkül tovább használhassuk.
Az adatbáziskezelőket használva nagyobb hatékonyság érthető el az alkalmazások fejlesztésében. Így ugyanaz a feladat kisebb fejlesztőcsoporttal vagy rövidebb idő alatt oldható meg.
1.4. Különböző adatbázis modellek Az adatbáziskezelők fejlődése során többfajta logikai modell alakult ki, melyek főként az adatok közötti kapcsolatok tárolásában térnek el egymástól. A három alapvető modell a hierarchikus, a háló és a relációs modell. Ezek közül manapság a DOS/Windows 3.x/Windows 95/NT illetve UNIX operációs rendszerekben kizárólag a relációs modellre épülő adatbáziskezelőket használnak. Ezért itt csak röviden ismertetjük a másik két modellt. 1.4.1. Hierarchikus adatbázis modell A hierarchikus modell volt a legelső az adatbáziskezelőkben és egyben a leginkább korlátozott. Például az IBM IMS adatbáziskezelő rendszer alkalmazta ezt a modellt. A neve is utal rá, hogy az adatokat egy hierarchiában kell elrendezni. Ezt egy fa szerkezettel tehetjük szemléletessé.
1.1 ábra Hierarchikus adatmodell Az adatbázis több egymástól független fából állhat. A fa csomópontjaiban és leveleiben helyezkednek el az adatok. A közöttük levő kapcsolat, szülő gyermek kapcsolatnak felel meg. Így csak 1:n típusú kapcsolatok képezhetők le segítségével. Az 1:n kapcsolat azt jelenti, hogy az adatszerkezet egyik típusú adata a hierarchiában alatta elhelyezkedő egy vagy több más adattal áll kapcsolatban. A hierarchikus modell természetéből adódóan nem ábrázolhatunk benne n:m típusú kapcsolatokat (lásd a háló modellt). Emellett további hátránya, hogy az adatok elérése csak egyféle sorrendben lehetséges, a tárolt hierarchiának megfelelő sorrendben. A hierarchikus adatmodell alkalmazására a legkézenfekvőbb példa a családfa. De a főnökbeosztott viszonyok vagy egy iskola szerkezete is leírható ebben a modellben. Az iskola esetén többféle hierarchia is felépíthető. Egyrészt az iskola több osztályra bomlik és az osztályok tanulókból állnak. Másrészt az iskolát az igazgató vezeti, a többi tanár az ő beosztottja és a tanárok egy vagy több tantárgyat tanítanak.
1.2 ábra Iskola hierarchikus felépítése a diákok szemszögéből
1.3 ábra Iskola hierarchikus felépítése a tanárok szemszögéből 1.4.2. Hálós adatbázis modell A hálós adatmodell esetén az egyes azonos vagy különböző összetételű adategységek (rekordok) között a kapcsolat egy gráffal írható le. A gráf csomópontok és ezeket összekötő élek rendszere, melyben tetszőleges két csomópont között akkor van adatkapcsolat, ha őket él köti össze egymással. Egy csomópontból tetszőleges számú él indulhat ki, de egy él csak két csomópontot köthet össze. Azaz minden adategység tetszőleges más adategységekkel lehet kapcsolatban. ebben a modellben n:m típusú adatkapcsolatok is leírhatók az 1:n típusúak mellett. A hierarchikus és a hálós modell esetén az adatbázisba fixen beépített kapcsolatok következtében csak a tárolt kapcsolatok segítségével bejárható adat-visszakeresések oldhatók meg hatékonyan (sok esetben hatékonyabban mint más modellekben). További hátrányuk, hogy szerkezetük merev, módosításuk nehézkes.
1.4 ábra Hálós adatmodell
Az iskolai példánál maradva az egyes diákok illetve tanárok közötti kapcsolat hálós modellben írható le. Minden diákot több tanár tanít és minden tanár több diákot tanít. 1.4.3. Relációs adatbázis modell A relációs az egyik legáttekinhetőbb és a 80-as évektől kezdve a legelterjedtebb adatmodell. Ebben a modellben az adatokat táblázatok soraiban képezzük le. A legfontosabb eltérés az előzőekben bemutatott két modellhez képest az, hogy itt nincsenek előre definiált kapcsolatok az egyes adategységek között, hanem a kapcsolatok létrehozásához szükséges adatokat tároljuk többszörösen. Ezzel egy sokkal rugalmasabb és általánosabb szerkezetet kapunk. A relációs modellt részletesen tárgyaljuk a következőkben.
2. Relációs adatbázisok, alapfogalmak A relációs adatmodell kidolgozása Codd nevéhez fűződik (1971). Azóta fontos szerepet játszik az adatbáziskezelők alkalmazásában. A relációs modell előnyei a következők: · · ·
A relációs adatszerkezet egyszerűen értelmezhető a felhasználók és az alkalmazás készítők számára is, így ez lehet közöttük a kommunikáció eszköze. A logikai adatmodell relációi egy relációs adatbáziskezelő rendszerbe módosítások nélkül átvihetők. A relációs modellben az adatbázistervezés a normál formák bevezetésével egzakt módon elvégezhető (ld. 3. fejezet).
2.1. Relációk és a velük kapcsolatos alapfogalmak A reláció nem más mint egy táblázat, a táblázat soraiban tárolt adatokkal együtt. A relációs adatbázis pedig relációk és csak relációk összessége. Az egyes relációkat egyedi névvel látjuk el. A relációk oszlopaiban azonos mennyiségre vonatkozó adatok jelennek meg. Az oszlopok névvel rendelkeznek, melyeknek a reláción belül egyedieknek kell lenniük, de más relációk tartalmazhatnak azonos nevű oszlopokat. A reláció soraiban tároljuk a logikailag összetartozó adatokat. A reláció sorainak sorrendje közömbös, de nem tartalmazhat két azonos adatokkal kitöltött sort. Egy sor és oszlop metszésében található táblázat elemet mezőnek nevezzük, a mezők tartalmazzák az adatokat. A mezőkben oszloponként különböző típusú (numerikus, szöveges stb.) mennyiségek tárolhatók. A reláció helyett sokszor a tábla vagy táblázat, a sor helyett a rekord, az oszlop helyett pedig az attribútum elnevezés is használatos.
2.1 ábra Relációk elemei
Például egy személyi adatokat tartalmazó reláció a következő lehet: Személy Személyi szám Név Város Foglalkozás 1 650410 1256 Kiss lászló Győr kőműves 2 781117 0131 Nagy Ágnes Szeged
tanuló
1 610105 1167 Kiss László Budapest lakatos 2.2 ábra Személyi adatok relációja Az előző relációból a személyi szám oszlopot elhagyva relációnak tekinthető-e a táblázat? Mivel nem zárható ki, hogy két azonos nevű és szakmájú személy éljen egy településen belül a személyi szám nélkül két azonos sor is szerepelhetne, mely a relációban nem megengedett. A reláció oszlopainak elnevezésére célszerű a tartalomra utaló elnevezést használni még akkor is, ha ez esetleg több gépeléssel is jár. Ehhez álljon itt a következő példa: R A
B
C
Anyag kód készlet egységár
1206 389 274
1206
389
274
967 2012 65
967
2012
65
12 654 712
12
654
712
2.3 ábra Példa az oszlopok helytelen és helyes elnevezésére Az előző két reláció ugyanazokat az oszlopokat tartalmazza, de a bal oldali esetben további feljegyzésekre van szükség az egyes oszlopok tartalmának leírására. A relációktól általában megköveteljük, hogy ne tartalmazzanak más adatokból levezethető vagy kiszámítható információkat. Például az anyag relációban (2.3 ábra) fölösleges lenne egy érték oszlopot is tárolni, mivel ez az adat a készlet és az egységár szorzataként kiszámítható a rendelkezésre álló adatokból. Hasonlóképpen a személyi szám mellett nincs értelme külön a születési dátumot nyilvántartani, mert az része a személyi számnak, abból előállítható.
3. Adatbázistervezés Az adatbázistervezés egy folyamat, mely több lépésből tevődik össze. Először az adatbázisban leképezendő rendszert elemzésnek vetjük alá és meghatározzuk a tárolandó adatok körét, azok egymásközötti kapcsolatait és az adatbázissal szemben felmerülő igényeket. Ezután következik a rendszer tervezés, melynek eredménye az adatbázis logikai modellje. Végül fizikai szinten képezzük le a logikai adatbázis modellt a felhasználható szoftver és hardver függvényében.
3.1 ábra A tervezés lépései A tényleges tervezés ismertetése előtt néhány újabb fogalmat kell bevezetni - funkcionális függőség, reláció kulcs, redundancia, - melyek segítségével a tervezés módszertana egyszerűbben magyarázható. 3.1 Adatok közötti funkcionális kapcsolat Adatok között akkor áll fenn funkcionális kapcsolat, ha egy vagy több adat konkrét értékéből más adatok egyértelműen következnek. Például a személyi szám és a név között funkcionális kapcsolat áll fenn, mivel minden embernek különböző személyi száma van. Ezt a SZEMÉLYI_SZÁM -> NÉV kifejezéssel jelöljük vagy pedig egy diagrammal.
3.2 ábra Funkcionális függőség diagram A funkcionális függőség bal oldalát a függőség meghatározójának nevezzük. A jobb oldalon levő egy, csak egy értéket határoz meg a funkcionális függőség. Nem áll fenn funkcionális függőség akkor, ha a meghatározó egy értékét több attribútum értékkel hozhatjuk kapcsolatba. Például a NÉV -> SZÜLETÉSI_ÉV állítás nem igaz, mert több személynek lehet azonos neve, akik különböző időpontokban születtek. Néhány évvel ezelőtt a SZEMÉLYI_SZÁM > AUTÓ_TIPUS funkcionális függőség igaz volt, mert mindenkinek csak egy autója lehetett. Ma azonban ez már nem állja meg a helyét. Az adatok közötti funkcionális függőségek az adatok természetéből következnek, nekünk csak fel kell ismerni ezeket a törvényszerűségeket. A tervezés során nagyon fontos, hogy ezeket pontosan felismerjük és figyelembe vegyük. A funkcionális függőség jobb oldalán több attribútum is állhat. Például az AUTÓ_RENDSZÁM -> TIPUS, TULAJDONOS funkcionális függőség azt fejezi ki, hogy az autó rendszámából következik a típusa és a tulajdonos neve, mivel minden autónak különböző a rendszáma, minden autónak egy tulajdonosa és típusa van. Ezt diagrammal is ábrázolhatjuk.
3.3 ábra Funkcionális függőség több meghatározott értékkel Az is előfordulhat, hogy két attribútum kölcsönösen függ egymástól. Ez a helyzet például a házastársak esetén FÉRJ_SZEM_SZÁMA -> FELESÉG_SZEM_SZÁMA FELESÉG_SZEM_SZÉMA <- FÉRJ_SZEM_SZÁMA. Mindkét funkcionális kapcsolat igaz és ezt a FÉRJ_SZEM_SZÁMA <-> FELESÉG_SZEM_SZÁMA jelöléssel fejezzük ki. Természetesen a fenti összefüggés a többnejűséget megengedő országokban nem teljesül. A funkcionális függőség bal oldalán több attribútum is megjelenhet, melyek együttesen határozzák meg a jobb oldalon szereplő attribútum értékét. Például hőmérsékletet mérünk különböző helyeken és időben úgy, hogy a helyszínek között azonosak is lehetnek. Ebben az esetben a következő funkcionális függőség áll fenn az attribútumok között: HELY, IDŐPONT -> HŐMÉRSÉKLET. A fenti összefüggést az alábbi diagrammal is jelölhetjük:
3.4 ábra Funkcionális függőség összetett meghatározóval A funkcionális függőségek speciális esete a teljes funkcionális függőség. Erről akkor beszélhetünk, ha a meghatározó oldalon nincsen felesleges attribútum. Például a RENDSZÁM, TÍPUS -> SZÍN funkcionális függőség nem teljes funkcionális függőség, mivel a rendszám már egyértelműen meghatározza a kocsi színét, ehhez nincs szükség a típusra is. A funkcionális függőség bevezetése után a relációk egy másik, matematikai jelölésekre épülő leírását is bemutatjuk. Általános formája: reláció_név=({attribútumok},{funkcionális függőségek listája}) Például: Az alábbi táblázat formában adott reláció Személyi szám
Név
Munkahely
3.5 ábra Reláció táblázatos megadása matematikai jelöléssel a következő formában SZEMÉLYEK=({SZEMÉLYI_SZÁM, NÉV, MUNKAHELY}, {SZEMÉLYI_SZÁM -> NÉV, SZEMÉLYI_SZÁM -> MUNKAHELY}) írható le a funkcionális függőségekkel együtt, feltételezve, hogy mindenkinek csak egy munkahelye van.
3.2 Adatok közötti többértékű függőség Az adatok között fennálló kapcsolatok közül nem mindegyik fejezhető ki a funkcionális függőség segítségével. Például minden embernek lehet több szakmája, illetve ugyanazzal a szakmával több ember is rendelkezhet. Ebben az esetben egyik irányban sincs egyértelmű függőség. Ez egy többértékű függőség, az egyik attribútumhoz egy másik attribútum csoportja, halmaza kapcsolódik. A többértékű függőség ábrázolására a dupla nyilat használjuk. SZEMÉLYI_SZÁM ->> SZAKMA. A funkcionális függőséghez hasonlóan, többértékű függőség esetén is előfordulhat, hogy egy attribútum értékéből egynél több további attribútum értéke következik. Az előző példát bővítve: SZEMÉLYI_SZÁM ->> SZAKMA, OKLEVÉL_KELTE
3.6 ábra Többértékű függőség diagram A funkcionális és a többértékű függőség között kapcsolat van. Nagyon gyakran ugyanazt a függőségi kapcsolatot kifejezhetjük funkcionális és többértékű függőséggel is. Ennek bemutatására nézzük meg a következő példát. Egy üzemben különböző termékeket gyártanak, melyek mindegyike többfajta alkatrészből tevődik össze. Szeretnénk nyilvántartani termékenként a felhasznált alkatrészek mennyiségét. Ezt leírhatjuk funkcionális függőség segítségével TERMÉK_AZONOSÍTÓ,ALKATRÉSZ_AZONOSÍTÓ -> MENNYISÉG, mely azt fejezi ki, hogy egy termékbe adott mennyiségű alkatrészt építettek be. Másik oldalról többértékű függőséggel is kifejezhetjük az adatok kapcsolatát. TERMÉK_AZONOSÍTÓ >> ALKATRÉSZ_AZONOSÍTÓ, MENNYISÉG. Ez azt fejezi ki, hogy minden termékbe az alkatrészek egy csoportját és azoknak bizonyos mennyiségét építették be. A funkcionális függőségeket mindig előnyben kell részesíteni a többértékű függőséggel szemben. Általános szabályként kimondhatjuk azt, hogy először az összes funkcionális függőséget írjuk fel, majd a hiányzó kapcsolatok leírására használjuk csak a többértékű függőséget. 3.3 Reláció kulcs fogalma A reláció kulcs a reláció egy sorát azonosítja egyértelműen. A reláció - definíció szerint- nem tartalmazhat két azonos sort, ezért minden relációban létezik kulcs. A reláció kulcsnak a következő feltételeket kell teljesítenie · · ·
az attribútumok egy olyan csoportja, melyek csak egy sort azonosítanak (egyértelműség) a kulcsban szereplő attribútumok egyetlen részhalmaza sem alkot kulcsot a kulcsban szereplő attribútumok értéke nem lehet definiálatlan (NULL)
A definiálatlan (NULL) értékek tárolását a relációs adatbázis kezelők speciálisan oldják meg. Numerikus értékek esetén a NULL érték és a 0 nem azonos. Egy relációban tartsuk nyilván az osztály tanulóinak személyi adatait Diák Személyi szám
Születési év
Név
3.7 ábra Reláció kulcs SZEMÉLY_ADATOK=({ SZEMÉLYI_SZÁM, SZÜL_ÉV, NÉV}). A SZEMÉLYI_ADATOK relációban a SZEMÉLYI_SZÁM attribútum kulcs, mert nem lehet az adatok között két különböző személy azonos személyi számmal. A születési év vagy a név nem azonosítja egyértelműen a reláció egy sorát mivel ugyanazon a napon is született tanulók vagy azonos nevűek is lehetnek az osztályban. Vajon a személyi szám és a születési év kulcsa-e a személyi adatok relációnak? Együtt a reláció egy sorát azonosítják, de nem tesznek eleget a kulcsokra vonatkozó azon feltételnek, hogy a bennük szereplő attribútumok részhalmaza nem lehet kulcs. Ebben az esetben a személyi szám már kulcs, így bármelyik másik attribútummal kombinálva már nem alkothat kulcsot. Előfordulnak olyan relációk is, melyekben a kulcs több attribútum érték összekapcsolásával állítható elő. Készítsünk nyilvántartást a diákok különböző tantárgyakból szerzett osztályzatairól az alábbi relációval: NAPLÓ=({SZEMÉLYI_SZÁM, TANTÁRGY, DÁTUM, OSZTÁLYZAT)}
Személyi szám
Napló Tantárgy
Dátum
Osztályzat
3.8 ábra reláció összetett kulccsal
A NAPLÓ relációban a SZEMÉLYI_SZÁM nem azonosít egy sort, mivel egy diáknak több osztályzata is lehet akár ugyanabból a tantárgyból is. Ezért még a SZEMÉLYI_SZÁM és a TANTÁRGY sem alkot kulcsot. A SZEMÉLYI_SZÁM, TANTÁRGY és a DÁTUM is csak akkor alkot kulcsot, ha kizárjuk annak lehetőségét, hogy ugyanazon a napon ugyanabból a tantárgyból egy diák két osztályzatot kaphat. Abban az esetben, ha ez a feltételezés nem tartható (ennek a rendszer analiziséből kell kiderülnie!), akkor nem csak az osztályzat megszerzésének dátumát, hanem annak időpontját is tárolni kell. Ilyenkor természetesen a NAPLÓ relációt ezzel az új oszloppal ki kell bővíteni.
Nem csak összetett kulcsok fordulhatnak elő a relációkban, léteznek olyan relációk is, melyekben nem csak egy, hanem több kulcs is található. Ennek illusztrálására nézzük meg a következő relációt KONZULTÁCIÓ=({TANÁR, IDŐPONT, DIÁK)}
Tanár
Konzultáció Időpont
Diák
3.9 ábra Reláció több kulccsal A KONZULTÁCIÓ relációban a tanár illetve a diák oszlopban olyan azonosítót képzelünk, mely a személyt egyértelműen azonosítja (például személyi szám). Minden egyes diák több konzultáción vehet rész, minden tanár több konzultációt tarthat, sőt ugyanaz a diák ugyanannak a tanárnak más-más időpontokban tartott konzultációin is részt vehet. Ezekből következik, hogy sem a TANÁR, sem a DIÁK, sem pedig ez a két azonosító együtt nem kulcsa a relációnak. De egy személy egy időben csak egy helyen tartózkodhat. Ebből következik, hogy a TANÁR, IDŐPONT attribútumok kulcsot alkotnak, de ugyanilyen okból kifolyólag a DIÁK, IDŐPONT attribútumok is kulcsot alkotnak. Vegyük észre azt, hogy a kulcsok nem önkényes döntések következtében alakulnak ki, hanem az adatok természetéből következnek, mint a funkcionális vagy a többértékű függőség. A relációban külső kulcsot vagy kulcsokat is megkülönböztetünk. Ezek az attribútumok nem az adott relációban, hanem az adatbázis másik relációjában alkotnak kulcsot. Például ha a KONZULTÁCIÓ relációban a DIÁK azonosítására a személyi számot alkalmazzuk, akkor ez egy külső kulcs a személyi adatokat nyilvántartó relációhoz. 3.4 Redundancia fogalma A logikai adatbázis tervezés egyik fő célja a redundanciák megszüntetése. Redundanciáról akkor beszélünk, ha valamely tényt vagy a többi adatból levezethető mennyiséget ismételten (többszörösen) tároljuk az adatbázisban. A redundancia, a szükségtelen tároló terület lefoglalása mellett, komplikált adatbázis frissítési és karbantartási műveletekhez vezet, melyek könnyen az adatbázis inkonzisztenciáját okozhatják. Egy adatbázis akkor inkonzisztens, ha egymásnak ellentmondó tényeket tartalmaz. Megjegyezzük, hogy a fizikai tervezés során az adatbázis műveletek gyorsítása érdekében esetleg redundáns attribútumokat is bevezetünk.
A redundancia egyik fajtája amikor ugyanazt a tényt többször tároljuk. Nézzük meg a következő relációt. Tanár Kiss Péter
Tantárgy
Össz_óraszám Tanított_órák Adatbázis kezelés 64 12
Nagy Andrea Matematika
32
8
Szabó Miklós Adatbázis kezelés 64
4
Kovács Rita Matematika
5
Angol
32 48
3.10 ábra Redundanciát tartalmazó reláció A fenti relációban a tantárgyak össz óraszámát annyiszor tároljuk, ahány tanár tanítja az adott tantárgyat. A példa kedvéért feltételeztük, hogy egy tantárgyat több tanár is tanít. A redundancia a következő hátrányokkal jár: · · ·
Ha egy tantárgy össz óraszáma megváltozik több helyen kell módosítani a relációban. Valahányszor egy új tanár kerül be a relációba ugyanannak a tantárgynak az előző soraiból kell elővenni az össz óraszám adatot. Az utolsó sorban szereplő tantárgy (angol) esetén még nem került kitöltésre a tanár személye. Új tanárnak a listára történő felvételekor ezt az esetet másként kell kezelni. Ilyenkor csak két üres értéket (tanár, tanított órák) kell átírni.
A redundanciát meg kell különböztetni az értékek duplikált (többszörös) tárolásától. A duplikált adattárolásra szükségünk lehet a relációkban, míg a redundanciát el kell kerülni. Vizsgáljuk meg a következő relációt. Termék
Alkatrész
Darab
Nyomtató
papír adagoló
1
Nyomtató
64Kb memória
2
Számítógép
1.2 MB floppy
1
Számítógép
1 MB memória
4
3.11 ábra Adatok többszörös tárolása Az előző táblázat a termék oszlopban többször tartalmazza a nyomtató és számítógép adatokat. Ez azonban nem okoz redundanciát, mivel egy termék több alkatrészből is állhat, így nem ugyanannak a ténynek a többszörös tárolásáról van szó, hanem egy másik tényt fejezünk ki, melyhez elengedhetetlen a duplikált tárolás. A duplikált és a redundáns adatok között a funkcionális függőségek vizsgálatával tehetünk különbséget. Ezt majd a normál formák ismertetésénél tesszük meg. A redundancia fordul elő akkor is, ha levezett vagy levezethető mennyiségeket tárolunk a relációkban.
Levezetett adatokat tartalmazhat egyetlen reláció is abban az esetben, ha egyes attribútumok értéke egyértelműen meghatározható a többi attribútum alapján, például, ha a kerületet is nyilvántartjuk az irányítószám mellett. A redundáns adatok megszüntetésére két mód van. A levezetett adatokat tartalmazó relációkat vagy attribútumokat el kell hagyni. A relációkban tárolt redundáns tényeket a táblázatok szétbontásával, dekompozíciójával szüntethetjük meg (a 3.10 példában szereplő relációt kettő relációra bontjuk fel Órák = {Tanár, Tantárgy, Tanított_Órák} és Össz_órák = {Tantárgy, Össz_óraszám} 3.5 Redundancia megszüntetése, a relációk normál alakjai A logikai tervezés célja egy redundancia mentes reláció rendszer, relációs adatbázis. A reláció elmélet módszereket tartalmaz a redundancia megszüntetésére, az úgynevezett normál formák segítségével. A következőkben a relációk normál formáinak definícióját mutatjuk be példákon keresztül. A normál formák előállítása során a funkcionális és a többértékű függőség, valamint a reláció kulcs fogalmát használjuk fel. A normál formák képzése során leegyszerűsítve, olyan relációk felírása a cél, melyekben csak a reláció kulcsra vonatkozó tényeket tárolunk. Öt normál formát különböztetünk meg. A különböző normál formák egymásra épülnek, a második normál formában levő reláció első normál formában is van. A tervezés során a legmagasabb normál forma elérése a cél. Az első három normál forma a funkcionális függőségekben található redundanciák, míg a negyedik és ötödik a többértékű függőségekből adódó redundanciák megszüntetésére koncentrál. A normál formákkal kapcsolatban két újabb a relációkhoz kapcsolódó fogalommal kell megismerkedni. Elsődleges attribútumnak nevezzük azokat az attribútumokat, melyek legalább egy reláció kulcsban szerepelnek. A többi attribútumot nem elsődlegesnek nevezzük. 3.5.1 Első normál forma (1NF) Egy reláció első normál formában van, ha minden attribútuma egyszerű, nem összetett adat. A könyvben eddig szereplő valamennyi reláció kielégíti az első normál forma feltételét. Mintaképpen álljon itt egy olyan reláció, melynek attribútumai is relációk.
Szakkör
Szakkörök Tanár
Diákok
Név Osztály Számítástechnika Nagy Pál Kiss Rita III.b Álmos Éva II.c Név Osztály I.a Gál János Réz Ede Vas Ferenc II.b
Video
Szakkör
Szakkörök Tanár Diák
Számítástechnika Nagy Pál Kiss Rita
Osztály III.b
Számítástechnika Nagy Pál Álmos Éva II.c Video
Gál János Réz Ede
I.a
Video
Gál János Vas Ferenc II.b
3.13 ábra Nem normál formájú reláció és első normál formája Annak eldöntése, hogy egy attribútumot egyszerűnek vagy összetettnek tekintünk nem mindig egyértelmű, az adatok felhasználásától is függ. A döntéseink során, hogy egy vagy több attribútumot tervezünk az adat tárolására, tartsuk szem előtt egyszerűbb több oszlopból egyet csinálni, mint egy oszlop tartalmát több részre vágni. Például egy vagy két attribútumban tároljuk a személyek vezeték és keresztnevét. Amennyiben a nevek között nem akarunk külön-külön kereszt és vezetéknév szerint keresni, akkor elfogadható lehet az egy mezőben tárolás.
3.5.2 Második normál forma (2NF) Az első normál forma nem elegendő feltétel a redundanciák megszüntetésére. Egy reláció második normál alakjában nem tartalmazhat tényeket a reláció kulcs egy részére vonatkozóan. A második normál forma definíciója két feltétellel írható le. · ·
A reláció első normál formában van A reláció minden nem elsődleges attribútuma teljes funkcionális függőségben van az összes reláció kulccsal
Terem
Konferencia Időpont Előadás
Férőhely
B
10:00
Mitológia
250
A
8:30
Irodalom
130
B
11:30
Szinház
250
A
11:00
Festészet
130
A
13:15
Régészet
130
Konferencia Terem Időpont Előadás B
10:00
Mitológia
A
8:30
Irodalom
B
11:30
Szinház
A
11:00
Festészet
A
13:15
Régészet
Termek Terem Férőhely A
130
B
250
3.14 Első normál formájú reláció és második normál alakú dekompozíciója Az előző ábrán látható első reláció a következő matematikai formában írható le KONFERENCIA = ({TEREM, IDŐPONT, ELŐADÁS, FÉRŐHELY}, {TEREM, IDŐPONT-> ELŐADÁS, TEREM->FÉRŐHELY}). A reláció attribútumai között két funkcionális függőség adható meg, minden egyes teremben egyidőben csak egy előadás lehet és minden terem befogadóképessége adott. A reláció kulcsa a TEREM, IDŐPONT attribútumok, ezek a reláció elsődleges attribútumai. A másodlagos attribútumok (ELŐADÁS, FÉRŐHELY) közül a FÉRŐHELY csak a reláció kulcs egy részétől függ, ezért nincs második normál formában. A felbontás után keletkezett két reláció már második normál formában van. KONFERENCIA = ({TEREM, IDŐPONT, ELŐADÁS}, {TEREM, IDŐPONT-> ELŐADÁS}) TERMEK = (TEREM, FÉRŐHELY}, {TEREM-> FÉRŐHELY}). Minden nem elsődleges attribútum teljes funkcionális függőségben van a reláció kulccsal (TEREM, IDŐPONT illetve TEREM). Azok a relációk, melyek reláció kulcsa csak egy attribútumból áll, mindig második normál formában vannak, ekkor ugyanis nem lehetséges, hogy csak a reláció kulcs egy részétől függjön egy nem elsődleges attribútum.
3.15 ábra Függőségi diagram Nézzünk egy másik példát is a második normál forma feltételeit megsértő relációra. Egy épület energia gazdálkodásának ellenőrzésére az egyes helységekben rendszeresen megmérik a hőmérsékletet. A mérési eredmények értékeléséhez nyilvántartjuk az egyes helységekben található radiátorok számát is.
Terem 213
Hőmérsékletek Időpont Hőmérséklet 98.11.18 23
2
213
98.11.24
22
2
213
98.12.05
21
2
214
98.12.05
21
3
214
98.12.15
20
3
Konferencia Terem Időpont Hőmérséklet 213 98.11.18 23 213
98.11.24 22
213
98.12.05 21
214
98.12.05 21
214
98.12.15 20
Radiátor
Termek Terem Radiátor 213 2 214
3
A redundanciát ismét a táblázat két részre bontásával tudjuk megszüntetni.
3.5.3 Harmadik normál forma (3NF) A második normál formájú relációkban nem lehetnek olyan tények, amelyek a reláció kulcs részeihez kapcsolódnak. Azonban ennek ellenére is lehet bennük redundancia, ha olyan tényeket tartalmaznak, amelyek a nem elsődleges attribútumokkal állnak kapcsolatban. Ezt a lehetőséget szünteti meg a harmadik normál forma. Egy reláció harmadik normál formában van, ha · ·
A reláció második normál formában van. A reláció nem tartalmaz funkcionális függőséget a nem elsődleges attribútumok között.
Ezt ismét egy példa segítségével mutatjuk be. Szakkörök Szakkör Tanár Születési év Képzőművész Sár Izodor 1943 Iparművész
Sár Izodor 1943
Karate
Erős János 1972
Szakkörök Szakkör Tanár Képzőművész Sár Izodor Iparművész
Sár Izodor
Karate
Erős János
Tanárok Tanár Születési év Erős János 1972 Sár Izodor 1943
3.16 ábra Második normál formájú reláció és harmadik normál formájú dekompozíciója
3.17 ábra Függőségi diagram A példában szereplő felső reláció második normál formában van, csak egy attribútumból áll a reláció kulcs és mindkét nem elsődleges attribútum teljes funkcionális függőségben van a reláció kulccsal. A reláció mégis tartalmaz redundanciát, mivel ugyanannak a tanárnak a születési éve többször is szerepel benne. A születési év funkcionálisan függ a tanár név attribútumtól. SZAKKÖRÖK = ({SZAKKÖR, TANÁR, SZÜLETÉSI ÉV}, {SZAKKÖR-> TANÁR, SZÜLETÉSI ÉV, TANÁR->SZÜLETÉSI ÉV}). A felbontás után a nem elsődleges attribútumok közötti függőséget kivettük az eredeti relációból: SZAKKÖRÖK = ({SZAKKÖR, TANÁR}, {SZAKKÖR->TANÁR}) TANÁROK = ({TANÁR, SZÜLETÉSI ÉV}, {TANÁR->SZÜLETÉSI ÉV}). Minden olyan reláció, mely második normál formában van és nincs vagy csak egy nem elsődleges attribútuma van, a harmadik normál forma feltételeit is kielégíti.
3.5.4 Boyce/Codd normál forma (BCNF) A normál formák tárgyalása során eddig olyan relációkra mutattunk példákat, melyeknek csak egy reláció kulcsa van. A normál formák definíciója természetesen alkalmazható a több kulccsal rendelkező relációkra is. Ebben az esetben minden attribútum, mely valamely kulcsnak a része, elsődleges attribútum, de ez az attribútum függhet egy másik, ezt nem tartalmazó kulcs részétől. Ha ez a helyzet fennáll, redundanciát tartalmaz a reláció. Ennek a felismerése vezetett a harmadik normál forma egy szigorúbb definíciójához, a Boyce/Codd normál formához. ·
Minden elsődleges attribútum teljes funkcionális függőségben van azokkal a kulcsokkal, melyeknek nem része
Mintaként tekintsük a következő relációt: Tantárgyak Tanár Időpont Tantárgy Félév
Diák_szá m
Kiss Pál 93/1
Adatbázis 1
17
Jó Péter 93/1
Unix
1
21
Kiss Pál 93/2
Adatbázis 2
32
Jó Péter 93/1
Unix
2
19
KissPál 93/1
Adatbázis 3
25
Tantárgyak Időpont Tantárgy Félév Diák_szám 93/1 Adatbázis 1 17 93/1
Unix
1
21
93/2
Adatbázis 2
32
93/2
Unix
2
93/1
Adatbázis 3
Tanárok Tanár Időpont
Tantárg y
Kiss Pál 93/1
Adatbázi s
19
Jó Péter 93/1
Unix
25
Kiss Pál 93/2
Adatbázis
3.18 ábra Reláció harmadik normál formája és Boyce/Codd normál formát kielégítő dekompozíciója Tételezzük fel, hogy minden tanár csak egy tantárgyat, de annak különböző féléveit oktatja. Ezek alapján a következő funkcionális függőségek írhatók fel: Tanár, Félév -> Tantárgy Tantárgy, Félév -> Tanár
A relációnak két kulcsa van, a (Tanár, Időpont, Félév) és a (Tantárgy, Időpont, Félév). A relációban csak egy nem elsődleges attribútum található, a Diák_szám. Ez teljes funkcionális függőségben van mindkét reláció kulccsal, az elsődleges attribútumok között nincs függőségi viszony. Ezek alapján a reláció harmadik normál formában van. Azonban tartalmaz redundanciát, mivel ugyanazon tanár mellett többször is tároljuk a tantárgyat azonos időpontokban. A redundanciának az az oka, hogy a tanár attribútum az őt nem tartalmazó reláció kulcs (Tantárgy, Időpont, Félév) csak egy részétől (Tantárgy, Félév) függ. 3.5.5 Negyedik normál forma (4NF) Sajnos még a Boyce/Codd normál forma is tartalmazhat redundanciát. Mindeddig csak a funkcionális függőségeket vizsgáltuk, a többértékű függőségeket nem. A további két normál forma a többértékű függőségekből adódó redundancia kiszűrését szolgálja. Egy reláció negyedik normál formában van · ·
Harmadik normál formában van és egy X->>Y többértékű függőséget tartalmazó relációban csak az X és Y-ban megtalálható attribútumokat tartalmazza.
Képzeljük el azt, hogy egy relációban tároljuk a személyek, és barátaik nevét valamint hobbiját. Minden személynek több barátja és több hobbija is lehet. Barátok-hobbik Személy Barát Hobbi Nagy József Elek Attila foci Nagy József Varga Attila foci Kiss Péter
Kiss Pál
sakk
Kiss Péter
Kiss Pál
video
Barát Személy Barát Nagy József Elek Attila
Hobbi Személy Hobbi Nagy József foci
Nagy József Varga Attila Kiss Péter
sakk
Kiss Péter
video
Kiss Pál
Kiss Péter
3.19 ábra Reláció és negyedik normál formája Az eredeti reláció kulcsa valamennyi attribútumot tartalmazza. Csak egy kulcs van és nincsenek nem elsődleges attribútumok. Ezek alapján a reláció harmadik, sőt Boyce/Codd normál formában van, de mégis tartalmaz redundanciát, ugyanaz a személy-barát illetve személy-hobby kapcsolat többször is szerepelhet. A barát illetve hobby oszlop nem maradhat üres (NULL), mert része a kulcsnak! A reláció két többértékű függőséget tartalmaz: Személy>>Barát és Személy->>Hobbi. A negyedik normál forma szabályait kielégítő két relációra felbontva a redundancia megszüntethető.
3.5.6. Ötödik normál forma (5NF) Hosszú ideig a negyedik normál formát tartották a normalizálás utolsó lépésének. A többértékű függőségek külön relációkban tárolásával azonban információt veszthetünk. Ennek bemutatására nézzünk egy példát. Egy számítógépes ismeretek oktatására szakosodott Kft. több jól képzett tanárral rendelkezik. A tanárok többfajta tanfolyam oktatására is alkalmasak. A tanfolyamok az ország különböző részeiben kerülnek megtartásra. Ezek alapján állítsuk össze a Tanár-Tanfolyam-Helyszín relációt. Ebben a relációban csupán azt kívánjuk tárolni, hogy hol és milyen tanfolyamokat tartottak a tanárok, feltételezzük, hogy ugyanazon a helyszínen egyfajta tanfolyam csak egyszer kerül megtartásra. Tanár-Tanfolyam-Helyszín Tanár Tanfolyam Helyszín Nagy Éva Adatbázis I. Szeged Kiss Pál
Adatbázis I. Győr
Nagy Éva Adatbázis II. Pécs Kiss Pál
Adatbázis I. Pécs
Tanár_Tanfolyam Tanár Tanfolyam Nagy Éva Adatbázis I. Kiss Pál
Adatbázis I.
Nagy Éva Adatbázis II.
Tanfolyam-Helyszín Tanár-Helyszín Tanfolyam Helyszín Tanár Helyszín Adatbázis I. Szeged Nagy Éva Szeged Adatbázis I. Győr
Kiss Pál
Győr
Adatbázis II. Pécs
Nagy Éva Pécs
Adatbázis I. Pécs
Kiss Pál
Pécs
3.20 ábra Reláció, dekompozíciója és ötödik normál formája A következő függőségeket írhatjuk fel Tanár->>Tanfolyam Tanár->>Helyszín Tanfolyam>>Helyszín. Az egyetlen reláció kulcs tartalmazza az összes attribútumot (Tanár, Tanfolyam, Helyszín), ebből következik, hogy Boyce/Codd normál formában van a reláció, de mégis tartalmaz redundanciát. Például két sorban is megtalálható, hogy Kiss Pál Adatbázis I. tanfolyamot tanít. A relációt felbontva két - csak egy többértékű függőséget tartalmazó relációra, (Tanár, Tanfolyam) és (Tanár, Helyszín), a redundancia megszüntetésével információt is vesztünk. A felbontás után már nem tudjuk, hogy a tanár melyik tantárgyát oktatja az adott helyszínen. Például Nagy Éva adatbázis I. vagy adatbázis II. tanfolyamot tarte Pécsett. Az eredeti relációt három relációra felbontva kapjuk meg az ötödik normál formát. Az eredményül kapott három reláció összekapcsolásával előállítható az eredeti reláció, de bármelyik két reláció összekapcsolása még nem elegendő. Az ötödik normál formának megfelelő felbontás eredményeképpen a tárolandó adatmennyiség megnövekszik, a reláció három táblára bontásával. Általában célszerűbb egy újabb oszlop bevezetésével csak két táblára bontani a relációt.
Tanfolyamok Tanfolyam_tanár ID Tanfolyam Helyszín Tanár ID 1 Adatbázis I. Szeged
Nagy Éva
1
2 Adatbázis I. Győr
Kiss Pál
2
3 Adatbázis II. Pécs
Nagy Éva
3
4 Adatbázis I. Pécs
Kiss Pál
4
Természetesen más felbontást is választhattunk volna, például a tanár-tanfolyam párokat is elláthattuk volna egy azonosítóval. A normál formák tárgyalása végén megjegyezzük, hogy a harmadik normál formáig mindenféleképpen érdemes normalizálni a relációkat. Ez a redundanciák nagy részét kiszűri. Azok az esetek, melyekben a negyedik illetve az ötödik normál formák alkalmazására van szükség, ritkábban fordulnak elő. Az ötödik normál forma esetén a redundancia megszüntetése nagyobb tároló terület felhasználásával lehetséges csak. Így általában az adatbázis tervezője döntheti el, hogy az ötödik normál formát és a nagyobb adatbázist vagy a redundanciát és a komplikáltabb frissítési, módosítási algoritmusokat választja. 3.6. Fizikai tervezés A relációs adatbázisok esetében a logikai tervezés során a relációk már elnyerhetik végleges alakjukat, melyeket egyszerűen leképezhetünk az adatbáziskezelőben. A fizikai tervezés során inkább arra koncentrálunk, hogy a logikai szerkezet mennyire felel meg a hatékony végrehajtás feltételeinek, illetve milyen indexeket rendeljünk az egyes relációkhoz. A relációkon végrehajtott művelet együttest tranzakciónak nevezzük és általában a tranzakciók gyors végrehajtását kívánjuk elérni. A fizikai tervezés során előfordulhat, hogy a relációkba szándékosan redundanciákat építünk a hatékonyabb tranzakció kezelés érdekében. Ez visszalépésnek tünhet a logikai tervezés során követett következetes redundancia megszüntető tevékenységünkhöz képest. A lényeges különbség viszont az, hogy itt a redundancia ellenőrzött módon kerül be a relációba, nem csak véletlenül maradt ott a hiányos tervezés miatt. Gyakran előfordul például az, hogy a sűrűn együtt szükséges adatokat egy relációban tároljuk a lehető leggyorsabb visszakeresés érdekében. 3.6.1. Indexek fogalma és felépítése A relációkban tárolt információk visszakeresését az indexek nagymértékben meggyorsíthatják, így a tervezés során nagy hangsúlyt kell fektetni a helyes indexek kiválasztására, szem előtt tartva azt is, hogy az indexek számának növelésével az adatok beviteléhez illetve módosításához szükséges idő megnövekszik az indexek frissítése miatt. A relációkhoz kapcsolt indexek segítségével az index kulcs ismeretében közvetlenül megkaphatjuk a kulcsot tartalmazó sor fizikai helyét az adatbázisban. Az indexek képzésére két módszer terjedt el, a hash kódok és a bináris fák.
A hash kódokat manapság már csak kevés adatbáziskezelő használja. Ennek a kódolási technikának az a lényege, hogy egy számítási algoritmus alapján magából az index kulcsból alakul ki a hash kód, mely alapján egy táblázatból kiolvasható a keresett értéket tartalmazó sor fizikai címe. A hash kód számítási algoritmusa nem mindig ad különböző értékeket az index kulcsokra. Ez abból is következik, hogy csak véges hosszúságú hash táblát tudunk kezelni. A hash kód ütközés kezelésére általában az azonos kódot adó kulcsokat összeláncolják egy listában. A láncok növekedésével természetesen a reláció sorainak eléréséhez szükséges idő is növekedik. A hash kód alapján történő visszakeresés nagyon hatékony. Sajnos azonban, több hátránya is van. A visszakeresés csak akkor lehetséges, ha a teljes index kulcs ismert. Az index kulcsnak egyben a reláció kulcsnak is kell lennie, hogy minél kevesebb ütközés legyen a hash táblában. Az index kulcs értékéből valamilyen matematikai művelettel képezik a hash kód értékét.
3.21 ábra Hash kódok Ma már szinte kizárólag a bináris fákat alkalmazzák a relációs adatbázisokban. Ennél a módszernél a bináris keresést alkalmazzuk. Ehhez az index kulcsokat növekvő vagy csökkenő sorrendbe kell rendezni. A fa szerkezetet azért használják, mert nagy adatbázisok esetén az összes index kulcs nem tartható egyidőben a memóriában. A fa gyökere és csomópontjai nem tartalmazzák az index kulcshoz tartozó sor fizikai helyét, hanem csak a fa levelei. A keresés mindig a gyökértől kezdődik, a megfelelő ág felé folytatódik, és akkor ér véget, ha egy levélhez érünk. Ha a levélben tárolt index kulcs azonos a keresettel, akkor megtaláltuk a keresett értéket, ellenkező esetben sikertelen volt a keresés.
3.22 ábra Bináris fa index A bináris fák felépítésénél arra törekszenek, hogy a fa valamennyi ága azonos hosszúságú legyen. Az ily módon felépített fát kiegyensúlyozott fának hívják. A kiegyensúlyozott fákban találhatjuk meg a lehető legkevesebb összehasonlítással a keresett elemet. A gyakorlati megoldásokban a hatékonyság kedvéért a csomópontokban nem csak egy index kulcs értéket tárolnak, hanem a háttértár tárolási egység (blokk) méretének megfelelő számút.
A bináris fák segítségével egy konkrét index kulcsot vagy az index kulcsok egy tartományát kereshetjük meg. A reláció sorait az index kulcs szerinti növekvő vagy csökkenő sorrendben is végigjárhatjuk, ami a hash kód esetén nem lehetséges.
4. Relációs algebra műveletei A relációkkal kapcsolatban a matematika egy külön ága fejlődött ki. A matematikusok műveleteket definiáltak a relációkra és a halmaz műveleteket is alkalmazták a relációkra. Röviden ismertetjük ezeket a műveleteket. 4.1. Szelekció A szelekció művelete során egy relációból csak egy adott feltételt kielégítő sorokat őrizzük meg az eredmény relációban.
1.1 ábra Szelekció, horizontális megszorítás 4.2.Projekció A projekció során egy reláció oszlopai közül csak bizonyosakat őrzünk meg az eredmény relációban.
1.2 ábra Projekció, vertikális megszorítás
4.3.Descartes szorzat A Descartes szorzat két reláció sorait minden kombinációban egymás mellé teszi az eredmény relációban.
1.3 ábra Descartes szorzat 4.4. Összekapcsolás Az összekapcsolás művelete két vagy több relációt kapcsol össze egy-egy attributum érték összehasonlításával. Az összekapcsolás leggyakoribb esete amikor az attributumok egyezését vizsgáljuk, ezt egyen összekapcsolásnak nevezzük. Ez egy speciális szorzás mely a következő műveletsorral írható le 1. Vegyük az első reláció egy sorát 2. Az összekapcsolási feltételt vizsgáljuk meg a második táblázat összes sorára, ha igaz, adjuk mindkét reláció sorát az eredményhez 3. Folytassuk az 1. ponttal amig van még sor az első relációban Az összekapcsolás eredmény relációjában az első reláció csak azon sorai szerepelnek, melyekre található a feltételt kielégítő sor a második relációban. Gyakran arra van szükség, hogy az első reláció valamennyi sora szerepeljen legalább egyszer az eredmény relációban. Ezt a fajta összekapcsolást külső összekapcsolásnak nevezzük. 4.5. Halmaz műveletek A halmazokkal kapcsolatos alapvető műveleteket, unió metszet, különbség, a relációkra is értelmezzük. Minden értelmezett halmazművelethez legalább két operandus szükséges, a különbség esetében több sem lehet. A halmaz műveletek csak azonos szerkezetű relációk között hajthatók végre, ez alatt azt értjük, hogy a műveletbe bevont két reláció oszlopainak meg kell egyeznie az elnevezésben és a tárolt adat tipusában is. A relációkra általában a komplemens képzés nem értelmezhető.
4.5.1 Unió Az unió művelete azonos szerkezetű két vagy több reláció között végezhető el. Az eredmény reláció tartalmazza azokat a sorokat, melyek a műveletbe bevont relációk közül legalább egyben szerepelnek. Ha ugyanaz a sor az egyesítendő relációk közül többen is szerepelne, akkor is csak egyszer szerepel az eredmény relációban.
1.4 ábra Az unió művelet 4.5.2. Metszet A metszet művelete azonos szerkezetű két vagy több reláció között végezhető el. Az eredmény reláció csak azokat a sorokat tartalmazza, melyek a műveletbe bevont relációk közül mindegyikben szerepelnek.
1.5 ábra A metszet művelet
4.5.3. Különbség A különbség művelete azonos szerkezetű két reláció között végezhető el. Az eredmény reláció csak azokat a sorokat tartalmazza, melyek a első relációban megtalálhatóak, de a másodikban nem.
1.6 ábra A különbség művelet
5. Az SQL lekérdező nyelv Az SQL a strukturált lekérdező nyelv (Structured Query Language) rövidítése, melyet az IBM dolgozott ki a DB2 relációs adatbáziskezelőjéhez. Ma már a relációs adatbáziskezelők szabványosított nyelve, bár több dialektusa, bővítése alakult ki. 5.1 Az SQL szerepe, tulajdonságai Az SQL egy szabványosított lekérdező nyelv, melyet több relációs adatbáziskezelő ismer, különböző operációs rendszeri környezetben. Ennek óriási jelentősége van az adatbázis alkalmazások fejlesztőinek körében, mert így az alkalmazások a különböző operációs rendszerek és adatbáziskezelők között módosítás nélkül vagy csekély módosítással átvihetők. Az SQL nem algoritmikus nyelv, nem tartalmaz algoritmus szerkezeteket (elágazás, ciklus stb.). Az SQL halmaz orientált nyelv, mely a relációkon dolgozik. A halmaz orientáltság azt jelenti, hogy nem kell definiálni a művelet végrehajtásának lépéseit, hanem a feladat nem eljárás szerű megfogalmazását kell megadni, melyek a reláció vagy relációk kiválasztott sorain hajtódnak végre. A művelet végrehajtásához optimális megoldás megtalálása a nyelvi processzor feladata, nem a programozóé. Például annak eldöntése, hogy egy adott visszakeresésben alkalmazhatók-e indexek, vannak-e indexek vagy építsen-e fel új indexet, a nyelvi processzor feladata. Az SQL nem rekurzív nyelv. Az SQL nyelvnek két felhasználási lehetősége van:
· ·
önálló SQL, vagy 4. generációs eszközbe építve beágyazott SQL
Az SQL nyelv önálló felhasználása esetén csak a nyelv utasításai állnak rendelkezésre. Ennek alkalmazására főként akkor kerülhet sor, ha nincs megfelelő alkalmazás az adott feladat elvégzésére, illetve az alkalmazások fejlesztői használják a negyedik generációs nyelvekbe építve. Ilyen eszközök a jelentés készítő, az űrlap készítő vagy menü készítő lehet. A beágyazott SQL esetén egy harmadik generációs algoritmikus nyelvbe (C, PL/SQL, Pascal, FORTRAN, stb.) ágyazva alkalmazzuk az SQL nyelv elemeit. Ebben az esetben az algoritmikus feladatokat a harmadik generációs nyelvre, az adatbázissal kapcsolatos műveleteket pedig az SQL-re bízhatjuk. A beágyazott SQL alkalmazását könyvünkben nem tárgyaljuk. Az SQL a következő elemekre osztható · · · ·
adatdefiníciós nyelv adatmanipulációs nyelv lekérdező nyelv adatvezérlő nyelv
A fejezet további részében ezekkel a részekkel és utasításaikkal ismerkedünk meg. Az utasítások ismertetésénél használt példákban az előzőekben tervezett iskolai adatbázist használjuk. A minta adatbázis tartalma a következő relációkat és adatokat tartalmazza. Az attribútumok neveiben szándékosan nem szerepelnek az ékezetes karakterek, mert ezeket általában nem fogadják el a rendszerek. A könnyebb érthetőség kedvéért a mintapéldában az órarend relációt használjuk az egyszerűbb SQL műveletek érdekében. Minta adatok
A parancsok ismertetésénél nagy betűvel írjuk az SQL parancsokon belüli fix szöveget, kisbetűvel pedig a felhasználó által megadható részeket. Szögletes zárójelbe tesszük a parancsok elhagyható részeit. A parancsok általános alakjába írt ... (három pont) az előző rész ismételhetőségére utal. A | (függőleges vonal) jelet az egymást kizáró paraméterek közé tesszük, ezek közül csak egy adható meg. A mintapéldákban az olvashatóság kedvéért általában több sorra bontottuk az SQL utasításokat, de azok egy sorban vagy másféle tördeléssel is leírhatók. A tárgyalás során nem csak a szabványos SQL parancsokat, hanem az ORACLE nyelvjárását ismertetjük, a teljesség igénye nélkül. 5.2 Az adatdefiniciós nyelv Az adatdefiniciós nyelv segítségével hozhatjuk létre illetve szüntethetjük meg a relációkat, az indexeket illetve a nézet táblázatokat. A nézet táblázat az adatbázisban fizikailag nem létező relációs műveletek (szelekció, projekció, összekapcsolás, halmazműveletek) segítségével létrehozott táblázat, mely a relációkhoz hasonlóan kezelhető. A relációk létrehozására a CREATE TABLE SQL utasítás szolgál, melynek általános alakja a következő:
CREATE TABLE reláció_név (attribútum_név adattípus [(szélesség)] [CONSTRAINT megszorítás_név] [oszlop_megszorítás], attribútum_név adattípus [(szélesség)] [CONSTRAINT megszorítás_név] [oszlop_megszorítás], ... ) [CONSTRAINT megszorítás_név] [tábla_megszorítás]; A szélesség megadása el is maradhat. A reláció név és általában a nevek megadására a következő szabályok érvényesek: · · ·
a névben csak az angol ABC betűi, a számjegyek és az _, #, $ karakterek szerepelhetnek a névnek betűvel kell kezdődnie a neveknek hatáskörükön belül egyedinek kell lennie (például nem lehet egy adatbázisban két azonos nevű reláció, egy relációban két azonos nevű attribútum, stb.)
A nevek hossza korlátozott, az Oracle-ben például 30. Az SQL az azonosítókban és a parancs szavakban általában nem tesz különbséget a kis és nagybetűk között. Az attribútumokra megadható adattípusok köre adatbáziskezelőnként változhat, a következők a legtöbb relációs adatbáziskezelőben használhatók: ·
·
·
· ·
·
·
CHAR [(hossz)] megadott maximális hosszúságú karakterlánc, csak a karakterláncnak megfelelő hosszúságú területet foglalja el, szinonimája a VARCHAR. Maximum 255 karakterig. NUMBER [(szélesség, tizedes)] valós szám megadása, a szélesség mellett a tizedespont utáni jegyek száma is megadható, hasonlóan használható a FLOAT INTEGER egész típusú érték megadása, hozzá hasonló, de számábrázolási tartományában eltérő típus még a SMALLINT, szinonimája a DECIMAL DATE dátum megadása RAW a karakterhez hasonló típus, de az adatok értelmezésére nincs semmilyen feltételezés, így segítségükkel tetszőleges bináris adatokat tárolhatunk, például ábrákat is. Maximális hossza 255. LONG Maximum 64 KByte hosszú szöveg. Relációnként csak egy ilyen lehet, és csak korlátozott helyeken használható LONGRAW Mint a RAW, de 64 KByte hosszú adat.
Az attribútum típusát megadva, a reláció azon oszlopába más típusú adat nem vihető be, erről az adatbáziskezelő gondoskodik illetve szükség esetén figyelmeztet. Az opcionális oszlop megszorítás a következőket tartalmazhatja. ·
NULL az attribútum definíciójában arra utal, hogy az adat megadása nem kötelző, ez az alapértelmezés ezért a legritkább esetben írják ki.
· · · · ·
NOT NULL az attribútum definíciójában arra utal, hogy az adat megadása kötelező, azaz nem vihető be olyan sor a relációban, ahol az így definiált adat nincs kitöltve. PRIMARY KEY ez az oszlop a tábla elsődleges kulcsa. UNIQUE ez az oszlop a tábla kulcsa. CHECK(feltétel) csak feltételt kielégítő értékek kerülhetnek be az oszlopba. [FOREIGN KEY] REFERENCES tábla [ (oszlop) ], ez az oszlop külső kulcs.
A tábla megszorításban több oszlopra vonatkozó korlátozásokat adhatunk meg. · · · ·
PRIMARY KEY(oszlop1[, oszlop2, ...]) ezek az oszlopok együtt alkotják az elsődleges kulcsot. UNIQUE(oszlop1[, osylop2, ...]) ezek az oszlopok együtt kulcsot alkotnak. CHECK(feltétel) csak feltételt kielégítő sorok kerülhetnek be a táblába. FOREIGN KEY (oszlop1[, oszlop2, ...]) REFERENCES tábla(oszlop1[, oszlop2, ...]), az oszlopok külső kulcsot alkotnak a megadott tábla oszlopaihoz.
A megszorításokhoz nevet is rendelhetünk, mely hasznos lehet a megszorítás módosítása, törlése esetén (ALTER TABLE) A minta adatbázisban található relációk létrehozása a következő utasításokkal történhet: CREATE TABLE Diakok (Diak_azonosito NUMERIC (4) PRIMARY KEY, Nev CHAR (30) NOT NULL, Cim CHAR (40) NOT NULL, Telefon CHAR (15) Osztaly CHAR (3) NOT NULL); CREATE TABLE Tanarok (Tanar_azonosito NUMERIC (4) PRIMARY KEY, Nev CHAR (30) NOT NULL, Cim CHAR (40) NOT NULL, Telefon CHAR (15)); CREATE TABLE Orarend (Tanar_azonosito NUMERIC (4) NOT NULL REFERENCES Tanarok(Tanar_azonosito), Tantargy CHAR (20) NOT NULL, Idopont NUMERIC (2), NOT NULL, Osztaly CHAR (3) NOT NULL), Terem NUMERIC (3) NOT NULL) PRIMARY KEY(Tanar_azonosito, Idopont) UNIQUE(Osztaly, Idopont); CREATE TABLE Osztalyzatok (Diak_azonosito NUMERIC (4) NOT NULL REFERENCES Diakok(Diak_azonosito), Tantargy CHAR (20) NOT NULL, Datum DATE NOT NULL, Osztalyzat NUMERIC (1) VALID(BETWEEN 1 AND 5));
CREATE TABLE Hianyzasok (Diak_azonosito NUMERIC (4) NOT NULL REFERENCES Diakok(Diak_azonosito), Datumtol DATE NOT NULL, Datumig DATE, Igazolt CHAR (1)) PRIMARY KEY (Diak_azonosito, Datumtol); A CREATE TABLE utasítással létrehozott táblázatok definícióját csak korlátozottan módosíthatjuk, újabb attribútumot adhatunk a relációhoz vagy egy attribútum szélességét megnövelhetjük. Egy attribútum szélességének csökkentésére illetve törlésére nincs közvetlen mód. Ez csak úgy érhető el, hogy a módosításoknak megfelelő üres relációt hozunk létre, amibe a szükséges adatokat átmásoljuk, majd az eredeti relációt töröljük. A reláció újabb attribútummal való bővítésére az alábbi parancs szolgál: ALTER TABLE reláció_név ADD attribútum_név adattipus [(szélesség)]; Az új attribútum a reláció utolsó oszlopa lesz. Ebben az esetben a NOT NULL módosító nem adható meg. Az attribútum értéke a már meglevő sorokban NULL (definiálatlan) lesz. Az SQL nyelv megkülönbözteti a nulla numerikus értéket és a NULL, még nem definiált értéket. Például a diákok adatai közé a születési évet felvehetjük a következő paranccsal: ALTER TABLE Diakok ADD szul_ev INT (4); Egy reláció attribútumának szélességét meg lehet növelni az ALTER TABLE paranccsal. ALTER TABLE reláció_név MODIFY attribútum_név adattipus (új_szélesség) [NOT NULL]; Abban az esetben, ha az attribútum csak NULL értékeket tartalmaz, akkor lehetőség van az adattípus módosítására és a szélesség csökkentésére is. Például a név attribútum szélességének megnövelése a következő paranccsal történhet. ALTER TABLE Diakok MODIFY nev CHAR (40) NOT NULL; Teljes relációk törlésére is lehetőséget biztosít az SQL nyelv a DROP TABLE reláció_név; utasítással. Ezután a relációban tárolt valamennyi adat és a reláció definíciója is törlődik. A diákok személyi adatait tartalmazó reláció törlése a következő paranccsal lehetséges: DROP TABLE Diakok; A nézettáblázat az adatbázisban létező reláción vagy relációkon végrehajtott művelet eredményét tartalmazó olyan új táblázat, amely mögött a valóságban nem áll megfelelő táblázat. Nézettáblát a
CREATE VIEW nézettábla_név [alias_név, alias_név ... AS lekérdezés; paranccsal hozhatunk létre. A lekérdező utasítás formáit a lekérdező nyelv tárgyalása során részletezzük. A 3/b osztály névsorát tartalmazó nézettáblázatot hoz létre a következő parancs. CREATE VIEW 3b AS SELECT * FROM Diakok WHERE osztaly = '3/b'; Akár több táblázatból is vehetünk oszlopokat a nézettáblába. A nézettábla segítségével a három relációra felbontott órarend relációt összevonhatjuk egy táblázatba a kényelmesebb kezelés érdekében. CREATE VIEW orarend FROM tanr-to_id, tantargy-osztaly, ora AS SELECT tanar_azonosito, tantargy, osztaly, idopont, terem FROM Tanar-to_id A, Tantargy-osztaly B, Ora C WHERE C.to_id = B.to_id AND C.to_id = A.to_id; Az itt szereplő lekérdező (SELECT) utasítás két összekapcsolás műveletet is tartalmaz, melyeket csak később magyarázunk meg. A nézettáblák megszüntetése a relációkhoz hasonlóan a DROP VIEW nézettábla_név; paranccsal lehetséges. A relációkhoz indexeket is rendelhetünk, melyek helyes megválasztása esetén a lekérdezések felgyorsíthatók. Az indexek létrehozására a következő utasítás szolgál: CREATE [UNIQUE] INDEX index_név ON reláció (attribútum, attribútum, ...); Az index létrehozásánál a UNIQUE módosító megadásával a reláció valamennyi sorában különbözőnek kell lennie az index kulcsnak. Általában a reláció kulcsok esetén használható csak (index kulcs = reláció kulcs). Hozzunk létre egy indexet a Diákok reláció Diák_azonosító attribútuma alapján: CREATE UNIQUE INDEX Diak ON Diakok (Diak_azonosito); A parancsban megadott UNIQUE következtében a reláció minden sorában különböző Diák_azonosítónak kell szerepelnie, illetve már létező érték bevitele hibát eredményez. Ez tulajdonképpen a célunk is, mert ez a reláció kulcs. Az index létrehozása után ugyanaz a Diák_azonosító nem vihető be mégegyszer, ilyen esetben hibaüzenetet kapunk.
Az indexek megszüntetése a DROP INDEX index_név ON [reláció] parancs segítségével történhet. Ezen a két parancson kívül nincs nincs szükség további az indexek kezelésére szolgáló parancsra. A relációkhoz kapcsolódó indexek használatáról az adatbáziskezelő optimalizáló algoritmusok alapján dönt. Az indexeket létrehozásuktól a megszüntetésükig az adatbáziskezelő automatikusan frissíti, a módosításoknak megfelelően. Figyelem, az indexek számának növelésével a relációkon végrehajtott módosítások, törlések végrehajtási ideje növekszik. 5.3 Az adatmanipuláiciós nyelv Az SQL adatmanipulációs része biztosítja a relációk feltöltését, az attribútumok módosítását és a sorok törlését. A relációk feltöltésére az INSERT SQL parancs szolgál, melynek általános alakja a következő: INSERT INTO reláció [(attribútum_név, attribútum_név, ...)] VALUES (érték, érték, ...); Egy utasítás segítségével egy sor adható meg az adott relációhoz. Az attribútum nevek megadása csak akkor kötelező, ha nem minden attribútumhoz rendelünk értéket, vagy az attribútumok értékét nem a definiálás sorrendjében adjuk meg. A NOT NULL megjelöléssel definiált attribútumok megadása kötelező az INSERT parancsnál, ellenkező esetben hibaüzenetet kapunk. Az attribútumok és a VALUES után álló értékek sorrendjének és típusának meg kell felelnie egymásnak. A definiálatlan értékekre a NULL érték is beállítható, mely numerikus értékekre sem azonos a nullával. Adjunk egy új sort a Diákok relációhoz INSERT INTO Diakok (Diak_azonosito, Nev, Cim, Osztaly) VALUES (435, 'Nagy Istvan', 'Budapest O utca 3.', '3.b'); A telefon attribútum értékét nem adtuk meg, így értéke NULL. A reláció létrehozásánál NOT NULL jelzéssel ellátott attribútumokat kötelező minden újabb sor megadásánál kitölteni. Ellenkező esetben hibaüzenetet kapunk. De bevihetjük az előző adatokat az alábbi formában is: INSERT INTO Diakok VALUES (435, 'Nagy Istvan', 'Budapest Ó utca 3.', NULL, '3.b'); Az INSERT utasítás lehetőséget biztosít arra is, hogy a relációt egy másik relációból átvett értékekkel töltsük fel. Ekkor az értékek megadása helyén egy lekérdező utasítás állhat. A lekérdezés eredményei kerülnek be a megadott relációba, egyszerre akár több sor is. Itt is igaz, hogy a lekérdezés eredmény relációjának attribútumai sorrendjének és típusának meg kell felelnie a feltöltendő reláció definíciójának.
INSERT INTO reláció_név [(attribútum_név, attribútum_név, ...)] lekérdező_utasítás; A lekérdező utasítások formájával a következő alpontban foglalkozunk, de már itt is bemutatunk egy példát. Töltsük át a diákok adatai közül a 3/a osztályba járókét egy külön relációba: INSERT INTO 3a SELECT * FROM diakok WHERE osztaly = '3/a'; A 3a relációt természetesen először létre kell hozni a diákok relációval egyező szerkezettel, hogy a fenti parancs működjön. A diákok reláció tartalma nem változik. A relációkban szereplő mezők tartalmát az UPDATE utasítással módosíthatjuk. UPDATE reláció_név SET attribútum_név = érték, attribútum_név = érték, ... [WHERE feltétel]; Az UPDATE utasítás segítségével egyidőben a relációk több sorát is módosíthatjuk. A SET után adhatjuk meg a módosítandó attribútumot és értékeit. A WHERE után egy feltétel adható meg, az utasítás csak a reláció azon sorain dolgozik, melyekre a feltétel értéke igaz. A WHERE rész el is maradhat, ekkor a reláció összes sorára vonatkozik az UPDATE parancs. A feltételek alakjára részletesen a lekérdező nyelv ismertetésénél térünk ki, de a szokásos összehasonlító operátorok itt is használhatók. Például az Osztályzatok relációban az összes osztályzatot egyesre (vagy ötösre?) állíthatjuk egy UPDATE paranccsal: UPDATE Osztalyzatok SET Osztalyzat = 1; De a következő parancs már csak egy diák telefonszámát módosítja (mivel a feltétel csak egy adott kulcs érték esetén lehet igaz): UPDATE diak SET telefon = '463-1234' WHERE diak_azonosito = 5; Az egyenlőség jobb oldalán a reláció attribútumaiból álló kifejezés is állhat. Ilyenkor az aktuális sor tartalma alapján értékelődik ki a kifejezés. Az értékek módosítása esetén a feltételben egy lekérdező utasítás is szerepelhet, melynek segítségével egy másik relációból vett értékeket használhatunk. UPDATE reláció SET attribútum = (lekérdező utasítás) [WHERE feltétel]; Ez a formája az UPDATE utasításnak lehetővé teszi, hogy egy másik reláció adataiból frissítsünk egy táblát. A WHERE utáni feltétel is tartalmazhat zárójelek közé tett lekérdező utasítást. Az újabb SQL implementációk lehetővé teszik, hogy az UPDATE utasítás táblák közötti összekapcsolást is tartalmazzon, de továbbra is csak egy tábla tartalmát lehet aktualizálni egy
UPDATE utasítással. Ez azonban még nem része az SQL szabványnak, így szintaktikája eltérő lehet az egyes adatbáziskezelőkben. A relációk sorait törölhetjük a DELETE parancs segítségével. DELETE FROM reláció_név [WHERE feltétel]; A feltételben az UPDATE parancshoz hasonlóan egy zárójelek közé tett lekérdező utasítás is megadható. Töröljük ki a Diákok közül a 1234 azonosítójút. DELETE FROM Diakok WHERE Diak_azonosito = 1234; A kérdés csak az, hogy megtehetjük-e ezt az adatbázis konzisztenciájának elvesztése nélkül? Ha az Orarend vagy a Hianyzasok relációban szerepel a törölt diák azonosítója, akkor ezek inkonzistenssé teszik az adatbázisunkat. Helyesebb az alábbi három törlő parancsot kiadni, ha semmi szin alatt sem szeretnénk elveszteni az adatbázis konzisztenciáját. DELETE FROM Hianyzasok WHERE Diak_azonosito = 1234; DELETE FROM Osztalyzatok WHERE Diak_azonosito = 1234; DELETE FROM Diakok WHERE Diak_azonosito = 1234; A WHERE alparancs elmaradása esetén a reláció összes sora törlődik. Tételezzük fel, hogy az iskolából kicsapott diákok nevét egy kicsapottak relációban őrizzük a kics oszlopban. Ennek alapján a diákok relációból a következő paranccsal törölhetjük őket: DELETE FROM Diakok WHERE nev IN (SELECT kics FROM kicsapottak); 5.4 A lekérdező nyelv A lekérdező nyelv egyetlen utasításból áll, mely számos alparancsot tartalmazhat, és a lekérdező utasítások többszörös mélységben egymásba ágyazhatók. A SELECT utasítás általános alakjának megadása helyett részletesen áttekintjük az egyes tipikus lekérdezési utasításokat, az egyszerűektől a komplikáltakig. Figyelem, a szelekció művelete és a SELECT utasítás csak nevében hasonló, egymásnak nem felelnek meg. Először tekintsük át az egy relációra vonatkozó lekérdezéseket. A projekció műveletét a következő utasítással valósíthatjuk meg: SELECT [DISTINCT] attribútum_név, attribútum_név, ... FROM reláció_név; A megadott reláció felsorolt attribútumai jelennek meg az utasítás hatására soronként. A DISTINCT módosító megadása esetén csak az egymástól különböző sorok láthatók.Például a Diakok reláció Diak_azonosito és Nev attribútumainak lekérdezése a SELECT Diak_azonosito, nev FROM Diakok; paranccsal történhet.
A különböző tanár-tantárgy párosítások lekérdezése az Orarend relációból a következő paranccsal történhet: SELECT DISTINCT Tanar_azonosito, Tantagy FROM Orarend; A szelekció művelet megvalósítása esetén a SELECT utasítást egy feltétellel egészítjük ki: SELECT attribútum_név, attribútum_név, ... FROM reláció_név WHERE feltétel; Ha az attribútum nevek helyett csak "*"-ot adunk meg, akkor az eredményben a reláció valamennyi attribútuma szerepelni fog: SELECT * FROM Diakok WHERE osztaly = '3/b'; Megadott attribútumok esetén a projekció és a szelekció művelete összevonható egy utasítással: SELECT idopont, tantargy FROM orarend WHERE osztaly = '3/b'; A keresési feltételben szerepelhetnek összehasonlító operátorok, melyek numerikus, karakteres és dátum típusú adatok esetén is használhatóak. Összehasonlító operátorok Operátor Értelmezés =
egyenlő
!= <> ^= nem egyenlő >
nagyobb
>=
nagyobb egyenlő
"<"
kisebb
"<="
kisebb egyenlő
Az összehasonlító operátorok segítségével attribútumokat és konstansokat hasonlíthatunk össze. A szöveg és dátum konstansokat idézőjelek között kell megadni. Az alapértelmezés szerinti dátum formátum nap-hónap-év. A hónap a hónap nevének három betűs angol rövidítése, az év pedig évezred és évszázad nélkül értendő. Az SQL tartalmaz olyan összehasonlító operátorokat is, melyek nem egy adott értékkel, hanem az értékek egy halmazával történő összehasonlítást eredményeznek. Összehasonlító operátorok halmazokra Operátor Értelmezés BETWEEN x AND y adott értékek közé esik IN (a, b, c, ...)
az értékek között található
LIKE minta
hasonlít a mintára
Az IN esetén egy halmazt adhatunk az elemek felsorolásával. A LIKE operátort karakteres mezők összehasonlítására alkalmazhatjuk. Két speciális karakter adható meg a mintában, a % jel tetszőleges hosszúságú karakter sorozatot helyettesít, az _ aláhúzás karakter pedig egy tetszőleges karaktert. Például: Szöveges minta megadása Operátor Értelmezés LIKE 'a%' minden 'a' betűvel kezdődő LIKE 'x_'
minden 'x'-el kezdődő kétbetűs
LIKE '%a%' minden 'a' betűt tartalmazó LIKE '_a%x' második betű 'a' és 'x'-re végződő Az attribútumokra használható még egy speciális összehasonlító operátor, az IS NULL, melyek segítségével eldönthetjük, hogy a mező ki van-e töltve. Több keresési feltételt is összekapcsolhatunk a logikai operátorokkal, illetve segítségükkel a halmaz műveleteket valósíthatjuk meg. Logikai operátorok Operátor Értelmezés NOT Logikai tagadás AND
Logikai és
OR
Logikai vagy
Az összehasonlító operátorok precedenciája (végrehajtási sorrendje) csökkenő sorrendben: 1. 2. 3. 4.
=, !=, <>, ^=, >, >=, <, <= NOT AND OR
Nézzünk néhány példát a lekérdezésekre. A 3.a osztályba járó diákok adatai: SELECT * FROM Diakok WHERE Osztaly = '3/a'; A matematikát tanító tanárok azonosítói (DISTINCT itt is használható!): SELECT DISTINCT Tanar_azonosito FROM Orarend WHERE Tantargy = 'matematika'; A 'C' betűvel kezdődő nevű diákok: SELECT Nev, Osztaly FROM Diakok WHERE nev = 'C%'; A 3/a-ba járó diákok, akiknek nincs otthon telefonjuk: SELECT Nev FORM Diakok WHERE Osztaly = '3.a' AND Telefon IS NULL;
Termek, ahol matematika vagy informatika órát tartanak: SELECT Terem FROM Orarend WHERE Tantargy = 'matematika' OR tantargy = 'informatika'; vagy ugyanez az IN felhasználásával: SELECT Terem FROM Orarend WHERE Tantargy IN ('matematika', 'informatika'); Matematikából hármas és ötös közötti osztályzatot szerzett diákok: SELECT Diak_azonosito FROM Osztalyzatok WHERE Osztalyzat BETWEEN 3 AND 5 AND tantargy = 'matematika'; Telefonnal rendelkező diákok: SELECT Diak_azonosito FROM Diakok WHERE NOT IS NULL Telefon; Az eddigi lekérdezések eredményei a sorok tárolt sorrendjében kerültek kiírásra. Az SQL lehetőséget biztosít a lekérdezés eredménysorainak rendezésére az ORDER BY alparancs segítségével. SELECT attribútum, attribútum, ... FROM reláció [WHERE feltétel] ORDER BY attribútum [ASC|DESC], attribútum [ASC | DESC], ...; Az ORDER BY után megadott attribútumok alapján ASC esetén (ez az alapértelmezés) növekvő, vagy DESC esetén csökkenő sorrendbe rendezi az eredmény sorait. Ha több attribútumot adunk meg a rendezési feltételben, akkor a megadás sorrendje alapján történik a rendezés, azaz először az elsőnek megadott attribútum alapján rendezi sorba a sorokat, ha ez az attribútum azonos két sorra, akkor a másodikként megadott attribútum alapján, és így tovább. Karakteres attribútumoknál a rendezés a karakterek kódjai alapján történik (ASCII vagy EBCD), azaz például nevek esetén az angol ABC szerint. Például a 3/a osztály órarendje időrendi sorrendben az alábbi lekérdezéssel kapható meg: SELECT Idopont, Tantargy, Terem FROM Orarend WHERE Osztaly = '3/a' ORDER BY Idopont; Egy terem elfoglaltsága időrendben: SELECT Idopont, Tantargy, Osztály FROM Orarend WHERE Terem = 104 ORDER BY Idopont; Osztályonkénti névsor az összes diákra: SELECT Nev, Osztály FROM Diakok ORDERED BY Osztaly, Nev; A lekérdezés eredményét csoportosíthatjuk és a csoportok között is további szelekciót alkalmazhatunk a GROUP BY és HAVING alparancsokkal. SELECT attribútumok FROM reláció [WHERE feltétel] GROUP BY attribútum [HAVING csoport_feltétel];
A GROUP BY alparancs után megadott attribum azonos értékei alapján csoportosítja az SQL a lekérdezés eredményeit és a csoport feltételnek megfelelő sorok kerülnek az eredménybe. A csoportok képzésekor az eredmények között az azonos csoportba tartozó sorokból levezetett további eredmények megjelenítésére is lehetőséget biztosít az SQL. Ez a következő függvényekkel valósítható meg: Függvények halmazokra és eredményeik az alábbi adatok esetén: 1, 2, 3, 4, 1, 4, 4, NULL, 5 Függvény Értelmezés Eredmény AVG (attribútum)
átlag
ALL DISTINCT 3 3
COUNT (attribútum) nem NULL elemek száma 8
5
COUNT (*)
sorok száma NULL is
9
6
MAX (attribútum)
maximális elem
5
5
MIN (attribútum)
minimális elem
1
1
SUM (attribútum)
összeg
24
15
STDDEV(attribútum) szórás A függvények argumentuma előtt megadható a DISTINCT vagy ALL módosító. DISTINCT esetén csak a különböző értékek, ALL esetén minden érték részt vesz a számításban. Az alapértelmezés az ALL. A definiálatlan, NULL értékek nem szerepelnek a számításban. Az ötnél nagyobb létszámú osztályok: SELECT Osztály, COUNT (*) FROM Diakok GROUP BY Osztaly HAVING COUNT (*) > 5 Diákok tanulmányi átlaga matematikából: SELECT Diak_azonosito, AVG (Osztalyzat) FROM Osztalyzatok WHERE tantargy = 'matematika' GROUP BY Diak_azonosito; A függvények alkalmazhatók a GROUP BY nélkül is. Például a tanárok száma: SELECT COUNT (*) FROM Tanarok; Matematika osztályzatok száma és átlaga: SELECT COUNT (*)', AVG (Osztalyzat) FROM Osztalyzatok WHERE Tantargy = 'matematika'; A lekérdezésekben az attribútumok és a csoportokra vonatkozó függvények mellett az attribútumok aritmetikai kifejezéseit is használhatjuk. A kifejezésekben az alapműveletek használhatók, illetve zárójelek a műveletek végrehajtási sorrendjének módosítására. A karakteres adatok konkatenálására a || operátor használható. Osztályzatok kiírása fordítva, vagyis az egyes a legjobb: SELECT Diak_azonosito, 6 - Osztályzat FROM Osztalyzatok;
A kifejezésekben az alapműveletek mellett számos függvényt is használhatunk a karakteres, numerikus és dátum tipusú adatokra.
Függvény ASC (szöveg)
Karakteres függvények Magyarázat A szöveg első karakterének ASCII kódja
Példa ASC ('abc') = 65
CHR (egész)
A számnak megfelelő kódú karakter
CHR (65) = 'a'
INITCAP (szöveg)
A szavak kezdőbetűit nagybetűvé
INITCAP ('ló pál') = 'Ló Pál'
INSTR (szöveg1, szöveg2, kezdet, hányadik)
A szöveg1-ben a szöveg2 hányadik INSTR ('abcd', 'cd') = előfordulása a kezdettől. Hányadik és kezdet 3 elmaradhat
LENGTH (szöveg)
A szöveg hosszát adja
LENGTH ('abc') = 3
LOWER (szöveg)
Kisbetűssé alakítja a szöveget
LOWER ('ABC') = 'abc'
LPAD ('x', 3) = ' x' A szöveget kiegészíti balról a megadott LPAD (szöveg, hossz, karakterekkel az adott hosszig, Karaktereket LPAD ('x', 5, '12') = karakterek) '1212x' nem kötelező megadni, ekkor szóköz. LTRIM (szöveg, karakterek)
A szöveg elejéről levágja a karakterekkel egyező részt. Karaktereket nem kötelező megadni, ekkor szóköz.
LTRIM (' x') = 'x LTRIM 'KUKURIKU', 'UK' = 'RIKU'
RPAD ('x', 3) = 'x ' A szöveget kiegészíti jobbról a megadott RPAD (szöveg, hossz, karakterekkel az adott hosszig, Karaktereket RPAD ('x', 5, '12') = karakter) 'x1212' nem kötelező megadni, ekkor szóköz RTIM (szöveg, karakter)
A szöveg végéről levágja a karakterekkel egyező részt. Karaktereket nem kötelező megadni, ekkor szóköz.
LTRIM ('x ') = 'x' LTRIM ('KUKURIKU', 'UKI' = 'KUKUR'
SUBSTR (szöveg, kezdet, hossz)
A szöveg része a kezdet pozíciótól adott hosszban. Hossz nélkül a szöveg végéig
SUBSTR ('abcd', 2, 1) = 'b' SUBSTR ('abcd', 3) = 'cd'
TRANSLATE (szöveg, mit, mire)
A szövegben előforduló mit karaktereket kicseréli a mire karaktereire
TRANSLATE ('abc', 'ab', 'AB') = 'ABc')
UPPER (szöveg) Nagybetűssé alakítja a szöveget UPPER ('abc') = 'ABC' Nem minden függvény található meg minden adatbáziskezelőben, előfordulhat, hogy más névvel találja meg.
Numerikus függvények Magyarázat
Függvény
Példa
ABS (érték)
Abszolút érték)
ABS (-1) = 1
CEIL (érték))
Az értéknél nagyobb vagy egyenlő legkisebb egész)
CEIL (6.12) = 7
FLOOR (érték))
Az értéknél kisebb vagy egyenlő legnagyobb egész)
FLOOR (3.95) = 3
MOD (érték, osztó))
Osztási maradék)
MOD (8, 3) = 2
POWER (érték, kitevő))
Hatványozás)
POWER (3, 4) = 81
ROUND (érték, pontosság))
Kerekítés a megadott jegyig. Negatív ROUND (123.456, 1) = 123,5 pontosság is megadható.) ROUND (163.456,-2) = 200
SIGN (érték))
Előjel függvény)
SIGN (-3) = -1
SQRT (érték))
Négyzetgyök vonás)
SQRT (144) = 12
TRUNC (érték, Csonkítás a megadott jegyig. (Negatív TRUNC (123.456, 1) = 123.4 pontosság)) pontosság is megadható.) TRUNC (163.456,-2) = 100 Nem minden függvény található meg minden adatbáziskezelőben, előfordulhat, hogy más névvel találja meg.
Függvény
Dátum függvények Magyarázat
Példa
ADD_MONTH (dátum, n)
A dátumhoz n hónapot ad
ADD_MONTH ('10-MAY-93',2) = '10-JUL-93'
LAST_DAY (dátum)
A dátumban szereplő hónap utolsó apja
LAST_DAY ('1-JAN-93') = '31JAN-93')
MONTH_BETWEEN (dátum1, dátum2)
A két dátum közötti idő hónapokban
MONTH_BETWEEN (
NEXT_DAY (dátum, nap)
A dátum utáni első nap nevű napra eső dátum
NEXT_DAY ('10-MAY93','TUESDAY') = '11-MAY-93')
ROUND (dátum, formátum)
Dátum kerekítése a megadott formátum szerint
TO_CHAR (dátum, formátum)
Dátum megadott karakteres TO_CHAR (4-DEC-58, formátumba konvertálása 'YY.MM.DD') = '58.12.04'
TO_DATE (szöveg, formátum)
A szöveg dátummá alakítása a formátum szerint
TO_DATE ('58.12.04', 'YY.MM.DD') = 4-DEC-58
Dátum csonkítása a megadott formátum szerint Nem minden függvény található meg minden adatbáziskezelőben, előfordulhat, hogy más névvel találja meg. TRUNC (dátum, formátum)
A SELECT utasítás a relációk közötti szorzás művelet végrehajtására is alkalmas. Ehhez több reláció nevét kell megadni a FROM után. Például az összes lehetséges diák és tanár azonosító pár visszaírása: SELECT diak_azonosito, tanat_azonosito FROM Diakok, Tanarok; Ennél komplikáltabb szorzás műveletet is megfogalmazhatunk. Például a 3/a osztályban rendezendő körmérkőzéses sakk bajnokság összes lehetséges párosításának előállítása: SELECT A.nev, B.nev FROM Diakok A, Diakok B WHERE A.osztaly = '3/a' AND B.osztaly = '3/a' AND A.diak_azonosito <> B.diak_azonosito; A különböző diák azonosítókra vonatkozó feltétel megadására azért van szükség, hogy az önmagukkal alkotott párok ne kerüljenek be a lekérdezés eredményébe. A SELECT SQL utasítás segítségével eddig is sokfajta kérdésre kaptuk meg közvetlenül a választ. Az összekapcsolás segítségével még komplexebb kérdéseket oldhatunk meg egy lépésben. SELECT [reláció.]attribútum, [reláció.]attribútum, ... FROM relációk WHERE kapcsoló_attribútum operátor kapcsoló_attribútum; Az összekapcsolás esetén mindig legalább két relációval dolgozunk. Ha a két relációban azonos az attribútumok neve, akkor minősített névvel, reláció.attribútum segítségével hivatkozhatunk a kivánt elemre. A diákok neve mellé írjuk ki az osztály, tantárgy és osztályzat értéket: SELECT Nev, Osztaly, Tantargy, Osztalyzat FROM Diakok, Osztalyzatok WHERE Diakok.Diak_azonosito = Osztalyzatok.Diak_azonosito; Az előző egy egyen összekapcsolás volt, de például a 12 azonosítójú diák osztályzatainál jobbat szerzők listája is előállítható az egy reláción végrehajtott összekapcsolással: SELECT DISTINCT A.Diak_azonosito, A.Osztalyzat FROM Osztalyzatok A, Osztalyzatok WHERE B.Osztalyzat < A.Osztalyzat AND B.Diak_azonosito = 12; Ebben a lekérdezésben az 'A' és 'B' ugyanannak a relációnak az alias nevei, melyek segítségével két sort vizsgálhatunk ugyanabból a relációból. Az előző lekérdezést módosítsuk úgy, hogy a diákok neve is megjelenjen: SELECT Nev, A.Osztalyzat FROM Osztalyzatok A, Osztalyzatok B, Diakok WHERE B.Osztalyzat < A.Osztalyzat AND B.Diak_azonosito = 12; AND Diakok.Diak_azonosito = A.Diak_azonosito; Az SQL szabvány egy másik megoldást is biztosít a belső összekapcsolásra az INNER JOIN kulcsszavakkal: SELECT [reláció.]attribútum, [reláció.]attribútum, ... FROM első_tábla INNER JOIN második tábla ON első_tábla.kulcs_mező = második_tábla.kulcs_mező;
A korábbi példa, a diákok neve mellé írjuk ki az osztály, tantárgy és osztályzat értéket, így is megfogalmazható: SELECT Nev, Osztaly, Tantargy, Osztalyzat FROM Diakok INNER JOIN Osztalyzatok ON Diakok.Diak_azonosito = Osztalyzatok.Diak_azonosito; A LEFT JOIN illetve RIGHT JOIN kulcsszavakkal az úgynevezett külső összekapcsolás is megvalósítható. Ebben az esetben az egyik tábla minden sora megjelenik az eredményben, akkor is, ha nincs az összekapcsolás feltételének megfelelő sor a másik táblában. A LEFT JOIN esetén az első tábla, míg a RIGHT JOIN esetén a második tábla minden sora bekerül az eredmény táblába. A külső összekapcsolás nem fogalmazható meg a WHERE feltételben. SELECT [reláció.]attribútum, [reláció.]attribútum, ... FROM első_tábla LEFT JOIN második tábla ON első_tábla.kulcs_mező = második_tábla.kulcs_mező; Az előző példát alakítsuk át úgy, hogy azoknak a diákoknak a neve is megjelenjen a listában akiknek még nincs osztályzatuk: SELECT Nev, Osztaly, Tantargy, Osztalyzat FROM Diakok LEFT JOIN Osztalyzatok ON Diakok.Diak_azonosito = Osztalyzatok.Diak_azonosito; Az összekapcsolás művelete mellett a lekérdezések további egymásba ágyazása is lehetséges. A WHERE alparancsban az összehasonlítás egyik oldalán szerepelhet egy újabb, zárójelbe tett SELECT utasítás. A mélyebben elhelyezkedő SELECT utasítás több sort is visszaadhat a relációból. Ezeket az eseteket az ANY, ALL és EXISTS segítségével is kezelhetjük. Az ANY és ALL esetén az összehasonlítás egyik oldalára egy listát írhatunk, mely származhat egy beágyazott lekérdezésből is. A legrosszabb osztályzat matematikából: SELECT DISTINCT Osztalyzat FROM Osztalyzatok WHERE Tantargy = 'matematika' AND Osztalyzat <= ALL (SELECT Osztalyzat FROM Osztalyzatok WHERE Tantargy = 'matematika'); Ugyanezt a kérdést egyszerűbben is megfogalmazhatjuk: SELECT MIN (Osztalyzat) FROM Osztalyzatok WHERE Tantargy = 'matematika'; Az ANY és ALL használható a WHERE részben fix listákkal is, például x > ALL (12,21,8), x nagyobb, mint a lista legnagyobb eleme vagy y < ANY (5,4,7), y kisebb, mint a lista egyik eleme. Ezeket a feltételeket általában egyszerűbben is le tudjuk írni, az előző példák esetén x > 21 illetve y < 7. Az EXISTS esetén mindig egy újabb beágyazott lekérdezés következik. Ha egy beágyazott lekérdezés talál a feltételt kielégítő sort, akkor igaz értéket kapunk, különben hamisat. Például kinek nincs még osztályzata matematikából a 3/b-ben: SELECT Nev FROM Diakok D WHERE NOT EXISTS (SELECT * FROM Osztalyzatok WHERE D.Diak_azonosito = Diak_azonosito AND tantargy = 'matematika'); Beágyazott lekérdezések használatára még egy példa, kíváncsiak vagyunk Kiss János osztálytársainak a nevére: SELECT Nev FROM Diakok WHERE Osztaly = (SELECT Osztaly FROM Diakok WHERE Nev = 'Kiss János');
Az egyenlőség vizsgálat esetén a belső lekérdezés csak egy értéket adhat vissza. A relációkon értelmezett halmazműveletek bizonyos esetekben az OR, AND, NOT és IN operátorok segítségével is elvégezhetők. Az SQL ezek mellett az INTERSECT, UNION és MINUS műveleteket is biztosítja. Ezek segítségével két lekérdezést kapcsolhatunk össze a következő formában: Halmaz muveletek megvalósítása az SQL-ben Unió Metszet Különbség SELECT ... SELECT .... SELECT ... INTERSECT MINUS UNION SELECT ... SELECT ... SELECT ... A MINUS kulcsszó helyett az EXCEPT használandó néhány adatbáziskezelőben Válasszuk ki azokat a termeket, ahol a 3/b-nek vagy a 3/a-nak vannak órái: SELECT Tanterem FROM Orarend WHERE Osztaly = '3/b' UNION SELECT Tanterem FROM Orarend WHERE Osztaly = '3/a'; Ugyanez az IN operátorral rövidebb: SELECT Tanterem FROM Orarend WHERE Osztaly IN ('3/a', '3/b'); Azon osztályok, melyeknek a 101 és 102 teremben is van órája: SELECT Osztaly FROM Orarend WHERE Tanterem = 101 INTERSECT SELECT Osztaly FROM Orarend WHERE Tanterem = 102; Azon osztályok, melyeknek a 101-es teremben van, de a 102-es teremben nincs órája: SELECT Osztaly FROM Orarend WHERE Tanterem = 101 MINUS SELECT Osztaly FROM Orarend WHERE Tanterem = 102; Az utóbbi két lekérdezés is megoldható a halmazműveletek nélkül, de csak egymásba ágyazott lekérdezésekkel. 5.5 A vezérlő nyelv A SQL vezérlő nyelv több funkciót lát el, ezek közül most csak a tranzakciók kezeléséhez szükséges parancsokat ismertetjük. A többi parancsról az adatbázis adminisztrátor parancsainál beszélünk. A logikailag egybe tartozó SQL utasításokat tranzakcióknak nevezzük. Az adatbázis ellentmondás mentes tartalmának megőrzéséhez a tranzakcióknak maradéktalanul végre kell hajtódniuk. Azonban egy tranzakció megszakadása esetén is gondoskodni kell az adatbázis konzisztenciájának megőrzéséről. Erre a COMMIT és ROLLBACK parancs pár szolgál. Az ORACLE nem az eredeti relációkon dolgozik. A sikeresen végrehajtott tranzakciók után a COMMIT parancs kiadásával véglegesíthetjük a tranzakció során végrehajtott változtatásokat a relációkban. A ROLLBACK parancs segítségével pedig visszaléphetünk az előző véglegesített állapothoz. Bizonyos parancsok automatikusan COMMIT-ot eredményeznek (CREATE TABLE, QUIT stb.). De az AUTOCOMMIT rendszerváltozó beállításától függően minden parancs kiválthat egy COMMIT-ot is. SET AUTOCOMMIT ON
