Databasteknik: Lösningar till tentamen 2016-01-12

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 1 (5 p)

Uppgift 2 (7 p)

Blåbär(nummer, diameter, vikt, kvalitet, buske)
Kvalitetsklasser(nummer, namn, kilopris);
Buskar(nummer, höjd, latitud, longitud)
Räknare(nummer, namn, telefon)
Sköter(buske, räknare)

Primärnycklarna är understrukna. I tabellen Kvalitetsklasser är även namn en kandidatnyckel.

Främmande nycklar:

Blåbär.kvalitet till Kvalitetsklasser.nummer
Blåbär.buske till Buskar.nummer
Sköter.buske till Buskar.nummer
Sköter.räknare till Räknare.nummer

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

drop table "Sköter";
drop table "Räknare";
drop table "Blåbär";
drop table Buskar;
drop table Kvalitetsklasser;

create table Kvalitetsklasser
(nummer integer not null primary key,
namn varchar(11) not null unique,
kilopris float not null);

insert into Kvalitetsklasser (nummer, namn, kilopris)
    values (1, 'Extra prima', 14.90);
insert into Kvalitetsklasser (nummer, namn, kilopris)
    values (2, 'Skräpbär', 1.00);
insert into Kvalitetsklasser (nummer, namn, kilopris)
    values (3, 'Extra bäst', 25.00);

create table Buskar
(nummer integer not null primary key,
"höjd" float not null,
latitud float not null,
longitud float not null);

insert into Buskar (nummer, "höjd", latitud, longitud)
    values (1, 15.2, 59.230, 15.250);
insert into Buskar (nummer, "höjd", latitud, longitud)
    values (2, 25.2, 59.240, 15.250);
insert into Buskar (nummer, "höjd", latitud, longitud)
    values (3, 35.2, 59.250, 15.250);

create table "Blåbär"
(nummer integer not null primary key,
diameter float not null,
vikt float not null,
kvalitet integer not null references Kvalitetsklasser(nummer),
buske integer null references Buskar(nummer));

insert into "Blåbär" (nummer, diameter, vikt, kvalitet, buske)
    values (1, 10.0, 0.8, 1, 1);
insert into "Blåbär" (nummer, diameter, vikt, kvalitet, buske)
    values (2, 8.0, 0.8, 1, 3);
insert into "Blåbär" (nummer, diameter, vikt, kvalitet, buske)
    values (3, 10.0, 0.8, 2, 3);
insert into "Blåbär" (nummer, diameter, vikt, kvalitet, buske)
    values (4, 10.0, 0.1, 1, null);
insert into "Blåbär" (nummer, diameter, vikt, kvalitet, buske)
    values (5, 20.0, 0.4, 2, null);

create table "Räknare"
(nummer integer not null primary key,
namn varchar(11) not null,
telefon varchar(11) not null);

insert into "Räknare" (nummer, namn, telefon)
    values (1, 'Anna', '123456');
insert into "Räknare" (nummer, namn, telefon)
    values (2, 'Bob', '123457');
insert into "Räknare" (nummer, namn, telefon)
    values (3, 'Cho', '123456');

create table "Sköter"
(buske integer not null references Buskar(nummer),
"räknare" integer not null references "Räknare"(nummer),
primary key (buske, "räknare"));

insert into "Sköter" (buske, "räknare") values (1, 1);
insert into "Sköter" (buske, "räknare") values (1, 2);
insert into "Sköter" (buske, "räknare") values (1, 3);
insert into "Sköter" (buske, "räknare") values (2, 2);
insert into "Sköter" (buske, "räknare") values (3, 3);

Uppgift 3 (3 p)

Ge tre exempel på integritetsvillkor av olika typer som kan vara bra att ha i din databas från uppgifterna ovan.

Nyckelvillkor, som att kolumnen nummer i tabellen Blåbär måste ha ett unikt värde för varje rad.
Referensintegritetsvillkor, som att om det står i kolumnen buske i tabellen Blåbär att ett blåbär växer på en buske med ett viss nummer, så ska det också finnas en buske med det numret i tabellen Buskar.
Allmänna semantiska villkor, som att kolumnen longitud i tabellen Buske ska ha värden mellan -180 grader och +180 grader. Om databasen bara handlar om en viss skog, kan man begränsa värdena mycket mer.

Uppgift 4 (10 p)

a) Vad är kilopriset för blåbär av kvalitetsklassen som heter Extra bäst?

select kilopris from Kvalitetsklasser where namn = 'Extra bäst';

b) Här har vi blåbär nummer 123456789. Det sitter på en blåbärsbuske. Hur hög är busken?

select Buskar."höjd" from "Blåbär", Buskar
where "Blåbär".buske = Buskar.nummer
and "Blåbär".nummer = 123456789;

select "höjd" from Buskar
where nummer in (select buske
                 from "Blåbär"
                 where nummer = 123456789);

select Buskar."höjd"
from "Blåbär" join Buskar on "Blåbär".buske = Buskar.nummer
and "Blåbär".nummer = 123456789;

c) Här, på latitud 59.250 och longitud 15.250, finns en blåbärsbuske. Vad heter den eller de blåbärsräknare som är ansvariga för den?

