1

Udo Matthias Munz

DatenbankenEntwurf und Design

2

Udo Matthias Munz

Inhaltsübersicht

VorbemerkungenDatenspeicherungBewertung der DatenorganisationLogische DatenorganisationDatenbankarchitekturDatenmodellierungEntity-Relationship-Modell (ERM)VorgehensweiseDas relationale DatenmodellERM - RDMDaten normalisierenDaten integrieren

RelationenalgebraStandard Query Language (SQL)JOINSystemarchitekturJoin-DesignSort-Merge-JoinTransaktionssteuerungSpeicher- und ZugriffssteuerungORACLE 7.1DatenbankstrukturSpeicheroptimierung Dynamische Datenbanksystemstruktur

3

Udo Matthias Munz

Grundlagen

4

Udo Matthias Munz

Datenspeicherung

DatenspeicherDatenspeicher

sequentieller Datenspeichersequentieller Datenspeicher Datenspeicher mit DirektzugriffDatenspeicher mit Direktzugriff

• Magnetbänder• Magnetkassetten

• Magnetplatten• Disketten• optische Speicher• Halbleiterspeicher

Datenblock

Anlaufzone Bremszone

Spur

Bandsprosse Spur 0

Sektor

Zylinder

5

Udo Matthias Munz

Logische Datenorganisation

Baumstruktur Netzstruktur

6

Udo Matthias Munz

Welche Anforderungen sollte ein Datenbankmanagementsystem erfüllen? Redundanzfreiheit Kein Datenelement soll in der Datenbank

mehrfach gepeichert sein; der Speicherplatz ist optimal auszunutzen.

Vielfachverwendbarkeit (Anwendungsunabhängigkeit) 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 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

Flexibilität Es muß möglich sein, die Struktur der Datenbank, unabhängig von den bestehenden Anwendungen, zu ändern. Es sollten Backups im laufendem Betrieb möglich sein.

7

Udo Matthias Munz

Welche Anforderungen sollte ein Datenbankmanagementsystem erfüllen? Unabhängigkeit von Betriebssystemen Die

Datenbank sollte ein offenes System sein, d.h. das Wechseln von Hard- und/oder Software sollte keine Anpassungsschwierigkeiten mit sich bringen.

Wirtschaftlichkeit Das System sollte möglichst geringe 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.)

8

Udo Matthias Munz

Logische Datenorganisation - Tabelle

FNR FNAME FSEM DAUER TAG ZEIT_VON ZEIT_BIS ZAHL 1 Grundlagen der Betriebswirtschaftslehre 2 B 90 08.07.1993 830 1000 320 2 Finanz- und Investitionswirtschaft 4 B 90 15.07.1993 830 1000 200 3 Marketing 4 B 90 14.07.1993 1130 1300 250 4 Material- und Fertigungswirtschaft 4 B 90 09.07.1993 1100 1230 200 5 Personalführung 4 B 90 08.07.1993 1400 1530 160 6 Buchführung und Bilanzierung 2 B 90 09.07.1993 830 1000 380 7 Kosten- und Leistungsrechnung 4 B 90 13.07.1993 1330 1500 200 8 Wirtschaftsmathematik 2 B 90 12.07.1993 830 1000 320 9 Betriebsstatistik 2 B 90 14.07.1993 830 1000 360 10 Grundlagen der Volkswirtschaftslehre 2 B 90 13.07.1993 830 1000 320 11 Wirtschaftsprivatrecht 4 B 90 12.07.1993 1130 1300 180 12 Englisch 1 2 B 60 24.06.1993 830 930 290 12 Französisch 1 2 B 60 24.06.1993 1330 1430 40

