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)

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:

Nr -> Namn
Nr -> Gatuadress
Nr -> Stad

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:

Tradgardsmastare.Nr - används fyra gånger
Tradgardsmastare.Kontor - används två gånger
Arbete.Kund - används en gång
Arbete.Tradgardsmastare - används tre gånger
Kontor.Nr - används två gånger
Kontor.Stad - används en gång, men är ett textfält
Kund.Nr - används två gånger
Kund.Namn - används en gång, men är ett textfält som kanske är brett
Kund.Stad - används en gång, men är ett textfält
Privatperson.Nr - används en gång

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):

Mer arbete för applikationsprogrammeraren, eftersom man inte kan överlåta det mesta jobbet på databashanteraren.
Det är svårt för programmeraren att tänka på alla ställen som måste kontrolleras. Tänk till exempel på hur många olika typer av ändringar som kan orsaka att referensintegritet bryts.
Reglerna finns inte samlade och i explicita, utan de är utspridda i en massa programkod, kanske i många olika program. Ska man förstå dem måste man läsa programkoden och sluta sig till vad det är för villkor som den försöker upprätthålla.
Det är svårt att ändra reglerna, eftersom man måste leta igenom alla existerande applikationsprogram, luska ut vilka delar av dem som kontrollerar reglerna, och sen ändra i alla programmen.

Fördelar med att kontrollera integritetsvillkor procedurellt i ett applikationsprogram:

Man kan göra mer komplicerade kontroller än vad som går att ange deklarativt i de flesta databashanterare.
Man kan hantera ett brott mot integritetsvillkoren på ett mer komplicerat sätt. (Att sätta ihop ett bra felmeddelande att visa för användaren är bara ett exempel på detta.)

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