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;
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);
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å?
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.
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.
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.