Datenbanken Inhaltsverzeichnis

Datenbanken
M. Jakob
Gymnasium Pegnitz
22. Januar 2017
Inhaltsverzeichnis
Grundlagen eines Datenbankmanagementsystems (DBMS)
Grundlegende Datenbankabfragen ohne verknüpfte
Selektionen
Erweiterte Abfragen an eine Tabelle
Gruppieren und Zusammenfassen von Datensätzen
Verknüpfung von Tabellen
Syntaxdiagramme
Beziehungen zwischen Klassen
In diesem Abschnitt
Grundlagen eines Datenbankmanagementsystems (DBMS)
Speichern großer Datenmengen
Klassendiagramme und Datentypen
Aufbau von Datentabellen
Datenbanken
1
Grundlagen
1
Speichern großer Datenmengen
Speichern großer Datenmengen
Beim Suchen bestimmter Artikel wird eine interne Datenbank
befragt und die Ergebnisse beliebig sortiert ausgegeben.
Gleichzeitig greifen weltweit viele Benutzer gleichzeitig auf die
Datenbank zu.
4 / 137
Datenbanken
1
Grundlagen
1
Speichern großer Datenmengen
Verwendung von Datenbanken
Heutige tägliche Kommunikationsabläufe sind ohne
Datenbanken nicht mehr denkbar.
I
Alle möglichen Internetseiten (CD-Datenbanken,
Datenbank für Online-Spiele)
I
Kundenverwaltung der Telekom
I
Flugbuchungssysteme von Reisebüros
I
Personendaten Einwohnermeldeämter
I
Jede Form der Lagerverwaltung (Kaufhäuser,
Supermärkte, Materiallager)
5 / 137
Datenbanken
1
Grundlagen
1
Speichern großer Datenmengen
Anforderungen an eine Datenbank
1. Die Daten sollten einfach erfasst, gespeichert und
abgerufen werden können.
2. Eingabefehler sollten vom System erkannt werden.
3. Durch Abfragen sollen Informationen gezielt abgerufen
werden können.
4. Verschiedene Sachbearbeiter (Anwendungen) müssen
unabhängig voneinander die Datenbestand bearbeiten
können.
6 / 137
Datenbanken
1
Grundlagen
1
Speichern großer Datenmengen
Zusammenfassung
Datenbank
Große Datenmengen werden so gespeichert, dass gezielt
ausgewählte Teile je nach Fragestellung geeignet
zusammengestellt werden können. Der Anwender sollte dabei
niemals direkt auf die Daten zugreifen sondern ein
Datenbankmanagementsysteme (DBMS) verwenden. So sind
die Daten vor unsachgemäßer Nutzung geschützt.
Anwendung
Anwendung
Anwendung
Anwendung
Anwendung
DBMS
Daten
Datenbank
7 / 137
Datenbanken
1
Grundlagen
1
Speichern großer Datenmengen
Übung
• Ü 1.1: Grenzen von normalen Datentabellen
Betrachte die Datei Einkauf.ods, sie zeigt eine Zusammenstellung von gekauften
Waren. Beantworte folgende Fragen und gib an, für warum und wen diese Frage von
Interesse sein könnte.
(a) Welches Produkt war am teuersten?
(b) Wie viele Artikel wurden bei Karma gekauft?
(c) Wer hat insgesamt am meisten Geld ausgegeben?
(d) Wie viele Artikel wurden bei Oldi am 26.8.2007 gekauft?
(e) Formuliere in einem Satz, warum die Datei Einkauf.ods nicht geeignet ist, große
Datenmengen zu speichern.
8 / 137
Datenbanken
1
Grundlagen
1
Speichern großer Datenmengen
Übung
• Ü 1.2: Grenzen von normalen Datentabellen
Nenne zu jeder oben aufgeführten Anforderungen an eine Datenbank mindestens ein
Beispiel aus der Datei Einkauf.ods, bei dem diese Anforderung nicht erfüllt ist.
9 / 137
In diesem Abschnitt
Grundlagen eines Datenbankmanagementsystems (DBMS)
Speichern großer Datenmengen
Klassendiagramme und Datentypen
Aufbau von Datentabellen
Datenbanken
1
Grundlagen
2
Klassendiagramme und Datentypen
Beispiel Bibliotheksverwaltung
Jedes Attribut einer Klasse besitzt einen bestimmten Datentyp.
Nur so kann das DBMS wissen, wie die Daten interpretiert
werden sollen.
11 / 137
Datenbanken
1
Grundlagen
2
Klassendiagramme und Datentypen
Datentypen in SQL (unserer DB-Sprache)
Datentyp
VARCHAR[n]
CHAR[n]
INT
FLOAT
DOUBLE
DECIMAL[n;d]
DATE
TIME
DATETIME
Beschreibung
Variable Zeichenfolge von max. n Zeichen
Zeichenfolge von genau n Zeichen
(Rest Leerzeichen)
Ganze Zahlen (etwa von -2Mrd. Bis
+2Mrd.)
Kommazahlen mit 7 gültigen Ziffern
Kommazahlen mit 15 gültigen Ziffern
Kommazahl mit d Dezimalstellen und
insgesamt n Ziffern
Datumsangaben
Zeitangaben
Kombination aus Datums- und Zeitangabe
12 / 137
Datenbanken
1
Grundlagen
2
Klassendiagramme und Datentypen
Übung
• Ü 1.3: Erweiterung Bibliotheksverwaltung
Öffne die Zeichnungen 02_Aufg3_Bibliothek.graphml. Dort findest du eine Vorlage
des oben abgebildeten Klassendiagramms.
(a) Ergänze die fehlenden Einträge.
(b) Füge die Klasse VERLAG mit folgenden Attributen hinzu:
Name, Strasse, PLZ, Ort, Telefon, Fax, email, website.
Lege selbst sinnvolle Datentypen fest.
13 / 137
Datenbanken
1
Grundlagen
2
Klassendiagramme und Datentypen
Übung
• Ü 1.4: Klassenkarte Einkauf
Erstelle zu der Tabelle Einkauf.ods eine Klassenkarte. Lege selbst sinnvolle
Datentypen fest (Vorlage 2_Aufg4_Einkauf.graphml)
Wo finden sich der Klassenname und die Attribute des Klassendiagramms in der
Tabelle wieder?
14 / 137
Datenbanken
1
Grundlagen
2
Klassendiagramme und Datentypen
Übung
• Ü 1.5: Verbesserung Einkauf
Die Klasse Einkauf soll in vier Klassen Kauf, Person, Artikel und Geschäft
aufgeteilt werden.
(a) Öffne die Vorlage 02_Aufg5_Einkauf.graphml und erstelle die fehlenden
Klassenkarten. Ordne dabei die Attribute Name (des Kunden), Geschlecht,
Sparte, Warenbezeichnung, Preis, Zahlungsart, Name (für des Geschäftes)
und Kaufdatum der richtigen Klasse zu.
(b) Lege sinnvolle Relationen zwischen den Klassen fest.
(c) Warum kann es sinnvoll sein, die eine Tabelle Einkauf in diese vier Tabellen
aufzuteilen?
15 / 137
In diesem Abschnitt
Grundlagen eines Datenbankmanagementsystems (DBMS)
Speichern großer Datenmengen
Klassendiagramme und Datentypen
Aufbau von Datentabellen
Datenbanken
1
Grundlagen
3
Aufbau von Datentabellen
Aufbau von Datentabellen
Beispiel: Tabelle city einer Datenbank world
Die Objekte einer Tabelle sind Datensätze. Ihre Attribute stehen
in den Spaltenbezeichnungen. Die Attributwerte der einzelnen
Attribute finden sich in den zugehörigen Spalten.
17 / 137
Datenbanken
1
Grundlagen
3
Aufbau von Datentabellen
Definition Tabellenschema und Schlüssel
Definition Tabellenschema und Schlüssel
Jeder Datensatz muss eindeutig identifizierbar sein.
Spalten(-kombination) die jeden Datensatz eindeutig festlegt,
nennt man Schlüssel.
Wenn sich keine Spaltenkombination als Schlüssel eignet,
verwendet man künstliche Schlüssel. Bekannte künstliche
Schlüssel sind die EAN (Strichcode) von Waren oder die
Personalausweisnummer.
Im Tabellenschema werden der Tabellenname, und die Attribute
samt Datentypen aufgeführt und der Schlüssel unterstrichen.
Tabellenschema für obige Tabelle:
city(ID: INT; Name: VARCHAR[40], ContryCode:
CHAR[3],
Destrict: VARCHAR[40], Population:
INT)
18 / 137
Datenbanken
1
Grundlagen
3
Aufbau von Datentabellen
Übung
• Ü 1.6: TERRA-Datenbank erforschen
https://www.sachsen.schule/~terra2014/index.php
Die Datenbank TERRA enthält geographische Informationen über der Erde. Besuche die
Seite
• https://www.sachsen.schule/ terra2014/index.php
https://www.sachsen.schule/~terra2014/index.php
(a) Surf dich durch die Seite und verschaffe dir so einen Überblick.
(b) Wie lautet das Tabellenschema der Tabelle LAND? Welchen Schlüssel, wie viele
Atribute und wie viele Spalten hat diese Tabelle?
(c) Wie viele Tabellen beinhaltet die Datenbank? Wie viele haben nur eine Spalte als
Schlüssel?
(d) Warum reicht es in der Tabelle STADT nicht, nur den Namen der Stadt als Schlüssel
zu verwenden?
(e) Stelle eine Vermutung auf, welche Schlüssel künstlich sind.
(f) Erstelle mit yEd je eine Klassenkarte der Tabellen LAND und STADT und lege eine
sinnvolle Relation fest.
19 / 137
Datenbanken
1
Grundlagen
3
Aufbau von Datentabellen
Übung
• Ü 1.7: Datenbank Bundesliga erforschen
http://dbup2date.uni-bayreuth.de
Die Datenbank Bundesliga enthält Informationen über die Fußball Bundesliga.
Besuche die Seite
• http://dbup2date.uni-bayreuth.de/
http://dbup2date.uni-bayreuth.de/
(a) Surf dich durch die Seite und verschaffe dir so einen Überblick.
(b) Wie viele Tabellen beinhaltet die Datenbank? Wie viele haben nur eine Spalte als
Schlüssel?
(c) Stelle eine Vermutung auf, welche Schlüssel künstlich sind.
(d) Erstelle die Klassenkarten zu den angegebenen Tabellenschamata und lege
sinnvolle Relationen fest.
20 / 137
Datenbanken
1
Grundlagen
3
Aufbau von Datentabellen
Übung
• Ü 1.8: Datenbank Wetter in Deutschland erforschen
http://dbup2date.uni-bayreuth.de
Die Datenbank Wetter enthält Informationen über das Wetter in Deutschland. Besuche
die Seite
• http://dbup2date.uni-bayreuth.de/
http://dbup2date.uni-bayreuth.de/
(a) Surf dich durch die Seite und verschaffe dir so einen Überblick.
(b) Wie viele Tabellen beinhaltet die Datenbank? Wie viele haben nur eine Spalte als
Schlüssel?
(c) Stelle eine Vermutung auf, welche Schlüssel künstlich sind.
(d) Erstelle die Klassenkarten zu den angegebenen Tabellenschamata und lege
sinnvolle Relationen fest.
21 / 137
In diesem Abschnitt
Grundlegende Datenbankabfragen ohne verknüpfte
Selektionen
Seletion und Projektion
Beispiele aus der TERRA-Datenbank
Datenbanken
2
Grundlegende Datenbankabfragen
1
Seletion und Projektion
Das DBMS MySQL
Das DBMS MySQL
Das bekanntestes Open Source DBMS ist MySQL. Es
verwendet die Datenbanksprache SQL (Structured Query
Language), mit der man aus einer Datenbankbasis
Informationen mit bestimmten Eigenschaften herausfiltern und
anzeigen kann.
23 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
1
Seletion und Projektion
Selektion und Projektion
Selektion und Projektion
Die . . .
Projektion filtert Spalten (Attribute)
Selektion filtert Zeilen mit bestimmten Eigenschaften
aus der Tabelle.
Selektion und Projektion werden normalerweise miteinander
kombiniert
24 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
1
Seletion und Projektion
Bespiel: Selektion und Projektion
25 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
1
Seletion und Projektion
SQL-Syntax einer Tabellenanfrage
Bisher
SELECT( Spalten ; Tabellen ; Bedingung)
Jetzt
SELECT S p a l t e n
FROM T a b e l l e n
WHERE Bedingung
26 / 137
In diesem Abschnitt
Grundlegende Datenbankabfragen ohne verknüpfte
Selektionen
Seletion und Projektion
Beispiele aus der TERRA-Datenbank
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
Beispiele aus der TERRA-Datenbank
Alle nachfolgenden Beispiele sind aus der
• TERRA-Datenbank
https://www.sachsen.schule/~terra2014/index.php
entnommen.
Dort sind auch die Tabellenschemata zu finden. Wir verwenden zunächst nur die
Tabelle
BERG (B_NAME, GEBIRGE, HOEHE, JAHR, LAENGE, BREITE)
28 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
2
Beispiele
Beispiel 1
Es soll die gesamte Tabelle Berg ausgegeben werden.
SELECT *
FROM BERG
I
Der Asterix „*“ ist eine Abkürzung,
wenn alle Spalten selektiert werden
sollen.
I
WHERE kann entfallen, wenn keine
speziellen Zeilen ausgewählt werden
sollen.
• TERRA
https://www.sachsen.schule/~terra2014/index.php
29 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
Beispiel 2
Aus der Tabelle Berg sollen nur die Spalten B_NAME und HOEHE
selektiert werden.
SELECT B_NAME, HOEHE
FROM
BERG
• TERRA
https://www.sachsen.schule/~terra2014/index.php
30 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
2
Beispiele
Beispiel 3
Aus der Tabelle Berg sollen alle Berge (Name und Höhe) des
Himalaya ausgegeben werden.
SELECT B_NAME,
HOEHE
FROM
BERG
WHERE GEBIRGE = "
Himalaya "
I
Name und Höhe der Berge
sind Projektionen (Spalten)
I
Himalaya ist eine Selektion
zur Auswahl der Zeilen, die
als Gebirge den Eintrag
“Himalaya“ haben
I
Datentypen die
Zeichenfolgen enthalten
(Strings) müssen in doppelte
Hochkommata gesetzt
werden.
• TERRA
https://www.sachsen.schule/~terra2014/index.php
31 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
2
Beispiele
Vergleichsoperatoren für die Selektion WHERE, 1. Teil
Operatoren für die Selektion
können sein:
<, >, <>, =, >=, <= wie aus der Tabellenkalkulation
bekannt.
between ... and ... filtert Zeilen deren Merkmal zwischen
den angegebenen Werten liegt.
Beides kann auf alle Datentypen angewandt werden. Bei
Zeichenketten wird die alphabetische Ordnung zugrunde
gelegt.
32 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
2
Beispiele
Beispiel 4
Gesucht sind alle Berge, die mindestens 7000 Meter hoch sind.
SELECT *
FROM
BERG
WHERE HOEHE >= 7000
33 / 137
Datenbanken
2
Grundlegende Datenbankabfragen
2
Beispiele
Beispiel 5
Gesucht sind alle Berge, die zwischen 1900 und 1950 erstmals
bestiegen wurden.
SELECT *
FROM
BERG
WHERE j a h r between 1900 and 1950
34 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
Beispiel 6
Gesucht sind alle Berge, mit einem „M“ oder „N“ beginnen.
SELECT *
FROM
BERG
WHERE B_NAME between "M" and "O"
35 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
Bisherige SQL-Syntax einer Tabellenanfrage
Für bedingung gilt folgende Syntax:
36 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
Übung
• Ü 2.1: TERRA-Datenbank
https://www.sachsen.schule/~terra2014/index.php
Bearbeite die Aufgaben 1-4b der Terra-Datenbank
37 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
• Ü 2.2: Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
Gib die SQL-Querries zu folgenden Anfragen an die Bundesliga-Datenbank an.
(a) Liste alle Verein der Datenbank auf.
(b) Liste alle Vereine der zweiten Liga auf.
(c) Liste alle Spieler auf, die weniger als 3 Tore geschossen haben
(d) Liste alle brasilianischen Spieler auf
(e) Liste alle ausländischen Spieler auf
(f) Gesucht sind alle Spiele, bei der die Heimmannschaft kein Tore geschossen hat.
(g) Gesucht sind alle Vereine, die nicht in der ersten Liga spielen
(h) Gesucht sind alle Spiele des ersten Spieltags
(i) Gesucht sind alle Spiele, die um 18.30 Uhr begonnen haben.
38 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
• Ü 2.3: Wetter-Datenbank
http://dbup2date.uni-bayreuth.de/
Gib die SQL-Querries zu folgenden Anfragen an die Wetter-Datenbank an.
(a) Liste alle Wetterstation der Datenbank auf.
(b) Liste alle Wettermessungen (nur Stations_ID und Datum) auf, die keinen
Sonnenschein gemessen haben.
(c) Liste alle Wettermessungen auf (Stations_ID, Datum, Niederschlagshoehe), die
zwischen 10 und 50 mm Niederschlag gemessen haben.
(d) Liste Wetterstationen auf, die nicht vom DWD betrieben werden.
(e) Welche Wetterstationen liegen weiter über dem Meerespiegel als Pegnitz?
39 / 137
Datenbanken
Grundlegende Datenbankabfragen
2
2
Beispiele
Übung
• Ü 2.4: Eigene DB-Abfrage formulieren
Formuliere jweils ein Fragestellungen und den dazugehörigen SQL-Befehl mit der
angegebenen Bediungung an eine Datenbank deiner Wahl.
(a) Es sollen alle Zeilen ausgegeben werden aber nicht alle Spalten
(b) Es soll nur eine Projektion aber keine Selektion stattfinden
(c) Es sollen alle Spalten ausgegeben werden aber nicht alle Zeilen
(d) Es soll nur eine Selektion aber keine Projektion stattfinden
(e) Es soll eine Selektion und eine Projektion stattfinden
• cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html
• TERRA-Datenbank
https://www.sachsen.schule/~terra2014/index.php
• Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
• Wetter-Datenbank
http://dbup2date.uni-bayreuth.de/
40 / 137
In diesem Abschnitt
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
1
Bedingungen verbinden
Beispiel 1
Gesucht sind alle Berge des Himalaya, die mindestens 7000
Meter hoch sind.
SELECT *
FROM
BERG
WHERE HOEHE >= 7000
AND Gebirge = ’Himalaya ’
42 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
1
Bedingungen verbinden
Beispiel 2
Gesucht sind alle Berge des Himalaya und der Anden.
SELECT *
FROM
BERG
WHERE Gebirge = ’Anden ’
OR Gebirge = ’Himalaya ’
43 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
1
Bedingungen verbinden
Beispiel 3
Gesucht sind alle Berge über 8000 m außerhalb des Himalaya.
SELECT *
FROM
BERG
WHERE HOEHE >=8000
AND NOT( Gebirge = ’Himalaya ’ )
44 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
1
Bedingungen verbinden
Logische Operatoren für die Selektion WHERE
Die logischen Operatoren für die Selektion
sind NOT, AND, OR wie aus der Tabellenkalkulation bekannt.
Beachte:
AND ist genau dann wahr ist, wenn beide Ausdrücke
wahr sind,
OR ist genau dann wahr ist, wenn mindestens ein
Ausdruck wahr ist und
NOT ist genau dann wahr ist, wenn der Ausdruck falsch
ist.
45 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
1
Bedingungen verbinden
Erweiterte Syntaxdiagramme der WHERE-Clause
Für bedingung ist das Syntaxdiagramm noch unverändert:
46 / 137
In diesem Abschnitt
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
2
Zeichenfolgen suchen
Zeichenfolgen Selektieren
Mit dem like-Operator
kann man in Zeichenfolgen nach Mustern suchen.
% steht für eine beliebige Zeichenfolge (auch für eine
der Länge Null) und
_ steht für ein einzelnes beliebiges Zeichenfolge.
48 / 137
Datenbanken
Erweiterte Abfragen an eine Tabelle
2
3
Zeichenfolgen suchen
Erweitertes Syntaxdiagramm der Bedingung
Unverändertes Syntaxdiagramm der WHERE-Clause
Erweitertes Syntaxdiagramm der bedingung
49 / 137
Datenbanken
Erweiterte Abfragen an eine Tabelle
2
3
Zeichenfolgen suchen
Beispiel 1
Gesucht sind alle Berge der Alpen.
SELECT *
FROM BERG
WHERE GEBIRGE l i k e ’% Alpen%’
50 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
2
Zeichenfolgen suchen
Beispiel 2
Gesucht sind alle Berge der Alpen, die eine ‘r‘ am zweiter Stelle
haben.
SELECT *
FROM BERG
WHERE GEBIRGE l i k e ’% Alpen%’
AND B_NAME l i k e ’_r%’
51 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
2
Zeichenfolgen suchen
Übung
• Ü 3.1: TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
(a) Bearbeite die Aufgaben 4c, d, e, 5, 8, 9, 11 der Terra-Datenbank
(b) Gehe zum letzten Abschnitt des Skriptes (Syntaxdiagramme) und gib den Weg
deiner Abfragen durch die Diagramme an.
52 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
2
Zeichenfolgen suchen
• Ü 3.2: cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html
Gib die SQL-Querries und Syntaxwege zu folgenden Anfragen an die cia-Datenbank
an.
(a) Gib die Namen aller asiatischen Länder aus, die weniger als 10 Millionen
Einwohner haben. (5)
(b) Gib die Namen aller Länder aus, die kleiner als 500000 Quadratkilometer sind
oder deren BIP unter 100 Mio Dollar liegt. (12)
(c) Gib die Namen aller nicht-afrikanischen Länder aus, deren BIP unter 100 Mio
Dollar liegt. (10)
(d) Gib die Namen aller europäischen Länder aus, die weniger als 10 Millionen
Einwohner haben. (30 − 40)
(e) Gib die Namen aller amerikanischen Länder aus, die weniger als 10 Millionen
Einwohner haben. (35 − 45)
53 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
2
Zeichenfolgen suchen
Übung
• Ü 3.3: Eigene DB-Abfrage formulieren
Formuliere, passend zu diesem Abschnitt, vier eigene Anfragen mit steigendem
Schwierigkeitsgrad an eine unserer Datenbanken.
• cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html
• TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
• Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
• Wetter-Datenbank
http://dbup2date.uni-bayreuth.de/
54 / 137
In diesem Abschnitt
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Ausgaben sortieren
Mit der ORDER BY-Clause
kann man Ausgaben sortieren. Das Schlüsselwort
ASC sortiert aufsteigend (Voreinstellung)
DESC sortiert absteigend.
56 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Ausgaben begrenzen
Mit der LIMIT-Clause
kann man bei langen Tabellen die Anzahl der Ergebnisse
begrenzen.
LIMIT n liefert die ersten n Datensätze,
LIMIT n, m lässt die ersten n Datensätze weg und zeigt die
nächsten m.
57 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Beispiel 1 (TERRA)
Gesucht sind alle Länder, sortiert nach der Fläche mit dem
kleinsten beginnend.
SELECT *
FROM LAND
ORDER BY FLAECHE
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
58 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Beispiel 2 (TERRA)
Gesucht sind alle Länder, sortiert nach der Fläche mit dem
größten beginnend.
SELECT *
FROM LAND
ORDER BY FLAECHE DESC
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
59 / 137
Datenbanken
Erweiterte Abfragen an eine Tabelle
3
3
Ausgaben Sortieren und begrenzen
Beispiel 3 (TERRA)
Gesucht sind die drei flächengrößten Länder.
SELECT *
FROM LAND
ORDER BY FLAECHE DESC
LIMIT 3
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
60 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Beispiel 4 (TERRA)
Gesucht sind die Länder mit der viert- bis zehntgrößten Fläche
SELECT *
FROM LAND
ORDER BY FLAECHE DESC
LIMIT 3 , 7
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
61 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Beispiel 5 (TERRA)
Gesucht sind die Berge sortiert nach dem Gebirge und dann
nach der Berghöhe.
SELECT *
FROM BERG
ORDER BY GEBIRGE ASC,HOEHE DESC
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
62 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Syntaxdiagramm: order by-Term (vollständig)
63 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Syntaxdiagramm: limit-Term (vollständig)
64 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Übung
• Ü 3.4: Wetter-Datenbank
http://dbup2date.uni-bayreuth.de/
Gib die SQL-Querries und Syntaxwege an.
(a) Gib die Wetterstationen alphabetisch sortiert aus.
(b) Gib Standort und geographische Breite der Wetterstation von Nord nach Süd
sortiert an.
(c) Gib die zehn höchstgelegenen Wetterstationen an.
(d) An welchen Tagen hat es in München (Stations_ID = 10870) nicht geregnet?
Sortiere nach dem Datum aufsteigend.
(e) Gib alle Wettermessungen an, die weder Regen noch Sonnenschein und
maximale Windgeschwindigkeit kleiner 10 gemessen haben. Die Sortierung soll
nach der Wetterstation erfolgen und innerhalb dieser Sortierung soll nach der max.
Windgeschwindigkeit sortiert werden.
(f) Gib die 20 windstärksten Wettermessungen aus.
65 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
3
Ausgaben Sortieren und begrenzen
Übung
• Ü 3.5: Eigene DB-Abfrage formulieren
Formuliere, passend zu diesem Abschnitt, vier eigene Anfragen mit steigendem
Schwierigkeitsgrad an eine der unten angegebenen Datenbanken.
• cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html
• TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
• Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
66 / 137
In diesem Abschnitt
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Mehrfachnennungen vermeiden
Gesucht sind alle Gebirge die in der Tabelle Berg enthalten
sind.
SELECT GEBIRGE
FROM BERG
Erzeugt Mehrfachnennungen.
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
68 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Mehrfachnennungen vermeiden
Das Schlüsselwort DISTINCT
nach dem Schlüsselwort SELECT vermeidet
Mehrfachnennungen
69 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Ausdrücke in Spalten
Gib die Berge der TERRA-Datenbank mit der Angabe der
vollständigen Tausender an.
SELECT BERG, HOEHE, round (HOEHE / 1000)
FROM BERG
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
70 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Ausdrücke in Spalten
In Spalten können Rechenausdrücke mit den üblichen
Rechenzeichen angegeben werden.
71 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Umbenennen von Spalten
Gib der Spalte der vollständigen Tausender den Name
„Tausender“
SELECT BERG, HOEHE, round (HOEHE / 1000) AS ’
Tausender ’
FROM BERG
• TERRA
http://www.sn.schule.de/~reimegym/terra/a00.html
72 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Umbenennen von Spalten
Mit dem Schlüsselwort AS lassen sich Spalten umbenennen.
73 / 137
Datenbanken
Erweiterte Abfragen an eine Tabelle
4
3
Vollständiger select-Term
Übung
• Ü 3.6: cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html
Gib die SQL-Querries an.
(a) Welche Regionen sind die der Datenbank verzeichnet?
(b) Gib den Namen die Fläche, die Einwohnerzahl und die
Einwohner pro Fläche in Tausenden an. Diese letztes
Spalte soll die Überschrift Einw. pro qkm in Tsd
bekommen. Sortiere nach dieser letzten Spalte.
(c) Gib den Namen das BIP, die Einwohnerzahl und das BIP
pro Einwohner an. Diese letztes Spalte soll die Überschrift
BIP pro Einw bekommen. Sortiere nach dieser letzten
Spalte. Es sollen nur die ersten 20 Datensätze ausgegeben
werden.
(d) Gib für alle europäischen Länder den Namen, die Region
und das BIP pro Quadratkilometer an. Sortiere
74 / 137
alphabetisch nach der Region und innerhalb einer Region
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Übung
• Ü 3.7: Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
Gib die SQL-Querries und Syntaxwege an.
(a) Welche Nationalitäten gibt es unter den Spielern der
Bundesliga? Die Ausgabe soll alphabetisch sortiert sein
und die Überschrift vertretene Nationen besitzen.
(b) Welche Trikot-Nummern wurden vergeben? Ergebnis
sortiert angeben!
(c) Gib alle Spalten und die Gesamtzahl der Tore von den
Spielen aus, bei denen mehr als vier Tore gefallen sind.
Sortiere nach dem Datum.
(d) Gib die Spiele sortiert aus, die 2013 nach 18.00 Uhr
stattfanden. Bezeichne die Uhrzeitspalte mit Abendspiel.
(e) Die Torgefährlichkeit eines Spielers berechnet man mittels
2· Torzahl + Vorlagenzahl. Gib die Spieler„ die nach dieser 75 / 137
Formel am torgefährlichsten sind, und ihre Torgefährlichkeit
sortiert aus.
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Übung
• Ü 3.8: Eigene DB-Abfrage formulieren
Formuliere, passend zu diesem Abschnitt, zwei eigene Anfragen an eine der unten
angegebenen Datenbanken.
• cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html
• TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
• Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
76 / 137
Datenbanken
3
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Übung
• Ü 3.9: TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
Formuliere, zu den angegebenen Syntaxwegeg je eine Anfrage
an die TERRA-Datenbank
(a) s 2−6−8−4−2
f/f
w 2
b 1 −4/b
/w
o 1 /o
/s
(b) s 2−7−8−4−2
f/f
o 1−4 / o
l 1
/l
/s
In diesem Abschnitt
Gruppieren und Zusammenfassen von Datensätzen
Aggregatfunktionen
Grupierungen
Auswahl von Gruppen
77 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
1
Aggregatfunktionen
• Beispiel 1 (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Wie viele Flüsse sind in der Datenbank
SELECT count ( * ) as ’Anzahl der Flüsse ’
FROM FLUSS
79 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
1
Aggregatfunktionen
Aggregatfunktionen
Aggregatfunktionen
werten Tabellen statistisch aus. Im select-Term sind erlaubt:
COUNT (Ausdruck) Anzahl der Datensätze
SUM (Ausdruck) Summe,
AVG (Ausdruck) Durchschnitt, der Werte von Ausdruck
MIN (Ausdruck) Minimum
MAX (Ausdruck) Maximum aller Werte von Ausdruck
80 / 137
In diesem Abschnitt
Gruppieren und Zusammenfassen von Datensätzen
Aggregatfunktionen
Grupierungen
Auswahl von Gruppen
Datenbanken
Gruppieren und Zusammenfassen von Datensätzen
2
4
Gruppierungen
• Beispiel 1 (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Liste die Anzahl der Zuflüsse in jedes Meer auf
SELECT Meer , count ( * ) as Z u f l ü s s e
FROM FLUSS
Group by Meer
82 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
2
Gruppierungen
• Beispiel 2 (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Liste die Anzahl der Wüsten, gruppiert nach der Wüstenart auf
SELECT WUESTENART, count ( * )
FROM WUESTE
Group by WUESTENART
83 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
2
Gruppierungen
group by-Klausel
group by
gruppiert Datensätze nach bestimmten Eigenschaften. Die
Aggregatfunktionen werden dann auf die Gruppen einzeln
angewendet.
Außer den Aggregatfunktionen dürfen in der select-Klausel
nur Attribute vorkommen, die in der group by-Klausel
aufgeführt sind.
84 / 137
Datenbanken
Gruppieren und Zusammenfassen von Datensätzen
2
4
Gruppierungen
• Beispiel 3 (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Liste die Gesamtfläche der Wüstenarten
SELECT WUESTENART, sum(FLAECHE) AS
Gesamtflaeche
FROM WUESTE
group by WUESTENART
ORDER BY Gesamtflaeche
85 / 137
In diesem Abschnitt
Gruppieren und Zusammenfassen von Datensätzen
Aggregatfunktionen
Grupierungen
Auswahl von Gruppen
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
3
Auswahl von Gruppen
having-Klausel
having-Klausel
Oft werden bei einer Gruppierung nur die Gruppen gewünscht,
die bestimmte Bedingungen erfüllen. Diese Bedingungen
werden in der having-Kausel formuliert. Dabei dürfen
Gruppierungsausdrücke verwendet werden. Die Anfragen
werden in folgender Reihenfolge ausgewertet:
1. Auswahl der Zeilen durch die WHERE-Klausel,
2. Bildung der Gruppen durch die GROUP BY-Klausel,
3. Auswahl der Gruppen, die die HEAVING-Klausel erfüllen.
87 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
3
Auswahl von Gruppen
• Beispiel 1 (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Liste die Anzahl der Nachbarländer für jedes Land auf, das
mehr als 5 Nachbarn hat.
s e l e c t LAND1, count ( * ) AnzahlNachbarlaender
from IST_BENACHBART_ZU
group by LAND1
having count ( * ) > 5
order by anzahlNachbarlaender desc
88 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
3
Auswahl von Gruppen
• Ü 4.1: cia-Datenbank (analog Übg 2 cia)
http://www.imoodle.de/sqltutorial/index.html
Gib die SQL-Querries an.
(a) Wie viele Länder enthält die cia-Datenbank?
(b) Ermittle die Weltbevölkerung.
(c) Gib das Durchschnitts-Bruttoinlandsprodukt an.
(d) Wie groß sind Bevölkerung und Bruttoinlandsprodukt für
ganz Europa?
(e) Ermittle die Flächen des kleinsten und größten Landes.
89 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
3
Auswahl von Gruppen
• Ü 4.2: cia-Datenbank (analog Übg 4 Aufg 1-5 cia)
http://www.imoodle.de/sqltutorial/index.html
Gib die SQL-Querries an.
(a) Zeige von jeder Region den Namen und die Anzahl der
Länder an.
(b) Zeige für alle Regionen den Namen und die Anzahl der
Länder mit mehr als 10 Millionen Einwohnern an.
(c) Welche Regionen haben eine Gesamtbevölkerung von
mindestens 100 Millionen?
(d) Stelle die Regionen der Erde mit Einwohnerzahl und
Gesamtfläche dar, geordnet nach der Einwohnerzahl.
(e) Wie vorhergehende Aufgabe aber nur die Regionen von
Amerika.
90 / 137
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
3
Auswahl von Gruppen
• Ü 4.3: WM-Titel-Datenbank (analog Übg 4 Aufg 6-10 WMTitel)
http://www.imoodle.de/sqltutorial/index.html
Gib die SQL-Querries an.
(a) Ermittle die von jedem Weltmeister erreichte Gesamtzahl
von WM-Punkten und stelle das Ergebnis nach
WM-Punkten geordnet dar.
(b) Welche Gesamtpunktzahlen der
Konstrukteursweltmeisterschaft haben die Teams in den
neunziger Jahren erreicht?
(c) Wie vorherige Aufgabe, allerdings sollen nur Teams mit
mindestens 100 Punkten ausgegeben werden.
(d) Ermittle für die Jahre, in denen Michael Schumacher
gefahren ist, die durchschnittliche Zahl der erreichten
Team-Punkte des Konstrukteursweltmeisters und stelle die
Liste geordnet dar.
91 / 137
(e) Ermittle ab 1995 in geordneter Reihenfolge die Gesamtzahl
der Team-Punkte für Teams mit mindestens 200 Punkten.
Datenbanken
4
Gruppieren und Zusammenfassen von Datensätzen
3
Auswahl von Gruppen
• Ü 4.4: TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
Gib die SQL-Querries an.
(a) Gesucht sind alle Flüsse (Name), die in die Ostsee oder
Nordsee münden und über 1000 km lang sind. [4]
(b) Gesucht sind alle Wüsten (Name) der Art ’Sandwueste’ mit
einer Fläche größer als 25000 qkm. [9]
(c) Gib für alle Millionenstädte, die in den Tropen liegen, die
Namen und ihre Koordinaten an. (Die Tropen liegen
zwischen 23.27 Grad nördlicher und 23.27 Grad südlicher
Breite.) [65]
(d) Gib die Ländernamen und die Zahl der Einwohner für alle
Länder an, die mehr als 45 Millionen Einwohner haben.
Ordnen Sie nach der Einwohnerzahl! [25]
(e) Gesucht ist der Anteil der Meere an der Erdoberfläche
(Angabe in Prozent). Hinweise: Oberflächenformel:
92 / 137
Datenbanken
Gruppieren und Zusammenfassen von Datensätzen
3
4
Auswahl von Gruppen
• Ü 4.5: TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
Gib die SQL-Querries an.
(a) Gesucht ist für alle Inselgruppen deren Gesamtfläche. [41]
(b) Gesucht ist von jeder Inselgruppe die Fläche der größten
Insel. [41]
(c) Gesucht sind alle Inselgruppen sowie die Anzahl der
zugehörigen Inseln für alle Inselgruppen mit mehr als einer
Insel. [21]
(d) Gesucht sind alle Flüsse mit mehr als zwei an ihnen
liegenden Städten, sortiert nach dieser Anzahl. [21]
(e) Gesucht sind alle Städte durch die mehr als einem Fluss
fließt, sortiert nach dieser Anzahl. [8]
(f) Gesucht sind alle Städte, in denen mehrere Organisationen
ihren Sitz haben. [10]
93 / 137
(g) Welche Länder haben mehr als 4 Millionenstädte? [7]
In diesem Abschnitt
Verknüpfung von Tabellen
Basisabfragen
Schema für Join-Abfragen
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
Bespiel
Gesucht ist der Name des Klassenleiters der 5. Klasse.
95 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
Joins
Joins
Oft werden bei einer SQL-Anfrage Daten gewünscht, die aus
verschiedenen Tabellen stammen. Dazu müssen die Tabellen
verknüpft werden. Solche Verknüpfungen nennt man Joins.
I
Die Verknüpfung der Tabellen erfolgt durch Angabe der
beteiligten Tabellen in der From- Klausel.
I
Bei gleichen Spaltennamen aus verschiedenen Tabellen
muss in der Abfragen zusätzlich der Tabellenname
angegeben werden. Man schreibt:
Tabellenname.Attributname.
96 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
Beispiel 1
Die Spalte Name ist zweideutig (Lehrernamen, Klassenname).
Es muss der Klassenname mit Klasse.Name und der Name der
Lehrkraft mit Lehrkraft.Name angesprochen werden.
97 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
Select Anfrage
Gewünscht ist eine Liste der Klassenleiter mit den jeweiligen
Klassen.
SELECT Klasse . Name, L e h r k r a f t . Name
FROM L e h r k r a f t , Klasse
WHERE PersNr = K l a s s e n l e i t u n g ;
98 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
• Beispiel 1 (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Gewünscht ist eine Liste welches Land zu welchem Kontinent
gehört.
SELECT L_Name , K_Name
FROM LAND, UMFASST
WHERE LAND . L_ID = UMFASST. L_ID
99 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
Joins
Joins
Lässt man die WHERE-Klausel bei Joins mit zwei Tabellen weg,
werden in der Ergebnistabelle alle Zeilen der ersten Tabelle mit
allen Zeilen der zweiten Tabelle kombiniert. Wir erhalten also
eine Tabelle deren Zeilenzahl so groß ist, wie das Produkt der
Zeilenzahlen der Ausgangstabelle.
I
Die Verknüpfung der Tabellen erfolgt durch Angabe der
beteiligten Tabellen in der From- Klausel.
I
Bei gleichen Spaltennamen aus verschiedenen Tabellen
muss in der Abfragen zusätzlich der Tabellenname
angegeben werden. Man schreibt:
Tabellenname.Attributname.
100 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
Bespiel
SELECT Klasse . Name, L e h r k r a f t . Name
FROM L e h r k r a f t , Klasse
liefert also 7 ∗ 2 = 14 Zeilen und 5 + 3 Spalten. Viele davon sind
unsinnig.
101 / 137
Datenbanken
Verknüpfung von Tabellen
1
5
Basisabfragen
Bespiel
102 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
• Beispiel (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
SELECT L_Name , K_Name
FROM LAND, UMFASST
Es werden alle Länder(190) mit allen UMFASST-Zeilen (188)
kombiniert es entstehen 35720 Zeilen.
103 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
• Ü 5.1: TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
Gib die SQL-Querries an.
(a) Gesucht ist eine Aufstellung der Länder mit ihren
Landesteilen. [551]
(b) Gesucht ist eine Aufstellung aller deutschen Städte. [114]
(c) Gesucht ist eine Aufstellung aller Organisationen mit ihrem
Hauptsitz. [55]
(d) Gesucht ist eine Aufstellung aller Flüsse, mit ihren Ländern,
durch die sie fließen. [185]
104 / 137
Datenbanken
5
Verknüpfung von Tabellen
1
Basisabfragen
• Ü 5.2: Bundesliga-Datenbank
http://dbup2date.uni-bayreuth.de/
Gib die SQL-Querries an.
(a) Gesucht ist die Vereinskader (Vereinsname, Spielername).
(b) Gesucht ist die Größe der Vereinskader
(c) Was liefert folgende Abfrage? Warum ist eine Abfrage über
3 Tabellen nötig?
Select S p i e l t a g , h . Name as Heimmannschaft ,
g . Name as Gastmannschaft , Tore_Heim ,
Tore_Gast
from V e r e i n as h , S p i e l , V e r e i n as g
where g . V_ID = Gast and h . V_ID = Heim
and h . L i g a = 1 and Tore_Heim =
Tore_Gast
Order By S p i e l t a g
(d) Es soll für jeden Spieltag die Anzahl der Unentschieden in
der ersten Liga angegeben werden.
In diesem Abschnitt
Verknüpfung von Tabellen
Basisabfragen
Schema für Join-Abfragen
105 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
Gewünscht ist eine Liste der Inseln samt Fläche, die größer als
50000 Quadratkilometer sind und die dazugehörigen
Landesteile und Länder.
107 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
1. Schritt: Welche Tabellen beinhalten die gewünschten
Informationen?
I
LAND (L_NAME, L_ID, EINWOHNER, FLAECHE,
HAUPTSTADT, LT_ID)
I
LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE,
HAUPTSTADT)
I
INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE,
BREITE)
I
GEO_INSEL(LT_ID, L_ID, I_NAME)
108 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
2. Schritt: Welche Spalten beinhalten die gewünschten
Informationen?
I
LAND (L_NAME, L_ID, EINWOHNER, FLAECHE,
HAUPTSTADT, LT_ID)
I
LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE,
HAUPTSTADT)
I
INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE,
BREITE)
I
GEO_INSEL(LT_ID, L_ID, I_NAME)
109 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
3. Schritt: Eine der Tabellen komplett ausgeben lassen
SELECT *
FROM LAND
110 / 137
Datenbanken
5
Verknüpfung von Tabellen
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
4. Schritt: Durch welche Spalte ist die Tabelle mit einer anderen
verbunden?
I
LAND (L_NAME, L_ID , EINWOHNER, FLAECHE,
HAUPTSTADT, LT_ID)
I
LANDTEIL (LT_NAME, LT_ID, L_ID , EINWOHNER, LAGE,
HAUPTSTADT)
111 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
5. Schritt: Tabellen verjoinen, d.h.
I
zweite Tabelle in die from-Klausel
I
Verknüpfungsspalten in die where-Klausel
SELECT *
FROM LAND as l , LANDTEIL as l t
WHERE l . L_ID = l t . L_ID
112 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
6. Schritt: Spalten einschränken
SELECT L_Name , LT_NAME
FROM LAND as l , LANDTEIL as l t
WHERE l . L_ID = l t . L_ID
113 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
7. Schritt: Nach dem gleichen Schema die nächste Tabelle
dazunehmen
I nächste Tabelle in die from-Klausel
I Verknüpfungsspalten in die where-Klausel
I Spalten, die die Richtigkeit der Ausgabe anzeigen können
in die select-Klausel
SELECT L_Name , LT_NAME, g . LT_ID , g . L_ID ,
I_NAME
FROM LAND as l , LANDTEIL as l t , GEO_INSEL as
g
WHERE
l . L_ID = l t . L_ID
114 / 137
and
l . L_ID = g . L_ID
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
8. Schritt: Letzte Tabelle dazunehmen
I
LAND (L_NAME, L_ID, EINWOHNER, FLAECHE,
HAUPTSTADT, LT_ID)
I
LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE,
HAUPTSTADT)
I
INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE,
BREITE)
I
GEO_INSEL(LT_ID, L_ID, I_NAME)
115 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
8. Schritt: Letzte Tabelle dazunehmen
SELECT L_Name , LT_NAME, g . LT_ID , g . L_ID , g .
I_NAME , i . FLAECHE
FROM LAND as l , LANDTEIL as l t , GEO_INSEL as
g , INSEL as i
WHERE
l . L_ID
= l t . L_ID
and
l . L_ID
= g . L_ID
and l t . LT_ID = g . LT_ID
and
g . I_NAME = i . I_NAME
116 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Schema für Joins (TERRA)
http://www.sn.schule.de/~reimegym/terra/a00.html
9. Schritt: Nur die benötigten Spalten und Zeilen ausgeben
SELECT L_Name , LT_NAME, g . I_NAME , i . FLAECHE
FROM LAND as l , LANDTEIL as l t , GEO_INSEL as
g , INSEL as i
WHERE
l . L_ID
= l t . L_ID
and
l . L_ID
= g . L_ID
and l t . LT_ID = g . LT_ID
and
g . I_NAME = i . I_NAME
and
i . FLACHE > 50000
117 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Ü 5.3: TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
Gib die SQL-Querries an. Achte auf eine möglichst kurze
Anfrage ohne unnötige joins.
(a) Gesucht ist eine Aufstellung aller Länder mit ihren
Nachbarländern.
(b) Gesucht ist eine Aufstellung aller Länder und der Anzahl
ihrer Nachbarländer sortiert nach dieser Anzahl.
(c) Gesucht ist eine Aufstellung aller Länder und ihrer
Nachbarländer, die größer sind als das Land selbst.
(d) Gesucht ist eine Aufstellung aller Länder und der Anzahl
der Nachbarländer, die größer sind als das Land selbst.
118 / 137
Datenbanken
Verknüpfung von Tabellen
5
Schema für Join-Abfragen
• Ü 5.4: Eigene Abfragen formulieren
Gib die SQL-Querries und Syntaxdiagramme von selbst
gewählten Abfragen an, die sinnvoll sind und
(a) Einen Join über zwei Tabellen enthält.
(b) Einen Join über drei Tabellen enthält.
(c) Einen Join über drei Tabellen enthält und alle Pfade eines
SQL-Querrys enthält.
119 / 137
Datenbanken
Syntaxdiagramme
6
SQL-Querry
120 / 137
Datenbanken
Syntaxdiagramme
6
select- und From-Term
121 / 137
Datenbanken
Syntaxdiagramme
6
Bedingungen in where- und having-Clause
122 / 137
Datenbanken
Syntaxdiagramme
6
order by-, limit- und group by-
123 / 137
In diesem Abschnitt
Beziehungen zwischen Klassen
Wiederholung Abschnitt 1
Beziehungen und Kardinalitäten
Datenbanken
7
Klassenbeziehungen
1
Wiederholung Abschnitt 1
Beispiel: Bibliotheksverwaltung
125 / 137
Datenbanken
7
Klassenbeziehungen
1
Wiederholung Abschnitt 1
Datentypen in SQL (unserer DB-Sprache)
Datentyp
VARCHAR[n]
CHAR[n]
INT
FLOAT
DOUBLE
DECIMAL[n;d]
DATE
TIME
DATETIME
Beschreibung
Variable Zeichenfolge von max. n Zeichen
Zeichenfolge von genau n Zeichen
(Rest Leerzeichen)
Ganze Zahlen (etwa von -2Mrd. Bis
+2Mrd.)
Kommazahlen mit 7 gültigen Ziffern
Kommazahlen mit 15 gültigen Ziffern
Kommazahl mit d Dezimalstellen und
insgesamt n Ziffern
Datumsangaben
Zeitangaben
Kombination aus Datums- und Zeitangabe
126 / 137
Datenbanken
7
Klassenbeziehungen
1
Wiederholung Abschnitt 1
Definition Tabellenschema und Schlüssel
Definition Tabellenschema und Schlüssel
Jeder Datensatz muss eindeutig identifizierbar sein.
Spalten(-kombination) die jeden Datensatz eindeutig festlegt,
nennt man Schlüssel.
Häufig verwendet man künstliche Schlüssel (z.B.
EAN/Strichcode von Waren, Personalausweisnummer)
Im Tabellenschema werden der Tabellenname, und die Attribute
samt Datentypen aufgeführt und der Schlüssel unterstrichen.
Tabellenschema für obige Tabelle:
city(ID: INT; Name: VARCHAR[40], ContryCode:
CHAR[3],
Destrict: VARCHAR[40], Population:
INT)
127 / 137
Datenbanken
7
Klassenbeziehungen
1
Wiederholung Abschnitt 1
Gleicher Sachverhalt verschiedene Darstellungen
Tabellenschema und Klassenkarte sind nur
zwei verschiedene Darstellungen des
gleichen Sachverhalts.
BUCH(Titel: VARCHAR[99], Fachbereich: VARCHAR[40],
Autor: VARCHAR[40], Verlag: VARCHAR[40],
ISBN: VARCHAR[20], Preis: DECIMAL[4;2],
Seitenzahl: INT, Erscheinungsjahr: DATE,
Exemplaranzahl: INT)
128 / 137
Datenbanken
7
Klassenbeziehungen
1
Wiederholung Abschnitt 1
ER-Diagramme — Noch ’ne andere Darstellung
Zur Verbesserung der Übersicht werden die Datentypen oft
weggelassen. Gruppiert man die Attribute als Blasen um die
Klassenkarte erhält man sogenannte
Entity-Relationship-Darstellung.
BUCH(Titel, Fachbereich, Autor, Verlag, ISBN, Preis,
Seitenzahl, Erscheinungsjahr, Exemplaranzahl)
129 / 137
In diesem Abschnitt
Beziehungen zwischen Klassen
Wiederholung Abschnitt 1
Beziehungen und Kardinalitäten
Datenbanken
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
Beziehungen (Relationen)
Klassen können in bestimmten
Beziehungen zueinander
stehen. Diese Relation wird auf
die Verbindungslinie der
Klassen geschrieben. Durch
die Klassen ist die Beziehung
noch nicht eindeutig festgelegt.
131 / 137
Datenbanken
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
Kardinalitäten
Die Kardinalität gibt an, wie viele Objekte der Nachbarklasse zu
einem Objekt der Heimatklasse gehören.
I
Wenn ich einen Schüler
(Heimatklasse)
herausgreife so kann er
nur in einer Schulklasse
(Nachbarklasse) sein. Also
kommt ein 1 an die
Schulklasse.
I
Wenn ich eine Schulklasse
(Heimatklasse)
herausgreife so können
dazu mehrere Schüler
gehören. Also kommt ein
Buchstabe (z.B. n) an die
132 / 137
Datenbanken
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
Arten der Kardinalität
Art
1
n
0,1
1 .. n
Beschreibung
Genau ein Objekt
Keines, eines oder mehrere Objekte
Kein oder ein Objekt
Ein oder mehrere Objekte
133 / 137
Datenbanken
Klassenbeziehungen
2
7
Beziehungen und Kardinalitäten
Klassendiagramme umfassen die
I
Klassenkarten
mit
Attributlisten
und
Datentypen,
I
Schlüssel und
I
Relationen mit
Kardinalitäten.
134 / 137
Datenbanken
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
ER-Diagramm statt Klassendiagramm
135 / 137
Datenbanken
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
• Ü 7.1: Schulverwaltung
In der Schulverwaltung gibt es folgende
fünf Klassen Schueler, Schulklasse, Lehrer, Raum und
Unterrichtsfach.
elf Relationen Schüler hat (Lehrer, Unterrichtsfach), Lehrer
unterrichtet (Unterrichtsfach, Schulklasse),
ist_Klassenkamerad_von,
ist_Fachbetreuer_von,
ist_Klassensprecher_von,
ist_Klassenleiter_von, hat_Klassenzimmer,
hat_Fachraum, hat_Lieblingsfach.
Erstelle ein übersichtliches(!) und kreuzungsfreies
ER-Diagramm (Vorlage Schulverwaltung.graphml). Ergänze
136 / 137
Datenbanken
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
• Ü 7.2: Relationen
(a) Welche Darstellungsarten werden bei den unten
aufgeführten Datenbanken verwendet? Gib auch an, wenn
in der Darstellung etwas fehlt und formatiere die Tabelle
sinnvoll (Vorlage: Relationen_a.ods).
(b) Verwandle das Klassendiagramm (guckst du gelber Link
bei Bundesliga-Datenbank) in ein ER-Diagramm.
Verwende dazu yEd. (Vorlage: keine)
(c) Erstelle ein Klassendiagramm aus der TERRA-Datenbank,
das die Tabellen LAND, LANDTEIL, KONTINENT und INSEL
umfasst. Es gibt eine Relation von LAND zu LAND. Trage
auch diese in das Klassendiagramm ein. (Vorlage: keine)
137 / 137
• Bundesliga / Wetter-Datenbank
http://dbup2date.uni-bayreuth.de/
• TERRA-Datenbank
http://www.sn.schule.de/~reimegym/terra/index.html
• cia-Datenbank
http://www.imoodle.de/sqltutorial/index.html