Databaskonstruktion: Lösningar till tentamen 2004-05-29

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

ER-diagram för uppgift 1

Uppgift 2 (5 p)

Tabeller:
Kund(Nr, Namn, Gatuadress, Stad)
Privatperson(Nr)
Foretag(Nr, Kontaktperson)
Kontor(Nr, Gatuadress, Stad)
Tradgardsmastare(Nr, Namn, Kontor)
Arbete(Nr, Typ, Datum, Kund, Tradgardsmastare)

create table-kommandon, som visar primärnyckel och referensattribut:

create table Kund
(Nr integer primary key,
Namn varchar(10),
Gatuadress varchar(10),
Stad varchar(10));

create table Privatperson
(Nr integer primary key references Kund(Nr));

create table Foretag
(Nr integer primary key references Kund(Nr),
Kontaktperson varchar(10));

create table Kontor
(Nr integer primary key,
Gatuadress varchar(10),
Stad varchar(10));

create table Tradgardsmastare
(Nr integer primary key,
Namn varchar(10),
Kontor integer references Kontor(Nr));

create table Arbete
(Nr integer primary key,
Typ varchar(10),
Datum varchar(10),
Kund integer references Kund(Nr),
Tradgardsmastare integer references Tradgardsmastare(Nr));
Några exempeldata:
insert into Kund values (1, 'Adam', 'Vägen 7', 'Alby');
insert into Kund values (2, 'Bengt', 'Gatan 3', 'Brunflo');
insert into Kund values (3, 'AB Sten', 'Stigen 17', 'Örebro');

insert into Privatperson values (1);
insert into Privatperson values (2);

insert into Foretag values (3, 'Lotta');

insert into Kontor values (1, 'Torget 1', 'Örebro');
insert into Kontor values (2, 'Platsen 2', 'Stockholm');
insert into Kontor values (3, 'Plazan 3', 'Stockholm');
insert into Kontor values (4, 'Allén 4', 'Stockholm');
insert into Kontor values (5, 'Gärdet 5', 'Brunflo');

insert into Tradgardsmastare values (1, 'Zorro', 1);
insert into Tradgardsmastare values (2, 'Zeke', 5);
insert into Tradgardsmastare values (3, 'Zapata', 5);
insert into Tradgardsmastare values (4, 'Zven', 5);

insert into Arbete values (1, 'Klippning', '2004-05-01', 1, 1);
insert into Arbete values (2, 'Klippning', '2004-05-01', 2, 2);
insert into Arbete values (3, 'Klippning', '2004-05-01', 3, 3);
insert into Arbete values (4, 'Klippning', '2004-05-02', 3, 4);
insert into Arbete values (5, 'Klippning', '2004-05-02', 1, 1);

Uppgift 3 (3 p)

Som exempel tar vi tabellen Kund. Den uppfyller BCNF.

Vi antar att tabellen endast får innehålla atomära värden, så man får exempelvis inte mata in Örebro/Kumla/Hallsberg som värde på Stad om man har ett kontor som sköter om alla tre städerna.

Följande tre fullständiga funktionella beroenden är alla som finns:

Definitionen för BCNF säger att tabellen bara får innehålla atomära värden, och att varje determinant måste vara en kandidatnyckel. Den enda determinant som finns är attributet Nr, och eftersom det attributet är primärnyckel är det också en kandidatnyckel. Alltså uppfyller tabellen villkoren för BCNF.

Uppgift 4 (10 p)

a (1p)
select Tradgardsmastare.Nr, Tradgardsmastare.Namn
from Tradgardsmastare, Kontor
where Tradgardsmastare.Kontor = Kontor.Nr
and Kontor.Stad = 'Brunflo';
b (2p)
select Tradgardsmastare.Nr, Tradgardsmastare.Namn
from Tradgardsmastare, Arbete, Kund
where Tradgardsmastare.Nr = Arbete.Tradgardsmastare
and Arbete.Kund = Kund.Nr
and Kund.Namn = 'Bengt Bengtsson'
and Kund.Stad = 'Bälinge';
c (3p)
create view AntalArbeten(Tradgardsmastare, Antal)
as select Tradgardsmastare.Nr, count(*)
from Arbete, Tradgardsmastare
where Arbete.Tradgardsmastare = Tradgardsmastare.Nr
group by Tradgardsmastare.Nr;

select Nr, Namn
from Tradgardsmastare
where Nr = (select Tradgardsmastare from AntalArbeten
            where Antal = (select max(Antal) from AntalArbeten));
d (2p)
select Stad
from Kontor
where Nr not in (select Kontor
                 from Tradgardsmastare
                 where Nr in (select Tradgardsmastare from Arbete));
e (2p)
select Namn from Kund, Privatperson where Privatperson.Nr = Kund.Nr
union
select Namn from Tradgardsmastare;

Uppgift 5 (5 p)

Följande kolumner bör (förmodligen) ha index:

Tänk på att "används" betyder används för sökningar, dvs i where-villkor och motsvarande, inte i uppräkningen av attribut som ska skrivas ut. Läs mer om index på http://www.ida.liu.se/~tompa/databaser/prestanda.html.

Uppgift 6 (5 p)

ODBC och dess alternativ beskrivs på webbsidan http://www.aass.oru.se/~tpy/dbk/2003-2004-p34/hemligt/bok/sql-inuti/. (Kräver lösenord. Både användarnamnet och lösenordet är namnet på kursen, skrivet med enbart små bokstäver.)

Uppgift 7 (5 p)

Läs om integritetsvillkor på http://www.ida.liu.se/~tompa/databaser/integritet.html. Där kan man hitta följande nackdelar med att kontrollera integritetsvillkor procedurellt i ett applikationsprogram, (även om de i den texten är formulerade som fördelar med att ange integritetsvillkoren deklarativt, och sen använda sig av databashanterarens automatiska kontroll): Fördelar med att kontrollera integritetsvillkor procedurellt i ett applikationsprogram:


Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se) 5 juni 2004