Informationssysteme / Datenbankabfragen

I N F O R M A T I K

Informationssysteme / Datenbankabfragen

Thomas Mohr

2

IN

FO

RM

AT

IK

Agenda

Abfragesprachesprache SQL

Verwaltung MySQL

Datenbankmodellierung

Ausblick

Datenbanken – Wozu?Datenbanken – Wozu?

3

IN

FO

RM

AT

IKInformationssysteme

4

IN

FO

RM

AT

IK

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

5

IN

FO

RM

AT

IK

Was ist ein Informationssystem ?

• 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

6

IN

FO

RM

AT

IK

Ein Ausgangspunkt im Unterricht…

• Schüler führen eine Internet-Recherche durch:„Nachbarländer Deutschlands – Fläche, Einwohner, Hauptstadt“ Nutzung eines Informationssystems Unterschiedliche Darstellung der Informationen

7

IN

FO

RM

AT

IK

Software-Architekturen: „Standalone“-Programm

• z.B. selbst programmierte Schülerdatei in Delphi programmierte Übersicht von Länderinformationen

PCDarstellung

Anwendungsprogramm „Logik“

Datenbasis

8

IN

FO

RM

AT

IK

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)

PCDarstellung


Datenbasis

9

IN

FO

RM

AT

IK

(Datenbank)Server Datenbasis

Darstellung


Datenbasis

Software-Architekturen - Client-Server

• z.B. Outlook und Exchange-Server „einfache“, datenbank-basierte Schülerverwaltung

Server

ClientDarstellung

Anwendungsprogramm„Logik“

10

IN

FO

RM

AT

IK


• z.B. Outlook und Exchange-Server „einfache“, datenbank-basierte Schülerverwaltung


Server

ClientDarstellung


11

IN

FO

RM

AT

IK



• 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

ClientDarstellung


12

IN

FO

RM

AT

IK

Datenbankserver (z.B. MySQL) Datenbasis

Darstellung


Software-Architekturen – Webarchitektur

• Eine moderne 3-schichtige Webarchitektur…

Webclient (Browser) Darstellung

Webserver (z.B. Apache und PHP) „Logik“

Server

Server

Client

13

IN

FO

RM

AT

IK


• Eine moderne 3-schichtige Webarchitektur…

Webclient (Browser)

Webserver (z.B. Apache und PHP)

Datenbankserver (z.B. MySQL)

Darstellung

„Logik“

Datenbasis

Server

Server

Client

14

IN

FO

RM

AT

IK


• 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)

Darstellung

„Logik“

Datenbasis

Server

Server

Client

15

IN

FO

RM

AT

IK

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

16

IN

FO

RM

AT

IK

… und zum Testen / für die Fortbildung?

• Alle drei Schichten sind auf einem Rechner!

Datenbankserver

Webserver

Clients InternetExplorer

Apache

MySQL

ClientWebserver

Datenbankserver

17

IN

FO

RM

AT

IK

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

18

IN

FO

RM

AT

IK

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

19

IN

FO

RM

AT

IK • Relationale Datenbanken

Die Daten werden in Form von Tabellen gespeichert Zwischen den Tabellen werden Beziehungen aufgebaut

(Relationen)

Objektrelationale Datenbanken• erweitern die relationalen Datenbanken und objektorientierte

Funktionen (z.B. Vererbung)

Name Einwohner Hauptstadt Kontinent

Dänemark 5165000 Kopenhagen EuropaDeutschland 81338000 Berlin EuropaIndien 761000000 Delhi AsienRwanda 6300000 Kigali Afrika


Attribut (Merkmal, Spalte)

Attributwert (Datenwert)

Datensatz (Tupel)Attributklas

se

Land

20

IN

FO

RM

AT

IK


• Relationale Datenbanken – typische Vertreter Oracle IBM (DB/2) Microsoft SQL Server (Access ?) Informix MySQL PostGreSQL

OpenSource

21

IN

FO

RM

AT

IK

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 Isolation

• Transaktionen beeinflussen sich nicht gegenseitig Durability

• Eine Transaktion ist dauerhaft gespeichert, auch gegen Systemabstürze gesichert

22

IN

FO

RM

AT

IK

Agenda


Verwaltung MySQL


Datenbanken – Wozu?



Ausblick

23

IN

FO

RM

AT

IK

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.

24

IN

FO

RM

AT

IK

Die erste Datenbank importieren

• Die Administration von MySQL funktioniert selbst schon am einfachsten über den Browser Webarchitektur http://localhost/phpmyadmin

Datenbankserver

Webserver

Client

Browser

Apache

MySQL

25

IN

FO

RM

AT

IK

Die erste Datenbank importieren

• Legen Sie eine neue Datenbank „terra1“ an.

