4. Adatbáziskezelés (MS Access)

: www.pszfsalgo.hu, : [email protected], : 30/644-5111

Adatbáziskezelés

4. Adatbáziskezelés (MS Access) 4.1. Az autó adatbázis (egytáblás példa) 1. Egy tábla létrehozásának lépései:

Válasszuk az ADATBÁZISABLAK/ÚJ/TERVEZİ nézetet. Írjuk be a mezınevet. Válasszuk ki a megfelelı adattípust a legördülı listából. Minden mezınek, ahol szükséges adjuk meg a mezıtulajdonságokat. Ha szükségesnek tartjuk, adjunk meg a mezı rövid magyarázatát a Leírás oszlopban. pl.: Ara: az autó beszerzési bruttó ára. Adjuk meg a táblában az elsıdleges kulcs mezıt (vagy mezıket). Mentsük el a táblát.

(A tábla neve max. 30 karakter hosszú lehet, nem tartalmazhat pontot, felkiáltó jelet, ahogyan a mezınevekben sem szerepelhetnek ezek a karakterek.) Az Autó tábla tervezı nézete és mezınevei, továbbá néhány mezıtulajdonsággal: Mezınév Rendszám

Adattípus Szöveg 7

Érvényességi szabály

Egyéb mezıtulajdonság Beviteli maszk: >LLL-000;0;

Típus Szín Ára

Keresésvarázsló Szöveg 20 Pénznem Between 100000 Tizedesek száand 15000000 ma:0 Személyek Szám/Bájt >1 and <15 száma Évjárat Szám/Egész >=1930 and <=2001 Hengerőr- Szám/Hosszú Between 500 and tartalom egész 6000 Fogyasztás Szám/Egysze>2 and <140 Tizedesek száres ma:1 BenIgen/Nem Cím: Benzin zin/Disel Kép OLE objektum A beviteli maszk: >LLL-000; Jelentése: a rendszám elsı 3 karaktere csak bető lehet (L), az utolsó 3 pedig csak szám (0). Ha kisbetőt írunk, akkor az Access átalakítja nagybetővé (>). A kötıjelet a gép nem tárolja, mivel a maszk második része nem 0, hanem üres. 2. Keresésvarázsló elkészítése Válasszuk ki az adattípusok közül a KERESÉSVARÁZSLÓT, majd az elsı párbeszédablakban a „Begépelem a szükséges értéket” váltógombra kattintunk, mivel nincs táblánk, ami az autótípusokat tartalmazná.

Fejezet: Bozó Mária f. adjunktus

121

Adatbáziskezelés


A következı párbeszédablakban megadjuk az oszlopok számát és beírjuk a leggyakrabban elıforduló autó típusokat. Fogadjuk el a gép által felkínált nevet és a „Befejezés” után elkészül a keresésvarázsló. Vizsgáljuk meg, vajon rögzíthetünk-e olyan adatot, amelyet nem tartalmaz a KERESÉSVARÁZSLÓ?

A keresésvarázsló további elınye, hogy megkönnyíti az adatfelvitelt. (Lásd baloldali ábra.) A varázslóban rögzített keresésvarázsló adatait a MEZİTULAJDONSÁGOK/MEGJELENÍTÉS/SORFORRÁS listában módosíthatjuk.

3. Őrlapkészítés Készítsünk egyoszlopos őrlapot az autók adatainak felviteléhez! Szúrjunk be képet az egyes autókhoz. A képbeszúráshoz, őrlapnézetben kattintsunk a kép helyére a jobb egér gombbal. Az OBJEKTUM beszúrása menübıl válasszuk pl. a Microsoft Clipart Galery-t. Illesszük be a kiválasztott képet.

A kép méretezése elvégezhetı az alábbi módon. Nyissuk meg az őrlapot tervezı nézetben. A MÉRETEZÉSI MÓD tulajdonságnál adjuk meg vagy a MÉRETEZÉS vagy a KITÖLTÉS tulajdonságot. A KITÖLTÉS tulajdonság torzíthatja a képet, mivel a kép a rendelkezésre álló teljes téglalap területben helyezkedik majd el.

122



Adatbáziskezelés

Készítsünk parancsgombokat, melyek őrlapbezárásra, rekordléptetésre és rekord felvitelére alkalmasak. Válasszuk ki a parancsgombot az eszközkészlet ikonok közül. Kattintsunk az őrlapon arra a részre, ahová a parancsgombot szeretnénk elhelyezni. (Egyoszlopos őrlapnál az őrlap törzs szakaszában, táblázatos őrlap esetén viszont célszerő az őrlapfejben vagy az őrlaplábban elhelyezni a parancsgombokat.) Válasszunk a KATEGÓRIÁK és a MŐVELETEK közül. Pl.: ŐRLAPMŐVELETEKŐRLAP BEZÁRÁSA. Döntsük el, hogy kép vagy szöveg lesz-e a parancsgomb. Majd gépeljük be a megfelelı szöveget, vagy válasszuk ki a képet. Nevezzük el a parancsgombot, és őrlapnézetben próbáljuk ki a mőködését.

Szöveges parancsgomb

Kép

4. Táblák rendezése, szőrése A feladat az alábbi eszköztár gombjainak segítségével végezhetı el. Nyissuk meg az AUTÓK táblát adatlap nézetben. Elsı lépésben oszlopmőveletek használatával szélesítsük a kívánt oszlopot. Szükséges, mivel az autó ára oszlop túl keskeny, így nem látható a mezı tartalma. Megoldása hasonló az Excelben kínált lehetıséggel. Azaz vagy a mezıneveket tartalmazó sorban az oszlophatáron kétszer kattintunk, vagy egérrel növeljük az oszlopszélességet.

Az évjárat oszlopot helyezzük el a szín oszlop mellett. Vagyis jelöljük ki az egész Évjárat oszlopot. A mezınévnél a „fogd és vidd” módszerrel mozgassuk az oszlopot az Ár oszlop elé. Mivel túl sok mezıbıl áll a táblánk, ezért nem látható minden mezı. Mozogjunk a táblában úgy, hogy az autó rendszáma mindig látható legyen. Elérhetjük úgy, hogy kijelöljük a Rendszám oszlopot, majd kiválasztjuk a FORMÁTUM menü OSZLOP RÖGZÍTÉSE lehetıséget. A rendezés az alábbi két lépésben végezhetı el: a.) Jelöljük ki a rendezendı oszlopot vagy oszlopokat. b.) Válasszuk a REKORD/RENDEZÉS NÖVEKVİ (CSÖKKENİ) parancsot vagy a rendezés ikonját. Fejezet: Bozó Mária f. adjunktus

123

Adatbáziskezelés


Ha olyan oszlopok szerint szeretnénk rendezni, amelyek nincsenek egymás mellett, akkor az oszlopok áthelyezésével mozgassuk át az oszlopokat egymás mellé. Válogassuk ki az Audi típusú autókat! A feladatot Szőrés kijelöléssel módon végezzük el. Elsı lépés: álljunk a tábla Típus nevő oszlopban arra a cellára, ahol az Audi megnevezés szerepel. Majd válasszuk a SZŐRÉS KIJELÖLÉSSEL ikont, (vagy REKORD/SZŐRİ/SZŐRÉS KIJELÖLÉSSEL menüt). Az eredeti tábla a SZŐRÉS ELTÁVOLÍTÁSA ikonnal vagy a REKORD/SZŐRÉS/RENDEZÉS TÖRLÉSE menü paranccsal visszaállítható.

Válogassuk ki azokat az autókat, amelyeknek a hengerőrtartalma 1000 és 2000 közötti. A megoldás SZŐRÉS gyorsmenüvel segítségével adjuk meg. Álljunk a tábla Áru egységára oszlopra. Kattintsunk a jobb egér gombbal. Megjelenik a mellékelt gyorsmenü. Írjuk be a SZŐRENDİ sorba a feltételt. Between 1000 and 2000 vagy >=1000 and <=2000

4.2. Tanfolyam adatbázis (egytáblás példa) 1. Egyszerő lekérdezések Készítsen lekérdezést, amely kilistázza azokat az autókat, amelyeknek a hengerőrtartalma 1000 és 2000 közötti. Rendezzük az eredményt rendszám szerint növekvı sorrendben. LEKÉRDEZÉS/ÚJ/TERVEZİ nézet Tábla hozzáadása ablakban adjuk meg az Autó táblát. Majd jelenítsük meg a mezı sorban a szükséges mezıket. Adjuk meg a rendezést a Rendszám mezınél (NÖVEKVİ). Adjuk meg a feltételt a hengerőrtartalomnál: Between 1000 and 2000. Váltsunk adatlap nézetbe, vagy a ! ikonnal futtassuk le a lekérdezést. Zárjuk be a lekérdezést és mentsük el.

