Databasteknik: Lösningar till tentamen 2011-01-11

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.

För att ge övning för läsaren (och inte alls för att jag är lat) har jag låtit bli att kontrollera och provköra dessa svar. Maila de fel ni hittar till mig.

Uppgift 1 (6 p)

a)

Enligt ER-diagrammet kan en bok bara ha en enda författare. I verkligheten har många böcker två eller flera författare.

b)

Relationer, med primärnycklarna understrukna:

Böcker(Nr, ISBN, Titel, År, Författare)
Författare(Nr, Namn, Land)
Exemplar(Nr, Bok, Exemplarnummer, Skick, UtlånadTill, Återdatum)
Låntagare(Nr, Namn, Adress, Bötessumma)
HarLånat(Nr, Exemplar, Låntagare)

I Böcker är ISBN en alternativnyckel. I Exemplar är Bok och Exemplarnummer en alternativnyckel. I HarLånat är Exemplar och Låntagare en alternativnyckel.

Referensattribut:

Böcker.Författare till Författare.Nr
Exemplar.Bok till Böcker.Nr
Exemplar.UtlånadTill till Låntagare.Nr
HarLånat.Exemplar till Exemplar.Nr
HarLånat.Låntagare.Nr till Låntagare.Nr

Uppgift 2 (5 p)

a) Vilka kandidatnycklar finns i tabellen?

ISBN och Exemplarnummer bildar en sammansatt kandidatnyckel. Om titlar är unika bildar även Titel och Exemplarnummer en sammansatt kandidatnyckel.

b) Vilka fullständiga funktionella beroende finns i tabellen?

Om boktitlar och låntagarnamn antas vara unika, fås fler ffb.

c) Vilken är den högsta normalform som tabellen uppfyller, av 1NF, 2NF, 3NF och BCNF? (Ledtråd: Det är inte BCNF.) Motivera svaret!

Första normalformen (1NF).

Tabellen har atomära attribut ("högst ett värde i varje ruta"), vilket är kravet för 1NF. Men eftersom Titel är ffb av enbart ISBN så är inte alla icke-nyckelattribut ffb av hela primärnyckeln, vilket är ett krav för 2NF. 3NF och BCNF förutsätter 2NF, och alltså uppfylls inte heller de.

d) Beskriv ett problem som finns i den här tabellen, men som man skulle slippa om den hade uppfyllt BCNF.

Några förslag:

  1. Att en bok med ett visst ISBN har en viss titel måste upprepas en gång för varje exemplar av boken.
  2. Om vi inte har några exemplar av en bok kan vi inte lagra dess ISBN-nummer och titel.
  3. Tabellen kan vara svår att förstå. (Notera att vi måste kalla den BöckerOchLån, så den handlar uppenbarligen om mer än en sak.)

Uppgift 3 (10 p)

a) Jag heter Thomas Padron-McCarthy och är författare. Vilket land kommer jag ifrån? 

select Land from Forfattare where Namn = 'Thomas Padron-McCarthy';

b) Jag heter Thomas Padron-McCarthy och har skrivit en bok. Vad har den boken för titel? 

select Bocker.Titel
from Forfattare, Bocker
where Bocker.Forfattare = Forfattare.Nr
and Forfattare.Namn = 'Thomas Padron-McCarthy';

c) Hur många exemplar av boken med titeln Databasteknik finns just nu inne, alltså är inte utlånade? Vi vill veta antalet. 

select count(*)
from Bocker, Exemplar
where Bocker.Nr = Exemplar.Bok
and Exemplar.UtlanadTill is null
and Bocker.Titel = 'Databasteknik';

d) Finns det några böcker som är med i databasen, men där vi inte har några exemplar? Vi vill veta ISBN-nummer och titel på dessa böcker. 

select ISBN, Titel
from Bocker
where Nr not in (select Bok from Exemplar);
Alternativ: 
select ISBN, Titel
from Bocker left outer join Exemplar on Bocker.Nr = Exemplar.Bok
where Exemplar.Nr is null;

