Databasteknik: Lösningar till tentamen 2025-01-16

Observera att detta är förslag på lösningar. Det kan finnas andra lösningar som också är korrekta. 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, eller att de bara hänvisar till var man kan läsa svaren. Dessutom har det inträffat i världshistorien att lärare skrivit fel i lösningsförslagen. Jag har förstås aldrig gjort det, men andra. Är det verkligen någon som läser såna här inledande texter? Jag vet inte. Det kan vara så. Rabarber rabarber rabarber. Det har jag hört att statisterna får säga på filminspelningar när det ska vara bakgrundssorl från en restaurang eller liknande. Här nedan kommer lösningsförslagen till uppgifterna.

Uppgift 1 (5 p)

Rita ett ER- eller EER-diagram för den beskrivna databasen. Använd informationen i scenariot ovan, men tänk också på att det ska gå att svara på frågorna i uppgifterna nedan.

ER- och EER-diagram kan ritas på flera olika sätt. Om du använder en annan notation än kursboken, måste du förklara den notation som du använder.

Svar:

Ett ER-diagram

Kommentarer: Man kan också ha en entitetstyp Skjutfält, och ett många-till-många-samband mellan skjutfält och provtillfällen.

Uppgift 2 (6 p)

Implementera den beskrivna databasen i relationsmodellen, dvs översätt ER-diagrammet till tabeller.

Du behöver inte skriva create table-kommandon i SQL, men du ska ange vilka relationer som finns och vilka attribut varje relation innehåller. Ange också alla kandidatnycklar, vilken av dessa som är primärnyckel, samt vilka referensattribut som finns och vad de refererar till.

Implementationen ska vara bra.

Svar:

Granattyper(Nummer, Namn, Kaliber, Vikt)
Granater(Serienummer, Typ, Vikt)
Provtillfällen(Nummer, Startdatum, Slutdatum, Plats)
Avfyrningar(Nummer, Datum, Tid, Granat, Provtillfälle)
Personer(Nummer, Namn)
Deltog(Person, Provtillfälle)

Kandidatnycklarna är understrukna. I tabellen Granattyper är Nummer primärnyckel och Namn en alternativnyckel. I tabellen Avfyrningar är Nummer primärnyckel, och eftersom varje granat bara kan avyras en gång är Granat en alternativnyckel.

Främmande nycklar:

Granater.Typ till Granattyper.Nummer
Avfyrningar.Granat till Granater.Nummer
Avfyrningar.Provtillfälle till Provtillfällen.Nummer
Deltog.Person till Personer.Nummer
Deltog.Provtillfälle till Provtillfällen.Nummer

Nedan visas create table-kommandon och exempeldata, för att underlätta provkörningar.

DROP TABLE Deltog CASCADE;
DROP TABLE Personer CASCADE;
DROP TABLE Provtillfällen CASCADE;
DROP TABLE Avfyrningar CASCADE;
DROP TABLE Granater CASCADE;
DROP TABLE Granattyper CASCADE;

CREATE TABLE Granattyper
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(20) NOT NULL UNIQUE,
Kaliber INTEGER NOT NULL CHECK (Kaliber > 0),
Vikt INTEGER NOT NULL CHECK (Vikt > 0));

INSERT INTO Granattyper VALUES (1, 'Strids-bomb 2000', 155, 46);
INSERT INTO Granattyper VALUES (2, 'Turbo-Pang XL', 155, 47);
INSERT INTO Granattyper VALUES (3, 'Mini-fjutt', 1, 1);

CREATE TABLE Granater
(Nummer INTEGER NOT NULL PRIMARY KEY,
Typ INTEGER NOT NULL REFERENCES Granattyper(Nummer),
Vikt FLOAT NOT NULL CHECK (Vikt > 0));

INSERT INTO Granater VALUES (1, 1, 46);
INSERT INTO Granater VALUES (2, 1, 45.978);
INSERT INTO Granater VALUES (3, 1, 45);
INSERT INTO Granater VALUES (4, 2, 47);
INSERT INTO Granater VALUES (5, 2, 48);

CREATE TABLE Provtillfällen
(Nummer INTEGER NOT NULL PRIMARY KEY,
Startdatum DATE NOT NULL,
Slutdatum DATE NOT NULL,
Plats NVARCHAR(25) NOT NULL);

INSERT INTO Provtillfällen VALUES (1, DATE '2025-01-10', DATE '2025-01-15', 'Villingsbergs skjutfält');
INSERT INTO Provtillfällen VALUES (2, DATE '2025-01-10', DATE '2025-01-15', 'Villingsbergs skjutfält');
INSERT INTO Provtillfällen VALUES (3, DATE '2025-01-10', DATE '2025-01-15', 'Hemma i trädgården');

CREATE TABLE Avfyrningar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE NOT NULL,
Tid TIME NOT NULL,
Granat INTEGER NOT NULL UNIQUE REFERENCES Granater(Nummer),
Provtillfälle INTEGER NOT NULL REFERENCES Provtillfällen(Nummer));

