3.1.2.7
Lösungswege zu den Übungen
Die Aufgaben des Kapitels sichern Schlüssel, Fremdschlüssel und Joins. Hier steht nicht das Rechnen im Vordergrund, sondern die Erklärung der Lösungswege.
Übung 1: Primärschlüssel wählen
- Für zugelassene Autos, erhältliche Bücher, Schülerinnen und Schüler sowie Lehrkräfte sollen passende Primärschlüssel gefunden werden.
- Zusätzlich soll entschieden werden, wo ein künstlicher Primärschlüssel sinnvoll ist.
Kernidee: Ein guter Primärschlüssel bleibt eindeutig, stabil und möglichst kurz.
Besprechung
Autos können über eine Fahrzeug Identifikationsnummer stabiler erkannt werden als über das Kennzeichen. Bücher oft über ISBN, sonst über eine Buch ID. Schülerinnen, Schüler und Lehrkräfte sollten nicht über Namen identifiziert werden, sondern über künstliche IDs.
Namen sind gut für Menschen, aber schlecht als dauerhafte Primärschlüssel.
Übung 2: Klassenverwaltung
- In einer Schüler Tabelle wirkt Name zunächst als möglicher Primärschlüssel.
- Für die Noten Tabelle sollen Primärschlüssel und Fremdschlüssel bestimmt werden.
- Die Tabellen sollen so verändert werden, dass spätere Namensprobleme vermieden werden.
Kernidee: Ein aktuell eindeutiger Name kann später doppelt vorkommen oder sich ändern.
Verbesserung
Die Schüler Tabelle erhält eine künstliche SID. Die Noten Tabelle speichert diese SID als Fremdschlüssel. Zusätzlich kann die Noten Tabelle eine eigene NID als Primärschlüssel bekommen.
schueler(SID, Name, Vorname, ...)
noten(NID, SID, Fach, Art, Note, ...)Übung 3: Ort Tabelle verändern
Die Tabelle ort aus dem vorherigen Kapitel soll so verändert werden, dass von dort auf die passenden Länderdaten verwiesen werden kann.
Kernidee: Der Ländername wird als Verbindung durch einen stabilen Fremdschlüssel ersetzt.
Lösungsmodell
Die Ort Tabelle bekommt einen Primärschlüssel ONR. Statt des Ländernamens speichert sie LNR als Fremdschlüssel auf land.LNR.
ort(ONR, Name, LNR, Einwohner, Laenge, Breite)
land(LNR, Name, Einwohner, Flaeche, HauptONR, KNR)
Übung 4: Länder und Kontinente
- Alle Länder mit Namen und Einwohnerzahl ausgeben, die in Asien liegen.
- Die asiatischen Länder nach Einwohnerzahl absteigend sortieren.
- Europäische Länder mit mehr als 300000 Quadratkilometern Fläche finden.
- Interne Bedingungen wie land.KNR = EU sollen vermieden werden.
Asien, fachlich über Kontinentname
Die Bedingung verwendet den Namen des Kontinents. Die interne Kontinentnummer dient nur zum Verbinden der Tabellen.
SELECT land.Name, land.Einwohner
FROM land, kontinent
WHERE land.KNR = kontinent.KNR
AND kontinent.Name = 'Asien';Mit Sortierung
ORDER BY land.Einwohner DESC sortiert die Ergebnistabelle absteigend.
SELECT land.Name, land.Einwohner
FROM land, kontinent
WHERE land.KNR = kontinent.KNR
AND kontinent.Name = 'Asien'
ORDER BY land.Einwohner DESC;Europa mit Fläche über 300000
Die Flächenbedingung gehört zum Land, die Kontinentbedingung zur verbundenen Kontinent Tabelle.
SELECT land.Name, land.Flaeche
FROM land, kontinent
WHERE land.KNR = kontinent.KNR
AND kontinent.Name = 'Europa'
AND land.Flaeche > 300000;Übung 5: Orte, Länder, Kontinente
- Alle Orte mit mehr als 10 Mio. Einwohnern mit ihren Ländern ausgeben und absteigend sortieren.
- Orte in Europa mit mehr als 1 Mio. Einwohnern finden.
- Orte mit mehr als 1 Mio. Einwohnern finden, deren Länder weniger als 10 Mio. Einwohner haben.
- Eine Tabelle mit den Hauptstädten der Länder erzeugen. Das Attribut HauptONR kann helfen.
Kernidee: Sobald drei Tabellen beteiligt sind, braucht die Abfrage zwei Joinbedingungen.
Orte und Länder
ort.LNR = land.LNR verbindet jeden Ort mit seinem Land.
SELECT ort.Name, ort.Einwohner, land.Name
FROM ort, land
WHERE ort.LNR = land.LNR
AND ort.Einwohner > 10000000
ORDER BY ort.Einwohner DESC;Orte in Europa
Für Europa muss zusätzlich die Tabelle kontinent verbunden werden.
SELECT ort.Name, ort.Einwohner, land.Name
FROM ort, land, kontinent
WHERE ort.LNR = land.LNR
AND land.KNR = kontinent.KNR
AND kontinent.Name = 'Europa'
AND ort.Einwohner > 1000000;Hauptstädte über HauptONR
land.HauptONR verweist auf die Ortsnummer der Hauptstadt.
SELECT land.Name, ort.Name
FROM land, ort
WHERE land.HauptONR = ort.ONR;Die SQL Beispiele verwenden die Schlüsselidee aus Terra 3: Fremdschlüssel verbinden Tabellen, fachliche Bedingungen beschreiben den gesuchten Inhalt. inf-schule Terra 3.1.2.7