Tabeller, med primärnyckeln understruken:
Klubbar(ID, Namn, Adress)
Skyttar(ID, Personnummer, Namn, Adress)
Medlemmar(ID, Skytt, Klubb)
Tävlingar(ID, Namn, Datum, Klubb)
Serier(ID, Poäng, Datum, Skytt, Tävling)
Namn är en alternativnyckel i Klubbar.
Personnummer är en alternativnyckel i Skyttar.
Skytt och Klubb bildar en sammansatt alternativnyckel i Medlemmar.
Namn och Datum bildar en sammansatt alternativnyckel i Tävlingar.
Medlemmar.Skytt refererar till Skyttar.ID
Medlemmar.Klubb refererar till Klubbar.ID
Tävlingar.Klubb refererar till Klubbar.ID
Serier.Skytt refererar till Skyttar.ID
Serier.Tävling refererar till Tävlingar.ID
SQL-kommandon för att provköra:
drop table Serier; drop table Tavlingar; drop table Medlemmar; drop table Skyttar; drop table Klubbar; create table Klubbar (ID integer not null primary key, Namn varchar(10) not null unique, Adress varchar(10)); create table Skyttar (ID integer not null primary key, Personnummer char(12) not null unique, Namn varchar(10), Adress varchar(10)); create table Medlemmar (ID integer not null primary key, Skytt integer not null references Skyttar(ID), Klubb integer not null references Skyttar(ID), unique(Skytt, Klubb)); create table Tavlingar (ID integer not null primary key, Namn varchar(10) not null, Datum date not null, unique(Namn, Datum), Klubb integer not null references Klubbar(ID)); create table Serier (ID integer not null primary key, Poang integer not null, Datum date not null, Skytt integer not null references Skyttar(ID), Tavling integer references Tavlingar(ID)); insert into Klubbar values (1, 'En klubb', 'Vallen'); insert into Klubbar values (2, 'Klubbis', 'Skogen'); insert into Klubbar values (3, 'Klubb Tre', 'Åkern'); insert into Skyttar values (1, '196312111658', 'Anna', 'Gränden 1'); insert into Skyttar values (2, '196312111659', 'Bodil', 'Vägen 7'); insert into Skyttar values (3, '196312111660', 'Carl', 'Vägen 7'); insert into Medlemmar values (1, 1, 1); insert into Medlemmar values (2, 1, 2); insert into Medlemmar values (3, 1, 3); insert into Medlemmar values (4, 2, 1); insert into Medlemmar values (5, 2, 2); insert into Medlemmar values (6, 3, 3); insert into Tavlingar values (1, 'Julpanget', DATE'2012-12-24', 1); insert into Tavlingar values (2, 'Julpanget', DATE'2013-12-24', 1); insert into Tavlingar values (3, 'Sommarpang', DATE'2013-06-20', 2); insert into Serier values (1, 50, DATE'2013-12-24', 1, 1); insert into Serier values (2, 48, DATE'2013-12-24', 1, 1); insert into Serier values (3, 49, DATE'2013-12-24', 1, 1); insert into Serier values (4, 50, DATE'2013-12-23', 1, null); insert into Serier values (5, 47, DATE'2013-12-23', 1, null); insert into Serier values (6, 47, DATE'2012-12-24', 1, 2); insert into Serier values (7, 40, DATE'2013-12-24', 2, 2); insert into Serier values (8, 32, DATE'2013-12-24', 2, 2); insert into Serier values (9, 41, DATE'2013-12-24', 2, 2); select * from Klubbar; select * from Skyttar; select * from Medlemmar; select * from Tavlingar; select * from Serier;
BCNF kräver att första normalformen är uppfylld, dvs att alla attribut är atomära, samt att varje determinant ska vara en kandidatnyckel. Det finns fyra fullständiga funktionella beroenden i tabellen: ID till Namn, ID till Adress, Namn till ID, Namn till Adress. Determinanter är alltså ID och Namn. Bägge dessa är kandidatnycklar. Alltså uppfyller tabellen BCNF.
a) Det bor några skyttar på adressen Vägen 7. Vad har de för personnummer och namn?
select Personnummer, Namn from Skyttar where Adress = 'Vägen 7';
b) Vad heter de skyttar som har skjutit minst en 50-serie, oavsett om det är på tävling eller träning?
Ett annat alternativ:select distinct Skyttar.Namn from Skyttar, Serier where Skyttar.ID = Serier.Skytt and Serier.Poang = 50;
Ett tredje alternativ:select distinct Namn from Skyttar, Serier where Skyttar.ID = Serier.Skytt and Poang = 50;
select Namn
from Skyttar
where ID in (select Skytt
from Serier
where Poang = 50);
Ett fjärde alternativ:
select distinct Skyttar.Namn from Skyttar join Serier on Skyttar.ID = Serier.Skytt where Serier.Poang = 50;
c) Vad heter de skyttar som har skjutit minst en 50-serie på tävling?
select distinct Skyttar.Namn from Skyttar, Serier where Skyttar.ID = Serier.Skytt and Serier.Poang = 50 and Serier.Tavling is not null;
d) Finns det några skyttar som aldrig har missat tian på en tävling, dvs som bara skjutit 50-serier på de tävlingar de varit med i? Vi vill veta de skyttarnas namn.
select Namn
from Skyttar
where ID not in (select Skytt
from Serier
where Poang <> 50
and Tavling is not null)
(Resultatet från den SQL-frågan innehåller även de skyttar som aldrig varit med i någon tävling.
De har ju aldrig missat tian på en tävling.)
e) Hur många serier har varje skytt skjutit, totalt? Vi vill ha ett resultat som för varje skytt innehåller skyttens personnummer och namn, och antalet serier. Skyttar som inte skjutit några serier alls ska också vara med, med antalet noll.
select Personnummer, Namn, count(Serier.ID) as Antal from Skyttar left join Serier on Skyttar.ID = Serier.Skytt group by Personnummer, Namn;
f) Vad heter den skytt som vann tävlingen Julpanget 24 december 2013? Det är alltså den skytt som sammanlagt fick högst antal poäng på de serier som han eller hon sköt under den tävlingen.
create view Julpangsresultat as
select Serier.Skytt, sum(Poang) as Poangsumma
from Skyttar, Serier, Tavlingar
where Skyttar.ID = Serier.Skytt
and Serier.Tavling = Tavlingar.ID
and Tavlingar.Namn = 'Julpanget'
and Tavlingar.Datum = DATE'2013-12-24'
group by Serier.Skytt;
select Namn
from Skyttar
where ID in (select Skytt
from Julpangsresultat
where Poangsumma = (select max(Poangsumma)
from Julpangsresultat));
Ett index är en fysisk datastruktur som gör att det går snabbare att hitta en rad i en tabell, om man vet värdet på en viss kolumn på den raden. (Man kan generalisera och även tala om filer, poster och fält, i stället för tabeller, rader och kolumner.)
b) Vad är det som skiljer ett index från en nyckel?
En nyckel är en logisk egenskap hos tabellen, medan ett index är en del av hur databashanteraren lagrar och hanterar tabellen internt. En nyckel är en kolumn, eller en kombination av kolumner, som alltid kommer att vara unik. Det kan alltså aldrig finnas två olika rader i tabellen som har samma värde på nyckeln. Man kan skapa ett index på en kolumn, eller kombination av kolumner, som inte är unik, men det kan förstås inte vara en nyckel.
c) De flesta databashanterarna skapar automatiskt index på de primärnycklar som man angett. Varför har databashanterartillverkarna valt att låta sina databashanterare göra så?
Huvudsakligen för att kontrollen av unikhet ska gå snabbare. men kanske också för att det är så vanligt att man söker eller joinar just på primärnyckeln i en tabell. Eftersom primärnyckeln är unik är det dessutom bra att indexera på den, eftersom ett index på en unik kolumn eller kombination av kolumner ger hög selektivitet.
Unikhet: När man angett en kolumn, eller en kombination av kolumner, som primärnyckel (eller bara unik), måste databashanteraren kontrollera att inga ändringar som man gör (med insert eller update) bryter mot det villkoret. Om det inte finns något index på nyckeln, måste databashanteraren leta igenom hela tabellen vid varje sådan ändring.
d) Databasen växer så att varje tabell innehåller många miljoner rader. Det finns inga index alls i databasen, inte ens på primärnycklar. De fyra SQL-frågorna a-d i uppgiften ovan körs många gånger. Vilka index bör man skapa för att just de frågorna ska gå snabbt att köra?
Skyttar.Adress
Skyttar.ID
Serier.Skytt
Serier.Poang
Serier.Tavling
Det är de kolumner som används för att hitta rader i tabellerna, antingen vid enkel sökning eller i samband med en join. Skyttar.Personnummer och Skyttar.Namn ska inte ha index, för de används inte för att hitta rader.
Förmodligen kommer databashanterarens optimerare att välja exekveringsplaner för fråga b, c och d som börjar med att leta i serietabellen, och då kommer ingen sökning efter rader att göras på kolumnen Serier.Skytt. Därför är det rimligt att inte välja att skapa index på den kolumnen.
select * from Apelsiner; -- Fråga nummer 1
ID Vikt 1 10
select * from Apelsiner; -- Fråga nummer 2
ID Vikt 1 10 2 20
(Beroende på vilken metod för isolering mellan transaktioner som databashanteraren använder, kan körningen av fråga nummer 2 komma att fördröjas tills klient 1 gjort rollback. Resultatet kommer alltid att bli som ovan.)
select * from Apelsiner; -- Fråga nummer 3
ID Vikt 1 10 2 20 3 10
select * from Apelsiner; -- Fråga nummer 4
ID Vikt 1 10 2 20
select * from Apelsiner; -- Fråga nummer 5
ID Vikt 1 10 2 20
select * from Apelsiner; -- Fråga nummer 6
ID Vikt 1 10 2 20 4 20
select * from Apelsiner; -- Fråga nummer 7
ID Vikt 1 10 2 20 4 100
(Beroende på vilken metod för isolering mellan transaktioner som databashanteraren använder, kan körningen av fråga nummer 7, och även av klient 1:s insert-kommando innan, komma att fördröjas tills klient 2 gjort commit. Då kan också insert-kommandot misslyckas med ett felmeddelande, klient 1:s transaktion avbrytas, och fråga 7 ge samma svar som fråga 6.)
b) Man kommer att få felmeddelanden från databashanteraren? Vilka, när och varför?
Båda klienterna försöker samtidigt lägga in varsin apelsin med med id-nummer 4. Eftersom id-numret är primärnyckel, kan inte båda apelsinerna läggas in i databasen. Exakt vad som händer varierar beroende på vilken metod för isolering mellan transaktioner som databashanteraren använder.
I Mimer, som använder optimistisk transaktionshantering, kommer det första commit-kommandot, i klient 2, att lyckas, och den transaktionens apelsin sparas i databasen. Det andra commit-kommandot, i klient 1, kommer misslyckas med ett felmeddelande om att det uppstått en konflikt med en annan transaktion.
I en databashanterare som använder pessimistisk transaktionshantering, dvs lås, kan det i stället bli så att körningen av klient 1:s insert-kommando fördröjs tills klient 2 gjort commit, och därmed släpper sitt lås. Då kommer klient 1:s insert-kommando att misslyckas med ett felmeddelande, klient 1:s transaktion avbryts, och fråga 7 kommer att ge samma svar som fråga 6.)