FNR FNAME FSEM DAUER TAG ZEIT_VON ZEIT_BIS ZAHL 1 Grundlagen der Betriebswirtschaftslehre 2 B 90 08.07.1993 830 1000 320 2 Finanz- und Investitionswirtschaft 4 B 90 15.07.1993 830 1000 200 3 Marketing 4 B 90 14.07.1993 1130 1300 250 4 Material- und Fertigungswirtschaft 4 B 90 09.07.1993 1100 1230 200 5 Personalführung 4 B 90 08.07.1993 1400 1530 160 6 Buchführung und Bilanzierung 2 B 90 09.07.1993 830 1000 380 7 Kosten- und Leistungsrechnung 4 B 90 13.07.1993 1330 1500 200 8 Wirtschaftsmathematik 2 B 90 12.07.1993 830 1000 320 9 Betriebsstatistik 2 B 90 14.07.1993 830 1000 360 10 Grundlagen der Volkswirtschaftslehre 2 B 90 13.07.1993 830 1000 320 11 Wirtschaftsprivatrecht 4 B 90 12.07.1993 1130 1300 180 12 Englisch 1 2 B 60 24.06.1993 830 930 290 12 Französisch 1 2 B 60 24.06.1993 1330 1430 40

Tabelle Datenobjekt Datei RelationTabellenzeile Entität Datensatz TupelTabellenspalte Wertebereich DatenfeldDomäneTabellenelement Attributswert Datenelement WertTabellenüberschrift Attribute DatenfeldbezeichnungSpaltenbezeichnung Attribut (Eigenschaft) DatenfeldAttribut

Tabelle Datenobjekt Datei RelationTabellenzeile Entität Datensatz TupelTabellenspalte Wertebereich DatenfeldDomäneTabellenelement Attributswert Datenelement WertTabellenüberschrift Attribute DatenfeldbezeichnungSpaltenbezeichnung Attribut (Eigenschaft) DatenfeldAttribut

AnwenderAnwender DatenverarbeitungDatenverarbeitungInformatikInformatik MathematikMathematik

Sichten

9

Udo Matthias Munz

Entitätstyp - EntitätDie 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.

10

Udo Matthias Munz

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.

11

Udo Matthias Munz

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, .....

12

Udo Matthias Munz

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“

13

Udo Matthias Munz

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 ...

14

Udo Matthias Munz

Datenbankarchitektur

Externes SchemaRaumbe-legung

Prüfungs-liste

Prüfungs-aushang

Aufsichten-einteilung

internes Schema

konzeptionellesSchema

BasistabellenBasistabellen

Prüfung

Prüfungsfach Dozent

AufsichtERM

DBMS

15

Udo Matthias Munz

Entity-Relationship-Modell (ERM)

1 : 1 1 : 1 identifizierende Beziehungz.B. Student hat Matrikelnummer

1 : n 1 : n charakterisierende und klassifizierendeBeziehungz.B. Semestergruppe hat Student

n : m n : m nicht eindeutige Beziehung, sie mußim Rahmen der Datennormalisierungaufgelöst werden.Beispiel: Student besucht Vorlesung

Beziehungsarten

16

Udo Matthias Munz

erweitertes Entity-Relationship-Modell (eERM)

1. Optionale Beziehungen 1. Optionale Beziehungen

Eine Beziehung ist optional, wenn sie auch den Wert "NULL" haben kann :

FachbereichFachbereich ParkplatzParkplatz c : n c : n

StudentStudent VorlesungVorlesung n : cm n : cm

BauteilBauteil BauteilBauteil cn : cm cn : cm

StudentStudent NoteNote 1 : c 1 : c

StudentStudent ParkplatzParkplatz c : c c : c

FachbereichFachbereich StudentStudent 1 : cn 1 : cn

StudentStudent BuchBuch c : cn c : cn

Student belegt Parkplatz

Student besucht Vorlesung

Student hat Buch ausgeliehen

Student hat Note

Fachbereich hat Parkplätze in derTiefgarage

Fachbereich hat Studenten(Ausnahme FB13)

Bauteil enthält Bauteil (Stückliste)

17

Udo Matthias Munz

erweitertes Entity-Relationship-Modell (eERM)

