Zusammenfassung - Software Bakery
Werbung
HSLU T&A, Semester 4, FS.13 DMG Datenmanagement Modulzusammenfassung Datenmanagement & Relationale Algebra c The Software Bakery Bruno Leupi, Tobias Maestrini, Edy Wermelinger 26. Februar 2014 Eidg. Dipl. Techniker in Informatik Bruno Leupi ist Student bei der Hochschule Luzern sowie Softwareentwickler für die Firma Schindler Aufzüge AG. E-Mail: [email protected] Internet: leupibr.no-ip.info Maresciallo Luogotenente (mit akademischer Grundausbildung) Tobias Maestrini studiert mit Bruno Leupi. Er ist promovierter Sekundarlehrer und gescheiterter Jazzpianist sowie gegroundeter Pilot, mit besonderer Vorliebe für Kryptisches und Fundamentales. E-Mail: [email protected] Internet: www.tobias-maestrini.ch Edy Wermelinger studiert mit Tobias Maestrini. Er arbeitet als grosser Compilerbauer und ist verantwortlich für jene technischen Meilensteine, welche in den Monaten des Frühjahrs 2013 für Aufsehen gesorgt hatten: Edy Wermelinger hat mit seinem EduardUno, dem EduardoPRO und dem Eduarden die Geschichte der Compiler grundlegend revolutioniert. Edy Wermelinger ist Mitglied der International Unified Compiler Builder Association (IUCBA), Mitgründer der Common LogServer Interface Group (CLoSIG) sowie Mitautor zahlreicher renommierter Standardwerke für Datentypen. E-Mail: [email protected] Internet: www.werminet.ch Zu diesem Skript. Dieses Skript stützt sich auf die Vorlesungen im Fach DMG bei Th. Olnhoff während des Frühlingssemesters 2013 und verarbeitet die dort gehörten Inputs mit ausgewählten Aspekten aus dem Lehrbuch "‘Datenbanksysteme – Eine Einführung"’ von A. Kemper und A. Eickler. Die Kapitelüberschriften korrespondieren mit den entsprechenden Kapiteln im Lehrbuch. Dieses Skript wird vorzugsweise auf säure- und chlorfrei hergestelltem Papier gedruckt. Inhaltsverzeichnis 1 2 Was soll ein DBMS leisten? 8 1.1 Datenmodellierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.2 Das relationale Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 1.2.1 9 Datenbankentwurf 10 2.1 Phasen des Datenbankentwurfs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.2 Entity/Relationship-Modellierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.3 Funktionalitäten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.3.1 3 Datenabstraktion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . n-stellige Beispiel-Beziehung . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.4 Notationen für Funktionalitäten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.5 Generalisierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.6 Konsolidierung von Teilschemata oder Sichtenintegration . . . . . . . . . . . . . . . . . 13 Das relationale Modell (incl. Algebra) 3.1 14 Relationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3.1.1 Binäre Relationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 3.1.2 Unterschiede und Gemeinsamkeiten zu Funktionen . . . . . . . . . . . . . . . . 15 3.2 Eigenschaften von Relationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 3.3 Kombination und Zusammensetzung von Relationen . . . . . . . . . . . . . . . . . . . 16 3.4 Darstellung von Relationen durch Matrizen . . . . . . . . . . . . . . . . . . . . . . . . 17 3.5 Äquivalenzrelationen und -klassen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 3.6 n− stellige Relationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.7 Operationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.8 Typ-kompatible Relationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 3.8.1 3.8.2 3.8.3 3.8.4 3.9 Vereinigung ∪ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Durchschnitt ∩ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Differenz \ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Kreuzprodukt × . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 3.8.5 Selektion σ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 3.8.6 Projektion Π . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 3.8.7 Umbenennung ρ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 3.8.8 Verbund ./ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 3.8.9 Division ÷ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Transformationsregeln . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 3.9.1 Übersicht Relationenalgebra . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 4 Relationale Anfragesprachen (SQL) 4.1 4.2 4.3 4.4 4.5 4.6 4.7 4.8 4.9 4.10 4.11 4.12 4.13 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6.2 6.3 Referentielle Integrität . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Datenbank-Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 DB-Prozeduren (Stored Procedures) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 39 Funktionale Abhängigkeiten [171ff] . . . . . . . 6.1.1 Schlüssel . . . . . . . . . . . . . . . . . Normalisierung . . . . . . . . . . . . . . . . . . Normalisierung mit Boyce-Codd (BCNF) [190ff] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Physische Datenorganisation 7.1 7.2 7.3 7.4 7.5 7.6 24 24 24 24 26 26 27 28 28 28 29 29 30 30 32 32 32 32 34 36 Relationale Entwurfstheorie 6.1 7 Einfache Datendefinition in SQL . . . . . . . . . . . . . . . . . . . . . . . . . 4.1.1 Online-Ressource (Link) . . . . . . . . . . . . . . . . . . . . . . . . . 4.1.2 Anlegen von Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . 4.1.3 Referentielle Integrität in SQL . . . . . . . . . . . . . . . . . . . . . . Veränderungen am Datenbestand . . . . . . . . . . . . . . . . . . . . . . . . . Anfragen über mehrere Relationen . . . . . . . . . . . . . . . . . . . . . . . . Aggregatfunktionen und Gruppierung . . . . . . . . . . . . . . . . . . . . . . Geschachtelte Anfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Verwertung der Ergebnismenge einer Unteranfrage . . . . . . . . . . . . . . . Auswertung bei NULL-Werten . . . . . . . . . . . . . . . . . . . . . . . . . . Das «case»-Konstrukt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . JOINs in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Rekursion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.10.1 Rekursionskonstrukt mittels WITH ... UNION ALL ... SELECT Veränderungen am Datenbestand . . . . . . . . . . . . . . . . . . . . . . . . . Sichten (Views) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . JDBC: Java Database Connectivity . . . . . . . . . . . . . . . . . . . . . . . . 4.13.1 Schritte in JDBC zur Erstellung einer DB-Abfrage . . . . . . . . . . . 4.13.2 Daten einfügen mittels verschiedener Statements . . . . . . . . . . . . Datenintegrität 5.1 5.2 5.3 6 24 Hintergrundspeicher . . . . . . . . B-Bäume . . . . . . . . . . . . . . B+ -Bäume («Clustered Index») . . B*-Bäume («non-clustered Index») Beispiele . . . . . . . . . . . . . . Statisches Hashing . . . . . . . . . 7.6.1 Vorteile . . . . . . . . . . . 7.6.2 Nachteile . . . . . . . . . . 39 39 39 40 41 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 42 43 44 45 47 48 48 8 Anfragebearbeitung / -Optimierung 8.1 Logische Optimierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 8.1.1 8.2 8.3 9 Heuristische Anwendungen der Transformationsregeln . . . . . . . . . . . . . . 51 Physische Optimierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 8.2.1 Selektivität . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 8.2.2 Übersetzung der logischen Algebra . . . . . . . . . . . . . . . . . . . . . . . . 52 „Tuning“ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Transaktionsverwaltung 9.1 50 54 Transaktionsverwaltung mit Fehlerbehandlung . . . . . . . . . . . . . . . . . . . . . . . 54 9.1.1 Wichtige Eigenschaften von Transaktionen [wichtig] . . . . . . . . . . . . . . . 54 9.1.2 Komponenten Transaktionsverwaltung . . . . . . . . . . . . . . . . . . . . . . . 55 10 Fehlerbehandlung 56 10.1 Die (zweistufige) Speicherhierarchie . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 10.1.1 Ersetzung von Seiten und Änderungen von Transaktionen . . . . . . . . . . . . 56 10.2 Protokollierung von Änderungsoperationen . . . . . . . . . . . . . . . . . . . . . . . . 57 10.2.1 Das WAL-Prinzip („Write Ahead Log“-Prinzip) . . . . . . . . . . . . . . . . . . 58 10.3 Wiederanlauf nach einem Fehler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 11 Transaktionsverwaltung mit Mehrbenutzersynchronisation 59 11.1 Fehler bei unkontrolliertem Mehrbenutzerbetrieb [S. 316] . . . . . . . . . . . . . . . . . 59 11.2 Serialisierbarkeit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 11.3 Sperrbasierte Synchronisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 11.3.1 Verhalten der Transaktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 11.4 2-Phase-Commit Transaktionsverarbeitung . . . . . . . . . . . . . . . . . . . . . . . . . 62 11.5 Abstürze . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 11.5.1 Absturz eines Koordinators → Hauptproblem des 2PC-Verfahrens . . . . . . . . 62 11.5.2 Absturz eines Agenten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 12 Sicherheitsaspekte 63 12.1 Zugriffskontrolle in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 12.1.1 Identifikation und Authentisierung . . . . . . . . . . . . . . . . . . . . . . . . . 63 12.1.2 Autorisierung und Zugriffskontrolle . . . . . . . . . . . . . . . . . . . . . . . . 63 12.1.3 Zugriffskontrolle durch Sichten (VIEW) . . . . . . . . . . . . . . . . . . . . . . 63 12.2 Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 12.3 Kryptographie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 13 Objektorientierte Datenbanken / Übung mit Java-Persistence-API (Objektorientierte Sicht auf relationale DB) 65 13.1 Klassenbeschreibungen einer relationalen Datenbank 13.1.1 Definition von Objekttypen . . . . . . . . . . 13.2 Abfragen, Statements und Transaktionen in OQL . . 13.3 Beispiel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Java Persistence API 14.1 Beschreibungsmittel und Klassen 14.1.1 Annotations-1 . . . . . . 14.1.2 Annotations-2 . . . . . . 14.1.3 Annotations-3 . . . . . . 14.1.4 Annotations-4 . . . . . . 14.1.5 Annotation-5 . . . . . . 14.1.6 Persistence.xml . . . . . 14.1.7 EntityManager . . . . . 65 66 67 68 70 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Verteilte Datenbanken 15.1 Fragmentierung . . . . . . . . . . . . 15.1.1 Horizontale Fragmentierung . 15.1.2 Vertikale Fragmentierung . . . 15.2 Allokation . . . . . . . . . . . . . . . 15.3 Transparenz . . . . . . . . . . . . . . 15.3.1 Fragmentierungstransparenz . 15.3.2 Allokationstransparenz . . . . 15.3.3 Lokale Schema-Transparenz . 15.4 Beispielaufgabe aus dem Unterricht . 15.5 Sperren von Replikationen . . . . . . 15.5.1 Quorum-Consensus Verfahren 15.6 Replikationsarten . . . . . . . . . . . 15.6.1 Snapshot Replikation . . . . . 15.6.2 Transaktionale Replikation . . 15.6.3 Merge- Replikation . . . . . . 70 70 71 71 71 72 72 72 74 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 OLTP, Data Warehouse, Data Mining 16.1 OLTP: Online Transaction Processing . . . . . . . . . . . . . . . . . . 16.1.1 OLAP: Online Analytical Processing – das Gegenteil von OLTP 16.2 Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16.2.1 Stern-Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . 16.2.2 Verdichtung der Daten zu Datenwürfeln . . . . . . . . . . . . . 74 75 76 77 78 78 79 79 79 80 80 80 81 81 81 82 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 82 83 83 84 17 XML-Datenmodellierung (mit Java-Bibliotheken, Fokus XQuery) 17.1 HTML-Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . 17.1.1 XML-Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . 17.2 Schemabeschreibung . . . . . . . . . . . . . . . . . . . . . . . . . . . 17.3 Anfragesprachen, Fokus: XQuery . . . . . . . . . . . . . . . . . . . . 17.3.1 XML-Anfragesprache XQuery und FLWOR-Regel . . . . . . . 17.3.2 XQuery-Implementation mit CONTAINS . . . . . . . . . . . . 17.4 Java API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17.4.1 Java: simple API for XML (SAX) . . . . . . . . . . . . . . . . 17.4.2 Java: DOM-Interface for XML DOM = Document Object Model 86 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 86 87 88 88 90 90 90 93 1 Was soll ein DBMS leisten? hohe Performance einfache Abfragemöglichkeiten Transaktionssicherheit referenzielle Integrität Daten sind nur an einem bestimmten Ort gespeichert und abhängige andere Daten (Referenzen) werden bei einer Modifikation mitmodifiziert («Referenzen sind stimmig») Redundanzfreiheit und Vermeidung von Inkonsistenzen Informationen sind redundanzfrei enthalten Datenschutz definierte Benutzer können entsprechende Berechtigungen ausführen Ausfallsicherheit bei einem Serverausfall gibt es ein entsprechendes Recovery, das auf den Zustand vor dem Ausfall zurückgeführt werden kann Mehrbenutzerbetrieb parallele Zugriffe mehrerer Benutzer sind möglich Sicherheitsprobleme sollen vermieden werden Entwicklungskosten für Anwendungsprogramme in einem angepassten Mass halten 1.1 Datenmodellierung Ein Abbild einer realen Welt wird in einem Schema abgebildet: Reale Miniwelt ⇒ Konzeptuelles Schema (ER-Schema1 ) Datenmodellierung Ausschnitt der Realen Miniwelt Modell-Ebene: DB-Ebene: Manuelle/intellektuelle D Modellierung M G Konzeptuelles Schema (ER-Schema) Halbautomatische Transformation Relationales Schema Netzwerk Schema Objektorientiertes Schema Abbildung 1: Datenmodellierung bzw. Abbildung der realen Welt auf ein ER-Schema 1 8 Entity Relationship Schema © A. Kemper / A. Eickler c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 7 1.2 Das relationale Datenmodell Das relationale Modell 3. Umsetzung konzeptuelles in logisches Schema Beispiel: Modellierung einer Anwendung Umsetzung konzeptuelles in logisches Schema MatrNr Studenten N Professoren hören M Vorlesungen N 1 lesen PersNr Name ....... Rang ....... VorlNr 3 Raum Überführung des Konzeptuellen Schemas in ein Logisches Schema (in der VL: Relationales Modell) Studenten hören Vorlesungen MatrNr Name MatrNr VorlNr VorlNr Titel 24002 Xenokrates 26120 5001 5001 Grundzüge 25403 Jonas 24002 5001 5041 Ethik 26120 Fichte 24002 4052 5049 Mäeutik 26830 Aristoxenos ... ... 4052 Logik 28106 Carnap 5216 Bioethik 29555 Feuerbach ... ... ... ... Katrin Seyr Seite 7 1.2 Das relationale Datenmodell2 Begriff. Auf den Arbeiten von E.F. Codd von 1970 basierendes Datenmodell, mit dem Beziehungen zwischen Daten in Form von Relationen bzw. in Tabellenform beschrieben werden. Grundlage fast aller neueren Datenbanksysteme (z.B. DB2, Oracle, Ingres, Sybase) Vorteile. hohe Flexibilität, leichte Handhabung, einfache Datenbankabfragen Nachteil. Effizienzprobleme bei grossen Datenvolumen 1.2.1 Datenabstraktion Der Abstraktionslevel einer Datenbank besagt, dass eine DB immer die gleiche Logische Ebene bzw. verschiedene externe Sichten einer physikalisch existierenden Realsituation zeigt, auch wenn sich die Physische Ebene ändert: Sicht 1 Sicht 2 Sicht n Logische Ebene (Datenbankschema) Physische Ebene (Hintergrundspeicher) Ein Vorteil des Modells liegt darin, dass für die «weiter oben liegenden» (Applikations-) Schichten sich nichts ändert – auch dann nicht, wenn sich auf der darunter liegenden Datenstruktur etwas verändert. 2 http://wirtschaftslexikon.gabler.de/Archiv/55871/relationenmodell-v8.html c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 9 2 Datenbankentwurf 2.1 Phasen des Datenbankentwurfs Der konzeptuell saubere Entwurf sollte die Voraussetzung aller DB-Anwendungen sein. Die Erstellung eines ER-Modells beschreibt eine gestufte Vorgehensweise: InformationsAnforderungen Anforderungsanalyse Konzeptueller Entwurf ER-Modell FunktionsAnforderungen Implementationsentwurf Tabellen DBMSCharakteristika Physischer Entwurf Speicherstrukturen Beschreibung der Datenbankobjekte in einer Tabelle: • Attribute definieren: Typ Länge Wertebereich Identifizierend als Primärschlüssel einsetzbar? Definiertheit «NOT NULL»-Eigenschaft (SQL) Wiederholung wieviele Objekte dieses Datentyps sind in einer Beziehung möglich? (vgl. «Col- lection») 2.2 Entity/Relationship-Modellierung • • • • • 10 Entity (Gegenstands- / Entitätstyp) Relationship (Beziehungstyp) Attribut (Eigenschaft) Schlüssel (Identifikation) Rolle c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 2.3 Funktionalitäten 2.3 Funktionalitäten Man kann Beziehungstypen (Relationships R) zwischen den Entitätstypen (E) hinsichtlich ihrer Funktionalität charakterisieren. Es gibt Funktionalitäten vier verschiedene Assotiationstypen: ... E1 c: can = 0 oder 1 R R E1 E1 x E2 ... E2 mc: multiple can = 0 bis viele E2 c : c mc : c c : mc D M G mc : mc © A. Kemper / A. Eickler 10 Die Ovale repräsentieren die Entitätstypen E1 und E2 , die kleinen Quadrate innerhalb der Ovale stellen die Entities dar und die verbindenden Linien repräsentieren eine Instanz der Beziehung R. Beispiel 1: Ehe – ein Beziehungstyp Mann ⇔ Frau ⇒ c ⇔ c Beispiel-Beziehung: betreuen 2.3.1 n-stellige Beispiel-Beziehung Aus der dreistelligen Beziehung zwischen den Entitytypen Studenten, Professoren und Seminarthemen gehen folgende Teilbeziehungen hervor: c Studenten mc betreuen c Professoren Seminarthemen Note betreuen : Professoren × Studenten → Seminarthemen betreuen : Seminarthemen × Studenten Seminarthemen → Professoren betreuen : Professoren x Studenten betreuen : Seminarthemen x Studenten D M G Professoren Im ER-Modell mit Funktionalitätsangaben beschränkt man sich sich auf 2-stellige BeziehunA. Kemper / A. Eickler 13 schafft gen („best practice“). 3-stellige bzw n-stellige©Beziehungen sind nicht anzustreben! Abhilfe dann die Einführung einer zusätzlichen Beziehungsentität (siehe nächste Grafik). c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 11 Charakterisierung von Beziehungstypen Umwandlung n-stelliger Beziehungen 2.4 Notationen für Funktionalitäten Beispiel für die Umwandlung einer dreistelligen Beziehung in drei zweistellige Beziehungen: Titel Titel empfiehlt-P-V Vorlesung Professor empfiehlt ➜ Professor Vorlesung empfiehlt-V-B Name Buch Name Fach Fach empfiehlt-P-B ISBN „Unsere“ Notationen für Funktionalitäten – Erläuterungen auf weiteren Folien Buch ISBN Ziel: Von der dreistelligen Beziehung (links) zur zweistelligen (rechts). Nur bei 2er Beziehungen Bei 3er und höheren Beziehungen: es wird eine Beziehungsentität 2.4 Notationen fürDesign Funktionalitäten DB:III-80 Conceptual eingeführt Immer mit den Unterscheidungen (Information Engineering Standard): c can, 0..1, entspricht 1 im Buch 1 one oder eins, 1..1, entspricht existenzabhängig im Buch mc multiple can, 0..*, entspricht N oder M oder P im Buch m multiple, 1..*, entspricht N oder M oder P im Buch c STEIN 2004-2012 D M G Beispiel 2: Bestellwesen Im folgenden wird mit den c-, 1-, mc-, m-Funktionalitäten gearbeitet. Mit diesen Angaben kann man etwas strenger unterscheiden als mit den 1 Angaben im Buch. macht Kunde Funktionalitäten werden auch Beziehungstypen, Kardinalitäten oder Multiplizitäten genannt – viele ..täten! mc Produkt mc Menge m Bestellung 26 mc mit 1 Zahlungsart 2.5 Generalisierung3 Das Prinzip der Generalisierung wird eingesetzt, um eine übersichtlichere und natürlichere Strukturierung der Entity-Typen zu erzielen. Gemeinsame Eigenschaften (Attribute und Beziehungen) ähnlicher EntityTypen werden „herausfaktorisiert“ und einem gemeinsamen Obertyp zugeordnet. 3 12 http://www2.tcs.informatik.uni-muenchen.de/lehre/lehrerausbildung/db_ermodell.pdf c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger Uni Duisburg-Essen Fachgebiet Informationssysteme 2.6 Prof. Dr. N. Fuhr Konsolidierung von Teilschemata oder Sichtenintegration es unterschiedliche Strategien, wobei sich die folgende am engsten an die E-RModellierung hält: Die beteiligten Entity-Typen sind dann Untertypen des jeweiligen Obertyps. Eigenschaften, die nicht • Für jeden Entitätstypen E in der Generalisierungshierarchie gibt es eine allen Untertypen gemeinsam sind, beim entsprechenden Untertyp. Relation mit denbleiben Schlüsselattributen des Obertypen und Wir den verwenden Attributen für die Kennvon E zeichnung die Bezeichnung is-a mit dem Raute-Symbol. Fachgebiet WissMitarbeiter RaumNr Rang is_a UniAngestellter Professor Name PersNr UniAngestellte : {[PersNr, Name, RaumNr]} Professoren : {[PersNr, Rang]} WissMitarbeiter : {[PersNr, Fachgebiet]} Eine Alternative wäre die Überführung des abgeleiteten Entitätstypen E in eine Relation, die als Attribute alle Attribute des Obertypen sowie die Attribute von 2.6 Konsolidierung von Teilschemata oder Sichtenintegration E besitzt. In dieser Variante würden in der Relation des Obertyps nur Instanzen gespeichert, die zu keinem der Subtypen gehören. Bei grösseren Anwendungen ist es nicht praktikabel, den konzeptuellen Entwurf (Ausschnitt der realen UniAngestellte : {[PersNr, Name, RaumNr]} Welt in die DB-Modell übertragen) in einem Guss durchzuführen – sinnvoll ist die Aufteilung in verKonsolidierung, Sichtenintegration Professoren : {[PersNr, Name, RaumNr, Rang]} konzipiert werden. Dabei entstehen schiedene Anwendersichten, welche vorerst unabhängig von einander WissMitarbeiter : {[PersNr, Name, RaumNr, Fachgebiet]} natürlich Modellierungsproblematik Überlagerungen: Zusammenführen von Relationen Sicht 1 Die direkte Überführung wie oben beschrieben liefert oft nicht die bestmöglichen Sicht 5 Relationen. Oft kann man nun noch Relationen zusammenführen. Allgemein globales Schema gilt: Relationen mit dem gleichen Schlüssel kann man zusammenfassen (aber auch Konsolidierung - redundanzfrei Ausschnitt der nur diese). widerspruchsfrei Sicht 2 realen Welt - Synonyme bereinigt Ein binärer Beziehungstyp R zwischen Entitätentypen E und F, an dem minde- Homonyme bereinigt stens einer der beiden beteiligten Entitätstypen (sagen wir E)- mit ... Funktionalität Sicht 4 1 teilnimmt, kann mit der E entsprechenden Relation zusammengeführt werden. Sicht 3 Dazu führt man eine Relation mit folgenden Attributen ein: • die Attribute von E • die Primärschlüsselattribute von F Die nach der Konzeption folgende Konsolidierung bedeutet die Zusammenfassung einzelner Sichten • die Attribute R zu einem globalen Schema, dasderu.Beziehung a. redundanzund widerspruchsfrei ist. Widersprüche können sein: • unterschiedliche Benennung gleicher Sachverhalte (Synonyme) • gleiche Praktikum BenennungDatenbanken unterschiedlicher / DB2Sachverhalte (Homonyme) Seite 3 von 5 3: Relationenschemata, 1. ein Normalform c STEIN 2004-2012 DB:III-96 Woche Conceptual Design • Sachverhalt einmal als Entity-Typ und andermal als Beziehungstyp modelliert (struktureller Widerspruch) • widersprüchliche Funktionalitätsangaben • widersprüchliche Datentypen, widersprüchliche Schlüsselattribute c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 13 3 Das relationale Modell (incl. Algebra) Das relationale Datenmodell stammt aus den siebziger Jahren. Es besteht aus der mengenorientierten Verarbeitung der Daten und ist sehr einfach strukturiert: es gibt im Wesentlichen nur flache Tabellen – Relationen –, in denen die Zeilen den Datenobjekten entsprechen. Die in den Tabellen (Relationen) gespeicherten Daten werden durch entsprechende Operatoren mengenorientiert verknüpft und verarbeitet. 3.1 Relationen Merke: Eine Relation ist das Kreuzprodukt von zwei (oder mehreren) Mengen (A × B). A = {1, 2, 3}; B = {a, b} A × B = {(1, a), (1, b), (2, a), (2, b), (3, a), (3, b)} |A| : Kardinalität von A = Anzahl El. von A |A| = 3; |B| = 2; |A × B| = 6 = 3 · 2 = |A||B| Allgemein: |A1 × A2 × · · · × An | = n Y k=1 |Ak | (a, 1) 6∈ A × B 3.1.1 Binäre Relationen Binäre Relationen bestehen aus zwei Tupeln! R⊂A×B Eine Relation in einer Menge A ist eine Teilmenge von A × A = A2 Beispiel 3: Menge aller Teilmengen einer Relation Wie viele Relationen gibt es in einer endlichen Menge A? 2 Lösung: 2|A| (Potenzmenge) 14 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 3.1 Relationen Beispiel 4: Relation zweiter Punkte mittels gerichtetem Graphen Sei A = {1, 2, 3, 4} und R = {(a, b)|a, b ∈ A ∧ a|b}, d.h die Menge der Paare (a,b) für die gilt: a|b. Stellen Sie die Relation durch einen gerichteten Graphen dar. Lösung: R = {(1, 1), (1, 2), (1, 3), (1, 4), (2, 2), (2, 4), (3, 3), (4, 4)} 1 2 3 4 gerichtete Knoten und gerichtete Kanten ⇒ gerichteter Graph gerichtete Kante von a nach b falls a|b Beispiel 5: Relationen in Z Betrachte folgende Relationen auf der Menge der ganzen Zahlen Z. R1 = {(a, b)|a, b ∈ Z ∧ a ≤ b} R2 = {(a, b)|a, b ∈ Z ∧ (a = b ∨ a = −b)} R3 = {(a, b)|a, b ∈ Z ∧ a = b + 1} Z R1 (reflexiv, transitiv) R2 (reflexiv, symmetrisch, transitiv) R3 Z Z × Z = Z2 3.1.2 Unterschiede und Gemeinsamkeiten zu Funktionen • f (x) von einer Menge A nach einer Menge B ordnet jedem x ∈ A genau ein Element y = f (x) ∈ B zu. • Jede Funktion f : A → B ist eine Relation aber nicht jede Relation eine Funktion. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 15 3.2 Eigenschaften von Relationen B G(f ): Graph von f ist eine f (a) = b (a, f (a)) Rel. weil Teilmenge A von A × B ∈A ∈B 3.2 Eigenschaften von Relationen • Die Relation R auf A heisst reflexiv, falls ∀x ∈ A((x, x) ∈ R). Nur R1 und R2 des vorherigen Beispieles sind reflexiv. Reflexivität im gerichteten Graph ist ersichtlich indem jeder Punkt auf sich selber zeigt. • Die Relation R auf A heisst symmetrisch, falls ∀x, y ∈ A((x, y) ∈ R → (y, x) ∈ R). • Die Relation R auf A heisst transitiv, falls ∀x, y, z ∈ A((x, y) ∈ R ∧ (y, z) ∈ R → (x, z) ∈ R). Falls es einen Weg von x nach y und von y nach z gibt, dann gibt es auch einen von x nach z. x (x, y) ∈ R a (x, z) ∈ R b y z (y, z) ∈ R A c 3.3 Kombination und Zusammensetzung von Relationen Merke: Da Relationen R1 und R2 von A nach B beides Teilmengen von A × B sind, ist die Vereinigung R1 ∪ R2 , der Durchschnitt R1 ∩ R2 und die Differenzen R1 \R2 , R2 \R1 , sowie das Komplement R1 wohl definiert. Zusammensetzung von Relationen. Ist R eine Relation von der Menge A in die Menge B und ist S eine Relation von B nach C; dann ist die Zusammensetzung von R und S die Relation. S ◦ R = {(a, c) ∈ A × C|∃b ∈ B((a, b) ∈ R ∧ (b, c) ∈ S)} Beispiel 6: Zusammensetzung von Relationen Was ist die Zusammensetzung S ◦ R der Relationen R und S wobei R eine Relation von A = {a, b, c} nach B = {a, b, c, d} mit R = {(a, a), (a, d), (b, c), (c, a), (c, d)} ist und S eine Relation von B nach C = {z, a, b} mit S = {(a, z), (b, z), (c, a), (c, b), (d, a)}? 16 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 3.4 A Darstellung von Relationen durch Matrizen C B a R a S z b a b c c b d S ◦ R = {(a, z), (a, a), (b, a), (b, b), (c, z), (c, a)} Potenzen Rn , n = 1, 2, 3 . . . einer Relation R in A werden rekursiv wie folgt definiert: R1 = R und Rn = Rn−1 ◦ R. Potenz einer Relation. A a A a A a A a b b b b c c c c d d d d R R2 = {(a, a), (b, a), (c, a), (d, b)} R3 = {(a, a), (b, a), (c, a), (d, a)} = R4 = R5 = R6 = Rn Eine Relation R ist genau dann tranisitiv, falls: ∀n ∈ N+ (Rn ⊂ R) 3.4 Darstellung von Relationen durch Matrizen Definition Ist R eine Relation von A = a1 , a2 , . . . , am nach B = b1 , b2 , . . . , bn , dann stellt die Matrix Mr = [mij ] mit ( 1, falls(ai , bj ) ∈ R mij 0, falls(ai , bj ) 6∈ R Beispiel 7: Darstellung von Relationen durch Matrizen (I) Sei A = {1, 2, 3} und B = {1, 2} und R die Relation, die (a, b) enthält, falls a > b. Wie lautet dann die zugehörige Matrix MR ? c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17 3.5 Äquivalenzrelationen und -klassen Allgemein: b2 . . . bj .. a1 . .. a2 . .. .. . . ai . . . . . . . . . mij an b1 bn Zur Aufgabe: A/B 1 2 1 0 0 2 1 0 3 1 1 Beispiel 8: Darstellung von Relationen durch Matrizen (II) Stellen Sie die Relation R = {(2, 2), (2, 3), (2, 4), (3, 2), (3, 3), (3, 4)} mit R = 1, . . . , 4 in einer Matrix dar. Stellen Sie auch R2 (welches oben berechnet wurde) mit Hilfe einer Matrix dar. 0 0 0 0 0 1 1 1 MR = 0 1 1 1 0 0 0 0 0 0 0 0 0 1 1 1 MR2 = MR◦R = MR MR = 0 1 1 1 0 0 0 0 3.5 Äquivalenzrelationen und -klassen Definition Eine Relation R auf einer Menge A heisst Äquivalenzrelation falls sie reflexiv, symmetrisch und transitiv ist. Beispiel: Wir definieren auf der Menge der Zeichenketten des Alphabets die Relation R = {(a, b)|l(a) = l(b)}, wobei die Länge der Zeichenkette a mit l(a) bezeichnet wird. Zeige, dass R eine Äquivalenzrelation ist und bestimme die Äquivalenzklassen. Reflexiv: Ja!; Symmetrisch: Ja! l(x) = l(y) ⇔ (x, y) ∈ R l(y) = l(x) ⇔ (y, x) ∈ R Transitiv: (x, y) ∈ R ⇔ l(x) = l(y) ∧ (y, z) ∈ R ⇔ l(y) = r(z) ⇔ l(x) = l(z) ⇔ l(x, z) ∈ R 18 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 3.6 n− stellige Relationen Beispiel 9: Kongruenz modulo m Eine sehr wichtige Relation, welche in der Kryptographie Anwendung findet, ist die Kongruenz modulo m welche für m ∈ N\{0, 1} definiert ist durch: R = {(a, b)|a ≡ b(modm)} Das Kongruenzmodul ist reflexiv, symmetrisch und transitiv. 3.6 n− stellige Relationen Definition Eine n−stellige Relation R auf den Mengen A1 , A2 , · · · , An ist eine Teilmenge des Kreuzprodukts A1 × A2 × · · · × An , d.h R ⊂ A1 × A1 × · · · × An 3.7 Operationen Relationenkalkül ist eine formale Sprache, die definiert, welche Daten man erhalten will – nicht aber wie man sie erhält. SQL ist prozedural orientiert und ist bereits weniger deklarativ als der Relationenkalkül. Beide Sprachen sind abgeschlossen, d.h. die Ergebnisse der Anfragen sind wieder Relationen: Dies ermöglicht die beliebige Verkettung der betrachteten Operationen! 3.8 Typ-kompatible Relationen Definition Zwei Relationen R und S heissen typ-kompatibel, wenn R und S Teilmengen des selben Kreuzproduktes M1 × M2 × · · · × Mn von Mengen Mi (i = 1, 2, . . . , n) sind. Zwei Symmetrieklassen (also Äquivalenzklassen) von Relationen [R] und [S] heissen typ-kompatibel, wenn Relationen R0 [R] und S 0 ∈ [S] derart existieren, dass R0 typ-kompatibel zu S 0 ist. Kurz: Die Relationen besitzen das selbe Schema: sch(R) = sch(S) {R.A1 , R.A2 , . . . R.An } = {S.A1 , S.A2 , . . . , S.An } (Die Reihenfolge spielt keine Rolle!) c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 19 3.8 Typ-kompatible Relationen 3.8.1 Vereinigung ∪ Man schreibt R ∪ S. Das Schema der Vereinigung ist gleich dem Schema der beiden Relationen. sch(R) = sch(S) = sch(R ∪ S) A B 3.8.2 Durchschnitt ∩ Der Durchschnitt wird nicht benötigt, da R ∩ S = R\(R\S) = S\(S\R). A B 3.8.3 Differenz \ Die Differenz ist die Menge ohne die Schnittmenge einer anderen Menge. Lies: A ohne B A B 3.8.4 Kreuzprodukt × Das Kreuzprodukt zweier n- und m-stelliger Relationen R und S ist wieder eine Relation, welche das Schema sch(R) ∪ sch(S) besitzt. Dieses besitzt (n + m)-Tupel, wobei die ersten n Komponenten aus R und die letzten m Komponenten aus S stammen. Die Anzahl Zeilen beläuft sich auf |R| · |S| Zeilen. 3.8.5 Selektion σ Die Selektion σF (R) wählt diejenigen Tupel der Relationen R aus, d.h. selektiert sie, die das Selektionsprädikat F erfüllen. Der Selektionsoperator kann eine arithmetische Operation sein und ist ein unärer Operator! σAlter > 80 (Personen) 20 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 3.8 Typ-kompatible Relationen 3.8.6 Projektion Π Die Projektion ΠA (R) wählt die jenigen Attribute der Relation R aus, die in der Menge der Attributnamen A aufgeführt sind. Die Projektion filtert Zeilen (Tupel) heraus. ΠVorname, Name (Personen) 3.8.7 Umbenennung ρ Umbenennung Relation: ρS (R) Umbenennung Attribut: ρA1,new ←A1,old (R) = ρA1,old →A1,new (R) 3.8.8 Verbund ./ Aus zwei Relationen wird einen neue Relation. Jp (R, S) = R ./p S R ./a,b S = Πa,b,c,d (σR.a=S.a∧R.b=S.b (R × S)) Verbundarten Kreuzverbund Kartesisches Produkt der beiden Verbundrelationen. Θ - Verbund Bedingter Verbund R ./R.x<S.y S Equivalenz-Verbund Wie Θ-Join, nur muss die Bedingung eine Äquivalenz darstellen: R ./R.x=S.y S Natürlicher Verbund Wie Equi-Join, nur muss die Äquivalenz auf dem „gleichen“ Attribut sein: R ./ S = R ./R.x=S.x S 3.8.9 Division ÷ Die Division ist die Umkehrung des Kreuzprodukts. Wir berechnen S÷T. S T sno pno pno s1 s1 s1 s1 s2 s2 s3 s4 s4 p1 p2 p3 p4 p1 p2 p2 p3 p4 p2 p4 sno: Supplier Number; pno: Partnumber S = Lieferanten liefern Teile mit Teilenummer T = Teile (mit Nummern), die uns interessieren c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 21 3.8 Typ-kompatible Relationen Beispiel 10: Division – Welcher Lieferant kann ALLE (beide) Teilchen aus Tabelle T liefern? Lösung: S ÷ T Folgende „Zwischenschritte“ sind zur Bestimmung nötig: 1. Nur Lieferanten auflisten, welche Teile liefern: Πsno (S) sno s1 s2 s3 s4 2. Kreuzprodukt bilden. Damit sind in dieser neuen Menge alle möglichen Kombinationen zwischen Lieferanten, die Teile liefern, dargestellt (Diese Lieferanten liefern diese Teile). Πsno (S)×T sno pno s1 s1 s2 s2 s3 s3 s4 s4 p2 p4 p2 p4 p2 p4 p2 p4 3. Vom Kreuzprodukt die «Urmenge» S extrahieren. Nun bleiben jene Lieferanten und Teile übrig, welche NICHT in der «Urmenge» S enthalten waren (links), d.h. die TEILE NICHT LIEFERN konnten. Daraus sind für uns nur die Lieferanten von Interesse (rechts): (Πsno (S) × T )\S sno pno s2 s3 s4 p4 p4 p2 Πsno ((Πsno (S) × T )\S sno s2 s3 s4 4. Diese Menge («Teilenichtlieferer») ziehen wir von der Lieferantenliste (Schritt 1) nun wieder ab und somit bleiben jene Lieferer zurück, die also alle Teile liefern konnten: Πsno (S)\Πsno ((Πsno (S) × T )\S) = S ÷ T sno s1 22 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 3.9 Transformationsregeln 3.9 Transformationsregeln [S. 244/45] Join, Vereinigung, Schnitt und Kreuzprodukt sind kommutativ und assoziativ: R1 ./ R2 = R2 ./ R1 R1 ∪ R2 = R2 ∪ R1 R1 ∩ R2 = R2 ∩ R1 R1 × R2 = R2 × R1 R1 ./ (R2 ./ R3 ) = (R1 ./ R2 ) ./ R3 ) R1 ∪ (R2 ∪ R3 ) = (R1 ∪ R2 ) ∪ R3 ) R1 ∩ (R2 ∩ R3 ) = (R1 ∩ R2 ) ∩ R3 ) R1 × (R2 × R3 ) = (R1 × R2 ) × R3 ) Konjunktionen können aufgebrochen und nacheinander als Selektionen ausgeführt werden (auch umgek.): σp1 ∧p2 ∧···∧pn = σp1 (σp2 (. . . (σpn (R)) . . . )) Selektion kann an einer Projektion „vorbeigeschoben“ werden falls keine Attribut aus der Selektion entfernt werden: Πl (σp R)) = σp (Πl (R)) falls attr(p) ⊆ l Selektionen können an Joinop. oder Kreuzprodukten „vorbeigeschoben“ werden falls zum Join nur ein Attribut verwendet: σp1 (R1 ./ R2 ) = σp1 (R1 ) ./ R2 Selektionen und Kreuzprodukten als Equi-Join: σR1 .A1 =R2 .A2 (R1 × R2 ) = R1 ./R1 .A1 =R2 .A2 R2 3.9.1 Übersicht Relationenalgebra π, σ, ×, , ∪, −, ∩, ÷, , , π A2 A1 ↑ πA2 ,A3 ↑ R A2 A3 A4 , ,... σ A1 A3 , A1 A2 A2 ↑ σΦ ↑ R × A3 A4 A3 A4 R.A1 σto=from (Connection) S.B1 S.B2 R.A1 R.A2 ↑ × A1 πloc (Airport) R.A2 ... R ↑ B1 S B2 A1 Airport × Connection Airport c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger S.B2 R.A2 =S.B1 # $ A2 S.B1 R # $ A2 B1 code=from S B2 Connection 23 4 Relationale Anfragesprachen (SQL) Entität E1 hat eine 1 oder c Beziehung zur Entität E2 → Der Verweis basiert auf Primärschlüssel von Tabelle (E2) und wird in Tabelle (E1) als Fremdschlüssel bezeichnet. 1:1 oder 1:c-Beziehungen in SQL modellieren • Ist auf beiden Seiten einer Beziehung der Beziehungstyp 1 oder c: so bekommt entweder Tabelle (E1) oder Tabelle (E2) den Fremdschlüssel → keine Join-Tables erstellen! • Achten Sie auf möglichst wenig NULL-Werte (undefiniert)! 4.1 Einfache Datendefinition in SQL • • • • • • • character (n), char (n) character varying (n), varchar (n) numeric (p,s) [p = Anzahl Stellen, s = Nachkommastellen], integer blob oder raw für sehr große binäre Daten clob für sehr große String-Attribute date für Datumsangaben xml für XML-Dokumente Anlegen von Tabelle 4.1.1 Online-Ressource (Link) http://www.1keydata.com/de/sql/ 1 2 3 4 5 4.1.2 Anlegen von Tabellen create table Professoren( PersNr integer not null, Name varchar (30) not null, Rang character (2) ); 4.1.3 Referenzielle Integrität in SQL4 In Bezug auf Datenbanken werden Zustände als konsistent bezeichnet, wenn sie die Integritätsbedingungen erfüllen. Integritätsbedingungen beschreiben Annahmen, die über die Daten getroffen werden, beispielsweise ein bestimmter Datentyp, ein Wertebereich oder eine Abhängigkeitsbeziehung zwischen zwei Objekten. 4 siehe auch Kapitel 5.1 24 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 4.1 Einfache Datendefinition in SQL Relationale Datenbanksysteme bieten die Möglichkeit, bei der Definition eines relationalen Schemas Integritätsbedingungen zu formulieren, deren Einhaltung von dem System garantiert wird. Ein typisches Beispiel für Integritätsbedingungen sind Schlüssel- und Fremdschlüsselbeziehungen: • Kandidatenschlüssel: unique • Primärschlüssel: primary key, dessen Attribute autom. mit NOT NULL spezifiziert werden • Fremdschlüssel: foreign key mit references-Angabe Werden Indizes angelegt, so garantieren diese die Einhaltung der referentiellen Integrität (vgl. zwei Tabellen, die sich referenzieren, z.B. Person (key: ID), Adresse (key: ID)). Dabei werden Indextabellen angelegt, welche die entsprechenden zusammengehörenden Tupel „gruppieren“ – und dadurch den Datenzugriff erheblich beschleunigen können (siehe Kapitel 7.2ff.). Es lässt sich spezifizieren, auf welche Art die Einhaltung gewährleistet bzw. wie auf Änderungen reagiert werden soll. Eine Änderung, die eine Integritätsbedingung verletzt, kann entweder ganz unterbunden werden oder aber weitere Änderungen zur Wiederherstellung der Integrität nach sich ziehen. Beispiel 11: Eine neue Tabelle erstellen mit Fremdschlüsselbeziehung REFERENCES 1 2 3 4 5 6 7 8 9 10 create table Vorlesung( VorlNr int [ identity /* MSSQL */ | auto_increment /* MySQL */ ] primary key, /*...*/ ); create table Pruefungen ( P_Id int identity primary key, VorlNr int not null references Vorlesung -- referenziert die Tabelle ’ Vorlesung’ (Fremdschluessel) [ ON DELETE | ON UPDATE ] [ CASCADE | SET NULL | NO ACTION ] ); • ON DELETE CASCADE = beim Löschen des Referenz-Datensatzes wird der davon abhängige Datensatz in dieser Tabelle (Fremdschlüssel mit REFERENCES-Klausel) ebenso gelöscht. Dies kann nur bei einem Fremdschlüssel ausgelöst werden, nicht aber bei einem Primärschlüssel. • ON DELETE SET NULL = beim Löschen des Referenz-Datensatzes wird in der Tabelle das Feld des Fremdschlüssels mit NULL überschrieben und damit die Referenz auf den Fremddatensatz gelöscht. • ON DELETE NO ACTION = verhindert, dass ein referenzierter Datensatz beim Löschen auch aus der Referenz-Tabelle gelöscht wird (d.h. der entsprechende Datensatz in der Referenz-Tabelle kann erst gelöscht werden, wenn es keine Referenzen mehr auf ihn gibt). Beispiel 12: Mehrere Tabellen mit Fremdschlüsseln erstellen c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 25 4.2 1 2 3 4 5 Veränderungen am Datenbestand create table Studenten( MatrNr integer primary key, Name varchar(30) not null, Semester integer check Semester between 1 and 13 ), 6 7 8 9 10 11 12 create table Professoren( PersNr integer primary key, Name varchar(30) not null, Rang character(2) check (Rang in (’C2’, ’C3’, ’C4’)), Raum integer unique ); 13 14 15 16 17 18 create table hoeren( MatrNr integer references Studenten on delete cascade, VorlNr integer references Vorlesungen on delete cascade, primary key(MatrNr, VorlNr) ); 19 20 21 22 23 24 create table voraussetzen( Vorgaenger integer references Vorlesungen on delete cascade, Nachfolger integer references Vorlesungen on delete cascade, primary key(Vorgaenger, Nachfolger) ); 4.2 Veränderungen am Datenbestand Beispiel 13: Löschen von Tupeln (DELETE) 1 DELETE FROM Studenten WHERE Semester > 13; Beispiel 14: Verändern von Tupeln (UPDATE) 1 2 UPDATE Studenten SET Semester= Semester + 1; SELECT DISTINCT Rang FROM Professoren; 4.3 Anfragen über mehrere Relationen Beispiel 16: Welcher Professor liest "Mäeutik"? (WHERE ...AND) Q In der relationalen Algebra: Name, Titel (σPersNr = gelesenVon V Titel = ’Mäeutik’ (Professoren × Vorlesungen)) 26 Beispiel 15: Duplikateliminierung beim Auslesen (SELECT DISTINCT) 1 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 4.4 1 2 3 Aggregatfunktionen und Gruppierung SELECT Name, Titel FROM Professoren, Vorlesungen WHERE PersNr = gelesenVon AND Titel = ’Maeeutik’ ; Beispiel 17: JOIN und COUNT 1 2 3 4 5 -- Vorlesungen des jeweiligen Studenten, \dabei. SELECT s.Name as Student, v.Titel as Vorlesung FROM Studenten s INNER JOIN hoeren h ON s.MatrNr = h.MatrNr INNER JOIN Vorlesungen v ON h.VorlNr = v.VorlNr; 6 7 8 9 10 11 -- Anzahl Vorlesungen pro Student, \dabei. SELECT s.Name as Student, COUNT(*) as Vorlesungen FROM Studenten s INNER JOIN hoeren h ON s.MatrNr = h.MatrNr GROUP BY s.Name; 4.4 Aggregatfunktionen und Gruppierung Abfragen werden sehr häufig gruppiert, weil nicht nur einzelne Informationen, sondern auch Zusammenfassungen bzw. Auswertungen gewünscht werden. Diese Funktion übernehmen die Aggregatfunktionen. ACHTUNG: alle in der SELECT-Klausel aufgefuehrten Attribute (ausser die aggregierten) müssen auch in der GROUP BY-Klausel aufgefuehrt werden. Beispiel 18: Aggregatfunktionen AVG, SUM, GROUP BY 1 2 SELECT AVG (Semester) FROM Studenten; 3 4 5 6 7 -- PRO GRUPPE werden die Semesterwochenstunden aufsummiert SELECT gelesenVon, SUM (SWS) FROM Vorlesungen GROUP BY gelesenVon; Beispiel 19: Ergebnis durch Bedingungen mittels HAVING einschränken 1 2 3 4 5 SELECT gelesenVon, Name, SUM (SWS) FROM Vorlesungen, Professoren WHERE gelesenVon = PersNr and Rang = ’C4’ GROUP BY gelesenVon, Name HAVING AVG (SWS) >= 3; HAVING ist eine Bedingung, die auf aggregierte Werte angewendet werden kann. Der Befehl dient dazu, nicht alle Ergebnisse der Auswahl in die Ausgabe zu übernehmen, sondern nur diejenigen, die c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 27 4.5 Geschachtelte Anfrage den zusätzlichen Bedingungen entsprechen. Die WHERE Bedingung kann nämlich auf gruppierte Werte (GROUP BY) nicht angewendet werden. 4.5 Geschachtelte Anfrage 1 2 3 4 SELECT PersNr, Name, ( SELECT SUM (SWS) FROM Vorlesungen WHERE gelesenVon=PersNr ) AS Lehrbelastung FROM Professoren; 4.6 Verwertung der Ergebnismenge einer Unteranfrage Beispiel 20: Verschachtelte SELECT-Abfragen 1 2 3 4 5 6 7 8 -- ACHTUNG: Das innere SELECT-Statement muss immer komplett ausfuehrbar sein! Es beinhaltet die eigentliche Abfrage. SELECT tmp.MatrNr, tmp.Name, tmp.VorlAnzahl FROM ( -- inneres SELECT-Statement definieren und als ’tmp’ festhalten: SELECT s.MatrNr, s.Name, count(*) AS VorlAnzahl FROM Studenten s, hoeren h WHERE s.MatrNr=h.MatrNr GROUP BY s.MatrNr, s.Name) tmp WHERE tmp.VorlAnzahl > 2; -- best. Tupel aus innerer Abfrage auswaehlen Beispiel 21: Existenzquantor (NOT) EXISTS (NOT) EXISTS stellt eine Abfrage in Beziehung mit einer Unterabfrage (→ Attributverknüpfung) und liefert einen Wert zurück, falls die Unterabfrage mindestens eine Zeile enthält: 1 2 3 4 5 6 SELECT p.Name FROM Professoren p WHERE NOT EXISTS ( SELECT * FROM Vorlesungen v WHERE v.gelesenVon = p.PersNr ); -- Attributverknuepfung wird hier erstellt 4.7 Auswertung bei NULL-Werten Nullwerte = unbekannter Wert. In arithmetischen Ausdrücken werden Nullwerte propagiert, d.h. sobald ein Operand null ist, wird auch das Ergebnis null. Dementsprechend wird z.B. NULL + 1 zu NULL ausgewertet – aber auch NULL * 0 wird zu NULL ausgewertet. Logische Ausdrücke werden nach der boole’schen Logik (Wahrheitstabellen) berechnet. In einer WHEREKlausel wird ein Vergleich auf NULL mit dem IS-Operator umgesetzt: 28 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 4.8 1 Das «case»-Konstrukt SELECT * FROM Studenten WHERE Semester IS null; 4.8 Das «case»-Konstrukt Bei logischen Entscheidungen nach einem CASE-Statement wird immer nur die erste qualifizierende when-Klausel ausgeführt. Beispiel 22: Entscheidungen mittels CASE ...WHEN 1 2 3 4 5 6 7 SELECT MatrNr, ( CASE WHEN Note < WHEN Note < WHEN Note < WHEN Note < ELSE ’nicht END) 1.5 THEN ’sehr gut’ 2.5 THEN ’gut’ 3.5 THEN ’befriedigend’ 4.0 THEN ’ausreichend’ bestanden’ 4.9 JOINs in SQL Um Tabellen sinnvoll miteinander zu verknüpfen (= verbinden, engl. join), wurde die JOIN-Klausel für den SELECT-Befehl mit folgender Syntax eingeführt5 : Beispiel 23: Syntax von JOIN 1 2 3 SELECT <spaltenliste> FROM <haupttabelle> [<join-typ>] JOIN <verknuepfte tabelle> ON <bedingung> SQL kennt folgende JOINs: • cross join Kreuzprodukt • natural join natürlicher Join. Erweiterung des Cross Joins, dabei wird automatisch die Ergebnismenge der beiden Tabellen gefiltert. Diese Einschränkung basiert auf gleichen Spaltennamen. • (inner) join auch Equi-Join genannt, ist eine Verknüpfung innerhalb zweier Tabellen, bei denen als Bedingung die Werte der Verknüpfungs- bzw. Vergleichsspalten immer gleich sein müssen (zu verknüpfende Spaltennamen müssen dazu angegeben werden). Auch als Theta-Join möglich: funktioniert gleich wie vorheriges Beispiel, ausser dass die Bedingungsfelder nicht mehr unbedingt gleich sein müssen sondern auch ungleich sein können. • left, right oder full outer join äusserer Join • union join Vereinigungs-Join 5 http://de.wikibooks.org/wiki/Einführung_in_SQL:_Arbeiten_mit_JOIN (28.04.2013) c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 29 4.10 Rekursion Beispiel 24: mehrere Verknüpfungen direkt hintereinander 1 2 3 4 SELECT <spaltenliste> FROM <haupttabelle> [<join-typ>] JOIN <zusatztabelle1> ON <bedingung1> [<join-typ>] JOIN <zusatztabelle2> ON <bedingung2> [<join-typ>] JOIN <zusatztabelle3> ON <bedingung3> 4.10 Rekursion Grundfrage: «Welche Kurse müssen vor dem Besuch von ’Der Wiener Kreis’ besucht werden?» Beispiel 25: Rekursion (händisch) – oder die direkten Vorgänger von «Der Wiener Kreis» 1 2 3 4 5 6 -- Es kommen die Datensaetze 5041 und 5043 heraus: SELECT v1.Vorgaenger FROM voraussetzen v1, voraussetzen v2, Vorlesungen v WHERE v1.Nachfolger = v2.Vorgaenger -- ’1. Rekursionsebene’ AND v2.Nachfolger = v.VorlNr -- ’2. Rekursionsebene’ AND v.Titel=’Der Wiener Kreis’ → Für n Rekursionsebenen müssen auf diese Weise n Statements gemacht werden. Der Wiener Kreis 5259 Wissenschaftstheorie 5052 Bioethik 5216 Erkenntnistheorie 5043 Ethik 5041 Mäeutik 5049 Grundzüge 5001 4.10.1 Rekursionskonstrukt mittels WITH ... UNION ALL ... SELECT Die optionale WITH-Klausel bildet einen möglichen Start einer SELECT-Anfrage. Mit ihr können temporäre Hilfssichten definiert werden, auf die dann in der Anfrage selber zugegriffen werden kann. Damit sollen Anfragen übersichtlicher, strukturierter gestaltet werden bzw. rekursive Anfragen können programmiert werden. Die tatsächliche Ausformulierung des entsprechenden SELECTs ist allerdings zumindest gewöhnungsbedürftig. Hilfreich ist es, sich an diese Fragen zu halten: 30 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 4.10 Rekursion Vorgehen bei der Formulierung des Rekursionskonstruktes 1. Welche Spalten sollen in meiner Ergebnismenge auftauchen und/oder werden für die Rekursionsbedingung benötigt ? 2. Wie lautet der SELECT für den Satz, von dem die Rekursion ausgehen soll ? 3. Wie lautet die Rekursionsbedingung ? Statt n Rekursionsebenen händisch durchzulaufen wie im Beispiel 25 gibt es eine elegante Lösung, welche auf dem REKURSIONS-Konstrukt aufbaut. Es beinhaltet die Verbindung UNION ALL ... SELECT: 1 2 3 4 5 6 7 8 -- temporaere Sicht festlegen: WITH common_table ( spaltenliste ) AS ( SELECT ... -- Ursprungsselect UNION ALL -- Rekursionsbeginn SELECT ... -- Rekursionsselect ) -- Ausfuehren der definierten Abfrage: SELECT spaltenliste FROM common_table WHERE ... Beispiel 26: Rekursion über die „tmpTbl“ mittels WITH ... UNION ALL ... SELECT 1 2 3 4 5 6 7 8 9 10 11 12 -- temporaere Sicht tmpTbl mit der WITH-Klausel angelegt (Initialisierung): WITH tmpTbl (vorgaenger, nachfolger, position) AS ( -- URSPRUNGSSELECT: SELECT vorgaenger, nachfolger, 1 FROM voraussetzen WHERE nachfolger = 5259 -- REKURSIONSBEGINN: UNION ALL -- REKURSION Diese Sicht tmpTbl ist rekursiv definiert, da sie selbst in der Definition vorkommt (im 2ten SELECT; dieses wird wiederholt ausgefuehrt, bis sich keine Zusaetze mehr ergeben): SELECT v.vorgaenger,v.nachfolger, tmpTbl.position + 1 FROM voraussetzen v JOIN tmpTbl ON tmpTbl.vorgaenger = v.nachfolger ) -- Aus dieser Sicht werden dann die gewuenschten Tupel extrahiert (das 3te SELECT, das auf tmpTbl basiert). Ausfuehren der Abfrage: SELECT DISTINCT vorgaenger FROM tmpTbl Hier wird dann nun die gesamte Tabelle REKURSIV ausgelesen und der Wert der Position im Falle einer Stufenänderung um 1 erhöht. Der letzte Eintrag erhält dann den höchsten Positions-Wert (allenfalls ist dieser mit einem desc-Order umgekehrt zu sortieren). c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 31 4.11 Veränderungen am Datenbestand 4.11 Veränderungen am Datenbestand Vorgehen: 1. die Kandidaten für die Änderung werden ermittelt und «markiert» 2. die Änderung wird an den in Schritt 1. ermittelten Kandidaten durchgeführt Beispiel 27: Verschachtelte Abfrage mit IN (Mengenabfrage; mindestens ein Wert muss vorliegen) 1 2 3 4 5 DELETE FROM voraussetzen WHERE Vorgaenger IN ( SELECT Nachfolger FROM voraussetzen ); 4.12 Sichten (Views) Sichten sind ein wichtiges Konzept, um eine DBS an die Bedürfnisse unterschiedlicher Benutzergruppen anpassen zu können. Somit zeigen «Sichten» einen definieren Ausschnitt des gesamten Modells. Beispiel 28: Sicht mit zwei Spalten («Name» und «GueteGrad») als Rückgabewert 1 2 3 4 5 6 -- Statistische Sicht: CREATE VIEW PruefGuete(Name, GueteGrad) AS ( SELECT prof.Name, AVG(pruef.Note) FROM Professoren prof JOIN pruefen pruef ON prof.PersNr = pruef.PersNr GROUP BY prof.Name, prof.PersNr HAVING COUNT(*) > 50 ) 4.13 JDBC: Java Database Connectivity Zugriff auf Datenbanken via JDBC: Zugriff auf Datenbanken via JDBC Web-Anbindung von Datenbanken via Servlets/JDBC Browser Internet Webserver VorlesungsVerzeichnis.html Servlet-Engine Servlet VrlVrz Servlet VrlVrz Servlet VrlVrz JDBC (Java Database Conn.) Datenbank © A. Kemper / A. Eickler 73 4.13.1 Schritte in JDBC© A.zur Erstellung einer DB-Abfrage Kemper / A. Eickler 72 1. notwendige/n JDBC-Treiber laden: Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); 32 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 4.13 JDBC: Java Database Connectivity 2. Connection aufbauen: Connection conn = DriverManager.getConnection("jdbc:sqlserver:// localhost\dbSchema[:Port];Database=db", "user", "pwd"); 3. Statement-Objekt generieren: Statement stmt = conn.createStatement(); 4. SQL-Anfragen absetzen bzw. ResultSet erzeugen: ResultSet rset = stmt.executeQuery( "SQL-Statement"); 5. Ergebnismenge überprüfen bzw. iterieren: rset.next(); Beispiel 29: kompletter JAVA-Code zur Initialisierung und Auswertung von SQL-Statements 1 2 3 import java.sql.*; import java.io.*; public class ResultSetExample { 4 public static void main(String[] argv) { Statement sql_stmt = null; Connection conn = null; 5 6 7 8 // Verbindungsversuch: try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); conn = DriverManager.getConnection("jdbc:sqlserver://localhost\\ dbSchema[:Port];Database=db", "user", "pwd"); sql_stmt = conn.createStatement(); } catch (Exception e) { System.err.println("Folgender Fehler ist aufgetreten: " + e); System. exit(-1); } // DB-Abfrage ausfuehren und Verbindung schliessen: try { ResultSet rset = sql_stmt.executeQuery( "SELECT avg(Semester) from Studenten"); rset.next(); // Iterator ansetzen und pruefen, ob Ergebnis 9 10 11 12 13 14 15 16 17 18 19 20 21 22 // ACHTUNG: IMMER Objekt als Rueckgabewert --> getDouble(1)! System.out.println("Durchschnittsalter: " + rset.getDouble(1)); rset.close(); } catch(SQLException se) { System.out.println("Error: " + se); } 23 24 25 26 27 28 } 29 30 } c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 33 4.13 JDBC: Java Database Connectivity 4.13.2 Daten einfügen mittels verschiedener Statements executeQuery() executeUpdate() execute() Gewählt beim Auslesen von Datensätzen, d.h. bei SELECT-Statements. Erzeugt ein ResultSet als Rückgabe. Eingesetzt bei Datensatzmodifikationen, z.B. DROP TABLE, DROP DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE. Erzeugt einen int-Wert als Rückgabewert, welcher die Anzahl der von der Änderung betroffenen Tupel definiert. Diese Methode dient der generellen Ausführung von SQL-Statements. Erzeug einen boolean-Wert: TRUE signalisiert das Resultat in der Form eines ResultSet. Beispiel 30: JAVA-Code zum Ausführen eines INSERT / UPDATE-Statements Statement stmt = conn.createStatement(); 1 2 // INSERT mit execute: gibt boolean zurueck. boolean isok = stmt.execute("INSERT into Studenten(name, personalNummer) values(’Dagobert’,1532)"); 3 4 5 6 7 // UPDATE mit executeUpdate: gibt int zurueck. int anzahlUpdates = stmt.executeUpdate("UPDATE Studenten SET name = ’ Dagobert’ WHERE personalNummer = 1532"); Beispiel 31: Prepared Statement 1 2 3 4 5 6 7 PreparedStatement stmt; stmt = con.preparedStatement("Insert into Mitarbeit(Mitarbeiter, ..., ..., ...,)" + " values (?, ?, ?, ?)"); stmt.setInt(1, mitarbeiterID); ... stmt.setDate(4, beginDatum); return stmt.execute(); Beispiel 32: Rückgabe der neu generierten ID bei INSERT-Statement Statement stmt = conn.createStatement(); 1 2 // INSERT mit executeUpdate: gibt int zurueck. int n = stmt.execute("INSERT into Studenten(name, personalNummer) values (’Dagobert’,1532)", Statement.RETURN_GENERATED_KEYS); ResultSet rs = stmt.getGeneratedKeys(); // neu generierter Schluessel 3 4 5 34 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 4.13 6 7 JDBC: Java Database Connectivity rs.next(); // Iterator positionieren int genKey = rs.getInt(1); // Wert als int auslesen c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 35 5 Datenintegrität 5.1 Referentielle Integrität6 Die Attributwerte eines Schlüssels identifizieren ein Tupel eindeutig innerhalb einer Relation. Wir reden von einem Fremdschlüssel wenn der Schlüssel einer Relation als Attribut einer in einer anderen Relation vorkommt. Dabei gelten die folgenden Eigenschaften: Referentielle Integrität R und S sind zwei Relationen mit dem Schema R und S, κ ist Primärschlüssel von R. Dann ist α ein Fremdschlüssel in S und es gilt für alle Tupel s ∈ S: 1. s.α enthält entweder nur Nullwerte oder nur Werte ungleich Null. 2. wenn s.α keine Nullwerte enthält, dann existiert ein Tupel r ∈ R mit s.α = r.κ. Die Erfüllung dieser Eigenschaften nennt man referentielle Integrität. 5.2 Datenbank-Trigger Als Trigger (deutsch: „Auslöser“) bezeichnet man in SQL eine Anweisungsfolge (eine Abfolge von Aktionen), die ausgeführt wird, wenn eine verändernde Anweisung auf einer bestimmten Tabelle automatisch vom DBMS ausgeführt werden soll. Ein Trigger ist in folgenden Situationen nützlich: • Werte in einer Zeile einer Tabelle sollen festgelegt werden (wird vor allem benutzt, wenn es keine AutoInc-Spalte gibt). • Werte sollen vor dem Speichern auf ihre Gültigkeit geprüft werden. • Veränderungen in der Datenbank sollen automatisch protokolliert werden. • Die Regeln der referenziellen Integrität sollen mit Hilfe der Fremdschlüssel-Beziehungen überwacht werden. Merke: Man kann Trigger entweder so schreiben, dass sie für jedes zu ändernde Tupel einmal (FOR EACH ROW) oder für jedes verändernde SQL-Statement einmal (FOR EACH STATEMENT, dieser Trigger ist aber nur mit AFTER-Trigger möglicha ) ausgeführt werden. a 6 wird auch aufgerufen, wenn kein Tupel vom DELETE- oder UPDATE-Statement betroffen war siehe auch Kapitel 4.1.3 36 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 5.2 Datenbank-Trigger Beispiel 33: Row-Level Trigger BEFORE UPDATE 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 -- CREATE TRIGGER Anweisung mit dem Triggernamen ’name’: CREATE TRIGGER name -- Trigger wird aufgerufen BEVOR (NACHDEM) die Bed. aus der Tabellendef. ueberprueft BEFORE [ AFTER ] update [ insert | delete ] -- Aufruf bei Operation on Professoren -- bezieht sich auf die angegebene Tabelle FOR EACH ROW -- Triggercode fuer jede veraenderte Zeile when (old.Rang is not null) -- Behandlung beginnt hier: BEGIN -- :OLD --> haelt das aktuelle (zu modifizierende) Tupel -- :NEW --> haelt das einzufuegende (neue) Tupel if :OLD.Rang = ’C3’ and :NEW.Rang = ’C2’ then :NEW.Rang := ’C3’; end if; if :old.Rang = ’C4’ then :new.Rang := ’C4’ end if; if :new.Rang is null then :new.Rang := :old.Rang; end if; end Wichtig: der mittels Trigger kontrollierte Tupel wird bei einer COUNT-Abfrage mitgezählt! Dies bedeutet, dass allfällige Zählroutinen dies berücksichtigen müssen. Beispiel 34: Row-Level Trigger AFTER INSERT und Umbenennung der Übergangsvariablen NEW 1 2 3 4 5 6 7 CREATE TRIGGER new_movie AFTER INSERT ON schauspieler REFERENCING NEW AS n -- Uebergangsvariable ’NEW’ umbenennen FOR EACH ROW UPDATE filmeProSchauspieler SET anzahl_filme = anzahl_filme + 1 WHERE n.name = filmeProSchauspieler.name Beispiel 35: Trigger in MS-SQL MS-SQL kennt keine Row-Level Trigger. Ein entsprechendes Statement lautet: 1 2 3 4 5 CREATE TRIGGER name ON [ table | view ] [ FOR | AFTER | INSTEAD OF ] [ INSERT | UPDATE | DELETE ] AS sqlStatement c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 37 5.3 DB-Prozeduren (Stored Procedures) 5.3 DB-Prozeduren (Stored Procedures) DB-Prozeduren sind eine Art von Funktionen. Sie werden vom DBA (oder vom Benutzer) erstellt und genauso wie alle anderen Datenbankobjekte (Tabellen, Views usw.) in einer Datenbank abgelegt. Jede DB-Prozedur beinhaltet sowohl SQL- als auch prozedurale Anweisungen. Jeder DB-Prozedur können Daten als Werteparameter zugewiesen werden. Die Übergabe wird beim Prozeduraufruf durchgeführt. Innerhalb einer DB-Prozedur können SQL-Anweisungen verwendet werden, um Datenwerte einer Datenbank abzufragen bzw. zu modifizieren. Beispiel 36: DB-Prozedur (Stored Procedure) 1 2 3 4 5 CREATE PROCEDURE mkDays(@start date, @anz int) as BEGIN -- Deklaration von Variablen mittels ’@’ declare @i int, @nextDay date select @i=1, @nextDay=@start 6 7 8 9 10 11 12 13 14 15 while (@anz>=@i) begin if (datepart(weekday,@nextDay) in (7,1)) -- Saturday, Sunday insert into kalender(datum, istfrei) values(@nextDay,1) else insert into kalender(datum, istfrei) values(@nextDay,0) select @i = @i + 1, @nextDay=dateadd(day,1,@nextDay) end -- Ende der Schleife end -- Ende der Prozedur GO -- Erstellung forcieren 38 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 6 Relationale Entwurfstheorie 6.1 Funktionale Abhängigkeiten [171ff] Merke: Eine Relation wird durch Attribute oder Attributwerte definiert. Bestimmen einige dieser Attribute bzw. -werte eindeutig die Werte anderer Attribute oder Attributwerte, so spricht man von funktionaler Abhängigkeit. Sie wird oft mit FD (engl. functional dependency) abgekürzt und wie folgt dargestellt: α→β Aus dem Attributwert von A ergibt sich also eindeutig der Attributwert von B. Beispiel 37: Zwei funktionale Abhängigkeiten (PLZ und AHV-Nummer) In der Tabelle „Ort“ sind die Attribute „Ort“ und „Kanton“ funktional abhängig vom Attribut „PLZ“, da jeder Ort mit einer bestimmten PLZ verknüpft ist. Es können nicht zwei verschiedene Orte mit der selben PLZ existieren. Damit wird jeder PLZ eindeutig ein Ort zugewiesen: {PLZ} → {Ort, Kanton} Ort und Kanton sind funktional abhängig von ihrer Postleitzahl. {Ort, Kanton} → {PLZ} Die Postleitzahl ist auch funktional abhängig von Ort und Kanton. {AHV-Nummer} → {Name, Vorname, Geburtsdatum, . . . } Die gleiche AHV-Nummer müsste immer die gleiche Person liefern. . . 6.1.1 Schlüssel Bestimmt eines oder einige Attribute einer Relation eindeutig die Werte aller Attribute der Relation, so spricht man von einem Schlüssel. Jedes Tupel dieser Relation ist damit eindeutig durch die Werte des/der Schlüssel-Attribute(s) bestimmt. 6.2 Normalisierung Die Normalisierung bezweckt die redundanzfreie Speicherung von Informationen innerhalb Tabellen der Datenbasis. Dies wird durch die entsprechende Zuweisung der Attribute zu den einzelnen Tabelle erreicht. 1. Normalform: alle Tabellenattribute weisen nur einfache Attributwerte (keine Dopplungen pro Attribut) auf (auch Nullwerte sind erlaubt). 2. Normalform: Tabelle ist schon in der 1. NF und jedes nicht zum ID-Schlüssel gehörende Attribut ist voll abhängig vom ganzen ID-Schlüssel (und nicht nur von Teilen davon). c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 39 6.3 Normalisierung mit Boyce-Codd (BCNF) [190ff] 3. Normalform: Tabelle ist schon in der 2. NF. Jedes Attribut darf nur vom ID-Schlüssel abhängig sein und daneben existieren untereinander keine weiteren funktionalen Abhängigkeiten! 6.3 Normalisierung mit Boyce-Codd (BCNF) [190ff] Das Ziel der Boyce-Codd Normalform (BCNF) besteht darin, dass Informationseinheiten (Fakten) nicht mehrmals, sondern nur genau einmal gespeichert werden. Eine Tabelle ist in der BCNF, falls für jede funktionale Abhängigkeit α → β gilt: Datenmanagement 1. alle Attribute haben atomare Wertebereiche (d.h. die einzelnen Werte sind z.B. keine Listen, keine Mengen, keine Strukturen) 2. für jede funktionale Abhängigkeit α → β in Tabelle (T) gilt auch: α hat Schlüsseleigenschaft Diskussion-2 Tabelle Vorlesungsanlass Titel, SWS, gelesenVon sind funktional abhängig von VorlNr. VorlNr hat Schlüsseleigenschaft (Primärschlüssel) → Tabelle ist in BCNF. Vorlesung «entartet» zu Vorlesungsanlass, pro Titel sind SWS und gelesenVon identisch (meine Konsequenz bei NICHTERFÜLLUNG BCNF: Normalisierungs-Verfahren. Annahme hier!) -> von Verstoss gegen BCNF Eine Tabelle T, die gegen (2.) verstösst, wird aufgeteilt in 2 Tabellen T1 (T ohne β) und T2 (mit α und β). V 1.1 40 © Hochschule Luzern, Datenmanagement 7 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 7 Physische Datenorganisation 7.1 Hintergrundspeicher Die RAID-Technologie (redundant Array of inexpensive Disks) nutzt aus, dass man anstelle eines einzigen (entsprechend grossen) Laufwerks effizienter mehrere (entsprechend kleinere und billigere) Laufwerke parallel betreiben kann. Dabei arbeiten die verteilten Laufwerke durch einen entsprechenden RAID-Controller nach aussen transparent wie ein einziges logisches Laufwerk. RAID 1: Spiegelung der Daten. Datensicherheit durch Redundanz aller Daten (Engl. mirror). Lastba- lancierung beim Lesen: z.B. kann Block A von der linken oder der rechten Platte gelesen werden. RAID 1:Aufgrund Spiegelung der Redundanz(mirroring) steht nur die Hälfte der «Gesamtspeichermenge» zur Verfügung. A B A B C D C D Datensicherheit: durch Redundanz aller Daten (Engl. mirror) RAIDSpeicherbedarf 5: Striping von Blöcken, Verteilung der Paritätsblöcke. Die Daten werden auf verschiedenen Doppelter Datenspeichern verteilt dieABlöcke E linken – F – G – H). Sogenannte Paritätsblöcke (z.B. Lastbalancierung beim Lesen: z.B.(siehe kann z.B. Block von der oder der rechten Platte gelesen werden PE−H ) speichern eine Prüfsumme (durch XOR-Verknüpfung der einzelnen Bitstrings dazugehö Aber beim Schreiben müssen beideder Kopien geschrieben werden x-y render Blöcke) auf einem verteilten Datenspeicher: Kann aber parallel geschehen Dauert also nicht doppelt so lange wie das Schreiben nur D eines Blocks M RAID 5: Striping von Blöcken, Verteilung der Paritätsblöcke P A E B F © A. Kemper / A. Eickler I M J PM-P C PI-L G N D PE-H G K 7 O PA-D H L P Sobald ein Datenspeicher ausfällt, können die darauf lagernden Daten wieder hergestellt werden, weil sich die Lastbalancierung ursprünglichen Bitstrings der RAID abhängigen Blöcke aufgrund der Paritätsblöcke wieder Bessere als bei 4 rekonstruieren Sofern abereingesetzt zwei Platten defekt sind, sind die betroffenen Daten nicht mehr Wird in derlassen. Praxis häufig rekonstruierbar. Guter Ausgleich zwischen Platzbedarf und Leistungsfähigkeit Datenbankpuffer: Datenmanagement zwischen Haupt- und D M Hintergrundspeicher. G Alle Operationen auf Daten müssen innerhalb des durchgeführt werden. Dabei kann © A. Kemper / A. Hauptspeichers Eickler 8 also nicht direkt auf den Seiten des Hintergrundspeichers gearbeitet werden; ein Datenbankpuffer übernimmt die clevere Verwaltung des Datentransfers zwischen Haupt- (temporäre Speicherung) und Hintergrundspeicher (persistente Speicherung). c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 41 7.2 B-Bäume 7.2 B-Bäume7 Normale Binärbäume sind immer balanciert und wurden als Suchstruktur für den Hauptspeicher konzipiert und lassen sich nicht effektiv als Speicherstruktur auf Seiten des Hintergrundspeichers abbilden. Deshalb braucht es Mehrwegbäume, deren Knoten einer Seite des Hintergrundspeichers entsprechen. Eigenschaften eines B-Baumes Jeder Knoten hat in einer Datensatz-Seite mindestens k und höchstens 2k Einträge. Somit hat der Baum immer eine garantierte Auslastung von ≥ 50% (d.h. es gibt keinen Knoten, der weniger als 50% an Einträgen haben darf): Jeder Eintrag besteht aus Suchschlüssel, Verweis (auf die nächste Seitennummer) und Daten. Die Einträge werden – basierend auf ihrem Wert im Suchschlüssel (bzw. dem Primärschlüssel des Datensatzes) – in einem entsprechenden Knoten versorgt. Jeder Knoten hat also eine Referenz zu einem nächsten Knoten, der wiederum Einträge hält (z.B. im linken Knoten werden Werte, die kleiner sind als der Suchschlüssel des darauf referenzierenden Eintrags abgelegt; im rechten jene, die grösser sind). Dabei können Einträge von Knoten dynamisch und immer wieder umplatziert werden bzw. ihren Knoten wechseln – je nachdem, welche Werte die Suchschlüssel der neu einzufügenden Knoten haben. Da jeder Eintrag nebst Suchschlüssel und Verweis auch alle Daten speichert, kann die benötigte Speichermenge, die dafür in einem Knoten gebraucht wird, sehr gross werden. Jeder Suchschlüssel kommt dabei genau an in einem Eintrag vor. 7 42 siehe auch Weblink: http://wikis.gm.fh-koeln.de/wiki_db/Datenbanken/B-Baum c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 7.3 B+ -Bäume («Clustered Index») 7.3 B+ -Bäume («Clustered Index»)8 Eigenschaften eines B+ -Baumes Der B+ -Baum besteht aus Basisdaten und Indexstruktur. Er ist ein besonderer B-Baum. In einem B+ -Baum wird NUR der Suchschlüssel eines Eintrags gespeichert (vgl. Analogie: Datenstruktur im Telefonbuch [Ort → Name → Eintrag]). Die vollständigen Tabellensätze werden sortiert nach dem Primärschlüssel in den Blattknoten gespeichert (und gehören aber auch zum Baum, siehe Abbildung 3!). Diese Blattknoten bezeichnet man auch als Basisdaten. Innerhalb eines Blattknotens (Block/Page) sind die Sätze (Einträge) physisch sortiert nach dem Primärschlüssel, was sich durch eine entsprechende Verkettung aller Blattknoten ergibt. Zudem besitzt der Knoten einen «internen Schlüssel», welchen man TID, Tupel Identifier, nennt. Der TID ist ein INTERNER ZEIGER in einem DB-System auf eine entsprechende Datensatz-Seite (siehe Abbildung). Verschiebung von einer Seite auf eine andere Bei der nächsten Verschiebung wird der „Forward“ auf Seite 4711 geändert (kein Forward auf Seite 4812) D M G © A. Kemper / A. Eickler 108 Abbildung 2: Tupel Identifier und Pages in B+ -Bäumen Dies ist ein Konzept, welches auch bei variabler Länge der Datensätze (Einträge) ihre Gültigkeit behält: sollten die Datenelemente (Einträge) tatsächlich umgelagert werden müssen (d.h. ein Knoten hat in der Seite keinen Platz mehr), so kann der Tupel auf einen neuen TID zeigen (siehe Grafik rechts in obiger Abbildung). 8 siehe auch Weblink: http://wikis.gm.fh-koeln.de/wiki_db/Datenbanken/B-Plus-Baum c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 43 7.4 B*-Bäume («non-clustered Index») <---------------------Indexstruktur------------------> Basisdaten <-------------------------------------B+-Baum--------------------------------------> Die Zugriffseinheiten von Indexstruktur und Basisdaten sind Blöcke/ Pages (oben blau + und gelb eingefärbt). Die Blöcke der Indexstruktur haben hier 2 bis 4 Einträge, die Blöcke der Basisdaten enthalten jeweils 2 Sätze. Sehr unrealistische Zahlen, aber geeignet für die Visualisierung des Aufbauprinzips. Normalerweise enthalten die Blöcke der Indexstruktur mehrere 100 Einträge – sehr viel mehr als vollständige Sätze in die Blöcke der Basisdaten passen! Die Blöcke der Basisdaten sind hier „in Reihenfolge“ dargestellt – wünschenswert, aber selten. Die logische Reihenfolge ist durch die Verkettung (geknickte Pfeile) sichergestellt. Abbildung 3: Strukturen von B -Bäumen 7.4 B*-Bäume («non-clustered Index») Eigenschaften eines B*-Baumes Datenmanagement: Visualisierung B+- und B*-Baum 2/8 Der B*-Baum besteht nur aus der Indexstruktur. Daher kann es mehrere B*-Bäume für eine Tabelle geben. Ein B*-Baum ermöglicht jeweils schnelle, gezielte Suche anhand von einem Attribut der Tabelle – möglich auch: anhand einer Attributkombination. Die letzte Ebene des B*-Baumes (Blattknoten-Ebene) hat für jeden Datensatz einen Verweis in die Basisdaten – geordnet nach den Attributwerten (im Beispiel nach Kurzname). Die Basisdaten gehören nicht zum B*-Baum (siehe Abbildung 4)! Im B+ -Baum hatten wir zudem eine Einschränkung auf das Primärschlüsselattribut, jetzt kann es ein beliebiges anderes Attribut sein. 44 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 7.5 <--------------------B*-Baum----------------------> Beispiele Basisdaten (s.o.) ( ) Blattknoten-Ebene, ihre Blöcke sind verkettet. Abbildung 4:sind Strukturen Die beiden Bäume Die Blöcke als Zugriffseinheiten wiederzusammen: gelbvon bzw. B*-Bäumen blau gefärbt. Die Blöcke des B*-Baumes haben hier 2 bis 3 Einträge – wieder sehr unrealistisch, normalerweise (weit) über 100! Die Verkettung der Basisdaten ist hier nicht gezeigt (irrelevant). Es ist auch nicht ausgeführt, dass jeder Satz referenziert wird, jeder Block der Basisdaten also mehrmals referenziert wird, wenn er mehrere Sätze enthält. Eine entsprechende Darstellung würde zu einem Pfeilwirrwarr (Durcheinander) führen. Aber die Blattknoten-Ebene enthält genau so viele Einträge wie es Sätze in den Basisdaten gibt (hier 22). Datenmanagement: Visualisierung B+- und B*-Baum 4/8 Abbildung 5: Vergleich von von B+ und B*-Bäumen 7.5 Beispiele Beispiel 38: Sequentielle Suche im B-Baum Wie viele Indexblöcke müssen sequentiell durchsucht werden bei einer Suche nach Name? Wir kennen folgende Angaben: – Anzahl Datensätze = 106 – Bytes pro Datensatz = 256 – Grösse eines Indexblocks = 4 KB Datenmanagement: Visualisierung B+- und B*-Baum 5/8 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 45 7.5 Beispiele Es folgt daraus: Anzahl = Anzahl DS · Bytes/DS Blockgrösse = 106 · 256 Bytes/Datensatz 4KB = 620 500 Beispiel 39: Höhe eines im B∗ -Baumes bei einer Suche Wie hoch muss ein B∗ -Baum sein bei einer Suche nach Name? Wir kennen folgende Angaben: – Anzahl Datensätze = 106 – Grösse eines Indexblocks = 4 KB – Länge eines Namens = 20 B – Länge der Verweise im Indexblock = 4 B – Total Länge pro Index = 20B + 4B = 24B – Füllgrad Indexblock (Annahme): 80% Es folgt daraus: Einträge Indexblock = 4KB 24B · 8 10 = 136 Die Höhe des B∗ -Baumes ergibt sich aus dem Umstand, dass 136 × 136 × 136 = 1363 ≥ 106 . Somit lässt sich sagen, dass der Baum eine Höhe von k = 3 haben muss, da k = 2 < log136 (106 ) < 3 . Allgemein: Höhe = logAnzahl Einträge pro Block (Anzahl Datensätze) . Beispiel 40: Anzahl der Blockzugriffe in einem B*-Baum Wir rechnen mit den Angaben aus dem vorherigen Beispiel: Anzahl Blockzugriffe = Baumhöhe + Zusätzlicher Block für den vollständigen Satz = 3 + 1 = 4. Allgemein: Anzahl Blockzugriffe = Baumhöhe + Zusätzlicher Block für den vollständigen Satz . Beispiel 41: Anzahl der nötigen Blöcke in einem B*-Baum (mit Verweisen) Wir kennen folgende Angaben: – Anzahl Aufträge in der DB (Datensätze) = 106 – durchschnittliche Einträge pro Tag = 100 – Grösse eines Indexblocks = 1 KB – Länge eines Datums = 8 B – Länge der Verweise im Indexblock = 4 B – Total Länge pro Index = 8B + 4B = 12B – Füllgrad Indexblock (Annahme): 80% Einträge Indexblock = 1KB 12B · 8 10 = 67 Höhe = log67 (1’000’000) = 4 46 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 7.6 Die Verweise auf 100 Einträge passen in 100 67 Statisches Hashing = 2 Blöcke. Gesamtblockzahl = Anzahl versch. Blöcke + Baumhöhe + Anzahl Verweise = 100 + 4 + 2 = 106. Allgemein: Gesamtblockzahl mit Verweisen = Anzahl versch. Blöcke + Baumhöhe + Anzahl Verweise . 7.6 Statisches Hashing Beim Hashing wird mit Hilfe einer Hashfunktion der Schlüssel auf einen Behälter („Bucket“) abgebildet, der die dem Schlüssel zugehörigen Daten enthält. Formal: h : S → B, wobei S eine beliebig grosse Schlüsselmenge und B eine Anzahl an Behältern im Intervall [0 . . . n ist. Ein paar wichtige Begriffe (aus http://www.informatik.uni-jena.de/dbis/lehre/ws2010/ dbsem/Ausarbeitungen/a_StatischesHashing.pdf): Hashwert Ist das Ergebnis der jeweiligen Hashfunktion. Hashtabelle Ist eine Symboltabelle, die mit den Hashwerten indiziert ist. Kollision Tritt auf, wenn ein Paar von Schlüsseln den gleichen Hashwert besitzt. Das bedeutet: h(k) = h(k 0 ), selbst wenn k 6= k 0 . k und k 0 sind dann Synonyme und befinden sich in einer Kollisionsklasse. Kollisionsauflösung Wird auch Überlaufbehandlung genannt. Hier geht es um Verfahren zur Vermeidung von Kollisionen. Eine Methode für die Wahl einer Hashfunktion ist die Divisionsrestmethode – auch Restklassenbildung genannt. Mittels der Formel h(k) = k mod m werden hier die Hashwerte berechnet. Dabei ist k ein Schlüssel aus dem Schlüsselraum K und m istStatisches gegeben. Meist wird m als Primzahl gewählt, die nicht nah Hashing an einer Zweierpotenz liegt. Wäre m eine Zweierpotenz, dann würden in Bitdarstellung nur die niederen Divisionsrestverfahren mit der Faltungsmethode und erhöht dadurch die (log m) Bits von k betrachtet, was verhindert werden soll. Gleichverteilungseigenschaften. Andere Methoden sind das Multiplikationsverfahren, die Basistransformation oder die schon genannte Faltung. Abbildung 2: Divisionsrestmethode Besonders dann,4.1 wenn eine konkrete Perfektes HashingSchlüsselverteilung vorher nicht bekannt ist, wird das Divisionsrestverfahren empfohlen. Beim Perfekten Hashing [4] ist die Anzahl der zu hashenden Schlüssel vorab bekannt. Dies tritt z.B. bei einer Liste von Schlüsselwörtern einer Programmiersprache oder bei Namen von Methoden eines Objektes auf. Hier kann man die Hashfunktion vorher dementsprechend anpassen um eine optimale Verteilung bei der Speicherung der Datensätze zu sichern. Kollisionen der Hashwerte werden so gänzlich vermieden. c The Software Bakery | BrunoHashing Leupi, Tobias Edy Wermelinger Für perfektes [OW02]Maestrini, ordnet man einfach alle Schlüssel lexikographisch und bildet jeden einzelnen Schlüssel auf eine Ordnungsnummer ab (laufende Nummer als Primärschlüssel). 47 7.6 Statisches Hashing 7.6.1 Vorteile dabei werden die Restklassen definiert und die entsprechenden Schlüssel in der zugehörigen «RestklassenSchublade» (Bucket) referenziert. Bei einer Suche bzw. Vergleichen muss dann nur der entsprechende Bucket verglichen, statt die gesamte Tabelle durchforstet werden. 7.6.2 Nachteile Das Verfahren ist grundsätzlich eher problematisch, da die Verteilung auf die Restklassen nie eine Gleichverteilung der Datensätze ergibt (aufgrund von Löschvorgängen entstehen Lücken in den Primärschlüsseln, welche dann ungleich verteilt sein könnten – wie man in der Grafik sofort sieht): Statisches Hashing D M G © A. Kemper / A. Eickler 114 Beispiel 42: Berechnung der Restklassen bei Statischem Hashing Wieviele Restklassen werden benötigt? Wir kennen folgende Angaben: – Anzahl Datensätze = 106 – Grösse eines Blocks = 4 KB – Anzahl Sätze pro Block = 8 48 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 7.6 Man sieht sofort: Anzahl Blöcke = 106 8 Statisches Hashing = 1250 000 Damit haben wir den Divisor berechnet und können die Hashtabelle mod(1250 000) führen. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 49 8 Anfragebearbeitung / -Optimierung Ein Optimierung wird wie in Abbildung 6 ausgeführt: Deklarative Anfrage Scanner Parser Sichtenauflösung Algebraischer Ausdruck AnfrageOptimierer Auswertungsplan (QEP) Codeerzeugung Ausführung Abbildung 6: Ablauf einer Anfrageoptimierung 8.1 Logische Optimierung Eine SQL-Anfrage erfolgt i.d.R. immer nach dem Muster: SELECT ...FROM ...WHERE ... Beispiel 43: SQL-Select und optimierte relationale Algebra (ohne Baum) SQL: SELECT Titel FROM Professoren, Vorlesungen WHERE Name = ’Popper’ and PersNr = gelesenVon; Relational: ΠTitel (σName = ’Popper’∧PersNr = gelesenVon (Professoren × Vorlesungen)) optimiert: ΠTitel (σPersNr = gelesenVon (σName = ’Popper’ (Professoren) × Vorlesungen)) Merke: Im ersten Schritt wird eine logische Optimierung durchgeführt. Grundsätzlich werden hierbei die Daten möglichst früh möglichst klein gemacht (σ möglichst nahe zu den Blättern). Achtung!: Π nicht direkt nach Blatt, Ansonsten gehen die B+-Strukturen verloren! 50 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 8.1 Logische Optimierung Beispiel 44: SQL-Select und optimierte relationale Algebra (mit Baum) SQL: SELECT DISTINCT s.Semester FROM Studenten s, hören h, Vorlesungen v, Professoren p WHERE Name = ’Sokrates’ and v.gelesenVon = p.PersNr and v.VorlNr = h.VorlNr and h.MatrNr = s.MatrNr; Relational: ΠSemester (σName = ’Sokrates’∧gelesenVon = Professoren.PersNr∧... (Studenten×hören×Vorlesungen×Professoren)) • Vergleich der beiden Statements: Πs.Semester • Πs.Semester σp.Name=! Sokrates! ∧v.gelesenVon... s.MatrNr=h.MatrNr × × × v.VorlNr=h.VorlNr p.PersNr=v.gelesenVon σp.Name= Abbildung 7: Baumdarstellung vor (links) und nach (rechts) der Optimierung 8.1.1 Heuristische Anwendungen der Transformationsregeln Opimierungsheuristik Aufbrechen von Selektionen (→ alle σ-Operationen nacheinander schreiben) Verschieben der Selektionen soweit wie möglich nach unten im Operatorbaum Zusammenfassen von Selektionen und Kreuzprodukten zu (Index-)Joins Bestimmung der Reihenfolge der Joins in der Form, dass möglichst kleine Zwischenergebnisse entstehen 5. unter Umständen Einfügen von Projektionen 6. Verschieben von Projektionen soweit wie möglich nach unten im Operatorbaum 1. 2. 3. 4. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 51 8.2 Physische Optimierung 8.2 Physische Optimierung Physische Optimierung heisst Auswahl der richtigen Operation. • • • • Nested-Loop Index-Join Hash-Join Sort/Merge-Join Das Kostenmodell stellt dazu Funktionen zur Verfügung, die den Aufwand, d.h. die Laufzeit, der Operatoren der physischen Optimierung abschätzen. Dafür sind verschiedene Parameter nötig, die in der Abbildung 8 dargestellt sind: Abbildung 8: Kostenmodell zur physischen Optimierung D M G 8.2.1 Selektivität Sind verschiedene Strategien anwendbar, so benötigt man zur Auswahl eine Kostenfunktion. Sie basiert © A. Kemper / A. Eickler 33 auf dem Begriff der Selektivität. • Die Selektivität eines Suchprädikats schätzt die Anzahl der qualifizierenden Tupel relativ zur Gesamtanzahl der Tupel in der Relation. • Beispiele: – die Selektivität einer Anfrage, die das Schlüsselattribut einer Relation R spezifiziert, ist 1/#R, wobei #R die Kardinalität der Relation R angibt. – Wenn ein Attribut A spezifiziert wird, für das i verschiedene Werte existieren, so kann die Selektivität als (#R/i)/#R oder 1/i abgeschätzt werden. 8.2.2 Übersetzung der logischen Algebra Hierbei werden die einzelnen Operatoren der logischen Algebra (siehe Beispiel 44) in eine äquivalente Darstellung der pysischen Algebra übersetzt. Es gelten folgende Übersetzungsmöglichkeiten für relationale Operationen: 52 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 8.3 „Tuning“ Abbildung 9: Mögliche Umsetzungen einiger relationaler Operatoren 8.3 „Tuning“ Optimieren von Tabellen/Indexen/Schlüsseln anhand aufgezeichneter Zugriffe: • Statistiken (Histogramme, etc.) müssen explizit angelegt werden • Anderenfalls liefern die Kostenmodelle falsche Werte • In Oracle . . . – analyze table Professoren compute statistics for table; – Man kann sich auch auf approximative Statistiken verlassen ∗ Anstatt compute verwendet man estimate • In DB2 . . . – runstats on table ... c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 53 9 Transaktionsverwaltung 9.1 Transaktionsverwaltung mit Fehlerbehandlung In den klassischen Transaktionssystemen: begin of transaction (BOT). Mit diesem Befehl wird der Beginn einer eine Transaktion darstellende Befehlsfolge gekennzeichnet. commit: Erfolgreicher Abschluss. Hierdurch wird die Beendigung der Transaktion eingeleitet. Alle Änderungen der Datenbasis werden durch diesen Befehl festgeschrieben, d.h. sie werden dauerhaft in die Datenbank eingebaut. rollback: Erfolgloser Abschluss. Dieser Befehl führt zu einem Selbstabbruch der Transaktion. Das Datenbanksystem muss sicherstellen, dass die Datenbasis wieder in den Zustand zurückgesetzt wird, der vor Beginn der Transaktionsausführung existiert. 9.1.1 Wichtige Eigenschaften von Transaktionen [wichtig] Transaktionen in Datenbanken sind charakterisiert durch die sogenannten ACID- Eigenschaften: Akronym: A — C — I — D Atomicity (Atomarität) Transaktion als kleinste, nicht mehr weiter zerlegbare Einheit; d.h. entweder werden alle Änderungen der Transaktion festgeschrieben oder gar keine. Consistency Konsistenter Zustand der DB nach Beendigung → ansonsten Zurücksetzung Isolation Jede Transaktion hat die DB „für sich allein“; parallele Transaktionen bzw. Zugriffe sind nicht möglich bzw. dürfen sich nicht gegenseitig beeinflussen Durability (Dauerhaftigkeit) Änderungen erfolgreicher Transaktionen dürfen nie verloren gehen, d.h. diese müssen persistent geschrieben werden. Dabei werden mit commit alle Änderungen festgelegt; mit rollback indes zurückgesetzt. Beispiel 45: Beispieltransaktion auf Basis des Universitätsschemas: 1 2 3 4 5 6 BEGIN TRANSACTION; -- Transaktion beginnen insert into Professoren values (2141, ‘Meitner‘, ‘C4‘, 205); insert into Vorlesungen values (5275, ‘Kernphysik‘, 3, 2141); COMMIT; -- oder ROLLBACK 54 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 9.1 Transaktionsverwaltung mit Fehlerbehandlung 9.1.2 Komponenten Transaktionsverwaltung Die Transaktionsverwaltung besteht aus zwei grossen Komponenten: • Mehrbenutzersynchronisation (→ siehe Kapitel 11) • Recovery: Gewährleistung der Atonalität und Dauerhaftigkeit (→ siehe Kapitel 10.1.1) Abbildung 10: Transaktionsverwaltung Für eine allfällige Erklärung der Zustandsübergänge: siehe Lehrbuch Kapitel 9.7, S. 291f. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 55 10 Fehlerbehandlung 10.1 Die (zweistufige) Speicherhierarchie Ein DB-Verwaltungssystem bearbeitet Daten innerhalb des sogenannten Datenbankpuffers (siehe Box im Kapitel 7.1). Dieser ist Teil des Hauptspeichers und ist in Seitenrahmen segmentiert, welche genau eine Seite fassen können. Das heisst, alle Tupel (Datensätze) – im Beispiel A, B, C, D – müssen auf persistent speicherbare Seiten abgebildet werden – im Beispiel PA , PB und PC : A! " D PA C! A! ! PC D C PB B Abbildung 11: Speicherhierarchie Eine Transaktion benötigt im Allgemeinen mehrere Datenseiten, die sich entweder schon (zufällig) im Puffer befinden oder aber noch eingelagert werden müssen. Für die Dauer eines Zugriffs bzw. eines Updates wird die jeweilige Seite im Puffer fixiert („FIX“), damit sie nicht von anderen Operationen aus dem Puffer verdrängt werden kann. Danach wird sie wieder freigegeben und kann also ersetzt werden. 10.1.1 Ersetzung von Seiten und Änderungen von Transaktionen Dabei gelten zwei Strategien in Bezug auf aktive (noch nicht festgeschriebene) Transaktionen: • steal: die nicht fixierte Seite ist prinzipiell ein Kandidat für die Ersetzung, falls neue Seiten eingelagert werden müssen. • ¬ steal: Bei dieser Strategie wird die Ersetzung von Seiten, die von einer noch aktiven Transaktion modifiziert wurden, ausgeschlossen. Die von einer abgeschlossenen Transaktion verursachten Änderungen – d.h. alle von ihr modifizierten Seiten – können dann auf zwei weitere Arten in die Datenbasis übertragen werden: • force: Änderungen werden zum Transaktionsende auf den Hintergrundspeicher geschrieben • ¬ force: geänderte Seiten können im Puffer verbleiben Daraus ergibt sich folgende Übersicht in Bezug auf ein Recovery der Daten (z.B. bei fehlerhafter Transaktion): 56 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 10.2 ¬ force force • kein Redo • Undo steal ¬ steal Protokollierung von Änderungsoperationen • Redo • Undo • kein Redo • kein Undo • Redo • kein Undo Tabelle 1: Recovery der Daten Redo: alle noch nicht in die Datenbasis eingebrachten Änderungen können nachvollzogen werden nach der Transaktion Undo: alle durch nicht abgeschlossene Transaktion noch nicht in die Datenbasis eingebrachten Änderungen können rückgängig gemacht werden 10.2 Protokollierung von Änderungsoperationen Die materialisierte Datenbasis enthält meist nicht einen konsistenten Zustand (siehe vorheriger Abschnitt: Daten können z.B. vom Puffer noch nicht in die Datenbasis propagiert worden sein, . . . ). Deshalb wird eine Log-Datei angelegt, um Zusatzinformationen für mögliche Änderungsoperationen anzulegen. Die Log-Einträge weisen folgende Struktur auf: [LSN, TransaktionsID, PageID, Redo, Undo, PrevLSN] • LSN (Log Sequence Number) eindeutige Kennung des Log-Eintrags • TransaktionsID Transaktion, welche die Änderung durchgeführt hat • PageID Kennung der Seite, auf der die Änderungsoperationen vollzogen wurden • Redo gibt an, wie die Änderung nachvollzogen werden kann • Undo beschreibt, wie die Änderung rückgängig gemacht werden kann • PrevLSN Zeiger auf den vorhergehenden Log-Eintrag der jeweiligen Transaktion. Der Log-Eintrag wird geschrieben, bevor eine Änderungsoperation angelegt wird. Die Einträge werden im Log-Puffer (Hauptspeicher) zwischengelagert. Der Log-Puffer ist meist als Ringpuffer ausgelegt (gleichmässige Auslastung, da an einem Ende dauernd geschrieben, am anderen dauernd ausgelesen werden kann), siehe Bild rechts. Spätestens sobald er voll ist, muss er auf den Hintergrundspeicher geschrieben werden. AP1 % & ... ( APn ' " ! " ! $ • ) # • • • • #40 • & ! #41 # $ #20 ' ( " • • #10 • • • % • c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 57 10.3 Wiederanlauf nach einem Fehler 10.2.1 Das WAL-Prinzip („Write Ahead Log“-Prinzip) Das Prinzip kommt beim Schreiben der Log-Einträge in den Speicher zur Anwendung: • Bevor eine Transaktion festgeschrieben (committed) wird, müssen alle „zu ihr gehörenden“ LogEinträge ausgeschrieben werden. • Bevor eine modifizierte Seite ausgelagert werden darf, müssen alle Log-Einträge, die zu dieser Seite gehören, in den temporären Speicher und das Log-Archiv ausgeschrieben werden. 10.3 Wiederanlauf nach einem Fehler Nach einem Fehler mit Verlust des Hauptspeicherinhalts muss der Fehler nach dem folgendem Recoverykonzept gelöst werden (drei Phasen): 1. Analyse: • Die temporäre Log-Datei wird von Anfang bis zum Ende analysiert, • Ermittlung der Winner-Transaktionen • Ermittlung der Loser-Transaktionen 2. Wiederholung der Historie: • alle protokollierten Änderungen werden in der Reihenfolge ihrer Ausführung in die Datenbasis eingebracht. 3. Undo der Loser: • Die Änderungsoperationen der Loser-Transaktionen werden in umgekehrter Reihenfolge ihrer ursprünglichen Ausführung rückgängig gemacht. Winner-Transaktionen: alle COMMIT-Einträge im Log Loser-Transaktionen: alle Einträge im Log, zu denen KEIN COMMIT GEFUNDEN werden kann 58 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 11 Transaktionsverwaltung mit Mehrbenutzersynchronisation Unter Mehrbenutzerbetrieb versteht man die gleichzeitige (nebenläufige, parallele) Ausführung mehrerer Programme (bzw. in unserem Kontext Transaktionen). 11.1 Fehler bei unkontrolliertem Mehrbenutzerbetrieb [S. 316] Verlorengegangene Änderungen (lost update). Änderungen eines Datensatzes, die während einer bereits ablaufenden Transaktion parallel gestartet werden, werden überschrieben durch die zeitlich früher gestartete Transaktion und sind dann verloren. Phantomproblem. Zwei identische Statements – welche nicht über eine Transaktion geschützt sind – liefern nicht dasselbe Ergebnis (weil man ja nicht wissen kann, was dazwischen passiert ist): Abbildung 12: Zwei Tabellen in einer Transaktion, die dem Phantomproblem unterliegt Es tritt auf, wenn während der Abarbeitung der Transaktion T2 eine andere Transaktion T1 (zeitlich jüngere Transaktion) diese beeinflusst. Als Folge davon ist das Ergebnis – trotz gleicher ausgeführter Aktion in T2 – ein unterschiedliches. 11.2 Serialisierbarkeit Merke: Die serialisierbare Ausführung entspricht einer Menge von Transaktionen (TA) einer kontrollierten, nebenläufigen, verzahnten Ausführung. Eine Kontrollkomponente sorgt dafür, dass dabei keine Fehler entstehen. Bei einer serialisierbaren Ausführung mehrerer Transaktionen muss die zeitlich jüngere TA warten, bis die ältere TA komplett beendet ist. Somit können sich Transaktionen nicht gegenseitig beeinflussen. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 59 11.2 Serialisierbarkeit Als serialisierbar bezeichnet man in Transaktionssystemen also eine Historie (= zeitliche Anordnung der einzelnen verzahnt ausgeführten Elementaroperationen einer Menge nebenläufiger Transaktionen), die nacheinander ausgeführt zum selben Ergebnis führt wie eine serielle Historie über dieselben Transaktionen (https://de.wikipedia.org/wiki/Serialisierbarkeit) – oder frei nach OlnSerialisierbarkeit hoff (11.04.2013): „zwei parallele Transaktionen, die sich gegenseitig nicht ’wehtun’“. dabei eine exklusive Sperrung: Historie Es istgilt „äquivalent“ zu einer seriellen HistorieZwei verzahnte Überweisungs-Transaktionen dennoch parallele (verzahnte) Ausführung möglich Serialisierbare Historie von T1 und T2 Schritt 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. T1 BOT read(A) T2 BOT read(C) write(A) write(C) read(B) write(B) commit Schritt T1 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. BOT read(A,a1) a1 := a1 – 50 write(A,a1) T3 BOT read(A,a2) a2 := a2 – 100 write(A,a2) read(B,b2) b2 := b2 + 100 write(B,b2) 12. read(A) write(A) commit © A. Kemper / A. Eickler commit 13. read(B,b1) 14. bD1 := b1 + 50 15. 16. D M G Mwrite(B,b ) 1 G commit/rollback 6 © A. Kemper / A. Eickler 9 Man beachte die rechtsstehende Tabelle dabei: bei einem „commit“ (Schritt 16) würde T1 dort bestätigt und damit auch T3 als „gültige Transaktion“ definiert – man hätte nichts gemerkt. Bei einem „rollback“ wäre dies aber nicht der Fall und T1 und T3 befänden sich in nicht gleichen Zuständen (T1 wird zurückgesetzt, T3 ist bereits abgeschlossen und hat das Datenobjekt A aus T1 beansprucht!); d.h. das Argument der Serialisierbarkeit würde dann verletzt. Damit ist das rechte Beispiel nicht serialisierbar. Abbildung 13: Serialisierbarkeit T1 Datenbank-Scheduler " Ein Datenbank-Scheduler gewährleistet – vereinfacht gesagt – die „vernünftige“ Ausführung verschiedener Einzeloperationen der Transaktionen unter Einhaltung der Kriterien der Serialisierbarkeit. T2 " T3 " Tn " " " ! " 60 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 11.3 Sperrbasierte Synchronisation 11.3 Sperrbasierte Synchronisation Bei der sperrbasierten Synchronisation wird während des laufenden Betriebs sichergestellt, dass die resultierende Historie serialisierbar bleibt. Welche Operationen lassen sich gegenseitig zu? Es gelten dabei folgende Arten von Sperren (bzw. Modi): S shared, read lock, Lesesperre Eine Transaktion kann ein Datenobjekt lesen. Wenn Ti eine S-Sperre für einen Zugriffszeitpunkt A besitzt, kann Ti read(A) ausführen. Mehrere Transaktionen können gleichzeitig eine S-Sperre auf demselben Objekt A besitzen. X exclusive, write lock, Schreibsperre Eine Transaktion kann ein Datenobjekt verändern. Ein write(A) darf aber nur eine Transaktion ausführen, die eine X-Sperre auf A hat. Man unterscheidet weiter bezüglich der Dauer einer Sperre: • Eine kurze Sperre wird von einer Transaktion auf einem Datenobjekt nur während des Zugriffs gehalten und danach gleich wieder freigegeben. • Eine lange Sperre wird im Verlauf einer Transaktion angefordert und dann bis zum Ende der Transaktion gehalten. Die Freigabe erfolgt erst beim Bestätigen oder Verwerfen der Transaktion. 11.3.1 Verhalten der Transaktionen Eine Prädikatsperre (P-Sperre) betrifft alle von der Änderung betroffenen Datensätze bei einer einer Modifikation (insert, update oder delete). Isolationslevel Sperre(n) Verhalten READ COMMITTED Lesen: kurze Lesesperren Schreiben: lange exklusive P-Sperren kann nur Daten lesen, die andere Transaktionen bestätigt haben. Mehrmaliges Lesen möglich, kann zu Problemen führen. SERIALIZABLE Lesen: lange (nicht-)exklusive P-Sperren Schreiben: lange exklusive P-Sperren Schutz der gesamten TA durch lange Sperren Die Verträglichkeit von Sperranforderungen mit bereits existierenden Sperren (auf demselben Objekt durch andere Transaktionen) kann man in einer Verträglichkeitsmatrix bzw. Kompatibilitätsmatrix zusammenfassen (NL = No Lock; d.h. keine Sperre): SERIALIZABLE: READ COMMITTED: NL S X S ja ja nein X ja nein nein NL S X S ja ja ja X ja ja nein c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 61 11.4 2-Phase-Commit Transaktionsverarbeitung 11.4 2-Phase-Commit Transaktionsverarbeitung Die EOT (End-of-Transaction)-Behandlung von globalen Transaktionen stellt in verteilten DBMSUmgebungen (VDBMS) ein Problem dar (da die Stationen eines VDBMS unabhängig voneinander „abstürzen“ können bzw. die Konsistenz der Tabellen nicht mehr gewährleistet ist). Problemlösung: Zweiphasen-Commit-Protokoll oder auch 2PhaseCommit (2PC). ! A1 " A2 K # A3 $ A4 ! A1 # & %K " " A2 # A3 # & %K " $ A4 Abbildung 14: Nachrichtenaustausch beim 2PC-Protokoll (Beispiel mit 4 Agenten) Zur Grafik: Der Commit-Koordinator (K) schickt ein Prepare-Statement (PREPARE) an alle Agenten (A) und wartet auf deren Feedback (FAILED / READY). Falls bei einem Agenten die Bestätigung ausbleibt wird das COMMIT abgebrochen (COMMIT/ABORT), ansonsten bestätigt (ACK). Das 2PC-Verfahren. . . • wird vom sogenannten Koordinator K überwacht • gewährleistet, dass die n Agenten (= Stationen im VDBMS) A1 , . . . , An , die an einer Transaktion beteiligt waren, entweder alle von Transaktion T geänderten Daten festschreiben oder alle Änderungen von T rückgängig machen 11.5 Abstürze 11.5.1 Absturz eines Koordinators → Hauptproblem des 2PC-Verfahrens • Absturz vor dem Senden einer COMMIT-Nachricht → Rückgängigmachung der Transaktion durch Versenden einer ABORT-Nachricht. • Absturz nachdem Agenten ein READY mitgeteilt haben → Blockierung der Agenten. 11.5.2 Absturz eines Agenten Antwortet ein Agent innerhalb eines Timeout-Intervalls nicht auf die PREPARE-Nachricht, gilt der Agent als abgestürzt; der Koordinator bricht die Transaktion ab und schickt eine ABORT-Nachricht an alle Agenten. 62 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 12 Sicherheitsaspekte 12.1 Zugriffskontrolle in SQL Der SQL-92 Standard stellt keine Normen für Authentisierung und Auditing (verifiziert die Richtigkeit und Vollständigkeit der Autorisierungsregeln und kann Schäden rechtzeitig erkennen) auf. Es existiert nur ein Befehl zur Vergabe von Rechten (GRANT) und einer zum Entzug derer (REVOKE). 12.1.1 Identifikation und Authentisierung Beispiel 46: neuen User erfassen mittels CREATE USER 1 2 CREATE USER eickler -- weitere Befehle absetzen fuer Passwort, etc. 12.1.2 Autorisierung und Zugriffskontrolle Beispiel 47: Zugriff ermöglichen (SELECT / UPDATE zulassen) mittels GRANT 1 2 3 -- SELECT auf Tabelle ’Professoren’ zulassen fuer User ’eickler’ GRANT SELECT on Professoren to eickler; 4 5 6 7 8 -- UPDATE auf Tabelle ’pruefen’ zulassen fuer User ’eickler’ GRANT UPDATE (MatrNr, VorlNr, PersNr) on pruefen to eickler; Beispiel 48: Entzug von Rechten (des UPDATE-Statements) mittels REVOKE 1 2 3 REVOKE UPDATE (MatrNr, VorlNr, PersNr) on pruefen FROM eickler CASCADE; -- durch ’CASCADE’ werden kaskadierend alle Rechte zurueckgenommen 12.1.3 Zugriffskontrolle durch Sichten (VIEW) Es besteht die Möglichkeit, Rechte von einer bestimmten Bedingung oder Aggregation von Daten abhängig zu machen. Dies wird mittels Sichten realisiert, die danach einem bestimmten User bzw. einer bestimmten Usergruppe zugewiesen werden. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 63 12.2 Schema Best präktiss bei Zugriffsbeschränkungen Immer zuerst VIEW definieren und dann GRANT für die entsprechenden User bzw. Usergruppe (siehe nächstes Beispiel). Beispiel 49: Änderungsbeschränkung mit GRANT auf bestimmten Daten (VIEW) 1 2 3 4 CREATE VIEW ErstSemestler AS SELECT * FROM Studenten WHERE Semester = 1 WITH CHECK OPTION; -- WITH CHECK OPTION verhindert, dass hier mit INSERT oder UPDATE ein anderer Wert als 1 bei Semester eingetragen werden koennte (siehe GRANT nachfolgend) 5 6 7 8 GRANT select, update, insert ON ErstSemestler TO tutor; 12.2 Schema Ein Schema ist eine Unterstruktur der DB (mySql: DB = Schema). Tabellen in unterschiedlichen Schemata können den gleichen Namen haben. 12.3 Kryptographie Die meisten Datenbankanwendungen werden in einer verteilten Umgebung betrieben – sei es als Client / Server-System oder als „echte“ verteilte Datenbank. In beiden Fällen ist die Gefahr des unlegitimierten Abhörens sowohl innerhalb eines LAN als auch im WAN gegeben und kann technisch fast nicht ausgeschlossen werden. Merke: Die Verschlüsselung einer DB ist Sache des Administrators. Das DBMS stellt dies nicht standardmässig zur Verfügung. Dabei sollten folgende Ebenen des Datenschutzes berücksichtigt werden: siehe Grafik rechts. 64 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 13 Objektorientierte Datenbanken / Übung mit Java-Persistence-API (Objektorientierte Sicht auf relationale DB) ER-Modellierung rel. DB-Schema OO-Schema XML-Schema unstrukturierte Daten SQL: -DDL -DML JDBC Phys. Strukturen (Bäume) Transaktionen Verteilung ODL OQL XSchema/DTD XQuery Skriptsprache in BigData „Analysen: hochverteiltelte Auswertungen“ 13.1 Klassenbeschreibungen einer relationalen Datenbank Einige Objekte aus der Universitätswelt id1 class Professoren { attribute long PersNr; attribute string Name; attribute string Rang; }; id2 VorlNr: Titel: SWS: gelesenVon: Hörer: Nachfolger: Vorgänger: PersNr: Name: Rang: resisiertIn: hatGeprüft liest: Profesoren 2137 „Kant“ „C4“ id9 {...} {id2, id3} id3 VorlNr: Titel: Vorlesungen 5001 „Grundzüge “ 4 SWS: gelesenVon: Hörer: Nachfolger: Vorgänger: id1 {...} {...} {...} D M G © A. Kemper / A. Eickler Vorlesungen 4630 „Die 3 Kriterien“ 4 id1 {...} {...} {...} 7 Abbildung 15: Achtung Redundanzen (siehe Abhängigkeiten zwischen Professoren und Vorlesungen bzw. Professoren und Vorlesungen!) c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 65 13.1 Klassenbeschreibungen einer relationalen Datenbank 13.1.1 Definition von Objekttypen Beispiel 50: Modellierung mit „inverse“ 1 2 3 4 5 6 class Professoren { attribute long PersNr; ... // relationship [Tabelle] [Name] INVERSE [Verweis] relationship Raeume residiertIn INVERSE Raeume::beherbergt; }; 7 8 9 10 11 12 13 14 // Raeume mit Primaerschluessel ’RaumNr’: class Raeume (Key RaumNr) { attribute long RaumNr; attribute short Groesse; ... relationship Professoren beherbergt INVERSE Professoren::residiertIn }; inverse-Abhängigkeit Die beiden Beziehungen sind zueinander invers, d.h. sie drücken die gleichen Informationen aus (gleiche Infos in unterschiedlichen Feldern gespeichert). Falls die eine Relationship durch den Programmierer aktualisiert wird, so wird im referenzierten Objekt die Änderungen automatisch auch aktualisiert. Professoren residiertIn inverse Raum beherbergt Beispiel 51: Typeigenschaften: Extensionen EXTENT und Schlüssel key Die Extension (extent) ist die Menge aller Instanzen eines Objekttyps. Man kann dazu auch Schlüssel definieren, deren Eindeutigkeit innerhalb der Extension gewährleistet wird. Sie sind nicht brauchbar zur Referenzierung von Objekten, sondern gewährleisten die Integritätsbedingung. 1 2 3 4 5 6 7 class Studenten (EXTENT AlleStudenten KEY MatrNr) { attribute long MatrNr; attribute string Name; attribute short Semester; relationship set(Vorlesungen) hoert INVERSE Vorlesungen::Hoerer; relationship set(Pruefungen) wurdeGeprueft INVERSE Pruefungen::Pruefling; }; 66 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 13.2 Abfragen, Statements und Transaktionen in OQL Wichtig: Erst das Keyword extent ermöglicht ein Iterieren über eine Kollektion von Datensätzen! 13.2 Abfragen, Statements und Transaktionen in OQL Pfadausdrücke in OQL-Anfragen Beispiel 52: Abfrage in OQL über eine Collection select s.Name from s in AlleStudenten, v in s.hört where v.gelesenVon.Name = „Sokrates“; Visualisierung des Pfadausdruckes Studenten hört Vorlesungen gelesenVon Professoren Nam e ein längerer Pfadausdruck eineVorlesung.gelesenVon.residiertIn.Größe Achtung Collections! Vorlesungen Der direkte Zugang (z.B. bekannt aus Java) ist nicht möglich, sofern eine Kollektion angesproProfessoren chen werden muss. Es muss ein „Umweg“ über „Zwischenvariablen“ gegangen werden, z.B.: D Räume M v und s beschreiben nun direkt einzelne Elemente aus den Collections ’Vorlesungen’ bzw. ’Studenfloat G © A. Kemper / A. Eickler 29 ten’. Beispiel 53: Erzeugung von Objekten 1 2 3 4 5 // formale Argumente in Vorlesungen(...): VorlNr, Titel, SWS, gelesenVon Vorlesungen(5555, "Ethik II", 4, ( select p from p in AlleProfessoren where p.Name = "Sokrates")); -- referenziert Sokrates-Objekt Beispiel 54: Objekterzeugung und Ballung von Objekten (= Angabe zur Platzierung im Speicher [C++]) 1 2 //UniDB entspricht einem Connection-Objekt (DB-Verbidnung) Professoren Russel = new(UniDD) Professoren(2126, "Russel", "C4", ...); 3 4 5 // Popper-Objekt kommt auch in die DB von Russel - zudem wird es nahe bei Russel gespeichert (gleicher Block) Professoren Popper = new(Russel) Professoren(2133, "Popper", "C3", ...); Beispiel 55: Verschachtelte Transaktionen 1 2 3 4 5 6 7 public void Umziehen(Raum neuerRaum) { Transaction TAumziehen; // Transaktion (TA) definieren TAumziehen.start(); // TA starten ... if ( /*Fehler? */ ) TAumziehen.rollback(); // TA ruecksetzen ... c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 67 13.3 8 9 Beispiel TAumziehen.commit(); }; // TA bestaetigen / abschliessen Merke: Bei geschachtelten Transaktionen werden in SQL sowie OQL nach einem „rollback“ alle inneren Transaktionen zurückgesetzt. Dies beinhaltet auch jene, welche bereits ein „commit“ erhalten haben! 13.3 Beispiel Beispiel 56: Objektorientierte Datenbank Welche Person kann das Oberteil des Teiles mit der Nummer 5 produzieren? Diagramm: ID bestehtAus mc Teil AnzahlUnterteile mc mc gefertigtVon ID c Maschine ID mc m Person bedienbarVon Klassenbeschreibungen: 1 2 3 4 class Person(extent allePersonen) { attribute string Name; relationship set(Maschine) bedient inverse Maschine::bedienbarVon; } 5 6 7 8 9 10 class Maschine(extent alleMaschinen key Bez) { attribute string Bez; relationship set(Person) bedienbarVon inverse Person::bedient; relationship set(Teil) fertigt inverse Teil::gefertigtVon; } 68 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 13.3 Beispiel 11 12 13 14 15 16 class Stueckliste { Attribute int Anzahl; relationship Teil Oberteil inverse Teil::Oberteilstuecklisten; relationship Teil Unterteil inverse Teil::Unterteilstuecklisten; } 17 18 19 20 21 22 23 class Teil(extent alleT, key TeilNr) { Attribute int TeilNr; relationship Maschine gefertigtVon inverse Maschine::fertigt; relationship set(Stueckliste) Oberteilstueckliste inverse Stueckliste:: Oberteil; relationship set(Stueckliste) Unterteilstueckliste inverse Stueckliste:: Unterteil; } 24 25 26 27 28 29 30 // 1. Abfragemoeglichkeit (ueber Teile): select p from t in alleT, s in t.Oberteilstueckliste, // liefert Set --> s p in s.Oberteil.gefertigtVon.bedienbarVon // liefert Set --> p where t.TeilNr = 5; 31 32 33 34 35 36 37 38 // 2. Abfragemoeglichkeit (ueber select from p in allePersonen, m in p.bedient, // liefert Set t in m.fertigt, // liefert Set s in t.Unterteilstueckliste // where s.Unterteil.TeilNr = 5; Personen): --> m --> t liefert Set --> s c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 69 14 Java Persistence API Anwendung Persistence API Entity Manager Service Provider Interface Eclipse Link Oracle Hibernate ... Abbildung 16: Schichten Modell der Java Persistence API 14.1 Beschreibungsmittel und Klassen Annotations: Mapping der Klassen zu DB-Tabellen EntityManager: Zugriff auf persistente Objekte (Suchen, Speichern, Transaktionen, . . . ) persistence.xml: Treiber, DB-Connection, . . . 14.1.1 Annotations-1 • • • • • @Entity: Klasse ist persistent @Table(name=. . . ): Klasse wird auf Tabelle abgebildet @Column(name = . . . ) : Klassen- zu Tabellenattribut @Id: Primärschlüsselattribut @GeneratedValue(strategy=. . . ): automatische Generierung eines Attributwertes (Id) Beispiel 57: Entity-Klasse JVorlesungen.java import javax.persistence.*; @Entity @Table(name = "vorlesungen") public class JVorlesungen implements Serializable { @Id @GeneratedValue(strategy =GenerationType.IDENTITY) @Basic(optional = false) // null not allowed @Column(name = "vorlnr") private Integer jVorlnr; 1 2 3 4 5 6 7 70 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 14.1 8 9 Beschreibungsmittel und Klassen // Db-Feld vorlnr wird zu java-Feld jVorlnr ...} 14.1.2 Annotations-2 M-zu-1 Beziehung zwischen JVorlesungen und JProfessoren in JVorlesungen eine Referenz auf Professoren: • @JoinColumn(name = “gelesenVon“, referencedColumnName = “PersNr“) • @ManyToOne • private JProfessoren jGelesenVon; Umkehrung in der Klasse JProfessoren: • @OneToMany(mappedBy = “jGelesenVon“) • private Collection<JVorlesungen> jVorlesungen; 14.1.3 Annotations-3 mc-zu-mc Beziehung zwischen JStudenten und JVorlesungen In JStudenten: • @JoinTable(name = “hoeren“, joinColumns = {@JoinColumn(name = “matrnr“, referencedColumnName = “persnr“)}, inverseJoinColumns = {@JoinColumn(name = “vorlnr“, referencedColumnName = “vorlnr“)}) • @ManyToMany • private Collection<JVorlesungen> jVorlesungen; In JVorlesungen: • @ManyToMany(mappedBy = "jVorlesungen") • private Collection<JStudenten> jHoerer; 14.1.4 Annotations-4 Vererbungshierarchien in einer Tabelle (single table, joined tables als Alternative) c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 71 14.1 Beschreibungsmittel und Klassen In JPersonen: @Entity @Table(name = “Personen“) @Inheritance(strategy = InheritanceType.SINGLE_TABLE) @DiscriminatorColumn( name = “DTYPE“, discriminatorType = DiscriminatorType.STRING) In Jassistenten extends JPersonen: @Entity @DiscriminatorValue(“assi“) Ein Eintrag in der Personen-Tabelle mit „assi“ in dtype ist ein Objekt der Klasse JAssistenten 14.1.5 Annotation-5 Zusätzliche Argumente für Beziehungen • in @One. . . ,@Many. . . CascadeType: Änderungen am referenzierten Objekt FetchType: Laden des referenzierten Objekte e.g. in der Klasse JProfessoren für die Collection jVorlesungen @OneToMany(mappedBy = “jGelesenVon“ , cascade=CascadeType.PERSIST, fetch=FetchType.EAGER) – liest der Prof p eine neue Vorlesung v (mit new erzeugt und zu p.jVorlesungen hinzugefügt), so wird bei Speicherung von p vorgängig v (automatisch) gespeichert – ein Prof-Objekt wird immer zusammen mit all „seinen“ Vorlesungen geladen. 1 2 3 4 5 6 7 1 2 14.1.6 Persistence.xml <persistence-unit name="appeUniPU" transaction-type="RESOURCE_LOCAL"> <provider>org.eclipse...</provider> ... Entity-classes .... <properties> Connection-properties, Log-property (zum Testen) </properties> </persistence-unit> 14.1.7 EntityManager EntityManagerFactory emf = Persistence.createEntityManagerFactory(" appeUniPU"); EntityManager em = emf.createEntityManager(); 3 72 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 14.1 Beschreibungsmittel und Klassen em.createQuery("select p from JProfessoren p"); // gibt JProfessorenCollection zurueck (Generics) // Query-Ausdruck ist (fast) OQL, immer Bezug auf Java-Namen 4 5 6 em.persist(entityObject): speichert ein neues Objekt (+referenzierte Objekte) em.merge(..) // aktualisiert ein Objekt in der DB 7 8 9 10 11 EntityTransaction ta = em.getTransaction(); // ta.begin(), ta.commit(), ta.rollback() Merke: • Veränderungen nur innerhalb von Transaktionen • Veränderungen werden (meist) erst mit dem Ende der Transaktion in die DB geschrieben erst dann ist eine IDENTITY-ID (auto_increment) verfuegbar - em.refresh(object)!! • Wenn die referenzierten Objekte „automatisch“ gespeichert werden sollen: cascade-Parameter in One. . . , Many. . . angeben! Parameter wird (bisher) nicht generiert!! • Laufzeitobjekte werden nicht automatisch aktualisiert, use em.refresh(object), sehr beachtenswert Tooling • Man kann die Entity-Klassen generieren lassen: siehe dazu JpaReadme.pdf • Alternative (für einen Datenbänkler verpönt!!) Entityklassen mit JPA-Annotationen programmieren in persistence.xml: Table Generation Strategy = Create beim Start des Programmes werden die Tabellen erzeugt („magic“ create table . . . ) c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 73 15 Verteilte Datenbanken9 Verteilte Datenbanksysteme stellen integrierte, geographisch verteilte Mehrrechner-Datenbanksysteme (wie in Abbildung 17) dar. Jede Station beinhaltet eine vollständige Instanz eines DBMS und kooperieren durch Nachrichtenaustausch über ein Kommunikationsnetz (LAN oder WAN) miteinander. S2 • S1 • • S3 Abbildung 17: Verteilte Datenbanken an verschiedenen Stationen Eine Herausforderung bei verteilten Datenbanken stellen Dateninkonsistenzen dar. Um dies zu umgehen, dazu dient das Sperren von Replikationen. Dabei kommt das Phase2-Commit Protokoll zum Einsatz. 15.1 Fragmentierung Fragmentierung Fragmente enthalten Daten mit gleichem Zugriffsverhalten – sie sind aber verteilt abgelegt. Dabei können sie entweder mit oder ohne Replikation (= ohne Redundanz) vorkommen. Es gelten dabei folgende Fragmentierungsmöglichkeiten: • horizontale Fragmentierung: Zerlegung der Relation in disjunkte Tupelmengen • vertikale Fragmentierung: Zusammenfassung von Attributen mit gleichem Zugriffsmuster (z.B. verschiedene Spalten auf versch. Servern lagernd) • kombinierte Fragmentierung: Anwendung horizontaler und vertikaler Fragmentierung auf dieselbe Relation Eine Anwendung greift auf verteilt gespeicherte Daten zu – und muss dabei aber eigentlich nur das globale Schema kennen. Darunter liegend werden die Daten fragmentiert und in verteilten Datenbanken abgelegt (Zuordnungsschema). Der User bzw. die Anwendung muss sich allerdings nicht darum bemühen 9 Dieses Kapitel verwendet zur Ergänzung Inhalte der Website http://dbs.uni-leipzig.de/buecher/mrdbs/mrdbs-3.html, davon insbesondere Zusammenfassungen aus den Kapiteln 4 & 5. 74 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 15.1 Fragmentierung zu wissen, wo die Daten liegen; der Zugriff erfolgt nach wie vor über das globale Schema. Um die korrekte Angabe des Zugriffsortes (lokales Schema) kümmert sich das VDBMS (siehe Abbildung 18). ... ... ... S1 ... Sn Abbildung 18: Fragmentierte Daten innerhalb eines globalen Schemas Der gesamte (d.h. unfragmentierte!) Datensatz muss immer rekonstruierbar sein. Dafür ist das VDBMS verantwortlich. 15.1.1 Horizontale Fragmentierung Die horizontale Fragmentierung ist die in existierenden Systemen bedeutendste Fragmentierungsform. Bei ihr wird eine globale Relation zeilenweise in disjunkte Teilmengen zerlegt. Die Zuordnung von Sätzen zu Fragmenten wird dabei i.a. über Selektionsprädikate (im Beispiel: Filiale) definiert. Abbildung 19: Einfache (primäre) horizontale Fragmentierung: die Selektionsprädikate beziehen sich ausschliesslich auf Attribute (im Beispiel: Filiale) der zu zerlegenden Relation. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 75 15.1 Fragmentierung Die nachstehende Abbildung 20 zeigt die Fragmente R1 bis R3 einer Relation R mit zwei ZerlegungspräHorizontale Fragmentierung dikaten p1 und p2 : abstrakte Darstellung: R R1 R2 R3 Für 2 Prädikate p1 und p2 ergeben sich 4 Zerlegungen: R1 := p1 p2(R) R2 := p1 p2(R) R3 := p1 p2 (R) R4 := p1 p2 (R) n Zerlegungsprädikate p1,...,pn ergeben 2n Fragmente © A. Kemper / A. Eickler D M G 11 Abbildung 20: Horizontal fragmentierte Daten (schematisch) Eine entsprechende horizontale Fragmentierung wird auch innerhalb eines zentralen Systems unterstützt. Die Fragmente werden dann Partitionen genannt. 15.1.2 Vertikale Fragmentierung Die vertikale Fragmentierung zerlegt eine Relation spaltenweise durch Definition von Projektionen auf den Attributen der Relation. Die Forderungen nach Vollständigkeit und Rekonstruierbarkeit verlangen, das jedes Attribut in wenigstens einem Fragment enthalten ist. Abbildung 21: Die Kundenrelation einer Bankanwendung wurde Anwendung von Projektionen in zwei vertikale Fragmente KUNDE1 und KUNDE2 zerlegt. Der Primärschlüssel KNR ist in beiden Fragmenten enthalten, um die Gesamt-Relation durch JOIN-Bildung wieder rekonstruieren zu können. 76 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 15.2 Allokation Die Rekonstruktion der globalen Relation erfordert den natürlichen Verbund JOIN zwischen den einzelnen Fragmenten. Um diese JOIN-Berechnung verlustfrei vornehmen zu können, existieren verschiedene Ansätze, zum Beispiel: Vertikale Fragmentierung • jedes Fragment enthält den Primärschlüssel der Originalrelation • jedem Tupel der Originalrelation wird ein eindeutiges Surrogat (= künstlich erzeugter Objektindikator) zugeordnet, welches in jedes vertikale Fragment des Tupels mit aufgenommen wird abstrakte Darstellung: R R2 R1 © A. Kemper / A. Eickler Abbildung 22: Vertikal fragmentierte Daten (schematisch) Verteilte Datenbanken 15.2 Allokation D M G 14 Fragmentierung Beispielanwendung(2) Allokation Fragmente werden den Stationen zugeordnet. Eine Anwendung muss die Speicherorte fragmentierter Daten nicht kennen; stattdessen werden diese durch die Allokation wieder als „Einheit“ zur Verfügung gestellt. • Bei der Allokation werden nun die Fragmente Stationen zugeteilt (hier ohne Replikation) Station SVerw SPhysik SPhilo STheol Bemerkung Verwaltungsrechner Dekanat Physik Dekanat Philosophie Dekanat Theologie zugeordnete Fragmente {ProfVerw } {PhysikVorls, PhysikProfs} {PhiloVorls, PhiloProfs} {TheolVorls, TheolProfs} Die Allokation (Ortszuweisung) bestimmt nach der Datenfragmentierung, welchem Rechner jedes der Fragmente zugeordnet wird, wobei eine replizierte Allokation von Fragmenten möglich ist. In der Abbildung 23 wird so eine Zerlegung in 3 Fragmente vorgenommen, von denen für R1 und R3 eine 478 / 520 replizierte Allokation stattfindet: c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 77 15.3 Transparenz R R1 R11 S1 R21 R2 R12 R3 S2 R32 R33 S3 Abbildung 23: Beispiel einer Allokation • Die Menge der einem Rechner zugeordneten Fragmente einer globalen Relation ergeben dessen lokale Relation; z.B. umfasst die an Station S1 vorliegende lokale Relation die Fragmente R1 und R2 . Die • DB-Partition eines Rechners besteht aus der Menge seiner lokalen Relationen. 15.3 Transparenz Begriffsdefinition Grad der Unabhängigkeit, den ein VDBMS dem Benutzer beim Zugriff auf veteilte Daten vermittelt Die Anforderungen an Mehrrechner-DBMS sollen natürlich auch von Verteilten DBMS erfüllt werden, insbesondere hohe Leistungsfähigkeit, hohe Verfügbarkeit, Verteilungstransparenz sowie Unterstützung dezentraler (geographisch verteilter) Organisationsstrukturen. 15.3.1 Fragmentierungstransparenz Eine Relation der Datenbank sollte verteilt an mehreren Knoten gespeichert werden können. Die dabei zugrundeliegende Fragmentierung der Relation braucht der Anwender aber nicht zu wissen (im Beispiel wären die Tabellen Vorlesungen und Professoren jeweils in verschiedene Fragmente aufgeteilt): 1 2 select Titel, Name from Vorlesungen, Professoren where gelesenVon = PersNr Ohne Fragmentierungstransparenz dagegen müsste u.U. auf jedes der drei Fragmente explizit zugegriffen werden. 78 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 15.4 Beispielaufgabe aus dem Unterricht 15.3.2 Allokationstransparenz Die Benutzer müssen die Fragmentierung kennen, aber nicht den „Aufenthaltsort“ eines Fragments: d.h., dass wir dann eben wissen müssen, in welchen Fragmenten welche Spalten abgespeichert werden. Um den vollständigen Datensatz zu rekonstruieren, muss er wieder aus den Fragmenten zusammengestellt werden (vgl. JOIN der beiden Tabellen im folgenden Beispiel): 1 2 3 4 select sum (Gehalt) from ProfVerw, TheolProfs -- Spalten sind in versch. Tabellen verteilt where ProfVerw.PersNr = TheolProfs.PersNr and Rang = ’C4’; 15.3.3 Lokale Schema-Transparenz Der Benutzer muss zusätzlich zum Fragment auch noch den Rechner kennen, auf dem das Fragment liegt: 1 2 select Name from TheolProfs AT STheol where Rang = ’C3’; -- AT verweist auf die DB 15.4 Beispielaufgabe aus dem Unterricht Die Studenten-Tabelle bekommt noch das Attribut Abteilung und ist entsprechend verteilt (I-, E-,..,Station): Studenten s verteilt auf die Fragmente sI und sE und . . . . • Studenten-I sI liegen auf db-1 und db-2 (db-2 ist eine OO-DB) • Studenten-E sE liegen auf db-3 Sämtliche Studenten sollen aufgelistet werden. Schreiben Sie das select-statement, wenn (nur) gilt: Beispiel 58: transparente Fragmentierung 1 select * from S Beispiel 59: transparente Allokation 1 2 3 Select * from sI UNION select * from sE Beispiel 60: lokale Schematransparenz 1 2 3 Select * from sI AT db-1 union select * from sE AT db-3 Beispiel 61: Skizzieren Sie die Suchoperationen in einer Anwendung mit einer OO-DB (OQL) c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 79 15.5 1 2 3 Sperren von Replikationen select stud from stud in alleSI at db-2 union select stud from stud in alleSE at db-3 15.5 Sperren von Replikationen Angenommen, zu einem Datum A gäbe es mehrere Kopien A1, A2, ..., An , die auf unterschiedlichen Stationen liegen. Bei Änderungstransaktionen müssen aber alle bestehenden Kopien geändert werden. 15.5.1 Quorum-Consensus Verfahren10 Das gewichtete Voting (lat. Quorum Consensus) ist ein Verfahren, das die Datenintegrität bei replizierten Datenbanken gewährleisten soll. Dazu wird mit einem Quorum gearbeitet. Daten, die zu einer Partition gehören dürfen nur operieren, wenn sie das Quorum besitzen. Jeder Knoten des Systems erhält so ein Gewicht und besteht ein sogenanntes Lesequorum RT und ein Schreibquorum WT, das bei einem Zugriff erfüllt werden muss. Für das Setzen von RT und WT muss gelten: Gewichte , nur bei Mehrheit wird geschrieben W T = Summe aller 2 W T + RT > Summe aller Gewichte, so wird beim Lesen mindestens eine aktuelle Version gefunden Beim Schreiben eines Datums muss die Summe der Gewichte der beschriebenen Knoten das Schreibequorum erreichen. So wird nur bei Mehrheit aktualisiert und nur eine Partition kann Änderungen vornehmen, die Konsistenz der Datenbank bleibt erhalten. Die Knoten, die an dem Quorum teilnehmen werden aktualisiert, andere Knoten behalten ihren alten Wert. Beim Lesen muss das Lesequorum erreicht werden, es werden also im Allgemeinen mehrere Knoten gelesen. Beispiel 62: Schreibzugriff durch Master mit Ausfallmöglichkeit Gegeben seien 5 Knoten mit je einem Gewicht von 1. Setzt man RT = 1 und WT = 5, so bedeutet das, dass für eine Leseoperation nur ein Knoten zustimmen muss. Für einen Schreibzugriff muss man hingegen auf alle Ressourcen schreiben. Dieses System wäre allerdings nicht ausfallsicher. Man könnte hingegen auch WT = 4 setzen und RT = 2; hier wäre Schreiben beim Ausfall eines Knotens noch möglich. 15.6 Replikationsarten Replikation bezeichnet die mehrfache Speicherung derselben Daten an meist mehreren verschiedenen Standorten und die Synchronisation dieser Datenquellen. Nebst dem Quorum-Consensus Verfahren, gibt es noch folgende Replikationsarten. Diese sind jedoch nicht streng synchronisiert. 10 http://de.wikipedia.org/wiki/Gewichtetes_Votieren 80 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 15.6 Replikationsarten 15.6.1 Snapshot Replikation Die Daten werden auf allen Systemen verteilt. Bei einer Änderung wird nicht zuerst überprüft ob die Daten noch aktuell sind. Die Daten werden beispielsweise nur einmal Täglich synchronisiert. Hierbei wird wieder ein kompletter Snapshot auf das entfernte System geschrieben. Snapshot Replikation wird vorzugsweise benutzt wenn folgendes zutrifft: • • • • Daten ändern selten. Redundante, inkonsistente und veraltete Daten können über eine gewisse Zeit akzeptiert werden. Es werden nur wenige Daten repliziert. Es treten viele Änderungen, aber währen einer kurzen Zeitspanne auf. 15.6.2 Transaktionale Replikation Die Transaktionale Replikation beginnt normalerweise mit einem Snapshot der Datenbank. Änderungen bei der „Hauptdatenbank“ (Publisher) werden schnellst möglich an die Verteilte Station (Subscriber) weitergeleitet. Subscribers sollten Read-Only eingestellt sein. • • • • Subscriber sollen aktuell gehalten werden. Es wird eine geringe Latenz zwischen geänderten und gelesenen Datenständen erwartet. Der Publisher ändert Daten häufig. der Publisher ist eine non-SQL Datenbank, beispielsweise Oracle. 15.6.3 Merge- Replikation Im Gegensatz zur Snapshot Replikation werden bei der Merge- Replikation auch Änderungen auf dem Subscriber berücksichtigt. Diese Art wird hauptsächlich eingesetzt wenn folgendes zutrifft: • • • • Viele Subscribers ändern die gleichen Daten an verschiedenen Zeitpunkten. Subscribers müssen Daten offline ändern können und erst später synchronisieren. Jeder Subscriber benötigt eine andere Datenpartition. Häufiges ändern eines einzigen Eintrages bevor synchronisiert wird. Bestehen Konflikte müssen delektiert und gelöst werden können. Dies muss unter umständen manuell geschehen. c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 81 16 OLTP, Data Warehouse, Data Mining 16.1 OLTP: Online Transaction Processing im Dialogbetrieb ablaufende Massendatenverarbeitung in operativen DV-Systemen, bei der betriebswirtschaftliche Transaktionen direkt und prompt – also ohne nennenswerte Zeitverzögerung – erfasst und verarbeitet werden. Merke: OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). (http://datawarehouse4u.info/OLTP-vs-OLAP.html) Das technische Hauptaugenmerk beim OLTP liegt auf der Transaktionssicherheit bei parallelen Anfragen und Änderungen, auf der Minimierung der Antwortzeit von Anfragen sowie auf einem möglichst hohen Durchsatz (Anzahl Transaktionen pro Zeiteinheit). Die Effizienz von OLTP-Systemen ist dabei von der Auswahl geeigneter Hardware (Datenbankserver, Netzwerkkommponenten wie LAN und WAN) und Software (Datenbankmanagementsystem) abhängig. Durch die Beachtung von Transaktionskriterien (siehe ACID, Kasten in Kapitel 9.1.1 auf Seite 54) wird sichergestellt, dass die Konsistenz der Datenbank (es können heutzutage auch mehrere verteilte sein) erhalten bleibt und Daten nie unvollständig oder inkonsistent gespeichert werden. (http://de.wikipedia.org/wiki/ Online_Transaction_Processing) 16.1.1 OLAP: Online Analytical Processing – das Gegenteil von OLTP OLAP-Auswertungen bilden die Grundlage für die Strategische Unternehmensplanung und beziehen ihre Daten entweder aus den operationalen Datenbeständen eines Unternehmens oder aus einem Data-Warehouse (Datenlager). Hierdurch wird verhindert, dass die Analysedaten mit den transaktionsorientierten Datenbeständen in Kontakt kommen, und die Leistungsfähigkeit beeinträchtigt wird. Ebenso ist die Leistung eines OLAP-Systems von der verwendeten Datenhaltungsform und deren Anbindung an den Analyse-Client abhängig. Merke: OLAP ist spezielles query processing! Dafür kann insbesondere SQL benutzt werden. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). (http://datawarehouse4u.info/OLTP-vs-OLAP.html) 82 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 16.2 Data Warehouse Im Gegensatz zum Online-Transaction-Processing (OLTP) steht hier die Durchführung komplexer Analysevorhaben im Vordergrund, welche ein sehr hohes Datenaufkommen verursachen. Die OLAP zugrunde liegende Struktur ist ein OLAP-Würfel (englisch cube), der aus der operationalen Datenbank erstellt wurde. Dieser folgt einer multidimensionalen, datenpunktorientierten Logik im Gegensatz zur zeilenorientierten Logik beim Online-Transaction-Processing (OLTP). (http://de.wikipedia.org/ wiki/Online_Analytical_Processing) 16.2 Data Warehouse Data Warehouses sind „Super-Datenbanken“, welche vielfach aus vielen verschiedenen Datenbanken Sammlung und periodische bestehen. Sie sind speziell aufbereitet für ein schnelles von Daten: Auffrischung der Data Auslesen Warehouse-Daten OLTP-Datenbanken und andere Datenquellen OLAP-Anfragen Decision Support Data Mining D M G Data Warehouse (DW) © A. Kemper / A. Eickler 6 Abbildung 24: Sammlung und periodische Auffrischung der Data Warehouse-Daten Darin abgelegte Daten können durchaus inkonsistent (Verstoss gegen Kriterien der Redundanzfreiheit) vorkommen, da die Aktualisierung ein mühsamer Prozess darstellt (z.B. Extraktion aus den operativen DBs, Transformation ins DW-Schema, Laden in die DW-DB)11 . 16.2.1 Stern-Schema Das Sternschema ist eine besondere Form eines Datenmodells, dessen Ziel nicht die Normalisierung ist, sondern eine Optimierung auf effiziente Leseoperationen. Das Schema setzt sich aus einer nicht redundanzfreien Faktentabelle (z.B. „Verkäufe“) und mehreren Dimensionstabellen (z.B. „Kunden“, „Produkte“, . . . ) zusammen, welche abfragefreundlich um eine Faktentabelle sternförmig geordnet werden und sich bei diesem Schema auf genau eine Faktentabelle beziehen. (https:// de.wikipedia.org/wiki/Sternschema) 11 Bei einem redundanzfreien Aufbau von Datenbanken werden Datensatzabfragen durch JOINS verknüpft – was oftmals sehr aufwändig sein kann c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 83 16.2 Data Warehouse Redundanzen in der Faktentabelle werden – wie gesagt – akzeptiert , da dies oft viel einfacher zu organisieren ist als die Daten beispielsweise immer redundanzfrei (z.B. ohne Mehrfacheinträge gleicher Entitäten) zu verwalten12 : Abbildung 25: Stern-Schema mit sehr grosser Faktentabelle und mehreren Dimensionstabellen 16.2.2 Verdichtung der Daten zu Datenwürfeln Ein CUBE ist so etwas wie ein „mehrdimensionaler Datenwürfel“, der alle möglichen Gruppierungen von Datenabfragen (mittels GROUP BY-Klausel der verschiedenen Attribute) aggregiert und zusammenfasst. Mit der GROUP BY CUBE-Klausel können beliebige n-dimensionale Auswertungen aggregierter Daten zusammengestellt und in einer Matrix gehalten werden: Beispiel 63: Der „cube“-Operator 1 2 3 4 5 6 7 8 select p.Hersteller, z.Jahr, f.Land, sum(v.Anzahl) -- Daten aus Faktentabelle ’Verkaeufe v’ und den Dimensionstabellen holen: from Verkaeufe v, Produkte p, Zeit z, Filialen f -- verdichten mit der Faktentabelle ’Verkaeufe v’: where v.Produkt = p.ProduktNr and p.Produkttyp = ’Handy’ and v.VerkDatum = z.Datum and v.Filiale = f.Filialenkennung -- Ergebnisse zu einem Datenwuerfel verdichten (--> GROUP BY CUBE): GROUP BY CUBE (z.Jahr, p.Hersteller, f.Land); 12 Die „JOINEREI“ entfällt dadurch (Olnhoff,11.05.2013). 84 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 16.2 Data Warehouse Zur Veranschaulichung: Würfeldarstellung D M G © A. Kemper / A. Eickler c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 23 85 17 XML-Datenmodellierung (mit Java-Bibliotheken, Fokus XQuery) 17.1 HTML-Datenmodell Das HTML-Datenmodell besitzt kein Schema. Folglich können nur „Insider“ diese Listen interpretieren. Das Datenmodell ist nur für Datenaustausch geeignet, wenn separat auch eine Beschreibung mitgeschickt wird. HTML-Modell „Nur“ Darstellung. Relationales Modell „Nur“ Daten mit ihrer Bedeutung. 17.1.1 XML-Datenmodell Das XML-Datenmodell liegt irgendwo dazwischen. • Semi-strukturierte Daten – Teilweise schematisch (well-formed Einschränkung) – Wenn ein Schema vorhanden, dann muss es eingehalten werden 13 – Das Schema bleibt sprechend. Beispiel 64: XML-Datenmodell 1 <?xml version="1.0" encoding="ISO-8859-1"?> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 <shiporder orderid="889923" xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance" xsi:noNamespaceSchemaLocation="shiporder.xsd"> <orderperson>John Smith</orderperson> <shipto> <name>Ola Nordmann</name> <address>Langgt 23</address> <city>4000 Stavanger</city> <country>Norway</country> </shipto> <item> <title>Empire Burlesque</title> <note>Special Edition</note> <quantity>1</quantity> <price>10.90</price> </item> <item> 13 valide Einschränkung bzgl. einem Xschema 86 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17.2 18 19 20 21 22 Schemabeschreibung <title>Hide your heart</title> <quantity>1</quantity> <price>9.90</price> </item> </shiporder> 17.2 Schemabeschreibung Beispiel 65: XSD zum XML-Datenmodell 1 <?xml version="1.0" encoding="ISO-8859-1" ?> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="shiporder"> <xs:complexType> <xs:sequence> <xs:element name="orderperson" type="xs:string"/> <xs:element name="shipto"> <xs:complexType> <xs:sequence> <xs:element name="name" type="xs:string"/> <xs:element name="address" type="xs:string"/> <xs:element name="city" type="xs:string"/> <xs:element name="country" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="item" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="title" type="xs:string"/> <xs:element name="note" type="xs:string" minOccurs="0"/> <xs:element name="quantity" type="xs:positiveInteger"/> <xs:element name="price" type="xs:decimal"/> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="orderid" type="xs:string" use="required"/> </xs:complexType> </xs:element> </xs:schema> c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 87 17.3 Anfragesprachen, Fokus: XQuery 17.3 Anfragesprachen, Fokus: XQuery 17.3.1 XML-Anfragesprache XQuery und FLWOR-Regel XQuery ist eine Sprache mit der Anfragen an XML-Dokumente gestellt sowie Ergebnisdokumente generiert werden können. Dabei spielt XPath eine wichtige Rolle. Beispiel 66: Pfadausdrücke XPath document(“uni.xml“)/Fakultäten/Fakultät[FakName=“Physik“] //Vorlesung document(“uni.xml“)/Fakultäten/Fakultät[2]//Vorlesung document(“uni.xml“)/Fakultäten/Fakultät[FakName=“Physik“]/ ProfessorIn/Vorlesungen/Vorlesung document(“uni.xml“)//Vorlesung[Titel=“Mäeutik“]/@Voraussetzungen→/Titel Beispiel 67: XQuery Abfrage Selektiere PersNr und Name aller Professoren der Fakultät „Physik“ und sortiere nach deren Namen absteigend 1 2 3 4 5 6 7 8 -- Hinweis: "WHERE"-Klausel kann auf zwei verschiedene Arten erfolgen select unixml.query(’ for $p in //Fakultaet[FakName="Physik"]/ProfessorIn where $p/../FakName = "Physik" order by $p/Name[1] descending return <Prof><PersNr>{data($p/@PersNr)}</PersNr>{$p/Name}</Prof> ’) as prof from dbo.uniTb Ergebnis: 1 2 3 4 5 6 7 8 <Prof> <PersNr>2127</PersNr> <Name>Kopernikus</Name> </Prof> <Prof> <PersNr>2136</PersNr> <Name>Curie</Name> </Prof> Merke: Eine zentrale Rolle in XQuery spielen die sogenannten FLWOR-Ausdrücke (ausgesprochen: flower). FLWOR ist eine Abkürzung für die Konstrukte for, let, where, order by und return, und kann als 88 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17.3 Anfragesprachen, Fokus: XQuery Analogie zu den (SELECT, FROM, WHERE) - Konstrukten in SQL betrachtet werden.a a http://de.wikipedia.org/wiki/XQuery#FLWOR-Ausdr.C3.BCcke c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 89 17.4 Java API Zusammengefasst: • XQuery basiert auf XPath • Formuliert mit FLWOR-Ausdruck: FOR – LET (Variablen definieren) – WHERE – ORDER – RETURN • Freudig: MSSqlServer-2012 unterstützt LET • Traurig: XQuery unterstützt → (Dereferenzierung) nicht, umständliche Formulierunt mit contains Wichtige Befehle: • $x//zzz: rekursive Suche nach zzz innerhalb $x • $x/zzz: Suche nach direktem Nachfolger zzz innerhalb $x • @hoert bedeutet: hoert ist ein Attribut • $s in //Student bedeutet: ist eine Variable, welche die Inhalte aller Studentenknoten der gesamten Datenstruktur (beliebige Tiefe!) temporär speichert • CONTAINS(haystack, needle): sucht nach needle in der Liste haystack • return $v/../../Name): liefert den Knoten Name zwei Elternelemente (Ebenen) höher als $v 17.3.2 XQuery-Implementation mit CONTAINS CONTAINS sucht innerhalb einer Werteliste nach einem bestimmten Wert. Beispiel 68: XQuery Abfrage mit CONTAINS Geben Sie die Namen der Professoren aus, die eine Vorlesung halten, die vom Studenten „Carnap“ besucht wird: 1 2 3 4 5 6 7 SELECT uniXml.query(’ for $v IN //Vorlesung, $s in //Student where $s//Name = "Carnap" and CONTAINS($s/@hoert, $v/@VorlNr) return $v/../../Name’) FROM irgendeinetabelle; 17.4 Java API 17.4.1 Java: simple API for XML (SAX) • Instanzierung eines SAXParser-Objekt p • Eine eigene Klasse h extends DefaultHandler p.parse(xml-datei, h) damit werden beim Parsen aufgerufen in h • startElement, endElement – mit aktuellem tag 90 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17.4 Java API • characters mit aktuellen Werten • ... Beispiel 69: MySax.java 1 package mysax; 2 3 4 5 6 7 8 import import import import import import java.io.File; javax.xml.parsers.SAXParser; javax.xml.parsers.SAXParserFactory; org.xml.sax.Attributes; org.xml.sax.SAXParseException; org.xml.sax.helpers.DefaultHandler; 9 10 /* 11 kleines Beispielprogramm zur Demo für das SAX-Interface SAX = Simple API for XML relevante Methoden des DefaultHandlers werden überschrieben für die Suche in uni.xml nach for $r in //ProfessorIn where $r/Name = "Curie" return $r/Raum Die Raum-Nr kommt nach System.out Mein Suchverfahren mag umständlich sein. Wichtig zum gesamten Verständnis sind Verständnis und Nutzen der Methoden: startElement, endElement, characters Als Kommentare sind Ausgaben * für alle Aufrufe programmiert. */ // myXmlFile = "c:\\temp\\uni.xml": ist das Ihre Test-Datei?? public class MySax extends DefaultHandler { private static final String myXmlFile = "c:\\temp\\uni.xml"; String[] tagnames = new String[100]; // speichert Elementhierachie // root hat index=0 int tagIndex = 0; boolean b1Found = false; // Variable um Kontext für die Suche zu speichern boolean b2Found = false; 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 * * * * * * * * * 30 31 public static void main(String param[]) { 32 33 34 DefaultHandler handler = new MySax(); // damit werden IHRE Handler-Methoden aufgerufen (siehe @Override) 35 36 37 File datei = new File(myXmlFile); boolean laden = datei.canRead(); 38 39 if (laden) { c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 91 17.4 Java API try { SAXParserFactory factory = SAXParserFactory.newInstance(); factory.setValidating(true); SAXParser saxParser = null; 40 41 42 43 44 saxParser = factory.newSAXParser(); 45 46 saxParser.parse(datei, handler); // Parsing und verarbeitung mit IHREN handler-Methoden 47 48 } catch (SAXParseException error) { System.out.println("\n+++Parse Error+++\nZeile: " + error.getLineNumber() + ", message=" + error.getMessage() + ", Datei: " + error.getSystemId()); } catch (Exception e) { System.out.println(e.getMessage()); } 49 50 51 52 53 54 55 56 57 58 59 60 } 61 62 } 63 64 @Override public void startElement(String namespaceURI, String sName, String qName, Attributes attrs) { tagIndex++; tagnames[tagIndex] = qName; // System.out.println("start:"+ qName); // die Attribute (tags + Werte sind auch verfügbar /* * for (int i=0;i<attrs.getLength();i++) { System.out.println( * "n="+attrs.getLocalName(i)+";v="+ attrs.getValue(i)); } */ } 65 66 67 68 69 70 71 72 73 74 75 76 @Override public void endElement(String namespaceURI, String sName, String qName) { // sName: short name when using namespaces, qName=tagName (with prefix) // System.out.println("end:"+ qName); tagIndex--; if (qName.compareTo("ProfessorIn") == 0) { b1Found = false; 77 78 79 80 81 82 83 92 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17.4 Java API } 84 } 85 86 @Override public void characters(char[] ch, int start, int length) { String s = new String(ch, start, length); if (s.trim().length() > 0) // es werden auch leer-strings à 14 bergeben { // System.out.println(tagnames[tagIndex] + ":" + s); if (tagIndex <= 1) { b1Found = false; return; } if ((tagnames[tagIndex].compareTo("Name") == 0) && (tagnames[tagIndex - 1].compareTo("ProfessorIn") == 0) && s.compareTo("Curie") == 0) { b1Found = true; } else if ((tagnames[tagIndex - 1].compareTo("ProfessorIn") != 0)) { b1Found = false; } if (b1Found && (tagnames[tagIndex].compareTo("Raum") == 0)) { System.out.println("********" + s); } } } 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 } 17.4.2 Java: DOM-Interface for XML DOM = Document Object Model • • • • Gesamte Xml-Datei wird zu einem Document-Object doc Ein Pfadausdruck wird zum XPathExpression-Objekt expr expr.evaluate(doc, . . . ) gibt die gesuchten Elemente Nodes Nodes werden traversiert (u.a. getChildNodes) Beispiel 70: MyDom.java 1 2 3 4 5 6 7 8 /* kleines Beispielprogramm zur Demo f????r das DOM-Interface DOM = Document Object Model * Wir k??nnen direkt nach Unterb??umen suchen (NodeLists) * * hier die Suche in uni.xml nach for $r in //ProfessorIn where $r/Name = "Curie" return $r/Raum * Die Raum-Nr kommt nach System.out * compile-Aufruf mit einer XPath-expression ist entscheidend. * / * c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 93 17.4 9 10 Java API // Sie m??ssen myXmlFile (Test-Datei) anpassen?? package mydom; 11 12 13 14 15 16 17 18 19 20 import import import import import import import import import javax.xml.parsers.DocumentBuilder; javax.xml.parsers.DocumentBuilderFactory; javax.xml.xpath.XPath; javax.xml.xpath.XPathConstants; javax.xml.xpath.XPathExpression; javax.xml.xpath.XPathFactory; org.w3c.dom.Document; org.w3c.dom.Node; org.w3c.dom.NodeList; 21 22 23 public class MyDom { private static final String myXmlFile = "c:\\temp\\uni.xml"; 24 public static void doNodeList(NodeList nodes) { // gesamte Hierarchien werden ausgegeben (bis zu den Werten) try { // System.out.println("Anzahl Nodes = " + nodes.getLength()); for (int i = 0; i < nodes.getLength(); i++) { Node node = nodes.item(i); if (node.getNodeType() == Node.TEXT_NODE) { if (node.getNodeValue().trim().length() > 0) { System.out.println("Wert=" + node.getNodeValue()); } } else { System.out.println("N=" + node.getNodeName()); if (node.hasChildNodes()) { doNodeList(node.getChildNodes()); } } } } catch (Exception ex) { System.out.println(ex.getMessage()); } } 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 public static void main(String[] args) { try { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance() ; factory.setNamespaceAware(true); // never forget this! 49 50 51 52 94 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17.4 DocumentBuilder builder = factory.newDocumentBuilder(); Document doc = builder.parse(myXmlFile); XPathFactory xPF = XPathFactory.newInstance(); XPath xp = xPF.newXPath(); XPathExpression expr = xp.compile( //XPath-expression "//ProfessorIn[Name=\"Curie\"]/Raum/text()"); // without text () one more hierarchy // "//ProfessorIn[Name=\"Curie\"]/Raum"); Object result = expr.evaluate(doc, XPathConstants.NODESET); NodeList nodes = (NodeList) result; doNodeList(nodes); 53 54 55 56 57 58 59 60 61 62 } catch (Exception ex) { System.out.println(ex.getMessage()); } 63 64 65 66 } 67 68 Java API } c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 95 17.4 Java API Übersicht über alle Beispiele Beispiel 1: Ehe – ein Beziehungstyp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Beispiel 2: Bestellwesen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Beispiel 3: Menge aller Teilmengen einer Relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Beispiel 4: Relation zweiter Punkte mittels gerichtetem Graphen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Beispiel 5: Relationen in Z . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Beispiel 6: Zusammensetzung von Relationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Beispiel 7: Darstellung von Relationen durch Matrizen (I) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Beispiel 8: Darstellung von Relationen durch Matrizen (II) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Beispiel 9: Kongruenz modulo m . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Beispiel 10: Division – Welcher Lieferant kann ALLE (beide) Teilchen aus Tabelle T liefern? . . . . 21 Beispiel 11: Eine neue Tabelle erstellen mit Fremdschlüsselbeziehung REFERENCES . . . . . . . . . . . 25 Beispiel 12: Mehrere Tabellen mit Fremdschlüsseln erstellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Beispiel 13: Löschen von Tupeln (DELETE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Beispiel 14: Verändern von Tupeln (UPDATE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Beispiel 15: Duplikateliminierung beim Auslesen (SELECT DISTINCT) . . . . . . . . . . . . . . . . . . . . . 26 Beispiel 16: Welcher Professor liest "Mäeutik"? (WHERE ...AND) . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Beispiel 17: JOIN und COUNT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Beispiel 18: Aggregatfunktionen AVG, SUM, GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Beispiel 19: Ergebnis durch Bedingungen mittels HAVING einschränken . . . . . . . . . . . . . . . . . . . . . . . 27 Beispiel 20: Verschachtelte SELECT-Abfragen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Beispiel 21: Existenzquantor (NOT) EXISTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Beispiel 22: Entscheidungen mittels CASE ...WHEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Beispiel 23: Syntax von JOIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Beispiel 24: mehrere Verknüpfungen direkt hintereinander . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Beispiel 25: Rekursion (händisch) – oder die direkten Vorgänger von «Der Wiener Kreis» . . . . . . . . 30 Beispiel 26: Rekursion über die „tmpTbl“ mittels WITH ... UNION ALL ... SELECT . . . . 31 Beispiel 27: Verschachtelte Abfrage mit IN (Mengenabfrage; mindestens ein Wert muss vorliegen) 32 Beispiel 28: Sicht mit zwei Spalten («Name» und «GueteGrad») als Rückgabewert . . . . . . . . . . . . . . 32 Beispiel 29: kompletter JAVA-Code zur Initialisierung und Auswertung von SQL-Statements . . . . . 33 Beispiel 30: JAVA-Code zum Ausführen eines INSERT / UPDATE-Statements . . . . . . . . . . . . . . . . . . 34 Beispiel 31: Prepared Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Beispiel 32: Rückgabe der neu generierten ID bei INSERT-Statement . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Beispiel 33: Row-Level Trigger BEFORE UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Beispiel 34: Row-Level Trigger AFTER INSERT und Umbenennung der Übergangsvariablen NEW 37 Beispiel 35: Trigger in MS-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Beispiel 36: DB-Prozedur (Stored Procedure) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 96 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 17.4 Java API Beispiel 37: Zwei funktionale Abhängigkeiten (PLZ und AHV-Nummer) . . . . . . . . . . . . . . . . . . . . . . . 39 Beispiel 38: Sequentielle Suche im B-Baum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Beispiel 39: Höhe eines im B∗ -Baumes bei einer Suche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Beispiel 40: Anzahl der Blockzugriffe in einem B*-Baum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Beispiel 41: Anzahl der nötigen Blöcke in einem B*-Baum (mit Verweisen) . . . . . . . . . . . . . . . . . . . . 46 Beispiel 42: Berechnung der Restklassen bei Statischem Hashing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Beispiel 43: SQL-Select und optimierte relationale Algebra (ohne Baum) . . . . . . . . . . . . . . . . . . . . . . . 50 Beispiel 44: SQL-Select und optimierte relationale Algebra (mit Baum) . . . . . . . . . . . . . . . . . . . . . . . . 50 Beispiel 45: Beispieltransaktion auf Basis des Universitätsschemas: . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Beispiel 46: neuen User erfassen mittels CREATE USER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Beispiel 47: Zugriff ermöglichen (SELECT / UPDATE zulassen) mittels GRANT . . . . . . . . . . . . . . . 63 Beispiel 48: Entzug von Rechten (des UPDATE-Statements) mittels REVOKE . . . . . . . . . . . . . . . . . . 63 Beispiel 49: Änderungsbeschränkung mit GRANT auf bestimmten Daten (VIEW) . . . . . . . . . . . . . . 64 Beispiel 50: Modellierung mit „inverse“ . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Beispiel 51: Typeigenschaften: Extensionen EXTENT und Schlüssel key . . . . . . . . . . . . . . . . . . . . . 66 Beispiel 52: Abfrage in OQL über eine Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Beispiel 53: Erzeugung von Objekten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Beispiel 54: Objekterzeugung und Ballung von Objekten (= Angabe zur Platzierung im Speicher [C++]) 67 Beispiel 55: Verschachtelte Transaktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Beispiel 56: Objektorientierte Datenbank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Beispiel 57: Entity-Klasse JVorlesungen.java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Beispiel 58: transparente Fragmentierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Beispiel 59: transparente Allokation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Beispiel 60: lokale Schematransparenz . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 Beispiel 61: Skizzieren Sie die Suchoperationen in einer Anwendung mit einer OO-DB (OQL) . . . 79 Beispiel 62: Schreibzugriff durch Master mit Ausfallmöglichkeit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Beispiel 63: Der „cube“-Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Beispiel 64: XML-Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Beispiel 65: XSD zum XML-Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Beispiel 66: Pfadausdrücke XPath . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Beispiel 67: XQuery Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Beispiel 68: XQuery Abfrage mit CONTAINS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Beispiel 69: MySax.java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Beispiel 70: MyDom.java . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 c The Software Bakery | Bruno Leupi, Tobias Maestrini, Edy Wermelinger 97
