Databasteknik: Lösningar till tentamen 2022-06-03

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)

Uppgift 2 (5 p)

Kunder(Nummer, Namn, Gatuadress, Postnummer, Ort, Telefon, Epostadress)
Fastigheter(Nummer, Namn, Gatuadress, Postnummer, Ort);
Klippområden(Nummer, Yta, Fastighet)
Gräsklippare(Nummer, Kört, Ägare, BefinnerSig)
SkaKlippa(Gräsklippare, Klippområde)just select from th

Kandidatnycklarna är understrukna. I tabeller med en enda kandidatnyckel är det förstås den som är primärnyckel, och i tabellen Fastigheter är Nummer primärnyckel.

Främmande nycklar:

Klippområden.Fastighet refererar till Fastigheter.Nummer
Gräsklippare.Ägare refererar till Kunder.Nummer
Gräsklippare.BefinnerSig refererar till Klippområden.Nummer
SkaKlippa.Gräsklippare refererar till Gräsklippare.Nummer
SkaKlippa.Klippområde refererar till Klippområden.Nummer

Kommentarer:

På riktigt vill man som ovan dela upp adressen i delar, men det är inget krav på tentan. Man kan anta att man hela tiden betraktar adressen som en helhet.
Man kan, om man vill, låta bli att skapa surrogatnyckeln Nummer i tabellen Fastigheter, och använda namnet som primärnyckel. SQL-frågorna blir lite enklare då.

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 Kunder
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn VARCHAR(20) NOT NULL,
Gatuadress VARCHAR(20) NOT NULL,
Postnummer CHAR(6) NOT NULL,
Ort VARCHAR(10) NOT NULL,
Telefon VARCHAR(10) NOT NULL,
Epostadress VARCHAR(20) NOT NULL);

CREATE TABLE Fastigheter
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn VARCHAR(20) NOT NULL UNIQUE,
Gatuadress VARCHAR(20) NOT NULL,
Postnummer CHAR(6) NOT NULL,
Ort VARCHAR(10) NOT NULL);

CREATE TABLE Klippområden
(Nummer INTEGER NOT NULL PRIMARY KEY,
Yta INTEGER NOT NULL,
Fastighet INTEGER NOT NULL REFERENCES Fastigheter(Nummer));

CREATE TABLE Gräsklippare
(Nummer INTEGER NOT NULL PRIMARY KEY,
Kört INTEGER NOT NULL,
Ägare INTEGER NOT NULL REFERENCES Kunder(Nummer),
BefinnerSig INTEGER NULL REFERENCES Klippområden(Nummer));

CREATE TABLE SkaKlippa
(Gräsklippare INTEGER NOT NULL REFERENCES Gräsklippare(Nummer),
Klippområde INTEGER NOT NULL REFERENCES Klippområden(Nummer),
PRIMARY KEY(Gräsklippare, Klippområde));

INSERT INTO Kunder VALUES (1, 'Anna', 'Vägen 1', '70221', 'Örebro', '1234', 'anna@hotmail.com');
INSERT INTO Kunder VALUES (2, 'Bo', 'Vägen 2', '70221', 'Örebro', '5678', 'bo@hotmail.com');
INSERT INTO Kunder VALUES (3, 'Cecilia', 'Vägen 3', '70221', 'Örebro', '9012', 'ceci@hotmail.com');

INSERT INTO Fastigheter VALUES (1, 'Burken 17:21', 'Vägen 1', '70221', 'Örebro');
INSERT INTO Fastigheter VALUES (2, 'Burken 17:22', 'Vägen 2', '70221', 'Örebro');
INSERT INTO Fastigheter VALUES (3, 'Burken 17:23', 'Vägen 3', '70221', 'Örebro');

INSERT INTO Klippområden VALUES (1, 70000, 1);
INSERT INTO Klippområden VALUES (2, 100, 2);
INSERT INTO Klippområden VALUES (3, 10000, 2);
INSERT INTO Klippområden VALUES (4, 200, 2);

