Blatt 6

Lehrstuhl für Praktische Informatik III
Prof. Dr. Carl-Christian Kanne
Email: [email protected]
Norman May
B6, 29, Raum C0.05
68131 Mannheim
Telefon: (0621) 181–2517
Email: [email protected]
Datenbanksysteme 1
Sommersemester 06
6. Übungsblatt
7. Juni 2006
Aufgabe 1
27 Punkte
Formulieren Sie folgende Anfrage in SQL:
Aufgabe 1 a)
5 Punkte
Geben Sie den Namen des Landes mit dem tiefsten See an.
Lösung
select l.name
from
land l, landesteil lt, geo gewaesser g, see s
where l.l id = lt.l id
and
lt.lt id = g.lt id
and
g.g id = s.g id
and
s.tiefe =
(select max(tiefe)
from see);
Aufgabe 1 b)
5 Punkte
Geben Sie die Namen aller Kontinente aus, in denen (laut Datenbank) keine Ebenen
existieren. (Sortieren Sie die Namen alphabetisch.)
Lösung
select
from
except
select
from
where
name
kontinent
k.name
kontinent k, umfasst u, land l, landesteil lt, geo ebene e
k.k id = u.k id
1
and
and
and
u.l id = l.l id
lt.l id = l.l id
lt.lt id = e.lt id;
Aufgabe 1 c)
5 Punkte
Geben Sie den Namen und die Höhe – in dieser Reihenfolge – des höchsten Berges
von Japan an.
Lösung
with berg japan AS
(select b.b id
from
berg b, geo Berg gb, landesteil lt, land l
where b.b id = gb.b id
and
lt.lt id = gb.lt id
and
lt.l id = l.l id
and
l.Name = ’Japan’)
select b.name, b.hoehe
from
berg b, berg japan bj
where b.b id = bj.b id
and
b.hoehe =
(select max(b2.hoehe)
from berg b2, berg japan bj2
where bj2.b id = b2.b id);
Aufgabe 1 d)
6 Punkte
Geben Sie für jedes Gebirge den Namen und die Höhe des höchsten Berges an. Im
Ergebnis soll also der Gebirgsname, der Name des höchsten Berges, und zuletzt die
Höhe dieses Berges stehen. Sortieren Sie die Gebirgsnamen alphabetisch (aufsteigend).
Lösung
select b1.gebirge, b1.name, b1.hoehe
from
berg b1,
(select b2.gebirge, max(b2.hoehe) as hoechsterberg
from
berg b2
group by b2.gebirge) b3
where b1.gebirge = b3.gebirge
and
b1.hoehe = b3.hoechsterberg
2
order by b1.gebirge;
Aufgabe 1 e)
6 Punkte
Berechnen Sie für jede Staatsform das durchschnittliche Bruttosozialprodukt (BSP)
und sortieren Sie Staatsformen absteigend nach dem durchschnittlichen BSP. Geben Sie
zuerst die Staatsform und anschließend das durchschnittliche Bruttosozialprodukt aus.
Geben Sie nur die Staatsformen aus, deren durchschnittliches BSP über dem durchschnittlichen BSP aller Länder liegt.
Lösung
select
from
group by
having
order by
staatsform, avg(BSP) as AVG BSP
land
staatsform
avg(BSP) > (select avg(BSP) from land)
avg BSP desc;
Aufgabe 2
Formulieren Sie folgende Anfragen in SQL:
Aufgabe 2 a)
Geben Sie alle Flüsse an, die direkt in den atlantischen Ozean münden.
Lösung
select
from
where
and
and
and
g2.name
fluss f, muendet m, gewaesser g, gewaesser g2
f.g id = m.g id
m.muendung g id = g.g id
g.name = ’Atlantischer Ozean’
f.g id = g2.g id;
Aufgabe 2 b)
Geben Sie alle Flüsse an, die direkt oder indirekt in den atlantischen Ozean münden.
Lösung
3
with rekfluss(g id, name) as
((select g2.g id, g2.name
from
fluss f, muendet m, gewaesser g, gewaesser g2
where f.g id = m.g id
and
m.muendung g id = g.g id
and
g.name = ’Atlantischer Ozean’
and
f.g id = g2.g id)
union all
(select g.g id, g.name
from
rekfluss r, fluss q, muendet m, gewaesser g
where q.g id = m.g id
and
m.muendung g id = r.g id
and
q.g id = g.g id))
select name
from rekfluss;
Aufgabe 3
Gegeben sei die Relation Sicherheitsrat mit den Attributen MitgliedsNr und
Name (siehe Abbildung 1). Die ständigen Mitglieder (mit den Mitgliedsnummern 1 bis
5) dürfen weder gelöscht noch geändert werden. Die anderen Tupel dürfen gelöscht
bzw. geändert werden. SQL erlaubt nur die Vergabe von Rechten auf der Ebene von
Schemaelementen, z.B. Tabellen, Views, u.ä. aber nicht auf der Ebene von Tupeln. Wie
realisieren Sie diesen Lösch- bzw. Änderungsschutz für die ersten fünf Tupel mit Hilfe
von SQL-Konstrukten?
Sicherheitsrat
MitgliedsNr Name
1
USA
2
Rußland
England
3
4
Frankreich
5
China
6
Japan
7
Kenia
8
Portugal
9
Schweden
...
...
Abbildung 1: Relation für UN-Sicherheitsrat
Lösung
4
Es gibt mehrere Möglichkeiten diesen Lösch- und Änderungsschutz in SQL zu realisieren:
• mit Triggern
• per referentieller Integrität
• per Views/Sichten
1. Trigger:
create trigger nodelete
after delete on Sicherheitsrat
for each row
when (old.MitgliedsNr < 6)
begin
insert into Sicherheitsrat values(old.MitgliedsNr, old.Name);
end
bzw.
create trigger nodelete
before delete on Sicherheitsrat
for each row
when (old.MitgliedsNr < 6)
begin
Fehlermeldung ausgeben !!
end
und
create trigger noupdate
after update on Sicherheitsrat
for each row
when (old.MitgliedsNr < 6)
begin
set new.MitgliedsNr = old.MitgliedsNr;
set new.Name = old.Name;
end
bzw.
5
create trigger noupdate
before update on Sicherheitsrat
for each row
when (old.MitgliedsNr < 6)
begin
Fehlermeldung ausgeben !!
end
2. referentielle Integrität:
create table lock
(
Nummer integer not null,
Name varchar(100) not null,
foreign key (Nummer, Name)
references Sicherheitsrat(MitgliedsNr, Name)
);
In die Tabelle lock werden die ersten fünf Tupel von Sicherheitsrat eingetragen.
Die Tabelle lock wird (für normale Benutzer) schreibgeschützt.
Nachteil: Fremdschlüssel können nur auf Schlüsselattributen deklariert werden.
D.h. entweder müssen in Sicherheitsrat alle Attribute zu Schlüsselattributen gemacht werden, oder der Änderungsschutz für die Nichtschlüsselattribute ist nicht
gewährleistet.
3. Sichten:
create view permanent(MitgliedsNr, Name) as
(
select distinct MitgliedsNr, Name
from Sicherheitsrat
where MitgliedsNr < 6
);
create view temporary(MitgliedsNr, Name) as
(
select MitgliedsNr, Name
from Sicherheitsrat
where MitgliedsNr > 5
);
create view total(MitgliedsNr, Name) as
(
(select MitgliedsNr, Name from permanent)
union
6
(select MitgliedsNr, Name from temporary)
);
Anfragen werden an die Sicht total gestellt. Änderungen müssen an der Sicht
temporary ausgeführt werden. Die Sicht permanent ist durch das Schlüsselwort
distinct nicht update-fähig.
Nachteil: Anfragen und Änderungen werden an zwei verschiedenen Sichten (mit
zwei verschiedenen Namen) ausgeführt.
7