Listázzuk ki az Audi típusú és szürke, vagy piros színő autókat! (Piros színő autó nemcsak AUDI típusú lehet.)

Listázzuk ki a legdrágább autó adatait! 124



Adatbáziskezelés

Rendezzünk Ár szerint csökkenı sorrendbe, és csak egy rekordot írassunk ki.

Az Autók* azt jelenti, hogy az összes mezıt szeretnénk megjeleníteni. 2. Kereszttáblás lekérdezés Készítsünk kereszttáblás lekérdezést, amely autó típusonként és színenként összesíti az árakat! Jelenítse meg a típusonkénti összárat is. A lekérdezés kereszttábla sorában két sorfejléc, egy oszlopfejléc és egy érték kerül kiválasztásra. A második sorfejlécben összesíthetjük az egy sorban szereplı árakat.

4.3. Tanfolyam adatbázis (egytáblás feladat és példa) Legyen a minta tábla az alábbi:

1. feladat: Tábla létrehozás (egyéni feladat lépésekkel segítve) 1. Hozza létre a TANULÓ táblát, határozza meg az adattípusokat, az elsıdleges kulcsot és ahol lehet adja meg az érvényességi szabályokat! 2. Adatlap nézetben rögzítsen néhány rekordot! 3. Módosítsa a TANULÓ tábla szerkezetét! Legyen a táblának egy OLE objektum típusú Kép és egy Pénznem típusú Tanfolyamdíj mezıje! 4. A további adatfelvitelhez készítsen egyoszlopos őrlapot! Készítsen az őrlapra rekordléptetı és őrlapbezáró parancsgombokat, valamint egy „Új hallgató felvitele” feliratú parancsgombot!


125

Adatbáziskezelés


2. feladat: Választó lekérdezések Készítse el az alábbi lekérdezéseket! 1. Válogassa ki azokat a rekordokat, amelyekben a város neve S vagy B betővel kezdıdik! 2. Listázza ki a Kati becenevő tanulók nevét és címét! 3. Listázza ki a tanuló kódját, nevét és a helységet! Rendezze a rekordokat helység, azon belül név szerint növekvı sorrendbe! 4. Válogassa ki azokat a rekordokat (Tanuló neve, Irányítószáma, Helység), amelyekben az irányítószám nem 0-ra végzıdik! 5. Válogassa ki azokat a rekordokat (Tanuló neve, Irányítószáma, Helység), amelyekben az irányítószám 1-re, 2-re vagy 4-re végzıdik! 6. Listázza ki azokat a tanulókat, akiknek van telefonja? (Telefon mezıben a feltétel: IS NOT NULL) 7. Határozza meg, mennyi rekordunk van a táblában? 8. Válogassa ki a tanulók közül a salgótarjáni nıket! A megoldás tervezı nézete:

Ha a logikai feltétel igaz, akkor –1 vagy YES, ha a feltétel hamis, akkor 0 vagy NO szerepel a feltétel sorban. 9. Számolja össze a férfitanulókat! A megoldás tervezı nézete:

A második oszlopban (a Count függvény fölötti mezıben) a tábla bármelyik mezıneve állhat, az eredmény ugyanaz lesz. 10. Hány tanulónak nincs telefonja? (Telefon mezıben a feltétel: IS NULL) 11. Listázza ki a tanulók nevét és címét! Az Ir_szám, Helység, Út egy Cím nevő oszlopba kerüljön! Rendezze a rekordokat név szerint növekvı sorrendbe! 12. A 8. feladatot oldjuk meg úgy, hogy a helység paraméter legyen! A megoldás tervezı nézete: 126



Adatbáziskezelés

A Cím mezı beíráshoz célszerő a kifejezés szerkesztıt használni. 13. Listázza ki a 40 évnél idısebb tanulók kódját, nevét és korát! A megoldás tervezı nézete:

A DateDiff(intervallum; dátum1; dátum2) két dátum megadott intervallum szerinti különbségét adja. Az intervallum lehet pl.: yyyy-Év, q-Negyedév, m-Hónap, d-nap stb. 14. Listázza ki azokat a tanulókat, akik májusban születtek! Megoldás javaslat: A Születési dátum feltétel sorában:*.05.*, vagy kifejezéssel egy új oszlopban: Hónap: DatePart("m";[tanuló]![Születési dátum]) és a feltétel sorban 5 áll. 3. AKCIÓ lekérdezések (példa) Az akció lekérdezések elsı lépése mindig egy választó lekérdezés elkészítése. Következı lépésként kiválasztjuk a Lekérdezés típusok listából a szükséges akció lekérdezést. Táblakészítı lekérdezés

Hozzáfőzı lekérdezés

A Tanuló táblából vigyük egy „Több helység” Főzzük hozzá a Több helység nevő táblához a nevő táblába a salgótarjáni hallgatókat. bátonyterenyei hallgatókat.

Törlı lekérdezés

Frissítı lekérdezés

Töröljük a Több helység táblából azokat, Emeljük meg a tanfolyamdíjat 20%-al. akiknek a neve K betővel kezdıdik.


127


Adatbáziskezelés 4. Jelentéskészítés (példa)

Készítsen jelentést, amelyben helységenkénti csoportosításban látható a tanuló kódja, neve, az út és a telefonszáma, valamint rendezze a listát a tanuló névre! Megoldás: Válasszuk a JELENTÉS/ÚJ/JELENTÉS varázslót. Adjuk meg a jelentés adatforrását, a Tanuló táblát. Válasszuk ki a mezıneveket. A csoportosítás párbeszédablakban kiemeljük a Helységet.

Tervezı nézetben a „Rendezés és csoportosítás” ablakban a megadható a név szerinti rendezés.

A jelentés eredményét exportáljuk Word-be! Megoldás: A jelentés Nyomtatási kép nézetében ikonnal vagy az ESZKÖZÖK/OFFICE LINKS/SZERKESZTÉS MS Worddel menüvel az adatbáziskezelı elkészíti a jelentésbıl az rtf formátumú fájlt. (Hasonló módon exportálhatunk Access objektumokat Excelbe is.) Helység

Kód

Név

Út

013 101 113 213 505

Bozó Róbertné Hegedős Ervinné Holczinger Géza Lahos Sándorné Nagy Lajos

Iskola Kossuth Ózdi Kossuth Kossuth

010

Antal László

Sugár

Telefon

Bátonyterenye 32/353339 32/353339 32/353155

Kishartyán

4.4. Fizetés adatbázis (kéttáblás példa és feladat) Legyen adott a Dolgozó és a Havi adatok tábla. A Havi adatok a dolgozók egy éven belüli havi fizetéseit tartalmazza, valamint egy dolgozó egy hónapban csak egyszer kaphat fizetést. Határozzuk meg a táblák adattípusait, az elsıdleges kulcsokat és a táblák közötti kapcsolatokat. Dolgozó Törzsszám 1 2 3 4 5

128

Név Kovács Péter Gál András Lakatos Lajos Kiss Tibor Kocsis Zoltán

Születési idı 1965.05.01. 1963.04.05. 1967.11.11. 1970.05.05. 1974.04.11.

Részleg Feldolgozó TMK Feldolgozó TMK TMK

Beosztás Gépkezelı Targoncás Csoportvezetı Lakatos Villanyszerelı

Órabér 580 Ft 400 Ft 700 Ft 440 Ft 550 Ft


: www.pszfsalgo.hu, : [email protected], : 30/644-5111 Havi adatok Dolgozó Törzsszám 1 1 2 2 3 3

Hó 1 2 1 2 1 2

Ledolgozott óra 168 145 155 167 170 172

Adatbáziskezelés

Pótlék 4000 Ft 0 Ft 2000 Ft 3000 Ft 0 Ft 0 Ft