INSERT INTO Gräsklippare VALUES (1, 1000, 1, 1);
INSERT INTO Gräsklippare VALUES (2, 1000, 1, NULL);
INSERT INTO Gräsklippare VALUES (3, 2000000, 1, 1);
INSERT INTO Gräsklippare VALUES (4, 1000, 2, 2);
INSERT INTO Gräsklippare VALUES (5, 2000000, 2, 3);
INSERT INTO Gräsklippare VALUES (6, 1800000, 2, 3);
INSERT INTO Gräsklippare VALUES (7, 1800000, 2, 3);
INSERT INTO Gräsklippare VALUES (8, 1800000, 2, 3);
INSERT INTO Gräsklippare VALUES (4711, 1000, 2, NULL);

INSERT INTO SkaKlippa VALUES (1, 1);
INSERT INTO SkaKlippa VALUES (2, 1);
INSERT INTO SkaKlippa VALUES (3, 1);
INSERT INTO SkaKlippa VALUES (4, 2);
INSERT INTO SkaKlippa VALUES (4, 3);
INSERT INTO SkaKlippa VALUES (4, 4);
INSERT INTO SkaKlippa VALUES (5, 2);
INSERT INTO SkaKlippa VALUES (5, 3);
INSERT INTO SkaKlippa VALUES (5, 4);
INSERT INTO SkaKlippa VALUES (6, 2);
INSERT INTO SkaKlippa VALUES (6, 3);
INSERT INTO SkaKlippa VALUES (6, 4);
INSERT INTO SkaKlippa VALUES (7, 2);
INSERT INTO SkaKlippa VALUES (7, 4);

Exempeldata:

Kunder
Nummer	Namn	Gatuadress	Postnummer	Ort	Telefon	Epostadress
1	Anna	Vägen 1	70221	Örebro	1234	anna@hotmail.com
2	Bo	Vägen 2	70221	Örebro	5678	bo@hotmail.com
3	Cecilia	Vägen 3	70221	Örebro	9012	ceci@hotmail.com

Fastigheter
Nummer	Namn	Gatuadress	Postnummer	Ort
1	Burken 17:21	Vägen 1	70221	Örebro
2	Burken 17:22	Vägen 2	70221	Örebro
3	Burken 17:23	Vägen 3	70221	Örebro

Klippområden
Nummer	Yta	Fastighet
1	70000	1
2	100	2
3	10000	2
4	200	2

Gräsklippare
Nummer	Kört	Ägare	BefinnerSig
1	1000	1	1
2	1000	1	null
3	2000000	1	1
4	1000	2	2
5	2000000	2	3
6	1800000	2	3
7	1800000	2	3
8	1800000	2	3
4711	1000	2	null

SkaKlippa
Gräsklippare	Klippområde
1	1
2	1
3	1
4	2
4	3
4	4
5	2
5	3
5	4
6	2
6	3
6	4
7	2
7	4

Primärnyckeln i tabellen SkaKlippa är sammansatt av båda kolumnerna, men det gick inte att göra ett streck under båda kolumnnamnen i HTML.

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) Här står en gräsklippare med numret 4711. Vad är telefonnumret till ägaren?

SELECT Telefon
FROM Kunder
WHERE Nummer IN (SELECT Ägare
                 FROM Gräsklippare
                 WHERE Nummer = 4711);

En alternativ lösning:

SELECT Kunder.Telefon
FROM Kunder, Gräsklippare
WHERE Kunder.Nummer = Gräsklippare.Ägare
AND Gräsklippare.Nummer = 4711;

Ännu ett alternativ:

SELECT Kunder.Telefon
FROM Kunder JOIN Gräsklippare ON Kunder.Nummer = Gräsklippare.Ägare
WHERE Gräsklippare.Nummer = 4711;

b) (2p) Ett antal gräsklippare ska klippa klippområden på fastigheten Burken 17:22. Vad är numren på de av dessa gräsklippare som har gått mer än en miljon meter?

