Relaatioiden luominen SQL:llä

English version

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. Ota yhteys ODBC Query Toolilla kurssin tietokantapalvelimeen. Query Toolin käyttämisen aloittamisesta löytyy erillinen ohje.
2. Saatuasi ODBC-yhteyden päälle pääset 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.
3. 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.
4. ODBC Query Tool suorittaa SQL-koodin kun klikkaat työkalupalkissa olevaa -kuvaketta tai painat F5. Kokeile suorittaa kirjoittamasi SQL-koodi. Jos saat virheilmoituksen niin tarkista kirjoititko SQL-lauseet varmasti oikein ja yritä uudelleen.
5. Tallenna kirjoittamasi SQL-koodi nimellä opiskelija.sql.
6. Voit tarkistaa taulun syntymisen tutkimalla Tables-listaa ODBC Query Toolin vasemmassa laidassa. Voit päivittää listan painamalla F8.
7. Lisää nyt ennen CREATE TABLE -lausetta lause, joka poistaa luomasi taulun:

   DROP TABLE Postinumero
   ;
   

   Varmista, että kaikissa kirjoittamissasi SQL-lauseissa on komennon lopettava puolipiste omalla rivillään. ODBC Query tool ei muuten osaa erottaa eri SQL-lauseita toisistaan. Suorita SQL-koodi. Jos kaikki meni ok niin et saa virheilmoituksia vaan tietokannanhallintajärjestelmä poisti ensin postinumero-taulun ja loi heti sen jälkeen taulun uudelleen. Tallenna.
8. Ensimmäinen versio taulun luovasta SQL-koodista ei ole vielä täydellinen. Lisätään SQL-koodiin taulun perusavaimen määrittely:

   DROP TABLE Postinumero
   ;
   
   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. PRIMARY KEY -sanojen jälkeen listataan sulkeissa perusavaimen muodostava kenttä tai yhdistetyn avaimen tapauksessa pilkulla eroteltuina kentät. Yritä suorittaa SQL-lauseet ja varmista taulun ilmestyminen Tables-listaan.
9. Samaan tapaan kuin Postinumero-taulu niin luodaan myös Tiedekunta-taulu. Kirjoita uusi SQL-lause edellisten perään:

   DROP TABLE Postinumero
   ;
   
   CREATE TABLE Postinumero (
   Postinumero      CHAR(5)     NOT NULL,
   Postitoimipaikka VARCHAR(64) NOT NULL,
      CONSTRAINT Postinumero_PK
      PRIMARY KEY (Postinumero)
   )
   ;
   
   CREATE TABLE Tiedekunta (
   TiedekuntaID      INTEGER     NOT NULL,
   Nimi              VARCHAR(64) NOT NULL,
   CONSTRAINT Tiedekunta_PK
      PRIMARY KEY (TiedekuntaID)
   )
   ;

   Suorita SQL-koodi. Tallenna. Lisää koko SQL-listauksen aivan ensimmäiseksi DROP TABLE -lause, joka poistaa tiedekunta-taulun.
10. Seuraavaksi voidaankin luoda Laitos-relaatio:

    DROP TABLE Tiedekunta
    ;
    DROP TABLE Postinumero
    ;
    
    CREATE TABLE Postinumero (
    Postinumero      CHAR(5)     NOT NULL,
    Postitoimipaikka VARCHAR(64) NOT NULL,
       CONSTRAINT Postinumero_PK
       PRIMARY KEY (Postinumero)
    )
    ;
    
    CREATE TABLE Tiedekunta (
    TiedekuntaID      INTEGER     NOT NULL,
    Nimi              VARCHAR(64) NOT NULL,
    CONSTRAINT Tiedekunta_PK
       PRIMARY KEY (TiedekuntaID)
    )
    ;
    
    CREATE TABLE Laitos (
    LaitosID          INTEGER     NOT NULL,
    Nimi              VARCHAR(64) NOT NULL,
    Tiedekunta        INTEGER     NOT NULL,
    CONSTRAINT Laitos_PK
       PRIMARY KEY (LaitosID)
    )
    ;

    Tallenna. Muista lisätä taas laitos-taulua vastaava DROP TABLE -lause aivan ensimmäiseksi.
11. Lisätään viite-eheysmäärittely Laitos-taulun määrittelevään SQL-lauseeseen:

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

    Viite-eheys määritellään FOREIGN KEY -sanoilla joiden jälkeen sulkeissa kerrotaan luotavan taulun kenttä tai kentät, jotka viittaavat toiseen tauluun. REFERENCES-sanan jälkeen kerrotaan viitattavan taulun nimi ja sulkeissa tässä taulussa oleva kenttä johon viitataan.

    Testaa SQL-lauseiden toimivuus. Tallenna.

12. Lisää vielä tarkemmat toimintasäännöt muutosten ja poistojen vyörymiselle viite-eheysmääritykseen.

    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 RESTRICT
    )
    ;

    ON UPDATE-määritys kertoo sallitaanko päivityksiä viitatussa taulussa. Jos päivitykset sallitaan niin ne on vyörytettävä (CASCADE) myös tähän tauluun. Jos päivityksiä ei sallita niin määritys on oltava RESTRICT.

    ON DELETE-määritys kertoo sallitaanko poistot viitatussa taulussa. Jos poistot sallitaan niin ne on vyörytettävä (CASCADE) myös tähän tauluun eli kaikki poistettuun tietueeseen viitanneet tietueet poistetaan tästä taulusta. Poistojen kieltäminen tapahtuu sanalla RESTRICT.

13. 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)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
    CONSTRAINT Opiskelija_FK_P
       FOREIGN KEY (Postinumero)
       REFERENCES Postinumero (Postinumero)
         ON UPDATE CASCADE
         ON DELETE RESTRICT
    )
    ;

    Testaa ja lisää myös vastaava DROP TABLE -lause. Tallenna.
14. Lisää aloitusvuosi-kentälle tarkistus ja oletusarvo:

    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 DEFAULT 2002,
    Laitos            INTEGER      NOT NULL,
    CONSTRAINT Opiskelija_PK
       PRIMARY KEY (Sotu),
    CONSTRAINT Opiskelija_FK_L
       FOREIGN KEY (Laitos)
       REFERENCES Laitos (LaitosID)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
    CONSTRAINT Opiskelija_FK_P
       FOREIGN KEY (Postinumero)
       REFERENCES Postinumero (Postinumero)
         ON UPDATE CASCADE
         ON DELETE RESTRICT,
    CONSTRAINT Opiskelija_ch
    	 CHECK ( aloitusvuosi >= 1990 )
    )
    ;

    Oletusarvot voi määritellä lisäämällä pakollisuusmääritteiden jälkeen sanan DEFAULT ja heti perään halutun oletusarvon.

    Tarkistuksia lisätään CHECK-määritteellä. Tarkistuksena voi käyttää melkein mitä tahansa SQL-kyselyä tai ehtoa. Kyselyiden tekeminen opitaan myöhemmin.

15. 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
    Lisää vain yhteen tauluun liittyvät lauseet kerralla. Testaa ja lisää seuraava. Muista tallentaa tarpeeksi usein.

Yritystietokanta

1. Poista kokonaan edellä luomasi tietokanta suorittamalla pelkät DROP TABLE -lauseet.
2. Luo edellisdemoissa tekemiesi määrityksien pohjalta tarvittavat SQL-lauseet yritystietokannan relaatioiden luomiseksi. Tallenna nämä yritys.sql-nimelle.

Lisätehtävä: Futistietokanta

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