Databasteknik: Lösningar till tentamen 2009-01-13

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 (3 p)

Resultat-sambandet mellan Uppgift och Student är ett ett-till-många-samband, så en student kan bara lösa en enda uppgift i hela sitt liv. Det är förstås fel, för det kan finnas flera uppgifter på en tenta, och dessutom kan studenten skriva flera olika tentor.

Om man i stället gör om det till ett många-till-många-samband, så kan en student lösa flera olika uppgifter, Men hon kan då bara få ett enda resultat (alltså antalet poäng) på den uppgiften, Studenten får i så fall antingen bara lösa uppgiften en enda gång, trots att hon kan få se den på flera tentor, eller så får hon samma resultat på uppgiften varje gång hon löser den.

Om man tänker sig att en student ska kunna ändra sin prestation på en viss uppgift mellan olika tentatillfällen, så borde Resultat varit ett trevägssamband mellan Uppgift, Student och Tenta. Alternativt kan man objektifiera sambandstypen Resultat till en entitetstyp.

En kommentar om rättningen: Förslag på utökningar och förändringar, till exempel att lägga till betyg, ger inga poäng om det som ändrades inte verkligen var konstigt.

Uppgift 2 (7 p)

Tabeller, med primärnycklarna understrukna: Kommentarer:
I tabellen Ingår är kombinationen Tenta och Nummer en alternativnyckel.
Det är numera vanligare att man använder plural i tabellnamn, alltså Uppgifter, Tentor och så vidare.

Referensattribut:

Det går också att göra Resultat-sambandet som en egen tabell, med Student (ja, bara den) som primärnyckel:

För att förenkla programmeringen av tillämpningsprogrammen kan det vara bra att införa en enkel numerisk primärnyckel även i hopkopplingstabellerna (Ingår, Skrivit och i förekommande fall Resultat).

SQL-kommandon för att provköra:

create table Uppgift
(Id integer not null primary key,
Text varchar(10) not null,
Maxpoang integer not null);

create table Tenta
(Id integer not null primary key,
Datum Date not null);

create table Student
(Id integer not null primary key,
Namn varchar(10) not null,
LostUppgift integer references Uppgift(Id),
Poang integer);

/* Ja, det ska inte vara "not null" på LostUppgift och Poang */

create table Ingar
(Uppgift integer not null references Uppgift(Id),
Tenta integer not null references Tenta(Id),
Nummer integer not null,
primary key(Uppgift, Tenta),
unique(Tenta, Nummer));

create table Skrivit
(Student integer not null references Student(Id),
Tenta integer not null references Tenta(Id),
primary key(Student, Tenta));

insert into Uppgift values (1, 'Who?', 1);
insert into Uppgift values (2, 'What?', 3);
insert into Uppgift values (3, 'Where?', 1);
insert into Uppgift values (4, 'When?', 1);
insert into Uppgift values (5, 'Why?', 10);
insert into Uppgift values (6, 'How?', 3);
insert into Uppgift values (199, 'Duh?', 0);

insert into Tenta values (1, DATE '2008-08-20');
insert into Tenta values (2, DATE '2009-01-13');
insert into Tenta values (3, DATE '2009-02-28');

insert into Student values (1, 'Adama', 1, 0);
insert into Student values (2, 'Bertilina', 2, 2);
insert into Student values (3, 'Cesarina', null, null);
insert into Student values (4, 'Davida', null, null);
insert into Student values (5, 'Erika', null, null);
insert into Student values (6, 'Filippa', null, null);

insert into Ingar values (1, 1, 1);
insert into Ingar values (2, 1, 2);
insert into Ingar values (3, 1, 3);
insert into Ingar values (4, 1, 4);
insert into Ingar values (6, 1, 5);
insert into Ingar values (1, 2, 5);
insert into Ingar values (2, 2, 4);
insert into Ingar values (3, 2, 3);
insert into Ingar values (4, 2, 2);
insert into Ingar values (6, 2, 1);
insert into Ingar values (5, 3, 1);

insert into Skrivit values (1, 2);
insert into Skrivit values (2, 2);
insert into Skrivit values (3, 2);
insert into Skrivit values (4, 2);
insert into Skrivit values (1, 3);
insert into Skrivit values (2, 3);
insert into Skrivit values (5, 3);

select * from Uppgift;
select * from Tenta;
select * from Student;
select * from Ingar;
select * from Skrivit;

Uppgift 3 (13 p)

a) (1p) Hur lyder uppgiften med id-nummer 199?

select Text
from Uppgift
where Id = 199;

b) (2p) Det är bara en tenta som ges 2009-01-13. Hur lyder uppgift nummer 3 på den tentan?

select Text
from Uppgift, Ingar, Tenta
where Uppgift.Id = Ingar.Uppgift
and Ingar.Tenta = Tenta.Id
and Tenta.Datum = DATE '2009-01-13'
and Ingar.Nummer = 3;

En alternativ lösning:

select Text
from Uppgift
where Id = (select Uppgift
            from Ingar
            where Nummer = 3
            and Tenta = (select Id
                         from Tenta
                         where Datum = DATE '2009-01-13'));

