====== n-m Beziehungen ======

In normalisierten Datenbanken kommen häufig "Beziehungstabellen" zum Einsatz, welche die Verbindung zwischen den Entitäten herstellen. **n-m-Beziehungen benötigen immer eine solche Beziehungstabelle**.

===== Beispiel =====



{{ :faecher:informatik:oberstufe:datenbanken:nm_beziehungen:erfilme.png |}}


{{ :faecher:informatik:oberstufe:datenbanken:nm_beziehungen:tabellen.png?300 |}}


Die Struktur der Beziehungstabelle sieht so aus:

''schauspieler_film(__sfid__, sid↑ , fid↑)''

Der Surrogatschlüssel ''sfid'' ist hier optional, man kann auch die Kombination der beiden "kpünstlichen" Schlüssel ''sid'' und ''fid'' als Primärschlüssel verwenden: 

''schauspieler_film(__sid↑__ , __fid↑__)''

Das vollständige Schema sieht also so aus:

''schauspieler_film(__sfid__, sid↑ , fid↑)''\\
''schauspieler(__sid__, SVorname, SNachname)''\\
''filme(__fid__, titel)''



Eine kurze Recherche ergibt (unter anderem), dass Meg Ryan in "Harry And Sally" mitspielt. Um diese Beziehung abzubilden, muss in unserer schauspieler_film-Tabelle eine Zeile der Form 

^ sfid  | sid  | fid  |
| 1     | 4    | 2    |
 

eingefügt werden. Der Umstand, dass John Belushi eine Rolle in Blues Brothers spielt, führt zu einer weiteren Zeile:

^ sfid  | sid  | fid  |
| 1     | 4    | 2    |
| 2     | 6    | 1    |

----
{{:aufgabe.png?nolink  |}}
=== (A1) ===

Ergänze die Beziehungstabelle unter Verwendung entsprechender Ressourcen (Suchmaschine).

In manchen Fällen macht es Sinn, dass Beziehungstabellen neben den eigentlichen Beziehungen **zusätzliche Informationen speichern, die zu einer bestimmten Beziehung gehören**. Im Fall der Filmdatenbank könnte das z.B. die Gage sein oder ob eine Schauspielerin für die Roll in diesem Film einen Oskar erhalten hat.

Ergänze das Scheme der Beziehungstabelle um die beiden im Beispiel genannten Felder.


----

<WRAP center round box 90%>
Importiere für die folgenden Übungen die Tabellen der normalisierten Zahnarztbedarfsdatenbank in deine Übungsdatenbank. ({{ :faecher:informatik:oberstufe:datenbanken:nm_beziehungen:zahnarztbedarf_2nf.zip |}})
</WRAP>


{{:aufgabe.png?nolink  |}}
=== (A2) ===

Erstelle ein ER-Diagramm für die Datenbank. Überführe das ER Modell in ein relationales Datenbankschema.


----
{{:aufgabe.png?nolink  |}}
=== (A3) ===

  - Gib ein SQL-Statement an, das alle Produkte der Firma  mit Hilfe des Filters ''WHERE hersteller.firma = 'Eisen Karl' '' auflistet((Du sollst also nicht "von Hand" zuerst die Hersteller ID nachschauen...)).
  - Gib ein SQL-Statement an, das alle Bestellungen (Produkt und Anzahl) von Viktoria auflistet.
  - Gib ein SQL-Statement an, das den Rechnungsbetrag von Dr. Blutgesicht ausgibt.
  - Gib ein SQL-Statement an, das alle Doktoren ausgibt, die Zement gekauft haben
  - Gib ein SQL-Statement an, das alle Doktoren ausgibt, deren Rechnungsbetrag über 100EUR liegt



++++ Lösung 1 |

<code sql>
SELECT produkte FROM hersteller, produkte, bestellungen 
WHERE bestellungen.produkt_id = produkte.id
AND bestellungen.hersteller_id = hersteller.id
AND hersteller.firma = "Eisen-Karl"
</code>

++++

++++ Lösung 2 |
<code sql>
SELECT produkt, anzahl FROM hersteller, doktoren, produkte, bestellungen 
WHERE bestellungen.produkt_id = produkte.id
AND bestellungen.doktor_id = doktoren.id
AND bestellungen.hersteller_id = hersteller.id
AND doktoren.vorname = "Viktoria"
</code>
++++

++++ Lösung 3 |
<code sql>
SELECT sum(preis*anzahl) AS rechnung, doktoren.name FROM hersteller,doktoren, produkte, bestellungen 
WHERE bestellungen.produkt_id = produkte.id
AND bestellungen.doktor_id = doktoren.id
AND bestellungen.hersteller_id = hersteller.id
AND doktoren.name = "Blutgesicht"
</code>
++++

++++ Lösung 4 |
<code sql>
select vorname, name from bestellungen, doktoren, produkte 
WHERE bestellungen.produkt_id=produkte.id 
AND bestellungen.doktor_id=doktoren.id 
AND produkte.produkt="Zement";
</code>
++++

++++ Lösung 5 |
<code sql>
SELECT sum(preis*anzahl) AS rechnung, doktoren.name FROM hersteller,doktoren, produkte, bestellungen 
WHERE bestellungen.produkt_id = produkte.id
AND bestellungen.doktor_id = doktoren.id
AND bestellungen.hersteller_id = hersteller.id
GROUP BY doktoren.name
HAVING rechnung >100
</code>
++++

----
{{:aufgabe.png?nolink  |}}
=== (Bonus 1) ===

Teste das folgende SQL Statement:

<code sql>
SELECT DISTINCT produkt, firma FROM produkte p
INNER JOIN bestellungen b ON p.id=b.produkt_id
INNER JOIN hersteller h ON h.id=b.hersteller_id
WHERE h.firma = "Eisen-Karl"
</code>

Was wird hier abgefragt? Experimentiere mit der WHERE Bedingung und mit den angezeigten Feldern.