A Dolgozó táblában a Törzsszám az elsıdleges kulcs. A Havi adatok táblában a Dolgozó törzsszám és a Hó együttesen lesz az elsıdleges kulcs, mivel egy dolgozó törzsszáma többször is elıfordulhat az év során, viszont ugyanaz a törzsszám és az adott hónap csak egyszer szerepelhet a második táblában. Milyen adattípust célszerő adni a Törzsszámnak, valamint milyen tényezık alapján dönti el? Ha a cégnél a dolgozók száma nem haladja meg a 255 fıt és szeretnénk, hogy a számítógép ne adja automatikusan a sorszámot, akkor választhatjuk a szám és bájt adattípust. Ha az adott mezıt nem akarjuk módosítani (pl.: 2 helyett 200-at írni) és a gépre bízzuk a sorszámozást, akkor lehet számláló típusú a mezı. Lehet-e szöveg a Törzsszám? Rendezés miatt nem célszerő szöveg típust választani, mert akkor az 1 után a 10,11,…,100,101,..következik és csak ezek után látható a 2,20,… sorszámú dolgozó. (Jelen esetben legyen bájt típusú a törzsszám, mert ebben a példában szeretnénk megvizsgálni a „kaszkádolt frissítést”.) Mikor kapcsolhatunk össze mezıket? Figyelem: Csak azonos típusú mezıket kapcsolhatunk össze. A két táblában a kapcsolatot létesítı mezık neve lehet különbözı, de a típusok nem. Ha pl.: a Dolgozó táblában a Törzsszám számláló lenne, akkor a Havi adatok táblában nem lehet szöveg, vagy Bájt típusú szám. Mivel a számláló hosszú egész típus, így a Havi adatok táblában a Dolgozó Törzsszámnak is Hosszú egész szám típust kellene adni. Válaszoljon: Miért nem lehet a Törzsszám mindkét táblában számláló adattípus? 1. Összetett elsıdleges kulcs megadása: Jelöljük ki a Dolgozó Törzsszám és a Hó sorokat. Válasszuk az Elsıdleges kulcs ikont.

Az elsıdleges kulcs (Primary key) beállítást az Indexek párbeszédablakban is meg lehet tekinteni, és az Indexnevet is átnevezhetjük. Az egyedi beállítás jelentése: egy dolgozó törzsszámot csak különbözı hónapszámmal lehet felvinni.


129

Adatbáziskezelés


2. Táblák közötti kapcsolatok létrehozása Határozza meg a táblák közötti kapcsolatokat! Válasszuk az ESZKÖZÖK MENÜ/KAPCSOLATOK menüpontot, vagy a KAPCSOLATOK ikont. Ekkor megjelenik a TÁBLÁK HOZZÁADÁSA párbeszédablak.

Jelöljük ki mindkét táblát, majd a HOZZÁADÁS, végül a BEZÁRÁS gombra kattintunk. Kössük össze a táblák megfelelı mezıit egymással. A KAPCSOLATOK párbeszédablakban ellenırizzük az összekapcsolandó táblák és mezık nevét. A HIVATKOZÁSI INTEGRITÁS MEGİRZÉSE jelölınégyzetet kapcsoljuk be.

A kapcsolatok eredménye:

3. Lekérdezések: csoportosítás, összegzés Számolja ki a dolgozónkénti eddigi összes bért! (Összes bér: a ledolgozott órák és az órabér szorzata + a pótlékok összege) Hozzuk le a dolgozó törzsszámát és nevét a mezı sorba. A harmadik mezı kifejezés lesz. Kattintsunk a mezıbe majd a SZERKESZTÉS ikonra. Megjelenik a KIFEJEZÉSSZERKESZTİ párbeszédablak.

Válasszuk ki a Táblák objektum megfelelı mezıjét (pl Havi adatok tábla Ledolgozott órák). A BEILLESZTÉS gombbal vagy a mezı néven 2-szer kattintva a párbeszédablak felsı részébe kerül a mezınév. A Kif1 mezınevet írjuk át ÖsszBér-re. Válasszuk a ∑ ikont, majd az Összesítés sorban az oszlopfüggvények közül a Sum függvényt.

ÖsszBér: Sum([Dolgozók]![Órabér]*[Havi adatok]![Ledolgozott óra]+[Havi adatok]![Pótlék]) A lekérdezés eredménye:

130



Adatbáziskezelés

Összesítse Dolgozónként a ledolgozott órákat! Megoldás:

Összesítse Dolgozónként és havonta a ledolgozott órákat! Megoldás:

4. Illesztés típusok Adjuk meg azokat a dolgozókat, akik nem kaptak még fizetést? Megoldás: Készítsünk választó lekérdezést, amelyben felvesszük a két táblát: a dolgozó nevét és a ledolgozott órákat. A táblák közötti alapértelmezett „jobb oldali szoros kapcsolat”-ból adódik, hogy csak azok a rekordok jelennek meg az eredményhalmazban, amelyeket mindkét tábla tartalmaz. A kapcsolatvonalon (2-szer kattintva vagy gyorsmenüvel) jelenítsük meg az ILLESZTÉSI TULAJDONSÁGOK párbeszédablakot.

Módosítsuk a kapcsolattípust 2-re. („Baloldali laza illesztés”) A Ledolgozott órák feltételsorába írjuk be: IS NULL és a megjelenítést kapcsoljuk ki. A lekérdezés eredménye: 5. Allekérdezés Listázzuk ki azokat a dolgozókat, akiknek az órabére meghaladja az átlagórabért. Készítsünk egy lekérdezést, amely megadja az átlagórabért és mentsük a lekérdezés SQL nézetét a vágólapra: SELECT Avg(Dolgozók.Órabér) AS [Avg:Órabér]FROM Dolgozók; Készítsünk egy választó lekérdezést, amelynek a feltételsorába a > reláció jel után beillesztjük a vágólap tartalmát:

6. Kaszkádolt frissítés és kaszkádolt törlés Módosítsa a 2 számú dolgozó törzsszámát 200-ra! Törölje a 3 számú dolgozó adatait mindkét táblából! Nyissuk meg a Kapcsolatok párbeszédablakot. (Ikonnal vagy az ESZKÖZÖK/KAPCSOLATOK menüvel) Kattintsunk kétszer a 2 táblát összekötı kapcsolat vonalra, vagy jobb egérgombbal a gyorsmenübıl válasszuk a KAPCSOLAT SZERKESZTÉSE… lehetıséget. A KAPCSOLATOK ablakban engedélyezzük a Kaszkádolt frissítést és a kaszkádolt törlést. Ok-val fogadjuk el a módosításokat. Fejezet: Bozó Mária f. adjunktus

131

Adatbáziskezelés


a.) Kaszkádolt frissítés Ha módosítjuk a dolgozótáblában az egyik dolgozó törzsszámát és engedélyezett a kaszkádolt frissítés, akkor az adott dolgozó törzsszáma az alárendelt (Havi adatok) táblában is módosul. b.) Kaszkádolt törlés Célszerő megnyitni mindkét táblát. Jelöljük ki a Dolgozó táblában a 3-as törzsszámú dolgozó rekordját. A DELETE billentyő használata után a párbeszédablakban Igennel fogadjuk el a törlést.

Az alárendelt táblában a 3-as törzsszámú dolgozó rekordjaiban a #Törölt# bejegyzés látható. Ha bezárjuk a Havi adatok táblát és újra megnyitjuk, akkor látható, hogy a fenti rekordok végleg törlıdtek.

4.5. Feladatok elsıdleges kulcsok és táblák közti kapcsolatok meghatározására Termék1 adatbázis Határozza meg az alábbi táblákban az elsıdleges kulcsokat és a táblák közötti kapcsolatokat! Termékcsoport Termék Csoport Csoport név Csoport Termék- Megnevezés Egységár kód kód kód 10 Tejtermék 10 1 Tej 140 Ft 20 Zöldség 10 2 Tejföl 170 Ft 30 Gyümölcs 10 3 Vaj 90 Ft 40 Húsáru 20 4 Paprika 300 Ft 20 5 Paradicsom 200 Ft Termék2 adatbázis Határozza meg az alábbi táblákban az elsıdleges kulcsokat és a táblák közötti kapcsolatokat! Termékcsoport Termék Csoport Csoport név Csoport Termék- Megnevezés Egységár kód kód kód 10 Tejtermék 10 001 Tej 140 Ft 20 Zöldség 10 002 Tejföl 170 Ft 30 Gyümölcs 10 003 Vaj 90 Ft 20 001 Paprika 300 Ft 20 002 Paradicsom 200 Ft

132



Adatbáziskezelés

Gépterem adatbázis Határozza meg az alábbi táblákban az elsıdleges kulcsokat és a táblák közötti kapcsolatokat. Gépterem Hol mikor milyen tanfolyam Terem Gépek TanfoTerem Légkondiconált Dátum Tárgy kód száma lyam kód 1 28 Igen 1 2001.05.01.08: típus A01 Word 2 15 Nem 1 2001.05.01.14: K01 Excel 3 18 Nem 2 2001.05.01.08: R02 Novell 4 17 Nem 2 2001.05.01.14: R02 Pascal 1 2001.05.0208: A01 Word Tanfolyamok Tanfolyamtípus Kezdés Dátuma A01 K01 R02 A02

2001.01.07. 2001.02.14. 2001.05.01 2001.01.07

Óraszám 200 350 650 230

Tandíj 45000 85000 165000 10000

Leírás Alapfok tanult tárgyak Windows Középfok Rendszerinformatikus Munkanélküli Alapfok

