A-04 - Eltviller.de
Werbung
Prof. Dr. J. Weinberg 3 IuK - Systeme Kurs A - WS 1999 Daten- und Datenbank- Design (Fortsetzung) 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 Übergang Entity-Relationship Modell Datenbank „Wie werden die Objekte im Rechner gespeichert?“ Aus der E-R Modell wird die Struktur der Datenbank abgeleitet: Objekt Datei (Tabelle) Primärschlüssel Attribut Feld Fremdschlüssel Formaler Aufbau der Datei: Dateibeschreibung (in ACCESS: „Entwurf“) In der Datei werden die „konkret existierenden“ Objekte gespeichert Datensätze Bemerkung: In der Terminologie wird in der Praxis i.d.R. nicht zwischen der formalen Beschreibung der Objekte den konkreten Objekten unterschieden. Beispiel: Das Objekt Student ist beschrieben durch die Attribute und Schlüssel (Felder): Die „konkreten Objekte“ (gespeicherten Studenten) sind: Seite 66 Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 67 Hinweis: Beim Erstellen der Datenbank sind ergänzende Festlegungen erforderlich formale Feldbeschreibungen (s.u.) Integritätsregeln („Gültigkeitsprüfungen“, s.u.) 3.9 Relationale Datenbanken Bemerkung: Es gibt weitere „Typen“ von Datenbanken: z.B. „hierarchische Datenbanken“ oder „Netzwerk Datenbanken“, die jedoch zunehmend auf relationale Datenbanken umgestellt werden. In der Praxis handelt es sich dabei entweder um „Altlasten“ oder es gibt im Einzelfall technische Gründe („Performance“ = „Zugriffsgeschwindigkeit“) für diese speziellen Lösungen. Die Verwaltung und der Zugriff auf die Daten erfordert bei diesen Datenbanken i.d.R „Spezialwissen“ und ist „EDV-Technikern“ vorbehalten. Eine relationale Datenbank wird durch „Tabellen“ (Dateien) gebildet, die „normalisiert“ sind, d.h. folgenden Regeln entsprechen: redundanzfrei (keine mehrfache Speicherung gleicher Information) keine „Defekte“ (Anomalien) bei Speichern, Löschen und Verändern der Datensätze „korrekte“ Beschreibung des Ausschnitts der Realität Diese Anforderungen werden „automatisch“ erfüllt, wenn der Datenbank ein korrektes Entity-Relationship Modell zugrunde liegt! (inhaltlicher Ansatz) In der Praxis werden aus „Performance“ - Gründen im Einzelfall gezielt redundante Daten gespeichert ( „Vorverdichtung“) In der Literatur findet sich i.d.R. ein theoretischer Ansatz: „1. - 5. Normalform“, der eine mathematisch formale Methode darstellt, die Anforderungen zu erfüllen. Eine Weiterentwicklung der relationalen Datenbanken sind „Objektorientierte Datenbanken“, die sich bislang in der Praxis nur selten finden. (siehe z.B. A. Maier, Wüst, Th: Objektorientierte Datenbanken) Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 68 Formal besteht eine relationale Datenbank aus folgenden Elementen. Datenbank (Database, Zusammenfassung aller Dateien eines Systems) Datei (Tabelle) (file, Zusammenfassung aller gleichartigen Datensätze) Datensatz (record, logische Einheit = „konkretes Obekt“ Datenfeld (kleinste Informationseinheit) (Skizze 16) Felder entsprechen Attributen, Primär- und Fremdschlüsseln des E-R Modells. Sie werden durch folgende Angaben festgelegt (Feldbeschreibung): Feldname ( Attribut) Typ Länge Integritätsregeln Typ (Bsp): Zeichen („Character“) Zahl (Ganzzahl (Integer), Dezimalzahl, Fließkomma, ..) Objekt (Graphik, Audio, Video, ...) Länge: Zeichen: (max) Anzahl Zeichen Ganzzahl (Integer). (max) Anzahl Stellen Dezimalzahl: Anzahl Vor-, Nachkommastellen Fließkommazahl: Anzahl „signifikanter Stellen“ Integritätsregeln (Auswahl) Zweck: Eingabe- oder Verarbeitungsfehler automatisch entdecken Abhängig vom Feld, anderen Feldern, anderen Dateien, ... werden automatisch vom Datenbank Verwaltungsprogramm überprüft Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 69 Abhängig vom Feld Wertebereich Eingabe erforderlich: „Muß-Feld“ ( Fehler), „ Soll-Feld“ ( Hinweis) Beispiele Wertebereich: von - bis: Preis zwischen 1,00 und 20,00 DM, Gewicht > 50, ... zulässige Werte: Haarfarbe blond, braun, schwarz, grün, ... Formel Beispiele „Eingabe erforderlich“: Kundenname muß eingegeben werden Vertreternummer „soll“ eingegeben werden, wenn nicht Hinweis, der bestätigt werden muß Abhängig von anderen Felder (der selben Datei) Wertebereich Eingabe erforderlich: „Muß-Feld“ ( Fehler), „ Soll-Feld“ ( Hinweis) Beispiele: Wenn Preis < 100 dann Rabatt < 3 %, sonst < 5 % Wenn Warengruppe = ABC dann Preis zwischen 50 und 500 DM Wenn Vertreter zugeordnet Provisionssatz erforderlich Abhängig von Datensätzen und Feldern anderer Dateien Wertebereich Eingabe erforderlich: „Muß-Feld“ ( Fehler), „ Soll-Feld“ ( Hinweis) referentielle Integrität (s.u.) Beispiele: Personalstamm. Wenn Ehegatte berufstätig dann Steuerklasse III, IV, V oder VI Auftrag: Wenn Kunde im „feindlichen“ Ausland und Produkt „Kriegswaffe“ (zB Verschlüsselungssoftware) dann Ausfuhrgenehmigung erforderlich Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 70 3.10 Referentielle Integrität Bei einer Referenz muß das bezogene Objekt vorhanden sein: „gültiger Fremdschlüssel“ (Skizze 16 Vertreter - Kunde: Der durch die Vertreter Nr eindeutig bestimmte Vertreter muß existieren) Die referentielle Integrität kann durch folgende Aktionen verletzt werden: falsche Eingabe ( Prüfung) Löschen des bezogen Objektes Verändern des Primärschlüssels des bezogen Objektes ( falsche Modellierung!) Beispiel: Welche Konsequenz hat das Ausscheiden des Vertreters Maier (Vertreternr. 4711)? Löschen des Vertreters: (Stammsatz) „hartes“ / „weiches“ Löschen Was geschieht mit den (ehemaligen) Kunden von „4711“ Möglichkeiten: Ersatz: Müller (4813) statt Maier (immer?) automatisch / manuell vorläufig kein Vertreter „Null-Wert“ (n/a, n/v, nicht zugeordnet) Hinweis: Unterschied „Null-Wert“ von Wert=0 (Beispiel: Preis!) Hinweis: „Trigger“ können ggf. die referentielle Integrität „überwachen“ und je nach „Entscheidungsregel“ Warnungen oder Hinweise geben, automatische Aktionen veranlassen oder im „Extremfall“ eine „Rückabwicklung“ (Roll-Back“) durchführen. Prof. Dr. J. Weinberg 4 IuK - Systeme Kurs A - WS 1999 Seite 71 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 4.1 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) Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 72 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 Ü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? Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 73 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 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“ Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 74 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 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“! Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 75 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. 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 Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Schmidt 125,00 0611-89874 BMW Seite 76 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 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 Prof. Dr. J. Weinberg IuK - Systeme Kurs A - WS 1999 Seite 77 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 Kurs A - WS 1999 Seite 78 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 Kurs A - WS 1999 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 79 Prof. Dr. J. Weinberg 4.4 IuK - Systeme Kurs A - WS 1999 Seite 80 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 Kurs A - WS 1999 Beispiel ACCESS: Alle Mitarbeiter ohne gültige Abteilung ( Musterlösung zu Übung6) Seite 81 Prof. Dr. J. Weinberg 4.5 IuK - Systeme Kurs A - WS 1999 Seite 82 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 Kurs A - WS 1999 Seite 83 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 Kurs A - WS 1999 Seite 84 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 Kurs A - WS 1999 Seite 85 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.
