Blatt 3- DBS (2024-25)

Aufgabe 3-1: Natural Join

Zu welcher Operation der relationalen Algebra ist der natürliche Verbund (natural join) identisch, falls beide beteiligten Relationen alle Attribute gemeinsam haben? Begründen Sie Ihre Wahl.

Dem Schnitt $\cap$, da der natural join wie der Name es schon sagt, versucht alle Attribute auf ihre natürliche Art und weise zu joinen und dies macht er indem er schaut ob die eine Attribute bei der anderen auch enthalten ist und verbindet diese dann mithilfe dieser Attribute

---

Aufgabe 3-2: Ableitung des Quotient-Operators (Relationale Algebra)

Bilden Sie die relationale Operation „Quotient“ durch die fünf relationalen Grundoperationen (Vereinigung, Differenz, Kartesisches Produkt, Selektion, Projektion) nach.

Kurze Version

Der Quotient-Operator $R÷S$ ermittelt alle $t$-Werte aus der Relation $R$, die mit jedem $s$-Wert aus der Relation $S$ verbunden sind.

Ableitung:

$$R÷S={\Pi }_{R-S}(R)-{\Pi }_{R-S}\left(({\Pi }_{R-S}(R)\times S)-R\right)$$

Schritte:

1. Projektion ($\Pi$) der relevanten Attribute von $R$.
2. Kartesisches Produkt ($\times$) mit $S$.
3. Differenz ($-$) zwischen dem Kartesischen Produkt und $R$.
4. Projektion ($\Pi$) auf die $t$-Werte der fehlenden Paare.
5. Differenz ($-$) zur Bestimmung des Quotienten.

Ausführliche Erklärung

Ziel:

Der Quotient-Operator $R÷S$ soll alle $t$-Werte aus der Relation $R$ liefern, die mit jedem $s$-Wert aus der Relation $S$ in Beziehung stehen:

$$R÷S=\{t|\forall s\in S:(t,s)\in R\}$$

Schritt-für-Schritt-Ableitung:

1. Projektion der relevanten Attribute: $${\Pi }_{R-S}(R)$$
   • Wählt die Attribute von $R$ aus, die nicht in $S$ enthalten sind (z.B. $t$).
2. Kartesisches Produkt mit $S$: $${\Pi }_{R-S}(R)\times S$$
   • Erzeugt alle möglichen Kombinationen von $t$-Werten aus $R$ mit $s$-Werten aus $S$.
3. Differenz zur Identifikation fehlender Paare: $$({\Pi }_{R-S}(R)\times S)-R$$
   • Findet die Paare $(t,s)$, die nicht in $R$ vorhanden sind.
4. Projektion auf die $t$-Werte der fehlenden Paare: $${\Pi }_{R-S}\left(({\Pi }_{R-S}(R)\times S)-R\right)$$
   • Extrahiert die $t$-Werte, die nicht mit allen $s$-Werten aus $S$ verbunden sind.
5. Endgültige Differenz zur Bestimmung des Quotienten: $$R÷S={\Pi }_{R-S}(R)-{\Pi }_{R-S}\left(({\Pi }_{R-S}(R)\times S)-R\right)$$
   • Entfernt die $t$-Werte, die nicht mit jedem $s$ aus $S$ verbunden sind, aus der ursprünglichen Projektion.

Zusammenfassung:

Durch die Kombination der Projektion, Kartesisches Produkt und Differenz wird der Quotient-Operator $R÷S$ konstruiert. Dies stellt sicher, dass nur die $t$-Werte ausgewählt werden, die mit jedem $s$-Wert aus $S$ in Beziehung stehen.

Beispiel zur Ableitung des Quotient-Operators R \div S

Gegebene Relationen:

Relation $R$ (Lehrer-Kurse):

Lehrer	Kurs
Alice	Mathe
Alice	Physik
Bob	Mathe
Bob	Chemie
Carol	Mathe
Carol	Physik
Carol	Chemie

Relation $S$ (Alle Kurse):

Kurs
Mathe
Physik
Chemie

