Databasteknik: Lösningar till tentamen 2017-05-29

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 bakgrundsorl från en restaurang eller liknande. Här nedan kommer lösningsförslagen till uppgifterna.

Uppgift 1 (5 p)

Ett ER-diagram

Uppgift 2 (6 p)

Flygplanstyper(ID, Tillverkare, Modell)
Flygbolag(ID, Namn, Land)
Flygplatser(ID, Namn, Kod, Land)
Flygplan(ID, Beteckning, Typ, Ägare, Flygplats)
Varit(ID, Flygplan, Flygplats)

Primär- och kandidatnycklarna är understrukna. I alla tabellerna är ID primärnyckel, och utgörs av en databasintern nyckel för att underlätta arbetet. Vi kan också klara oss utan den.

Främmande nycklar:

Flygplan.Typ till Flygplanstyper.ID
Flygplan.Ägare till Flygbolag.ID
Flygplan.Flygplats till Flygplatser.ID
Varit.Flygplan till Flygplan.ID
Varit.Flygplats till Flygplats.ID

Tabeller med exempeldata (sammansatta kandidatnycklar visas inte korrekt, och ser ut som om de ingående kolumnerna var för sig är nycklar):

Flygplanstyper
ID Tillverkare Modell
1 Airbus A380
2 Airbus A400M
3 Boeing 747

Flygbolag
ID Namn Land
1 SAS Sverige
2 United USA
3 Air France Frankrike

Flygplatser
ID Namn Kod Land
1 Arlanda ARN Sverige
2 Bromma BMA Sverige
3 Heathrow LHR UK

Flygplan
ID Beteckning Typ Ägare Flygplats
1 SE-FGA 1 1 1
2 SE-FGB 1 2 1
3 SE-FGC 3 2 1
4 SE-FGD 3 2 null

Varit
ID Flygplan Flygplats
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2

Här finns create table-kommandon och exempeldata, för att underlätta provkörningar:

DROP TABLE Varit;
DROP TABLE Flygplan;
DROP TABLE Flygplatser;
DROP TABLE Flygbolag;
DROP TABLE Flygplanstyper;

CREATE TABLE Flygplanstyper
(ID INTEGER NOT NULL PRIMARY KEY,
Tillverkare NVARCHAR(10) NOT NULL,
Modell NVARCHAR(10) NOT NULL,
UNIQUE (Tillverkare, Modell));

CREATE TABLE Flygbolag
(ID INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL UNIQUE,
Land NVARCHAR(10) NOT NULL);

CREATE TABLE Flygplatser
(ID INTEGER NOT NULL PRIMARY KEY,
Namn NVARCHAR(10) NOT NULL UNIQUE,
Kod NVARCHAR(10) NOT NULL UNIQUE,
Land NVARCHAR(10) NOT NULL);

CREATE TABLE Flygplan
(ID INTEGER NOT NULL PRIMARY KEY,
Beteckning NVARCHAR(10) NOT NULL UNIQUE,
Typ INTEGER NOT NULL REFERENCES Flygplanstyper(ID),
Ägare INTEGER NOT NULL REFERENCES Flygbolag(ID),
Flygplats INTEGER NULL REFERENCES Flygplatser(ID));

CREATE TABLE Varit
(ID INTEGER NOT NULL PRIMARY KEY,
Flygplan INTEGER NOT NULL REFERENCES Flygplan(ID),
Flygplats INTEGER NOT NULL REFERENCES Flygplatser(ID),
UNIQUE(Flygplan, Flygplats));

INSERT INTO Flygplanstyper (ID, Tillverkare, Modell) VALUES (1, 'Airbus', 'A380');
INSERT INTO Flygplanstyper (ID, Tillverkare, Modell) VALUES (2, 'Airbus', 'A400M');
INSERT INTO Flygplanstyper (ID, Tillverkare, Modell) VALUES (3, 'Boeing', '747');

INSERT INTO Flygbolag (ID, Namn, Land) VALUES (1, 'SAS', 'Sverige');
INSERT INTO Flygbolag (ID, Namn, Land) VALUES (2, 'United', 'USA');
INSERT INTO Flygbolag (ID, Namn, Land) VALUES (3, 'Air France', 'Frankrike');

INSERT INTO Flygplatser (ID, Namn, Kod, Land) VALUES (1, 'Arlanda', 'ARN', 'Sverige');
INSERT INTO Flygplatser (ID, Namn, Kod, Land) VALUES (2, 'Bromma', 'BMA', 'Sverige');
INSERT INTO Flygplatser (ID, Namn, Kod, Land) VALUES (3, 'Heathrow', 'LHR', 'UK');

