Relaatiotietokannat - Luento 6

• Luentotaltiointi
• Kertaus
• Relaatiotietokannat
  • Esimerkki
• Taulut (tables)
• Kentät (fields)
  • Standardoidut tietotyypit
• Avaimet (keys, indexes)
• Viite-eheys (Referential Integrity)
• ER-kaavion muuntaminen relaatioiksi
• Harjoitustyö

Tutustutaan relaatiotietokantojen peruskäsitteisiin sekä ER-kaavioiden muuntamiseen relaatiotietokannaksi.

Kertaus

Esimerkissä hinta-tieto olisi voinut olla myös kuhunkin tavaraan liittyvä tieto, jolloin saataisiin tehtyä helpommin tehtyä arvioita siitä mikä on tuottavin tavaratyyppi. Tavarassa ja työssä voisi olla myös kiinteästi myös jokin nimeävä ominaisuus (esim. tavaran nimi, tavaran merkki, työn nimi), jos suutari pystyisi jokaiselle tavaralle/työlle keksimään tällaisen tallennettavan tiedon.

• suutari_suunnitelma.xls
• suutari.erkka
• Etenemisjärjestys
  1. Kattava esimerkki
  2. Mitä ominaisuuksia esimerkin tiedoista saadaan aikaan?
  3. Kootaan ominaisuudet ominaisuuskokoelmiksi eli kohteiksi
  4. Mietitään kohteiden väliset suhteet ja niiden kardinaalisuudet
  5. Merkitään ominaisuuksien tietotyypit
  6. Selvitetään perusavaimet ja lisätään mahdolliset ID-kentät
  7. Muunnetaan ER-kaavio relaatiomalliin eli tehdään kohteista ja suhteista tauluja sekä lisätään viitekentät (kts. transformointiohje)
• Huom! ER-kaavioon ei laiteta yksittäisiä tietoja (esim. Matti Meikäläinen, Kengät, musta, hyllynro 3) vaan mietitään mikä kaikki yksittäisiä tapauksia yhdistävä kattotermi (=yleistäminen).
• ER-kaavioesimerkki Erkka-ohjelmaa käyttäen

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

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

• 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)

VARCHAR [(pit)]

Vaihtuvanmittainen merkkijono jolle määritetään maksimipituus

• Etunimi: VARCHAR(30)
• Sukunimi: VARCHAR(100)

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

INTEGER

Kokonaisluku, jolla on käyttöympäristöstä riippuva maksimikoko

• Lukumaara: INTEGER

DATE

Vuosi (0001 - 9999), kuukausi ja päivä.

• Syntymaaika: DATE

TIME

tunti, minuutti ja sekunti.

• Kellonaika: TIME

INTERVAL [(mittayksikkö)]

ajanväli, kulunut aika

• Juoksuaika: INTERVAL HOUR TO MINUTE

TIMESTAMP

Vuosi, kuukausi, päivä, sekä tunti, minuutti ja sekunti.

• Lahetysaika (esim. sähköposti): TIMESTAMP

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 tällaisiin tauluun syötetyistä tiedoista:

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

ER-kaavion muuntaminen relaatioiksi

1. Jokainen tavallinen kohde muutetaan relaatioksi (tauluksi)
2. Jokainen ER-diagrammista löytyvä ominaisuus lisätään siihen liittyvään relaatioon
3. 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.
4. 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

Harjoitustyö

• Suunnitelma
• Tietokantaosuus
• Taulukkolaskentaosuus