Jan-Christoph Kalo Florian Plötzky
Institut für Informationssysteme
Technische Universität Braunschweig www.ifis.cs.tu-bs.de
5. Große Übung
Relational Algebra
Gegeben sei folgendes Schema:
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde → Kunde, Kontotyp)
Buchung(konto → Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto → Konto)
Formulieren sie Queries in relationaler Algebra zu den gegebenen Fällen.
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 2
Aufgabe 1
Geben Sie die Kontonummern aller Konten des Kunden „Lex Luther“ an.
Aufgabe 1.1
LexLuther = σVorname=′Lex′∧ Name=′Luther′Kunde
πkontonummer( LexLuther ⋈ kundennummer=kunde Konto) Natürlich auch ohne den Zwischenschritt möglich.
Variation: was wäre, wenn die Relation Kunde wie folgt
aufgebaut wäre:
Kunde(kunde, Vorname, Name, Geburtsdatum)?
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde→ Kunde, Kontotyp)
Buchung(konto→ Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto→ Konto)
Finden Sie die Vornamen der ältesten Kunden.
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 4
Aufgabe 1.2
minGeb = 𝔉min Geburtsdatum Kunde
πVorname(σGeburtsdatum=minGeb Kunde )
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde→ Kunde, Kontotyp)
Buchung(konto→ Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto→ Konto)
Beliebter Fehler: 𝜋𝑉𝑜𝑟𝑛𝑎𝑚𝑒 𝔉𝑚𝑖𝑛 𝐺𝑒𝑏𝑢𝑟𝑡𝑠𝑑𝑎𝑡𝑢𝑚 𝐾𝑢𝑛𝑑𝑒
Geben Sie die Nachnamen aller Kunden zurück, die sowohl über ein Sparkonto als auch über ein
Girokonto verfügen.
Aufgabe 1.3
GirokontoKunden = πkundennummer,Name(
σKontotyp=′Girokonto′Konto ⋈kunde=kundennummer Kunde) SparkontoKunden = πkundennummer,Name(
σKontotyp=′Sparkonto′Konto ⋈kunde=kundennummer Kunde) πName GirokontoKunden ∩ SparkontoKunden
Beliebter Fehler: 𝜎𝐾𝑜𝑛𝑡𝑜𝑡𝑦𝑝=′𝐺𝑖𝑟𝑜𝑘𝑜𝑛𝑡𝑜′∧ 𝐾𝑜𝑛𝑡𝑜𝑡𝑦𝑝=′𝑆𝑝𝑎𝑟𝑘𝑜𝑛𝑡𝑜′Konto
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde→ Kunde, Kontotyp)
Buchung(konto→ Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto→ Konto)
Klammern an dieser Stelle benötigt, da die Projektion stärker bindet als
der Join (letzterer aber zuerst durchgeführt werden muss)
Geben Sie Vor- und Nachnamen aller Kunden zurück, die über kein Konto verfügen.
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 7
Aufgabe 1.4
KundenMitKonto = Kunden ⋉kundennummer=kunde Konto πVorname,Name(Kunde\KundenMitKonto)
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde→ Kunde, Kontotyp)
Buchung(konto→ Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto→ Konto)
Wie hoch ist der Gesamtbetrag aller Buchungen, die an das Konto 3417 überwiesen wurden?
Aufgabe 1.5
B3417 = σFremdesKonto=3417∧Kennzeichen=′Abbuchung′ Buchung
ρresult𝔉sum(Betrag)B3417
Überweisung an Konto 3417 = Abbuchung von einem Konto bei dem als fremdes Konto 3417 angegeben ist.
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde→ Kunde, Kontotyp)
Buchung(konto→ Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto→ Konto)
Finden Sie die Kontonummer auf der die frühste Buchung eingegangen ist.
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 9
Aufgabe 1.6
Beliebter Fehler: Uhrzeit vor dem Datum selektieren!
Kunde(kundennummer, Vorname, Name, Geburtsdatum) Konto(kontonummer, kunde→ Kunde, Kontotyp)
Buchung(konto→ Konto, bid, Datum, Uhrzeit, Kennzeichen, Betrag, FremdesKonto→ Konto)
Datum_Buchung= σDatum=𝔉min(Datum)BuchungBuchung πKonto (σUhrzeit=𝔉min Uhrzeit Datum_BuchungBuchung)
Gegeben seien die Relationen R(a,b), S(a,c) und
T(a,b,c). Geben Sie für jede der folgenden Aussagen an, ob Sie wahr ist oder nicht.
1. 𝑅 ⋈ 𝑆 = 𝑇
2. 𝑅 ⋈ 𝑆 = 𝑅 ⋉ 𝑆 ∪ (𝑆 ⋉ 𝑅)
3. 𝑅 ⋈ 𝑇 = σ𝑅.𝑎=𝑇.𝑎∧𝑅.𝑏=𝑇.𝑏 𝑅 × 𝑇 4. (π𝑎𝑅 ∩ π𝑎𝑆) = π𝑎 𝑅 ⋈ 𝑆
5. ( 𝑅⟗S ∪ (𝑅⋈S)) \ (R⟖S) \ (S⟖R) = ∅
Aufgabe 2
Ist 𝑅 ⋈ 𝑆 = 𝑇 wahr oder nicht?
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 11
Aufgabe 2.1
Die Aussage ist nicht wahr.
Die Relationen R und S haben keine Verbindung zu T, ein inner join der beiden kann also nicht T ergeben.
Aufgabe 2.2
Ist 𝑅 ⋈ 𝑆 = 𝑅 ⋉ 𝑆 ∪ 𝑆 ⋉ 𝑅 wahr oder nicht?
Die Aussage ist nicht wahr.
𝑅 ⋉ 𝑆 ∪ 𝑆 ⋉ 𝑅 ist nicht zulässig, da R und S nicht (Union-)kompatibel sind.
Aufgelöst hieße dies:
𝑅 ⋈ 𝑆 [𝑎, 𝑏, 𝑎, 𝑐] ≠
𝑅 ⋉ 𝑆 [𝑎, 𝑏] ∪ 𝑆 ⋉ 𝑅 𝑎, 𝑐
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 13
Aufgabe 2.3
Ist 𝑅 ⋈ 𝑇 = σ𝑅.𝑎=𝑇.𝑎∧𝑅.𝑏=𝑇.𝑏 𝑅 × 𝑇 wahr oder nicht.
Die Aussage ist wahr.
Aufgabe 2.4
Ist (π𝑎𝑅 ∩ π𝑎𝑆) = π𝑎 𝑅 ⋈ 𝑆 wahr oder nicht.
Die Aussage ist wahr.
Sei 𝑅 ≔ (𝑎1, 𝑏1 , (𝑎2, 𝑏2)}, 𝑆 ≔ { 𝑎1, 𝑐1 , (𝑎3, 𝑐3)}.
π𝑎 𝑅 ⋈ 𝑆 = π𝑎{(𝑎1, 𝑏1), 𝑎1, 𝑐1 } = 𝑎1 (π𝑎𝑅 ∩ π𝑎𝑆) = {𝑎1, 𝑎2} ∩ {𝑎1, 𝑎3} = 𝑎1
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 15
Aufgabe 2.5
Ist ( 𝑅⟗S ∪ (𝑅⋈S)) \ (R⟖S) \ (S⟖R) = ∅ wahr oder nicht.
Die Aussage ist wahr.
Zunächst: ( 𝑅⟗S ∪ (𝑅⋈S)) = 𝑅⟗S da der full outer join alle Tupel des inner joins einschließt.
Generell gilt: 𝐴\𝐵\ 𝐶 ≡ 𝐴\ 𝐵 ∪ 𝐶 für 𝐵, 𝐶 ⊆ 𝐴 Weiter: (R⟖S) ∪ (S⟖R) = 𝑅⟗S und damit:
𝑅⟗S \ 𝑅⟗S = ∅
• Tuple relational calculus
– foundation of SQL
• Domain relational calculus
– foundation of Query-by-example (QBE)
Nächste Woche
1. {′𝐾𝑎𝑛𝑡′}
2. 2
3. {′𝐽𝑜𝑛𝑎𝑠′, ′𝐶𝑎𝑟𝑛𝑎𝑝′}
4. {′𝐸𝑡ℎ𝑖𝑘′, ′𝐸𝑟𝑘𝑒𝑛𝑛𝑡𝑛𝑖𝑠𝑡ℎ𝑒𝑜𝑟𝑖𝑒′}
5. {′𝑆𝑐ℎ𝑜𝑝𝑒𝑛ℎ𝑎𝑢𝑒𝑟′, ′𝑇ℎ𝑒𝑜𝑝ℎ𝑟𝑎𝑠𝑡𝑜𝑠′, ′𝐹𝑒𝑢𝑒𝑟𝑏𝑎𝑐ℎ′}
Bei 5. wäre es auch legitim, wenn Fichte selber Teil der Lösungsmenge wäre.
Relational Database Systems 1 –Wolf-Tilo Balke –Institut für Informationssysteme –TU Braunschweig 17
Ergebnismengen Bonusaufgaben