2. Min-Max-Notation 2. Min-Max-Notation

RelationEntitäts-menge A

(a,b) (c,d) Entitäts-menge 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

4. Die Beziehungsentität enthält die Schlüsselattribute der verknüpften Entitätsmengen als Kombinationsschlüssel

RelationEntitäts-menge A

1 : m n : 1 Entitäts-menge B

18

Udo Matthias Munz

Vorgehensweise

1. Schritt : Sammeln der Datenelemente1. Schritt : Sammeln der Datenelemente

Datenliste

PS Datenfeld Typ Länge NULL Entität Alias Beschreibung

PS : Primärschlüssel [Ja / Nein]Datenfeld : Bezeichnung des Datenfeldes, wie es gespeichert werden sollTyp : Datentyp (z.B. Integer, Währung, Text, ext.)Länge : maximale Zahl der Zeichen pro WertNULL : leeres Datenfeld zulässig? [Ja / Nein] Entität : Welcher Entitätsmenge wird das Datenfeld zugeordnetAlias : Alternative Bezeichnungen für das DatenfeldBeschreib. : Weiter Angaben zum Datenfeld (z.B. zulässige Werte etc.)

19

Udo Matthias Munz

Vorgehensweise

2. Schritt : Datenobjekte (Entitäten) finden 2. Schritt : Datenobjekte (Entitäten) finden

Datenliste

PS Datenfeld Typ Länge NULL Entität Alias Beschreibung

AUFNR INT 10 NJ 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 KundennameAuftragAuftrag

ArtikelArtikel

KundeKunde

20

Udo Matthias Munz

Vorgehensweise

Kunde

3. Schritt : Beziehungen zwischen den Entitätsmengen beschreiben und n:m-Beziehungen auflösen (->Verbindungsentität)3. Schritt : Beziehungen zwischen den Entitätsmengen beschreiben und n:m-Beziehungen auflösen (->Verbindungsentität)

erteilt

(1,1)

(1,n)(1,n)(0,n)

• 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

• 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

Auftrag enthält Artikel

Auftrags-position

enthält enthält

(1,1)(1,1)(1,n) (1,n)

21

Udo Matthias Munz

Vorgehensweise

4. Schritt : Schlüssel- und Datenfelder den Entitätsmengen zuordnen und die Entitätsmengen in die Datenliste eintragen4. Schritt : Schlüssel- und Datenfelder den Entitätsmengen zuordnen und die Entitätsmengen in die Datenliste eintragen

• Ü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.

• Ü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.

Kunde

ert

(1,1)

Auftrag Artikel

Auftrags-position

enthält enthält

(1,1)(1,1)(1,n) (1,n)

KNRKNAMEKADRESSE

AUFNRDATUMKNR

ARTNRARTIKELPREIS

AUFNRARTNRMENGE

22

Udo Matthias Munz

Vorgehensweise5. Schritt : Konsistenz- und Integritätsbedingungen beschreiben5. Schritt : Konsistenz- und Integritätsbedingungen beschreiben

KonsistenzDie gespeicherten Daten müssen in sich und zur Realität widerspruchsfrei sein. IntegritätDie gespeicherten Daten müssen vollständig und korrekt sein.

Datentypkonsistenz z.B.: Ein Schlüsselfeld muß stets vom gleichen Datentypusein.

Schlüsselkonsistenz [eindeutiger Wert, nicht NULL]

Wertebereichkonsistenz 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.

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.

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.

23

Udo Matthias Munz

Das relationale Datenmodell

Student

Mtnr

Name

SemGr.

Adresse

Student ( Mtnr, Name, SemGr, Adresse)Student ( Mtnr, Name, SemGr, Adresse)

Entität Schlüsselattribut

Relation mit einemidentifizierenden SchlüsselattributRelation mit einemidentifizierenden Schlüsselattribut

