Databaskonstruktion: Lösningar till tentamen 2005-03-14

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

Rättningskommentarer:

Man kan klara sig med en enda typ av samtal, för N:M-sambandstypen Deltagare klarar ju även av en enda mottagare. SQL-frågorna blir enklare då. Alternativt kan man ha två sorters samtal, men låta Deltagare sitta på basklassen.
Uppringaren kan vara med bland deltagarna, eller inte.
Arvet kan ritas som ett enkelt arv också (utan att ange fullständig specialisering och disjunkta subklasser).
Abonnemangsformerna bör inte modelleras med arv och subklasser, för det stod ju i uppgiften att vilka former som finns kommer att ändras ofta. (-2p) Då är det bättre att ha det som data än i schemat.
Samtalstid kan göras som ett härlett attribut.

Uppgift 2 (5 p)

Tabeller:

Abonnent(Nr, Namn, Adress)
Abonnemangsform(Nr, Namn)
Abonnemang(Nr, Telefonnummer, Ägare, Form)
Samtal(Nr, Starttid, Sluttid, Samtalstid, Kostnad, Uppringare)
VanligtSamtal(Nr, Mottagare)
Konferenssamtal(Nr)
Deltagare(Konferenssamtal, Abonnemang)

Rättningskommentar:

Här har jag lagt till en heltalsnyckel i Abonnemang, för att slippa en massa referensattribut som är breda textfält.
create table-kommandon, som visar primärnyckel och referensattribut. (Provkört med MySQL under Linux, och där måste man ange att tabellernas internformat ska vara InnoDB för att foreign key-kontrollen ska fungera.)

create table Abonnent
(Nr integer,
Namn varchar(10),
Adress varchar(10),
primary key (Nr)) engine=InnoDB;

create table Abonnemangsform
(Nr integer,
Namn varchar(10),
primary key (Nr)) engine=InnoDB;

create table Abonnemang
(Nr integer,
Telefonnummer varchar(11),
Ägare integer,
Form integer,
primary key (Nr),
unique (Telefonnummer),
foreign key (Ägare) references Abonnent (Nr),
foreign key (Form) references Abonnemangsform (Nr)) engine=InnoDB;

create table Samtal
(Nr integer,
Starttid timestamp,
Sluttid timestamp,
Samtalstid integer,
Kostnad float,
Uppringare integer,
primary key (Nr),
foreign key (Uppringare) references Abonnemang (Nr)) engine=InnoDB;

create table VanligtSamtal
(Nr integer,
Mottagare integer,
primary key (Nr),
foreign key (Nr) references Samtal (Nr),
foreign key (Mottagare) references Abonnemang (Nr)) engine=InnoDB;

create table Konferenssamtal
(Nr integer,
primary key (Nr),
foreign key (Nr) references Samtal (Nr)) engine=InnoDB;

create table Deltagare
(Konferenssamtal integer,
Abonnemang integer,
primary key (Konferenssamtal, Abonnemang),
foreign key (Konferenssamtal) references Konferenssamtal (Nr),
foreign key (Abonnemang) references Abonnemang (Nr)) engine=InnoDB;

Några exempeldata:

Tabellen Abonnent:
+----+---------+-----------+
| Nr | Namn    | Adress    |
+----+---------+-----------+
|  1 | Adam    | Vägen 7   |
|  2 | Bengt   | Gatan 3   |
|  3 | AB Sten | Stigen 17 |
+----+---------+-----------+

Tabellen Abonnemangsform:
+----+---------+
| Nr | Namn    |
+----+---------+
|  1 | Privat  |
|  2 | Företag |
|  3 | Kamrat  |
|  4 | Ovän    |
+----+---------+

Tabellen Abonnemang:
+----+---------------+-------+------+
| Nr | Telefonnummer | Ägare | Form |
+----+---------------+-------+------+
|  1 | 019-94639     |     1 |    1 |
|  2 | 0707-347013   |     2 |    2 |
|  3 | 013-174590    |     3 |    3 |
|  4 | 90510         |     1 |    4 |
|  5 | 118118        |     2 |    4 |
+----+---------------+-------+------+

