Lösung - nicolasruh
Werbung
Repetition Datenbanken
1. 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.
Kunde(KundenNr, Vorname, Nachname, Ort)
Auftrag(AuftragNr, Datum, KundenNr)
enthält(AuftragNr, ISBN, Anzahl)
Buch(ISBN, Autor, Titel, Name, Ort)
Verlag(Name, Ort, Beschreibung)
1
1
1
1
1
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€
Aufgaben
Ü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
a) Ist das Attribut BeraterID funktional abhängig von Kundenname?
b) Ist das Attribut Total funktional abhängig von der Attributmenge (Stundenlohn,
AnzStunden)?
c) Ist das Attribut Kundenname funktional abhängig von KundenNr? Und umgekehrt?
Normalisierung
Die folgende Tabelle mit Bestelldaten ist in der 1NF. Transformiere sie schrittweise in die 2.
und 3. Normalform. Zeichne die entsprechenden Relationenschemas mit allen
Abhängigkeiten auf (verwende die Abkürzungen teilweise, vollständig,
transitiv). Den Inhalt der Tabelle musst du nicht aufschreiben, nur die Namen der
Attribute.
KNr
Nachname
Ort
181
Meier
Genf
ArtikelNr ArtikelBezeichnung Preis
25
Papier A4
5.50
Anzahl
Total
2
11.-
Lösung
Abhängigkeitsdiagramm
2NF
KNr Name
Ort
Artikel-Nummer Artikel-Bezeichnung
KNr ArtikelNr
Anz
Preis
Total
Das Total ist vollständig abhängig vom Schlüssel, gleichzeitig aber auch funktional
abhängig von ArtikelNr und Anz (also transitiv abhängig, weil eine Abhängigkeit von
einem nicht-Schlüsselattribut besteht).
3NF
KNr Name
Straße
Nr
Ort
Artikel-Nummer Artikel-Bezeichnung
KNr ArtikelNr
Datum
Preis
Anz
Die transitive Abhängigkeit wird dadurch aufgelöst, dass das Total einfach ganz
weggelassen wird. Das ist zulässig, weil die Information redundant ist (sie kann aus
anderen Feldern der DB hergeleitet werden).
SQL
Gehe für die folgenden SQL-Aufgaben von diesen Tabellen 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, ...).
SELECT Name
FROM Kunde
ORDER BY Name DESC
(2) [1] Den Durchschnittspreis aller Waren, die angeboten werden.
SELECT AVG(Preis)
FROM Waren
(3) [2] Wie viele Artikel werden in der Warengruppe "Kolonialwaren" angeboten?
SELECT COUNT(*)
FROM Waren
WHERE Warengruppe = "Kolonialwaren";
(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.
SELECT DISTINCT Name
FROM Kunde NATURAL JOIN Kauf
WHERE Datum = '08.01.2009';
(5) [2] Eine Tabelle, die zeigt welche Waren (Bezeichnung) die Kunden Pauli, Jauch und
Leu gekauft haben.
SELECT Bezeichnung, Name
FROM Kunde NATURAL JOIN Kauf NATURAL JOIN Ware
WHERE Name IN ('Pauli', 'Jauch', 'Leu');
(6) [2.5] Für jedes Datum den Umsatz (das Total aller Einkäufe dieses Tages).
SELECT Datum, sum(Preis*Menge)
FROM Ware NATURAL JOIN Kauf
GROUP BY Datum
(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.
SELECT Datum, sum(Menge)
FROM Kunde NATURAL JOIN Kauf
WHERE Name = "Vonlanten"
GROUP BY Datum
HAVING sum(Menge) > 1
(10) [3] Welche Personen haben gar nichts eingekauft (Tipp: LEFT JOIN)
SELECT Name
FROM Kunde LEFT JOIN Kauf
ON Kunde.KundenNr = Kauf.KundenNr
WHERE WarenNr IS NULL;
(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);
Name
Vorname
Emmengger Roland
Vonlanten
Peter
(12) [4.5] Welche Personen (Nachnamen) haben insgesamt eine grössere Anzahl Artikel
gekauft als Herr Vonlanten?
SELECT Name
FROM Kunde NATURAL JOIN Kauf
GROUP BY Name
HAVING sum(Menge) > (
SELECT sum(Menge)
FROM Kunde NATURAL JOIN Kauf
WHERE Name = 'Vonlanten');
