Sapientia - Erdélyi Magyar Tudományegyetem (EMTE ) Csíkszereda

1. Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda 9. Előadás tartalma Függőségek vetítése. Normalizálás Normálformák. A relációs adatmodellt először E. F. Codd határozta (Codd 1970). Ő vezette be a normalizált reláció kifejezést. Amikor megalkotta a relációs modellt, 13 szabályt adott meg a relációk táblákkal való bemutatására.

2. Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda Egy relációban az attribútumok közötti függőségek generálhatnak olyan többletinformációt, amit redundanciának nevezünk. Ezek anomáliákhoz vezetnek. A beszúrási, törlési és módosítási anomáliákat ki tudjuk küszöbölni azzal, hogy a kiinduló relációt (táblát) több táblára bontjuk fel. Ezen táblák felbontását úgy végezzük el, hogy a táblák összekapcsolásából visszakaphassuk az eredeti relációt. Általában idegen kulcsok és gyenge egyedhalmazok (gyenge relációk) keletkezése történik, vagy éppen kapcsolat-relációk (ld. Egyed/Kapcsolat modell).

4. Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda A normálforma: Tábla újrarendezése a benne levő funkcionális függőségek alapján Funkcionális függőség: A→B, ha ismerjük egy (vagy több) tulajdonság értékét, akkor ebből meg tudjuk határozni egy másik tulajdonság értékét Többértékű függőség: A→→B Ha ismerjük egy (vagy több) tulajdonság értékét, akkor mindig meg tudjuk határozni egy másik tulajdonság értékeinek HALMAZÁT. Pl: Ha tudom egy tanár nevét, akkor meg tudom határozni a tanítványainak a névsorát.

5. Funkcionális függőségek vetítése R reláció egy S funkcionális függőséghalmazzal, ahol elkészít-jük R egy vetítését: R1=πL(R) R néhány attribútumára. Milyen függőségek állnak fel R1-ben? S funkcionális függőségeinek vetítése adja meg, amelyek: S-ből levezethetők csak R1 attribútumait tartalmazzák. R1 funkcionális függőségeinek kiszámítási ideje legrosszabb esetben az R1-beli attribútumok számának exponenciális függvénye (Ullman)

6. BEMENET: R, R1 (az R vetítése), S függőséghalmaz R-ben • KIMENET: Az R1-ben fennálló funkcionális függőségek (FF) halmaza • Legyen T a végül előálló FF-ek halmaza. Kezdetben üres. • Minden olyan X attribútumhalmazra, amely R1 része, számítsuk ki az X+-t. Adjuk hozzá T-hez az összes nem triviális függőséget, amelyek X→A formátumúak, ahol A eleme az X+és az R1 attribútumhalmaznak is. • Ezután a kapott T bázisa az R1-beli funkcionális függőségeknek, de nem biztos a minimalitás. • Ha szerepel F funkcionális függőség, amely más T-beli függőségekből következik, akkor töröljük a T halmazból • Y→B egy T-beli funkcionális függőség, ahol Y legalább két attribútumot tartalmaz, és legyen Z az a halmaz, amelyet úgy kapunk, hogy Y-ból egy attribútumot elhagyunk. Ha Z→B függőség következik a T-beli funkcionális függőségekből (beleértve Y→B), akkor cseréljük ki Y→B-t Z→B-re.

7. R(A,B,C,D) reláció és A→B, B→C és C→D FF-ek. Keressük R1(A,C,D) reláció FF halmazát Elvileg {A,C,D} mind a 8 részhalmazát kellene vizsgálni Az üres, vagy a minden attribútumot tartalmazó halmaz lezárása nem vezet nem triviális függőséghez Ha X halmaz lezárása tartalmazza az összes attribútumot, akkor nem tudunk újabb FF-hez jutni az X szuperhalmazai-nak lezárásával. {A}+={A,B,C,D}, azaz A→C és A→D fennáll R1-ben. {C}+={C,D}, amelyből következik a C→D {D}+={D} {A} szuperhalmazaival nem kell foglalkozni a 2 pont szerint Egyetlen 2 elemű halmaz {C,D}+={C,D}. A megtalált FF-ek: A→C, A→D és C→D. Tranzitivitás miatt kiküszöbölhetjük az A→D FF-et Maradtak: A→C és C→D FF-ek.

