Közlekedési információs rendszerek I

Közlekedési információs rendszerek I. számítógépes laborgyakorlat jegyzet

Szerzők: Dr. Csiszár Csaba, Caesar Bálint, Csonka Bálint és Földes Dávid Kulcsszavak: információ, adat, adatbázis, SQL nyelv, Microsoft Access A tananyag összefoglalja az adatbázis-kezelő rendszerek és a számítógépes adatbázis-kezelés alapjaival kapcsolatos fogalmakat, ismeretanyagot. Az SQL nyelv parancsai és azok alkalmazási szabályai („szavak”+”nyelvtan”) minta adatbázison keresztül kerülnek ismertetésre. A képzés során a hallgatók elsajátítják az adatmodellezés és az adatbázisok önálló tervezésének módszereit, és ezek alapján képessé válnak saját adatbázis létrehozására és az adatok több szempont szerinti feldolgozására.

Tartalomjegyzék Előszó

1

1. Az adatbázis-kezelés alapjai

2

Az adatbázis-kezelő rendszerek és szoftverek fejlődése Az SQL nyelv jellemzői A Microsoft Access jellemzői Adattípusok 2. Normalizálás

2 4 5 7 9

Normalizálás lépései – 1. példa 9 Normalizálás lépései – 2. példa 12 N:M (több-több) kapcsolat felbontása kapcsolótáblával (fogalmi egyedtípussal) 13 Kapcsolatok létrehozása táblák ismételt hozzáadásával 14 Minta adatbázis szerkezete (táblán belüli kapcsolat) 15 3. Minta adatbázis létrehozása

17

A tábla szerkezet létrehozása Minta adatok felvétele

17 19

4. SQL parancsok csoportosítása, SELECT parancs DDL parancsok DML parancsok SELECT parancs szerkezete SELECT lista elemei, alternatív oszlopnév 5. SELECT parancs Rendezés (ORDER BY) Hiányzó adatelem (NULL érték) problematika Adatválaszték megtekintése (DISTINCT) Sorfeltétel (WHERE) Aritmetikai, logikai és összefűző operátorok 6. SELECT parancs Függvények Csoportképzés (GROUP BY) Csoportfeltétel (HAVING) 7. Összetett lekérdezések Listázás egyszerre több (kapcsolódó) táblából Egymásra épülő lekérdezések Beágyazott lekérdezés (subquery) Kereszttáblás lekérdezések

20 20 21 21 22 23 23 23 24 24 26 27 27 29 32 33 33 33 34 35

8. DDL parancsok CREATE parancs Érvényességi szabályok (CONSTRAINT-ek) Alapértelmezett érték ALTER parancs DROP parancs 9. DML parancsok INSERT INTO parancs UPDATE parancs DELETE FROM parancs (rekord, táblatartalom) törlése Rekordok uniója Táblakészítő lekérdezés 10. Gyakorló feladatsorok 1. gyakorlati ZH-ra felkészítő feladatok 2. gyakorlati ZH-ra felkészítő feladatok

37 37 37 38 39 39 40 40 41 41 42 42 43 43 44

Irodalomjegyzék

45

Mellékletek

46

Előszó A közlekedési rendszerek (szervezetek) és folyamatok megszervezése, valamint célorientált működtetése magas színvonalú információellátást kíván. A közlekedési információs rendszerek célja az információigények kielégítése, melynek hatékonysága az „erőforrásként” kezelt adatbázis-szerkezeteken múlik. Az információnak és az azokat hordozó adatoknak értéke van, és ezért nem közömbös, hogy hogyan bánunk ezzel az értékkel. A közlekedési információs rendszerek legfontosabb „soft” összetevője az adat. A rendszerelemek és folyamatok között az áramló adatok teremtenek kapcsolatot. Az adatbázisok az adatok strukturált rendszere, amely az adatfelvételi és felhasználási folyamatok közötti időkülönbség „áthidalását” is lehetővé teszik (tárolás). Emellett az adatbázis fogalom az utóbbi időben kibővült a feldolgozási folyamatokkal is. Ennek megfelelően az adatbázis-kezeléssel összefüggő ismeretanyag elsősorban a tárolási szerkezet és a feldolgozási műveletek megtervezésére fókuszál. Mivel az adatok leképezik a működtetett rendszer összetevőit és folyamatait, ezért a közlekedési rendszerek felépítésének és működésének precíz ismerete és a megfelelő absztrakciós készség az adatmodellezéshez nélkülözhetetlen. A téma jelentőségét a napjainkban megfigyelhető, hosszabb folyamatként tekinthető ún. információrobbanás fokozza. A kezelt információk mennyiségét alapvetően a közlekedésben résztvevő alapfolyamati összetevők száma, az egyes összetevőkről gyűjtött jellemzők köre és a mintavételezési időköz befolyásolja. Az utóbbi két tényező miatt az utóbbi időszakban jelentősen nőtt a kezelendő adatok mennyisége, miközben a felhasználás során továbbra is kis időtartam áll rendelkezésre a lekérdezések lefuttatására. További kihívás a különböző forrásokból származó, eltérő formátumú, jelentéstartalmú, stb. információk „összefésülése” és „közös kezelése”, ami a közlekedés egész információs rendszerére kiterjedő integráció alapfeltétele [1]. A közlekedési információs rendszerek elemzésével, tervezésével és azokon belül az adatbázistervezéssel foglalkozó tantárgyak oktatása több évtizedes hagyományokra tekint vissza a Budapesti Műszaki és Gazdaságtudományi Egyetem Közlekedésmérnöki és Járműmérnöki Karának Közlekedésüzemi és Közlekedésgazdasági Tanszékén [2]. A relációs adatmodellezés és adatbázis-tervezés ismeretanyaga viszonylag stabilnak tekinthető. Ezt a tudásbázist adjuk át a hallgatóknak a folyamatosan változó szoftverek kínálta környezetben. Tehát az oktatás során elsősorban az időtálló ismeretekre fókuszálunk, miközben az adott szoftver alkalmazását is elsajátítják a hallgatók. A jegyzet összeállítását a tananyag folyamatos frissítése és didaktikai szempontok szerinti strukturálása, valamint az oktatás eredményei, tapasztalatai tették lehetővé [3]. Az adatbázis-kezelés ismeretköre a kifejlődés előzményeivel, alapjaival kezdődik. A fogalmakat és definícióikat, valamint azok egymásra épülését a hallgatók az előadási órákon sajátítják el, amelyet a gyakorlati foglalkozások példáin keresztül mélyítenek el. Terjedelmi korlátok miatt a jegyzet csak a tananyag vázát tartalmazza, így annak tartalma évről-évre változhat. A parancsok kipróbálása, gyakorlati elsajátítása számítógépes laborban történik, ami megkívánja a hallgatóságnak a laborfoglalkozásokon történő intenzív részvételét. A sokrétű anyag könnyebb elsajátításához a jegyzet fejezetei után gyakorlati feladatok szerepelnek. A tananyag végén a legfontosabbnak tartott irodalmi forrásoknak a megjelölését találjuk, amelyekből – elmélyítési szándék esetén – a témához tartozó további részletek megismerhetők. 1

1. Az adatbázis-kezelés alapjai Adatbázis: Felesleges átfedés nélkül együtt tárolt és egymással kapcsolatban lévő adatok együttese, összetett logikai szerkezetű adathalmaz. Korábban az adatbázis csupán a strukturált adathalmazt jelentette, ma már azonban a feldolgozási műveletek egy jelentős része is az adatbázis fogalmához tartozik. Az adatbázis szervezettsége lehetővé teszi a tárolt adatokhoz való hozzáférést és ezek módosítását. Az adatbázisok célja adatok megbízható tárolása, és gyors visszakereshetőségének biztosítása. Az adatbázis fogalma nem keverendő össze az adatbáziskezelővel, amely az adatbázis működtetésére, rendszerszintű és felhasználói folyamatainak szervezésére szolgál. Az adatbázisok tervezése adatmodell készítésével kezdődik. Adatmodell: valóság leképezése adatokra, azok kapcsolataira, felhasználásuk körülményeire, szabályaira. Az adatbázis szerkezetének a megtervezése. Az adatbázissal végzett műveleteket adatbázis-kezelésnek nevezzük, melynek célja, hogy a keletkező, rendelkezésre álló adatokból (amelyek volumene a technika fejlődésével folyamatosan nő) a leghatékonyabban felhasználható információkat nyerjük ki rövid lekérdezési idővel. Az adatok tárolása két ’síkon’ jelenik meg: 

logikai sík - mit tárolunk; milyen adatokat milyen összefüggésekben tárolunk;



fizikai sík - hogyan tároljuk; hogyan érjük el azokat a fizikai háttértáron.

A számítógépes adatfeldolgozás feladatai: 1. az adatbázis-szerkezet megtervezése, elkészítése, (az üres táblák és kapcsolataik definiálása), 2. adatbevitel az üres szerkezetbe (felhasználóbarát legyen), adatok formai és tartalmi ellenőrzése, 3. adatok aktualizálása: - új adat felvétel, - módosítás, - törlés. 4. adatok lekérdezésének megtervezése (ennek a szolgálatába állítjuk az adatmodellt) - egyszerű lekérdezés (pl. keresés, sorba rendezés), - összetett lekérdezés (pl. csoportosítás, feldolgozás).

1.1. Az adatbázis-kezelő rendszerek és szoftverek fejlődése Az adatok gyors, gépesített, tárolásának és visszakeresésének igénye már a múlt század első felében felmerült, amikor az első népesség nyilvántartást végző lyukszalagos számítógépek megjelentek. Az első ún. szekvenciális (’soros’ hozzáférés az adatokhoz a fizikai elhelyezkedés sorrendjében, sok adat felesleges ’érintésével’) fájlokat az 1940-es évek végén alkalmazták. Az első nem szekvenciális (’direkt’ hozzáférés bármelyik adatelemhez címzéssel) hozzáférést biztosító fájlrendszert 1959-ben fejlesztették ki az IBM-nél. Az adatbázisok jelenlegi, korszerűbb formái az 1960-as évek közepén kezdtek el kialakulni, amikor számos új programozási nyelv jelent meg (pl. Fortran, Basic). Nem sokkal ezután megjelentek az első adatbázis-kezelő rendszerek is. Az 1961-es évben dolgozták ki a hálós adatmodell alapjait, 2

majd nem sokkal később kialakult a hierarchikus adatmodell is. A hálós adatmodellben bármely egyed, bármely másik egyedhez kapcsolódhat (többféle kapcsolatban is lehetnek egymással). Például személyek közötti kapcsolatrendszer (N:M kapcsolatok). A kapcsolatrendszer láncolt listákkal (pointerekkel) működik. Felbontják 1:N kapcsolatokra. A hierarchikus adatmodell az egyedek alá-fölérendeltségét veszi alapul, amit ’szülő’-’gyerek’ kapcsolatként ábrázol fa-gráfban (1:N, 1:1 kapcsolatok). Az első hálózatos hozzáférést biztosító ’adatbank’ 1965-ben jelent meg az IBM-nél, és a SABRE nevet kapta. A hagyományos adatbázis-kezelő rendszerek a számmal és betűvel leírható (alfanumerikus) adatok gyűjtésével és feldolgozásával foglalkoztak viszonylag szűk területen. A később kialakuló korszerű adatbázis-kezelő rendszerek már lehetővé teszik a képi, hang, stb. adatállományok tárolását is. Az adatmodellezés célja, hogy az egyedekről egy számítógéppel könnyen feldolgozható adatszerkezetet készítsünk. Az adatmodellek legelterjedtebb típusai: hierarchikus, hálós, relációs, objektumorientált, multidimenziós. A hálós és a hierarchikus adatmodelleket az első kereskedelmi adatbázisrendszerekben használták az 1960-as évek végén és az 1970-es években. A relációs adatmodellek később ezeket kiszorították. A relációs adatmodell az 1980-as évektől kezdve a legelterjedtebb adatmodell. Az adatokat táblázatok soraiban ábrázolják. Ebben a modellben nincsenek előre definiált kapcsolatok az egyes adatelemek között. A kapcsolatok létrehozásához szükséges adatokat többszörösen tárolják. Manapság az adatbázis-kezelés területén is teret hódítanak az új megközelítések, mint pl. az objektumorientáltság. A hálózatok elterjedésével az osztott adatbázis-kezelők szerepe is jelentősen növekszik. A legelterjedtebb adatbázis-kezelő szoftvereket az 1.1. táblázat foglalja össze. 1.1. táblázat A legelterjedtebb adatbázis-kezelők