Tabellen Samtal:
+----+---------------------+---------------------+------------+---------+------------+
| Nr | Starttid            | Sluttid             | Samtalstid | Kostnad | Uppringare |
+----+---------------------+---------------------+------------+---------+------------+
|  1 | 2005-02-14 10:59:23 | 2005-02-14 10:59:25 |          2 |     0.5 |          1 |
|  2 | 2005-02-14 11:59:23 | 2005-02-14 11:59:25 |          2 |     0.5 |          1 |
|  3 | 2005-02-14 12:59:23 | 2005-02-14 12:59:25 |          2 |     0.5 |          1 |
+----+---------------------+---------------------+------------+---------+------------+

Tabellen VanligtSamtal:
+----+-----------+
| Nr | Mottagare |
+----+-----------+
|  1 |         2 |
|  2 |         3 |
+----+-----------+

Tabellen Konferenssamtal:
+----+
| Nr |
+----+
|  3 |
+----+

Tabellen Deltagare;
+-----------------+------------+
| Konferenssamtal | Abonnemang |
+-----------------+------------+
|               3 |          2 |
|               3 |          3 |
|               3 |          4 |
+-----------------+------------+

Uppgift 3 (10 p)

a) (1p)

select Abonnent.Namn
from Abonnemang, Abonnent
where Abonnemang.Ägare = Abonnent.Nr
and Abonnemang.Telefonnummer = '0707-347013';

+-------+
| Namn  |
+-------+
| Bengt |
+-------+

b) (2p)

select distinct Namn
from Abonnemang uppringt_abonnemang, VanligtSamtal, Samtal,
     Abonnemang uppringande_abonnemang, Abonnent
where uppringt_abonnemang.Telefonnummer = '0707-347013'
and uppringt_abonnemang.Nr = VanligtSamtal.Mottagare
and VanligtSamtal.Nr = Samtal.Nr
and Samtal.Uppringare = uppringande_abonnemang.Nr
and uppringande_abonnemang.Ägare = Abonnent.Nr;

+------+
| Namn |
+------+
| Adam |
+------+

Rättningskommentar:

Man kan (som i svaret ovan) tolka "ringa upp" som att det bara handlar om vanliga samtal, eller som att även konferenssamtal ska räknas med.

c) (3p)

select Abonnent.Namn, sum(Samtal.Kostnad)
from Abonnent, Abonnemang, Samtal
where Abonnent.Nr = Abonnemang.Ägare
and Abonnemang.Nr = Samtal.Uppringare
group by Abonnent.Nr
having sum(Samtal.Kostnad) > 1000;

d) (2p)

select count(*)
from Abonnemang
where Nr not in (select Uppringare from Samtal)
and Nr not in (select Mottagare from VanligtSamtal)
and Nr not in (select Abonnemang from Deltagare);

Rättningskommentar:

Till skillnad från i (b) bör man rimligen ta med konferenssamtal här.
e) (2p)

select Abonnemangsform.Namn, count(*)
from Abonnemang, Abonnemangsform
group by Abonnemangsform.Namn;

Uppgift 4 (5 p)

Fördelar med att använda en databashanterare:

Enkelt! Mycket enklare att bygga datahanteringen, jämfört med ett traditionellt programmeringsspråk.
Kraftfullt! Även komplicerade saker (som sökningar och integritetsvillkor) går att göra på ett enkelt sätt.
Flexibelt! Lätt att ändra. Fysiskt och logiskt dataoberoende.
Databasteknik möjliggör samtidig åtkomst av data.
Databasteknik möjliggör återställande efter krascher.
Databasteknik gör det lättare att möta olika användares behov.
Databasteknik möjliggör bättre säkerhet.

