Informationssysteme / Datenbankabfragen

1. Informationssysteme / Datenbankabfragen Thomas Mohr

2. Datenbanken – Wozu? Datenbanken – Wozu? Abfragesprachesprache SQL Verwaltung MySQL Datenbankmodellierung Zugriff auf Datenbanken (PHP & Co.) Ausblick Agenda

3. Darstellung PC „Logik“ Anwendungsprogramm Datenbasis Software-Architekturen: „Standalone“-Programm • z.B. • selbst programmierte Schülerdatei • in Delphi programmierte Übersicht der WM-Begegnungen

4. 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) Darstellung PC „Logik“ Anwendungsprogramm Datenbasis Software-Architekturen: „Standalone“-Programm

5. Darstellung Darstellung Anwendungsprogramm „Logik“ Client „Logik“ Anwendungsprogramm Datenbasis Datenbasis (Datenbank)Server Server Software-Architekturen - Client-Server • z.B. • Outlook und Exchange-Server • „einfache“, datenbank-basierte Schülerverwaltung

6. Darstellung Anwendungsprogramm „Logik“ Client Datenbasis (Datenbank)Server Server Software-Architekturen - Client-Server • z.B. • Outlook und Exchange-Server • „einfache“, datenbank-basierte Schülerverwaltung

7. 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 Darstellung Anwendungsprogramm „Logik“ Client Datenbasis (Datenbank)Server Server Software-Architekturen - Client-Server

8. Darstellung Client Webclient (Browser) Darstellung Anwendungsprogramm „Logik“ „Logik“ Webserver (z.B. Apache und PHP) Server Datenbasis Datenbankserver (z.B. MySQL) Server Software-Architekturen – Webarchitektur • Eine moderne 3-schichtige Webarchitektur…

9. Software-Architekturen – Webarchitektur • Eine moderne 3-schichtige Webarchitektur… Darstellung Client Webclient (Browser) „Logik“ Webserver (z.B. Apache und PHP) Server Datenbasis Datenbankserver (z.B. MySQL) Server

10. Vorteile Keine Installation von zusätzlicher Software beim Client Nachteile http-Protokoll ohne Sessionverwaltung Software-Architekturen – Webarchitektur Darstellung Client Webclient (Browser) „Logik“ Webserver (z.B. Apache und PHP) Server Datenbasis Datenbankserver (z.B. MySQL) Server

11. Clients Webserver Datenbankserver 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.

12. Clients Webserver Datenbankserver … und zum Testen / für die Fortbildung? • Alle drei Schichten sind auf einem Rechner! InternetExplorer Client Webserver Datenbankserver Apache MySQL

13. 2,4 TByte Das andere Extrem – eine Web Farm Load Balancer Application Server Farm je 4Prozessoren HACMP Fail-Over Production DB Server FailOver DB Server 32 GByte RAM 32 GByte RAM FDDI Switch

14. Informationssystem DatenbanksystemDBS Erfassung Datenbankmanagementsystem DBMS, z.B. MySQL Speicherung Analyse Darstellung Datenbank (Datenbasis) Datenbank (Datenbasis) Was ist ein Informationssystem ? • Ein Informationssystem kann auf formalisierte Fragen eines Anwenders Antworten aus einer gegebenen Datenmenge geben • Komponenten eines Informationssystems:

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

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

17. Attribut (Merkmal) Attributwert (Datenwert) Attributklasse Datensatz (Tupel) Arten von Datenbanken • 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) Begegnung Team1 Team2 Datum Ergebnis Deutschland Costa Rica 9.6.2006 4:2 Polen Ecuador 9.6.2006 0:2 Deutschland Polen 14.6.2006 1:0 Ecuador Costa Rica 16.6.2006 3:0

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

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

20. Datenbanken – Wozu? Datenbanken – Wozu? Abfragesprachesprache SQL Abfragesprachesprache SQL Verwaltung MySQL Datenbankmodellierung Zugriff auf Datenbanken (PHP & Co.) Ausblick Agenda

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

22. Browser Client Apache Webserver MySQL Datenbankserver Die erste Datenbank importieren • Die Administration von MySQL funktioniert selbst schon am einfachsten über den Browser • Webarchitektur http://localhost/phpmyadmin