INSERT INTO Flygplan (ID, Beteckning, Typ, Ägare, Flygplats) VALUES (1, 'SE-FGA', 1, 1, 1);
INSERT INTO Flygplan (ID, Beteckning, Typ, Ägare, Flygplats) VALUES (2, 'SE-FGB', 1, 2, 1);
INSERT INTO Flygplan (ID, Beteckning, Typ, Ägare, Flygplats) VALUES (3, 'SE-FGC', 3, 2, 1);
INSERT INTO Flygplan (ID, Beteckning, Typ, Ägare, Flygplats) VALUES (4, 'SE-FGD', 3, 2, NULL);

INSERT INTO Varit (ID, Flygplan, Flygplats) VALUES (1, 1, 1);
INSERT INTO Varit (ID, Flygplan, Flygplats) VALUES (2, 1, 2);
INSERT INTO Varit (ID, Flygplan, Flygplats) VALUES (3, 1, 3);
INSERT INTO Varit (ID, Flygplan, Flygplats) VALUES (4, 2, 1);
INSERT INTO Varit (ID, Flygplan, Flygplats) VALUES (5, 2, 2);

SELECT * FROM Flygplanstyper;
SELECT * FROM Flygbolag;
SELECT * FROM Flygplatser;
SELECT * FROM Flygplan;
SELECT * FROM Varit;

Uppgift 3 (10 p)

Formulera följande frågor i SQL. Definiera gärna vyer om det underlättar, men skapa inte nya tabeller.

a) (1p) Vad heter de flygplatser som har en kod som innehåller bokstaven X?

SELECT namn FROM Flygplatser WHERE kod LIKE '%X%';

b) (2p) Vilka flygbolag från USA har just nu flygplan som befinner sig på Arlanda?

SELECT DISTINCT Flygbolag.namn
FROM Flygplan, Flygbolag, Flygplatser
WHERE Flygplan.ägare = Flygbolag.ID
AND Flygplan.flygplats = Flygplatser.ID
AND Flygbolag.land = 'USA'
AND Flygplatser.namn = 'Arlanda';

c) (2p) Vilka länder har flygplanen från flygbolaget United besökt? (Man kan se det på vilka flygplatser de varit på.)

SELECT DISTINCT Flygplatser.land
FROM Flygbolag, Flygplan, Varit, Flygplatser
WHERE Flygbolag.namn = 'United'
AND Flygbolag.ID = Flygplan.ägare
AND Flygplan.ID = Varit.flygplan
AND Varit.flygplats = Flygplatser.ID;

d) (2p) Vad heter de flygbolag som inte har några flygplan?

SELECT namn
FROM Flygbolag
WHERE NOT ID IN (SELECT ägare FROM Flygplan);

e) (3p) Vad heter det flygbolag som har flest flygplan?

CREATE VIEW AntalFlygplan AS
SELECT Flygbolag.namn AS Bolag, COUNT(*) AS Antal
FROM Flygplan, Flygbolag
WHERE Flygplan.ägare = Flygbolag.ID
GROUP BY Flygbolag.namn;

SELECT bolag
FROM AntalFlygplan
WHERE antal = (SELECT MAX(antal) FROM AntalFlygplan);

Uppgift 4 (4 p)

Databasen innehåller realistiska mängder data, vilket innebär 10-20 flygplanstillverkare, tusentals flygplatser och tiotusentals flygplan. SQL-frågorna i uppgift 3 körs väldigt ofta (men kanske med andra konstanter, till exempel att man söker efter flygplatser med Y i flygplatskoden). De tar för lång tid att köra, och behöver snabbas upp. Vi märker att det inte finns några index alls i databasen, inte ens på nycklar.

a) Fråga a kan vara svår att få att gå snabbare genom att skapa index. Varför?

Index på textsträngar kan normalt bara användas om man vet hela, eller ett prefix (alltså inledning) av den sökta strängen, inte en del mitt i.

b) Vilka index bör man skapa för att fråga b och c ska gå snabbare?

Flygbolag.ID
Flygbolag.land
Flygbolag.namn
Flygplan.ID
Flygplan.flygplats
Flygplan.ägare
Flygplatser.ID
Flygplatser.namn
Varit.flygplan
Varit.flygplats

c) De flesta databashanterarna skapar automatiskt index på primärnycklar. Vilka anledningar finns det till att man gjort så?