Ziel: Finde alle Lehrer, die alle Kurse aus $S$ unterrichten.

Schritte zur Ableitung:

1. Projektion der relevanten Attribute: $${\Pi }_{\text{Lehrer}}(R)=\{\text{Alice},\text{Bob},\text{Carol}\}$$
2. Kartesisches Produkt mit $S$: $${\Pi }_{\text{Lehrer}}(R)\times S=\{(\text{Alice},\text{Mathe}),(\text{Alice},\text{Physik}),(\text{Alice},\text{Chemie}),(\text{Bob},\text{Mathe}),(\text{Bob},\text{Physik}),(\text{Bob},\text{Chemie}),(\text{Carol},\text{Mathe}),(\text{Carol},\text{Physik}),(\text{Carol},\text{Chemie})\}$$
3. Differenz zwischen Kartesischem Produkt und $R$: $$({\Pi }_{\text{Lehrer}}(R)\times S)-R=\{(\text{Alice},\text{Chemie}),(\text{Bob},\text{Physik})\}$$
   • Erklärung: Alice unterrichtet nicht Chemie und Bob unterrichtet nicht Physik.
4. Projektion auf die $t$-Werte der fehlenden Paare: $${\Pi }_{\text{Lehrer}}\left(({\Pi }_{\text{Lehrer}}(R)\times S)-R\right)=\{\text{Alice},\text{Bob}\}$$
5. Endgültige Differenz zur Bestimmung des Quotienten: $$R÷S={\Pi }_{\text{Lehrer}}(R)-{\Pi }_{\text{Lehrer}}\left(({\Pi }_{\text{Lehrer}}(R)\times S)-R\right)=\{\text{Carol}\}$$

Ergebnis: Nur Carol unterrichtet alle Kurse aus $S$.

---

Aufgabe 3-3: Kardinalitäten

Geben Sie zu jeder Grundoperation der relationalen Algebra die Kardinalität des Ergebnisses bezogen auf die Kardinalitäten der Grundmengen an. Dabei sollen obere Schranken der Ergebniskardinalität möglichst klein und untere Schranken möglichst groß sein. Markieren Sie die korrekte Ordnungsrelation und den korrekten Operand in den entsprechenden Boxen.

Beispiel:
|A ∪ A| = |A|

a) $|A\cup B|$

$$\begin{array}{rlrl}& \text{Wenn A}\wedge \text{B komplett verschieden:}& & |A\cup B|=|A|+|B|\\ & \text{Wenn A}\wedge \text{B gemeinsame Attribute haben:}& & |A\cup B|<|A|+|B|\\ & & & \\ & \text{Daraus bildet sich:}& & |A\cup B|\le |A|+|B|\end{array}$$

b) $|A-B|$

$$\begin{array}{rlrl}& \text{Wenn A}\wedge \text{B komplett verschieden:}& & |A-B|=|A|\\ & \text{Wenn A}\wedge \text{B gemeinsame Attribute haben:}& & |A-B|>|A|-|B|\\ & & & \\ & \text{Daraus bildet sich:}& & |A\cup B|\ge |A|-|B|\end{array}$$

c) $|A\times B|$

$$|A\times B|=|A|\ast |B|$$

d) $|{\Pi }_{x,y,\dots }(A)|$

$$|{\Pi }_{x,y,\dots }(A)|\le |A|$$

e) $|{\sigma }_F(A)|$

$$|{\sigma }_F(A)|\le |A|$$

---

Aufgabe 3-4: Anfragen in relationaler Algebra

Gegeben seien die Relationen Kunde, Personal, Verkauf, Inventar und Auftragsposten als Datennmodell für eine Möbel-Verkauf-Datenbank:

$$\begin{array}{rl}\text{Kunde}& :(\underline{\text{kund\_nr}},\text{kund\_name},\text{adresse},\text{ort},\text{plz})\\ \text{Personal}& :(\underline{\text{pers\_nr}},\text{nachname},\text{vorname},\text{einsatz},\text{vorgesetzt},\text{gehalt})\\ \text{Verkauf}& :(\underline{\text{auftr\_nr}},\text{bestelldatum},\text{pers\_nr},\text{kund\_nr})\\ \text{Inventar}& :(\underline{\text{art\_nr}},\text{art\_bez},\text{lagerbest},\underline{\text{lagerort}},\text{preis})\\ \text{Auftragsposten}& :(\underline{\text{auftr\_nr}},\underline{\text{art\_nr}},\text{menge})\end{array}$$

