Käsitteellinen mallintaminen ja relaatiotietokannat - Luento 2
- Luentotaltiointi
- Käsitteellinen mallintaminen (Entity-Relationship-malli)
- ER-kaavioiden piirtäminen
- Relaatiotietokannat
- Taulut (tables)
- Kentät (fields)
- Avaimet (keys, indexes)
- Viite-eheys (Referential Integrity)
Tutustutaan relaatiotietokantojen peruskäsitteisiin sekä ER-kaavioiden piirtämiseen ja muuntamiseen relaatiotietokannaksi.
Luentotaltiointi
- tiedonhallinta02.wmv 43M
- tiedonhallinta02.mp3 17M
- tiedonhallinta02.avi 184M
Käsitteellinen mallintaminen (Entity-Relationship-malli)
Kohteet
- Kohde on ominaisuuskokoelma
- 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 tallentaa 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, hetu, osoite yms.
- Ominaisuus vastaa taulukossa sarakeotsikoita.
- Ominaisuus ei ole tallennettava tieto!
- Ominaisuuksien joukosta valitaan avaimeksi sopivat
- Avaimen pitää olla yksilöivä, uniikki.
- Avain voi olla myös yhdistelmä useammasta kentästä. (esim. puhelinnumero: etuosa + loppuosa)
- Avain voi olla juokseva numero omana ominaisuutena.
- Ominaisuus voi olla yksi- tai moniarvoinen. Moniarvoiset ominaisuudet (multivalued attributes) irrotetaan tällä kurssilla omiksi kohteikseen.
- Ominaisuuden arvojen kannattaa olla jakamattomia, 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).
- esim. tilausten kokonaislukumäärä lasketaan yksittäistilausten kappalemääristä.
- esim. ikä lasketaan syntymäajasta.
- Jokainen ominaisuus saa arvonsa tietystä arvojoukosta (esim. kurssin arvosana voi olla 1,2,3,4,5 tai 0 eli hylätty).
- Sallitaanko ominaisuuksille tyhjät arvot. (esim. koditon)
- Sallitaanko tuntemattomat arvot.
- Ominaisuus voi liittyä myös suhteeseen.
Suhteet
- Suhdetta kuvaa usein verbi.
- Suhde on 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 suhde 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 nimi.
- 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.
Relaatiotietokannat
- E.F. Codd esitteli relaatiomallin jo 1970
- yksinkertaisin, joustavin ja toteuttaa parhaiten tietokannalle asetettavat vaatimukset
- Vaatii eniten koneresursseja
- Relaatiotietokannassa tiedot esitetään tauluina (table) joiden yhtä riviä kutsutaan tietueeksi (record)
- taulun jokaisella rivillä on yhtä monta tietoa
- Jokaisella rivillä on yksikäsitteinen perusavain
- kutakin riviä vastaa jokin reaalimaailman kohde
- kuhunkin kohteeseen liitetään vain siihen välittömästi liittyvät ominaisuudet
- tauluja kutsutaan myös relaatioiksi
- Kukin yksittäinen tieto kannassa voidaan hakea ainakin ilmoittamalla taulun nimi, perusavaimen sarakenimi ja avaimen arvo sekä haettavan tiedon sarakenimi. Ainakin tämä hakutapa olemassa, lisäksi lukemattomia muita.
Esimerkki
Hetu | Etunimi | Sukunimi |
---|---|---|
111111-1234 | Jukka | Mäntylä |
222222-2345 | Tommi | Lahtonen |
121212-5678 | Petri | Heinonen |
ISBN | Nimi |
---|---|
1111111234 | Taru sormusten herrasta |
2222222345 | Harry Potter and the Half-Blood Prince |
1212125678 | SQL-Toolkit |
Taulut (tables)
Taulun nimeksi kannattaa valita jokin selkeä ja kuvaava nimi. Nimessä kannattaa kuitenkin välttää välilyöntejä, erikoismerkkejä ja skandinaavisia merkkejä (å,ä,ö).
Jokainen taulu sisältää yhden tai useamman kentän
Kentät (fields)
Kenttiä luotaessa niille määritellään seuraavanlaisia ominaisuuksia:
- Kentän nimi
Kentän nimeksi kannattaa valita jokin selkeä ja kuvaava nimi. Nimessä kannattaa kuitenkin välttää välilyöntejä, erikoismerkkejä ja skandinaavisia merkkejä (å,ä,ö).
- Tietotyyppi
- Numeerinen
Esim. lukumaara, paino, pituus, matka
- Merkkijono
Esim. Hetu, Etunimi, Sukunimi, postitoimipaikka, postinumero (!), kuvaus, puhelinnumero (!)
- Aika
Esim. Syntymäaika, Tenttipvm
- Numeerinen
- Kiinteä vai vaihtuvamittainen merkkijono?
Esimerkkejä
- Hetu on kiinteämittainen (aina 11 merkkiä)
- Postinumero on kiinteämittainen (aina viisi merkkiä)
- Etunimi on vaihtuvamittainen
- Sukunimi on vaihtuvamittainen
- Puhelinnumero on vaihtuvamittainen
- Maksimipituus ja mahdollinen tarkkuus
Esimerkkejä
- Etunimi-kentälle maksimipituus voisi olla 30 merkkiä.
- Sukunimi-kentälle maksimipituus voisi olla 100 merkkiä.
- Lukumaara-kentän tarkkuudeksi riittävät kokonaisluvut
- Paino-kentän tarkkuus pitää olla vähintään yksi desimaali esim. 70,5 kg
- Onko tieto pakollinen
Mahdollisimman moni kenttä pitää määritellä pakolliseksi tai kentälle pitää antaa jokin oletusarvo jolloin vältetään tuntemattomien arvojen tuomat ongelmat
- Tarkistukset syötettävän tiedon oikeellisuudelle tai
sallittujen arvojen joukko.
Esimerkkejä
- Hetu-kentän tarkistus: alussa pitää olla kuusi numeroa, sitten +,- tai A, sitten kolme numeroa ja viimeisenä tarkistusmerkki, jonka arvo saadaan laskettua kaavalla.
- Postinumeron tarkistus: Viisi merkkiä "0123456789"-joukosta
- Syntymäajan tarkistus: Jos käsitellään vain elossa olevia niin voitaisiin tarkistaa että syntymäaika ei saa olla ennen vuotta 1880. Syntymäaika ei saa olla myöskään suurempi kuin meneillään oleva ajanhetki.
- Lukumäärän on oltava suurempi kuin nolla
- Oletusarvo
Esimerkkejä
- Lukumäärän oletusarvo voisi olla esim. 1 tai 0
Standardoidut tietotyypit
Seuraavassa luetellaan tärkeimmät standardoidut tietotyypit.
CHAR [(pit)]
- Kiinteänmittainen merkkijono. Oletuspituus on 1.
- Hetu:
CHAR(11)
- Postinumero:
CHAR(5)
- Hetu:
VARCHAR [(pit)]
- Vaihtuvanmittainen merkkijono jolle määritetään maksimipituus
- Etunimi:
VARCHAR(30)
- Sukunimi:
VARCHAR(100)
- Etunimi:
NUMERIC [(pit., [desim.osa])]
- Tarkka numeerinen arvo, jonka maksimipituus on pit numeroa ja desim.osa
kuvaa desimaaliosan pituutta.
- Paino:
NUMERIC(4,1)
eli enintään neljä numeroa joista yksi on varattu desimaaliosalle
- Paino:
INTEGER
- Kokonaisluku, jolla on käyttöympäristöstä riippuva maksimikoko
- Lukumaara:
INTEGER
- Lukumaara:
DATE
- Vuosi (0001 - 9999), kuukausi ja päivä.
- Syntymaaika:
DATE
- Syntymaaika:
TIME
- tunti, minuutti ja sekunti.
- Kellonaika:
TIME
- Kellonaika:
INTERVAL [(mittayksikkö)]
- ajanväli, kulunut aika
- Juoksuaika:
INTERVAL HOUR TO MINUTE
- Juoksuaika:
TIMESTAMP
- Vuosi, kuukausi, päivä, sekä tunti, minuutti ja sekunti.
- Lahetysaika (esim. sähköposti):
TIMESTAMP
- Lahetysaika (esim. sähköposti):
Avaimet (keys, indexes)
- perusavain (primary key)
- Perusavain on tietueen uniikki tunniste
- yksikäsitteinen muodostuen yhdestä tai useammasta sarakkeesta
- taulusta ei saa löytyä identtisiä perusavaimen arvoja
- ei saa puuttua yhdeltäkään riviltä
- Toissijaiset avaimet (secondary key, secondary index)
Nopeuttavat taulun järjestämistä vrt. hakemisto
Toimivat rajoitteina
- Viite-avaimet (foreign key)
kelpuutettavat arvot ovat samoja kuin jonkin taulun kentän arvot (viitatun taulun)
Puhelinnumero-taulun Hetu-kenttään voi syöttää vain sellaisia arvoja jotka esiintyvät Henkilo-taulun hetu-kentästä:
Esimerkki tauluun syötetyistä tiedoista:
Hetu | Etunimi | Sukunimi |
---|---|---|
111111-1234 | Jukka | Mäntylä |
222222-2345 | Tommi | Lahtonen |
121212-5678 | Petri | Heinonen |
Suuntanro | Numero | Hetu |
---|---|---|
014 | 2602746 | 111111-1234 |
040 | 1575923 | 111111-1234 |
045 | 9987183 | 222222-2345 |
Tietokantaesimerkki samasta aiheesta (mdb)
Viite-eheys (Referential Integrity)
- jokaista taulussa esiintyvää viiteavaimen arvoa pitää vastata sama perusavaimen arvo taulussa, johon viiteavain viittaa
- yritettäessä rikkoa viite-eheyttä perusavaimen päivityksellä
on kolme vaihtoehtoa:
- Viiteavaimen nollaus
Kaikki poistuvaan perusavaimeen viittaavat viiteavaimet muutetaan tuntemattomiksi. Soveltuu vain sarakkeisiin, joilta ei ole kielletty puuttuvaa arvoa.
- Johdannaispoisto
kaikki poistuvaan perusavaimeen viittaavat viiteavaimet ja vastaavat rivit poistetaan
- Rajoitettu poisto
Voidaan poistaa vain perusavaimet, joihin ei ole viittauksia
- Viiteavaimen nollaus
Käyttäjien kommentit