Løsningsforslag til kapittel 11

Oppgave 1

Distribuerte databasesystemer.

Anta T2 inneholder 10 ganger så mange rader som T1 (kun et eksempel). Spørreresultatet vil ha like mange rader som det er rader i T1. Fordi en rad i spørreresultatet kun inneholder primærnøkkelen i T1 og et heltall, antar vi at samlet plassbehov for spørreresultatet er 20% av plassbehovet til T1.

Anta at spørringen blir initiert i Trondheim (brukeren sitter her). Noen strategier:

1. Hele T1 blir samlet i Trondheim, der spørringen blir utført og vist: 90% av T1 blir overført til Trondheim.
2. Hele T1 blir samlet i Bergen, der spørringen blir utført, før resultatet blir overført til Trondheim: 70% av T1 blir overført til Bergen, og deretter blir spørreresultatet (=20% av T1) overført til Trondheim. Totalt må data svarende til 90% av T1 bli overført.
3. Hele T1 blir samlet i Oslo, der spørringen blir utført, før resultatet blir overført til Trondheim: 40% av T1 blir overført til Oslo, og deretter blir spørreresultatet (=20% av T1) overført til Trondheim. Totalt må data svarende til 60% av T1 bli overført.
4. Spørringen blir utført på hvert sted for aktuelt fragment av T1 (husk at T2 er replikert), og resultatene blir overført til Trondheim, der de blir slått sammen (union): Kun spørreresultatet fra Oslo og Bergen (størrelsen kan anslås til 90% av samlet spørreresultat, det vil si under 20% av T1) blir overført til Trondheim.

Hvis vi kun sammenligner mengden av data som bli overført er strategi 4 klart best, deretter strategi 3 og til slutt strategi 1 og 2 som like dårlige. For strategi 4 får vi riktignok en ekstrajobb med å sette sammen de tre "delresultatene" til slutt. Tilsvarende resonnementer kan gjøres for å analysere hva som skjer når spørringen blir initiert fra Bergen eller Oslo. Hvis spørringen blir initiert fra Oslo er det tilstrekkelig å overføre omlag 40% av spørreresultatet.

Hvis T2 kun er lagret på én node (og vi antar at T2 krever mye større plass enn T1) vil det trolig lønne seg å overføre (alle fragmenter av) T1 til denne noden, utføre spørringen her, og deretter overføre resultatet til initierende node.

Oppgave 2

Algoritmer for behandling av data på disk.

Vi må først sortere tabellene med hensyn på koblingskolonnen. I begge tilfeller kan dette gjøres med 1 fletting (filene inneholder færre enn 100² blokker). For tabell S trenger vi 3 × 1 000 = 3 000 diskaksesser, og for T trenger vi 3 × 3 000 = 9 000 diskaksesser. Selve flettekoblingen krever 1 000 + 3 000 = 4 000 diskaksesser. Altså totalt 16 000 diskaksesser.

Oppgave 3

Spørreoptimalisering.

3a

Hvilke varer har kunde 17 kjøpt siste måned?

Bruker MySQL-funksjoner Year, Month og Date.

SELECT DISTINCT Vare.VNr, Betegnelse
FROM Ordre, Ordrelinje, Vare
WHERE Ordre.OrdreNr=Ordrelinje.OrdreNr
AND Ordrelinje.VNr = Vare.VNr
AND Ordre.KNr = 17
AND Year(Ordre.Dato) = Year(CurDate())
AND Month(Ordre.Dato) = Month(CurDate())

3b

Relasjonsalgebra.

Vi forkorter navn på tabellene til O, OL og V for å spare litt plass.

Π_{V.VNr,Betegnelse}( σ_{O.KNr=17 ∧ Year(O.Dato)=Year(CurDate()) ∧ Month(O.Dato)=Month(CurDate())}( O ⊗_{O.OrdreNr=OL.OrdreNr}( OL ⊗_OL.VNr=V.VNr V ) )

3c

Bruk av omskrivningsregler.

1. Skyver seleksjoner nedover i treet (uttrykket).
2. Bytter om på rekkefølgen av koblinger.

Én ekvivalent spørring blir som følger:

Π_{V.VNr,Betegnelse}( V ⊗_OL.VNr=V.VNr( OL ⊗_{O.OrdreNr=OL.OrdreNr} σ_{O.KNr=17 ∧ Year(O.Dato)=Year(CurDate()) ∧ Month(O.Dato)=Month(CurDate())}(O ) )

Merk at det i dette eksemplet ikke var nødvendig å splitte opp den sammensatte betingelsen i seleksjonen, fordi alle delbetingelsene hadde å gjøre med Ordre.

3c

Beregning av kostnader.

Løsningsforslag foreligger ikke.

Oppgave 4a-d

Brukeradministrasjon.

Løsningen bruker Oracle-syntaks.

-- a) Oppretter roller
CREATE ROLE innsyn;
CREATE ROLE oppdatering;
CREATE ROLE utvikler;

-- Tildeler rettigheter til rollene
GRANT SELECT ON Ordre TO innsyn;
GRANT SELECT ON Vare TO innsyn;
-- og så videre...

GRANT INSERT, UPDATE, DELETE ON Ordre TO oppdatering;
-- og så videre...

GRANT CREATE ANY TABLE TO utvikler;
GRANT DROP ANY TABLE TO utvikler;
GRANT innsyn TO utvikler;

-- b) Oppretter brukere
CREATE USER ola;
CREATE USER lise;
CREATE USER kari;

-- Tildeler roller til brukere
GRANT ROLE innsyn TO ola;
GRANT ROLE ajourhold TO lise;
GRANT ROLE utvikler TO kari;

-- c) Tildele en bestemt bruker leserettighet
GRANT SELECT ON Vare TO kari;

-- d) Trekker tilbake leserettigheten
REVOKE SELECT ON Vare FROM kari;

Oppgave 4e

Lesetilgang til utvalgte rader.

Lag et view som "inneholder" de aktuelle radene:

CREATE VIEW NoenRader AS
SELECT *
FROM Tabell
WHERE Betingelse;

Lag deretter en rolle og tildel denne leserettighet på viewet:

CREATE ROLE R;
GRANT SELECT ON NoenRader TO R;

Til slutt kan de aktuelle brukerne få denne rollen:

GRANT R TO bruker1;
GRANT R TO bruker2;

Oppgave 5

Overvåking.

Vi bruker Oracle-syntaks i denne oppgaven.

5a

Loggføre oppdateringer.

AUDIT UPDATE ON Kunde BY Ola;

5b

Vis resultatet.

SELECT * FROM dba_audit_trail;

5c

Overvåke uten AUDIT.

For å overvåke i et DBHS uten støtte for AUDIT kan man lage sin egen overvåkingsmekanisme ved hjelp av såkalte triggere, som er "programmer" som blir utført når bestemte hendelser inntreffer, for eksempel at en bestemt tabell blir oppdatert.