Databasteknik: Vissa lösningar till tentamen 2016-13-15

Uppgift 1 (5 p)

Uppgift 2 (7 p)

Alla kandidatnycklar, såväl primärnycklar som alternativnycklar, är understrukna. Notera att i Innehåller bildar pizza och ingrediens en sammansatt kandidatnyckel. Om det finns alternativnycklar i en tabell är primärnyckeln den som står först.

Främmande nycklar:

Pizzor.prisklass till Prisklasser.nummer
Innehåller.pizza till Pizzor.nummer
Innehåller.ingrediens till Ingredienser.nummer

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

create table Pizzabagare
(nummer integer not null primary key,
namn varchar(10) not null,
telefon varchar(13) not null);

insert into Pizzabagare (nummer, namn, telefon)
    values (1, 'Luigi', '070-73 47 013');
insert into Pizzabagare (nummer, namn, telefon)
    values (2, 'Emilio', '019-94639');
insert into Pizzabagare (nummer, namn, telefon)
    values (3, 'Anna', '070-73 47 013');

create table Prisklasser
(nummer integer not null primary key,
pris integer not null);

insert into Prisklasser (nummer, pris)
    values (1, 60);
insert into Prisklasser (nummer, pris)
    values (2, 70);
insert into Prisklasser (nummer, pris)
    values (3, 70);
insert into Prisklasser (nummer, pris)
    values (4, 80);

create table Pizzor
(nummer integer not null primary key,
namn varchar(25) not null unique,
prisklass integer not null references Prisklasser(nummer));

insert into Pizzor (nummer, namn, prisklass)
    values (1, 'Bearnaise-symfoni', 1);
insert into Pizzor (nummer, namn, prisklass)
    values (2, 'Lyx-kebaborama', 3);
insert into Pizzor (nummer, namn, prisklass)
    values (3, 'Entrecote-båt', 3);

create table Ingredienser
(nummer integer not null primary key,
namn varchar(20) not null unique);

insert into Ingredienser (nummer, namn)
    values (1, 'rostade champinjoner');
insert into Ingredienser (nummer, namn)
    values (2, 'kantareller');

create table "Innehåller"
(ID integer not null primary key,
pizza integer not null references Pizzor(nummer),
ingrediens integer not null references Ingredienser(nummer),
unique(pizza, ingrediens));

insert into "Innehåller" (ID, pizza, ingrediens)
    values (1, 1, 1);
insert into "Innehåller" (ID, pizza, ingrediens)
    values (2, 2, 1);
insert into "Innehåller" (ID, pizza, ingrediens)
    values (3, 3, 1);
insert into "Innehåller" (ID, pizza, ingrediens)
    values (4, 1, 2);
insert into "Innehåller" (ID, pizza, ingrediens)
    values (5, 3, 2);

select * from Pizzabagare;
select * from Prisklasser;
select * from Pizzor;
select * from Ingredienser;
select * from "Innehåller";

Uppgift 3 (10 p)

select namn
from Pizzabagare
where telefon = '070-73 47 013';

b) Här har vi en pizza som heter Entrecote-båt. Vad kostar den?

Några olika förslag:

select Prisklasser.pris
from Pizzor, Prisklasser
where Pizzor.prisklass = Prisklasser.nummer
and Pizzor.namn = 'Entrecote-båt';

select pris
from Pizzor, Prisklasser
where prisklass = Prisklasser.nummer
and namn = 'Entrecote-båt';

select Prisklasser.pris
from Pizzor join Prisklasser on Pizzor.prisklass = Prisklasser.nummer
where Pizzor.namn = 'Entrecote-båt';

select pris
from Prisklasser
where nummer in (select prisklass
                 from Pizzor
                 where namn = 'Entrecote-båt');

c) Vad heter de ingredienser som pizzan Entrecote-båt innehåller?

select Ingredienser.namn
from Pizzor, "Innehåller", Ingredienser
where Pizzor.nummer = "Innehåller".pizza
and "Innehåller".ingrediens = Ingredienser.nummer
and Pizzor.namn = 'Entrecote-båt';

d) Hur många pizzor finns i prisklass 1? Vi vill alltså veta antalet, inte vilka det är.

Två förslag:

select count(*)
from Pizzor, Prisklasser
where Pizzor.prisklass = Prisklasser.nummer
and Prisklasser.nummer = 1;

select count(*)
from Pizzor
where prisklass = 1;

e) Vad heter den dyraste pizza som innehåller kantareller?

create view Kantarellpizzor as
select Pizzor.namn, Prisklasser.pris
from Pizzor, Prisklasser, "Innehåller", Ingredienser
where Pizzor.prisklass = Prisklasser.nummer
and Pizzor.nummer = "Innehåller".pizza
and "Innehåller".ingrediens = Ingredienser.nummer
and Ingredienser.namn = 'kantareller';

select * from Kantarellpizzor;

select namn
from Kantarellpizzor
where pris = (select max(pris) from Kantarellpizzor);

Eller utan vyn (rekommenderas inte):

select Pizzor.namn
from Pizzor, Prisklasser, "Innehåller", Ingredienser
where Pizzor.prisklass = Prisklasser.nummer
and Pizzor.nummer = "Innehåller".pizza
and "Innehåller".ingrediens = Ingredienser.nummer
and Ingredienser.namn = 'kantareller'
and Prisklasser.pris =
(select max(Prisklasser.pris)
from Pizzor, Prisklasser, "Innehåller", Ingredienser
where Pizzor.prisklass = Prisklasser.nummer
and Pizzor.nummer = "Innehåller".pizza
and "Innehåller".ingrediens = Ingredienser.nummer
and Ingredienser.namn = 'kantareller');

Om man har en databashanterare där det är enkelt att få fram bara ett visst antal rader i resultatet, som MySQL med LIMIT eller Microsoft SQL Server med TOP, kan man använda det i stället för att jämföra med maxvärdet, och allt kan skrivas som en enda fråga. Exempel:

select top 1 Pizzor.namn
from Pizzor, Prisklasser, "Innehåller", Ingredienser
where Pizzor.prisklass = Prisklasser.nummer
and Pizzor.nummer = "Innehåller".pizza
and "Innehåller".ingrediens = Ingredienser.nummer
and Ingredienser.namn = 'kantareller'
order by Prisklasser.pris desc;

Uppgift 4 (3 p)

Index gör att sökningar går snabbare. Med så lite data och så begränsad användning behövs knappast några index alls, och i en vanlig diskbaserad databas gör de förmodligen inte heller någon skillnad.

b) Föreslå en databashanterare som kan vara lämplig att använda. Motivera varför den är lämplig!

Vilken som helst som är billig, enkel att installera och lättanvänd, till exempel MySQL (med ett lämpligt gränssnittsprogram) eller Microsoft Access. I det här fallet behöver man förmodligen inte ens någon databashanterare, utan skulle klara sig bra med till exempel en textfil.

Uppgift 5 (5 p)

Uppgift 6 (5 p)