Rättningskommentar:

De tre första punkterna räcker för full poäng, om de är bra förklarade.

Uppgift 5 (5 p)

a) (2p) Fördelar med vyer:

Lättare att skriva komplicerade SQL-frågor, eftersom man kan formulera dem stegvis.
Bättre logiskt dataoberoende, eftersom en vy kan se likadan ut även om de tabeller den baseras på skulle ändras.
Bättre anpassning efter olika användares behov, och olika användningars, eftersom de kan få anpassade vyer.
Vyer kan även användas med grant för finkorning åtkomstkontroll.

b) (1.5p) Fördelar med lagrade procedurer:

Kan ge bättre prestanda vid avancerade körningar, eftersom all behandling kan ske i servern, och man slipper skeppa data fram och tillbaka till klienten.
Kan ge större flexibilitet, eftersom olika klienter, skrivna i olika programmeringsspråk, kan köra samma lagrade procedurer.

c) (1.5p) Fördelar med triggers:

Ger möjlighet att skriva komplicerade integritetsvillkor.
Kan ge bättre skydd mot oavsiktliga inkonsistenser, eftersom de aktiva reglerna alltid finns där och alltid körs när de ska.

Uppgift 6 (10 p)

a) (2p)

Index på Namn och Avdelning:

create index PersonalNamn on Personal(Namn);
create index PersonalAvdelning on Personal(Avdelning);

b) (5p: 3p för problemen, 1p för teorin, 1p för den bättre lösningen)

Tabellen bryter mot tredje normalformen, i och med att det finns ett fullständigt funktionellt beroende från Avdelning till Avdelningschef. Nackdelar med det:

Redundans i och med att uppgiften om vem som är chef för en avdelning måste upprepas för varje person som är chef. Förutom att det tar onödig plats, såväl lagringsmässigt som i svaren på en del frågor, leder det lätt till inkonsistenser i databasen.
Vissa saker går inte att lagra, vilket kan leda till borttagnings- och insätningsanomalier. Om man till exempel tillfälligt inte har någon personal på dataavdelningen, så försvinner uppgiften att vi har en dataavdelning och vem som är chef.
Det kan vara svårt att första vad tabellen egentligen betyder. Är det där med avdelningschef något som varierar med olika personer, eller är det gemensamt för alla på en viss avdelning?

Det är bättre att dela upp tabellen i två. För att slippa en primärnyckel och ett referensattribut som är ett långt textfält väljer jag också att införa ett avdelningsnummer:

Personal

Anställningsnummer	Namn	Telefon	Chef	Avdelning
1	Bob Boss	4711	null	Huvudkontoret
2	Cecilia Ceder	1234	2	Huvudkontoret
3	Anders And	1235	2	Huvudkontoret
4	Lotta Lind	5551	3	Dataavdelningen
5	David Dahl	5552	4	Dataavdelningen
6	Erik Ek	5553	4	Dataavdelningen

Avdelning

Nr	Namn	Chef
1	Huvudkontoret	1
2	Dataavdelningen	4

c) (1p)

Den första frågan ser likadan ut, och den andra ser ut så här:

select Personal.Namn
from Personal, Avdelning
where Personal.Avdelning = Avdelning.Nr
and Avdelning = 'Dataavdelningen';

d) (2p)

Skapa i stället index på Personal.Namn, Avdelning.Namn, Avdelning.Nr och Personal.Avdelning:

create index PersonalNamn on Personal(Namn);
create index AvdelningNamn on Avdelning(Namn);
create index AvdelningNr on Avdelning(Nr);
create index PersonalAvdelning on Personal(Avdelning);

Indexet på Avdelning.Nr behövs förmodligen inte, för den kolumnen är primärnyckel, och de flesta databashanterare skapar automatiskt index på primärnyckeln.

Kanske behövs inga index alls i tabellen Avdelning, för den är förmodligen ganska liten.

Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se) 3 april 2005