Wirtschaftsinformatik II

Wirtschaftsinformatik II
Organisatorisches
Prof. Dr. Peter Chamoni
Dipl.-Math. Tanja Bley
Wintersemester 2008/2009
Mercator School of Management
Lehrstuhl für Wirtschaftsinformatik und Operations Research
Prof. Dr.
Peter Chamoni
Prof. Dr. Peter
Chamoni
- Wirtschaftsinformatik II - Wintersemester 2008/2009
1
Organisatorisches
Kontakt
Dozent:
Dipl.-Math. Tanja Bley
Ansprechpartner:
Dipl.-Math. Tanja Bley
[email protected]
LF 219
Website:
http://www.msm.uni-due.de/wi
Download der
Unterlagen:
Lehre – Lehrveranstaltungen – Wirtschaftsinformatik II
Klausur:
voraussichtlich 10.12.2008
Beachten Sie bitte die Bekanntmachungen des
Prüfungsamtes.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
2
Organisatorisches
Vorlesungsunterlagen
• Die Folien stellen eine Vorlesungsbeilage dar.
kein Skript
• Alte Klausuren sind am Lehrstuhl (Herrn Dipl. Ök. Stefan Krebs,
LF 212) erhältlich.
• Mit freundlicher Genehmigung von Herrn Dr. Steffen Stock
wurden einige Folien aus seiner Veranstaltung übernommen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
3
Organisatorisches
Microsoft Access 2003
• Download: http://www.uni-due.de/zim/services/software/msdnaa/
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4
Wirtschaftsinformatik II
Kapitel 1: Grundlagen
Prof. Dr. Peter Chamoni
Dipl.-Math. Tanja Bley
Wintersemester 2008/2009
Mercator School of Management
Lehrstuhl für Wirtschaftsinformatik und Operations Research
Prof. Dr.
Peter Chamoni
Prof. Dr. Peter
Chamoni
- Wirtschaftsinformatik II - Wintersemester 2008/2009
5
Gliederung
1 Grundlagen
2 Entity-Relationship-Modell
3 Relationales Datenmodell
4 SQL
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
6
Gliederung
1 Grundlagen
1.1 Abgrenzung
1.2 Begriffliches
1.3 Internes Schema
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
7
1.1 Abgrenzung
Daten – Information – Wissen
• Daten
Folge von Zeichen [syntaktische Ebene]
• Information
Bedeutung und Interpretation der Daten [semantische Ebene]
• Zweckorientiertes Wissen
zielgerichtete Information (in der BWL)
• Wissen
Kontextbezug der Informationen [pragmatische Ebene]
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
8
Beispiele zu Folie 8
-Daten
Syntaktische Dimension
RB3720W1703G7 (Folge von Buchstaben/Zahlen)
-Informationen
Semantische Dimension (Bedeutung hinzufügen)
Regionalbahn 3720 fährt werktags um 17:03 von leis 7 ab.
-Wissen
Pragmatischen Dimension
Bisher fuhr die Regionalbahn 3720
Um 17:10 ab; umsteigende aus dem Zug IR 317 erreichen den Anschluss
nun nicht mehr.
1.1 Abgrenzung
Begriffshierarchien
Herr Müller wohnt
in Hamburg. Er ist
potenzieller
Kunde …
Müller ist ein
Nachname
Wissen
Pragmatik
Vernetzung
Information
Semantik
Kontext
Daten
Müller
Syntax
„A“, …, „e“,
„l“, „M“, …
Zeichen
[Bodendorf (2001), S. 35]
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
9
Gliederung
1. Grundlagen
1.1 Abgrenzung
1.2 Begriffliches
1.3 Internes Schema
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
10
1.2 Begriffliches
Daten – Datenbank
• Daten [DIN 44 300]
Angaben über Sachverhalte und Vorgänge aufgrund
bekannter oder unterstellter Abmachungen in einer maschinell
verarbeitbaren Form.
• Datenbank [Zehnder (2002), S. 35]
selbständige, auf Dauer und für den flexiblen und sicheren
Gebrauch ausgelegte Datenorganisation
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
11
1.2 Begriffliches
Datenbankarchitekturmodell (ANSI/X3/SPARC)
Abstraktionsebene
externe Ebene
externes …
Schema 1
externes
Schema n
zugeordnete Operationen
Datenabfrage
Datenmanipulation
konzeptionelle Ebene
interne Ebene
Datendefinition
Datenadministration
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
12
S.12 Interne Ebene
-Definition interner Schemata
-Beschreibung der physikalischen Speicherstrukturen
der Datenbank
-Zugriffspfade für die Datenbank
Konzeptionelle Ebene
-Definition eines konzeptionellen Schemas
-Verwendung eines logischen Datenmodells
-Beschreibung von
-Objekten
-Beziehungen unter den Objekten
-Datentypen ( numerisch?; alphanumerisch?)
Externe Ebene
-Beinhaltet eine Reihe von externen Schemata
-Umfassen alle individuellen Sichten ( view ) -auf die Datenbank
(Topmanagement hat möglk. auf alle Daten zu zugreifen.
-Sicherheitspolitik
-Performanceaspekte (Eingrenzung, wenn man nur auf einige Daten zugreifen
will
1.2 Begriffliches
Diskurswelt – Schema
• Diskurswelt
zweckdienliche Abstraktion eines Ausschnitts der zu
beschreibenden Realität
• Schema
Erfassung allgemeiner Gesetzmäßigkeiten, die für die
Diskurswelt immer gelten.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
13
1.2 Begriffliches
Datenmodell – Datenmodellierung
• Datenmodell
Formale Beschreibung des Schemas, z.B. in Form eines
Diagramms oder einer Datenstruktur.
Strukturierte Darstellung der Daten der Diskurswelt mit
einem formalen Beschreibungsmittel
• Datenmodellierung
Prozess, der sicherstellen soll, dass eine Datenbasis zu
jedem Zeitpunkt ein korrektes Abbild der Diskurswelt
wiedergibt.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
14
1.2 Begriffliches
Ebenen der Modellierung
• Klassifikation der Datenmodelle anhand ihrer Nähe zur
Realwelt
- Semantisches Datenmodell
•
•
•
Brücke zwischen Realwelt und dem logischen
Datenmodell
Losgelöst vom einzusetzenden Datenbanksystem
Realitätsausschnitt wird abstrahierend in einem
Modell abgebildet
- Logisches Datenmodell
•
•
Ebenfalls unabhängig von der physischen
Repräsentation
Ausrichtung an der für die Speicherung
einzusetzenden Datenbanktechnologie
Logisches
Datenmodell
Physisches
Datenmodell
- Physisches Datenmodell
•
Semantisches
Datenmodell
Aspekte der physischen Speicherung und
Speicheroptimierung
15
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
1.2 Begriffliches
Betrachtungsebenen
Übergabe
1. Reale Welt,
Teile der realen Welt:
2. Informationen über Teile
der realen Welt, Modelle:
Bibel
3. Logisches
Datensystem:
Buch
an
H. Meier
Ausleiher
4. Physisches
Datensystem:
Physische Datensätze und Dateien
5. Computerspeicherung
(Hardware + Software)
Speichermedien und deren Inhalt
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
16
1.2 Begriffliches
Datenbank-Management-System (DBMS)
• Datenbank-Management-System (DBMS)
Software, die die einheitliche Beschreibung und sichere
Bearbeitung einer Datenbank ermöglicht.
• Ein DBMS garantiert
- die Korrektheit der Daten durch die Überprüfung von
Konsistenzregeln,
- die Sicherheit der Daten, z.B. bei fehlerhaften Abläufen einzelner
Anwendungen oder bei Systemzusammenbrüchen,
- den Schutz der Daten vor unberechtigten Zugriffen und
Manipulationen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
17
1.2 Begriffliches
Transaktionskonzept (I)
• Transaktion
Folge von Datenbankoperationen, die hinsichtlich gewisser
Integritätsanforderungen atomare Einheiten darstellen. Sie
lässt die Datenbank in konsistentem Zustand zurück, wenn
diese vor Beginn der Transaktion schon konsistent war.
• Beispiel
Ausführung einer Umbuchung eines Betrages zwischen
zwei Konten X und Y.
Hierbei sei KX der Kontostand des Kontos X, KY der
Kontostand des Kontos Y.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
18
1.2 Begriffliches
Transaktionskonzept (II)
•
Folgende Datenbankoperationen werden hierzu durchgeführt:
1.
2.
3.
4.
Lesen des Kontostandes von X:
Schreiben des neuen Kontostandes von X:
Lesen des Kontostandes von Y:
Schreiben des neuen Kontostandes von Y:
-
Temporäre Inkonsistenz der Datenbank nach der zweiten
Datenbankoperation.
Konsistenz der Datenbank ist erst nach der letzten
Datenbankoperation wieder hergestellt.
Sicherstellen, dass die vier Datenbankoperationen der
Transaktion logisch zusammen ausgeführt werden.
-
KX
KXKY
KY+
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
19
1.2 Begriffliches
Transaktionskonzept (III)
•
Folgende Bedingungen müssen durch ein DMBS
gewährleistet werden (ACID-Prinzip):
-
Atomarität (Atomarity)
•
•
-
Transaktionen werden entweder vollständig oder gar nicht
durchgeführt.
Bei Störungen vor Abschluss der Transaktion sind alle
Veränderungen, die während dieser Transaktion durchgeführt
wurden, rückgängig zu machen.
Konsistenz (Consistency)
•
•
Nach dem Ausführen einer Transaktion muss die Datenbank in
widerspruchsfreiem Zustand sein. (Integritätsbedingungen müssen
erfüllt sein)
Während einer Transaktion darf sich die Datenbank temporär in
einem inkonsistenten Zustand befinden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
20
1.2 Begriffliches
Transaktionskonzept (IV)
- Isolation (Isolation)
•
•
Transaktionen werden isoliert von anderen potenziell parallelen
Transaktionen ausgeführt.
Das Ergebnis einer Transaktion ist erst nach deren Ende für andere
Transaktionen sichtbar.
- Persistenz (Durability)
•
Erfolgreich abgeschlossene Transaktionen können nur durch eine
weitere Transaktion rückgängig gemacht werden.
21
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
1.2 Begriffliches
Datenbanknutzer
professioneller
Benutzer
Vorbereitetes
Anw‘progr.
gelegentlicher
Benutzer
Vorbereitetes
Anw‘progr.
spezieller
Benutzer
Freie
Abfragemöglichkeit
Datenbank-Management-System
DBMS
Datenbasis
Datenbank
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
22
Gliederung
1. Grundlagen
1.1 Abgrenzung
1.2 Begriffliches
1.3 Internes Schema
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
23
1.3 Internes Schema
Beispiel
• Beispiel: Bibliotheksverwaltung
• Problem: Schnelles Wiederfinden abgespeicherter Daten
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
24
1.3 Internes Schema
Dateien
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
25
1.3 Internes Schema
Datei- und Speichertypen
• Sequentielle Dateien
- Zugriff auf Datensätze nur in der gespeicherten Reihenfolge
- Auf sequentiellen Speichern (z.B. Magnetband)
- Ebenfalls möglich auf Direktzugriffsspeichern (z.B. Magnetplatte,
optische Speicherplatte)
• Dateien mit direktem Zugriff
- Auf jeden Datensatz kann bei Kenntnis seiner Adresse direkt
zugegriffen werden.
- Nur auf Direktzugriffsspeichern
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
26
1.3 Internes Schema
Suchen in Dateien
Sequentielle
Dateien
Dateien
mit
direktem
Zugriff
Sequentielle
Suche
Sequentielle
Organisation
Index als
Hilfsmittel
Indizierte
Organisation
Algorithmus als
Hilfsmittel
Gestreute
Organisation
27
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
1.3 Internes Schema
Indizierte Organisation
Indexdatei
Hauptdatei
Schlüssel
AV
Date C.J.
2
1
4711 Martin J.
Application Devel…
Kent W.
3
2
4812 Date C.J.
An Introduction …
Lucas H.C.
4
3
1907 Kent W.
Data and Reality
Martin J.
1
4
1001 Lucas H.C. Information Systems
…
…
…
RA INR*
…
Autor
Titel
…
…
* INR = Inventarnumner, AV = Adressverweis, RA = Relative Adresse
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
28
1.3 Internes Schema
Gestreute Organisation (Hash)
Codd, E.F.
Kent, W.
Date, C.J.
RA INR*
Algorithmus
Autor
Titel
1
4711 Martin J.
Application Devel…
2
4812 Date C.J.
An Introduction …
3
1907 Kent W.
Data and Reality
4
1001 Lucas H.C. Information Systems
…
…
…
…
Erzeugt die Adresse
aus dem Schlüssel
* INR = Inventarnumner, RA = Relative Adresse
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
29
1.3 Internes Schema
Suchen in indizierten Dateien
• Unsortierter Index
• Physisch sortierter Index
- Sortierreihenfolge der Schlüssel entspricht der Reihenfolge der
Sätze im Speicher
- Binäres Suchen
- m-Wege-Suchen
• Logisch sortierter Index
- Reihenfolge der Sätze ist durch Zeiger bestimmt, nicht durch die
Reihenfolge im Speicher
- Ketten
- Bäume
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
30
1.3 Internes Schema
Binäres Suchen (I)
• Durch fortgesetztes Halbieren des Suchbereichs
• Annahme, dass das gesuchte Element in der Mitte des
Indexbereiches liegt und Vergleich des gesuchten Elements
mit dem Element in der Mitte.
• Ist dieses Element kleiner als das gesuchte Element, wird die
Suche in der oberen Hälfte des Indexes fortgesetzt.
• Ist dieses Element größer als das gesuchte Element, wird die
Suche in der unteren Hälfte des Indexes fortgesetzt.
• Die Suche wird so lange fortgesetzt bis das gesuchte Element
gefunden wurde oder die Länge des Suchbereichs = 1.
31
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
1.3 Internes Schema
Binäres Suchen (II)
•
Zum Beispiel Suche nach Kotler P.
1. … Vergleiche das gesuchte Element
mit dem Element in der Mitte
Kotler P. < Lucas H. C.
2. … Gehe zur Mitte der oberen Hälfte
Kotler P. > Hansen H.R.
3. … Gehe zur Mitte des zweiten
Viertels.
Gefunden!
•
65.535 Elemente: höchstens 16
Suchschritte
2
3
1
Schlüssel
AV
Codd E.F.
8
Date C.J.
2
Hansen H.R.
10
Kent W.
3
Kotler P.
9
Lucas H.C.
4
Martin J.
1
Rockart J.
6
Sprague R.
7
Turban E.
11
Zachmann R.
5
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
32
1.3 Internes Schema
m-Wege-Suchen
• Die Indexdatei wird in Blöcke konstanter Länge eingeteilt.
• Gehe zum letzten Element des ersten Blocks.
• Falls der Suchschlüssel größer ist als dieses Element: Gehe
zum nächsten Block.
• Andernfalls befindet sich das gesuchte Element in diesem
Block.
• Verwende eine andere Suchmethode (z.B. sequentielles oder
binäres Suchen) um das Element in diesem Block zu finden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
33
1.3 Internes Schema
m-Wege-Suchen in hierarchischen Indexdateien
RA
Schlüssel
AV
1
Codd E.F.
8
2
Date C.J.
2
Schlüssel
AV
3
Hansen H.R.
10
Codd E.F.
1
4
Kent W.
3
Kotler P.
5
5
Kotler P.
9
9
6
Lucas H.C.
4
7
Martin J.
1
8
Rockart J.
6
9
Sprague R.
7
10
Turban E.
11
11
Zachmann R.
5
Sprague R.
Zweistufiger
hierarchischer Index
Hauptdatei
AV = Adressverweis, RA = Relative Adresse
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
34
1.3 Internes Schema
Ketten
•
•
•
•
•
Sätze sind nicht physisch sortiert.
Jeder Satz enthält Zeiger zum logisch folgenden Satz.
Zeiger, der auf den ersten Satz zeigt, heißt Anker.
Zeiger des letzten Satzes ist eine Endemarke.
Einfügen neuer Sätze physisch am Ende der Datei. Zeiger
des nächst kleineren Elementes (Vorgänger) wird auf das neu
eingefügte gerichtet.
• Zeiger des neuen Elementes wird auf das nächst größere
Element gesetzt.
• Beim Löschen eines Datensatzes wird nur der Zeiger des
Vorgängerdatensatzes verändert, und der Speicherplatz des
gelöschten Datensatzes überschreibbar gemacht.
- Lücken bleiben, Reorganisation erforderlich
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
35
1.3 Internes Schema
Physische Repräsentation von Ketten
Physische
Schlüssel
Nachf.
Sortierung
AV
1
Sprague R.
5
7
2
Date C.J.
4
2
3
Kent W.
6
3
4
Hansen H.R.
3
10
5
Turban E.
10
11
Kotler P.
11
9
Codd E.F.
2
8
Martin J.
9
1
9
Rockart J.
1
6
10
Zachmann R.
--
5
11
Lucas H.C.
8
4
Logische
6
Reihenfolge
durch Zeiger zum 7
folgenden Satz 8
Hauptdatei
Nachf. = Nachfolger, AV = Adressverweis
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
36
1.3 Internes Schema
Bäume
• Komponenten
- Knoten: Enthalten Anwendungsinformation
- Kanten: Enthalten Adressinformation
• Es gibt genau einen Knoten, der keinen Vorgänger hat:
Wurzel
• Jeder Knoten, außer der Wurzel, hat genau einen
unmittelbaren Vorgänger.
• Zu jedem Nichtwurzelknoten gibt es genau einen Weg von
der Wurzel zu diesem Knoten.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
37
1.3 Internes Schema
Logische Struktur binärer Bäume
Lucas
LucasH.C.
H.C.
Linker
Nachfolger
Hansen
HansenH.R.
H.R.
Date
DateC.J.
C.J.
Codd
CoddE.F.
E.F.
Kotler
KotlerP.
P.
Kent
KentW.
W.
Rechter
Nachfolger
Sprague
SpragueR.
R.
Martin,
Martin,J.J.
Rockart
RockartJ.J.
Turban
TurbanE.
E.
Zachmann
ZachmannR.
R.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
38
1.3 Internes Schema
Physische Repräsentation binärer Bäume
Schlüssel
KF li
KF re
AV
1
Sprague R.
8
5
7
2
Date C.J.
7
--
2
3
Kent W.
--
--
3
4
Hansen H.R.
2
6
10
5
Turban E.
--
10
11
6
Kotler P.
3
--
9
7
Codd E.F.
--
--
8
8
Martin J.
--
9
1
9
Rockart J.
--
--
6
10
Zachmann R.
--
--
5
11
Lucas H.C.
4
1
4
Hauptdatei
Kf li = linkes Kettenfeld, KF re = rechtes Kettenfeld, AV = Adressverweis
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
39
1.3 Internes Schema
Suchen in sortierten binärer Bäume
Lucas
LucasH.C.
H.C.
? „Kent“
<
Hansen
HansenH.R.
H.R.
Sprague
SpragueR.
R.
>
Date
DateC.J.
C.J.
Kotler
KotlerP.
P.
Martin
MartinJ.
J.
Turban
TurbanE.
E.
<
Codd
CoddE.F.
E.F.
Kent
KentW.
W.
Rockart
RockartJ.J.
Zachmann
ZachmannR.
R.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
40
1.3 Internes Schema
Vergleich von Dateiorganisationsformen
Speicher
Suchstrategien
Reorganisation
Primär/Sekundärschl.
Nein
Kein
Schlüsselzugriff möglich
Sequentiell
oder direkt
adressierbar
Sequentiell
Indiziert
Direkt
adressierbar
Sequentiell,
binär,
m-Wege
Nein
Ja
Primärschlüssel,
Sekundärschlüssel
Hash
Direkt
adressierbar
Suche nur
bei Kollision
notwendig
Nein
Primärschlüssel
Sequentiell
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
41
Wirtschaftsinformatik II
Kapitel 2: Entity-Relationship-Modell
Prof. Dr. Peter Chamoni
Dipl.-Math. Tanja Bley
Wintersemester 2008/2009
Mercator School of Management
Lehrstuhl für Wirtschaftsinformatik und Operations Research
Prof. Dr.
Peter Chamoni
Prof. Dr. Peter
Chamoni
- Wirtschaftsinformatik II - Wintersemester 2008/2009
42
Gliederung
1 Grundlagen
2 Entity-Relationship-Modell
3 Relationales Datenmodell
4 SQL
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
43
Gliederung
2. Entity-Relationship-Modell
2.1 Grundlagen
2.2 Beziehungen
2.3 Symbole
2.4 Entwurf eines E-R-Modells
2.5 Beispiele
2.6 Übungsaufgaben
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
44
2.1 Grundlagen
Entity-Relationship-Modell
• Das Entity-Relationship-Modell ist von einer
Implementierung in einem bestimmten
Datenbanksystem unabhängig
[Semantisches Datenmodell].
Semantisches
Datenmodell
• Graphische Modellierungstechnik zur
Darstellung von Objekten und ihrer
Beziehungen untereinander.
Logisches
Datenmodell
• In der Praxis weit verbreitet.
Physisches
Datenmodell
• Vorteil: Einfache Überführung von E-RModellen in relationale Datenmodelle
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
45
2.1 Grundlagen
Entity-Relationship-Modell
• Komponenten eines E-R-Modells
- Entitäten („entities“)
- Beziehungen („relationships“) zwischen den Entitäten
- Attribute
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
46
2.1 Grundlagen
Entitäten und Entitätsmengen
• Eine Entität ist eine individuelle Ausprägung der Diskurswelt,
die eindeutig identifiziert werden kann (z. B. eine bestimmte
Person oder ein bestimmtes Produkt).
• Entitäten können durch ihre Eigenschaften, die sog. Attribute,
beschrieben werden.
• Entitäten mit den gleichen Attributen aber unterschiedlichen
Attributwerten können zu Entitätsmengen mit einem
eindeutigen Namen zusammengefasst werden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
47
2.1 Grundlagen
Beziehungen und Beziehungsmengen
• Zwischen den Entitäten bestehen Beziehungen, die durch
Attribute beschrieben werden können.
• Eine Entitätsmenge ist über eine Beziehungsmenge mit
einer anderen Entitätsmenge verbunden.
Es dürfen nie direkt zwei Entitätsmengen und auch nicht zwei
Beziehungsmengen direkt miteinander verknüpft werden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
48
2.1 Grundlagen
Attribute und Schlüsselattribute
• Attribute beschreiben die relevanten Eigenschaften von
Entitäten oder Beziehungen.
• Identifizierende Attribute (Schlüsselattribute) kennzeichnen
eindeutig eine bestimmte Entität einer Entitätsmenge.
• Schlüssel = Minimale Menge von Attributen, die jede
einzelne Entität eindeutig identifiziert.
• Bei einem abgeleiteten Attribut wird dieses aus den Werten
anderer Attribute bestimmt.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
49
Gliederung
2. Entity-Relationship-Modell
2.1 Grundlagen
2.2 Beziehungen
2.3 Symbole
2.4 Entwurf eines E-R-Modells
2.5 Beispiele
2.6 Übungsaufgaben
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
50
2.2 Beziehungen
Kardinalitäten von Beziehungstypen
• Einfacher Beziehungstyp (1)
Die Entitäten der Entitätsmenge 2 sind genau einer Entität aus der
Entitätsmenge 1 zugeordnet.
• Konditioneller Beziehungstyp (c)
Die Entitäten der Entitätsmenge 2 sind höchstens einer Entität aus
der Entitätsmenge 1 zugeordnet.
• multipler Beziehungstyp (m)
Die Entitäten der Entitätsmenge 2 sind mindestens einer Entität
aus der Entitätsmenge 1 zugeordnet.
• multipler-konditioneller Beziehungstyp (mc)
Die Entitäten der Entitätsmenge 2 sind beliebig vielen Entitäten aus
der Entitätsmenge 1 zugeordnet.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
51
2.2 Beziehungen
Kardinalitäten von Beziehungstypen – Beispiele (I)
• 1:1-Beziehung (einfach-einfache Beziehung)
- Zu einer Person gehört
genau eine Geburtsurkunde.
Person
Geburtsurkunde
gehört
- Eine Geburtsurkunde
gehört zu genau einer
Person.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
52
2.2 Beziehungen
Kardinalitäten von Beziehungstypen – Beispiele (II)
• 1:m-Beziehung (einfach-komplexe Beziehung)
- Ein Mitarbeiter gehört
zu genau einer Abteilung.
Mitarbeiter
Abteilung
gehört zu
- Zu einer Abteilung gehört
mindestens ein Mitarbeiter.
53
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
2.2 Beziehungen
Kardinalitäten von Beziehungstypen – Beispiele (III)
• m:m-Beziehung (komplex-komplexe Beziehung)
- Ein Mitarbeiter bearbeitet
mindestens ein Projekt.
Mitarbeiter
Projekt
bearbeitet
- Ein Projekt wird von
mindestens einem
Mitarbeiter bearbeitet.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
54
Mitschrift 30.Oktober 2008
Beispiele:
1:mc Beziehung
Mitarbeiter
o
o
o
Abteilungen
o
o
o
c:mc Beziehung
Mitarbeiter
o
o
o
o
Abteilungen
o
o
o
Folie 61
mc:m – komplex komplexe Beziehung
Begründung/Erläuterung
{1,2,3,...}
m: ein Projekt wird von midestens einem Mitarbeiter bearbeitet
{0,1,2,...}
mc: ein Mitarbeiter bearbeitet beliebig viele Projekte
Gliederung
2. Entity-Relationship-Modell
2.1 Grundlagen
2.2 Beziehungen
2.3 Symbole
2.4 Entwurf eines E-R-Modells
2.5 Beispiele
2.6 Übungsaufgaben
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
55
2.3 Symbole
Verwendete Symbole
Entitätsmenge
Beziehungsmenge
Attribut
Schlüsselattribut
Abgeleitetes Attribut
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
56
Gliederung
2. Entity-Relationship-Modell
2.1 Grundlagen
2.2 Beziehungen
2.3 Symbole
2.4 Entwurf eines E-R-Modells
2.5 Beispiele
2.6 Übungsaufgaben
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
57
2.4 Entwurf eines E-R-Modells
Prozess der Datenmodellierung
Fachbegriffe, Aussagen
Abstimmung mit:
Grobdatenmodellierung
•
Evtl. vorhandenen
Datenmodellen
Feindatenmodellierung
•
Datenelementstandards
(Objekttypen, Beziehungen)
(Attribute, Integritätsbedingungen)
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
58
2.4 Entwurf eines E-R-Modells
Schritte zum Entwurf eines E-R-Modells
1. Identifikation von Entitätsmengen und Beziehungsmengen in
einem Ausschnitt der Realität.
2. Bestimmung der Beziehungstypen zwischen den
Entitätsmengen und den Beziehungsmengen.
3. Bestimmung der Attribute der Entitätsmengen.
4. Bestimmung der Attribute der Beziehungsmengen.
5. Bestimmung der Schlüsselattribute (der Entitätsmengen).
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
59
Gliederung
2. Entity-Relationship-Modell
2.1 Grundlagen
2.2 Beziehungen
2.3 Symbole
2.4 Entwurf eines E-R-Modells
2.5 Beispiele
2.6 Übungsaufgaben
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
60
2.5 Beispiele
Beispiel (I)
Wohnort
Geburtsdatum
Straße
Alter
mc
Mitarbeiter
P#
Bearbeitung
Telefonnummer
Name
m
Projekte
Projekt#
Projektname
Vorname
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
61
2.5 Beispiele
Beispiel (II)
Erstellen Sie ein Entity-Relationship-Diagramm für einen
Waschmaschinenhersteller, das die unten dargestellten Sachverhalte
adäquat abbildet. Ergänzen Sie gegebenenfalls fehlende
notwendige Attribute.
Der Hersteller stellt zwölf verschiedene Waschmaschinentypen her, die
durch eine eindeutige Nummer identifiziert werden können. Zudem
verfügen die einzelnen Typen über eine Bezeichnung und eine
unverbindliche Preisempfehlung des Herstellers. Von jedem
Waschmaschinentyp wurden zahlreiche Exemplare ausgeliefert. Diese
Waschmaschinen können durch eine Seriennummer eindeutig
bestimmt werden. Für die Wartung dieser Waschmaschinen wird das
jeweilige Herstellungsdatum in der Datenbank gespeichert.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
62
2.5 Beispiele
Beispiel (II) - Fortsetzung
Zu jedem Waschmaschinentyp existieren mehrere PDF-Dokumente,
wie beispielsweise Bedienungs- oder Wartungsanleitungen. Zu jedem
einzelnen Dokument sollen die zusätzlichen Informationen Titel und
Autorenname der Datenbank zu entnehmen sein.
Der Hersteller beschäftigt für die Durchführung von Wartungsarbeiten
mehrere Techniker, die über einen Vor- und Nachnamen sowie einen
festen Stundenlohn verfügen. Zudem sind Sie jeweils genau einer
Abteilung zugeordnet.
Sollten an einer Waschmaschine Wartungsarbeiten durchgeführt
werden, so ist das jeweilige Datum und die Anzahl der Arbeitsstunden,
die der Techniker für die Wartung benötigt hat, in der Datenbank zu
hinterlegen. Zum Leidwesen der Kunden wird bei jeder Wartung einer
Waschmaschine ein anderer Techniker mit der Wartung beauftragt. Zu
jedem Kunden wird neben dem Vor- und Zunamen auch die
Telefonnummer gespeichert. Zur Freude des Herstellers besitzen
einige Kunden mehrere Waschmaschinen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
63
Gliederung
2. Entity-Relationship-Modell
2.1 Grundlagen
2.2 Beziehungen
2.3 Symbole
2.4 Entwurf eines E-R-Modells
2.5 Beispiel
2.6 Übungsaufgaben
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
64
Vorlesungsmitschrift
30.11.08
S. 62
Beispiel (II) Lösung
Blau Klausur- Schwarz Erläuterung
1.Identifikation von Entitätsmengen und Beziehungsmengen
in einem Ausschnitt der Realität.
Entitätsmengen
Waschmaschinen
Techniker
Kunden
Waschmaschinentypen
Dokumente
Abteilungen
Beziehungsmengen
Dokumente
⇔
Waschmaschinentypen
Waschmaschinentypen
⇔ Waschmaschinen
Waschmaschinen
⇔
Waschmaschinen
⇔ Techniker
Techniker
⇔
Kunden
Abteilungen
2.Bestimmung des Beziehungstypen zwischen den Entitätsmengen und der Beziehungsmengen
1) (m) zu jedem (einem) Waschmaschinentyp gehört mindestens eine Waschmaschine
2) (1) eine Waschmaschine gehört zu genau einem Waschmaschinentyp
3) (m) Zu einem Waschmaschinentyp gehört mindestens ein Dokument
4) (1)Ein Dokument gehört zu genau einem Waschmaschinentyp
5) (1)Ein Techniker arbeitet in genau einer Abteilung
6) (m) In einer Abteilung arbeitet mindestens ein Techniker
7) (mc)Eine Waschmaschine wird von belibieg vielen Technikern gewartet
8) (mc)Ein Techniker wartet beliebig viele Waschmaschinen
9) Eine Kunde besitzt mindestens eine Waschmaschine (m)
10) Eine Waschmaschine gehört zu höchstens einem Kunden (c)
3.Bestimmung der Attribute der Entitätsmengen
4.Bestimmung der attribute der Beziehungsmengen
5. Schlüsselattribute
Lösung S.62
Fortsetzung
Vorname
Zuname
Telefonnummer
Kunden
9
m
Seriennummer
Bezeichnung
Typ Nr.
besitzt
UVP
11
m
Waschmaschinentypen
10
c
2
1
Waschmaschinen
Gehören zu
3
m
7
mc
Herstellungsdatum
Wartungsdatum
beschreibt
gewartet
8
mc
Arbeitsstunden
4
1
Dokumente
Techniker
Vorname
Stundenlohn
5
1
(Dokumententyp)
Autorenname
Nachname
arbeitet
Titel
Dokumentennummer
Technikernummer
6
m
Abteilung
Abteil. NR
Abteilungsname
(nicht in Klausur)
2.6 Übungsaufgaben
• Erstellen Sie ein E-R-Diagramm für Abteilungen und
Angestellte.
• Erstellen Sie ein E-R-Diagramm für Dozenten, die
Kursangebote bereitstellen.
• Erstellen Sie ein E-R-Diagramm für Dozenten, die
Vorlesungen halten und hierfür Bücher empfehlen.
• Erstellen Sie ein E-R-Diagramm zur Bibliotheksverwaltung.
• Erstellen Sie ein E-R-Diagramm, welches den Bestellprozess
eines Versandunternehmens abbildet. Hierbei ist auch die
Nachbestellung der Artikel bei den Lieferanten zu
berücksichtigen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
65
Wirtschaftsinformatik II
Kapitel 3: Relationales Datenmodell
Prof. Dr. Peter Chamoni
Dipl.-Math. Tanja Bley
Wintersemester 2008/2009
Mercator School of Management
Lehrstuhl für Wirtschaftsinformatik und Operations Research
Prof. Dr.
Peter Chamoni
Prof. Dr. Peter
Chamoni
- Wirtschaftsinformatik II - Wintersemester 2008/2009
66
Gliederung
1 Grundlagen
2 Entity-Relationship-Modell
3 Relationales Datenmodell
4 SQL
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
67
Gliederung
3. Relationales Datenmodell
3.1 Relationen
3.2 Schlüsselbeziehungen
3.3 Überführung des E-R-Modells
3.4 Normalisierung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
68
3.1 Relationen
Logisches Datenmodell – Relationales Datenmodell
• Logisches Datenmodell (Wiederholung)
- Ebenfalls unabhängig von der physischen
Repräsentation
- Ausrichtung an der für die Speicherung
einzusetzenden Datenbanktechnologie
• Hier: Einsatz einer relationalen Datenbanktechnologie
relationales Datenmodell
Semantisches
Datenmodell
Logisches
Datenmodell
Physisches
Datenmodell
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
69
3.1 Relationen
Mathematisches Relationenmodell (I)
• Die Relationale Datenmodellierung basiert auf dem
mathematischen Relationenmodell.
• Relation (datenbanktheoretisch)
A1 , A 2 , , A n seien Attribute mit den Wertebereichen
W(A1 ), W(A 2 ), , W(A n ) .
W(A n ) heißt
Eine Teilmenge R W(A1 ) W(A 2 )
n -stellige Relation über den Bereichen W(A1 ), W(A 2 ), , W(A n ).
Für r (a 1 , a 2 , , a n ) mit a i Wi , i 1, , n bezeichne n den
Grad der Relation. r heißt n -Tupel aus R .
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
70
3. Vorlesungsmitschrift 6.11.08
a)
Beispiel Folie 69:
n=2
A1 Nachname
A2 Vorname
W(A1)= (Müller, Meier, Schulze)
W(A2)=(Daniel,Maria)
W(A1) x W(A2)=((Müller,Daniel);(Müller,Maria); (Meier,Daniel);(Meier,Maria);
(Schulze,Maria);(Schulz;Daniel); (Schulze; Maria))
R=((Müller,Daniel);(Schulz,Maria))
Kartesisches Produkt in Tabellendarstellung
Nachname
Vorname
Müller
Müller
Meier
Meier
Schulz
Schulz
Daniel
Maria
Daniel
Maria
Daniel
Maria
Relation in Tabellendarstellung
(Mitarbeiter eines Unternehmens)
Nachname
Vorname
Müller
Schulz
Daniel
Maria
Weiteres Beispiel:
Student
Matrikelnummer
Nachname
Vorname
PlZ
Geburtsdatum
3.1 Relationen
Mathematisches Relationenmodell (II)
• Beispiel
- Sei n
2. Hieraus folgt, dass insgesamt 2 Attribute A1 und A 2
betrachtet werden.
- Sei nun A1 das Attribut Nachname und A 2 das Attribut
Vorname.
- So kann für die Wertebereiche W(A1 ) und W(A 2 ) dann
beispielsweise gelten:
W(A1 ) {Müller, Maier, Weingarten}
W(A 2 ) {Dieter, Marie}
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
71
3.1 Relationen
Mathematisches Relationenmodell (III)
- Ermittlung des kartesischen Produktes der Wertebereich
W(A1 ) und W(A 2 ) :
W(A1 ) W(A 2 ) {(Müller, Dieter), (Müller, Marie),
(Maier, Dieter), (Maier, Marie),
(Weingarten, Dieter), (Weingarten, Marie)}
- Eine Relation R ist eine Teilmenge des kartesischen Produkts
der Wertebereiche:
R
W(A1 ) W(A 2 )
- z.B.: R {(Müller, Dieter), (Weingarten, Marie)}
Die Relation R stellt die Mitarbeiter eines Unternehmens dar.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
72
3.1 Relationen
Mathematisches Relationenmodell (IV)
• Weitere Darstellungsmöglichkeit: Relation in
Tabellendarstellung
- Das kartesische Produkt in Tabellendarstellung:
Nachname
Vorname
Müller
Dieter
Müller
Marie
Maier
Dieter
Maier
Marie
Weingarten
Dieter
Weingarten
Marie
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
73
3.1 Relationen
Mathematisches Relationenmodell (V)
- Die Relation R in Tabellendarstellung:
Nachname
Vorname
Müller
Dieter
Weingarten
Marie
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
74
3.1 Relationen
Relation in Tabellendarstellung
MITARBEITER
PNr
Nachname
Vorname
Straße
Wohnort
27
Schulz
Gerhard
Hauptstr. 3
Duisburg
63
Müller
Dieter
Bahnhofstr. 7
Essen
75
Maier
Werner
Schulstr. 5
Duisburg
89
Schmidt
Karl
Ahornweg 10
Moers
13
Maier
Werner
Mühlenstraße 99
München
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
75
Gliederung
3. Relationales Datenmodell
3.1 Relationen
3.2 Schlüsselbeziehungen
3.3 Überführung des E-R-Modells
3.4 Normalisierung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
76
3.2 Schlüsselbeziehungen
Schlüssel (I)
• Die Identifikation und Verknüpfung von Datenbeständen
erfolgt über Schlüssel.
• Ein Schlüssel für eine Relation ist eine minimale
identifizierende Attributmenge.
• Ein Schlüssel muss ein Tupel eindeutig identifizieren.
Die Werte zweier Schlüsselattribute dürfen nicht
identisch sein.
Die Schlüsselattribute dürfen keine undefinierten
Werte (Nullwerte) beinhalten.
77
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
3.2 Schlüsselbeziehungen
Schlüssel (II)
• Der Schlüssel kann aus einem Attribut oder einer Menge von
Attributen bestehen, wobei er eindeutig und minimal sein
muss.
Durch Weglassen eines Attributes darf kein
kleinerer Schlüssel entstehen.
• Als Schlüssel kann ein künstliches Attribut eingeführt werden.
In der Regel eine Teilmenge der natürlichen Zahlen
.
Beispiel: Schlüssel: Matrikelnnr.
Annahme: Nachname,Vorname wäre Schlüssel (unrealistisch es gibt mehrere mit den gleichen V/N)
oder:
Nachname,Vorname,Geburtsdatum – Schlüssel? Nein!! kein Schlüssel, weil nicht minimal
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
78
3.2 Schlüsselbeziehungen
Primärschlüssel
• In einer Relation können mehrere Attribute, welche die
Eigenschaften eines Schlüssels erfüllen, existieren.
• Ein Primärschlüssel einer Relation ist ein Attribut
(oder eine Attributkombination), welches jedes Tupel
eindeutig identifiziert.
• Während der gesamten Existenz eines Objektes in einer
Datenbank darf dessen Primärschlüssel nicht verändert
werden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
79
3.2 Schlüsselbeziehungen
Relation in Tabellendarstellung
Schlüsselattribut (Primärschlüssel)
Attribut
PNr
Nachname
Vorname
Straße
Wohnort
27
Schulz
Gerhard
Hauptstr. 3
Duisburg
63
Müller
Dieter
Bahnhofstr. 7
Essen
75
Maier
Werner
Schulstr. 5
Duisburg
89
Schmidt
Karl
Ahornweg 10
Moers
13
Maier
Werner
Mühlenstraße 99
München
Tupel
Attributwert
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
80
3.2 Schlüsselbeziehungen
Fremdschlüssel
• Relationen werden über Fremdschlüssel miteinander in eine
logische Beziehung gesetzt.
• Ein Fremdschlüssel ist ein Attribut (oder eine Attributkombination), welches in einer anderen Relation
Primärschlüssel ist.
• Wertebereich (= Domäne) des Fremdschlüssels muss mit
dem Wertebereich des Primärschlüssels aus der
referenzierten Relation übereinstimmen.
Nullwerte in Fremdschlüsseln sind aus Sicht des
Datenmodells unzulässig!
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
81
Gliederung
3. Relationales Datenmodell
3.1 Grundsätzliches
3.2 Schlüsselbeziehungen
3.3 Überführung des E-R-Modells
3.4 Normalisierung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
82
3.3 Überführung des E-R-Modells
Überführung des E-R-Modells
• Bei der Überführung eines Entity-Relationship-Modells in das
relationale Datenmodell treten in der Regel drei Fälle auf, die
im Folgenden einzeln erläutert werden:
- Entitätsmengen
- einfach-komplexe Beziehungen
- komplex-komplexe Beziehungen
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
83
3.3 Überführung des E-R-Modells
Entitätsmengen (I)
• Jede Entitätsmenge muss als eigenständige Relation mit
einem eindeutigen Primärschlüssel definiert werden.
• Als Primärschlüssel der Relation dient der entsprechende
Schlüssel der Entitätsmenge.
• Die übrigen Attribute der Entitätsmengen gehen in die
korrespondierenden Attribute der Relation über.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
84
3.3 Überführung des E-R-Modells
Entitätsmengen (II)
E-R-Modell
Stundenlohn
Nachname
Techniker
PNr
relationales Datenmodell
Vorname
TECHNIKER
PNr
Vorname
…
…
Nachname Stundenlohn
…
…
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
85
3.3 Überführung des E-R-Modells
Einfach-komplexe Beziehung (I)
• Kann ohne eine eigenständige Beziehungsmengenrelation
durch die beiden Relationen der zugeordneten
Entitätsmengen ausgedrückt werden.
• In der Relation mit der einfachen Assoziation
[= einfache Beziehung (1) oder konditionelle Beziehung (c)]
wird ein Fremdschlüssel auf die referenzierte Relation mit
eventuell weiteren Attributen der Beziehungsmenge geführt.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
86
3.3 Überführung des E-R-Modells
Einfach-komplexe Beziehung (II)
E-R-Modell
SerienNr
relationales Datenmodell
Herstellungsdatum
Waschmaschine
1
gehört zu
Waschmaschinentyp
UVP
SerienNr
Herstellungsdatum
…
…
TypNr
WASCHMASCHINENTYP
m
TypNr
WASCHMASCHINE
TypNr
Bezeichnung
…
…
UVP
Bezeichnung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
87
3.3 Überführung des E-R-Modells
Komplex-komplexe Beziehung (I)
• Die Beziehungsmenge muss als eigenständige Relation
definiert werden, wobei die Schlüssel der zugehörigen
Entitätsmengen als Fremdschlüssel in dieser Relation
auftreten müssen.
• Als Primärschlüssel der Beziehungsmengenrelation dient der
aus den Fremdschlüsseln zusammengesetzte Schlüssel.
• Weitere Attribute der Beziehungsmenge erscheinen als
zusätzliche Attribute in der Relation.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
88
Beispiel (F.87)
(Relation) Waschmaschine
SerienNR Herstellungsdatum
Waschmaschinetyp
typNR Beziehung
UVP SerienNR
1
Miele 6000
699
4711
 Typel 1
