Budapesti Mûszaki Egyetem Villamosmérnöki és Informatikai Kar
ADATBÁZISOK Oktatási segédanyag az Informatika c. tantárgyhoz
Csak egyetemi belsõ használatra!
szerkesztette: dr. Gajdos Sándor
szerzõk: dr. Gajdos Sándor dr. Iváncsy Szabolcs Vitéz András
1
Ez a segédlet nem tankönyv, csupán kibõvített óravázlat. Ennélfogva elmélyült tanulásra önmagában nem használható. Nem is feltétlenül tartalmaz mindent, ami az elõadásokon elhangzik. Így nem helyettesítheti az elõadásokat, de gondolatmenetével remélhetõleg segítséget nyújt a felkészüléshez. Felmerülõ megválaszolatlan kérdéseire választ kaphat J. D. Ullman: Priciples of Database Systems címû mûvébõl (Computer Science Press, 1986) vagy konzultáción az oktatóitól. A szerzõk
Tartalom: 1. Alapfogalmak 1.1. A programozó és a felhasználó kapcsolata a rendszerrel. 1.2. Járulékos feladatok. 1.3. Személyek. 1.4. Az absztrakciós szintek.
2. Elvi alapok. 2.1. Sémák és esetek. 2.2. Adatfüggetlenség. 2.3. Entitások. 2.4. Attributumok. 2.5. Kapcsolatok 2.6. Ábrázolás.
3. Fizikai adatszervezés. 3.1. Heap szervezés. 3.2. Hash állományok. 3.3. Indexelt állományok. 3.4. B*-fák, mint többszintes ritka indexek 3.5. Másodlagos indexek, invertálás
4. Adatmodellek. 4.1. A hálós adatmodell. 4.2. A hierarchikus adatmodell 4.3. A relációs adatmodell.
5. Relációk normál alakjai 5.1. Anomáliák. 5.2. Funkcionális függõség. 5.3. Kulcsok. 5.4. Funkcionális függõségek tulajdonságai. 5.5. A tulajdonságok következményei. 5.6. Veszteségmentes felbontás (lossless decomposition) 5.7. A 0. és 1. normál forma. 5.8. A 2. normál forma. 5.9. A 3. normál forma 5.10. A Boyce-Codd normál forma (BCNF). 5.11. Sémadekompozíciók 5.12. Többértékû függõség. 5.13. A 4. normál forma.
Példák
2
1. Alapfogalmak Az adatbáziskezelés az a terület, amelyre a számítógépet talán leggyakrabban alkalmazzák. Adatbázisnak a valós világ egy részhalmazának leírásához használt adatok összefüggõ, rendezett halmazát nevezzük. Ma ezek többé kevésbé állandó formában egy számítógépben tárolódnak. Azt a szoftvert, amely egy vagy több személy számára lehetõvé teszi, ezen adatok olvasását vagy módosítását, adatbáziskezelõ rendszernek (database management system, DBMS) nevezzük. Az adatbáziskezelõ fõ feladata, hogy az adatbázis felhasználójának biztosítsa a tárolt adatok (absztrakt és konkrét) kezelését. Ebben az értelemben az adatbáziskezelõ úgy mûködik, mint egy magasszintû programozási nyelv interpretere, amely ideális esetben lehetõvé teszi, hogy a felhasználó anélkül tudja elõírni a teendõket, hogy az adatbáziskezelõ algoritmusairól vagy az adatok tárolási elvérõl információval bírna. A továbbiakban azokat az általánosságokat igyekszünk vizsgálni, amelyek közösek az adatbázisokban és adatbáziskezelõ rendszerekben, bár sejthetõ, hogy ahány felhasználási terület, és felhasználó csak elképzelhetõ, annyiféle igényt támaszt e területtel szemben. 1.1. A programozó és a felhasználó kapcsolata a rendszerrel. Az ábra egyesítve mutatja a DBMS leggyakoribb felépítését és környezetét. Gyakori eset, hogy az adatbáziskezelõ rendszert közvetlenül egy adott alkalmazás megvalósítására írják. Ebben az esetben a lekérdezésfeldolgozó modul információval rendelkezhet az adatbázisról. Más rendszereket kereskedelemben kapható általános célú modulokból építenek fel. Itt beépített információról nem lehet szó, tehát kulcsfontosságú, hogy legyen alkalmas leíró formalizmusunk. Erre szolgálnak az adatleíró nyelvek (data definition language, DDL). Az ábrán látható, hogy a felhasználótól közvetlenül származó, vagy felhasználói applikáció által generált lekérdezéseket lefordítva átadjuk az adatbáziskezelõ modulnak. Ennek feladata, hogy az állománykezelõ által értelmezhetõ parancsokká alakítsa azokat.
lekérdezô applikáció
felhasználói lekérdezés
lekérdezés feldolgozó
adatbázis séma adatleíró nyelv fordítóprogram
lefordított lekérdezés
adatbázis leírás
adatbáziskezelô állománykezelô fizikai adatbázis
3
Az állománykezelõ (file manager) biztosítja a hozzáférést a fizikai adatbázishoz. Az állománykezelõ egyszerûbb esetben része lehet az alkalmazott operációs rendszernek, de ennél többet is elvárhatunk tõle, ha figyelembe vesszük a késõbbiekben megismerendõ speciális állományszerkezeteket. 1.2. Járulékos feladatok.
Az adatbáziskezelõtõl néhány egyéb, de nem mellékes feladat megoldását is elvárjuk. 1.2.1. Biztonság (adatvédelem)
Nem minden felhasználó férhet hozzá minden tárolt adathoz. Gyakran jelszóhoz kötik az elérés jogát, de ugyanígy hardver is támogathatja. 1.2.2. Integritás.
Fontos, hogy legyen olyan beépített szolgáltatás, amely segítségével az adatbázis integritása ellenõrizhetõ, mivel a beszúrás, törlés, módosítás funkciók kényesek a sikeres végrehajtásra. Az integritásnak számos foka és ennek megfelelõ típusa létezik. Itt csak kettõt említünk meg. A formai ellenõrzés viszonylag egyszerûbb feladat. Ezalatt azt értjük, hogy egy adott mezõben valóban az ott engedélyezett érték áll-e. Árulkodó jel, ha egy családnév pontosvesszõt tartalmaz, vagy egy személy testmagassága három és fél méter. Sokkal bonyolultabb kérdés a strukturális integritás ellenõrzése. Ezalatt azt kell értenünk és ellenõriznünk, hogy nem sérült-e meg valamely feltételezés, amelyre az adatbázis szerkezetének megtervezésekor építettünk.Leggyakrabban elõforduló ilyen hiba az egyértelmûség, pl. nem mohamedán országokban probléma lehet, ha egy férfiról egyidejûleg két érvényes bejegyzés van különbözõ feleségekkel. 1.2.3. Szinkronitás.
A ma használatos adatbáziskezelõ rendszerek általában többfelhasználósak, és nagyon gyakran térben elosztott számítógéphálózaton üzemelnek.Rendkívüli fontossággal bír, hogy az azonos adatokon közel egyidõben mûveleteket végzõ felhasználók beavatkozásainak ne legyenek nemkívánatos mellékhatásai egymás tevékenységére, illetve az adatbázis tartalmára. 1.3. Személyek.
Az adatbázissal kapcsolatba kerülõ személyeket tevékenységük és jogosultságaik szerint csoportokra oszthatjuk. 1.3.1. Képzetlen felhasználó.
A felhasználók legszélesebb rétege, akik csak bizonyos betanult ismeretekkel rendelkeznek a rendszerrõl (pl. légitársaság alkalmazottja, amikor helyet foglal egy járatra), vagy még ennyivel sem (pl. áruházi katalógus lapozgatója).
1.3.2. Applikáció programozó.
4
Applikáció programozó az a szakember, aki a (képzetlen) felhasználó által látott programot készíti és karbantartja. Szaktudásánál fogva ismeri azt a nyelvet, amely lehetõvé teszi az adatbázisban tárolt adatok elérését. A felhasználó nemcsak a csupasz adatokat szeretné látni, hanem feldolgozást is akar végezni rajtuk. A programnak kommunikálnia kell a felhasználóval. Ezek olyan feladatok, amelyek ugyan programozót igényelnek, de nem szükséges, hogy az illetõ az adatbázis belsõ szerkezetébe is belelásson, vagy a szerkezetet (a tárolt adatok megõrzése mellett) módosítani tudja. 1.3.3. Adatbázis adminisztrátor.
Hagyományosan így nevezzük azt a személyt, aki az adatbázis felett gyakorlatilag korlátlan jogokkal bír. Vannak olyan kitüntetett tevékenységek, amelyeket kizárólag õ végezhet el az adatbázison, mint pl.: Generálás: Az adatbázis szerkezetének kijelölése, és annak meghatározása, hogy milyen állomány-szerkezetben tároljuk az adatokat. Jogosítványok karbantartása: A hozzáférés jogának naprakészen tartása, módosításai. Szerkezetmódosítás: Az adatbázis eredeti szerkezetének módosítása. Ez feltételezi azt az alapvetõ igényt, hogy egyetlen adat se semmisüljön meg azért, mert az összetartozó adatok mellé újabbakat is felveszünk a tárolandók közé. Mentés-visszaállítás: Biztonsági okokból idõszakonként másolatot célszerû készíteni az adatbázisról. Ha az adatbázis megsérül, ez a másolat teszi lehetõvé a visszaállítást a mentés idõpontjának állapotába. A mentést alkalmas célprogram felhasználásával bárki elvégezheti, de a visszaállítás nagy körültekintést igénylõ feladat. 1.4. Az absztrakciós szintek. •
•
•
Fizikai adatbázis: Fizikai adatbázison azt értjük, hogyan helyezkednek el az adatbázis adatai a fizikai tárolókon. Ide érthetjük a fizikailag megvalósított szerkezetet is (ld. késõbb). Fogalmi (logikai) adatbázis: Fogalmi adatbázis voltaképpen az a modell, ahogyan az adatbázis tükrözi a valós világot. Azt határozza meg, hogy melyik adatot hogyan kell értelmezni. Nézet (view): Nézet az, amit a felhasználó az adatbázisból lát. Ha az adatbázisnak több felhasználási lehetõsége van, ezek mindegyikéhez külön nézet tartozhat. Ez lehet a felhasználók jogosítványaihoz kötött is. (pl. a légitársaság egységes nyilvántartásából más adatok érdekesek, ha a pilóták szabadságolási tervét készítjük, és az adatok másik körére van szükségünk, ha egy gép utaslistáját akarjuk megtekinteni.)
5
2. Elvi alapok. 2.1. Sémák és esetek.
El kell különítenünk az adatbázis tervezésének és felhasználásának idõszakát. A tervezéskor elsõsorban egy modell felállítása és az adatbázis felépítése a célunk. Erre szolgálnak az adatbázis mûködését leíró sémák. Amikor az adatbázist már használjuk, a (sémáknak megfelelõen tárolt) információ lép elõtérbe. Az adatbázis sémák konkrét adatait eseteknek (néha példányoknak) nevezzük. 2.2. Adatfüggetlenség.
Az adatfüggetlenség igényén azt értjük, hogy az adatok tárolásának szervezésében ne használjuk olyan megoldásokat, amelyek az adatbázis megváltoztatásakor nem biztosítják az adatok változatlanságát. (Vagyis az adatoknak a változásoktól kell függetlennek lenniük.) Ennek megfelelõen kétféle adatfüggetleségrõl szokás beszélni. Fizikai adatfüggetlenségen azt értjük, hogy a fizikai mûködés sémáiban véghezvitt változások ne érintsék a fogalmi (logikai) adatbázist. Logikai adatfüggetlenségrõl akkor beszélünk, ha a logikai adatbázis megváltozása nem jár az egyes felhasználásokhoz-felhasználókhoz tartozó nézetek megváltozásával. 2.3. Entitások.
Entitásnak (egyednek) megkülönböztethetõ.
nevezünk
mindent,
ami
létezik
és
2.4. Attributumok.
Az entitásoknak jellemzõik vannak. Ezeket attributumoknak vagy tulajdonságoknak nevezzük. Azt az attributumot, vagy az attributumok egy csoportját, amelynek alapján keresünk (el akarjuk dönteni, hogy az illetõ entitás számunkra érdekes-e, vagy sem), kulcsnak nevezzük. 2.5. Kapcsolatok
A kapcsolatoknak (relationship) három alapvetõ típusa van. Az elsõ az úgynevezett egy-egyes típusú kapcsolat (pl. házastársa), a másik a többesegyes típusú (pl. beosztottja). Mindkét esetben egyszerûen eldönthetõ a hozzátartozás kérdése. Ábrázolási problémák miatt ritkábban használjuk a többes-többes típusú kapcsolatokat (pl. osztálytársa). . 2.6. Ábrázolás.
Gyakran alkalmazzák az ú.n. entity-relationship (ER, magyarul: egyedkapcsolat) ábrázolásmódot. Ennek lényege, hogy az entitáshalmazokat téglalapokkal ábrázoljuk, az attributumokat körökkel, a kapcsolatokat rombuszokkal jelöljük. Az összetartozást élekkel jeleníthetjük meg.
6
3. Fizikai adatszervezés. Ebben a fejezetben arról esik szó, hogyan tároljuk az adatbázis adatait lehetõleg hatékonyan - egy nem felejtõ tárolóeszközön (mágneslemez). Ennek mégértéséhez az adott tárolóeszköz ismerete is szükséges. 3.1. Heap szervezés.
Ennél az ábrázolási módnál a legegyszerûbben közelítjük meg a tárolás problémáját. Az adatokhoz külön strukturát nem rendelünk hozzá. 3.1.1. Keresés.
Mivel mindent éppen akkora helyen tárolunk, amennyi hely szükséges, a tárolásban nincs rendszer. A keresés általában valamennyi adat elolvasását jelenti, amíg csak rá nem találunk a keresettre. 3.1.2. Törlés.
A törlendõ adatot megkeressük. Jelezzük, hogy a terület felszabadult. A szemétgyûjtõ programmodul e jelzés alapján tudja, hogy ezt a területet ki lehet söpörni. 3.1.3. Beszúrás.
Elõször a törlés által felszabadított területeken próbálkozunk. Ha már nincs hely, az állomány végére vesszük fel. Meg lehet próbálkozni a tárolt adatok átrendezésével, a felszabadított helyek összegyûjtésével is. Ezt szemétgyûjtésnek (garbage collection) szokás nevezni. 3.2. Hash állományok.
A hash címzés vagy csonkolásos címzés onnan kapta nevét, hogy elvileg a keresés kulcsának bitmintájából csonkolással nyerhetõ cím is használható címgenerátorként.Az ábrázolás lényege u.i. az, hogy a kulcsból, mint bitmintából, alkalmas függvény segítségével címet generálunk, és ezt tekintjük a keresett adatsor címének. A függvénynek olyannak kell lennie, hogy a szóba jöhetõ kulcsokat egyenletesen terítse a címtartományon. Minden bejegyzett adatsort kiegészítünk két bittel. Az egyik jelentése "szabad/foglalt", a másiké pedig "még sohasem volt foglalt". Ha valóban csonkolásos címgenerátort használunk, akkor a címtartomány kettõnek egészszámú hatványa. Az adatok helyét logikailag tömbnek képzeljük, így az adatrekordok rögzített hosszúságúak. Változat: az ú. n. "vödrös" hash (bucket hashing), amikor a hash függvény által visszaadott cím csak adatblokkok halmazát (vödör) azonosítja. A "vödrön" belül a tárolás a heap-hez hasonló.
3.2.1. Beszúrás.
Kérünk egy címet a kulcs alapján. Ha a rekesz még sohasem volt foglalt, akkor használttá és foglalttá tesszük. Az adatot beírjuk. Ha már volt foglalt, de most szabad, foglalttá tesszük és beírjuk az adatot. Ha foglalt, egy túlcsordulási területre írjuk az adatokat.. 3.2.2. Keresés.
7
Kérünk egy címet a kulcs alapján. Ha a rekesz még sohasem volt foglalt vagy törölt, akkor az adat nem szerepel. Ha a rekesz egy túlcsordulási területre mutató pointert tartalmaz, akkor a túlcsordulási területen lineáris keresést végzünk. Mindig ellenõrizzük a kulcsot. Ha egyezik, megvan az adat, ha nem, új címet kérünk.
3.2.3. Törlés.
Megkeressük a kívánt adatot. Foglaltsági jelzését szabadra állítjuk. 3.3. Indexelt állományok.
Alapgondolata, hogy a kulcsot egy index állományban megismételjük, és a tárolt adatrekordra mutatót állítunk. A kulcsot rögzített hosszúsággal ábrázoljuk. Az index állományt mindig rendezve tartjuk. Az adatállomány méretével takarékoskodhatunk, ha megtartjuk a korábban megismert foglaltsági jelzés bitet. Ha minden egyes adatrekordra mutat mutató: sûrû index. Ha csak adatrekordok halmazára - tipikusan blokkokra -: ritka index. Ilyenkor az adatállományt is rendezetten kell tárolni. 3.3.1. Beszúrás sûrû indexek esetén
Keresünk egy üres helyet a tárolandó adatnak. Ha nem találunk, akkor az állomány végére vesszük fel. Beállítjuk a foglaltsági jelzést, és beírjuk az adatot. A kulcsot és a tárolás helyére hivatkozó mutatót a kulcs szerint berendezzük az index állományba. 3.3.2. Keresés sûrû index esetén
Az index állományban megkeressük a kulcsot, pl. bináris kereséssel. A hozzá tartozó mutatóval elérhetjük a tárolt adatot. 3.3.3. Törlés sûrû index esetén
Megkeressük a kívánt adatot. Foglaltsági jelzését szabadra állítjuk. A kulcsot kivesszük az index állományból, és az index állományt tömörítjük. 3.4. B - fák, mint többszintes ritka indexek
Az index állomány tárolásában is különbözik az elõzõtõl. A bináris keresésnél gyorsabb, logk-val arányos keresési idõt érhetünk el, ha az indexeket pontosan k-ágú fában tároljuk. A legalsó szint mutatói az adatállomány egy-egy blokkjára mutatnak, a fölötte levõ szintek mutatói pedig az index állomány egy-egy részfáját azonosítják. Az egy csomópontban ábrázolt k mutatóhoz elegendõ k-1 kulcs tárolása, mert a kulcs jelentése a kijelölt részfában tárolt legkisebb érték. Így az elsõ bejegyzés nem hordozna információt. 3.4.1. Beszúrás.
Megkeressük, hogy a tárolandó adat melyik blokkba tartozik. Ha nem találunk ott számára helyet, akkor új blokkot kérünk. Beírjuk az adatot. A megfelelõ kulcsot és a tárolás helyére hivatkozó mutatót a kulcs szerint berendezzük az index állomány legalsó szintjébe. Ha a szint betelt, új szintet
8
nyitunk, és az egész fát rendezzük. Egyébként csak az érintett részfát rendezzük. 3.4.2. Keresés.
Az index állományban logaritmikus kereséssel megkeressük a kulcsot. A hozzá tartozó mutatóval elérhetjük a blokkot, amelyben az adatot tároljuk.
3.4.3. Törlés.
Megkeressük a kívánt adatot és töröljük. Az adatblokkokat lehetõség szerint összevonjuk. Ilyenkor egy kulcsot kivesszük az index állomány érintett részfájából. Ehhez adott esetben az egész fát rendezni kell. 3.5. Másodlagos indexek, invertálás
A cél az, hogy több kulcs szerint is kereshessünk. Szélsõ esetben akár minden mezõ lehet kulcs. Ennek következménye, hogy több index állományra lehet szükségünk. Szélsõ esetben ez kétszeres tárolási igényt jelent az adatokra nézve, ha a mutatók tárolásának helyfoglalásától eltekintünk. Elvi probléma azonban nem merül fel. Ez a helypazarlás elkerülhetõ az invertálási technikával. Az index állományok mutatói nem egy igazi adatállományra mutatnak, hanem helyettük az egyes mezõk a megfelelõ index állományokba mutató hivatkozásokat tartalmaznak. Ez beszúrás és törlés esestén kétszeres adminisztrációt jelent, ezért gyakran az index állományok végén függeléket használnak, ahol az újabban felvett adatok bejegyzéseit tárolják. A függelékben lineáris keresés alkalmazható. Ha a függelék mérete egy kritikus határt elér, az index állományokat rendezni kell. Módosítani legegyszerûbben törlés-beszúrás módszerével lehet. 3.5.1. Beszúrás.
Keresünk egy üres helyet az invertált állományban. Ha nem találunk, akkor az állomány végére vesszük fel. Beállítjuk a foglaltsági jelzést, de nem írunk be semmit. Valamennyi kulcsot berendezzük az index állományokba, és az állományokat szükség szerint rendezzük. Kitöltjük az invertált állományban lefoglalt helyet az index állományokra hivatkozó mutatókkal. 3.5.2. Keresés.
A megfelelõ index állományban megkeressük a kulcsot. A hozzá tartozó mutatóval elérhetjük az invertált állományban tárolt adatot. A kiolvasott mutatókkal összeállítjuk a rekordot az egyes index állományokban tárolt kulcsokból. 3.5.3. Törlés.
Megkeressük a kívánt adatot az invertált állományban. Valamennyi index állományból töröljük a hivatkozásokat. Ha szükséges az állományokat rendezzük. Az invertált állományban a bejegyzés foglaltsági jelzését szabadra állítjuk.
9
4. Adatmodellek. Az adatmodell meghatározza, hogy az adatbázisban az adatok milyen strukturában tárolódnak és milyen mechanizmusokon keresztül lehet az adatokhoz hozzáférni. Így az adatbáziskezelõ rendszer legalapvetõbb tulajdonságait rögzíti. Egyetlen adatbáziskezelõ rendszer mindig egyetlen adatmodellnek megfeleõen mûködik. 4.1. A hálós adatmodell. 4.1.1. Története
A hálós adatmodellre épülõ adatbázisok mintapéldája a COBOL nyelv szabványosításáról ismert Conference on Data System Languages (CODASYL) Data Base Task Group (DBTG) nevû csoportjához fûzõdik. Az általuk kidolgozott ajánlásnak két eleme van. Az adatdefiníciós formalizmus Subschema Data Definition Language (Subschema DDL) néven, az applikációs programok írására alkalmas formalizmus Data Manipulation Language (DML) néven vált ismertté. 4.1.2. Alaptulajdonságok
A hálós adatmodell egy olyan egyed-kapcsolati adatmodell, amely csak többes-egyes típusú bináris kapcsolatokat enged meg a típusok szintjén.. Ez a megszorítás lehetõvé teszi, hogy adatainkat egyszerû irányított gráffal jellemezzük. Ez a kapcsolatok implementálását is megkönnyíti. Alepvetõ stuktúraegysége a rekord, amely számos atomi komponensbõl tevõdhet össze. A következõ strukturaegység lehetõvé teszi a rekordok összetartozásának megjelenítését láncolás formájában, így születnek meg a CODASYL terminológia szerinti Set-ek. A rekordok egyidejûleg több kapcsolatban is szerepet játszhatnak, így a rekordok változatos módon kapcsolódhatnak össze. Innen az adatmodell elnevezése. Egy TR rekord-típus egy olyan AR1, AR2,....,ARn, n-es (tupel) ahol ARi-k az attributumnevek és minden ARi-hez egy Di halmaz, az attributum domain-je is hozzátartozik. Egy TR típusú, n-elemû rekord a D1.....Dn halmazok Descartes szorzatának egy eleme, szintén egy n-es: (d1, d2,....,dn). Az összetartozó rekordok rendezett összefogása céljából vezették be a Set fogalmát, amely kétféle rekordból áll: egy (akár üres) halmaza az egyenrangú, összeláncolt Memberrekordoknak, és pontosan egy Owner-rekord, aminek a Member-rekordok alárendeltek. Az összeláncolt rekordok ugyanannak a kapcsolatnak a példányait (eseteit) valósítják meg. Legyen TR1és TR2 két rekord-típus és legyenek ℑ(TR1) és ℑ(TR2) a konkrét eseteik halmazai. Ekkor a TS Set-típust a TS:=TR1×TR2 mûvelettel definiálhatjuk, ami egy ℑ(TR1) → ℑ(TR2) kapcsolatot ír le. TR1 az Owner-típus, TR2 pedig a Member-típus. A Set-típusokat grafikus ábrázolásban hagyományosan a Member-típustól az Owner-típushoz irányított nyilakkal jelezzük. 10
4.1.3. Egyed-kapcsolati diagrammok a hálós modellben.
Alkalmazzunk egy trükköt arra az esetre, ha nem csupán többes-egyes bináris kapcsolatokat kellene ábrázolnuk a típusok szintjén, hanem többestöbbes típusút is, amit modellünk nem enged meg. Vezessünk be egy új logikai rekord típust, amelynek az lesz az egyetlen szerepe, hogy minden olyan egyedhez, amely eleme volt a tiltott kapcsolatnak, húzunk egy összeköttetést jelentõ élet, és ezzel minden kapcsolatot többes-egyes típusúvá tettünk. 4.1.4. Mûveletek
Hálós modellre épülõ adatbázisban végzett mûveleteknek két fontos jellemzõje van. • Mindenkor egyetlen rekordot, és nem pedig rekordok halmazát jelöljük ki (rekordorientált tulajdonság). • Azért, hogy egyik rekordot a másik után elérhessük, a megvalósított "hálóban" "mozognunk" kell - szigorúan a struktura által meghatározott módon -, amit navigációnak neveznek. A navigációt ú. n. Currency-indikátorok (hol vagyok most, ill. hol voltam?) támogatják. 4.2. A hierarchikus adatmodell
A legrégebbi adatmodell. Lényegében az IBM cég IMS (Information Management System) rendszerén alapul. Ma már új rendszereket nem telepítenek, jelentõsége csekély, elsõsorban elvi. 4.3. A relációs adatmodell.
A reláció szót itt a korábban hivatkozott halmazelméleti értelemben használjuk. Adott tehát n halmaz, amelyekbõl képzett Descartes-szorzat egy részhalmaza a reláció. A halmazokat gyakran tartományoknak (domain) is nevezzük, a tartományok elnevezését pedig az adott reláció attributumainak azonosítására használjuk - hasonlóan a hálós modellnél megszokotthoz. Magát a relációt is névvel látjuk el. Jelölésként szokás a reláció neve után az attributumnevek zárójelek közötti felsorakoztatása, pl.: SZEMÉLY (NÉV, KOR, FOGLALKOZÁS), amit relációs sémának nevezünk. Áttekinthetõbben ábrázolhatjuk relációnkat táblázatos formában. A táblázat oszlopai jelentik a tartományokat, sorai pedig a relációban álló n-esek konkrét elõfordulásait. A fejlécbe az attributumok megnevezése kerül. NÉV KOR FOGLALKOZÁS Nagy Vazul 37 üzletkötõ Kovács Oszkár 4 óvodás Délceg Bendegúz 72 teremõr A reláció matematikai definíciója: Legyenek adottak a T1,T2,...Tn halmazok és képezzük ezen halmazok T=T1×T2×...×Tn Descartes-szorzatát. (A T szorzat elemei olyan t1,t2,...tn elem n-esek, amelykre t1∈T1, t2∈T2, ...tn∈Tn .) A T szorzathalmaz egy R résztartományát relációnak, a T1,T2,...Tn halmazokat pedig a reláció tartományainak nevezzük. − A relációban lévõ oszlopok (tulajdonságok, attribútumok, tartományok) számát a reláció fokának nevezzük. 11
− A relációban lévõ sorok számát (a konkrét elõfordulások számát) a reláció számosságának nevezzük. − Azt a tulajdonságot vagy tulajdonsághalmazt, amely a táblázat egy-egy sorát egyértelmûen meghatározza, kulcsnak nevezzük. A reláció: − nem tartalmaz két azonos sort − a sorok sorrendje nem számít, − az oszlopoknak egyértelmû neve, helye van. 4.3.1. Mûveletek relációs adatbázisokon.
A halmazelméletben megismertek alapján néhány halmazalgebrai mûveletet relációs mûveletként kívánunk használni, vagyis operandusaink relációk, illetve ezek attribútumai. 4.3.1.1. Egyesítés (unió).
Az egyesítés feltétele, hogy az egyesítendõ relációknak azonos n-esekbõl kell állniuk. Nem szükséges azonban, hogy ezek ténylegesen azonos attributumokat jelensenek. Ez azt jelenti, hogy a mûveletet ilyenkor mindig el tudjuk végezni, de nem biztos, hogy az eredmény attributumait sorszámukon kívül nevükkel is azonosítani tudjuk. Pl.: R1 R2 R1∪R2 A B C D E F 1 2 3 a b c a c d a b c c b a a d c a c d a d c b b c c b a a d c b b c 4.3.1.2. Különbségképzés.
Ugyanazok a megkötések érvényesek, mint az egyesítésnél. Pl.: R1 R2 R1 - R2 A B C D E F 1 2 a b c a c d a b c b a a d c c b a d c b b c
3 c a
4.3.1.3. Descartes-szorzat.
Az eredmény olyan (n1+n2)-esekbõl áll, amelyeknek elsõ n1 eleme az elsõ operandusból, második n2 eleme a második operandusból származik, ebben a rögzített sorrendben. Az operandusok szerkezetére ebben az esetben semmilyen megkötést sem kell tennünk. Pl.: R1 R2 R1 × R2 A B C D A B C D a b c d a b c d b a a c a b a c b a c d b a a c
12
4.3.1.4. Vetítés (projekció).
A mûvelet azt jelenti, hogy egy meglevõ R reláció egyes összetevõibõl egy új relációt hozunk létre. Ehhez ki kell jelölnünk, hogy mely összetevõket kívánjuk felhasználni, és az új relációban mi legyen a sorrendjük. A halmazalgebrában szokásos jelölésmód az eredeti összetevõket sorszámukkal azonosítja. Ez megengedett a relációalgebrában is, de szokás helyette az attributumokat nevükkel azonosítani. Π 1,3,7,2 (R) Fenti jelölés azt írja elõ, hogy vegyük az R reláció elsõ, harmadik, hetedik és második attributumát és ebben a sorrendben vegyük fel az új relációba az attributumok értékeit. Hasonlóképpen Π TÍPUS,ÉVJÁRAT,FOGYASZTÁS. (GÉPKOCSI) Az eredeti GÉPKOCSI reláció ezen kívül tartalmazhatta még pl. az ÁR, RENDSZÁM, ELSÕ TULAJDONOS, VIZSGA ÉRVÉNYESSÉGE, stb. attributumokat. 4.3.1.5. Kiválasztás (szelekció).
A kiválasztás mûvelete egy valódi vagy nem valódi részhalmaz képzése az R reláción, amelynek vezérlésére egy logikai kifejezés szolgál. Az R reláció valamennyi elemére kiértékeljük a logikai kifejezést, és azokat az elemeket vesszük be az új relációba, amelyekre a kifejezést igaznak találtuk. Jelölése: σK (R), ahol K a logikai kifejezés. A logikai kifejezés felépítése a következõ lehet: - operandusok, lehetnek konstansok, vagy összetvõk azonosítói, - aritmetikai operátorok, (< = > ≥ ≤ ) - logikai operátorok (∧ ∨ ¬) Megjegyezzük, hogy az egyértelmûség érdekében a numerikus konstansokat is aposztrófok közé írjuk, hogy meg lehessen különbözteni az összetevõk sorszámától. σ2>5 (R) Ennek megfelelõen az R reláció azon elemeinek halmazát jelenti, amelyekre igaz, hogy a második összetevõ értéke nagyobb az ötödik összetevõ értékénél, σKOR<'23'∧ 1='Kovács' (NÉVSOR) pedig a NÉVSOR reláció azon elemeit, amelyeknek KOR azonosítójú összetevõje kisebb huszonháromnál, elsõ összetevõje pedig Kovács. 4.3.1.6. Metszet.
Értelme megegyezik a halmazalgebrai jelentéssel. Pl.: R1
A a c a
B b b d
C c a c
R2
D a a b
E c d b
4.3.1.7. Hányados.
13
F d c c
R1 ∩ R2 1 a
2 d
3 c
A hányadosképzés voltaképpen szójáték, ugyanis a Descartes-i szorzás megfordítását jelenti. Jelölje R ÷ S azt a relációt, amely úgy keletkezik, hogy vesszük az R reláció n1 -esei közül azokat, amelyek utolsó n2 összetevõje mint önálló n2-es - eleme az S relációnak, és elsõ (n1-n2 ) elemet - mint (n1n2)-est vegyük be az R ÷ S relációba.
14
Pl.:
R A a a a e e e
R÷S A B a b e f
S B b b b f f f
C a c d a c a
D c d c c d d
C a c
D c d
4.3.1.8. Természetes illesztés (más néven összekapcsolás) (natural join)
Legyen R és S két reláció, amelyeknek van legalább egy, de akár több név szerint megegyezõ összetevõje (azonos attributuma). Vegyük sorra a két reláció valamennyi elemét, és válasszuk ki azokat, amelyeknek a megegyezõ nevû összetevõi érték szerint is megegyeznek. Egyesítsük ezeket olyan Descartes-szorzattá, amelyben a mindkét relációban szereplõ attributumokat csak egyszer vesszük figyelembe. Jelölése: R Pl.: R A a a a b c
S S B b b b c d
C c d e e a
B b b b b d
C c c e e a
R D d e f a f
S A a a a a c
B b b b b d
C c c e e a
D d e f a f
4.3.1.9. Θ-Illesztés (Θ-join)
Legyen R és S két reláció. Θ jelölje valamelyik aritmetikai hasonlító operátort. R és S reláció Θ-illesztésén az i,j pontban azt a relációt értjük, amely az R és S relációk Descartes-szorzatának az a részhalmaza, amelyre igaz, hogy az R-beli n-es i-edik összetevõje az adott hasonlító relációban áll az S beli n-es j-edik összetevõjével. S Jelölése: R iΘj Pl.: S R S R 2=1 A B C D E F A C E F a b c b c d a b c c d a a d b c e a b c c e a d e a e f a b c e a b c e b e a a a d e f c d a d a f a d e a f c d a a f
15
5. Relációk normál alakjai A relációk tetszõleges számú tulajdonságokból építhetõk fel. Elvileg a rendszerben található valamennyi adatot beépíthetjük egyetlen relációba. Ekkor egy tábla írja le az egész rendszert. Ezt univerzális relációnak nevezzük. Ez a reláció általában sok redundanciát tartalmaz, ezért a rendszert több relációból alakítjuk ki, biztosítva ezáltal a redundanciamentességet. Azon adatokat, melyek valamely egyed jellemzõje, alapadatnak nevezzük. Pl: valakinek a születési dátuma. Azokat az adatokat, amelyek alapadatuk alapján meghatározhatóak, származtatott adatoknak nevezzük. Pl.: milyen napra esik a születésnap. Redundancia: A feleslegesen többször tárolt alapadatok, vagy a tárolt származtatott adatok. A redundancia részben az adatbázis méretét növeli meg, másrészt következetlenné teheti az adatbázist. (Pl.: a többször tárolt adatok egyikét már megváltoztattuk amikor rendszerhiba miatt a tranzakció megszakad.)
Nem minden többször tárolt adat jelent redundanciát. Pl.: név
tanszék
TOTH ISTVÁN KISS JÁNOS NAGY TIBOR VERES PÉTER NOVÁK BÉLA
MATEMATIKA MATEMATIKA GÉPTAN GÉPTAN HõTAN
tsz.telefo n 1664-555 1664-555 1345-233 1345-233 2313-454
tanszékvezet õ BOROS Z. BOROS Z. KOVÁCS I. KOVÁCS I. ERDõS G.
Itt a tanszék nevének tárolása szükséges, nem jelent redundanciát, azonban a többször tárolt tanszékvezetõ már igen. 5.1. Anomáliák.
Relációinkat kénytelenek vagyunk függõlegesen felbontani (dekomponálni), mert minden logikailag összetartozó adatnak egyetlen sémában történõ ábrázolása (univerzális reláció) problémákat vet fel. 5.1.1. Tárolási anomália.
Mivel egy adat több sorban is szerepelhet, szükségtelenül pazaroljuk a tárat. pl.: tanszékvezetõ 5.1.2. Módosítási anomália.
Ugyanazt az adatot több helyen kell módosítani. Ez veszélyforrás. 5.1.3. Beszúrási anomália.
Nem tudunk olyan adatot nyivántartásba venni, amely kell ugyan, de jelenleg nincs olyan adat, amellyel az adott relációban állna. Pl.: ha a egy új tanszéknek még nincs tanszékvezetõje. 5.1.4. Törlési anomália.
Mivel csak egész sorok törölhetõk, elveszíthetünk olyan adatot is, amelyre még szükségünk lehet, de már nem marad belõle példány. 16
Pl.: NOVÁK BÉLA kilép, eltünik a HÕTAN tanszék is. 5.2. Funkcionális függõség.
Annek érdekében, hogy "hasznos" felbontásokat tudjunk kitalálni, az attributumok kapcsolatainak mélyebb vizsgálata szükséges. Legyen adott az R(A1,A2,...An) reláció, ahol Ai az attributumokat jelöli. Legyen X és Y a relációk attributumainak részhalmaza. X ⊆ {A1,A2,...An} és Y ⊆ {A1,A2,...An} Jelöljük R[XY]-nal az R relációnak azon vetületét, amely csak az X∪Y attributumokat tartalmazza. Az R(A1,A2,...An) reláción akkor és csak akkor áll fenn az X →Y funkcionális függõség, ha az idõ minden pillanatában érvényes R[XY]-ban az R[X] → R[Y] leképezés. Más szavakkal a funkcionális függõség azt jelenti, hogy a tulajdonságok által meghatározott rendszerben egy tulajdonság egyártelmûen meghatároz egy vagy több másik tulajdonságot. Általánosan Y akkor függ funkcionálisan X-tõl, ha X minden értékéhez hozzárendelhetõ Y egy és csakis egy értéke. A funkcionális függõségek meghatározása koncepcionális (modellezési) kérdés. Ha X →Y és Y nem függ funkcionálisan X egyetlen részhalmazától sem, akkor X -et Y determinánsának nevezzük. Pl.: tanszék → tsz.telefon (feltéve, hogy a tanszéknek csak egy telefonja van). Viszont tsz.telefon → tanszék mindig igaz (Ugyanis egy telefonszám nem rendelhetõ több tanszékhez). 5.2.1. Megjegyzések.
a) Ez azt jelenti, hogy xy, xy' ∈ R[XY] ⇒ y = y' b) A leképezés maga lehet az idõben változó, csak az egyértelmûségnek kell megmaradnia. c) X →Y olvasata: X meghatározza Y-t, ill. Y X-tõl függ. Pl.: R(NÉV,CÍM,VÁROS,IRÁNYÍTÓSZÁM,TELEFON) relációs sémában a valóságot "elég jól" modellezõ funkcionális függõségek pl. az alábbiak: NÉV → CÍM NÉV → VÁROS NÉV → IRÁNYÍTÓSZÁM NÉV→ TELEFON CÍM,VÁROS → IRÁNYÍTÓSZÁM IRÁNYÍTÓSZÁM → VÁROS 5.3. Kulcsok.
A fizikai modelleknél már használtunk egy kulcs fogalmat. Ott azt mondtuk, kulcs minden, ami szerint keresni akarunk.Most megadjuk egy reláción értelmezett kulcs matematikai definícióját.
17
Legyen adott az R(A1,A2,...An) reláció, ahol Ai az attributumokat jelöli. Legyen X a relációk attributumainak részhalmaza: X ⊆{A1,A2,...An}. • •
X-et akkor és csak akkor nevezzük kulcsnak az R reláción, ha X meghatározza R valamennyi attributumát, vagyis X → Ai ahol i = 1,2,....n és X-nek nincs olyan valódi részhalmaza, amely meghatározza R valamennyi attributumát, vagyis X-bõl bármit elhagyva már nem a teljesíti az elsõ feltételt, más szavakkal minimális, vagyis nem létezik X'⊆X ; X'→Aj, i= 1,2,....n.
5.3.1. Szuperkulcs, kulcs
X-et szuperkulcsnak nevezzük az R reláción, ha a kulcsokra vonatkozó két kritérium közül csak az elsõt teljesíti. Más szavakkal akkor, ha tartalmaz kulcsot. Definíció: Legyen adott az R reláció a T tulajdonságok halmaza felett. A tulajdonságok egy olyan K részhalmazát, melynek értékei az R reláció egy sorát egyértelmûen meghatározzák, a reláció szuperkulcsának nevezzük. Ha K szuperkulcs, de K'⊆K már nem az, akkor K minimális kulcs, vagy egyszerûen kulcs. Ha K egy tulajdonságból áll, akkor egyszerû kulcs, egyébként összetett kulcs. • a kulcs a relációnak egy és csakis egy sorát határozza meg • a kulcsnak nincs olyan részhalmaza, amely szintán kulcs lenne • a kulcs tulajdonságok nem lehetnek nulla-értékûek • minden relációnak van kulcsa • egy relációnak több kulcsa is lehet 5.3.2. Minden relációnak van kulcsa.
Legyen adott a korábban látott R reláció. Válasszuk X-nek az attributumok teljes halmazát. Ez a kulcsokra vonatkozó elsõ feltételnek eleget tesz, hiszen nincs olyan attributum, amit ne vettünk volna figyelembe. Ha a második feltétel teljesül, akkor kulcs, ha pedig nem, akkor szuperkulcs, tehát tartalmaz kulcsot. 5.3.3. Elsõdleges kulcs.
Ha a relációnak több kulcsa is van, kiválasztunk egyet, amelyet elsõdleges kulcsnak nevezünk. A többi kulcsot kulcsjelöltnek hívjuk. 5.3.4. Idegen kulcs.
Egy relációban lehetnek olyan tartományok, amelyek másik relációban a sorokat egyértelmûen azonosítják, tehat ott kulcsok, de ebben a relációban nem. Ezeket idegen kulcsoknak nevezzük. 5.4. Funkcionális függõségek tulajdonságai.
Armstrong három axiómája a funkcionális függõségrõl: 5.4.1. Reflexivitás
Ha ugyanazon R reláción X ⊆Y , akkor Y → X. Szokás triviális függõségnek nevezni. 5.4.2. Tranzitivitás.
18
Ha ugyanazon R reláción X → Y és Y→Z , akkor X → Z . 5.4.3. Bõvíthetõség.
Ha ugyanazon R reláción X → Y , akkor XZ → YZ
19
5.5. A tulajdonságok következményei. 5.5.1. Egyesítési szabály.
Ha X → Y és X →Z , akkor X →YZ 5.5.3. Pszeudotranzitivitás.
Ha X →Y és WY → Z , akkor XW → Z 5.5.4. Dekompozíciós szabály.
Ha X→Y és Z⊆ Y, akkor X→Z 5.6. Veszteségmentes felbontás (lossless decomposition)
Egy reláció tetszõlegees függõleges felbontásakor információt veszíthetünk, ami abban nyilvánulhat meg, hogy új sorok is keletkezhetnek az újraegyesítéskor. Az R reláció ρ(R1,R2,...Rn) felbontását (ahol ∪Ri=R) veszteségmentesnek mondjuk, ha n Ri , azaz új sor nem keletkezik. R= i=1 5.6.1. Tétel: Az R reláció ρ(R1,R2) dekompozíciója akkor és csak akkor veszteségmentes, ha (R1∩ R2) →(R1- R2) vagy (R1∩R2) → (R2-R1) 5.6.2. Példa dekompozíciókra.
Legyen adott R(X,Y,Z), és képezzük ebbõl R1(X,Y)-t és R2(Y,Z)-t, majd egyesítsük R'(X,Y,Z)-vé. Látni fogjuk, hogy könnyû olyan példát találni, hogy R(X,Y,Z) ≠ R'(X,Y,Z). Veszteséges: R(X,Y,Z) R'(X,Y,Z) R(Y,Z) (Z→X) nem igaz (Z→X) R(X,Y) X Y Z X Y Y Z X Y Z a c e a c c e a c e a d f a d d f a c g b c g b c c g a d f b d h b d d h a d h b c e b c g b d f b d h Veszteségmentes: R(X,Y,Z) R1(X,Z) R2(Y,Z) ekkor XZ∩ YZ = Z és XZ-YZ = X R(X,Y,Z) R'(X,Y,Z) R(Y,Z) (Z→X) igaz (Z→X) R(X,Z) X Y Z X Z Y Z X Y Z a c e a e c e a c e a d f a f d f a d f b c g b g c g b c g 20
b
d
h
b
h
d
h
b
d
h
5.6.3. Függõségõrzõ felbontások
Egy relációs séma veszteségmentes felbontása eredményezheti, hogy a rész-sémákban nem tudjuk többé az eredeti sémában érvényes függõségeket alkalmazni. Ennek következtében a rész-relációinkba nem megengedett adatok is bekerülhetnek. Célszerû ezért olyan sémákat konstruálni, amelyekre a funkcionális függéseket vetítve a vetített függésekbõl az eredeti függõségek (az Armstrong axiómák segítségével) helyreállíthatók. Ekkor a felbontás függõségõrzõ. Egy felbontás lehet veszteségmentes és függõségõrzõ, veszteségmentes és nem függõségõrzõ, nem veszteségmentes(veszteséges) és függõségõrzõ, nem veszteségmentes és nem függõségõrzõ. 5.7. A 0. és 1. normál forma.
A relációk normál alakjai közül a nulladiknak és elsõnek nincs gyakorlati jelentõsége. 0NF alakúnak kell tekintenünk minden olyan relációt, amelyben az attributumok nem atomiak, pl. mert ismétlõdõ csoport van az attributumok között. 1NF alakú, ha csak egyszerû tulajdonság-értékek szerepelnek benne. 5.7.1. Példa 0NF alakra egyetem rektor kar dékán tanszék vezetõ
(E,R(K,D,(T,V)* )* ) A * jelû csoportok ismétlõdnek. 5.8. A 2. normál forma.
Definíció: Az R reláció ti attribútuma elsõdleges, ha ti eleme a reláció valamely K kulcsának. egyébként a ti másodlagos. Definíció: Egy relációs séma 2NF alakú, ha benne minden másodlagos attribútum a reláció valamely kulcsától teljesen függ, azaz nincs benne függõség részkulcstól. 5.9. A 3. normál forma
Definíció: Egy R relációs séma 3NF alakú, ha minden A→X nemtriviális függõség esetén vagy X szuperkulcsa R-nek, vagy A elsõdleges attribútum. Más megfogalmazásban: Egy reláció 3NF alakú, ha benne egyetlen másodlagos attribútum sem függ tranzitíven valamelyik kulcstól. 5.10. A Boyce-Codd normál forma (BCNF).
21
Egy reláció BCNF alakú, ha benne nemtriviális függõség szuperkulcstól van. Pl.: Eszerint a (város,utca,irányítószám) reláció 3NF de nem BCNF.
22
csak
5.11. Sémadekompozíciók
Tétel: Minden normalizált felbontása, amely BCNF.
relációnak
Tétel: Minden normalizált relációnak függõségõrzõ felbontása, amely 3NF.
létezik
létezik
veszteségmentes
veszteségmentes
és
Definíció: Egy függõséghalmaz lefedése (F+) jelentse azt a függõséghalmazt, amely az F függõséghalmazból úgy származtatható, hogy belevesszük az Armstrong axiómákkal F-bõl levezethetõ valamennyi függõséget. Definíció: Legyen F a függõségek halmaza! G az F-nek minimális lefedése, ha F+=G+ és G minimális. Egy függõséghalmaz akkor minimális, ha − csak X → A alakú függõségek vannak, ahol A elemi attributum, − nem hagyható el belõle függõség. − a függõségek bal oldalából nem hagyható el attributum. 5.11.1. A 3NF alakra hozás veszteségmentes, függõségõrzõ dekompozícióval. − − −
Képezzük a függõségrendszer minimális lefedését, legyen ez G. Minden X →Y ∈G függõséghez készítsünk egy Ri(XY) részsémát; egy K kulcs attributumaiból is képezzünk egy további részsémát.
5.11.2. A BCNF alakra hozás veszteségmentes dekompozícióval.
1. Megszerkesztjük az univerzális relációt. 2. Meghatározzuk a funkcionális függõségeket. 3. Eldöntjük, hogy a reláció BCNF-e, ha igen, kész, ha nem, akkor a relációt két relációra bontjuk szét veszteségmentes dekompozícióval 4. Megismételjük a 2. és 3. lépéseket az új relációkra. 5.12. Többértékû függõség.
Definíció: többértékû függõség X →> Y függõséget kielégíti egy R reláció, ha ∀ t1,t2 ∈ R -hez ∃ t3 ∈ R, hogy • t1[X] = t2[X], • t3[XY] = t1[XY] és, • t3[R-XY] = t2[R-XY].
5.11.1. Következménye: ∃ t4 ∈R, hogy
t4[XY] = t2[XY] és, t4[R-XY] = t1 [R-XY]. 5.11.2. Illusztráció.
t1: t2: t3:
X a a a
Y b d b
R-XY c e e 23
t4:
a
d
c
5.11.3. Tételek.
X → Y ⇒X →> Y Olvasata: "Ha X meghatározza Y-t, akkor X multideterminálja Y-t." X →> Y⇒ X→> R - XY Egy reláció 4NF alakú, ha X→> Y esetén X szuperkulcs. Magyarázat: X→> Y és X →> R - XY úgy szokott keletkezni, hogy XY
X(R - XY).
Példa: ÓRAREND(TANTÁRGY, FÉLÉV, ELÕADÓ, HALLGATÓ, TEREM, NAP), ahol minden tárgy minden félévét csak egy elõadó adja elõ, mindig ugyanazon a napon, ugyanabban a teremben, és mindenki csak egy helyre iratkozhat be. Ekkor: TÁRGY, FÉLÉV → ELÕADÓ HALLGATÓ TÁRGY → FÉLÉV TÁRGY, FÉLÉV→NAP, TEREM TÁRGY, FÉLÉV →> NAP, TEREM TÁRGY, FÉLÉV →> HALLGATÓ ezért az ÓRAREND reláció nem 4NF-ben adott, mert (TÁRGY, FÉLÉV) nem kulcs. Helyette bontsuk fel 3 részre a relációt: HALLGATÓI_ÓRAREND (TÁRGY, FÉLÉV, HALLGATÓ) ELÕADÓI_ÓRAREND (TÁRGY, FÉLÉV, ELÕADÓ) TEREM_ÓRAREND (TÁRGY, FÉLÉV, NAP, TEREM) már 4NF alak.
24
PÉLDÁK
1. Legyen adott az alábbi reláció: NYELV(TANSZÁM, TANNÉV, OFÕNÖK, OFTEL, NYELV, FÉLÉVKÓD, OSZTÁLYZAT) Alakítsuk át BCNF alakra! Tekintettel arra, hogy a fenti reláció univerzális, ezért a feladat megoldását a második lépéssel kezdhetjük, azaz meghatározzuk a függõségeket. 2. lépés TANSZÁM → TANNÉV TANSZÁM →OFÕNÖK OFÕNÖK→OFTEL OFTEL→OFÕNÖK TANSZÁM →OFTEL TANSZÁM, NYELV,FÉLÉVKÓD →OSZTÁLYZAT 3. lépés A relációnak három nem kulcsjelölt determinánsa van TANSZÁM OFÕNÖK OFTEL Vegyük külön relációba a tranzitív TANSZÁM →OFÕNÖK→OFTEL függõség utolsó két tagját, legyen ez az R2 reláció, míg a maradk rész legyen az R1, ekkor R1(TANSZÁM, TANNÉV, OFÕNÖK, NYELV, FÉLÉVKÓD, OSZTÁLYZAT) R2(OFÕNÖK, OFTEL) lesz. Az R2 BCNF alakú, így csak az R1-et vizsgáljuk tovább. Ennek függõségei: TANSZÁM → TANNÉV TANSZÁM →OFÕNÖK TANSZÁM, NYELV,FÉLÉVKÓD → OSZTÁLYZAT. Ez még mindig nem BCNF alakú, mert a Tanszám determináns, de nem kulcsjelölt. Az elsõ két függõséget az alábbi formában is felírhatjuk: TANSZÁM → TANNÉV,OFÕNÖK. Legyen ez az R4 reláció, míg a maradék az R3. Ekkor R3(TANSZÁM, NYELV,FÉLÉVKÓD,OSZTÁLYZAT) R4(TANSZÁM, TANNÉV,OFÕNÖK,) Itt már R3 és R4 is BCNF alak, tehát készen vagyunk, a megoldás az R2,R3 és az R4 relációk együttese lesz. 2. Legyen adott az alábbi reláció: TANSZÉKKÓD (TSZKÓD) TANSZÉKNÉV (TSZNÉV) TANSZÉKVEZETÕ (TSZVEZ) SZEMÉLYSZÁM (SZSZÁM) SZEMÉLYNÉV (SZNÉV) FOKOZAT (FOKKÓD) TÉMASZÁM (TÉSZÁM) TÉMANÉV (TÉNÉV) ELFOGLALTSÁG % (ELFOGL) SZEMÉLYSZÁM SZEMÉLYNÉV 25
FOKOZAT TÉMASZÁM TÉMANÉV ELFOGLALTSÁG % ... TANSZÉKNÉV TANSZÉKVEZETÕ SZEMÉLYSZÁM SZEMÉLYNÉV FOKOZAT TÉMASZÁM TÉMANÉV ELFOGLALTSÁG % SZEMÉLYSZÁM SZEMÉLYNÉV FOKOZAT TÉMASZÁM TÉMANÉV ELFOGLALTSÁG % ... Hozzuk 3NF alakra! Elsõ lépésként az ismétlõdõ mezõket szüntetjük meg, azaz felírjuk az univerzális relációt. R1(TSZKÓD, TSZNÉV, TSZVEZ, SZSZÁM, SZNÉV, FOKKÓD, TÉSZÁM, TÉNÉV, ELFOGL) Ha egy személy több témán is dolgozhat, akkor az R1 reláció kulcsa SZSZÁM, TÉSZÁM lehet, ez ugyanis egyértelmûen meghatároz egy sort a relációban. A reláció függõségei ekkor SZSZÁM,TÉSZÁM → ELFOGL TÉSZÁM →TÉNÉV SZSZÁM → TSZKÓD → TSZNÉV, TSZVEZ SZSZÁM → SZNÉV, FOKKÓD A második normál alakban az oszlopok csak a teljes elsõdleges kulcstól függenek, tehát a relációk: R2(SZSZÁM,TÉSZÁM , ELFOGL) R3(TÉSZÁM ,TÉNÉV) R4(SZSZÁM, TSZKÓD, TSZNÉV, TSZVEZ, SZNÉV, FOKKÓD) Harmadik normál alakot akkor kapunk, ha az R4-bõl kivesszük a tranzitív függõséget, azaz SZSZÁM →TSZKÓD →TSZNÉV,TSZVEZ figyelembevételével R5(SZSZÁM ,TSZKÓD, SZNÉV,FOKKÓD) R6(TSZKÓD ,TSZNÉV,TSZVEZ ). A kapott 3NF alakú reláció az R2,R3,R5,R6 lesz.
26
Budapesti Mûszaki Egyetem Villamosmérnöki és Informatikai Kar
ADATBÁZISOK Oktatási segédanyag az Informatika c. tantárgyhoz
Csak egyetemi belsõ használatra!
szerkesztette: dr. Iváncsy Szabolcs
szerzõk: Kiss Istvén dr. Iváncsy Szabolcs
27
Tartalom: I.rész 1. Az SQL nyelv 1.1. Bevezetés 1.2 A példákban szereplô táblák 2. A nyelv definíciója 3. Lekérdezések 3.1 Vetítés (projection) 3.2 Kizárás (restriction) 3.3 Összekapcsolás (join) 3.4 Oszlopfüggvények 3.5 Egymásba ágyazott lekérdezések 3.6 Csoportosítás 3.7 Rendezés 3.8 Egyéb, nem részletezett mûveletek 4. Táblák módosítása 4.1 Sorok mûveletei 4.2 Táblák létrehozása 4.3 Nézet létrehozása 4.4 Index létrehozása 4.5 Törlések 4.6 Tábla definíciók módosítása 5. Adatelérések szabályozása 5.1 Jogosultságok definiálása 5.2 Tranzakciók 5.3 Párhuzamos hozzáférések szabályozása 6. Bôvítések 6.1 Konzisztencia feltételek 7. PL/SQL
II. rész 8. Az adatbázis lekérdezések az ORACLE adatbáziskezelõ alapján 8.1 Oszlopok kiválasztása a táblából 8.2 Sorok kiválasztása a táblából 8.3 A táblák összekapcsolása 8.4 Számok kezelése 8.4.1 Az aritmetikai függvények (oszlopfüggvények) 8.4.2 A csoportfüggvények 8.5 Szövegek kezelése 8.5.1 Szöveges függvények 8.6 Dátumok kezelése 8.6.1 Dátum függvények 8.7 A NULL értékek kezelése 8.8 Alkérdések ( Subqueries ) 8.9 Fa struktúrába rendezett táblák 9. Táblák létrehozása és módosítása 9.1 Táblák létrehozása 9.2 Sorok beszúrása, módosítása és törlése 9.3 Táblák módosítása 9.4 Nézet-táblák (VIEW) készítése és használata 10. Függelék 10.1 Minta táblák 10.2 A számok formátuma
28
10.3 Dátum formátumok
29
I. rész 1. Az SQL nyelv 1.1. Bevezetés Rövid történet - 1974-75-ben kezdték a kifejlesztését az IBM-nél, az "eredeti" neve SEQUEL (Structured English QUEry Language); - 1979-tôl több cég (pl. IBM, ORACLE Corp.) kereskedelmi forgalomban kapható termékeiben; - 1987-tôl ANSI szabvány. Jelentôsége - szabvány, amelyet jelenleg csaknem minden relációs adatbáziskezelô alkalmaz (kisebb-nagyobb módosításokkal); - tömör, felhasználó közeli nyelv, alkalmas hálózatokon adatbáziskezelô szerver és kliensek közötti kommunikációra; - nem procedurális programozási nyelv (legalábbis a lekérdezéseknél). 1.2. A példákban szereplô táblák A leírás az ORACLE adatbáziskezelô SQL dialektusát ismerteti, ez többékevésbé megfelel az egyéb termékekben található nyelv variációknak. A nyelv termék- illetve hardverspecifikus elemeit nem, vagy csak futólag ismertetjük. Az utasítások ismertetésénél a következô táblákat használjuk. EMP tábla az alkalmazottak adatainak tárolására: EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
7369 7499 7521 7566 7654 7698 7782 7788 7839 7844 7876 7900 7902 7934
SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER
CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK
7902 7698 7698 7839 7698 7839 7839 7566
17-DEC-80 20-FEB-81 22-FEB-81 02-APR-81 28-SEP-81 01-MAY-81 09-JUN-81 21-JUL-85 17-NOV-81 08-SEP-81 24-AUG-85 03-DEC-81 03-DEC-81 23-JAN-82
800.00 1,600.00 1,250.00 2,975.00 1,250.00 2,850.00 2,450.00 3,000.00 5,000.00 1,500.00 1,100.00 950.00 3,000.00 1,300.00
7698 7788 7698 7566 7782
DEPT tábla a cég részlegeinek adataival:
DEPTNO DNAME 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS
LOC NEW YORK DALLAS CHICAGO BOSTON
30
COMM
300.00 500.00 1,400.00
DEPTN O 20 30 30 20 30 30 10 20 10 30 20 30 20 10
2. A nyelv definíciója A nyelv utasításait a következô csoportokra oszthatjuk: - adatleíró (DDS - Data Definition Statement) - adatmódosító (DMS - Data Manipulation Statements) - lekérdezô (Queries) - adatelérést vezérlô (DCS - Data Control Statements) A nyelvben - a szöveg literálok kivételével - a kis- és nagybetûket nem különböztetjük meg. A megadott példáknál a könnyebb érthetôség miatt a nyelv alapszavait csupa nagy betûvel, míg a programozó saját neveit kis betûkkel írjuk. A parancsok több sorba is átnyúlhatnak, a sorokra tördelésnek nincs szemantikai jelentôsége. Az SQL parancsokat a pontosvesszô zárja le.
3. Lekérdezések A lekérdezések általános szintaxisa a következô: SELECT <jellemzôk> FROM
[WHERE ] [] []; A lekérdezés mûvelete eredményül egy újabb táblát állít elô - persze lehet, hogy az eredmény táblának csak 1 oszlopa és egy sora lesz. Az eredmény tábla a lekérdezés után megjelenik vagy a tábla felhasználható pl. az adatmódosító utasításokban. A <jellemzôk> definiálják az eredmény tábla oszlopait, adják meg a lekérdezésben résztvevô táblák nevét, a segítségével "válogathatunk" az eredmény sorai között. A az eredmény tábla sorait rendezi egymás mellé, illetve a a megjelenô sorok sorrendjét határozza meg. Nézzük meg, hogy a lekérdezés mûveletével hogyan lehet megvalósítani a relációs algebra alapmûveleteit. 3.1. Vetítés (projection) A vetítés mûvelete egy táblából adott oszlopokat válogat ki. A <jellemzôk> között kell felsorolni a kivánt oszlopokat. Például az alkalmazottak neve és fizetése: SELECT ename, sal FROM emp; minden oszlop kiválasztása: SELECT * FROM emp; A <jellemzôk> közé nem csak a FROM mögött megadott tábla oszlopainak nevét lehet megadni, hanem használhatjuk az SQL beépített mûveleteit, pl. egyszerû aritmetikai kifejezéseket új érték elôállítására. A dolgozók egész éves fizetése: SELECT ename, 12 * sal FROM emp; Amennyiben a dolgozó által kézhez kapott teljes összeget - fizetést és prémiumot - együtt akarjuk megkapni, hasonlóan járhatunk el. Az jelent csak problémát, hogy a comm érték nincs mindenhol kitöltve, az üres mezôket nem tudjuk hozzáadni a fizetéshez (az üres nem 0)! Ilyenkor használhatjuk az NVL(oszlop, érték) függvényt, amely az üres (NULL) mezô helyett a megadott értéket adja vissza.
31
A dolgozó által felvett pénz: SELECT ename, sal + NVL(comm, 0) FROM emp; A kiválasztott oszlopokat tartalmazó táblákban lehetnek azonos sorok, ami ellentmond a relációs táblák egyik alapvetô követelményének. Ennek ellenére a SELECT utasítás nem szûri ki automatikusan az azonos sorokat, mert ez túlságosan idôigényes mûvelet. A programozónak kell tudnia, hogy az elôállított táblában lehetnek-e (zavarnak-e) ilyen sorok. Ha kell, a következôképpen szûrhetjük ki ezeket: Az összes különbözô munka megnevezése: SELECT DISTINCT job FROM emp; 3.2. Kizárás (restriction) A kizárás mûveleténél a tábla sorai közül válogatunk. A igaz értékeinek megfelelô sorok kerülnek be az eredmény táblába. A 2000 dollárnál több fizetésû dolgozók: SELECT ename, sal FROM emp WHERE sal > 2000; Vizsgáljuk meg a logikai kifejezések szerkezetét. A kifejezések elemi összetevôi: - literálok különbözô típúsú értékekre: számok, szöveg, dátum; - oszlopok nevei; - a fenti elemekbôl elemi adatmûveletekkel képzett kifejezések számoknál: aritmetikai mûveletek, aritmetikai függvények; szövegeknél: SUBSTR(), INSTR(), UPPER(), LOWER(), SOUNDEX(), ...; dátumoknál: +, -, konverziók; - halmazok: pl.: (10,20,30); - zárójelek között egy teljes SELECT utasítás (ld. késôbb). A fenti mûveletekkel képzett adatokból logikai értéket a következô mûveletekkel állíthatunk elô: - relációk: <, <=, =, !=, >=, >; - intervallumba tartozás: BETWEEN .. AND ..; - NULL érték vizsgálat: IS NULL, IS NOT NULL; - halmaz eleme: IN ; - szöveg vizsgálat: mintával összevetés .. LIKE <minta>, ahol % a tetszôleges karaktersorozat, _ a tetszôleges karakter jelzése; Végül a logikai értékeket a zárójelezéssel illetve az AND, OR és NOT mûveletekkel lehet tovább kombinálni. Példák: A 82 .. 89-es években felvett dolgozók: SELECT ename, hiredate FROM emp WHERE hiredate BETWEEN '01-JAN-82' AND '31-DEC-89'; A 2000 dollárnál kevesebbet keresô és prémiumot nem kapó dolgozók: SELECT ename, sal FROM emp WHERE sal < 2000 AND comm IS NULL; 3.3. Összekapcsolás (join)
32
Az természetes összekapcsolás mûveleténél 2 vagy több tábla soraiból hozunk össze egy-egy új rekordot akkor, ha a két sor egy-egy mezôjének értéke megegyezik. A SELECT kifejezésben a -ban kell megadni az érintett táblák neveit, a WHERE mögötti logikai kifejezés definiálja azokat az oszlopokat, amely értékei szerint történik meg az összekapcsolás. Az egyes osztályok neve, székhelye és a hozzájuk tartozó dolgozók: SELECT dept.dname, dept.loc, emp.ename FROM dept, emp WHERE dept.deptno = emp.deptno; Látható, hogy mivel mindkét felhasznált táblában azonos az összekapcsolást megvalósító oszlop neve, a WHERE-t követô logikai kifejezésben az oszlop neve mellé meg kell adni a tábla nevét is. Hasonló helyzet elôfordulhat a SELECT-et követô <jellemzôk> között is. Ha megvizsgáljuk a fenti példában kapott eredmény, láthatjuk, hogy a 40-es osztály nem szerepel a listában. Ennek az az oka, hogy az osztálynak nincs egyetlen dolgozója sem, tehát az egyesítésnél nem találtunk az emp táblában egyetlen olyan sort sem, amelyet ehhez az osztályhozkapcsolhattunk volna. Ez lehet kívánatos eredmény, azonban az SQL-ben lehetôség van arra is, hogy ezeket a sorokat is egyesítsük, azaz az egyesítésben az emp táblához hozzáképzeljünk egy üres sort is. Ezt külsô egyesítésnek hívjuk. A módosított példa a következôképpen néz ki: SELECT dept.dname, dept.loc, emp.ename FROM dept, emp WHERE dept.deptno = emp.deptno (+); A (+) jel jelzi azt a táblát, amelyikhez az egyesítés elôtt az üres mezôket tartozó sort hozzá kell venni. Az egyesítésnél lehet ugyanarra a táblára többször is hivatkozni. Pl. Azon dolgozók, akik többet keresnek a fônöküknél: SELECT e.ename, e.sal, m.ename, m.sal FROM emp e, emp m WHERE e.mgr = m.empno AND e.sal > m.sal; A fenti példában ugyanazt a táblát kétszer is használjuk, a két tábla oszlopainak megkülönböztetésére a táblákat a FROM részben lokális névvel látjuk el. Lokális neveket természetesen különbözô táblák esetén is használhatunk. Látható, hogy az egyesítés mellett egyidejûleg más logikai kifejezéseket is használhatunk. A fizetések fenti vizsgálatát felfoghatjuk úgy is, mint a természetes egyesítés mûveletének általánosított esetét, ahol nem csak az egyenlôség mûvelete használható (ami megengedett), valamint úgy is, hogy a már egyesített táblából zárjuk ki a fizetésekre szabott követelményeknek meg nem felelô sorokat. Az SQL programozónak - általában - fogalma sincs, hogyan hajtódik végre a fenti kifejezés. Itt fedezhetô fel a nyelv nem algoritmikus jellege. 3.4. Oszlopfüggvények A lekérdezés eredményeként elôálló táblák egyes oszlopaiban lévô értékeken végrehajthatunk szokásos nyelven ciklussal kifejezhetô mûveleteket, amelyek egyetlen értéket állítanak elô. Ilyenek: AVG() átlag SUM() összeg COUNT() darabszám MAX() maximális érték MIN() minimális érték Az üzletkötôk átlagfizetése: SELECT AVG(sal) FROM emp WHERE job = 'SALESMAN';
33
Hány dolgozó van: SELECT COUNT(*) FROM emp; Hány különbözô beosztás van: SELECT COUNT(DISTINCT job) FROM emp; Åtlagos prémium: SELECT AVG(NVL(comm, 0)) FROM emp; Figyelem: az utolsó példa az NVL függvényt használva az összes dolgozóra átlagolja a kifizetett prémiumot, míg NVL nélkül csak azokra átlagolná, akik kaptak egyáltalán prémiumot. (Az oszlopfüggvények kiszámításánál a NULL értékû rekordok kimaradnak.) Mivel az oszlopfüggvény eredménye egyetlen értéket állít elô, az oszlopfüggvény mellé vagy más oszlopfüggvényeket írhatunk, vagy olyan értéket írhatunk, amelyik az összes kiválasztott sorban azonos. Például írhatnánk: SELECT job, AVG(sal) FROM emp WHERE job = 'SALESMAN'; SELECT COUNT(*), AVG(sal) FROM emp; de hibás SELECT COUNT(*), ename FROM emp; 3.5. Egymásba ágyazott lekérdezések A WHERE mögött álló logikai kifejezésben állhat egy teljes SELECT utasítás is. Például A nem New York-ban dolgozók listája: SELECT ename FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc != 'NEW YORK') Azaz elôször kiválasztjuk azon osztályok azonosítóját, amelyek nem New York-ban vannak, majd azt vizsgáljuk, hogy az ezekbôl képzett halmazban található-e az adott dolgozó osztályának azonosítója. Mellesleg ugyanezt a listát megkaphatnánk az egyesítés mûveletével is: SELECT ename FROM dept, emp WHERE dept.deptno = emp.deptno AND dept.loc != 'NEW YORK'; A beágyazott lekérdezés vagy egyetlen értéket - azért mert egyetlen megfelelô sor egyetlen oszlopát választottuk ki illetve oszlopfüggvényt használtunk -, vagy több értéket - több sort - állít elô. Az elôbbi esetben a SELECT értékét az elemi értékekkel azonos módon használhatjuk. Több érték egy halmazt jelent, tehát a halmazmûveleteket használhatjuk. A korábban megismert IN() - eleme - mûvelet mellett használható az ANY() illetve az ALL() mûvelet, ahol a kivánt reláció a halmaz legalább egy, illetve valamennyi értékére igaz. Legmagasabb fizetésû dolgozók (lehet, hogy több van!): SELECT ename, sal FROM emp WHERE sal >= ANY (SELECT sal FROM emp);
34
illetve ugyanez a példa oszlopfüggvény felhasználásával: SELECT ename, sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp); 3.6. Csoportosítás Az oszlopfüggvények a teljes kiválasztott táblára - minden sorra - lefutnak. Gyakran célszerû lenne a kiválasztott sorokat valamilyen szempont szerint csoportosítani és az oszlopfüggvényeket az egész tábla helyett ezekre a csoportokra alkalmazni. Foglalkozásonkénti átlagfizetés: SELECT job, AVG(SAL) FROM emp GROUP BY job; A fenti parancs az emp tábla sorait a job oszlop azonos értékei alapján csoportosítja. Természetesen az oszlopfügvények korábbi használatához hasonlóan a SELECT <jellemzô>-k között csak a csoportosítás alapját képezô oszlop neve illetve a csoportokra alkalmazott oszlopfüggvények szerepelhetnek. A csoportosítás után az eredménybôl bizonyos csoportok kihagyhatók. Foglalkozásonkénti átlagfizetés a 1000 és 3000 dollár közötti tartományban: SELECT job, AVG(emp) FROM emp GROUP BY job HAVING AVG(sal) BETWEEN 1000 AND 3000; A HAVING mögötti logikai kifejezésben természetesen csak egy-egy csoport közös jellemzôire vonatkozó értékek - a csoportosítás alapját képezô oszlop értéke, vagy oszlopfüggvények eredménye szerepelhet. Természetesen a csoportosítás elôtt azért a WHERE feltételek használhatók. Célszerû - gyorsabb - WHERE feltételeket használni mindenhol, ahol csak lehet, a HAVING szerkezetet csak akkor alkalmazni, amikor a teljes csoporttól függô értékeket akarjuk vizsgálni. 3.7. Rendezés Az eddig tárgyalt lekérdezések eredményében a sorok "véletlenszerû" - a programozó által nem megadható - sorrendben szerepeltek. A sorrendet lehet az ORDER BY által megadott rendezéssel szabályozni. A rendezés több oszlop értékei szerint is történhet, ilyenkor az elôször megadott oszlop szerint rendezünk, majd az itt álló azonos értékek esetében használjuk a következônek megadott oszlop(ok) értékét. Minden egyes oszlop esetében külön meg lehet adni a rendezés "irányát", amely alap esetben emelkedô, de a DESC módosítóval csökkenô rendezés írható elô. A 30-as osztály dolgozói (kicsit rendezve): SELECT * FROM emp WHERE deptno = 30 ORDER BY job, sal DESC; Osztályok szerinti átlagfizetés növekvô sorrendben: SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ORDER BY AVG(sal); 3.8. Egyéb, nem részletezett mûveletek Az egyes lekérdezések által elôállított táblák halmazként is felfoghatók, az SQL nyelv ezen táblák kombinálására tartalmaz szokásos halmazmûveleteket is. UNION unió
35
INTERSECT metszet MINUS különbség A mûveleteket két SELECT utasítás közé kell írni. Relációs táblák segítségével le tudunk írni hierarchikus kapcsolatokat a különbözô sorok között. Például az emp táblában az empno és az mgr oszlopok értékei alapján meg lehet konstruálni a vállalati hierarchiát. Erre a CONNECT BY PRIOR szerkezet szolgál. Például a SELECT ename, empno, job, deptno, mgr FROM emp CONNECT BY PRIOR empno = mgr START WITH ename = 'KING' ORDER BY deptno; utasítás kiírja a dolgozók 'fáját', az elnöktôl kezdôdôen. Nem tartoznak szorosan az SQL nyelvhez, de a legtöbb rendszer tartalmaz utasí'tásokat, amelyekkel a lekérdezések által elô'allított táblázatok megjelenését - pl. az oszlopok neveit, szélességét, adatformátumát, illesztését, tördelését - definiálhatjuk.
4. Táblák módosítása A következô mûveletek az adatbázis táblák módosítására szolgálnak. új sorokat egy meglévô táblába az INSERT INTO [(, ...)] VALUES (, ...) illetve az INSERT INTO [(, ...)] <SELECT ...>; utasításokkal lehetséges. Míg az elsô szerkezet egyetlen sort, addig a második a lekérdezés által elôállított összes sort beilleszti. (Figyelem: a táblákban az egyes rekordok sorrendje tetszôleges, így a beillesztés sem feltétlenül a tábla "végére" történik.) Amennyiben nem adtuk meg az oszlopok nevét, akkor a - tábla deklarálásánál megadott sorrenben - minden mezônek értéket kell adni - esetleg NULL-t -, ha viszont megadtuk egyes oszlopok neveit, akkor csak azoknak adnk értéket, mégpedig a felsorolásuk sorrendjében, a többi mezô NULL értékû lesz. Az adatbáziskezelô ellenôrzi, hogy az egyes mezôkbe ne kerülhessen a tábla definiziójával ellentétben NULL érték. 4.1. Sorok mûveletei Sorokat törölni a DELETE FROM [WHERE ]; paranccsal lehet. Ha a WHERE hiányzik, a tábla valamennyi sorát, egyébként a logikai kifejezés által kiválasztott sorokat töröljük. Sorokban mezôket az UPDATE SET = , ... [WHERE ]; paranccsal lehet. Ha a WHERE hiányzik, a parancs a tábla valamennyi sorában módosít, egyébként csak a kiválasztott sorokban. Például: az üzletkötôknek 20% fizetésemelés: UPDATE emp SET sal = 1.2 * sal
36
WHERE job = 'SALESMAN'; A parancsban a kifejezésben a szokásos operátorok és függvényeken felül egyes implementációk akár lekérdezést is megengednek. 4.2. Táblák létrehozása ùj táblákat a CREATE TABLE ( [NOT NULL], ...); paranccsal lehet létrehozni. A lehetséges adattípusok implementációkként változhatnak, általában a következô adattípusok megtalálhatók: CHAR(n) max n. karakter hosszú szöveg; LONG(n) mint CHAR, de hosszára általában nincs (nagyon nagy) felsô korlát; NUMBER(w) az elôjellel együtt max w karakter széles egész szám; NUMBER(w,d) w a teljes szám, d a törtrész szélessége; DATE dátum (és általában idôpont). Ha valamelyik oszlop definíciója tartalmazza a NOT NULL módosítót, a megfelelô mezôben mindig érték kell, hogy szerepeljen. A felhasznált táblák definíciója a következô lehet: CREATE TABLE emp (empno NUMBER(4) NOT NULL, ename CHAR(10), job CHAR(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2) comm NUMBER(7,2) deptno NUMBER(2) NOT NULL);
CREATE TABLE dept (deptno NUMBER(2) dname CHAR(14), loc CHAR(13));
NOT NULL,
4.3. Nézet létrehozása A nézetek olyan virtuális táblák, amelyek a fizikai táblákat felhasználva a tárolt adatok más és más logikai modelljét, csoportosítását tükrözik. Nézetet a CREATE VIEW [(, ...)] AS ; A lekérdezésre az egyedüli megkötés, hogy rendezést nem tartalmazhat. Amennyiben nem adunk meg oszlopneveket, a nézet oszlopai a SELECT után felsorolt oszlopok neveivel azonosak. Meg kell viszont adni az oszlopneveket, ha a SELECT számított értéket is elôállít. Például: CREATE VIEW dept_sal (deptno, avg_salary) AS SELECT deptno, AVG(sal) FROM emp GROUP BY deptno; A nézetek a lekérdezésekben a táblákkal megegyezô módon használhatók. Jelentôségük, hogy az adatok más modelljét fejezik ki, felhasználhatók a tárolt információ részeinek elrejtésére, pl. különbözô felhasználók más nézeteken keresztül szemlélhetik az adatokat. A nézet általában csak olvasható, az adatmódosító mûveletekben csak akkor szerepelhet, hagy egyetlen táblából keletkezett és nem tartalmaz számított értékeket. 4.4. Index létrehozása Az indexek a táblákban keresést gyorsítják meg. Létrehozásuk: CREATE [UNIQUE] INDEX ON (, ...);
37
Az indexeket az adatbáziskezelô a táblák minden módosításánál felfrissíti. Amennyiben valamelyik indexet a UNIQUE kulcsszóval definiáltuk, a rendszer biztosítja, hogy az adott oszlopban minden mezô egyedi értéket tartalmaz. Lehetséges több oszlopot egybefogó, kombinált indexek létrehozására. A lekérdezésekben nem jelenik meg, hogy a táblához tartozik-e index, az indexek a létrehozásuk után a felhasználó számára láthatatlanok, csak a lekérdezéseket gyorsítják. Indexeket azokra az oszlopokra érdemes definiálni, amelyek gyakran szerepelnek keresésekben. Szerencsés esetben az adattáblában nem kell egyáltalán keresni, a kívánt rekord az index alapján közvetlen kiválasztható. Pl. a SELECT * FROM emp WHERE ename = 'JONES'; az emp táblában keresés nélkül kiválaszthatja JONES rekordját, ha az ename oszlopra definiáltunk indexet. 4.5. Törlések A fenti adatbázis objektumokat a DROP paranccsal lehet törölni. DROP [TABLE | VIEW | INDEX] ; 4.6. Tábla definíciók módosítása
Már létezô táblákat módosítani az ALTER TABLE [ADD | MODIFY] ; paranccsal lehet, ahol ADD egy új, NULL értékû oszlopot illeszt a táblához, míg MODIFY paranccsal egy létezô oszlop szélességét növelhejük.
5. Adatelérések szabályozása Az adatbáziskezelô rendszereket tipikusan több felhasználó használja, ezzel kapcsolatban újabb problémák merülnek fel. 5.1. Jogosultságok definiálása A egyes felhasználók részint az adatbáziskezelô rendszerrel, részint az egyes objektumaival különbözô mûveleteket végezhetnek. Ezeknek a megadására szolgálnak a GRANT utasítások. A GRANT [DBA | CONNECT | RESOURCES] TO , ... IDENTIFIED BY <jelszó>, ...; paracs az egyes felhasználók az adatabázishoz hozzáférésének jogát szabályozzák. A DBA jogosultság az adatbázis adminisztrátorokat (DataBase Administrator) definiálja, akiknek korlátlan jogai vannak az összes adatbázis objektum felett, nem csak létrehozhatja, módosíthatja illetve törölheti, de befolyásolhatja az objektumok tárolásával, hozzáférésével kapcsolatos paramétéreket is. A RESOURCE jogosultsággal rendelkezô felhasználók létrehozhatnak, módosíthatnak ill. törölhetnek új objektumokat, míg a CONNECT jogosultság csak az adatbáziskezelôbe belépésre jogosít. Az egyes objektumokhoz - táblák ill. nézetek - a hozzáférést a GRANT <jogosultság>, ... ON TO [WITH GRANT OPTION]; parancs határozza meg. A <jogosultág> az objektumon végezhetô mûveleteket adja meg, lehetséges értékei: ALL SELECT INSERT UPDATE , ... 38
DELETE ALTER INDEX Az utolsó két mûvelet nézetekre nem alkalmazható. A felhasználó neve helyett PUBLIC is megadható, amely bármelyik felhasználóra vonatkozik. A WITH GRANT OPTION-nal megkapott jogosultságokat a felhasználók tovább is adhatják. 5.2. Tranzakciók Az adatbázisok módosítása általában nem történhet meg egyetlen lépésben, hiszen legtöbbször egy módosítás során több táblában tárolt információn is változtatni akarunk, illetve egyszerre több rekordban akarunk módosítani, több rekordot akarunk beilleszteni. Elôfordulhat, hogy módosítás közben meggondoljuk magunkat, vagy ami még súlyosabb következményekkel jár, hogy az adatbáziskezelô leáll. Ilyenkor a tárolt adatok inkonzisztens állapotba kerülhetnének, hiszen egyes módosításokat már elvégeztünk, ehhez szorosan hozzátartozó másokat viszont még nem. A tranzakció az adatbázis módosításainak olyan sorozata, amelyet vagy teljes egészében kell végrehajtani, vagy egyetlen lépését sem. Az adatbáziskezelôk biztosítják, hogy mindig vissza lehessen térni az utolsó teljes egészében végrehajtott tranzakció utáni állapothoz. Egy folyamatban lévô tranzakciót vagy a COMMIT utasítással zárhatjuk le, amely a korábbi COMMIT óta végrehajtott összes módosítást véglegesíti, vagy a ROLLBACK utasítással törölhetjük a hatásukat, visszatérve a megelôzô COMMIT kiadásakor érvényes állapotba. Beállítható, hogy bizonyos mûveletek automatikusan COMMIT mûveletet hajtsanak végre. SET AUTOCOMMIT [IMM | OFF]; Az IMM állapotban az ALTER, CREATE, DROP, GRANT és EXIT utasítások sikeres végrehajtása COMMIT-ot is jelent. A rendszer hardver hiba utáni újraindulásakor illetve hibás INSERT, UPDATE vagy DELETE parancs hatására automatikusan ROLLBACK-et hajt végre. Érdemes hát biztonságos helyeken COMMIT parancsot kiadni, nehogy egy hibásan kiadott parancs visszavonja a korábbi módosításokat. 5.3. Párhuzamos hozzáférések szabályozása Az adatbázist több felhasználó egyidejûleg is használhatja. Ilyenkor az egyes táblákhoz a párhuzamos hozzáférést külön-külön lehet szbályozni. LOCK TABLE , ... IN [SHARE | SHARED UPDATE| EXCLUSIVE] MODE [NOWAIT]; A LOCK parancsal egy felhasználó megadhatja, hogy az egyes táblákhoz más felhasználóknak milyen egyidejû hozzáférést engedélyez. Az utasítás végrehajtásánál a rendszer ellenôrzi, hogy a LOCK utasításban igényelt felhasználási mód kompatibilis-e a táblára érvényben lévô kizárással. Amennyiben megfelelô, az utasítás visszzatér és egyéb utasításokat lehet kiadni. Ha az igényelt kizárás nem engedélyezett, az utasítás várakozik amíg az érvényes kizárást megszüntetik, ha csak a parancs nem tartalmazza a NOWAIT módosítót. Ebben az esetben a LOCK utasítás mindig azonnal visszatér, de visszaadhat hibajelzést is. A táblához hozzáférést az elsô sikeres LOCK utasítás definiálja. A SHARE módban megnyitott táblákat mások olvashatják, a SHARE UPDATE módban mások módosíthatják is, ilyenkor automatikusan kölcsönös kizárás teljesül egy-egy sorhoz hozzáférésnél. Az EXCLUSIVE mód kizárólagos hozzáférést biztosít.
6. Bôvítések 39
6.1. Konzisztencia feltételek A táblák definíciójánál eddig csak azt adhattuk meg, hogy milyen adattípusba tartozó értékeket lehet az egyes oszlopokban használni, illetve mely oszlopokban kell feltétlenül értéknek szerepelnie. Célszerû lenne a táblákhoz olyan feltételeket rendelni, amelyek szigorúbb feltételeket definiálnak az egyes adatokra, amelyeket aztán a rendszer a tábla minden módosításánál ellenôriz. Ilyenek például: - az egyes adatok értékészletének az általános adattípusnál pontosabb definíciója (pl. adott intervallumba tarozás, adott halmazba tartozás, ahol a hallmaz lehet egy másik tábla egyik oszlopának értékei); - az oszlop elsôdleges kulcs, azaz a tábla soraiban minden értéke különbözô (hasonló hatás elérhetô a UNIQUE indexxel is); - az oszlop idegen kulcs, azaz értéke meg kell, hogy egyezzen egy másik tábla elsôdleges kulcs oszlopának valamelyik létezô elemével; Amennyiben a táblák módosításánál valamelyik feltételt megsértenénk, a rendszer egy kivétel jelet (exception) generál, lefuttatja a hibajelhez tartozó kiszolgáló utasítást, ha van ilyen.
7. PL/SQL A PL/SQL a nyelv olyan bôvítése, amely segítségével - több SQL utasítást össze lehet fogni egy programmá; - szükség esetén vissza lehet csempészni az algoritmikus porgramozás elemeit: változókat, vezérlési szerkezeteket. Az SQL SELECT utasítása több - elôre nem látható számú - sort állít elô, amelyet a klasszikus algoritmikus programozási nyelvek nem tudnak kezelni. A PL/SQL bevezette a kurzor fogalmát, amely segítségével egy lekérdezés által elôállított sorokon lehet végigpásztázni. Hosszas magyarázatok helyett nézzünk egy példát. A következô PL/SQL programrészlet kiszámolja a 20-as osztályban kifizetett összes pénzt (fizetés és prémium), megszámolja, hogy hányan kapnak 2000$-nál nagyobb fizetést, illetve hány dolgozónak nagyobb a prémiuma, mint a fizetése. Az erdményeket egy átmeneti (temp) táblába rakja. DECLARE total_wages NUMBER := 0; high_paid NUMBER := 0; higher_comm NUMBER := 0; BEGIN FOR emp_record IN (SELECT sal, comm FROM emp WHERE deptno = 20) LOOP emp_record.comm := NVL(emp_record.comm, 0); total_wages := total_wages + emp_record.sal + emp_record.comm; IF emp_record.sal > 2000 THEN high_paid := high_paid + 1 ENDIF; IF emp_record.comm > emp_record.sal THEN higher_comm := higher_comm + 1 ENDIF; ENDLOOP; INSERT INTO temp VALUES (high_paid, higher_comm, 'Total wages:' || TO_CHAR(total_wages)); COMMIT; END;
40
Bár a fenti értékeket elô lehetne állítani 3 független lekérdezéssel, de a fenti program csak egyszer halad végig az emp táblán, nem háromszor, ami nagy táblák esetén jelentôs futási idôkülönbséget jelenthet.
41
II. rész 8. Az adatbázis lekérdezések az ORACLE adatbáziskezelõ alapján 8.1. Oszlopok kiválasztása a táblából
Az adatbázis lekérdezése a SELECT paranccsal történik. Általános alakja : SELECT a kiválasztott oszlopok listája FROM a tábla amelyben az oszlopok vannak ; Az oszlopneveket listaszerûen kell megadni. Az oszlopok a megadás sorrendjében jelennek meg. SELECT DNAME, DEPTNO FROM DEPT ; Ha valamennyi oszlopot meg akarjuk jeleníteni, akkor a nevek helyébe *-ot kell ími. SELECT * FROM EMP ; Ha egy oszlopon belül egy értéket csak egyszer akarunk megjeleníteni, akkor a DISTINCT kulcsszót kell használni. SELECT DISTINCT JOB FROM EMP ; Az oszlopokhoz hivatkozási név (alias) is megadható. A hivatkozási név a parancs végrehajtása után is megmarad, egészen addig, amíg újra nem definiáljuk. Az oszlopok kiírásakor a fejrészben a hivatkozási név jelenik meg. SELECT DNAME DEPARTMENT, DEPTNO FROM DEPT : 8.2. Sorok kiválasztása a táblából
Feltételekkel lehet szabályozni, hogy mely sorok jelenjenek meg a táblában. A feltételekben használható operátorok, precedencia sorrendben : 1.
2. 3. 4.
= != ^= BETWEEN ... NOT BETWEEN NOT AND OR SEl.ECT FROM WHERE OR AND
<> < <= AND... IN (list) NOT IN
> >= LIKE NOT LIKE
IS NULL IS NOT NULL
* EMP DEPTNO = 30 JOB IN ('CLERK', 'SALESMAN') SAL NOT BETWEEN 2800 AND 3000 :
A LIKE operátor segítségével karakterminták illeszthetõk. Két speciális karakter használható a mintában: _ egyetlen tetszõ1eges karaktert jelent % tetszõleges, akár 0 hosszúságú karaktersorozatot jelent SELECT ENAME, JOB FROM EMP 42
WHERE
ENAME LIKE 'M%' ,
Alapesetben nincs meghatározva, hogy milyen sorrendben jelennek meg a sorok. De lehetõség van akár több oszlop szerinti rendezésre, növekvõ vagy csökkenõ sorrendben. Az alapértelmezés szerinti irány a növekvõ (ASC). SELECT ENAME, JOB, SAL FROM EMP ORDER BY JOB, SAL DESC ; Minden esetben a lista elejére kerülnek azok a sorok, amelyekben a rendezõ oszlopbeli mezõ értéke NULL. 8.3. A táblák összekapcsolása
A táblák összekapcsolása a WHERE szakaszban határozható meg, logikai kifejezés formájában. Amennyiben az összekapcsolandó táblákban azonos nevû oszlopok vannak, úgy az oszlopra való hivatkozáskor a tábla nevét is meg kell adni. SELECT DEPT.DEPTN0, DNAME, LOC, ENAME, JOB FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO IN (30,40) ORDER BY EMP.DEPTNO ; A táblához ideiglenes név is rendelhetõ. Ez a név az egész parancson érvényes. Segítségével a táblát akár saját magához is hozzákapcsolhatjuk. SELECT X.ENAME, Y.ENAME FROM EMP X. EMP Y WHERE X.MGR = Y.EMPNO ; A kapcsolat a táblák között nem-egyenlõség típusú is lehet. Ekkor az egyik tábla sora a másik tábla több sorához is hozzákapcsolódhat. Nagy méretû táblák esetén így nagyon sok sor keletkezhet. Ennek elkerülésére szûkítõ feltételeket ke11 megadni. SELECT X.ENAME, X.SAL. X.JOB, Y.ENAME, Y.SAL, Y.JOB FROM EMP X, EMP Y WHERE X.SAL > Y.SAL AND Y.ENAME = 'JONES' ; Ha a tábla egy sora nem elégíti ki a kapcso1ódási feltételt, akkor a sor nem jelenik meg az eredményben. Ha mégis látni szeretnénk, akkor ezt külön jelezni kell (+). SELECT DEPT.DEPTN0, DNAME. JOB, ENAME FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO (+) AND DEPT.DEPTNO IN (30,40) ORDER BY DEPT.DEPTNO A (+) hatására az SQL*Plus úgy kezeli az EMP táblát, mintha lenne egy csupa NULL értékkel rendelkezõ sora. Ezt a sort kapcsolja hozzá a másik tábla minden olyan sorához, amelyhez nem sikerült a feltételnek megfele1õ sort találni. Ez a módszer használható arra is, hogy kiválasszuk azokat a sorokat, melyeket nem lehet a másik táblához kapcsolni. SELECT DISTINCT DEPT.DEPTN0, DNAME. LOC FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO (+) AND EMPNO IS NULL ; 43
8.4. Számok kezelése
Az oszlopok kijelzési formátuma a COLUMN paranccsal adható meg. Aritmetikai kifejezések használhatók a SELECT, a WHERE, és az ORDER BY szakaszban is. SELECT ENAME, COMM/SAL. COMM, SAL FROM EMP WHERE JOB = 'SALESMAN' ORDER BY COMM/SAL DESC ; 8.4.1. Az aritmetikai függvények (oszlopfüggvények) minden sorra kiszámításra kerülnek, és minden sorra külön eredményt adnak. ABS (BALANCE) abszolút érték GREATEST (SAL,COMM) nagyobb érték LEAST (SAL, COMM) kisebb érték ROUND (SAL , 2) kerekítés két tizedes jegyre TO_NUMBER ( '34.5' ) szöveg átalakítása számmá TRUNC (SAL,2) csonkítás két tizedes jegyre 8.4.2. A csoportfüggvények több sorró1 adnak egyetlen összegzõ információt. A függvények NULL értékû mezõket nem veszik figyelembe. AVG (COMM) az oszlopbeli értékek átlaga COUNT (SAL) a sorok száma az oszlopban MAX (SAL) a legmagasabb érték az oszlopban MIN (SAL) a legkisebb érték az oszlopban SUM (COMM) az oszlopok elemeinek az összege
Mód van arra, hogy csak azokat a sorokat számoljuk össze, melyben különbözõ értékek vannak. SELECT COUNT (DISTINCT JOB) FROM EMP WHERE DEPTNO = 30; Acsoportfüggvényekkel együtt nem használható olyan kifejezés, amelyik minden sorra külön eredményt ad. SELECT ENAME, AVG(SAL) # rossz !!! Két esetben tehetõ ez alól kivétel: - a1kérdés használatakor SELECT ENAME, JOB, SAL FROM EMP WHERE SAL = (SELECT MAX(SAL) FROM EMP) ; -sorok csoportosításakor SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO ; Míg a sorok közül a WHERE szakaszban, a csoportok közül a HAVING szakaszban lehet válogatni. SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB 44
HAVING
COUNT(*) > 2 ;
Az SQL*Plus a következõ sorrendben végzi a feldolgozást : l. A WHERE szakaszban szerep1õ feltételek szerint kiválogatja a sorokat. 2. Kialakítja a csoportokat, kiszámítja a csoportfüggvényeket. 3. A HAVING szakaszban szerep1õ feltételek szerint kiválogatja a csoportokat. Acsoportfüggvények oszlopait címkézni lehet. SELECT JOB, AVG(SAL) "Average Salary" FROM EMP GROUP BY JOB ; 8.5. Szövegek kezelése
ACOLUMN paranccsal beállítható az oszlop szélessége. A kilógó szövegrészek átkerülnek a következõ sorba. COLUMN DNAME FORMAT A8 Aszövegek összefûzhetõk a || operátorral. SELECT NAME || '-' || LOC DEPARTMENTS FROM DEPT ; A szövegkonstansokat aposztrófok ( ' ) közé kell írni. A szövegben elõforduló aposztrófot a jel megduplázásával kell jelölni ('Tom' 's diner'). 8.5.1. Szöveges függvények
DECODE(GRADE,'A',4,'B',3,'C',2,0) default INITCAP(ENAME) INSTR(LOC.' ') LENGTH(ENAME) LOWER(ENAME) SOUNDEX('DES MOINES') SUBSTR(GRADE. 1,2) UPPER(ENAME)
szöveg dekódolása, utolsó érték a nagy kezdõbetû tartalmazás vizsgálata szöveg hossza kisbetûssé alakít hangzás szerint alakít át részszöveget képez nagybetûssé alakít
8.6. Dátumok kezelése
Adátum standard formátuma: 'DD-MON-YY', 17-FEB-93 A dátum típus az idõpontot is magában foglalja (default 12AM ). A dátumokkal számolni is lehet. Napokat lehet hozzáadni, illetve levonni. Két dátum kivonható egymásbó1. Ilyenkor az eredmény tört nap is lehet, erre vigyázni kell. 8.6.1. Dátum függvények
ADD_MONTHS(D,N) GREATEST(D1,D2) LEAST(Dl,D2) LAST_DAY(D) napja MONTHS_BETWEEN(D1,D2)
N hónapot hozzáad a dátumhoz kiválasztja a késõbbi dátumot kiválasztja a korábbi dátumot a dátumot tartalmazó hónap utolsó a dátumok közötti hónapok szárna 45
NEXT_DAY(D.'FRIDAY') a dátum utáni elsõ péntek ROUND(HIREDATE) egész napra kerekít TO_CHAR(D,'MM/DD/YY') dátumot szöveggé alakít TO_DATE(CHDATE,'MM/DD/YY') szöveget dátummá alakít A rendszer nyilvántart egy minden táblából kiválasztható pszeudooszlopot is, amely az aktuális rendszeridõt tartalmazza. SELECT SYSDATE FROM DUMMY ;
8.7. A NULL értékek kezelése
Két esetben kaphat egy mezõ, vagy változó NULL értéket. - ha értéke ismeretlen - ha értelmetlenség értéket adni neki Ha meg akarjuk vizsgálni, hogy egy mezõ értéke NULL-e, akkor az IS NULL illetve az IS NOT NULL feltételeket kell használni. Az = NULL feltétel nem használható. SELECT ENAME, SAL, COMM, JOB FROM EMP WHERE COMM IS NOT NULL ; Jelen verzióban a nulla hosszúságú karakterlánc is NULL értéknek számít, de a késõbbiekben ez változhat. A rendezések során, függetlenül az iránytól, mindig a NULL értéket tartalmazó sorok kerülnek a sor elejére. Ha egy kifejezésben egy tag NULL értékû, akkor az egész kifejezés értéke NULL lesz. Az NVL függvénnyel rendelhetünk értéket a NULL értékû mezõkhöz. SELECT ENAME. JOB, SAL, COMM, SAL+NVL(COMM,0) FROM EMP WHERE DEPTNO = 30; 8.8. Alkérdések ( Subqueries )
Többnyire a WHERE szakaszban használatosak. Akkor érdemes használni, ha a sorok kiválasztására szolgáló feltétel függ a táblában szereplõ értékektõl. SELECT ENAME, JOB FROM EMP WHERE JOB = (SELECT JOB FROM EMP WHERE ENAME = 'JONES' ); Ha az alkérdés több értéket ad vissza, akkor az ALL és az ANY segítségével kell a feltételeket megfogalmazni. Az = ANY helyett az IN, a != ALL helyett a NOT IN használható. SELECT SAL, JOB. ENAME, DEPTNO FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30 );
46
Az alkérdés több oszlopot is visszaadhat eredményként. A feltételeket ennek megfelelõen kell megfogalmazni. SELECT ENAME, JOB, SAL FROM EMP WHERE (JOB, SAL ) = (SELECT JOB, SAL FROM EMP WHERE ENAME = 'FORD' ); Az alkérdések egymásba ágyazhatók, logikai (AND, OR) és halmazmûveletekkel (UNION, INTERSECT, MINUS ) összekombinálhatók. Ha a fõkérdésben és az alkérdésben ugyanaz a tábla szerepel, és a fõkérdésbeli táblára akarunk hivatkozni, akkor a fõkérdésben hivatkozási nevet kell a táblához rendelni. Ha csak arra vagyunk kíváncsiak, hogy létezik-e legalább egy sor, ami a feltételt kielégíti, akkor az EXISTS függvényt használhatjuk. SELECT JOB, ENAME, EMPNO, DEPTNO FROM EMP X WHERE EXISTS (SELECT * FROM EMP WHERE X.EMPNO = MGR ) ORDER BY EMPNO; 8.9. Fa struktúrába rendezett táblák
A tábla sorait precedencia szabály megadásával fa struktúrába lehet rendezni. A gyökérelem kijelölése után a fa csomópontjai a mélységi bejárás sorrendjében kiírhatók, ha szükséges, akkor a mélységi szintszám feltüntetésével. Az alábbi példa szerint, ha egy sorban az EMPNO érték megegyezik egy másik sorban található MGR értékkel, akkor az elsõ sor szülõje a másodiknak. A fa bejárása a kezdõ elemtõl, mint gyökértõl a levelek irányába halad, és a gyökér valamennyi leszármazottját érinti. SELECT LEVEL, ENAME, EMPNO, JOB, MGR FROM EMP CONNECT BY PRIOR EMPNO = MGR START WITH ENAME = 'KING' A PRIOR kulcsszó áthelyezésével megváltoztatható a bejárási irány. A következõ példában a bejárás a kezdõ elemtõl mint levéltõl a gyökér felé halad. Ebben az esetben a bejárás csak a levél õseit érinti. SELECT LEVEL, ENAME, EMPNO, JOB. MGR FROM EMP CONNECT BY EMPNO = PRIOR MGR START WITH ENAME = 'SMITH'; A WHERE szakaszban megadott feltételek nem befolyásolják a fa bejárását. Ha le akarunk vágni bizonyos ágakat, akkor a CONNECT szakaszban kell a feltételeket megadni. SELECT ENAME, JOB FROM EMP CONNECT BY PRIOR EMPNO = MGR AND ENAME != 'SCOTT' START WITH ENAME = 'JONES'; 47
A mûveleteket az SQL*Plus a következõ sorrendben hajtja végre: Megkeresi a kezdõ csomópontot Létrehozza a fa struktúrát a kapcsolatnak megfelelõen Bejárja a fát a megadott irányban Levágja a feltétel szerinti ágakat Kiválogatja a WHERE szakaszbeli feltételeknek megfelelõ sorokat Sorbarendezi a sorokat
48
9. Táblák létrehozása és módosítása 9.1. Táblák létrehozása
A táblát a CREATE TABLE paranccsal lehet létrehozni. Létrehozáskor meg kell adni a tábla nevét, az oszlopok nevét, típusát, szélességét és esetleg további paramétereket, például, hogy a mezõ tartalma nem lehet NULL. CREATE TABLE PROJ (PROJNO NUMBER NOT NULL, PNAME CHAR(10), BUDGET NUMBER(8,2), START DATE, DESCRIPT LONG); A nevek betûvel kell, hogy kezdõdjenek, tartalmazhatnak számokat, valamint a _, #, és $ karaktert. A kis és nagy betûk azonosnak számítanak. Amennyiben a neveket idézõjelek között adjuk meg, abban az esetben az idézõjelen kívül minden betû használható, és ilyenkor a kis- és nagybetûk is különbözõnek számítanak. Nem használhatók névként a foglalt kulcsszavak. A tábla neveknek egyedieknek kell lenniük, hosszuk legfeljebb 30 karakter lehet. A CHAR típus maximális hossza 240. A NUMBER típus legfeljebb 40 számjegy szélességû lehet. A DATE típusban i.e. 4712 január l. és i.sz. 4712 december 31. közötti dátumok tárolhatók. A LONG típus hasonló a CHAR típushoz, de 65535 karakter hosszúságú adatot tárol. A definiált szélességtõ1 függetlenül a mezõ csak annyi tárolóhelyet foglal, amennyi az adat tárolásához szükséges. 9.2. Sorok beszúrása, módosítása és törlése
Új sor beszúrása a táblába az INSERT paranccsal történik. INSERT INTO EMP VALUES ( 7954, 'CARTER', 'CLERK', 7698. '7-APR-84', 1000, NULL, 30); A fenti esetben az értékeket olyan sorrendben kell megadni, amilyen sorrendben a mezõk a tábla létrehozásakor definiálva voltak. Ha ettõ1 el akarunk térni, akkor a mezõk neveit is meg kell adni. A fel nem sorolt mezõk értéke NULL lesz. INSERT INTO EMP ( EMPNO, ENAME, HIREDATE, DEPTNO, SAL ) VALUES ( 7955, 'WlLSON', TO DATE('1993-FEB-19 9:30', 'YYYY-MON-DD HH:Ml'), 30, 1500); A táblába beszúrandó sorok a SELECT paranccsal is elõállíthatók. Így több sor is beszúrható egyszeire. INSERT INTO BONUS (ENAME, JOB, SAL, COMM ) SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE JOB = 'MANAGER' OR COMM > 0.25 * SAL; A tábla létrehozása és a sorok feltöltése akár egyetlen paranccsal is megoldható. CREATE TABLE BONUS (ENAME, JOB, SAL, COMM ) AS SELECT ENAME, JOB, SAL, COMM 49
FROM WHERE OR
EMP JOB = 'MANAGER' COMM > 0.25 * SAL;
A fentiek hatására még nem módosul az adatbázis. A változtatásokat a COMMIT paranccsal kell véglegesíteni. A ROLLBACK parancs segítségével még visszaállítható a legutolsó COMMIT parancs utáni állapot. Az UPDATE paranccsal a már meglévõ sorokban is meg lehet változtatni a mezõk értékét. A SELECT parancs eredménye itt is használható. UPDATE EMP SET SAL = 1.5 * SAL WHERE ENAME IN (SELECT ENAME FROM BONUS); A DELETE paranccsal lehet a táblából sort, sorokat törölni. DELETE FROM BONUS WHERE JOB IN (SELECT JOB FROM EMP WHERE EHAME = 'JONES' ); A SET AUTOCOMMIT IMMEDIATE/ON parancs kiadása után az INSERT, UPDATE és DELETE parancsok azonnal módosítják az adatbázist. Ebben az esetben nincs lehetõség a "visszagörgetésre". Az eredeti üzemmód a SET AUTOCOMMIT OFF paranccsal állítható vissza. A két egymást követõ COMMIT közötti változásokat tranzakciónak nevezik. Ha egy tranzakciót nem sikerül teljesen végrehajtani valamilyen hiba miatt, akkor a rendszer automatikusan visszaállítja a tranzakció e1õtti állapotot. Ezzel megelõzi a nemkívánt változásokat és megõrzi az adatbázisok integritását. 9.3. Táblák módosítása
A táblák formátumának módosítására szolgál az ALTER TABLE parancs. Két olyan változtatás van, amely nem változtatja meg a tábla adatait : egy oszlop szélességének növelése, és egy új oszlop bozzáadása a táblához. ALTER TABLE PROJ MODIFY ( BUDGET NUMBER(9,21); ALTER TABLE EMP ADD ( PROJHO NUMBER ) ; 9.4. Nézet-táblák (VIEW) készítése és használata
A nézet-tábla egy ablakhoz hasonlít, amelyen keresztül a táblákban levó adatok megvizsgálhatók és módosíthatók. Használatukkal elkerülhetjük a bonyolult kérdések használatát. Ezen kívül fontos szerepet játszanak a biztonság szempontjából. A nézet-tábla létrehozát pontosan úgy kell elvégezni, ahogyan a táblákat. CREATE VIEW EMP10 AS SELECT EMPNO. ENAME, JOB FROM EMP WHERE DEPTNO = 10 ; 50
Egyetlen megkötés, hogy nem lehet benne ORDER BY szakasz. A rendezést a kérdezéskor kell megadni. A kérdezések pontosan úgy végezhetõk mint a közönséges táblákon. SELECT FROM WHERE
ENAME, JOB EMP10 EMPHO > 7800 ;
Az adatok a nézet-táblán keresztül közvetlenül módosíthatók. UPDATE EMP10 SET JOB = 'CLERK' WHERE ENAME = 'MlLLER' , A nézet-táblával több tábla összekapcsolására is lehetóség van. CREATE VIEW PROJECTS ( PROJECT, EMPLOYEE. EMP_NUMBER, LOCATION ) AS SELECT PNAME, ENAME, EMPHO, LOC FROM PROJ, EMP, DEPT WHERE EMP.DEFTNO = DEPT.DEPTNO AND EMP.PROJNO = PROJ.PROJNO; Kifejezések vagy fúggvények segítségével úgynevezett virtuális oszlopok, vagy mezõk definiálhatók. CREATE VIEW PAY (NAME, MONTHLY_SAL, ANNUAL_SAL, DEPTNO ) AS SELECT ENAME. SAL, 12*SAL, DEPTNO FROM EMP; SELECT FROM PAY WHERE
* DEPTNO = 30;
51
10. Függelék 10.1 Minta táblák
EMP - a személyek adatait tartalmazza DEPT - az egyes osztályok adatait tartalmazza BONUS - a jutalmakat tartalmazza SALGRADE - a fizetési csoportokat tartalmazza.
EMP, DEPT, BONUS és a SALGRADE tablák DEPT DEPTNO 10 20 30 40
DNAME ACCOUNTING RESEARCH SALES OPERATIONS
LOC MEW YORK DALLAS CHICAGO BOSTON
BONUS EMAME SMITH ALLEN WARD JONES MARTIN
SALGRADE JOB CLERK SALESMAN SALESMAN MANAGER SALESMAN
SAL 800.00 1,600.00 1,250.00 1,875.00 1,250.00
COMM 300.00 300.00 500.00 1,l00.00
GRADE 1 2 3 4 5
OSAL 700 1201 1101 2001 3001
HISAL 1200 1100 2000 3000 8995
EMP EMPN O 7329 7499 7521 7566 7654
ENAME
JOB
MGR
HIREDATE
SALARY
SMITH ALLEN WARD JONES MARTIN
CLERK SALESMAN SALESMAN MANAGER SALESMAN
7902 7698 7698 7839 7698
17-DEC-80 20-FE8-81 22-FE8-81 02-APA-8I 28-5EP-8I
800.00 1.600.00 1,250.00 2.975.00 1,250.00
7698 7782 7788 7839
BLAKE CLARK SCOTT KING
7839 7839 7566
01-MAY-81 09-JUN-81 09-DEC-82 17-NOV-81
2,850.00 2,450.00 3,000.00 5,000.00
30 10 20 10
7844 7876 7900 7902 7934
TURMER ADAMS JAMES FORD MILLER
MANAGER MANAGER ANALYST PRESIDEN T SALESMAN CLERK CLERK ANALYST CLERK
7698 7788 7698 7566 7782
OB-SEP-81 12-JUN-83 03-DEC-81 03-DEC-81 23-JAH-82
1,500.00 1.100.00 950.00 3,000.00 1,300.00
30 20 30 20 10
52
COMM
300.00 500.00 1,400.0 0
DEPTNO 20 30 30 20 30
10.2 A számok formátuma
Format
Value
Displays As
Explanation
999.99 999V99
56.478 56.478
56.48 5648
9,999 9,999 99999 09999 9999 9999MI 9999PR B999 B999 99.99 $99.99 $99.99P R 9.99EEE E
8410 639 607 607 -5609 -5609 -5609 0 564 124.98 4523 -45.23
8,410 639 607 607 -5609 5609<5609> 564 ##.## $4523 <$45.23>
Rounds to 2 decimal places Multiply value by 100, when n is the number of 9's after 'V' Comma separates thousands No comma if no thousands Leading zeroes normally absent Leading zeroes fill format Minus sign normally precedes Minus sign follows number Negative numbers parenthesized Zero values displayed s blocks No effect if valu s Value too large for format Dollar sign displayed Formats may be combined
12000
1.20E + 04
Exponential notation (1.20x10a)
Note: COLUMN is a SQL*Plus command, not a SQL command. It does not affect the SQL command in the SQL buffer. The column format will sta in effect until you cancel it, set a new one, or exit from Exercise ` SQL*Plus.
53
10.3 Dátum formátumok Elemen Meaning YYYYorSYYYY Year; 'S' prefixes "BC" date with sign (-). YYY YYorY Last 3, 2, or 1 digit(s) of year. SYEARorYEAR Year, spelled out; '5' prefixes "BC" date with sign (-). Q Quarter of year. MM Month. MONTH or MON Name of month, or 3-letter abbreviation. DDD DD or D Day of year, month, or week. DAY or DY Name of day, or 3-letter abbreviation. AM or PM Meridian indicator. A.M. or P.M. Meridian indicator with periods HH or HH12 Hour of day(1-12) HH24 Hour of day (0-23) MI Minute. SS Second. /. , etc. Punctuation is reproduced in the resuit. "..." Quoted string is reproduced in the result The suffixes below may be added to the codes above: TH Ordinal number (e.g., "DDTH" for "4TH"). Sp Spelled-out number (e.g , "DDSP" for "FOUR"). SPTH or THSP Spelled-out ordinal number (e.g , "DDSPTH" for"FOURTH"). Capitalization in a spelled-out word or abbreviation follows the capitalization in the eorresponding format element. For example, "DAY" in a format model produces capitalized words like "MONDAY"; "Day" produces "Monday"; and "day" produces "monday."
54
Ellenõrzõ kérdések az adatbázisok fejezethez II. évf. Villamosmérnöki szak, 1994. 1. Mi az adatbáziskezelô rendszer feladata? 2. Az adatbáziskezelô szempontjából hogyan csoportosítjuk a felhasználókat? 3. Hogyan biztosítják az adatok biztonságát illetéktelen felhasználókkal szemben? 4. Mi a formai integritás és a struktúrális integritás? 5. Miért szükséges a szinkronitás biztosítása? 6. Milyen feladatokat végezhet az adatbázison a képzetlen felhasználó? 7. Milyen feladatokat végez az applikáció programozó? 8. Mi a feladata az adatbázis adminisztrátornak? 9. Ki a felelõs az adatbázis karbantartásáért? 10. Ki adja ki a jogosultságokat? 11. Ki végezheti el az idõszakos mentéseket, és meghibásodás esetén ki állíthatja vissza az adatbázist? 12. Ki jogosult az adatbázis struktúrájának megváltoztatására, és mire kell ügyelnie közben? 13. Mi a fizikai adatbázis? 14. Mi a fogalmi adatbázis? 15. Mi a nézet fogalma, mire jó? 16. Mit nevezünk sémának? Mi az eset? 17. Mit értünk fizikai adatfüggetlenségen? 18. Mi a logikai adatfüggetlenség? 19. Mi az entitás? 20. Mi az attribútum? 21. Mit nevezünk relációnak? 22. Adja meg heap modell esetén a beszúrás, keresés és törlés algoritmusát! Számítsa ki a helyszükségletét! 23. Adja meg hash címzésû állományok esetén a beszúrás, keresés és törlés algoritmusát! Számítsa ki a helyszükségletét! Hogyan alakulnak az elôbbiek "vödör" használata esetén? 24. Adja meg sûrûn indexelt állományok esetén a beszúrás, keresés és törlés algoritmusát! Számítsa ki a helyszükségletét! 25. Adja meg B-fák esetén a beszúrás, keresés és törlés algoritmusát! Számítsa ki a helyszükségletét! 26. Adja meg invertált állományok esetén a beszúrás, keresés és törlés algoritmusát! Számítsa ki a helyszükségletét! 27. Mi a jellemzõje a hálózati modellnek? 28. Mi az adatdefiníciós nyelv? Mi az adatmanipulációs nyelv? 29. Hogyan valósítható meg hálózati modellben a többes-többes kapcsolat? 30. Mit jelent a navigálás a hálózati modellben? 31. Mi a hierarchikus modell jellemzôje? 32. Hogyan alakítható át hálózati modell hierarchikussá? 33. Mi a virtuális rekord szerepe a hierarchikus modellben? 34. Mit nevezünk relációnak? 35. Adja meg a reláció matematikai definícióját! 36. Mi a reláció fokszáma és mi a számossága? 37. Mi a reláció kulcsa? 38. Mit jelent az, hogy a reláció az adatok logikai reprezentációja?
55
39. Egyesítse az alábbi két relációt! R1 R2 R1UR2 = ? A B C D E F a b c a c d c b a a d c a c d b a c 40. Képezze a fenti relációk különbségét! 41. Készítse el az alábbi relációk DESCARTES-i szorzatát! R3 R4 R3 X R4 A B C D a b c a b c a d 42. Mi a vetítés? Képezze a Π C,A (R1) vetítést! 43. Mi a kiválasztás? Végezze el R1-en az alábbi kijelôlt kiválasztást! σA
66. Végezze el az alábbi reláció dekompozícióját! R(X,Y,Z) {ZÆX} R1(X,Z) és R2(Y,Z) veszteséges vagy veszteségmentes? 67. Mikor mondjuk, hogy egy reláció 0NF alakú? 68. Mikor mondjuk, hogy egy reláció 1NF alakú? 69. Mikor mondjuk, hogy egy reláció 2NF alakú? 70. Mikor mondjuk, hogy egy reláció 3NF alakú? 71. Mikor mondjuk, hogy egy reláció BCNF alakú? 72. Adja meg a BCNF felbontás lépései 73. Mi a minimális függôséghalmaz? Hogyan állítjuk elõ? 74. Adja meg a 3NF-re hozás lépéseit! 75. Mi a többértékû függõség? 76. Adjon mintapéldát többértékû függõségre! 77. Mik az SQL nyelv összetevõi? 78. A lekérdezõ parancs általános alakja! 79. Milyen parancs végzi az oszlopok kiválogatását? Írjon lekérdezési parancsot, amely kiirja az EMP táblából a dolgozók nevét és fizetését, valamint prémiumát! 80. Melyik kulcsszóval lehet a sorok közül válogatni? Irjon lekérdezési parancsot, amely kiirja az EMP táblából a dolgozók nevét és fizetését, valamint prémiumát, ha a fizetésük 2000.00 -nél kisebb! 81. Milyen oszlopfüggvényeket ismer? Írjon lekérdezési parancsot, amely kiirja az EMP táblából a dolgozók átlagfizetését! 82. Milyen matematikai függvényeket ismer? Írjon lekérdezési parancsot, amely kiírja az EMP táblából a dolgozók nevét és évi fizetését, valamint prémiumát! 83. Milyen string mûveletek értelmezettek az SQL-ben? Írjon lekérdezési parancsot, amely kiirja az EMP táblából az 'K'betûvel kezdõdõ dolgozók nevét és fizetését, valamint prémiumát! 84. Milyen dátumfüggvények vannak? 85. Mit jelent a NULL érték, hogyan lehet ezt kezelni? Írjon lekérdezési parancsot, amely kiírja az EMP táblából a prémiumok átlagát az egész táblára, illetve csak azokra átlagolva, akik prémiumot kaptak! 86. Hogyan lehet táblákat összekapcsolni? Írjon lekérdezési parancsot, amely kiirja az EMP és a DEPT táblából a dolgozók nevét és fizetését, valamint mukahelyüket! 87. Hogyan lehet egy táblát saját magával összekapcsolni? Írjon lekérdezési parancsot, amely kiirja az EMP táblából a dolgozók nevét és fizetését, valamint prémiumát, ha a dolgozó többet keres a fônökénél! 88. Melyik parancsszó szolgál a csoportosításra, és melyik a rendezésre? Írjon lekérdezési parancsot, amely kiirja az EMP táblából a dolgozók nevét és fizetését, valamint prémiumát osztályonként! Rendezze névsorba! 89. Hogyan lehet a csoportok között válogatni? 90. Lehet-e egy select-en belül újabb select? 91. Hogyan lehet egy táblát struktúrába rendezni? 92. Új sor befûzése egy táblába? 93. Sor(ok) törlése egy táblából? 94. Mezõk módosítása egy sorban? Mi a hatása, ha a WHERE parancs elmarad? 95. Új táblák létrehozása? Milyen tipusú mezôket ismer az SQL? 96. Nézet létrehozása? 57
97. Index létrehozása? 98. Tábla, nézet és index törlése? 99. A COMMIT és a ROLLBACK szerepe. 100.Jogosultságok definiálása?
58
101. Adott az alábbi leírás: Egy paciensnek egy vagy több betegsége is lehet, vannak betegségek, amiben pillnatnyilag senki sem szenved. Minden pacienst egyetlen mentõállomáson kezelnek, akár több orvos is. Az orvosoknak lehet több paciensük is, akik különbözõ mentõállomásokon is fekhetnek. Egy mentõállomás akár üres is lehet és mindig pontosan egy kórházhoz tartozik. Egy kórháznak több mentõállomása és több orvosa is lehet. Egy orvost legfeljebb 3 kórház alkalmaz. Készítsen a fentiekrõl egyed-kapcsolati (ER) diagramot és alakítsa át relációs sémákká! 102. Adottak az alábbi relációk: JÁTÉKOS(JSZÁM, JNÉV, KOR, NEM,CSSZÁM) EDZÔ(ESZÁM, ENÉV,JÁTÉK) CSAPAT(CSSZÁM, CSNÉV,ESZÁM) Adjon relációalgebrai kifejezést az alábbi lekérdezésre: Keresett az összes olyan <JNÉV, ESZÁM, JÁTÉK> 3-as, ahol az adott edzô adott játékos edzôje, és a játékos idôsebb 25 évnél. 103. Alakítson ki egy egyed-kapcsolati (ER) diagramot az alábbi informális leírásból, majd alakítsa át relációs sémákká! A cél egy fodrászati kellékszállító társaság tevékenységének leírása. A következõket szeretnénk ábrázolni: Alkalmazott: név, személyi szám, cím, a fiók, ahol dolgozik, Ügynök: olyan alkalmazott, aki kellékek eladásával foglalkozik, Fiók: cím, vezetõje, az ott dolgozók, az oda tartozó kellékek, Kellék: gyártó neve, kellék azonosító száma, kellék típusa, a kellék ára, a kelléket szállító ügynök neve, Üzlet: ahová a kellékeket szállítják. Tároljuk az üzlet nevét és címét. 104. Adja meg az alábbi R séma egy minimális lefedését az F függõséghalmaz mellett: R(A, B, C, D), F={CD→AB, B→A, A→BD, AB→C}. 105. Adott az R(A, B, C, D, E) relációs séma és az F={AC→B, A→E, C→D} függõséghalmaz. Adja meg R-nek egy veszteségmentes felbontását BCNF relációkba!
59
Ellenõrzõ kérdések a számítógéphálózatok fejezethez II. évf. Villamosmérnöki szak, 1995. 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20.
21. 22. 23. 24. 25. 26. 27.
28. 29. 30. 31. 32. 33. 34.
Számítógép hálózatok jelentôsége, néhány tipikus alakalmazása. A számítógépes hálózatok csoportosítása kiterjedés szerint. Soroljon fel 4 fogalmat a számítógépes hálózatokkal kapcsolatban, adja meg értelmezésüket! Két pont közötti és üzenetszórásos átvitel jellemzôi, azonosságai, különbségei. Rajzoljon fel legalább 4 hálózati topológiát, adja meg a megnevezésüket! Miért többszintûek a hálózati protokollok? Mit nevezünk hálózati architektúrának? Hogyan valósul meg a kommunikáció a felsôbb rétegek között? A protokoll-tervezés szempontjai, fôbb lépései. Az OSI 7 rétegû hivatkozási modell ismertetése. Két alkalmazási folyamat kommunikációja az OSI modell alapján. Hogyan valósul meg fizikailag az adatátvitel az OSI modellben? Összeköttetés alapú és összeköttetés mentes szolgálatok jellemzôi, azonosságok, különbségek. Mik a szolgálat primitívek osztályai (tipusai), hogyan mûködnek? Mutasson egy példát a megerôsített szolgálatra!(pl. telefon)! A szolgálatok és a protokollok közötti kapcsolat. Négyszintû digitális kódolás esetén másodpercenként hány bitet lehet átküldeni egy X MHz sávszélességû csatornán zajmantes esetben? Mekkora maximális jelátviteli sebesség érhetô el egy zajos csatornán? PL. ha a sávszélesség 3 kHz, és a jel/zaj viszony 30 dB? Mi a Manchester-kódolás elve? Hogyan ábrázoljuk a logikai 0 ill. 1 szintet? Kódolja be az következô jelsorozatot: 100111010100! Mi a különbségi Manchester-kódolás elve? Hogyan ábrázoljuk a logikai 0 ill. 1 szintet? Kódolja be az következô jelsorozatot: 100111010100, ha a rendszer induláskor alacsony állapotban van! Egy modem atviteli diagramjának pontjai: (1,1) (1,-1) (-1,-1) (-1,1). Hány bit/sec érhetô el 2400 baud esetén? Mi a jelzési sebesség? Mi a sávszélesség? Rajzoljon fel egy szélessávú átvitel topológiát kétkábeles ill. egykábeles megoldással! Mit jelent az alacsonymettszésû illetve a középmettszésû átviteli rendszer? Mit ért egymódusú ill. többmódusú optikai szálon? Mi a különbség egy passzív csillag és egy optikai ismétlô közôtt? Milyen vezetéknélküli átviteleket ismer? Mit használna két épület között, két városrész között, két város között vagy az ország területén több város összekapcsolására? Milyen modulációkat ismer? Mire jók ezek? Mi a QAM moduláció? Mire jó a visszhangelnyomó, milyen problémát okoz ez a számítógépes adatátvitlben? Hogyan lehet ezt kiküszöbölni? Mire való az RS232-C szabvány? Mi az azonosság és a különbség az RS232_c és az X.21 interfész között? Mi az impulzuskód-moduláció(PCM)? Külömbségi és delta moduláció? Mi a frekvenciaosztásos és az idôosztásos multiplexelés? Mi a különbség és az azonosság köztük? A helyi hálózatok (LAN-ok ) és a nagyvárosi hálózatok ( MAN-ok) összehasonlítása jellemzôik alapján! Statikus és dinamikus csatornakiosztások, jellemzôik. 60
35. A perzisztens és nem perzisztens CSMA jellemzôi a csatornakihasználás szempontjából. 36. A perzisztens és nem perzisztens CSMA jellemzôi a csatorna hozzáférés szempontjából. 37. Miben különbözik a CSMA/CD a CSMA protokolltól? 38. Folyamatábrával írja le egy sín rendszerû 802.3 tipusú LAN állomásainak belsô mûködését 1-perzisztens CSMA/CD esetén! 39. Folyamatábrával írja le egy sín rendszerû 802.3 tipusú LAN állomásainak belsô mûködését p-perzisztens CSMA/CD esetén! 40. Folyamatábrával írja le egy sín rendszerû 802.3 tipusú LAN állomásainak belsô mûködését nem perzisztens CSMA/CD esetén! 41. Folyamatábrával írja le közeghozzáférési szinten egy gyûrû topológiájú 802.5 tipusú LAN egy állomásának belsô mûködését! 42. Folyamatábrával írja le közeghozzáférési szinten egy sín topológiájú 802.4 tipusú LAN egy állomásának belsô mûködését! 43. Folyamatábrával adja meg egy többpontos kialakítású terminál hálózat egy termináljának belsô mûködését, ha karakterorientált protokollal dolgozik! 44. Folyamatábrával adja meg egy kétpontos kialakítású terminál hálózat egy termináljának belsô mûködését, ha karakterorientált protokollal dolgozik! 45. Folyamatábrával adja meg egy kétpontos kialakítású terminál hálózat számítógépének belsô mûködését, ha karakterorientált protokollal dolgozik! 46. Mikor használunk ismétlôt és mikor hidat? 47. Egy 802.3 MAC protokollal átvitt 22522 byteos file átvitelénél az utolsó keretben mnnyi lesz a "töltelék" byte-ok száma, ha egyébként a maximális adatátviteli hosszat kihasználjuk? 48. Mit nevezünk kettes exponenciális visszatartásnak? 49. Mi a vezérjel? Hogyan befolyásolja a csatornakiosztást? 50. A kábelcsatlakozás vezérjeles sín és vezérjeles gyûrû esetén. 51. Vezérjeles sín ill. vezérjeles gyûrû esetén milyen az állomások hierarchiája jogosultságok szempontjából? 52. Miárt szükséges a maximális kábelhosszat korlátozni sín topológiájú hálózatnál? 53. Miért korlátozzák a minimális "hosszat" gyûrû topológia esetén? Mi a felügyelô állomás feladata, ha sok a tétlen állomás? 54. Milyen kódolást használnak a 802.3 esetén? Mekkora a jelzési sebessége, ha az átvitel 10 Mbit/sec? 55. Ki a felelôs vezérjeles gyûrûben a keretek eltávolításáért? Mikor keletkezik árva keret? 56. Mi a feladata a felügyelô állomásnak vezérjeles gyûrû esetén? 57. Beléptetés mechanizmusa vezérjeles sIn és gyûrû esetén? 58. A vezérjel továbbadása, átvétele vezérjeles sín és gyûrû esetén? 59. Mennyiben "gyûrû" a vezérjeles sín? 60. Az adatkapcsolati réteg fôbb szolgálat tipusai, ezek jellemzôi? 61. Milyen keretképzési módokat ismer? 62. Karakteralapú adatkapcsolati vezérlésnél melyik keretezési módot használjuk és hogy néz ez ki? 63. SDLC (HDLC) tipusú vezérlésnél melyik keretezési módot használjuk és hogyan valósul ez meg? 64. Egy karakterbeszúrásos algoritmus esetén az adatok között az alábbi karakterek találhatók: ...,A,B,DLE,C,DLE,STX,D, DLE,DLE,ETX E,F,... Hogyan fog ez a karaktersorozat kinézni az átvitel alatt? 61
67. A következô bitsorozat átvitelénél bitbeszúrást alkalmazunk. Hogyan fog kinézni a bitbeszúrás után? 111011110111110111111011111110000000 69. Használható-e a fizikai szintû kódolás megsértése keretezésre Manchester kódolás esetén ill. kólönbségi Manchester kódolásnál? 70. Hasonlítsa össze csatorna kihasználás szempontjából a körbekérdezéses és a központ felé haladó lekérdezéses terminálkezelést! 71. Mi a paritásbit? Mire jó? 72. Mi a Hamming távolság, mi a feltétele a hibafelismerésnek és a hibajavításnak a Hamming távolsággal kifejezve? 73. Egy átviendô kodszó a következô: 100001011. Mi lesz az átvitt bitsorozat, ha a generátor polinom 10011? 74. Állítsa elô az elküldendô bitsorozatot, ha az átvitelnél CRC-t alkalmazunk, és az információ bitsorozata: 10011101, a generátor poligon pedig P(x) = x5+x2+x+1 76. Adja meg a CRC kód generálásának és vételének lépéseit! 77. Mi az átlapolt kód, egy 1 bit javítására alkalmas kódolás esetén k*os átlapolás esetén milyen hibák javítására képes? 78. Milyen hibák felismerésére alkalmas egy 16 bites CRC kód? 79. Miért van szükség adatkapcsolati szintû hibavédelemre és forgalomszabályozásra? 80. A hálózati réteg szolgálatai, ezek céljai. 81. Melyek az összeköttetés-alapú hálózati szolgálat legfontosabb jellemzôi? 82. Hasonlítsa össze az összeköttetésmentes és az összeköttetésalapú hálózati szolgálatot! 83. Melyek a fôbb hálózati összeköttetés primitívek(csoportok)? 84. A hálózati összeköttetés mûködése virtuális áramkörök esetén? 85. A hálózati összeköttetés mûködése datagramm tipusú szolgálat esetén? 86. Hasonlítsa össze a virtuális áramkörök és a datagramm tipusú szolgálatot! 87. Melyek a forgalomirányító algoritmussal szemben támasztott legfontosabb követelmények? 88. Milyen jelenséget nevezünk a számítógépes hálózatban torlódásnak? 89. Melyek a hálózati együttmûködés legfontosabb kérdései? 90. Két különbözô alhálózat összekapcsolása hoszton keresztül. Ábra, mûködés, adatfolyam? 91. Mi a különbség a híd és az átjáró között? 92. Mik a legfontosabb szállítási szolgálatok. 93. Ábrázolja a szállítási rátegek logikai és fizikai kapcsolatait! 94. Mi az opcióegyeztetés? Miért kell egyeztetni a szolgálat minôségi jellemzôit? 95. A szállítási primitívek sorrendje összeköttetés létesítés és bontás esetén? 96. Miért kell a szállítási protokollokat osztályba sorolni? Melyek ezek az osztályok? 97. Melyek a szállítási protokollokkal megvalósított feladatok ( protokollelemek)? 98. A viszonyréteg szolgálatai. 99. Rajzolja fel az egyes bontási lehetôségek esemény sorrendjét! 100. Az adatcsere hirtelen bontás és rendezett bontás esetén? 101. Mi a kölcsönhatás menedzselés? Mi a különbség duplex és félduplex esetben ? 102. Mire jók a szinkronizációs pontok? Mi a különbség a fô- és a mellékszinkronizációs pontok között?
62
103. Mi a jelentôsége a viszonyszintû tevékenység strukrúrálásának, tevékenységekkénti kezelésének? 104. Hogyan valósul meg a tevékenység felfüggesztése és újraindítása? 105. A megjelenítési réteg szolgálatai. 106. Milyen feladatok megoldását végzi a megjelenítési réteg? 107. Mi a titkosítás feladata? 108. Mik az alkalmazási réteg szolgálatai. 109. Mi a virtuális terminálkezelés, miért szükséges? 110. Mit értünk virtuális állománykezelés alatt? 111. Melyek azok a legfontosab alkalmazások, amelyek támogatására alkalmazási szintû szolgálatatókat és protokollokat dolgoztak ki?
63
