Osa 4

Tietokannan normalisointi

Tietokannan normalisoinnin tavoitteena on vähentää tietokantatauluissa esiintyvää toisteista tietoa. Pääpiirteittäin tavoite on sama kuin käsiteanalyysissä — lopulta jokainen taulu liittyy vain tiettyyn käsitteeseen ja taulun attribuutit liittyvät vain kyseisen taulun esittämään käsitteeseen.

Tietokannan normalisointi tehdään olemassaolevalle tietokannalle. Tietokannan normalisoinnissa etsimme tietokantatauluista tunnettuja epäkohtia, jonka jälkeen näitä epäkohtia korjataan.

Tunnettujen epäkohtien tunnistaminen tapahtuu askeleittain valmiita "säännöstöjä" eli normaalimuotoja seuraamalla.

Ensimmäinen normaalimuoto

Tietokantataulu on ensimmäisessä normaalimuodossa mikäli se täyttää seuraavat ehdot:

  1. Tietokantataulun sarakkeen arvot eivät sisällä listoja eli jokainen arvo on yksittäinen.
  2. Taulun sarakkeet eivät saa muodostaa toistuvia ryhmiä.
  3. Sarakkeen arvojen tulee olla saman tyyppisiä eli sarakkeessa ei saa olla eri tyyppisiä arvoja.
  4. Jokaisen sarakkeen nimen tulee olla uniikki. Samassa tietokantataulussa ei saa olla kahta saman nimistä saraketta.
  5. Sarakkeiden järjestyksen ei tule vaikuttaa tietokantataulun toimintaan.
  6. Tietokantataulussa ei saa olla kahta täsmälleen samanlaista riviä.
  7. Rivien järjestyksen ei tule vaikuttaa tietokantataulun toimintaan.

Tarkastellaan alla olevaa henkilöitä sisältävää tietokantataulua. Tietokantataulussa on jokaiselle henkilölle tunnus (id), nimi, sekä puhelinnumeroita.

idnimipuhelinnumerot
1Larry555-1024, 555-2048
2Moe555-0512, 555-0256, 555-0128
3Curly555-0001, 555-0002, 555-0004

Taulu rikkoo ehtoa 1. "Tietokantataulun sarakkeen arvot eivät sisällä listoja eli jokainen arvo on yksittäinen.". Sarakkeessa puhelinnumerot on useita puhelinnumeroita listana.

Ensimmäinen korjaus ylläolevaan tietokantatauluun on eritellä puhelinnumerot erillisiksi sarakkeikseen (tehty alla).

idnimipuh1puh2puh3
1Larry555-1024555-2048
2Moe555-0512555-0256555-0128
3Curly555-0001555-0002555-0004

Tämä ei kuitenkaan ole hyvä ratkaisu. Puhelinnumerojen hakeminen on tietokantataulusta monimutkaista, jonka lisäksi neljännen (tai viidennen, tai kuudennen, ...) puhelinnumeron lisääminen vaatisi tietokantataulun rakenteen muuttamista siten, että tauluun lisättäisiin uusia sarakkeita.

Yllä oleva ratkaisu, missä tietokantataulu sisältää kolme erillistä saraketta puhelinnumeroille rikkoo myös ensimmäisen normaalimuodon ehtoa 2. "Taulun sarakkeet eivät saa muodostaa toistuvia ryhmiä." sillä puhelinnumerot muodostavat toistuvan ryhmän.

Parempi korjaus ongelmaan on luoda erillinen tietokantataulu puhelinnumeroille. Luodaan tietokantataulut Henkilo ja Puhelinnumero. Henkilön ja puhelinnumeron välillä on yhden suhden moneen -yhteys, eli yhteen henkilöön liittyy monta puhelinnumeroa, mutta jokainen puhelinnumero liittyy yhteen henkilöön.

Taulussa Henkilo on nyt vain henkilön yksilöivä pääavain sekä henkilön nimi.

idnimi
1Larry
2Moe
3Curly

Tauluun puhelinnumero lisätään rivin yksilöivä pääavain, viiteavain henkilöön, ja puhelinnumero. Periaatteessa puhelinnumerokin voisi olla taulun pääavain.

idhenkilo_idpuhelinnumero
11555-1024  
21555-2048
32555-0512
42555-0256
.........

Huom! Esimerkkimme ei tarkoita sitä, että puhelinnumero tulee aina eristää omaksi käsitteekseen. Päinvastoin, puhelinnumero on tyypillisesti hyvä attribuutti. Esimerkissämme henkilöllä kuitenkin voi olla monta puhelinnumeroa, jolloin muunnos tarvitaan — ensimmäinen normaalimuoto vaatii käytännössä toistuvien sarakejoukkojen eristämisen omaksi taulukseen.