INSERT INTO Avfyrningar VALUES (1, DATE '2025-01-11', TIME '07:01:00', 1, 1);
INSERT INTO Avfyrningar VALUES (2, DATE '2025-01-12', TIME '07:02:00', 2, 1);
INSERT INTO Avfyrningar VALUES (3, DATE '2025-01-13', TIME '07:03:00', 4, 2);

CREATE TABLE Personer
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(20) NOT NULL);

INSERT INTO Personer VALUES (1, 'Kajsa Kanon');
INSERT INTO Personer VALUES (2, 'Hans Haubits');
INSERT INTO Personer VALUES (3, 'Mats Mörsare');

CREATE TABLE Deltog
(Person INTEGER NOT NULL REFERENCES Personer(Nummer),
Provtillfälle INTEGER NOT NULL REFERENCES Provtillfällen(Nummer),
PRIMARY KEY (Person, Provtillfälle));

INSERT INTO Deltog VALUES (1, 3);
INSERT INTO Deltog VALUES (2, 2);
INSERT INTO Deltog VALUES (3, 1);
INSERT INTO Deltog VALUES (3, 2);
INSERT INTO Deltog VALUES (3, 3);

SELECT * FROM Granattyper;
SELECT * FROM Granater;
SELECT * FROM Provtillfällen;
SELECT * FROM Avfyrningar;
SELECT * FROM Personer;
SELECT * FROM Deltog;

Uppgift 3 (1 p)

Mycket i kursen handlar om relationsmodellen och relationsdatabaser. Vad är det som är "relationerna" i en relationsdatabas?

Svar:

Det är tabellerna. Det är inte kopplingarna mellan tabellerna i form av referensattribut eller främmande nycklar, och det är inte heller sambandstyperna i ett ER-diagram.

Uppgift 4 (13 p)

Formulera följande frågor i SQL. Definiera gärna vyer eller CTE:er om det underlättar, men skapa inte nya tabeller.

a) (2p) Vilka datum har vi avfyrat granater av typen Strids-bomb 2000?

Svar:

SELECT Datum
FROM Avfyrningar, Granater, Granattyper
WHERE Avfyrningar.Granat = Granater.Nummer
AND Granater.Typ = Granattyper.Nummer
AND Granattyper.Namn = 'Strids-bomb 2000';

En alternativ lösning med nästlade frågor:

SELECT Datum
FROM Avfyrningar
WHERE Granat IN (SELECT Nummer
                 FROM Granater
                 WHERE Typ IN (SELECT Nummer
                               FROM Granattyper
                               WHERE Namn = 'Strids-bomb 2000'));

En lösning med explicit join:

SELECT Datum
FROM Avfyrningar JOIN Granater ON Avfyrningar.Granat = Granater.Nummer
        JOIN Granattyper ON Granater.Typ = Granattyper.Nummer
WHERE Granattyper.Namn = 'Strids-bomb 2000';

b) (2p) Det finns bara en person i databasen som heter Kajsa Kanon. Vad heter de skjutfält som hon varit på?

Svar:

SELECT Provtillfällen.Plats
FROM Personer, Deltog, Provtillfällen
WHERE Personer.Namn = 'Kajsa Kanon'
AND Personer.Nummer = Deltog.Person
AND Deltog.Provtillfälle = Provtillfällen.Nummer;

c) (2p) Vad heter de granattyper som är tyngre än Strids-bomb 2000?

Svar:

SELECT Tyngre.Namn
FROM Granattyper AS SB2000, Granattyper AS Tyngre
WHERE Tyngre.Vikt > SB2000.Vikt
AND SB2000.Namn = 'Strids-bomb 2000';

Det kan vara enklare att använda nästlade frågor:

SELECT Namn
FROM Granattyper
WHERE Vikt > (SELECT Vikt
              FROM Granattyper
              WHERE Namn = 'Strids-bomb 2000');

d) (2p) Jag är ute och går på Villingsbergs skjutfält, och på marken hittar jag en granat som kommer från en provskjutning. Vilken typ av granat kan det vara?

Svar:

SELECT DISTINCT Granattyper.Namn
FROM Provtillfällen, Avfyrningar, Granater, Granattyper
WHERE Provtillfällen.Plats = 'Villingsbergs skjutfält'
AND Provtillfällen.Nummer = Avfyrningar.Provtillfälle
AND Avfyrningar.Granat = Granater.Nummer
AND Granater.Typ = Granattyper.Nummer;

e) (3p) Vad heter den granattyp som vi avfyrat flest gånger?

Svar:

WITH AvfyrningarPerTyp AS
(SELECT Granattyper.Namn AS Namn, COUNT(*) AS Antal
FROM Avfyrningar, Granater, Granattyper
WHERE Avfyrningar.Granat = Granater.Nummer
AND Granater.Typ = Granattyper.Nummer
GROUP BY Granattyper.Namn)
SELECT Namn
FROM AvfyrningarPerTyp
WHERE Antal = (SELECT MAX(Antal) FROM AvfyrningarPerTyp);

