Datenbanken und Datenbankmanagementsystemehochschule-bochum.de/fileadmin/media/fb_e/labore/aidlab/brabender/... · SS 2007 Datenbanken Seite 3 Einige Literatur aus dem Bereich der

SS 2007 Datenbanken Seite 1

Datenbanken und Datenbankmanagementsysteme

Labor für Angewandte Informatik und Datenbanken

Prof. Dr. Katrin Brabender Version: 12.03.2007


Inhalte der Vorlesung

• Einführung in die Theorie der Datenbanken

• Relationale Datenbanken

• Phasen des Datenbankentwurfs

• Das ER-Modell

• Normalisierung

• Die Datenbanksprache SQL

• Datenbank-Techniken

• Arbeitsweise eines DBMS und Optimierung

• Die Datenbank im Netz

• Einige Datenbanken im Vergleich

• Datawarehouse und Mulitidimensionale Datenbanken


Einige Literatur aus dem Bereich der Datenbanken

• Ramez Elmasir, Shamkant B. Navathe: Grundlagen von Datenbanksystemen, Addison-Wesley 3. Auflage 2002

• Andreas Heuer, Gunter Saake, Kai-Uwe Sattler: Datenbanken kompakt, mitp 2001

• Rene Steiner: Theorie und Praxis relationaler Datenbanken, vieweg 2000

Zum Thema Data-Warehouse

• Bauer, A.; Günzel, H.: Data-Warehouse-Systeme. Dpunkt.verlag Heidelberg 2001

• Inmon, W.H.: Building the Data Warehouse. Second Edition, John Wiley & Sons, New York, 1996.


Einführung in die Theorie der Datenbanken

Datenbanken bzw. Datenbanksysteme sind Systeme zur Beschreibung,

Speicherung und Wiedergewinnung von umfangreichen Datenmengen,

die von mehreren Anwendungsprogrammen benutzt werden.

Ein Datenbanksystem besteht aus der Datenbank (Abkürzung DB), d.h.

der Datenbasis, in der die Daten abgelegt werden, und dem

Datenbankmanagementsystem (Abkürzung DBMS), d.h. den

Verwaltungsprogrammen, die die Daten entsprechend den

vorgegebenen Beschreibungen abspeichern, auffinden, verändern etc.


Ein Datenbanksystem hat folgende Eigenschaften

• Der Nutzer soll Zugriff auf die gespeicherten Daten haben, ohne

dass dieser wissen muss, wie die Daten im System organisiert sind.

• Daten müssen vor ungewollter Manipulation geschützt werden, d.h.

ein Benutzer darf auf Daten nur lesend oder schreibend zugreifen,

wenn er hierfür eine Zugriffsberechtigung hat. Es darf nicht

passieren, dass wegen Fehlmanipulationen des Benutzers Daten

zerstört werden können (bis hin zum gesamten Datenbestand).

• Datenbanken sollten gewährleisten, dass eine Änderung der

internen Datenorganisation nicht zu einer Anpassung der

Anwendersoftware führen muss.


• Die Daten sollen in strukturierter Form zur Verwendung durch mehr

als ein Software-System gespeichert werden.

• Ein Ziel von Datenbanksystemen ist die Beseitigung von

Datenredundanzen.

• Sie können große Datenmengen effizient verwalten.

Dabei bieten sie benutzergerechte Anfragesprachen an, die es dem

Anwender ermöglichen auf die Daten zuzugreifen ohne Rücksicht

auf die interne Realisierung der Datenspeicherung. Interne

Optimierungen ermöglichen einen effizienten Zugriff auf die Daten.

• Multiuser-Fähigkeit, d.h. viele Nutzer können gleichzeitig auf die

Datenbank zugreifen. Ein Transaktionskonzept verhindert

unerwünschte Nebeneffekte beim Zugriff auf gemeinsam genutzte

Daten.


Das Problem der Datenredundanz

• Ohne den Einsatz von Datenbanksystemen tritt das Problem der

Datenredundanz (Mehrfachspeicherung) auf.

Das Speichern von Daten in Dateien führt zum mehrfachen Speichern

der selben Informationen, d.h. Informationen werden mehrfach

abgelegt. Man bezeichnet dies als redundante Speicherung.

• Die redundante Speicherung führt zu einer Verschwendung von

Speicherplatz und zur Dateninkonsistenz.

• Zugriffskontrollen und Datensicherheit sind nicht gewährleistet.

• Die Datenunabhängigkeit ist nicht gegeben, d.h. die interne Darstellung

der Daten ist nicht einheitlich und erschwert so dem

Anwendungsprogrammierer das Zugreifen auf diese Daten.


• Sowohl das Problem der fehlenden Datenunabhängigkeit als auch

der fehlenden Zugriffskontrolle und Datensicherheit kann mit Hilfe

des Einsatzes von Datenbanksystemen gelöst werden.

• Im Gegensatz zur Datenredundanz spricht man dann von

Datenintegration.

Das Prinzip der Datenintegration basiert auf folgenden Überlegungen:

• Die gesamte Basis- und Anwendungssoftware arbeitet auf

denselben Daten, die in einer zentralen Datenhaltungskomponente

verwaltet werden.


Die Datenunabhängigkeit

Das Konzept der Datenunabhängigkeit hat das Ziel, eine Datenbank

von notwendigen Änderungen der Anwendung abzukoppeln.

Sie kann in zwei Aspekte aufgeteilt werden:

• Die Implementierungsunabhängigkeit oder physische

Datenunabhängigkeit bedeutet, dass die konzeptionelle Sicht auf

einen Datenbestand unabhängig von der für die Speicherung der

Daten gewählten Datenstruktur besteht.

• Die Anwendungsunabhängigkeit oder logische

Datenunabhängigkeit koppelt die Datenbank von Änderungen und

Erweiterungen der Anwendungsschnittstelle ab.


Transaktionen

• Transaktionen sind eine Folge von Datenbankoperationen, die

einen konsistenten Datenbestand in einen neuen konsistenten

Datenbestand überführen.

• Die Folge von Datenbankoperationen wird dabei entweder

vollständig oder gar nicht ausgeführt.

• Gerade im Mehrbenutzerbetrieb ist die Unterstützung des

Transaktionskonzeptes ein wichtiges Merkmal von

Datenbanksystemen.


Bemerkung

• Die Datenunabhängigkeit wird durch die sog. Drei-Ebenen-

Architektur (s. später) gewährleistet.

• Zugriffskontrolle, d.h. kein unbefugter Zugriff und Datensicherheit,

d.h. kein ungewollter Datenverlust werden vom System

gewährleistet.


Anforderungen an ein Datenbank-Management-System

Der Mathematiker Dr. Edgar F. Codd hat die theoretischen Grundlagen

für Datenbanken gelegt. Anfang der 70er Jahre hat Codd die

Anforderung an ein Datenbank-Management-System in 9 Regeln

aufgestellt, die noch heute ihre Gültigkeit haben.


Die Codd‘schen Regeln

• Integration

Einheitliche Verwaltung aller von Anwendungen benötigten Daten,

d.h. nicht-redundante Datenhaltung.

• Operationen

Auf der Datenbank müssen Operationen möglich sein, die

Datenspeicherung, Suchen, Verändern des Datenbestandes

ermöglichen.

• Katalog

Der Katalog oder Data dictionary ermöglicht Zugriffe auf die

Datenbeschreibungen der Datenbank.


• Benutzersichten

Für die unterschiedlichen Anwendungen sind unterschiedliche

Sichten auf die Daten notwendig.

• Konsistenzüberwachung

Überprüfung der Dateninhalte und der korrekten Ausführung von

Änderungen.

• Zugriffskontrolle

• Transaktionen

Zusammenfassung von Datenbank-Änderungen zu

Funktionseinheiten.


• Synchronisation

Konkurrierende Transaktionen mehrer Benutzer müssen koordiniert

werden.

• Datensicherung

Das Wiederherstellen von Daten z.B. nach Systemfehlern muss

gewährleistet werden.


Grundmerkmale von modernen Datenbanksystemen sind

(abgeleitet aus den Codd‘schen Regeln)

• Verwaltung von persistenten (langfristig zu haltende) Daten.

• Effiziente Verwaltung großer Datenmengen.

• Datenbank-Management-Systeme definieren ein Datenmodell, mit

dessen Konzepten alle Daten einheitlich beschrieben werden.

• Sie stellen Operationen und Sprachen zur Verfügung. (Bei

relationalen Datenbanken ist SQL der Standard).

• Sie unterstützen das Transaktionskonzept.

• Sie unterstützen die Einhaltung des Datenschutzes,

Datenkonsistenz und Datensicherheit.


Architektur in drei Ebenen

Die heute noch allgemein akzeptierte Methode zur Beschreibung der

Architektur eine Datenbank wurde in den 70er Jahren von der

ANSI/X3/SPARC Study Group on Database Management Systems

entworfen.

Es handelt sich um die Drei-Ebenen-Schema Architektur einer

Datenbank.

Ein Datenbankschema wird in drei aufeinander aufbauenden Ebenen

aufgeteilt:


Datenbankarchitektur

• Interne Ebene:

Die interne Ebene beschreibt die systemspezifische Realisierung der

Datenbank, d.h. die Art und Weise, wie die Daten physisch auf der

Hardware abgespeichert werden. Die Interne Ebene verwaltet das DBMS.

• Konzeptionelle Ebene:

Sie beinhaltet eine implementierungsunabhängige Modellierung der

Datenbank in einem systemunabhängigen Datenmodell. Die Struktur der

Datenbank wird vollständig beschrieben.

Zuständig für diese Ebene ist der Datenbank-Administrator.

• Externe Schicht:

Sicht der Endanwender auf die Daten. Es kann mehrere Sichten, d.h.

mehrere Externe Schemata geben.


Klassifizierung von Datenbankmanagementsystemen

DBMS werden anhand verschiedener Kriterien klassifiziert.

• Das dem DBMS zugrunde liegende Datenmodell .

Man unterscheidet zwischen einem

• Hierarchischem Modell

• Netzwerk Modell

• Relationalem Modell

• Objektdatenmodell

Das Hierarchische und Netzwerk Modell sind veraltete Modelle, bei denen die Datendateien hierarchisch angeordnet sind. Jeder Datensatz einer höheren Hierarchieebene enthält einen Verweis auf die ihm zugeordneten Datensätze der nächst niedrigeren Ebene.


Bei relationalen Datenbanken werden die Daten nicht hierarchisch in

einem File, sondern geordnet nach Themenkreisen (Entitäten) in Form

von Tabellen abgelegt. Relationale Datenbanken zeichnen sich durch

eine hohe Flexibilität aus.

Objektmodelle beinhalten Konzepte der Objektorientierung.

• Die vom System unterstützte Anzahl an Nutzern, die gl eichzeitig auf

die Datenbank zugreifen können.

Unterschieden wird hier zwischen Single- und Multi-User System.

• Anzahl der Rechner, auf die sich die Datenbank vertei lt.

Man spricht von einem zentralen DBMS, wenn die Daten auf einem

einzigen Rechner gespeichert werden und von einem dezentralen

DBMS, falls die Datenbank auf mehreren Rechnern verteilt ist.

• Kosten eines DBMS


Konkrete kommerzielle Datenbank Management Systeme

sind z.B. die relationalen Datenbanksysteme Oracle, IBM DB2,

Microsoft SQL-Server, Sybase.

Diese Systeme haben

• eine Drei-Ebenen-Architektur nach ANSI-SPARC

• eine einheitliche Datenbanksprache (SQL)

• eine Einbettung dieser Sprache in kommerzielle Programmiersprachen

• verschiedene Werkzeuge für die Definition, Anfrage und Darstellung von

Daten

• kontrollierter Mehrbenutzerbetrieb, Zugriffskontrolle und

Datensicherheitsmechanismen.


Relationale Datenbanken

Das relationale Datenmodell wurde von Codd 1970 eingeführt mit

seiner Arbeit

E. F. Codd: A Relational Model of Data for Large Shared Data Banks,

Communications of the ACM Vol 13, June 1970.

Die Firma Oracle war die erste Firma, die ein geeignetes DBMS auf

den Markt brachte.


Das Konzept einer relationalen Datenbank

Die Basis für das Speichern von Daten in einer relationalen Datenbank

sind Tabellen.

Beispiel: Die Kunden einer Firma sind in einer Tabelle abgelegt:

Name Vorname PLZ Ort Straße WertigkeitMeier Klaus 44799 Bochum Laerheidestr. 26 BBeier Andrea 60528 Frankfurt Zeil 5 AMeier Klaus 42111 Wuppertal Güntherstr. 11 CBecker Inga 88212 Ravensburg Lindenallee 2 AKohnen Silvia 60389 Frankfurt Im Prüfling 2 B

Kunde


Die Grundbegriffe des relationalen Datenmodells

Entität (Tabellenname): Eine Entität stellt einen Themenkreis dar, der

Elemente mit gleichen Merkmalen umfasst, Beispiel Kunde, Student etc.

Entitätsmenge (Datensätze): Die Entitätsmenge beinhaltet alle zu den

Merkmalen einer Entität gehörenden Werte. D.h. eine Entitätsmenge

entspricht allen gespeicherten Datensätzen einer Tabelle.

Tabelle: Entität mit zugehöriger Entitätsmenge

Tupel (Datensatz): Ein Tupel umfasst alle Merkmale eines Elementes als

Bestandteil einer Entitätsmenge. Entspricht also einem vollständigen

Datensatz.

Attribut (Spaltenname): Beschreibt spezifische Eigenschaft einer

Entitätsmenge, Bsp. Name


Attributwert: Datenwert, der das zugehörige Attribut eines Tupels beschreibt,

Beispiel Attribut = Name, Attributwert = Meier.

Jedes Tupel einer Entitätsmenge muss eindeutig identifizierbar sein. Dies

kann durch ein Attribut oder einer Kombination von Attributen gewährleistet

werden. Man bezeichnet dieses Attribut bzw. diese Kombination aus

Attributen als Identifikationsschlüssel (Id-Schlüssel).

Im Beispiel der Entität Kunde wäre der Identifikationsschlüssel

beispielsweise gegeben durch

Name, Vorname, PLZ

Die Kombination Name, Vorname würde nicht ausreichen.


Eigenschaften des Identifikationsschlüssels

• Er ist eindeutig.

• Jedem neuen Tupel muss sofort der entsprechende Attributwert des

Identifikationsschlüssels zugeteilt werden können.

• Der Identifikationsschlüssel eines Tupels darf sich während dessen

Existenz nicht ändern.

• Der Identifikationsschlüssel und auch kein Bestandteil darf ein NULL-

Wert sein.


Zur Wahrung der Übersichtlichkeit führt man meist künstliche Identifikations-

schlüssel ein, z.B. laufende Nummern.

Damit sieht die Kundentabelle wie folgt aus

KNr Name Vorname PLZ Ort Straße Wertigkeit100 Meier Klaus 44799 Bochum Laerheidestr. 26 B101 Beier Andrea 60528 Frankfurt Zeil 5 A102 Meier Klaus 42111 Wuppertal Güntherstr. 11 C103 Becker Inga 88212 Ravensburg Lindenallee 2 A104 Kohnen Silvia 60389 Frankfurt Im Prüfling 2 B

KundeEntität

Id-SchlüsselTupel

Attribut


Die Daten einer Datenbank werden unterteilt in Stammdaten und sog.

Bewegungsdaten.

Beispiel : Eine Firma verkauft und versendet Computerartikel. Die Kunden

und die angebotenen Artikel wären hier die Stammdaten, die Aufträge die

Bewegungsdaten.

Ein Auftrag stammt von einem Kunden, ein Auftrag besteht aus einem

oder mehreren Artikeln, die bestellt werden.

Damit besteht eine Beziehung zwischen den Tabellen Auftrag und Kunde

und eine weitere Beziehung zwischen den Tabellen Auftrag und Artikel.


Eine Beziehung wird durch einen Fremdschlüssel ausgedrückt.

Ein Fremdschlüssel in einer Tabelle T2 ist ein Attribut oder eine

Attributkombination, welche in einer Tabelle T1 den

Identifikationsschlüssel bildet.

Auf der folgenden Folie sind die Tabellen mit ihren Beziehungen

dargestellt.

Das Attribut KNr in der Tabelle Auftrag ist ein Fremdschlüssel.

Zwischen der Tabelle Kunde und Auftrag besteht eine 1:n Beziehung, d.h.

1 Kunde kann n Aufträge erteilen, 1 Auftrag stammt aber nur von 1

Kunden.


AufNr KNr AufDat LiefDat30 102 26.03.2004 02.04.200440 104 01.03.2004 15.03.200450 102 29.03.2004 02.04.200460 103 03.01.2004 06.01.2004

Auftrag

ArtNr ArtBez EkPreis VKPreis1001 CPU 180 2002036 Grafikkarte 130 1503000 Speicher 90 1003057 Monitor 220 3004000 Festplatte 70 80

Artikel

ArtNr AufNr Menge1001 40 32036 40 13000 30 13057 60 53057 50 104000 50 2

Position

KNr Name Vorname PLZ Ort Straße Wertigkeit100 Meier Klaus 44799 Bochum Laerheidestr. 26 B101 Beier Andrea 60528 Frankfurt Zeil 5 C102 Meier Klaus 42111 Wuppertal Güntherstr. 11 A103 Becker Inga 88212 Ravensburg Lindenallee 2 C104 Kohnen Silvia 60389 Frankfurt Im Prüfling 2 B

Kunde


Für eine Beziehung kann referentielle Integrität bestimmt werden.

Dann kann

• kein Tupel in der Tabelle Auftrag mit einem Attributwert eines Kunden

erzeugt werden, den es nicht in der Kundentabelle gibt

• kein Kunde aus der Kundentabelle gelöscht werden, der noch Aufträge

in der Tabelle Auftrag hat.


Die Datenbanksprache SQL

SQL (Structured Query Language) ist eine weitestgehend standardisierte

Sprache für relationale Datenbanken.

SQL ist eine deskriptive, d.h. nichtprozedurale Sprache. Es wird damit dem

Datenbankmanagementsystem nicht mitgeteilt, wie die Daten gesucht

werden sollen, sondern nur was erreicht werden soll.

SQL ist mengenorientiert, d.h. das Ergebnis einer Datenbankabfrage kann

aus einem oder mehreren Treffern bestehen.


SQL besteht aus den Bereichen

• DDL Data Definition Language mit den Befehlen

CREATE (Anlegen von Tabellen, Sichten,…)

ALTER (Ändern)

DROP (Löschen)

• DML Data Manipulation Language mit den Befehlen

INSERT (Einfügen von Zeilen)

UPDATE (Ändern)

DELETE (Löschen)

SELECT (Abfragen)


• DCL Data Control Language mit den Befehlen

GRANT (Vergabe von Zugriffsrechten)

REVOKE (Zurücknahme von Zugriffsrechten)

COMMIT (Abschluss von Transaktionen)

ROLLBACK (Abbruch von Tranksaktionen)


Der Datenbankentwurfsprozess

Dem Entwurf einer Datenbank kommt eine sehr große Bedeutung zu.

Der Datenbankentwurf kann in mehrere Phasen unterteilt werden:

• Anforderungsanalyse

Sammeln und Analysieren der Anforderungen an die zu realisierende

Datenbank

• Konzeptioneller Entwurf

Die Datenbank soll zusammen mit den Anwendungsfunktionen

unabhängig von dem später zur Implementierung verwendeten System

entworfen werden. Es soll ein Datenbankmodell benutzt werden, das an

konzeptionellen Informationsstrukturen und nicht an

Implementierungsmöglichkeiten angelehnt ist.

Gut geeignet ist das sog. ER-Modell.


• Verteilungsentwurf

Die Verteilung der Daten muss entworfen werden, wenn die

Datenbankanwendung verteilt realisiert werden soll.

• Logischer Entwurf

In dieser Phase erfolgt der Detail-Entwurf. Das ER-Modell wird z.B. auf

ein relationales Schema übertragen.

• Datendefinition

Hier werden die Datentypen, Wertebereiche etc. definiert.

• Physischer Entwurf

Anlegen von Datencontainern auf den Platten des Datenbankcomputers,

Wahl von spezifischen Speicherstrukturen und Zugriffspfaden für die

Datenbankdateien.


• Externer Datenbankentwurf

Definition von Benutzer-Sichten auf die Datenbank, Anlegen von

Benutzern und Gruppen, Vergabe von Zugriffsrechten.

• Realisierung des Entwurf in einem konkreten DBMS

Installation, Anlegen der Datenbank, Anlegen der Tabellen. Dies fällt im

Normalfall in den Aufgabenbereich des DBA und wird zusammen mit den

Datenbankdesignern durchgeführt.


Der Konzeptuelle Entwurf- Das Entity-Relationship-Mod ell (ERM)

• Das Entity-Relationship-Modell wird häufig für den konzeptuellen Entwurf

eingesetzt.

• Der Begriff des Entity-Relationship-Modells geht zurück auf den

grundlegenden Artikel von P.P.Chen im Jahre 1976:

The Entity-Relationship Model-Toward a Unified View of Data

in ACM Transcations on Database Systems, Band 1, Nr. 1

• Ein ER-Schema ist eine graphische Repräsentation der konzeptuellen

Modellierung der Daten.

• Das ERM basiert auf den drei Grundkonzepten Entity als zu modellierende

Informationseinheit, Relationship zur Modellierung von Beziehungen

zwischen den Enities und Attribut als Eigenschaft von einer Entity oder einer

Relationship.


• Entity bzw. Entität

Objekt der realen Welt, über das Informationen zu speichern sind, z.B.

Produkt, Kunde, Bestellungen, Artikel.

• Relationship

Beschreibt eine Beziehung zwischen Entities, z.B. ein Kunde bestellt n

Produkte

• Attribut

Repräsentiert eine Eigenschaft einer Entity, z.B. Kunde hat Namen


Verwendete Symbole im ER-Modell

Für die Modellierung gibt es keinen einheitlichen Standard. Es gibt mehrere

Darstellungsformen. Wir verwenden die Folgende:

• Entities bzw. Entitäten

werden durch Rechtecke repräsentiert:

• Attribute

werden durch Ellipsen repräsentiert:

Eindeutige Attribute werden unterstrichen.

Student

Name


• Relationship

werden durch Rauten repräsentiert:

Student Vorlesungbesucht


Beispiel für eine Entwicklung eines ER-Modells

Eine Hochschule möchte eine Struktur in ihre Daten bringen.

Studenten, Fachbereiche, Mitarbeiter, Studiengänge sollen sinnvoll mit ihren

Beziehungen zueinander abgelegt werden.

Vorgehensweise

1. Zunächst bildet man eine erste intuitive Entity-Struktur, Entities wären

Student, Fachbereich, Mitarbeiter, Studiengang.

2. Untersuchung der wichtigen Beziehungen zwischen diesen Entitäten.


Folgende Beziehungstypen (Kardinalitäten) sind mögli ch

Beziehungstyp 1:N

Dieser Typ liegt vor, wenn zu einem Wert eines Entities A mehrere Werte

eines anderen Entities B in Beziehung stehen, umgekehrt aber jeder Wert

von B genau zu einem Wert von A in Beziehung steht.

Bsp. Fachbereich Studienganghat1 N

Ein Fachbereich hat mehrere Studiengänge,

1 Studiengang gehört zu einem Fachbereich


Beziehungstyp N:M

Dieser Typ liegt vor, wenn zu einem Wert eines Entities ein oder beliebig

viele Werte eines anderen Entities in Beziehung stehen und umgekehrt

(many to many).

Beispiel

Student StudienganghatN M

Ein Student kann für mehrere Studiengänge (M) eingeschrieben sein,

ein Studiengang hat mehrere Studenten (N).


Beziehungstyp 1:1

Dieser Typ liegt vor, wenn jeder Wert eines Entities A genau zu einem Wert

eines anderen Entities B eine Beziehung hat und umgekehrt.

Beispiel

Mitarbeiter Fachbereichleitet1 1

Ein Mitarbeiter (Dekan) leitet einen Fachbereich,

ein Fachbereich wird von einem Mitarbeiter geleitet.


Kann- oder Muss-Beziehung

Es ist außerdem wichtig zu überprüfen, ob eine Beziehung optional (kann-

Beziehung) oder obligatorisch (muss-Beziehung) ist.

Eine kann-Beziehung wird symbolisch durch ein ausgedrückt, eine muss-

Beziehung durch ein

Beispiel

Mitarbeiter Fachbereichleitet1 1

Ein Mitarbeiter kann einen Fachbereich leiten (dies ist der Mitarbeiter Dekan),

ein Fachbereich muss von einem Mitarbeiter geleitet werden.


Fachbereich Mitarbeiterhat1 N

Ein Fachbereich muss N (d.h. mindestens 1) Mitarbeiter haben,

ein Mitarbeiter gehört zu genau einem Fachbereich.


Grad der Beziehung

An einer Beziehung können mehrer Entities beteiligt sein.

Von einer Binären Beziehung spricht man, wenn genau zwei Entities

beteiligt sind.

Fachbereich Mitarbeiterhat1 N

Sind drei oder mehr Entities beteiligt, so spricht man von einer Tenären

bzw. n-ären Beziehung.


Beispiel für eine Tenäre Beziehung

Projekt MitarbeiterhatN M

Qualifikation

P


Ist nur eine einzige Entity an einer Beziehung beteiligt, so spricht man

