Transcript Datenbanksysteme - Zugriff mit SQL

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
F
Zugriff auf Datenbanken (PHP & Co.)
I
N
Ausblick
2
Software-Architekturen: „Standalone“-Programm
K
• z.B.
O
R
M
A
T
I
 selbst programmierte Schülerdatei
 in Delphi programmierte Übersicht der WM-Begegnungen
I
N
F
Anwendungsprogramm
3
PC
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
4
PC
Software-Architekturen - Client-Server
K
• z.B.
R
M
A
T
I
 Outlook und Exchange-Server
 „einfache“, datenbank-basierte Schülerverwaltung
Client
I
N
F
O
Anwendungsprogramm
Anwendungsprogramm
(Datenbank)Server
Server
5
Software-Architekturen - Client-Server
K
• z.B.
R
M
A
T
I
 Outlook und Exchange-Server
 „einfache“, datenbank-basierte Schülerverwaltung
Client
I
N
F
O
Anwendungsprogramm
(Datenbank)Server
Server
6
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
7
Software-Architekturen – Webarchitektur
A
T
I
K
• Eine moderne 3-schichtige Webarchitektur…
(Browser)
R
M
Webclient
Client
Server
I
N
F
O
Anwendungsprogramm
Webserver (z.B. Apache und PHP)
Datenbankserver
(z.B. MySQL)
Server
8
Software-Architekturen – Webarchitektur
A
T
I
K
• Eine moderne 3-schichtige Webarchitektur…
(Browser)
O
R
M
Webclient
Client
(z.B. Apache und PHP)
Server
I
N
F
Webserver
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
• 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
12
MySQL
K
Das andere Extrem – eine 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
13
• Ein Informationssystem kann auf formalisierte Fragen eines
Anwenders Antworten aus einer gegebenen Datenmenge geben
 Komponenten eines Informationssystems:
A
T
I
K
Was ist ein Informationssystem ?
R
Speicherung
I
N
F
Erfassung
O
M
Informationssystem
14
Datenbanksystem
DBS
Datenbankmanagementsystem
DBMS, z.B. MySQL
Analyse
Darstellung
Datenbank
(Datenbasis)
Datenbank
(Datenbasis)
I
N
F
O
R
M
A
T
I
K
Was ist ein Informationssystem ?
15
• Datenbasis / Datenbank
 Menge von Daten, die aus Sicht der Systembetreiber in irgendeiner
Weise als zusammengehörig betrachtet werden.
 Angereichert um weitere Daten, die das DBMS zur Erfüllung seiner
Aufgaben benötigt, bilden sie eine Datenbank (DB).
• Datenbankverwaltungssystem (DBMS)
 Softwareprodukte für die dauerhafte, integre und
anwendungsunabhängige Speicherung und Verwaltung von großen,
mehrfachbenutzbaren integrierten Datenbasen
• Datenbanksystem
 Ein Datenbanksystem (DBS) besteht aus einem DBMS und einer oder
mehreren Datenbanken
• Informationssystem
 Ein Informationssystem erweitert die Datenbank um eine Reihe von
Werkzeugen zur Abfrage, Darstellung, Transformation und Analyse
von Daten
• Man unterscheidet verschiedene Arten von Datenbanken:
• Hierarchische 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
I
K
Arten von Datenbanken
• Beide Formen waren vor allem bei Großrechnern im Einsatz und
werden zunehmend von relationalen Datenbanken abgelöst
16
• Relationale Datenbanken
 Die Daten werden in Form von Tabellen gespeichert
 Zwischen den Tabellen werden Beziehungen aufgebaut (Relationen)
Attribut (Merkmal)
Attributwert (Datenwert)
Begegnung
Team1
Team2
Datum
Ergebnis
Deutschland
Polen
Deutschland
Ecuador
Costa Rica
Ecuador
Polen
Costa Rica
9.6.2006
9.6.2006
14.6.2006
16.6.2006
4:2
0:2
1:0
3:0
Datensatz (Tupel)
Attributklasse
I
N
F
O
R
M
A
T
I
K
Arten von Datenbanken
 Objektrelationale Datenbanken
• erweitern die relationalen Datenbanken und objektorientierte Funktionen
(z.B. Vererbung)
17
I
N
F
O
R
M
A
T
I
K
Arten von Datenbanken
18
• 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
19
• Transaktionen beeinflussen sich nicht gegenseitig
 Durability
• Eine Transaktion ist dauerhaft gespeichert, auch gegen Systemabstürze
gesichert
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
20
Zugriff auf Datenbanken (PHP & Co.)
Ausblick
• Starten Sie den Datenbankserver und den Webserver
I
N
F
O
R
M
A
T
I
K
MySQL – Jetzt wird es (endlich) praktisch…
21
• Das Datenbanksystem bietet einen Service für andere Rechner an
 Die Windows-Firewall kann dies melden.
Der Port muss freigegeben werden.
• Die Administration von MySQL funktioniert selbst schon am
einfachsten über den Browser
 Webarchitektur
T
I
K
Die erste Datenbank importieren
A
Browser
R
M
Client
Webserver
N
F
O
Apache
I
MySQL
22
Datenbankserver
http://localhost/phpmyadmin
• Legen Sie eine neue Datenbank „wm-stufe1“ an.
I
N
F
O
R
M
A
T
I
K
Die erste Datenbank importieren
23
• Wählen Sie den Punkt „Importieren“ und suchen die Datei
„wm-stufe1.sql“
I
N
F
O
R
M
A
T
I
K
Die erste Tabelle…
24
• Klicken Sie auf „Struktur“: Die Tabelle „begegnung“ wird
angezeigt.
• Lassen Sie sich den Inhalt der Tabelle anzeigen.
• Jeder Datensatz wird durch einen Schlüssel (Primärschlüssel)
eindeutig identifiziert. Der Schlüssel
 kann aus mehreren Attributen zusammengesetzt werden.
 ist minimal, d.h. es kann kein Attribut weggelassen werden.
