Harjoitus: Johdatus normalisointiin

Tämä on viimeinen kurssin ohjattu harjoitus. Edellisissä ohjatuissa harjoituksissa suunniteltiin, määriteltiin ja toteutettiin yksinkertainen relaatiotietokanta opiskelijarekisterin ylläpidolle yliopistossa.

Tässä harjoituksessa tehdään viimeinen tarkistus luodulle relaatiotietokannalle tarkastamalla sen normalisointia.

Yleensä seuraamalla Luvussa 3.2 esitettyjä transformointisääntöjä saadaan jo valmiiksi hyvin normalisoituja relaatioita (yleensä 3NF tai jopa BCNF). 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ä harjoitus on jatkoa Harjoitukselle 3.1. Tee ensin Harjoitus 3.1 ennen kuin aloitat tämän harjoituksen.

Ennen harjoituksen aloittamista tutustu seuraaviin lukuihin:

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

Huomautus

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

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

Tehtävä

Jatketaan Harjoituksessa 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

# openRiippuvuudet

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

Funktionaalisen riippuvuuden pikainen määritelmä

Jos joukko relaation R attribuutteja X yksilöi jonkun toisen joukon saman relaation attribuutteja Y, sanotaan, että relaatiossa pätee funktionaalinen riippuvuus

{X} -> {Y}

Funktionaalisessa riippuvuudessa X on määräävä attribuuttijoukko. Sanotaan, että "Attribuutit Y riippuvat funktionaalisesti attribuuteista X" tai "Y on funktionaalisesti riippuvainen X:stä". Yleisemmin voi sanoa, että "X yksilöi Y:n".

Toisin sanoen jokaisella monikolla, jossa attribuuteilla X on tietty arvo a, on attribuuteilla Y korkeintaan yksi arvo b.

Toisin sanoin relaation attribuutin X jotakin arvoa vastaa korkeintaan yksi attribuutin Y arvo. Esimerkiksi potilastietokannassa henkilötunnusta vastaisi aina yksi henkilö, eli henkilö on funktionaalisesti riippuvainen henkilötunnuksesta. Vastaavasti henkilön nimen perusteella ei välttämättä saisi selville yhtä tiettyä 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 useita 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 merkinnässä siis tarvitaan sekä attribuutin X että attribuutin Z arvo, jotta voidaan yksilöidä attribuutin Y arvo.

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

{X} -> {Y}: attribuutti Y on funktionaalisesti riippuva attribuutista X
{X} -> {A, B, C}: attribuutit A, B ja C ovat funktionaalisesti riippuva attribuutista X
- Tämä tarkoittaa samaa kuin {X} -> {A}, {X} -> {B} sekä {X} -> {C}
{X, Z} -> {Y}: attribuutti Y on funktionaalisesti riippuva 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äy onni. Pääsimme tapaamiselle yliopiston hallinnon kanssa, jossa he tarkensivat seuraavia asioita opiskelijoista ja yliopistosta:

Jotkut opiskelijat saattavat jakaa sähköpostiosoitteensa. Esimerkiksi jotkut pariskunnat tekevät 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 eli samannimisiä opiskelijoita.
Kurssikoodi on yleensä merkkijono, esim. "ITKA2004".
Eri laitoksilla saattaa olla samannimisiä kursseja eri kurssikoodilla, esim. "TJTA3001 Kandidaattiseminaari" ja "TEKA005 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.1 esitettyjen päättelysääntöjen perusteella johtaa tai yksinkertaistaa).

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.

# ex51-step1

Normaalimuotojen tarkastelu

# openNormalisointi

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

Opiskelija:

{id} -> {sähköpostiosoite, osoite, aloitusvuosi, 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ä apusäännöt.
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)

Relaatio on 1NF:ssä, jos se täyttää relaation vaatimukset:

Relaatiolle tulee olla valittuna sopiva perusavain.
Monikoiden (eli rivien) järjestyksellä ei saa olla merkitystä tiedon merkityksen kannalta.
Jokaisella attribuutilla (eli sarakkeella) tulee olla yksi selkeä tietotyyppi. Erityyppisen datan sekoittaminen samassa attribuutista on kielletty.
Jokainen attribuutti on atominen, eli ei sisällä montaa arvoa.

