Databaskonstruktion: Lösningar till tentamen 2003-10-20

Med vissa rättelser och tillägg.

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.

Uppgift 1: ER-diagram (5 p)

ER-diagram för uppgift 1

Om man vill kan man i stället göra en särskild entitetstyp Nyckelord, som har ett många-till-många-samband med Nyhet. Även en svag entitetstyp går att använda.

Uppgift 2: Relationsmodellen (5 p)

Primärnycklarna är understrukna. Så här kan create table-kommandon se ut:
create table Lasare
(Nr integer,
Loginnamn varchar(8),
Losenord varchar(8),
primary key (Nr));

create table Betalande
(Nr integer,
Slutdatum date,
primary key (Nr),
foreign key (Nr) references Lasare(Nr));

create table Nyhet
(Nr integer,
Tid date,
Rubrik varchar(100),
Text varchar(2000),
Ersatter integer,
primary key (Nr),
foreign key (Ersatter) references Nyhet(Nr));

create table HarLast
(Lasare integer,
Nyhet integer,
primary key (Lasare, Nyhet),
foreign key (Lasare) references Lasare(Nr),
foreign key (Nyhet) references Nyhet(Nr));

create table Nyckelord
(Nyhet integer,
Ord varchar (20),
primary key(Nyhet, Ord),
foreign key (Nyhet) references Nyhet(Nr));

Uppgift 3: SQL (5 p)

a) (1p) Vad är texten till den nyhet som har rubriken Norge vann VM?
select Text
from Nyhet
where Rubrik = 'Norge vann VM';
Kommentar: En del verkar ha fått för sig att alla jämförelser av textsträngar ska göras med like i stället för =. Det är fel. Like används bara vid mönstermatchning. Jag har dock inte dragit några poäng för det, för det fungerar lika bra med like - så länge man inte vill jämföra med tecknen % och _. Det är ju de som används som jokertecken av like.

b) (2p) Vilka rubriker har de nyheter som läsaren med loginnamnet Bengt har läst?

select Rubrik
from Nyhet, HarLast, Lasare
where Nyhet.Nr = HarLast.Nyhet
and HarLast.Lasare = Lasare.Nr
and Lasare.Loginnamn = 'Bengt';
Eller:
select Rubrik
from Nyhet
where Nr in (select Nyhet
             from HarLast
             where Lasare in (select Nr
                              from Lasare
                              where Loginnamn = 'Bengt'));
Eller, om man av någon anledning råkar gilla explicita joinar:
select Rubrik
from (Nyhet join HarLast on Nyhet.Nr = HarLast.Nyhet)
  join Lasare on HarLast.Lasare = Lasare.Nr
where 
Lasare.Loginnamn = 'Bengt';
Eller "åt andra hållet":
select Rubrik
from (Lasare join HarLast on Lasare.Nr = HarLast.Lasare)
  join Nyhet on HarLast.Nyhet = Nyhet.Nr
where 
Lasare.Loginnamn = 'Bengt';
c) (2p) Vilka rubriker har de nyheter som läsaren med loginnamnet Bengt inte har läst?
select Rubrik
from Nyhet
where Nr not in (select Nyhet
                 from HarLast
                 where Lasare in (select Nr
                                  from Lasare
                                  where Loginnamn = 'Bengt'));
Kommentar: En del har använt <> i stället för not in. Det ger helt fel svar.

d) (2p) Hur många läsare har läst nyheten som har rubriken Norge vann VM?

select count(Lasare.Nr)
from Lasare, HarLast, Nyhet
where Lasare.Nr = HarLast.Lasare
and HarLast.Nyhet = Nyhet.Nr
and Nyhet.Rubrik = 'Norge vann VM';
Eller:
select count(*)
from Lasare
where Nr in (select Lasare
             from HarLast
	     where Nyhet = (select Nr
	                    from Nyhet
                            where Rubrik = 'Norge vann VM'));
