Adatbáziskezelés 2

Adatbáziskezelés 2 3. Előadás Dr. Pauler Gábor, egyetemi docens PTE-TTK IATT, 7624 Pécs, Ifjúság u.6. B104 Mobil: 30/9015-488, Skype: gjpauler E-mail:pauler@t-online.hu Facebook: Pécs Gazdinfo Adatbázis http://www.facebook.com/groups/278606362188127/ ftp://szentagothai.ttk.pte.hu/pub/pauler/Database2

Az előadás tartalma • Relációs adatbáziskezelők • Normalizációs folyamat • 2.Normálforma: Relációk • Elsődleges kulcsok kiválasztása • 1:több relációk tárolása • Több:több relációk tárolása • Egyedkapcsolati diagramm (ERD) • Relációk ERD-n • 1:több Kapcsolat ábrázolása • 1:több Hibalehetőségek: Idegen kulcs elfelejtése, Oldalcsere • Több:több Kapcsolat ábrázolása • Több:több Hibalehetőségek: Relációs tábla kifelejtése, Csirkelábak megfordítása • A minimális számosságú csatolás szabálya • 3.Normálforma: Rejtett függések kibontása • Hasznossága • Kinézegető táblák • A normalizált tárolási szerkezet és a GUI szerkezete közti különbség

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Elsődleges kulcsok • 2. Normálforma: a gyakorlati adatstruktúrák adattartamának megőrzése miatt a szétbontott egyedeket/táblákat össze kell kötni relációkkal (Relations). Ehhez először Elsődleges kulcsmezőt (Primary Key, PK) adunk minden egyedhez, mely: • Egyedileg azonosítja (Unique ID) az előfordulásokat, nem lehetnek benne ismétlődő értékek, nem tartalmazhat NULLt. • Mindig mesterséges kulcs (Artificial Key) mező: • Numerikus (Numeric): 10× gyorsabban működik, mint szöveges kulcsok • Auto-számozott (Auto-Numbered): a felhasználó ne tudja összekutyulni az értékeit • Ha az egyednek van természetes azonosítója (Natural Key), azt egyszerű adatmezőként tároljuk a mesterséges elsődleges kulcs mellett: (pl. a Számlának van természetes egyedi azonosítója, a SzámlaSzám, mégis egy mesterséges SzámlaID-t használunk elsődleges kulcsnak) • Miért? Mert különben a természetes azonosítóban bekövetkező bármely hiba (pl. két hajléktalannak azonos a személyi igazolvány száma és összesen 18 kormánypárthoz közeli off-shore cég van a nevükön) vagy változás (pl. magyar számlaszámokról EU-kompatibilisra átállás) bedöntené az adatbázist • Az elsődleges kulcs neve mindig legyen EgyedLogikaiNév+ID • Ez azért fontos, mert ha minden táblában egyszerűen „Kód”-nak hívnánk az elsődleges kulcsot, amikor majd más táblákban meghivatkozzuk őket, a sok azonos mezőnév ott ütközne egymással, és könnyen összekeverhetők lennének • Mivel az egyedek logikai neve egyedi az adatbázisban, a belőlük képzett elsődleges kulcs nevek soha nem keveredhetnek össze! • Az elsődleges kulcsot mindig narancs színnel jelöljük

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Relációk: 1:több • 1:1 reláció tárolódása adatbázisban: • Ilyen normalizált adatbázisban nincs, mert az 1:1 kapcsolódó mezők egy egyedbe kerülnek az egyed-tulajdonság szabály alapján! • 1:több reláció tárolódása adatbázisban: • pl. 1Eladóhoz többSzámlatartozhat, de 1Számlához 1Eladótartozik. • Az 1oldali tábla (Eladók) elsődleges (EladóID) kulcsára hivatkozunk a több oldali táblába (Számlák) rakott idegen kulcsmezővel (Foreign Key, FK), aminek: • Neve (EladóID), típusa megegyezik a hivatkozott elsődleges kulcséval, • Ismétlődhetnek benne értékek (pl. sokSzámla kaphat azonos EladóID-t, így 1Eladóhoz korlátlan sokSzámla csatolható) • Kötelező kitöltésű az idegen kulcs (Reqired FK) (nem lehet benne NULL), ha a reláció 1:több, független:függő • pl. Számla nem létezhet Eladó nélkül, muszáj hogy hivatkozzon egyre • Opcionális kitöltésű az idegen kulcs (Optional FK) (lehet benne NULL), ha a reláció 1:több, független:független: • pl.1EmbertöbbKönyvPéldánytkölcsönözhet, de 1KönyvPéldány1Ember által kölcsönözhető • KönyvPéldány úgy is létezik, hogy épp senki nem kölcsönzi, ilyenkor az EmberID idegen kulcsa NULL • Az idegen kulcsot sötétzöld színnel jelöljük

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Relációk: Több:több • Több:több reláció tárolódása adatbázisban: • pl. 1Számlának többTerméklehetTétele, és 1TerméktöbbSzámlán lehetTétel • 2 db 1:több kapcsolatra bontva tárolható, ahol mindkét rész-kapcsolat több oldala a relációs tábla (RelationTable) amit kék színnel jelölünk (pl. Tételek): ez 2 idegen kulcsot tartalmaz (pl.VonalKód, SzámlaID) amelyek az összekötött törzstáblák (Master Table) (pl. Számlák és Termékek) elsődleges kulcsaira hivatkoznak: • A relációs táblához is mindig adunk elsődleges kulcsot (pl. TételID), de ennek nincs szerepe a több:több reláció tárolásában. Azért kell, hogy később a relációs táblára más táblákat tudjunk kötni (pl. Engedmények). • A relációs táblának lehetnek saját adatmezői is (pl. Mennyiség) amelyek gyakran másból számított mezők (pl. BruttóÉrték)

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Egyedkapcsolati diagramm EgyedNév EgyedNévID Szöveg EgészSzám TörtSzám Bináris Dátum Idő Kép Hang Mozi KötIdegKulcs OpcIdegKulcs Módosító Módosítva Állapot Kód KódID KódNév • A relációs adatbázis szerkezete mintatáblákkal nehezen áttekinthető, ezért egyedkapcsolati diagramm (Entity-relationship Diagram, ERD) segítségével modellezhetjük, melynek jelölései: • Az egyedek lekerekített blokkok, EgyedNev aláhúzva • Elsődleges kulcs( ):EgyedNevID • Félkövérek az auto-kitöltött mezők (pl. és rendszernaplózó mezők: Módosító, Módosítva, Állapot={0:terv, 1:aktív, 2:archív, 3:törlendő}) • A kötelező mezők normál, az opcionálisak dőlt szedésűek • A sima adatmezők előtt adat-típus ikonok:( , , , , , , , , ) • A kötelező/ opcionálisidegen kulcsok ikonja:( ) • A tranzakció egyedek (TransactionEntity) - amelyek gyorsan változó adatokat tartalmaznak - háttere sárga, bennük a rekordok életciklu-sát leíró rendszernaplózó mezők (System LoggingFields) találhatók • A ritkán változó törzs/kód egyedek (Master/CodeEntity) háttere kék • Reláció(Relation):derékszögben törő összekötő vonal, ahol: • Több oldalon csirkeláb (Crows leg, ) van, ezt mindig az idegen kulcs mellé kötjük az egyed blokkon • 1 oldalon vonal, az egyed-blokk aljára/tetejére kötjük • A vonal az egzisztenciálisan függő oldalon folytonos ( ), füg-getlenen szaggatott( )(ha ez több oldal, akkor az opcionális) • A relációk működésének 3 hibás adatok elleni védelmi beállítása: • Hivatkozás integritás (ReferentialIntegrity)ellenőrzés be/ki( , ): • Ha aktív, nem törölhető olyan 1 oldali rekord, amire többol-dali rekordok hivatkoznak, előbb azokat kell letörölni, ÉS • A több oldalon idegen kulcsmezőbe nem vihető be olyan érték, ami nem szerepel az 1 oldal elsődleges kulcsában • Hivatkozás-átkapcsolás (Switch) tiltása/engedése ( , ): az idegen kulcs módosítható-e, vagy csak létrehozni/törölni lehet • Kaszkádolt törlés (CascadeDelete) tiltása/engedése ( , ): ha az 1 oldali tábla rekordját törlik, törlődjenek-e a rá hivatkozó több oldali rekordok, valamint az azokra hivatkozók is, láncreakcióban

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Relációk ERD-n 1 Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő EladóID Eladó EladóID EladóNév EladAdóSzám Mobil E-mail URL • Az egyedkapcsolati diagram tömören és jól áttekinthetően ábrázolja az adatbázis szerkezetét, de szükség van némi absztrakciós készségre a megértéséhez: • Példa 1:több reláció ábrázolására ERD-n: • VIGYÁZAT, TIPIKUS KEZDŐ HIBA! Egy egyed-dobozka nem csak azt a pár mezőt jelenti, amit magunk előtt látunk, hanem képzeljük mögé az adatbázis táblát is, amiben akár több millió rekord is lehet, különben nem fogjuk érteni, hogy a reláció hogy képes letárolni nem fix strukturális hosszúságú adatokat! Elsődleges kulcs 1 oldal Több oldal Idegen kulcs 1 oldali tábla több oldali tábla Idegen kulcs Elsődleges kulcs

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Hibák 1 Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő Eladó EladóID EladóNév EladAdóSzám Mobil E-mail URL Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő • Eladó • EladóID • EladóNév • EladAdóSzám • Mobil • E-mail • URL • SzámlaID • VIGYÁZAT, TIPIKUS KEZDŐ HIBA!: Ne felejtsük el az idegen kulcsot a több oldali táblából! A csirkeláb önmagában NEM jelent kapcsolatot, csak kijelzi azt. Ha nincs idegen kulcs, a kapcsolat nem tud hol tárolódni!!! • VIGYÁZAT, TIPIKUS KEZDŐ HIBA!: Ne cseréljük fel az 1 és több oldalt! • Az elsődleges kulcs van az 1 oldalon, az idegen kulcs a több oldalon • Ha felcseréljük, a kapcsolat csak 1:1-et tud tárolni és adatot fogunk veszteni: Osztán hol tárolod a cég által adott többi 612781 számlát, Öcsi-sajt!? Mer’ ide csak 1 db SzámlaID fér!

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Relációk ERD-n 2 Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő Termék VonalKód Leírás EgységÁr Tétel TételID Mennyis NetÉrt BrutÉrt SzámlaID VonalKód • Példa több:több kapcsolat ábrázolására ERD-n: • Különösen fontos az egyedek mögötti táblákat elképzelni a több:több kapcsolat ábrázolásánál: a relációs táblában (pl. Tételek) rengeteg rekord lehet, ezért képes leírni bármilyen kapcsolódásta törzstáblák rekordjai közt: • Pl. ugyanazon ismétlődő VonalKódhoz sok eltérő SzámlaID rendelődik számos rekordon keresztül, ha egyfajta TerméksokSzámlán szerepel • Vagy ugyanazon ismétlődő SzámlaIDhez sok eltérő VonalKód rendelődik számos rekordon keresztül, ha egySzámlánsokfajta Termék szerepel Törzs egyed Relációs egyed Törzs egyed 1 oldal Több oldal Több oldal 1 oldal Törzs tábla Törzs tábla Relációs tábla Elsődleges kulcs Elsődleges kulcs Idegen kulcs Idegen kulcs

