Databasteknik: Lösningar till tentamen 2023-01-11

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.

Ordet "attribut" böjs så här:
ett attribut, två attribut, det attributet, de attributen
inte t. ex. en attribut, två attributer

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å göra företagen som en egen entitetstyp, och man kan redan i ER-diagramet lägga till ett nummer som surrogatnyckel på problemtyperna.

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:

Personer(Nummer, Namn, Telefon, Företag)
Problemtyper(Nummer, Beskrivning)
Rapporter(Nummer, Datum, Tid, Inlämnare)
Gäller(Rapport, Problemtyp)

Primärnycklarna är understrukna. I tabellen Problemtyper är även Beskrivning en kandidatnyckel,

Främmande nycklar:

Rapporter.Inlämnare till Personer.Nummer
Gäller.Rapport till Rapporter.Nummer
Gäller.Problemtyp till Problemtyper.Nummer

Så här kan man också rita upp schemat:

Ett ER-diagram

Tabellerna med exempeldata:

Personer
Nummer	Namn	Telefon	Företag
1	Anna	1234	Polisen
2	Bodil	1234	null
3	Bodil	019-5678	Närkefrakt

Problemtyper
Nummer	Beskrivning
1	stod i vägen
2	slängd i en snödriva
3	rysk invasion

Rapporter
Nummer	Datum	Tid	Inlämnare
1	2022-12-24	10:22:00	2
2	2023-01-10	13:11:00	2
3	2023-01-11	04:22:00	3

Gäller
Rapport	Problemtyp
1	1
2	1
2	2
3	1
3	2

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

DROP TABLE Personer CASCADE;
DROP TABLE Problemtyper CASCADE;
DROP TABLE Rapporter CASCADE;
DROP TABLE Gäller CASCADE;

CREATE TABLE Personer
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL,
Telefon NVARCHAR(10) NOT NULL,
Företag NVARCHAR(10) NULL);

INSERT INTO Personer VALUES (1, 'Anna', '1234', 'Polisen');
INSERT INTO Personer VALUES (2, 'Bodil', '1234', NULL);
INSERT INTO Personer VALUES (3, 'Bodil', '019-5678', 'Närkefrakt');

CREATE TABLE Problemtyper
(Nummer INTEGER NOT NULL PRIMARY KEY,
Beskrivning NVARCHAR(21) NOT NULL UNIQUE);

INSERT INTO Problemtyper VALUES (1, 'stod i vägen');
INSERT INTO Problemtyper VALUES (2, 'slängd i en snödriva');
INSERT INTO Problemtyper VALUES (3, 'rysk invasion');

CREATE TABLE Rapporter
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE NOT NULL,
Tid TIME NOT NULL,
Inlämnare INTEGER NOT NULL REFERENCES Personer(Nummer));

INSERT INTO Rapporter VALUES (1, DATE '2022-12-24', TIME '10:22:00', 2);
INSERT INTO Rapporter VALUES (2, DATE '2023-01-10', TIME '13:11:00', 2);
INSERT INTO Rapporter VALUES (3, DATE '2023-01-11', TIME '04:22:00', 3);

CREATE TABLE Gäller
(Rapport INTEGER NOT NULL REFERENCES Rapporter(Nummer),
Problemtyp INTEGER NOT NULL REFERENCES Problemtyper(Nummer),
PRIMARY KEY (Rapport, Problemtyp));

INSERT INTO Gäller VALUES (1, 1);
INSERT INTO Gäller VALUES (2, 1);
INSERT INTO Gäller VALUES (2, 2);
INSERT INTO Gäller VALUES (3, 1);
INSERT INTO Gäller VALUES (3, 2);

SELECT * FROM Personer;
SELECT * FROM Problemtyper;
SELECT * FROM Rapporter;
SELECT * FROM Gäller;

Uppgift 3 (2 p)

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

Svar:

Tabellerna!

Uppgift 4 (5 p)

Här är några typer av integritetsvillkor som man kan skriva i SQL:

CHECK
FOREIGN KEY
NOT NULL
PRIMARY KEY
UNIQUE

Ge för var och en av dessa typer dels en kort förklaring till vad den innebär, och dels ett tydligt och konkret exempel på hur den skulle kunna användas i uppgift 2 ovan.

Svar:

CHECK-villkor används för att kontrollera data på en rad. Till exempel kanske man vill att beskrivningen av problemtyperna alltid ska bestå av minst två ord, och då skulle man kunna ange att den måste innehålla ett blanktecken:

