Transcript Datenbanksysteme - Zugriff mit SQL

Informationssysteme / Datenbankabfragen
Thomas Mohr
24. April 2009
I
N
F
O
R
M
A
T
I
K
R
M
A
T
I
K
Was sagt der Lehrplan?
N
F
O
 Bedeutung von Datenbanken
 Informationsdarstellung mit verknüpften Tabellen
 Erstellung von Abfragen mit einer Abfragesprache
I
 Sammlung personenbezogener Daten
 Missbrauch personenbezogener Daten
 Schutz personenbezogener Daten
2
I
N
F
O
R
M
A
T
I
K
Informationssysteme
3
Was ist ein Informationssystem ?
I
K
• Ein Informationssystem kann auf formalisierte Fragen eines
Anwenders Antworten aus einer gegebenen Datenmenge geben
A
T
 Komponenten eines Informationssystems:
R
Erfassung
O
M
Informationssystem
Speicherung
Datenbanksystem
DBS
Datenbankmanagementsystem
F
DBMS, z.B. MySQL
N
Analyse
Darstellung
I
Datenbank
(Datenbasis)
4
Datenbank
(Datenbasis)
Ein Ausgangspunkt im Unterricht…
I
K
• Schüler führen eine Internet-Recherche durch:
„Nachbarländer Deutschlands – Fläche, Einwohner, Hauptstadt“
I
N
F
O
R
M
A
T
 Nutzung eines Informationssystems
 Unterschiedliche Darstellung der Informationen
5
Software-Architekturen: „Standalone“-Programm
K
•
Vorteile
Nachteile
 Daten meist nur vom
erzeugenden Programm zu lesen
 Erweiterungen aufwändig
 Immer wieder gleiche Probleme
(z.B. Datumsformat)
O
R
M
A
T
I
 Übersichtlichkeit (?)
 Schnell zu programmieren
 nur eine Programmiersprache
•
I
N
F
Anwendungsprogramm
6
PC
R
M
A
T
I
K
Software-Architekturen - Client-Server
Client
I
N
F
O
Anwendungsprogramm
Anwendungsprogramm
(Datenbank)Server
Server
7
Software-Architekturen - Client-Server
 Datenbank übernimmt
„Standardaufgaben“
 Daten zentral vorhanden
 Erweiterungen relativ einfach
I
T
Vorteile
A
K
•
Nachteile
 Installation von Software auf
allen Clients notwendig
 Weitere „Sprache“ zum
Datenbankzugriff
R
M
(für mehrere Benutzer / Programme)
•
Client
I
N
F
O
Anwendungsprogramm
(Datenbank)Server
Server
8
A
T
I
K
Software-Architekturen – Webarchitektur
(Browser)
R
M
Webclient
Client
Server
I
N
F
O
Anwendungsprogramm
Webserver (z.B. Apache und PHP)
Datenbankserver
(z.B. MySQL)
Server
9
•
Vorteile
•
 Keine Installation von
zusätzlicher Software beim Client
Nachteile
 http-Protokoll ohne
Sessionverwaltung
Webclient
Client
(Browser)
Webserver
(z.B. Apache und PHP)
Server
I
N
F
O
R
M
A
T
I
K
Software-Architekturen – Webarchitektur
Datenbankserver
(z.B. MySQL)
Server
10
• Viele (Web-) Clients teilen sich die Dienste eines Webservers, der
wiederum auf einen Datenbankserver zurückgreift.
 In kleinen Systemen können Web- und Datenbankserver auf dem
gleichen Rechner sein.
A
T
I
K
Typische Hardware-Verteilung
Webserver
I
N
F
O
R
M
Clients
Datenbankserver
11
K
…bis hin zum Extrem einer Web Farm
T
I
Load Balancer
M
A
Application Server Farm
R
je 4 Prozessoren
O
HACMP Fail-Over
FailOver DB Server
32 GByte RAM
32 GByte RAM
I
N
F
Production DB Server
FDDI Switch
2,4 TByte
12
• Relationale Datenbanken
 Die Daten werden in Form von Tabellen gespeichert
 Zwischen den Tabellen werden Beziehungen aufgebaut (Relationen)