• Oft wird ein zusätzliches, eindeutiges Schlüsselattribut hinzugefügt,
z.B. Nummer der Begegnung
 wird meist durch Unterstreichung gekennzeichnet.
Begegnung
BNR
Team1
Team2
Datum
Ergebnis
A1
A2
A3
A4
Deutschland
Polen
Deutschland
Ecuador
Costa Rica
Ecuador
Polen
Costa Rica
9.6.2006
9.6.2006
14.6.2006
16.6.2006
4:2
0:2
1:0
3:0
I
N
F
O
R
M
A
T
I
K
Relationale Datenbank – Aufbau
Schlüsselattribut
25
• 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:
26
• Die WHERE- und die ORDER BY-Klausel sind optional.
• Eine Projektion schränkt die Abfrage hinsichtlich der Attribute ein
(die Spalten der resultierenden Tabelle):
SELECT
FROM
WHERE
ORDER
[Spalten]
[Tabelle]
[Bedingung]
BY [Attribute];
• Die darzustellenden Spalten der Tabelle müssen durch Kommata
getrennt angegeben werden; * steht für „alle Spalten“.
I
N
F
O
R
M
A
T
I
K
SQL – SELECT und FROM
27
• Beispiele:
 SELECT * FROM Begegnung;
 SELECT Team1 FROM Begegnung;
 SELECT Team1, Team2 FROM Begegnung;
• Eine Selektion schränkt die Abfrage hinsichtlich der Datensätze ein
(die Zeilen der resultierenden Tabelle):
SELECT
FROM
WHERE
ORDER
[Spalten]
[Tabelle]
[Bedingung]
BY [Attribute];
• Die Abfrage enthält alle Datensätze, welche die angegebene
Bedingung erfüllen.
I
N
F
O
R
M
A
T
I
K
SQL – WHERE
28
• Beispiele:
 SELECT * FROM Begegnung WHERE (Team1='Deutschland')
 SELECT * FROM Begegnung WHERE (Datum>='2006-07-01')
 SELECT Team1, Team2, Ergebnis
FROM Begegnung
WHERE (Team1='Deutschland')
T
I
K
Veranschaulichendes Beispiel
A
Team1
Team2
Ort
Ergebnis
A1
A2
A3
F4
Deutschland
Polen
Deutschland
Brasilien
Costa Rica
Ecuador
Polen
Australien
München
Gelsenkirchen
Dortmund
München
4:2
0:2
1:0
2:0
I
N
F
O
R
BNR
M
Begegnung
29
Ergebnistabelle
Team1
Team2
Ergebnis
Deutschland
Deutschland
Costa Rica
Polen
4:2
1:0
• Bedingungen mit Textattributen:
 Team1='Deutschland'
 Team1 LIKE 'P%'
 Name LIKE “M_ller“
(Schmitt)
(Paraguay, Polen, Portugal)
(Müller, Muller, Miller, Maller)
A
T
I
K
SQL – WHERE
I
N
F
O
R
M
• Bedingungen mit Zahlattributen:
30







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)
• Vergleich auf Nullwert (kein Attributwert angegeben):
SNR IS NULL
• Logische Verknüpfungen:
 NOT (Name='Schmitt')
(Name nicht Schmitt)
R
M
A
T
I
K
SQL – WHERE
 (Ort='Berlin') AND (Datum<'2006-07-01')
I
N
F
O
(Spiele in Berlin vor Juli 2006)
31
 (Team1='Deutschland') OR (Team2='Deutschland')
