Databasteknik: Lösningar till tentamen 2018-01-09

Uppgift 1 (5 p)

Här har vi antagit att ett paket bara kan komma till varje plats en enda gång. Annars bör man göra "Har varit" som en entitetstyp.

Vi har en dubbellagring i den här lösningen! För att hitta var ett paket är just nu kan man antingen använda sambandet Är nu, eller titta i Har varit efter den senaste plats ett paket har varit på och ännu inte lämnat.

(Men det beror på hur man lägger in i data tabellerna, och det är inte säkert att det verkligen är en dubbellagring.)

Det skulle kunna vara bättre att ta bort Är nu, och i stället göra en vy som visar varje pakets aktuella plats.

Uppgift 2 (6 p)

Primärnycklarna är understrukna. I tabellen Plats är även Namn en kandidatnyckel. I tabellen HarVarit kan man tänka sig att även kombinationen av Paket och Ankomsttid är en kandidatnyckel, och att kombinationen av Paket och Avgångstid är en kandidatnyckel.

Främmande nycklar:

Postombud.Nummer till Plats.Nummer
Paket.Från till Postombud.Nummer
Paket.Till till Postombud.Nummer
Paket.AktuellPlats till Plats.Nummer
HarVarit.Paket till Paket.Nummer
HarVarit.Plats till Plats.Nummer

Nedan visas create table-kommandon och exempeldata, för att underlätta provkörningar. Vi har förenklat lite så att mottagar- och avsändaradresserna bara innehåller namn, inte gatu- och postadress.

DROP TABLE HarVarit;
DROP TABLE Paket;
DROP TABLE Postombud;
DROP TABLE Plats;

CREATE TABLE Plats
(Nummer INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(50) NOT NULL UNIQUE);

CREATE TABLE Postombud
(Nummer INTEGER NOT NULL PRIMARY KEY REFERENCES Avdelningar(Nummer));

CREATE TABLE Paket
(Nummer INTEGER NOT NULL PRIMARY KEY,
Vikt FLOAT NOT NULL,
Avsändare NVARCHAR(50) NOT NULL,
Mottagare NVARCHAR(50) NOT NULL,
FrånOmbud INTEGER NOT NULL REFERENCES Postombud(Nummer),
TillOmbud INTEGER NOT NULL REFERENCES Postombud(Nummer),
AktuellPlats INTEGER NOT NULL REFERENCES Plats(Nummer));

CREATE TABLE HarVarit
(Paket INTEGER NOT NULL REFERENCES Paket(Nummer),
Plats INTEGER NOT NULL REFERENCES Plats(Nummer),
PRIMARY KEY (Paket, Plats),
Ankomsttid TIMESTAMP NOT NULL,
Avgångstid TIMESTAMP);

INSERT INTO Plats (Nummer, Namn) VALUES (1, 'Tobbes Tobak');
INSERT INTO Plats (Nummer, Namn) VALUES (2, 'Sigges Cigarrer');
INSERT INTO Plats (Nummer, Namn) VALUES (3, 'Frendo');
INSERT INTO Plats (Nummer, Namn) VALUES (4, 'Svenska Centrala Paketsorteringsenheten');

INSERT INTO Postombud (Nummer) VALUES (1);
INSERT INTO Postombud (Nummer) VALUES (2);
INSERT INTO Postombud (Nummer) VALUES (3);

INSERT INTO Paket (Nummer, Vikt, Avsändare, Mottagare, FrånOmbud, TillOmbud, AktuellPlats)
    VALUES (1, 1.1, 'Anders Andersson', 'Bodil Bodilsson', 1, 2, 4);
INSERT INTO Paket (Nummer, Vikt, Avsändare, Mottagare, FrånOmbud, TillOmbud, AktuellPlats)
    VALUES (2, 2.2, 'Anders Andersson', 'Thomas Padron-McCarthy', 1, 3, 4);