8. Normálformák Azadatmodellezésegyikfőcéljaazoptimalizálás, vagyisazadatmodelltalkotóegyedtípusoklehetőlegjobbszerkezetének a megkeresése. A normalizálásaz a folyamat, amellyelkialakítjuk a relációknormálformáját. A normálformák: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, egymásbaskatulyázottak, logikusanegymásraépülnek.

9. Első normál forma (1NF) Értelmezés: EgyR reláció 1NF –ban van, ha azattribútumoknakcsak elemi (nemösszetettvagyismétlődő) értékeivannak. Nincsenek ismétlődő csoportok és minden oszlop SKALÁRIS. Nincsenek TÖBÖK, Kapcsolt Listák, Beágyazott Táblák és rekordszerkezetek. Példaegyolyanrelációra, melynincs 1NF-ben: Alkalmazottak:

10. 1NF-re alakítás • Összetett attribútum esetén: azösszetettattribútumhelyettbeírjukazaztalkotó elemi attribútumokat. • Ismétlődő attribútum esetén felbontjuk két relációra: azegyikrelációban a kulcsattribútummellettazismétlődőattribútumok (csakegyszer) fognakszerepelni, a másikbanpedig a kulcsmellettazonattribútumokmelyeknemismétlődőek • Példa: • Alkalmazott (SzemSzám, Név, Helység, Utca, Szám) • AlkalmGyerekei (SzemSzám, GyerekNév, SzülDátum) • Ebben az esetben látjuk, hogy az alkalmazott gyerekeinek nyilvántartása egy olyan relációba kerül, amelyik gyenge egyedhalmazként definiálható az Egyed/Kapcsolat modellben.

11. A1 A2 A3 A4 A5 A6 Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda Parciális függőség: Ha X és Y oszlopok és X kulcs, akkor bármely Z-re, amely részhalmaza X-nek igaz, hogy Z nem határozhatja meg funkcionálisan Y-t A második normálforma (2NF) Egy második normálformában levő tábla úgy jellemezhető, hogy NEM tartalmaz parciális függőségeket. Ha {A1,A2} KULCS, akkor ez a reláció NINCS 2NF-ben mert A2→A3

12. A reláció kulcsa {szállID,ÁruID} Nincs 2NF-ben, mert SzállID→{SzállNév, SzállCím} ÁruID→{ÁruNév, MértEgys} {SzállID,ÁruID}→Ár Varga Ibolya példája

13. Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda Definíció: Egy R reláció akkor és csakis akkor van 2NF-ben ha R 1NF-ben van és minden nem-kulcs attribútuma funkcionálisan függ a kulcstól és csakis a kulcstól. A relációkat 2NF-ra olyan formán hozzuk, hogy megkeressük az összes lehetséges kulcsot, majd annál amelyiket kiválasztottuk elsődleges kulcsként megvizsgáljuk az összes PARCIÁLIS függőséget, majd ezeket külön relációkba tesszük. A kapott relációkban is megvizsgáljuk ugyanazt.

14. Az R(A) relációt felbontjuk két relációra, melyek sémái: T(D, B) ésS(A-B) Első lépésben B = {SzállNév,SzállCím}, D = {SzállID}. Felbontás után kapjuk: Szállítók (SzállID, SzállNév,SzállCím) SzállInf (SzállID, ÁruID, ÁruNév, MértEgys, Ár) nincs 2NF-ben, mert ÁruID→{ÁruNév, MértEgys} parciáliskulcsfüggőség Továbbá S(X) felbomlik U(C,E) es V(X-E) E= {ÁruNév, MértEgys}, C= {ÁruID}, X-E={SzállID, ÁruID,Ár} Áruk (ÁruID, ÁruNév, MértEgys), Szállít (SzállID, ÁruID, Ár).

15. Szállítók: Szállít: Áruk:

