Normalisointi

1. Normalisointi • Normalisointi liittyy ”bottom-up” –menetelmään. • Normalisoinnissa aloitetaan tutkimalla attribuuttien välisiä yhteyksiä. • Aiemmin esitetyssä ER-mallinnuksessa normalisointia voidaan käyttää validointitehtäviin. • Jos hyvin käy, niin normalisoinnin avulla lopullinen malli täyttää asetetut rajoitukset ja vältytään datan turhalta redundanssilta, päällekkäisyydeltä. tMyn

2. Normalisoinnin tavoitteet lyhykäisesti: • Relaatiotietokannasta saadaan relaatiomallin mukainen • Relaatioiden ja kohdealueen objektien välille pyritään saamaan läheinen rakenteellinen vastaavuus • Minimoidaan tietokantaan sisältyvää käsitteellistä ja talletettavista tiedoista aiheutuvaa redundanssia • Tietokannan päivitysten yhteydessä mahdollisten anomalioiden välttäminen (lisäys-, päivitys- ja poistoanomalia) • Relaatioiden riveistä pyritään tekemään lisäysten, poistojen ja muutosten kannalta itsenäisiä kokonaisuuksia • Tietokannan mahdollisimman suuri rakenteellinen joustavuus tulevien muutosten mahdollistamiseksi tMyn

3. Normalisointi perustuu normaalimuotoihin • Normaalimuodot ovat asteittain tiukkenevia ehtoja, jotka relaatioiden on täytettävä • Relaatio on tietyssä normaalimuodossa, mikäli se täyttää tietyt rajoitusehdot 5 NF 4 NF BCNF 3 NF 2 NF 1 NF KAIKKI RELAATIOT tMyn

4. Normalisoitu malli kuvaa hyvin todellisuutta ja on stabiili. • Kun tauluja suunnitellaan, niin helposti voi syntyä datan redundanssia (=sama tieto esiintyy taulussa turhan usein). • Tästä redundanssista voi tulla hankalia ongelmia esim. sellaisessa tilanteessa jossa tietoa päivitetään (=update anomalies) . • Otetaan esimerkkinä taulut Henkilokunta(hloNro, sNimi, asema, palkka, tpNro), Toimipiste(tpNro, tpOsoite) ja hkToimipiste(hloNro, sNimi, asema, palkka, tpNro, tpOsoite), kuva 1. tMyn

5. Henkilokunta Toimipiste hkToimipiste Kuva 1. Hankaluudet tiedon päivittämisessä. tMyn

6. Taulu hkToimipiste on vaihtoehtoinen esitys kahdelle taululle Henkilokunta ja Toimipiste. • Huomataan, että taulussa hkToimipiste on redundanssia. tpOsoite esiintyy aina silloin kun henkilökuntaan kuuluva esitellään. • Katsotaan seuraavaksi tyypillisiä ongelmia (update anomalies) hkToimipiste-tyylisissä tauluissa. • Lisätään tietoa tauluun (insertion anomaly). Kun lisätään uusi henkilö hkToimipiste-tauluun, joudutaan samalla lisäämään toimipisteeseen liittyvät tiedot. Jos olisi erikseen taulu Toimipiste, niin tällaista ongelmaa ei olisi. tMyn

7. Toinen ongelma samassa (insertion anomalies) tilanteessa: Yritetään lisätä uusi toimipiste hkToimipiste-tauluun. Siinä ei vielä ole henkilökuntaa. Siispä pitäisi antaa arvo NULL sarakkeeseen hloNro. Se on kuitenkin taulun hkToimipiste perusavain (primary key)! • Tiedon lisäämiseen liittyvä sivuvaikutus siis ilmenee silloin, kun jonkin kohteen ilmentymän lisääminen edellyttää toisen kohteen ilmentymän lisäämistä. • Kyseistä tietoa ei ole voitu tallentaa aiemmin, koska tiedot ovat toisistaan siten riippuvaisia, että ne on talletettava samanaikaisesti. tMyn

8. Entäpä jos poistetaan rivi SA9 (Minna Auvinen) taulusta hkToimipiste? Seurauksena olisi se, että toimipisteen B007 (Mannerheimintie 15, Helsinki) tiedot häviäisivät samalla kertaa (deletion anomaly)! • Tiedon poistamiseen liittyvä sivuvaikutus ilmenee siis silloin, kun yhden kohdetyypin jonkin ilmentymän poistaminen aiheuttaa tahattomasti toisen kohteen tietojen tuhoutumisen. tMyn

