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

Uppgift 1 (5 p)

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:

Kommentarer: I stället för sambandstypen Ansluter kan man ha en lösning med en entitetstyp Vägkorsning som har ett många-till-många-samband med gatorna. Inget poängavdrag om man gjort Hus som en vanlig entitetstyp i stället för en svag, och inte angett Husnummer som en partiell nyckel.

Uppgift 2 (6 p)

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:

Gator(Nummer, Namn, Längd)
Hus(Nummer, Husnummer, Gata)
Ansluter(Gata1, Gata2)

Kandidatnycklarna är understrukna. I tabellen Hus är Nummer primärnyckel och kombinationen av Husnummer och Gata en alternativnyckel.

Främmande nycklar:

Hus.Gata till Gator.Nummer
Ansluter.Gata1 till Gator.Nummer
Ansluter.Gata2 till Gator.Nummer

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

Tabellerna med exempeldata:

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

DROP TABLE Ansluter CASCADE;
DROP TABLE Hus CASCADE;
DROP TABLE Gator CASCADE;

CREATE TABLE Gator
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(20) NOT NULL,
Längd INTEGER NOT NULL CHECK (Längd > 0));

INSERT INTO Gator VALUES (1, 'Granvägen', 100);
INSERT INTO Gator VALUES (2, 'Brickebergsvägen', 200);
INSERT INTO Gator VALUES (3, 'Alstavägen', 300);
INSERT INTO Gator VALUES (4, 'Barkvägen', 400);
INSERT INTO Gator VALUES (5, 'Barrstigen', 500);
INSERT INTO Gator VALUES (6, 'Backavägen', 600);
INSERT INTO Gator VALUES (7, 'Åstadalsvägen', 700);
INSERT INTO Gator VALUES (8, 'Sörbyängsvägen', 800);
INSERT INTO Gator VALUES (9, 'Gällerstavägen', 900);
INSERT INTO Gator VALUES (10, 'Granvägen', 1000);
INSERT INTO Gator VALUES (11, 'Granvägen', 1100);

CREATE TABLE Hus
(Nummer INTEGER NOT NULL PRIMARY KEY,
Husnummer VARCHAR(10) NOT NULL,
Gata INTEGER NOT NULL REFERENCES Gator(Nummer),
UNIQUE (Husnummer, Gata));

INSERT INTO Hus VALUES (1, '6A', 1);
INSERT INTO Hus VALUES (2, '6B', 1);
INSERT INTO Hus VALUES (3, '6A', 2);
INSERT INTO Hus VALUES (4, '6B', 2);
INSERT INTO Hus VALUES (5, '6A', 3);

-- Ska inte fungera!
INSERT INTO Hus VALUES (1, 'XX', 1);
INSERT INTO Hus VALUES (100, '6A', 1);
INSERT INTO Hus VALUES (100, '6A', 100);

CREATE TABLE Ansluter
(Gata1 INTEGER NOT NULL REFERENCES Gator(Nummer),
Gata2 INTEGER NOT NULL REFERENCES Gator(Nummer),
PRIMARY KEY (Gata1, Gata2),
CHECK (Gata1 < Gata2)); -- Eller inte? Beror på hur man vill göra!

INSERT INTO Ansluter VALUES (1, 3);
INSERT INTO Ansluter VALUES (1, 4);
INSERT INTO Ansluter VALUES (1, 5);
INSERT INTO Ansluter VALUES (1, 6);
INSERT INTO Ansluter VALUES (2, 3);
INSERT INTO Ansluter VALUES (4, 9);

-- Ska inte fungera!
INSERT INTO Ansluter VALUES (3, 1);

SELECT * FROM Gator;
SELECT * FROM Hus;
SELECT * FROM Ansluter;

Uppgift 3 (1 p)

Svar:

Det är tabellerna. Det är inte kopplingarna mellan tabellerna i form av referensattribut eller främmande nycklar, och det är inte heller sambandstyperna i ett ER-diagram.

Uppgift 4 (3 p)

Vad är det för problem som detta kan leda till, och hur har du löst det i din databas?

Svar:

Om man i databasen lagrar både att den första vägen sitter ihop med den andra, och att den andra sitter ihop med den första, har man en dubbellagring (även kallad redundans). Om man ändrar i databasen och lägger till, tar bort eller ändrar en korsning, måste man komma ihåg att ändra på båda ställena, annars får man en inkonsistent databas, vilket i sin tur kan leda till felaktiga svar på sökningar. Man kan använda triggers för att upprätthålla den konsistensen. Om man räknar korsningar riskerar man att få dubbelt så många som finns, så man måste anpassa sina SQL-frågor efter detta.

Om man i stället bara lagrar den ena kopplingen, att den första vägen sitter ihop med den andra, så måste man även här tänka på det när man formulerar SQL-frågorna. Om jag vill veta om Alstavägen sitter ihop med Granvägen, räcker det inte att söka efter vilka vägar som Alstavägen sitter ihop med, utan man måste också söka efter vilka vägar som Granvägen sitter ihop med. Man måste också undvika att av misstag lägga in korsningen åt båda hållen, och man kan till exempel ha ett CHECK-villkor som jag visat i uppgift 2 ovan.

