Relationale Algebra: Aufgaben (Lösungen)

Relationale Algebra: Aufgaben (Lösungen)#

Hinweis: Du kannst die folgenden Aufgaben auch mit Relax lösen. Die Links zu den Basisschemata stehen an den Aufgaben.

Aufgabe 1#

Eine Bibliothek speichert ihre vorhandenen Exemplare mithilfe einer lokalen Kennung. Zu jeder lokalen Kennung gehört eine ISBN, die jedes Exemplar eindeutig identifiziert. Zusätzlich speichert die Bibliothek ihre Nutzer und die Bücher, welche die jeweiligen Nutzer ausgeliehen haben. In den folgenden Teilaufgaben erstellen Sie typische Anfragen in Relationaler Algebra an diese Bibliotheksdatenbank. Diskutieren Sie auch für jede Teilaufgabe, wie das Ausgabeschema der Anfrage aussieht und welche Instanzen ausgegeben werden würden.

Gegeben sein die folgende kleine Bibliotheksdatenbank:

import duckdb
from IPython.display import display_html

duck_con = duckdb.connect()

css = "style='display:inline; margin: 0px 20px 0px 0px; vertical-align: top;'"


display_html(
    duck_con.sql("select * from '../resources/05_relationale_algebra/Benutzer.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Benutzer</strong>")
    ._repr_html_()
    + duck_con.sql("select * from '../resources/05_relationale_algebra/Leiht_aus.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Leiht_aus</strong>")
    ._repr_html_()
    + duck_con.sql("select * from '../resources/05_relationale_algebra/Exemplar.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Exemplar</strong>")
    ._repr_html_()
    + duck_con.sql("select * from '../resources/05_relationale_algebra/Buch.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Buch</strong>")
    ._repr_html_(),
    raw=True,
)
Benutzer
  Bid Vorname
0 1 Babsi
1 2 Tad
2 3 Odo
3 4 Laila
4 5 Pam
Leiht_aus
  Bid Signatur
0 1 KS345
1 5 ST220
2 1 JLb 12
3 2 ST270
4 5 ST270
Exemplar
  Signatur ISBN
0 S2710 0-13-1X
1 ST270 0-13-1X
2 JLb 12 3-540-X
3 ST220 0-544-X
4 KS345 37020X
Buch
  ISBN Titel Sprache
0 0-13-1X Database Systems en
1 3-540-X Wein : verstehen und geniessen de
2 0-544-X Big data : a revolution that … en
3 37020X Bierbrauen fuer jedermann de
4 38266X Data Warehouse Technologien de

Bibliotheks-Schema in ReLaX öffnen (Hinweis: Falls die Datenbank nicht automatisch geladen werden sollte, kann man diese in der rechten oberen Ecke bei Select DB ganz unten unter Miscellaneous auswählen.)

Aufgabe 1.1#

Welche englischsprachigen Bücher (Titel) gibt es?

Musterlösung

Musterlösung

\(\large \pi_{\text{Titel}}(\sigma_{\text{Sprache = 'en'}}(Buch))\)

Titel

0

Database Systems

1

Big data : a revolution that …

Aufgabe 1.2#

Welche Benutzer (Vornamen) haben noch nie ein Buch ausgeliehen?

Musterlösung

Musterlösung

\(\large \pi_{\text{Vorname}} \ (Benutzer\bowtie(\pi_{Bid} \ (Benutzer) - (\pi_{Bid} \ (Benutzer \bowtie Leiht\_aus))))\)

Alternativ: \(\large \pi_{\text{Vorname}}(Benutzer - (\pi_{Bid, Vorname} \ (Benutzer \bowtie Leiht\_aus))))\)

Vorname

3

Odo

4

Laila

Aufgabe 1.3#

Welche Benutzer (Vornamen) haben ein Exemplar mit dem Buchtitel „Bierbrauen fuer jedermann“ ausgeliehen?

Musterlösung

Musterlösung

\(\large \pi_{Vorname} \ (Benutzer \bowtie Leiht\_aus \bowtie Exemplar \bowtie(\sigma_{\text{Titel = 'Bierbrauen fuer jedermann'}} \ \ (Buch)))\)

