Databasteknik: Lösningar till tentamen 2024-05-29

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 EER-diagram

Kommentarer om lösningen ovan:

  • Man kan tänka sig fullständigt deltagande på en del ställen, utom där det uttryckligen står i scenariot att det inte är fullständigt deltagande: "En förening behöver inte nödvändigtvis vara ansluten till något förbund"; "En ishockeyklubb, som bara är ansluten till Svenska Ishockeyförbundet, kanske har en egen gren som heter luftballonghockey, och som det inte finns något förbund för".

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:

Tabeller (kallas också relationer):

  • Idrotter(Nummer, Namn)
  • Förbund(Nummer, Namn)
  • Organiserar(Förbund, Idrott)
  • Föreningar(Nummer, Namn)
  • Anslutna(Förening, Förbund)
  • Bedriver(Förening, Idrott)

Kandidatnycklar är understrukna. I de tabeller där det finns flera kandidatnycklar, är den första primärnyckel.

Referensattribut (kallas också främmande nycklar):

  • Organiserar.Förbund refererar till Förbund.Nummer
  • Organiserar.Idrott refererar till Idrotter.Nummer
  • Anslutna.Förening refererar till Föreningar.Nummer
  • Anslutna.Förbund refererar till Förbund.Nummer
  • Bedriver.Förening refererar till Föreningar.Nummer
  • Bedriver.Idrott refererar till Idrotter.Nummer

Här nedan visa tabellerna med exempeldata. I tabellerna Organiserar, Anslutna och Bedriver är den enda kandidatnyckeln, och därmed primärnyckeln, sammansatt av båda kolumnerna, men jag vet inte hur man skiver i HTML för att göra ett streck som går genom flera kolumner.

Idrotter
Nummer Namn
1 fotboll
2 snöskolöpning
3 schackboxning
4 luftballonghockey
5 varpa
6 hästsko
7 strafing

Förbund
Nummer Namn
1 Svenska Fotbollförbundet
2 Svenska Skyttesportförbundet
3 Sveriges schackförbund
4 Svenska Pistolskytteförbundet
5 Svenska Ishockeyförbundet
6 Svenska Varpaförbundet
7 Sveriges Akademiska Idrottsförbund
8 Korpen Svenska Motionsidrottsförbundet
9 Svenska Flygsportförbundet
10 Nationella Bubbelplastpoppningsförbundet

Organiserar
Förbund Idrott
1 1
3 3
6 5
6 6
9 7

Föreningar
Nummer Namn
1 Djurgårdens Idrottsförening
2 Allmänna Idrottsklubben
3 Almby Idrottsklubb

Anslutna
Förening Förbund
1 1
1 5
2 1
2 5
3 8

Bedriver
Förening Idrott
1 1
2 1
3 3
3 4
3 7

(Observera att var och en av tabellerna Organiserar, Anslutna och Bedriver har en sammansatt kandidatnyckel, och därmed primärnyckel.)

Nedan visas create table-kommandon och exempeldata. Det krävs inte som svar, men är med för att underlätta provkörningar. 

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

INSERT INTO Idrotter VALUES (1, 'fotboll');
INSERT INTO Idrotter VALUES (2, 'snöskolöpning');
INSERT INTO Idrotter VALUES (3, 'schackboxning');
INSERT INTO Idrotter VALUES (4, 'luftballonghockey');
INSERT INTO Idrotter VALUES (5, 'varpa');
INSERT INTO Idrotter VALUES (6, 'hästsko');
INSERT INTO Idrotter VALUES (7, 'strafing');

CREATE TABLE Förbund
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(40) NOT NULL UNIQUE);

