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:

Uppgift(Id, Text, Maxpoäng)
Tenta(Id, Datum)
Student(Id, Namn, LöstUppgift, Poäng)
Ingår(Uppgift, Tenta, Nummer)
Skrivit(Student, Tenta)

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:

Ingår.Uppgift till Uppgift.Id
Ingår.Tenta till Tenta.Id
Skrivit.Student till Student.Id
Skrivit.Tenta till Tenta.Id
Student.LöstUppgift till Uppgift.Id

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

Resultat(Student, Uppgift, Poäng)

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å:

Uppgift.Id
Tenta.Id
Tenta.Datum
Ingar.Uppgift
Ingar.Tenta
Ingar.Nummer

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:

Ingar.Text
Uppgift.Maxpoang

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:

Microsoft Access fungerar bara på Windows, så om man till exempel har en Linux-dator kan man inte använda Access. MySQL och Mimer fungerar på många olika operativsystem.
Microsoft Access kostar pengar (men lärare och studenter i Örebro kan ladda ner den gratis från MSDN Academic Alliance), Mimer kostar pengar (men går att ladda ner gratis för utveckling och utvärdering), medan MySQL är gratis (dock inte i alla versioner), och open source.
Microsoft Access har ett lättanvänt grafiskt gränssnitt, men det är svårare att skriva SQL-frågor direkt i den.
Den underliggande databasmotorn som Access använder är ganska enkel, med en ganska begränsad SQL-dialekt, så vill man göra avancerade saker (till exempel med triggers) måste man välja en annan databashanterare. Och just en databaslärare vill kanske utnyttja avancerade funktioner i sin databas? Mimers och MySQL:s SQL-dialekter klarar mer. Mimers SQL-dialekt följer SQL-standarden bättre.
Mimer och MySQL är bättre på att hantera stora databaser, med mycket data och många samtidiga användare, än vad Access är. Men med databasmått mätt är det här inte en särskilt stor databas, även om man skulle lägga in tusentals tentor och tiotusentals studenter, med varje students resultat på varje uppgift. Ingen databashanterare (för lämpliga värden på "ingen") har problem med så lite data. Det står visserligen i uppgift 4 att databasen är "stor", så man behöver skapa index, men med lämpliga index har inte heller Access några svårigheter att hantera en så här liten databas. Och kommer den här databasen verkligen att ha mer än en enda användare?
Både MySQL och Access har många användare, så det kan vara lättare att få hjälp med dem. än med en mer ovanlig databashanterare som Mimer.

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)

A står för atomicitet, dvs odelbarhet. En transaktion ska vara odelbar. Det betyder att antingen ska hela transaktionen genomföras, så att alla ändringar den gör förs in i databasen, eller så ska inga ändringar alls göras. Om en transaktion avbryts mitt i (för att användaren ångrar sig, eller för att strömmen går, eller vad det nu kan bero på), måste databashanteraren alltså ta bort alla ändringar som den hunnit göra.
C står för consistency preserving, dvs "konsistensbevarande". Om databasen är konsistent, dvs utan några inre motsägelser, före en transaktion, så ska den också vara konsistent efter transaktionen. Det här betyder att databashanteraren måste kontrollera alla integritetsvillkor så att de är uppfyllda.
I står för isolering. Transaktionerna ska hållas isolerade från varandra. Även om databashanteraren utför flera transaktioner samtidigt så får en transaktion aldrig se en annan transaktions halvfärdiga ändringar. (Något förenklat. I en del system är det lite krångligare än så.)
D står för durability, dvs "hållbarhet". När transaktionen är genomförd och avslutad ska de ändringar den gjort aldrig försvinna ur databasen, även om strömmen går, om datorn kraschar, och helst också om disken går sönder.

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