Adatbázis-tervezés konzultáció 9. Előadás Dr. Pauler Gá bor , egyetemi docens, ev.

1. Adatbázis-tervezés konzultáció 9. Előadás 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

2. Az előadás tartalma A Belső logisztika dimenzió szerkezete az MDH-ban • Tárolórendszerek térszerezetének modellezése • Algoritmusok, protokollok modellezése • Alkalmazás-specifikus eszközállomány modellezése • Könyvelési adatok modellezése • Értékesítési adatok modellezése Szakirodalom

3. Belső logisztika dimenzió: A tárolórendszerek térszerkezete 1 A Belső logisztika (internal Logistics) dimenzió a célszervezeten belüli anyag-/ információ-/ pénzáramlás modellezésével foglalkozik. Szerkezete erősen függ attól, hogy a célszervezet milyen méretű és milyen iparágban, milyen technológiával tevékenykedik, ezért az eddigiektől eltérően nem tudunk általánosan érvényes receptet adni a megtervezésére. Ehelyett példákat és irányelveket mutatunk be rá a tananyag hátralevő részében. Első példánk a gyógyszerkutató labor adatbázistervéből származik (lásd: ResearchLabERD.vsd ) és a raktárak tárolóeszközök térszerkezetét tárgyalja: • Első ránézésre egyszerű fix szintszámú hierarchia, amely 1:több kapcsolatokkal összekapcsolódó szintleíró egyedekből áll: • telephely (Facility), • épület (Building), • szoba (Room), • tárolóeszköz (Storage), • konténer (Container) • Azonban az egyes szinteknek a gyógyszerlabornál rengeteg speciális tulajdonsága, paramétere lehet: • Az épületekhez és a szobákhoz különböző tisztasági szabványok, illetve biológiai izolációs rendszerek tartozhatnak (gondoljunk súlyosan fertőző vírusok tárolására és a rajtuk végzett munkára: tippeljük meg, hány méterre van tőlünk most a legközelebbi Ebola retrovírus törzs – meglepődnénk, nem Afrikában!) • A tárolóeszközökhöz hasonlóképpen bonyolult leírás tartozhat, hogy a tárolt anyagoknak milyen fizikai környezetet kell biztosítaniuk (pl. vészhelyzeti segédáramforrásról is üzemeltethető -80C°-os hűtők) • Éppen ezért minden szintet Cikként (PartID) vagy Sorozatként (LotID) is definiálunk a Session8-ban tárgyaltak alapján (lásd: MintaERD2.vsd), hogy tetszőleges számú egyedi leíró paramétert tudjunk hozzájuk rendelni

4. Belső logisztika dimenzió: A tárolórendszerek térszerkezete 2 • Mivel minden szint megjelenik Cikként vagy Sorozatként, az összeköttetései-ket ábrázolhatnank a CikkKapcs (PartDependency) vagy Sorozat-Kapcs (LotDependency) egyedekben • Azonban a szintek összeköttetései olyan egyszerűek (fix szintszámú hierarchia), hogy nem érdemes „ágyúval lőni verébre”, és a leíró egyedeiket inkább direkt kötjük össze • Az egyetlen bonyolultabb dolog, hogy a konténer(Container) egyednek van egy ön-magára mutató 1:több kap-csolata a FoKontenerID (MainCon-tainerID) idegen kulcson keresztül, amivel meg tudja jeleníteni a tároló-dobozok „egymásba dobozolódását” egy nem fix szintszmú hierarchiában • A konténerek ugyanis még egy munkafolyamaton belül is igen változatos méretűek lehetnek: pl. a gyógyszerlabornál konténernek minősül egy 0.125m3-es hűtődoboz, a benne található kémcső tartó reke-szek, és az ezekben található, min-tákat tároló, ún. Eppendorf-csövek (ilyet mindenki láthat vérvételnél, ha nem ájul el közben). • A KontenerTipus (ContainerType) egyed írja le, hogy az adott típusú konténer milyen, és hányadik szinten ágyazódhat be a konténerek hierar-chiájába: KontenerRang (Container-Rank)