Relációs adatbáziskezelés: Normalizáció: 2.Normálforma: Hibák 2 Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő VonalKód • Termék • VonalKód • Leírás • EgységÁr • SzámlaID • VIGYÁZAT, TIPIKUS KEZDŐ HIBA!: Ne felejtsük el, hogy a több:több kapcsolat tárolásához kell egy harmadik relációs tábla! Gyakori hiba, hogy ehelyett a 2 törzstáblát kötik szembe fordított irányú 1:több relációkkal: • Ez azonban működésképtelen: pl. ha van olyan Termékfajta, ami többSzámlán is szerepel, és ezen Számlák közt is van olyan, amin többfajta Termék szerepel, a kapcsolatok letárolásához meg kellene többszörözni azonos rekordokat mind a Termékek és Számlák táblában, ami ismétlődést okozna az elsődleges kulcsaikban, és totálisan összezavarná a rájuk szóló hivatkozásokat! • VIGYÁZAT, TIPIKUS KEZDŐ HIBA!: Ne tévesszük el a csirkelábak irányát a relációs táblánál! Gyakran úgy rajzolják fel a 2 csirkelábat, hogy a törzstáblák vannak a több oldalon, mert így néz ki a „több:több” csirkeláb. De a relációs táblában vannak az idegen kulcsok, ezért az mindkét részkapcsolat több oldala! Ezek közül most melyik az igazi, Öcsisajt?!