Entity-Relationship-ModellEntity-Relationship-Modell relationales Datenmodellrelationales Datenmodell

Entität = DatenobjektRelationship = Beziehung

Relation = zweidimensionale Tabelle

24

Udo Matthias Munz

ERM - RDM

Student Vorlesung

MtnrNameSemGrAdresse

VorlnrFachSemesterRaumZeit

belegt

Student ( Mtnr, Name, SemGr, Adresse)Student ( Mtnr, Name, SemGr, Adresse)

Fach ( Vorlnr, Fach, Semester, Raum, Zeit)Fach ( Vorlnr, Fach, Semester, Raum, Zeit)

(0,m) (0,n)

Belegung (Mtnr, Vorlnr)Belegung (Mtnr, Vorlnr)

25

Udo Matthias Munz

Daten normalisieren1. Normalform1. 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)

Zusammengesetzter SchlüsselZusammengesetzter Schlüssel

Auftrag (Aufnr, Kundennr, Kundenname, Adresse, Datum)Bestellartikel (Aufnr, Artikelnr, Artikelbezeichnung, Menge, Preis)

PrimärschlüsselPrimärschlüssel

26

Udo Matthias Munz

Daten normalisieren1. Normalform1. 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 (TourendatenNr, Bezeichnung, Länge, Termine)

Zusammengesetzter SchlüsselZusammengesetzter Schlüssel

TourenZiele (TourenNummer, Tour, Kurzbeschreibung,Länge, Grad, Start, Ziel)

TourenTermine (TourTerminNr, TourenNummer, Beginn, Ende , MaxTeilnehmer)

PrimärschlüsselPrimärschlüssel

27

Udo Matthias Munz

Daten normalisieren

Auftrag (Aufnr, Kundennr, Kundenname, Adresse, Datum)Bestellartikel (Aufnr, Artikelnr, Artikelbezeichnung, Menge, Preis)

Auftrag (Aufnr, Kundennr, Kundenname, Adresse, Datum)Bestellartikel (Aufnr, Artikelnr, Menge)Artikel (Artikelnr, Artikelbezeichnung)Preis (Artikelnr, Preis)

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.

2. Normalform2. Normalform Gilt nur bei zusammengesetztem Primärschlüssel!

28

Udo Matthias Munz

Daten normalisieren3. Normalform3. 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 (Aufnr, Kundennr, Kundenname, Adresse, Datum)Bestellartikel (Aufnr, Artikelnr, Menge)Artikel (Artikelnr, Artikelbezeichnung)Preis (Artikelnr, Preis)

Auftrag (Aufnr, Kundennr, Datum)Kunde (Kundennr, Kundenname)Anschrift (Kundennr, Adresse)Bestellartikel (Aufnr, Artikelnr, Menge)Artikel (Artikelnr, Artikelbezeichnung)Preis (Artikelnr, Preis)

29

Udo Matthias Munz

Daten integrieren

Auftrag (Aufnr, Kundennr, Datum)Kunde (Kundennr, Kundenname)Anschrift (Kundennr, Adresse)Bestellartikel (Aufnr, Artikelnr, Menge)Artikel (Artikelnr, Artikelbezeichnung)Preis (Artikelnr, Preis)

Auftrag Kunde

AnschriftBestellartikel

Artikel Preis

1 : n

1 : 11 : n

n : 11 : 1

Auftrag (Aufnr, Kundennr, Datum)Kunde (Kundennr, Kundenname, Adresse)Bestellartikel (Aufnr, Artikelnr, Menge)Artikel (Artikelnr, Artikelbezeichnung, Preis)

30

Udo Matthias Munz

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 Entity-Relationship-Diagramm.

Bestimmen Sie die Relationen (Tabellen) und die dazugehörigen Attribute. Kennzeichnen Sie die Primär-

und Fremdschlüssel

31

Udo Matthias Munz

Anwendungsentwicklung und Datenbankadministration

32

Udo Matthias Munz

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.

33