Nyíltforrású adatbázis-kezelők

Ingyenesen letölthető adatbázis-kezelők

Korlátozás nélkül használható adatbázis-kezelők

Az egyes adatbázis-kezelők esetén a felhasználás korlátozott, de tanulási célra mindegyik megfelelő Microsoft SQL Server 2008 Express Edition Microsoft SQL Server 2008 Management Studio Express

MySQL (Linux/Windows)

PostgreSQL (Linux/Windows)

Oracle Database 11g Express Edition (Linux/Windows) Microsoft Jet Database Engine 3

Ingres Community Edition

Trial: Informix Dynamic Server Enterprise Edition V10.0

SQLite (Linux/Windows)

Mini SQL

3

A Közlekedésmérnöki és Járműmérnöki Karon a korábbi évtizedekben a dBase, Clipper, Oracle9.i, Access 2.0 majd az újabb verziójú Access adatbázis-kezelőket és a hozzájuk tartozó alkalmazásfejlesztési gyakorlatot ismerték meg a hallgatók. Jelenleg az Access adatbáziskezelő a legalkalmasabb oktatási célra a széleskörű hozzáférhetősége miatt. A képzésnek mindig fontos része volt az önálló adatbázis-tervezési, fejlesztési munka; ezért a hallgatók az adatmodellt és az alkalmazást egy általuk választott közlekedési témában készítik el (konzultációs támogatással). (Féléves házi feladat témáinak kiválasztása).

1.2. Az SQL nyelv jellemzői A különböző típusú adatbázis-kezelő rendszerek kialakulása szükségessé tette a szabványos lekérdező nyelv kifejlesztését. Így jött létre az általános lekérdező nyelv, az SQL (Structured Query Language), ami a köznapi beszédhez és gondolkodáshoz hasonló jelkészletet használ; 1986-ban szabványosították. SQL= (parancs)szavak + nyelvtan (a szavak használatára vonatkozó szabályok). Az SQL a relációs adatbázis létrehozására és lekérdezésére, a ’relációkban’ tárolt információk visszanyerésére szolgál. Nem az adatok elérési módját kell megadni, hanem a kívánt információ tulajdonságait. Az SQL nyelvben a következő elemeket különböztetjük meg:  objektumok,  NULL érték,  kifejezések,  műveletek,  utasítások,  szintaktikai elemek,  adatbázisok eljárás jellegű elemei. Az SQL parancsok megadásának formai szabályai: egyezményes jelölésrendszert alkalmaznak (1.2. táblázat). Például:  nagybetű: parancsszavak, logikai műveleti jelek, függvények nevei, stb.  kisbetű: objektumok, oszlopok nevei, stb. 1.2. táblázat SQL parancsok fontosabb formai szabályai

Jelölés

Jelentés

NAGYBETŰ

Kötelező megadás, nem szabad elhagyni és pontosan kell leírni az ilyen jelöléssel megadott szavakat.

Dőlt kisbetű

Felhasználói megadás, a felhasználó által megadott változókat jelenti.

[]

Nem kötelező elemek felsorolása.

…

Azonos elemek ismétlődését jelöli.

<>

Feltétlenül megadott elemeket kell felsorolni.

{}

Ebben az esetben egy vagy több elem közül egyet kötelező felsorolni.

|

Két vagy több lehetőség közüli választást jelöli.

4

Az egyes adatbázis-kezelők eltérő módon könnyítik meg a felhasználói parancsok bevitelét; azaz felismerik a nem formai szabályok szerinti begépelést és a szintaktikai szabályok szerint módosítják azokat. Az adatbázisok létrehozásának célja, hogy minél több információt nyerjünk ki azokból. Ennek megfelelően a legfontosabb parancs a lekérdező SELECT parancs. Nagyméretű, hálózatos adatbázisok esetében különösen fontos a felhasználók/felhasználó csoportok jogosultságainak beállítása (pl. GRANT, REVOKE parancsok) és az általuk végzett műveletek naplózása. Az alapvető SQL parancsokat az 1.3. táblázat foglalja össze. 1.3. táblázat Alapvető SQL parancsok

Adatdefiniáló (DDL) Data Definition Language

objektum létrehozás

CREATE

objektum módosítás

ALTER

objektum megszüntetés

DROP

rekord felvitel Adatkezelő (DML) rekord módosítás Data Manipulation Language rekord törlés Lekérdező (DQL) Data Query Language

lekérdezés

INSERT INTO UPDATE DELETE FROM SELECT

Az SQL nem eljárás orientált nem azt tartalmazza, hogy hogyan, hanem azt hogy mit akarunk megkapni. Szigorú a szintaktika. SQL-t befogadó nyelvek: pl. Pascal, C, Cobol, dBase, Access PL/SQL=Procedural Language - eljárások készítését is lehetővé teszi.     

változók deklarálása, értékadás, feltételes szerkezetek, ciklus, függvények, eljárások definiálása.

1.3. A Microsoft Access jellemzői MS ACCESS= relációs adatbázis-kezelő program, amely felhasználóbarát grafikus felülettel rendelkezik. Az Accessben alkalmazott objektum típusokat és azok kapcsolatait (egymásra épülését) az 1.1. ábra szemlélteti. Az objektumok részletes jellemzése az 1.4. táblázatban található. Számos esetben a felhasználói műveleteket beépített segédalkalmazások, ún. varázslók támogatják. Az Access programnak újabb és újabb verziói jelennek meg (kompatibilitási kérdések), azonban a ’háttérben’ lévő tudásanyag (relációs adatmodell) változatlan. Mire alkalmas az Access? És mire nem?

5

Táblák

Lekérdezések Adatbázis alkalmazás Űrlapok

Jelentések Makrók

Modulok

1.1. ábra Az Access objektumtípusok kapcsolatai 1.4. táblázat Az Access adatbázisok objektumainak jellemzői

Táblák

Lekérdezések

Űrlapok

Jelentések

Makrók

Modulok

 

megjelenésükben hasonlítanak a táblázatokra

  

meglévő táblá(k)ra és/vagy lekérdezés(ek)re épülnek

 

a különböző táblában tárolt adatokat egy táblában meg tudja jeleníteni

 

adatbeviteli és adatmegjelenítő ’képernyő’ - az adatokkal végzett munkához

  

adatmegjelenítés nyomtatásban - az adatok bemutatására/összegzésére szolgál

 

egy egyszerű programozási nyelv

 

segítségével új funkciók adhatók az adatbázishoz

ne legyen benne adat ismétlődés/redundancia (normalizálás)

többféle feladatot láthatnak el két alaptípus: választó (összegyűjti és elérhetővé teszi a kért adatokat) és módosító lekérdezés (valamilyen feladatot/akciót hajt végre az adatokkal)

gyakran szolgálnak rekordforrásként űrlapok és jelentések számára

felületén az műveleteket végrehajtó parancsgombok is lehetnek (menürendszer építhető)

futtatásakor az adatbázis aktuális adatait tükrözi e-mail-en küldhető felület képezhető

eseményvezérlés automatizálását teszik lehetővé; gyakran ismétlődő műveletek egy parancsba foglalása, a műveletek végrehajtása különböző feltételekhez köthető

VBA-Visual Basic nyelven írt saját „programok”, eljárások, függvények, stb.

Új adatbázis létrehozásakor a felkínált mintaadatbázisok használata nem szükséges. Az Accessben egyetlen egy file jön létre, ami hordozható. A jellemző képernyőképet az 1.2. ábra szemlélteti. Az ablak bal oldalán választható ki az objektumtípus. Az adatbázis-objektumok létrehozására általában három lehetőség van: 

Automatikusan: ekkor az Access automatikusan elkészíti az objektum egy előre definiált változatát (ritkán használjuk). 6



Varázsló segítségével: a varázsló-technikával végigvezet a tervezési folyamaton.



Tervező nézetben: teljesen manuális a tervezés, itt a rendszer valamennyi lehetősége elérhető.

1.2. ábra Az Access jellemző képernyőképe

A lekérdezések ’hátterében’ az SQL nyelv található, hiszen minden lekérdezéshez egy SQL utasítás rendelődik, mely a lekérdezés műveleteit definiálja. Az egyszerűbb lekérdezéseket a tervező nézetben ’rács’ segítségével hozzuk létre, míg az összetett lekérdezéseket (bonyolultabb feldolgozási műveleteket) általában az SQL utasítások szerkesztésével (1.3. ábra). Lekérdezések

Egyszerű

Összetett

QBE rács és grafikus lekérdezés-tervező eszközök (feltétel összeállítása vizuálisan)

SQL utasítások szerkesztése (feltétel összeállítása ’programozással’)

1.3. ábra A lekérdezések típusainak létrehozása

1.4. Adattípusok Az Access különböző verziói kb. 10 jellegzetes adattípus tárolását teszik lehetővé (1.5. táblázat). A verzióktól függően lehetnek eltérések. A táblák mezőinek (az egyedek tulajdonságainak) adattípusát a táblatervezésnél (Tervező nézetben) kell beállítani. Az adattípusok beállításával egyúttal az adatérvényesítés legegyszerűbb módját is biztosítjuk, hiszen a felhasználók csak a megfelelő típusú adatokat vihetik be a táblamezőkbe. Keresés varázsló: egy másik táblából választhatunk értéket (idegen kulcs), de mi is megadhatjuk a választható adatok körét. Általában idegen kulcsnál alkalmazzuk, amikor az ’1’ oldalon lévő táblából választunk értéket. 7

1.5. táblázat Az Access adattípusai

Adattípus

Amit tárol

Korlátok, követelmények

Szöveg

Alfanumerikus adatok (szöveg és számok) – fix hosszúságú

Feljegyzés

Alfanumerikus adatok (szöveg és számok) – változó hosszúságú

 max. 255 karakter  beviteli maszk adható meg  keresés, szűrés, sorba rendezés NEM LEHETSÉGES Feljegyzés adattípus esetében

 manuálisan adatbevitelnél max. 65535 karakter

Numerikus adatok (akkor választjuk, ha műveletet akarunk vele végezni; egyébként szöveg típus)

Szám

 mezőméret beállítható 1, 2, 4, 8 és 16 bájtra  bájt (0…255), egész (0…65535), hosszú egész (csak egész számok)

 egyszeres, dupla (tizedesek is lehetnek)  8 bájtos dupla pontosságú egész számként

Dátum/idő

Dátumok és idők megadása (numerikusból származtatott)

Pénznem

Pénzösszeg adatok (numerikusból származtatott)

 8 bájtos számokként, négy tizedes jegy

Számláló

Egyedi értékek, amelyeket az Access tölt fel minden új rekord létrehozásakor (numerikusból származtatott)

 4 bájtos értékeken tárolódik  általában elsődleges kulcs

Igen/Nem

Logikai (Igaz vagy Hamis értékű) adat (numerikusból származtatott)

OLE-objektum

Képek, dokumentumok, rajzok, egyéb objektumok Office és Windows-alapú programokból

Hiperhivatkozás

Webcímek

Melléklet

Bármely támogatott típusú fájl

tárolódik

 beviteli maszk adható meg pontossággal tárolódik

 -1 => Igen  0 => Nem  max. 2 GB adatot tárol  szükség van egy OLE-kiszolgálóra (olyan programra, amely támogatja az adott fájltípust)

 max. 1 GB adatot tárol  internetes, belső és helyi hálózati vagy a saját számítógépen található webhelyekre és fájlokra mutató hivatkozások tárolhatóak

 képeket, számolótáblákat, dokumentumokat, grafikonokat és más támogatott típusú fájlokat csatolhat az adatbázis rekordjaihoz

Az adattárolási egységek két típusa:  egyszerű adattárolási egység: változó, 

összetett adattárolási egység: rekord.

A rekordban az adatok logikailag kapcsolódnak egymáshoz. A rendezőelv maga az egyed, aminek a tulajdonságait tárolja el a rekord. Az egyed lehet személy, tárgy, fogalom (pl. megállóhelyi kiszolgálás). Azok az egyedek tartoznak egy egyedtípusba, amelyekről azonos tulajdonságokat tárolunk.

8

