DBS-Blatt 12

Aufgabe 12-1 ER-Modell

Aufgabenstellung

In einer relationalen Datenbank sollen Informationen über die XXIII. Olympischen Winterspiele in Pyeongchang gespeichert werden.

Die einzelnen Wettkämpfe besitzen eine Wettkampfnummer, den Namen der Sportart, einen Termin und eine Wettkampfstätte. An jedem Wettkampf nehmen beliebig viele Sportler teil, die durch eine Startnummer identifiziert werden. Jeder Wettkampf wird von genau einem Schiedsrichter geleitet, der eine eindeutige Schiedsrichternummer besitzt. Es sollen die Nationen mit eindeutigem Kürzel und dem vollen Namen abgespeichert werden. Jede Person besitzt einen Namen und gehört zu einer Nation.

(a) Entwerfen Sie zu diesem Zweck ein E/R-Modell.

Scrollen und zoomen mit STRG gedrückt

(b) Setzen Sie das E/R-Diagramm in ein relationales Datenbankschema um.

$$Wettkampf(\underline{WK-Nr},Sportart,Zeit,Ort)$$ $$Sportler(\underline{Start-Nr},\overline{Nation},Name)$$ $$Schiedsrichter(\underline{Schiri-Nr},\overline{Nation},Name)$$ $$Nation(\underline{K\ddot{u}rzel},Name)$$ $$nimmt\_teil(\overline{Start-Nr},\overline{WK-Nr})$$

---

Aufgabe 12-2 Relationale Algebra

Aufgabenstellung

Gegeben seien folgende Relationen R und S.

$$\begin{array}{cccc}\text{A}& \text{B}& \text{C}& \text{D}\\ 1& 2& 3& 4\\ 4& 3& 2& 1\\ 1& 2& 2& 5\\ 4& 3& 3& 4\\ 1& 2& 2& 4\\ 4& 3& 3& 5\end{array}$$ $$\begin{array}{ccc}\text{C}& \text{D}& \text{E}\\ 3& 4& 1\\ 2& 5& 2\\ 2& 4& 3\end{array}$$

Geben Sie die Ergebnisrelationen folgender Ausdrücke der relationalen Algebra also Tabellen an. Wenn nötig, ist die Duplikat-Elimination durchzuführen.

(a) Vereinigung

$${\pi }_{A,B,D}(R)\underset{D<E}{\bowtie }{\pi }_{C,E}({\sigma }_{D<5}(S))$$

Lösung

$$\begin{array}{cccc}\text{A}& \text{B}& & \text{D}\\ 1& 2& & 4\\ 4& 3& & 1\\ 1& 2& & 5\\ 4& 3& & 4\\ 1& 2& & 4\\ 4& 3& & 5\end{array}$$ $$\underset{D<E}{\bowtie }$$ $$\begin{array}{ccc}\text{C}& & \text{E}\\ 3& & 1\\ & & \\ 2& & 3\end{array}$$ $$\Downarrow$$ $$\begin{array}{ccccc}\text{A}& \text{B}& \text{D}& \text{C}& \text{E}\\ 4& 3& 1& 2& 3\end{array}$$

(b) Quotient

$$R÷{\pi }_{C,D}(S)$$

Lösung

$$\begin{array}{cccc}\text{A}& \text{B}& \text{C}& \text{D}\\ \ast 1\ast & \ast 2\ast & \ast \ast 3\ast \ast & \ast \ast 4\ast \ast \\ 4& 3& 2& 1\\ \ast 1\ast & \ast 2\ast & \ast \ast 2\ast \ast & \ast \ast 5\ast \ast \\ 4& 3& \ast \ast 3\ast \ast & \ast \ast 4\ast \ast \\ \ast 1\ast & \ast 2\ast & \ast \ast 2\ast \ast & \ast \ast 4\ast \ast \\ 4& 3& 3& 5\end{array}$$ $$÷$$ $$\begin{array}{ccc}\text{C}& \text{D}& \\ 3& 4& \\ 2& 5& \\ 2& 4& \end{array}$$ $$\Downarrow$$ $$\begin{array}{cc}\text{A}& \text{B}\\ 1& 2\end{array}$$

(c) Quotient

$$R÷{\pi }_D(S)$$

Lösung

