Musterlösung (6

Einführung in die Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2005
Musterlösung (6. Übungszettel)
Einführung in die Datenbanksysteme
Datenbanken für die Bioinformatik
Heinz Schweppe, Manuel Scholz
Projekt
Aufgabe
Bearbeiten Sie nun Teil 2 - 6 des Projekts.
Übungsaufgaben
1. Aufgabe
Gegeben sei folgendes Datenbankschema:
(Beachten Sie: Autor und Spieler sind von Person abgeleitet)
Rollenspiel-DB(
Personen (ID : integer, Vorname : string(20), Nachname : string(20), Geburtstag : date),
Spieler (ID : integer, Telefonnummer : integer),
Autoren (ID : integer, e-mail : string(20)),
Abenteuer (ID : integer, Name : string(2), Welt : integer, Spieleranzahl : integer, Autor : integer),
Welten (ID : integer, Name : string(20), Genre : string(20)),
Spielfigur (ID : integer, Vorname : string(20), Nachname : string(20), Alter : integer,
Rasse: string, Spieler : integer, Welt : integer)
)
Interpretieren Sie die folgenden Ausdrücke der relationalen Algebra in natürlicher Sprache.
•
Π Name,Vorname (σ Geburtstag <12.03.1985 ( Person ) )
Name und Vorname aller Personen die vor dem 12.03.1983 geboren sind.
•

ӀХӀ
Π Name,Vorname  ( σ Alter >120 ( Spielfigur ) )
(σ Name= 'DSA' (Welten ) ) 
Spielfigur .Welt =Welten.id


Name und Vorname aller Spielfiguren die in der Welt 'DSA' beheimatet sind und
älter als 120 Jahre sind.
•
ΠVorname,Genre ( Spielfigur
ӀХӀ
Abenteuer .Welt =Welten.id
ӀХӀ
Welten
Spielfigur .Welt =Welten.id
σ Name = 'Ein Gott greif t ein ' ( Abenteuer ))
Vorname der Spielfiguren, die in einer Welt leben, für die es das Abenteuer 'Ein
Gott greift ein' gibt und das Genre dieser Welt.
2. Aufgabe
a) Erstellen Sie mit Hilfe der Basisoperationen der relationalen Algebra einen „Full Outer
Join“ mit den beiden Relationen R und S.
Der Full Outer Join wird anhand der beiden Relationen A (Attribute ID, a1, a2, a3) und B
(Attribute ID, b1, b2) verdeutlicht.
Ausgabe 18.05.2005
Einführung in die Datenbanksysteme, Datenbanken für die Bioinformatik
( ( A-σ
mit
Sommersemester 2005
( X 1 ) ) × N 2 ) ∪ X 1 ∪ ( ( B-σ B.ID ,B.b1,B.b 2 ( X 1 ) ) × N 3 )
X 1 = ∏ A.ID ,a1,a 2,a 3,b1,b 2 ( σ A.ID = B.ID ( A × B ) )
A. ID , A. a1, A.a 2 , A. a 3
b) Erstellen Sie in Pseudocode ein kurzes Programm, das einen „Equi Join“ zweier Tabellen
durchführt.
for each r∈R
for each s∈S
if r.A = s.B then
result := result∪(r×s)
c) Untersuchen Sie ob der ӀХӀ Operator assoziativ/kommutativ ist. (Beweis)
Beweisidee: Rückführung des Joins auf Kreuzprodukt und Selektion
Kommutativität:
AӀХӀA.ID=B.ID B
<=> ϬA.ID=B.ID(A Х B)
<=> ϬA.ID=B.ID(B Х A)
<=> BӀХӀA.ID=B.ID A
Assoziativität:
(AӀХӀA.ID=B.ID B)ӀХӀB.ID=C.ID C)
<=>ϬB.ID=C.ID(ϬA.ID=B.ID(A Х B))Х C)
<=>ϬB.ID=C.ID
& A.ID=B.ID
((A Х B))Х C)
<=>ϬB.ID=C.ID
& A.ID=B.ID
(A Х (B Х C))
<=>ϬA.ID=B.ID(A Х(ϬB.ID=C.ID(BХ C)))
d) Betrachten Sie folgenden Semi Join R ӀХ S. Unter welchen (Schema) Bedingungen gilt
(unabhängig von der DB Ausprägung) immer |R ӀХ S| = |R|.
|R ӀХ S| = |R| gilt immer, wenn R einen Foreign Key (NOT NULL) besitzt, der auf ein Attribut
von S zeigt.
3. Aufgabe
Gegeben sei die Videodatenbank aus der Vorlesung.
Ausgabe 18.05.2005
Einführung in die Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2005
a) Die folgende Anfrage liefert auf einer Oracle-DB in einer Datenbank mit einem
"Rental"-Datensatz und 7 "Customer"-Datensätzen ein einziges Ergebnistupel.
Geben Sie dafür eine Begründung.
SELECT t_Id,format, m.title, c.name
FROM
Movie m
JOIN Tape t ON t.m_Id = m.m_ID
JOIN Rental ON t.t_id = Rental.tape_Id
NATURAL Right OUTER JOIN Customer c
WHERE m.director = 'Spielberg'
AND t.format = 'VHS';
Die Auswertungsreihenfolge ist Entscheidend. Da Director = "Spielberg" erst am ende nach
dem OUTER JOIN durchgeführt wird wird nur ein Datensatz zurückgegeben.
b) Formulieren Sie dieselbe Anfrage ohne JOIN und OUTER JOIN Anweisung. (Natürlich
auch ohne (+) oder ähnliche Konstrukte.)
(
SELECT t_Id,format, m.title, c.name
FROM
Movie m, Tape t, Rental r, Customer c
WHERE t.m_Id = m.m_ID
AND t.t_id = Rental.tape_Id
AND r.mem_no = c.mem_no
WHERE m.director = 'Spielberg'
AND t.format = 'VHS')
UNION
(
SELECT n.nvalue,n.nvalue,n.nvalue,c.name
FROM
Null_Table n, Movie m, Tape t, Rental r, Customer c
WHERE t.m_Id = m.m_ID
Ausgabe 18.05.2005
Einführung in die Datenbanksysteme, Datenbanken für die Bioinformatik
Sommersemester 2005
AND t.t_id = Rental.tape_Id
AND r.mem_no != c.mem_no
WHERE m.director = 'Spielberg'
AND t.format = 'VHS')
c) Formulieren Sie in relationaler Algebra und SQL folgende Anfragen:
Alle SQL Statements sind den Ausdrücken der relationalen Algebra nachempfunden um die
Äquivalenz der beiden Anfragesprachen zu zeigen.
•
Alle Filme (Titel), von denen derzeit alle Exemplare ausgeliehen sind.
•
Filme die in den letzten 6 Monaten nicht ausgeliehen wurden.
Ausgabe 18.05.2005
Einführung in die Datenbanksysteme, Datenbanken für die Bioinformatik
•
Namen und Städt der Kunden, die keine Telefonnummer haben.
•
Namen aller Filme von denen es keine Tapes mehr gibt.
•
Namen aller Kunden, die aus Berlin kommen.
Sommersemester 2005
Ausgabe 18.05.2005