INSERT INTO Förbund VALUES (1, 'Svenska Fotbollförbundet');
INSERT INTO Förbund VALUES (2, 'Svenska Skyttesportförbundet');
INSERT INTO Förbund VALUES (3, 'Sveriges schackförbund');
INSERT INTO Förbund VALUES (4, 'Svenska Pistolskytteförbundet');
INSERT INTO Förbund VALUES (5, 'Svenska Ishockeyförbundet');
INSERT INTO Förbund VALUES (6, 'Svenska Varpaförbundet');
INSERT INTO Förbund VALUES (7, 'Sveriges Akademiska Idrottsförbund');
INSERT INTO Förbund VALUES (8, 'Korpen Svenska Motionsidrottsförbundet');
INSERT INTO Förbund VALUES (9, 'Svenska Flygsportförbundet');
INSERT INTO Förbund VALUES (10, 'Nationella Bubbelplastpoppningsförbundet');

CREATE TABLE Organiserar
(Förbund INTEGER REFERENCES Förbund (Nummer),
Idrott INTEGER REFERENCES Idrotter (Nummer),
PRIMARY KEY (Förbund, Idrott));

INSERT INTO Organiserar VALUES (1, 1);
INSERT INTO Organiserar VALUES (3, 3);
INSERT INTO Organiserar VALUES (6, 5);
INSERT INTO Organiserar VALUES (6, 6);
INSERT INTO Organiserar VALUES (9, 7);

CREATE TABLE Föreningar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(30) NOT NULL UNIQUE);

INSERT INTO Föreningar VALUES (1, 'Djurgårdens Idrottsförening');
INSERT INTO Föreningar VALUES (2, 'Allmänna Idrottsklubben');
INSERT INTO Föreningar VALUES (3, 'Almby Idrottsklubb');

CREATE TABLE Anslutna
(Förening INTEGER REFERENCES Föreningar (Nummer),
Förbund INTEGER REFERENCES Förbund (Nummer),
PRIMARY KEY (Förening, Förbund));

INSERT INTO Anslutna VALUES (1, 1);
INSERT INTO Anslutna VALUES (1, 5);
INSERT INTO Anslutna VALUES (2, 1);
INSERT INTO Anslutna VALUES (2, 5);
INSERT INTO Anslutna VALUES (3, 8);

CREATE TABLE Bedriver
(Förening INTEGER REFERENCES Föreningar (Nummer),
Idrott INTEGER REFERENCES Idrotter (Nummer),
PRIMARY KEY (Förening, Idrott));

INSERT INTO Bedriver VALUES (1, 1);
INSERT INTO Bedriver VALUES (2, 1);
INSERT INTO Bedriver VALUES (3, 3);
INSERT INTO Bedriver VALUES (3, 4);
INSERT INTO Bedriver VALUES (3, 7);

SELECT * FROM Idrotter;
SELECT * FROM Förbund;
SELECT * FROM Organiserar;
SELECT * FROM Föreningar;
SELECT * FROM Anslutna;
SELECT * FROM Bedriver;

Uppgift 3 (10 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 olika idrotter organiseras av Svenska Varpaförbundet?

Svar:

Några olika sätt att forumlera sökningen: 

SELECT Idrotter.Namn
FROM Förbund, Organiserar, Idrotter
WHERE Förbund.Nummer = Organiserar.Förbund
AND Organiserar.Idrott = Idrotter.Nummer
AND Förbund.Namn = 'Svenska Varpaförbundet';

SELECT Idrotter.Namn
FROM Förbund JOIN Organiserar ON Förbund.Nummer = Organiserar.Förbund
    JOIN Idrotter ON Organiserar.Idrott = Idrotter.Nummer
WHERE Förbund.Namn = 'Svenska Varpaförbundet';

SELECT Namn FROM Idrotter WHERE Nummer IN
(SELECT Idrott FROM Organiserar WHERE Förbund IN
(SELECT Nummer FROM Förbund WHERE Namn = 'Svenska Varpaförbundet'));

b) (2p) De flesta förbunden heter något med Sveriges eller Svenska, till exempel Sveriges Akademiska Idrottsförbund. eller Korpen Svenska Motionsidrottsförbundet. Vilka förbund heter inte något med Sveriges eller Svenska?

Svar: 

SELECT Namn
FROM Förbund
WHERE Namn NOT LIKE '%Sveriges%'
AND Namn NOT LIKE '%Svenska%';

c) (3p) Vilka idrotter som organiseras av Svenska Flygsportförbundet bedrivs av Almby Idrottsklubb?

