Databasteknik: Lösningar till tentamen 2011-03-03

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.

Uppgift 1 (5 p)

Alternativt kan man ersätta entitetstypen Fika med ett trevägssamband mellan Person, Kopp och Paus.

Uppgift 2 (5 p)

Relationer, med primärnycklarna understrukna:

Person(Nr, Namn)
Telefonnummer(Person, Telefonnummer)
Kopp(Nr, Färg, Storlek, Beskrivning)
Paus(Nr, Datum, Starttid, Sluttid)
Fika(Nr, Person, Kopp, Paus)

I tabellen Fika bildar Paus och Person en sammansatt alternativnyckel, eftersom det stod i uppgiften att varje person bara använder en kaffekopp per fikapaus. Om man också tänker sig att varje kopp bara används av en person per fikapaus, bildar även Paus och Kopp en sammansatt alternativnyckel.

Referensattribut:

Telefonnummer.Person till Person.Nr
Fika.Person till Person.Nr
Fika.Kopp till Kopp.Nr
Fika.Paus till Paus.Nr

SQL-kommandon för att provköra:

drop table Fika;
drop table Paus;
drop table Kopp;
drop table Telefon;
drop table Person;

create table Person
(Nr integer not null primary key,
Namn varchar(10) not null);

insert into Person values (1, 'Adam');
insert into Person values (2, 'Bertil');
insert into Person values (3, 'Cesar');
insert into Person values (4, 'David');
insert into Person values (5, 'Erik');

create table Telefon
(Id integer not null primary key,
Person integer references Person(Nr),
Telefonnummer varchar(10) not null,
unique(Person, Telefonnummer));

insert into Telefon values (1, 1, 'A-1');
insert into Telefon values (2, 1, 'A-2');
insert into Telefon values (3, 1, 'A-3');
insert into Telefon values (4, 2, 'B-1');
insert into Telefon values (5, 2, 'B-2');
insert into Telefon values (6, 3, 'C');

create table Kopp
(Nr integer not null primary key,
Farg varchar(10) not null,
Storlek integer not null,
Beskrivning varchar(10) not null);

insert into Kopp values (1, 'Grön', 10, 'Ful');
insert into Kopp values (2, 'Grön', 10, 'Fin');
insert into Kopp values (3, 'Blå', 10, 'Ful');
insert into Kopp values (4, 'Blå', 10, 'Fin');
insert into Kopp values (5, 'Gul', 10, 'Sned');

create table Paus
(Nr integer not null primary key,
Datum date not null,
Starttid time not null,
Sluttid time not null,
unique(Datum, Starttid, Sluttid));

insert into Paus values (1, DATE'2012-03-03', TIME'10:10:00', TIME'10:25:00');
insert into Paus values (2, DATE'2012-03-03', TIME'12:00:00', TIME'12:27:00');
insert into Paus values (3, DATE'2012-03-03', TIME'15:10:00', TIME'15:45:00');

create table Fika
(Nr integer not null primary key,
Person integer references Person(Nr),
Kopp integer references Kopp(Nr),
Paus integer references Paus(Nr),
unique(Person, Kopp, Paus));

insert into Fika values (1, 1, 1, 1);
insert into Fika values (2, 1, 1, 2);
insert into Fika values (3, 2, 2, 1);
insert into Fika values (4, 2, 3, 2);
insert into Fika values (5, 2, 4, 3);
insert into Fika values (6, 3, 3, 1);

Uppgift 3 (5 p)

a) Ett integritetsvillkor är en regel som talar om vilka data som kan lagras i databasen, till exempel en regel som säger att varje anställd måste ha ett unikt nummer, eller regeln att varje bil måste ha en och endast en person som ägare.

b) Genom att den som skapar databasen formulerar integritetsvillkor och anger dem för databashanteraren, kan databashanteraren automatiskt upprätthålla dessa villkor. Det underlättar för dem som arbetar med databasen, eftersom de inte själva behöver kontrollera villkoren varje gång de gör en ändring i databasen, och eftersom de kan lita på att villkoren alltid är uppfyllda. Det gäller både den som sitter och arbetar direkt mot databasen med SQL-kommandon, och den som skriver ett applikationsprogram som arbetar mot databasen.

c) Några förslag:

Med domänintegritetsvillkor kan man garantera att kolumnerna i tabellen innehåller rätt typ av data, till exempel att det inte står Kalle i kolumnen Fikapaus.Starttid, som ska innehålla en tidsangivelse.
Med nyckelintegritetsvillkor kan man garantera att numren i tabellerna är unika, exempelvis så att två personer aldrig kan ha samma nummer.
Med referensintegritetsvillkor kan man garantera att referensattribut alltid har en rad att referera till, exempelvis så att en person som nämns i tabellen Fika faktiskt finns (i tabellen Person).
Integritetsvillkor kan även vara mer generella, till exempel villkoret att ingen person som har mer än ett telefonnummer får använda en kaffekopp som tidigare använts av en person utan något telefonnummer alls, om den kaffekoppen inte är gul. (Att ange detta integritetsvillkor i Mimer lämnas som övning till läsaren...)

Uppgift 4 (9 p)

a) Det finns bara en Anna Andersson som jobbar här. Vilket, eller vilka, telefonnummer har hon?

select Telefonnummer
from Person, Telefon
where Person.Nr = Telefon.Person
and Person.Namn = 'Anna Andersson';

b) Det finns bara en Bo Berg som jobbar här. Vilka gröna kaffekoppar har han någon gång använt?

select distinct Kopp.Nr
from Person, Kopp, Fika
where Person.Nr = Fika.Person
and Person.Namn = 'Bo Berg'
and Kopp.Nr = Fika.Kopp
and Kopp.Farg = 'Grön';

c) Finns det några kaffekoppar som aldrig är använda? Vi vill veta de kaffekopparnas nummer och färg.

select Nr, Farg
from Kopp
where Nr not in (select Kopp from Fika);

d) Vad heter den person som har deltagit vid flest fikapauser?

create view AntalFikapauser as
select Person.Nr, Person.Namn, count(*) as Antal
from Fika, Person
where Fika.Person = Person.Nr
group by Person.Nr, Person.Namn;

select Namn
from AntalFikapauser
where Antal = (select max(Antal) from AntalFikapauser);

Uppgift 5 (3 p)

...

Uppgift 6 (5 p)

...

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@oru.se), 8 mars 2012