A-K-4 - Eltviller.de

Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-1
(bisher)
1
Anforderungen
2
Berichtgestaltung, Datenbasis, Verdichtung
3
Daten- und Datenbank- Design
(weiter mit)
4
Daten Retrieval
„Wo finde ich die gewünschte Information?“
„Wie erhalte ich die gewünschte Information aus der Datenbank?“
„Problem“: Wie erfahre ich, welche Kunden welchen Wagen gemietet haben?
Beispiel: Mietwagen
Mietwagen
Mietvertrag
Kunde
 Mietwagennr
 Mietvertragsnr
 Kundennr
 Hersteller
 Mietwagennr
 Name
 Typ
 Kundennr
 Adresse
 Baujahr
 Preis
 Fuhrparkchef
 Farbe
 von
 Kundengruppe
 ...
 bis
 ...
 ...
Liste aller Kunden, die den Wagen WI-ER-16 gemietet haben:
Sortiert nach Kunden-Namen
Prof. Dr. J. Weinberg
4.1
IuK-Systeme A - SS 2000
Seite 4-2
Programme zum Datenretrieval (früher)
Erstellen eines Programmes mit einer prozeduralen Programmiersprache ( 3. Generation,
zB Cobol, C, Pascal, PL/1, Basic, ...)
 Programm Ablaufplan (Pseudo Code) s. u.
 Programmierung in der jeweiligen Programmiersprache
 Erfordert gute Programmierkenntnisse („handwerkliches Geschick“)
 Aufwendig (auch für geübte Programmierer)