Ensimmäisen normaalimuodon ehdot 3.—7. pätevät, joten tietokantataulu Henkilo on ensimmäisessä normaalimuodossa. Koska teimme uuden taulun Puhelinnumero, tulee sekin normalisoida. Tässä tapauksessa myös taulu Puhelinnumero on ensimmäisessä normaalimuodossa.

Funktionaalinen riippuvuus

Ensimmäisessä normaalimuodossa otetaan ensiaskeleet tietokannan rakenteen järkevöittämiseen. Muissa normaalimuodoissa käsite funktionaalinen riippuvuus sarakkeiden välillä on oleellinen.

Funktionaalisella riippuvuudella tarkoitetaan tilannetta, missä sarakkeen arvon perusteella voidaan selvittää (yksikäsitteinen) toisen sarakkeen arvo. Esimerkiksi sähköpostiosoitteella voidaan saada selville yksikäsitteinen sähköpostin vastaanottajan nimi.

Yleisemmin ottaen mikä tahansa sarake B on funktionaalisesti riippuvainen sarakkeesta A (A määrää funktionaalisesti B:n), jos sarakkeen A arvon perusteella voidaan yksikäsitteisesti selvittää sarakkeen B arvo. Tällöin kirjoitetaan A -> B, ja sanotaan, että "sarake B on funktionaalisesti riippuvainen sarakkeesta A".

Huom! A voi olla myös kokoelma sarakkeita!

Esimerkiksi henkilön nimi on funktionaalisesti riippuvainen henkilötunnuksesta, sillä henkilötunnuksen perusteella voidaan yksikäsitteisesti selvittää nimi. Toisaalta, henkilötunnus ei ole funktionaalisesti riippuvainen henkilön nimestä, koska useammalla henkilöllä voi olla sama nimi.

SQL-kielessä selvittäminen voi tapahtua kyselyllä SELECT DISTINCT b FROM Taulu WHERE a=tiedetty_arvo, missä avainsana DISTINCT palauttaa uniikit rivit. Jos attribuutti b on funktionaalisesti riippuva a:sta, tuottaa ylläoleva kysely joko yhden tai ei yhtään tulosriviä, mutta ei koskaan enempää. Tämän ehdon on oltava voimassa aina ja jokaiselle mahdolliselle sarakkeen a arvolle , ei vain hetkellisesti.

Tietokantataulun sarakkeiden välistä funktionaalista riippuvuutta voi tarkastella luomalla tietokantataulun sarakkeista sarake x sarake -matriisin, missä kukin solu kertoo onko sarakepari riippuvainen toisistaan. Alla on kuvattuna tietokantataulu Henkilo , jolla on sarakkeet id, nimi ja henkilotunnus. Sarake id on pääavain. Tarkastellaan mitkä sarakkeen arvot ovat funktionaalisesti riippuvaisia toisistaan?

A: id A: nimiA:henkilotunnus
B: id? ??
B: nimi ???
B: henkilotunnus ???

Tarkastellaan lausetta "Sarake B on funktionaalisesti riippuvainen sarakkeesta A (A määrää funktionaalisesti B:n), jos sarakkeen A arvon perusteella voidaan yksikäsitteisesti selvittää sarakkeen B arvo.". Sarakkeen perusteella voi aina määritellä itsensä. Esimerkiksi id -> id on aina totta ja kysely SELECT DISTINCT nimi FROM Henkilo WHERE nimi = 'esimerkki' palauttaa aina korkeintaan yhden rivin.

A: id A: nimiA:henkilotunnus
B: idkyllä ??
B: nimi ?kyllä?
B: henkilotunnus ??kyllä

Voimmeko tunnistaa nimen perusteella henkilön yksilöivän tunnisteen? Useammalla henkilöllä voi olla sama nimi, joten tämä ei pidä paikkansa.

A: id A: nimiA:henkilotunnus
B: idkyllä ei?
B: nimi ?kyllä?
B: henkilotunnus ??kyllä

Voimmeko tunnistaa henkilötunnuksen perusteella henkilön yksilöivän tunnisteen? Henkilötunnus on uniikki, joten oletetaan että kyllä (tämä pätee tosin vain Suomessa..).

A: id A: nimiA:henkilotunnus
B: idkyllä eikyllä
B: nimi ?kyllä?
B: henkilotunnus ??kyllä

