Harjoitus: Tietokannan luominen SQL:llä

Harjoituksessa 3.1 määrittelimme relaatiotietokannan kaavan opiskelijatietokannalle. Tässä harjoituksessa rakennamme varsinaisen opiskelijatietokannan aiemman harjoituksen relaatiotietokannan kaavan pohjalta.

Tällä kurssilla relaatiotietokantojen luomiseen ja käsittelyyn käytetään SQLite-ohjelmaa. SQLite on tietokannanhallintajärjestelmä, joka soveltuu pienten relaatiotietokantojen luomiseen ja hallintaan. SQLite:ssa tietokannat määritellään ja hallitaan SQL-kyselykielellä.

Tämä harjoitus on jatkoa Harjoitukselle 3.1. Tee ensin Harjoitus 3.1 ennen tämän harjoituksen aloittamista.

Ennen harjoituksen aloittamista tutustu seuraaviin lukuihin:

Luku 4.1, jossa käsitellään SQL-kielen perusajatusta ja olennaisia käsitteitä.
Luku 4.3, jossa esitellään tietokannan rakenteen määrittämiseen tarkoitettua CREATE TABLE -syntaksia.
Luku 4.2, jossa esitellään SQL-kielen datahallintakielen (DCL) syntaksia. Tässä harjoituksessa riittää, että aluksi silmäilet lukua. SQL-kieltä pääsee harjoittelemaan paljon laajemmin Harjoituksessa 4.2.

Huomautus

Jos huomaat virheitä tai sinulla on kysyttävää tehtävistä, ota yhteyttä kurssin opettajiin

lähettämällä sähköpostia osoitteeseen itka2004-opet@tim.jyu.fi
laittamalla viestiä kurssin Teams-ryhmään tai osallistumaalla ohjauksiin: Teams ja ohjaukset

Tehtävänanto

Jatketaan Harjoituksessa 3.1 työstettyä opiskelijatietokantaa. Muodostetaan edellisessä harjoituksessa laaditun opiskelijatietokannan rakenteen perusteella SQLite-tietokanta.

Seuraa alla olevien osioiden vaiheita ja täydennä alla oleviin palautuslaatikoihin vastauksia niiden perusteella. Löydät jokaisen osion lopusta tarkistuspainikkeen, jolla voit testata vastauksesi oikeellisuuden.

Taulun rakenteen määritys

# openTaulut

Aloitetaan määrittelemällä jokin relaatiokaavassa oleva relaatio SQLite-tauluna.

Luodaan ensin taulu Tiedekunta, sillä sitä vastaava relaatio on melko yksinkertainen:

Tiedekunta (id, nimi)

SQL-kielessä taulu määritellään seuraavalla syntaksilla (ks. Luku 4.3):

CREATE TABLE taulu (
	sarake tietotyyppi[, 
	sarake tietotyyppi]*
);

Yllä olevan syntaksin ja relaatiokaavan perusteella Tiedekunta-taulu määritellään SQL-kielellä seuraavasti:

-- Tapa 1: Suositeltu tapa kirjoittaa taulun määrittely
CREATE TABLE Tiedekunta (
  id,
  nimi
);

Huomautus

Lisähuomioita (avaa klikkaamalla)

Jokainen SQL-kielen komento tulee päättää puolipisteeseen (;). Toisin sanoen, jokaisen CREATE TABLE -komennon lopussa on puolipiste.
SQL-kielen avainsanat voi kirjoittaa joko isolla tai pienellä. Siis create table ja CREATE TABLE tarkoittavat samaa asiaa. SQLite:ssä myöskään taulujen ja sarakkeiden nimien kirjainkoolla ei ole oletuksena merkitystä.
SQLite poikkeaa muista tietokannanhallintajärjestelmistä siten, että taulujen määrittelyn yhteydessä tietotyyppi ei ole pakollinen. Tämä ei kuitenkaan välttämättä päde muissa tuotteissa!
Sarakkeen nimessä ei tule olla välilyöntejä (vaikka SQL-kieli sen salliikin). Korvaa välilyönnit esimerkiksi alaviivalla (_).
SQLite:ssä sisennyksellä tai rivinvaihdolla ei yleensä ole merkitystä. Niinpä yllä oleva Tiedekunta-taulu voidaan määritellä vaihtoehtoisesti seuraavilla tavoilla
```
-- Tapa 2: Kaikki samalla rivillä
CREATE TABLE Tiedekunta (id, nimi);

-- Tapa 3: SQL-määreet ovat pienellä
create table Tiedekunta (id, nimi);

-- Tapa 4: Rivinvaihdot ja sisennykset satunnaisesti
create TABLE Tiedekunta (
   id,
nimi);
```
Kaikki yllä olevat tavat määritellä Tiedekunta-taulu ovat sallittuja. Suositus on kuitenkin käyttää tapaa 1, jossa jokainen sarake on omalla rivillään. Tämä helpottaa taulun rakenteen tulkitsemista erityisesti silloin, kun taululle ja sarakkeille määritellään eheysrajoitteita.