von einer rekursiv binären Beziehung.

Ein Mitarbeiter kann mit einem Mitarbeiter verheiratet sein.

Eine andere Darstellungsform für eine rekursiv binäre Beziehung ist

Mitarbeiter1

Mitarbeiter1

verheiratet

Mitarbeiter

1

verheiratet

1


Spezialisierung und Aggregation

Unter einer Spezialisierung versteht man, wenn eine Teilmenge (Subtyp)

weitere Attribute gegenüber der Grundmenge (Supertyp) hat. Die Entity

Supertyp ist dann die Generalisierung, die Subtypen- Entities sind die

Spezialisierung.

SubtypSupertyp IS-A


Beispiel für eine Spezialisierung

Sekretär

Mitarbeiter IS-Ad

Techniker

Professor

Laborassistent

Das d in der Beziehung gibt an, dass die Mengen disjunkt sind.


Aggregation

GUI

Datenbankanwendung PART-OF

Dokumentation

DBS

Von Aggregation spricht man, wenn ein Entity aus mehreren

eigenständigen Entities zusammengesetzt ist.


Redundante Beziehungen

Bei der ER-Modellierung muss darauf geachtet werden, dass keine

Redundanten Beziehung in dem Modell existieren.

Hat man alle Einzelbeziehungen zwischen den Entities untersucht, dann

setzt man die Beziehungen zu einem gesamten ER-Modell zusammen.

Dort muss jeder geschlossene Kreis auf Redundanzen überprüft werden.

Sind Redundanzen vorhanden, müssen diese im Modell bereinigt

werden.


Diese und die nächste Folie zeigen geschlossene Kreis-Beziehungen im

Modell. Beim ersten geschlossenen Kreis handelt es sich um eine

redundante Beziehung, der zweite Kreis stellt nicht-redundante

Beziehungen dar.

AuftragKunde erteilt

enthält

Artikel

bestelltredundante Beziehung

N1

M

NN

M


AuftragKunde erteilt

enthält

Artikel

bevorzugtnicht-redundante Beziehung

N1

M

NN

M

Die Beziehung „bevorzugt“ drückt nun etwas anderes aus. Diese Beziehung wäre beispielsweise wichtig um Kundenverhalten zu analysieren.


Ein ER-Modell (hier sind nicht die Attribute eingezeichnet) für unser Hochschulbeispiel könnte dann wie folgt aussehen:

FachbereichStudiengang hat

hat

Mitarbeiter

hat

1N

1M

NN

Student ist 11

leitet

1

11

verheiratet

1

Laborassistent Sekretär Professor Techniker

IS-A


Die Beziehung Student- Studiengang sieht dann mit den Attributen wie folgt aus

Studiengang

hat

M

N

Student

MatrNr

Name

Geburtstag

StudGangNr

StudGangBez


Das Logische Modell - Die Übertragung der Beziehungen in Tabellen

Nachdem das Konzeptuelle Modell erstellt ist, folgt die Übertragung auf ein

logisches Modell. Wir verwenden ein Relationales DBMS, d.h. die

Beziehungen im ER-Modell werden in Tabellen überführt.

Für den Aufbau einer Tabelle kann man folgende Kurzschreibweise wählen:

Entitätsname (Id-Schlüssel,Attribut 1, Attribut 2,…, Attribut n)

Der Tabellenname wird fett gedruckt, der Id-Schlüssel wird unterstrichen.

Falls der Id-Schlüssel aus zusammengesetzten Attributen besteht, werden

alle zur Bildung des Id-Schlüssels erforderlichen Attribute unterstrichen.


Ein Attribut ohne Attributwert besitzt einen sog. Nullwert.

Nullwerte dürfen in Fremdschlüsseln zunächst nicht vorhanden sein, da

ein Fremdschlüsselattributwert immer im Wertebereich des

entsprechenden Id-Schlüssels liegen muss.

(Wir werden später Fälle betrachten, bei denen Nullwerte dennoch

sinnvoll sind.)


Alle Beziehungstypen werden wir anhand eines fiktiven Beispiels mit den

Entities Person und Haustier durchführen.

Die beiden Tabellen haben den folgenden Aufbau

Person (PNr, Name, Vorname)

Haustier (TNr, Art, Rasse, Alter)


Die 1:1- Beziehung

Eine Person hat also 1 Haustier, ein Haustier gehört einer Person.

a) Beziehungstyp

Person Haustierhat1 1

Eine Person hat genau 1 Haustier,

ein Haustier gehört zu genau einer Person.


Übertragen auf Tabellen:

1. Möglichkeit

• Es entstehen 2 Tabellen (Person und Haustier).

• Der Id-Schlüssel der Tabelle Haustier wird zum Fremdschlüssel der

Tabelle Person (umgekehrt geht natürlich auch).

Kurzschreibweise: Person (PNr, Name, Vorname, TNr)



TNr Art Rasse Alter1 Vogel Papagei 202 Hund Boxer 13 Hund Dackel 104 Fisch Goldfisch 0,55 Katze Siam 7

PNr Name Vorname TNr

1 Meier Kai 2

2 Müller Ute 5

3 Becker Inga 4

4 Kohnen Bernd 1

5 Laufer Thomas 3

Person

Haustier


zu jedem Tupel in Person gibt es genau ein Tupel in Haustier


2. Möglichkeit

• Man fasst beide Entities zu einer Tabelle Haustierbesitzer zusammen

Kurzschreibweise:

Haustierbesitzer (PNr, Name, Vorname, Art, Rasse, Alter)

Dies ist nur erlaubt, wenn die Tabelle Haustiere nicht noch mit anderen

Tabellen in Beziehung steht, da es nun keinen Id-Schlüssel TNr mehr gibt.


PNr Name Vorname Art Rasse Alter

1 Meier Kai Hund Boxer 1

2 Müller Ute Katze Siam 7

3 Becker Inga Fisch Goldfisch 0,5

4 Kohnen Bernd Vogel Papagei 20

5 Laufer Thomas Hund Dackel 10

Haustierbesitzer


b) Beziehungstyp


Eine Person kann höchstens 1 (kein oder genau ein) Haustier haben,





• 1. Möglichkeit

In der Tabelle Haustier wird der Fremdschlüssel PNr eingefügt.

Kurzschreibweise: Person (PNr, Name, Vorname)

Haustier (TNr, Art, Rasse, Alter, PNr)


TNr Art Rasse Alter PNr1 Vogel Papagei 20 32 Hund Boxer 1 13 Hund Dackel 10 4

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

5 Laufer Thomas

Person

Haustier


jedes Tupel in Haustier hat genau ein zugehöriges Tupel in Person

Es gibt Tupel in Person, die keinen Bezug zu einem Tupel in Haustier haben


• 2. Möglichkeit

Da der Fremdschlüssel PNr in Haustier nur eindeutige Attributwerte

annehmen kann, wird er gleichzeitig Id-Schlüssel für die Tabelle

Haustier.


Haustier (PNr, Art, Rasse, Alter)


Art Rasse Alter PNrVogel Papagei 20 3Hund Boxer 1 1Hund Dackel 10 4

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

5 Laufer Thomas

Person

Haustier


jedes Tupel in Haustier hat genau ein zugehöriges Tupel in Person

Es gibt Tupel in Person, die keinen Bezug zu einem Tupel in Haustier haben


c) Beziehungstyp


Eine Person kann höchstens 1 (kein oder genau ein) Haustier haben,

ein Haustier gehört zu höchstens einer Person.


Übertragen auf zwei Tabellen:

In der Tabelle Person wird der Fremdschlüssel TNr, in der Tabelle

Haustiere der Fremdschlüssel PNr verwendet.

PNr Name Vorname TNr

1 Meier Kai 2

2 Müller Ute

3 Becker Inga 4

4 Kohnen Bernd 1

5 Laufer Thomas

TNr Art Rasse Alter PNr1 Vogel Papagei 20 42 Hund Boxer 1 13 Hund Dackel 104 Fisch Goldfisch 0,5 35 Katze Siam 7

Hier sind Nullwerte in den Fremdschlüsseln, daher Transformation erforderlich.


Übertragen der Beziehung auf Tabellen

( ohne Nullwerte im Fremdschlüssel):

• Es entstehen 3 Tabellen (Person, Haustier, Tierhalter ).

• In der Tabelle Tierhalter existieren nur diejenigen Tupel, die eine 1:1

(muss) Beziehung zwischen den Tabellen Person und Haustier herstellen.

• Der Id-Schlüssel der Tabelle Tierhalter wird aus den Fremdschlüsseln

PNr und TNr gebildet.

• Jeder Attributwert der Attribute TNr und PNr darf in Tierhalter nur einmal

vorkommen, daher reicht auch einer dieser Attribute als Id-Schlüssel aus.





Tierhalter (TNr,PNr)


Person

Haustier

PNr TNr

1 2

3 4

4 1

TierhalterPNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

5 Laufer Thomas

TNr Art Rasse Alter1 Vogel Papagei 202 Hund Boxer 13 Hund Dackel 104 Fisch Goldfisch 0,55 Katze Siam 7


Tierhalter

1

1 1

1


Die 1:N- Beziehung

Eine Person hat also N Haustiere, ein Haustier gehört einer Person.

a) Beziehungstyp

Person Haustierhat1 N

Eine Person muss N (d.h. mindestens 1) Haustier haben,





• Der Id-Schlüssel der Tabelle Person wird zum Fremdschlüssel der

Tabelle Haustier.




Die Tabellen haben folgende Eigenschaften:

• Ein Tupel der Tabelle Person hat eine Beziehung mit mehreren

Tupeln aus der Tabelle Haustier.

• Die Tabelle Haustier besitzt mindestens gleich viele Tupel wie die

Tabelle Person.

• Der Fremdschlüssel PNr in der Tabelle Haustier kann den selben

Attributwert mehrmals annehmen.

• Jeder Attributwert des Attributs PNr aus der Tabelle Person muss

mindestens einmal als Fremdschlüssel in Haustier vertreten sein.


TNr Art Rasse Alter PNr1 Vogel Papagei 20 32 Hund Boxer 1 13 Hund Dackel 10 44 Fisch Goldfisch 0,5 25 Katze Siam 7 56 Pferd Araber 3 17 Reptil Schlange 30 4

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

5 Laufer Thomas

Person

Haustier



b) Beziehungstyp


Eine Person kann N (d.h. 0, 1 oder mehr) Haustier haben,






Tabelle Haustier.





• Der Fremdschlüssel PNr in der Tabelle Haustier kann die gleichen

Attributwerte mehrmals verwenden.

• In der Tabelle Haustier existieren nur Tupel, die einen Bezug zur

Tabelle Person aufweisen.

• In der Tabelle Person können Tupel stehen, deren Id-Schlüsselwert

nicht im Fremdschlüssel PNr der Tabelle Haustier vorkommt.


TNr Art Rasse Alter PNr1 Vogel Papagei 20 32 Hund Boxer 1 13 Hund Dackel 10 14 Fisch Goldfisch 0,5 25 Katze Siam 7 56 Pferd Araber 3 17 Reptil Schlange 30 2

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

5 Laufer Thomas

Person

Haustier


Tupel mit PNr 4 besitzt in Haustier keinen Datensatz


c) Beziehungstyp


Eine Person muss N (d.h. mindestens 1) Haustier haben,

ein Haustier hat höchstens einen (d.h. keinen oder genau einen) Besitzer.



Würde man wieder die zwei Tabellen Person und Haustier wählen und

die Beziehung abbilden, so ergäbe sich die Eigenschaften wie im

Beziehungsfall a) mit der Besonderheit:

In der Tabelle Haustier können auch Tupel auftreten, die zu keinem

Tupel in der Tabelle Person einen Bezug haben.

Das folgende Beispiel zeigt die Übertragung der Beziehung mit Hilfe von

zwei Tabellen:


TNr Art Rasse Alter PNr1 Vogel Papagei 20 32 Hund Boxer 1 13 Hund Dackel 104 Fisch Goldfisch 0,5 25 Katze Siam 7 16 Pferd Araber 37 Reptil Schlange 30 4

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

Person

HaustierDas Attribut PNr, d.h. der Fremdschlüssel hat Null-Werte.


Die Abbildung der Beziehung c) mit Hilfe von zwei Tabellen führt zu

Nullwerten im Fremdschlüssel PNr.

Dies sollte vermieden werden.

Daher muss die Beziehung c) transformiert werden:

Es wird eine weitere Tabelle Tierhalter angelegt.






Tabelle Tierhalter.

• Der Id-Schlüssel der Tabelle Haustier ist gleichzeitig Id-Schlüssel der

Tabelle Tierhalter.






Die drei Tabellen haben folgende Eigenschaften:

• Zu jedem Tupel in der Tabelle Person muss es mindestens ein

Tupel in der Tabelle Tierhalter geben.

• Ein Haustier muss dagegen keinen Tierhalter haben.


TNr Art Rasse Alter1 Vogel Papagei 202 Hund Boxer 13 Hund Dackel 104 Fisch Goldfisch 0,55 Katze Siam 76 Pferd Araber 37 Reptil Schlange 30

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

Person

Haustier

PNr TNr

1 2

1 5

2 4

3 1

4 7

Tierhalter


Tierhalter

1

N 1

1


d) Beziehungstyp


Eine Person kann N (d.h. kein, ein oder mehr) Haustiere haben,

ein Haustier hat höchstens einen (d.h. keinen oder genau einen) Besitzer.



Würde man wieder die zwei Tabellen Person und Haustier wählen und

die Beziehung dort abbilden, so ergäben sich wieder Nullwerte im

Fremdschlüssel

Das folgende Beispiel zeigt die Übertragung der Beziehung mit Hilfe von

zwei Tabellen:


TNr Art Rasse Alter PNr1 Vogel Papagei 20 22 Hund Boxer 1 13 Hund Dackel 104 Fisch Goldfisch 0,5 25 Katze Siam 7 16 Pferd Araber 37 Reptil Schlange 30 4

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

Person

HaustierDas Attribut PNr, d.h. der Fremdschlüssel hat Null-Werte.

Diese Tupel gehören zu keiner Person.

Zu diesem Tupel gibt es kein Haustier






Tabelle Tierhalter.

• Der Id-Schlüssel der Tabelle Haustier ist gleichzeitig Id-Schlüssel der

Tabelle Tierhalter.







• Im Fremdschlüssel TNr der Tabelle Tierhalter darf jeder Attributwert

nur einmal vorkommen

• Im Fremschlüssel PNr der Tabelle Tabelle Tierhalter darf der gleiche

Attributwert mehrmals vorkommen

• Das Attribut TNr bildet den Id-Schlüssel für die Tabelle Tierhalter.


TNr Art Rasse Alter1 Vogel Papagei 202 Hund Boxer 13 Hund Dackel 104 Fisch Goldfisch 0,55 Katze Siam 76 Pferd Araber 37 Reptil Schlange 30

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

Person

Haustier

PNr TNr

1 2

1 5

2 1

2 4

4 7

Tierhalter


Tierhalter

1

N 1

1


Die N:M- Beziehung

Eine Person hat M Haustiere, ein Haustier gehört N Personen.

a) Beziehungstyp

Person HaustierhatN M

Eine Person hat mehrere (mindestens ein) Haustier,

ein Haustier gehört zu mehreren (mindestens einer) Person.



Auch dieser Beziehungstyp muss transformiert werden.

Eine Abbildung des Beziehungstyps in 2 Tabellen, würde zu

Mehrfacheinträgen in beiden Tabellen führen.

Beispiel: Die Personen Kai Meier und Ute Müller sind beide Besitzer des

Haustieres Papagei mit Alter 20.


PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

Person

Haustier

TNr Art Rasse Alter PNr1 Vogel Papagei 20 12 Hund Boxer 1 33 Hund Dackel 10 24 Fisch Goldfisch 0,5 11 Vogel Papagei 20 24 Fisch Goldfisch 0,5 24 Fisch Goldfisch 0,5 3

Die Beziehung ist korrekt dargestellt, aber:

Doppelte Datensätze gefährden die Datenkonsistenz.


Daher Transformation der Beziehung:


• Der Id-Schlüssel der Tabelle Tierhalter setzt sich zusammen aus dem

Attribut PNr und TNr.



Tierhalter (PNr,TNr)



PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

Person

Haustier

TNr Art Rasse Alter1 Vogel Papagei 202 Hund Boxer 13 Hund Dackel 104 Fisch Goldfisch 0,5

PNr TNr

1 1

1 4

2 3

2 1

2 4

3 2

3 4

TierhalterNur eine Kombination aus PNr und TNr kann in dieser Tabelle der Id-Schlüssel sein

Alle Tupel in den Tabellen sind nun verschieden


Tierhalter

1

M N

1


b) Beziehungstyp


Eine Person kann mehrere (kein, ein oder mehrere) Haustier haben,

ein Haustier gehört mehreren (mindestens einer) Personen.


Übertragung auf Tabellen

Die Beziehung ist ähnlich wie die Beziehung vom Typ a).

In der Tabelle Person können allerdings auch Tupel existieren, die keinen

Bezug zu einem Tupel in Tabelle Haustier besitzen.

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

Person

Haustier

TNr Art Rasse Alter PNr1 Vogel Papagei 20 12 Hund Boxer 1 33 Hund Dackel 10 24 Fisch Goldfisch 0,5 11 Vogel Papagei 20 24 Fisch Goldfisch 0,5 24 Fisch Goldfisch 0,5 3

Diese Person besitzt kein Haustier

Der Goldfisch hat mehrere Besitzer


Um auch hier Doppelspeicherung zu vermeiden, muss trans formiert

werden







Person

Haustier

PNr TNr

1 1

1 4

2 3

2 1

2 4

3 2

3 4



PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd



Tierhalter

1

M N

1


c) Beziehungstyp


Eine Person kann mehrere (kein, ein oder mehrere) Haustier haben,

ein Haustier kann mehreren Personen gehören.


Übertragung auf Tabellen

Die Beziehung ist ähnlich wie die Beziehung vom Typ a) und b).

In der Tabelle Haustier können allerdings auch Tupel existieren, die keinen

Bezug zu einem Tupel in Tabelle Person besitzen.

PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd

Person

Haustier

TNr Art Rasse Alter PNr1 Vogel Papagei 20 12 Hund Boxer 13 Hund Dackel 10 24 Fisch Goldfisch 0,5 11 Vogel Papagei 20 24 Fisch Goldfisch 0,5 24 Fisch Goldfisch 0,5 3

Diese Person besitzt kein Haustier

Der Hund hat keinen Besitzer


Hier tauchen also zwei Probleme auf:

Doppelspeicherung und Nullwerte im Fremdschlüssel.

Also muss transformiert werden:







Person

Haustier

PNr TNr

1 1

1 4

2 3

2 1

2 4

3 4



PNr Name Vorname

1 Meier Kai

2 Müller Ute

3 Becker Inga

4 Kohnen Bernd




Tierhalter

1

M N

1


Rekursiv binäre Beziehung

Mitarbeiter

1

verheiratet

1

Auf den nächsten Folien wird die Überführung zweier möglicher rekursiver Beziehungen dargestellt:

Beispiel 1


1. Möglichkeit der Umsetzung in Tabellenform

Eine Tabelle der Form

Mitarbeiter (MID, Name, Vorname, VID) ,

wobei VID Fremdschlüssel ist, der aus dem Id-Schlüssel MID von

Mitarbeiter gebildet wird.

Hier entstehen sehr viele NULL-Werte im Fremdschlüssel, da solche Ehen sehr selten

sind. Daher sollte diese Variante nicht gewählt werden.



Zwei Tabellen der Form

Mitarbeiter (MID, Name, Vorname) ,

MitarbeiterEhe (MID1, MID2, verheiratet_seit),

wobei MID1 und MID2 Fremdschlüssel sind, die aus dem Id-Schlüssel MID von

Mitarbeiter gebildet werden.



Drei Tabellen der Form

Mitarbeiter (MID, Name, Vorname) ,

EheName (EID, Name),

MitarbeiterEhe (MID, EID).

Bemerkung

Bei dieser Variante sind die Namen der Fremdschlüssel stets identisch mit dem Namen

des zugehörigen Id-Schlüssels.

In der Praxis würde man aber bei diesem Beispiel die Variante 2 vorziehen.


Mitarbeiter

1

leitet

N

Beispiel 2

Ein Mitarbeiter wird von genau einem Mitarbeiter (dies ist der Abteilungsleiter) geleitet,

ein Mitarbeiter kann N Mitarbeiter leiten.



Eine Tabelle der Form

Mitarbeiter (MNr, LNr, Name, Vorname) ,

wobei LNr Fremdschlüssel ist, der aus dem Id-Schlüssel MNr von

Mitarbeiter gebildet wird.

MNr LNr Name Vorname1 4 Schmidt Uwe2 4 Müller Anke3 3 Meier Bettina4 3 Dicke Malte5 3 Becker Ingo6 3 Fischer Volker7 4 Bauer Ute

Meier, Bettina ist Chefin, d.h. sie wird von sich selbst geleitet

Nachteil: Um zu Überprüfen, ob z. B. Volker Fischer Abteilungsleiter ist, müssen alle Attributwert von LNr auf den Eintrag 6 untersucht werden.



Zwei Tabellen der Form

Mitarbeiter (MNr, LNr, Name, Vorname) ,

Abteilungsleiter (LNr, MNr).

MNr LNr Name Vorname1 2 Schmidt Uwe2 2 Müller Anke3 1 Meier Bettina4 1 Dicke Malte5 1 Becker Ingo6 1 Fischer Volker7 2 Bauer Ute

Nachteil: Hier hängt der Id-Schlüssel MNr vom Fremdschlüssel LNr ab und umgekehrt hängt der Id-Schlüssel LNr vom Fremdschlüssel MNr ab.

LNr MNr1 32 4

Mitarbeiter

Abteilungsleiter



Drei Tabellen der Form

Mitarbeiter (MNr, ANr, Name, Vorname) ,

Abteilung (ANr, Name)

Abteilungsleiter (ANr, MNr).


MNr Anr Name Vorname1 2 Schmidt Uwe2 2 Müller Anke3 1 Meier Bettina4 1 Dicke Malte5 1 Becker Ingo6 1 Fischer Volker7 2 Bauer Ute

MNr ANr3 14 2Mitarbeiter

Abteilungsleiter

ANr Name

1 Bo1 Operativ

2 Bo1P Projekte

Abteilung


Tabellendarstellung für Ternäre- Beziehungen bzw. n-äre Beziehungen

Als Beispiel betrachten wir die folgende Ternäre-Beziehung

Projekt MitarbeiterhatN M

Qualifikation

P

Um jede Beziehung abzubilden, müssen zusätzlich zu den 3 Entities 3 weitere Beziehungstabellen gebildet werden.


MNr Name Vorname1 Schmidt Uwe2 Müller Anke3 Meier Bettina4 Dicke Malte5 Becker Ingo6 Fischer Volker7 Bauer Ute

PNr Name StDat Laufzeit1 CostPlus 01.09.2003 62 EasyGo 01.04.2004 12

MitarbeiterProjekt

QNr Name1 C++2 Oracle3 Project Manager4 GUI-Designer5 Business Analyst

Qualifikation

PNr QNr1 11 31 42 32 5

ProjektQualifikation

MNr PNr1 11 23 24 15 16 27 1

ProjektMitarbeiter

MNr QNr1 11 52 23 44 44 55 15 26 37 3

MaQualifikation

Beziehungstabellen


Die Tabellen haben die Form

Mitarbeiter (MNr, Name, Vorname) ,

Projekt (PNr, Name, StDat,Laufzeit)

Qualifikation (QNr, Name),

MaQualifikation (MNr, QNr)

ProjektQualifikation (PNr, QNr)

ProjektMitarbeiter (MNr, PNr)

Die Tabellen geben nun z.B. Auskunft über

• Mitarbeiter, die in keinem Projekt sind

• Alle Qualifikationen eines Mitarbeiters

• Die Qualifikationen, die im Projekt benötigt werden


Auch die Abbildung mit Hilfe einer einzigen Beziehungstabelle ist möglich.

Allerdings werden jetzt nicht alle Informationen abgebildet.

Informationen, die verloren gehen:

• Qualifikationen von Mitarbeiter, die in keinem Projekt sind

• Alle Qualifikationen eines Mitarbeiters


MNr Name Vorname1 Schmidt Uwe2 Müller Anke3 Meier Bettina4 Dicke Malte5 Becker Ingo6 Fischer Volker7 Bauer Uter

PNr Name StDat Laufzeit1 CostPlus 01.09.2003 62 EasyGo 01.04.2004 12

MitarbeiterProjekt

QNr Name1 C++2 Oracle3 Project Manager4 GUI-Designer5 Business Analyst

Qualifikation

PNr MNr QNr1 1 11 4 41 4 51 5 11 5 21 7 32 1 12 1 52 3 32 6 4

Projektressourcen

Beziehungstabelle


Generalisierung / Spezialisierung (IS-A)

Die spezialisierten Tabellen, d.h. die Subtypen (hier Laborassistent, Sekretär etc)

haben weitere Attribute gegenüber der Grundmenge, d.h. dem Supertyp (hier

Mitarbeiter).

Die entstehenden Subtypen können die Grundmenge total oder partiell überdecken

sowie disjunkt oder nicht disjunkt sein.

Sekretär

Mitarbeiter IS-Ad

Techniker

Professor

