Relaatioiden luominen SQL:llä

Jos et ole vielä tehnyt loppuun kahta ensimmäistä demoa niin viimeistele ne ennen, kuin jatkat näitä tehtäviä.

Opiskelijatietokanta

Edellisissä demoissa määriteltiin ER-kaavion pohjalta luotavat relaatiot ja niihin sisältyvät kentät. Tällä kertaa tehdään varsinainen relaatioiden luominen SQL-kielen avulla. Samalla määritellään myös relaatioiden väliset viite-eheydet.

1. Käynnistä Microsoft Access 2000
2. Luo uusi tietokanta (Blank Access Database)
3. Anna uudelle tietokannalle nimeksi Opiskelija ja talleta se U:-asemalle Tietokannat-kansioon.
4. Access aukaisee dialogin, josta voit valita erilaisia vaihtoehtoja. SQL-koodin kirjoittaminen tapahtuu Accessin kyselyjenlaatimistilassa. Valitse Queries|Create query in Design view.
5. Sulje Show Table-dialogi.
6. Valitse ylävalikoista View|SQL View.
7. Nyt pääsetkin jo kirjoittamaan SQL-koodia. Käynnistä Excel ja aukaise näkyville relaatioiden määrittelyt, jotka olet tehnyt edellisissä demoissa. Voit hätätilassa käyttää apunasi myös malliratkaisua.
8. Luodaan tietokannan relaatiot vaiheittain. Luominen täytyy aloittaa aina sellaisista relaatioista, joista ei ole viittauksia muihin relaatioihin. Tällaisia relaatioita opiskelijatietokannassamme ovat:
   • Postinumero
   Tiedekunta
   Luodaan ensimmäiseksi Postinumero-taulu kirjoittamalla:

   CREATE TABLE Postinumero (
   Postinumero      CHAR(5)     NOT NULL,
   Postitoimipaikka VARCHAR(64) NOT NULL
   )

   Kenttien nimet, tietotyypit ja pakollisuusmääritykset saadaan suoraan Exceliin kirjoitetuista tiedoista. Jos kenttä ei olisi pakollinen niin NOT NULL -määritys jäisi pois.
9. Access suorittaa SQL-koodin klikkaamalla työkalupalkissa olevaa !-kuvaketta tai valitsemalla Query|Run. Kokeile suorittaa kirjoittamasi SQL-koodi. Jos Access ei anna mitään ilmoituksia niin koodisi on kirjoitettu oikein. Jos saat virheilmoituksen niin korjaa virhe ja yritä uudelleen.
10. Talleta kirjoittamasi SQL-koodi nimellä 01_Luo_Postinumero.
11. Voit tarkistaa taulun syntymisen tutkimalla Tables-välilehteä Accessin käynnistyessä aukaisemassa ikkunassa. Poista luomasi Postinumero-taulu valitsemalla se ja painamalla DELETE-näppäintä.
12. Ensimmäinen versio taulun luovasta SQL-koodista ei ole vielä täydellinen. Lisätään SQL-koodiin taulun perusavaimen määrittely:

    CREATE TABLE Postinumero (
    Postinumero      CHAR(5)     NOT NULL,
    Postitoimipaikka VARCHAR(64) NOT NULL,
    CONSTRAINT Postinumero_PK
       PRIMARY KEY (Postinumero)
    )

    CONSTRAINT-määrityksellä annetaan perusavaimelle yksilöllinen nimi, jonka avulla siihen voidaan tarvittaessa myöhemmin viitata. Yritä suorittaa uusi SQL-kysely ja varmista taulun ilmestyminen Tables-välilehdelle.
13. Microsoft Accessilla ei ikävä kyllä voi SQL-kielellä määritellä taulun kenttiin liittyviä oletusarvoja tai tarkistuksia. Lisätään nämä taulumäärityksiin myöhemmässä vaiheessa Accessin omilla työkaluilla.
14. Samaan tapaan kuin Postinumero-taulu niin luodaan myös Tiedekunta-taulu. Aloita uusi kysely johon kirjoitat:

    CREATE TABLE Tiedekunta (
    TiedekuntaID      INTEGER     NOT NULL,
    Nimi              VARCHAR(64) NOT NULL,
    CONSTRAINT Tiedekunta_PK
       PRIMARY KEY (TiedekuntaID)
    )

    Suorita SQL-koodi. Talleta tämä koodi 02_Luo_Tiedekunta-nimellä.
15. Seuraavaksi voidaankin luoda Laitos-relaatio:

    CREATE TABLE Laitos (
    LaitosID          INTEGER     NOT NULL,
    Nimi              VARCHAR(64) NOT NULL,
    Tiedekunta        INTEGER     NOT NULL,
    CONSTRAINT Laitos_PK
       PRIMARY KEY (LaitosID)
    )

    Talleta SQL-koodi 03_Luo_Laitos-nimellä.