f) (2p) Finns det några granattyper som vi aldrig avfyrat? Vi vill veta namnen på dem.

Svar:

SELECT Namn
FROM Granattyper
WHERE Nummer NOT IN (SELECT Typ
                     FROM Granater
                     WHERE Nummer IN (SELECT Granat FROM Avfyrningar));

Uppgift 5 (4 p)

Det finns inte så många personer eller granattyper, men annars växer alla tabeller i databasen och blir mycket stora. Sökningarna i deluppgift 4a och 4b ovan körs ofta, men kanske med andra konstanter än de i uppgiften. Vi vill få dem att gå betydligt snabbare. Från början finns det inga index i databasen, inte ens på nycklar.

a) Vilka index vi bör skapa för att sökningen i 4a ska gå snabbare?

Svar:

Avfyrningar.Granat
Granater.Nummer
Granater.Typ

b) Vilka index vi bör skapa för att sökningen i 4b ska gå snabbare?

Svar:

Deltog.Person
Deltog.Provtillfälle
Provtillfällen.Nummer

Uppgift 6 (4 p)

a) Vad menas i databassammanhang med en normalform?

Svar:

En normalform är en regel för hur en tabell får utformas. Normalformen förbjuder vissa typer av dålig design som skulle kunna leda till problem när man ska lagra information och söka i databasen.

b) Ge ett exempel på en normalform som vi behandlat i kursen, och förklara vad den innebär.

Svar:

Vi tar första normalformen ("1NF")som exempel.

Första normalformen säger att alla värden som vi lagrar i tabellen måste vara enkla och atomära. Det betyder att en enskild ruta i tabellen bara får innehålla (högst) ett värde, inga listor eller sammansatta värden.

c) Vilket eller vilka problem är det som just denna normalform försöker förhindra? Visa med exempel!

Svar:

Annars skulle det bli besvärligt att arbeta med tabellen. Här är en tabell Personer, där varje person har nummer, namn och flera telefonnummer:

Personer
Nummer	Namn	Telefon
1	Anna	1490, 2230, 1650
2	Bodil	1777
3	Charlie	2822, 1490

SQL är gjort för att jämföra värdet i en ruta med värdet i en annan, så med den här lösningen kan man inte söka fram om flera personer har samma telefonnummer utan att använda komplicerad programmering med strängfunktioner.

Antag att man i stället följer första normalformen och delar upp tabellen i två:

Personer
Nummer	Namn
1	Anna
2	Bodil
3	Charlie

Telefonnummer
Person	Telefon
1	1490
1	2230
1	1650
2	1777
3	2822
3	1490

Nu är det lättare att söka fram att telefonnumret 1490 står på två ställen.

Uppgift 7 (3 p)

a) Vad menas, i databassammanhang, med en trigger?

Svar:

En trigger är en "aktiv regel", dvs en regel med någon typ av åtgärd som utförs när en händelse sker i databasen, till exempel att en ny rad läggs till i en tabell. Till skillnad från if-satser i ett vanligt program, som man uttryckligen måste köra varje gång man vill utföra åtgärden, kommer triggern att utlösas automatiskt när händelsen sker i databasen.

b) Beskriv två olika användningsområden för triggers i databaser.

Svar:

Att upprätthålla en materialiserad vy, till exempel summan av alla löner i en tabell med anställda, så att om data i tabellen ändras så ändras också den lagrade lönesumman.
Att kontrollera dynamiska integritetsvillkor, till exempel att löner inte får sänkas utan bara höjas.

Uppgift 8 (4 p)

I databassammanhang talar man om de så kallade ACID-egenskaperna, där I:et står för "isolering".

a) Vad är det för saker man isolerar från varandra?

Svar:

Transaktioner. En transaktion är en följd av operationer i databasen (ändringar eller sökningar) som hör ihop som en enhet.

b) Vad innebär det att man isolerar dessa saker?

Svar:

Varje transaktion ska "uppleva" det som att den är ensam i databasen, och att inga andra transaktioner körs samtidigt. Under transaktionens gång får det inte ske några ändringar i databasen, som transaktionen kan se och som den inte gjort själv. (SQL-standarden anger flera olika isoleringsnivåer, så det går att tillåta en viss åtkomst mellan transaktioner.)

c) Databashanteraren Mimer använder det som brukar kallas för optimistiska metoder för att åstadkomma isolering. Det är lite ovanligt, och de flesta andra databashanterare använder vad man kan kalla pessimistiska metoder. Vad innebär dessa två sätt, och vilka är de viktigaste skillnaderna?

Svar:

Pessimistiska metoder betyder att man låser data i databasen, så att bara en transaktion åt gången kan komma åt dem. Då kan det inte uppstå några "krockar" mellan transaktionerna, så att två eller flera transaktioner gör motstridiga operationer i databasen.

Optimistiska metoder använder inte lås, utan i stället genomför varje transaktionen alla sina operationer, och sedan kontrollerar databashanteraren efteråt (vid commit) om det gick bra eller inte.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 30 januari 2025