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

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)

Personer(Nr, Namn, Adress, Epost)
Persontelefoner(Person, Telefon)
Julklappstyper(Nr, Namn, Beskrivning, Pris)
Julklappar(Nr, Namn, Beskrivning, Pris, Storlek, Färg, Typ)
Har(Julklapp, Person)
VillHa(Julklapp, Person, Prioritet)

Ytterligare kandidatnycklar:

Förmodligen är Julklappstyper.Namn en kandidatnyckel, och kanske Personer.Epost. Om man lägger till en intern nyckel ("id") till tabellerna Persontelefoner, Har och VillHa, är de angivna primärnycklarna i dessa tabeller fortfarande kandidatnycklar.

Referensattribut:

Persontelefoner.Person till Person.Nr
Julklappar.Typ till Julklappstyper.Nr
Har.Julklapp till Julklappar.Nr
Har.Person till Personer.Nr
VillHa.Julklapp till Julklappar.Nr
VillHa.Person till Personer.Nr

Uppgift 2 (10 p)

a) (1p) Hur många personer finns just nu registrerade på webbplatsen?

select count(*) from Personer

b) (2p) Vad är e-postadresserna till de personer som vill bli av med blåa ylletröjor?

select Epost
from Personer, Har, Julklappar
where Personer.Nr = Har.Person
and Har.Julklapp = Julklappar.Nr
and Julklappar.Namn = 'Ylletröja'
and Julklappar.Färg = 'Blå'

select Epost
from Personer, Har, Julklappar, Julklappstyper
where Personer.Nr = Har.Person
and Har.Julklapp = Julklappar.Nr
and Julklappar.Typ = Julklappstyper.Nr
and Julklappstyper.Namn = 'Ylletröja'
and Julklappar.Färg = 'Blå'

c) (2p) Jag heter Thomas Padron-McCarthy. Vad är namnen på de julklappar som jag vill ha?

select Julklappar.Namn
from Personer, VillHa, Julklappar
where Personer.Namn = 'Thomas Padron-McCarthy'
and Personer.Nr = VillHa.Person
and VillHa.Julklapp = Julklappar.Nr

d) (2p) Vilka julklappar vill ingen ha? Vi vill veta numret och namnet på dessa julklappar.

select Nr, Namn
from Julklappar
where Nr not in (select Julklapp from VillHa)

e) (3p) Vilken julklappstyp är mest efterfrågad?

create view ÖnskatAntalPerTyp
as select Typ, count(*) as Antal
from Julklappar, VillHa
where Julklappar.Nr = VillHa.Julklapp
group by Typ;

select Nr, Namn
from Julklappstyper
where Nr = (select Nr
            from ÖnskatAntalPerTyp
            where Antal = (select max(Antal) from ÖnskatAntalPerTyp));

Uppgift 3 (5 p)

Julklappar.Nr
Julklappar.Namn
Julklappar.Färg
Personer.Nr
Personer.Namn
Har.Julklapp
Har.Person
VillHa.Julklapp
VillHa.Person

Kommentar 1: Julklappar.Färg kan vara lite tveksamt, eftersom den kolumnen kan antas ha dålig selektivitet.

Kommentar 2: Tänk på att ett sammansatt index, till exempel på Har.Julklapp och Har.Person, inte fungerar på samma sätt som två separata index. Med två separata index på Har.Julklapp och Har.Person kan man söka snabbt i Har-tabellen både efter en julklapp och efter en person, men med ett sammansatt index på Har.Julklapp och Har.Person kan man inte söka snabbt efter en person. (Beroende på typ av index kan man kanske inte ens söka snabb efter en julklapp, utan bara efter en kombination av en julklapp och en person.)

b) (1p)

Ett index är en datastruktur som gör att databashanteraren snabbare kan hitta rader i en tabell, när den söker efter rader som har ett visst värde i en kolumn.

c) (1p)

SQL-frågorna kan inte köras direkt, eftersom de inte är steg-för-steg-program, utan databashanteraren översätter först SQL-frågan till ett steg-för-steg-program. Då väljer databashanteraren det snabbaste sättet (dvs det snabbaste steg-för-steg-programmet), med hänsyn taget till alla index som finns. Om man skapar ett nytt index som gör att frågan kan köras på ett nytt och snabbare sätt, kommer databashanteraren alltså automatiskt att välja det sättet i stället för den gamla, trots att SQL-frågan inte ändrats.

Kommentar: Här har många svarat ungefär samma sak som i deluppgift b, och kanske missat det där med att SQL-frågorna inte ändras. Det intressanta är alltså hur frågan kan gå snabbare att köra när man skapat index, trots att det är precis samma SQL-kod i frågan.

Uppgift 4 (3 p)

Microsoft Access skulle jag inte använda, eftersom den i första hand är avsedd för mindre databaser med få användare, och vi inte kommer att använda dess lättanvända grafiska gränssnitt, eftersom databasen kommer att nås via en webbplats.

SQLite skulle jag inte heller använda, eftersom det är en enkel databashanterare främst avsedd för inbyggnad (i program, eller i handhållna enheter som telefoner).

Några andra vanliga databashanterare som skulle kunna passa: DB2, Microsoft SQL Server, Oracle, PostgreSQL.

Uppgift 5 (5 p)

Uppgift 6 (2 p)

Uppgift 7 (6 p)

Uppgift 8 (3 p)

b) (2p)

Relationer, med primärnycklarna understrukna:

Svampar(Nr, Höjd, Vikt) -- vi antar här att numret från EER-diagrammet är unikt
Storsvampar(Nr) -- där Nr refererar till Svampar.Nr
Mellansvampar(Nr) -- där Nr refererar till Svampar.Nr
Småsvampar(Nr, Färg) -- där Nr refererar till Svampar.Nr
Minisvampar(Nr) -- där Nr refererar till Småsvampar.Nr
Pyttesvampar(Nr, Maskstatus) -- där Nr refererar till Småsvampar.Nr

En alternativ lösning:

Svampar(Id, Nr, Höjd, Vikt) -- om numret från EER-diagrammet inte är unikt
Storsvampar(Id) -- där Id refererar till Svampar.Id
Mellansvampar(Id) -- där Id refererar till Svampar.Id
Småsvampar(Id, Färg) -- där Id refererar till Svampar.Id
Minisvampar(Id) -- där Id refererar till Småsvampar.Id
Pyttesvampar(Id, Maskstatus) -- där Id refererar till Småsvampar.Id