• Wählen Sie den Punkt „Importieren“ und suchen die Datei„terra1.sql“

26

IN

FO

RM

AT

IK

Die erste Tabelle…

• Klicken Sie auf „Struktur“: Die Tabelle „land“ wird angezeigt.

• Lassen Sie sich den Inhalt der Tabelle anzeigen.

27

IN

FO

RM

AT

IK

LNR Name Einwohner Hauptstadt Kontinent

DK Dänemark 5.16 Kopenhagen EuropaD Deutschland 81.33 Berlin EuropaIND Indien 761.00 Delhi AsienRWA Rwanda 6.30 Kigali Afrika

Land

Relationale Datenbank – Aufbau

• 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. eine Landeskennung

wird meist durch Unterstreichung gekennzeichnet.

Schlüsselattribut

28

IN

FO

RM

AT

IK

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.

31

IN

FO

RM

AT

IK


DK Dänemark 5.16 Kopenhagen EuropaD Deutschland 81.34 Berlin EuropaIND Indien 761.00 Delhi AsienRWA Rwanda 6.30 Kigali Afrika… … … … …

Land

SELECT Name, Einwohner, Kontinent FROM Land WHERE (Einwohner > 10)

Veranschaulichendes Beispiel

Länder mit über 10 Mio.

Einwohner

Ergebnistabelle

Name Einwohner Kontinent

Deutschland 81.34 EuropaIndien 761.00 Asien… … …

32

IN

FO

RM

AT

IK

SQL – WHERE

• Bedingungen mit Textattributen: Name = 'Deutschland' Name LIKE 'P%' (Paraguay, Polen,

Portugal) Name LIKE 'I_land' (Irland, Island)

• 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 BETWEEN 7 AND 10 (zwischen 7 und 10)

33

IN

FO

RM

AT

IK

SQL – WHERE

• Vergleich auf Nullwert (kein Attributwert angegeben):LNR IS NULL

• Logische Verknüpfungen: NOT (Kontinent='Europa')

(Kontinent nicht Europa)

(Kontinent='Europa') AND (Einwohner<1)

(Zwergstaaten in Europa)

(Kontinent='Europa') OR (Kontinent='Asien')

(Länder in Eurasien)

34

IN

FO

RM

AT

IK

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 Land ORDER BY Name

SELECT * FROM Land ORDER BY Kontinent, Einwohner DESC

35

IN

FO

RM

AT

IKDie 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, Hauptstadt FROM Land ORDER BY Einwohner DESC

36

IN

FO

RM

AT

IK

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

WHERE Kontinent='Asien' OR Kontinent='Australien'

WHERE Einwohner BETWEEN 10 AND 100 ORDER BY Einwohner DESC

37

IN

FO

RM

AT

IK

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

38

IN

FO

RM

AT

IK


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"

39

IN

FO

RM

AT

IK

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


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

40

IN

FO

RM

AT

IK

Relationale Datenbanken – Beziehungen

• Zwischen den Tabellen bestehen Beziehungen (Relationen) z.B. Kontinent eines Landes

• 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.

PKey DataPKey Data FKey

Master Detail

41

IN

FO

RM

AT

IKSQL – 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


Kontinent

42

IN

FO

RM

AT


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

Lan

d KNR Name


Kon

tin

en

t

43

IN

FO

RM

AT

IK

WHERE Land.KNR= Kontinent.KNR



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

44

IN

FO

RM

AT

IK

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



45

IN

FO

RM

AT


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

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.


D Deutschland 81.34 EU EU EuropaIND Indien 761.00 AS AS Asien… … … … …

46

IN

FO

RM

AT

IK 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.Name FROM Land, Kontinent WHERE Land.KNR = Kontinent.KNR AND Land.Einwohner > 100

SELECT DISTINCT Kontinent.Name FROM Land, Kontinent WHERE Land.KNR = Kontinent.KNR AND Land.Einwohner > 100


SELECT Kontinent.Name, Land.Name FROM Kontinent, Land WHERE Kontinent.KNR = Land.KNR ORDER BY Kontinent.Name DESC

Problem: Es werden Duplikate angezeigt

Lösung: DISTINCT-Anweisung

47

IN

FO

RM

AT

IK

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

48

IN

FO

RM

AT

IK

Kontinent

Teil von

Land Ort

Teil von

Hauptstadt

Bisheriges „Schema“ der Datenbank

• Ein erstes „E/R-Modell“ eigentlich intuitiv zu lesen!?

Finden Sie die Stelle in der Datenbank terra3,

an der die „Hauptstadt“ abgespeichert ist?

49

IN

FO

RM

AT

IK 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 Hauptstadt FROM Ort, Land, Kontinent WHERE Ort.ONR = Land.HauptONR AND Land.KNR = Kontinent.KNR ORDER BY Kontinent.Name, Land.Name


