Upload
trinhkhanh
View
212
Download
0
Embed Size (px)
Citation preview
I N F O R M A T I K
Informationssysteme / Datenbankabfragen
Thomas Mohr
IN
FO
RM
AT
IK
2
Agenda
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Datenbanken – Wozu?Datenbanken – Wozu?
IN
FO
RM
AT
IK
3
Informationssysteme
IN
FO
RM
AT
IK
4
Was ist ein Informationssystem ?
• Ein Informationssystem kann auf formalisierte Fragen eines Anwenders Antworten aus einer gegebenen Datenmenge geben� Komponenten eines Informationssystems:
Informationssystem
Erfassung
Speicherung
Analyse
Darstellung
DatenbanksystemDBS
Datenbank(Datenbasis)
Datenbank(Datenbasis)
DatenbankmanagementsystemDBMS, z.B. MySQL
IN
FO
RM
AT
IK
6
Ein Ausgangspunkt im Unterricht…
• Schüler führen eine Internet-Recherche durch:„Städte in Deutschland – Einwohner, geografische Lage“� Schüler nutzen ein Informationssystem
� Schüler stellen automatisch Informationen unterschiedlich dar
MainzMainzMainzMainz� 196.000196.000196.000196.000� 50505050°°°° NordNordNordNord� 8888°°°° 16‘ Ost16‘ Ost16‘ Ost16‘ OstLandauLandauLandauLandau� 43000430004300043000� 49,1949,1949,1949,19� 8,128,128,128,12
Listen
888850505050199000199000199000199000MainzMainzMainzMainz
88884949494943000430004300043000LandauLandauLandauLandau
LängeLängeLängeLängeBreiteBreiteBreiteBreiteEinwohnerEinwohnerEinwohnerEinwohnerNameNameNameName
Tabellen
Tabellen bieten schon „von Hand“ Vorteile:� leichter zu ergänzen (neue Spalte)� Summenzeile� …
IN
FO
RM
AT
IK
7
Motivation - Datenbank
• Idee: Sammlung der gefundenen Städte in einem Tabellenblatt (OpenOffice Calc / MS Excel) auf einem zentralen Laufwerk
� Es treten typische Probleme auf:� 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.
IN
FO
RM
AT
IK
8
Software-Architekturen: „Standalone“-Programm
• z.B. � selbst programmierte Schülerdatei
� in Delphi programmierte Übersicht von Länderinformationen
PCAnwendungsprogramm
IN
FO
RM
AT
IK
9
Software-Architekturen: „Standalone“-Programm
• Vorteile� Übersichtlichkeit (?)
� Schnell zu programmieren
� nur eine Programmiersprache
• Nachteile� Daten meist nur vom
erzeugenden Programm zu lesen
� Erweiterungen aufwändig
� Immer wieder gleiche Probleme (z.B. Datumsformat)
PCAnwendungsprogramm
IN
FO
RM
AT
IK
10
(Datenbank)Server
Anwendungsprogramm
Software-Architekturen - Client-Server
• z.B. � Outlook und Exchange-Server
� „einfache“, datenbank-basierte Schülerverwaltung
Server
ClientAnwendungsprogramm
IN
FO
RM
AT
IK
11
Software-Architekturen - Client-Server
• z.B. � Outlook und Exchange-Server
� „einfache“, datenbank-basierte Schülerverwaltung
(Datenbank)Server
Server
ClientAnwendungsprogramm
IN
FO
RM
AT
IK
12
(Datenbank)Server
Software-Architekturen - Client-Server
• Vorteile� Datenbank übernimmt
„Standardaufgaben“
� Daten zentral vorhanden(für mehrere Benutzer / Programme)
� Erweiterungen relativ einfach
• Nachteile� Installation von Software auf
allen Clients notwendig
� Weitere „Sprache“ zum Datenbankzugriff
Server
ClientAnwendungsprogramm
IN
FO
RM
AT
IK
13
Datenbankserver (z.B. MySQL)
Anwendungsprogramm
Software-Architekturen – Webarchitektur
• Eine moderne 3-schichtige Webarchitektur…
Webclient (Browser)
Webserver (z.B. Apache und PHP)
Server
Server
Client
IN
FO
RM
AT
IK
14
Software-Architekturen – Webarchitektur
• Eine moderne 3-schichtige Webarchitektur…
Webclient (Browser)
Webserver (z.B. Apache und PHP)
Datenbankserver (z.B. MySQL)
Server
Server
Client
IN
FO
RM
AT
IK
15
Software-Architekturen – Webarchitektur
• Vorteile� Keine Installation von
zusätzlicher Software beim Client
• Nachteile� http-Protokoll ohne
Sessionverwaltung
Webclient (Browser)
Webserver (z.B. Apache und PHP)
Datenbankserver (z.B. MySQL)
Server
Server
Client
IN
FO
RM
AT
IK
16
Typische Hardware-Verteilung
• 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.
Datenbankserver
Webserver
Clients
IN
FO
RM
AT
IK
17
… und zum Testen / für die Fortbildung?
• Alle drei Schichten sind auf einem Rechner!
Datenbankserver
Webserver
Clients InternetExplorer
Apache
MySQL
ClientWebserver
Datenbankserver
IN
FO
RM
AT
IK
18
Das andere Extrem – eine Web Farm
HACMP Fail-Over
Production DB Server FailOver DB Server
2,4 TByte
FDDI Switch
32 GByte RAM
Load Balancer
Application Server Farm
je 4 Prozessoren
32 GByte RAM
IN
FO
RM
AT
IK
19
Arten von Datenbanken
• 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
• Beide Formen waren vor allem bei Großrechnern im Einsatz und werden zunehmend von relationalen Datenbanken abgelöst
IN
FO
RM
AT
IK
20
• Relationale Datenbanken� Die Daten werden in Form von Tabellen gespeichert
� Zwischen den Tabellen werden Beziehungen aufgebaut (Relationen)
• Empfehlung:� Einheitliche Begriffe nutzen,
� nicht zu nah an der mathematischen Betrachtung (Relationenalgebra)
Arten von Datenbanken
Name Einwohner Breite Laenge LandBerlin 3458763 52,52 13,41 Deutschland
Mainz 184752 50,00 8,27 Deutschland
Paris 2181300 48,86 2,35 Frankreich
Speyer 50600 49,31 8,43 Deutschland
Ort
Spalte (Merkmal, Attribut)
Datenwert (Attributwert)
Datensatz (Tupel)
Attributklasse
IN
FO
RM
AT
IK
21
Arten von Datenbanken
• Relationale Datenbanken – typische Vertreter� Oracle
� IBM (DB/2)
� Microsoft SQL Server (Access ?)
� Informix
� MySQL
� PostGreSQLOpenSource
IN
FO
RM
AT
IK
22
Was bietet mir eine Datenbank?
� Strukturierte Speicherung von Daten
� Verteilter, gleichzeitiger Zugriff mehrerer Benutzer / Programme
� Verwaltung von Zugriffsrechten
� ACID – Prinzip (Idee: Analogie zu Bank-Transaktionen)
� 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.
� Isolation• Transaktionen beeinflussen sich nicht gegenseitig.
� Durability• Eine Transaktion ist dauerhaft gespeichert, auch gegen Systemabstürze
gesichert.
Im Unterricht gut durch Rollenspiele zu veranschaulichen.
IN
FO
RM
AT
IK
23
500 €
ACID-Prinzip
• A – Atomicity
• Bsp.: Ein Kontosystem mit nicht überziehbaren Konten� Bob überweist Alice 500 €.
Die Transaktion bricht nach der Abbuchung von Bobs Konto ab.
Bob Alice
700 € 300 €
200 €
Die Transaktion darf nur „ganz oder gar nicht“ stattfinden.Konkret: Das Geld darf unterwegs nicht „verschwinden“
IN
FO
RM
AT
IK
24
1000 €
ACID-Prinzip
• C – Consistency
• Bsp.: � Bob will Alice 1000 € überweisen.
Die Transaktion überzieht sein Konto, was nicht erlaubt ist.
Bob Alice
700 € 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.)
1300 €
IN
FO
RM
AT
IK
25
400 €
ACID-Prinzip
• 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.
Bob Alice
700 € 300 €
-200 €
Jede Transaktion muss so ablaufen, dass parallel ablaufendeTransaktionen sie nicht stören können.
(zumindest logisch nacheinander ablaufen – serialisierbar)
500 €
Carol
700 €600 €
100 €
IN
FO
RM
AT
IK
26
Agenda
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Datenbanken – Wozu?
Abfragesprachesprache SQL
Datenbanken – Wozu?
IN
FO
RM
AT
IK
27
GF -
Le
hrp
lan
Sprachen zur Datenbankverwaltung
• 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.
IN
FO
RM
AT
IK
28
MySQL – Jetzt wird es (endlich) praktisch…
• Starten Sie den Datenbankserver und den Webserver
• Das Datenbanksystem bietet einen Service für andere Rechner an�Die Windows-Firewall kann dies melden.
Der Port muss freigegeben werden.
IN
FO
RM
AT
IK
29
Die erste Datenbank importieren
• Die Administration von MySQL funktioniert selbst schon am einfachsten über den Browser (oder Admin-Button in Control Panel)
�Webarchitektur http://localhost/phpmyadmin
Datenbankserver
Webserver
Client
Browser
Apache
MySQL
IN
FO
RM
AT
IK
30
Die erste Datenbank importieren
• Legen Sie eine neue Datenbank „terra1“ an.
• Wechseln Sie zur Datenbank (links), wählen Sie den Punkt „Importieren“ und suchen die Datei „terra1.sql“
IN
FO
RM
AT
IK
31
Die erste Tabelle…
• Klicken Sie auf „Struktur“: u.a. wird die Tabelle „ort“ angezeigt.� Lassen Sie sich den Inhalt der Tabelle anzeigen.
Tipp: Ändern Sie die Einstellungen von phpmyadmin, so dass nur die Icons angezeigt werden!
IN
FO
RM
AT
IK
32
Abfragen mit SQL
• 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.
• Syntax einer (einfachen) SQL-Abfrage:
SELECT [Spalten]
FROM[Tabelle]
WHERE[Bedingung]
ORDER BY [Attribute];
• Die WHERE- und die ORDER BY-Klausel sind optional.
IN
FO
RM
AT
IK
35
Name Einwohner Breite Laenge LandBerlin 3458763 52,52 13,41 Deutschland
Mainz 184752 50,00 8,27 Deutschland
Paris 2181300 48,86 2,35 Frankreich
Speyer 50600 49,31 8,43 Deutschland
… … … … …
Ort
� SELECT Name, Einwohner, LandFROM Ort
WHERE Einwohner > 1000000
Datenbankzugriff mit SQL - Ein erstes Beispiel
Millionenstädte ?
Ergebnistabelle
Name Einwohner LandBerlin 3458763 Deutschland
Paris 2181300 Frankreich
… … …
IN
FO
RM
AT
IK
36
SQL – WHERE
• Bedingungen mit Textattributen:� Name = ' Paris'
� Name LIKE 'P%' (Potsdam, Peine, Pirmasens)
� Name LIKE 'A_len' (Aalen, Ahlen)
• Bedingungen mit Zahlattributen:� Stufe =7 (gleich 7)
� Stufe <>7 (ungleich 7)
� Stufe <7 (kleiner 7)
� Stufe >7 (größer 7)
� Stufe <=7 (kleiner gleich 7)
� Stufe >=7 (größer gleich 7)
� Stufe BETWEEN7 AND 10 (zwischen 7 und 10)
IN
FO
RM
AT
IK
37
SQL – WHERE
• Vergleich auf Nullwert (kein Attributwert angegeben):Breite IS NULL
• Logische Verknüpfungen:� NOT (Land='Deutschland')
(Land nicht Deutschland)
� (Land='Deutschland') AND (Einwohner>100000)
(Millionenstädte in Deutschland)
� (Land='Deutschland') OR (Land='Schweiz')
(Orte in Deutschland und Schweiz)
IN
FO
RM
AT
IK
38
SQL – ORDER BY
• Das Abfrageergebnis kann sortiert werden:
SELECT [Spalten]
FROM [Tabelle]
WHERE [Bedingung]
ORDER 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
• Beispiele:SELECT * FROM Ort ORDER BY Name
SELECT * FROM Ort ORDER BY Land, Einwohner DESC
IN
FO
RM
AT
IK
39
Die ersten SQL Befehle
• Öffnen Sie das SQL-Fenster
1. Geben Sie alle Länder aus!
2. Geben Sie alle Länder aus:� Name, Einwohner, Hauptstadt
� Sortierung nach Einwohner absteigend
SELECT *FROM Land
SELECT Name, Einwohner, HauptstadtFROM Land
ORDER BY Einwohner DESC
IN
FO
RM
AT
IK
40
Aufgaben, Datenbank: terra1
3. Welche Länder liegen in Asien und Australien?
4. Welche Länder haben zwischen 10 und 100 Mio. Einwohner?� Absteigend nach Einwohner sortiert
5. Welche Länder haben einen „Arm“ oder ein „Bein“ im Namen?
WHERE Kontinent='Asien' ORKontinent='Australien'
WHERE Einwohner BETWEEN 10 AND 100ORDER BY Einwohner DESC
WHERE name like '%bein%'OR name like '%arm%'
IN
FO
RM
AT
IK
41
Hilfsfunktionen
• Wichtig sind vor allem Funktionen zur Manipulation von Strings und Datumsangaben, z.B.� DATEDIFF(D1, D2) Differenz (Tage)
� NOW() Aktuelle Zeit/Datum (SELECT CURDATE())
� …
• Wichtige Stringfunktionen� CONCAT(S1,S2,…) Verbinden von Strings
� LOWER(),UPPER() In Groß-/Kleinbuchstaben umwandeln
� CHAR_LENGTH() Länge in Zeichen
� MID(str,pos,len) String ausschneiden
� TRIM(s), LTRIM, RTRIM Abschneiden von Leerzeichen
� …
• Weitere Funktionen online in der Hilfe zu MySQL!
SQL ist hier nicht standardisiert � Die Funktionen sind bei anderen DBS oft unterschiedlich
IN
FO
RM
AT
IK
42
Aufgaben, Datenbank: terra1
5. Geben Sie die Länder in folgender Form aus:
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!
Lösung: Auslagerung in eine eigene Tabelle
Spalten können mit „AS“ umbenannt werden
CONCAT(UPPER(Name),' - ',Hauptstadt) AS Land ,round(Einwohner) AS "Mio. Einwohner"
IN
FO
RM
AT
IK
43
• Wozu dient die Spalte LNR in der Land-Tabelle ?
• … ein anderes Beispiel:� Suche nach der Hauptstadt „Berlin“
� Suche nach der Hauptstadt „Washington“
Exkurs: Primärschlüssel
Man benötigt noch das Land als Suchhilfe.
Selbst das Land reicht als Suchhilfe nicht aus.
IN
FO
RM
AT
IK
45
Relationale Datenbanken – Beziehungen
• 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.
KNR Name
EU EuropaAS AsienAF Afrika
Kontinent
LNR Name Einwohner Hauptstadt Kontinent
DK Dänemark 5.16 Kopenhagen EuropaD Deutschland 81.34 Berlin EuropaIND Indien 761.00 Delhi AsienRWA Rwanda 6.30 Kigali Afrika
LNR Name … KNR
DK Dänemark … EUD Deutschland … EUIND Indien … ASRWA Rwanda … AF
Land
Schlüsselattribut aus Kontinent
IN
FO
RM
AT
IK
46
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 Kontinentfläche) lassen sich meist mit geringem Aufwand realisieren.
� Die Struktur lässt flexiblere Abfragen zu.
KNR Name
EU EuropaAS AsienAF Afrika
Kontinent
LNR Name … KNR
DK Dänemark … EUD Deutschland … EUIND Indien … ASRWA Rwanda … AF
Land
Primärschlüssel aus Kontinent
IN
FO
RM
AT
IK
47
SQL – einfache Joins
• Müssen in SQL Daten aus mehreren Tabellen entnommen werden, so werden sog. „Joins“ gebildet.� 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.
LNR Name Einwohner KNR
DK Dänemark 5.16 EUD Deutschland 81.34 EUIND Indien 761.00 ASRWA Rwanda 6.30 AF
Land
KNR Name
EU EuropaAS AsienAF Afrika
Kontinent
IN
FO
RM
AT
IK
48
SQL – einfache Joins
1. Cross-Join („jede Zeile mit jeder“)� SELECT *
FROM Land, Kontinent
LNR Name Einwohner KNR KNR Name
DK Dänemark 5.16 EU EU EuropaDK Dänemark 5.16 EU AS AsienDK Dänemark 5.16 EU AF AfrikaD Deutschland 81.34 EU EU EuropaD Deutschland 81.34 EU AS AsienD Deutschland 81.34 EU AF AfrikaIND Indien 761.00 AS EU EuropaIND Indien 761.00 AS AS AsienIND Indien 761.00 AS AF Afrika… … … … …
LNR Name Einwohner KNR
DK Dänemark 5.16 EUD Deutschland 81.34 EUIND Indien 761.00 ASRWA Rwanda 6.30 AF
Land KNR Name
EU EuropaAS AsienAF Afrika
Kon
tine
nt
IN
FO
RM
AT
IK
49
WHERE Land.KNR= Kontinent.KNR
LNR Name Einwohner KNR KNR Name
DK Dänemark 5.16 EU EU EuropaDK Dänemark 5.16 EU AS AsienDK Dänemark 5.16 EU AF AfrikaD Deutschland 81.34 EU EU EuropaD Deutschland 81.34 EU AS AsienD Deutschland 81.34 EU AF AfrikaIND Indien 761.00 AS EU EuropaIND Indien 761.00 AS AS AsienIND Indien 761.00 AS AF Afrika… … … … …
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
SQL – einfache Joins
IN
FO
RM
AT
IK
50
AND Land.Einwohner > 10
SQL – einfache Joins
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
LNR Name Einwohner KNR KNR Name
DK Dänemark 5.16 EU EU EuropaDK Dänemark 5.16 EU AS AsienDK Dänemark 5.16 EU AF AfrikaD Deutschland 81.34 EU EU EuropaD Deutschland 81.34 EU AS AsienD Deutschland 81.34 EU AF AfrikaIND Indien 761.00 AS EU EuropaIND Indien 761.00 AS AS AsienIND Indien 761.00 AS AF Afrika… … … … …
IN
FO
RM
AT
IK
51
SQL – einfache Joins
3. Einschränken auf gesuchte Spalten.� Nur bestimmte Spalten werden ausgegeben.
� SELECT Land.Name, Land.Einwohner, Kontinent.NameFROM Land,Kontinent
WHERE Land.KNR = Kontinent.KNRAND Land.Einwohner > 10
Name Einwohner Name
Deutschland 81.34 EuropaIndien 761.00 Asien… … …
In der Realität versucht das DBMS, durch „geschicktes“ Vorgehen die Datenmenge schon früher zu reduzieren.
LNR Name Einwohner KNR KNR Name
D Deutschland 81.34 EU EU EuropaIND Indien 761.00 AS AS Asien… … … … …
IN
FO
RM
AT
IK
52
1. Geben Sie alle Kontinente mit ihren Ländern aus:� Name der Kontinente und Länder
� Sortierung nach Kontinent absteigend
2. In welchen Kontinenten gibt es Länder mit mehr als 100 Mio. Einwohner?
SELECT Kontinent.NameFROM Land, Kontinent
WHERE Land.KNR = Kontinent.KNRAND Land.Einwohner > 100
SELECT DISTINCT Kontinent.NameFROM Land, Kontinent
WHERE Land.KNR = Kontinent.KNRAND Land.Einwohner > 100
Aufgaben, Datenbank: terra2
SELECT Kontinent.Name, Land.NameFROM Kontinent, Land
WHERE Kontinent.KNR = Land.KNRORDER BY Kontinent.Name DESC
Problem: Es werden Duplikate angezeigt
Lösung: DISTINCT-Anweisung
IN
FO
RM
AT
IK
53
Erweiterung der Datenbank
• 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“.
ONR Name … LNR
BANGAL Bangalore … INDGOETTI Göttingen … DKARLSR Karlsruhe … DKOPENH Kopenhagen … DK
Ort
LNR Name … KNR
DK Dänemark … EUD Deutschland … EUIND Indien … ASRWA Rwanda … AF
Land
Schlüsselattribut aus Land
IN
FO
RM
AT
IK
54
Kontinent
Teil von
Land Ort
Teil von
Hauptstadt
Bisheriges „Schema“ der Datenbank
• Ein erstes „E/R-Modell“ (Entity/Relationship)
� eigentlich intuitiv zu lesen!?
Finden Sie die Stelle in der Datenbank terra3, an der
die „Hauptstadt“ abgespeichert ist?
IN
FO
RM
AT
IK
55
1. Geben Sie alle Orte mit ihren Ländern aus:� Name der Orte und Länder
� Sortierung nach Einwohnerzahl absteigend
2. Geben Sie alle Länder mit ihrer Hauptstadt aus!� Sortierung nach Kontinent und Land.
SELECT Kontinent.Name AS Kontinent, Land.Name, Ort. Name AS HauptstadtFROM Ort, Land, Kontinent
WHERE Ort.ONR = Land.HauptONRAND Land.KNR = Kontinent.KNR
ORDER BY Kontinent.Name, Land.Name
Aufgaben, Datenbank: terra3
SELECT Ort.Name, Ort.Einwohner, Land.Name AS LandFROM Ort, Land
WHERE Ort.LNR = Land.LNRORDER BY Ort.Einwohner DESC
IN
FO
RM
AT
IK
56
SQL – Tabellen-Alias
• 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:
� SELECT o.Name AS Stadt, l .Name AS Land, hs .NameFROM Ort o, Land l , Ort hs
WHERE o.LNR = l .LNRAND l .HauptONR = hs .ONRAND o.Einwohner>10000000
Der Alias-Name für Land ist nicht
notwendig (verkürzt die
Abfrage)
Land OrtTeil von HauptstadtOrt
IN
FO
RM
AT
IK
57
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)
• Wie sieht die Tabelle für die Flüsse aus?
FNR Name Laenge
ELB Elbe 1144MEK Mekong 4500MOS Mosel 544RHE Rhein 1320
Fluss
ONR Name
GOETTI GöttingenKARLSR KarlsruheKOBLEN Koblenz KOPENH Kopenhagen
Ort
ONR
HAMBURPHNOMPKOBLENKOBLEN
Problem: Für Flüsse müssen beliebig viele Orte eingetragen werden.
IN
FO
RM
AT
IK
58
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)
• Wie sieht die Tabelle für die Flüsse aus?
FNR Name Laenge
ELB Elbe 1144MEK Mekong 4500MOS Mosel 544RHE Rhein 1320
Fluss
ONR Name
GOETTI GöttingenKARLSR KarlsruheKOBLEN Koblenz KOPENH Kopenhagen
Ort
FNR
LEIRHERHE
Problem: Für Orte müssen mehrere Flüsse eingetragen werden.
Lösung: Auslagerung der Zuordnung in eine eigene Tabelle
IN
FO
RM
AT
IK
59
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.
FNR Name Laenge
ELB Elbe 1144MEK Mekong 4500MOS Mosel 544RHE Rhein 1320
Fluss
ONR Name
GOETTI GöttingenKARLSR KarlsruheKOBLEN Koblenz KOPENH Kopenhagen
Ort
FNR ONR
ELB HAMBURRHE KOBLENRHE KARLSRMOS KOBLEN
Stadtfluss
IN
FO
RM
AT
IK
60
Struktur der Datenbank terra4
Kontinent
Teil von
Land
Fluss
Ort
Teil von
durchfließt
n
1
1 n
Hauptstadt1 1 n
m
IN
FO
RM
AT
IK
61
Aufgaben, Datenbank: terra4
1. Welche Orte liegen an der Donau?
2. Welche Länder durchfließt die Donau?
3. Welche Flüsse fließen durch Deutschland?
SELECT o.NameFROM Ort o, Stadtfluss sf, Fluss f
WHERE o.ONR = sf.ONRAND sf.FNR = f.FNRAND f.Name = 'Donau'
SELECT DISTINCT l.NameFROM Land l, Ort o, Stadtfluss sf, Fluss f
WHERE l.LNR = o.LNRAND o.ONR = sf.ONRAND sf.FNR = f.FNRAND f.Name = 'Donau'
SELECT DISTINCT f.Name, f.LaengeFROM Fluss f, StadtFluss sf, Ort o, Land l
WHERE f.FNR = sf.FNRAND sf.ONR = o.ONRAND o.LNR = l.LNRAND l.Name = 'Deutschland'
IN
FO
RM
AT
IK
62
Gruppieren von Ergebnissen
• 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 lWHERE o.LNR = l.LNR
AND l.KNR = 'EU‘ORDER BY l.Name
Selbst zählen???NEIN!
ONR Name
AALBOR DänemarkKOPENH DänemarkBERLIN DeutschlandDUESSE DeutschlandMAINZ DeutschlandTALLIN Estland
IN
FO
RM
AT
IK
63
Gruppieren von Ergebnissen
• 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 lWHERE o.LNR = l.LNR
AND l.KNR = 'EU‘GROUP BY l.NameORDER BY l.Name
ONR Name
AALBOR DänemarkKOPENH DänemarkBERLIN DeutschlandDUESSE DeutschlandMAINZ DeutschlandTALLIN Estland
3
1
2
Name Anzahl
Dänemark 2Deutschland 3Estland 1
IN
FO
RM
AT
IK
64
Aufgaben, Datenbank: terra4
1. Wie viele Staaten haben Megacities (> 5 Mio. Einwohner) als Hauptstadt?
2. Zählen Sie die Staaten pro Kontinent!� Geben Sie die Kontinente nach der Anzahl absteigend aus.
SELECT COUNT(*)FROM Land L, Ort O
WHERE O.ONR = L.HauptONRAND O.Einwohner > 5000000
SELECT K.Name, COUNT(*) AS AnzahlFROM Land L, Kontinent K
WHERE L.KNR = K.KNRGROUP BY K.NameORDER BY Anzahl DESC
GROUP BY entfällt, wenn „nur“ gezählt
wird.
IN
FO
RM
AT
IK
65
Aufgaben, Datenbank: terra4
3. Wie viele Einwohner haben die Länder pro Kontinent im Schnitt?
4. Welche Städte liegen an mehr als einem Fluss?
SELECT k.Name AS Kontinent, AVG(l.Einwohner) AS SchnittFROM Kontinent k, Land l
WHERE k.KNR = l.KNRGROUP BY k.NameORDER BY k.Name
SELECT o.Name, COUNT(*) AS AnzahlFROM Ort o, StadtFluss sf
WHERE o.ONR = sf.ONRGROUP BY o.Name
HAVING COUNT(*)>1ORDER BY o.Name
Weitere BuiltIn-Funktionen:
MAX, MIN, SUM,…
IN
FO
RM
AT
IK
66
Struktur der kompletten Datenbank „miniterra“
Kontinent
Sprache
Teil von
gesprochen
Land
Fluss
Ort
Teil von
durchfließt
mündet
n
m
n
1
1 n
Hauptstadt1 1 n
m
1
n
benachbart
m
n
IN
FO
RM
AT
IK
67
Für Experten…
• Welche Städte liegen an mehr als einem Fluss?� Welche Flüsse sind es jeweils?
• Welche Länder grenzen an Deutschland?
SELECT o.Name, f.NameFROM Ort o, StadtFluss sf1, Fluss f
WHERE o.ONR = sf1.ONRAND f.FNR = sf1.FNRAND EXISTS (SELECT NULL FROM StadtFluss sf2
WHERE sf1.FNR<>sf2.FNR AND sf2.ONR=o.ONR)ORDER BY o.Name
SELECT l1.Name, l2.NameFROM Nachbarland n, Land l1, Land l2
WHERE (n.LNR1='D' OR n.LNR2='D')AND l1.LNR = n.LNR1AND l2.LNR = n.LNR2
IN
FO
RM
AT
IK
68
Für Experten…
• Welche Länder grenzen an Deutschland?� Geben Sie nur die Nachbarländer aus!
• Welche Flüsse münden in den Rhein?
SELECT IF(l1.LNR='D',l2.Name, l1.Name) AS "Direkte Nachbarn"FROM Nachbarland n, Land l1, Land l2
WHERE (n.LNR1='D' OR n.LNR2='D')AND l1.LNR = n.LNR1AND l2.LNR = n.LNR2
ORDER BY "Direkte Nachbarn"
SELECT fc.NameFROM Fluss fp, Fluss fc
WHERE fp.FNR = fc.ZielFNRAND fp.Name = 'Rhein'
ACHTUNG:Nicht genormt!
Selbstreferenz der Tabelle Fluss
IN
FO
RM
AT
IK
69
u.v.m.
• 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)
� …
IN
FO
RM
AT
IK
70
Agenda
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Datenbanken – Wozu?
Abfragesprachesprache SQL
Verwaltung MySQL
IN
FO
RM
AT
IK
71
Verwaltung des Datenbanksystems
• In einem Datenbanksystem müssen Rechte für verschiedene Benutzer verwaltet werden:� MySQL arbeitet nach der Installation ohne Schutz des Administrators
• 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
IN
FO
RM
AT
IK
72
Grundlegenden Schutz einrichten
• Das XAMPP-Paket erlaubt das Einrichten des Administrator-Passworts auf einfache Weise:� Rufen Sie http://localhost auf.
� Klicken Sie auf Sicherheitscheck.
� Im Link unter der Tabelle können wichtigeSicherheitslücken geschlossen werden.
• Setzen Sie zumindest das Passwort für den Administrator (root) und stellen Sie die Anmeldung um auf http.
dann:MySQL Server neu starten(XAMPP Control Panel)
IN
FO
RM
AT
IK
73
MySQL – Passwort für Administrator ändern
• phpMyAdmin in Browser starten� Menüpunkt „Rechte“
� Benutzer „root“ auswählen und bearbeiten
� Passwort eingeben und speichern
� Beim nächsten Aufruf von phpMyAdmin ist die Eingabe des neuen Passworts notwendig
IN
FO
RM
AT
IK
74
Benutzer / Rechte vergeben
• Ü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
Da phpmyadminimmer lokal auf dem Server läuft…
IN
FO
RM
AT
IK
Benutzer / Rechte vergeben
• Einem Benutzer können aber auch Rechte (z.B. nur Leserecht) auf zentralen Datenbanken wie miniterra vergeben werden:
75
IN
FO
RM
AT
IK
Problem: XAMPP-Rechte
• 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
�Manuelles Anpassen der Konfigurationsdatei httpd-xampp.conf nötig.
76
IN
FO
RM
AT
IK
Anpassen der Konfigurationsdatei httpd-xampp.conf
• 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.
� Danach muss der Webserver mit XAMPP Control Panel neu gestartet werden.
�Dies ist die „quick and dirty“-Methodeohne Beachtung eines Sicherheitskonzepts
�Die Anpassung ist für ein „öffentliches“System im Internet absolut ungeeignet!77
IN
FO
RM
AT
IK
78
Benutzer anlegen
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.
3. Passen Sie die Konfigurationsdatei httpd-xampp.conf an.
4. Versuchen Sie, mit diesem Benutzer auf die Datenbank des Nachbarn / der Nachbarin zuzugreifen.
IN
FO
RM
AT
IK
79
Skript zur Anlage der Datenbanken
• 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.
IN
FO
RM
AT
IK
80
Agenda
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Datenbanken – Wozu?
Verwaltung MySQL
Datenbankmodellierung
IN
FO
RM
AT
IK
81
Ein neues Informationssystem… Anforderungskatalog
Schulverwaltung
• 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.
IN
FO
RM
AT
IK
82
• 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.
• …
?
Datenmodell
Repräsentation der Informationen als strukturierte Daten
Modellbildung
• Um ein Informationssystem zu erstellen, muss ein Ausschnitt der realen Welt („Miniwelt“) im Computersystem erfasst werden.
Miniwelt
Unstrukturierte Informationen über die
Miniwelt
IN
FO
RM
AT
IK
83
Modellbildung
• 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
ExterneSicht
KonzeptionelleSicht
InternesModell
(z.B. relationalesDatenmodell)
IN
FO
RM
AT
IK
84
Konzeptionelles Modell – E/R-Diagramme
• E/R-Diagramme dienen dazu, das konzeptionelle Modell des Informationssystems zu erstellen. Es werden zwei Konstrukteverwendet:� 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ätstypenzusammengefasst� Symbol: Rechteck
Lehrer
IN
FO
RM
AT
IK
85
E/R-Diagramme - Entitätstyp
• Beispiel Entitätstyp:� Entität: Lehrer Lämpel, Kürzel Lä
� Entitätstyp: Menge aller Lehrer mit den Merkmalen Vorname,Nachname, Kürzel, …
� Primärschlüssel: Kürzel (?) (oder ein künstlicher Schlüssel)
Entitätstyp„Lehrer“
Kürzel: LäName: LämpelVorname: LudwigTitel: StD
3 Entitäten
Kürzel: KrName: KribichVorname: AlfredTitel: StR
Kürzel: LeName: LenkVorname: GertrudTitel: OStR'
IN
FO
RM
AT
IK
86
Klasse
E/R-Diagramme - Beziehungstyp
• Gleichartige Beziehungen zwischen Entitäten werden als ein Beziehungstyp zwischen den Entitätstypen definiert.� Symbol: Raute
• Beispiel:� Ein Lehrer führt Klassenfahrten durch.
Lehrer
Kuerzel: MeName: Merz
Kuerzel: KrName: Kribich
Kuerzel: LäName: Lämpel
Stufe: 12Teil: m3
Stufe: 11Teil: M1
Stufe: 8Teil: a
Entitätstyp Entitätstyp
Klassenfahrt
Beziehungstyp
Beziehungen
IN
FO
RM
AT
IK
87
Modellierung oft nicht eindeutig
• 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.
KlasseLehrer
führt durch Klassenfahrt nimmt
teil
IN
FO
RM
AT
IK
88
E/R-Diagramme - Attribute
• Die Eigenschaften aller Entitäten und Beziehungen eines Entitätstyps bzw. eines Beziehungstyps werden mit Hilfe von Attributen erfasst.� Symbol: Ellipse
• Beispiel:
KlasseLehrer Klassenfahrt
NameKürzel
Vornameidentifizierend
beschreibend
Teil
Anzahl
StufeDatum
Ziel
IN
FO
RM
AT
IK
89
E/R-Diagramme – Kardinalität
• Ein Beziehungstyp wird durch die Kardinalität genauer bezeichnet:� 1:n- Beziehung am Beispiel: Klassenzuordnung der Schüler
SchülerKlasse angehören
Name: 9a
Name: 9b
Name: 10d
Name: Müller Vorname: Yvonne
Name: Meier Vorname: Jan
Name: Dietz Vorname: Nicole
Name: Seiler Vorname: Manfred
Klasse Schüler
1 n
Ein Schüler gehört einer Klasse an. Einer Klasse gehören mehrere Schüler an.
IN
FO
RM
AT
IK
90
E/R-Diagramme – Kardinalität
• n:m – Beziehung am Beispiel: Kurszuordnung von Schülern in der MSS
SchülerKurs besucht
Fach: Mathe LK: ja
Fach: Deutsch LK: ja
Fach: Englisch LK: ja
Kurs Schüler
n m
Ein Kurs hat mehrere Schüler. Ein Schüler nimmt an mehreren Kursen teil.
Name: Müller Vorname: Yvonne
Name: Meier Vorname: Jan
Name: Dietz Vorname: Nicole
Name: Seiler Vorname: Manfred
IN
FO
RM
AT
IK
91
E/R-Diagramme – Kardinalität
• Die Kardinalitäten (1:1, 1:n, n:m) geben nur an, wie viele Entitäten maximal miteinander verbunden sind.
� 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.
� Bsp.: Eine Klasse hat minimal 8 und maximal 30 Schüler.
SchülerKlasse angehören1 n
SchülerKlasse angehören1 n
[8,30][1,1]
IN
FO
RM
AT
IK
92
Personen
E/R-Diagramme – Reflexive Beziehungstypen
• Beziehungen können auch auf dem gleichen Entitätstyp gelten, Selbstbeziehung oder Reflexive Beziehung.� Bsp.: Heirat
Name: Meier Vorname: Klaus
Name: Bach Vorname: Stefan
Name: Meier Vorname: Sabine
Name: Bach-Meier Vorname: Petra
Name: Hurtig Vorname: Hans
Personen Heirat1
1oder n:m ?
IN
FO
RM
AT
IK
94
Normalisierung von relationalen Schemata
• 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.
� Weitergehendes mit Beispielen in: http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
IN
FO
RM
AT
IK
95
Transformation E/R-Modell in relationales Schema
• Transformation von Entitätstypen� Für jeden Entitätstyp eine Tabelle
Schüler
NameSNR
Vorname
Schueler
SNR Name Vorname
E/R
-Mod
ell
Rel
atio
nale
s Sc
hem
a
IN
FO
RM
AT
IK
96
Transformation E/R-Modell in relationales Schema
• 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).
SchülerKlasse angehören1 n
NameSNR
Vorname
StufeKNR
Klasse
KNR Stufe
Schueler
SNR Name Vorname
E/R
-Mod
ell
Rel
atio
nale
s Sc
hem
a
KNR
IN
FO
RM
AT
IK
97
Transformation E/R-Modell in relationales Schema
• Transformation von n:m-Beziehungstypen� Einfügen eines Fremdschlüssels nicht möglich
� Separate Tabelle mit zwei Fremdschlüsseln
SchülerKurs besuchtn m
Schueler
SNR Name Vorname
NameSNR
Vorname
StufeKNR
Kurs
KNR Stufe
Fehlstunden
KNR
Besucht
Fehlst.SNR
E/R
-Mod
ell
Rel
atio
nale
s Sc
hem
a
IN
FO
RM
AT
IK
98
Transformation E/R-Modell in relationales Schema
• 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.
SchülerPrivatSchüler Zusatzdaten1 1
SchuelerPrivat
KonfessionSNR
Herkunft
NameSNRVorname
Schueler
SNR Name Vorname
E/R
-Mod
ell
Rel
atio
nale
s Sc
hem
a
HerkunftKonfessionSNR
IN
FO
RM
AT
IK
100
Tools für E/R-Diagramme
• DBDesigner4http://fabforce.net/dbdesigner4/
• MySQL Workbenchhttp://www.mysql.com/downloads/workbench/
• Ungetestet:� Mogwai ER-Designer
http://sourceforge.net/projects/mogwai/
� TOAD Data Modeler
� TOAD für MySQL http://toadformysql.com/index.jspa
• Oder doch einfach mit Office-Programmen…
IN
FO
RM
AT
IK
101
Aufgaben
• Erstellen Sie ein E/R-Modell und dann das relationale Schema.
„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.
IN
FO
RM
AT
IK
102
Bibliothek … Anforderungskatalog
• Erstellen Sie ein E/R-Modell und dann das relationale Schema.
„Bibliothek“
• In der Bibliothek müssen Bücher erfasst werden. Eine Suche ist möglich über Sachgebiet, Autor, Titel, Erscheinungsort und –jahr, Verlag.
• 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.
IN
FO
RM
AT
IK
103
Anfragen an die Bibliothek-Datenbank
• Eine Datenbank für das Schema kann fertig importiert werden!(Bibliotheks-DB_latin.sql)
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!(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?)
IN
FO
RM
AT
IK
104
Arztpraxis … Anforderungskatalog
„Arztpraxis“
• 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.
IN
FO
RM
AT
IK
105
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Datenbanken – Wozu?
Themenvernetzung (DB-Zugriff, etc.)
Agenda
Datenbankmodellierung
IN
FO
RM
AT
IK
106
Vernetzung des Themas - Auswertungen
• Grafische Auswertung der Datenbank(z.B. Anzahl der Länder pro Kontinent)� über Werkzeuge (z.B. Excel)
� über selbst erstellte Programme
IN
FO
RM
AT
IK
107
Vernetzung des Themas - Koordinatensystem
• Umrechnung Breiten/Längen-Angaben in BildschirmkoordinatenI
NF
OR
MA
TI
K
108
Vernetzung des Themas - Grenzen von SQL
• Problem: „Verfolgen eines Flusslaufes“� von der Quelle zum Meer
z.B. Spree
• Hier werden iterative Strukturenbenötigt, die SQL (als mengen-orientierte Sprache) im Standard nicht bietet.
� Anknüpfung an Programmierung� Python, PHP, Delphi, Java
Rhein
Mosel
Main
NeckarDonau
Isar
Inn
FuldaWerra
Weser
Elbe
HavelSpreeAller
Elbe
HavelSpree
Nordsee
IN
FO
RM
AT
IK
109
Vernetzung des Themas - Grenzen von SQL
• Problem: „Verfolgen eines Flusslaufes“� vom Meer zu den Zuflüssen
z.B. Nordsee
� Beispiel für eine (elegante?)rekursive Programmierung
Rhein
Mosel
Main
NeckarDonau
Isar
Inn
FuldaWerra
Weser
Elbe
HavelSpreeAller
Nordsee
Rhein
Mosel
Main
Neckar
FuldaWerra
Weser
Aller
Elbe
HavelSpree
Nordsee
IN
FO
RM
AT
IK
110
Zugriff auf MySQL über Python
• Grundlegende Schritte jedes Datenbankzugriffs am Beispiel Python und der Bibliothek MySQLDb:
1. Verbindung mit dem Datenbanksystem aufbauen
� 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.
Host='localhost'Benutzer='root'Passwort='sql'Datenbank='miniterra'conn = MySQLdb.connect (Host, Benutzer, Passwort, Datenbank )
IN
FO
RM
AT
IK
111
Zugriff auf MySQL über Python
2. Abfrage ausführen
� Ergebnis ist eine Variable (Handle), mit der auf das Resultat der Anfrage zugegriffen werden kann.
3. Ergebnisse ausgeben� Grundlegendes Problem
• 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. Tupel / Dictionary
ii. Speicherung der Ergebnisse auf Server / Client
iii. …
4. Freigabe der Handles
SQLText='SELECT Name, Breite, Laenge FROM …' cursor = conn.cursor(MySQLdb.cursors.DictCursor)cursor.execute(SQLText)
cursor.close()conn.close()
IN
FO
RM
AT
IK
Auslesen und Speicherneiner Ergebniszeile
112
Zugriff auf eine Spalte der Ergebniszeile
Zugriff auf MySQL über Python
row=cursor.fetchone()while (row!=None):
print(row["Name"], row["Laenge"], row["Breite"]))row = cursor.fetchone ()
• 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
Iteration über alle Zeilen
des Ergebnisses
IN
FO
RM
AT
IK
113
MySQL in Delphi
• 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.
� Lösung: Verzicht auf Borland-Datenbank-Komponenten und Verwendung freier Zugriffssoftware� MySQLDirect (http://www.sourceforge.net/projects/directsql)
• Units, die einen einfachen Datenbankzugriff erlauben
� Einsteiger-Tutorial dazu: http://www.delphi-treff.de/tutorials/
IN
FO
RM
AT
IK
114
Datenschutz
• Anknüpfungspunkt: www.schober.de
IN
FO
RM
AT
IK
115
Datenschutz
• Ein Blick ins Bundesdatenschutzgesetz:(http://bundesrecht.juris.de/bdsg_1990/ )
IN
FO
RM
AT
IK
116
Vernetzung des Themas - Datenschutz
• Datenbank-basierte Umfrage in der Schule� Wie können durch Verknüpfen (eigentlich harmloser) Daten neue
Informationen gewonnen werden?
IN
FO
RM
AT
IK
117
Abfragesprachesprache SQL
Verwaltung MySQL
Datenbankmodellierung
Themenvernetzung (DB-Zugriff, etc.)
Ausblick
Datenbanken – Wozu?
Themenvernetzung (DB-Zugriff, etc.)
Agenda
Ausblick
IN
FO
RM
AT
IK
118
Ausblick - Was hätte man noch alles behandeln können?
• 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
• Verändern der Datenbankinhalte mit SQL� INSERT / DELETE / UPDATE
• Verändern der Datenbankstruktur über Anweisungen� Data Definition Language (DDL)
• u.v.m.
IN
FO
RM
AT
IK
119
Software / Links
• XAMPP� http://www.apachefriends.org/de/xampp.html
• Deutsche MySQL Seite� http://www.mysql.de/ (englisch: http://mysql.com/)
• MySQL Workbench (mit XAMPP-Paket noch nicht voll nutzbar)
� http://www.mysql.com/downloads/workbench/
• PHP-Dokumentation (mit MySQL Funktionen)� http://www.php.net/download-docs.php
• Connectors (ODBC, JDBC, …) für MySQL� 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/
IN
FO
RM
AT
IK
120
Software / Links
• Material zur MySQL-Verwendung von Klaus Merkert� http://www.hsg-
kl.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
• u.v.m.
IN
FO
RM
AT
IK
121
Informationssysteme / Datenbankabfragen
FragenFragen
IN
FO
RM
AT
IK
122
Schemata der Beispieldatenbanken
• Schema zur Beispieldatenbank „schulverwaltung.sql“
Lehrer Faecher
Klassen
Klassenleiter
unterrichtet
n
m
n m
KBuchstabeKStufe Saal
LNR FNR FachName Vorname
IN
FO
RM
AT
IK
123
Schemata der Beispieldatenbanken
• Schema zur Beispieldatenbank „bibliothek.sql“
Buecher Ausleihe
BuchNr Autor …
Leser
LeserNr Nachname …
Leserprivat
LeserNr Geschlecht …
Ist Teil von
…zurueck
Verlag
VNR Name Ort
gibt heraus
n
1 1
1
n m
IN
FO
RM
AT
IK
124
Schemata der Beispieldatenbanken
• Schema zur Beispieldatenbank „wm2006.sql“
Begegnung
SchiedsrichterTeam
Land
Ort
Team1 Team2
n
11
n
1
1
1 1
n
n
1
Datum
Ergebnis