Svar:

Ett par olika sätt att forumlera sökningen: 

SELECT Idrotter.Namn
FROM Föreningar, Bedriver, Idrotter, Organiserar, Förbund
WHERE Föreningar.Nummer = Bedriver.Förening
AND Bedriver.Idrott = Idrotter.Nummer
AND Föreningar.Namn = 'Almby Idrottsklubb'
AND Idrotter.Nummer = Organiserar.Idrott
AND Organiserar.Förbund = Förbund.Nummer
AND Förbund.Namn = 'Svenska Flygsportförbundet';

SELECT Namn
FROM Idrotter
WHERE Nummer IN (SELECT Idrott
                 FROM Bedriver
                 WHERE Förening IN (SELECT Nummer
                                    FROM Föreningar
                                    WHERE Namn = 'Almby Idrottsklubb'))
AND Nummer IN (SELECT Idrott
               FROM Organiserar
               WHERE Förbund IN (SELECT Nummer
                                 FROM Förbund
                                 WHERE Namn = 'Svenska Flygsportförbundet'));

d) (3p) Vilken förening bedriver det största antalet idrotter?

Svar:

En lösning med en CTE: 
WITH AntalIdrotterPerFörening AS
(SELECT Föreningar.Nummer, Föreningar.Namn, COUNT(*) AS Antal
FROM Föreningar, Bedriver, Idrotter
WHERE Föreningar.Nummer = Bedriver.Förening
AND Bedriver.Idrott = Idrotter.Nummer
GROUP BY Föreningar.Nummer, Föreningar.Namn)
SELECT Nummer, Namn
FROM AntalIdrotterPerFörening
WHERE Antal = (SELECT MAX(Antal) FROM AntalIdrotterPerFörening);
En lösning med en vy: 
CREATE VIEW AntalIdrotterPerFörening AS
SELECT Föreningar.Nummer, Föreningar.Namn, COUNT(*) AS Antal
FROM Föreningar, Bedriver, Idrotter
WHERE Föreningar.Nummer = Bedriver.Förening
AND Bedriver.Idrott = Idrotter.Nummer
GROUP BY Föreningar.Nummer, Föreningar.Namn;

SELECT Nummer, Namn
FROM AntalIdrotterPerFörening
WHERE Antal = (SELECT MAX(Antal) FROM AntalIdrotterPerFörening);

Uppgift 4 (3 p)

Alla tabeller i databasen växer och blir mycket stora. Sökningarna a, b och c i uppgiften ovan körs ofta, men kanske med andra konstanter än de i uppgiften, och vi märker att de går för långsamt. Vi ser att det inte finns några index i databasen, inte ens på nycklar.

a) Hur kan man få sökningen i deluppgift 3a att gå snabbare?

Svar:

Skapa index på de kolumner som kan komma att användas för att söka fram rader i tabellerna:

  • CREATE INDEX Förbundsnummerindex ON Förbund(Nummer);
  • CREATE INDEX Organiserarförbundsindex ON Organiserar(Förbund);
  • CREATE INDEX Organiseraridrottsindex ON Organiserar(Idrott);
  • CREATE INDEX Idrottsnummerindex ON Idrotter(Nummer);
  • CREATE INDEX Förbundsnamnsindex ON Förbund(Namn);
Man kan göra antaganden om hur frågeoptimeraren väljer att utföra sökningen, och nöja sig med index på Förbund.Namn, Organiserar.Förbund och Idrotter.Nummer.

Idrotter.Namn är bara med i resultatet från sökningen, och används inte för att söka fram rader, så ett index på den hjälper normalt inte.

b) Hur kan man få sökningen i deluppgift 3b att gå snabbare?

Svar:

Det är betydligt svårare. Vi vill hitta de namn som inte innehåller orden Sveriges och Svenska, även mitt inne i namnet, och då fungerar det inte med ett vanligt index på Förbund.Namn. Ett vanligt index på ett textfält hjälper oss bara att söka på ett prefix till texten. En del databashanterare har möjlighet till effektiv fritextsökning genom att bygga särskilda index, men annars kan man göra en egen lösning och lagra vilka ord som förekommer i vilka namn, kanske kombinerat med triggers för att uppdatera dem automatiskt. Då behöver man också skriva om frågan, och använda de nya tabeller man skapat.