SELECT Ort.Name, Ort.Einwohner, Land.Name AS Land FROM Ort, Land WHERE Ort.LNR = Land.LNR ORDER BY Ort.Einwohner DESC

50

IN

FO

RM

AT

IKSQL – 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.Name FROM Ort o, Land l, Ort hs WHERE o.LNR = l.LNR AND l.HauptONR = hs.ONR AND o.Einwohner>10000000

Der Alias-Name für Land ist nicht

notwendig (verkürzt die

Abfrage)

Land OrtTeil von HauptstadtOrt

51

IN

FO

RM

AT

IK


• 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 NameLaenge

ELB Elbe 1144MEK Mekong 4500MOS Mosel 544RHE Rhein 1320

Fluss

ONR Name

GOETTI GöttingenKARLSR KarlsruheKOBLEN Koblenz KOPENHKopenhagen

Ort

ONR

HAMBURPHNOMPKOBLENKOBLEN

Problem: Für Flüsse müssen beliebig viele Orte eingetragen werden.

52

IN

FO

RM

AT

IK



(z.B. Koblenz)

• Wie sieht die Tabelle für die Flüsse aus?

FNR NameLaenge


Fluss

ONR Name


Ort

FNR

LEIRHERHE

Problem: Für Orte müssen mehrere Flüsse eingetragen werden.

Lösung: Auslagerung der Zuordnung in eine eigene Tabelle

53

IN

FO

RM

AT

IK



(z.B. Koblenz) Zuordnungstabelle mit Schlüsseln aus beiden

Haupttabellen.

FNR NameLaenge


Fluss

ONR Name


Ort

FNR ONR

ELBHAMBURRHE KOBLENRHE KARLSRMOS KOBLEN

Stadtfluss

54

IN

FO

RM

AT

IK

Struktur der Datenbank terra4

Kontinent

Teil von

Land

Fluss

Ort

Teil von

durchfließt

n

1

1 n

Hauptstadt1 1 n

m

55

IN

FO

RM

AT

IK


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.Name FROM Ort o, Stadtfluss sf, Fluss f WHERE o.ONR = sf.ONR AND sf.FNR = f.FNR AND f.Name = 'Donau'

SELECT DISTINCT l.Name FROM Land l, Ort o, Stadtfluss sf, Fluss f WHERE l.LNR = o.LNR AND o.ONR = sf.ONR AND sf.FNR = f.FNR AND f.Name = 'Donau'

SELECT DISTINCT f.Name, f.Laenge FROM Fluss f, StadtFluss sf, Ort o, Land l WHERE f.FNR = sf.FNR AND sf.ONR = o.ONR AND o.LNR = l.LNR AND l.Name = 'Deutschland'

56

IN

FO

RM

AT

IK

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 l WHERE 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

57

IN

FO

RM

AT

IK

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 l WHERE o.LNR = l.LNR AND l.KNR = 'EU‘ GROUP BY l.Name ORDER BY l.Name

ONR Name

AALBOR DänemarkKOPENH DänemarkBERLIN DeutschlandDUESSE DeutschlandMAINZ DeutschlandTALLIN Estland

3

1

2

NameAnzahl

Dänemark 2Deutschland 3Estland 1

58

IN

FO

RM

AT

IK


1. Zählen Sie die Staaten pro Kontinent! Geben Sie die Kontinente nach der Anzahl absteigend aus.

2. Wie viele Staaten haben Megacities (> 5 Mio. Einwohner) als Hauptstadt?

SELECT COUNT(*) FROM Land L, Ort O WHERE O.ONR = L.HauptONR AND O.Einwohner > 5000000

SELECT K.Name, COUNT(*) AS Anzahl FROM Land L, Kontinent K WHERE L.KNR = K.KNR GROUP BY K.Name ORDER BY Anzahl DESC

GROUP BY entfällt, wenn „nur“ gezählt

wird.

59

IN

FO

RM

AT

IK


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 Schnitt FROM Kontinent k, Land l WHERE k.KNR = l.KNR GROUP BY k.Name ORDER BY k.Name

SELECT o.Name, COUNT(*) AS Anzahl FROM Ort o, StadtFluss sf WHERE o.ONR = sf.ONR GROUP BY o.NameHAVING COUNT(*)>1 ORDER BY o.Name

Weitere BuiltIn-Funktionen:

MAX, MIN, SUM,…

60

IN

FO

RM

AT

IK

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

61

IN

FO

RM

AT

IK

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.Name FROM Ort o, StadtFluss sf1, Fluss f WHERE o.ONR = sf1.ONR AND f.FNR = sf1.FNR AND EXISTS (SELECT NULL FROM StadtFluss sf2 WHERE sf1.FNR<>sf2.FNR AND sf2.ONR=o.ONR) ORDER BY o.Name