23. Die erste Datenbank importieren • Legen Sie eine neue Datenbank „wm-stufe1“ an. • Wählen Sie den Punkt „Importieren“ und suchen die Datei„wm-stufe1.sql“

24. Die erste Tabelle… • Klicken Sie auf „Struktur“: Die Tabelle „begegnung“ wird angezeigt. • Lassen Sie sich den Inhalt der Tabelle anzeigen.

25. Schlüsselattribut 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. Nummer der Begegnung • wird meist durch Unterstreichung gekennzeichnet. Begegnung BNR Team1 Team2 Datum Ergebnis A1 Deutschland Costa Rica 9.6.2006 4:2 A2 Polen Ecuador 9.6.2006 0:2 A3 Deutschland Polen 14.6.2006 1:0 A4 Ecuador Costa Rica 16.6.2006 3:0

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

27. SQL – SELECT und FROM • Eine Projektion schränkt die Abfrage hinsichtlich der Attribute ein (die Spalten der resultierenden Tabelle): SELECT [Spalten] FROM [Tabelle] WHERE [Bedingung] ORDER BY [Attribute]; • Die darzustellenden Spalten der Tabelle müssen durch Kommata getrennt angegeben werden; * steht für „alle Spalten“. • Beispiele: • SELECT * FROM Begegnung; • SELECT Team1 FROM Begegnung; • SELECT Team1, Team2 FROM Begegnung;

28. SQL – WHERE • Eine Selektion schränkt die Abfrage hinsichtlich der Datensätze ein (die Zeilen der resultierenden Tabelle): SELECT [Spalten] FROM [Tabelle] WHERE [Bedingung] ORDER BY [Attribute]; • Die Abfrage enthält alle Datensätze, welche die angegebene Bedingung erfüllen. • Beispiele: • SELECT * FROM Begegnung WHERE (Team1='Deutschland') • SELECT * FROM Begegnung WHERE (Datum>='2006-07-01')

29. Team1 Team2 Ergebnis Deutschland Costa Rica 4:2 Deutschland Polen 1:0 Ergebnistabelle Veranschaulichendes Beispiel • SELECT Team1, Team2, Ergebnis FROM BegegnungWHERE (Team1='Deutschland') Begegnung BNR Team1 Team2 Ort Ergebnis A1 Deutschland Costa Rica München 4:2 A2 Polen Ecuador Gelsenkirchen 0:2 A3 Deutschland Polen Dortmund 1:0 F4 Brasilien Australien München 2:0

30. SQL – WHERE • Bedingungen mit Textattributen: • Team1='Deutschland' (Schmitt) • Team1 LIKE 'P%' (Paraguay, Polen, Portugal) • Name LIKE “M_ller“ (Müller, Muller, Miller, Maller) • 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)

31. SQL – WHERE • Vergleich auf Nullwert (kein Attributwert angegeben): SNR IS NULL • Logische Verknüpfungen: • NOT (Name='Schmitt') (Name nicht Schmitt) • (Ort='Berlin') AND (Datum<'2006-07-01') (Spiele in Berlin vor Juli 2006) • (Team1='Deutschland') OR (Team2='Deutschland') (Deutschland spielt als erstes oder zweites Team)

32. 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 Begegnung ORDER BY Datum • SELECT * FROM Begegnung ORDER BY Ort, Datum DESC

33. Die ersten SQL Befehle • Öffnen Sie das SQL-Fenster • Geben Sie alle Begegnungen aus! • Geben Sie alle Begegnungen aus: • Datum, Name der Teams und Ergebnis • Sortierung nach Datum absteigend SELECT * FROM Begegnung SELECT Datum, Team1, Team2, Ergebnis FROM Begegnung ORDER BY Datum DESC