(Deutschland spielt als erstes oder zweites Team)
• Das Abfrageergebnis kann sortiert werden:
SELECT
FROM
WHERE
ORDER
[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
I
K
SQL – ORDER BY
• Beispiele:
 SELECT * FROM Begegnung ORDER BY Datum
 SELECT * FROM Begegnung ORDER BY Ort, Datum DESC
32
•
Öffnen Sie das SQL-Fenster
1. Geben Sie alle Begegnungen aus!
SELECT *
FROM Begegnung
I
N
F
O
R
M
A
T
I
K
Die ersten SQL Befehle
33
2. Geben Sie alle Begegnungen aus:


Datum, Name der Teams und Ergebnis
Sortierung nach Datum absteigend
SELECT Datum, Team1, Team2, Ergebnis
FROM Begegnung
ORDER BY Datum DESC
3. An welchen Spielen war Deutschland beteiligt?
WHERE Team1='Deutschland' OR
Team2='Deutschland'
4. Welche Spiele fanden zwischen dem 10.6.2006 und 15.6.2006
statt?

Absteigend nach Datum sortiert
WHERE Datum BETWEEN '2006-06-10' AND '2006-06-15'
ORDER BY Datum DESC
F
Lösung: Konvertierung mit Datumsfunktion für die Ausgabe
I
Problem: Datumsformat unterscheidet sich vom deutschen Format
N
O
R
M
A
T
I
K
Aufgaben, Datenbank: wm-stufe1
34
SELECT Team1, Team2, DATE_FORMAT(Datum,'%d.%m.%Y')
FROM ...
• Wichtig sind vor allem Funktionen zur Manipulation von Strings
und Datumsangaben, z.B.
 DATEDIFF(D1, D2)
 NOW()
 …
Differenz (Tage)
Aktuelle Zeit/Datum
(SELECT CURDATE())
• Wichtige Stringfunktionen






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
A
T
I
K
Hilfsfunktionen
SQL ist hier nicht standardisiert
 Die Funktionen sind bei anderen DBS oft unterschiedlich
35
5. Geben Sie die Spiele in folgender Form aus:
CONCAT(UPPER(LEFT(Team1,3)),'-',UPPER(LEFT(Team2,3)),': ',Ergebnis) AS Spiel,
DATE_FORMAT(Datum,'%d.%m.%Y') AS 'Wann'
Spalten können mit „AS“ umbenannt werden
6. Welche Spiele fanden in München statt?
WHERE Ort='München'
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: wm-stufe1
I
N
Problem: München wird unterschiedlich geschrieben!
36
Lösung: Auslagerung in eine eigene Tabelle
• Begegnung und Ort werden in zwei getrennten Tabellen
gespeichert und über eine Beziehung miteinander verknüpft.
• Zur Verknüpfung dient ein Ortskürzel, das als Fremdschlüssel in
Begegnung gespeichert wird.
BNR
A1
M
A
T
I
K
Relationale Datenbanken – Beziehungen
Polen
Deutschland
Brasilien
Team2
Costa Rica
Ecuador
Polen
Australien
Ort
München
Gelsenkirchen
Dortmund
München
F
O
R
A2
A3
F4
Team1
Deutschland
I
N
Begegnung
Ort
BNR
Team1
Team2
ONR
ONR
Name
A1
A2
A3
F4
Deutschland
Polen
Deutschland
Brasilien
Costa Rica
Ecuador
Polen
Australien
MUE
GEL
DOR
MUE
MUE
GEL
DOR
München
Gelsenk.
Dortmund
37
Schlüsselattribut aus Ort
• Zwischen den Tabellen bestehen Beziehungen (Relationen)
Master
PKey
Detail
Data
FKey
PKey
Data
I
N
F
O
R
M
A
I
 z.B. Spielort der Begegnung
T
K
Relationale Datenbanken – Beziehungen
• 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.
• Vorteile:
 Daten werden jeweils nur in einer Tabelle gespeichert.
 Datenänderungen sind leichter durchzuführen.
 Strukturänderungen (z.B. das Hinzufügen der Stadiongröße) lassen sich
meist mit geringem Aufwand realisieren.
 Die Struktur lässt flexiblere Abfragen zu.
38
• Müssen in SQL Daten aus mehreren Tabellen entnommen werden,
so werden sog. „Joins“ gebildet.
I
K
SQL – einfache Joins
T
 Die Abarbeitung eines Joins in mehreren Schritten kann an folgendem
Beispiel veranschaulicht werden:
I
N
F
O
R
M
A
• Es sollen alle Begegnungen ausgegeben werden, die in Stadien mit mehr
als 60.000 Sitzplätzen gespielt wurden.
39
Begegnung
Ort
BNR
Team1
Team2
ONR
ONR
Name
Sitze
A1
A2
A3
F4
GER
POL
GER
BRA
CRC
ECU
POL
AUS
MUE
GEL
DOR
MUE
MUE
GEL
DOR
München
Gelsenk.
Dortmund
66000
52000
65000
1. Cross-Join („jede Zeile mit jeder“)
Begegnung
SELECT *
FROM Begegnung, Ort
BNR
Team1
Team2
ONR
A1
A2
A3
F4
GER
POL
GER
BRA
CRC
ECU
POL
AUS
MUE
GEL
DOR
MUE
ONR
Name
Sitze
MUE
GEL
DOR
München
Gelsenk.
Dortmund
66000
52000
65000
I
N
F
O
R
M
A
T
I

Ort
K
SQL – einfache Joins
40
BNR Team1
Team2
ONR
ONR
Name
Sitze
A1
A1
A1
A2
A2
A2
GER
GER
GER
POL
POL
POL
CRC
CRC
CRC
ECU
ECU
ECU
MUE
MUE
MUE
GEL
GEL
GEL
MUE
GEL
DOR
MUE
GEL
DOR
München
Gelsenk.
Dortmund
München
Gelsenk.
Dortmund
66000
52000
65000
66000
52000
65000
F4
F4
BRA
BRA
AUS
AUS
MUE
MUE
MUE
GEL
München
Gelsenk.
66000
52000
…
…
…
…
…
…
…
I


Es dürfen nur die Zeilen genommen werden, für die die „Begegnung“
und die „Ort“ Tabelle Daten des gleichen Ortes enthalten.
Dies wird durch die sog. „Join-Bedingung“ erreicht.
SELECT *
FROM Begegnung, Ort
WHERE Begegnung.ONR = Ort.ONR
I
N
F
O
R
M

T
2. Einschränken auf „passende“ Datensätze.
A
K
SQL – einfache Joins
41
BNR Team1
Team2
ONR
ONR
Name
Sitze
A1
A1
A1
A2
A2
A2
GER
GER
GER
POL
POL
POL
CRC
CRC
CRC
ECU
ECU
ECU
MUE
MUE
MUE
GEL
GEL
GEL
MUE
GEL
DOR
MUE
GEL
DOR
München
Gelsenk.
Dortmund
München
Gelsenk.
Dortmund
66000
52000
65000
66000
52000
65000
F4
F4
BRA
BRA
AUS
AUS
MUE
MUE
MUE
GEL
München
Gelsenk.
66000
52000
…
…
…
…
…
…
…
2. Einschränken auf „passende“ Datensätze (2).


T
I
K
SQL – einfache Joins
I
N
F
O
R
M
A

Es sollen nur Spielorte mit mehr als 60000 Sitzen gezeigt werden.
Momentan würde auch „Gelsenkirchen“ ausgegeben werden.
Also muss eine weitere Bedingung erfüllt sein:
SELECT *
FROM Begegnung, Ort
WHERE Begegnung.ONR = Ort.ONR
AND Ort.Sitze > 60000
42
BNR Team1
Team2
ONR
ONR
Name
Sitze
A1
A1
A1
A2
A2
A2
GER
GER
GER
POL
POL
POL
CRC
CRC
CRC
ECU
ECU
ECU
MUE
MUE
MUE
GEL
GEL
GEL
MUE
GEL
DOR
MUE
GEL
DOR
München
Gelsenk.
Dortmund
München
Gelsenk.
Dortmund
66000
52000
65000
66000
52000
65000
F4
F4
BRA
BRA
AUS
AUS
MUE
MUE
MUE
GEL
München
Gelsenk.
66000
52000
…
…
…
…
…
…
…
3. Einschränken auf gesuchte Spalten.


BNR Team1
Team2
ONR
ONR
Name
Sitze
A1
F4
…
CRC
AUS
…
MUE
MUE
…
MUE
MUE
…
München
München
…
66000
66000
…
Team1
Team2
Name
GER
BRA
…
CRC
AUS
…
München
München
…
GER
BRA
…
I
N
F
O
M
Nur bestimmte Spalten werden ausgegeben.
SELECT Begegnung.Team1, Begegnung.Team2, Ort.Name
FROM Begegnung, Ort
WHERE Begegnung.ONR = Ort.ONR
AND Ort.Sitze > 60000
R
A
T
I
K
SQL – einfache Joins
43
In der Realität versucht das
DBMS, durch „geschicktes“
Vorgehen die Datenmenge
schon früher zu reduzieren.
1. Geben Sie alle Begegnungen mit dem Ort der Austragung aus:


T
I
K
Aufgaben, Datenbank: wm-stufe2
Datum, Name der Teams und Name des Orts
Sortierung nach Datum absteigend
Datum, Team1, Team2, Ort.Name
Begegnung, Ort
Ort.ONR=Begegnung.ONR
BY Datum DESC
R
M
A
SELECT
FROM
WHERE
ORDER
2. Wo fanden Spiele mit deutscher Beteiligung statt?
Name und Sitzplätze der Stadien
SELECT DISTINCT
Name, Sitze
Name, Sitze
FROM Begegnung, Ort
WHERE Ort.ONR=Begegnung.ONR
AND ( Begegnung.Team1='Deutschland'
OR Begegnung.Team2='Deutschland')
I
N
F
O

Problem: Es werden Duplikate angezeigt
Lösung: DISTINCT-Anweisung
44
• Es sollen nun auch die Trainer der Teams gespeichert werden.
 Um Duplikate zu vermeiden, wird eine Tabelle der Teams angelegt.
 Es werden zwei Fremdschlüssel in der Tabelle Begegnung benötigt.
BNR
Team1
Team2
ONR
A1
A2
A3
F4
Deutschland
Polen
Deutschland
Brasilien
Costa Rica
Ecuador
Polen
Australien
MUE
GEL
DOR
MUE
I
N
F
O
R
M
A
T
I
K
Relationale Datenbanken – Beziehungen
45
Begegnung
Team
BNR
TNR1
TNR2
ONR
A1
A2
A3
F4
GER
POL
GER
BRA
CRC
ECU
POL
AUS
MUE
GEL
DOR
MUE
TNR
Name
Trainer
GER
POL
CRC
ECU
…
Deutschland
Polen
Costa Rica
Ecuador
Klinsmann
Janas
Guimaraes
Suarez
• Soll in SQL auf eine Tabelle mehrfach zugegriffen werden, so kann
dies mit Alias-Namen geschehen:
 Alle Begegnungen mit ihren Teams und Trainern
I
N
F
O
R
M
A
T
I
K
SQL – Tabellen-Alias
46
 SELECT B.Datum,
T1.Name, T1.Trainer,
T2.Name, T2.Trainer
FROM Begegnung B, Team T1, Team T2
WHERE B.TNR1=T1.TNR
AND B.TNR2=T2.TNR
Der Alias-Name für
Begegnung ist
nicht notwendig
(verkürzt die
Abfrage)
• Es werden nun Spalten mit gleichen Namen ausgegeben
 Spaltennamen können umbenannt werden.
I
N
F
O
R
M
A
T
I
K
SQL – Spaltennamen
47
 SELECT B.Datum,
T1.Name AS Team1, T1.Trainer AS Trainer1,
T2.Name AS Team2, T2.Trainer AS Trainer2
FROM Begegnung B, Team T1, Team T2
WHERE B.TNR1=T1.TNR
AND B.TNR2=T2.TNR
I
K
Komplette Struktur der Datenbank
Ort
T
ONR
A
TNR1
M
TNR2
Name
Sitze
ONR
Begegnung
SNR
Datum
TNR
TNR
Team
48
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
K
Komplette Struktur der Datenbank als E/R-Diagramm
Ort
T
I
1
n
Begegnung
n
M
A
n
Datum
Team2
R
Team1
Ergebnis
1
1
F
1
Schiedsrichter
n
I
Team
N
O
1
1
49
Land
1
1. Welche Teams kommen aus Ländern, die weniger als 10 Mio.
Einwohner haben?
SELECT Land.Name, Land.Einwohner * 1000000 AS Einwohnerzahl,
Team.Trainer
FROM Team, Land
WHERE Team.TNR = Land.LNR
Rechenoperationen
AND Land.Einwohner<10
sind erlaubt.
2. Geben Sie alle Begegnungen aus


Teamnamen, Schiedsrichter, Ergebnisse
nach Datum geordnet
I
N
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: wm-stufe4
50
SELECT B.Datum, T1.Name, T2.Name,
S.Name, S.Vorname, B.Ergebnis
FROM Begegnung B, Schiedsrichter S, Land T1, Land T2
WHERE B.SNR = S.SNR
AND B.TNR1 = T1.LNR
AND B.TNR2 = T2.LNR
Der Join kann direkt
ORDER BY B.Datum
auf das Land erfolgen,
da von Team nichts
benötigt wird.
3. Geben Sie alle Begegnungen aus

Teamnamen, Ergebnis, Schiedsrichter und Kontinent des
Schiedsrichters
I
N
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: wm-stufe4
51
SELECT B.Datum, T1.Name, T2.Name,
S.Name, S.Vorname, LS.Name, LS.Kontinent,
B.Ergebnis
FROM Begegnung B, Schiedsrichter S,
Land T1, Land T2,
Land LS
WHERE B.SNR = S.SNR
AND B.TNR1 = T1.LNR
AND B.TNR2 = T2.LNR
AND S.LNR = LS.LNR
ORDER BY B.Datum
Die Tabelle Land wird
nun sogar dreimal
benötigt.
• Manchmal müssen die Ergebnisse einer Anfrage gruppiert und
verrechnet werden.
 Es sollen
werden.
 SELECT
FROM
WHERE
ORDER
die Kontinente und die Anzahlen der Teams ausgegeben
Team.TNR, Land.Kontinent
Team, Land
Team.TNR = Land.LNR
BY Land.Kontinent
I
N
F
O
R
M
A
T
I
K
Gruppieren von Ergebnissen
52
TNR
Kontinent
KOR
JPN
IRN
AUS
PAR
Asien
Asien
Asien
Australien
Südamerika
ARG
Südamerika
Selbst zählen???
NEIN!
• Manchmal müssen die Ergebnisse einer Anfrage gruppiert und
verrechnet werden.
 Es sollen
werden.
 SELECT
FROM
WHERE
GROUP
ORDER
die Kontinente und die Anzahlen der Teams ausgegeben
Land.Kontinent, COUNT(*)
Team, Land
Team.TNR = Land.LNR
BY Land.Kontinent
BY Land.Kontinent
I
N
F
O
R
M
A
T
I
K
Gruppieren von Ergebnissen
53
TNR
Kontinent
Kontinent
Anzahl
KOR
JPN
IRN
AUS
PAR
Asien
Asien
Asien
Australien
Südamerika
ARG
Südamerika
Asien
Australien
Südamerika
3
1
2
3
1
2
1. Geben Sie die Anzahl an Spiele pro Kontinent aus!
SELECT
FROM
WHERE
T.TNR)
AND
GROUP
COUNT(*) as Anzahl, L.Kontinent
Begegnung B, Team T, Land L
(B.TNR1 = T.TNR OR B.TNR2 =
T.TNR = L.LNR
BY L.Kontinent
2. In welchen Stadien hat Deutschland wie oft gespielt?
SELECT
FROM
WHERE
AND
GROUP
O.Name, COUNT(*)
Begegnung B, Ort O
(B.TNR1='GER' OR B.TNR2='GER')
B.ONR=O.ONR
BY O.Name
I
N
F
O
R
M
A
T
I
K
Aufgaben, Datenbank: wm-stufe4
3. Wie viele Sitze haben die Stadien im Schnitt, maximal und
minimal?
SELECT ROUND(AVG(Sitze)), MAX(Sitze), MIN(Sitze)
FROM Ort
54
4. Welche Schiedsrichter kommen aus Ländern, die keine Teams
stellen?
I
K
Aufgaben, Datenbank: wm-stufe4
S.Name, S.Vorname, L.Name, L.Kontinent
Schiedsrichter S, Land L
S.LNR = L.LNR
NOT EXISTS
( SELECT NULL FROM Team T WHERE T.TNR=L.LNR)
ORDER BY L.Kontinent, S.Name
O
R
M
A
T
SELECT
FROM
WHERE
AND
5. Welche Spiele wurden von einem Schiedsrichter aus dem gleichen
Kontinent gepfiffen?
Geben Sie auch die Ländernamen der Teams aus.
I
N
F

55
SELECT
FROM
WHERE
AND
AND
AND
AND
LT1.Name, LT2.Name, B.Ergebnis, S.Name, LS.Name, LS.Kontinent
Begegnung B, Schiedsrichter S, Land LS, Land LT1, Land LT2
B.SNR
= S.SNR
S.LNR
= LS.LNR
LT1.LNR = B.TNR1
LT2.LNR = B.TNR2
( LT1.Kontinent = LS.Kontinent OR LT2.Kontinent = LS.Kontinent )
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
56
Zugriff auf Datenbanken (PHP & Co.)
Ausblick
• 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“
I
N
F
O
R
M
T
 MySQL arbeitet nach der Installation ohne Schutz des Administrators
A
I
K
Verwaltung des Datenbanksystems
57
• 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
I
K
MySQL – Passwort für Administrator vergeben
58
• Anmeldetyp für phpMyAdmin ändern
 Ins Verzeichnis xampp\phpmyadmin wechseln.
 Datei config.inc.php mit Notepad öffnen
 Zeile $cfg['Servers'][$i]['auth_type'] = ‘config';
ändern auf $cfg['Servers'][$i]['auth_type'] = 'http';
• 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 vergeben
59
 Passwort eingeben und speichern
 Beim nächsten Aufruf von phpMyAdmin ist die Eingabe des Passworts
notwendig
I
N
F
O
R
M
A
T
I
K
Benutzer / Rechte vergeben
60
• Über den gleichen Dialog 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
61
• Soll sich auch der Administrator (root) von anderen Rechnern aus
anmelden können, so muss dies separat konfiguriert werden.
 Bei jedem Benutzer ist angegeben, von welchem Rechner aus eine
Anmeldung möglich ist.
 Menüpunkt „Rechte“
 Benutzer „root“ auswählen und bearbeiten
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 „wm-stufe4“
erhalten.
I
N
F
O
R
M
A
T
I
K
Benutzer anlegen
62
3. Versuchen Sie, mit diesem Benutzer auf die Datenbank des
Nachbarn / der Nachbarin zuzugreifen.
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
63
Zugriff auf Datenbanken (PHP & Co.)
Ausblick
Schulverwaltung
T
I
K
Ein neues Informationssystem… Anforderungskatalog
A
•
•
•
•
N
F
O
R
M
•
I
•
64
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.
• Um ein Informationssystem zu erstellen, muss ein Ausschnitt der
realen Welt („Miniwelt“) im Computersystem erfasst werden.
I
N
F
O
R
M
A
T
I
K
Modellbildung
65
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
66
Externe
Sicht
Konzeptionelle
Sicht
Internes
Modell
(z.B. relationales
Datenmodell)
• 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
 z.B. eine Person, ein Gegenstand, ein Ereignis
• Entitäten mit gleichen Eigenschaften werden zu Entitätstypen
zusammengefasst
 Symbol: Rechteck
I
N
F
O
R
M
A
T
I
K
Konzeptionelles Modell – E/R-Diagramme
Lehrer
67
• Beispiel Entitätstyp:
 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'
I
N
F
O
R
M
A
T
I
K
E/R-Diagramme - Entitätstyp
68
Entitätstyp
„Lehrer“
3 Entitäten
• Gleichartige Beziehungen zwischen Entitäten werden als ein
Beziehungstyp zwischen den Entitätstypen definiert.
 Symbol: Raute
• 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
I
K
E/R-Diagramme - Beziehungstyp
69
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.
Lehrer
Klasse
I
N
F
O
R
M
A
T
I
K
Modellierung oft nicht eindeutig
70
führt
durch
Klassenfahrt
nimmt
teil
• 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
Name
I
N
F
O
Klassenfahrt
Stufe
Teil
identifizierend
Vorname
beschreibend
71
Datum
Kürzel
Ziel
Anzahl
• Ein Beziehungstyp wird durch die Kardinalität genauer bezeichnet:
T
9a
Name: Müller
Vorname: Yvonne
Name:
9b
Name: Meier
Vorname: Jan
Name:
10d
Name: Dietz
Vorname: Nicole
Name: Seiler
Vorname: Manfred
O
R
Name:
A
 1:n- Beziehung am Beispiel: Klassenzuordnung der Schüler
M
I
K
E/R-Diagramme – Kardinalität
Schüler
I
N
F
Klasse
72
1
Klasse
n
angehören
Schüler
Ein Schüler gehört einer Klasse an. Einer Klasse gehören mehrere Schüler an.
• n:m – Beziehung am Beispiel:
Kurszuordnung von Schülern in der MSS
T
LK: ja
Name: Müller Vorname: Yvonne
A
Fach: Deutsch
LK: ja
Name: Meier
Vorname: Jan
Fach: Englisch
LK: ja
Name: Dietz
Vorname: Nicole
R
Fach: Mathe
M
I
K
E/R-Diagramme – Kardinalität
Name: Seiler Vorname: Manfred
Schüler
Kurs
n
besucht
m
Schüler
I
N
F
O
Kurs
Ein Kurs hat mehrere Schüler. Ein Schüler nimmt an mehreren Kursen teil.
73
• Die Kardinalitäten (1:1, 1:n, n:m) geben nur an, wie viele
Entitäten maximal miteinander verbunden sind.
Klasse
1
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
I
Klasse
1
[1,1]
angehören
n
Schüler
[8,30]
 Bsp.: Eine Klasse hat minimal 8 und maximal 30 Schüler.
74
• Beziehungen können auch auf dem gleichen Entitätstyp gelten,
Selbstbeziehung oder Reflexive Beziehung.
 Bsp.: Heirat
T
I
K
E/R-Diagramme – Reflexive Beziehungstypen
Vorname: Klaus
Name: Bach
Vorname: Stefan
Name: Meier
Vorname: Sabine
R
Name: Hurtig
Vorname: Hans
Personen
N
F
Name: Bach-Meier Vorname: Petra
O
M
A
Name: Meier
I
Personen
oder n:m ?
75
1
1
Heirat
• Die Ist-Beziehung gibt an, dass ein Entitätstyp eine Spezialisierung
eines anderen Entitätstyps darstellt.
 Bsp.: „Lehrer“ spezialisiert „Person“
Name
Vorname
Person
Generalisierungstyp
R
M
A
T
I
K
E/R-Diagramme – Ist-Beziehung
ist
Schüler
Lehrer
Spezialisierungstypen
N
F
O
ist
I
Klasse
• Die Spezialisierungstypen erben die Attribute des
Generalisierungstyps.
76
Titel
• 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
77
K
 Für jeden Entitätstyp eine Tabelle
T
• Transformation von Entitätstypen
I
Transformation E/R-Modell in relationales Schema
E/R-Modell
Name
SNR
R
M
A
Schüler
Schueler
I
SNR
78
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).
n
angehören
Schüler
KNR
Name
SNR
Stufe
O
R
M
Klasse
1
E/R-Modell
A
T
I
K
Transformation E/R-Modell in relationales Schema
Klasse
I
KNR
79
Schueler
Stufe
SNR
Name
Vorname
KNR
Relationales Schema
N
F
Vorname
• Transformation von n:m-Beziehungstypen
 Einfügen eines Fremdschlüssels nicht möglich
 Separate Tabelle mit zwei Fremdschlüsseln