c) Hur kan man få sökningen i deluppgift 3c att gå snabbare?

Svar:

Skapa index på de kolumner som kan komma att användas för att söka fram rader i tabellerna:

  • CREATE INDEX FöreningarNummerIndex ON Föreningar(Nummer);
  • CREATE INDEX BedriverFöreningIndex ON Bedriver(Förening);
  • CREATE INDEX BedriverIdrottIndex ON Bedriver(Idrott);
  • CREATE INDEX IdrotterNummerIndex ON Idrotter(Nummer);
  • CREATE INDEX FöreningarNamnIndex ON Föreningar(Namn);
  • CREATE INDEX OrganiserarIdrottIndex ON Organiserar(Idrott);
  • CREATE INDEX OrganiserarFörbundIndex ON Organiserar(Förbund);
  • CREATE INDEX FörbundNummerIndex ON Förbund(Nummer);
  • CREATE INDEX FörbundNamnIndex ON Förbund(Namn);
Här är det svårt att göra antaganden om hur frågeoptimeraren väljer att utföra sökningen, och om den börjar med förbundsnamn och sen jobbar sig vidare, eller om den börjar med föreningsnamn.

Idrotter.Namn är bara med i resultatet från sökningen, och används inte för att söka fram rader, så ett index på den hjälper normalt inte.

Uppgift 5 (3 p)

Ibland lägger man ner en idrottsförening, och då behöver vi ta bort den ur databasen. Men för att den inte ska glömmas bort helt, vill vi flytta den till en annan tabell.

Hur skulle man kunna använda en trigger för det? Du behöver inte skriva SQL-kommandon, utan du kan beskriva triggern med ord.

Svar:

Skapa en trigger on delete för tabellen med föreningar, som gör en insert i tabellen med gamla föreningar. Så här kan det se ut i Mimer, förutom att Mimer inte har for each row: 
CREATE TABLE GamlaFöreningar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(30) NOT NULL UNIQUE);

@
CREATE TRIGGER GlömOssInte
AFTER DELETE ON Föreningar
    REFERENCING OLD TABLE AS o
FOR EACH ROW
BEGIN ATOMIC
    DECLARE GamlaNumret INTEGER;
    DECLARE GamlaNamnet NVARCHAR(30);
    SELECT Nummer, Namn INTO GamlaNumret, GamlaNamnet FROM o;
    INSERT INTO GamlaFöreningar (Nummer, Namn) VALUES (GamlaNumret, GamlaNamnet);
END
@

Uppgift 6 (4 p)

Förklara kort vad följande SQL-kommandon gör:

a) DELETE

Svar:

Tar bort rader ur en tabell.

b) DROP

Svar:

Tar bort en hel tabell, vy, trigger eller annat objekt i databasens schema.

c) ROLLBACK

Svar:

Avbryter en transaktion och återställer de ändringar den gjort i databasen.

d) REVOKE

Svar:

Återkallar en rättighet, till exempel för att lägga till rader i en tabell, som man tidigare delat ut med GRANT.

Uppgift 7 (3 p)

a) Vad menar man i databassammanhang med en transaktion?

Svar:

En eller flera operationer i databasen som hör ihop som en enhet. Om man till exempel vill flytta pengar från ett bankkonto till ett annat måste man både dra bort pengarna från det ena kontot, och lägga till dem till det andra. En transaktion behöver inte nödvändigtvis göra ändringar, så exempelvis en summering av alla bankkontona kan vara en transaktion.

b) I samband med transaktioner brukar man tala om ACID-egenskaperna. Vilken av bokstäverna A, C, I och D är det som handlar om att förhindra "krockar" mellan samtidiga transaktioner?

Svar:

"I", som i "Isolation", dvs att olika transaktioner ska isoleras från varandra.