Udo Matthias Munz

Structured Query Language (SQL)

Funktionen

Datendefinition [DDL]• CREATE• ALTER• DROP

Datenmanipulation [DML]• SELECT• UPDATE• INSERT• DELETE

Kontrolle und Steuerung• GRANT• LOCK• COMMITT• ROLLBACK

CREATE TABLE Student Mtknr, char(8), Name, char(30), SemGr, char(6);

CREATE TABLE Student Mtknr, char(8), Name, char(30), SemGr, char(6);

SELECT Name FROM Student WHERE SemGr= "10BW4A";

SELECT Name FROM Student WHERE SemGr= "10BW4A";

GRANT USER Regier IDENTIFIED BY "Dozent" ;

GRANT USER Regier IDENTIFIED BY "Dozent" ;

34

Udo Matthias Munz

JOIN

Bulk-Join => ohne WHERE-Klausel (kartesisches Produkt)

Teta-Join => mit WHERE-Klausel

Natural-Join => identische Spalten werden nur einmal angezeigt. Semi-Join => Nur Spalten einer Tabelle (senkt das Kommunikationsvolumen

bei verteilter Datenhaltung

Multiple-Join => Join mit mehr als zwei Tabellen

Outer-Join => Datensätze der ersten Tabelle, denen keine Datensätze der zweiten Tabelle zugeordnet werden können, werden mit einem leeren Datensatz verknüpft.

Restricted-Join => Durch AND werden weitere Bedingungen eingefügt

Equi-Join => Bedingung enthält nur Gleichheitszeichen

Auto-Join => Join einer Tabelle auf sich selbst

SELECT <merkmal> [,<merkmal>, ...] FROM tabelle [, tabelle, ...] WHERE <selektionsprädikat>

[AND <selektionsprädikat>] ;

Vergleichende BewertungInner schneller als OuterEqui schneller als Non-EquiRestricted schneller als NO-Restricted

35

Udo Matthias Munz

Ad Hoc Abfrage Anwendungsprogramm

Übersetzung und Optimierung

Transaktionen- und Cursorverwaltung

Speicher- und Zugriffsverwaltung

Systemtabellen Benutzertabellen

Systemprozeß

Systemnutzung (Job-Design)• Optimierung durch eine zweckmäßige Reihenfolge der Operationen (algebraische Optimierung)• Optimierung der JOIN-Operationen

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

Systemadministration• Datensicherheit• Vermeidung von Dateninkonsistenz• Vermeidung von Blockaden bei der Nutzung verteilter Systeme

Systemkonfiguration• Optimierung der Zugriffszeiten durch Systemauswahl und Konfiguration

Systemkonfiguration• Optimierung der Zugriffszeiten durch Systemauswahl und Konfiguration

Datenbankdesign• Optimierung der Datenbankstruktur (ERM / RDM)• optimale Größe der Tablespaces und Extens

Datenbankdesign• Optimierung der Datenbankstruktur (ERM / RDM)• optimale Größe der Tablespaces und Extens

36

Udo Matthias Munz

Parsing

SQL-Befehle werden als Text an das Datenbankmanagementsystem übermittelt. Dieses muß den Text lesen und interpretieren (Parsing).

lexikalische Analyselexikalische Analyse

TabellensucheTabellensuche

SpaltenattributsucheSpaltenattributsuche

Typ- und Constraint-Ver-gleich für die Spaltenattribute

Typ- und Constraint-Ver-gleich für die Spaltenattribute

Sperrung (Parse Lock)Sperrung (Parse Lock)

BerechtigungsprüfungBerechtigungsprüfung

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.

AusführungsplanungAusführungsplanung

37

Udo Matthias Munz

Optimierung

Für die Ausführung der übersetzten SQL-Befehle erstellt das DBMS einen Ausführungsplan, wobei vom DBMS Optimierungsregeln eingesetzt werden.

Regelbasierte OptimierungDie SQL-Befehle werden nach festen Regeln analysiert und die Reihen-folge der Operationen danach fest-gelegt.(siehe folgende Seite)

Aufwandbezogene OptimierungHierbei werden internen Statistiken und Strukturmerkmale der Daten-bank (z.B. Indizes) analysiert um den schnellsten Weg zu den gesuchten Daten zu finden.

Oracle ab Version 7

Manuelle OptimierungDurch 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.

38

Udo Matthias Munz

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ätEin 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.

Index-SelektivitätEin 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.

39

Udo Matthias Munz

Cache

Library Cache

least recently used (LRU)

SQL-Statements

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!

40

Udo Matthias Munz

Join-Design

SELECT Datum FROM Kunde, Auftrag WHERE = Kunde.KNR = Auftrag.KNR

AND Name = "Müller"

SELECT Datum FROM Kunde, Auftrag WHERE = Kunde.KNR = Auftrag.KNR

AND Name = "Müller"

Tabelle := Datum

(Name = "Müller" (Kunde |X| Kunde.KNR = Auftrag.KNR , Auftrag ))

Tabelle := Datum

(Name = "Müller" (Kunde |X| Kunde.KNR = Auftrag.KNR , Auftrag ))

Kundentabelle Auftragstabelle

|X| Kunde.KNR = Auftrag.KNR

Name = "Müller"

Datum

Tabelle := Datum

(Auftrag.KNR, Datum (Auftrag) (|X| Kunde.KNR = Auftrag.KNR )

Kunde.KNR

(Name = "Müller"(Kunde)))))

