1. Inledning

Vad är en databas?

En databas (database) är en samling data som är lagrad på en dator och som man kan söka i samt ändra. Några exempel på databaser är:

ett universitets studieregister
produkter och lagersaldo i en nätbutik
en banks kunduppgifter och kontotransaktioner
dagligen uppmätta väderobservationer på olika platser
ett flygbolags tidtabeller och bokningsstatus

Det finns numera en mängd olika databaser. De flesta av oss använder dagligen flera tjänster som är kopplade till databaser.

Utmaningar med databaser

Det finns flera utmaningar som hör ihop med den tekniska implementeringen av databaser, till exempel:

Effektivitet: En databas kan innehålla mycket stora mängder data som ständigt söks fram och uppdateras. Hur kan man bygga databasen så att datan kan nås på ett effektivt sätt?
Samtidig åtkomst av data: En databas kan ha många användare som söker och ändrar datan samtidigt. Vad behöver man ta i beaktande i databasens konstruktion för att hantera detta?
Oväntade situationer: Databasens innehåll borde hållas intakt också i oväntade situationer. Till exempel, vad händer om strömmen går precis när datan håller på att uppdateras?

Databashanterare

En databashanterare (ett databashanteringssystem) sköter innehållet i databasen och erbjuder de funktioner som användaren behöver för att kunna söka fram och ändra data. Observera att ordet databas också ofta används synonymt med databashanterare/databashanteringssystem.

De flesta databaser som används i dag bygger på relationsmodellen och SQL-språket, vars teoretiska grund utvecklades på 1970-talet. Under denna kurs bekantar vi oss med databasen SQLite som lämpar sig väl för att lära sig SQL. Exempel på andra SQL-databaser är MySQL och PostgreSQL.

Termen NoSQL syftar på databaser som inte bygger på relationsmodellen och SQL-språket. MongoDB och Redis är exempel på sådana databaser. NoSQL-databaser behandlas dock inte i någon större utsträckning under denna kurs.

Gör-det-själv-databas

Innan vi bekantar oss med befintliga databashanterare är det bra att fundera på vilket behov sådana system fyller. Varför skulle vi inte helt enkelt kunna implementera en egen databas, till exempel genom att lagra databasens innehåll i en textfil i lämpligt format?

Exempel

Vi vill i databasen spara data om kursdeltagarnas uppgiftslösningar. När en kursdeltagare skickar in en lösning sparas studerandenumret, uppgiftsnumret, tidpunkten för inskickandet av lösningen och lösningens poäng i databasen.

Ett enkelt sätt att implementera databasen är att skapa en textfil där varje rad motsvarar en inlämning. Varje gång en studerande skickar in en lösning läggs en ny rad till i filen. I praktiken kunde vi spara databasen som ett CSV-format i filen database.csv på följande sätt:

012121212;1;2020-05-03 12:50:32;100
012341234;1;2020-05-03 14:02:12;20
012121212;2;2020-05-04 14:05:50;70
012121212;3;2020-05-04 14:43:12;0
012341234;2;2020-05-04 10:15:23;0
012341234;2;2020-05-04 16:40:39;0
013371337;1;2020-05-06 18:11:13;0
012341234;2;2020-05-07 10:02:15;100

I en CSV-fil skiljer ett visst avgränsningstecken fälten åt på varje rad. I det här fallet används semikolon ; som avgränsare. Till exempel visar filens första rad att studerande 012121212 har lämnat in en lösning för uppgift 1 och fått 100 poäng för lösningen.

Om vi nu exempelvis vill föra statistik där vi ser hur många inlämningar varje studerande har gjort, kan vi göra det i Python på följande sätt:

stats = {}

for line in open("database.csv"):
    student_id = line.split(";")[0]

    if student_id not in stats:
        stats[student_id] = 0

    stats[student_id] += 1

print(stats)

Koden ger följande resultat:

{'012121212': 3, '012341234': 4, '013371337': 1}

Detta betyder att studerande 012121212 har skickat in tre lösningar, studerande 012341234 har skickat in fyra lösningar och studerande 013371337 har skickat in en lösning.

Eventuella problemsituationer

I princip fungerar en sådan här databas som använder en CVS-fil. Problem kan dock uppstå:

Effektivitet

När datamängden växer kan det dröja att söka data i en CSV-fil. Det beror på att vi i de flesta situationer måste gå igenom hela filen från början till slut för att hitta det vi söker efter.

Om vi exempelvis vill ta reda på hur många poäng studerande 012341234 har fått för uppgift 2, måste vi gå igenom alla rader i filen för att hitta de rätta raderna. De rader som hör till en viss studerande kan ligga på flera olika ställen i filen och vi vet inte på förhand var de finns.

Att gå igenom filen är inget problem om den är liten. Det går snabbt att gå igenom en fil på till exempel hundra rader. Att gå igenom alla rader i en större fil för att få fram viss data är däremot tidskrävande.

Samtidig åtkomst av data

Vad händer om två studerande skickar in en lösning samtidigt? I det fallet borde två rader med data läggas till i slutet av filen på följande sätt:

012341234;3;2020-05-07 15:42:02;0
013371337;7;2020-05-07 15:42:02;0

Resultatet kan dock bli följande:

012341234;3;2020013371337;7;2020-05-07 15:42:02;0
-05-07 15:42:02;0

En sådan situation kan uppstå när två processer som körs samtidigt ändrar filens innehåll. Den första processen lägger först till följande i slutet av filen: 012341234;3;2020, varpå den andra processen lägger till följande mitt i filen: 013371337;7;2020-05-07 15:42:02;0. Slutligen lägger den första processen till följande: -05-07 15:42:02;0. Detta leder till att filen tappar sin struktur.

När data läggs till en fil är det inte självklart att det lyckas som önskat om någon annan försöker lägga till data samtidigt. Här har filsystemet, mängden data och sättet på hur filen hanteras betydelse.

Oväntade situationer

Låt oss betrakta en situation där vi vill radera studerandes 012341234 inlämningar från databasen. Ett sätt på vilket detta kan göras är att först läsa in alla rader i minnet och sedan skriva tillbaka endast de rader som inte tillhör studerande 012341234.

Vad händer om strömmen går när vi endast har hunnit skriva tillbaka hälften av raderna? När vi omstartar datorn inser vi att filen bara innehåller hälften av raderna och att resten av raderna har försvunnit. Vi har nu ingen möjlighet att få tillbaka de förlorade raderna.

Vad lär vi oss utav detta exempel?

En enkel textfil är i sig inte ett dåligt sätt att lagra data på, men den lämpar sig inte för alla användningsändamål. Vi behöver därför separata databashanterare som vi kommer att bekanta oss med under denna kurs.

Utvecklarna av databashanterare har noggrant övervägt hur ett system bör utformas för att ge effektiv åtkomst till data, undvika problem när flera användare samtidigt använder systemet och säkerställa att data inte går förlorad i oväntade situationer. När vi använder en databashanterare behöver vi alltså inte själva ta hand om allt detta.

Relationsmodellen och SQL-språket

Under denna kurs bekantar vi oss med databaser genom relationsmodellen och SQL-språket. De grundläggande principerna är:

All data lagras i tabeller som består av rader och raderna kan hänvisa till varandra.
Databasanvändaren hanterar data med hjälp av SQL-språket, vilket döljer detaljerna om hur databasen fungerar internt.

Databasens struktur

En databas består av tabeller (table) som har fasta kolumner (column). Data lagras i tabellerna som rader (row). Varje rad har ett specifikt värde i varje kolumn. Varje tabell innehåller samlad data gällande ett visst ämne.

Nedan finns ett exempel på en databas som kunde användas för en nätbutik. Tabellerna Products, Customers och Purchases innehåller data om produkterna, kunderna och innehållet i deras köpkorgar.

id	name	price
1	rädisa	7
2	morot	5
3	rova	4
4	kålrot	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Exempelvis innehåller tabellen Products kolumnerna id, name och price. Tabellen innehåller för tillfället fem rader med data som beskriver de produkter som finns i databasen.

I tabellerna Products och Customers har varje rad ett unikt ID-nummer som kan användas för att referera till raden. Detta är ett vanligt sätt att utforma databaser på. Tack vare detta kan vi i tabellen Purchases, med hjälp av ID-nummeren, visa vilka produkter respektive kund har valt. I detta exempel innehåller Uolevis köpkorg en morot och en selleri. Maijas köpkorg innehåller en rädisa, en kålrot och en selleri. Aapelis köpkorg är tom.

SQL-språket

SQL (Structured Query Language) är ett etablerat sätt att hantera innehållet i en databas. Språket innehåller kommandon som för databasanvändaren (till exempel en programmerare som arbetar med databasen) möjliggör att lägga till, hämta, ändra och ta bort data.

SQL-kommandon består av nyckelord (som SELECT och WHERE), tabellnamn, kolumnnamn och andra värden. Till exempel kommandot

SELECT price FROM Products WHERE name = 'rädisa';

hämtar priset på rädisan från databasens produkter. Kommandot avslutas med ett semikolon ;. Vi kan fritt använda mellanslag och radbrytningar. Vi kan också skriva kommandot på flera rader, till exempel på följande sätt:

SELECT price
FROM Products
WHERE name = 'rädisa';

SELECT
  price
FROM
  Products
WHERE
  name = 'rädisa';

Vi bekantar oss med SQL-språket mer ingående i kapitlen 2–4.

SQL-språket utvecklades på 1970-talet och det bär på många drag från programmering från förr i tiden som kännetecknas av bland annat att:

Nyckelorden är fullständiga engelska ord och kommandon liknar engelska meningar
Bokstavsstorleken spelar ingen roll i nyckelorden. Till exempel betyder SELECT, select och Select samma sak. Nyckelorden skrivs oftast helt med stora bokstäver.
Tecknet = används både för tilldelning av värden till variabler och för likhetsjämförelse (numera används ofta == för likhetsjämförelse i programmering).

Det finns standarder för SQL-språket som försöker ge en gemensam grund för språket. I praktiken fungerar dock varje databassystems SQL-implementation på sitt eget sätt. Under kursen fokuserar vi på de egenskaper i SQL som finns tillgängliga i de flesta databaser.

Databasens interna funktion

Databashanterarens uppgift är att hantera de SQL-kommandon som användaren anger. Till exempel, om användaren ger ett kommando som hämtar data från databasen ska databashanteraren hitta ett effektivt sätt att bearbeta kommandot och returnera resultaten till användaren så snabbt som möjligt.

Det fina med SQL-språket är att användaren bara behöver beskriva vilken data som ska hämtas ur databasen, varefter databassystemet sköter om resten av arbetet med att hämta uppgifterna från databasen. Detta är smidigt eftersom användaren inte behöver veta något om databasens interna funktioner, utan kan lita på databashanteraren.

Det är svårt att implementera ett databassystem eftersom systemet måste kunna hantera SQL-kommandon, säkerställa att allt fungerar korrekt när flera användare är aktiva och kunna hantera oväntade situationer. Under kursen bekantar vi oss med databaser främst ur användarens perspektiv och går inte desto djupare in på databasernas interna funktioner.

2. Grunderna i SQL-språket

Grundkommandon

I detta kapitel bekantar vi oss med de vanligaste SQL-kommandona som används för att lägga till, hämta, ändra och ta bort innehåll i databasen.

Skapa en tabell

Kommandot CREATE TABLE skapar en tabell med önskade kolumner. Till exempel skapar följande kommando tabellen Products som innehåller tre kolumner:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER
);

Vi kan namnge tabellen och kolumnerna med valfria namn. I denna kurs är det praxis att skriva tabellnamnet med stor begynnelsebokstav och i pluralform. Kolumnnamnen skriver vi med liten begynnelsebokstav.

För varje kolumn anges förutom namnet även dess datatyp. I den här tabellen är kolumnerna id och price heltal (INTEGER) och kolumnen name är en sträng (TEXT). Kolumnen id är dessutom tabellens primärnyckel (PRIMARY KEY), vilket innebär att den identifierar varje rad i tabellen och gör det enkelt för oss att referera till raderna i tabellen.

Primärnyckel

En tabells primärnyckel (primary key) är en kolumn (eller en kombination av kolumner) som identifierar varje rad i tabellen. Primärnyckeln har alltid ett unikt värde för varje rad i tabellen. Som primärnyckel används vanligtvis ett heltalsbaserat id-nummer.

Ofta vill vi dessutom att id-numret ska ha löpande numrering. Det betyder att när rader läggs till i tabellen får den första raden automatiskt id-numret 1, den andra raden id-numret 2 och så vidare. Hur löpande numrering implementeras beror på databasen. I en SQLite-databas får till exempel en kolumn av datatypen INTEGER PRIMARY KEY automatiskt löpande numrering.

Lägg till data

Med kommandot INSERT kan man lägga till en ny rad i en tabell. Till exempel lägger följande kommando till en rad i tabellen Products vi nyss skapade:

INSERT INTO Products (name, price) VALUES ('rädisa', 7);

Här anger vi värden för kolumnerna name och price i raden som ska läggas till. I och med att kolumnen id har löpande numrering får den automatiskt värdet 1 för tabellens första rad. Tabellen ser nu ut på följande sätt:

id  name     price
--  -------  -----
1   rädisa   7    

I fall att vi inte anger ett värde för en kolumn får den ett standardvärde. Standardvärdet är vanligtvis NULL, vilket betyder att data saknas. Som exempel anger vi inget värde för kolumnen price i följande kommando:

INSERT INTO Products (name) VALUES ('rädisa');

Tabellen får då en rad där priset är NULL (alltså priset fattas):

id  name     price
--  -------  -----
1   rädisa   NULL 

Exempeltabell

Vi antar i detta avsnitts kommande exempel att följande fem rader har lagts till i tabellen Products:

INSERT INTO Products (name, price) VALUES ('rädisa', 7);
INSERT INTO Products (name, price) VALUES ('morot', 5);
INSERT INTO Products (name, price) VALUES ('rova', 4);
INSERT INTO Products (name, price) VALUES ('kålrot', 8);
INSERT INTO Products (name, price) VALUES ('selleri', 4);

Tabellen ser alltså ut på följande sätt:

id  name      price
--  --------  -----
 rädisa    7    
 morot     5    
 rova      4    
 kålrot    8    
 selleri   4    

Hämta data

Kommandot SELECT ställer en fråga (query), alltså hämtar data från en tabell. Det enklaste sättet att formulera en fråga på är att hämta all data från tabellen:

SELECT * FROM Products;

Frågan ger följande svar:

id  name      price
--  --------  -----
 rädisa    7    
 morot     5    
 rova      4    
 kålrot    8    
 selleri   4    

I frågan anger stjärnan * att vi vill hämta alla kolumner. Det är också möjligt att hämta endast en del av kolumnerna genom att ange deras namn. Till exempel hämtar följande fråga bara produktnamnen:

SELECT name FROM Products;

Frågan ger följande svar:

name
--------
rädisa    
morot             
rova               
kålrot               
selleri              

Följande fråga hämtar produktnamnen och produktpriserna.

SELECT name, price FROM Products;

Frågan ger nu följande svar:

name      price
--------  -----
rädisa    7    
morot     5    
rova      4    
kålrot    8    
selleri   4    

Raderna som frågan returnerar bildar en tabell som kallas för en resultattabell (result table). Dess kolumner och rader beror på innehållet i frågan. Till exempel skapade den föregående frågan en resultattabell som består av två kolumner och fem rader.

När man jobbar med databaser förekommer det alltså två typer av tabeller: dels de tabeller som finns permanent i databasen och som innehåller databasens data, och dels de tillfälliga resultattabeller som skapas av frågor och vars innehåll bygger på de permanenta tabellerna.

Sökvillkor

Genom att lägga till villkoret WHERE i en SELECT-fråga kan vi välja endast de rader som uppfyller ett visst villkor. Till exempel hämtar följande fråga information om kålroten:

SELECT * FROM Products WHERE name = 'kålrot';

Frågan ger följande svar:

id  name    price
--  ------  -----
4   kålrot  8    

I villkoren kan man använda jämförelser och orden AND och OR på samma sätt som i programmering. Till exempel söker följande fråga produkter vars pris ligger mellan 4 och 6:

SELECT * FROM Products WHERE price >= 4 AND price <= 6;

Frågan ger följande svar:

id  name      price
--  --------  -----
2   morot     5    
3   rova      4    
5   selleri   4    

I SQL betyder operatorn <> “inte lika med”. Till exempel hämtar följande fråga de rader där priset inte är 4:

SELECT * FROM Products WHERE price <> 4;

Frågan ger följande svar:

id  name      price
--  --------  -----
1   rädisa    7    
2   morot     5    
4   kålrot    8    

Sortering

Som standard kan raderna i resultattabellen ha vilken ordning som helst. Vi kan dock ange önskad ordning med hjälp av ORDER BY. Till exempel hämtar följande fråga produkterna i alfabetisk ordning enligt namn:

SELECT * FROM Products ORDER BY name;

Frågan ger följande svar:

id  name      price
--  --------  -----
 kålrot    8    
 rova      4    
 morot     5    
 rädisa    7    
 selleri   4    

Ordningen är som standard från minst till störst. Om vi vill ha ordningen från störst till minst kan vi lägga till DESC efter kolumnnamnet:

SELECT * FROM Products ORDER BY name DESC;

Frågan ger nu följande svar:

id  name      price
--  --------  -----
 selleri   4    
 rädisa    7    
 morot     5     
 rova      4    
 kålrot    8    

I databasspråk är ordningen antingen stigande (ascending), alltså från minst till störst, eller fallande (descending), alltså från störst till minst. Som standard är ordningen stigande, och nyckelordet DESC betyder alltså fallande ordning.

Nyckelordet ASC, betyder stigande ordning. Följande frågor fungerar alltså på samma sätt:

SELECT * FROM Products ORDER BY name;

SELECT * FROM Products ORDER BY name ASC;

Nyckelordet ASC används sällan i praktiken.

Vi kan också ordna rader enligt flera olika kriterier. Till exempel ordnar följande fråga raderna först enligt pris från dyrast till billigast, och därefter alfabetiskt enligt namn:

SELECT * FROM Products ORDER BY price DESC, name;

Frågan ger följande svar:

id  name      price
--  --------  -----
 kålrot    8    
 rädisa    7    
 morot     5    
 rova      4    
 selleri   4    

Produkterna rova och selleri ordnas här i alfabetisk ordning eftersom de kostar lika mycket.

Separata resultatrader

Ibland kan resultattabellen innehålla flera likadana rader:

SELECT price FROM Products;

Eftersom priset för två produkter är 4, blir innehållet i två resultatrader 4:

Om vi däremot bara vill ha unika resultatrader kan vi lägga till nyckelordet DISTINCT i frågan:

SELECT DISTINCT price FROM Products;

Frågan ger nu följande svar:

Begränsning av resultatrader

När vi lägger till LIMIT x i slutet av frågan returnerar frågan som svar endast de x första resultatraderna. Till exempel betyder LIMIT 3 att frågan returnerar som svar de tre första resultatraderna.

En mer generell form är LIMIT x OFFSET y, vilket betyder att vi vill ha x rader med start från position y (0-indexerat). Till exempel betyder LIMIT 3 OFFSET 1 att frågan returnerar som svar den andra, tredje och fjärde resultatraden.

Låt oss som exempel titta på en fråga som hämtar produkterna från den billigaste till den dyraste:

SELECT * FROM Products ORDER BY price;

Frågan ger följande resultattabell som svar:

id  name      price
--  --------  -----
 rova      2    
 selleri   4    
 morot     5    
 rädisa    7    
 kålrot    8    

Vi kan hämta de tre billigaste produkterna på följande sätt:

SELECT * FROM Products ORDER BY price LIMIT 3;

Frågan ger följande svar:

id  name      price
--  --------  -----
3   rova      2    
5   selleri   4    
2   morot     5    

Följande fråga hämtar de tre billigaste produkterna med start från den näst billigaste produkten:

SELECT * FROM Products ORDER BY price LIMIT 3 OFFSET 1;

Frågan ger följande svar:

id  name      price
--  --------  -----
5   selleri   4    
2   morot     5    
1   rädisa    7    

Skillnader mellan databaser

Begränsning av resultatrader är ett exempel på något som implementeras olika i olika databashanterare. Den syntax som visas här, LIMIT x OFFSET y, fungerar förutom i SQLite även i MySQL och PostgreSQL.

I SQLite och MySQL finns också en kortare syntax, LIMIT y, x, där parametrarna x och y kommer i omvänd ordning. Denna kortare syntax fungerar inte i PostgreSQL.

Möjligheten att begränsa resultatrader kom med i SQL-standarden först år 2008. Den standardenliga syntaxen är OFFSET y ROWS FETCH FIRST x ROWS ONLY. Denna syntax fungerar i PostgreSQL men inte i MySQL eller SQLite.

Ändra data

Kommandot UPDATE ändrar de rader i en tabell som matchar det önskade villkoret. Till exempel ändrar följande kommando priset på produkt 2 till 6:

UPDATE Products SET price = 6 WHERE id = 2;

Flera kolumner kan ändras samtidigt genom att kombinera ändringarna med kommatecken. Till exempel ändrar följande kommando namnet på produkt 2 till ananas och priset till 6:

UPDATE Products SET name = 'ananas', price = 6 WHERE id = 2;

Om kommandot inte innehåller något villkor påverkar ändringen alla rader. Till exempel ändrar följande kommando priset på varje produkt till 1:

UPDATE Products SET price = 1;

Ta bort data

Kommandot DELETE tar bort de rader i en tabell som matchar det givna villkoret. Till exempel tar följande kommando bort produkt 5 från tabellen:

DELETE FROM Products WHERE id = 5;

På samma sätt som när man ändrar data så påverkar kommandot alla rader när man tar bort data om man inte anger något villkor. Följande kommando tar alltså bort alla produkter från tabellen:

DELETE FROM Products;

Kommandot DROP TABLE tar bort en tabell från databasen (och allt dess innehåll). Till exempel tar följande kommando bort tabellen Products:

DROP TABLE Products;

Kommentarer

Tecknet -- används för att skriva en kommentar som sträcker sig till slutet av raden:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER -- price in euros
)

Ett annat sätt är att börja kommentaren med /* och avsluta med */:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER /* price in euros */
)

Sammanfattning och gruppering

En sammanfattningsfråga returnerar ett enskilt värde från raderna i en tabell, till exempel antalet rader i tabellen eller summan av alla värden i en kolumn. Resultattabellen för en sådan fråga innehåller endast en rad.

Grunden för en sammanfattningsfråga är en aggregeringsfunktion (aggregate function) som beräknar ett sammanfattande värde från tabellens rader. De vanligaste aggregeringsfunktionerna är:

COUNT(): antal rader
SUM(): summan av värdena
MIN(): minsta värdet
MAX(): största värdet
AVG(): medelvärdet av värdena

Exempel

Låt oss titta på tabellen Products:

id  name      price
--  --------  -----
 rädisa    7    
 morot     5    
 rova      4    
 kålrot    8    
 selleri   4    

Följande fråga hämtar antalet rader i tabellen:

SELECT COUNT(*) FROM Products;

COUNT(*)
--------
5

Följande fråga hämtar antalet rader där priset är 4:

SELECT COUNT(*) FROM Products WHERE price = 4;

COUNT(*)
--------
2

Följande fråga hämtar summan av produkternas priser:

SELECT SUM(price) FROM Products;

SUM(price)
----------
28

I det här fallet är svaret på frågan 7 + 5 + 4 + 8 + 4 = 28.

Följande fråga hämtar det lägsta och det högsta priset:

SELECT MIN(price), MAX(price) FROM Products;

MIN(price)  MAX(price)
----------  ----------
4           8

Mer om COUNT-funktionen

Om COUNT-funktionen innehåller stjärnan *, räknar frågan alla rader. Om funktionen däremot innehåller ett kolumnnamn, räknar frågan de rader där kolumnen har ett värde, det vill säga där kolumnvärdet inte är NULL.

I följande exempel har rad 3 i tabellen inget pris:

id  name     price
--  -------  -----
1   rädisa   7    
2   rova     4    
3   kålrot   NULL 
4   selleri  4    

Följande fråga hämtar det totala antalet rader:

SELECT COUNT(*) FROM Products;

COUNT(*)  
--------
4

Följande fråga hämtar antalet rader som har ett pris:

SELECT COUNT(price) FROM Products;

COUNT(price)
------------
3

Vi kan också använda DISTINCT för att räkna hur många olika värden som finns i en kolumn:

SELECT COUNT(DISTINCT price) FROM Products;

COUNT(DISTINCT price)
---------------------
2

Svaret på denna fråga blir 2 eftersom kolumnen price har två olika värden (4 och 7). NULL-värden räknas inte med.

Hantering av tomma dataset

Aggregeringsfunktioner kan ge något oväntade resultat om datasetet som behandlas inte innehåller några rader. Låt oss ta följande tomma tabell som exempel:

id  name     price
--  -------  -----

Funktionen COUNT anger antalet rader som 0:

SELECT COUNT(price) FROM Products;

COUNT(price)
------------
0

Funktionen SUM anger däremot summan som NULL:

SELECT SUM(price) FROM Products;

SUM(price)
----------
NULL

Svaret kan vara oväntat eftersom man skulle kunna anta att summan av en tom mängd är 0 och inte NULL. På samma sätt ger även MIN, MAX och AVG svaret NULL för ett tomt dataset.

Funktionen IFNULL kan användas för att hantera situationer där en aggregeringsfunktion kan ge resultatet NULL. I följande fråga betyder IFNULL(SUM(price), 0) i princip samma sak som SUM(price), men om svaret skulle bli NULL ges istället 0 som svar.

SELECT IFNULL(SUM(price), 0) FROM Products;

IFNULL(SUM(price), 0)
---------------------´
0

Hantering av NULL-värden

Funktionen IFNULL(x, y) returnerar värdet x om x inte är NULL och annars värdet y. På så sätt kan man hantera situationer där ett värde i en SQL-fråga kan vara NULL. Till exempel gör IFNULL(x, 0) att värdet blir 0 om x är NULL.

Funktionen IFNULL ingår inte i SQL-standarden och fungerar inte i alla databashanterare. Den standardenliga SQL-funktionen är COALESCE som kan användas på samma sätt som IFNULL. Till exempel är COALESCE(x, 0) ekvivalent med IFNULL(x, 0).

Vi återkommer till en mer ingående hantering av NULL-värden senare i materialet.

Gruppering

Med gruppering kan vi kombinera radvis information med den information som en aggregeringsfunktion ger. Idén är att raderna delas in i grupper baserat på de kolumner som anges i GROUP BY-delen. Aggregeringsfunktionen beräknar sedan värdet för varje grupp separat.

Låt oss ta följande tabell Employees som exempel:

id  name      company   salary
--  --------  --------  ------
 Anna      Google    8000  
 Liisa     Google    7500  
 Kaaleppi  Amazon    5000  
 Uolevi    Amazon    8000  
 Maija     Google    9500  
 Vihtori   Facebook  5000  

Följande fråga hämtar antalet anställda för varje företag:

SELECT company, COUNT(*) FROM Employees GROUP BY company;

Frågan ger följande svar:

company   COUNT(*)
--------  --------
Amazon    2       
Facebook  1       
Google    3       

Detta betyder att Amazon har 2 anställda, Facebook har 1 anställd och Google har 3 anställda.

Hur fungerar en gruppering?

Vid en gruppering innehåller varje grupp alla rader som har samma värden i de kolumner som används för grupperingen. Grupperingen genererar en resultattabell där antaler rader motsvarar antalet grupper. Varje rad innehåller de kolumner som användes för grupperingen och de värden som beräknats med aggregeringsfunktionerna.

I den föregående frågan är villkoret för grupperingen GROUP BY company, vilket innebär att raderna delas in i grupper baserat på kolumnen company. I detta fall är grupperna:

Grupp 1:

id  name      company  salary
--  --------  -------  ------
3   Kaaleppi  Amazon   5000  
4   Uolevi    Amazon   8000  

Grupp 2:

id  name     company   salary
--  -------  --------  ------
6   Vihtori  Facebook  5000  

Grupp 3:

id  name   company  salary
--  -----  -------  ------
1   Anna   Google   8000  
2   Liisa  Google   7500  
5   Maija  Google   9500  

Därefter beräknas antalet rader för varje grupp med funktionen COUNT(*).

Kolumner för gruppering

Rätt ofta baseras gruppering på exakt en kolumn som kolumnen company i det föregående exemplet. Om en gruppering baseras på flera kolumner så består varje grupp av rader som har samma kombination av värden i de kolumner som ingår i grupperingen.

Till exempel baseras grupperingen i följande fråga på två kolumner:

SELECT company, salary, COUNT(*) FROM Employees GROUP BY company, salary;

Eftersom varje rad har en unik kombination av värden i dessa kolumner tillhör varje rad sin egen grupp i resultattabellen:

company   salary  COUNT(*)
--------  ------  --------
Amazon    5000    1       
Amazon    8000    1       
Facebook  5000    1       
Google    7500    1       
Google    8000    1       
Google    9500    1       

Efter grupperingen kan resultattabellen innehålla de kolumner som ingår i grupperingen, men inte kolumner utanför grupperingen. Till exempel är följande fråga inte korrekt:

SELECT company, name FROM Employees GROUP BY company;

Här ingår inte kolumnen name i grupperingen, vilket gör det oklart vilket värde kolumnen ska få i resultattabellen. Till exempel får resultattabellen endast en rad som hör till företaget Amazon, men företaget har två anställda med olika namn (Kaaleppi och Uolevi).

Gruppering i SQLite

Observera att SQLite tillåter den föregående frågan där en kolumn utanför gruppering hämtas:

SELECT company, name FROM Employees GROUP BY company;

company   name   
--------  -------
Amazon    Uolevi 
Facebook  Vihtori
Google    Maija  

Eftersom kolumnen name inte ingår i gruppering kan den ha flera värden i gruppen, och resultattabellen får ett av dessa värden enligt någon bestämd logik. En sådan här fråga fungerar dock vanligtvis inte i andra databaser.

Fler exempel på frågor

Följande fråga hämtar summan av lönerna för varje företag:

SELECT company, SUM(salary) FROM Employees GROUP BY company;

company   SUM(salary)
--------  -----------
Amazon    13000      
Facebook  5000       
Google    25000      

Här blir summan av Amazons löner 5000 + 8000 = 13000, summan av Facebooks löner 5000, och summan av Googles löner 8000 + 7500 + 9500 = 25000.

Följande fråga hämtar den högsta lönen:

SELECT company, MAX(salary) FROM Employees GROUP BY company;

company   MAX(salary)
--------  -----------
Amazon    8000       
Facebook  5000       
Google    9500       

Här är Amazons högsta lön 8000, Facebooks högsta lön 5000 och Googles högsta lön 9500.

Namnge resultatskolumnen

Som standard får kolumnen i resultattabellen sitt namn direkt från frågan. Om vi vill kan vi ge den ett eget namn med hjälp av AS. På så sätt kan vi till exempel förtydliga vad sammanfattningsfrågan handlar om.

Till exempel namnges den andra kolumnen som max_salary i följande fråga:

SELECT
  company, MAX(salary) AS max_salary
FROM
  Employees
GROUP BY
  company;

company   max_salary
--------  ----------
Amazon    8000      
Facebook  5000      
Google    9500      

Observera att AS inte är nödvändigt att ha med i frågan. Frågan kan alternativt skrivas på följande sätt:

SELECT
  company, MAX(salary) max_salary
FROM
  Employees
GROUP BY
  company;

Begränsning efter gruppering

Vi kan också lägga till HAVING i frågan. HAVING begränsar resultaten efter grupperingen. Till exempel hämtar följande fråga de företag som har minst två anställda:

SELECT
  company, COUNT(*)
FROM
  Employees
GROUP BY
  company
HAVING
  COUNT(*) >= 2;

company  COUNT(*)
-------  --------
Amazon   2       
Google   3       

Vi kan också använda en aggregeringsfunktion endast i HAVING-delen:

SELECT
  company
FROM
  Employees
GROUP BY
  company
HAVING
  COUNT(*) >= 2;

company
-------
Amazon    
Google    

En överblick

SQL-strukturen kan sammanfattas enligt följande:

SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT

Vilka av dessa delar som ingår beror på själva frågan. Detta är ändå alltid ordningen som delarna i en SQL-fråga följer i förhållande till varandra.

Låt oss betrakta en exempelfråga som innehåller alla ovannämnda delar samtidigt. Frågan körs mot tabellen Tasks som innehåller uppgifter kopplade till olika projekt. Varje uppgift har en viss prioritet. En uppgift är kritisk om dess prioritet är minst 3.

id  project_id  priority
--  ----------  --------
 1           3       
 1           4       
 1           4       
 2           1       
 2           5       
 3           2       
 3           4       
 3           5       

Följande fråga söker efter projekt som har minst två kritiska uppgifter. Frågan sorterar resultaten efter projektets id-nummer och ger som svar de tio första resultaten.

SELECT
  project_id, COUNT(*)
FROM
  Tasks
WHERE
  priority >= 3
GROUP BY
  project_id
HAVING
  COUNT(*) >= 2
ORDER BY
  project_id
LIMIT
  10;

Frågan ger följande svar:

project_id  COUNT(*)
----------  --------
1           3       
3           2       

Detta innebär att projekt 1 har tre kritiska uppgifter och att projekt 3 har två kritiska uppgifter.

Låt oss ta en närmare titt på hur frågan fungerar. Utgångspunkten för frågan är alla rader i tabellen Tasks. Villkoret WHERE priority >= 3 väljer ut de kritiska uppgifterna för vidare bearbetning:

id  project_id  priority
--  ----------  --------
 1           3       
 1           4       
 1           4       
 2           5       
 3           4       
 3           5       

Frågan grupperar därefter raderna med GROUP BY project_id på följande sätt:

Grupp 1:

id  project_id  priority
--  ----------  --------
1   1           3       
2   1           4       
3   1           4       

Grupp 2:

id  project_id  priority
--  ----------  --------
5   2           5       

Grupp 3:

id  project_id  priority
--  ----------  --------
7   3           4       
8   3           5       

HAVING COUNT(*) >= 2 väljer till resultattabellen de grupper som har minst två rader. I det här fallet väljs grupperna 1 och 3.

Resultattabellen innehåller för varje grupp kolumnen project_id och resultatet, alltså antalet rader i gruppen, för funktionen COUNT(*). I det här fallet har projekt 1 tre kritiska uppgifter och projekt 3 två kritiska uppgifter.

ORDER BY project_id sorterar raderna i resultattabellen enligt projektets id-nummer. I det här fallet är projekten 1 och 3. LIMIT 10 påverkar inte resultatet i det här fallet eftersom det finns färre än 10 rader i tabellen.

Frågan ger följande resultattabell som svar:

project_id  COUNT(*)
----------  --------
1           3       
3           2       

Databasen SQLite

SQLite är en enkel och fritt tillgänglig databashanterare som lämpar sig väl för att lära sig SQL. Du kan enkelt pröva olika SQL-funktioner med SQLite. I kursens övningar använder vi SQLite.

Observera att SQLite dock har vissa begränsningar som kan orsaka problem i verkliga tillämpningar. Populära och fritt tillgängliga databashanterare är också MySQL och PostgreSQL. Dessa är något svårare att installera och använda än SQLite, men erbjuder fler funktioner.

Alla databashanterare använder ett liknande SQL-språk, vilket gör det smidigt att byta mellan olika databashanterare.

SQLite-tolk

SQLite-tolk är ett program som används för att hantera en SQLite-databas. Tolken startas genom att skriva kommandot sqlite3 i kommandoraden. Därefter kan man skriva antingen körbara SQL-kommandon eller tolkspecifika kommandon som börjar med en punkt.

Installationen av SQLite-tolken beror på operativsystemet:

Om du använder Linux kan du installera SQLite via pakethanteraren.
Om du använder en Mac är SQLite troligtvis redan installerat på din dator.
Om du använder Windows kan du hämta paketet från SQLites nedladdningssida via rubriken command-line tools (det vill säga kommandoradsverktyg). Filen som behövs är den vars namn börjar med sqlite3.

(Video: Installation och användning av SQLite för Windows)

Exempel

I SQLite-tolken gäller som standard att databasen finns i minnet (en så kallad in-memory database), vilket betyder att databasen från början är tom och försvinner när tolken stängs. En diskussion med tolken kan exempelvis se ut såhär:

$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT, price INTEGER);
sqlite> .tables
Products
sqlite> INSERT INTO Products (name, price) VALUES ('rädisa', 7);
sqlite> INSERT INTO Products (name, price) VALUES ('morot', 5);
sqlite> INSERT INTO Products (name, price) VALUES ('rova', 4);
sqlite> INSERT INTO Products (name, price) VALUES ('kålrot', 8);
sqlite> INSERT INTO Products (name, price) VALUES ('selleri', 4);
sqlite> SELECT * FROM Products;
1|rädisa|7
2|morot|5
3|rova|4
4|kålrot|8
5|selleri|4
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM Products;
id  name      price
--  --------  -----
1   rädisa    7    
2   morot     5    
3   rova      4    
4   kålrot    8    
5   selleri   4    
sqlite> .quit

I exemplet ovan skapar vi först tabellen Products och kontrollerar sedan med tolkens kommando .tables vilka tabeller som finns i databasen. Den enda tabellen som finns i databasen är Products, vilket är helt i sin ordning eftersom databasen är tom från början.

Vi lägger sedan till rader i tabellen och hämtar därefter raderna från tabellen. SQLite-tolken visar resultatet med lodräta streck mellan kolumnerna, vilket kan vara något svårt att läsa. Utskriften kan förbättras med tolkens kommandon .mode column (varje kolumn får en fast bredd) och .headers on (kolumnnamnen visas).

Vi avslutar med kommandot .quit som stänger tolken.

Databasen i en fil

När man startar SQLite-tolken kan man som parameter ange en fil dit databasen sparas. Databasens innehåll sparas då även efter att tolken stängts.

I följande exempel sparas databasen i filen test.db. Databasens innehåll finns nu kvar när tolken senare startas på nytt.

$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT, price INTEGER);
sqlite> .tables
Products
sqlite> .quit
$ sqlite3 test.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
Products
sqlite> .quit

Kommandon från en fil

Vi kan också ge SQLite-tolken en fil vars kommandon körs i följd. På så sätt kan vi automatisera körningen av kommandon. Till exempel kan vi skapa följande fil commands.sql:

commands.sql

CREATE TABLE Products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO Products (name, price) VALUES ('rädisa', 7);
INSERT INTO Products (name, price) VALUES ('morot', 5);
INSERT INTO Products (name, price) VALUES ('rova', 4);
INSERT INTO Products (name, price) VALUES ('kålrot', 8);
INSERT INTO Products (name, price) VALUES ('selleri', 4);
.mode column
.headers on
SELECT * FROM Products;

Därefter kan vi ge tolken kommandona från filen på följande sätt:

$ sqlite3 < commands.sql
id  name      price
--  --------  -----
1   rädisa    7    
2   morot     5    
3   rova      4    
4   kålrot    8    
5   selleri   4    

3. Frågor som omfattar flera tabeller

Tabellreferenser

En central idé inom databaser är att en rad i en tabell kan referera till en rad i en annan tabell. Således kan man skapa frågor som samlar data från flera tabeller utifrån dessa referenser. I praktiken består en sådan referens oftast av ID-numret för raden i den andra tabellen.

Exempel

Låt oss som exempel titta på en situation där databasen innehåller data om kurser och deras lärare. Vi antar att varje kurs har en lärare, och att samma lärare kan undervisa flera kurser.

Vi sparar information om lärarna i tabellen Teachers. Varje lärare har ett ID-nummer som vi kan referera till.

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Tabellen kan exempelvis se ut på följande sätt:

id  name      
--  ----------
1   Kaila     
2   Luukkainen
3   Kivinen   
4   Laaksonen 

Tabellen Courses innehåller data om kurserna. För varje kurs finns en referens till kursens lärare.

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Här refererar kolumnen teacher_id till ID-numret för en rad i tabellen Teachers, vilket anges med REFERENCES Teachers.

Tabellen kan exempelvis se ut på följande sätt:

id  name                    teacher_id
--  ----------------        ----------
 Beräkningsmodeller      3         
 Datanätverk             1         
 Seminarium              1         
 PHP-programmering       4         
 Neuronnät               3         

Detta innebär att till exempel kursen Beräkningsmodeller undervisas av läraren med ID-numret 3. Från tabellen Teachers framgår att detta är lärare Kivinen.

Vi kan nu hämta kurserna med deras lärare med följande fråga som samtidigt hämtar information från tabellerna Courses och Teachers:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Eftersom frågan involverar flera tabeller anger vi vilken tabell varje kolumn tillhör. Till exempel refererar Courses.name till kolumnen name i tabellen Courses.

Frågan ger följande svar:

name                    name     
----------------        ---------
Beräkningsmodeller      Kivinen  
Datanätverk             Kaila    
Seminarium              Kaila    
PHP-programmering       Laaksonen
Neuronnät               Kivinen  

Vad händer i exemplet ovan?

Frågan ovan omfattar flera tabeller (FROM Courses, Teachers). Vad innebär det egentligen?

Idén är att när en fråga omfattar flera tabeller är utgångspunkten för resultattabellen alla de olika möjliga sätten att kombinera rader från tabellerna. Därefter kan man med villkoren i WHERE-delen bestämma vilka radkombinationer som är av intresse.

Ett bra sätt för att förstå hur en fråga med flera tabeller fungerar är att först titta på en fråga som hämtar alla kolumner och som inte innehåller någon WHERE-del. I exemplet ovan är en sådan fråga följande:

SELECT * FROM Courses, Teachers;

Eftersom tabellen Courses har 5 rader och tabellen Teachers har 4 rader blir svaret en resultattabell på 5 * 4 = 20 rader. Resultattabellen innehåller alla möjliga sätt att först välja en rad från tabellen Courses och sedan en rad från tabellen Teachers:

id  name               teacher_id  id  name      
--  ----------------   ----------  --  ---------
 Beräkningsmodeller  3          1   Kaila     
 Beräkningsmodeller  3          2   Luukkainen
 Beräkningsmodeller  3          3   Kivinen   
 Beräkningsmodeller  3          4   Laaksonen 
 Datanätverk         1          1   Kaila     
 Datanätverk         1          2   Luukkainen
 Datanätverk         1          3   Kivinen   
 Datanätverk         1          4   Laaksonen 
 Seminarium          1          1   Kaila     
 Seminarium          1          2   Luukkainen
 Seminarium          1          3   Kivinen   
 Seminarium          1          4   Laaksonen 
 PHP-programmering   4          1   Kaila     
 PHP-programmering   4          2   Luukkainen
 PHP-programmering   4          3   Kivinen   
 PHP-programmering   4          4   Laaksonen 
 Neuronnät           3          1   Kaila     
 Neuronnät           3          2   Luukkainen
 Neuronnät           3          3   Kivinen   
 Neuronnät           3          4   Laaksonen 

De flesta resultatrader är dock inte av intresse eftersom deras information inte hör ihop sinsemellan. Till exempel berättar den första resultatraden bara att det finns en kurs som heter Beräkningsmodeller och att det finns en lärare som heter Kaila.

För att filtrera fram de rader som ger oss användbar information begränsar vi sökningen så att lärarens id-nummer ska vara samma i båda tabellerna:

SELECT
  *
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

Nu ger frågan ett mer intressant svar:

id  name                teacher_id  id  name     
--  ----------------    ----------  --  ---------
 Beräkningsmodeller  3           3   Kivinen  
 Datanätverk         1           1   Kaila    
 Seminarium          1           1   Kaila    
 PHP-programmering   4           4   Laaksonen
 Neuronnät           3           3   Kivinen  