4.6. Hobby adatbázis 1. Tábla importálása Feladat: Hozzon létre egy üres adatbázist, amelynek a Hobby nevet adja, importálja a Tanfolyam adatbázisból a Tanuló táblát! Megoldás: Válassza a TÁBLÁK/ÚJ/TÁBLA importálása opciót! Adja meg a helyet és az importálandó fájl nevét! Kattintson az IMPORTÁLÁS gombra. Megjelenik az OBJEKTUM IMPORTÁLÁSA párbeszédablak. Válassza ki a Tanuló táblát. Az Ok gomb lenyomása után megjelenik a Tanuló tábla a Hobby adatbázisban. Hozza létre tervezı nézetben a Hobby és a Kinek mi a hobbija táblákat! Hobby Hobby Kód Hobby név AE Aerobik AU Autó HO Horgászás KE Kertészet KI Kirándulás MO Motorsport OL Olvasás SP Sport TE Természetjárás ÚT Utazás VI Virág

Kinek mi a hobbija Hobby kód Tanuló Forint AE 101 0 Ft AE 510 1 400Ft AU 420 10 000Ft HO 297 0 Ft HO 301 3 000Ft HO 516 500Ft KE 142 2 000Ft KI 101 1 000Ft KI 113 2 000Ft MO 342 1 000Ft OL 010 0 Ft OL 397 0 Ft SP 142 500 Ft TE 010 2 000Ft Út 301 1 000 Ft VI 490 1 500Ft

Határozza meg az elsıdleges kulcsokat és a táblák közötti kapcsolatokat! A kapcsolatok eredménye: Fejezet: Bozó Mária f. adjunktus

133

Adatbáziskezelés


A Hobby táblában a Hobby kód, a Tanuló táblában a Kód, a Kinek mi a hobbija táblában a Hobby kód és a tanuló kód az elsıdleges kulcs. A Kinek mi a hobbija tábla alárendeltje a másik két táblának. Készítse el az alábbi lekérdezéseket! 1. Paraméteres: paraméter: hobby neve, eredmény: tanuló neve. 2. Paraméteres: paraméter: tanuló neve, eredmény: hobby neve. 3. Kereszttáblás: tanulónként és hobby nevenként a költségek. 4. Kinek nincs hobbija? 5. Tanulónként mennyi a hobbira költött összeg? 6. 1000 és 3000 Ft közötti összegek (Tanuló neve, hobby neve, összeg). 7. Frissítı lekérdezés: Összeget növeljük 10%-al. 8. Ki és melyik hobbira költ a legtöbbet? 9. Tanuló neve, hobby neve, a havi összeg, az éves összeg (12 hónappal számolva). 2. Kombipanel elkészítése az őrlapon (példa) Készítsünk őrlapot a tanulók hobbijának a felviteléhez, a tanuló neve és a hobby kódja egy legördülı listából legyen választható! Megoldás lépései: 1. Készítsünk táblázatos őrlapot a Kinek mi a hobbija táblához. 2. Nyissuk meg az őrlapot tervezı nézetben. 3. Töröljük a törzs szakaszban a Tanuló kód és Hobby kód vezérlıelemeket. 4. Kattintsunk az eszközkészlet kombipanel varázsló ikonjára. 5. Egérrel rajzoljunk egy téglalapot arra a helyre, ahonnan az elıbb töröltük a Tanuló kód vezérlıelemet. Elindul a varázsló. 6. Az elsı párbeszédablakban a felkínált „Szeretném, ha a kombinált lista vezérlıelem megkeresné az értéket az adott táblában vagy lekérdezésben” lehetıséget fogadjuk el. 7. A következı párbeszédablakban válasszuk ki a Tanuló táblát, majd a táblából a kód és a név mezıket. 8. Fontos, hogy megadjuk a Tanuló kód mezıt az érték tárolásához.

9. Mentsük a vezérlıelemet. Tervezzük meg a Hobby kombipanelt is! 10. Tervezı nézetben töröljük ki a kombipanel címkéjét. 11. Helyezzük el a kombipanelt a megfelelı őrlapfej felírat alá.

134



Adatbáziskezelés

Kombipanel címke

Feladatok 1. Készítsen jelentést, amelyben összesíti hobby nevenként a hobbira fordított összeget: A jelentés fejléce: Hobby neve, hobby kódja, tanuló neve, tanuló kódja, címe, összeg. 2. Készítsen jelentést, amelyben összesíti hobby nevenként a hobbira fordított havi és éves összeget: A jelentés fejléce: Hobby neve, hobby kódja, tanuló neve, tanuló kódja, címe, havi költség, éves költség.

4.7. Mikulás adatbázis (feladat) Hozza létre a MIKULÁS adatbázist a következı táblákkal! AJÁNDÉKOK (Mit rejt a Mikulás puttonya?) Ajándék kód Termék név 1 Tejcsokoládé 2 Csoki mikulás 3 Cukor 4 Barby 5 Legó 1 6 Legó 2 7 Autó 8 Virgács

Db 20 30 5 4 7 9 25 10

GYEREKEK (Akiket meglátogat a Mikulás.) Gyermek Név Cím Megjegyzés 1 Kiss Janika Kossuth út 5 Jó 2 Kiss Márton Kossuth út 5 Rossz 3 Levente Pis- Mártírok útja Kiváló 4 Kovács Csilla Rákóczi út 42 Változó 10 Joó Janika Kossuth út 15 Jó 20 Balázs Lilike Rákóczi út 43 Jó GYEREK-AJÁNDÉK (Ki mit kapott a Mikulástól?) Sorszám Gyermek Ajándék kód Mennyiség 1 1 1 2 2 1 7 3 3 2 2 1 4 2 7 2 5 2 8 2 Fejezet: Bozó Mária f. adjunktus

135


Adatbáziskezelés 6 7 8 9 10 11 12

3 3 3 10 10 10 20

1 3 5 2 6 7 8

3 2 3 1 2 2 1

Feladatok: 1. Készítsen táblázatos őrlapot a 3 tábla felviteléhez. A gyermek neve és az ajándék neve a Ki mit kapott a Mikulástól őrlapon legyen kombipanel. 2. Hány gyermek kapott legót? 3. Ki nem kapott virgácsot? 4. Ki nem kapott még ajándékot? 5. Milyen ajándékból mennyi maradt a Mikulás puttonyában? Megoldás: Két lekérdezést kell elkészíteni. Az elsı lekérdezésben ajándékkódonként összesítjük a mennyiséget. A második lekérdezés adatforrása az elıbb elkészített lekérdezés és az Ajándékok tábla. KIFEJEZÉS-SZERKESZTİVEL kiszámítjuk a két mennyiség különbségét. 6. Készítsen jelentést, amelyben ajándék kódra csoportosít és összesíti az ajándékokat. A jelentésben az alábbi mezık szerepeljenek: ajándék kódja, ajándék neve, gyermek kódja, gyermek neve, gyermek címe, a kapott ajándék mennyisége.)

4.8. Törlesztés (Éves törlesztések nyilvántartása, feladat) Hozza létre a KÖLCSÖN adatbázist az alábbi táblákkal. A TÖRLESZTÉS tábla az adósok egy éven belüli törlesztéseit tartalmazza. ADÓS Adós kódja 28 31 68 71 72 73 74 75 76 77 78 79 80 81

Adós neve Pál János Ádám Zoltán Szabó Pál Horváth Aladár Kiss Péter Zoltán Ákos Bors Levente Kozma Jolán Kiss Árpád Orosz Ádám Németh Éva Kiss Kálmán Nagy Mária Levente Péter

HELYSÉG Irányítószám 3070 3078 3151

136

Irszám 3070 3078 3078 3070 3070 3078 3070 3078 3070 3070 3078 3070 3078 3070

Kölcsönzés dátuma Fı út 13. 1993.11.08 Abonyi út 20 1993.11.12 Csillag út 1/6. 1995.11.14 Fı út 5. 1995.12.07 Szabadság út 1995.12.12 Mátyás k. út 1996.07.23 Gyöngyvirág 1996.07.11 Arany János 1996.07.15 Széchenyi út 1996.07.24 Ózdi út 25 1996.12.06 Klapka út 60 1996.12.09 Liget út l4. 1996.12.09 Déryné út 48. 1996.12.10 Ózdi út 106. 1996.12.09 Út

Kölcsönzés összege 80 000 Ft 50 000 Ft 100 000 Ft 50 000 Ft 70 000 Ft 100 000 Ft 70 000 Ft 100 000 Ft 100 000 Ft 200 000 Ft 80 000 Ft 120 000 Ft 100 000 Ft 100 000 Ft