SELECT DISTINCT Gräsklippare.Nummer
FROM Gräsklippare, SkaKlippa, Klippområden, Fastigheter
WHERE Gräsklippare.Nummer = SkaKlippa.Gräsklippare
AND SkaKlippa.Klippområde = Klippområden.Nummer
AND Klippområden.Fastighet = Fastigheter.Nummer
AND Fastigheter.Namn = 'Burken 17:22'
AND Gräsklippare.Kört > 1000000;

c) (1p) Vad är den sammanlagda ytan av alla klippområden?

SELECT SUM(Yta) FROM Klippområden;

d) (3p) Vad heter den fastighet som har den största sammanlagda gräsytan (alltså den sammanlagda ytan av alla klippområdena på den fastigheten)?

CREATE VIEW YtaPerFastighet AS
SELECT Fastighet, SUM(Yta) AS Yta
FROM Klippområden
GROUP BY Fastighet;

-- SELECT * FROM YtaPerFastighet;

SELECT Fastigheter.Namn
FROM Fastigheter, YtaPerFastighet
WHERE Fastigheter.Nummer = YtaPerFastighet.Fastighet
AND YtaPerFastighet.Yta = (SELECT MAX(Yta) FROM YtaPerFastighet);

SELECT-frågan på slutet ovan kan som alternativ skrivas:

SELECT Namn
FROM Fastigheter
WHERE Nummer IN (SELECT Fastighet
                 FROM YtaPerFastighet
                 WHERE Yta = (SELECT MAX(Yta) FROM YtaPerFastighet));

En lösning med en CTE:

WITH YtaPerFastighet AS
(SELECT Fastighet, SUM(Yta) AS Yta
FROM Klippområden
GROUP BY Fastighet)
SELECT Fastigheter.Namn
FROM Fastigheter, YtaPerFastighet
WHERE Fastigheter.Nummer = YtaPerFastighet.Fastighet
AND YtaPerFastighet.Yta = (SELECT MAX(Yta) FROM YtaPerFastighet);

En annan lösning med en CTE:

WITH YtaPerFastighet AS
(SELECT Fastighet, SUM(Yta) AS Yta
FROM Klippområden
GROUP BY Fastighet)
SELECT Namn
FROM Fastigheter
WHERE Nummer IN (SELECT Fastighet
                 FROM YtaPerFastighet
                 WHERE Yta = (SELECT MAX(Yta) FROM YtaPerFastighet));

e) (2p) Ibland blir det fel i databasen, till exempel om det står i databasen att en gräsklippare just nu befinner sig på ett klippområde som det inte är angivet att den ska klippa. Finns det några sådana gräsklippare? Vi vill veta deras nummer. (Om du skulle gjort ett databasschema i uppgift 2 som gör att detta fel inte kan uppstå, förklara då varför felet inte kan uppstå!)

SELECT Gräsklippare.Nummer
FROM Gräsklippare LEFT JOIN SkaKlippa
ON Gräsklippare.Nummer = SkaKlippa.Gräsklippare
AND Gräsklippare.BefinnerSig = SkaKlippa.Klippområde
WHERE SkaKlippa.Gräsklippare IS NULL
AND Gräsklippare.BefinnerSig IS NOT NULL;

Om man tillåter sig att ändra lite i uppgiften, och söka efter vilka gräsklippare som "inte befinner sig på ett klippområde som det är angivet att den ska klippa", får man med även de gräsklippare som inte befinner sig på något klippområde alls:

SELECT Gräsklippare.Nummer
FROM Gräsklippare LEFT JOIN SkaKlippa
ON Gräsklippare.Nummer = SkaKlippa.Gräsklippare
AND Gräsklippare.BefinnerSig = SkaKlippa.Klippområde
WHERE SkaKlippa.Gräsklippare IS NULL;

Några alternativ:

SELECT Nummer
FROM Gräsklippare
WHERE Nummer NOT IN (SELECT Gräsklippare.Nummer
                     FROM Gräsklippare, SkaKlippa
                     WHERE Gräsklippare.Nummer = SkaKlippa.Gräsklippare
                     AND Skaklippa.Klippområde = Gräsklippare.BefinnerSig);

