Datenbanken und Datenbankmanagementsysteme

Datenbanken und Datenbankmanagementsysteme
Prof. Dr. Katrin Brabender
Labor für Angewandte Informatik
und Datenbanken
Version: 12.03.2007
SS 2007 Datenbanken
Seite 1
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
SS 2007 Datenbanken
Seite 2
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.
SS 2007 Datenbanken
Seite 3
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.
SS 2007 Datenbanken
Seite 4
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.
SS 2007 Datenbanken
Seite 5
•
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.
SS 2007 Datenbanken
Seite 6
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.
SS 2007 Datenbanken
Seite 7
•
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.
SS 2007 Datenbanken
Seite 8
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.
SS 2007 Datenbanken
Seite 9
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.
SS 2007 Datenbanken
Seite 10
Bemerkung
•
Die Datenunabhängigkeit wird durch die sog. Drei-EbenenArchitektur (s. später) gewährleistet.
•
Zugriffskontrolle, d.h. kein unbefugter Zugriff und Datensicherheit,
d.h. kein ungewollter Datenverlust werden vom System
gewährleistet.
SS 2007 Datenbanken
Seite 11
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.
SS 2007 Datenbanken
Seite 12
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.
SS 2007 Datenbanken
Seite 13
•
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.
SS 2007 Datenbanken
Seite 14
•
Synchronisation
Konkurrierende Transaktionen mehrer Benutzer müssen koordiniert
werden.
•
Datensicherung
Das Wiederherstellen von Daten z.B. nach Systemfehlern muss
gewährleistet werden.
SS 2007 Datenbanken
Seite 15
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.
SS 2007 Datenbanken
Seite 16
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:
SS 2007 Datenbanken
Seite 17
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.
SS 2007 Datenbanken
Seite 18
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.
SS 2007 Datenbanken
Seite 19
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 gleichzeitig auf
die Datenbank zugreifen können.
Unterschieden wird hier zwischen Single- und Multi-User System.
• Anzahl der Rechner, auf die sich die Datenbank verteilt.
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
SS 2007 Datenbanken
Seite 20
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.
SS 2007 Datenbanken
Seite 21
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.
SS 2007 Datenbanken
Seite 22
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:
Kunde
Name
Meier
Beier
Meier
Becker
Kohnen
Vorname
Klaus
Andrea
Klaus
Inga
Silvia
PLZ
44799
60528
42111
88212
60389
Ort
Bochum
Frankfurt
Wuppertal
Ravensburg
Frankfurt
Straße
Laerheidestr. 26
Zeil 5
Güntherstr. 11
Lindenallee 2
Im Prüfling 2
Wertigkeit
B
A
C
A
B
SS 2007 Datenbanken
Seite 23
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
SS 2007 Datenbanken
Seite 24
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.
SS 2007 Datenbanken
Seite 25
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 NULLWert sein.
SS 2007 Datenbanken
Seite 26
Zur Wahrung der Übersichtlichkeit führt man meist künstliche Identifikationsschlüssel ein, z.B. laufende Nummern.
Damit sieht die Kundentabelle wie folgt aus
Kunde
KNr
100
101
102
103
104
Entität
Name
Meier
Beier
Meier
Becker
Kohnen
Vorname
Klaus
Andrea
Klaus
Inga
Silvia
Attribut
PLZ
44799
60528
42111
88212
60389
Ort
Bochum
Frankfurt
Wuppertal
Ravensburg
Frankfurt
Straße
Laerheidestr. 26
Zeil 5
Güntherstr. 11
Lindenallee 2
Im Prüfling 2
Wertigkeit
B
A
C
A
B
Tupel
Id-Schlüssel
SS 2007 Datenbanken
Seite 27
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.
SS 2007 Datenbanken
Seite 28
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.
SS 2007 Datenbanken
Seite 29
Kunde
KNr
100
101
102
103
104
Name
Meier
Beier
Meier
Becker
Kohnen
AufNr
30
40
50
60
Vorname
Klaus
Andrea
Klaus
Inga
Silvia
PLZ
44799
60528
42111
88212
60389
Ort
Bochum
Frankfurt
Wuppertal
Ravensburg
Frankfurt
Auftrag
KNr AufDat
LiefDat
102 26.03.2004
02.04.2004
104 01.03.2004
15.03.2004
102 29.03.2004
02.04.2004
103 03.01.2004
06.01.2004
1001
2036
3000
3057
4000
ArtBez
CPU
Grafikkarte
Speicher
Monitor
Festplatte
EkPreis
180
130
90
220
70
Wertigkeit
B
C
A
C
B
Position
ArtNr AufNr Menge
1001
40 3
2036
40 1
3000
30 1
3057
60 5
3057
50 10
4000
50 2
Artikel
ArtNr
Straße
Laerheidestr. 26
Zeil 5
Güntherstr. 11
Lindenallee 2
Im Prüfling 2
VKPreis
200
150
100
300
80
SS 2007 Datenbanken
Seite 30
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.
SS 2007 Datenbanken
Seite 31
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.
SS 2007 Datenbanken
Seite 32
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)
SS 2007 Datenbanken
Seite 33
•
DCL Data Control Language mit den Befehlen
GRANT (Vergabe von Zugriffsrechten)
REVOKE (Zurücknahme von Zugriffsrechten)
COMMIT (Abschluss von Transaktionen)
ROLLBACK (Abbruch von Tranksaktionen)
SS 2007 Datenbanken
Seite 34
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.
SS 2007 Datenbanken
Seite 35
•
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.
SS 2007 Datenbanken
Seite 36
•
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.
SS 2007 Datenbanken
Seite 37
Der Konzeptuelle Entwurf- Das Entity-Relationship-Modell (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.
SS 2007 Datenbanken
Seite 38
•
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
SS 2007 Datenbanken
Seite 39
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:
Student
•
Attribute
werden durch Ellipsen repräsentiert:
Name
Eindeutige Attribute werden unterstrichen.
SS 2007 Datenbanken
Seite 40
•
Relationship
werden durch Rauten repräsentiert:
Student
besucht
Vorlesung
SS 2007 Datenbanken
Seite 41
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.
SS 2007 Datenbanken
Seite 42
Folgende Beziehungstypen (Kardinalitäten) sind möglich
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
1
hat
N
Studiengang
Ein Fachbereich hat mehrere Studiengänge,
1 Studiengang gehört zu einem Fachbereich
SS 2007 Datenbanken
Seite 43
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
N
hat
M
Studiengang
Ein Student kann für mehrere Studiengänge (M) eingeschrieben sein,
ein Studiengang hat mehrere Studenten (N).
SS 2007 Datenbanken
Seite 44
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
1
leitet
1
Fachbereich
Ein Mitarbeiter (Dekan) leitet einen Fachbereich,
ein Fachbereich wird von einem Mitarbeiter geleitet.
SS 2007 Datenbanken
Seite 45
Kann- oder Muss-Beziehung
Es ist außerdem wichtig zu überprüfen, ob eine Beziehung optional (kannBeziehung) oder obligatorisch (muss-Beziehung) ist.
Eine kann-Beziehung wird symbolisch durch ein
ausgedrückt, eine muss-
Beziehung durch ein
Beispiel
Mitarbeiter
1
leitet
1
Fachbereich
Ein Mitarbeiter kann einen Fachbereich leiten (dies ist der Mitarbeiter Dekan),
ein Fachbereich muss von einem Mitarbeiter geleitet werden.
SS 2007 Datenbanken
Seite 46
Fachbereich
1
hat
N
Mitarbeiter
Ein Fachbereich muss N (d.h. mindestens 1) Mitarbeiter haben,
ein Mitarbeiter gehört zu genau einem Fachbereich.
SS 2007 Datenbanken
Seite 47
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
1
hat
N
Mitarbeiter
Sind drei oder mehr Entities beteiligt, so spricht man von einer Tenären
bzw. n-ären Beziehung.
SS 2007 Datenbanken
Seite 48
Beispiel für eine Tenäre Beziehung
Projekt
N
hat
M
Mitarbeiter
P
Qualifikation
SS 2007 Datenbanken
Seite 49
Ist nur eine einzige Entity an einer Beziehung beteiligt, so spricht man
von einer rekursiv binären Beziehung.
Mitarbeiter
1
verheiratet
1
Mitarbeiter
Ein Mitarbeiter kann mit einem Mitarbeiter verheiratet sein.
Eine andere Darstellungsform für eine rekursiv binäre Beziehung ist
1
Mitarbeiter
verheiratet
1
SS 2007 Datenbanken
Seite 50
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.
Supertyp
IS-A
Subtyp
SS 2007 Datenbanken
Seite 51
Beispiel für eine Spezialisierung
Laborassistent
Sekretär
Mitarbeiter
IS-A
d
Professor
Techniker
Das d in der Beziehung gibt an, dass die Mengen disjunkt sind.
SS 2007 Datenbanken
Seite 52
Aggregation
GUI
Datenbankanwendung
PART-OF
DBS
Dokumentation
Von Aggregation spricht man, wenn ein Entity aus mehreren
eigenständigen Entities zusammengesetzt ist.
SS 2007 Datenbanken
Seite 53
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.
SS 2007 Datenbanken
Seite 54
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.
N
1
Kunde
Auftrag
erteilt
N
N
redundante Beziehung
bestellt
enthält
M
Artikel
M
SS 2007 Datenbanken
Seite 55
N
1
Kunde
Auftrag
erteilt
N
bevorzugt
N
nicht-redundante
Beziehung
enthält
M
Artikel
M
Die Beziehung „bevorzugt“ drückt nun etwas anderes aus. Diese Beziehung
wäre beispielsweise wichtig um Kundenverhalten zu analysieren.
SS 2007 Datenbanken
Seite 56
Ein ER-Modell (hier sind nicht die Attribute eingezeichnet) für unser
Hochschulbeispiel könnte dann wie folgt aussehen:
Studiengang
1
N
hat
M
Fachbereich
1
hat
hat
N
Student
1
leitet
N
1
ist
1
1
1
Mitarbeiter
verheiratet
1
IS-A
Laborassistent
Sekretär
Professor
Techniker
SS 2007 Datenbanken
Seite 57
Die Beziehung Student- Studiengang sieht dann mit den Attributen wie folgt aus
StudGangBez
Studiengang
M
StudGangNr
hat
N
Geburtstag
Student
Name
MatrNr
SS 2007 Datenbanken
Seite 58
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.
SS 2007 Datenbanken
Seite 59
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.)
SS 2007 Datenbanken
Seite 60
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)
SS 2007 Datenbanken
Seite 61
Die 1:1- Beziehung
Eine Person hat also 1 Haustier, ein Haustier gehört einer Person.
a) Beziehungstyp
Person
1
hat
1
Haustier
Eine Person hat genau 1 Haustier,
ein Haustier gehört zu genau einer Person.
SS 2007 Datenbanken
Seite 62
Ü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)
Haustier (TNr, Art, Rasse, Alter)
SS 2007 Datenbanken
Seite 63
Person
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
Art
Vogel
Hund
Hund
Fisch
Katze
1
hat
1
Haustier
zu jedem Tupel in Person gibt es
genau ein Tupel in Haustier
Haustier
TNr
1
2
3
4
5
Person
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
SS 2007 Datenbanken
Seite 64
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.
SS 2007 Datenbanken
Seite 65
Haustierbesitzer
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
SS 2007 Datenbanken
Seite 66
b) Beziehungstyp
Person
1
hat
1
Haustier
Eine Person kann höchstens 1 (kein oder genau ein) Haustier haben,
ein Haustier gehört zu genau einer Person.
SS 2007 Datenbanken
Seite 67
Übertragen auf Tabellen:
•
Es entstehen 2 Tabellen (Person und Haustier).
•
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)
SS 2007 Datenbanken
Seite 68
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
5
Laufer
Thomas
Es gibt Tupel in Person,
die keinen Bezug zu
einem Tupel in Haustier
haben
Person
Haustier
TNr
1
2
3
Art
Vogel
Hund
Hund
Rasse
Papagei
Boxer
Dackel
Alter
20
1
10
PNr
3
1
4
1
hat
1
Haustier
jedes Tupel in
Haustier hat genau
ein zugehöriges
Tupel in Person
SS 2007 Datenbanken
Seite 69
•
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.
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (PNr, Art, Rasse, Alter)
SS 2007 Datenbanken
Seite 70
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
5
Laufer
Thomas
Es gibt Tupel in Person,
die keinen Bezug zu
einem Tupel in Haustier
haben
Person
Haustier
Art
Vogel
Hund
Hund
Rasse
Papagei
Boxer
Dackel
Alter
20
1
10
PNr
3
1
4
1
hat
1
Haustier
jedes Tupel in
Haustier hat genau
ein zugehöriges
Tupel in Person
SS 2007 Datenbanken
Seite 71
c) Beziehungstyp
Person
1
hat
1
Haustier
Eine Person kann höchstens 1 (kein oder genau ein) Haustier haben,
ein Haustier gehört zu höchstens einer Person.
SS 2007 Datenbanken
Seite 72
Ü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
Müller
Ute
3
Becker
Inga
4
4
Kohnen
Bernd
1
5
Laufer
Thomas
2
TNr
1
2
3
4
5
Art
Vogel
Hund
Hund
Fisch
Katze
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch
0,5
Siam
7
PNr
4
1
3
Hier sind Nullwerte in den Fremdschlüsseln, daher Transformation erforderlich.
SS 2007 Datenbanken
Seite 73
Übertragen der Beziehung auf Tabellen
( ohne Nullwerte im Fremdschlüssel):
Person
1
hat
1
Haustier
•
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.
SS 2007 Datenbanken
Seite 74
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter)
Tierhalter (TNr,PNr)
SS 2007 Datenbanken
Seite 75
Person
Tierhalter
PNr
Name
Vorname
1
Meier
Kai
PNr
TNr
2
Müller
Ute
1
2
3
Becker
Inga
3
4
4
Kohnen
Bernd
4
1
5
Laufer
Thomas
1
Person
1
hat
1
1
Haustier
TNr
1
2
3
4
5
Art
Vogel
Hund
Hund
Fisch
Katze
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch
0,5
Siam
7
Haustier
1
1
Tierhalter
SS 2007 Datenbanken
Seite 76
Die 1:N- Beziehung
Eine Person hat also N Haustiere, ein Haustier gehört einer Person.
a) Beziehungstyp
Person
1
hat
N
Haustier
Eine Person muss N (d.h. mindestens 1) Haustier haben,
ein Haustier gehört zu genau einer Person.
SS 2007 Datenbanken
Seite 77
Übertragen auf Tabellen:
•
Es entstehen 2 Tabellen (Person und Haustier).
•
Der Id-Schlüssel der Tabelle Person wird zum Fremdschlüssel der
Tabelle Haustier.
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter, PNr)
SS 2007 Datenbanken
Seite 78
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.
SS 2007 Datenbanken
Seite 79
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
5
Laufer
Thomas
Person
1
hat
N
Haustier
Haustier
TNr
1
2
3
4
5
6
7
Art
Vogel
Hund
Hund
Fisch
Katze
Pferd
Reptil
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
Araber
3
Schlange
30
PNr
3
1
4
2
5
1
4
SS 2007 Datenbanken
Seite 80
b) Beziehungstyp
Person
1
hat
N
Haustier
Eine Person kann N (d.h. 0, 1 oder mehr) Haustier haben,
ein Haustier gehört zu genau einer Person.
SS 2007 Datenbanken
Seite 81
Übertragen auf Tabellen:
•
Es entstehen 2 Tabellen (Person und Haustier).
•
Der Id-Schlüssel der Tabelle Person wird zum Fremdschlüssel der
Tabelle Haustier.
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter, PNr)
SS 2007 Datenbanken
Seite 82
Die Tabellen haben folgende Eigenschaften:
•
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.
SS 2007 Datenbanken
Seite 83
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
5
Laufer
Thomas
Person
1
hat
N
Haustier
Tupel mit PNr 4 besitzt in Haustier
keinen Datensatz
Haustier
TNr
1
2
3
4
5
6
7
Art
Vogel
Hund
Hund
Fisch
Katze
Pferd
Reptil
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
Araber
3
Schlange
30
PNr
3
1
1
2
5
1
2
SS 2007 Datenbanken
Seite 84
c) Beziehungstyp
Person
1
hat
N
Haustier
Eine Person muss N (d.h. mindestens 1) Haustier haben,
ein Haustier hat höchstens einen (d.h. keinen oder genau einen) Besitzer.
SS 2007 Datenbanken
Seite 85
Übertragen auf Tabellen:
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:
SS 2007 Datenbanken
Seite 86
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
Haustier
TNr
1
2
3
4
5
6
7
Art
Vogel
Hund
Hund
Fisch
Katze
Pferd
Reptil
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
Araber
3
Schlange
30
PNr
3
1
Das Attribut PNr, d.h.
der Fremdschlüssel hat
Null-Werte.
2
1
4
SS 2007 Datenbanken
Seite 87
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.
SS 2007 Datenbanken
Seite 88
Übertragen der Beziehung auf Tabellen
( ohne Nullwerte im Fremdschlüssel):
Person
1
hat
N
Haustier
•
Es entstehen 3 Tabellen (Person, Haustier, Tierhalter ).
•
Der Id-Schlüssel der Tabelle Person wird zum Fremdschlüssel der
Tabelle Tierhalter.
•
Der Id-Schlüssel der Tabelle Haustier ist gleichzeitig Id-Schlüssel der
Tabelle Tierhalter.
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter)
Tierhalter (TNr,PNr)
SS 2007 Datenbanken
Seite 89
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.
SS 2007 Datenbanken
Seite 90
Tierhalter
Person
PNr
Name
Vorname
1
Meier
2
PNr
TNr
Kai
1
2
Müller
Ute
1
5
3
Becker
Inga
2
4
4
Kohnen
Bernd
3
1
4
7
Haustier
TNr
1
2
3
4
5
6
7
Art
Vogel
Hund
Hund
Fisch
Katze
Pferd
Reptil
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
Araber
3
Schlange 30
1
Person
N
hat
1
Haustier
1
1
N
Tierhalter
SS 2007 Datenbanken
Seite 91
d) Beziehungstyp
Person
1
hat
N
Haustier
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.
SS 2007 Datenbanken
Seite 92
Übertragen auf Tabellen:
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:
SS 2007 Datenbanken
Seite 93
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
Zu diesem Tupel gibt es
kein Haustier
Haustier
TNr
1
2
3
4
5
6
7
Art
Vogel
Hund
Hund
Fisch
Katze
Pferd
Reptil
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
Araber
3
Schlange
30
PNr
2
1
2
1
Das Attribut PNr, d.h.
der Fremdschlüssel hat
Null-Werte.
Diese Tupel gehören zu
keiner Person.
4
SS 2007 Datenbanken
Seite 94
Übertragen der Beziehung auf Tabellen
( ohne Nullwerte im Fremdschlüssel):
Person
1
hat
N
Haustier
•
Es entstehen 3 Tabellen (Person, Haustier, Tierhalter ).
•
Der Id-Schlüssel der Tabelle Person wird zum Fremdschlüssel der
Tabelle Tierhalter.
•
Der Id-Schlüssel der Tabelle Haustier ist gleichzeitig Id-Schlüssel der
Tabelle Tierhalter.
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter)
Tierhalter (TNr,PNr)
SS 2007 Datenbanken
Seite 95
Die Tabellen haben folgende Eigenschaften:
•
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.
SS 2007 Datenbanken
Seite 96
Tierhalter
Person
PNr
TNr
Kai
1
2
Müller
Ute
1
5
3
Becker
Inga
2
1
4
Kohnen
Bernd
2
4
4
7
PNr
Name
Vorname
1
Meier
2
Haustier
TNr
1
2
3
4
5
6
7
Art
Vogel
Hund
Hund
Fisch
Katze
Pferd
Reptil
Rasse Alter
Papagei 20
Boxer
1
Dackel
10
Goldfisch 0,5
Siam
7
Araber
3
Schlange 30
1
Person
N
hat
1
Haustier
1
1
N
Tierhalter
SS 2007 Datenbanken
Seite 97
Die N:M- Beziehung
Eine Person hat M Haustiere, ein Haustier gehört N Personen.
a) Beziehungstyp
Person
N
hat
M
Haustier
Eine Person hat mehrere (mindestens ein) Haustier,
ein Haustier gehört zu mehreren (mindestens einer) Person.
SS 2007 Datenbanken
Seite 98
Übertragen auf Tabellen:
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.
SS 2007 Datenbanken
Seite 99
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
Die Beziehung ist korrekt
dargestellt, aber:
Doppelte Datensätze gefährden die
Datenkonsistenz.
Haustier
TNr
1
2
3
4
1
4
4
Art
Vogel
Hund
Hund
Fisch
Vogel
Fisch
Fisch
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Papagei
20
Goldfisch 0,5
Goldfisch 0,5
PNr
1
3
2
1
2
2
3
SS 2007 Datenbanken
Seite 100
Daher Transformation der Beziehung:
Person
N
hat
M
Haustier
•
Es entstehen 3 Tabellen (Person, Haustier, Tierhalter ).
•
Der Id-Schlüssel der Tabelle Tierhalter setzt sich zusammen aus dem
Attribut PNr und TNr.
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter)
Tierhalter (PNr,TNr)
SS 2007 Datenbanken
Seite 101
Person
Tierhalter
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
Haustier
TNr
1
2
3
4
Art
Vogel
Hund
Hund
Fisch
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Alle Tupel in den Tabellen sind
nun verschieden
PNr
TNr
1
1
1
4
2
3
2
1
2
4
3
2
3
4
Person
Nur eine
Kombination aus
PNr und TNr
kann in dieser
Tabelle der
Id-Schlüssel sein
N
M
hat
1
Haustier
1
N
M
Tierhalter
SS 2007 Datenbanken
Seite 102
b) Beziehungstyp
Person
N
hat
M
Haustier
Eine Person kann mehrere (kein, ein oder mehrere) Haustier haben,
ein Haustier gehört mehreren (mindestens einer) Personen.
SS 2007 Datenbanken
Seite 103
Ü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.
Der Goldfisch hat mehrere
Besitzer
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
Diese Person besitzt kein
Haustier
Haustier
TNr
1
2
3
4
1
4
4
Art
Vogel
Hund
Hund
Fisch
Vogel
Fisch
Fisch
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Papagei
20
Goldfisch 0,5
Goldfisch 0,5
PNr
1
3
2
1
2
2
3
SS 2007 Datenbanken
Seite 104
Um auch hier Doppelspeicherung zu vermeiden, muss transformiert
werden
Person
•
N
hat
M
Haustier
Es entstehen 3 Tabellen (Person, Haustier, Tierhalter ).
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter)
Tierhalter (PNr,TNr)
SS 2007 Datenbanken
Seite 105
Person
Tierhalter
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
Haustier
TNr
1
2
3
4
Art
Vogel
Hund
Hund
Fisch
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
PNr
TNr
1
1
1
4
2
3
2
1
2
4
3
2
3
4
Nur eine
Kombination aus
PNr und TNr
kann in dieser
Tabelle der
Id-Schlüssel sein
N
M
hat
Person
Haustier
Alle Tupel in den Tabellen sind
nun verschieden
1
1
N
M
Tierhalter
SS 2007 Datenbanken
Seite 106
c) Beziehungstyp
Person
N
hat
M
Haustier
Eine Person kann mehrere (kein, ein oder mehrere) Haustier haben,
ein Haustier kann mehreren Personen gehören.
SS 2007 Datenbanken
Seite 107
Ü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.
Der Hund hat keinen
Besitzer
Person
PNr
Name
Vorname
1
Meier
Kai
2
Müller
Ute
3
Becker
Inga
4
Kohnen
Bernd
Diese Person besitzt kein
Haustier
Haustier
TNr
1
2
3
4
1
4
4
Art
Vogel
Hund
Hund
Fisch
Vogel
Fisch
Fisch
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Papagei
20
Goldfisch 0,5
Goldfisch 0,5
PNr
1
2
1
2
2
3
SS 2007 Datenbanken
Seite 108
Hier tauchen also zwei Probleme auf:
Doppelspeicherung und Nullwerte im Fremdschlüssel.
Person
N
hat
M
Haustier
Also muss transformiert werden:
•
Es entstehen 3 Tabellen (Person, Haustier, Tierhalter ).
Kurzschreibweise: Person (PNr, Name, Vorname)
Haustier (TNr, Art, Rasse, Alter)
Tierhalter (PNr,TNr)
SS 2007 Datenbanken
Seite 109
Person
Tierhalter
PNr
Name
Vorname
1
Meier
2
PNr
TNr
Kai
1
1
Müller
Ute
1
4
3
Becker
Inga
2
3
4
Kohnen
Bernd
2
1
2
4
3
4
Haustier
TNr
1
2
3
4
Art
Vogel
Hund
Hund
Fisch
Rasse
Alter
Papagei
20
Boxer
1
Dackel
10
Goldfisch 0,5
Nur eine
Kombination aus
PNr und TNr
kann in dieser
Tabelle der
Id-Schlüssel sein
Alle Tupel in den Tabellen sind
nun verschieden
N
M
hat
Person
Haustier
Alle Tupel in den Tabellen sind
nun verschieden
1
1
N
M
Tierhalter
SS 2007 Datenbanken
Seite 110
Rekursiv binäre Beziehung
Auf den nächsten Folien wird die Überführung zweier möglicher
rekursiver Beziehungen dargestellt:
Beispiel 1
1
Mitarbeiter
verheiratet
1
SS 2007 Datenbanken
Seite 111
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.
SS 2007 Datenbanken
Seite 112
2.
Möglichkeit der Umsetzung in Tabellenform
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.
SS 2007 Datenbanken
Seite 113
3.
Möglichkeit der Umsetzung in Tabellenform
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.
SS 2007 Datenbanken
Seite 114
Beispiel 2
1
Mitarbeiter
leitet
N
Ein Mitarbeiter wird von genau einem Mitarbeiter (dies ist der Abteilungsleiter)
geleitet,
ein Mitarbeiter kann N Mitarbeiter leiten.
SS 2007 Datenbanken
Seite 115
1.
Möglichkeit der Umsetzung in Tabellenform
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
1
2
3
4
5
6
7
LNr
4
4
3
3
3
3
4
Name
Schmidt
Müller
Meier
Dicke
Becker
Fischer
Bauer
Vorname
Uwe
Anke
Bettina
Malte
Ingo
Volker
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.
SS 2007 Datenbanken
Seite 116
2.
Möglichkeit der Umsetzung in Tabellenform
Zwei Tabellen der Form
Mitarbeiter (MNr, LNr, Name, Vorname) ,
Abteilungsleiter (LNr, MNr).
Mitarbeiter
MNr
LNr
1
2
2
2
3
1
4
1
5
1
6
1
7
2
Name
Schmidt
Müller
Meier
Dicke
Becker
Fischer
Bauer
Vorname
Uwe
Anke
Bettina
Malte
Ingo
Volker
Ute
Abteilungsleiter
LNr
1
2
MNr
3
4
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.
SS 2007 Datenbanken
Seite 117
3.
Möglichkeit der Umsetzung in Tabellenform
Drei Tabellen der Form
Mitarbeiter (MNr, ANr, Name, Vorname) ,
Abteilung (ANr, Name)
Abteilungsleiter (ANr, MNr).
SS 2007 Datenbanken
Seite 118
Abteilung
ANr
Name
1
Bo1 Operativ
2
Bo1P Projekte
MNr
3
4
Mitarbeiter
MNr
1
2
3
4
5
6
7
Anr
2
2
1
1
1
1
2
Abteilungsleiter
Name
Schmidt
Müller
Meier
Dicke
Becker
Fischer
Bauer
ANr
1
2
Vorname
Uwe
Anke
Bettina
Malte
Ingo
Volker
Ute
SS 2007 Datenbanken
Seite 119
Tabellendarstellung für Ternäre- Beziehungen bzw. n-äre Beziehungen
Als Beispiel betrachten wir die folgende Ternäre-Beziehung
Projekt
N
hat
M
Mitarbeiter
P
Qualifikation
Um jede Beziehung abzubilden, müssen zusätzlich zu den 3 Entities 3
weitere Beziehungstabellen gebildet werden.
SS 2007 Datenbanken
Seite 120
Mitarbeiter
MNr
1
2
3
4
5
6
7
Name
Schmidt
Müller
Meier
Dicke
Becker
Fischer
Bauer
ProjektMitarbeiter
MNr PNr
1
1
1
2
3
2
4
1
5
1
6
2
7
1
Vorname
Uwe
Anke
Bettina
Malte
Ingo
Volker
Ute
Projekt
PNr
1
2
Name
CostPlus
EasyGo
Beziehungstabellen
Laufzeit
6
12
Qualifikation
ProjektQualifikation
PNr QNr
1
1
1
3
1
4
2
3
2
5
StDat
01.09.2003
01.04.2004
MaQualifikation
MNr QNr
1
1
1
5
2
2
3
4
4
4
4
5
5
1
5
2
6
3
7
3
QNr
1
2
3
4
5
Name
C++
Oracle
Project Manager
GUI-Designer
Business Analyst
SS 2007 Datenbanken
Seite 121
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
SS 2007 Datenbanken
Seite 122
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
SS 2007 Datenbanken
Seite 123
Mitarbeiter
MNr
1
2
3
4
5
6
7
Name
Schmidt
Müller
Meier
Dicke
Becker
Fischer
Bauer
Vorname
Uwe
Anke
Bettina
Malte
Ingo
Volker
Uter
Projekt
PNr
Name
1
CostPlus
2
EasyGo
Beziehungstabelle
Laufzeit
6
12
Qualifikation
Projektressourcen
PNr MNr
1
1
1
4
1
4
1
5
1
5
1
7
2
1
2
1
2
3
2
6
StDat
01.09.2003
01.04.2004
QNr
1
4
5
1
2
3
1
5
3
4
QNr
1
2
3
4
5
Name
C++
Oracle
Project Manager
GUI-Designer
Business Analyst
SS 2007 Datenbanken
Seite 124
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.
Laborassistent
Sekretär
Mitarbeiter
IS-A
d
Professor
Techniker
SS 2007 Datenbanken
Seite 125
Ü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)
SS 2007 Datenbanken
Seite 126
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.
SS 2007 Datenbanken
Seite 127
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.
SS 2007 Datenbanken
Seite 128
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.
SS 2007 Datenbanken
Seite 129
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.
SS 2007 Datenbanken
Seite 130
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).
SS 2007 Datenbanken
Seite 131
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
SS 2007 Datenbanken
Seite 132
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.
SS 2007 Datenbanken
Seite 133
Beispiel: Tabelle mit Redundanzen
Kurs_Nr
14
7
16
9
11
Kurs_Bez
Datenbanken
Einführung Programmierung
Betriebssysteme I
Verteilte Systeme
Data-Warehouse
Semester
WS 04/05
SS 05
WS 04/05
SS 05
SS 05
Redundanzen
Doz_Kürzel
dm
dm
ib
ib
ak
Doz_Name
Meier
Meier
Bauer
Bauer
Kühne
SS 2007 Datenbanken
Seite 134
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
SS 2007 Datenbanken
Seite 135
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.
SS 2007 Datenbanken
Seite 136
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.
SS 2007 Datenbanken
Seite 137
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.
SS 2007 Datenbanken
Seite 138
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
123 456 Sparkasse Frankfurt (630 500 00)
234 567 Sparda-Bank Kiel (600 100 70)
987 654 Postbank Bochum (600 908 00)
876 543
Firmenname
Bau u. Partner
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.
SS 2007 Datenbanken
Seite 139
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.
Firmenkonto
KontoNr
123456
234567
987654
876543
654452
4543288
54322686
BLZ
630 500 00
600 100 70
600 908 00
600 908 00
500 400 00
100 20522
631 500 00
Geldinstitut
FID
Sparkasse Frankfurt
101
Sparda-Bank Kiel
101
Postbank Bochum
102
Postbank Bochum
102
Commerzbank Frankfurt 103
Deutsche Bank Hamburg 103
Sparkasse Frankfurt
103
Firmenname
Bau und Partner
Bau und Partner
Burkhardt
Burkhardt
WohnIdee
WohnIdee
WohnIdee
Firmenkonto(KontoNr, BLZ, Geldinstitut, FID, Firmenname)
SS 2007 Datenbanken
Seite 140
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 IdSchlüsseln in der 2. Normalform befinden.
SS 2007 Datenbanken
Seite 141
2. Normalform
KontoNr
123456
234567
987654
876543
654452
4543288
54322686
Geldinstitut ist nur von BLZ abhängig
BLZ
630 500 00
600 100 70
600 908 00
600 908 00
500 400 00
100 20522
631 500 00
Geldinstitut
Sparkasse Frankfurt
Sparda-Bank Kiel
Postbank Bochum
Postbank Bochum
Commerzbank Frankfurt
Deutsche Bank Hamburg
Sparkasse Frankfurt
FID
101
101
102
102
103
103
103
Firmenname
Bau und Partner
Bau und Partner
Burkhardt
Burkhardt
WohnIdee
WohnIdee
WohnIdee
Die Tabelle befindet sich also nicht in der 2. Normalform.
SS 2007 Datenbanken
Seite 142
2. Normalform
FirmenKonto
KontoNr BLZ
123456 63050000
234567 60010070
987654 60090800
876543 60090800
654452 50040000
4543288 10020522
54322686 63150000
Bank
FID
101
101
102
102
103
103
103
Firmenname
Bau und Partner
Bau und Partner
Burkhardt
Burkhardt
WohnIdee
WohnIdee
WohnIdee
BLZ
63050000
60010070
60090800
50040000
Geldinstitut
Sparkasse Frankfurt
Sparda-Bank Kiel
Postbank Bochum
Commerzbank Frankfurt
10020522 Deutsche Bank Hamburg
63150000 Sparkasse Frankfurt
Es entstehen 2 Tabellen
FirmenKonto(KontoNr, BLZ, FID, Firmenname)
Bank(BLZ, Geldinstitut)
SS 2007 Datenbanken
Seite 143
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.
SS 2007 Datenbanken
Seite 144
3. Normalform
Die Tabelle Bank befindet sich also schon in der 3. Normalform.
Die Tabelle FirmenKonto nicht, da das Attribut Firmenname vom IdSchlüssel (KontoNr, BLZ) transitiv abhängig ist.
KontoNr
123456
234567
987654
876543
654452
4543288
54322686
BLZ
FID
630 500 00 101
600 100 70 101
600 908 00 102
600 908 00 102
50040000 103
10020522 103
63150000 103
Firmenname
Bau und Partner
Bau und Partner
Burkhardt
Burkhardt
WohnIdee
WohnIdee
WohnIdee
SS 2007 Datenbanken
Seite 145
3. Normalform: keine Abhängigkeiten über Umwege
Bank
FirmenKonto
KontoNr
123456
234567
987654
876543
654452
4543288
54322686
BLZ
630 500 00
600 100 70
600 908 00
600 908 00
50040000
10020522
63150000
FID
101
101
102
102
103
103
103
BLZ
63050000
60010070
60090800
50040000
Geldinstitut
Sparkasse Frankfurt
Sparda-Bank Kiel
Postbank Bochum
Commerzbank Frankfurt
10020522 Deutsche Bank Hamburg
63150000 Sparkasse Frankfurt
Firma
FID Firmenname
101 Bau und Partner
102 Burkhardt
Nun sind alle Tabellen in der 3. Normalform.
103 WohnIdee
SS 2007 Datenbanken
Seite 146
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.
SS 2007 Datenbanken
Seite 147
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.
SS 2007 Datenbanken
Seite 148
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.
SS 2007 Datenbanken
Seite 149
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.
SS 2007 Datenbanken
Seite 150
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 IdSchlüssel transitiv abhängig ist.
4. Normalform: Alle Tabellen sind in der 3. Normalform und nur noch
lokale und globale Attribute existieren.
SS 2007 Datenbanken
Seite 151
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)
SS 2007 Datenbanken
Seite 152
Einige Bemerkung zur Normalisierung:
•
Durch die Normalisierung wird erreicht, dass die Tabellen redundanzfrei 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.
SS 2007 Datenbanken
Seite 153
Vorgehen beim Datenbankentwurf
Beim Entwurf werden zusammenfassend folgende Aktivitäten durchgeführt
(es kann, bzw. muss auch wieder zurückgesprungen werden):
1.
Definition der Aufgabenstellung
Zunä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.
Informationsbeschaffung
Es werden alle für die Anwendung benötigten Informationen gesammelt.
3.
Bestimmung der Entities mit ihren Attributen
Es 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“.
SS 2007 Datenbanken
Seite 154
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.
SS 2007 Datenbanken
Seite 155
6.
Ableiten der Tabellenstruktur aus dem ERM
Aus dem ERM werden die Tabellenstrukturen einschließlich der
Fremdschlüssel abgeleitet.
7.
Überprüfung des Entwurfs mit Hilfe der globalen Normalisierung
(bis 4NF). Damit können logische Fehler bei der ERM-Methode festgestellt
werden.
8.
Festlegung der Datentypen und Formulierung der
Konsistenzbedingungen
Formulierung der Bedingungen, die von den gespeicherten Daten eingehalten
werden müssen. Damit ist sichergestellt, dass die Datenkonsistenz jederzeit
erhalten bleibt.
9.
Test des Entwurfs
Erstellen der Datenbank als Prototyps. Testen anhand eines Testkonzeptes.
10. Transaktionen definieren
11. Anlegen von Benutzersichten und Zugriffsrechte
SS 2007 Datenbanken
Seite 156
Ein Beispiel: Sitzplatzreservierung auf Flügen
Aufgabenstellung
Die neu gegründete Airline EasyFlight möchte ihr SitzplatzReservierungssystem ü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.
SS 2007 Datenbanken
Seite 157
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
SS 2007 Datenbanken
Seite 158
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
SS 2007 Datenbanken
Seite 159
Flugereignis
Datum
Flug an einem bestimmten Datum
Sitz
SitzNr
Sitzplatznummer
Kategorie
F, C oder M
Bezeichnung
z.B. 7D
SS 2007 Datenbanken
Seite 160
Bestimmung bzw. Bildung der Id-Schlüssel
SS 2007 Datenbanken
Seite 161
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
SS 2007 Datenbanken
Seite 162
Ü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.
SS 2007 Datenbanken
Seite 163
Ü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?
SS 2007 Datenbanken
Seite 164
Festlegung der Datentypen und Formulierung der Konsistenzbedingungen
Beispiel für Datentypen
Tabelle
Attribut
Wertebereich
Airport
APID
Ganze Zahl
3LC
Zeichenkette mit 3 Zeichen
Bezeichnung
Zeichenkette mit 50 Zeichen
SitzID
Ganze Zahl
Kategorie
Nur Zeichen C, M, F zugelassen
Bezeichnung
Zeichenkette
Sitz
SS 2007 Datenbanken
Seite 165
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.
SS 2007 Datenbanken
Seite 166
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
SS 2007 Datenbanken
Seite 167
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.
SS 2007 Datenbanken
Seite 168
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.
SS 2007 Datenbanken
Seite 169
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
SS 2007 Datenbanken
Seite 170
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
SS 2007 Datenbanken
Seite 171
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
SS 2007 Datenbanken
Seite 172
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“
SS 2007 Datenbanken
Seite 173
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.
SS 2007 Datenbanken
Seite 174
Optional sind
•
DEFAULT-Option
•
Spalten-Constraint
•
Tabellen-Constraint
SS 2007 Datenbanken
Seite 175
Constraints
•
Constraints erzwingen Regeln auf Tabellenebene
•
Die folgenden Constraint-Regeln sind gültig
• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
SS 2007 Datenbanken
Seite 176
Constraint
Beschreibung
NOT NULL
Gibt an, dass die Spalte keinen NULLWert 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.
SS 2007 Datenbanken
Seite 177
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,…)
SS 2007 Datenbanken
Seite 178
NOT NULL-Constraint
Dieses Constraint kann nur auf Spaltenebene definiert werden.
Beispiel: Anlegen einer Tabelle Airport
Constraint wird vom
System benannt.
CREATE TABLE Airport
(APID
INT
NOT NULL ,
A3LC
CHAR(3)
NOT NULL
ABEZ
VARCHAR(100),
COUNTRY
VARCHAR(100)
DEFAULT 'aaa',
);
SS 2007 Datenbanken
Seite 179
oder
Constraint wird vom
Nutzer benannt.
CREATE TABLE Airport
(APID
INT
NOT NULL ,
A3LC
CHAR(3)
DEFAULT 'aaa‘
CONSTRAINT A_3LC NOT NULL,
ABEZ
VARCHAR(100),
COUNTRY
VARCHAR(100));
SS 2007 Datenbanken
Seite 180
UNIQUE-Constraint
Ein UNIQUE-Constraint erfordert, dass jeder Wert eines Attributs oder
Attributkombination eindeutig ist.
Dieses Constraint wird auf Tabellen- oder Spaltenebene definiert.
CREATE TABLE Airport
(APID
INT
NOT NULL ,
A3LC
CHAR(3) NOT NULL
ABEZ
VARCHAR(100),
COUNTRY
VARCHAR(100)
UNIQUE,
);
SS 2007 Datenbanken
Seite 181
oder
CREATE TABLE Airport
(APID
INT
A3LC
CHAR(3) NOT NULL ,
ABEZ
VARCHAR(100),
COUNTRY
VARCHAR(100),
NOT NULL ,
CONSTRAINT A_3LC UNIQUE (A3LC)
);
SS 2007 Datenbanken
Seite 182
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.
SS 2007 Datenbanken
Seite 183
Beispiel
CREATE TABLE Airport
(APID
INT
PRIMARY KEY ,
A3LC
CHAR(3) NOT NULL ,
ABEZ
VARCHAR(100),
COUNTRY VARCHAR(100)
);
Bemerkung: Besteht ein Primary Key aus einer Attributkombination, so
muss PRIMARY KEY als Constraint auf Tabellenebene angegeben
werden.
SS 2007 Datenbanken
Seite 184
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.
SS 2007 Datenbanken
Seite 185
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)
);
SS 2007 Datenbanken
Seite 186
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 NULLWerte.
SS 2007 Datenbanken
Seite 187
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.
SS 2007 Datenbanken
Seite 188
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
SS 2007 Datenbanken
Seite 189
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,…;
SS 2007 Datenbanken
Seite 190
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
Beispiel für die Syntax zum Einfügen einer neuen Spalte
ALTER TABLE Tabellenname ADD
[CONSTRAINT constraint] type (Attribut);
SS 2007 Datenbanken
Seite 191
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…]);
SS 2007 Datenbanken
Seite 192
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
SS 2007 Datenbanken
Seite 193
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);
SS 2007 Datenbanken
Seite 194
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‘);
SS 2007 Datenbanken
Seite 195
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.
SS 2007 Datenbanken
Seite 196
Grundlegende SELECT-Anweisung
SELECT * | {[DISTINCT] Attribut | Ausdruck [alias],…}
FROM tabellenname;
| steht für „alternativ“
•
SELECT bestimmt, welches Attribut
•
FROM bestimmt, welche Tabelle
SS 2007 Datenbanken
Seite 197
Die Tabelle mitarbeiter(mid,name,vorname,abteilung,gehalt) habe 6 Tupel:
Alle Spalten auswählen
SELECT *
FROM mitarbeiter;
liefert
mid
100
101
102
103
104
105
name
Hoffmann
Schulte
Becker
Schmidt
Schlodder
Hoffmann
vorname
Richard
Karin
Malte
Andreas
Kim
Richard
abteilung
F3/20
F2/4 Projekte
D13/1
A12/2 Marketing
F3/21
gehalt
40000,00
60450,00
25230,00
65000,00
40000,00
42000,00
Alternativ können auch statt * alle Attribute angeben werden:
SELECT mid, name, vorname, abteilung, gehalt
FROM
mitarbeiter
SS 2007 Datenbanken
Seite 198
Bestimmte Spalten auswählen
SELECT name, vorname
FROM mitarbeiter;
liefert
name
Hoffmann
Schulte
Becker
Schmidt
Schlodder
Hoffmann
vorname
Richard
Karin
Malte
Andreas
Kim
Richard
Die Reihenfolge der Angabe der Attribute entscheidet über die Reihenfolge
der Ausgabe.
SS 2007 Datenbanken
Seite 199
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
Hoffmann
Schulte
Becker
Schmidt
Schlodder
Hoffmann
vorname
Richard
Karin
Malte
Andreas
Kim
Richard
gehalt
40000,00
60450,00
25230,00
65000,00
40000,00
42000,00
gehalt/12+100
3433,3333
5137,5000
2202,5000
5516,6667
3433,3333
3600,0000
Bemerkung: Es wird keine neue Spalte in der Tabelle erzeugt sondern
nur in der Anzeige eine weitere Spalte hinzugefügt.
SS 2007 Datenbanken
Seite 200
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.
SS 2007 Datenbanken
Seite 201
Attribut-Aliasnamen verwenden
SELECT name AS Mitarbeitername,gehalt, gehalt/12 AS Monatsgehalt
FROM mitarbeiter;
Mitarbeitername
Hoffmann
Schulte
Becker
Schmidt
Schlodder
Hoffmann
gehalt
40000,00
60450,00
25230,00
65000,00
40000,00
42000,00
Monatsgehalt
3333,3333
5037,5000
2102,5000
5416,6667
3333,3333
3500,0000
SELECT name "Mitarbeiter Name", gehalt, gehalt/12 Monatsgehalt
FROM mitarbeiter;
Mitarbeiter Name
Hoffmann
Schulte
gehalt
40000,00
60450,00
Monatsgehalt
3333,3333
5037,5000
SS 2007 Datenbanken
Seite 202
Mehrfach vorhandene Zeilen ausblenden mit DISTINCT
SELECT name
FROM mitarbeiter;
liefert
name
Hoffmann
Schulte
Becker
Schmidt
Schlodder
Hoffmann
SELECT DISTINCT name
FROM
liefert dagegen
mitarbeiter
name
Hoffmann
Schulte
Becker
Schmidt
Schlodder
SS 2007 Datenbanken
Seite 203
Daten einschränken und sortieren – die WHERE- Klausel
Die zurückgegebenen Zeilen werden mit Hilfe der WHERE-Klausel
eingeschränkt.
SELECT * | {[DISTINCT] Attribut | Ausdruck [alias],…}
FROM tabellenname
[WHERE bedingung(s)];
Die WHERE-Klausel besteht aus
•
Spaltenname,
•
Vergleichsoperator,
•
Attribut, Konstante oder Werteliste
SS 2007 Datenbanken
Seite 204
Die WHERE-Klausel
SELECT *
FROM
mitarbeiter
WHERE mid = 101;
liefert
oder
mid
101
name
Schulte
vorname
Karin
abteilung
F2/4 Projekte
gehalt
60450,00
SELECT mid, name, vorname , gehalt
FROM
Zeichenfolgen müssen in
Hochkommata gesetzt
werden
mitarbeiter
WHERE abteilung= 'F3/20';
mid
100
name
Hoffmann
vorname
Richard
gehalt
40000,00
SS 2007 Datenbanken
Seite 205
Die folgenden Vergleichsoperatoren sind möglich
=
>
<
>=
<
<=
<>
(auch !=)
SELECT *
FROM
mitarbeiter
WHERE gehalt > 40000
mid
101
103
name
Schulte
Schmidt
vorname
Karin
Andreas
abteilung
F2/4 Projekte
A12/2 Marketing
gehalt
60450,00
65000,00
SS 2007 Datenbanken
Seite 206
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
SS 2007 Datenbanken
Seite 207
Der Operator BETWEEN
SELECT *
FROM mitarbeiter
WHERE gehalt BETWEEN 40000 AND 60450;
liefert
mid
100
101
104
105
name
Hoffmann
Schulte
Schlodder
Hoffmann
vorname
Richard
Karin
Kim
Richard
abteilung
F3/20
F2/4 Projekte
F3/21
gehalt
40000,00
60450,00
40000,00
42000,00
SS 2007 Datenbanken
Seite 208
Der Operator IN
SELECT *
FROM mitarbeiter
WHERE mid IN (100, 104);
liefert
mid
100
104
name
Hoffmann
Schlodder
vorname
Richard
Kim
abteilung
F3/20
gehalt
40000,00
40000,00
Bemerkung: Zeichen- oder Datumswerte müssen in der Liste in Hochkommata
(‘‘)gesetzt werden.
SS 2007 Datenbanken
Seite 209
Der Operator LIKE
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%' ;
mid
101
103
104
name
Schulte
Schmidt
Schlodder
vorname
Karin
Andreas
Kim
abteilung
F2/4 Projekte
A12/2 Marketing
gehalt
60450,00
65000,00
40000,00
SS 2007 Datenbanken
Seite 210
Der Operator IS NULL
SELECT *
FROM mitarbeiter
WHERE abteilung IS NULL;
liefert
mid
104
name
Schlodder
vorname
Kim
abteilung
gehalt
40000,00
Entsprechen kann auf IS NOT NULL abgefragt werden.
SS 2007 Datenbanken
Seite 211
Logische Operatoren
Operator
Bedeutung
AND
TRUE, falls beide Komponentenbedingungen wahr sind
OR
TRUE, falls eine der beiden Komponentenbedingungen wahr ist.
NOT
TRUE, falls die Bedingung falsch ist.
Mit diesen Operatoren können mehrere Bedingungen in der WHEREKlausel verknüpft werden.
SS 2007 Datenbanken
Seite 212
SELECT *
FROM mitarbeiter
WHERE name LIKE 'SCH%'
AND gehalt > 50000;
liefert
mid
101
103
name
Schulte
Schmidt
vorname
Karin
Andreas
abteilung
F2/4 Projekte
A12/2 Marketing
gehalt
60450,00
65000,00
SS 2007 Datenbanken
Seite 213
Dagegen ergibt die Anweisung
SELECT *
FROM mitarbeiter
WHERE name LIKE 'SCH%'
OR gehalt > 50000;
mid
101
103
104
name
Schulte
Schmidt
Schlodder
vorname
Karin
Andreas
Kim
abteilung
F2/4 Projekte
A12/2 Marketing
gehalt
60450,00
65000,00
40000,00
SS 2007 Datenbanken
Seite 214
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
101
102
103
105
name
Schulte
Becker
Schmidt
Hoffmann
vorname
Karin
Malte
Andreas
Richard
abteilung
F2/4 Projekte
D13/1
A12/2 Marketing
F3/21
gehalt
60450,00
25230,00
65000,00
42000,00
SS 2007 Datenbanken
Seite 215
Sortieren mit der ORDER BY-Klausel
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
SELECT * | {[DISTINCT] Attribut | Ausdruck [alias],…}
FROM tabellenname
[WHERE bedingung(s)]
[ORDER BY {Attribut , Ausdruck ,alias} [ASC|DESC|]];
SS 2007 Datenbanken
Seite 216
Absteigend und Aufsteigend sortieren
SELECT *
FROM mitarbeiter
ORDER BY name DESC;
liefert
mid
101
103
104
105
100
102
name
Schulte
Schmidt
Schlodder
Hoffmann
Hoffmann
Becker
vorname
Karin
Andreas
Kim
Richard
Richard
Malte
abteilung
F2/4 Projekte
A12/2 Marketing
F3/21
F3/20
D13/1
gehalt
60450,00
65000,00
40000,00
42000,00
40000,00
25230,00
SS 2007 Datenbanken
Seite 217
Es kann auch nach Attributen sortiert werden, die nicht in der
SELECT Anweisung enthalten sind
SELECT name, vorname
FROM mitarbeiter
ORDER BY gehalt DESC;
SS 2007 Datenbanken
Seite 218
Auch nach mehreren Attributen oder nach alias kann sortiert
werden
SELECT name, gehalt/12 monatsgehalt
FROM mitarbeiter
ORDER BY monatsgehalt, name;
name
Becker
Hoffmann
Schlodder
Hoffmann
Schulte
Schmidt
monatsgehalt
2102,5
3333,333333
3333,333333
3500
5037,5
5416,666667
SS 2007 Datenbanken
Seite 219
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).
SS 2007 Datenbanken
Seite 220
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.
SS 2007 Datenbanken
Seite 221
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
LOWER(attribut|ausdruck)
UPPER(attribut|ausdruck)
CONCAT(attribut1|ausdruck1,attribut2|ausdruck2)
LENGTH(attribut|ausdruck)
TRIM(trim_character FROM trim_source)
Zweck
Konvertiert alphanumerische Zeichenwerte in
Kleinbuchstaben
Konvertiert alphanumerische Zeichenwerte in
Großbuchstaben
Verkettung der Attribute bzw. Ausdrücke
entfernt am Anfang und Ende das Zeichen oder
die Zeichenkette trim_character aus
trim_source
SS 2007 Datenbanken
Seite 222
Beispiel für das Verwenden von Zeichenfunktionen
SELECT LOWER(name), UPPER(vorname),
CONCAT(vorname,' ',name) AS mitarbeitername, LENGTH(name),
TRIM('Sch' FROM name)
FROM mitarbeiter;
LOWER(name) UPPER(vorname) mitarbeitername LENGTH(name TRIM('Sch' FROM name)
hoffmann
RICHARD
Richard Hoffmann
8
Hoffmann
schulte
KARIN
Karin Schulte
7
ulte
becker
MALTE
Malte Becker
6
Becker
schmidt
ANDREAS
Andreas Schmidt
7
midt
schlodder
KIM
Kim Schlodder
9
lodder
hoffmann
RICHARD
Richard Hoffmann
8
Hoffmann
SS 2007 Datenbanken
Seite 223
Numerische Funktionen
•
haben als Input und Output numerische Werte
Auszug der Funktionen
Funktion
ROUND (attribut|ausdruck,n)
TRUNC (attribut|ausdruck,n)
bzw. TRUNCATE(attribut|ausdruck,n) in MySQL
MOD (m,n)
SQRT(attribut|ausdruck)
SIN(…), COS(..), TAN(…)
…
Zweck
Rundet die Spalte bzw. den Ausdruck auf n
Dezimalstellen. Ist kein Wert angegeben, wird
auf einen ganzzahligen Wert gerundet.
Schneidet die Spalte bzw. den Ausdruck auf n
Dezimalstellen ab. Ist kein Wert für n
angegeben, werden die Ziffern hinter dem
Dezimalkomma abgeschnitten
Gibt den ganzzahligen Rest von m/n zurück.
Berechnet die Wurzel
Winkelfunktionen
SS 2007 Datenbanken
Seite 224
Beispiel für das Verwenden von Numerischen Funktionen
SELECT gehalt, gehalt/12, ROUND(gehalt/12,2),
TRUNC (gehalt/12,2), MOD(gehalt, 12)
FROM mitarbeiter;
gehalt
65000
25230
60450
40000
40000
42000
gehalt/12
5416,6667
2102,5
5037,5
3333,333333
3333,333333
3500
ROUND(gehalt/12,2) TRUNC(gehalt/12)
5416,67
5416,66
2102,50
2102,50
5037,50
5037,50
3333,33
3333,33
3333,33
3333,33
3500,00
3500,00
MOD(gehalt,12)
8
6
6
4
4
0
SS 2007 Datenbanken
Seite 225
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.
SS 2007 Datenbanken
Seite 226
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.
SS 2007 Datenbanken
Seite 227
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
SS 2007 Datenbanken
Seite 228
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
SS 2007 Datenbanken
Seite 229
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.
SS 2007 Datenbanken
Seite 230
Bedingte Ausdrücke
Bedingte Ausdrücke stellen die IF-THEN-ELSE Logik innerhalb einer SQLAnweisung 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
SS 2007 Datenbanken
Seite 231
Beispiel für das Verwenden von CASE
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;
name
Hoffmann
Schulte
Becker
Schmidt
Schlodder
Hoffmann
vorname
Richard
Karin
Malte
Andreas
Kim
Richard
abteilung
F3/20
F2/4 Projekte
D13/1
A12/2 Marketing
F3/21
gehalt
40000,00
60450,00
25230,00
65000,00
40000,00
42000,00
Angepasstes Gehalt
40500,00
60450,00
25230,00
65100,00
40000,00
42000,00
SS 2007 Datenbanken
Seite 232
EINSCHUB- Daten in Tabellen ändern und löschen
Tupel in einer Tabelle werden in SQL mit Hilfe der UPDATE Anweisung geändert:
Syntax
UPDATE
tabellenname
SET
attribut = wert [, attribut2 = wert,…]
[WHERE
bedingung];
Es können ein oder mehrere Tupel pro Tabelle geändert werden.
SS 2007 Datenbanken
Seite 233
Beispiel für das Ändern von Tupeln
Die Tabelle airport habe die folgenden Attribute und Datentypen (hier in MySQL):
Field
Type
APID
int(11)
A3LC
char(3)
ABEZ
varchar(100)
COUNTRY
varchar(100)
UPDATE_DAT
timestamp(14)
INSERT_DAT
timestamp(14)
In die Tabelle werden zwei Datensätze eingefügt:
INSERT INTO airport
VALUES (100,'FRA', 'Frankfurt','Deutschland',NULL,NULL);
INSERT INTO airport (APID, A3LC, ABEZ, COUNTRY,INSERT_DAT)
VALUES (105,'LAX', 'Los Angeles','USA',NULL);
SS 2007 Datenbanken
Seite 234
Damit hat die Tabelle den Inhalt
Frankfurt soll in Frankfurt am Main geändert werden:
UPDATE airport
SET
ABEZ = 'Frankfurt am Main'
Satz wurde
geändert
WHERE APID = 100;
SS 2007 Datenbanken
Seite 235
Löschen von Tupeln
Tupel in einer Tabelle werden in SQL mit Hilfe der DELETE Anweisung gelöscht:
Syntax
DELETE [FROM]
tabellenname
[WHERE
bedingung];
SS 2007 Datenbanken
Seite 236
Daten aus mehreren Tabellen anzeigen
SS 2007 Datenbanken
Seite 237
mitarbeiter
abteilung
Das Ergebnis der Abfrage:
SS 2007 Datenbanken
Seite 238
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.
SS 2007 Datenbanken
Seite 239
Equi-Joins bzw. Inner-Joins
mitarbeiter
Fremdschlüssel
abteilung
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.
SS 2007 Datenbanken
Seite 240
Datensätze mit Equi-Join abfragen
SELECT
mitarbeiter.m_id, mitarbeiter.name,
mitarbeiter.vorname, abteilung.abt_name
FROM
mitarbeiter, abteilung
WHERE
mitarbeiter.abt_id = abteilung.abt_id;
JOINBedingung
SS 2007 Datenbanken
Seite 241
Die Attribute aus den unterschiedlichen Tabellen müssen 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.
SS 2007 Datenbanken
Seite 242
Tabellen-Aliasnamen
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;
Der Tabellen-Aliasname gilt nur für die aktuelle SELECT-Anweisung.
SS 2007 Datenbanken
Seite 243
Zusätzliche Suchkriterien mit AND
SELECT
m.m_id, m.name,
m.vorname, a.abt_name
FROM
mitarbeiter m, abteilung a
WHERE
m.abt_id = a.abt_id
AND
m.name = 'Schutt';
SS 2007 Datenbanken
Seite 244
Mehrere Tabellen verknüpfen
mitarbeiter
abteilung
standort
SS 2007 Datenbanken
Seite 245
SELECT
m.m_id, m.name, m.vorname, a.abt_name,s.stadt
FROM
mitarbeiter m, abteilung a, standort s
WHERE
m.abt_id = a.abt_id
AND
a.abt_sitz = s.abt_sitz
SS 2007 Datenbanken
Seite 246
Alternativ kann auch ein INNER JOIN gesetzt werden
SELECT
m.m_id, m.name,
m.vorname, a.abt_name
FROM
mitarbeiter m INNER JOIN abteilung a
ON
m.abt_id = a.abt_id;
oder bei Verbindungen von 3 Tabellen
SELECT
m.m_id, m.name, m.vorname,a.abt_name,s.stadt
FROM
mitarbeiter m INNER JOIN abteilung a
ON
m.abt_id = a.abt_id
INNER JOIN standort s
ON
a.abt_sitz = s.abt_sitz;
SS 2007 Datenbanken
Seite 247
Bemerkung
Bei den bisherigen JOINS müssen die vergleichenden Attribute nicht gleich heißen.
Beispiel:
mitarbeiter(m_id, name, vorname, abtid),
abteilung (abt_id, abt_name, abt_sitz)
dann ist ein INNER JOIN gegeben durch
SELECT m.m_id, m.name,
m.vorname, a.abt_name
FROM
mitarbeiter m INNER JOIN abteilung a
ON
m.abtid = a.abt_id;
SS 2007 Datenbanken
Seite 248
INNER JOIN USING
Heißen die zu verbindenden Attribute in den Tabellen gleich, so kann man bei Oracle
auch statt des ON ein USING setzen:
Beispiel: Statt
SELECT
m.m_id, m.name,
m.vorname, a.abt_name
FROM
mitarbeiter m INNER JOIN abteilung a
ON
m.abt_id = a.abt_id;
kann
SELECT
m.m_id, m.name, m.vorname, a.abt_name
FROM
mitarbeiter m INNER JOIN abteilung a
USING
(abt_id);
die Klammer muss gesetzt werden
es darf kein Tabellenpräfix gesetzt werden
SS 2007 Datenbanken
Seite 249
Outer-Joins
mitarbeiter
abteilung
Diese Sätze würden bei
einem INNER-JOIN
rausfallen
Ein OUTER-JOIN zeigt auch Datensätze an, die keinen Bezug zur zweiten
Tabelle haben.
SS 2007 Datenbanken
Seite 250
LEFT OUTER JOIN ( RIGHT OUTER JOIN)
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.
SS 2007 Datenbanken
Seite 251
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
SS 2007 Datenbanken
Seite 252
Beispiel für einen RIGHT OUTER JOIN
SELECT
m.m_id, m.name, m.vorname, a.abt_id, a.abt_name
FROM
mitarbeiter m
RIGHT OUTER JOIN abteilung a
ON
(m.abt_id = a.abt_id);
alle Tupel der
rechten Tabelle
abteilung werden
angezeigt
SS 2007 Datenbanken
Seite 253
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
SELECT
m.m_id, m.name, m.vorname, a.abt_id, a.abt_name
FROM
mitarbeiter m
FULL OUTER JOIN abteilung a
ON
(m.abt_id = a.abt_id);
Bemerkung: Der FULL OUTER JOIN existiert nicht unter MySQL.
SS 2007 Datenbanken
Seite 254
SELF JOIN
Beim SELF JOIN wird eine Tabelle mit sich selbst verknüpft.
mitarbeiter
Attributwerte vom
Fremdschlüssel
CHEF_ID
entsprechen
denen vom IdSchlüssel M_ID
Um CHEF_ID aufzulösen, muss die Tabelle mitarbeiter mit sich selbst
verknüpft werden.
SS 2007 Datenbanken
Seite 255
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;
SS 2007 Datenbanken
Seite 256
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;
SS 2007 Datenbanken
Seite 257
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.
SS 2007 Datenbanken
Seite 258
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
mitarbeiter
abteilung
standort
SS 2007 Datenbanken
Seite 259
Beispiel für den NATURAL JOIN
SELECT m.m_id, m.name, m.vorname,a.abt_name,s.stadt
FROM mitarbeiter m
NATURAL JOIN abteilung a
NATURAL JOIN standort s;
SS 2007 Datenbanken
Seite 260
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
SELECT m.m_id, m.name, m.vorname,a.abt_name,s.stadt
FROM mitarbeiter m
NATURAL JOIN abteilung a
NATURAL JOIN standort s;
…
SS 2007 Datenbanken
Seite 261
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.
SS 2007 Datenbanken
Seite 262
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.
SS 2007 Datenbanken
Seite 263
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;
SS 2007 Datenbanken
Seite 264
Daten mit Gruppenfunktionen aggregieren
SS 2007 Datenbanken
Seite 265
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
SS 2007 Datenbanken
Seite 266
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.
SS 2007 Datenbanken
Seite 267
Funktionen AVG(), SUM(), MIN() und MAX()
SELECT
AVG(gehalt), SUM(gehalt)/10,
MAX(gehalt), MIN(gehalt), SUM(gehalt)
FROM
Ergebnis der Abfrage
mitarbeiter ;
mitarbeiter
NULL-Werte werden ignoriert
SS 2007 Datenbanken
Seite 268
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
SS 2007 Datenbanken
Seite 269
Gruppierungen durchführen mit GROUP BY
SELECT
abt_id, AVG(gehalt)
FROM
mitarbeiter
GROUP BY
abt_id
ergibt
mitarbeiter
gruppieren
nach abt_id
SS 2007 Datenbanken
Seite 270
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.
SS 2007 Datenbanken
Seite 271
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;
SS 2007 Datenbanken
Seite 272
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.
SS 2007 Datenbanken
Seite 273
Gruppenergebnisse filtern
mitarbeiter
Das maximale Gehalt
jeder Abteilung soll
ermittelt werden, wenn
es größer als 3000 ist
SELECT
abt_id, MAX(gehalt)
FROM
mitarbeiter
GROUP BY
abt_id
HAVING
MAX(gehalt) > 3000;
SS 2007 Datenbanken
Seite 274
Die Syntax von Gruppenfunktionen lautet zusammenfassend
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.
SS 2007 Datenbanken
Seite 275
Unterabfragen
Hauptabfrage
Unterabfrage gibt
Wert an
Hauptabfrage zurück
Unterabfrage
SS 2007 Datenbanken
Seite 276
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.
SS 2007 Datenbanken
Seite 277
Ein Beispiel für eine Unterabfrage
mitarbeiter
SELECT m_id, name, vorname
FROM
mitarbeiter
WHERE gehalt >
(SELECT
gehalt
FROM
mitarbeiter
WHERE
name = 'Breitinger');
Ergebnis der Unterabfrage
Ergebnis der Hauptabfrage
SS 2007 Datenbanken
Seite 278
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.
SS 2007 Datenbanken
Seite 279
Single Row-Unterabfragen
•
geben nur eine Zeile zurück
•
verwenden Single Row Vergleichsoperatoren, d.h.
=
>
>=
<
<=
<>
SS 2007 Datenbanken
Seite 280
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);
Ergebnis der Hauptabfrage
SS 2007 Datenbanken
Seite 281
Gruppenfunktionen in Unterabfragen
SELECT
name, vorname
FROM
mitarbeiter
WHERE
gehalt =
(SELECT
MAX(gehalt)
FROM
mitarbeiter);
Ergebnis der Hauptabfrage
SS 2007 Datenbanken
Seite 282
Gruppenfunktionen in Unterabfragen
Welche Mitarbeiter liegen mit ihrem Gehalt über dem Durchschnitt aller Gehälter?
SELECT
name, vorname
FROM
mitarbeiter
WHERE
gehalt >
(SELECT
AVG(gehalt)
FROM
mitarbeiter);
SS 2007 Datenbanken
Seite 283
Unterabfragen in HAVING-Klauseln
SELECT
MIN(m.gehalt) "min gehalt", m.abt_id, a.abt_name
FROM
mitarbeiter m, abteilung a
WHERE
m.abt_id = a.abt_id
GROUP BY m.abt_id, a.abt_name
HAVING
MIN(m.gehalt) >=
(SELECT
MIN(m.gehalt)
FROM
mitarbeiter
WHERE
m.abt_id = 11);
Ergebnis der Hauptabfrage
SS 2007 Datenbanken
Seite 284
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
AND
AND
(SELECT
MAX(g.gehalt_von)
FROM
gehaltstufe g)
(SELECT
MAX(g.gehalt_bis)
FROM
gehaltstufe g)
m.gehalt BETWEEN g.gehalt_von AND g.gehalt_bis;
SS 2007 Datenbanken
Seite 285
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
SS 2007 Datenbanken
Seite 286
Operator ANY in Multiple Row Unterabfragen
SELECT m_id, name, vorname, job_id, gehalt
FROM
mitarbeiter
mitarbeiter
WHERE gehalt < ANY
(
SELECT gehalt
FROM
mitarbeiter
WHERE job_id = 10)
AND
job_id <> 10;
SS 2007 Datenbanken
Seite 287
Operator ALL in Multiple Row Unterabfragen
SELECT m_id, name, vorname, job_id, gehalt
FROM
mitarbeiter
mitarbeiter
WHERE gehalt < ALL
(
SELECT gehalt
FROM
mitarbeiter
WHERE job_id = 10)
AND
job_id <> 10;
SS 2007 Datenbanken
Seite 288
Operator IN und NOT IN in Multiple Row Unterabfragen
Welche Mitarbeiter sind keine Chefs?
SELECT
m.name, m.vorname
FROM
mitarbeiter m
WHERE
m.m_id NOT IN
mitarbeiter
(SELECT v.chef_id
FROM mitarbeiter v)
Ergebnis
SS 2007 Datenbanken
Seite 289
ACHTUNG bei NULL-WERTEN in einer Unterabfrage
Welche Mitarbeiter sind keine Chefs?
SELECT
m.name, m.vorname
FROM
mitarbeiter m
WHERE
m.m_id NOT IN
mitarbeiter
(SELECT v.chef_id
FROM mitarbeiter v)
Es wird kein Ergebnis geliefert.
SS 2007 Datenbanken
Seite 290
Beim Operator IN gibt es mit NULL-Werten keine Probleme:
Welche Mitarbeiter sind Chefs?
SELECT
m.name, m.vorname
FROM
mitarbeiter m
WHERE
m.m_id IN
mitarbeiter
(SELECT v.chef_id
FROM mitarbeiter v)
Ergebnis
SS 2007 Datenbanken
Seite 291
EXISTS and NOT EXISTS
SELECT
m.name, m.vorname
FROM
mitarbeiter m
WHERE EXISTS
(SELECT
'c'
FROM
mitarbeiter v
WHERE
v.chef_id = m.m_id)
SS 2007 Datenbanken
Seite 292
Unterabfragen können auch in der FROM-Klausel verwendet 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
SS 2007 Datenbanken
Seite 293
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.
SS 2007 Datenbanken
Seite 294
•
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
SS 2007 Datenbanken
Seite 295
Beispiel für UNION
mitarbeiter
job_historie
Ausgabe des aktuellen Jobs und aller vorherigen Jobs
eines Mitarbeiters
SELECT
FROM
m_id, job_id
mitarbeiter
UNION
SELECT
FROM
m_id, job_id
job_historie;
SS 2007 Datenbanken
Seite 296
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.
SS 2007 Datenbanken
Seite 297
•
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.
SS 2007 Datenbanken
Seite 298
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.
SS 2007 Datenbanken
Seite 299
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.
SS 2007 Datenbanken
Seite 300
Das Erstellen von Views
Die Syntax für das Erstellen einer View lautet
CREATE VIEW viewname [(alias,…)]
AS
SELECT- Anweisung
[WITH CHECK OPTION]
SS 2007 Datenbanken
Seite 301
Bemerkung zur Syntax
•
Die SELECT-Anweisung darf kein ORDER BY enthalten
•
Werden keine Attribute angegeben, so werden die in der SELECTAnweisung 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.
SS 2007 Datenbanken
Seite 302
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
SS 2007 Datenbanken
Seite 303
Auch durch Änderungen in der View können die Inhalte der Tabelle
verändert werden
UPDATE v_mitarbeiter_abt5_11
SET
name = 'Schuttemann‚
WHERE
m_id = 50;
SELECT *
FROM mitarbeiter
WHERE m_id = 50
ergibt
SS 2007 Datenbanken
Seite 304
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
m.abt_id = a.abt_id
INNER JOIN
standort s
ON
a.abt_sitz = s.abt_sitz
WHERE
s.stadt = 'München'
SELECT *
FROM v_mitarbeiter_abtsitz_muc
ergibt
SS 2007 Datenbanken
Seite 305
Ändert man nun einen Datensatz mit Hilfe der View
UPDATE
v_mitarbeiter_abtsitz_muc
SET
abt_id = 10
WHERE
m_id = 50;
der Mitarbeiter m_id = 50
wird nicht mehr angezeigt
so ergibt
SELECT *
FROM v_mitarbeiter_abtsitz_muc
ergibt
SS 2007 Datenbanken
Seite 306
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
INNER JOIN
abteilung a
ON
m.abt_id = a.abt_id
INNER JOIN
standort s
ON
a.abt_sitz = s.abt_sitz
WHERE
s.stadt = 'München'
WITH CHECK OPTION
SELECT *
FROM v_mitarbeiter_abtsitz_muc
ergibt auch hier
SS 2007 Datenbanken
Seite 307
Ä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
SS 2007 Datenbanken
Seite 308
•
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.
SS 2007 Datenbanken
Seite 309
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
SS 2007 Datenbanken
Seite 310
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.
SS 2007 Datenbanken
Seite 311
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
INNER JOIN abteilung a
ON m.abt_id = a.abt_id
GROUP BY abt_name
werden Gruppenfunktionen
benutzt, so müssen AliasSpaltennamen verwendet werden
SELECT * FROM
v_abt_gehalt
SS 2007 Datenbanken
Seite 312
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
SS 2007 Datenbanken
Seite 313
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.
SS 2007 Datenbanken
Seite 314
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)
SS 2007 Datenbanken
Seite 315
Falls das Attribut vorname in mitarbeiter ein NOT NULL Attribut ist,
ergibt sich beim INSERT die Fehlermeldung
SS 2007 Datenbanken
Seite 316
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.
SS 2007 Datenbanken
Seite 317
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.
SS 2007 Datenbanken
Seite 318
Wie werden die Datensätze bei einer SELECT Anweisung bearbeitet?
Beispiel
SELECT
name, vorname
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.
SS 2007 Datenbanken
Seite 319
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.
SS 2007 Datenbanken
Seite 320
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.
SS 2007 Datenbanken
Seite 321
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
SS 2007 Datenbanken
Seite 322
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);
SS 2007 Datenbanken
Seite 323
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);
SS 2007 Datenbanken
Seite 324
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.
SS 2007 Datenbanken
Seite 325
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 WHEREKlausel oder Join-Bedingung verwendet
•
Die Tabelle ist sehr groß und die meisten Abfragen rufen wahrscheinlich
nicht mehr als 5 % der Zeilen ab.
SS 2007 Datenbanken
Seite 326
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
SS 2007 Datenbanken
Seite 327
Transaktionen
SS 2007 Datenbanken
Seite 328
Beispiel für Transaktionen
Überweisung von einem Konto auf ein anderes Konto.
Konto
100 EUR
werden
überwiesen
SS 2007 Datenbanken
Seite 329
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
2. Erhöhung des Kontostandes mit der Kontonummer 1556432 um 100
UPDATE konto
SET
kontostand = kontostand +100
WHERE
konto_nr = 1556432
SS 2007 Datenbanken
Seite 330
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.
SS 2007 Datenbanken
Seite 331
•
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.
SS 2007 Datenbanken
Seite 332
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.
SS 2007 Datenbanken
Seite 333
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
SS 2007 Datenbanken
Seite 334
Beispiel: SQL-Server Transaktion für die Banküberweisung
BEGIN TRANSACTION
UPDATE
konto
SET
kontostand = kontostand -100
WHERE
konto_nr = 4532168
UPDATE
konto
SET
kontostand = kontostand +100
WHERE
konto_nr = 1556432
COMMIT TRANSACTION
durch das COMMIT wird die
Transaktion abgeschlossen und die
Werte in die Datenbank geschrieben.
SS 2007 Datenbanken
Seite 335
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
SET
kontostand = kontostand +100
WHERE
konto_nr = 4532168
UPDATE
konto
SET
kontostand = kontostand -100
WHERE
konto_nr = 1556432
ROLLBACK TRANSACTION
durch das ROLLBACK wird die
laufende Transaktion zurückgesetzt.
SS 2007 Datenbanken
Seite 336
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.
SS 2007 Datenbanken
Seite 337
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.
SS 2007 Datenbanken
Seite 338
Beispiel Sicherungspunkte für Oracle
In Oracle lautet die Syntax
SET 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.
SS 2007 Datenbanken
Seite 339
Transaktionen und Sperren
Um die Konsistenz der Daten zu gewährleisten, gibt es das Transaktionskonzept. Eine Transaktion ist dabei eine Folge von Datenbankzugriffen
mit folgenden Eigenschaften:
•
Atomarität
Eine Tranksaktion ist unteilbar. Das DBMS stellt (mittels LoggingTechnik) sicher, dass eine Transaktion selbst im Fall eines
Systemabsturzes entweder komplett oder gar nicht durchgeführt
wird.
SS 2007 Datenbanken
Seite 340
•
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.
SS 2007 Datenbanken
Seite 341
•
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.
SS 2007 Datenbanken
Seite 342
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.
SS 2007 Datenbanken
Seite 343
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.
SS 2007 Datenbanken
Seite 344
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
SS 2007 Datenbanken
Seite 345
•
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.
SS 2007 Datenbanken
Seite 346
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.
SS 2007 Datenbanken
Seite 347
Sicherheitskonzept – Zugriffsrechte
Für ein DBMS ist ein gutes Benutzer und Berechtigungskonzept von
entscheidender Bedeutung.
In einem DBMS gibt es drei Konzepte
• Benutzerkonzept
• Rollenkonzept
• Privilegienkonzept.
SS 2007 Datenbanken
Seite 348
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.
SS 2007 Datenbanken
Seite 349
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.
SS 2007 Datenbanken
Seite 350
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
SS 2007 Datenbanken
Seite 351
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.
SS 2007 Datenbanken
Seite 352
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.
SS 2007 Datenbanken
Seite 353
Ä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'
SS 2007 Datenbanken
Seite 354
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-Server
zuzugreifen
exec sp_revokelogin 'domäne\name'
SS 2007 Datenbanken
Seite 355
Der Nutzer abcd kann nun auf den SQL-Server zugreifen
Als Standard Datenbank
wurde test festgelegt
SS 2007 Datenbanken
Seite 356
Er kann aber noch nicht auf seine Standarddatenbank zugreifen.
Für die einzelnen Datenbanken müssen die User angelegt werden.
SS 2007 Datenbanken
Seite 357
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.
SS 2007 Datenbanken
Seite 358
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.
SS 2007 Datenbanken
Seite 359
Der User abcd wurde für die Datenbank test angemeldet
Nun kann der User abcd auf
die Datenbank test zugreifen
SS 2007 Datenbanken
Seite 360
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.
SS 2007 Datenbanken
Seite 361
Der User abcd hat nach dem Anlegen keinerlei Privilegien auf der
Datenbank test.
Dem Nutzer abcd müssen Privilegien zugewiesen werden.
SS 2007 Datenbanken
Seite 362
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.
SS 2007 Datenbanken
Seite 363
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.
SS 2007 Datenbanken
Seite 364
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.
SS 2007 Datenbanken
Seite 365
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.
SS 2007 Datenbanken
Seite 366
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.
SS 2007 Datenbanken
Seite 367
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.
SS 2007 Datenbanken
Seite 368
Syntax für GRANT
GRANT Privileg,…|ALL [PRIVILEGES]
ON
tabellenname,…
TO
benutzername,….|rollenname
entfällt für
Datenbankprivilegien
[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.
SS 2007 Datenbanken
Seite 369
Syntax für REVOKE
REVOKE Privileg,…|ALL [PRIVLEGES]
ON
tabellenname,…
FROM benutzername,….|rollenname
entfällt für
Datenbankprivilegien
[CASCADE]
Falls CASCADE angegeben ist und der Rechteempfänger Rechte an
andere weitergegeben hat, werden auch diese entfernt.
SS 2007 Datenbanken
Seite 370
Syntax für DENY
DENY Privileg,…|ALL [PRIVILEGES]
ON
tabellenname,…
TO
benutzername,….|rollenname
entfällt für
Datenbankprivilegien
[CASCADE]
SS 2007 Datenbanken
Seite 371
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.
SS 2007 Datenbanken
Seite 372
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 mitarbeiter
TO rolle_test
exec sp_addrolemember 'rolle_test' , 'abcd'
DENY UPDATE ON mitarbeiter TO abcd
SS 2007 Datenbanken
Seite 373
Das Anlegen und Verwalten der User, Privilegien und Rollen über
das SQL Server Management Studio
1. Zugriff auf den SQL-Server
SS 2007 Datenbanken
Seite 374
Das Anlegen und Verwalten der User, Privilegien und Rollen über
das SQL Server Management Studio
2. Zugriff auf die Datenbanken
Über Eigenschaften des SQL-Users
in Sicherheit\Anmeldungen können
die Zugriffe auf die einzelnen
Datenbanken definiert werden.
SS 2007 Datenbanken
Seite 375
Das Anlegen und Verwalten der User, Privilegien und Rollen über
das SQL Server Management Studio
3. Rollen definieren
SS 2007 Datenbanken
Seite 376
Das Anlegen und Verwalten der User, Privilegien und Rollen über
das SQL Server Management Studio
4. Rechte den Rollen oder Usern zuweisen
SS 2007 Datenbanken
Seite 377
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 Datenbankprogrammierung 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.
SS 2007 Datenbanken
Seite 378
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.
SS 2007 Datenbanken
Seite 379
Kontrollstrukturen in Transact-SQL
•
Wie in jeder höheren Programmiersprache gibt es in Transact-SQL
Kontrollstrukturen. Unterschieden werden die zwei Kategorien Auswahlund Schleifenstruktur.
•
Repräsentiert werden die beiden Kategorien durch IF-ELSE und
WHILE.
SS 2007 Datenbanken
Seite 380
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)
SS 2007 Datenbanken
Seite 381
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
SS 2007 Datenbanken
Seite 382
Gespeicherte Prozeduren –
Stored Procedures
SS 2007 Datenbanken
Seite 383
Stored Procedures
•
Gespeicherte Prozeduren sind wichtige Bestandteile im Datenbankbetrieb. 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.
SS 2007 Datenbanken
Seite 384
•
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 ClientRechner. 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.
SS 2007 Datenbanken
Seite 385
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
SS 2007 Datenbanken
Seite 386
Ein Beispiel für eine einfache Stored Porcedure
CREATE PROCEDURE
AS
sp_mitarbeiterabt_11
SELECT
name, vorname
FROM
mitarbeiter
WHERE
abt_id = 11
ORDER BY
name
DESC
Um die Prozedur auszuführen wird der Befehl EXEC verwendet:
EXEC
sp_mitarbeiterabt_11
SS 2007 Datenbanken
Seite 387
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
SS 2007 Datenbanken
Seite 388
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
SS 2007 Datenbanken
Seite 389
Auch eine Gruppe von gespeicherten Prozeduren kann erstellt werden:
CREATE PROCEDURE
AS
sp_g_mitarbeiter; 1
SELECT
name, vorname
FROM
mitarbeiter
WHERE
abt_id = 11
GO
CREATE PROCEDURE
AS
sp_g_mitarbeiter; 2
SELECT
name,vorname, abt_name
FROM
mitarbeiter m
INNER JOIN
abteilung a
ON
m.abt_id = a.abt_id
INNER JOIN
standort s
ON
a.abt_sitz = s.abt_sitz
WHERE
s.stadt = 'München'
GO
SS 2007 Datenbanken
Seite 390
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
SS 2007 Datenbanken
Seite 391
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
SS 2007 Datenbanken
Seite 392
•
OUTPUT bedeutet, dass der Parameter ein Rückgabeparameter
darstellen soll.
•
VARYING bezieht sich auf die zurück gelieferte Datenmenge/Cursor.
SS 2007 Datenbanken
Seite 393
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.
SS 2007 Datenbanken
Seite 394
Um den Wert von myAvg zu verwenden, muss zunächst eine Variable
deklariert werden.
DECLARE @durchschnitt real
Danach kann die Prozedur mit den Werten ausgeführt werden:
EXEC sp_myDurchschnitt3 10, 14, 7, @durchschnitt OUTPUT
Jetzt kann das Ergebnis angezeigt werden:
SELECT 'Der Durchschnitt ist: ',@durchschnitt
SS 2007 Datenbanken
Seite 395
Bei Verwendung von default-Werten sollte die Reihenfolge 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.
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.
EXEC sp_myDurchschnitt_d @durchschnitt OUTPUT, 4
SS 2007 Datenbanken
Seite 396
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
SS 2007 Datenbanken
Seite 397
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
SS 2007 Datenbanken
Seite 398
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.
SS 2007 Datenbanken
Seite 399
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
SS 2007 Datenbanken
Seite 400
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.
SS 2007 Datenbanken
Seite 401
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]
SS 2007 Datenbanken
Seite 402
Ein komplexeres Beispiel für eine Stored Procedure
artikel
gruppe
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
SS 2007 Datenbanken
Seite 403
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
SS 2007 Datenbanken
Seite 404
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
SS 2007 Datenbanken
Seite 405
Einfügen des Datensatzes 'Keyboard-Super', 35.6, 'Tastatur'
DECLARE @hilf int
EXEC
@hilf = sp_insert_artikel 'Keyboard-Super',35.6,'Tastatur'
Einfügen des Datensatzes 'easyKlick', 10. ,'Maus'
DECLARE @hilf int
EXEC
@hilf = sp_insert_artikel 'easyKlick',10.,'Maus'
SS 2007 Datenbanken
Seite 406
Demonstration der Übersetzungszeit für Prozeduren anhand
einer mehrfach aufgerufenen Stored Procedure
Beispiel
SS 2007 Datenbanken
Seite 407
Anhand der Prozedur sp_messwert wird der Zeitaufwand für einen
Übersetzungsprozess simuliert
-- 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)
SS 2007 Datenbanken
Seite 408
Mehrfacher Aufruf der Testprozedur mit Zeitmessung
SS 2007 Datenbanken
Seite 409
Laufzeit-Ergebnisse
Laufzeit mit Prozeduraufruf, aber ohne Recompile
Laufzeit mit Prozeduraufruf, aber mit Recompile
SS 2007 Datenbanken
Seite 410
Datenbank-Trigger
SS 2007 Datenbanken
Seite 411
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.
SS 2007 Datenbanken
Seite 412
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
SS 2007 Datenbanken
Seite 413
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.
SS 2007 Datenbanken
Seite 414
Ein einfaches Beispiel für einen Trigger
CREATE TRIGGER t_addupmitarbeiter
ON mitarbeiter
FOR INSERT,UPDATE
AS PRINT cast(@@rowcount AS varchar)+ ' Zeilen wurden geändert'
Ein Update oder Insert auf die Tabelle mitarbeiter löst den Trigger
aus:
UPDATE
mitarbeiter
SET
gehalt = gehalt + 100
WHERE
abt_id = 11
Ergebnis des Triggers
SS 2007 Datenbanken
Seite 415
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.
SS 2007 Datenbanken
Seite 416
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 .
SS 2007 Datenbanken
Seite 417
Beispiel: Erstellung eines Triggers, der einen Datensatz 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;
SS 2007 Datenbanken
Seite 418
Beispiel: Erstellung eines eigenen Autoincrements
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
SS 2007 Datenbanken
Seite 419
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.
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!'
SS 2007 Datenbanken
Seite 420
Auslösen des Triggers
DELETE
FROM
kunde
WHERE
kdnr = 1
löst die folgende Fehlermeldung aus
DELETE
FROM
kunde
WHERE
kdnr = 2
löscht den Datensatz
SS 2007 Datenbanken
Seite 421
Beispiel: Erstellen des Triggers, der die Preise der gelöschten
Artikel in einer separaten Tabelle aufsummiert.
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
PRINT 'Dieser Befehl ist nur für einzelne Zeilen zulässig!'
SS 2007 Datenbanken
Seite 422
Trigger können eingesetzt werden um die referentielle Integrität zu
gewährleisten.
Beispiel:
kunde
nur dieser Kunde
darf gelöscht
werden
auftrag
Ein Trigger soll gewährleisten, dass nur Kunden gelöscht werden
dürfen, die keine Aufträge in der Tabelle Auftrag haben.
SS 2007 Datenbanken
Seite 423
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
PRINT 'Dieser Befehl ist nur für einzelne Zeilen zulässig!'
SS 2007 Datenbanken
Seite 424
Ein Löschen des Kunden mit der kdnr = 1 ist nicht möglich
DELETE
FROM
kunde
WHERE
kdnr = 1
SS 2007 Datenbanken
Seite 425
Sicherung von Datenbanken
SS 2007 Datenbanken
Seite 426
•
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 ITSicherheitsmanagement.
•
Die Art der Datensicherung ist von dem jeweiligen Datenbanksystem
abhängig.
•
Fehlende Datensicherung können nach Auffassung der Gerichte
juristische Konsequenzen nach sich ziehen.
SS 2007 Datenbanken
Seite 427
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
SS 2007 Datenbanken
Seite 428
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
SS 2007 Datenbanken
Seite 429
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.
SS 2007 Datenbanken
Seite 430
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.
SS 2007 Datenbanken
Seite 431
•
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.
SS 2007 Datenbanken
Seite 432
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.
SS 2007 Datenbanken
Seite 433
•
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.
SS 2007 Datenbanken
Seite 434
•
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.
SS 2007 Datenbanken
Seite 435
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.
SS 2007 Datenbanken
Seite 436
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?
SS 2007 Datenbanken
Seite 437
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.
SS 2007 Datenbanken
Seite 438
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.
SS 2007 Datenbanken
Seite 439
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.
SS 2007 Datenbanken
Seite 440
Die Datenbank im Netz
Verteilte Datenbanken, Replikation, ODBC
SS 2007 Datenbanken
Seite 441
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)
SS 2007 Datenbanken
Seite 442
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.
SS 2007 Datenbanken
Seite 443
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ügbarkeitsOptimierung.
SS 2007 Datenbanken
Seite 444
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
SS 2007 Datenbanken
Seite 445
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.
SS 2007 Datenbanken
Seite 446
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.
SS 2007 Datenbanken
Seite 447
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.
SS 2007 Datenbanken
Seite 448
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.
SS 2007 Datenbanken
Seite 449
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.
SS 2007 Datenbanken
Seite 450
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.
SS 2007 Datenbanken
Seite 451
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.
SS 2007 Datenbanken
Seite 452
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.
SS 2007 Datenbanken
Seite 453
•
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.
SS 2007 Datenbanken
Seite 454
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.
SS 2007 Datenbanken
Seite 455
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.
SS 2007 Datenbanken
Seite 456
Das Two Phase Commit Protokoll
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.
SS 2007 Datenbanken
Seite 457
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.
SS 2007 Datenbanken
Seite 458
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
Anwendung
Anwendung
ODBC-Schnittstelle
DBS
DBS
DBS
SS 2007 Datenbanken
Seite 459
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.
SS 2007 Datenbanken
Seite 460
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 Datenbankfunktionen zugegriffen werden.
Teilweise problematische Anwendungen durch die
unterschiedliche SQL-Syntax der verschiedenen DBMSAnbieter.
SS 2007 Datenbanken
Seite 461
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
SS 2007 Datenbanken
Seite 462
ODBC einrichten
für einen MS SQL-Server
SS 2007 Datenbanken
Seite 463
Datenquelle
hinzufügen
SS 2007 Datenbanken
Seite 464
Es folgen weitere
Treiberspezifische Formulare,
die hier nicht aufgeführt werden.
Diese müssen beim SQLServer vorerst nicht verändert
werden.
SS 2007 Datenbanken
Seite 465
Damit ist die ODBC Vebindung
vom Client zur Datenbank
hergestellt.
SS 2007 Datenbanken
Seite 466
Praktischer Einsatz von Data-Warehouse-Systemen
in Großprojekten
SS 2007 Datenbanken
Seite 467
Ist-Situation in Großunternehmen
•
Verschiedenste operative IT-Systeme und Datenbanken, die die
Anforderungen des entsprechenden Bereichs abdecken.
•
Historisch bedingter ‚Wildwuchs‘ .
M A R K O -D B
A nbieter F lug, C ar,
H otel
A nbieter
T ouristik
div. L isten aus
M ythos
PM 5
SN
div. D ateien der
tour. A nbieter
exciting
MA
AR
AR
TK
EB
amadeus
Bahn
PM 3
AB
PR
div. D ateien von
AN
div. D ateien aus
A SP
A nbieter F ähren
PB
SU
KC
RW
SZ
GF
PM 6
CP
PW
SA P
TM
CM
AF
EI
AA
ST A D I-D B
CO
AT
R egionalbüros
VK
PM 1
DER
KM
AI
IO
RSB-K ettenZ e
ntralen
D atei erzeugt
durch SBT
PM 7
KS
VA
BT X
PI
EA
Spedition
PD
T IC
SRZ
PM 2
D atei erzeugt
durch PM
A nbieter
V E RS
=> Übergreifende Analysen sind kaum möglich
SS 2007 Datenbanken
Seite 468
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.
SS 2007 Datenbanken
Seite 469
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.
SS 2007 Datenbanken
Seite 470
Basis-Architektur eines Data-Warehouse-Systems
Data-Warehouse-System
Bereich der Datenbeschaffung
Datenquelle(n)
Extraktion
Arbeitsbereich
Laden
Basisdatenbank
Laden
Data
Warehouse
Analyse
Transformation
Data-WarehouseManager
Monitor
MetadatenManager
Datenfluss
Kontrollfluss
Repository
SS 2007 Datenbanken
Seite 471
Analyse
Einer der wichtigsten Analyseansätze im Data Warehouse Umfeld ist
OLAP (Online Analytical Processing) (Codd 1993).
•
Analyseansatz, der die dynamische, multidimensionale Analyse von
Daten 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.
SS 2007 Datenbanken
Seite 472
Das Multidimensionale Datenmodell
Quartal
Ze
itr
au
m
Jahr
Fakt, Kennzahl
Produkt
(z.B. Umsatz)
Kategorie
Navigieren im Würfel durch
Artikel
- drill down / roll-up
Region
Filiale
Dimension
Stadt
- drill across
- slice & dice
Bundesland
SS 2007 Datenbanken
Seite 473
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)
SS 2007 Datenbanken
Seite 474
Beispiel ROLAP: Star-Schema
Produkt
Zeit
Zeit_ID
Zeit_ID
Tag
Tag
Monat
Monat
Quartal
Quartal
Jahr
Jahr
Geographie
Geo_ID
Geo_ID
Stadt
Stadt
Bundesland
Bundesland
Land
Land
Kontinent
Kontinent
......
Verkauf
Produkt_ID
Produkt_ID
Zeit_ID
Zeit_ID
Geo_ID
Geo_ID
Verkäufer
VerkäuferID
ID
Verkäufe
Verkäufe
Umsatz
Umsatz
......
Produkt_ID
Produkt_ID
Produktname
Produktname
Beschreibung
Beschreibung
Kategorie
Kategorie
......
Verkäufer
Verkäufer_ID
Verkäufer_ID
Verkäufername
Verkäufername
Position
Position
......
SS 2007 Datenbanken
Seite 475
Metadaten
Alle Informationen, die den Aufbau, die Wartung und die Administration des DataWarehouse 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.
SS 2007 Datenbanken
Seite 476
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.
SS 2007 Datenbanken
Seite 477
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.
SS 2007 Datenbanken
Seite 478
Bildung von Data Marts
•
spezielle Teildatenmenge eines Data Warehouses, z.B. für eine bestimmte
Abteilung des Unternehmens.
Analyse A
Analyse B
Analyse C
Analyse D
Data Marts
Data
Warehouse
Laden
SS 2007 Datenbanken
Seite 479
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
SS 2007 Datenbanken
Seite 480
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 Data
Warehouse nicht geeignet.
Im Data Warehouse werden Bitmap-Indizes eingesetzt.
Vorteil: Daten mit großen Wertebereichen werden speicherplatzeffizient
indiziert und Bereichsanfragen schneller bearbeitet.
SS 2007 Datenbanken
Seite 481
Standard-Bitmap-Index
Bsp.: Dimension Kunde, Attribut Geburtsmonat M.
Ein Bitmap-Index soll auf Attribut ‚Geburtsmonat‘ erzeugt werden.
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
Bei Anfrage nach allen Tupeln mit Geburtsmonat April wird der BitmapVekor 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.
SS 2007 Datenbanken
Seite 482
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.
SS 2007 Datenbanken
Seite 483
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
B 11
B 10
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).
SS 2007 Datenbanken
Seite 484
2. Partitionierung
Bildet Ergänzung zu den Indexverfahren. Stammt aus dem Bereich
verteilter und paralleler Datenbanksysteme.
Horizontale
Partitionierung
Master-Tabelle
Vertikale
Partitionierung
SS 2007 Datenbanken
Seite 485
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-MONYYYY‘),
PARTITION nach_2002
VALUES LESS THAN (MAXVALUE))
SS 2007 Datenbanken
Seite 486
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.
SS 2007 Datenbanken
Seite 487
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/
SS 2007 Datenbanken
Seite 488
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.
SS 2007 Datenbanken
Seite 489
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.
SS 2007 Datenbanken
Seite 490