Datenbanken

Datenbanken
M. Jakob
Gymnasium Pegnitz
20. Februar 2016
Inhaltsverzeichnis
1
Grundlagen eines Datenbankmanagementsystems (DBMS)
2
Grundlegende Datenbankabfragen ohne verknüpfte Selektionen
3
Erweiterte Abfragen an eine Tabelle
4
Gruppieren und Zusammenfassen von Datensätzen
5
Verknüpfung von Tabellen
6
Syntaxdiagramme
7
Beziehungen zwischen Klassen
In diesem Abschnitt
Grundlagen eines Datenbankmanagementsystems (DBMS)
1.1 Speichern großer Datenmengen
1.2 Klassendiagramme und Datentypen
1.3 Aufbau von Datentabellen
1
1 1
Speichern großer Datenmengen
Grundlagen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
4 / 138
1 1
Verwendung von Datenbanken
Grundlagen
Speichern großer Datenmengen
Heutige tägliche Kommunikationsabläufe sind ohne Datenbanken nicht
mehr denkbar.
Alle möglichen Internetseiten (CD-Datenbanken, Datenbank für
Online-Spiele)
Kundenverwaltung der Telekom
Flugbuchungssysteme von Reisebüros
Personendaten Einwohnermeldeämter
Jede Form der Lagerverwaltung (Kaufhäuser, Supermärkte,
Materiallager)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
5 / 138
1 1
Anforderungen an eine Datenbank
Grundlagen
Speichern großer Datenmengen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
6 / 138
Grundlagen
1
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
M. Jakob (Gymnasium Pegnitz)
DBMS
Daten
Datenbank
Datenbanken
20. Februar 2016
7 / 138
Grundlagen
1
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
8 / 138
Grundlagen
1
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
9 / 138
In diesem Abschnitt
Grundlagen eines Datenbankmanagementsystems (DBMS)
1.1 Speichern großer Datenmengen
1.2 Klassendiagramme und Datentypen
1.3 Aufbau von Datentabellen
1
1 2
Beispiel Bibliotheksverwaltung
Grundlagen
Klassendiagramme und Datentypen
Jedes Attribut einer Klasse besitzt einen bestimmten Datentyp. Nur so
kann das DBMS wissen, wie die Daten interpretiert werden sollen.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
11 / 138
Klassendiagramme und Datentypen
1 2
Datentypen in SQL (unserer DB-Sprache)
Grundlagen
Datentyp
VARCHAR[n]
CHAR[n]
INT
FLOAT
DOUBLE
DECIMAL[n;d]
DATE
TIME
DATETIME
M. Jakob (Gymnasium Pegnitz)
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
Datenbanken
20. Februar 2016
12 / 138
Grundlagen
1
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
13 / 138
Grundlagen
1
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?
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
14 / 138
Grundlagen
1
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?
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
15 / 138
In diesem Abschnitt
Grundlagen eines Datenbankmanagementsystems (DBMS)
1.1 Speichern großer Datenmengen
1.2 Klassendiagramme und Datentypen
1.3 Aufbau von Datentabellen
1
1
Aufbau von Datentabellen
Grundlagen
3
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
17 / 138
Aufbau von Datentabellen
1 3
Definition Tabellenschema und Schlüssel
Grundlagen
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)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
18 / 138
Grundlagen
1
3
Aufbau von Datentabellen
Übung
åÜ 1.6: TERRA-Datenbank erforschen
Die Datenbank TERRA enthält geographische Informationen über der
Erde. Besuche die Seite
åhttp://www.sn.schule.de/ reimegym/terra/index.html
(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? Was fehlt in dem Tabellenschema?
(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) Ergänze die Attribute der Tabellen Land, Stadt und Berg um
sinnvolle Datentypen.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
19 / 138
Grundlagen
1
3
Aufbau von Datentabellen
Übung
åÜ 1.7: Datenbank Bundesliga erforschen
Die Datenbank Bundesliga enthält Informationen über die Fußball
Bundesliga. Besuche die Seite å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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
20 / 138
Grundlagen
1
3
Aufbau von Datentabellen
Übung
åÜ 1.8: Datenbank Wetter in Deutschland erforschen
Die Datenbank Wetter enthält Informationen über das Wetter in
Deutschland. Besuche die Seite å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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
21 / 138
In diesem Abschnitt
Grundlegende Datenbankabfragen ohne verknüpfte Selektionen
2.1 Seletion und Projektion
2.2 Beispiele aus der TERRA-Datenbank
2
Grundlegende Datenbankabfragen
2
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
23 / 138
Grundlegende Datenbankabfragen
2
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
24 / 138
2 1
Bespiel: Selektion und Projektion
Grundlegende Datenbankabfragen
M. Jakob (Gymnasium Pegnitz)
Datenbanken
Seletion und Projektion
20. Februar 2016
25 / 138
2 1
SQL-Syntax einer Tabellenanfrage
Grundlegende Datenbankabfragen
Bisher
Seletion und Projektion
Jetzt
SELECT( Spalten ; Tabellen ; Bedingung)
SELECT S p a l t e n
FROM T a b e l l e n
WHERE Bedingung
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
26 / 138
In diesem Abschnitt
Grundlegende Datenbankabfragen ohne verknüpfte Selektionen
2.1 Seletion und Projektion
2.2 Beispiele aus der TERRA-Datenbank
2
2 2
Beispiele aus der TERRA-Datenbank
Grundlegende Datenbankabfragen
Beispiele
Alle nachfolgenden Beispiele sind aus der åTERRA-Datenbank
entnommen.
Dort sind auch die Tabellenschemata zu finden. Wir verwenden
zunächst nur die Tabelle
BERG (B_NAME, GEBIRGE, HOEHE, JAHR, LAENGE, BREITE)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
28 / 138
Grundlegende Datenbankabfragen
2
2
Beispiele
Beispiel 1
Es soll die gesamte Tabelle Berg ausgegeben werden.
SELECT *
FROM BERG
Der Asterix „*“ ist eine Abkürzung, wenn alle
Spalten selektiert werden sollen.
WHERE kann entfallen, wenn keine speziellen
Zeilen ausgewählt werden sollen.
TERRA
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
29 / 138
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
30 / 138
Grundlegende Datenbankabfragen
2
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 "
Name und Höhe der Berge sind
Projektionen (Spalten)
Himalaya ist eine Selektion zur
Auswahl der Zeilen, die als
Gebirge den Eintrag “Himalaya“
haben
Datentypen die Zeichenfolgen
enthalten (Strings) müssen in
doppelte Hochkommata gesetzt
werden.
TERRA
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
31 / 138
Beispiele
2 2
Vergleichsoperatoren für die Selektion WHERE, 1. Teil
Grundlegende Datenbankabfragen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
32 / 138
Grundlegende Datenbankabfragen
2
2
Beispiele
Beispiel 4
Gesucht sind alle Berge, die mindestens 7000 Meter hoch sind.
SELECT *
FROM
BERG
WHERE HOEHE >= 7000
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
33 / 138
Grundlegende Datenbankabfragen
2
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
34 / 138
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"
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
35 / 138
Beispiele
2 2
Bisherige SQL-Syntax einer Tabellenanfrage
Grundlegende Datenbankabfragen
Für bedingung gilt folgende Syntax:
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
36 / 138
Grundlegende Datenbankabfragen
2
2
Beispiele
Übung
åÜ 2.1: TERRA-Datenbank
Bearbeite die Aufgaben 1-4b der Terra-Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
37 / 138
Grundlegende Datenbankabfragen
2
2
Beispiele
åÜ 2.2: Bundesliga-Datenbank
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
38 / 138
Grundlegende Datenbankabfragen
2
2
Beispiele
åÜ 2.3: Wetter-Datenbank
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?
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
39 / 138
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
åTERRA-Datenbank
åBundesliga-Datenbank
åWetter-Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
40 / 138
In diesem Abschnitt
3
3.1
3.2
3.3
3.4
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
Erweiterte Abfragen an eine Tabelle
3
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 ’
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
42 / 138
Erweiterte Abfragen an eine Tabelle
3
1
Bedingungen verbinden
Beispiel 2
Gesucht sind alle Berge des Himalaya und der Anden.
SELECT *
FROM
BERG
WHERE Gebirge = ’Anden ’
OR Gebirge = ’Himalaya ’
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
43 / 138
Erweiterte Abfragen an eine Tabelle
3
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 ’ )
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
44 / 138
Bedingungen verbinden
3 1
Logische Operatoren für die Selektion WHERE
Erweiterte Abfragen an eine Tabelle
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
45 / 138
Bedingungen verbinden
3 1
Erweiterte Syntaxdiagramme der WHERE-Clause
Erweiterte Abfragen an eine Tabelle
Für bedingung ist das Syntaxdiagramm noch unverändert:
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
46 / 138
In diesem Abschnitt
3
3.1
3.2
3.3
3.4
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
3
Zeichenfolgen Selektieren
Erweiterte Abfragen an eine Tabelle
2
Zeichenfolgen suchen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
48 / 138
Zeichenfolgen suchen
3 2
Erweitertes Syntaxdiagramm der Bedingung
Erweiterte Abfragen an eine Tabelle
Unverändertes Syntaxdiagramm der WHERE-Clause
Erweitertes Syntaxdiagramm der bedingung
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
49 / 138
Erweiterte Abfragen an eine Tabelle
3
2
Zeichenfolgen suchen
Beispiel 1
Gesucht sind alle Berge der Alpen.
SELECT *
FROM BERG
WHERE GEBIRGE l i k e ’% Alpen %’
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
50 / 138
Erweiterte Abfragen an eine Tabelle
3
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%’
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
51 / 138
Erweiterte Abfragen an eine Tabelle
3
2
Zeichenfolgen suchen
Übung
åÜ 3.1: TERRA-Datenbank
(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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
52 / 138
Erweiterte Abfragen an eine Tabelle
3
2
Zeichenfolgen suchen
åÜ 3.2: cia-Datenbank
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)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
53 / 138
Erweiterte Abfragen an eine Tabelle
3
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
åTERRA-Datenbank
åBundesliga-Datenbank
åWetter-Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
54 / 138
In diesem Abschnitt
3
3.1
3.2
3.3
3.4
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
Erweiterte Abfragen an eine Tabelle
3
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
56 / 138
Erweiterte Abfragen an eine Tabelle
3
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
57 / 138
Erweiterte Abfragen an eine Tabelle
3
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
58 / 138
Erweiterte Abfragen an eine Tabelle
3
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
59 / 138
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
60 / 138
Erweiterte Abfragen an eine Tabelle
3
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
61 / 138
Erweiterte Abfragen an eine Tabelle
3
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
62 / 138
Ausgaben Sortieren und begrenzen
3 3
Syntaxdiagramm: order by-Term (vollständig)
Erweiterte Abfragen an eine Tabelle
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
63 / 138
Ausgaben Sortieren und begrenzen
3 3
Syntaxdiagramm: limit-Term (vollständig)
Erweiterte Abfragen an eine Tabelle
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
64 / 138
Erweiterte Abfragen an eine Tabelle
3
3
Ausgaben Sortieren und begrenzen
Übung
åÜ 3.4: Wetter-Datenbank
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
65 / 138
Erweiterte Abfragen an eine Tabelle
3
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
åTERRA-Datenbank
åBundesliga-Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
66 / 138
In diesem Abschnitt
3
3.1
3.2
3.3
3.4
Erweiterte Abfragen an eine Tabelle
Bedingungen verbinden
Zeichenfolgen suchen
Ausgaben Sortieren und begrenzen
Vollständiger select-Term
3 4
Mehrfachnennungen vermeiden
Erweiterte Abfragen an eine Tabelle
Vollständiger select-Term
Gesucht sind alle Gebirge die in der Tabelle Berg enthalten sind.
SELECT GEBIRGE
FROM BERG
Erzeugt Mehrfachnennungen.
TERRA
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
68 / 138
3 4
Mehrfachnennungen vermeiden
Erweiterte Abfragen an eine Tabelle
Vollständiger select-Term
Das Schlüsselwort DISTINCT
nach dem Schlüsselwort SELECT vermeidet Mehrfachnennungen
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
69 / 138
Erweiterte Abfragen an eine Tabelle
3
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
70 / 138
Erweiterte Abfragen an eine Tabelle
3
4
Vollständiger select-Term
Ausdrücke in Spalten
In Spalten können Rechenausdrücke mit den üblichen Rechenzeichen
angegeben werden.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
71 / 138
3
Umbenennen von Spalten
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Gib der Spalte der vollständigen Tausender den Name „Tausender“
SELECT BERG, HOEHE, round (HOEHE / 1000) AS ’
Tausender ’
FROM BERG
TERRA
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
72 / 138
3
Umbenennen von Spalten
Erweiterte Abfragen an eine Tabelle
4
Vollständiger select-Term
Mit dem Schlüsselwort AS lassen sich Spalten umbenennen.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
73 / 138
3 4
åÜ 3.6: cia-Datenbank
Erweiterte Abfragen an eine Tabelle
Übung
Vollständiger select-Term
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 alphabetisch nach der
Region und innerhalb einer Region nach dem BIP pro
Quadratkilometer.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
74 / 138
Vollständiger select-Term
3 4
åÜ 3.7: Bundesliga-Datenbank
Erweiterte Abfragen an eine Tabelle
Übung
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 Formel am
torgefährlichsten sind, und ihre Torgefährlichkeit sortiert aus.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
75 / 138
Erweiterte Abfragen an eine Tabelle
3
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
åTERRA-Datenbank
åBundesliga-Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
76 / 138
3 4
åÜ 3.9: TERRA-Datenbank
Erweiterte Abfragen an eine Tabelle
Übung
Vollständiger select-Term
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
77 / 138
In diesem Abschnitt
Gruppieren und Zusammenfassen von Datensätzen
4.1 Aggregatfunktionen
4.2 Grupierungen
4.3 Auswahl von Gruppen
4
Gruppieren und Zusammenfassen von Datensätzen
4
1
Aggregatfunktionen
åBeispiel 1 (TERRA)
Wie viele Flüsse sind in der Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
79 / 138
Gruppieren und Zusammenfassen von Datensätzen
4
1
Aggregatfunktionen
åBeispiel 1 (TERRA)
Wie viele Flüsse sind in der Datenbank
SELECT count ( * ) as ’Anzahl der Flüsse ’
FROM FLUSS
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
79 / 138
Gruppieren und Zusammenfassen von Datensätzen
4
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
80 / 138
In diesem Abschnitt
Gruppieren und Zusammenfassen von Datensätzen
4.1 Aggregatfunktionen
4.2 Grupierungen
4.3 Auswahl von Gruppen
4
Gruppieren und Zusammenfassen von Datensätzen
4
2
Gruppierungen
åBeispiel 1 (TERRA)
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
82 / 138
Gruppieren und Zusammenfassen von Datensätzen
4
2
Gruppierungen
åBeispiel 2 (TERRA)
Liste die Anzahl der Wüsten, gruppiert nach der Wüstenart auf
SELECT WUESTENART, count ( * )
FROM WUESTE
Group by WUESTENART
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
83 / 138
Gruppieren und Zusammenfassen von Datensätzen
4
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
84 / 138
Gruppieren und Zusammenfassen von Datensätzen
4
2
Gruppierungen
åBeispiel 3 (TERRA)
Liste die Gesamtfläche der Wüstenarten
SELECT WUESTENART, sum(FLAECHE) AS Gesamtflaeche
FROM WUESTE
group by WUESTENART
ORDER BY Gesamtflaeche
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
85 / 138
In diesem Abschnitt
Gruppieren und Zusammenfassen von Datensätzen
4.1 Aggregatfunktionen
4.2 Grupierungen
4.3 Auswahl von Gruppen
4
Gruppieren und Zusammenfassen von Datensätzen
4
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
87 / 138
Gruppieren und Zusammenfassen von Datensätzen
4
3
Auswahl von Gruppen
åBeispiel 1 (TERRA)
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
88 / 138
Auswahl von Gruppen
4 3
åÜ 4.1: cia-Datenbank (analog Übg 2 cia)
Gruppieren und Zusammenfassen von Datensätzen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
89 / 138
Auswahl von Gruppen
4 3
åÜ 4.2: cia-Datenbank (analog Übg 4 Aufg 1-5 cia)
Gruppieren und Zusammenfassen von Datensätzen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
90 / 138
Auswahl von Gruppen
4 3
åÜ 4.3: WM-Titel-Datenbank (analog Übg 4 Aufg
6-10 WMTitel)
Gruppieren und Zusammenfassen von Datensätzen
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.
(e) Ermittle ab 1995 in geordneter Reihenfolge die Gesamtzahl der
Team-Punkte für Teams mit mindestens 200 Punkten.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
91 / 138
4 3
åÜ 4.4: TERRA-Datenbank
Gruppieren und Zusammenfassen von Datensätzen
Auswahl von Gruppen
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: O = 4πr 2 , Erdradius
gleich 6370 km. [1]
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
92 / 138
4 3
åÜ 4.5: TERRA-Datenbank
Gruppieren und Zusammenfassen von Datensätzen
Auswahl von Gruppen
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]
(g) Welche Länder haben mehr als 4 Millionenstädte? [7]
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
93 / 138
In diesem Abschnitt
Verknüpfung von Tabellen
5.1 Basisabfragen
5.2 Schema für Join-Abfragen
5
Verknüpfung von Tabellen
5
1
Basisabfragen
Bespiel
Gesucht ist der Name des Klassenleiters der 5. Klasse.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
95 / 138
Verknüpfung von Tabellen
5
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.
Die Verknüpfung der Tabellen erfolgt durch Angabe der beteiligten
Tabellen in der From- Klausel.
Bei gleichen Spaltennamen aus verschiedenen Tabellen muss in
der Abfragen zusätzlich der Tabellenname angegeben werden.
Man schreibt: Tabellenname.Attributname.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
96 / 138
Verknüpfung von Tabellen
5
1
Basisabfragen
Beispiel 1
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
97 / 138
Verknüpfung von Tabellen
5
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
97 / 138
Verknüpfung von Tabellen
5
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 ;
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
98 / 138
Verknüpfung von Tabellen
5
1
Basisabfragen
åBeispiel 1 (TERRA)
Gewünscht ist eine Liste welches Land zu welchem Kontinent gehört.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
99 / 138
Verknüpfung von Tabellen
5
1
Basisabfragen
åBeispiel 1 (TERRA)
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
99 / 138
Verknüpfung von Tabellen
5
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.
Die Verknüpfung der Tabellen erfolgt durch Angabe der beteiligten
Tabellen in der From- Klausel.
Bei gleichen Spaltennamen aus verschiedenen Tabellen muss in
der Abfragen zusätzlich der Tabellenname angegeben werden.
Man schreibt: Tabellenname.Attributname.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
100 / 138
Verknüpfung von Tabellen
5
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
101 / 138
Verknüpfung von Tabellen
5
1
Basisabfragen
Bespiel
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
102 / 138
Verknüpfung von Tabellen
5
1
Basisabfragen
åBeispiel (TERRA)
SELECT L_Name , K_Name
FROM LAND, UMFASST
Es werden alle Länder(190) mit allen UMFASST-Zeilen (188)
kombiniert es entstehen 35720 Zeilen.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
103 / 138
5 1
åÜ 5.1: TERRA-Datenbank
Verknüpfung von Tabellen
Basisabfragen
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]
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
104 / 138
5 1
åÜ 5.2: Bundesliga-Datenbank
Verknüpfung von Tabellen
Basisabfragen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
105 / 138
In diesem Abschnitt
Verknüpfung von Tabellen
5.1 Basisabfragen
5.2 Schema für Join-Abfragen
5
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
107 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
1. Schritt: Welche Tabellen beinhalten die gewünschten Informationen?
LAND (L_NAME, L_ID, EINWOHNER, FLAECHE, HAUPTSTADT,
LT_ID)
LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE,
HAUPTSTADT)
INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE, BREITE)
GEO_INSEL(LT_ID, L_ID, I_NAME)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
108 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
2. Schritt: Welche Spalten beinhalten die gewünschten Informationen?
LAND (L_NAME, L_ID, EINWOHNER, FLAECHE, HAUPTSTADT,
LT_ID)
LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE,
HAUPTSTADT)
INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE, BREITE)
GEO_INSEL(LT_ID, L_ID, I_NAME)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
109 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
3. Schritt: Eine der Tabellen komplett ausgeben lassen
SELECT *
FROM LAND
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
110 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
4. Schritt: Durch welche Spalte ist die Tabelle mit einer anderen
verbunden?
LAND (L_NAME, L_ID , EINWOHNER, FLAECHE, HAUPTSTADT,
LT_ID)
LANDTEIL (LT_NAME, LT_ID, L_ID , EINWOHNER, LAGE,
HAUPTSTADT)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
111 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
5. Schritt: Tabellen verjoinen, d.h.
zweite Tabelle in die from-Klausel
Verknüpfungsspalten in die where-Klausel
SELECT *
FROM LAND as l , LANDTEIL as l t
WHERE l . L_ID = l t . L_ID
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
112 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
113 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
7. Schritt: Nach dem gleichen Schema die nächste Tabelle
dazunehmen
nächste Tabelle in die from-Klausel
Verknüpfungsspalten in die where-Klausel
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
and
l . L_ID = g . L_ID
and l t . LT_ID = g . LT_ID
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
114 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
8. Schritt: Letzte Tabelle dazunehmen
LAND (L_NAME, L_ID, EINWOHNER, FLAECHE, HAUPTSTADT,
LT_ID)
LANDTEIL (LT_NAME, LT_ID, L_ID, EINWOHNER, LAGE,
HAUPTSTADT)
INSEL(I_NAME, INSELGRUPPE, FLAECHE, LAENGE, BREITE)
GEO_INSEL(LT_ID, L_ID, I_NAME)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
115 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
116 / 138
5 Schema für Join-Abfragen
åSchema für Joins (TERRA)
Verknüpfung von Tabellen
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
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
117 / 138
5 Schema für Join-Abfragen
åÜ 5.3: TERRA-Datenbank
Verknüpfung von Tabellen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
118 / 138
5 Schema für Join-Abfragen
Ü 5.4: Eigene Abfragen formulieren
Verknüpfung von Tabellen
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
119 / 138
Syntaxdiagramme
6
SQL-Querry
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
120 / 138
Syntaxdiagramme
6
select- und From-Term
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
121 / 138
6
Bedingungen in where- und having-Clause
Syntaxdiagramme
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
122 / 138
6
order by-, limit- und group bySyntaxdiagramme
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
123 / 138
In diesem Abschnitt
Beziehungen zwischen Klassen
7.1 Wiederholung Abschnitt 1
7.2 Beziehungen und Kardinalitäten
7
7 1
Beispiel: Bibliotheksverwaltung
Klassenbeziehungen
M. Jakob (Gymnasium Pegnitz)
Datenbanken
Wiederholung Abschnitt 1
20. Februar 2016
125 / 138
Wiederholung Abschnitt 1
7 1
Datentypen in SQL (unserer DB-Sprache)
Klassenbeziehungen
Datentyp
VARCHAR[n]
CHAR[n]
INT
FLOAT
DOUBLE
DECIMAL[n;d]
DATE
TIME
DATETIME
M. Jakob (Gymnasium Pegnitz)
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
Datenbanken
20. Februar 2016
126 / 138
Wiederholung Abschnitt 1
7 1
Definition Tabellenschema und Schlüssel
Klassenbeziehungen
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)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
127 / 138
Wiederholung Abschnitt 1
7 1
Gleicher Sachverhalt verschiedene Darstellungen
Klassenbeziehungen
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)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
128 / 138
Wiederholung Abschnitt 1
7 1
ER-Diagramme — Noch ’ne andere Darstellung
Klassenbeziehungen
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)
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
129 / 138
In diesem Abschnitt
Beziehungen zwischen Klassen
7.1 Wiederholung Abschnitt 1
7.2 Beziehungen und Kardinalitäten
7
7
Beziehungen (Relationen)
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
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.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
131 / 138
Klassenbeziehungen
7
2
Beziehungen und Kardinalitäten
Kardinalitäten
Die Kardinalität gibt an, wie viele Objekte der Nachbarklasse zu einem
Objekt der Heimatklasse gehören.
Wenn ich einen Schüler
(Heimatklasse) herausgreife
so kann er nur in einer
Schulklasse (Nachbarklasse)
sein. Also kommt ein 1 an die
Schulklasse.
Wenn ich eine Schulklasse
(Heimatklasse) herausgreife
so können dazu mehrere
Schüler gehören. Also kommt
ein Buchstabe (z.B. n) an die
Klasse Schüler.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
132 / 138
7
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
Arten der Kardinalität
Art
1
n
0,1
1 .. n
M. Jakob (Gymnasium Pegnitz)
Beschreibung
Genau ein Objekt
Keines, eines oder mehrere Objekte
Kein oder ein Objekt
Ein oder mehrere Objekte
Datenbanken
20. Februar 2016
133 / 138
7 2
Klassendiagramme umfassen die
Klassenbeziehungen
Beziehungen und Kardinalitäten
Klassenkarten
mit Attributlisten
und Datentypen,
Schlüssel und
Relationen mit
Kardinalitäten.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
134 / 138
Beziehungen und Kardinalitäten
7 2
ER-Diagramm statt Klassendiagramm
Klassenbeziehungen
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
135 / 138
7
Ü 7.1: Schulverwaltung
Klassenbeziehungen
2
Beziehungen und Kardinalitäten
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 ggf. weitere Relationen.
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
136 / 138
Klassenbeziehungen
7
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)
åBundesliga / Wetter-Datenbank
åTERRA-Datenbank
åcia-Datenbank
M. Jakob (Gymnasium Pegnitz)
Datenbanken
20. Februar 2016
137 / 138

Download Report