$$\begin{array}{cccc}\text{A}& \text{B}& \text{C}& \text{D}\\ 1& 2& 3& 4\\ 4& 3& 2& 1\\ 1& 2& 2& 5\\ 4& 3& 3& 4\\ 1& 2& 2& 4\\ 4& 3& 3& 5\end{array}$$ $$÷$$ $$\begin{array}{ccc}& \text{D}& \\ & 4& \\ & 5& \\ & & \end{array}$$ $$\Downarrow$$ $$\begin{array}{ccc}\text{A}& \text{B}& \text{C}\\ 1& 2& 2\\ 4& 3& 3\end{array}$$

---

Aufgabe 12-3 Relationale Algebra | Tupelkalkül | Bereichskalkül

Aufgabenstellung

Gegeben sei das folgende Relationenschema. Dabei sind die Schlüsselattribute jeweils unterstrichen.

$$\text{Produkt}(\underline{\text{PNR}},\text{Name},\text{Gewicht},\text{Farbe})$$ $$\text{Filiale}(\underline{\text{FNR}},\text{Name},\text{Ort})$$ $$\text{Angestellter}(\underline{\text{PersNr}},\text{Name},\text{Gehalt},\text{FNR})$$ $$\text{Verkauf}(\underline{\text{PNR}},\underline{\text{PersNr}},\underline{\text{Datum}},\text{VKPreis})$$

Geben Sie für die folgende verbal formulierte Anfrage einen äquivalenten Ausdruck in Relationaler Algebra und wahlweise in relationalem Tupel- oder Bereichskalkül an. Für Anfragen im Tupelkalkül soll darüber hinaus das Schema aller freien Variable angegeben werden. Sie können an passender Stelle auch den Join-Operator benutzen.

• Bestimmen Sie Name und Verkaufspreis aller Produkte, die der Angestellte Peter Müller am 04.02.2017 verkauft hat.

Relationale Algebra

Bedingung vor Join

$${\Pi }_{Angestellter.Name,Verkauf.VKPreis}({\sigma }_{Angestellter.Name{=}^{\prime }PeterM\ddot{u}lle{r}^{\prime }\wedge Verkauf.Datum=\mathrm{04.02.2017}}(Produkt\underset{Produkt.PNR=Verkauf.PNR}{\bowtie }Verkauf\underset{Verkauf.PersNr=Angestellter.PersNr}{\bowtie }Angestellter))$$

Bedingung nach Join

$${\Pi }_{\text{Produkt.Name, VKPreis}}\left(\text{Produkt}{\bowtie }_{\text{Produkt.PNR}=\text{Verkauf.PNR}}\left({\sigma }_{\text{Name}{=}^{\prime }PeterM\ddot{u}lle{r}^{\prime }}(\text{Angestellter})\bowtie {\sigma }_{\text{Datum}{=}^{\prime }\mathrm{04.01.201}{7}^{\prime }}(\text{Verkauf})\right)\right)$$

Tupelkalkül

$$Schema(pro)=Schema(Produkt);$$ $$\{[pro.Name,ver.VKPreis]\}|pro\in Produkt\wedge (\exists ver\in Verkauf,ang\in Angestellter)$$ $$ang.Name{=}^{\prime }PeterM\ddot{u}lle{r}^{\prime }\wedge ver.Datum{=}^{\prime }\mathrm{04.02.201}{7}^{\prime }$$

Bereichskalkül

$$\{proname,vkpreis|\exists ProNr,PersNr:$$ $$Produkt(ProNr,proname,\_,\_)\wedge$$ $$Verkauf(ProNr,PersNr{,}^{\prime }\mathrm{04.02.201}{7}^{\prime },vkpreis)\wedge$$ $$Angestellter(PersNr{,}^{\prime }PeterM\ddot{u}lle{r}^{\prime },\_,\_)\}$$

---

Aufgabe 12-4 SQL

Aufgabenstellung

Gegeben sei das folgende Relationenschema zur Fußball-Weltmeisterschaft.

• $Mannschaft(\underline{Land},Trainer)$ >
  
• $Spieler(\underline{SpielerNr},Name,\underline{Mannschaft},Geburtsdatum,Position)$ >
  
• $Austragungsort(\underline{Stadion},Platze)$ >
  
• $Schiedsrichter(\underline{SID},Name,Nationalitat)$ >
  
• $Spiel(\underline{SpielID},Tag,MannschaftA,MannschaftB,Austragungsort,Zuschauer)$ >
  