Vorname

1

Babsi

Aufgabe 1.4#

Alle Benutzer (Vornamen), die mindestens zwei Exemplare ausgeliehen haben.

Musterlösung

Musterlösung

\(\large \pi_{Vorname} \ (Benutzer \bowtie (\sigma_{count\_signatur >=2} \ \ (\gamma_{Bid, COUNT(Signatur) \rightarrow \text{count\_signatur}} \ ( Leiht\_aus ))))\)

Vorname

1

Babsi

5

Pam

Aufgabe 2: Minimale und maximale Ausgabekardinalität von Operatoren#

  • Gegeben seien zwei kompatible Relationen R(A,B) und S(A,B).

  • Die Relationen enthalten jeweils |R| und |S| Tupel.

  • Welche Aussage können wir über die Kardinalität von |R ∪ S| treffen (Mengen)?

    • Maximal?

    • Minimal?

  • Welche Aussage können wir über die Kardinalität von |R ∪ S| treffen (Multimengen)?

    • Maximal?

    • Minimal?

Musterlösung

Musterlösung

Mengen:

  • Maximal: Die Relationen sind disjunkt (kein gemeinsames Element)

    • |R ∪ S| = |R| + |S|

  • Minimal: Eine Menge ist Teilmenge der anderen Menge (R ⊆ S oder S ⊆ R)

    • |R ∪ S| = max(|R|,|S|)

Multimengen:

  • Maximal: |R| + |S|

  • Minimal: |R| + |S|

Motivation / Kontext:

  • Relationale Algebra ist auf Mengen definiert und somit gibt es keine Duplikate.

  • SQL-DQL ist hingegen auf Multimengen definiert und somit sind Duplikate möglich.

Aufgabe 3: Regatta-Informationssystem#

Formulieren Sie für die folgenden Teilaufgaben die Anfragen in der Relationalen-Algebra, die zu den entsprechenden textuellen Anfragen korrespondieren. Beschreiben Sie außerdem jeweils das Ausgabeschema und welche Instanzen im Ergebnis enthalten sind.

Gegeben sei ein Regatta-Informationsschema mit den folgenden Relationen:

css = "style='display:inline; margin: 0px 20px 0px 0px; vertical-align: top;'"


display_html(
    duck_con.sql("select * from '../resources/05_relationale_algebra/Bootsklasse.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Bootsklasse</strong>")
    ._repr_html_()
    + duck_con.sql("select * from '../resources/05_relationale_algebra/Wettfahrt.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Wettfahrt</strong>")
    ._repr_html_()
    + duck_con.sql("select * from '../resources/05_relationale_algebra/Platzierung.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Platzierung</strong>")
    ._repr_html_()
    + duck_con.sql("select * from '../resources/05_relationale_algebra/Teilnehmer.csv'")
    .to_df()
    .style.set_table_attributes(css)
    .set_caption("<strong>Teilnehmer</strong>")
    ._repr_html_(),
    raw=True,
)
Bootsklasse
  Bootsklasse.Klasse Bootsklasse.Bauart
0 Folkeboot Holz
1 H-Boot GFK
2 Optimist GFK
3 Pirat Holz
Wettfahrt
  Wettfahrt.FahrtNr Wettfahrt.Name Wettfahrt.Datum Wettfahrt.Zeit
0 1 Moorpokal Tue Jun 18 2013 00:00:00 GMT+0200 (Central European Summer Time) 10
1 2 Herbstmeister Mon Sep 16 2013 00:00:00 GMT+0200 (Central European Summer Time) 14
2 3 F. Huber Gedenk Preis Wed May 15 2013 00:00:00 GMT+0200 (Central European Summer Time) 14
3 4 Blaues Band Wed May 29 2013 00:00:00 GMT+0200 (Central European Summer Time) 10
Platzierung
  Platzierung.SegelNr Platzierung.Wettfahrt Platzierung.Platz
