Download
1 / 22
220 likes | 355 Views
Excel konzultáció 1. Gyakorlat Dr. Pauler Gá bor , egyetemi docens, ev. Adószám: 63673852-3-22 Számlaszám: 50400113-11065546 Telephely: 7666 Pogány, Széchenyi u. 1. Tel: 30/9015-488 E-mail: pauler @ t-online.hu. A gyakorlat tartalma. A relációs adatbázis kezelés alapfogalmai
E N D
Excel konzultáció 1. Gyakorlat Dr. Pauler Gábor, egyetemi docens, ev. Adószám: 63673852-3-22 Számlaszám: 50400113-11065546 Telephely: 7666 Pogány, Széchenyi u. 1. Tel: 30/9015-488 E-mail: pauler@t-online.hu
A gyakorlat tartalma A relációs adatbázis kezelés alapfogalmai • Különbség a papír-alapú rendszerektől • Adatbázis táblák, elsődleges- és idegen kulcsok • Relációs táblák és egyedkapcsolati diagramm • Normalizáció, adatbázis tervezés • Lekérdezések működése • Felhasználói felület, űrlapok Relációs adatbázisok tervezése • A manuális tervezés problémái • Az MS Access automatikus adatbázis tervező varázslója • A varázsló értékelése Relációs adatbázisok és felhasználói felületük megvalósítása MS Accessben • Tábladefiníciók és lekérdezések • Űrlapok és alűrlapok • Webes felület létrehozása • Vezérlőpanelek és alkalmazás futtatási beállítások 1. Házi feladat: Automatikus adatbázis tervezés
A Session1–ben láthattuk, hogy az Excel kapacitáskorlátai miatt nem alkalmas bonyolult struktúrájú, nagy tömegű tranzakció adatok kezelésére. Azonban, ha elé rakunk egy relációs adatbázis kezelő rendszert (Relational Database Management System, RDBMS), akkor ez megtöbbszörözi a rendelkezésre álló adatdkezelési kapacitást, és az Excel -ebből adatokat kinyerve - már igen jól használható és gyors felhasználói felületet képez. • Illő tehát megismerkednünk az RDBMS-ek alapfogalmaival és tervezésével: A relációs adatbázis kezelés alapfogalmai 1 Egy létező, papír-alapú adatkezelő rendszert nem könnyű hatékony adatbázis rendszerré alakítani: • A papír logikailag rendkívül rugalmas struktúrájú adattároló: számot, szöveget, képet tárol, akár laponként változó szerkezetben, viszont visszakeresési sebessége borzalmasan lassú és élőmunka-igényes, mert fizikai anyagmozgatást igényel. Ezért a papír-alapú rendszerek adatstruktúráit, űrlapjait úgy tervezik, hogy minden fontos egyedből beleszorítanak egy pár fontos tulajdonságot, és csak pár rekordnak adnak helyet. • (Erre látunk példát egy igen közkeletű űrlapon: az ÁFA-s számla a Számla, a Kibocsátó, a Vevő, egyedek tulajdonságait tudja tárolni, illetve a Megvásárolt tétel12 előfordulását. Mivel az eladó neve és a fizetve jelzés lemaradt róla tervezéskor, egyszerűen ráfirkantják). Ennek a rugalmasságnak azonban nagyon nagy ára van a lassúság mellett is: • A 13. megvásárolt tétel már nem fér rá, ezért vagy adatvesztés (Data loss) jön létre, • Vagy új számlát kell nyitni, ami szinte megduplázza a munkát és a szükséges tárolóhelyet: minden más adatot feleslegesen még egyszer be kell írni, ez a redundancia (Redundancy) • Vagy nem töltik ki a többi adatot, de ekkor nem egyértelmű a hivatkozás (Ambigous Reference), kié a számla
A relációs adatbázis kezelés alapfogalmai 2 • Egy adatbázis tábla (Database Table) önmagában rendkívül rugalmatlan tároló: soraiban rekordok (Record) találhatók, amelyek egy egyed (pl. Ügyfél) adott előfordulásait (pl. „Kovács Jánosné”, „Nagy Józsefné”) tárolják, és a merevlemezen sorfolytonosan egymás után állnak. A rekordok csak azonos mező (Field) szerkezetűek lehetnek, a mezők az egyed-előfordulások tulajdonságait tárolják. Ezért a rekordok tárolási hossza fix, így előre kiszámítható, hol kezdődik a merevlemezen az n-edik rekord, anélkül, hogy végig kellene olvasni az előzőket, emiatt itt a visszakeresés gyors lesz, de ennek ára van: • A valóságban előforduló adatszerkezetek NEM fix hosszúságúak (pl. egy anyának igen eltérő számú gyereke lehet – Kovács Józsefné: 0.6, Orsós Petronella Dzsenifer: 12). Ha fix számú gyerek adatait tudjuk csak tárolni, a legtöbbször tárolóhelyet pazarlunk (Redundancy), néhol pedig adatot vesztünk (Data Loss) • Nem lehet két rekord közé beszúrni egy harmadikat, csak a végéhez írni, • Ha kitörlünk egy közbülső rekordot, a helye ott marad kihasználatlanul. • Az E.F Codd által 1971-ben feltalált relációs adatbáziskezelés (Relational DataBase Management, RDBM, lásd: Codd) nem fix hosszúságú struktúrákat kezelni képes, nagy sebességű, redundancia-mentes, adatvesztés-mentes, egyértelmű hivatkozásokat tárol: • A nem fix hosszúságú gyakorlati adatstruktúrákat addig bontja szét (Decomposition), amíg fix hosszúságú rekord-struktúrákat tartalmazó táblákat nem kap • Minden táblába rekordjaikat egyedileg (Unique) azonosító elsődleges kulcs (Primary Key) mezőket vezet be. Utána a szétbontott egyedeket az eredeti struktúra adatainak megőrzése érdekében relációkkal (Relation) köti össze: 2 tábla rekordjai közti hivat-kozási kapcsolat, ahol az 1 oldali tábla adott rekordjának elsődleges kulcsára a több oldali táblába rakott idegen kulcsmező (Foreign Key) megegyező értéke hivatkozik: többgyerek: 1anyához
Kikölcsönző # Kód * Név * Cím Kölcsönzés # Kód * KönyvKód * Kölcskód Könyv # Kód * Író * Cím többKölcsönzés:1Kölcsönző 1Könyv:többKölcsönzés A relációs adatbázis kezelés alapfogalmai 3 • Két tábla rekordjai közti reláció számossága elvileg háromféle lehet:1:1 pl. Menyasszo-nyok:Vőlegények, 1:több pl. Anyák:Gyerekek, több:több pl. Könyvek:Kikölcsönzők. • De ezek ábrázolása megoldható kizárólag 1:több kapcsolatokkal: • Ha két tábla rekordjai 1:1 kapcsolatban vannak, a két tábla összevonható egy táblába • Ha a két tábla rekordjai több:több kapcsolatban vannak, ez ábrázolható, úgy hogy a két összekötött törzstábla (pl. Könyvek, Kikölcsönzők) elsődleges kulcsaira hivatkozó idegen kulcsokat tartalmazó relációs tábla (Relation Table) (pl. Kölcsönzések) köti őket össze 2db 1:több-höz kapcsolattal (1Könyv vagy 1Kölcsönző:többKölcsönzés): (A relációs táblának is lehet elsődleges kulcsa, de annak nincs szerepe a kapcsolat ábrázolásában. Arra szolgál, hogy a relációs táblára is hivatkozhassanak más táblák.) • Mivel egy nagy adatbázis sok táblája közti rengeteg relációt nem lehet áttekinthető módon ábrázolni a táblák tartalmán keresztül, szükség volt egy egyszerűsített szimbolikus ábrázolásmódra a táblaszerkezetek (egyedek) és relációik ábrázolására. • Ez az egyedkapcsolati diagramm (Entity Relationship Diagram, ERD), amelya relációs adatbázis tervezés fő eszköze, és a következő jelöléseket alkalmazza: az egyedek névvel ellátott, lekerekített sarkú dobozok, bennük az elsődleges kulcsok narancsszínűek, elő-jelölőjük #, a sima tulajdonságok lilák, az idegen kulcsok sötétzöldek. A kötelezően kitöl-tendők előjelölője *, az opcionálisaké o, az 1:több kapcsolatot csirkeláb (Crow Leg) jelöli:
Kpfizetési számla # Számlaszám * Kibocsátó neve * Kibocsátó címe * Kibocs. Adósz. * Vevő neve * Vevő címe * Számla kelte * Áfa-tartalom% * Végösszeg * Áthárított adó% o Eladó neve o Fizetve Tétel * ITJ-kategória * Termék neve * Menny. egység * Mennyiség * Egységár * Érték Számla # Számlaszám * Dátum * Végösszeg * Áthárít.adó% * Fizetve * Eladókód * Vásárlókód Cég # Adószám * Név * Házszám * Emelet * Lakásszám * Közterületnév * Közter.típus * Irányítószám Vásárló # Vásárlókód * Név * Házszám * Emelet * Lakásszám * Közterületnév * Közter.típus * Irányítószám ÁfaKategoria # Áfa-sáv * Áfa% IparTermJegyz # ITJ-kategória * Név * Áfa-sáv Termékfajta # Vonalkód * Név * Menny.egys. * Egységár * ITJ-kategória Tétel # Tételkód * Mennyiség * Érték * SzlaSzám * Vonalkód Eladó # Eladókód * Név * Adószám Irányszám # Irányítószám * Településnév A relációs adatbázis kezelés alapfogalmai 4 Az adatfolyam diagramm tevékenységeinél rög-zített valós adatstruktúrák (pl. ÁFA-s számla) nem alkalmasak az adatbázisba tételre, mert tele vannak belső 1:több kapcsolatokkal, amelyek redundanciát, adatvesztést, és kétértelmű hivatkozásokat okoznak, pl: • Egyszámláhoztöbbtételtartozik: együtt tá-rolva őket csak fix számú tételt tudunk kezelni • Egycégtöbbeladótalkalmaz: de melyik adta melyik számlát melyik vevőnek? • Egyvásárlótöbbszámlátkap: de miért kell mindig újra és újra beírni az összes adatát? • Egyterméktöbbtételbenszerepel: de miért kell mindig újra kitölteni az ÁFA-százalékát? Ezért a valós adatstruktúrákat át kell tervezni, normalizálni (Normalize) kell 5 lépésben: 1. Valós adatstruktúra szétszedése belső 1:több kapcsolatokat nem tartalmazó egyedekre 2. Elsődleges kulcs kijelölése minden egyedben 3. Nem a kulcstól függő tulajdonságok kidobása külön egyedbe (pl. az irányí- tószám meghatározza a tele- pülésnevet, így ez különválik) 4. Az egyedek összefüggő rend- szerbe kötése relációkkal 5. Több:Több kapcsolatok kivál- tása 1:több+relációs táblákkal A normalizáció eredményeként a valós adatszerkezet ember szá- mára áttekinthetetlenül sok egyed- re esik szét, de ez az adatbáziske- zelőt nem zavarja: helypazarlás nélkül, pontosan tárol adatokat és nagyon gyorsan keres vissza!
OrvosEset Eset OrvosKód EsetKód SzemIgSzám EsetKód GZ-II-2547 411 11 411 GF-I-3624 412 11 412 GZ-II-2547 413 13 413 • Egybelső orvoshoztöbbesettartozik és egyeset is többbelső orvoshoztartozik • Egybetegheztöbbesettartozik, de egyeset csak egybetegheztartozik Beteg # SzemIgSzám * Név * LeányNév * Kor * CsaládÁll * Foglalkozás * Lakcím * HozzátartNév * Apanév * ApaFoglalk BelsőOrvos # OrvosKód * Név * Beosztás * Tel * SzakTerül * Fizetés A relációs adatbázis kezelés alapfogalmai 5 húz • A normalizált szerkezetű relációs adatbázis az SQL nyelv segítségével kérdezhető le egyszerűen és nagy sebességgel: Pl. Lekérdezés: Kik a főorvos úr betegei? • A belépési pont (Entry Point) az ismert információ (pl. főorvos), a WHERE (szűrőfeltétel) részben foglal helyet. • A kilépési pont (Exit Point) az eredmény, ami kell (pl. beteg neve), a SELECT (kiválasztott eredmény) részben foglal helyet. • A lekérdezési útvonal (Query Path) a két pont közt halad minimális számú reláción keresz-tül, és a FROM részben definálódik: ezt nem kell bepötyögni, csak az utat egérrel kihúzni! SELECT Beteg.SzemIgSzam, Beteg.Név FROM ( ( ( Beteg INNER JOIN Eset ON Eset.SzemIgSzam= Beteg.SzemIgSzam) INNER JOIN OvosEset ON OrvosEset.EsetKod= Eset.EsetKod) INNER JOIN BelsoOrvos ON BelsoOrvos.OrvosKod = OrvosEset.OrvosKod) WHERE BelsoOrvos.Beosztas = „Főorvos”; húz Eset # EsetKód * SzemIgSzám OrvosEset * OrvosKód * EsetKód húz
Számla # Számlaszám * Dátum * Végösszeg * Áthárít.adó% * Fizetve * Eladókód * Vásárlókód Cég # Adószám * Név * Házszám * Emelet * Lakásszám * Közterületnév * Közter.típus * Irányítószám Vásárló # Vásárlókód * Név * Házszám * Emelet * Lakásszám * Közterületnév * Közter.típus * Irányítószám ÁfaKategoria # Áfa-sáv * Áfa% IparTermJegyz # ITJ-kategória * Név * Áfa-sáv Termékfajta # Vonalkód * Név * Menny.egys. * Egységár * ITJ-kategória Tétel # Tételkód * Mennyiség * Érték * SzlaSzám * Vonalkód Eladó # Eladókód * Név * Adószám Irányszám # Irányítószám * Településnév A relációs adatbázis kezelés alapfogalmai 6 • Az SQL kód arra is alkalmas, hogy a normalizáció által széttagolt sok-sok táblából felhozzon/ azokba visszavi-gyen adatokat egy elektronikus, akár interneten is elérhető űrlapokból (Forms) álló grafikus felhasználói felületről, amely hasonlóan nézhet ki az eredeti papír űrlapokhoz, hogy ismerős legyen a felhasználónak. A nagy különbség, hogy ezek korlátlanul bővíthetők (pl. 1 számlának tetszőleges számú tétele lehet), az adatrög-zítés nagy része menüből mehet, és nem pötyögéssel, és a mögöttük álló normalizált táblaszerkezet gyorsan, pontosan tárol és keres vissza! SELECT Szamlak.SzlaSzam, Szamlak.Datum, Szamlak.VegOsszeg, Szamlak.Fizetve, Szamlak.EladoKod, Szamlak.VasarloKod, Eladok.Nev, Eladok.AdoSzam, Cegek.Nev, Cegek.HazSzam, Cegek.Emelet, Cegek.LakasSzam, Cegek.KozTerNev, Cegek.IranySzam, IranySzamok.TelepulNev, Vasarlok.Nev, Vasarlok.HazSzam, Vasarlok.Emelet, Vasarlok.LakasSzam, Vasarlok.KozTerNev, Vasarlok.IranySzam, IranySzamok_1.TelepulNev FROM ((IranySzamok AS IranySzamok_1 INNER JOIN Vasarlok ON IranySzamok_1.IranySzam = Vasarlok.IranySzam) INNER JOIN(((IranySzamok INNER JOIN Cegek ON IranySzamok.IranySzam = Cegek.IranySzam) INNER JOIN Eladok ON Cegek.AdoSzam = Eladok.AdoSzam) INNER JOIN Szamlak ON Eladok.EladoKod = Szamlak.EladoKod) ON Vasarlok.VasarloKod = Szamlak.VasarloKod);
A gyakorlat tartalma A relációs adatbázis kezelés alapfogalmai • Különbség a papír-alapú rendszerektől • Adatbázis táblák, elsődleges- és idegen kulcsok • Relációs táblák és egyedkapcsolati diagramm • Normalizáció, adatbázis tervezés • Lekérdezések működése • Felhasználói felület, űrlapok Relációs adatbázisok tervezése • A manuális tervezés problémái • Az MS Access automatikus adatbázis tervező varázslója • A varázsló értékelése Relációs adatbázisok és felhasználói felületük megvalósítása MS Accessben • Tábladefiníciók és lekérdezések • Űrlapok és alűrlapok • Webes felület létrehozása • Vezérlőpanelek és alkalmazás futtatási beállítások 1. Házi feladat: Automatikus adatbázis tervezés
Relációs adatbázisok tervezése • A fentiekből láthattuk, hogy relációs adatbázis rendszer – még a mezítlábas Accessben létrehozva is – nagyságrendekkel nagyobb adatkezelési kapacitással rendelkezik, mint az Excel. Akkor mégis miért van az, hogy szerencsétlen Excelt próbálják tranzakció adatok kezelésére használni – amire nyilván alkalmatlan – és ezzel gyakran tízmilliókat eresztenek a szélbe? • A szűk keresztmetszet a relációs adatbázisok tervezésénél van: a hatékony adatbázisok tervezése nehezen algoritmizálható folyamat. A normalizáció 5-6 lépése csak a vázát adja meg, de rengeteg intuitív döntésre van benne szükség, amelyhez jelentős absztrakciós készségre, gyakorlásra és tapasztalatokra van szükség. • Ráadásul, teljesen másképp kell gondolkodni, mint a régóta jól megszokott papír-alapú adatkezelő rendszereknél: ott az az elv, hogy mindenféle adatból szorítsak bele egy kicsit egy űrlapba, mert nehéz más aktát visszakeresni. Ha elfogy a hely, majd a margóra írunk. Az adatbázis tervezése során viszont addig darabolom a gyakorlati adatstruktúrát táblákra, amíg abszolút fix helyen tárolható rekordokból állnak, mert nincs margó, ahova beszúrhatnék valamit! A visszakeresési idő viszont nem téma, mert brutálisan gyorsabb, mint papíron: a szerver akkor is miliszekundumok alatt talál meg egy táblát, ha 2 van belőlük vagy 2000 • Mindezek miatt sajnos a BSc képzésről kikerülő informatikusoknak jó, ha a 2-3%-a tud elfogadható szinten adatbázist tervezni (itt nem arra gondolunk, hogy nagy nehezen össze tud kapcsolni 2 táblát a copy-paste szakdolijában!!!) Azonban néhány rendszer (pl. MS Access) tartalmaz automatikus adatbázis tervezési varázslót, adatbázis tervezésben kevésbé jártas felhasználók (pl. üzleti elemző) részére. Automatizált tervezésre akkor van esély, ha: • A gyakorlati adatstruktúrákhoz jelentős mennyiségű és jó minőségű mintaadatunk van: • Nincs bennük sok hiányzó érték • Nincs bennük sok elgépelés, vagy korruptálódott adat • A különböző adatstruktúrák mezői a nevük és a tipusuk hasonlósága alapján megfeletethetők (pl. ami egyik adatforrásban RendszerDatum, az a másik adatforrásban is megvan RDatum vagy R_Date néven, dátumnak kinéző tipussal) • Amelyeket már elektronikus formában tárolnak, vagy a megrendelő erőforrásokat biztosít az adatok normalizálatlan, széttagolt adatbázis táblákban történő rögzítésére (a legtöbbször nem adnak pénzt egy pocsék papír-alapú rendszer adatrögzítésére)
Az MS Access adatbázis-tervező varázslója 1 katt • A Stores.xls fájlban egy NY, PA, OH álla-mokban tevékenykedő áruházlánz 160 üzletének natural joinolt adatai találhatók. Első lépésként, importáljuk ezt Accessbe (a végeredményt lásd: Stores.mdb): • Fájl|Külső adatok|Import menüvel indítsuk el az importáló varázslót! • Válasszuk ki az Excel fájlt, és jelöljük be, hogy oszlopfejléc az első sorban van • Tovább gombbal lépve, bejelöljük, hogy új táblát szeretnénk • Majd felülírhatjuk a mezőkneveit, ha kell • Majd magunk választunk elsődleges kulcsot (StoreID) • Majd megadjuk az importált tábla nevét: Stores • Majd a Befejez gombbal zárjuk a varázslót • Erre a tábla megjelenik a Táblák listában • Duplán kattintva rajta, megnézhetjük a tartalmát katt katt kat-kat katt katt katt katt
katt Access adatbázis-tervező varázslója 2 katt katt katt A Stores tábla kijelölése után Eszközök| Analizálás|Tábla menüvel indíthatjuk a Táblaanalizáló varázslót: • Ez a gyakorisági táblás módszerrel meg-próbálja automatikusan dekompozícionál-ni a táblát, ha bejelöljük varázsló dönt-öt • A széttagolt táblákat kijelölve a gomb-ra kattintva nevezhetjük el őket (az elne-vezésben segít, hogy a varázsló mit jelölt elsődleges kulcsnak a táblában, és ne adjuk meg már létező tábla nevét!) • Ha úgy gondoljuk, valamely mezőt rossz táblába tette, egérrel áthúzhatjuk a táblák közt, illetve több mezőt egyszerre kijelöl-ve Shift+katt-tal, egy üres helyre húzva őket, új táblát készít belőlük • A kézzel kibontott táblában gombbal jelölhetünk ki elsődleges kulcsot, ha nem lenne alkalmas mező, gombbal adha-tunk hozzá mesterséges kulcsot Az eredményben látható, hogy a varázsló a főbb összefüggéseket jól bontja ki: • 1városhoz(City) többüzlet(Store) tartozik • 1 régióhoz(Region) több város(City) • 1 megyéhez(County) több város(City) Azonban a bontás nem tökéletes: • Nem veszi észre az állam(State):régió (Region)=1:több kapcsolatot, mert van olyan állam (OH), amiben csak 1 régió van, és ez megzavarja • Nem bontja ki a telefonszámkörzet(Area): város(City) = több:több kapcsolatot A problémás mezőket mindig a tábla el- sődleges kulcsa elé rakja! katt katt katt katt
Access adatbázis-tervező varázslója 3 katt Ezenkívül nem ír fel olyan rendundáns relációkat, amelyek a lekérdezések gyorsításához kellenének: • 1államhoz(State) többváros(City) tartozik • 1államhoz(State) többmegye(County) • 1államhoz(State) többszámkörzet(Area) A varázsló viszont detektálja, hogy a város: telefonszámkörzet kapcsolat kevés kivé-teltől eltekintve majdnem több:1 kapcsolat: 3 olyan City van csak, ahol több Area kód is jelen van. Ha ezek eltűnnének, az el-sődleges kulcs (City) egyértelműen meg-határozná az Area-t, így az a City tábla rendes mezője lehet (ld. 3.Normálforma) • A varázsló feltételezi, hogy a kilógó esetek elgépelés miatt vannak, és kinyit egy ablakot, amiben lehetőséget ad arra, hogy 1City számára kiválasszunk a több előforduló közül 1 érvényes Area kódot, visszanyomva a City:Area kapcsolatot több:több kapcsolatból több:1 kapcsolatba • Az adattisztítási műveletek végeztével a varázsló felkínálja, hogy készítsen-e olyan lekérdezést, amely egy nézettáblában összerakja az eredeti natural joinolt táblát a szétbontott táblákból, és azt átnevezve más névre,beáll a helyére az adatbázisban • A varázslót befejez gombbal bezárva bele-írja az adatbázisba a szétbontott táblákat és a köztük lévő relációkat, meghagyva az eredeti táblát is • Eszközök|Kapcsolatok menüben megte-kinthető a szétbontott szerkezet relációs diagrammja, és kézzel tovább finomítható! katt katt katt katt katt katt
Access adatbázis-tervező varázslója 4 katt katt húz gyorsító redundáns relációk létrehozását, mert ehhez be kellene gyűjteni adatokat a felhasználótól arról, hogy mely mezőből (belépési pont) mely mezőt (kilépési pont) milyen gyakorisággal akar lekérdezni. • Abszolút használhatatlan, ha nincsen elégséges mennyiségű mintaadat az elemzéshez, vagyis az estek 95%-ában, például, amikor klasszikus papír alapú rendszerre fejlesztünk rá. Igazából olyan, már régóta működő adatbázisokat lehet vele kijavítani, amiben kisebb normalizá-ciós hibák vannak. • Általában nincs elég adat,ha üzleti mé-retű 120-140 táblás rendszert akarunk normalizálni vele, mert n tábla szétbontá-sához átlagosan 16/6×n×(n-1) reprezen-tatív rekord kell az alábbi tábla alapján: 100 táblához 26400 reprezentatív rekord kell! Megállapítjuk,hogy a varázsló inkább tanulási segédeszköz, de annak sem tökéletes Ha a varázsló elégséges adat híján nem bontott ki egy relációt (pl. állam(state): régió(Region) = 1:több), a szétbontott táblát (Regions1) kijelölve, a varázslót újraindítva, azt kézzel tovább bonthatjuk: • Ilyenkor a táblából kibontandó mezőket Shift+katt-al kijelöljük, és egérrel kihúz-zuk egy üres helyre, ahol új táblát alkot-nak, amit gombbal elnevezhetünk, gombbal pedig elsődleges kulcsot jelölhetünk ki benne. Az MS Access adatbázis-tervező varázslójának előnyeit és hátrányait a következőkben foglalhatjuk össze: • Képes automatikus dekompozíciót végrehajtani, ha a natural joinolt táblában elégséges mennyiségű és reprezentati-vitású (Representativeness) adatot kap: vagyis az adatok minden, a valóságban gyakran előforduló kombinációjából kap a gyakoriságukkal arányosan rekordokat • Automatikusan detektálja az elsődle-ges kulcsnak alkalmas mezőket • Automatikusan detektálja, ha egy tábla egy mezőjét nem határozza meg egyértelműen a tábla elsődleges kulcsa (3. Normálforma megsértése), és ilyenkor a kilógó adatok módosítását javasolja • csak 1:több, független:függő kapcsola-tokat tud felismerni, igy messze nem használja ki a gyakorisági táblás elemzés összes lehetőséget • n szétbontott egyedet n-1 relációval köt össze, nem kínálja fel a lekérdezéseket
A gyakorlat tartalma A relációs adatbázis kezelés alapfogalmai • Különbség a papír-alapú rendszerektől • Adatbázis táblák, elsődleges- és idegen kulcsok • Relációs táblák és egyedkapcsolati diagramm • Normalizáció, adatbázis tervezés • Lekérdezések működése • Felhasználói felület, űrlapok Relációs adatbázisok tervezése • A manuális tervezés problémái • Az MS Access automatikus adatbázis tervező varázslója • A varázsló értékelése Relációs adatbázisok és felhasználói felületük megvalósítása MS Accessben • Tábladefiníciók és lekérdezések • Űrlapok és alűrlapok • Webes felület létrehozása • Vezérlőpanelek és alkalmazás futtatási beállítások 1. Házi feladat: Automatikus adatbázis tervezés
Alűrlap Csatolt Bázis Relációs adatbázisok és felhasználói felületük megvalósítása MS Accessben 1 Egy nőgyógyászati kérdőíves felmérés adatait tároló adatbázishoz (lásd: L.mdb) már elvégeztük az MS Access varázslójával a táblaszer-kezetek megtervezését: • 1leánykatöbbször lehetválaszadó, de 1válaszadáshoz 1leánykatartozik • 1válaszadóhoz többanyaság tartozhat, de 1anyaság csak 1válaszadóhoz tartozhat A felhasználó a következő felhasználói felületen keresztül szeretné használni az adatbázist: • A létrehozandó 1 űrlap alapvetően 1válaszadó adatait ábrázolja majd, ezért ezt bázisegyednek (Base Entity) nevezzük • Az ezzel több:1 kapcsolatban álló leánykacsatolt egyed (Join Entity) lesz: adatai a bázissal azonos űrlapon jelenhetnek meg, mert 1válaszadáshoz1leánykatartozik • A bázishoz1:több kapcsolódó anya alűrlap egyed (Subform Entity) lesz, és adatai a bázis munkaképernyőbe ágyazott, gördíthető képernyőn, alűrlapon (Subform) jelennek meg, mert 1válaszadóhoz egyszerre többanyaság tartozhat, listát kellene róluk mutatni
Csatolt Alűrlap Bázis Relációs adatbázisok és felhasználói felületük megvalósítása MS Accessben 2 katt katt katt 1.Lépés: az egyedek alapján táblastruktú-rákat definiálunk a Táblák|Tábla létre-hozás szerkesztő nézetben menüvel, Fájl|Mentés menünél beírjuk a nevüket 2.Lépés: Számláló tipusú, Egyednév+ID ne-vű mezők hozzáadása a táblákhoz, gombbal elsődleges kulcsnak jelöljük 3.Lépés:Hosszú egész tipusú idegen kulcsok hozzáadása a relációk( ) több oldalán a táblákhoz, nevük megegyezik a hivat-kozott elsődleges kulcséval,Megjelené-sük legyen Kombinált lista, melyek sorforrásai a relációk 1 oldali Táblái,kö-tött oszlopuk az 1-es(elsődleges kulcs). Hasonlóképp formázzuk azon mezőket, amik értéklista tipusúak (pl.”Jó/Rossz”) 4.Lépés:Eszközök|Kapcsolatok( )menüben gombbal hozzáadunk a diagrammhoz minden táblát, és a megfelelő elsődle-ges és idegen kulcsokat összehúzzuk egérrel, majd a relációs vonalakon dup-lán kattintva beállítjuk a Hivatkozási in-tegritást és az Illesztés tipus gombnál az 1.pontot(csak az egyezők csatolása) 5.Lépés:Mivel 1 űrlapnak csak 1 adatforrása lehet, viszont a Bázis és Csatolt táblák tartalmát 1 űrlapon kellene mutatni, Le-kérdezések|Új lekérdezés tervezőben menüvel készítünk adatforrás lekérde-zést: gombbal hozzáadjuk ezen táb-lákat, és mezőiket egérrel behúzgáljuk a Megjelenítésbe.Fájl|Mentés menüvel mentjük a lekérdezés kódját egy néven katt katt katt katt katt katt katt katt katt húz katt kat- kat katt katt húz katt katt katt
Megvalósítás Accessben 3 katt katt katt katt katt katt katt katt 6.Lépés:a kérdőív főűrlap létrehozására indít-suk el az Űrlapok|Űrlap létrehozása varázslóval menüvel az űrlapvarázslót: • Válasszuk ki az adatforrás lekérdezést, és >> gombbal jelöljük ki összes mezőjét • Ezután válasszuk ki a lekérdezésben szereplő bázis-táblát (pl.Válaszadók) • Az űrlap szerkezete legyen Oszlopos • Stílus legyen Szabványos,címe Főűrlap 7.Lépés:Befejez gombra a varázsló lefut, és Tervező nézetre( ) kapcsolva szer-keszthetjük az űrlapot: húzzuk lejjeb a láblécet, hogy az oldaltesten legyen hely az alűrlapnak, és igazítsuk el egérhúzás-sal a főűrlap adatbeviteli kontroljait 8.Lépés:Nézet|Eszköztárak|Vezérlők eszköz-sorról gombbal adjunk hozzá alűrlapot, egérrel kihúzva helyét az üres területen. Erre elindul a segédűrlap-varázsló: • Meglévő táblát akarunk adatforrásául • Jelöljük ki az alűrlapos táblát (pl.Anyák) és >> gombbal az összes mezőjét • Listából választunk felkínált kap- csolómezőt (pl.VálaszadóID) • Elnevezzük Segédűrlapnak • Befejez gombra lefut a varázs- ló és az alűrlap kontroljai szer- keszthetők egérhúzással 9.Lépés:Az alűrlapot kijelöljük a bal felső űrlap-kijelölő gombbal( ) és jobbkattra előug-ró menüben a Tulajdonságokat kinyitjuk Itt a Formátum fülön Folytonos nézetet jelölünk be,hogy több rekordot mutasson A főűrlap hasonló menüjében az Adat fülön Adatbevitel módot állítunk, a Törlést tiltjuk katt katt katt katt katt katt húz húz katt húz katt katt húz húz katt húz katt katt katt katt katt katt katt katt katt
Megvalósítás Accessben 4 katt 10.Lépés:Űrlap nézetre( ) váltással az űrlap fut-tatható. A főűrlapon és az alűrlapon kitöltött adatokat visszaviszi az adatforrás táblákba: • A főűrlap adatforrását képező FőűrlapBá-zisLekérdezés ilyenkor fordítva működik, és az alapjául szolgáló Válaszadók és Le-ánykák táblát tölti a főűrlap adataival • Az alűrlap közvetlenül az Anyák táblát tölti • A táblák elsődleges kulcsai automatikusan számozódnak az új rekordok létrejöttével, mert számláló típusúak • Azt, hogy a felhasználó mely táblák mely rekordjait töltötte egyszerre az űrlapon, az elsődleges kulcsokra 1:több relációkon( ) keresztül hivatkozó, automatikusan beíró-dó idegen kulcsok tárolják 11.Lépés:A működőképes és letesztelt űrlapot Fájl|Mentés másként|Adatelérési lap menüvel menthetjük Html-formátumban, (ld.: L.htm), ami ezek után az adatbázis *.mdb fájljával (ld.: L.mdb) együtt feltölt-hető a lekérdezésekhez rendelkezésre álló webszerverre. Sajnos, az Access automatikus weblap-generátora alűrlap- okat nem képes kezelni, ezeket kihagyja Ezért kifinomoltabb megoldásokhoz használjunk MySql +PHP-t, vagy MS SQL szerver és .Net környezet kom -binációját! Válaszadók Leánykák katt katt Anyák
Megvalósítás Accessben 5 katt katt katt 12.Lépés:Ha a kérdőív olyan sok kérdést tartalmaz, hogy egy Access űrlap nem tudja kezelni a hozzájuk tartozó adat-beviteli kontrollokat (max. 255 mehet rá), akkor a kérdőívet oldalanként bont-suk több adatbeviteli űrlapra, majd az Eszközök|Adatbázis segédeszközök| Kapcsolótábla-kezelő menüvel hoz-zunk létre föléjük vezérlőpanel-űrlapot: • Válaszoljunk Igen-t az új létrehozására • Nevezzük el FőKapcsolótáblának és Szerkesztés gombbal szerkesszük • A listához Új gombbal adjunk új elemet • Nevezzük el a vezérlőt (pl.Főűrlap) • A Parancslistából válasszuk ki, mit csináljon (pl.Űrlap Hozzáadás módban: a felhasználó feltölthet új egy új üres űrlapot, de nem néze-getheti a régieket) • Adjuk meg, mely űrlapot indítja a vezérlő (pl.Főűrlap) • Ügyeljünk rá, hogy legyen egy Ki-lép az alkalmazásból vezérlő is! • Bezár gombra létrejön a vezérlőpanel 13.Lépés:Az alkalmazás indítási beállításai. Hogy az adatrögzítő kézzel ne tudjon belepiszkálni az Access adatbázisba, az Eszközök|Indít menüben állítsuk: • Az alkalmazás címét: Szüléstörténet • Az indító ikonját *.ico formájú fájlként • Melyik űrlappal induljon: Vezérlőpanel • Adatbázis ablak megjelenjen: Nem • Állapotsor megjelenjen: Nem • Access menük megjelenjenek: Nem Úgy fog kinézni, mintha önálló program lenne katt katt katt katt katt katt katt katt katt katt katt katt katt katt katt
A gyakorlat tartalma A relációs adatbázis kezelés alapfogalmai • Különbség a papír-alapú rendszerektől • Adatbázis táblák, elsődleges- és idegen kulcsok • Relációs táblák és egyedkapcsolati diagramm • Normalizáció, adatbázis tervezés • Lekérdezések működése • Felhasználói felület, űrlapok Relációs adatbázisok tervezése • A manuális tervezés problémái • Az MS Access automatikus adatbázis tervező varázslója • A varázsló értékelése Relációs adatbázisok és felhasználói felületük megvalósítása MS Accessben • Tábladefiníciók és lekérdezések • Űrlapok és alűrlapok • Webes felület létrehozása • Vezérlőpanelek és alkalmazás futtatási beállítások 1. Házi feladat: Automatikus adatbázis tervezés
1. Házi feladat: Automatikus adatbázis tervezés Janice Schoemaker, a Pops Friendly Markets Inc. (157 szupermarketből álló lánc az USA északkeleti érészén) marketing menedzsere nagy bajban van: a 2003 2. negyedéves üzlet-értékelési adatokat egyetlen táblázatba ömlesztve kapta meg a meglehetősen gondatlanul és nem hozzáértő módon dolgozó Computer Associates Inc. informatikai alvállalkozótól (lásd: StoreMassData.mdb). • Ezért megbízta a nemzetközi ösztöndíj-csereprogramban épp Buffaloban tartózkodó, és a Pops-nál nyári gyakorlatát töltő Butler Jánost, aki a PTE-PMMK-n végzett 2002-ben, hogy próbálja kihalászni a következő adatokat, és egy „rendesen működő” adatbázisba tenni (Janice nem informatikai szakember, és csak nagyjából tudta körülírni a feladatot): • A táblában valamilyen POS kezdetű mezőkben az AC Nielsen Inc. nevű független piackutató cégtől az üzletekre vonatkozó adatok vannak • Valamilye Store kezdetű mezőkben a saját üzletek pénzügyi jelentéseinek adatai • Nem világos a kapcsolat a két mezőcsoport közt • Próbálja meg az MS Access táblaanalizáló varázslója segítségével, illetve saját szöveges elemzése alapján normalizált adatbázis szerkezetbe szétszedni a StoreMassData.mdb tábla adatait! (3p) • A normalizált adatbázishoz hozzon létre űrlapokból-alűrlapokból álló felhasználói felületet! (2p) A megoldás: 1-1Megoldas.mdb MassDataTable