INSERT INTO Paket (Nummer, Vikt, Avsändare, Mottagare, FrånOmbud, TillOmbud, AktuellPlats)
    VALUES (3, 3.3, 'Bodil Bodilsson', 'Thomas Padron-McCarthy', 2, 3, 3);
INSERT INTO Paket (Nummer, Vikt, Avsändare, Mottagare, FrånOmbud, TillOmbud, AktuellPlats)
    VALUES (4, 4.4, 'Bodil Bodilsson', 'Ali Abdul Khaliq', 2, 3, 4);

INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (1, 1, LOCALTIMESTAMP(), LOCALTIMESTAMP());
INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (1, 4, LOCALTIMESTAMP(), NULL);

INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (2, 1, LOCALTIMESTAMP(), LOCALTIMESTAMP());
INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (2, 4, LOCALTIMESTAMP(), NULL);

INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (3, 2, LOCALTIMESTAMP(), LOCALTIMESTAMP());
INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (3, 4, LOCALTIMESTAMP(), LOCALTIMESTAMP());
INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (3, 1, LOCALTIMESTAMP(), LOCALTIMESTAMP());
INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (3, 3, LOCALTIMESTAMP(), NULL);

INSERT INTO HarVarit (Paket, Plats, Ankomsttid, Avgångstid)
    VALUES (4, 2, LOCALTIMESTAMP(), NULL);

SELECT * FROM Plats;
SELECT * FROM Postombud;
SELECT * FROM Paket;
SELECT * FROM HarVarit;

Uppgift 3 (10 p)

SELECT Plats.Namn
FROM Paket, Plats
WHERE Paket.AktuellPlats = Plats.Nummer
AND Paket.Mottagare = 'Thomas Padron-McCarthy';

Två andra alternativ:

SELECT Plats.Namn
FROM Paket JOIN Plats ON Paket.AktuellPlats = Plats.Nummer
WHERE Paket.Mottagare = 'Thomas Padron-McCarthy';

SELECT Namn
FROM Plats
WHERE Nummer IN (SELECT AktuellPlats
                 FROM Paket
                 WHERE Mottagare = 'Thomas Padron-McCarthy');

b) (2p) Vad är paketnumren på de paket som redan kommit fram till sitt destinationspostombud?

SELECT Nummer
FROM Paket
WHERE AktuellPlats = TillOmbud;

c) (2p) Vad är paketnumren på de paket som inte kommit fram till sitt destinationspostombud?

SELECT Nummer
FROM Paket
WHERE AktuellPlats <> TillOmbud;

d) (2p) Alla paket skickas via Svenska Centrala Paketsorteringsenheten, som av arbetsmarknadsskäl placerats i Övre Soppero. Hur många paket befinner sig just nu på den platsen?

SELECT COUNT(*)
FROM Paket
WHERE AktuellPlats IN (SELECT Nummer
                       FROM Plats
                       WHERE Namn = 'Svenska Centrala Paketsorteringsenheten');

e) (2p) Vad väger det paket som varit på flest platser?

CREATE VIEW AntalBesöktaPlatser AS
SELECT Paket, COUNT(*) AS Antal
FROM HarVarit
GROUP BY Paket;

SELECT Vikt
FROM Paket
WHERE Nummer IN (SELECT Paket
                 FROM AntalBesöktaPlatser
                 WHERE Antal IN (SELECT MAX(Antal)
                                 FROM AntalBesöktaPlatser));

WITH AntalBesöktaPlatser AS
(SELECT Paket, COUNT(*) AS Antal
FROM HarVarit
GROUP BY Paket)
SELECT Vikt
FROM Paket
WHERE Nummer IN (SELECT Paket
                 FROM AntalBesöktaPlatser
                 WHERE Antal IN (SELECT MAX(Antal)
                                 FROM AntalBesöktaPlatser));

Uppgift 4 (4 p)

a) Ange vilka index man bör skapa för att den frågan ska gå snabbare att köra.

• Paket.Mottagare
• Paket.AktuellPlats (men ganska säkert inte)
• Plats.Nummer