9. Kolmantena ongelmana taulussa hkToimipiste on toimipisteen attribuutin arvon muuttaminen. Jospa vaikka toimipiste B003 Kuopiossa muuttaa Makrillitie 6:sta Makrillitie 5:een. Tuo tieto tulisi muuttaa kolmeen kohtaan taulussa hkToimipiste (modification anomaly)! • Yllä olevan perusteella voidaan jo todeta, että tauluilla Henkilokunta ja Toimipiste on haluttavampia ominaisuuksia kuin mitä on taululla hkToimipiste. • Yksi iso taulu jaettiin kahteen pienempään. Tällöin kaikki se tieto mikä löytyi yhdestä isosta löytyy edelleenkin näistä kahdesta pienestä (lossless join). tMyn

10. Samaten kaikki ne rajoitteet (constraint) jotka ovat olleet voimassa alkuperäisessä taulussa voidaan asettaa voimaan näissä uusissa, pienemmissä tauluissa (dependency preservation). • Normalisointiin liittyy käsite funktionaalinen riippuvuus (Functional Dependency) ja se on attribuuttien välinen suhde. Jos tietyn attribuutin X arvon perusteella voidaan osoittaa tai johtaa toisen attribuutin Y arvo, voidaan sanoa, että X:n arvo määrittää Y:n arvon ja Y on funktionaalisesti riippuvainen X:stä. Tällöin X on Y:n determinantti (determinant), X->Y. tMyn

11. Esim. henkilotunnus->henkilonNimi rekisterinumero->autonMerkki • Yleistys: X, Y->Z • Esim. tilausNro, tuoteNro->tilausMaara tMyn

12. Taulusta Henkilokunta voidaan poimia vaikkapa hloNro SL21. Tuo hloNro-tunnuksen avulla voidaan selvittää kyseisen henkilön asema (johtaja). Siis attribuutti asema on funktionaalisesti riippuvainen attribuutista hloNro. Sitä vastoin toiseen suuntaan tämä ei pidä paikkansa: henkilökunnan joukossa voi olla monta sellaista henkilöä, joiden asema on johtaja. • Kun on löydetty funktionaaliset riippuvuudet relaatiosta, niin sen jälkeen voidaan näiden pohjalta valita perusavain relaatioon – ja määritellä muutenkin tarvittavat eheysvaatimukset. tMyn

13. Attribuuttien hloNro ja asema välinen yhteys (relationship) on 1:1 (one-to-one), siis kutakin hloNro:a kohti on vain yksi asema-arvo. • Sitä vastoin attribuuttien asema ja hloNro välinen suhde on 1:* (one-to-many), koska on olemassa useita hloNro-arvoja kutakin asema-arvoa-kohti. • Normalisoinnissa käytetään hyväksi relaationfunktionaalisia riippuvaisuuksia sellaisten attribuuttienvälillä joissa vallitsee 1:1 yhteys. • Toisekseen näiden riippuvuuksien tulee olla voimassa koko ajan ja niiden tulee olla ei-triviaaleja. tMyn

14. Triviaali funktionaalinen riippuvuus: Triviaalilla funktionaalisella riippuvuudella tarkoitetaan attribuutin riippuvuutta oman ylijoukkonsa (superset) kanssa. • tyontekijaID, tyontekijaOsoite-> tyontekijaOsoite tMyn

15. Miten kaikki funktionaaliset riippuvuudet löydetään elävässä elämässä? Ei luultavammin oikeastaan mitenkään! • Tavoitteena tuleekin olla löytää riittävä joukko funktionaalisia riippuvuuksia niin että ne kuvaavat relaation riittävän hyvin. tMyn

16. Normalisointi perustuu relaatioiden analysointiin perusavaimen ja funktionaalisten riippuvuuksien avulla. • Normalisoinnissa käytetään joukkoa sääntöjä, joiden avulla relaatiota voidaan testata. Näin edeten tietokanta voidaan normalisoida haluttuun normaalimuotoon. • Jos relaatio ei täytä jotakin ehtoa, niin ratkaisu saavutetaan jakamalla loogisesti itsenäiset ominaisuusryhmät omiksi relaatioikseen. • Normalisointi etenee askel kerrallaan. Eteenpäin mentäessä relaatiot tulevat yhä vahvemmiksi ja tunnottomammiksi sivuvaikutuksille (anomalies). tMyn

17. Ennen normalisointiprosessin alkua oletetaan, että kullakin relaatiolla on perusavain ja kaikki funktionaaliset riippuvuudet ovat tiedossa (hip!). • Aluksi olisi syytä tehdä ER-malli ratkaistavasta ongelmasta, mutta ohitetaan se tässä ja aloitetaan raakadatasta, siis vaikkapa jostakin excel-taulusta. • Raakadatan hieno nimi on Unformalized form (UNF), kuva 2. tMyn

18. asiakasVuokrattavat Kuva 2. Lähdetään liikkeelle normalisoimattomasta raakadatasta. tMyn

