Themen - Downloads für den Studiengang WI
Werbung
Vorlesung Datenbanken 2 - A. Achilles Themen Prolog Wozu Datenbanken Datenmodelle und Grundlagen Datenbank-Entwurf Relationale DBMS Schnittstellen Optimierung Replikation und Synchronisation DRDA XML-Datenbanken DataWareHouse-Konzept Objekt-Relationale DBMS Objektorientierte DBMS -1- Vorlesung Datenbanken 2 - A. Achilles Prolog Prof. Dr. Albrecht Achilles Büro: C.2.46 Sprechstunde: Dienstag 11-12 Telefon 0231/755-6782 privat: 02306/42489 montags, mittwochs: 16.00-17.00 dienstags, donnerstags: 17.00-18.15 freitags vormittags(?) e-mail: [email protected] www: www.inf.fh-dortmund.de/personen/professoren/achilles Vorlesung Studiengang Fachnummer Vorlesung Übung Praktikum Datenbanken Wirtschaftsinformatik 43101 Mo, 8:30-10:05 Raum A.E.02 Mo, 10:15-11:00, Raum A.E.02 Mo, 11:05-13:35, Raum B.E.22 Themen Wozu Datenbanken Geschichtliche Betrachtung Einsatzarten Anforderungen Standardisierungen Entwicklungen Datenmodelle Relationale DBMS Schnittstellen Datenbank-Entwurf Optimierung Replikation und Synchronisation DRDA DataWareHouse-Konzept Objektorientierte DBMS Bücher Achilles, A.: SQL - Standardisierte Datenbanksprache vom PC bis zum Mainframe Oldenbourgh, Auflage von Mitte 1999 Date, C.J.: An Introduction to Database Systems Vol I, II Addison-Wesley, 1990 Date, C.J., Darwen, H.: A Guide to The SQL Standard Addison-Wesley, 1997 Lang, S.M., Lockemann, P.C.: Datenbankeinsatz Springer, 1995 Melton, J., Simon, A.R.: Understanding Relational Language Concepts Morgan Kaufmann Publishers, 1999 Ceri, St., Pelagatti, G.: Distributed Databases, Principles and Systems McGraw Hill, 1984 Weitere Hinweise Kimball, R.: The Data Warehouse Toolkit John Wiley & Sons, 1996 McFadden, F. R. et al.: Modern Database Management Addison-Wesley, 1998 Morrison, J.: Database Driven Web Sites Course Technology, Inc., May 2000 Mullins, C. S.: DB2 Developers Guide Yarger, R. J. et al.: MySQL and mSQL O'Reilly & Associates, 1999 Fleming, C.C., von Halle, B.: Handbook of Relational Database Design Addison-Wesley, 1989 (hrsg.) Lockemann, P.C., Schmidt, J.W.: Datenbank-Handbuch Springer-Verlag, 1987 -2- Vorlesung Datenbanken 2 - A. Achilles Quellen im Internet Auflistung von deutschsprachigen Online-Tutorials (auch zum Thema Datenbanken) Interaktives SQL-Online Tutorial Datenbank-Design Oracle Database Documentation DB2 Online MySQL Tutorial Lotus Notes und Domino http://java.sun.com/docs/index.html http://www.sqlcourse.com http://www.sum-it.nl/cursus/dbdesign/english http://docs.oracle.com/database_mp.html http://www-4.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v7pubs/en_main http://www.mysql.com/doc/T/u/Tutorial.html http://www.notes.net Abschluss Unbenoteter Teilnahmenachweis berechtigt zur Teilnahme an der Fachprüfung wird erreicht durch Abgabe von 50% der gestellten Aufgaben die Anzahl der abzugebenden Aufgaben liegt zu den Weihnachtsferien fest Fachprüfung (FPg) Form: Klausur Hilfsmittel: ??? wird rechtzeitig bekannt gegeben -3- Vorlesung Datenbanken 2 - A. Achilles Datenbanken - Wozu Geschichtliche Entwicklung Einsatzarten Anforderungen Standardisierungen Entwicklungen Geschichtliche Entwicklung Dateiensystem: in Anwendungen verankerte I/O-Programmierung => Neuprogrammierung aller I/O-Module, bei Verlagerung oder Veränderung der Dateien, Einführung neuer Platten-Hardware, struktureller Veränderung der gespeicherten Daten Zur Wahrung der Datenkonsistenz: Abgleichprogramme Prä-Relationale DBMS Datacom (invertierte Listen) IMS (hierarchisch, 68) IDMS (Netzwerk, 71) Relationale DBMS Aufsätze seit ca. 75 DB2 (seit ca. 1980) Oracle ... verteilt (seit ca. 95) MySQL (Web-Anfragen) Post-Relational Objekt-Relational: Weiterentwicklung von RDBMS Objekt-Orientiert: z.B. Poet (seit ca. 95) -4- Vorlesung Datenbanken 2 - A. Achilles Einsatzarten "traditionell": kleine bis mittlere Komplexität der Datenstruktur, geringe Transaktionsrate kleines bis mittleres Transaktionsvolumen kleines bis großes Datenvolumen "formatierte Daten" Beispiele: Lagerhaltung Personalverwaltung Geschäftsvorgänge "neue Anwendungen": Typ A mittlere bis hohe Komplexität der Datenstruktur, viele lesende Zugriffe pro Zeiteinheit: Datenvolumen oder Zahl der lesenden Transaktionen geringe schreibende Transaktionsrate sehr geringes Transaktionsvolumen mittleres bis sehr großes Datenvolumen Beispiele: Data Warehouse Web-Anwendungen Typ B geringe bis mittlere Komplexität der Datenstruktur, geringe lesende Zugriffe pro Zeiteinheit extrem hohe Transaktionsraten extrem hohes Transaktionsvolumen mittleres bis sehr großes Datenvolumen Beispiele: Daten für Energieversorger Daten für Telefonabrechnung (insb. Handy) Typ C geringe bis sehr hohe Komplexität der Datenstruktur, geringe lesende Zugriffe pro Zeiteinheit geringe Transaktionsraten ggf. sehr lange Transaktionsdauern extrem hohes Transaktionsvolumen mittleres bis sehr großes Datenvolumen Beispiele: Dokumentenretrieval-Systeme CAD/CAM-Systeme geographische Datenbanken -5- Vorlesung Datenbanken 2 - A. Achilles Anforderungen seit der relationalen Phase Programmierung nicht IO-zentriert: unabhängig von physischer Speicherung unabhängig von Performance-steigernden Maßnahmen Datenadressierung logisch über Eigenschaften Sichern der Integrität Kontrolle der Zugriffe Transaktionsunterstützung Backup und Recovery Verteilung Anforderungen an RDBMS Codd Basis-Regeln Struktur-Regeln Manipulations-Regeln Integritäts-Regeln Basis-Regeln Datenrepräsentation auf logischer Ebene als Tabellen jeder gespeicherte Wert ist logisch durch Kombination von Tabellenname, Primärschlüssel und Spaltenname erreichbar Der NULL-Wert wird unterstützt, er ist von allen anderen Werten verschieden der Katalog ist wie alle anderen Daten auch als Tabellen repräsentiert es muss mindestens eine DB-Sprache geben, die Datendefinition Datenmanipulation Datenretrieval Integritätsbeschränkungen Autorisierungen Transaktionen unterstützt theoretisch änderbare virtuelle Tabellen müssen auch vom System geändert werden Relationen können durchgängig als Operanden behandelt werden Anwendungsprogramme bleiben logisch unberührt von Veränderung der Speicherrepräsentation das Gleiche gilt für informationserhaltende Änderungen der Tabellen Integritätsregeln werden in relationaler DB-Sprache definiert und im Katalog gespeichert. Das System überwacht sie eigenständig und unabhängig von den Anwendungsprogrammen das DBMS ist verteilungsunabhängig falls eine "low-level"-Schnittstelle existiert, können damit nicht Integritäts- und Autorisierungsregeln umgangen werden Struktur-Regeln Beziehungen zwischen Feldern und Tabellen nur über benannte Spalten und unnumerierte Zeilen alle gespeicherten Daten sind in Basistabellen enthalten Ergebnis einer Anfrage ist wiederum eine Tabelle Daten einer virtuellen Tabelle werden bei der Ausführung dynamisch erzeugt Tabellenkopien, die gespeichert werden, erhalten einen Eintrag im Katalog jede Spalte einer Tabelle kann als Attribut aufgefaßt werden das Konzept der Domänen wird unterstützt jede Tabelle hat einen Primärschlüssel stammt eine Spalte(nkombination) aus der gleichen Domäne wie der Primärschlüssel einer (anderen) Tabelle, so handelt es sich um einen Fremdschlüssel zu der Tabelle -6- Vorlesung Datenbanken 2 - A. Achilles Manipulations-Regeln Folgende Operationen werden zumindest unterstützt: Selektion von Zeilen bzgl. der Vergleichsoperatoren = <= < >= > <> Projektion auf Spalten Join von Tabellen mittels Vergleichs von Spalten mit obigen Vergleichsoperatoren Outer Join Division Mengenorientierte Operationen: UNION INTERSECT SET DIFFERENCE Integritäts-Regeln Primärschlüssel darf in keiner Komponente NULL enthalten für jeden Fremdschlüssel, der nicht NULL enthält, muß es einen Eintrag mit korrespondierendem Primärschlüssel in der referenzierten Tabelle geben Anwendungsbezogene Integritätsregeln müssen in der DB-Sprache unterstützt und im Katalog eingetragen werden Standardisierungen CODASYL ANSI/SQL, ISO NIST X/Open ODMG Entwicklungen (RDBMS) Trigger Verteilung Objekt-Relational -7- Vorlesung Datenbanken 2 - A. Achilles Modelle Das hierarchische Modell Benutzer nimmt Daten in hierarchischer Form wahr, also nicht wie im relationalen Modell in Tabellenform Benutzer muss durch die Daten navigieren typischer Vertreter: IMS Beispiel: Modellierung Studenten aus der Sicht des PS Problem: 1. Ebene Student oder Prüfung? Entscheidung für die Ebenen: 1. Student mit Matrikelnummer, Name, Anschrift 2. Grundstudium - Hauptstudium 3. Fächer mit Nr., Dozent, Semester, Ergebnis Besonderheiten im Hauptstudium: Projekt, Arbeit, Kolloquium in IMS Strukturbeschreibung in Assembler-Datei durch die Übersetzung werden ausführbare Dateien und Bibliotheken erzeugt: zum physischen Anlegen der Datenspeicher zum Erzeugen von Zugriffsmethoden, mit deren Hilfe kann aus einem Programm heraus auf die Daten zugegriffen werden graphische Darstellung Skizzierung des DD-Files (Datendefinition) DBD SEGM FIELD FIELD FIELD SEGM FIELD FIELD SEGM FIELD FIELD FIELD FIELD FIELD ... NAME=STUDIES NAME=STUDENT,BYTES=100 NAME=(MATRNR#,SEQ),BYTES=7,START=1 NAME=NAME,BYTES=30,START=8 NAME=ADDRESS,BYTES=63,START=38 NAME=GRUNDST,PARENT=STUDENT;BYTES=2 NAME=(NR#,SEQ),BYTES=1,START=1 NAME=DONE,BYTES=1,START=2 NAME=VORLES,BYTES=35 NAME=(NR#,SEQ),BYTES=2,START=1 NAME=CODE,BYTES=7,START=3 NAME=DOZENT,BYTES=20,START=10 NAME=SEMSTR,BYTES=2,START=30 NAME=RESULT,BYTES=3,START=32 -8- Vorlesung Datenbanken 2 - A. Achilles Skizzierung des PCB-Files (Sicht) PCB SENSEG SENFLD SENFLD SENSEG DBDNAME=STUDIES NAME=STUDENT,PROGOPT=G NAME=MATRNR,START=1 NAME=NAME,START=8 NAME=GRUNDST,PROGOPT=G Damit wird festgelegt, welche Felder der Benutzer sieht wie er auf die Felder zugreifen darf Skizzierung der Operationen Verwendet werden Funktionsaufrufe (z.B. in PL/I), deren Parameter die entsprechende Aktion angeben: GU Get Unique GN Get Next GNP Get Next under current Parent GHU wie GU mit der Möglichkeit eines anschließenden DLET oder REPL entsprechend GHN und GHNP ISRT InSeRT new segment DLET DeLETe existing segment REPL REPLace existing segment GU dient auch der Initialisierung (initial position). Navigierender Zugriff (Pseudo-Code): GU STUDENT WHERE MATRNR# = ' ... ' ; do until no more GRUNDST ; GN GRUNDST ; do until no more VORLES ; GNP VORLES ; /* mach was damit */ end ; end ; Besonderheiten Logical Database Secondary Indexes Recovery Concurrency: Record (Segment) locking Security (via PCB) Integrity: Uniqueness (seq), Regeln für logische Datenbanken ANSI/SPARC-Architektur: drei Ebenen-Architektur -9- Vorlesung Datenbanken 2 - A. Achilles Wie eine Anfrage bearbeitet wird Aufgaben eines DBA Erstellen der konzeptionellen Sicht, Erstellen der internen Sicht, Erstellen der externen Sichten - dies betrifft insbesondere Zugriffsrechte, Dazu kommen noch: Verankern von Regeln, die die Sicherheit und die Integrität der Daten gewährleisten, Datenschutz, Erstellen von Backup-Strategien und Recovery-Prozeduren, Überwachen der Performance und Erstellen von Maßnahmen, um die Gesamtperformance zu steigern, in Zusammenarbeit mit der Systemgruppe: Installation des DBMS und Einbinden neuer Hardware. - 10 - Vorlesung Datenbanken 2 - A. Achilles Generelle Struktur eines DBMS DBA Typische Eingriffsmöglichkeiten auf unterster Ebene: Reservieren von Hauptspeicher für Systempuffer usw. Zeitpunkt: Installation des DBMS Verteilung der Daten auf die Platten Zeitpunkt: Implementation der DB sowie im Lebenszyklus der DB Anschaffung neuer Speicherperipherie zusammen mit Betriebssystemsgruppe Zeitpunkt: Lebenszyklus der DB Einwirkungen auf unterer Ebene: Füllungsgrad der Seiten beeinflussen Reorganisation und damit Clustern der Daten auf den Speicherseiten Bereitstellen von Sekundärindexen Typ eines Sekundärindexes bestimmen (soweit das DBMS dies zuläßt) - 11 - Vorlesung Datenbanken 2 - A. Achilles Einwirkungen auf höherer Ebene: Informationserhaltende Strukturänderungen Bereitstellung von DB-Prozeduren Zugriffspfad-Analyse und -Optimierung TID Indirektion und damit einfache Verschiebbarkeit von Information auf einer Seite Grund: nach mehreren Einfügungen, Löschungen und Änderungen von Datensätzen in einer Seite entstehen Lücken, da die Sätze in der Regel unterschiedlich lang sind => Garbage Collection kann wieder ausreichend Platz auf der Seite beschaffen TID-Konzept verschiebt die Information, wo genau der Datensatz steht, auf die Seite: kürzere Information über den Speicherort gegenüber der vollständigen Adresse bei Garbage Collection braucht nur der Index auf der Seite, die sich bereits im Speicher befindet, geändert werden bei Verschieben von Datensatz auf Überlaufseite (bei Clusterung) muss nur Eintrag im Seitenindex geändert werden Cluster Erfolgt häufig ein Zugriff auf Datensätze in einer bestimmten Reihenfolge, kann eine Clusterung die Performance steigern: die Datensätze werden so auf die Seiten verteilt, dass Sätze, die in der Ordnung (Clusterindex) aufeinanderfolgen, so weit wie möglich jeweils auf einer Seite zusammengefasst werden bei der Verarbeitung in Reihenfolge des Clusterindex werden die physischen Speicherzugriffe minimiert Problem liegen Daten geclustered vor und erfolgen eine Reihe von Änderungen, so können nach gewisser Zeit Datensätze nicht mehr auf diejenige Seite geschrieben werden, auf die sie auf Grund der Clusterung kommen müßten Überlaufseiten zur Aufnahme der Datensätze Lösungen: Füllungsgrad der Seiten verändern Reorganisation - 12 - Vorlesung Datenbanken 2 - A. Achilles Index Aufgabe eines Indexes ist es, einen schnellen Zugriff auf gewünschte Information zu ermöglichen Zugriff auf Datensätze in sortierter Reihenfolge direkter Zugriff auf einen Datensatz Bereichsanfrage Dazu wird der Schlüssel zusammen mit dem TID gespeichert => die zu speichernde Datenmenge ist wesentlich reduziert Zum Suchen gut geeignete Strukturen: Pointerketten Index-sequentiell B-Tree Hash Die Art des Index ist in den meisten Fällen bereits durch die Wahl des DBMS vorgegeben Index: Pointerketten Voraussetzung: Pointerkette wird in Schlüsselreihenfolge verwaltet Schnellerer Zugriff allein dadurch, dass zu durchsuchende Datenmenge erheblich reduziert Bereichsabfragen sowie sortierter Zugriff möglich Typische Verwendung z.B. in Netzwerk-DBMS Index: Index-sequentiell Voraussetzung: Speicherung auf den Daten-Seiten in Schlüsselreihenfolge (sequentiell) Im Index wird für jede Daten-Seite der Schlüssel (und TID) des letzten Records aufgenommen (desjenigen mit größtem Schlüsselwert) Index selbst sequentiell geordnet => Verfahren kann mehrstufig benutzt werden Bereichsanfragen sowie sortierter Zugriff sehr gut Suchen: vgl. "Algorithmen..." Besonderheiten: geeignet z.B. als Cluster-Index Überlauf-Organisation Reorganisations-anfällig Index: B-Tree insbesondere: ausgeglichener sortierter B-Tree Spezialfall von Index-sequentiell wird mit k die für den Baum vereinbarte Schlüsselanzahl bezeichnet, so gilt: der Baum ist sortiert jeder Knoten (bis auf die Wurzel) enthält mindestens k und max. 2k Schlüssel der Weg von der Wurzel zu jedem Blatt ist gleich lang k beschreibt die Breite (Fan Out) des Baumes: k=100 bedeutet z.B., dass man mit nur zwei Zugriffen 100x100=10000 TIDs auffinden kann, mit drei Zugriffen einen Datensatz in 10000 Datensätzen lesen kann schneller Zugriff Bereichsanfragen möglich sortierter Zugriff möglich Problem: Reorganisation Einsatz typisch für RDBMS - 13 - Vorlesung Datenbanken 2 - A. Achilles Index: Hash die Verbindung zwischen Schlüssel und TID erfolgt über eine Funktion, kein Lesezugriff => besonders schnell Problem: keine Bereichsanfrage möglich! kein Zugriff in sortierter Reihenfolge möglich Wahl der geeigneten Hash-Funktion Index Nur eindeutiger Index garantiert, daß angegebener Wert höchstens einmal vorhanden ist Vorteile beim Einsatz: schnelles Suchen von Datensätzen Nachteile beim Einsatz: bei Änderungen (Einfügen, Löschen, Ändern von Datensätzen) muß Index mitgepflegt werden Verlangsamung bei sehr kleinen Dateien (nur wenige Seiten) erzeugt Index sogar beim Lesen zuviel Overhead Index: kombinierte Spalten Index kann aus mehreren Attributen kombiniert sein. Index kann beim Auffinden nur dann sinnvoll benutzt werden, wenn Attribute von links her ohne Lücken bekannt sind: Tabelle Prüfungen MatrNr 4711 4712 4712 4711 4711 VName Programmierung Programmierung Programmierung Datenbanken Programmierung 1 1 2 2 Semester Sommer 2000 Sommer 2000 Winter 2000 Sommer 2001 Sommer 2001 Note 2.0 3.0 3.0 1.0 1.3 werden Informationen über Prüfungen verwaltet und ein Index über MatrNr, VName und Note (in dieser Reihenfolge) angelegt, so kann Index ausgenutzt werden, wenn MatrNr, VName und Note MatrNr und VName MatrNr bekannt sind, sonst jedoch nicht. Manche DBMSe ermöglichen es, mit einem Zugriff auf den Index auszukommen ohne auf die Datensätze zuzugreifen, wenn gesuchte Information bereits im Index vorhanden ist. Anmerkung: die Reihenfolge des Indexspalten muß nicht mit der Reihenfolge der Spalten in der Tabelle übereinstimmen Datenkompression damit kann das gespeicherte Datenvolumen verringert werden weniger Lesezugriffe mehr CPU-Leistung bei der Dekomprimierung Manche DBMSe verwenden spezielle Kompression bei Cluster-Index und geclusterten Datensätzen Optimizer Aufgabe der Komponente: Ermitteln von Zugriffspfaden Ermitteln des "günstigsten" Pfads Kriterien für "günstigen" Pfad: - 14 - Vorlesung Datenbanken 2 - A. Achilles keine Regel-basiert Reihenfolge der Tabellen im SELECT-Statement wenn möglich, Index verwenden ... Kosten-basiert Ermittlung mehrerer Alternativen abschätzende Bewertung der jeweiligen Kosten Plattenzugriffe CPU-Belastung Speicherbedarf Auswahl der günstigsten Alternative Beispiel: Optimzer Tabelle Student Matr-Nr 4711 4712 Name Mustermann Element Vorname Demo ... Anschrift Irgendwo ... ImmatrikDatum 11.11.1999 ... ExmatrikDatum ... ... ... Tabelle Prüfungen MatrNr 4711 4712 4712 4711 4711 VName Programmierung Programmierung Programmierung Datenbanken Programmierung 1 1 2 2 Semester Sommer 2000 Sommer 2000 Winter 2000 Sommer 2001 Sommer 2001 Note 2.0 3.0 3.0 1.0 1.3 Index auf Student.Name und Prüfungen.Matr.Nr Anfrage: SELECT Student.MatrNr, Name, VName, Semester, Note FROM Student, Prüfungen WHERE Name = "Mustermann" AND Vorname = "Demo" Mögliche Zugriffspfade? Optimizer, Beispiele für Zugriffspfade Einige Beispiele: ohne Indexe: Sequentielles Abarbeiten von Student, zu jedem akzeptierten Datensatz sequentielles Abarbeiten von Prüfungen umgekehrt: Abarbeiten von Prüfungen und für jeden akzeptierten Datensatz Abarbeiten von Student Vorsortieren von Prüfungen hinsichtlich des Attributs MatrNr, sequentielle Verarbeitung von Student, für jeden akzeptierten Datensatz binäres Suchen in der sortierten Tabelle von Prüfungen Vorsortieren beider Tabellen nach dem Attribut MatrNr und Zusammenfügen, danach satzweises Abarbeiten mit Einsatz von Indexen Sequentielles Abarbeiten von Student, für jeden akzeptierten Datensatz direkter Zugriff auf Prüfungen Direkter Zugriff auf akzeptierte Datensätze von Student, danach direkter Zugriff auf Prüfungen ... ... und wenn beide Tabellen nach MatrNr geclustered vorliegen? - 15 - Vorlesung Datenbanken 2 - A. Achilles Regel-basiert Direkter Zugriff auf akzeptierte Datensätze von Student, danach direkter Zugriff auf Prüfungen Je nach Regelsatz bleibt nur dieser Ansatz übrig. Nicht immer optimal! (?) Kosten-basiert ohne Indexe: Sequentielles Abarbeiten von Student, zu jedem akzeptierten Datensatz sequentielles Abarbeiten von Prüfungen ergeben sich aus dem sequentiellen Lesen der Tabelle Student und dem wiederholten sequentiellen Lesen der Tabelle Prüfungen. Dabei geht die Selektivität der Anfrage hinsichtlich der Tabelle Student ein umgekehrt: Abarbeiten von Prüfungen und für jeden akzeptierten Datensatz Abarbeiten von Student Kosten: ? Vorsortieren von Prüfungen hinsichtlich des Attributs MatrNr, sequentielle Verarbeitung von Student, für jeden akzeptierten Datensatz binäres Suchen in der sortierten Tabelle von Prüfungen Kosten: ? Vorsortieren beider Tabellen nach dem Attribut MatrNr und Zusammenfügen, danach satzweises Abarbeiten Kosten: ? mit Einsatz von Indexen Sequentielles Abarbeiten von Student, für jeden akzeptierten Datensatz direkter Zugriff auf Prüfungen ... Kosten: ? Direkter Zugriff auf akzeptierte Datensätze von Student, danach direkter Zugriff auf Prüfungen Kosten: ? ... und wenn beide Tabellen nach MatrNr geclustered vorliegen? - 16 - Vorlesung Datenbanken 2 - A. Achilles Datenbanken - Entwurf Fragestellung: von der "Idee" zur implementierten Datenbank (einschließlich "zentraler Programme/DB-Prozeduren"): wie? es muß sichergestellt werden, daß Datenbasis über langen Zeitraum gestellten Anforderungen genügt Randbedinungen ähnlich dem Entwurf von Programm-Systemen Rückgriff auf Methoden und Erkenntnisse des Software Engineering Vorgehensmodell (4-Phasen-Modell): Analyse konzeptueller Entwurf logischer Entwurf physischer Entwurf Ziel dieser Vorgehensweise möglichst frühe Berücksichtigung von Informations- und Datenverarbeitungsanforderungen möglichst späte Festlegung auf DBMS Hardware und BS sollen erst ganz zum Schluss Eingang finden (vgl. Software-Entwurf und -Entwicklung) Analyse-Phase Abgrenzung des Anwendungsbereichs, Konkretisierung (vgl. Systemanalyse) Daten: Herkunft, Ziel, Volumen ermitteln Prozesse: rechnergestützt, andere Analyse-Phase (detailierter) Datenbank dient der Informationsablage und -gewinnung Informationsbedarf muss detailiert beschrieben werden Unterschiedliche Aspekte müssen in getrennten Verzeichnissen festgehalten werden: Datenverzeichnisse: beschreiben die zu speichernde Fakten Operationsverzeichnisse: beschreiben Verwendung der Daten Ereignisverzeichnisse: beschreiben, welches Ereignis eine Operation auslöst. Daraus ergeben sich die Abläufe. Endanwender-orientiert, hier wird nur schwach formalisiertes Vorgehen vorgestellt basiert z.B. auf Formularen, Arbeitsabläufen und -beschreibungen, Mitarbeiterbefragungen, Dokumenten usw. - 17 - Vorlesung Datenbanken 2 - A. Achilles Schritte 1. Erfassen und Beschreiben der benötigten Daten 2. Beseitigen von Synonymen und Homonyen Redundanzen 3. Erfassung nur implizit vorhandener Information 4. Klassifizierung: Aufteilung und Zuordnung zu Daten/ Operationen/ Ereignisse Datenverzeichnis (FH) Nr. D001 Name Student D002 Professor D003 Prüfung D004 Beschreibung Identifikation MatrNr Klassifikation Person Daten Name, Anschrift, FB, Datum Immatrikulation, - Exmatrikulation Identifikation PersNr Klassifikation Person Daten Name, Anschrift, FB, Raum, Telefon, Lehrgebiet Identifikation PrüfNr Klassifikation Studienleistung Daten Fach, Professor, Datum, Vorleistungen Zusatz-Info Synonyme immatrikulierter Student, aktiver Student ehemaliger Student Kardinalität 150000 Oberbegriff Mitglied Synonyme Dozent Kardinalität 500 Oberbegriff Mitglied Synonyme Fachprüfung, Leistungsnachweis, Projektarbeit, Diplomarbeit, Kolloquium Kardinalität 5000 Oberbegriff Zensur Studentische Identifikation LNr Synonyme Prüfungsleist Klassifikation Studienleistung Kardinalität 50000000 ung Daten Student, Prüfung, Oberbegriff Wiederholung, Note Operationsverzeichnis (FH) Nr. O001 Name Student immatrikulieren O002 Student exmatrikulieren O003 Notenspiegel erstellen O004 Noten eingeben O005 Prüfungsliste erstellen Beschreibung Eingabe Name, Anschrift, FB, Datum Ausgabe eindeutige MatrNr Eingabe Name, Anschrift, Datum Exmatrikulation Ausgabe Eingabe Name, Anschrift Ausgabe Fächer, Noten Zusatz-Info Häufigkeit selten Bezugsdaten D001 DB Einfügen Häufigkeit selten Bezugsdaten D001 DB Suchen, Ändern Häufigkeit mittel Bezugsdaten D001, D002, D003, D004 DB Suchen Häufigkeit häufig Bezugsdaten D001, D003, D004 DB Suchen, Einfügen Eingabe MatrNr, PrüfNr, Wiederholung, Note Ausgabe [endgültig nicht[ bestanden Eingabe Fach, Professor, Datum Häufigkeit häufig Ausgabe Student, Note Bezugsdaten D002, D003, D004 DB Suchen Ereignisverzeichnis (FH) Nr. E001 E002 E003 Name Ankunft Studentenanwärter Eingang Notenliste Anforderung Ergebnisspiegel Bedingung Syntax elementar Semantik konditional (falls) Syntax elementar Semantik konditional Syntax elementar Semantik temporal (wenn) - 18 - löst aus O001 O003, O002 O005 Vorlesung Datenbanken 2 - A. Achilles konzeptuelle Phase vergleichbar mit Spezifikationsphase bei Software-Entwicklung um Sachverhalte und Gesetzmäßkeiten in formale Gestalt überführen Beschreibungsmittel eines semantischen Modells verwenden Ziel: vorgegebene Sachverhalte vollständig und korrekt innerhalb der festgelegten Strukturen des Modells zu beschreiben, dabei wird angestrebt, daß das Ergebnis unabhängig von einem speziellen Datenmodell eines DBMS ist. konzeptuelle Phase -Relationentheorie wünschenswert: Transformation vom semantischen zum logischen Modell automatisieren Wenn relationales Modell auch für semantische Zwecke geeignet ist, kann es als Ausgangs- und Zielpunkt sukzessiver Transformationen genommen werden dazu: Abhängigkeit zwischen Attributen betrachten Gütekriterium bei Transformation: Verringerung der Speicherredundanzen und Anomalien Der Entwurfsansatz kann dann zur Optimierung bestehender Relationenschemata als auch bei Neuentwicklung benutzt werden. Redundanzen und Anomalien Werden zur Verdeutlichung die kompletten Informationen über die Professoren in den Prüfungen aufgeführt: Nr. - Name Professor D003 Prüfung Beschreibung Identifikation PersNr Klassifikation Person Identifikation PrüfNr Klassifikation Studienleistung Daten Fach, Professor, Anschrift, FB, Raum, Telefon, Lehrgebiet, Datum, Vorleistungen Zusatz-Info wird nach D003 verlagert Synonyme Fachprüfung, Leistungsnachweis, Projektarbeit, Diplomarbeit, Kolloquium Kardinalität 5000 Oberbegriff Zensur dann: Ändern sich die Daten eines Professors an vielen Stellen Änderungen notwendig Änderungsanomalie Einfügen von Daten eines neuen Professors neue Prüfung muss angegeben werden, ohne Prüfung nicht möglich! Einfügeanomalie Löschen einer Prüfung, die als einzige Daten eines Professors enthält Informationen über diesen verschwinden ebenfalls Löschanomalie - 19 - Vorlesung Datenbanken 2 - A. Achilles Relationentheorie - Normalisierung Darstellung als Relationen d.h. als Teilmenge aller Tupel des durch die Domänen gegebenen Kreuzproduktes. Domäne: Wertebereich eines Attributs Entwurfsziel: Redundanzen verringern und damit die Anomalien vermeiden Einführung der Normalformen dienen der Elimination der Redudanzen: 1. Normalform Eine Relation ist in 1. NF, wenn alle Attributdomänen ausschließlich atomare Werte besitzen: Zahlen, Zeichenketten, Datumswerte usw. Ausgeschlossen sind damit explizit Listen, Records, Relationen, Mengen, usw. Funktionale Abhängigkeiten (Definitionen): Anmerkung: die folgenden Aussagen gelten zeitunabhängig, d.h. man kann ihre Gültigkeit nicht aus der momentanen Ausprägung einer Relation entnehmen. Definition "funktional abhängig" Gibt es zeitunabhängig eine Abbildung (Funktion) von einer Attributmenge in eine andere Attributemenge einer Funktion, so heißt die zweite Attributmenge funktional abhängig von der ersten Definition „voll funktional abhängig“ Ist eine Attributmenge funktional abhängig von einer Attributmenge, nicht aber von einer Teilmenge dieser Attributmenge, so nennt man diese Abhängigkeit voll funktional abhängig Definition „Schlüssel“ Ein Schlüssel ist eine Attributmenge, von der die Relation voll funktional abhängt diese Attributmenge ist minimal identifizierend für die Relation Eine Relation kann mehrere unterschiedliche Schlüssel haben. 2. Normalform Eine Relation in 1NF ist in 2NF, wenn jedes Nicht-Schlüsselattribut voll funktional von jedem Schlüssel abhängt. Definition „transitiv abhängig“ Eine funktionale Abhängigkeit heißt transitiv abhängig, wenn sie durch 2 nicht-triviale funktionale Abhängigkeiten ersetzt werden kann. - 20 - Vorlesung Datenbanken 2 - A. Achilles 3. Normalform Eine Relation in 2NF ist in 3NF, wenn es kein Nicht-Schlüsselattribut gibt, das transitiv von einem Schlüssel abhängt Anmerkung: die Abhängigkeit im letzten Bild war trivial! Boyce-Codd Normalform Alle Attribute hängen voll funktional von jedem Schlüssel ab Definition „mehrwertige Abhängigkeit“ Spannen die disjunkten Attributmengen A, B, C die Relation R auf, so liegt eine MVD (Multi Valued Dependence = mehrwertige Abhängigkeit) vor, wenn der Zusammenhang zwischen A und C ohne Kenntnis der Werte von B beschrieben werden kann 4. Normalform Eine Relation in 3NF ist in 4NF, wenn es außer funktionalen Abhängigkeiten keine MVDs gibt. Anmerkung 1 Die Normalisierung kann bis zur 4NF schrittweise durch verlustfreie informationserhaltende Zerlegung der jeweiligen Ausgangsrelation in zwei Relationen erfolgen. Anmerkung 2 Das Vorgehen bei der Zerlegung ist robust hinsichtlich der Auswahl der Primärschlüssel, wenn mehrere Schlüsselkandidaten vorliegen. Kleine Abweichungen sind nur zu erwarten bei der Aufstellung von "Verbindungsrelationen". Es kann noch den Fall geben, daß eine verlustfreie Zerlegung nicht in 2, wohl aber in mehr Relationen möglich ist. Dieser Fall wird durch die 5. Normalform beschrieben. Definition: JD (Join Dependency) Eine Relation erfüllt JD bzgl. der disjunkten aufspannenden Attributmengen A, B, C, ..., wenn die Relation durch Join aus den Projektionen R|A, R|B, R|C, ... entsteht. 5. Normalform Eine Relation in 4NF ist in 5NF, wenn alle JDs nur auf Schlüsselkandidaten beruhen. Die Zerlegung kann mit Werkzeugen unterstützt werden. Höhere Normalformen bewirken verringerte Redundanz weniger Anomalien besser hinsichtlich Datenänderungen Aber: in der Regel mehr Tabellen mehr Joins verringerte Effizienz beim Lesen deswegen: Implementation relationaler Datenbanken beendet Normaliserung in vielen Fällen bereits nach der 2. NF Relationentheorie gut, wenn auf den logischen Entwurf abgestimmt, d.h. gut geeignet für relationale Datenbanken Generell wird Semantik nur sehr eingeschränkt unterstützt Relationentheorie wenig geeignet, um konzeptuelle Modellierung weitgehend unabhängig vom Datenmodell eines DBMS durchzuführen - 21 - Vorlesung Datenbanken 2 - A. Achilles E-R-Modell (Entity-Relationship) semantisches Modell, ausschließlich für konzeptuellen Entwurf muß zur Implementierung noch übersetzt werden E-R-Modell ca. 25 Jahre alt entstanden in einer Zeit, als noch keine Objekt-orientierten Techniken diskutiert wurden Vorteil: intuitive Semantik einfache graphische Darstellung anschaulich und gut lesbar Definitionen: Entity (Gegenstand) abstraktes oder physisches Objekt der zu modellierenden Welt Entity Type Zusammenfassung aller Entities gleicher Bedeutung Attribut dienen der Beschreibung und zur eindeutigen Identifizierung von Entities Domäne Wertebereich eines Attributs Relationship beschreibt Zusammenhang zwischen mehreren Entities ggf. mit zusätzlicher Information n-stelliger Relationship-Type kombiniert eine feste Menge von n Entity-Types (nicht notwendig unterschiedliche Entity-Types) Beispiel für graphische Darstellung - 22 - Vorlesung Datenbanken 2 - A. Achilles Beispiel zeigt: Entity-Types mit Attributen Relationship-Types insbesondere 3-stelligen Relationship-Typ Kardinalitäten macht für einen Relationship-Typ eine Aussage über Mindest- und Höchstzahl des Auftretens einer Ausprägung eines Entity-Types Darstellung: <min,max> (max=*: keine Beschränkung) Aggregation (blau) Aggregation, Generalisierung Relationship nur zwischen Entities möglich Aggreation: neuen Zusammenfassung eines Relationship-Types mit seinen Entity-Types zu einem Entity-Type Generalisierung: Zusammenfassung ähnlicher Entity-Types zu einem neuen generischen Entity-Type. für das eingangs betrachtete Beispiel: Professor Student (Mitarbeiter) durch Generalisierung ggf. FH-Angehöriger Weak Entity-Type Entity-Type kann nicht für sich alleine existieren er bedarf (mindestens) eines zugehörigen Relationship-Types Kardinalität: <1,1> Beispiel: Student, Prüfungsleistung Wird Information über Student entfernt, so auch seine Prüfungsleistungen Darstellung - 23 - Vorlesung Datenbanken 2 - A. Achilles Grenzen: Stärke (Einfachheit, nur Entity- und Relationshiptype) = Schwäche mangelnde formale Fundierung Kardinalitätsangaben besonders anfällig für Fehler objektiv ermittelbare Gütekriterien? Objektorientiert Relationentheorie, E-R-Modellierung ausschließlich strukturelle Merkmale, nicht Verhalten, Oeratoren, Ereignisfolgen aber: Datenstrukturen und verarbeitende Algorithmen eng verzahnt einerseits: DB-Einsatz sollte Trennung zwischen Daten und Anwendungsprogrammen betonen, da auf Grund von Langlebigkeit Anwendungsprogramme nie vollständig vorhersehbar andererseits: Leistungsoptimierung erfordert Vorhersagen über Anwendungen, Abfolge von Programmaufrufen, ... OO-Techniken naheliegend 3 Phasen, in der Regel nacheinander ausgeführt Strukturelle Modellierung Beschreibung aller relevanten Eigenschaften Dynamikmodellierung Beschreibung der Objektzustände und der einwirkenden Ereignisse Funktionsmodellierung Beschreibung der Aktivitäten als Ausführung von Operatoren Strukturelle Modellierung Klasse anstelle von Entity-Type Beziehungsdarstellung mittels Kardinalitäten - 24 - Vorlesung Datenbanken 2 - A. Achilles - 25 - Vorlesung Datenbanken 2 - A. Achilles mehrstellige attributierte Assoziation Aggregation (ist Bestandteil von) und Generalisierung (Vererbungsbeziehung) Dynamikmodellierung Zeitveränderliche Aspekte, Kontrollfluß zwischen den Objekten Zustandsübergangsdiagramme: zulässige Zustände und mögliche Übergänge - 26 - Vorlesung Datenbanken 2 - A. Achilles Funktionsmodell basiert auf klassischem Datenflußdiagramm Prozesse Datenspeicher Aktoren Entwurf und Integration Problem: die Zahl der Entity-Types kann bei Entwürfen schnell auf mehrere 100 kommen Übersichtlichkeit nicht mehr gegeben Vorgehensweise: Der konzeptuelle Entwurf wird aufgeteilt Gleiches Vorgehen, wenn bereits existierende Datenbanken zusammengeführt werden müssen im ersten Fall: die zu modellierenden Ausschnitte entstehen koordiniert im zweiten Fall: Ausschnitte sind unkoordiniert entstanden Überlegung: jede Anwendergruppe hat eine ihrer Aufgabenstellung gemäße Sicht der Datenbasis Phase 1: identifiziere die verschiedenen Anwendergruppen entwickle konzeptuelles Modell Phase 2: integriere die so entstandenen Modelle Problem ist das Auffinden der Arbeitsgruppen, ansonsten: Entwicklung der Teilmodelle ist bereits behandelt Phase 2: Zusammenführung der Teilmodelle Welche Probleme können auftreten? Namenskonflikte gleicher Sachverhalt wird mit unterschiedlichen Namen belegt (Synonyme) unterschiedliche Sachverhalte werden mit gleichem Namen belegt (Homonyme) Merkmalskonflikt gleicher Sachverhalt wird in unterschiedlichen Teilmodellen unterschiedlich betrachtet und führt zu unterschiedlich vielen oder nur teilweise überlappenden Satz an Attributen Strukturkonflikt gleicher Sachverhalt wird in den Teilmodellen unterschiedlich modelliert z.B. im E-R-Modell als Attribut-Domänenpaar oder als Relationship mit zweitem Entity-Type Bedingungskonflikt funktionale oder mehrwertige Abhängigkeiten, Schlüsseleigenschaften, Kardinaltitäten... im E-R-Modell z.B. unterschiedliche Kardinalitäten bei Teilmodellen Abstraktionskonflikt gleicher Sachverhalt in unterschiedlichen Teilmodellen unterschiedlich detailliert modelliert - 27 - Vorlesung Datenbanken 2 - A. Achilles Phase 2: Zusammenführungs-Strategie Problem: Komplexitätsreduzierung Problem: Systematik der Anordnung - 28 - Vorlesung Datenbanken 2 - A. Achilles Problem: Systematik der Reihenfolge Bemerkung erster Ansatz scheidet in der Regel aus Komplexitätsgründen aus Ansätze 2 und 3 aus Sicht der Komplexität her möglich liefern aber je nach Zusammenfassung, Reihung unterschiedliche Ergebnisse n-1 gleichartige Konsolidierungsschritte jeder Schritt: Konfliktanalyse beide Sichten vergleichen Konfliktbereinigung für jeden ausgemachten Konflikt: eine Sicht "gibt nach" z.B: bei Abstraktionskonflikt diejenige mit geringerem Detailierungsgrad Sichtenverbindung zu einem Schema zusammenführen: identische Teile nur einmal erfassen disjunkte Teile vollständig übernehmen bei Abstraktionskonflikten: einpassen Physischer Entwurf (bei RDBMS) prinzipiell gilt: die Kosten durch den physischen Zugriff sind zu minimieren dies kann bei RDBMS durch folgenden Ansatz geschehen: Festlegung von Speicherbereichen und Zuordnung zu Plattenpartitionen Vereinbarung von Tabellenbereichen zur Aufnahme einer oder ggf. mehrerer gleichartig zu behandelnder Relationen Anzugeben sind Speicherbereich Anfangsgröße - 29 - Vorlesung Datenbanken 2 - A. Achilles erwartete Wachstumsrate Seitengröße Zuordnung von Relationen zu Tabellenbereichen Clusterung Erzeugen von Indexen manche werden automatisch durch das verwendete RDBMS angelegt, ggf. ist noch der Speicherbereich anzugeben Clusterindex Primärindex Sekundärindexe zur Unterstützung von Fremdschlüsseln weitere Indexe Installation des gewählten DBMS Anpassung der Installationsparameter an die Anforderungen dies betrifft unter anderem Directory-Größe Größe für temporäre Tabellenbereiche (Sortieren usw.) im Speicher zu reservierender Bereich für DBMS Cache für Datenseiten Cache für DB-Kommandos und -Zugriffspfade Bereitstellen der Sichten Benutzerverwaltung Rechtevergabe Implementation der Integritätsregeln Implementation der DB-Prozeduren - 30 - Vorlesung Datenbanken 2 - A. Achilles Relationale DBMS SQL Standard SQL92 mit Erweiterungen Vorgänger: SQL86, SQL89 derzeit: SQL92 in drei Leveln, um Herstellern Übergang zu erleichtern (mit späteren Erweiterungen) Entry-Level notwendig für SQL92-Kompatibilität NIST-Institute überwachte die Konformität zu diesem Level Intermediate-Level Full Level Erweiterungen CLI (Call Level Interface) - 1995 PSM (Persistent Stored Modules) - 1996 SQL Standard Sprache besteht aus Definitionssprache Datenretrieval Datenmanipulation Kontrolle Sprachanbindungen Module Embedded SQL CLI JDBC - 31 - Vorlesung Datenbanken 2 - A. Achilles Sprachelemente Datentypen, Konstanten, NULL vordefinierte Variable CURRENT_DATE usw. CURRENT_USER usw. skalare Funktionen numerische Funktionen POSTION, EXTRACT, CHARACTER_LENGTH, OCTET_LENGTH, BIT_LENGTH Zeichenkettenfunktionen CONVERT, LOWER, SUBSTRING, TRANSLATE, TRIM, COLLATE, UPPER, || CAST CASE mit NULLIF, COALLESCE Spaltenfunktionen AVG, COUNT, MAX, MIN, SUM Zeilenkonstruktor (Row Value Constructor) ausdruck oder (ausdruck1[, ausdruck2[,...]]) oder (subquery) Tabellenkonstruktor Ausdrücke skalare Ausdrücke Tabellenausdrücke Join-Tabellenausdruck Tabellenname geklammerter allgemeiner Tabellenausdruck Prädikate einfache Prädikate VALUES zk1[, zk2[,...]] BETWEEN, EXISTS, IN, IS NULL, LIKE, MATCH, OVERLAPS, UNIQUE quantifizierte Prädikate z.B: >= ALL, >= ANY, >= SOME gefolgt von einem Subselect Datenbankstruktur SQL-Umgebung: DBMS- Instanz zusammen mit allen Datenbanken, auf die diese Instanz zugreifen kann angelegten Benutzern Programmen Katalog enthält eine Menge von SQL-Schemata, SQL-Schema ist jeweils einem Benutzer zugeordnet, aber Benutzer kann mehrere Schemata besitzen Schema enthält eine Reihe von realen Einträgen wie z.B. Tabellen, Views, etc. Katalog: eine Menge logisch zusammengehörender Tabellen, Schema: ein spezieller Ausschnitt daraus Implementation eines Kataloges: DBMS-Hersteller jedoch: Standard fordert pro Katalog genau ein spezielles Schema mit dem Namen INFORMATION_SCHEMA "Dictionary" - Metainformation aller in den Schemata gespeicherten Objekte - 32 - Vorlesung Datenbanken 2 - A. Achilles INFORMATION_SCHEMA INFORMATON_SCHEMA_CATALOG_NAME SCHEMATA DOMAINS TABLES VIEWS COLUMNS TABLE_PRIVILEGES COLUMN_PRIVILEGES USAGE_PRIVILEGES DOMAIN_CONSTRAINTS KEY_COLUMN_USAGE ASSERTIONS CHARACTER_SETS COLLATIONS TRANSLATIONS VIEW_TABLE_USAGE VIEW_COLUMN_USAGE CONSTRAINT_TABLE_USAGE CONSTRAINT_COLUMN_USAGE COLUMN_DOMAIN_USAGE SQL_LAGUAGES INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY SQL_IDENTIFIER CHARACTER_DOMAIN CARDINAL_NUMBERS (Assertion) (Domain) (Domain) (Domain) Benennung (voll-qualifiziert): katalog_name.schema_name.objekt_name katalog_name.schema_name.objekt_name.spalten_name Da mit SQL-Environment und -Sitzung Katalog und Schema verbunden sind, werden diese ergänzt, wenn der Name nicht voll-qualifiziert ist. SQL-Sitzung Verbindung: Ende mit CONNECT DISCONNECT innerhalb eine Reihe von Transaktionen Eine Anwendung kann nacheinander mehrere Verbindungen - und damit mehrere Sitzungen - aufbauen; die jeweils vorhergehende Sitzung wird damit vorübergehend "in Ruhe versetzt" CONNECT TO ... SET CONNECTION ... Zeichensätze eigene Zeichensätze können vereinbart werden alphabetische Sortierreihenfolge kann vereinbart werden Übersetzungsvorschrift kann vereinbart werden CREATE CHARACTER SET CREATE COLLATION CREATE TRANSLATION - 33 - Vorlesung Datenbanken 2 - A. Achilles Constraints Einschränkungen, die erfüllt sein müssen, damit Daten in Tabellen eingetragen werden können Constraints (benannt oder unbenannt) sind möglich für Schema (nur unbenannt) Domain Tabelle Tabellenspalte Constraints können sofort oder erst am Ende einer Transaktion (verzögert) überprüft werden Sie lauten somit (innerhalb der jeweiligen CREATE Anweisung): [CONSTRAINT name] einschränkung [[INITIALLY DEFERRED | INITIALLY IMMEDIATE] [[NOT] DEFERRABLE] ] Constraints (Einschränkung) CHECK (prädikat) wirkt, als würde entsprechend die Anweisung SELECT * FROM tables WHERE NOT (prädikat) ausgeführt. Enthält diese Ergbnistabelle Zeilen, so wäre die CHECK-Einschränkung verletzt. Constraints (Einschränkung) in einer Tabellendefinition PRIMARY KEY UNIQUE ( ... ) ( ... ) FOREIGN KEY ( ... ) REFERENCES tabelle [((...)] [ MATCH {FULL|PARTIAL} ] [ ON UPDATE {CASCADE|SET NULL|SET DEFAULT|NO ACTION} ] [ ON DELETE {CASCADE|SET NULL|SET DEFAULT|NO ACTION} ] Primärschlüssel, Eindeutigkeit und Fremdschlüssel-Vereinbarungen Constraints (Einschränkung) in einer Spaltendefinition NOT NULL PRIMARY KEY UNIQUE REFERENCES tabelle [((...)] [ MATCH {FULL|PARTIAL} ] [ ON UPDATE {CASCADE|SET NULL|SET DEFAULT|NO ACTION} ] [ ON DELETE {CASCADE|SET NULL|SET DEFAULT|NO ACTION} ] Unzulässigkeit von NULL-Werten, Primärschlüssel, Eindeutigkeit und Fremdschlüssel-Vereinbarung DDL - Data Definition Language CREATE SCHEMA ... DEFAULT CHARACTER SET CREATE DOMAIN CREATE TABLE CREATE VIEW CREATE ASSERTION CREATE CHARACTER SET CREATE COLLATION CREATE TRANSLATION GRANT ... ... ... ... ... ... ... ... ... - 34 - Vorlesung Datenbanken 2 - A. Achilles temporäre Tabellen CREATE TABLE {GLOBAL|LOCAL} TEMPORARY name ... [ ON COMMIT {DELETE|PRESERVE} ROWS ] Nicht persistente Daten, die im Laufe einer Transaktion zur Verfügung stehen müssen, können auf diese Weise strukturiert werden. Besonderheiten: Gültigkeitsbereich Aufbewahrungsdauer Foreign Key Check Views Gründe Information kann auf Sicht des jeweiligen Benutzers angepasst werden Basis-Tabellen können unabhängig von Benutzersicht strukturell verändert werden Datenschutz CREATE VIEW AS SELECT ... ... [ WITH [CASCADED|LOCAL] CHECK OPTION ] ORDER BY nicht erlaubt rekursive Definition ausgeschlossen Überprüfung der VIEW-Definition Datenänderung durch Views: änderbare VIEWs Domains CREATE DOMAIN ... [ DEFAULT ... ] [ constraint ] [ COLLATE .... ] permamente Tabellen (Basis-Tabellen) CREATE TABLE ... ( spalte ... [ spalten_constraint ] [ DEFAULT ... ] [ COLLATE ... ], ... [ tabellen_constraint, ... ] ) Assertions (An Schemata gebundene Einschränkungen) CREATE ASSERTION ... CHECK ( prädikat ) [ [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] [ [NOT] DEFERRABLE ] Im Gegensatz zu Tabellen-Einschränkungen gelten diese Einschrängungen generell Tabellen-Einschränkungen werden nur überprüft, wenn eine Datenänderung vorgenommen wird, die diese Tabelle betrifft typische Erweiterungen CREATE CREATE CREATE CREATE CREATE FUNCTION INDEX PROCEDURE TABLESPACE TRIGGER - 35 - Vorlesung Datenbanken 2 - A. Achilles typische Erweiterungen Ergänzungen der Standard-Anweisungen um Angaben, die den Speicherort festlegen das Logging beeinflussen Clustern/Partitionieren unterstützen Art der Replikation beeinflussen ... typische Erweiterung: Trigger CREATE TRIGGER ... { BEFORE | AFTER | INSTEAD OF } { INSERT | DELETE | UPDATE } ON ... [ REFERENCING [ OLD AS ref_old ] [ NEW AS ref_new ] ] { FOR EACH ROW | FOR EACH STATEMENT } [ WHEN ( bedingung ) ] trigger_aktion Rechte GRANT { ALL PRIVILEGES | SELECT | DELETE | INSERT [( ... )] | UPDATE [( ... )] | REFERENCES [( ... )] } ON TABLE ... TO ... | PUBLIC [ WITH GRANT OPTION ] Für Datenbankobjekte wie DOMAIN, COLLATION usw. lautet die Rechtevergabe GRANT USAGE ON ... TO ... | PUBLIC [ WITH GRANT OPTION ] REVOKE [ GRANT OPTION FOR ] ... ON ... FROM ... | PUBLIC { CASCADE | RESTRICT } GRANT OPTION FOR entzieht die GRANT OPTION CASCADE löscht auch abhängige Objekte - 36 - Vorlesung Datenbanken 2 - A. Achilles Veränderung des SQL-Environments SET CONNECTION ... SET CONSTRAINTS ... { DERFERRED | IMMEDIATE } SET TRANSACTION { ISOLOATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZE } | { READ ONLY | READ WRITE } | ... } SET CONNECTION dient dazu, die aktive Verbindung zu wechseln SET CONSTRAINTS verändert den Zeitpunkt, zu dem die Prüfung durchgeführt wird SET TRANSATION beeinflußt die Performance (Nebenläufigkeit von Transaktionen) sowie die gegenseitige Beeinflussung: volle Serialisierbarkeit: SERIALIZE wenn erlaubt ist, dass bei erneutem (gleichen) SELECT innerhalb einer Transaktion auch Datensätze erscheinen können, die von anderen Transaktionen inzwischen eingefügt wurden: REPEATABLE READ (Zeilen, die gelesen wurden, bleiben unverändert) wenn bei erneutem gleichen SELECT innerhalb einer Transaktion auch die gelesenen Zeilen von einer anderen Transaktion geändert werden dürfen: READ COMMITTED wenn auch Zeilen gelesen werden dürfen, die von anderen Transaktionen noch nicht freigegeben wurden: READ UNCOMMITTED DML Datenretrieval SELECT FROM WHERE GROUP BY HAVING .... .... .... .... .... JOIN in der FROM-Klausel: tabelle1 JOIN tabelle2 ON tabelle1 USING (spalte) tabelle1 NATURAL JOIN ON tabelle2 und weitere Tabellenausdrücke Unteranfragen - ungebunden: in einer Subquery wird eine Ergebnismenge ermittelt und ein Vergleich mit einem Record (ggf. mit Allquantor) durchgeführt Unteranfrage - gebunden: durch Einführung einer Tabellenreferenz und Benutzung der Referenz in der Subquery wird die Subquery jeweils für die gerade betrachtete Zeile berechnet Verbindung von SELECT-Anfragen: SELECT .... FROM .... WHERE .... .... UNION | INTERSECT | EXCEPT SELECT .... FROM .... WHERE .... .... Geordnete SELECT-Anfragen: SELECT-Anweisung ORDER BY .... - 37 - Vorlesung Datenbanken 2 - A. Achilles Problem mit NULL: Vergleich zwischen SUM(spalte)/COUNT(*) und AVG(spalte) Einfügen INSERT INTO tabelle [ (spalte1 ,... ) ] { SELECT-Anweisung | Tabellen-Konstruktor } Löschen DELETE FROM tabelle [ WHERE CURRENT OF cursor | WHERE Suchbedingung ] Ändern UPDATE tabelle SET spaltei = { ausdrucki | NULL | DEFAULT } .... [ WHERE CURRENT OF cursor | WHERE Suchbedingung ] DCL - Datenkontrolle Transaktionen COMMIT ROLLBACK Erweiterungen: SAVEPOINT save ROLLBACK SAVEPOINT save LOCK TABLE tabelle IN {SHARE|EXCLUSIVE} MODE - 38 - Vorlesung Datenbanken 2 - A. Achilles Schnittstellen - Überblick Spracherweiterungen haben sich nicht durchgesetzt Module-Sprache: insbesondere als Definitionsgrundlage für SQL92 Konzept Trennung zwischen SQL und Programmiersprache, wohldefinierter Übergabemechanismus zwischen Programm und Module aber: Implementation völlig von DBMS-Hersteller abhängig embedded SQL Konzept Einbetten der SQL-Anweisungen in den Programm-Quelltext Verwendung eines Präkompilers aber: Probleme bei der „Vermischung“ von Einbettung und Programmiersprache Call-Level-Interface wichtiger Standard Trennung zwischen SQL und Programmiersprache, wohldefinierter Übergabemechanismus zwischen Programm und CLI-Routine Implementation weitgehend Datenbank-unabhängig JDBC wichtiger (kommender) Standard, insbesondere für DB-Prozeduren, Web-Anbindungen Grundlagen Cursor-Konzept Status und Fehlererkennung NULL-Wert und Indikator-Variablen Schnittstellen - Cursor-Konzept SELECT-Anweisung ergibt eine Ergebnistabelle Prozedurale Programmierung ist auf satzweise Verarbeitung abgestellt SELECT ... INTO :arg1, ... FROM ... WHERE ... erlaubt, wenn höchstens eine Zeile ermittelt wird Problem: Lösung des allgemeinen Problems? dient dazu, eine Ergebnismenge in eine Folge einzeln einlesbarer Datensätze aufzulösen DECLARE cname [INSENSITIVE] [SCROLL] CURSOR FOR { SELECT-Anweisung | prepared-Anweisung } der Cursor-Name ist keine Variable, sondern eine statische Referenz auch der Kursor kann "dynamisch" behandelt werden Öffnen eines Cursors Zeitpunkt, zu dem die SELECT-Anweisung ausgewertet wird OPEN cname [ USING ...] Position des Cursors: vor der ersten Ergebniszeile USING ... dient zur Übergabe von Hostvariablen bei prepared Anweisungen Schließen eines Cursors CLOSE cname - 39 - Vorlesung Datenbanken 2 - A. Achilles Lesen eines Datensatzes mittels Cursor Cursor wird auf den nächsten Satz der Ergebnismenge geschoben FETCH [ ABOLUTE n | FIRST | LAST | NEXT | PRIOR | RELATIVE n ] FROM cname INTO { Liste von Hostvariablen | SQL DESCRIPTOR dname } Nutzung eines Cursors Problem: Erkennung des Endes Schnittstellen - Status Jede SQL-Anweisung erzeugt eine Status-Information Normierung in SQL92: Variable SQLSTATE 5 Byte, aufgeteilt in 2 Byte "Klasse" und 3 Byte "Unterklasse" wichtige Klassen: Klasse 00 01 02 08 23 27 Bedeutung erfolgreiche Ausführung Warning Daten nicht vorhanden Connection error Constraint violation Triggered data change violation - 40 - Vorlesung Datenbanken 2 - A. Achilles Schnittstellen - Indikator-Variable NULL-Wert ist nicht Bestandteil der üblichen Programmiersprachen Konzept zur Behandlung von NULL wird benötigt Zusammen mit einer Hostvariablen (vgl. Module-Sprache, embedded SQL) kann eine Indikatorvariable benutzt werden (kein Komma zwischen Host- und zugehöriger Indikatorvariable): wird aus der Datenbank gelesen, so besagen die Werte der Indikator-Variablen nach Ausführung der SQL-Anweisung = 0 Übertragung korrekt > 0 bei Übertragung wurden Stellen abgeschnitten Anzahl der abgeschnittenen Stellen = Wert der Indikatorvariable < 0 NULL-Wert wurde übertragen wird in die Datenbank geschrieben, so gilt: wird vor Ausführung Wert < 0 gesetzt: NULL-Wert wird in Datenbank geschrieben nach Ausführung werden die Werte = 0 und > 0 wie oben interpretiert Schnittstellen - Module-Schnittstelle Aufruf von Modulen dadurch Trennung von Host-Sprache und SQL Module sind in eigener "Sprache" geschrieben pro Module eine SQL-Anweisung Argumentübergabe möglich Vorteile: Module und Programm können getrennt optimiert werden kein Precompiler kein Konflikt zwischen SELECT (Programmiersprache) und SELECT (SQL) Übersetzung und Anbindung der Module aber nicht geregelt, DBMS-Hersteller-abhängig Beispiel MODULE beispiel LANGUAGE PLI SCHEMA demo DECLARE cursor_stud_pruefungen CURSOR FOR SELECT vname, semester, note FROM pruefungen WHERE matrnr = :matrstud; PROCEDURE commit (SQLSTATE); COMMIT WORK; PROCEDURE rollback (SQLSTATE); ROLLBACK WORK; PROCEDURE open_cursor_stud_pruefungen ( SQLSTATE, :matrstud CHAR(7) ); OPEN open_cursor_stud_pruefungen; PROCEDURE close_cursor_stud_pruefungen ( SQLSTATE); CLOSE open_cursor_stud_pruefungen; PROCEDURE lies_cursor_stud_pruefungen ( SQLSTATE, :vorlesname CHAR(20) :semester CHAR(11), :note CHAR(3), :i_note INTEGER); FETCH NEXT FROM cursor_stud_pruefungen INTO :vorlesname, :semester, :note INDICATOR :i_note; - 41 - Vorlesung Datenbanken 2 - A. Achilles Beispiel für Programm DCL sqlstate CHAR( 5); DCL student CHAR( 7); DCL vorlesung Char(20); DCL semester CHAR(11); DCL note CHAR( 3); DCL inote BIN FIXED; ON CONDITION (SQLEXCEPTION) BEGIN ... END; ... student = '4711'; CALL open open_cursor_stud_pruefungen(sqlstate, student); IF SUBSTR(sqlstate, 1, 2) > '02' THEN SIGNAL SQLEXCEPTION; DO WHILE ('1'b); CALL lies_cursor_stud_pruefungen(sqlstate, vorlesung, semester, note, inote); IF SUBSTR(sqlstate, 1, 2) > '02' THEN SIGNAL SQLEXCEPTION; IF SUBSTR(sqlstate, 1, 2) = '02' THEN LEAVE; IF SUBSTR(sqlstate, 1, 2) > '00' THEN PUT ('Warnung beim Lesen'); ELSE DO; IF itext < 0 THEN text='???'; PUT LIST (vorlesung, semester, note); END; END; CALL close_cursor_stud_pruefungen; ... Schnittstellen - Embedded SQL SQL-Anweisungen werden in den Quelltext eingebettet: EXEC SQL SQL_Anweisung; konkretes Beispiel: EXEC SQL INSERT INTO pruefungen VALUES (:student, :vorlesung, :semester, :note:inote) ; Einleitung und Ende der eingebetteten Anweisung ergeben sich aus der gewählten Programmiersprache - 42 - Vorlesung Datenbanken 2 - A. Achilles Beispiel EXEC SQL BEGIN DECLARE SECTION; DCL sqlstate CHAR( 5); DCL student CHAR( 7); DCL vorlesung Char(20); DCL semester CHAR(11); DCL note CHAR( 3); DCL inote BIN FIXED; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cursor_stud_pruefungen CURSOR FOR SELECT vname, semester, note FROM pruefungen WHERE matrnr = :student; ... student = '4711'; EXEC SQL WHENEVER NOT FOUND GOTO ENDE; EXEC SQL OPEN open_cursor_stud_pruefungen; DO WHILE ('1'b); EXEC SQL FETCH cursor_stud_pruefungen INTO :vorlesung, :semester : note:inote; IF SUBSTR(sqlstate, 1, 2) > '00' THEN PUT ('Warnung beim Lesen'); ELSE DO; IF itext < 0 THEN text='???'; PUT LIST (vorlesung, semester, note); END; END; ENDE: EXEC SQL CLOSE cursor_stud_pruefungen; ... Precompiler übersetzt eingebettete Anweisungen in Statements der jeweiligen Programmiersprache arbeitet vor Übersetzungszeit keine Kenntnis von Information zur Zeit der Ausführung arbeitet rein sequentiell prüft zusammen mit DBMS für jede eingebettete Anweisung: ist Anweisung syntaktisch korrekt sind alle referenzierten Objekte in DB vorhanden sind Zugriffsrechte gegeben nach erfolgreicher Prüfung: ersetzt Anweisung durch Quelltext und benötigte Datenstrukturen ggf. ermitteln "optimalen" Zugriffspfad Ausnahmebedingungen EXEC SQL WHENEVER NOT FOUND | SQLERROR GOTO label | CONTINUE - 43 - Vorlesung Datenbanken 2 - A. Achilles Statische SQL-Anweisungen alle Informationen sind bereits zum Zeitpunkt der Programmierung bekannt: Namen der Objekte Struktur der Manipulationsanweisung Struktur der WHERE-Klausel verwendete Host-Variablen ... Offen bleiben darf nur der Wert der in Ausdrücken verwendeten Host-Variablen Vorteile je nach DBMS kann der Zugriffspfad ermittelt und in der DB gespeichert werden damit entfällt das Berechnen während der Ausführungsphase einzig Rechte müssen noch überprüft werden Oracle schaut einem Cache nach, ob SQL-Anweisung bereits bekannt. Schreibweise muß präzise übereinstimmen dann wird zugehörige Ausführungsform aus dem Cache übernommen Dynamische SQL-Anweisungen nicht in jedem Fall ist die SQL-Anweisung bereits vor der Compilationsphase bekannt Typisch z.B. für interaktive Oberflächen, die auf DB zugreifen Anweisung wird erst zur Laufzeit des Programmes ermittelt es muß Möglichkeiten geben, eine Zeichenkette an das DBMS zu übergeben, die zur Laufzeit ausgewertet und als SQL-Anweisung ausgeführt wird. Problem einmaliger Zugriff oder wiederholter Zugriff? Bei wiederholtem Zugriff mit Hilfe der ermittelten Anweisung müßte jeweils die Analyse-Phase erneut durchlaufen werden Performance! einmaliger Zugriff EXEC SQL BEGIN DECLARE SECTION; DCL zeichenkette CHAR(200); EXEC SQL END DECLARE SECTION; zeichenkette = 'CREATE TABLE KUNDEN (' || ' KNUMMER SMALLINT PRIMARY KEY,' || ' KNAME CHARACTER(20) NOT NULL, ' | ' KTEXT CHARACTER(50) )' ; EXEC SQL EXECUTE IMMEDIATE :zeichenkette ; mehrfacher Zugriff EXEC SQL BEGIN DECLARE SECTION; DCL zeichenkette CHAR(200); EXEC SQL END DECLARE SECTION; zeichenkette = 'INSERT INTO KUNDEN ' || ' VALUES( ?, ?, ? ) ' EXEC SQL PREPARE prep_anweisung FROM :zeichenkette ; - 44 - Vorlesung Datenbanken 2 - A. Achilles Ausführung EXEC SQL EXECUTE prep_anweisung USING :var1, :var2, :var3 ; Anmerkungen prep_anweisung ist ein Bezeichner, keine Host-Variable! :zeichenkette muß gültige SQL-Syntax besitzen es sind nur positionale Parameter erlaubt keine Kommentare, keine Host-Variablen erlaubte DML-Anweisungen: INSERT, DELETE, UPDATE sowie single SELECT alle DDL-Anweisungen DCL-Anweisungen: COMMIT, ROLLBACK an Stelle von USING ... kann auch allgemeiner ein SQL-Descriptor verwendet werden. Dies wird insbesondere bei dynamischen SELECT-Anweisungen benötigt: EXEC SQL ALLOCATE DESCRIPTOR demo_descriptor ; // Initialisieren des Descriptors EXEC SQL DESCRIBE prep_anweisung USING SQL DESCRIPTOR demo_descriptor ; // Zugriff: EXEC SQL GET DESCRIPTOR demo_descriptor :anzahl = COUNT ; EXEC SQL GET DESCRIPTOR demo_descriptor VALUE :i :var1= INFO, ... die wichtigsten Eigenschaften sind NAME, UNNAMED, TYPE, LENGTH, DATA, PRECISION; SCALE, INDICATOR Dynamische SQL-Anweisungen: SELECT sql_frage = 'SELECT KNUMMER, KNAME FROM KUNDEN WHERE KNAME LIKE ?' ; EXEC SQL PREPARE prep FROM :sql_frage; EXEC SQL ALLOCATE curs CURSOR FOR prep; EXEC SQL ALLOCATE DESCRIPTOR d_in ; EXEC SQL ALLOCATE DESCRIPTOR d_out; EXEC SQL DESCRIBE INPUT prep USING SQL DESCRIPTOR d_in; EXEC SQL DESCRIBE OUTPUT prep USING SQL DESCRIPTOR d_out; EXEC SQL SET DESCRIPTOR d_in VALUE 1 DATA=:string; EXEC SQL OPEN curs USING SQL DESCRIPTOR d_in; // Schleife zum Auslesen ... EXEC SQL FETCH curs INTO SQL DESCRIPTOR d_out; Schnittstellen - CLI Sprach- und DBMS-unabhängige Schnittstelle von Prozeduraufrufen keine Inkonsistenzen zwischen Hostsprache und SQL Übersetzung der SQL-Anweisungen zur Laufzeit dazu wird nur das DBMS benötigt Bibliotheken sind sprach- und DBMS-unabhängig => Software-Firmen können Anwendungen im Object-Code weitergeben Zugriff auf mehr als ein DBMS möglich Debugging wird erleichtert Nachteile gegenüber Embedded SQL: durch Binden lassen sich bei Embedded SQL weitere Sicherheitsstufen einbauen bei statischem SQL sind unter Umständen durch Voroptimierung effizientere Programme möglich, da bei CLI jede SQL-Anweisung erst vom DBMS übersetzt und optimiert werden muß. - 45 - Vorlesung Datenbanken 2 - A. Achilles Typische Verarbeitung: Initialisierung Transaktionsverarbeitung Terminierung Initialisierung /* Verbindungsaufbau es folgen einige "Handles" */ SQLHENV henv; SQLHDBC hdbc; SQLCHAR *server; SQLCHAR *user; SQLCHAR *password; SQLHSTMT hstmt; SQLCHAR stmt[] = "INSERT INTO student VALUES (?, ?, ?); SQLHSTMT hstmt; SQL_CHAR matrnr[7] = "1111111"; SQL_CHAR name[10] = "Mustermann"; SQL_CHAR adresse[10] = "Irgendwo 1"; /* SQL-Umgebung bereitstellen */ rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ); /* Verbindungshandle anlegen */ rc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ); /* Verbindungsaufbau */ if (SQLConnect( hdbc, server, SQL_NTS, user, SQL_NTS, password, SQL_NTS ) != SQL_SUCCESS ) return ( print_err(...) ); Transaktionsverarbeitung /* rc rc // /* rc Transaktionsverarbeitung */ = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ); = SQLPrepare( hstmt, stmt, SQL_NTS ); da jede Anweisung dynamisch zur Parameterübergabe: */ = SQLGetStmrAttr( hstmt, SQL_ATTR_APP_PARAM_DESC, &hdesca, 0L, (SQLINTEGER *)NULL ); rc = SQLGetStmtAttr( hstmt, SQL_ATTR_IMP_PARAM_DESC, &hdesci, 0L; (SQLINTEGER *)NULL ); rc = SQLSetDescRec( hdesca, 1, SQL_C_CHAR, ... 7, ... (SQLPOINTER)matrnr), ...); rc = SQLSetDescRec( hdesci, 1, SQL_CHAR, ... 7, ...); ... rc = SQLExecute( hstmt ); rc = SQLEndTran( henv, SQL_COMMIT ); rc = SQLFreeStmt( hstmt, SQL_DROP ); Terminierung /* rc rc rc Terminierung */ = SQLDisconnect( hdbc ); = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ); = SQLFreeHandle( SQL_HANDLE_ENV, henv ); - 46 - Vorlesung Datenbanken 2 - A. Achilles Innerhalb der Transaktionsverarbeitung: Statement-Handle bereitstellen: SQLAllocHandle() direkte Ausführung: SQLExecDirect(...) mehrfache Ausführung: Vorbereiten: SQLPrepare() Parameter übergeben: SQLSetDescRec() Ausführen: SQLExecute() Ergebnisse bearbeiten: Zeilenstruktur erkennen und Variablen an Zeilenstruktur binden Struktur beschreiben: SQLDescribeCol() Binden: SQLBindCol() Zeile holen: SQLFetch() Daten auslesen: SQLGetData() wurden Daten verändert: Bestimmung der Anzahl veränderter Zeilen: SQLRowCount() Transaktion beenden: SQLEndTran() Statement-Handle freigeben: SQLFreeHandle() Schnittstellen - Java Eigenschaften von Java Sprache ist objektorientiert weitestgehend unabhängig von BS Vernetzung wird direkt unterstützt weitgehede Sicherheit eingebaut JDBC: Schnittstelle zu DBMS normiert, beruht auf CLI und SQL/92 Entry-Level Inzwischen bieten namhafte DBMS-Hersteller die Möglichkeit an, Java-DBMS-Prozeduren einzubinden JDBC besteht aus Klassen und Interfaces. Die wichtigsten sind: java.sql.DriverManager dient zum Laden eines Treibers und zum Aufbau einer DB-Verbindung java.sql.Connection stellt eine DBMS-Verbindung dar java.sql.Statement dient dazu, SQL-Anweisungen auszuführen java.sql.ResultSet erlaubt es, Ergebniszeilen auszuwerten Java-Anbindungen an DBMS 1. JDBC-ODBC-Schnittstelle: erste funktionierende Anbindungsart, setzt JDBC-Aufrufe in ODBCAufrufe um. Wenig performant, ODBC-Treiber muss auf derselben Maschine installiert sein. 2. Java-DBMS-spezifisches API: auch hier muss Umsetzung von JDBC-Aufrufe in Herstellerspezifische DBMS-API (Java-fremder Code) vorgenommen werden. 3. JDBC-Netz mit pure Java Treiber: JDBC-Aufrufe werden in Netzwerk-Protokoll übersetzt, das DBMS-unabhängig ist. Java-Server nimmt Aufrufe entgegen und kommuniziert mit Ziel-DB. 4. Natives Protokoll mit pure Java Treiber: Treiber wandelt die JDBC-Aufrufe direkt in das vom DBMS verwendete Netzwerk-Protokoll um. Geringerer Kommunikationsaufwand als 3, aber stärkere Hersteller-Bindung Middleware: EJB-Technologie ermöglicht es, mehrere DBMS-Server einzubinden, unterstützt Skalierbarkeit, erzwingt Anwendung von Geschäftslogik - 47 - Vorlesung Datenbanken 2 - A. Achilles Verbindungsaufbau try { Class.forName( "jdbc.Treibername" );// Laden des Treibers Connection conn = DriverManager.getConnection( url, "userid", "password" ); } catch (java.lang.ClassNotFoundException e) { ... } catch (java.sql.SQLWarning e) { ... } catch (java.sql.SQLException e) { ... } beim Laden eines Treibers wird der statische Initialisierer aufgerufen dieser registriert "sich" beim DriverManager damit kann der DriverManager bei Verbindungsanfragen auf den Treiber zugreifen die Klassen-Methode getConnection der Klasse DriverManager dient zum Aufbau der Verbindung zu einer Datenbank: das erste Argument spezifiziert Treibertyp und Datenbank, z.B.: jdbc:odbc:DemoDB URL-Darstellung, immer mit jdbc beginnend; in diesem Falle eine JDBC-ODBC-Bridge. DemoDB ist der Name der Datenbank das zweite Argument ist die Benutzerkennung das dritte Argument das zu dieser Benutzerkennung gehörige Passwort Achtung: Voreinstellung für autoCommit ist true (vgl.: Java-Transaktionen) das Objekt conn vom Typ Connection besitzt eine Implementation der Schnittstelle Connection damit kann es Statement-Objekte erzeugen, um SQL-Anweisungen an die Datenbank zu übergeben PreparedStatement-Objekte erzeugen, um SQL-Anweisungen aufzubereiten und wiederholt parametrisiert auszuführen CallableStatement-Objekt erzeugen, um Stored Procedures aufzurufen mit der Methode natievSQL( String sqlAnweisung ) die aus der JDBC-sqlAnweisung erzeugte native SQL-Anweisung anzeigen, die an das DBMS übermittelt wird Metadaten über die Verbindung abfragen, Informationen über unterstützte SQL-Grammatik, Stored Procedures, Tabellen, usw. die Verbindung schließen ( close() ) geschieht automatisch, wenn Gabage Collector das Verbindungsobjekt löscht nur lesende Zugriffe erlauben Transaktionen unterstützen entweder im autoCommit-Modus oder direkt durch commit() und rollback() den Isolationsgrad durch setTransactionIsolation(..) setzen (vgl. SET TRANSACTION) Ausführen von SQL-Anweisungen innerhalb eines Connection-Objektes stehen die Methoden createStatement() erzeugt ein Statement-Objekt prepareStatement( String sqlAnweisung ) erzeugt ein PreparedStatement-Objekt prepareCall( String sqlAnweisung ) erzeugt ein CallableStatement-Objekt bereit, um die Voraussetzungen zu schaffen, SQL-Anweisungen zu bearbeiten Ausführen von SQL-Anweisungen Statement-Interface int anzahl; Statement stmnt = conn.createStatement(); anzahl = stmnt.executeUpdate( "CREATE TABLE test " + "(NUMMER INTEGER, NAME VARCHAR(20)) " ); anzahl = stmnt.executeUpdate( "INSERT INTO test " + "VALUES( 1, 'Demozeile' )" ); ResultSet rs = stmnt.executeQuery( "SELECT * FROM test" ); ... stmnt.close(); - 48 - Vorlesung Datenbanken 2 - A. Achilles Das Interface enthält als wichtigste Methoden executeUpdate( String sqlAnweisung ) diese dient dazu, eine SQL-INSERT-, UPDATE- oder DELETE-Anweisung oder SQL-DDLAnweisungen ohne Parameter an das DBMS zu übermitteln und direkt auszuführen executeQuery( String sqlSelect ) übergibt beliebige parameterlose SQL-SELECT-Anweisungen an das DBMS und erzeugt ein Objekt vom Typ ResultSet, das die Ergebniszeilen enthält und zugreifbar macht. (Vgl. Cursor-Konzept) Neben diesen Methoden gibt es weitere Methoden um die maximale Länge von Ergebnisfeldern abzufragen bzw. zu begrenzen die maximale Anzahl von Ergebniszeilen abzufragen oder zu begrenzen ein Timeout für die Dauer der Ausführung der Anweisung abzufragen oder zu begrenzen die SQL-Warnings auszulesen einen Cursor-Namen zu vereinbaren bei SELECT FOR UPDATE kann dann in einem anderen Statement positioniertes UPDATE, DELETE (WHERE CURRENT OF cursor) verwendet werden beliebige SQL-Prozeduren aufzurufen, die ggf. mehrere SELECT-Anfragen beinhalten: execute( String sqlProzedur ) Methoden, um weitere ResultSet bzw. UpdateCounts zu ermitteln, wenn mehrere erzeugt worden sind um das Statment-Objekt zu schließen: close() PreparedStatement-Interface (ist von Statement abgeleitet) PreparedStatement prep = conn.prepareStatement( "INSERT INTO test VALUES( ?, ? )" ); prep.setInt( 1, 2); prep.setString(2, "Zeile 2"); prep.executeUpdate(); Die SQL-Anweisung wird an das DBMS zur Analyse und "Vorübersetzung" geschickt. Das Interface enthält als wichtigste Methoden executeUpdate() diese dient dazu, die vorbereitete SQL-INSERT-, UPDATE- oder DELETE-Anweisung oder SQL-DDLAnweisungen mit den vereinbarten Parametern an das DBMS zu übermitteln und auszuführen executeQuery() übergibt mit den vereinbarten Parametern die SQL-SELECT-Anweisungen an das DBMS und erzeugt ein Objekt vom Typ ResultSet, das die Ergebniszeilen enthält und zugreifbar macht. (Vgl. Cursor-Konzept) Neben diesen sowie den ererbten Methoden gibt es weiterhin clearParameter() hiermit werden alle vereinbarten Parameterwerte gelöscht set...( int nr, ... wert ) setzt den Parameter nr auf den angegebenen wert setNull( int nr, int sqlType ) setzt den angegebenen Parameter auf den NULL-Wert - 49 - Vorlesung Datenbanken 2 - A. Achilles CallableStatement-Interface (ist von PreparedStatement abgeleitet) // Vorbereitung: in der Regel nicht in rufenden // Programm enthalten int anzahl; Statment stmnt = conn.createStatement(); anzahl = stmnt.executeUpdate( "create procedure SHOW_TEST "+ "AS select * FROM test WHERE NUMMER = ?" ); // Ende der Vorbereitung CallableStatement cstmnt = conn.prepareCall( "{call SHOW_TEST(?)}" ); cstmnt.setInt( 1, 2); ResultSet rs = cstmnt.executeQuery(); Eine Prozedur ist (in der Regel) unabhängig von der Ausführung eines Programmes in der Datenbank vorhanden und vorübersetzt. Das Interface enthält als wichtigste Methoden execute() falls mehrere SQL-Anweisungen (insbesondere SELECT) durch die Prozedur zusammengebunden sind. executeUpdate() diese dient dazu, die Prozedur mit den vereinbarten Parametern aufzurufen und auszuführen executeQuery() erzeugt ein Objekt vom Typ ResultSet und führt die Prozedur mit den vereinbarten Parametern aus Neben den ererbten Methoden gibt es weitere Methoden um Ausgabe-Parameter zu registrieren registerOutParameter( int nr, int sqlType ) AusgabeParameter auszulesen den gerade ausgelesenen Parameterwert auf NULL zu prüfen var = get...( int nr ) wasNull() Auslesen von Ergebnistabellen ResultSet-Interface ResultSet rs = stmt.executeQuery( "SELECT * FROM test"); System.out.println("Nummer, Name "); while (rs.next()) { String eins = Integer.toString(rs.getInt( 1 ).toString()); String zwei = rs.getString( 2 ); System.out.println( eins + ", " + zwei ); } Ein Objekt vom Typ ResultSet entspricht einem Cursor. Die wesentlichen Methoden sind next() um die nächste Zeile zu lesen (den Cursur eine Zeile weiterzuschieben) get...( int nr ) bzw. get...( String spaltenName ) um die entsprechende Spalte, auf der der Cursor derzeit positioniert ist, auszulesen wasNull() um festzustellen, ob in der zuletzt ausgelesenen Spalte der NULL-Wert übertragen wurde getMetaData() um die Struktur des ResultSet analysieren zu können - 50 - Vorlesung Datenbanken 2 - A. Achilles Analysieren der Struktur von Ergebnistabellen ResultSetMetaData-Interface ResultSet rs = stmt.executeQuery( "SELECT * FROM test"); ResultSetMetaData rsmd = rs.getMetaData(); System.out.println( "Anzahl der Spalten: "+ rsmd.getColumnCount() ); for (int i = 1; 1 <= rsmd.getColumnCount(); i++) { int jdbcType = rsmd.getColumnType( i ); String tname = rsmd.getColumnTypeName( i ); String cname = rsmd.getColumnName( i ); } Ein Objekt vom Typ ResultSetMetaData enthält Methoden getColumnCount() um die Anzahl der Spalten zu bestimmen getColumnType( int nr ) um den JDBCType der entsprechenden Spalte zu bestimmen getColumnName( int nr ) um den Namen der entsprechenden Spalte zu bestimmen getPrecision( int nr ) getScale( int nr ) um Information über die Interna der Zahldarstellung der entsprechenden Spalte zu bekommen isAutoIncrement( int nr ) isCaseSensitive( int nr ) isSearchable( int nr ) isCurrency( int nr ) isNullable( int nr ) isSigned( int nr ) isReadOnly( int nr ) isWritable( int nr ) um weitere Informationen über die entsprechende Spalte zu bekommen JDBC 2.0 JDBC 2.0 ist zweigeteilt Paket java.sql ist der Kern der SQL-Schnittstelle, der auch die alte API JDBC 1.0 enthält. Änderungen liegen in Verbesserungen des ResultSet Scrollbar und änderbar neue Datentypen: BLOB, CLOB Batch-Updates Paket javax.sql JNDI Unterstützung Connection Pooling distributed Transactions RowSet-Objekte - 51 - Vorlesung Datenbanken 2 - A. Achilles java.sql Ein Objekt vom Typ ResultSet muß nicht nur sequentiell mit next() durchlaufen werden, es gibt weitere Methoden zum beliebigen Zugriff: absolute( int nr ) positioniert auf die Zeile nr afterLast() positioniert direkt hinter das Ende der letzten Zeile beforeFirst() positioniert direkt vor die erste Zeile first() positioniert auf die erste Zeile getRow() liefert die Nummer der aktuellen Zeile zurück isAfterLast() gibt an, ob hinter der letzten Zeile positioniert ist isBeforeFirst() gibt an, ob vor der ersten Zeile positioniert ist isFirst() zeigt an, ob die Position auf der ersten Zeile liegt isLast() entsprechend, ob die Position auf der letzten Zeile ist last() positioniert auf die letzte Zeile moveToInsertRow() hierbei handelt es sich um eine spezielle Zeile, die mit einem änderbaren ResultSet-Objekt verknüpft ist. Dadurch kann eine neue Zeile in das Objekt eingefügt werden. Bei dem Aufruf wird die aktuelle Position des Cursurs gemerkt moveToCurrentRow() steht der Cursur auf der Eingabezeile, so wird er daraufhin auf die ehemalige Position zurückgesetzt next() bereits in JDBC 1.0: positioniert auf die folgende Zeile previous() positioniert auf die vorhergehende Zeile relative( int anzahl ) verschiebt die Position um anzahl Zeilen, bei negativem Vorzeichen in Richtung Anfang Damit ein Objekt vom Typ ResultSet entsprechende Eigenschaften hat, muß das Statement-Objekt darauf eingerichtet werden: Statement stmnt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, // Scrolling zugelassen // Änderungen anderer // werden nicht wahrgenommen ResultSet.CONCUR_UPDATABLE); // Änderungen zugelassen - 52 - Vorlesung Datenbanken 2 - A. Achilles Ist ein Objekt vom Typ ResultSet änderbar, so können folgende Methoden darauf angewandt werden: deleteRow() löscht die aktuelle Zeile aus der Datenbank insertRow() fügt den Inhalt der Eingabezeile in die Datenbank ein rowDeleted() zeigt an, ob eine Zeile gelöscht wurde rowInserted() gibt an, ob eine Zeile eingefügt wurde rowUpdated() zeigt, ob eine Zeile geändert wurde update...( int nr, typ wert ) ändert in der aktuellen Zeile die Spalte nr auf wert updateNull( int nr ) setzt in der aktuellen Zeile die Spalte nr auf NULL updateRow() ändert in der Datenbank die der aktuellen Zeile zugehörige Zeile Ändern einer Zeile in der Datenbank mittels eines änderbaren ResultSet-Objekts Statement stmnt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmnt.executeQuery("SELECT * FROM test"); rs.absolute(2); // aktuelle Zeile ist Zeile 2 rs.updateString("NAME", "Zeile ZWEI"); // Ändern der zweiten Spalte der // aktuellen Zeile in "Zeile ZWEI" rs.updateRow(); // Eintragen in die Datenbank ... conn.commit(); // Transaktion beenden // damit Änderung für andere // sichtbar machen Einfügen in die Datenbank mittels eines änderbaren ResultSet-Objekts ... rs.moveToInsertRow(); // zur Eingabezeile gehen rs.updateInt( 1, 3); rs.updateString( 2, "3. Zeile" ); // Ändern der Spalten der Eingabezeile rs.updateRow(); // Eingabezeile in Datenbank eintragen rs.moveToCurrentRow(); // zurück zur alten aktuellen Zeile ... conn.commit(); // Transaktion beenden // damit Änderung für andere // sichtbar machen Weitere Verbessereungen des ResultSet: stmnt.setFetchSize( 25 ); ResultSet rs = stmnt.executeQuery( "SELECT * FROM test" ); // es werden zunächst nur 25 Zeilen // in den ResultSet rs geladen Weitere Ergebnisse mittels stmnt.getMoreResults() - 53 - Vorlesung Datenbanken 2 - A. Achilles Neue Datentypen die neuen SQL-Datentypen BLOB CLOB ARRAY REF werden unterstützt, es gibt entsprechende Interfaces und get...(...) und set...(...)-Methoden in ResultSet Auch benutzerdefinierte Typen werden durch das Interface Struct sowie die ResultSetMethoden getObject(...) und setObject(...) bereitgestellt. Performance-Verbesserung durch „Batch“ Statement stmnt = conn.createStatement(); conn.setAutoCommit( false ); stmnt.addBatch("INSERT INTO test VALUES( 4, 'Z4' )" ); stmnt.addBatch("INSERT INTO test VALUES( 5, 'zeile fuenf' )" ); stmnt.addBatch("INSERT INTO test VALUES( 6, 'sechs' )" ); int [] updateCounts = stmnt.executeBatch(); ... conn.commit(); Die Anweisungen werden in der Reihenfolge ausgeführt, in der sie dem Batch hinzugefügt wurden jede einzelne Anweisung muß eine Zahl zurückliefern, die die Anzahl der Änderungen angibt; diese Zahl wird dem Array updateCounts hinzugefügt. Ist dies nicht der Fall, wird eine BatchUpdateException geworfen. ein JDBC-Treiber muß nicht notwendig Batch unterstützen und die Methoden addBatch(...), clearBatch() und executeBatch() unterstützen Aufschluß über den letzten Punkt liefern die DatabaseMetaData: DatabaseMetaData dbmd = conn.getMetaData(); if (dbmd.supportsBatchUpdates()) System.out.println( "der Treiber unterstützt Batch" ); JNDI DataSource-Objekt repräsentiert eine Datenquelle: DBMS, Tabelle, Datei, ... wird vom Administrator mit entsprechendem Werkzeug bei JNDI (Java Naming and Directory Interface) Dienst registriert Anwendung kann unter logischem Namen danach beim JNDI suchen und dann die Verbindung herstellen Informationen über die Datenquelle - Name, Server, usw. - sind als Eigenschaften im DataSourceObjekt enthalten müssen nicht mehr hart codiert werden bei Verlagerung der Datenquelle muss Code nicht verändert werden Verbindung mittels JNDI-API ohne Verwendung von Treibern: String lname = new String("jdbc/DemoDB"); // logischer Name für Datenquelle Context ctx = new InitialContext(); // JNDI-API DataSource ds = (DataSource)ctx.lookup( lname ); // unter dem Namen "jdbc/DemoDB" wird // eine Datenquelle gesucht Connection conn = ds.getConnection( "NAME", "passwort" ); Verbindungsaufbau über JNDI ist insbesondere dann wichtig, wenn verteilte Transaktionen oder Connection Pooling eingesetzt werden soll. - 54 - Vorlesung Datenbanken 2 - A. Achilles Pooled Connection um die Performance zu verbessern, ist es bei häfigen Zugriffen sinnvoll, eine Verbindung nicht völlig zu zerstören, sondern sie wiederzubenutzen durch entsprechende Maßnahmen stellt der Administrator einen Pool von Verbindungen zu einer Datenquelle bereit für die Programmierung ändert sich (fast) nichts Im folgenden sei unter dem logischen Namen jdbc/poolDB ein Pool von Verbindungen bereitgestellt. ctx = new InitialContext(); ds = (DataSource)ctx.lookup("jdbc/poolDB"); try { Connection conn = ds.getConnection( "NAME", "passwort" ); // und hier wird damit gearbeitet } catch (Exception e) {... } finally { if (conn != null) conn.close(); } Die finally-Klausel bewirkt, daß die Verbindung auf jeden Fall geschlossen wird, auch dann, wenn eine Ausnahme geworfen wird. Damit steht die Verbindung wieder im Pool bereit und wird nicht durch die Anwendung lange blockiert. Verteilte Transakionen auch hier wird die wesentliche Arbeit beim „Deployen“ erledigt: die Datenquelle muß korrekt beim JNDI angemeldet werden und dabei mit einer XADataSource verbunden werden die Anmeldung im Programm verläft wieder wie gehabt, d.h. verteilte Transaktionen sind für den Programmierer nahezu transparent ein TransactionManager verwaltet im Hintergrund die Transaktion einzig folgende Restriktionen sind zu beachten: verboten sind die Aufrufe conn.commit() conn.rollback() conn.setAutoCommit(true) RowSet dient als "Behälter" für Zeilen Implementation ist ein Aufsatz auf den Treiber erweitert ResultSet unterstützt das JavaBeans Modell hat Methoden um Listener hinzuzufügen, zu entfernen und deren Eigenschaften festzulegen eine spezielle Eigenschaft ist das Kommando, das gesetzt und ausgeführt werden kann ein RowSet kann sein Kommando selbst einstellen und ausführen und sich somit selbst mit Daten versorgen ein RowSet kann sich nach dem Laden von der Datenquelle abkoppeln es kann in abgekoppelten Zustand serialisiert werden es eignet sich zur Versendung über das Netz z.B. zu einem PDA es kann aktualisiert und dann zum Datenabgleich erneut mit seiner Datenquelle verbunden werden - 55 - Vorlesung Datenbanken 2 - A. Achilles Datenbanken - Optimierung Ansätze ein oder mehrere Datenbank-Knoten: bezogen auf einen Datenbank-Knoten Installation des DBMS Bereitstellen von physischem Speicher Plazierung von Tabellen, Indexen Indexierung Partitionierung Clustern Denormalisierung mehrere Knoten: Knoten-übergreifend Verteilung weitere Einflüsse Replikation Installation Größe des Dictionaries ggf. Blockgröße des Dictionaries Positionierung des Ditionaries im physischen Speicher Vermeidung von Congestion des Controllers sowie der Platte Sicherungskonzepte: Dual Logging Überschreiben? Logging auf externe Medien Strategie Größe und Speicherort ... Bestimmung der Größe von Plattenplatz für Daten Indexe (temporärem) Plattenplatz für Sortiervorgänge Blockgröße für physisches IO auf Daten Physischer Speicherplatz Ziel: durch Bereitstellung von genügend physischen Laufwerken an genügend Controllern sollen im laufenden Betrieb die IO-Zugriffe gut verteilt werden, so dass in diesem Bereich keine Engpässe auftreten Möglichst Trennung von DBMS-System-Dateien Dictionary Log-Bereiche temporäre Plattenbereiche für große Sortiervorgänge (mehrere) Bereiche für die Daten (ggf.) eigene Index-Bereiche Plazierung Ziel: durch Verteilung der Tabellen, Indexe auf mehrere Controller/Laufwerke sollen im laufenden Betrieb die IO-Zugriffe gut verteilt werden, so dass in diesem Bereich keine Engpässe auftreten Hier ist das zu erwartende Zugriffsverhalten des Systems - d.h. Häufigkeit der Zugriffe auf die Daten einzubeziehen - 56 - Vorlesung Datenbanken 2 - A. Achilles Indexe Indexe sollen typischerweise folgende Aspekte unterstützen: den direkten Zugriff auf Datensätze den Zugriff auf einen Bereich das Sortieren Indexe verändern die Struktur der gespeicherten Daten nicht, können somit ohne Probleme auch nachträglich eingerichtet werden Probleme beim Index-Einsatz: bei kleinem Datenvolumen (Richtwert < 8 Pages für eine Tabelle): Zugriff über Index teurer als kompletter Table-Scan bei Änderungen an einer Tabelle müssen sämtliche Indexe für diese Tabelle mitgepflegt werden Geeignete Index-Kandidaten: Schlüsselspalten Spalten, die in Join, Group, Having und Sort-Operationen häufig benötigt werden ggf. falls Index bereits gesamte Information für häfigen Zugriff enthält Partitionierung Geeignete Aufteilung einer Tabelle auf mehrere Partitionen führt dazu, dass im Mittel weniger physische IO_Vorgänge abzuwickeln sind: so kann z.B. statt eines kompletten Table-Scans ein Scan einer geeigneten Partition ausreichen Partitionen können ggf. unabhängig voneinander gesichert werden ggf. können Partitionen bei Mehrprozessorsystemen parallel verarbeitet werden Arten der Partitionierung: horizontale Partitionierung: ein Partitionierungs-Index nimmt transparent die Aufteilung in die einzelnen Partitionen vor dies wird bereits von vielen DBMS unterstützt vertikale Partitionierung: bedeutet eine strukturelle Veränderung Schlüsselspalten müssen in allen Partitionen wiederholt werden Verbesserung bei allen Zugriffen, die nur auf eine Partition zugreifen müssen wegen verringertem IO aber: Partitionsübergreifende Zugriffe erfordern Join, werden somit erheblich teurer sorgfältige Planung erforderlich Clustern Ziel: Tabellenzeilen, die logisch "zusammengehören" und somit häufig zusammen gelesen werden, sollen physisch nahe benachbart gespeichert werden Gelingt dies, so werden in der Regel bei einer IO-Operation bereits die gemeinsam benötigten Tabellenzeilen gelesen Unterstützung: ein Index, der als Cluster-Index ausgezeichnet wird, sorgt für die physische Speicherung der Datensätze nahe benachbarte Sätze werden möglichst im gleichen Block gespeichert Ausnutzung von "FreeSpace"-Parametern Problem: was passiert nach mehrfachen Änderungen? - 57 - Vorlesung Datenbanken 2 - A. Achilles Denormalisieren Ziel: Performance-Steigerung durch Verminderung der Lese- und Join-Operationen Problem: Denormalisierung macht Eregbnisse des vorhergehenden Entwurfs teilweise rückgängig Konsequenzen sorgfältig bedenken wichtige Vorgehensweisen: häufig benutzte Verdichtungen werden in regelmäßigen Abständen berechnet und physisch in einer eigenen Tabelle gespeichert spart sowohl IO-Operationen als auch Rechenzeit Normalisierung wird nicht vollständig durchgeführt spart Join-Operationen Tabellen (oder Teile davon) werden verdoppelt kann lesenden Zugriff verbessern Problem: ändernde Transaktionen wegen Redundanz bei Informations-Systemen mit großen Datenbeständen und hohen Anforderungen an die Zugriffsgeschwindigkeit wird man in vielen Fällen auf Denormalisierung zurückgreifen müssen. Mehrere Knoten - Verteilung Verteilung ohne Replikation limitierender Faktor die Datenübertragung => Verteilung ist so zu wählen, dass Datenübertragung minimiert wird Faktoren: Verteilung der Daten auf den DB-Knoten Anwendungen und deren Zugriffsverhalten Häfigkeitsverteilung bzgl. des Aufrufs der Anwendungen Verteilung der Aufrufe auf die Knoten weitere Einflüsse je nach Gesamtanforderung: Ausfallsicherheit der einzelnen DB-Knoten Anzahl der Verbindungen zwischen den DB-Knoten Geschwindigkeit der Verbindungen Qualität der Verbindungen Auslastung der einzelnen Knoten CPU-Leistung der Knoten Hauptspeicher der Knoten (Platten-) Peripherie der Knoten ... Mehrere Knoten - Replikation * Replikation bedeutet: durch Duplizieren von Tabellen gezielte Redundanz Replikation von Tabellen, die (nahezu) ausschließlich gelesen werden, ist völlig unproblematisch Problem: Replikate, die auch geändert werden sollen in diesem Fall sind für jedes Replikat Vor- und Nachteile abzuwägen: größere Verfügbarkeit der Daten höhere Performance bei lesenden Transaktionen erhöhter Systemaufwand höhere Kommunikation Verlangsamung bei ändernden Transaktionen (je nach Replikationsverfahren) - 58 - Vorlesung Datenbanken 2 - A. Achilles Synchronisation und Replikation Warum verteilte Informationssysteme? größere Ausfallsicherheit auch wenn ein Knoten ausfällt, kann noch auf andere Knoten - und damit auf die dort gespeicherten Daten - zugegriffen werden erhöhte Datenverfügbarkeit kann als "Korollar" der ersten Aussage angesehen werden insbesondere eine Duplizierung von Daten gestattet die Erreichbarkeit auch bei Ausfall von Knoten jedoch: diese Aussage gilt nur bei rein lesendem Zugriff; Änderungen können Probleme bereiten: vgl. Transaktionen Verbesserung des Durchsatzes durch Duplizierung von Daten auf den einzelnen Knoten kann eine Reduzierung der Netzlast erreicht werden - da Anwendungen parallel auf den Knoten laufen, kann damit eine Verbesserung des Durchsatzes erzielt werden Lastausgleich insbesondere bei Anfragen kann bei geeigneter Zwischenschicht die Auslastung der einzelnen Knoten (und Netzverbindungen) berücksichtigt werden um eine gleichmäßige Auslastung zu erreichen Mobilität dieses Argument ist anders gelagert als die vorherigen: externe Mitarbeiter bekommen zur Unterstützung Laptops, deren Daten nahtlos in das „Firmendatenmodell“ eingefügt werden sollen Probleme bei verteilten Informationssystemen Kommunikationsaufwand Daten, die nicht auf dem Knoten liegen, auf dem eine Anfrage erfolgt, müssen über das Netz geholt werden bei Duplikaten muß bei einer Datenänderung die Änderung "rechtzeitig" an alle Knoten weitergegeben werden Aktualität der Daten auf den einzelnen Knoten wie rechtzeitig werden Änderungen an die anderen Knoten weitergereicht? Konsistenz der Daten bei Duplizieren der Daten tritt auf Grund der obigen Aussagen das Problem auf, daß Daten ggf. nicht mehr (oder auch nur zeitweise nicht) konsistent über die Datenbasis aller beteiligten Knoten sind Auf Grund der Probleme bei verteilten Informationssystemen die Verteilung ist sorgfältig zu planen Duplizierung der Daten ist sorgfältig zu planen das Verfahren, duplizierte Daten zu aktualisieren, entscheidet über die Art, wie die das System eingesetzt werden kann Wie bei Normalisierung gilt: Verteilung und Duplizierung nur soviel wie nötig Auch bei verteilten Systemen müssen Transaktionen wie in einem lokalen DBMS unterstützt werden D.h. die Eigenschaften A Atomicity (Atomarität) eine Änderung der Daten wird entweder vollständig oder überhaupt nicht vorgenommen C Consistency (Konsistenz) geänderte Daten müssen Konsistenz-Bedinungen erfüllen I Isolation (isolierte Zurücksetzbarkeit) ein Zurücksetzen betrifft ausschließlich die Änderungen, die in der Transaktion vorgenommen wurden D Durability (Dauerhaftigkeit) die Daten einer erfolgreichen Transaktion werden dauerhaft gespeichert müssen gewährleistet sein - 59 - Vorlesung Datenbanken 2 - A. Achilles Anschauliche Darstellung: eine Transaktion ist eine zusammengefaßte Folge von Operationen, die eine logische Einheit bilden sie werden entweder alle ausgeführt oder es wird keine Änderung sichtbar die Datenbasis wird aus einem konsistenten Zustand in einen neuen konsistenten Zustand überführt anschaulich: am Ende einer Transaktion wird wieder ein korrekter Zustand der abzubildenden Welt dargestellt wird eine Transaktion zurückgesetzt, so werden Änderungen anderer Transaktionen nicht davon betroffen Änderungen, die innerhalb einer Transaktion gemacht werden, werden am Ende permanent gespeichert lokale Transaktionen betreffen nur ein DBMS (einen Datenbank-Knoten) globale Transaktionen in einem Verteilten System betreffen mehrere Knoten Verfahren zur Transaktionsunterstützung an einem Knoten: typisch für bekannte relationale DBMSe: Sperrverfahren weitere Verfahren in diesem Abschnitt Verfahren zur globalen Transaktionsunterstützung? Globale Transaktionsunterstützung setzt auf der lokalen Transaktionsunterstützung auf durch Kommunikation der beteiligten Knoten muss gewährleistet werden, dass die auf den beteiligten Knoten laufenden Subtransaktionen gemeinsam ausgeführt oder verworfen werden üblicherweise verwendetes Protokoll: Zwei-Phasen-Commit-Protokoll Dieses Protokoll kennzeichnet für jede Transaktion einen Knoten besonders aus: "Koordinator" ist derjenige Knoten, an dem die Transaktion gestartet wird Abschluß der Transaktion: 1. der Koordinator schickt an alle an der Transaktion beteiligten Knoten die Meldung Prepare-to-commit 2. die Knoten informieren daraufhin den Koordinator, ob sie die Transaktion lokal durchführen können (Commit) die Transaktion verwerfen müssen (Abort) 3. die jeweilige Subtransaktion wird jedoch noch nicht vollzogen, vielmehr warten die Knoten auf die endgültige Mitteilung des Koordinators 4. der Koordinator sammelt alle Rückmeldungen auf und informiert die Knoten: falls alle Rückmeldungen Commit lauten, mit der Meldung Commit falls wenigstens eine Rückmeldung Abort lautet, mit Abort 5. die Knoten vollziehen ihre Aktion auf Grund der endgültigen Meldung des Koordinators - 60 - Vorlesung Datenbanken 2 - A. Achilles 2-Phasen-Commit-Protokoll dieses Protokoll arbeitet korrekt, solange keine Ausfälle oder Verklemmungen auftreten was bei gestörter Kommunikation zwischen Koordinator und einem Knoten? Timeout-Mechanismus in den WAIT-Phasen: Kommt bis zum Ablauf des Timeouts keine Nachricht an, so muß auf diesen Fehler geeignet reagiert werden um Blockierung aufzuheben Timeout für Knoten Kommunikation zum Koordinator gestört mögliche Reaktion: kann andere beteiligte Knoten befragen und reagieren falls wenigstens ein Knoten mit Abort antwortet: selbst Abort vollziehen falls ein Knoten bereits die globale Rückmeldung vom Koordinator erhalten hat: entsprechend der globalen Commit- oder Abort-Meldung reagieren in allen anderen Fällen muß Knoten solange warten, bis Kommunikation zu Koordinator erneut aufgebaut ist Knotenausfall - Koordinator mögliche Reaktion: falls END-Record im Protokoll: alle beteiligten Subtransaktionen sind abgeschlossen Commit- oder Abort-Record: alle beteiligten Knoten werden darüber informiert andernfalls: globales Abort da Koordinator bei Auftreten des Problems offensichtlich noch in WAIT-Phase war - 61 - Vorlesung Datenbanken 2 - A. Achilles Knoten einer Subtransaktion mögliche Reaktion: Commit- oder Abort-Record im eigenen Protokoll: entsprechendes Redo oder Undo, um lokale Subtransaktion angemessen zu beenden Ready-Record: globales Abstimmungsergebnis muss erfragt werden andernfalls: Transaktion abbrechen offensichtlich hat Commit-Protokoll noch nicht begonnen Problem dieses Protokolls Koordinator muß Information auch über beendete Transaktionen aufbewahren Blockade der beteiligten Knoten bei Ausfall des Koordinators Problem Wie können Transaktionen synchronisiert werden, so dass der Isolationsgrad SERIALIZABLE erreicht wird? Es werden üblicherweise zwei verschiedene Arten von Verfahren eingesetzt: Sperrverfahren dies wird bei nahezu allen bekannten RDBMS verwendet Transaktionskonflikte werden ausgeschlossen, jedoch wird dies durch die Gefahr von Deadlocks erkauft Optimistische Synchronisationsverfahren hierbei handelt es sich um eine Reihe von Verfahren, die zunächst einmal Transaktionskonflikte zulassen um diese im Nachhinein zu korrigieren. Deadlocks werden dadurch vermieden. Die von einer Transaktion betroffenen Daten werden mit einer Lese- bzw. Schreibsperre versehen am Transaktionsende werden die Sperren wieder freigegeben will eine Transaktion Sperren auf bereits gesperrte Daten setzen, so muss sie auf die Freigabe der Sperren warten Deadlockgefahr Entscheidend für Parallelisierungsgrad und die Performance des Systems sind die folgenden Parameter die „Größe“ der gesperrten Daten die Art der Sperren Größe der gesperrten Datenobjekte: Objekt Record Page Tabelle Speicherbereich ... Datenbank Eskalation der Sperren variieren von System zu System Arten der Sperre zum Lesen andere Transaktionen können ebenfalls lesend aber nicht schreibend zugreifen nicht exklusiv mit der Option auf exklusiven Zugriff ... zum Schreiben exklusiver Zugriff - 62 - Vorlesung Datenbanken 2 - A. Achilles Zwei-Phasen-Sperrprotokoll: Phase 1: Eine Transaktion belegt jedes Datenobjekt, auf das sie zugreift, mit einer entsprechenden Sperre Phase 2: Nach Freigabe der einer Sperre darf die Transaktion keine weiteren Sperren anfordern d.h. in der ersten Phase werden alle Sperren angesammelt und nach erfogten Änderungen in der zweiten wieder freigegeben. Am einfachsten zu implementieren: Freigabe aller Sperren am Ende der Transaktion Sperren werden in Sperrtabelle vermerkt, Einträge müssen zumindest enthalten Transaktionsnummer gesperrtes Objekt Art der Sperre Zugriff auf Sperrtabelle muss exklusiv erfolgen bei verteilten Systemen könnte zentrale Sperre verwendet werden Vorteil: Synchronisation der Transaktionen wie im lokalen System Nachteile: Kommunikation zum zentralen Knoten sowie Autonomieverlust der übrigen Knoten dezentrale Sperrtabellen: jede Transaktion fordert bei dem jeweiligen lokalen Sperrmanager Sperren an Problem: globale Deadlocks Zeitstempelverfahren Anstelle Sperrtabelle: jede Transaktion besorgt sich zu Beginn einen Zeitstempel bei Zugriff auf ein Objekt wird dieser in ein zusätzliches Feld des Objektes eingetragen (Erweiterung der Datenstruktur) (Basic Timestamp Ordering): eine Transaktion, die ein Objekt sperren will, das bereits einen jüngeren Zeitstempel trägt wird zugunsten der jüngeren Transaktion zurückgesetzt Vorteil: garantierte Deadlockfreiheit Nachteile: Erweiterung der Datenstrukturen Benachteiligung länger laufender Transaktionen Problem: Zeitstempel bei verteilten Systemen zentraler Zeitstempelserver zweitteiliger Zeitstempel: Zeitmarke + RechnerID optimistische Synchronisationsverfahren falls nur selten Transaktionskonflikte auftreten vorbeugende Sperren sind unnötiger Aufwand greifen nicht in den Ablauf einer Transaktion ein sie überprüfen statt dessen am Ende einer Transaktion, ob Konflikt aufgetreten ist in dem Falle wird Transaktion zurückgesetzt Transaktion läft in drei Phasen ab: 1. Lesephase benötigte Objekte werden gelesen sowie in einem Read-Set gespeichert bei Änderungen werden sei in einem Write-Set gepuffert, aber nicht zurückgeschrieben 2. Validierungsphase bei Transaktionende wird geprüft, ob Konflikte zu anderen Transaktionen aufgetreten sind Validierungsphase muß exklusiv durchlaufen werden, d.h. es darf jeweils nur eine Transaktion validiert werden dies gilt auch für verteilte Anwendungen 3. Schreibphase ist Validierung erfolgreich verlaufen, so wird Write-Set zurückgeschrieben Änderungen werden dadurch sichtbar - 63 - Vorlesung Datenbanken 2 - A. Achilles Vorteile Deadlock-frei geringer Kommunikationsaufwand Probleme vergleichsweise großer Aufwand bei Transaktionskonflikten zwei grosse Kategorien Unterschied in den zu überprüfenden Transaktionen rückwärts orientiert vorwärts orientiert Rückwärts orientierte Validierungsverfahren Validierungsphase vergleicht, ob benötigte Objekte in der Zwischenzeit von anderen Transaktionen verändert wurden Read-Set der zu validierenden Transaktion wird verglichen mit Write-Sets aller Transaktionen, die während der Lesephase der Transaktion validiert wurden: Transaktion ist validiert, wenn keine Differenzen auftreten Problem: langlaufende Transaktionen Vorwärts orientierte Validierungsverfahren Validierende Transaktion wird gegen zur Zeit der Validierung aktive Transaktionen geprüft Write-Set der zu validierenden Transaktion wird verglichen mit allen Read-Sets parallel laufender Transaktionen: gilt als validiert, wenn keine Konflikte auftreten Bei Validierungsfehler können zwei unterschiedliche Strategien beschritten werden: die zu validierende Transaktion wird zurückgesetzt die den Konflikt erzeugenden laufenden Transaktionen werden zurückgesetzt werden bei Validierungsfehler die den Konflikt erzeugenden laufenden Transaktionen zurückgesetzt lang laufende Transaktionen werden unterstützt nach vagen Informationen: Navision native DBMS ist ein Beispiel für ein RDBMS mit optimistischem Synchronisationsverfahren Allerdings kann Navision auch mit anderen RDBMS betrieben werden Probleme bei verteilten DBMS langsame Kommunikationsverbindungen fehleranfällige Verbindungen Daten nicht verfügbar entfernter Rechner ggf. stark überlastet Replikation adressiert diese Probleme: (einige) Datenobjekte werden mehrfach (auf mehreren Knoten) gespeichert: höhere Datenverfügbarkeit Performance-Verbesserung jedoch: Widerspruch zur Redundanzfreiheit jeder Fakt wird genau einmal gespeichert jeder Zugriff auf Daten liefert immer aktuellen Wert Replikation: bei lesendem Zugriff auf entfernte Daten: leicht „veraltete“ Kopie der Daten kann bereits nützlich sein: Verfügbarkeit Zugriffsgeschwindigkeit jedoch: zusätzlicher Plattenplatz Aktualität der Daten - 64 - Vorlesung Datenbanken 2 - A. Achilles Replikation einer Tabelle: lesende Transaktionen Systemverfügbarkeit bei lesenden Transaktionen steigt System wird gegenüber Ausfällen stabiler ändernde Transaktionen da Änderung an allen Replikaten vollzogen werden muß: Systemverfügbarkeit sinkt, da mit jedem Replikat die Wahrscheinlichkeit des Ausfalls mindestens eines beteiligten Knotens wächst Korrektheit: um globale Konsistenz zu gewährleisten: Änderung an replizierten Objekt muß an allen Replikaten so erfolgen, daß Transaktionen konsistente Version des Objekts wahrnehmen für jede globale Folge von Transaktionen muss gelten: die an jedem Knoten entstehenden Teiltransaktionen sind serialisierbar die Menge aller lokalen Transaktionsfolgen kann in serielle Ausführungsreihenfolge gebracht werden, die äquivalent zu einer globalen Folge ist Replikationverfahren sollen Korrektheit unterstützen syntaktische Verfahren unabhängig von weiterem Wissen über die Anwendung semantische Verfahren setzt spezielles Wissen über die Eigenarten der Anwendung voraus nicht universell einsetzbar Syntaktische Replikationverfahren: absolutistische Kopien-übergreifende Synchronisation z.B. Primary Copy, Token, Exclusive Writes Voting-basierte Kopien-übergreifende Synchronisation unstrukturiertes Quorum statisches Quorum (z.B. Majority Consensus) dynamisches Quorum (z.B. Dynamic Voting) strukturiertes Quorum statisches Quorum (z.B. Tree Quorum) dynamisches Quorum (z.B. Reconfigurable Tree Quorum) Read-One-Copy Kopien-übergreifende Synchronisation (z.B. ROWA) Primary Copy für Replikate einer Tabelle: ein Knoten wird für diese Replikate als Primärknoten ausgezeichnet Änderung darf nur an diesem Knoten erfolgen nach erfolgreicher Änderung durch eine Transaktion: Knoten übernimmt Verteilung der Änderung an die Knoten, die Replikate tragen Änderung ist asynchron Kommunikationsaufwand abhängig von Wahl des Primärknotens Verringerung des Kommunikationsaufwandes: Sammeln von Änderungsinformationen auf Primärknoten verzögerte gebündelte Weitergabe an andere Knoten Achtung: Aktualität der Replikate sinkt je nach Anforderung bzgl. der Aktualität bei lesenden Zugriffen: Lesen von Primärkopie keine Vorteile durch Replikation - 65 - Vorlesung Datenbanken 2 - A. Achilles Voting-Verfahren: unstrukturiertes Quorum Majority Consensus Änderungen: jeder Knoten, der ein Replikat hat, muss alle anderen Knoten kennen, die entsprechendes Replikat besitzen der Knoten, an dem Transaktion initiiert wird, muss alle beteiligten Replikat-tragenden Knoten befragen jeder befragte Knoten antwortet positiv, falls das betreffende Datenobjekt lokal frei ist (und sperrt es für die Transaktion), negativ falls es bereits gesperrt ist der fragende Knoten sammelt die Antworten ist die Mehrheit der Antworten positiv d.h. Hälfte aller Knoten + 1, darf die Änderung erfolgen (denn keine andere Transaktion kann entsprechend viele Stimmen erlangen) lesende Transaktionen: gleiches Prinzip wie bei Änderungen Vermeidung von Dirty Reads Inconsistent Reads Vorteile des Verfahrens: Stabilität gegenüber Knotenausfällen Nachteile: hoher Kommunikationsaufwand Voting-Verfahren: strukturiertes Quorum Tree Quorum Idee des Abstimmungsverfahren entspricht dem Majority Consensus: Bringe die Mehrheit der Knoten hinter dich! Jedoch Verringerung des Kommunikationsaufwandes: es wird eine Struktur (z.B. durch Vernetzung vorgegebene Baumstruktur) zu Grunde gelegt Anfrage startet vom Wurzelknoten aus anstelle der Mehrheit der Knoten: es ist die Mehrheit der Ebenen zu erzielen Mehrheit einer Ebene ist erreicht, wenn Mehrheit der Knoten dieser Ebene der Transaktion zustimmt Probleme: Kommunikation startet von Baumwurzel: Kommunikationsengpässe sind möglich Ausfall mehrerer (wichtiger) Knoten: ggf. keine entscheidungsfähige Mehrheit möglich Dynamische Quoren Problem bisherigen Vorgehens: Anzahl der Rückmeldungen starr auch wenn Knoten ausfallen: wird die ursprüngliche Anzahl von positiven Antworten eingefordert Vorgehen: mache Anzahl der geforderten positiven Antworten von Anzahl der erreichbaren Knoten abhängig Vorteil: höhere Verfügbarkeit Nachteil Gefahr der Netzpartitionierung - 66 - Vorlesung Datenbanken 2 - A. Achilles Netzpartitionierung (Dynamische Quoren) ROWA - Read One Write All jede Lesetransaktion fndet auf jedem Knoten den aktuellsten Stand Datenänderungen müssen synchron auf allen Replikaten durchgeführt werden ändernde Transaktionen müssen nicht nur das zu ändernde Datenobjekt, sondern auch alle Replikate dieses Objekts sperren Problem: fällt ein replikat-führender Knoten aus, so kann kein globales Commit erfolgen Stabilität des Systems von entscheidender Bedeutung Vorteil: für lesende Transaktionen gelten alle Vorteile von Replikation - 67 - Vorlesung Datenbanken 2 - A. Achilles Replikation bei konkreten DBMS Oracle: Basic Replication: Änderung auf Primärtabelle, Datenverteilung asynchron auf Replikate Replikate als Snapshot der Primärtabelle Änderungs-LOG der Primärtabelle wird benutzt, um die Änderungen zu propagieren entspricht dem Primary Copy Advanced Replication Symmetrische Replikation mit asynchroner Aktualisierung alle Replikate sind gleichberechtigt, Änderungen werden an alle übrigen Replikate asynchron weitergereicht keine Behandlung des DELETE-Konflikts Änderbare Snapshots Variante beider vorhergehenden Arten: Primärtabelle verteilt weiterhin die Änderungen asynchron an alle Replikate, Replikate richten die Änderungsmitteilung mit Hilfe des INSTEAD OF-Triggers an die Primärtabelle Symmetrische synchrone Aktualisierung (ROWA) ist nicht vorgesehen DB2: Asynchrone Aktualisierung unter Einsatz einer Primärtabelle es werden nur transaktions-konsistente, aber nicht transaktions-basierte Änderungen verteilt Verringerung der Netzbelastung, indem zwischen zwei Replikationszeiten Änderungen zusammengefaßt werden Transaktions-basierte asynchrone Aktualisierung unter Einsatz einer Primärtabelle Primary Copy Asynchrone Aktualisierung unter Einsatz einer Primärtabelle mit änderbaren Kopien sowohl Replikate als auch Primärtabelle erzeugen Änderungsmitteilungen, Änderungsmitteilungen der Replikate gehen an Primärtabelle, die auf Konsistenz überprüft, Primärtabelle übernimmt Verteilung Symmetrische synchrone Aktualisierung: (ROWA) ist nicht vorgesehen - 68 - Vorlesung Datenbanken 2 - A. Achilles Distributed Relational Database Architecture - DRDA Architekturbeschreibung von IBM 1998/99 festgelegt Menge von Protokollen bzw. Regeln, um plattform-übergreifend auf verteilte Daten zugreifen zu können definiert Methoden zur koordinierten Kommunikation zwischen verteilten RDBMS Zugriff auf entfernte Tabellen wirkt für Benutzer wie lokaler Zugriff Unterschied beachten Architektur DRDA beschreibt die Architektur, die Regeln, die den Zugriff ermöglichen Implementation APIs werden von diversen Herstellern angeboten und müssen sich an den Regeln/Protokollen von DRDA orientieren DB2 ist z.B. DRDA-verträglich, d.h. es hält sich an die DRDA-Spezifikation auch heute (Anfang 2002) gibt es noch kein Produkt, das den vollen Umfang von DRDA unterstützt DRDA ist nicht die einzige Architektur dieser Zielrichtung: RDA (Remote Database Access) (ISO, ANSI-Standard Kommittee) Unterschiede: RDA: Standard-Subset von SQL, das auf allen Plattformen verfügbar ist DRDA: arbeitet mit Plattform-spezifischen Erweiterungen von SQL RDA: nur dynamisches SQL DRDA: auch statisches SQL Verteilter Zugriff wird in DRDA mit drei Funktionen verwirklicht, die miteinander operieren: Application Requester (AR) Application Server (AS) Database Server (DS) Application Requester (AR) ermöglicht SQL und entsprechende Anforderungen durch Programme nimmt SQL-Anforderungen vom Programm entgegen und sendet sie zur Verarbeitung an die entsprechenden Server AR ist in DB2Connect verwirklicht Anmerkung: falls Daten völlig lokal vorliegen, wird kein DRDA benötigt falls Daten ausschließlich entfernt vorliegen, wird kein lokales RDMBS benötigt Application Server (AS) empfängt Anforderungen vom AR und bearbeitet diese AS bearbeitet Anforderungen direkt, sofern möglich SQL-Anweisungen werden an DS zur Bearbeitung weitergeleitet Kommunikation zwischen AR und AS Kommunikation-Protokoll zwischen AR und AS: Application Support Protocol Kommunikationsprotokoll sorgt für entsprechende Datenumwandlung z.B. ASCII-EBCDIC - 69 - Vorlesung Datenbanken 2 - A. Achilles Kommunikation zwischen AR und AS Database Server (DS) DS erhält Anforderung von AS oder einem anderen DS Anforderung kann SQL-Anweisung oder "Programm-Vorbereitung" sein wie AS: DS bearbeitet Anforderung soweit möglich, reicht den Rest weiter an anderen DS z.B. Join von Tabellen, die auf unterschiedlichen DS liegen Protokoll: Database Support Porotocoll damit unterschiedliche DBMS eingebunden werden können wird benutzt zwischen AS - DS DS - DS Rückgabe nach kompletter Bearbeitung der Anforderung AS gibt ReturnCode und ResultSet (sofern erzeugt) an AR ReturnCode: SQLSTATE es wird kein ResultSet erzeugt, falls INSERT, UPDATE, DELETE keine Ergebniszeilen bei SELECT DCL oder DDL Limited Block Protocol: Voraussetzung: Read only Cursor sendet mehrere Zeilen über das Netz, auch wenn Fetch jeweils nur eine Zeile bearbeiten kann dadurch Verringerung des Netzverkehrs und damit Performance-Verbesserung - 70 - Vorlesung Datenbanken 2 - A. Achilles Andere Standards: auf denen DRDA aufsetzt Advanced Program to Program Comunication (APPC) Kommunikations-Unterstützung LU6.2 zwischen funktional gleichberechtigten logischen Einheiten Distributed Data Management (DDM) definiert Methoden, um verteilte Daten über Netz mittels APPC anzusprechen Daten können entweder Files oder Tabellen in einem RDBMS sein Formatted Data: Object Content Architecture (FD:OCA) Architektur, um Daten-Felder auszutauschen Daten und ihre Beschreibung werden zusammen verpackt, so dass jedes DRDA-unterstützende DMBS Struktur und Inhalt verstehen kann Character Data Representation Architecture (CDRA) unterstützt den Austausch von Zeichen zwischen den unterschiedlichen Hardware- und BSArchitekturen DRDA bietet 5 unterschiedliche Ebenen für die Unterstützung von Verteilung 1. User-Assisted 2. Remote Request 3. Remote Unit of Work (RUW) 4. Distributed Unit of Work (DUW) 5. Distributed Request Übersicht über die DRDA-Ebenen DRDA-Ebene #SQL-Anweisungen User-Assisted Remote Request 1 Remote Unit of Work >1 Distributed Unit of Work >1 Distributed Request >1 #DBMS pro Einheit #DBMS pro SQL-Anweisung 1 1 1 1 >1 1 >1 >1 User-Assisted Distribution der Benutzer nimmt die physische Verteilung wahr, er kann nur Daten aus dem System extrahieren Daten in das System laden Achtung: Probleme bei Replikaten nützlich z.B. bei Erstellung von Snapshots Remote Request Wenn ein DBMS DRDA Remote Request Fähigkeiten unterstützt, dann kann eine einzige SQLAnweisung pro Single Unit of Work an ein entferntes DBMS gestellt werden um Daten zu lesen oder zu verändern Remote Unit of Work mehrere SQL-Anweisungen können in einer Transaktion gegen genau ein entferntes DBMS gestellt werden Distributed Unit of Work mehrere SQL-Anweisungen können in einer Transaktion gegen mehrere entfernte DBMS gestellt werden pro SQL-Anweisung kann jedoch nur ein DBMS angesprochen werden 2-Phase-Commit-Protokoll dient zur Synchronisation der Transaktion Distributed Request bedeutet vollständige Verteilung: eine SQL-Anweisung kann mehrere DBMS betreffen mehrere Anweisungen können in einer Transaktion verwendet werden Anmerkung: derzeit gibt es keine Produkte, die Distributed Request Eigenschaft besitzen - 71 - Vorlesung Datenbanken 2 - A. Achilles DRDA-Kommanofluss - 72 - Vorlesung Datenbanken 2 - A. Achilles XML Datenbanken Verarbeitung und Speicherung von XML-Dateien hat eine Reihe von Werkzeugen hervorgebracht, darunter auch "native XML-Datenbanken" Native XML-Datenbanken müssen ein logisches Modell für XML-Dokumente unterstützen und Speicherung und Zugriff daran ausrichten. Das Modell muß zumindest Elemente Attribute PCDATA und die Anordnung innerhalb eines Dokumentes unterstützen. Beispiele dafür: DOM und XPath das XML-Dokument muß die grundlegende Einheit zum (logischen) Zugriff darstellen (vgl. Tabellenzeile bei RDBMS) physisch kann ein beliebiges Modell zu Grunde liegen: hierarchisch relational objekt-orientiert proprietär Anforderungen: das DBMS ist ausgerichtet auf Speicherung von XML-Daten Speicherung und Wiederauffinden erfolgt auf der Basis von Dokumenten Werkzeug, um sicher XML-Dlkumente zu speichern und zu manipulieren Speicherung XML-Dlkumente werden als Einheit gespeichert Modell richtet sich an XML/DOM aus das Modell wird automatisch auf den zu Grunde liegenden Speichermechanismus gemapped Mengen von Dokumenten Abfragen und Änderungen beziehen sich auf eine Menge von Dokumenten (vgl. relationales Konzept) im Unterschied zum relationalen Konzept benötigt nicht jede XML-Datenbank ein Schema, dem die Dokumentenmenge genügt Schema-Unabhängigkeit erhöht Flexibilität Schema-Unabhängigkeit vergrößert das Risiko von Integritätsverletzungen müssen Schema-Strukturen eingehalten werden, sollten entsprechende XML-Datenbanken verwendet werden einige XML-Datenbanken unterstützen DTD-Validation Zukunft: W3C XML-Schema ? Anfragesprache und Performance XPath ermöglicht Anfragen auf Dokumentenmengen Probleme: keine Gruppierung keine Sortierung Datentypen werden nicht unterstützt keine Jois zwischen Dokumenten XQuery neuere Entwicklung von W3 adressiert diese Probleme in der Regel ist Indexierung möglich - 73 - Vorlesung Datenbanken 2 - A. Achilles Updates direkt: suchen, mit XML-API ändern, zurückspeichern XML:DBXUpdate neuere Update-Sprache, die Änderungen innerhalb des Servers ermöglicht wird von einigen XMLDBs untertützt - 74 - Vorlesung Datenbanken 2 - A. Achilles Data Warehouse Woher kommt der Ansatz? Entscheidungsfindung bei Geschäftsvorgängen: DSS (Decision Support System) Interaktive Verbindung von Regeln sowie Intuition von Experten Ziele: ad hoc Modellierung jeweils neuer Situationen Simulation eines Ausschnitts der realen Welt Aufgaben eines DSS Management Information System Standard Reports, Sales Forcast usw. Testen von Hypothesen Modellierung Erzeugung eines Modells und Validierung an Hand der historischen Daten Erkennung unbekannter Trends DSS benötigt Datenbasis Datenbasis muss flexibel und bei OLAP-Anforderungen (OnLine Analytical Processing) online zur Verfügung stehen: Datenbank (Data Warehouse) mit folgenden Eigenschaften: themenorientierte Zusammenfassung von Daten lesende Anfragen Verarbeitung großer Datenmvolumina pro Anfrage regelmäßiges Update durch Daten aus dem OLTP-System (OnLine Transaction Processing), das die aktuellen Geschäftsvorgänge unterstützt aus Sicht der Informatik werden folgende Problemstellungen angesprochen was unterscheidet ein Data Warehouse von einem OLTP spezielle Datenstrukturen, Komprimierung relationale DBMS - Spezialsysteme wie können Daten aus dem OLTP übernommen werden nicht behandelt werden wirtschaftliche Aspekte Problematik historischer Daten Vergleich OLTP - Data Warehouse OLTP Tabellengröße klein Zeilen pro Tabelle wenige Umfang einer Transaktion gering Dauer einer Transaktion kurz # online-Benutzern hoch # Updates sehr hoch Full Table Scan selten historische Daten wenig Normalisierungsgrad hoch Data Warehouse groß extrem viele sehr groß sehr lang einzelne nahezu keine häufig fast ausschließlich stark denomalisiert - 75 - Vorlesung Datenbanken 2 - A. Achilles um bei den zu erwartenden großen Datenmengen pro (lesender) Transaktion eine angemessene Performance zu erreichen De-Normalisierung der OLTP-Datenbank Pre-Join mehrerer Tabellen Ziel ist es, die Join-Operationen weitgehend zu vermeinden führt zu STAR- oder SNOWFLAKE-Schema Pre-Aggregation bereits werden des Ladens werden auf unterschiedlichen Stufen Aggregate gebildet anstelle einer kontinuierlichen Änderung periodisches Update mittels Batch STAR-Schema an Hand eines Beispiels: Fakt-Tabelle ist aus den normalisierten Tabellen mittels Join gewonnen. Enthält neben den Schlüsseln weitgehend sämtliche Information Schlüssel werden als Indexe (Dimensionen) verwendet (ggf. mit zusätzlicher Information) SNOWFLAKE-Schema: Verbindung mehrerer unterschiedlich thematisierter STAR-Schemata Multidimensionale Datenbanken (typisch unter Einbeziehung der Zeit) spezielle Architektur MOLAP (Multidimensional OnLine Analytical Processing) konventionelles RDBMS mit Aufsatz ROLAP (Relational Online Analytical Processing) - 76 - Vorlesung Datenbanken 2 - A. Achilles Spezielle multidimensionale Datenbank-Architektur Vorteil: gute Performance speziell bei beliebigen Ausschnitten Nachteil: Skalierbarkeit Multidimensionale Datenbank: RDBMS mit Aufsatz Vorteil: Flexibilität wesentlich besser Skalierbarkeit wesentlich besser als bei MOLAP Nachteil: Performance hinsichtlich einiger Operationen kann teilweise ausgeglichen werden durch entsprechenden Entwurf DBMS-unterstützte Partitionierung Mehrprozessor-Einsatz Redundanz im Data Warehouse zu beachtende Faktoren: Größe der Daten beeinflußt die Menge des Speicherplatzes und somit der Plattenkosten Änderungshäfugikeit wirkt sich auf CPU und I/O-Zeit aus Obwohl der wesentliche Einsatz lesend ist und somit Redundanz unproblematisch eingesetzt werden könnte, läßt sich Redundanz im Data Warehouse nur in einem bestimmten Bereich gut nutzen: Anlegen und Laden eines Data Warehouses vom OLTP SQL-Anweisungen während off-Zeit des OLTP Snapshot Log sniffing Insert-, Update-, Delete-Trigger spezielle Data Extract Programme während off-Zeit des OLTP SQL-Anweisungen während off-Zeit des OLTP können insbesondere dazu benutzt werden, Pre-Joins anzulegen Snapshot Überführung einer Tabelle in das Data Warehouse Ausnutzen von Replikation Log sniffing mit Spezialprogramm die Logs auf Änderungen untersuchen und diese in das Data Warehouse einbringen - 77 - Vorlesung Datenbanken 2 - A. Achilles Insert-, Update-, Delete-Trigger direktes Übernehmen jeder Änderung spezielle Data Extract Programme während off-Zeit des OLTP dieses Vorgehen bietet die besten Möglichkeiten, vor dem Einfügen Daten zu aggregieren - 78 - Vorlesung Datenbanken 2 - A. Achilles Objekt-Relationale DBMS OO-Methoden der Software-Entwicklung wirken sich auf Anforderungen an DBMS aus. OODB-Manifesto: notwendige Anforderungen komplexe Objekte Objekt-Identität Kapselung Typen und Klassen Typen- und Klassenhierarchie Overriding, Polymorphie, Overloading, Late Binding Berechnungsvollständige Datenbankprogrammiersprache Erweiterbarkeit Persistenz Sekundärspeicherverwaltung Synchronisation und Recovery von Transaktionen Anfragesprachen optionale Anforderungen Mehrfachvererbung statische Typisierung und Typ-Inferenz Verteilung Entwurfstransaktionen Versionen Zwei Ansätze: Erweiterung der RDBMS Neuentwicklung von OODBMS Erweiterung von RDBMS Komplex strukturierte Typen, Typkonstruktoren Objekt-Identifikation und Referenzen Spezialisierung, sowohl auf Objektebene als auch auf Klassenebene Beispiel Oracle 8 komplexe Typen Referenzen SQL3-Standard: baut auf SQL92 auf Erweiterung hinsichtlich ODBMS: Konzept benutzerdefinierter abstrakter Datentypen (ADT) Funktionen der ADT werden analog zu Methoden durch das DBMS verwaltet Tapeltypen (row) auf ADT ist Subtypkonzept mit Vererbung realisiert Redefinition von Attributen und Funktionen ist erlaubt Typkonstruktoren list, set, multiset nicht-atomare Wertebereiche für Attribute, können zusammen mit row verwendet werden Identifikatoren können für Tupel vergeben werden und können als Referenzen verwendet werden SQL3 umfaßt somit strukturelle Konzepte von ODBMS bei Kompatibilität zu vorhergehenden SQLVersionen - 79 - Vorlesung Datenbanken 2 - A. Achilles Einordnung von Oracle8 keine Vererbungshierarchie Beschränkung auf einen Kollektionstyp (Tabelle) Objekt-Views Komplexe Datentypen - Objekttypen User-Defined Datatypes (UDT): Object Types Collection Types Object Types - Objekttypen bestehen aus Namen des Typs Attributmenge Methoden Object Types - Vereinbarung: create type Typ_Adresse as object ( Strasse varchar2(30), Nummer integer, Zusatz varchar2(3), PLZ integer, Ort varchar2(30) ); Object Type Typ_Adresse kann z.B. durchgängig in den DBs der FH für die Speicherung von Adressen verwendet werden Prinzip der Wiederverwendung Hinzufügen oder Modifizieren von Attributen impliziert ausschließlich Änderungen an der Objektdefinition Object Types können in weiteren Objekttypen als Wertebereich eingesetzt werden: create type Typ_Student as object ( Nachname varchar2(40), Vorname varchar2(40), Matrikelnr varchar2(7), Adresse Typ_Adresse ); zyklischer Verweis durch ref-Attribute. Zur Auflösung durch DDL-Compiler ist Forward-Deklaration nötig: create type Typ_XXX; Object Types in Tabellen als Objekttabellen als Attributbereiche Objekttabellen create table T_Student of Typ_Student ( Matrikelnr primary key); Attributbereiche create table T_Student_Heimat ( Matrikelnr varchar2(7), Anschrift T_Anschrift, Matrikelnr primary key); Einfügen: insert into T_Student_Heimat values ('1234567',Typ_Adresse('Strasse',5,'',47110,'Irgendwo') ); - 80 - Vorlesung Datenbanken 2 - A. Achilles Konstruktormethoden werden nicht explizit definiert, vielmehr automatisch bei Anlegen eines neuen Objekttyps erzeugt Zugriff durch Punkt-Operator select T.Matrikelnr, T.Adresse.Ort from T_Student_Heimat T where T.Adresse.PLZ between 40000 and 41200; Methoden für Objekttypen create type Typ_Student_FH as object ( student Typ_Student, member function semester return number, pragma RESTRICT_REFERENCES (semester, WNDS) ); lediglich Typ-Definition der Methode Implementierung erfolgt unabhängig unter Ausnutzung von PL/SQL pragma RESTRICT_REFERENCES dient zum Kontrollieren der Seiteneffekte: Referenzen auf Tabellen und Package-Variablen Methoden für Objekttypen create type body Typ_Student_FH as member function semester return number is maxsem number; begin select max(semester) into maxsem from T_Belegung; return (maxsem); end; end; vorausgesetzt, T_Belegung ist eine geeignete Sicht auf die Fächerbelegung für den jeweiligen Studenten Anforderung an Methoden: die pragma-Anweisung beschränkt die Referenzen auf Tabellen und Package-Variablen damit werden die Seiteneffekte der Methoden eingeschränkt Einsatz von Methoden select T.Student.Name, T_Student_FH.semester() from T_Student_FH where T.Student.Matrkelnr = 47110 Get- und Set-Methoden bei echter Kapselung würden diese Methoden benötigt, jedoch unterstützt Oracle8 dies noch nicht Vergleichsmethoden damit Oracle auf Gleichheit/Ungleichheit testen kann, müssen entsprechende Funktionen bereitgestellt werden dazu gibt es zwei Arten der Festlegung: map member function Vergleichswert return number; order member function Ordnung(x in Typ_...) return integer; Die Map-Methode erzeugt für ein Objekt einen Datenwert, der zur Sortierung verwendet werden kann Die Order-Methode vergleicht zwei Objekte und liefert für < einen negativen Wert = den Wert 0 > einen positiven Wert - 81 - Vorlesung Datenbanken 2 - A. Achilles mögliche Implementationen map member fucntion Vergleichswert return number is begin return Matrikelnr; end; order member function Ordnung( x in Typ_Student_FH ) return integer is begin return Matrikelnr - x.Matrikelnr; end; Kollektionstypen arrays nested tables Kollektionstypen: Arrays create type Werte_array as varray(anzahl) of number; Kollektionstypen: Nested Tables create type Typ_Adressen_Tabelle as table of Typ_Adresse; Kollektionstypen: Nested Tables create table T_Student_Neu ( PK_Matrikelnr varchar2( 7) primary key, Name varchar2(40), Adressen Typ_Adressen_Tabelle) nested table Adressen store as Studenten_Adressen_Liste; Anzahl mit count abfragbar, kann dann in for benutzt werden Realisierung: zwei Tabellen, verbunden über system-generierte ID Einfügen durch "Entschachtelung": Selektieren mittels Cursor: insert into the ( select Adressen from T_Studenten_Neu where PK_Matrikelnr = ... ) values (...) /* Hier stehen die Werte der Adresse */ select T.PK_Matrikelnr, T.Name, cursor ( select * from table (T.Adressen) ) from T_Studenten_Neu T; Referenzen Konzept der Objektidentifikation: Datentyp ref als Referenz auf Row-Objekte noch ausstehend: Objektidentität vollständige Kapselung Vererbung - 82 -
