Eötvös Loránd Tudományegyetem Informatikai Kar Média- és Oktatásinformatika Tanszék
Tehetséggondozás az informatikában – ADATBÁZIS-KEZELÉS
Készítette: Csongrádi Tamás (Tanári MA, informatika-történelem szak) Témavezető: Tarcsi Ádám (Média- és Oktatásinformatika Tanszék, tanársegéd)
Budapest, 2012
EÖTVÖS LORÁND TUDOMÁNYEGYETEM
EREDETISÉGNYILATKOZAT Tanári mesterszakos szakdolgozat tanulmány részéhez (Kitöltés után a tanulmány részét képezi.) A hallgató neve:
Csongrádi Tamás
A hallgató EHA-kódja: A tanulmány címe:
CSTOABI.ELTE Tehetséggondozás az informatikában – Adatbázis-kezelés
Az ELTE tanári mesterszakos hallgatójaként büntetőjogi felelősségem tudatában kijelentem és aláírásommal igazolom, hogy a szakdolgozat részét képező tanulmányom saját, önálló szellemi munkám, az abban hivatkozott, nyomtatott és elektronikus szakirodalom felhasználása a szerzői jogok általános szabályinak megfelelően történt. Tudomásul veszem, hogy szakdolgozat esetén plágiumnak számít: – a szószerinti idézet közlése idézőjel és hivatkozás megjelölése nélkül; – a tartalmi idézet hivatkozás megjelölése nélkül; – más publikált gondolatainak saját gondolatként való feltüntetése. Alulírott kijelentem, hogy a plágium fogalmát megismertem, és tudomásul veszem, hogy plágium esetén tanulmányom visszautasításra kerül, és ilyen esetben fegyelmi eljárás indítható. Budapest, 2012. november 19.
.....................................................................
aláírás
–i–
Tartalomjegyzék I.
Előszó ..................................................................................................................................1
II.
Táblák létrehozása ...............................................................................................................4 1.
Adattípusok az Access-ben ............................................................................................... 4
2.
Érvényességi szabály, érvényesítési szöveg, kötelezőség ................................................. 5 2.1.
3.
Beviteli maszk ................................................................................................................... 8 3.1.
III.
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) A részfeladat ..... 5
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) A részfeladat ............................. 9
Importálási lehetőségek .....................................................................................................12
4.
OKTV 2003/2004. 2. forduló 3. feladat (Síkölcsönző) B részfeladat ............................. 13
5.
OKTV 2005/2006. 2. forduló 7. feladat (Torino2006) A és B részfeladatok ................. 16
IV.
Adatbázis-tervezés, normalizálás.......................................................................................20
6.
Fogalmak ......................................................................................................................... 20
7.
OKTV 2005/2006. 3. forduló 10. feladat (Járatok) A és B részfeladatok ....................... 25
V.
Lekérdezések .....................................................................................................................32 8.
Választó lekérdezések ..................................................................................................... 32 8.1.
Egyszerű választó lekérdezés .................................................................................. 33
8.1.1.
OKTV 2003/2004. 2. forduló (Síkölcsönző) C részfeladat ............................. 33
8.1.2.
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) D részfeladat ................... 35
8.2.
Egyedi értékek megjelenítése .................................................................................. 37
8.2.1. 8.3.
OKTV 2003/2004. 2. forduló (Síkölcsönző) E részfeladat ............................. 37
Csoportosítás, összesítő függvények ....................................................................... 40
8.3.1.
OKTV 2003/2004. 2. forduló 3. feladat (Síkölcsönző) G részfeladat ............. 41
8.3.2.
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) F részfeladat ...................................................................................................... 45
8.3.3.
OKTV 2005/2006. 3. forduló 10. feladat (Járatok) D részfeladat ................... 47
8.4.
Első n/utolsó n elem listázása (TOP) ...................................................................... 49
8.4.1.
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) G részfeladat ...................................................................................................... 49
8.4.2.
OKTV 2005/2006. 3. forduló 10. feladat (Járatok) C részfeladat ................... 51
8.5.
Allekérdezések ........................................................................................................ 52
8.5.1. 8.6.
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) I részfeladat ....... 53
Tartalmazza/Nem tartalmazza ................................................................................. 55
8.6.1.
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) G részfeladat ..... 56
8.6.2.
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) B részfeladat ........................ 57
– ii –
8.7.
Paraméteres lekérdezések........................................................................................ 61
8.7.1. 8.8.
Beépített függvények használata ............................................................................. 66
8.8.1.
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) C részfeladat ........... 67
8.8.2.
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) F részfeladat .................... 69
8.8.3.
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) E részfeladat ........... 71
8.9.
Bonyolultabb lekérdezések ..................................................................................... 74
8.9.1. 9.
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) E részfeladat ........................ 61
OKTV 2005/2006. 3. forduló 10. feladat (Járatok) F részfeladat ................... 74
Hozzáfűző lekérdezések .................................................................................................. 76 9.1.
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) H részfeladat ... 78
10. Frissítő lekérdezések ....................................................................................................... 80 10.1. OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) B részfeladat ................... 81 11. Törlő lekérdezések .......................................................................................................... 84 11.1. OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) F részfeladat ..................... 85 12. Kereszttáblás lekérdezések.............................................................................................. 86 12.1. OKTV 2009/2010. 3. forduló 6. feladat (Új bolygók) E részfeladat....................... 86 13. Táblakészítő lekérdezések............................................................................................... 89 14. SQL-specifikus lekérdezések .......................................................................................... 89 14.1. OKTV 2010/2011. 3. forduló 5. feladat (Állatvédelem) D részfeladat (emlősök).. 91 VI.
Jelentések ...........................................................................................................................95
15. OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) J részfeladat ....................... 95 VII. Űrlapok ..............................................................................................................................98 16. OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) I részfeladat (paraméteres)........................................................................................................................... 98 VIII. Új feladattípusok ..............................................................................................................104 17. Felhasználói felület készítése ........................................................................................ 104 18. Kapcsolódás külső adatbázisokhoz ............................................................................... 104 19. Hibakeresés és –javítás ................................................................................................. 105 IX.
Függelék ..........................................................................................................................106
20. SQL az Access-ben ....................................................................................................... 106 X.
Irodalomjegyzék ..............................................................................................................107
– iii –
Előszó
I. Előszó Szakdolgozatom elsődleges célja, hogy segítse az informatika alkalmazói OKTV-re illetve OKATV-re készülő diákokat a felkészülésben, tanáraikat pedig a felkészítésben. Ezzel egyszerre persze segítséget nyújt mindenki számára, aki az informatika középszintű érettségi, illetve az ECDL szintjén már túlmutató ismereteket és rutint szeretne szerezni a táblázatkezelés témakörében. Azért választottam ezt a célt, mert azt tapasztalom, hogy a könyvesboltok polcain és az interneten is olyan szakirodalom található, mely az adatbázis-kezelés alapjait mutatja be. Olyannal viszont még nem találkoztam, ami ennél magasabb szintű, de mégis könynyen érthető, gyakorlatias, és átfog minden – a mindennapi életben is felhasználható – témakört. A versenyeken használható Windowsos és Linuxos szoftvercsomag is1, ennek ellenére a korábbi OKTV/OKATV versenyeken indulók döntő többsége a Microsoft Office programcsomag valamely – versenyen megengedett – verzióját választotta. Éppen ezért dolgozatomban a Microsoft Office 2010-es verzióját használom egy-egy probléma megoldására. Szakdolgozatom hangvétele az egyszerűségre, érthetőségre törekszik, hogy mind a középiskolás korosztály mind pedig az adatbázis-kezelés témakörében már valamennyire jártas olvasó számára is könnyen emészthető legyen. Nem írok úgy általában a versenyekről, hiszen akit ezen információk érdekelnek, az úgyis utána tud nézni az interneten (pl. http://tehetseg.inf.elte.hu). Pedagógiai tanácsokat sem fogalmazok meg konkrétan, az adatbázis-kezelés általam helyesnek talált tanítási szempontjait csak a sorok közé bújtatva, a témakörök felépítésén, a magyarázatok megfogalmazásán, a típushibák megemlítésével sugalmazom. Munkám során igyekeztem minél gyakorlat-orientáltabb lenni. Az egyes fejezeteket és alfejezeteket a feladatok megoldásához és megértéséhez feltétlenül szükséges elméleti bevezetővel kezdem, bemutatva ezzel az Access főbb funkcióit. Ezt követően a versenyfeladatokból merített példákon keresztül mélyítem el az anyagot, ezek megoldását bőséges magyarázatokkal látom el.
1
A mindenkori használható szoftverlistát lásd itt: http://tehetseg.inf.elte.hu/nemesa/index.html#szoft [Utolsó megtekintés: 2012.06.28.]
–1–
Előszó Mint az a fentiekből is kitűnik, munkám nem azon olvasóknak készült, akik még csak most ismerkednek az adatbázis-kezeléssel. Helyettük elsősorban azoknak ajánlom, akik legalább a középszintű érettségi2 vagy az ECDL3 vizsga letételéhez szükséges tudásszinttel rendelkeznek, mivel ezekre az ismeretekre építek. Ezen felül támaszkodok némi SQL ismeretre is, lévén az emeltszintű érettségi követelményrendszerében szerepel egy lekérdező nyelv alapvető ismerete is. Ezért a Függelék Gyors SQL áttekintés című alfejezetében röviden áttekintem az SQL lekérdezőnyelvvel kapcsolatos általános tudnivalókat. A Függelék SQL az Access-ben című alfejezetével azok munkáját szeretném megkönnyíteni, akik már ismernek egy SQL implementációt (pl. MySQL), de Access-szel, vagy Access-en belül SQL-lel még nem dolgoztak. A feladatmegoldásokat elsősorban az Access QBE4 rács felületén készítem el, azonban a lemezmellékleten mellékelem az SQL-es megoldást is, valamint részletesen kitérek azokra az esetekre, amelyeket SQL-lel célszerűbb, illetve könnyebb megoldani. Az egyes fejezetekben a versenyfeladatok adott részfeladatán keresztül mutatom be az adott probléma néhány lehetséges megoldási lehetőségét. Azonban nem minden versenyfeladatot oldok meg, és nem is teljesen. Az adott témakör végén viszont felsorolom azokat a versenyfeladatokat és részfeladataikat, amelyek bár nem lettek részletesen levezetve, de megoldásuk hasonló elveket követ, mint a részletesen tárgyaltaké. Az SQL-es megoldásokat Courier New betűtípussal írom, az SQL foglalt kifejezéseit pedig csupa nagybetűvel. Például: SELECT nev, szuletes FROM ember WHERE szuletes > 1950; Sok esetben az elinduláskor szükség lehet más alkalmazói rendszer alaposabb ismeretére is. Ekkor a (rész)feladat megoldásához szükséges részeket részletezem, de az esetlegesen szükséges alapszintű ismereteket feltételezem, azokra részletesen nem térek ki.
2
Lásd itt: http://www.oh.gov.hu/letolt/okev/doc/erettsegi_40_2002_201201/informatika_vk.pdf [Utolsó megtekintés: 2012.06.28.] 3 Lásd itt: http://njszt.hu/ecdl/kovetelmenyek [Utolsó megtekintés: 2012.06.28.] 4 Query by Example
–2–
Előszó A lemezmellékleten megtalálható minden eddigi versenyfeladat nyersanyaga, valamint a hozzá tartozó javítási-értékelési útmutató és egy lehetséges megoldás. Ezek ugyanolyan mappanévben találhatóak, mint ahogy a feladatokra hivatkozok, csak rövidítve. Például: a 2003/2004-es tanév OKTV döntőjére a szövegben így hivatkozok: OKTV 2003/2004. 3. forduló, a lemezmellékleten az ehhez tartozó anyagok pedig az OKTV_0304_3 mappában találhatóak. A mappákon belül pedig külön vannak választva a nyersanyagok (források) és a megoldások. Végezetül ajánlom szaktársaim ugyanezen „sorozatba” tartozó, de más témakörben megírt szakdolgozatait is:
• Barta Anita: Szövegszerkesztés és prezentációkészítés • Kiss Csaba: Web- és képszerkesztés • Molnár Katalin: Táblázatkezelés Budapest, 2012. június-november A szerző
–3–
Táblák létrehozása
II. Táblák létrehozása Ahhoz, hogy egy adatbázissal dolgozni tudjunk, adatokra van szükségünk. Az adatbázisban az adatokat táblákban tároljuk. Táblákat kétféleképpen tudunk létrehozni az Access-ben: egy egyszerű űrlap felületen vagy a táblatervező segítségével.
1. ábra: Tábla létrehozása
Az előbbi módszert használva egy táblázatot kitöltve az Access megpróbálja felismerni az egyes mezőkbe megadott adatok típusát, a mezőneveket pedig egyszerűen csak Mező1, Mező2, stb. néven nevezi el, így mindenképpen kénytelenek leszünk használni a második módszert, a Táblatervezőt. A Táblatervezőben meg kell adnunk a mezők neveit, típusait, és opcionálisan adhatunk egy rövid leírást az adott mezőről (ha például a mezőnevek nem lennének elég beszédesek, akkor ez segíthet az eligazodásban).
1. Adattípusok az Access-ben A következőkben röviden összefoglalom az Access 2010-ben használható, az eddigi érettségi és versenyfeladatokban előfordult alap adattípusokat. 5 FORMÁTUM
MEGJELENÍTHETŐ ADAT
Szöveg
Rövid alfanumerikus értékek, például családnevek vagy utcanevek
Szám
Számértékek, például távolságok. Ne feledje, hogy a pénznemeknek külön adattípus van fenntartva
Pénznem
Pénzügyi értékek
5
A teljes táblázat részletes magyarázattal megtalálható a http://office.microsoft.com/hu-hu/accesshelp/adattipusok-es-mezotulajdonsagok-bevezetes-HA010341783.aspx weblapon. [Utolsó megtekintés: 2012.10.11.]
–4–
Táblák létrehozása
Igen/Nem
Igen és nem értékeket, valamint mindig két lehetséges érték egyikét tartalmazó mezők
Dátum és idő
Dátum- és időértékek a 100–9999. évig
Az alap adattípusok további ismertetésétől eltekintenék, azok megtalálhatóak az Access súgójában vagy a http://office.microsoft.com/hu-hu/access-help/adattipusok-esmezotulajdonsagok-bevezetes-HA010341783.aspx webhelyen a fenti táblázattal együtt.
2. Érvényességi szabály, érvényesítési szöveg, kötelezőség Bizonyos esetekben szükségünk lehet arra, hogy korlátozzuk egy mező tartalmát. Ezt az érvényességi szabályok segítségével oldhatjuk meg. Az érvényességi szabály tulajdonképpen egy olyan kifejezés, ami korlátozza a mezőbe írható értéket. Adatbevitelkor az Access megvizsgálja, hogy a rögzítendő adat eleget tesz-e az érvényességi szabálynak. Ha eleget tesz, akkor az adat bekerül a táblába. Ha nem elégíti ki az érvényességi szabályt, akkor a rendszer hibaüzenetet dob. Viszont ez a hibaüzenet nem biztos, hogy mindenki számára informatív. Számunkra megfelelő hibaüzenet megjelenítésére szolgál az érvényesítési szöveg. Abban az esetben, ha megadtunk érvényesítési szöveget, és a rögzítendő adat nem tesz eleget az érvényességi szabálynak, akkor a rendszer az érvényesítési szöveget jeleníti meg hibaüzenetként. Másik, gyakran előforduló eset, hogy a feladat megköveteli azt, hogy bizonyos mezők mindenképpen tartalmazzanak adatot, vagyis hogy tartalmuk nem lehet NULL érték (tehát nem lehet üres). Ilyen esetekben célszerű beállítani a mezőre azt a megszorítást, hogy kötelező kitölteni. Ezt a beállítási lehetőséget a Mezőtulajdonságok panel Általános fülén találjuk. A következőkben a fenti megszorításokra nézzünk egy példafeladatot.
2.1. OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) A részfeladat A feladatban egy konferencia résztvevőinek adatait tároló adatbázist kell létrehozni. A feladat megadja az adatbázis szerkezetét, *-gal jelölve az elsődleges kulcsokat, valamint aláhúzással jelölve a kötelező mezőket, és zárójelben a mező típusa után leírja a mezőre vonatkozó megszorítást is.
–5–
Táblák létrehozása tSzállás
*Szállás (egész), Épület (szöveges, értéke csak ‘A’ vagy ‘B’ lehet), Szobaszám (szöveges)
tRésztvevő
tElőadás
*Résztvevő (egész), Név (szöveges), Férfi_e (logikai), Város (szöveges), SzülDátum (dátum), Szállás (egész), Alkalom (egész), Összelőadás (egész) *Előadás (egész), Cím (szöveges), Szekció (egész), Nap (szöveges, értéke csak ’Csütörtök’, ’Péntek’ vagy ’Szombat’ lehet), Plenáris_e (logikai), Előadó (szám)
tÉrdeklődés
*Előadás (egész), *Résztvevő (egész)
A feladat megoldása szempontjából a táblák közötti kapcsolatokat itt nem tüntetem fel, de a feladat teljes leírásában megtalálható az is. Javasolt megoldás: Megoldási terv: Az A feladatban létre kell hozni a fenti táblákat, be kell állítani az egyes mezőkre vonatkozó megszorításokat és a táblák közötti kapcsolatot. A táblák közötti kapcsolatokat addig ne állítsuk be, míg nem importáltunk minden adatot az adatforrásból. (Erről részletesen lásd az Importálási lehetőségek című fejezetet.) Miután létrehoztuk az adott táblát, állítsuk be a megszorításokat. Javasolt megoldás lépései: 1. Hozzuk létre mind a négy táblát! 2. Importáljuk be a táblákba az adatforrásból az adatokat (Arról, hogy miért importáljuk be korábban az adatokat lásd az Importálási lehetőségek című fejezetet). 3. Ezután kezdjük a tSzállás tábla megszorításaival! a. Nyissuk meg a tSzállás táblát tervező nézetben. Az első megszorítás, hogy az elsődleges kulcsot (Szállás mezőt) kötelező kitölteni. Ha egy mezőt elsődleges kulcsként definiálunk, onnantól kezdve azt kötelező kitölteni. Viszont ha megnézzük a mező tulajdonságait, ott azt láthatjuk, hogy a Kötelező tulajdonságnál Nem szerepel. Állítsuk át Igenre.
–6–
Táblák létrehozása b. Az Épület mezőt szintén kötelező kitölteni. Kattintsunk az Épület mezőnévre, majd a Mezőtulajdonságoknál a Kötelezőt állítsuk Igenre. Emellett láthatjuk, hogy ennél a mezőnél meg van határozva, hogy csak A vagy B értékeket tartalmazhat. Kattintsunk az Érvényességi szabály tulajdonság melletti szövegmezőbe, majd gépeljük be a következő utasítást: = „A” or „B”. Ennek hatására a mezőbe csak A vagy B értékek szerepelhetnek. Ha lenne olyan sor, ahol nem A vagy B szerepel a mezőben, akkor az Access ezt tudatja velünk. Ezután váltsunk át Adatlap nézetre, és próbáljuk ki, működik-e az érvényességi szabályunk!
2. ábra: Érvényességi szabály, érvényesítési szöveg és kötelezőség beállítása
c. Vegyünk fel egy új rekordot, és írjunk C-t az épület mezőbe. Ekkor a rendszer megjeleníti a standard hibaüzenetét. Bár a feladat nem kéri, de célszerű ezt felhasználó barátabbá tenni. Menjünk vissza a Tervező nézetbe, és az Érvényesítési szövegbe írjunk valami egyszerű hibaüzenetet (például: „Csak 'A' vagy 'B' lehet.”). Ezután ha az Épület mezőbe rossz adatot próbálnánk rögzíteni, az általunk megadott hibaüzenet fog megjelenni. d. Végül állítsuk be a Szobaszám táblára a Kötelező megszorítást.
–7–
Táblák létrehozása 4. A fentiekhez hasonlóan állítsuk be a tRésztvevő táblában a Résztvevő és a Név mezőkre a Kötelező megszorítást, a tElőadás táblában az Előadás, Plenáris_e és az Előadó mezőkre szintén a Kötelező megszorítást, a Nap mezőre pedig a Kötelező megszorításon túl az Érvényességi szabályt és az Érvényesítési szöveget is. 5. A tÉrdeklődés táblában pedig mindkét mezőre állítsuk be a Kötelező megszorítást. Ezzel a részfeladat el is készült. További, hasonló módon megoldható feladatok: •
OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) A és B részfeladatok
3. Beviteli maszk Mint az egyes táblák mezőtulajdonságainál is láthatjuk, lehetőségünk van a mezőket beviteli maszkokkal ellátni6. De mire is jók a beviteli maszkok? Ezek segítségével meghatározhatjuk, hogy az adott mezőbe milyen formátummal lehet rögzíteni az adatokat. Ez azt garantálja, hogy az adott mezőbe a felhasználók egységes formátumban adják meg az adatokat. Kétféle karaktercsoport építi fel a maszkot: az úgynevezett literális karakterek és a helyőrzők. A literális karakterek azon karakterek, melyek mindenképpen megjelennek a mezőben, a helyőrzők pedig azok, amiknek a helyére mehet a változó adat. Nézzünk erre egy egyszerű példát! Telefonszámokat szeretnénk rögzíteni az adatbázisunkba, mégpedig a következő formátumban: (10)-234-5678. Ehhez a formátumhoz a következő beviteli maszkot kell elkészítenünk: (_ _)-_ _ _ - _ _ _ _. Ebben a maszkban literális karakternek számít a zárójel és a kötőjel, helyőrzőnek pedig az aláhúzás jel. Beviteli maszkot csak szöveg, szám vagy dátum típusú mezőre lehet készíteni. A beviteli maszkokban felhasználható karakterekről és jelentésükről részletesen lásd az Irodalomjegyzék beviteli maszkokkal foglalkozó pontjait.
6
A táblamezőkön kívül lekérdezésekre, űrlap- és jelentésvezérlőkre is lehet alkalmazni beviteli maszkokat.
–8–
Táblák létrehozása
3.1.
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) A részfeladat
A feladatban űrhajók és küldetéseik adatait tároljuk. A tipusok.xls állományban tároljuk a Típusok tábla adatait. Ebben a részfeladatban a Típusok tábla Start mezőjére kell beviteli maszkot készíteni úgy, hogy csak éveket lehessen rögzíteni a mezőben. Javasolt megoldás: Jelen esetben többféleképpen is el lehet készíteni a Start mezőre vonatkozó beviteli maszkot. Mivel a két megoldás kimenete némiképp eltérő, ezért mindkét megoldást közlöm. Megoldási terv: Először importáljuk be a táblákat, majd miután a Típusok táblát sikeresen beimportáltuk, lássunk neki a beviteli maszk elkészítésének. Az első megoldás lépései: 1. Mint fentebb tisztáztuk, beviteli maszkot csak szöveg, szám vagy dátum típusú mezőre lehet elkészíteni. Mivel a Start mező az első fellövés évét tartalmazza, adhatja magát, hogy állítsuk be dátum típusra a mezőt. Ez esetben viszont napra pontosan jelennek meg a dátumok, nekünk viszont elég csak a fellövés évét megjeleníteni, illetve a későbbiekben rögzíteni. Ezért célszerű szám típust (az Importálás varázslóban Hosszú egész típust) beállítani a mezőre7. 2. Nyissuk meg a Típusok táblát tervező nézetben, és kattintsunk a Start mezőre! A Mezőtulajdonságok panelen kattintsunk a Beviteli maszk sorba. Ha a sor végén lévő ikonra kattintunk, akkor elindul a Beviteli maszk varázsló, vagyis csak indulna. Ugyanis a Beviteli maszk varázsló csak szöveggel vagy dátummal dolgozik. Mindezek ellenére nem kell átállítanunk, egyszerűen csak be kell gépelnünk a maszkot. Ehhez azonban ismernünk kell a beviteli maszkokban felhasználható karaktereket. 3. Szerencsére ez esetben használhatjuk a joker „#” karaktert. A „#” karakter esetén tetszőlegesen beírhatunk egy számjegyet, szóközt, plusz- vagy mínuszjelet. Mivel csak az évszámot kell megjeleníteni, ezért írjunk be az alábbi karakterso-
7
Adatvesztés miatt nem kell aggódni. Importálás után ha megváltoztatjuk a mező típusát Dátum/Időre, akkor a teljes dátumot megjeleníti a rendszer.
–9–
Táblák létrehozása 6. Váltsunk Adatlap nézetre (közben, mikor rákérdez az Access, mentsünk), és próbáljuk ki a most elkészített beviteli maszkot, hasonlóan az előző megoldáshoz. 7. Ha rendesen működik, akkor töröljük az általunk rögzített rekordot. Mint azt láttuk, az egyik megoldás esetén csak az évek látszanak, míg a másik esetén a teljes dátum, de csak azoknál a rekordoknál, amelyeket importáltunk. Ezután viszont csak az évszámot tudjuk rögzíteni. Mindkét megoldás teljesen jó, így mindegy, melyiket választjuk.
– 11 –
Importálási lehetőségek
III. Importálási lehetőségek A legegyszerűbb dolgunk akkor van, ha magunknak kell létrehoznunk táblákat az adatbázisban, és azokat a folyamatos használat során mi töltjük fel adatokkal. Az ECDL, érettségi és versenyfeladatokban azonban mindig a rendelkezésünkre bocsátanak valamilyen forrásállományt, aminek a tartalmát be kell importálnunk egy üres vagy félkész adatbázisba. A következőkben áttekintjük a különböző importálási lehetőségeket, és megnézünk néhány konkrét feladatot is.
4. ábra: Az Access 2010 importálási lehetőségei
Az importálási lehetőségeket a Külső adatok fülön érhetjük el. Mint a fenti ábrán is láthatjuk, az egyszerű szöveges fájltól kezdve az XML-fájlon át egészen egy másik Access adatbázisig importálhatunk adatokat. Sőt, az Egyebek gomb alatt lehetőségünk van HTML dokumentumok, Sharepoint listák és dBase állományok importálására is. Ezen utóbbi két típussal az eddigi érettségi és versenyfeladatok alkalmával nem találkozhattunk. Az adatbázis-kezelés versenyfeladatokkal kapcsolatban általánosságban elmondható, hogy sokszor a legnehezebb feladat pont az elindulás, vagyis hogy hogyan hozzuk a rendelkezésünkre álló forrásállományt olyan formára, hogy azzal dolgozni tudjunk. Ehhez sokszor más alkalmazói programot (például szövegszerkesztőt és/vagy táblázatkezelőt) is használnunk kell, és nem csupán alapszinten. Nézzük meg azokat az általános irányelveket, melyeket célszerű betartani külső adatok importálásakor: •
Általános, minden importálásnál szem előtt tartandó szabály, hogy ügyelni kell az importálandó mezők típusára! Bár az Access importálás varázslója igen nagy
– 12 –
Importálási lehetőségek hatékonysággal ismeri fel az importálni kívánt adatok típusát, célszerű azt az importálás alatt leellenőrizni. •
Mivel a versenyek során az adatbázisok több táblából állnak, ezért már az importálás előtt meg kell vizsgálni, hogy mely táblák vannak egymással kapcsolatban. A kapcsolatban részt vevő mezőknél fokozottan ügyelni kell arra, hogy a típusuk megegyezzen!
•
A táblák közötti kapcsolatot minden esetben az importálás elvégzése után állítsuk be, különben elképzelhető, hogy „Subscript out of range8” hibaüzenetet kapunk.
•
A kapcsolatokon kívül minden egyéb táblákra vagy az egyes mezőkre vonatkozó speciális megszorítást (például érvényességi szabályokat) szintén importálás után állítsuk be. Ez azért is jó, mert például ha az érvényességi szabály létrehozásakor olyan hibaüzenetet kapunk, hogy a táblában szereplő adatok egy része ennek ellentmond, akkor nagy valószínűség szerint valamit elrontottunk (vagy az importálás végén átsiklottunk azon üzenet felett, hogy nem sikerült mindent tökéletesen importálni, vagy az érvényességi szabályban gépeltünk félre valamit).
Ezen általános elvek után nézzünk néhány gyakorlati példát!
4. OKTV 2003/2004. 2. forduló 3. feladat (Síkölcsönző) B részfeladat A feladatban egy síkölcsönző adatbázisát kell létrehoznunk. A feladat megadja az adatbázis szerkezetét, *-gal jelölve az elsődleges kulcsokat: Eszköztípus Eszköz
*Típus (szöveges), Heti_ár (szám) *EAzonosító (szám), Típus (szöveges), Márka (szöveges)
Ügyfél
*ÜAzonosító (szám), Név (szöveges), Cím (szöveges), Fizetendő (szám) *ÜAzonosító (szám), *EAzonosító (szám), Hány_hét (szám), Mitől (dátum), Meddig (dátum)
Kölcsönzés
8
Előre elkészített táblákba való importáláskor is kaphatunk ilyen hibaüzenetet. Ez esetben az importálandó adat nem egyezik meg a mező típusával.
– 13 –
Importálási lehetőségek
Javasolt megoldás: Megoldási terv: Az A feladatban létre kell hozni a fenti táblákat. Itt arra kell ügyelni, hogy a feladat nem adja meg az egyes típusokon belüli részletesebb beállításokat (például az Ügyfél tábla ÜAzonosító mezőjéről csak annyit mond, hogy szám típusú, de azt már nem mondja meg, hogy egész vagy hosszú egész, a Név mezőről pedig csak annyit mond, hogy szöveg típusú, de a szöveg hosszát szintén nem adja meg). A táblák létrehozása után (részletesen lásd a Táblák létrehozása című fejezetet) még ne állítsuk be a táblák közötti kapcsolatot, előbb importáljuk be az adatokat a Kolcson.xls táblázatból! A Kolcson.xls állományt megnyitva láthatjuk, hogy a négy tábla adatai 1-1 munkalapon helyezkednek el. Megvizsgálva a munkalapokat, az első dolog, ami szembetűnő lehet, hogy egyik munkalapon szereplő táblázat sem tartalmaz oszlopneveket. Ha pedig megvizsgáljuk a cellákat, akkor láthatjuk, hogy egyiknél sincs beállítva a megfelelő típus. Javasolt megoldás lépései: 1. Hozzuk létre mind a négy táblát. 2. Ezután nyissuk meg a Kolcson.xls állományt. 3. Minden munkalapon szúrjunk be egy új sort az első sor elé, és az oszlopokat nevezzük el pontosan úgy, ahogyan azt az adatbázisban létrehoztuk. 4. Minden munkalapon minden egyes oszlopnál állítsuk be azt a típust, amit az adatbázisban az adott mezőre beállítottunk. Fontos: az Eszköztípus munkalapon
– 14 –
Importálási lehetőségek a heti ár pénznem formátumban van megadva. Mivel az adatbázisban ez a mező szám típusú, ezért ezt az oszlopot mindenképpen állítsuk át szám formátumra. Ha minden beállítást elvégeztünk, akkor mentsük a módosításokat, és zárjuk be az állományt. Ezután neki is láthatunk az importálásnak. 5. Lépjünk át az Access-be, majd kattintsunk a Külső adatok fülre, és az Importálás és csatolás panelen kattintsunk az Excel gombra. Ezután tallózzunk rá a megfelelő formára hozott Excel állományra, majd jelöljük be a Rekordok másolatának hozzáfűzése a következő táblához opciót, és válasszuk ki az importálni kívánt táblát. 6. A következő lépésben válasszuk ki azt a munkalapot, aminek az adatait szeretnénk az előző lépésben kiválasztott táblába beimportálni (például az Eszköztípus tábla esetén az Eszköztípus munkalapot). 7. Menjünk végig a varázsló lépésein, majd kattintsunk a Befejezés gombra. 8. Ismételjük meg az 5-7. lépéseket addig, amíg minden táblát be nem importáltunk. Ha az összes táblát sikeresen beimportáltuk, akkor létrehozhatjuk a táblák közötti kapcsolatot az Adatbáziseszközök fül Kapcsolat gombjára kattintva. A kapcsolatok létrehozásánál ügyeljünk arra, hogy kipipáljuk a Hivatkozási integritás megőrzése jelölőnégyzetet. Erre azért van szükség, mert a feladatleírásban szereplő képen látható, hogy be van kapcsolva. Maga a hivatkozási integritás azt jelenti, hogy a kapcsolatban csak olyan kulcsmezőre hivatkozhatunk, ami a másik táblában szintén létezik. Ennek megőrzése pedig azt jelenti, hogy a rendszer figyel arra, hogy ne módosíthassunk, vagy törölhessünk ki olyan kulcsot, amire egy másik táblában hivatkozunk. Emellett még arra is figyel, hogy ne rögzíthessünk rossz hivatkozást. Ezzel el is érkeztünk ennek a részfeladatnak a végéhez. Másik megoldási lehetőség, hogy egész egyszerűen – miután a forrásállományban elneveztük az oszlopokat – beimportáljuk a táblákat az adatbázisba, aztán a feladat leírásának megfelelően importálás közben, vagy azután beállítjuk az egyes mezők típusait.
– 15 –
Importálási lehetőségek Abban az esetben, ha az elsődleges kulcsokat több mező alkotja (például a Kölcsönzés táblában), akkor importálás után állítsuk be, ugyanis az Importálás varázslóban nem adhatunk meg több mezőt elsődleges kulcsnak. Több mezőből úgy készíthetünk elsődleges kulcsot, hogy kijelöljük a mezőket, majd rákattintunk a szalagon az Elsődleges kulcs gombra.
5. OKTV 2005/2006. 2. forduló 7. feladat (Torino2006) A és B részfeladatok Ebben a feladatban a 2006-os torinoi téli olimpia programját tároló adatbázist kell elkészíteni. A versenyszámoknál megkötik, hogy férfi, női illetve vegyes lehet. A következő öt táblát kell létrehozni, illetve adatokkal feltölteni (*-gal vannak jelölve az elsődleges kulcsok): Sportág Versenyszám
*SáAzonosító (szám), Név (szöveges) *VszAzonosító (szám), Név (szöveges), Nem (szöveges: N/F/V), Megjegyzés (szöveges), Sportág (szám)
Esemény
*EAzonosító (szám), Név (szöveges), Versenyszám (szám) *Helyszín (szám), *Esemény (szám), *Kezdés (dátum/idő), Időtartam (szám) *HAzonosító (szám), Név (szöveges)
Program Helyszín
A nyersanyagok a TORINO2006.xls állományban találhatók, ezen felül pedig lehet használni a Torino2006.mdb állományt is, melyben az Esemény, Sportág és Versenyszám táblák sémája el van készítve, csupán fel kell tölteni adatokkal.
– 16 –
Importálási lehetőségek Javasolt megoldás: Megoldási terv: Vizsgáljuk meg először, hogyan bocsátották rendelkezésünkre az adatforrást. Ha megnyitjuk a TORINO2006.xls állományt, láthatjuk, hogy minden egyes tábla adata külön munkalapon szerepel. Vegyük észre, hogy egyik munkalapon sincsenek elnevezve az oszlopok, továbbá ha megnézzük a cellák formátumát, láthatjuk, hogy mindegyik Általános formátumú. A Torino2006.mdb állományban három tábla sémája szerepel, de nincsenek adattal feltöltve, valamint a táblák közötti kapcsolat sincs még beállítva. Javasolt megoldás lépései: 1. Hogy ne legyenek a régebbi formátummal problémák, készítsünk egy teljesen új üres adatbázist torino2006.accdb néven! 2. Importáljuk ebbe az adatbázisba a régi adatbázisból a három üres táblasémát (Esemény, Sportág, Versenyszám)! Kattintsunk a Külső adatok fülre, majd itt az Access gombra. A felugró ablakban tallózzunk rá a Torino2006.mdb állományra, majd válasszuk ki a Táblák, lekérdezések, űrlapok, jelentések, makrók és modulok importálása a jelenlegi adatbázisba opciót, majd kattintsunk az Ok gombra. 3. A következő lépésben megjelenik az Objektum importálása ablak. Itt lehetőségünk van az adatforrás adatbázisból minden rendelkezésre álló objektumot beimportálni. Ha a Beállítások gombra kattintunk, láthatjuk, hogy mennyire testre szabható, mit és hogyan importáljunk. Mivel nekünk csak a három táblára van szükségünk, ezért jelöljük ki mindhármat, majd kattintsunk az Ok gombra. Ha mindent jól csináltunk, akkor megjelennek a táblák az adatbázis objektumai között mindenféle hibaüzenet nélkül.
6. ábra: Objektumok importálása másik adatbázisból
– 17 –
Importálási lehetőségek 4. A táblák importálása után nyissuk meg a TORINO2006.xls állományt! 5. Mint azt már korábban megállapítottuk, az egyes munkalapokon nincsenek elnevezve az oszlopok, így ezeket pótolnunk kell a későbbi munkánk megkönnyítése érdekében. Szúrjunk be mindegyik munkalap elejére egy új sort, majd gépeljük be az oszlopneveket pontosan úgy, ahogy az a feladatban, illetve a három már létező táblában szerepel. Ha ezzel megvagyunk, mentsük a módosításokat, majd zárjuk be az állományt, és lássunk neki az importálásnak. 6. Lépjünk át az Access-be, majd kattintsunk a Külső adatok fülre, és az Importálás és csatolás panelen kattintsunk az Excel gombra. Ezután tallózzunk rá a megfelelő formára hozott Excel állományra. 7. Kezdjük az importálást a három, már létrehozott táblával, és ezek közül is a Versenyszámmal! Jelöljük be a Rekordok másolatának hozzáfűzése a következő táblához opciót, és válasszuk ki a Versenyszám táblát. 8. Ezután a következő ablakban jelöljük is a Versenyszám nevű munkalapot, majd lépjünk tovább, és menjünk végig a varázsló lépésein. 9. Ismételjük meg a 6-8. pontokat, amíg mindhárom, már előre létrehozott táblát fel nem töltöttük adatokkal. 10. Ezután már csak a többi, előre létre nem hozott táblát kell beimportálnunk. Ehhez válasszuk ki a Külső adatok fülön az Excel gomb megnyomása és az adatforrásra való tallózás után a Forrásadatok importálása új táblába az aktuális adatbázisban opciót, válasszuk ki az importálni kívánt táblát, majd menjünk végig a varázsló lépésein. 11. Az egyes mezők típusának beállításakor ügyeljünk arra, hogy azon mezők típusának, melyek részt vesznek a táblák közötti kapcsolatokban, ugyanolyan típusúnak kell lenniük, valamint a mezőtulajdonságaiknak is meg kell egyezniük. Ha minden táblát sikeresen beimportáltunk, akkor állítsuk be a Versenyszám tábla Nem mezőjére a szükséges érvényességi szabályt (csak N/F/V értékeket vehet fel – részletesen lásd az Érvényességi szabály, érvényesítési szöveg, kötelezőség című alfejeze-
– 18 –
Importálási lehetőségek tet), utána pedig a táblák közötti kapcsolatot (ne felejtsük el beállítani a Hivatkozási integritás megőrzését is). Ha ez is megvan, akkor elkészültünk e két részfeladattal. További, hasonló módon megoldható feladatok: •
OKTV 2003/2004. 3. forduló (Jelentkezési adatbázis) A és B részfeladatok
•
OKTV 2005/2006. 2. forduló (Torino 2006) A és B részfeladatok
•
OKTV 2006/2007. 2. forduló (Nemzeti park) A részfeladat
•
OKTV 2006/2007 3. forduló 5. feladat (Napelem) A részfeladat
•
OKTV 2010/2011. 2. forduló (Űrhajók) A részfeladat
– 19 –
Adatbázis-tervezés, normalizálás
IV. Adatbázis-tervezés, normalizálás Az előző fejezetben arra néztünk általános elveket és néhány példát, hogy hogyan tudjuk a feladat megoldásához megfelelő formára hozni a rendelkezésünkre bocsátott forrásállományokat. Ebben a fejezetben olyan feladatokkal fogunk foglalkozni, ahol nem csak a forrásállományok formátumával van probléma, hanem a forrásállományok szerkezetével is, sőt, adott esetben nekünk kell megtervezni az adatbázis sémáját is. Ehhez szükségesnek tartom bevezetni a normalizálás fogalmát, és az ahhoz kapcsolódó kulcsfogalmakat is. Ezen fogalmakat igyekszem a lehető legegyszerűbben, legközérthetőbben tárgyalni, hozzájuk egyszerű példákat csatolni, majd néhány OKTV feladaton keresztül bemutatni az alkalmazásukat.
6. Fogalmak Elsődleges kulcs: minden táblában – nem kötelező, de – ajánlott lennie olyan mezőnek vagy mezőknek, amely(ek) a rekord többi mezőjét egyértelműen azonosítják. Ez(eke)t a mező(ke)t nevezzük elsődleges kulcsnak. Funkcionális függőség: ha egy táblában az egyik mező bármely értékéhez egy másik mezőnek csak egy értéke rendelhető. Például egy személyi számhoz csak egy név tartozhat, de ugyanahhoz a névhez több személyi szám is. Kölcsönös funkcionális függőség: ha a funkcionális függőség mindkét irányba igaz. Például: rendszám – autó. Funkcionális függetlenség: ha a két mező értékei között nincs semmiféle kapcsolat. Például: a dolgozó szemének színe és a cég telephelye. Tranzitív funkcionális függőség: ha egy tábla egy mezőjének konkrét értékei meghatároznak a táblán belül más mező értékeit. Például: a FEOR kód meghatározza a szakképzettséget. Redundancia: bizonyos információk feleslegesen ismétlődnek több sorban. Nézzük meg a következő példát9:
9
A példa az Irodalomjegyzék [2]-es számú hivatkozásának 90. oldalán található példa.
– 20 –
Adatbázis-tervezés, normalizálás filmcím Csillagok háborúja
év hossz műfaj 1977 124 sci-fi
stúdióNév Fox
színészNév Carrie Fischer
Csillagok háborúja
1977
124 sci-fi
Fox
Marh Hamill
Csillagok háborúja
1977
124 sci-fi
Fox
Harrison Ford
Elfújta a szél
1939
231 dráma
MGM
Vivien Leigh
Wayne világa
1992
95 vígjáték Paramount
Dana Carvey
Wayne világa
1992
95 vígjáték Paramount
Mike Meyers
1. táblázat: A Filmek tábla
Nézzük meg figyelmesen a Filmek táblát. Láthatjuk, hogy a Csillagok háborúja és a Wayne világa című filmek esetén a színészNév mezőben szereplő neveken kívül minden más adat megegyezik. Ez felesleges ismétlődés, vagyis redundancia. Módosítási anomáliák: ezek akkor fordulhatnak elő, ha nem terveztük meg gondosan az adatbázisunkat, és sok benne a redundáns adat. Ilyen esetben, ha módosítjuk az egyik rekord egy mezőjében tárolt adatot, akkor gondoskodnunk kell arról, hogy a többi hasonló rekordban is megváltozzon. Nézzük ismét a Filmek táblát! Ha valamiért meg szeretnénk változtatni a Wayne világa műfaját vígjátékról drámára, akkor – ha nem figyelünk oda – csak az első sorban változtatjuk meg, míg a második sorban változatlanul hagyjuk. Így viszont nem lennének egységesek a műfaji adatok a filmre nézve. Törlési anomáliák: adott esetben, ha bizonyos értékek halmaza üressé válik, akkor felléphet olyan mellékhatás, hogy más fontos információt is elveszíthetünk. Példának okáért, ha az Elfújta a szél című filmben szereplő színészek közül kitörölnénk Vivien Leight-t, akkor ebben a táblában (és egyúttal az adatbázisban) nem maradna több színész az Elfújta a szélhez, így a Filmek táblából eltűnne az erre vonatkozó sor, minden rá vonatkozó információval együtt (hossz, műfaj). Ezen anomáliák kiküszöbölése érdekében fontos feladat az adatbázis logikai felépítésének gondos megtervezése. Ezt a folyamatot nevezzük normalizálásnak. A normalizálás különböző stádiumait normálformákkal írjuk le. A továbbiakban a normálformákat NF-fel jelölöm, előttük pedig a sorszám jelzi, hogy hányadik normálformáról van szó. •
1NF: a tábla minden sorában oszloponként szereplő értékek atomiak (vagyis egy mezőben csak egy érték szerepel), és megadható hozzá kulcs. Ha több értéket
– 21 –
Adatbázis-tervezés, normalizálás tartalmaz egy mező, akkor annyi sorra bontjuk, ahány érték szerepel a mezőben, és a sor többi elemét ismételjük. •
2NF: a tábla 1NF-ban van, és minden olyan érték, amely nem kulcs, funkcionálisan függ az elsődleges kulcstól.
•
3NF: a tábla 2NF-ban van, és csak az elsődleges kulcstól függnek a mezők. Ha van olyan a táblában, hogy a „B” mező értéke függ az „A” mező értékétől, és a „C” mező értéke függ a „B” értékétől, akkor ebből az következik, hogy a „C” mező értéke tranzitíven függ az „A” mező értékétől. Ha ilyen tranzitív függések fennállnak, azokat ki kell küszöbölni, mégpedig úgy, hogy a táblát két táblára kell bontani úgy, hogy az egyes táblákban már ne legyenek ilyen függések, vagy 3NF-ban legyenek.
Megjegyzés: a fentieken kívül léteznek még magasabb rendű normálformák (BoyceCodd Normálforma, 4NF, 5NF) is, ezekre azonban nem térek ki. Nézzünk egy példát erre a folyamatra! Egy gépjárműkölcsönző forgalmát papíron az alábbiak szerint lehetne vezetni: Kölcsönzés
Kölcsönző
Kölcsönző
Kölcsönzött
Jármű
Kölcsönzési
dátuma
neve
címe
jármű típusa
kategóriája
díj
2012.08.08.
Vincs Eszter
Fő u. 23.
Toyota Yaris
személy
1000
Segít Elek
Alma u. 1.
IFA W50
teher
1500
Kiss József
Körte u. 2.
Opel Astra
személy
1000
Nagy Ferenc
Szilva u. 3.
Renault Midliner
teher
1500
Em Elek
Hold u. 10.
Scania R124
kamion
2000
2012.08.09.
2. táblázat: a Járműkölcsönző tábla
Ha figyelmesen megnézzük, láthatjuk, hogy ez a táblázat nincsen 1NF-ban, hiszen egy soron belül több oszlopban több érték is szerepel (Kölcsönző neve, Kölcsönző címe, Kölcsönzött jármű típusa, Jármű kategóriája, Kölcsönzési díj), vagyis az oszlopok értékei nem atomiak. Úgy tehetjük 1NF-ba, hogy a fenti két sort annyi sorra osztjuk fel, ahány különböző adat szerepel bennük, és mindegyikhez beírjuk a kölcsönzés dátumát. Így a táblázat 5 soros lesz, és a táblázat minden oszlopában csak atomi értékek szerepelnek. Ezek után a tábla így néz ki:
– 22 –
Adatbázis-tervezés, normalizálás Kölcsönzés
Kölcsönző
Kölcsönző
Kölcsönzött
Jármű
Kölcsönzési
dátuma
neve
címe
jármű típusa
kategóriája
díj
2012.08.08.
Vincs Eszter
Fő u. 23.
Toyota Yaris
személy
1000
2012.08.08.
Segít Elek
Alma u. 1.
IFA W50
teher
1500
2012.08.09.
Kiss József
Körte u. 2.
Opel Astra
személy
1000
2012.08.09.
Nagy Ferenc
Szilva u. 3.
Renault Midliner
teher
1500
2012.08.09.
Em Elek
Hold u. 10.
Scania R124
kamion
2000
3. táblázat: az Járműkölcsönző tábla 1NF-ben
Viszont a táblázat tele van redundanciával, hiszen a dátumok többször is szerepelnek a táblázatban. Ebben az esetben törlési és módosítási anomáliák lehetősége is fennáll: •
Ha kitöröljük az IFA W50-t, akkor ezzel töröljük Segít Eleket is minden adatával együtt, pedig a későbbiekben még kölcsönözhet, és akkor jó lenne, ha az adatai az adatbázisban lennének. (törlési anomália)
•
Ha megváltoztatjuk a személygépjárművek kölcsönzési díját, akkor ezt minden egyes sorban meg kell tennünk, különben az adatok nem lesznek konzisztensek. (módosítási anomália)
•
Ha bekerül egy új jármű a táblába, akkor nem tudunk minden mezőt kitölteni, hiszen azt a járművet még nem kölcsönözte ki senki. (bővítési anomália)
Ezeket az anomáliákat kellene kiküszöbölnünk, vagyis a táblát 2NF-ra kell hoznunk. Az előző módosításnak köszönhetően a tábla már 1NF-ban van, így most meg kell találnunk azokat a mezőket, amelyek elsődleges kulcsok lehetnek. Ez esetben elsődleges kulcs lehet a Kölcsönző neve, a Kölcsönzött jármű típusa és a Jármű kategóriája is, mert a Kölcsönző neve meghatározza a Címet, a Kölcsönzött jármű típusa a Jármű kategóriáját, a Jármű kategóriája pedig a Kölcsönzési díjat. A 2NF akkor teljesül, ha az elsődleges kulcsoktól részben függő több különálló táblát készítünk (a továbbiakban csak a mezőneveket tüntetem fel): Sorszám
Kölcsönzés dátuma
Kölcsönző neve
Kölcsönzött jármű típusa
4. táblázat: Kölcsönzések tábla
Kölcsönzött jármű típusa
Jármű kategóriája
5. táblázat: Járművek tábla
Kölcsönző neve
Cím
6. táblázat: Ügyfelek tábla
– 23 –
Kölcsönzési díj
Adatbázis-tervezés, normalizálás A Kölcsönzések táblába bevezettem egy új, Sorszám mezőt, ami az elsődleges kulcs szerepét tölti be. Még így is maradt egy törlési anomália a Járművek táblában, mert ha kitörlünk egy járműtípust, akkor törlődik a kategória és a kölcsönzési díj is. Ahhoz, hogy ezt megszüntessük, és egyben 3NF-ra hozzuk az adatbázist, a Járművek táblát kell tovább bontanunk. Meg kell szüntetnünk a tranzitív függést (a típus meghatározza a kategóriát, a kategória pedig a kölcsönzési díjat, tehát a típus jelenleg meghatározza a kölcsönzési díjat). Bontsuk szét a Járművek táblát az alábbi módon: •
Hozzunk létre egy Kategória táblát Kategória és Díj mezőkkel. A Kategória mező legyen elsődleges kulcs.
•
A Járművek táblát pedig módosítsuk úgy, hogy kivesszük belőle a Díj mezőt!
Így a végleges adatbázis a következőképpen néz ki (az elsődleges kulcsokat aláhúzással jelölöm): •
Kölcsönzések (Sorszám, Dátum, Jármű típusa, Kölcsönző neve)
•
Járművek (Jármű típusa, Kategória)
•
Ügyfelek (Kölcsönző neve, Cím)
•
Kategória (Kategória, Díj)
A négy táblát természetesen össze is kell kapcsolni, hiszen a legtöbb esetben csak együttes használattal lehet kiolvasni az adatokat az adatbázisból. Például, ha azt szeretnénk megtudni, hogy ki és mennyiért kölcsönzött járművet, akkor szükség lesz mind a négy táblára. Azokat a mezőket, melyek hivatkoznak egy másik tábla elsődleges kulcsára, idegen kulcsnak nevezzük. Ezeken keresztül valósulnak meg a táblák közötti kapcsolatok. A könnyebbség kedvéért azokat a mezőket, melyeken keresztül össze kell kapcsolni a táblákat, ugyanúgy neveztem el. Célszerű a gyakorlatban is így tenni, mert ezzel saját munkánkat könnyítjük meg, továbbá így az adatbázis önmagát is „dokumentálja”. E hosszabb elméleti rész után nézzünk néhány OKTV részfeladatot, melyekben alkalmazhatjuk ezen új ismereteket.
– 24 –
Adatbázis-tervezés, normalizálás
7. OKTV 2005/2006. 3. forduló 10. feladat (Járatok) A és B részfeladatok Ebben a feladatban a rendelkezésünkre bocsátott nyersanyagokat felhasználva magunknak kell megterveznünk az adatbázis sémáját. Lássuk a feladat szövegét10! A Nevesincs Közlekedési Szövetség autóbusz járatinformációit egy adatbázisban szeretnénk tárolni (járatok.mdb). Azokat a kulcsfogalmakat, amelyek az adattárolás szempontjából fontosak számunkra, vastagon szedjük a következő mondatban: A menetrend megadja, hogy melyik járat, melyik település megállóhelyére mikor érkezik, és a járatról azt is tudjuk, hogy ki a sofőr. Feltehetjük, hogy egy járat egy településen csak egyszer áll meg. A sofőrökről tároljuk, hogy hány éve vannak szolgálatban a Szövetségnél (sofor.txt). A. Tervezz adatbázis struktúrát a fent leírt adatok tárolására! Hozd létre a táblákat, és a köztük lévő kapcsolatokat! Minden táblában legyen elsődleges kulcs! A menetrendek tárolását úgy kell kialakítani, hogy elvben tetszőlegesen sok járat lehet, és egy járat tetszőlegesen sok településen megállhat. Az tehát nem jó megoldás, hogy annyi oszlopot veszünk fel, ahány település vagy ahány járat maximum lehet. Ügyeljünk arra, hogy egy információt feleslegesen többször ne tároljunk, továbbá azt is oldjuk meg, hogy a menetrendben szereplő városnevek csak a menet0.txt fájlban tárolt nevek közül kerülhessenek ki (természetesen a városnevek itt megadott listája a későbbiekben bővülhet, az adatbázis ezt is támogassa)! A tervezésnél vedd figyelembe, hogy az Általad létrehozott táblákba kell betölteni a B. feladatban megadott fájlok adatait (érdemes először a fájlok adatait megtekinteni)! B. Töltsd fel az adatbázisodat a kapott fájlok adatai alapján! A települések listáját a menet0.txt fájlban, a sofőrök listáját (sofőrazonosító, név, hány éve dolgozik a cégnél) a sofor.txt-ben a járatinformációkat (járatszám, sofőr azonosítója) a jarat.txt-ben, a menetrendi adatokat pedig a menet1.txt – menet9.txt fájlokban találod, ez utóbbiaknál a járat azonosítója a fájlnévből olvasható ki. A szöveges fájlokban kapott adatokat érdemes bemásolni Excel-be vagy egy szövegszerkesztőbe, és ott hozni őket olyan formára, hogy bemásolhatók legyenek az adatbázis tábláidba.
10
A teljes feladatsor megtalálható a CD mellékleten, valamint elérhető a http://tehetseg.inf.elte.hu/nemesa/2006/Nt06-3f4.doc cím alatt [Utolsó megtekintés: 2012.10.18.]
– 25 –
Adatbázis-tervezés, normalizálás Megjegyzés: E feladat elegáns elkészítéséhez szükség lesz a korábbi példákhoz képest mélyebb táblázatkezelői ismeretekre is, ha nem vagyunk elég figyelmesek. ☺ Javasolt megoldás: Megoldási terv: Mivel a forrásadatok összesen 11 állományban (Jarat.txt, Sofor.txt, menet0-9.txt) vannak tárolva, célszerűnek tartom ezeket egy Excel munkafüzetbe összefogni. Egyrészt azért, mert egyben kezelhetőbbek, másrészt azért, mert ha megnyitjuk az egyes szöveges állományokat, láthatjuk, hogy az összefésülés mellett még sok más dolgunk is lesz velük. A legkönnyebb dolgunk a Sofor.txt, a Jarat.txt és a menet0.txt állományokkal lesz, hiszen az ezekben szereplő adatok fogják adni a Sofőrök, a Járatok és a Települések táblák adatait. A Településeknek azért célszerű külön táblába kerülniük, mert a feladat is kiemelte, hogy ezeknek a listája folyamatosan bővülhet, így, ha külön táblában vannak eltárolva, akkor biztosíthatjuk azt, hogy csak azokat a településeket használhassuk fel a menetrendben, melyek szerepelnek a Települések táblában is (emlékezz az anomáliákra). A menet1-9.txt állományokkal első látásra nehezebb dolgunk lesz. Ha megnyitjuk az egyiket, láthatjuk, hogy a települések nevei és a járatok érkezési időpontjai szerepelnek bennük. Első ránézésre láthatjuk, hogy mindegyik állományból hiányoznak az oszlopnevek, ezeket feltétlenül pótolnunk kell, de a feladat megoldása során láthatjuk, hogy még más, fontos adat is hiányzik belőlük. Javasolt megoldás lépései: Kezdjük először a menet0.txt állománnyal! Ebben találhatóak a településnevek. 1. Nyissunk egy üres Excel állományt, mentsük el jarat_forras.xlsx néven, majd kattintsunk az Adatok fülre, és itt a Szövegből gombra. 2. Tallózzunk rá a menet0.txt állományra, majd menjünk végig az importálás varázsló lépésein! 3. Az első lépésben nézzük meg, hogy a mintában minden egyes város neve rendesen megjelenik-e (ékezetes karakterek)! Ha nem, akkor A fájl eredete legördülő
– 26 –
Adatbázis-tervezés, normalizálás listát nyissuk le, és keressünk olyan karakterlapot, amely jól jeleníti meg az ékezetes karaktereket. Ha ez megvan, menjünk tovább. 4. A következő lépésben a mezőket határoló karaktert kell kiválasztani. Az Excel általában jól felismeri a határoló karaktereket, de minden esetben érdemes a mintán megnézni, hogy minden oszlopban normálisan jelennek-e meg az adatok. Ha valahol nem, akkor másik határoló karaktert kell választani. Ennél az állománynál szerencsére nincs probléma, hiszen csak egyetlen oszlopot tartalmaz. 5. Az utolsó lépésben megadhatjuk az oszlopnak a típusát, de akkor sincs probléma, ha Általánoson hagyjuk, a későbbiekben módosíthatjuk. Ezután kattintsunk a Befejezés gombra, és szúrjuk be a munkalapra az adatokat. 6. Miután beimportáltuk az adatokat, szúrjunk be egy sort az oszlop elejére, és írjuk be, hogy Település. Ez lesz ennek a mezőnek a neve. 7. Ha ezzel megvagyunk, nevezzük el a munkalapot Településnek. Ez lesz majd az adatbázisban a Település tábla. A következőkben a Jarat.txt-t fogjuk beimportálni. 8. Hozzunk létre egy új munkalapot az Excel állományban, és nevezzük el Járatoknak. Ez lesz majd az adatbázisban a Járatok tábla. 9. Az előző alapján importáljuk be a Jarat.txt tartalmát! Ügyeljünk a karakterkódolásra! 10. Az importálás után szúrjunk be egy sort a táblázat elejére, majd nevezzük el az oszlopokat! Az első oszlopban a járatazonosítók vannak, így nevezzük Járatazonosítónak. A másodikban a sofőrazonosítók szerepelnek, így azt nevezzük Sofőrazonosítónak. Ezután a Sofor.txt-t importáljuk be. 11. Az előzőekhez hasonlóan hozzunk létre egy új munkalapot Sofőrök néven. 12. Ennek az állománynak a kódolását nem ismeri fel az Excel, itt a 1250: középeurópai (Windows) karakterkódolást kell használni. 13. Ezután a korábbi módon menjünk végén az importálás varázslón.
– 27 –
Adatbázis-tervezés, normalizálás 14. Miután beimportáltuk az adatokat, szúrjunk be egy sort, és nevezzük el az oszlopokat: Sofőrazonosító, Sofőr_neve, Évek_száma. A következőkben importáljuk be a menet1-9.txt állományokat. A munkalapot nevezzük el Menetrendnek. Kezdjük az importálást a menet1.txt-vel! 15. Lépjünk rá az imént létrehozott Menet1_nyers nevű munkalapra. 16. A korábbi táblákhoz hasonlóan menjünk végig az importálás varázsló lépésein. Ügyeljünk a megfelelő karakterkódolásra! 17. Ha nem figyelünk importálás közben, akkor határoló karakternek ott hagyhatjuk a tabulátort az importálás varázslóban, és így minden sort egyetlen cellába fog beilleszteni. Ha ezt tesszük, és nem jut eszünkbe, hogy próbáljunk ki más határoló karaktert, akkor kicsit megnehezítettük a saját dolgunkat – ahogy én tettem először. Ebben a nehezített esetben az Excel Szöveg függvényeit (a Bal, a Közép és a Szöveg.keres függvényeket megfelelő paraméterezéssel) kell alkalmaznunk ahhoz, hogy megfelelő formára hozzuk a nyers állományt11. Viszont, ha figyelmesek voltunk, akkor láthattuk, hogy a menet1-9.txt állományok esetén a határoló karakter a szóköz. 18. Ha sikeresen beimportáltuk – szóköz határoló karakter használatával – a menet1.txt állomány adatait, akkor láthatjuk, hogy nincsenek oszlopnevek. Szúrjunk be egy sort a táblázat elejére, és nevezzük el az oszlopokat Településnek és Érkezésnek. 19. Már csak egy problémánk van ezzel az adathalmazzal: szerepelnek benne a települések és az érkezési idők, viszont az nem, hogy ezek az adatok melyik járathoz tartoznak. Ez azért probléma, mert így számunkra is nehéz lesz azonosítani a járatokat, de ami fontosabb, hogy nem fogjuk tudni összekapcsolni a Járatok táblával. Ezért szúrjunk be egy új oszlopot a Település oszlop elé, és nevezzük el Járatazonosítónak. Ha ez megvan, akkor a települések nevei elé írjunk mindenhova 1-est, hiszen a menet1.txt – aminek az adataival jelenleg dolgozunk – az 1es azonosítójú járat adatait tartalmazza. 11
A lemezmellékleten az OKTV_0506_3\megoldas mappában megtalálható a megfelelő formára hozott állomány, amelyben ez a bonyolultabb megoldás látható.
– 28 –
Adatbázis-tervezés, normalizálás 20. Álljunk be a következő üres sorba, majd importáljuk be a menet2.txt-t. A módszer megegyezik az előző tábla importálási lépéseivel (karakterkódolás, határoló karakter). Megjegyzés: innentől kezdve már nem kell felvennünk az oszlopneveket, hiszen azok a legelső sorban szerepelnek. 21. Ha beimportáltuk az adatokat, akkor a Járatazonosító oszlop üres részeit töltsük fel 2-es számmal, mert ezek az adatok a 2-es azonosítójú járathoz tartoznak. 22. Ismételjük meg a fenti lépéseket a menet3-9.txt állományokkal! 23. Miután a menet1-9.txt állományokat beimportáltuk a Menetrend munkalapra, és fentieknek megfelelően kiegészítettük az adatokat, már csak egyetlen teendőnk maradt: az Érkezés oszlopban az óra és a perc közötti vessző karaktereket ki kell cserélnünk „:” (kettőspont) karakterekre. Ehhez célszerű használnunk a Kereséscsere funkciót (Kezdőlap fül, Keresés és kijelölés gomb, Csere menüpont, vagy CTRL+H billentyűkombináció). Ezután egy javaslat a gyorsabb cserére (lehet, hogy van jobb megoldás is): a Keresett szöveg mezőbe írjuk be az órát, majd tegyünk utána vesszőt (6,), a Csere erre mezőbe pedig írjuk be az órát, majd tegyünk utána kettőspontot (6:). Ezt követően kattintsunk Az összes cseréje gombra, és ismételjük meg minden, az oszlopban szereplő óra esetén. Ha mindegyik állományt beimportáltuk, akkor elkészült a jarat_forras.xlsx állományunk, ami megfelelő formában tartalmazza adatbázisunk minden táblájának minden adatát. Ezután nincs más dolgunk hátra, mint létrehozni az adatbázist, beimportálni a jarat_forras.xlsx állományból az adatokat, majd beállítani a táblák közötti kapcsolatot. 24. Hozzuk létre a jaratok.accdb adatbázist! 25. Importáljuk be a táblákat! (Részletesen lásd az Importálási lehetőségek című fejezetet!) Ügyeljünk arra, hogy a táblák kapcsolataiban részt vevő mezők (idegen kulcsok) azonos típusúak és mezőtulajdonságúak legyenek! 26. Állítsuk be az egyes táblákban az elsődleges kulcsokat is! a. A Települések táblában egyetlen mező található, ami a települések neveit tartalmazza; állítsuk ezt be elsődleges kulcsnak.
– 29 –
Adatbázis-tervezés, normalizálás b. A Járatok táblában legyen a Járatazonosító az elsődleges kulcs, hiszen egy járatot általában ugyanaz a sofőr vezet végig. c. A Sofőrök táblában a Sofőrazonosító egyértelműen azonosítja a sofőröket, ezért legyen ez a mező az elsődleges kulcs! d. A Menetrend táblában a három mező közül önmagában egyik sem azonosítja egyértelműen a másik két mezőt. Például, ha a Járatazonosító mezőt választanánk elsődleges kulcsnak, az nem határozza meg egyértelműen a Település és az Érkezés mező értékeit. Így tehát ebben a táblában az elsődleges kulcs egyben összetett kulcs, vagyis több mezőt foglal magába. Két lehetőségünk van: az egyik, hogy elsődleges kulcsnak választjuk a Járatazonosító és a Település mezőket együtt. A másik, hogy a táblában szereplő mindhárom mező fogja alkotni az elsődleges kulcsot. Az előbbi esetén a Járatazonosító és a Település már egyértelműen meghatározza az Érkezést, hiszen a feladat szövege kiemelte, hogy feltehetjük, hogy minden járat minden településen csak egyszer áll meg, vagyis csak egyszer érkezhet meg oda. Az utóbbi eset (vagyis hogy a három mező együttesen alkotja az elsődleges kulcsot) pedig garantálja, hogy még véletlenül se legyen ismétlődés a rekordok között. 27. Ha minden táblát beimportáltunk, állítsuk be a kapcsolatokat! Kattintsunk az Adatbáziseszközök fülre, majd itt a Kapcsolatok gombra. Állítsuk be az alábbi ábrán látható kapcsolatokat (ne feledkezzünk meg a Hivatkozási integritás megőrzéséről)!
7. ábra: A jaratok adatbázis táblái és a köztük lévő kapcsolatok
Ezzel pedig elérkeztünk ennek a részfeladatnak a végéhez.
– 30 –
Adatbázis-tervezés, normalizálás További, hasonló módon megoldható feladatok: •
OKTV 2009/2010. 3. forduló 6. feladat (Új bolygók) A részfeladat
•
OKTV 2010/2011. 3. forduló 5. feladat (Állatvédelem) A részfeladat
– 31 –
Lekérdezések
V. Lekérdezések Ebben a fejezetben az adatbázis-kezelés leglényegesebb részével fogunk foglalkozni: a lekérdezésekkel. Segítségükkel tudunk egy nagy adathalmazból konkrét kérdéseinket megválaszoló információkat kigyűjteni. Ezt úgy érjük el, hogy az adatbázis tábláinak egyes mezőkre különböző feltételeket szabunk. A következőkben röviden tárgyaljuk, hogy mire jók az egyes lekérdezés típusok, majd korábbi OKTV részfeladatokon keresztül gyakoroljuk őket. Számos lekérdezésnél – főleg a választó lekérdezéseknél – szükségünk lesz az Access beépített függvényeinek a használatára. Ezen beépített függvényekkel megoldható feladatok külön alfejezetben lesznek tárgyalva, de az adott függvény felépítését, működését konkrétan a részfeladat megoldása során fogjuk látni. Az egyes lekérdezés-típusok példafeladatainál röviden összefoglalom, mit kér az adott részfeladat. Javaslom azonban az eredeti feladatlap – megtalálhatóak a lemezmellékleten, valamint a http://tehetseg.inf.elte.hu/nemesa/nemesa_archivum.html honlapon is – megtekintését is, ugyanis azon található az adatbázis sémája, aminek ismerete megkerülhetetlen a feladatok megoldásánál.
8. Választó lekérdezések Ha adatokat szeretnénk kinyerni az adatbázisból, vagy ki szeretnénk számítani valamit, de azt nem akarjuk eltárolni, akkor választó lekérdezéseket kell készítenünk. Minden olyan művelet, melyben nem hozunk létre új táblát, nem módosítjuk (új mező hozzáfűzése, adatok módosítása vagy törlése) a táblák tartalmát, ebbe a kategóriába tartozik. Érettségin és az ECDL feladatokban legtöbbször ilyen lekérdezéseket kell készíteni, így feltételezem, hogy kedves Olvasómnak már van tapasztalata választó lekérdezések készítésében. A legfontosabb dolgokat azonban gyorsan áttekinteném. •
Az esetek túlnyomó részében célszerű a Lekérdezéstervező eszközt használni a Lekérdezés varázsló helyett. A feladatok megoldásánál én is ezt teszem.
•
Csak azokat a táblákat adjuk hozzá a lekérdezéshez, amelyeket fel is használunk.
•
Minden lekérdezés egy – ha mi külön nem gondoskodunk róla – rendezetlen halmazt ad vissza. Ha egy lekérdezés nem ad vissza végeredményt, elképzelhe-
– 32 –
Lekérdezések tő, hogy rosszul dolgoztunk, de az is, hogy a lekérdezés eredménye az adatbázis pillanatnyi adataitól függően egy üres halmaz. •
Ha több mező szerint rendeznénk, viszont a mezőket más sorrendben szeretnénk megjeleníteni, mint amilyen sorrendben a rendezést végre szeretnénk hajtani, akkor a mezőket újra fel kell venni a rendezés sorrendjében, és ebben a sorrendben kell beállítani a mezők rendezését. Ez esetben viszont ne feledkezzünk meg arról, hogy ezen mezőket nem kell kétszer megjeleníteni a lekérdezés végeredményében.
•
Amennyiben több mezőre vonatkoznak feltételek, úgy végig kell gondolnunk, hogy a feltételek között logikai ÉS vagy logikai VAGY kapcsolat szerepel. Amennyiben a feltételek között logikai ÉS kapcsolat van, akkor az egyes mezőkre vonatkozó feltételeknek egy sorba kell kerülniük a QBE rácson. Ha azonban logikai VAGY kapcsolat van az egyes mezők feltételei között, akkor a feltételeknek külön sorba kell kerülniük.
8. ábra: Példa mezők közötti logikai ÉS kapcsolatra
9. ábra: Példa mezők közötti logikai ÉS és logikai VAGY kapcsolatra
•
Egy lekérdezést felhasználhatunk más lekérdezésekben is.
Ezek után nézzünk néhány feladatot!
8.1.
Egyszerű választó lekérdezés
8.1.1. OKTV 2003/2004. 2. forduló (Síkölcsönző) C részfeladat Maga a feladat nagyon egyszerű: egy olyan lekérdezést kell készítenünk, amely kiírja a Kölcsönzés tábla tartalmát, de úgy, hogy a táblában szereplő azonosítók helyett az ügyfél nevét, valamint a kölcsönzött eszköz típusát és márkáját jelenítsük meg.
– 33 –
Lekérdezések Javasolt megoldás: Megoldási terv: Ha valamiért még nem tettük volna meg, akkor kapcsoljuk össze az adatbázis tábláit. Ezután adjuk hozzá a lekérdezéshez a szükséges táblákat, majd jelenítsük meg a kívánt mezőket. Javasolt megoldás lépései: 0. Kapcsoljuk össze a táblákat, ha még nem lennének összekapcsolva! Ezt az Adatbáziseszkök fülön a Kapcsolat gombra kattintva tehetjük meg. Adjuk hozzá az adatbázis összes tábláját, majd kapcsoljuk össze őket. Ügyeljünk arra, hogy a kapcsolat beállításakor kipipáljuk a Hivatkozási integritás megőrzése jelölőnégyzetet! 1. Kattintsunk a Létrehozás fülre, majd itt a Lekérdezéstervező gombra. 2. Adjuk hozzá a Kölcsönzés, az Eszköz és az Ügyfél táblákat a lekérdezéshez. 3. A feladat azt kéri, hogy jelenítsük meg a Kölcsönzés tábla mezőit úgy, hogy az ügyfelek neveit, valamint az eszközök típusát és márkáját jelenítjük meg az azonosítóik helyett. Kattintsunk duplán az Ügyfél tábla Név mezőjére, majd az Eszköz tábla Típus és Márka mezőire. Ezután – szintén dupla kattintással – adjuk hozzá a lekérdezéshez a fennmaradt mezőket a Kölcsönzés táblából (Hány_hét, Mitől, Meddig). A lekérdezés így néz ki tervező nézetben: 4. Ha készen vagyunk, nézzük meg a végeredményt. Váltsunk át Adatlap nézetre, ellenőrizzük, hogy csak azon mezők értékei jelennek meg a lekérdezés végeredményében, amelyek kellenek, majd zárjuk be a lekérdezést, és mentsük C néven.
– 34 –
Lekérdezések
10. ábra: A Síkölcsönző című feladat C részfeladatának megoldása tervező nézetben
Készen is vagyunk ezzel a részfeladattal.
8.1.2. OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) D részfeladat Ebben a feladatban űrhajók és küldetéseik, valamint az űrhajótípusok adatait tároló adatbázist kell készítenünk.
11. ábra: Az Űrhajók adatbázis táblái között lévő kapcsolatok
A szóban forgó D részfeladatban pedig arra a kérdésre kell válaszolnunk, hogy melyik évben lőttek fel olyan űrhajókat, melyek nevében szerepel a Ranger vagy a Szputnyik szó? Az egyes éveket pedig csak egyszer szabad megjelenítenünk (részletesen lásd az Egyedi értékek megjelenítése című alfejezetet).
– 35 –
Lekérdezések Javasolt megoldás: Megoldási terv: A fenti ábra segítségével válasszuk ki a lekérdezésben szereplő táblát, vagy táblákat, utána fogalmazzuk meg a feltételeinket. Mivel a feltételben karakterláncokat keresünk, ezért a Like kulcsszót kell használnunk. A nevekben a Ranger és a Szputnyik szavakat keressük, viszont azt nem tudjuk, hogy ezen szavak a teljes nevek, vagy szerepel valami még a névben (de azt biztosan mindenki tudja, hogy több Szputnyik nevű járművet lőttek fel). Ezért még a „*” helyettesítő karaktert12 is használnunk kell. Végül állítsuk be, hogy csak az egyedi értékek jelenjenek meg a lekérdezés végeredményében. Javasolt megoldás lépései: 1. Kattintsunk a Létrehozás fülre, majd ott a Lekérdezéstervező gombra. 2. Adjuk hozzá a lekérdezéshez az Urhajok táblát, hiszen ez a tábla tartalmazza az űrhajók neveit. 3. A lekérdezéshez adjuk hozzá az Ev és a Nev mezőket. 4. Ezután a Nev mezőre fogalmazzuk meg a feltételünket; kattintsunk a Nev oszlop Feltétel sorába. Emlékeztetőül: a feladat az, hogy jelenítsük meg azoknak az űrhajóknak a fellövési évét, amelyeknek a nevében szerepel a Ranger vagy a Szputnyik szavak valamelyike. Mivel a feltételben karakterlánc mintákat keresünk, ezért a feltétel így fog kinézni: Like *Ranger* or Like *Szputnyik*13. Megjegyzés: a feltételt úgy is megadhatjuk, hogy a Nev oszlop Feltétel sorába beírjuk azt, hogy Like *Ranger*, majd alá – a Vagy sorba – pedig azt, hogy Like * Szputnyik*. Jelen feladatnál mindegy, melyik megoldást választjuk, viszont olyan esetekben, ahol bizonyos feltételek között logikai „ÉS”, míg mások között logikai „VAGY” kapcsolatnak kell fennállnia, már nem lesz mindegy, hogy me-
12
A „*” helyettesítő karakter jelentése: tetszőleges számú karakter (szöveg, szám vagy szóköz) állhat a helyén. 13 Bár szövegre vonatkozik a feltétel, nem szükséges a QBE rácson a keresendő szövegrészleteket idézőjelek közé tenni, ezt az Access megteszi helyettünk. Viszont ha az SQL nézetben írjuk meg a lekérdezést, ott kötelező idézőjelek közé tenni a keresendő szövegrészleteket.
– 36 –
Lekérdezések lyik módszert választjuk, mert módszertől függően kell megfogalmaznunk a megfelelő feltételt. 5. Utolsó lépésként pedig állítsuk be azt, hogy az évszámok csak egyszer jelenjenek meg (Tulajdonságlap, Egyedi értékek, Igen). A feladat két lehetséges megoldása látható az alábbi ábrán:
12. ábra: Az Űrhajók feladat D részfeladatának két lehetséges megoldása QBE rácson
5. Mentsük a lekérdezést a megadott néven, majd zárjuk be. További, hasonló módon megoldható feladatok: •
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) C részfeladat
•
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) D részfeladat
•
OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) C részfeladat
8.2.
Egyedi értékek megjelenítése
Sokszor előfordulhat, hogy egy lekérdezés eredményében többször szerepel ugyanaz az érték, nekünk viszont csak egyszer lenne szükségünk rá. Ilyen esetekben kell beállítanunk az adott mezőre, hogy az egyes értékek csak egyszer jelenjenek meg.
8.2.1. OKTV 2003/2004. 2. forduló (Síkölcsönző) E részfeladat A feladat azt kéri tőlünk, hogy adjuk meg egyszer az összes olyan eszköztípust, amelyet legalább egyszer, legalább két hétig kikölcsönöztek.
– 37 –
Lekérdezések Javasolt megoldás lépései: 1. Kattintsunk a Létrehozás fülre, majd ott a Lekérdezéstervező gombra. 2. Adjuk hozzá a lekérdezéshez az Eszköz és Kölcsönzés táblákat. 3. Mivel a feladat az eszköztípusok megjelenítését kéri, ezért adjuk hozzá az Eszköz tábla Típus mezőjét a lekérdezéshez. Ezen kívül még azt a feltételt szabta a feladat, hogy csak azok a típusok jelenjenek meg, amelyeket legalább egyszer, legalább két hétig kikölcsönöztek. Ezért szükségünk lesz a Kölcsönzés tábla Hány_hét mezőjére is. A Hány_hét mező Feltétel sorába formalizáljuk a feltételt; legalább két hétre kölcsönözték ki, tehát a feltétel: >=2. 4. Mivel a feladat csak az eszköztípusok megjelenítését kérte, ne felejtsük el kivenni a pipát a Hány_hét mező Megjelenítés sorából. 5. Ha most átváltunk Adatlap nézetre, láthatjuk, hogy megjelennek az eszköztípusok, de többször. 6. Menjünk vissza a Tervező nézetbe, majd a szalagon a Tervezés fülön kattintsunk rá a Tulajdonságlap gombra. 7. A megjelenő ablakban, attól függően, hogy az egyes mezőkre, vagy a táblákat tartalmazó ablakba kattintunk, a lekérdezésben szereplő mezők, illetve a lekérdezés tulajdonságait állíthatjuk be. Mivel az egész lekérdezésre vonatkozik, hogy ne jelenjenek meg ugyanazok az elemek többször, kattintsunk a táblák mellé, majd a Tulajdonságlap Általános fülén az Egyedi értékek beállítást állítsuk Igenre. A feladat megoldása az alábbi ábrán látható:
– 38 –
Lekérdezések
13. ábra: A Síkölcsönző című feladat E részfeladatának megoldása tervező nézetben
8. Váltsunk nézetet, és láthatjuk, hogy most már csak egyszer jelennek meg az eszköztípusok. Zárjuk be a lekérdezést, és mentsük E néven. További, hasonló módon megoldható feladatok: •
OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) D részfeladat
•
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) D részfeladat
– 39 –
Lekérdezések
8.3.
Csoportosítás, összesítő függvények
Sokszor szükség van arra, hogy az adatbázisban szereplő adatokat csoportosítva jelenítsük meg, és hozzá számoljunk is ki valamit. Az ilyen feladatok megoldására szolgálnak a QBE rács Összesítés sorában található összesítő függvények és záradékok. Az Összesítés
sor
alapértelmezetten
nem
látszik.
Ahhoz,
hogy
bekapcsolhassuk,
a
Lekérdezéstervezőben a Tervezés fülön rá kell kattintanunk az Összesítés gombra.
14. ábra: Az Összesítés sor bekapcsolása a QBE rács felületen
A megjelent Összesítés sorban ezután összesítő függvények és záradékok közül válogathatunk. Nézzük meg, melyik függvény vagy záradék mire szolgál! Összesítő függvények: •
Sum: a mezőben szereplő értékek összegét adja vissza.
•
Avg: a mezőben szereplő értékek átlagát adja vissza.
•
Min: a mezőben szereplő értékek közül a legkisebbet adja vissza.
•
Max: a mezőben szereplő értékek közül a legnagyobbat adja vissza.
•
Count: a mezőben szereplő értékek darabszámát adja vissza.
•
StDev: a mezőben előforduló értékek szórását adja vissza.
•
Var: a mezőben előforduló értékek szórásnégyzetét adja vissza.
– 40 –
Lekérdezések •
First: az első rekordban lévő értéket adja vissza.
•
Last: az utolsó rekordban lévő értéket adja vissza.
Záradékok: •
Group by: azon mezők szerint csoportosítja a lekérdezés eredményét, ahol az Összesítés sorban a Group by záradék szerepel. Abban az esetben, ha a lekérdezésben szereplő mezők közül akár csak egyre is alkalmazunk összesítő függvényt, akkor a többi mezőnél a Group by záradékot kell beállítanunk, különben a lekérdezés nem fog működni.14
•
Expression: ezt a záradékot olyan számított mezők esetén kell használni, melyek tartalmaznak összesítő függvényeket.
•
Where: ezt a záradékot olyan mezőkre kell alkalmazni, amelyre feltételt szabunk meg, viszont nem csoportosítunk velük, és nem is tartalmaznak összesítő függvényt.
Az összesítő függvények és a záradékok rövid áttekintése után nézzünk néhány példafeladatot!
8.3.1. OKTV 2003/2004. 2. forduló 3. feladat (Síkölcsönző) G részfeladat Ebben a feladatban egy olyan lekérdezést kell készíteni, ami kiszámolja, hogy egy ügyfélnek mennyit kell fizetnie a kölcsönzései után. Javasolt megoldás: Megoldási terv: Elsőként el kell döntenünk, hogy mely táblákat kell felhasználnunk, majd miután ez megvan, hozzá kell adnunk a lekérdezéshez a megjelenítendő mezőket, valamint ki is kell számolnunk az ügyfél által fizetendő összeget. A számítás módjában segít a feladat is: a kölcsönzések darabszámát, időtartamát és a bérelt eszköztípusok árát kell felhasználni.
14
Ennek oka, hogy a QBE rács felület mögött SQL utasítások hajtódnak végre, ez pedig alapvető szabály egy SQL lekérdezés készítésekor.
– 41 –
Lekérdezések Javasolt megoldás lépései: 1. Kattintsunk a Létrehozás fülre, majd ott a Lekérdezéstervező gombra. 2. Mivel az ügyfelek nevére szükség van, ezért adjuk hozzá a lekérdezéshez az Ügyfél táblát. A kölcsönzés időtartama (Hány_hét) a Kölcsönzés, az eszköztípusok ára az Eszköztípus táblákban található, így ezeket is hozzá kell adnunk a lekérdezéshez. Ezeken kívül még az Eszköz táblára is szükségünk lesz. Ennek az az oka, hogy a Kölcsönzés és Eszköztípus táblák az Eszköz táblán keresztül vannak összekapcsolva, és ha kihagynánk a lekérdezésből, akkor megszűnne a két tábla közötti kapcsolat. 3. Adjuk hozzá a lekérdezéshez az Ügyfél tábla Név mezőjét. 4. Ezután kattintsunk a szalagon a Tervezés fülön az Összesítés gombra. Ezzel megjelenik az Összesítés sor a rácson. 5. A Név mezőnél állítsuk be az összesítéshez a Group by záradékot, hiszen ügyfelenként kell kiszámolnunk, hogy mennyit kell fizetniük. 6. Kattintsunk jobb egérgombbal a Név mező mellett található üres mezőbe, majd a legördülő menüben válasszuk ki a Szerkesztés menüpontot. Ezzel belépünk a Kifejezésszerkesztőbe, ahol létre tudunk hozni mindenféle számított mezőt.
15. ábra: Belépés a Kifejezésszerkesztőbe
7. Először is, adjunk a számított mezőnek nevet! Mivel az ügyfelenként fizetendő összeget kell kiszámítanunk, ezért legyen a mező neve az, hogy Öszszes_fizetendő. A szöveg után tegyünk kettőspontot (:), ez jelzi a rendszernek, hogy ez a szöveg lesz a számított mező neve.
– 42 –
Lekérdezések 8. A következő lépésben számoljuk ki, hogy egy ügyfél egy kölcsönzött termékért mennyit kell, hogy fizessen. Az adatbázisban nyilván van tartva, hogy egy heti kölcsönzésért mennyit kell fizetni (Heti_ár), valamint az is, hogy az ügyfél hány hétre kölcsönözte ki az adott terméket (Hány_hét). Ebből következik, hogy az egy termékért fizetendő összeg a Heti_ár és a Hány_hét mezők értékeinek szorzata. 9. Álljunk a kurzorral a mezőnév után, majd a Kifejezéselemek között tallózzunk rá az
adatbázisfájlunk
táblái
között
a
Kölcsönzés
táblára,
majd
a
Kifejezéskategóriák között kattintsunk duplán a Hány_hét mezőre. Ekkor megjelenik a szerkesztőben a mező. 10. Gépeljünk utána egy csillag (*) karaktert – ez a szorzás operátor15 –, majd ismételjük meg a 9 lépést, csak ezúttal az Eszköztípus tábla Heti_ár mezőjével tegyük ezt. Ha elkészültünk, akkor ezt kell látnunk a képernyőn:
16. ábra: Az Összesen_fizetendő nevű számított mező elkészítésének első lépése
11. Már csak annyit kell tennünk, hogy az egyes termékek kölcsönzési díját összeadjuk. Ehhez a Sum összesítő függvényt kell használnunk. Nyissuk meg a Kifejezéselemek között a Függvényeket, majd ott kattintsunk a Beépített függvényekre. Ezután a Kifejezésértékek között keressük meg a Sum függvényt, és kattintsunk rá duplán. Ekkor az általunk készített kifejezés után az alábbi kifejezést szúrja be
15
Felvetődhetne a kérdés, hogy a * miért szorzás operátorként viselkedik, ha korábban már láttuk, hogy a * egy helyettesítő karakter. Ez igaz, viszont jelen esetben nem a Like összehasonlító kifejezésben és nem is idézőjelek között – tehát szövegként – használjuk, hanem szám típusú mezők között.
– 43 –
Lekérdezések a program: «Kif» Sum(«kifejezés»). Töröljük ki a Sum előtti «Kif» szöveget, majd jelöljük ki az általunk elkészített szorzat kifejezést, és vágjuk ki. Utána kattintsunk a zárójelek közötti «kifejezés» szövegre, majd illesszük be oda a szorzatot. Ha készen vagyunk, ezt kell látnunk:
17. ábra: Az Összesen_fizetendő nevű számított mező elkészítés után
12. Ha ezzel megvagyunk, kattintsunk az Ok gombra a Kifejezésszerkesztőben. Ezzel visszatérünk a QBE rácshoz, és láthatjuk, hogy a lekérdezésünk immár két mezőt jelenít meg. Ha viszont most megpróbálnánk átváltani Adatlap nézetre, akkor az Access azzal a hibaüzenettel fogadna, hogy „Összesítő függvény nem alkalmazható GROUP BY záradékban.” Ezt úgy tudjuk megszüntetni, hogy az Összesen_fizetendő
mező
Összesítés
sorában
a
Group
by-t
átállítjuk
Expressionre. Ezt azért kell megtennünk, mert – mint ahogy azt korábban már tárgyaltuk – Expression záradékot kell használnunk minden olyan számított mezőnél, amely összesítő függvényt tartalmaz. Ez a számított mező pedig a Sum összesítő függvényt használja.
– 44 –
Lekérdezések
18. ábra: A Síkölcsönző című feladat G részfeladatának megoldása tervező nézetben
13. Ha átállítottuk a záradékot, és átváltunk Adatlap nézetre, akkor a lekérdezés gond nélkül lefut, és ügyfelenként megjeleníti a fizetendő kölcsönzési díjat. Ezzel pedig el is érkeztünk a részfeladat végéhez. Mentsük el a lekérdezést a megadott néven, és zárjuk be.
8.3.2. OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) F részfeladat A feladatban egyetlen lekérdezéssel kell meghatároznunk, hogy melyek azok a szobák, melyekben csak egy ember száll meg. Javasolt megoldás: Megoldási terv: Mint már korábban, úgy itt is azzal kell kezdenünk, hogy meghatározzuk, mely táblákra lesz szükségünk az adatbázisból. Majd miután hozzáadtuk a lekérdezéshez, kiválasztjuk a szükséges mezőket, alkalmazzuk a megfelelő összesítő függvényt, és megfogalmazzuk a feltételeket. Javasolt megoldás lépései: 1. Kattintsunk a Létrehozás fülre, majd itt a Lekérdezéstervező gombra. 2. A feladatot elolvasva láthatjuk, hogy két táblára lesz szükségünk: a tSzállásra, hiszen ebben vannak eltárolva az egyes szobák, és a tRésztvevőkre, mert ebben tároljuk, hogy az egyes résztvevők melyik szobában szállnak meg.
– 45 –
Lekérdezések 3. Adjuk hozzá a lekérdezéshez a tSzállás táblából az Épület és a Szobaszám mezőket, valamint a tRésztvevők táblából a Szállás mezőt. 4. Kattintsunk a szalagon az Összesítés gombra (Tervezés fül). 5. Mivel arra vagyunk kíváncsiak, hogy mely szobákban szállt meg csak egy ember, ezért az Épület és Szobaszám mezőknél állítsuk be a Group by záradékot az Összesítés sorban. Így e szerint a két mező szerint lesznek csoportosítva az adatok. 6. Nincs más dolgunk, minthogy meghatározzuk, hányan laknak az egyes szobákban. A tRésztvevő tábla Szállás mezőjére alkalmazzuk a Count összesítő függvényt. Ez megszámolja, hogy az egyes szobákban hányan laknak. 7. Nekünk viszont arra van szükségünk, hogy csak azokat a szobákat adja vissza a lekérdezés eredményül, melyekben csak egy ember szállt meg. Ezért a Szállás mező feltétel sorába írjunk egy 1-est. Ha most átváltunk Adatlap nézetre, láthatjuk, hogy csak azok a szobák maradtak bent, melyekben csak egy személy szállt meg. 8. Viszont a feladat azt kérte, hogy csak a szobákat jelenítsük meg, a benne megszállók számát ne. Váltsunk vissza Tervező nézetre, és a Szállás mezőnél vegyük ki a pipát a Megjelenítés sorból. A lekérdezés most így néz ki:
19. ábra: A Jelentkezési adatbázis című feladat F részfeladatának megoldása tervező nézetben
9. Zárjuk be a lekérdezést, és mentsük a megadott néven. Ezzel kész is van a részfeladat.
– 46 –
Lekérdezések
8.3.3. OKTV 2005/2006. 3. forduló 10. feladat (Járatok) D részfeladat A feladatban egy olyan lekérdezést kell készítenünk, amely a járatok mellé kiírja az indulási és a végállomásra érkezési időt is. Fontos kikötés – és egyben könnyítés –, hogy nincs olyan járat, ami éjfélkor is közlekedne. Javasolt megoldás lépései: 1. Készítsünk egy új lekérdezést a Létrehozás fülön lévő Lekérdezéstervező gombra kattintva. 2. Adjuk hozzá a lekérdezéshez a Menetrend táblát. A tábla Érkezés mezője tartalmazza, hogy az adott járat mikor érkezik meg az adott településre. A Járatszám mező pedig azonosítja az egyes járatokat. 3. Adjuk hozzá a lekérdezéshez a Járatszám mezőt, majd kétszer az Érkezés mezőt. Azért kell kétszer, mert az adott járatnál az Érkezés mezőben szereplő legkorábbi „érkezési” időpont lesz a járat indulási időpontja az egyik, míg a legkésőbbi időpont a járat érkezési időpontja a másik végállomásra. 4. Kapcsoljuk be a rácsfelületen az Összesítés sort (Tervezés fül, Összesítés gomb). 5. Mivel járatonként kell meghatározni az indulási és érkezési időt, ezért a Járatazonosító mező Összesítés sorában állítsuk be a Group by záradékot. 6. Ezután határozzuk meg az egyes járatok indulási időpontjait az egyik végállomásról! Az első Érkezés mező Összesítés sorában állítsuk be a Min összesítő függvényt. 7. Nem maradt más hátra, minthogy meghatározzuk az egyes járatok érkezési időpontjait a másik végállomásukra. Állítsuk be a másik Érkezés mező Összesítés sorában a Max összesítő függvényt. 8. Bár a feladat nem kéri, azért, hogy a lekérdezés eredménye informatívabb legyen, célszerű lenne elnevezni azokat a mezőket, amelyeken a Min és Max öszszesítő függvényeket alkalmaztuk.16 Kattintsunk be az első Érkezés mezőnév elé, és gépeljük be azt, hogy Indulás, majd tegyünk utána kettőspontot (:). A kettőspont jelzi, hogy az előtte lévő szöveg lesz a mező neve. Tegyük meg ugya16
Alapesetben ugyanis a rendszer a következő elnevezést alkalmazza:
of<mezőnév>. A fenti példában ez úgy nézne ki, hogy MaxOfÉrkezés és MinOfÉrkezés.
– 47 –
Lekérdezések nezt a másik Érkezés mezőnél is, csak oda az Érkezés szöveget gépeljük be, és ne feledkezzünk meg utána a kettőspontról sem. Ha mindent jól csináltunk, akkor az alábbi képet kell látnunk a tervező nézetben:
20. ábra: A Járatok című feladat D részfeladatának megoldása tervező nézetben
9. Mentsük el a lekérdezést a megadott néven, majd zárjuk be. Ezzel pedig be is fejeztük a részfeladatot. További, hasonló módon megoldható feladatok: •
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) G részfeladat
•
OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) E részfeladat
•
OKTV 2005/2006. 3. forduló 10. feladat (Járatok) E részfeladat
•
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) C részfeladat
•
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) E részfeladat
•
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) C részfeladat
•
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) E részfeladat
•
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) G részfeladat
– 48 –
Lekérdezések
8.4.
Első n/utolsó n elem listázása (TOP)
A korábbiakban láthattunk példát arra, hogyan tudjuk egy halmaz legnagyobb vagy legkisebb elemét lekérdezni. Azonban sokszor nem csak az első vagy utolsó, legnagyobb vagy legkisebb elemekre vagyunk kíváncsiak. Például egy emberek adatait nyilvántartó adatbázisból szeretnénk lekérdezni az öt legöregebb ember adatait. Ennek a feladatnak a megoldásához a Visszatérés értékét kell megfelelően beállítani. Azonban mindez nem elég, hiszen mint tudjuk, a lekérdezés végeredménye egy halmaz, ami alapértelmezetten nincs rendezve. Így ahhoz, hogy a legöregebb emberek adatait kapjuk meg, a Visszatérés mellett még növekvő sorrendbe kell rendezni az adatokat születési dátum szerint is. Tehát az ilyen típusú feladatokat két lépésben lehet megoldani: •
rendezni kell az adatokat
•
be kell állítani a visszatérés értékét.
A Visszatérés értékét a Tervezés fül Lekérdezés beállításai panelen állíthatjuk be.
21. ábra: A Visszatérés beállítása
8.4.1. OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) G részfeladat Feladatunk, hogy megkeressük, melyik a legnagyobb érdeklődésre számot tartó előadás. A lekérdezés eredményében meg kell jelenítenünk az előadás nevét, és azt is, hogy hányan érdeklődnek iránta. Javasolt megoldás lépései: 1. Készítsünk lekérdezést Tervező nézetben! 2. Mivel a legnagyobb érdeklődésre számot tartó előadást keressük, ezért nyilván szükségünk lesz a tElőadás táblára. 3. Azt, hogy melyik előadáson kik vesznek részt, a tÉrdeklődés táblában tároljuk, így ezt is adjuk hozzá a lekérdezéshez. – 49 –
Lekérdezések 4. A lekérdezés eredményében meg kell jeleníteni az előadás nevét és a résztvevők számát. Ezért adjuk hozzá a lekérdezéshez a tElőadás tábla Cím mezőjét, valamint a tÉrdeklődés tábla Résztvevő mezőjét is. 5. A következő lépés, hogy meghatározzuk, melyik előadáson hányan vesznek részt. Kapcsoljuk be az Összesítés sort! 6. Mivel előadásonként kell számolnunk a résztvevőket, ezért az előadás címéhez állítsuk be az Összesítés sorba a Group by záradékot. Ezzel beállítottuk, hogy cím szerint csoportosítsuk az adatokat. 7. Most már csak a résztvevők megszámlálása van hátra. Ezt a Count összesítő függvény segítségével tehetjük meg; állítsuk be a Résztvevő mező Összesítés sorába. Ha most átváltunk Adatlap nézetbe, akkor megkapjuk, hogy az egyes előadásokon hány résztvevő van. Nekünk viszont arra az előadásra van szükségünk, ahol a legtöbben vannak. 8. Menjünk vissza Tervező nézetbe, majd a Résztvevő mezőre állítsunk be csökkenő rendezést. Így ha visszaváltunk adatlap nézetbe, legfelül lesz az az előadás, amire a legtöbben kíváncsiak. Most már csak az kell beállítanunk, hogy csak ezt az egy előadást adja vissza a lekérdezés. 9. Ha nem ott állnánk, kattintsuk a szalagon a Tervezés fülre, majd a Lekérdezés beállításai panelen a Visszatérés értékéhez gépeljük be, hogy 1.17 Ezután váltsunk át Adatlap nézetre. Láthatjuk, hogy most már csak annak az előadásnak a címét és résztvevőit adja vissza a lekérdezés, amelyen a legtöbben részt vesznek. 10. Bár a feladat nem köti ki, de a könnyebb értelmezhetőség kedvéért a „CountOfRésztvevő” mezőt nevezzük át „Résztvevők számá”-nak. Ezek után a tervező nézetben ezt kell látnunk:
17
Nem elég csak egy 1-est begépelni, és félrekattintani, ugyanis ekkor 100-at fog beírni a rendszer a mezőbe (a 100 egy előre definiált érték). Az 1-es begépelése után nyomjunk egy Backspace-t, majd egy Entert. Így a visszatérés értéke valóban 1 lesz.
– 50 –
Lekérdezések
22. ábra: A Jelentkezési adatbázis című feladat G részfeladatának megoldása tervező nézetben.
8.4.2. OKTV 2005/2006. 3. forduló 10. feladat (Járatok) C részfeladat Ebben a feladatban ábécé sorrendben kell kilistáznunk az első három olyan város nevét, ahol 7:00 előtt is megáll valamelyik járat. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben. 2. A feladat szövege szerint olyan városok neveit kell kilistáznunk, ahol 7:00 előtt is megáll a busz. Mivel ezek az adatok a Menetrend táblában rendelkezésünkre állnak (Település és Érkezés mezők), ezért csak ezt az egy táblát adjuk hozzá a lekérdezéshez. 3. Jelenítsük meg a Település és az Érkezés mezőket a lekérdezésben. 4. Mivel a városokat ábécé sorrendben kell megjeleníteni, ezért állítsunk be növekvő rendezést a Település mezőre. 5. Csak azokat a településeket kell megjeleníteni, ahol 7:00 előtt állnak meg a buszok. Az Érkezés mező feltétel sorába gépeljük be ezt a feltételt: <#7:00#. Mivel csak a települések neveit kell megjelenítenünk, ezért szedjük ki a pipát az Érkezés mező Megjelenítés sorából. 6. Ha most nézetet váltunk, láthatjuk az összes olyan települést, ahol 7 óra előtt megállnak buszok. Egyben azt is láthatjuk, hogy egy település neve többször is megjelenik, eltérő időpontokkal. Ennek az az oka, hogy több buszjárat is meg– 51 –
Lekérdezések állhat ugyanazon a településen, akár különböző időpontban is. Viszont a lekérdezés végeredményében nem szeretnénk ugyanazt a települést többször megjeleníteni. 7. Kattintsunk a szalagon a Tulajdonságlap gombra, majd a megjelenő panelen állítsuk be Igenre az Egyedi értékek tulajdonságot. Így ha Adatlap nézetre váltunk, most már minden település csak egyszer jelenik meg. 8. Nem maradt más hátra, minthogy beállítsuk azt, hogy csak az első három település neve jelenjen meg. Kattintsunk a szalagon a Tervezés fülre, majd itt a Lekérdezés beállításai panelen a Visszatérés értékéhez gépeljük be, hogy 3, majd nyomjunk egy Entert. Ekkor a következő képet kell látnunk:
23. ábra: A Járatok című feladat C részfeladatának megoldása tervező nézetben
9. Zárjuk be a lekérdezést, és mentsük a megadott néven. További, hasonló módon megoldható feladatok: •
8.5.
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) F részfeladat
Allekérdezések
Azokban az esetekben beszélünk allekérdezésről, amikor egy lekérdezés bármelyik adatát egy másik lekérdezés szolgáltatja. Ez esetben a „szolgáltató” lekérdezést allekérdezésnek, alkérdésnek, vagy alselectnek18 nevezzük. Az allekérdezés – hasonlóan minden más lekérdezéshez – egy halmazt állít elő. Ha a halmaz egyelemű (tehát egy sort és egy oszlopot tartalmaz), akkor egyszerű dolgunk
18
Ez utóbbi a választó lekérdezés SQL kódjában szereplő SELECT záradékra utaló elnevezés.
– 52 –
Lekérdezések van, hiszen azt megadhatjuk feltételnek, összehasonlíthatunk vele, stb. Viszont ha többelemű, akkor csak azt vizsgálhatjuk, hogy a főkérdésben szereplő mező elemei benne vannak, vagy nincsenek benne az allekérdés által visszaadott halmazban.19
8.5.1. OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) I részfeladat Ebben a részfeladatban azt kell meghatároznunk, hogy hányan laknak abban a városban, ahol a legtöbben laknak. Javasolt megoldás: Megoldási terv: A megoldást két részre kell bontanunk: •
Először meg kell határoznunk, hogy az egyes városokban hányan laknak.
•
Utána pedig meg kell néznünk, hogy ezek közül melyikben laknak a legtöbben.
Ezt figyelembe véve a fő lekérdezésünkhöz készíteni fogunk egy segédlekérdezést (allekérdezést) is. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben. 2. Ebben a lekérdezésben számoljuk ki, hogy az egyes városokban hányan laknak. Ehhez szükségünk lesz a Varos, Irszam_varos és a Tag táblákra; adjuk hozzá ezeket a lekérdezéshez, majd jelenítsük meg a lekérdezésben a Varos tábla varoskod mezőjét, valamint a Tag tábla kod mezőjét. Ezután kattintsunk rá a szalagon az Összesítés gombra. 3. Mivel városonként szeretnénk kiszámítani a lakosok számát, ezért a varoskod mező Összesítés sorába állítsuk be a Group by záradékot, a kod mező Összesítés sorába pedig a Count összesítő függvényt. Nevezzük el ezt a mezőt Lakosok_számának. A képernyőn ezt látjuk:
19
Ilyen esetben SQL-ben a WHERE záradékban csak az IN/NOT IN operátorok használhatóak.
– 53 –
Lekérdezések
24. ábra: A Tagnyilvántartás című feladat I részfeladatának allekérdezése
4. Zárjuk be a lekérdezést, és mentsük I_seged néven! 5. Hozzunk létre egy új lekérdezést Tervező nézetben. 6. Adjuk hozzá a lekérdezéshez az előbb létrehozott I_seged nevű lekérdezést!
25. ábra: Az I_seged nevű lekérdezés felhasználása egy másik lekérdezés elkészítéséhez
7. Már csak egy dolog maradt hátra: meg kell határoznunk, hogy mennyien laknak abban a városban, ahol a legtöbben laknak. Az I_seged nevű lekérdezésben a Lakosok_száma mezőben tároljuk, hogy az egyes városokban hányan laknak. Adjuk hozzá a lekérdezéshez ezt a mezőt. 8. Kattintsunk a szalagon az Összesítés gombra, majd alkalmazzuk a Max összesítő függvényt a mezőre. Nevezzük el ezt a mezőt Maxnak. A Tervező nézetben most ezt kell látnunk:
– 54 –
Lekérdezések
26. ábra: A Tagnyilvántartás című feladat I részfeladatának megoldása tervező nézetben
9. Váltsunk át Adatlap nézetre! Ha mindent jól csináltunk, akkor az eredmény egy darab szám. 10. Zárjuk be, és mentsük a lekérdezést I_LAKOSOK néven! További, hasonló módon megoldható feladatok: •
OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) G részfeladat
•
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) D részfeladat
•
OKTV 2006/2007. 3. forduló 5. feladat (Napelem) E részfeladat
•
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) B részfeladat
•
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) C részfeladat
•
OKTV 2007/2008. 3. forduló 7. feladat (Cégek) G részfeladat
8.6.
Tartalmazza/Nem tartalmazza
Előfordulhat olyan feladat, amikor azt kell meghatároznunk, hogy egy mező nem tartalmaz valamit, vagy nem rendelkezik valamilyen tulajdonsággal, stb. Ilyen esetekben legtöbbször allekérdezést kell készítenünk, majd azt felhasználva a feltételben az allekérdezés eredményhalmazát az IN vagy NOT IN kulcsszavak valamelyikével a főlekérdezés megfelelő mezőjéhez kell kötnünk. Másik eset, amikor olyan rekordokat kell megkeresünk, amelyek egyik mezője üres. Ilyenkor az üres mezőbe az Is NULL feltétel kerül. – 55 –
Lekérdezések Vannak olyan esetek is, amikor célszerű az ilyen típusú feladat megoldására a Nem egyezőket kereső lekérdezés varázslót használni.
8.6.1. OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) G részfeladat Ebben a részfeladatban azt kell megvizsgálnunk, hogy hány olyan budapesti tagja van az Internetfüggők Országos Egyesületének, akiknek az újság mezőjében nem szerepel semmi. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben. 2. Mivel létszámot kell számolnunk, ezért szükségünk lesz a Tag táblára. Ezen kívül szükség lesz a Varos táblára is, hiszen a lekérdezés eredményében csak a budapesti tagoknak kell szerepelniük. Ahhoz azonban, hogy a már beállított kapcsolat e két tábla között a lekérdezésben is érvényesüljön, hozzá kell még adnunk a lekérdezéshez az Irszam_varos nevű kapcsolótáblát is. 3. Adjuk hozzá a lekérdezéshez a Tag tábla kod és ujsag mezőit, valamint a Varos tábla varosnev mezőjét. 4. A következő lépés, hogy megszámoljuk városonként a tagokat. Kattintsunk a szalagon a Tervezés fülön az Összesítés gombra. Ezután a varosnev mező összesítés sorában állítsuk be a Group by záradékot, hiszen városonként számoljuk meg a taglétszámot. Ha ez megvan, akkor a kod mező Összesítés sorában állítsuk be a Count összesítő függvényt. 5. Mivel csak a budapesti tagokra vagyunk kíváncsiak, ezért a varosnev mező Feltétel sorába írjuk be, hogy „Budapest”. 6. Most már csak annyit kell tennünk, hogy meghatározzuk azokat a tagokat, akiknek az ujsag mezőjében nem szerepel semmi. Az ujsag mező Feltétel sorába írjuk be az Is NULL feltételt. Fontos, hogy a varosnev mezőre vonatkozó feltétel („Budapest”) és az ujsag mezőre vonatkozó feltétel (Is NULL) között logikai ÉS kapcsolat van, tehát a két feltételnek egy sorban kell szerepelnie. 7. Ezen kívül még fontos, hogy az ujsag mező Összesítés sorában beállítsuk a Where záradékot. Miért is? A Csoportosítás, összesítő függvények című alfeje– 56 –
Lekérdezések zetben részletesen tárgyaltuk, hogy a Where záradékot olyan feltételt tartalmazó mezőkre kell alkalmazni, amelyekkel nem csoportosítunk, és nem is tartalmaznak összesítő függvényeket. Mivel az ujsag mező pontosan ilyen, ezért be kell állítani a Where záradékot. Ha mindent jól csináltunk, akkor a Tervező nézetben ezt kell látnunk:
27. ábra: A Tagnyilvántartás című feladat G részfeladatának megoldása tervező nézetben
8. Zárjuk be a lekérdezést, és mentsük a megadott néven.
8.6.2. OKTV 2007/2008. 2. forduló 8. feladat (Kávé) B részfeladat A feladatban azokat a származási helyeket kell lekérdeznünk, amelyekről nem szállítanak Arabica kávét. Ezt a feladatot kétféleképpen is meg lehet oldani. Meg lehet oldani úgy, hogy az allekérdezést a főlekérdezésbe ágyazva SQL-en írjuk meg (ez esetben a feltételnél használnunk kell a Not In utasítást), vagy pedig külön elkészítjük a QBE rácson az allekérdezést, és használjuk a Nem egyezőket kereső lekérdezés varázslót. Most ez utóbbi megoldást mutatom be részletesen, de a lemezmellékleten megtalálható a másik megoldási mód is. Javasolt megoldás: Megoldási terv: Két lépésben fogjuk megoldani a feladatot: először elkészítjük az allekérdezést, majd azt felhasználva elkészítjük a végleges megoldást a Nem egyezőket kereső lekérdezés varázsló segítségével.
– 57 –
Lekérdezések Javasolt megoldás lépései: 1. Hozzunk létre egy új lekérdezést Tervező nézetben. Ez lesz a majdani allekérdezésünk. 2. Az allekérdezésben gyűjtsük ki azokat a származási helyeket, ahonnan szállítanak Arabica kávét! Ehhez szükségünk lesz a tBabkave táblára – hiszen ebben tároljuk a származási hely azonosítókat – valamint a tFajta táblára is (ebben pedig a fajtanevek vannak tárolva). 3. Jelenítsük meg az allekérdezés végeredményében a tBabkave tábla Szarmazas mezőjét és a tFajta tábla Nev mezőjét. Ez utóbbi mező feltételéhez írjuk be, hogy „Arabica”. Mivel csak a származási azonosítókra van szükségünk, ezért szedjük ki a Nev mező Megjelenítés sorából a pipát. Most az alábbi képet kell látnunk:
28. ábra: A Kávé című feladat B részfeladatának allekérdezése tervező nézetben
4. Zárjuk be, és mentsük a lekérdezést B_arabica_segéd néven! 5. Kattintsunk a Létrehozás fülön a Lekérdezés varázsló gombra! 6. A felugró ablakban válasszuk ki a Nem egyezőket kereső lekérdezés varázslót, majd kattintsunk a Tovább gombra. 7. A következő lépésben meg kell adnunk azt a táblát, amiből a végeredményt szeretnénk kilistázni. Ez a tSzarmazas tábla. Jelöljük ki, majd kattintsunk a Tovább gombra.
– 58 –
Lekérdezések
8. Most azt az objektumot kell megadnunk, ami a kapcsolódó rekordokat tartalmazza. Ez a B_arabica_segéd nevű lekérdezés. Jelöljük be a Lekérdezéseket, majd jelöljük ki a B_arabica_segéd nevű lekérdezést, és kattintsunk a Tovább gombra.
9. A következő lépésben össze kell kapcsolnunk a két objektumot. Ezt az összekapcsolást a tSzarmazas tábla SzAzonosíto és a B_arabica_segéd lekérdezés Szarmazas mezőin keresztül tudjuk megvalósítani, hiszen ezek a mezők tartalmazzák a származási helyek azonosítóit. Jelöljük ki mindkét mezőt, majd kattintsunk a
gombra. Így a két mező megjelent az Egyező mezők sorban. Ha
megvagyunk, kattintsunk a Tovább gombra.
– 59 –
Lekérdezések
31. ábra: Az egyező adatokat tartalmazó mezők kiválasztása
10. Az utolsó előtti lépésben azt kell eldöntenünk, hogy a lekérdezés végeredményében mely mezők jelenjenek meg. Mivel a származási helyekre vagyunk kíváncsiak, ezért mind a Nev, mind a SzAzonosíto mezők megjelenhetnek a lekérdezés végeredményében. Adjuk hozzá őket a kijelölt mezőkhöz, majd kattintsunk a Tovább gombra.
32. ábra: A megjelenítendő mezők kiválasztása
11. Az utolsó lépésben el kell neveznünk a lekérdezést. Legyen a neve B_arabica! Miután begépeltük, kattintsunk a Befejezés gombra, és tekintsük meg munkánk végeredményét.
– 60 –
Lekérdezések
33. ábra: A lekérdezés elnevezése
További, hasonló módon megoldható feladatok: •
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) E részfeladat
•
OKTV 2007/2008. 3. forduló 7. feladat (Cégek) G részfeladat
8.7.
Paraméteres lekérdezések
Az eddigi lekérdezéseknél láthattuk, hogy mindegyik egy konkrét problémát, kérdést válaszolt meg. Eddigi ismereteink alapján, ha egy emberek adatait tartalmazó adatbázisból szeretnénk kigyűjteni a budapestiek adatait, akkor a megfelelő mezők hozzáadása után a város mező feltételébe azt írnánk be, hogy „Budapest”, és ezzel meg is volnánk. De ha egy másik város lakóinak adataira lenne szükségünk, akkor vagy új lekérdezést készítenénk, vagy a meglévőben módosítanánk a városra vonatkozó feltételt. A paraméteres lekérdezések használatával nem kell egy kész lekérdezést módosítani, és nem is kell sok lekérdezést gyártani ugyanarra az alapproblémára. Ilyen típusú lekérdezések esetén futtatáskor kell megadni azokat az értékeket, amelyek szűkíteni fogják a lekérdezés végeredményét. Tehát nem előre adjuk meg, hogy mi kerüljön a Feltétel sorba a QBE rácson, hanem futás közben kérjük be a felhasználótól. Lássunk erre egy példát!
8.7.1. OKTV 2007/2008. 2. forduló 8. feladat (Kávé) E részfeladat Ebben a részfeladatban egy olyan paraméteres lekérdezést kell készíteni, ami minden Robusta kávéról megadja, hogy mennyi az ára Euróban a megadott napi árfolyamon. A megadandó paraméter annak a napnak a dátuma, aminek az árfolyamával számolni kell.
– 61 –
Lekérdezések Ha a megadott napra nincs ismert árfolyam, akkor a paraméterként megadott dátum előtti legutolsó ismert jegyzési nap Euró árfolyamát kell alkalmazni. Ezen kívül az Euróban listázott árak két tizedesjegy pontossággal és Euró pénznem formátumban kell megjeleníteni. Javasolt megoldás: Megoldási terv: A feladatot érdemes most is két részre bontani. Először készítsünk egy segédlekérdezést, melyben meghatározzuk az adott napra érvényes Euró árfolyamot, majd egy másik lekérdezésben ezt felhasználva megjelenítjük a szükséges adatokat. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben. 2. Ebben a lekérdezésben fogjuk meghatározni az adott napra érvényes Euró árfolyamot. Így a lekérdezésben csak a tArfolyam táblára lesz szükségünk. 3. Vegyük fel mindhárom mezőt a lekérdezésbe (Kod, Datum, ArfolyamHUF). 4. Mivel csak Euró árfolyammal szeretnénk dolgozni, ezért a Kod mező Feltétel sorába írjuk be, hogy „EUR”. Viszont mivel nem szeretnénk kiíratni a Kod mező tartalmát, ezért kapcsoljuk ki a mező megjelenítését. 5. Azt a napot, aminek az árfolyamával a későbbiekben számolnunk kell, a felhasználónak kell mindig megadnia. Kattintsunk a tArfolyam tábla melletti részre jobb egérgombbal, majd a legördülő menüből válasszuk ki a Paraméterek menüpontot.
34. ábra: Belépés a Paraméterek menüpontba
– 62 –
Lekérdezések 6. A megnyíló ablakban vegyünk fel egy új paramétert, melynek neve legyen Árfolyam_dátuma, típusa pedig Dátum/Idő. Ha ezzel megvagyok, kattintsunk az Ok gombra.
35. ábra: Az Árfolyam_dátuma nevű paraméter elkészítése
7. Miután elkészítettük a paramétert, kattintsunk a Datum mező Feltétel sorába jobb egérgombbal, és válasszuk ki a Szerkesztés menüpontot. Ezzel beléptünk a Kifejezésszerkesztőbe. 8. Most gondoskodunk arról, hogy ha olyan dátumot adunk meg, aminek nem ismerjük az árfolyamát, akkor a legutolsó ismert dátum árfolyamával dolgozzon a rendszer. A Kifejezéselemeknél kattintsunk a Lekérdezés1 nevű objektumra. Így a Kifejezésértékeknél megjelenik az előbb létrehozott Árfolyam_dátuma nevű paraméter. Kattintsunk erre duplán, hogy megjelenjen a szerkesztő dobozban. Most már csak annyit kell tennünk, hogy biztosítjuk a fenti feltételt; gépeljük be a paraméter elé azt, hogy <=. Most ezt kell látnunk a Kifejezésszerkesztőben:
36. ábra: A Datum mezőre vonatkozó feltétel elkészítése
– 63 –
Lekérdezések 9. Ha átváltunk Adatlap nézetre, és futtatjuk a lekérdezést, láthatjuk, hogy a paraméterként megadott dátum előtti összes dátumot és árfolyamot megjeleníti a lekérdezés20. Hogy mindig csak egyet jelenítsen meg, állítsuk a Visszatérés értékét 1-re. Ez azonban még nem lesz elég, hiszen így már csak egy rekordot ad vissza a lekérdezés, de nem azzal a dátummal, amit megadtunk neki. Hogy az ahhoz a dátumhoz tartozó rekorddal térjen vissza a lekérdezés, állítsunk be a Datum mezőre csökkenő rendezést. Erre azért van szükség, hogy meglévő dátum esetén azt adja vissza, ismeretlen dátum esetén pedig a legutolsó ismertet. Ha mindent jól csináltunk, akkor most ezt kell látnunk:
37. ábra: A Kávé című feladat E részfeladatának segédlekérdezése tervező nézetben
10. Zárjuk be a lekérdezést, és mentsük E_robusta_segéd néven! 11. Hozzunk létre egy új lekérdezést Tervező nézetben. 12. A feladat csak az Euróra átváltott árakra kíváncsi, viszont minket segít a lekérdezés készítése közben még néhány plusz adat. Épp ezért adjuk hozzá a lekérdezéshez a tBabkave, a tFajta, a tSzarmazas és a tPorkoles táblákat, valamint az előbb létrehozott E_robusta_segéd lekérdezést!
20
Ha megnézzük a tArfolyam tábla tartalmát, láthatjuk, hogy csak 2007.06.11. és 2007.07.27. között tartalmaz adatokat. Erre ügyeljünk, mikor megadjuk a dátum paramétert.
– 64 –
Lekérdezések 13. Mindenképpen szűkítenünk kell a lekérdezés eredményét a Robusta kávéra, így adjuk hozzá a lekérdezéshez a tFajtai tábla Nev mezőjét. A mezőt nevezzük el Fajtának, a feltétel sorába pedig írjuk be, hogy „Robusta”. 14. Tegyük láthatóvá, hogy honnan származnak ezek a kávék: jelenítsük meg a tSzarmazas tábla Nev mezőjét Származás néven. 15. Lássuk azt is, hogy milyen módon pörkölték ezeket a kávékat: jelenítsük meg a tPorkoles tábla Nev mezőjét Pörkölés néven. 16. Utolsó lépésként pedig számítsuk ki az Euróban mért árát a kávéknak. Kattintsunk jobb egérgombbal a Pörkölés melletti üres oszlop Mező sorába, majd lépjünk be a Kifejezésszerkesztőbe. Először is nevezzük el ezt a számított mezőt EuróÁrnak, majd tegyünk utána kettőspontot (:). Az Euróban mért árát úgy tudjuk kiszámítani a kávéknak, hogy elosztjuk a Ft-ban mért árát az aktuális napi árfolyammal. Ezt úgy tudjuk megtenni, hogy a tBabkave Ar mezőjét elosztjuk a korábban már létrehozott E_robusta_segéd lekérdezés ArfolyamHUF mezőjével.
38. ábra: A kávéárak Euróban történő meghatározása
17. Zárjuk be az ablakot, és nézzük meg munkánk gyümölcsét: minden rendben van, kivéve, hogy a rendszer nem €-ban jeleníti meg az árakat, és hogy nem csak két tizedesjegy pontossággal. 18. Kattintsunk a szalagon a Tulajdonságlap gombra, majd kattintsunk rá az EuróÁr mező nevére. A Tulajdonságlapon a Formátumhoz írjuk be, hogy Euró, majd nyomjunk egy Entert, és futtassuk a lekérdezést. Most már minden úgy jelenik meg, ahogy azt szerettük volna. A Tervező nézetben ezt kell látnunk: – 65 –
Lekérdezések
39. ábra: A Kávé című feladat E részfeladatának megoldása tervező nézetben
19. Mentsük a lekérdezést E_robusta néven, és zárjuk be. További, hasonló módon megoldható feladatok: •
8.8.
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) H részfeladat
Beépített függvények használata
Sok esetben felmerülhet, hogy egy-egy feladatot mennyivel könnyebben is meg lehetne oldani, ha rendelkezésünkre állna egy – a táblázatkezelőkben már megszokott – függvénykészlet, például dátum- vagy szövegfüggvények. Ezek közül jó néhány be lett építve az Access-be. Először kiemelek néhány függvényt, röviden ismertetem a szintaktikájukat a legfontosabb paraméterekkel, majd a példafeladatokban megnézzük, hogyan is működnek a gyakorlatban. A nem kötelező paramétereket – az Access súgójához hasonlóan – szögletes zárójelek („[„ és „]”) közé teszem. Az Access beépített függvényeiről részletesen lásd az Irodalomjegyzék 7-es számú hivatkozását. •
Százalékformátum21: e függvény egy százalékként formázott kifejezést ad viszsza. A függvény felépítése: Százalékformátum (kifejezés; [pontosság]; [vezetőnulla]; [negatívzárójelben]; [ezreselválasztó]). E paraméterek közül csak a kifejezés kötelező, hiszen ezt alakítja százalék formátumúvá.
•
Date(): az aktuális rendszerdátumot adja vissza.
21
Angolul FormatPercent. Részletesen lásd: http://office.microsoft.com/hu-hu/access-help/formatpercentfuggveny-HA001228843.aspx?CTT=5&origin=HA010131676 [Utolsó megtekintés: 2012.11.03.]
– 66 –
Lekérdezések •
Year(): egy dátum típusú értékből csak az évet adja vissza.
•
IIF: lényegében az Excel Ha() függvényének felel meg. Megvizsgál egy logikai kifejezést, majd attól függően, hogy a logikai kifejezés igaz vagy hamis, visszaadja a paraméterekben megadott igazrészt vagy hamisrészt.
•
MID: ez megfelel az Excelből már ismert Közép függvénynek. Egy karakterláncból ad vissza karaktereket. Paraméterben kell megadni, hogy a karakterlánc hányadik karakterétől hány karaktert szeretnénk megkapni.
További, áttekintésre javasolt beépített függvények: InStr, Left, Month, Val.
8.8.1. OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) C részfeladat Ebben a feladatban meg kell határoznunk, hogy a nemzeti parkok fokozottan védett területe átlagosan hány százaléka a védett területeknek. Az eredményt százalék formátumban, két tizedesjegy pontossággal kell megjeleníteni. Javasolt megoldás: Megoldási terv: Maga a feladat roppant egyszerű és hasonlít a már korábban látott összesítő függvényeket használó feladatokhoz. Ami egyedül újdonság, az a százalék formátumra történő megformázás. Ehhez most a Százalékformátum nevű függvényt fogjuk használni. Javasolt megoldás lépései: 1. Készítsünk egy új lekérdezést Tervező nézetben. 2. Mivel a nemzeti parkokra vonatkozó összes adat a Terület táblában található, ezért csak ezt adjuk hozzá a lekérdezéshez. 3. Adjuk hozzá a lekérdezéshez a Típus mezőt. Ezt nem kell megjelenítenünk, úgyhogy vegyük ki a pipát a Megjelenítésből. Viszont csak a nemzeti parkok adataival kell számolnunk, úgyhogy a Feltétel sorába írjuk be feltételnek azt, hogy „NP”. Azért ezt, mert a Típus mezőben az azonosítók szerepelnek. 4. A lekérdezés tényleges tartalmát egy számított mező fogja visszaadni. Kattintsunk jobb egérgombbal a következő üres Mezőbe, és lépjünk be a Kifejezésszerkesztőbe.
– 67 –
Lekérdezések 5. Először is, nevezzük el a mezőt Átlagnak, majd gépeljünk utána egy kettőspontot (:). Ezután számoljuk ki a fokozottan védett területek és a védett területek arányát. Nincs más dolgunk, mint elosztani a FokozottMéret mezőt a VédettMéret mezővel. 6. Ha ezt megtettük, akkor ezt az értéket átlagolnunk kell. Tegyük zárójelbe a két mezőt, és írjuk a zárójel elé, hogy Avg. Ezzel lényegében meghívtuk az átlag függvényt.
Jelenleg
ez
a
kifejezés
szerepel
a
mezőnév
után:
Avg([Terület]![FokozottMéret]/[Terület]![VédettMéret]). 7. Ezután már csak százalék formátumúra kell alakítanunk ezt a mezőt. Nyissuk ki a Függvények gyűjteményt, majd itt kattintsunk a Beépített függvényekre, és a Kifejezésértékeknél keressük meg a Százalékformátum nevű függvényt, majd kattintsunk rá duplán. Ezzel beszúrtuk a függvényt. Most már csak fel kell töltenünk a paramétereit. 8. A függvény elől töröljük ki a «Kif» szöveget a függvény paraméterlistájával együtt (csak a nyitó és csukó zárójeleket hagyjuk meg). A két zárójel közé illeszszük be az átlagszámító kifejezésünket, majd tegyünk egy pontosvesszőt (;) utána, és írjunk be egy 2-t. Ezzel azt mondtuk meg a függvénynek, hogy a százalék formátumra alakítandó kifejezés két tizedesjegy hosszúságú legyen. Ha mindent jól csináltunk, akkor most ezt látjuk magunk előtt:
40. ábra: A SzázalékFormátum függvény használata
– 68 –
Lekérdezések 9. Ha ezzel megvagyunk, nyomjuk meg az Ok gombot. Váltsunk át Adatlap nézetre, és nézzük meg munkánk gyümölcsét. Tervező nézetben ezt kell látnunk:
41. ábra: A Nemzeti park című feladat C részfeladatának megoldása tervező nézetben
10. Mentsük a lekérdezést C_átlag néven, és zárjuk be.
8.8.2. OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) F részfeladat Ebben a részfeladatban egy olyan lekérdezést kell készítenünk, ami évenként megadja, hogy hány küldetés volt. Annyival van megfűszerezve a dolog, hogy tudjuk azt is, hogy az aktuális évben nem volt még űrhajó indítás. Ezen túl a lekérdezés csak azt a 17 évet adja vissza eredményül, amikor a legtöbb kilövés volt. A kilövések számát is meg kell jeleníteni, és eszerint kell csökkenő sorrendbe rendezni az adatokat. Javasolt megoldás: Megoldási terv: Ez a feladat a dátum függvényekre van kihegyezve. A dolgunk rendkívül egyszerű: meg kell számolnunk, hogy évenként hány űrhajót lőttek fel annyi megszorítással, hogy az aktuális – tehát nem kézzel rögzítve az éppen aktuális évet – évben még nem volt kilövés. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben.
– 69 –
Lekérdezések 2. Most csak egyetlen táblára lesz szükségünk: az Urhajokra, hiszen ez tartalmazza a kilövési éveket (Ev) valamint a Sorszam mező segítségével meg is tudjuk számolni az elindított űrhajókat. 3. Adjuk hozzá a lekérdezéshez az Ev és a Sorszam mezőket. Ez utóbbit nevezzük el Darabnak, hiszen ennek segítségével fogjuk megszámolni az űrhajókat. Ha már itt vagyunk, akkor állítsuk be a Darab mezőre a csökkenő sorrendet is. 4. Kattintsunk a szalagon az Összesítés gombra. Mivel évenkénti csoportosításban szeretnénk megszámolni az űrhajókat, ezért az Ev mező Összesítés sorában állítsuk be a Group by záradékot. A Darab mező összesítés sorában pedig állítsuk be a Count összesítő függvényt. Ezzel megszámoljuk, hogy az egyes években hány űrhajót lőttek fel. 5. Készítsük el az évekre vonatkozó feltételt! Kattintsunk jobb egérgombbal az Ev mező Feltétel sorában, és lépjünk be a Kifejezésszerkesztőbe. Itt a Beépített függvények Dátum/idő kategóriáján belül keressük meg a Date() függvényt. Ez a függvény visszaadja nekünk az aktuális dátumot22. Viszont nekünk nem az egész dátumra van szükségünk, hanem csak az évre. Ennek kinyerésére szolgál a Year() függvény. A Year() függvény paramétere legyen a Date() függvény (Year(Date())). Viszont ez a feltétel azt mondja jelenleg, hogy azoknak a kilövéseknek a számát adja meg, amelyeket idén hajtottak végre. Viszont tudjuk, hogy az aktuális évben nem hajtottak végre kilövést. Így az eddigi feltételünk elé be kell írni egy < (kisebb) jelet. A Kifejezésszerkesztőben most ezt látjuk:
22
Egész pontosan az aktuális rendszerdátumot. Tehát ha valamilyen oknál fogva rosszul van beállítva a rendszerdátum, akkor rossz dátummal fogunk dolgozni a Date() függvény használata esetén.
– 70 –
Lekérdezések
42. ábra: Az Ev mezőre vonatkozó feltétel
6. Nyomjunk egy Ok gombot, és ezzel be is zártuk a Kifejezésszerkesztőt. Már csak egy dolog maradt hátra. Be kell állítanunk, hogy csak azt a 17 évet jelenítse meg a lekérdezés, amelyekben a legtöbb űrhajót lőtték fel. Állítsuk be a szalagon a Visszatérés értékét 17-re. Ha mindent jól csináltunk, ezt látjuk magunk előtt:
43. ábra: Az Űrhajók című feladat F részfeladatának megoldása tervező nézetben
7. Mentsük el a lekérdezést F néven, és zárjuk is be.
8.8.3. OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) E részfeladat Ebben a feladatban egy lekérdezés segítségével meg kell határozni, hogy hány „Kicsi”, „Közepes” és „Nagy” terület van. Kicsinek számít az a terület, melynek védett mérete kisebb, mint 1000 hektár, nagynak pedig az, amelynek a védett területe legalább 10000 hektár. A kettő közötti, tehát nagyobb vagy egyenlő 1000 hektár és a kisebb, mint 10000 hektár védett területtel rendelkező terület számít közepesnek. – 71 –
Lekérdezések Javasolt megoldás: Megoldási terv: Első lépésben meg kellene határozni, hogy mely területek melyik kategóriába esnek, majd utána megszámolni, hogy melyik kategóriába hány ilyen terület tartozik. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben. 2. Adjuk hozzá a lekérdezéshez a Terület táblát, hiszen minden számunkra fontos adat ebben található. 3. Kezdjük a – látszólag – nehezebb feladattal: határozzuk meg az egyes méretkategóriákat! Kattintsunk jobb egérgombbal a legelső Mező sorba, és nyissuk meg a Kifejezésszerkesztőt. Nevezzük el a mezőt Méretnek. A Beépített függvények közül keressük ki az IIF függvényt, és kattintsunk rá duplán, majd töröljük ki a függvény előtti «Kif» szöveget. 4. Kezdjük a közepes méret meghatározásával. Kattintsunk az IIF első paraméterére, a «kifejezés»-re, majd formalizáljuk a szabály egyik részét: közepesnek számít az a terület, aminek a védett területe nagyobb vagy egyenlő mint 1000 hektár. Ez formalizálva így néz ki: [Terület]![VédettMéret]>=1000. 5. Nézzük, mit csináljon a függvény, hogy ha igaz ez az állítás. Kattintsunk az «igazrész»-re. Ha a védett terület mérete nagyobb vagy egyenlő mint 1000 hektár, akkor egy újabb logikai vizsgálatot kell végeznünk: meg kell néznünk, hogy nagyobb, vagy egyenlő-e, mint 10000 hektár. Ha igen, akkor ki kell íratnunk, hogy „Nagy”, különben azt íratjuk ki, hogy „Közepes”. Formalizálva tehát az első (külső) IIF függvényünk igazrésze így néz ki: IIf([Terület]![VédettMéret] >= 10000; "Nagy"; "Közepes"). 6. Végül lássuk, mi történjen, ha hamis ez az állítás. Abban az esetben, ha az eredeti állítás hamis, tehát a védett terület mérete nem nagyobb vagy egyenlő mint 1000 hektár, akkor kis méretű területről van szó, tehát írassuk ki azt, hogy „Kicsi”. Ezzel el is készült ez a mező:
– 72 –
Lekérdezések
44. ábra: A méretkategóriák meghatározása
Az Ok gomb megnyomásával zárjuk be a Kifejezésszerkesztőt. 7. Mivel a Méret mező szerint szeretnénk megszámolni a területeket, ezért a szalagon a Tervezés fülön kattintsunk az Összesítés gombra, és a Méret mező Összesítés sorában állítsuk be a Group by záradékot. 8. Az egyes mezőket legegyszerűbben az Azonosító mező segítségével tudjuk megszámolni; adjuk hozzá a lekérdezéshez. Nevezzük el a mezőt Darabnak, majd az Összesítés sorában állítsuk be a Count összesítő függvényt. 9. Váltsunk át Adatlap nézetre, és ha mindent jól csináltunk, akkor láthatjuk, hány darab kicsi, közepes és nagy védett terület van. Tervező nézetre visszaváltva ezt kell látnunk:
– 73 –
Lekérdezések
45. ábra: A Nemzeti park című feladat E részfeladatának megoldása tervező nézetben
10. Zárjuk be a lekérdezést, és mentsük E néven. További, hasonló módon megoldható feladatok: •
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) B részfeladat
•
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) D részfeladat
•
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) D részfeladat
8.9.
Bonyolultabb lekérdezések
Ebben az alfejezetben olyan részfeladatokat ajánlok megoldásra, melyekben az összes ezt megelőző lekérdezési témakör, feladattípus egyszerre megtalálható, továbbá olyan feladatok is szerepelnek itt, amiket egyik korábbi kategóriába sem tudtam besorolni.
8.9.1. OKTV 2005/2006. 3. forduló 10. feladat (Járatok) F részfeladat A feladat roppant egyszerű(nek hangzik): ki kell listázni azon járatok azonosítóját, amelyek megállnak Agárdon és Baracskán is. Javasolt megoldás: Megoldási terv: Egyetlen probléma vetődik fel az egész feladat kapcsán. Hogyan fogalmazzuk meg egy mezőre, hogy megáll a járat Agárdon és Baracskán is? Hiszen ha azt írnánk be Település mező feltételébe, hogy „Agárd And Baracska”, akkor – teljesen jogosan – a lekérdezés egy üres halmazt adna vissza. Hiszen a Település mezők vagy azt tartalmaz– 74 –
Lekérdezések zák, hogy „Agárd” vagy azt, hogy „Baracska”. De akkor mégis hogyan tudjuk megoldani a feladatot? A válasz egyszerű: kétszer használjuk fel a Menetrend táblát. Javasolt megoldás lépései: 1. Készítsünk egy lekérdezést Tervező nézetben. 2. Adjuk hozzá kétszer a Menetrend táblát a lekérdezéshez. 3. Jelenítsük meg a Menetrend tábla Járatazonosító mezőjét, és adjuk hozzá a lekérdezéshez a Menetrend és a Menetrend_1 táblák Település mezőit is. 4. Írjuk be a Menetrend tábla Feltétel sorába azt, hogy „Agárd”, a Menetrend_1 tábláéba pedig azt, hogy „Baracska”. Váltsunk át Adatlap nézetre. 5. Láthatjuk, hogy rengetegszer megkaptuk ugyanazt, sőt, ha az adatbázis tábláiban elkezdünk szétnézni, azt is láthatjuk, hogy olyan járatazonosítók is megjelentek a lekérdezés eredményében, amelyek nem is állnak meg mindkét településen. Például az 1-es és 5-ös számú járatok. Mi lehet ennek az oka? A válasz rendkívül egyszerű: nincsenek összekapcsolva a táblák. És mivel nincsenek összekapcsolva, ezért az adatbázis-kezelő rendszer az egyik tábla minden sorát összepárosította a másik tábla minden sorával, vagyis direktszorzatot képzett. Hogy lehet ezt megszüntetni? 6. Kapcsoljuk össze a lekérdezésen belül a Menetrend és a Menetrend_1 táblákat a Járatazonosító mezőkön keresztül23. 7. Ha ezután Adatlap nézetre váltunk, láthatjuk, hogy most már csak azok a járatok jelennek meg, amelyeknél egyszerre szerepel Agárd és Baracska is a Település mezőkben. Viszont mivel a feladat csak a járatazonosítókat kérte, ezért a Megjelenítést kapcsoljuk ki a Település mezőkre. Ha mindent jól csináltunk, akkor ezt kell látnunk a Tervező nézetben:
23
Ezt az összekapcsolást nevezzük self-joinnak.
– 75 –
Lekérdezések
46. ábra: A Járatok című feladat F részfeladatának megoldása tervező nézetben
8. Mentsük a lekérdezést F néven, és zárjuk is be. További, hasonló módon megoldható feladatok: •
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) H részfeladat (önmagára hivatkozó tábla)
•
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) C részfeladat
•
OKTV 2006/2007. 3. forduló 5. feladat (Napelem) B részfeladat
•
OKTV 2006/2007. 3. forduló 5. feladat (Napelem) C részfeladat
•
OKTV 2006/2007. 3. forduló 5. feladat (Napelem) D részfeladat (önmagára hiv. tábla)
•
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) D részfeladat
9. Hozzáfűző lekérdezések Ezen lekérdezés típus segítségével új rekordokat rögzíthetünk egy táblába. Ez a tábla akár egy másik adatbázisban is elhelyezkedhet. Létrehozásának menete hasonló a választó lekérdezéséhez. Viszont fontos, hogy itt előbb állítsuk be a lekérdezés típusát „Hozzáfűző”-re, majd válasszuk ki a táblát, amihez szeretnénk valamit hozzáfűzni.
– 76 –
Lekérdezések
Ezután pedig adjuk hozzá a lekérdezéshez azokat a táblákat, amelyekre feltételeket kell megfogalmaznunk. Fontos: azt a táblát, amihez hozzáfűzzük a rekordokat, csak akkor kell itt hozzáadnunk a lekérdezéshez, hogyha valamelyik mezőjére feltételt kell megfogalmaznunk, különben nem. A táblák hozzáadása után a forrás táblákból azokat az oszlopokat kell felsorolni, amelyek adatait szeretnénk hozzáfűzni a feltöltendő táblához. A „Hozzáfűzés” sorban a feltöltendő tábla minden mezőjét fel kell sorolni, kivéve, ha az adott mezőt üresen akarjuk hagyni, vagy ha a mező automatikusan töltődik fel adatokkal (mert például számláló típusú). Abban az esetben, ha a mező értékét konstansokkal szeretnénk feltölteni, akkor a QBE rács „Mező” cellájába kell beírnunk a konstanst, és el is kell neveznünk a mezőt. Minden más esetben hozzá kell adnunk a forrás táblák megfelelő mezőit, és a „Hozzáfűzés” sorban beállítani, hogy a feltöltendő tábla melyik mezőjébe írja az adatot. Végül adjuk hozzá a lekérdezéshez a forrás táblák azon mezőit, amelyekre feltételt kell megfogalmaznunk. Itt csak a „Feltétel” sort töltsük ki, a „Hozzáfűzés” sor maradjon üresen! Ennél a lekérdezésnél – az összes többi adatmanipuláló lekérdezéssel egyetemben – nem elég csak átváltanunk Adatlap nézetre, hogy lássuk az eredményét. Ezt a le-
kérdezést külön le kell futtatnunk, a Tervezés fülön látható
– 77 –
gombra kattintva.
Lekérdezések
9.1.
OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) H
részfeladat Ebben a részfeladatban a konferencián résztvevők közül a „B” épület 202-es szobájának lakói úgy döntenek, hogy érdekli őket a Funkcionális konyhaművészet című előadás. Viszont eddig egyikük sem érdeklődött a téma iránt. A feladatunk az, hogy készítsünk hozzáfűző lekérdezést, ami a szoba lakóit felveszi érdeklődőként az előadáshoz. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben. 2. Mielőtt hozzáadnánk a lekérdezéshez a szükséges táblákat, állítsuk át a lekérdezés típusát Hozzáfűzőre, majd adjuk meg cél táblának a tÉrdeklődés táblát.
48. ábra: Hozzáfűzés a tÉrdeklődés táblához
3. Ezután adjuk hozzá a lekérdezéshez a forrás táblákat: a tElőadást, a tRésztvevőt és a tSzállást. 4. Először adjuk hozzá azokat a mezőket a lekérdezéshez, amelyek adatait szeretnénk hozzáfűzni a tÉrdeklődés tábla megfelelő mezőihez: a tElőadás tábla Előadás mezőjét és a tRésztvevő tábla Résztvevő mezőjét. Ha valamiért nem jelenne meg automatikusan, akkor a Hozzáfűzés sorban az Előadás mezőhöz állítsuk be a tÉrdeklődés tábla Előadás mezőjét, majd a Résztvevő mezőhöz a tÉrdeklődés Résztvevő mezőjét. Ezzel biztosítjuk, hogy a megfelelő mezőkbe szúrja be a lekérdezés az adatokat.
– 78 –
Lekérdezések 5. Következő feladatunk, hogy hozzáadjuk a lekérdezéshez azokat a mezőket, amelyekre feltételeket kell megfogalmaznunk: a tElőadás tábla Cím mezőjét, a tSzállás Épület és Szobaszám mezőit. 6. Ha ezzel megvagyunk, akkor fogalmazzuk meg az előbb hozzáadott mezőkre vonatkozó feltételeket: a. a Cím mező Feltétel sorába írjuk be az előadás címét: „Funkcionális konyhaművészet”, b. az Épület mező Feltétel sorába azt, hogy „B”, és c. a Szobaszám mező Feltétel sorába azt, hogy 202. Fontos, hogy ezen mezők Hozzáfűzés sorába semmit ne írjunk! 7. Ezzel el is készült a lekérdezés. Már csak futtatnunk kell. Kattintsunk a szalagon
a
gombra. Ekkor az Access felhívja a figyelmünket arra, hogy egy hozzá-
fűző lekérdezést szeretnénk futtatni. Kiírja, hogy hány sor lesz hozzáfűzve a táblához, és jelzi, hogy ezt a módosítást nem lehet visszavonni. 8. Ha mindent jól csináltunk, akkor ezt kell látnunk a Tervező nézetben:
9. Mentsük a lekérdezést H néven, és zárjuk be.
– 79 –
Lekérdezések További, hasonló módon megoldható feladatok: •
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) C részfeladat
•
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) D részfeladat
•
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) E részfeladat
•
OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) F részfeladat
10. Frissítő lekérdezések Ezzel a lekérdezés típussal módosítani tudjuk a táblákban lévő rekordok tartalmát. Elkészítésének menete megegyezik a Hozzáfűző lekérdezés elkészítésével. A különbség csak annyi, hogy a lekérdezés típusát itt Frissítőre kell állítani. Ekkor a QBE rácson megjelenik a Módosítás sor.
50. ábra: Frissítő lekérdezés tervező nézetben
Hasonlóan a hozzáfűző lekérdezéshez, itt is vegyük fel a módosítani kívánt táblákat, valamint azokat, amelyekre valamilyen feltételt kell megfogalmaznunk. Ezután rögzítsük a módosítani kívánt mezőket, majd ezen mezők Módosítás sorába írjuk be, hogy mire szeretnénk módosítani a mezők tartalmát. Ha ezek megvannak, akkor már csak azokat a mezőket kell felvennünk, amikre valamilyen feltételt kell megfogalmaznunk. Miután felvettük őket, a Feltétel sorukba írjuk be a rájuk vonatkozó feltételeket.
– 80 –
Lekérdezések
10.1. OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) B részfeladat Ebben a részfeladatban egy olyan frissítő lekérdezést kell készíteni, ami a Terület táblában kitölti a Típus és a TörzskönyviSzám mezőket. A feltétel, ami alapján a Típus mezőt fel kell tölteni az, hogy ha a Típus táblában található Megnevezés, mint szöveg, szerepel a Terület tábla Megnevezés mezőjében, akkor a Típus tábla ehhez tarozó Típus mezőjének értéke kerüljön a Terület tábla Típus mezőjébe. A feladat nehézsége nem a lekérdezés típusában rejlik, hanem abban, hogy a TörzskönyviSzám mezőbe a Terület tábla Megnevezés mezőjében található szövegből csak az a szövegrész kerülhet, ami zárójelek között van. Ebből már látszik, hogy szükségünk lesz néhány beépített szövegfüggvényre is a feladat sikeres megoldásához. Javasolt megoldás lépései: 1. Hozzunk létre egy új lekérdezést Tervező nézetben. 2. Mielőtt hozzáadnánk a lekérdezéshez a szükséges táblákat, állítsuk a lekérdezés típusát Frissítőre. Ezután adjuk hozzá a lekérdezéshez a Terület és Típus táblákat. 3. A módosítandó mezők: a Terület tábla Típus és TörzskönyviSzám mezői; vegyük ezeket fel a QBE rácsra. A feltételt, ami alapján fel kell töltenünk ezt a két mezőt, a Terület tábla Megnevezés mezőjére kell megfogalmaznunk; vegyük ezt is fel. 4. Kezdjük a feltétel megfogalmazásával! A feladat szövege szerint azt kell vizsgálnunk, hogy a Terület tábla Megnevezés mezőjében lévő szöveg tartalmazza-e a Típus tábla Megnevezés mezőjében található szöveget. Mivel nem pontos egyezést kell vizsgálnunk, hanem csak azt, hogy az egyik karakterlánc tartalmazza-e a másik karakterláncot, ezért a Like kifejezést kell használnunk. Maga a feltétel így néz ki: Like "*"+[Típus]![Megnevezés]+"*". Mivel most nem konkrét szöveget keresünk, hanem egy mező tartalmában lévő karakterláncot, ezért a Like *[Típus]![Megnevezés]* nem működik, a * karakterhez a + operátorral hozzá kell konkatenálnunk a mezőt. 5. Következő lépésként adjuk meg, hogy mire módosítsuk a Terület tábla Típus mezőjét, ha az előbb elkészített feltétel igazzá válik. A feladat azt kéri, hogy a
– 81 –
Lekérdezések Típus tábla Típus mezőjének tartalma kerüljön a mezőbe. Kattintsunk jobb egérgombbal
a
Típus
mező
Módosítás
sorába,
majd
lépjünk
be
a
Kifejezésszerkesztőbe. Itt a Típus tábla Típus mezőjére kattintsunk duplán, majd kattintsunk az Ok gombra. 6. Utolsó feladatunk, hogy a Terület tábla Megnevezés mezőjéből kiszedjük a zárójelek közötti karakterláncot, és azt szúrjuk be a TörzskönyviSzám táblába. Ennek a megoldásához szükségünk lesz a Mid és az InStr beépített szövegfüggvényekre. 7. A Mid függvény első paramétere az a karakterlánc, amiből ki akarunk nyerni egy rész karakterláncot. Ez a Terület tábla Megnevezés mezője. 8. Második paramétere annak a karakternek a sorszáma, ahonnan kezdődik a rész karakterlánc, amire szükségünk van. Mivel minden rekordban máshol van az első nyitó zárójel, ezért meg kell azt keresnünk. Erre szolgál az InStr függvény. Ennek első24 – kötelező – paramétere az a karakterlánc, amiben a keresést végre kell hajtani. Ez a Terület tábla Megnevezés mezője. 9. Az InStr függvény második – szintén kötelező – paramétere az a karakter vagy karakterlánc, amit keresünk. Jelen esetben ez a nyitó zárójel („(”) utáni első karakter. Tehát most így néz ki a megoldásunk: Mid ([Terület]![Megnevezés]; InStr([Terület]![Megnevezés]; "(")+1; ) 10. A Mid függvény harmadik paramétere pedig megadja, hogy a kezdő karaktertől milyen hosszúságú karakterláncot kell visszaadnia. Ez szintén rekordonként változik, tehát ezt is ki kell számolnunk. A kiszámolásban az előzőleg használt InStr függvény lesz segítségünkre. 11. Magát a karakterlánc hosszát a következőképpen tudjuk kiszámolni: a. Tudjuk, hogy a keresett szöveg csukó zárójellel („)”) végződik. b. Innen kell visszafelé megszámolnunk a karakterek hosszát egészen a nyitó zárójelig („(”). Viszont maga a nyitó zárójel már nem kell, tehát a karakterlánc hossza eggyel rövidebb lesz.
24
Igazából az első paraméterében – kezdet – lehet megadni, hogy melyik pozíciótól kezdődjön a keresés. Ez azonban nem kötelező paramétere az InStr függvénynek.
– 82 –
Lekérdezések Ezt a gondolatmenetet valósítjuk meg a következőkben! 12. Keressük meg a csukó zárójelet a Terület tábla Megnevezés mezőjében! Erre a megoldás: InStr([Terület]![Megnevezés];")"). 13. Innen számoljunk vissza a nyitó zárójel előtti karakterig. Erre a megoldás: InStr([Terület]![Megnevezés];"(")-1. 14. Ennek a két függvénynek a különbsége adja meg a karakterlánc hosszát: InStr([Terület]![Megnevezés];")")-InStr([Terület]![Megnevezés];"(")-1 A fenti képlet lesz a Mid függvény harmadik paramétere, vagyis a karakterlánc hossza. 15. Ha mindent jól csináltunk, akkor a Kifejezésszerkesztőben ezt kell látnunk:
51. ábra: A TörzskönyviSzám mező megfelelő adattal való feltöltésére szolgáló képlet
16. Az Ok gomb megnyomásával zárjuk be a Kifejezésszerkesztőt. Ezzel el is készült a frissítő lekérdezésünk. A Tervező nézetben ezt kell látnunk:
– 83 –
Lekérdezések
17. Futtassuk a lekérdezést a
gombra kattintva.
18. Miután lefutott a lekérdezés, zárjuk be, és mentsük B néven. További, hasonló módon megoldható feladatok: •
OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) G részfeladat
•
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) C részfeladat
11. Törlő lekérdezések Ezzel a lekérdezés típussal tudunk a táblákból rekordokat törölni. Ha olyan táblából szeretnénk rekordot törölni, ami úgy áll kapcsolatban egy másik táblával, hogy a kapcsolatra beállítottuk a Hivatkozási integritás megőrzése és a Kapcsolt mezők kaszkádolt törlése opciókat, akkor a másik tábla kapcsolódó rekordjai is törlődni fognak. Hasonlóan a hozzáfűző és a frissítő lekérdezésekhez, itt is – miután beállítottuk a lekérdezés típusát – először vegyük fel azt a táblát, amiből törölni szeretnénk, majd azokat a táblákat amelyeknek a mezőire feltételt kell megfogalmaznunk. Ha ez megvan, akkor adjuk meg, hogy melyik táblából szeretnénk törölni. Ezt úgy tehetjük meg, hogy a mezőhöz beírjuk a .*-ot, majd a Törlés sorban beállítjuk a From záradékot. Ezután vegyük fel azokat a mezőket, melyekre feltételt kell megfogalmaznunk. Ezeknek a Törlés sorába állítsuk be a Where záradékot, majd a Feltétel sorukba készítsük el a feltételt.
– 84 –
Lekérdezések
53. ábra: Példa törlő lekérdezésre
11.1. OKTV 2005/2006. 2. forduló 7. feladat (Torino 2006) F részfeladat Ebben a részfeladatban csupán annyit kell tennünk, hogy ki kell törölnünk az adatbázisból azokat a programokat, amelyeket 2006.02.18-ára terveztek. Javasolt megoldás lépései: 1. Hozzunk létre egy lekérdezést Tervező nézetben! 2. Állítsuk be a lekérdezés típusát Törlőre, majd adjuk hozzá a Program táblát. 3. A QBE rácson az első Mezőbe írjuk be, hogy Program.*, és a Törlés sorban állítsuk be a From záradékot. Ezzel azt adtuk meg, hogy a Program táblából akarunk törölni. 4. Most fogalmazzuk meg azt a feltételt, ami alapján törölni szeretnék a rekordokat a Program táblából! A Kezdés mező tartalmazza az egyes versenyszámok kezdési idejét; adjuk hozzá a lekérdezéshez, és a Törlés sorában állítsuk be a Where záradékot. 5. Mivel 2006.02.18-án több versenyszám is lehet, ezért a Feltétel sorba azt írjuk be, hogy Like „2006.02.18.*”. Így minden azon a napon kezdődő rekord törlődni fog a Program táblából, miután lefuttattuk a lekérdezést. Most ezt kell látnunk a Tervező nézetben:
– 85 –
Lekérdezések
54. ábra: A Torino 2006 című feladat F részfeladatának megoldása tervező nézetben
6. Futtassuk a lekérdezést, majd zárjuk be, és mentsük F néven! További, hasonló módon megoldható feladatok: •
OKTV 2006/2007 3. forduló 5. feladat (Napelem) E részfeladat
•
OKTV 2007/2008. 3. forduló 7. feladat (Cégek) E részfeladat
12. Kereszttáblás lekérdezések Kereszttáblás lekérdezéseket olyan esetekben szoktunk készíteni, amikor az adatokat két szempont szerint szeretnénk csoportosítani. A végeredmény egy a táblázatkezelőkhöz hasonló táblázat. Lényegében megfeleltethető az Excel Kimutatás eszközének. Abban az esetben, ha a kereszttáblás lekérdezésben több táblából származó adatokat szeretnénk megjeleníteni, akkor először készítenünk kell egy olyan lekérdezést, ami tartalmazza az összes, felhasználni kívánt mezőt minden szükséges táblából. Ellentétben a korábban tárgyalt lekérdezésekkel, a kereszttáblás lekérdezések elkészítéséhez célszerűbb a Lekérdezés varázslót használni, ugyanis nagyban megkönnyíti a dolgunkat.
12.1. OKTV 2009/2010. 3. forduló 6. feladat (Új bolygók) E részfeladat Ebben a részfeladatban megfelelő diagram segítségével ábrázolni kell azt, hogy a megadott években az egyes bolygófajtákból hányat fedeztek fel. – 86 –
Lekérdezések Bár nem diagram, de ez a feladat nagyon jó példa kereszttáblás lekérdezés készítésére, ezért így fogjuk megoldani. Javasolt megoldás lépései: 1. Mivel a lekérdezéshez szükséges adatok mindegyikét a Bolygok tábla tartalmazza, ezért azt adjuk hozzá a lekérdezéshez. 2. Adjuk hozzá a lekérdezéshez a Felfedezés éve, a Bolygó típusa és a Bolygó neve mezőket! Ha megvan, mentsük a lekérdezést E_segéd néven, és zárjuk is be! 3. Kattintsunk a Létrehozás fülön a Lekérdezés varázslóra, majd válasszuk ki a Kereszttáblás lekérdezés varázslót, majd kattintsunk az Ok gombra!
55. ábra: Kereszttáblás lekérdezés kiválasztása
4. A következő lépésben válasszuk ki az E_segéd nevű lekérdezést, majd menjünk tovább.
– 87 –
Lekérdezések
56. ábra: A felhasználandó objektum (tábla vagy lekérdezés) kiválasztása
5. A következő két lépésben azt kell meghatároznunk, hogy mely mezők legyenek a sor- és oszlopfejlécek. Az biztos, hogy a Felfedezés éve és a Bolygók típusa mezők lesznek ezek, de hogy melyik adat kerüljön a sorfejlécbe, és melyik az oszlopfejlécbe, az teljesen mindegy. Legyen a Bolygók típusa a sorfejléc és a Felfedezés éve az oszlopfejléc. 6. Az utolsó előtti lépésben azt kell eldöntenünk, hogy mit is szeretnénk kiszámítani az egyes cellákban. Mivel azt kell kiderítenünk, hogy az egyes években bolygótípusonként hány új bolygót fedeztek fel, ezért nyilvánvaló, hogy a Count öszszesítő függvényt kell használnunk. Válasszuk ki a függvények közül, és menjünk tovább.
57. ábra: Összesítő függvény kiválasztása
– 88 –
Lekérdezések 7. Az utolsó lépésben csak el kell neveznünk a lekérdezést. Adjuk neki az E_kereszttábla nevet, és kattintsunk a Befejezés gombra. Ha mindent jól csináltunk, akkor ezt kellett kapnunk:
58. ábra: Az Új bolygók című feladat E részfeladatának megoldása adatlap nézetben
13. Táblakészítő lekérdezések A táblakészítő lekérdezések alapja ugyanaz, mint a választó lekérdezéseké. Annyiban nyújt többet, hogy míg a választó lekérdezések végeredményét nem tároljuk külön táblában, addig a táblakészítő lekérdezés a választó lekérdezés végeredményét egy táblában elmenti. Ilyen módon megoldható feladat: •
OKTV 2009/2010. 2. forduló. 4. feladat (Teleki Sámuel Travel) E részfeladat
14. SQL-specifikus lekérdezések Az SQL-specifikus lekérdezések azért különlegesek az Access-ben, mert ezeket nem lehet a QBE rácson „összedobni”. Elkészítésükhöz mindenképpen szükségünk van az SQL nyelv ismeretére.25 Ebből következően csak az SQL nézetben lehet elkészíteni őket. Az SQL-specifikus lekérdezésekbe az egyesítő, átadó és adatdefiniáló lekérdezések tartoznak. A következőkben ezeket tekintjük át röviden. Egyesítő lekérdezés Ennek a lekérdezésnek a segítségével több tábla tartalmát vagy lekérdezés eredményét „fésülhetjük” össze. Mint azt már korábban tárgyaltuk, a táblák és lekérdezések eredménye lényegében egy halmaz. Az egyesítő lekérdezés pedig nem más, mint az unió halmazművelet megvalósítása két vagy több lekérdezés vagy tábla között. Felépítése: 25
Illetve némiképp meg lehet kerülni az SQL-t, de még ez esetben is szükség van a nyelv bizonyos szintű ismeretére.
– 89 –
Lekérdezések UNION Az SQL kód használatát ennél a lekérdezés típusnál úgy lehet megkerülni, hogy elkészítjük Tervező nézetben azokat a lekérdezéseket, amelyeknek az eredményét szeretnénk egyesíteni. Ha ez megvan, akkor egy új lekérdezésbe bemásoljuk a már elkészített lekérdezések SQL kódját, és az kódok közé beírjuk a UNION kulcsszót. Arra azonban ügyeljünk, hogy az egyes lekérdezések SQL kódjának a végére az Access automatikusan beteszi az utasítást lezáró pontosvesszőt. Ezeket az egyes részlekérdezések végéről ki kell törölni, és a teljes lekérdezés végére lehet beírni.26 Átadó lekérdezés Ez a lekérdezés típus olyan esetekben használható, amikor egy távoli – akár más SQL-t, például Oracle, vagy MySQL-t használó, röviden ODBC27 – adatforráshoz kapcsolódva szeretnénk kihasználni az Access nyújtotta szolgáltatásokat, például jelentés- vagy űrlapkészítés céljából. Nagy előny, hogy ilyenkor a távoli adatbázis tábláival közvetlenül dolgozhatunk; nincs szükség a táblák csatolására vagy importálására. Adatdefiniáló lekérdezés Ezzel a típussal új táblákat hozhatunk létre. Ezt Access-ben nagyon egyszerűen ki tudjuk kerülni: a Táblatervező eszköz használatával. Ami miatt esetleg csábító lehetne a használata, az talán az, hogy – más adatbáziskezelő rendszerből kiindulva – azt gondoljuk, egymás után, pontosvesszőkkel elválasztva több ilyen utasítást futtathatunk egyszerre, ezáltal gyorsabban létrehozva a táblákat. Azonban erre sajnos nincs lehetőség. Egyszerre csak egy SQL utasítást lehet futtatni SQL nézetben. Az adatdefiniáló lekérdezés SQL szintaxisára egy példa: CREATE TABLE tSzállás ([Szállás] integer, [Épület] text,
26
Ha nem írjuk be, az sem gond, az Access automatikusan megteszi helyettünk, ezzel lezárva az SQL utasítást. 27 Az ODBC adatforrásokról részletesen lásd: http://office.microsoft.com/hu-hu/access-help/odbcadatforrasok-HP005241666.aspx [Utolsó megtekintés: 2012.11.07.]
– 90 –
Lekérdezések [Szobaszám] text, CONSTRAINT [pk_Szállás] PRIMARY KEY ([Szállás])); Megjegyzések: •
ha a tábla nevében szóköz szerepel, akkor a tábla nevét szögletes zárójelek közé kell írni
•
a CREATE TABLE utasításon belül szögletes zárójelek közé kell írni a mezők neveit
•
A CONSTRAINT kulcsszó után jönnek a mezőkre vonatkozó megszorítások (elsődleges kulcs, idegen kulcs, egyediség)
14.1. OKTV 2010/2011. 3. forduló 5. feladat (Állatvédelem) D részfeladat (emlősök) Ebben a részfeladatban meg kell határoznunk, hogy az egyes emlősfajokból hány egyedet észleltek összesen úgy 2010-ben, hogy azokhoz az emlősfajokhoz, amelyekből nem észleltek egyedet, azokhoz 0-t írjon a lekérdezés. Javasolt megoldás: Megoldási terv: A feladat szövegéből látszik, hogy itt valójában két lekérdezés eredményét kell egyesítenünk. Első lépésként bontsuk szét a feladatot két részfeladatra: •
készítsünk lekérdezést, ami megadja, hogy 2010-ben az egyes emlősfajokból hány egyedet észleltek összesen, majd
•
készítsünk egy másik lekérdezést, amelyben azokat az emlősfajokat gyűjtjük össze, melyekből nem észleltek egy példányt sem 2010-ben. Ne feledkezzünk meg arról, hogy itt a darabszámhoz 0-t kell írnunk!
Miután elkészült a két segédlekérdezésünk, nincs más dolgunk, mint hogy uniózzuk az eredményüket! Javasolt megoldás lépései: 1. Készítsünk egy lekérdezést Tervező nézetben.
– 91 –
Lekérdezések 2. Először készítsük el azt a lekérdezést, melyben a 2010-es észlelések számát adjuk meg. Adjuk hozzá a lekérdezéshez az Állatok és az Észlelések táblákat. 3. Jelenítsük meg az Állatok táblából a Faj neve, az Észlelések táblából pedig az Észlelt egyed száma és a Dátum mezőket. Egyúttal a Dátum mezőre fogalmazzuk meg a feltételünket: Between #2010.01.01.# And #2010.12.31.#. 4. Mivel faj szerint kellene megszámolni az egyedszámot, ezért kapcsoljuk be a QBE rács Összesítés sorát (Tervezés fül, Összesítés gomb). 5. A Faj neve mező Összesítés sorába állítsuk be a Group by záradékot, az Észlelt egyed száma mezőnél pedig a Sum összesítő függvényt. Mivel a Dátum mező szerint nem csoportosítunk, és nem is tartalmaz összesítő függvényt, ezért az Összesítés sorába állítsuk be a Where záradékot. Ha mindent jól csináltunk, akkor ezt kell látnunk:
59. ábra: A 2010-ben észlelt állatok száma
6. Mentsük a lekérdezést D_emlősök_észleltek néven.28 7. Most készítsük el a nem észlelt emlősöket tartalmazó lekérdezést! Hozzunk létre ismét egy új lekérdezést Tervező nézetben, és adjuk hozzá az Állatok táblát. 8. Most azokat az emlős fajokat kell kilistáznunk, amelyeket nem észleltek 2010ben. Adjuk hozzá a lekérdezéshez a Faj neve és az Osztály mezőket. Az Osztály
28
Nem lenne kötelező elmenteni, elég lenne csak addig megnyitva tartani, míg az SQL kódjára szükség van.
– 92 –
Lekérdezések mező feltétel sorába írjuk be, hogy emlősök, ezzel a lekérdezés egyik feltételét már el is készítettük. 9. Mivel a nem észlelt fajok darabszáma 0, ezért hozzunk létre egy új mezőt Észlelt egyedek száma névvel, és adjunk neki konstans 0 értéket. 10. Most már csak azt a feltételt kell elkészítenünk, amivel megkapjuk, hogy melyek azok a fajok, amelyeket nem észleltek. Ezt legegyszerűbben egy SQL allekérdezéssel tudjuk elkészíteni. Az allekérdezésben kigyűjtük az Észlelések táblából azokat a fajokat, melyeket 2010.01.01. és 2010.12.31. között észleltek. Végül pedig megnézzük, hogy melyek azok a fajok, amelyek nem szerepelnek ennek az allekérdezésnek a végeredményében. 11. Tehát a fentieket figyelembe véve a Faj neve mező Feltétel sorába a következő kód kerül: Not In (SELECT észlelések.faj FROM észlelések WHERE (észlelések.Dátum)>=#1/1/2010# And (észlelések.Dátum)<=#12/31/2010#) 12. Fontosnak tartom megjegyezni, hogy ezt a kódrészletet beírhatjuk a QBE rács Feltétel sorában, vagy a Kifejezésszerkesztőbe is. Viszont mivel itt már SQL kódot írunk, sajnos a dátumnál nem használhatjuk a magyar formátumot (év.hónap.nap), hanem csak és kizárólag a hónap/nap/év formátummal dolgozhatunk, különben szintaktikai hibaüzenetet ad a rendszer. 13. Mentsük el a lekérdezést D_emlősök_nem_észleltek néven.29 14. Most már nincs más dolgunk, mint hogy vegyük ennek a két lekérdezésnek az unióját! Hozzunk létre egy lekérdezést Tervező nézetben. Ne adjunk hozzá semmilyen táblát, de a szalagon a Tervezés fülön állítsuk be a lekérdezés típusát Egyesítőre.
29
Lásd az előző lábjegyzetet.
– 93 –
Lekérdezések
60. ábra: Egyesítő lekérdezés beállítása
15. Ha bezártuk volna, akkor nyissuk meg az előbb létrehozott két lekérdezést, és váltsunk át SQL nézetre. 16. Jelöljük ki az első lekérdezés SQL kódját, majd másoljuk át az egyesítő lekérdezésbe. Ne felejtsük el kitörölni az utasítás végéről a pontosvesszőt! 17. Hogy olvashatóbb legyen a kód, nyomjunk egy entert, majd az új sorba írjuk be a UNION utasítást, és nyomjunk ismét entert. 18. Ezután másoljuk ide a másik lekérdezés SQL kódját is. Ha mindent jól csináltunk, akkor a következő SQL kódot kell látnunk:
61. ábra: Az Állatvédelem című feladat D részfeladatának megoldása SQL nézetben
19. Mentsük a lekérdezést D_emlősök néven, és zárjuk be. További, hasonló módon megoldható feladatok: •
OKTV 2009/2010. 3. forduló. 6. feladat (Új bolygók) C részfeladat
– 94 –
Jelentések
VI. Jelentések A jelentések – a lekérdezésekhez hasonlóan – azt a célt szolgálják, hogy az adatbázisban szereplő adatokat valamilyen szempont szerint megjelenítsük a felhasználók számára. A jelentés annyiban tér el a lekérdezésektől, hogy – a lekérdezések végeredményével és a táblák tartalmával ellentétben – ezt kifejezetten a papír alapú megjelenítésre találták ki. Jelentések készítésekor célszerű a következőket végiggondolni: •
az adatbázis adatai közül miket szeretnénk megjeleníteni,
•
milyen csoportosítási elv szerint,
•
milyen sorrendben,
•
van-e szükség ilyen-olyan összesítésekre, stb.
A fenti szempontok figyelembe vételével kézenfekvő, hogy a jelentéseket egy jól összerakott lekérdezéssel könnyen el lehet készíteni. Innentől kezdve pedig csak a formai követelményeket kell beállítanunk. A jelentések elkészítéséhez első körben célszerű a Jelentésvarázslót használni, majd ha még extra dolgokkal szeretnénk a jelentést bővíteni, vagy módosítani rajta, akkor használjuk a Jelentéstervező nézetet.
62. ábra: A Jelentés varázsló és a Jelentéstervező
15. OKTV 2004/2005. 3. forduló 5. feladat (Tagnyilvántartás) J részfeladat Ebben a részfeladatban egy olyan jelentést kell készítenünk, amelyben városonként felsoroljuk az ott lakó tagokat, a feladatlapon megadott mintának megfelelően. A településeket név szerint kell rendezni, és a városban lakó tagokat szintén ábécé sorrendben kell megjeleníteni.
– 95 –
Jelentések Javasolt megoldás lépései: 1. Készítsük el a jelentés alapját képező lekérdezést! Hozzunk létre egy lekérdezést Tervező nézetben, majd adjuk hozzá a lekérdezéshez a szükséges táblákat (Varos, Irszam_varos, Tag)! 2. Adjuk hozzá a lekérdezéshez a megjelenítendő adatokat (a mintáról leolvasható): a varosnev, nev, tipus, ujsag, irsz, pcim mezőket. Ezzel a lekérdezés el is készült, mentsük el J_TAGOK_segéd néven! 3. Készítsük el a jelentést a Jelentés varázsló segítségével! Első lépésként válaszszuk ki, hogy mik szerepeljenek a jelentésben. Adjuk hozzá a J_TAGOK_segéd jelentés minden mezőjét, majd menjünk tovább. 4. A következő lépésben ki kell választanunk, hogy a három tábla között fennálló kapcsolattól függően milyen csoportosításban jelenjenek meg az adatok. A TAG táblát kiválasztva nem lesz semmilyen csoportosítás, de ez nem is baj. A csoportosításról a következő lépésekben fogunk gondoskodni. 5. Miután kiválasztottuk a TAG táblát, és továbbmentünk, meg kell határoznunk, hogy mely mezők szerint történjen az adatok csoportosítása. A feladat azt kéri, hogy városonként jelenítsük meg a tagok adatait, így csoportszintnek adjuk hozzá a varosnev mezőt, majd menjünk tovább. 6. Ezután meg kell adnunk, hogy mely rekordok szerint és hogyan szeretnénk rendezni. A feladat leírása szerint a tagok neve szerint kell rendezni városonként az adatokat, úgyhogy adjuk hozzá a rendezéshez a nev mezőt, és menjünk tovább. 7. A következő lépésekben a jelentés megjelenítését befolyásoló beállításokat kell elvégeznünk, majd az utolsó mentésben nevezzük el a jelentést J_TAGOK-nak, és zárjuk be a varázslót. 8. Megtekintve a legenerált jelentést, láthatunk néhány eltérést a mintához képest. Ezeket az eltéréseket a jelentés Tervező nézetében tudjuk átállítani. 9. Az első és legfontosabb eltérés, hogy az Oldalfejben szerepel minden mezőnév, viszont a mintán láthatjuk, hogy semmi ilyesmi nincs megjelenítve. Ezért az Oldalfejből ki kell törölnünk ezeket a címkéket.
– 96 –
Jelentések 10. A következő eltérés, amit láthatunk, hogy a városnevek alatt egy zöld vonal látható a mintán. Ezt nekünk a varosnev fejlécbe kell beillesztenünk. A szalagon a Vezérlők panelen találjuk a Vonal vezérlőt.
63. ábra: Vonal vezérlő beszúrása
11. Ezután nem maradt más dolgunk, mint hogy a Törzsben a mintának megfelelően helyezzük el a már oda generált beviteli mezőket, valamint még két új címkét nekünk is rögzítenünk kell: az egyiket Típus, a másikat Újság felirattal. További, hasonló módon megoldható feladatok: •
OKTV 2005/2006. 3. forduló 10. feladat (Járatok) G részfeladat (Megjegyzés: sajnálatosan a feladathoz nincs minta mellékelve a honlapon)
•
OKTV 2006/2007. 3. forduló 5. feladat (Napelem) G részfeladat
•
OKTV 2007/2008. 3. forduló 7. feladat (Cégek) K részfeladat
•
OKTV 2010/2011. 3. forduló 5. feladat (Állatvédelem) H részfeladat (fajok)
– 97 –
Űrlapok
VII. Űrlapok Az űrlapok egyik – a jelentésekhez hasonló – célja, hogy az adatokat valamilyen meghatározott formában tárja a felhasználók felé. Azonban a jelentésekkel ellentétben nem a papír alapú megjelenítésre, hanem a képernyőn valóra van kihegyezve. Űrlapokat viszont más céllal is készíthetünk. Segítségükkel olyan felhasználói felületet készíthetünk az adatbázishoz, amelynek segítségével egy, az adatbázis-kezeléshez nem értő felhasználó is könnyen rögzíthet, módosíthat, törölhet vagy kérdezhet le adatok az adatbázisból úgy, hogy nem tudja, mik azok a táblák, lekérdezések.30 Az űrlapok készítéséhez sokszor célszerű használnunk az Űrlap varázslót, de csak azért, mert így sok „rutin munkát” (például címkék, mezők elhelyezése) automatizálhatunk, és aztán a végeredményt kedvünkre testre szabhatjuk a Tervező nézetben.
16. OKTV 2003/2004. 3. forduló 13. feladat (Jelentkezési adatbázis) I részfeladat (paraméteres) E részfeladatban egy olyan űrlapot kell készítenünk, amelynek segítségével meg tudjuk keresni, hogy melyik résztvevőt melyik szobában szállásolták el. Az űrlap két beviteli mezőből áll: az egyikbe az épület megnevezése kerül (A vagy B), a másikba pedig a szoba száma. Emellett szükség van még egy nyomógombra is, melynek lenyomásakor lefut az a lekérdezés, ami az eredménnyel vissza fog térni. A részfeladat elkészítése során párhuzamosan készítjük a lekérdezést és az űrlapot. Jelen esetben viszont célravezetőbb az Űrlaptervezés nevű eszközt használnunk az Űrlapvarázsló helyett. Javasolt megoldás lépései: 1. Készítsünk egy lekérdezést Tervező nézetben! 2. A szükséges táblák: tRésztvevő és tSzállás. Adjuk ezeket hozzá a lekérdezéshez! 3. Mivel épületre és szobaszámra szeretnénk rákeresni, és kilistázni a keresett szobában lakókat, ezért adjuk hozzá a lekérdezéshez a tSzállás tábla Épület és Szobaszám mezőit, valamint a tRésztvevő tábla Név mezőjét is! 30
Megjegyzés: a táblák és lekérdezések Adatlap nézete valójában egy táblázat elrendezésű űrlap.
– 98 –
Űrlapok 4. Ha ezzel megvagyunk, mentsük a lekérdezést I_segéd néven, de ne zárjuk be! 5. Hozzunk létre egy űrlapot Tervező nézetben! Ehhez a Létrehozás fülön az gombra kell kattintanunk. 6. A Tervezés fülön nyissuk le a Vezérlők panelt! Legyen kipipálva a Vezérlőelem varázslók használata menüpont. Ennek köszönhetően, ha létrehozunk az űrlapon egy vezérlőelemet, akkor – ha tartozik hozzá – automatikusan elindul egy varázsló, ami nagyban megkönnyíti a munkánkat.
7. Hozzunk létre két Beviteli mezőt! A Vezérlőelem varázsló segítségének köszönhetően a beviteli mezőkhöz automatikusan létrehozott a rendszer két címkét is. Az első beviteli mező címkéjébe írjuk be azt, hogy Épület, a másodikéba pedig azt, hogy Szoba. 8. Kapcsoljuk be a Tulajdonságlapot, majd kattintsunk rá az első beviteli mezőre! A Tulajdonságlapon az Egyéb fülön a Névhez írjuk be azt, hogy Epulet. Tegyük meg ugyanezt a második beviteli mezővel, csak itt a Név tulajdonsághoz azt írjuk be, hogy Szoba. Ha ezzel megvagyunk, akkor mentsük az űrlapot I néven, de – csakúgy mint az előbb létrehozott lekérdezést – ne zárjuk be! 9. Menjünk át az I_segéd lekérdezést Tervező nézetébe! Most össze fogjuk kötni a lekérdezést az űrlappal. Kattintsunk jobb egérgombbal az Épület mező Feltétel sorában, és lépjünk be a Kifejezésszerkesztőbe. Itt az űrlap objektumok között tallózzunk rá az Epulet beviteli mezőre, majd kattintsunk rá kétszer. Ezzel azt értük el, hogy a lekérdezés paraméterként megkapja az Epulet beviteli mezőbe írt karaktersort. Mivel a feladat kéri, hogy legyen úgy lekezelve az az eset, amikor valamelyik beviteli mező üresen marad, hogy akkor a lekérdezés jelenítsen meg mindent, ezért az [Űrlapok]![I]![Epulet] hivatkozás elé gépeljük be a Like
– 99 –
Űrlapok kulcsszót, majd a hivatkozás után gépeljük be azt, hogy &”*”. Így tehát ezt látjuk most a Kifejezésszerkesztőben: Like [Űrlapok]![I]![Epulet] & "*". Ennek köszönhetően ha nincs kitöltve a mező, akkor a lekérdezés eredményében megjelenik az összes olyan rekord, melyben a szobaszámnál az szerepel, amit megadtunk. 10. Tegyük meg ugyanezt a Szobaszám mező Feltétel sorában is. Ha mindent jól csináltunk, akkor ezt kell látnunk a lekérdezés Tervező nézetében:
65. ábra: A Jelentkezési adatbázis című feladat I részfeladatához készült segédlekérdezés tervező nézetben
11. Mentsük a lekérdezést, és zárjuk is be! 12. Már csak a nyomógombot kell elkészítenünk az űrlapon. Menjünk vissza az I űrlap tervező nézetébe, és helyezzünk el egy nyomógombot az űrlapon. Ezután elindul a Parancsgomb varázsló. 13. Az első lépésben ki kell választanunk, hogy mi történjen a gomb lenyomásakor. Az Egyéb kategórián belül válasszuk ki a Lekérdezés futtatását, majd menjünk tovább.
– 100 –
Űrlapok
66. ábra: Esemény hozzárendelése a nyomógombhoz
14. A következő lépésben ki kell választanunk a rendelkezésünkre álló lekérdezések közül, hogy melyik fusson le a gomb megnyomásakor. Válasszuk ki az I_segéd nevű lekérdezést.
67. ábra: A futtatandó lekérdezés kiválasztása
15. Az ezt követő lépésben meghatározhatjuk, hogy mi legyen a gombon (szöveg vagy kép). Írjuk be a Szöveghez azt, hogy Keresés, majd menjünk tovább.
– 101 –
Űrlapok
68. ábra: Gombfelirat készítése
16. Az utolsó lépésben magának a gombnak, mint vezérlőnek adhatunk nevet. Ezt általában célszerű megtenni, mert így a későbbiekben könnyebben azonosítható lesz a vezérlő. Most hagyhatjuk az alapértelmezett néven. Végezetül kattintsunk a Befejezés gombra! 17. Ha mindent jól csináltunk, akkor valami hasonlót kell látnunk az űrlap tervező nézetében:
69. ábra: A Jelentkezési adatbázis című feladat I részfeladatának megoldása tervező nézetben
18. Végezetül váltsunk át Űrlap nézetbe, és próbáljuk ki munkánk gyümölcsét! Ezután mentsük el az űrlapot, és zárjuk be! További, hasonló módon megoldható feladatok: •
OKTV 2003/2004. 2. forduló (Síkölcsönző) I részfeladat
•
OKTV 2006/2007. 2. forduló 7. feladat (Nemzeti park) F részfeladat
•
OKTV 2006/2007. 3. forduló 5. feladat (Napelem) F részfeladat
– 102 –
Űrlapok •
OKTV 2007/2008. 2. forduló 8. feladat (Kávé) F részfeladat
•
OKTV 2009/2010. 2. forduló 4. feladat (Teleki Sámuel Travel) H részfeladat
•
OKTV 2010/2011. 2. forduló 4. feladat (Űrhajók) U részfeladat31
31
A lemezmellékleten található megoldásban – az Access 2010 valamilyen számomra ismeretlen belső működési metódusa miatt – bármilyen országot választunk ki a kombinált listából, az űrlapot kiszolgáló paraméteres lekérdezés eredmény nélkül tér vissza. Viszont ha csak a paraméteres lekérdezést futtatjuk, akkor az jó eredménnyel tér vissza.
– 103 –
Új feladattípusok
VIII. Új feladattípusok Ebben a fejezetben olyan feladattípusokról fogok röviden írni, amelyek a korábbi versenyeken nem fordultak elő, viszont úgy gondolom, hogy a későbbiekben megjelenhetnek.
17. Felhasználói felület készítése A korábbi versenyeken is volt már olyan feladat, hogy készíteni kellett egy kezdőűrlapot, melyről a létrehozott lekérdezések, jelentések elindíthatóak voltak. Ezt az alapgondolatot továbbgondolva olyan feladatokat tartok elképzelhetőnek, hogy a versenyzők megkapnak egy kész adatbázist lekérdezésekkel, jelentésekkel együtt. A feladatuk egy olyan felhasználói felület tervezése lenne, aminek segítségével egy olyan felhasználó is könnyedén tudja manipulálni az adatbázist – megadott keretek között –, aki nincs tisztában azzal, hogy a felhasználói felület mögött pontosan mi is zajlik. Az ilyen feladattípusok megoldásához mindenképpen mélyíteni kell az űrlapokkal és a makrókkal kapcsolatos ismereteket, valamint ki kell térni az Access beállítási lehetőségeire is.
18. Kapcsolódás külső adatbázisokhoz Az Access egyik ritkán használt funkciója, hogy hozzá tud kapcsolódni más adatbázisokhoz. Ehhez szükség van egy, az ODBC32 protokollt támogató adatbázisra is. Ebben az esetben a távoli adatbázis tábláit nem kell importálni, csupán csatolni azt. Ez esetben a táblák továbbra is eredeti helyükön tárolódnak, sémájukat nem módosíthatjuk.33 Az ilyen típusú feladatok azért lennének hasznosak, mert így a versenyzők, diákok megtapasztalnák, hogy milyen egy éles rendszeren dolgozni, hogy egyszerre többen is dolgozhatnak ugyanazokkal az adatokkal, stb. A jelenlegi körülményeket figyelembe véve OKTV-n egyelőre valószínűtlennek tartom az ilyen jellegű feladatokat. Sok iskolában még manapság is probléma, hogy egy 32 33
Open DataBase Connectivity – Nyílt adatbázis-összekapcsolás Éppen ezért figyelembe kell venni, hogy a távoli adatbázisban hogyan vannak tárolva az adatok.
– 104 –
Új feladattípusok számítógépnél egy diák dolgozzon. Továbbá sok helyen a gépek állapota sem a legjobb. Emellett nem hiszem, hogy lenne kapacitás még egy adatbázis-szerver üzemeltetésére is (noha vannak ingyenes megoldások). Ezektől eltekintve úgy gondolom, hogy ahol van rá lehetőség, érdemes lenne ilyen típusú feladatokkal is foglalkozni, mert tágítja a diákok adatbázis-kezeléssel kapcsolatos látókörét.
19. Hibakeresés és –javítás Ezen feladattípusokban adva vannak már előre elkészített lekérdezések, jelentések illetve űrlapok, amik valamilyen szempontból hibákat tartalmaznak. A feladat pedig ezen hibák kijavítása lenne. Például a lekérdezésekben rossz feltétel van megadva, rossz mezőkkel dolgozik, a beépített függvények paraméterezése rossz, vagy rossz beépített függvénnyel van elkészítve, stb.
Az előbb tárgyalt feladattípusok közül a felhasználói felület tervezése és a hibakeresés, hibajavítás az, ami minden probléma nélkül megjelenhetne, mert ezekhez nem szükséges plusz eszköz. Viszont figyelembe véve az új kerettantervi ajánlásokat34 azt láthatjuk, hogy az informatika óraszámokat drasztikusan lecsökkentették. Ha eddig nem volt szó informatika órán, fakultáción vagy versenyfelkészítésen szó ezekről a feladattípusokról, akkor ilyen alacsony óraszámok mellett kevés esélyt látok arra, hogy az érettségi tananyagra is elég idő jusson, nem beszélve a fent röviden ismertetett új feladattípusokról.
34
Az új kerettantervi ajánlások elérhetőek itt: http://kerettanterv.ofi.hu/ [Utolsó megtekintés: 2012.11.11.]
– 105 –
Függelék
IX. Függelék 20. SQL az Access-ben Alapvetően az Access QBE rács felületét használom az egyes feladatok megoldásához, ennek ellenére olykor szükséges – vagy a feladat megoldása szempontjából ésszerűbb – az SQL nézetet használni. Az alábbiakban röviden áttekintem az Access által használt szintaxis eltéréseit az SQL szabványtól: •
A LIKE operátornál az Access a „*” és „?” helyettesítő karaktereket használja a szabványos „_” és „%” helyettesítők helyett.
•
A dátumokat „#”-ek közé kell tenni.
•
A szöveges konstanst elhelyezhetjük ' jelek vagy " jelek közé.
•
A karaktersorozatok konkatenálásánál (összeépítésénél) használható az & jel és a + jel is.
•
Magyar nyelvű Windows operációs rendszeren megengedett az ékezetes karakterek használata.
További, nem szintaktikus eltérések az Access-ben: •
Más adatbázis-kezelő rendszerekkel ellentétben az Access SQL nézetében egyszerre csak egy utasítást adhatunk. Vagyis ha például szeretnénk feltölteni egy táblát az INSERT INTO utasítással, akkor annyi „lekérdezést” kell készítenünk, ahány sort be akarunk szúrni.
•
Az SQL kódban nem lehet megjegyzést elhelyezni.
– 106 –
Irodalomjegyzék
X. Irodalomjegyzék [1] Bódy Bence: Az SQL példákon keresztül. Jedlik Oktatási Stúdió. Budapest, 2004. [192], ISBN 963-210-860-4. [2] Jeffrey D. Ullman – Jennifer Widom: Adatbázis-rendszerek. Alapvetés. Második, átdolgozott kiadás. Panem. Budapest, 2009. [600], ISBN 978-9635454-81-5. [3] Bodnár István – Magyary Gyula: Adatbázis-kezelés. Második, javított és bővített kiadás. Kiskapu Kft. Budapest, 2007. [136], ISBN 978-963-9637-26-9. [4] Beviteli
maszkok:
http://office.microsoft.com/hu-hu/access-help/az-
adatbeviteli-formatumok-szabalyozasa-beviteli-maszkokkalHA010096452.aspx [Utolsó megtekintés: 2012.10.23.] [5] A beviteli maszk szintaxisa: http://office.microsoft.com/hu-hu/access-help/abeviteli-maszk-szintaxisa-peldak-HP005187550.aspx [Utolsó megtekintés: 2012.10.11.] [6] A korábbi alkalmazói OKTV feladatai, nyersanyagai: http://tehetseg.inf.elte.hu/nemesa/nemesa_archivum.html [Utolsó megtekintés: 2012.11.01.] [7] Az Access beépített függvényei: http://office.microsoft.com/hu-hu/accesshelp/fuggvenyek-kategoria-szerint-rendezve-HA010131676.aspx [Utolsó megtekintés: 2012.11.03.]
– 107 –