Attribut (Merkmal, Spalte)
Attributwert (Datenwert)
Land
Name
Einwohner
Hauptstadt
Kontinent
Dänemark
Deutschland
Indien
Rwanda
5165000
81338000
761000000
6300000
Kopenhagen
Berlin
Delhi
Kigali
Europa
Europa
Asien
Afrika
I
N
F
O
R
M
A
T
I
K
Arten von Datenbanken
13
Datensatz (Tupel)
Attributklasse
I
N
F
O
R
M
A
T
I
K
Arten von Datenbanken
14
• Relationale Datenbanken – typische Vertreter






Oracle
IBM (DB/2)
Microsoft SQL Server (Access ?)
Informix
MySQL
OpenSource
PostGreSQL
O
R
M
A
T
I
K
Was bietet mir eine Datenbank?
 Strukturierte Speicherung von Daten
 Verteilter, gleichzeitiger Zugriff mehrerer Benutzer / Programme
 ACID – Prinzip
 Atomicity
• Transaktionen (Änderungen an der Datenbank) werden ganz oder gar
nicht durchgeführt
 Consistency
• Eine Transaktion führt wieder zu einem konsistenten (gültigen) Zustand
der Datenbank
I
N
F
 Isolation
15
• Transaktionen beeinflussen sich nicht gegenseitig
 Durability
• Eine Transaktion ist dauerhaft gespeichert, auch gegen Systemabstürze
gesichert
Länder mit über 10
Mio. Einwohner
Land
LNR
Name
Einwohner
Hauptstadt
Kontinent
DK
D
IND
RWA
…
Dänemark
Deutschland
Indien
Rwanda
…
5.16
81.34
761.00
6.30
…
Kopenhagen
Berlin
Delhi
Kigali
…
Europa
Europa
Asien
Afrika
…
I
N
F
O
R
M
T
 SELECT Name, Einwohner, Kontinent
FROM Land
WHERE Einwohner > 10
A
I
K
Datenbankzugriff mit SQL - Ein erstes Beispiel
17
Ergebnistabelle
Name
Einwohner
Kontinent
Deutschland
Indien
…
81.34
761.00
…
Europa
Asien
…
• Welche Länder gibt es in Europa mit mehr als 20 Mio Einwohner?
SELECT
FROM
WHERE
AND
Name, Einwohner
Land
Kontinent = 'Europa'
Einwohner > 20
I
N
F
O
R
M
A
T
I
K
Ein erstes Problem der Datenbank führt zur Erweiterung…
Frankreich??
Problem: Europa wird unterschiedlich geschrieben!
Lösung: Auslagerung in eine eigene Tabelle
18
• Wozu dient die Spalte LNR?
• … ein anderes Beispiel: Eine Tabelle mit Städten.
 Suche nach der Hauptstadt „Berlin“
R
M
A
T
I
K
Exkurs: Primärschlüssel
 Suche nach der Hauptstadt „Washington“
I
N
F
O
Man benötigt noch das
Land als Suchhilfe.
19
Selbst das Land reicht als
Suchhilfe nicht aus.
• Land und Kontinent werden in zwei getrennten Tabellen
gespeichert und über eine Beziehung miteinander verknüpft.
• Zur Verknüpfung dient ein Kürzel des Kontinents, das als
Fremdschlüssel in Land gespeichert wird.
LNR
Name
Einwohner
Hauptstadt
Kontinent
DK
D
IND
RWA
Dänemark
Deutschland
Indien
Rwanda
5.16
81.34
761.00
6.30
Kopenhagen
Berlin
Delhi
Kigali
Europa
Europa
Asien
Afrika
F
O
R
M
A
T
I
K
Relationale Datenbanken – Beziehungen
I
N
Land
Kontinent
LNR
Name
…
KNR
KNR
Name
DK
D
IND
RWA
Dänemark
Deutschland
Indien
Rwanda
…
…
…
…
EU
EU
AS
AF
EU
AS
AF
Europa
Asien
Afrika
21
Schlüsselattribut aus Kontinent
 Die Abarbeitung eines Joins in mehreren Schritten kann an folgendem