Tabelle := Datum

(Auftrag.KNR, Datum (Auftrag) (|X| Kunde.KNR = Auftrag.KNR )

Kunde.KNR

(Name = "Müller"(Kunde)))))

SELECT Datum FROM (SELECT KNR, Datum FROM Auftrag WHERE AUFTRAG.KNR =

(SELECT KNR FROM Kunde WHERE Name = "Müller")))

SELECT Datum FROM (SELECT KNR, Datum FROM Auftrag WHERE AUFTRAG.KNR =

(SELECT KNR FROM Kunde WHERE Name = "Müller")))

41

Udo Matthias Munz

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.

Joinist 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

42

Udo Matthias Munz

Paarweise Join versus Star-Abfrage

FaktentabelleAuftrag

DimensionstabelleKunde

Zwischenergebnis 1Auftrag & Kunde

JOIN

DimensionstabelleArtikel

Zwischenergebnis 2Auftrag & Kunde & Artikel

JOIN

DimensionstabelleLieferant

AbfrageergebnisAuftrag & Kunde & Artikel & Lieferant

JOIN

FaktentabelleAuftrag

Dimensions-tabelleKunde

Kartesisches ProduktKunde & Artikel & Lieferant

Dimensions-tabelleArtikel

Dimensions-tabelleLieferant

AbfrageergebnisAuftrag & Kunde & Artikel & Lieferant

JOIN

Eigenschaften:- viele Operationen- kleine Zwischentabellen

Eigenschaften:- wenige Operationen- große Zwischentabelle

Effizient bei großen Datenmengen Effizient bei kleinen Datenmengen

43

Udo Matthias Munz

Sort-Merge-Join

KNR Name Adresse4711 Müller 81243 München5612 Maier 71324 Stuttgart3254´ Huber 12312 Berlin

KNR Name Adresse4711 Müller 81243 München5612 Maier 71324 Stuttgart3254´ Huber 12312 Berlin

AUFNR Datum KNR2137 12.10.97 47112231 13.10.97 32542178 15.10.97 47112337 12.10.97 5511

AUFNR Datum KNR2137 12.10.97 47112231 13.10.97 32542178 15.10.97 47112337 12.10.97 5511

KNR AUFNR4711 2137

223121782337

5612 21372231

..........

KNR AUFNR4711 2137

223121782337

5612 21372231

..........