Egentligen behöver man inte titta i läsartabellen:
select count(*)
from HarLast, Nyhet
where HarLast.Nyhet = Nyhet.Nr
and Nyhet.Rubrik = 'Norge vann VM';
e) (3p) Vad är rubriken på den nyhet som lästs av flest läsare?
create view AntalLasare as
select Nyhet, count(*) as Antal
from HarLast
group by Nyhet;

select Rubrik
from Nyhet
where Nr in (select Nyhet
	     from AntalLasare
             where Antal = (select max(Antal) from AntalLasare));

Uppgift 4: Prestanda (3 p)

Dessa kolumner används i join (när man kopplar ihop tabeller) och select (när man väljer rader i en tabell baserat på värdet i en eller flera kolumner). De är inte heller breda textkolumner, och de har inte dålig selektivitet.

Kommentarer:

  1. HarLast.Lasare används internt i databashanteraren i en join i b och c, även om det inte syns så tydligt när man formulerar de frågorna med in och not in. (Tänk på att alla de olika lösningsförslagen till fråga b ska ge samma resultat.) Jag drar inga poäng om man skrivit SQL-frågorna på det viset, och sen missat det indexet.
  2. Många databashanterare skapar automatiskt index för primärnycklarna, och man behöver i så fall inte skapa nya index för kolumnerna Nyhet.Nr och Lasare.Nr. Tabellen HarLast har en sammansatt primärnyckel bestående av HarLast.Nyhet och HarLast.Lasare, och ett sådant sammansatt index kan oftast inte användas för sökningar på de ingående enskilda kolumnerna. Därför bör man skapa index på HarLast.Nyhet och HarLast.Lasare.
  3. Man bör inte förutsätta att databashanteraren internt kommer att köra frågorna på ett visst sätt, till exempel att den kommer att leta efter värden i kolumnen Artikel för att hitta vissa rader i tabellen HarLast, och sen finns raden tillgänglig så man behöver inget index på kolumnen Lasare i den tabellen. Databashanteraren väljer själv det snabbaste sättet att köra en fråga, oberoende av hur man formulerat den i SQL.

Uppgift 5: Några databastermer (7 p)

a) relationsdatabas (1p)

Nästan rätt, utom att (1) en tabell består av en eller flera kolumner, inte två eller flera, och (2) man kan använda flera kolumner som sammansatt nyckel, och (3) man kan dessutom ha flera olika kandidatnycklar i en tabell. Det är också lite oklart vad de menar med att "samma nyckel kan förekomma i flera tabeller". Om de menar att en nyckel i en tabell kan förekomma som referensattribut i en annan tabell, utan att nödvändigtvis vara nyckel i den tabellen, är det rätt.

Dessutom vill den där järnhandeln nog hellre joina kundregistret med försäljningsregistret!

b) databas (1p)

Rätt. Det är dock lite oklart vad de menar med att "skilt från".

c) atomärt värde (2p)

Helt fel. Atomära värden har inte att göra med vad som går att räkna fram utgående från andra värden, utan om att de atomära värdena inte kan delas upp och delarna sen användas i sökningar. Ett heltal i en databas är ett atomärt värde, oavsett om det kan räknas ut utifrån andra värden eller inte, men en lista av heltal, där man kan söka efter eller jämföra med de enskilda talen i listan, är inte att atomärt värde.

d) referentiell integritet (2p)

Åtminstone i akademiska sammanhang brukar man oftare säga referensintegritet på svenska. Förklaringen är rätt så till vida att det har med konsekvens och motsägelsefrihet att göra, men annars är det fel. Det som beskrivs i förklaringen är någon sorts dubbelriktade samband, men referensintegritet handlar helt enkelt om att de värden som används i ett referensattribut också ska finnas i den tabell som referensattributet refererar till. Om det till exempel står i tabellen Anställd att Sven arbetar på avdelning 7, så ska det också finnas en avdelning med nummer 7 i tabellen Avdelning.

e) trigger (1p)