Törlesz- Havi törtés lesztés kezdete összege 1994.12.0 6 666 Ft 1994.11.0 4 167 Ft 1997.01.0 8 334 Ft 1997.01.0 4 167 Ft 1997.01.0 5 832 Ft 1997.07.0 8 334 Ft 1997.08.0 5 832 Ft 1997.08.0 8 334 Ft 1997.08.0 8 334 Ft 1998.01.0 16 667 Ft 1998.01.0 6 667 Ft 1998.12.0 8 334 Ft 1998.01.0 8 334 Ft 1998.01.0 8 334 Ft

Helység név Bátonyterenye Bátonyterenye Kisterenye Bátonyterenye Rákóczitelep


: www.pszfsalgo.hu, : [email protected], : 30/644-5111 TÖRLESZTÉS Adós Banki kód kivonat 28 11/00 28 116/00 28 136/00 28 158/00 28 224/00 28 244/00 28 74/00 28 97/00 71 114/00 71 200/00 71 231/00 75 10/00 75 121/00 75 191/00 75 216/00 75 98/00 76 111/00 76 239/00 76 3/00

Adatbáziskezelés

Törlesztés Törlesztés Adós Banki Törlesztés Törlesztés dátuma összeg kód kivonat dátuma összeg 2000.01.17. 2 000 Ft 77 131/00 2000.07.06. 16 660 Ft 2000.06.15. 2 000 Ft 77 199/00 2000.10.10. 16 666 Ft 2000.07.13. 2 000 Ft 77 6/00 2000.01.10. 16 667 Ft 2000.08.14. 2 000 Ft 77 68/00 2000.04.06. 16 667 Ft 2000.11.16. 4 000 Ft 78 149/00 2000.08.01. 6 667 Ft 2000.12.14. 4 000 Ft 78 214/00 2000.11.02. 6 667 Ft 2000.04.14. 2 000 Ft 78 22/00 2000.02.01. 6 667 Ft 2000.05.18. 2 000 Ft 78 86/00 2000.05.03. 6 667Ft 2000.06.13. 2 000 Ft 79 136/00 2000.07.13. 8 332Ft 2000.10.11. 1 500 Ft 79 200/00 2000.10.11. 8 328Ft 2000.11.27. 1 500 Ft 79 75/00 2000.04.17. 8 334Ft 2000.01.14. 8 334 Ft 79 8/00 2000.01.12. 8 334Ft 2000.06.22. 8 334 Ft 80 101/00 2000.05.24. 8 332Ft 2000.09.28. 8 334 Ft 80 155/00 2000.08.09. 8 332Ft 2000.11.06. 8 334 Ft 80 200/00 2000.10.11. 8 332Ft 2000.05.19. 8 334 Ft 80 28/00 2000.02.09. 8 334Ft 2000.06.07. 8 334 Ft 2000.12.07. 8 334 Ft 2000.01.05. 8 334 Ft

Készítse el az Adósok felviteléhez az alábbi őrlapot!

Készítse el a törlesztések felviteléhez a következı fı-segédőrlapot!


137

Adatbáziskezelés


Készítse el az alábbi lekérdezéseket! 1. 2. 3. 4. 5.

Ki vette fel a legtöbb kölcsönt? Listázza ki azokat az adósokat, akik 100 000 és 200 000 Ft közötti kölcsönt vettek fel. Melyik adós hányszor törlesztett? Melyik adós nem törlesztett még? Készítsen paraméteres lekérdezést, ahol a paraméter az adós neve, eredmény: adós címe, rendszeres törlesztés összege. Készítsen paraméteres lekérdezést, ahol a paraméter az adós neve, eredmény: törlesztés részletezése: banki kivonat, törlesztés dátuma, törlesztés összege. Jelenítse meg az adós nevét, címét és az eddigi összesített törlesztését. Listázza ki az adós nevét, címét, a kölcsön összegét, az eddigi összesített törlesztést és a tartozás összegét. Frissítı lekérdezés: Növelje meg a kölcsön összegét 550 Ft-al!

6. 7. 8. 9.

Készítse el az alábbi jelentéseket!

Részletes havi törlesztések Kód Név 28 Pál János

Ir.sz. Út 3070 Fı út 13.

Dátum

Banki kivonat

Összeg

2000.01.17.

11/00

2 000 Ft

2000.04.14.

74/00

2 000 Ft

2000.05.18.

97/00

2 000 Ft

2000.06.15.

116/00

2 000 Ft

2000.07.13.

136/00

2 000 Ft

2000.08.14.

158/00

2 000 Ft

2000.11.16.

224/00

4 000 Ft

2000.12.14.

244/00

4 000 Ft

Összegzés: 'Adós kódja' = 28 (8 törzsrekord) 20 000 Ft

71 Horváth Aladár

3070 Fı út 5. 2000.06.13.

114/00

2 000 Ft

2000.10.11.

200/00

1 500 Ft

2000.11.27.

231/00

1 500 Ft


138



Adatbáziskezelés

---------------------------------------------------------------------------------------------------------------80 Nagy Mária

3078

Déryné út 48. 2000.02.09.

28/00

8 334 Ft

2000.05.24.

101/00

8 332 Ft

2000.08.09.

155/00

8 332 Ft

2000.10.11.

200/00

8 332 Ft


Végösszeg

251 658 Ft

Adósok tartozása Adós neve

Horváth Aladár

Adós kódja

71

Irányító szám

3070

Helység név

Út

Kölcsön Törlesztés Törlesztés Összes összege dátuma összeg Törlesztés

50 550 Ft

Bátonyterenye

5 000 Ft

Tartozás

45 550 Ft

Fı út 5. 2000.06.13.

2 000 Ft

2000.10.11.

1 500 Ft

2000.11.27.

1 500 Ft 5 000 Ft

Kiss Árpád

76

3070

100 550 Ft

Bátonyterenye

25 002 Ft

75 548 Ft

Széchenyi út 100 2000.01.05.

8 334 Ft

2000.06.07.

8 334 Ft

2000.12.07.

8 334 Ft 25 002 Ft

----------------------------------------------------------------------------------------------------------------

Pál János

28

3070

80 550 Ft

Bátonyterenye

20 000 Ft

60 550 Ft

Fı út 13. 2000.01.17.

2 000 Ft

2000.04.14.

2 000 Ft

2000.05.18.

2 000 Ft

2000.06.15.

2 000 Ft

2000.07.13.

2 000 Ft

2000.08.14.

2 000 Ft

2000.11.16.

4 000 Ft

2000.12.14.

4 000 Ft

20 000 Ft

Végösszeg


251 658 Ft

139


Adatbáziskezelés

4.9. Üveggyár (Excel táblák importálása) Importálja az alábbi 5 táblát Excelbıl, módosítsa az adattípusokat, határozza meg az elsıdleges kulcsokat és a táblák közötti kapcsolatokat. Javaslat: Excel munkafüzet importálasakor a következı lépésekre kell figyelni: ♦ A fájltípusnál be kell állítani a Microsoft Excel típust. Minden munkalapot csak külön-külön importálhatunk. ♦ „Az oszlopfejléceket az 1. sor tartalmazza” jelölınégyzetrıl ne feledkezzünk meg. ♦ Nem célszerő „Az Access adjon elsıdleges kulcsot a táblához” lehetıség elfogadása, hasznosabb a „Magam választom ki” vagy a ”Ne legyen elsıdleges kulcs” bejelölése az importálás befejezése elıtt. TERMÉKCSOPORT Csoport kód Csoport név 01 csillár 02 pohár 03 kehely 04 váza TERMÉK Termékkód 1 2 3 4 5 6 7 8 9 10 11 12

Terméknév

Egységár

4 ágú csillár 6 ágú csillár díszes csillár Talpas pohár Konyakos pohár Féldecis pohár Boros pohár Wiszkis pohár Pezsgıs pohár Fagyi kehely-01 Fagyi kehely-02 Fagyi kehely-03

5000 12000 17000 80 70 90 120 200 400 120 130 120

VEVİ Vevıkód Vevınév 1 Bodor és tsa kft 2 Kiss és Nagy bt 3 Kovács Lajos 4 Pál Éva 5 Co-Co Kft SZÁMLATÉTEL SzámlaTermékkód szám 1 1 1 2 1 4 1 5 1 6 140

Helység Salgótarján Etes Eger Salgótarján Eger

Termékcsoport kód

Minimális készlet

01 01 01 02 02 02 02 02 02 03 03 03

40 100 150 160 200 400 200 200 210 200 200 200

Cím Rákóczi út 59 Fı út 45 Fürdı út 23 Kistarján út 3/6 Gárdonyi G út 23

Vásárolt mennyiség 5 4 3 12 25

SZÁMLAFEJ Számlaszám Vevıkód 1 2 3 4 5

1 1 2 2 2