Voiko yksilöivän avaimen perusteella tunnistaa henkilön nimen? Kyllä.

A: id A: nimiA:henkilotunnus
B: idkyllä eikyllä
B: nimi kylläkyllä?
B: henkilotunnus ??kyllä

Yllä olevassa esimerkissä muutama vaihtoehdoista jää kysymysmerkeiksi. Ratkaiset nämä seuraavaksi.

Toinen normaalimuoto

Tietokantataulu on toisessa normaalimuodossa jos (1) se on ensimmäisessä normaalimuodossa ja (2) tietokantataulun sarakkeet (poislukien avaimet) ovat funktionaalisesti riippuvaisia tietokantataulun (yhdellä sarakkeella määritellystä) pääavaimesta. Jos tietokantataulun pääavain on määritelty yhden sarakkeen avulla, ovat kaikki tietokantataulun sarakkeet käytännössä automaattisesti funktionaalisesti riippuvaisia pääavaimesta ja tietokantataulu on toisessa normaalimuodossa.

Mikäli tietokantataulun pääavain määritellään useamman sarakkeen avulla, tulee tietokantataulun sarakkeiden olla riippuvaisia koko tietokantataulun pääavaimesta (ei vain osasta tietokantataulun sarakkeita). Mikäli sarakkeet ovat riippuvaisia vain pääavaimen osasta, on tietokantataulussa toisteista tietoa, ja tietokantataulu tulee normalisoida.

Tarkastellaan alla olevaa tietokantataulua, joka kuvaa työntekijät sekä heidän toimistonsa. Tietokantataulu on ensimmäisessä normaalimuodossa. Tietokantataulun pääavain on muodostettu sarakkeiden tyontekija_id ja toimisto_id yhdistelmänä.

tyontekija_idtyontekija_nimitoimisto_idtoimisto_nimi
TT-1CoddT-1San Jose
TT-2BoyceT-1San Jose
TT-3ChamberlinT-2Almaden

Yllä olevassa tietokantataulussa sarake tyontekija_nimi on funktionaalisesti riippuvainen sarakkeesta tyontekija_id, ja sarake toimisto_nimi on funktionaalisesti riippuvainen sarakkeesta toimisto_id. Sarakkeet ovat funktionaalisesti riippuvaisia vain tietokantataulun pääavaimen osasta ja kuten huomaamme, tietokantataulussa on turhaa toisteisuutta.

Ratkaisuna tähän on tietokantataulun pilkkominen kahteen osaan. Toinen osa kuvaa työntekijää ja toinen osa kuvaa toimistoa.

tyontekija_idtyontekija_nimi
TT-1Codd
TT-2Boyce
TT-3Chamberlin
toimisto_idtoimisto_nimi
T-1San Jose
T-2Almaden

Nyt molemmat taulut ovat ensimmäisessä ja toisessa normaalimuodossa.

Mutta! Kadotimme tiedon työntekijöiden sijainnista! Ratkaistaan ongelma lisäämällä tietokantataulu, joka kertoo työntekijöiden toimistot.

tyontekija_idtoimisto_id
TT-1T-1
TT-2T-1
TT-3T-2

Mikäli tietokantataulun pääavain on määritelty useamman sarakkeen avulla, tulee tietokantataulun jokaisen sarakkeen olla riippuvainen koko avaimesta, eli osittaista riippuvuutta pääavaimesta ei sallita. Osittaisen riippuvuuden tapauksessa tietokantataulu pilkotaan pienempiin osiin.

Tarkastellaan vielä toista tilannetta, missä tietokantataulun pääavain on määritelty useamman sarakkeen kautta. Tarkastellaan seuraavia tietokantatauluja Asiakas, Kauppa ja Ostos. Ensimmäisessä kahdessa tietokantataulussa pääavain on id. Oletetaan, että kolmannessa taulussa pääavain on määrätty kahden viiteavaimen yhdistelmänä (vaikkei tämä todellisuudessa loisi kovin mielekästä tilannetta).

  • Asiakas ((pk) id, nimi)
  • Kauppa ((pk) id, nimi, osoite)
  • Ostos ((fk) asiakas_id -> Asiakas, (fk) kauppa_id -> Kauppa, hinta, kaupunki)

Taulut Asiakas ja Kauppa ovat ensimmäisessä ja toisessa normaalimuodossa.

Tarkastellaan seuraavaa taulua Ostos. Taulun Ostos sarake hinta kertoo ostoksen hinnan. Sarake kaupunki kertoo missä ostos tehtiin.