2. Normalizálás Az adatbázis készítés a nyilvántartandó adatok körének meghatározásával kezdődik. Ezt követi a normalizálás (táblázat szétbontó műveletek): amikor ’helyes’ tárolási szerkezetet alakítunk ki. A normalizálás során a nyilvántartandó adatokat egymással kapcsolatban álló táblákba csoportosítjuk úgy, hogy eredményül egy kisebb tárolási igényű és áttekinthetőbb, a normalizálás szabályrendszerének megfelelő adatbázist kapjunk. A normalizálás több lépésből áll, minden lépés eredményeként az adatbázis szerkezete eggyel magasabb szintű normál formula (NF) feltételeit teljesíti. A NF szintek kumulatívak, tehát a magasabb szint eléréséhez az alacsonyabb szintek összes feltételének is teljesülnie kell (szigorodó szabályrendszer). Példákon keresztül mutatjuk be a normalizálás egymásra épülő lépéseit.

2.1 Normalizálás lépései – 1. példa A példában a járművekhez rendelhető kiegészítőket tartjuk nyilván. Egy adatbázis szerkezetének kialakításakor első lépés a szükséges adatigény meghatározása, majd táblázatba rendezése (2.1. táblázat):      

alvázszám, gyártmány, típus, ajtók száma, kiegészítő neve, kiegészítő (típusának) cikkszáma. 2.1. táblázat 0. NF

W0B578547 Opel Astra GTH4724782 Fabia Skoda CCP4672134 Lada CCP4672134 Lada

5 Légzsák öt Légzsák

Samara 3 Féklámpa Samara 3 Féklámpa

10125 10125

Klíma Klíma

98542 32545

Riasztó, 10254, CD-tár 65020

00001 00001

1. NF Az 1. NF feltételei nem teljesülnek, mert:    

az oszlopok száma és sorrendje nem azonos minden sorban, az ajtók száma attribútum nem minden sorban szám típusú értéket vesz fel, egyes attribútumok több értéket is felvesznek, van két egyforma sor (redundancia), így nem lehet elsődleges kulcsot képezni.

Az 1. NF feltételeinek megfelelő táblaszerkezetet a 2.2. táblázat mutatja be. Egy egyednek (rekordnak) egy járműbe épített konkrét kiegészítő felel meg. A sorismétlések miatt az egyértelmű azonosítás (elsődleges kulcs) összetett kulccsal valósítható meg. Például jelen esetben az alvázszám és a kiegészítő cikkszáma egyértelműen azonosítja a sort. Az összetett 9

elsődleges kulcsban a kiegészítő neve nem lenne megfelelő, ugyanis a kiegészítő neve nem határozza meg egyértelműen a cikkszámot, hanem fordítva, a kiegészítő cikkszáma határozza meg annak nevét. Az 1. NF-ban ugyanis feltétel, hogy a másodlagos attribútumok funkcionálisan függjenek az elsődleges kulcstól. Abban az esetben, ha nem tudunk összetett kulcsot meghatározni vagy már túl sok mezőből állna az összetétel, akkor egyedi azonosítót veszünk fel (pl. ID elnevezéssel - számláló típussal). 2.2. táblázat 1. NF

Alvázszám

Gyártmány

W0B578547 W0B578547 GTH4724782 GTH4724782 GTH4724782 GTH4724782 CCP4672134

Opel Opel Skoda Skoda Skoda Skoda Lada

Típus Astra Astra Fabia Fabia Fabia Fabia Samara

Ajtók száma 5 5 5 5 5 5 3

Kiegészítő neve Légzsák Klíma Légzsák Klíma Riasztó CD-tár Féklámpa

Kiegészítő cikkszáma 10125 98542 10125 32545 10254 65020 00001

2. NF A 2. NF feltételei nem teljesülnek, mert: 

van olyan mező, amely az összetett elsődleges kulcsnak csak egy részétől függ (pl.: alvázszám már egyértelműen meghatározza a gyártmányt).

A 2. NF megalkotásához meg kell határozni, hogy az egyes nem-kulcs (másodlagos) attribútumok mely összetevőktől függnek (2.1. ábra).

Alvázszám

Gyártmány Típus Ajtók száma

Kiegészítő cikkszáma

Kiegészítő neve

2.1. ábra Attribútumok egymás közötti funkcionális függése

Az alvázszám attribútum egyértelműen meghatározza a jármű gyártmányát, típusát és az ajtók számát. A 2. NF táblázatszétbontó szabályának megfelelően (minden mező a teljes összetett kulcstól függ, vagy a kulcs egy mezőből áll) külön választott attribútumokat 3 külön táblába rendezzük (2.2. ábra), ahol már az egyedtípusok is azonosíthatók. A táblák közötti kapcsolattípusok alapján meghatározhatók az elsődleges és az idegen kulcsok. Az Autó és Kiegészítő (típus) táblában egy, míg az Egy autó egy kiegészítő típusa táblában két mezőből áll az elsődleges kulcs (ez utóbbi két mező egy-egy idegen kulcs is). A több (N) oldalon lévő táblában (Egy autó egy kiegészítő típusa) a két mező együttesen alkotja az összetett kulcsot; ez a tábla egy ún. kapcsolótábla.

10

1:N

Autó Alvázszám

Gyártmány

W0B578547 Opel GTH4724782 Skoda CCP4672134 Lada

Ajtók száma

Típus Astra Fabia Samara

5 5 3

1:N

Egy autó egy kiegészítő típusa Cikkszám Alvázszám W0B578547 10125 W0B578547 98542 GTH4724782 10125 GTH4724782 32545 GTH4724782 10254 GTH4724782 65020 CCP4672134 00001

Kiegészítő (típus) Cikkszám Név 10125 Légzsák 98542 Klíma 32545 Klíma 10254 Riasztó 65020 CD-tár 00001 Féklámpa jelmagyarázat: elsődleges kulcs idegen kulcs 2.2. ábra 2. NF

Megfigyelhető, hogy az adatelemek száma csökkent: 42 helyett 38, azaz mérsékeltük a redundanciát. (Gyenge logikai redundancia a kapcsolatok felépítéséhez szükséges.) 3. NF A 3. NF feltételei nem teljesülnek, mert: 

funkcionális függés nem csak az elsődleges kulcsból indul ki, azaz a másodlagos attribútumok között is van funkcionális függőség (tranzitív függőség).

A 2. NF Autó táblában az Alvázszámtól valamennyi másodlagos attribútum függ. Azonban a Típus mező is egyértelműen meghatározza a Gyártmányt, azaz két másodlagos attribútum között is van tranzitív függőség. Például, ha több Opel Astra típusú autó szerepelne a táblában, akkor minden egyes alkalommal rögzíteni kell a gyártmányt és a típust is (redundancia). Ezért a 3. NF feltételeit újabb táblázatszétbontással lehet teljesíteni, aminek az eredményét a 2.3. ábra táblaszerkezete szemléltet. A szakirodalomban létezik még 4. és 5. NF is, de rendszerint elég, ha az adatbázis teljesíti a 3. NF feltételeit.

11

1:N

Autó típus Típus Astra Fabia Samara

Gyártmány Opel Skoda Lada

1:N

Autó Alvázszám W0B578547 GTH4724782 CCP4672134

Típus Ajtók száma Astra 5 Fabia 5 Samara 3

1:N

Kiegészítő (típus) Cikkszám Név 10125 Légzsák 98542 Klíma 32545 Klíma 10254 Riasztó 65020 CD-tár 00001 Féklámpa

Egy autó egy kiegészítő típusa Cikkszám Alvázszám W0B578547 10125 W0B578547 98542 GTH4724782 10125 GTH4724782 32545 GTH4724782 10254 GTH4724782 65020 CCP4672134 00001

jelmagyarázat: elsődleges kulcs idegen kulcs 2.3. ábra 3. NF

2.2 Normalizálás lépései – 2. példa A példában számlák legfontosabb adatait tartjuk nyilván. A következő adatelemeket (tulajdonságokat) kívánjuk tárolni:    

SZLASZÁM DÁTUM VEVŐNÉV VEVŐCÍM

    

TERMÉKKÓD TERMÉKNÉV MENNYISÉG EGYSÉGÁR ÖSSZEG (más mezőkből számítható értéket nem tárolunk)



ÖSSZESEN

A normalizálásnak megfelelő ’helyes’ táblaszerkezetet egyszerűsített ábrázolási móddal szemlélteti a 2.4. ábra (táblák és attribútumok nevei, kapcsolatok jelölése a kulcsok megadásával).

12

4. tábla

Vevő

1:N

VEVŐNÉV VEVŐCÍM

1. tábla

1:N

Szla_törzs SZLASZÁM DÁTUM VEVŐNÉV ÖSSZESEN

2. tábla

Eladás

N:1

3. tábla

Termék TERMÉKKÓD TERMÉKNÉV EGYSÉGÁR

SZLASZÁM TERMÉKKÓD MENNYISÉG

jelmagyarázat: elsődleges kulcs idegen kulcs 2.4. ábra 3. NF-ban lévő táblaszerkezet (számlás példa)

2.3. N:M kapcsolatok felbontása – kapcsolótáblával (fogalmi egyedtípussal) Az adatbázis táblaszerkezetének kialakításakor az N:M típusú, vagyis a több-a-többhöz típusú kapcsolatok kerülendők. Az ilyen kapcsolatok egy közbenső harmadik tábla (kapcsolótábla) bevezetésével bonthatók fel. Az új tábla többnyire egy fogalmi egyedtípushoz tartozik. A felbontás műveletét két példán mutatjuk be. Az eredmény: két darab egymásra szimmetrikus 1:N kapcsolat. 1. Példa: kórházi kezelések (orvosok és betegek) nyilvántartása – 2.5. ábra Beteg BetegAz

Beteg BetegAz

Név

Név

N:M

Szül_dátum

N:1

Szül_dátum

Orvos OrvosAz Név

Osztály

Orvos OrvosAz Név

Osztály

1:N Kezelés KezelésAz

BetegAz

OrvosAz

Betegség Dátum

jelmagyarázat: elsődleges kulcs idegen kulcs 2.5. ábra N:M kapcsolatok felbontása (kórházi kezelés példa)

13

2. Példa: online webshop ügyfeleinek és termékeinek nyilvántartása (egy vásárlás során több, egymástól eltérő terméket is megvehet egy ügyfél) – 2.6. ábra Ügyfél ÜgyfélAz

Név

Szül_dátum Cím

Vásárlás VásárlásAz ÜgyfélAz TermékAz

Név

Ár

Termék (típus) TermékAz Név

Ár

N:M

1:N

Ügyfél ÜgyfélAz

Termék (típus) TermékAz Név

Darab

Dátum

Szül_dátum Cím

1:N

N:1

Vásárlás VásárlásAz ÜgyfélAz Dátum

Egy vásárlás egy terméktípusa VásárlásAz TermékAz Darab

1:N jelmagyarázat: elsődleges kulcs idegen kulcs 2.6. ábra N:M kapcsolatok felbontása (online webshop példa)

2.4. Kapcsolatok létrehozása táblák ismételt hozzáadásával A példában foci-mérkőzések legfontosabb adatait tartjuk nyilván. A gólok számával nem végzünk műveleteket. A normalizálásnak megfelelő ’helyes’ táblaszerkezetet egyszerűsített ábrázolási móddal szemlélteti a 2.7. ábra (táblák és attribútumok nevei, kapcsolatok jelölése a kulcsok megadásával). A szerkezet érdekessége, hogy a csapat táblát kétszeresen kapcsoljuk a mérkőzés táblához; úgymint hazai csapat (csapat1) és mint vendég csapat (csapat2); miközben ugyanazon adatokat tároljuk a hazai és a vendégcsapatról is, azaz ténylegesen csak egyetlen adattábla létezik. (A csapatok ’szerepköröket’ is válthatnak, ugyanis egyszer hazai csapatok, máskor vendégek.) Ez a problémakör ún. virtuális tábla hozzáadásával oldható fel, ami az Accessben a tábla ismételt hozzáadásával végezhető el (pl. a lekérdezés tervező rácsa feletti mezőben).

14

1:N

pálya

csapat_1

mérkőzés

PÁLYAAZ PÁLYA_MEGNEVEZÉS CÍM

N:1

PÁLYAAZ CSAPAT1AZ CSAPAT2AZ IDŐPONT EREDMÉNY

CSAPATAZ CSAPAT_MEGNEVEZÉS SZÉKHELY

csapat_2 CSAPATAZ CSAPAT_MEGNEVEZÉS SZÉKHELY

N:1 jelmagyarázat: elsődleges kulcs idegen kulcs

2.7. ábra Kapcsolatok létrehozása táblák ismételt hozzáadásával (foci-mérkőzéses példa)

2.5. Minta adatbázis szerkezete – táblán belüli kapcsolat

EMP

1

DEPT 1

empno

deptno

ename

dname

job

loc

mgr sal comm deptno Jelmagyarázat:

Elsődleges kulcs

Idegen kulcs

2.8. ábra Minta adatbázis szerkezete

A minta adatbázisban egy szervezeten belük az alkalmazottak és a telephelyek adatait tartjuk nyilván két táblában. Az EMP táblában szerepelnek az alkalmazottak adatai (pl.: azonosító, név, munkakör, stb.), míg a DEPT táblában a telephelyek adatai (azonosító, név és helyszín). A 15

két tábla között a telephelyi azonosító teremti meg a kapcsolatot. Az adatbázis EMP táblájában az empno az elsődleges kulcs, de mint idegen kulcs is megjelenik a táblában az mgr mezőben. A DEPT táblában az elsődleges kulcs a telephely azonosító, ami az EMP táblában jelenik meg idegen kulcsként. Az egyes mezőnevek rövid leírását a 2.3. táblázat tartalmazza. 2.3. táblázat Minta adatbázis mezőnevek magyarázata

Mezőnév empno ename job mgr hiredate sal

Magyar megnevezés Alkalmazott azonosító Alkalmazott neve Munkakör Menedzser azonosító Felvétel dátuma Fizetés

comm

Jutalék

deptno dname loc

Telephely azonosító Telephely neve Helyszín

Leírás Minden alkalmazotthoz tartozik egy egyedi azonosító Alkalmazott vezetéknevét tartalmazza Alkalmazott munkakörét (pl.: CLERK) tartalmazza Alkalmazott közvetlen felettesének azonosítója éééé/hh/nn formátumban a felvétel időpontja Havi fizetés (mindenkinél ki van töltve) Nem mindenkinél van kitöltve – NULL érték (akinél ki van töltve, annak is lehet 0, azaz nulla mértékű jutaléka). Minden telephelyhez tartozik egy egyedi azonosító Telephely nevét tartalmazza (pl.: SALES) Telephely helyszínét tartalmazza (pl.: CHICAGO)

A szerkezet érdekessége a táblán belüli kapcsolat az empno és mgr mezők között; azaz egy beosztott mgr mezőjében a főnökének az empno értékét tároljuk el. Ez a szerkezet kifejezetten alkalmas hierarchikus felépítésű szervezetek esetén az alkalmazottak közötti viszonyrendszer leképezésére. Ez a megoldás az eredeti és egy új ’virtuális’ EMP tábla közötti hagyományos kapcsolatnak tekinthető. A virtuális tábla gondolati alkalmazása hasonlatos, mint az előző példában. Egy alkalmazott ugyanis egyidejűleg lehet beosztott is (van főnöke) és főnök is (azaz van beosztottja). Kivételt csak a vezérigazgató (ename: KING, job: PRESIDENT) képez, ugyanis neki nincs főnöke, azaz nála az mgr mező NULL értékű. A többi alkalmazottnál az mgr mező értéke ki van töltve. Ha valakinek nincs beosztottja, azaz a hierarchia legalsó szintjén helyezkedik el, akkor az ő empno-ja nem jelenik meg más alkalmazott mgr mezőjében. Adatbeviteli sorrend: mivel adatbevitelnél mindig az ’1’ oldalon lévő tábla rekordjait töltjük ki először, ezért ebben a szerkezetben a hierarchia magasabb szintjén álló alkalmazottat (főnököt) vesszük fel először a nyilvántartásba (vagy esetleg az mgr mező, mint idegen kulcs kitöltésére később térünk vissza).

16

3. Minta adatbázis létrehozása A 2.5. fejezetben bemutatott minta adatbázist a Microsoft Access szoftverrel hozzuk létre, ami támogatja az SQL nyelv használatát. Egyetemi hallgatók számára az MS Access a szoftver.eik.bme.hu oldalról tölthető le a teljes Office csomag részeként. A minta adatbázis létrehozásához nyissuk meg az Access alkalmazást, válasszuk az „Üres asztali adatbázis” opciót, adjuk meg a fájlnevet valamint az elérési útvonalat. Ezután a program létrehoz egy fájlt, aminek kiterjesztése accdb. Míg az adatbázis meg van nyitva egy laccdb kiterjesztésű fájl is megjelenik azonos névvel, amit a program automatikusan hoz létre. Másolat készítése előtt célszerű bezárni az alkalmazást és csak az accdb kiterjesztésűt átmásolni. Megjegyzendő, hogy a szabványosítás ellenére vannak különbségek az eltérő szoftverek által használt SQL nyelvben. Használat előtt célszerű ellenőrizni, hogy az ANSI 92 szabványnak megfelelő szintaxist használja az alkalmazás. (Fájl -> Beállítások -> Objektumtervezők -> SQL Server-kompatibilis szintaxis (ANSI 92) -> Ebben az adatbázisban: ) Továbbá lehetőség van az Access alkalmazás felületének nyelvét módosítani a Fájl -> Beállítások -> Nyelv menüpontban.

3.1. Táblaszerkezet létrehozása Az üres asztali adatbázis létrehozása után a „Létrehozás” lapon a „Táblatervező” ikonra kattintva hozható létre új tábla (3.1. ábra). Egy táblát akkor érdemes létrehozni, ha már a táblában szereplő összes idegenkulcs szerepel más meglévő táblákban, mint elsődleges kulcs. Sorrendi szabály: kapcsolt tábláknál mindig az ’1’ oldalon lévő táblát hozzuk létre először, majd az ottani elsődleges kulcsot vesszük fel a ’több’ oldalon lévő táblában idegen kulcsként. Az idegen kulcsok adattípusának felvételekor kényelmes megoldás a ’Keresés varázsló’ (Lookup Wizard) használata, amely egyúttal a táblák közötti kapcsolatot is automatikusan beállítja. Ha nem használjuk a ’Keresés varázslót’, akkor manuálisan kell beállítani a kapcsolatokat az ’Adatbázis eszközök’ (Database Tools) ’Kapcsolatok’ (Relationships) menüpont alatt. A sorrendi szabályt adatbevitelkor is érdemes alkalmazni; azaz először az ’1’ oldalon lévő táblába, majd a ’több’ oldalon lévő táblába viszünk be adatokat.

3.1. ábra Új tábla létrehozása és megnyitása tervező nézetben

17

3.1. táblázat Minta adatbázisban szereplő mezők adattípusai

DEPT

EMP

Tábla Mezőnév empno ename job mgr hiredate sal comm deptno deptno dname loc

Adattípus Rövid szöveg Rövid szöveg Rövid szöveg Rövid szöveg Dátum/Idő Szám Szám Rövid szöveg Rövid szöveg Rövid szöveg Rövid szöveg

További beállítások Beviteli maszk: 0000 Kötelező, maximum 30 karakter Maximum 30 karakter Keresés varázslóval Rövid dátum >0 Keresés varázslóval Beviteli maszk: 00 Kötelező, maximum 30 karakter Alternatív fejléc: Helyszín

jelmagyarázat: elsődleges kulcs idegen kulcs

A minta adatbázisban a 3.1. táblázatban felsorolt táblákat és mezőket hozzuk létre az adattípusok beállításával. Elsődleges kulcs beállítása: tervező nézetben ki kell jelölni a megfelelő mezőt vagy mezőket (attól függően, hogy összetett-e a kulcs), és a tervezés lapon az Eszközök csoportban az Elsődleges kulcs gombra kell kattintani. Ugyanígy lehet törölni az elsődleges kulcs megjelölést egy mező sorában. Minden mezőnek a legfontosabb tulajdonsága az adattípus, ugyanis ez határozza meg, hogy milyen adatokat tárolhatnak. A mezők adattípusa további fontos jellemzőket is meghatároz, például:    

a mezők kifejezésekben való használatát, a mezőérték maximális méretét, a mező indexelhetőségét, a mezőnél használható formátumokat.

Az így létrehozott táblaszerkezet kapcsolati ábrája az Adatbáziseszközök lapon a Kapcsolatok ikonra kattintva tekinthető meg (3.2. ábra).

3.2. ábra Minta adatbázis szerkezete Access-ben

Az Access-ben nem kell külön elkészíteni az emp_1 táblát a belső kapcsolat létrehozásához. Az emp_1 mint virtuális tábla jelenik meg az Access programban a kapcsolatok szemléltetéséhez. (Táblák ismételt hozzáadása.) 18

3.2. Minta adatok felvétele Új adatok (rekordok) bevitelét a megfelelő tábla adatlap nézetben való megnyitásával lehet elvégezni. A minta adatbázisban szereplő adatokat a 3.2. és a 3.3. táblázatok tartalmazzák.

3.2. táblázat A DEPT tábla rekordjai

deptno 10 20 30 40

DEPT dname ACCOUNTING RESEARCH SALES OPERATIONS

loc NEW YORK DALLAS CHICAGO BOSTON

3.3. táblázat Az EMP tábla rekordjai

empno

ename

7369 7499 7521 7566 7654 7698 7782 7788 7839 7844

SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER

job CLERK SALESMAN SALESMAN MANAGER SALESMAN MANAGER MANAGER ANALYST PRESIDENT SALESMAN

EMP mgr hiredate 7902 7698 7698 7839 7698 7839 7839 7566 7698

1980. 12. 17. 1981. 02. 20. 1981. 02. 22. 1981. 04. 02. 1981. 09. 28. 1981. 05. 01. 1981. 06. 09. 1987. 04. 19. 1981. 11. 17. 1981. 09. 08.

sal 800 1600 1250 2975 1250 2850 2450 3000 5000 1500

comm deptno 300 500 1400

0

20 30 30 20 30 30 10 20 10 30

Táblanézetben az adatbevitelt megkönnyíti a tabulátor gomb használata, ami mindig a következő mezőre vált, vagy a másolás-beillesztés. Nagy mennyiségű digitalizált adat bevitelére az „importálás fájlból” opció alkalmas, többek között támogatott forrás fájl az xls is. Ha egy új rekordot táblanézetben veszünk fel, akkor az adatbevitel során megjelenik egy ceruza ikon. Az ikon szerkesztés közben végig ott marad, csak azután tűnik el, hogy a rekordot elmentette az alkalmazás. Az ikon gyakorlati haszna, hogy hálózatos üzemben a felhasználó tudja, hogy mikor válik az új rekord a többi felhasználó számára is elérhetővé (miután a ceruza ikon eltűnt).

19

4. SQL parancsok csoportosítása, SELECT parancs Az SQL nyelvben használható parancsok a következő csoportokba sorolhatók:    

DDL (Data Definition Language): az adatbázis szerkezetének definiálásához szükséges parancsok. DML (Data Manipulation Language): adatok kezeléséhez szükséges parancsok. DCL (Data Control Language): az adatbázis hozzáférhetőségének definiálásához szükséges parancsok. TCL (Transaction Control Language): a DML parancsok által végrehajtott módosítások kezelésére alkalmas.

A gyakorlatok során az első két csoportba tartozó DDL és DML parancsokat ismerjük meg. Az Access-ben egy SQL parancsot a következőképpen lehet végrehajtani (4.1. ábra): begépeljük a kifejezést egy lekérdezés SQL nézetébe, majd lefuttatjuk.

Nézet választó

Parancs futtatása

Adatbázis objektumok

SQL parancs helye

4.1. ábra SQL parancsok végrehajtása (futtatása) Access-ben

4.1. DDL parancsok Adat definíciós nyelv, amely tartalmazza az adatbázis struktúra (táblaterületek, táblák, indexek, megszorítások, stb.) létrehozásával és karbantartásával kapcsolatos utasításokat. A legfontosabb DDL parancsok: 

CREATE: adatbázis objektum (pl.: tábla) létrehozására szolgál. Tábla létrehozásakor itt kell definiálni annak szerkezetét figyelembe véve a kapcsolatokat is. 20

 

ALTER: meglévő objektum szerkezetének módosítására szolgál. Például új oszlop adható hozzá egy táblához. DROP: meglévő objektum teljes törléséra szolgál (szerkezet és tartalom is törlésre kerül).

Más adatbázis kezelő alkalmazásokban további DDL parancsok is elérhetőek, mint például a TRUNCATE (minden rekord törlése – ’reset’-elés, azaz ha ezt követően új rekordot viszünk be, akkor a számláló 1-ről indul) vagy a RENAME, de az Access-ben a leggyakrabban használt parancsok az előbb felsoroltak. Mivel az Access különösen felhasználóbarát, ezért számos művelet elvégezhető a DDL parancsok alkalmazása nélkül is egyéb módon.