0 GER 1393 3 1
1 GER 3876 3 4
2 GER 4309 3 3
3 GER 4318 1 9
4 GER 4318 2 2
5 GER 4833 3 dnf
6 GER 4995 1 2
7 GER 4995 2 1
8 GER 5107 4 1
9 GER 5503 3 2
10 GER 5505 4 3
11 GER 5703 4 2
12 GER 4318 3 5
13 GER 4318 4 4
Teilnehmer
  Teilnehmer.SegelNr Teilnehmer.Name Teilnehmer.Bootsklasse Teilnehmer.Baujahr Teilnehmer.Farbe Teilnehmer.Eigner
0 GER 1393 Carla F. Folkeboot 1972 Weiß G.Gerhard
1 GER 3876 No. Uno Folkeboot 1993 Rot N. Nichts
2 GER 3999 Willi Optimist 1989 Weiß E. Ernst
3 GER 4309 Elkche H-Boot 1981 Blau M. Michel
4 GER 4318 Marie Pirat 1992 Blau D. Dummer
5 GER 4833 Martha H. H-Boot 1994 Weiß O. Otter
6 GER 4995 Celeste Pirat 1991 Rot S. Schott
7 GER 5107 Windrose Optimist 1987 Lila V. Voelz
8 GER 5503 Lisa H-Boot 1983 Grün H. Hiller
9 GER 5505 Pistensau Optimist 1993 Braun F. Faser
10 GER 5703 Grünspan Optimist 1988 Grün M. Meise

Regatta-Schema in ReLaX öffnen

Aufgabe 3.1#

Aus welchem Material (Bauart) wurde das Boot mit der Segelnummer „GER 4318“ gebaut und welcher Klasse gehört es an?

Musterlösung

Musterlösung

\(\large \pi_{Bauart, Klasse} \ (Bootsklasse \bowtie_{Klasse = Bootsklasse}(\sigma_{SegelNr = \text{'GER 4318'}} \ \ (Teilnehmer)))\)

Bauart

Klasse

Holz

Pirat

Aufgabe 3.2#

Wann starteten die Boote der Klasse ‘Optimist’ am 29.05.03 ins Rennen und welche Namen haben sie?

Musterlösung

Musterlösung

\(\large a = (\rho_{Wettfahrt, WettfahrtName, Datum, Startzeit} \ \ (Wettfahrt)) \bowtie Platzierung \bowtie Teilnehmer\)

\(\large \pi_{Startzeit, Name} \ (\sigma_{\text{Datum = '29.05.13'} \wedge \text{Bootsklasse = 'Optimist'}} \ \ \ (a))\)

Startzeit

Name

10:00

Gruenspan

10:00

Windrose

10:00

Pistensau

Aufgabe 3.3#

An welchen Wettfahrten (FahrtNr, Name) haben ausschließlich Holzboote teilgenommen?

Musterlösung

Musterlösung

\(\large a = (\rho_{Wettfahrt, WettfahrtName, Datum, Startzeit} \ \ (Wettfahrt)) \bowtie Platzierung \bowtie Teilnehmer \bowtie_{Bootsklasse = Klasse} Bootsklasse\)

Wettfahrten an denen Boote teilgenommen haben, die nicht aus Holz gebaut sind. \(\\\large b = (\pi_{Wettfahrt, WettfahrtName}\ \ (\sigma_{\text{Bauart != 'Holz'}} \ \ (a)))\)

Wettfahrten an denen ausschließlich Holzboote teilgenommen haben. \(\\\large c = (\rho_{Wettfahrt, WettfahrtName} \ (\pi_{FahrtNr, Name} (Wettfahrt) )) - b\)

Wettfahrt

WettfahrtName

1

Moorpokal

2

Herbstmeister

Aufgabe 3.4#

Welche Bootsklassen nehmen am „Franz Huber Gedenk Preis“ oder am „Moorpokal“ teil?

Musterlösung

Musterlösung

\(\large a = (\rho_{Wettfahrt, WettfahrtName, Datum, Zeit} \ \ (Wettfahrt)) \bowtie Platzierung \bowtie Teilnehmer\)

\(\large \pi_{Bootsklasse} \ (\sigma_{\text{Name = 'Franz Huber Gedenk Preis'} \ \vee \ \text{Name = 'Moorpokal'}} \ \ \ \ (a))\)

Bootsklasse

Pirat

Folkeboot

H-Boot

Aufgabe 3.5#