16. Harmadik normálforma 3NF (a nem kulcs attribútumok közötti függőség) Definíció: Egy R reláció harmadik normál formában van, ha második normál formában van és nem tartalmaz Y->B alakú tranzitív funkcionális függőséget, ahol B nem prim attribútum. Definíció: Egy tábla 3NF-ben van, ha minden X→Y, ra ahol X és Y a tábla oszlopai X kulcs, vagy Y része egy kulcsnak. Értelmezés: Minden olyan oszlop, amely nem kulcs, meghatározható a kulccsal, az egész kulccsal és csak a kulccsal. Magyarázat a 3NF-hez: Nincsenek benne TRANZITÍV függőségek X→Y és Y→Z

17. Cím év→stúdióNév stúdióNév→stúdióCím Tranzitivitás miatt következik Cím év→stúdióCím

19. A tanárok, hallgatók, osztálytermeket leíró adatbázis a következő képpen nézhet ki: Kurzustartas(kurzusID, szakID, profID, terem, teremméret, időpont) Kurzusfelvétel(Törzsszám, kurzusID, évfolyam) Hallgató(törzsszám, név, főszak) Professzor(profID, név, tanszék, fokozat) Kurzus(kurzusID, megnevezés) {kurzusID,szakID}→terem terem→teremméret A Kurzustartas a következő relációkra bontható, hogy 3NF-ben legyen Terem(teremID, teremméret) Kurzustartas(kurzusID, szakID, profID, teremID, időpont)

20. Boyce-Codd normálforma (BCNF) Egy tábla BCNF-ban van, ha minden nem triviális (X→A) funkcionális függőségre X szuperkulcs az egész sémára nézve. Minden nem triviális függőség bal oldalának szuper-kulcsnak kell lennie. Ekvivalens def.: R reláció BCNF-ben van akkor és csak akkor, ha bármikor fenáll az R-ben az A1A2..An→B1B2...Bm nem triviális függőség, akkor az {A1,A2...An} halmaz R szuperkulcsa kell legyen 2 attribútumból álló reláció BCNF-ben van. Akkor bontsunk minden relációt 2 tagra ?????!!!!!! Ez sem működik, mivel nem biztos, hogy a relációk összekapcsolásából vissza tudjuk kapni az eredeti relációt.

21. Boyce-Codd normálformájú felbontás • Bármely relációsémát fel tudunk bontani az attribútumaiból álló részhalmazok összességére, amelyre az alábbi fontos tulajdonságok teljesülnek: • Ezek a részhalmazok BCNF-ben levő relációsémák • A felbontott relációkból összekapcsolásokon keresztül vissza lehet állítani az eredeti relációt. • Felbontási stratégia: • Felveszünk egy A1A2..An→B1B2...Bm nem triviális funkcionális függőséget, amelyik megsérti a BCNF-t, azaz {A1,A2…An} nem szuperkulcs.

22. BCNF dekompozíció algoritmusa R relációra és S függőségi halmazra alkalmazható rekurzívan. Kezdetben R=R0és S=S0. Ellenőrizzük, R BCNF-ben van-e. Ha igen, kész. Ha vannak BCNF – t megsértő függőségek, pl. X→Y. Kiszámítjuk X+-t. Legyen R1=X+ az egyik relációséma, R2-ben legyenek benne az X attribútumai és azok az R-beli attribútumok, amelyek nincsenek az X+-ban. Használjuk az algoritmust az R1 és R2 függőségeinek meghatározásához, melyek S1 és S2. Rekurzívan bontsuk fel R1-et és R2-t ennek az algoritmusnak a használatával. A végeredmény a dekompozíciók uniója lesz.

23. Nem teljesül a B→C funkcionális függőség R vetítései az {A, B} és {B, C} sémájú relációkra Kaptunk 2 hamis sort is, mivel a B mindkét sorban 2 volt.

24. Az előzőleg normalizált (3NF-re hozott) tabla NINCS BCNF-ben. • Kurzustartas(kurzusID, szakID, profID, teremID, időpont) • Mivel 1 tanár 1 időben NEM tarthat órát 2 teremben, érvényes a következő funkcionális függőség • (teremID, időpont)→(kurzusID, szakID, profID) • Mivel a bal oldal NEM szuperkulcs, ezért a következő lehetőségeink vannak: • UNIQUE-nek definiáljuk a (teremID, időpont) párost • A relációt felbontjuk a következő relációkra • TeremBeosztás(teremID, időpont, kurzusID) • Kurzustartás(kurzusID, szakID, profID )

