Adatbáziskezelés és Adatbázis szervezés Jegyzet Fábián Zoltán 2007
Bevezetés
1
5
1.1
Adatbázis fogalma
5
1.2
Történelmi áttekintés
5
2
Miről beszélünk, azaz általános definíciók? 6 2.1
Adat, információ 6
2.2
A Shannon féle információs elmélet
2.3
Az adatok tulajdonságai 7
2.4
Az adatfeldolgozás formalizálható 7
2.5
Az adatok osztályozása funkciójuk szerint 7
2.6
Az adatszerkezetek fajtái 7
2.7
Az adatok között kapcsolatok
7
2.8
Adatmodellek története röviden
8
3
Adatbáziskezelő rendszerek és feladataik
6
9
3.1
Adatbázis-kezelők elemei 9
3.2
A file-kezelő
3.3
A lekérdező nyelv
3.4
Adatbáziskezelőkben ellátott segédfeladatok
3.5
Adatbázis statisztikák
3.6
Az adatbázis adminisztrátor
3.7
A 4GL-ek
3.8
Integrált CASE eszközök 10
3.9
Az ANSI/SPARC modell 10
9 9 9
9 10
10
3.10 Logikai, fizikai szerkezet 11 3.11 A programok és az adatok függetlensége
11
3.12 Az adatbázis életciklusa 11 3.13 Objektum-orientált adatbázisok 4
12
Az adatmodellek – a logikai séma 13 4.1
Az egyed fogalma
13
4.2
A rekordtípus
4.3
A tulajdonság fogalma, Azonosítók
4.4
Kulcs
4.5
Többértékű vagy összetett tulajdonságok
4.6
A kapcsolat fogalma
4.7
A kapcsolat fokai14
4.8
A kapcsolatok fajtái
4.9
A kardinalitás
13 13
13 13
13 14
14
4.10 A belső szerkezete, belső tulajdonságai 4.11 Az egyed külső tulajdonságai 4.12 A settipus fogalma
14
14
14
4.13 A kapcsolatok ábrázolásának különféle módjai 4.14 Egyed-szupertípus, egyed-altípus 15 2
15
4.15 Aggregáció 5
Az adatmodellek és az adatbázisok 16 5.1
Az adatmodell fogalma
16
5.2
A hálós adatmodell
16
5.3
A hierarchikus adatmodell16
5.4
A relációs adatmodell
16
5.5
Reláció definíciója
17
5.6
Reláció, tábla, adatbázis 17
5.7
Egyedtípus és reláció kapcsolata
6
17
A relációs adatbázis belső szerkezete 6.1
19
A funkcionális függőség - Adatok közötti funkcionális kapcsolat
19
6.1.1
Teljes funkcionális függőség ............................................................................................................... 19
6.1.2
Adatok közötti többértékű függőség ................................................................................................... 20
6.2
Reláció kulcs fogalma
6.3
A külső kulcs
20
21
6.3.1
Külső kulcs ugyanabban a táblában (Rekurzív kapcsolat) .................................................................. 21
6.3.2
Külső kulcs párhuzamos kapcsolatban ................................................................................................ 21
6.3.3
Külső kulcs egy-egy típusú kapcsolatban............................................................................................ 21
6.3.4
Generátorhalmaz: ................................................................................................................................ 21
6.4
Redundancia fogalma
6.5
Normálformák – a redundancia megszűntetése
21 22
6.5.1
Első normál forma (1NF) .................................................................................................................... 22
6.5.2
Második normál forma (2NF) ............................................................................................................. 23
6.5.3
Harmadik normál forma (3NF) ........................................................................................................... 23
6.5.4
Boyce/Codd normál forma (BCNF) .................................................................................................... 24
6.5.5
Negyedik normál forma (4NF) ............................................................................................................ 24
6.6
3
15
Anomáliák az adatbázisban
24
6.6.1
Módosítási anomália ........................................................................................................................... 25
6.6.2
Törlési anomália .................................................................................................................................. 25
6.6.3
Bővítési anomália ................................................................................................................................ 25
6.6.4
Inkonzisztencia .................................................................................................................................... 25
6.6.5
Tranzakció ........................................................................................................................................... 25
6.7
Az adatmodell időfüggvényének szerepe az adatmodell kialakításában.
6.8
Metaadatok tárolása az adatbázisokban
6.9
Fizikai tervezés 26
25
25
6.9.1
Indexek fogalma és felépítése ............................................................................................................. 26
6.9.2
Hash kódok ......................................................................................................................................... 26
6.9.3
A hash kód kulcsütközés kezelése....................................................................................................... 26
6.9.4
Bináris fa ............................................................................................................................................. 27
6.9.5
Asszociatív rendszerek ........................................................................................................................ 27
6.9.6
A hálózati adatkezelés problémái ........................................................................................................ 27
6.9.7
Tervezés a feldolgozási módok szerint................................................................................................ 28
6.9.8
Szinkronizálási módszerek .................................................................................................................. 28
6.9.9
Hálózati, adatbázisokkal kapcsolatos biztonsági kérdések.................................................................. 29
7
Adatbázis-kezelőkben alkalmazható adattípusok
8
Az adatbázis- rendszer dokumentálása
9
Az SQL lekérdező nyelv 34 9.1
Az SQL története, általános tulajdonágai
9.2
Az adatdefiniciós nyelv
34
35
Adattípusok az SQL-ben ..................................................................................................................... 35
9.2.2
Adattípusok a MySql-ben.................................................................................................................... 35
9.2.3
Speciális adattípusok a Microsoft Accesben ....................................................................................... 36
9.2.4
Adatdefiníáló utasítások – Táblák létrehozása .................................................................................... 37
9.2.5
Táblák módosítása ............................................................................................................................... 38
9.2.6
Teljes táblák törlése............................................................................................................................. 38
9.2.7
Nézettáblák létrehozása, módosítása, törlése ...................................................................................... 38
9.2.8
Indexek létrehozása ............................................................................................................................. 39
9.2.9
Az indexek megszüntetése .................................................................................................................. 39
Az adatmanipulációs nyelv
39
9.3.1
Új sorok beszúrása .............................................................................................................................. 39
9.3.2
A mezők tartalmának módosítása........................................................................................................ 40
9.3.3
Sorok törlése........................................................................................................................................ 40
9.4
A lekérdező nyelv
41
9.4.1
Az eredményhalmaz szűkítése – WHERE feltétel .............................................................................. 41
9.4.2
Halmaz műveletek SQL-ben ............................................................................................................... 48
9.5
4
33
9.2.1
9.3
10
31
Egyéb SQL parancsok
49
9.5.1
Tranzakciókezelés ............................................................................................................................... 49
9.5.2
Jogosultság kezelés ............................................................................................................................. 49
9.5.3
Tárolt eljárások (MySQL 5.x.x-től) .................................................................................................... 50
9.5.4
Triggerek ............................................................................................................................................. 55
Függelék
56
1 evezetés Az adatbáziskezelés é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) ismertetjük. Az SQL esetén megismerkedünk az ASNI SQL 92 alapjaival, illetve alkalmanként a MySQL és a Microsoft Access specialitásaival is. Nem beszélünk az Access kezeléséről és nem beszélünk a MySql kezeléséről sem. Ezeket a tudnivalókat a kedves olvasó más kiadványokból vagy a gyakorlatban sajátíthatja el. A jegyzet belső használatra, a Szily Kálmán Műszaki Középiskola diákjkai számára készült, de az internetről szabadon letölthető és tovább felhasználható. Jelen állapotában ideiglenes jegyzet, mely tovább formálódik. Az adatbáziskezelés és szervezés tantárgy keretén belül az adatokkal kapcsolatos alapfogalmakat, az adatok leírásának alapfogalmait, 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 ACCESS adatbáziskezelő használatát (a teljesség igénye nélkül) ismertetjük.
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 híjá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ázis-kezelő 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 é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 és a negyedik generációs nyelvek (4GL). Az adatbázis kezelő rendszerek számos eszközt nyújtanak az interaktiv 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.
5
2 Miről beszélünk, azaz általános definíciók? 2.1
Adat, információ
Az eltelt több mint 30 évben nagy terminológiai viták folytak az adatfeldolgozással kapcsolatban. Volt idő amikor az volt a felfogás, hogy az adat- feldolgozás (data processing) helyett információfeldolgozást (information processing) célszerűbb mondani és információs rendszerekről beszéltünk. Ennek megfelelően megkísérelték megadni az adat és az információ definícióját. Felesleges erőfeszítés és talán nem is lehet az adat fogalmát matematikai precízséggel definiálni, mint ahogy nem definiáljuk a halmazt, a teret stb sem. Elfogadhatjuk, hogy a valóságos világ minden objektumához, jelenségéhez tartozhat egy sor olyan "jellemző", paraméter, "adat" ami számokkal, betűkombinációkkal, tehát szöveggel vagy akármilyen jellel, jelsorozattal, jelenséggel, mozgással, stb. kifejezve megadható Az információ fogalmát már fontosabb definiálni, de a létező definíciók nem túl meggyőzőek. Mindenesetre az információt célszerű megkülönböztetni az adattól. Egy adat egyik ember számára jelent valamit a másik számára nem. Vannak irányzatok, amelyek azt javasolják, hogy a felhasználható, vagy hasznos adatot nevezzük információnak. Mások azt mondják az információ olyan adat, ami döntés megtételére alkalmas. Az információ tehát több mint adat, és az információt adatok hordozzák, az információt adatokkal adjuk meg. Az információ mint világunk harmadik fő attribútuma Noha az információ fogalmát pontosan nem tudjuk definiálni, érezzük, tudjuk, hogy az anyag és az energia mellett a valóságos világ egy harmadik nagyon fontos jellemzője, dolga, attribútuma. Információról beszélünk akkor, amikor adatfeldolgozást végzünk. Információ a tárgya a híradástechnikának. Információ a tárgya a folyamatok vezérlésének. Információ megy, adódik át a természetes nyelvekben. A biológiai kommunikációs rendszerekben is információ-feldolgozás történik például magában a sejtben, az élővilág minden egyedének minden sejtjében. Pontos definíciót nem adunk, számok szövegek, speciális jeleket értünk rajtuk.
2.2
A Shannon féle információs elmélet
Az első kísérlet az információ fogalmának megalkotására C.E. Shannon nevéhez fűződik aki 1948-ban megjelent. "A kommunikáció matematikai elmélete" című munkájában vizsgálta, kísérelte megadni az információ fogalmát. Azt mondhatjuk, hogy Shannon alapozta meg az információelmélet tudományát. Shannon elméletének a lényege az; hogy az információt jelek, adatok hordozzák. Pontosan ugyan nem adta meg, hogy mi is az információ amit ezek a jelek hordoznak, de megkísérelte mérni ennek az információnak a nagyságát, tehát egy mértéket próbált definiálni az információra. Shannon úgy gondolta, hogy egy jel akkor hordoz nagyobb információt, hogy ha ezen jel megjelenésének a váratlansága nagyobb. Shannon szerint tehát ha egy jel kisebb valószínűséggel jelenik meg valahol, akkor nagyobb információt hordoz. E filozófiát követve Shannon egy képletet adott meg egy jelsorozat által hordozott információ nagyságára, amiről kiderítették, hogy ez voltaképpen, teljesen egybeesik az energiával kapcsolatos u.n. entrópia megadására szolgáló képlettel, úgy hogy sokan éppen emiatt egymás mellé állítják, közös tulajdonságúnak tekintik az energiát és az információt. A Shannon féle információelmélet nem foglalkozik az információ tartalmával. Nem foglalkozik azzal, hogy egy szöveg értelmes, fölfogható, helyes, vagy éppen helytelen, vagy értelmetlen. Ezen elmélet szerint több információt hordoz egy véletlenszerűen összedobott betűkombináció, mint egy azonos hosszúságú de nyelvileg, tartalmilag érdekes szöveg. A Shannon féle elmélettel nagyon jól leírható ezen elmélet alapján a híradástechnika, átviteltechnika számos problémája, illetve az információtárolás néhány feladata is jól megfogható ezen elmélet segítségével. Számos próbálkozás történt az információ szemantikai definíciójára, egy olyan definíció megadására, amely az információ tartalma szerint adna lehetőséget arra, hogy mérjük, melyik jelsorozatnak, melyik közleménynek van nagyobb információtartalma. A Shannon féle információelméletben az információhoz voltaképpen egy bitsorozatot rendeltek hozzá és azt mondták, hogy egy információ mennyisége annál nagyobb, minél több bitből áll, minél több bittel adható meg, vagyis a bitek száma adta meg az információ nagyságát. Nyilvánvaló, hogy ennek az információ tartalmához nincs sok köze. Ez ideig nem sikerült az információra olyan definíciót adni, amelynek a segítségével minden területen tudnánk vizsgálni az információ nagyságát, a hordozhatóság tulajdonságait, a feldolgozás egzakt szabályait. Elfogadjuk, hogy létezik egy ilyen dolga a világnak, elfogadjuk, hogy ugyanúgy, mint ahogy az anyag energiát tud hordozni képes arra is, hogy információt hordozzon, információt közvetítsen. Elfogadjuk, hogy mint ahogy az energiát fel lehet dolgozni, át lehet alakítani, hasonlóan az információfeldolgozás is egy "valóságos" dolgon történik. 6
Úgy vesszük tehát, hogy az információt adatok hordozzák, és ennek megfelelően amikor adatfeldolgozást végzünk, voltaképpen információfeldolgozás történik. Különösen igaz ez az adatbázisok tekintetében. Maga az adatbázis vagy az adatbázisok információkat hordoznak, és az adatbázisból való lekérdezésekkel voltaképpen új információkhoz jutunk, az adatbázisban lévő elemi információkból összetett információt hozunk létre. Ha az adatbázist erre a célra létrehozott speciális eljárásokkal, algoritmusokkal dolgozzuk fel, akkor az adatbázist un. tudásbázisnak tekinthetjük (ez a tárgya az un. szakértői rendszereknek).
2.3
Az adatok tulajdonságai
A 60-as évek elején a számítógépek alkalmazásának nagyobbik részét az ún. adatfeldolgozás tette ki. A programozók rájöttek, hogy az adatokat és azok kapcsolódásait matematikai struktúrákkal lehet leírni, a feladatok megoldására pedig algoritmusok használhatók.
2.4
Az adatfeldolgozás formalizálható
Az adatfeldolgozás jobban "gépesíthető", ha az adatok közötti kapcsolatokat struktúrának tekintjük és adatmodellekben, adatsémákban gondolkodunk. Ez lehetővé teszi az egységes kezelést, olyan nyelvek kialakítását, amelyekkel szabványosan kezelhetők a tartalmuk szempontjából teljesen eltérő adathalmazok is. Az olyan adathalmazokat, amelyeket modellbe foglalva kezeltek, adatbankoknak, később pedig adatbázisoknak nevezték el.
2.5
Az adatok osztályozása funkciójuk szerint
Felhasználói információhordozó alatt a felhasználót ténylegesen érdeklő adatokat értjük. Szerkezethordozó adatról beszélünk, ha az információ tárolásához szükséges elengedhetetlen adatokat tekintjük. Ilyenek lehetnek például mutatók, indexek, egyéb jelölő és vezérlő (törlés jelző, rekordhossz jelző, állapotjelző, termináló és termináló jelek)
2.6
Az adatszerkezetek fajtái
Bár ezt a témát a szerző „Módszeres programozás” jegyzetében korábban már körüljártuk, de itt mégegyszer röviden áttekintjük a kérdést: Egyszerű adatszerkezetről beszélünk, ha az adat egy fajta értéket tartalmaz. Ilyen lehet például egy szám, karakter, logikai érték, dátum, stb... Az így kezelt adatokat csak egyesével, egymástól független módon tudjuk kezelni, elérni. Összetett adatszerkezetről beszélünk, ha az adatokat valamiféle struktúrába illesztjük. Az így keletkezett adatstruktúrát egységesen tudjuk kezelni, elérni. Ezekre példa, az egy-, és két dimenziós tömb, rekord, láncolt adatszerkezet, pédlául a lista (egy és kétirányú, vagy gyűrűs). Filekezelés kapcsán említhetjük meg a soros vagy szekvenciális filekezelést (például Pascal text file), • • • •
2.7
Direkt filekezelést – amikor a file-ban lévő adat fizikai helyére hivatkozunk Random file-ok, amikor a file tetszőleges rekordját elérjük, Index-szekvenciális, amikor a helyes elérési sorrendet indexek segítségével határozzuk meg, stb. Master vagy struktúrafile-ról beszélünk, amikor egy fileban tárojuk egy adatfile tartalmának leíróját és segítségével tudjuk kezelni az állományt.
Az adatok között kapcsolatok
Adatbázison voltaképpen adatoknak kapcsolataikkal együtt va1ó ábrázolását, tárolását értjük. Annak, hogy egy konkrét adatrendszert egy megadott szerkezetű adatbázisba foglalnak egybe, nagyon nagy előnye, hogy egyszerre sokan használhatják. Maguk az adatok csak "egyszeresen" vannak tárolva, a közöttük lévő kapcsolatok (az adatrendszer struktúrája) ismerete mégis lehetővé teszi, hogy a felhasználók egymástól függetlenül hozzáférjenek szinte úgy, hogy ki-ki "kiszakít" magának egy részt (nem fizikailag); vagy átszerkeszti az adathalmaz egy részét a maga számára (ezt az angol view szóból felhasználói nézetnek nevezzük). Az adatbázisok tehát "többfelhasználós" adathalmazok. Az adatbázis szemléletben az adatokat egy sémába 'foglaljuk bele"; ezt központilag tervezzük meg és ha ezt jól végezzük el, akkor minden adatérték csak egyszer szerepel a rendszerben, ráadásul egységes formátumban és így séma az "kompatibilisek" az adatok. – adatbázis Az adatbázis-séma a valóság egy részének tükörképe Az adatbázis-sémát úgy tekintjük, mint a valóságos világ egy olyan adatmodelljét, amely az adott szervezet szempontjából érdekes. Azok az objektumok (később ezeket egyedeknek, vagy egyedtípusoknak hívjuk) amelyek kapcsolatban vannak egymással a valóságos világban; az adatbázisban is kapcsolatban lesznek, tehát az adatbázisban a kapcsolatok is tükröződnek. kövei. Természetesen a valós világ egy adott részéhez nem csak egy adatbázis-séma rendelhető.
7
2.8
Adatmodellek története röviden
Adatbázisok modelljéül szolgáló struktúra kidolgozására számos próbálkozás történt. Ezek a próbálkozások az alábbi három irány körül csoportosultak. Formalista megközelítés. Ezen irányzat képviselői gráfokkal, diagramokkal kísérelték meg felírni az adatok közötti kapcsolatot, a szerkeze- tet (Bachmann, Nijssen, Sowa, stb. modelljei). Egy kis erőltetéssel azt mondhatjuk, hogy ezek a később elterjedt hierarchikus illetve hálós adatmodell alapjait képezték A szemantikai megközelítés lényege az volt, hogy a modellt teljesen verbálisan, szavak segítségével kísérelték meg leírni. Ez a modellfajta teljesen életképtelen volt, mert nehéz volt olyan programokat írni, amelyek tudták volna kezelni a nem teljesen precízen megadott adatbázisokat. A matematikai jellegű modellekben az adatok közötti kapcsolatot szintaktikusan pontosan, matematikai struktúrák felhasználásával írták le. Több egymástól eltérő felfogásban próbálkoztak, leginkább Codd relációs adatmodellje (1970) látszott életképesnek, de azt senki sem gondolta, hogy a század végére az adatbáziskezelő rendszerek nagy része a Codd féle modellre támaszkodik.
8
3 Adatbáziskezelő rendszerek és feladataik Az adatbázis önmagában semmit sem ér. A felhasználó számára akkor válik értékké, ha megadunk hozzá egy olyan szoftvert (programnyelvet), amellyel az adatbázist kezelni tudjuk. Az ilyen szoftver nagyon bonyolult, általában több elemből álló rendszert képez. Ezt a szoftvert adatbáziskezelő rendszernek hívjuk. Az adatbázisokkal lényegében két fontos műveletet kell elvégezni, nevezetesen az adatbázis létrehozását (az adatbázis szerkezetének definiálását és a szerkezet feltöltését konkrét adatokkal, illetve ezek módosítását, törlését), valamint az adatok visszakeresését, lekérdezését az adatbázisból. Ennek megfelelően az adatbáziskezelő rendszereknek (DBMS=DataBase Management System) is két fő funkciójuk van: az adatdefiníció ill. a lekérdezés.
3.1
Adatbázis-kezelők elemei
3.2
A file-kezelő
Az adatbáziskezelő rendszerek nagyon magas szintű nyelvek, de voltaképpen filekezelést végeznek. Az adatbázist alkotó fele-okban lévő adatok fizikailag pontosan úgy vannak tárolva mint minden más esetben: bináris jegyek kombinációjaként. Az adatbázisok létrehozásakor mi a logikai szerkezetet (az adatbázis sémáját) adjuk meg, az adatbáziskezelő rendszer fordítja ezt le, készíti el a "fizikai" file-okat, és kialakítja a logikai kapcsolatoknak megfelelő fizikai kapcsolatokat az adatok között. Ezeket a feladatokat a file-kezelő (fele-manager) végzi ami az DBMS fő része. Ez vezérli a file-ok létrehozását, a rekordok "beszúrását" (felvitelét) a törléseket; a módosításokat. A fele-kezelő tartja nyilván az adatok (fizika helyét, a köztük lévő kapcsolatokat, stb. Erre a célra a file-kezelő mélyén elhelyezkedő adatszótár (Data Dictionary) szolgál.
3.3
A lekérdező nyelv
A lekérdező nyelv egy interaktív eszköz, amelynek segítségével dialógus folytathatunk a rendszerrel. Ilyen például az SQL-nyelv. Az ilyen nyelveknek az a lényege, hogy nagyon könnyen felírhatók bennük "ad-hoc" kérdések, amelyekre rendszer azonnali választ ad. (lásd az SQL SELECT parancsát). Az adatbáziskezelő rendszereknek, mint nyelveknek két fajtája van: a. egyik az ún. beépülő típusú, befogadó típusú (host-típusú), amelyik voltaképpen nem is igazi programnyelv, hiszen csak egy másik valódi program nyelvvel (COBOL, PL1, PASCAL stb.) együtt használható fel alkalmazó: programok írására, alkalmazói rendszerek felépítésére. Ilyen az SQL nyelv is. A másik típus valóságos programozási nyelv. Ezeknél ahhoz, hogy alkalmazói rendszereket készítsünk az adatbázis felhasználására, nem kell más programnyelvet bekapcsolni. Erre példa a dBase (III, IV, V) vagy a Clipper nyelv.
3.4
Adatbáziskezelőkben ellátott segédfeladatok
Az adatbáziskezelő rendszerek a fő funkciókon kívül több "segédfeladatot" is ellátnak. Ezek közül kiemeljük a következőket. Adatvédelem, adatbiztonság. Nem minden felhasználónak van joga az adatbázis minden adatához hozzáférni. Amikor a felhasználó programján keresztül az adatbázishoz fordul egy jelszóval kell azonosítani magát. Ezt az adatbáziskezelő rendszer értékeli, és csak azokhoz az adatokhoz engedi hozzáférni, amelyekhez annak jogosultsága van. Az adatok védelme nagy adatbázisoknál rendkívül fontos dolog, hiszen az adatbázis tönkretétele nagyon nagy károkat okozhat (gondoljunk egy óriási bank adatbázisára). Az adatok védelmének szoftver eszközökkel történő biztosítása programozói szemszögbál nézve igen érdekes, de nagyon nehéz feladat. Az integritási feltételek teljesülésének figyelése. Az adatbázis adataival kapcsolatban gyakran meg lehet fogalmazni olyan feltételeket; amelyek ellenőrzésével az adatbázis létrehozásakor kiszűrhetők azok az input adatok, amelyek nem az adott adatbázisba valók (hibásak). Az adatbázis belső szerkezete is hordozhat olyan információkat, amelyek meghatározzák, hogy új adatok bevitele esetén ezek illeszkednek-e az adatbázishoz. Például kiköthetjük, hogy a dátum nevű adattétel nem vehet fel az 1900-as évek előtti dátumot, vagy előfordulhat, a rendszer olyan, hogy két adat megegyezése valahol az adatbázisban maga után hozza másik két adat megegyezését. Az ilyen típusú információkat integritási feltételeknek nevezzük. Az adatbáziskezelő feladata ezen integritási feltételek teljesülésének vizsgálata is. Szinkronizáció. Különösen hálózatban-üzemelő nagy adatbázisoknál, egyidejűleg nagyon sok felhasználó fordulhat esetleg ugyanazon adathoz. Ráadásul úgy, hogy egyik éppen módosítani akarja, a másik pedig lekérdezni. Ezeknek az ún. holtpont helyzeteknek a megoldása nagyon nehéz feladat.
3.5
Adatbázis statisztikák
Mivel az adatbáziskezelő rendszerekben az adatokhoz való hozzáférés a központi szoftver vezérlése alatt megy végbe, ezért az ilyen rendszerek minden, az adatokkal kapcsolatos műveletről feljegyzést vezetnek, mindent nap9
lóznak. Ezeknek az adatoknak a felhasználásával azokhoz a műveletfajtákhoz, amelyek gyakran szerepelnek különféle gyorsításokat (pl. indexelés) hajthat végre a rendszer.
3.6
Az adatbázis adminisztrátor
Az adatok központosítása lehetővé teszi, hogy egy személy az un. adatbázis adminisztrátor (ABA) (nagy adatbázisok esetén ez több személy is lehet) feleljen a teljes adatszervezésért, a biztonságért, az egész rendszerért. Az ABA adja ki a különféle jelszókat, engedélyez hozzáféréseket, módosításokat. Mivel minden releváns információ "egy fejben" van, így könynyebb biztosítani az adatbázis konzisztenciáját és integritását. Az adatbázis adminisztrátort speciális szoftverek segíthetik.
3.7
A 4GL-ek
A 4GL-ek (Fourth Generation Languages = Negyedik Generációs Nyelvek) adatbáziskezelő rendszerek, amelyekkel nagyon kényelmesen, gyorsan írhatók fel adatbázis-alkalmazások, rendszerfejlesztések. Nincs mindenki által elfogadott definíció arra, hogy pontosan mit tekinünk 4GL-nek. Mindenesetre fóbb paraméterekként a következők sorolhatók fel. A 4GL segítségével képernyőn könnyen és gyorsan tervezhető meg az input és a módosítás. A felhasználónak nincsen más dolga, mint a képernyőn megjelenő képen (ábrán) kitölteni a mezőket. Bizonyos mezők letilthatók, vagyis ezekbe nem lehet beírni. Több adatbázis-táblán egyszerre is végrehajthatók a képernyőn keresztül bevitelek, módosítások. A másik fontos tulajdonsága a 4GL-eknek, hogy rendelkeznek olyan, az output megvalósítására szolgáló eszközzel, amelyekkel jól formázott, sablonokból előállítható listák írhatók ki (akár több adatbázis táblából is) anélkül, hogy programot kellene írnunk. Ez azt jelenti, hogy szabványos kiírási képeket definiálhatunk, azt könyvtárba foglaljuk és szükség szerint építjük bele "grafikusan" a különféle felhasználói programokba. A 4GL-tulajdonságú adatbáziskezelő rendszerekben nem csak az input, output támaszkodhat képernyőelemekre, hanem más felhasználói műveletek is (Pl. keresés, módosítás, stb. az adatbázisban). Azt szokták mondani, hogy 4GL esetén vagy 4GL tulajdonságú adatbáziskezelő alkalmazásakor csak azt kell megmondani, mivel mit akarunk elvégezni, de azt nem, hogy hogyan, tehát az eljárásnak, a procedúrának nincs szerepe, azaz a nyelv nem eljárásorientált. A 4GL jellemzője az is, hogy az előbbiekben említett "képi" megoldásokkor a 4GL programkódokat is generál, amiket a programozó módosíthat, átírhat.
3.8
Integrált CASE eszközök
A korszerű, relációs modellre támaszkodó adatbáziskezelő rendszerek közül sokban megtalálható a CASE csomag (CASE = Computer Aided Softver Engineering = Számítógéppel támogatott szoftver technológia) A CASE többek között lehetővé teszi az adatbázistervező számára az un. egyed-tulajdonság diagramok interaktív felrajzolását, az adatbázisséma létrehozását, módosítását. A CASE-zel automatikusan generáltatók a tényleges táblák, relációk, (lásd később) és elvégezhetők bizonyos normalizálási teendők is (lásd később) Az adatbázis tervezést használó programok Az összetett feladatok ellátásához ma már meglehetősen bonyolult feladatrendszerek állnak rendelkezésre. Több olyan módszertan fejlődött ki, amelyek alkalmazások fejlesztéséhez használhatók. Ilyen például az UML (=Unified Modelling Language) vagy az SSADM. Ezekkel a tervezőeszközökkel az adatok struktúrűja, a tevékenységek struktúrája formálisan leírható, majd a formális leírás alapján néha az adatbázisok szerkezetén kívül a kezelő program, vagy annak váza is létrehozható.
3.9
Az ANSI/SPARC modell
A 70-es években az adatfeldolgozásban nagyon elterjedt az adatbázis szemlélet, és számos adatbáziskezelő rendszer jött létre. Abból a célból, bizonyos szabványosítást lehessen elvégezni a fogalmak és a terminológiák tekintetében, az ANSI/X3/SPARC bizottság elkészített egy ANSI/SPARC modellt. A belső nézet a tárolt, fizikai adatbázist jelenti. A belső koncepcionális leképezés a belső nézet a koncepcionális sémát, az un. logikai sémáját hozza létre A külső koncepcionális leképezés a logikai sémákból külső nézeteket hoz létre. A felhasználók az alkalmazások használatán keresztül általában csak a külső nézetekkel találkoznak. Az adatbázisok tervezésénél a logikai séma megalkotása a legfontosabb
10
3.10 Adatbázis tervezés lépései 3.11 Logikai, fizikai szerkezet Az adatbáziskezelésben (általában az adatkezelésben) a logikai jelző azt jelenti, hogy felhasználó-orientált, azaz a felhasználó számára érthető. Például egy adatbázis tervezésénél logikai szerkezetben írjuk le a dolgokat, amikor a valóságos világ objektumait képezzük le adatmodellé. A fizikai jelző azt fejezi ki, hogy az adatok szerkezete "gépközeli" azaz a memóriában való tárolás, a file-kezelés szempontjából tekintjük az adatok szerkezetét, úgy ahogy a rendszerprogramozó "látja" amikor az adatbáziskezelő fordítóprogramjait írja.
3.12 A programok és az adatok függetlensége Ez az adatbázisok fontos tulajdonsága. A program és az adatok függetlenségén azt értjük, hogy kis módosítások az adatbázis struktúráján megtehetők anélkül, hogy az alkalmazói programot módosítani kellene. Természetesen teljes függetlenség nem valósítható meg, de minél nagyobb ez a függetlenség, annál jobb a rendszer.
3.13 Az adatbázis életciklusa A felhasználó számára kész termékként átadható, üzemeltethető adatbázisok (a kezelő-programokkal együtt) többlépcsős folyamaton keresztül jönnek létre nagyjából egy adott séma szerint. 3.13.1
Az elemzés
Az első lépcső az adatbázissá formálandó valóságos probléma megértése, a korlátozó feltételek felismerése, a lehetséges variációk feltérképezése. A probléma megértéséhez először azonosítjuk a problémakörben szereplő valóságosan létező tárgyakat, személyeket. Összefoglalva ezeket szereplőknek hívom. Meghatározzuk a szereplők figyelembe venni kívánt tulajdonságait és elvetjük a nem szükséges tulajdonságaikat. A fontosság kritériuma, hogy az adatbázis céljához van-e köze az illető tulajdonságnak Meghatározzuk, az adatbázissal modellezendő folyamatokat. Ezek lesznek az események. Az események általában valamiféle dinamizmust, változást határoznak meg. Megnézzük, hogy ezekben a folyamatokban hogyan vesznek részt a szereplők. A fentiek alapján megvizsgáljuk, hogy az egyes szereplők milyen csoportokba sorolhatók, a valóságos folyamatokban milyen szerepekben vesznek részt (pl. egy üzleti folyamatban vevő, vagy szállító, vagy eladó...) Megvizsgáljuk, hogy az egyes szereplők között a valóságban milyen statikus és vagy dinamikus kapcsolat van. Csak az adatbázis szempontjából lényeges kapcsolatokat vesszük figyelembe, a többi kapcsolatot figyelmen kívül hagyjuk. Ebben a folyamatban matematikai értelemben vett halmazokat definiálunk, amely halmazok elemei a fent említett szereplők és az események. 3.13.2
A modellalkotás
A második lépésben megalkotjuk az adatmodellt (az egyed-kapcsolat modellt), meghatározzuk a kulcsokat. Az adatmnodell valójában a valóság leképezéseként jön létre. Az adatmodell nem a valóság tükörképe, hiszen a résztvevők tulajdonságaiból elhagytunk, a kapcsolatoknak csak a fontos tulajdonságait emeljük át a modellbe. Meghatározzuk a modell típusát (hierarchikus, hálós vagy relációs - lásd később). Meghatározzuk a modellben szereplő egyedek kapcsolatait egymással (az egyed meghatározása később). Adatmodellünket normalizálási procedúrának vetjük alá a következő lépésben. Az adatbázisokat általában a lehető legmagasabb normálformása hozzuk, azonban időnként el kell rontani a normalizált állapotot. Ezt denormalizálásnak hívják. Miután megalkotjuk az adatbázis modelljét létrehozzuk a fizikai adatbázist. 3.13.3
Fizikai létrehozás
Az adatbázis modllje alapján szintén egyfajta leképezéssel jön létre a fizikai adatbázis. Gyakran az adatbázismodellt nem tudjuk az eszközeinkkel egyszerű módon leképezni, ezért ilyenkor előfordulhat, hogy visszanyúlunk a tervezési fázishoz és módosítjuk az adatmodellt. A fizikai adatmodell az alkotó táblák halmaza, melyeket az adatbázis adminisztrátor az adatbáziskezelő rendszer programmal (SQL-ben a CREATE TABLE paranccsal) definiál.
11
A következő lépés a különféle file-ok létrehozása, az indexelés. Az indexelés során megállapítjuk, hogy mely mezőkre keres a rendszer gyakran, ezekre állítunk be indexeket. Ezután jöhet és a szükséges nézetek definiálása. Ezután a lekérdezések megtervezése következik. Bár ez a folyamat sokadik lépése, de minden előtte lévő lépést is befolyásol, mivel szinte minden amiatt történik az adatbázisban, hogy lekérdezéseket adhassunk fel. A képernyőtervet a beviendő adatok és a kimenő eredmények határozzák meg; itt voltaképpen az ember-gép kapcsolatot, a felhasználó és az alkalmazási rendszer kapcsolatát határozzuk meg. A képernyőterv alkalmas is, hogy egyszerűbb kérdés-felelet dialógust szerkesszünk meg, amelyek segítségével a nem profi alkalmazók jól definiált feladatokat oldhatnak meg. Létrehozhatunk a képernyőn olyan menüket, amelyek egy sor lehetőséget kínálnak fel a felhasználónak aki különféle sorrendben választhat a menüpontok közül abból a célból, hogy kitűzött feladatát megoldja. A listázási terv szintén fontos eleme az adatbáziskezelésnek. Itt speáljuk mit és milyen standard táblázatban akarunk kiírni az adatbázisból lekérdezett eredményekből. A legtöbb DBMS-ben megtalálhatók az un. „report writer"ek, a listázó programok, amelyek segítségével könnyen tudjuk szerkeszteni az output formátumát. A következő lépés a tesztelés megtervezése. Ez természetesen nemcsak az adatbázistervezés fontos eleme, hanem bármilyen más program írásakor is lényeges szerepe van; kulcs-lekérdezések, input és karbantartó képernyőképek, listázó programok szolgálnak az adatbázis helyességének illetve az egész rendszer életképességének ellenőrzésére. Nagy jelentősége van egy teszt-adatbázisnak, ami pontosan olyan, mint az "élő" adatbázis, de a benne szereplő adatokat úgy tervezzük meg, hogy a lehető legtöbb esetet reprezentálják. Az utolsó lépés a rendszer átadása, amikor a felhasználó megkapja az adatbázist, az alkalmazói programokat és elkezdődhet az adatbázis feltöltése.
3.14 Objektum-orientált adatbázisok Az adatbázisok egy új generációját jelentik az objektum-orientált adatbázisok (illetve adatbáziskezelők). Ez a filozófia abban tér el az eddigiektől, hogy integrálja az adatkezelés és az adatdefiniálás mechanizmusát. Ez a gyakorlatban azt jelenti, hogy az adattáblák definiálásakor nem csak a mezők típusát, és méretét definiáljuk, hanem az mező validálási információit, formátum információit, kezdőértékét, indexelését stb... Ilyen módon az adatbázis kezelése során nem kell plusz eljárásokat írni azadatbevitel és megjelenítés minde esetét tekintve, hanem ha egy adatot beviszünk vagy megjelenítünk, akkor a rendszer önmagától képes a megfelelő vizsgálatokra és formátumok alkalmazására. (Nagyon szépen látdszik ez a fajta gondolkodás a Microsoft Access tábladefiniálásakor) Magában az adatbázistervezésben az objektum-orientált szemlélet nem jelent lényeges eltérést az egyed-kapcsolat modellhez képest, a korszerű adatbáziskezelő rendszerek (pl. a 4GL-ek) viszont teljességgel követik az objektumorientált nyelvek filozófiáját.
12
4 Az adatmodellek – a logikai séma 4.1
Az egyed fogalma
Egyednek hívunk minden olyan dolgot (objektumot), ami minden más objektumtól megkülönböztethető és amiről adatokat tároltunk. Az egyed konkrét dolgok absztrakciója, konkrét dolgokat fog egybe. Matematikailag az egyed bizonyos tulajdonságú dolgok halmaza. Például az autó mint egyed sok autót jelent. Egy adott egyed által képviselt konkrét elemek halmazát egyedhalmaznak is szoktuk nevezni. Például a dolgozó nevű egyed egyedhalmaza az összes dolgozókból áll, a személy egyedhalmaz az összes személyt tartalmazza, az érzelem egyedhalmaz az összes érzelem. Az egyed egy konkrét értékét az egyed egy előfordulásának nevezzük, de az előfordulás helyett mondhatunk egyedértéket is. Megjegyzés. Gyakori, hogy az egyed helyett egyedtípust mondunk.
4.2
A rekordtípus
Az adatbáziskezelő rendszerekben (elsősorban a hálós, hierarchikus modelleken alapulókban) az egyedtípust rekordtípusnak hívjuk. Ez a legkisebb címezhető egység (ennél kisebb részeire nem lehet hivatkozni az adatbázisnak).
4.3
A tulajdonság fogalma, Azonosítók
Az egyedeket tulajdonságokkal (attribútumokkal) írjuk le, a tulajdonság az egyed egy jellemzője, ami megadja, meghatározza az egyed e részletét. A tulajdonság is absztrakció, amely több elemet foglal magába, tehát konkrést előfordulásokból áll. A tulajdonságok lehetnek: - azonosítók (például a kulcsmezők) Ha egy tulajdonság minden értéke minden egyed előfordulásra más és más értéket vesz fel, akkor azonosítónak hívjuk. - leírók (nem kulcsmezők) A leírók nem határozák meg egyértelműen az egyed előfordulásait. Az azonosítók létrehozása esetenként az egyed természetes tulajdonságaiból adódik. Erre példa a személyi szám a személyek azonosítása terén. Ekkor beszélhetünk egyszerű azonosítóról. Amikor nem lehet ilyen azonosítót alkalmazni, két lehetőségünk van: Addig bővítjük az azonosításra alkalmas tulajdonságok körét, amíg elérjük, hogy a sajátosságok kombinációja már egyedileg azonosítja a kérdéses egyedek előforulásait, Mesterséges nevet alkotunk, aminél biztosítjuk az egyediséget, de aminek esetleg semmi köze nincsen az egyedtípushoz. (pl. automatikusan növekvő sorszám)
4.4
Kulcs
A menniyben a tulajdonságok vagy a tulajdonságok egy csoportja egyértelműen meghatározza, hogy az egyed melyik értékéről, előfordulásáról van szó, akkor ezeket kulcsoknak nevezzük.
4.5
Többértékű vagy összetett tulajdonságok
Ha a tulajdonságok olyanok, hogy több értékkel rendelkeznek, akkor összetett vagy többértékű tulajdonságnak hívjuk.
4.6
A kapcsolat fogalma
Kapcsolatnak nevezzük az egyedek közötti viszonyt. A kapcsolat mindig valóságos objektumok közötti viszonyt fejez ki, hiszen az egyed ilyen objektumokat képvisel; A kapcsolat is absztrakció: két egyed értékei közötti konkrét viszonyokat fejez ki. A konkrét kapcsolatokat a kapcsolat értékeinek (előfordulásainak) nevezzük. A kapcsolódó egyedek között általában nem egyenrangú a viszony, hanem lehet egyfajta irányításról beszélni. A kapcsolat meghatározója a tulajdonos (owner) és a kapcsolat másik oldalán lévő egyed vagy egyedek a tagok (member). Az egyedek közötti kapcsolat lehet kötelező, félig kötelező, opcionális. Kötelező kapcsolatról beszélünk, ha az adatmodell időfüggvényének szerepe van az adatmodell kialakításában.
13
Az archiválás problémaköre (inaktív egyed-előfordulások).
4.7
A kapcsolat fokai
Kapcsolat egyidejűleg több egyed között is lehet. Ha a kapcsolat n egyedet "köt össze" akkor n-edfokúnak nevezzük. A leggyakoribb a bináris (másodfokú) kapcsolat, amikor is két egyed között van viszony. Speciális bináris kapcsolat a rekurzív bináris kapcsolat. Ilyenről van szó akkor, ha az egyeden belül az előfordulások vannak kapcsolatban. Pl.: a dolgozó egyedben benne vannak a vezetők is (általában ők is dolgozók), de a többi dolgozóval speciális kapcsolátban vannak: vezetői-beosztotti kapcsolatban. Gyakoriak a valóságos világ objektumai között a harmadfokú (angolul ternary) kapcsolatok, vagyis olyanok amikor három egyed között van viszony.
4.8
A kapcsolatok fajtái
Egy kapcsolat voltaképpen az egyedhalmazok elemei közötti viszonyt fejezi attól függően, hogy az egyedhalmaz elemei milyen módon kapcsolódik egymáshoz, azaz egy adott egyed egy előfordulásához hány előfordulás kapcsolódik a másik egyedből három esetet különböztetünk meg a) Egy-egy típusú kapcsolat (1:1 kapcsolat) A kapcsolat lelet olyan, hogy az egyik egyedhalmaz mindegyik eleméhez a másik egyedhalmaznak pontosan egy eleme kapcsolódik (tehát a két egyedhalmaz egymásba kölcsönösen egyértelműen képezhető le. Az ilyen kapcsolatot egy-egy (jelben 1 :1) típusúnak nevezzük. b) Egy-több típusú kapcsolat (1:N kapcsolat) Azt mondjuk, hogy az A egyed és a B egyed között egy-több (jelben: 1:N) típusú kapcsolat van, 1a az A egyedhalmaz mindegyik eleméhez a B egyedhalmaznak több eleme is tartozhat. c) Több-több típusú kapcsolat (N:M kapcsolat) Több-több (jeIben N:M) típusú kapcsolatnak nevezzük az A egyed és a B egyed közötti kapcsolatot; ha az A egyedhalmaz minden eleméhez B halmaz több eleme is tartozhat, továbbá B minden eleméhez A több eleme is kapcsolódhat. Az ilyen kapcsolatot az adatbvázis-kezelő rendszerek nem feltétlenül tudják automatikusan lekezelni, ezért ilyenkor szokásos automatikusan a két egyedtípus közé felvenni egy kapcsoló egyedtípust, amely a kapcsolatokat szétvágja két egy-több típusú kapcsolatra. Az ilyen kapcsoló egyedek gyakran valamiféle eseményt hordoznak. d) Nulla vagy egy-egy típusú kapcsolat Ez a kapcsolattípus akkor jön létre, ha egy egydhalmaz előfordulásaihoz a másik egyedhalmazból nem feltétlenül tartozik egy előfordulás e) Egy-nulla vagy egy – több típusú kapcsolat Ha az egyik egyedtípushoz a másik egyedtípusban nem feltétlenül tartozik előfordulás vagy több előfordulás tartozhat. A kapcsolatok ábrázolásához használhatjuk az Egyedkapcsolat diagramot. (Entity RelationShip Diagram – ERD) Ilyen diagramokat lehet készíteni a Microsoft Access kapcsolattervezőjében vagy sok más hasonló alkalmazásban.
4.9
A kardinalitás
Az egyedek a kapcsolat szempontjából az un. kardinalitási számmal is jellemezhetők. Az egyed kardinalitási száma a kapcsolatban azt adja meg, hogy egy egyed egy előfordulásához a másik egyedből maximálisan hány előfordulás kapcsolódhat, azaz az N:M kapcsolatban a kardinalitási szám : max. (N), max. (M).
4.10 A belső szerkezete, belső tulajdonságai Egy adatmodellben valamennyi egyedet egyenként véges számú tulajdonsággal írunk le. Ezek a tulajdonságok együttesen alkotják az egyed belső szerkezetét, és az egyed belső tulajdonságainak hívjuk őket.
4.11 Az egyed külső tulajdonságai Az egyedek kapcsolatainak összességét külső tulajdonságoknak hívjuk
4.12 A settipus fogalma Az adatbáziskezelő rendszerekben (elsősorban a hálós és hierarchikus modellen alapulókban) két egyedtípust (agyaz rekordtípust) a köztük lévő kapcsolattal együtt settipusnak nevezzük. Ezt névvel látjuk el; így a feldolgozás során hivatkozni lehet rá (a rekordtípushoz képest eggyel magasabban címezhető egység). 14
A settipusban a kapcsolatnak mindig "irányt" is adunk, rajzban ezt nyíllal fejeszük ki. Az egyik rekordtípust (a.miből a nyíl vezet) tulajdonosnak (owner) a másikat (amibe a nyíl vejet) taginak (member) nevezzük. (1.14 ábra)
4.13 A kapcsolatok ábrázolásának különféle módjai A kapcsolatok ábrázolására több módszer terjedt el. Vonalas összekötés A legegyszerűbb ezek közül az, amikor két egyedtípust egy egyenessel, vagy kifejezve a kapcsolat irányát egy nyíllal kötünk össze. A Chen féle jelölés Egyes szerzők a Chen féle jelölést használják. A kapcsolatot itt rombusz jelöli; amibe beírjuk a kapcsolat "tartalmát", nevét is. Ennél a jelölésnél megadjuk a kapcsolat fajtáját és a zérus minimális kardinalitást is (körrel). Nyíllal jelölhetjük a kapcsolat irányát is. A magyar nyelvben nehezebb felírni a kapcsolat tartalmát, mint azokban a nyelvekben, ahol a rag a főnév előtt van (pl. angol, német, stb.). A "varjú-lábas" ábrázolás A kapcsolatban előforduló egyedeket dobozokba írjuk, a kapcsolatot egy vonal jelképezi, amely összeköti a dobozokat, a kapcsolat típusát az egy oldalon egy vonal, a több oldalon három kis vonal jelöli.
4.14 Egyed-szupertípus, egyed-altípus Amennyiben több egyedtípus ugyanazon tulajdonságtípusokkal rendelkezik, úgy ezek az egyedtípusok egy magasabb szintű egyedtípusba vonhatók össze. Ezt az absztrakciót generalizálásnak nevezzük. Azt az egyedtípust amit több egyedtípusból generalizálunk egyed-szupertípusnak, az öt alkotó egyedtípusokat pedig egyed-altípusoknak nevezzük. Az egyed-szupertípust más szemszögből is nézhetjük; azt mondhatjuk, az egyed-altípusok az egyed-szupertípus specializációi. Egy egyed-szupertípus egy kapcsolatban, lehet egyed-altípus egy másik kapcsolatban. Ha az egyed-szupertípus és az egyed-altípus kapcsolatok egy struktúrát hoznak létre, akkor ezt generalizációs hierarchiának nevezzük. Az egyed-altípusokat két csoportra sorolhatjuk. Ezek lehetnek ugyanis diszjunktak, vagyis olyanok, amelyeknek nincsen közös elemük. A generalizációnál azt mondhatjuk, hogy a tulajdonságokat az egyed-altípusok öröklik az egyed-szupertíustól.
4.15 Aggregáció Az aggregáció egy másik absztrakció az egyed-szupertípus és az egyed-altípus között. Az aggregáció esetén az altípus része az egyed szupertípusnak. Pl. egy szoftverterméknek része a program és a felhasználói kézikönyv. Az aggregációnál a tulajdonságok nem öröklődnek, mindegyik egyednek saját tulajdonságai vannak.
15
5 Az adatmodellek és az adatbázisok Az előbbiekben megismert elemekből többféle adatmodell hozható létre.
5.1
Az adatmodell fogalma
Az adatmodell egyszerű, tulajdonságok és kapcsolatok halmaza, amely absztrakt módon tükrözi a valós objektumoknak, szak jellemzőinek (tulajdonságainak) és viszonyainak (kapcsolatainak) elvont kategóriáit. Az adatmodell egy olyan séma, amelyben megadjuk milyen tulajdonságok határozzák meg az egyedeket, milyen egyedek szerepelnek a sémában, és ezek között milyen kapcsolatok vannak. Az adatbázisokat különféle adatmodell szerint építhetjük fel. Az adatmodell meghatározza az adatbázis szerkezetét, sémáját. Az adatmodell, adatbázismodell szavak ugyanazt jelentik. Az egyedet, a tulajdonságot és a kapcsolatot adatmodell-elemeknek nevezzük. Attól függően, hogy ezekből milyen módon hozzuk létre az adatmodell szerkezetét, különféle adatmodelleket kapunk. Három adatmodell terjedt el az elmúlt 30 év alatt. Az első két modellről (a hierarchikusról és a hálósról) úgy látszott, hogy velük sikerült véglegesen "megfogni" az adatfeldolgozást. A harmadikról (a `relációsról) sokan azt hittük, hogy zsákutca. A nyolcvanas évek elejére kiderült, hogy az utóbbi jóslat tévedés volt; ma már a relációs modell szinte egyeduralkodóvá vált. Az, hogy milyen modellre alapozva végezzük az adatfeldolgozást, az adatbázis megszerkesztését meghatározza az adatbáziskezelő rendszert, a nyelvet is. Minden egyes modellre számos nyelvet dolgoztak ki.
5.2
A hálós adatmodell
A "történelem" folyamán nem ez volt időben az első adatmodell, de mivel úgy tekinthető, mint a hierarchikus általánosítása (azaz a hierarchikus ennek speciális esete), ezért a hálós adatmodell ismertetésével kezdjük. A hálós adatmodell szerkezetét gráffal adjuk meg. Ebben a gráfban a csomópontok az egyedek, az élek pédig a kapcsolatot fejezik ki. Az egyedeket tulajdonságaikkal írjuk le. A hálós adatmodell adatbázisaiban az egyedek (tehát az előfordulásaik) közötti kapcsolatot listaszerkezettel (pointerek, mutatók használatával) adják meg. Például: Tejüzemeket és boltokat nézünk. Az egyes tejüzemek több boltba szállítanak árut. A tejüzemeknek a teherautókkal a lehető legrövidebb úton (leggazdaságosabban) kell eljuttatni az árukat a boltokba. A csomópontok a termelőegységek és a boltok, az élek megfelelnek az útvonalaknak.
5.3
A hierarchikus adatmodell
A hierarchikus adatmodell szerkezetét specilis gráffal adjuk meg, fával. A csomópontok itt is egyedeket jelentenek és a nyilak a kapcsolatokat fejezik ki. A hierarchikus adatmodellre épülő adatbázisok kezelésére számos adatbáziskezelő rendszert dolgoztak ki. Például: • Hierarchikus adatmodell alakítható ki egy cég szervezeti hierarchiájának leírására • Hierarchikus adatbodellt használnak a windows rendszerek felhasználói adatbázisának tárolására. • Hierarchikus adatszerkezetet használunk egy filerendszer leírására.
5.4
A relációs adatmodell
A relációs adatmodell más filozófiára épül, mint az előbbi kettő. Ennél az adatmodellnél a három adatmodellelem közül a kapcsolat nem játszik szerepet, pontosabban szólva a kapcsolat nem, csak a lehetőség épül be az adatmodellbe. A relációs adatmodellnél a tulajdonságok kapják a fő hangsúlyt, a tulajdonságokkal definiáljuk az adatmodell szerkezetét. Az olvasó bizonyára észrevette, hogy az előbbi két modellben a gráffal történő leírásban éppen a tulajdonságok nem szerepeltek, ezeket külön adtuk meg. A relációs adatmodellben az egyedet egy táblázattal adjuk meg, a táblát oszlopai a tulajdonságok. A táblázat sorai az egyed értékei (vagyis a táblázat maga éppen az egyedhalmaz). A táblázat egy-egy sorát a tulajdonságok konkrét értékei adják. A relációs adatmodellben létrehozott adatbázisokat több táblázattal adjuk meg (ne felejtsük el, minden tábla egy egyedhalmaz), de a táblázatok (tehát az egyedek) közötti kapcsolatokat nem definiáljuk az adatmodell felírásakor. 16
Ez nem azt jelenti, hogy nincsen közöttük kapcsolat, de ezeket a kapcsolatokat például egyszerűen az fejezi ki, hogy két táblának van közös oszlopa (mindkét táblában szerepel ugyanaz a tulajdonság). Miért hívják ezt a modellt relációsnak?
5.5
Reláció definíciója
Definíció Legyenek D1, D2,...Dn adott halmazok. E halmazok Descartes szorzatán azt a D halmazt értjük, amelyeknek az elemei a (v1,v2,...,vn) érték n-esek, amelyekben v1 elme D1-nek, v2 eleme D2, nek stb... Azaz minden halmazból kiveszünk pontosan egy elemet és a megadott sorrendben egymás után írjuk. Az így kapott elemsorozat lesz a szorzathalmaz egy eleme. A Descartes szorzat egy R részhalmazát relációnak nevezzük. A D,, D~,..., D„ halmazokat a reláció tartományainak (domain), a reláció egy elemét n-esnek (elem n-esnek, angolul taple-nak) nevezzük. Az R reláció elemeit gyakran rekordoknak hívjuk. A továbbiakban az adatbázis egy "oszlopát" attribútumnak nevezzük. Úgy is fogalmazhatunk, hogy az attribútum egy változó, ami az oszlopba írt értékeket "veszi fel" a tartományban szereplő értékek közül, azaz a tartományt az adott attribútum értékkészletének tekinthetjük. Jelöljük a továbbiakban az attribútumokat az A1,A2,..., An indexes nagybetűkkel, ezeket együtt (vagyis az attribútumok halmazát) A-val, azaz A = { A1,,A2~,..., An} . Megadtuk tehát a relációs adatmodell két definícióját. Az egyik definíció szerint a relációs adatbázis táblázatokból áll, a táblázatokat az oszlopok definiálják. A másik definíció szerint a relációs adatbázis relációkból áll. Az olvasó bizonyára észrevette, hogy voltaképpen elnevezésbeli különbségről van szó. A továbbiakban vegyesen használjuk a táblázat ill. reláció elnevezést. Az attribútumok számát (az oszlopok számát) az R reláció fokának, a sorok számát pedig a reláció számosságának nevezzük. Ahelyett, hogy n-ed fokú, gyakran mondjuk, hogy n változós. A relációs adatbázist röviden jelölni, a következő módon lehet: R ( A1,A 2,..., An). alakban tesszük, ahol R a reláció neve, Ai pedig egy attribútum. A továbbiakban a reláció nevét és az attribútumokat nagybetűkkel jelöljük. Az R (A2,A2,..., An) alakot a reláció sémájának nevezzük. Gyakran attribútum helyett egyszerűen oszlopnevet mondunk sőt még egyszerűbben azt mondjuk, hogy a reláció oszlopokból áll. (pl. az SQL nyelvben) Több adatbáziskezelő rendszerben mezőnek nevezzük az attribútumot, és a mező értékeiről beszélünk (pl. dBASE IIL, IV.)
5.6
Reláció, tábla, adatbázis
Az előbbiekben egy táblát (relációt) adatbázisnak neveztünk. A relációs adatbázisok általában nem egyetlen relációból, táblából állnak, hanem több tábla alkot egy adatbázist. Azt, hogy egy adatbázist hány táblára bontunk szét, vagy hány táblát fogunk össze egy adatbázisba, már az adatbázis megtervezésekor eldöntjük. A táblákra bontásnál az attribútumok közötti kapcsolat jelentősen befolyásolja, hogy mely oszlopok kerülnek egy táblába. Egy adatbázis nem egymástól független. táblák halmaza, hiszen így semmit sem érnénk velük. A relációs adatbázisokban a különböző táblákat közös attribútumokkal (oszlopokkal) kötjük össze. Az ilyen oszlopokat kulcsoszlopoknak, (kulcsmezőknek) is szoktuk nevezni.
5.7
Egyedtípus és reláció kapcsolata
Valójában minden egyedtípus egy relációnak felel meg. Ezzel vége is van a hálós (hierarchikus) illetve a relációs adatmodell közötti kapcsolatnak. Miben áll a különbség ettől a ponttól kezdve? Abban, hogy amíg a hálós (hierarchikus) modellben az egyedtípusok közötti kapcsolatot is megadjuk, azaz két egyedből egy settípust hozunk létre és listaszerkezettel valósítjuk meg az előfordulások kapcsolatait, vagyis pontosan megmondjuk, hogy a tulajdonos egyedtípus mindegyik előfordulásához a tag egyedtípus melyik előfordulása tartozik addig a relációs modellben nem rögzítjük a kapcsolatot. Természetesen, mint az előbbi pontban láttuk a relációs adatbázis táblái között is van kapcsolat (azonos tartalmú oszlopokon keresztül) de az egyik reláció soraihoz (rekordjaihoz) nem fűzzük hozzá a másik reláció sorait.
17
Ezt úgy is mondhatjuk, hogy a hálós adatmodellben az egyedek előfordulásai között statikus kapcsolat van, amit az ezekkel dolgozó adatbáziskezelő rendszerek ki is használnak, addig a relációk között ez a kapcsolat nem statiku hanem dinamikus.
18
6 A relációs adatbázis belső szerkezete A reláció (a tábla) voltaképpen egy adatok közötti struktúra. A szerkezet mélyebb" elemzése az adatbázisban szereplő adatok közötti fontos kapcsotokra, összefüggésekre mutathat rá. Kiderülhet például, hogy az attribútumok nem mind "egyenlőek" az adatbázis szempontjából; bizonyos attribútumok (vagy ezek egy csoportja együtt) kitüntetett szerepet kapnak. Előfordulhat, hogy az attribútumok között valamilyen kapcsolat van, például, ha attribútum valamilyen két sorban megegyezik, akkor egy másik attribútum is ezt teszi.
6.1
A funkcionális függőség - Adatok közötti funkcionális kapcsolat
Definíció Ha egy relációnak egy P-vel jelölt attribútuma olyan, hogy minden egymástól különböző értékéhez a reláció Q attribútúmából különböző értékek tartoznak, akkor azt mondjuk, hogy P funcionálisan meghatározza Q –t, azaz Q funkcionálisan függ P-től. 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. 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. 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 attributum is állhat. Például az AUTÓ_RENDSZÁM -> TÍPUS, 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. Az is előfordulhat, hogy két attributum 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ég esetén nem teljesül ☺. A funkcionális függőség bal oldalán több attributum is megjelenhet, melyek együttesen határozzák meg a jobb oldalon szereplő attributum é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 attributumok között: HELY, IDŐPONT -> HŐMÉRSÉKLET. 6.1.1
Teljes funkcionális függőség
Definició: Teljes funkcionális függőségről beszélünk, ha P-től függ Q attribútum, de P-nek semmilyen részhalmazától nem függ funkcionálisan. (pl P egyszerű adat!) 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 attributum. Például a RENDSZÁM, TÍPUS -> SZIN 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 szinét, ehhez nincs szükség a típusra is. Teljes funkcionális függés viszont az alábbi: RENDSZÁM -> TÍPUS vagy RENDSZÁM -> SZIN 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=({attributumok},{funkcionális függőségek listája}) Például: 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.
19
6.1.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 mindegyike 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 attributumhoz egy másik attributum 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 attributum értékéből egynél több további attributum értéke következik. Az előző példát bővítve: SZEMÉLYI_SZÁM ->> SZAKMA, OKLEVÉL_KELTE 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.
6.2
Reláció kulcs fogalma
Definíció. Az A attribútumhalmaz egy K részhalmazát kulcsnak nevezzük ha a K értékei az R reláció mindegyik sorát egyértelműen meghatározza, de ha egyetlen attribútumot is elhagyunk K-ból, akkor ez már nem teljesül. Egy reláció kulcsa tehát olyan attribútum-csoport, amelyben nincs olyan két sor, amelyeknek a kulcsban szereplő attribútumok értékei azonosak volnának. Ha K egyetlen attribútumból áll, akkor a kulcsot egyszerűnek nevezzük, ha nem ilyen, akkor összetett. Nyilvánvaló, hogy egy relációban mindig van kulcs (esetleg a teljes attribútumhalmaz). Az is világos, hogy egy relációnak több kulcsa is lehet. A reláció attribútumai közül azokat, amelyek legalább egy kulcsban szerepelnek, elsődleges attribútumoknak, a többieket másodlagosaknak (nem elsődlegeseknek) nevezzük. A reláció kulcs a reláció egy sorát azonosítja egyértelműen. A reláció - definició 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ő attributumok egyetlen részhalmaza sem alkot kulcsot • a kulcsban szereplő attribútumok értéke nem lehet definiálatlan (NULL) Egy relációban tartsuk nyilván az osztály tanulóinak személyi adatait SZEMÉLY_ADATOK=({ SZEMÉLYI_SZÁM, SZÜL_ÉV, NÉV}). A SZEMÉLYI_ADATOK relációban a SZEMÉLYI_SZÁM attributum kulcs, mert nem lehet az adatok között két különböző személy azonos személyi számmal. Előfordulnak olyan relációk is, melyekben a kulcs több attributum é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)} 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 analízisé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
20
KONZULTÁCIÓ=({TANÁR, IDŐPONT, DIÁK)} 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 attributumok kulcsot alkotnak, de ugyanilyen okból kifolyólag a DIÁK, IDŐPONT attribútumok is kulcsot alkotnak.
6.3
A külső kulcs
Külső kulcsnak (vagy idegen kulcsnak) nevezzük egy relációnak azokat az attribútumait, amelyek egy másik relációban kulcsot alkotnak. Külső kulcs nem azonosítja a rekordokat, tehát nem valódi kulcs, hacsak a táblák sorai közötti kapcsolatot fejezi ki. 6.3.1
Külső kulcs ugyanabban a táblában (Rekurzív kapcsolat)
A relációs adatmodellben megengedjük, hogy egy külső kulcs ugyanabban a táblában szerepeljen mint amiben maga a kulcs. 6.3.2
Külső kulcs párhuzamos kapcsolatban
A külső kulcs egy táblázatban többféle minőségben, jelentéssel is szerepelhet. Ilyenkor természetesen a külső kulcsnak annyi nevet adunk, amennyi jelentése van. 6.3.3
Külső kulcs egy-egy típusú kapcsolatban
Előfordulhat, hogy két táblázat kapcsolata olyan, hogy a külső kulcs és a között 1:1 kapcsolat van. Külső kulcsot vagy kulcsokat is megkülönböztetünk egy relációban. Ezek az attributumok 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. 6.3.4
Generátorhalmaz:
P attribútumhalmaz generátor, ha P-től függ Q attribútum halmaz és P-nek és a P-Q kapcsolatnak kevesebb eleme van, mint Q. A tényleges tervezés ismertetése előtt korábbi fogalmakat konkretizálunk.
6.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 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
Tantárgy
Össz_óraszám
Tanított_órák
Fábián Zoltán
Adatbázis kezelés
64
12
Szűcs Gabriella
Matematika
32
8
Dulácska Ildikó
Adatbázis kezelés
64
4
Bencze Zsolt
Matematika
32
5
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.
21
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ó papir
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
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. Az ilyen mezőket számított mezőknek hívjuk. Levezetett adatokat tartalmazhat egyetlen reláció is abban az esetben, ha egyes attributumok értéke egyértelműen meghatározható a többi attributum 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} Fizikai redundanciáról beszélünk, ha az adatokat többszörösen tároljuk egy reláción belül vagy több relációban. Logikai redundanciáról beszélünk, ha különböző relációkban tárolunk azonos adatokat. A logikai redundancia alkalmas külnböző relációk közötti kapcsolóattribútum szerepére, ezért ilyenkor kívánatos. Ezt az esetet gyenge logikai redundancának hívjuk egyébként erős logikai redundanciáról beszélünk.
6.5
Normálformák – a redundancia megszűntetése
Az adatbázis-tervezés folyamatában az adatbázisban leképezendő rendszert elemzésnek vetjük alá és meghatározzuk a tárolandó adatok körét, azok egymás közötti kapcsolatait és az adatbázissal szemben felmerülő igényeket. Ezután következik a rendszertervezé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. 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. 6.5.1
Első normál forma (1NF)
Egy reláció első normál formában van, ha minden attribútuma egyszerű, nem összetett adat. Másképpen az adatbázis minden oszlopában csak egy értéket tárolunk. 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
Tanár
Diákok
Számítástechnika
Nagy Pál
Név
osztály
Kiss Rita 22
III.b
Álmos Éva Video
6.5.1.1
Gál János
Név
II.c
osztály
Réz Ede
I. a
Vas Ferenc
II. b
Hogyan hozzuk 1NF-re egy relációt:
1. lehetőség: Ha több értéket tartalmaz egy mező egy sorban, akkor annyi sorra bontjuk a sort, ahány értéket tartalmaz a mező. 2. lehetőség: Két vagy több relációra bontjuk az eredeti relációt és közös azonosítóval hivatkozunk a közös értékekre. Az egyikben az azonosító kulcs lesz, a többi relációban pedig a külső kulcs lesz. 6.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 pontos 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 attributuma teljes funkcionális függőségben van az összes reláció kulccsal. Vagy • A reláció első normál formában van és a reláció (tábla) minden egyes sora egyedileg azonosítható legyen. Gyakorlatban ez azt jelenti, hogy egyszerű értékek szerepelnek a mezőkben és minden másodlagos attribútum teljes funkcionális függőségben van. Megjegyzés: Ha a kulcs egy attributumból áll, akkor a reláció 2NF. Ha csak egy mező van a relációban, akkor 2NF. 6.5.2.1
Hogyan hozzuk 2NF-re egy relációt?
Kiemeljük a kulcsból azokat az attribútumokat, amelyek önállóan is meghatározzák a másodlagos attribútumokat. Az előző lépésszerint összetartozó elsődleges és másodlagos attribútumokból reációt állítunk elő. Azokat a másodlagos attribútumokat, amelyek csak a kulcstól függ a kulcsban szereplő elsődleges attribútumokkal egy táblába fogjuk össze. 6.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 attributumokkal állnak kapcsolatban. Ezt a lehetőséget szünteti meg a harmadik normál forma definíciója. 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 attributumok között. Vagy • A reláció második normál formában van. • A tábláinkban nem lehet olyan redundáns (ismétlődő) nem-kulcs információ, amely más táblákban is nemkulcs információt jelent. 6.5.3.1
Hogyan hozzuk 3NF-re egy relációt?
Megszüntetjük a másodlagos attribútumok közötti funkcionális függőséget, azaz szintén több relációra bontjuk az eredeti relációt.
23
6.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 definiciója természetesen alkalmazható a több kulccsal rendelkező relációkra is. Ebben az esetben minden attributum, mely valamely kulcsnak a része, elsődleges attributum, de ez az attributum függhet egy másik, ezt nem tartalmazó kulcs részétől. Ha ez a helyzet fennáll, redundaciát tartalmaz a reláció. Ennek a felismerése vezetett a harmadik normál forma egy szigorúbb definiciójához, a Boyce/Codd normál formához. Minden elsődleges attributum teljes funkcionális függőségben van azokkal a kulcsokkal, melyeknek nem része 6.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, ha egy XY többértékű függőséget tartalmazó relációban csak az X és Yban megtalálható attributumokat tartalmazza. Képzeljük el azt, hogy egy relációban tároljuk az apa, és gyermekeinek nevét valamint a gyermek hobbiját. Minden apának több gyermeke és minden gyereknek több hobbija is lehet. 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. 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.
6.6
Denormalizálás
A denormalizálásnak az adatbázis működési hatékonyságában vagy biztonságában van szerepe. Ilyenkor tudatosan redundanciát viszünk az adatbázisba. Le kell szögezni, hogy a denormalizálás nem feledékenység. Mikor és hogyan célszerű a folyamatot véghezvinni? a) Ha egy számított adatot letárolunk egy adatoszlopban, akkor lekérdezéseink egyszerűbbekké válhatnak, ezáltal természetesen gyorsabbá is. A megoldandó kérdési lyenkor az, hogyan értesül az adatbázis számított oszlopa a számítás alapjául szolgáló mezők értékének változásáról. A válasz a triggerekben keresendő, amit majd az SQL leírásának végén olvahatunk. A lényeg az, hogy ezek a szerveren futó eljárások automatikusan értesülhetnek értékek megváltozásáról és azok segítségével más rétékeket módosíthatnak. b) Ha egy vagy néhány segédoszlopot beviszönnk az adatbázisba a lekérdezéseink bonyolultsága ugrászserűen csökkenhet és a sebessége is drámaian nőhet. c) Ha visszamenőleg tárolunk adatokat, és ezek utólag már nem változnak, az ilyen adatokat célszerű egy csak olvasható táblába elhelyezni, nem kell azokat napra készen a lekérdezésekben újra és újra szerepeltetnünk. d) Ha az adatbázis felhasználói saját maguk készítenek lekérdezéseket gyakran elveszhetnek a normalizált táblák útvesztőiben, esetleg tudásuk hiányosságai vagy a probléma bonyolultsága miatt. Ilyenkor célszerű lehet a megfelelő denormalizálási eljárásokat megalkotni, 6.6.1
A normalizáláson túl - irányelvek
Legyenek a megoldásaink egyszerűek. Ha az adatbázis kezelő alkalmas tárolt eljárások, triggerek készítésére és alkalmazzuk is azokat, továbbá az alkalmazás kliens oldali részébe is telepítünk adatmanipulációs kódokat könnyen áttekinthetetlen megoldások születhetnek belőlük.Célszerűen jól bevált módszereket alkalmazzunk, és ha ötletre van szükség előbb azért vizsgáljuk meg, hogy hagyományosabb, egyszerűbb módon nem lehet-e elvégezni a feladatot. Adattípusaink kiválasztásánál legyünk tekiintettel a tárolókapacitásra. Itt nem csak a háttértárak tárolókapacitásáról van szó, hanem egy lekérdezés eredménye ámgy a hálózaton, akár többször is, továbbá a kiens alkalmazásoknak is fel kell dolgozni az adatokat. HA lehet ne használjunk unicode alapú szövegeket és a mezők mérete is legyen ehetőleg nem túl nagy. Használjunk dinamikus méretű adattípust (varchar) Inább több adatot tároljunk, mint kevesebbet. Ez az irányelv látszólag az előzővel ellentétben áll. Valójában arról van szó, ha nem szükséges régi adatokat ne töröljünk. Ha egyzser egy rendszerben rögzítettünk adatot, az adat pedig megváltozott, akkor célserű a változást úgy rögzíteni, hogy az előő állapot is megmaradjon és a változás időpontja is legyen rögzítve az adatbázisban.
24
6.7
Anomáliák az adatbázisban
Az egyes normálformákkal az adatbázisban meglévő bizonyos anomáliák szüntethetők meg. Ezek közül most azokkal foglalkozunk, amelyek a 2NFre hozással szűrhetők ki. 6.7.1
Módosítási anomália
AElőfordulhat; hogy valamely attribútum értékeit módosítani kell. Ha például az ELADÁS adatbázisban a termékkódokon (TKÓD) akarunk változtatni, akkor a TKÓD oszlop minden elemét meg kell vizsgálni, és minden olyan sorban végre kell hajtani a módosítást, amelyben a módosítandó kód szerepel. A TERMÉKI relációban viszont a TKÓD oszlopban minden kód csak egyszer szerepel, tehát csak egy helyen kell módosítani. Azt mondhatjuk tehát, hogy a 2NF-re hozással a módosítási anomáliát szüntettük meg. 6.7.2
Törlési anomália
Az adatbázisunk lehet olyan, hogy ha benne egy sort törlünk, alapvető információkat veszítünk el. Ha pl. az ELADÁS adatbázisban {082, 110} kulcsú rekordot töröljük, akkor ez a 110-es videóra vonatkozó információk elvesztését eredményezné. Viszont ha a törlést az ELADÁS2 adatbázisban végezzük el, nincsen baj, mert a TERMÉKI adatbázisban megmarad ez az információ. A 2NF-es forma tehát megszünteti ezt az anomáliát. 6.7.3
Bővítési anomália
AA bővítési anomália fogalmát is példánkon mutatjuk be. Tegyük fel, hogy az ELADÁS relációt egy új információval kellene bővíteni, mondjuk a hajszárító termékkel, aminek kódja: 002; ára: 2000 Ft. Ezeket az információkat az eladó ismeri, de nem tudja beírni az ELADÁS relációba, mert eddig még nem adtak el ilyen terméket, (nem készült róla számla) és így nincsen kulcsunk rá. A TERMÉKI relációba viszont könnyen bevihetők ezek az adatok. Ez azt jelenti, hogy a 2NF-es relációval elkerülhető a bővítési anomália. 6.7.4
Inkonzisztencia
Egy adatbázis akkor inkonzisztens, ha egymásnak ellentmondó értékeket tartalmaz. Ilyen esetek jöhetnek létre akkor, ha egy adatbázis frissítésekor, módosítás törlés vagy új érték felvitelekor a kapcsolatban lévő relációk tábláit nem módosítjuk megfelelően. 6.7.5
Tranzakció
A tranzakció általános fogalom. Amikor egy rendszer egy kiinduló állapotból átalakul egy végső állapotba, az egy ideig eltart és az átalakulás során instabil állapotban van, vagy lehet. Ha az átalakulás folyamata megszakad valamilyen külső okból, vagy hibás működés eredményeként, akkor a kiindulási állapot már nem érvényes, a cél állapotot pedig még nem érte el a rendszer. A rendszer inkonzisztenssé válhat, illetve a rendszer integritása sérülhet. Ez adatbázisok esetén azt jelentheti, hogy egyes mezők, rekordok vagy akár egész táblák tartalma nem lesz megfelelő, vagy megsérül, adatvesztés a következménye. A tranzakció a rendszerbe beépített olyan alrendszer, amely az állapotváltozás előtt automatikusan elmenti az állapot paramétereit, és csak akkor törli az előző állapot elmentett értékeit, ha a tranzakció sikeresen lezajlott, vagyis a cél állapotot elértük. Hiba esetén automatikusan visszaállítja a kiinduló állapotot a rendszer.
6.8
Az adatmodell időfüggvényének szerepe az adatmodell kialakításában.
A törzsadatok olyan adatok, amelyek a rendszer életciklusának jelentős részében nem változnak. Ilyen esetekben el lehet térni olyan elvektől, amelyek általában igazak a táblákra. Lehetséges szótárakat létrehozni, amelyek azonosítására például nem természetes kulcsokat használunk, hanem például magát az adat elnevezését, mivel boztisítani tudjuk a rendszerben a megfelelő, általános egyediséget. Felmerül a kérdés, hogy az adatok archiválásakor vajpon szükséges-e ezeket a táblákat is archiválni? A váalasz nem, mivel ezeknek a tábláknak az értéke statikus.
6.9
Metaadatok tárolása az adatbázisokban
Azokat az adatokat, amelyek egy adatbázis szerkezetét, az egyes adatok jelentését, formátuát, tulajdonságait írják le metaadatoknak hívjuk. A metaadatok tehát az adatokról szóló adatok. Mikor lehet ilyen adatokra szükség? Az adatbázisok tervezése, a programfejlesztés során előforduk, hogy az adatbázisok szerkezete módosul menet közben, ugyanakkor a megjelenítési forma nem változik, illetve igazodnia kell az éppen aktuális szerkezethez – változatlan programfelépítés mellett.
25
Lehetnek továbbá olyan adatok, amelyek attól függően, a tárolt objektum jellegétől függően jellemeznek egyes objektumokat, míg másokat nem jellemeznek, – mégis közös módon kell megjeleníteni, illetve kezelni ezeket az objektumfajtákat. Az adatok célszerűen egy adatkezelő alkalmazáson belül mindig ugyanabban a formátumban, validálási feltételek mellett jelennek meg az alkalmazás minden részén. Ezekben az esetekben célszerű az adatokra vonatkozó tulajdonságokat metaadatokként tárolni az adatbázisban, és az alkalmazás működését a metaadatok vezérlik. Akkor, amikor az adatbázis metaadatai vezérlik az adatbázis-kezelő alkalmazást adatvezérelt programoknak hívjuk.
6.10 Fizikai tervezés Ha egy saját tervezésű szoftverrel óhajtjuk kezelni adatainkat a fizikai tervezés szintjébe bele kell, hogy értsük annak az eszközrendszernek a tervezését is, amely a file-ok fizikai kezelését végzi. Ilyenkor meg kell tervezni a fileszerkezetet is, az elérés módját, a rekordok címzését, az indexelés módját, az elérési útvonal kezelését stb. Mivel a modern rendszerek ezeknek a terheknek a jó részét leveszi a vállunkról ezért céldszerű az adatbázis-kezelési feladatokat DBMS-ekkel megoldani. Köztes megoldás az olyan rendszerek használata, mint a CLIPPER, DBASE, stb, amely bizoyos funkciókat levesz a programozó válláról. Az adatbázis táblák tervezése és létrehozása már nem a programozó szoftverének a feladata, de a fizikai elhelyezkedés a könyvtárstruktúrában, az adattáblák elérése, az indexek megnyitása, bezárása a programozó feladata. 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ázis-kezelő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ő manipulációkhoz 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. 6.10.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 hanjgsú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. 6.10.2
Hash kódok
A hash kódokat manapság már csak hálós vagy hierarchikus modellben használják. 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, melynek alapján egy táblázatból kiolvasható a keresett értéket tartalmazó sor fizikai címe vagy egy számítási algoritmus adja meg a keresett értéket. A hash kód számítási algoritmusa nem mindig ad különböző értékeket az index kulcsokra. Ennek oka, hogy csak véges hosszúságú hash táblát tudunk kezelni. Minnél jobb a hash algoritmus, annál kevesebb lesz a kulcsütközés. 6.10.3
A hash kód kulcsütközés kezelése
Azokban az esetekben amikor különböző kulcsértékekhez ugyanazt a címet adja, ezeket a különböző kulcsú rekordokat szinonimoknak mondjuk. A szinonim rekordokat kezelni kell (Például külön táblákba tesszük a különbözö betűvel kezdődő neveket, és ott más módszerrel finomítjuk a keresést. Ez az elosztott altáblák módszere) Nyilván annál jobb egy hash leképezés minél kevesebb szinonim van. Példa hash leképezésre: Alkalmasan nagy prímszámmal osztjuk a numerikusnak tekintett kulcsot, és a maradék lesz a cím. Kulcs szerinti közvetlen feldolgozásra a jó hash leképezés hatékonyabb az indexnél, ezért a hash leképezést előszeretettel alkalmazták a hierarchikus és hálós modellben. A relációs adatbázisoknál nem használják, mert: nem tesz lehetővé szekvenciális elérést
26
csak kulcs szerint lehet keresni, egy rekord más mezője szerint nem. Az azonos kódot adó kulcsokat összeláncolják a hash táblá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 gyors. 6.10.4
Bináris fa
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. 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 öszszehasonlí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ének megfelelő számút. Mivel a PC-s rendszerek olvasási egysége 2-4-8-16-32 vagy 64 kb, ezért egy lap általában ekkora. 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 tulajdonképpen a bináris fa Bal-Közép-Jobb vagy Jobb-Közép-Bal bejárását jelenti. Ez hash kód esetén nem lehetséges. 6.10.5
Asszociatív rendszerek
Az utóbbi évtizedben kialakult és nem túlságsan széles körben elterjedt rendszerek, amelyek hardver szinten támogatják a memóriatartalom alapján az adateléréseket. Ezeket asszociatív memóriának hívjuk. A operációs rendszerek tárgykörben volrt rüla szó. Ezek segítségével lehet olyan tárolási és keresési módokat létrehozni, amelyek a tartalom alapján adnak eredményeket. A gyakorlatban speciális célhardver és célszoftver segítségével lehet alkalmazni csak őket. 6.10.6
A hálózati adatkezelés problémái
Az adatbáziskezelési feladatoka valós helyzetekben bonyolódnak, amikor hálózati hozzáféréssel kezeljük az adatbázisokat. Az alapszituáció az, hogy egyidejűleg többen is írnak az adatbázisba és olvasnak onnan. A filekezelő rendszerek általában a file-ok megnyitását kizárólagos módon (exclusive) engedélyezik, ami azt jelenti, hogy többen ugyanazt a file-t írásra nem nyithatják meg. Az adatbáziskezelők a filekezelő rendszerekkel ellentétben megosztott hozzáférést (share) engedélyeznek, ami azt jelenti, hogy egy időben többen is módosíthatják az adatbázist. Milyen problémák merülhetnek fel? Egyidejűleg olvasnak többen egy táblából. Ez nem probléma, mivel a kiolvasás nem változtatja meg az adatok tartalmát. Valaki olvas egy adatbázis táblából, más valaki ír bele. Ez problematiks, de az adatbáziskezelő rendszerek ilyenkor az éppen olvasott rekordra úgynevezett olvasási zárolást (lock)-ot alkalmaznak, ami azt jelenti, hogy amíg a rekord tartalmát teljesen ki nem olvassa a rendszer az alatt az idő alatt más nem módosíthatja a rekord tartalmát. Ezt a Clipper, DBASE stb esetén a programozónak külön le kellett programoznia, majd az olvasás végrehajtása után ki kellett kapcsolnia. Ekkor persze felmerül a kérdés, hogy az olvasás után következő írásról, és ezáltal a megváltozott tartalomról hogyan értesül az olvasást végrehajtó. Általában csak akkor értesül, ha mégegyszer kiolvassa az adatokat. Nagyon speciális rendszerek esetén a hálózaton keresztül nyitvatartott kapcsolatokat felhasználva az adatbáziskezelő képes értesíteni a klienseket a változásról és azok újraolvassák a megváltozott rekordokat. Van olyan rendszer, amely a rekord szintű zárolás esetén engedi azoknak a mezőknek a módosítását, amelyeket nem olvas más a táblából! A fenti eset egy speciális válfaja, amikor új rekordot visznek fel a táblába olvasás közben. Mivel az új rekord nem lehet még az olvasás tárgya, ezért azt szabadon megteheti a rendszer. Többen akarnak írni egy táblába. Amennyiben ezek az írási folyamatok különböző rekordok módosítását jelentik, akkor nyilván elvileg elképzelhető. Ekkor rekordszntű zárolást alkalmaznak a rendszerek és az írás végeztéig az adott rekord nem írható és olvasható más folyamat által. Ha a zárolt rekordot módosítani akarja egy másk folyamat, akkor kénytelen várnia, az előző folyamat írásának befejeztéig. Többen egy időben több rekord (akár az összes) tartalmát ismódosítani akarják. Ebben az esetben a rendszerek táblaszintű zárolást alkalmaznak, ami azt jelenti, hogy a tábla tartalmához más nem fér hozzá a módosítás ideje alatt. A táblaszintű zárolás időtartama hosszú is lehet, mivel a tábla méretét elvileg nem korlátozza semmi. Minden olyan esetben, amikor a zárolás miatt egy másik folyamat kénytelen várni, akkor a várakozó folyamat ciklikusan megnézi, 27
hogy haszálhatja-e már a rekordokat. A várakozás nem tarthat a végtelenig, mert sajnos ez az eset nem különbözik a hardverhibától, ezért a folyamatok túl hosszú várakozás esetén holtponti helyzetbe juthatnak. (Lásp Operációs rendszerek) Ennek a megoldása érdekében a folyamatok egy idő mlva timeout-ot jeleznek, amely után felaszabadítják a lefoglalt erőforrásaikat, „elszáll” a rendszer. A timeout helyzetek elkerülése érdekében a teljes táblák zárolását csak olyanesetben szabad és szokták megengedni, amikor biztonságosan és gyorsan futhat le az illető folyamat. 6.10.7
Tervezés a feldolgozási módok szerint
Interaktív (real-time vagy on-line) esetben fontos, hogy a kérdésekre adandó válaszok ideje megfelelően rövid legyen. Ez a fajta feldolgozási mód átlagosan kevésbé terheli le az adatbázis eredményét szolgáltató számítógépeket! Ha egyidejűleg sokan végeznek ilyen feldolgozást, akkor a válaszidők növekedhetnek. Ilyenkor az adatbázis szolgáltató hardvert, szoftvert jelentősen túl kell tervezni, hogy minden körülmények között is elfogadható válaszidőket produkáljon. Jellemzően hálózatokon keresztül érkeznek a válaszok a kliens gépekhez, a hálózatoknak az átviteli kpacitása véges, ezért az ilyen rendszerek alapvetően kliens-szerver üzemmódban működnek, és nagy szerepe van az adatok optimalizálásának! Batch jellegű rendszerekben a kéréseket ütemezni lehet, ezáltal a feladatok akkor hajtódnak vége, amikor van rá elég erőforrás, processzoridő, memória, stb..., ezáltal nagyobb átlagos kihasználtságot lehet elérni, azonban a válaszidők a választott erőforráskezelési stratégiától függően változóak, de jellemzően hosszabbak is lehetnek. Ha az adatokat off-line kérdezzük le, az azt jelenti, hogy a kapcsolatok csak időnként élnek, az adatokat egy helyi rendszerben is rtárolni kell, és rendszeres időközönként szinkronizálni kell az adatokat a központi rendszerrel, adatbázissal. A szinkronizációhoz természetesen az egyes tranzakciók időpontját is rögzíteni kell, mivel a frissítések oda-vissza meg kell, hogy történjenek és mindig az utolsó módosítás lesz a mérvadó versenyhelyzetben. Ilyenkor óriási szerepe van a szinkronizációs mechanizmusnak. A szinkronizációt olyan időben kell elvégezni, amikor lehetőleg kicsi az adatbázis forgalma. Biztosítani kell a tranzakciós kezelést a szinkronizáció közben, hiszen az siokáig is eltarthat és a kapcsolat menet közben megszakadhat. Elosztott adatbázisokról (distributed) beszélünk, amikor az adatbázis egyes részeit nem közös helyen tároljuk. Az egyes részek közötti kapcsolat, a frissítési, szinkronizációs kérdéseket itt is meg kell oldani. Nyilván úgy érdemes elhelyezni az adatbázis egyesrészeit, hogy azok a feldolgozás helyéhez „közel” legyenek. Érdemes a különböző helyeken lévő adatbázisok fizikai szerkezetét, kezelési módját egységessé tenni. 6.10.8
Szinkronizálási módszerek
Az adatbázisok szinkronizálása elosztott adatbázisoknál vagy olyan esetekben szükséges, amikor egy adatbázis tükörképeit helyezem el különbző szervereken. Érdekes módon a szinkronizálás nem csak az adatbázis-kezelés terén felmerülő univerzális probléma. Ilyen esetek például: • Filerendszerek szinkronizálása • Levelezőrendszerek szinkronizálása • WEB site-ok szinkronizálása • Felhasználói adatbázisok – Novell NDS vagy Microsoft Active Directory – szinkronizálása Párhuzamosan egy projekten együtt dolgozó fejlesztők munkáinak szinkronizálása Az alapprobléma minden esetben az, hogy fizikailag különböző helyeken előállnak adatok, amelyeknek szerkezete ugyanaz, tartalma viszont egymástól függetlenül változik. A rendszernek automatikusan biztosítania kell a független adatállapotok időnkénti szinkronba hozását. A kiindulási alap mindig az, hogy a kettő vagy több helyen lévő adathalmaz állapota megegyezik. A használat során az alábbi műveleteket végzik az adatokon. • Az adatokat lekérdezik – Ez szinkronizálási szempontból nem érdekes, csak akkor, ha a különböző helyeken más példányokat listáznak ki a felhasználók. • Új elemeket vesznek fel. - Ha egy listába új elemet veszünk fel, akkor a szinkronizálás után az új elemek hogyan helyezkednek el, milyen azonosítót kapnak, lehet-e ütközés (kulcsütközés) az egyes felvitt elemek között? • Meglévő elemeket törölnek – Az törölt elemek törlődjenek ki máshonnan is, mivel ilyenkor máshol esetleg módosítottak az eredeti példányon. • Meglévő elemeket módosítanak - Ha ugyanazt az elemet módosítják, akkor mi legyen a tartalma a szinkronizáláskor létrejövő végleges példánynak? A szinkronizálást rendszerint olyan időben érdemes elindítani, amikor az asatbázis-kezelő rendszer egyéb terheltsége kicsi, tipikusan az éjszakai órákban, munkaidőn túl. Itt akkor van probléma, ha az egyes adattárak olyan távol vannak egymástól, hogy – pldául a Föld két átellenes pontján, hogy nincsen olyan időpont, amikor a rendszerek munkaidőn túl vannak együtt.
28
A szinkron kezdeményezése mindig két oldalú. Ha több adatforrás szinkronját kell megoldani, akkor is felbontható ez két oldalú szinkronizálási folyamatok sorozatára. A szinkronizálás alapja általában az, hogy a módosításokat az adatforrás letárolja időpont szerinti sorrendben és az így letárolt adatokat cseréli ki a másik szerverrel. Ennek oka az átküldendő adatok minimalizálásának igénye. Megoldási lehetőségek. A továbbiakban csak adatbázisok esetében tárgyaljuk a megoldásokat, de más rendszerek esetén is hasonló megoldások vannak 6.10.8.1
Master-Slave modell
A legegyszerűbb modell. Egy Master adatbázis van. Itt zajlik minden módosítási jellegű művelet. Minden más helyen csak lekérdezések lehetnek. A Módosítások átvitelét a Slave rendszerekre vagy a Master rendszer vagy a Slave rendszerek kezdeményezik, rendszeres időközönként. A lekérdezések mindig azt az állapotot mutatják a Slave rendszereken, amelyek a legutolsó szinkronizáláskor létrejöttek. 6.10.8.2
Két oldalú szinkronizálási modell
Ebben az esetben mind a két oldalon vannak módosítások. A lekérdezések két szinkron között eltelt időben az adott adatforrás állapotát mutatják. • A módosított rekordok végső állapotát az adott rekordon végzett utolsó módosítás időpontja adja meg. A beszúrt rekordok általában automatikusan vagy más módon kapnak kulcsértékeket. Ha két adatforrásban ugyanazt a kulcsérétéket kapja egy új rekord, akkor kulcsütközés jön létre. Ezt az ütközést gyakran automaitkus mechanizmus nem tudja feloldani. Ekkor az alábbi esetek vannak: • Az egyik szervernek prioritása van. – Az ilyen esetekben az azon felvitt új rekord a nyerő. Előnye az egyszerűség Hátránya, hogy a felvitt adatok egy része elveszik • A két rendszer hibalistát generál és az adminisztrátorra bízza az ütközés feloldását. Előnye, hogy adatok nem vesznek el. Hátránya, hogy nagy mennyiségű adat felvitele során az emberi beavatkozásra várás a hatékonyság rovására megy • Automatikusan módosítja az egyik vagy mindkét kulcsértéket, a különbözőség érdekében. Előnye a hatékonyság Hátránya az, hogy csak olyan kulcsnál lehet alkalmazni, ami algoritmikusan generálható. Tipikusan a számláló típusú kulcsmezőknél lehet. Számláló típusú kulcsmezők automatikus szinkronizálása azon az elven működik, hogyha két külön adatforrában a kulcsértékek automatikusan növekednek kihagyás nélkül, akkro mind a két adatforrásban létezik maximális kulcs. Az utolsó szinkronizálás óta eltelt időszakban az előző maximális kulcs feletti értékek kerültek be az adatforrásokba. Az így létrejött eltérő adatforrások maximális kulcsértékeinek maximumánál eggyel nagyobb értéket fel lehet használni új kulcsérték létrehozására. 6.10.9
Hálózati, adatbázisokkal kapcsolatos biztonsági kérdések
Az adatok általában nem nyilvánosak, ezért az adatokhoz való hozzáférés módját is szabályozni kell. A szabályozás alapja mindenkor: felhasználói név Itt is ugyanolyan neveket kell használni, mint általában a szervereken, különböző belépési szituációkban jelszó A jelszó természetesn hasonlóan , mint más heélyzetekben nehezen kitalálható, kis és nagybetűket és számokat tartalmazó lehetőleg zagyva karaktersorozat. Nem szabad leírni, nem szabad a hozzátartozók adatait használni. A jelszavak tárolását titkosítva egyutas titkosítással célszerű megoldani, ami annyit jelent, hogy nincsen a titkosító kódnak inverzfüggvénye. Hálózaton keresztül csak a titkosított jelszó közlekedhet. a felhasználói névhez tartozó jogosultságok Általában az adatbázisokhoz a jelszó egyúttal egy tipizálható hozzáférési jogosultságot is jelent. Lehetséges a hozzáféréseket adatbázisszinte, táblaszinten vagy rekordszinten is kezelni. Sőt előfordulhat az is, hogy mezők szintjén szabályozzuk a hozzáférést. A hozzáférések tipikus szintjei: • Nincs hozzáférés • Olvasási hozzáférés 29
• Módosítási hozzáférés. • Adminisztrációs hozzáférés Felmerül a kérdés, hogy miért nem az adatokat titkosítjuk? Nagy mennyiségú adat és intenzív használat esetén jelentős teljesítményromlást okozhat az adatok titkosítása, azonkívül esetleges rendszerösszeomás esetén a szervízfunkciók alkalmazását nehezíti. A védett anyagot általában olyan rendszeren helyezik el, amely védett az illetéktelen hozzáférés ellen, így az adatok nem kerülhetnek illetéktelenek kezébe.
30
7 Adatbázis-kezelőkben alkalmazható adattípusok Az adatbáziskezelő szoftverek napjainkra eléggé egységesekké váltak a bennük alkalmazható adattípusokat tekintve. Tipikusan az alábbi adattípusokat kezelhetünk ezekkel a rendszerekkel: Szöveg Általában maximum 255 karakter hosszú lehet. Az adatbázis definiálásakor meg kell adnunk a szöveg hosszát. Numerikus típusok Egyes rendszerekben külön kezelik az egész és a lebegőpontos típusokat, míg más esetekben közös a kezelés. Ennek megfelelően a numerikus típusoknál alapvetően egész típus esetén megkülönböztethetünk rövid egészet (1 byte-on tároljuk), egész típust (két byte-on tárolva) és hosszú egészet (4 byte-on tárolva). Ez megegyezik a legelterjedtebb általános célú programozási nyelvek típusaival, sokszor még az elnevezés is ugyanaz. Ezen adattípusok méretei adottak. A lebegőpontos típusok tárolása esetén meg kell adnunk, hogy hány tizedesjegy pontossággal szeretnénk tárolni az adatokat és maga az adat hány jegy pontosságú legyen. Adatbázis-kezelő rendszertől függően lehet beállítani a maximális értékeket és a rendszerektől függ a belső ábrázolás mikéntje is. Például a Clipper DBASE stb... rendszerek esetében a számokat nem binárisan kódolva, hanem ascii kódokkal leírva tárolják az adatbázisban. Numerikus típust akkor alkalmazunk egy adatbázisban, hogyha a mező értékével matematikai, logikai vagy statisztikai műveleteket kell majd végrehajtanunk! A logikai értékek egy speciális fajtája a currency, azaz pénznem. Ez csak abban különbözik a többi numerikus értéktől, hogy formátumában hordozza az illető ország vagy az előre beállított pénznem megjelenési formáját is, továbbá csak két tizedes pontossággal számol. Dátum, idő A numerikus típus egy speciális fajtája, ugyanis a dátumokkal számolni lehet! A dátumoknak speciális formájuk van. A tárolás belső formája lehet string vagy numerikus, de minden esetben megkülönböztetjük az előbbi adattípusoktól, csak konverziós függvényeken keresztül lehet a fenti adattípusokba átkonvertálni és vissza. A dátum típusú tárolási egység megadásakor meg kell határoznunk a dátum formátumát. Általában megkülönböztethetünk rövid és hosszú dátumot, amely különbözhet az évszámok kiírásában, vagy különbözhet a hónap és a hét napja kiírásában. (2001.01.12 rövid alak, 2001 január 12, szombat – hosszú alak) Különböző nyelvi területeken természetesen az adott területre jellemző dátumformátumokat határozhatunk meg, amennyiben az adatbázis-kezelő ezt lehetővé teszi. A dátum típus mérete mindig adott, tehát a formátum meghatározásával egyúttal meghatározzuk az elfoglalt terület nagyságát is. Az idő érték általában a dátum érték része, vagy legalábbis a két fajta adattípus konvertálható egymásba. Az idő kezelése során 12 vagy 24 órás napi idővel számolhatunk, illetve egyes rendszerekben a délelőttöt és délutánt a DE, DU vagy angolszász esetben AM, PM jelekkel jelöljük. Logikai érték A jól ismert Logikai változóknak megfelelő érték. Egyes rendszerekben ezt Igen-Nem, Boolean vagy hasonló névvel illetik. Két értéket tartalmazhat: Igaz-Hamis. A rendszerekben ezek szinonimájaként használatosak: Igen-Nem, YesNo, .Y.-.N., True-False, .T.-.F., vagy az 1, 0 illetve a nem nulla-nulla értékpárok. Az alábbi esetben biztosan Igaz, és biztosan hamis értéket tartalmaznak a kifejezések: (1 =1), (1 > 1) Ha nem vagyunk biztosak az értékadésban, akkor a fenti kis trükkel tudsunk magunkon segíteni! A logikai érték mindig 1 byte hosszúságú, bár ez így redundanciát okoz, mivel egy bit is elegendő lenne, de az ennél kisebb érték kezelése a mai modern számítógépeken sebességcsökkenéssel jár. Megjegyzés (MEMO) Nem minden rendszer tartalmazza az alábbi mezőtípust, de ahol igen ott az alábbiak igazak rá. Struktúrálatlan szövegek tárolására alkalmas adattípus. A mérete korlátos, de a korlát megfelelően nagy 1-4-16-3264 KB. Rendezésekben, lekérdezésekben ezt az adattípust nem célszerű használni. Kezelésekor stringből (szövegből) kell kiindulni. Általában a tárolás modja különbözik az egyéb adatok tárolásától, sokszor (Clipper, DBASE Foxpro) az megjegyzés adatokat külön file-ban kezelik a rendszerek, de legalábbis speciálisan helyezik el a file-ban. A megjegyzés adattípus változását sokszor nem helyben kwezeli le a rendszer, hanem inkább egy új példányt hoz létre az eredeti pédlány helyett, amiben a módosított értékekkel együtt tároja a régi adatokat, így az ilyen adattíypus használata az adatbázis méretének rohamos növekedésével járhat együtt. Csak indokolt esetben célszerű alkalmazni. BLOB 31
Bináris adatok, objektumok tárolására alkalmas adattípus. Nem minden adatbázis-kezelő rendszer alkalmazza, csak azok, ahol az objektumok kezelése megoldott az operációs rendszer más komponenseivel egyetértésben. A windows alatt kifejlesztett adatbáziskezelők többnyire ilyenek. Segítségükkel a rendszerben ,megadott bármilyen objektumtípust tárolhatunk az adatbázisban. Ennek a tárolásnak persze van egy hátránya: Az adatbázis mérete óriásira duzzadhat. Speciális függvények és kezelési módszerek vonatkozhatnak rájuk. Alternatívát jelenthet az, ha például képállományokat szeretnénk tárolni, ha nem magát a képet, hanem a képre mutató elérési utata tároljuk egy szöveg típusú mezőben, majd a képen végrehajtott módosításokat, műveleteket külső szoftver segítségével végezzük el.
32
8 Az adatbázis – rendszer dokumentálása A dokumentációnak az alábbiakat kell tartalmaznia: • • • • •
Az adatbázis rendszer által tárolt adatokat Az adatok belső és külső logikai szerkezetét Az adatmodell szerinti táblák felépítését, az adatok formátumát Adatszótár (Melyik mező mit jelent és mi a formátuma, esetleg mi a megjelenítésével kapcsolatos forma) Az adatbázis fizikai megvalósítását, a használt adatbázis-kezelő leírását, telepítését, a táblák generálásának módját • A használt lekérdezéseket, azoktól elvárt adatokat • A lekérdezések eredményeit megjelenítő listák riportok definícióját, formátumát • Az egyes felhasználó eljárások leírását és haszálatának módját illetve forrásanyag esetén a forrásanyagot. • Tesztelési eredményeket, a módszerek és a felhasznált adatok bemutatásával. • Az archiválás módját és menetét tartalmazó előírásokat • A rendszer indításához, leállításához és archiválásához, illetve módodításához szüksége mindenféle azonosítót és jelszót (biztonságos formában) Minden fenti dokumentációt lehetőség szerint írott és elektronikus formában is. Abban az esetben, ha az adatbázis-kezelő rendszert valamilyen standard vagy általánosan elterjedt fejlesztő rendszerrel végeztük vagy végezzük, akkor a rendszer újragenerálásához szükséges adatok, információk a rendszer által előállított generátor file-okban található. Ilyen esetben ezeket az állományokat kell letárolni, biztonságos módon, megadva azokat a körülményeket, esetleg a fejlesztői rendszer teljes telepítzőkészletével egyetemben. Ilyenkor célszerű használni a rendszer saját dokumentáló tulajdonságait is. Az utólagos módosítások tárolása is fontos feladat. A módosítások tárolására ma már vannak úgynevezett CSV jellegű rendszerek, amelyek a források megváltozását tárolják adatbázisban, visszakereshető módon. Adatbázis rendszer dokumentálásához jól használható programok léteznek. Általában minden adatbázis-kezelő kliens program képes az adatábázisok valamiféle rendszerezett megjelenítésére, kapcsolatokkal és egyé kiegészítő infomrációkkal. Általánosan jól használható program ilyen célra a Microsoft Visio, amellyel sok más rendszeren kívül adatbázisokat is jól dokumentálhatunk. Ez a program mellesleg UML modellezésre is alkalmas.
33
9 Az SQL lekérdező nyelv 9.1
Az SQL története, általános tulajdonágai
Az SQL alapjait az IBM-nél fektették le, még az 1970-es években. Elvi alapot a relációs adatmodell szolgáltatott, amit Edgar F. Codd híres 12 szabályával írt le először, 1970-ben. Az IBM, az Oracle és más gyártók is érdekeltek voltak egy szabványos lekérdező nyelv kifejlesztésében, amivel a relációs adatbázisok programozhatók lehettek. Az iparági összefogással létrejött ANSI NCITS (National Committee on Information Technology Standards) H2 csoport lerakta az SQL alapjait. A szabványt az ANSI (Amerikai Nemzeti Szabványügyi Intézet – American National Standards Institute) 1986-ban, az ISO (Nemzetközi Szabványügyi Szervezet – International Organization for Standardization) 1987-ben jegyezte be. Az első változatot SQL86 néven is szokták emlegetni. Az SQL-t folyamatosan továbbfejlesztették, és négy jelentős kiadást különböztetünk meg: • SQL86 • SQL89 • SQL92 • SQL99 Az első kivételével mindegyik szabvány többszintű megvalósítást tesz lehetővé a gyártóknak (belépő szintű, közepes vagy teljes). Általában a későbbi szabványok belépő szintjei az előző szabvány teljes szintjeinek felelnek meg. 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, PHP 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 az adott host nyelv illetve a konkrét adatbáziskezelő SQL dokumentációjában találhatjuk meg. Az SQL a következő elemekre osztható • adatdefiniciós nyelv (Data Definition Language – DDL) • 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 egy iskolai adatbázist használunk. 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.
34
9.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ő. 9.2.1
Adattípusok az SQL-ben
Az attributumokra megadható adattípusok a következő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. Más adatbázis-kezelő megvalósításokban olyan típusok is vannak, amelyek az SQL közös szabványában nem léteznek. Ilyenek például: 9.2.2
Adattípusok a MySql-ben
9.2.2.1
Numerikus típusok
Oszlop típus
Tárolási hossz
Leírás
TINYINT
1 byte
Rövid egész
SMALLINT
2 byte
Egész
MEDIUMINT
3 byte
Egész
INT
4 byte
Egész
INTEGER
4 bytes
Egész
BIGINT
8 bytes
Hosszú egész
FLOAT(X)
4 if X <= 24 or 8 if 25 <= X <= 53
Lebegőpontos típusok
FLOAT
4 bytes
DOUBLE
8 bytes
DOUBLE PRECISION
8 bytes
REAL
8 bytes
DECIMAL(M,D)
Ha D>0, akkor M+2 bytes Ha D = 0 (D+2, if M < D) M+1 bytes
35
Adott pontosságú típusok
NUMERIC(M,D)
Ha D > 0 akkor M+2 bytes Ha M+1 bytes if D = 0 (D+2, if M < D)
9.2.2.2
Dátum és időpont típusok
A dátumot és időpontokat a Mysql úgynevezett belső formában tárolja. Ez a belső forma egy adott dátum óta eltelt másodpercek számát jelenti. A különböző típusú értékek ugyanattól a kezdőértéktől számolódnak. Az egyes dátumtípusok átkonvertálhatóak egymásba a MySQL megfelelő dátumfüggvényeit használva. A részletekért nézze meg a Mysql dokumentációját. Oszlop típus
Tárolási méret, formátum
Leírás
DATETIME
8 byte, 'ÉÉÉÉ-HH-NN ÓÓ:PP:MM'
Dátum és időpont érték: '1000-01-01 00:00:00'-tól '9999-12-31 23:59:59'-ig
DATE
3 byte, ’ÉÉÉÉ-HH-NN’
Dátum érték: A fentieknek megfelelő.
TIMESTAMP
4 byte, ’ÓÓ:PP:MM’
Egy időpont érték. 00:00:00 – 23:59:59
TIME
3 byte
Idő:
YEAR
1 byte
Év: 0000-9999
9.2.2.3
String típusok
A szövegeket az SQL92-höz képest kibővítették a változó hosszúságú adattípusokkal. Ezek esetében a tárolási méret függ az adott mezőben aktuálisan letárolt adatok mennyiségétől. Column type
Tárolási hossz
Leírás
CHAR(M)
M bytes, 1 <= M <= 255
Karakter
VARCHAR(M)
L+1 byte, ahol
Változó hosszúságú szöveg
L <= M és 1 <= M <= 255 TINYBLOB, TINYTEXT
L+1 byte, ahol L < 2^8
Rövid szöveg vagy bináris adat
BLOB, TEXT
L+2 byte, ahol L < 2^16
Szöveg vagy bináris adat
MEDIUMBLOB, MEDIUMTEXT
L+3 byte, ahol L < 2^24
Közepes hosszúságú szöveg vagy bináris adat
LONGBLOB, LONGTEXT
L+4 byte, ahol L < 2^32
Hosszú szöveg vagy bináris adat
ENUM('value1','value2',...)
1 vagy 2 byte, a kiértékelendő adatok számától függően (65535 érték maximum)
SET('value1','value2',...)
1, 2, 3, 4 vagy 8 byte, a halmaz elemeinek számától függően (64tag lehet maximum)
9.2.3
Speciális adattípusok a Microsoft Accesben
Az ACCESS felhasználói felületén ugyanazokat a mezőtípusokat használjuk mind a magyar, mind az angol nyelvű változatokban, azonban az elnevezések nem azonosak, hiszen lefordították őket. Az SQL-ben azonban az eredeti angol nyelvű elnevezéseket kötelező használni. Column type
36
Tárolási hossz
Leírás
Text, Szöveg
max 255 karakter hosszú = 255
Szöveg
MEMO, Megjegyzés
Max 65535 karakter hosszú
Változó hosszúságú szöveg
Number, Szám
1,2,4 vagy 8 byte hosszú
Egész vagy lebegőpontos szám. Az Access automatikusan megállapítja a tárolási méretet.
Date/Time, Dátum/Idő
8 byte
Dátum és időpont. Mindig a teljes dátumot tárolja az Access, esetleg nulla időponttal. A bevitel és megjelenítés módja Magyarországon: #ÉÉÉÉHH-NN# A megjelenítés pontos formátuma függ az illető ország beállításaitól.
Currency, Pénznem
8 byte
A rendszerben megadott pénznem szerinti érték
Autonumber/Sorszámozott 4 byte vagy 16 byte
Automatikusan növekvő érték
Yes/No, Igen/Nem
Logikai érték.
1 byte
OLE Object, OLE Objek- Max 2 GB lehet tum
A windowsban szereplő bármilyen objektum, amelyet dokumentumokba be léehet ágyazni. Megfelel a ’BLOB’ típusnak
Hyperlink, Csatolás
A Windows rendszerben letárolt file, vagy az Interneten tárolt file.
9.2.4
Max 1 GB lehet
Adatdefiníáló utasítások – Táblák létrehozása
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 (attributum_név adattípus [(szélesség)] [NOT NULL]. (attributum_név adattípus [(szélesség)] [NOT NULL]. ... ); A szélesség megadása el is maradhat. A relációk é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ű attributum, stb.) A nevek hossza korlátozott, az Oracle-ben például 30 karakter. Az SQL az azonosítókban és a parancsszavakban általában nem tesz különbséget a kis és nagybetűk között. Az attributum 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. A NOT NULL az attributum definiciójában arra utal, hogy az adat megadása kötelező, azaz nem lehet olyan sor a relációban, ahol az így definiált adat nincs kitöltve. 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) NOT NULL, 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) NOT NULL, Nev CHAR (30) NOT NULL, Cim CHAR (40) NOT NULL, Telefon CHAR (15));
37
CREATE TABLE Orarend (Tanar_azonosito NUMERIC (4) NOT NULL, Tantargy CHAR (20) NOT NULL, Idopont NUMERIC (2), NOT NULL, Osztaly CHAR (3) NOT NULL), Terem NUMERIC (3) NOT NULL); CREATE TABLE Osztalyzatok (Diak_azonosito NUMERIC (4) NOT NULL, Tantargy CHAR (20) NOT NULL, Datum DATE NOT NULL, Osztalyzat NUMERIC (1) NOT NULL); CREATE TABLE Hianyzasok (Diak_azonosito NUMERIC (4) NOT NULL, Datumtol DATE NOT NULL, Datumig DATE, Igazolt CHAR (1)); A CREATE TABLE utasítással létrehozott táblázatok definicióját csak korlátozottan módosíthatjuk, újabb attributumot adhatunk a relációhoz vagy egy attributum szélességét megnövelhetjük. Egy attributum 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. 9.2.5
Táblák módosítása
A reláció újabb attributummal való bővítésére az alábbi parancs szolgál: ALTER TABLE reláció_név ADD attributum_név adattípus [(szélesség)]; Az új attributum a reláció utolsó oszlopa lesz. Ebben az esetben a NOT NULL módosító nem adható meg. Az attributum é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ó attributumának szélességét meg lehet növelni az ALTER TABLE paranccsal. ALTER TABLE reláció_név MODIFY attributum_név adattípus (új_szélesség) [NOT NULL]; Abban az esetben, ha az attributum 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 attributum szélességének megnövelése a következő paranccsal történhet. ALTER TABLE Diakok MODIFY nev CHAR (40) NOT NULL; 9.2.6
Teljes táblák törlése
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ó definició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; 9.2.7
Nézettáblák létrehozása, módosítása, törlése
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'; 38
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. 9.2.8
Indexek létrehozása
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>BR> ON reláció (attributum, attributum, ...); 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ó attributuma 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. 9.2.9
Az indexek megszüntetése
DROP INDEX index_név ON [reláció] parancs segítségével történhet. Ezen a két parancson kivül nincs több az indexek kezelésére. 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.
9.3 9.3.1
Az adatmanipulációs nyelv Új sorok beszúrása
Az SQL adatmanipulációs része biztosítja a relációk feltöltését, az attributumok 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 táblanév [(mező_név_1, mező_név_2, ...)] VALUES (érték, érték, ...); Egy utasítás segítségével egy sor adható meg az adott relációhoz. Az attributum nevek megadása csak akkor kötelező, ha nem minden attributumhoz rendelünk értéket, vagy az attributumok értékét nem a definiálás sorrendjében adjuk meg. A NOT NULL megjelöléssel definiált attributumok megadása kötelező az INSERT parancsnál, ellenkező esetben hibaüzenetet kapunk. Az attributumok é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'); Ha egy mező értékét nem adjuk meg, akkor az értéke NULL lesz.. A tábla létrehozásánál NOT NULL jelzéssel ellátott mezőket kötelező minden újabb sor megadásánál kitölteni, mert különben hibaüzenetet kapunk. Ha egy AUTO_INCREMENT (Access: Automatikusan növekvő) tulajdonságú mező értékét az utasításban explicit módon megaduk, akkor hibát fogunk kapni, hiszen az adatbázis-kezelő fog a letárolt addigi értékek alapján új értéket adni a mezőnek. Ezt olyan módon teszi, hogy letárolja az addigi értékeket és az új sor beszúrásakor az utolsó értékhez hozzáad egyet és ez lesz az új érték. 39
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 attributumai sorrendjének és típusának meg kell felelnie a feltöltendő reláció definiciójának. INSERT INTO reláció_név [(attributum_név, attributum_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. 9.3.2
A mezők tartalmának módosítása
A relációkban szereplő mezők tartalmát az UPDATE utasítással módosíthatjuk. UPDATE reláció_név SET attributum_név = érték, attributum_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ó attributumot é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; Az egyenlőség jobb oldalán a reláció attributumaibó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 attributum = (lekérdező utasítás) [WHERE feltétel]; 9.3.3
Sorok törlése
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);
40
9.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] attributum_név, attributum_név, ... FROM reláció_név; A megadott reláció felsorolt attributumai 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 attributumainak 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; 9.4.1
Az eredményhalmaz szűkítése – WHERE feltétel
A szelekció művelet megvalósítása esetén a SELECT utasítást egy feltétellel egészítjük ki: SELECT attributum_név, attributum_név, ... FROM reláció_név WHERE feltétel; Ha az attributum nevek helyett csak "*"-ot adunk meg, akkor az eredményben a reláció valamennyi attributuma szerepelni fog: SELECT * FROM Diakok WHERE osztaly = '3/b'; Megadott attributumok 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. 9.4.1.1
Ö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 attributumokat é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. 9.4.1.2
Ö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
41
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: 9.4.1.3
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 attributumokra 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. 9.4.1.4
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: =, !=, <>, ^=, >, >=, <, <= NOT AND OR
42
9.4.1.5
Példák a lekérdezésekre
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; 9.4.1.6
Sorrendek – ORDER BY
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 attributum, attributum, ... FROM reláció [WHERE feltétel] ORDER BY attributum [ASC|DESC], attributum [ASC | DESC], ...; Az ORDER BY után megadott attributumok 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 attributumot 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 attributum alapján rendezi sorba a sorokat, ha ez az attributum azonos két sorra, akkor a másodikként megadott attributum alapján, és így tovább. Karakteres attributumokná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 órarandje 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;
43
9.4.1.7
Az eredmények csoportosítása – GROUP BY
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 attributumok FROM reláció [WHERE feltétel] GROUP BY attributum [HAVING csoport_feltétel]; A GROUP BY alparancs után megadott oszlop értékei alapján az addig megtalált sorokat csoportosítja az adatbáziskezelő, és a megfelelő csoportokból egy kerül az eredménybe. A csoportokra vonatkozólag használhatunk függvényeket, amelyek a csoportokra vonakozó értékeket számolhatnak ki. Függvények csoportokra COUNT (mező) – A megadott mező értékei alapján a csoport számának megszámlálása COUNT (*) – A csoportokba tartozó sorok számának megjelenítésem MAX (attributum) – A sorok közötti maximum megkeresése MIN (attributum) – A sorok közül a minimális érték megkeresése SUM (attributum) – Az összeg kiszámolása Függvény
Értelmezés
Eredmény -
AVG (attributum)
átlag3
3
nem NULL elemek száma
8
5
sorok száma NULL is
9
6
maximális elem
5
5
minimális elem
1
1
Összeg
24
15
DISTINCT
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 (*) AS Darab 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, ha csak egy oszlopot szolgáltat a lekérdezés. 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 attributumok és a csoportokra vonatkozó függvények mellett az attributumok 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:
44
SELECT Diak_azonosito, 6 - Osztályzat FROM Osztalyzatok; 9.4.1.8
További függvények lekérdezésekben
A kifejezésekben az alapműveletek mellett számos függvényt is használhatunk a karakteres, numerikus és dátum típusú adatokra. Karakteres függvények Függvény
Magyarázat
Példa
ASC (szöveg)
A szöveg első karakterének ASCII kódja
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ö- A szöveg1-ben a szöveg2 hányadik előfordulása a kezdettől. Há- INSTR ('abcd', 'cd') = veg2, kezdet, hányadik) nyadik és kezdet elmaradhat 3 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 (szöveg, hossz, A szöveget kiegészíti balról a megadott karakterekkel az adott LPAD ('x', 3) = ' x' karakterek) hosszig, Karaktereket nem kötelező megadni, ekkor szóköz. LPAD ('x', 5, '12') = '1212x' LTRIM (szöveg, karak- A szöveg elejéről levágja a karakterekkel egyező részt. Karaktere- LTRIM (' x') = 'x terek) ket nem kötelező megadni, ekkor szóköz. LTRIM 'KUKURIKU', 'UK' = 'RIKU' RPAD (szöveg, hossz, A szöveget kiegészíti jobbról a megadott karakterekkel az adott RPAD ('x', 3) = 'x ' karakter) hosszig, Karaktereket nem kötelező megadni, ekkor szóköz RPAD ('x', 5, '12') = 'x1212' RTIM (szöveg, karak- A szöveg végéről levágja a karakterekkel egyező részt. Karaktere- LTRIM ('x ') = 'x' ter) ket nem kötelező megadni, ekkor szóköz. LTRIM ('KUKURIKU', 'UKI' = 'KUKUR' SUBSTR (szóveg, A szöveg része a kezdet poziciótól adott hosszban. Hossz nélkül a SUBSTR ('abçd', 2, 1) kezdet, hossz) szöveg végéig = 'b' SUBSTR ('abcd', 3) = 'cd' TRANSLATE (szöveg, A szövegben előforduló mit karaktereket kicseréli a mire karaktere- TRANSLATE ('abc', mit, mire) ire 'ab', 'AB') = 'ABc') UPPER (szöveg)
45
Nagybetűssé alakítja a szöveget
UPPER 'ABC'
('abc')
=
Numerikus függvények Függvény
Magyarázat
Példa
ABS (érték)
Abszolút érték)
ABS (-1) = 1
CEIL (érték))
Az értéknél nagyobb vagy egyenlő legkisebb CEIL (6.12) = 7 egész)
FLOOR (érték))
Az értéknél kisebb vagy egyenlő legnagyobb FLOOR (3.95) = 3 egész)
MOD (érték, osztó)) Osztási maradék)
MOD (8, 3) = 2
POWER kitevő))
(érték, Hatványozás)
POWER (3, 4) = 81
ROUND pontosság))
(érték, Kerekítés a megadott jegyig. Negatív pontosság is ROUND (123.456, 1) = 123,5 ROUND megadható.) (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 pontosság))
(érték, Csonkítás a megadott jegyig. (Negatív pontosság is TRUNC (123.456, 1) = 123.4 TRUNC megadható.) (163.456,-2) = 100
Dátum függvények Függvény
Magyarázat
Példa
ADD_MONTH (dátum, n) A dátumhoz n hónapot ad
ADD_MONTH ('10-MAY-93',2) = '10JUL-93'
LAST_DAY (dátum)
A dátumban szereplő hónap utolsó apja
LAST_DAY ('1-JAN-93') = '31-JAN-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ő NEXT_DAY ('10-MAY-93','TUESDAY') dátum = '11-MAY-93') ROUND (dátum, formá- Dátum kerekítése a megadott formátum tum) szerint TO_CHAR (dátum, formá- Dátum megadott karakteres formátumba TO_CHAR (4-DEC-58, 'YY.MM.DD') = tum) konvertálása '58.12.04' TO_DATE (szöveg, for- A szöveg dátummá alakítása a formátum TO_DATE ('58.12.04', 'YY.MM.DD') = 4mátum) szerint DEC-58 TRUNC (dátum, formá- Dátum csonkítása a megadott formátum tum) szerint
46
A SELECT utasítás a relációk közötti szorzás művelet végrahajtá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>BR> 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ó.]attributum, [reláció.]attributum, ... FROM relációk WHERE kapcsoló_attributum operátor kapcsoló_attributum; Az összekapcsolás esetén mindig legalább két relációval dolgozunk. Ha a két relációban azonos az attributumok neve, akkor minősített névvel, reláció.attributum 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 úgynevezett külső összekapcsolás megvalósításához a WHERE-ben szereplő összekapcsolás utasítás után a plusz jelet kell tenni zárójelek közé. Például egy olyan lista összeállításához melyben minden diák legalább egyszer, de a hiányzók a hiányzásaik számának megfelelő számú sorban szerepelnek, az alábbi utasítás szükséges: SELECT nev, datumtol, datumig FROM diakok, hianyzasok WHERE diakok.diak_azonosito = hianyzasok.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';
47
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, kiváncsiak vagyunk Kiss János osztalytá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: 9.4.2
Halmaz műveletek SQL-ben Metszet
Unió SELECT UNION SELECT ...
.... SELECT INTERSECT SELECT ...
Különbség ... SELECT MINUS SELECT ...
...
A halmazműveletekben résztvevő lekérdezéseknek azonos sorrendben azonos típusú eredményeket kell szolgáltatniuk, különben a lekérdezés hibát okoz. Az nem követelmény, hogy a résztvevő lekérdezések ugyanolyan nevű mezőket tartalmazzanak. Ebben az esetben a második lekérdezés oszlopnevvei leszenek az eredményhalmaz olszopnevei. 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. A fenti műveletek meglehetősen lassúaknak számítanbak, tehát ha nem szükséges ne hazsnáljuk őket, próbáljuk meg a lekérdezések WHERE feltételének megfelelő kialakításával, esetleg allekérdezések alkalmazásával megoldani a problémát.
48
9.5
Egyéb SQL parancsok
9.5.1
Tranzakciókezelés
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 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. 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 Ekkor minden adatvégrehajtó uasítás automatikusan tranzakciókezelést eredményez. Ha egymás után több adatmódosító utasítást kezdeményezünk, akkor a tranzakciókezelés lelassíthatja az utasítások végrehajtásának a sebességét. Ebben az esetben kifejezetten célszerű kikapcsolni az automatikus tranzakció kezelést, saját magunk által kezdeményezni a tranzakciót BEGIN TRANSACTION Amikor lefutottak a megfelelő utasítások ki kell adni a COMMIT TRANSACTION, ha hibát tapasztaltunk, akkor a ROLLBACK TRANSACTION utasításokat. Ezt hívják a tranzakciók kötegelésének. A kötegelt végrehajtás alatt más adatmódosító parancs nem hajtható végre a megadott adattáblákon, sőt a lekérdező műveletek sem biztos, hogy futni fognak. Mi a megoldás? Egy példán keresztül érzékeltetem. Tegyük fel, hogy 10000 db insert utasítást kell végrehajtatnunk programmal egy adattábán. Azt utasítások egyesével való tranzakciókezelése a programunk futását nagyon hosszúvá teszi. A teljes utasítássorozat egy kötegbe szervezése az adatbázis használatát más felhasználó részére gyakorlatilag lehetetlenné teszi, bár ez lenne a leggyorsabb végrehajtási lehetőség.. A feldolgozó programot úgy írjuk meg, hogy az utasításokat 10-es kötegekben hajtatjuk végre. A 10-es csomag elején bekapcsoljuk a tranzakciókezelést, majd a csomag végén végrehajtatjuk a tranzakciót.. Minden 10-es köteg végrehajtása után a rendszer feléled, más felhasználó részére is biztosítja a hozzáférést, a kliens programunk pedig újra elkezdheti az új tranzakció köteget. Ilyenkor az optimális sebességhez képest a rendszer csak kb. 10%-ot veszít, a sebességéből, de a rendszer más számára is elérhető marad. 9.5.2
Jogosultság kezelés
Az adatbázis-kezelő programok különbözően viselkednek a felhasználói jogosultságokkal kapcslolatban. Az adatbázis-kezelő programnak mindig van egy adminisztrátor jogú felhasználója. Ez a felhasználó alapértelmezetten létezik a rendszerben. Ez alól kivétel a Microsoft Access, amely jogosultság nélküli rendszer. Minden rendszeren jogosultságot lehet adni a GRANT utasítással, és jogosultságot lehet elvenni a REVOKE utasítással. 9.5.2.1
Microsoft SQL jogosultságok
Az Microsoft SQL esetén a felhasználó az sa (System Administrator) vagy bármelyik user lehet, aki az Adtabázis kezelőt futtató operációs rendszeren létezik. Egy felhasználónak az MS SQL-ben az egyes táblákara (objektumokre) alábbi jogai lehetnek: • • • • • • 49
SELECT – lekérdezési, Insert – adatbeszúrási Update – Adatfrissítési Delete – törlési References – hivatkozás Execute (=futtatási jog tárolt eljárásra)
Egy táblán belül a mezőkre is lehet jogosultságokat osztani: • Select – lekérdezésben szerepelhet • Update – frissítésben szerepelhet, • References – hivatkozni lehet rá Jogosultságokat lehet osztajni minden CREATE utasításra és a BACKUP utasításokra. 9.5.2.2
MySQL jogosultságok
A felhasználók MySQL-ban az alábbi formájú névvel rendelkeznek: usernév@bejelentkezési_hely Az alábbiakban az fz user többféleképpen létezik: fz@localhost – ha ugyanazon a gépen fut a kliens program, mint a szerver,
[email protected] – ha a 10.1.1.22 IP című gépről elérheti a user az adatbázis- szervert fz@% - ha bárhonnan, tehát lokálisan és mtetszőleges gépről elérheti a user az adatbázis szervert. MySQL esetén a default rendszeradminisztrátor neve root@localhost felhasználó. A felhasználóknak adatbázisokra lehetnek jogosultságaik. Ezek az alábbiak lehetnek: • SELECT – lekérdezési, • Insert – adatbeszúrási • Update – Adatfrissítési • Delete – törlési • Create – objektum létrehozása (Table, Temp Table, View, Index, User, Routine - eljárás) • Drop – objektum törlése • Reload – egy tábla vagy adat újratöltése • Shutdown – A szerver leállítása • Process – Egy tárolt eljárás futtatása (5.0.x verziótól) • File – fájlelérés SQL utasításból (például SELECT * FROM tábla INTO file) • Grant – jogok adása és elvétele • Index – index létrehozása • Alter (Table, Index, routine) • Show Database – Adatbázis létének lekérdezése • Lock Tables – tábla zárolása • References – hivatkozás • Execute (=futtatási jog tárolt eljárásra) • Replicate Slave – replikációban slave • Replicate Client – replikációban hozzáférés kliensként A fenti jopgosultságokat a teljesség kedvéért soroltuk fel, de egy átlagos felhasználónak általában ezekkel nem nagyon kell törődnie. Ha egy kliens kapcsolódik az adatbázis-kezelőhöz, akkor az a program, amivel kapcsolódik azzal a joggal fut, amelyik user nevében kapcsolódik. A nyilvános szervereken általában a felhasználó csak a saját adatbázisához férhet hozzá. Az adatbázis-kezelők a userek adatait és jogosultságait is egy adatbázisban tárolják. Ez egy rendszeradatbázis, amelyik ezen kívül még nagyszámú egyéb adatot is tartalmazhat a remdóndszer összetettségétől függően. 9.5.3
Tárolt eljárások (MySQL 5.x.x-től)
Bár az egyes adatbáziskezelők nem egységesek ebből a szempontból és az SQL szabvány sem írja le, de vannak olyan adatbázis kezelők, amelyekkel programokat lehet létrehozni és futtatni az adatbázisszerveren. Ezek a programok az SQL utasításokból és értékadó, változódeklaráló, vezérlési utasításokból állhatnak. Előnye, hogy a bonyolultabb kifejezéseket egy eljárásnévvel lehet illetni, azokat paraméterrel meghívni. Ilyen rendszer például a Microsoft SQL szervere (PL-SQL) vagy a MySQL esetén az SQL:2003 (IBM DB2 adatbázis nyelve). A MySQL 5.0.x-tól állnak rendelkezásünkre tárolt eljárások. A tárolt eljárás a szerveren fut le, tehát semmiféle adatmozgás nincsen a szerver és a kliens program között, azaz gyors és hatrékony mód. A továbbiakban a MySQL tárolt eljárásait vesszük górcső alá. Két féle tárolt eljárás van a MySQL-ben, eljárás és függvény. Az eljárást PROCEDURE, a függvényt FUNCTION néven nevezzük. A FUNCTION visszaad értéket a hívó félnek, míg a PROCEDURE nem ad vissza értéket. A hívás során alkalmazhatunk paramétereket, így ezekkel vezérelhetjük a tárolt eljárás működését.
50
9.5.3.1
A tárolt eljárás hívása egy SQL utasítás,
PROCEDURE hívása: CALL ELJARASNEV(@version, @increment); FUNCTION hívása: SELECT Fuggvénynév('world'); Függvény hívásakor természetesen visszakapunk valamiféle értéket, így a tárolt eljárások függvények formáját úgy tudjuk használni, mint az SQL-ben a korábban felsorolt függvényeket. 9.5.3.2
A tárolt eljárás létrehozása CREATE PROCEDURE Eljárásnév ([parameterlista]) BEGIN Utasítások ... END
vagy CREATE FUNCTION függvénynév ([parameterlista]) RETURNS adattípus BEGIN utasítások ... RETURN visszatérési érték END Ha a tárolt eljárásban csak egy utasítás van, akkor a BEGIN ... END elhagyható. 9.5.3.3
Paraméterátadás a tárolt eljárás részére
A paraméterlistában a parmétereket az alábbi módon használhatjuk: IN paraméternév Típus Ekkor a tárolt eljárást a megadott paraméterrel hívhatjuk meg (érték szerinti paraméterátadás) OUT paraméternév típus A paramétert visszatérési értékként hazsnálhatjuk (cím szerinti paraméterátadás) INOUT paraméternév típus A paramétert átadjuk az eljárásnak, módosíthatjuk a tárolt eljárásban, majd a visszatérési értéket felhasználhatjuk (cím szerinti paraméterátadás). Az OUT és az INOUT típusú paraméterátadásnál a megfelelő helyen álló változónak léteznie kell, azaz ha létezik egy ilyen tárolt eljárás, hogy CREATE PROCEDURE p (INOUT Ver Char(50)) BEGIN # A kimeneti változónak értéket adunk SELECT VERSION() INTO Ver; END; majd kiadjuk az alábbi SQL utasításokat >SET @Ver=”” >CALL p(@x) >SELECT @x Az eredmény : 5.0.27-log
51
9.5.3.4
Változók használata a tárolt eljárásban
A tárolt eljárásban deklarálhatunk lokális változókat az alábbi módon: DECLARE változo_neve Típus [DEFAULT érték]; A változó neve nem lehet azonos az oszlopnévvel. A változót még azelőtt kell deklarálni, hogy bármiféle lekérdezést vagy értékadást futtattunk volna. Ha a tárolt eljárás törzsében BEGI…END között vannak az utasítások, akkor még a BEGIN előtt kell deklarálni (mint a Pascalban ☺) Változó értékadása: SET változónév = érték | kifejezés; Lehetséges egy lekérdezés eredményének érétkadása a tárolt eljárásban: SELECT oszlopnév INTO változónév FROM tábla WHERE feltétel 9.5.3.5
CONDITION – feltétel létrehozása
Az utasítás segítségével saját hibakezelést vezethetünk be, úgy hogy egy névhez hozzárendelhetünk hibakódot. DECLARE feltétel_neve CONDITION FOR feltétel_értéke 9.5.3.6
Handler – kezelő használata a tárolt eljárásban
A kezelők olyan deklarációk, amelyek egy esemény esetén futnak le. Használata DECLARE kezelőtípus HANDLER FOR feltétel_érték utasítás. A kezelőtípus az alábbi lehet: CONTINUE – hatására az esemény lezajlása után tovább halad a program EXIT – hatására a program megszakad (END utáni utasításra ugrik a program) UNDO – Ez még nem támogatott!!! A feltétel_érték az alábbi lehet: SQLSTATE [VALUE] sqlstate_value – a MYSQL hibakezelő rendszere által szolgáltatott értékek egyike condition_name - korábban deklarált feltétel SQLWARNING – 01-gyel kezdődő SQLSTATE értékek, figyelmeztetések NOT FOUND - a 02-vel kezdődő SQLSTATE értékek SQLEXCEPTION – Korábban nem felsorolt végzetesnek számító SQL hivételek Példa a handler kezelésére. A példában feltételezzük, hogy parancssorban dolgozunk (lehet bármilyen kliensben). Hozzuk létre az alábbi tárolt eljárást: CREATE PROCEDURE handlerdemo () BEGIN DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; SET @x = 1; INSERT INTO test.t VALUES (1); SET @x = 2; INSERT INTO test.t VALUES (1); SET @x = 3; END; Futtassuk le az alábbi sorokat CREATE TABLE test.t (s1 int,primary key (s1)); CALL handlerdemo(); SELECT @x; Az eredmény: @x => 3 lesz, mivel a tábla deklarálása során hiba lépett fel, mert az aláhúzott sorban a primary key ugyanazt az értéket kapta! Ha CONTINUE HANDLEr nem lett volna definiálva, akkor a tárolt eljárás vezérlése kiugrott volna az END utáni utasításra, így a CONTINUE HANDLER hatására a következő soron folytatta a programot, és mellesleg értéket adott a @x2 változónak is, amit pedig nem használtunk fel.
52
9.5.3.7
Kurzorok használata tárolt eljárásban
A kurzor olyan érték, amely egy lekérdezés aktuális sorára mutat a tárolt eljárásban. A kurzort deklarálni kell a tárolt eljárás fejlécében. Minden kurzor deklaráció csak a változók deklarálása után jöhet. A kurzort használatra meg kell nyitni az OPEN paranccsal, majd a FETCH paranccsal lehet lekérdezni és változóba tenni a következő sor értékeit. Ha elérte az utolsó sort a lekérdezés, akkor egy HANDLERT (kezelőt) kell futtatni. Példa: CREATE PROCEDURE curdemo() BEGIN DECLARE done INT DEFAULT 0; DECLARE a CHAR(16); DECLARE b,c INT; DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1; DECLARE cur2 CURSOR FOR SELECT i FROM test.t2; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN cur1; OPEN cur2; REPEAT FETCH cur1 INTO a, b; FETCH cur2 INTO c; IF NOT done THEN IF b < c THEN INSERT INTO test.t3 VALUES (a,b); ELSE INSERT INTO test.t3 VALUES (a,c); END IF; END IF; UNTIL done END REPEAT; CLOSE cur1; CLOSE cur2; END 9.5.3.8
Vezérlési szerkezetek tárolt eljárásban
A vezérlési szerkezetek majdnem a más nyelveken megszokott szintaktikával hajthatók végre. A megvalósított utasítások: IF, CASE LOOP, WHILE, REPLACE ITERATE, LEAVE Az IF utasítás használata IF feltétel THEN utasítások [ELSEIF feltétel2 THEN utasítások] ... [ELSE utasítások] END IF A használata olyan, mint más nyelveken. Létezik az IF() függvény, amit a lekérdezésekben lehet használni, de azt a dokumentációban kell megnézni. A CASE utasítás használata CASE case_érték WHEN feltétel_érték THEN utasítások 53
[WHEN feltétel_érték THEN utasítások] ... [ELSE utasítások] END CASE vagy CASE WHEN keresési_feltétel THEN utasítások [WHEN keresési_feltétel THEN utasítások] ... [ELSE utasítások] END CASE Ha a keresési_feltétel igaz, akkor az utasítás lefutHa a keresési feltétel nem válik igazzá, akkor az ELSE ág fut le.inden ágban egy vagy több utasítás is lehet.. A lekérdezésekben is létezik a CASE utasítás, azonban ott nincs ELSE NULL lehetőség, tovább a END CASE helyett csak END van. Ciklusok LOOP ciklus [címke:] LOOP utasítások END LOOP [címke] A ciklus magjában lévő utasítások végrehjatódnak, amíg valami miatt nem szakad meg. Gyakran a magban lévő LEAVE utasítás fut le, ekkor a vezérlés kilép a ciklusból. A LOOP elé címkét lehet tenni. A LOOP után csak akkor lehet címke, ha előtte is van. LEAVE címke Az utasítás hatására a címkével jelölt ciklusból vagy BEGIN…END részből kiugrik a vezérlés. ITERATE utasítás ITERATE címke Az ITERATE utasításhatására a címkével jelölt ciklus azonnalk újra végehajtódik. Példa: CREATE PROCEDURE doiterate(p1 INT) BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; SET @x = p1; END REPEAT utasítás [címke:] REPEAT utasítások UNTIL feltétel END REPEAT [címke] A ciklusban bentmaradás feltétele, a feltétel igazsága. Másszóvalk addig maradunk a ciklusban, amíg a feltétel igaz. Hátultesztelő ciklus. Példa: CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END Hívás CALL dorepeat(1000) WHILE utasítás [címke:] WHILE feltétel DO utasítások END WHILE [címke] Amíg a feltétel igaz, addig a ciklusban marad a vezérlés. Elöltesztelős ciklus. Lehet használni címkét itt is. Példa: 54
CREATE PROCEDURE dowhile() BEGIN DECLARE v1 INT DEFAULT 5; WHILE v1 > 0 DO ... SET v1 = v1 - 1; END WHILE; END 9.5.4
Triggerek
A triggerek olyan tárolt eljárások, amelyek egy táblán valamilyen adatbázis esemény hatására futnak le. Nem közvetlenül kliens program indítja a trigger futását. Jól használhatók feltételek teljesülésének ellenőrzésére, logok futtatására, stb. A MySql 5.0.2-ben vezették be őket. A trigger futását kiváltó esemény az INSERT (LOAD DATA és REPLACE), UPDATE, DELETE (REPLACE utasítás speciális esetben) lehet. Jelen változatban (MySQL 5.0.17) kaszkádolt eseményekre nem alkalmasak a triggerek, de aközeljövőben alkalmasakká válnak. Trigger létrehozása CREATE TRIGGER triggernév időpont esemény on tábla FOR EACH ROW BEGIN utasítások END A triggernév tetszőleges lehet, Az időpont
BEFORE – ha az esemény előtt fut le a trigger AFTER – ha az esemény után fut le a trigger
Az esemény
INSERT, UPDATE, DELETE
A tábla egy táblanév. Például: CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF; END; Másik példa triggerekre. A példában 4 különböző tábla van és a test1 tábla INSERt utasítására lefutnak különböző más utasítások a többi táblákra. CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; Trigger törlése DROP TRIGGER triggernév
55
10 Relációs adatbázis – kezelő rendszerek ismertetése Relációs adatbázisok kezelésével kapcsolatban az eddigiekben sokféle rendszerről tettünk említést, néhány szóval a továbbiakban áttekintjük a szélesebb körben használt rendszereket. Mielőtt ezeket megtárgyalnánk, előtte néhány általános megjegyzés a használható segédeszközökről
10.1 Az adatbázisok felügyeletéhez használható kliens programok feladatai Az adatbázisok kezeléséhez használható kliens programok alapvető feladatai: • • • • • • • • • • • • • • • •
Helyi és távoli adatbázis szerverek elérése Adatbázis létrehozás Az adatbázis táblaszerkezetének létrehozása Az adatbázis táblák mezőinek létrehozása Indexek létrehozása Az adatbázis táblák közötti kapcsolatok létrehozása SQL lekérdezések létrehozása, futtatása View (Nézet) létrehozása, módosítása Tárolt eljárások, Triggerek létrehozása (ha a rendszer támogatja) Adatbázis backup, restore futtatása Adatbázis dump (Teljes adatbázis mentés) SQL fájlba Szinkronizálás két vagy több adatbázis szerver között Adatok importja más adatbázisokból vagy fájltípusokból Adatok exportja más adatbázis kezelőkbe vagy fájltípusokba Felhasználók kezelése és jogosultságkezelés Az adatbázis kezelő szerver speciális paramétereinek megmutatása, beállítása a program segítségével
10.2 Microsoft Access / JET Adatbázis motor A Microsoft Access a mindenkori Microsoft Office programcsomag része. Az Access maga alapvetően egy kliens program. A Win95-nél fiatalabb Microsoft operációs rendszereken JET-nek hívják azt az adatbázis motort, amelyet az Access meghajt. Ennek megfelelően az Jet elérhető mindig a működő operációs rendszeren, de kliensnek célszerű használni az Access-t. Az Access részletes kezelésével itt és most nem foglalkozom. Mire alkalmas az eszköz? Használható az adatbázis megtervezése során, hiszen az adatbázis létrehozását, táblák létrehozását, a táblák közti állandó kapcsolatok létrehozását, lekérdezések létrehozását nagymértékben segíti. Segítségével jelentéseket, és űrlapokat is készíthetünk, illetve egyfajta makro nyelvet vagy Visual Basic-et használva alkalmazásokat írhatunk benne. Támogatja, bár nem túlságosan az SQL-en alapuló feladatok megoldását is. SQL92 szintaktikát használ, de jelentős különbségeket építettek bele az SQL 92-höz képest, főleg az ékezetes és a szóközöket tartalmazó adatbázis azonosítók használata, továbbá a dátum kezelés speciálizálása miatt. A JET esetén egy file – egy adatbázis koncepció él, éppen ezért az Accessben a CREATE DATABASE SQL utasítás használatának nincsen értelme. Az Access 2000-től számítva lehet használni a programot Microsoft SQL Server adatbázisainak kezelésére is. ODBC adatbázisokat becsatolva az Access segítségével meglehetősen sokféle adatbázishoz férhetünk hozzá.
10.3 Microsoft SQL szerver (7.0 / 2000 / 2005) A Microsoft SQL szerver 7.0, 2000, és 2005-ös verziója Microsoft dedikált adatbázis kezelője. Különböző verziókban bocsátották ki, a legkisebb MSDE verziótól kezdve az Enterprise verzióig. A rendszer az adatbázisokat egy könyvtárban tárolja, adatbázisonként külön-külön fájlban. Alapesetben nem követel sok memórát és meglehetősen gyors. Tranzakciókezelés, Tárolt eljárások, triggerek tartoznak a jellemző tulajdonságai közé. Csak Windows NT4 / 2000 / XP / 2003 / Vista operációs rendszereken elérhető. Az SQL 92’ nyelvenek egy kibővített változatát használja. ának különböző kliens programok állnak rendelkezésre. A Mmicrosoft SQL Servernek több verziója létezik, amelyek közül egyes verziók a felhasználó részére ingyenesen használhatók – MSDN, illetve az Office megfelelő verziójának részeként. Ezek a verziók (7.0, 2000 desktop) nem tartalmaznak interaktícv GUI-val rendelkező klienseket. Ezekhez lehet használni a korábban említett módon a Microsoft Access megfelelő verzióját. 10.3.1
Enterprise Manager
A szerver 7.0 és 2000-es verziójának teljes változatában létező programcsomag az Enterprise Manager, amelynek segítségével az adatbázisainkat teljeskörűen menedzselhetjük. Ez a programcsomag némiképpen hasonlít az Accesshez és így az adat bázis tervezés minden fázisában használható, kivéve a jelentések, űrlapok létrehozása területét.
56
10.3.2
SQL Server Express Edition
A Microsoft által kifejlesztett, ingyenesen használható adatbázis-kezelő programcsomag, Microsoft SQL 7.0 / 2000 / 2005 szerverekhez. A legszükségesebb alkalmazások megtalálhatók benne. A felhasználók kezelés, az adatbázisok létrehozása, a ktáblák közötti kapcsolatok kezelésére, export, importra alkalmas. Jól használható benne az SQL felület is. Riportok készítésére nem alkalmas. A program elérése, letöltése, ismertető: http://www.microsoft.com/sql/demos/sqlexpress2005/index.html 10.3.3
MS SQL Manager (www.mssqlmanager.com)
Ez a program az EMS cég adatbázis felügyeleti programjai közül a Microsoft SQL kezelésére alkalmas programcsomag. Rendkívül jól használható. A tulajdonságai: • • • • • • •
az adatbázis szerkezete (vizuálisan is), táblák, létrehozása adatok módosítása, adatok lekérdezése, tárolt eljárások és triggerek létrehozása és módosítása, backup, restore, külső scriptek futtatása jelentések készítése (ez nem igazán jó)
10.4 MySQL szerver A MySQL napjaink egyik legelterjedtebb, népszerű RDBMS-e. Minden operációs rendszeren van futó változata, Windows NT/2000/XP/2003/Vista, Linux, BSD, Novell Netware (5-től). Nonprofit felhasználása ingyenes. Az adatbázisokat tárolására többféle formátumot használ. Gyors, kevés memóriát használ. A MyIsam formátum hagyományos felépítésű, nem tranzakcióképes. Az egy adatbázishoz tarto táblák, BLOB adatok, Indexek egy folderben helyezkednek el, operációs rendszertől független formátumban, így az adatbázis mentése is viszonylag egyszerű. Az InnoDB formátum újabb, tranzakcióképes formátum. Az ilyen táblák leírása az adatbázishoz tartozó könyvtárban vannak, de maguk az adatok a szerverhez tartozó közös InnoDB fájlban találhatók meg. Egy adatbázisban lehetnek a táblák kevert típudúak is. A táblák közötti kapcsolatokat (idegen kulcsokat!) csak az InnoDB adattáblák esetén tárolja az adatbázis. Memória tárolás. A táblákat memóriában tárolja, átmeneti táblák tárolására használható. Az 5.0 verziótól kezdve képes a rendszer tárolt eljárások és triggerek használatára is. Az Adatbázisok operációs rendszer függetlenek, tehát ha pl. Windowsos rendszeren fejlesztenek egy adatbázis és Linuxos rendszeren fut, akkor egyszerűen át lehet vinni az adatokat. Letölthető: http://dev.mysql.com/downloads/mysql/5.0.html 10.4.1
phpMyAdmin
Az alábbi szoftver nagyon jól használható a WEBes adatbázisfejlesztések területén. MySQL adatbázisok kezelésére kiválóan alkalmas, ingyenes és gyakorlatilag mindent meg lehet vele tenni, ami lényeges egy adatbázis-kezelés esetén. Letölthető az alábbi linkről: http://www.phpmyadmin.net/home_page/index.php A használatáról néhány kép:
57
Bejelentkező oldal
A tábla kiválasztása
58
Egy tábla tartalma
Egy tábla mezőinek definiálása
Tábla készen van 59
A többi funkció magától értetődő. 10.4.2
MySQL Manager saját programcsomag (ingyenes)
A MySQL szerver korábbi verzióihpoz is adtak kezelőprogramot, de az nem volt túlságosan kifinomult, de az 5.x.x verziójhoz adott már sokféle igényt kielégít. A szerverrel együtt letöltve majd a telepítő futtatása után feltelepül a rendszerre. Innen lehet letölteni: http://dev.mysql.com/downloads/gui-tools/5.0.html Két részből áll • MySQL Administrator – az adminisztárciós feladatokra • MySQL Query Browser – az adatbázis fejlesztés, adatok feltöltése, adatok lekérdezése jellegű feladatokra 10.4.3
EMS MySQL Manager
A korábbiakban felsorolt szinte mindenMySQL-lel kapcsolatos feladat elvégezhető benne. Nagyon jüól használható segédprogram. Egyszerre tetszőleges adatbázis kezelését képes végezni.
10.5 Oracle 1983 óta létező adatbázis kezelő rendszer. Az Oracle cég a világ harmadik legnagyobb sazoftvergyártója. Az adtabázis-kezelő terméke profgesszionális alkalmazások futtatására alkalmas. Létezik Unix, Linux, Windows, Novell Netware, nagygépes IBM VMS, OS/390, Siemens BS2000, Tandem, stb... verziója is. Meglehetősen borsos árú rendszer, de nagyon hibatűrő, stabil, és nagy sebességű. Lokális hálózatokban, internetes alkalmazásoknál is előszeretettel használják. Elérése: www.oracle.com . Memóriaigényes. Nagyobb adatbzásiok esetén csak akkor lehet jó teljesítményt elérni, ha a rendszer a lehető legtöbb memóriával rendelkezik. Az Oracle SQL verziója Objektum – Orientált szemlééletű, aminek segítségével új, speciális adattípusokat lehet használni ebben a rendszerben. Ezek a típusok a tömb, beépített tábla, Objektum tábla. 10.5.1 • • • •
Oracle Enterprise Manager DBA Management Pack – rendszer adminisztrációs csomag Enterprise Manager Diagnostics Pack – monitorozás és nyomkövetés Tuning Pack – Optimalizálás, finomhangolás Change Management Pack – változatkezelés céljára
10.6 Sybase 1985-ben alakult a cég. Azóta alkotnak adatbázis-kezelőket. Szinte minden eltrjedt operációs rendszerre és géptípusra kifejlezstették az adatbázis-kezelő verziójukat. Először létesítettek triggereket a rendszereikben.
60
11 Függelék Ebben a fejezetben leírom az adatbázis-kezelés tanulása során megbeszélt adatbázisok szererkezetét valamilyen szinten. Természetesen ezek a leírások csak az egyik lehetséges megvalósítást tartalmazzák. Az adatbázisok tervezésekor több megoldás lehetséges. Megjelölöm, azokat a fogalmakat, amelyek alapján el lehet készíteni az adatbázisban szereplő egyedeket, majd azokból a táblákat. Az egyes adatbázisokat 3NF-be optimalizálom, majd esetenként elrontom a 3NF-et, célszerűségi okokból.
11.1 Egy WEBoldal statisztikája 11.1.1
Hozzuk létre egy website statisaztikáját kezelő adatbázist!
A szükséges ismeretek A webszerver a PHP kód rendelkezésére bocsátja változókban az alábbi paramétereket: • a kliens IP címét, • a szerver órája alapján a kérés időpontját, • a kérést magát (melyik oldalt kérte le a kliens), • a UserAgent paramétert. Az IP cím ismeretében lehet DNS neveket is lekérni a szerveren bár az kissé lassú műveletnek számít. Ennak alapján az alábbi adatbázis lehet definiálni.
A megoldásban a sötéten (Bold) írt sorok kötelezőek, azaz NOT NULL klauzula előírt. • Megoldás
11.2 Autókölcsönző Fogalmak Autók, személyek, kölcsönzések Események, történések Kölcsönzés, az autó elveszítése, karambol, visszahozatal, rongálódás, szervíz,
11.3 Videokölcsönző (média) adatbázis Fogalmak: Felhasználók, médiahordozók, filmek, hanganyagok
11.4 Pénztárkönyv Fogalmak bevételek, kiadások rendszere
11.5 Számlázó – raktár készlet nyilvántartó adatbázis Fogalmak termékek, számlák, számla tételek, beszállítk, vásárlók
11.6 Blogger adatbázis Fogalmak Blog tulajdonos, téma, hozzászólók
61
11.7 Fórum program Fogalmak Témakörök, hozzászóló személyek, topicok (egy témakörön belül)
11.8 Könyvesbolt Fogalmak vásárlók, könyvek, szállítók, kiadók
11.9 Iskolai témák o o o
Hiányzások nyilvántartása (tanulók, osztályok, hiányzás események, állapotok) Tantárgyfelosztás (Tanár, tanulócsoport, tantárgy, tantárgyanként hány óra) Órarend (Tanulócsoportok, termek, tanárok, tanított tárgyak, tantárgyfelosztás,
11.10 Egy cég teherautóinak mozgását nyilvántartó adatbázis Fogalmak Teherautók, sofőrők, áru tételek, szállítási helyek
11.11 Egy cég dolgozóinak kifizetéseit nyilvántartó adatbázis Fogalmak Dolgozói nyilvántartás, pénzmozgás
62
12 Ajánlott irodalom • • • • • • •
63
Halassy Béla – Az adatbzási-tervezés alapjai és titkai Stolnicki Gyula – SQL programozóknak Stolnicki Gyula – SQL kézikönyv Stephen Wynkoop – SQL Server 7.0 Wrox – Kezdőkönyv az SQL Server 2005 programozásához Reese, Yarger, King – A MySQL kezelése és használata Czenky Márta – Adatmodellezés, SQL és Access alkalmazás SQL Server és ADO