e) Vad är ISBN-numret och titeln på den bok som biblioteket har i flest antal exemplar? 

create view AntalExemplar as
select Bok, count(*) as Antal
from Exemplar
group by Bok;

select ISBN, Titel
from Bocker
where Nr in (select Bok from AntalExemplar
             where Antal in (select max(Antal) from AntalExemplar));

Uppgift 4 (5 p)

a) (3p)

Dessa är de kolumner som används för att söka efter rader i frågorna. Alla har god selektivitet, utom Exemplar.UtlanadTill som förmodligen innehåller väldigt många null-värden. Ingen är extremt bred, utom Bocker.Titel, men den är viktig och behöver indexeras i alla fall. Ingen tabell uppdateras extremt ofta.

Exemplar.UtlanadTill används för is null, och en del databashanterare indexerar inte null-värden.

b) (1p) Vad är det för skillnad på ett index och en primärnyckel?

Stor. Primärnyckel är ett logiskt begrepp som säger att en kolumn (eller en kombination av kolumner) har unika värden. Ett index är en fysisk datastruktur som används för sökning i databasen för att snabbt hitta rader i en tabell baserat på värdet i en kolumn (eller i en kombination av kolumner).

Ett index kan vara unikt, dvs ange att dess kolumn eller kolumner måste ha unika värden, men måste inte vara det.

c) (1p) Hur kan index göra att samma SQL-frågor går snabbare att köra? Om SQL-koden inte har ändrars, borde det väl ta precis lika lång tid att köra frågan som innan man skapade indexen?

SQL-frågan är inte ett steg-för-steg-program som kan köras direkt, som till exempel ett C-program, utan databashanteraren måste först översätta SQL-frågan till ett steg-för-steg-program, en så kallad exekveringsplan. Databashanteraren gör den översättningen automatiskt. En SQL-fråga brukar kunna översättas till många olika exekveringsplaner, ofta med väldigt olika prestanda (dvs snabbhet). Databashanteraren har en så kallad frågeoptimerare som automatiskt väljer en exekveringsplan som är snabb. Oftast går det mycket snabbare att göra sökningarna i databasen om man går via de index som finns, och då kommer databashanteraren automatiskt att välja en exekveringsplan som använder indexen. När det nu finns nya index som kan snabba upp sökningarna, så kommer databashanteraren alltså att använda dem.

Uppgift 5 (3 p)

Vilken databashanterare bör vi välja för vår databas? Nämn tre olika databashanterare, och tala om varför, eller varför inte, de passar för den här tillämpningen.

Några databashanterare som jag själv skulle kunna rekommendera till den här tillämpningen:

Några databashanterare som jag själv inte skulle rekommendera till den här tillämpningen:

Uppgift 6 (2 p)

Ett EER-diagram för biblioteket

Kommentar: Jag har inte dragit poäng för dem som ritat underentitetstyperna överst och överentitetstypen underst. Det gör det visserligen svårt och förvirrande för läsaren, men själva symbolerna är kanske inte fel.

Uppgift 7 (3 p)

Under förutsättning att vi har auto-inkrement på Nr i HarLanat (eller att Nr saknas i den tabellen). Kommer att ge fel om man lånar samma exemplar flera gånger. 
create trigger Aterlamning
after update on Exemplar
referencing old table as o new table as n
for each row
begin atomic
    declare Exemplaret integer;
    declare GamlaLantagaren integer;
    declare NyaLantagaren integer;

    select Exemplarnummer into Exemplaret from o;
    select UtlanadTill into GamlaLantagaren from o;
    select UtlanadTill into NyaLantagaren from n;

    if (GamlaLantagaren is not null and NyaLantagaren is null) then
        insert into HarLanat (Exemplar, Lantagare) values (Exemplaret, GamlaLantagaren);
    end if;
end

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), 28 januari 2012