Määritä seuraavaksi alla olevaan palautuslaatikkoon kaksi taulua yllä olevan esimerkin mukaisesti: Tiedekunta ja Laitos. Tässä vaiheessa ei tarvitse vielä merkitä sarakkeiden tietotyyppejä eikä eheysrajoitteita, sillä niitä käsitellään seuraavassa vaiheessa.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# ex41-step1

Taulun perusavaimen ja viiteavaimen määritys

# openEheysrajoitteet

Määritetään sitten perusavain ja viiteavaimet tauluille Tiedekunta ja Laitos.

Perusavainta (ks. Luku 4.3 ja Luku 3.1) käytetään yksilöimään jokainen taulussa oleva rivi. Taulun jokaisella rivillä perusavaimen arvon tulee olla erilainen. SQL-kielessä perusavain voidaan määrittää kahdella tavalla: sarakkeen määrittelyn yhteydessä tai sarakkeiden jälkeen. Tässä harjoituksessa keskitymme erityisesti jälkimmäiseen merkintätapaan:

CREATE TABLE taulu (
    sarake tietotyyppi[, 
    sarake tietotyyppi]*[,
    PRIMARY KEY (sarake[, sarake]*)]?
);

Tässä harjoituksessa taulun perusavain määritetään sarakkeiden määrittelyn jälkeen PRIMARY KEY -määreellä. Muista, että perusavaimeen voi kuulua yksi tai useampi sarake.

Tarkastellaan relaatiota Tiedekunta. Relaation kaavasta nähdään, että relaation perusavaimessa on vain yksi attribuutti: id. Lisätään siis perusavaimen määrittely taulun Tiedekunta määritelmään:

CREATE TABLE Tiedekunta (
  id,
  nimi,
  PRIMARY KEY (id)
);

Viiteavain (ks. Luku 4.3 ja Luku 3.1) määrittää sarakkeen tai sarakkeita, joiden arvot vastaavat jonkin toisen taulun perusavaimen tai yksilöllisen sarakkeen arvoja. Viiteavaimen tarkoitus on mahdollistaa eri taulujen rivien välisten suhteiden kuvaaminen.

Viiteavaimen määritys ei ole lähtökohtaisesti pakollinen SQL-kielessä, mutta sen avulla tietokannanhallintajärjestelmä helpottaa viitteiden hallintaa ja eheyden valvontaa. Eri tietokannanhallintajärjestelmät saattavat tarjota hieman erilaisia ominaisuuksia, kun viiteavain merkitään tauluun. Esimerkiksi SQLite:

osaa taulun rivejä lisätessä varmistaa, että viiteavaimeen kuuluvien sarakkeiden arvot todellakin viittaavat toisen taulun johonkin olemassa olevaan riviin (esimerkiksi estää, ettei tilaukseen lisätä tuotteita, joita ei ole olemassa);
osaa automaattisesti päivittää viiteavainten arvot, jos viitatussa taulussa tapahtuu muutoksia (esimerkiksi päivittää tuotteen tunnuksen kaikissa tauluissa, jos se vaihtuu tuotteella);
osaa varmistaa, että viitatun taulun rivejä ei voi vahingossa poistaa, jos niihin on viittaus jossain toisessa taulussa (esimerkiksi estää, ettei tuotteen tietoja voi vahingossa poistaa, jos joku on tilannut sitä).

Oletuksena SQLite ei tee mitään varmistuksia viiteavaimen oikeellisuudesta, ellei viiteavaintukea ole kytketty erikseen päälle. Tässä materiaalissa viiteavainten oikeellisuuden tarkistus on aina päällä kaikissa tehtävissä. Jos teet oman SQLite-tietokannan, katso virallinen ohje viiteavaimista.

