Transcript Tietokannat2Osa3_3

3.2. Relaatiotietokannan normalisointi
• Rakenteelliset ongelmat:
Miten ne tunnistaa ja miten niistä pääsee eroon?
• Attribuuttien väliset riippuvuudet keskeisiä:
Minkälaiset riippuvuudet ovat haitallisia ja miksi?
• Relaatioiden normaalimuodot:
Asteittain tiukempia laatuvaatimuksia, joilla
ongelmat vältetään.
3-2-Normalisointi
Teuhola-2012
132
Pyrkimys laatuun
• Millainen on hyvä relaatiotietokannan kaava?
– Käsitteellinen taso (conceptual): Kaavan semanttinen
oikeellisuus ja ymmärrettävyys käyttäjälle.
– Toteutustaso (implementation): Talletuksen tehokkuus,
päivitysten helppous.
• Normalisointi käsittelytason ”taikasana”
– Suunnitteluperiaatteet korkean laadun takaamiseksi
– Kohteena joko yksi relaatio (alemmat normaalimuodot) tai
koko relaatiokaava (ylemmät normaalimuodot)
– Osallistuva suunnittelu (käyttäjä + suunnittelija)
3-2-Normalisointi
Teuhola-2012
133
Suunnittelun suunnat
•
Alhaalta ylös (Bottom-up)
–
•
Lähtökohtana yksittäiset attribuutit ja niiden väliset
semanttiset yhteydet
Ylhäältä alas (Top-down)
–
–
’Design-by-analysis’
Lähtökohtana valmiiksi ryhmitellyt attribuutit
(tuloksena käsitetason suunnittelusta).
3-2-Normalisointi
Teuhola-2012
134
Relaatiokaavan laadun mittarit
(1) Relaation ja sen attribuuttien
luontevuus
• Pyrkimys yksinkertaisiin relaatioihin
• Relaatioilla selkeä merkitys
• Ei sotketa useita entiteettejä ja (moniarvoisia)
liittymiä samaan relaatioon.
• Relaatio kuvaa vain yhdentyyppisiä asioita.
3-2-Normalisointi
Teuhola-2012
135
Esimerkki: pyrkimys yksinkertaisuuteen
• Huonosti suunniteltu (2 entiteettiä ja liittymä):
Työntekijä (Hetu, Nimi, Osoite, OsastoNo, OsastoNimi,
JohtajaHetu)
• Parempi ratkaisu (toinen entiteetti eri relaatioon):
Työntekijä (Hetu, Nimi, Osoite, OsastoNo)
Osasto (OsastoNo, OsastoNimi, JohtajaHetu)
3-2-Normalisointi
Teuhola-2012
136
Relaatiokaavan laadun mittarit
(2) Redundanssin minimointi
• Muistintarpeen vähentäminen
• Päivitysongelmien välttäminen:
– Lisäys: Yksi asia kerrallaan
– Poisto: Ei kadoteta tarpeellista informaatiota
– Muutos: Ei samoja muutoksia moneen paikkaan
• Redundanssia voi joskus käyttää harkitusti
tehokkuuden parantamiseen
– Tällöin on huolehdittava myös syntyvistä ongelmista!
3-2-Normalisointi
Teuhola-2012
137
Esimerkki redundanssista
Työntekijä
Hetu
Nimi
Osoite
OsNo
OsNimi
JohtHetu
123456
Aalto
Turku
11
Tutkimus
987654
234567
Laine
Raisio
11
Tutkimus
987654
345678
Järvi
Kaarina
22
Varasto
876543
456789
Lahti
Turku
22
Varasto
876543
567890
Virta
Raisio
22
Varasto
876543
3-2-Normalisointi
Teuhola-2012
138
Relaatiokaavan laadun mittarit
(3) Null-arvojen vähentäminen
• NULLin hankaluuksia:
– Muistin haaskaus
– Tulkintaeroja: ’arvo ei mahdollinen’, ’arvo tuntematon’,
’arvo puuttuu’
– Liitosten määrittely epäselvä
– Tulkinta koostefunktioissa (COUNT, AVG) epäselvä
– Oikeellisuuden valvonta viiteavaimissa hankalaa
• Siis: Valitse vaihtoehtoisista relaatiokaavoista se, joka
minimoi NULLien määrän. Systeemi valvoo NOT NULL
–määreiden voimassaoloa automaattisesti.
3-2-Normalisointi
Teuhola-2012
139
Esimerkki NULL-arvojen välttämisestä
• Ei näin:
Työntekijä (Hetu, Nimi, Osoite, OsNoJotaJohtaa)
Osasto (OsNo, OsNimi)
vaan näin (useimmat työntekijät eivät johda osastoa):
Työntekijä (Hetu, Nimi, Osoite)
Osasto (OsNo, OsNimi, JohtajaHetu)
3-2-Normalisointi
Teuhola-2012
NOT NULL
140
Esimerkki NULL-arvojen välttämisestä (2)
• Oletetaan, että joillakin työntekijöillä on työsuhdeauto,
mutta useimmilla ei.
NULL-arvoja tuottava ratkaisu:
Työntekijä (Hetu, Nimi, Osoite, OsNo, TyöAutoRek)
NULL-arvot välttävä ratkaisu:
Työntekijä (Hetu, Nimi, Osoite, OsNo)
TyöntekijäAuto (Hetu, TyöAutoRek)
Jälkimmäisessä relaatiossa ovat vain autoedun omaavat.
Mutta: Luonnollinen liitos ei tuota alkuperäistä relaatiota!
3-2-Normalisointi
Teuhola-2012
141
Relaatiokaavan laadun mittarit
(4) Väärien monikkojen välttäminen
• Huonosti suunniteltu relaatiokaava voi johtaa
virheellisiin (’spurious’) monikoihin liitoksessa.
• Ohje:
Suunnittele relaatiot siten, että ne on mielekästä liittää
vain viiteavain-pääavain-yhteyden perusteella, jolloin
vääriä monikkoja ei synny
3-2-Normalisointi
Teuhola-2012
142
Esimerkki väärien monikoiden synnystä
TyöntekOsasto
Liitoksen tulos (OsNo:n perusteella)
Hetu
Osoite
OsNo
Hetu
Nimi
Osoite Osno
12345
Turku
11
12345
Aalto
Turku
11
23456
Salo
11
12345
Järvi
Turku
11
34567
Turku
22
23456
Aalto
Salo
11
23456
Järvi
Salo
11
34567
Laine
Turku
22
NimiJaOsasto
Nimi
OsNo
Aalto
11
Järvi
11
Laine
22
3-2-Normalisointi
*)
*)
*) ‘Väärät’ monikot
Teuhola-2012
143
Funktionaalinen riippuvuus
(functional dependency, FD)
• Alempien normaalimuotojen perusta
• Funktionaalinen = yksiarvoinen
• Funktionaalinen riippuvuus: yksiarvoinen
lukumääräsuhde
• Riippuvuuden osapuolet ovat relaation sisällä
olevia attribuutteja tai attribuuttiryhmiä.
• Vrt. ER-kaavion lukumääräsuhteet 1:1 ja M:1
3-2-Normalisointi
Teuhola-2012
144
Funktionaalinen riippuvuus:
alustava esimerkki (1)
• Työntekijä(Hetu, Nimi, Osasto)
Kullakin työntekijällä on tarkalleen yksi nimi ja
osasto. ‘Hetu’ identifioi työntekijät.
Funktionaaliset eli yksiarvoiset riippuvuudet:
Hetu  Nimi
Hetu  Osasto
3-2-Normalisointi
Teuhola-2012
145
Funktionaalinen riippuvuus:
alustava esimerkki (2)
• Osallistuu (Hetu, Projekti, Tuntimäärä)
Henkilö voi osallistua moneen projektiin ja samassa
projektissa voi olla useita työntekijöitä, mutta henkilön
tuntimäärä yhdessä projektissa on yksiarvoinen.
Funktionaalinen riippuvuus:
(Hetu, Projekti)  Tuntimäärä
Riippuvuuden vasen puoli on siis attribuuttiryhmä
3-2-Normalisointi
Teuhola-2012
146
Funktionaalinen riippuvuus formaalisti
• Olkoot X ja Y attribuutteja tai attribuuttiyhdelmiä
relaatiossa R, ja merkitään ti[X] = X:n arvo rivillä ti.
• Jos Y on funktionaalisesti riippuva X:stä, niin
( t1, t2 R): Jos t1[X] = t2[X] niin myös t1[Y] = t2[Y]
•
•
•
•
X ’määrää’ yksikäsitteisesti/funktionaalisesti Y:n.
FD on relaation attribuuttien semanttinen ominaisuus.
Sitä ei voida päätellä relaation hetkellisestä sisällöstä.
FD kuvaa sääntöä, jonka pitää aina olla voimassa tietyssä
relaatiossa (kaikilla instansseilla). Sitä tulisi pystyä
valvomaan automaattisesti.
3-2-Normalisointi
Teuhola-2012
147
Normalisoinnin yleiset periaatteet
• Normalisointi:
– Redundanssin minimointi, päivitysongelmien poisto
– Relaatiokaavojen jako projektioilla pienempiin osiin
siten, että normaalimuodoille asetetut ehdot
täyttyvät.
• Relaatiokaavojen tulisi yhdessä toteuttaa:
– Häviötön (lossless) ja ’ei-lisäävä’ (nonadditive)
liitos:
Infoa ei häviä, eikä synny vääriä monikoita. Tärkeä!
– Riippuvuuksien säilymisominaisuus: Kukin
alkuperäinen FD esiintyy jossakin normalisoidussa
relaatiossa. Tätä ei voida aina taata.
3-2-Normalisointi
Teuhola-2012
148
Normaalimuodot
• 1 NF, 2 NF, 3 NF ja BCNF perustuvat
avaimiin ja funktionaalisiin riippuvuuksiin.
• 4 NF perustuu moniarvoisiin riippuvuuksiin.
• 5 NF perustuu liitosriippuvuuksiin.
• Tavoitteena on käytännössä BCNF.
– Jos BCNF ei ole mahdollinen, niin 3 NF
– 1 NF ja 2 NF eivät riitä
3-2-Normalisointi
Teuhola-2012
149
Avaimista ja attribuuteista
• Avaimet
– Superavain: Attribuutti(joukko), joka identifioi
monikot, eli eri monikoilla on eri superavainarvot.
– Avain: minimaalinen superavain (ei turhia attrib.)
Samassa relaatiossa voi olla useita avaimia;
näistä ehdokasavaimista valitaan jokin relaation
pääavaimeksi.
• Attribuutit jaetaan kahteen ryhmään:
– Avainattribuutti (prime attribute) on jokin
(ehdokas-)avain tai sen osa.
– Muut ovat ei-avainattribuutteja (non-prime attribute)
3-2-Normalisointi
Teuhola-2012
150
Relaation (R) avainten (K) päättely
Algoritmi:
1. Asetetaan aluksi K = { R:n kaikki attribuutit }
2. Toistetaan seuraavaa:
–
–
Jos K:n jokin attribuutti a on funktionaalisesti riippuva joistain
K–{a}:n attribuuteista, niin poistetaan a K:sta.
Muuten lopetetaan; K on avain.
Huom! Lopputulos riippuu attribuuttien tutkimisjärjestyksestä. Kannattaa aloittaa niistä, jotka eivät
esiinny minkään FD:n vasemmalla puolella.
3-2-Normalisointi
Teuhola-2012
151
Esimerkki
Suoritus (Opno, OpNimi, Kurssi-id, Knimi, Sali,
Relaatio:
Ajankohta, Laitos, Arvosana)
FD:t:
(Sali, Ajankohta)  Laitos
(Opno, Ajankohta)  Sali
(Opno, Ajankohta)  Kurssi-id
(Opno, Ajankohta)  Arvosana
Opno  OpNimi
Kurssi-id  Knimi
Attribuuttien poisto avaimesta (eräs järjestys):
OpNimi, Knimi, Laitos, Sali, Kurssi-id, Arvosana
Avain:
(Opno, Ajankohta)
3-2-Normalisointi
Teuhola-2012
152
1 NF
• Kuuluu oikeastaan relaation määritelmään
(’flat’ relation).
• Attribuuttien arvot atomisia
• Ei yhdistettyjä tai moniarvoisia attribuutteja
• On myös ehdotettu yleistettyä mallia:
Non-First-Normal-Form (NF2 eli 1NF):
– Relaatio voi olla hierarkkinen.
– Se saadaan 1NF-muotoon unnest-operaatiolla.
3-2-Normalisointi
Teuhola-2012
153
Esimerkki: Normalisointi 1NF 1NF
Osasto
(1NF)
OsNo
OsNimi
JohtHetu
ToimiPaikka
11
Tutkimus
123456
Turku
Salo
22
Varasto
234567
Turku
Kaarina
unnest
Osasto
(1NF)
Huom.
Pääavain
muuttui!
OsNo
OsNimi
JohtHetu
ToimiPaikka
11
Tutkimus
123456
Turku
11
Tutkimus
123456
Salo
22
Varasto
234567
Turku
22
Varasto
234567
Kaarina
3-2-Normalisointi
Teuhola-2012
154
Esimerkki 3-tasoisesta 1NF-relaatiosta
OsastoNo OsNimi
11
Tutkimus
22
Hallinto
3-2-Normalisointi
Työntekijät
Hetu Nimi Lapset
Lnimi
1234 Aho Anni
Arto
2345 Elo
Eeva
Eero
3456 Oja
Oili
Olli
Teuhola-2012
155
2 NF (yleinen muoto)
• Relaatiokaava R on 2NF, jos mikään ei-avain-attribuutti
ei ole osittain riippuvainen mistään R:n avaimesta.
• Osittainen riippuvuus: X  Y, missä X  avain,
ja avain on usean attribuutin yhdelmä.
• Täydellinen riippuvuus: X’  Y, missä X’ on avain, eikä
X’:sta voida poistaa attribuutteja, menettämättä FD-omin.
• Normalisointi 2NF:ään: Jaetaan R useampaan 2NFrelaatioon siten, että ei-avain-attribuutit tulevat samaan
relaatioon avaimen sen osan kanssa, josta ne ovat
täydellisesti riippuvaisia.
• Esim. R(A,B,C) ja BC: Tulos R1(A,B), R2(B,C)
3-2-Normalisointi
Teuhola-2012
156
Esimerkki: 2NF  2NF
Osasto
OsNo
OsNimi
JohtHetu
ToimiPaikka
11
Tutkimus
123456
Turku
11
Tutkimus
123456
Salo
22
Varasto
234567
Turku
22
Varasto
234567
Kaarina
Osasto’
OsNo
OsNimi
JohtHetu
11
Tutkimus
123456
22
Varasto
234567
3-2-Normalisointi
Osastonimi
ja johtajan
hetu toistuvat
kunkin toimipisteen
kohdalla
OsNo
ToimiPaikka
11
Turku
11
Salo
22
Turku
22
Kaarina
Teuhola-2012
OsastoPaikat
157
3 NF (yleinen muoto)
• Relaatiokaava R on 3NF, jos kullekin FD:lle
X  A on voimassa joko
a) X on R:n superavain, tai
b) A kuuluu johonkin avaimeen R:ssä
• Käytännön merkitys: 3NF kieltää ei-avainattribuuttien transitiiviset riippuvuudet avaimesta.
• Normalisointi 3NF:ään: Dekompositio.
• Esim. R(A,B,C) ja AB sekä BC (jolloin AC)
Tulos R1(A,B), R2(B,C)
3-2-Normalisointi
Teuhola-2012
158
Esim.
Työntekijä
(3NF)
Työntekijä’
Hetu
Nimi
Osoite
OsNo
OsNimi
JohtHetu
123456
Aalto
Turku
11
Tutkimus
987654
234567
Laine
Raisio
11
Tutkimus
987654
345678
Järvi
Kaarina
22
Varasto
876543
456789
Lahti
Turku
22
Varasto
876543
567890
Virta
Raisio
22
Varasto
876543
Hetu
Nimi
Osoite
OsNo
Osasto
123456
Aalto
Turku
11
OsNo
OsNimi
JohtHetu
234567
Laine
Raisio
11
11
Tutkimus
987654
345678
Järvi
Kaarina
22
22
Varasto
876543
456789
Lahti
Turku
22
567890
Virta
Raisio
22
3-2-Normalisointi
Teuhola-2012
159
BCNF eli Boyce-Codd-normaalimuoto
• Relaatio R on BCNF:ssä, jos FD:n X  A ollessa
voimassa X on R:n superavain
• Auttaa poistamaan ongelmia esimerkiksi, kun
relaatiolla on
– useita ehdokasavaimia ja
– ehdokasavaimet ovat yhdistettyjä ja
– ehdokasavaimet ovat 'päällekkäisiä' eli samoja kenttiä
on useissa ehdokasavaimissa
• Huom! Riippuvuudet eivät välttämättä säily, vaan
jakautuvat normalisoinnissa usean relaation osalle
3-2-Normalisointi
Teuhola-2012
160
Esimerkki: BCNF BCNF
R1
R2
Opisk
Kurssi
Opett
Opisk Opett
Matti
Kannat
Aalto
Matti
Aalto
Matti
TRAK
Laine
Matti
Laine
Liisa
Kannat
Virta
Liisa
Virta
Liisa
OhjPK
Salmi
Liisa
Salmi
Liisa
TRAK
Laine
Liisa
Laine
Maija
Kannat
Virta
Maija
Virta
Maija
OhjPK
Järvi
Maija
Järvi
3-2-Normalisointi
Teuhola-2012
Opett Kurssi
Aalto
Kannat
Laine TRAK
Virta
Kannat
Salmi OhjPK
Järvi
OhjPK
161
Edellisen esimerkin tulkinta
• Opettaja opettaa vain yhtä kurssia
• Samaa kurssia voi (eri vuosina) pitää usea eri
opettaja.
• Opiskelija voi osallistua usealle kurssille, ja
samalle kurssille osallistuu useita opiskelijoita.
• Opiskelija osallistuu tietylle kurssille vain kerran,
jolloin opettaja tulee kiinnitettyä.
3-2-Normalisointi
Teuhola-2012
162
Dekompositio BCNF-muotoon
• Häviötön BCNF-algoritmi:
Jos X  Y rikkoo BCNF:n relaatiossa Q, tee
dekompositio relaatioiksi
R1 { XY }, R2 { Q–Y }
• Pitäisi varmistua siitä, että informaatiosisältö
säilyy dekompositiossa.
3-2-Normalisointi
Teuhola-2012
163
Häviötön liitos –testi
• Ns. NJB-ominaisuus, eli Nonadditive Join Test for
Binary Decompositions:
{R1, R2} on R:n häviötön dekompositio silloin
ja vain silloin kun (R1R2)  (R1 R2) pätee
tai symmetrisesti: (R1R2)  (R2 R1) pätee
• Huom.1. Tässä leikkaus ja erotus ovat attribuuttijoukkojen, eivät relaatioalgebran operaatioita
• Huom.2. Sääntö pätee koko R:ään myös silloin
kun R:n osarelaatiot jaetaan edelleen osarelaatioihin, joilla on häviötön liitosominaisuus.
3-2-Normalisointi
Teuhola-2012
164
Häviöttömyys edellisessä esimerkissä
•
•
•
•
•
•
•
Q = {Opiskelija, Kurssi, Opettaja}
X = {Opettaja}, Y = {Kurssi}
X Y = {Opettaja, Kurssi} = R1
Q  Y = {Opiskelija, Opettaja} = R2
R1  R2 = {Opettaja}
R1  R2 = {Kurssi}
(R1  R2)  (R1  R2) pätee, joten {R1, R2} on
häviötön dekompositio
3-2-Normalisointi
Teuhola-2012
165
Esimerkki häviöllisestä dekompositiosta
• Q(Opisk, Kurssi, Opett) 
R1’(Opisk, Kurssi), R2’(Opett, Kurssi)
• R1’  R2’ = {Kurssi}
• R1’  R2’ = {Opisk}
• R2’  R1’ = {Opett}
• {Kurssi}  {Opisk} ei päde
• {Kurssi}  {Opett} ei päde
• Siis liitos R1’ * R2’ voi tuottaa vääriä monikoita
3-2-Normalisointi
Teuhola-2012
166
Yleistettyjä riippuvuuksia ja korkeampia
normaalimuotoja
• Moniarvoinen riippuvuus (MVD):
4. normaalimuodon (4NF) perusta
• Liitosriippuvuus (JD):
5. normaalimuodon (5NF) perusta
• Sisältymisriippuvuus:
Eri relaatioissa sijaitsevien attribuuttien välinen
• Template-riippuvuus:
Yleisempi oikeellisuusehto (vrt. ASSERTION)
3-2-Normalisointi
Teuhola-2012
167
Moniarvoinen riippuvuus
(Multivalued dependency, MVD)
• FD:n yleistys: samaan attribuuttiarvoon voi liittyä
useita toisen attribuutin arvoja.
• Esim. Osallistuu(OpiskNo, Kurssi)
– Opiskelija mukana monella kurssilla
– Kurssilla useita opiskelijoita
• 1NF:stä seuraa, että arvoja joudutaan toistamaan.
• Toiston haitallisuus riippuu siitä, mitä muita
attribuutteja relaatiossa on. (Yo. relaatio on OK)
3-2-Normalisointi
Teuhola-2012
168
Ongelmia aiheuttava MVD-tapaus
• Jos tiettyyn X-arvoon x liittyy joukko Y-arvoja
{y1...yk} ja joukko Z-arvoja {z1...zn} toisistaan
riippumatta, niin relaatiossa on rivit, joissa x on
liittyneenä kaikkiin yizj-pareihin (yhteensä kn kpl).
• Kyseessä on eräänlainen relaation sisäinen
karteesinen tulo.
• Seurauksena on suuri määrä redundanssia.
3-2-Normalisointi
Teuhola-2012
169
MVD:n merkintätapa
• X -» Y, eli X-arvoon liittyy tietty Y-arvojen joukko
muista attribuuteista riippumatta. Tällöin
sanotaan että attribuutti(joukko) X ’määrää’
moniarvoisesti attribuutti(joukon) Y.
• Symmetrisyyden vuoksi:
Jos X -» Y niin X -» Z, missä Z = R(X  Y):
Näitä yhdessä merkitään:
X -» Y | Z
3-2-Normalisointi
Teuhola-2012
170
Esimerkki moniarvoisesta riippuvuudesta
Opetus
• Kurssilla on useita
opettajia ja opiskelijoita:
Kurssi ->>
Opettaja | Opiskelija
• Huom! Kaikki
attribuutit mukana
pääavaimessa
• Ei funktionaalisia
riippuvuuksia
Kurssi
Opettaja
Opiskelija
TRAK
Aalto
Matti
TRAK
Aalto
Liisa
TRAK
Salmi
Matti
TRAK
Salmi
Liisa
OhjPK
Aalto
Liisa
OhjPK
Aalto
Maija
OhjPK
Laine
Liisa
OhjPK
Laine
Maija
3-2-Normalisointi
Teuhola-2012
171
MVD formaalisesti
• Olkoon R:ssä attr.joukot X, Y ja Z = R(X  Y).
• (X -» Y|Z):sta seuraa:
Jos monikot t1,t2  R siten että t1[X] = t2[X],
niin monikot t3,t4R siten että
– t3[X] = t4[X] = t1[X] = t2[X]
– t3[Y] = t1[Y] ja t4[Y] = t2[Y]
– t3[Z] = t2[Z] ja t4[Z] = t1[Z]
Vrt. edellinen esimerkki
3-2-Normalisointi
Teuhola-2012
172
Moniarvoisen riippuvuuden merkitys
• Formaalin määritelmän mukaan X -» Y kun
attribuutti(ryhmä) X määrää yksinään
täydellisesti joukon attribuutti(ryhmän) Y arvoja,
eikä määrääminen riipu muista attribuuteista Z.
• FD on MVD:n erikoistapaus.
• X -» Y on triviaali MVD, jos
a) Y on X:n alijoukko (eli Y X)
tai
b) X  Y = R
3-2-Normalisointi
Teuhola-2012
173
4 NF
• Relaatiokaava on 4NF suhteessa riippuvuuksien joukkoon F, jos kaikissa ei-triviaaleissa
MVD:issä X -» Y pätee: X on superavain.
• Käytännössä tämä tarkoittaa, että ei-triviaalin
MVD:n on oltava FD (MVD:n erikoistapaus).
• 4NF:n tyypillinen rikkomistapaus on relaatio,
joka sisältää kaksi M:N-suhdetta.
3-2-Normalisointi
Teuhola-2012
174
Häviötön dekompositio 4NF:ään
• Jos R:ssä on voimassa X -» Y, niin R1 = XY ja
R2 = RY on häviötön dekompositio.
• Formaalisesti:
{R1, R2} on R:n häviötön dekompositio silloin ja
vain silloin kun (R1R2) -» (R1 R2)
tai symmetrisesti: (R1R2) -» (R2 R1)
[Vrt. Aikaisempi teoreema FD:lle]
3-2-Normalisointi
Teuhola-2012
175
Esimerkki dekompositiosta 4NF:ään
Opetus
Kurssi Opett
Opisk
TRAK
Aalto
Matti
KurssiOpett
KurssiOpisk
TRAK
Aalto
Liisa
Kurssi Opett
Kurssi
Opisk
TRAK
Salmi
Matti
TRAK
Aalto
TRAK
Matti
TRAK
Salmi
Liisa
TRAK
Salmi
TRAK
Liisa
OhjPK
Aalto
Liisa
OhjPK
Aalto
OhjPK
Liisa
OhjPK
Aalto
Maija
OhjPK
Laine
OhjPK
Maija
OhjPK
Laine
Liisa
OhjPK
Laine
Maija
3-2-Normalisointi
Teuhola-2012
176
Liitosriippuvuus JD (Join Dependency)
• Aina ei ole mahdollista tehdä 'häviötön liitos' dekompositiota kahdeksi relaatiokaavaksi,
mutta kylläkin useammaksi.
• JD(R1 , R2 , .... , Rn) relaatiokaavalle R
määrittelee, että sen dekompositio osiin
R1, ..., Rn on häviötön kaikissa tilanteissa, eli:
pR1(R )  pR2(R )  ...  pRn(R) = R
3-2-Normalisointi
Teuhola-2012
177
Esimerkki liitosriippuvuudesta
• Relaatio R (Toimittaja, Osa, Projekti)
• Sääntö:
Jos toimittaja t toimittaa osaa o johonkin projektiin
ja toimittaja t toimittaa jotain osaa projektiin p
ja joku toimittaa osaa o projektiin p,
niin myös toimittaja t toimittaa osaa o projektiin p.
• Tällöin on voimassa liitosriippuvuus
JD( {toimittaja, osa}, {osa, projekti}, {toimittaja, projekti} )
3-2-Normalisointi
Teuhola-2012
178
Esimerkki liitosriippuvuudesta: häviötön
dekompositio
R
R1
Toimitt.
Osa
Projekti
t1
o1
p2
t2
o1
p1
t1
o2
p1
t1
o1
p1
Toimitt.
Osa
t1
o1
o1
p2
t1
p2
t2
o1
o1
p1
t2
p1
t1
o2
o2
p1
t1
p1
R2 Osa Projekti R3 Toimitt. Projekti
3-2-Normalisointi
Teuhola-2012
179
Esimerkki liitosriippuvuudesta:
parittaiset liitokset häviöllisiä
R1 * R2
R2 * R3
R1 * R3
Toimitt
Osa
Proj
Toimitt
Osa
Proj
Toimitt
Osa
Proj
t1
o1
p2
t1
o1
p2
t1
o1
p2
t1
o1
p1
t2
o1
p1
t1
o1
p1
t2
o1
p2
t2
o2
p1
t2
o1
p1
t2
o1
p1
t1
o1
p1
t1
o2
p2
t1
o2
p1
t1
o2
p1
t1
o2
p1
Kaikissa parittaisissa liitoksissa on ylimääräinen monikko
(lihavoitu). Vain kaikkien kolmen liitos (R1 * R2 * R3)
tuottaa alkuperäisen relaation R.
3-2-Normalisointi
Teuhola-2012
180
5 NF (PJNF, Project-Join NF)
• Relaatiokaava on 5NF, jos kaikille
liitosriippuvuuksille JD(R1 , R2 , .... , Rn),
jokainen Ri on R:n (super)avain.
• Liitosriippuvuuksien havaitseminen on
käytännössä vaikeaa ja vaatii syvällistä
asiantuntemusta sovelluksen semantiikasta.
• 5NF ei pidetä tärkeänä käytännön kannalta.
3-2-Normalisointi
Teuhola-2012
181
Sisältymisriippuvuus
• Vallitsee eri relaatioiden vastinattribuuttien
välillä, merkitään R.X < S.Y.
• Voimassa, jos pX(R)  pY(S) kaikissa
R:n ja S:n sallituissa tiloissa.
• Käytännön tilanteet:
– Viiteavaimet, aliluokkasuhteet.
• Ei uusia normaalimuotoja, mutta tärkeitä
rajoitteita (assertion, foreign key, ym.)
relaatiokaavaa täydentämässä
3-2-Normalisointi
Teuhola-2012
182
Esimerkki sisältymisriippuvuuksista
• Relaatiokaava yliopiston piiristä:
Työntekijä (Hetu, Osoite, Palkka)
Opettaja (Hetu, Oppiarvo, Oppiaine)
Kurssi (KurssiId, OpeHetu, Laajuus, Periodi)
• Sisältymisriippuvuudet:
Opettaja.Hetu < Työntekijä.Hetu
Kurssi.OpeHetu < Opettaja.Hetu
3-2-Normalisointi
Teuhola-2012
183
Template-riippuvuus
• Template = malli, kaava
• Template-riippuvuus perustuu sääntöihin:
Jos tietokannassa on tietynlaiset monikot,
niin siinä on myös tietyt (muut) monikot.
• Riippuvuuksien valvonta tietokantakaavassa
esim. väittämillä eli rajoitteilla (assertion) tai
herätteillä (trigger)
3-2-Normalisointi
Teuhola-2012
184
Esimerkki template-riippuvuudesta
• ’Jos opettajan äidinkieli on muu kuin suomi, hän luennoi
englanniksi’:
Kurssi (KurssiId, KurssiNimi, OpeHetu, LuentoKieli)
Opettaja (Hetu, Osoite, Äidinkieli)
• Riippuvuuden määrittely:
Jos (a, b, c, d)  Kurssi
ja (c, e, f)  Opettaja
ja f  ’suomi’
niin d = ’englanti’
3-2-Normalisointi
Teuhola-2012
185
Yhteenveto normalisoinnista
• Joukko sääntöjä, jotka laadukkaan relaatiokaavan tulee
toteuttaa. Säännöillä pyritään mm.
–
–
–
–
–
yksinkertaisiin relaatioihin,
redundanssin minimointiin,
null-arvojen välttämiseen,
päivitysvirheiden estämiseen,
väärien monikoiden estämiseen.
• ER-kaavio  relaatiokaava –muunnos tuottaa
useimmiten hyvän lopputuloksen; normalisointi on
lähinnä laadunvarmistuskeino.
3-2-Normalisointi
Teuhola-2012
186
Normalisoinnin kääntöpuoli
• Normalisoinnissa ongelmat ratkaistaan
dekompositiolla, mikä johtaa suureen määrään
relaatioita.
• Käsittelytehtävissä relaatiot joudutaan usein
liittämään takaisin yhteen, mikä on hidasta.
• Joskus tehokkuussyistä tehdään ns.
denormalisointia, eli talletetaan valmiiksi
liitettyjä relaatioita. Tämän käyttöä pitää harkita
tarkasti.
3-2-Normalisointi
Teuhola-2012
187