• $Tor(\underline{Spieler},\underline{Mannschaft},\underline{Spiel},\underline{Minute})$ >
  
• $leitet(\underline{Spiel},\underline{Schiedsrichter})$ >
  > Formulieren Sie die folgenden Anfragen in SQL.

a) Bestimmen Sie für jeden Austragungsort die Anzahl der Spiele, die dort stattgefunden haben. Beachten Sie, dass auch Austragungsorte gespeichert wurden, an denen dann kein Spiel stattgefunden hat.

SELECT Stadion, COUNT(SpielID) FROM Austragungsort
LEFT JOIN Spiel ON Spiel.Austragungsort = Austragungsort.Stadion
GROUP BY Austragungsort.Stadion

b) Bestimmen Sie die Anzahl der Spiele, die Spanien im Laufe der WM gespielt hat, in denen mindestens ein Tor fiel.

SELECT COUNT(DISTINCT Spiel.SpielID) AS Anzahl_Spiele FROM Spiel
JOIN Tor ON Spiel.SpielID = Tor.Spiel
WHERE (Spiel.MannschaftA = 'Spanien' OR Spiel.MannschaftB = 'Spanien');

1. SELECT COUNT(DISTINCT Spiel.SpielID) AS Anzahl_Spiele: Diese Zeile zählt die einzigartigen Spiel-IDs. Mit COUNT(DISTINCT …) wird sichergestellt, dass jedes Spiel, unabhängig davon, wie viele Tore darin gefallen sind, nur einmal gezählt wird.

2. FROM Spiel JOIN Tor ON Spiel.SpielID = Tor.Spiel: Diese Zeile verknüpft die Tabellen Spiel und Tor über die Spiel-ID. Das bedeutet, dass nur Spiele berücksichtigt werden, in denen mindestens ein Tor gefallen ist (da nur solche Spiele in der Tor-Tabelle aufgeführt werden).

3. WHERE (Spiel.MannschaftA = ‘Spanien’ OR Spiel.MannschaftB = ‘Spanien’): Diese Bedingung filtert die Spiele, in denen Spanien entweder also Mannschaft A oder also Mannschaft B beteiligt war.

Die Kombination dieser Elemente führt dazu, dass die Abfrage die Anzahl der unterschiedlichen Spiele liefert, in denen Spanien gespielt hat und in denen mindestens ein Tor erzielt wurde, wobei jedes Spiel nur einmal gezählt wird, auch wenn mehrere Tore fielen.

Weitere Lösungen

SELECT COUNT(SpielID) FROM spiel s JOIN Tor t ON s.SpiellD = t.spiel
WHERE MannschaftA = 'Spanien' OR MannschaftB = 'Spanien'
GROUP BY SpielID;

SELECT COUNT(SpiellD) FROM Spiel
WHERE (MannschaftA= 'Spanien' OR MannschaftB = 'Spanien') AND
EXISTS (SELECT * FROM Tor WHERE Tor.Spiel = Spiel.SpiellD);

---

Aufgabe 12-5 Funktionale Abhängigkeiten

Aufgabenstellung a)

Gegeben sei das Relationenschema $R_1(A,B,C,D,E,F)$, sowie die Menge der zugehörigen nicht-trivialen funktionalen Abhängigkeiten:

$$\{A,B\to DB,C\to EB\to F\}$$

a) Bestimmen Sie die Menge der Schlüsselkandidaten von R1. Geben Sie dazu alle Schlüsselkandidaten an und erläutern Sie, warum es keine weiteren Schlüsselkandidaten gibt

$$Schl\ddot{u}sselkandidatenS=\{A,B,C\}$$

• $A,B\to D$ → $R_1(\cancel{A},\cancel{B},C,\cancel{D},E,F)$ → $R_1(C,E,F)$

• $B,C\to E$ → $R_1(\cancel{C},\cancel{E},F)$ → $R_1(F)$

• $B\to F$ → $R_1(F)$ → $R_1(\cancel{F})$

$⟹$ Mithilfe von $\{A,B,C\}$ kann man alle Attribute herleiten. Zudem ist diese Menge $S$ eindeutig und minimal

b) Bestimmen Sie die kanonische Überdeckung $F_C$ zu $F$. Geben Sie dazu die kanonische Überdeckung an und erläutern Sie, wie sie erreicht wird. Synthesealgorithmus

Aufgabenstellung b)

