Databasteknik: Lösningar till tentamen 2010-01-12

Observera att detta är förslag på lösningar. Det kan finnas andra lösningar som också är korrekta, och det kan hända att en del av lösningarna är mer omfattande än vad som krävs för full poäng på uppgiften. En del av lösningarna är kanske inte fullständiga, utan hänvisar bara till var man kan läsa svaret.

Uppgift 1 (6 p)

Tabeller, med primärnycklarna understrukna: I tabellen Har är kombinationen Abonnent och Hämtställe en alternativnyckel.
I tabellen Avser är kombinationen Hämtning och Faktura en alternativnyckel.
I tabellen Hämtställe är Adress (troligen) en alternativnyckel, Även kombinationen Lat och Long är (troligen) en alternativnyckel.
I tabellen Hämtningar skulle kombinationen Hämtställe, Datum och Tid kunna vara en alternativnyckel, om man antar att två olika sopbilar inte kan hämta sopor på samma hämtställe samtidigt.

Referensattribut:

För att förenkla programmeringen av tillämpningsprogrammen har vi infört en enkel numerisk primärnyckel i hopkopplingstabellerna Har och Avser.

SQL-kommandon för att provköra: 

create table Abonnenter
(Anr integer not null primary key,
Namn varchar(10) not null,
Adress varchar(10) not null);

create table "Hämtställen"
(Snr integer not null primary key,
Adress varchar(10) not null,
Lat decimal(10,6) not null,
Long decimal(10,6) not null,
unique(Lat, Long));

create table "Hämtningar"
(Hnr integer not null primary key,
Datum date not null,
Tid time,
"Hämtställe" integer not null references "Hämtställen"(Snr));

create table Fakturor
(Fnr integer not null primary key,
Datum date not null,
Belopp integer not null,
Abonnent integer not null references Abonnenter(Anr));

create table Har
(Id integer not null primary key,
Abonnent integer not null references Abonnenter(Anr),
"Hämtställe" integer not null references "Hämtställen"(Snr),
Tunnor integer not null,
unique(Abonnent, "Hämtställe"));

create table Avser
(Id integer not null primary key,
"Hämtning" integer not null references "Hämtningar"(Hnr),
Faktura integer not null references Fakturor(Fnr),
unique("Hämtning", Faktura));

insert into Abonnenter values (1, 'Alice', 'Almby');
insert into Abonnenter values (2, 'Bengt', 'Borneo');
insert into Abonnenter values (3, 'Claes', 'Chicago');
insert into Abonnenter values (4, 'Dick', 'Dorotea');
insert into Abonnenter values (5, 'Eva-Lotta', 'Elvägen 2');

insert into "Hämtställen" values (1, 'Almby', 17.2, 33.9);
insert into "Hämtställen" values (2, 'Almby', 17.4, 33.8);
insert into "Hämtställen" values (3, 'Utlandet', 45.2, 17.8);

insert into Har values (1, 1, 1, 1);
insert into Har values (2, 1, 2, 7);
insert into Har values (3, 2, 3, 2);
insert into Har values (4, 3, 3, 1);

insert into "Hämtningar" values (1, DATE '2010-01-10', TIME '08:17:00', 1);
insert into "Hämtningar" values (2, DATE '2010-01-10', TIME '08:19:04', 2);
insert into "Hämtningar" values (3, DATE '2010-01-11', TIME '09:01:31', 2);
insert into "Hämtningar" values (4, DATE '2010-01-12', TIME '08:02:01', 2);

insert into Fakturor values (1, DATE '2010-01-10', 1000, 1);

insert into Avser values (1, 1, 1);
insert into Avser values (2, 2, 1);

select * from Abonnenter;
select * from "Hämtställen";
select * from "Hämtningar";
select * from Fakturor;
select * from Har;
select * from Avser;

Uppgift 2 (10 p)

a)

Vilken position (som anges med koordinaterna Lat och Long) har hämtstället på Grangrensvägen 2? 

select Lat, Long
from "Hämtställen"
where Adress = 'Grangrensvägen 2';