n x m Operationenn x m Operationen

KNR Name Adresse3254 Huber 12312 Berlin4711 Müller 81243 München5612 Maier 71324 Stuttgart

KNR Name Adresse3254 Huber 12312 Berlin4711 Müller 81243 München5612 Maier 71324 Stuttgart

AUFNR Datum KNR2231 13.10.97 32542137 12.10.97 47112178 15.10.97 47112337 12.10.97 5511

AUFNR Datum KNR2231 13.10.97 32542137 12.10.97 47112178 15.10.97 47112337 12.10.97 5511

max: n + m Operationenmax: n + m Operationen

Vorsortierung beider Tabellen nach dem Vergleichsattribut

44

Udo Matthias Munz

Speicher- und Zugriffssteuerung (1)

Mehrwegbaum (B*-Tree)

05 10 15

05 10 15

01 02 03 04 01 02 03 04 06 07 08 0906 07 08 09 11 12 13 1411 12 13 14 16 17 18 1916 17 18 19

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.

RIDRID 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.

45

Udo Matthias Munz

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 1 0 0 0 1 0 0 0 1 1 1 1 1 0 1 0 1 ....Privatkunde 0 1 1 1 0 1 1 0 0 0 0 0 0 1 0 1 0 ....Sonstige 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 ....

Geschäftskunde 1 0 0 0 1 0 0 0 1 1 1 1 1 0 1 0 1 ....Privatkunde 0 1 1 1 0 1 1 0 0 0 0 0 0 1 0 1 0 ....Sonstige 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 ....

Merkmalsausprägung 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

46

Udo Matthias Munz

Komprimierung von Bitmaps (1)

0100

1011

0101 1000 0110

1110 1100 1011 1010 0110

111000001100000000000000000010110000000000000000000010100110Rekonstruierte Bitliste

Darstellung erfordert 10 4-Bit-Blöcke für insgesamt 256 4-Bit-Blöcke

47

Udo Matthias Munz

Komprimierung von Bitmaps (2)

1 k

Kenn-Bit

.........

Bitmuster oder Nullfolge

Ist das Kenn-Bit gleich 1, werden die folgenden k-1 Bits als unkompri-mierte Bitfolge interpretiert.Ist das Kenn-Bit gleich 0, so wird der Inhalt der folgenden k-1 als binäreGanzzahl interpretiert, die angibt, wieviele aufeinander folgende 0-enauf 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.

48

Udo Matthias Munz

Auswertung komplexer Verbundoperationen

Bestell-Nr. Kunden-Nr. Artikel-Nr. Filial-Nr. Menge Datum

Kunde Produkt Filiale

Branche Produkt-Gruppe

Region

Detail - Relation

Dimensions-Relationen

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’;

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’;

DSS-Anfrage

49

Udo Matthias Munz

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

50

Udo Matthias Munz

Lösungen für die Speicherung großer Tabellen Array-Speicher

Speichermedien sind miteinander verkettet; Fassungsvermögen mehrere Terabytes; Problem: Datensicherung

Raid-ArrayPlattenkombinationen 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-TechnologieMit 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 DatenbankenVom Denkansatz versprechen sie eine zweckmäßige Lösung über die Instanzenbildung. Aktuelle OODBMS leisten dies jedoch noch nicht.

51

Udo Matthias Munz

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.

TechnikDie 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.

52

Udo Matthias Munz

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

53

Udo Matthias Munz

Kostenfaktoren bei Datenbanksystemen

Lizenz

2%Beratung

8%

Entwicklung

20%

Administration

60%

Wartung

8%Schulung

2%

Computerzeitung 7/98

Im Durchschnitt entfallen 60 % der Kosten eines Datenbanksystems auf die Systemadministration.

Im Durchschnitt entfallen 60 % der Kosten eines Datenbanksystems auf die Systemadministration.

54

Udo Matthias Munz

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

55

Udo Matthias Munz

Entity Relationship Modell