Gegeben sei das Relationenschema $R_2(A,B,C,D,E,F)$, sowie die Menge der zugehörigen nicht-trivialen funktionalen Abhängigkeiten:

$$\{A,B,C\to D,D\to E,B,C\to D,E,F,E\to F\}$$

Erinnerung

Schritte des Synthesealgorithmus:

• Linksreduktion
• Rechtsreduktion
• Entfernung von rechtsleeren Abhängigkeiten
• Zusammenfassen von Abhängigkeiten mit gleicher linker Seite
• Neues Relationsschema erzeugen
• Rekonstruktion eines Schlüsselkandidaten:
• Elimination überflüssiger Relationen

$$R_2(A,B,C,D,E,F)$$ $$F=\{A,B,C\to D,D\to E,B,C\to D,E,F,E\to F\}$$

Linksreduktion

• $A,B,C\to D$ → $\cancel{A},B,C\to D$ → $B,C\to D$

  • Da gilt $D\in (F,\{A,B,C\}-A)=D$
  • Wir können $A$ in der funktionalen Abhängigkeit $A,B,C\to D$ weglassen, weil $D$ bereits durch $B$ und $C$ alleine hergeleitet werden kann.

• $D\to E$

  • minimal gibt nix zu ändern

• $B,C\to D,E,F$

  • gibt nix zu ändern

• $E\to F$

  • minimal gibt nix zu ändern

Rechtsreduktion

Falsche Anwendung der Rechtsreduktion zum vorbeugen von Fehlern

• $B,C\to D$
  • $B,C\to \varnothing$
  • $D$ wird abgedeckt durch $B,C\to D,E,F$
• $D\to E$
  • $D\to \varnothing$
  • $E$ wird abgedeckt durch $B,C\to D,E,F$
• $B,C\to D,E,F$
  • bleibt unverändert
• $E\to F$
  • $E\to \varnothing$
  • $F$ wird abgedeckt durch $B,C\to D,E,F$

nicht korrekt, weil das Entfernen von $E$ und $F$ aus diesen Abhängigkeiten bedeuten würde, dass sie aus der Überdeckung komplett verschwinden. Das ist nicht das Ziel der Rechtsreduktion. Das Ziel ist es, Redundanzen zu entfernen, nicht aber die komplette Abhängigkeit.

Richtige Anwendung der Rechtsreduktion

• $B,C\to D$
  • $B,C\to \varnothing$
  • $D$ wird abgedeckt durch $B,C\to D,E,F$
• $D\to E$
  • bleibt unverändert
• $B,C\to D,E,F$
  • $B,C\to D,\cancel{E},\cancel{F}$
  • $E$ und $F$ werden abgedeckt durch $D\to E$ und $E\to F$
• $E\to F$
  • bleibt unverändert

Wir behalten $D\to E$ und $E\to F$ in der kanonischen Überdeckung, da diese direkten funktionalen Abhängigkeiten eine klare und eigenständige Beziehung zwischen den Attribute definieren, die nicht implizit durch $B,C\to D,E,F$ abgedeckt sind.

c) Entfernung von rechtsleeren Abhängigkeiten

$$F=\{B,C\to \varnothing ,D\to E,B,C\to DE\to F\}$$ $$wirdzu$$ $$F=\{D\to E,B,C\to DE\to F\}$$

d) Zusammenfassen von Abhängigkeiten mit gleicher linker Seite

Gibt keine gleiche linke Seite $$⟹F_c=\{D\to E,B,C\to DE\to F\}$$

e) Neues Relationsschema erzeugen

• $R_1(\underline{D},E)\to F_1=\{D\to E\}$

• $R_2(\underline{B},\underline{C},D)\to F_2=\{B,C\to D\}$

• $R_3(\underline{E},F)\to F_3=\{E\to F\}$

f) Rekonstruktion eines Schlüsselkandidaten:

Unsure

Unsicher ob dies nötig, da in $R_2$ schon $B$ und $C$ enthalten

Neue Relation für Schlüsselkandidaten $\{B,C\}$

$$⟹R_4(\underline{B},\underline{C})\to F_4=\varnothing$$

g) Elimination überflüssiger Relationen

In diesem Schritt nix zu tun

Finale Relationen:

• $R_1(\underline{D},E)$

• $R_2(\underline{B},\underline{C},D)$

• $R_3(\underline{E},F)$

• $Unsicher:(R_4(\underline{B},\underline{C})$

---