1
Miele 6000
699
1815
 Tupel 2
Die Typnummer ist in diesem Fall nicht mehr eindeutig!
Die Tupel können nicht mehr eindeutig zugewiesen werden!
3.3 Überführung des E-R-Modells
Komplex-komplexe Beziehung (II)
E-R-Modell
SerienNr
relationales Datenmodell
WASCHMASCHINE
Herstellungsdatum
SerienNr
Herstellungsdatum
…
…
Waschmaschine
mc
Arbeitsstunden
WARTUNG
Wartungsdatum
wartet
Stundenlohn
PNr
SerienNr
…
…
…
…
TECHNIKER
m
Techniker
PNr
Arbeitsstunden Wartungsdatum
Vorname
PNr
Vorname
…
…
Nachname
Nachname Stundenlohn
…
…
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
89
3.3 Überführung des E-R-Modells
Mögliche Kombinationen der Beziehungstypen
1
c
m
mc
1
1:1
c:1
m:1
mc:1
c
1:c
c:c
m:c
mc:c
m
1:m
c:m
m:m
mc:m
mc
1:mc
c:mc
m:mc
mc:mc
• Nur die hierarchischen Beziehungen (1:x) werden von einem
Datenbanksystem unterstützt.
• Nichthierarchische Beziehungen müssen transformiert
werden (siehe z. B. vorherige Folie).
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
90
Folie 89
Wartung
PNr
Seriennummer
Arbeitsstunden
Wartungsdatum
3
3
4711
4711
3
2,5
27.11.06
1.04.2007
Funktioniert nicht!! Dies wäre Ergebnis,wenn die Bedingung in der Aufgabenstellung zur
Erstellung des ER-Modells nicht angegeben wäre.
„ ...zum Leidwesen der Kunden wird bei jeder Wartung ein anderer Techniker eingesetzt.“
(durch diesem ... Satz kann dieses Tupel nicht auftauchen)
Relatives Datenmodell
- hier ok das fs gleich sind
Wartung
Wartungsnum.
Arbeitsstunden
Wartungsdatum
FS
Seriennummer
27
29
3
2,5
27,11
1,12
4711
4711
FS
Pnr
3
3
So kann der Fall auftreten, dass ein Techniker mehrmals zum gleichen Kunden kommt!!
also Modellierung hier eher besser in der Praxis
Alternative Modellierung
SerienNr.
Waschmasch.
Herstellungsdatum
mc
wartet
1
Arbeitsstunden
Wartungsnum.
Wartung
1
Wartungsdatum
arbeitet
Vorname
mc
Pnr.
Techniker
Nachname
Relatives Datenmodel: ...
Stundenlohn
3.3 Überführung des E-R-Modells
Beispiel
• Überführen Sie das zuvor erstellte Entity-Relationship-Modell
(Waschmaschinen) in das relationale Datenbankmodell.
Antwort siehe
vorher
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
91
Gliederung
3. Relationales Datenmodell
3.1 Grundsätzliches
3.2 Schlüsselbeziehungen
3.3 Überführung des E-R-Modells
3.4 Normalisierung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
92
Übung Folie 91 (letzte woche gemacht)
-Kursiv kommt im 2. Schritt hinzu! (buchstabe zeigt nur Verbindung)
1.Fall
Entitätsmengen
Dokument
DokumentenNr.
Autoren TypNR (a)
name
Titel
Waschmaschinetyp
Beziehung
UVP
TypNr.
Kunden
KundenNr
Vorname
Zuname TeleNr.
Waschmaschine
Seriennummer
TypNR. (a) KundenNR
Herstellungsdatum
Techniker
Pnr.
Vorname
Nachname
Stundenlohn
AbtNr.
Abteilung
AbtNr.
Abteilungsname
2.Fall 4 einfach- komplexe Beziehung
3.Fall komplex-komplex Beziehung Wartung (immer eigenständige Relation erstellen!!)
SerienNR
PNR
(insgesamt nun 7 Relationen „zähle Tabelle...“ )
Arbeitsstunden
Wartungsstunden
3.4 Normalisierung
Redundanzfreie Datenhaltung
• Normalisierung bezweckt die redundanzfreie Speicherung von
Daten in den Relationen einer Datenbank.
• Redundanzfreie Datenhaltung:
Kein Teil eines Datenbestandes kann weggelassen werden,
ohne dass dies zu Informationsverlusten führt.
• Redundanzfreie Datenspeicherung bringt
Speicherplatzersparnis und verhindert Mutationsanomalien.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
93
3.4 Normalisierung
Beispiel einer Relation mit Redundanzen
PNr Nachname Vorname
Stundenlohn
AbtNr Abteilungsname
27
Schulz
Gerhard
17,50
1
Kundendienst
63
Müller
Dieter
16,80
2
Werkstatt
75
Maier
Werner
26,50
2
Werkstatt
89
Schmidt
Karl
18,40
1
Kundendienst
13
Maier
Werner
24,90
1
Kundendienst
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
94
Zu Folie 94
Pnr
Nachname
Vorname
Studenlohn
Ab.Nr
Abteilungsname
1
2
Kundendienst
Werkstat
AbtNr.
3.4 Normalisierung
Funktionale Abhängigkeit (I)
• 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 eindeutig der
Attributwert von B.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
95
3.4 Normalisierung
Funktionale Abhängigkeit (II)
• Beispiel:
PNr Nachname Vorname Stundenlohn
…
…
…
…
• Nachname ist funktional abhängig von PNr, da zu jeder
Personalnummer genau ein Nachname gehört.
Aus PNr ergibt sich eindeutig der Nachname.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
96
Beispiel Folie 95
Student
A/C
B/D
Matrikelnr
Nachname
Vorname
11
12
13
Müller
Meier
Müller
Carsten
Sophia
Benjamin
Ist B von A funktional abhängig?
Wenn ich einen Attrb.wert von A nehme folgt genau nur ein einziger Attrb.wert von B !!
– Aus der Matrikelnummer ergibt sich eindeutig der Nachname
Überlegung umgekehrt:
Ist C von D funktional abhängig
Aus dem Attrb.wert von D ergibt sich eindeutig der Attrb.wert von C?
(gilt nur wenn es funktional abhängig wäre)
Natürlich NICHT!!!
Müller ist nicht eindeutig einer Matrikelnr zugewiesen!!!
3.4 Normalisierung
Voll funktionale Abhängigkeit (I)
• Ein Attribut bzw. eine Attributkombination B ist dann von einer
Attributkombination A voll funktional abhängig, wenn B
nur von A, nicht jedoch schon von einem Teil der
Attributkombination A funktional abhängig ist.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
97
3.4 Normalisierung
Voll Funktionale Abhängigkeit (II)
• Beispiel:
PNr SerienNr Wartungsdatum
…
…
…
•
Wartungsdatum ist nicht von PNr funktional abhängig.
•
•
Wartungsdatum ist nicht von SerienNr funktional abhängig
Wartungsdatum ist von der Attributkombination PNr, SerienNr
funktional abhängig.
Wartungsdatum ist voll funktional abhängig von der
Attributkombination PNr, SerienNr.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
98
Folie 97
A - Attributkombination
x,y sind Attribute
(B von A voll funktional abhängig)
Bedingung B von A voll fkt. abh. Ist
– B von A (x,y) fkt. abh ist
– B von X ist nicht fkt. abh.
– B von Y ist nicht fkt. abh.
Folie 98
Attributkombination A (Pnr, SerienNR)
x Pnr
y Serien Nr
Attribut
B Wartungsdatum
frage: Ist B von a voll fkt. abh?
Ist Wartungsdatum von (PNr, SerienNr) voll fkt.abh.?
Bedingung überprüfen
B von X funktional abh. ?
-Wartungsdatum von Pnr fkt. abh? Nein!!!
B von Y fkt abh.?
-Wartungsdatum von SerNR fkt abh? Nein!!
B von A fkt. abh?
-Wartungsdatum von SerienNR und PN Nr fkt abh?
Ja!!
Drei Bedingungen sind erfüllt!
-Wartungsdatum ist voll fkt. abh. Von SerienNR und PersonalNR.
3.4 Normalisierung
Transitive Abhängigkeit (I)
• 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.
99
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
3.4 Normalisierung
Transitive Abhängigkeit (II)
• Beispiel:
A
B
C
SerienNr Herstellungsdatum TypNr Bezeichnung UVP
…
…
3 Bedingungen müssen für die
transitive Abhängigkeit erfüllt sein
1 . C von B funkt. Abhängig
2. B von A funkt. Abhängig
3. A von C nicht funkt. Abhängig
2
• TypNr ist von SerienNr funktional abhängig.
1
• Bezeichnung ist von TypNr funktional abhängig.
3
• SerienNr ist aber nicht von Bezeichnung funktional abhängig.
Bezeichnung ist von SerienNr transitiv abhängig, d.h. aus
SerienNr ergibt sich die Bezeichnung.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
100
3.4 Normalisierung
Non-First-Normalform
• Beispiel: Techniker, die zu unterschiedlichen Abteilungen
gehören, warten Waschmaschinen.
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
Abteilungsname
SerienNr
Herstellungsdatum
Arbeitsstunden
Wartungsdatum
27
Schulz
Gerhard
17,50
1
Kundendienst
42568
01.09.2005
3
03.11.2006
63
Müller
Dieter
16,80
2
Werkstatt
25632
30.10.2002
4,5
04.09.2005
75
Maier
Werner
26,50
2
Werkstatt
42568
11263
01.09.2005
27.11.2004
1
2
01.10.2007
17.06.2006
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
101
3.4 Normalisierung
Erste Normalform (I)
• Eine Relation befindet sich in der 1. Normalform (1NF), wenn
alle Attributwerte elementar sind.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
102
3.4 Normalisierung
Erste Normalform (II)
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
Abteilungsname
SerienNr
Herstellungsdatum
Arbeitsstunden
Wartungsdatum
27
Schulz
Gerhard
17,50
1
Kundendienst
42568
01.09.2005
3
03.11.2006
63
Müller
Dieter
16,80
2
Werkstatt
25632
30.10.2002
4,5
04.09.2005
75
Maier
Werner
26,50
2
Werkstatt
42568
01.09.2005
1
01.10.2007
75
Maier
Werner
26,50
2
Werkstatt
11263
27.11.2004
2
17.06.2006
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
103
3.4 Normalisierung
Zweite Normalform (I)
• Eine Relation befindet sich in der 2. Normalform (2NF), wenn
sie schon in der 1. Normalform ist und jedes nicht zum
Schlüssel gehörende Attribut vom Schlüssel voll funktional
abhängig ist.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
104
3.4 Normalisierung
Zweite Normalform (II)
Waschmaschine
Wartung
SerienNr
Herstellungsdatum
SerienNr
PNr
Arbeitsstunden
Wartungsdatum
11263
27.11.2004
42568
27
3
03.11.2007
25632
30.10.2002
25632
63
4,5
04.09.2005
42568
01.09.2005
42568
75
1
01.10.2007
11263
75
2
17.06.2006
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
Abteilungsname
27
Schulz
Gerhard
17,50
1
Kundendienst
63
Müller
Dieter
16,80
2
Werkstatt
75
Maier
Werner
26,50
2
Werkstatt
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
105
3.4 Normalisierung
Dritte Normalform (I)
• Eine Relation befindet sich in der 3. Normalform (3NF), wenn
sie schon in der 2. Normalform ist und kein
Nichtschlüsselattribut vom Schlüssel transitiv abhängig ist.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
106
3.4 Normalisierung
Dritte Normalform (II)
Waschmaschine
Wartung
SerienNr
Herstellungsdatum
SerienNr
PNr
Arbeitsstunden
Wartungsdatum
11263
27.11.2004
42568
27
3
03.11.2007
25632
30.10.2002
25632
63
4,5
04.09.2005
42568
01.09.2005
42568
75
1
01.10.2007
11263
75
2
17.06.2006
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Abteilungsname
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
2
107
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
3.4 Normalisierung
Übungsaufgabe Normalisierung
•
•
Überführen Sie die folgende Relation in die dritte Normalform.
Problemsituation: Sie wurden aufgefordert, für verschiedene
Produkte bei unterschiedlichen Händlern Angebote einzuholen.
Angebotsübersicht
HändlerNr
ProdName
HändlerTelefon
AngPreis
ProdNr
Händlername
HerstNr
HerstName
…
…
…
…
…
…
…
…
•
Erläuterung der Attribute:
-
HändlerNr
ProdName
HändlerTelefon
AngPreis
ProdNr
Händlername
HerstNr
HerstName
Händlernummer
Produktname
Händlertelefonnummer
Angebotspreis
Produktnummer
Händlername
Herstellernummer
Herstellername
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
108
Klausuraufgabe: S.108
(Betrachte welche das ist)
Übung zur Normalisierung
1. Normalform
Def. 1. NF:
Eine Relation befindet sich in der ersten NF, wenn alle Attributwerte elementar sind.
-Die Relation befindet sich in der ersten Normalform, da die Annahme getroffen wird,
dass alle Attributwerte elementar sind.
Oder
-Die Relation befindet sich in der 1. NF, da sich leere Relationen immer in der 1. NF befinden
2. Normalform
Def 2. NF: Eine Relation befindet sich in der 2. NF, wenn sie sich bereits in der 1. NF befindet
und jedes nicht zum Schlüssel gehörende Attribut vom Schlüssel voll funktional
abhängig ist.
Def. Voll fkt abh. Ein Attribut oder eine Attributkombination B ist von einer Attributkombination A
voll fkt. abh., wenn B von A , und nicht bereits von einem Teil der Attributkombination A fkt.
abhängig ist.
Def.fkt.abh.:Ein Attribut oder eine Attributkombination B ist von einem Attribut oder einer
Attributkombination A fkt. abh., wenn zu einem bestimmten Attributwert von A genau ein
Attributwert von B gehört.
Produktname
Händlertelefon
AngPreis
HändlerName
HerstNr.
HerstName
1.Teil PK
HändlerNr
2.Teil PK
ProdNr.
Nicht fkt. abh.
fkt. abh
n.f.a
f.a
n.f.a
n.f.a
f.a
n.f.a
n.f.a
n.f.a
f.a
f.a
PK
HändlerNr, ProdNr.
hier überall f.a/aber nun voll?-Nein!
Nein!
Ja! (Voll fkt. abh.!)
Nein!
Nein!
Nein!
Die Relation Angebotsübersicht befindet sich nicht in der 2.Normalform, da eben die Attribute
Produktname, Händlertelefon, Händlername Hersteller und Herstellername nicht voll fkt. abh. sind,
deshalb muss die Relation wie folgt aufgelöst werden.
Angebotsübersicht
HändlerNR ProduktNR. Angb. Preis
Händler
HändlerNr HändlerTelefon HändlerName
Produkt
ProdNr ProdName HerstNR. HerstName
3.Normalform
Def. 3.NF: Eine Relation befindet sich in der 3. NF, wenn sie sich bereits in der 2. NF befindet
und kein Nichtschlüsselattribut vom Schlüssel transitiv abh. ist.
Def. Trans. Abh.: Ein Attribut oder eine Attributkombination C ist von einem Attribut oder einer
Attributkombination A transitiv abhängig, wenn C von B funktional abh. ist, das Attribut B von A
fkt. abhängig ist, nicht jedoch A von C fkt. abh.
-Die Relation Angebotsübersicht besitzt nur ein Nichtschlüsselattribut und befindet sich somit
in der 3. NF, da sie sich bereits in der 2. NF befindet.
Händler
A=Händlernummer
B
HändlerTelefon
Händlername
C
Händlername
Händlertelefon
Bedingung
C von B fkt. abh.
C von B fkt. abh.
Ergebnis
Nein
Nein
Die Relation Händler befindet sich in der 3. NF, da die Attribute Händlertelefon
und Händlername nicht transitiv abhängig sind und die 2. NF bereits erfüllt ist
Produktname
A=ProdNr
B
ProdName
ProdName
HerstNr
HerstNr
C
Herst.Nr
HerstName
ProdName
HerstName
HerstName
HerstName
ProdName
HerstNr
Bedingung
C von B fkt. abh.
...
...
...
B von A fkt abh.
A von C nicht fkt ab
C von B fkt abh.
C von B fkt abh.
Ergebnis
Nein
Nein
Nein
ja
ja
ja
Nein
Nein
Da das Attribut HerstName transitiv abhängig ist vom Schlüssel, muss
die Relation wie folgt aufgeteilt werden.
Produkt
ProdNR ProdName
Hersteller
HerstNR HerstName
HerstNR
Wirtschaftsinformatik II
Kapitel 4: SQL
Prof. Dr. Peter Chamoni
Dipl.-Math. Tanja Bley
Wintersemester 2008/2009
Mercator School of Management
Lehrstuhl für Wirtschaftsinformatik und Operations Research
Prof. Dr.
Peter Chamoni
Prof. Dr. Peter
Chamoni
- Wirtschaftsinformatik II - Wintersemester 2008/2009
109
Gliederung
1 Grundlagen
2 Entity-Relationship-Modell
3 Relationales Datenmodell
4 SQL
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
110
Gliederung
4. SQL
4.1 Begriffliches
4.2 Relationale Algebra
4.3 Relationale Algebra und SQL
4.4 Datenmanipulationssprache
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
111
4.1 Begriffliches
SQL
• SQL = structured query language
• datenorientierte Sprache
• Sprache der 4. Generation (4GL)
[= deklarative Sprachen]
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
112
4.1 Begriffliches
SQL - Historische Entwicklung
• Anfang der 80er Jahre durch IBM entwickelt
• SQL wurde vom ANSI (American National Standard Institut)
als Standardsprache für relationale Datenbanken erklärt.
• Normierungen durch die ISO (International Organization for
Standardization):
-
1. Norm: 1986
2. Norm: 1989 (SQL1)
3. Norm: 1992 (SQL2)
4. Norm: 1999 (SQL3)
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
113
4.1 Begriffliches
SQL - Sprachumfang
• Zum Sprachumfang von SQL gehören 3 Befehlsgruppen:
- Datendefinitionssprache (data definition language; DDL):
Erstellen von Datenbanken, Tabellen (Relationen) und Indizes.
- Datenmanipulationssprache (data manipulation language; DML):
Anlegen, Ändern, und Löschen von Datensätzen.
- Datenkontrollsprache (data control language; DCL):
Anlegen von Benutzern und Vergabe von Zugriffsrechten.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
114
4.1 Begriffliches
Abfrage und Mutation
• Eine Abfrage (query) erlaubt, einen bestimmten Ausschnitt
einer Datenbank abzugrenzen und aufzufinden sowie dessen
Inhalt in geeigneter Form darzustellen.
• Eine Mutation oder Nachführung (update) erlaubt, einen
bestimmten Ausschnitt einer Datenbank abzugrenzen und
aufzufinden sowie dessen Inhalt konsistenzerhaltend zu
verändern.
Datenkonsistenz ist gegeben, wenn der Inhalt einer Datenbank
alle vordefinierten Bedingungen erfüllt.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
115
4.1 Begriffliches
Transaktion
• Transaktion
Folge von Datenbankoperationen, die hinsichtlich gewisser
Integritätsanforderungen atomare Einheiten darstellen. Sie
lässt die Datenbank in konsistentem Zustand zurück, wenn
diese vor Beginn der Transaktion schon konsistent war.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
116
4.1 Begriffliches
Begriffshierarchie bei Datenbankoperationen
Mutationen
zum Nachführen der
Datenbasis
Transaktionen
(konsistenzerhaltend)
Abfragen
ohne Änderung der
Datenbasis
einfügen
ändern
entfernen
auswählen
navigieren
darstellen
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
117
Gliederung
4. SQL
4.1 Begriffliches
4.2 Relationale Algebra
4.3 Relationale Algebra und SQL
4.4 Datenmanipulationssprache
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
118
4.2 Relationale Algebra
Grundlagen
• Das relationale Datenmodell basiert auf den Grundlagen der
mathematischen Mengenlehre und bildet den formalen
Rahmen für relationale Datenbanksprachen.
• Die relationale Algebra ist ein Konzept zur Manipulation von
Relationen, d. h. von Mengen, deren Elemente mehrstellige
Tupel sind.
• Codd erstellte acht Operatoren, mit denen alle denkbaren
Zugriffe auf beliebige Relationen der Datenbank möglich sind.
Vollständige, in sich geschlossene Algebra!
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
119
4.2 Relationale Algebra
Vereinigung (I)
• Wenn zwei Relationen die gleichen Attribute besitzen, können
diese Relationen vereinigt werden.
• Die Vereinigung enthält dann alle Tupel beider Relationen.
• Identische Tupel werden dabei eliminiert.
• Voraussetzung für die Vereinigung ist, dass die Namen und
Wertebereiche der Attribute beider Relationen
übereinstimmen.
Relationale Algebra: R1 UNION R2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
120
___________________________________________________
S.119
Wiederholung
Relation
R ⊆ W  A  × ... × W  A 
1
n
Relation
Tupel (...6715;Meier...)
...
Relation in Tabellendarstellung
Relationenname
A
1
A
2
A
3
4.2 Relationale Algebra
Vereinigung (II)
• Bsp.: Gesucht wird die Vereinigung der Relationen Techniker1
und Techniker2.
Techniker2
Techniker1
PNr
Nachname
Vorname
Stundenlohn
AbtNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
10
Wurzel
Marius
23,90
2
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker1
UNION
Techniker2
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
10
Wuzel
Marius
23,90
2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
121
4.2 Relationale Algebra
Durchschnitt (I)
• Der Durchschnitt zweier Relationen enthält alle Tupel, die in
beiden Relationen enthalten sind.
• Voraussetzung für den Durchschnitt ist, dass die Namen und
Wertebereiche der Attribute beider Relationen
übereinstimmen.
Relationale Algebra: R1 INTERSECT R2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
122
4.2 Relationale Algebra
Durchschnitt (II)
• Bsp.: Gesucht wird der Durchschnitt der Relationen
Techniker1 und Techniker3.
Techniker3
Techniker1
PNr
Nachname
Vorname
Stundenlohn
AbtNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
75
Maier
Werner
26,50
1
29
Naumann
Harry
14,50
2
Relationale Algebra
Techniker1
INTERSECT
Techniker3
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
75
Maier
Werner
26,50
1
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
123
4.2 Relationale Algebra
Differenz (I)
• Die Differenz zweier Relationen enthält alle Tupel der ersten
Relation, die nicht in der zweiten Relation enthalten sind.
• Voraussetzung für die Differenz ist, dass die Namen und
Wertebereiche der Attribute beider Relationen
übereinstimmen.
Relationale Algebra: R1 MINUS R2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
124
4.2 Relationale Algebra
Differenz (II)
• Bsp.: Gesucht wird die Differenz der Relationen Techniker1
und Techniker3.
Techniker3
Techniker1
PNr
Nachname
Vorname
Stundenlohn
AbtNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
75
Maier
Werner
26,50
1
29
Naumann
Harry
14,50
2
Relationale Algebra
Techniker1
MINUS
Techniker3
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
63
Müller
Dieter
16,80
2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
125
4.2 Relationale Algebra
Selektion (I)
•
Bei der Selektion handelt es sich um einen Suchvorgang in einer
Relation.
•
Es wird eine Auswahlbedingung aus dem Wertebereich eines oder
mehrerer Attribute vorgegeben, auf die sämtliche Tupel der Relation
untersucht werden.
•
Tupel, die das gesuchte Kriterium erfüllen, werden zu einer neuen
Relation zusammengefasst.
•
Ist in der Relation kein Tupel vorhanden, welches die gesuchte
Bedingung erfüllt, so ist das Ergebnis dieser Relation die leere
Relation.
Relationale Algebra: R WHERE Bedingung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
126
4.2 Relationale Algebra
Selektion (II)
• Bsp.: Gesucht sind alle Tupel aus der Relation Techniker, bei
denen PNr kleiner als 50 ist.
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Ergebnisrelation
Techniker WHERE PNr < 50
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
127
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.2 Relationale Algebra
Selektion (III)
• Bsp.: Gesucht sind alle Tupel aus der Relation Techniker, bei
denen der Nachname=„Maier“ ist.
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker WHERE Nachname = 'Maier'
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
75
Maier
Werner
26,50
1
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
128
4.2 Relationale Algebra
Projektion (I)
• Auswahl von Attributen einer Relation.
• Alle Attribute, welche die Auswahlbedingungen erfüllen,
werden in eine neue Relation projiziert.
• Bei der Projektion werden evtl. entstehende identische Tupel
eliminiert.
Relationale Algebra: R [Attributauswahl]
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
129
4.2 Relationale Algebra
Projektion (II)
• Bsp.: Gesucht sind die Attribute Vorname und Nachname aus
der Relation Techniker.
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker [Vorname, Nachname]
Ergebnisrelation
Vorname
Nachname
Gerhard
Schulz
Dieter
Müller
Werner
Maier
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
130
4.2 Relationale Algebra
Kartesisches Produkt (I)
• Bei dem kartesischen Produkt zweier Relationen wird eine
neue Relation erzeugt, die aus allen möglichen
Kombinationen der Tupel der beiden Relationen bestehen.
Relationale Algebra: R1 TIMES R2
131
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.2 Relationale Algebra
Kartesisches Produkt (II)
• Bsp.: Gesucht wird das kartesische Produkt der Relationen
Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker
TIMES
Abteilung
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
27
Schulz
Gerhard
17,50
1
2
Werkstatt
63
Müller
Dieter
16,80
2
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
1
Kundendienst
75
Maier
Werner
26,50
1
2
Werkstatt
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
132
4.2 Relationale Algebra
Verbund (I)
• Bei dem Verbund zweier Relationen wird eine neue Relation
erzeugt, die aus allen möglichen Kombinationen der Tupel der
beiden Relationen bestehen, wobei gemeinsame Attribute
beider Relationen als Verknüpfung dienen.
• Der Verbund (zweier Relationen) ist das kartesische Produkt
aller Tupel von zwei Relationen, bei denen ein bestimmter
Vergleich (= Selektion) zwischen den beiden Tupeln erfüllt ist.
• Als gemeinsame Schnittstelle beider Relationen dienen dabei
Attribute, die in beiden Relationen vorkommen und eine
gleiche Ausprägung besitzen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
133
4.2 Relationale Algebra
Verbund (II)
• Das Ergebnis einer Verbundoperation ist eine Relation, in
denen die Quellrelationen über dieses gemeinsame Attribut
miteinander verbunden sind.
Relationale Algebra: R1 JOIN R2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
134
4.2 Relationale Algebra
Wiederholung Kartesisches Produkt
• Bsp.: Gesucht wird das kartesische Produkt der Relationen
Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker
TIMES
Abteilung
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
27
Schulz
Gerhard
17,50
1
2
Werkstatt
63
Müller
Dieter
16,80
2
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
1
Kundendienst
75
Maier
Werner
26,50
1
2
Werkstatt
135
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.2 Relationale Algebra
Verbund (III)
• Bsp.: Gesucht wird der Verbund zwischen den Relationen
Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Ergebnisrelation
Relationale Algebra
Techniker
JOIN
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
1
Kundendienst
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
136
4.2 Relationale Algebra
Natürlicher Verbund (I)
• Beim Verbund ist das in beiden Relationen vorkommende
Attribut redundant.
• Wird das redundante Attribut nachträglich durch eine
Projektion entfernt, so handelt es sich um einen
natürlichen Verbund.
137
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.2 Relationale Algebra
Natürlicher Verbund (II)
• Bsp.: Gesucht wird der natürliche Verbund zwischen den
Relationen Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
Kundendienst
63
Müller
Dieter
16,80
2
Werkstatt
75
Maier
Werner
26,50
1
Kundendienst
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
138
4.2 Relationale Algebra
Division (I)
• Voraussetzung ist, dass die erste Relation mindestens alle
Attribute der zweiten Relation enthält und die Namen und die
Wertebereiche dieser Attribute bei beiden Relationen
übereinstimmen.
• Bei der Division zweier Relationen entsteht eine neue
Relation, die alle Attribute der ersten Relation außer den
Attributen der zweiten Relation enthält.
• Es werden nur diejenigen Tupel der ersten Relation
übernommen, deren Attributwerte mit allen Attributwerten der
zweiten Relation übereinstimmen.
Relationale Algebra: R1 DIVIDED BY R2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
139
4.2 Relationale Algebra
Division (II)
• Bsp.: Gesucht ist die Division der Relationen A und B.
A
B
X#
Y#
Y#
X1
Y1
Y2
X1
Y2
Y3
X1
Y3
X2
Y1
X2
Y2
X3
Y2
X3
Y3
Relationale Algebra
A DIVIDEDBY B
Ergebnisrelation
X#
X1
X3
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
140
4.2 Relationale Algebra
Division (III)
• Bsp.: Gesucht sind die Waschmaschinen, die in allen
Abteilungen repariert wurden.
Wartung2
Abteilung
Seriennr
Abtnr
Abtnr
42568
1
1
25632
2
2
42568
2
11263
2
Relationale Algebra
Ergebnisrelation
Wartung2 DIVIDEDBY Abteilung
Seriennr
42568
141
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.2 Relationale Algebra
Zusammenfassende Übersicht (I)
Mengenoperatoren:
Vereinigung
Relationenspezifische Operatoren:
Durchschnitt
Differenz
Selektion
Projektion
(natürlicher) Verbund
a1
a2
a3
Kartesisches Produkt
a
b
c
x
y
a
b
c
a
b
c
x
x
x
y
y
y
b1
b1
b2
b1
b2
c1
c2
a1
a2
a3
b1
b1
b2
c1
c1
c2
Division
a
a
a
b
c
x
y
z
x
y
x
y
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
a
142
4.2 Relationale Algebra
Zusammenfassende Übersicht (II)
• Das Ergebnis bei diesen 8 Operatoren sind wieder
Relationen, auf die die Operatoren wieder angewendet
werden können.
• Es müssen bei der Anwendung der Algebra nicht nur die
Attributwerte in den Tupeln, sondern auch die Attributnamen
beachtet werden.
• Bei einigen Operatoren, z. B. beim Verbund, müssen die
Attributnamen u. U. umbenannt werden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
143
Gliederung
4. SQL
4.1 Begriffliches
4.2 Relationale Algebra
4.3 Relationale Algebra und SQL
4.4 Datenmanipulationssprache
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
144
4.3 Relationale Algebra und SQL
Befehle
• Zur Suche und Abfrage von Daten in der Datenbank steht nur
genau ein Befehl zur Verfügung, der SELECT-Befehl.
• Vorgehensweise
- SELECT- und FROM-Klausel
- WHERE-Klausel
- Mengenoperatoren
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
145
4.3 Relationale Algebra und SQL
SELECT- und FROM-Klausel (I)
Syntax:
SELECT *
FROM Tabellenname
Beispiel:
Ergebnis:
SELECT *
FROM Techniker;
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
146
4.3 Relationale Algebra und SQL
SELECT- und FROM-Klausel (II)
Syntax:
SELECT Attributliste
FROM Tabellenname
Beispiel:
SELECT PNr, Nachname
FROM Techniker;
Ergebnis:
PNr
Nachname
27
Schulz
63
Müller
75
Maier
147
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.3 Relationale Algebra und SQL
Projektion – vgl. Folie 130
• Bsp.: Gesucht sind die Attribute Vorname und Nachname aus
der Relation Techniker.
TECHNIKER
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker [Vorname, Nachname]
SQL-Befehl
SELECT Vorname, Nachname
FROM Techniker;
Ergebnisrelation
Vorname
Nachname
Gerhard
Schulz
Dieter
Müller
Werner
Maier
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
148
4.3 Relationale Algebra und SQL
Kartesisches Produkt (I) – vgl. Folie 132
• Bsp.: Gesucht wird das kartesische Produkt der Relationen
Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker TIMES
Abteilung
SQL-Befehl
SELECT *
FROM Techniker, Abteilung;
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
27
Schulz
Gerhard
17,50
1
2
Werkstatt
63
Müller
Dieter
16,80
2
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
1
Kundendienst
75
Maier
Werner
26,50
1
2
Werkstatt
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
149
4.3 Relationale Algebra und SQL
WHERE-Klausel (I)
Syntax:
SELECT * | Attributliste
FROM Tabellenname
[WHERE Bedingung]
Beispiel:
Ergebnis:
SELECT *
FROM Techniker
WHERE PNr < 50;
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
150
4.3 Relationale Algebra und SQL
Selektion (I) – vgl. Folie 127
• Bsp.: Gesucht sind alle Tupel aus der Relation Techniker, bei
denen PNr kleiner als 50 ist.
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Ergebnisrelation
Techniker WHERE PNr < 50
SQL-Befehl
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
SELECT *
FROM Techniker
WHERE PNr < 50;
151
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.3 Relationale Algebra und SQL
Selektion (II) – vgl. Folie 128
• Bsp.: Gesucht sind alle Tupel aus der Relation Techniker, bei
denen der Nachname=„Maier“ ist.
Techniker
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker WHERE Nachname =
'Maier'
SQL-Befehl
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
75
Maier
Werner
26,50
1
SELECT *
FROM Techniker
WHERE Nachname = 'Maier';
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
152
4.3 Relationale Algebra und SQL
Verbund – vgl. Folie 136
• Bsp.: Gesucht wird der Verbund zwischen den Relationen
Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Ergebnisrelation
Relationale Algebra
Techniker JOIN Abteilung
SQL-Befehl
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
1
Kundendienst
SELECT *
FROM Techniker, Abteilung
WHERE Techniker.AbtNr = Abteilung.AbtNr;
153
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.3 Relationale Algebra und SQL
Natürlicher Verbund – vgl. Folie 138
• Bsp.: Gesucht wird der natürliche Verbund zwischen den
Relationen Techniker und Abteilung.
Techniker
Abteilung
PNr
Nachname
Vorname
Stundenlohn
AbtNr
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
1
Kundendienst
63
Müller
Dieter
16,80
2
2
Werkstatt
75
Maier
Werner
26,50
1
Ergebnisrelation
SQL-Befehl
PNr
Nachname
Vorname
Stundenlohn
AbtNr
Bezeichnung
27
Schulz
Gerhard
17,50
1
Kundendienst
63
Müller
Dieter
16,80
2
Werkstatt
75
Maier
Werner
26,50
1
Kundendienst
SELECT PNr, Nachname, Vorname, Stundenlohn, Techniker.AbtNr, Bezeichnung
FROM Techniker, Abteilung
WHERE Techniker.AbtNr = Abteilung.AbtNr;
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
154
4.3 Relationale Algebra und SQL
Mengenoperatoren
• Mehrere SELECT-Befehle können durch Mengenoperatoren
miteinander verknüpft werden.
- UNION (= Vereinigung)
- INTERSECT (= Durchschnitt)
- MINUS (= Differenz)
155
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.3 Relationale Algebra und SQL
Mengenoperator UNION (Vereinigung) – vgl. Folie 121
• Bsp.: Gesucht wird die Vereinigung der Relationen
Techniker1 und Techniker2.
Techniker2
Techniker1
PNr
Nachname
Vorname
Stundenlohn
AbtNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
10
Wurzel
Marius
23,90
2
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
Relationale Algebra
Techniker1 UNION Techniker2
SQL-Befehl
SELECT * FROM Techniker1
UNION
SELECT * FROM Techniker2;
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
10
Wuzel
Marius
23,90
2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
156
4.3 Relationale Algebra und SQL
Mengenoperator INTERSECT (Durchschnitt) – vgl. Folie 123
• Bsp.: Gesucht wird der Durchschnitt der Relationen
Techniker1 und Techniker3.
Techniker3
Techniker1
PNr
Nachname
Vorname
Stundenlohn
AbtNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
75
Maier
Werner
26,50
1
29
Naumann
Harry
14,50
2
Relationale Algebra
Techniker1 INTERSECT Techniker3
SQL-Befehl
SELECT * FROM Techniker1
INTERSECT
SELECT * FROM Techniker3;
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
75
Maier
Werner
26,50
1
157
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.3 Relationale Algebra und SQL
Mengenoperator MINUS (Differenz) – vgl. Folie 125
• Bsp.: Gesucht wird die Differenz der Relationen Techniker1
und Techniker3.
Techniker1
Techniker3
PNr
Nachname
Vorname
Stundenlohn
AbtNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
27
Schulz
Gerhard
17,50
1
27
Schulz
Gerhard
17,50
1
63
Müller
Dieter
16,80
2
75
Maier
Werner
26,50
1
75
Maier
Werner
26,50
1
29
Naumann
Harry
14,50
2
Relationale Algebra
Techniker1 MINUS Techniker3
SQL-Befehl
Ergebnisrelation
PNr
Nachname
Vorname
Stundenlohn
AbtNr
63
Müller
Dieter
16,80
2
SELECT * FROM Techniker1
MINUS
SELECT * FROM Techniker3;
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
158
Gliederung
4. SQL
4.1 Begriffliches
4.2 Relationale Algebra
4.3 Relationale Algebra und SQL
4.4 Datenmanipulationssprache
Vorlesung:
Abfolge SQL (ACCESS 2003)
-Öffnen
-Auswahl der Relationen(.mdb Datei)
-Abfrage erstes Fenster schließen
oben links sql-Ansicht
- befehl eingeben oben Ausrufezeichen ausführen!
- immer richtig schreiben!!!
159
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
4.4 Datenmanipulationssprache
Zugrundeliegende Relationen
Dokument
Techniker
DokumentNr
Titel
Autorenname
TypNr
PNr
Nachname
Vorname
Stundenlohn
AbtNr
…
…
…
…
…
…
…
…
…
Wartung
Waschmaschinentyp
TypNr
Bezeichnung
UVP
SerienNr
PNr
Arbeitstunden
Wartungsdatum
…
…
…
…
…
…
…
Abteilung
Kunde
KundenNr
Vorname
Zuname
Telefonnummer
AbtNr
Abteilungsname
…
…
…
…
…
…
Waschmaschine
SerienNr
Herstellungsdatum
TypNr
KundenNr
…
…
…
…
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
160
4.4 Datenmanipulationssprache
SELECT- und WHERE- Klausel (I)
Syntax:
Bsp.:
SELECT * | Attributliste
FROM Tabellenname
Stellen Sie alle Daten der Tabelle Techniker dar.
SELECT *
FROM Techniker
Bsp.:
Geben Sie für alle Techniker PNr, Nachname und
Vorname an.
SELECT PNr, Nachname, Vorname
FROM Techniker
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
161
4.4 Datenmanipulationssprache
SELECT- und WHERE- Klausel (II)
• Spalten umbenennen
Der Spaltenname kann zur Ausgabe mit Hilfe des
AS-Operators umbenannt werden.
Bsp.:
Die Spalte PNr soll in der Ausgabe umbenannt werden
in Personalnummer.
SELECT PNr AS Personalnummer, Nachname, Vorname
FROM Techniker
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
162
4.4 Datenmanipulationssprache
SELECT- und WHERE- Klausel (III)
• Spalte mit konstanten Werten anzeigen
In einer eigenen Spalte können konstante Werte angezeigt
werden.
Bsp.:
Es soll eine zusätzliche Spalte angezeigt werden, die
den konstanten Wert Angestellter enthalten und den
Namen Status tragen soll.
SELECT PNr AS Personalnummer, Nachname, Vorname,
'Angestellter' AS Status
FROM Techniker
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
163
4.4 Datenmanipulationssprache
WHERE-Klausel (I)
Syntax:
SELECT * | Attributliste
FROM Tabellenname
[WHERE Bedingung]
Bsp.:
Numerische Werte
Geben Sie diejenigen Techniker aus, die in der
Abteilung 2 arbeiten.
SELECT *
FROM Techniker
WHERE AbtNr = 2
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
164
4.4 Datenmanipulationssprache
WHERE-Klausel (II)
Bsp.:
Alphanumerische Werte
Listen Sie diejenigen Techniker auf, die Maier heißen.
SELECT *
FROM Techniker
WHERE Nachname = 'Maier'
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
165
4.4 Datenmanipulationssprache
WHERE-Klausel – Bedingungsarten (I)
Bedingung
Erläuterung
Beispiel
Vergleichsoperatoren
Vergleicht den Wert eines
Datenfeldes mit einem
vorgegebenen Wert.
Mögliche Vergleichoperatoren:
<, >, <>, =, >=, <=
SELECT *
FROM Techniker
WHERE AbtNr <> 5
Bereichsprüfung
Prüft, ob der Wert eines Feldes SELECT *
innerhalb eines bestimmten
FROM Techniker
Bereichs liegt.
WHERE AbtNr BETWEEN 3 AND 7
BETWEEN … AND …
Elementprüfung
IN (…, …, …)
Prüft, ob der Wert eines Feldes SELECT *
sich in der angegebenen Liste FROM Techniker
befindet.
WHERE Nachname IN ('Maier',
'Müller')
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
166
4.4 Datenmanipulationssprache
WHERE-Klausel – Bedingungsarten (II)
Bedingung
Erläuterung
Beispiel
Mustervergleich
Überprüft einen Feldinhalt auf
Übereinstimmung mit einem
angegebenen Muster.
Trunkierungszeichen: *
SELECT *
FROM Techniker
Prüft einen Feldinhalt auf den
Wert NULL (Datenfeld enthält
keinen Wert).
SELECT *
FROM Waschmaschine
WHERE KundenNr IS NULL
Verknüpfung mehrerer
Bedingungen.
Mögliche logische Operatoren:
AND, OR, NOT
SELECT *
FROM Techniker
LIKE '…'
Nullwertprüfung
IS NULL
Logische
Operatoren
WHERE Nachname LIKE 'M*'
WHERE Nachname LIKE 'M*'
AND (AbtNr = 2 OR AbtNr = 5)
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
167
4.4 Datenmanipulationssprache
WHERE-Klausel – Bedingungsarten (III)
Vergleichsoperatoren
Bsp.: Welche Techniker arbeiten nicht in der Abteilung mit der
Nummer 2?
SELECT *
FROM Techniker
WHERE AbtNr <> 2
Bereichsprüfung
Bsp.: Welche Techniker verdienen pro Stunde zwischen
15,00 € und 17,80 €?
SELECT *
FROM Techniker
WHERE Stundenlohn BETWEEN 15,00 AND 17,80
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
168
Alternative zu S . 168
SELECT * FROM Techniker WHERE Stundenlohn >= 15,00 AND Stundenlohn < = 17,80
(sind 2 voneinander getrennte Bedingungen!)
Techniker 4
PNR
Nachname
Vorname
Stundenlohn
Eintrittsdatum
SELECT * FROM Techniker4 WHERE Eintrittsdatum BETWEEN 2007-01-01 AND
2009-12-31
-Gibt Techniker wieder, die im Jahr 2007 eingestellt wurden.
FALSCH Wäre: ...LIKE '2007' - immer ein Intervall benutzen !
Datumsfeld:
!!!Für die Klausur wäre richtig:
JJJJ-MM-TT
Access
ACCESS:
oder
2008-12-31
#12/31/2008#
oder
31.12.2008
4.4 Datenmanipulationssprache
WHERE-Klausel – Bedingungsarten (IV)
Elementsprüfung
Bsp.: Welche Techniker heißen Maier oder Müller?
SELECT *
FROM Techniker
WHERE Nachname IN ('Maier', 'Müller')
Mustervergleich
Bsp.: Welche Techniker besitzen einen Nachnamen, der mit M
beginnt?
SELECT *
FROM Techniker
WHERE Nachname LIKE 'M*'
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
169
4.4 Datenmanipulationssprache
WHERE-Klausel – Bedingungsarten (V)
Nullwertprüfung
Bsp.: Welche Waschmaschinen sind noch keinem Kunden
zugeordnet?
SELECT *
FROM Waschmaschine
WHERE KundenNr IS NULL
Logische Operatoren
Bsp.: Welche Techniker besitzen einen Nachnamen, der mit M
beginnt und nicht Müller oder Maier ist?
SELECT *
FROM Techniker
WHERE Nachname LIKE 'M*‘ AND NOT (Nachname=‘Müller’ OR
Nachname=‘Maier’)
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
170
4.4 Datenmanipulationssprache
Übungsaufgaben
• Welche Techniker haben einen Stundenlohn, der über 16 €
liegt?
• Welche Techniker haben einen Stundenlohn, der zwischen
14 € und 22,50 € liegt?
• Welche Nachnamen der Techniker enden auf „mann“?
• Welche Techniker heißen Maier bzw. Müller?
• Welche Techniker verdienen mehr als 24,60 € pro Stunde und
arbeiten in der Abteilung 2?
• Welche Techniker heißen nicht Maier oder Müller?
• Welche Techniker arbeiten weder in Abteilung 1 noch in
Abteilung 2?
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
171
4.4 Datenmanipulationssprache
DISTINCT-Befehl
Syntax:
SELECT [DISTINCT] * | Attributliste
FROM Tabellenname
[WHERE Bedingung]
Durch DISTINCT wird die Ausgabe identischer Tupel vermieden.
Bsp.:
Listen Sie alle Nachnamen der Techniker auf, wobei
jeder Nachname nur einmal erscheinen darf.
SELECT DISTINCT Nachname
FROM Techniker
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
172
4.4 Datenmanipulationssprache
ORDER- BY- Klausel (I)
Syntax:
SELECT [DISTINCT] * | Attributliste
FROM Tabellenname
[WHERE Bedingung]
[ORDER BY Attributliste] [ASC|DESC]
Durch die ORDER-BY-Klausel kann die Ausgabe nach bestimmten
Attributen sortiert werden.
Mit dem Schlüsselwort DESC wird absteigend sortiert.
Mit dem Schlüsselwort ASC wird aufsteigend sortiert. Dies ist jedoch
standardmäßig der Fall und muss somit nicht angegeben werden.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
173
4.4 Datenmanipulationssprache
ORDER- BY- Klausel (II)
DESC | ASC bezieht sich immer nur auf ein Attribut.
Bsp.:
Sortieren Sie die Technikerliste nach dem Nachnamen.
SELECT *
FROM Techniker
ORDER BY Nachname
Bsp.:
Sortieren Sie die Technikerliste nach dem Stundenlohn
und anschließend nach dem Nachnamen.
SELECT *
FROM Techniker
ORDER BY Stundenlohn, Nachname
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
174
4.4 Datenmanipulationssprache
ORDER- BY- Klausel (III)
Bsp.:
Sortieren Sie die Technikerliste absteigend nach dem
Nachnamen.
SELECT *
FROM Techniker
ORDER BY Nachname DESC
Bsp.:
Sortieren Sie die Technikerliste absteigend nach dem
Stundenlohn und aufsteigend danach nach dem
Nachnamen.
SELECT *
FROM Techniker
ORDER BY Stundenlohn DESC, Nachname
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
175
4.4 Datenmanipulationssprache
ORDER- BY- Klausel (IV)
Bsp.:
Sortieren Sie die Technikerliste aufsteigend nach dem
Stundenlohn und absteigend danach nach dem
Nachnamen.
SELECT *
FROM Techniker
ORDER BY Stundenlohn, Nachname DESC
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
176
4.4 Datenmanipulationssprache
Aggregatfunktionen (I)
Funktion
Erläuterung
Beispiel
COUNT()
Liefert die Anzahl der Werte
(außer den Wert NULL) in der
Ergebnismenge einer SELECTAbfrage bzw. einer
Gruppierung.
SELECT COUNT(PNr) AS Anzahl
FROM Techniker
AVG ()
Liefert den Durchschnittswert
eines Datenfeldes der Abfrage
oder Gruppierung.
SELECT AVG(Stundenlohn) AS Durchschnitt
FROM Techniker
MIN ()
MAX ()
Liefert den kleinsten bzw.
größten Wert eines Datenfeldes
der Abfrage oder Gruppierung.
SELECT MIN(Stundenlohn) AS Minimum
FROM Techniker
SELECT COUNT(*) AS Anzahl
FROM Techniker
SELECT MAX(Stundenlohn) AS Maximum
FROM Techniker
SUM ()
Liefert die Summe der Werte
eines Datenfeldes in der
Abfrage oder Gruppierung.
SELECT SUM(Stundenlohn) AS
Gesamtstundenlohn
FROM Techniker
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
177
4.4 Datenmanipulationssprache
Aggregatfunktionen (II)
Bsp.:
Wie viele Techniker sind in der Datenbank
gespeichert?
SELECT COUNT(PNr) AS Anzahl
FROM Techniker
Bsp.:
Wie hoch ist der durchschnittliche Stundenlohn?
SELECT AVG(Stundenlohn) AS Durchschnitt
FROM Techniker
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
178
4.4 Datenmanipulationssprache
Aggregatfunktionen (III)
Bsp.:
Wie hoch ist der minimale Stundenlohn?
SELECT MIN(Stundenlohn) AS Minimum
FROM Techniker
Bsp.:
Wie viele Arbeitsstunden wurden insgesamt für
Wartungsarbeiten aufgebracht?
SELECT SUM(Arbeitsstunden) AS Gesamt
FROM Wartung
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
179
4.4 Datenmanipulationssprache
Aggregatfunktionen (IV)
Syntax:
SELECT [DISTINCT] * | Attributliste
FROM Tabellenname
[WHERE Bedingung]
[GROUP BY Attributliste]
[ORDER BY Attributliste] [ASC|DESC]
Mit Hilfe von Aggregatfunktionen können zusätzliche Spalten
ausgegeben werden, in denen ein berechneter Wert dargestellt
wird.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
180
4.4 Datenmanipulationssprache
Aggregatfunktionen (V)
Ist in der SELECT-Klausel mindestens eine Aggregatfunktion
enthalten und wird noch mindestens ein Attribut zusätzlich
ausgewählt, so muss das Schlüsselwort GROUP BY verwendet
werden.
Hierbei muss nach allen weiteren ausgewählten Attributen in der
Attributliste, außer denjenigen, auf die sich die Aggregatfunktion
bezieht, gruppiert werden.
Da sich die Aggregatfunktionen jeweils auf Datensatzgruppen
beziehen, geben sie pro gebildeter Gruppe genau einen Wert
zurück.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
181
4.4 Datenmanipulationssprache
Aggregatfunktionen (VI)
Bsp.:
Listen Sie für die einzelnen Abteilungen anhand der
Abteilungsnummer die Anzahl der Techniker auf.
SELECT AbtNr, COUNT(*) AS Anzahl
FROM Techniker
GROUP BY AbtNr
Bsp.:
Geben Sie pro Abteilungsnummer den
durchschnittlichen Stundenlohn an.
SELECT AbtNr, AVG(Stundenlohn) AS Durchschnitt
FROM Techniker
GROUP BY AbtNr
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
182
4.4 Datenmanipulationssprache
HAVING-Klausel (I)
Syntax:
SELECT [DISTINCT] * | Attributliste
FROM Tabellenname
[WHERE Bedingung]
[GROUP BY Attributliste [HAVING Bedingung]]
[ORDER BY Attributliste] [ASC|DESC]
Die Verwendung der HAVING-Klausel ermöglicht das Definieren
von Gruppenbedingungen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
183
4.4 Datenmanipulationssprache
HAVING-Klausel (II)
Im Gegensatz zu der WHERE-Klausel werden die mit der
HAVING-Klausel angegebenen Bedingungen nicht auf einzelne
Datensätze, sondern auf Datensatzgruppen angewendet.
Es wird eine Auswahlbedingung aus dem Wertebereich der
Aggregatfunktion vorgegeben, auf die sämtliche
Datensatzgruppen der Relation untersucht werden.
Hierbei werden alle Datensatzgruppen, welche die HAVINGBedingung nicht erfüllen, verworfen.
Mögliche Vergleichsoperatoren: <, >, <>, =, >=, <=
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
184
4.4 Datenmanipulationssprache
HAVING-Klausel (III)
Bsp.:
Listen Sie für die einzelnen Abteilungen anhand der
Abteilungsnummer die Anzahl der Techniker auf und
geben Sie hierbei nur jene Abteilungen aus, in denen
mehr als 5 Techniker arbeiten.
SELECT AbtNr, COUNT(*) AS Anzahl
FROM Techniker
GROUP BY AbtNr HAVING COUNT(*) > 5
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
185
4.4 Datenmanipulationssprache
HAVING-Klausel (IV)
Bsp.:
Geben Sie pro Abteilungsnummer den
durchschnittlichen Stundenlohn an. Hierbei sollen nur
jene Abteilungen aufgeführt werden, in denen die
Techniker höchstens einen Durchschnittsstundenlohn
von 21,50 € haben.
SELECT AbtNr, AVG(Stundenlohn) AS Durchschnitt
FROM Techniker
GROUP BY AbtNr HAVING AVG(Stundenlohn) <= 21,50
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
186
4.4 Datenmanipulationssprache
Referentielle Integrität (I)
• Die referentielle Integrität ist eine Konsistenzbedingung,
welche verlangt, dass Fremdschlüsselwerte nur auf Tupel der
referenzierten Relation verweisen, die zur Zeit tatsächlich
existieren.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
187
4.4 Datenmanipulationssprache
Referentielle Integrität (II)
Stundenlohn
Nachname
Techniker
PNr
PNr
1
AbtNr
arbeitet
m
Vorname
Nachname
Vorname
Stundenlohn
Abteilung
Abteilungsname
AbtNr
AbtNr
Abteilungsname
Konsequenz:
Wird bei der einfach-multiplen-Beziehung eine neue Entität
Techniker eingefügt, so ist dies nur möglich, wenn gleichzeitig
auch mindestens eine davon abhängige Entität Abteilung
eingefügt wird.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
188
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – innerer Verbund (I)
• Um Abfragen über mehrere Tabellen durchführen zu können,
müssen die entsprechenden Tabellen miteinander verknüpft
werden.
• Beim Inner-Join (innerer Verbund) wird das kartesische
Produkt über zwei Relationen gebildet, wenn ein oder
mehrere gemeinsame Attribute den gleichen Wert haben.
• In der Relationalen Algebra wird der innere Verbund mit
Verbund bezeichnet.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
189
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – innerer Verbund (II)
• Dieser innere Verbund zweier oder mehrerer Tabellen stellt
die Grundlage für die zuvor in diesem Kapitel dargestellten
Abfragemöglichkeiten dar.
• Alle zu verknüpfenden Tabellennamen werden in die FROMKlausel aufgenommen und jeweils durch ein Komma
voneinander getrennt.
• In die WHERE-Klausel wird die Bedingung eingefügt, dass die
Primärschlüsselwerte der einen Tabelle mit den
Fremdschlüsselwerten der jeweils referenzierten Tabelle
übereinstimmen müssen.
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
190
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – innerer Verbund (III)
Bsp.:
Innerer Verbund zwischen den beiden Relationen
Techniker und Abteilung.
SELECT *
FROM Techniker, Abteilung
WHERE Techniker.AbtNr = Abteilung.AbtNr
Sobald ein Attributname in mehr als einer Relation
vorhanden ist, muss angegeben werden aus welcher
der verwendeten Relationen das Attribut verwendet
werden soll. Dies erfolgt durch:
Tabellenname.Attributname
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
191
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – innerer Verbund (IV)
Bsp.:
Innerer Verbund zwischen den beiden Relationen
Waschmaschine und Techniker.
- Diese beiden Relationen können nicht direkt miteinander
verknüpft werden.
- Hierzu muss zusätzlich die Relation Wartung betrachtet
werden.
SELECT *
FROM Waschmaschine, Wartung, Techniker
WHERE Waschmaschine.SerienNr = Wartung.SerienNr
AND Wartung.PNr = Techniker.PNr
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
192
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – Beispiele (I)
• Erstellen Sie eine Übersicht, aus der hervorgeht, welche
Techniker in welchen Abteilungen arbeiten.
SELECT PNr, Nachname, Vorname, Abteilung.AbtNr, Abteilungsname
FROM Techniker, Abteilung
WHERE Techniker.AbtNr = Abteilung.AbtNr
• Erstellen Sie eine Übersicht, aus der hervorgeht, welche
Techniker in der Abteilung Kundendienst arbeiten.
SELECT PNr, Nachname, Vorname, Abteilung.AbtNr, Abteilungsname
FROM Techniker, Abteilung
WHERE Techniker.AbtNr = Abteilung.AbtNr AND
Abteilungsname = 'Kundendienst'
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
193
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – Beispiele (II)
• Listen Sie alle Kunden auf, die mehr als 7 Waschmaschinen
besitzen.
SELECT Kunde.KundenNr, Vorname, Zuname, Telefonnummer,
COUNT(SerienNr) AS Anzahl
FROM Kunde, Waschmaschine
WHERE Kunde.KundenNr = Waschmaschine.KundenNr
GROUP BY Kunde.KundenNr, Vorname, Zuname, Telefonnummer
HAVING COUNT(SerienNr) > 7
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
194
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – Beispiele (III)
• Geben Sie pro Waschmaschine an, wie hoch der Arbeitslohn
für alle bislang vorgenommenen Wartungen der einzelnen
Maschinen war.
SELECT SerienNr, SUM(Arbeitsstunden*Stundenlohn) AS Arbeitslohn
FROM Techniker, Wartung
WHERE Techniker.PNr = Wartung.PNr
GROUP BY SerienNr
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
195
4.4 Datenmanipulationssprache
Abfragen über mehrere Tabellen – Beispiele (IV)
• Aufgrund einer Rückrufaktion müssen alle Kunden ausfindig
gemacht werden, die über eine Waschmaschine vom Typ
Miele 6100 N verfügen, die im August 2007 gefertigt wurden.
SELECT DISTINCT Kunde.KundenNr, Vorname, Zuname,
Telefonnummer
FROM Kunde,Waschmaschine, Waschmaschinentyp
WHERE Kunde.KundenNr = Waschmaschine.KundenNr AND
Waschmaschine.TypNr = Waschmaschinentyp.TypNr AND
Bezeichnung = 'Miele 6100 N' AND
Herstellungsdatum BETWEEN 01.08.2007 AND 31.08.2007
Prof. Dr. Peter Chamoni - Wirtschaftsinformatik II - Wintersemester 2008/2009
196