Musterlösung 4 - auf Matthias

Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
4. Übungszettel
(Musterlösung)
Einführung in Datenbanksysteme
Datenbanken für die Bioinformatik
Heinz Schweppe, Jürgen Broß, Katharina Hahn
Hinweise:
●
●
●
Auf dem Server esel verfügbar, wird die TerraDB sowohl für Oracle als auch
für Postgres zur Verfügung gestellt. Auf der Veranstaltungswebseite sind
Informationen verfügbar, wie Sie auf die jeweilige Datenbank zugreifen
können.
Logins für die Übungsaufgaben erhalten Sie in Ihren Tutorien.
Zusatzpunkte werden auf dem aktuellen Zettel verrechnet.
Aufgabe 1 (Relationale Algebra)
9 Punkte
Gegeben sei folgendes Datenbankschema, in dem Informationen über Orte, Filme
und das aktuelle Programm gespeichert sind:
KinoDB (
Orte(Kino, Adresse, Telefon),
Filme(Titel, Regie, Schauspieler),
Programm(Kino, Titel, Zeit)
)
Beachten Sie, dass der Primärschlüssel den Relationen Filme und Programm jeweils
alle drei Attribute umfasst, da ein Film von mehreren Regisseuren gedreht worden
sein kann (z.B. Raumpatrouille Orion - Rücksturz ins Kino) und mehrere
Schauspieler mitspielen können, bzw. ein Film an einem Ort zu verschiedenen
Uhrzeiten gezeigt werden kann.
a) Interpretieren Sie folgende Ausdrücke der relationalen Algebra in natürlicher
Sprache:
(1)  Regisseur  Schauspieler =Scarlett Johansson  Filme
Welche Regisseure haben schon mal mit Scarlett Johansson gearbeitet?
(2)
 Schauspieler  Filme ⋈ Titel =Titel Programm
Welche Schauspieler spielen in Filmen, die aktuell laufen?
(3)
 Kino , Adresse Orte ⋈ Kino=Titel 
 Titel  Schauspieler=Humphrey Bogart Filme∖ Titel Programm 
Welche Kinos (+Adresse) sind nach einem Film mit Humphey Bogart benannt, der nicht
mehr aktuell in den Kinos läuft? (Casablanca)
b) Geben Sie folgende Anfragen in relationaler Algebra an:
Ausgabe 07.05.2008
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
(1) In welchen Filmen (Titel) spielt Philip Seymour Hoffman mit?
 Titel  sigmal Schauspieler=Philipp Seymour Hoffman  Filme
(2) In welchen Kinos (Name und Adresse) laufen Filme mit Natalie Portman?
 Kino , Adresse Orte ⋈ Kino=Kino  Programm ⋈ Titel =Titel  Schauspieler= Natalie Portman  Filme
(3) Welche Regisseure haben noch nie mit Nicolas Cage gearbeitet?
 Regisseur  Filme∖  Regisseur  Schauspieler=Nicolas Cage  Filme
(4) In welchen laufenden Filme spielen ausschließlich Schauspieler mit, die
schon mal mit Steven Spielberg gearbeitet haben?
 Titel  Programm∖ Titel  Filme ⋈ Schauspieler =Schauspieler   Schauspieler  Filme∖
 Schauspieler  Regisseur =Stephen Spielberg  Filme