Dátum 2001.02.25 2001.02.26 2001.02.25 2001.02.26 2001.02.27


: www.pszfsalgo.hu, : [email protected], : 30/644-5111 1 2 2 2 2 2 3 4 5

7 1 2 4 5 7 1 2 7

Adatbáziskezelés

20 1 1 40 40 40 4 12 12

Készítse el az alábbi lekérdezéseket! 1. Listázza ki a 200 Ft-nál olcsóbb termékeket! 2. Mely termékcsoporthoz nem tartozik termék? (Termék Csoport-Csoport név, Termék-Termék név) 3. Melyek azok a termékek, amelyeket február 26 és 27 között vásároltak? ( Termék -Termék név, Számlafej- Dátum, Számlatétel) (A lekérdezés tulajdonságban az Egyedi érték Igen legyen.) 4. Listázza ki azokat a termékeket, amelyeknek az ára 90 és 400 Ft között van. 5. Listázza ki azokat a vásárolt termékeket, amelyeknek az ára 90 és 400 Ft-között van! (Termék, Számlafej) Egyedi érték! 6. Mely termékekbıl vásároltak eddig? (Termék -Termék név, Számla tétel) Egyedi érték! 7. Mely termékekbıl nem vásároltak még? (Termék-Termék név, Számla tétel –Vásárolt mennyiség /Is Null/) 8. Táblakészítı: Készítse el az Olcsó termék táblát, amely a 200 Ft alatti termékek adatait tartalmazza. 9. Frissítı: Olcsó termék táblában a „pohár” termékek árát növelje 40%-al. 10. Hozzáfőzı: Olcsó termék táblához a Termék táblából vigye át azokat a termékeket, amelyeknek az ára vagy 200 Ft vagy 400 Ft. 11. Törlı: Olcsó termék táblából törölje azokat a termékeket, amelyeknek az ára 200 Ft felett van. Csoportosítás: 12. Mennyi a Termékek táblában a Minimális, Maximális és Átlagár? 13. Hány termék nevében szerepel a „pohár”? 14. Mennyi az üzlet összbevétele? 15. Mennyi a termékenkénti eladások összmennyisége és összbevétele? 16. Paraméteres: Melyik termékbıl mennyit vásároltak? (paraméter a termék neve) 17. Melyek azok a termékek amelyekbıl több, mint 40-db-ot vásároltak? 18. Melyik vevı mennyit költött? 19. Melyik vevı melyik termékre mennyit költött? 20. Kereszttáblás lekérdezés: Terméknév, Vevınév, Vásárlás összesített értéke. Készítse el az alábbi őrlapokat! ⇒ Fı és segédőrlap: 1. Termékcsoport, Termék 2. Számlafej, Számlatétel (Kombipanelek: Termék, Vevı) Rögzítsen legalább 2 számlát (5-5 számla tétellel) március hónapra. ⇒ Engedélyezze a kaszkádolt törlést és törölje a márciusi számlákat! Készítse el az alábbi jelentéseket! 1. Vevıkód, Vevınév, Helység, Dátum, Termékkód, Terméknév, Egységár, Vásárolt mennyiség, Érték (Csoportosítás: Vevıkód, Dátum. Rendezés: Termékkód, Összesítés: Érték) 2. Termékkód, Terméknév, Egységár, Dátum, Számlaszám Vásárolt mennyiség, Érték (Csoportosítás: Termékkód, Dátum, Rendezés: Számlaszám, Fejezet: Bozó Mária f. adjunktus

141


Adatbáziskezelés

Összesítés: Vásárolt mennyiség, Érték)

Termékek eladása Termékkód 1

Dátum

Terméknév Egységár Vevõnév

4 ágú csillár 2001.02.25.

Helység

Vásárolt Érték mennyiség

5 000 Ft Kiss és Nagy bt

Etes

4 20 000 Ft

Bodor és tsa kft

Salgótarján

5 25 000 Ft

Dátum összesen:

9 45 000 Ft

2001.02.26. Bodor és tsa kft

Salgótarján

Dátum összesen:

5 000 Ft

1

5 000 Ft

10 50 000 Ft

Termék Összesen:

2

1

6 ágú csillár 2001.02.25.

12 000 Ft Bodor és tsa kft

Salgótarján

Dátum összesen:

4 48 000 Ft 4 48 000 Ft

2001.02.26. Kiss és Nagy bt

Etes

Bodor és tsa kft

Salgótarján

Dátum összesen:

12 144 000 Ft 1 12 000 Ft 13156 000 Ft 17204 000 Ft

Termék Összesen:

----------------------------------------------------------------------------------------------------------------

Vevõ-számlaszám összesítés Vevõkód Számlaszám Vevõnév

1

Helység Dátum Termék Termék Ár Vásárolt Érték kód név mennyiség

Bodor és tsa kft Salgótarján 1

2001.02.25. 1 4 ágú csillár 5 000 Ft

5 25 000 Ft

2 6 ágú csillár 12 000 Ft

4 48 000 Ft

7 Boros pohár

142

120 Ft 20

2 400 Ft

6 Féldecis pohár 90 Ft 25

2 250 Ft

5 Konyakos pohár 70 Ft 12

840 Ft



Adatbáziskezelés

4 Talpas pohár Összeg számlaszámra: 2

80 Ft

2001.02.26. 1 4 ágú csillár 5 000 Ft

1

2 6 ágú csillár 12 000 Ft

1 12 000 Ft

5 000 Ft

120 Ft 40

4 800 Ft

5 Konyakos pohár 70 Ft 40

2 800 Ft

4 Talpas pohár

3 200 Ft

80 Ft 40

Összeg számlaszámra:

27 800 Ft

Összeg vevõkódra: Kiss és Nagy bt Etes 3

240 Ft

78 730 Ft

7 Boros pohár

2

3

106 530 Ft

2001.02.25. 1 4 ágú csillár 5 000 Ft

Összeg számlaszámra: 4

4 20 000 Ft 20 000 Ft

2001.02.26. 2 6 ágú csillár 12 000 Ft 12 144 000 Ft

Összeg számlaszámra: 5

144 000 Ft 2001.02.27. 7 Boros pohár

Összeg számlaszámra:

120 Ft 12

1 440 Ft

1 440 Ft

Összeg vevõkódra: Végösszeg:

165 440 Ft 271 970 Ft

4.10. Normalizálás Példa: Szeretnénk nyilvántartani, hogy melyik tanuló mit győjt, továbbá azt is, hogy kinek hány db van eddig a győjtött tárgyból. Egy tanuló több tárgyat is győjthet, és ugyanazt a tárgyat többen is győjthetik. Az alábbi táblában nincs minden sorban kitöltve a tanulóra vonatkozó adatok. TANULÓ MIT GYŐJT Tanuló Tárgy Győjtött tárgy Név Ir_szám Város Út DB kód kód neve 1500 Kiss Antal 3100 Salgótarján Fı út 6 H1 Match-box 150 1600 1700 1800

Pál Éva Fehér Zsolt Jó Lajos

3104 3100 3300

Salgótarján Tó út 5 Salgótarján Fa út 6 Eger Sós út 6

1900

Molnár Kinga

3100

Salgótarján Petıfi út 1

H2 H1 H2 H1 H2 H3 H4

Pokémon kártya Match-box Pokémon kártya Match-box Pokémon kártya Bélyeg Jelvény

200 300 250 120 250 300 250

Töltsünk ki minden sort és határozzuk meg a tábla azonosítóját!


143

Adatbáziskezelés 0NF Tanuló kód 1500 1500 1600 1700 1800 1800 1800 1900


MIT GYŐJT A TANULÓ Tárgy Név Ir_szám Város Út kód Kiss Antal 3100 Salgótarján Fı út 6 H1 Kiss Antal 3100 Salgótarján Fı út 6 H2 Pál Éva 3104 Salgótarján Tó út 5 H1 Fehér Zsolt 3100 Salgótarján Fa út 6 H2 Jó Lajos 3300 Eger Sós út 6 H1 Jó Lajos 3300 Eger Sós út 6 H2 Jó Lajos 3300 Eger Sós út 6 H3 Válóczi György 3100 Salgótarján Petıfi út 1 H4

Győjtött tárgy neve Match-box Pokémon kártya Match-box Pokémon kártya Match-box Pokémon kártya Bélyeg Jelvény

DB 150 200 300 250 120 250 300 250