Laborassistent


Übertragen der Generalisierung auf Tabellen

Zusätzlich zur Tabelle für die Grundmenge wird eine weitere Tabelle für jede

Teilmenge angelegt, die denselben Id-Schlüssel wie die Grundmenge hat.

In unserem Beispiel würden also zusätzlich zur Tabelle Mitarbeiter vier weitere

Tabellen angelegt, wobei jede dieser Tabellen denselben Id-Schlüssel wie die

Tabelle Mitarbeiter hat.

Zusätzlich haben die Subtyp-Tabellen weitere charakteristische Attribute.

Beispiel: Mitarbeiter IS-A Laborassistent

Mitarbeiter (MaNr, Name, Vorname)

Laborassistent (MaNr, Labor)


Totale Überdeckung der Grundmenge

Man spricht von einer totalen Überdeckung der Grundmenge, wenn die

Entitätsmenge des Supertyps vollständig aus der Entitätsmenge der Subtypen

besteht.

D.h. in der Tabelle des Supertyps existieren keine Tupel, deren Id-Schlüssel nicht

in einer der Tabellen der Subtypen als Fremdschlüssel vorkommt.


Partielle Überdeckung der Grundmenge

Man spricht von einer partiellen Überdeckung der Grundmenge, wenn die

Entitätsmenge des Supertyps nur partiell aus der Entitätsmenge der Subtypen

besteht.

D.h. in der Tabelle des Supertyps existieren Tupel, deren Id-Schlüssel in keiner

der Tabellen der Subtypen als Fremdschlüssel vorkommen.


Disjunkte Überdeckung der Grundmenge

Man spricht von einer disjunkten Überdeckung der Grundmenge, wenn die

Entitätsmenge des Supertyps sich nicht überschneiden.

D.h. zu einem Id-Schlüssel des Supertypen gibt es nur ein Tupel in einem

Subtypen.


Nicht-Disjunkte Überdeckung der Grundmenge

Man spricht von einer nicht-disjunkten Überdeckung der Grundmenge, wenn die

Entitätsmenge des Supertyps sich überschneiden.

D.h. zu einem Id-Schlüssel des Supertypen kann es in mehreren Subtypen ein

entsprechendes Tupel geben.

Bemerkung:

Handelt es sich um disjunkte Mengen, so kann eindeutig angegeben werden,

welches Tupel aus dem Supertypen in welchem Subtypen vorkommt.

Daher kann in der Supertyp-Tabelle ein sog. diskriminierendes Attribut eingefügt

werden, das die Subtyp-Tabelle angibt.


Anmerkungen zu Null-Werten im Fremdschlüssel

Null-Werte im Fremdschlüssel sollten nur verwendet werden, wenn sie die

Ausnahme sind.

Unter referentieller Integrität versteht man die Bedingung für Fremdschlüssel,

dass diese nur Werte annehmen können, die im Wertebereich des

entsprechenden Id-Schlüssels liegen (oder NULL sind).


Die 10 Beziehungstypen

Durch die Transformation der einzelnen Beziehungen, können alle

Beziehungstypen durch die folgenden 4 Beziehungen ausgedrückt werden:

1 1

1 1

1 N

1 N

Können von einem Datenbankprogramm nicht direkt auf Datendefinitionsebene unterstützt werden.

Für die Realisierung dieser Beziehungen ist der Datenbankentwickler zuständig


Normalisierung

Die Normalisierung ist ein wichtiger Prozess in der Datenmodellierung.

Die Normalisierung bezweckt die redundanzfreie Speicherung von

Informationen innerhalb der Tabellen der Datenbasis.

Redundanzfreie Datenspeicherung: Kein Teil eines Datenbestandes kann

weggelassen werden, ohne dass dies zu Informationsverlusten führt.

Redundanzfreie Speicherung führt zum einen zu Speicherplatzersparnis,

zum anderen verhindert es Dateninkonsistenz und Löschanomalie.


Beispiel: Tabelle mit Redundanzen

Kurs_Nr Kurs_Bez Semester Doz_Kürzel Doz_Name14 Datenbanken WS 04/05 dm Meier7 Einführung Programmierung SS 05 dm Meier

16 Betriebssysteme I WS 04/05 ib Bauer9 Verteilte Systeme SS 05 ib Bauer

11 Data-Warehouse SS 05 ak Kühne

Redundanzen


Abhängigkeiten

Vorab müssen die unterschiedlichen Abhängigkeiten von Attributen

innerhalb einer Relation definiert werden:

Es werden drei Abhängigkeiten unterschieden

• Funktionale Abhängigkeit

• Volle Abhängigkeit

• Transitive Abhängigkeit


Definition

Ein Attribut bzw. eine Attributkombination B ist dann von einem Attribut

oder einer Attributkombination A funktional abhängig, wenn zu einem

bestimmten Attributwert von A genau ein Attributwert von B gehört.

Aus dem Attributwert von A ergibt sich also eindeutig der Attributwert

von B.

Beispiel: In der Tabelle Mitarbeiter (MNr, Name) ist das Attribut Name

funktional abhängig vom Attribut MNr.


Definition

Ein Attribut bzw. eine Attributkombination B ist dann von einer

Attributkombination A voll abhängig, wenn B nur von A, nicht jedoch von

einem Teil der Attributkombination A funktional abhängig ist.

Beispiel: In der Tabelle MitarbeiterQualifikation (MNr,QNr, zertifiziert)

ist das Attribut zertifiziert voll abhängig von der Kombination MNr und

QNr. Es gibt an, ob ein Mitarbeiter für eine entsprechende Qualifikation

zertifiziert ist. Das Attribut zertifiziert ist nur von der Kombination MNr

und QNr abhängig.


Definition

Ein Attribut bzw. eine Attributkombination C ist von einem Attribut oder

einer Attributkombination A transitiv abhängig, wenn das Attribut B von A

und das Attribut C von B funktional abhängig ist, aber A nicht von C

funktional abhängig ist.

Beispiel: In der Tabelle Mitarbeiter (MNr,AbtNr, Abteilung) ist das

Attribut Abteilung vom Attribut MNr transitiv abhängig, da

• Abteilung von AbtNr und AbtNr von MNr funktional abhängig ist.

• MNr ist von Abteilung aber nicht abhängig.

• Aus MNr folgt die AbtNr und aus AbtNr folgt die Abteilung.

• Also erhält man aus MNr auch die Abteilung.


Der Normalisierungsprozess verläuft schrittweise über die Bildung von

sog. Normalformen.

Es werden hier die ersten 4 Normalformen vorgestellt.

Im Beispiel sollen die unterschiedlichen Bankverbindungen von Firmen

sinnvoll dargestellt werden.

Bankverbindung Firmenname123 456 Sparkasse Frankfurt (630 500 00)234 567 Sparda-Bank Kiel (600 100 70) Bau u. Partner987 654 Postbank Bochum (600 908 00)876 543 Burkhardt

654 452 Commerzbank Frankfurt (500 400 00) 454 328 8 Deutsche Bank Hamburg (100 205 22)543 226 86 Sparkasse Frankfurt (631 500 00) WohnIdee

Diese Tabelle hat keine korrekte Form.


1. Normalform

Eine Tabelle befindet sich in der 1. Normalform, wenn alle Attribute nur

einfache Attributwerte aufweisen, wobei auch Nullwerte zulässig sind.

Nur atomare Merkmalswerte sind erlaubt.

KontoNr BLZ Geldinstitut FID Firmenname123456 630 500 00 Sparkasse Frankfurt 101 Bau und Partner234567 600 100 70 Sparda-Bank Kiel 101 Bau und Partner987654 600 908 00 Postbank Bochum 102 Burkhardt876543 600 908 00 Postbank Bochum 102 Burkhardt654452 500 400 00 Commerzbank Frankfurt 103 WohnIdee

4543288 100 20522 Deutsche Bank Hamburg 103 WohnIdee54322686 631 500 00 Sparkasse Frankfurt 103 WohnIdee

Firmenkonto

Firmenkonto (KontoNr, BLZ, Geldinstitut, FID, Firmenname)


2. Normalform

Eine Tabelle befindet sich in der 2. Normalform, wenn sie schon in

der

1. Normalform ist und jedes nicht zum Id-Schlüssel gehörende

Attribut voll vom Id-Schlüssel abhängig ist.

Es können sich also nur Tabellen mit zusammengesetzten Id-

Schlüsseln in der 2. Normalform befinden.


2. Normalform

KontoNr BLZ Geldinstitut FID Firmenname123456 630 500 00 Sparkasse Frankfurt 101 Bau und Partner234567 600 100 70 Sparda-Bank Kiel 101 Bau und Partner987654 600 908 00 Postbank Bochum 102 Burkhardt876543 600 908 00 Postbank Bochum 102 Burkhardt654452 500 400 00 Commerzbank Frankfurt 103 WohnIdee

4543288 100 20522 Deutsche Bank Hamburg 103 WohnIdee54322686 631 500 00 Sparkasse Frankfurt 103 WohnIdee

Geldinstitut ist nur von BLZ abhängig

Die Tabelle befindet sich also nicht in der 2. Normalform.


2. Normalform

KontoNr BLZ FID Firmenname123456 63050000 101 Bau und Partner234567 60010070 101 Bau und Partner987654 60090800 102 Burkhardt876543 60090800 102 Burkhardt654452 50040000 103 WohnIdee

4543288 10020522 103 WohnIdee54322686 63150000 103 WohnIdee

BLZ Geldinstitut63050000 Sparkasse Frankfurt60010070 Sparda-Bank Kiel60090800 Postbank Bochum

50040000 Commerzbank Frankfurt

10020522 Deutsche Bank Hamburg63150000 Sparkasse Frankfurt

Es entstehen 2 Tabellen

FirmenKonto (KontoNr, BLZ, FID, Firmenname)

Bank (BLZ, Geldinstitut)

FirmenKonto Bank


3. Normalform

Eine Tabelle befindet sich in der 3. Normalform, wenn sie schon in

der 2. Normalform (bzw. mit einfachem Id-Schlüssel in der 1.

Normalform) ist und kein Nichtschlüssel-Attribut vom Id-Schlüssel

transitiv abhängig ist.

Die Attribute innerhalb einer Tabelle sind also nur vom Id-Schlüssel

funktional abhängig. Untereinander existieren keine sonstigen

funktionalen Abhängigkeiten.


3. Normalform

Die Tabelle Bank befindet sich also schon in der 3. Normalform.

Die Tabelle FirmenKonto nicht, da das Attribut Firmenname vom Id-Schlüssel (KontoNr, BLZ) transitiv abhängig ist.

KontoNr BLZ FID Firmenname123456 630 500 00 101 Bau und Partner234567 600 100 70 101 Bau und Partner987654 600 908 00 102 Burkhardt876543 600 908 00 102 Burkhardt654452 50040000 103 WohnIdee

4543288 10020522 103 WohnIdee54322686 63150000 103 WohnIdee


3. Normalform: keine Abhängigkeiten über Umwege

Nun sind alle Tabellen in der 3. Normalform.

FID Firmenname101 Bau und Partner102 Burkhardt

103 WohnIdee

Bank

Firma

FirmenKonto

KontoNr BLZ FID123456 630 500 00 101234567 600 100 70 101987654 600 908 00 102876543 600 908 00 102654452 50040000 103

4543288 10020522 10354322686 63150000 103

BLZ Geldinstitut63050000 Sparkasse Frankfurt60010070 Sparda-Bank Kiel60090800 Postbank Bochum

50040000 Commerzbank Frankfurt

10020522 Deutsche Bank Hamburg63150000 Sparkasse Frankfurt


Tabellen, die sich in der 3. Normalform befinden, werden als normalisiert

bezeichnet.

Die darin enthaltenen Informationen sind redundanzfrei.

Dies gilt allerdings nur innerhalb der Relation und sagt nichts über die

Redundaz-Freiheit in der gesamten Datenbasis aus.


4. Normalform

Eine Datenbasis befindet sich in der 4. Normalform, wenn sich alle

Tabellen in der 3. Normalform befinden und nur noch lokale und

globale Attribute existieren.

Auch dürfen die Tabellen keine aus der Datenbasis abgeleiteten

Attribute, z.B. Berechnungen enthalten.


Bemerkung:

Es muss auch untersucht werden, ob sich ein Attribut aus Attributen

anderer Tabellen ableiten lässt.

Beispiel: In Tabelle

Rechnung (RechNr, RechDat, NettoWert, MWST, BruttoWert)

kann BruttoWert durch NettoWert und MWST berechnet werden.

Tabelle befindet sich nicht in der 4 NF.

Lösung: Streichen des Attributes BruttoWert.


Als lokale Attribute einer Tabelle bezeichnet man alle Attribute, die nur

innerhalb einer einzigen Tabelle vorkommen und nicht deren Id-Schlüssel

bilden, bzw. Bestandteile des Id-Schlüssels sind.

Als globale Attribute bezeichnet man alle Attribute, die mindestens in einer

Tabelle im Id-Schlüssel vorkommen bzw. den Id-Schlüssel bilden.


Zusammenfassung der Normalformen

1. Normalform: Tabelle hat nur Attribute mit einfachen Attributwerten.

2. Normalform: Tabelle ist in 1 NF und jedes nicht zum Id-Schlüssel

gehörende Attribut ist voll vom Id-Schlüssel abhängig.

3. Normalform: Tabelle ist in 2 NF (bzw. mit einfachem Id-Schlüssel in der

1. Normalform) und kein Nichtschlüssel-Attribut vom Id-

Schlüssel transitiv abhängig ist.

4. Normalform: Alle Tabellen sind in der 3. Normalform und nur noch

lokale und globale Attribute existieren.


Ein weiteres Beispiel: Tabellen vor der Normalisierung

Kunde (KuNr, Firma, Ort, AufNr)

Auftrag (AufNr, AufDat, LiefDat)

Artikel (ArtNr, ArtBez, LagNr, LagOrt, LagStr)

Position (ArtNr, AufNr, Menge, Preis)

Rechnung ( RechNr, RechDat, Nettowert, MWST, Bruttowert, AufNr)

Tabellen nachdem Datenbasis in 4 NF gebracht wurde

Kunde (KuNr, Firma, OId)

Ort (OId, Ort)

Auftrag (AufNr, AufDat, LiefDat,KuNr)

Artikel (ArtNr, ArtBez, Preis, LagNr)

Position (ArtNr, AufNr, Menge)

Rechnung ( RechNr, RechDat, AufNr, MWST)

Lager (LagNr, LagOrt, LagStr)


Einige Bemerkung zur Normalisierung:

• Durch die Normalisierung wird erreicht, dass die Tabellen redundanz-

frei sind.

• Durch das Überführen der Datenbasis in die 4. Normalform erreicht man

die redundanz-freie Speicherung der Daten innerhalb der gesamten

Datenbasis.

• Anwenden der Normalformen ist kein „Muss“, Voraussetzung ist nur,

dass die Tabellen mindestens in der 1NF vorliegen.

• Mit steigendem Normalisierungsgrad werden immer mehr Tabellen

erzeugt, so dass das Datenmodell sehr unübersichtlich wird. Dies kann

Auswirkungen auf die Performance bei Datenmanipulationen haben.

• Die 4. Normalform wird in der Praxis meist nicht angewendet.


Vorgehen beim Datenbankentwurf

Beim Entwurf werden zusammenfassend folgende Aktivitäten durchgeführt

(es kann, bzw. muss auch wieder zurückgesprungen werden):

1. Definition der AufgabenstellungZunächst wird die zu lösende Aufgabenstellung klar umrissen. Dabei können

größerer Vorhaben in mehrere kleine Zwischenschritte aufgeteilt werden. Es ist

wichtig, sich die Ziele vom Auftraggeber schriftlich bestätigen zu lassen.

2. InformationsbeschaffungEs werden alle für die Anwendung benötigten Informationen gesammelt.

3. Bestimmung der Entities mit ihren AttributenEs werden intuitiv die für die zu lösende Aufgabenstellung benötigten Entities festgelegt. Dabei werden die Daten der Informationsbeschaffung strukturiert,

indem zusammengehörige Daten zusammengefasst und einem Oberbegriff

zugeordnet werden. Beispiel „Firmenadresse“ und „Firmenname“ zu Oberbegriff „Kunde“.


Bei der Strukturierung ist zu beachten, dass

- jedes Attribut eines Entities einen direkten Bezug zu diesem Entity hat

- alle benötigten Informationen als Entities bzw. Attribute auftauchen

- keine berechneten Attribute existieren

Bestimmung des Id-Schlüssels

Es wird dasjenige Attribut bestimmt, dessen Wert innerhalb des Entities

eindeutig ist. Falls kein solches existiert, werden mehrere geeignete Attribute

zum Id-Schlüssel zusammengefasst oder ein künstlicher Schlüssel wird

angelegt.

• Ermittlung der Beziehungen

Mit Hilfe des ERM werden die Beziehungen zwischen den bisher definierten

Entities festgestellt.


6. Ableiten der Tabellenstruktur aus dem ERMAus dem ERM werden die Tabellenstrukturen einschließlich der

Fremdschlüssel abgeleitet.

7. Überprüfung des Entwurfs mit Hilfe der globalen N ormalisierung (bis 4NF). Damit können logische Fehler bei der ERM-Methode festgestellt

werden.

8. Festlegung der Datentypen und Formulierung der KonsistenzbedingungenFormulierung der Bedingungen, die von den gespeicherten Daten eingehalten

werden müssen. Damit ist sichergestellt, dass die Datenkonsistenz jederzeit

erhalten bleibt.

9. Test des EntwurfsErstellen der Datenbank als Prototyps. Testen anhand eines Testkonzeptes.

10. Transaktionen definieren

11. Anlegen von Benutzersichten und Zugriffsrechte


Ein Beispiel: Sitzplatzreservierung auf Flügen

Aufgabenstellung

Die neu gegründete Airline EasyFlight möchte ihr Sitzplatz-

Reservierungssystem über eine Datenbankanwendung abbilden. Für ein

konkretes Flugereignis soll erfasst werden, welcher Sitzplatz von welchem

Kunden reserviert wurde.

Dabei gilt:

• Ein Flugereignis ist ein konkreter Flug an einem bestimmten Datum.

• Ein Flug ist eindeutig charakterisiert durch eine Flugnummer und einen

Wochentag.

• Ein Flug besteht aus mehreren Teilstrecken (Legs).

• Die zu reservierenden Sitzplätze sind unterschieden in die Kategorien First,

Business und Economy.


Bildung der Entitätsmengen mit den Attributen

Flugzeug

Kennzeichen jedes Flugzeug hat ein eindeutiges Kennzeichen

Flugzeugtyp

Flotte Ein Flugzeug gehört einer bestimmten Flotte an,

z.B. A340, B747-400

Max_Kapazität gibt die maximale Kapazität, d.h. Personen+Fracht an


Flug

FlugNr Eine Flugnummer wird nur einmal pro Tag vergeben und

beinhaltet nicht den Hin- und Rückflug

WT Wochentag

DepA_F Abflug-Airport

ArrA_F Ankunft-Airport

Leg

LegNr Jedes Teilstück bekommt eine Nummer

DepA_L Abflug-Airport des Legs

ArrA_L Ankunft-Airport des Legs


Flugereignis

Datum Flug an einem bestimmten Datum

Sitz

SitzNr Sitzplatznummer

Kategorie F, C oder M

Bezeichnung z.B. 7D


Bestimmung bzw. Bildung der Id-Schlüssel


Festlegen der Beziehungen

1. Alle möglichen, gegenseitigen Beziehungen zwischen den Entitätsmengen sind

festzuhalten. Unklare Beziehungen sind anzuschreiben.

2. Streichen von redundanten Beziehungen.

3. Transformation der Beziehungen => Bildung zusätzlicher Entitäten


Überführung der Beziehung in Tabellenform

Flugzeugtyp ( FTID, Flotte, Max_Kapazität)

Flugzeug (FID, Kennzeichen, FTID)

Flug (FlugID,FlugNr, WT, DepA_F, ArrA_F, FID)

Leg (LegID, DepA_L, ArrA_L)

FlugLeg (FlugID, LegID)

Airport (AID, 3LC, Bezeichnung)

Flugereignis (FEID, Datum, LegID, FlugID)

Reservierung (RID, FEID, SitzID, Kundenname)

Sitz (SitzID, Kategorie, Bezeichnung, FTID )

Anmerkung: DepA_F, ArrA_F, DepA_L, ArrA_L sind Fremdschlüssel und

entsprechen AID.


Überprüfung des Entwurfs auf Normalisierung

Zunächst fällt auf, dass Redundanzen in den Tabellen sind, da der Airport zweimal

in Tabelle Flug und zweimal in Tabelle Leg auftaucht.

Der Abflug-Airport des 1. Legs eines Fluges muss mit dem Abflug-Airport des

Fluges übereinstimmen. Ebenso muss der Ankunft-Airport des letzten Legs mit

dem Ankunft-Airport des Fluges übereinstimmen. Daher sind DepA_F, ArrA_F in

Flug überflüssig. Zusätzlich muss aber eine Kennung eingefügt werden, die eine

Reihenfolge der Legs anzeigt.

Auch die Tabelle FlugLeg erweist sich als unnötig. Sie liefert keine neuen

Informationen.

Sind die Tabellen sonst alle in der 3NF, bzw. befindet sich Datenbasis in 4NF?


Festlegung der Datentypen und Formulierung der Kons istenzbedingungen

Beispiel für Datentypen

Tabelle Attribut Wertebereich

Airport APID Ganze Zahl

3LC Zeichenkette mit 3 Zeichen

Bezeichnung Zeichenkette mit 50 Zeichen

Sitz SitzID Ganze Zahl

Kategorie Nur Zeichen C, M, F zugelassen

Bezeichnung Zeichenkette


Formulierung der Konsistenzbedingungen

Bei diesem Schritt geht es darum, Bedingungen zu formulieren, die von den

gespeicherten Daten eingehalten werden müssen. Damit ist sichergestellt, dass

die Datenkonsistenz jederzeit erhalten bleibt.


Transaktionen definieren

Einige Transaktionen für unser Flugbeispiel

A: Einfügen, Löschen und Korrigieren von Reservierungen in der Tabelle

Reservierung.

B: Einfügen eines Datensatzes in der Tabelle Flugzeug

C: Einfügen, Updaten eines Datensatzes in der Tabelle Airport.

D: Einfügen eines Flugereignisses

E: Löschen eines Fluges


Nach dem Entwurf des Datenmodells, müssen die Tabellen physisch angelegt

werden, die Beziehungen müssen angelegt werden, die Tabellen müssen mit

Daten gefüllt werden, die Daten müssen manipuliert werden etc.

Dazu dient die Datenbanksprache SQL.


Die Datenbanksprache SQL

• SQL (Structured Query Language) wurde Ende der 70er Jahre von IBM

entwickelt und war ursprünglich für DB2 vorgesehen.

• Mitte der 80er Jahre wurde SQL als ANSI-Standard formuliert.

• Im Jahre 1992 wurde SQL92 zum Standard, seit 2000 gibt es SQL3.

• Die meisten Anbieter relationaler DBMS unterstützen ein erweitertes SQL.

=> es gibt kein einheitliches SQL sondern verschiedene Dialekte.

In der Vorlesung gehen wir auf Besonderheiten vom SQL-Server und Oracle ein.


Namenskonventionen für Tabellen

Tabellennamen und Attribute:

• müssen mit einem Buchstaben beginnen

• dürfen 1-30 Zeichen enthalten

• dürfen nur die folgenden Zeichen enthalten: A-Z, a-z, 0-9,_, $, #

• dürfen nicht den Namen eines anderen Objekts duplizieren, das demselben

Benutzer gehört

• dürfen nicht einem reservierten Wort entsprechen


Ausschnitt aus den verschiedenen Datentypen in ANSI- SQL

CHAR(size) (Synonym CHARACTER)

Zeichenkette mit der maximalen Länge

size. Werte dieses Datentyps müssen von

einfachen Hochkommata eingeschlossen

sein.

DEC(n,m) (Synonym DECIMAL)

Dezimalzahl mit Genauigkeit und Anzahl der

Nachkommastellen.

INT, FLOAT, REAL Datentypen für Zahlen

DATE Felder für Datum und Zeit


Einige Datentypen von Oracle

VARCHAR2(size) Zeichendaten variabler Länge

CHAR(size) Zeichendaten fester Länge

NUMBER(p,s) Numerische Daten variabler Länge.

Gesamtstellenzahl ist p, Anzahl der

Nachkommastellen ist s

LONG Zeichendaten variabler Länge mit bis zu 2

Gigabyte

DATE Datums- und Zeitwerte

BLOB Binärdaten bis zu 4 Gigabyte

CLOB Zeichendaten bis zu 4 Gigabyte


Einige Datentypen vom SQL Server

VARCHAR(size) Zeichendaten variabler Länge

CHAR(size) Zeichendaten fester Länge

FLOAT[(p)] Fließkommazahl, wobei p die Genauigkeit

festlegt.

REAL Fließkommazahl

DECIMAL(g,n) Fließkommazahl in Abhängigkeit von g,n. g

kennzeichnet die Anzahl aller Ziffern, p die

Anzahl der Ziffern hinter dem Komma.

INT ganzzahliger numerischer Wert, der in 4 Bytes

gespeichert wird.

DATETIME Datumswerte, gespeichert als Ganzzahlen in