b) På vilka hämtställen gjordes det hämtningar 2010-01-12 klockan 09:03? Vi vill veta adresserna för dessa hämtställen.

En lösning utan nästlade frågor: 

select "Hämtställen".Adress
from "Hämtställen", "Hämtningar"
where "Hämtställen".Snr = "Hämtningar"."Hämtställe"
and "Hämtningar".Datum = DATE '2010-01-12'
and "Hämtningar".Tid = TIME '09:03:00';

En lösning med nästlade frågor: 

select Adress
from "Hämtställen"
where Snr in (select "Hämtställe" from "Hämtningar"
              where Datum = DATE '2010-01-12'
              and Tid = TIME '09:03:00');

c) Ange namn och adress för de abonnenter som har hämtställe på Talltoppstrappan 9. 

select Abonnenter.Namn, Abonnenter.Adress
from Abonnenter, Har, "Hämtställen"
where Abonnenter.Anr = Har.Abonnent
and Har."Hämtställe" = "Hämtställen".Snr
and "Hämtställen".Adress = 'Talltoppstrappan 9';

d) Jag heter Thomas Padron-McCarthy. När har mina sopor hämtats? Jag vill veta datum och tid. 

select "Hämtningar".Datum, "Hämtningar".Tid
from Abonnenter, Har, "Hämtställen", "Hämtningar"
where Abonnenter.Anr = Har.Abonnent
and Har."Hämtställe" = "Hämtställen".Snr
and "Hämtställen".Snr = "Hämtningar"."Hämtställe"
and Abonnenter.Namn = 'Thomas Padron-McCarthy';

Frågan ovan ger egentligen datum och tid för alla hämtningar från mitt hämtställe, så jag kan få med hämtningar som gjordes innan jag flyttade dit. Som alternativ kan man i stället gå via fakturorna, men då får man bara med de hämtningar som hunnit faktureras.

e) Vilka abonnenter har aldrig fått sina sopor hämtade? Vi vill veta de abonnenternas namn och adress.

Den här SQL-frågan ger de abonnenter som aldrig fått sina sopor hämtade, och som faktiskt har minst ett hämtställe att hämta sopor från. Det räcker för full poäng på deluppgiften. 

select Abonnenter.Namn, Abonnenter.Adress
from Abonnenter, Har, "Hämtställen"
where Abonnenter.Anr = Har.Abonnent
and Har."Hämtställe" = "Hämtställen".Snr
and "Hämtställen".Snr not in (select "Hämtställe" from "Hämtningar");

Den här SQL-frågan ger de abonnenter som aldrig fått sina sopor hämtade, oavsett om de har något hämtställe att hämta sopor från. 

select Abonnenter.Namn, Abonnenter.Adress
from Abonnenter, Har, "Hämtställen"
where Abonnenter.Anr = Har.Abonnent
and Har."Hämtställe" = "Hämtställen".Snr
and "Hämtställen".Snr not in (select "Hämtställe" from "Hämtningar")
union
select Abonnenter.Namn, Abonnenter.Adress
from Abonnenter
where Abonnenter.Anr not in (select Abonnent from Har);

Båda de här frågorna har samma problem som i deluppgift d, nämligen att de inte tar hänsyn till att abonnenter kan ha flyttat, och haft olika hämtställen.

f) Vilken abonnent har flest soptunnor? Vi vill veta namn och adress för den abonnenten.

Vi antar att det finns minst en abonnent med minst en soptunna, så att maxantalet inte är noll. 

create view Abonnenttunnor as
select Abonnenter.Anr, Abonnenter.Namn, Abonnenter.Adress, sum(Har.Tunnor) as Antal
from Abonnenter, Har
where Abonnenter.Anr = Har.Abonnent
group by Abonnenter.Anr, Abonnenter.Namn, Abonnenter.Adress;

select Namn, Adress
from Abonnenttunnor
where Antal = (select max(Antal) from Abonnenttunnor);

Uppgift 3 (3 p)