RDM: Normalizáció: 2.Normálforma:Minimális számosság Számla EladóNév R EladóCím R EladóAdóSzám R VevőNév CRU VevőCím CRU SzámlaSzám R ÉrtékesítőNév CRU ÖsszÉrték= Sum(Tétel.BruttóÉrték) Fizetve CRUD Dátum Idő Tétel TételLeír R MértEgys R VonalKód CR ITJKód R Mennyis CRUD EgységÁr R ÁFA% R BruttóÉrték= (EgységÁr* Mennyis*(+ ÁFA%/100)) Eladó EladóID CégNév JogiForma EladóAdóSz Ajtó Emelet Épület HázSzám KözTerNév KözTerTip Város IránySzám Ország Telefon Mobil E-mail URL Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő EladóID VevőID ÉrtékesítID Módosító Módosítva Állapot Vevő VevőID KeresztNév VezetékNév Ajtó Emelet Épület HázSzám KözTerNév KözTerTip Város IránySzám Ország Mobil E-mail Értékesítő ÉrtékesítőID KeresztNév VezetékNév Ajtó Emelet Épület HázSzám KözTerNév KözTerTip Város IránySzám Ország Telefon Mobil E-mail Tétel TételID Mennyis NetÉrt BrutÉrt SzámlaID VonalKód Modosító Modosítva Állapot • Termék • VonalKód • Leírás • EgységÁr • ITJKód • MértEgys • A 2. normálformában a gyakorlati adatstruktúrák a következő összekötött egyedekre esnek szét: • 1Eladóhoz többSzámlatartozhat, de 1Számlához 1Eladótartozik • 1Vevőhöz többSzámlatartozhat, de 1Számlához 1Vevőtartozik • 1Értékesítőhöz többSzámlatartozhat, de 1Számlához 1Értékesítőtartozik • 1Számlához többTételtartozhat, de 1Tételhez 1Számlatartozik • Kérdés, hogy melyikhez érdemes kötni új egyed-ként a Termék(fajtát)? Erre 3 lehetőség is van: • 1ÉrtékesítőtöbbTerméket adhat el, és 1Termék(fajtát)többÉrtékesítőadhat el • 1VevőtöbbTerméket vehet, és 1Termék(fajtát)többVevővehet • 1Termék(fajta)többTételhez tartozhat, de 1Tételhez 1Termék(fajta)tartozik •  Amikor a Termék(fajtát) az Értékesítőhöz vagy az Eladóhoz kapcsoljuk, ezek több:több kapcsolatok lennének, és elveszítjük az infót, hogy a Termék(fajta) mely Tételekben szerepelt. •  Amikor a Tételhez csatoljuk, ez 1:több kapcsolat lesz, és a Számlán keresztül vissza tudjuk hozzá keresni az Értékesítőt és az Eladót, így nem vesztünk infót. • Ez a legalacsonyabb számosság szabálya (Mini-mal cardinality rule): ha egy új egyedet több meglévő egyedhez is csatolhatunk, akkor a leg-alacsonyabb számosságú, legfüggőbb kapcso-lattal csatoljuk (pl. 1:több,független:függőtöbb:több,független:független helyett)