5. Lépés1 Lépés2 Lépés3 Lép2 Lép1 IF: ENDIF: ELSE: Ciklusmag FOR: ENDFOR: Belső logisztika dimenzió: Algoritmusok, protokollok modellezése Bármely termelő tevékenység irányításakor bonyolult algoritmusokat kell nyilvántartani, amelyek több hierarchikus szinten ágyazódhatnak egymásba: • Üzleti Folyamat Diagramm (Business Process Diagramm, BPD): a szervezet működési struktúrájának áttekintő vázlata, egy tevékenység az egy munkaképernyőn elférő adatok feldolgozásához szükséges algoritmus-részletet szimbolizál • Működési Szabályzat (Standard Operation Protocol, SOP): a fenti feltolgozás további részletezése kb. a felhasznált programmodulok, technológiai leírások szintjéig • Technológiai leírások (Patent, License, KnowHow, Protocol) illetve programkódok: elemi műveleti szintű algoritmusok A legkézenfekvőbb eszközök bonyolult algoritmusok pontos leírására a 4. generációs, struktúrált, objektum orientált programnyelvek (C#, VB, Delphi, Java), illetve tárolt eljárások (MS Transact SQL, Oracle PL/SQL) lennének, azonban az emberek (sőt az informatikusok!!!) elenyészően kicsi töredéke képes ezeket megfelelően használni, és megmaradnak pontatlan élő nyelvi szöveges algoritmus leírásoknál (pl. „apuci ugorj be a boltba, az óvodába a gyerekért, és ha kész hozd el a ruhámat a tisztítóból, még én végzek a műkörmösnél és kozmetikusnál…”) • Így pl. orvosi kutatási/kezelési protokollok leírásakor az orvosok informatikai tudáshiánya miatt elesünk az adatbáziskezelőkbe épített leghatékonyabb algoritmus-ábrázolási eszközök haszná-latától. Ekkor is segít azonban a struktúrált programtervezési módszerként ismert Jackson-elv(Jackson-principle,ld. Session1), mely szerint bármely algo-ritmus leírható 3 építőelem: Lépés-szekvenciák (Step), Döntések-szelekciók (Decision), Ciklusok (Iteration) hierarchikus egymásba ágyazásával, melyeket 3 leíró egyeddel ábrázolunk • Mivel kapcsolatrendszerük, egymásba épülésük elég bonyolult lehet, mindhármat Cikként (PartID)/ Sorozatként (LotID) definiál-juk, és a Session8-ban tárgyalt CikkKapcs (PartDependency), SorozatKapcs (LotDependency) egyedekkel oldjuk meg!

6. Belső logisztika dimenzió: Algoritmusok, protokollok modellezése 2 • A fejlett Termék dimenzió használata az algoritmusok ábrázolásakor ott is segít, hogy ha a fenti 3 építőelemből készült hierarchiákat struktúrált módon Eljárá-sokba (Protocol), azokat pedig Főprog-ramba (MasterSchedule) rendezzük, ezekhez változatos számú és adattipusú bemenő/kimenő, globális/lokális para-méter tartozhat (mint bármely program-nyelvben…) de ez már nem jelent prob-lémát, mert ezt a Cikk/Sorozat egyedek- nél már megoldottuk, itt jön vissza a Termék dimenzióba fektetett munka • Felvetődik a kérdés, miért rakunk min-denhova duplán Cikkre és Sorozatra történő hivatkozást is? Amíg egy algorit-mus elméleti terv, technológiai leírás, de nem valósul meg, addig csak a CikkID van kitöltve. Amikor ezen leírás alapján elindul egy sorozat termelése, és az elméleti folyamat többször egymás után megismétlődik a valóságban, akkor az őt leíró rekordok több példányban lemá-solódnak, azonos CikkID, de eltérő SorozatID idegen kulcsokkal, így szükség esetén a tényleges gyártási folyamat minden elemi szintű lépését is ábrázolni tudjuk, ugyanabban az adat-bázis struktúrában! Ezt a dupla kulcsolá-sos trükköt Objektum-előfordulás modellnek (Object-Instance Model) nevezzük: minden legyártott fizikai termék egy elméleti terv objektum egy előfordulása!

7. Az előadás tartalma A Belső logisztika dimenzió szerkezete az MDH-ban • Tárolórendszerek térszerezetének modellezése • Algoritmusok, protokollok modellezése • Alkalmazás-specifikus eszközállomány modellezése • Könyvelési adatok modellezése • Értékesítési adatok modellezése Szakirodalom

8. Alkalmazás-specifikus eszközállomány modellezése 1 Bármely termelő tevékenység algoritmusa során eszközöket használ, ezek azonban annyira alkalmazásfüggőek, hogy nehéz általános sémát adni a modellezésükre, az alábbi szempontokon túl: • Az eszközök típusleíró (EquipmentType) egyedeiben mindig van CikkID (PartID) hivatkozás, hogy egyedi leíró paramétereket tudjunk róluk tárolni • A konkrét fizikai eszközöket (Equipment) leíró egyedekben mindig van SorozatID (LotID) hivatkozás, hogy adott sorozatelem esetleges egyedi paramétereit is ábrázolni tudjuk • Ha az eszközök összekapcsolódása viszonylag egyszerű, fix szerkezetű hierarchiát alkot, akkor az egyedeiket közvetlenül kötjük relációkkal, ez minden-képp gyorsabb és kevesebb helyez foglal. Ha összeépülési struktúrájuk nem fix és bonyo-lult, a CikkKapcs (PartDependency), Soro-zatKapcs (LotDependency) egyedekkel ábrázoljuk, ami több helyet fogyaszt és lassú Példaként a flow-citometriás (Flow-Citometry) mérőműszerekkel ellátott gyógyszerkutató labor eszközeinek modellezését mutatjuk be: • A flow-citométer csúcstechnológiás lézeres mérőműszer, amely adott hullámhosszokon (Emission Wavelength)fényvisszaverő anya-gokkal (Fluorescent) megjelölt molekulák (Labeled Material) jelenlétét képes kimutatni sejtek (Cell), nanométeres átmérőjű üveg mikrogyöngyök (MicroBead) felszínén a visszavert lézersugár képének elemzésével

9. Alkalmazás-specifikus eszközállomány modellezése 2 • A sejtek vagy adott típusú fluoreszcenssel jelölt gyöngyök, mint hordózóanyag felszínére több rétegből (Layer) álló struktúrában (Layer-Structure) rögzítőanyagok (Fixer) segítségével kötik a szintén fluoreszcensekkel jelölt teszt-anyagokat vagy kisebb gyöngyöket. • Majd ezeket adott oldószer (Solvent) segít-ségével folyékony oldatba (Substrate) keverik, és valamilyen tároló eszközön lévő (Reaction Space Holder) (pl. műnyag plate vagy Eppen-dorf cső-rekesz) lyukakba vagy Eppendorf-csövekbe (Reaction Space) helyezik el, ame-lyek falát előzetesen speciális kötőanyagokkal készíthetik elő (Solid Surface) • A tárolóeszköz által sorokba és oszlopokba rendezett, különböző tulajdonságú reakció-terek egy logikai egységet képeznek (Solid Surface Matrix), ahol adott kutatási protokoll (Protocol) adott lépéséhez (Step) tartozó összes teszt és mérőműszer-kalibráció végbe-megy: a flow-citométer meghatározott mérési program szerint felszippantja az adott rekció-terekből a benne lévő oldatot, és azt egy fúvókából mikrométeres átmérőjű sugárban kilőve, különböző színű lézerekkel világítja át • A lézerek visszaverődési színképéből, a visszavert sugarak szögéből, interferencia képükből a fluoreszcensekkel jelölt molekulák koncentrációjára és a sejtek/gyöngyök felszí-nén elfoglalt helyzetére lehet következtetni, amit a műszerhez tartozó statisztikai elemző-szoftver automatikusan előfeldolgoz (a fény-másoló méretű gép többe kerül, mint az épület, amiben van…)

10. Alkalmazás-specifikus eszközállomány modellezése 3 Mindezeket nem azért mondtuk el, hogy jelentősen bővítsük a hallgatóság biológiai/ gyógyszerkutatási ismereteit, hanem hogy: • Képet adjunk róla, hogy az adatbázis tervezőnek egy csúcstechnológiás projekt során milyen szintig kell belemásznia az adott témába – sokszorosan levezekeltük alternatív programként sörözéssel töltött gimnáziumi biosz óráinkat! • Példát mutassunk egy olyan alkalmazásra, ahol az eszközök eléggé LEGO-szerűen változatos struktúrában építhetők össze: • Egy sejt/ jelölt gyöngy felületére köthetünk jelölt molekulát/gyöngyöt első rétegben • Gyöngyöt gyöngyre nem szoktak kötni, technikailag lehetséges, de nincs értelme • Ha az első réteg gyöngy, akkor arra második rétegben köthetnek jelölt molekulát, stb. • Ezért a modellezés megoldása során elég erősen építettünk arra, hogy nem közvetlen relációkkal kötöttük az egyedeket egy fix struktúrába (bár a triviálisabb kapcsolatoknál megtettük), hanem intenzíven használtuk az MDH Termék dimenzióját: mindent cikként vagy sorozatként is definiáltunk és ekként változatos egymásba épüléseket tudtunk leírni kutatási protokollok részeként

11. Könyvelési adatok modellezése Egy szervezet könyvelési alrendszerének két hierarchikus szintje van: • Analitikus könyvelés (Analytic Accounting): a szervezet gazdasági folyamatainak főkönyvi számlákon (Account) történő rögzítése • Szintetikus könyvelés (Synthetic Accounting): az analitikus köny-velés adatainak aggregációja a számviteli törvény előírásai szerint, mérlegek, eredmény-kimutatások és mutatószámos mérleg-elemzés készítése, ami már határos a kontrolinggal is A szintetikus könyveléssel adatbázis tervezés tekintetében nincs sok gondunk, mert törvény írja elő minden országban meghatározott adatstruktúrák használatát, amely ráadásul évente változhat kisebb részleteiben, ezért ezt a területet specializált könyvelési szoftverek látják el Az analitikus könyveléssel annál több dolgunk lesz, mint adatbázis tervezőnek. Az alapstruktúrája igen egyszerű (lásd: VechicleRoutingERD.vsd): • Egyszámlához (Account), ahol az AccountTypeID mutatja, hogy eszköz(Asset)-/vagy forrás(Liability) számla, többszámlatétel (AccountItem) tartozik, ahol az AccountItemTypeID mutatja, hogy tartozik(Payable)-/követel(Due) számlatétel, • A számla önmagára mutató 1:több kapcsolata a FőSzámlaID (MainAccountID) idegen kulcson keresztül ábrázolja, hogy a számlák hierarchikusan egymásba ágyazódhatnak • Bármely eszközt vagy folyamatlépést leíró egyedben a SzámlaTételID (AccountItemID) idegen kulcs mutatja, hogy adott dolog költsége/bevétele hova könyvelődik • A számlatételek felől a tematikus egyedek irányába haladó lekérdezés a Session8–ban már tárgyalt paraméteres táblahivatkozási trükkel oldható meg A bonyolultságok és a kavarások ott kezdődnek, hogy az analitikus könyvelés az egyik fő adóelkerülési eszköz több 1000 trükkel, amitől egy rendes könyvelő fél lábbal mindig a börtönben van, így sokszor köze nincs a valós folyamatokhoz és nem is alkalmas azok nyomon követésére. Az, hogy ez milyen plusz egyedi adat-struktúrákat jelent, mindig az adott alkalmazásban dől el.

12. Az előadás tartalma A Belső logisztika dimenzió szerkezete az MDH-ban • Tárolórendszerek térszerezetének modellezése • Algoritmusok, protokollok modellezése • Alkalmazás-specifikus eszközállomány modellezése • Könyvelési adatok modellezése • Értékesítési adatok modellezése Szakirodalom

13. Szegmens # SzegKód * SzegNév * LojKód * ProfKód Lojalitás # LojKód * LojNév Háztartás # HáztKód * Cím, demo * Duráció * CBG Kód * SzegmKód * LegjobbÜzl Kártya # KártyKód * VevőNév * HáztKód Profit # ProfKód * ProfNév VersCsop # VersCsop * CsopNév Üzlet # ÜzlKód * RégióKód * VersCsop Régió # RégióKód * RégióNév Árazás # ÁrazKód * VonalKód * Hétszám * RégióKód * EgységÁr * EgysKtg Kupon # Vonalkód * Típus * Engedm * ÁrazKód Dimenziók: Tér Idő Termék Üzlet Fogyasztó TermCsop # TermKód * TermNév * FelettKód Termék # Vonalkód * Leírás * TermKód Negyedév # NegyÉv * ÉvSzám Hét # HétSzám * NegyÉv Nap # Dátum * HétSzám Év # ÉvSzám CBG # CBGKód * Terület * Demogr. Tényadatok Tétel # TételKód * Mennyiség * TranzKód * ÁrazKód Tranzakció # TranzKód * Dátum * ÜzletKód * KártyaKód Fogyasztás # FogyKód * Fogyaszt * TermKód * CBG Alsó- Közép- Felső- aggreg.szintek Értékesítési adatok modellezése 1 Fogyasztói kártyát (Loyalty Card) használó értékesítési rendszerek sémájára már a Session4-ben mutattunk példát. A szerkezet részletes kifejtését lásd a SuperMarketERD.xls–ben: • Több fogyasztói Kártyával is rendelkező Háztartások adott ÉvNegyedévének és Hetének adott Napján adott Régióban fekvő Üzletben vásárlási Tranzakció-kat csinálnak, amelyek több megvásárolt Tételből állnak • Ezek önmagára mutató 1:több kapcsolattal nem fix szintszámú hirarchiát alkotó TermékCso-port-okba sorolt Termékek adott hétre/régióra Árazásából, illetve engedmény Kuponokból állnak • A háztartások fogyasztói csopor-tokba, Szegmensekbe csoporto-sulnak, az alapján, hogy milyen kategóriába esik a Profitabilitá-suk (=Profit/Árbevétel) és a Loja-litásuk (=Nálunk vásárolt/Összes fogyasztás). A háztartások be-csült összFogyasztását az alap-ján határozzuk meg, hogy a kár-tyafeliratkozáskor megadott Cím alapján geokódolva őket, melyik népszámlálási körzetbe (CBG) esnek. Ezenkívül üzletekhez/ré-giókhoz is soroljuk őket, a nekik Legjobban eladó üzlet szerint

14. Értékesítési adatok modellezése 2 A gyakorlati adatmodellben (lásd: SuperMarketERD.xls) felvetődik a kérdés, miért kell a kártyákat (Card) háztartásokba (Household) csoportosítani (Householding): • Egy-egy fogyasztói kártya forgalma külön-külön torz képet adhat: pl. a papa csak sört vesz, ezért alkoho-listának nézzük, a mama csak zöld-séget, ezért vegetáriánusként azono-sítjuk, holott ketten együtt átlagos fogyasztású háztartást alkotnak. Ezért kell a kártyákat háztatásokba csopor-tosítani • A fogyasztó a kártyára történő felirat-kozáskor a legtöbb valós rendszerben sajnos egy normalizálatlan címet kérnek, gyakran lakásszám nélkül • Ezért a HázSzám, KözterületNév, KözterületTipusID, IrányítóSzám szerint normalizáljuk a címeket a Session6–ban tárgyalt programkód segítségével, és ezen mezők egyezése alapján próbáljuk a kártyákat háztartásokba csoportosítani • Néha beleütközünk a problémába, hogy gyanúsan sok kártyát csoporto-sítunk egybe: pl. lakásszám híján egy tömbház, vagy nyugdíjasotthon összes lakóját egy háztartásba rakjuk • Ezért a konzervatív megközelítést követjük: megköveteljük a Csaladi-Név mező egyezését is.

15. Ezzel elbukjuk ugyan az együtt lakó diákok, élettársak, férj nevét fel nem vett feleségek azonosítását, de tapasztalataink szerint ez még mindig a kisebbik rossz Biztonsági korlátként, semmiképpen nem csoportosítunk egy háztartásba három kártyánál többet (töröljük a 3 kártyánál nagyobb létszámú csoportokat (HAVING COUNT(CardCode)>3), és ezeket a kártyákat külön háztartásonként APPEND-eljük Az értékesítési adatok kezelésének másik fő problémája az aggregációjuk, összesítésük alapmértékegységének (Basic Measure Unit) kiválasztása: Az aggregáció hierarchikus szintjein (pl. Kártya<Háztartás<Szegmens vagy Kártya<Háztartás<Üzlet<Régió vagy pl. időben Nap<Hét<Negyedév<Év) nem jó keverni különféle mértékegységeket, mert ez hibákhoz vezethet Így az összes monetáris mérték (profit, forgalom, költségek, kuponbeváltások) $/hét/háztartás-szintű átlagként kell, hogy megjelenjen a kimutatásokban Ez azért szükséges, mert az egyes szegmensek/ $ profit csoportok/ régiók /versenycsoportok és összes kombinációik eltérő számú háztartást tartalmaznak, tehát adataik csak akkor összehasonlíthatóak, ha beosztunk a háztartások számával Az egyes háztartások eltérő számú hetet tölthetnek a rendszerben egy adott negyedévben. Pl. ha egy új, jó vevőnk van, aki sok profitot termel, de csak 4 hete van a rendszerben, nyilván nem képes abszolút mértékben annyi proftot termelni, mint egy közepes vevő, akinek erre 13 hete volt. Így hajlamosak lennénk alulértékelni egy jó, de új vevőt. Ezt korrigálandó, osztjuk az adatokat a rendszerben töltött hetek számával (Duration) Azonban, a nagyon új vevőknél bizonytalan, hogy a jövőben is fennmarad kedvező teljesítményük: általában a klubkártya kiváltásakor mindenki vásárol egy nagyot, de lehet, hogy többet soha!! Ezért a 2 hétnél fiatalabb vevőknél a Duration értékét egy előzetes lekérdezéssel felnyomjuk 13 hétre, ami majd leviszi az átlagaikat, hogy ne lehessen őket túlértékelni 1-2 jó kezdő vásárlás alapján. Az átlagokat NEM tároljuk az előaggregációs szinteket képező táblákban, mivel az átlagok nem aggregálhatók tovább Ehelyett, külön-külön aggregáljuk a $ mennyiségeket, a durációs heteket, a háztartások számát, és CSAK a végső kimutatás számol ezekből átlagot Értékesítési adatok modellezése 3

16. Szakirodalom • A CDISC Inc. Orvosi protokol adatmodellje (angolul): http://www.cdisc.org/models/odm/v1.1/odm1-1-0.html • HDML: protokoll orvosi adatok továbbítására: http://cmbi.bjmu.edu.cn/news/report/2004/medinfo2004/pdffiles/papers/132_d040005713.pdf