Datenbanksysteme I, SS 2004

Universität Mannheim
Lehrstuhl für Praktische Informatik III
Norman May
D7 27, Raum 410
68131 Mannheim
Telefon: (0621) 181-2586
Email: [email protected]
Datenbanksysteme I, SS 2004
Lösungen zum 6. Übungsblatt
1. SQL-Aufgaben: UniDB
Formulieren Sie folgende Anfragen in SQL:
(a) In der Annahme, dass die Fachgebiete eines Professors alle Fachgebiete seiner
Assistenten umfasst, bestimmen Sie für alle Professoren die Anzahl der Fachgebiete. Achten Sie auch darauf, dass es nach dieser Definition Professoren ohne
Fachgebiet geben kann.
(b) Geben Sie alle C3-Professoren aus, die eine Vorlesung halten, die eine Vorlesung
direkt voraussetzt, welche von einem C4-Professor gelesen wird.
(c) Bestimmen Sie eine Rangliste der von Professoren gehaltenen Vorlesungen. D.h.
nummerieren Sie die Namen der Professoren, nach der Anzahl der gehaltenen
Vorlesungen. Professoren mit den meisten Vorlesungen haben den kleinsten
Rang. Sortieren Sie die Rangliste absteigen.
Lösungsvorschlag
(a) Der erste Teil der Anfrage findet nur Professoren mit Assistenten.
(SELECT count(DISTINCT a.fachgebiet) as Fachgebiete, p.name
FROM assistenten a, professoren p
WHERE a.boss = p.persnr
GROUP BY p.name)
UNION
(SELECT 0 as Fachgebiete, p.name
FROM Professoren p
WHERE NOT EXISTS
(SELECT * FROM Assistenten a
WHERE a.boss = p.persnr))
ORDER BY Fachgebiete DESC;
Implizite Gruppierung:
1
SELECT (SELECT COUNT(DISTINCT a.fachgebiet)
FROM assistenten
WHERE a.boss = p.persnr)) as Fachgebiete, p.name
FROM professoren p
ORDER BY Fachgebiete DESC;
Mit Outer Join:
SELECT COUNT(DISTINCT a.fachgebiet) as Fachgebiete, p.name
FROM assistenten a RIGHT OUTER JOIN professoren p
ON a.boss = p.persnr
GROUP BY p.name
ORDER BY Fachgebiete DESC;
(b) SELECT p2.name
FROM
voraussetzen vr, Vorlesungen v1, Vorlesungen v2,
Professoren p1, Professoren p2
WHERE vr.vorgaenger = v1.vorlnr
AND vr.nachfolger = v2.vorlnr
AND v1.gelesenvon = p1.persnr
AND v2.gelesenvon = p2.persnr
AND p1.rang = ’C4’
AND p2.rang=’C3’
(c) WITH NumVorl AS (
SELECT COUNT(VorlNr) as Counter, gelesenVon
FROM Vorlesungen
GROUP BY gelesenVon
)
SELECT count(*) as Rang, p.Name
FROM NumVorl a, NumVorl b, Professoren p
WHERE (a.Counter < b.Counter OR a.gelesenVon = b.gelesenVon)
AND a.gelesenVon = p.PersNr
GROUP BY p.PersNr, p.Name
ORDER BY Rang;
2. SQL-Aufgaben: Terra2-DB (elektronische Abgabe: je 4 Punkte)
Formulieren Sie folgende Anfragen in SQL:
(a) Geben Sie die Namen aller Städte an, die am längsten Fluss liegen. Sortieren
Sie die Stadtnamen absteigend nach der Anzahl der Einwohner.
(b) Bestimmen Sie den Anteil der Seen, für den die Tiefe angegeben ist. Verwenden
sie die Funktion CAST(anteil AS DECIMAL(4,3)), die den Wert von anteil in
eine Dezimalzahl mit 3 Nachkommastellen umwandelt.
2
(c) Geben Sie den Namen des tiefsten Gewässers an – also den tiefsten See oder
das tiefste Meer.
(d) Geben Sie die Städte an, die weniger als 0.1 Breiten- und Längengrade von einer
Flussquelle entfernt sind. Geben Sie zu jeder Stadt den Namen des zugehörigen
Flusses an und sortieren Sie das Ergebnis nach Stadtname und Flussname.
Verwenden Sie die Funktion abs zur Berechnung des Absolutbetrags einer Zahl.
Lösungsvorschlag
(a) SELECT s.Name
FROM Stadt s, liegt_an l, Gewaesser g, Fluss f
WHERE g.g_id = f.g_id
AND l.g_id = g.g_id
AND l.s_id = s.s_id
AND f.laenge = (SELECT MAX(Laenge)
FROM Fluss)
ORDER BY s.Einwohner DESC
(b) Achtung: Bei der Countfunktion werden Nullwert werden nicht mitgezählt.
Multiplikation mit 1.0 für Typ-konvertierung
SELECT CAST(((1.0 * COUNT(TIEFE)) / COUNT(*))
AS DECIMAL(4,3)) AS ANTEIL
FROM See
(c) WITH Tiefen AS (
(SELECT G_ID, Tiefe FROM See)
UNION
(SELECT G_ID, Tiefe FROM Meer)
)
SELECT g.Name
FROM
Tiefen t, Gewaesser g
WHERE g.G_ID = t.G_ID
AND t.Tiefe = ( SELECT MAX(Tiefe)
FROM Tiefen )
(d) SELECT s.Name AS Stadt, g.Name AS Fluss
FROM Stadt s, Flussquelle q, Gewaesser g
WHERE g.G_ID = q.G_ID
AND ABS(s.laenge - q.laengeu) < 0.1
AND ABS(s.breite - q.breiteu) < 0.1
ORDER BY s.Name, g.Name
3. SQL-Aufgaben: Terra2-DB
Formulieren Sie folgende Anfragen in SQL:
3
(a) Geben Sie die Namen aller Berge aus, die zu mehreren Ländern gehören. Geben
Sie für jeden Berg jeweils ein eindeutiges Paar von Nachbarländern an (D.h. je
Berg darf es keine zwei Tupel (LandA, LandB) und (LandB, LandA) geben.
Sortieren Sie das Ergebnis aufsteigend nach dem Schlüsselattribut des Berges
und dem Namen des ersten Landes und des zweiten Landes.
(b) Geben Sie die Namen aller Berge aus, die zu mehreren Länder gehören und in
allen diesen Ländern der höchste Berg sind. Gruppieren Sie jeweils den Berg
und die Nachbarländer, in dem Sie die Ausgabe nach Name des Berges und
den Namen Nachbarländer sortieren.
Lösungsvorschlag
(a) WITH berg_land AS (
SELECT b.b_id, lt.l_id
FROM berg b, geo_Berg gb, landesteil lt
WHERE b.b_id = gb.b_id AND lt.lt_id = gb.lt_id)
SELECT b.name, l1.name, l2.name
FROM berg b, berg_land bl1, berg_land bl2,
land l1, land l2, benachbart bn
WHERE b.b_id = bl1.b_id
AND b.b_id = bl2.b_id
AND l1.l_id = bl1.l_id
AND l2.l_id = bl2.l_id
AND bl1.l_id = bn.l_id1
AND bl2.l_id = bn.l_id2
ORDER BY b.name, l1.name, l2.name;
Alternative:
WITH berg_land AS (
SELECT b.b_id, lt.l_id
FROM berg b, geo_Berg gb, landesteil lt
WHERE b.b_id = gb.b_id AND lt.lt_id = gb.lt_id)
SELECT b.Name as Berg,
l1.Name as Land1,
l2.Name as Land2
FROM
Berg b, berg_land bl1, berg_land bl2,
Land l1, Land l2
WHERE b.b_id = bl1.b_id
AND b.b_id = bl2.b_id
AND l1.l_id = bl1.l_id
AND l2.l_id = bl2.l_id
AND bl1.l_id > bl2.l_id
ORDER BY b.b_id, l1.Name, l2.Name;
4
Anmerkung: Die Bedingung der LänderIDs muss > oder < verwenden, weil
sonst die beteiligten Länder zweimal auftauchen.
(b) WITH berg_land AS
(SELECT b.b_id, lt.l_id
FROM
berg b, geo_Berg gb, landesteil lt
WHERE
b.b_id = gb.b_id
AND
lt.lt_id = gb.lt_id)
SELECT b.name, l1.name, l2.name
FROM berg b, geo_berg gb, landesteil lt,
benachbart bn, land l1, land l2
WHERE b.b_id = gb.b_id
AND gb.lt_id = lt.lt_id
AND lt.l_id = bn.l_id1
AND l1.l_id = lt.l_id
AND l2.l_id = bn.l_id2
AND b.hoehe = (SELECT MAX(b2.hoehe)
FROM
berg b2, berg_land bl2
WHERE b2.b_id = bl2.b_id
AND l1.l_id = bl2.l_id)
AND b.hoehe = (SELECT MAX(b2.hoehe)
FROM
berg b2, berg_land bl2
WHERE b2.b_id = bl2.b_id
AND l2.l_id = bl2.l_id)
ORDER BY b.name, l1.name, l2.name;
4. 3-wertige Logik
Zeigen Sie, dass für 3-wertige Logik das Assoziativgesetz, das Kommutativgestz und
das Distributivgesetz gilt, d.h. für alle Variablen x, y, z , die die Werte true, f alse
oder unknown annehmen können gilt:
(a) (x ∧ y) ∧ z = x ∧ (y ∧ z) bzw. (x ∨ y) ∨ z = x ∨ (y ∨ z)
(b) x ∧ y = y ∧ x bzw. x ∨ y = y ∨ x
(c) x ∨ (y ∧ z) = (x ∨ y) ∧ (x ∨ z) bzw. x ∧ (y ∨ z) = (x ∧ y) ∨ (x ∧ z)
Lösungsvorschlag
Verwende eine Wahrheitstabelle. Wir brauchen nur die Ausdrücke zu untersuchen, in
denen der Wert unknown (U) vorkommt, denn alle anderen Ausdrücke entsprechen
der 2-wertigen Logik, für die die Gesetze bekannt sind.
(a) Assoziativgesetz:
5
x
T
T
U
T
U
U
F
F
U
F
U
U
U
y
T
U
T
U
T
U
F
U
F
U
F
U
U
z
U
T
T
U
U
T
U
F
F
U
U
F
U
(x ∧ y) ∧ z
U
U
U
U
U
U
F
F
F
F
F
F
U
(b) Kommutativgesetz:
x y x∧y y∧x
T U
U
U
U T
U
U
F U
F
F
U F
F
F
U U
U
U
(c) Distributivgesetz:
x y z x ∨ (y ∧ z)
T T U
T
T U T
T
U T T
T
T U U
T
U T U
U
U U T
U
F F U
F
F U F
F
U F F
U
F U U
U
U F U
U
U U F
U
U U U
U
x ∧ (y ∧ z)
U
U
U
U
U
U
F
F
F
F
F
F
U
x∨y
T
T
U
U
U
(x ∨ y) ∨ z
T
T
T
T
T
T
U
U
U
U
U
U
U
x ∨ (y ∨ z)
T
T
T
T
T
T
U
U
U
U
U
U
U
y∨x
T
T
U
U
U
(x ∨ y) ∧ (x ∨ z)
T
T
T
T
U
U
F
F
U
U
U
U
U
6
x ∧ (y ∨ z)
T
T
U
U
U
U
F
F
F
F
U
U
U
(x ∧ y) ∨ (x ∧ z)
T
T
U
U
U
U
F
F
F
F
U
U
U