Beispiel veranschaulicht werden:
• Es sollen alle Länder mit ihren Kontinenten ausgegeben werden, die mehr
als 10 Mio. Einwohner haben.
I
N
F
O
R
M
T
• Müssen in SQL Daten aus mehreren Tabellen entnommen werden,
so werden sog. „Joins“ gebildet.
A
I
K
SQL – einfache Joins
23
Land
Kontinent
LNR
Name
Einwohner
KNR
KNR
Name
DK
D
IND
RWA
Dänemark
Deutschland
Indien
Rwanda
5.16
81.34
761.00
6.30
EU
EU
AS
AF
EU
AS
AF
Europa
Asien
Afrika
1. Cross-Join („jede Zeile mit jeder“)
Land
SELECT *
FROM Land, Kontinent
LNR
Name
Einwohner
KNR
KNR
Name
DK
D
IND
RWA
Dänemark
Deutschland
Indien
Rwanda
5.16
81.34
761.00
6.30
EU
EU
AS
AF
EU
AS
AF
Europa
Asien
Afrika
I
N
F
O
R
M
A
T
I

24
LNR Name
Einwohner
KNR
KNR
Name
DK
DK
DK
D
D
D
IND
IND
IND
…
5.16
5.16
5.16
81.34
81.34
81.34
761.00
761.00
761.00
…
EU
EU
EU
EU
EU
EU
AS
AS
AS
…
EU
AS
AF
EU
AS
AF
EU
AS
AF
…
Europa
Asien
Afrika
Europa
Asien
Afrika
Europa
Asien
Afrika
Dänemark
Dänemark
Dänemark
Deutschland
Deutschland
Deutschland
Indien
Indien
Indien
…
Kontinent
K
SQL – einfache Joins
I


Es dürfen nur die Zeilen genommen werden, für die die „Land“ und
die „Kontinent“ Tabelle Daten des gleichen Kontinents enthalten.
Dies wird durch die sog. „Join-Bedingung“ erreicht.
SELECT *
FROM Land, Kontinent
WHERE Land.KNR= Kontinent.KNR
I
N
F
O
R
M

T
2. Einschränken auf „passende“ Datensätze.
A
K
SQL – einfache Joins
25
LNR Name
Einwohner
KNR
KNR
Name
DK
DK
DK
D
D
D
IND
IND
IND
…
5.16
5.16
5.16
81.34
81.34
81.34
761.00
761.00
761.00
…
EU
EU
EU
EU
EU
EU
AS
AS
AS
…
EU
AS
AF
EU
AS
AF
EU
AS
AF
…
Europa
Asien
Afrika
Europa
Asien
Afrika
Europa
Asien
Afrika
Dänemark
Dänemark
Dänemark
Deutschland
Deutschland
Deutschland
Indien
Indien
Indien
…
2. Einschränken auf „passende“ Datensätze (2).


T
I
K
SQL – einfache Joins
I
N
F
O
R
M
A

Es sollen nur Länder mit > 10 Mio. Einwohner gezeigt werden.
Momentan würde auch „Dänemark“ ausgegeben werden.
Also muss eine weitere Bedingung erfüllt sein:
SELECT *
FROM Land, Kontinent
WHERE Land.KNR = Kontinent.KNR
AND Land.Einwohner > 10
26
LNR Name
Einwohner
KNR
KNR
Name
DK
DK
DK
D
D
D
IND
IND
IND
…
5.16
5.16
5.16
81.34
81.34
81.34
761.00
761.00
761.00
…
EU
EU
EU
EU
EU
EU
AS
AS
AS
…
EU
AS
AF
EU
AS
AF
EU
AS
AF
…
Europa
Asien
Afrika
Europa
Asien
Afrika
Europa
Asien
Afrika
Dänemark
Dänemark
Dänemark
Deutschland
Deutschland
Deutschland
Indien
Indien
Indien
…
3. Einschränken auf gesuchte Spalten.


Nur bestimmte Spalten werden ausgegeben.
SELECT Land.Name, Land.Einwohner, Kontinent.Name
FROM Land,Kontinent
WHERE Land.KNR = Kontinent.KNR
AND Land.Einwohner > 10
LNR Name
Einwohner
KNR
KNR
Name
D
Deutschland
IND Indien
…
…
81.34
761.00
…
EU
AS
…
EU
AS
…
Europa
Asien
Name
Einwohner
Name
Deutschland
Indien
…
81.34
761.00
…
Europa
Asien
…
I
N
F
O
R
M
A
T
I
K
SQL – einfache Joins
27
In der Realität versucht das
DBMS, durch „geschicktes“
Vorgehen die Datenmenge
schon früher zu reduzieren.
• Es sollen nun die wichtigsten Orte der Länder gespeichert werden.
• Wie sieht eine solche Ländertabelle aus?
 Es wird eine neue Tabelle „Ort“ angelegt mit einem Fremdschlüssel
