Databasteknik: Vissa lösningar till tentamen 2014-08-20

Observera att detta är förslag på lösningar. Det kan finnas andra lösningar som också är korrekta, och det kan hända att en del av lösningarna är mer omfattande än vad som krävs för full poäng på uppgiften. En del av lösningarna är kanske inte fullständiga, utan hänvisar bara till var man kan läsa svaret. Dessutom har det inträffat i världshistorien att lärare skrivit fel i lösningsförslagen.

Uppgift 2 (6 p)

drop table Matcher;
drop table GillarDryck;
drop table Spelare;
drop table Grafikkort;
drop table Energidrycker;
drop table Lag;

create table Lag
(nummer integer not null primary key,
namn varchar(13) not null unique);

insert into Lag (nummer, namn) values (1, 'SK Telecom T1');
insert into Lag (nummer, namn) values (2, 'Andra laget');
insert into Lag (nummer, namn) values (3, 'Tredje laget');
insert into Lag (nummer, namn) values (4, 'Fjärde laget');
insert into Lag (nummer, namn) values (5, 'Femte laget');

create table Energidrycker
(nummer integer not null primary key,
namn varchar(10) not null unique,
tillverkare varchar(12) not null);

insert into Energidrycker (nummer, namn, tillverkare) values (1, 'Hot6', 'Lotte');
insert into Energidrycker (nummer, namn, tillverkare) values (2, 'Red Bull', 'Red Bull');
insert into Energidrycker (nummer, namn, tillverkare) values (3, 'Battery', 'Sinebrychoff');
insert into Energidrycker (nummer, namn, tillverkare) values (4, 'Fyran', 'Bolaget AB');
insert into Energidrycker (nummer, namn, tillverkare) values (5, 'Femman', 'Bolaget AB');

create table Grafikkort
(nummer integer not null primary key,
modell varchar(30) not null unique,
pris integer not null);

insert into Grafikkort (nummer, modell, pris) values (1, 'Nvidia Geforce GTX Titan Z', 25099);
insert into Grafikkort (nummer, modell, pris) values (2, 'Kort 2', 100);
insert into Grafikkort (nummer, modell, pris) values (3, 'Kort 3', 100);
insert into Grafikkort (nummer, modell, pris) values (4, 'Kort 4', 100);
insert into Grafikkort (nummer, modell, pris) values (5, 'Kort 5', 100);

create table Spelare
(nummer integer not null primary key,
namn varchar(10) not null unique,
lag integer not null references Lag(nummer),
favoritkort integer not null references Grafikkort(nummer));

insert into Spelare (nummer, namn, lag, favoritkort) values (1, 'Kim', 1, 1);
insert into Spelare (nummer, namn, lag, favoritkort) values (2, 'Kim 2', 1, 1);
insert into Spelare (nummer, namn, lag, favoritkort) values (3, 'Kim 3', 1, 2);
insert into Spelare (nummer, namn, lag, favoritkort) values (4, 'Kim 4', 1, 3);
insert into Spelare (nummer, namn, lag, favoritkort) values (5, 'Kim 5', 2, 1);
insert into Spelare (nummer, namn, lag, favoritkort) values (6, 'Kim 6', 3, 1);

create table GillarDryck
(spelare integer not null references Spelare(nummer),
dryck integer not null references Energidrycker(nummer),
primary key (spelare, dryck));

insert into GillarDryck (spelare, dryck) values (1, 1);
insert into GillarDryck (spelare, dryck) values (2, 1);
insert into GillarDryck (spelare, dryck) values (2, 2);
insert into GillarDryck (spelare, dryck) values (2, 3);
insert into GillarDryck (spelare, dryck) values (3, 1);
insert into GillarDryck (spelare, dryck) values (4, 2); 
insert into GillarDryck (spelare, dryck) values (5, 2); 

create table Matcher
(nummer integer not null primary key,
datum char(10) not null,
spelare1 integer not null references Spelare(nummer),
spelare2 integer not null references Spelare(nummer),
check (spelare1 <> spelare2),
vinnare integer not null);

insert into Matcher (nummer, datum, spelare1, spelare2, vinnare) values (1, '2014-08-20', 1, 2, 1);
insert into Matcher (nummer, datum, spelare1, spelare2, vinnare) values (2, '2014-08-20', 1, 2, 2);
insert into Matcher (nummer, datum, spelare1, spelare2, vinnare) values (3, '2014-08-20', 1, 2, 1);
insert into Matcher (nummer, datum, spelare1, spelare2, vinnare) values (4, '2014-08-20', 1, 2, 1);
insert into Matcher (nummer, datum, spelare1, spelare2, vinnare) values (5, '2014-08-21', 1, 3, 1);

Uppgift 4 (12 p)

a) (1p)

select nummer from Spelare where namn = 'Kim';

b) (2p)

select Lag.namn
from Spelare, Lag
where Spelare.namn = 'Kim'
and Spelare.lag = Lag.nummer;

select L.namn
from Spelare as S, Lag as L
where S.namn = 'Kim'
and S.lag = L.nummer;

select Lag.namn
from Lag
where nummer = (select lag from Spelare where namn = 'Kim');

select Lag.namn
from Spelare join Lag on Spelare.lag = Lag.nummer
where Spelare.namn = 'Kim';

c) (2p)

select Energidrycker.namn
from Spelare, GillarDryck, Energidrycker
where Spelare.nummer = GillarDryck.spelare
and GillarDryck.dryck = Energidrycker.nummer
and Spelare.namn = 'Kim';

d) (2p)

select datum
from Matcher, Spelare
where Spelare.nummer = Matcher.spelare1
or Spelare.nummer = Matcher.spelare2
and Spelare.namn = 'Kim';

e) (3 p)

create view SpelarePerLag
as select lag, count(*) as antal
from Spelare
group by lag;

select * from SpelarePerLag;

select max(antal) from SpelarePerLag;

select Lag.namn
from Lag, SpelarePerLag
where Lag.nummer = SpelarePerLag.lag
and SpelarePerLag.antal = (select max(antal) from SpelarePerLag);

f) (2p)

select Lag.namn, count(Spelare.nummer) as antal
from Lag left outer join Spelare on Lag.nummer = Spelare.lag
group by Lag.namn;

Uppgift 5 (6 p)

c) Vi antar att frågorna a, b och c i uppgift 4 körs ofta, och vi behöver snabba upp just dessa frågor. Vilka index behöver vi skapa? Databashanteraren som vi använder skapar inga index automatiskt, inte ens på primärnycklar.

create index Spelarnamn on Spelare(namn);

create index Spelarlag on Spelare(lag); -- kanske inte
create index Lagnummer on Lag(nummer);

create index Spelarnummer on Spelare(nummer); -- kanske inte
create index Gillandespelare on GillarDryck(spelare);
create index Gilladdryck on GillarDryck(dryck); -- kanske inte
create index Drycknummer on Energidrycker(nummer);

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), 21 augusti 2014