vier Byte. Eingabe im Format

„MMMM dd yyyy“


Anlegen einer Tabelle: die CREATE TABLE Anweisung

Tabellen werden durch die Anweisung CREATE TABLE angelegt.

(Die Eckigen Klammern zeigen optionale Ausdrücke an.

Alle von SQL reservierten Wörter werden hier zur Identifizierung mit

Großbuchstaben geschrieben).

CREATE TABLE Tabellenname

(Attribut1 Datentyp [DEFAULT deftyp][Spalten_constraint],

Attribut2 Datentyp,

…., [table_constraint]);

- Für jedes Attribut dieser Tabelle muss Attributname und Datentyp angegeben

werden.


Optional sind

• DEFAULT-Option

• Spalten-Constraint

• Tabellen-Constraint


Constraints

• Constraints erzwingen Regeln auf Tabellenebene

• Die folgenden Constraint-Regeln sind gültig

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK


Constraint Beschreibung

NOT NULL Gibt an, dass die Spalte keinen NULL-

Wert enthalten darf.

UNIQUE Gibt eine Spalte oder Spaltenkombination an,

deren Werte in allen Zeilen der Tabelle

eindeutig sein müssen.

PRIMARY KEY Identifiziert jede Zeile der Tabelle eindeutig.

FOREIGN KEY Richtet eine Fremdschlüsselbeziehung

zwischen der Spalte und einer Spalte der

referenzierten Tabelle ein und setzt diese

durch.

CHECK Gibt eine Bedingung an, die erfüllt sein muss.


Constraints definieren

Constraints werden in der Regel gleichzeitig mit der Tabelle erstellt.

Sie können aber auch nach dem Erstellen der Tabelle hinzugefügt

werden.

Constraints auf Spaltenebene

Attribut [CONSTRAINT constraint_name] constraint_type,…

Constraints auf Tabellenebene

Attribut,….

[CONSTRAINT constraint_name] constraint_type (Attribut1,…)


NOT NULL-Constraint

Dieses Constraint kann nur auf Spaltenebene definiert werden.

Beispiel: Anlegen einer Tabelle Airport

CREATE TABLE Airport

(APID INT NOT NULL ,

A3LC CHAR(3) NOT NULL DEFAULT 'aaa',

ABEZ VARCHAR(100),

COUNTRY VARCHAR(100)

);

Constraint wird vom System benannt.


oder



A3LC CHAR(3) DEFAULT 'aaa‘

CONSTRAINT A_3LC NOT NULL,

ABEZ VARCHAR(100),

COUNTRY VARCHAR(100));

Constraint wird vom Nutzer benannt.


UNIQUE-Constraint

Ein UNIQUE-Constraint erfordert, dass jeder Wert eines Attributs oder

Attributkombination eindeutig ist.

Dieses Constraint wird auf Tabellen- oder Spaltenebene definiert.



A3LC CHAR(3) NOT NULL UNIQUE,

ABEZ VARCHAR(100),


);


oder



A3LC CHAR(3) NOT NULL ,

ABEZ VARCHAR(100),

COUNTRY VARCHAR(100),

CONSTRAINT A_3LC UNIQUE (A3LC)

);


PRIMARY KEY-Constraint

Ein PRIMARY KEY-Constraint erstellt für die Tabelle einen Id-Schlüssel.

Für jede Tabelle kann nur ein Id-Schlüssel erstellt werden.

Das PRIMARY KEY-Constraint ist ein Attribut oder eine Attributkombination, die

jedes Tupel einer Tabelle eindeutig identifiziert.


Beispiel


(APID INT PRIMARY KEY ,

A3LC CHAR(3) NOT NULL ,

ABEZ VARCHAR(100),


);

Bemerkung: Besteht ein Primary Key aus einer Attributkombination, so muss PRIMARY KEY als Constraint auf Tabellenebene angegeben werden.


FOREIGN KEY-Constraint

Ein FOREIGN KEY-oder referentielle Integritäts-Constraints bestimmten ein

Attribut oder eine Attributkombination als Fremdschlüssel und richten eine

Beziehung zwischen einem PRIMARY KEY in derselben oder einer anderen

Tabelle ein.

Ein FOREIGN KEY- Constraint kann auf Spalten- oder Tabellenebene definiert

werden.

Bemerkung: Ein FOREIGN KEY – Constraint wird von MySQL nur unterstützt mit

dem Tabellentyp TYPE=INNODB.


Beispiel

CREATE TABLE Abteilung

(AbtID NUMBER(6) PRIMARY KEY ,

Abt_Name VARCHAR2(20) NOT NULL

);

CREATE TABLE Mitarbeiter

(MID NUMBER(6) PRIMARY KEY ,

Name VARCHAR2(20) NOT NULL ,

Vorname VARCHAR2(20),

AbtID NUMBER(6) REFERENCES Abteilung (AbtID)

);


Schüsselwörter von FOREIGN KEY-Constraints

• FOREIGN KEY: definiert das Attribut der untergeordneten Tabelle auf

Tabellen-Constraint-Ebene.

• REFERENCES: identifiziert die Tabelle und das Attribut in der übergeordneten

Tabelle

• ON DELETE CASCADE: löscht die abhängigen Zeilen aus der

untergeordneten Tabelle, wenn eine Zeile in der übergeordneten Tabelle

gelöscht wird.

• ON DELETE SET NULL: konvertiert abhängige Fremdschlüsselwerte in NULL-

Werte.


CHECK-Constraint

Ein CHECK-Constraint definiert eine Bedingung, die jede Zeile erfüllen muss.

Beispiel:

CREATE TABLE Mitarbeiter (

….

gehalt NUMERIC (8,2) CHECK (gehalt > 0),

….)

Bemerkung: MySQL unterstützt dieses Constraint nicht.


Veränderung von bestehenden Tabellen

Die Strukturen bestehender Tabellen müssen unter Umständen verändert werden,

d.h. Spalten hinzufügen, Spaltendefinition verändern, Spalten entfernen,

Constraints verändern.

Dies erfolgt mit Hilfe der

ALTER TABLE- Anweisung


ALTER TABLE- Anweisung

Spalten hinzufügen:

ALTER TABLE Tabellenname ADD

Neuer_Attributname Datentyp [DEFAULT deftyp],…;

Bestehende Attribute ändern

ALTER TABLE Tabellenname MODIFY

Bestehendes_Attribut neuer_Datentyp [DEFAULT deftyp],…;

Attribut löschen

ALTER TABLE Tabellenname DROP

Bestehendes_Attribut,…;


Constraints nachträglich hinzufügen

Mit der ALTER TABLE Anweisung können auch Constraints nachträglich

bearbeitet werden.

Ein Constraint kann

• hinzugefügt oder gelöscht werden

• aktiviert oder deaktiviert werden

ALTER TABLE Tabellenname ADD[CONSTRAINT constraint] type (Attribut);

Beispiel für die Syntax zum Einfügen einer neuen Spalte


Daten in eine Tabelle einfügen mit INSERT

Die Syntax für das Einfügen eines Tupels in eine Tabelle lautet

INSERT INTO Tabellenname [(Attribut1 [, Attribut2…])]

VALUES (wert1 [,wert2…]);


Beispiel

Es sollen Tupel in die Tabelle Mitarbeiter (MID,Name, Vorname, Gehalt)

eingefügt werden.

Es gibt folgende Möglichkeiten

INSERT INTO Mitarbeiter

VALUES (100,'Beier','Marc',50000);

-> dann müssen Werte in Reihenfolge der Tabellendefinition angegeben

werden


Sind NULL-Werte für einzelne Attribute erlaubt, so müssen für diese nicht

unbedingt Werte eingegeben werden:

INSERT INTO Mitarbeiter(MID,Name)

VALUES(103,'Becker');

oder

INSERT INTO Mitarbeiter

VALUES (104,'Becker',NULL,NULL);


oder

INSERT INTO Mitarbeiter (MID,Name,Vorname, Gehalt)

VALUES (101,'Beier','Marc',50000);

werden die Attribute hinter dem Tabellennamen angegeben, so kann die

Reihenfolge vertauscht werden:

INSERT INTO Mitarbeiter(MID, Gehalt, Name, Vorname)