Viiteavaimen määrityksen syntaksi on hieman perusavaimen syntaksia laajempi:

CREATE TABLE taulu (
    sarake tietotyyppi[, 
    sarake tietotyyppi]*[,
    PRIMARY KEY (sarake[, sarake]*)]?[,
    FOREIGN KEY (sarake[, sarake]*) REFERENCES isäntätaulu (sarake[, sarake]*)
      [ON UPDATE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
      [ON DELETE [RESTRICT|SET NULL|CASCADE|SET DEFAULT]]?
    ]*
);

Huomautus

Lisähuomioita (avaa klikkaamalla)

Huomaa yhteys relaatiokaavassa olevaan viiteavaimen merkintään.

Esimerkiksi merkintää Taulu1.sarake -> Taulu2.sarake vastaava SQL-määre on
```
FOREIGN KEY (sarake) REFERENCES Taulu2 (sarake)
```
Taulua, jonka sarakkeisiin viiteavain viittaa, kutsutaan usein isäntätauluksi. Vastaavasti taulua, johon viiteavain määritellään, kutsutaan usein renkitauluksi. Yllä olevassa esimerkissä Taulu1 on renkitaulu ja Taulu2 on isäntätaulu.
SQL-kielessä samaan viiteavaimeen voi kuulua useampi sarake. Myös useita erillisiä viiteavaimia voidaan määritellä.

Esimerkiksi, jos relaatiokaavassa on merkinnät:

Taulu1.sarake1 -> Taulu2.viitattu_sarake1
Taulu1.sarake2 -> Taulu2.viitattu_sarake2

SQL-kielessä ne voitaisiin määritellä kahtena erillisenä viiteavaimena seuraavasti:
```
-- Kaksi erillistä viiteavainta
FOREIGN KEY (sarake1) REFERENCES Taulu2 (viitattu_sarake1),
FOREIGN KEY (sarake2) REFERENCES Taulu2 (viitattu_sarake2)
```
Tällöin sarake1 viittaa johonkin taulun Taulu2 riviin viitattu_sarake1:n kautta, ja sarake2 viittaa johonkin (mahdollisesti eri) taulun Taulu2 riviin viitattu_sarake2:n kautta.

Jos taas halutaan määritellä yksi yhdistetty viiteavain, jossa sarakkeiden sarake1 ja sarake2 yhdistelmä viittaa taulun Taulu2 johonkin riviin, käytetään seuraavaa tapaa:
```
-- Yksi yhdistetty viiteavain
FOREIGN KEY (sarake1, sarake2) REFERENCES Taulu2 (viitattu_sarake1, viitattu_sarake2)
```
Tämä tarkoittaa, että sarakkeiden sarake1 ja sarake2 arvojen yhdistelmä Taulu1:ssä viittaa vastaavaan yhdistelmään (viitattu_sarake1, viitattu_sarake2), joka yksilöi rivin taulussa Taulu2. Eli sarake1:n ja sarake2:n arvot yhdessä viittaavat aina yhteen ja samaan riviin taulussa Taulu2.

Valitse käytettävä tapa sen mukaan, onko kyseessä yhdistetty viiteavain vai useampi erillinen viiteavain.
Lisämääreillä (ON UPDATE ja ON DELETE) voi kertoa, miten SQLite:n tulee toimia, jos isäntätaulussa muokataan tai sieltä poistetaan rivejä, joihin renkitaulun rivit viittaavat (katso myös lisätietoja viite-eheyden säilyttämisestä Luvussa 3.1). Vaihtoehdot ovat:
- RESTRICT: Estää operaation, jos se rikkoo viite-eheyttä.
- SET NULL: Asettaa viittaavat sarakkeet NULL-arvoisiksi renkitaulussa.
- CASCADE: Vyöryttää muutokset: isäntätaulun rivin poisto poistaa myös viittaavat rivit renkitaulusta; isäntätaulun viitatun avaimen päivitys päivittää myös renkitaulun viittaavat avainarvot.
- SET DEFAULT: Asettaa renkitaulun viittaavat sarakkeet niiden määriteltyihin oletusarvoihin.
Jos lisämääreitä ei anneta, SQLite käyttää oletuksena NO ACTION -toimintoa. Tämä toiminto on samankailtainen kuin RESTRICT, eli se estää operaation, jos se rikkoo viite-eheyttä.

Tarkastellaan relaatiota Laitos. Relaation kaavasta huomataan, että relaatiolla on viiteavain:

Laitos.tiedekunta_id -> Tiedekunta.id

Tässä tapauksessa viitteen isäntätaulu on Tiedekunta ja renkitaulu on Laitos. Mietitään, miten tulisi menetellä, jos tiedekuntia muokattaisiin tai poistettaisiin:

Jos tiedekunta poistettaisiin, laitoksia saattaisi jäädä ilman tiedekuntaa. ER-kaaviossa kuitenkin vaadittiin, että laitos kuuluu aina johonkin tiedekuntaan. Siksi on järkevää, että tiedekuntia ei saisi poistaa, jos siihen kuuluu laitoksia. Toisin sanoen, ennen tiedekunnan poistoa tietokannan käyttäjän vastuulla on siirtää laitokset toiseen tiedekuntaan.
Tiedekunnan id on yksilöllinen ja pysyvä arvo, joten sen voi olettaa olevan aina sama. Siksi tiedekunnan id:n muuttumista ei tarvitse erikseen käsitellä ON UPDATE -lausekkeella.

Tämän perusteella muutetaan taulu Laitos seuraavaan muotoon:

CREATE TABLE Laitos (
  id,
  nimi,
  tiedekunta_id,
  FOREIGN KEY (tiedekunta_id) REFERENCES Tiedekunta (id)
    ON DELETE RESTRICT
);

Täydennetään edellisessä osassa luotujen taulujen Laitos ja Tiedekunta määritykset lisäämällä niihin perusavaimet ja mahdolliset viiteavaimet.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# ex41-step2

Sarakkeiden tietotyyppien ja eheysrajoitteiden määritys

# openTietotyypit

Seuraavaksi määritellään tarkemmat tietotyypit taulujen sarakkeille.

Oletuksena SQLite-tauluissa sarakkeisiin voi tallentaa mitä tahansa dataa. Toisin sanoen esimerkiksi SQLite sallii, että Opiskelija-taulussa on rivejä, joissa aloitusvuosi on 'kissa' tai että kurssin laajuus on 1.1.1996. Jotta vältytään virheiltä dataa tauluihin tallennettaessa, kannattaa jokaiselle sarakkeelle määrittää tarkka tietotyyppi.

Tietotyypit eroavat jonkin verran eri tietokannanhallintajärjestelmien välillä. Lähtökohtaisesti kaikki SQL-kieltä tukevat tietokannanhallintajärjestelmät tukevat alla olevia tietotyyppejä (ks. Luku 4.3).

Tietotyyppi	Selite	Käyttöesimerkki	Esimerkkisarake	Esimerkkiarvo
CHAR, CHARACTER	määrätyn mittainen merkkijono	`CHAR(10)`	`henkilötunnus`	`'111111-XXX'`
VARCHAR, CHARACTER VARYING	vaihtelevan mittainen merkkijono	`VARCHAR(20)`	`etunimi`	`'Seppo'` tai `'Viljami'`
INTEGER, INT	kokonaisluku	`INT`	`syntymävuosi`	`1990`
NUMERIC	(desimaali)luku (kokonaisosa ja desimaaliosa mahdollisesti rajoitettu)	`NUMERIC(7,2)`	`kuukausipalkka`	`2600.12`
BOOLEAN	totuusarvo	`BOOLEAN`	`tilaus_vahvistettu`	`TRUE`
DATE	päivämäärä	`DATE`	`tilauspvm`	`'2025-01-12'`
TIMESTAMP	aikaleima	`TIMESTAMP`	`maksuaika`	`'12:00:00'`
ARRAY	lista	`INTEGER[7]`	`lottonumerot`	`[1, 2, 3, 4, 5, 6, 7]`
TEXT	tekstimuotoinen sarake	`TEXT`	`arvostelu`	`'Kissa istuu puussa.'`

Eri järjestelmissä on toki muitakin tietotyyppejä. Esimerkiksi SQLite tukee yllä mainittujen lisäksi yleistä BLOB-tyyppiä, johon voi tallentaa mitä tahansa binääridataa (esim. kuvatiedostoja tai muita dokumentteja).

Myös sarakkeille voi antaa eheysrajoitteita, joilla voi vaikuttaa siihen, mitä arvoja sarakkeessa saa olla. Yleisimmät eheysrajoitteet on esitelty alla (ks. Luku 4.3).

DEFAULT-määreellä asetetaan sarakkeelle oletusarvo.
NOT NULL-määre kieltää tyhjäarvot sarakkeesta.
UNIQUE-määre takaa, että sarakkeen kaikki arvot ovat yksilöllisiä.
CHECK-määreellä voidaan tarkistaa, että sarakkeen arvot noudattavat annettua ehtolauseketta.

Sekä tietotyyppi että eheysrajoite määritellään jokaisen sarakkeen yhteydessä:

CREATE TABLE taulu (
    sarake tietotyyppi[ eheysrajoite[ eheysrajoite]*][, 
    sarake tietotyyppi[ eheysrajoite[ eheysrajoite]*]]*
);

Tarkastellaan taas taulua Tiedekunta ja asetetaan sen sarakkeille tietotyypit. Relaatiokaavan, ER-kaavion sekä alkuperäisen kuvauksen (ks. Harjoitus 2.1) perusteella voidaan päätellä seuraavat asiat sarakkeista:

Sarakkeelle id sopisi parhaiten tyyppi INTEGER eli kokonaisluku. Kokonaisluku on pienikokoinen, helppo käsitellä, ja eri tiedekunnille on helppo antaa yksilöllinen numero-ID. Koska sarake kuuluu perusavaimeen, se on jo yksilöllinen eikä salli NULL-arvoja.
Sarakkeelle nimi sopisi parhaiten tyyppi TEXT eli tekstimuotoinen sarake. Emme tiedä ennalta, kuinka pitkiä tiedekuntien nimet voivat olla, joten sen pituutta ei ole syytä rajata. Tämän lisäksi voidaan olettaa, että kaikilla tiedekunnilla on oltava nimi (eli se ei voi olla tyhjä, NOT NULL), ja että jokaisen tiedekunnan nimen on oltava yksilöllinen (UNIQUE), eli kahdella eri tiedekunnalla ei voi olla samaa nimeä.
- Bonustieto: Monissa lähteissä tekstin tyypiksi ehdotetaan VARCHAR. Käytännössä nykyään tietokannanhallintajärjestelmissä pituudeltaan rajoittamattomat merkkijonot (TEXT) ja vaihtelevan pituiset merkkijonot (VARCHAR sopivalla pituusmääreellä) tallennetaan usein samankaltaisella tavalla, joten TEXT ja pituudeltaan rajoittamaton VARCHAR (tai VARCHAR suurella pituusmääreellä) ovat usein toiminnallisesti hyvin samankaltaisia. Näin on esimerkiksi SQLite:ssa ja PostgreSQL:ssä. Sitä vastoin MySQL:ssä TEXT- ja VARCHAR-tyypeillä on selkeämpiä eroja datan tallennuksen ja käsittelyn kannalta. Oikeaa tietokantaa rakentaessa on aina tärkeää tutustua kyseisen järjestelmän tarjoamiin tietotyyppeihin ja niiden ominaisuuksiin.

Tämän perusteella muokataan Tiedekunta-taulu seuraavaan muotoon:

CREATE TABLE Tiedekunta (
  id INTEGER,
  nimi TEXT NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

Näin saatiin lopullinen Tiedekunta-taulun määritys.

Muokkaa alla olevassa palautuslaatikossa taulut Tiedekunta ja Laitos lisäämällä niiden sarakkeisiin sopivat tietotyypit sekä mahdolliset eheysrajoitteet.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# ex41-step3

Datan syöttäminen ja hakeminen

# openData

Kokeillaan tässä vaiheessa syöttää mallidataa kahteen luotuun tauluun sekä suorittaa yksinkertaisia hakukyselyjä.

Uusia rivejä voi syöttää tauluun käyttäen INSERT-käskyä (ks. Luku 4.2):

INSERT INTO taulu [(sarake[, sarake]*)]? 
VALUES (arvo[, arvo]*);

Tämän esimerkin perusteella lisätään pari riviä tauluihin Tiedekunta ja Laitos. Keksitään tiedekunnille ja laitoksille mielikuvitukselliset nimet ja ID:t.

INSERT INTO Tiedekunta VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta');

INSERT INTO Laitos VALUES (1, 'Matematiikan laitos', 1);
INSERT INTO Laitos VALUES (2, 'Fysiikan laitos', 1);

Huomautus

Lisähuomioita (avaa klikkaamalla)

Rivejä syötettäessä voi määrittää sarakkeet, joihin arvot syötetään. Esimerkiksi yllä oleva tiedekunnan lisääminen voitaisiin kirjoittaa tarkemmin:
```
INSERT INTO Tiedekunta (id, nimi)
VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta');
```
Tämä merkintätapa on hyödyllinen tapauksissa, joissa kaikille sarakkeille ei haluta tai voida antaa arvoja. Esimerkiksi SQLite:ssä jos perusavaimena on kokonaisluku, voidaan ID-sarakkeen arvo jättää antamatta, jolloin tietokannanhallintajärjestelmä antaa sille automaattisesti seuraavan vapaan kokonaisluvun:
```
-- Huomaa: annetaan vain nimi. Jos id on INTEGER PRIMARY KEY,
-- SQLite asettaa riville perusavaimen automaattisesti.
-- HUOM: Tämä ei välttämättä suoraan toimi kaikissa tietokannanhallintajärjestelmissä!
INSERT INTO Tiedekunta (nimi)
VALUES ('Kasvatustieteiden ja psykologian tiedekunta');
```

Jotkin tietokannanhallintajärjestelmän (kuten SQLite) sallivat usean rivin lisäämisen samaan tauluun yhdellä INSERT-käskyllä seuraavasti:

INSERT INTO Tiedekunta (id, nimi) -- Sarakkeiden nimet on yleensä hyvä määritellä vaikka niitä voisikin jättää pois.
VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta'),
       (2, 'Kasvatustieteiden ja psykologian tiedekunta');

Käskyjen järjestyksellä voi olla merkitys! SQL-kieltä suoritetaan aina käsky kerrallaan, ylhäältä alas. Jos taulussa on viiteavain, joka viittaa toiseen tauluun, on ensin lisättävä rivit siihen tauluun, johon viitataan, ja vasta sen jälkeen rivit, jotka sisältävät viittauksen.

Esimerkiksi alla oleva esimerkki EI TOIMI, koska viiteavainrajoite Laitos.tiedekunta_id -> Tiedekunta.id on voimassa:

-- EI TOIMI: Tässä yritetään lisätä Laitos-tauluun rivi, jonka tiedekunta_id-sarakkeeseen 
-- asetetaan arvo 1. Kuitenkaan Tiedekunta-taulussa ei ole vielä riviä, jonka id olisi 1,
-- koska se lisätään vasta myöhemmin.
-- Tiedekunnan on siis oltava jo lisättynä ennen kuin lisätään laitoksia, jotka
-- kuuluvat kyseiseen tiedekuntaan!
INSERT INTO Laitos (id, nimi, tiedekunta_id) VALUES (1, 'Matematiikan laitos', 1);
INSERT INTO Laitos (id, nimi, tiedekunta_id) VALUES (2, 'Fysiikan laitos', 1);

INSERT INTO Tiedekunta (id, nimi) VALUES (1, 'Matemaattis-luonnontieteellinen tiedekunta');

Toisin sanoen, laitokset eivät voi viitata sellaisiin tiedekuntiin, joita ei ole vielä olemassa laitoksen lisäyshetkellä.

Kun dataa on lisätty, sitä voidaan hakea SELECT-käskyllä. SELECT on SQL-kielen monipuolisin käsky: sillä voi hakea taulun rivejä erilaisten ehtojen perusteella, valita tietyt sarakkeet, yhdistää dataa eri tauluista, suorittaa laskutoimituksia sarakkeiden arvoilla ja ryhmitellä rivejä yhteen. SQL-kielen SELECT-käskyä käydään läpi tarkemmin Luvussa 4.2 sekä Harjoituksessa 4.2.

Tutustutaan tässä tehtävässä yksinkertaisimpaan hakulauseen muotoon: haku yksinkertaisen ehdon perusteella.

UPDATE taulu
SET sarake = lauseke[, sarake = lauseke]*
[WHERE ehtolauseke[ operaattori ehtolauseke]*]?;

SELECT-käskyssä määritellään haettavat sarakkeet (sarake1, sarake2, ...), taulu (taulu), josta dataa haetaan, ja valinnainen WHERE-ehto (ehtolauseke), jolla rajataan tulosjoukkoa.

Esimerkiksi kaikkien tiedekuntien hakeminen onnistuu komennolla:

SELECT id, nimi    -- Haetaan sarakkeet id ja nimi...
FROM Tiedekunta;   -- ...taulusta Tiedekunta.

Vastaavasti kaikkien laitosten nimet (eikä ID:itä) voi hakea seuraavasti:

SELECT nimi       -- Valitaan sarake nimi...
FROM Laitos;      -- ...taulusta Laitos.

Lisää seuraavaksi tietokantaan alla luetellut tiedekunnat ja niiden laitokset. Anna tiedekunnille ja laitoksille sopivat yksilölliset ID:t (tai anna SQLite:n luoda ne automaattisesti).

Tiedekunta: Matemaattis-luonnontieteellinen tiedekunta
- Laitos: Matematiikan laitos
- Laitos: Fysiikan laitos
- Laitos: Kemian laitos
- Laitos: Bio- ja ympäristötieteiden laitos
Tiedekunta: Kasvatustieteiden ja psykologian tiedekunta
- Laitos: Opettajankoulutuslaitos
- Laitos: Kasvatustieteiden laitos
- Laitos: Psykologian laitos

Lopuksi, hae kaikkien tiedekuntien nimet ja kaikkien laitosten nimet varmistaaksesi, että lisäys onnistui. Älä hae niiden ID:itä tai muita sarakkeita.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# ex41-step4

Harjoitus: Muiden taulujen määritys

# openTietokanta

Käy nyt läpi loput relaatiokaavan relaatiot ja määrittele niitä vastaavat taulut SQL-kielellä. Anna tauluille sekä niiden sarakkeille sopivat eheysrajoitteet (perusavain, viiteavain, sarakkeen pakollisuus) ja määritä sarakkeille sopivat tietotyypit.

Kokeile sitten lisätä dataa jokaiseen tauluun. Saat itse päättää, mitä dataa ja kuinka paljon sitä lisäät. Jokaisessa taulussa tulee olla vähintään yksi rivi.

Vinkkejä

Muista, että rivien lisäysjärjestyksellä voi olla väliä viite-eheyden tarkistuksen takia! Esimerkiksi jos taululla on viiteavain, rivejä lisättäessä viitatussa taulussa (isäntätaulussa) tulee olla jo olemassa rivi, johon viitataan. Rivien lisääminen kannattaa aloittaa tauluista, joissa ei ole viiteavaimia, ja edetä sitten tauluihin, jotka viittaavat niihin.

Jos saat seuraavanlaisen virheilmoituksen:
```
FOREIGN KEY constraint failed
```
se tarkoittaa, että yrität lisätä rivin, jonka viiteavaimen arvo ei ole vielä olemassa isäntätaulussa.

Esimerkiksi laitokset kuuluvat aina tiedekuntiin, joten ennen laitoksen lisäämistä on lisättävä tiedekunta, johon laitos kuuluu. Vastaavasti, ennen kuin opiskelijoita voi lisätä, on lisättävä laitos, johon opiskelija kuuluu. Samalla logiikalla tenttisuorituksia voi lisätä vain sellaisille kurssikoodeille, jotka ovat olemassa Kurssi-taulussa.
Päivämääräksi ja kellonajaksi sopii tietotyyppi DATETIME. Sen arvot tulee kirjoittaa merkkijonona muodossa 'VVVV-KK-PP HH:MM:SS', esimerkiksi '2025-06-02 20:50:46'. Vastaavasti tyyppi DATE tarkoittaa päivämäärää ilman kellonaikaa ja TIME pelkkää kellonaikaa.
Huomaa, että automaattitarkistin osaa tarkistaa vastauksesi vain, jos taulujen sekä sarakkeiden nimet ovat täsmälleen samat kuin relaatiokaavassa.
Tässä kohdealueessa (yliopiston opiskelijarekisteri) kurssikoodin on järkevää olla merkkijono (TEXT). Kurssikoodit ovat yleensä muotoa ITKP102, TJTA2020, ITKA2004. Alla olevan palautuslaatikon tarkistin olettaa, että kurssikoodi on merkkijono.

Voit kelluttaa kaavioeditorin Kelluta-painikkeella. Silloin teksti siirtyy oikeaan laitaan, ja editorille vapautuu tilaa oikealle.

# ex41-step5

Lopuksi

Kun olet valmis, voit jatkaa eteenpäin muihin harjoitustehtäviin.

Voit antaa lyhyen itsearvion onnistumisestasi ja yleisesti palautteen harjoituksesta alla.

# palaute