Beispiel
Mietvertrag lesen
Vergleichen ob
MW-Nr = WI-ER-16
JA
Kundensatz lesen mit Kundennr
aus dem Mietvertrag
NEIN
Letzter Mietvertrag
Schreiben des Kundensatzes
in Hilfsdatei
NEIN
JA
Lies Hilfdateisatz
Drucke Hilfsdateisatz
(=Kundenstammsatz)
Letzter Hilfsdateisatz
NEIN
JA
ENDE
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-3
Übung:
Wie muß das Programm (Ablaufplan ergänzt werden, wenn auch der Mietwagen Typ
und die Farbe ausgedruckt werden soll ?
Was passiert, wenn der Kunde den (selben) Mietwagen 2x gemietet hat?
Prozedural: Genaue Anweisungen wie die Daten gesucht und verarbeitet werden:
 Satzweises lesen der Datei
 Selektion des Datensatzes
 ggf. lesen weiterer Dateien
 aufbereiten
 ausgeben
4.2
SQL-Abfragen
Heutige Technik: SQL-Abfrage
 S = Structured
 Q = Query (sprich „kwieri“)
 L = Language
„Umgangssprachlicher“ Ausdruck, der von der Datenbank verarbeitet wird
 Ergebnis:
 Datensätze
 Nicht mehr „wie“ sondern „was“
Bemerkung:
 SQL = Programmiersprache der 4. Generation (ergebnisorientiert)
 Oft mit „benutzerfreundlicher“ Oberfläche ( ACCESS)
 Die Datenbankstruktur muß dem Benutzer bekannt sein
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-4
Folgende Angaben müssen erfolgen
was:
welche Felder (Attribute)
select
„Wähle Felder .... „
woher:
aus welchen Dateien (Objekten)
from
„aus Datei ...“
welche:
Auswahl (Filter)
where
„für die gilt ...“
wie:
Sortierung
order by
„sortiert nach den Feldern ...
auf-/absteigend“
Beispiel:
Alle Kunden aus Seeheim-Jugenheim: Kundenname, Adresse und Telefonnummer
nach Namen absteigend sortiert:
Select Name, Straße, PLZ, Ort, Telefonnummer
From Kunde
Where PLZ=64342
Order by Name DESC (descending=absteigend)
Übungsbeispiel: Alle Kunden aus Wiesbaden?
Seit der Umstellung der Postleitzahlen ist die Zuordnung Stadt  PLZ nicht mehr gegeben:
PLZ: xx nnn, wobei xx für eine Region steht und nnn eine laufende Nummer pro
Zustellbezirk ist
( Probleme der Umstellung bei vielen Vertriebsinformationssystemen)
Bsp: Wiesbaden:
65183, 65185, 65187, 65189, 65191, 65193, 65195, 65197, 65199,
65201, 65203, 65205, 65207
 Bei „WHERE“ sind Formeln („Boolsche Operatoren“) möglich
WHERE PLZ=65183 OR PLZ=65185 OR PLZ=65187 ...
Alternativ ein Bereich: 65183 < PLZ < 65207
WHERE PLZ>65183 AND PLZ<65207
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-5
Diese Beispiele waren vom
 Typ A: Abfrage über eine Datei (Tabelle)
Das Mietwagenbeispiel (s.o) erfordert jedoch SQL-Abfragen vom
 Typ B: Abfrage über mehrere Dateien (Tabellen)
Beispiel:
Liste aller Kunden, die den Mietwagen WI-ER 16 gemietet haben, sortiert nach Kundennamen
absteigend. Angabe von Kundenname, Telefonnr., Mietpreis, Typ, Mietdauer von bis.
 Information ist in drei Dateien „verstreut“!
Lösung „virtuelle Dateien“, die die Information „zur Laufzeit“ zur Verfügung stellen.
4.3
Views / JOIN
Auch:
„logische Datei“, „virtuelle Datei/Tabelle“, „Join-Datei“ oder „externes Schema“
Hinweis: Bei ACCESS heißen Views „Abfragen“
Eine View ist eine „logische Sicht“ auf die Datenbank:
 virtuelle Datei (virtuelle Tabelle)
 Kombination von einer oder mehreren Dateien (Tabellen)
 Auswahl bestimmter Felder (Attribute) + ggf. neue „Rechenfelder“
 Selektion (Filter)
 Sortierung
Eine View wird zur „Laufzeit“ (d.h. bei Benutzung) als temporäre Datei erstellt und kann wie
eine „normale“ Datei/Tabelle benutzt werden.
Eine View enthält also keine Daten, wie eine „physische“ Datei, sondern nur eine Anweisung,
wie die „virtuelle“ Datei erzeugt werden soll.
 Basis einer View ist eine SQL-Abfrage
oder anders ausgedrückt
 SQL-Abfragen erzeugt Views
Bei einfachen SQL-Abfragen (Typ A, nur eine Tabelle) kann man sich die View als einen
„Ausschnitt“ der Tabelle vorstellen.
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-6
Views, die sich aus mehreren Tabellen zusammensetzen werden über eine spezielle Operation
(SQL-Befehl) gebildet
 JOIN ... ON
Sie erscheinen wie eine neue „große“ Tabelle, die alle gewünschten Felder enthält.
Beispiel: Mietwagen
Liste aller Kunden, die den Mietwagen WI-ER 16 gemietet haben, sortiert nach Kundennamen
absteigend. Angabe von Kundenname, Telefonnr., Mietpreis, Typ, Mietdauer von bis.
Name
Telefonnr
Preis
Typ
Miete von
bis
Maier
0611-19194
153,00
BWM
1.12.1997
12.12.1997
Mayer
069-787989
325,00
BMW
13.5.1997
14.5.1997
Schmidt
0611-89874
125,00
BMW
12.11.1997
14.11.1997
Beim Join muß angegeben werden:
 welche Dateien
 über welche Schlüssel
die Datensätze verbunden werden
Beispiel für Vertreter / Kunde
SELECT Kundenname, Kundenort, Vertretername
FROM Kunde
JOIN Vertreter ON Kunde.Vertreternr = Vertreter.Vertreternr
WHERE Vertretername=’Müller’ OR Vertretername=’Maier’
ORDER BY Kundenname ASC (ascending=aufsteigend)
Ergebnisbeispiel:
Kundenname
Kundenort
Vertretername
Adam
Wiesbaden
Müller
Beyer
Leverkusen
Müller
Certa
Mannheim
Maier
Düsentrieb
Entenhausen
Müller
Erlenemayer
München
Maier
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-7
Bemerkung:
Die exakte Syntax (=Schreibweise) unterscheidet sich bei den unterschiedlichen Datenbanken,
muß jedoch (für die spezielle Datenbank) genau eingehalten werden!
Viele SQL-Datenbanken besitzen eine „benutzerfreundliche“ Oberflächen, mit der die
Abfrage erzeugt werden kann, oft „Query by Example“ genannt. Die Benutzung scheitert in
der Praxis jedoch vielfach daran, daß die Benutzer aus den Fachabteilungen die
Datenbankstruktur (E-R Modell) nicht kennen oder verstehen. ( „Entwurfsansicht“ bei
ACCESS)
Bei ACCESS wird ein JOIN in der Regel dadurch erzeugt, daß die entsprechenden Dateien in
das „Tabellenfenster“ der Abfrage gebracht werden (Entwurfsansicht). Wenn die
„Beziehungen“ korrekt definiert wurden, wird der JOIN automatisch hergestellt. Die Angaben
zu Select, Where, Order by werden implizit durch das weitere Ausfüllen der Entwurfsansicht
festgelegt. Das SQL-Statement wird im Hintergrund hieraus automatisch erzeugt und kann
über Ansicht  SQL angesehen und ggf. modifiziert werden.
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-8
Beispiel (Aufgabe 19) Projektabrechnung ( Übung 6: Datenbank „Projekt.mdb“)
E-R Modell (Ausschnitt)
View
Das zugehörige SQL-Statement (SQL-Befehl): (Wähle: Ansicht  SQL)
SELECT DISTINCTROW Kunde.[Name der Firma], Mitarbeiter.Name, Projekt.[Telefonnr
beim Kunden], Projekt.Stundenverrechnungssatz
FROM Kunde
INNER JOIN (Mitarbeiter INNER JOIN Projekt ON Mitarbeiter.Personalnr =
Projekt.Mitarbeiter) ON Kunde.Kundennr = Projekt.Kunde
ORDER BY Kunde.[Name der Firma], Mitarbeiter.Name;
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Beispiel Auftragskopf / Auftragsposition mit Rechenfeld „Betrag“:
( Übung 7: Datenbank „Auftrag.mdb“
Auftragsbestätigung über eine virtuelle Tabelle („View“, Abfrage“ s.u.)
Rechenfeld: Betrag: Preis * Menge *(1-Positionsrabatt)
Seite 4-9
Prof. Dr. J. Weinberg
4.4
IuK-Systeme A - SS 2000
Seite 4-10
Inner JOIN / Outer JOIN
Beispiel „Vertreter / Kunde“: Was wird (soll) passieren, wenn kein „Partner“ vorhanden ist?
 Nur Kunden und Vertreter mit „Partner“
 Alle Kunden (ggf N/V-Vermerk  Null-Wert)
 Alle Vertreter
Übung: mehrere Szenarien in denen die Alternativen „sinnvoll“ sind.
Bsp: Ehe nur Ehepaare, alle Männer mit ggf. Ehefrauen, alle Frauen mit ggf. Ehemänner
Inner Join:
In der View nur die Datensätze, bei denen bezüglich aller Beziehungen ein
entsprechender „Partner“-Datensatz vorhanden ist.
Outer Join:
Eine Primärdatei, die vollständig abgearbeitet wird. Eine oder mehrere
Sekundärdateien, deren Felder ggf. leer bleiben (oder mit einem N/V-Vermerk).
Beispiel: Projekte, Kundenlisten, ...
Sonderfall:
Bei einigen Datenbanken (zB DB2,) ist es auch möglich eine „inverse“ Join-Operation
durchzuführen, d. h. alle Datensätze der Primädatei, die keinen gültigen Verweis auf eine
Sekundärdatei haben.
Beispiel: Alle Kunden ohne gültigen Vertreter.
Beispiel: in ACCESS kann dies durch eine Outer-Join Abfrage realisiert werden, mit einer
Selektion auf den Primärschlüssel der Sekundärdatei „=NULL“
 „Assistent zur Inkonsistenzsuche
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Beispiel ACCESS:
Alle Mitarbeiter ohne gültige Abteilung ( Musterlösung zu Übung6)
Seite 4-11
Prof. Dr. J. Weinberg
4.5
IuK-Systeme A - SS 2000
Seite 4-12
Gruppierungen
Oft stellt sich in der Praxis das Problem, nicht einzelne Datensätze anzuzeigen, sondern
vielmehr
 gleichartige Datensätze zusammenfassen
und Berechnungen für die Gruppe durchzuführen ( „Aggregatfunktionen“).
Beispiel:
 Summe aller Umsätze eines Kunden
 Anzahl aller bei einem Kunden eingesetzten Berater
 Maximaler Einkaufspreis eines Artikels
 Durchschnittlicher Einkaufspreis einer Warengruppe
 ...
Hierzu stehen in SQL „Aggregatfunktionen“ zur Verfügung, die in einer View benutzt werden
können:
 Summe (SUM)
 MIN
 MAX
 Mittelwert (AVG)
 Std. Abweichung (STDDEV)
 Anzahl (COUNT)
 ...
Als Ergebnis enthält die View einen Datensatz pro Gruppe mit dem Ergebnis der
Aggregatfunktion.
Beispiel: pro Kunde ein Datensatz mit dem Jahresumsatz.
Nun kann bei Bedarf eine weitere Selektion und oder Sortierung auf die „Ergebnisfelder“
erfolgen.
Beispiel: Hitliste
Kundenliste mit Jahresumsatz größer 10 Mio. DM, absteigend sortiert nach Umsatz.
Beispiel: Sortimentsübersicht(summarisch)
Anzahl aller Artikel pro Warengruppe, sortiert nach Warengruppen
(oder ... sortiert nach Anzahl Artikel).
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-13
SQL-Befehle zum „Gruppieren“
GROUP BY
„Fasse alle Datensätze zusammen, die den gleichen
Feldinhalt von ... haben“
HAVING
„Jedoch nur, wenn das Ergebnisfeld die folgende
Bedingung erfüllt ...“
Beispiel: Anzahl aller Artikel pro Warengruppe:
Select Warengruppe, COUNT()
From Artikel
Group by Warengruppe
Order by Warengruppe ASC
Übung: ... absteigend nach Anzahl Artikel.
Realisierung in ACCESS:
Bemerkung: Der „Entwurf“ ist noch nicht vollständig, die Sortierung muß noch gewählt
werden.
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-14
Wenn zusätzlich die Warengruppe im „Klartext“ erscheinen soll, ist ein „Join“ auf die Tabelle
„Warengruppe“ erforderlich:
Bemerkung: Auch in diesen „Screen-shots“ fehlt noch die Sortierung.
Beispiel: „Hitliste“: Kunden nach Gesamtumsatz (lfd. Jahr) absteigend, jedoch nur mit
Gesamtumsatz > 10.000.000:
Select Kunde, SUM(Umsatz)
From Auftrag
Where Auftragsdatum >= 1.1.1997
Group by Kunde
HAVING SUM(Umsatz) >10000000
Order by SUM(Umsatz) DESC (descending=absteigend)
Prof. Dr. J. Weinberg
IuK-Systeme A - SS 2000
Seite 4-15
Beispiel: Alle Mitarbeiter, die an mehr als einem Projekt arbeiten nach Anzahl
Projekten absteigend
Select Mitarbeiternr, COUNT()
From Projekt
Group by Mitarbeiternr
Having COUNT() > 1
Order by COUNT() DESC
Das Beispiel mit ACCESS ( Übung 6) „joint“ darüber hinaus den Mitarbeiternamen
aus „Mitarbeiter“
Weitere Beispiele mit ACCESS: siehe Übung 6 und Übung 7.