Repetition Datenbanken 1. ER-Diagramm Für eine Musikschule soll ein ER-Diagramm erstellt werden. Folgendes soll berücksichtigt werden: Jede Lektion (findet wöchentlich statt) wird von genau einem Lehrer gehalten, es können aber mehrere Schüler an der Lektion teilnehmen. Nicht alle Schüler einer Lektion müssen das gleiche Instrument spielen. Es soll aber festgehalten werden, welcher Schüler mit welchem Instrument an einer bestimmten Lektion teilnimmt. Ein Schüler kann mehrere Instrumente spielen. Sowohl für Schüler als auch Lehrer soll der Name und die Adresse gespeichert werden (Attribut Name und Adresse). 2. Transformiere das folgende ER-Diagramm in Relationenschemas. 3. Anomalien und funktionale Abhängigkeit Eine Firma bietet Beratungen in verschiedenen Bereichen an. Im Folgenden die Tabelle Beratung mit einem Auszug der getätigten Beratungen: KundenNr 1 1 1 1 2 2 3 3 4 4 Kundenname BeraterID BeraterName Emil Schmidt 1 Helena Meier Emil Schmidt 2 Ingo Fuchs Emil Schmidt 3 John Müller Emil Schmidt 4 Elisabeth Schulz Hans Müller 2 Ingo Fuchs Hans Müller 3 John Müller Johanna Schulze 1 Helena Meier Johanna Schulze 3 John Müller Markus Schulte 1 Helena Meier Markus Schulte 2 Ingo Fuchs Aufgabe Stundenlohn AnzStunden Total IT-Berater 80€ 3 240€ Finanzberater 70€ 5 350€ IT-Berater 80€ 7 560€ Finanzberater 70€ 8 560€ Finanzberater 70€ 4 280€ IT-Berater 80€ 3 240€ IT-Berater 80€ 4 320€ IT-Berater 80€ 2 160€ IT-Berater 80€ 5 400€ Finanzberater 70€ 2 140€ Überlege dir zur obigen Tabelle folgendes: a) Welche Informationen sind redundant (also mehrfach abgespeichert)? b) Finde zu den folgenden Anomalien je ein konkretes Beispiel: Update-Anomalie Lösch-Anomalie Einfüge-Anomalie c) Ist das Attribut BeraterID funktional abhängig von Kundenname? d) Ist das Attribut Total funktional abhängig von der Attributmenge (Stundenlohn, AnzStunden)? e) Ist das Attribut Kundenname funktional abhängig von KundenNr? Und umgekehrt? 4. Normalisierung Die folgende Tabelle mit Bestelldaten ist in der 1NF. a) Zeichne das Abhängigkeitsdiagramm mit allen funktionalen Abhängigkeiten auf (verwende die Abkürzungen teilweise, vollständig, transitiv). b) Transformiere die Tabelle schrittweise in die 2. und 3. Normalform. Den Inhalt der Tabelle musst du nicht aufschreiben, nur die Namen der Attribute. 5. SQL Gehe für die folgenden SQL-Aufgaben von den Tabellen auf der Hinterseite aus. Schreibe SQL-Abfragen, die folgendes liefern (dazu dürfen keine Daten "von Hand" aus den Tabellen herausgelesen werden). (1) [2] Vorname und Nachname aller Kunden, nach Nachnamen alphabetisch absteigend geordnet (z, y, x, ...). (2) [1] Den Durchschnittspreis aller Waren, die angeboten werden. (3) [2] Wie viele Artikel werden in der Warengruppe "Kolonialwaren" angeboten? (4) [2] Welche Kunden (Nachnamen) haben am 8.1.2009 eingekauft (Format des Datums: '08.01.2009')? Jeder Nachname soll nur einmal in der Resultat-Tabelle erscheinen. (5) [2] Eine Tabelle, die zeigt welche Waren (Bezeichnung) die Kunden Pauli, Jauch und Leu gekauft haben. (6) [2.5] Für jedes Datum den Umsatz (das Total aller Einkäufe dieses Tages). (7) [2.5] Für jedes Datum die Anzahl Artikel, die Herr Vonlanten gekauft hat, aber nur die Tage, an denen er mehr als einen Artikel eingekauft hat. (10) [3] Welche Personen haben gar nichts eingekauft (Tipp: LEFT JOIN) (11) Welche Tabelle liefert die folgende SQL-Abfrage? a) [2] SELECT Name, Vorname FROM Kunde WHERE KundenNr IN (SELECT KundenNr FROM Kauf WHERE WarenNr < 400); (12) [4.5] Welche Personen (Nachnamen) haben insgesamt eine grössere Anzahl Artikel gekauft als Herr Vonlanten?