Datenbanken Entwurf und Design Udo Matthias Munz 1 Inhaltsübersicht Vorbemerkungen Datenspeicherung Bewertung der Datenorganisation Logische Datenorganisation Datenbankarchitektur Datenmodellierung Entity-Relationship-Modell (ERM) Vorgehensweise Das relationale Datenmodell ERM - RDM Daten normalisieren Daten integrieren Udo Matthias Munz Relationenalgebra Standard Query Language (SQL) JOIN Systemarchitektur Join-Design Sort-Merge-Join Transaktionssteuerung Speicher- und Zugriffssteuerung ORACLE 7.1 Datenbankstruktur Speicheroptimierung Dynamische Datenbanksystemstruktur 2 Grundlagen Udo Matthias Munz 3 Datenspeicherung Datenspeicher sequentieller Datenspeicher • • • • • Magnetbänder • Magnetkassetten Datenblock Datenspeicher mit Direktzugriff Magnetplatten Disketten optische Speicher Halbleiterspeicher Bandsprosse Spur 0 Sektor Spur Anlaufzone Udo Matthias Munz Bremszone Zylinder 4 Logische Datenorganisation Baumstruktur Udo Matthias Munz Netzstruktur 5 Welche Anforderungen sollte ein Datenbankmanagementsystem erfüllen? Redundanzfreiheit Vielfachverwendbarkeit (Anwendungsunabhängigkeit) Kein Datenelement soll in der Datenbank mehrfach gepeichert sein; der Speicherplatz ist optimal auszunutzen. Die Datenbank soll so aufgebaut sein, daß sie möglichtst viele Funktionsbereiche bedienen kann. Jeder Benutzer, für den die gespeicherten Daten von Bedeutung sind, soll mit der Datenbank arbeiten können. Benutzerfreundlichkeit Flexibilität Es muß möglich sein, die Struktur der Datenbank, Der Umgang mit der Datenbank soll leicht erlernbar sein. Mit möglichst geringem Aufwand sollen möglichst viele Funktionen des Datenbanksystems eingesetzt werden können unabhängig von den bestehenden Anwendungen, zu ändern. Es sollten Backups im laufendem Betrieb möglich sein. Udo Matthias Munz 6 Welche Anforderungen sollte ein Datenbankmanagementsystem erfüllen? Unabhängigkeit von Betriebssystemen Wirtschaftlichkeit Das System sollte möglichst geringe Die Datenbank sollte ein offenes System sein, d.h. das Wechseln von Hard- und/oder Software sollte keine Anpassungsschwierigkeiten mit sich bringen. Betriebskosten verursachen. Abfragen und Datenmanipulationen sollten mit möglichst wenig Plattenzugriffen erfolgen. Datenintegrität Datenschutz, Datensicherheit und Datenkonsistenz sollten vom System weitgehend unterstützt werden. (Beispiele: Wiederanfahrhilfen, Transaktionserkennung, Passcodeverwaltung, Rollback, Logbuch usw.) Udo Matthias Munz 7 Logische Datenorganisation - Tabelle FNR FNAME FSEM DAUER TAG ZEIT_VON ZEIT_BIS ZAHL 1 2 3 4 5 6 7 8 9 10 11 12 12 Grundlagen der Betriebswirtschaftslehre Finanz- und Investitionswirtschaft Marketing Material- und Fertigungswirtschaft Personalführung Buchführung und Bilanzierung Kosten- und Leistungsrechnung Wirtschaftsmathematik Betriebsstatistik Grundlagen der Volkswirtschaftslehre Wirtschaftsprivatrecht Englisch 1 Französisch 1 2B 4B 4B 4B 4B 2B 4B 2B 2B 2B 4B 2B 2B 90 90 90 90 90 90 90 90 90 90 90 60 60 08.07.1993 15.07.1993 14.07.1993 09.07.1993 08.07.1993 09.07.1993 13.07.1993 12.07.1993 14.07.1993 13.07.1993 12.07.1993 24.06.1993 24.06.1993 830 830 1130 1100 1400 830 1330 830 830 830 1130 830 1330 1000 1000 1300 1230 1530 1000 1500 1000 1000 1000 1300 930 1430 320 200 250 200 160 380 200 320 360 320 180 290 40 Sichten Anwender Tabelle Tabellenzeile Tabellenspalte Tabellenelement Tabellenüberschrift Spaltenbezeichnung Udo Matthias Munz Informatik Datenobjekt Entität Wertebereich Attributswert Attribute Attribut (Eigenschaft) Datenverarbeitung Datei Datensatz Datenfeld Datenelement Datenfeldbezeichnung Datenfeld Mathematik Relation Tupel Domäne Wert Attribut 8 Entitätstyp - Entität Die Entität (Entity) ist das konkrete, individuell identifizierbare Objekt bzw. Exemplar von Dingen, Personen oder Begriffen der realen oder der Vorstellungswelt, für das (auf einem Datenträger) Sachverhalte festzuhalten sind. Beispiele: Individuen: Mitarbeiterin Brech, Schüler Weber... Reale Objekte: Maschine 2, Raum 7, Artikel 4711... Ereignisse:Zahlung,Buchung, Mahnung,Start,Landung... Abstraktes: Unterricht, Dienstleistung, Verarbeitungsart, Zahlungsart... Die Entität ist Mitglied einer Gruppe (Klasse), dem Entitätstyp. Der Kunde Müller ist ein konkretes individuell identifizierbares Objekt, über den Informationen abgespeichert werden müssen. Er gehört zur Gruppe der Kunden. Man kann auch sagen, er ist vom Entitätstyp Kunden. Alle Informationen, die über Kunden abgespeichert werden, sind von der Struktur her gleich. Udo Matthias Munz 9 Attribute Ein Attribut ist jede Einzelheit, die dazu dient, eine Entity zu qualifizieren, zu identifizieren, zu klassifizieren, zu quantifizieren oder ihren Status auszudrücken. "Welche Informationen müssen Sie über das Objekt (Entity) haben oder speichern?" Das Attribut muß die Entity beschreiben, unter der es aufgeführt ist. Jede Entity muß eindeutig identifizierbar sein und mindestens zwei Attribute besitzen. Udo Matthias Munz 10 Attribut Attribute beschreiben die Entitäten. Beispiel: Kunde(KdNr, KName, KAdresse,......) Man unterscheidet zwischen identifizierenden Attributen (z.B.: KdNr, FirmenNr, PersNr....) beschreibenden Attributen (z.B.: KName, MitarbeiterName, ArtikelBez, ..... Udo Matthias Munz 11 Beziehungstyp - konkrete Beziehung Zwischen den Entitätstypen Firma und AZUBI besteht ein Beziehungstyp „ist beschäftigt bei”. Wenn es einen solchen Beziehungstyp gibt, so kann (muß) eine konkrete Beziehung zwischen einem Paar der dazu gehörenden Entitäten bestehen: Beziehungsattribute sind die beschreibenden interessierenden Merkmale der Beziehung. Beispiel zwischen Schüler und Fächern: „belegt“ Udo Matthias Munz 12 Nützliche Beziehungsendnamen arbeiten unter, Chef sein von, auslösen, ausgelöst werden von, belegt, wird belegt, bestellen, bestellt werden in, betrieben werden von, Betreiber sein von, empfangen, gehen an, erhält, ist für, erteilen, erteilt werden von, erteilt, wird erteilt von, erteilt von, Auftraggeber von, erteilt werden, verantwortlich sein für, führt zu, entstehen aus, für, Gegenstand von, gehören zu, umfassen, gekauft werden von, Lieferant sein von, ist in, umfasst, Teil sein von, zusammengesetzt sein aus, Teil von, bestehen aus, Teilnehmer sein von, sein für, unterbreitet für, wird unterbreitet, unterrichtet, wird unterrichtet, wird bestellt in, wird erteilt für ... Udo Matthias Munz 13 Datenbankarchitektur Externes SchemaRaumbelegung konzeptionelles Schema Prüfungsliste Prüfung Prüfungsaushang Aufsicht Prüfungsfach internes Schema Aufsichteneinteilung ERM Dozent Basistabellen Basistabellen DBMS Udo Matthias Munz 14 Entity-Relationship-Modell (ERM) Beziehungsarten 1 : 1 identifizierende Beziehung z.B. Student hat Matrikelnummer 1 : n charakterisierende und klassifizierende Beziehung z.B. Semestergruppe hat Student n : m nicht eindeutige Beziehung, sie muß im Rahmen der Datennormalisierung aufgelöst werden. Beispiel: Student besucht Vorlesung Udo Matthias Munz 15 erweitertes Entity-Relationship-Modell (eERM) 1. Optionale Beziehungen Eine Beziehung ist optional, wenn sie auch den Wert "NULL" haben kann : 1 : c Student Note c : c Student Parkplatz Student belegt Parkplatz c : n Fachbereich Parkplatz Fachbereich hat Parkplätze in der Tiefgarage 1 : cn Fachbereich Student Student hat Note Fachbereich hat Studenten (Ausnahme FB13) c : cn Student Buch n : cm Student Vorlesung cn : cm Bauteil Bauteil Udo Matthias Munz Student hat Buch ausgeliehen Student besucht Vorlesung Bauteil enthält Bauteil (Stückliste) 16 erweitertes Entity-Relationship-Modell (eERM) 2. Min-Max-Notation Entitätsmenge A (a,b) Relation (c,d) Entitätsmenge B 1. Ein Element der Entitätsmenge A steht in Relation mit • mindestens c Elementen der Entitätsmenge B • höchstens d Elementen der Entitätsmenge B 2. Ein Element der Entitätsmenge B steht in Relation mit • mindestens a Elementen der Entitätsmenge A • höchstens b Elementen der Entitätsmenge A 3. Gilt b > 1 und d > 1 so erweitert sich die Relation zu einer Beziehungsentität Entitätsmenge A 1 : m Relation n : 1 Entitätsmenge B 4. Die Beziehungsentität enthält die Schlüsselattribute der verknüpften Entitätsmengen als Kombinationsschlüssel Udo Matthias Munz 17 Vorgehensweise 1. Schritt : Sammeln der Datenelemente Datenliste PS Datenfeld Typ Länge PS : Datenfeld : Typ : Länge : NULL : Entität : Alias : Beschreib. : Udo Matthias Munz NULL Entität Alias Beschreibung Primärschlüssel [Ja / Nein] Bezeichnung des Datenfeldes, wie es gespeichert werden soll Datentyp (z.B. Integer, Währung, Text, ext.) maximale Zahl der Zeichen pro Wert leeres Datenfeld zulässig? [Ja / Nein] Welcher Entitätsmenge wird das Datenfeld zugeordnet Alternative Bezeichnungen für das Datenfeld Weiter Angaben zum Datenfeld (z.B. zulässige Werte etc.) 18 Vorgehensweise 2. Schritt : Datenobjekte (Entitäten) finden Datenliste PS Datenfeld J Typ Länge NULL Entität Alias AUFNR INT 10 N Auftragsnummer DATUM DAT 8 N Auftragsdatum ARTNR INT 10 N Artikelnummer ARTIKEL TXT 80 J Artikelbezeichnung KNR INT 10 N Kundennummer KNAME TXT 50 J Auftrag Beschreibung Kundenname Artikel Kunde Udo Matthias Munz 19 Vorgehensweise 3. Schritt : Beziehungen zwischen den Entitätsmengen beschreiben und n:m-Beziehungen auflösen (->Verbindungsentität) Kunde (1,1) • • • • erteilt Kunde erteilt einen oder mehrere Aufträge Ein Auftrag ist eindeutig einem Kunden zugeordnet Ein Auftrag enthält einen oder mehrere Artikel Ein Artikel ist in keinem, einem oder mehreren Aufträgen enthalten (1,n) (0,n) Auftrag (1,n) enthält Artikel (1,1) (1,1) (1,n) enthält Udo Matthias Munz Auftragsposition (1,n) enthält 20 Vorgehensweise 4. Schritt : Schlüssel- und Datenfelder den Entitätsmengen zuordnen und die Entitätsmengen in die Datenliste eintragen Kunde KNR KNAME KADRESSE (1,1) ert AUFNR DATUM KNR AUFNR ARTNR MENGE Auftrag (1,1) enthält Artikel ARTNR ARTIKEL PREIS (1,1) (1,n) Udo Matthias Munz • Über die Schlüsselfelder werden die Entitätsmengen miteinander verknüpft. • Die Kundennummer (KNR) erscheint in der Entitätsmenge Auftrag als Fremdschlüssel und stellt die Verbindung zur Entitätsmenge Kunde her. Auftragsposition (1,n) enthält 21 Vorgehensweise 5. Schritt : Konsistenz- und Integritätsbedingungen beschreiben Konsistenz Die gespeicherten Daten müssen in sich und zur Realität widerspruchsfrei sein. Integrität Die gespeicherten Daten müssen vollständig und korrekt sein. Datentypkonsistenz Schlüsselkonsistenz Wertebereichkonsistenz Beziehungsintegrität Welche Werte darf ein Fremdschlüssel annehmen? [nur Werte eines PS, auch NULL, jeden Wert] Wie ist im Falle einer Löschung bzw. Änderung zu verfahren? Die Löschung/Änderung des Primärschlüssels führt zur Löschung der Datensätze in denen der Schlüssel enthalten ist löscht/ändert den Schlüsselwert wird nur durchgeführt, wenn kein Datensatz mit dem Schlüsselwert existiert. z.B.: Ein Schlüsselfeld muß stets vom gleichen Datentypusein. [eindeutiger Wert, nicht NULL] Ist der zulässige Wertebereich kleiner als die dem Datentyp entsprechende Wertemenge, so muß der Wertebereich festgelegt und dem System für die Eingabekontrolle mitgeteilt werden. rechnerische Konsistenz z.B. sind die systembedingten Rundungen von Dezimalzahlen akzeptabel Logik des Geschäftsvorfalls Wann dürfen welche Daten eingegeben werden? Die Ausführung eines Auftrags erfolgt erst nach Eingang der Zahlung. Udo Matthias Munz 22 Das relationale Datenmodell Entity-Relationship-Modell relationales Datenmodell Entität Relationship Relation = zweidimensionale Tabelle = Datenobjekt = Beziehung Student Mtnr Name Student ( Mtnr, Name, SemGr, Adresse) Entität Schlüsselattribut SemGr. Adresse Udo Matthias Munz Relation mit einem identifizierenden Schlüsselattribut 23 ERM - RDM Student (0,m) belegt (0,n) Mtnr Name SemGr Adresse Vorlesung Vorlnr Fach Semester Raum Zeit Student ( Mtnr, Name, SemGr, Adresse) Fach ( Vorlnr, Fach, Semester, Raum, Zeit) Belegung (Mtnr, Vorlnr) Udo Matthias Munz 24 Daten normalisieren 1. Normalform Eine Relation ist in der 1. Normalform, wenn kein Attribut enthalten ist, zu dem es pro Datensatz mehrere Attributswerte geben kann, d.h. wenn in jeder Zeile und Spalte nur atomare, nicht weiter zerlegbare Werte gespeichert werden. Auftrag (Aufnr, Artikelnr, Artikelbezeichnung, Menge, Preis, Kundennr, Kundenname, Adresse, Datum) Auftrag Bestellartikel (Aufnr, Kundennr, Kundenname, Adresse, Datum) (Aufnr, Artikelnr, Artikelbezeichnung, Menge, Preis) Primärschlüssel Udo Matthias Munz Zusammengesetzter Schlüssel 25 Daten normalisieren 1. Normalform Als Beispiel sollen die Tourendaten verwendet werden. Die Tabelle Tourendaten genügt nicht der 1. NF, da die dritte Spalte noch weiter aufgeteilt werden könnte. Die geteilten Tabellen und entsprechende Beziehung zueinander erfüllt die 1. NF Tourendaten TourenZiele TourenTermine (TourendatenNr, Bezeichnung, Länge, Termine) (TourenNummer, Tour, Kurzbeschreibung,Länge, Grad, Start, Ziel) (TourTerminNr, TourenNummer, Beginn, Ende , MaxTeilnehmer) Primärschlüssel Udo Matthias Munz Zusammengesetzter Schlüssel 26 Daten normalisieren 2. Normalform Gilt nur bei zusammengesetztem Primärschlüssel! Eine Relation ist in der 2. Normalform, wenn sie in der 1. Normalform ist und keine Attribute enthält, die in einer 1:1 Beziehung zum Primärschlüssel oder Teilen des Primärschlüssels stehen, d.h. jede Spalte die nicht zum Primärschlüssel gehört, ist vom kompletten PS abhängig. Auftrag Bestellartikel (Aufnr, Kundennr, Kundenname, Adresse, Datum) (Aufnr, Artikelnr, Artikelbezeichnung, Menge, Preis) Auftrag Bestellartikel Artikel Preis (Aufnr, Kundennr, Kundenname, Adresse, Datum) (Aufnr, Artikelnr, Menge) (Artikelnr, Artikelbezeichnung) (Artikelnr, Preis) Udo Matthias Munz 27 Daten normalisieren 3. Normalform Eine Relation ist in der 3. Normalform, wenn sie in der 2. Normalform ist und keine Attribute enthält, die untereinander abhängig sind. Auftrag Bestellartikel Artikel Preis (Aufnr, Kundennr, Kundenname, Adresse, Datum) (Aufnr, Artikelnr, Menge) (Artikelnr, Artikelbezeichnung) (Artikelnr, Preis) Auftrag Kunde Anschrift Bestellartikel Artikel Preis (Aufnr, Kundennr, Datum) (Kundennr, Kundenname) (Kundennr, Adresse) (Aufnr, Artikelnr, Menge) (Artikelnr, Artikelbezeichnung) (Artikelnr, Preis) Udo Matthias Munz 28 Daten integrieren Auftrag Kunde Anschrift Bestellartikel Artikel Preis (Aufnr, Kundennr, Datum) (Kundennr, Kundenname) (Kundennr, Adresse) (Aufnr, Artikelnr, Menge) (Artikelnr, Artikelbezeichnung) (Artikelnr, Preis) Auftrag Kunde Bestellartikel Artikel Udo Matthias Munz Auftrag 1:n 1:n Kunde 1:1 Bestellartikel Anschrift n:1 Artikel 1:1 Preis (Aufnr, Kundennr, Datum) (Kundennr, Kundenname, Adresse) (Aufnr, Artikelnr, Menge) (Artikelnr, Artikelbezeichnung, Preis) 29 Datenbankdesign 1 An der RBS soll ein Schulverwaltungsprogramm erstellt werden. In dieser Datenbank sind Informationen zu Lehrfächern, Schüler, Lehrer und Klassen festzuhalten. Folgende Fragen sollen beantwortet werden können: Bei welchem Lehrer hat ein Schüler Unterricht? Aus welcher Klasse kommt ein Schüler? Welche Fächer belegt ein Schüler? Welche Fächer gibt ein Lehrer? In welchen Klassen unterrichtet ein Lehrer? Welche Noten hat ein Schüler in seinen belegten Fächern? . Entwerfen Sie zu diesem Problem ein EntityRelationship-Diagramm. Bestimmen Sie die Relationen (Tabellen) und die dazugehörigen Attribute. Kennzeichnen Sie die Primärund Fremdschlüssel Udo Matthias Munz 30 Anwendungsentwicklung und Datenbankadministration Udo Matthias Munz 31 Beispiele UNION : Aus den Tabellen "Kunde" und "Vorgang" wird eine Tabelle erzeugt. INTERSECTION : Aus den Tabellen "Kunde" und "Vorgang" werden alle Kundennummern mit einem offenen Vorgang in eine Tabelle gestellt. DIFFERENCE : Aus den Tabellen "Kunde" und "Vorgang" werden alle Kunden selektiert, die keinen offenen Vorgang aufweisen. CARTESIAN PRODUCT : Aus den Tabellen "Vorgang" und "Fahrzeug" wird eine Tabelle erzeugt. PROJECTION : Alle Kundennamen aus der Kundentabelle. SELECTION : Aus der Kundentabelle alle Daten des Kunden mit K# = 4711. JOIN : Alle Kunden mit Adresse aus den Tabellen "Kunde" und "Adresse". DIVISION : Alle Fahrzeuge, die in allen Angeboten enthalten sind. Udo Matthias Munz 32 Structured Query Language (SQL) Funktionen Datendefinition [DDL] • CREATE • ALTER • DROP Datenmanipulation [DML] • SELECT • UPDATE • INSERT • DELETE Kontrolle und Steuerung • GRANT • LOCK • COMMITT • ROLLBACK Udo Matthias Munz CREATE TABLE Student Mtknr, char(8), Name, char(30), SemGr, char(6); SELECT Name FROM Student WHERE SemGr= "10BW4A"; GRANT USER Regier IDENTIFIED BY "Dozent" ; 33 JOIN SELECT <merkmal> [,<merkmal>, ...] FROM tabelle [, tabelle, ...] WHERE <selektionsprädikat> [AND <selektionsprädikat>] ; Bulk-Join Teta-Join Natural-Join Semi-Join => => => => ohne WHERE-Klausel (kartesisches Produkt) Multiple-Join Outer-Join => => Join mit mehr als zwei Tabellen Restricted-Join Equi-Join Auto-Join => => => Durch AND werden weitere Bedingungen eingefügt mit WHERE-Klausel identische Spalten werden nur einmal angezeigt. Nur Spalten einer Tabelle (senkt das Kommunikationsvolumen bei verteilter Datenhaltung Datensätze der ersten Tabelle, denen keine Datensätze der zweiten Tabelle zugeordnet werden können, werden mit einem leeren Datensatz verknüpft. Bedingung enthält nur Gleichheitszeichen Join einer Tabelle auf sich selbst Vergleichende Bewertung Inner schneller als Equi schneller als Restricted schneller als Udo Matthias Munz Outer Non-Equi NO-Restricted 34 Systemprozeß Ad Hoc Abfrage Anwendungsprogramm Übersetzung und Optimierung Transaktionen- und Cursorverwaltung Speicher- und Zugriffsverwaltung Systemtabellen Udo Matthias Munz Benutzertabellen Systemnutzung (Job-Design) • Optimierung durch eine zweckmäßige Reihenfolge der Operationen (algebraische Optimierung) • Optimierung der JOIN-Operationen Systemadministration • Datensicherheit • Vermeidung von Dateninkonsistenz • Vermeidung von Blockaden bei der Nutzung verteilter Systeme Systemkonfiguration • Optimierung der Zugriffszeiten durch Systemauswahl und Konfiguration Datenbankdesign • Optimierung der Datenbankstruktur (ERM / RDM) • optimale Größe der Tablespaces und Extens 35 Parsing SQL-Befehle werden als Text an das Datenbankmanagementsystem übermittelt. Dieses muß den Text lesen und interpretieren (Parsing). lexikalische Analyse Tabellensuche Spaltenattributsuche Typ- und Constraint-Vergleich für die Spaltenattribute Sperrung (Parse Lock) Berechtigungsprüfung Ausführungsplanung Udo Matthias Munz Parsing benötigt etwa die Hälfte der Antwortzeit. Bekannte SQL-Befehle werden (von den meisten DBMS) wiedererkannt und müssen nicht erneut das Parsing durchlaufen (Shared SQL) Allerdings müssen die Befehle absolut identisch sein! Für SQL-Befehle wird ein Hash-Wert errechnet und im Library Cache gespeichert. Nutzung durch Bind Variables, die anstelle von Werten im SQL-Text verwendet werden, verhindert ein erneutes Übersetzen. 36 Optimierung Für die Ausführung der übersetzten SQL-Befehle erstellt das DBMS einen Ausführungsplan, wobei vom DBMS Optimierungsregeln eingesetzt werden. Regelbasierte Optimierung Die SQL-Befehle werden nach festen Regeln analysiert und die Reihenfolge der Operationen danach festgelegt. (siehe folgende Seite) Aufwandbezogene Optimierung Hierbei werden internen Statistiken und Strukturmerkmale der Datenbank (z.B. Indizes) analysiert um den schnellsten Weg zu den gesuchten Daten zu finden. Oracle ab Version 7 Manuelle Optimierung Durch einen Kommentar im SELECT Befehl kann ein Ausführungshinweis an den Optimierer gegeben werden. SELECT /* FULL */ name, . FROM.. Über den Befehl EXPLAIN PLAN sind bei Oracle (nicht leicht zu lesende) Informationen über das tatsächlich gewählte Optimierungsverfahren zu erhalten. Udo Matthias Munz 37 Tabellengröße Bei kleinen Tabellen ist ein vollständiges Lesen der Tabelle schneller als eine Selektion. Häufig benötigte kleine Tabellen können im Cache gepuffert werden: ALTER TABLE <tabellenname> CACHE; Bei großen Tabellen spielen Indizes eine herausragende Rolle. Index-Selektivität Ein eindeutiger Schlüssel (Primärschlüssel) liefert die höchste Selektivität mit dem Wert 1. Je größer der Wert, um so mehr Datensätze werden im Fall eines SELECT Befehls gelesen. Bei Oracle heißt der Wert "badness". Er kann für jedes Attribut errechnet werden, um festzustellen, ob es als Indexfeld geeignet ist. Udo Matthias Munz 38 Cache least recently used (LRU) SQL-Statements Library Cache Functions Triggers Stored Procedures Im Library Cache werden aufgerufene Befehle gespeichert, um bei einem wiederholten Aufruf des gleichen Befehls eine schnellere Ausführung zu erreichen. Dies gelingt jedoch nur dann, wenn der Anwendungsentwickler gebunden Variablen verwendet, so daß die SQL-Statements, Functions etc. identisch sind. Datenbankadministrator und Anwendungsentwickler müssen bei der Optimierung der Datenbankanwendungen zusammenarbeiten! Udo Matthias Munz 39 Join-Design SELECT Datum FROM Kunde, Auftrag WHERE = Kunde.KNR = Auftrag.KNR AND Name = "Müller" Tabelle := Datum (Name = "Müller" Datum Name = "Müller" (Kunde |X| Kunde.KNR = Auftrag.KNR , Auftrag )) |X| Kunde.KNR = Auftrag.KNR Tabelle := Datum (Auftrag.KNR, Datum (Auftrag) (|X| Kunde.KNR = Auftrag.KNR ) Kunde.KNR Kundentabelle Auftragstabelle (Name = "Müller"(Kunde))))) SELECT Datum FROM (SELECT KNR, Datum FROM Auftrag WHERE AUFTRAG.KNR = (SELECT KNR FROM Kunde WHERE Name = "Müller"))) Udo Matthias Munz 40 Fakten- und Dimensionstabellen Faktentabellen enthalten die betriebswirtschaftlich relevanten numerischen Meßgrößen wie Umsatz, Kosten, Leistung. Dimensionstabellen liefern Angaben zu den Dimensionen (z.B. Zeit, Kunde, Mitarbeiter, Artikel) auf die sich die Faktentabellen via Schlüsselattribute beziehen. Join ist die Verbindung zwischen zwei Tabellen wobei häufig eine Faktentabelle mit einer Dimensionstabelle verbunden wird (z.B. Kunde -Auftrag). Ein Join ist grundsätzlich nur zwischen zwei Tabellen möglich. Zur Verknüpfung mehrere Tabellen bestehen zwei alternative Verfahren: • Paarweise Join • Star-Abfrage Udo Matthias Munz 41 Paarweise Join Faktentabelle Auftrag versus Dimensionstabelle Kunde Star-Abfrage Dimensionstabelle Kunde Dimensionstabelle Artikel Dimensionstabelle Lieferant JOIN Zwischenergebnis 1 Auftrag & Kunde Dimensionstabelle Artikel Kartesisches Produkt Kunde & Artikel & Lieferant Faktentabelle Auftrag JOIN Zwischenergebnis 2 Auftrag & Kunde & Artikel Dimensionstabelle Lieferant JOIN JOIN Abfrageergebnis Auftrag & Kunde & Artikel & Lieferant Abfrageergebnis Auftrag & Kunde & Artikel & Lieferant Eigenschaften: - viele Operationen - kleine Zwischentabellen Effizient bei großen Datenmengen Udo Matthias Munz Eigenschaften: - wenige Operationen - große Zwischentabelle Effizient bei kleinen Datenmengen 42 Sort-Merge-Join KNR Name 4711 Müller 5612 Maier 3254´ Huber Adresse 81243 München 71324 Stuttgart 12312 Berlin n x m Operationen KNR 4711 AUFNR 2137 2231 2178 2337 Datum 12.10.97 13.10.97 15.10.97 12.10.97 KNR 4711 3254 4711 5511 AUFNR 2137 2231 2178 2337 2137 2231 5612 .......... Vorsortierung beider Tabellen nach dem Vergleichsattribut KNR 3254 4711 5612 Name Huber Müller Maier Adresse 12312 Berlin 81243 München 71324 Stuttgart AUFNR 2231 2137 2178 2337 Datum 13.10.97 12.10.97 15.10.97 12.10.97 KNR 3254 4711 4711 5511 max: n + m Operationen Udo Matthias Munz 43 Speicher- und Zugriffssteuerung (1) Mehrwegbaum (B*-Tree) 01 02 03 04 05 10 15 06 07 08 09 11 12 13 14 RID 16 17 18 19 physische Zeilenadresse [ROW ID] (oder Liste mit Zeilenadressen) Die Indexdatei ist hierarchisch unterteilt. Beim Suchen nach dem Datenfeld mit dem Wert 12 sind statt 12 nur 4 Vergleichsoperationen erforderlich. Eigenschaften: • Gute Performanz bei hoher Kardinalität, d.h. das Suchkriterium weist eine hohe Zahl unterschiedlicher Wertausprägungen im Verhältnis zu den Tabellenzeilen auf. • Schlechte Performanz bei der Suche nach nicht indizierten Datenfeldern mit geringer Kardinalität. Udo Matthias Munz 44 Speicher- und Zugriffssteuerung (2) Bitmap-Indizierung Für jede Ausprägung einer zu indizierenden Tabellenspalte wird eine Bitfolge angelegt, die kennzeichnet, ob in der entsprechenden Zeile der Tabelle der Wert anzutreffen ist (1) oder nicht (0). Geschäftskunde Privatkunde Sonstige Merkmalsausprägung 1 0 0 0 1 0 0 0 1 1 1 1 1 0 1 0 1 .... 0 1 1 1 0 1 1 0 0 0 0 0 0 1 0 1 0 .... 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 .... Bitmap-Index Eigenschaften: • extrem platzsparend bei geringer Merkmalszahl Bei 1.000.000 Kundensätzen werden 3 Mio. Bit = 375 KB benötigt. Im Vergleich dazu benötigt das B*-Tree Verfahren dazu 4MB. Allerdings benötigt jede weitere Ausprägung 125 KB. • Bei Häufigkeitszählungen entfällt der Zugriff auf die Tabelle. • Schnell bei kombinierten Abfragen Udo Matthias Munz 45 Komprimierung von Bitmaps (1) 0100 1011 0101 1110 1100 1000 1011 0110 1010 0110 111000001100000000000000000010110000000000000000000010100110 Rekonstruierte Bitliste Darstellung erfordert 10 4-Bit-Blöcke für insgesamt 256 4-Bit-Blöcke Udo Matthias Munz 46 Komprimierung von Bitmaps (2) 1 k ......... KennBit Bitmuster oder Nullfolge Ist das Kenn-Bit gleich 1, werden die folgenden k-1 Bits als unkomprimierte Bitfolge interpretiert. Ist das Kenn-Bit gleich 0, so wird der Inhalt der folgenden k-1 als binäre Ganzzahl interpretiert, die angibt, wieviele aufeinander folgende 0-en auf diese Weise komprimiert dargestellt werden. Je weniger 1en in der Bitfolge eines Wertes auftreten, desto wirksamer ist die Komprimierung. Noch effizientere Verfahren resultieren aus der Verwendung mehrerer Kennbits. Udo Matthias Munz 47 Auswertung komplexer Verbundoperationen DimensionsRelationen Kunde Branche Detail Relation Bestell-Nr. DSSAnfrage Udo Matthias Munz Produkt ProduktGruppe Kunden-Nr. Artikel-Nr. Filial-Nr. Filiale Region Menge Datum SELECT SUM(Menge) FROM Bestellung, Kunde, Produkt, Filiale WHERE Bestellung.Kunden-Nr. = Kunde.Kunden-Nr. AND Bestellung.Artikel-Nr. = Produkt.Artikel-Nr. AND Bestellung.Filial-Nr. = Filiale.Filial-Nr. AND Kunde.Branche = ‘Elektronik’ AND Produkt.Produktgruppe = ‘Telefon’ AND Filiale.Region = ‘Nord’; 48 Grundformen verteilter Datenbanken Verteilung kompletter Tabellen auf verschieden Server mit der Folge, daß bei einem Join auf zwei Server zugegriffen werden muß. Dieses Verfahren beherrschen die meisten DBMS, wobei die verteilten Datenbanken auf unterschiedlichen Plattformen laufen können. Verteilung einer Tabelle auf verschiedene Server ist bei sehr großen Tabellen erforderlich, wobei sowohl eine horizontale als auch eine vertikale Teilung möglich ist. Tabelle 1 Tabelle 2 Tabelle 2 horizontal vertikal Udo Matthias Munz 49 Lösungen für die Speicherung großer Tabellen Array-Speicher Speichermedien sind miteinander verkettet; Fassungsvermögen mehrere Terabytes; Problem: Datensicherung Raid-Array Plattenkombinationen mit redundanter Datenspeicherung. Vorteil: Ausfallschutz; Plattenteile können bei laufendem Betrieb ausgewechselt werden. Nachteil: Langsame Verarbeitung; Schutz bezieht sich aber nur auf physische Fehler. Logische Fehler wirken sich hingegen redundant und ggf. irreparabel aus. 64-Bit-Technologie Mit ihr lassen sich bis zu 2 Gigabyte im Hauptspeicher bearbeiten, beschleunigt folglich die Prozesse ohne jedoch das Problem der Massendatenspeicherung zu lösen. Mehrprozessorsysteme Hierzu fehlen bislang entsprechende Betriebs- und Datenbankmanagementsysteme, die dieses Leistungspotential ausschöpfen. Objektorientierte Datenbanken Vom Denkansatz versprechen sie eine zweckmäßige Lösung über die Instanzenbildung. Aktuelle OODBMS leisten dies jedoch noch nicht. Udo Matthias Munz 50 Datensicherung Zweck Zweck der Datensicherung ist die Wiederherstellung eines konsistenten Datenbestandes nach einem Störfall. Verfahren Komplettsicherung Es werden immer alle Daten gesichert. Differenzsicherung Es werden nur die Änderungen zur vorhergehenden Datensicherung gespeichert. Technik Die von den Betriebssystemen UNIX und Windows angebotenen Sicherungssysteme sind für eine professionelle Sicherung großer Datenbestände nicht geeignet. Von Anbietern für Großraumspeicher werden entsprechende Managementsysteme angeboten. Organisation Datensicherungsplan Es ist in einem Plan festzuschreiben, wie die Datensicherung durchzuführen ist. Notfallplan Der Notfallplan beschreibt das Vorgehen bei der Rekonstruktion der Datenbestände nach einem Störfall. Udo Matthias Munz 51 Richtlinien zur Verwaltung der Speicherressourcen Trenne Data Dictionary- und Benutzerdaten. Trenne die Daten unterschiedlicher Anwendungen. Speichere Tablespaces auf verschiedenen Platten um I/O-Konflikte auszuschließen. Trenne Rollbacksegmente von Datensegmenten, um den Verlust von Daten durch Plattenabsturz zu verhindern. Halte individuelle Tablespaces offline. Schränke die Datenbanknutzung für einen Tablespace ein auf high update performance read only activity temporary segment storage Mache Backups von individuellen Tablespaces Lege Default-Speicherparameter für Objekte fest, die in einer Tablespace angelegt werden sollen. Lege Default-Speicherparameter für eine Tablespace zur Verwaltung spezieller Objekte fest. Vergib Tablespace-Anteile an die Anwender. Oracle-Avices Udo Matthias Munz 52 Kostenfaktoren bei Datenbanksystemen Wartung Lizenz 2% 8% Beratung 8% Schulung 2% Entwicklung 20% Administration 60% Computerzeitung 7/98 Im Durchschnitt entfallen 60 % der Kosten eines Datenbanksystems auf die Systemadministration. Udo Matthias Munz 53 Datenbankadministration Datenadministration Benutzer- und Sicherheitsadministration Systemadministration Installation Datensicherung und -wiederherstellung Datenarchivierung Datenreorganisation Datenverifikation Laufzeitkontrolle Performance-Analyse und Tuning Auditing Massendatentransfer Quelle: H. Schöning "Datenbankadministration" in Datenbank Rundbrief 17.5.1996 S. 77-81 Udo Matthias Munz 54 Entity Relationship Modell Udo Matthias Munz 55