Hinweis

Am Ende des Übungsblatts finden Sie zum Verständnis eine passende Beispielausprägung des Relationsschemas. Achten Sie darauf, dass Ihre Anfrage für jede mögliche Ausprägung der Datenbank das richtige Ergebnis liefert; nicht nur für die gegebene Beispielausprägung.

Formulieren Sie die folgenden Anfragen durch Ausdrücke über der relationalen Algebra:

a) Bestimme die Artikelbezeichnung (art_bez) und den Lagerort für alle Artikel mit einem Preis von über 5000.

SELECT art_bez, lagerort FROM Inventar
WHERE preis > 5000

$${\Pi }_{\text{art\_bez, lagerort}}({\sigma }_{\text{preis}>5000}(\text{Inventar}))$$

b) Bestimme die Vornamen aller Mitarbeiter, die in München im Einsatz sind und mindestens ein Produkt seit dem 24.07.2023 verkauft haben.

Anmerkung: In der SQL Datenbank ist das Datum '2019' nicht '2023' man wird niemals ein Ergebnis für die Abfrage bekommen

SELECT DISTINCT P.vorname
FROM Personal P
JOIN Verkauf V ON P.pers_nr = V.pers_nr
WHERE P.einsatz = 'Muenchen'
  AND V.bestelldatum >= '2023-07-24';

$${\Pi }_{\text{vorname}}({\sigma }_{\text{(einsatz = ’Muenchen’)}\wedge (\text{betselldatum}\ge {}^{\prime }\mathrm{24.07.202}{3}^{\prime })}(\text{Verkauf}\bowtie \text{Personal}))$$

c) Bestimme die Personalnummern der Vorgesetzten aller Mitarbeiter, die etwas an einen Kunden aus dem Bezirk mit der PLZ „74391“ verkauft haben.

SELECT DISTINCT p.vorgesetzt
FROM Personal p
JOIN Verkauf v ON p.pers_nr = v.pers_nr
JOIN Kunde k ON v.kund_nr = k.kund_nr
WHERE k.plz = '74391';
 

$${\Pi }_{\text{vorgesetzt}}({\sigma }_{\text{(plz = ’74391’)}}(\text{Verkauf}\bowtie \text{Personal}\bowtie \text{Kunde}))$$

d) Bestimme die Auftragsnummern (auftr_nr) aller Bestellungen, die „Betten Kaiser“ aufgegeben hat und die von „Michael Roser“ bearbeitet wurden.

SELECT DISTINCT v.auftr_nr
FROM Verkauf v
JOIN Kunde k ON v.kund_nr = k.kund_nr
JOIN Personal p ON v.pers_nr = p.pers_nr
WHERE k.kund_name = 'Betten Kaiser'
  AND p.vorname = 'Michael'
  AND p.nachname = 'Roser';

$${\Pi }_{\text{auftr\_nr}}({\sigma }_{\text{(kund\_name = ’Betten Kaiser’)}\wedge (\text{vorname}={}^{\prime }Michae{l}^{\prime })\wedge (\text{nachname}={}^{\prime }Rose{r}^{\prime })}(\text{Verkauf}\bowtie \text{Kunde}\bowtie \text{Personal}))$$

e) Bestimme das Bestelldatum und die Artikelnummern (art_nr) aller Aufträge, deren Produkte im gleichen Ort lagern, in dem der Kunde seinen Sitz hat und die von einem Mitarbeiter mit einem Gehalt von unter 4500 verkauft wurden.