16. Varmista, että Laitos-taulun luominen onnistui. Poista vielä Laitos-taulu niin voit luoda sen uudelleen viite-eheysmääritysten kera.
17. Lisätään viite-eheysmäärittely Laitos-taulun määrittelevään SQL-kyselyyn:

    CREATE TABLE Laitos (
    LaitosID          INTEGER     NOT NULL,
    Nimi              VARCHAR(64) NOT NULL,
    Tiedekunta        INTEGER     NOT NULL,
    CONSTRAINT Laitos_PK
       PRIMARY KEY (LaitosID),
    CONSTRAINT Laitos_FK_T
       FOREIGN KEY (Tiedekunta)
    	 REFERENCES Tiedekunta (TiedekuntaID)
    )

    Testaa SQL-kyselyn toimivuus.
18. Access ei ikävä kyllä ymmärrä viite-eheyden toimintaan liittyviä määrityksiä SQL-kielellä kerrottuna. SQL-92:en mukaan voisimme kirjoittaa seuraavasti:

    CREATE TABLE Laitos (
    LaitosID          INTEGER     NOT NULL,
    Nimi              VARCHAR(64) NOT NULL,
    Tiedekunta        INTEGER     NOT NULL,
    CONSTRAINT Laitos_PK
       PRIMARY KEY (LaitosID),
    CONSTRAINT Laitos_FK_T
       FOREIGN KEY (Tiedekunta)
       REFERENCES Tiedekunta (TiedekuntaID)
          ON UPDATE CASCADE
          ON DELETE NO ACTION
    )

    Ylläoleva koodi ei kuitenkaan Accessissa toimi joten tyydymme määrittelemään viite-eheyssäännöt myöhemmin Accessin omilla työkaluilla.
19. Luodaan seuraavaksi Opiskelija-taulu:

    CREATE TABLE Opiskelija (
    Sotu              CHAR(11)     NOT NULL,
    Sahkopostiosoite  VARCHAR(64),
    Etunimi           VARCHAR(32)  NOT NULL,
    Sukunimi          VARCHAR(64)  NOT NULL,
    Lahiosoite        VARCHAR(64)  NOT NULL,
    Postinumero       CHAR(5)      NOT NULL,
    Aloitusvuosi      SMALLINT     NOT NULL,
    Laitos            INTEGER      NOT NULL,
    CONSTRAINT Opiskelija_PK
       PRIMARY KEY (Sotu),
    CONSTRAINT Opiskelija_FK_L
       FOREIGN KEY (Laitos)
       REFERENCES Laitos (LaitosID),
    CONSTRAINT Opiskelija_FK_P
       FOREIGN KEY (Postinumero)
       REFERENCES Postinumero (Postinumero)
    )

    Talleta kysely 04_Luo_Opiskelija-nimellä. Testaa kyselyn toiminta.
20. Luo kaikki loput opiskelijatietokantaan liittyvät relaatiot samalla tavalla kuin edeltävät relaatiot luotiin. Luo viimeiset relaatiot seuraavassa järjestyksessä:
    1. Puhelinnumero
    2. Kurssi
    3. Tenttii
    Talleta jokaiseen relaatioon liittyvät SQL-lauseet edellisien taulujen yhteydessä käytettyä nimeämislogiikkaa noudattaen.
21. Saatuasi valmiiksi SQL-lauseet jokaista relaatiota varten niin poista vielä kaikki luomasi taulut. Luo koko tietokanta kerralla uudelleen suorittamalla oikeassa järjestyksessä kaikki kirjoittamasi SQL-kyselyt. Kyselyn saa suoritettua tuplaklikkaamalla.
22. Lisätään tietokantaan vielä viite-eheysmääritykset. Valitse Tools|Relationship.
23. Saat näkyviin kaaviokuvan relaatioista ja niiden välisistä viite-eheysyhteyksistä. Järjestele kuva selkeäksi.
24. Klikkaa jokaista relaatioiden välistä viivaa ja määrittele aukeavan dialogin avulla aina kyseisen yhteydet viite-eheyden toiminta. Oikean toiminnan näet exceliin kirjoittamistasi rajoituksista.
25. Sulje Relationships-ikkuna ja anna Accessin tallettaa sen asetukset.

Yritystietokanta

1. Luo Accessiin uusi tietokanta, jonka nimeksi annat yritys. Talleta tämä tietokanta U:-asemalle Tietokannat-kansioon.
2. Luo edellisdemoissa tekemiesi määrityksien pohjalta tarvittavat SQL-lauseet yritystietokannan relaatioiden luomiseksi.
3. Luo yritystietokanta.
4. Muista määritellä lopuksi myös viite-eheystoiminnat.

Futistietokanta

Jos sinulla vielä riittää aikaa ja innostusta niin luo myös futistietokanta SQL:n avulla.