c) (2p) Vad är maxpoängen på den tentan?

select sum(Uppgift.Maxpoang)
from Uppgift, Ingar, Tenta
where Uppgift.Id = Ingar.Uppgift
and Ingar.Tenta = Tenta.Id
and Tenta.Datum = DATE '2009-01-13';

d) (2p) Vilka uppgifter har aldrig varit med på någon tenta? (Vi vill veta de uppgifternas id-nummer.)

select Id
from Uppgift
where Id not in (select Uppgift
                 from Ingar);

e) (3p) Vilken uppgift har varit med på flest tentor? (Vi vill veta den uppgiftens id-nummer.)

create view AntalForekomster as
select Uppgift, count(*) as Antal
from Ingar
group by Uppgift;

select Uppgift from AntalForekomster
where Antal = (select max(Antal)
               from AntalForekomster);

f) (3p) Ordna studenterna i ordning efter hur många tentor de skrivit. Studenter med flest tentor ska komma först, och studenter med minst tentor ska komma sist. Även studenter som inte skrivit några tentor alls ska vara med i resultatet, och förstås komma sist. Vi vill ha med studenternas id-nummer och namn.

select Student.Id, Student.Namn, count(Skrivit.Tenta) as Antal
from Student left outer join Skrivit on Student.Id = Skrivit.Student
group by Student.Id, Student.Namn
order by Antal desc;

Den yttre joinen behövs för att få med de studenter som inte skrivit någon tenta alls. Följande SQL-fråga, utan yttre join, tar inte med dem.

select Student.Id, Student.Namn, count(Skrivit.Tenta) as Antal
from Student, Skrivit
where Student.Id = Skrivit.Student
group by Student.Id, Student.Namn
order by Antal desc;

Uppgift 4 (4 p)

Skapa index på: Dessa är de kolumner som används i de aktuella frågorna för att välja ut rader i en tabell eller för att koppla ihop rader i två tabeller, alltså det som internt i databashanteraren kallas "select"- och "join"-operationer.

Man kan låta bli att skapa index på Ingar.Nummer, med motiveringen att den kolumnen innehåller få olika värden, och därför har dålig selektivitet.

Skapa inte index på följande kolumner, som visserligen finns med i frågorna, men som inte används för att välja ut rader i en tabell eller för att koppla ihop rader i två tabeller:

En kommentar: Om man skrivit SQL-frågor med nästlade frågor, framgår det kanske inte så tydligt vilka kolumner som används (eller kan användas) för att koppla ihop rader i tabellerna. Om man tittar på det andra alternativt i lösningsförslagen till uppgift 3b ovan, tror man kanske inte att kolumnerna Ingar.Uppgift och Tenta.Id används i hopkopplingen. Men i det första alternativet, som inte innehåller nästlade frågor, syns det tydligare, och eftersom de båda SQL-frågorna är ekvivalenta måste de användas även i lösningen med nästlade frågor.

En annan kommentar: Sen kan man också resonera vidare om vilka kolumner som databashanteraren faktiskt kommer att använda när den letar i tabellerna medan den kör frågan, och då kan det hända att man kan dra fler slutsatser om vilka index som man verkligen har och inte har nytta av.

SQL-kommandon för att provköra:

create index UppgiftId on Uppgift(Id);
create index TentaId on Tenta(Id);
create index TentaDatum on Tenta(Datum);
create index IngarUppgift on Ingar(Uppgift);
create index IngarTenta on Ingar(Tenta);
create index IngarNummer on Ingar(Nummer);

Uppgift 5 (4 p)

Här kan man skriva ganska många olika saker, och man kan komma fram till olika (men fullt rimliga) slutsatser om vilken databashanterare som passar bäst. Här är några saker som man kan ta upp:

Uppgift 6 (9 p)

a) (2p)

select max(Id) from Student;
insert into Student (Id, Namn) values (4712, 'Olle');

b) (1p)

I ett textgränssnitt där man matar in SQL-satser för hand påbörjar man transaktionen med kommandot start transaction, och avslutar med kommandot commit (eller rollback). Om man använder SQL inuti ett vanligt program, till exempel skrivet i C eller Java, brukar det finnas andra sätt.

c) (1p)

En samling operationer mot databasen som hör ihop som en enhet, till exempel operationen att flytta pengar mellan två bankkonton. Beloppet som ska överföras ska dels subtraheras från det ena kontot, och dels adderas till det andra.

d) (4p)

e) (1p)

Om databasen har två användare (eller bara två program som körs mot den samtdigt), som ungefär samtidigt vill lägga in varsin ny student, kan de råka ut för att båda två kör den första SQL-frågan, och får fram att det högsta värdet på Student.Id är 4711. Därefter försöker båda lägga in sin respektive student med nummer 4712. Då kommer den som kör sitt insert-kommando först att lyckas, men den andra misslyckas, eftersom det redan finns en rad i Student-tabellen med det värdet på primärnyckeln Id.


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