Man selektiert alle Schauspieler, die schon mal mit Spielberg gearbeitet haben, zieht diese
von allen ab und erhält somit alle Schauspieler, die noch nie mit Steven Spielberg
gearbeitet haben. Dann selektiert man alle Filme, mit einem solchen Schauspieler und
zieht diese Menge von der Menge aller aktuelle laufenden Filme ab.
Aufgabe 2 (SQL Anfragen)
10 (+2) Punkte
Gegeben sei das Datenbankenschema aus Aufgabe 1. Formulieren Sie folgende
Anfragen in SQL.
a) In welchen Kinos (Name und Adresse) läuft Slumdog Millionaire?
SELECT o.Kino, o.Adresse
FROM Orte o, Programm p
WHERE o.Kino = p.Kino
AND p.Titel = 'Slumdog Millionaire'
b) Welche Filme laufen nicht mehr aktuell im Kino?
SELECT Titel
FROM Filme f
WHERE f.Titel NOT IN
(SELECT Titel
FROM Programm p)
c) In welchen Kinos (Name und Adresse) laufen Filme mit Johnny Depp?
SELECT o.Name, o.Adresse
FROM Orte o, Programm p, Filme f
WHERE o.Kino = p.Kino
AND p.Titel = f.Titel
AND f.Schauspieler = 'Johnny Depp'
d) Welcher Regisseur hat schon mal mit Romy Schneider und James Dean
gearbeitet? Beachten Sie, dass die Zusammenarbeit mit den beiden
genannten Schauspielern in unterschiedlichen Filmen stattgefunden haben
kann.
SELECT f1.regie
FROM Filme f1, Filme f2
WHERE f1.regie = f2.regie
AND f1.Schauspieler = 'Romy Schneider'
AND f2.Schauspieler = 'James Dean'
e) Wieviele Filme laufen aktuell, bei denen Andreas Dresen Regie geführt
hat?
SELECT COUNT (f1.regie)
FROM Filme f, Programm p
WHERE f.Titel = p.Titel
AND f.Regie = 'Andreas Dresen'
Zusatzaufgabe:
f) In welchen laufenden Filmen spielen ausschließlich Schauspieler mit, die
schon mal mit Steven Spielberg gearbeitet haben?
SELECT Titel
FROM Programm p
WHERE p.titel NOT IN
(SELECT f3.Titel
FROM Filme f3
Ausgabe 07.05.2008
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
WHERE Schauspieler IN
--alle schauspieler, die noch nicht mit spielberg gearbeitet haben
(SELECT DISTINCT Schauspieler
FROM Filme f2
WHERE schauspieler NOT IN
(SELECT Schauspieler
FROM Filme f
WHERE f.Regie='Steven Spielberg')))
Geben sie jeweils gültige SQL-Statements an.
Aufgabe 3 (SQL - TerraDB)
12 (+3) Punkte
Die TerraDB steht Ihnen auf dem Server esel zur Verfügung. Auf der Webseite
finden Sie Informationen, wie Sie mit Hilfe Ihres Logins auf die Datenbank zugreifen
können. Formulieren Sie die folgenden Anfragen in SQL. Verwenden Sie für ihre
Anfragen jeweils die englische Version der TerraDB (unter Oracle terradb_english,
unter Postgres geodb). Einige der Anfragen lassen sich unter zu Hilfenahme der
Mengenquantoren ALL, ANY, EXISTS formulieren. Beachten Sie die Hinweise
(s.u.):
a) Finden Sie alle Landesteile, durch die der Mekong fließt. Geben Sie sowohl
die Landesteil und das jeweilige Land namentlich an.
SELECT DISTINCT l.name as Land, t.name as Landesteil
FROM terradb.land l, terradb.landesteil t, terradb.geo_fluss f
WHERE t.L_ID = f.L_ID
AND t.LT_ID = f.LT_ID
AND t.L_ID = l.L_ID
AND f.fluss = 'Mekong'
ORDER BY l.name, t.name;
b) Welche Länder liegen nicht an Meeren, wohl aber alle ihre Nachbarländer?
Geben Sie die Länder namentlich an.
WITH ohne_kueste AS
(SELECT l.l_id AS ok_id, l.name AS ok_name
FROM terradb.land l
WHERE l.l_id NOT IN (SELECT l_id FROM terradb.geo_meer)),
nachbarlaender AS
(SELECT l.l_id AS nb_id, b.land2 AS nb_id2
FROM terradb.land l, terradb.benachbart_sym b
WHERE l.l_id = b.land1
AND l.l_id NOT IN (SELECT l_id FROM terradb.geo_meer))
SELECT DISTINCT ok_name
FROM ohne_kueste
WHERE NOT EXISTS (SELECT *
FROM nachbarlaender
WHERE nachbarlaender.nb_id2 = ohne_kueste.ok_id)
order by ok_name;
ohne_kueste enthält die Länder, an die laut geo_meer, kein Meer grenzt. In nachbarlaender, sind Länder (ohne
Küste) mit samt ihren Nachbarn drin (für jedes Paar ein Tupel). Man selektiert jetzt alle Länder aus ohne_kueste, die
selbst nicht Nachbar zu einem Land ohne Kueste (keinen Eintrag unter nb in nachbarn) sind. Denn ein Land L, das
keine Küste hat selbst Nachbar zu einem Land L2 ist, das keine Küste hat, dann kann es nicht selektiert werden, weil
L2 auch keine Küste hat, somit nicht den Suchkriterien entspricht.
alternative Lsg fuer Postgres :
SELECT country2, country.name , count(country1)
FROM country LEFT JOIN neighbor_of_sym ON
(country2 = country.c_id
AND country1 NOT IN
(SELECT distinct country.c_id
FROM geo_sea NATURAL JOIN region JOIN country
ON (country.c_id = region.c_id)))
WHERE country.c_id NOT IN
(SELECT distinct country.c_id
FROM geo_sea natural
JOIN region JOIN country ON (country.c_id = region.c_id))
GROUP BY country2, country.name
HAVING count(country1) = 0;
Ausgabe 07.05.2008
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
c) Welche Länder (Name) mit mehr als 10 Millionen Einwohnern haben eine
höhere
Bevölkerungsdichte
(Einwohner/Landfläche)
als
alle
ihre
Nachbarländer?
SELECT l.name, l.population, l.population/l.area AS density
FROM country l
WHERE l.population > 10000000
AND l.population/l.area > ALL (SELECT l2.population/l2.area
FROM country l2, neighbor_of_sym b
WHERE b.country1 = l.c_id
AND b.country2 = l2.c_id)
d) Finden Sie alle Länder, die direkt an Sambia grenzen.
);
SELECT l2.name
FROM terradb.Land l1,terradb.Land l2,terradb.benachbart b
WHERE l1.name='Sambia'
AND((b.Land1=l1.L_ID
AND b.Land2=l2.L_ID)
OR( b.Land2=l1.L_ID
AND b.Land1=l2.L_ID))
oder
SELECT l2.name
FROM Terradb.Land l1,Terradb.Land l2,Terradb.benachbart_sym b
WHERE l1.name='Sambia'
AND b.Land1=l1.L_ID
AND b.Land2=l2.L_ID
e) Geben Sie alle Flüsse an, die in ein Meer münden. Das Meer soll dabei nur
an eines der Landesteile grenzen, durch das der Fluss fließt. Das Meer kann
aber an andere Landesteile grenzen, durch die der Fluss nicht fließt.
SELECT r.name
FROM river r, geo_river gr, geo_sea gs
WHERE r.sea <> ''
AND r.name = gr.river
AND gr.r_id = gs.r_id
AND gr.c_id = gs.c_id
AND r.sea = gs.sea
GROUP BY r.name
HAVING COUNT (gs.sea)=1
ORDER BY r.name;
Zusatzaufgabe
f) Gesucht sind die Meere (Name), die die maximalen Anzahl an anliegenden
Städten mit mehr als 1 Mio. Einwohner haben bzw. mit der maximalen Anzahl
der Flüsse, die in sie münden.
SELECT s.name FROM sea s
WHERE s.name IN (
SELECT a.sea FROM (
SELECT l.sea, count(*) AS count1
FROM located_at l, city c
WHERE c.c_id = l.c_id AND c.r_id = l.r_id AND l.city = c.name
AND c.population > 1000000 AND l.sea IS NOT NULL
GROUP BY l.sea
ORDER BY count1 DESC
LIMIT 1) AS a
) OR s.name IN (
SELECT b.sea FROM (
SELECT r.sea, count(*) AS count2
FROM river r
WHERE r.sea IS NOT NULL
GROUP BY r.sea
ORDER BY count2 DESC
LIMIT 1) AS b
)
Geben sie jeweils gültige SQL-Statements an.
Hinweise: Die Informationen in der TerraDB sind nicht vollständig, daher kann es
vereinzelt zu irreführenden Ergebnissen kommen. Z.B. sind keine Informationen
darüber gespeichert, an welchen Meer(en) Thailand oder die Fidschi-Inseln liegen.
Ausgabe 07.05.2008
Einführung in Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2009
Die Relation, neighbor_of ist nicht symmetrisch. Eine symmetrische Darstellung der
Nachbarschaftsbeziehung bietet die View 'neighbor_of_sym'. Diese ermöglicht
einfachere SQL-Statements bei der Formulierung mancher Anfragen.
Aufgabe 4 (Kardinalitäten von Join-Ergebnissen)
8 Punkte
Gegeben seien die Relationen R, S, T mit den Attributen r1, r2, … bzw. s1, s2, … bzw.
t1, t2, … und den Kardinalitäten |R|, |S| und |T|. Die Relation Q bezeichnet das
Ergebnis des Inner-Joins von R, S und T wie folgt:
Q=R ⋈ ri=sj S ⋈ sk=tl T
a) Zwischen welchen Schranken liegt die Kardinalität von Q?
∀ i , j :r i≠s j ) und S
keinen Joinpartner in T findet (also für alle Tupel in R und S gilt: ∀ k , l :s k ≠t l ), dann ist
die Kardinalität von |Q| = 0. Falls im Gegensatz dazu folgendes gilt ∀ i , j :r i=s j und
∀ k , l :s k =t l , dann ist die Kardinalität von Q: |Q| = |R| * |S| * |T|. Daher gilt:
Falls R keinen Joinpartner in S findet (für alle Tupel in R und S gilt:
0 <= |Q| <= |R| * |S| * |T|
b) Zwischen welchen Schranken liegt die Kardinalität von Q, falls ri Schlüssel von
R ist?
Falls keine Joinpartner gefunden werden, ist |Q| = 0. Da ri der Schlüssel von R ist, müssen die
Werte paarweise verschieden sein. Somit ist die Obergrenze von |Q| = |S|. Es gilt also:
0 <= |Q| <= |S| * |T|
c) Zwischen welchen Schranken liegt die Kardinalität von Q, falls ri Schlüssel von
R und tl Schlüssel von T ist.
Falls keine Joinpartner gefunden werden, ist |Q| = 0. Für den ersten Teil des Joins gilt die
Kardinalität wie eben erläutert. Da tl Schlüssel von T ist, müssen die Werte von tl paarweise
verschieden sein. Daher gilt:
0 <= |Q| <= |S|
d) Zwischen welchen Schranken liegt die Kardinalität von Q, falls ri Schlüssel von
R, sj ein Fremdschlüssel auf R ist und tl Schlüssel von T ist.
Die obere Schranke ändert sich gegenüber c) nicht. Da sj Fremdschlüssel auf R ist, kann sj
entweder nur den Wert NULL annehmen, oder eindeutige Werte aus R. Wenn alle sj NULL
sind, dann ist der linke Teil des Joins leer. Daher gelten die gleichen Kardinalitäten, wie bei c).
(Begründung für die untere Schranke muss gegeben werden).
Geben Sie möglichst scharfe Schranken an und erläutern Sie Ihre Antwort.
Ausgabe 07.05.2008