Tehtävä: Johdatus normalisointiin

Tämä on viimeinen kurssin ohjattu tehtävä. Edellisissä ohjatuissa tehtävissä suunniteltiin, määriteltiin ja toteutettiin yksinkertainen relaatiotietokanta opiskelijareksisterin ylläpidolle yliopistossa.

Tässä tehtävässä tehdään viimeinen tarkistus luomallemme relaatiotietokannalle tarkastamalla sen normaalimuotoa. Normaalimuotojen tarkastelu käytännössä tarkoittaa, että tutkitaan, onko samaa dataa toistettu turhaan eri tietokantatauluissa.

Yleensä seuraamalla Luvussa 3.2 esitettyjä transformointisääntöjä saadaan heti jotain normaalimuotoa täyttävä tietokanta. Tämä tehtävä onkin varsin lyhyt, sillä onnistuneen suunnittelun ja transformoinnin seurauksena opiskelijarekisterimme on jo hyvin pitkälti normalisoitu 🙂 Varmistetaan kuitenkin asia vielä erillisellä tarkastelulla.

Tämä tehtävä on jatkoa Tehtävälle 3.1. Tee ensin Tehtävä 3.1 ennen kuin aloitat tämän tehtävän. Tehtävän voi tehdä, vaikket ole tehnyt Tehtävää 4.1.

Ennen tämän tehtävän aloittamista, tutustu seuraaviin asioihin:

Luku 5.1 ja 5.2: erityisesti funktionaalisen riippuvuuden määritelmä
Luku 5.3: 1NF, 2NF, 3NF, BCNF, 4NF määritelmät pääpiirteissään

Jos huomaat virheitä tai sinulla on kysyttävää tehtävistä, ota yhteyttä kurssin opettajiin

lähettämällä sähköpostia osoitteeseen itka2004-k24-opet@tim.jyu.fi
laittamalla viestiä kurssin Teams-ryhmään tai osallistumaalla ohjauksiin: Teams ja etäohjaukset

Tärkeimmät muutospäivitykset listataan tähän:

Tehtävä

Jatketaan Tehtävässä 3.1 työstettyä opiskelijatietokantaa. Tällä kertaa tarkastellaan tietokannan normaalimuotoja.

Seuraa alla olevissa osioissa olevia vaiheita ja täydennä alla oleviin palautuslaatikoihin vastauksia sen perusteella. Mallivastaus löytyy jokaisen tehtävän yhteydessä.

Funktionaaliset riippuvuudet

# openOsa1

Ennen normalisoinnin tarkastelua tulee meidän etsiä relaatiokaavasta funktionaalisia riippuvuuksia. Funktionaalista riippuvuutta määritellään Luvussa 5.2. Voit vielä katsoa pikaisen määrittelyn alta:

Funktionaalisen riippuvuuden pikainen määritelmä

$\{X\} \rightarrow Y$

Yllä oleva luetaan "Y on funktionaalisesti riippuvainen X:stä". Toisin sanoen, relaatiossa R pätee funktionaalinen riippuvuus $\small \{X\} \rightarrow Y$ , jossa {X} on määräävä attribuuttijoukko, josta Y riippuu, jos {X}:n arvon ollessa a relaation R monikoilla, on Y:n arvo jokaisella vastaavalla monikolla aina b. Tarkastellaan funktionaalista riippuvuutta alla olevan taulukkona esitetyn relaation henkilö avulla.

Yksinkertaistettuna tämä tarkoittaa, että relaation attribuutin X jotakin arvoa vastaa korkeintaan yksi attribuutin Y arvo. Esimerkiksi potilastietokannassa jotakin henkilön henkilötunnusta vastaa aina yksi henkilö, eli henkilö on funktionaalisesti riippuvainen henkilötunnuksesta. Vastaavasti henkilön nimen perusteella ei välttämättä saisi selville yhtä henkilöä, sillä nimellä voi olla kaimoja. Eli henkilö ei ole yleensä funktionaalisesti riippuvainen hänen nimestään.

Funktionaalinen riippuvuus merkitään seuraavasti:

{X} -> Y