SELECT DISTINCT v.bestelldatum, ap.art_nr
FROM Verkauf v
JOIN Auftragsposten ap ON v.auftr_nr = ap.auftr_nr
JOIN Inventar i ON ap.art_nr = i.art_nr
JOIN Personal p ON v.pers_nr = p.pers_nr
JOIN Kunde k ON v.kund_nr = k.kund_nr
WHERE k.ort = i.lagerort
  AND p.gehalt < 4500;

$${\Pi }_{\text{bestelldatum, art\_nr}}({\sigma }_{(\text{ort=lagerort})\wedge (\text{gehalt}<4500)}(\text{Inventar}\bowtie \text{Verkauf}\bowtie \text{Personal}\bowtie \text{Auftragsposten}\bowtie \text{Kunde}))$$

---

Aufgabe 3-5: Anfragen mit dem Quotient-Operator

Gegeben seien die Relationen Lieferant (L), Teil (T) und Projekt (P) als Datenmodell für eine Lieferanten-Teile-Projekte-Datenbank. Ferner existiert eine Relation LTP, die die Beziehungen der vorgenannten Relationen modelliert:

$$\begin{array}{rl}\text{Lieferant}(L)& :(\underline{\text{lnr}},\text{lname},\text{status},\text{sitz})\\ \text{Teil}(T)& :(\underline{\text{tnr}},\text{tname},\text{farbe},\text{gewicht})\\ \text{Projekt}(P)& :(\underline{\text{pnr}},\text{pname},\text{ort})\\ \text{LTP}& :(\underline{\text{lnr}},\underline{\text{tnr}},\underline{\text{pnr}},\text{menge})\end{array}$$

Außerdem sei die Relation RT definiert durch:

$$\text{RT}={\Pi }_{\text{tnr}}\left({\sigma }_{\text{farbe}=\text{’ROT’}}(T)\right)$$

Was berechnen die folgenden Ausdrücke?

a) ${\Pi }_{\text{lnr, tnr}}(LTP)÷RT$

• Nummern der Lieferant*innen, die jedes rote Teil liefern.

  Erklärung:
  Der Quotient ${\Pi }_{\text{lnr, tnr}}(LTP)÷RT$ ermittelt alle Lieferanten (lnr), die für jedes tnr in der Relation RT (rote Teile) eine entsprechende Lieferung in LTP haben. Das Ergebnis sind die Nummern der Lieferant*innen, die alle roten Teile liefern.

b) ${\Pi }_{\text{lnr}}({\Pi }_{\text{lnr, pnr,tnr}}(LTP)÷RT)$

• Alle Lieferant*innen, die in mindestens einem Projekt alle roten Teile liefern.

  Erklärung:
  Der Quotient $LTP÷RT$ identifiziert Kombinationen von Lieferanten (lnr) und Projekten (pnr), bei denen der Lieferant in diesem Projekt alle roten Teile (tnr in RT) liefert. Die Projektion ${\Pi }_{\text{lnr, pnr}}$ extrahiert diese Lieferanten-Projekt-Kombinationen.

c) ${\Pi }_{\text{lnr}}(LTP÷RT)$

• ⇒ Alle Lieferant*innen, die jedes Rote Teil in jeder Menge an mind. Ein Projekt liefern

  Erklärung:
  Der Ausdruck $LTP÷RT$ ergibt ein leeres Set (∅), was bedeutet, dass es keine Lieferanten gibt, die jedes rote Teil in jedem Projekt liefern. Daher ist die Projektion ${\Pi }_{\text{tnr}}$ auf dieses Ergebnis ungültig und liefert ebenfalls kein sinnvolles Ergebnis.

---

Beispielausprägung für die Möbel-Verkauf-Datenbank

Kunde