19. 1. normaalimuoto, 1NF (First normal form), on taulu, jossa ei ole toistuvia monikkoja, rivejä. Jokaisella monikolla on ainakin yksi yksilöllinen attribuutti. Jokaisessa relaation alkiossa on vähintään ja enintään yksi arvo. • Raakadatasta voidaan yleensä muuntaa 1. normaalimuotoon täydentämällä jokaisen taulun rivin ja sarakkeen risteykseen yksi arvo (flattening the table)… tämä aiheuttaa valitettavasti redundanssia. • Valitaan perusavaimeksi sarakkeet asNro ja kohdeNro (composite key), kuva 3. tMyn

20. asiakasVuokrattavat Kuva 3. 1 NF, 1. normaalimuoto datasta. tMyn

21. 2. normaalimuoto sisältää käsitteen täydellinen funktionaalinen riippuvuus (Full Functional Dependency). Osittainen funktionaalinen riippuvuus (Partial Functional Dependency) tulee vastaan hieman myöhemmin. Y on täydellisesti funktionaalisesti riippuvainen X:stä jos pätee sekä se, että Y on funktionaalisesti riippuvainen X:stä ja se, että jos X koostuu kahdesta tai useammasta attribuutista, niin silloin minkä tahansa attribuutin tiputtaminen pois X:stä aiheuttaa sen, että Y ei enää olekaan funktionaalisesti riippuvainen X:stä. tMyn

22. Otetaan esimerkki funktionaalisesta riippuvuudesta • taulusta Henkilokunta: • hloNro, sNimi -> tpNro Henkilokunta tMyn

23. On oikein sanoa, että kukin arvo (hloNro, sNimi) liittyy yksittäiseen arvoon tpNro. tpNro ei kuitenkaan ole täydellisesti funktionaalisesti riippuvainen (hloNro, sNimi)-arvosta, koska tpNro on myös funktionaalisesti riippuvainen (hloNro, sNimi):n osajoukosta, nimittäin hloNro:sta. • Normalisointi 2. normaalimuotoon (2NF, Second Normal Form) kohdistetaan tauluihin, joissa perusavain koostuu ainakin kahdesta attribuutista (composite key). Jos taulun perusavain koostuu vain yhdestä attribuutista, niin se on silloin vähintäänkin 2. normaalimuodossa. tMyn

24. Jos taulu ei ole 2NF-muodossa, niin voi tulla ongelmia. Esim. taulussa asiakasVuokrattavat: jos haluttaisiin muuttaa kohteen PG4 vuokra-arvoa, niin se tulisi tehdä kahteen paikkaan. • Taulu on 2. normaalimuodossa, jos jokainen ei-perusavain –attribuutti on täysin riippuvainen perusavaimesta. • Se voi kuitenkin myös olla riippuvainen toisesta, ei-perusavain –attribuutista (nk. transitiivinen riippuvuus). • Tavoitteena on siis tässä vaiheessa osittaisten riippuvuuksien (partial functional dependencies) poistaminen. tMyn

25. Partial Functional Dependency: an attribute is dependent on only a part of a primary key. • A Transitive Functional Dependency is a type of functional dependency in which the value in a non-key field is determined by the value in another non-key field and that field is not a candidate key. tMyn

26. Osittainen funktionaalinen riippuvuus (Partial Functional Dependency): Nyt siis perusavaimen osa (composite primary key) riittää identifioimaan osan relaation perusavaimeen kuulumattomista attribuuteista. • Transitiivinen funktionaalinen riippuvuus (TransitiveFunctional Dependency): Määrääkö attribuutti A attribuutin C suoraan (ilman välittäviä attribuutteja) vai välillisesti ominaisuuden B kautta? Attribuutti C on transitiivisesti riippuva attribuutista A, jos ja vain jos A->B ja B->C ja lisäksi EI ole voimassa B->A eikä C->A. tMyn

27. Tutkitaan taulua kuvassa 1, asiakasVuokrattavat. Olkoot perusavaimena sarakkeet asNro ja kohdeNro. • Kiinnostuksen kohteina ovat funktionaaliset riippuvuudet, fr1-fr4. tMyn

28. fr1 (Perusavain) fr2 (Osittainen riippuvuus) (Osittainen riippuvuus) fr3 fr4 (Transitiivinen riippuvuus) Kuva 4. Funktionaaliset riippuvuudet asiakasVuokrattavat-taulussa valitun perusavaimen (asNro, kohdeNro) suhteen. tMyn