Om man i ER-diagrammet har objektifierat vägkorsningarna, och tolkar en "vägkorsning" som flera gator som sitter ihop och inte bara två, vilket när man översätter till tabeller leder till en tabell med vägkorsningar och en annan tabell som kopplar ihop gatorna med vägkorsningarna, slipper man dessa problem, till priset av ett något mer komplicerat databasschema.

Uppgift 5 (11 p)

a) (1p) Vad är numren på de gator som heter Granvägen?

Svar:

SELECT Nummer FROM Gator WHERE Namn = 'Granvägen';

b) (1p) Det finns bara en gata som heter Åstadalsvägen och bara en som heter Sörbyängsvägen. Vad är numren och längden på Åstadalsvägen och Sörbyängsvägen? (Som en SQL-fråga, inte två!)

Svar:

SELECT Nummer, Längd
FROM Gator
WHERE Namn = 'Åstadalsvägen' OR Namn = 'Sörbyängsvägen';

SELECT Nummer, Längd
FROM Gator
WHERE Namn IN ('Åstadalsvägen', 'Sörbyängsvägen');

c) (2p) Vad heter de gator som är längre än både Åstadalsvägen och Sörbyängsvägen?

Svar:

SELECT Namn
FROM Gator
WHERE Längd > (SELECT Längd FROM Gator WHERE Namn = 'Åstadalsvägen')
AND Längd > (SELECT Längd FROM Gator WHERE Namn = 'Sörbyängsvägen');

SELECT Namn
FROM Gator
WHERE Längd > (SELECT MAX(Längd)
               FROM Gator
               WHERE Namn IN ('Åstadalsvägen', 'Sörbyängsvägen'));

SELECT Namn
FROM Gator
WHERE Längd > ALL (SELECT Längd
                   FROM Gator
                   WHERE Namn IN ('Åstadalsvägen', 'Sörbyängsvägen'));

SELECT Längre.Namn
FROM Gator AS Längre, Gator AS Åstadalsvägen, Gator AS Sörbyängsvägen
WHERE Längre.Längd > Åstadalsvägen.Längd
AND Längre.Längd > Sörbyängsvägen.Längd
AND Åstadalsvägen.Namn = 'Åstadalsvägen'
AND Sörbyängsvägen.Namn = 'Sörbyängsvägen';

d) (2p) Vad heter de gator som Gällerstavägen sitter ihop med?

Svar:

Om vi använder en databas där varje korsning mellan två gator bara lagras en gång i tabellen Ansluter:

SELECT Namn
FROM Gator
WHERE Nummer IN (SELECT Gata1
                 FROM Ansluter
                 WHERE Gata2 IN (SELECT Nummer
                                 FROM Gator
                                 WHERE Namn = 'Gällerstavägen'))
OR Nummer IN (SELECT Gata2
                 FROM Ansluter
                 WHERE Gata1 IN (SELECT Nummer
                                 FROM Gator
                                 WHERE Namn = 'Gällerstavägen'));

e) (3p) Vad heter den gata som det ligger flest hus vid?

Svar:

En lösning med en CTE:

WITH AntalHusPerGata AS
(SELECT Gata, COUNT(*) AS Antal
FROM Hus
GROUP BY Gata)
SELECT Namn
FROM Gator
WHERE Nummer IN (SELECT Gata
                 FROM AntalHusPerGata
                 WHERE Antal IN (SELECT MAX(Antal)
                                 FROM AntalHusPerGata));

CREATE VIEW AntalHusPerGata AS
SELECT Gata, COUNT(*) AS Antal
FROM Hus
GROUP BY Gata;

SELECT Namn
FROM Gator
WHERE Nummer IN (SELECT Gata
                 FROM AntalHusPerGata
                 WHERE Antal IN (SELECT MAX(Antal)
                                 FROM AntalHusPerGata));

f) (2p) Finns det några gator som det inte finns några hus alls vid? Vi vill veta dessa gators namn.

Svar:

SELECT Namn
FROM Gator
WHERE Nummer NOT IN (SELECT Gata FROM Hus);

Uppgift 6 (2 p)

Svar:

Gator.Namn
Gator.Längd

Uppgift 7 (3 p)

Svar:

En "aktiv regel", dvs en regel som "löser ut" ("triggar") och utför en åtgärd ("action") när ett villkor ("condition") är uppfyllt. Jämför med en if-sats i ett programspråk, som bara kan lösa ut när programmet kör if-satsen, och som därför skulle kunna kallas för en "passiv regel". Den aktiva regeln löser ut när villkoret är uppfyllt, utan att något program behöver köra den.

b) Beskriv två olika användningsområden för triggers i databaser.

Svar:

En användning är för att upprätthålla konsistensen mellan en materialiserad vy och de tabeller den baseras på. Exempelvis kanske man vill materialisera vyn AntalHusPerGata från uppgift 5 ovan, och gör den som en tabell i stället för en vy. Om det byggs nya hus vid en gata (insert i tabellen Hus), försvinner hus (delete) eller om hus flyttas till en annan gata (update), måste antalet hus i tabellen AntalHusPerGata uppdateras så det stämmer överens med hur många hus som faktiskt finns på varje gata. Med hjälp av triggers kan dessa uppdateringar ske automatiskt.

En annan användning är upprätthålla dynamiska integritetsvillkor, som inte beskriver vilka data man får lagra i databasen, utan vilka ändringar man får göra. Ett exempel på ett dynamiskt integritetsvillkor kan vara att löner inte får sänkas. Sådana villkor är svåra eller omöjliga att ange med hjälp av databashanterarens vanliga mekanismer för integritetsvillkor, som referensintegritet eller CHECK-villkor.

Uppgift 8 (5 p)

a) Vilka ffb finns i tabellen?

Svar:

Det skulle kunna finnas ett fullständigt funktionellt beroende från Vikt till Färg, om man varje morgon väger alla katterna, delar in dem i grupper efter vikt, och målar om dem så att alla katter med samma vikt får samma färg.

Men om man gör rimliga antaganden finns det sex olika fullständiga funktionella beroenden: från kandidatnyckeln Nummer till var och en av de andra attributen (Namn, Vikt och Färg), och från kandidatnyckeln Namn till var och en av de andra attributen (Nummer, Vikt och Färg).

b) Vilka av normalformerna 1NF, 2NF, 3NF och BCNF uppfyller tabellen? Om det är någon normalform som inte uppfylls, så förklara varför den inte uppfylls.

Svar:

Alla dessa, i alla fall om man inte väljer att betrakta namnen och kanske färgerna som uppdelade i enskilda bokstäver och betrakta dem som sammansatta.

c) Man brukar vilja undvika redundans i databaser. Vi ser att just nu väger nästan alla katterna 2 kilo. Är det en form av redundans?

Svar:

Ungefär som på frågan ovan om fullständiga funktionella beroenden skulle det kunna finnas en form av redundans, till exempel om alla katter i hela världen utom några få väger två kilo, och bara någon enstaka väger något annat. I så fall skulle man kunna lagra undantagen i en separat tabell.

Men om man gör rimliga antaganden så är det inte någon redundans. Att Pelle väger två kilo, Maja väger två kilo, Måns väger två kilo och Bill väger två kilo är olika information, fyra olika uppgifter där ingen av dem kan räknas ut utifrån de andra katternas vikter. Det råkar bara vara så att många av katterna som finns i tabellen just nu väger just två kilo. Att samma värde, som heltalet 2, upprepas många gånger behöver inte vara redundant!

d) Vi ser också att alla katterna är grå. Är det en form av redundans?

Svar:

Även här skulle det kunna finnas en form av redundans, till exempel om alla katter alltid är grå, eller om det här är en tabell just för gråa katter, och då finns det ingen anledning att lagra färgen för varje katt.

Om färgerna kan ha långa namn (som "blåsilversköldpaddstigré med vitt") kan man se upprepningen av de långa färgnamnen som en form av redundans, och man kan spara plats i databasen genom att ha färgnamnen i en separat tabell. Men upprepning av långa namn är inte vad man brukar mena med redundans i en databas, utan man menar upprepning av samma information, till exempel vilken färg en och samma katt har.

Men om man gör rimliga antaganden så är det inte någon redundans. Det råkar bara vara så att alla katter som finns i tabellen just nu är grå.

Uppgift 9 (4 p)

a) Vi använder en databashanterare som uppfyller ACID-egenskaperna. Vad blir resultatet av de tre SELECT-frågorna ovan?

Svar:

Den första och den andra frågan ger båda svaret 2. Den tredje frågan ger svaret 4.

b) Vad står bokstaven "I" i ACID för, och vad innebär det?

Svar:

"Isolering", som betyder att transaktionerna i databasen är isolerade från varandra i betydelsen varje transaktion "upplever" det som att den är ensam i databasen. Medan transaktionen körs ska den alltså aldrig se att innehållet i databasen ändras, om den inte själv gjort ändringen.

c) Nu byter vi databashanterare till en som inte uppfyller ACID-egenskaperna, särskilt inte I:et. Beskriv något problem som kan uppstå när vi kör transaktionerna ovan!

Svar:

Slutresultatet skulle kunna bli oväntat, till exempel 2 eller 5:

• Transaktionen i klient 1 gör rollback, vilket ska göra att inga ändringar den gjort ska finnas kvar i databasen. Det ska vara som om den transaktionen aldrig har funnits. Men om ändringarna görs direkt i databasen, och är synliga för de andra transaktionerna, kommer klient 2 att läsa värdet 3 på vikten (en så kallad "dirty read"), och lägga till 2 så den blir 5, och lagra det i databasen.
• Om transaktionen i klient 1, i samband med sin rollback, ändrar tillbaka till ursprungsvärdet 2, försvinner klient 2:s ökning av Pelles vikt (en variant av "lost update").