select "Räknare".namn
from Buskar, "Sköter", "Räknare"
where Buskar.nummer = "Sköter".buske
and "Sköter"."räknare" = "Räknare".nummer
and Buskar.latitud = 59.250
and Buskar.longitud = 15.250;

select namn
from "Räknare"
where nummer in (select "räknare"
                 from "Sköter"
                 where buske in (select nummer
                                 from Buskar
                                 where latitud = 59.250
                                 and longitud = 15.250));

d) Hur många blåbär sitter inte på någon buske?

select count(*)
from "Blåbär"
where buske is null;

Observera att eftersom alla jämförelser med NULL ger resultatet falskt, så ger den här frågan fel svar om man har NULL-värden i kolumnen buske:

select count(*)
from "Blåbär"
where buske not in (select nummer from Buskar);

e) Vad är det sammanlagda priset för alla blåbär i skogen?

select sum(vikt * kilopris / 1000)
from "Blåbär", Kvalitetsklasser
where "Blåbär".kvalitet = Kvalitetsklasser.nummer;

Uppgift 5 (5 p)

a) Lämpliga databashanterare som nämnts i kursen: Mimer, MySQL, Microsoft SQL Server

b) En mindre lämplig databashanterare: Microsoft Access. Den klarar inte de stora datamängderna, med miljarder rader i tabellen Blåbär.

c) Lämpliga index:

Blåbär.nummer
Buskar.nummer

Ger inga poängavdrag, men hjälper troligen inte:

Kvalitetsklasser.namn -- tabellen är förmodligen mycket liten
Blåbär.buske -- kopplingen i joinen görs förmodligen åt andra hållet

Några kolumner som inte ska ha index:

Kvalitetsklasser.kilopris -- används inte för att söka
Buskar.höjd -- används inte för att söka
Buskar.latitud -- nämns överhuvudtaget inte i frågorna
Räknare.nummer -- den tabellen används inte i frågorna

Uppgift 6 (5 p)

a) Vilka fullständiga funktionella beroenden finns i tabellen?

Nr till Diameter
Nr till Vikt
Nr till Kvalitetsklass
Nr till Kvalitetsnamn
Nr till Kilopris
Kvalitetsklass till Kvalitetsnamn
Kvalitetsklass till Kilopris
Kvalitetsnamn till Kvalitetsklass
Kvalitetsnamn till Kilopris

b) Vilken är den högsta normalform, av 1NF, 2NF, 3NF och BCNF, som tabellen uppfyller?

2NF

c) Varför uppfyller tabellen inte den närmast högre normalformen?

3NF tillåter inte ffb mellan ickenyckelattribut, och det finns flera sådana, exempelvis från Kvalitetsklass till Kvalitetsnamn.

d) Beskriv något problem som finns i den här tabellen, och som man skulle slippa med en högre normalform.

Redundans: För varje blåbär som tillhör en viss kvalitetsklass måste man lagra inte bara den kvalitetsklassens nummer, utan också dess namn och kilopris.
Vissa saker går inte att lagra: Om det för tillfället inte finns några blåbär som tillhör en viss kvalitetsklass, går det inte att lagra data om den kvalitetsklassen.

Exempel på fel svar:

Redundans: Upprepningen 0.8 0.8 0.8 i kolumnen Vikt.

Uppgift 7 (5 p)

Vad är det för skillnad på:

a) en primärnyckel och en kandidatnyckel

En kandidatnyckel är en kolumn, eller kombination av kolumner, som är garanterad att alltid ha unika värden, dvs två rader kan inte ha samma värden i kolumnen eller kolumnkombinationen. En primärnyckel är en kandidatnyckel som man valt ut för att använda i första hand.

b) en kandidatnyckel och ett index

Det är helt olika saker. En kandidatnyckel är en logisk egenskap hos en tabell (unikhet, enligt delfråga a ovan) medan ett index är en intern datastruktur i databashanteraren, som den använder för att sökningar ska gå snabbare.

c) hållbarhet (D:et i ACID) och isolering (I:et i ACID)

Hållbarhet gäller för en transaktion, och betyder att när transaktionen avslutats och "committats", så får dess ändringar inte försvinna ur databasen. Isolering gäller när man har flera samtidiga transaktioner, och betyder att även fast flera transaktioner läser och ändrar i databasen samtidigt, så ska varje transaktion (i idealfallet) fungera som om den var ensam i databasen, så ingen transaktion ser någon annan transaktions halvfärdiga ändringar, eller skriver över någon annans ändringar.

d) en främmande nyckel och en kandidatnyckel

En kandidatnyckel är en logisk egenskap hos en tabell (unikhet, enligt delfråga a ovan), medan en främmande nyckel är en kolumn eller kombination av kolumner som refererar till en kandidatnyckel i en annan (eller ibland samma) tabell, för att koppla ihop rader i de två tabellerna.

e) en databashanterare och en databasadministratör

Den största skillnaden är att databashanteraren är ett program, medan databasadministratören är en människa! Databashanteraren är det program, eller system av program, som hanterar datat i databasen, och gör det möjligt att söka och ändra i datat. Databasadministratören är den person, eller ibland en grupp av personer, som ansvarar för driften av databasen.

Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), 22 januari 2016