auf „Land“.
Ort
Land
ONR
Name
…
LNR
LNR
Name
…
KNR
BANGAL
GOETTI
KARLSR
KOPENH
Bangalore
Göttingen
Karlsruhe
Kopenhagen
…
…
…
…
IND
D
D
DK
DK
D
IND
RWA
Dänemark
Deutschland
Indien
Rwanda
…
…
…
…
EU
EU
AS
AF
I
N
F
O
R
M
A
T
I
K
Erweiterung der Datenbank
Schlüsselattribut aus Land
28
• Ein erstes „E/R-Modell“
 eigentlich intuitiv zu lesen!?
T
I
K
Bisheriges „Schema“ der Datenbank
M
A
Teil von
Land
Ort
Teil von
I
N
F
O
R
Hauptstadt
29
Kontinent
• Es sollen Flüsse gespeichert werden.
 Flüsse fließen durch Orte.
 Manche Orte werden von mehreren Flüssen durchflossen
(z.B. Koblenz)
• Wie sieht die Tabelle für die Flüsse aus?
Fluss
Ort
FNR
Name
Laenge ONR
ONR
Name
ELB
MEK
MOS
RHE
Elbe
Mekong
Mosel
Rhein
1144
4500
544
1320
GOETTI
KARLSR
KOBLEN
KOPENH
Göttingen
Karlsruhe
Koblenz
Kopenhagen
HAMBUR
PHNOMP
KOBLEN
KOBLEN
I
N
F
O
R
M
A
T
I
K
Erweiterung der Datenbank
Problem: Für Flüsse müssen beliebig viele Orte eingetragen werden.
31
• Es sollen Flüsse gespeichert werden.
 Flüsse fließen durch Orte.
 Manche Orte werden von mehreren Flüssen durchflossen
(z.B. Koblenz)
• Wie sieht die Tabelle für die Flüsse aus?
Fluss
Ort
FNR
Name
Laenge
ONR
Name
FNR
ELB
MEK
MOS
RHE
Elbe
Mekong
Mosel
Rhein
1144
4500
544
1320
GOETTI
KARLSR
KOBLEN
KOPENH
Göttingen
Karlsruhe
Koblenz
Kopenhagen
LEI
RHE
RHE
I
N
F
O
R
M
A
T
I
K
Erweiterung der Datenbank
Problem: Für Orte müssen mehrere Flüsse eingetragen werden.
32
Lösung: Auslagerung der Zuordnung in eine eigene Tabelle
F
O
R
M
A
T
I
K
Erweiterung der Datenbank
• Es sollen Flüsse gespeichert werden.
 Flüsse fließen durch Orte.
 Manche Orte werden von mehreren Flüssen durchflossen
(z.B. Koblenz)
 Zuordnungstabelle mit Schlüsseln aus beiden Haupttabellen.
Fluss
Ort
FNR
Name
Laenge
ONR
Name
ELB
MEK
MOS
RHE
Elbe
Mekong
Mosel
Rhein
1144
4500
544
1320
GOETTI
KARLSR
KOBLEN
KOPENH
Göttingen
Karlsruhe
Koblenz
Kopenhagen
FNR
ONR
ELB
RHE
RHE
MOS
HAMBUR
KOBLEN
KARLSR
KOBLEN
I
N
Stadtfluss
33
Sprache
n
gesprochen
Land
n
1
Teil von
n
Ort
1
Hauptstadt
1
n
F
R
m
O
M
A
T
I
K
Struktur der Datenbank terra4
m
Kontinent
34
durchfließt
1
I
N
Teil von
Fluss
• Manchmal müssen die Ergebnisse einer Anfrage gruppiert und
verrechnet werden.
 Bsp.: Wie viele Städte sind in Europa pro Land verzeichnet?
 SELECT o.ONR, l.Name
FROM Ort o, Land l
WHERE o.LNR = l.LNR
AND l.KNR = 'EU‘
ORDER BY l.Name
I
N
F
O
R
M
A
T
I
K
Gruppieren von Ergebnissen
35
ONR
Name
AALBOR
KOPENH
BERLIN
DUESSE
MAINZ
TALLIN
Dänemark
Dänemark
Deutschland
Deutschland
Deutschland
Estland
Selbst zählen???
NEIN!
• Manchmal müssen die Ergebnisse einer Anfrage gruppiert und
verrechnet werden.
 Bsp.: Wie viele Städte sind in Europa pro Land verzeichnet?
 SELECT l.Name, COUNT(*) AS Anzahl