SELECT Nummer
FROM Gräsklippare
WHERE Nummer NOT IN (SELECT Skaklippa.Gräsklippare
                     FROM SkaKlippa
                     WHERE Skaklippa.Klippområde = Gräsklippare.BefinnerSig);

SELECT Nummer
FROM Gräsklippare
WHERE NOT EXISTS (SELECT *
                  FROM SkaKlippa
                  WHERE Skaklippa.Gräsklippare = Gräsklippare.Nummer AND Klippområde = Gräsklippare.BefinnerSig);

Ett exempel på ett felaktigt svar, som ger numren på alla gräsklippare som ska klippa minst ett annat klippområde utöver det där den befinner sig just nu:

SELECT DISTINCT Gräsklippare.Nummer
FROM Gräsklippare, SkaKlippa
WHERE Gräsklippare.Nummer = SkaKlippa.Gräsklippare
AND Gräsklippare.BefinnerSig != SkaKlippa.Klippområde;

Uppgift 4 (5 p)

Sökning a i uppgiften ovan, om telefonnumret till ägaren av gräsklippare 4711, körs ofta, men med olika gräsklipparnummer. Vi ser också att det inte finns några index i databasen, inte ens på nycklar.

a) Vilka index bör man skapa för att den angivna frågan ska gå snabbare att köra? Skriv create index-kommandon!

CREATE INDEX Gräsklipparnummer ON Gräsklippare(Nummer);
CREATE INDEX Gräsklipparägare ON Gräsklippare(Ägare);
CREATE INDEX Kundnummer ON Kunder(Nummer);

b) Ange ett index som inte skulle få den frågan att gå snabbare att köra, och förklara varför det indexet inte hjälper.

CREATE INDEX Kundtelefon ON Kunder(Telefon);
Kolumnen Kunder.Telefon är med i svaret på sökningen, men används inte för att hitta rader.

c) I vår databas fanns det inga index på nycklar, men de flesta moderna databashanterare skapar automatiskt ett index på de primärnycklar och andra kandidatnycklar som man angett. Varför gör de det?

Två viktiga skäl:

Upprätthållande av integritetsvillkor. Databashanteraren måste upprätthålla unikheten hos de deklarerade nycklarna, så det aldrig uppstår dubbletter. Därför måste den, varje gång man lägger till en rad och varje gång man ändrar värdet på nyckeln på en rad, söka igenom resten av tabellen för att se om det nya nyckelvärdet redan finns. Den måste också göra motsvarande kontroller för att upprätthålla referensintegritet, om det finns främmande nycklar som refererar till den här kandidatnyckeln. Detta skulle bli orimligt långsamt om det inte gick att göra via ett index.
Man söker ofta på just primärnycklar, och ibland kandidatnycklar, så därför vill man förmodligen ha index på dem.

Uppgift 5 (3 p)

Även sökning c, om den sammanlagda ytan av alla klippområden, körs ofta, och vi vill göra den snabbare. Hur skulle man kunna göra det?

Vi måste gå igenom och summera alla raderna, och därför har vi ingen nytta av att snabbt hitta enskilda rader, så index hjälper förmodligen inte så mycket. Man kan förstås använda snabbare hårdvara, eller en snabbare databashanterare, men en bra metod är en materialiserad vy. Det finns databashanterare som har inbyggda materialiserade vyer, men för det mesta får man göra det själv, med hjälp av en tabell och triggers.
Skapa en tabell som till exempel heter Ytsumman, med en enda rad och en enda kolumn. Lägg in den nuvarande ytsumman i tabellen. För att hålla den aktuell skapar vi tre triggers (ON INSERT, ON DELETE och ON UPDATE), som uppdaterar den lagrade ytsumman när innehållet i tabellen Klippområden ändras.

Uppgift 6 (3 p)