besucht
m
Schüler
Name
KNR
Stufe
SNR
Fehlstunden
O
R
M
Kurs
n
E/R-Modell
A
T
I
K
Transformation E/R-Modell in relationales Schema
Kurs
I
KNR
80
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.
Zusatzdaten
1
SchülerPrivat
Vorname
SNR
Konfession
SNR
Name
O
R
M
Schüler
1
E/R-Modell
A
T
I
K
Transformation E/R-Modell in relationales Schema
Schueler
I
SNR
81
Name
SchuelerPrivat
Vorname
SNR Konfession
Herkunft
Relationales Schema
N
F
Herkunft
• Transformation der Ist-Beziehung
 Separate Tabelle, ähnlich der 1:1-Beziehung
M
A
Vorname
Name
Konfession
SNR
Schulart
Person
I
SNR
82
Schueler
Name
Vorname
SNR Schulart Konfession
Relationales Schema
N
F
O
R
PNR
Schüler
ist
Person
E/R-Modell
T
I
K
Transformation E/R-Modell in relationales Schema
• DBDesigner4
http://fabforce.net/dbdesigner4/
• Ungetestet:
 Mogwai ER-Designer
(http://sourceforge.net/projects/mogwai/)
 TOAD Data Modeler
 TOAD für MySQL (Beta)
http://www.toadsoft.com/toadmysql/Overview.htm
I
N
F
O
R
M
A
T
I
K
Tools für E/R-Diagramme
83
• Oder doch einfach mit Office-Programmen…
I
•
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
„Schule“
T
• Erstellen Sie ein E/R-Modell und dann das relationale Schema.
A
K
Aufgaben
84
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
85
•
Eine Datenbank für das Schema kann fertig importiert werden!
(Bibliotheks-DB_latin.sql)
O
R
M
A
T
I
K
Anfragen an die Bibliothek-Datenbank
1. Welche Bücher sind bei einem Verlag aus München erschienen?
(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!
I
N
F
(7 Ergebnisse)
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?
(Kinder: 14)
5. Welche Leser haben zur Zeit Bücher ausgeliehen?
(49 Ergebnisse, Achtung Duplikate?)
86
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
87
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
88
Zugriff auf Datenbanken (PHP & Co.)
Ausblick
•
Grundlegende Schritte jedes Datenbankzugriffs in PHP:
1. Verbindung mit dem Datenbanksystem aufbauen
$link = mysql_connect($Host, $Benutzer, $Passwort)
or die("DB-Verbindung unmöglich: " . mysql_error());
M
A
T
I
K
Zugriff auf MySQL über PHP

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.
N
F
O
R

I
2. Datenbank auswählen
89
mysql_select_db($Datenbank)
or die("Auswahl Datenbank unmöglich: " . mysql_error());
Abfrage ausführen
$result = mysql_query($SQLText)
or die("Anfrage fehlgeschlagen: " . mysql_error());

4.
Grundlegendes Problem
F
O
•
Programmiersprachen sind iterativ
•
relationale Datenbanken (und damit SQL) sind mengenorientiert
 Speicherung und „Durchlaufen“ der Ergebnisse notwendig

Für das Abrufen der Ergebnisse gibt es drei wesentliche Varianten:
I
N
i.
ii.
iii.
5.
90
Ergebnis ist eine Variable (Handle), mit der auf das Resultat der
Anfrage zugegriffen werden kann.
Ergebnisse ausgeben

R
M
A
I
3.
T
K
Zugriff auf MySQL über PHP
Array
Assoziatives Array
Objektorientiert
Freigabe der Handles
(kann entfallen)
mysql_free_result($result);
mysql_close($link);
 mysql_fetch_array liefert eine komplette Zeile des Ergebnisses
als Array (Alternative: mysql_fetch_row)
 Nach der letzten Zeile wird FALSE zurückgegeben
A
I
• Ausgabe der Ergebnisse als Array
T
K
Zugriff auf MySQL über PHP
Schleife über
alle Zeilen
des Ergebnisses
while ($arrRow = mysql_fetch_array($result))
{
echo "$arrRow[0] : $arrRow[1]";
}
I
N
F
O
R
M
Auslesen und Speichern einer
Ergebniszeile als Array
Zugriff auf eine Spalte der Ergebniszeile
91
• Ausgabe der Ergebnisse als assoziatives Array
 mysql_fetch_array liefert auch ein assoziatives Array, d.h. es
kann über die Spaltennamen zugegriffen werden.
 Problem: Spaltennamen müssen eindeutig sein
A
T
I
K
Zugriff auf MySQL über PHP
Schleife über
alle Zeilen
des Ergebnisses
while ($arrRow = mysql_fetch_array($result))
{
echo "$arrRow[Team1] : $arrRow[Team2]";
}
I
N
F
O
R
M
Auslesen und Speichern einer
Ergebniszeile als Array
Zugriff auf eine Spalte der Ergebniszeile
92
• Ausgabe der Ergebnisse als Objekt
 mysql_fetch_object liefert eine komplette Zeile des Ergebnisses
als Objekt
 Nach der letzten Zeile wird FALSE zurückgegeben
A
T
I
K
Zugriff auf MySQL über PHP
Schleife über
alle Zeilen
des Ergebnisses
while ($oRow = mysql_fetch_object($result))
{
echo "$oRow->Team1 : $oRow->Team2";
}
I
N
F
O
R
M
Auslesen und Speichern einer
Ergebniszeile als objekt
Zugriff auf eine Spalte der Ergebniszeile
93
K
Zugriff auf MySQL über PHP
• Weitere interessante Funktionen:
T
I
 mysql_num_rows($result)
• Gibt die Anzahl der Zeilen (Datensätze) im Ergebnis aus.
 mysql_num_fields($result)
M
A
• Gibt die Anzahl der Spalten im Ergebnis aus.
 mysql_field_name($result, 0)
• Name eines Feldes im Ergebnis.
I
N
F
O
R
 mysql_affected_rows()
94
• Liefert die Anzahl betroffener Datensätze einer vorhergehenden MySQL
Operation wie z.B. DELETE, UPDATE
• Vereinfachung:
 Herstellen der Verbindung / Datenbankauswahl in eine separate
Include-Datei, die immer wieder verwendet werden kann.
• include "_DBVerbindung.inc";
1. Erstellen Sie php-Anzeigeseiten für die Datenbank wm-stufe4.
Verwenden Sie dazu Abfragen aus dem SQL-Teil.
2. Erstellen Sie eine Abfrage, bei der der Spielort eingegeben
werden kann (HTML-Formular). Es werden dann alle
Begegnungen an diesem Ort ausgegeben.
3. Erweiterung zu 2. : Die Liste der
verfügbaren Orte wird vorgeblendet.
I
N
F
O
R
M
A
T
I
K
PHP und Datenbanken – Aufgaben
95
4. Geben Sie die Begegnungen aus.
Es sollen Links in jeder Spalte sein, die eine Sortierung
erlauben.
• 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.
O
• Units, die einen einfachen Datenbankzugriff erlauben
 Einsteiger-Tutorial dazu: http://www.dsdt.info/tutorials/
I
N
M
R
 Lösung: Verzicht auf Borland-Datenbank-Komponenten und
Verwendung freier Zugriffssoftware
F
A
T
I
K
MySQL in Delphi
96
 MySQLDirect (http://www.sourceforge.net/projects/directsql)
• ODBC (Open DataBase Connectivity) ist ein (alter) Standard, um
auf Datenquellen (insbesondere Datenbanken) zuzugreifen.
 gewisse Standardisierung und damit leichtere Portierung auf andere
Datenbanksysteme
 Zugriffsmöglichkeit für verschiedene Programme (Excel, Access,
Delphi, …)
 Muss für MySQL separat installiert werden
(http://mysql.org/downloads/connector/)
F
ODBC
Client
I
Anwendungsprogramm
N
O
R
M
A
T
I
K
MySQL mit ODBC „anzapfen“
(Datenbank)Server
97
Server
I
N
F
O
R
M
A
T
I
K
MySQL mit ODBC „anzapfen“
98
• In Excel gibt es Probleme mit der erstellten Datenquelle:
 Ein Zeichen des Charactersets wird abgeschnitten.
 Lösung: Speicherung der Quelle als dsn-Datei.
 Daten  Importieren  mysql_Quelle_für_wm-stufe1.dsn öffnen.
I
N
F
O
R
M
A
T
I
K
ODBC-Zugriff am Beispiel Excel 2003
99
 Diese Datei kann für eigene Zwecke
leicht mit einem Editor angepasst werden.
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
I
N
F
O
R
M
A
T
I
K
Agenda
100
Zugriff auf Datenbanken (PHP & Co.)
Ausblick
• Weitere Möglichkeiten von SQL
 Outer Joins
 Sub-Selects
 Zugriffsbeschleunigung über Indizes
 Verwaltung der physischen Speicherung
• storage engine InnoDB für MySQL
• Sicherung der Datenbank
• Verändern der Datenbankinhalte mit SQL
 INSERT / DELETE / UPDATE
I
N
F
O
M
• Datenbank-Management
R
A
T
I
K
Ausblick - Was hätte man noch alles behandeln können?
• Verändern der Datenbankstruktur über Anweisungen
 Data Definition Language (DDL)
• u.v.m.
101
• XAMPP
 http://www.apachefriends.org/de/xampp.html
• Deutsche MySQL Seite
 http://www.mysql.de/
(englisch: http://mysql.com/)
• MySQL Gui Tools
 http://www.mysql.de/downloads/gui-tools/
• PHP-Dokumentation (mit MySQL Funktionen)
 http://www.php.net/download-docs.php
N
F
O
R
M
A
T
I
K
Software / Links
I
• Connectors (ODBC, JDBC, …) für MySQL
102
 http://www.mysql.de/downloads/connector/
• Material zur MySQL-Verwendung von Klaus Merkert
 http://www.hsgkl.de/faecher/inf/material/datenbanken/mysql/index.php
I
N
F
O
R
M
A
T
I
K
Software / Links
103
• Material zu DB allgemein von OSZ Handel, Berlin
 http://oszhdl.be.schule.de/gymnasium/faecher/informatik/datenbank
en/index.htm
• u.v.m.
• 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
104
KBuchstabe
Saal
unterrichtet
m
Fach
Faecher
• Schema zur Beispieldatenbank „bibliothek.sql“
BuchNr
Autor
…
zurueck
n
A
T
I
K
Schemata der Beispieldatenbanken
Ausleihe
LeserNr
m
Nachname
…
Leser
M
Buecher
…
1
R
n
F
Verlag
Ist Teil von
1
Leserprivat
I
1
N
O
gibt heraus
VNR
105
Name
Ort
LeserNr
Geschlecht
…