CREATE TABLE Problemtyper
(Nummer INTEGER NOT NULL PRIMARY KEY,
Beskrivning NVARCHAR(21) NOT NULL UNIQUE CHECK (Beskrivning LIKE '% %'));

FOREIGN KEY används för att ange främmande nycklar. Man kan ange en främmande nyckel med bara REFERENCES, eller som en egen del när man skapar tabellen, exempelvis:

CREATE TABLE Rapporter
(Nummer INTEGER NOT NULL PRIMARY KEY,
Datum DATE NOT NULL,
Tid TIME NOT NULL,
Inlämnare INTEGER NOT NULL,
FOREIGN KEY (Inlämnare) REFERENCES Personer(Nummer));

Databashanteraren kommer att upprätthålla referensintegriteten för de främmande nycklar som man angivit. Den använder dem inte för att automatiskt koppla ihop tabeller i SQL-frågor med flera tabeller.

NOT NULL används för att ange att en ruta i tabellen inte får lämnas tom, som de tre första kolumnerna i tabellen Personer, men inte den sista:

CREATE TABLE Personer
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL,
Telefon NVARCHAR(10) NOT NULL,
Företag NVARCHAR(10) NULL);

PRIMARY KEY används för att ange primärnycklar, som kolumnen Nummer i tabellen ovan.

UNIQUE används för att ange alternativnycklar, förutom primärnyckeln, som i tabellen Problemtyper ovan, där Nummer är primärnyckel och Beskrivning är en alternativnyckel.

Uppgift 5 (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) Jag har telefonnummer 070-7347013. Vilka datum har jag skickat in rapporter om problem?

Svar:

SELECT DISTINCT Datum
FROM Rapporter, Personer
WHERE Rapporter.Inlämnare = Personer.Nummer
AND Personer.Telefon = '070-7347013';

Ett alternativ med en explicit join:

SELECT DISTINCT Datum
FROM Rapporter JOIN Personer ON Rapporter.Inlämnare = Personer.Nummer
WHERE Personer.Telefon = '070-7347013';

Ett alternativ med nästlade frågor:

SELECT DISTINCT Datum
FROM Rapporter
WHERE Inlämnare IN (SELECT Nummer
                    FROM Personer
                    WHERE Telefon = '070-7347013');

b) (2p) Telefonnummer i Örebro börjar på 019- och telefonnummer i Fjugesta börjar på 0585-. Sammanlagt hur många personer har telefonnummer i Örebro och Fjugesta?

Svar:

SELECT COUNT(*)
FROM Personer
WHERE Telefon LIKE '019-%' OR Telefon LIKE '0585-%';

c) (2p) Vilka typer av problem rapporterades igår (2023-01-10)? Vi vill veta beskrivningarna, inte bara numren.

Svar:

SELECT DISTINCT Problemtyper.Beskrivning
FROM Rapporter, Gäller, Problemtyper
WHERE Rapporter.Nummer = Gäller.Rapport
AND Gäller.Problemtyp = Problemtyper.Nummer
AND Rapporter.Datum = DATE '2023-01-10';

Ett alternativ med en explicit join:

SELECT DISTINCT Problemtyper.Beskrivning
FROM Rapporter JOIN Gäller ON Rapporter.Nummer = Gäller.Rapport
    JOIN Problemtyper ON Gäller.Problemtyp = Problemtyper.Nummer
WHERE Rapporter.Datum = DATE '2023-01-10';

Ett alternativ med nästlade frågor:

SELECT Beskrivning
FROM Problemtyper
WHERE Nummer IN (SELECT Problemtyp
                 FROM Gäller
                 WHERE Rapport IN (SELECT Nummer
                                   FROM Rapporter
                                   WHERE Datum = DATE '2023-01-10'));

d) (3p) Vilket problem är vanligast? Vi vill alltså veta beskrivningen på den problemtyp som det finns flest rapporter om.

Svar:

CREATE VIEW AntalRapporter AS
SELECT Problemtyp AS Typ, COUNT(*) AS Antal
FROM Gäller
GROUP BY Problemtyp;

SELECT Beskrivning
FROM Problemtyper
WHERE Nummer IN (SELECT Typ
                 FROM AntalRapporter
                 WHERE Antal IN (SELECT MAX(Antal) FROM AntalRapporter));