Det är de attribut som används i fråga a, b och c för att välja ut rader (det som i relationsalgebra kallas operationen selektion) och för att koppla ihop rader i två tabeller (det som i relationsalgebra kallas operationen join).

Exempelvis i fråga a används attributet Adress på det sättet, men inte attributen Lat och Long.

Notera att den nästlade versionen ovan av fråga b är ekvivalent med den onästlade, så även om det kanske inte ser ut som att attributet Hämtningar.Hämtställe används för en join i den nästlade frågan, så gör den det.

Uppgift 4 (3 p)

Här kan man förstås räkna upp en hel massa olika databashanterare, och skriva ganska många olika saker om varför, eller varför inte, de passar. Man kan också komma fram till olika (men fullt rimliga) slutsatser om vilken databashanterare som passar bäst. Men några korta ord om de tre databashanterare som vi sett i kursen: Här är några saker som man kan ta upp: Baserat på detta skulle jag avråda från att använda Access, utan i stället rekommendera antingen Mimer eller MySQL. Om jag skulle bygga systemet själv skulle jag antagligen välja MySQL, mest eftersom jag råkar ha använt den mer, och eftersom det finns mer resurser att läsa på webben om MySQL än Mimer.

Uppgift 5 (3 p)

Grant och revoke är två kommandon som används (normalt av databasadministratören) för att ange vad olika användare får göra med de data som finns i databasen. Med grant delar man ut rättigheter, och med revoke tar man bort dem igen.

Exempel: 

grant select, insert on Abonnenter to hjalmar;
Ger användaren hjalmar rätt att göra sökningar i tabellen Abonnenter, och att lägga till rader i den tabellen, men inte att ta bort eller ändra rader. 

grant update on Abonnenter to hjalmar;
Nu har Hjalmar även rätt att ändra existerande rader, men fortfarande inte att ta bort rader. 

revoke insert on Abonnenter from hjalmar;
Nu har Hjalmar inte längre rätt att ta lägga till nya rader, men han kan fortfarande både söka i tabellen och ändra existerande rader.

Man kan styra åtkomsten mer finkornigt genom att skapa vyer, och dela ut rättigheter till dessa vyer, i stället för till de tabeller som de baseras på: 

create view DagensFakturor as
select Fnr, Belopp
from Fakturor
where Datum = CURRENT_DATE;

grant select on DagensFakturor to hjalmar;
Nu har Hjalmar rätt att se nummer och belopp på de fakturor som är daterade idag, men inga andra kolumner, och inga andra fakturor. 

grant delete on DagensFakturor to hjalmar;
Nu kan Hjalmar dessutom ta bort fakturor som är daterade idag. Han kan dock varken se eller ta bort fakturor med andra datum.

Man kan också ge rättigheter with grant option, vilket ger rättighet att dela ut rättigheter. Databasadministratören skriver: 

grant select on DagensFakturor to hjalmar with grant option;
Nu har Hjalmar rätt att se dagens fakturer, men även rätt att dela ut samma rättighet. Hjalmar kan nu skriva: 

grant select on DagensFakturor to hulda;
Nu har även användaren hulda rätt att se dagens fakturor.

Antag nu att databasadministratören ångrar sig, och tar tillbaka Hjalmars select-rättighet: 

revoke select on DagensFakturor from hjalmar cascade;
Nu har Hjalmar inte längre rätt att se dagens fakturor. Även Huldas rättigheter har försvunnit, eftersom hon hade fått dem via Hjalmar!

Uppgift 6 (6 p)

a)

Här har jag valt att tolka scenariot bland annat som att varje person är antingen kapten eller sjöman, men inte båda, och att det inte finns några fiske-ubåtar. Andra tolkningar är möjliga.

Ett EER-diagram över båtdatabasen

b)

Tabeller, med primärnycklarna understrukna:

Inga alternativnycklar.

Uppgift 7 (4 p)

Förklara för var och en av de fyra egenskaperna vad den egentligen betyder, och ge ett exempel på vad som kan hända om den saknas.

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), 2 februari 2010