Folien-Präsentation

Informationssysteme / Datenbankabfragen
Thomas Mohr
I
N
F
O
R
M
A
T
I
K
K
Agenda
T
I
Datenbanken – Wozu?
A
Abfragesprachesprache SQL
R
M
Verwaltung MySQL
O
Datenbankmodellierung
N
F
Themenvernetzung (DB-Zugriff, etc.)
I
Ausblick
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
M
Informationssystem
Datenbanksystem
DBS
O
Erfassung
Speicherung
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:
„Städte in Deutschland – Einwohner, geografische Lage“
A
T
Schüler nutzen ein Informationssystem
Schüler stellen automatisch Informationen unterschiedlich dar
N
F
O
R
M
Mainz
196.000
50°
50° Nord
8° 16‘ Ost
Landau
43000
49,19
8,12
I
Listen
Name
Einwohner Breite
Länge
Mainz
199000
50
8
Landau
43000
49
8
Tabellen
Tabellen bieten schon „von Hand“ Vorteile:
leichter zu ergänzen (neue Spalte)
Summenzeile
…
6
Motivation - Datenbank
T
I
K
• Idee: Sammlung der gefundenen Städte in einem Tabellenblatt
(OpenOffice Calc / MS Excel) auf einem zentralen Laufwerk
Es treten typische Probleme auf:
I
N
F
O
R
M
A
Es kann immer nur ein Benutzer die Datei öffnen.
Daten können von jedem geändert / gelöscht werden.
Keinerlei Konsistenzprüfung der eingegebenen Daten.
Abhilfe: Nutzung eines Datenbanksystems…
MS Access ist in dieser Hinsicht schon ungeeignet.
7
Software-Architekturen: „Standalone“-Programm
K
• z.B.
O
R
M
A
T
I
selbst programmierte Schülerdatei
in Delphi programmierte Übersicht von Länderinformationen
PC
I
N
F
Anwendungsprogramm
8
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
9
PC
• z.B.
Outlook und Exchange-Server
„einfache“, datenbank-basierte Schülerverwaltung
Anwendungsprogramm
Anwendungsprogramm
Client
I
N
F
O
R
M
A
T
I
K
Software-Architekturen - Client-Server
(Datenbank)Server
Server
10
• z.B.
Outlook und Exchange-Server
„einfache“, datenbank-basierte Schülerverwaltung
Anwendungsprogramm
Client
I
N
F
O
R
M
A
T
I
K
Software-Architekturen - Client-Server
(Datenbank)Server
Server
11
Vorteile
•
Datenbank übernimmt
„Standardaufgaben“
Daten zentral vorhanden
Erweiterungen relativ einfach
(für mehrere Benutzer / Programme)
Nachteile
Installation von Software auf
allen Clients notwendig
Weitere „Sprache“ zum
Datenbankzugriff
Anwendungsprogramm
Client
I
N
F
O
R
M
I
T
•
A
K
Software-Architekturen - Client-Server
(Datenbank)Server
Server
12
• Eine moderne 3-schichtige Webarchitektur…
T
I
K
Software-Architekturen – Webarchitektur
A
Client
(Browser)
Anwendungsprogramm
Webserver (z.B. Apache und PHP)
Server
I
N
F
O
R
M
Webclient
Datenbankserver
(z.B. MySQL)
Server
13
• Eine moderne 3-schichtige Webarchitektur…
T
I
K
Software-Architekturen – Webarchitektur
A
Client
(Browser)
O
R
M
Webclient
(z.B. Apache und PHP)
Server
I
N
F
Webserver
Datenbankserver
(z.B. MySQL)
Server
14
•
Vorteile
•
Keine Installation von
zusätzlicher Software beim Client
Nachteile
http-Protokoll ohne
Sessionverwaltung
T
I
K
Software-Architekturen – Webarchitektur
A
Client
(Browser)
Webserver
(z.B. Apache und PHP)
Server
I
N
F
O
R
M
Webclient
Datenbankserver
(z.B. MySQL)
Server
15
• 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
F
O
R
M
Clients
I
N
Webserver
Datenbankserver
16
• Alle drei Schichten sind auf einem Rechner!
A
T
I
K
… und zum Testen / für die Fortbildung?
Internet
Explorer
Client
Webserver
Webserver
Datenbankserver
Apache
I
N
F
O
R
M
Clients
Datenbankserver
17
MySQL
I
K
Das andere Extrem – eine Web Farm
T
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
18
• Man unterscheidet verschiedene Arten von Datenbanken:
I
K
Arten von Datenbanken
Die Datenelemente sind baumartig miteinander verbunden
• Vernetzte Datenbanken
Die Datenelemente sind mit Zeigern zu einem Netz miteinander
verbunden
I
N
F
O
R
M
A
T
• Hierarchische Datenbanken
• Beide Formen waren vor allem bei Großrechnern im Einsatz und
werden zunehmend von relationalen Datenbanken abgelöst
19
• Relationale Datenbanken
Die Daten werden in Form von Tabellen gespeichert
Zwischen den Tabellen werden Beziehungen aufgebaut (Relationen)
T
I
K
Arten von Datenbanken
Spalte (Merkmal, Attribut)
A
Datenwert (Attributwert)
M
Einwohner
Breite
Berlin
Mainz
Paris
Speyer
3458763
184752
2181300
50600
52,52 13,41
50,00
8,27
48,86
2,35
49,31
8,43
Laenge
Land
Deutschland
Deutschland
Frankreich
Deutschland
F
O
Name
R
Ort
Datensatz (Tupel)
I
N
Attributklasse
• Empfehlung:
20
Einheitliche Begriffe nutzen,
nicht zu nah an der mathematischen Betrachtung (Relationenalgebra)
I
N
F
O
R
M
A
T
I
K
Arten von Datenbanken
21
• Relationale Datenbanken – typische Vertreter
Oracle
IBM (DB/2)
Microsoft SQL Server (Access ?)
Informix
MySQL
OpenSource
PostGreSQL
Strukturierte Speicherung von Daten
Verteilter, gleichzeitiger Zugriff mehrerer Benutzer / Programme
Verwaltung von Zugriffsrechten
A
Atomicity
R
ACID – Prinzip
M
T
I
K
Was bietet mir eine Datenbank?
(Idee: Analogie zu Bank-Transaktionen)
• Transaktionen (Änderungen an der Datenbank) werden ganz oder gar
nicht durchgeführt.
N
F
O
Consistency
• Eine Transaktion führt wieder zu einem konsistenten (gültigen) Zustand
der Datenbank.
Isolation
• Transaktionen beeinflussen sich nicht gegenseitig.
I
Durability
• Eine Transaktion ist dauerhaft gespeichert, auch gegen Systemabstürze
gesichert.
Im Unterricht gut durch Rollenspiele zu veranschaulichen.
22
• A – Atomicity
• Bsp.: Ein Kontosystem mit nicht überziehbaren Konten
Bob überweist Alice 500 €.
Die Transaktion bricht nach der Abbuchung von Bobs Konto ab.
O
R
M
A
T
I
K
ACID-Prinzip
Alice
F
Bob
I
N
700 €
23
500 €
300 €
200 €
Die Transaktion darf nur „ganz oder gar nicht“ stattfinden.
Konkret: Das Geld darf unterwegs nicht „verschwinden“
• C – Consistency
• Bsp.:
Bob will Alice 1000 € überweisen.
Die Transaktion überzieht sein Konto, was nicht erlaubt ist.
O
R
M
A
T
I
K
ACID-Prinzip
Alice
F
Bob
N
700 €
1000 €
1300 €
I
-300 €
300 €
Jede Transaktion muss die Datenbank in einem konsistenten
(den definierten Regeln entsprechenden) Zustand hinterlassen.
(insbesondere bei der Konsistenz von Schlüsselbeziehungen, s.u.)
24
• I – Isolation
• Bsp.:
Bob überweist Alice 400 € und gleichzeitig an Carol 500 €.
Jede Transaktion für sich ist in Ordnung, zusammen überziehen sie
das Konto.
O
R
M
A
T
I
K
ACID-Prinzip
Bob
Alice
F
Carol
600 €
500 €
700 €
400 €
-200 €
I
N
100 €
25
Jede Transaktion muss so ablaufen, dass parallel ablaufende
Transaktionen sie nicht stören können.
(zumindest logisch nacheinander ablaufen – serialisierbar)
300 €
700 €
K
Agenda
T
I
Datenbanken – Wozu?
A
Abfragesprachesprache SQL
R
M
Verwaltung MySQL
O
Datenbankmodellierung
N
F
Themenvernetzung (DB-Zugriff, etc.)
I
Ausblick
26
• An der Schnittstelle nach außen bietet das Datenbanksystem
(DBS) Sprachen für folgende Zwecke:
Datenabfrage und -manipulation (DML)
Verwaltung der Datenbank und Definition der Datenstrukturen (DDL)
Berechtigungssteuerung (DCL)
• Bei relationalen DBS ist dies alles in der Sprache SQL vereint.
Beschränkung in der Schule im Wesentlichen auf Abfragesprache.
27
GF - Lehrplan
I
N
F
O
R
M
A
T
I
K
Sprachen zur Datenbankverwaltung
• Starten Sie den Datenbankserver und den Webserver
I
N
F
O
R
M
A
T
I
K
MySQL – Jetzt wird es (endlich) praktisch…
• Das Datenbanksystem bietet einen Service für andere Rechner an
Die Windows-Firewall kann dies melden.
Der Port muss freigegeben werden.
28
I
K
Die erste Datenbank importieren
• Die Administration von MySQL funktioniert selbst schon am
einfachsten über den Browser (oder Admin-Button in Control Panel)
T
Webarchitektur
A
Browser
R
M
Client
Webserver
N
F
O
Apache
I
MySQL
Datenbankserver
29
http://localhost/phpmyadmin
• Legen Sie eine neue Datenbank „terra1“ an.
• Wechseln Sie zur Datenbank (links), wählen Sie den Punkt
„Importieren“ und suchen die Datei „terra1.sql“
I
N
F
O
R
M
A
T
I
K
Die erste Datenbank importieren
30
• Klicken Sie auf „Struktur“: u.a. wird die Tabelle „ort“ angezeigt.
Lassen Sie sich den Inhalt der Tabelle anzeigen.
I
N
F
O
R
M
A
T
I
K
Die erste Tabelle…
Tipp: Ändern Sie die Einstellungen von phpmyadmin, so dass nur die Icons
angezeigt werden!
31
• SQL = Structured Query Language.
bezeichnet eine Sprache zur Kommunikation mit Datenbanken.
ist international genormt und wird von vielen DBS verstanden.
wird im Folgenden zur Formulierung von Abfragen eingesetzt.
A
T
I
K
Abfragen mit SQL
SELECT
FROM
WHERE
ORDER
[Spalten]
[Tabelle]
[Bedingung]
BY [Attribute];
I
N
F
O
R
M
• Syntax einer (einfachen) SQL-Abfrage:
• Die WHERE- und die ORDER BY-Klausel sind optional.
32
Ort
Name
Einwohner
Breite
Berlin
Mainz
Paris
Speyer
…
3458763
184752
2181300
50600
…
52,52 13,41
50,00
8,27
48,86
2,35
49,31
8,43
…
…
Laenge
Millionenstädte ?
Land
Deutschland
Deutschland
Frankreich
Deutschland
…
I
N
F
O
R
M
T
SELECT Name, Einwohner, Land
FROM Ort
WHERE Einwohner > 1000000
A
I
K
Datenbankzugriff mit SQL - Ein erstes Beispiel
35
Ergebnistabelle
Name
Einwohner
Land
Berlin
Paris
…
3458763
2181300
… …
Deutschland
Frankreich
• Bedingungen mit Textattributen:
Name = 'Paris'
Name LIKE 'P%'
Name LIKE 'A_len'
(Potsdam, Peine, Pirmasens)
(Aalen, Ahlen)
A
T
I
K
SQL – WHERE
Stufe=7
Stufe<>7
Stufe<7
Stufe>7
Stufe<=7
Stufe>=7
Stufe BETWEEN 7 AND 10
(gleich 7)
(ungleich 7)
(kleiner 7)
(größer 7)
(kleiner gleich 7)
(größer gleich 7)
(zwischen 7 und 10)
I
N
F
O
R
M
• Bedingungen mit Zahlattributen:
36
K
SQL – WHERE
• Vergleich auf Nullwert (kein Attributwert angegeben):
• Logische Verknüpfungen:
NOT (Land='Deutschland')
(Land nicht Deutschland)
R
M
A
T
I
Breite IS NULL
(Land='Deutschland') AND (Einwohner>100000)
I
N
F
O
(Millionenstädte in Deutschland)
37
(Land='Deutschland') OR (Land='Schweiz')
(Orte in Deutschland und Schweiz)
• Das Abfrageergebnis kann sortiert werden:
I
K
SQL – ORDER BY
[Spalten]
[Tabelle]
[Bedingung]
BY [Attribute];
• Die Sortierung geschieht nach dem angegebenen Attribut.
• Bei mehreren Sortierattributen wird nach dem zweiten (dritten...)
sortiert, sobald die Werte des ersten (zweiten...) identisch sind.
• Absteigende Sortierung mit DESC
I
N
F
O
R
M
A
T
SELECT
FROM
WHERE
ORDER
• Beispiele:
SELECT * FROM Ort ORDER BY Name
SELECT * FROM Ort ORDER BY Land, Einwohner DESC
38
•
Öffnen Sie das SQL-Fenster
1. Geben Sie alle Länder aus!
SELECT *
FROM Land
I
N
F
O
R
M
A
T
I
K
Die ersten SQL Befehle
39
2. Geben Sie alle Länder aus:
Name, Einwohner, Hauptstadt
Sortierung nach Einwohner absteigend
SELECT Name, Einwohner, Hauptstadt
FROM Land
ORDER BY Einwohner DESC
3. Welche Länder liegen in Asien und Australien?
WHERE Kontinent='Asien' OR
Kontinent='Australien'
4. Welche Länder haben zwischen 10 und 100 Mio. Einwohner?
Absteigend nach Einwohner sortiert
WHERE Einwohner BETWEEN 10 AND 100
ORDER BY Einwohner DESC
5. Welche Länder haben einen „Arm“ oder ein „Bein“ im Namen?
N
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: terra1
I
WHERE name like '%bein%'
OR name like '%arm%'
40
A
T
I
K
Hilfsfunktionen
• Wichtig sind vor allem Funktionen zur Manipulation von Strings
und Datumsangaben, z.B.
DATEDIFF(D1, D2)
NOW()
…
Differenz (Tage)
Aktuelle Zeit/Datum
(SELECT CURDATE())
CONCAT(S1,S2,…)
LOWER(),UPPER()
CHAR_LENGTH()
MID(str,pos,len)
TRIM(s), LTRIM, RTRIM
…
Verbinden von Strings
In Groß-/Kleinbuchstaben umwandeln
Länge in Zeichen
String ausschneiden
Abschneiden von Leerzeichen
• Weitere Funktionen online in der Hilfe zu MySQL!
I
N
F
O
R
M
• Wichtige Stringfunktionen
SQL ist hier nicht standardisiert
Die Funktionen sind bei anderen DBS oft unterschiedlich
41
5. Geben Sie die Länder in folgender Form aus:
CONCAT(UPPER(Name),' - ',Hauptstadt) AS Land,
round(Einwohner) AS "Mio. Einwohner"
A
T
I
K
Aufgaben, Datenbank: terra1
6. Welche Länder gibt es in Europa mit mehr als 20 Mio Einwohner?
WHERE Kontinent = 'Europa'
AND Einwohner > 20
Frankreich??
Problem: Europa wird unterschiedlich geschrieben!
I
N
F
O
R
M
Spalten können mit „AS“ umbenannt werden
Lösung: Auslagerung in eine eigene Tabelle
42
• Wozu dient die Spalte LNR in der Land-Tabelle ?
• … ein anderes Beispiel:
Suche nach der Hauptstadt „Berlin“
Man benötigt noch das
Land als Suchhilfe.
Suche nach der Hauptstadt „Washington“
I
N
F
O
R
M
A
T
I
K
Exkurs: Primärschlüssel
43
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
45
Schlüsselattribut aus Kontinent
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
A
T
I
K
Relationale Datenbanken – Beziehungen
F
O
R
M
Primärschlüssel aus Kontinent
• Die Verknüpfung erfolgt grundsätzlich dadurch,
dass ein Fremdschlüssel der einen Tabelle
auf den zugehörigen Primärschlüssel der anderen Tabelle zeigt.
I
N
• Vorteile:
46
Daten werden jeweils nur in einer Tabelle gespeichert.
Datenänderungen sind leichter durchzuführen.
Strukturänderungen (z.B. das Hinzufügen der Kontinentfläche) lassen sich
meist mit geringem Aufwand realisieren.
Die Struktur lässt flexiblere Abfragen zu.
T
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.
M
K
I
• Müssen in SQL Daten aus mehreren Tabellen entnommen werden,
so werden sog. „Joins“ gebildet.
A
SQL – einfache Joins
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
Land
47
1. Cross-Join („jede Zeile mit jeder“)
Land
T
A
M
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
LNR
48
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
SELECT *
FROM Land, Kontinent
I
K
SQL – einfache Joins
2. Einschränken auf „passende“ Datensätze.
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
A
T
I
K
SQL – einfache Joins
49
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).
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
I
N
F
O
R
M
A
T
I
K
SQL – einfache Joins
50
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
In der Realität versucht das
DBMS, durch „geschicktes“
Vorgehen die Datenmenge
schon früher zu reduzieren.
51
1. Geben Sie alle Kontinente mit ihren Ländern aus:
Name der Kontinente und Länder
Sortierung nach Kontinent absteigend
SELECT
FROM
WHERE
ORDER
Kontinent.Name, Land.Name
Kontinent, Land
Kontinent.KNR = Land.KNR
BY Kontinent.Name DESC
2. In welchen Kontinenten gibt es Länder mit mehr als 100 Mio.
Einwohner?
SELECT
FROM
WHERE
AND
DISTINCT
Kontinent.Name
Kontinent.Name
Land, Kontinent
Land.KNR = Kontinent.KNR
Land.Einwohner > 100
I
N
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: terra2
Problem: Es werden Duplikate angezeigt
Lösung: DISTINCT-Anweisung
52
• 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
53
• Ein erstes „E/R-Modell“ (Entity/Relationship)
eigentlich intuitiv zu lesen!?
M
A
T
I
K
Bisheriges „Schema“ der Datenbank
Teil von
Land
Ort
O
R
Hauptstadt
I
N
F
Teil von
54
Kontinent
Finden Sie die Stelle in der
Datenbank terra3, an der
die „Hauptstadt“
abgespeichert ist?
1. Geben Sie alle Orte mit ihren Ländern aus:
Name der Orte und Länder
Sortierung nach Einwohnerzahl absteigend
T
I
K
Aufgaben, Datenbank: terra3
Ort.Name, Ort.Einwohner, Land.Name AS Land
Ort, Land
Ort.LNR = Land.LNR
BY Ort.Einwohner DESC
R
M
A
SELECT
FROM
WHERE
ORDER
2. Geben Sie alle Länder mit ihrer Hauptstadt aus!
SELECT
FROM
WHERE
AND
ORDER
Kontinent.Name AS Kontinent, Land.Name, Ort.Name AS Hauptstadt
Ort, Land, Kontinent
Ort.ONR = Land.HauptONR
Land.KNR = Kontinent.KNR
BY Kontinent.Name, Land.Name
I
N
F
O
Sortierung nach Kontinent und Land.
55
• Soll in SQL auf eine Tabelle mehrfach zugegriffen werden, so
kann dies mit Alias-Namen geschehen:
Es sollen alle Städte mit mehr als 1 Mio. Einwohner ausgegeben
werden; dabei auch das zugehörige Land mit Hauptstadt.
logische Struktur:
Ort
Teil von
Land
Hauptstadt
Ort
I
N
F
O
R
M
A
T
I
K
SQL – Tabellen-Alias
56
SELECT
FROM
WHERE
AND
AND
o.Name AS Stadt, l.Name AS Land, hs.Name
Ort o, Land l, Ort hs
o.LNR = l.LNR
Der Alias-Name für
l.HauptONR = hs.ONR
Land ist nicht
o.Einwohner>10000000
notwendig
(verkürzt die
Abfrage)
• 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.
57
• 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.
58
Lösung: Auslagerung der Zuordnung in eine eigene Tabelle
• Es sollen Flüsse gespeichert werden.
Flüsse fließen durch Orte.
Manche Orte werden von mehreren Flüssen durchflossen
(z.B. Koblenz)
F
O
R
M
A
T
I
K
Erweiterung der Datenbank
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
I
N
Stadtfluss
FNR
ONR
ELB
RHE
RHE
MOS
HAMBUR
KOBLEN
KARLSR
KOBLEN
59
I
K
Struktur der Datenbank terra4
1
n
T
Teil von
Ort
n
Teil von
N
I
60
1
Hauptstadt
1
n
durchfließt
1
m
F
O
R
M
A
Land
Kontinent
Fluss
1. Welche Orte liegen an der Donau?
SELECT
FROM
WHERE
AND
AND
o.Name
Ort o,
o.ONR
sf.FNR
f.Name
Stadtfluss sf, Fluss f
= sf.ONR
= f.FNR
= 'Donau'
2. Welche Länder durchfließt die Donau?
SELECT
FROM
WHERE
AND
AND
AND
DISTINCT l.Name
Land l, Ort o, Stadtfluss sf, Fluss f
l.LNR = o.LNR
o.ONR = sf.ONR
sf.FNR = f.FNR
f.Name = 'Donau'
3. Welche Flüsse fließen durch Deutschland?
SELECT
FROM
WHERE
AND
AND
AND
I
N
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: terra4
61
DISTINCT
Fluss f,
f.FNR =
sf.ONR =
o.LNR =
l.Name =
f.Name, f.Laenge
StadtFluss sf, Ort o, Land l
sf.FNR
o.ONR
l.LNR
'Deutschland'
• 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
62
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
ONR
Name
AALBOR
KOPENH
BERLIN
DUESSE
MAINZ
TALLIN
Dänemark
Dänemark
Deutschland
Deutschland
Deutschland
Estland
2
Name
Anzahl
Dänemark
Deutschland
Estland
2
3
1
3
1
63
1. Wie viele Staaten haben Megacities (> 5 Mio. Einwohner) als
Hauptstadt?
SELECT
FROM
WHERE
AND
COUNT(*)
Land L, Ort O
O.ONR = L.HauptONR
O.Einwohner > 5000000
A
T
I
K
Aufgaben, Datenbank: terra4
I
N
F
O
R
M
GROUP BY entfällt,
wenn „nur“ gezählt
wird.
64
2. Zählen Sie die Staaten pro Kontinent!
Geben Sie die Kontinente nach der Anzahl absteigend aus.
SELECT
FROM
WHERE
GROUP
ORDER
K.Name, COUNT(*) AS Anzahl
Land L, Kontinent K
L.KNR = K.KNR
BY K.Name
BY Anzahl DESC
3. Wie viele Einwohner haben die Länder pro Kontinent im Schnitt?
SELECT
FROM
WHERE
GROUP
ORDER
k.Name AS Kontinent, AVG(l.Einwohner) AS Schnitt
Kontinent k, Land l
k.KNR = l.KNR
BY k.Name
Weitere BuiltInBY k.Name
Funktionen:
MAX, MIN, SUM,…
4. Welche Städte liegen an mehr als einem Fluss?
O
R
M
A
T
I
K
Aufgaben, Datenbank: terra4
o.Name, COUNT(*) AS Anzahl
Ort o, StadtFluss sf
o.ONR = sf.ONR
BY o.Name
COUNT(*)>1
BY o.Name
I
N
F
SELECT
FROM
WHERE
GROUP
HAVING
ORDER
65
K
Struktur der kompletten Datenbank „miniterra“
I
Sprache
T
n
benachbart
m
m
1
n
Teil von
Land
n
Ort
1
Hauptstadt
1
n
F
R
n
O
M
A
gesprochen
durchfließt
m
1
I
N
Teil von
Kontinent
66
Fluss
n
1
mündet
• Welche Städte liegen an mehr als einem Fluss?
Welche Flüsse sind es jeweils?
SELECT
FROM
WHERE
AND
AND
o.Name, f.Name
Ort o, StadtFluss sf1, Fluss f
o.ONR = sf1.ONR
f.FNR = sf1.FNR
EXISTS (SELECT NULL FROM StadtFluss sf2
WHERE sf1.FNR<>sf2.FNR AND sf2.ONR=o.ONR)
ORDER BY o.Name
R
M
A
T
I
K
Für Experten…
O
• Welche Länder grenzen an Deutschland?
l1.Name, l2.Name
Nachbarland n, Land l1, Land l2
(n.LNR1='D' OR n.LNR2='D')
l1.LNR = n.LNR1
l2.LNR = n.LNR2
I
N
F
SELECT
FROM
WHERE
AND
AND
67
• Welche Länder grenzen an Deutschland?
Geben Sie nur die Nachbarländer aus!
SELECT
FROM
WHERE
AND
AND
ORDER
IF(l1.LNR='D',l2.Name, l1.Name) AS "Direkte Nachbarn"
Nachbarland n, Land l1, Land l2
(n.LNR1='D' OR n.LNR2='D')
l1.LNR = n.LNR1
l2.LNR = n.LNR2
ACHTUNG:
BY "Direkte Nachbarn"
Nicht genormt!
R
M
A
T
I
K
Für Experten…
SELECT
FROM
WHERE
AND
fc.Name
Fluss fp, Fluss fc
fp.FNR = fc.ZielFNR
fp.Name = 'Rhein'
I
N
F
O
• Welche Flüsse münden in den Rhein?
Selbstreferenz der
Tabelle Fluss
68
I
K
u.v.m.
• SQL bietet noch einige weitere (hier nicht behandelte)
Möglichkeiten:
• 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
OUTER JOINS:
69
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
70
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
I
K
Verwaltung des Datenbanksystems
• In einem Datenbanksystem müssen Rechte für verschiedene
Benutzer verwaltet werden:
• Jeder kann auf alle Datenbanken zugreifen und diese verändern
Mehrere Schüler sollen sich einen Datenbankserver „teilen“, dabei
sich aber nichts gegenseitig „kaputtmachen“
• Die Rechte können sehr fein vergeben werden, am Wichtigsten
sind:
SELECT:
Benutzer kann Tabellen auslesen
INSERT/UPDATE/DELETE: Tabelleninhalte dürfen verändert werden
CREATE/ALTER/…:
Tabellenstruktur darf verändert werden
I
N
F
O
R
M
A
T
MySQL arbeitet nach der Installation ohne Schutz des Administrators
71
• Das XAMPP-Paket erlaubt das Einrichten des AdministratorPassworts auf einfache Weise:
Rufen Sie http://localhost auf.
Klicken Sie auf Sicherheitscheck.
Im Link unter der Tabelle können wichtige
Sicherheitslücken geschlossen werden.
• Setzen Sie zumindest das Passwort für den
Administrator (root) und stellen Sie die Anmeldung um
auf http.
I
N
F
O
R
M
A
T
I
K
Grundlegenden Schutz einrichten
dann:
MySQL Server neu starten
(XAMPP Control Panel)
72
• phpMyAdmin in Browser starten
Menüpunkt „Rechte“
Benutzer „root“ auswählen und bearbeiten
I
N
F
O
R
M
A
T
I
K
MySQL – Passwort für Administrator ändern
Passwort eingeben und speichern
Beim nächsten Aufruf von phpMyAdmin ist die Eingabe des neuen
Passworts notwendig
73
• Über den Reiter „Rechte“ können weitere Benutzer angelegt
werden und Rechte auf den Datenbanken vergeben werden
z.B. für einen zentralen Datenbankserver sollen pro Schülergruppe
„exklusive“ Datenbanken zur Verfügung gestellt werden
I
N
F
O
R
M
A
T
I
K
Benutzer / Rechte vergeben
74
Da phpmyadmin
immer lokal auf
dem Server läuft…
• Einem Benutzer können aber auch Rechte (z.B. nur Leserecht) auf
zentralen Datenbanken wie miniterra vergeben werden:
I
N
F
O
R
M
A
T
I
K
Benutzer / Rechte vergeben
75
• Jetzt kann der Benutzer über http://Rechnername/phpmyadmin
oder über http://IP-Adresse/phpmyadmin vom Schulnetzwerk auf
den Server zugreifen.
Problem: Neues Rechte-System in aktuelleren XAMPP-Versionen
I
N
F
O
R
M
A
T
I
K
Problem: XAMPP-Rechte
Manuelles Anpassen der Konfigurationsdatei httpd-xampp.conf nötig.
76
• Im Verzeichnis \xampp\apache\conf\extra findet sich die
Konfigurationsdatei httpd-xampp.conf
Sicherungskopie anlegen!
Im letzten Abschnitt muss der Zugriff wieder erlaubt werden, indem
das Verbot „auskommentiert“ wird.
I
N
F
O
R
M
A
T
I
K
Anpassen der Konfigurationsdatei httpd-xampp.conf
77
Danach muss der Webserver mit XAMPP Control Panel neu gestartet
werden.
Dies ist die „quick and dirty“-Methode
ohne Beachtung eines Sicherheitskonzepts
Die Anpassung ist für ein „öffentliches“
System im Internet absolut ungeeignet!
1. Schützen Sie den Administrator-Zugang für ihren mySQL-Server
wie zuvor beschrieben.
2. Legen Sie einen Benutzer „ifb“ und Passwort „Speyer“ an. Dieser
soll die Leserechte (SELECT) auf der Datenbank „miniterra“
erhalten.
O
R
M
A
T
I
K
Benutzer anlegen
I
N
F
3. Passen Sie die Konfigurationsdatei httpd-xampp.conf an.
78
4. Versuchen Sie, mit diesem Benutzer auf die Datenbank des
Nachbarn / der Nachbarin zuzugreifen.
• Zur Vereinfachung existiert ein PHP-Skript, das eine erste
Umgebung für den Unterricht einrichtet:
Verzeichnis terra_install in das htdocs-Verzeichnis des XAMPP-Pakets
kopieren.
http://localhost/terra_install aufrufen.
• Das Skript
legt die Datenbanken terra1…4 und miniterra an.
erzeugt Benutzerkonten und Datenbanken für die Schüler mit
entsprechenden Rechten.
I
N
F
O
R
M
A
T
I
K
Skript zur Anlage der Datenbanken
79
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
80
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Schulverwaltung
T
I
K
Ein neues Informationssystem… Anforderungskatalog
A
•
•
O
R
M
•
•
N
F
•
I
•
Die Benutzer können eine Liste aller Lehrer mit ihrem
Dienstkürzel abrufen.
Für jede Klasse ist eine Liste der durchgeführten
Klassenfahrten mit dem leitenden Lehrer ersichtlich.
StD L. Lämpel übernimmt dieses Jahr die 7a als
Klassenleiter. Die Klasse kann das im Internet schon in den
Ferien erfahren.
Eine Suche ist möglich über Klassenstufe, Lehrer oder Fach.
StR A. Kribich hat sich fortgebildet und darf ab diesem Jahr
neben Mathematik und ev. Religion auch Informatik
unterrichten. Die Fachschaftsliste muss aktualisiert werden.
Nachdem LiA Sch. Merz in Rente gegangen ist, geht die
Fachbereichsleitung in Sport an OStR‘ G. Lenk.
81
I
N
F
O
R
M
A
T
I
K
Modellbildung
82
• Um ein Informationssystem zu erstellen, muss ein Ausschnitt der
realen Welt („Miniwelt“) im Computersystem erfasst werden.
Miniwelt
Datenmodell
• Die Benutzer können eine
Liste aller Lehrer mit ihrem
Dienstkürzel abrufen.
• StD L. Lämpel übernimmt
dieses Jahr die 7a als
Klassenleiter. Die Klasse kann
das im Internet schon in den
Ferien erfahren.
• Eine Suche ist möglich über
Klassenstufe, Lehrer oder
Fach.
• …
Unstrukturierte
Informationen über die
Miniwelt
?
Repräsentation der
Informationen als
strukturierte Daten
• Um aus einer Beschreibung einer Miniwelt das Datenmodell einer
Datenbank abzuleiten, bietet es sich an, zunächst ein grafisches
Konzept der Daten zu erstellen.
Text
I
N
F
O
R
M
A
T
I
K
Modellbildung
Externe
Sicht
Konzeptionelle
Sicht
Internes
Modell
(z.B. relationales
Datenmodell)
83
R
M
A
T
I
K
Konzeptionelles Modell – E/R-Diagramme
• E/R-Diagramme dienen dazu, das konzeptionelle Modell des
Informationssystems zu erstellen. Es werden zwei Konstrukte
verwendet:
Entitätstypen
Beziehungstypen (Relationships)
• Eine Entität ist ein bestimmtes Objekt der realen Welt oder
unserer Vorstellung
• Entitäten mit gleichen Eigenschaften werden zu Entitätstypen
zusammengefasst
Symbol: Rechteck
I
N
F
O
z.B. eine Person, ein Gegenstand, ein Ereignis
Lehrer
84
K
Entität:
Entitätstyp:
Lehrer Lämpel, Kürzel Lä
Menge aller Lehrer mit den Merkmalen Vorname,
Nachname, Kürzel, …
Primärschlüssel: Kürzel (?) (oder ein künstlicher Schlüssel)
Kürzel:
Name:
Vorname:
Titel:
Lä
Lämpel
Ludwig
StD
Kürzel:
Name:
Vorname:
Titel:
Kr
Kribich
Alfred
StR
Kürzel:
Name:
Vorname:
Titel:
Le
Lenk
Gertrud
OStR'
Entitätstyp
„Lehrer“
I
N
F
O
R
M
A
T
• Beispiel Entitätstyp:
I
E/R-Diagramme - Entitätstyp
3 Entitäten
85
I
K
E/R-Diagramme - Beziehungstyp
• Gleichartige Beziehungen zwischen Entitäten werden als ein
Beziehungstyp zwischen den Entitätstypen definiert.
• Beispiel:
Ein Lehrer führt Klassenfahrten durch.
Kuerzel:
Name:
Lä
Lämpel
Stufe:
Teil:
8
a
Kuerzel:
Name:
Kr
Kribich
Stufe:
Teil:
11
M1
Kuerzel:
Name:
Me
Merz
Stufe:
Teil:
12
m3
Beziehungen
I
N
F
O
R
M
A
T
Symbol: Raute
86
Lehrer
Klassenfahrt
Klasse
Entitätstyp
Beziehungstyp
Entitätstyp
• Die Beziehung „Klassenfahrt“ könnte man auch als eigenen
Entitätstyp modellieren.
Modellierung ist oft nicht eindeutig, es gibt je nach Ansicht mehrere
sinnvolle Modelle für eine Miniwelt.
A
T
I
K
Modellierung oft nicht eindeutig
Klasse
O
R
M
Lehrer
nimmt
teil
Klassenfahrt
I
N
F
führt
durch
87
• Die Eigenschaften aller Entitäten und Beziehungen eines
Entitätstyps bzw. eines Beziehungstyps werden mit Hilfe von
Attributen erfasst.
Symbol: Ellipse
• Beispiel:
R
M
A
T
I
K
E/R-Diagramme - Attribute
Lehrer
Klasse
F
O
Klassenfahrt
Name
Datum
I
N
Kürzel
Teil
identifizierend
Vorname
beschreibend
88
Stufe
Ziel
Anzahl
• Ein Beziehungstyp wird durch die Kardinalität genauer bezeichnet:
1:n- Beziehung am Beispiel: Klassenzuordnung der Schüler
Name:
9a
Name: Müller
Vorname: Yvonne
Name:
9b
Name: Meier
Vorname: Jan
Name:
10d
Name: Dietz
Vorname: Nicole
Name: Seiler
Vorname: Manfred
O
R
M
A
T
I
K
E/R-Diagramme – Kardinalität
Schüler
I
N
F
Klasse
1
Klasse
n
Schüler
angehören
Ein Schüler gehört einer Klasse an. Einer Klasse gehören mehrere Schüler an.
89
T
LK: ja
Name: Müller Vorname: Yvonne
Fach: Deutsch
LK: ja
Name: Meier
Vorname: Jan
Fach: Englisch
LK: ja
Name: Dietz
Vorname: Nicole
R
Fach: Mathe
A
• n:m – Beziehung am Beispiel:
Kurszuordnung von Schülern in der MSS
M
I
K
E/R-Diagramme – Kardinalität
O
Name: Seiler Vorname: Manfred
Schüler
N
F
Kurs
n
besucht
m
Schüler
I
Kurs
Ein Kurs hat mehrere Schüler. Ein Schüler nimmt an mehreren Kursen teil.
90
• Die Kardinalitäten (1:1, 1:n, n:m) geben nur an, wie viele
Entitäten maximal miteinander verbunden sind.
1
Klasse
angehören
n
Schüler
Bsp.: Ein Schüler ist maximal einer Klasse zugeordnet.
Einer Klasse sind maximal n Schüler zugeordnet.
• Die Kardinalität kann zusätzlich eingeschränkt werden.
N
F
O
R
M
A
T
I
K
E/R-Diagramme – Kardinalität
1
I
Klasse
angehören
[1,1]
n
Schüler
[8,30]
Bsp.: Eine Klasse hat minimal 8 und maximal 30 Schüler.
91
I
K
E/R-Diagramme – Reflexive Beziehungstypen
• Beziehungen können auch auf dem gleichen Entitätstyp gelten,
Selbstbeziehung oder Reflexive Beziehung.
T
Bsp.: Heirat
Vorname: Klaus
Name: Bach
Vorname: Stefan
Name: Meier
Vorname: Sabine
Name: Bach-Meier Vorname: Petra
Name: Hurtig
Vorname: Hans
Personen
N
F
O
R
M
A
Name: Meier
I
Personen
oder n:m ?
92
1
1
Heirat
• Im ersten Teil wurde eine bestehende Datenbank sukzessive
erweitert und vor allem in mehrere Tabellen zerlegt, um
Redundanzen zu vermeiden.
• Dieser Prozess kann in der sog. „Normalisierung“ formalisiert
werden.
Das Relationenschema wird dabei in die erste, zweite, dritte usw.
Normalform überführt.
Immer mehr Redundanzen werden vermieden.
Immer mehr Tabellen sind notwendig (und Anfragen werden
komplexer).
Prozess wird in der Realität nur bis zu einem gewissen Grad
durchlaufen.
I
N
F
O
R
M
A
T
I
K
Normalisierung von relationalen Schemata
Weitergehendes mit Beispielen in:
http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
94
K
Für jeden Entitätstyp eine Tabelle
T
• Transformation von Entitätstypen
I
Transformation E/R-Modell in relationales Schema
E/R-Modell
M
A
Schüler
Name
R
SNR
Schueler
I
SNR
95
Name
Vorname
Relationales Schema
N
F
O
Vorname
• Transformation von 1:n-Beziehungstypen
Fremdschlüssel wird auf Seite der „Kind“-Klasse hinzugefügt
(kann, aber muss nicht der Name des Primärschlüssels sein).
1
n
angehören
Schüler
M
Klasse
E/R-Modell
A
T
I
K
Transformation E/R-Modell in relationales Schema
R
Name
KNR
SNR
O
Stufe
Klasse
Stufe
SNR
Name
Vorname
KNR
I
KNR
Schueler
96
Relationales Schema
N
F
Vorname
• Transformation von n:m-Beziehungstypen
Einfügen eines Fremdschlüssels nicht möglich
Separate Tabelle mit zwei Fremdschlüsseln
n
besucht
m
Schüler
M
Kurs
E/R-Modell
A
T
I
K
Transformation E/R-Modell in relationales Schema
R
Name
KNR
SNR
Fehlstunden
O
Stufe
Kurs
I
KNR
97
Besucht
Stufe
KNR
SNR
Schueler
Fehlst.
SNR
Name Vorname
Relationales Schema
N
F
Vorname
• Transformation von 1:1-Beziehungstypen
z.B. das separate Speichern von sensiblen Daten zu Personen
Separate Tabelle, wobei dort Primärschlüssel auch Fremdschlüssel ist.
1
1
Zusatzdaten
SchülerPrivat
M
Schüler
E/R-Modell
A
T
I
K
Transformation E/R-Modell in relationales Schema
R
Vorname
SNR
Konfession
SNR
O
Name
Schueler
Name
Vorname
SNR Konfession
Herkunft
I
SNR
SchuelerPrivat
98
• DBDesigner4
http://fabforce.net/dbdesigner4/
• MySQL Workbench
http://www.mysql.com/downloads/workbench/
A
T
I
K
Tools für E/R-Diagramme
Mogwai ER-Designer
http://sourceforge.net/projects/mogwai/
TOAD Data Modeler
TOAD für MySQL
http://toadformysql.com/index.jspa
I
N
F
O
R
M
• Ungetestet:
100
• Oder doch einfach mit Office-Programmen…
Relationales Schema
N
F
Herkunft
„Schule“
•
Es werden alle LehrerInnen mit ihren Namen, Vornamen,
Kürzeln und Dienstgrad erfasst.
•
Die Benutzer können im Internet eine Liste der Klassen mit
ihren KlassenleiterInnen einsehen.
•
Es ist eine Suche nach den Fachschaften der Schule
möglich. Dabei werden auch alle LehrerInnen angezeigt, die
der Fachschaft angehören.
I
N
F
O
R
M
A
I
• Erstellen Sie ein E/R-Modell und dann das relationale Schema.
T
K
Aufgaben
101
I
T
•
In der Bibliothek müssen Bücher erfasst werden. Eine Suche
ist möglich über Sachgebiet, Autor, Titel, Erscheinungsort
und –jahr, Verlag.
M
•
Bei der Suche wird eine Liste aller verfügbaren Verlage
vorgeblendet.
•
Leser, die Bücher ausleihen wollen, müssen sich zuvor
registrieren.
•
Für ein Buch kann herausgefunden werden, ob es zur Zeit
ausgeliehen ist und von wem.
•
Um Schäden nachvollziehen zu können, können alle
vorherigen Ausleiher ermittelt werden.
•
Bei zu langer Ausleihe erfolgt eine Mahnung an den Leser.
Das muss vermerkt werden.
I
N
F
A
„Bibliothek“
R
• Erstellen Sie ein E/R-Modell und dann das relationale Schema.
O
K
Bibliothek … Anforderungskatalog
102
K
Anfragen an die Bibliothek-Datenbank
•
Eine Datenbank für das Schema kann fertig importiert werden!
A
T
I
(Bibliotheks-DB_latin.sql)
1. Welche Bücher sind bei einem Verlag aus München erschienen?
O
R
M
(214 Ergebnisse)
2. Welche Jugendbücher sind zur Zeit von welchem Leser
ausgeliehen?
Geben Sie den Buchtitel, den Lesernamen und das Datum der
Ausleihe aus!
3. Wie viele Bücher existieren zu jedem Sachgebiet?
Geben Sie die Sachgebiete nach Anzahl absteigend sortiert aus.
4. Wie viele Bücher aus jedem Sachgebiet sind zur Zeit ausgeliehen?
I
N
F
(7 Ergebnisse)
(Kinder: 14)
5. Welche Leser haben zur Zeit Bücher ausgeliehen?
(49 Ergebnisse, Achtung Duplikate?)
103
I
•
Dr. Mager (kurz Ma) behandelt am 21.2.2005 den Patienten
Willi Schäfer (Patientennummer 3012). Im Rahmen dieser
Behandlung werden die folgenden Leistungen erbracht:
Beratung, symptombezogene Untersuchung, Schutzimpfung.
Jede dieser Leistungen ist über eine Nummer identifizierbar
und kostet eine bestimmte Gebühr.
•
Frau Dr. Hurtig (kurz Hu) wird am Sonntag (27.2.2005) zu
einem Notfall gerufen. Patient Manfred Achilles ist beim
Fußballspiel eine Sehne gerissen. In der Untersuchung vor
Ort wird das Bein ruhig gestellt und der Patient ins
Krankenhaus eingewiesen.
•
Herr Dr. Alzheimer (kurz Al) besucht regelmäßig seine
Patientin Paula Stein im Altenheim. Diese Untersuchung gilt
als Vorsorgemaßnahme.
I
N
F
O
R
M
A
„Arztpraxis“
T
K
Arztpraxis … Anforderungskatalog
104
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
I
N
F
O
R
M
A
T
I
K
Agenda
105
über Werkzeuge (z.B. Excel)
über selbst erstellte Programme
I
N
F
O
R
M
A
K
I
• Grafische Auswertung der Datenbank
(z.B. Anzahl der Länder pro Kontinent)
T
Vernetzung des Themas - Auswertungen
106
• Umrechnung Breiten/Längen-Angaben in Bildschirmkoordinaten
I
N
F
O
R
M
A
T
I
K
Vernetzung des Themas - Koordinatensystem
107
• 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.
F
Spree
Weser
Rhein
Fulda
Werra
Main
Anknüpfung an Programmierung
Python, PHP, Delphi, Java
N
Havel
Aller
O
R
M
A
Elbe
Mosel
Neckar
Donau
I
Inn
Isar
108
• Problem: „Verfolgen eines Flusslaufes“
vom Meer zu den Zuflüssen
z.B. Nordsee
Nordsee
T
I
K
Vernetzung des Themas - Grenzen von SQL
A
Elbe
Havel
M
Aller
R
Beispiel für eine (elegante?)
rekursive Programmierung
Spree
Weser
Rhein
Fulda
O
Werra
F
Main
N
Mosel
Neckar
Donau
I
Inn
Isar
109
I
N
F
O
R
M
A
T
I
K
Zugriff auf MySQL über Python
110
•
Grundlegende Schritte jedes Datenbankzugriffs am Beispiel
Python und der Bibliothek MySQLDb:
1. Verbindung mit dem Datenbanksystem aufbauen
Host='localhost'
Benutzer='root'
Passwort='sql'
Datenbank='miniterra'
conn = MySQLdb.connect (Host, Benutzer, Passwort, Datenbank)
Ergebnis der Verbindung ist eine Variable (Handle), mit der auf die
Verbindung zugegriffen werden kann.
Fehler sollten (gerade hier) abgefangen (PHP-Konstrukt „or die“) und
ausgegeben werden.
In manchen Fällen (z.B. PHP) kann die Datenbank erst in einem
zweiten Schritt ausgewählt werden.
2.
T
A
Ergebnis ist eine Variable (Handle), mit der auf das Resultat der
Anfrage zugegriffen werden kann.
Ergebnisse ausgeben
R
3.
Grundlegendes Problem
O
M
Abfrage ausführen
SQLText='SELECT Name, Breite, Laenge FROM …'
cursor = conn.cursor(MySQLdb.cursors.DictCursor)
cursor.execute(SQLText)
I
K
Zugriff auf MySQL über Python
•
•
F
Programmiersprachen sind iterativ
relationale Datenbanken (und damit SQL) sind mengenorientiert
Speicherung und „Durchlaufen“ der Ergebnisse notwendig
Für das Abrufen der Ergebnisse gibt es Varianten:
I
N
i.
ii.
iii.
4.
Tupel / Dictionary
Speicherung der Ergebnisse auf Server / Client
…
Freigabe der Handles
cursor.close()
conn.close()
111
• Ausgabe der Ergebnisse als Dictionary
cursor.fetchone() liefert eine komplette Zeile des Ergebnisses
als Dictionary (bei anderen Cursorn als Tupel)
Nach der letzten Zeile wird None zurückgegeben
M
A
T
I
K
Zugriff auf MySQL über Python
Iteration über
alle Zeilen
des Ergebnisses
row=cursor.fetchone()
while (row!=None):
print(row["Name"], row["Laenge"], row["Breite"]))
row = cursor.fetchone ()
I
N
F
O
R
Auslesen und Speichern
einer Ergebniszeile
Zugriff auf eine Spalte der Ergebniszeile
112
• In Delphi kann der Zugriff auf Datenbanken mittels Borland
Database Engine (BDE) bzw. dbExpress erfolgen.
Problem: Ab Delphi 5 ist die Datenbankunterstützung nur in der
Professional-Version enthalten.
R
M
A
T
I
K
MySQL in Delphi
Lösung: Verzicht auf Borland-Datenbank-Komponenten und
Verwendung freier Zugriffssoftware
• Units, die einen einfachen Datenbankzugriff erlauben
F
O
MySQLDirect (http://www.sourceforge.net/projects/directsql)
I
N
Einsteiger-Tutorial dazu: http://www.delphi-treff.de/tutorials/
113
I
N
F
O
R
M
A
T
I
K
Datenschutz
114
• Anknüpfungspunkt: www.schober.de
• Ein Blick ins Bundesdatenschutzgesetz:
(http://bundesrecht.juris.de/bdsg_1990/ )
I
N
F
O
R
M
A
T
I
K
Datenschutz
115
I
N
F
O
R
M
A
T
I
K
Vernetzung des Themas - Datenschutz
116
• Datenbank-basierte Umfrage in der Schule
Wie können durch Verknüpfen (eigentlich harmloser) Daten neue
Informationen gewonnen werden?
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
I
N
F
O
R
M
A
T
I
K
Agenda
117
• Weitere Möglichkeiten von SQL
Outer Joins
Sub-Selects
• Datenbank-Management
Zugriffsbeschleunigung über Indizes
Verwaltung der physischen Speicherung
• storage engine InnoDB für MySQL
• Sicherung der Datenbank
F
O
R
M
A
T
I
K
Ausblick - Was hätte man noch alles behandeln können?
• Verändern der Datenbankinhalte mit SQL
I
N
INSERT / DELETE / UPDATE
• Verändern der Datenbankstruktur über Anweisungen
Data Definition Language (DDL)
• u.v.m.
118
K
Software / Links
• XAMPP
T
I
http://www.apachefriends.org/de/xampp.html
• Deutsche MySQL Seite
(englisch: http://mysql.com/)
M
A
http://www.mysql.de/
• MySQL Workbench (mit XAMPP-Paket noch nicht voll nutzbar)
O
R
http://www.mysql.com/downloads/workbench/
• PHP-Dokumentation (mit MySQL Funktionen)
N
F
http://www.php.net/download-docs.php
• Connectors (ODBC, JDBC, …) für MySQL
I
http://www.mysql.de/downloads/connector/
• Bibliothek für Python 2.6
(Python 3 noch nicht offiziell unterstützt – unautorisierte Versionen im „Netz“ zu finden)
http://sourceforge.net/projects/mysql-python/
119
• Material zur MySQL-Verwendung von Klaus Merkert
http://www.hsgkl.de/faecher/inf/material/datenbanken/mysql/index.php
• Material zu PostgreSQL von Klaus Merkert
http://www.hsg-kl.de/faecher/inf/datenbanken/postgres/index.php
• Material zu DB allgemein von OSZ Handel, Berlin
http://oszhdl.be.schule.de/gymnasium/faecher/informatik/datenbank
en/index.htm
I
N
F
O
R
M
A
T
I
K
Software / Links
120
• u.v.m.
Fragen
I
N
F
O
R
M
A
T
I
K
Informationssysteme / Datenbankabfragen
121
• Schema zur Beispieldatenbank „schulverwaltung.sql“
LNR
Name
A
T
I
K
Schemata der Beispieldatenbanken
Vorname
n
M
Lehrer
FNR
Klassenleiter
F
O
R
n
N
m
I
Klassen
KStufe
122
KBuchstabe
Saal
unterrichtet
m
Fach
Faecher
• Schema zur Beispieldatenbank „bibliothek.sql“
BuchNr
Autor
…
A
T
I
K
Schemata der Beispieldatenbanken
zurueck
n
LeserNr
Nachname
m
Ausleihe
…
Leser
M
Buecher
…
1
R
n
F
Verlag
Ist Teil von
1
Leserprivat
I
1
N
O
gibt heraus
VNR
Name
Ort
LeserNr
Geschlecht
…
123
• Schema zur Beispieldatenbank „wm2006.sql“
I
K
Schemata der Beispieldatenbanken
Ort
T
1
A
n
Team1
Begegnung
Datum
Team2
1
n
Ergebnis
1
1
F
O
R
M
n
Schiedsrichter
N
Team
n
I
1
1
124
Land
1
• WM-Datenbank als UML-Diagramm
I
K
Schemata der Beispieldatenbanken
Ort
T
ONR
A
TNR1
Name
Sitze
ONR
Begegnung
SNR
M
TNR2
Datum
TNR
TNR
Team
125
Schiedsrichter
Trainer
LNR
TNR
LNR
I
N
F
O
R
Ergebnis
TNR in Team ist
Primär- und
Fremdschlüssel
Land
LNR
Name
Einwohner
Kontinent
Name
Vorname