Tietokannan suunnittelu
- Luentotaltiointi
- Mikä on tietokanta?
- Laaja osoitekirja
- Taulukoista relaatiotietokantoihin
- Tietokannan suunnittelun vaiheet
- Käsitteellinen mallintaminen (ER-malli)
- ER-kaavioiden piirtäminen
Tällä luennolla selvitetään mikä on tietokanta. Käydään läpi myös tietokantojen suunnittelun perusteita ER-mallinnuksen avulla.
Mikä on tietokanta?
- Tietokanta on kokoelma yhteenliittyvää tietoa (data)
- Tietokanta on loogisesti yhtenäinen kokoelma tietoa jolla on jokin merkitys.
- Tietokanta on suunniteltu, rakennettu ja täytetty tiedolla jotain tiettyä tarkoitusta varten. Sillä on jokin tarkoitettu käyttäjäryhmä ja joitain ennaltalaadittuja ohjelmia joita käyttäjät käyttävät.
Mitä tietokannalta halutaan?
Halutaan...
- Säilöä tietoa turvallisesti.
- Muuttaa tietoja helposti.
- Hakea tietoa tietyin ehdoin.
- Tehdä yhteenvetoja ja laskutoimituksia.
- Jakaa tietoa.
Laaja osoitekirja
Ratkaisu 1: Paperi ja kynä.
- Halpa ja yksinkertainen ratkaisu
- Hankala etsiä, päivittää, jakaa ja laajentaa
- Kenen puhelinnumero olikaan 123456?
- Miten korjata osoite jos edellinen on kirjoitettu tussilla? Entäs jos tila ei riitä?
- Hankalaa lisätä uutta informaatiota esim. sähköpostiosoitteita
- Turhaa tiedon toistoa
- Kalle Kuoma, opiskelijakatu 13 b 2, 40100 Jyväskylä
- Olli Opiskelija, opiskelijakatu 13 b 2, 40100 Jyväskylä
- Korvataan toinen osoite viitteellä. Entä jos toinen muuttaakin muualle?
Muistetaanko päivittää myös toiselle uusi osoite? Mitä jos osoitekirjassa oleva iso
kommuuni hajoaa?
- Kalle Kuoma, opiskelijakatu 13 b 2, 40100 Jyväskylä
- Olli Opiskelija, kts. Kallen osoite
- Varmuuskopiointi?
Ratkaisu 2: Mappi välilehdillä.
- Voidaan helposti pitää järjestyksessä esim. nimen mukaan
- Voidaan helpommin lisätä, poistaa ja päivittää
- Edelleen samat ongelmat kuin ensimmäisessä vaihtoehdossa. Hakeminenkaan ei onnistu helposti kuin nimen mukaan.
Ratkaisu 3: Tekstitiedosto tai dokumentti.
Suppea osoitekirja Word-dokumenttina
- Vapaasti muotoiltavissa
- Rajoittamaton koko
- Helposti kopioitavissa
- Jaettavissa
- Tekstihaku
- Edelleen ongelmana tiedon toistuminen ja eheys.
- Entä jos vaatimukset kasvavat? Tiedosto voi kasvaa hyvin suureksi.
- Hakeminen hidastuu
- Haku: "Opiskelija" antaakin tulokseksi kaikki joiden osoitteessa lukee "opiskelijankatu" eikä pelkästään "Olli Opiskelija"
Ratkaisu 4: Tavallinen taulukko
Suppea osoitekirja Excel-taulukkona
- Jaetaan tieto kenttiin esim. nimi, osoite ja puhelinnumero.
- Nyt haku on helpompaa kun voimme hakea nimen tai osoitteen perusteella. Tämä vaatii ohjelmalta jo jonkinlaista hakulogiikkaa! Valitaan sarake ja etsitään. Miten haetaan useamman hakuehdon perusteella?
- Miten saadaan järjestykseen?
- Miten saadaan yhteenvetoja? Esim. kuinka monta kaveria asuu Jyväskylässä.
- Edelleen tieto toistuu. Päivittäminen työlästä.
Ratkaisu 5: Edistynyttä taulukkolaskentaa.
- Käytetään hakuihin Excelissä erikoissuodatusta.
- Järjestäminen onnistuu sopivilla työkaluilla.
- Kaavioilla ja ristiintaulukoinneilla saadaan yhteenvetoja. Funktioilla voidaan tehdä laskutoimituksia.
- Tietojen rajaukset (onnistuu osittain taulukkolaskennassakin).
- Miten voidaan estää esim. tyhjät solut jos tieto on välttämätön?
- Miten rajataan tiettyyn numeroväliin tai listaan vaihtoehdoista?
- Miten estetään samaa henkilöä tule kahteen kertaan?
- Tietojen päivittämisen ongelma.
- Miten yhdistetään tiedot?
- Entä jos yhdellä henkilöllä voi olla monta osoitetta?
- Monen käyttäjän ongelmat.
- Mitä jos haluamme, että monta käyttäjää voi katsella tietoja ja päivittää niitä?
- Entä jos halutaan rajata tiedon käyttö- ja päivitysoikeuksia eri käyttäjille?
- Entä jos haluammekin lisätä kokonaan uudenlaisia tietoja?
- Laajan tietomäärän aiheuttamat ongelmat. Haku hidastuu.
Ratkaisu 6: Tietokanta
Yleisiä vaatimuksia tietokannalta:
- Kukin tieto tallennetaan tietokannassa vain yhteen paikkaan eli ei esiinny turhaa toistuvuutta.
- Tietoja pystytään hakemaan joustavasti erilaisin perustein, myös sellaisin, joita ei tietokantaa suunnitellessa ole pystytty ennakoimaan.
- Tietokannan rakenteellinen muuttaminen on joustavaa.
- Hyväksikäyttö ja sovellusohjelmat ovat riippumattomia tietojen fyysisestä talletusrakenteesta: tietoriippumattomuus.
Taulukoista relaatiotietokantoihin
Toiston välttäminen
Osoitteet ja nimet on erotettava toisistaan.
- Isoja perheitä, jotka muuttavat usein. Jonkun osoite saattaa unohtua päivittää.
- Olli Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
- Oili Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
- Oili Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
- Väinö Opiskelija, Opiskelijaboksintie 13 b 2, 40500 Jyväskylä
- Säästetään tilaa jos voidaan tehdä päivitys vain yhteen paikkaan. Päivittäminen helpottuu.
- Ei enää ongelmia joulukortteja lähetettäessä: yksi kortti jokaiseen osoitteeseen.
Ratkaisu
- Kaksi taulukkoa: yksi henkilöille ja yksi osoitteille
- Kuinka yhdistetään henkilö ja osoite?
Myös postinumero ja postitoimipaikka voitaisiin laittaa eri taulukoihin.
Käsitteitä
Useimmat tietokannat ovat relaatiotietokantoja, joissa tieto tallentaan tällaisiin taulukoihin. Käsitteitä:
- Tietue (engl. record)
- Taulun yksi rivi. Sisältää useita kenttiä.
- Kenttä (engl. field)
- Tallennettava tieto. Pienin yksittäinen tiedon osanen. Kenttien nimiä voidaan ajatella taulukon sarakeotsikkoina.
- Taulu (engl. table)
- Yhteenkuuluvat tietueet (joissa yhtä monta samanlaista kenttää) muodostavat yhden taulun.
Tietokannan on kuitenkin huomioitava vielä monia asioita. Mitä jos haluamme pitää kirjaa henkilöistä, jotka asuvat useammassa osoitteessa (esim. varsinainen ja tilapäinen)? Miten erotamme kaksi samannimistä henkilöä?
Viitteet ja suhteet
Kukin tietue voi liittyä 0-n muuhun tietueeseen. Tietokannan rakenteen takia on järkevä määrätä millaisia nämä suhteet ovat, liittyykö joku tietue toiseen ollenkaan, liittyykö se aina yhteen vai voiko tietue liittyä useampaankin. Seuraavassa muutama esimerkkitapaus:
- Henkilö voi omistaa useita asuntoja. Asunnossa voi olla monta henkilöä. (Monen suhde moneen, M-to-M)
- Henkilöllä voi olla useita puhelinnumeroita. Tietyn puhelinnumeron omistaa kuitenkin yksi henkilö. (Yhden suhde moneen, 1-to-M)
- Lähiosoite kuuluu yhteen postiosoitteeseen. Postiosoitteessa voi olla monta lähiosoitetta. (Monen suhde yhteen, M-to-1)
- Yksi henkilö voi olla aviossa vain yhden henkilön kanssa (Yhden suhde yhteen, 1-to-1)
Suhde riippuu siitä millaista tietoa haluamme tallentaa! Esim. jossain tietokannassa voi olla tarpeen, että yhteen henkilöön liittyy monta asuntoa (halutaan pitää kirjaa omistuksista), mutta voi olla yhteen henkilöön riittää liittää yksi asunto (halutaan pitää kirjaa vain pääasiallisesta asuinpaikasta).
Ratkaisu
- Henkilöllä voi olla useita puhelinnumeroita (Yhden suhde moneen)
Henkilo Hetu Etunimi Sukunimi 000000-0000 Olli Opiskelija 000001-0001 Muuli Hevostenhoitaja 000002-0002 Reimo Alaselkä 000003-0003 Masi Muurari 000004-0004 Taavi Ruudintekijä 055555-0555 Oili Opiskelija 060606-0606 Väinö Opiskelija Puhelinnumero Suuntanro Numero Hetu 014 123456 000000-0000 044 1234567 000000-0000 0400 436637 000001-0001 050 532235 000002-0002 041 324211 000003-0003 045 112234 000004-0004 0500 145155 055555-0555 +358 400 112234 060606-0606 050 3215351 000000-0000 - Lähiosoite kuuluu yhteen postiosoitteeseen. Postiosoitteessa voi olla monta lähiosoitetta. (Monen suhde yhteen)
Lahiosoite LahiosoiteID Paikannimi Numeroosa Postinumero 1 Opiskelijaboksintie 13 b 2 40500 2 Pohjoisenkatu 245 99999 3 Työntekijänkuja 6 a 6 00000 4 Kommuunikatu 134 h 8 00000 Postiosoite Postinumero Postitoimipaikka 40500 Jyväskylä 99999 Nullula 00000 Hesa - Uusi taulu, joka yhdistää omistajan ja asunnon (Monen suhde moneen).
Henkilö voi omistaa useita asuntoja ja asunnolla voi olla useita omistajia
Henkilo Hetu Etunimi Sukunimi 000000-0000 Olli Opiskelija 000001-0001 Muuli Hevostenhoitaja 000002-0002 Reimo Alaselkä 000003-0003 Masi Muurari 000004-0004 Taavi Ruudintekijä 055555-0555 Oili Opiskelija 060606-0606 Väinö Opiskelija Lahiosoite LahiosoiteID Paikannimi Numeroosa Postinumero Opiskelijaboksintie 1 13 b 2 40500 Pohjoisenkatu 2 245 99999 Työntekijänkuja 3 6 a 6 00000 Kommuunikatu 4 134 h 8 00000 Asuu Hetu LahiosoiteID 000000-0000 1 000001-0001 3 000002-0002 2 000003-0003 3 000000-0000 4 000004-0004 3 055555-0555 1 060606-0606 1
Käsitteitä
- Perusavain (engl. primary key)
- Kenttä, joka yksilöi taulussa olevan tietueen. Voi koostua useammastakin kentästä.
- Kardinaalisuus (engl. cardinality)
- Kertoo montako kohdetta voi liittyä suhteeseen. Yleensä suhteeseen liittyy kaksi kohdetta ja näiden vaihtoehdot ovat yhden suhde yhteen (1-to-1), yhden suhde moneen (1-to-M), monen suhde yhteen (M-to-1) ja monen suhde moneen (M-to-M).
Tietokantojen hyödyt
Tietokannan hallintajärjestelmä vastaa kyseisiin haasteisiin ja mahdollistaa näiden ongelmien välttämisen. Useimmat tietokantojen hallintajärjestelmät mahdollistavat:
- Tietokantojen luomisen ja muuttamisen.
- Tietojen syöttämisen ja muuttamisen.
- Tiedon hakujen ja yhteenvetojen tekemisen SQL-kyselykielellä.
- Lisätään paljon käytetyille kentille hakemisto.
- Käyttäjähallinnan.
- Näkymät. Tietojen yhdistely.
- Oikeustasot
- Monet käyttäjät. Toimintokokonaisuuksien hallinta. Tiedon varmistus.
- Useat voivat päivittää tietokannan tietoja.
- Päivitys tapahtuu aina kerralla.
- Tapahtumat eivät jakaudu osiin ja tapahtuvat peräkkäin tietyssä järjestyksessä.
- Tiedon eheys. Tarpeellinen tieto ei katoa. Viitteet eivät mene rikki.
- Tietojen yksilöinti. Tietotyypit. Tietojen rajoitukset. Pakollisuus.
Koko tietokantajärjestelmää tehdessä on suunniteltava miten tieto organisoidaan ja miten sitä voidaan käsitellä.
Tietokannan suunnittelun vaiheet
- Vaatimusten
määrittely
- Haastatteluin, kirjalliseen materiaaliin tutustumalla ja keskusteluin selvitetään järjestelmältä vaadittavat ominaisuudet. Apuna käytetään usein käyttötapaus-tekniikkaa.
- Käsitteellinen
mallintaminen
- Laaditaan kaavio, joka kuvaa tietokannan sisällön ja rakenteen.
- Käyttöliittymän suunnittelu
- Suunnitellaan sovelluksen rakenne ja sovellukseen kuuluvien näyttöjen sisältö ja rakenne
- Tietokannan looginen suunnittelu
- Kaavion pohjalta laaditaan varsinainen tietokannan rakenne
- Toteutus
- Toteutetaan edellisten osien suunnitelmat
Käsitteellinen mallintaminen (ER-malli)
Kohteet
- Kohteeseen tulee on ominaisuuskokoelman nimi.
- Kohde ei ole ominaisuus tai yksittäinen tieto!
- Kohteen yhteyteen liitetään vain kohteeseen välittömästi liittyviä tietoja (ominaisuuksia).
- Kunkin tiedon päivitys tapahtuu vain yhteen paikkaan
- Tunnistettavissa oleva asia tai tapahtuma.
- Kohdetta kuvaa usein substantiivi.
- Helposti havaittavia kohteita ovat käyttäjien puheissa esiintyvät henkilöt, esineet, tilat ja tuotteet. Hankalampia ovat käsitteelliset kohteet kuten tilaus tai sopimus.
- Kohteet ovat sellaisia, joista halutaan tallettaa tietoja pysyvästi tietokantaan. Raportit ja tulosteet eivät ole kohteita vaan tietokannan tiedoista johdettuja tulostietoja
Ominaisuudet
- Jokaisella samantyyppisellä kohteella on tiettyjä yhteisiä ominaisuuksia
- Henkilöillä on kaikilla nimi, sotu, osoite yms.
- Ominaisuus vastaa taulukossa sarakeotsikoita.
- Ominaisuus ei ole talletettava tieto!
- Ominaisuuksien joukosta valitaan avaimeksi sopivat
- Avaimen pitää olla yksilöivä, uniikki.
- Avain voi olla myös yhdistelmä useammasta kentästä.
- Avain voi olla juokseva numero omana ominaisuutena.
- Ominaisuus voi olla yksi- tai moniarvoinen. Moniarvoiset ominaisuudet (multivalued attributes) irrotetaan tällä kurssilla omiksi kohteikseen.
- Ominaisuudet voivat olla johdettuja
- esim. tilausten kokonaislukumäärä lasketaan yksittäistilausten kappalemääristä.
- esim. ikä lasketaan syntymäajasta.
- Ominaisuuden arvojen kannattaa olla yksiosaisia, osat kannattaa erottaa aina omiksi ominaisuuksikseen. Esim. etunimi ja sukunimi.
- Muista ominaisuuksista johdettavia tietoja (derived attributes) ei kannata laittaa ominaisuuksiksi (täydelliseen ER-kaavioon ne merkitään katkoviivalla).
- Jokainen ominaisuus saa arvonsa tietystä arvojoukosta.
- Sallitaanko ominaisuuksille tyhjät arvot.
- Sallitaanko tuntemattomat arvot.
- Ominaisuus voi liittyä myös suhteeseen.
Suhteet
- Suhdetta kuvaa usein verbi.
- Vähintään kahden kohteen välillä vallitseva riippuvuus.
- Suhde voi merkitä olemassaoloa, toiminnallista suhdetta tai tapahtumaa
- Keksi, jos mahdollista, sellainen kuvaava sana, joka toimii molempiin suuntiin.
- Suhteen aste määräytyy suhteeseen liittyvien kohteiden lukumäärän mukaan.
- Suhteen kardinaalisuus:
- Yhden suhde yhteen (one-to-one, 1-to-1)
- Yhden suhde moneen (one-to-many, 1-to-M) (monen suhde yhteen (many-to-one, M-to-1))
- Monen suhden moneen (many-to-many, M-to-M)
ER-kaavioiden piirtäminen
Piirto-ohjelmana voi käyttää mitä tahansa kaavioiden piirtämiseen sopivaa työkalua tai vaikka Exceliä. Järkevintä on kuitenkin käyttää erityisesti ER-kaavioita varten suunniteltuja työkaluja koska ne helpottavat kaavion muuntamista tietokannaksi. Tällä kurssilla käytetetään Erkka-ohjelmaa.
- Kohteet
- Jokainen kohde esitetään suorakulmiona jonka sisällä lukee kyseisen kohteen kohdetyyppi.
- Ominaisuudet
- Esitetään ellipseillä jotka on liitetty jatkuvalla viivalla kohteeseen tai suhteeseen. Ellipsin sisällä lukee ominaisuuden nimi.
- Avaimina toimivat ominaisuudet alleviivataan.
- Hieman epästandardisti voidaan ominaisuus merkitä myös kohteen sisältävään suorakulmioon.
- Suhteet
- Jokainen suhde esitetään timanttikuviona jonka sisällä lukee suhteen nimi.
- Suhteeseen liittyvät kohteet liitetään siihen jatkuvalla viivalla joista jokaisen kohdalla lukee 1 tai M riippuen siitä onko kyseessä yhden suhde yhteen, yhden suhde moneen vai monen suhde moneen.
- Lue kaaviota esimerkiksi seuraavasti:
- "Yksi henkilö voi asua enintään yhdellä paikkakunnalla" (ei pakollinen tieto)
- "Henkilö asuu tietyllä paikkakunnalla" (pakollinen tieto)
- "(Yhdellä) paikkakunnalla voi asua monta henkilöä."
Esimerkkikaavio
Suunnitellaan tietokanta pienen yrityksen tietojen ja projektien hallintaan. Yksinkertaistettu versio tietokannan vaatimusmäärittelystä on seuraavanlainen:
- Työntekijöistä talletetaan nimi (etunimi, toinen nimi, sukunimi), puhelinnumerot, palkka ja tieto mahdollisista lapsista.
- Jokainen työntekijä toimii jollakin osastolla.
- Projekteissa työskentelee yksi tai useampia työntekijöitä. Sama työntekijä voi olla yhtäaikaa monessa projektissa.
- Jokaisella projektilla on yksi projektipäällikkö.
- Projekteissa rakennetaan monenlaisia laitteita. joihin tarvitaan tietty määrä tietynlaisia osia. Osia projekteille toimittavat monet eri toimittajat, joiden yhteystiedot pitää tallettaa järjestelmään. Järjestelmän pitää sisältää tieto siitä, mitä osia ja miltä toimittajalta on toimitettu millekin projektille.
- Jotkut osat koostetaan itse muista osista. Tietokannan pitää siis sisältää tieto myös osien koostumuksesta.
- Järjestelmästä pitää selvitä myös, mitkä toimittajat pystyvät toimittamaan mitäkin osia.
Luentoesimerkki
Halutaan pitää kirjaa muutamien henkilöiden lainauksista. Lainaukset voivat olla esim. kirjoja, polkupyöriä, cd-levyjä, videoita...
- Esimerkki 1 Erkka-tiedostona
- Kaikkia erityyppisiä kohteita ei kannata liittää yhteen Lainaa-suhteeseen, koska tällä tarkoitetaan, että henkilö liittyy toiseen kohteeseen, mutta myös samalla lainaushetkellä kolmanteen kohteeseen jne.
- Kirja, Polkupyörä, Levy jne. on yleistetty ensin Tavaraksi, jolloin esimerkiksi tavaralainojen määrä on helpompi laskea. Tavara-kohteeseen tulee tavaroiden yhteiset ominaisuudet.
- Nyt saamme tavaroiden erilaiset ominaisuudet huomioon, mutta entä jos haluaisimme jälkikäteen lisätä uudentyyppisen tavaran?
- Esimerkki 2 Erkka-tiedostona
- Nyt eri tyyppejä on helppo lisätä.
- Entä eri tyyppisiin tavaroihin liittyvät ominaisuudet? Lisää luennolla 2.
Käyttäjien kommentit