kund_nr	kund_name	adresse	ort	plz
’GK4441'	'Müller & Partner'	'Schillerstr. 56'	'Stuttgart'	'74598'
'GK4442'	'Stadtverwaltung Landshut'	'Am Rathausplatz'	'Landshut'	'85321'
'GK4477'	'Betten Kaiser'	'Industriepark'	'Goettingen'	'35134'
'GK4489'	'Stadtverwaltung Köln'	'Am Dom 8'	'Koeln'	'50987'
'EK5689'	'Paula Peterson'	'Maistr. 46'	'Muenchen'	'84977'
'EK5558'	'Manfred Keller GmbH'	'Mozartstr. 45'	'Stuttgart'	'74391'
'GK4333'	'Planungsbüro Bertoldt'	'Bauhofstr. 87'	'Duisburg'	'49155'
'GK4688'	'Ulrich & Co.'	'Rosenheimer Str. 234'	'Muenchen'	'81377'
'EK8992'	'Peter Hauser'	'Am Bismarckturm 2'	'Stuttgart'	'76232'
'EK8993'	'Miriam Zechmeister KG'	'Gänsemarkt 36'	'Hamburg'	'21357'
'GK4443'	'Freiling & Partner'	'Engenser Landstr. 17'	'Koblenz'	'54621'
'GK4490'	'Einrichtungshaus Röder'	'Rheinlanddamm 199'	'Bonn'	'50321'
'GK4491'	'Stuhl Kaiser'	'Im Tal 4'	'Muenchen'	'80432'
'GK4492'	'Planmöbel GmbH'	'Am Stockborn 5'	'Frankfurt'	'61234'
'GK4493'	'Systemmöbel Wisst & Co.'	'Greinstr. 2'	'Koeln'	'53261'
'GK0310'	'Sitzmöbel Engels'	'Karl-Marx-Str. 4'	'Cottbus'	'03096’

---

Personal

pers_nr	nachname	vorname	einsatz	vorgesetzt	gehalt
1	’Winter'	'Margot'	'Muenchen’	8	4200
2	’Roser'	'Michael'	'Hamburg’	0	5200
3	’Thomas'	'Fred'	'Hamburg’	2	4000
4	’Scholl'	'Friedrich'	'Muenchen’	8	5300
5	’Hartinger'	'Roswita'	'Muenchen’	8	4800
6	’Reitzig'	'Hans-Peter'	'Frankfurt’	7	5550
7	’Moll'	'Rolf'	'Frankfurt’	0	5900
8	’Sandner'	'Ernst'	'Muenchen’	0	6300
9	’Starck'	'Brigitte'	'Koeln’	0	5200
10	’Anger'	'Lars'	'Koeln’	9	5200
11	’Freudenfeld'	'Burghard'	'Muenchen’	8	5500
12	’Neumann'	'Gabriele'	'Hamburg’	2	4900

---

Verkauf

auftr_nr	bestelldatum	pers_nr	kund_nr
20002	’2019-07-23’	5	’GK4688’
20003	’2019-07-23’	1	’EK8992’
20004	’2019-07-23’	8	’EK5558’
20005	’2019-07-24’	7	’GK4491’
20006	’2019-07-24’	12	’EK8993’
20007	’2019-07-24’	12	’GK4477’
20008	’2019-07-24’	1	’GK4442’
20009	’2019-07-24’	9	’GK4492’
20010	’2019-07-24’	11	’EK5689’
20011	’2019-07-24’	8	’GK4441’

---

Inventar

art_nr	art_bez	lagerbest	lagerort	preis
104002	’BUECHERREGAL’	2	’Muenchen’	4100.00
104002	’BUECHERREGAL’	3	’Frankfurt’	4100.00
104002	’BUECHERREGAL’	1	’Koeln’	4100.00
301001	’SCHLAFZIMMER’	20	’Koeln’	4200.00
104789	’BAUERNSCHRANK’	5	’Frankfurt’	780.00
201080	’COUCH STOCKHOLM’	4	’Muenchen’	4800.00
201081	’COUCH MIRABELL’	2	’Muenchen’	2700.00
203333	’COUCH GARNITUR KLASSIKA’	6	’Koeln’	4300.00
203333	’COUCH GARNITUR KLASSIKA’	10	’Hamburg’	4300.00
104003	’SCHRANKWAND’	0	’Muenchen’	10500.00

---

Auftragsposten

auftr_nr	art_nr	menge
20002	104002	2
20002	203333	3
20002	201080	1
20003	104888	5
20003	301001	1
20004	104003	1
20006	201080	2
20007	401001	2
20008	401000	1
20011	401001	1

---