Relasjonsmodellen, del II

1. Relasjonsmodellen, del II Eksempelbase side 2 Relasjonsalgebra side 3SQL-operatorer side 4 Seleksjon og projeksjon side 5 Produkt side 6 Forening (join) side 7-10 Settoperasjonene side 11-13Grupperingsoperatoren divisjon side 14-15 Hva er en relasjonsdatabase? 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

2. Eksempelbase leveranse leverandor produkt Sqlscript vedlagt Datamodellering og databaserElse Lervik, spetember 2012

3. 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 databaserElse Lervik, spetember 2012

4. SQL-operatorer • Sammenlikningsoperatorer: = > < >= <= <> IN • <> betyr ”ikke lik” • Aritmetiske operatorer: + - * / • / 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): • Finn produkter med fargekode ’rød’ og som veier mindre enn 15 gramselect * from produkt where kode = 'rød' and vekt < 15; • Finn leveranser på enten 100, 200 eller 400 enheterselect * from leveranse where antallin (100, 200, 400); • Finn leveranser som verken er 100 eller 200 enheterselect * from leveranse where antallnot in(100, 200); Datamodellering og databaserElse Lervik, spetember 2012

5. Seleksjon, også kalt restriksjon, lager en ny relasjon ut fra bestemte tupler i en eksisterende 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; Seleksjon og projeksjon seleksjon projeksjon operasjon(r1:s:r, r2:s:r, ...); relasjon Datamodellering og databaserElse Lervik, spetember 2012

6. 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 CROSS(leveranse::, leverandor::, R::); SQL: SELECT * FROM leveranse, leverandor; Sjekk resultatet fra SQL-setningen! Produkt seleksjon projeksjon operasjon(r1:s:r, r2:s:r, ...); relasjon x x a a y y a b z z a c p p a B q q a x b A y b z b p b q b x c c y z c p c R q c Datamodellering og databaserElse Lervik, spetember 2012

7. 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. 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) de to attributtene har samme verdi Naturlig forening (natural join) det samme som likhetsforening, men duplikatattributtene er fjernet Generelt, k er fellesattributtet/-ene 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) Indre forening (inner join) seleksjon projeksjon operasjon(A:s:r:k, B:s:r:k, R:s:r); felles attributt relasjon Datamodellering og databaserElse Lervik, spetember 2012

8. Eksempelbase, lite data – for å illustrere SQL-forening leverandor leveranse produkt Datamodellering og databaserElse Lervik, spetember 2012

9. 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 databaserElse Lervik, spetember 2012

10. Ytterforening seleksjon projeksjon operasjon(A:s:r:k, B:s:r:k, R:s:r); • 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: • 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) felles attributt relasjon Datamodellering og databaserElse Lervik, spetember 2012

11. Å 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 databaserElse Lervik, spetember 2012

12. Eksempelbase leveranse leverandor produkt Sqlscript vedlagt Datamodellering og databaserElse Lervik, spetember 2012

13. Settoperasjonen UNION seleksjon projeksjon 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: • 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 felles attributt relasjon Datamodellering og databaserElse Lervik, spetember 2012

14. Settoperasjonen SNITT seleksjon projeksjon operasjon(A:s:r:k, B:s:r:k, R:s:r); • Relasjonsoperatoren snitt (intersect) lager en ny 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: SELECT lev_by FROM leverandor INTERSECT SELECT prod_by FROM produkt • Resultat: LEV_BY --------------- Lillehammer Porsgrunn felles attributt relasjon Datamodellering og databaserElse Lervik, spetember 2012

15. Settoperasjonen DIFFERANSE seleksjon projeksjon 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; • 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; felles attributt relasjon LEV_BY --------------- Arendal PROD_BY --------------- Risør Datamodellering og databaserElse Lervik, spetember 2012

16. 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 databaserElse Lervik, spetember 2012

17. Divisjon, eksempel ”r” ”c” ”g” ”c” leveranse::lev_nr prod_nr:lev_nr:prod_nr produkt::prod_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) R::lev_nr Datamodellering og databaserElse Lervik, spetember 2012

18. 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 databaserElse Lervik, spetember 2012