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)

ER-diagram för uppgift 1

Rättningskommentarer:

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

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: 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:
  1. Enkelt! Mycket enklare att bygga datahanteringen, jämfört med ett traditionellt programmeringsspråk.
  2. Kraftfullt! Även komplicerade saker (som sökningar och integritetsvillkor) går att göra på ett enkelt sätt.
  3. Flexibelt! Lätt att ändra. Fysiskt och logiskt dataoberoende.
  4. Databasteknik möjliggör samtidig åtkomst av data.
  5. Databasteknik möjliggör återställande efter krascher.
  6. Databasteknik gör det lättare att möta olika användares behov.
  7. Databasteknik möjliggör bättre säkerhet.
Rättningskommentar:

Uppgift 5 (5 p)

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

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

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

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:

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