Relációs adatbáziskezelés: Normalizáció: 3.Normálforma 1 JogiForma JogiForma FormaNév • Vevő • VevőID • KeresztNév • VezetékNév • Ajtó • Emelet • Épület • HázSzám • KözTerNév • KözTerTip • IrSzám • Mobil • E-mail • Eladó • EladóID • CégNév • JogiForma • EladóAdóSz • Ajtó • Emelet • Épület • HázSzám • KözTerNév • KözTerTip • IrSzám • Telefon • Mobil • E-mail • URL • A 3. Normálforma az egyeden belüli rejtett függések (Hidden Dependencies) kibontásáról szól: • 1Eladóhoz/Vevőhöz/Értékesítőhöz 1IránySzám, Település, Országtartozik (1 időpontban) ezért ezek egyszerű (nem kulcs) mezők lettek ezen táblákban, így az adatrögzítőnek minden rekordban kézzel kell kitölteni mind a hármat (pl. „8688”, „Vasmacskakovácsi”, „Közép-Kelet Abszurdisztán”) • Előfordulhat azonban, hogy egy egyszerű adatmező értéke egyértelműen meghatározza egy másik értékét: • 1IrányítóSzám1Településhez tartozik(Mo.-n vannak ritka kivételek aprófalvalban de hanyagolhatók), de 1Településhez többIrányítóSzámtartozhat Település:IrányítóSzám=1:több, független:függő  IránySzámegyértelműenmeghatározza aTelepülést • Hasonlóképpen: Ország:IrányítóSzám=1:több, füg-getlen:függő  IrSzámmeghatározza azOrszágot • Ekkor érdemes az IrSzámok és az Országok táblákat külön kibontani a fenti relációkkal összekötve, mert így az adatrögzítő csak a rövid, nehezen elírható IrSzám idegen kulcsot viszi be a címeknél (pl. „8688”), a többi adat automatikusan lekérdezhető hozzá a táblákból a relációkon keresztül  Órási adatrögzítési- és idő-megtakarítás! (pl. áruházakban ÁFÁs számlázásnál!) • Sőt, egyáltalán nem tud elírni semmit, ha az IrSzám idegen kulcs, nem egyszerű szövegdoboz (Textbox) kontrollként jelenik meg, hanem olyan legördülő lista (Dropdown list/Combo Box), ami a tartalmát a hivat-kozott 1 oldali tábla (IrSzámok) elsődleges kulcsából (IrSzám) és annak leírásából (Települ) szedi fel. Ezt nevezzük kinézegető táblának (Lookup table) • Hasonló okokból bontjuk ki a JogiForma, KözTerTip, ÁFA, ITJ, MértEgys kinézegető táblákat KözTerTip KözTerTip TipusNév IrSzám IrSzám Települ Ország Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő EladóID VevőID ÉrtékesítID Módosító Módosítva Állapot • Értékesítő • ÉrtékesítőID • KeresztNév • VezetékNév • Ajtó • Emelet • Épület • HázSzám • KözTerNév • KözTerTip • IrSzám • Telefon • Mobil • E-mail Ország Ország OrszNév ÁFA ÁFASáv ÁFA% Tétel TételID Mennyis NetÉrt BrutÉrt SzámlaID VonalKód Modosító Modosítva Állapot ITJ ITJKód Leírás ÁFASáv Termék VonalKód Leírás EgységÁr IITJKód MértEgys MértEgys MértEgys MEgysNév