Toisin sanoin relaatio on 1NF:ssä, jos mikään relaation attribuutti ei sisällä listoja ja niillä on selkeät tietotyypit.

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.

# ex51-nf1

Toinen normaalimuoto (2NF)

Relaatio on 2NF:ssä, jos se täyttää seuraavat vaatimukset:

Relaatio on 1NF:ssä.
Mikään relaation perusavaimeen kuulumaton attribuutti ei riipu ainoastaan perusavaimen osasta, vaan koko perusavaimesta.

Toisin sanoen, jos on voimassa funktionaalinen riippuvuus {X} -> {Y}, jossa X:ssä on relaation perusavaimeen kuuluvat attribuutit ja Y ei kuulu perusavaimeen, niin X:stä ei voi ottaa pois perusavaimeen kuuluvia attribuutteja niin, että funktionaalinen riippuvuus säilyisi. Tällaista funktionaalista riippuvuutta kutsutaan täydeksi funktionaaliseksi riippuvuudeksi.

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

Relaatio on 1NF:ssä
Jos relaation perusavaimessa on vain yksi attribuutti, relaatio on 2NF:ssä.
Jos relaatiossa ei ole perusavaimeen kuulumattomia attribuutteja, relaatio on 2NF:ssä.
Jos relaation perusavaimessa on useampi attribuutti, niin perusavaimesta ei tule voida poistaa jotain attribuuttia niin, että jokin attribuutti olisi edelleen riippuvainen tällaisesta "perusavaimen osasta".

# ex51-nf2

Kolmas normaalimuoto (3NF)

Relaatio on 3NF:ssä, jos kaikki seuraavat vaatimukset pätevät:

Relaatio on 2NF:ssä.
Kaikki relaation perusavaimeen kuulumattomat attribuutit riippuvat vain, ainoastaan ja kokonaan perusavaimesta eikä mistään muusta attribuutista.

Poikkeus: Jos funktionaalisen riippuvuuden määräävä attribuuttijoukko on relaation avainehdokas (eli se yksilöi kaikki relaation attribuutit), se 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.

# ex51-nf3

Boyce/Codd-normaalimuoto (BCNF)

Relaatio on BCNF:ssä, jos seuraavat vaatimukset pätevät:

Relaatio on 3NF:ssä.
Kaikki relaation attribuutit riippuvat vain, ainoastaan ja kokonaan jostain avainehdokkaasta eikä mistään muusta attribuutista.

Toisin sanoen, funktionaaliset riippuvuudet, jossa määräävä joukko ei ole relaation jokin avainehdokas, on kielletty.

Toisin sanoin relaatio on BCNF:ssä, jos se on 3NF:ssä ja jokaisessa funktionaalisessa riippuvuudessa on "mainittuna" jokainen relaation attribuutti (joko "nuolen" oikealla tai vasemmalla puolella).

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ä.

# ex51-bcnf

Neljäs normaalimuoto (4NF)

Relaatio on 4NF:ssä, jos kaikki seuraavat vaatimuksen täyttyvät:

Relaatio on BCNF:ssä.
Relaation kaikkien epätriviaalien moniarvoisten riippuvuuksien määräävä attribuuttijoukko tulee olla relaation avainehdokas.

Toisin sanoen, jos relaatiossa on moniarvoinen riippuvuus {X} ->> {Y}, niin attribuuttijoukon X tulee olla relaation avainehdokas.

Huomaa, että 4NF:ään liittyy moniarvoinen eikä funktionaalinen riippuvuus. Moniarvoinen riippuvuus {X} ->> {Y} käytännössä tarkoittaa, että riippuvuutta määräävän joukon X arvoa voi vastata yksi tai useampi tietty 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 transformoinnissa on virheitä. Tästä syystä 4NF kannattaa tarkistaa aina varmuudeksi.

# ex51-4nf

Lopuksi

Kun olet valmis, voit jatkaa eteenpäin muihin harjoitustehtäviin.

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