Az azonosító a Tanuló kód és a Tárgykód, mivel egy tanulókódhoz több tárgy kód is tartozik, de a két kód együtt minden sorban különbözı. (1500 H1, 1500 H2 stb.) A fenti tábla már 0NF-ben van. Teljesíti a relációs adatbázis elsı 5 jellemzıjét. Az egytáblás nyilvántartásnak több hátránya is van. A módosítás nehézkes, mivel egy táblában egy tulajdonság értéket csak úgy lehet változtatni, hogy a táblázat valamennyi sorát végignézzük. Pl.: ha a tanuló elköltözik, akkor az összes tanulóhoz tartozó rekordban át kell vezetni a változásokat. A törlés adatvesztéssel jár. Ha pl.: töröljük az 1900 kódon szereplı tanulót, akkor a jelvény, mint győjtött tárgy elveszik a nyilvántartásunkból, mert csak Válóczi György győjteménye között szerepelt. Bıvítési problémák: új tárgyat csak akkor vihetünk fel a táblába, ha valaki győjti, valamint új tanulót is csak akkor rögzíthetünk, ha valamit győjt. A győjtés többértékő tulajdonság (mivel egy tanulóhoz több győjtött tárgy is tartozhat). Ezért a tanuló adatait és a győjtéssel kapcsolatos tulajdonságokat vigyük egy új táblába. Csak akkor marad meg a kapcsolat a 2 tábla között, ha a tanuló kódját is tartalmazza a Győjtés tábla. 1NF TANULÓ Név Irszám Város Út Tanuló kód 1500 Kiss Antal 3100 Salgótarján Fı út 6 1600 Pál Éva 3104 Salgótarján Tó út 5 1700 Fehér 3100 Salgótarján Fa út 6 Zsolt 1800 Jó Lajos 3300 Eger Sós út 6 1900 Válóczi 3100 Salgótarján Petıfi út György 1

GYŐJTÉS Tanuló Győjtés Győjtött tárgy neve kód kód 1500 H1 Match-box 1500 H2 Pokémon kártya 1600 H1 Match-box

DB 150 200 300

1700 1800

H2 H1

Pokémon kártya 250 Match-box 120

1800 1800 1900

H2 H3 H4

Pokémon kártya 350 Bélyeg 200 Jelvény 250

A fenti két reláció legalább 1NF-ben van, eleget tesz a relációs adatmodell elsı hat feltételének. 1NF

TANULÓ(Tanuló kód) GYŐJTÉS(Tanuló kód) (1:N)

A Tanuló tábla a Győjtés táblának fölérendeltje, mivel a Tanuló tábla egy rekordjához több rekord is tartozhat a Győjtés táblában. Jelölése 1:N. A két táblát a tanuló kód mezı kapcsolja össze. Ahhoz, hogy eljussunk a 2. normálformához vizsgáljuk meg azt a táblát, amelyik összetett azonosítót tartalmaz. A GYŐJTÉS táblában a Tanuló_kód+Tárgy_kód összetett azonosító (funkcionálisan meghatározza a tábla többi tulajdonságát).

144


: www.pszfsalgo.hu, : [email protected], : 30/644-5111 Nézzük meg, hogy az összetett azonosítóból bármely tagot kiemelve fennáll-e a függés. Egyedül a DB az a tulajdonság, amely a Tanuló_kód+Tárgy_ kód-tól, mint összetett azonosítótól függ. A Győjtött tárgy neve csak az összetett azonosító egy részétıl, csak a Tárgy kód-tól függ. Ezért, hogy a részleges függéseket megszüntessük az alábbi 3 táblát hozzuk létre: 1NF-bıl 2NF-be DEKOMPOZÍCIÓ (szétbontás) útján jutunk. 2NF

DB

Tanuló kód

Tárgy kód

Győjtött tárgy neve

TANULÓ Tanuló kód 1500 1600 1700 1800 1900

Név Kiss Antal Pál Éva Fehér Zsolt Jó Lajos Válóczi György

Ir_szá m 3100 3104 3100 3300 3100

Város

Út

Salgótarján Salgótarján Salgótarján Eger Salgótarján

Fı út 6 Tó út 5 Fa út 6 Sós út 6 Petıfi út 1

KI MIT GYŐJT Tanuló kód 1500 1500 1600 1700 1800 1800 1800 1900 2NF

Adatbáziskezelés

GYŐJTÖTT TÁRGYAK

Tárgy kód DB H1 H2 H1 H2 H1 H2 H3 H4

150 200 300 250 120 350 200 250

Tárgy kód H1 H2 H3 H4

Győjtött tárgy neve Match-box Pokémon kártya Bélyeg Jelvény

TANULÓ(Tanuló kód) KI MIT GYŐJT(Tanuló kód) (1:N) GYŐJTÖTT TÁRGYAK(Tárgy kód) KI MIT GYŐJT(Tárgy kód) (1:N)

A Tanuló és a Győjtött tárgyak tábla is fölérendeltje a Ki mit győjt táblának. Nézzük meg a fenti három tábla között találunk-e olyat, amely tranzitív függést is tartalmaz. A Tanuló relációban az Ir_szám nem azonosító és meghatározza a Város tulajdonságot. A tranzitív függés megszüntetéséhez a kétszeresen meghatározott tulajdonságot (Város) új táblába visszük leíróként, azonosítóként pedig annak meghatározóját (Ir_szám). A Helység tábla fölérendeltje lesz a Tanuló táblának. A normalizálás eredménye az alábbi négy tábla: 3NF TANULÓ HELYSÉG Tanuló kód 1500 1600 1700 1800 1900

Név Kiss Antal Pál Éva Fehér Zsolt Jó Lajos Válóczi György


Ir_szám 3100 3104 3100 3300 3100

Út Fı út 6 Tó út 5 Fa út 6 Sós út 6 Petıfi út 1

Ir_szám 3100 3104 3300

Város Salgótarján Salgótarján Eger

145


Adatbáziskezelés KI MIT GYŐJT Tanuló kód 1500 1500 1600 1700 1800 1800 1800 1900

GYŐJTÖTT TÁRGYAK Tárgy kód DB H1 H2 H1 H2 H1 H2 H3 H4

Tárgy kód

150 200 300 250 120 350 200 250

H1 H2 H3 H4

Győjtött tárgy neve Match-box Pokémon kártya Bélyeg Jelvény

HELYSÉG(Ir_szám) TANULÓ(Ir_szám) (1:N) TANULÓ(Tanuló kód) KI MIT GYŐJT(Tanuló kód) (1:N) GYŐJTÖTT TÁRGYAK(Tárgy kód) KI MIT GYŐJT(Tárgy kód) (1:N)

3NF

A normalizálást az alábbi táblázattal is megadhatjuk: HELYSÉG Ir_szám Város Tanuló kód Név Út Tárgy kód Győjtött tárgy neve DB * → • ↔

* →

TANULÓ GYŐJTÖTT TÁRGYAK →

KI MIT GYŐJT

•

* → → * →

• →

Elsıdleges kulcs Leíró tulajdonság Részazonosító (Összetett azonosító része) Alternatív azonosító (pl.: Személyig.-szám ↔Taj szám Tanuló kód, ha a tanulónál több mezı is lehetne elsıdleges kulcs.)

4.11. Adatbázis tervezés Tervezzen adatbázist, amely egy könyvesbolt könyvbeszerzését tartja nyilván, egy könyvet csak egy kiadó adhat ki, és egy nap több könyvet is vásárolhat a bolt! Mezı nevek Adattípusok Mezıtulajdonságok irányítószám helység kiadó azonosítója kiadó neve kiadó címe könyv azonosítója szerzı neve könyv címe könyv ára vásárlás dátuma vásárolt db 146



Adatbáziskezelés

Adja meg a szükséges táblák szerkezetét az alábbi minta szerint: Tábla(Mezı1; Mezı2; ....) felsorolással, vagy az elızı példa (*,→ → stb. jelölésekkel)! Továbbá adja meg a kapcsolatokat az alábbi minta szerint: (Mezıx, Mezıy kapcsoló mezık) (kapcsolat típusa) Tábla1(Mezıx)------------------------------>Tábla2(Mezıy)

