Relaatiotietokantojen peruskäsitteet
Aloitetaan tutustuminen relaatiotietokantojen peruskäsitteisiin. Luodaan ensimmäiseksi pieni esimerkkitietokanta. Työntekijärekisteriin halutaan tallentaa yrityksen työntekijöiden etunimi, sukunimi ja työntekijänumero. Näitä kolmea tallennettavaa tietoa kutsutaan kentiksi (engl. field). Kenttä on pienin osanen, josta alkaa rakentua varsinainen tietokantamme.
Etunimi |
|
Kaikki tarvitsemamme kentät muodostavat yhdessä yhden rivin eli tietueen (record).
TyontekijaID | Etunimi | Sukunimi |
| | |
Monta tietuetta taasen muodostavat yhden taulun (table).
Tyontekija
TyontekijaID | Etunimi | Sukunimi |
| | |
| | |
| | |
| | |
Kentät
Kenttiä luotaessa niille määritellään yleensä seuraavanlaisia ominaisuuksia:
Kentän nimi (engl. fieldname).
Tietotyyppi (engl. datatype), joka voi olla numeerinen, alfanumeerinen (teksti), päivämäärä ja/tai kellonaika, looginen tai bittijono.
Maksimipituus ja mahdollinen tarkkuus (engl. maximum length, precision).
Onko tieto pakollinen (engl. required).
Tarkistukset syötettävän tiedon oikeellisuudelle tai sallittujen arvojoukkojen joukko (engl. validity checks).
Oletusarvo (engl. default value), jota käytetään jätettäessä kenttä tietoja syötettäessä tyhjäksi.
Syöttömaski (engl. Input Mask), jolla kuvataan kenttään kelpuutettavat merkit ja niiden muoto.
Kentän nimeksi kannattaa valita jokin selkeä ja kuvaava nimi. Nimessä kannattaa kuitenkin välttää välilyöntejä, erikoismerkkejä ja skandinaavisia kirjaimia (å,ä,ö).
Tietotyypit ja niiden nimet vaihtelevat hieman eri tietokantaohjelmistojen välillä. Eri tyypeistä kerrotaan tarkemmin kirjan SQL-osuuden yhteydessä.
Kentän maksimipituutta määriteltäessä pitää olla erityisen tarkkana. Hyvin tyypillinen virhe on varata tilaa liian vähän esimerkiksi sukunimien tallentamiseen. Lähes aina kannattaa siis varata mieluummin liian paljon tilaa kuin liian vähän. Kaikkihan tietävät kuinka ärsyttäviä ovat paperilomakkeet, joiden allekirjoituskenttään ei mahdu kirjoittamaan omaa nimeään. Tietokoneella vastaavassa tilanteessa ei voi edes yrittää kaventaa käsialaansa.
Tietokantaa suunnitellessa tulee päättää, valitaanko kiinteämittainen vai vaihtuvamittainen kenttä. Kiinteämittaisessa kentässä varataan aina saman verran tilaa riippumatta siitä, kuinka paljon siihen tallennettava tieto oikeasti tarvitsee. Vaihtuvamittaisessa taas tilanvaraus muuttuu tarpeen mukaan kuitenkaan ylittämättä määriteltyä maksimipituutta. Kiinteämittainen kenttä on tietokantaohjelmiston kannalta helpompi ja kevyempi vaihtoehto mutta sen huonona puolena saattaa olla tilahukka. Kiinteän kentän pohjalta ohjelmiston on helpompi varata tarvitsemansa muisti- ja levytila sekä laskea eri tietueiden tai kenttien paikkoja muistissa. Toisaalta tietokanta suunnitellaan ihmisen ehdoilla ihmisen käytettäväksi. Tietokone ei hikoile, mutta käyttäjä kyllä osaa vääntää esiin tuskanhikeä, kun levytila täyttyy tai haluttu tieto ei mahdukaan sille varattuun kiinteään tilaan. Kenttäkohtaisesti on siis tarkkaan harkittava, ottaako käyttöön kiinteä- vai vaihtuvamittaisen kentän.
Oikeellisuustarkistuksien avulla voidaan määrätä numeerisille kentille sallittu arvoväli (esim. luvut yhdestä kymmeneen) tai jokin laskennallinen tarkistus (esim.> 100). Useimmissa ohjelmistoissa voidaan määritellä myös jonkinlainen syöttömalli. Oikeellisuustarkistuksien määrittely on aina hyvin tuotekohtaista.
Mahdollisimman moni kenttä kannattaa määritellä pakolliseksi tai käyttää oletusarvoa. Tällöin vältetään NULL-ongelmia. NULL on erityinen merkintätapa puuttuvalle arvolle. NULL ei tarkoita välilyöntiä tai nollaa. Jos kentälle ei syöttövaiheessa anneta mitään arvoa, niin sen sisällöksi tulee NULL. Se tarkoittaa lähinnä tuntematonta arvoa eli sen arvo voisi periaatteessa olla mikä tahansa tai ei mikään.
Taulut ja tietueet
Tietue koostuu yhdestä tai useammasta kentästä. Taulun yhtä riviä kutsutaan tietueeksi.
Taulu taas sisältää useampia tietueita tai ei yhtään tietuetta (tyhjä taulu). Tauluja nimettäessä on hyvä noudattaa samoja periaatteita kuin kenttien nimeämisessä. Vältetään siis välilyöntejä, erikoismerkkejä ja skandinaavisia kirjaimia (å,ä,ö).
Avaimet (keys)
Jokaiselle taululle pitää määritellä perusavain (engl. primary key), joka yksilöi kyseisen taulun sisältämät tietueet. Jokaisella suomalaisella on oma yksilöllinen henkilötunnuksensa. Vastaavasti täytyy jokaisella taulussa olevalla tietueella olla jokin kenttä tai kenttien yhdistelmä, jollaista ei ole yhdelläkään toisella samassa taulussa olevalla tietueella. Jokaisella tietueella pitää olla yksilöllinen l. uniikki (engl. unique) perusavaimen arvo eli kahta samaa perusavaimen arvoa ei taulussa saa olla.
Perusavain muodostetaan yhdestä tai useammasta taulun kentästä. Useammasta kuin yhdestä sarakkeesta muodostuvaa perusavainta kutsutaan yhdistetyksi avaimeksi (engl. composite key). Perusavain ei saa puuttua eli saada arvoa NULL.Tätä kutsutaan perusavaimen eheydeksi (engl. entity integrity).
Tietokannan hallintajärjestelmä pitää taulun järjestyksessä perusavaimen mukaan. Järjestetyn taulun käsitteleminen on huomattavasti tehokkaampaa kuin järjestämättömän. Toimintaa voi hyvin verrata tavallisen puhelinluettelon käyttämiseen: Puhelinluettelon sisältämät tiedot on järjestetty nimien mukaan eli nimi toimii hieman vastaavassa tarkoituksessa kuin perusavain toimisi tietokannassa. Tietyn ihmisen puhelinnumeron etsiminen puhelinluettelosta onnistuu hyvin näppärästi. Jos nimet olisivat puhelinluettelossa sattumanvaraisessa järjestyksessä, hakeminen olisi huomattavasti hankalampaa ja hitaampaa.
Tauluissa esiintyy myös muunlaisia avaimia kuin perusavaimia. Joitakin kenttiä määritetään toissijaisiksi avaimiksi (engl. secondary key, secondary index). Tietokanta pitää automaattisesti yllä järjestysindeksiä avainkentistä. Haettaessa taulun tiedot järjestettynä jonkin avainkentän mukaan tietokantaohjelma pystyy toteuttamaan haun tehokkaammin kuin, jos kyseessä olisi tavallinen kenttä. Toissijaisiksi avaimiksi määritellään juuri tällaisia kenttiä, joiden mukaan tietoja halutaan mahdollisesti järjestellä. Esimerkiksi sukunimi-kenttä voisi olla tällainen. Toissijaiset avaimet siis nopeuttavat taulusta haettavan tiedon järjestämistä. Toisaalta ne hieman hidastavat päivitys- ja poisto-operaatioita, koska näiden tapahtumien yhteydessä täytyy päivittää myös avainkenttiin liittyvät järjestysindeksitiedot.
Viite-eheys (Referential Integrity)
Taulun kentistä voidaan viitata jonkin toisen taulun perusavaimeen tai toissijaiseen avaimeen. Tällaista yhteyttä kutsutaan viite-eheydeksi (engl. referential integrity) ja kyseinen kenttä määritellään yleensä myös avaimeksi, jota kutsutaan viiteavaimeksi (engl. foreign key). Viite-eheys määrää, että jokaista viittaavassa taulussa (”lapsi”) esiintyvää viiteavaimen arvoa pitää vastata sama perusavaimen arvo viittauksen kohteena olevassa taulussa (”äiti”). Yritettäessä syöttää Työntekijä-tauluun (”lapsi”) arvoja, joita vastaavia ei ole Osasto-taulussa, viite-eheys pakottaa syöttämään jotain kelvollista ennen kuin syöttö voidaan hyväksyä. Sama tapahtuu, jos Projekti-taulun projektipäällikkö-kenttään yritetään syöttää arvoa jota ei löydy Työntekijä-taulun työntekijäID-kentästä. Viite-eheys ei kuitenkaan koske NULL-arvoja, joten yleensä on syytä kieltää NULL-arvojen esiintyminen kyseisessä lapsi-taulun kentässä.
Kuva 1 Viite-eheydet
Seuraavissa taulukoissa näkyy edellisessä kuvassa määriteltyjen viite-eheyksien mukaisen tietokannan esimerkkisisältöä.
Osasto
OsastoID | Osastonimi |
| |
| |
| |
Työntekijä
TyöntekijaID | Etunimi | Sukunimi | Osasto |
| | | |
| | | |
| | | |
| | | |
Projekti
ProjektiID | ProjektiNimi | Projektipäällikkö |
| | |
| | |
”Äiti”-taulussa olevia arvoja voidaan kuitenkin yrittää muuttaa tai poistaa. Jos muuttaminen tai poistaminen on aiheuttamassa viite-eheyden rikkoutumisen, on olemassa kolme perusperiaatetta joiden mukaan muutoksia voidaan tehdä. Nämä periaatteet ovat seuraavat:
Viiteavaimen nollaus (engl. NULLify). Kaikki äiti-taulusta poistuvaan tai äiti-taulussa muuttuvaan perusavaimeen viittaavat lapsi-taulun viiteavaimet muutetaan NULLeiksi. Soveltuu vain kenttiin, joilta ei ole kielletty puuttuvaa arvoa (NOTNULL).
Oletusarvon asettaminen (engl. set default). Kaikki äiti-taulusta poistuvaan tai äiti-taulussa muuttuvaan perusavaimeen viittaavat lapsi-taulun viiteavaimet saavat oletusarvonsa. Tämä edellyttää, että äiti-taulusta löytyy oletusarvoa vastaava tietue ellei oletusarvoksi ole määritelty NULL.
Johdannaismuutos (engl. cascade). Kaikki äiti-taulusta poistuvaan perusavaimeen viittaavat lapsi-taulun viiteavaimet ja vastaavat tietueet poistetaan. Äiti-taulun muuttuvaan perusavaimeen viittaavan lapsi-taulun viiteavaimen arvo muutetaan uutta äiti-taulun perusavainta vastaaviksi.
Rajoitettu muutos (engl. restricted). Vain sellaisia äiti-taulun perusavaimia, joihin ei ole viittauksia lapsi-tauluista, voidaan poistaa tai muuttaa.
Esimerkkitietokannassa on kaikissa viite-eheyksissä voimassa johdannaismuutos. Muutetaan Tuotanto-osaston TuotantoID:ksi 10. Viite-eheyden määritysten mukaan kaikki Työntekijä-taulussa olevat kentät, joissa on viitattu kyseiseen Osasto-taulun tietueeseen, päivittyvät myös.
Osasto
OsastoID | Osastonimi |
| |
| |
| |
Työntekijä
TyöntekijaID | Etunimi | Sukunimi | Osasto |
| | | |
| | | |
| | | |
| | | |
Samoin jos muutamme TyöntekijäID-kentän arvon 1 arvoksi 15, muuttuvat vastaavat numerot Projekti-taulun Projektipäällikkö-kentässä.
Työntekijä
TyöntekijaID | Etunimi | Sukunimi | Osasto |
| | | |
| | | |
| | | |
| | | |
Projekti
ProjektiID | ProjektiNimi | Projektipäällikkö |
| | |
| | |
Käyttäjien kommentit