34. Problem: Datumsformat unterscheidet sich vom deutschen Format Lösung: Konvertierung mit Datumsfunktion für die Ausgabe Aufgaben, Datenbank: wm-stufe1 • An welchen Spielen war Deutschland beteiligt? • Welche Spiele fanden zwischen dem 10.6.2006 und 15.6.2006 statt? • Absteigend nach Datum sortiert WHERE Team1='Deutschland' OR Team2='Deutschland' WHERE Datum BETWEEN '2006-06-10' AND '2006-06-15' ORDER BY Datum DESC SELECT Team1, Team2, DATE_FORMAT(Datum,'%d.%m.%Y') FROM ...

35. SQL ist hier nicht standardisiert  Die Funktionen sind bei anderen DBS oft unterschiedlich 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!

36. Problem: München wird unterschiedlich geschrieben! Lösung: Auslagerung in eine eigene Tabelle Spalten können mit „AS“ umbenannt werden Aufgaben, Datenbank: wm-stufe1 • Geben Sie die Spiele in folgender Form aus: • Welche Spiele fanden in München statt? CONCAT(UPPER(LEFT(Team1,3)),'-',UPPER(LEFT(Team2,3)),': ',Ergebnis) AS Spiel,DATE_FORMAT(Datum,'%d.%m.%Y') AS 'Wann' WHERE Ort='München'

37. BNR Team1 Team2 Ort A1 Deutschland Costa Rica München A2 Polen Ecuador Gelsenkirchen A3 Deutschland Polen Dortmund F4 Brasilien Australien München Begegnung Ort BNR Team1 Team2 ONR A1 Deutschland Costa Rica MUE A2 Polen Ecuador GEL A3 Deutschland Polen DOR F4 Brasilien Australien MUE ONR Name MUE München GEL Gelsenk. DOR Dortmund Schlüsselattribut aus Ort Relationale Datenbanken – Beziehungen • 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.

38. Relationale Datenbanken – Beziehungen • Zwischen den Tabellen bestehen Beziehungen (Relationen) • z.B. Spielort der Begegnung • 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. Master Detail PKey Data FKey PKey Data

39. 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 Begegnungen ausgegeben werden, die in Stadien mit mehr als 60.000 Sitzplätzen gespielt wurden. Begegnung Ort BNR Team1 Team2 ONR A1 GER CRC MUE A2 POL ECU GEL A3 GER POL DOR F4 BRA AUS MUE ONR Name Sitze MUE München 66000 GEL Gelsenk. 52000 DOR Dortmund 65000

40. BNR Team1 Team2 ONR A1 GER CRC MUE A2 POL ECU GEL A3 GER POL DOR F4 BRA AUS MUE BNR Team1 Team2 ONR ONR Name Sitze A1 GER CRC MUE MUE München 66000 A1 GER CRC MUE GEL Gelsenk. 52000 A1 GER CRC MUE DOR Dortmund 65000 A2 POL ECU GEL MUE München 66000 A2 POL ECU GEL GEL Gelsenk. 52000 A2 POL ECU GEL DOR Dortmund 65000 F4 BRA AUS MUE MUE München 66000 F4 BRA AUS MUE GEL Gelsenk. 52000 … … … … … … … SQL – einfache Joins • Cross-Join („jede Zeile mit jeder“) • SELECT * FROM Begegnung, Ort ONR Name Sitze MUE München 66000 GEL Gelsenk. 52000 DOR Dortmund 65000 Ort Begegnung

41. WHERE Begegnung.ONR = Ort.ONR BNR Team1 Team2 ONR ONR Name Sitze A1 GER CRC MUE MUE München 66000 A1 GER CRC MUE GEL Gelsenk. 52000 A1 GER CRC MUE DOR Dortmund 65000 A2 POL ECU GEL MUE München 66000 A2 POL ECU GEL GEL Gelsenk. 52000 A2 POL ECU GEL DOR Dortmund 65000 F4 BRA AUS MUE MUE München 66000 F4 BRA AUS MUE GEL Gelsenk. 52000 … … … … … … … SQL – einfache Joins • Einschränken auf „passende“ Datensätze. • 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