SELECT l1.Name, l2.Name FROM Nachbarland n, Land l1, Land l2 WHERE (n.LNR1='D' OR n.LNR2='D') AND l1.LNR = n.LNR1 AND l2.LNR = n.LNR2

62

IN

FO

RM

AT

IK

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.LNR1 AND l2.LNR = n.LNR2 ORDER BY "Direkte Nachbarn"

SELECT fc.Name FROM Fluss fp, Fluss fc WHERE fp.FNR = fc.ZielFNR AND fp.Name = 'Rhein'

ACHTUNG:Nicht genormt!

Selbstreferenz der Tabelle Fluss

63

IN

FO

RM

AT

IK

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) …

64

IN

FO

RM

AT

IK

Agenda


Verwaltung MySQL




Verwaltung MySQL

Ausblick

65

IN

FO

RM

AT

IK

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

66

IN

FO

RM

AT

IK

MySQL – Passwort für Administrator vergeben

• 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';

67

IN

FO

RM

AT

IK

MySQL – Passwort für Administrator vergeben

• 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

Passworts notwendig

68

IN

FO

RM

AT

IK

Benutzer / Rechte vergeben

• Ü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

69

IN

FO

RM

AT

IK

Benutzer / Rechte vergeben

• 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

70

IN

FO

RM

AT

IK

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. Versuchen Sie, mit diesem Benutzer auf die Datenbank des Nachbarn / der Nachbarin zuzugreifen.

71

IN

FO

RM

AT

IK

Agenda


Verwaltung MySQL



Verwaltung MySQL


Ausblick

72

IN

FO

RM

AT

IK

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.

73

IN

FO

RM

AT

IK

• 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

74

IN

FO

RM

AT

IK

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)

75

IN

FO

RM

AT

IK

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 z.B. eine Person, ein Gegenstand, ein Ereignis

• Entitäten mit gleichen Eigenschaften werden zu Entitätstypen zusammengefasst Symbol: Rechteck

Lehrer

76

IN

FO

RM

AT

IK

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'

77

IN

FO

RM

AT

IK

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

78

IN

FO

RM

AT

IK

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

79

IN

FO

RM

AT

IK

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

80

IN

FO

RM

AT

IK

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.

81

IN

FO

RM

AT

IK


• 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

82

IN

FO

RM

AT

IK


• 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


[8,30][1,1]

83

IN

FO

RM

AT

IK

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 ?

85

IN

FO

RM

AT

IK

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

86

IN

FO

RM

AT

IK

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

Rela

tion

ale

s Sch

em

a

87

IN

FO

RM

AT

IK


• 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).


NameSNR

Vorname

StufeKNR

Klasse

KNR Stufe

Schueler

SNR Name Vorname

E/R

-Mod

ell

Rela

tion

ale

s Sch

em

a

KNR

88

IN

FO

RM

AT

IK


• 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

Rela

tion

ale

s Sch

em

a

89

IN

FO

RM

AT

IK


• 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

Rela

tion

ale

s Sch

em

a

HerkunftKonfessionSNR

91

IN

FO

RM

AT

IK

Tools für E/R-Diagramme

• DBDesigner4http://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

• Oder doch einfach mit Office-Programmen…

92

IN

FO

RM

AT

IK

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.

93

IN

FO

RM

AT

IK

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.

94

IN

FO

RM

AT

IK

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?)

95

IN

FO

RM

AT

IK

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.

96

IN

FO

RM

AT

IK

Ausblick

Agenda


Verwaltung MySQL




Ausblick

97

IN

FO

RM

AT

IK

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 PHP, Delphi, Java

Rhein

Mosel

Main

NeckarDonau

Isar

Inn

FuldaWerra

Weser

Elbe

HavelSpreeAller

Elbe

HavelSpree

Nordsee

98

IN

FO

RM

AT

IK

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

99

IN

FO

RM

AT

IK

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.

100

IN

FO

RM

AT

IK

Software / Links

• 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

• Connectors (ODBC, JDBC, …) für MySQL http://www.mysql.de/downloads/connector/

101

IN

FO

RM

AT

IK

Software / Links

• Material zur MySQL-Verwendung von Klaus Merkert http://www.hsg-kl.de/faecher/inf/material/datenbanken/mysq

l/index.php

• Material zu DB allgemein von OSZ Handel, Berlin http://oszhdl.be.schule.de/gymnasium/faecher/informatik/dat

enbanken/index.htm

• u.v.m.

I N F O R M A T I K

Informationssysteme / Datenbankabfragen

Thomas Mohr

Documents

Informationssysteme / Datenbankabfragen