4.12. SQL utasítások 1. Egyszerő lekérdezések (Válogatás, feltételek, rendezés) Az SQL lekérdezés általános alakja: SELECT { [ ALL] | DISTINCT} mezı [, ...] FROM táblaneve [, ...] [WHERE Feltétel_összesítés_elıtt [ ...]] [GROUP BY mezı1 [,mezı2]] [HAVING Feltétel_összesítés_után..] [ORDER BY mezı1 [ASC|DESC] [,mezı2 [ASC|DESC] [,…]]]; Nézzük az egyszerő lekérdezéseket az alábbi Termék táblával. Termékek (Kategória, Termékkód, Terméknév, Egységár, MinKészlet, UtolsóMódosítás, Import) 2. Feltételek SELECT Termékek.Terméknév, Termékek.Egységár FROM Termékek (A WHERE feltételei a következık is lehetnek:) WHERE (Termékek.Terméknév ="kehely"); (Termékek.Terméknév Like "*pohár*"); (Termékek.Terméknév ="kehely" .OR. (Termékek.Terméknév ="vizes pohár"); (Termékek.Terméknév) Like "*pohár*") AND (Termékek.Import)=Yes); (Termékek.UtolsóMódosítás Between #1/1/95# And #12/31/96#); (Termékek.Egységár >200); 3. Kifejezések használata és elnevezése Listázzuk ki a termék egységárát és az emelt árat, amely a 30%-al emelt árat mutatja! SELECT Termékek.Terméknév, Termékek.Egységár AS JelenlegiÁr, [Termékek.Egységár]*1,3 AS EmeltÁr FROM Termékek; 4. Rendezés Listázzuk ki a 10 legdrágább termék nevét és egységárát! SELECT TOP 10 Termékek.Terméknév, Termékek.Egységár FROM Termékek ORDER BY Termékek.Egységár DESC; 5. Allekérdezés Listázzuk ki az átlagár feletti termékeket! SELECT Termékek.Terméknév, Termékek.Egységár FROM Termékek WHERE (Termékek.Egységár)>(SELECT Avg(Egységár) From Termékek); 6. Csoportosítás Kategóriánként listázzuk ki az átlagárat! SELECT Termékek.Kategória, Avg(Termékek.Egységár) AS Átlagár Fejezet: Bozó Mária f. adjunktus

147

Adatbáziskezelés


FROM Termékek GROUP BY Termékek.Kategória; Listázzuk ki azokat a kategóriákat, ahol a min.készlet meghaladja az 500-at. SELECT Termékek.Kategória, Sum(Termékek.MinKészlet) AS MinKészlet FROM Termékek GROUP BY Termékek.Kategória HAVING (MinKészlet)>500; 7. Feladatok több táblával Adott az alábbi két egyszerő tábla: Juttatások(JuttatásKód, SzemélyAzo, Összeg) Tanuló(SzemélyAzo, Név, Évfolyam, Csoport) Mivel egy tanuló többféle juttatásban is részesülhet (pl. 10 JuttatásKód sport eredményekért, 20 juttatáskód tanulmányi versenyekért), így a két tábla közötti kapcsolat 1:N. 1. tábla: Juttatások JuttatásKód 10 10 10 20 ……

SzemélyAzo 1 3 4 1

Összeg 15 000 25 000 25 000 20 000

2. tábla: Tanuló SzemélyAzo 1 2 3 4 …..

Név Kiss Éva Nagy Ágnes Pál József Vass Ádám

Évfolyam I. I. I. II.

Csoport 1 1 3 1

a.) Több tábla összekapcsolása Listázzuk ki a Tanuló azonosítóját, nevét, a kapott juttatás kódját és összegét! SELECT Tanuló. SzemélyAzo, Tanuló.Név, Juttatások.JuttatásKód, Juttatások.Összeg FROM Juttatások INNER JOIN Tanuló ON Juttatások. SzemélyAzo = Tanuló. SzemélyAzo; Listázzuk ki azokat a tanulókat, akik nem kaptak juttatást. SELECT Tanuló. SzemélyAzo, Tanuló. SzemélyAzo, Juttatások.JuttatásKód, Juttatások.Összeg FROM Juttatások LEFT JOIN Tanuló ON Juttatások. SzemélyAzo = Tanuló. SzemélyAzo WHERE Tanuló.Név = IS.NULL; b.) Összesítés Összesítsük tanulónként a juttatásokat, és listázzuk ki a legmagasabb összesített juttatással kezdve! SELECT Tanuló.Név, Sum(Juttatások.Összeg) AS [Sum:Összeg] FROM Tanuló INNER JOIN Juttatások ON Tanuló.SzemélyAzo = Juttatások.SzemélyAzo GROUP BY Tanuló.Név ORDER BY Sum(Juttatások.Összeg) DESC; 148



Adatbáziskezelés

c.) Feltétel összesítés után Listázzuk ki azokat az évfolyamokat, ahol a létszám meghaladja a 100 fıt! SELECT Tanuló.Évfolyam, Count(Tanuló.Évfolyam) AS [Count:Évfolyam] FROM Tanuló GROUP BY Tanuló.Évfolyam HAVING ((Count(Tanuló.Évfolyam))>100); d.) Feltétel összesítés elıtt és összesítés után Listázzuk ki azokat a csoportokat az I évfolyam tanulói közül, ahol a létszám meghaladja a 15 fıt! SELECT Tanuló.Csoport, Count(Tanuló.Évfolyam) AS [Count:Évfolyam] FROM Tanuló WHERE (Tanuló.Évfolyam)="I.") GROUP BY Tanuló.Csoport, Tanuló.Évfolyam HAVING ((Count(Tanuló.Évfolyam))>15); 8. Táblakészítı lekérdezés Készítsünk el egy 10Juttatás nevő lekérdezést, amely a 10 JuttatásKódú rekordokat tartalmazza! SELECT * INTO 10Juttatás FROM Juttatások WHERE (((Juttatások.JuttatásKód)=”10”)); 9. Példák tábla és index létrehozására Hozzuk létre a Tanuló táblát. CREATE TABLE Tanuló (Név TEXT(25), Évfolyam TEXT(6), Csoport BYTE, SzemélyAzo COUNTER CONSTRAINT SzAzo PRIMARY KEY); Hozzuk létre a Juttatások táblát. CREATE TABLE Juttatások (JuttatásKód TEXT(2), SzemélyAzo LONG INTEGER, Összeg CURRENCY; Hozzuk létre a Juttatások táblához az elsıdleges kulcsot. CREATE INDEX JuttatasSzAzo On Juttatások (JuttatásKód, SzemélyAzo) WITH PRIMARY; Indexeljük a Tanuló táblát évfolyam szerint. CREATE INDEX evfolyam ON Tanuló (Évfolyam); 10. Feladat táblák, indexek létrehozására és törlésére. 1. Hozza létre a Barátok táblát! A tábla a következı mezıket tartalmazza: Barát_Kódja-számláló, Vezetéknév szöveg-25, Keresztnév szöveg-25, Születési dátum-Dátum/Idı, Irányítószám szöveg-4. Legyen a Barát kódja az elsıdleges kulcs. Megoldás lépései: Az adatbázis ablakban válasszuk a LEKÉRDEZÉSEK/ÚJ/TERVezı nézet opciót. A táblák hozzáadása párbeszédablakot zárjuk be. (Nincs szüksége a lekérdezésnek egyetlen adatforrásra sem.) Válasszuk a lekérdezés SQL nézetét. A következı képet látjuk:

Töröljük a SELECT; utasítást és írjuk be az alábbiakat: Fejezet: Bozó Mária f. adjunktus

149

Adatbáziskezelés


CREATE TABLE Barátok ( Vezetéknév TEXT(25), Keresztnév TEXT(25), [Születési dátum] DATETIME, Irányítószám TEXT(4), Barát_Kódja COUNTER CONSTRAINT kod PRIMARY KEY ); Futtassuk le a lekérdezést. (A lekérdezést nem szükséges elmenteni.) Figyelem! A tábla és indexkészítı és törlı lekérdezések csak akkor adnak üzenetet a felhasználónak, ha már létezik, vagy nem létezik az adott névvel tábla vagy index. Ellenırizzük a Táblák objektumban az új táblát. Nézzük meg az index ablakot is. 2. Hozzunk létre egy Irsz nevő indexet az irányítószámhoz! CREATE INDEX Irsz ON Barátok (Irányítószám); Ez az index „Igen lehet azonos” tulajdonságot vesz fel. Az irányítószámra történı keresés ezáltal gyorsabb lesz. 3. Hozzunk létre egy Név_szül_adat nevő egyedi indexet! Nincs olyan barátunk, akinek a vezetékneve, keresztneve és a születési dátuma is megegyezne. CREATE UNIQUE INDEX Név_szül_adat ON Barátok (Vezetéknév, Keresztnév, [Születési dátum]); Név_szül_adat nevő index egyedi index lesz, vagyis a három mezı egyszerre nem lehet azonos. Az eddigi SQL utasítások futtatása után a Barátok tábla Index ablaka a következı indexneveket tartalmazza:

4. Töröljük az Irsz nevő indexet! DROP INDEX Irsz ON Barátok; 5. Töröljük azokat a barátokat, akiknek a vezetékneve „Kis” szóval kezdıdik. DELETE * (A DELETE utasítás teljes rekordot töröl.) FROM Barátok WHERE Vezetéknév LIKE 'Kis*'; 6. Töröljük a Barátok nevő táblát! DROP TABLE Barátok;

150


4. Adatbáziskezelés (MS Access)

Recommend Documents