VALUES (102,50000,'Marc','Beier‘);


Abfragen von Datensätzen: die SELECT-Anweisung

Um Daten aus der Datenbank zu extrahieren, verwendet man die

SELECT Anweisung.

Mit einer SELECT Anweisung können folgende Aktionen ausgeführt werden:

• Projektion: Legt fest, welche Spalten einer Tabelle die Abfrage

zurückgibt. Es können beliebig viele Spalten der Tabelle gewählt werden.

• Auswahl: Legt fest, welche Zeilen einer Tabelle die Abfrage zurückgibt.

Es können verschiedene Kriterien angegeben werden, um die

angezeigten Zeilen einzuschränken.

• Join: Die in verschiedenen Tabellen gespeicherten Daten können durch

Verknüpfung zusammengebracht werden.


Grundlegende SELECT-Anweisung

SELECT * | {[DISTINCT] Attribut | Ausdruck [alias],…}

FROM tabellenname;

| steht für „alternativ“

• SELECT bestimmt, welches Attribut

• FROM bestimmt, welche Tabelle


mid name vorname abteilung gehalt100 Hoffmann Richard F3/20 40000,00101 Schulte Karin F2/4 Projekte 60450,00102 Becker Malte D13/1 25230,00103 Schmidt Andreas A12/2 Marketing 65000,00104 Schlodder Kim 40000,00105 Hoffmann Richard F3/21 42000,00

Die Tabelle mitarbeiter (mid,name,vorname,abteilung,gehalt) habe 6 Tupel:

Alle Spalten auswählen

SELECT *

FROM mitarbeiter;

liefert

Alternativ können auch statt * alle Attribute angeben werden:

SELECT mid, name, vorname, abteilung, gehalt

FROM mitarbeiter


Bestimmte Spalten auswählen

SELECT name, vorname

FROM mitarbeiter;

liefertname vorname

Hoffmann RichardSchulte KarinBecker MalteSchmidt Andreas

Schlodder KimHoffmann Richard

Die Reihenfolge der Angabe der Attribute entscheidet über die Reihenfolge

der Ausgabe.


Arithmetische Ausdrücke

In der Anzeige können auch Zahlendaten durch arithmetisch verknüpft

werden.

SELECT name, vorname, gehalt, gehalt/12 + 100

FROM mitarbeiter;

liefert

name vorname gehalt gehalt/12+100Hoffmann Richard 40000,00 3433,3333Schulte Karin 60450,00 5137,5000Becker Malte 25230,00 2202,5000Schmidt Andreas 65000,00 5516,6667

Schlodder Kim 40000,00 3433,3333Hoffmann Richard 42000,00 3600,0000

Bemerkung : Es wird keine neue Spalte in der Tabelle erzeugt sondern nur in der Anzeige eine weitere Spalte hinzugefügt.


Attribut-Aliasnamen definieren

Ein Attribut-Aliasname

• benennt ein Attribut in der Ausgabe um

• kann z.B. nützlich bei nicht-aussagekräftigen Attributsnamen oder bei

Berechnungen sein

• wird direkt hinter dem Attributnamen angegeben. Optional kann

zwischen Attribut und Aliasname AS angegeben werden

• hat der Aliasname Leerzeichen, so muss er in “ “ gesetzt werden

Bemerkung : in Oracle werden in der Anzeige alle Attribute in Großbuchstaben ausgegeben. Sollen sie auch in Kleinbuchstaben ausgegeben werden, müssen sie ebenfalls in “ “ gesetzt werden.


SELECT name AS Mitarbeitername,gehalt, gehalt/12 AS Monatsgehalt

FROM mitarbeiter;

Attribut-Aliasnamen verwenden

Mitarbeitername gehalt MonatsgehaltHoffmann 40000,00 3333,3333Schulte 60450,00 5037,5000Becker 25230,00 2102,5000

Schmidt 65000,00 5416,6667Schlodder 40000,00 3333,3333Hoffmann 42000,00 3500,0000

SELECT name "Mitarbeiter Name", gehalt, gehalt/12 Monatsgehalt

FROM mitarbeiter; Mitarbeiter Name gehalt MonatsgehaltHoffmann 40000,00 3333,3333Schulte 60450,00 5037,5000


nameHoffmannSchulteBeckerSchmidt

SchlodderHoffmann

Mehrfach vorhandene Zeilen ausblenden mit DISTINCT

SELECT name

FROM mitarbeiter;

liefert

SELECT DISTINCT name

FROM mitarbeiter

liefert dagegen

nameHoffmannSchulteBeckerSchmidt

Schlodder


Daten einschränken und sortieren – die WHERE- Klausel

Die zurückgegebenen Zeilen werden mit Hilfe der WHERE-Klausel

eingeschränkt.


FROM tabellenname

[WHERE bedingung(s)];

Die WHERE-Klausel besteht aus

• Spaltenname,

• Vergleichsoperator,

• Attribut, Konstante oder Werteliste


Die WHERE-Klausel

SELECT *

FROM mitarbeiter

WHERE mid = 101;

liefertmid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00

mid name vorname gehalt100 Hoffmann Richard 40000,00

oder SELECT mid, name, vorname , gehalt

FROM mitarbeiter

WHERE abteilung= 'F3/20';

Zeichenfolgen müssen in Hochkommata gesetzt werden


Die folgenden Vergleichsoperatoren sind möglich

=><>=<<=<> (auch !=)

SELECT *

FROM mitarbeiter

WHERE gehalt > 40000

mid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00103 Schmidt Andreas A12/2 Marketing 65000,00


Bemerkung

In einer WHERE-Klausel darf kein Alias verwendet werden.

Andere Vergleichsoperatoren

Operator Bedeutung

BETWEEN….AND…. Zwischen zwei Werten

(einschließlich dieser Werte)

IN (menge) Entspricht einem Wert aus der Menge

LIKE Entspricht einem Zeichenmuster

IS NULL ist ein NULL-Wert


SELECT *

FROM mitarbeiter

WHERE gehalt BETWEEN 40000 AND 60450;

Der Operator BETWEEN

mid name vorname abteilung gehalt100 Hoffmann Richard F3/20 40000,00101 Schulte Karin F2/4 Projekte 60450,00104 Schlodder Kim 40000,00105 Hoffmann Richard F3/21 42000,00

liefert


SELECT *

FROM mitarbeiter

WHERE mid IN (100, 104);

Der Operator IN

mid name vorname abteilung gehalt100 Hoffmann Richard F3/20 40000,00104 Schlodder Kim 40000,00

liefert

Bemerkung : Zeichen- oder Datumswerte müssen in der Liste in Hochkommata

(‘‘)gesetzt werden.


Mit LIKE kann eine Platzhaltersuche durchgeführt werden.

Der Platzhalter % steht für kein, ein oder beliebig viele Zeichen

Der Platzhalter _ steht für genau ein Zeichen.

SELECT *

FROM mitarbeiter

WHERE name LIKE 'S%' ;

Der Operator LIKE

mid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00103 Schmidt Andreas A12/2 Marketing 65000,00104 Schlodder Kim 40000,00


SELECT *

FROM mitarbeiter

WHERE abteilung IS NULL;

Der Operator IS NULL

mid name vorname abteilung gehalt104 Schlodder Kim 40000,00

liefert

Entsprechen kann auf IS NOT NULL abgefragt werden.


Operator Bedeutung

AND TRUE, falls beide Komponenten-bedingungen wahr sind

OR TRUE, falls eine der beiden Komponenten-bedingungen wahr ist.

NOT TRUE, falls die Bedingung falsch ist.

Logische Operatoren

Mit diesen Operatoren können mehrere Bedingungen in der WHERE-

Klausel verknüpft werden.


SELECT *

FROM mitarbeiter

WHERE name LIKE 'SCH%'

AND gehalt > 50000;

liefert

mid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00103 Schmidt Andreas A12/2 Marketing 65000,00


Dagegen ergibt die Anweisung

SELECT *

FROM mitarbeiter

WHERE name LIKE 'SCH%'

OR gehalt > 50000;

mid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00103 Schmidt Andreas A12/2 Marketing 65000,00104 Schlodder Kim 40000,00


Der NOT-Operator kann mit anderen Operatoren wie IN, BETWEEN, LIKE

und NULL kombiniert werden.

SELECT *

FROM mitarbeiter

WHERE mid NOT IN (100,104)

mid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00102 Becker Malte D13/1 25230,00103 Schmidt Andreas A12/2 Marketing 65000,00105 Hoffmann Richard F3/21 42000,00


Die ORDER BY Klausel in Verbindung mit

ASC sortiert in aufsteigender Reihenfolge

DESC sortiert in absteigender Reihenfolge.

Wird hinter ORDER BY weder ASC noch DESC angegeben, so wird

aufsteigend sortiert.

Syntax


FROM tabellenname

[WHERE bedingung(s)]

[ORDER BY {Attribut , Ausdruck ,alias} [ASC|DESC|]];

Sortieren mit der ORDER BY-Klausel


SELECT *

FROM mitarbeiter

ORDER BY name DESC;

Absteigend und Aufsteigend sortieren

liefert

mid name vorname abteilung gehalt101 Schulte Karin F2/4 Projekte 60450,00103 Schmidt Andreas A12/2 Marketing 65000,00104 Schlodder Kim 40000,00105 Hoffmann Richard F3/21 42000,00100 Hoffmann Richard F3/20 40000,00102 Becker Malte D13/1 25230,00



FROM mitarbeiter

ORDER BY gehalt DESC;

Es kann auch nach Attributen sortiert werden, die nich t in der SELECT Anweisung enthalten sind


SELECT name, gehalt/12 monatsgehalt

FROM mitarbeiter

ORDER BY monatsgehalt, name;

Auch nach mehreren Attributen oder nach alias kann sor tiert werden

name monatsgehaltBecker 2102,5

Hoffmann 3333,333333Schlodder 3333,333333Hoffmann 3500Schulte 5037,5Schmidt 5416,666667


SQL-Funktionen

Es gibt zwei verschiedene Arten von Funktionen:

• Single Row-Funktionen

Diese Funktionen bearbeiten nur einzelne Zeilen und geben ein Ergebnis pro

Zeile zurück.

• Multiple Row-Funktionen

Diese Funktionen können Gruppen von Zeilen bearbeiten, um ein Ergebnis

pro Zeilengruppe zurückzugeben (Gruppenfunktionen).


Single Row-Funktionen

• bearbeiten Datenelemente

• bearbeiten jede zurückgegebene Zeile

• geben ein Ergebnis pro Zeile zuück

• akzeptieren Spalten oder Ausdrücke als Argumente

• können verschachtelt sein.

Im Folgenden werden einige dieser Funktionen behandelt.


Zeichenfunktionen

• haben als Input Zeichendaten, als Output Zeichenwerte oder numerische

Werte.

• es wird unterschieden zwischen Zeichenfunktionen zur Umwandlung von

Groß-/Kleinbuchstaben

• Funktionen zum Bearbeiten von Zeichen

Funktion Zweck

LOWER(attribut|ausdruck)Konvertiert alphanumerische Zeichenwerte in Kleinbuchstaben

UPPER(attribut|ausdruck)Konvertiert alphanumerische Zeichenwerte in Großbuchstaben

CONCAT(attribut1|ausdruck1,attribut2|ausdruck2) Verkettung der Attribute bzw. AusdrückeLENGTH(attribut|ausdruck)

TRIM(trim_character FROM trim_source)

entfernt am Anfang und Ende das Zeichen oder die Zeichenkette trim_character aus trim_source


SELECT LOWER(name), UPPER(vorname),

CONCAT(vorname,' ',name) AS mitarbeitername, LENGTH(name),

TRIM('Sch' FROM name)

FROM mitarbeiter;

Beispiel für das Verwenden von Zeichenfunktionen

LOWER(name) UPPER(vorname) mitarbeitername LENGTH(name TRIM('Sch' FROM name)hoffmann RICHARD Richard Hoffmann 8 Hoffmannschulte KARIN Karin Schulte 7 ultebecker MALTE Malte Becker 6 Becker

schmidt ANDREAS Andreas Schmidt 7 midtschlodder KIM Kim Schlodder 9 lodderhoffmann RICHARD Richard Hoffmann 8 Hoffmann


Numerische Funktionen

• haben als Input und Output numerische Werte

Funktion Zweck

ROUND (attribut|ausdruck,n)

Rundet die Spalte bzw. den Ausdruck auf n Dezimalstellen. Ist kein Wert angegeben, wird auf einen ganzzahligen Wert gerundet.

TRUNC (attribut|ausdruck,n)bzw. TRUNCATE(attribut|ausdruck,n) in MySQL

Schneidet die Spalte bzw. den Ausdruck auf n Dezimalstellen ab. Ist kein Wert für n angegeben, werden die Ziffern hinter dem Dezimalkomma abgeschnitten

MOD (m,n) Gibt den ganzzahligen Rest von m/n zurück.SQRT(attribut|ausdruck) Berechnet die WurzelSIN(…), COS(..), TAN(…) Winkelfunktionen…

Auszug der Funktionen


SELECT gehalt, gehalt/12, ROUND(gehalt/12,2),

TRUNC (gehalt/12,2), MOD(gehalt, 12)

FROM mitarbeiter;

Beispiel für das Verwenden von Numerischen Funktionen

gehalt gehalt/12 ROUND(gehalt/12,2) TRUNC(gehalt/12) MOD(gehalt,12)65000 5416,6667 5416,67 5416,66 825230 2102,5 2102,50 2102,50 660450 5037,5 5037,50 5037,50 640000 3333,333333 3333,33 3333,33 440000 3333,333333 3333,33 3333,33 442000 3500 3500,00 3500,00 0


Datums- und Zeitwerte

In SQL2 gibt es die Datentypen DATE und TIME.

DATE umfasst 10 Stellen im Format YYYY-MM-DD.

TIME hat 8 Stellen im Format HH:MM:SS.

Zusätzlich gibt es den Datentyp TIMESTAMP, der DATE- und TIME-Felder und

Stellen für Bruchteile von Sekunden beinhaltet.

Die Datums- und Zeitwerte sind je nach DBMS unterschiedlich.


Datums- und Zeitwerte in Oracle

• Oracle speichert Datumswerte in einem internen numerischen Format:

Jahrhundert, Jahr, Monat, Tag, Stunde, Minute, Sekunde

• Das Default-Format für die Anzeige ist DD-MM-JJ

• Mit der Funktion TO_CHAR(datum,‘format‘) kann ein datum in einem anderen

Format angezeigt werden.

• SYSDATE Funktion, die das aktuelle Datum und die aktuelle Uhrzeit des

Datenbankservers zurückgibt

• Da Datumswerte als Zahlen gespeichert werden, können mit Hilfe der

arithmetischen Operatoren Berechnungen durchgeführt werden.


Einige Funktionen für Datum in Oracle

Funktion Bedeutung

MONTH_BETWEEN(date1,date2) Anzahl der Monate zwischen zwei Datumswerten

ADD_MONTHS(date,n) Kalendermonat zu einem Datum addieren

NEXT_DAY(date,‘char‘) Datum des nächsten Wochentages

LAST_DAY(date) Letzter Tag des Monats

ROUND Datumswert runden

TRUNC Datumswert abschneiden


Datums- und Zeitwerte beim SQL-Server

• Der SQL-Server kennt die Datentypen DATETIME und SMALLDATETIME.

DATETIME speichert das Datum und die Zeit in Sekundengenauigkeit,

SMALLDATETIME in Minutengenauigkeit.

• GETDATE() Funktion, die das aktuelle Datum und die aktuelle Uhrzeit des

Datenbankservers zurückgibt


Einige Funktionen für Datum beim SQL-Server

DATEADD(datumsteil, anzahl, datum): Zu einem gegebenen Datum kann eine

bestimmte Anzahl an Intervallen hinzugefügt oder abgezogen werden. Intervalle sind

year, month, week, quarter, hour, minute, second(date,n)

DATEDIFF(datumsteil, startdatum, enddatum): Liefert Differenz zwischen zwei

Datumswerten

DATENAME(datumsteil, datum): Liefert den angegebenen Datumsteil

DAY(datum), MONTH(datum), YEAR(datum): Liefert den entsprechenden Datumsteil

als Zahl.


Bedingte Ausdrücke

Bedingte Ausdrücke stellen die IF-THEN-ELSE Logik innerhalb einer SQL-

Anweisung bereit.

In SQL werden sie mit Hilfe von CASE ausgeführt.

Syntax

CASE attribut|ausdruck WHEN vergleichs_ausdruck1 THEN return_ausdruck1

[WHEN vergleichs_ausdruck2 THEN return_ausdruck2

WHEN vergleichs_ausdruck3 THEN return_ausdruck3

…

ELSE else_ausdruck]

END


SELECT name, vorname, abteilung, gehalt,

CASE abteilung WHEN 'A12/2 Marketing' THEN gehalt+100

WHEN 'F3/20' THEN gehalt + 500

ELSE gehalt END 'Angepasstes Gehalt'

FROM mitarbeiter;

Beispiel für das Verwenden von CASE

name vorname abteilung gehalt Angepasstes GehaltHoffmann Richard F3/20 40000,00 40500,00Schulte Karin F2/4 Projekte 60450,00 60450,00Becker Malte D13/1 25230,00 25230,00Schmidt Andreas A12/2 Marketing 65000,00 65100,00

Schlodder Kim 40000,00 40000,00Hoffmann Richard F3/21 42000,00 42000,00


Tupel in einer Tabelle werden in SQL mit Hilfe der UPDATE Anweisung geändert:

Syntax

UPDATE tabellenname

SET attribut = wert [, attribut2 = wert,…]

[WHERE bedingung];

EINSCHUB- Daten in Tabellen ändern und löschen

Es können ein oder mehrere Tupel pro Tabelle geändert werden.


Die Tabelle airport habe die folgenden Attribute und Datentypen (hier in MySQL):

Beispiel für das Ändern von Tupeln

Field Type

APID int(11)

A3LC char(3)

ABEZ varchar(100)

COUNTRY varchar(100)

UPDATE_DAT timestamp(14)

INSERT_DAT timestamp(14)

INSERT INTO airport

VALUES (100,'FRA', 'Frankfurt','Deutschland',NULL,NULL);

In die Tabelle werden zwei Datensätze eingefügt:

INSERT INTO airport (APID, A3LC, ABEZ, COUNTRY,INSERT_DAT)

VALUES (105,'LAX', 'Los Angeles','USA',NULL);


Damit hat die Tabelle den Inhalt

UPDATE airport

SET ABEZ = 'Frankfurt am Main'

WHERE APID = 100;

Frankfurt soll in Frankfurt am Main geändert werden:

Satz wurde geändert


Tupel in einer Tabelle werden in SQL mit Hilfe der DELETE Anweisung gelöscht:

Syntax

DELETE [FROM] tabellenname

[WHERE bedingung];

Löschen von Tupeln


Daten aus mehreren Tabellen anzeigen


mitarbeiter abteilung

Das Ergebnis der Abfrage:


Um Daten aus mehreren Tabellen anzuzeigen, müssen sog.

JOIN-Bedingungen benutzt werden.

In SQL99 gibt es In Oracle

• Natural-Join/Inner-Join Equi-Join

• Left-Outer-Join bzw. Right-Outer-Join Outer-Join

• Self-Join Self-Join

• Cross-Join Kartesisches Produkt

Seit Oracle9i unterstützt Oracle auch die Standard-Joins.


Equi-Joins bzw. Inner-Joins


Fremdschlüssel Id-Schlüssel

Um für eine M_ID den Abteilungsname zu erhalten, müssen die Attributwerte aus der Spalte ABT_ID der Tabelle mitarbeiter mit ABT_ID aus der Tabelle abteilung verglichen werden.

Die Verknüpfung der beiden Tabellen nennt man Equi-Join oder Inner-Join.


SELECT mitarbeiter.m_id, mitarbeiter .name,

mitarbeiter.vorname, abteilung .abt_name

FROM mitarbeiter, abteilung

WHERE mitarbeiter.abt_id = abteilung.abt_id;

Datensätze mit Equi-Join abfragen

JOIN-Bedingung


Die Attribute aus den unterschiedlichen Tabellen müss en eindeutig

sein

Die Eindeutigkeit wird gewährleistet, indem Tabellenpräfixe gesetzt

werden. Z.B. mitarbeiter.abt_id

Tabellenpräfixe verbessern die Performance bei Abfragen über mehrere

Tabellen.


Die Abfragen sollten über Tabllen-Aliasnamen vereinfacht werden.

Durch das Verwenden von Tabellenpräfixen wird die Performance verbessert.

Beispiel

SELECT m.m_id, m.name,

m.vorname, a.abt_name

FROM mitarbeiter m, abteilung a

WHERE m.abt_id = a.abt_id;

Tabellen-Aliasnamen

Der Tabellen-Aliasname gilt nur für die aktuelle SELECT-Anweisung.





WHERE m.abt_id = a.abt_id

AND m.name = 'Schutt';

Zusätzliche Suchkriterien mit AND


Mehrere Tabellen verknüpfen

mitarbeiterabteilung standort


SELECT m.m_id, m.name, m.vorname, a.abt_name,s.stadt

FROM mitarbeiter m, abteilung a, standort s


AND a.abt_sitz = s.abt_sitz




FROM mitarbeiter m INNER JOIN abteilung a

ON m.abt_id = a.abt_id;

Alternativ kann auch ein INNER JOIN gesetzt werden

SELECT m.m_id, m.name, m.vorname,a.abt_name,s.stadt


ON m.abt_id = a.abt_id

INNER JOIN standort s

ON a.abt_sitz = s.abt_sitz;

oder bei Verbindungen von 3 Tabellen


Bei den bisherigen JOINS müssen die vergleichenden Attribute nicht gleich heißen.

Beispiel:

mitarbeite r(m_id, name, vorname, abtid),

abteilung (abt_id, abt_name, abt_sitz)

dann ist ein INNER JOIN gegeben durch

Bemerkung

SELECTm.m_id, m.name,



ON m.abtid = a.abt_id;


Heißen die zu verbindenden Attribute in den Tabellen gleich, so kann man bei Oracle

auch statt des ON ein USING setzen:

Beispiel: Statt




ON m.abt_id = a.abt_id;

kann

INNER JOIN USING

SELECT m.m_id, m.name, m.vorname, a.abt_name


USING (abt_id);die Klammer muss gesetzt werden

es darf kein Tabellenpräfix gesetzt werden


Outer-Joins


Ein OUTER-JOIN zeigt auch Datensätze an, die keinen Bezug zur zweiten Tabelle haben.

Diese Sätze würden bei einem INNER-JOIN rausfallen


Eine Verknüpfung zwischen zwei Tabellen, die das Ergebnis des INNER JOINS

sowie die Zeilen ohne Übereinstimmung in der linken Tabelle zurückgibt, wird als

LEFT-OUTER-JOIN bezeichnet.

Eine Verknüpfung zwischen zwei Tabellen, die das Ergebnis des INNER JOINS

sowie die Zeilen ohne Übereinstimmung in der rechten Tabelle zurückgibt, wird als

RIGHT-OUTER-JOIN bezeichnet.

Durch das Vertauschen der Tabellen, ist der RIGHT OUTER JOIN überflüssig.

LEFT OUTER JOIN ( RIGHT OUTER JOIN)


Beispiel für einen LEFT OUTER JOIN

SELECT m.m_id, m.name, m.vorname, a.abt_id, a.abt_name

FROM mitarbeiter m

LEFT OUTER JOIN abteilung a

ON (m.abt_id = a.abt_id);

alle Tupel der linken Tabelle mitarbeiter werden angezeigt


Beispiel für einen RIGHT OUTER JOIN


FROM mitarbeiter m

RIGHT OUTER JOIN abteilung a


alle Tupel der rechten Tabelle abteilung werden angezeigt


FULL OUTER JOIN

Sollen alle Datensätze aus den beteiligten Tabellen angezeigt werden, so spricht

man von einem FULL OUTER JOIN. Wo es möglich ist, werden auch hier

Verknüpfungen vorgenommen.

Beispiel


FROM mitarbeiter m

FULL OUTER JOIN abteilung a


Bemerkung: Der FULL OUTER JOIN existiert nicht unter MySQL.


SELF JOIN

Beim SELF JOIN wird eine Tabelle mit sich selbst verknüpft.

Attributwerte vom Fremdschlüssel CHEF_ID entsprechen denen vom Id-Schlüssel M_ID

Um CHEF_ID aufzulösen, muss die Tabelle mitarbeiter mit sich selbst verknüpft werden.

mitarbeiter


Beispiel für einen SELF JOIN

SELECT m.m_id, m.name, m.vorname,

concat(v.vorname,' ' , v.name) AS vorgesetzter

FROM mitarbeiter m , mitarbeiter v

WHERE m.chef_id = v.m_id;


Was ergibt die folgende SELECT-Anweisung?

SELECT m.m_id, m.name, m.vorname,

concat(v.vorname,' ' , v.name) AS vorgesetzter

FROM mitarbeiter m , mitarbeiter v

WHERE m.m_id = v.chef_id;


CROSS JOIN

Der CROSS JOIN entspricht dem Kartesischen Produkt aus zwei Tabellen.

Beispiel

SELECT m.name, a.abt_name

FROM mitarbeiter m , abteilung a;

entspricht

SELECT m.name, a.abt_name

FROM mitarbeiter m

CROSS JOIN abteilung a;

Alternativ zu CROSS JOIN kann auch nur JOIN gesetzt werden.


Der NATURAL JOIN

Der NATURAL JOIN entspricht dem INNER JOIN mit einer USING Klausel. Es

werden alle Datensätze mit einer Verbindung angezeigt, deren Spalten den selben

Namen haben.

Beispiel

mitarbeiterabteilung standort


Beispiel für den NATURAL JOIN


FROM mitarbeiter m

NATURAL JOIN abteilung a

NATURAL JOIN standort s;


Beispiel für den NATURAL JOIN

Hat aber der Id-Schlüssel der Tabelle standort nicht den Namen abt_sitz sondern

ab_sitz, so ergibt die folgende SELECT Anweisung 56 Datensätze


FROM mitarbeiter m

NATURAL JOIN abteilung a

NATURAL JOIN standort s;

…


Non- Equi- Joins

Equi-Joins selektieren nur die Datensätze, die durch = miteinander verbunden

werden (z.B. WHERE m.abt_id = a.abt_id).

Benutzt man einen anderen Operator als den = Operator, so spricht man von

einem Non-Equi-Join.


Beispiel für eine Non-Equi-Join

Die Tabelle mitarbeiter habe noch eine Spalte gehalt.

Die Tabelle gehaltstufe gibt die Gehaltsstufen innerhalb der Firma an.


Non- Equi- Join

SELECT m.m_id, m.name, m.vorname, m.gehalt, g.gehalt_stufe

FROM mitarbeiter m, gehaltstufe g

WHERE m.gehalt BETWEEN g.gehalt_von AND g.gehalt_bis;


Daten mit Gruppenfunktionen aggregieren


Gruppenfunktionen

Im Gegensatz zu Single-Row Funktionen werden Gruppenfunktionen auf

Gruppen von Zeilen angewendet und geben ein Ergebnis pro Gruppe zurück.

Gruppenfunktionen sind z.B.

COUNT( ) Anzahl der Zeilen.

SUM(n) Summe der Werte von n.

MIN() Minimum

MAX() Maximum

AVG() Durchschnitt


Die Syntax von Gruppenfunktionen

SELECT [attribut,] gruppen_funktion(attribut),…

FROM tabellenname

[WHERE bedingung]

[GROUP BY attribut]

[ORDER BY attribut]

Für die Gruppenfunktionen gilt:

• Alle Funktionen können zusätzlich zum Attribut als Argument ein

DISTINCT haben. DISTINCT bewirkt, dass die Funktion keine

doppelten Werte berücksichtigt.

• Alle Gruppenfunktionen ignorieren NULL-Werte.


Funktionen AVG(), SUM(), MIN() und MAX()

SELECT AVG(gehalt), SUM(gehalt)/10,

MAX(gehalt), MIN(gehalt), SUM(gehalt)

FROM mitarbeiter ;

mitarbeiter

Ergebnis der Abfrage

NULL-Werte werden ignoriert


Funktionen COUNT() zählt die Datensätze

SELECT COUNT(*)

FROM mitarbeiter ;

ergibt 10

SELECT COUNT(abt_id)

FROM mitarbeiter;

ergibt 7

SELECT COUNT(DISTINCT abt_id)

FROM mitarbeiter;

ergibt 4


Gruppierungen durchführen mit GROUP BY

SELECT abt_id, AVG(gehalt)

FROM mitarbeiter

GROUP BY abt_id

gruppieren nach abt_id

mitarbeiter

ergibt


Für Gruppierungen gilt:

• Ist eine Gruppenfunktion in einer SELECT-Klausel angegeben, können nicht

gleichzeitig einzelne Attribute selektiert werden, es sei denn, sie werden in

der GROUP BY-Klausel angegeben.

• Mit der WHERE-Klausel können vorher Zeilen ausgeschlossen werden,

bevor die übrigen Zeilen gruppiert werden

• Es dürfen keine Spalten-Aliasnamen in der GROUP BY-Klausel verwendet

werden.

• Die in der GROUP BY-Klausel angegebene Spalte muss nicht in der

SELECT Liste enthalten sein.


Nach mehreren Spalten gruppieren

mitarbeiter

Die Gehälter aus der Tabelle mitarbeiter sollen für jede job_id addiert und gruppiert nach abt_id werden

SELECT abt_id, job_id, SUM(gehalt)

FROM mitarbeiter

GROUP BY abt_id, job_id;


Gruppenergebnisse filtern mit HAVING

• Die WHERE-Klausel darf nicht verwendet werden um Gruppen einzuschränken,

d.h. es darf keine Gruppenfunktionen in der WHERE Klausel benutzt werden.

• Um Gruppen einzuschränken, benutzt man die HAVING-Klausel.

• HAVING arbeitet wie folgt:

• Die Zeilen werden gruppiert

• Die Gruppenfunktion wird angewandt

• Gruppen, die der HAVING-Klausel entsprechen, werden angezeigt.


SELECT abt_id, MAX(gehalt)

FROM mitarbeiter

GROUP BY abt_id

HAVING MAX(gehalt) > 3000;

Gruppenergebnisse filtern

mitarbeiter

Das maximale Gehalt jeder Abteilung soll ermittelt werden, wenn es größer als 3000 ist


Die Syntax von Gruppenfunktionen lautet zusammenfasse nd

SELECT [attribut,] gruppen_funktion(attribut),…

FROM tabellenname

[WHERE bedingung]

[GROUP BY attribut]

[HAVING gruppen_bedingung]

[ORDER BY attribut]

• Benutzt man eine Gruppenfunktion zusammen mit einem Attribut in eine

SELECT-Klausel, muss das Attribut in der GROUP BY Klausel angegeben

werden.

• Bedingungen für Gruppenfunktionen müssen in der HAVING-Klausel

angegeben werden.


Unterabfragen

Hauptabfrage

Unterabfrage

Unterabfrage gibt Wert an Hauptabfrage zurück


Die Syntax von Unterabfragen

SELECT selections_liste

FROM tabellenname1

WHERE ausdruck operator

( SELECT selections_liste

FROM tabellenname2);

• Die Unterabfrage wird einmal vor der Hauptabfrage ausgeführt

• Die Hauptabfrage verwendet das Ergebnis der Unterabfrage

• Unterabfragen können z.B. in WHERE Klauseln, HAVING-Klauseln und FROM-Klauseln eingefügt werden.

• Der operator ist ein Vergleichsoperator.

• Die Tabellen aus der Haupt-und Unterabfrage können gleich oder verschieden sein.


Ein Beispiel für eine Unterabfrage

SELECT m_id, name, vorname

FROM mitarbeiter

WHERE gehalt >

(SELECT gehalt

FROM mitarbeiter

WHERE name = 'Breitinger');

mitarbeiter

Ergebnis der Hauptabfrage

Ergebnis der Unterabfrage


Für Unterabfragen gilt:

• Sie müssen in Klammern gesetzt werden

• Sie stehen auf der rechten Seite des Vergleichoperators

• Single Row-Operatoren müssen bei Single Row- Unterabfragen verwendet

werden

• Multiple Row-Operatoren müssen bei Multiple Row- Unterabfragen

verwendet werden.


Single Row-Unterabfragen

• geben nur eine Zeile zurück

• verwenden Single Row Vergleichsoperatoren, d.h.

=

>

>=

<

<=

<>


Ein weiteres Beispiel für eine Unterabfrage

SELECT name, vorname, gehalt

FROM mitarbeiter

WHERE job_id =

(SELECT job_id

FROM mitarbeiter

WHERE m_id = 60)

AND gehalt >

(SELECT gehalt

FROM mitarbeiter

WHERE m_id = 54);



Gruppenfunktionen in Unterabfragen


FROM mitarbeiter

WHERE gehalt =

(SELECT MAX(gehalt)

FROM mitarbeiter);



Gruppenfunktionen in Unterabfragen

Welche Mitarbeiter liegen mit ihrem Gehalt über dem Durchschnitt aller Gehälter?


FROM mitarbeiter

WHERE gehalt >

(SELECT AVG(gehalt)

FROM mitarbeiter);


Unterabfragen in HAVING-Klauseln

SELECT MIN(m.gehalt) "min gehalt", m.abt_id, a.abt_name



GROUP BY m.abt_id, a.abt_name

HAVING MIN(m.gehalt) >=

(SELECT MIN(m.gehalt)

FROM mitarbeiter

WHERE m.abt_id = 11);



Welche Mitarbeiter sind in der höchsten Gehaltstufe ?

SELECT m.name, m.vorname, g.gehalt_stufe

FROM mitarbeiter m, gehaltstufe g

WHERE m.gehalt

BETWEEN

(SELECT MAX(g.gehalt_von)

FROM gehaltstufe g)

AND (SELECT MAX(g.gehalt_bis)

FROM gehaltstufe g)

AND m.gehalt BETWEEN g.gehalt_von AND g.gehalt_bis;


Multiple Row-Unterabfragen

• geben mehrere Zeile zurück

• verwenden Multiple Row Vergleichsoperatoren

Operator Bedeutung

IN Gleich einem Element aus der Liste

ANY Vergleicht einen Wert mit jedem Wert der

Ergebnisliste.

ALL Wert wird mit allen von der Unterabfrage

zurückgegebenen Werten verglichen

EXISTS Fragt Existenz von Werten ab


Operator ANY in Multiple Row Unterabfragen

SELECT m_id, name, vorname, job_id, gehalt

FROM mitarbeiter

WHERE gehalt < ANY

( SELECT gehalt

FROM mitarbeiter

WHERE job_id = 10)

AND job_id <> 10;

mitarbeiter


Operator ALL in Multiple Row Unterabfragen

SELECT m_id, name, vorname, job_id, gehalt

FROM mitarbeiter

WHERE gehalt < ALL

( SELECT gehalt

FROM mitarbeiter

WHERE job_id = 10)

AND job_id <> 10;

mitarbeiter


Operator IN und NOT IN in Multiple Row Unterabfrage n

Welche Mitarbeiter sind keine Chefs?

SELECT m.name, m.vorname

FROM mitarbeiter m

WHERE m.m_id NOT IN

(SELECT v.chef_id

FROM mitarbeiter v)

mitarbeiter

Ergebnis


ACHTUNG bei NULL-WERTEN in einer Unterabfrage

Welche Mitarbeiter sind keine Chefs?


FROM mitarbeiter m

WHERE m.m_id NOT IN

(SELECT v.chef_id

FROM mitarbeiter v)

mitarbeiter

Es wird kein Ergebnis geliefert.


Beim Operator IN gibt es mit NULL-Werten keine Probleme:

Welche Mitarbeiter sind Chefs?


FROM mitarbeiter m

WHERE m.m_id IN

(SELECT v.chef_id

FROM mitarbeiter v)

mitarbeiter

Ergebnis


EXISTS and NOT EXISTS


FROM mitarbeiter m

WHERE EXISTS

(SELECT 'c'

FROM mitarbeiter v

WHERE v.chef_id = m.m_id)


Unterabfragen können auch in der FROM-Klausel verwe ndet werden

Welche Mitarbeiter liegen mit ihrem Gehalt über dem Durchschnitt in ihrer Abteilung?

SELECT a.abt_id,a.name, a.vorname, a.gehalt, b.durchschnittgehalt

FROM mitarbeiter a, (SELECT abt_id, AVG(gehalt) durchschnittgehalt

FROM mitarbeiter

GROUP BY abt_id) b

WHERE a.abt_id = b.abt_id

AND a.gehalt > b.durchschnittgehalt


Mengenoperatoren

Mengenoperatoren kombinieren die Ergebnisse von zwei oder mehreren

Abfragen

Es wird unterschieden zwischen

UNION Gibt alle eindeutigen Zeilen von einer der

beiden Abfragen zurück

INTERSECT Gibt alle eindeutigen Zeilen zurück, die von

beiden Abfragen geliefert werden

MINUS Gibt alle eindeutigen Zeilen zurück, die von der

ersten SELECT-Anweisung, nicht jedoch von

der zweiten SELECT-Anweisung geliefert werden.


• Die Anzahl und Datentypen der Spalten müssen in allen von der

Abfrage verwendeten SELECT-Anweisungen identisch sein.

• Die Spaltennamen müssen nicht identisch sein.

• Für die Syntax gilt

SELECT attribut,…

FROM tabellenname1

WHERE bedingung

UNION

SELECT attribut,…

FROM tabellenname2

WHERE bedingung


Beispiel für UNION

job_historie

mitarbeiter

Ausgabe des aktuellen Jobs und aller vorherigen Jobs

eines Mitarbeiters

SELECT m_id, job_id

FROM mitarbeiter

UNION

SELECT m_id, job_id

FROM job_historie;


Strategien zur Formulierung von SELECT-Anweisungen

• Wie soll die Ergebnisliste aussehen

• Welche Tabellen werden benötigt?

• Aliasnamen für Tabellen verwenden, sobald mehr als eine Tabelle verwendet

werden

• Welche Attribute sollen angezeigt werden?

• Welche virtuellen Spalten kommen vor?

• Welche (Gruppen-) Funktionen kommen vor?

Falls man das Ergebnis einer Gruppenfunktion anzeigen will, darf die

Ergebnistabelle entweder nur einen Wert haben oder es muss eine GROUP

BY Anweisung folgen, in der die übrigen Ergebnisspalten aufgenommen

werden.


• Werden mehrere Tabellen verwendet?

Damit kein kartesisches Produkt entsteht, muss eine JOIN- Bedingung

angegeben werden.

• Welche Selektionsbedingungen liegen vor?

• Bezieht sich die Bedingung auf eine einzelne Zeile, so muss die

WHERE-Klausel verwendet werden.

• Bezieht sich die Bedingung auf eine Gruppe von Zeilen, so muss mit

GROUP BY die HAVING Klausel verwendet werden.

• Unterabfragen

Liegen Vergleichswerte in einer anderen Tabelle?

• Besteht der Vergleichswert aus mehr als einer Zeile, wird eine

Unterabfrage mit ANY, ALL, IN oder EXISTS formuliert

• Eine Ordnung wird durch ORDER BY erreicht.


Views

• Eine View ist eine virtuelle Tabelle, d.h. sie ist nicht physisch

vorhanden.

• Eine View enthält keine Daten.

• Eine View basiert auf einer Tabelle oder einer anderen View.

• Man bezeichnet die Tabellen, auf denen eine View basiert als

Basistabellen.

• Eine View wird im Data Dictionary als SELECT-Anweisung

gespeichert.


Vorteile von Views

Der Einsatz von Views dient zur

• Beschränkung von Datenzugriffen

• Erleichterung von komplexen Abfragen

• Erzielung von Datenunabhängigkeit

• Darstellung von verschiedenen Ansichten derselben Daten

Analog zu Tabellen können nicht zwei Views mit dem identischen

Namen innerhalb einer Datenbank angelegt werden.


Das Erstellen von Views

Die Syntax für das Erstellen einer View lautet

CREATE VIEW viewname [(alias,…)]

AS

SELECT- Anweisung

[WITH CHECK OPTION]


Bemerkung zur Syntax

• Die SELECT-Anweisung darf kein ORDER BY enthalten

• Werden keine Attribute angegeben, so werden die in der SELECT-

Anweisung angegebenen Spalten für die View-Definition verwendet.

• CHECK OPTION bedeutet, dass beim Ändern in der VIEW

automatisch darauf geachtet wird, dass die View-Definition in der

SELECT-Anweisung durch die Änderung nicht verletzt wird.


Beispiel für das Anlegen einer View

Es soll eine View erstellt werden, die nur die Informationen der Mitarbeiter aus den

Abteilungen 5 und 11 enthält.

CREATE VIEW v_mitarbeiter_abt5_11

AS SELECT m_id, name, vorname , abt_id

FROM mitarbeiter

WHERE abt_id IN (5,11)

SELECT * FROM v_mitarbeiter_abt5_11

ergibt


Auch durch Änderungen in der View können die Inhalte d er Tabelle

verändert werden

UPDATE v_mitarbeiter_abt5_11

SET name = 'Schuttemann‚

WHERE m_id = 50;

SELECT * FROM mitarbeiterWHERE m_id = 50

ergibt


Klausel WITH CHECK OPTION verwenden

Zunächst wird eine View ohne die CHECK OPTION angelegt

CREATE VIEW v_mitarbeiter_abtsitz_muc

AS SELECT m.m_id, m.name, m.vorname , m.gehalt, m.abt_id

FROM mitarbeiter m

INNER JOIN abteilung a



ON a.abt_sitz = s.abt_sitz

WHERE s.stadt = 'München'

SELECT * FROM v_mitarbeiter_abtsitz_mucergibt


Ändert man nun einen Datensatz mit Hilfe der View

UPDATE v_mitarbeiter_abtsitz_muc

SET abt_id = 10

WHERE m_id = 50;

SELECT * FROM v_mitarbeiter_abtsitz_mucergibt

so ergibt

der Mitarbeiter m_id = 50 wird nicht mehr angezeigt


Klausel WITH CHECK OPTION verwenden

Nun legt man eine identische View mit Hilfe der WITH CHECK OPTION an

CREATE VIEW v_mitarbeiter_abtsitz_muc

AS SELECT m.m_id, m.name, m.vorname , m.gehalt, m.abt_id

FROM mitarbeiter m






WITH CHECK OPTION

SELECT * FROM v_mitarbeiter_abtsitz_mucergibt auch hier


Ändert man nun einen Datensatz mit Hilfe der View

UPDATE v_mitarbeiter_abtsitz_muc

SET abt_id = 10

WHERE m_id = 50;

so ergibt sich die Fehlermeldung


• Mit der Klausel WITH CHECK OPTION wird sichergestellt, dass auf

eine View angewandte DML-Operationen nur innerhalb der Zeilen

ausgeführt werden können, die die View selektieren kann.

• Falls versucht wird, DML-Operation für Zeilen auszuführen, die von

der View nicht ausgewählt wurden, wird eine Fehlermeldung erzeugt.


Mit der Klausel WITH CHECK OPTION können diskrete Werte für Views

als zugelassene Werte definiert werden

CREATE VIEW aufmwst

AS SELECT *

FROM auftrag

WHERE mwst IN (0,0.07,0.16)

WITH CHECK OPTION


Auch virtuelle Spalten sind zugelassen

CREATE VIEW v_leistnet (anr, lnr, ltext, ldatum, netto)

AS SELECT anr, lnr, ltext, ldatum, lanz*lsatz

FROM leistung

• Benutzt man Spaltenaliasnamen in der CREATE VIEW Anweisung, so müssen diese in der selben Reihenfolge angegeben werden, wie die Spalten in der Unterabfrage

• Ein INSERT, UPDATE oder DELETE auf dieser View, die einen Wert des Attributes netto verändert, wird mit einer Fehlermeldung zurückgewiesen, da netto ein berechnetes Feld ist.


Views mit Gruppierungen

CREATE VIEW v_abt_gehalt

(abteilungsname, min_gehalt, max_gehalt, avg_gehalt)

AS SELECT a.abt_name,MIN(m.gehalt), MAX(m.gehalt), AVG(m.gehalt)

FROM mitarbeiter m



GROUP BY abt_name

werden Gruppenfunktionen benutzt, so müssen Alias-Spaltennamen verwendet werden

SELECT * FROM v_abt_gehalt


Auch DISTINCT kann verwendet werden

CREATE VIEW v_leiter

AS SELECT DISTINCT v.name, v.vorname ,v.gehalt

FROM mitarbeiter m, mitarbeiter v

WHERE m.chef_id = v.m_id

SELECT * FROM v_leiter


DML-Operationen für eine View

Es können keine DML-Operationen auf Views durchgeführt werden,

wenn die View

• Gruppenfunktionen

• GROUP BY Klauseln

• DISTINCT

enthält.

Dann kann die View auch nicht mit der WITH CHECK OPTION angelegt

werden.

Auch darf kein UPDATE oder INSERT auf einem abgeleiteten Feldern

durchgeführt werden.


Es können keine Daten über eine View eingefügt werden, wenn es

NOT NULL Spalten in den Basistabellen gibt, die nicht für die View

ausgewählt wurden.

Beispiel: Die View v_mitarbeiter_abt5 sei definiert durch

CREATE VIEW v_mitarbeiter_abt5

AS SELECT m_id, name, gehalt

FROM mitarbeiter

WHERE abt_id = 5;

Das Einfügen eines Tupels über die View erfolgt durch

INSERT INTO v_mitarbeiter_abt5

VALUES (80,'Birker', 6023)


Falls das Attribut vorname in mitarbeiter ein NOT NULL Attribut ist,

ergibt sich beim INSERT die Fehlermeldung


View entfernen

• Views werden mit DROP VIEW viewname gelöscht

• Eine View wird ungültig, wenn die Basistabelle nicht mehr vorhanden

ist.

• Sobald die Basistabelle wieder vorhanden ist, wird auch die View

erneut gültig.


Indizes

• Ein Index ist ein Datenbankobjekt

• Ein Index kann die Geschwindigkeit von Abfragen erheblich

beschleunigen.

• Indizes können auch verwendet werden um für eine Spalte oder eine

Gruppe von Spalten Eindeutigkeit zu erzwingen.


Wie werden die Datensätze bei einer SELECT Anweisung bearbeitet?

Beispiel


FROM mitarbeiter

WHERE name = 'Birker '

Ohne die Verwendung eines Indizes werden alle Datensätze der Tabelle

sequentiell durchsucht und es wird überprüft ob der name im Datensatz

= 'Birker' ist.

Man spricht von einem Full Table Scan.

Bei sehr großen Datenmengen in den Tabellen führt dies zu langen

Antwortzeiten.


Um das Suchen der gewünschten Datensätze zu beschleunigen, setzt

man Indizes ein.

Ein Index ermöglicht den unmittelbaren und schnellen Zugriff auf Zeilen

einer Tabelle.

Indizes sind unabhängig von der indizierten Tabelle, d.h. sie können

jederzeit ohne Auswirkungen auf die zu Grunde liegende Tabelle erstellt

oder gelöscht werden.

Wird eine Tabelle gelöscht, so werden alle zugehörigen Indizes

ebenfalls gelöscht.


Wie arbeitet ein Index

Es gibt unterschiedliche Index-Strategien.

• In einem relationalen DBMS wird meistens ein B* -Baum Index

verwendet.

• B* - Bäume sind ausbalancierte Baumstrukturen, deren Blattknoten

alle den gleichen Abstand zum Wurzelknoten haben.

• Vorteil von B* - Bäume sind die extrem kurzen Suchpfade von der

Wurzel zum Blatt.

Ein weiterer wichtiger Index ist der Bitmap Index, der z.B. im

DataWarehouse Umfeld eingesetzt wird.


Welche Spalten müssen/sollten indiziert werden?

Indizes werden automatisch erstellt bei Verwendung eines

• PRIMARY KEY- Constraints

• UNIQUE- Constraints

Eine manuelle Indizierung sollte erfolgen bei

• Fremdschlüsselspalten (der Index wird nicht automatisch durch

Constraint erzeugt)

• Häufig verwendete Spalten


Indizes erstellen

Ein Index wird durch die folgende Syntax erstellt:

CREATE INDEX indexname

ON tabellenname(Attribut1[,Attribut2]…);

Beispiel: Ein Index wird für das Attribut name in der Tabelle mitarbeiter angelegt

CREATE INDEX mitarbeiter_name_idx

ON mitarbeiter (name);


Zusammengesetzte Indizes

Auch mehrere Spalten können zusammen einen Index bilden. Man

spricht dann von einem zusammengesetzten Index.

Beispiel: Ein Index wird für die Attribute name und gehalt der Tabelle

mitarbeiter angelegt

CREATE INDEX mitarbeiter_name_gehalt_idx

ON mitarbeiter (name, gehalt);


Bemerkung zur Indizierung

Der Einsatz von Indizes sollte gut überlegt sein.

• Mehrere Indizes auf einer Tabelle führen nicht unbedingt zu

schnelleren Abfragen.

• Bei jeder DML-Operation, die für eine Tabelle mit Indizes ausgeführt

wird, müssen die Indizes aktualisiert werden.

• Je mehr Indizes auf dieser Tabelle sind, umso höher ist der

Aktualisierungsaufwand.


Wann sollte ein Index erstellt werden?

In den folgenden Fällen sollte ein Index erstellt werden

• Eine Spalte enthält eine große Anzahl von Werten

• Eine Spalte enthält viele NULL-Werte

• Eine oder mehrere Spalten werden häufig gemeinsam in einer WHERE-

Klausel oder Join-Bedingung verwendet

• Die Tabelle ist sehr groß und die meisten Abfragen rufen wahrscheinlich

nicht mehr als 5 % der Zeilen ab.


Wann sollte kein Index erstellt werden?

In den folgenden Fällen sollte kein Index erstellt werden

• Die Tabelle ist klein

• Die Spalten werden selten als Bedingung in einer Abfrage verwendet

• Die meisten Abfragen rufen wahrscheinlich mehr als 5 % der Zeilen ab.

• Die Tabelle wird häufig aktualisiert


Transaktionen


Beispiel für Transaktionen

Konto

Überweisung von einem Konto auf ein anderes Konto.

100 EUR werden überwiesen


Um diese Überweisung durchzuführen sind zwei Schritte notwendig:

1. Reduzierung des Kontostandes mit der Kontonummer 4532168 um 100

UPDATE konto

SET kontostand = kontostand -100

WHERE konto_nr = 4532168

UPDATE konto

SET kontostand = kontostand +100


2. Erhöhung des Kontostandes mit der Kontonummer 1556432 um 100


Diese UPDATES müssen beide ausgeführt werden.

Kann durch einen Fehler nur das erste UPDATE ausgeführt werden,

so kommt es zu inkonsistenten Daten in der Datenbank.

Fehler können z.B. auftreten durch

• Programmierfehler in der Anwendung

• Systemabstürze

• Fehleingaben des Benutzers

Damit solche Inkonsistenzen nicht entstehen können gibt es bei DBMS

das Transaktionskonzept.


• Transaktionen sind Arbeitseinheiten, die als Gruppe in einer logischen

Reihenfolge, komplett oder gar nicht ausgeführt werden.

• Eine Transaktion hat einen definierten Anfang und ein definiertes

Ende.

• Erst wenn alle Arbeitseinheiten abgearbeitet sind, wird das Ergebnis in

der Datenbank gespeichert.

• Eine Transaktion überführt einen konsistenten Datenbestand in einen

neuen konsistenten Datenbestand.


Das Erstellen von Transaktionen

Die beiden entscheidenden Schlüsselwörter für eine Transaktion sind

COMMIT zum Abschließen einer laufenden Transaktion

ROLLBACK zum Zurücksetzen einer laufenden Transaktion.

Die DBMS, die Tranksaktionen unterstützen, gehen bzgl. der Syntax mit

Transaktionen unterschiedlich um.

Alle diese DBMS müssen dem System explizit mitteilen können, dass eine

Transaktion beginnt.


Das Erstellen von Transaktionen

Transaktionen werden erstellt durch

Anweisung zum Beginn der Transaktion

Anweisung1

Anweisung2…

COMMIT oder ROLLBACK der Transaktion

Im SQL-Server wird der Beginn einer Transaktion durch

BEGIN TRANSACTION eingeleitet,

Oracle benutzt für den Beginn die Anweisung SET TRANSACTION


Beispiel: SQL-Server Transaktion für die Banküberweisun g

BEGIN TRANSACTION

UPDATE konto



UPDATE konto



COMMIT TRANSACTION

durch das COMMIT wird die Transaktion abgeschlossen und die Werte in die Datenbank geschrieben.


Beispiel: Zurücksetzen einer laufenden Transaktion

Transaktionen können stets – bevor sie mit COMMIT bestätigt wurden -

mit ROLLBACK zurückgesetzt werden.

BEGIN TRANSACTION

UPDATE konto



UPDATE konto



ROLLBACK TRANSACTIONdurch das ROLLBACK wird die laufende Transaktion zurückgesetzt.


Sicherungspunkte

Durch ein ROLLBACK werden die gesamten Aktionen innerhalb der

Transaktion rückgängig gemacht.

Einige DBMS (wie Oracle und SQL Server) können für ihre Transaktionen

sog. Sicherungspunkte setzen. Eine Transaktion kann dann nur bis zu

dem bestimmten Sicherungspunkt rückgängig gemacht werden.


Beispiel Sicherungspunkte für den SQL Server

BEGIN TRANSACTION

Anweisung1: Buche Betrag von Konto K1 ab

SAVE TRANSACTION t1

Anweisung2: Füge diesen Betrag zu Konto K2 hinzu

Ein ROLLBACK TRANSACTION t1 macht Anweisung2 rückgängig,

nicht aber Anweisung1.

Ein ROLLBACK TRANSACTION macht sowohl Anweisung1 als auch

Anweisung2 rückgängig.


Beispiel Sicherungspunkte für Oracle

In Oracle lautet die SyntaxSET TRANSACTION;

Anweisung1: Buche Betrag von Konto K1 ab;

SAVEPOINT t1;

Anweisung2: Füge diesen Betrag zu Konto K2 hinzu;

Ein ROLLBACK TO SAVEPOINT t1 macht Anweisung2 rückgängig,

nicht aber Anweisung1.

Ein ROLLBACK macht sowohl Anweisung1 als auch Anweisung2

rückgängig.


Transaktionen und Sperren

Um die Konsistenz der Daten zu gewährleisten, gibt es das Transaktions-

konzept. Eine Transaktion ist dabei eine Folge von Datenbankzugriffen

mit folgenden Eigenschaften:

• Atomarität

Eine Tranksaktion ist unteilbar. Das DBMS stellt (mittels Logging-

Technik) sicher, dass eine Transaktion selbst im Fall eines

Systemabsturzes entweder komplett oder gar nicht durchgeführt

wird.


• Konsistenzerhaltung

Stellt sicher, dass eine Transaktion die Datenbank in einem gültigen

Zustand hinterlässt.

Die Kriterien, welcher Zustand als gültig zu betrachten sind, sind

Anwendungsspezifisch.

• Isoliertheit

Die Transaktion ist von Effekten anderer Transaktionen isoliert, d.h.

gleichzeitig ausgeführte Transaktionen haben keinen Einfluss

aufeinander.

Sie kann ohne Nebeneffekte zurückgesetzt werden.


• Dauerhaftigkeit

Nach Beendigung einer Transaktion wird die Dauerhaftigkeit aller

Änderungen garantiert (Persistenz).

Das Tranksaktionskonzept realisiert damit das ACID –Prinzip durch die

Eigenschaften Atomicity, Consistency, Isolation, Durability.


Sperren

Beim Arbeiten mit einer Datenbank kann es passieren, dass zwei

Benutzer denselben Datenbestand gleichzeitig bearbeiten wollen.

Dies kann zu inkonsistenten Daten führen.

Zur Vermeidung dieser Inkonsistenzen müssen die Teile der Datenbank,

die von solchen Änderungen betroffen sind, vorübergehend gegen den

Zugriff anderer Benutzer gesperrt werden.

Die DBMS haben die Möglichkeit, Sperren auf Tabellen oder Zeilen zu

setzten und wieder freizugeben.


Das Sperren bei Transaktionen

• Hat ein User eine Transaktion gestartet, aber noch nicht mit COMMIT

bestätigt, so kann er sich den veränderten Datenbestand schon

anzeigen lassen.

• Die Daten sind aber für jeden anderen Nutzer gesperrt. Erst nach der

Bestätigung durch ein COMMIT, kann auf dem neuen Datenbestand

durch andere Nutzer zugegriffen werden.

• Durch dieses Sperren wird Dateninkonsistenz vermieden.


Logging und Recovery

Die geforderte Atomarität einer Transaktion wird in DBMS durch Logging

realisiert, d.h.

Informationen werden in einem LOG-Protokoll aufgezeichnet.

Für eine Transaktion gibt es zwei Arten von Logs:

• UNDO Log

• REDO Log


• UNDO Log

Beim UNDO Log wird der Zustand der Daten vor ihrer Änderung

meist zeilenweise aufgezeichnet.

Dieses Log wird im Falle eines ROLLBACK herangezogen

• REDO Log

Das REDO-Log enthält den Zustand der Daten nach der

Datenänderung.

Durch dieses Log kann eine bereits abgeschlossene Transaktion,

deren Änderungen noch nicht in der Datenbank realisiert waren,

nachgefahren werden.

Das REDO-Log wird beim Hochfahren des Datenbankystems nach

einem Ausfall herangezogen.


Recovery

Beim Hochfahren des DBS wird automatisch ermittelt, ob das letzte

Abschalten des DBS ordnungsgemäß oder aufgrund eines Fehlers erfolgt

ist.

Lag ein Fehler vor, wird beim Hochfahren ein Recovery durchgeführt.

Bei einem Recovery werden die zum Zeitpunkt der Unterbrechung

abgeschlossenen Transaktionen erneut zum Abschluß gebracht (REDO)

und die offenen Trankaktionen werden zurückgesetzt.


Sicherheitskonzept – ZugriffsrechteFür ein DBMS ist ein gutes Benutzer und Berechtigungskonzept von

entscheidender Bedeutung.

In einem DBMS gibt es drei Konzepte

• Benutzerkonzept

• Rollenkonzept

• Privilegienkonzept.


Es müssen Nutzer für die Datenbanken angelegt werden,

Berechtigungen zur Ausführung bestimmter SQL-Anweisungen (d.h.

Privilegien) vergeben werden,

sowie Rollen, d.h. Gruppen von Privilegien angelegt werden, die

Benutzern zugeordnet werden können.

Der Datenbankadministrator (DBA) ist ein Benutzer auf höchster Ebene,

der alle Systemprivilegien und Objektprivilegien hat.

Die gesamte Verwaltung von Benutzern, Privilegien und Rollen kann

beim SQL-Server über den SQL Server Enterprise Manager bzw. dem

SQL Server Management Studio oder über die Kommandozeile erfolgen.


Das Benutzerkonzept

Das Benutzerkonzept wird anhand des DBMS Microsoft SQL Server

beschrieben.

Datenbankuser müssen auf zwei Ebenen bekannt sein:

1. SQL-Server

2. Auf den entsprechenden Datenbanken.


Das Benutzerkonzept für den SQL-Server

Eine Anmeldung auf dem SQL-Server erfolgt entweder über

• SQL Server Authentifizierung

oder über

• das System, d.h. die Domäne, zu der der SQL-Server gehört.

im Ordner Sicherheit findet man die Benutzernamen des Servers


Anlegen eines Users über die Kommandozeile

User können mittels DDL-Anweisungen oder mittels gespeicherter

Prozeduren angelegt werden. Zunächst werden die gespeicherten

Prozeduren vorgestellt:

1. SQL Server-Authentifizierung

exec sp_addlogin 'name', 'passwort' [, ' standard db ']

Beispiel: Anlegen des Users abcd mit Passwort a23&57v1a3

exec sp_addlogin 'abcd', ' a23&57v1a3 ';

Wird keine Standard-Datenbank angegeben, wird als default die

Datenbank MASTER gesetzt.


Löschen eines Users über die Kommandozeile

exec sp_droplogin 'name' ;

Bemerkung: Ist der zu löschende User noch einer Datenbank zugeordnet, so

kann er nicht gelöscht werden.

Bemerkung: Beide Prozeduren sollen mit der nächsten SQL-Server Version

nicht mehr gültig sein.

Stattdessen werden die User mit Hilfe der folgenden DDL-Anweisung angelegt:

CREATE LOGIN login_name

bzw. mit

DROP LOGIN login_name gelöscht.


Ändern des Passworts eines Users

exec sp_password ' pw_alt', 'pw_neu'

Der Administrator kann das Passwort für einen User jederzeit neu

setzen:

exec sp_password NULL, ' pw_neu', 'name_user'

Ändern der Standard-Datenbank für einen User

exec sp_defaultdb ' name_user', 'db'


Anlegen eines Users über die Kommandozeile

Auch hier gibt es die Möglichkeit über DDL-Anweisungen oder über

Stored Procedures User anzulegen. Hier die auszuführenden

gespeicherten Prozeduren:

2. Anmeldung über die Domäne

exec sp_grantlogin 'domäne\name'

Entziehung der Berechtigung für einen User auf den SQL-S erver

zuzugreifen

exec sp_revokelogin 'domäne\name'


Der Nutzer abcd kann nun auf den SQL-Server zugreifen

Als Standard Datenbank wurde test festgelegt


Er kann aber noch nicht auf seine Standarddatenbank z ugreifen.

Für die einzelnen Datenbanken müssen die User angelegt werden.


Verwaltung von Datenbankbenutzern

Das Hinzufügen eines Nutzers zu einer Datenbank erfolgt entweder mit

Hilfe von Transact SQL Anweisungen oder mit Hilfe von gespeicherten

Prozeduren.

Die folgenden gespeicherten Prozeduren erlauben bzw. entziehen

einem User den Zugriff auf eine bestimmte Datenbank.

use datenbankname;

exec sp_grantdbaccess 'user_name' ;

Mit

exec sp_revokedbaccess 'user_name' ;

wird ein Benutzer aus einer Datenbank entfernt.


Mit

CREATE USER user_name

FOR LOGIN lname

kann per Transact SQL ein User für eine Datenbank angemeldet werden.

Z.B.

USE test

CREATE USER abcd FOR LOGIN abcd

erlaubt dem User abcd das Arbeiten auf der Datenbank test.

Bemerkung: Das Bekanntmachen eines Nutzers in einer Datenbank weist

diesem Benutzer keine Rechte auf Datenbankobjekte zu.


Der User abcd wurde für die Datenbank test angemeldet

Nun kann der User abcd auf die Datenbank test zugreifen


Privilegien

Es wird unterschieden zwischen System- und Objektprivilegien.

Systemprivilegien wirken Systemweit (z.B. Anlegen und Löschen von

Usern, Datenbanken erstellen Rechteverwaltung etc.)

Objektprivilegien gelten für ein spezielles Datenbankobjekt (z.B.

UPDATE, INSERT….)

Diese Privilegien werden entweder einzeln oder durch die Verwendung

von Rollen verwaltet.


Der User abcd hat nach dem Anlegen keinerlei Privilegien auf der

Datenbank test.

Dem Nutzer abcd müssen Privilegien zugewiesen werden.


Rollen

• Eine Rolle ist ein Gruppe verwandter Privilegien.

• Benutzer werden einer oder mehrere Rollen zugewiesen und

erhalten die damit verbundenen Privilegien.

• Ein Benutzer kann verschiedenen Rollen angehören, und dieselbe

Rolle kann von mehreren Benutzern wahrgenommen werden.

• Im SQL Server gibt es fest vorgegebene Rollen, die durch eigene

Rollen ergänzt werden können.


Einige der vorgegebenen Rollen

Es wird unterschieden zwischen Server-Rollen und Datenbank-Rollen.

Server-Rollen enthalten Rechte, die zur Administration des gesamten

Servers benötigt werden, z.B. Anlegen von Benutzern und Datenbanken

oder Herunterfahren des Servers.

Alle Mitglieder der Rolle sysadmin haben DBA-Rechte und können damit

alle Operationen ausführen.

User können einer Server-Rolle durch die Prozedur

sp_addsrvrolemember 'rollen_name' ,'user_name' zugewiesen werden.


Mitglieder von Datenbank-Rollen enthalten Rechte auf Datenbankebene.

• Mitglieder der Rolle db_owner sind Besitzer einer Datenbank und

haben damit innerhalb dieser Datenbank alle Rechte.

• Mitglieder der Datenbankrolle db_datareader haben die Berechtigung

SELECT für jede Tabelle oder View ihrer Datenbank. Sie können

niemandem Berechtigungen erteilen oder entziehen.

• Alle Datenbank Benutzer sind automatisch immer der Rolle public

zugewiesen. Rechte die der Rolle public zugewiesen werden gelten

damit für alle Nutzer.


Rollen erstellen

Eigene Rollen können entweder über die Transact SQL Anweisung

CREATE ROLE rollen_name oder über die Prozedur sp_addrole erzeugt

werden.

Die so erzeugten Rollen beinhalten noch keine Rechte.

Beispiel:

exec sp_addrole 'rollen_name' bzw.

CREATE ROLE rollen_name

Dieser oder andere Datenbank-Rollen können durch die Prozedur

sp_addrolemember 'rollen_name' ,'user_name'

Benutzer zugewiesen werden.


sp_droprolemember entfernt Benutzer aus einer Rolle.

sp_droprole löscht die Rolle.

Alternativ kann ALTER ROLE zum Ändern des Rollennames bzw. DROP

ROLE zum Löschen der Rolle verwendet werden.


Zuordnung von Privilegien

Privilegien können Benutzern oder Rollen zugewiesen werden.

Zu diesem Zweck gibt es drei Kommandos:

GRANT, DENY, REVOKE

GRANT fügt vorhandene Rechten ein oder mehrere neue Rechte hinzu.

DENY verbietet Privilegien, ist dem GRANT übergeordnet

REVOKE entfernt mit GRANT oder DENY erteilte Berechtigungen.


Syntax für GRANT

GRANT Privileg,…|ALL [PRIVILEGES]

ON tabellenname,…

TO benutzername,….|rollenname

[WITH GRANT OPTION]

Falls WITH GRANT OPTION angegeben ist, kann der Rechteempfänger

diese Rechte an andere weitergeben.

Mit ALL werden alle Privilegien vergeben.

Anstelle eines Tabellennames kann auch ein Viewname angegeben

werden.

entfällt für Datenbank-privilegien


Syntax für REVOKE

REVOKE Privileg,…|ALL [PRIVLEGES]

ON tabellenname,…

FROM benutzername,….|rollenname

[CASCADE]

Falls CASCADE angegeben ist und der Rechteempfänger Rechte an

andere weitergegeben hat, werden auch diese entfernt.



Syntax für DENY

DENY Privileg,…|ALL [PRIVILEGES]

ON tabellenname,…

TO benutzername,….|rollenname

[CASCADE]



Objekt-Privilegien sind dabei z.B.

SELECT

INSERT

UPDATE

DELETE

Datenbank-Privilegien sind

CREATE TABLE | VIEW | FUNCTION

Das Privileg CREATE DATABASE kann nur in der Datenbank MASTER

vergeben werden.


Beispiel

GRANT SELECT

ON mitarbeiter

TO PUBLIC;

=> jeder Nutzer hat SELECT Rechte auf der Tabelle mitarbeiter;

exec sp_addrole 'rolle_test'

GRANT SELECT, UPDATE, INSERT

ON mitarbeiterTO rolle_test

exec sp_addrolemember 'rolle_test' , 'abcd'

DENY UPDATE ON mitarbeiter TO abcd


Das Anlegen und Verwalten der User, Privilegien und R ollen über

das SQL Server Management Studio

1. Zugriff auf den SQL-Server




2. Zugriff auf die Datenbanken

Über Eigenschaften des SQL-Users

in Sicherheit\Anmeldungen können

die Zugriffe auf die einzelnen

Datenbanken definiert werden.




3. Rollen definieren




4. Rechte den Rollen oder Usern zuweisen


Transact-SQL und PL/SQL

• Transact-SQL und PL/SQL sind Datenbankprogrammiersprachen

des SQL-Servers und Oracle.

• SQL ist ein Abfragesprache. Dies reicht für eine Datenbank-

programmierung nicht aus. Prozedurale Elemente fehlen.

• Transact-SQL bzw. PL/SQL sind prozedurale Spracherweiterungen

zu SQL.

• Sie werden unter anderem zur Erstellung von Triggern, Stored

Procedures und userdefinierten Funktionen benötigt.


Der SQL-Server: Transact-SQL

• Es werden zwei Arten von Variablen unterschieden:

• Benutzerdefinierte Variablen. Sie werden innerhalb eines

Transact-SQL Programms vom Benutzer erzeugt und gelten nur

innerhalb dieses Programms. Deklariert werden sie durch das

reservierte Wort DECLARE gefolgt vom Variablennamen und

dem Datentyp. Der Name der Variablen beginnt mit einem @

• Globale Variablen. Ihr Inhalt wird durch das System zugewiesen.

Geben Informationen z.B. über das System. Der Name beginnt

mit einem @@.

• Eine Wertzuweisung erfolgt durch eine SET-Anweisung oder durch eine

SELECT- Anweisung.


Kontrollstrukturen in Transact-SQL

• Wie in jeder höheren Programmiersprache gibt es in Transact-SQL

Kontrollstrukturen. Unterschieden werden die zwei Kategorien Auswahl-

und Schleifenstruktur.

• Repräsentiert werden die beiden Kategorien durch IF-ELSE und

WHILE.


Oracle: PL/SQL

• Der Deklarationsteil eines PL/SQL-Blocks beginnt mit dem reservierten

Wort declare.

• Variablen werden deklariert durch die Syntax

variablenname datentyp := initialwert;

(die Zuweisung eines Initialwertes ist hier optional)


Der Ausführungsteil in PL/SQL

• Nach dem Deklarationsblock folgt der Ausführungsteil. Dieser wird

durch begin und end eingeschlossen.

• PL/SQL kennt die Kontrollstrukturen

IF THEN ELSIF ENDIF

LOOP

EXIT

WHILE LOOP

FOR LOOP

GOTO


Gespeicherte Prozeduren –

Stored Procedures


Stored Procedures

• Gespeicherte Prozeduren sind wichtige Bestandteile im Datenbank-

betrieb. Sie können Datenbankverwaltung und –wartung

vereinfachen und beschleunigen.

• Sie können große Gruppierungen von SQL Anweisungen, Transact

SQL bzw. PL/SQL Anweisungen enthalten.

• Sie können mit großer Geschwindigkeit ausgeführt werden, weil viele

zur Abarbeitung erforderliche Schritte bereits bei der Erzeugung bzw.

beim ersten Aufruf durchgeführt werden.


• Die Anweisungen innerhalb einer Stored Procedure laufen alle

nacheinander auf dem Datenbank-Server ab. Es erfolgt erst nach

Beendigung aller Anweisungen ein Datenaustausch mit dem Client-

Rechner. Dies führt zu einer deutlichen Performance-Steigerung.

• Stored Procedures können Werte zurückgeben und können auf

Eingabeparametern beruhen.

• Sie können beim Start des Datenbank Servers automatisch

ausgeführt werden

• Sie werden explizit aufgerufen.


Das Erstellen von Stored Procedures

Syntax für den MS SQL-Server:

CREATE PROCEDURE Prozedurname {;Nummer}

[{@Parameter Datentyp} [VARYING] [=default][OUTPUT]]

[WITH {RECOMPILE | ENCRYPTION }]

[FOR REPLICATION]

AS SQL-Anweisungen


Ein Beispiel für eine einfache Stored Porcedure

CREATE PROCEDURE sp_mitarbeiterabt_11

AS SELECT name, vorname

FROM mitarbeiter

WHERE abt_id = 11

ORDER BY name DESC

EXEC sp_mitarbeiterabt_11

Um die Prozedur auszuführen wird der Befehl EXEC verwendet:


Den Quelltext der Stored Procedure erhält man durch Ausführen der

Stored Procedure

sp_helptext spname

Beispiel:

EXEC sp_helptext sp_mitarbeiterabt_11

ergibt


Die Abhängigkeiten der Stored Procedure erhält man durch Ausführen der

SP sp_depends spname

d.h.

EXCEC sp_depends sp_mitarbeiterabt_11

ergibt


Auch eine Gruppe von gespeicherten Prozeduren kann ers tellt werden:

CREATE PROCEDURE sp_g_mitarbeiter; 1

AS SELECT name, vorname

FROM mitarbeiter

WHERE abt_id = 11

GO

CREATE PROCEDURE sp_g_mitarbeiter; 2

AS SELECT name,vorname, abt_name

FROM mitarbeiter m






GO


Bemerkung zur Prozedurengruppe

• Bei einer Prozedurengruppe wird nur eine einzige Prozedur angelegt,

die mehrere Prozeduren als Gruppe beinhaltet.

Im Beispiel hat die Prozedur den Namen sp_g_mitarbeiter.

• Die Referenzierung der einzelnen Prozeduren erfolgt über

Prozedurengruppenname; Nummer

Im Beispiel:

EXEC sp_g_mitarbeiter;2

führt die zweite Prozedur innerhalb

der Gruppe aus


Parameter in Stored Procedures

Die folgende Zeile ist für SP mit Parametern zuständig:

[{@Parameter Datentyp} [VARYING] [=default][OUTPUT]]

• @Parameter legt den Namen des Parameters innerhalb der

Prozedur fest. Innerhalb einer Prozedur können bis zu 1024

Parameter verwendet werden.

• Hinter dem Parameternamen muss der Datentyp angegeben

werden.

• Mit = default kann für diesen Parameter ein default Wert angegeben

werden. Dieser wird gesetzt, wenn beim Ausführen der Prozedur kein

Wert hierfür angegeben wird


• OUTPUT bedeutet, dass der Parameter ein Rückgabeparameter

darstellen soll.

• VARYING bezieht sich auf die zurück gelieferte Datenmenge/Cursor.


Beispiel: Berechnung des Durchschnitts von 3 Zahlen

CREATE PROCEDURE sp_myDurchschnitt3

@param1 int,

@param2 int,

@param3 int,

@myAvg real OUTPUT

AS SELECT @myAvg = (@param1 + @param2 + @param3)/3.


DECLARE @durchschnitt real

Um den Wert von myAvg zu verwenden, muss zunächst eine Variable deklariert werden.

Danach kann die Prozedur mit den Werten ausgeführt werden:

EXEC sp_myDurchschnitt3 10, 14, 7, @durchschnitt OUTPUT

SELECT 'Der Durchschnitt ist: ',@durchschnitt

Jetzt kann das Ergebnis angezeigt werden:


Bei Verwendung von default-Werten sollte die Reihen folge beachtet

werden

CREATE PROCEDURE sp_myDurchschnitt_d

@myAvg real OUTPUT,

@param1 int = 0,

@param2 int = 0,

@param3 int = 0

AS SELECT @myAvg = (@param1 + @param2 + @param3)/3.

EXEC sp_myDurchschnitt_d @durchschnitt OUTPUT, 4

Beim Ausführen der Prozedur müssen nicht alle Paramter angegeben werden. Die Reihenfolge ist entscheidend. Z.B. Wert, default, Wert ist nicht möglich.


Bemerkung

• Die Werte müssen an die gespeicherte Prozedur in einer

festgelegten Reihenfolge übergeben werden.

• Eine Übergabe als benannte Parameter ist möglich, indem man die

Werte in der Form Parametername = Wert übergibt.

Damit kann eine beliebige Reihenfolge angegeben werden.

DECLARE @durchschnitt real

EXEC sp_myDurchschnitt3 @myAvg = @durchschnitt OUTPUT,

@param1 = 10,

@param3 = 5,

@param2 = 0


Rückgabe mit RETURN

• Statt der Verwendung des Schlüsselworte OUTPUT innerhalb der

gespeicherten Prozedur und bei der Ausführung der Prozedur kann

auch das Schlüsselwort RETURN verwendet werden.

CREATE PROCEDURE sp_addition

@p1 int,

@p2 int,

@retadd int

AS SELECT @retadd = @p1 + @p2

RETURN @retadd

DECLARE @myReturnWert int

EXEC @myReturnWert = sp_addition 6,9,0

SELECT 'Das Ergebnis ist', @myReturnWert


Die Option WITH RECOMPILE

• kann in der Anweisung CREATE PROCEDURE oder in der Anweisung

EXEC PROCEDURE stehen.

• Wird die Option in CREATE PROCEDURE eingesetzt, wird der

Ausführungsplan für die Prozedur nicht im Prozedurcache gespeichert.

Die gesamte Prozedur wird bei jedem Ablauf neu kompiliert.

• Wird WITH RECOMPILE in der Anweisung EXEC PROCEDURE

verwendet, so wird die gespeicherte Prozedur einmal bei der Ausführung

kompiliert und der neue Ausführungsplan anschließend für nachfolgende

Aufrufe von EXEC PROCEDURE im Prozedurcache gespeichert.


Die Option WITH ENCRYPTION

• Die Option WITH ENCRYPTION verschlüsselt die zur Erzeugung der

Prozedur eingesetzten SQL-Anweisungen.

Beispiel

CREATE PROCEDURE sp_kunde

WITH ENCRYPTION

AS SELECT kname, kvorname

FROM kunde

versucht man nun den Quelltext anzuzeigen, erhält man die Meldung


Das Löschen von gespeicherten Prozeduren

Wie alle Datenbankobjekt werden auch SP mit dem reservierten Wort

DROP gelöscht.

DROP PROCEDURE prozedurname;

Prozedur-Gruppen können nur komplett gelöscht werden. Um eine

einzelne Prozeduren innerhalb der Gruppe zu löschen, muss die

gesamte Gruppe gelöscht werden und mit dem veränderten Quelltext

erneut angelegt werden.


Kontrollstrukturen innerhalb einer Stored Procedure

Wie in anderen Programmiersprachen gibt es in Transact-SQL und

PL/SQL Kontrollstrukturen.

Syntax für Transact-SQL

IF boolean_ausdruck

{Anweisungsblock}

[ELSE

{Anweisungsblock}]

Die Schleife wird durch folgende Syntax abgebildet:

WHILE boolean_ausdruck

{Anweisungsblock}

[BREAK]


Ein komplexeres Beispiel für eine Stored Procedure

gruppeartikel

Mit Hilfe einer Stored Procedure sollen Werte in die Tabelle artikel eingefügt werden. Die Werte werden in der Form Artikelbezeichnung, Artikelpreis, Gruppenbezeichnung eingegeben.

Zurückgegeben werden soll die Id des eingefügten Datensatzes


CREATE PROCEDURE sp_insert_artikel

@aname varchar(50),

@apreis real,

@agruppenbez varchar(50)

AS

DECLARE @grupp_id int, @max_art_id int

SELECT @grupp_id = gruppen_id

FROM gruppe

WHERE @agruppenbez = gruppen_bez

SELECT @max_art_id = max(a_id)+1

FROM artikel


IF @grupp_id IS NOT NULL

INSERT artikel (a_id,a_bez,a_preis,gruppen_id)

VALUES (@max_art_id, @aname,@apreis,@grupp_id)

ELSE

BEGIN

DECLARE @max_grupp_id int

SELECT @max_grupp_id = max(gruppen_id)+1

FROM gruppe

INSERT gruppe

VALUES (@max_grupp_id, @agruppenbez)

INSERT artikel

VALUES (@max_art_id, @aname,@apreis,@max_grupp_id)

END

RETURN @max_art_id


DECLARE @hilf int

EXEC @hilf = sp_insert_artikel 'Keyboard-Super',35.6,'Tastatur'

Einfügen des Datensatzes 'Keyboard-Super', 35.6, 'Tastatur'

Einfügen des Datensatzes 'easyKlick', 10. ,'Maus'

DECLARE @hilf int

EXEC @hilf = sp_insert_artikel 'easyKlick',10.,'Maus'


Demonstration der Übersetzungszeit für Prozeduren anhand einer mehrfach aufgerufenen Stored Procedure

Beispiel


-- Diese Prozedur erzeugt einen neuen Messwert für eine Geräte-ID mit vorgegebenem Zeitstempel

CREATE PROCEDURE sp_messwert

@geraete_id int,

@ts datetime

AS

INSERT INTO messungen (mgi,wert,gemessen_am,geprueft_am,prid) VALUES(@geraete_id, RAND()*100, @ts , NULL , NULL)

Anhand der Prozedur sp_messwert wird der Zeitaufwand für einen Übersetzungsprozess simuliert


Mehrfacher Aufruf der Testprozedur mit Zeitmessung


Laufzeit mit Prozeduraufruf, aber ohne Recompile

Laufzeit mit Prozeduraufruf, aber mit Recompile

Laufzeit-Ergebnisse


Datenbank-Trigger


Trigger

• Trigger sind eine Art gespeicherte Prozeduren, die automatisch

gestartet werden, sobald ein vordefiniertes Ereignis und zwar eine

Datenmodifikation eintritt.

• Sie sind direkt einer Tabelle oder View zugeordnet.

• Trigger reagieren nur auf DML-Kommandos, d.h. auf

Insert, Update oder Delete.

• Trigger können keine Parameter übernehmen und lassen sich nicht

explizit aufrufen.

• Trigger werden standardmäßig nach einer Datenmodifikation

ausgelöst oder Ersetzen eine Datenmodifikation.


Einsatzmöglichkeiten von Triggern

• Trigger unterstützen die Datenintegrität. Sie können unbefugte oder

inkonsistente Datenänderungen verhindern.

• Trigger sichern die referentielle Integrität

• Trigger können die Durchsetzung komplexer Unternehmensregeln

gewährleisten.

• Trigger dienen zur Ausführung zusammenhängender Aktionen


Das Erstellen von Trigger

Syntax für den MS SQL-Server:

CREATE TRIGGER Triggername

ON Tabellenname|Viewname

[FOR | AFTER | INSTEAD OF] {INSERT | UPDATE | DELETE}

[WITH ENCRYPTION]

AS SQL-Anweisungen

Die folgenden Beispiele verwenden diese Syntax.


CREATE TRIGGER t_addupmitarbeiter

ON mitarbeiter

FOR INSERT,UPDATE

AS PRINT cast(@@rowcount AS varchar)+ ' Zeilen wurden geändert'

Ein einfaches Beispiel für einen Trigger

UPDATE mitarbeiter

SET gehalt = gehalt + 100

WHERE abt_id = 11

Ein Update oder Insert auf die Tabelle mitarbeiter löst den Trigger aus:

Ergebnis des Triggers


Die Tabellen inserted und deleted im MS SQL-Server

• Trigger im SQL-Server benutzen die zwei virtuellen Tabellen inserted

und deleted, die vom SQL-Server selber erstellt und verwaltet

werden.

• Diese beiden Tabellen haben die identische Struktur wie die Tabelle,

die dem Trigger zugrunde liegt, d.h. wie die Basistabelle.


AFTER bzw. FOR Trigger

• Die Tabelle deleted enthält alle Zeilen, die aus der Basistabelle

gelöscht wurden.

• Die Tabelle inserted enthält alle Zeilen, die der Basistabelle zugefügt

worden sind oder verändert wurden.

INSTEAD OF Trigger

• Die Tabelle deleted enthält alle Zeilen, die aus der Basistabelle

gelöscht werden sollen. Die Basistabelle ist noch unverändert.

• Die Tabelle inserted enthält alle Zeilen, die der Basistabelle zugefügt

bzw. verändert werden sollen .


Beispiel: Erstellung eines Triggers, der einen Datens atz in die Tabelle bestell einfügt, sobald der Bestand eines Artikels < 10 wird.

artikel

CREATE TRIGGER tr_bestell

ON artikel

AFTER UPDATE

AS

INSERT bestell

SELECT artnr FROM inserted WHERE bestand < 10;


CREATE TRIGGER t_autoinc_kunde

ON kunde

INSTEAD OF INSERT

AS

DECLARE @count int

SELECT @count = max(a_id)

FROM kunde;

IF @count IS NOT NULL

SELECT @count = @count+2;

ELSE SELECT @count = 1

INSERT INTO kunde SELECT @count, kname, kwert

FROM inserted

Beispiel: Erstellung eines eigenen Autoincrements


CREATE TRIGGER tr_loeschkontrolle

ON kunde

INSTEAD OF DELETE

AS

IF (@@rowcount < 2)

BEGIN

IF ((SELECT auftraege FROM deleted) = 0)

BEGIN

DELETE FROM kunde WHERE kdnr = (SELECT kdnr FROM deleted)

END

ELSE PRINT 'Fehler! Auftraege ist > 0'

END

ELSE

PRINT 'Dieser Befehl ist nur für einzelne Zeilen zulässig!'

Beispiel: Erstellen eines Lösch-Triggers, der Datensä tze nur dann aus der Tabelle kunde löscht, falls der Kunde keine offenen Aufträge mehr hat.


DELETE

FROM kunde

WHERE kdnr = 1

Auslösen des Triggers

DELETE

FROM kunde

WHERE kdnr = 2

löst die folgende Fehlermeldung aus

löscht den Datensatz


CREATE TRIGGER tr_loeschsumme

ON artikel

INSTEAD OF DELETE

AS

IF (@@rowcount <2)

BEGIN

UPDATE loeschsumme

SET summe = (

SELECT summe

FROM loeschsumme)+

(SELECT preis FROM deleted)

DELETE FROM artikel WHERE artnr = (SELECT artnr FROM deleted)

END

ELSE


Beispiel: Erstellen des Triggers, der die Preise der g elöschten Artikel in einer separaten Tabelle aufsummiert.


Trigger können eingesetzt werden um die referentielle In tegrität zu gewährleisten.

Beispiel:

kundeauftrag

Ein Trigger soll gewährleisten, dass nur Kunden gelöscht werden dürfen, die keine Aufträge in der Tabelle Auftrag haben.

nur dieser Kunde darf gelöscht werden


CREATE TRIGGER tr_integr ON kunde

INSTEAD OF DELETE

AS

IF (@@rowcount < 2)

BEGIN

IF( 0 = (SELECT COUNT(*)

FROM auftrag

WHERE kdnr = (SELECT kdnr FROM deleted))

)

DELETE FROM kunde WHERE kdnr = (SELECT kdnr FROM deleted)

ELSE

PRINT 'Für diesen Kunden sind noch Aufträge vorhanden'

END

ELSE



DELETE

FROM kunde

WHERE kdnr = 1

Ein Löschen des Kunden mit der kdnr = 1 ist nicht mö glich


Sicherung von Datenbanken


• Die richtige Datensicherung ist ein wichtiger Aspekt für den

Datenbankbetrieb.

• Welche Datensicherung für die entwickelte Datenbankanwendung am

besten bzw. am sinnvollsten ist, sollte bereits während der Projektphase

entschieden werden.

• Für die Durchführung der Datenbanksicherungen ist meist der

Datenbankadministrator zuständig.

Verantwortlich für die Initiierung ist der Leiter oder das IT-

Sicherheitsmanagement.

• Die Art der Datensicherung ist von dem jeweiligen Datenbanksystem

abhängig.

• Fehlende Datensicherung können nach Auffassung der Gerichte

juristische Konsequenzen nach sich ziehen.


Bei der Wahl der Datensicherung spielen die folgenden Kriterien eine Rolle

• Wie viele Daten beinhaltet die Datenbank

• Wie wichtig sind die Daten für das Unternehmen

• Wie wird die Verfügbarkeit der Daten für das Unternehmen eingestuft

• Wie oft werden die Daten verändert


Hat man sich für ein Sicherungsverfahren entschieden, müssen folgende

Punkte geklärt werden:

• Welche Medien benutzt man zur Sicherung

• Wie oft soll gesichert werden

• Wann soll gesichert werden, d.h. wann ist die geringste Last auf dem

Server

• Wie lange sollen Sicherungskopien aufgehoben werden

• Wie lange dauert die Wiederherstellung einer Sicherungskopie


Datensicherung einer Datenbank

Auszüge aus dem IT-Grundschutzhandbuch des Bundesamtes für

Sicherheit in der Informationstechnik

• Die Sicherung der Daten eines Datenbanksystems kann in der Regel

nicht mit den Datensicherungsprogrammen auf Betriebssystemebene

vollständig abgedeckt werden.

• Zur Sicherung des DBMS und der Daten müssen die jeweiligen

Dienstprogramme des DBMS eingesetzt werden.


Auszüge aus dem IT-Grundschutzhandbuch des Bundesamtes für Sicherheit in

der Informationstechnik

Möglichkeiten einer Datenbanksicherung

• Komplettsicherung der Datenbank in heruntergefahrenem Zustand.

Dies ist die einfachste und sicherste Methode, die allerdings aus

Gründen der Verfügbarkeitsanforderungen an die Datenbank oder

aufgrund des zu sichernden Datenvolumens oft nicht durchführbar

ist.

• Online-Sicherung der Datenbank.

Die Sicherung erfolgt während des laufenden Betriebs, d.h.

Datenbank muss nicht heruntergefahren werden.

Nachteile: Inkonsistenzen können nicht explizit ausgeschlossen

werden. Zusätzlich muss eine Offline-Komplettsicherung bestehen.


• Partielle Datenbanksicherung

Sollte immer dann verwendet werden, wenn das zu sichernde

Datenvolumen zu groß ist, um eine vollständige Sicherung

durchführen zu können.


Auszüge aus dem

IT-Grundschutzhandbuch des Bundesamtes für Sicherheit in der

Informationstechnik

Für die Datensicherung eines Datenbanksystems muss ein eigenes

Datensicherungskonzept erstellt werden.

Einflussfaktoren für ein solches Konzept sind:

• Verfügbarkeitanforderungen an die Datenbak

Wenn beispielsweise eine Datenbank werktags rund um die Uhr zur

Verfügung stehen muss, so kann eine Komplettsicherung nur am

Wochenende durchgeführt werden, da dies im allgemeinen ein

Herunterfahren der Datenbank erfordert.


• Datenvolumen

Das gesamte zu sichernde Datenvolumen muss mit den zur

Verfügung stehenden Sicherungskapazitäten verglichen werden.

Dabei muss festgestellt werden, ob die Sicherungskapazität für das

entsprechende Datenvolumen der Datenbank ausreichend

dimensioniert ist.

Falls dies nicht der Fall ist, muss ein Konzept zur Teilsicherung des

Datenvolumens erstellt werden.

• Maximal verkraftbarer Datenverlust

• Wiederanlaufzeit

Die maximal zulässige Zeitdauer des Wiederherstellens der

Datenbank nach einem Absturz muss festgelegt werden, um den

Verfügbarkeitsanforderungen zu genügen.


• Datensicherungsmöglichkeiten der Datenbank-Software

Im allgemeinen werden von einer Datenbank-Standardsoftware nicht

alle denkbaren Datensicherungsmöglichkeiten unterstützt, wie z.B.

eine partielle Datenbanksicherung. Dies ist vorab zu prüfen.


Anhand dieser Informationen kann ein Konzept für die Datensicherung der

Datenbank erstellt werden.

In diesem Sicherungskonzept wird u.a. festgelegt

• wer für die ordnungsgemäße Durchführung von Datensicherungen

zuständig ist,

• in welchen Zeitabständen eine Datenbanksicherung durchgeführt wird,

• in welcher Art und Weise die Datenbanksicherung zu erfolgen hat,

• zu welchem Zeitpunkt die Datenbanksicherung durchgeführt wird,

• die Spezifikation des zu sichernden Datenvolumens je Sicherung,

• wie die Erstellung von Datensicherungen zu dokumentieren ist,

• wo die Datensicherungsmedien aufbewahrt werden.


Ergänzende Kontrollfragen:

• Existiert eine Dokumentation, wie im Falle eines Absturzes der

Datenbank diese wiederherzustellen ist?

• Ist für die Institution ein aktuelles Datensicherungskonzept für den

Bereich Datenbanken dokumentiert?

• Wie werden Mitarbeiter über den sie betreffenden Teil des Konzeptes

unterrichtet?

• Wird die Einhaltung dieses Konzeptes kontrolliert?

• Wie werden Änderungen der Einflussfaktoren berücksichtigt?


Technische Verfahren zur Datensicherung

- Spiegelung, Duplexing und Striping -

• Zwei Festplatten bezeichnet man als gespiegelt, wenn sie exakt die

gleichen Daten enthalten. Eine Änderung der Daten auf der einen

Festplatte, führt auch zu einer Änderung der Daten auf der

gespiegelten Platte. Beide Platten werden von einem gemeinsamen

Controller angesteuert.

• Unter Duplexing versteht man eine Spiegelung, wobei aber jede

Festplatte einen eigenen Festplattencontroller besitzt.

• Beim Striping werden die Daten gleichmäßig auf verschiedene

Festplatten verteilt. Angesteuert werden die Festplatten von einem

gemeinsamen Controller.


RAID Konzept

• RAID = Redundant Array of Inexpensive Disk bzw.

Redundant Array of Independent Disk

• Das RAID Konzept ist in der Industrie weit verbreitet.

Es gibt mehrere Stufen.

• Festplatten werden in RAID-Array konfiguriert, um die auf den

Festplatten enthaltenen Daten zu schützen und eine hohe

Verfügbarkeit zu gewährleisten.


Die wichtigsten RAID Stufen

• RAID 0: Festplattenstriping ohne Paritätsinformationen

• RAID 1: Spiegelung oder Duplexing.

• RAID 5: Festplattenstriping mit Parität. Paritätsdaten werden auf alle

Festplatten verteilt.

• RAID 10: Festplattenspiegelung mit Striping, wobei ein

Festplattenarray auf einen anderen Satz von verteilten Festplatten

mit einem separaten Controller gespiegelt wird.


Die Datenbank im Netz

Verteilte Datenbanken, Replikation, ODBC


Verteilte Datenbanken

In einem Netzwerk befinden sich mehrere Datenbanken, die miteinander

verknüpft sind. Die einzelnen lokalen Datenbanken werden so zu einer

einheitlichen Sicht zusammengefasst, dass sie sich für den Anwender als

eine logische Gesamtdatenbank darstellt.

Das DBMS sorgt dafür, dass die Anfragen und Änderungen der Anwender

auf die richtigen Daten im Netz zugreifen.

Verteilte Datenbanken werden im wesentlichen aus zwei Gründen

eingesetzt:

• aus Performancegründen

• zur dezentralen Datenhaltung

(Replikation)


Verteiltes Datenbanken-Design

Das Design einer verteilten Datenbank erfolgt in den folgenden Schritten

• Zunächst wird das globale Datenmodell entworfen. Dieser Schritt gilt

für alle Datenbanken, d.h. er ist unabhängig von einer Verteilung

• Fragmentierung bzw. Partitionierung. Fragmente können ganze

Tabellen oder Teile von Tabellen sein.

• Zuordnung der Fragmente zu den vorgesehenen Datenbanken.


Fragmentierung bzw. Partitionierung

• Unter Fragmentierung versteht man die disjunkte Zerlegung einer

Tabelle in Teiltabellen.

• Diese Partitionen werden auf die einzelnen dezentralen Datenbanken

(Knoten) verteilt.

• Fragmentierung dient zur Performance- und Verfügbarkeits-

Optimierung.


Replikation

• Unter Replikation versteht man die redundante Speicherung von

Tabellen oder Tabellen-Fragmenten auf unterschiedlichen Knoten.

• Man unterscheidet im wesentlichen drei Arten von Replikation:

• synchrone Replikation

• asynchrone Replikation

• symmetrische Replikation


Gründe für die Anwendung von Replikation

• Zugriffsoptimierung :

Kopien der Daten werden am Ort der Verarbeitung gespeichert.

Dadurch werden Lese-Zugriffe auf entfernte Datenbankknoten

vermieden.

=> Verringerung von Netzwerkzugriffen und Lastverteilung für die

Datenbankserver.

• Verfügbarkeitsoptimierung:

Dadurch, dass die Tabellen mehrfach im Verbund vorhanden sind,

ergibt sich eine höherer Verfügbarkeit.


Synchrone Replikation

• Bei der synchronen Replikation werden Tabellen in einer zentralen

Datenbank gehalten. Auf mehreren Knoten werden Kopien dieser

Tabelle redundant abgelegt. Diese bezeichnet man als Replikate.

• Die Replikate zusammen mit der Basistabelle der zentralen Datenbank

enthalten zu jedem Zeitpunkt inhaltlich identische Daten.

• Schreiboperationen auf den Tabellen müssen in allen anderen

Replikaten nachvollzogen werden. Hierfür eignet sich der Einsatz von

Triggern.


Eigenschaften der Synchronen Replikation

• Vorteil:

Ein Anwender liest seine Daten stets von der Datenbank, die am

nächsten für ihn liegt. Dadurch wird das Netz, sowie die zentrale

Datenbank nicht belastet.

• Nachteil:

Eine Datenänderung muss in allen Replikaten nachgeführt werden.

Dies geschieht, sobald das Netz verfügbar ist.

Synchrone Replikationen eigenen sich, wenn bevorzugt Leseoperationen

auf den Tabellen durchgeführt werden.


Beispiel für den Einsatz von Synchronen Replikationen :

Literatur-Datenbank, die Artikel aus Fachzeitschriften enthält.

Auf diese Datenbank finden sehr viele Lesezugriffe durch eine Vielzahl

von Clients statt.

Schreibzugriffe finden in geringerer Zahl beim Zufügen neuer Artikel statt.


Asynchrone Replikation

• Bei der asynchronen Replikation gibt es nur einen Knoten, auf dem die

Tabellen geändert werden können (Masterknoten).

• Dieser Masterknoten enthält immer den aktuellen Datenbestand.

• Die anderen Knoten enthalten Kopien dieses Masters.

• Die Aktualisierung der Kopien erfolgt nur periodisch.

• Dies führt dazu, dass die Daten nicht konsistent sind.

• Die Kopien werden auch als Snapshot bezeichnet.


Bemerkung zur Asynchrone Replikation

• Asynchrone Replikation eignet sich nur, wenn es auf die Aktualität der

Datenbestände nicht ankommt.

• Auf Snapshots kann nur lesend zugegriffen werden.

• Die Datenaktualisierung durch den Master ist unkritisch.

• Snapshots eigenen sich besonders gut für Tabellen mit Stammdaten.

Stammdaten verändern sich mit einer niedrigen Frequenz.

Nach jedem Versionswechsel der Stammdaten sollte eine

Aktualisierung der Snapshots stattfinden.


Symmetrische Replikation

• Asynchrone und Synchrone Replikation sind die beiden Hauptarten

der Replikation.

• Es gibt eine Reihe weiterer Replikationsarten, die eine Mischform oder

Erweiterung der beiden Hauptarten darstellt.

• Die Symmetrische Replikation ist ein Überbegriff dieser Mischformen

und Erweiterungen.


Einige Arten der Symmetrischen Replikation

• Mehrere Master

Kopien der Tabellen werden auf mehrere Knoten verteilt:

Jede Kopie kann verändert werden.

Transaktionen werden – wenn möglich- auf allen Knoten zeitgleich

durchgeführt. Falls dies auf einem Knoten nicht möglich ist, werden die

Änderungen für diesen Knoten in eine Warteschlage gestellt und erst

übertragen, wenn der Knoten wieder zugänglich ist.

• Aktualisierbare Snapshots

Änderungen können auch an einem Snapshot durchgeführt werden.

Die Änderungen werden an den Masterknoten übertragen, der dann

eine Verteilung an die anderen Knoten vornimmt.


• Gemischtes Verfahren

Kombiniert Verfahren der mehreren Master und der aktualisierbaren

Snapshots.

Bemerkung:

Die symmetrische Replikation kann zu Konflikten führen, wenn an zwei

verschiedenen Orten auf den gleichen Daten etwas geändert wird.

Daher müssen Regeln definiert werden, die das Konfliktfreie

Durchführen von Transaktionen sicherstellen.


Durchführung von verteilten Transaktionen

Verteilte Transaktionen sind Transaktionen, für die gilt:

• Von der Datenmanipulation sind mehrere Datenbanken betroffen

• Die gesamte Transaktion wird durch ein COMMIT oder ROLLBACK

abgeschlossen.

Zur Wahrung der Konsistenzerhaltung und Atomarität bei verteilten

Transaktionen wird z.B. das sog. Two Phase Commit Protokoll benutzt.


Jeder der verteilten Datenbankserver hat eine spezielle

Softwarekomponente des DBMS, den sog. Transaktionsmanager.

Der Transaktionsmanager des Datenbankservers, von wo aus die

Transaktion gestartet wurde, kommuniziert mit den anderen an der

Transaktion beteiligten Transaktionsmanagern.


1. Phase (Prepare Phase)

In dieser Vorbereitungsphase schickt der Transaktionsmanager des

auszuführenden Servers eine Prepare Aufforderung an alle beteiligten

Server, um sie zur Schaffung der Voraussetzung für ein Commit oder

Rollback zu veranlassen.

Nachdem die Logs geschrieben wurden, schicken die Server eine

Antwort auf die Prepare Aufforderung an den Transaktionsmanager.

Das Two Phase Commit Protokoll


2. Phase (Commit Phase)

Wenn alle beteiligten Server eine positive Bestätigung geschickt haben,

schickt der Transaktionsmanager eine COMMIT Aufforderung an alle

Server. Ansonsten verschickt er eine ROLLBACK Aufforderung.

Erst dann schließen die Server die Transaktionen ab bzw. machen sie

rückgängig.


ODBC

• Bei ODBC handelt es sich um eine standardisierte Methode, die den

Zugriff auf Datenbanken erlaubt.

Hierbei muss nicht berücksichtigt werden, aus welchem Programm auf

welche Datenbank zugegriffen werden.

• ODBC stammt ursprünglich von Microsoft, ist aber inzwischen auch für

eine Reihe von anderen Betriebssystemen verfügbar.

• ODBC steht für Open DataBase Connectivity.

Anwendung

ODBC-Schnittstelle

Anwendung Anwendung

DBSDBSDBS


ODBC

• ODBC wird dem Bereich Middelware zugerechnet, d.h. einer

Softwareschicht, die zwischen Anwendung und Datenhaltungssystem

liegt.

• Mit Hilfe von ODBC greift ein Anwendungsprogramm über eine -auf

dem Client installierte- Standardschnittstelle auf das DBMS zu.

• Mit ODBC wird ein Standard API zur Verfügung gestellt, das eine

einheitliche Anwendungsprogrammierung für unterschiedliche DBMSe

ermöglicht.

• ODBC beruht auf einer Spezifikation, die von der SQL-ACCESS

Group unter Federführung von Microsoft ins Leben gerufen wurde.

• ODBC ist ein ISO-Standard.


Vor- und Nachteile von ODBC

• Vorteil:

• ODBC wird eingesetzt um einen Datenbankzugriff unabhängig

vom DBMS-Hersteller zu ermöglichen.

• Reduzierter Aufwand bei der Treiberentwicklung.

• Nachteil:

• Performance-Verlust durch zusätzliche Schicht.

• Es kann nur auf eine Untermenge der verfügbaren Datenbank-

funktionen zugegriffen werden.

Teilweise problematische Anwendungen durch die

unterschiedliche SQL-Syntax der verschiedenen DBMS-

Anbieter.


Alternativen zu ODBC

Datenbank bzw. Programmiersprachenspezifische DBMS Schnittstellen

z.B. hat PHP Schnittstellen zu über 20 verschiedenen Datenbanken.

Vorteile dieser Schnittstellen:

• Sie können den gesamten Funktionsumfang der Datenbank abdecken

• Höhere Kommunikations-Geschwindigkeit

Nachteile dieser Schnittstellen:

• Erhöhter Aufwand bei der Treiberentwicklung

• Erhöhter Aufwand bei der Wartung

• Erhebliche Flexibilitätseinbußen beim Datenbankwechsel


ODBC einrichten

für einen MS SQL-Server


Datenquelle hinzufügen


Es folgen weitere Treiberspezifische Formulare, die hier nicht aufgeführt werden.

Diese müssen beim SQL-Server vorerst nicht verändert werden.


Damit ist die ODBC Vebindung vom Client zur Datenbank hergestellt.


Praktischer Einsatz von Data-Warehouse-Systemen in Großprojekten


AnbieterVERS

SRZ

RSB-KettenZentralen

IO

TIC

BTX

AR

GF

SAP

Datei erzeugtdurch PM

Datei erzeugtdurch SBT

div. L isten ausM ythos

div. Dateien dertour. Anbieter

div. Dateien ausASP

M ARKO-DB

DER

amadeus

Spedition

Regional-büros

exciting

CP

SZ

SN

PD

PI

AT

AR

AF

AB

AA

EB

EA

VAKS

VK

TM

TK

CO

CM

M A

RW

PM 7

PM 6

PM 5

PM 3

PM 2

div. Dateien vonAN

PW

PR

PBSU

KC

PM 1

EI

KM

Bahn

Anbieter F ähren

STADI-DB

AI

AnbieterTouristik

Anbieter F lug, Car,Hotel

Ist-Situation in Großunternehmen

• Verschiedenste operative IT-Systeme und Datenbanken, die die Anforderungen des entsprechenden Bereichs abdecken.

• Historisch bedingter ‚Wildwuchs‘ .

=> Übergreifende Analysen sind kaum möglich


Probleme für die Analyse

• Die Daten sind in zu vielen Datenbanken verstreut und nicht vergleichbar.• Unterschiedliche Begrifflichkeiten in den Systemen erschweren die

Vergleichbarkeit und Zusammenführung der Daten.

• Extraktion liegt in der Hand der “Datenbänker” (SQL-Kenner).

Unternehmen ein starkes Interesse diese Daten für Analysen und Entscheidungsfindung nutzbar zu machen.

Wichtig: verdichtete Daten und Vergleiche über längere Zeiträume.

=> Ein Data Warehouse ist erforderlich.

Technischer Sicht• Datenbank, die meist große Datenmengen aus verschiedenen Datenquellen

integriert.

Betriebswirtschaftlicher Sicht

• Stellt Daten dem Anwender zu Analysezwecken zur Verfügung.


Eine der ersten Definitionen des Begriffes Data Warehouse wurde von W. H.

Inmon 1992 geprägt.

‚A data warehouse is a subject oriented, integrated, non -volatile, and time variant collection of data in support of management‘s decisions‘

Damit hat nach Inmon ein Data-Warehouse 4 Eigenschaften:

• Themenorientiert (subject-oriented)

• Integrierte Datenbasis (intergrated)

• Nicht flüchtige Datenbasis (non-volatile)

• Historische Daten (time-variant)

Erweiterung der Definition:

• dient der Analyse , d.h. ein adäquater Modellierungsansatz ist erforderlich.


Basis-Architektur eines Data-Warehouse-Systems

Daten-quelle(n)

Extraktion AnalyseLaden LadenArbeits-bereich

Basis-datenbank

Data Warehouse

Transformation

Monitor

Data-Warehouse-Manager

Metadaten-Manager

RepositoryDatenflussKontrollfluss

Bereich der DatenbeschaffungData-Warehouse-System


Analyse

Einer der wichtigsten Analyseansätze im Data Warehouse Umfeld ist

OLAP (Online Analytical Processing) (Codd 1993).

• Analyseansatz, der die dynamische, multidimensionale Analyse vonDaten bezeichnet.

• Analyse basiert auf Fragestellungen wie z.B. ‚In welchem Bezirk macht eine Produktgruppe den größten Umsatz?‘

Um den OLAP-Ansatz gerecht zu werden, muss ein multidimensionales Datenmodell zugrunde liegen.


Das Multidimensionale Datenmodell

Kategorie

Jahr

Fakt, Kennzahl(z.B. Umsatz)

Region

Pro

dukt

Zeitra

um

Bundesland

Stadt

Filiale

Quartal

Artikel

Dimension

Navigieren im Würfel durch

- drill down / roll-up

- drill across

- slice & dice


Umsetzung des multidimensionalen Datenmodells

Wie kann das MDD auf das Datenmodell des Datenbanksystems abgebildet werden?

• Umsetzung auf relationales Datenmodell (ROLAP)

• Umsetzung direkt auf multidimensionale Speicherstrukturen (MOLAP)


Beispiel ROLAP: Star-Schema

Produkt_IDZeit_IDGeo_IDVerkäufer IDVerkäufeUmsatz...

Produkt_IDZeit_IDGeo_IDVerkäufer IDVerkäufeUmsatz...

Verkauf

Zeit_IDTagMonatQuartalJahr

Zeit_IDTagMonatQuartalJahr

ZeitProdukt_IDProduktnameBeschreibungKategorie...

Produkt_IDProduktnameBeschreibungKategorie...

Produkt

Geo_IDStadtBundeslandLandKontinent...

Geo_IDStadtBundeslandLandKontinent...

Geographie

Verkäufer_IDVerkäufernamePosition...

Verkäufer_IDVerkäufernamePosition...

Verkäufer


Metadaten

Alle Informationen, die den Aufbau, die Wartung und die Administration des Data-Warehouse Systems vereinfachen und darüber hinaus den Anwender bei der Informationsgewinnung aus dem Data Warhouse unterstützen.

Technische Metadaten informieren über bzw. beinhalteten z.B.

• die Datengenerierung (build process): alle Datenquellen, Speichermedien, Datenstrukturen, Extraktions- und Transformationsprogramme, Generierungszeitpunkte.

• die Kontrolldaten: Zugriffsrechte, letzter Aktualisierungszeitpunkt.

Betriebswirtschaftliche Metadaten informieren z.B. über...

• das Datenmodell

• Kalkulationen und Aggregationen

• Dimensionen und Hierarchien

Die Metadaten des Data-Warehouse-Systems werden im Repository abgelegt.


Data-Warehouse-Manager ...

ist eine zentrale Komponente eines Data-Warehouse Systems. Ist verantwortlich für

• Initiierung, Steuerung und Überwachung der einzelnen Prozesse.

Man spricht von Ablaufsteuerung.

• Initiierung des Datenbeschaffungsprozesses.

• Nach Auslösen des Ladeprozesses

• Überwachung der weiteren Schritte (Bereinigung, Integration ...).

• Koordination der Reihenfolge der Verarbeitung.

• Im Fehlerfall: Protokollierung der Fehler, Wiederanlaufmechanismen.


Data-Warehouse-Systeme mit sehr großen Datenmengen

Problematik bei großen Datenmengen

• langen Bewirtschaftungsprozessen.

• lange Antwortzeiten bei den Analysen durch die Nutzer.

=> Optimierungen sowie Verteilungen der Datenbasis sind notwendig.

Lösungsansätze

• Verteilung der Daten auf Data Marts.

• Einsatz von Optimierungstechniken.


DataWarehouse

Laden

Analyse A Analyse B Analyse C Analyse D

Data Marts

Bildung von Data Marts

• spezielle Teildatenmenge eines Data Warehouses, z.B. für eine bestimmte Abteilung des Unternehmens.


Data Marts

Gründe für die Aufteilung in Data Marts:

• Besserer Übersichtlichkeit

• Leichtere Pflege

• Eigenständigkeit

• Datenschutz, da nur eine Teilsicht auf die Daten gewährt ist

• Organisatorische Aspekte (Unabhängigkeit von Abteilungen)

• Verringerung des Datenvolumens

• Performanzgewinn

• Verteilung der Last


Optimierungstechniken

1. Indexstrukturen

• Sehr große Datenvolumen und ein häufiges Lesen dieser Daten erfordern eine Technik, mit der die komplexen Anfragegebilde in vernünftiger Zeit und mit möglichst geringem Aufwand bewältigt werden können.

Dazu bedient man sich verschiedener Indizierungstechniken.

• Im Data Warehouse werden Daten über längere Zeit nicht verändert =>

• Die in Datenbanksystemen üblichen B-Bäume sind für den Einsatz im DataWarehouse nicht geeignet.

Im Data Warehouse werden Bitmap-Indizes eingesetzt.

Vorteil: Daten mit großen Wertebereichen werden speicherplatzeffizient indiziert und Bereichsanfragen schneller bearbeitet.


Monat Dez Nov Okt Sep Aug Jul Jun Mai Apr Mar Feb Jan

M B11 B10 B9 B8 B7 B6 B5 B4 B3 B2 B1 B0

5 0 0 0 0 0 0 1 0 0 0 0 0

3 0 0 0 0 0 0 0 0 1 0 0 0

11 1 0 0 0 0 0 0 0 0 0 0 0

3 0 0 0 0 0 0 0 0 1 0 0 0

Standard-Bitmap-Index

Bsp.: Dimension Kunde, Attribut Geburtsmonat M.

Ein Bitmap-Index soll auf Attribut ‚Geburtsmonat‘ erzeugt werden.

Bei Anfrage nach allen Tupeln mit Geburtsmonat April wird der Bitmap-Vekor B3 geladen und für jede 1 in dem Vektor wird das entsprechende Tupel selektiert.

Nachteil: für jede Ausprägung eines Attributs muss ein Bitmap-Vektor angelegt werden => hoher Speicherplatzbedarf.


Mehrkomponenten-Bitmap-Index

• Löst das Speicherproblem

• Die jeweiligen Werte von M werden kodiert.

Bsp.: Jeder Wert x von M zwischen 0 und 11 kann dargestellt werden durch x=4*y+z mit y in {0,1,2}, z in {0,1,2,3}.

x y Z

M B2,1 B1,1 B0,1 B3,0 B2,0 B1,0 B0,0

5 0 1 0 0 0 1 0

3 0 0 1 1 0 0 0

11 1 0 0 1 0 0 0

3 0 0 1 1 0 0 0

=> nur noch 7 Vektoren müssen gespeichert werden. Aber Lesezugriff für eine Punktabfrage immer 2 Leseoperationen.

Standard-Bitmaps und Mehrkomponenten-Bitmaps sind für Punktabfragen gut geeignet.


Bereichskodierte Bitmap-Indizes

• Bits aller Bitmap-Vekoren werden auf eins gesetzt, die größer oder gleich dem gegebenen Wert sind.

Bsp.:

Monat Dez Nov Okt Sep Aug Jul Jun Mai Apr Mar Feb Jan

M B11 B10 B9 B8 B7 B6 B5 B4 B3 B2 B1 B0

5 1 1 1 1 1 1 1 0 0 0 0 0

3 1 1 1 1 1 1 1 1 1 0 0 0

11 1 0 0 0 0 0 0 0 0 0 0 0

3 1 1 1 1 1 1 1 1 1 0 0 0

Anfrage alle Werte mit 2 <=M<=7 würde bei Standard-Bitmaps 6 Vektoren lesen, hier durch ((NOT B1) AND B7).

Für Punktabfrage allerdings auch zwei Abfragen: alle Kunden, die im April Geburtstag haben ((NOT B2) AND B3).


2. Partitionierung

Bildet Ergänzung zu den Indexverfahren. Stammt aus dem Bereich verteilter und paralleler Datenbanksysteme.

Horizontale Partitionierung

Master-TabelleVertikale Partitionierung


Einsatz im Data-Warehouse ist meist die Range-Partitionierung:

• häufig angefragte Datenbereiche sollen in getrennten Partitionen liegen.

Bsp.: Fakttabelle ‚Verkauf‘ soll partitioniert werden.

Partition mit Daten für alle Tage vor 2002, Partition mit Daten >= 2002

Oracle 8i und höher unterstützt diese Partitionierung:

CREATE TABLE Verkauf

(Produkt_ID NUMBER,

Zeit_ID DATE,

Geo_ID NUMBER,

Verkäufer_ID NUMBER)

PARTITION BY RANGE(Zeit_ID)

( PARTITION vor_2002

VALUES LESS THAN (TO_DATE (‚01-JAN-2002‘,‘DD-MON-

YYYY‘),

PARTITION nach_2002

VALUES LESS THAN (MAXVALUE))


Zusammenfassung

Ein Data Warehouse ist eine physische Datenbank

• die meist große Datenmengen aus verschiedensten Datenquellen integriert.• dient der Analyse (d.h. ein adäquater Modellierungsansatz ist erforderlich)

• - erreichbar durch ein multidimenisonales Datenmodell

• - OLAP.• Daten sind in der Regel nicht modifizierbar.

• Ein Data Warehouse ist in ein Data-Warehouse-System eingebunden.

• Meist werden die Daten auf ‚Data Marts‘ aufgeteilt.

• Spezielle Optimierungstechniken wie Bitmap-Indizes und Range Partitionierung sind erforderlich.


Literaturausschnitt

Bauer, A.; Günzel, H.: Data-Warehouse-Systeme. Dpunkt.verlag Heidelberg 2001

Inmon, W.H.: Building the Data Warehouse. Second Edition, John Wiley & Sons, New York, 1996.

Kimball, R.: The Data Warehouse Toolkit. John Wiley & Sons, New York 1996.

Web-Adressen:

German OLAP and Data Warehouse Forum:http://www.winf.ruhr-uni-bochum.de/olap/

http://www.datawarehousing.com/


Wird eine gespeicherte Prozedur das erste Mal ausgeführt, werden die

folgenden Schritte durchlaufen.

• Die Prozedur wird auf ihre Bestandteile hin analysiert

• Die Objekte, die vom Quelltext referenziert werden (Tabellen, Views

etc) werden auf ihr Vorhandensein hin untersucht. Man bezeichnet

dies auch als Auflösung.

• Name und Code zum Erstellen der Prozedur werden in

Systemtabellen gespeichert.

• Es wird eine optimierte Abfragestruktur erstellt und gespeichert.

• Die Abfragestruktur wird gelesen, übersetzt und als Ausführungsplan

im Prozedurcache gespeichert und ausgeführt.


Wird eine Stored Procedure erneut ausgeführt, müssen diese Schritte

nicht mehr durchgeführt werden. Es wird wird nur noch der

Ausführungsplan aus dem Prozedurcache gelesen und ausgeführt.

Documents

Datenbanken und Datenbankmanagementsystemehochschule-bochum.de/fileadmin/media/fb_e/labore/aidlab/brabender/... · SS 2007 Datenbanken Seite 3 Einige Literatur aus dem Bereich der