Informationsbestände analysieren Datenabfrage mit QBE 4. Datenabfrage mit QBE11 4.1. QBE vs. SQL Relationale Datenbanken haben schon früh den Anspruch gestellt, auch für NichtInformatiker nutzbar zu sein. Dazu hat man in den 70er Jahren die Sprache SQL12 definiert. Obwohl die Sprache einen sehr logischen Aufbau aufweist – Sie werden sich im Verlauf diese Moduls noch mit SQL beschäftigen – ist sie doch recht schwer zu formulieren. Aus diesem Grund wurde von Microsoft für das kommerzielle Produkt Access eine einfachere Lösung gesucht und mit QBE auch gefunden. Hierbei ist das Formulieren der Anfragen in einem graphischen Fenster mittels Drag-and_drop möglich. 4.1.1. Struktur der DB Um Anfragen an die DB stellen zu können, müssen alle Tabellen sowie deren Attribute und Beziehungen bekannt sein. Daher ist es unumgänglich, ein ERD lesen zu können! Abbildung 19 : 11 12 Beispiel eines ERD in Access Query By Example Structured Query Language © René Probst Jan 2004 4-1 Theorie 4.2. Modul-100 Erstellen von Abfragen Basis der meisten Datenauswertungen sind sogenannte Abfragen13 die durch die Auswahl der beteiligten Tabellen (eine oder mehrere) und den geforderten Attributen aufgebaut werden. 1. Im Hauptfenster von Access wird als bearbeitetes Objekt die Abfrage gewählt und dann auf die Schaltfläche [Neu] geklickt. Abbildung 20 : 2. 3. Access Hauptdialog / Erstellen einer neuen Abfrage Im Dialog wird der erste Punkt “Entwurfsansicht“ beibehalten und über die Schaltfläche [OK] bestätigt. Aus der Liste der verfügbaren Tabellen (sowohl DB-Tabellen als auch bestehende Abfragen) werden nun die benötigten Tabellen selektiert und durch [Hinzufügen] ins Auswahlfenster gebracht. Abbildung 21 : Auswahl der Tabellen für die Abfrage Nun kann der Dialog geschlossen werden. 13 4-2 bei andern Produkten als View bezeichnet Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE 4. Mittels drag-and-drop werden die gewünschten Attribute in die Felder abgelegt. Abbildung 22 : 5. Selektion der Attribute für die Abfrage Durch Auswahl der Darstellungsart kann von der „Konstruktion“ zur „Präsentation“ gewechselt werden. Abbildung 23 : Auswahl der Darstellungsart einer Abfrage ( ) Abbildung 24 : Resultat einer Abfrage (Wer hat welches Hobby?) © René Probst Jan 2004 4-3 Theorie 4.3. Modul-100 Einfache Abfragen 4.3.1. Sortieren von Daten Sind die Daten in einer DB gespeichert, so lassen sie sich nicht mehr sortieren! Zumindest nicht in der Tabelle der Datenbank. Bei einer RDB muss dazu eine Abfrage generiert werden, die eine neue – temporäre – Tabelle erzeugt, in welcher die Daten sortiert sind. Abbildung 25 : DB-Tabelle (unsortiert) und Abfrage (sortiert) Die Sortierung wird in der Entwurfsansicht der Abfrage festgelegt. Es ist auch möglich, nach mehreren Attributen zu sortieren, wobei zuerst nach dem Attribut das links steht sortiert wird. Abbildung 26 : Auswählen einer Sortierung für die Abfrage 4.3.2. Kombinieren von Daten Bei einer Abfrage lassen sich Attribute aus mehreren Tabellen zusammengefasst darstellen. Dabei wird die Zuordnung der Daten über die Schlüsselattribute geregelt. Abbildung 27 : 4-4 Zusammengezogene Daten aus mehreren verknüpften Tabellen Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE 4.3.3. Verdichten von Daten Werden Daten abgefragt, bei denen sich mehrmals die gleiche Information ergibt, so lässt sich durch eine Gruppierung die Zahl der Datensätze verringern. Abbildung 28 : Verdichtete und unverdichtete Abfrage einer Datenbank Damit die Funktion für die Datenverdichtung – als Gruppieren bezeichnet – verfügbar ist, muss die Funktionsauswahl zuerst aktiviert werden. Abbildung 29 : Ein-/Ausschalten der Funktionen für die Darstellung ( ) Abbildung 30 : Gruppieren von Daten in einer Abfrage © René Probst Jan 2004 4-5 Theorie 4.4. Modul-100 Filtern von Daten 4.4.1. Filtern eines Attributs Eine Tabelle kann bezüglich der Werte ihrer Attribute gefiltert werden. D.h. dass nur die Daten angezeigt werden, deren Wert dem Suchbegriff entsprechen. Dabei kann auch ein „Joker“-Wert das Zeichen * bzw. % für beliebige Zeichenfolgen das Zeichen ? bzw. _ für genau ein beliebiges Zeichen eingesetzt werden. Abbildung 31 : Filterung auf Attribut Adresse ( “Haupt*“ ) Ob es sich bei der Tabelle um eine Stammtabelle der Datenbank oder aber eine durch Abfragen generierte Tabelle handelt spielt dabei keine Rolle. Abbildung 32 : Festlegen eines Suchbegriffs in Access 4.4.2. Filter auf mehrere Attribute Mittels logischre Operationen (UND, ODER) lassen sich beliebig komplexe Abfragen gestalten. UND-Verknüpfung Abbildung 33 : Filterung auf Attribut Adresse UND PLZ Begriffe die in der Entwurfsansicht in einer Zeile angeordnet sind, werden durch eine UND-Funktion verknüpft. 4-6 Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE Abbildung 34 : Festlegen mehrerer UND verknüpfter Attributswerte ODER-Verknüpfung Abbildung 35 : Filterung auf Attribut Adresse ( „Haupt*“ ODER „Dorf*“ ) Begriffe die in der Entwurfsansicht in mehreren Zeile angeordnet sind, werden durch eine ODER-Funktion verknüpft. Abbildung 36 : Festlegen mehrerer ODER verknüpfter Attributswerte Bereichsprüfung Abbildung 37 : Filterung auf Attribut PLZ (>7000 UND <9000) Um Wertebereiche zu filtern kann mit Vergleichsoperatoren und einer UNDVerknüpfung – diese muss hier aber explizit angeschrieben werden – eine entsprechende Aussage formuliert werden. Abbildung 38 : © René Probst Festlegen eines Wertebereichs für die Filterung Jan 2004 4-7 Modul-100 Theorie 4.5. Aggregierende Funktionen Wenn die Daten nicht mehr in ihrer ursprünglichen tabellarischen Form sondern durch (mathematische) Operationen verdichtet sind, spricht man bei RDB’s von Aggregierung. Damit Aggregats-Funktionen eingesetzt werden können, müssen bestimmte Datenwert mehrfach (redundant) in der Abfrage vorkommen. Bei Access muss die Auswahl der Funktionen explizit angewählt werden (siehe oben) 4.5.1. Anzahl eines Kriteriums festlegen Werden zu einem Attributswert mehrere andere Werte aufgelistet (wie das z.B. bei Person und Hobby der Fall ist), so kann nach der Anzahl der Nennungen gefragt werden. 4-8 Abbildung 39 : Abfrage nach der Anzahl der Hobbys? Abbildung 40 : Funktion in Access für die Bestimmung der Anzahl der Verknüpfungen Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE 4.5.2. Summierung von Zahlenwerten Nebst der Anzahl zutreffender Beziehungen ist die Summenbildung eine wichtige Aggregatsfunktion. Nur so lässt sich z.B. mittels einer DB eine automatisierte Rechnungsstellung realisieren. Wichtig dabei ist, dass sich nebst der Spalte mit den zu addierenden Werten keine weiteren abweichenden Daten in der Abfrage wiederfinden. Abbildung 41 : Abfrage die keine Summation zulässt Die abgebildete Tabelle ermöglicht keine Summation, da es bezüglich der Spalte “Aufwand/Jahr“ nur eindeutige Datensätze gibt. Erst durch das Entfernen der Spalte “Bezeichnung“ eignet sich die Datenbasis für eine Summation der Werte. Abbildung 42 : Abfrage für Summation vorbereitet Es gibt zwei Meier Peter in der DB! Daher muss der Primärschlüssel in die Abfrage mit einbezogen sein(Er wird hier für die Darstellung einfach ausgeblendet) Abbildung 43 : © René Probst Abfrage mit Summation über Attribut “Aufwand/Jahr“ Jan 2004 4-9 Theorie Abbildung 44 : 4-10 Modul-100 Summation über einen Attributswert Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE 4.6. Bedeutung der Verknüpfung Die Beziehungen die zwischen den einzelnen Entitäten bestehen sind massgebend für das Resultat einer Abfrage. Es gibt drei grundlegende Möglichkeiten, die zu unterscheiden sind. Keine Beziehung¨ Inner-Join Outer-Join 4.6.1. Keine Beziehung Es wird das Produkt aller Datensätze der beteiligten Entitäten erstellt. Beispiel : Es werden die beiden Tabellen Person und Hobby in einer Abfrage „kombiniert“. Abbildung 45 : Abfrage über zwei Tabellen, die keine Beziehung aufweisen In diesem Fall wird die DB jeden Datensatz der Tabelle ’Person’ (20 Datensätze) mit jedem Datensatz der Tabelle ’Hobby’ (20 Datensätze) verknüpfen, so dass am Ende 400 Datensätze resultieren. © René Probst Jan 2004 4-11 Theorie Abbildung 46 : Modul-100 Resultat der Abfrage zweier Entitäten mit je 20 Datensätze 4.6.2. Inner-Join Es werden nur die Datensätze gezeigt, die sowohl in der einen als auch in der andern Entität einen Eintrag (Schlüssel-Fremdschlüssel Paar) haben! Beispiel : Abfrage aller Personen die ein Beziehung zu Hobby haben, wobei über die Entität ’PersonHobby’ ein Schlüssel-Fremdschlüssel Paar geprüft wird. Abbildung 47 : Abfrage über zwei Tabellen, die in Beziehung stehen In diesem Fall wird die DB alle Datensätze aus ’Person’ lesen, zu denen in der Tabelle ’PersonHobby’ ein korrespondierender Eintrag gefunden wird. 4-12 Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE Wenn eine Person kein Hobby hat, erscheint sie auch nicht in dieser Auswertung. Dies ist im Beispiel daran ersichtlich, dass von den 20 erfassten Personen nur deren 19 aufgelistet sind. D.h. dass einer Person kein Hobby zugewiesen ist. Abbildung 48 : Resultat der Abfrage mit Inner-Join 4.6.3. Outer-Join Es werden alle Datensätze der einen Tabelle aber nur die korrespondierenden Datensätze der andern Tabelle gezeigt. Beispiel : Auf die oben gezeigte Abfrage wird ein Outer-Join angewendet.. Abbildung 49 : © René Probst Tabellen mit Outer-Join Beziehung Jan 2004 4-13 Theorie Modul-100 Einrichten eines Outer-Join 1. 2. 3. 4. Öffnen Sie die Abfrage in der Entwurfsansicht. Führen Sie die Maus auf die Beziehungslinie und drücken Sie dann die rechte Maustaste. Wählen Sie aus dem Kontextmenü “Verknüpfungseigenschaften“ Wählen Sie im Dialog den Verknüpfungstyp aus. Möglich sind • Inner Join (1:) • Left-Outer Join (2:) • Right-Outer Join (3:) Abbildung 50 : Auswahl des Verknüpfungstyp bei Access Das Ergebnis zeigt wie erwartet 20 Personen an. Abbildung 51 : 4-14 Resultat der Abfrage mit Outer-Join Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE 4.6.4. Outer-Join bei m:m-Beziehungen Eine m:m Beziehung wird in der DB in zwei 1:m Beziehungen mit einer Zwischentabelle abgebildet. Dadurch ist es aber nicht mehr möglich, einen OuterJoin (direkt) in die Abfrage einzubeziehen. Es müssen hier zwei getrennte Abfragen erstellt werden, wobei eine Abfrage den Outer-Join enthält. Diese Abfrage wird dann als Grundlage für eine weitere Abfrage verwendet. Beispiel : Es sollen alle Personen und auch deren Hobbys gezeigt werden. Dazu müssen die Tabellen ’Hobby’ und ’PersonHobby’ in einer Abfrage gespeichert werden, um dann diese Abfrage mit der Tabelle ’Person’ zu verknüpfen. Dabei wird für diese Verknüpfung dann ein Outer-Join gewählt. Abbildung 52 : © René Probst Abfragen für Outer-Join einer m:m-Beziehung Jan 2004 4-15 Theorie Modul-100 Die Beziehung zwischen den beiden Schlüsselwerten – PID bei Entität ’Person’ und Person bei Abfrage ’HobbiesDerPersonen’ – muss hier noch erstellt werden. Dies erfolgt durch ziehen des Primärschlüssles (PID) auf den Fremdschlüssel (Person). Anschliessend ist noch der Verknüpfungstyp auf Outer-Join zu setzen. Abbildung 53 : 4-16 Resultat der Abfrage mit Outer-Join bei m:m-Beziehung Modul 100 V1.2b.doc Abteilung Informatik/Technik Informationsbestände analysieren Datenabfrage mit QBE Kapitel 4 : Eigene Notizen © René Probst Jan 2004 4-17 Theorie 4-18 Modul 100 V1.2b.doc Modul-100 Abteilung Informatik/Technik