Tiedonhallinta I, loppukoe 28.3.2000
Tehtävät, ratkaisut ja arvostelu
1. Tarkastellaan relaatioita R(A,B,C,D) ja S(E,F,G), missä AB on R:n
perusavain, BC on R:n avainehdokas, EF on S:n perusavain ja CD on S:ään
viittaava R:n viiteavain.
a) Määrittele tarvittavat eheysrajoitteet (kolme avainrajoitetta ja yksi viite-
eheysrajoite) SQL-kielellä (monisteen mukaan SQL92-kielellä).
Ratk. Rajoitteet liitetään taulujen määrittelyyn (create table -lauseisiin) tai lisätään
myöhemmin alter table -lauseella:
create table R ...
constraint Rpk primary key (A, B),
constraint Rck unique (B,C),
constraint Rfk foreign key (C, D) references S(E, F)
create table S ...
constraint Spk primary key (E, F)
b) Mitä määrittelyjä tarvitaan, jos halutaan, että monikon poistaminen
relaatiosta S onnistuu aina? Missä vaiheessa ja minkä transaktion osana viite-
eheyden korjaavat operaatiot pitää suorittaa?
Ratk. Taulun S monikon poisto ei ole mahdollista a-kohdan viite-eheysrajoitteen Rfk
takia (mikäli taulussa R on taulusta S poistettavaan monikkoon liittyvä C,D-arvojen
pari). Kun muutetaan viite-eheysrajoite muotoon
constraint Rfk1 foreign key (C, D) references S(E, F) on delete cascade
S:n monikon poisto onnistuu (samalla poistetaan R:stä ne monikot, jotka viittaavat
poistettavaan S:n monikkoon). (Myös 'on delete set null' on mahdollinen, jos R:ssä
sallitaan attribuuteille C, D tyhjät arvot.)
Korjaavat operaatiot pitää suorittaa eheyttä rikkovan transaktion osana (muuten transaktion
päättyessä viite-eheys ei olisi voimassa). Kun tässä ei ole syklisiä viite-eheysrajoitteita,
operaatiot voidaan suorittaa välittömästi tai viivästetysti (transaktion lopussa).
Syklisessä tapauksessa viivästetty tapa on ainoa mahdollinen.
Arvostelu: a) 5 p, b) 3 + 2 + 2 = 7 p
2. Relaation R(A,B,C) ensisijaisena saantipolkuna on harva ISAM-hakemisto
attribuuttiparilla AB. Relaatioon on myös hajautusrakenteinen
oheishakemisto attribuutilla C.
a) Piirrä tiedostorakennetta havainnollistava kuva, kun relaatiossa on monikot
(22,11,77), (22,22,77), (22,33,55), (33,11,22), (33,33,88), (33,66,66)
ja sivulle mahtuu kolme tietuetta.
Ratk. Sijoitetaan perustiedoston sivuille vain 2 tietuetta, jolloin sivulle jää tilaa
yhdelle tietueelle (täyttösuhde 67 %, mikä on tässä tapauksessa sopivan lähellä yleistä
noin 50 %:n ohjetta). ISAM-hakemistosivut täytetään 100 %:sti.
Perustiedoston sivu P1: (22,11,77), (22,22,77) P2: (22,33,55), (33,11,22)
P3: (33,33,88), (33,66,66)
ISAM-hakemistosivu: (0,0,P1), (22,33,P2), (33,33,P3) (P1, P2, P3 linkkejä
vast. perustiedoston sivuille)
Hakemisto on siis tässä yksitasoinen.
(Tässä on lisäksi oletettu, että attribuuttien arvot ovat ei-negatiivisia.
Jos voi olla negatiivisia A,B-arvoja, korvataan nollat pienimmällä negatiivisella
luvulla.)
Oheishakemisto: Käytetään esim. hajautinta 'C mod 3' ja varataan hajautusalueen
soluun tilaa kolmelle hakemistotietueelle. Tällöin
solu 0: (66,P3,2) solu 1: (22,P2,2), (55,P2,1), (88,P3,1)
solu 2: (77,P1,1), (77,P1,2)
(tietueessa C-arvo, perustiedoston sivun tunniste ja tietueen numero)
b) Mitä hakemistorakenteiden käsittelytoimintoja sisältyy seuraaviin SQL-
operaatioihin?
b1) insert into R values(33,22,44),
b2) delete from R where C = 55,
b3) update R set B = 22 where B = 66.
Ratk. b1) Etsitään hakemistosivulta osoite P2 ja lisätään tietue sivun P2
kolmanneksi. Oheishakemiston soluun 2 lisätään tietue (44,P2,3).
b2) Etsitään oheishakemistosta arvolla 55 varustettu tietue (joita on tässä
vain yksi). Poistetaan tai poistomerkitään tietue sekä sen osoittama
perustiedoston tietue (22,33,55). (ISAM-hakemistosivuja ei päivitetä.)
b3) Etsitään perustiedostosta peräkkäishaulla tietueet, joissa B = 66
(löytyy vain yksi). Asetus B = 22 aiheuttaa tietueen siirron perustiedostossa:
etsitään sen paikka hakemistosivun kautta (P2,3), poistetaan tietue sivulta P3
ja lisätään sivulle P2, Lisäksi muutetaan oheishakemistossa vastaavan tietueen
osoite (uusi tietue (66,P2,3)).
Arvostelu: a) 6 p, b) 2 + 2 + 2 p. Yleisiä virheitä: tiheä hakemisto, oheishakemiston
rakenne (hajautus) epäselvä, b-kohdassa ei selitetty mitenkään tietueiden etsintää.
3. Eräiden taidemuseoiden kokoelmista säilytetään tietoja seuraavissa
relaatioissa:
TAIDETEOS(Taiteilija, Työ, Laji, Päiväys),
SIJOITUS(Taiteilija, Työ, Museo),
TAIDEMUSEO(Museo, Osoite, Hoitaja),
ARVIO(Taiteilija, Työ, Hinta).
Kirjoita relaatioalgebralla seuraavat kyselyt:
a) Rembrandtin maalausten sijoituspaikkojen osoitteet,
b) kalleimman maalauksen sijoituspaikan osoite.
Ratkaisut:
a) P (S (TAIDETEOS)*SIJOITUS*TAIDEMUSEO)
osoite taiteilija = 'Rembrandt' ^ laji = 'maalaus'
b) Vaiheittain:
1. T1 <- "sellaiset maalaukset, joita kalliimpi maalaus löytyy".
Tämä saadaan muodostamalla karteesinen tulo, jossa verrataan jokaista maalausta
jokaiseen muuhun maalaukseen ja valitaan tulokseen ehdon 'Hinta < H' täyttävät
rivit. (H on toinen hinta, joka esitetään uudelleen nimettynä operaatiolla R.)
T1 <- S (P (S (ARVIO*TAIDETEOS)) X
Hinta < H Tait,Työ Laji = 'Maalaus'
R (P (S (ARVIO*TAIDETEOS)))
Hinta->H Hinta Laji = 'Maalaus'
2. T2 <- "kaikki maalaukset" - T1. Tulokseksi saadaan kallein maalaus (yksi tai
useampia, jos samanhintaisia). Erotusta varten muodostetaan projektiot sekä kaikista
maalauksista että välituloksesta T1.
T2 <- P (S (ARVIO*TAIDETEOS)) - P T2
Tait,Työ Laji = 'Maalaus' Tait,Työ
3. Kootaan luonnollisella liitoksella tarvittavat työn ja sijoituspaikan yhdistelmät
ja muodostetaan lopputulos projektiolla:
P (T2 * SIJOITUS * TAIDEMUSEO)
Tait,Työ,Osoite
Arvostelu: a) 6 p (jos ei rajoituttu maalauksiin, 4 p)
b) 6 p Yleinen virhe oli, ettei hintojen vertailua tehty algebran operaatioin,
vaan 'oikaisemalla' operaatiolla max tms. Tästä sai yleensä 2 p, jos
ratkaisuyritys oli muuten selkeä. Täydet pisteet sai b-kohdassa, vaikka ei
olisikaan rajoittunut hintavertailussa maalauksiin (eli jos kalleimmaksi
tuli esim. jokin veistos).
4. Oletetaan, että järjestelmän romahdettua lokitiedoston sisältö on seuraava:
100: (start, T1)
101: (start, T2)
102: (write, T1, A, 10, 20)
103: (write, T2, B, 11, 22)
104: (commit, T1)
105: (start, T3)
106: (write, T3, A, 20, 30)
107: (abort, T2)
108: (checkpoint)
109: (start, T4)
110: (write, T3, B, 11, 33)
111: (write, T3, A, 30, 40)
112: (write, T4, C, 15, 25)
113: (commit, T3)
114: (write, T4, C, 25, 35)
Mitä operaatioita sisältyy tästä tilanteesta alkavaan elvytykseen? Oletetaan,
että tietoalkion A sisältävän sivun PageLSN = 106, tietoalkion B sisältävän
sivun PageLSN = 110 ja tietoalkion C sisältävän sivun PageLSN = 112.
Ratk. Lokin perusteella perutaan (undo) operaatioita, jotka kuuluvat kesken (sitoutumatta
tai keskeyttämättä) jääneeseen transaktioon. Vastaavasti suoritetaan uudelleen (redo)
operaatiot, joiden transaktio on sitoutunut, mutta joiden tietoja ei ole vielä viety
levylle. Sivun PageLSN kertoo viimeisimmän lokitietueen, jota vastaavan operaation tulos
on viety levylle.
Perumista varten tehdään transaktioista lista L1 ja uudelleen suoritusta varten lista L2
(ks. luennot, s. 224). Listan L1 transaktioiden write-operaatioiden tulokset palautetaan
lokitietueiden alkukuvien avulla, operaatiot käänteisessä järjestyksessä. Listan L2
transaktioiden write-operaatiot suoritetaan uudelleen alkuperäisessä järjestyksessä
lokin jälkikuvia käyttäen.
Tehtävässä L1 (sitoutumattomat) = ja L2 (uusittavat) = . (T1 ja T2 ovat päättyneet
ennen tarkistuspistettä.)
T4:n operaatiot perutaan:
write_item(C,25) (ei välttämätön, koska PageLSN(C)<114)
write-item(C,15) välttämätön
T3:n operaatiot uusitaan:
(write_item(A,30) ei tarvitse tehdä, koska tehty ennen tarkistuspistettä)
write_item(B,33) ei välttämätön, koska PageLSN(B)=110 eli B=33 on jo levyllä
write-item(A,40) välttämätön
Arvostelu: max 12 p Osa vastauksista hyvin sekavia: lokitietueen sisältö tuntematon,
samoin koko redo/undo-logiikka.
5. Selitä lyhyesti seuraavat asiat:
a) Armstrongin aksioomat: sisältö ja mihin niitä käytetään?
b) herätin (trigger); anna myös esimerkki tehtävän 3 tietokantaan liittyvästä
herättimestä.
Ratk. a) Ks. luennot, s. 306 tai E&N3, s. 479-481 (funktionaalisten riippuvuuksien
väliset päättelysäännöt: refleksiivisyys, täydennys ja transitiivisuus).
b) Ks. luennot, s. 264-269 tai E&N3, s. 734-739.
Esimerkki: rekisteröidään tauluun 'muutokset' tiedot epätavallisen suurista
hinta-arvion muutoksista:
create trigger suurimuutos
after update of hinta on arvio
for each row
when (new.hinta > 1.5 * old.hinta)
insert into muutokset values (new.taiteilija, new.tyo, old.hinta,
new.hinta, sysdate, ...);
Arvostelu. a) 5 p, b) 7 p