Jos attribuutti X yksilöi muitakin attribuutteja, ne voidaan merkitä seuraavasti:

{X} -> {A, B, C}

Yllä olevassa siis A, B sekä C ovat funktionaalisesti riippuvaisia X:stä. Jos attribuutin X lisäksi tarvitaan vielä joku attribuutti, jotta toista attribuuttia voidaan yksilöidä, voidaan merkitä seuraavasti:

{X, Z} -> Y

Yllä olevassa siis tarvitaan sekä attribuutin X että attribuutin Z arvo, jotta voidaan tunnistaa korkeintaan yksi attribuutin Y arvo.

Kertauksena: tässä tehtävässä käytetään seuraavia merkintöjä:

{X} -> Y: attribuutti Y on funktionaalisesti riippuvainen attribuutista X
{X} -> {A, B, C}: attribuutit A, B ja C ovat funktionaalisesti riippuvaisia attribuutista X
- Tämä on sama kuin {X} -> A, {X} -> B sekä {X} -> C
{X, Z} -> Y: attribuutti Y on funktionaalisesti riippuvainen attribuuttien X ja Z yhdistelmästä

Funktionaalisten riippuvuuksien tarkastelu edellyttää, että kohdealuetta tunnetaan tarpeeksi hyvin. Viimeistään tässä vaiheessa joudutaan tekemään lisäoletuksia.

Opiskelijatietokannan tapauksessa meille kävi valtava onni. Pääsimme tapaamiselle yliopiston hallinnon kanssa, jossa he tarkensivat seuraavia asioita opiskelijoista ja yliopistosta:

Jotkut opiskelijat saattavat jakaa sähköpostiosoitteensa. Esimerkiksi pariskunnat näyttävät tekevän niin.
Opiskelijat eivät kuitenkaan jaa puhelinnumeronsa, eli se on aina yksilöllinen.
Samassa osoitteessa voi asua useampi opiskelija (esim. soluasunto).
Yliopistossa voi olla kaimoja.
Kurssikoodi on yleensä merkkijono, esim. "ITKA2004".
Eri laitoksilla saattaa olla samannimisiä kursseja eri kurssikoodilla, esim. "TJTA3001 Kandidaattiseminaari" ja "TERA005 Kandidaattiseminaari".
Saman laitoksen kurssien nimet nimet ovat kuitenkin yksilöllisiä.
Laitosten nimet halutaan pitää yksilöllisenä. Eli siis esimerkiksi koko yliopistossa saa olla vain yksi "Matematiikan laitos".
Yliopiston tasolla tiedekunnilla saa olla vain yksilöllinen nimi. Eli ei ole kahta "Informaatioteknologian tiedekuntaa".
Opiskelija voi tenttiä saman kurssin monta kertaa.

Näiden perusteella voidaan lähteä tarkastamaan funktionaalisia riippuvuuksia opiskelijatietokannassa.

Käydään nyt läpi jokainen relaatio ja etsitään kaikki epätriviaalit funktionaaliset riippuvuudet (eli kaikki ne, joita ei voi Luvussa 5.2.1 esitettyjen päättelysääntöjen perusteella johtaa tai yksinkertaistaa enempää).

Tallenna vastauksesi lopuksi. Voit katsoa mallivastauksen tallentamisen jälkeen käyttämällä tehtävän yläpuolella olevaa "Näytä mallivastaus" -painiketta.

Voit irrottaa palautuslaatikon kelluvaksi painamalla "Kelluta" -painiketta.

# t51_o1

Normaalimuotojen tarkastelu

# openOsa2

Edellisen perusteella löysimme siis seuraavat epätriviaalit funktionaaliset riippuvuudet:

Opiskelija:

{id} -> {sähköpostiosoite, osoite, aloitusviosi, etunimi, sukunimi, laitos_id}

Kurssi:

{kurssikoodi} -> {nimi, laajuus, laitos_id}
{laitos_id, nimi} -> {kurssikoodi, laajuus}

Laitos:

{id} -> {nimi, tiedekunta_id}
{nimi} -> {id, tiedekunta_id}

Tiedekunta:

{id} -> {nimi}
{nimi} -> {id}