A normalizáció következtében egy egyszerű kis ÁFÁ-s szám-la adatai is rémisztően sok táblára esnek szét, és a norma-lizált, minimális helyfoglalásra és adatrögzítési igényre opti-malizált tárolási szerkezet teljesen más,mint a papír-alapú! Ezért a tervet a fejlesztő is csak ERD-n tudja áttekinteni, de a felhasználónak erre esélye sincs Ezért a tárolási rendszer adatbázis táblái és a GUI űrlapjai (Form) közt majd egy nézettáblás lekérdezés (View Table) teremt kétirányú adatkapcsolatot: felhozza a tárolt adatokat és visszaviszi a felhasználó által rögzítetteket Emiatt a GUI-n a felhasználó szinte ugyanazt láthatja majd, mint papíron, de az elektronikus űrlap korlátlan kapacitással fog működni (pl.max.12 tétel helyett sok 100-at bevihetünk) DE SOHA NE KEVERJÜK ÖSSZE A TÁROLÁSI STRUK-TÚRÁT A GUI-N LÁTHATÓVAL!!! Lássunk erről 1 mesét: Relációs adatbáziskezelés: Normalizáció: 3.Normálforma: Hol tartunk most? JogiForma JogiForma FormaNév • Vevő • VevőID • KeresztNév • VezetékNév • Ajtó • Emelet • Épület • HázSzám • KözTerNév • KözTerTip • IrSzám • Mobil • E-mail • Eladó • EladóID • CégNév • JogiForma • EladóAdóSz • Ajtó • Emelet • Épület • HázSzám • KözTerNév • KözTerTip • IrSzám • Telefon • Mobil • E-mail • URL KözTerTip KözTerTip TipusNév IrSzám IrSzám Települ Ország SELECT Szamlak.SzlaSzam, Szamlak.Datum, Szamlak.VegOsszeg, Szamlak.Fizetve, Szamlak.EladoKod, Szamlak.VevoKod, Eladok.Nev, Eladok.AdoSzam, Ertekesitok.Nev, Cimek.HazSzam, Cimek.Emelet, Cimek.LakasSzam, Cimek.KozTerNev, Cimek.IranySzam, IrSzamok.Telepul, Vevok.Nev, Vevok.HazSzam, Vevok.Emelet, Vevok.LakasSzam, Vevok.KozTerNev, Vevok.IranySzam, IrSzam_1.Telepul FROM ((IranySzamok AS IranySzamok_1 INNER JOINVasarlok ONIranySzamok_1.IranySzam = Vasarlok.IranySzam) INNER JOIN(((IranySzamokINNER JOINCegek ONIranySzamok.IranySzam = Cegek.IranySzam) INNER JOIN Eladok ONCegek.AdoSzam = Eladok.AdoSzam) INNER JOINSzamlak ONEladok.EladoKod = Szamlak.EladoKod) ONVasarlok.VasarloKod = Szamlak.VasarloKod); Számla SzámlaID SzámlaSzám TételSzám NetÖsszÉrt BrtÖsszÉrt ÁFAÖssz Fizetve KibocsDátum KibocsIdő EladóID VevőID ÉrtékesítID Módosító Módosítva Állapot • Értékesítő • ÉrtékesítőID • KeresztNév • VezetékNév • Ajtó • Emelet • Épület • HázSzám • KözTerNév • KözTerTip • IrSzám • Telefon • Mobil • E-mail Ország Ország OrszNév ÁFA ÁFASáv ÁFA% Tétel TételID Mennyis NetÉrt BrutÉrt SzámlaID VonalKód Modosító Modosítva Állapot ITJ ITJKód Leírás ÁFASáv Termék VonalKód Leírás EgységÁr IITJKód MértEgys MértEgys MértEgys MEgysNév

Szakirodalom Relációs adatbáziskezelés: • E. F. Codd: http://en.wikipedia.org/wiki/Edgar_F._Codd, http://www.itworld.com/nl/db_mgr/05072001/ Normalizáció: • www.inczedy.hu/~szikszai/adatbazis/ab2.ppt • http://support.microsoft.com/kb/283878/hu

Adatbáziskezelés 2