Kentät, avaimet, viite-eheys, ER-kaavion muuntaminen relaatioiksi

• tietokannat0302.mp3 3.6M
• tietokannat0303.mp3 4.0M
• tietokannat0304.mp3 4.1M
• tietokannat0305.mp3 3.0M
• tietokannat0306.mp3 2.5M
• tietokannat0307.mp3 2.3M

ääniterekisteri

Käydään vielä läpi edellisellä luennolla aloitettua ääniterekisterin er-kaaviota.

• malli.xls (excel-tiedosto)

Relaatiotietokannan osat

Relaatiotietokanta muodostuu tauluista, jotka sisältävät kenttiä.

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
  • Merkkijono
  • Aika
• Kiinteä vai vaihtuvamittainen kenttä?
• Maksimipituus ja mahdollinen tarkkuus
• Onko tieto pakollinen
  mahdollisimman moni kenttä pitää määritellä pakolliseksi jolloin vältetään NULL-arvojen tuomat ongelmat
• Tarkistukset syötettävän tiedon oikeellisuudelle tai sallittujen arvojoukkojen joukko.
• Oletusarvo

SQL-92:en tietotyypit

CHAR [(pit)]

Kiinteänmittainen merkkijono. Oletuspituus on 1.

VARCHAR [(pit)]

Vaihtuvanmittainen merkkijono.

NUMERIC [(pit., [desim.osa])]

Tarkka numeerinen arvo, jonka koko pituus on pit ja desim.osa kuvaa desimaaliosan pituutta.

DECIMAL [(pit., [desim.osa])]

Kuten edellä mutta pituus voi ohjelmistokohtaisesti olla pitempikin kuin pit.

INTEGER

Kokonaisluku, jonka pituus on ohjelmistokohtainen. Esim. 32-bittinen kokonaisluku.

SMALLINT

Kokonaisluku, joka on pienempi kuin INTEGER. Sen pituus on ohjelmistokohtainen (puolisana). Esim. 16-bittinen kokonaisluku.

FLOAT [(pit.)]

Liukuluku, jonka pituus on suurempi tai yhtäsuuri kuin pit.

REAL

Liukuluku, jolla on ohjelmistokohtainen pituus.

DOUBLE PRECISION

Kaksoistarkkuudella esitetty liukuluku, jonka pituus on ohjelmistokohtainen ja pitempi kuin REALin pituus.

BIT [(pit.)]

Mielivaltainen bittijono. Oletuspituus on 1.

BIT VARYING (pit.)

Mielivaltainen annetunmittainen bittijono. Pit on annettava.

DATE

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

TIME [(pit.)]

Tunti (00-23), minuutti (0-59) ja sekunti (00-61.9999).

TIMESTAMP [(pit.)]

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

TIME WITH TIME ZONE (pit.)

Sama kuin TIME, mutta lisätietona erotus aikavyöhykkeeseen.

TIMESTAMP WITH TIME ZONE (pit.)

Sama kuin TIMESTAMP, mutta lisätietona erotus aikavyöhykkeeseen.

AVAIMET (keys)

• perusavain (primary key)
  
  • yksikäsitteinen muodostuen yhdestä tai useammasta sarakkeesta
  • taulusta ei saa löytyä identtisiä perusavaimen arvoja
  • ei saa puuttua yhdeltäkään riviltä
  • perusavaimen eheys (entity integrity)
• toissijaiset avaimet (secondary key, secondary index)
  nopeuttavat taulun järjestämistä
• viite-avaimet
  määritysalue on sama kuin jonkin perusavaimen

ER-mallin 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
5. Moniarvoisista ominaisuuksista luodaan uusi kohde josta tehdään viittaus ominaisuuteen liittyvään kohteeseen

Viite-eheys (Referential Integrity)

• jokaista taulussa esiintyvää (NULL:sta poikkeavaa) viiteavaimen arvoa pitää vastata sama perusavaimen arvo taulussa, johon viiteavain viittaa
• yritettäessä rikkoa viite-eheyttä perusavaimen päivityksellä on kolme vaihtoehtoa:
  • NULLIFIES eli viiteavaimen nollaus
    kaikki poistuvaan perusavaimeen viittaavat viiteavaimet muutetaan NULLeiksi soveltuu vain sarakkeisiin, joilta ei ole kielletty puuttuvaa arvoa (NOT NULL)
  • CASCADES eli johdannaispoisto:
    kaikki poistuvaan perusavaimeen viittaavat viiteavaimet ja vastaavat rivit poistetaan
  • RESTRICTED eli rajoitettu poisto:
    voidaan poistaa vain perusavaimet, joihin ei ole viittauksia eli kyseistä päivitystä ei tehdä.
• Viite-eheysmäärittelyt
  • Many-to-Many -suhteisiin DELETE RESTRICTED, UPDATE CASCADES
  • Many-to-One -suhteet DELETE RESTRICTED, UPDATE CASCADES
    • heikoille kohdetyypeille DELETE CASCADES, UPDATE CASCADES
• malli.mdb