c) Många databashanterare använder lås för att förhindra sådana krockar. Då kan man få deadlock. Vad innebär det?

Svar:

Att en transaktion (låt oss kalla den T1) väntar på att få tillgång till en resurs, typiskt en eller flera rader i en tabell, som en annan transaktion (kallad T2) arbetar med och därför har "låst", så ingen annan transaktion kan komma åt dem. Men samtidigt väntar transaktionen T2 på att få tillgång till en resurs som T1 har låst. De väntar alltså på varandra, och kommer aldrig att kunna fortsätta. (Det kan vara fler än två transaktioner som väntar på varandra, i en cykel.)

Uppgift 8 (3 p)

Vad innebär den så kallade tre-schema-arkitekturen? Vad är det för tre scheman? Vilka fördelar får man av den arkitekturen?

Svar:

Att samma databas kan beskrivas med tre olika scheman, dvs beskrivningar av databasens struktur och vad som går att lagra i den:
  • Ett fysiskt schema, på den lägsta nivån, som beskriver interna lagringsstrukturer, som index.
  • Ett logiskt schema som beskriver databasens logiska uppbyggnad, till exempel vilka tabeller den innehåller, vilka kolumner de har och vilka nycklar som finns.
  • Fler olika externa scheman, med vyer, där vart och ett av dessa externa scheman visar upp databasen på ett visst sätt, till exempel med sammanställningar i stället för att direkt visa de tabeller som databasen innehåller.
Fördelen är att man får oberoende mellan nivåerna. Man kan ändra det fysiska schemat, till exempel genom att lägga till ett index på en kolumn, utan att det påverkar det logiska schemat och de SQL-frågor (eller motsvarande) som man kan göra, förutom att de går snabbare. Man kan ändra det logiska schemat, till exempel genom att dela upp en tabell i två, men med en vy som slår ihop de två nya tabellerna kan man få det att se ut som att den gamla tabellen finns kvar. Med flera olika externa scheman kan man också erbjuda olika användargrupper olika sätt att se databasen som är anpassade efter deras behov.

Uppgift 9 (3 p)

a) Vad innebär SQL-injektion?

Svar:

Att man med särskilt formulerade SQL-frågor, gärna med "konstigt" formulerad eller formaterad text som i namn, kan få databashanteraren att göra något annat än vad databasens skapare och programmerare avsett. Till exempel kan det handla om att få tillgång till data som man normalt inte ska ha tillgång till, eller att radera data på ett skadligt sätt.

b) Är det bra eller dåligt?

Svar:

Dåligt, för det är ett sätt att bryta sig in i databasen.

c) Visa med ett exempel hur det går till!

Svar:

Antag att man ska skriva in sitt namn och sitt lösenord på en webbsida, och så visar webbsidan hur mycket pengar man har på sitt konto. Webbprogrammeraren som skapade webbsidan lägger namnet man skriver in i en variabel som heter InmatatNamn och lösenordet i en variabel som heter InmatatLösenord. Därefter används det för uppslagning i tabellen Konton. SQL-frågan byggs upp som en sträng så här:

string query = "SELECT Belopp FROM Konton WHERE Namn = '" + InmatatNamn + "' AND Lösenord = '" + InmatatLösenord + "'";

Om jag till exempel skriver in namnet Thomas och lösenordet banan, blir SQL-frågan så här:

SELECT Belopp FROM Konton WHERE Namn = 'Thomas' AND Lösenord = 'banan'

Antag nu att jag vill veta hur mycket pengar kungen har. Jag kan förstås inte kungens lösenord, men om jag skriver in det ganska konstiga namnet Carl XVI Gustaf' OR '1'='2 och vilket lösenord som helst, till exempel banan, kommer SQL-frågan att bli:

SELECT Belopp FROM Konton WHERE Namn = 'Carl XVI Gustaf' OR '1'='2' AND Lösenord = 'banan'

Villkoret före OR är sant för kungens rad i tabellen. Villkoret efter OR är alltid falskt, och kan ignoreras. Alltså får jag beloppet för kungens konto.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 19 juni 2025