Databasteknik: Lösningar till hemtentamen 2020-06-02

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? Kanske skummar de början och hoppar över resten. 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 uppgift 4 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.

ER-diagram

Uppgift 2 (7 p)

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

a) Visa en tydlig översikt över dina tabeller. Ange vilka relationer som finns och vilka attribut varje relation innehåller, med väl valda exempeldata. Ange också alla kandidatnycklar, vilken av dessa som är primärnyckel, samt vilka referensattribut som finns och vad de refererar till.

Relationer (som är samma sak som tabeller), med primärnycklarna understrukna:

Myrstackar(Nummer, Latitud, Longitud)
Myror(Nummer, Bor_i)
Barr(Nummer, Längd, Vikt, På_stack)
Burit(Myra, Barr)

I Myrstackar bildar kombinationen av Latitud och Longitud antagligen en sammansatt alternativnyckel. (Man skulle kunna tänka sig myrstackar ovanpå varandra, till exempel en uppe i ett torn i en ruin, och man skulle också kunna tänka sig att upplösningen på koordinaterna är så dålig att flera myrstackar kan få samma koordinater trots att de egentligen är på olika platser.)

Referensattribut (som är samma sak som främmande nycklar):

Myror.Bor_i refererar till Myrstackar.Nummer
Barr.På_stack refererar till Myrstackar.Nummer
Burit.Myra refererar till Myror.Nummer
Burit.Barr refererar till Barr.Nummer

Exempeldata:

Myrstackar
Nummer	Latitud	Longitud
1	59.0	15.0
2	59.253934	15.247065
3	59.2	15.2
4711	59.1	15.1

Myror
Nummer	Bor_i
1	2
2	4711
3	4711
4	4711
17	3

Barr
Nummer	Längd	Vikt	På_stack
1	0.1	0.1	1
2	0.2	0.2	2
3	0.3	0.3	3
4	0.4	0.4	3
5	0.5	0.5	3
6	0.6	0.6	4711
7	0.7	0.7	4711
8	0.8	0.8	4711
193	0.9	0.9	4711

Burit
Myra	Barr
2	6
3	6
3	193
17	3
17	4
17	5

b) Skriv de SQL-kommandon som behövs för att skapa tabellerna, med lämpliga integritetsvillkor.

(Om man ska ge varje myra och varje barr ett unikt nummer, kanske vanliga heltal av datatypen integer inte räcker till. Det behövs fler olika värden än som går att lagra. Men det behöver vi inte bry oss om i den här uppgiften.)

CREATE TABLE Myrstackar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Latitud FLOAT NOT NULL,
Longitud FLOAT NOT NULL,
UNIQUE (Latitud, Longitud));

CREATE TABLE Myror
(Nummer INTEGER NOT NULL PRIMARY KEY,
Bor_i INTEGER NOT NULL REFERENCES Myrstackar(Nummer));

CREATE TABLE Barr
(Nummer INTEGER NOT NULL PRIMARY KEY,
Längd FLOAT NOT NULL,
Vikt FLOAT NOT NULL,
På_stack INTEGER NULL REFERENCES Myrstackar(Nummer));

CREATE TABLE Burit
(Myra INTEGER NOT NULL REFERENCES Myror(Nummer),
Barr INTEGER NOT NULL REFERENCES Barr(Nummer),
PRIMARY KEY (Myra, Barr));

Exempeldata:

INSERT INTO Myrstackar (Nummer, Latitud, Longitud) VALUES (1, 59.0, 15.0);
INSERT INTO Myrstackar (Nummer, Latitud, Longitud) VALUES (2, 59.253934, 15.247065);
INSERT INTO Myrstackar (Nummer, Latitud, Longitud) VALUES (3, 59.2, 15.2);
INSERT INTO Myrstackar (Nummer, Latitud, Longitud) VALUES (4711, 59.1, 15.1);
    
INSERT INTO Myror (Nummer, Bor_i) VALUES (1, 2);
INSERT INTO Myror (Nummer, Bor_i) VALUES (2, 4711);
INSERT INTO Myror (Nummer, Bor_i) VALUES (3, 4711);
INSERT INTO Myror (Nummer, Bor_i) VALUES (4, 4711);
INSERT INTO Myror (Nummer, Bor_i) VALUES (17, 3);

INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (1, 0.1, 0.1, 1);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (2, 0.2, 0.2, 2);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (3, 0.3, 0.3, 3);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (4, 0.4, 0.4, 3);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (5, 0.5, 0.5, 3);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (6, 0.6, 0.6, 4711);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (7, 0.7, 0.7, 4711);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (8, 0.8, 0.8, 4711);
INSERT INTO Barr (Nummer, Längd, Vikt, På_stack) VALUES (193, 0.9, 0.9, 4711);

INSERT INTO Burit (Myra, Barr) VALUES (17, 3);
INSERT INTO Burit (Myra, Barr) VALUES (17, 4);
INSERT INTO Burit (Myra, Barr) VALUES (17, 5);

INSERT INTO Burit (Myra, Barr) VALUES (2, 6);
INSERT INTO Burit (Myra, Barr) VALUES (3, 6);
INSERT INTO Burit (Myra, Barr) VALUES (3, 193);

SELECT * FROM Myrstackar;
SELECT * FROM Myror;
SELECT * FROM Barr;
SELECT * FROM Burit;

Uppgift 3 (2 p)

Databasen är nu tom. Skriv de INSERT-kommandon som behövs för att lägga in myra nummer 1 som bor i myrstack nummer 2, med koordinaterna latitud 59,253934, longitud 15,247065.

INSERT INTO Myrstackar (Nummer, Latitud, Longitud) VALUES (2, 59.253934, 15.247065);
INSERT INTO Myror (Nummer, Bor_i) VALUES (1, 2);

Uppgift 4 (11 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) Myra nummer 17 hittar inte hem. Vad är koordinaterna för myrstacken som hon bor i?

Några olika sätt som man kan skriva frågan på:

SELECT Myrstackar.Latitud, Myrstackar.Longitud
FROM Myror, Myrstackar
WHERE Myrstackar.Nummer = Myror.Bor_i
AND Myror.Nummer = 17;

SELECT Latitud, Longitud
FROM Myror, Myrstackar
WHERE Myrstackar.Nummer = Bor_i
AND Myror.Nummer = 17;

SELECT Myrstackar.Latitud, Myrstackar.Longitud
FROM Myror JOIN Myrstackar ON Myrstackar.Nummer = Myror.Bor_i
WHERE Myror.Nummer = 17;

SELECT Latitud, Longitud
FROM Myrstackar
WHERE Nummer IN (SELECT Bor_i
                FROM Myror
                WHERE Nummer = 17);

b) (2p) Här på myrstacken hittar vi barr nummer 193. Vilka myror hjälpte till att bära hem det barret?

SELECT Myror.Nummer
FROM Myror, Burit
WHERE Myror.Nummer = Burit.Myra
AND Burit.Barr = 193;

Eller bara:

SELECT Myra
FROM Burit
WHERE Barr = 193;

c) (2p) Här är myrstack nummer 4711. Vilka myrstackar ligger norr om den? (Det ser man på att deras latitud är större.) Vi vill veta de myrstackarnas nummer och koordinater.

SELECT Nummer, Latitud, Longitud
FROM Myrstackar
WHERE Latitud > (SELECT Latitud
                 FROM Myrstackar
                 WHERE Nummer = 4711);

d) (2p) Här är (fortfarande) myrstack nummer 4711. Hur mycket väger den? Dvs, vad är den sammanlagda vikten av alla barr i den stacken?

SELECT SUM(Vikt)
FROM Barr
WHERE På_stack = 4711;

e) (3p) Och en fråga till om myrstack nummer 4711. Vilken myra i stacken är flitigast? Dvs, vilken myra har burit flest barr?

Med en CTE:

WITH antal_burna_barr_i_stack_4711 AS
(SELECT Myror.Nummer AS Myrnummer, COUNT(*) AS AntalBarr
FROM Burit, Myror
WHERE Burit.Myra = Myror.Nummer
AND Myror.Bor_i = 4711
GROUP BY Myror.Nummer)
SELECT Myrnummer
FROM antal_burna_barr_i_stack_4711
WHERE AntalBarr = (SELECT MAX(AntalBarr)
                   FROM antal_burna_barr_i_stack_4711);

Med en vy:

CREATE VIEW antal_burna_barr_i_stack_4711 AS
SELECT Myror.Nummer AS Myrnummer, COUNT(*) AS AntalBarr
FROM Burit, Myror
WHERE Burit.Myra = Myror.Nummer
AND Myror.Bor_i = 4711
GROUP BY Myror.Nummer;

SELECT Myrnummer
FROM antal_burna_barr_i_stack_4711
WHERE AntalBarr = (SELECT MAX(AntalBarr)
                   FROM antal_burna_barr_i_stack_4711);

Uppgift 5 (4 p)

Det blir förstås väldigt många myror och barr i databasen, och åtminstone några hundra myrstackar. Från början finns inga index alls i databasen, inte ens på nycklar.

Sökningarna i deluppgift a och b i frågan ovan körs ofta, men kanske med andra konstanter (till exempel att man söker efter myra 6312111658 i stället för myra 17). De tar för lång tid att köra. Hur skulle man kunna få de sökningarna att gå snabbare att köra?

Skapa index på Myror.Nummer, Myrstackar.Nummer och Burit.Barr. Även Myror.Bor_i och (beroende på hur man skrev frågan i deluppgift b) Burit.Myra, om man ska följa de enkla reglerna om vilka kolumner som används i sökningen, men de indexen kommer troligen inte att behövas.

SQL-kommandon:

CREATE INDEX Myrnummer on Myror(Nummer);
CREATE INDEX Myrstacksnummer on Myrstackar(Nummer);
CREATE INDEX Buritbarr on Burit(Barr);
CREATE INDEX Myrboende on Myror(Bor_i); -- kanske
CREATE INDEX Buritmyra on Burit(Myra); -- kanske

Myrstackar.Latitud och Myrstackar.Longitud är fel.

Uppgift 6 (6 p)

Wikipedia skriver om myror att individerna delas in i de subfertila eller sterila honorna som utgör "arbetare" och "soldater", fertila hanar som utgör "drönare", och fertila honor som utgör "drottningar".

a) Rita ett EER-diagram över myrorna och de olika underkategorierna. De har unika nummer, som förut, men i den här uppgiften behöver vi inte bry oss om barr och myrstackar.

Man kan göra på olika sätt, och att identifiera entitetstyper (klasser) är både en hel vetenskap och en konst. En komplicerad variant:

Komplicerat EER-diagram

En enklare variant:

Enkelt EER-diagram

Med några exempeldata kan man visualisera de olika sorternas myror på ett annat sätt i ett mängddiagram. Om vi använder den komplicerade varianten av EER-diagrammet:

Mängddiagram

b) Implementera den beskrivna databasen i relationsmodellen, dvs översätt ER-diagrammet till tabeller. Implementationen ska vara bra. Visa en tydlig översikt över dina tabeller. Ange vilka relationer som finns och vilka attribut varje relation innehåller, med väl valda exempeldata. Ange också alla kandidatnycklar, vilken av dessa som är primärnyckel, samt vilka referensattribut som finns och vad de refererar till.

Vi väljer den komplicerade varianten ovan. Relationerna blir helt enkelt dessa. I varje relation är Nummer primärnyckel, och (i alla utom Myror) även referensattribut till relationen som implementerar överentitetstypen närmast ovanför i arvshierarkin.

Myror(Nummer)
Hanar(Nummer)
Drönare(Nummer)
Honor(Nummer)
Infertila(Nummer)
Arbetare(Nummer)
Soldater(Nummer)
Fertila(Nummer)
Drottningar(Nummer)

Exempeldata:

Myror
Nummer
1
2
3
4
5
6
7
8
9
10

Hanar
Nummer
1
2
3

Drönare
Nummer
1
2
3

Honor
Nummer
4
5
6
7
8
9
10

Infertila
Nummer
4
5
6
7
8

Arbetare
Nummer
4
5
6

Soldater
Nummer
7
8

Fertila
Nummer
9
10

Drottningar
Nummer
9
10

c) Skriv de SQL-kommandon som behövs för att skapa tabellerna, med lämpliga integritetsvillkor.

CREATE TABLE Myror
(Nummer INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE Hanar
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Myror(Nummer));

CREATE TABLE Drönare
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Hanar(Nummer));

CREATE TABLE Honor
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Myror(Nummer));

CREATE TABLE Infertila
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Honor(Nummer));

CREATE TABLE Arbetare
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Infertila(Nummer));

CREATE TABLE Soldater
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Infertila(Nummer));

CREATE TABLE Fertila
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Honor(Nummer));

CREATE TABLE Drottningar
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Fertila(Nummer));

Uppgift 7 (3 p)

ACID-transaktioner kan vara viktiga när man arbetar med databaser. Välj någon av de fyra egenskaperna och visa hur det skulle kunna uppstå problem i vår myrdatabas om egenskapen saknades. Ge specifika exempel utgående från scenariot.

Exempel: A, som i atomicitet. Antingen ska alla ändringar som en transaktion gör genomföras och finnas kvar i databasen, eller inga. Om en myra bär ett barr till stacken, behöver det noteras i två tabeller: Burit och Barr. Om vi gör ändringen i Burit men inte Barr, till exempel för att strömmen går efter den första ändringen, ser vi att myran burit ett barr, men inte att det nu finns på stacken. Myran kan bli misstänkt för att ha försnillat barret!

Exempel: C, som i Consistency Preserving (konsistensbevarande, eller bevarande av logisk koherens). Om databasen var i ett konsistent tillstånd, dvs utan inre motsägelser, före transaktionen, ska den också vara det efter transaktionen. Om vi till exempel lägger in en ny myra med ett nummer som redan använts, är nyckelvillkoret att numret ska vara unikt inte längre uppfyllt, och vet vi inte om det är den nya eller gamla myran som burit ett visst barr.

Exempel: I, som i isolering. Varje transaktion ska (beroende på hur hög grad av isolering man använder) uppleva det som att den är den enda samtidiga transaktionen i databasen. Data ska alltså inte plötsligt ändras av andra transaktioner medan transaktionen arbetar. Om vi ska lägga in en ny myra i databasen behöver vi ett unikt nummer, så vi kanske söker efter det högsta myrnumret (select max(Nummer) from Myror). Vi ökar det numret med ett, så vi får ett nytt och unikt myrnummer, och lägger in den myran i databasen. Men samtidigt har en annan transaktion gjort samma sak, och redan hunnit lägga in den myra med det numret. Myrnumret är alltså upptaget, trots att vi just kontrollerade att det var ledigt!

Exempel: D, som i Durability (hållbarhet). Om en myrinventerare jobbar hela dagen med att måla streckkoder på myror, och lägga in dem i databasen, får de data hon lagt in inte försvinna efter att hon gjort commit, till exempel om strömmen går i serverhallen där databasen finns, eller om Internet-förbindelsen till hennes bärbara dator bryts. Då skulle skogen vara full med myror som har målade streckkoder men inte finns i databasen, och någon annan myrinventerare kanske återanvänder de streckkoderna på andra myror.

Uppgift 8 (3 p)

Vad skulle de olika nivåerna i tre-schema-arkitekturen kunna innehålla i vår myrdatabas? Skriv inte bara vad nivåerna normalt innehåller, utan ge specifika exempel utgående från scenariot.

Ett externt schema innehåller vyer, till exempel om en person som ska inventera myrstackar behöver en vy med myrstackar, deras koordinater, vikt och antalet myror i varje stack.

Det logiska schemat är de tabeller som finns i databasen, enligt uppgift 2 ovan.

Det interna schemat beskriver lagringsformat och datastrukturer, som de index som skapade i uppgift 5.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 22 juni 2020