asiakas_idkauppa_idhintakaupunki
1114.90Helsinki
1315.20Vantaa
218.40Helsinki
3219.20Espoo
3310.40Vantaa
4112.20Helsinki
............

Kun tarkastelemme taulua Ostos, huomaamme, että tietokantataulun sarake kaupunki on funktionaalisesti riippuvainen sarakkeesta kauppa_id. Koska sarake kauppa_id on osa tietokantataulun pääavaimesta (oletamme, että pääavain muodostuu sarakkeista asiakas_id ja kauppa_id), tämä rikkoo toista normaalimuotoa.

Eräs mahdollinen ratkaisu ongelmaan on kaupungin siirtäminen tauluun Kauppa.

  • Asiakas ((pk) id, nimi)
  • Kauppa ((pk) id, nimi, osoite, kaupunki)
  • Ostos ((fk) asiakas_id -> Asiakas, (fk) kauppa_id -> Kauppa, hinta)

Nyt jokainen ylläolevista tietokantatauluista on ensimmäisessä ja toisessa normaalimuodossa.

Kolmas normaalimuoto

Kolmanteen normaalimuotoon liittyy oleellisesti käsite transitiivinen riippuvuus.

Tietokantataulu on kolmannessa normaalimuodossa jos se on toisessa normaalimuodossa ja siinä olevat sarakkeet eivät ole transitiivisesti riippuvaisia taulun pääavaimesta.

Jos tietokantataulu rikkoo kolmannen normaalimuodon, eli tietokantataulusta tunnistetaan sarakkeita, jotka ovat transitiivisesti riippuvaisia pääavaimesta, eriytetään ne omaan tauluun taulukseen. Eräs klassinen esimerkki tällaisesta tilanteesta liittyy postinumeroon — tarkastellaan seuraavaa taulua Osoite.

  • Osoite((pk) id, katuosoite, postinumero, postitoimipaikka)

Oletetaan, että taulun sisältö on seuraava.

idkatuosoitepostinumeropostitoimipaikka
1Työpajankatu 1300580Helsinki
2Työpajankatu 2 R1 C00580Helsinki
3Siltavuorenranta 1800170Helsinki
............

Yllä olevassa tietokantataulussa havaitaan funktionaalinen riippuvuus postinumero -> postitoimipaikka, eli postitoimipaikan saa selvitettyä postinumeron perusteella. Samalla kaikki sarakkeet ovat selvitettävissä taulun pääavaimen kautta, joten taulusta löytyy myös transitiivinen riippuvuus. Ratkaisu tähän on — esimerkiksi — erillinen taulu postinumeroille.

  • Osoite((pk) id, katuosoite, (fk) postinumero -> Postinumero)
  • Postinumero((pk) postinumero, postitoimipaikka)

Toistuvaan tietoon liittyviä ongelmia

Tietokannan normalisoinnin tavoitteena on tilanne, missä tietokannassa ei ole toisteista tietoa. Normalisointi johtaa yleisesti ottaen parempaan tietokannan rakenteeseen, missä tiedon lisääminen, poistaminen ja päivittäminen on mahdollista ilman suurempia ongelmia.

Tutustutaan tilanteeseen, missä tietokantataulua ei ole (täysin) normalisoitu. Tarkastellaan seuraavaa tietokantataulua sekä siihen liittyviä ongelmia.

idnimiosastojarjesto
1JaskaTTMoodi
2TelluMATMatrix
2TelluMATLimes
3SalliFYSResonanssi
3SalliFYSGeysir

Ongelmia on yleisesti liittyen kolmenlaisia: päivitysongelmia, poisto-ongelmia ja lisäysongelmia.

Esimerkki päivitysongelmasta: Mikäli Sallilla on merkittynä tietokantaan väärä osasto, tulee Sallin osasto muuttaa (vähintään) kahdelta eri riviltä tai tietokannassa oleva tieto on epäjohdonmukaista.

Esimerkki poisto-ongelmasta: Yllä ainejärjestöt ja osastot sijaitsevat samassa tietokantataulussa henkilöiden kanssa. Mikäli haluaisimme poistaa ainejärjestön Moodi, johtaisi tämä myös henkilön Jaskan poistamiseen. Samalla katoaisi tieto osastosta TT.

Esimerkki lisäysongelmasta: Oletetaan, että saraketta jarjesto ei saa jättää tyhjäksi. Mikäli tietokantaan yritetään lisätä henkilö ilman järjestöä, lisääminen ei onnistu.

Pääsit aliluvun loppuun! Jatka tästä seuraavaan osaan: