Databasteknik: Lösningar till tentamen 2022-03-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 (3 p)

Enligt scenariot omfattar varje tömningsrunda ett antal papperskorgar som man tömde på den rundan. Förklara varför sambandet mellan tömningsrundor och papperskorgar är ett många-till-många-samband och inte någon annan typ av samband.

Svar:

Det står i scenariot att varje tömningsrunda omfattar ett antal papperskorgar som man tömde. Alltså ska varje tömningsrunda kunna kopplas till flera olika papperskorgar, så sambandet mellan tömningsrundor och papperskorgar måste vara något-till-många.

Det vore orimligt att varje papperskorg bara skulle kunna tömmas en enda gång, och sen aldrig mer. Alltså ska varje papperskorg kunna kopplas till flera olika tömningsrundor, och sambandet mellan tömningsrundor och papperskorgar måste vara många-till-något.

Enklare uttryckt: Eftersom varje tömningsrunda ska kunna kopplas till flera olika papperskorgar, och varje papperskorg ska kunna kopplas till flera olika tömningsrundor, måste sambandet vara av typen många till många.

Uppgift 2 (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.

Svar:

Ett ER-diagram

Uppgift 3 (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:

Lokaler(Namn)
Papperskorgar(Nummer, Volym, Placering)
Tömningsrundor(Nummer, Datum)
Tömda(Papperskorg, Tömningsrunda)

Primärnycklarna är understrukna.

Främmande nycklar:

Papperskorgar.Placering till Lokaler.Namn
Tömda.Papperskorg till Papperskorgar.Namn
Tömda.Tömningsrunda till Tömningsrundor.Nummer

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

CREATE TABLE Lokaler
(Namn NVARCHAR(5) NOT NULL PRIMARY KEY);

CREATE TABLE Papperskorgar
(Nummer INTEGER NOT NULL PRIMARY KEY,
Volym INTEGER NOT NULL,
Placering NVARCHAR(5) NOT NULL REFERENCES Lokaler(Namn));

CREATE TABLE Tömningsrundor
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE NOT NULL);

CREATE TABLE Tömda
(Papperskorg INTEGER NOT NULL REFERENCES Papperskorgar(Nummer),
Tömningsrunda INTEGER NOT NULL REFERENCES Tömningsrundor(Nummer),
PRIMARY KEY (Papperskorg, Tömningsrunda));

INSERT INTO Lokaler (Namn) VALUES ('T120');
INSERT INTO Lokaler (Namn) VALUES ('T124');
INSERT INTO Lokaler (Namn) VALUES ('T002');
INSERT INTO Lokaler (Namn) VALUES ('T004');
INSERT INTO Lokaler (Namn) VALUES ('T006');

INSERT INTO Papperskorgar (Nummer, Volym, Placering)
VALUES (1, 10, 'T120') UNION
VALUES (2, 10, 'T120') UNION
VALUES (3, 10, 'T120') UNION
VALUES (4, 20, 'T124') UNION
VALUES (5, 20, 'T124') UNION
VALUES (6, 20, 'T124');

INSERT INTO Tömningsrundor (Nummer, Datum)
VALUES (1, DATE '2022-03-14') UNION
VALUES (2, DATE '2022-03-14') UNION
VALUES (3, DATE '2022-03-15');

INSERT INTO Tömda (Papperskorg, Tömningsrunda)
VALUES (1, 1) UNION
VALUES (2, 1) UNION
VALUES (3, 1) UNION
VALUES (4, 1) UNION
VALUES (5, 1) UNION
VALUES (6, 1) UNION
VALUES (1, 2) UNION
VALUES (2, 2) UNION
VALUES (3, 2) UNION
VALUES (4, 3) UNION
VALUES (5, 3) UNION
VALUES (6, 3);

Uppgift 4 (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) Hur stora är de papperskorgar som finns i T120? Vi vill veta volymen på var och en av papperskorgarna.

Svar:

SELECT Volym FROM Papperskorgar WHERE Placering = 'T120';

b) (2p) Vilka dagar har vi tömt minst en av papperskorgarna i T120?

Svar:

SELECT DISTINCT Tömningsrundor.Datum
FROM Tömningsrundor, Tömda, Papperskorgar
WHERE Tömningsrundor.Nummer = Tömda.Tömningsrunda
AND Tömda.Papperskorg = Papperskorgar.Nummer
AND Papperskorgar.Placering = 'T120';

c) (2p) Var står den största papperskorgen?

Svar:

SELECT Placering
FROM Papperskorgar
WHERE Volym = (SELECT MAX(Volym) FROM Papperskorgar);

d) (2p) I vilken lokal finns det flest papperskorgar?

Svar:

WITH AntalPapperskorgarPerLokal AS
(SELECT Papperskorgar.Placering AS Lokal, COUNT(*) AS Antal
FROM Papperskorgar
GROUP BY Papperskorgar.Placering)
SELECT Lokal
FROM AntalPapperskorgarPerLokal
WHERE Antal = (SELECT MAX(Antal) FROM AntalPapperskorgarPerLokal);

e) (2p) I vilka lokaler finns det inga papperskorgar alls?

Svar:

SELECT Namn
FROM Lokaler
WHERE Namn NOT IN (SELECT Placering FROM Papperskorgar);

Uppgift 5 (4 p)

En databas innehåller tre tabeller: tabellen A, med kolumnerna A1, A2 och A3, tabellen B, med kolumnerna B1, B2 och B3, och tabellen C, med kolumnerna C1, C2 och C3. Alla tabellerna innehåller flera miljoner rader.

Den här select-frågan ställs ofta mot databasen:

select A1, B1, C1
from A, B, C
where A2 = B2
and B3 = C3
and A3 = K1
and C2 = K2

K1 och K2 är konstanter, som varierar mellan olika körningar av frågan.

a) Frågan tar för lång tid att köra. Vi ser att det inte finns några index i databasen, inte ens på nycklar. Om vi vill att frågan ska gå snabbt att köra, vilka index skulle du rekommendera att man skapar? Motivera vart och ett av indexen.

Svar:

Man bör skapa dessa index:

CREATE INDEX A2index ON A(A2);
CREATE INDEX A3index ON A(A3);
CREATE INDEX B2index ON B(B2);
CREATE INDEX B3index ON B(B3);
CREATE INDEX C2index ON C(C2);
CREATE INDEX C3index ON C(C3);

Dessa är de kolumner som används i WHERE-villkoret, antingen för att jämföra med konstanter (A3 och C2) eller i join-villkor (A2, B2, B3 och C3). Därför kan databashanteraren komma att använda värdena i dessa kolumner för att söka fram rader i tabellerna.

b) Föreslå något index på någon av de tre tabellerna som inte skulle få frågan att bli snabbare, och motivera varför det inte hjälper.

Svar:

Dessa index skulle inte göra frågan snabbare:

CREATE INDEX A1index ON A(A1);
CREATE INDEX B1index ON B(B1);
CREATE INDEX C1index ON C(C1);

Dessa kolumner finns visserligen med i select-frågan, men bara i resultatet, så databashanteraren kommer inte att använda värdena i dessa kolumner för att söka fram rader i tabellerna. Därför skulle de inte göra frågan snabbare.

Uppgift 6 (4 p)

Här är tabellen A från uppgiften ovan, med de data som den just nu innehåller:

A
A1	A2	A3
1	2	3
2	2	3
4	3	4
3	5	4

Vi vet att kolumnen A1 är den enda kandidatnyckeln i tabellen.

a) Någon frågar dig vilka fullständiga funktionella beroenden som finns i tabellen. Förklara varför det inte går att svara på den frågan!

Svar:

Eftersom vi vet att att A1 är en kandidatnyckel, och den är inte sammansatt, vet vi att det finns ett ffb från A1 till A2 och ett ffb från A1 till A3. Men för att kunna bestämma såväl kandidatnycklar som ffb i en tabell måste man veta vad tabellen och dess data betyder, och det vet inte här.

Kanske anger A2 ett land, och A3 vilken världsdel landet ligger i. Då finns ett ffb från A2 till A3. Kanske anger A2 ett land, och A3 en person som besökt det landet. Då finns inget ffb från A2 till A3.

b) Ange något fullständigt funktionellt beroende som inte finns i tabellen.

A3 till A2. Värdet 4 i kolumnen A3 står på en rad med värdet 3 i A2, och på en annan rad med värdet 5 i A2. Alltså är ett och samma värde på A3 inte alltid kopplat till ett och samma värde på A2, och det kan alltså inte finnas ett ffb från A3 till A2.

På samma sätt finns inte dessa ffb: A2 till A1. A3 till A1. {A2, A3} till A1. Dessutom vet vi, eftersom A1 är den enda kandidatnyckeln, att dessa beroenden inte finns. Om något bestämmer en kandidatnyckel måste den också själv vara en kandidatnyckel.

Eftersom vi vet att A1 är en kandidatnyckel, och att det därför finns ett ffb från A1 till A2 och ett ffb från A1 till A3, och determinanter måste vara minimala, kan det inte finnas ett ffb från {A1, A2} till A3, och inte heller från {A1, A3} till A2.

c) Någon frågar dig varför raderna kommer i fel ordning. A1 var ju nyckel, så varför kommer raden där A1 är 4 före raden där A1 är 3? Förklara!

Svar:

Raderna i en tabell i en relationsdatabas har ingen definierad ordning. Därför finns det ingen ordning som är rätt och ingen som är fel. När man skriver ut eller på annat sätt går igenom raderna i tabellen kan de komma i vilken ordning som helst, och det enda sättet att få en viss ordning är att ange "ORDER BY" i SQL. (Däremot brukar det oftast vara så att de kommer i ordning efter den primärnyckel som man angett.)

Uppgift 7 (3 p)

Nämn tre olika databashanterare, och ange för var och en av dem någon egenskap som skiljer den från de två andra.

Svar:

....

Uppgift 8 (5 p)

Förklara skillnaden mellan

a) ett index och en primärnyckel

Svar:

Ett index är en intern datastruktur som databashanteraren använder för att hitta rader i en tabell. En primärnyckel är en logisk begränsning på vilka data som en tabell kan innehålla.

b) en primärnyckel och en främmande nyckel

Svar:

En primärnyckel är en kolumn, eller en kombination av kolumner, som är garanterad att vara unik. Dvs, det kan aldrig finnas två rader i tabellen med samma värden på primärnyckeln. En främmande nyckel är en kolumn, eller en kombination av kolumner, som refererar till en annan (eller i vissa fall samma) tabell, genom att det står samma värden för den främmande nyckeln på en rad som det står i primärnyckeln (eller i vissa fall en kandidatnyckel) på den rad i den andra tabellen som man refererar till.

c) ett ER-diagram och ett EER-diagram

Svar:

I EER-diagrammet finns dessutom under- och överentitetstyper (även kallade subklasser och superklasser), med arv.

d) atomicitet (A:et i ACID) och isolering (I:et i ACID)

Svar:

Atomicitet gäller en enda transaktion, och att antingen måste alla den transaktionens ändringar sparas i databasen eller också inga av dem. Isolering handlar däremot om flera transaktioner, och att de inte ska störa varandra på olämpliga sätt genom att skriva över varandras ändringar eller läsa varandras halvfärdiga ändringar.

e) en relation och en tabell

Svar:

Det är samma sak.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 3 april 2022