Databasteknik: Lösningar till tentamen 2012-08-22

Uppgift 1 (5 p)

ER- och EER-diagram kan ritas på flera olika sätt. Om du använder en annan notation än kursboken, måste du förklara den notation som du använder.

Uppgift 2 (5 p)

Du behöver inte skriva create table-kommandon i SQL, men du ska ange vilka relationer som finns och vilka attribut varje relation innehåller. Ange också alla kandidatnycklar, vilken av dessa som är primärnyckel, samt vilka referensattribut som finns och vad de refererar till.

Implementationen ska vara bra.

Uppgift 3 (10 p)

a) Det finns bara en deltagare som heter Anders And. Vad har han för startnummer?

b) Det finns bara en deltagare som heter Bengt Berg. Hur lång är sträckan som han anmält sig till?

c) Det finns bara en deltagare som heter Cecilia Citron. Vilka kontroller har han passerat, och vid vilka tidpunkter? Vi vill ha en lista sorterad efter tiderna.

d) Hur många deltagare har anmält sig till sträckan 40 kilometer?

e) Finns det några deltagare som passerat en kontroll som han eller hon inte borde ha passerat? Dvs, en kontroll som inte hör till den marschsträckan som han eller hon är anmäld till? Vi vill veta dessa deltagares namn och nummer.

Uppgift 4 (4 p)

Nijmegen-arrangörerna har följt dina lösningar i uppgifterna ovan, så nu har de den databasen i drift. Men de är oroliga för att databasen ska räcka till för belastningen. Beskriv vad de bör tänka på, och hur de bör göra!

Uppgift 5 (3 p)

Uppgift 6 (5 p)

a) Vi tycker det här med SQL verkar krångligt, med alla "select" och "from" och vad det nu var. Hur ska våra frivilliga funktionärer klara av det?

b) Tänk om två deltagare får samma startnummer? Det skulle ju bli kaos! Hur ska vi hantera det?

c) Förra året gick strömmen mitt under marschen. Då stängs ju datorerna av, och alla data försvinner! Hur ska vi hantera det?

Uppgift 1 (5 p)

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

Uppgift 2 (5 p)

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

I Fika bildar Person, Kopp och Paus 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)

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.

c) 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 två olika personer inte kan använda samma kaffekopp under en och samma kaffeapus.

Uppgift 4 (9 p)

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)