Opiskelija_puhelinnumerot:

{puhelinnumero} -> {opiskelija_id}

Tenttii:

{opiskelija_id, kurssi_kurssikoodi, päivämäärä} -> arvosana

Katsotaan läpi relaatioiden normalisointia näiden riippuvuuksien perusteella.

Tee seuraavasti:

Katso kunkin normaalimuodon määritelmä alla sekä "selkokielinen" selitys.
Tarkista, että jokainen relaatio täyttää määritelmän. Tässä voit esimerkiksi piirtää riippuvuudet paperille ja katsoa ne yksitellen läpi.
Vastaa määritelmän alla olevaan monivalintaan.

Ensimmäinen normaalimuoto (1NF)

Määritelmä: relaatio on 1NF:ssä, jos sen kaikki attribuutit ovat atomisia ja sopivaa tyyppiä.

Toisin sanoin relaatio on 1NF:ssä, jos mikään relaation attribuutti ei ole arvoltaan lista.

Käytännössä kaikki ER-kaaviosta oikein transformoidut relaatiot ovat aina 1NF:ssä. Tämä on niin, sillä moniarvoiset attribuutit transformoidaan omaksi relaatioksi.

Siispä tässä voidaan todeta heti, että opiskelijatietokanta täyttää 1NF:n.

# t51_o2k1

Toinen normaalimuoto (2NF)

Määritelmä: relaatio on 2NF:ssä, jos se on 1NF:ssä ja relaation kaikki perusavaimeen kuulumattomat attribuutit ovat täysin funktionaalisesti riippuvia relaation perusavaimesta.

Täydellä funktionaalisella riippuvuudella tarkoitetaan funktionaalista riippuvuutta $\small \{X\} \rightarrow Y$, jossa attribuuttijoukkoa {X} ei voida jakaa osiin niin, että funktionaalinen riippuvuus $\small \{X\} \rightarrow Y$ säilytetään.

Toisin sanoin relaatio on 2NF:ssä, jos kaikki seuraavat asiat pätevät:

Relaatio on 1NF:ssä
Jos relaation perusavaimessa on vain yksi attribuutti, kaikki muut attribuutit riippuvat perusavaimesta
Jos relaatiossa ei ole perusavaimeen kuulumattomia attribuutteja, relaatio on 2NF:ssä
Jos relaation perusavaimessa on useampi attribuutti ja perusavaimesta poistaisi jonkun attribuutin, mikään perusavaimeen kuulumaton attribuutti ei olisi enää riippuvainen perusavaimesta

# t51_o2k2

Kolmas normaalimuoto (3NF)

Määritelmä: relaatio on 3NF:ssä, jos se on 2NF:ssä ja mikään relaation perusavaimeen kuulumaton attribuutti ei ole transitiivisesti riippuvainen relaation perusavaimesta.

Poikkeus: Jos 3NF:ää rikkovan transitiivisen riippuvuuden määräävä attribuuttijoukko on relaation avainehdokas, riippuvuus ei riko 3NFää.

Toisin sanoin relaatio on 3NF:ssä, jos kaikki seuraavat pätevät:

Relaatio on 2NF:ssä
Mikään perusavaimeen kuulumaton attribuutti ei riipu jostain toisesta perusavaimeen kuulumattomasta attribuutista
- Poikkeus: jos perusavaimeen kuulumaton attribuutti on avainehdokas (eli se yksilöi relaation kaikki muut attribuutit), niin silloin tämä funktionaalinen riippuvuus on sallittu.

Huomaa erityisesti mainittu poikkeus. Esimerkiksi opiskelijatietokannassa relaatiossa Laitos pätevät seuraavat funktionaaliset riippuvuudet:

{id} -> {nimi, tiedekunta_id}
{nimi} -> {id, tiedekunta_id}

nimi nyt ei kuulu perusavaimeen, joten periaatteessa riippuvuus {nimi} -> tiedekunta_id on transitiivinen eikä siten täyttäisi 3NF. Kuitenkin nimi on avainehdokas, eli se yksilöi relaation kaikki muut attribuutit (samalla tavalla kuin perusavain). 3NF:ssä tällainen riippuvuus on sallittu ja siten Laitos täyttää 3NF:n vaatimukset.