b) Ange ett index som inte skulle göra den frågan snabbare, och förklara varför det inte förbättrar tiden.

Två exempel:

• Plats.Namn är med i svaret från frågan, men används inte för att hitta rader.
• Paket.Vikt är överhuvudtaget inte med i frågan.

Uppgift 5 (5 p)

a) COMMIT och ROLLBACK

COMMIT avslutar en transaktion och gör (om den lyckas) att transaktionens ändringar sparas permanent i databasen. ROLLBACK avslutar en transaktion och gör att transaktionens ändringar tas bort ur databasen.

b) WHERE och HAVING

WHERE används i en SQL-fråga för att filtrera fram vilka rader man vill arbeta med. Det görs före eventuella aggregatfunktioner som SUM och COUNT. HAVING används i en SQL-fråga för att filtrera bort resultatrader, efter att aggregatfunktionernas värden beräknats.

c) UPDATE och ALTER TABLE

UPDATE ändrar på data i tabellen genom att ändra värden i en eller flera kolumner på en eller flera rader, medan ALTER TABLE ändrar schemat, till exempel genom att lägga till eller ta bort kolumner.

d) ett index och en primärnyckel

En primärnyckel är en kolumn eller en kombination av kolumner i en tabell som är garanterad att ha unika värden, dvs inga dubbletter får förekomma. Ett index är en datastruktur som databashanteraren använder för att kunna söka snabbare.

e) en tabell och en relation

Det är samma sak! Termen "relation" används huvudsakligen i teorin om relationsdatabaser, medan man använder termen "tabell" vid praktisk användning, som i SQL.

Ibland kallas också kopplingar mellan tabeller med referensattribut (främmande nycklar) för relationer, men det är olämpligt, för i en relationsdatabas är det själva tabellerna som är relationerna. (I en del Microsoft-produkter har någon, som inte riktigt kunde relationsdatabaser, översatt engelskans "relationships" till det svenska "relationer".) Även sambanden i ER-diagram kallas ibland för relationer, men det är också olämpligt, just för att man riskerar att blanda ihop det med tabeller.

Uppgift 6 (5 p)

Paket nummer 1 har varit på plats 1, 3 och 5, och ligger fortfarande kvar på plats 5. Paket nummer 2 har bara varit på plats 5, och ligger fortfarande kvar där.

a) Vilka kandidatnycklar finns i tabellen?

Om vi antar att varje paket bara kan komma till en och samma plats en enda gång:

Kombinationen av Paketnummer och Platsnummer är en kandidatnyckel. Troligen är också kombinationen av Paketnummer och Ankom en kandidatnyckel, liksom kombinationen av Paketnummer och Lämnade.

b) Vilka fullständiga funktionella beroenden finns i tabellen?

Om vi antar kandidatnycklarna enligt ovan:

• Från Paketnummer till Vikt
• Från kombinationen Paketnummer + Platsnummer till Ankom
• Från kombinationen Paketnummer + Platsnummer till Lämnade
• Från kombinationen Paketnummer + Ankom till Platsnummer
• Från kombinationen Paketnummer + Ankom till Lämnade
• Från kombinationen Paketnummer + Lämnade till Platsnummer
• Från kombinationen Paketnummer + Lämnade till Ankom

c) Vilken är den högsta normalform, av 1NF, 2NF, 3NF och BCNF, eller ingen av dessa, som tabellen uppfyller?

Första normalformen.

d) Varför uppfyller tabellen inte den närmast högre normalformen?

I beroendet från Paketnummer till Vikt är determinanten Paketnummer en del av en kandidatnyckel, och andra normalformen kräver att varje icke-nyckel-attribut ska vara fullständigt funktionellt beroende av hela kandidatnyckeln.

e) Beskriv något problem som finns i den här tabellen, och som man skulle slippa med en högre normalform.

Redundans: Vikten på varje paket lagras en gång för varje plats som paketet varit på.