Welche Boote (SegelNr, Name) konnten das Rennen „Blaues Band“ nicht beenden („dnf“)?

Musterlösung

Musterlösung

\(\large a = (\rho_{Wettfahrt, WettfahrtName, Datum, Startzeit} \ \ (Wettfahrt)) \bowtie Platzierung \bowtie Teilnehmer\)

\(\large \pi_{SegelNr, Name}\ (\sigma_{\text{WettfahrtName = 'Blaues Band'} \wedge \text{Platz = 'dnf'}}\ \ \ (a))\)

SegelNr

Name

Aufgabe 3.6#

Wie viele Boote haben am „Franz Huber Gedenk Preis“ teilgenommen?

Musterlösung

Musterlösung

\(\large a = \gamma_{Wettfahrt, COUNT(SegelNr)\rightarrow AnzahlBoote} \ \ (Platzierung)\)

\(\large \pi_{AnzahlBoote} \ \ (\sigma_{\text{Name = 'Franz Huber Gedenk Preis'}} \ \ (Wettfahrt \bowtie_{FahrtNr = Wettfahrt}(a)))\)

AnzahlBoote

5

Aufgabe 3.7#

An welchen Wettfahrten (Namen) haben Boote aller Bauarten teilgenommen?

Musterlösung

Musterlösung

\(\large a = \pi_{Wettfahrt, Bauart} (Bootsklasse \ \bowtie_{Klasse=Bootsklasse} \ \ (\pi_{SegelNr, Bootklasse}(Teilnehmer)) \ \bowtie \ \ Platzierung)\)

\(\large \pi_{Name} \ (Wettfahrt \ \bowtie_{FahrtNr = Wettfahrt} \ (a / (\pi_{\text{Bauart}} (Bootsklasse))))\)

Name

Franz Huber Gedenk Preis

Aufgabe 4: Semantisches Verständnis#

Geben Sie das Ergebnis und die umgangssprachliche Formulierung der folgenden Relationalen-Algebra Ausdrücke an:

Regatta-Schema in ReLaX öffnen

Aufgabe 4.1#

\(\large \pi_{SegelNr} \ (\sigma_{Name = 'Herbstmeister'}\ \ (Wettfahrt \bowtie_{FahrtNr = Wettfahrt}(\sigma_{Platz = 1} \ (Platzierung))))\)

Musterlösung

Musterlösung

Umgangssprachlicher Ausdruck:

Welches Boot (SegelNr) war der Sieger der Regatta “Herbstmeister”?

SegelNr

GER4995

Aufgabe 4.2#

\(\large \pi_{SegelNr, Wettfahrt, Platz, Name, Datum, Startzeit} \ \ \ (\sigma_{Name = 'Herbstmeister'} \ \ (Wettfahrt \bowtie_{FahrtNr = Wettfahrt}(\sigma_{Platz = 1} \ (Platzierung))))\)

Musterlösung

Musterlösung

Umgangssprachlicher Ausdruck:

Welches Boot (SegelNr, Wettfahrt, Platz, Name, Datum, Startzeit) war der Sieger der Regatta “Herbstmeister”?

SegelNr

Wettfahrt

Platz

Name

Datum

Startzeit

GER 4995

2

1

Herbsmeister

16.09.13

14:00

Aufgabe 4.3#

\(\large a = Platzierung \bowtie \ ( \pi_{SegelNr} \ ( \sigma_{Baujahr <1980} \ (Teilnehmer)))\)

\(\large \pi_{Name} \ ( Wettfahrt \bowtie_{FahrtNr = Wettfahrt} (a))\)

Musterlösung

Musterlösung

Umgangssprachlicher Ausdruck:

An welchen Wettfahrten nahmen Boote teil, die vor 1980 gebaut wurden?

Name

Franz Huber Gedenk Preis

Hinweis: Zusatzmaterial befindet sich in 02_relationale_algebra_reframe_einfuehrung.ipynb, 03_relationale_algebra_reframe_zusatz.ipynb und 04_relationale_algebra_pragrammieraufgabe_zusatz.ipynb. Die Zusatzmaterialien sind ohne Gewähr auf Richtigkeit.

Contents