Relationsdatabashanterare brukar ha inbyggda mekanismer för att upprätthålla de så kallade ACID-egenskaperna. Välj en av dessa egenskaper, förklara kort vad den innebär, och visa med ett exempel från gräsklippardatabasen vad som skulle kunna hända om den inte upprätthölls.

Vi kan ta C:et, som står för consistency preserving, på svenska konsistensbevarande, dvs att om databasen var i ett konsistent tillstånd (utan motsägelser, eller utan brott mot integritetsvillkoren) före transaktionen, så ska den också vara i ett konsistent tillstånd efter transaktionen.
Annars kan databasen (förstås!) bli inkonsistent, till exempel genom att man får två gräsklippare med samma nummer trots att numren ska vara unika, eller att det står att en gräsklippare ägs av en kund som inte finns.

Uppgift 7 (3 p)

En av de första teknikerna för att skapa webbplatser som visar data ur en databas var så kallade CGI-skript. Hur fungerar ett CGI-skript, och varför brukar man inte längre använda dem?

Ett CGI-skript (även kallat CGI-script eller CGI-program) är ett separat program startas av webbservern, och det programmets utmatning blir HTML-koden för en webbsida. Det startade programmet kopplar själv upp sig mot databasen, som vilket program som helst som ska komma åt databasen.
CGI-program kan vara långsamma, därför att om en webbsida ska genereras av ett CGI-program, måste programmet startas och köras varje gång någon vill titta på den webbsidan. Att starta ett program tar på de flesta system mycket lång tid, jämfört med att göra saker i programmet när det väl har startats.
På en databasbaserade webbplats kan CGI-program vara extra långsamma, eftersom CGI-programmet inte bara måste startas, utan det måste dessutom koppla upp sig mot databashanteraren och logga in. Detta kan ta lång tid, jämfört med att bara köra en SQL-fråga.
Ännu ett skäl att undvika CGI-program är att eftersom de är ett separat program från webbservern, har man i CGI-programmet inte tillgång till webbserverns olika mekanismer och stödfunktioner, till exempel för inloggning och åtkomstkontroll.

Uppgift 8 (4 p)

a) Jag har glömt vad SQL-kommandona för att ge användarna rättigheter till tabeller, och sedan ta bort dem, heter. Det är inte create access right och drop access right, utan vad heter kommandona egentligen?

Grant och revoke.

b) Om jag vill ge användaren Anna rätt att ändra rader i tabellen Kameler, men inte lägga till eller ta bort rader, hur skriver jag det kommandot?

GRANT UPDATE ON Kameler TO Anna;

c) Om jag vill ge användaren Bengt rätt se alla data i tabellen Kameler, men bara de kameler som har en vikt större än 1000 kilo, hur ska jag göra då? (Det finns en kolumn Vikt i den tabellen.)

Man kan skapa en vy, och ge åtkomsträttigheter till den vyn:
CREATE VIEW TungaKameler AS
SELECT *
FROM Kameler
WHERE Vikt > 1000;

GRANT SELECT ON TungaKameler TO Bengt;

Uppgift 9 (2 p)

Visa med ett exempel vad SQL-injektion är, och hur det fungerar!

SQL-injektion (på engelska SQL injection) innebär att en angripare kan komma åt och förändra en databas genom att skriva in särskilt utformade texter, till exempel i ett textfält på en webbsida.
SQL-injektion fungerar när användarens inmatning stoppas in en textsträng, som därefter tolkas som ett SQL-kommando. Antag att användaren matar in sitt namn, som Bengt, och därefter bygger applikationen det ihop till den här SQL-frågan:
select * from Hemligheter where Namn = 'Bengt';
Om en fiende skriver in det ganska konstiga namnet Bengt' or 'x'='x kommer SQL-frågan att bli:
select * from Hemligheter where Namn = 'Bengt' or 'x'='x';
Eftersom where-villkoret alltid blir sant ger detta åtkomst till alla hemligheterna, inte bara Bengts.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 23 juni 2022