Vi kan ytterligare precisera svarstabellen genom att endast välja de kolumner som är av intresse:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

På så sätt kommer vi fram till samma svar som tidigare:

name                name     
----------------    ---------
Beräkningsmodeller  Kivinen  
Datanätverk         Kaila    
Seminarium          Kaila    
PHP-programmering   Laaksonen
Neuronnät           Kivinen  

Flera villkor

I frågor som involverar flera tabeller kopplar WHERE-delen ihop de rader i tabellerna som är av intresse. Vi kan också lägga till andra villkor i WHERE-delen på samma sätt som tidigare. Till exempel kan vi ställa följande fråga:

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id AND Teachers.name = 'Kivinen';

Vi får då fram de kurser där Kivinen är lärare:

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Neuronnät           Kivinen

Förkortade tabellnamn

Vi kan göra frågor som involverar flera tabeller mer lättlästa genom att ge tabellerna alternativa kortare namn som vi sedan kan hänvisa till. Exempelvis frågan

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

kan förkortas:

SELECT
  C.name, T.name
FROM
  Courses AS C, Teachers AS T
WHERE
  C.teacher_id = T.id;

Ordet AS är inte heller nödvändigt att använda. Vi kan alltså förkorta frågan ytterligare:

SELECT
  C.name, T.name
FROM
  Courses C, Teachers T
WHERE
  C.teacher_id = T.id;

Att upprepa samma tabell

I en fråga som involverar flera tabeller kan samma tabell också förekomma flera gånger så länge varje tabell som upprepas får ett annat namn. Till exempel hämtar följande fråga alla sätt att välja ett par av två lärare:

SELECT A.name, B.name FROM Teachers A, Teachers B;

Frågan ger följande svar:

name        name      
----------  ----------
Kaila       Kaila     
Kaila       Luukkainen
Kaila       Kivinen   
Kaila       Laaksonen 
Luukkainen  Kaila     
Luukkainen  Luukkainen
Luukkainen  Kivinen   
Luukkainen  Laaksonen 
Kivinen     Kaila     
Kivinen     Luukkainen
Kivinen     Kivinen   
Kivinen     Laaksonen 
Laaksonen   Kaila     
Laaksonen   Luukkainen
Laaksonen   Kivinen   
Laaksonen   Laaksonen 

Länkningstabeller

Mellan tabeller finns det i allmänhet två typer av relationer:

En-till-många-relation: En rad i tabell A är kopplad till högst en rad i tabell B. En rad i tabell B kan vara kopplad till flera rader i tabell A.
Många-till-många-relation: En rad i tabell A kan vara kopplad till flera rader i tabell B. En rad i tabell B kan vara kopplad till flera rader i tabell A.

I fall 1 kan vi lägga till en kolumn i tabell A som refererar till tabell B precis som vi gjorde i exemplet i föregående avsnitt. Fall 2 är däremot mer komplicerat eftersom en enskild referens i någon av tabellraderna inte räcker. Lösningen är att skapa en tredje kopplingstabell (join table) som innehåller datan om referenserna.

Exempel

Låt oss som exempel titta på en situation där en webbutik har produkter och kunder, och varje kund har valt vissa produkter till sin köpkorg. I en viss köpkorg kan det finnas flera produkter, och en viss produkt kan finnas i flera köpkorgar.

Vi skapar en databas som består av tre tabeller: Products, Customers och Purchases. Kopplingstabellen Purchases anger vilka produkter som finns i varje köpkorg. Varje rad i kopplingstabellen motsvarar ett par i formen “Kunden id har produkten id i sin köpkorg”.

Vi antar att tabellerna ser ut på följande sätt:

id	name	price
1	rädisa	7
2	morot	5
3	rova	4
4	kålrot	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Detta betyder att Uolevis köpkorg innehåller en morot och en selleri. Maijas köpkorg innehåller en rädisa, en kålrot och en selleri. Aapelis köpkorg är tom.

Vi kan nu hämta kunderna och produkterna på följande sätt:

SELECT
  Customers.name, Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

Syftet med frågan är att hämta information från tabellerna Customers och Products som motsvarar raderna i tabellen Purchases. För att få fram relevanta svar kopplar vi ihop raderna med hjälp av två villkor. Frågan ger som svar följande resultattabell:

name    name    
------  --------
Uolevi  morot
Uolevi  selleri 
Maija   rädisa 
Maija   kålrot  
Maija   selleri 

Hur fungerar frågan?

Vi kan återigen undersöka hur frågan fungerar genom att hämta alla kolumner och ta bort villkoren:

SELECT * FROM Customers, Products, Purchases;

Resultattabellen från denna fråga innehåller alla möjliga kombinationer av kund, produkt och köp. Resultattabellen har 5 * 3 * 5 = 75 rader och börjar på följande sätt:

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  1   rädisa    7      1            2         
 Uolevi  1   rädisa    7      1            5         
 Uolevi  1   rädisa    7      2            1         
 Uolevi  1   rädisa    7      2            4         
 Uolevi  1   rädisa    7      2            5         
 Uolevi  2   morot     5      1            2         
 Uolevi  2   morot     5      1            5         
 Uolevi  2   morot     5      2            1         
 Uolevi  2   morot     5      2            4         
 Uolevi  2   morot     5      2            5         
 Uolevi  3   rova      4      1            2         
 Uolevi  3   rova      4      1            5         
 Uolevi  3   rova      4      2            1         
 Uolevi  3   rova      4      2            4         
 Uolevi  3   rova      4      2            5         
...

När vi sedan lägger till villkoren i frågan kan vi begränsa oss till de rader som vi är intresserade av:

SELECT
  *
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  2   morot     5      1            2         
 Uolevi  5   selleri   4      1            5         
 Maija   1   rädisa    7      2            1         
 Maija   4   kålrot    8      2            4         
 Maija   5   selleri   4      2            5         

När vi därtill specificerar de önskade kolumnerna får vi den slutgiltiga frågan:

SELECT
  Customers.name, Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id;

name    name    
------  --------
Uolevi  morot
Uolevi  selleri 
Maija   rädisa 
Maija   kålrot  
Maija   selleri 

Flera villkor i frågan

Vi kan lägga till flera villkor i frågan om vi vill få reda på mer om köpkorgarna. Till exempel hämtar följande fråga de produkter som finns i Maijas köpkorg:

SELECT
  Products.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id AND
  Customers.name = 'Maija';

name
----------
rädisa   
kålrot    
selleri   

Följande fråga visar vilka kunder som har selleri i sin köpkorg:

SELECT
  Customers.name
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND
  Products.id = Purchases.product_id AND
  Products.name = 'selleri';

name
----------
Uolevi    
Maija    

Sammanfattning av tabellerna

Vi kan också använda aggregeringsfunktioner och gruppering i frågor som omfattar flera tabeller. De behandlar resultattabellen på samma sätt som i frågor som använder endast en tabell.

Vi betraktar fortsättningsvis databasen som innehåller produkter, kunder och köp:

id	name	price
1	rädisa	7
2	morot	5
3	rova	4
4	kålrot	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Följande fråga sammanfattar varje kunds totala antal produkter i köpkorgen och det totala priset för produkterna.

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Frågan ger följande svar:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 

Uolevis köpkorg innehåller 2 produkter med ett totalt pris på 9. Maijas köpkorg innehåller 3 produkter med ett totalt pris på 19.

Hur fungerar frågan?

Frågan utgår från följande:

SELECT
  *
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id;

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
 Uolevi  2   morot     5      1            2         
 Uolevi  5   selleri   4      1            5         
 Maija   1   rädisa    7      2            1         
 Maija   4   kålrot    8      2            4         
 Maija   5   selleri   4      2            5         

Om vi i frågan lägger till grupperingen GROUP BY Customers.id delas raderna upp i två grupper baserat på kolumnen Customers.id:

Grupp 1:

id  name    id  name      price  customer_id  product_id
--  ------  --  --------  -----  -----------  ----------
1   Uolevi  2   morot     5      1            2         
1   Uolevi  5   selleri   4      1            5         

Grupp 2:

id  name   id  name     price  customer_id  product_id
--  -----  --  -------  -----  -----------  ----------
2   Maija  1   rädisa   7      2            1         
2   Maija  4   kålrot   8      2            4         
2   Maija  5   selleri  4      2            5         

För dessa grupper beräknas sedan antalet produkter med COUNT(Products.id) samt det totala priset för köpen med SUM(Products.price).

Observera att grupperingen sker enligt kolumnen Customers.id men att frågan hämtar kolumnen Customers.name. Detta är logiskt eftersom det är kolumnen Customers.id som definierar Customers.name. Medan denna fråga fungerar i exempelvis SQLite, kräver vissa andra databaser att alla kolumner som hämtas direkt också finns med i grupperingen. I sådana fall ska grupperingen skrivas som GROUP BY Customers.id, Customers.name.

Problem med en rad som saknas

Frågan ovan fungerar i och för sig bra, men något saknas:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 

Ett problem med frågan ovan är att svaret inte innehåller den tredje kunden i databasen, alltså Aapeli. Eftersom Aapelis köpkorg är tom kopplas hans rad inte ihop med någon annan rad och kommer därför inte med i resultattabellen.

Som tur är så har vi snart en lösning på detta problem.

JOIN-syntaxen

Hittills har vi hämtat information från tabellerna genom att lista dem i frågans FROM-del, vilket i regel fungerar bra. Ibland är det dock nödvändigt att använda JOIN-syntaxen, vilket är användbart när det ser ut som om viss information saknas i frågans svar.

Sätt att ställa frågan på

Nedan visas två sätt att ställa samma fråga på. Först ställs frågan på det redan bekanta sättet och sedan med hjälp av JOIN-syntaxen.

SELECT
  Courses.name, Teachers.name
FROM
  Courses, Teachers
WHERE
  Courses.teacher_id = Teachers.id;

SELECT
  Courses.name, Teachers.name
FROM
  Courses JOIN Teachers ON Courses.teacher_id = Teachers.id;

I JOIN-syntaxen står ordet JOIN mellan tabellnamnen. Villkoret som kopplar ihop tabellraderna anges i en separat ON-del.

I exemplet ovan tillför JOIN-syntaxen inget nytt, utan är bara ett alternativt sätt att ställa frågan på. Vi ska i nästa exempel se hur vi kan utvidga syntaxen så att den ger oss nya möjligheter i frågorna.

Exempel

Låt oss som exempel titta på en situation där databasen innehåller de bekanta tabellerna Courses och Teachers men där en kurs i tabellen Courses saknar en lärare.

id  name                teacher_id
--  ----------------    ----------
 Beräkningsmodeller  3         
 Datanätverk         1         
 Seminarium          1         
 PHP-programmering   NULL      
 Neuronnät           3         

I rad 4 har kolumnen teacher_id värdet NULL. Om vi kör någon av de tidigare frågorna uppstår problemet att rad 4 inte matchar någon rad i tabellen Teachers. Detta leder till att resultattabellen inte innehåller någon rad för kursen PHP-programmering:

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Datanätverk         Kaila  
Seminarium          Kaila  
Neuronnät           Kivinen

Lösningen på problemet är att använda LEFT JOIN-syntaxen, vilket innebär att om en rad i den vänstra tabellen inte kopplas till någon rad i den högra tabellen, skapas en rad i resultattabellen där varje kolumn som hör till den tabellen då får värdet NULL.

Vi kan här ställa frågan på följande sätt:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

Nu visas även kursen PHP-programmering utan lärare i resultattabellen.

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Datanätverk         Kaila  
Seminarium          Kaila  
PHP-programmering   NULL   
Neuronnät           Kivinen

Hur fungerar frågan?

Återigen är ett bra sätt att förstå hur frågan fungerar att förenkla den:

SELECT
  *
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

id  name                teacher_id  id    name   
--  ----------------    ----------  ----  -------
 Beräkningsmodeller  3           3     Kivinen
 Datanätverk         1           1     Kaila  
 Seminarium          1           1     Kaila  
 PHP-programmering   NULL        NULL  NULL   
 Neuronnät           3           3     Kivinen

Här ser man att eftersom rad 4 i den vänstra tabellen inte matchar någon rad i den högra tabellen, blir det en rad i resultattabellen för den vänstra raden där varje kolumn som hör till den högra tabellen har värdet NULL.

Mera om JOIN

Det finns fyra olika varianter av JOIN-frågan:

JOIN alltså INNER JOIN: fungerar som en vanlig fråga med två tabeller
LEFT JOIN: om en rad i den vänstra tabellen inte matchar någon rad i den högra tabellen, tas den ändå med som en egen rad.
RIGHT JOIN: om en rad i den högra tabellen inte matchar någon rad i den vänstra tabellen, tas den ändå med som en egen rad.
FULL JOIN: både från den vänstra och den högra tabellen tas de rader som inte matchar någon rad i den andra tabellen med som egna rader.

En begränsning i SQLite var länge att endast JOIN och LEFT JOIN var möjliga, men från och med version 3.39.0 är även RIGHT JOIN och FULL JOIN tillgängliga. I materialet för denna kurs använder vi LEFT JOIN-syntax när vi vill att resultatet även ska innehålla rader som inte kopplas ihop med rader i den andra tabellen.

ON vs. WHERE

Ordet ON är centralt i en LEFT JOIN-fråga eftersom det anger villkoret som gör att även de extra raderna i den vänstra tabellen tas med:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id;

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Datanätverk         Kaila  
Seminarium          Kaila  
PHP-programmering   NULL   
Neuronät            Kivinen

Om vi i stället använder ordet WHERE faller de extra raderna i den vänstra tabellen bort:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers
WHERE
  Courses.teacher_id = Teachers.id;

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Datanätverk         Kaila  
Seminarium          Kaila  
Neuronnät           Kivinen

Både ON och WHERE kan förekomma i samma fråga:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers ON Courses.teacher_id = Teachers.id
WHERE
  Courses.name <> 'Datanätverk';

I sådant fall sköter ON-delen sammanslagningen av tabellerna medan WHERE-delen begränsar resultaten ytterligare:

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Seminarium          Kaila  
PHP-Programmering   NULL   
Neuronnät           Kivinen

I det här fallet gör WHERE-delen att resultattabellen inte innehåller någon rad för kursen Datanätverk.

Om båda villkoren istället finns i ON-delen förändras resultatet:

SELECT
  Courses.name, Teachers.name
FROM
  Courses LEFT JOIN Teachers
          ON Courses.teacher_id = Teachers.id AND
             Courses.name <> 'Datanätverk';

name                name   
----------------    -------
Beräkningsmodeller  Kivinen
Datanätverk         NULL   
Seminarium          Kaila  
PHP-programmering   NULL   
Neuronät            Kivinen

I det här fallet blir raderna för kurserna Datanätverk och PHP-programmering kvar som extra rader i den vänstra tabellen eftersom de inte kan kopplas till någon rad i den högra tabellen enligt ON-villkoret.

Lösning för sammanfattningen

Vi återgår till det tidigare problemet där frågan som sammanfattar saknade data. I vår databas finns fortfarande följande tabeller:

id	name	price
1	rädisa	7
2	morot	5
3	rova	4
4	kålrot	8
5	selleri	4

id	name
1	Uolevi
2	Maija
3	Aapeli

customer_id	product_id
1	2
1	5
2	1
2	4
2	5

Vi sammanfattade köpkorgarna med följande fråga:

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers, Products, Purchases
WHERE
  Customers.id = Purchases.customer_id AND Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Problemet var att Aapeli inte fanns med i sammanfattningen:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 

Orsaken till problemet är att Aapelis köpkorg är tom. När frågan väljer kombinationer av rader från tabellerna finns det ingen rad som innehåller Aapeli. Lösningen på problemet är att använda LEFT JOIN-syntaxen på följande sätt:

SELECT
  Customers.name, COUNT(Products.id), SUM(Products.price)
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Nu finns också Aapeli med i sammanfattningen:

name    COUNT(Products.id)  SUM(Products.price)
------  ------------------  -------------------
Uolevi  2                   9                  
Maija   3                   19                 
Aapeli  0                   NULL               

Eftersom Aapelis köpkorg inte innehåller några produkter blir summan av produkternas priser NULL. Vi kan förbättra frågan ytterligare genom att använda IFNULL.

SELECT
  Customers.name, COUNT(Products.id), IFNULL(SUM(Products.price), 0)
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id
GROUP BY
  Customers.id;

Då blir NULL istället 0:

name    COUNT(Products.id)  IFNULL(SUM(Products.price), 0)
------  ------------------  ------------------------------
Uolevi  2                   9                             
Maija   3                   19                            
Aapeli  0                   0                             

Hur fungerar frågan?

När frågan innehåller flera LEFT JOIN-delar tolkas det som att tabellerna slås ihop från vänster till höger. I frågan ovan kan vi tänka oss att det första steget slår ihop tabellerna Customers och Purchases:

SELECT
  *
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id;

id  name    customer_id  product_id
--  ------  -----------  ----------
 Uolevi  1            2         
 Uolevi  1            5         
 Maija   2            1         
 Maija   2            4         
 Maija   2            5         
 Aapeli  NULL         NULL      

I det andra steget slås resultattabellen från det första steget ihop med tabellen Products:

SELECT
  *
FROM
  Customers LEFT JOIN Purchases ON Customers.id = Purchases.customer_id
            LEFT JOIN Products ON Products.id = Purchases.product_id;

id  name    customer_id  product_id  id    name      price
--  ------  -----------  ----------  ----  --------  -----
 Uolevi  1            2           2     morot     5    
 Uolevi  1            5           5     selleri   4    
 Maija   2            1           1     rädisa    7    
 Maija   2            4           4     kålrot    8    
 Maija   2            5           5     selleri   4    
 Aapeli  NULL         NULL        NULL  NULL      NULL 

I båda stegen inkluderas Aapeli i resultattabellen eftersom hans rad inte matchar någon rad i den högra tabellen.

4. Mer om SQL-språket

Datatyper och uttryck

I SQL-språket används datatyper och uttryck på samma sätt som i programmering. I tidigare kapitel har vi redan gått igenom en hel del exempel på olika SQL-kommandon. Låt oss nu studera SQL-språket ännu mer ingående.

Varje databashanterare implementerar datatyper och uttryck på sitt eget sätt, och det finns en hel del små skillnader i hur olika databashanterare fungerar. Det är därför klokt att kontrollera detaljerna i dokumentationen för den databashanterare som används.

Datatyper

När man definierar en tabell anges en datatyp för varje kolumn:

CREATE TABLE Movies (
  id INTEGER PRIMARY KEY,
  name TEXT,
  release_year INTEGER
);

Här är kolumnen name av typen TEXT (sträng) och kolumnen release_year av typen INTEGER (heltal). Dessa är de vanligaste datatyperna som finns tillgängliga under dessa namn i många databaser. Andra vanliga datatyper är till exempel TIMESTAMP (tidpunkt), REAL (flyttal) och BLOB (rådata).

TEXT vs. VARCHAR

Ett vanligt sätt att lagra en sträng i SQL är att använda typen VARCHAR där man anger den maximala längden på strängen inom parentes. Exempelvis betyder typen VARCHAR(10) att strängen kan innehålla högst 10 tecken.

Det här påminner om programmering från förr i tiden, då en sträng kunde representeras som en teckenarray med fast längd. Datatypen TEXT är smidigare att använda eftersom man inte behöver ange någon maximal längd på strängen.

Datatyper i SQLite

En ovanlig egenskap hos SQLite är att datatypen som anges vid tabellens definition bara är en riktlinje för vilken datatyp kolumnen bör ha. Vi kan dock bortse från denna riktlinje och till exempel lagra en sträng i en kolumn som egentligen är avsedd för heltal:

INSERT INTO Movies (name, release_year) VALUES ('Snövit', 'abc');

Dessutom kan datatypens namn vara vilken som helst sträng, även om SQLite inte har en sådan datatyp. Vi kan således till exempel definiera en kolumn där vi avser att lagra en tidpunkt:

CREATE TABLE Bookings (
  id INTEGER PRIMARY KEY,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  description TEXT
);

I SQLite finns inte TIMESTAMP som datatyp utan tidpunkter hanteras som strängar. Här anger dock kolumnens datatyp vilken typ av värde som är avsedd att lagras i kolumnen.

Uttryck

Ett uttryck är en del av ett SQL-kommando som har ett visst värde. Exempelvis i frågan

SELECT price FROM Products WHERE name = 'rädisa';

finns det fyra uttryck: price, name, 'rädisa' och name = 'rädisa'. Uttrycken price och name får sina värden från kolumnen i raden, uttrycket 'rädisa' är en strängkonstant och uttrycket name = 'rädisa' är booleskt.

Vi kan göra uttrycken mer komplexa på samma sätt som i programmering. Till exempel frågan

SELECT price * 5 FROM Products;

ger som svar varje produkts pris multiplicerat med fem. Frågan

SELECT name FROM Products WHERE price % 2 = 0;

hämtar de produkter vars pris är jämnt.

Ett bra sätt att testa SQL-uttryck är att interagera med databasen genom att ställa frågor som inte hämtar data från någon tabell utan enbart beräknar värdet av ett visst uttryck. Nedan följer ett exempel:

sqlite> SELECT 2 * (1 + 3);
8
sqlite> SELECT 'te' || 'st';
test
sqlite> SELECT 3 < 5;
1

Den första frågan beräknar värdet på uttrycket 2 * (1 + 3). Den andra frågan kombinerar strängarna 'te' och 'st' med hjälp av ||. Den tredje frågan bestämmer värdet av villkorsuttrycket 3 < 5. Här ser man att i SQLite representerar ett heltal ett booleskt (logiskt) värde där 1 är sant och 0 är falskt.

Mycket som rör SQL-uttryck är bekant från programmering:

räkneoperationer: +, -, *, /, %
jämförelser: =, <>, <, <=, >, >=
kombinering av villkor: AND, OR, NOT