Ett alternativ med en CTE:

WITH AntalRapporter AS
(SELECT Problemtyp AS Typ, COUNT(*) AS Antal
FROM Gäller
GROUP BY Problemtyp)
SELECT Beskrivning
FROM Problemtyper
WHERE Nummer IN (SELECT Typ
                 FROM AntalRapporter
                 WHERE Antal IN (SELECT MAX(Antal) FROM AntalRapporter));

e) (2p) Vilka problemtyper finns det ingen rapport alls om?

Svar:

SELECT Beskrivning
FROM Problemtyper
WHERE Nummer NOT IN (SELECT Problemtyp FROM Gäller);

Uppgift 6 (6 p)

Det visar sig att sökningen i deluppgift 5c, om vilka typer av problem som rapporterades en viss dag, körs ofta, men kanske med andra datum än just 2023-01-10. Alla tabeller i databasen växer och blir mycket stora, och till följd av de stora datamängderna går sökningen för långsamt. Vi måste få den att gå snabbare.

a) Det visar sig att det inte finns några index alls i databasen. Vilka index bör vi skapa för att sökningen ska gå snabbare?

Svar:

Gäller.Rapport
Gäller.Problemtyp
Problemtyper.Nummer
Rapporter.Nummer
Rapporter.Datum

b) Ge exempel på något index som inte skulle få sökningen att gå snabbare, och förklara varför det indexet inte skulle hjälpa.

Svar:

Problemtyper.Beskrivning - bara med i resultatet, används inte för sökningar
Rapporter.Tid - inte alls med i frågan

c) I a-uppgiften ovan stod det att det inte fanns några index alls i databasen. Nästan alla databashanterare, till exempel Mimer och MySQL, skapar en del index automatiskt. Vilka, och varför?

Svar:

De skapar index på angivna primär- och alternativnycklar, dvs som deklarerats med PRIMARY KEY eller med UNIQUE. Det är dels för att i synnerhet primärnyckeln ofta används i sökningar, så man vill antagligen ha index på dem, och dessutom är de unika, dvs har god selektivitet. Det kanske allra viktigaste skälet är att databashanteraren måste upprätthålla nycklarnas unikhet, så varje gång man sätter in en rad, eller ändrar värdet på en nyckel på en rad, måste man annars läsa igenom hela tabellen för att se om det finns dubbletter. Databashanteraren måste också upprätthålla referensintegritet, och främmande nycklar refererar alltid till en primär- eller alternativnyckel, och för att kontrollera referensintegriteten behöver man slå upp värdet på nyckeln i den tabell som den främmande nyckeln refererar till.

Uppgift 7 (3 p)

En transaktion som startats med SQL-kommandot START TRANSACTION kan avslutas på olika sätt:

med kommandot COMMIT
med kommandot ROLLBACK
genom att det uppstår ett fel, till exempel deadlock eller ett strömavbrott

Vad händer, i vart och ett av dessa fall, med de ändringar som transaktionen gjort i databasen?

Svar:

Efter COMMIT sparas ändringarna permanent i databasen. Internt kan det ske genom att databashanteraren redan gjort ändringarna direkt i databasen, och helt enkelt låter bli att ändra tillbaka. Det kan också ske genom att de nya versionerna av data lagrats på en separat plats, och nu integreras med den vanliga databasen. Om det skulle uppstå ett fel under commit-processen så att committen inte kan genomföras, till exempel vid vissa typer av samtidighetshantering där konflikter kan upptäckas då, görs i stället en rollback.

Efter ROLLBACK tas alla ändringar som transaktionen gjort bort ur databasen. (Internt kan det ske genom att databashanteraren, som redan gjort ändringarna direkt i databasen, ändrar tillbaka allt som ändras. Det kan också ske genom att de nya versionerna av data, som lagrats på en separat plats, helt enkelt kastas bort i stället för att integreras med den vanliga databasen.)

Om transaktionen avbrutits av ett fel, tas alla ändringar som transaktionen gjort bort ur databasen, på samma sätt som vid ROLLBACK. (Om transaktionen avbröts av ett strömavbrott, eller att databashanteraren plötsligt stängdes av på något annat sätt, kan det ske som en del av återstartsprocessen, även kallad "recovery". Om ändringarna har gjorts direkt i databasen, måste databashanteraren läsa loggfilen för att veta vilka ändringar som gjorts, så de går att ändra tillbaka.)

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 29 januari 2023