Relaatiotietokannat, ER-kaavion muuntaminen relaatiotietokannaksi, Normalisointi, SQL ja ODBC - Luento 3
- Luentotaltiointi
- Relaatiotietokannat
- Taulut (tables)
- Kentät (fields)
- Avaimet (keys, indexes)
- Viite-eheys (Referential Integrity)
- ER-kaavion muuntaminen relaatioiksi
Tällä luennolla käydään läpi relaatiotietokantojen perusteita, ER-kaavion muuntamista relaatiotietokannaksi ja normalisoinnin idea
Luentotaltiointi
Ongelmia videon katselussa?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, 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
ER-kaavion muuntaminen relaatioiksi
- Jokainen tavallinen kohde muutetaan relaatioksi (tauluksi)
- Jokainen ER-diagrammista löytyvä ominaisuus lisätään siihen liittyvään relaatioon
- Jokainen Many-to-Many -suhde muutetaan tavalliseksi
relaatioksi.
- Määritellään relaatioon yhtä monta viiteavainta (foreign key) kuin on suhteeseen liittyviä kohteita
- määritellään perusavaimeksi viiteavaimista tehty yhdistetty avain
- lisätään relaatioon siihen liittyvät muut kentät.
- Many-to-One -suhteet
- Ei luoda uusia relaatioita mutta lisätään ennestään olevaan relaatioon uusi viiteavain. Lisäys tapahtuu kohteeseen, jonka puolella on Many-merkintä.
- Suhteeseen liittyneet ominaisuudet lisätään ennestään olevaan relaatioon
Käyttäjien kommentit
Nyt ei jotenkin uppoa kaaliin; minne ihmeeseen nämä kyselyt kirjoitetaan? Mihin ohjelmaan, mille riville? Mistä ohjelmasta tulee tuollaisia taulukoita (keltainen otsikkO)?
Ihan mihin tahansa tietokantaohjelmaan, joka ymmärtää sql-kieltä. Katso luentovideo tahi tee demotehtävät niin asia selkenee.