FROM Ort o, Land l
WHERE o.LNR = l.LNR
AND l.KNR = 'EU‘
GROUP BY l.Name
ORDER BY l.Name
I
N
F
O
R
M
A
T
I
K
Gruppieren von Ergebnissen
36
ONR
Name
AALBOR
KOPENH
BERLIN
DUESSE
MAINZ
TALLIN
Dänemark
Dänemark
Deutschland
Deutschland
Deutschland
Estland
2
3
1
Name
Anzahl
Dänemark
Deutschland
Estland
2
3
1
K
Struktur der kompletten Datenbank „miniterra“
I
Sprache
T
n
benachbart
m
m
1
Teil von
n
Land
n
Ort
1
Hauptstadt
1
n
F
R
n
O
M
A
gesprochen
durchfließt
m
1
I
N
Teil von
Kontinent
37
Fluss
n
1
mündet
I
N
F
O
R
M
A
T
I
K
u.v.m.
38
• SQL bietet noch einige weitere (hier nicht behandelte)
Möglichkeiten:
 OUTER JOINS:
• Es werden beim Join auch Datensätze angezeigt, die keinen „Join-Partner“
finden.
• Bsp.: Alle Städte sollen ausgegeben werden und zwar (wenn vorhanden)
mit ihren Flüssen.
 Behandlung von leeren Feldern (NULL-Werten)
 …
I
N
F
O
R
M
A
T
I
K
Vernetzung des Themas - Auswertungen
39
• Grafische Auswertung der Datenbank
(z.B. Anzahl der Länder pro Kontinent)
 über Werkzeuge (z.B. Excel)
 über selbst erstellte Programme
I
N
F
O
R
M
A
T
I
K
Vernetzung des Themas - Koordinatensystem
40
• Umrechnung Breiten/Längen-Angaben in Bildschirmkoordinaten
• Problem: „Verfolgen eines Flusslaufes“
 von der Quelle zum Meer
z.B. Spree
Nordsee
T
I
K
Vernetzung des Themas - Grenzen von SQL
• Hier werden iterative Strukturen
benötigt, die SQL (als mengenorientierte Sprache) im Standard
nicht bietet.
Weser
Rhein
Fulda
Mosel
Neckar
Donau
Inn
F
I
N
Werra
Main
 Anknüpfung an Programmierung
 PHP, Delphi, Java
Havel
Aller
O
R
M
A
Elbe
Isar
41
Spree
• Problem: „Verfolgen eines Flusslaufes“
 vom Meer zu den Zuflüssen
z.B. Nordsee
Nordsee
T
I
K
Vernetzung des Themas - Grenzen von SQL
R
M
A
Elbe
Havel
Aller
 Beispiel für eine (elegante?)
rekursive Programmierung
Weser
Rhein
O
Fulda
Werra
F
Main
Neckar
Donau
Inn
I
N
Mosel
Isar
42
Spree
 Freie Software mit großer Entwicklergemeinde
 inzwischen allerdings von SUN aufgekauft
 Installation über XAMPP-Paket auch für Schüler leicht machbar
 vorkonfigurierte Version (inkl. Datenbanken) ohne Installationszwang
kann den Schülern zur Verfügung gestellt werden
R
M
A
T
I
K
Welches Datenbanksystem ? - Vorschlag: MySQL
 Echte Fremdschlüssel-Überwachung
 Trigger
 …
I
N
F
O
 Es fehlen noch typische Konzepte relationaler Datenbanken
• www.xampp.org
• www.mysql.com
43
I
N
F
O
R
M
A
T
I
K
Datenschutz
44
• Anknüpfungspunkt: www.schober.de
I
N
F
O
R
M
A
T
I
K
Datenschutz
45
• Ein Blick ins Bundesdatenschutzgesetz:
(http://bundesrecht.juris.de/bdsg_1990/ )
I
N
F
O
R
M
A
T
I
K
Vernetzung des Themas - Datenschutz
46
• Datenbank-basierte Umfrage in der Schule
 Wie können durch Verknüpfen (eigentlich harmloser) Daten neue
Informationen gewonnen werden?
I
N
F
O
R
M
A
T
I
K
Wahlfach Informatik im Leibniz-Gymnasium Pirmasens
47
Fragen