42. AND Ort.Sitze > 60000 BNR Team1 Team2 ONR ONR Name Sitze A1 GER CRC MUE MUE München 66000 A1 GER CRC MUE GEL Gelsenk. 52000 A1 GER CRC MUE DOR Dortmund 65000 A2 POL ECU GEL MUE München 66000 A2 POL ECU GEL GEL Gelsenk. 52000 A2 POL ECU GEL DOR Dortmund 65000 F4 BRA AUS MUE MUE München 66000 F4 BRA AUS MUE GEL Gelsenk. 52000 … … … … … … … SQL – einfache Joins • Einschränken auf „passende“ Datensätze (2). • 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

43. Team1 Team2 Name GER CRC München BRA AUS München … … … In der Realität versucht das DBMS, durch „geschicktes“ Vorgehen die Datenmenge schon früher zu reduzieren. SQL – einfache Joins • Einschränken auf gesuchte Spalten. • Nur bestimmte Spalten werden ausgegeben. • SELECT Begegnung.Team1, Begegnung.Team2, Ort.Name FROM Begegnung, Ort WHERE Begegnung.ONR = Ort.ONRAND Ort.Sitze > 60000 BNR Team1 Team2 ONR ONR Name Sitze A1 GER CRC MUE MUE München 66000 F4 BRA AUS MUE MUE München 66000 … … … … … … …

44. Problem: Es werden Duplikate angezeigt Lösung: DISTINCT-Anweisung Aufgaben, Datenbank: wm-stufe2 • Geben Sie alle Begegnungen mit dem Ort der Austragung aus: • Datum, Name der Teams und Name des Orts • Sortierung nach Datum absteigend • Wo fanden Spiele mit deutscher Beteiligung statt? • Name und Sitzplätze der Stadien SELECT Datum, Team1, Team2, Ort.Name FROM Begegnung, Ort WHERE Ort.ONR=Begegnung.ONR ORDER BY Datum DESC SELECT DISTINCT Name, Sitze FROM Begegnung, Ort WHERE Ort.ONR=Begegnung.ONR AND ( Begegnung.Team1='Deutschland' OR Begegnung.Team2='Deutschland') SELECT Name, Sitze FROM Begegnung, Ort WHERE Ort.ONR=Begegnung.ONR AND ( Begegnung.Team1='Deutschland' OR Begegnung.Team2='Deutschland')

45. BNR TNR1 TNR2 ONR A1 GER CRC MUE A2 POL ECU GEL A3 GER POL DOR F4 BRA AUS MUE Begegnung Team TNR Name Trainer GER Deutschland Klinsmann POL Polen Janas CRC Costa Rica Guimaraes ECU Ecuador Suarez … Relationale Datenbanken – Beziehungen • 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 Deutschland Costa Rica MUE A2 Polen Ecuador GEL A3 Deutschland Polen DOR F4 Brasilien Australien MUE

46. SQL – Tabellen-Alias • Soll in SQL auf eine Tabelle mehrfach zugegriffen werden, so kann dies mit Alias-Namen geschehen: • Alle Begegnungen mit ihren Teams und Trainern • 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)

47. SQL – Spaltennamen • Es werden nun Spalten mit gleichen Namen ausgegeben • Spaltennamen können umbenannt werden. • 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

48. Komplette Struktur der Datenbank Ort ONR Name ONR Sitze TNR1 Begegnung SNR TNR2 Datum Ergebnis TNR TNR Team Schiedsrichter Name Trainer Vorname LNR TNR LNR LNR Land TNR in Team ist Primär- und Fremdschlüssel Name Einwohner Kontinent

49. Datum Komplette Struktur der Datenbank als E/R-Diagramm Ort 1 n Begegnung n n Team1 Team2 Ergebnis 1 1 1 Team Schiedsrichter n 1 Land 1 1

50. Aufgaben, Datenbank: wm-stufe4 • Welche Teams kommen aus Ländern, die weniger als 10 Mio. Einwohner haben? • Geben Sie alle Begegnungen aus • Teamnamen, Schiedsrichter, Ergebnisse • nach Datum geordnet SELECT Land.Name, Land.Einwohner * 1000000 AS Einwohnerzahl, Team.Trainer FROM Team, Land WHERE Team.TNR = Land.LNR AND Land.Einwohner<10 Rechenoperationen sind erlaubt. 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 ORDER BY B.Datum Der Join kann direkt auf das Land erfolgen, da von Team nichts benötigt wird.