4.2. DML parancsok A DML parancsok az adatbázisban tárolt és tárolandó adatok kezeléséhez szükségesek. A DDL parancsok segítségével létrehozott adatbázis szerkezetet a DML parancsok töltik fel adatokkal, illetve ezek alkalmasak az adattartalom módosítására is. A legfontosabb DML parancsok:    

INSERT INTO: új adat (rekord) felvétele. UPDATE: meglévő adat (rekord vagy rekordok) módosítása. DELETE FROM: rekord vagy rekordok törlése. SELECT: adatok kilistázása (lekérdezés nyers vagy feldolgozott formában).

4.3. SELECT parancs szerkezete A SELECT parancs segítségével egy vagy több táblában tárolt rekordokat lehet megjeleníteni vagy csoportokat lehet képezni. A SELECT parancs szerkezete: SELECT [DISTINCT] select-lista [FROM WHERE <sorfeltétel> GROUP BY HAVING ORDER BY ]; A SELECT parancson belül a parancsszavak sorrendje minden esetben kötött, azonban csak a SELECT elengedhetetlen a parancs sikeres végrehajtásához. A parancsszavak alkalmazásának magyarázata:  SELECT: kötelező, meghatározza a select-lista tartalmát (oszlopnevek, kifejezések).  FROM: utána szerepelnek az adatforrásként szolgáló táblák, lekérdezések nevei.  WHERE: a lekérdezés eredményül azokat a rekordokat adja, amelyek megfelelnek a WHERE után szereplő sorfeltételeknek. Több sorfeltételt logikai operátorral összekötve lehet megadni.  GROUP BY: a lekérdezés sorfeltételeinek megfelelő rekordokat (vagy ha nincs feltétel, akkor a tábla összes rekordját) csoportokba rendezi a GROUP BY után szereplő csoportszempontok szerint. Azok az egyedek (rekordok) kerülnek egy csoportba, amelyeknél a csoportképző oszlop tartalma megegyezik.

21





HAVING: csak akkor szerepelhet, ha alkalmazunk GROUP BY kifejezést is. Csak azokat a csoportokat adja eredményül a lekérdezés, amelyek megfelelnek a HAVING után következő csoportfeltételeknek. Több csoportfeltételt logikai operátorral összekötve lehet megadni. ORDER BY: a lekérdezés eredményeit (rekordokat, csoportokat) az ORDER BY után szereplő sorrend-szempontok szerint, a rendezési irány szerinti sorrendben jeleníti meg.

4.4. SELECT lista elemei, alternatív oszlopnév A select-listában szerepelhet:      

*: minden oszlopot megjelenít. oszloplista: oszlop1, oszlop2, … számított kifejezés: 3*oszlop1 (feltéve ha oszlop1 adattípusa szám), 47/3, stb. karakteres konstans: például: „Ft”. Vigyázat: SQL kódban mindkét idézőjelnek felül kell lennie. konkatenáció: például: oszlop1&oszlop2. A művelet két oszlopot egyesít, összeolvaszt. alternatív oszlopnév: oszlop1 as [alternatív oszlopnév]. Az alternatív oszlopnév csak a lekérdezés eredményében fog megjelenni.

Példák:  SELECT * FROM EMP;  SELECT empno, ename FROM EMP;  SELECT 6*30;  SELECT 6*sal FROM EMP;  SELECT „TOM SELLECK”;  SELECT empno & „Azonosító” FROM EMP;  SELECT empno as [Azonosító] FROM EMP;

22

5. SELECT parancs SELECT parancs szerkezete: SELECT [DISTINCT] <select-lista> [FROM WHERE <sorfeltétel> GROUP BY HAVING ORDER BY ];

5.1. Rendezés (ORDER BY) ORDER BY: a listába kigyűjtött rekordok/csoportok (az eredményhalmaz) a megadott mező, vagy kifejezés szerint növekvő/csökkenő sorrendbe rendezése. Alapértelmezés a növekvő sorrend.   

növekvő vagy abc szerinti sorrend: ORDER BY oszlop1 [asc]; csökkenő vagy fordított abc szerinti sorrend: ORDER BY oszlop1 desc; vegyes: ORDER BY oszlop1 desc, oszlop2;

Egyszerre több szempont is szerepelhet az order by után. Ilyenkor a második és az azt követő szempontok akkor kerülnek figyelembe vételre, ha a megelőző szempont(ok) szerint azonos értékek fordulnak elő. A rendezés alapjául szolgáló kifejezésnek nem muszáj szerepelnie a select-listában.

5.2. Hiányzó adatelem (NULL érték) problematika NULL érték problematika: ha egy mező nincs kitöltve, akkor az a mező null értékű. Információtartalma: bizonytalan (nem ismert). A NULL értékű mezőt tartalmazó műveleteknek az eredménye is lehet NULL értékű, ami problémákat okozhat. Például egy összegnek akkor is NULL az eredménye, ha a tagok között legalább egy NULL értékű elem van. Használható eredmények érdekében a NULL értéket helyettesíteni lehet az Iif beépített függvény segítségével. Az Iif függvény szerkezete: Iif(kifejezés; ha igaz; ha hamis) Például a bizonytalan NULL értéket átalakíthatjuk számításoknál használható biztos 0 értékké. Az átalakítás azonban információtorzulást okoz: Iif(comm is NULL; 0; comm)

23

5.3. Adatválaszték megtekintése (DISTINCT) DISTINCT: az ismétlődő sorokat csak egyszer jeleníti meg. Például:

SELECT DISTINCT job FROM emp;

SELECT job FROM emp; CLERK

ANALYST

SALESMAN

CLERK

SALESMAN

MANAGER

MANAGER

PRESIDENT

SALESMAN

SALESMAN

MANAGER MANAGER ANALYST PRESIDENT SALESMAN CLERK CLERK ANALYST CLERK

5.4. Sorfeltétel (WHERE) Sorfeltétel: a forrásul szolgáló tábla vagy lekérdezés megjelenítendő sorainak szűrésére szolgál. WHERE utáni elemek és sorrendjük (egyszerű feltételnél): I. kifejezés (mit hasonlítunk?) II. összehasonlító operátor (milyen jellegű az összehasonlítás?) III. kifejezés, érték, értéklista, intervallum, séma (mihez hasonlítunk?) A WHERE után következő sorfeltételben használható összehasonlító operátorokat az 5.1. táblázat tartalmazza. Az operátorok mindkét oldalán azonos adattípusú értékeknek kell szerepelniük.

24

5.1. táblázat Összehasonlító operátorok

Operátor

Leírás Egyenlő, karakter típusú adattal is használható (a bal és jobb oldalon

=

szereplő kifejezéseknek teljesen meg kell egyezniük - kis és nagybetű fontos!)

<> < > >= <= [NOT] BETWEEN [NOT] IN [NOT] ALIKE =[]

Nem egyenlő, karakter típusú adattal is használható Kisebb, mint Nagyobb, mint Nagyobb vagy egyenlő, mint Kisebb vagy egyenlő, mint Két érték közt van, egyenlőség megengedett (zárt intervallum) A listában szereplő egyik értékkel megegyezik Egy részlete megegyezik vele (illeszkedik a sablonhoz)* Paraméteres lekérdezés

*: Az SQL ANSI92 nyelvben csak az ALIKE operátor használható. Alapértelmezésként az Access az SQL ANSI89 nyelvet használja, ami a LIKE és ALIKE operátort is felismeri, azonban a helyettesítő karakterek eltérőek.

Az ALIKE operátor esetén használt helyettesítő karaktereket az 5.2. táblázat tartalmazza. 5.2. táblázat Helyettesítő karakterek

ANSI89 ? vagy _ # * []

ANSI92 _ [1-9] % []

[!]

[!]

Egyezés a helyettesítő karakterrel Egy tetszőleges karakter Egy tetszőleges számjegy Tetszőleges számú (akár 0) karakter Bármely karakter, ami a megadott tartományon belül van. Pl.:[a-m] vagy [1-5] vagy [abcdef] Bármely karakter, ami a megadott tartományon kívül esik. Pl.: [!a-m]

A dátum adattípust az Access számként tárolja, ami a bázis dátum (1899. december 30. 0:00) óta eltelt napok számának felel meg. Például 1899. december 31. 12:00 az 1,5-nek felel meg és fordítva. Ennek megfelelően feltételben dátum adattípust két féleképpen is össze lehet hasonlítani konstanssal: vagy a bázis dátum óta eltelt napok számát megadva, vagy a dátumot #ISO dátum jelölés# formátumban kiírva: #nn-hh-éééé# (területi beállításoktól függetlenül ez a dátum formátum mindig működik). Példák:         