# t51_o2k3

Boyce/Codd-normaalimuoto (BCNF)

Määritelmä: relaatio on BCNF:ssä, jos se on 3NF:ssä ja jokaisen funktionaalisen riippuvuuden määräävä attribuuttijoukko {X} on myös relaation superavain.

Toisin sanoin relaatio on BCNF:ssä, jos se on 3NF:ssä ja jokaisessa funktionaalisessa riippuvuudessa on "mainittuna" jokainen relaation attribuutti (joko määräävässä tai määritetyssä joukossa).

Jos jossain funktionaalisessa riippuvuudessa ei ole kaikkia attribuuttia mainittuna, tarkista vielä päättelysäännöillä (erityisesti transitiivisuussääntö), saako funktionaaliselle riippuvuudelle kaikki attribuutit mukaan.

Esimerkiksi opiskelijatietokannassa relaation Kurssi funktionaaliset riippuvuudet ovat

{kurssikoodi} -> {nimi, laajuus, laitos_id}
{laitos_id, nimi} -> {kurssikoodi, laajuus}

Huomaa, että kummassakin funktionaalisessa riippuvuudessa ovat mukana relaation kaikki neljä attribuuttia (kurssikoodi, nimi, laajuus sekä laitos_id). Siispä relaatio on BCNF:ssä.

# t51_o2k4

Neljäs normaalimuoto (4NF)

Määritelmä: relaatio on 4NF:ssä, jos se on BCNF:ssä ja jokaisen ei-triviaalin moniarvoisen riippuvuuden määräävä attribuuttijoukko {X} on relaation superavain.

Huomaa, että 4NF:ään liittyy moniarvoinen eikä funktionaalinen riippuvuus. Moniarvoinen riippuvuus MVD{X} -> Y käytännössä tarkoittaa, että riippuvuutta määräävän joukon X arvoa voi vastata yksi tai useampi attribuutin Y arvo.

4NF on hyvin harvoin rikottu muoto. 4NF liittyy pääsääntöisesti vain niihin tilanteisiin, jossa yhdellä relaatiolla yritetään mallintaa useampaa N-M -suhdetta. Jos relaatio on tehty transformoimalla oikeaoppisesti ER-kaavio, 4NF:n päävaatimus pätee aina.

Toisin sanoin relaatio on 4NF:ssä jos se on BCNF:ssä ja jos yksi seuraavista pätee:

Relaatio ei mallinna N-M -suhdetta
Relaation mallintaa vain yhden N-M -suhteen
Relaatio mallintaa enemmän kuin yhden N-M -suhteen, mutta suhteet riippuvat toisistaan

Käytännössä ER-kaavioita oikeaoppisesti transformoimalla on sinänsä mahdotonta saada relaatio, joka olisi BCNF:ssä muttei 4NF:ssä. Näin voi kuitenkin käydä esimerkiksi jos transformoi monimutkaisia tertiäärisiä N-M -suhteita tai jos transfromoinnissa on virheitä. Tästä syystä 4NF kannattaa tarkistaa aina varmuudeksi.

# t51_o2k5

Lopuksi

Huh! Tämä oli kurssin viimeinen ohjattu tehtävä. Enempää opiskelijatietokantaa ei enää tule 🙂.

Tämä tehtävä oli "pieni" loppukevennys, jolla kerrattiin hieman, mitä kaikkea edellisissä tehtävissä saatiin aikaan. Samalla tutustuttiin hieman normaalimuotoihin ja niihin liittyviin yleisiin nyrkkisääntöihin.

Pyydän lopuksi jättämään hieman palautetta tämän kurssin ohjatuista tehtävistä (Tehtävä 2.1, 3.1, 4.1 ja 5.1). Miltä koko ajatus ylipäätään vaikutti? Miten koit hyötyväsi ohjatuista tehtävistä (tai koitko ollenkaan)? Ehdotuksia ohjattujen tehtävien jatkokehitykselle (jos et ole maininnut niitä jo edellisien tehtävien palautteessa)?

# palaute