Rätt. (Man brukar inte använda triggers till att upprätthålla referensintegritet, men den sortens dubbelriktade samband som Computer Sweden tror är referensintegritet kan man behöva triggers till.)

Kommentar: Många har anmärkt på påståendet att en trigger skulle kunna "göra vad som helst - utlösa larm, skicka meddelanden om överskriden kreditgräns eller starta andra program". Om den kan detta är förstås beroende på om databashanteraren har sådana funktioner. Det finns det databashanterare som har.

Uppgift 6: Transaktioner (4 p)

Se http://www.ida.liu.se/~tompa/databaser/transaktioner.html.

Uppgift 7: ODBC (7 p)

a-c) Se http://www.aass.oru.se/~tpy/dbk/2003-2004-p1/restricted/sql-inuti/.

d)

SQLBindCol används för att binda kolumner i resultatet från en SQL-fråga till variabler i C-programmet, så att man kan överföra data från databasen till C-programmet. SQLBindParameter används för att binda delar i en SQL-fråga, markerade med frågetecken, till variabler i C-programmet, så att man kan överföra data från C-programmet till databasen.

e)

SQLExecPrepare förbereder körningen av frågan på databasservern, med parsning av frågans text och med optimering, medan SQLExecute sedan verkligen kör frågan. Fördelen är att man kan förbereda en fråga en gång, och sen köra den många gånger med olika parametrar. För att spara exekveringstid bör en fråga som ska köras inuti en loop därför delas upp, så att man gör SQLExecPrepare före loopen, och sen bara SQLExecute inuti loopen.

Uppgift 8: Aktiva databaser (5 p)

Här kan man använda exemplet från föreläsningen, http://www.aass.oru.se/~tpy/dbk/2003-2004-p1/lectures/triggers/triggers.txt:

Antag att vi ofta vill summera alla lönerna i employee-tabellen:

select sum(salary) as salsum
from employee;
Vi skapar en liten tabell och lagrar summan där:
create table salsum (salsum integer);
insert into salsum (salsum) select sum(salary) from employee;
select salsum from salsum;

+----------------------+
| SALSUM               |
+----------------------+
| 296688               |
+----------------------+
1 rows returned
Lönesumman kan ändras om man lägger till, tar bort eller ändrar anställda. Därför måste vi skapa tre aktiva regler för employee-tabellen, en för insert, en för delete och en för update.

Om vi nöjer oss med att det bara fungerar att lägga till, ta bort och ändra en rad i taget, blir det så här, med Mimer-syntax:

@
create trigger emp_insert after insert on employee
    referencing new table as n
for each statement
begin atomic
    declare newsal integer;
    select salary into newsal from n;
    update salsum set salsum = salsum + newsal;
end
@

@
create trigger emp_delete after delete on employee
    referencing old table as o
for each statement
begin atomic
    declare oldsal integer;
    select salary into oldsal from o;
    update salsum set salsum = salsum - oldsal;
end
@

@
create trigger emp_update after update on employee
    referencing old table as o new table as n
for each statement
begin atomic
    declare oldsal integer;
    declare newsal integer;
    select salary into oldsal from o;
    select salary into newsal from n;
    update salsum set salsum = salsum - oldsal + newsal;
end
@

Uppgift 9: Databasbaserade webbplatser (2 p)

Ja, konsulten kan ha rätt.

CGI-program kan vara långsamma därför att om en webbsida ska genereras av ett CGI-program, så måste programmet startas och köras varje gång någon vill titta på den webbsidan. Att starta ett program tar på de flesta system mycket lång tid, jämfört med att göra saker i programmet när det väl har startats.

På en databasbaserade webbplats kan CGI-program vara extra långsamma, eftersom CGI-programmet inte bara måste startas, utan det måste dessutom koppla upp sig mot databashanteraren och logga in. Detta kan ta lång tid, jämfört med att bara köra en SQL-fråga.


Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se) 15 december 2003