Utöver dessa finns det i SQL även några mer speciella funktioner som kan vara nyttiga att känna till. Några av dessa är bland annat:

BETWEEN

Uttrycket x BETWEEN a AND b är sant om x är minst a och högst b. Exempelvis frågan

SELECT * FROM Products WHERE price BETWEEN 4 AND 6;

hämtar de produkter vars pris är minst 4 och högst 6. Samma fråga kan också skrivas på följande sätt:

SELECT * FROM Products WHERE price >= 4 AND price <= 6;

CASE

Strukturen CASE möjliggör skapandet av villkorsuttryck. CASE kan innehålla en eller flera WHEN-delar och en valfri ELSE-del. Exempelvis frågan

SELECT
  name,
  CASE WHEN price > 5 THEN 'dyr' ELSE 'billig' END
FROM
  Products;

hämtar varje produktnamn och information om produkten är dyr eller billig. Här räknas en produkt som dyr om priset är över 5. Om priset är 5 eller under 5 så räknas produkten som billig.

Uttrycket x IN (...) är sant om x motsvarar något av de angivna värdena. Till exempel frågan

SELECT
  SUM(price)
FROM
  Products
WHERE
  name IN ('kålrot', 'rova', 'selleri');

hämtar det totala priset för kålroten, rovan och sellerin.

LIKE

Uttrycket s LIKE p är sant om strängen s överensstämmer med beskrivningen p. I beskrivningen kan man använda specialtecken. Tecknet _ betyder vilket enstaka tecken som helst och tecknet % betyder ett godtyckligt antal tecken. Till exempel frågan

SELECT * FROM Products WHERE name LIKE '%rot%';

hämtar de produkter i vilkas namn strängen “rot” förekommer (morot och kålrot)

Funktioner

Uttrycken kan innehålla funktioner på samma sätt som i programmering. Nedan är några exempel på funktioner i SQLite:

ABS(x): absolutbeloppet av x
LENGTH(s): längden på strängen s
LOWER(s): strängen s skriven med små bokstäver
MAX(x, y): det större av talen x och y
MIN(x, y): det mindre av talen x och y
RANDOM(): ett slumpmässigt tal
ROUND(x, d): talet x avrundat med d desimalers noggrannhet
SUBSTR(s, a, b): b antal tecken från strängen s med start från position a
UPPER(s): strängen s skriven med stora bokstäver

Följande fråga hämtar de produkter vars namn innehåller sex bokstäver (rädisa och kålrot)

SELECT * FROM Products WHERE LENGTH(name) = 6;

Följande fråga grupperar produkterna efter första bokstaven och visar antalet produkter som börjar med respektive bokstav.

SELECT
  SUBSTR(name, 1, 1), COUNT(*)
FROM
  Products
GROUP BY
  SUBSTR(name, 1, 1);

Följande fråga returnerar raderna i slumpmässig ordning eftersom ordningen inte baseras på innehållet i någon kolumn utan på ett slumpmässigt värde.

SELECT * FROM Products ORDER BY RANDOM();

ORDER BY och uttryck

Man kunde anta att i frågan

SELECT * FROM Products ORDER BY 1;

sorteras raderna enligt uttrycket 1, och eftersom uttryckets värde är 1 på varje rad så innebär detta ingen särskild ordning. Detta är dock inte fallet. 1 sorterar nämligen raderna efter den första kolumnen, 2 efter den andra kolumnen och så vidare. Detta är alltså ett alternativt sätt att ange vilken kolumn sorteringen ska baseras på.

Om uttrycket i ORDER BY-delen däremot är något annat än ett enstaka tal (till exempel RANDOM()), ordnas raderna enligt uttrycket i fråga.

NULL-värden

NULL är ett speciellt värde som anger att en kolumn i tabellen saknar information eller att en del av en fråga inte returnerade något värde. NULL kan vara praktiskt i vissa situationer, men kan också orsaka utmaningar.

Som standard visar SQLite-tolken värdet NULL som tomt:

sqlite> SELECT NULL;

NULL-värdet kan visas med tolken genom kommandot .nullvalue:

sqlite> .nullvalue NULL
sqlite> SELECT NULL;
NULL

Observera att NULL inte är talet 0. Om NULL ingår i en beräkning blir hela resultatet NULL.

sqlite> SELECT 5 + NULL;
NULL
sqlite> SELECT 2 * NULL + 1;
NULL

En vanlig jämförelse ger inte något svar om NULL ingår i jämförelsen:

sqlite> SELECT 5 = NULL;
NULL
sqlite> SELECT 5 <> NULL;
NULL

Svaret kan vara en aning oväntat. För uttrycken a och b gäller vanligtvis antingen a = b eller a <> b. Vi kan kontrollera om ett uttryck har värdet NULL med syntaxen IS NULL:

sqlite> SELECT 5 IS NULL;
0
sqlite> SELECT NULL IS NULL;
1

Information saknas i kolumnen

Ett av syftena med värdet NULL är att ange att en kolumn saknar information. Till exempel i följande tabell Movies saknas utgivningsåret för filmen Dumbo:

id  name       release_year
--  ---------  ------------
 Snövit     1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      NULL        
 Bambi      1942        

Om vi först hämtar filmer från år 1940 och sedan alla filmer från andra år, får vi följande resultat:

SELECT * FROM Movies WHERE release_year = 1940;

id  name       release_year
--  ---------  ------------
2   Fantasia   1940        
3   Pinocchio  1940        

SELECT * FROM Movies WHERE release_year <> 1940;

id  name     release_year
--  -------  ------------
1   Snövit   1937        
5   Bambi    1942        

Observera att eftersom filmen Dumbo saknar utgivningsår så finns den inte med i något av svaren. Vi kan hämta de filmer som saknar utgivningsår med följande fråga:

SELECT * FROM Movies WHERE release_year IS NULL;

id  name   release_year
--  -----  ------------
4   Dumbo  NULL        

Ett NULL-värde i en aggregeringsfunktion

När en aggregeringsfunktion innehåller ett uttryck (till exempel ett kolumnvärde) räknas raden inte med om uttryckets värde är NULL. Som exempel kan vi titta på följande tabell Employees:

id  name      company  salary
--  --------  -------  ------
 Anna      Google   8000  
 Liisa     Google   7500  
 Kaaleppi  Amazon   NULL  
 Uolevi    Amazon   NULL  
 Maija     Google   9500  

I tabellen har Googles anställda ett angivet lönebelopp medan Amazons anställda inte har det. Aggregeringsfunktionen COUNT(salary) räknar endast med de rader där lönen är angiven:

SELECT COUNT(salary) FROM Employees WHERE company = 'Google';

COUNT(salary)
-------------
3

SELECT COUNT(salary) FROM Employees WHERE company = 'Amazon';

COUNT(salary)
-------------
0

När vi beräknar summan av lönerna med aggregeringsfunktionen SUM(salary) får vi följande resultat:

SELECT SUM(salary) FROM Employees WHERE company = 'Google';

SUM(salary)
-----------
25000      

SELECT SUM(salary) FROM Employees WHERE company = 'Amazon';

SUM(salary)
-----------
NULL

Svaret kan vara en aning oväntat eftersom man skulle kunna förvänta sig att en tom summa blir 0 istället för NULL.

Ändra NULL-värdet

Funktionen IFNULL(a, b) returnerar värdet a om a inte är NULL. Om a är NULL så returneras istället värdet b:

sqlite> SELECT IFNULL(5, 0);
IFNULL(5, 0)
------------
5          
sqlite> SELECT IFNULL(NULL, 0);
IFNULL(NULL, 0)
---------------
0

Ovanstående exempel är ett vanligt sätt att använda IFNULL(a, b) på. Funktionen omvandlar ett eventuellt NULL-värde till noll när den andra parametern (b) i IFNULL(a, b) är 0. Detta är användbart till exempel i LEFT JOIN-frågor tillsammans med SUM-funktionen.

Funktionen IFNULL är inte en standardfunktion i SQL och fungerar därmed inte i alla databashanterare. En standardfunktion i SQL är COALESCE(...), till vilken man ger en lista med värden. Funktionen returnerar det första värdet i listan som inte är NULL, eller NULL om alla värden i listan är NULL. Om funktionen endast har två parametrar fungerar den på samma sätt som IFNULL.

sqlite> SELECT COALESCE(1, 2, 3);
COALESCE(1, 2, 3)
-----------------
1              
sqlite> SELECT COALESCE(NULL, 2, 3);
COALESCE(NULL, 2, 3)
--------------------
2                 
sqlite> SELECT COALESCE(NULL, NULL, 3);
COALESCE(NULL, NULL, 3)
-----------------------
3                    
sqlite> SELECT COALESCE(NULL, NULL, NULL);
COALESCE(NULL, NULL, NULL)
--------------------------
NULL

Underfrågor

En underfråga är ett uttryck som ingår som en del av ett SQL-kommando och vars värde bestäms av en viss fråga. Vi kan formulera underfrågor på samma sätt som vanliga frågor och använda dem för att göra sökningar som annars vore svåra att genomföra.

Exempel

Låt oss betrakta en situation där databasen innehåller spelares resultat i tabellen Results. Vi antar att tabellen ser ut på följande sätt:

id  name      score
--  --------  -----
 Uolevi    120  
 Maija     80   
 Liisa     120  
 Aapeli    45   
 Kaaleppi  115  

Vi vill nu ta reda på vilka spelare som har uppnått högsta poäng. Från tabellen ovan ser vi att det är Uolevi och Liisa som upnått högsta poäng. Vi kan utföra följande underfråga:

SELECT
  name, score
FROM
  Results
WHERE
  score = (SELECT MAX(score) FROM Results);

Frågan ger som svar:

name    score
------  -----
Uolevi  120  
Liisa   120  

I frågan ovan är underfrågan SELECT MAX(score) FROM Results. Underfrågan ger det bästa resultatet i tabellen som i det här fallet är 120. Observera att underfrågan måste skrivas inom parenteser för att den inte ska blandas ihop med huvudfrågan.

Skapa en underfråga

En underfråga kan förekomma nästan var som helst i frågan. En underfråga kan, beroende på situation, returnera ett enstaka värde, en lista med värden eller en hel tabell.

En underfråga i en kolumn

I följande fråga skapas med hjälp av en underfråga en tredje kolumn som visar spelarens poängskillnad från rekordresultatet:

SELECT
  name, score, (SELECT MAX(score) FROM Results) - score
FROM
  Results;

name      score  (SELECT MAX(score) FROM Results) - score
--------  -----  ----------------------------------------
Uolevi    120    0                                     
Maija     80     40                                    
Liisa     120    0                                     
Aapeli    45     75                                    
Kaaleppi  115    5                                     

Uttrycket som skapar kolumnen i resultattabellen är rätt invecklat. Resultattabellen kan göras tydligare genom att ge kolumnen ett nytt namn.

SELECT
  name,
  score,
  (SELECT MAX(score) FROM Results) - score AS difference
FROM
  Results;

name      score  difference
--------  -----  ----------
Uolevi    120    0         
Maija     80     40        
Liisa     120    0         
Aapeli    45     75        
Kaaleppi  115    5         

Underfråga som en tabell

I följande fråga skapar underfrågan en tabell med de tre bästa resultaten. Summan av dessa resultat (120 + 120 + 115) beräknas i huvudfrågan.

SELECT
  SUM(score)
FROM
  (SELECT * FROM Results ORDER BY score DESC LIMIT 3);

SUM(score)
----------
355

Här begränsar LIMIT resultattabellen så att den endast innehåller de tre första raderna.

Observera att vi skulle erhålla fel svar utan underfrågan:

SELECT SUM(score) FROM Results ORDER BY score DESC LIMIT 3;

SUM(score)
----------
480     

I denna resultattabell finns endast en rad med summan av alla resultat (480). LIMIT 3 i slutet av frågan påverkar därför inte alls resultatet.

Underfråga som en lista

Följande fråga hämtar spelare vars resultat hör till de tre bästa. Underfrågan returnerar resultatet som en lista för att användas i IN-uttrycket.

SELECT
  name
FROM
  Results
WHERE
  score IN (SELECT score FROM Results ORDER BY score DESC LIMIT 3);

name
----------
Uolevi    
Liisa     
Kaaleppi  

En underfråga som beror på huvudfrågan

Det är också möjligt att skapa en underfråga så att den beror på den rad som behandlas i huvudfrågan. Detta är fallet i följande fråga:

SELECT
  name,
  score,
  (SELECT COUNT(*) FROM Results WHERE score > R.score) AS better_count
FROM
  Results R;

Denna fråga räknar för varje spelare hur många spelares resultat som är bättre än spelarens eget resultat. Till exempel är svaret 3 för Maija, eftersom resultaten för Uolevi, Liisa och Kaaleppi är bättre. Frågan ger som svar följande:

name      score  better_count
--------  -----  ------------
Uolevi    120    0           
Maija     80     3           
Liisa     120    0           
Aapeli    45     4           
Kaaleppi  115    2           

Eftersom tabellen Results förekommer i två roller i underfrågan har tabellen i huvudfrågan getts aliaset R. Detta gör det tydligt i underfrågan att man vill räkna de rader vars resultat är bättre än resultatet på den rad som för tillfället behandlas i huvudfrågan.

Nedan är ett till exempel på en underfråga som beror på huvudfrågan:

SELECT
  name
FROM
  Results R
WHERE
  (SELECT COUNT(*) FROM Results WHERE score < R.score) >= 1;

Frågan söker de spelare som har ett bättre resultat än någon annan spelare. Underfrågan räknar hur många spelare som har ett sämre resultat och villkoret i huvudfrågan är att underfrågans resultat är minst ett. Det slutgiltiga svaret på frågan blir:

name
----------
Uolevi    
Maija     
Liisa     
Kaaleppi  

Frågan ger som svar alla spelare förutom Aapeli som har det sämsta resultatet.

I SQL finns också nyckelordet EXISTS som anger huruvida en underfråga returnerar åtminstone en rad. Med hjälp av detta nyckelord kan den föregående frågan skrivas tydligare:

SELECT
  name
FROM
  Results R
WHERE
  EXISTS (SELECT * FROM Results WHERE score < R.score);

När ska man använda underfrågor?

Rätt ofta är en underfråga ett alternativt sätt att ställa en fråga som också kunde ställas på något annat sätt. Till exempel hämtar båda frågorna nedan namnen på de produkter som finns i köpkorgen för kund 1:

SELECT
  Products.name
FROM
  Products, Purchases
WHERE
  Products.id = Purchases.product_id AND Purchases.customer_id = 1;

SELECT
  name
FROM
  Products
WHERE
  id IN (SELECT product_id FROM Purchases WHERE customer_id = 1);

Den första frågan är en typisk fråga som använder två tabeller medan den andra frågan väljer ut produkterna med hjälp av en underfråga. Vilken av dessa två frågor är bättre?

Den första frågan är bättre eftersom detta är det avsedda sättet att hämta information ur tabeller i SQL med hjälp av referenser. Den andra frågan fungerar visserligen, men följer inte standardpraxis och databashanteraren kan nödvändigtvis inte heller utföra den lika effektivt som den första frågan.

En underfråga bör endast användas när det verkligen behövs. Om frågan enkelt kan göras med en fråga som involverar flera tabeller är det vanligtvis en bättre lösning.

Fler tekniker

Detta avsnitt innehåller fler exempel på möjligheter i SQL. Dessa tekniker är användbara för att lösa vissa av de mer avancerade uppgifterna i SQL Trainer.

Kumulativ summa

En användbar färdighet i SQL är att kunna beräkna en kumulativ summa, det vill säga summan av kolumnvärden fram till varje rad. Låt oss titta på följande exempel med tabellen Items:

id  value
--  -----
1   200  
2   100  
3   400  
4   100  

Vi kan beräkna den kumulativa summan med en fråga som involverar två tabeller på följande sätt:

SELECT
  A.id, SUM(B.value)
FROM
  Items A, Items B
WHERE
  B.id <= A.id
GROUP BY
  A.id;

id  SUM(B.value)
--  ------------
1   200         
2   300         
3   700         
4   800         

Idén är här att summan beräknas för en rad i tabellen A och från tabell B hämtas alla rader vars id är mindre än eller lika med radens id i tabell A. De önskade summorna kan sedan beräknas med SUM-funktionen efter grupperingen.

En liknande teknik kan användas i andra situationer när vi vill beräkna ett resultat som på något sätt beror på alla “mindre” rader i tabellen.

Nästlade frågor

Låt oss betrakta en situation där vi vill ta reda på det största antalet filmer som har släppts under samma år. Till exempel i följande tabell Movies är det önskade resultatet 2 eftersom två filmer släpptes år 1940.

id  name       release_year
--  ---------  ------------
 Snövit     1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      1941        
 Bambi      1942        

Detta kan verka lite knepigt eftersom vi skulle behöva göra nästlade frågor med COUNT som räknar hur många filmer som släppts under samma år och MAX som hämtar det största värdet. SQL tillåter dock inte en fråga i stil med SELECT MAX(COUNT(release_year)).

Vi kan här utgå från en fråga som grupperar filmerna efter år och hämtar antalet filmer i varje grupp:

SELECT COUNT(*) FROM Movies GROUP BY release_year;

COUNT(*)  
--------
1         
2         
1         
1       

Nu ska vi ännu hämta det största värdet, vilket kan göras med en underfråga. Det är här praktiskt att forma frågan så att underfrågans resultat är i FROM-delen i huvudfrågan. Underfrågan skapar då en tabell som huvudfrågan sedan hämtar information ifrån:

SELECT MAX(year_count) FROM (
  SELECT COUNT(*) year_count FROM Movies GROUP BY release_year
);

MAX(year_count)
---------------
2       

Kan uppgiften lösas utan en underfråga? Ja. Vi kan nämligen sortera resultaten från störst till minst och välja den första raden i resultattabellen:

SELECT COUNT(*) AS year_count FROM Movies GROUP BY release_year
ORDER BY year_count DESC LIMIT 1;

year_count
----------
2          

Placeringar

Låt oss titta på tabellen Results som innehåller spelare och deras resultat:

id  name      score
--  --------  -----
 Aapeli    45   
 Kaaleppi  115  
 Liisa     120  
 Maija     80   
 Uolevi    120  

Målet är att hämta raderna i resultatordning från störst till minst och dessutom ange varje rads placering. Ett sätt att göra detta är att skapa en underfråga som räknar hur många rader som har ett bättre resultat, varpå placeringen blir ett högre än underfrågans resultat:

SELECT
  (SELECT COUNT(*) FROM Results WHERE score > R.score) + 1 AS place,
  name, score
FROM
  Results R
ORDER BY
  score DESC, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Enligt samma princip kan placeringar beräknas så att varje spelare får en unik plats och vid lika resultat avgör alfabetisk ordning placeringen:

SELECT
  (SELECT COUNT(*) FROM Results WHERE score > R.score OR
    (score = R.score AND name < R.name)) + 1 AS place,
  name, score
FROM
  Results R
ORDER BY
  score DESC, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Ett alternativt sätt att beräkna placeringar är med en fönsterfunktion, förutsatt att databashanteraren som används tillåter det. Till exempel kan man i nyare versioner av SQLite använda fönsterfunktionen RANK för att beräkna motsvarande placeringar som i de tidigare exemplen.

SELECT
  RANK() OVER (ORDER BY score DESC) place, name, score
FROM
  Results
ORDER BY
  place, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

SELECT
  RANK() OVER (ORDER BY score DESC, name) place, name, score
FROM
  Results
ORDER BY
  place, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Jämförelse av listor

Låt oss betrakta tabellen Lists som består av innehållet i olika listor. Till exempel innehåller lista 1 talen [2, 4, 5], lista 2 talen [3, 5] och lista 3 talen [2, 4, 5]:

id  list_id  value
--  -------  -----
 1        2    
 1        4    
 1        5    
 2        3    
 2        5    
 3        2    
 3        4    
 3        5    

Följande fråga räknar för varje par av listor hur många gemensamma tal de har:

SELECT
  A.list_id, B.list_id, COUNT(*)
FROM
  Lists A, Lists B
WHERE
  A.value = B.value
GROUP BY
  A.list_id, B.list_id;

list_id  list_id  COUNT(*)
-------  -------  --------
      1        3       
      2        1       
      3        3       
      1        1       
      2        2       
      3        1       
      1        3       
      2        1       
      3        3       

Här framgår det att till exempel listorna 1 och 2 har ett gemensamt tal (5) och listorna 1 och 3 har tre gemensamma tal (2, 4, 5). En sådan här fråga kan man bygga vidare på och till exempel jämföra om två listor har exakt samma innehåll. Detta är fallet när listorna har lika många tal och antalet gemensamma tal är lika stort som antalet tal i varje enskild lista.

5. Databaser i programmering

Testdatabas

I exemplen i det här kapitlet och i kursuppgifterna används en testdatabas som innehåller uppgifter om cykelturer med stadscyklarna under år 2024 i Helsingfors och Esbo. Databasen bygger på offentlig data som publicerats av HRT. Datan har bearbetats till en SQL-databas för denna kurs.

Du kan ladda ner databasen här: bikes_2024.zip

Databasen innehåller två tabeller:

CREATE TABLE Stations (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE Trips (
    id INTEGER PRIMARY KEY,
    start_time TEXT,
    end_time TEXT,
    start_station_id INTEGER REFERENCES Stations,
    end_station_id INTEGER REFERENCES Stations,
    distance INTEGER,
    duration INTEGER
);

Tabellen Stations innehåller uppgifter om stadscykelstationerna. Tabellen har två kolumner: id (ID-nummer) och name (stationens namn).

Tabellen Trips innehåller uppgifter om utförda cykelturer. Tabellen har följande kolumner:

id: ID-nummer
start_time: cykelturens starttid (i formen yyyy-mm-ddThh:mm:ss)
end_time: cykelturens sluttid (i formen yyyy-mm-ddThh:mm:ss)
start_station_id: ID-nummer för startstationen
end_station_id: ID-nummer för slutstationen
distance: cykelturens längd (i meter)
duration: cykelturens varaktighet (i sekunder)

Vi kan utforska innehållet i databasen via SQLite-tolken på följande sätt:

$ sqlite3 bikes_2024.db
sqlite> .tables
Stations  Trips
sqlite> SELECT COUNT(*) FROM Stations;
458
sqlite> SELECT COUNT(*) FROM Trips;
2585668
sqlite> SELECT * FROM Stations LIMIT 10;
1|Kaivopuisto
2|Laivasillankatu
3|Kapteeninpuistikko
4|Viiskulma
5|Sepänkatu
6|Hietalahdentori
7|Designmuseo
8|Vanha kirkkopuisto
9|Erottajan aukio
10|Kasarmitori
sqlite> SELECT * FROM Trips WHERE id = 100;
100|2024-04-01T10:05:03|2024-04-01T10:26:19|119|259|4627|1271
sqlite> SELECT name FROM Stations WHERE id = 119;
Gebhardinaukio
sqlite> SELECT name FROM Stations WHERE id = 259;
Petter Wetterin tie
sqlite> .quit

Vi ser att databasen innehåller 458 stationer och 2 585 668 cykelturer. Till exempel började cykelturen med ID-nummer 100 vid Gebhardsplatsen (Gebhardinaukio) och slutade vid Petter Wetters väg (Petter Wetterin tie). Turen var 4,6 kilometer lång och tog drygt 21 minuter.

Låt oss se hur vi kan hantera denna databas med Python och R.

SQLite i Python

I Pythons standardbibliotek finns modulen sqlite3, som gör det möjligt att använda en SQLite-databas. Följande kod skapar en anslutning till databasen och hämtar information från tabellen Stations med SQL-frågor:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

result = db.execute("SELECT id, name FROM Stations WHERE id = 5")
print(result.fetchone())

result = db.execute("SELECT id, name FROM Stations ORDER BY id LIMIT 10")
print(result.fetchall())

Koden ger följande resultat:

(5, 'Sepänkatu')
[(1, 'Kaivopuisto'), (2, 'Laivasillankatu'), (3, 'Kapteeninpuistikko'), (4, 'Viiskulma'), (5, 'Sepänkatu'), (6, 'Hietalahdentori'), (7, 'Designmuseo'), (8, 'Vanha kirkkopuisto'), (9, 'Erottajan aukio'), (10, 'Kasarmitori')]

Här är db ett databasobjekt genom vilket SQL-kommandon kan köras med metoden execute. I den här koden körs två SELECT-kommandon.

Det första SELECT-kommandot hämtar från tabellen Stations raden med ID-numret 5. Eftersom frågan returnerar en rad används metoden fetchone, som returnerar raden som en tupel (eng. tuple).

Det andra SELECT-kommandot hämtar de tio första raderna från tabellen Stations. Här används metoden fetchall som returnerar en lista där varje tupel motsvarar en rad i resultattabellen.

Var finns databasfilen?

Ett vanligt problem i en kod som använder en databas är att databasfilen finns på en annan plats på datorn än vad koden förväntar sig. Om du inte får koden ovan att fungera är detta troligtvis orsaken.

Om den databasfil som koden hänvisar till inte finns, skapar koden en ny, tom databasfil. Eftersom det i denna databas inte finns någon tabell Stations kommer ett försök att hämta information från den tabellen att misslyckas.

Exempel: Stationens namn

Följande kod frågar användaren efter ett stations ID-nummer och hämtar därefter stationens namn från databasen.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

station_id = input("Stationens ID-nummmer: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])
station_name = result.fetchone()[0]
print("Stationens namn:", station_name)

Resultatet kan se ut på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

I frågan används parametern ?, vars värde blir ID-nummret i variabeln station_id. Koden visar hur värden för parametrar anges som en lista när metoden execute används.

I det här fallet returnerar fetchone en tupel med ett element. Innehållet i detta element hämtas med hjälp av syntaxen [], där 0 anger att det första elementet i tupeln ska hämtas.

En nackdel med koden är att den inte tar hänsyn till situationer där det i databasen inte finns någon station med det angivna ID-numret.

Stationens ID-nummer: 666
Traceback (most recent call last):
  File "test.py", line 7, in <module>
    station_name = result.fetchone()[0]
                   ~~~~~~~~~~~~~~~~~^^^
TypeError: 'NoneType' object is not subscriptable

I det här fallet returnerar metoden fetchone värdet None, vilket gör att det inte går att hämta det första elementet i tupeln. För att lösa detta kan man använda en try/except-struktur på följande sätt:

import sqlite3

db = sqlite3.connect("bikes_2024.db")

station_id = input("Stationens ID-nummer: ")
result = db.execute("SELECT name FROM Stations WHERE id = ?", [station_id])

try:
    station_name = result.fetchone()[0]
    print("Stationens namn:", station_name)
except TypeError:
    print("Stationen hittades inte")

Koden meddelar nu att stationen inte hittades:

Stationens ID-nummer: 666
Stationen hittades inte

Nedan utförs databassökningen via funktionen find_station_name. Funktionen returnerar stationens namn eller strängen -- om stationen inte hittas.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

def find_station_name(station_id):
    result = db.execute("SELECT name FROM Stations WHERE id = ?",
                        [station_id])
    result_row = result.fetchone()
    return result_row[0] if result_row else "--"

station_id = input("Stationens ID-nummer: ")

station_name = find_station_name(station_id)
print("Stationens namn:", station_name)

Koden fungerar på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

Stationens ID-nummer: 666
Stationens namn: --

Exempel: Slutstationer

Följande kod frågar användaren efter en startstation och ett datum, och söker sedan upp alla slutstationer för det angivna datumet dit resor som började vid startstationen avslutades.

import sqlite3

db = sqlite3.connect("bikes_2024.db")

def find_destinations(station_name, date):
    sql = """SELECT
                 DISTINCT B.name
             FROM
                 Stations AS A, Stations AS B, Trips AS T
             WHERE
                 T.start_station_id = A.id AND
                 T.end_station_id = B.id AND
                 A.name = ? AND
                 T.start_time LIKE ?
             ORDER BY
                 B.name"""
    result = db.execute(sql, [station_name, date + "%"])
    return [row[0] for row in result.fetchall()]

station_name = input("Stationens namn: ")
date = input("Datum: ")

destinations = find_destinations(station_name, date)
print("Antal slutstationer:", len(destinations))
for destination in destinations:
    print(destination)

Nedan följer ett exempel på hur koden fungerar:

Stationens namn: Syystie
Datum: 2024-05-16
Antal slutstationer: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Här är SQL-frågan rätt komplicerad, vilket är anledningen till att den delas upp på flera rader i koden. I detta fall är det praktiskt att använda Pythons syntax """ som gör det möjligt att definiera en sträng med flera rader.

Till kommandot ges två parametrar som placeras på tecknen ? i den ordning de anges i listan. Listans första element ersätter det första tecknet ? och det andra elementet ersätter det andra tecknet ?. Eftersom parametrarna är strängar placeras de inom '-tecken i SQL.

Cykelturer som börjar på ett visst datum kan hämtas med hjälp av syntaxen LIKE genom att begränsa sökningen så att värdet i kolumnen start_time börjar med det angivna datumet. Tecknet % anger att vilket som helst klockslag kan följa efter datumet.

SQLite i R

I R används SQLite-databaser vanligtvis med paketet RSQLite som kan installeras med kommandot install.packages("RSQLite"). Följande kod skapar en anslutning till databasen och hämtar information från tabellen Stations med SQL-frågor:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

result <- dbGetQuery(db, "SELECT id, name FROM Stations WHERE id = 5")
print(result)

result <- dbGetQuery(db, "SELECT id, name FROM Stations ORDER BY id LIMIT 10")
print(result)

Koden ger följande resultat:

  id      name
5 Sepänkatu

   id               name
 1        Kaivopuisto
 2    Laivasillankatu
 3 Kapteeninpuistikko
 4          Viiskulma
 5          Sepänkatu
 6    Hietalahdentori
 7        Designmuseo
 8 Vanha kirkkopuisto
 9    Erottajan aukio
10        Kasarmitori

Här är db ett databasobjekt genom vilket SQL-kommandon kan köras med funktionen dbGetQuery. I den här koden körs två SELECT-kommandon.

Det första SELECT-kommandot hämtar raden med ID-numret 5 från tabellen Stations. Det andra SELECT-kommandot hämtar de tio första raderna från tabellen Stations.

Var finns databasfilen?

I RStudio kan katalogen anges via Session -> Set Working Directory -> To Source File Location. Därefter bör koden fungera om databasfilen finns i samma katalog som kodfilen.

Exempel: Stationens namn

Följande kod frågar användaren efter ett stations ID-nummer och hämtar därefter stationens namn från databasen.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

station_id <- readline(prompt = "Stationens ID-nummer: ")
result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

station_name <- result$name[1]
cat("Stationens namn:", station_name, "\n")

Resultatet kan se ut på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

I frågan används parametern ?, vars värde hämtas från variabeln station_id. Koden visar hur värden för parametrar anges som en lista när funktionen dbGetQuery används.

Syntaxen result$name[1] betyder att värdet i kolumnen name hämtas från rad 1 i resultattabellen, vilket i det här fallet är den enda raden.

Om det inte finns någon station med det angivna ID-numret i databasen ger koden följande resultat (NA betyder att värdet saknas):

stationens ID-nummer: 666
Stationens namn: NA

Vi kan vid behov ge ett tydligare meddelande som svar genom att till exempel kontrollera antalet rader i resultatet:

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

station_id <- readline(prompt = "Stationens ID-nummer: ")

result <- dbGetQuery(db,
                     "SELECT name FROM Stations WHERE id = ?",
                     params = list(station_id))

if (nrow(result) == 1) {
  station_name <- result$name[1]
  cat("Stationens namn:", station_name, "\n")
} else {
  cat("Stationen hittades inte\n")
}

Nu fås ett tydligare svar där det meddelas att stationen inte kunde hittas:

Stationens ID-nummer: 666
Stationen hittades inte

Nedan utförs databassökningen via funktionen find_station_name. Funktionen returnerar stationens namn eller strängen -- om stationen inte hittas.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

find_station_name <- function(station_id) {
  result <- dbGetQuery(db,
                       "SELECT name FROM Stations WHERE id = ?",
                       params = list(station_id))
  if (nrow(result) == 1) result$name[1] else "--"
}

station_id <- readline(prompt = "Stationens ID-nummer: ")
station_name <- find_station_name(station_id)
cat("Stationens namn:", station_name, "\n")

Koden fungerar på följande sätt:

Stationens ID-nummer: 42
Stationens namn: Haapaniemenkatu

Stationens ID-nummer: 666
Stationens namn: --

Exempel: Slutstationer

Följande kod frågar användaren efter en startstation och ett datum, och söker sedan upp alla slutstationer för det angivna datumet dit cykelturer som började vid startstationen avslutades.

library(RSQLite)

db <- dbConnect(SQLite(), "bikes_2024.db")

find_destinations <- function(station_name, date) {
  sql <- "
    SELECT DISTINCT B.name
    FROM Stations AS A, Stations AS B, Trips AS T
    WHERE
      T.start_station_id = A.id AND
      T.end_station_id = B.id AND
      A.name = ? AND
      T.start_time LIKE ?
    ORDER BY B.name
  "
  res <- dbGetQuery(db, sql, params = list(station_name, paste0(date, "%")))
  res$name
}

station_name <- readline(prompt = "Stationens namn: ")
date <- readline(prompt = "Datum: ")

destinations <- find_destinations(station_name, date)
cat("Antal slutstationer:", length(destinations), "\n")
for (destination in destinations) {
  cat(destination, "\n")
}

Nedan följer ett exempel på hur koden fungerar:

Stationens namn: Syystie
Datum: 2024-05-16
Antal slutstationer: 5
A.I. Virtasen aukio
Ala-Malmin tori
Huhtakuja
Pukinmäen asema
Vanha Tapanilantie

Cykelturer som börjar på ett visst datum kan hittas med hjälp av LIKE-syntaxen genom att begränsa sökningen så att värdet i kolumnen start_time börjar med det angivna datumet. Funktionen paste0 lägger till tecknet % efter datumet, vilket anger att vilket som helst klockslag kan följa efter datumet.

Vad gör man var?

I databasen och i koden kan man ofta göra liknande saker. Nedan presenteras två olika sätt att söka fram den längsta cykelturen ur databasen:

result = db.execute("SELECT MAX(distance) FROM Trips")
max_distance = result.fetchone()[0]
print(max_distance)

result = db.execute("SELECT distance FROM Trips")
max_distance = max(result.fetchall())[0]
print(max_distance)

I den första lösningen hämtar man den längsta cykelturen direkt i databasen med SQL-funktionen MAX. I den andra lösningen samlar man längden på alla cykelturer från databasen till en lista och söker sedan den längsta turen i koden med Pythons max-funktion.

Av de två metoderna är den första klart bättre. Det lönar sig inte att hämta onödig information och behandla den i koden när samma sak kan göras enkelt direkt i databasen.

Det lönar sig särskilt att undvika situationer där man i onödan kör flera SQL-kommandon när ett enda kommando skulle räcka. Nedan finns ett dåligt exempel på hur man hämtar antalet cykelturer som startat från varje station ur databasen.

result = db.execute("SELECT id, name FROM Stations")
stations = result.fetchall()

for station_id, station_name in stations:
    result = db.execute("""SELECT COUNT(*) FROM Trips
                           WHERE start_station_id = ?""",
                        [station_id])
    trip_count = result.fetchone()[0]
    print(station_name, trip_count)

Koden samlar först in varje stations ID-nummer och namn till en lista. Därefter hämtar koden i en loop, för varje station separat, antalet cykelturer som startade från just den stationen. Koden fungerar i och för sig, men den gör nu en massa onödigt arbete genom att hämta varje antal turer med en egen fråga. En bättre lösning är att skapa en enda fråga som direkt hämtar all nödvändig information:

sql = """SELECT S.name, COUNT(*)
         FROM Stations AS S
           LEFT JOIN Trips AS T ON S.id = T.start_station_id
         GROUP BY S.id"""

data = db.execute(sql).fetchall()
for station_name, trip_count in data:
    print(station_name, trip_count)

Den uppdaterade frågan är visserligen mer komplicerad än den tidigare, men den gör det nu möjligt för databashanteraren att optimera hämtningen av datan som en helhet och leverera den så effektivt som möjligt till koden.

6. Databasdesign

Principerna för god design

Vid design av en databas behöver man fastställa dess struktur: vilka tabeller ska databasen innehålla och vilka kolumner ska varje tabell ska ha. Det finns många möjliga sätt att göra detta på, och med hjälp av några centrala principer kommer man långt.

Vid databasdesign vill man att den resulterande databasen ska vara enkel och smidig att använda med SQL. Databasens struktur bör utformas så att information enkelt kan hämtas och ändras med SQL-kommandon.

Principerna för databasdesign är användbara och hjälper oss att få fungerande lösningar. Det är dock viktigt att reflektera över vad som ligger bakom principerna och när det kan vara motiverat att göra på något annat sätt. Det är viktigare att designa en databas så att den lämpar sig för sitt användningsområde än att följa principerna bara för principernas skull.

Tabell vs. klass

Definitionen av en databastabell beskriver vilken typ av information som kan lagras i tabellen. Till exempel innehåller varje rad i tabellen Movies nedan filmens namn och utgivningsår:

CREATE TABLE Movies (
  id INTEGER PRIMARY KEY,
  name TEXT,
  release_year INTEGER
);

INSERT INTO Movies (name, release_year) VALUES ('Snövit', 1937);
INSERT INTO Movies (name, release_year) VALUES ('Fantasia' 1940);
INSERT INTO Movies (name, release_year) VALUES ('Pinocchio', 1940);

I många programmeringsspråk beskriver definitionen av en klass vilken typ av information objekten innehåller. Till exempel definierar följande Pythonkod klassen Movie, som innehåller filmens namn och utgivningsår. Därefter lägger koden till objekt i en lista.

@dataclass
class Movie:
    name: str
    release_year: int

movies = []
movies.append(Movie("Snövit", 1937))
movies.append(Movie("Fantasia", 1940))
movies.append(Movie("Pinocchio", 1940))

Definitionen av en databastabell liknar alltså en klass i programmering, och en enskild rad i tabellen motsvarar ett objekt som skapats från klassen.

En eller flera tabeller?

I programmering baseras alla objekt av samma typ på samma klass. På samma sätt finns alla rader av samma typ i en tabell i databasen. Således kan vi hantera raderna smidigt med SQL-kommandon.

Om databasen exempelvis innehåller filmer är ett bra tillvägagångssätt att lagra alla filmer i en och samma tabell, Movies:

id  name       release_year
--  ---------  ------------
 Snövit     1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      1941        
 Bambi      1942        

Från denna tabell kan vi till exempel hämta filmer från år 1940 på följande sätt:

SELECT name FROM Movies WHERE release_year = 1940;

Vad händer om vi istället skulle dela upp filmerna i flera tabeller, till exempel efter utgivningsår? Då skulle tabellen Movies1940 innehålla filmer från 1940. Vi skulle kunna hämta filmerna på följande sätt:

SELECT name FROM Movies1940;

Denna lösning fungerar så länge vi bara vill hämta filmer från ett visst utgivningsår. Databasen blir dock snabbt knepig att använda om vi vill göra andra typer av sökningar. Om vi exempelvis vill hämta alla filmer från åren 1940–1950 behöver vi flera olika frågor:

SELECT name FROM Movies1940;
SELECT name FROM Movies1941;
SELECT name FROM Movies1942;
...
SELECT name FROM Movies1950;

När filmerna är i en och samma tabell räcker en fråga:

SELECT name FROM Movies WHERE release_year BETWEEN 1940 AND 1950;

När filmerna finns i en och samma tabell kan vi alltså hantera dem mångsidigt med enskilda SQL-kommandon, vilket inte skulle vara möjligt om flmerna fanns i flera olika tabeller.

Referera

En-till-många-relation

Vi betraktar en databas som innehåller tabeller med kurser och lärare. Mellan tabellerna finns en en-till-många-relation: varje kurs har en lärare, medan en och samma lärare kan undervisa flera kurser. Vi kan skapa databasens tabeller på följande sätt:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

I tabellen Courses refererar kolumnen teacher_id till tabellen Teachers, det vill säga den innehåller ett ID-nummer för en viss lärare. Referensen anges med REFERENCES-attributet, som anger att heltalet i kolumnen refererar till tabellen Teachers.

Vi kan till exempel lägga in följande data i tabellerna:

INSERT INTO Teachers (name) VALUES ('Kaila');
INSERT INTO Teachers (name) VALUES ('Kivinen');
INSERT INTO Teachers (name) VALUES ('Laaksonen');

INSERT INTO Courses (name, teacher_id) VALUES ('Datanätverk', 1);
INSERT INTO Courses (name, teacher_id) VALUES ('Seminarium', 1);
INSERT INTO Courses (name, teacher_id) VALUES ('PHP-programmering', 3);
INSERT INTO Courses (name, teacher_id) VALUES ('Neuronnät', 2);

Många-till-många-relation

Låt oss betrakta en situation där flera lärare kan undervisa en kurs tillsammans. Det är då fråga om en många-till-många-relation, eftersom en kurs kan ha flera lärare och en lärare kan undervisa flera kurser.

Nu kan en rad i tabellen Teachers vara kopplad till flera rader i tabellen Courses, och på motsvarande sätt kan en rad i tabellen Courses vara kopplad till flera rader i tabellen Teachers. Eftersom en rad i databasen inte kan innehålla en lista med referenser kan vi inte lägga till referenser direkt i någon av tabellerna, utan vi måste skapa en ny tabell för referenserna:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE CourseTeachers (
  course_id INTEGER REFERENCES Courses,
  teacher_id INTEGER REFERENCES Teachers
);

Skillnaden från tidigare är att tabellen Courses inte längre innehåller någon referens till tabellen Teachers. Istället finns nu en ny tabell, CourseTeachers, som refererar till båda tabellerna. Varje rad i denna tabell beskriver en relation av typen ”kursen id undervisas av lärare id”.

Vi kan till exempel på följande sätt beskriva att en kurs har två lärare:

INSERT INTO Teachers (name) VALUES ('Laaksonen');
INSERT INTO Teachers (name) VALUES ('Luukkainen');

INSERT INTO Courses (name) VALUES ('PHP-programmering');
INSERT INTO Courses (name) VALUES ('Neuronnät');

INSERT INTO CourseTeachers VALUES (1, 1);
INSERT INTO CourseTeachers VALUES (1, 2);
INSERT INTO CourseTeachers VALUES (2, 1);

Detta innebär att lärarna Laaksonen och Luukkainen undervisar kursen PHP-programmering. Därtill undervisar Laaksonen kursen Neuronnät.

Observera att denna lösning kunde användas även i det tidigare fallet där en kurs alltid har exakt en lärare. I så fall skulle dock databasen i princip innehålla en onödig tabell.

Atomär data

Princip: Varje kolumn i en databastabell ska innehålla en enda, alltså atomär (odelbar), uppgift, såsom ett tal eller en sträng. En kolumn får inte innehålla en lista med flera värden.

Denna princip underlättar hanteringen av databasen med SQL-kommandon. När varje uppgift finns i sin egen kolumn går det enkelt att hänvisa till datan.

En lista kan lagras i databasen genom att skapa en tabell där varje rad motsvarar ett enskilt element i listan, som i det tidigare exemplet med tabellen CourseTeachers. Varför skulle vi dock inte bara kunna lagra listan i en enda kolumn? Följande exempel förtydligar detta.

Exempel

Steg 1

Vi vill spara studerandes tentresultat i databasen. Tenten består av fyra uppgifter och man kan få 0–6 poäng per uppgift. Vi kunde försöka lagra tentpoängen på följande sätt:

student_id  points 
----------  -------
1           6,5,1,4
2           3,6,6,6
3           6,4,0,6

Idén är att kolumnen points innehåller en sträng med en lista av poäng separerade med kommatecken. Denna lösning bryter mot principen ovan, där varje kolumn ska innehålla en enda uppgift. Vad är problemet med denna lösning?

Problemet med lösningen är att det blir besvärligt att komma åt poängen med SQL-kommandon eftersom de ligger inuti en sträng. Om vi till exempel vill beräkna varje studerandes totala poäng behöver vi en fråga som ser ut ungefär på följande sätt:

SELECT student_id,
       SUBSTR(points, 1, 1) + SUBSTR(points, 3, 1) +
       SUBSTR(points, 5, 1) + SUBSTR(points, 7, 1) AS total_points
FROM Results;

Här används funktionen SUBSTR för att extrahera en delsträng från huvudsträngen. Frågan är dock krånglig och fungerar endast när det finns exakt fyra poäng och de är ensiffriga. Vi behöver ett bättre sätt för att lagra poängen.

Steg 2

I följande tabell finns fyra kolumner för poängen, vilket gör att vi kan hantera dem en i taget:

student_id  points1  points2  points3  points4
----------  -------  -------  -------  -------
1           6        5        1        4
2           3        6        6        6
3           6        4        0        6

Vi kan nu ställa frågan på ett smidigare sätt:

SELECT student_id,
       points1 + points2 + points3 + points4 AS total_points
FROM Results;

Denna lösning är klart bättre, men det finns fortfarande problem. Även om poängen finns i olika kolumner förutsätts det fortfarande att tenten består av exakt fyra uppgifter. Om antalet uppgifter i tenten ändras måste vi ändra tabellens struktur och alla SQL-kommandon som har med poängen att göra. Lösningen kan alltså ytterligare förbättras.

Steg 3

När vi vill lagra en lista i databasen är en bra lösning att spara varje element på en egen rad. I det här exemplet kan vi skapa en tabell där varje rad anger en viss studerandes poäng för en viss uppgift:

student_id  task_id  points
----------  -------  ------
         1        6     
         2        5     
         3        1     
         4        4     
         1        3     
         2        6     
         3        6     
         4        6     
         1        6     
         2        4     
         3        0     
         4        6     

Vi kan nu hämta varje studerandes totalpoäng på följande sätt:

SELECT student_id, SUM(points) AS total_points
FROM Results
GROUP BY student_id;

Denna allmänna fråga fungerar oavsett antalet uppgifter. Vi kan använda funktionen SUM för att beräkna summan istället för att behöva lista alla uppgifter manuellt.

Att antalet rader i tabellen ökar avsevärt till följd av förändringen är inget man behöver oroa sig för. Databashanterare är utformade så att de fungerar bra även om en tabell innehåller många rader.

Vad är atomär data?

Begreppet atomär data är inte särskilt väl definierat. Det är tydligt att en lista inte är atomär data, men hur är det till exempel med en sträng som innehåller flera ord?

Låt oss som exempel betrakta en situation där en tabellkolumn innehåller ett användarnamn. Samma kolumn innehåller både för- och efternamn. Är denna design dålig?

id  name          
--  --------------
1   Anna Virtanen 
2   Maija Korhonen
3   Pasi Lahtinen 

Vi kunde också lagra för- och efternamnen skilt på följande sät:

id  first_name  last_name
--  ----------  ---------
1   Anna        Virtanen 
2   Maija       Korhonen 
3   Pasi        Lahtinen 

Vilken tabell som är bättre beror på situationen. Om systemet uttryckligen behöver kunna söka information baserat på för- eller efternamn (till exempel hitta alla användare vars förnamn är Anna), är den senare tabellen bättre. Ofta är det ändå inte så, och det finns inget fel i att spara både för- och efternamn i samma kolumn.

På motsvarande sätt, om man sparar ett meddelande som en användare har skickat till databasen, kan det innehålla många ord. Egentligen är meddelandet alltså en lista av ord. Det är ändå en bra lösning att spara hela meddelandet i en och samma kolumn eftersom meddelandet hanteras som en enda helhet i databasen. En dålig lösning skulle vara att ”atomiskt” dela upp orden i egna kolumner.

Man kan alltså tänka så här: om viss data behöver behandlas separat i SQL-kommandon är den atomär och bör ligga i en egen kolumn. Om man däremot inte refererar till datan i SQL-kommandon, kan den vara en del av en större helhet i samma kolumn.

Redundans

Princip: Varje uppgift finns på exakt ett ställe i databasen. I databasen finns det inte information som kan räknas ut eller härledas utgående från annat innehåll i databasen.

Genom att följa den här principen blir det enkelt att uppdatera innehållet i databasen, eftersom uppdateringen bara behöver göras på ett ställe och inte påverkar andra delar av databasen.

Exempel 1

Vi sparar de meddelanden som användarna skickar till systemet på följande sätt i tabellen Messages:

id  user        message       
--  ----------  --------------
1   Anna123     Var är du?   
2   Julgubben   Fortfarande i bussen
3   Anna123     Tar det länge?
4   Julgubben   5 min         

Lösningen fungerar i stort sett bra, men det är svårt att uppdatera innehållet i databasen om användaren bestämmer sig för att byta namn. Till exempel, om Anna123 vill byta sitt namn, måste ändringen göras i varje meddelande som hon har skickat.

En bättre lösning är att användarens namn finns på endast ett ställe i databasen, exempelvis i tabellen Users, där alla användare finns.

id  name      
--  ----------
1   Anna123   
2   Julgubben

I de andra tabellerna finns endast användarens ID-nummer som referens, vilket är oföränderlig information. Till exempel ser tabellen Messages nu ut så här:

id  user_id  message       
--  -------  --------------
1   1        Var är du?   
2   2        Fortfarande i bussen
3   1        Tar det länge?
4   2        5 min         

Nu är det lätt att ändra användarens namn, eftersom det räcker med att göra ändringen i en rad i tabellen Users. Ändringen uppdateras genast överallt, eftersom de andra tabellerna fortfarande refererar till rätt rad.

Över lag är detta en välfungerande lösning. Frågorna blir dock mer komplicerade eftersom informationen nu måste hämtas från flera olika tabeller.

Fortfarande redundans?

Trots den senaste ändringen så kan det fortfarande förekomma redundant information i databasen. Till exempel i följande situation, där användarna skickar ett likadant meddelande, “Hej!”. Borde databasens struktur förbättras?

id  user_id  message
--  -------  -------
1   1        Hej!   
2   2        Hej!   

I det här fallet vore det inte en bra idé att utforma databasen så att om två användare skickar ett meddelande med samma innehåll, sparas innehållet bara på ett ställe.

Även om meddelandena har samma innehåll, så är de separata meddelanden som inte är avsedda att referera till samma sak. Om användare 1 ändrar innehållet i sitt meddelande ska ändringen inte påverka meddelandet från användare 2, även om detta meddelande för tillfället har samma innehåll.

Exempel 2

Vi sparar information om studerandenas prestationer i databasen. Ur databasen kan man ta reda på hur många studiepoäng en studerande har avklarat.

I följande databas har information sparats om hur många studiepoäng varje studerande har avklarat. Tabellen Students innehåller följande:

id  name    total_credits
--  ------  -------------
1   Maija   20           
2   Uolevi  10           

Tabellen Completions innehåller däremot följande rader:

id  student_id  course_id  credits
--  ----------  ---------  -------
 1           1          5      
 1           2          5      
 1           4          10     
 2           1          5      
 2           3          5      

Vi kan enkelt hämta den totala summan av en studerandes studiepoäng på följande sätt:

SELECT total_credits FROM Students WHERE name = 'Maija';

I databasen finns dock redundant information: innehållet i kolumnen total_credits i tabellen Students kan beräknas med hjälp av tabellen Completions. Till exempel Maijas totala antal studiepoäng, som i tabellen Students är 20, kan också beräknas som summan 5 + 5 + 10 från tabellen Completions.

Ett problem är nu att för varje prestation som registreras måste en ny rad läggas till i tabellen Completions och det totala antalet studiepoäng i tabellen Students måste uppdateras. Om uppdateringen glöms bort eller misslyckas uppstår motsägelsefull information i databasen.

Vi kan bli av med den redundanta informationen genom att ta bort kolumnen total_credits från tabellen Students:

id  name  
--  ------
1   Maija 
2   Uolevi

Denna ändring gör det dock svårare att ta reda på en studerandes totala antal studiepoäng, eftersom informationen nu måste beräknas utifrån prestationerna:

SELECT SUM(Completions.credits) AS total_credits
FROM Completions, Students
WHERE Completions.student_id = Students.id AND Students.name = 'Maija';

Över lag är detta ändå en bra lösning, eftersom vi nu kan ändra prestationer i tabellen Completions och vara säkra på att vi alltid beräknar det senaste uppdaterade antalet studiepoäng för varje studerande.

Ändringar vs. frågor

Fastän det ideala är att en databas inte ska innehålla redundant information, behövs sådan ibland för att effektivisera sökningar. Redundans gör databasen svårare att ändra, men underlättar frågorna.

Ett ofta förekommande fenomen inom datavetenskap är att vi måste balansera mellan om vi vill kunna ändra eller hämta information effektivt, och hur mycket lagringsutrymme vi kan använda. Det här gäller utöver databaser också till exempel vid utformning av algoritmer.

Om databasen inte innehåller redundant information är ändringar lätta att göra, eftersom varje uppgift finns på endast ett ställe och det således räcker med att ändra en rad i en tabell. Man sparar också på lagringsutrymme genom att inte ha redundant information. Å andra sidan kan frågorna bli mer komplicerade och långsamma, eftersom den information man behöver nu måste samlas in från flera olika delar av databasen.

Genom att lägga till redundant information kan vi snabba upp frågorna. Ändringar i databasen blir dock mer besvärliga, eftersom den ändrade informationen måste uppdateras på flera ställen. Den redundanta informationen gör också att databasen tar upp mer lagringsutrymme.

Det finns ingen allmän regel för hur mycket redundant information det lönar sig att lägga till, utan det beror på databasens innehåll och vilka typer av frågor man vill kunna göra. Ett bra tillvägagångssätt är att börja utan någon redundant information alls i databasen. Varefter det visar sig att frågorna behöver göras mer effektiva, kan redundans införas.

Exempel på design

Låt oss till sist betrakta ett mer omfattande exempel där vårt mål är att designa en databas för bokning av universitetsföreläsningssalar. Databasen ska möjliggöra följande:

Användaren kan logga in i systemet genom att ange ett användarnamn och ett lösenord. I systemet finns både vanliga användare och administratörer.
Användaren ser salens kod, byggnaden, antalet sittplatser och prisklassen. Exempel: A111, Exactum, 280, A10
Timpriset för bokningen beror på prisklassen. Användaren ser det totala priset i samband med bokningen.
Byggnaderna har bestämda öppettider. En sal kan bokas endast när byggnaden är öppen.
Användaren kan söka efter en lämplig sal genom att ange byggnad och hur många sittplatser salen minst ska ha.
Användaren kan göra en bokningsförfrågan för en sal för ett visst tidsintervall på en viss dag. Användaren kan också lägga till en kommentar i samband med bokningsförfrågan. Administratören bekräftar bokningen utifrån bokningsförfrågan.

Designen steg-för-steg

Databasdesignen utvecklas vanligtvis stegvis, så att nya tabeller och kolumner läggs till i databasen allteftersom behov uppstår.

Låt oss se hur exempel-databasen byggs upp steg för steg.

Inloggning i systemet

Användaren kan logga in i systemet genom att ange ett användarnamn och ett lösenord. I systemet finns både vanliga användare och administratörer.

Detta är ett vanligt och bra sätt att börja på när man designar en databas. Vi behöver en tabell som innehåller användarnamn och lösenord:

CREATE TABLE Users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT
);

Eftersom det finns två typer av användare i systemet (vanliga användare och administratörer) måste denna information också sparas i databasen. Två möjliga lösningar är:

Lösning 1: I tabellen Users läggs en kolumn till som anger användarens roll.
Lösning 2: Tabellen Users innehåller de vanliga användarna och en separat tabell Admins skapas för administratörerna.

Lösning 1 är vanligtvis bättre ur databasanvändningens synvinkel. Vi lägger därför till en ny kolumn user_role i tabellen Users som anger användarens roll (t.ex. 1 = vanlig användare, 2 = administratör):

CREATE TABLE Users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT,
  user_role INTEGER
);

Information om salen som ska bokas

Användaren ser salens kod, byggnaden, antalet sittplatser och prisklassen. Exempel: A111, Exactum, 280, A10

En bra lösning är att skapa separata tabeller Buildings och Categories där information om byggnader respektive prisklasser sparas:

CREATE TABLE Buildings (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
);

Därefter kan vi skapa tabellen Rooms, som innehåller information om salarna. Denna tabell refererar till tabellerna Buildings och Categories.

CREATE TABLE Rooms (
  id INTEGER PRIMARY KEY,
  name TEXT,
  building_id INTEGER REFERENCES Buildings,
  seat_count INTEGER,
  category_id INTEGER REFERENCES Categories
);

Vi kan nu lägga till följande information om salen A111 i systemet:

INSERT INTO Buildings (name) VALUES ('Exactum');
INSERT INTO Categories (name) VALUES ('A10');
INSERT INTO Rooms (name, building_id, seat_count, category_id)
       VALUES ('A111', 1, 280, 1);

Totalpriset för bokningen

Timpriset för bokningen beror på prisklassen. Användaren ser det totala priset i samband med bokningen.

För denna funktion kan vi lägga till en kolumn price_per_hour i tabellen Categories som anger timpriset för respektive prisklass:

CREATE TABLE Categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price_per_hour INTEGER
);

Därefter kan vi beräkna hur mycket en bokning på fyra timmar för sal A111 kostar.

SELECT Categories.price_per_hour * 4 AS total_price
FROM Rooms, Categories
WHERE Rooms.category_id = Categories.id AND Rooms.name = 'A111';

Byggnadernas öpettider

En sal kan bokas endast när byggnaden är öppen.

Byggnaderna är vanligtvis öppna på vardagar men stängda på helger och helgdagar. Dessutom kan öppettiderna ändras, till exempel under sommaren.

I praktiken kan det vara svårt att definiera riktlinjer som fungerar i alla situationer och anger när en viss byggnad är öppen. Ett bra alternativ är istället att skapa tabellen OpeningHours som innehåller en rad för varje byggnad och för varje dag den är öppen:

CREATE TABLE OpeningHours (
  id INTEGER PRIMARY KEY,
  building_id INTEGER REFERENCES Buildings,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

Till exempel lägger följande kommandon till uppgifter om när byggnaden Exactum är öppen under den första veckan i september 2026:

INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2026-09-01 08:00:00', '2026-09-01 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2026-09-02 08:00:00', '2026-09-02 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2026-09-03 08:00:00', '2026-09-03 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2026-09-04 08:00:00', '2026-09-04 20:00:00');
INSERT INTO OpeningHours (building_id, start_time, end_time)
       VALUES (1, '2026-09-05 08:00:00', '2026-09-05 18:00:00');

Söka efter en lämplig sal

Användaren kan söka efter en lämplig sal genom att ange byggnad och hur många sittplatser salen minst ska ha.

Denna funktion kan utföras med hjälp av de redan befintliga tabellerna. Till exempel söker följande kommando efter en sal i Exactum som rymmer minst 100 personer:

SELECT Rooms.name
FROM Rooms, Buildings
WHERE Rooms.building_id = Buildings.id AND
      Rooms.seat_count >= 100 AND Buildings.name = 'Exactum';

Bokningsförfrågan och bekräftelse

Användaren kan göra en bokningsförfrågan för en sal för ett visst tidsintervall på en viss dag. Användaren kan också lägga till en kommentar i samband med bokningsförfrågan. Administratören bekräftar bokningen utifrån bokningsförfrågan.

Vi kan skapa tabellen Requests där bokningsförfrågningar sparas. Varje förfrågan är kopplad till en användare, en sal, bokningens start- och sluttid samt en kommentar.

CREATE TABLE Requests (
  id INTEGER PRIMARY KEY
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  comment TEXT
);

Vad händer när administratören godkänner en bokning? En lösning skulle vara att skapa en annan tabell Reservations, som innehåller de godkända bokningarna:

CREATE TABLE Reservations (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

Även om en bokningsförfrågan och en bekräftad bokning i sig är olika saker, uppstår här problemet att tabellerna Requests och Reservations innehåller mycket liknande information. En bättre lösning kunde vara att slå ihop dessa tabeller exempelvis på följande sätt:

CREATE TABLE Reservations (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  status INTEGER
);

I den här tabellen anger kolumnen status bokningens status. Vi kan till exempel bestämma att status 1 betyder en bokningsförfrågan och status 2 betyder en bekräftad bokning.

Beskrivning av databasen

Det finns två vanliga sätt att beskriva databasens struktur: ett grafiskt databasschema som visar relationerna mellan tabellerna, och ett SQL-schema som innehåller kommandon för att skapa tabellerna

Databasschema

Ett databasschema är en grafisk representation av databasen där varje tabell visas som en ruta som innehåller tabellens namn och kolumner som en lista. Referenser mellan rader visas som kopplingar mellan rutorna.

Det finns olika sätt att rita ett databasschema. Följande schema har skapats med ett onlineverktyget dbdiagram.io:

SQL-schema

Ett SQL-schema innehåller CREATE TABLE-kommandon som används för att skapa databasen. Följande SQL-schema motsvarar vår exempel-databas:

CREATE TABLE Users (
  id INTEGER PRIMARY KEY,
  username TEXT,
  password TEXT,
  user_role INTEGER
);

CREATE TABLE Buildings (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price_per_hour INTEGER
);

CREATE TABLE Rooms (
  id INTEGER PRIMARY KEY,
  name TEXT,
  building_id INTEGER REFERENCES Buildings,
  seat_count INTEGER,
  category_id INTEGER REFERENCES Categories
);

CREATE TABLE OpeningHours (
  id INTEGER PRIMARY KEY,
  building_id INTEGER REFERENCES Buildings,
  start_time TIMESTAMP,
  end_time TIMESTAMP
);

CREATE TABLE Reservations (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES Users,
  room_id INTEGER REFERENCES Rooms,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  status INTEGER
);

7. Databasernas egenskaper

Dataintegritet

Dataintegritet innebär att datan i databasen är korrekt och konsekvent. Det primära ansvaret för datakvaliteten ligger hos användaren eller applikationen som ändrar databasen. Även den som designar databasen kan påverka datakvaliteten genom att lägga till villkor i tabellerna som övervakar den information som matas in i databasen.

Villkor för kolumner

När vi skapar en tabell kan vi lägga till villkor på kolumnerna som databashanteraren övervakar när data läggs till eller ändras. Dessa villkor kan begränsa vilken information som får matas in i databasen. Vanliga villkor är följande:

UNIQUE

Villkoret UNIQUE innebär att kolumnen måste ha olika värden på varje rad. Till exempel i följande tabell är kravet att varje produkt ska ha ett unikt namn:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE,
  price INTEGER
);

Villkoret UNIQUE kan också gälla flera kolumner, och då anges det separat efter kolumnerna:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER,
  UNIQUE(name, price)
);

Detta innebär att tabellen inte kan innehålla två rader med samma namn och samma pris.

NOT NULL och DEFAULT

Villkoret NOT NULL innebär att kolumnen inte får innehålla värdet NULL. Till exempel i följande tabell får produktens pris inte fattas:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER NOT NULL
);

DEFAULT ger kolumnen ett standardvärde om inget värde anges när en rad läggs till. Till exempel kan vi ange priset till standardvärdet 0 på följande sätt:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER DEFAULT 0
);

CHECK

Ett vanligt sätt att skapa ett villkor är att använda nyckelordet CHECK, följt av ett valfritt villkorsuttryck. Till exempel i följande tabell innebär villkoret price >= 0 att priset inte får vara negativt:

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER,
  CHECK (price >= 0)
);

Övervakning av villkoren

Fördelen med villkor är att databashanteraren övervakar dem och inte tillåter användare att lägga till eller ändra data som bryter mot villkoren. Nedan är ett exempel på detta i SQLite:

sqlite> CREATE TABLE Products (id INTEGER PRIMARY KEY,
   ...>                        name TEXT,
   ...>                        price INTEGER,
   ...>                        CHECK (price >= 0));
sqlite> INSERT INTO Products(name, price) VALUES ('rädisa', 4);
sqlite> INSERT INTO Products(name, price) VALUES ('selleri', 7);
sqlite> INSERT INTO Products(name, price) VALUES ('rova', –2);
Error: CHECK constraint failed: Products
sqlite> SELECT * FROM Products;
1|rädisa|4
2|selleri|7
sqlite> UPDATE Products SET price = –2 WHERE id = 2;
Error: CHECK constraint failed: Products

I exemplet ovan försöker man lägga till en rad i tabellen Products där priset är negativt. Detta bryter mot villkoret price >= 0. SQLite tillåter således inte raden att läggas till, utan ger felet CHECK constraint failed: Products. Samma sak händer om vi senare försöker ändra priset i en redan befintlig rad till ett negativt värde.

Villkor i programmering

Villkor i tabeller kan man dra nytta av i programmering för att göra koden enklare och mer pålitlig.

Låt oss betrakta ett exempel där vi vill försäkra oss om att varje produkt som läggs till i tabellen har ett unikt namn. Ett sätt att göra detta i koden är att innan ett INSERT-kommando utföra ett SELECT-kommando som kontrollerar att det ännu inte finns någon produkt med samma namn i tabellen:

result = db.execute("SELECT 1 FROM Products WHERE name = ?", [name])
if result.fetchone() is None:
    db.execute("INSERT INTO Products (name, price) VALUES (?, ?)",
               [name, price])
    print("Produkten har lagts till i tabellen")
else:
    print("Produkten kunde inte läggas till eftersom den redan finns i tabellen")

Idén är att SELECT-frågan returnerar 1 om det redan finns en produkt med samma namn i tabellen, och inget resultat alls om produkten inte finns. På detta sätt kan man identifiera en situation där produkten redan har lagts till i tabellen.

Denna lösning är inte värst bra eftersom den är onödigt komplicerad och dessutom inte fungerar korrekt i alla situationer. Eftersom databasen kan ha flera användare är det möjligt att en annan användare lägger till en produkt i databasen mellan utförandet av SELECT- och INSERT-kommandot. I sådant fall förlitar sig koden på föråldrad information om tabellens innehåll och samma produkt finns då dubbelt i tabellen.

En bättre lösning är att använda villkoret UNIQUE på kolumnen name i tabellen och på så sätt försäkra sig om att tabellen inte kan innehålla två produkter med samma namn. Vi kan nu implementera hur en produkt läggs till i databasen på följande sätt:

try:
    db.execute("INSERT INTO Products (name, price) VALUES (?, ?)",
               [name, price])
    print("Produkten har lagts till i tabellen")
except sqlite3.IntegrityError:
    print("Produkten kunde inte läggas till eftersom den redan finns i tabellen")

I det här fallet misslyckas INSERT-kommandot om det redan finns en produkt med samma namn i tabellen. I Python-koden kan situationen identifieras genom att tilläggsförsöket ger felet sqlite3.IntegrityError. Koden kan alltså försöka lägga till produkten utan att först kontrollera om den redan finns i tabellen, och det eventuella felet som uppstår visar att produkten redan har lagts till tidigare.

Detta är klart en bättre lösning än att i koden kontrollera med en SELECT-fråga om produkten redan finns i tabellen, eftersom databasen med hjälp av villkoret UNIQUE på ett pålitligt sätt säkerställer detta. Själva koden blir nu också kortare.

Villkor för referenser

Villkor som gäller referenser i tabeller säkerställer att referenserna pekar på faktiska rader. Låt oss betrakta följande tabeller som exempel:

CREATE TABLE Teachers (
  id INTEGER PRIMARY KEY,
  name TEXT
);

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER
);

Tanken här är att kolumnen teacher_id i tabellen Courses ska referera till kolumnen id i tabellen Teachers. Databasens användare kan dock ange vilket värde som helst för kolumnen teacher_id (t.ex. siffran 123), vilket gör att databasens innehåll då blir felaktigt.

Detta kan förbättras genom att vid skapandet av tabellen Courses ange med REFERENCES att kolumnen teacher_id refererar till tabellen Teachers:

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers
);

Kolumnen teacher_id i tabellen Courses är en främmande nyckel, kallas också referensattribut (på engelska foreign key), som refererar till primärnyckeln id i tabellen Teachers. Det innebär att värdet i kolumnen teacher_id i tabellen Courses måste referera till en faktisk rad i tabellen Teachers.

Observera att SQLite av historiska skäl inte övervakar referenser som standard, utan vi måste först ange följande kommando:

sqlite> PRAGMA foreign_keys = ON;

Detta är en särskild egenskap i SQLite. Referenser övervakas alltid i andra databashanterare.

Nedan är ett exempel på hur referenser övervakas:

sqlite> PRAGMA foreign_keys = ON;
sqlite> CREATE TABLE Teachers (id INTEGER PRIMARY KEY, name TEXT);
sqlite> CREATE TABLE Courses (id INTEGER PRIMARY KEY, name TEXT,
   ...>                       teacher_id INTEGER REFERENCES Teachers);
sqlite> INSERT INTO Teachers (name) VALUES ('Kaila');
sqlite> INSERT INTO Teachers (name) VALUES ('Kivinen');
sqlite> SELECT * FROM Teachers;
1|Kaila
2|Kivinen
sqlite> INSERT INTO Courses (name, teacher_id)
   ...>             VALUES ('Beräkningsmodeller', 2);
sqlite> INSERT INTO Courses (name, teacher_id)
   ...>             VALUES ('PHP-programmering', 123);
Error: FOREIGN KEY constraint failed   

Tabellen Teachers innehåller två lärare med ID-numren 1 och 2. När vi försöker lägga till en rad i tabellen Courses där teacher_id är 123 tillåter SQLite inte detta, utan vi får felmeddelandet FOREIGN KEY constraint failed.

Referenser och borttagningar

Villkor som gäller referenser kan vara mer komplicerade än vanliga kolumnvillkor, eftersom referenser kopplar samman två tabeller. Vad händer om man försöker ta bort en rad i en tabell som refereras i en annan tabell?

Standardinställningen i databashanterare är vanligtvis att en rad inte kan tas bort om den refereras någon annanstans. Om vi exempelvis i slutet av det tidigare exemplet försöker ta bort raden med ID 2 från tabellen Teachers kommer det inte att lyckas, eftersom raden refereras i tabellen Courses:

sqlite> DELETE FROM Teachers WHERE id = 2;
Error: FOREIGN KEY constraint failed

Om vi vill kan vi dock vid skapandet av tabellen specificera exakt vad som ska hända i en sådan situation. Ett alternativ är ON DELETE CASCADE, vilket innebär att när en rad tas bort raderas även de rader som refererar till den:

CREATE TABLE Courses (
  id INTEGER PRIMARY KEY,
  name TEXT,
  teacher_id INTEGER REFERENCES Teachers ON DELETE CASCADE
);

Om en lärare nu tas bort från databasen, tas automatiskt alla kurser som läraren undervisar i också bort. Denna lösning är dock inte nödvändigtvis den bästa, eftersom den kan leda till att data försvinner oväntat från databasens tabeller.

Möjliga alternativ i ON DELETE -delen är:

NO ACTION: “gör ingenting” (standard)
RESTRICT: förhindra borttagning
CASCADE: ta även bort alla rader som refererar till den borttagna raden
SET NULL: sätt referenserna till värdet NULL
SET DEFAULT: sätt referenserna till standardvärdet

Det kan kännas förvirrande att standardalternativet NO ACTION förhindrar borttagning av en rad, även om namnet kan antyda något annat. Alternativen NO ACTION och RESTRICT fungerar i praktiken nästan på samma sätt, men beroende på databashanteraren kan det finnas skillnader i vissa speciella situationer.

Transaktioner

En transaktion är en serie efterföljande SQL-kommandon som databassystemet ämnar att köra som en enda helhet. Databasanvändaren kan lita på att antingen (1) alla kommandon körs och ändringarna sparas permanent i databasen, eller (2) transaktionen avbryts och inga av kommandona leder till några ändringar i databasen.

I samband med transaktioner nämns ofta ACID. Dessa bokstäver står för fyra egenskaper som transaktioner bör ha. Bokstäverna kommer från följande ord:

Atomicity: Kommandona i en transaktion körs som en enda helhet (antingen genomförs hela transaktionen eller så sker inga ändringar alls).
Consistency: Transaktionen bevarar databasens innehåll intakt.
Isolation: Transaktionerna utförs isolerade från varandra.
Durability: De ändringar som görs i en avslutad transaktion blir permanenta. Ändringar som gjorts i databasen ska alltså aldrig försvinna.

Transaktionens olika steg

I själva verket är en transaktion något som används hela tiden i databaser eftersom varje SQL-kommando som körs som standard utgör en egen transaktion. Låt oss titta på följande exempel där priset höjs med ett på varje produkt:

UPDATE Products SET price = price + 1;

Eftersom kommandot körs som en transaktion kan vi vara säkra på att priset antingen höjs med ett för samtliga produkter eller att inga priser ändras alls. Det senare kan till exempel inträffa om strömmen går mitt under uppdateringen. I så fall ändras inga priser, och det är alltså inte möjligt att endast vissa priser uppdateras.

Ofta syftar ordet transaktion i synnerhet på att helheten som körs består av flera SQL-kommandon. Vi börjar med kommandot BEGIN som startar transaktionen, varefter alla kommandon som ingår i transaktionen körs på vanligt sätt. Transaktionen avslutas med kommandot COMMIT.

Ett klassiskt exempel på en transaktion är när pengar överförs från ett bankkonto till ett annat. Till exempel överför följande transaktion 100 euro från Maijas konto till Uolevis konto:

BEGIN;
UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
COMMIT;

Idén med en transaktion är att inga permanenta förändringar sker innan kommandot COMMIT körs. I exemplet ovan är det alltså inte möjligt att Maija förlorar 100 euro utan att Uolevi får pengarna. Antingen ändras båda kontonas saldon (pengarna överförs), eller så förblir båda saldon oförändrade.

Om en transaktion av någon anledning avbryts innan kommandot COMMIT körs, återgår alla ändringar som gjorts i transaktionen. En orsak till att en transaktion avbryts kan exempelvis vara ett fel i datorns funktion (till exempel ett strömavbrott). Vi kan själva också avbryta transaktionen genom att ange kommandot ROLLBACK.

Att testa transaktioner

Ett bra sätt att förstå transaktioner är att testa dem i praktiken och se hur de fungerar. Här är ett exempel på hur det kan se ut i SQLite:

sqlite> CREATE TABLE Accounts (id INTEGER PRIMARY KEY,
   ...>                        customer TEXT, balance INTEGER);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Uolevi', 350);
sqlite> INSERT INTO Accounts (customer, balance) VALUES ('Maija', 600);
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|500
sqlite> ROLLBACK;
sqlite> SELECT * FROM Accounts;
1|Uolevi|350
2|Maija|600
sqlite> BEGIN;
sqlite> UPDATE Accounts SET balance = balance - 100 WHERE customer = 'Maija';
sqlite> UPDATE Accounts SET balance = balance + 100 WHERE customer = 'Uolevi';
sqlite> COMMIT;
sqlite> SELECT * FROM Accounts;
1|Uolevi|450
2|Maija|500

Till att börja med har Uolevi 350 euro på sitt konto och Maija 600 euro på sitt konto. I den första transaktionen tar vi först 100 euro från Maijas konto, men ändrar oss sedan och avbryter transaktionen. Ändringen som gjordes i transaktionen återställs alltså, och kontonas saldon är desamma som i början. I den andra transaktionen genomförs däremot hela transaktionen. Efteråt har Uolevi 450 euro på sitt konto och Maija 500 euro.

Observera att ändringarna inom en transaktion är synliga, även om de ännu inte har sparats permanent i databasen. Till exempel visar SELECT-frågan i den första transaktionen att Maijas kontosaldo är 500 euro eftersom det föregående UPDATE-kommandot redan har ändrat saldot.

Transaktioner i programmering

Transaktionskommandon (BEGIN, COMMIT osv.) kan köras i programmering på samma sätt som andra SQL-kommandon. Till exempel lägger följande kod till tusen rader i tabellen Products inom en och samma transaktion i en for-loop:

db.execute("BEGIN")

for i in range(1000):
    db.execute("""
               INSERT INTO Products (name, price) VALUES (?, ?)
               """, ["product" + str(i), 1])

db.execute("COMMIT")

Eftersom koden körs inom en transaktion kommer antingen alla rader att läggas till i databasen eller inga rader alls om transaktionen av någon anledning misslyckas.

I det här fallet gör användningen av en transaktion också koden snabbare, eftersom varje rad inte läggs till i en separat transaktion utan tilläggen sker som en helhet. Det hjälper databasen att hantera insättningen av rader mer effektivt.

Parallella transaktioner

En ytterligare aspekt i hanteringen av transaktioner är att en databas kan ha flera användare som har pågående transaktioner samtidigt. I vilken utsträckning bör transaktioner från olika användare isoleras från varandra?

Detta är en fråga som inte har endast ett korrekt svar, utan svaret beror på situationen och på databasens egenskaper. Å ena sidan skulle den bästa lösningen vara att isolera transaktionerna helt från varandra, men å andra sidan kan detta försämra databasens användbarhet.

SQL-standarden definierar transaktioners isoleringsnivåer på följande sätt:

Nivå 1 (read uncommitted)

Det tillåts att en transaktion ser ändringar som en annan transaktion har gjort, även om den andra transaktionen ännu inte har slutförts.

Nivå 2 (read committed)

Till skillnad från nivå 1 får en transaktion här se ändringar som en annan transaktion har gjort endast om den andra transaktionen har slutförts.

Nivå 3 (repeatable read)

För nivå 3 gäller kraven för nivå 2, och dessutom, om samma rad läses flera gånger under en transaktion, ska innehållet vara detsamma varje gång.

Nivå 4 (serializable)

Transaktionerna är helt isolerade, och kommandona beter sig som om transaktionerna hade körts efter varandra, en i taget, i någon ordning.

Exempel

Låt oss titta på en situation där priset på produkt 1 i början är 8, och två användare (K1 och K2) samtidigt kör kommandon inom sina transaktioner:

K1: BEGIN;

K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;

K1: SELECT price FROM Products WHERE id = 1;
K1: COMMIT;

På nivå 1 kan användare 1 få resultaten 5 och 7 från sina frågor eftersom ändringar som användare 2 gör kan bli synliga direkt, även om transaktionen som användare 2 utför ännu inte har slutförts.

På nivå 2 kan användare 1 få resultaten 8 och 7 från sina frågor eftersom den andra transaktionen ännu inte har slutförts vid den första frågan, medan den är slutförd vid den andra frågan.

På nivåerna 3 och 4 får användare 1 resultaten 8 och 8 från sina frågor eftersom detta är situationen före det att transaktionen börjar och en transaktion som slutförs under tiden inte får ändra innehållet i raden som lästs.

Transaktioner i praktiken

Hur transaktioner implementeras och vilka isoleringsnivåer som är tillgängliga beror på vilken databashanterare som används. I exempelvis SQLite är den enda möjliga nivån 4, medan PostgreSQL stöder nivåerna 2–4, där nivå 2 är standard.

Isoleringsnivå 4 är på sätt och vis den “bästa”, eftersom ändringar i transaktioner då inte kan bli synliga för varandra på något sätt. Varför finns det då andra nivåer och varför är till exempel standardnivån i PostgreSQL nivå 2?

En konsekvens av en hög isoleringsnivå är att det kan sakta ner eller till och med hindra transaktioner från att genomföras, eftersom slutförandet av en transaktion skulle kunna orsaka en konfliktsituation. Å andra sidan räcker det i många praktiska situationer med en lägre isoleringsnivå så länge databasanvändaren är medveten om det.

Bra kunskap om hur parallella transaktioner fungerar får man genom att studera databasernas dokumentationener och genom att själv testa att utföra parallella transaktioner. Till exempel kan vi starta två SQLite-tolkar, öppna samma databas med dem och sedan skriva kommandon som innehåller transaktioner för att göra egna observationer.

Följande konversation visar resultatet av det tidigare exemplet i två SQLite-tolkar som körs parallellt:

K1: BEGIN;
                                         
K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;
8

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;
Error: database is locked

K1: SELECT price FROM Products WHERE id=1;
8
K1: COMMIT;

Här ser man att den första transaktionen får resultatet 8 i båda frågorna. Den andra transaktionen kan däremot inte slutföras, utan ett felmeddelande Error: database is locked uppstår eftersom databasen är låst på grund av den parallella transaktionen. Isoleringen fungerar alltså som den ska, men den andra transaktionen måste försöka slutföras på nytt.

Här är motsvarande konversation i PostgreSQL-tolkar (nivå 2):

K1: BEGIN;

K2: BEGIN;
K2: UPDATE Products SET price = 5 WHERE id = 1;

K1: SELECT price FROM Products WHERE id = 1;
8

K2: UPDATE Products SET price = 7 WHERE id = 1;
K2: COMMIT;

K1: SELECT price FROM Products WHERE id = 1;
7
K1: COMMIT;

Nu dyker värdet 7, som den andra transaktionen ändrat, upp i den första transaktionen. Å andra sidan kan båda transaktionerna slutföras utan problem.

Hur fungerar transaktioner?

Att genomföra transaktioner är en fascinerande teknisk utmaning i databaser. På sätt och vis ska en transaktion göra ändringar i databasen eftersom olika kommandon kan bero på tidigare steg. Samtidigt får ingenting ändras permanent förrän transaktionen har avslutats

En central tanke bakom databaser är att spara förändringar på två sätt. Först antecknas en beskrivning av ändringen i en loggfil (write-ahead log) som man kan tänka sig som en lista över de kommandon som har körts. Först efter detta görs ändringarna i databasens egentliga datastrukturer. Om något oväntat händer i det senare skedet finns ändringarna redan sparade i loggfilen och kan utföras på nytt senare.

I samband med transaktioner måste databashanteraren också hålla reda på vilka ändringar som hör till vilken pågående transaktion. I praktiken kan tabellerna innehålla radändringar som bara är synliga för vissa transaktioner. Om en transaktion sedan slutförs, fogas dessa ändringar till tabellens permanenta innehåll.

Köra frågor

Idén bakom SQL-språket är att användaren bara behöver beskriva vilken information som ska hämtas, och databashanteraren sköter resten. Därför är det viktigt att databashanteraren kan hitta ett effektivt sätt att genomföra frågan och returnera resultatet.

Frågeplan

Många databashanterare kan på begäran visa sin plan för hur en given fråga ska utföras. Det här gör det möjligt att studera hur databashanteraren fungerar internt.

Låt oss som exempel titta på en fråga som hämtar uppgifter om rädisan ur tabellen Products:

SELECT * FROM Products WHERE name = 'rädisa';

Om vi i SQLite placerar ordet EXPLAIN framför en fråga får vi en beskrivning av frågeplanen som ser ut ungefär så här:

sqlite> EXPLAIN SELECT * FROM Products WHERE name = 'rädisa';
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
   Init           0     12    0                    00  Start at 12  
   OpenRead       0     2     0     3              00  root=2 iDb=0; Products
   Rewind         0     10    0                    00               
     Column         0     1     1                    00  r[1]=Products.name
     Ne             2     9     1     (BINARY)       52  if r[2]!=r[1] goto 9
     Rowid          0     3     0                    00  r[3]=rowid   
     Copy           1     4     0                    00  r[4]=r[1]    
     Column         0     2     5                    00  r[5]=Products.price
     ResultRow      3     3     0                    00  output=r[3..5]
   Next           0     3     0                    01               
  Close          0     0     0                    00               
  Halt           0     0     0                    00               
  Transaction    0     0     1     0              01  usesStmtJournal=0
  TableLock      0     2     0     Products       00  iDb=0 root=2 write=0
  String8        0     2     0     rädisa         00  r[2]='rädisa'
  Goto           0     1     0                    00 

SQLite omvandlar frågan till ett internt program som hämtar data från tabellerna. I det här fallet börjar programmet på rad 12 där transaktionen startar. På rad 14 sätts strängen ‘rädisan’ från sökvillkoret in i register 2. Därefter fortsätter programmet på rad 1 där hanteringen av tabellen Products börjar. Raderna 2–9 utgör en loop som letar upp de rader som matchar sökvillkoret i tabellen.

Vi får en mer sammanfattad frågeplan genom att sätta orden EXPLAIN QUERY PLAN framför frågan. Resultatet kan bli följande:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM Products WHERE name = 'rädisa';
0|0|0|SCAN TABLE Products

Här betyder SCAN TABLE Products att frågan går igenom raderna i tabellen Products.

Optimering av frågor

Om en fråga hämtar data från endast en tabell är den vanligtvis enkel att utföra. Utmaningarna uppstår oftast vid frågor som involverar flera tabeller. Databashanteraren måste då kunna optimera körningen av frågan, det vill säga skapa en bra plan som gör det möjligt att samla in den önskade datan effektivt från tabellerna.

Låt oss som exempel titta på följande fråga som listar kursnamn och lärare:

SELECT Courses.name, Teachers.name
FROM Courses, Teachers
WHERE Courses.teacher_id = Teachers.id;

Eftersom frågan behandlar två tabeller kan man tänka sig att den först bildar alla möjliga kombinationer av rader från tabellerna Courses och Teachers, och sedan väljer de rader där villkoret Courses.teacher_id = Teachers.id gäller. Även om detta är ett bra sätt att tänka på, överensstämmer det inte med hur en riktigt bra databashanterare fungerar.

Problemet är att tabellerna Courses och Teachers båda kan innehålla ett stort antal rader. Om till exempel varje tabell har en miljon rader, skulle antalet radkombinationer bli en biljon. Det skulle ta enormt lång tid att skapa och gå igenom alla kombinationer.

I det här fallet måste databashanteraren förstå vad användaren faktiskt vill hämta och hur villkoret i frågan begränsar resultatet. I praktiken räcker det att gå igenom alla rader i tabellen Courses och för varje rad effektivt hitta den önskade raden i tabellen Teachers.

Vi ber SQLite förklara frågeplanen:

sqlite> EXPLAIN QUERY PLAN SELECT Courses.name, Teachers.name FROM Courses, Teachers WHERE Courses.teacher_id = Teachers.id;
0|0|0|SCAN TABLE Courses
0|1|1|SEARCH TABLE Teachers USING INTEGER PRIMARY KEY (rowid=?)

Frågan går igenom raderna i tabellen Courses (SCAN TABLE Courses) och hämtar information från tabellen Teachers via primärnyckeln (SEARCH TABLE Teachers). Det senare betyder att när en viss rad i tabellen Courses behandlas, hämtar frågan effektivt raden i tabellen Teachers där Teachers.id är samma som Courses.teacher_id.

Hur kan man i praktiken hämta raden från tabellen Teachers på ett effektivt sätt? Detta görs genom att använda det index som är kopplat till tabellen.

Index

Ett index är en sökstrukturerad datakonstruktion som lagras tillsammans med en databastabell med syftet att effektivisera utförandet av frågor som rör tabellen. Med hjälp av ett index kan databashanteraren snabbt avgöra var i tabellen rader som matchar ett visst sökvillkor finns.

Man kan tänka på ett index på samma sätt som ett register i slutet av en bok, där det anges på vilka sidor olika sökord förekommer. Med hjälp av registret hittar vi ett visst ords placering mycket snabbare än om vi läste igenom hela boken.

Primärnyckelindex

När en tabell skapas i databasen får dess primärnyckel automatiskt ett index. Tack vare detta kan man effektivt utföra sökningar där villkoret rör primärnyckeln.

När vi i SQLite exempelvis skapar tabellen

CREATE TABLE Products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  price INTEGER
);

får tabellen ett index på kolumnen id, och vi kan effektivt söka efter produkter baserat på ID-nummer. Tack vare detta fungerar till exempel följande fråga effektivt:

SELECT price FROM Products WHERE id = 3;

Vi kan bekräfta detta genom att titta på frågeplanen:

sqlite> EXPLAIN QUERY PLAN SELECT price FROM Products WHERE id = 3;
selectid    order       from        detail                                                   
----------  ----------  ----------  ---------------------------------------------------------
0           0           0           SEARCH TABLE Products USING INTEGER PRIMARY KEY (rowid=?)

Frågeplanen innehåller SEARCH TABLE, vilket betyder att frågan kan hämta data effektivt från tabellen med hjälp av index.

Skapa index

Primärnyckelindexet är praktiskt, men vi kanske också vill söka information baserat på en annan kolumn. Till exempel hämtar följande fråga rader baserat på kolumnen price:

SELECT name FROM Products WHERE price = 4;

Denna fråga är som standard inte effektiv eftersom kolumnen price saknar index. Vi ser detta genom att titta på frågeplanen:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid    order       from        detail             
----------  ----------  ----------  -------------------
0           0           0           SCAN TABLE Products

SCAN TABLE betyder att frågan måste gå igenom alla rader i tabellen. Detta tar länge om tabellen innehåller många rader.

Vi kan skapa ett nytt index som gör frågor som använder kolumnen price mer effektiva. Vi kan skapa indexet med kommandot CREATE INDEX på följande sätt:

CREATE INDEX idx_price ON Products (price);

Här är idx_price namnet på indexet som vi kan referera till senare. Efter skapandet fungerar indexet helt automatiskt, vilket innebär att databashanteraren använder indexet i frågor och uppdaterar det.

Låt oss nu titta på frågeplanen efter att vi har skapat indexet:

sqlite> EXPLAIN QUERY PLAN SELECT name FROM Products WHERE price = 4;
selectid    order       from        detail                                               
----------  ----------  ----------  -----------------------------------------------------
0           0           0           SEARCH TABLE Products USING INDEX idx_price (price=?)

I och med att indexet nu finns används inte längre SCAN TABLE utan istället SEARCH TABLE i planen. Planen visar också att indexet idx_price kommer att användas.

Fler användningsområden

Vi kan också använda index i frågor där vi söker efter mindre eller större värden. Med hjälp av indexet som skapats för kolumnen price kan vi till exempel hitta rader där villkoret price < 3 eller price >= 8 gäller.

Det är också möjligt att skapa index baserat på flera kolumner. Till exempel kan vi skapa ett index på följande sätt:

CREATE INDEX idx_price ON Products (price, name);

I det här indexet är raderna ordnade i första hand efter pris och i andra hand efter namn. Indexet effektiviserar sökningar där sökvillkoret är antingen endast pris eller både pris och namn. Indexet förbättrar däremot inte sökningar där sökvillkoret endast är namn.

Hur fungerar index?

Ett index behöver en stödkonstruktion, en sökstruktur, som gör det möjligt att effektivt hämta rader baserat på kolumnvärden. Detta kan till exempel implementeras som en trädstruktur, där nycklarna utgörs av kolumnvärdena.

Relaterad teori behandlas mer ingående i kursen Datastrukturer och algoritmer i samband med binära sökträd. Typiska trädstrukturer som används i databaser är B-träd och dess varianter.

När ska man skapa index?

I princip kunde man tänka sig att skapa ett index för varje kolumn i tabellen, vilket skulle göra många typer av frågor snabba. Detta är dock inte en bra idé i praktiken.

Även om index gör frågor snabbare finns det också två problem: ett index tar upp utrymme och gör dessutom insättning och ändring av data långsammare. Det senare beror på att när tabellens innehåll ändras måste ändringen uppdateras i alla index som är kopplade till tabellen. Index ska alltså användas med eftertanke och inte skapas i onödan.

En bra anledning att skapa ett index är om vi ofta vill utföra vissa typer av frågor som tar länge eftersom databashanteraren annars måste gå igenom alla rader i tabellen i onödan. I sådana fall kan vi lägga till ett lämpligt index på tabellen, vilket gör att frågorna fungerar effektivt i fortsättningen.

Index har i praktiken stor betydelse för databasers effektivitet. Många databaser fungerar långsamt därför att de saknar viktiga index.

Index är också ett exempel på hur redundant information kan effektivisera frågor. I index lagras dock inte den redundanta informationen i tabellen utan utanför tabellen i en separat sökstruktur.

8. Databasernas teori

Relationsmodellen

SQL-databaser bygger på relationsmodellen, vars teoretiska grund utvecklades på 1970-talet. År 1970 presenterade E. F. Codd i sin artikel A relational model of data for large shared data banks både idén om en databas som använder relationsmodellen och ett allmänt databasspråk för frågor.

Jämfört med tidigare databaser var relationsmodellens styrka dess enkelhet. Med samma tydliga modell kan man på ett naturligt sätt representera olika slags data och genomföra mångsidiga frågor.

Men vad betyder egentligen en relation? Ett enkelt exempel på en relation är den binära relation $<$ alltså “mindre än”. Denna relation definierar en mängd av par där varje par $(a,b)$ uppfyller villkoret $a<b$. Om vi begränsar oss till positiva heltal ser mängden ut på följande sätt:

\[[(1,2),(1,3),(2,3),(1,4),(2,4),(3,4),\dots]\]

En binär (tvåställig) relation innebär att varje element i relationen är ett par. Mer generellt kan man definiera en $k$-ställig relation, där varje element i relationen är en k-tupel av formen $(x_1,x_2,\dots,x_k)$.

I en relationsdatabas representerar en relation en samling information med en viss struktur. Till exempel innehåller följande relation information om produkterna:

\[P = \{(1,rädisa,7), (2,morot,5), (3,rova,4),\\ (4,kålrot,8), (5,selleri,4)\}\]

Denna relation är treställig och dess attribut är:

$id$: produktens ID-nummer
$name$: produktens namn
$price$: produktens pris

Till exempel i tupeln $(1,rädisa,7)$ är attributet $id$ lika med $1$, attribtet $name$ lika med $rädisa$ och attributet $price$ lika med $7$.

Relationen ovan motsvarar databastabellen Products som innehåller information om produkterna:

id	name	price
1	rädisa	7
2	morot	5
3	rova	4
4	kålrot	8
5	selleri	4

En relation är alltså ett matematiskt sätt att beskriva innehållet i en databastabell som en mängd. Varje tupel i relationen motsvarar en rad i tabellen, och antalet attribut i tupeln är detsamma som antalet kolumner i tabellen.

Relationsoperatorer

Med hjälp av relationsoperatorer kan man skapa nya relationer från redan befintliga relationer. Detta motsvarar en SQL-fråga där man skapar en resultattabell från en eller flera tabeller. Tre centrala relationsoperatorer är projektion, selektion och join.

Projektion

Projektion (projection) $\Pi$ skapar en relation som endast innehåller vissa attribut från den ursprungliga relationen. Exempelvis:

\[\Pi_{name}(P) = \{(rädisa),(morot),(rova),(kålrot),(selleri)\}\] \[\Pi_{price}(P) = \{(7),(5),(4),(8)\}\] \[\Pi_{name,price}(P) = \{(rädisa,7),(morot,5),(rova,4),\\(kålrot,8),(selleri,4)\}\]

Observera att eventuella upprepade tupler tas bort i projektionen eftersom en projektion är en relation, det vill säga en mängd. Projektionen $\Pi_{price}(P)$ innehåller således endast fyra tupler, eftersom två produkter har samma pris.

Projektion motsvarar en SQL-fråga som hämtar vissa kolumner från en tabell. Exempelvis projektionen $\Pi_{name}(P)$ motsvarar SQL-frågan SELECT name FROM Products.

Selektion

En selektion (restriction) $\sigma$ skapar en relation som endast innehåller de tupler från den ursprungliga relationen som uppfyller vissa villkor. Exempelvis:

\[\sigma_{name = rova}(P)=\{(3,rova,4)\}\] \[\sigma_{price = 4}(P)=\{(3,rova,4),(5,selleri,4)\}\] \[\sigma_{price \le 5}(P)=\{(2,morot,5),(3,rova,4),(5,selleri,4)\}\]

Selektionen motsvarar en SQL-fråga där rader väljs med WHERE-villkoret. Till exempel motsvarar selektionen $\sigma_{price = 4}(P)$ SQL-frågan SELECT * FROM Products WHERE price = 4.

Genom att kombinera en projektion och en selektion får man en motsvarighet till exempelvis SQL-frågan SELECT name FROM Products WHERE price <= 5:

\[\Pi_{name}(\sigma_{price \le 5}(P))=\{(morot),(rova),(selleri)\}\]

Join

Join (sammanslagning, på engelska: join) $\bowtie$ skapar en relation som innehåller kombinationer av tupler från två relationer. I joinen väljs sådana kombinationer där attributvärdena är desamma i de attribut som förekommer i båda de ursprungliga relationerna. De gemensamma attributen visas endast en gång i joinen.

Vi betraktar som exempel relationerna:

\[E = \{(1,Maija,1),(2,Liisa,1),(3,Kaaleppi,3)\}\] \[C = \{(1,Google),(2,Amazon),(3,Facebook)\}\]

Relationen $E$ representerar de anställda och består av följande attribut:

$eid$: den anställdes ID-nummer
$ename$: den anställdes namn
$cid$: företagets ID-nummer

Relationen $C$ representerar företagen och består av följande attribut:

$cid$: företagets ID-nummer
$cname$: företagets namn

När man skapar joinen ${E\ \bowtie\ C }$ letar man efter kombinationer av tupler där det gemensamma attributet $cid$ är lika. Dessa kombinationer är:

$(1,Maija,1)$ ja $(1,Google)$
$(2,Liisa,1)$ ja $(1,Google)$
$(3,Kaaleppi,3)$ ja $(3,Facebook)$

I joinen samlas attributen $eid$, $ename$, $cid$ och $cname$ på följande sätt:

\[{E\ \bowtie\ C } = \{(1,Maija,1,Google),\\(2,Liisa,1,Google),\\(3,Kaaleppi,3,Facebook)\}\]

Vi kan använda projektion efter joinen på följande sätt:

\[\Pi_{ename,cname}({E\ \bowtie\ C}) = \{(Maija,Google),\\(Liisa,Google),\\(Kaaleppi,Facebook)\}\]

Joinen motsvarar en viss typ av SQL-fråga mellan två tabeller. Till exempel motsvarar de ovanstående relationerna tabellerna Employees och Companies:

id	name	company_id
1	Maija	1
2	Liisa	1
3	Kaaleppi	3

id	name
1	Google
2	Amazon
3	Facebook

Nu motsvarar $\Pi_{ename,cname}({E\ \bowtie\ C})$ följande SQL-fråga:

SELECT Employees.name, Companies.name
FROM Employees, Companies
WHERE Employees.company_id = Companies.id;

Teori vs. praxis

Tabeller i en SQL-databas motsvarar inte fullt ut relationerna i relationsmodellen, utan det finns skillnader mellan tabeller och relationer.

En skillnad är att varje tupel i en relation är unik, medan en tabell i en SQL-databas kan innehålla flera identiska rader. Till exempel kan vi skapa tabellen Test och lägga till tre likadana rader:

sqlite> CREATE TABLE Test (x INTEGER);
sqlite> INSERT INTO Test VALUES (1);
sqlite> INSERT INTO Test VALUES (1);
sqlite> INSERT INTO Test VALUES (1);
sqlite> SELECT * FROM Test;
1
1
1

Ofta har dock en SQL-tabell en kolumn id som garanterar att det inte finns två identiska rader, eftersom varje rad har ett unikt ID-nummer.

En annan skillnad är att varje attribut i en tupel i en relation måste ha ett värde, medan en kolumn i en SQL-tabell kan innehålla NULL, det vill säga värdet saknas.

Det finns också skillnader mellan SQL-frågor och relationsoperatorer. Som vi såg tidigare innehåller projektionen $\Pi_{price}(P)$ varje pris bara en gång, medan resultattabellen av frågan SELECT price FROM Products kan innehålla samma pris flera gånger. I SQL finns det faktiskt två olika sätt att hämta information:

SELECT ALL ...: hämtar alla rader, även dubbletter
SELECT DISTINCT ...: hämtar varje unik rad endast en gång

Det första sättet är standard, i vilket ordet ALL normalt sett inte används. Dubblettrader kan tas bort med hjälp av ordet DISTINCT. Strängt taget motsvaras alltså projektionen $\Pi_{price}(P)$ av frågan SELECT DISTINCT price FROM Products.

I SQL kan ordningen på raderna spela roll, medan tupler i en relation inte har någon ordning. I SQL syns radordningen till exempel när en fråga använder ORDER BY i slutet för att sortera resultattabellen på önskat sätt. Med relationsoperatorer är det inte möjligt att genomföra en sådan sortering.

Nycklar och beroenden

Begrepp som är relaterade till nycklar är:

Supernyckel (superkey) är en kombination av attribut som garanterat är unik för varje tupel i relationen. En supernyckel identifierar alltså varje tupel i relationen.
Kandidatnyckel (candidate key), eller kort sagt nyckel (key), är en minimal supernyckel. Minimalitet innebär att om man tar bort något attribut från supernyckeln, är det inte längre en supernyckel.
Primärnyckel (primary key) är en kandidatnyckel som har valts specifikt till primärnyckel.

Exempel

Låt oss som exempel titta på en relation som beskriver produkter med attributen $id$, $name$ och $price$:

I denna relation är supernycklarna åtminstone $id$, $(id,name)$, $(id,price)$ och $(id,name,price)$. Dessa kombinationer av attribut är supernycklar eftersom de identifierar varje tupel i relationen. Av dessa supernycklar är endast $id$ en nyckel, eftersom de andra supernycklarna inte är minimala.

Attributet $price$ kan inte vara en supernyckel eftersom flera produkter kan ha samma pris. Attributet $name$ kan vara en supernyckel om varje produkt garanterat har ett unikt namn. Kombinationen $(name,price)$ är en supernyckel om det inte kan finnas två produkter med både samma namn och samma pris. Vilka kombinationer av attribut som är supernycklar beror alltså på de antaganden som görs om datan.

Val av nyckel

En nyckel kan vara antingen en naturlig nyckel (natural key) eller en surrogatnyckel (surrogate key). En naturlig nyckel består av ursprunglig information, medan en surrogatnyckel läggs till just för att fungera som nyckel. Till exempel är $(name,price)$ en naturlig nyckel medan $id$ är en surrogatnyckel.

I databasteorin används ofta naturliga nycklar, men i praktiska databaser är nyckeln vanligtvis ett ID-nummer eller motsvarande surrogatnyckel. Fördelen med ett ID-nummer är att det är kompakt information som garanterat fungerar som nyckel. Om man skulle välja en naturlig nyckel måste man överväga om de valda attributen verkligen räcker för att identifiera varje tupel i alla situationer.

Funktionella beroenden

Ett funktionellt beroende (functional dependency) $A \to B$ betyder att attributen $A$ bestämmer attributen $B$. Med andra ord, om det finns två tupler i relationen med samma värden för attributen $A$, måste även attributen $B$ ha samma värden.

Exempelvis om en relation har attributen $postalcode$ och $city$, finns det ett funktionellt beroende $postalcode \to city$ under antagandet att på basis av postnumret kan man bestämma staden. Med andra ord kan det inte finnas två tupler med samma postnummer men olika stad. Om till exempel postnumret är 00560 kan man dra slutsatsen att staden är Helsingfors.

Vad kan man dra för slutsatser av ett postnummer?

Strikt taget kan man i Finland inte härleda staden från ett postnummer, utan endast namnet på postanstalten. Postanstalten är inte nödvändigtvis en stad, och samma postnummer kan täcka områden i flera kommuner. Ett postnummer kan även avse ett företag eller en organisation, som till exempel Helsingfors universitets postnummer 00014. Mer detaljerad information finns på Postens nätsida.

För exemplen i detta kapitel kan man anta att postnumret bestämmer staden fastän det är mer komplicerat i verkligheten. Generellt kan det vara svårt att avgöra om ett funktionellt beroende gäller i alla situationer, även om det verkar så.

Attributen $A$ utgör en supernyckel precis när $A \to B$ gäller för alla attribut $B$. Det betyder att i vilken tupel som helst i relationen identifierar attributen i supernyckeln vilken tupel det handlar om. När en relation exempelvis har attributen $id$, $name$ och $price$, är attributet $id$ en supernyckel eftersom det gäller att $id \to id$, $id \to name$ och $id \to price$.

Normalformer

Normalform (normal form) är ett krav som gäller en databasrelation (tabell) och syftar till att främja dataintegriteten och underlätta användningen av databasen. Teoretiskt bygger normalformerna på liknande idéer som de som behandlades i kapitel 6 om principerna för god design.

De vanligaste normalformerna är första, andra och tredje normalformen.

Första normalformen

En relation uppfyller första normalformen när varje attribut i relationen har ett enkelt (atomärt) värde.

Till exempel uppfyller inte följande relation den första normalformen:

\[\{(Google, (London, Paris, Stockholm)), \\ (Amazon, Amsterdam) \\ (Facebook, (Marseille, Paris))\}\]

Denna relation anger företagskontors placeringar. Problemet ur första normalformens synvinkel är att det andra attributet kan innehålla flera värden. Till exempel innehåller tupeln $(Google, (London, Paris, Stockholm))$ tre värden i det andra attributet.

Problemet kan åtgärdas genom att presentera informationen på ett annat sätt:

\[\{ (Google, London), \\ (Google, Paris), \\ (Google, Stockholm), \\ (Amazon, Amsterdam), \\ (Facebook, Marseille), \\ (Facebook, Paris) \}\]

Relationen uppfyller nu första normalformen eftersom varje attribut innehåller exakt ett värde.

Första normalformen är i databasdesign kopplad till principen om att tabellernas kolumner ska innehålla atomär data.

Andra normalformen

En relation uppfyller andra normalformen när den uppfyller första normalformen och dessutom inte innehåller något funktionellt beroende $A \to B$, där $A$ är en del av en nyckel och $B$ ligger utanför nyckeln.

Andra normalformen är endast relevant när nyckeln består av flera attribut. Så är fallet i följande relation:

\[\{ (Google, London, UK), \\ (Google, Paris, France), \\ (Google, Stockholm, Sweden), \\ (Amazon, Amsterdam, Netherlands), \\ (Facebook, Marseille, France), \\ (Facebook, Paris, France) \}\]

I denna relation är $(name,city)$ en nyckel, eftersom dessa attribut identifierar varje tupel. I relationen finns ett funktionellt beroende $city \to country$, eftersom landet kan härledas från kontorets stad.

Relationen uppfyller inte andra normalformen eftersom det finns ett attribut $country$ utanför nyckeln $(name,city)$ som är beroende av en del av nyckeln, nämligen $city$.

Vi kan uppnå andra normalformen genom att dela upp relationen i två relationer. Den första relationen innehåller företagets namn och kontorets stad:

\[\{ (Google, London), \\ (Google, Paris), \\ (Google, Stockholm), \\ (Amazon, Amsterdam), \\ (Facebook, Marseille), \\ (Facebook, Paris) \}\]

Den andra relationen anger i vilka länder städerna ligger:

\[\{ (Amsterdam, Netherlands), \\ (London, UK), \\ (Marseille, France), \\ (Paris, France), \\ (Stockholm, Sweden) \}\]

Betydelsen av andra normalformen är i praktiken liten, eftersom databastabeller vanligtvis använder ID-nummer som nyckel, och nyckeln inte består av flera kolumner. Den andra normalformen uppfylls därför oftast automatiskt.

Tredje normalformen

En relation uppfyller tredje normalformen om den uppfyller första och andra normalformen och dessutom inte innehåller något funktionellt beroende $A \to B$, där $A$ och $B$ är skilda uppsättningar av attribut utanför nyckeln.

Till exempel uppfyller inte följande relation den tredje normalformen:

\[\{ (1, Liisa, Helsingfors, 00100), \\ (2, Maija, Helsingfors, 00560), \\ (3, Kaaleppi, Esbo, 02600), \\ (4, Uolevi, Helsingfors, 00560) \}\]

Relationens nyckel är attributet $id$. Relation uppfyller inte tredje normalformen eftersom $postalcode$ och $city$ är attribut utanför nyckeln, men det gäller ett funktionellt beroende $postalcode \to city$. Här antar vi återigen att vi kan bestämma staden på basis av postnumret.

Även i detta fall kan vi dela upp relationen i två relationer, varefter tredje normalformen uppfylls. Den första relationen innehåller endast postnummer, men inga städer:

\[\{ (1, Liisa, 00100), \\ (2, Maija, 00560), \\ (3, Kaaleppi, 02600), \\ (4, Uolevi, 00560) \}\]

Den andra relationen kopplar samman postnummer och städer:

\[\{ (00100, Helsingfors), \\ (00560, Helsingfors), \\ (02600, Esbo) \}\]

Tredje normalformen är i databasdesign kopplad till principen om att det inte får finnas redundant information i tabellerna.

Teori vs. praxis

Syftet med normalformerna är att ge ett teoretiskt perspektiv på databasdesign. Om en databas exempelvis inte uppfyller tredje normalformen kan dess struktur behöva förbättras.

I flera normalformer är tanken att minska beroenden i relationerna som kan leda till redundant information. Om en relation inte uppfyller en normalform är lösningen ofta att dela upp relationen i flera relationer, vilket minskar den redundanta informationen.

I praktiken utformas databaser ändå vanligtvis inte med hjälp av normalformer, utan enligt principerna som beskrivs i kapitel 6. Normalformerna illustrerar en del av det tänkesätt som en skicklig databasutformare har.