Transcript Forelesning

LC238D Datamodellering og databaser
http://www.aitel.hist.no/fag/_dmdb/
Relasjonsmodellen, del II
Eksempelbase
Relasjonsalgebra
SQL-operatorer
Seleksjon og projeksjon
Produkt
Forening (join)
Settoperasjonene
Grupperingsoperatoren divisjon
Hva er en relasjonsdatabase?
side 2
side 3
side 4
side 5
side 6
side 7-10
side 11-13
side 14-15
side 16
Basisreferansen er:
E.F. Codd (IBM): ”A Relational Model of Data for Large Shared Data Banks”
Comm. ACM, juni 1970
Se ellers læreboka, side 59-76
Else Lervik, september 2012
Forelesning 3, uke 36
Eksempelbase
leveranse
leverandor
lev_nr
lev_navn
status
lev_by
lev_nr
prod_nr
antall
1
Svendsen
20
Lillehammer
1
1
300
2
Jensen
10
Porsgrunn
1
2
200
3
Bø
30
Porsgrunn
1
3
400
4
Christiansen
20
Lillehammer
1
4
200
5
Andersen
30
Arendal
1
5
100
2
1
300
2
2
400
3
2
200
4
2
200
4
4
300
4
5
400
produkt
prod_nr
prod_navn
kode
vekt
prod_by
1
synåler
rød
12
Lillehammer
2
binders
grønn
17
Porsgrunn
3
skruer
blå
17
Risør
4
skruer
rød
14
Lillehammer
5
knapper
blå
12
Porsgrunn
6
spiker
rød
19
Lillehammer
Datamodellering og databaser
Else Lervik, spetember 2012
Sqlscript vedlagt
side 2
Relasjonsalgebra
•
•
Et sett med operasjoner som utføres på relasjoner
Resultatet er alltid en ny relasjon, dvs at vi kan kombinere og nøste
operasjonene etter ønske
– De egentlige algebraoperatorene:
• seleksjon, projeksjon, produkt og forening
– Settoperatorene:
• union, snitt, differanse
– Grupperingsoperatoren:
• divisjon
– Kan uttrykkes slik (en av flere mulige notasjoner)
• operasjon(r1:s:r.., r2:s:r..., ...);
• r1, r2, osv er relasjoner, der den siste er resultatrelasjonen
• s uttrykker horisontalt utvalg (seleksjon), mens r uttrykker vertikalt utvalg (projeksjon,
attributter), flere parametere kommer etter hvert
•
•
Bruker select-setningen til å utføre operasjonene i et SQL-basert
databasesystem
Med unntak av divisjon er alle operasjonene enkle å utføre i SQL.
Datamodellering og databaser
Else Lervik, spetember 2012
side 3
SQL-operatorer
•
Sammenlikningsoperatorer: = > < >= <= <> IN
–
•
Aritmetiske operatorer: + - * /
–
•
•
•
<> betyr ”ikke lik”
/ utfører heltallsdivisjon hvis operandene er heltall
Sammensatte logiske uttrykk lages vhja AND, OR og NOT. AND har høyere
prioritet enn OR – slik det også er i Java.
Parenteser kan brukes til å overstyre prioritetene.
Eksempler (kan bruke OR i eksempel 2 og 3):
1. Finn produkter med fargekode ’rød’ og som veier mindre enn 15 gram
select * from produkt where kode = 'rød' and vekt < 15;
2. Finn leveranser på enten 100, 200 eller 400 enheter
select * from leveranse where antall in (100, 200, 400);
3. Finn leveranser som verken er 100 eller 200 enheter
select * from leveranse where antall not in(100, 200);
Datamodellering og databaser
Else Lervik, spetember 2012
side 4
seleksjon
projeksjon
Seleksjon og projeksjon
operasjon(r1:s:r, r2:s:r, ...);
•
Seleksjon, også kalt restriksjon, lager en ny
relasjon ut fra bestemte tupler i en eksisterende
relasjon.
relasjon
– begrenser antall rader
– eksempel:
• SELECT(leverandor:(status > 20):*, R::);
– SQL:
• SELECT * FROM leverandor WHERE status > 20;
•
En projeksjon eller reduksjon lager en ny relasjon ut fra
bestemte attributter i en eksisterende relasjon.
– begrenser antall kolonner
– eksempel:
• PROJECT(leverandor::lev_nr lev_navn, R::);
– SQL:
• SELECT DISTINCT lev_nr, lev_navn FROM leverandor;
Datamodellering og databaser
Else Lervik, spetember 2012
side 5
seleksjon
projeksjon
Produkt
operasjon(r1:s:r, r2:s:r, ...);
•
Det kartesiske produkt eller kryssprodukt lager en
ny relasjon R med alle mulige sammensettinger av
tupler fra to relasjoner A og B.
– antall tupler i resultatrelasjonen er lik antall tupler i
A multiplisert med antall tupler i B
– antall attributter i resultatrelasjonen er lik summen
av antall attributter i A og antall attributter i B
•
CROSS(A::, B::, R::);
– Eksempel: Ønsker å få ut hele leveransetabellen med
alle opplysninger om leverandørene
relasjon
x
y
z
p
q
a
b
c
B
A
• CROSS(leveranse::, leverandor::, R::);
– SQL:
• SELECT * FROM leveranse, leverandor;
– Sjekk resultatet fra SQL-setningen!
R
Datamodellering og databaser
Else Lervik, spetember 2012
x
y
z
p
q
x
y
z
p
q
x
y
z
p
q
side 6
a
a
a
a
a
b
b
b
b
b
c
c
c
c
c
seleksjon
projeksjon
Indre forening (inner join)
operasjon(A:s:r:k, B:s:r:k, R:s:r);
•
Forening (join) lager en ny relasjon med sammensettinger
av tupler fra to relasjoner på et felles attributt, slik at
hver sammensetting tilfredsstiller en gitt betingelse.
–
–
felles
attributt
relasjon
Vanligvis
• «De to relasjonene» er to forskjellige relasjoener
• Fellesatributtet er primærnøkkel i den ene relasjonen og fremmednøkkel i den andre.
• Fellesattributtet har samme navn i de to relasjonene.
Men det behøver ikke være slik.
• Fellesattributtet må defineres!
•
Likhetsforening (equijoin)
–
•
Naturlig forening (natural join)
–
•
det samme som likhetsforening, men duplikatattributtene er fjernet
Generelt, k er fellesattributtet/-ene
–
•
de to attributtene har samme verdi
JOIN(A:s:r:k, B:s:r:k, R:s:r)
Eksempel, naturlig forening
–
–
hele leveransetabellen med alle opplysninger om leverandørene:
JOIN(leverandor:::lev_nr, leveranse:::lev_nr, R::lev_nr lev_navn status lev_by prod_nr antall)
Datamodellering og databaser
Else Lervik, spetember 2012
side 7
Eksempelbase, lite data – for å illustrere SQL-forening
leverandor
leveranse
lev_nr
lev_navn
status
lev_by
lev_nr
prod_nr
antall
1
Svendsen
20
Lillehammer
1
1
300
2
Jensen
10
Porsgrunn
2
1
300
3
Bø
30
Porsgrunn
produkt
prod_nr
prod_navn
kode
vekt
prod_by
1
synåler
rød
12
Lillehammer
2
binders
grønn
17
Porsgrunn
Datamodellering og databaser
Else Lervik, spetember 2012
side 8
Indre forening (inner join) , SQL
SQL:
Prøv: SELECT * FROM leverandor, leveranse;
Resultat: Kartesisk produkt
Hva må gjøres?
SELECT * FROM leverandor, leveranse
WHERE leverandor.lev_nr = leveranse.lev_nr;
Naturlig forening blir dermed slik:
SELECT leverandor.*, prod_nr, antall FROM leverandor, leveranse
WHERE leverandor.lev_nr = leveranse.lev_nr;
eller:
SELECT leverandor.*, prod_nr, antall FROM leverandor JOIN leveranse
ON (leverandor.lev_nr = leveranse.lev_nr);
eventuelt (Oracle ok, Java DB ok fra versjon 10.6)
SELECT * FROM leverandor NATURAL JOIN leveranse;
Vær klar over at NATURAL JOIN kun fungerer korrekt dersom navnene på kolonnene i de
to involverte tabellene er de samme. Det går altså ikke nødvendigvis på kopling primær- fremmednøkkel.
Indre forening, dvs der betingelsen blir evaluert til sann.
Datamodellering og databaser
Else Lervik, spetember 2012
side 9
seleksjon
projeksjon
Ytterforening
• En venstre ytterforening er en likhetsforening,
men med det tillegg at alle tupler i venstre
relasjon skal være med. Der det mangler
sammenfallende verdier fra høyre relasjon,
er attributtene fra høyre relasjon gitt nullverdier.
• Eksempel:
operasjon(A:s:r:k, B:s:r:k, R:s:r);
felles
attributt
relasjon
– Vil også ha med leverandører som ikke leverer noe
– LEFT-OUTER-JOIN(leverandor:::lev_nr, leveranse:::lev_nr, R::lev_nr
lev_navn status lev_by prod_nr antall)
– SQL:
• SELECT leverandor.*, prod_nr, antall FROM leverandor
LEFT OUTER JOIN leveranse ON (leverandor.lev_nr = leveranse.lev_nr);
• Resultatet som før, men med følgende tilleggsrad:
3, Bø, 30, Porsgrunn, null, null
– Kan ikke sette opp denne spørringen uten å bruke ”LEFT JOIN”.
• Høyre ytterforening defineres tilsvarende.
• Full ytterforening (FULL OUTER JOIN) kombinerer venstre og høyre
ytterforening (Oracle ok, Java DB ikke ok)
Datamodellering og databaser
Else Lervik, spetember 2012
side 10
Å kombinere tre relasjoner via koplingsrelasjon (SQL)
• Problem: Lag en liste med alle leveransene og navn og nummer på
leverandør og produkt.
• Løsninger:
– SELECT lev_navn, leveranse.*, prod_navn FROM leverandor, leveranse,
produkt WHERE leverandor.lev_nr = leveranse.lev_nr AND
leveranse.lev_nr = produkt.lev_nr;
– SELECT lev_navn, leveranse.*, prod_navn FROM leverandor JOIN
(leveranse JOIN produkt ON (leveranse.prod_nr = produkt.prod_nr))
ON leverandor.lev_nr = leveranse.lev_nr;
Datamodellering og databaser
Else Lervik, spetember 2012
side 11
Eksempelbase
leveranse
leverandor
lev_nr
lev_navn
status
lev_by
lev_nr
prod_nr
antall
1
Svendsen
20
Lillehammer
1
1
300
2
Jensen
10
Porsgrunn
1
2
200
3
Bø
30
Porsgrunn
1
3
400
4
Christiansen
20
Lillehammer
1
4
200
5
Andersen
30
Arendal
1
5
100
2
1
300
2
2
400
3
2
200
4
2
200
4
4
300
4
5
400
produkt
prod_nr
prod_navn
kode
vekt
prod_by
1
synåler
rød
12
Lillehammer
2
binders
grønn
17
Porsgrunn
3
skruer
blå
17
Risør
4
skruer
rød
14
Lillehammer
5
knapper
blå
12
Porsgrunn
6
spiker
rød
19
Lillehammer
Datamodellering og databaser
Else Lervik, spetember 2012
Sqlscript vedlagt
side 12
seleksjon
projeksjon
Settoperasjonen UNION
operasjon(A:s:r:k, B:s:r:k, R:s:r);
•
•
•
Union lager en ny relasjon med tupler som finnes i den ene
eller begge av to relasjoner. Relasjonene må være
unionkompatible, dvs. at de har det samme antallet
attributter, og at attributtene er definert på samme domene
(har samme datatype).
UNION(A:s:r:k, B:s:r:k, R:s:r)
Eksempel:
–
–
–
–
relasjon
felles
attributt
Alle involverte byer
UNION(leverandor:::lev_by, produkt:::prod_by, R::);
SQL:
SELECT lev_by FROM leverandor
UNION
SELECT prod_by FROM produkt;
Resultat:
LEV_BY
--------------Arendal
Lillehammer
Porsgrunn
Risør
Datamodellering og databaser
Else Lervik, spetember 2012
side 13
seleksjon
projeksjon
Settoperasjonen SNITT
operasjon(A:s:r:k, B:s:r:k, R:s:r);
• Relasjonsoperatoren snitt (intersect) lager en ny
relasjon
relasjon med tupler som finnes i begge av to
relasjoner. Relasjonene må være
unionkompatible.
• INTERSECT(A:s:r:k, B:s:r:k, R:s:r)
• Eksempel:
• INTERSECT(leverandor:::lev_by, produkt:::prod_by, R::);
• SQL:
felles
attributt
SELECT lev_by FROM leverandor
INTERSECT
SELECT prod_by FROM produkt
• Resultat:
LEV_BY
--------------Lillehammer
Porsgrunn
Datamodellering og databaser
Else Lervik, spetember 2012
side 14
seleksjon
projeksjon
Settoperasjonen DIFFERANSE
operasjon(A:s:r:k, B:s:r:k, R:s:r);
•
•
•
Relasjonsoperatoren differanse lager en ny relasjon
med tupler som finnes i den første, men ikke i den
andre av to relasjoner. Relasjonene må være
unionkompatible.
DIFFERENCE(A:s:r:k, B:s:r:k, R:s:r)
Eksempel:
– Byer der det er leverandører, men ikke produkter
– DIFFERENCE(leverandor:::lev_by, produkt:::prod_by, R::);
– SQL:
SELECT lev_by FROM leverandor
EXCEPT
SELECT prod_by FROM produkt;
–
–
–
–
relasjon
felles
attributt
LEV_BY
--------------Arendal
OBS! Oracle krever at vi bruker MINUS istf. EXCEPT
Omvendt:
DIFFERENCE(produkt:::prod_by, leverandor:::lev_by, R::);
SQL:
SELECT prod_by FROM produkt
EXCEPT
SELECT lev_by FROM leverandor;
Datamodellering og databaser
Else Lervik, spetember 2012
PROD_BY
--------------Risør
side 15
Grupperingsoperatoren DIVISJON
• Operatoren krever at vi har to relasjoner. Den første må ha flere eller
like mange tupler som relasjon nummer to. Den første relasjonen, A,
har to attributter eller attributtgrupper, g og c. Tuplene i A grupperers
på g. Tuplene i B har attributter som er sammenliknbare med c.
Tuplenes c-del i hver gruppe testes så mot alle tuplene i B. Hvis alle Bs
tupler finnes for samme gruppe, vil gruppenes samlingsattributt(er) g,
inkluderes i resultatrelasjonen.
• DIVIDE(A:s:r:g:c, B:s:r:c, R:s:r);
• Eksempel:
– Hvem har leveranser på alle produktene?
– DIVIDE(leveranse::lev_nr prod_nr:lev_nr:prod_nr,
produkt::prod_nr:prod_nr, R::lev_nr)
– Denne operasjonen finnes ikke i SQL, men kan løses f.eks. ved å bruke
EXISTS, se egne SQL-forelesninger senere i kurset.
Datamodellering og databaser
Else Lervik, spetember 2012
side 16
”r”
”g”
Divisjon, eksempel
”c”
leveranse::
lev_nr prod_nr:lev_nr:prod_nr
Hvem har leveranser
på alle produktene?
DIVIDE(leveranse::lev_nr
prod_nr:lev_nr:prod_nr,
produkt::prod_nr:prod_nr,
R::lev_nr)
produkt::prod_nr:prod_nr
lev_nr
prod_nr
prod
_nr
1
1
1
1
2
1
3
1
4
1
5
2
1
2
2
3
2
4
2
4
4
4
5
Datamodellering og databaser
Else Lervik, spetember 2012
”c”
2
3
R::lev_nr
4
5
6
side 17
Hva er en relasjonsdatabase?
• Relasjonsmodellen er et teoretisk fundament for databaser.
• En database må tilfredsstille kravene i modellen for å kunne kalles en
relasjonsdatabase.
• Codd sier at et databasesystem er relasjonelt hvis det tilfredsstiller
følgende:
– Brukeren av systemet skal oppfatte dataene som en samling med
relasjoner (tabeller som tilfredsstiller visse krav), og intet annet.
– Systemet må minst tilby følgende operasjoner: Seleksjon, projeksjon,
forening (join) uten på forhånd å måtte definere fysiske aksessveier for å
kunne bruke disse operasjonene.
• Detaljert i tolv regler ang datarepresentasjon, aksessveier,
datakataloger, databasespråk, virtuelle relasjoner (views), fysisk og
logisk datauavhengighet og integritet.
• I hht denne definisjonen finnes det ikke relasjonelle systemer på
markedet i dag. Kontroversielt. Kun en akademisk øvelse? Uansett en
solid teoretisk basis og et mål å strekke seg etter.
Datamodellering og databaser
Else Lervik, spetember 2012
side 18