29. Huomataan, että attribuutti asNimi on osittain riippuvainen perusavaimesta, siis ainoastaan osasta asNro, kts fr2. • Attribuutit kohdeOsoite, vuokra, omistNro ja omistNimi ovat osittain riippuvaisia perusavaimesta, siis osasta kohdeNro, kts fr3. • Attribuutit vuokrSAlk ja vuokrSLopp ovat täysin riippuvaisia perusavaimesta, kts. fr1. • Huomataan, että asiakasVuokrattavat-taulussa on myös transitiivinen riippuvuussuhde (fr4), mutta se saa olla 2 NF-muodossa. tMyn

30. Koska siis osittaisia riippuvuussuhteita löytyi, niin ne poistetaan muodostamalla uusia tauluja. • Periaate: otetaan ei-perusavainsarakkeet pois yhdessä sen osuuden kanssa perusavaimesta, jonka (joiden) suhteen nämä ei-perusavainsarakkeet ovat täydellisesti riippuvaisia. • Tämän seurauksena syntyy kolme taulua: Asiakas, Vuokrasuhde ja kohdeOmistaja, kuva 5. • Näistä kukin on 2 NF –muodossa, koska jokainen ei-perusavain –attribuutti on täydellisesti funktionaalisesti riippuvainen perusavainattribuutista. tMyn

31. Vuokrasuhde Asiakas Kuva 5a. 2 NF –muodossa olevat taulut Asiakas ja Vuokrasuhde. tMyn

32. kohdeOmistaja Kuva 5b. 2 NF –muodossa oleva taulu Omistaja. tMyn

33. Taulut ovat nyt seuraavassa muodossa (2 NF), perusavain alleviivattuna: Asiakas(asNro, asNimi) Vuokrasuhde(asNro, kohdeNro, vuokrSAlk, vuokrSLopp) kohdeOmistaja(kohdeNro, kohdeOsoite, vuokra, omistNro, omistNimi) tMyn

34. Vaikka taulut ovat nyt 2 NF –muodossa, niin vielä voi tulla ongelmia taulujen sisältöjä muutettaessa (update anomalies). • Jos esim. taulussa kohdeOmistaja joudutaan modifioimaan omistNimi-sarakkeessa nimeä Kalle Savolainen, niin se tulisi tehdä kahdelle riville. • Jos muutos tehtäisiin vain jompaankumpaan riviin, niin seurauksena olisi ristiriitainen taulun sisältö (inconsistent state). • Edellä kuvattu uhkatilanne johtuu transitiivisesta riippuvuudesta (Transitive Functional Dependency). tMyn

35. Transitiivinen riippuvuus on voimassa seuraavassa tilanteessa: Olkoot A, B ja C relaation attribuutteja. Jos B on funktionaalisesti riippuvainen A:sta (A -> B) ja C on funktionaalisesti riippuvainen B:stä (B -> C) niin edellä mainittujen ehtojen vallitessa C on transitiivisesti riippuvainen A:sta attribuutin B kautta. Tämä sillä ehdolla, että A ei ole funktionaalisesti riippuvainen B:stä eikä C:stä. tMyn

36. Kun muistellaan alussa olevaa taulua, hkToimipiste, niin huomataan funktionaaliset riippuvuudet hloNro -> tpNro ja tpNro -> tpOsoite. • Tässä transitiivinen riippuvuus hloNro -> tpOsoite esiintyy sarakkeen tpNro kautta. hkToimipiste tMyn

37. 3. normaalimuoto, 3NF (Third normal form), on taulu, joka täyttää 1NF ja 2NF ehdot, ja lisäksi yksikään ei-perusavain –attribuutti ei ole transitiivisesti riippuvainen perusavaimesta. • Palataan nyt tauluihin Asiakas, Vuokrasuhde ja kohdeOmistaja. • Tauluissa Asiakas ja Vuokrasuhde ei ole transitiivisia riippuvuuksia ja ne ovat valmiiksi 2NF ehdot täyttäviä. • Niinpä ne ovat valmiiksi myös 3NF ehdot täyttävässä muodossa. tMyn

38. Taulussa kohdeOmistaja on transitiivinen riippuvuus omistNro -> omistNimi. Muistetaan, että kyseisen taulun perusavain on kohdeNro. • Poistetaan tämä riippuvuus (ja siis päästään 3NF muotoon) luomalla kaksi taulua, vuokrattavatKohteet ja Omistaja, kuva 6. tMyn

39. vuokrattavatKohteet Omistaja Kuva 6. Taulun kohdeOmistaja muuttaminen 3NF –muotoon luomalla kaksi uutta taulua. tMyn

40. Voidaan sanoa, että 3 NF=normalisoinnin tavoitetaso. • Yhteenvetona 3 NF –taulut ovat: Asiakas(asNro, asNimi) Vuokrasuhde(asNro, kohdeNro, vuokrSAlk, vuokrSLopp) vuokrattavatKohteet(kohdeNro, kohdeOsoite, vuokra, omistNro) Omistaja(omistNro, omistNimi) tMyn