SELECT ename FROM emp WHERE sal=1250; SELECT ename FROM emp WHERE ename=’CLARK’; SELECT ename FROM emp WHERE sal>comm; SELECT ename FROM emp WHERE sal NOT BETWEEN 500 and comm; SELECT ename FROM emp WHERE empno IN(’7900’,’1234’); SELECT ename FROM emp WHERE empno ALIKE’7900’ OR empno ALIKE’1234’; SELECT ename FROM emp WHERE empno ALIKE’_[1-5]0%’; SELECT ename FROM emp WHERE empno=[Azonosító száma?]; SELECT ename, hiredate from emp where hiredate IN(#28-09-1981#,29637);

25

5.5. Aritmetikai, logikai és összefűző operátorok Az aritmetikai operátorokat az 5.3. táblázat tartalmazza. 5.3. táblázat Aritmetikai operátorok

Operátor + * / \ ^ MOD

Leírás Összeadás, 5+2=7 Kivonás, 5-2=3 Szorzás, 5*2=10 Osztás, 5/2=2,5 Osztás, ami az eredmény egész értékét adja eredményül, 5\2=2 Hatványozás, 5^2=25 Osztás maradékát adja eredményül: 5 MOD 2 =1

Több feltételt logikai operátorral összekapcsolva lehet megadni (összetett feltételek képzése). A logikai operátorokat az 5.4. táblázat tartalmazza.

5.4. táblázat Logikai operátorok

Bemenetek A B 0 0 0 1 1 0 1 1 0 NULL 1 NULL NULL 0 NULL 1 NULL NULL

Logikai operátorok kimenetei AND OR EQV IMP NOT(A) 0 0 1 1 1 0 1 0 1 1 0 1 0 0 0 1 1 1 1 0 0 NULL NULL NULL 1 NULL 1 NULL NULL NULL 0 NULL NULL NULL NULL NULL 1 NULL NULL 1 NULL NULL NULL NULL NULL

XOR 0 1 1 0 1 0 NULL NULL NULL

Az összefűző operátorokat az 5.5. táblázat tartalmazza.

5.5. táblázat Összefűző operátorok

Operátor Leírás + Két szöveg adattípusú karakterlánc egyesítése egy karakterlánccá a NULL érték propagálásával: ha az egyik karakterlánc NULL értékű, a kimenet is az. & Két karakterlánc egyesítése egy karakterlánccá.

26

6. SELECT parancs SELECT parancs szerkezete: SELECT [DISTINCT] <select-lista> [FROM WHERE <sorfeltétel> GROUP BY HAVING ORDER BY ];

6.1. Függvények A Microsoft Access beépített függvényei műveletet hajtanak végre, az adatok átalakítására és feldolgozására alkalmasak. A függvényeket a bemeneti rekordok száma szerint két csoportra bonthatjuk (6.1. ábra).

Függvény Csoportfüggvény

Sorfüggvény (1 rekord)

(több rekord)

6.1. ábra Függvények csoportosítása a bemeneti rekordok száma szerint

A függvények jellemzői:  a bemenet és kimenet adattípusa eltérhet, 

a függvények egymásba ágyazhatóak.

A sorfüggvények fontosabb jellemzői az alábbiak:  minden esetben egy argumentum (oszlopnév, konstans, kifejezés) a bemenet, 

minden rekordhoz egy visszaadott érték tartozik,



a select-listában, a WHERE és az ORDER BY részekben alkalmazhatóak.

A sorfüggvényeket a bemenet és kimenet adattípusa szerint további alcsoportokra bonthatjuk, melyek közül a fontosabbakat mutatjuk be. A gyakran használt beépített sorfüggvényeket az 6.1. táblázat tartalmazza. (A felhasználó is definiálhat függvényeket.) Bizonyos beépített függvények több csoportba is tartoznak, így például a Day függvény az átalakítás és a dátum/idő függvény csoportban is megtalálható.

27

6.1. táblázat Gyakran használt beépített sorfüggvények az Access-ben Csoport

Függvény CDate(szám) Day(dátum)

Matematikai

Dátum/idő

Weekday(dátum, [hét első napja])

Date() Time() Now() Weekdayname(szám, [rövidítés], [hét első napja]);

Abs(szám) Int(szám) vagy Fix(szám) Kerek(szám, [pontosság]) (Round(…)) Sqr(szám) LCase(karakterlánc) UCase(karakterlánc)

Szöveg

StrConv(szöveg, n)

Len(karakterlánc)

Leírás Egy szám típusú értéket dátum értékké konvertál. Egy egész számot ad vissza, ami a hónap valamely napjának felel meg (1..31). Egy egész számot ad vissza, amely a hét valamely napját jelöli (1..7); hét első napja: 1, ha vasárnap; 2, ha hétfő stb. Alapértelmezés szerint 1. A rendszerdátumot adja eredményül. A rendszeridőt adja eredményül. A rendszerdátumot és -időt adja eredményül. A hét napjának nevét adja eredményül.  szám: hét napjának száma 1..7  rövidítés: o 0: az eredmény a teljes név o -1: az eredmény a rövidített név  hét első napja: lásd weekday fv. A szám abszolút értékét adja eredményül. Egy szám egészrészét adja eredményül Egy számot a megadott számú tizedeshelyre kerekítve ad vissza Egy szám négyzetgyökét adja eredményül. Kisbetűssé alakított szöveget ad eredményül. Nagybetűssé alakított szöveget ad eredményül. Ha n:  1: nagybetűssé alakított szöveget ad eredményül,  2: kisbetűssé alakított szöveget ad eredményül,  3: a szavak első betűjét nagybetűssé, a többit kisbetűssé alakítja A szöveg karakterszámát adja eredményül.

Megjegyzés: az 5.2. fejezetben bemutatott Iif függvény a programfolyamat csoportban található. Példák:  SELECT CDate(40235); 

SELECT Weekday(hiredate, 2) FROM EMP; 28



SELECT Date();



SELECT Round(Sqr(13), 2);

A csoportfüggvények (vagy más néven összesítőfüggvények) fontosabb jellemzői az alábbiak:  a függvény változója az egy csoportba sorolt rekordok megjelölt oszlopában található értékek, csoportonként egy értéket ad eredményül, 

a null értéket tartalmazó sorokat figyelmen kívül hagyják,



a select-listában, a HAVING és az ORDER BY részekben alkalmazhatóak.

A csoportfüggvényeket az 6.2. táblázat tartalmazza. 6.2. táblázat Csoportfüggvények az Access-ben

Függvény Sum() Avg() Min() Max() Count() StDev() Var() First() Last()

Leírás A csoportban szereplő értékek összegét adja eredményül. A csoportban szereplő értékek átlagát adja eredményül. A csoport legkisebb értékű elemét adja eredményül. A csoport legnagyobb értékű elemét adja eredményül. A csoportban szereplő értékek számát adja eredményül. (A csoportba tartozó rekordok száma is ezzel a függvénnyel képezhető.) A csoportban szereplő értékek szórását adja eredményül. A csoportban szereplő értékek szórásnégyzetét adja eredményül. A csoportban szereplő elemek közül az elsőt adja eredményül. A csoportban szereplő elemek közül az utolsót adja eredményül.

Nem kötelező GROUP BY kifejezéssel csoportot képezni, annak érdekében, hogy lehessen használni a csoport függvényeket. GROUP BY nélkül a tábla teljes tartalmára vonatkozik a művelet (az objektum valamennyi rekordja egyetlen csoportot alkot). Példa: 

SELECT Avg(sal) FROM EMP;

6.2. Csoportképzés (GROUP BY) Lehetőség van az egyes rekordokat csoportokba sorolni. A csoportképzés alapját jelentő oszlopnevet vagy neveket a GROUP BY kifejezés után adjuk meg. Azok a rekordok fognak egy csoportba kerülni, amelyeknél megegyezik a csoportképző oszlop tartalma. Olyan oszlopot érdemes csoportképzőnek választani, amelyekben van ismétlődő érték. A csoportképző oszlopot célszerű felvenni a select-listába. A select-listában csak csoportra jellemző érték szerepelhet (csoportképző oszlopok és/vagy csoportfüggvénnyel képzett kifejezés). Ha a csoportképzés szempontjaként több oszlopnevet is megadunk, akkor a lekérdezés a csoportosításkor balról jobbra haladva veszi figyelembe az oszlopneveket. A csoportképző szempontok eltérő sorrendje esetén a csoportosítás értelme is eltérő. Ha több csoportképző szempontot (oszlopot) egyidejűleg alkalmazunk, akkor azok lehetnek egymásba ágyazódóak, vagy függetlenek.

29

GROUP BY , , …, A 6.2. ábra példa az egymásba ágyazódó csoportképzésre. (Pl. alkalmazottak csoportosítása lakóhelyük szerint; az alkalmazottak, mint egyedek feltüntetésétől eltekintettünk).

Régió 1

Régió 2

Megye 1 Város1

Megye 3

Megye 5

Város2 Város6 Város5

Megye 2

Megye 4 Város4

Város3

6.2. ábra Régió-megye-város csoportképzés

A 6.3. ábra példát mutat a független szempontok (deptno, job) szerinti csoportképzésre. A csoportképző szempontok sorrendje az eredménylistán belüli sorrendet befolyásolja (6.3. táblázat). (A személyeket, mint egyedeket az azonosítószámmal jelölt körök mutatják). 6.3. táblázat Eltérő csoportképzési sorrendek eredménylistái

GROUP BY deptno, job deptno job Count 10 CLERK 1 10 MANAGER 1 10 PRESIDENT 1 20 ANALYST 2 20 CLERK 2 20 MANAGER 1 30 CLERK 1 30 MANAGER 1 30 SALESMAN 4

GROUP BY job, deptno job deptno Count ANALYST 20 2 CLERK 10 1 CLERK 20 2 CLERK 30 1 MANAGER 10 1 MANAGER 20 1 MANAGER 30 1 PRESIDENT 10 1 SALESMAN 30 4

Példa: mekkora az egyes vezetők beosztottjainak átlagfizetése? 

SELECT mgr, Avg(sal) FROM EMP GROUP BY mgr;

30

10

1. csoportosítás GROUP BY

deptno, job

CLERK 7934

20

MANAGER

PRESIDENT

ANALYST

7566

7788

7839

MANAGER

7902

CLERK

7782

7369

7876

30 CLERK

SALESMAN

7900

7499

7521

MANAGER

7844

7654

7698

ANALYST

2. csoportosítás GROUP BY

job, deptno

20 7788

CLERK

10 7902

20

7934 7369

30 MANAGER

7900

10 7782

30

PRESIDENT

20 7566

10 7839

7698

SALESMAN

30 7499

7521

7844

7654

6.3. ábra Csoportképzés telephely és beosztás szerint

31

7876

6.3. Csoportfeltétel (HAVING) A csoportképzés során keletkezett csoportokat egyszerű vagy összetett csoportfeltétel megadásával lehet szűrni. A csoportfeltétel szerkezete megegyezik a sorfeltételével, azzal a különbséggel, hogy a feltétel bal oldalán csak csoportra-jellemző érték szerepelhet. (Alternatív oszlopnév nem használható a feltételben.) A csoportfeltételt a HAVING után lehet megadni. Példa: mekkora a legnagyobb fizetés az egyes vezetők beosztottjainak a csoportjában, ahol a vezető azonosítója nem egyezik meg a 7452 és a 7899 azonosítószámokkal? 

SELECT Max(sal) FROM EMP GROUP BY mgr HAVING mgr<>7452 AND mgr<>7899;

A csoportfeltétel abban különbözik, a sorfeltételtől, hogy az eredménylistából pl. teljes csoportok zárhatóak ki, míg a sorfeltétellel csak egyes rekordok. A sorfeltétel és csoportfeltétel közötti különbséget a 6.4. ábra mutatja be.

10

20

CLERK 7934

MANAGER

PRESIDENT

7566

Csoportfeltétel HAVING job!= ANALYST

ANALYST 7788

7839

MANAGER

7902

CLERK

7782

7369

7876

30 CLERK

SALESMAN

7900

7499

7521

MANAGER

7844

7654

Sorfeltétel WHERE empno!=7521

7698

6.4. ábra Különbség sor- és csoportfeltétel között

A sorfeltétel és a csoportfeltétel egyidejűleg is alkalmazható. Ilyenkor a sorfeltétellel kiválogatott rekordokon működik a csoportképzés, aminek az eredménye szűrhető a csoportfeltétellel.

32

7. Összetett lekérdezések Az eddig megismert egyszerű lekérdezések egy táblára épültek. Azonban épülhetnek már létező másik lekérdezésre is, vagy egyszerre több objektumra is (táblákra, lekérdezésekre vegyesen). A lekérdezések egymásra is „halmozhatók”, azaz több szinten is építhetjük azokat egymásra.

7.1. Listázás egyszerre több (kapcsolódó) táblából Az SQL nyelvben a SELECT parancs select-listájában szereplő oszlopnevek több különböző táblából is származhatnak. Ebben az esetben a FROM után minden érintett tábla nevét fel kell sorolni. Ha több táblából listázunk adatokat, akkor az alábbiakra kell figyelni: 

Ha a forrás táblákban szereplő oszlopok neve megegyezik, a select-listában jelezni kell, hogy melyik tábla adatait kívánjuk megjeleníteni.



Ha a forrás táblák között kapcsolat van, azt a SELECT parancsban meg kell adni sorfeltétel segítségével (WHERE után a kapcsoló mezők egyenlőségét előírva feltételként), különben a rekordok összes permutációját adja eredményül a lekérdezés.

Példa: 

SELECT ename, job, emp.deptno, loc FROM EMP, DEPT WHERE emp.deptno=dept.deptno;

7.2. Egymásra épülő lekérdezések Miután elmentettünk egy lekérdezést, az a további feldolgozásokban úgy használható, mintha tábla lenne. Egymásra épülő lekérdezéseknek hívjuk azokat a lekérdezéseket, amelyek közül egyik lekérdezés select-listájában szereplő oszlopnevek között megtalálható olyan, ami egy másik lekérdezésből származik. Egymásra épülő lekérdezéseket abban az esetben érdemes használni, ha a SELECT parancsban nem egy konstanst, hanem az adattábla rekordjainak tartalmától függő értéket kívánunk megadni. Ez az ún. al-lekérdezéssel meghatározott érték szerepelhet   

a select-listában, sorfeltétel jobb oldalán, csoportfeltétel jobb oldalán.

Az al-lekérdezés visszaadhat egyetlen értéket, több (véges számú) értéket vagy értékpár (értékhármas…) listákat is. Ha az al-lekérdezést feltételben használjuk, akkor a feltételben lévő oszlopok száma és az alkalmazott operátor az al-lekérdezés által visszaadott értékhalmaznak megfelelően módosul. Első lépésben mindig az al-lekérdezést készítjük el; majd erre építjük az ún. külső lekérdezést. Egy külső lekérdezés egyidejűleg több al-lekérdezést is magában foglalhat.

33

Abban az esetben, ha egy lekérdezés alapját több másik lekérdezés alkotja, a számítás időigénye megnőhet. Ha az alapot jelentő lekérdezések értékei állandóak, érdemes azokat egy táblában rögzíteni, így csökkentve a számítási időigényt. Példa: kinek a legnagyobb a fizetése? Ehhez először meghatározzuk, hogy mekkora a legmagasabb fizetés értéke, majd egy másik (külső) lekérdezésben az alkalmazottak fizetés értékeit összehasonlítjuk az al-lekérdezésben meghatározott legnagyobb fizetés értékével. 1. SELECT max(sal) as max_fizetes FROM EMP; 2. A lekérdezést elmentjük Lekérdezés1 néven. 3. SELECT ename, sal FROM EMP, Lekérdezés1 WHERE sal=max_fizetes;

7.3. Beágyazott lekérdezés (subquery) Az egymásra épülő lekérdezések mellett a beágyazott lekérdezés is lehetőséget ad arra, hogy a SELECT parancsban nem konstans, hanem az adattábla értékeitől függő értéket vegyünk figyelembe. Ebben az esetben a számított értéket vagy értékeket egy zárójelek közé tett SELECT paranccsal lehet megadni. Ez a beágyazott lekérdezéssel meghatározott érték (az egymásra épülő lekérdezésekhez hasonlóan) szerepelhet  a select-listában: feltéve hogy a beágyazott lekérdezés eredménye egy érték,  sorfeltétel jobb oldalán,  csoportfeltétel jobb oldalán. A beágyazott lekérdezés előnye az egymásra épülő lekérdezésekhez képest, hogy egy SQL parancson belül definiálható a teljes lekérdezés. Az egyszerre több értéket eredményül adó beágyazott lekérdezésekhez használható operátorok listáját a 7.1. táblázat tartalmazza. 7.1. táblázat Beágyazott lekérdezéssel használható operátorok

Operátor ANY vagy SOME

ALL

[NOT] IN [NOT] EXISTS

Leírás Sor- vagy csoportfeltételben. A lekérdezés eredménye azon rekordok, melyek a beágyazott lekérdezés eredménylistájának bármely értékével összehasonlítva eleget tesz az összehasonlító feltételnek. Sor- vagy csoportfeltételben. A lekérdezés eredménye azon rekordok, melyek a beágyazott lekérdezés eredménylistájának összes értékével összehasonlítva eleget tesz az összehasonlító feltételnek. Sor- vagy csoportfeltételben. A lekérdezés eredménye azon rekordok, melyek a beágyazott lekérdezés eredménylistájának bármely értékével azonosságot mutat. A beágyazott lekérdezés eredmény igaz, ha a beágyazott lekérdezés eredménylistája nem üres, különben hamis.

34

Példák: 1. Kinek a legnagyobb a fizetése? SELECT ename FROM EMP WHERE sal=(SELECT max(sal) FROM EMP);

2. Kiknek nagyobb a fizetése, mint a legalacsonyabb fizetésű SALESMAN? SELECT ename FROM EMP WHERE sal> ANY(SELECT sal FROM EMP WHERE job=’SALESMAN’);

7.4. Kereszttáblás lekérdezések Az eddig bemutatott lekérdezések egymás alatti sorokban listázták ki az eredményeket. Az Access-ben lehetőség van az eredményeket mátrix formában is megjeleníteni, amikor a mátrix sorainak és oszlopainak száma a forrástábla értékkészletétől függ. Ezeket hívjuk kereszttáblás lekérdezéseknek. Kereszttáblás lekérdezéseket akkor lehet készíteni, ha két (egymástól független) csoportképző szempont (oszlop) szerint jelenítjük meg az eredményeket. Ebből következik, hogy a mátrix elemei csak valamely csoportképző függvény eredményei lehetnek. A kereszttáblás lekérdezés előnye, hogy az összesített adatok könnyebben értelmezhető formában jelennek meg. A különbséget a 7.2. és 7.3. táblázat mutatja be. 7.2. táblázat Hagyományos lekérdezés kettős csoportképzéssel

job ANALYST CLERK CLERK CLERK MANAGER MANAGER MANAGER PRESIDENT SALESMAN

GROUP BY job, deptno deptno Count 20 2 10 1 20 2 30 1 10 1 20 1 30 1 10 1 30 4 7.3. táblázat Kereszttáblás lekérdezés

ANALYST 10 20 30

2

CLERK 1 2 1

MANAGER 1 1 1 35

PRESIDENT 1

SALESMAN

4

A kereszttáblás lekérdezés SQL parancsának szerkezete: TRANSFORM csoportfüggvény SELECT sorfejléc, [csoportfüggvény] FROM tábla1 [WHERE sorfelétel] GROUP BY sorfejléc PIVOT oszlopfejléc [csoportképzés feltétele/módja]; Ahol a: 

TRANSFORM parancsszó után lehet megadni azt a csoportfüggvényt, aminek az eredménye a mátrix celláiban fog megjelenni,  SELECT parancsszó után szerepel a sorfejlécben szereplő csoportképzési szempont, ezután a csoportfüggvény ismételt megadásával van lehetőség a sorösszegeket tartalmazó oszlopot hozzáadni az eredmény mátrixhoz,  FROM parancsszó után a select-lista elemeinek forrását szükséges megadni,  WHERE parancsszó után megjelenítendő eredményhalmazra vonatkozó sorfeltételt lehet megadni,  GROUP BY parancsszó után a sorfejlécben szereplő csoportképzési szempont szerepel,  PIVOT parancsszó után az oszlopfejlécben szereplő csoportképzési szempont szerepel, ami után opcionálisan az oszlopfejlécre vonatkozó csoportképzési feltételt vagy módot lehet megadni. Példa: jelenítse meg kereszttáblás lekérdezésben az ugyanazon főnökkel rendelkező és ugyanazon beosztású alkalmazottak számát, azon beosztottak esetén, akiknek a beosztása ANALYST, CLERK, MANAGER vagy SALESMAN. TRANSFORM Count(empno) SELECT mgr, Count(empno) FROM EMP GROUP BY mgr PIVOT job IN(’ANALYST’, ’CLERK’, ’MANAGER’, ’SALESMAN’); Az eredményt a 7.4. táblázat mutatja be. Megjegyzés: megjelent egy új oszlop, ami a sorösszegeket tartalmazza (Expr1003), ami a select listában szereplő Count(empno) kifejezés eredménye. 7.4. táblázat Kereszttáblás lekérdezés a minta adatbázisban

mgr 7566 7698 7782 7788 7839 7902

Expr1003 ANALYST 2 2 5 1 1 3 1

CLERK

MANAGER SALESMAN

1 1 1

4

3 1

36

8. DDL parancsok Adat definíciós parancsok, ide tartoznak az adatbázis struktúra (táblák, érvényességi szabályok, stb.) létrehozásával és karbantartásával kapcsolatos utasítások.

8.1. CREATE parancs A CREATE parancs alkalmazásával lehet létrehozni adatbázis objektumokat (pl. tábla, eljárások). Tábla létrehozása: A CREATE parancs általános szerkezete tábla definiálásakor: CREATE TABLE tábla1 ( oszlop1 adattípus [constraint lista DEFAULT érték], oszlop2 adattípus [constraint lista DEFAULT érték], oszlopn adattípus [constraint lista DEFAULT érték], [constraint lista] ); Az adattípusokat a 8.1. táblázat tartalmazza. 8.1. táblázat Access SQL-ben használható adattípusok

Adattípus autoincrement identity(x,y) byte single number integer currency char(n) text datetime yesno longbinary

Leírás számláló, 1-től kezd számláló: első értéke x, y-al nő egész, 0-255 lebegőpontos szám, maximum 7 számjegy lebegőpontos szám, maximum 15 számjegy hosszú egész, -2 147 483 648-tól +2 147 483 647-ig pénznem rövid szöveg, n: maximum karakterszám hosszú szöveg dátum igen/nem OLE objektum, pl.: excel fájl csatolható egy rekordhoz

8.2. Érvényességi szabályok (CONSTRAINT-ek) Tábla definiálásakor lehetőség van constraint-ek, azaz érvényességi szabályok megadására. Az érvényességi szabályok minden egyes adatbevitelnél/módosításnál ellenőrzésre kerülnek. Azok az adatelemek írhatók be, amelyek teljesítik a szabályt (feltételt). Ha az érvényességi szabálynak nevet adunk, akkor az adatbevitelkor név szerint megadja a feltételi hibát. Az érvényességi szabályoknak két alapvető típusa létezik:  

mezőérvényességi szabály: a feltételben az adott oszlop neve szerepel. rekord- vagy táblaérvényességi szabály: a feltételben ugyanazon táblából több oszlop szerepel.

A gyakran használt érvényességi szabályokat a 8.2. táblázat tartalmazza.

37

8.2. táblázat Gyakran használt érvényességi szabályok Access SQL-ben

Érvényességi szabály Not null Unique Primary key References Check

Leírás Nem lehet üresen hagyni (kötelező kitölteni) Nem lehet két egyforma érték (egyedi érték) Elsődleges kulcs (=not null+unique) Idegen kulcs Tetszőleges (felhasználó által definiált) feltétel

Példa: Create table szerviz1 ( SzAz autoincrement primary key, Rendszam char(7) not null, Tipus char(25), KmAllas number, GyartasDatum Datetime, Datum Datetime not null, Casco yesno, FelvevoAz integer references dolgozok(DolgAz), constraint [Km allas nem lehet 0 vagy negatív] check(KmAllas>0), constraint GyartasDatum check (GyartasDatum
8.3. Alapértelmezett érték Tábla definiálásakor lehetőség van az adott oszlophoz alapértelmezett érték beállítására. Az alapértelmezett érték egy új rekord felvételékor automatikusan megjelenik a mezőben, azonban a módosítására is van lehetőség. Akkor használjuk, ha az egyedek túlnyomó többsége az adott tulajdonság tekintetében azonos jellemzővel bír. Alapértelmezett értéket a tábla definiálásakor, az oszlopra vonatkozó érvényességi szabály után, a DEFAULT parancsszót követően lehet megadni, ügyelve az adattípus által megkívánt szintaktikai szabályokra. Alapértelmezett érték nem csak konstans, hanem függvény által visszaadott érték is lehet. Példák: … oszlop1 number default 5, oszlop2 char(25) default ”BME”, oszlop3 datetime default now(), … Az első esetben az alapértelmezett szám az 5, második esetben az alapértelmezett szöveg a BME, végül a harmadik esetben az alapértelmezett érték az éppen aktuális időpont.

38

8.4. ALTER parancs Az ALTER parancs már meglévő adatbázis objektumok szerkezetének módosítására alkalmas. A szerkezet módosítása különösen veszélyes lehet az adatokkal feltöltött táblák esetében, ezért körültekintően járjunk el. Az ALTER parancs segítségével egy táblának az alábbi tulajdonságai módosíthatóak: 

Új oszlop felvitele: ALTER TABLE tábla1 ADD oszlopn típus [constraint DEFAULT érték]; Példa: Alter table szerviz1 ADD gyartmany char(25) not null;



Meglévő oszlop módosítása: ALTER TABLE tábla1 ALTER COLUMN oszlopn típus [constraint DEFAULT érték]; Példa: Alter table szerviz1 ALTER COLUMN gyartmany char(25) unique;



Oszlop törlése: ALTER TABLE tábla1 DROP COLUMN oszlopn; Példa: Alter table szerviz1 DROP COLUMN Casco;



Érvényességi szabály hozzáadása: ALTER TABLE tábla1 ADD CONSTRAINT constraint1 CHECK (logikai feltétel); Példa: Alter table szerviz1 ADD CONSTRAINT [Km allas nem lehet 0 vagy negatív] CHECK (KmAllas>0);



Érvényességi szabály törlése: ALTER TABLE tábla1 DROP CONSTRAINT constraint1; Példa: Alter table szerviz1 DROP CONSTRAINT [Km allas nem lehet 0 vagy negatív];

8.5. DROP parancs A DROP parancs meglévő adatbázis objektumok TELJES törlésére alkalmas (a tábla szerkezete és tartalma is törlésre kerül). Alkalmazásával egy táblának az elemei külön-külön is törölhetőek, amit a 8.4. fejezetben az ALTER parancsnál bemutattunk (oszlop és érvényességi szabály törlése). Teljes tábla törlése parancs szerkezete: DROP TABLE tábla1; Példa: DROP TABLE szerviz1; Megjegyzés: érvényes CHECK megkötéssel rendelkező táblát általában nem enged törölni az Access.

39

9. DML parancsok Az adatbázisban tárolt és tárolandó adatok kezelésére alkalmas parancsok tartoznak ide. Ebben a fejezetben a táblák adatokkal való feltöltésére, illetve az adatok módosítására alkalmas SQL parancsokat mutatjuk be. Az adatok kilistázására alkalmas SELECT parancsot a korábbi fejezetekben már részletesen megismertük; így ebben a fejezetben csak speciális típusokat mutatunk be.

9.1. INSERT INTO parancs Az INSERT INTO parancs a táblák adatokkal való feltöltésére alkalmas. Az adatok bevitelének módjai: 

Új rekord felvétele: az új rekord értékeit a kezelő adja meg. A parancs általános szerkezete: INSERT INTO tábla1 [(oszlop lista)] VALUES (érték1, érték2, értékn); Ha a tábla minden oszlopához megadunk értéket, akkor az oszlop lista elhagyható. Egyébként az oszloplistában van lehetőség megadni, hogy mely mezőket (és milyen sorrendben) kívánjuk feltölteni adatokkal. Példa: INSERT INTO EMP (empno, ename, job, hiredate, sal) VALUES (1001, ’FOG’, ’ANALYST’, ’1989.03.21.’, 1500); Megjegyzés: alapértelmezett értékkel rendelkező mezőt az Access abban az esetben is kitölti, ha az adatbevitelnél nincs megadva.



Rekord(ok) másolása egy másik táblából vagy lekérdezésből: ha az új rekord(ok) értékeit egy másik tábla tárolja, vagy egy lekérdezés eredmény listájában szerepelnek. Ha a ’forrás’ és a ’cél’ tábla(lekérdezés) szerkezete teljesen megegyezik, és egy rekord valamennyi adatát átmásoljuk, akkor az oszlop lista elhagyható és a select-lista *-ot tartalmaz. Ettől eltérő esetekben az oszlop lista és a select-lista tartalmát egyeztetni szükséges, annak érdekében, hogy a kiolvasott adatelemek a megfelelő mezőkbe kerüljenek. A parancs általános szerkezete: INSERT INTO tábla1 [(oszlop lista)] SELECT select-lista FROM tábla2 vagy lekérdezés1 [WHERE sorfeltétel];

40

Példa: INSERT INTO DOLG SELECT * FROM EMP WHERE empno=7899; Megjegyzés: számított értékeket nem érdemes táblában tárolni a redundancia miatt. Kivéve, ha azt további lekérdezések felhasználják és így a számítási idő csökkenthető.

9.2. UPDATE parancs Meglévő rekordok adatainak módosítására alkalmas parancs. A parancs általános szerkezete: UPDATE tábla1 SET oszlop1=kifejezés1, oszlop2=kifejezés2,… [WHERE sorfeltétel]; Abban az esetben, ha nem adunk meg WHERE feltételt, a tábla összes rekordjában módosulnak az adatok. Példa: növelje a 7902 azonosítószámú alkalmazott fizetését 500-al! UPDATE EMP SET [sal] = [sal]+500 WHERE empno=7902; Megjegyzés: egy rekord adateleme törölhető UPDATE paranccsal, ha az új értéknek a NULL értéket állítjuk be.

9.3. DELETE FROM parancs (rekord, táblatartalom) törlése A táblákban tárolt rekordok törlésére alkalmas parancs. A parancs általános szerkezete: DELETE FROM tábla1 [WHERE sorfeltétel]; Ha a parancsban nincs megadva sorfeltétel, a tábla teljes tartalma törlésre kerül.

41

9.4. Rekordok uniója Összekapcsoló lekérdezéssel két teljesen azonos szerkezetű táblából származó rekordokat lehet összefűzni egy eredményhalmazba. A parancs két SELECT lekérdezést kapcsol össze az UNION parancsszóval. Az eredményhalmazba mindkét lekérdezés által kiválogatott rekordok kerülnek (ismétlődés nélkül). A parancs általános alakja: SELECT parancs 1 UNION SELECT parancs2; Példa: SELECT * FROM EMP UNION SELECT * FROM DOLG;

9.5. Táblakészítő lekérdezés Táblakészítő lekérdezéssel egy lekérdezés eredménylistája táblában menthető el. A táblát (ha már korábban létrehoztunk egyet ugyanazon névvel) minden egyes lekérdezés előtt törli, majd újra létrehozza és feltölti adatokkal. A DML parancsok közé soroljuk, mivel a SELECT parancsszót használja. A parancs általános alakja csak a különbséget kiemelve: SELECT select-lista INTO új_tábla FROM tábla1, lekérdezés1; Megjegyzés: számított értékeket nem érdemes táblában tárolni a redundancia miatt. Kivéve, ha azt további lekérdezések felhasználják és így a számítási idő csökkenthető.

42

10. Gyakorló feladatsorok Ebben a fejezetben a gyakorlati zh-kra való felkészülést segítő minta feladatok találhatók.

10.1. 1. gyakorlati ZH-ra felkészítő feladatok Minta adatbázisra épülő lekérdezések: 1. 2. 3. 4.

Milyen munkakörök vannak a cégnél? Egy munkakört egyszer jelenítsen meg. Rendezze az alkalmazottakat a főnökük azonosítója szerinte csökkenő sorrendbe! Jelenítse meg a telephelyek adatait magyar fejléccel. Jelenítse meg az alkalmazott nevét, beosztását és a telephelyi azonosítót. A telephelyi azonosító előtt egy új oszlopban szerepeljen a „Telephely:” kifejezés. 5. Mekkora az éves jövedelme az alkalmazottaknak? 6. Jelenítse meg azon alkalmazottak nevét abc sorrendben, akiknek nincs jutaléka. 7. Jelenítse meg azon alkalmazottak nevét és beosztását, akik nem a 20-as azonosítószámú telephelyen dolgoznak. 8. Jelenítse meg azon alkalmazottak listáját, akik nevében az A betű a második. 9. Kiknek végződik a munkahelyi azonosítója 9-essel? 10. Kiknek nem a 7788 és 7839 munkahelyi azonosítóval rendelkező alkalmazott a főnöke? 11. Kiknek van a jutaléka 200 és 600 között? 12. Kik léptek be a céghez 1981 decemberében? 13. Jelenítse meg azokat az alkalmazottakat, akiknek a neve nem M-mel vagy A-val kezdődik. Fogalmi modellek: készítse el az adatbázis táblaszerkezetét, figyelembe véve a feladat többi részét is! Vegyen fel rekordokat úgy, hogy a működés jól követhető legyen. 1.

Egy elektromos jármű töltőállomáson a töltőpontok és azok havi ellenőrzését tartjuk nyilván. 4 töltőpont van: 1-1 normál és gyorstöltő, valamint 2 villámtöltő. A töltőpontok csatlakozó típusát és a töltési teljesítményt tartjuk nyilván. A havi ellenőrzéskor rögzítésre kerül a töltőpont villanyórájának az állása, valamint, hogy észlelt-e hibát az ellenőr.  

Rendezze a töltőpontokat a töltési teljesítmény szerinti növekvő sorrendbe. Jelenítse meg a gyorstöltő havi ellenőrzéseit az ellenőrzés ideje szerinti csökkenő sorrendben.

2. Egy légitársaságnak a repülőgépeit és azok bázis reptereit tartjuk nyilván. 5 repülőgép van, melyek közül 3 Boeingnek Budapest a bázis állomása, 2 Airbusnak pedig Róma. Nyilvántartjuk a repülőgépek típusát, gyártási évét, flottába állás időpontját és a férőhelyek számát.  

Jelenítse meg a Budapesten állomásozó gépek listáját a flottába állás dátuma szerinti csökkenő sorrendben. Mennyi idősek lesznek a repülőgépek 2020 május 17.-én?

43

10.2. 2. gyakorlati ZH-ra felkészítő feladatok 1. 2. 3.

Hány nap telt el az 1956-os forradalom kitörése óta? Adja meg √7 értékét 3 tizedesjegy pontossággal! Mekkora az A(-4; 12) és B(7;2) pont távolsága?

Minta adatbázisra épülő lekérdezések: 4. 5. 6. 7. 8. 9. 10. 11. 12.

Milyen napon lépett be Ford? Ki dolgozik a legrégebb óta a vállalatnál? Mennyi az átlagfizetés a SALESMAN és CLERK beosztásokban? Hány %-kal kell növelni az alkalmazottak fizetését, hogy az átlagfizetés elérje a 2500-at? Kik keresnek többet, mint Blake, és mennyivel? Kinek a jutaléka a legmagasabb és a jövedelmének hány %-a a jutalék? Kik dolgoznak New York-ban? Hányan dolgoznak az egyes városokban? Kereszttáblás lekérdezéssel jelenítse meg, hogy hányan dolgoznak az egyes telephelyeken a különböző beosztásokban. 13. Mennyivel nagyobb a fizetése a legnagyobb fizetésű alkalmazottnak, mint JONES-nak? Fogalmi modellek: készítse el az adatbázis táblaszerkezetét SQL DDL parancsok használatával, figyelembe véve a feladat többi részét is! Vegyen fel rekordokat DML parancsok segítségével úgy, hogy a működés jól követhető legyen. 1. Egy elektromos jármű töltőállomáson a töltőpontok és azok havi ellenőrzését tartjuk nyilván. 4 töltőpont van: 1-1 normál és gyorstöltő, valamint 2 villámtöltő. A töltőpontok csatlakozó típusát és a töltési teljesítményt tartjuk nyilván. A havi ellenőrzéskor rögzítésre kerül a töltőpont villanyórájának az állása, valamint, hogy észlelt-e hibát az ellenőr.  

Törölje a lassú töltőt és vegyen fel még egy gyorstöltőt az adatbázisba. Mekkora az összes töltési teljesítmény a töltőállomáson?

2. Egy légitársaságnak a repülőgépeit és azok bázis reptereit tartjuk nyilván. 5 repülőgép van, melyek közül 3 Boeingnek Budapest a bázis állomása, 2 Airbusnak pedig Róma. Nyilvántartjuk a repülőgépek típusát, gyártási évét, flottába állás időpontját és a férőhelyek számát.  

Módosítsa az egyik Boeing repülőgép bázis állomását Rómára. Mekkora a repülőgépek átlagéletkora az egyes bázis állomásokon?

44

IRODALOMJEGYZÉK [1]

Csiszár, Cs. – Sándor, Zs.: Közlekedési informatika (jegyzet) 2014.

[2]

Csiszár, Cs. – Westsik, Gy.: A közlekedési informatika kutatása és oktatása a BME Közlekedésüzemi és Közlekedésgazdasági Tanszékén. Közlekedéstudományi Szemle LXIV. évf. 2. szám 44-52.o. Budapest, 2014.

[3]

Munkácsiné Lengyel, E. – Tóth, J. – Csiszár, Cs. – Juhász, J.: Közlekedési informatika (jegyzet) 2004.

Adatbázis témájú irodalmak gyűjteménye Szelezsán János: Adatbázisok (példatár is) Bálint Dezső: Adatbázis-kezelés (Talentum kiadó) Jeffrey D. Ullmann – Jennifer Widom: Adatbázis-rendszerek Kende Mária - Kotsis Domokos - Nagy István: Adatbázis-kezelés az ORACLE rendszerben Michael Abbey - Michael J. Corey - Ian Abramson: Oracle 8i kezdőknek Bodnár Ibolya - Nagy Zoltán: Adatbázis-kezelés Stolniczki Gyula: SQL kézikönyv

45

MELLÉKLETEK 1. melléklet Adatmodell készítés feladat Készítsen az egyedtípusok megválasztásával 3NF-ra hozott logikai adatmodellt! Adja meg az egyes tulajdonságok tárolásához szükséges adattípusokat és hosszokat is! Téma: Automata parkolóház nyilvántartása Tárolt tulajdonságok: BE_IDOPONT

(jármű behajtásának időpontja)

RENDSZAM HELY_AZ

(parkolóhely azonosítója)

KI_IDOPONT

(jármű kihajtásának időpontja)

MARKA

(jármű márkája)

ORADIJ

(parkolási óradíj, a behajtás időpontjától függ)

PARKOLAS_AZ

(parkolás azonosítója)

MEGFIGYELT_E

(van-e kamerás megfigyelés?)

PARKOLÁS_JELLEMZŐI

(szabálytalan parkolás leírása)

SZEGMENS szinten?)

(melyik szegmensben található az adott

SZIN SZINT

(melyik szinten található?)

TIPUS

46

2. melléklet Adatmodell készítés feladat Készítsen az egyedtípusok megválasztásával 3NF-ra hozott logikai adatmodellt! Adja meg az egyes tulajdonságok tárolásához szükséges adattípusokat és hosszokat is! Téma: Fuvarozó vállalat lebonyolított fuvarfeladatainak nyilvántartása Tárolt tulajdonságok: ALVAZSZAM JMU_VEZ_NEVE

(járművezető neve)

IND_IDOPONT

(indulás időpontja)

TAVOLSAG RENDSZAM SZEM_SERULES

(személyi sérülés)

BALESETI_JK_SZ

(baleseti jegyzőkönyv száma)

SZUL_DATUM UT_SZERV_DATUM

(utolsó szervízelés dátuma)

BALESET_IDOPONT

(baleset időpontja)

ARU_TOMEG

(szállítot áru tömege)

FUTOTT_KM

(indulásig futott km)

ERK_IDOPONT

(érkezés időpontja)

BALESET_KORULM

(baleset körülményei)

FUV_LEV_SZAM

(fuvarlevél száma)

SOFOR_VETKES

(a sofőr vétkes-e?)

JMU_TIPUS JMU_VEZ_JELLEMZOI

(járművezető jellemzői)

ARU_ERTEK

(szállított áru értéke)

JOG_SZAM

(jogosítvány szám)

UTICEL

(város neve)

UZ_HELY_DATUM

(üzembe helyezés dátuma)

ANY_KAR_ERTEKE

(anyagi kár értéke)

47