Uppgift 5 (5 p)

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

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

2NF.

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

I 3NF får det inte finnas några ffb mellan icke-nyckel-attribut, och i den här tabellen finns ett ffb mellan icke-nyckel-attributen Bolag och Bolagsland.

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

Kommentar: Det är alltså redundansen att det står Sverige Sverige Sverige Sverige i tabellen som man slipper om man normaliserar tabellen till tredje normalformen. Att det står Saab Saab Saab Saab, eller SAS SAS SAS SAS, eller 340 340 340, eller SE- SE- SE- SE- är visserligen upprepningar, men inte redundant information, och det påverkas inte av normaliseringen. Ett svar som tar upp dessa upprepningar är fel.

Uppgift 6 (5 p)

a) ACID-transaktion

En transaktion är en följd av operationer som hör ihop som en enhet. Transaktioner bör ha ACID-egenskaperna, dvs de ska vara atomära (A), konsistensbevarande (C), isolerade från varandra (I) och hållbara (D). De flesta databashanterare har stöd för att automatiskt ge transaktionerna dessa egenskaper.

b) B-träd

En lagringsstruktur som ofta används i diskbaserade databaser. Ett B-träd är inte samma sak som ett binärt träd, utan noderna brukar vara stora, med plats för pekare till många (hundratals) subträd under varje nod. Det finns olika teorier om vad B:et står för, men det kan vara "balanserat", eftersom B-träden automatiskt balanseras om vid insättningar och borttagningar. Eftersom träden både är av hög ordning och balanserade blir de låga, vilket gör att man inte behöver besöka så många noder för att hitta sina data. Ett B-träd kan snabbt hantera även stora datamängder, man kan söka efter intervall (exempelvis efter alla namn som börjar med "Berg"), och man kan snabbt få tillgång till posterna sorterade i ordning.

c) cursor

Används i lagrade procedurer och när SQL inbäddas i ett programmeringsspråk för att ange den aktuella raden i resultatet från en fråga.

d) DBA

Databasadministratör. En person eller en grupp av personer som är ansvariga för driften av ett databassystem.

e) ECA-regel

En regel i en aktiv databas. ECA står för Event-Condition-Action. Varje gång en viss händelse (event) inträffar, till exempel att en rad läggs till i en viss tabell, kommer detabashanteraren automatiskt att kontrollera ett villkor (condition), och om villkoret stämmer utförs en åtgärd (action).

Se även kurslitteraturen eller webbkursen.

Uppgift 7 (5 p)

a) "Relationerna" i en relationsdatabas är kopplingarna mellan tabeller med främmande nycklar ("foreign keys").

Nej, det är själva tabellerna som kallas relationer. Det kommer från matematikens relationer, som är en generalisering av funktioner.

b) En databas är normalt lagrad på en hårddisk eller SSD. Databashanterare kan fungera på lite olika sätt, men det vanliga är att databashanteraren läser in alla data till primärminnet när man startar den, och jobbar med dem där. När minnet är fullt, skrivs databasen tillbaka till disken. Därför är det viktigt att ha stabli strömförsörjning till datorn, så man inte blir av med alla sina data.

Nej, databasens data finns på disk. Databashanteraren hämtar in data till primärminnet när den ska arbeta med det, och skriver ändringarna till disken (ganska) direkt när de är gjorda. Tillsammans med loggfilen gör det att inga, eller mycket få, data försvinner vid ett strömavbrott.

Däremot används både buffring och cachning, som använder primärminnet som mellanlagring. Det finns också primärminnesdatabaser, som lagrar alla sina data i primärminnet, men traditionella databaser är diskbaserade.

c) När databashanteraren kör en SQL-fråga, söker den i tabellerna i den ordning som de står i frågan. Till exempel kommer en fråga som select A, B, C from X, Y where X.D = Y.E att börja med tabell X. Olika ordningar kan ge mycket olika prestanda, och därför är det viktigt att skriva tavellerna i en lämplig ordning när man skriver SQL-frågor.

Nej, SQL-frågan körs inte direkt som den står, som med ett program skrivet i ett vanligt programmeringsspråk som C eller Java, utan SQL-frågan måste först översättas till en så kallad exekveringsplan. Då optimeras frågan av en särskild frågeoptimerare, som väljer en "billig", dvs snabb, exekveringsplan. Två SQL-frågor som är skrivna på olika sätt, men som är logiskt ekvivalenta och ska ge samma svar, kommer (om allt fungerar som det ska) att översättas till samma exekveringsplan.


Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 14 juni 2017