25. {filmcím, év, stúdióNév,elnök, elnökCím} Feltételezzük a következő funkcionális függőségeket: filmcím, év→stúdióNév(1)stúdióNév→elnök(2) elnök→elnökCím(3) jobb oldalon nincs filmcím és év (2) és (3) megsértik a BCNF-t. {filmcím,év}+=(1){filmcím,év,stúdióNév}+=(2){filmcím,év, stúdióNév,elnök}+=(3){filmcím,év,stúdióNév,elnök,elnökCím} stúdióNév→elnök (3) stúdióNév→elnök, elnökCím {filmcím, év, stúdióNév}{stúdióNév, elnök, elnökCím} Az első relációban filmcím, év→stúdióNév áll fenn, BCNF A második relációban stúdióNév a kulcs stúdióNév→elnök elnök→elnökCím – megszegi a BCNF feltételt {filmcím, év, stúdióNév} {stúdióNév, elnök} {elnök, elnökCím}

26. Sziklaszilárd Bank tárolja a nála vezetett számlák adatait, a számláknál a következő információkat vesszük figyelembe: • Ügyfél Adatok (személyi szám, név, cím, státus), Számlaszám, Egyenleg. • A számlák lehetnek betéti számlák és folyószámlák. • Az ügyfeleknek tetszőleges számlájuk lehet. • A számlaszámok teljes mértékben azonosítanak egy számlát. • Egy számla több ügyfélhez is tartozhat • Minden ügyfél rendelkezik egyedi személyi számmal • Az egyes számlákat a bank egy meghatározott fiókja vezeti • A fiókok adatai: FiókNév, Cím és Fiókvezető • Két bankfióknak nem lehet ugyanaz a neve

27. 1NF: BankFiók(Fióknév, cím, vez_szám, számlaszám, egyenleg, tipús) Ügyfél(személyiszám, név, cím, státus, számlaszám) 2 NF fióknév→fiókcím fióknév→vezérszám fióknév→fiókcím,vezérszám számlaszám→egyenleg számlaszám→tipús számlaszám→egyenleg, tipús számlaszám→Fióknév, fiókCím, vez_szám BankFiók(Fióknév, cím, vez_szám, számlaszám, egyenleg, tipús) 2 NF-ben van, mert a többi attribútum mind függ a számlaszámtól személyiszám→név, cím, státus

28. Ügyfél(személyiszám, név, cím, státus, számlaszám) személyiszám→név, cím, státus Az ügyfél reláció felbontása, hogy megfeleljen a 2NF-nek Ügyfél1(személyiszám, név, cim, státus) Ügyfél2(személyiszám, számlaszám) 3 NF BankFiók(Fióknév, cím, vez_szám, számlaszám, egyenleg, tipús) Fióknév→(Cím, vez_szám) tranzitív függőség áll fenn (számlaszám)→Fióknév→(Cím, vez_szám) ezért felbontjuk BankFiók1(Fióknév, Cím, vez_szám) BankFiók2(számlaszám, egyenleg tipús, Fióknév)

29. Többértékű függőségek: Az attribútumfüggetlenségből származó redundancia Többértékű függőség: A→→B Ha ismerjük egy (vagy több) tulajdonság értékét, akkor mindig meg tudom határozni egy másik tulajdonság értékeinek HALMAZÁT.

30. SzínészLakhely(név, város, utca, filmcím, gyártév) Reláció felbontása, hogy 4NF-ben legyen Lakcímek{név, város, utca} Szereplő{név, cím, év}

31. tanfszám→tanfnév, előadó hszám→hnév

32. Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda

33. Sapientia - Erdélyi Magyar Tudományegyetem (EMTE)Csíkszereda • Összefoglaló kérdések • 1NF • 1NF-re alakítás • Parciális függőségek • 2 NF definició • 2NF-re való hozás • 3NF definíció • Boyce-Codd normálforma • Sziklaszilárd bank adatbázisának normalizálása