SELECT

Vorlesung Datenbanken
2
Wintersemester 2013/14
Das Relationenmodell: Sprachen und Systeme
E.F. Codd, 1970
2.1 Grundlegende Definitionen
Sei U eine endliche, universelle Attributmenge. Sei X eine abzählbare,
universelle Wertemenge. Sei NULL ∈ X ein ausgezeichneter Nullwert.
Sei dom : U → 2X eine Funktion, welche jedem Attribut A ∈ U einen
nichtleeren Wertebereich dom(A) ⊆ X mit NULL ∈ dom(A) zuordnet.
Tupel und Relationen werden analog zu Entities und Entity–Mengen
definiert.
Prof. Dr. Dietmar Seipel
65
Vorlesung Datenbanken
Wintersemester 2013/14
Definition (Tupel, Relation)
1. Ein Tupel t über V ⊆ U ist eine Abbildung, welche jedem Attribut
A ∈ V einen Wert t(A) ∈ dom(A) zuordnet.
def (t) := V heißt Definitionsbereich von t.
Die Projektion t[X] von t auf X ⊆ V ist die Abbildung mit dem
eingeschränkten Definitionsbereich X.
2. Eine Menge r von Tupeln über V heißt Relation über V .
Beispiel (EMPLOYEE) V = { FNAME, MINIT, LNAME, . . . , DNO }
t(FNAME) = ’John’, t(MINIT) = ’B’, t(LNAME) = ’Smith’,
t(SSN) = ’444444444’, . . . , t(SUPERSSN) = ’222222222’, t(DNO) = 5.
Prof. Dr. Dietmar Seipel
FNAME
MINIT
LNAME
SSN
...
SUPERSSN
DNO
John
B
Smith
444444444
...
222222222
5
66
Vorlesung Datenbanken
Wintersemester 2013/14
Definition (Relationenschema)
1. R = (V, K, NOTNULL) heißt Relationenschema mit
K ⊆ NOTNULL ⊆ V ⊆ U
2. def (R) := V heißt auch Definitionsbereich von R.
3. K und NOTNULL spezifizieren intrarelationale Bedingungen für R:
• K heißt Primärschlüssel von R,
• NOTNULL heißt die Menge der NOTNULL–Attribute von R.
Beispiel (EMPLOYEE)
V
=
{ FNAME, MINIT, LNAME, SSN, BDATE,
ADDRESS, SEX, SALARY, SUPERSSN, DNO },
K
=
{ SSN },
NOTNULL
=
{ FNAME, LNAME, SSN, DNO }.
Prof. Dr. Dietmar Seipel
67
Vorlesung Datenbanken
Wintersemester 2013/14
Definition (Relation)
Sei R = (V, K, NOTNULL) ein Relationenschema.
Eine Relation r für R ist eine Menge von Tupeln über V ,
die zusätzlich die intrarelationalen Bedingungen von R erfüllen:
1.
∀ t, t′ ∈ r : t[K] = t′ [K] ⇒ t = t′ ,
2.
∀ t ∈ r ∀A ∈ NOTNULL : t(A) 6= NULL.
Beispiel (EMPLOYEE)
Prof. Dr. Dietmar Seipel
FNAME
MINIT
LNAME
SSN
...
SUPERSSN
DNO
John
B
Smith
444444444
...
222222222
5
...
...
...
...
...
...
...
James
E
Borg
111111111
...
NULL
1
68
Vorlesung Datenbanken
Wintersemester 2013/14
Definition (Fremdschlüsselbedingung)
Sei R = { R1 , . . . , Rn } eine Menge von Relationenschemata
Ri = (Vi , Ki , NOTNULLi )
und R = (V, K, NOTNULL) ein einzelnes Relationenschema. Dann heißt
FK = (R, KF , QFIER, R, DRULE , URULE )
Fremdschlüsselbedingung (foreign key constraint), falls gilt:
1. KF ⊆ V
2. QFIER ∈ { exactly one, at least one, all }
3. DRULE , URULE ∈
{ N O ACTION , S ET N ULL , S ET D EFAULT , C ASCADE }
R heißt referenzierendes Relationenschema, die Ri heißen referenzierte
Relationenschemata.
Prof. Dr. Dietmar Seipel
69
Vorlesung Datenbanken
Wintersemester 2013/14
Eine Teilmenge KF ⊆ V der Attributmenge V von R referenziert die
Schlüssel Ki der Relationenschemata Ri .
R = (V, K, . . . )
KF
^
R1 = (V1 , K1 , . . . ) . . . Rn = (Vn , Kn , . . . )
K1
^
...
Kn
• Sei r eine erlaubte Instanz von R, und
• sei ri eine erlaubte Instanz von Ri , für 1 ≤ i ≤ n.
Ein Tupel t ∈ r referenziert die Relation ri ,
falls es ein Tupel ti ∈ ri gibt, mit t[KF ] = ti [Ki ].
Da Ki der Schlüssel von Ri ist, gibt es zu jedem t ∈ r maximal ein solches
referenziertes Tupel ti ∈ ri .
Prof. Dr. Dietmar Seipel
70
Vorlesung Datenbanken
Wintersemester 2013/14
Ein Tupel t ∈ r referenziert ri , falls es ein Tupel ti ∈ ri gibt, mit
t[KF ] = ti [Ki ].
...
KF
...
t[KF ]
...
K1
...
t1 [K1 ]
Prof. Dr. Dietmar Seipel
...
...
Kn
...
U
tn [Kn ]
71
Vorlesung Datenbanken
Wintersemester 2013/14
Die Fremdschlüsselbedingung FK wird von
• der erlaubten Instanz r von R und
• den erlaubten Instanzen ri von Ri , für 1 ≤ i ≤ n,
erfüllt, falls für alle Tupel t ∈ r gilt:
(∀A ∈ KF : t(A) = NULL) ∨
(QFIER = exactly one ⇒ t referenziert genau ein ri ) ∨
(QFIER = at least one ⇒ t referenziert mindestens ein ri ) ∨
(QFIER = all ⇒ t referenziert alle ri ).
Der Normalfall ist n = 1.
Dann fallen “exactly one”, “at least one” und “all” zusammen.
In S QL können momentan nur Fremdschlüsselbedingungen mit n = 1
referenzierten Relationenschemata spezifiziert werden.
Prof. Dr. Dietmar Seipel
72
Vorlesung Datenbanken
Wintersemester 2013/14
Inklusionsabhängigkeit
Eine Fremdschlüsselbedingung
FK = (R, KF , QFIER, R, DRULE , URULE )
mit nur einem referenzierten Relationenschema
R1 = (V1 , K1 , NOTNULL1 )
ist äquivalent zu einer Inklusionsabhängigkeit, denn für die zugehörigen
erlaubten Instanzen muß folgendes gelten:
ΠKF (r) \ { tNULL } ⊆ ΠK1 (r1 ),
wobei tNULL das Tupel über KF mit lauter Nullwerten ist.
Diese Bedingung nennt man eine Inklusionsabhängigkeit.
Prof. Dr. Dietmar Seipel
73
Vorlesung Datenbanken
Wintersemester 2013/14
Beispiel (COMPANY)
Für die Tabelle E MPLOYEE gilt folgendes:
• Wenn ein Angestellter einen von NULL verschiedenen
S UPERSSN–Eintrag hat, dann muß es genau ein referenziertes Tupel in
E MPLOYEE geben mit diesem S SN–Eintrag.
Wenn ein Angestellter keinen Vorgesetzten hat (so wie der Chef James
E. Borg der Firma), dann kann man NULL eintragen.
• Aufgrund der NOTNULL–Bedingung für D NO muß jeder Angestellte
einer Abteilung zugeordnet sein. Die von NULL verschiedene D NO
referenziert genau ein Tupel in D EPARTMENT mit demselben
D NUMBER–Eintrag.
Da S SN und D NUMBER die Primärschlüssel ihrer Tabellen sind, gibt es
jeweils maximal ein referenziertes Tupel.
Prof. Dr. Dietmar Seipel
74
Vorlesung Datenbanken
Wintersemester 2013/14
E MPLOYEE
FNAME
MINIT
LNAME
SSN
BDATE
ADDRESS
SEX
SALARY
SUPERSSN
DNO
John
B
Smith
444444444
1955-01-09
731 Fondren, Houston, TX
M
30000
222222222
5
Franklin
T
Wong
222222222
1945-12-08
638 Voss, Houston, TX
M
40000
111111111
5
Alicia
J
Zelaya
777777777
1958-07-19
3321 Castle, Spring, TX
F
25000
333333333
4
Jennifer
S
Wallace
333333333
1931-06-20
291 Berry, Bellaire, TX
F
43000
111111111
4
Ramesh
K
Narayan
555555555
1952-09-15
975 Fire Oak, Humble, TX
M
38000
222222222
5
Joyce
A
English
666666666
1962-07-31
5631 Rice, Houston, TX
F
25000
222222222
5
Ahmad
V
Jabbar
888888888
1959-03-29
980 Dallas, Houston, TX
M
25000
333333333
4
James
E
Borg
111111111
1927-11-10
450 Stone, Houston, TX
M
55000
NULL
1
6
D EPARTMENT
DNAME
DNUMBER
MGRSSN
MGRSTARTDATE
Headquarters
1
111111111
1971-06-19
Administration
4
333333333
1985-01-01
Research
5
222222222
1978-05-22
6
Jede D NO–Zelle in E MPLOYEE referenziert genau eine D NUMBER–Zelle in
D EPARTMENT mit demselben Attributwert. Analog für S UPERSSN.
Prof. Dr. Dietmar Seipel
75
Vorlesung Datenbanken
Wintersemester 2013/14
Die Relationship–Typen aus dem ER–Diagramm führen zu folgenden
Fremdschlüsselbedingungen im Relationenmodell:
1. Die 1:N–Beziehung WORKS_FOR, welche als Attribut DNO in die
Relation EMPLOYEE integriert wurde, führt zu
FK1 = ( EMPLOYEE, {DNO}, _, {DEPARTMENT}, _, _ ).
Das Attribut DNO von EMPLOYEE referenziert den Schlüssel
DNUMBER von DEPARTMENT.
2. Die 1:N–Beziehung SUPERVISION, welche als Attribut SUPERSSN in
die Relation EMPLOYEE integriert wurde, führt zu
FK2 = ( EMPLOYEE, {SUPERSSN}, _, {EMPLOYEE}, _, _ ).
Das Attribut SUPERSSN von EMPLOYEE referenziert den Schlüssel
SSN von EMPLOYEE.
Prof. Dr. Dietmar Seipel
76
Vorlesung Datenbanken
Wintersemester 2013/14
3. Die 1:1–Beziehung MANAGES mit Existenzabhängigkeit, welche als
Attribut MGRSSN in die Relation DEPARTMENT integriert wurde,
führt zu
FK3 = ( DEPARTMENT, {MGRSSN}, _, {EMPLOYEE}, _, _ ).
Das Attribut MGRSSN von DEPARTMENT referenziert den Schlüssel
SSN von EMPLOYEE.
4. Die N:M–Beziehung WORKS_ON, welche zu einer eigenen Relation
wurde, führt zu je einer Fremdschlüsselbedingung auf die beteiligten
Relationen:
FK4 = ( WORKS_ON, {ESSN}, _, {EMPLOYEE}, _, _ )
FK5 = ( WORKS_ON, {PNO}, _, {PROJECT}, _, _ )
Das Attribut ESSN von WORKS_ON referenziert den Schlüssel SSN
von EMPLOYEE. Das Attribut PNO von WORKS_ON referenziert den
Schlüssel PNUMBER von PROJECT.
Prof. Dr. Dietmar Seipel
77
Vorlesung Datenbanken
Wintersemester 2013/14
Fremdschlüsselbedingungen mit mehreren referenzierten Relationenschemas
R1 , . . . , Rn treten z.B. auf, wenn ein referenziertes Relationenschema
zerlegt wird.
Beispiel (COMPANY)
Falls man das Relationenschema EMPLOYEE zerlegt in
• ein Schema Ei mit allen internen und
• ein weiteres Schema Ee mit allen externen Angestellten,
so erhält man anstelle von FK4 die neue Fremdschlüsselbedingung FK′4 :
FK′4 = ( WORKS_ON, {ESSN}, exactly one, {Ei , Ee }, _, _ )
Das Attribut ESSN von WORKS_ON referenziert den Schlüssel SSN von Ei
bzw. Ee .
Prof. Dr. Dietmar Seipel
78
Vorlesung Datenbanken
Wintersemester 2013/14
Fremdschlüssel stellen inter–relationale Bedingungen für
Datenbankschemata dar.
Definition (Datenbankschema, Datenbank–Instanz)
1. Ein relationales Datenbankschema S = (R, FK) besteht aus
• einer Menge von Relationenschemata,
R = { R1 , . . . , Rn },
• und einer Menge FK von Fremdschlüsseln auf R.
2. Eine erlaubte Datenbank–Instanz von S ist eine Menge
I = { r1 , . . . , rn }
von Relationen für die Relationenschemata Ri , welche die
Fremdschlüsselbedingungen aus FK erfüllen.
3. Dann ist DB = (S, I) eine relationale Datenbank.
Prof. Dr. Dietmar Seipel
79
Vorlesung Datenbanken
Wintersemester 2013/14
Fremdschlüsselbedingungen sind für Operationen (Löschen, Modifizieren)
auf den referenzierten Relationen relevant.
D ELETE– und U PDATE–Modi für Fremdschlüsselbedingungen
N O ACTION: Es erfolgt keine Aktion.
S ET N ULL, S ET D EFAULT:
Beim Löschen und Update von referenzierten Tupeln werden die Werte
der referenzierenden Attribute auf NULL bzw. auf den DEFAULT–Wert
gesetzt. Dies erfolgt nur, soweit keine NOTNULL–Bedingungen verletzt
werden; ansonsten muß die Operation abgelehnt werden.
C ASCADE:
Beim Löschen von referenzierten Tupeln werden alle referenzierenden
Tupel ebenfalls gelöscht. Beim Update werden die Werte der
referenzierenden Fremdschlüsselattribute auf die neuen Werte der
referenzierten Attribute gesetzt.
Prof. Dr. Dietmar Seipel
80
Vorlesung Datenbanken
Wintersemester 2013/14
Zyklische Fremdschlüsselbedingungen müssen geeignet behandelt werden.
Beispiel (Zyklus)
EMPLOYEE
...
SSN
...
DNO
DEPARTMENT
...
Prof. Dr. Dietmar Seipel
DNUMBER
MGRSSN
...
81
Vorlesung Datenbanken
Wintersemester 2013/14
2.2 Relationale Algebra
äquivalente relationale Anfragesprachen:
• Relationale Algebra
• Relationale Kalküle (Tupelkalküle, Wertebereichskalküle)
Relationale Algebra: 9 Operatoren
• 4 traditionelle Mengenoperatoren:
Vereinigung ∪, Schnitt ∩, Differenz \, kartesisches Produkt ×
• 5 spezifische relationale Operatoren:
Selektion σ, Projektion π, Join ⊲⊳, Division ÷, Umbenennung ̺
Das Ergebnis einer relationalen Operation ist wieder eine Relation,
d.h. die Relationale Algebra ist abgeschlossen.
Prof. Dr. Dietmar Seipel
82
Vorlesung Datenbanken
Wintersemester 2013/14
Kartesisches Produkt
1. Für zwei Tupel t = (t1 , . . . , tn ) und t′ = (t′1 , . . . , t′m ) ist
t × t′ = (t1 , . . . , tn , t′1 , . . . , t′m ).
2. Für zwei Relationen r und s über Vr bzw. Vs betrachten wir das
kartesische Produkt r × s = { t × t′ | t ∈ r, t′ ∈ s } als eine Relation
über der Vereinigung V = Vr ∪· Vs mit Duplikaten;
V ist eine Multimenge, falls Vr ∩ Vs 6= ∅. r × s enthält |r| · |s| Tupel.
r×s
s
A
B
C
D
1
2
2
5
1
2
2
6
1
2
3
7
r
A
Prof. Dr. Dietmar Seipel
C
D
2
5
2
6
5
6
2
5
3
7
5
6
2
6
5
6
3
7
B
1
2
5
6
⇒
83
Vorlesung Datenbanken
Wintersemester 2013/14
Suchprädikate
• Vergleichsoperatoren:
OP = { =, 6=, <, >, ≤, ≥ }
• θ := A ⊙ B, mit A, B ∈ U , ⊙ ∈ OP, ist ein Suchprädikat mit
def (θ) = { A, B }; z.B. θ = (A < B).
Für ein Tupel t mit def (θ) ⊆ def (t) gilt genau dann θ(t) = true,
wenn t(A) ⊙ t(B).
• θ := A ⊙ a, mit A ∈ U, a ∈ dom(A), ⊙ ∈ OP, ist ein Suchprädikat
mit def (θ) = { A }; z.B. θ = (A = 1).
Für ein Tupel t mit def (θ) ⊆ def (t) gilt genau dann θ(t) = true,
wenn t(A) ⊙ a.
Prof. Dr. Dietmar Seipel
84
Vorlesung Datenbanken
Wintersemester 2013/14
• Sind θ, θ1 , θ2 Suchprädikate, dann auch
θ1
AND θ2 ,
θ1
OR θ2 , NOT
θ,
und es gilt def (θ1 AND θ2 ) = def (θ1 OR θ2 ) = def (θ1 ) ∪ def (θ2 ),
def (NOT θ) = def (θ); z.B. θ = (A > 4) AND (B 6= 1).
Für ein Tupel t mit def (θ1 ) ∪ def (θ2 ) ⊆ def (t) gilt
(θ1
(θ1
AND θ2 )(t)
= θ1 (t) AND θ2 (t),
OR θ2 )(t) = θ1 (t) OR θ2 (t).
Für ein Tupel t mit def (θ) ⊆ def (t) gilt
(NOT θ)(t) =
NOT
θ(t).
Für θ = (A ≤ B) AND (C 6= 1) und {A, B, C} ⊆ def (t) gilt
θ(t) = (t(A) ≤ t(B)) AND (t(C) 6= 1).
Der Definitionsbereich def (θ) eines Suchprädikats θ ist also immer die
Menge aller in θ vorkommenden Attribute A ∈ U .
Prof. Dr. Dietmar Seipel
85
Vorlesung Datenbanken
Wintersemester 2013/14
spezifische relationale Operatoren
Sie R die Menge aller Relationen über einer Attributmenge V ⊆ U .
Selektion:
σθ : R −→ R
Falls def (θ) ⊆ def (r), so enthält σθ (r) alle Tupel aus r, welche das
Suchprädikat θ erfüllen:
σθ (r) := { t ∈ r | θ(t) = true }.
Berechnung: |r| Schritte
Projektion:
πV : R −→ R
Falls V ⊆ def (r), so erhalten wir πV (r) durch Projektion aller
Tupel aus r auf V :
πV (r) := { t[V ] | t ∈ r }.
Berechnung: |r| Schritte
Prof. Dr. Dietmar Seipel
86
Vorlesung Datenbanken
Wintersemester 2013/14
Selektion und Projektion:
r
A
σA<B (r)
B
1
2
4
3
5
6
⇒
A
ΠA (r)
A
B
1
2
5
6
1
4
5
Bei der Selektion werden Zeilen der Tabelle weggelassen,
bei der Projektion Spalten.
Prof. Dr. Dietmar Seipel
87
Vorlesung Datenbanken
Wintersemester 2013/14
Verbund (Join, Theta–Join):
⊲⊳θ : R × R −→ R
Sei θ ein Suchprädikat, Vi = def (ri ) und V = V1 ∪· V2 die
Vereinigung mit Duplikaten.
Falls def (θ) ⊆ V , so ist r1 ⊲⊳θ r2 die Relation über V mit allen
Tupeln t des kartesischen Produkts r1 × r2 , welche θ erfüllen:
r1 ⊲⊳θ r2 := { t ∈ r1 × r2 | θ(t) = true } = σθ (r1 × r2 ).
bei lauter Gleichheitsoperatoren in θ: Equi–Join.
Berechnung (Nested Loop–Join):
• Finde zu jedem Tupel t1 ∈ r1 alle Tupel t2 ∈ r2 , so daß
t = t1 × t2 das Suchprädikat θ erfüllt. −→ |r1 | · |r2 | Schritte
• Ein Equi–Join über eine Fremdschlüssel/Schlüssel–Beziehung
kann den Index über r2 verwenden. −→ |r1 | · log2 |r2 | Schritte
Beispiel: EMPLOYEE ⊲⊳DNO=DNUMBER DEPARTMENT
Prof. Dr. Dietmar Seipel
88
Vorlesung Datenbanken
Wintersemester 2013/14
Equi–Join:
s
r
C
A
r ⊲⊳B=C s
D
B
1
2
3
4
5
6
2
5
2
6
3
7
4
8
⇒
A
B
C
D
1
2
2
5
1
2
2
6
3
4
4
8
Das kartesische Produkt von r und s hat 3 · 4 = 12 Tupel.
Nur 3 davon kommen in den Equi–Join, denn dafür muß B = C sein.
Prof. Dr. Dietmar Seipel
89
Vorlesung Datenbanken
Wintersemester 2013/14
Natural Join: Join über gemeinsame Attribute
Sie Vi = def (ri ) und V = V1 ∪ V2 die Vereinigung ohne Duplikate.
Falls def (θ) ⊆ V , so ist der Natural Join r1 ⊲⊳ r2 die Menge aller
Tupel über V , deren Projektionen auf Vi in ri liegen:
r1 ⊲⊳ r2 := { t | def (t) = V ∧ t[V1 ] ∈ r1 ∧ t[V2 ] ∈ r2 }.
Zwei Tupel ti ∈ ri werden zu einem Tupel t ∈ r1 ⊲⊳ r2 verbunden,
falls sie auf den gemeinsamen Attributen übereinstimmen:
t1 [V1 ∩ V2 ] = t2 [V1 ∩ V2 ] :


für A ∈ V1 \ V2 ,

 t1 (A),
t(A) =
t2 (A),
für A ∈ V2 \ V1 ,



t1 (A) = t2 (A), für A ∈ V1 ∩ V2 .
r1 ⊲⊳ r2 entspricht einem Equi–Join mit Gleichheitsprädikaten über
V1 ∩ V2 gefolgt von einer Projektion zur Elimination doppelter Attribute.
Prof. Dr. Dietmar Seipel
90
Vorlesung Datenbanken
Wintersemester 2013/14
Der Natural Join entspricht einem Equi–Join mit Projektion:
r ⊲⊳ s′ = ΠA,r.B,D (r ⊲⊳ r.B=s′ .B s′ ).
s′
r ⊲⊳ r.B=s′ .B s′
r
B
A
D
B
1
2
3
4
5
6
2
5
2
6
3
7
4
8
⇒
r ⊲⊳ s′
A
B
B
D
A
B
D
1
2
2
5
1
2
5
1
2
2
6
1
2
6
3
4
4
8
3
4
8
Der Equi–Join r ⊲⊳ r.B=s′ .B s′ ohne Projektion würde hingegen eine
Relation erzeugen, in der das Attribut B doppelt vorkommt.
Wir können kurz ΠA (r) für Π{A} (r) und ΠA1 ,... ,An (r) für Π{A1 ,... ,An } (r)
schreiben.
Prof. Dr. Dietmar Seipel
91
Vorlesung Datenbanken
Wintersemester 2013/14
Outer Join:
Tupel aus r bzw. s, die keinen Join–Partner finden, werden durch
NULL–Werte aufgefüllt.
A
B
C
D
1
2
2
5
1
2
2
6
3
4
4
8
NULL
NULL
3
7
5
6
NULL
NULL
Durch Projektion des Outer Join u auf die beiden Attributmengen
Vr bzw. Vs und Elimination der reinen NULL–Tupel erhält man
wieder die ursprünglichen Relationen:
r = σA6=NULL OR B6=NULL (ΠA,B (u)),
s = σC6=NULL OR D6=NULL (ΠC,D (u)).
Prof. Dr. Dietmar Seipel
92
Vorlesung Datenbanken
Division (Faktorisierung):
Wintersemester 2013/14
÷ : R × R −→ R
Falls def (s) ⊆ def (r), so definieren wir r ÷ s als die maximale
Relation über def (r) \ def (s), deren kartesisches Produkt mit s in r
enthalten ist: (r ÷ s) × s ⊆ r (vgl. ganzzahlige Division).
Mathematische Formalisierung (Exkurs):
r ÷ s := { t | def (t) = def (r) \ def (s) ∧ ∀u ∈ s : t × u ∈ r },
wobei (t1 , . . . , tn ) × (s1 , . . . , sm ) = (t1 , . . . , tn , s1 , . . . , sm ).
Für r, s ∈ IN+ ist t = r ÷ s die maximale natürliche Zahl, so daß t × s ≤ r.
Umbenennung (Renaming):
̺Θ : R −→ R
Θ = { A1 7→ B1 , . . . , Ak 7→ Bk } ist eine Umbenennung der
Attribute. Falls V = { A1 , . . . , Ak } ⊆ def (r), so erhält man die
Relation ̺Θ (r), indem jedes Attribut Ai in Bi umbenannt wird;
Attribute A von r, mit A 6∈ V , bleiben unverändert.
Prof. Dr. Dietmar Seipel
93
Vorlesung Datenbanken
r=
r=
Wintersemester 2013/14
A
B
C
7
1
2
7
3
4
9
1
2
A
B
C
7
1
2
7
3
4
9
1
2
9
3
4
s=
s=
B
C
1
2
3
4
B
C
1
2
3
4
⇒ r÷s=
A
7
A
⇒ r÷s=
7
9
Für ganze Zahlen gilt z.B. 3 ÷ 2 = 1 und 4 ÷ 2 = 2.
Prof. Dr. Dietmar Seipel
94
Vorlesung Datenbanken
Wintersemester 2013/14
Beispiel (COMPANY)
Finde die Namen aller Angestellten heraus, die an allen Projekten
mitarbeiten, welche von Abteilung Nummer 5 kontrolliert werden:
r1
=
̺{PNUMBER7→PNO} ( ΠPNUMBER (σDNUM = 5 (PROJECT))),
r2
=
ΠESSN,PNO (WORKS_ON),
r3
=
ΠFNAME,LNAME (EMPLOYEE ⊲⊳SSN=ESSN (r2 ÷ r1 )).
r2
ESSN
PNO
r1
222222222
2
PNO
444444444
1
r2 ÷ r1
444444444
2
ESSN
FNAME
LNAME
1
444444444
3
3
444444444
John
Smith
2
555555555
666666666
1
666666666
Joyce
English
666666666
2
666666666
3
666666666
10
3
r3
Der Join zur Berechnung von r3 könnte nach Umbenennung von r2 ÷ r1
mittels {ESSN 7→ SSN} auch als Natural Join geschrieben werden.
Prof. Dr. Dietmar Seipel
95
Vorlesung Datenbanken
Wintersemester 2013/14
Einfache Optimierung in der relationalen Algebra
Selektion und Projektion sollten in Ausdrücken der relationalen Algebra
frühzeitig angewendet werden, da sie die Anzahl der Tupel einer Relation
unter Umständen deutlich verkleinern können:
r
s
A
B
A
C
a
b1
a
c1
...
...
...
...
a
bn
a
cn
ΠA (r)
⇒
σC=ci (s)
A
A
C
a
a
ci
Der Join r ⊲⊳ s enthält n2 Tupel, aber
s′ = σC=ci (ΠA,C (r ⊲⊳ s)) = ΠA,C (r ⊲⊳ σC=ci (s)) = ΠA (r) ⊲⊳ σC=ci (s)
kann aus ΠA (r) und σC=ci (s) berechnet werden, ohne daß man r ⊲⊳ s
berechnen muß. Hier enthält s′ nur 1 Tupel, denn s′ = σC=ci (s).
Prof. Dr. Dietmar Seipel
96
Vorlesung Datenbanken
Wintersemester 2013/14
2.3 Die relationale Datenbanksprache SQL
Relationenalgebra
Der Benutzer muß beim Formulieren der Anfrage angeben, auf welche Weise
das gewünschte Resultat berechnet werden soll.
Deklarative High–Level–Sprachschnittstellen
Der Benutzer gibt auf sehr abstrakte Weise an, wie das gewünschte Resultat
der Anfrage aussehen soll. Der Ausführungsplan inklusive der
Anfrageoptimierung wird vom System erstellt.
ANSI–Standards:
SQL1: 1986
SQL2: 1992
SQL3: objektorientierte Erweiterungen, . . .
Prof. Dr. Dietmar Seipel
97
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.1 Datendefinition in SQL
Im Großen und Ganzen kann man einen ER–Entwurf in SQL mittels
C REATE TABLE–Statements umsetzen:
1. Für jedes Attribut einer Tabelle wird auch ein Datentyp angegeben, und
möglicherweise eine NOT NULL–Bedingung und ein DEFAULT–Wert.
2. Eine Attributkombination K wird mittels P RIMARY K EY (K) zum
primären Schlüssel mit automatischer NOT NULL–Bedingung gemacht.
Nach diesem werden die Datensätze auf dem Sekundärspeicher
organisiert, und es wird automatisch ein Index angelegt.
3. Weitere Attributkombinationen X können mittels U NIQUE (X) zu
sekundären Schlüsseln gemacht werden. NOT NULL–Bedingungen
müssen hierfür bei Bedarf explizit angegeben werden.
Prof. Dr. Dietmar Seipel
98
Vorlesung Datenbanken
Wintersemester 2013/14
4. Fremdschlüsselbedingungen von einer Attributkombinationen X auf
einen Schlüssel K einer Tabelle T werden mittels
F OREIGN K EY (X) R EFERENCES T (K)
vereinbart.
Erzeugen einer Datenbank
Vor dem erste C REATE TABLE–Statement muß man die Datenbank
erzeugen und eine Verbindung damit aufbauen:
CREATE DATABASE COMPANY;
USE COMPANY;
Alle folgenden S QL–Statements richten sich dann an diese Datenbank.
Prof. Dr. Dietmar Seipel
99
Vorlesung Datenbanken
Wintersemester 2013/14
Erzeugen einer Tabelle
CREATE TABLE EMPLOYEE (
FNAME
VARCHAR(15) NOT
MINIT
CHAR,
LNAME
VARCHAR(15) NOT
SSN
CHAR(9)
NOT
BDATE
DATE,
ADDRESS VARCHAR(30),
SEX
CHAR,
SALARY
DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO
INT
NOT
Prof. Dr. Dietmar Seipel
NULL,
NULL,
NULL,
NULL,
100
Vorlesung Datenbanken
Wintersemester 2013/14
PRIMARY KEY (SSN),
FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER) );
Durch die NOT NULL–Bedingung für DNO wird die Existenzabhängigkeit
der Angestellten von den Abteilungen aus WORKS_FOR realisiert.
Die umgekehrte Existenzabhängigkeit kann im CREATE TABLE–Statement
nicht realisiert werden.
Mittels UNIQUE (FNAME,MINIT,LNAME,BDATE) könnte man die
Kombination aus dem Namen und dem Geburtsdatum zu einem weiteren,
sekundären Schlüssel machen.
Prof. Dr. Dietmar Seipel
101
Vorlesung Datenbanken
Wintersemester 2013/14
Syntax
CREATE TABLE table-name (
column-name type [ NOT NULL | UNIQUE ]
[ DEFAULT { literal | NULL } ]
[, column-name . . . ]
[, UNIQUE ( list-of-column-names ) . . . ]
[, PRIMARY KEY ( list-of-column-names ) ]
[, FOREIGN KEY ( list-of-column-names )
REFERENCES table-name
[ ( list-of-column-names ) ] . . . ]
[, CHECK ( condition ) . . . ] )
Prof. Dr. Dietmar Seipel
102
Vorlesung Datenbanken
Wintersemester 2013/14
FOREIGN KEY ( list-of-column-names )
REFERENCES table-name
[ ( list-of-column-names ) ]
[ MATCH { FULL | PARTIAL } ]
[ ON DELETE
{ NO ACTION | CASCADE |
SET DEFAULT | SET NULL } ]
[ ON UPDATE
{ NO ACTION | CASCADE |
SET DEFAULT | SET NULL } ]
Prof. Dr. Dietmar Seipel
103
Vorlesung Datenbanken
Wintersemester 2013/14
Datentypen
1. numerisch:
INT[EGER], SMALLINT
FLOAT, REAL, DOUBLE PRECISION
NUMERIC(I,J), DEC[IMAL](I,J)
precision: I, scale: J (default: J=0),
Beispiel: 1.43 ⇒ I=3, J=2
2. Character String:
CHAR[ACTER](N): feste Länge N, der Default ist N=1
VARCHAR(N), CHAR[ACTER] VARYING(N): N ist das Maximum
3. Bit String:
BIT(N) (default: N=1)
BIT VARYING(N)
Prof. Dr. Dietmar Seipel
104
Vorlesung Datenbanken
Wintersemester 2013/14
4. Datum und Zeit:
DATE:
YYYY-MM-DD
TIME:
HH:MM:SS
TIME(I):
HH:MM:SS:F1 . . . FI
TIME WITH TIME ZONE:
08:32:16+01:00
TIMESTAMP:
DATE TIME(6)
TIMESTAMP WITH TIME ZONE
INTERVAL:
YYYY-MM oder DD TIME
Character Strings sowie Datum und Zeit werden mit Hochkommatas
angegeben (z.B.: ’John’, ’Smith’, ’1955-01-09’), Zahlenwerte ohne
Hochkommatas (z.B.: 30000, 5).
Prof. Dr. Dietmar Seipel
105
Vorlesung Datenbanken
Wintersemester 2013/14
Das Erstellen der kompletten COMPANY–Datenbank
CREATE TABLE DEPARTMENT (
DNAME
VARCHAR(15)
DNUMBER
INT
MGRSSN
CHAR(9)
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN)
REFERENCES EMPLOYEE(SSN)
NOT NULL,
NOT NULL,
NOT NULL,
);
Durch die NOT NULL–Bedingung für MGRSSN wird die
Existenzabhängigkeit aus MANAGES realisiert, die besagt, daß eine
Abteilung genau einen Manager haben muß.
Prof. Dr. Dietmar Seipel
106
Vorlesung Datenbanken
Wintersemester 2013/14
CREATE TABLE DEPT_LOCATIONS (
DNUMBER
INT
NOT NULL,
DLOCATION VARCHAR(15) NOT NULL,
PRIMARY KEY (DNUMBER, DLOCATION),
FOREIGN KEY (DNUMBER)
REFERENCES DEPARTMENT(DNUMBER)
ON DELETE CASCADE ON UPDATE CASCADE );
Die Fremdschlüsselbedingung ist mit einem Trigger verbunden, der bei
Löschungen und Updates in DEPARTMENT aktiv wird.
Prof. Dr. Dietmar Seipel
107
Vorlesung Datenbanken
Wintersemester 2013/14
CREATE TABLE PROJECT (
PNAME
VARCHAR(15) NOT NULL,
PNUMBER
INT
NOT NULL,
PLOCATION VARCHAR(15),
DNUM
INT
NOT NULL,
PRIMARY KEY (PNUMBER),
UNIQUE (PNAME),
FOREIGN KEY (DNUM)
REFERENCES DEPARTMENT(DNUMBER) );
Durch die NOT NULL–Bedingung für DNUM wird die
Existenzabhängigkeit aus CONTROLS realisiert, die besagt, daß ein Projekt
genau eine verantwortliche Abteilung haben muß.
Prof. Dr. Dietmar Seipel
108
Vorlesung Datenbanken
Wintersemester 2013/14
CREATE TABLE WORKS_ON (
ESSN
CHAR(9)
NOT NULL,
PNO
INT
NOT NULL,
HOURS DECIMAL(3,1) NOT NULL,
PRIMARY KEY (ESSN, PNO),
FOREIGN KEY (ESSN)
REFERENCES EMPLOYEE(SSN),
FOREIGN KEY (PNO)
REFERENCES PROJECT(PNUMBER) );
Die beiden Existenzabhängigkeiten aus WORKS_ON können in den
CREATE TABLE–Statements nicht realisiert werden.
Prof. Dr. Dietmar Seipel
109
Vorlesung Datenbanken
Wintersemester 2013/14
CREATE TABLE DEPENDENT (
ESSN
CHAR(9) NOT NULL,
DEPENDENT_NAME VARCHAR(15) NOT NULL,
SEX
CHAR,
BDATE
DATE,
RELATIONSHIP
VARCHAR(8),
PRIMARY KEY (ESSN, DEPENDENT_NAME),
FOREIGN KEY (ESSN)
REFERENCES EMPLOYEE(SSN) );
Prof. Dr. Dietmar Seipel
110
Vorlesung Datenbanken
Wintersemester 2013/14
Namen und Trigger für Constraints
CREATE TABLE EMPLOYEE (
...
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK PRIMARY KEY (SSN),
CONSTRAINT EMPSUPERFK FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN)
ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT EMPDEPTFK FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER)
ON DELETE SET DEFAULT ON UPDATE CASCADE );
Beim Löschen einer Abteilung wird die DNO der zugehörigen Angestellten
auf den Default–Wert 1 gesetzt, beim Update der DNUMBER in
DEPARTMENT wird die DNO der zugehörigen Angestellten aktualisiert.
Prof. Dr. Dietmar Seipel
111
Vorlesung Datenbanken
Wintersemester 2013/14
CREATE TABLE DEPARTMENT (
...
MGRSSN CHAR(9) NOT NULL DEFAULT ’111111111’,
CONSTRAINT DEPTPK PRIMARY KEY (DNUMBER),
CONSTRAINT DEPTSK UNIQUE (DNAME),
CONSTRAINT DEPTMGRFK FOREIGN KEY (MGRSSN)
REFERENCES EMPLOYEE(SSN)
ON DELETE SET DEFAULT ON UPDATE CASCADE );
Man kann später bei Änderungen an den Tabellenstrukturen über die Namen
auf die Constraints zugreifen.
Prof. Dr. Dietmar Seipel
112
Vorlesung Datenbanken
Wintersemester 2013/14
Typ–Definition
CREATE DOMAIN SSN_TYPE AS CHAR(9);
Schema–Modifikationen
DROP DATABASE COMPANY
CASCADE
RESTRICTED (nur für leeres Schema)
DROP TABLE DEPENDENT
CASCADE
RESTRICTED (nur für nicht referenzierte Tabellen)
Prof. Dr. Dietmar Seipel
113
Vorlesung Datenbanken
Wintersemester 2013/14
ALTER TABLE table-name
{ ADD [ COLUMN ] column-name data-type
| ALTER [ COLUMN ] column-name
{ SET default-definition | DROP DEFAULT }
| DROP [ COLUMN ] column-name
| ADD [ CONSTRAINT constraint-name ]
{ { PRIMARY KEY | UNIQUE }
( list-of-column-names )
| FOREIGN KEY ( list-of-column-names )
REFERENCES . . .
| CHECK ( condition ) }
| DROP CONSTRAINT constraint-name }
Prof. Dr. Dietmar Seipel
114
Vorlesung Datenbanken
Wintersemester 2013/14
Beispiel
ALTER TABLE EMPLOYEE
ADD JOB VARCHAR(12);
ALTER TABLE EMPLOYEE
DROP ADDRESS CASCADE;
ALTER TABLE EMPLOYEE
DROP CONSTRAINT EMPSUPERFK CASCADE;
ALTER TABLE DEPARTMENT
ALTER MGRSSN DROP DEFAULT;
ALTER TABLE DEPARTMENT
ALTER MGRSSN SET DEFAULT ’222222222’;
Prof. Dr. Dietmar Seipel
115
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.2 Anfragen in SQL
S ELECT–F ROM –W HERE–Block:
Verglichen mit der Relationenalgebra realisiert der SELECT–Teil eine
Projektion auf die interessierenden Attribute, der FROM–Teil realisiert den
Join der genannten Tabellen, und der WHERE–Teil enthält die Selektions–
und Join–Bedingungen.
SELECT
<attribute list>
→ Resultatsattribute
FROM
<table list>
→ angefragte Tabellen
WHERE
<condition>
→ Bedingung an die Resultatstupel
Joins erfolgen oft über Schlüssel/Fremdschlüssel–Beziehungen; dies hilft
beim Finden geeigneter Join–Bedingungen. Außerdem können die
Join–Partner zu Tupeln aus der Tabelle mit dem Fremdschlüssel in der
Tabelle mit dem Schlüssel sehr schnell gefunden werden.
Prof. Dr. Dietmar Seipel
116
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 0 (Selektion und Projektion)
Finde das Geburtsdatum und die Addresse des Angestellten mit dem Namen
“John B. Smith” heraus.
S ELECT
BDATE, ADDRESS
F ROM
EMPLOYEE
W HERE
FNAME=’John’ A ND MINIT=’B’ A ND LNAME=’Smith’
Ergebnis:
BDATE
ADDRESS
1955-01-09
731 Fondren, Houston, TX
In der Relationenalgebra:
ΠB DATE , A DDRESS (σF NAME=’John’ ∧ M INIT=’B’ ∧ L NAME=’Smith’ (EMPLOYEE)).
Prof. Dr. Dietmar Seipel
117
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage (Projektion und Join)
Bestimme für alle Angestellten – gegeben durch Vornamen und
Nachnamen – ihre Projekte – gegeben durch die Projektnamen –
und die Anzahl der daran gearbeiteten Stunden.
S ELECT
FNAME, LNAME, PNAME, HOURS
F ROM
EMPLOYEE, WORKS_ON, PROJECT
W HERE
SSN=ESSN A ND PNO=PNUMBER
Jeder Tabellenname im F ROM–Teil steht für ein Tupel.
E MPLOYEE
FNAME
...
LNAME
W ORKS _O N
ESSN
PNO
HOURS
P ROJECT
PNAME
Prof. Dr. Dietmar Seipel
PNUMBER
...
SSN
...
In der Relationenalgebra:
ΠFNAME, L NAME, PNAME, HOURS (
E MPLOYEE ⊲⊳ SSN = E SSN
W ORKS _O N ⊲⊳ PNO = PNUMBER P ROJECT ).
118
Vorlesung Datenbanken
Wintersemester 2013/14
Für jedes Tripel von Tupeln, welches die W HERE–Bedingung erfüllt, kann man
durch Projektion auf die Attribute aus dem S ELECT–Teil ein Resultatstupel bilden.
Prof. Dr. Dietmar Seipel
FNAME
LNAME
PNAME
HOURS
John
Smith
ProductX
32.5
John
Smith
ProductY
7.5
Ramesh
Narayan
ProductZ
40.0
Joyce
English
ProductX
20.0
Joyce
English
ProductY
20.0
Franklin
Wong
ProductY
10.0
Franklin
Wong
ProductZ
10.0
Alicia
Zelaya
Newbenefits
30.0
Alicia
Zelaya
Computerization
10.0
Ahmad
Jabbar
Computerization
35.5
Ahmad
Jabbar
Newbenefits
5.0
Jennifer
Wallace
Newbenefits
20.0
Jennifer
Wallace
Reorganization
15.0
James
Borg
Reorganization
NULL
119
Vorlesung Datenbanken
Wintersemester 2013/14
Mit binären Joins in der Relationenalgebra:
ΠF NAME , L NAME , P NAME , H OURS (
E MPLOYEE ⊲⊳ S SN = E SSN ( W ORKS _O N ⊲⊳ P NO = P NUMBER P ROJECT ) ).
Zugehöriger Operatorbaum:
O1 :
ΠF NAME , L NAME , P NAME , H OURS
6
⊲⊳ S SN = E SSN
I
E MPLOYEE
⊲⊳ P NO = P NUMBER
I
W ORKS _O N
Prof. Dr. Dietmar Seipel
P ROJECT
120
Vorlesung Datenbanken
Wintersemester 2013/14
Mit binären Joins in der Relationenalgebra:
ΠF NAME , L NAME , P NAME , H OURS (
( E MPLOYEE ⊲⊳ S SN = E SSN W ORKS _O N ) ⊲⊳ P NO = P NUMBER P ROJECT ).
Zugehöriger Operatorbaum:
O2 :
ΠF NAME , L NAME , P NAME , H OURS
6
⊲⊳ P NO = P NUMBER
I
⊲⊳ S SN = E SSN
P ROJECT
I
E MPLOYEE
Prof. Dr. Dietmar Seipel
W ORKS _O N
121
Vorlesung Datenbanken
Wintersemester 2013/14
Das folgende Mengendiagramm zeigt die Attributmengen der beteiligten
Relationen. Es ist angereichert um die Join–Bedingungen (in blau und rot).
Es sind nur die Projektions– bzw. Join–Attribute angezeigt.
W ORKS _O N
E MPLOYEE
P ROJECT
L NAME
S SN
E SSN
P NO
F NAME
P NUM BER
P NAME
H OURS
Der Join sollte immer von der zentralen Relation W ORKS _O N ausgehen.
Es ist sinnlos, zuerst E MPLOYEE und P ROJECT zu joinen, denn dies wäre ein
kartesisches Produkt.
Hier erscheint auch ein ternärer (Drei–Wege–) Join sinnvoll, bei dem in
einem Durchlauf für jedes Tupel aus W ORKS _O N indexunterstützt die
passenden Partner aus den anderen beiden Relationen gesucht werden.
Prof. Dr. Dietmar Seipel
122
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 1 (Selektion, Projektion und Join)
Finde die Namen und Addressen aller Angestellten heraus, die für die
“Research”–Abteilung arbeiten.
S ELECT
F ROM
W HERE
Ergebnis:
FNAME, LNAME, ADDRESS
EMPLOYEE, DEPARTMENT
DNAME=’Research’ A ND DNUMBER=DNO
FNAME
LNAME
ADDRESS
John
Smith
731 Fondren, Houston, TX
Franklin
Wong
638 Voss, Houston, TX
Ramesh
Narayan
975 Rice, Houston, TX
Joyce
English
5631 Rice, Houston, TX
In der Relationenalgebra:
ΠF NAME , L NAME , A DDRESS ( σD NAME=’Research’ (
E MPLOYEE ⊲⊳D NUMBER =D NO D EPARTMENT ) ).
Prof. Dr. Dietmar Seipel
123
Vorlesung Datenbanken
Wintersemester 2013/14
In der Relationenalgebra:
ΠF NAME , L NAME , A DDRESS ( σD NAME=’Research’ (
E MPLOYEE ⊲⊳D NUMBER =D NO D EPARTMENT ) ).
Zugehöriger Operatorbaum:
O1 :
ΠF NAME , L NAME , A DDRESS
6
σD NAME=’Research’
6
⊲⊳D NUMBER =D NO
I
E MPLOYEE
Prof. Dr. Dietmar Seipel
D EPARTMENT
124
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage–Optimierung: Push Selection into Join
ΠF NAME , L NAME , A DDRESS (
E MPLOYEE ⊲⊳D NUMBER =D NO σD NAME=’Research’ (D EPARTMENT) ).
Zugehöriger Operatorbaum:
O2 :
ΠF NAME , L NAME , A DDRESS
6
⊲⊳D NUMBER =D NO
I
E MPLOYEE
σD NAME=’Research’
6
D EPARTMENT
Prof. Dr. Dietmar Seipel
125
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 2
Liste die Projektnummer, die Nummer der zugehörigen Abteilung sowie den
Nachnamen, die Addresse und das Geburtsdatum des Abteilungsleiters für
alle Projekte, die in Stafford angesiedelt sind.
S ELECT
PNUMBER, DNUM, LNAME, ADDRESS, BDATE
F ROM
PROJECT, DEPARTMENT, EMPLOYEE
W HERE
DNUM=DNUMBER A ND MGRSSN=SSN
A ND
PLOCATION=’Stafford’
Ergebnis:
Prof. Dr. Dietmar Seipel
PNUMBER
DNUM
LNAME
ADDRESS
BDATE
10
4
Wallace
291 Berry, Bellaire, TX
1931-06-20
30
4
Wallace
291 Berry, Bellaire, TX
1931-06-20
126
Vorlesung Datenbanken
Wintersemester 2013/14
Zusammenhang mit der Relationenalgebra
S ELECT D ISTINCT eliminiert doppelte Tupel aus dem Resultat
1. Projektion: πA1 , ... ,Ak (R), mit {A1 , . . . , Ak } ⊆ def (R):
S ELECT D ISTINCT A1 , . . . , Ak F ROM R
2. Selektion: σA=B (R), mit A, B ∈ def (R):
S ELECT D ISTINCT *
F ROM R
W HERE A = B
Prof. Dr. Dietmar Seipel
127
Vorlesung Datenbanken
Wintersemester 2013/14
3. Vereinigung | Durchschnitt | Differenz: R ∪ S, R ∩ S, R \ S
S ELECT D ISTINCT * F ROM R
U NION | I NTERSECT | E XCEPT
S ELECT D ISTINCT * F ROM S
4. Natural Join: R ⊲⊳ S, mit def (R) ∩ def (S) = {B1 , . . . , Bm } :
def (R) = {A1 , . . . , An , B1 , . . . , Bm },
def (S) = {B1 , . . . , Bm , C1 , . . . , Cl }
S ELECT D ISTINCT A1 , . . . , An , R.B1 , . . . , R.Bm , C1 , . . . , Cl
F ROM R, S
W HERE R.B1 = S.B1 A ND . . . A ND R.Bm = S.Bm
Prof. Dr. Dietmar Seipel
128
Vorlesung Datenbanken
Wintersemester 2013/14
Mehrdeutige Attributnamen und Aliasing
Um Mehrdeutigkeiten zu beseitigen,
• kann man einen Attributnamen A durch Voranstellen des
Relationennamens R eindeutig machen (R.A), und
• man kann ein Alias R′ für einen Relationennamen R vergeben (R R′ ,
z.B. EMPLOYEE E).
Beides ist z.B. erforderlich, wenn man eine Relation mit sich selbst joint.
Anfrage 8
Prof. Dr. Dietmar Seipel
S ELECT
E.FNAME, E.LNAME, S.FNAME, S.LNAME
F ROM
EMPLOYEE E, EMPLOYEE S
W HERE
E.SUPERSSN=S.SSN
129
Vorlesung Datenbanken
Wintersemester 2013/14
Die Vorgesetzten–Hierarchie:
111111111
1: James Borg
j
222222222
5: Franklin Wong
444444444
5: John Smith
Prof. Dr. Dietmar Seipel
?
555555555
5: Ramesh Narayan
333333333
4: Jennifer Wallace
j
666666666
5: Joyce English
777777777
4: Alicia Zelaya
R
888888888
4: Ahmad Jabbar
130
Vorlesung Datenbanken
Wintersemester 2013/14
E MPLOYEE
FNAME
MINIT
LNAME
SSN
...
SUPERSSN
...
E
John
B
Smith
444444444
...
222222222
...
S
Franklin
T
Wong
222222222
...
111111111
...
...
...
...
...
...
...
...
Ergebnis:
Prof. Dr. Dietmar Seipel
E.FNAME
E.LNAME
S.FNAME
S.LNAME
John
Smith
Franklin
Wong
Franklin
Wong
James
Borg
Alica
Zelaya
Jennifer
Wallace
Jennifer
Wallace
James
Borg
Ramesh
Narayan
Franklin
Wong
Joyce
English
Franklin
Wong
Ahmad
Jabbar
Jennifer
Wallace
131
Vorlesung Datenbanken
Wintersemester 2013/14
Anfragen ohne WHERE–Klausel, Benutzung von ’*’ (Wildcard)
Anfrage 9 (Projektion)
Suche alle EMPLOYEE SSNs in der Datenbank.
S ELECT
F ROM
SSN
EMPLOYEE
Ergebnis:
SSN
444444444
222222222
777777777
333333333
in der Relationenalgebra:
ΠSSN (EMPLOYEE)
555555555
666666666
888888888
111111111
Prof. Dr. Dietmar Seipel
132
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 1C (Selektion)
S ELECT
*
F ROM
EMPLOYEE
W HERE
DNO = 5
Ergebnis:
FNAME
MINIT
LNAME
SSN
BDATE
ADDRESS
SEX
SALARY
SUPERSSN
DNO
John
B
Smith
444444444
1955-01-09
...
M
30000
222222222
5
Franklin
T
Wong
222222222
1945-12-08
...
M
40000
111111111
5
Ramesh
K
Narayan
555555555
1952-09-15
...
M
38000
222222222
5
Joyce
A
English
666666666
1962-07-31
...
F
25000
222222222
5
in der Relationenalgebra:
Prof. Dr. Dietmar Seipel
σDNO=5 (EMPLOYEE)
133
Vorlesung Datenbanken
Wintersemester 2013/14
Tabellen als Mengen
Anfrage 11
Hole das Gehalt aller Angestellten aus der Datenbank.
S ELECT
SALARY
F ROM
EMPLOYEE
S ELECT
D ISTINCT SALARY
F ROM
EMPLOYEE
Falls es mehrere Angestellte mit dem selben Lohn gibt, so liefert das erste
Statement den Lohn mehrfach zurück, während das zweite Statement nur die
einzelnen Lohnstufen jeweils einmal auflistet.
Prof. Dr. Dietmar Seipel
134
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 4
Erstelle eine Liste aller Projekte, an denen ein Arbeiter mit dem Nachnamen
“Smith” arbeitet, oder die einer Abteilung zugeordnet sind, deren
Abteilungsleiter den Nachnamen “Smith” trägt.
( S ELECT
PNO
F ROM
WORKS_ON, EMPLOYEE
W HERE
ESSN=SSN A ND LNAME=’Smith’ )
U NION
( S ELECT
Prof. Dr. Dietmar Seipel
PNUMBER
F ROM
PROJECT, DEPARTMENT, EMPLOYEE
W HERE
DNUM=DNUMBER
A ND
MGRSSN=SSN A ND LNAME=’Smith’ )
135
Vorlesung Datenbanken
Wintersemester 2013/14
Geschachtelte Anfragen und Mengenvergleiche
Finde die ESSN aller Angestellten, die an einem Projekt so viele Stunden
arbeiten wie der Angestellte mit der ESSN=’444444444’:
S ELECT
D ISTINCT ESSN
F ROM
WORKS_ON
W HERE
(PNO, HOURS) I N (
S ELECT
PNO, HOURS
F ROM
WORKS_ON
W HERE
ESSN=’444444444’ );
Dabei bezieht sich die innere/äußere Variable ESSN auf das innere/äußere
WORKS_ON.
Prof. Dr. Dietmar Seipel
136
Vorlesung Datenbanken
Wintersemester 2013/14
Finde alle Angestellten, die mehr verdienen als alle Angestellten aus
Abteilung 5:
S ELECT
LNAME, FNAME
F ROM
EMPLOYEE
W HERE
SALARY > A LL I N (
S ELECT
SALARY
F ROM
EMPLOYEE
W HERE
DNO = 5 );
Auch möglich mit <, =, <=, >=, <>, SOME, ANY
Prof. Dr. Dietmar Seipel
137
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 4A
S ELECT
D ISTINCT PNUMBER
F ROM
PROJECT
W HERE
PNUMBER I N (
OR
S ELECT
PNUMBER
F ROM
PROJECT, DEPARTMENT, EMPLOYEE
W HERE
DNUM = DNUMBER
A ND
MGRSSN = SSN A ND LNAME=’Smith’ )
PNUMBER I N (
S ELECT
PNO
F ROM
WORKS_ON, EMPLOYEE
W HERE
ESSN = SSN A ND LNAME = ’Smith’ )
Anders als bei Anfrage 4 werden hier keine Duplikate geliefert.
Prof. Dr. Dietmar Seipel
138
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 12
Suche die Namen aller Angestellten, die einen Angehörigen mit gleichem
Vornamen und Geschlecht haben.
S ELECT
E.FNAME, E.LNAME
F ROM
EMPLOYEE E
W HERE
SSN I N (
S ELECT
ESSN
F ROM
DEPENDENT
W HERE
SSN = ESSN
A ND
FNAME = DEPENDENT_NAME
A ND
E.SEX = SEX )
Das Attribut SEX ohne Präfix E im inneren S ELECT–Statement bezieht sich
auf DEPENDENT.
Prof. Dr. Dietmar Seipel
139
Vorlesung Datenbanken
Wintersemester 2013/14
Eine alternative Formulierung mit einem flachen S ELECT–Statement wäre:
S ELECT
E.FNAME, E.LNAME
F ROM
EMPLOYEE E, DEPENDENT D
W HERE
E.SSN = D.ESSN
A ND
E.FNAME = D.DEPENDENT_NAME
A ND
E.SEX = D.SEX
Hier sind zwei Aliase, E und D, erforderlich, um das Attribut SEX korrekt
den Relationen EMPLOYEE bzw. DEPENDENT zuzuordnen.
Prof. Dr. Dietmar Seipel
140
Vorlesung Datenbanken
Wintersemester 2013/14
Explizite Mengen
Anfrage 13
Finde die Sozialversicherungsnummern aller Angestellten heraus,
die an einem der Projekte 1, 2 oder 3 arbeiten.
S ELECT
D ISTINCT ESSN
F ROM
WORKS_ON
W HERE
PNO I N (1, 2, 3)
Alternativ – aber weniger elegant – könnte man die folgende, etwas längere
W HERE–Bedingung schreiben:
Prof. Dr. Dietmar Seipel
W HERE
PNO = 1
OR
PNO = 2
OR
PNO = 3
141
Vorlesung Datenbanken
Wintersemester 2013/14
NULL–Werte
Anfrage 14
Finde die Namen aller Angestellten heraus, die keinen Vorgesetzten haben.
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
SUPERSSN I S N ULL
Eigentlich würde bereits “SUPERSSN = N ULL” eindeutig anzeigen, daß auf
den Nullwert getestet wird, da ja keine Hochkommata verwendet werden.
Zur zusätzlichen Unterscheidung muß man aber sogar “I S” anstelle von “=”
schreiben.
Prof. Dr. Dietmar Seipel
142
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 3
Finde die Namen aller Angestellten heraus, die an allen Projekten
mitarbeiten, welche von Abteilung Nummer 5 kontrolliert werden.
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
( ( S ELECT
PNO
F ROM
WORKS_ON
W HERE
SSN = ESSN )
C ONTAINS
( S ELECT
PNUMBER
F ROM
PROJECT
W HERE
DNUM = 5 ) )
Diese Anfrage mit C ONTAINS kann in der Relationenalgebra mittels einer
Division ausgewertet werden.
Prof. Dr. Dietmar Seipel
143
Vorlesung Datenbanken
Wintersemester 2013/14
E XISTS
Anfrage 12B
Suche die Namen aller Angestellten, die einen Angehörigen mit gleichem
Vornamen und Geschlecht haben.
Prof. Dr. Dietmar Seipel
S ELECT
E.FNAME, E.LNAME
F ROM
EMPLOYEE E
W HERE
E XISTS (
S ELECT
*
F ROM
DEPENDENT
W HERE
E.SSN = ESSN
A ND
E.SEX = SEX
A ND
E.FNAME = DEPENDENT_NAME )
144
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 6
Suche die Namen aller Angestellten, die keine Angehörigen haben.
Prof. Dr. Dietmar Seipel
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
N OT E XISTS (
S ELECT
*
F ROM
DEPENDENT
W HERE
SSN = ESSN )
145
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 7
Führe die Namen aller Abteilungsleiter auf, die mindestens einen
Angehörigen haben.
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
E XISTS (
A ND
Prof. Dr. Dietmar Seipel
S ELECT
*
F ROM
DEPENDENT
W HERE
SSN = ESSN )
E XISTS (
S ELECT
*
F ROM
DEPARTMENT
W HERE
SSN = MGRSSN )
146
Vorlesung Datenbanken
Wintersemester 2013/14
Eine alternative Formulierung mit einem flachen S ELECT–Statement, die
zusätzlich noch die Vornamen der jeweiligen Angehörigen ausgibt, wäre:
S ELECT
FNAME, LNAME, DEPENDENT_NAME
F ROM
EMPLOYEE, DEPENDENT, DEPARTMENT
W HERE
SSN = ESSN
A ND
SSN = MGRSSN
Ergebnis:
FNAME
LNAME
DEPENDENT_NAME
Franklin
Wong
Alice
Franklin
Wong
Joy
Franklin
Wong
Theodore
Jennifer
Wallace
Abner
Falls man DEPENDENT_NAME nicht mit ausgibt, so kann man mittels
DISTINCT verhindern, daß derselbe Angestellte mehrmals aufgelistet wird.
Prof. Dr. Dietmar Seipel
147
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 3A
Finde die Namen aller Angestellten heraus, die an allen Projekten
mitarbeiten, welche von Abteilung Nummer 5 kontrolliert werden.
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
N OT E XISTS
(S ELECT
*
F ROM
WORKS_ON B
W HERE
B.PNO I N
A ND
Prof. Dr. Dietmar Seipel
N OT E XISTS
(S ELECT
PNUMBER
F ROM
PROJECT
W HERE
DNUM=5)
(S ELECT
*
F ROM
WORKS_ON C
W HERE
C.ESSN=SSN
A ND
C.PNO=B.PNO))
148
Vorlesung Datenbanken
Wintersemester 2013/14
Umbenennung von Attributen und explizite Joins
Anfrage 8A
S ELECT
E.LNAME A S EMPLOYEE,
S.LNAME A S SUPERVISOR
F ROM
EMPLOYEE A S E, EMPLOYEE A S S
W HERE
E.SUPERSSN=S.SSN
Ergebnis:
Prof. Dr. Dietmar Seipel
EMPLOYEE
SUPERVISOR
Smith
Wong
Wong
Borg
Zelaya
Wallace
...
...
149
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 1A
S ELECT
FNAME, LNAME, ADDRESS
F ROM
(EMPLOYEE J OIN DEPARTMENT O N DNO=DNUMBER)
W HERE
DNAME=’Research’
Die Join–Bedingung kann in den F ROM–Teil gezogen werden.
Anfrage 1B
S ELECT
FNAME, LNAME, ADDRESS
F ROM
(EMPLOYEE NATURAL JOIN
(DEPARTMENT A S D(DNAME, DNO, X, Y)))
W HERE
DNAME=’Research’
Die Join–Bedingung DNO=DNUMBER wird beim NATURAL JOIN in 1B
dadurch realisiert, daß DNUMBER von DEPARTMENT mittels A S an DNO
von EMPLOYEE angeglichen wird (Renaming).
Prof. Dr. Dietmar Seipel
150
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 8B
Die folgende Anfrage bestimmt wieder die Vorgesetzten:
S ELECT
E.LNAME A S EMPLOYEE,
S.LNAME A S SUPERVISOR
F ROM
(EMPLOYEE E LEFT OUTER JOIN EMPLOYEE S
ON E.SUPERSSN=S.SSN)
Der LEFT OUTER JOIN enthält immer Tupel zu den Tupeln der linken
Tabelle, selbst wenn es keine passenden Tupel in der rechten Tabelle gibt.
Das bedeutet hier, daß alle Angestellten E aufgelistet werden, selbst wenn
sie – wie der Firmenchef James Borg – keinen Vorgesetzten S haben.
Prof. Dr. Dietmar Seipel
151
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 2A
S ELECT
PNUMBER, DNUM, LNAME, ADDRESS, BDATE
F ROM
( ( PROJECT
JOIN DEPARTMENT ON DNUM=DNUMBER )
JOIN EMPLOYEE ON MGRSSN=SSN )
W HERE
PLOCATION=’Stafford’
Man kann Join–Spezifikationen auch schachteln; d.h., eine der Tabellen eines
Joins kann selbst wieder das Resultat eines Joins sein.
In unserem Falle werden zuerst PROJECT und DEPARTMENT verbunden;
das Resultat wird dann mit EMPLOYEE verbunden.
Prof. Dr. Dietmar Seipel
152
Vorlesung Datenbanken
Wintersemester 2013/14
Aggregatsfunktionen und Gruppierung
Anfrage 15
Berechne die Summe, das Maximum, das Minimum und den Durchschnitt
der Gehälter aller Angestellten.
S ELECT
F ROM
S UM (SALARY), M AX (SALARY),
M IN (SALARY), AVG (SALARY)
EMPLOYEE
Nullwerte (bei SALARY) werden bei der Aggregation nicht berücksichtigt.
Anfrage 18
Bestimme die Anzahl der Mitarbeiter in der Abteilung ’Research’.
S ELECT
F ROM
W HERE
Prof. Dr. Dietmar Seipel
C OUNT (*)
EMPLOYEE, DEPARTMENT
DNO=DNUMBER A ND DNAME=’Research’
153
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 19
Bestimme die Anzahl verschiedener Löhne in der Datenbank.
S ELECT
C OUNT (D ISTINCT SALARY)
F ROM
EMPLOYEE
Anfrage 20
Bestimme für alle Abteilungen die Abteilungsnummer, die Anzahl der
Angestellten und das Durchschnittsgehalt.
Prof. Dr. Dietmar Seipel
S ELECT
DNO, C OUNT (*) A S EMPS, AVG (SALARY)
F ROM
EMPLOYEE
G ROUP B Y
DNO
154
Vorlesung Datenbanken
Wintersemester 2013/14
Ergebnis von Anfrage 20:
Zunächst werden die Angestellten nach Abteilungszugehörigkeit gruppiert.
FNAME
MINIT
LNAME
SSN
...
SALARY
SUPERSSN
DNO
John
B
Smith
444444444
...
30000
222222222
5
Franklin
T
Wong
222222222
...
40000
111111111
5
Ramesh
K
Narayan
555555555
...
38000
222222222
5
Joyce
A
English
666666666
...
25000
222222222
5
Alicia
J
Zelaya
777777777
...
25000
333333333
4
Jennifer
S
Wallace
333333333
...
43000
111111111
4
Ahmad
V
Jabbar
888888888
...
25000
333333333
4
James
E
Borg
111111111
...
55000
NULL
1






















o
Danach werden die Gruppen zusammengefaßt.
Prof. Dr. Dietmar Seipel
DNO
EMPS
AVG(SALARY)
5
4
33250
4
3
31000
1
1
55000
155
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 5
Bestimme die Namen und Vornamen aller Angestellten, die mindestens zwei
Angehörige haben.
Prof. Dr. Dietmar Seipel
S ELECT
LNAME, FNAME
F ROM
EMPLOYEE
W HERE
( S ELECT
C OUNT (*)
F ROM
DEPENDENT
W HERE
SSN = ESSN ) >= 2
156
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 21
Zu jedem Projekt sollen Projektnummer und Projektname sowie die Anzahl
der an diesem Projekt arbeitenden Angestellten zurückgegeben werden.
Prof. Dr. Dietmar Seipel
S ELECT
PNUMBER, PNAME, C OUNT (*) A S EMPS
F ROM
PROJECT, WORKS_ON
W HERE
PNUMBER = PNO
G ROUP B Y
PNUMBER, PNAME
157
Vorlesung Datenbanken
Wintersemester 2013/14
Gruppierung mit Having
Anfrage 22
Zu jedem Projekt mit mehr als zwei Angestellten, die daran arbeiten, sollen
Projektnummer und Projektname sowie die Anzahl der an diesem Projekt
arbeitenden Angestellten zurückgegeben werden.
Prof. Dr. Dietmar Seipel
S ELECT
PNUMBER, PNAME, C OUNT (*) A S EMPS
F ROM
PROJECT, WORKS_ON
W HERE
PNUMBER=PNO
G ROUP B Y
PNUMBER, PNAME
H AVING
C OUNT (*) > 2
158
Vorlesung Datenbanken
Wintersemester 2013/14
Ergebnis von Anfrage 22:
Zunächst wird der Join der Relationen PROJECT und WORKS_ON gebildet,
wobei die Bedingung PNO=PNUMBR berücksichtigt wird.
Prof. Dr. Dietmar Seipel
PNAME
PNUMBER
...
ESSN
PNO
HOURS
ProductX
1
...
444444444
1
32.5
ProductX
1
...
666666666
1
20.0
ProduktY
2
...
444444444
2
7.5
ProduktY
2
...
666666666
2
20.0
ProduktY
2
...
222222222
2
10.0
ProductZ
3
...
555555555
3
40.0
ProductZ
3
...
222222222
3
10.0
Computerization
10
...
222222222
10
10.0
Computerization
10
...
777777777
10
10.0
Computerization
10
...
888888888
10
35.0
Reorganization
20
...
222222222
20
10.0
Reorganization
20
...
333333333
20
15.0
Reorganization
20
...
111111111
20
NULL
Newbenefits
30
...
888888888
30
5.0
Newbenefits
30
...
333333333
30
20.0
Newbenefits
30
...
777777777
30
30.0










































159
Vorlesung Datenbanken
Wintersemester 2013/14
Dann wird die Bedingung “H AVING C OUNT (*) > 2” ausgewertet.
PNAME
...
ESSN
PNO
HOURS
ProduktY
2
...
444444444
2
7.5
ProduktY
2
...
666666666
2
20.0
ProduktY
2
...
222222222
2
10.0
Computerization
10
...
222222222
10
10.0
Computerization
10
...
777777777
10
10.0
Computerization
10
...
888888888
10
35.0
Reorganization
20
...
222222222
20
10.0
Reorganization
20
...
333333333
20
15.0
Reorganization
20
...
111111111
20
NULL
Newbenefits
30
...
888888888
30
5.0
Newbenefits
30
...
333333333
30
20.0
Newbenefits
Prof. Dr. Dietmar Seipel
PNUMBER
30
...
777777777
30
30.0




























160
Vorlesung Datenbanken
Wintersemester 2013/14
Und schließlich werden noch die Gruppen zusammengefaßt.
Prof. Dr. Dietmar Seipel
PNUMBER
PNAME
EMPS
2
ProductY
3
10
Computerization
3
20
Reorganization
3
30
Newbenefits
3
161
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 23
Gib zu jedem Projekt den Projektnamen, die Projektnummer und die Anzahl
der Angestellten, die daran arbeiten und Abteilung 5 angehören, an.
Prof. Dr. Dietmar Seipel
S ELECT
PNUMBER, PNAME, C OUNT (*) A S EMPS
F ROM
PROJECT, WORKS_ON, EMPLOYEE
W HERE
PNUMBER=PNO A ND SSN=ESSN A ND DNO=5
G ROUP B Y
PNUMBER, PNAME
162
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 24
Es sind alle Abteilungen gesucht, die mehr als 5 Angestellte haben; zu diesen
Abteilungen ist auch die Anzahl der Angestellten mit mehr als 40.000 $
Verdienst gesucht.
S ELECT
DNAME, C OUNT (*) A S EMPS
F ROM
DEPARTMENT, EMPLOYEE
W HERE
DNUMBER=DNO A ND SALARY > 40000
A ND
DNO I N (
G ROUP B Y
Prof. Dr. Dietmar Seipel
S ELECT
DNO
F ROM
EMPLOYEE
G ROUP B Y
DNO
H AVING
C OUNT (*) > 5 )
DNAME
163
Vorlesung Datenbanken
Wintersemester 2013/14
Anfrage 24A
Es sind alle Abteilungen gesucht, die mehr als 5 Angestellte mit mehr als
40.000 $ Verdienst haben; zu diesen Abteilungen ist auch die Anzahl dieser
Angestellten gesucht.
Prof. Dr. Dietmar Seipel
S ELECT
DNAME, C OUNT (*) A S EMPS
F ROM
DEPARTMENT, EMPLOYEE
W HERE
DNUMBER=DNO A ND SALARY > 40000
G ROUP B Y
DNAME
H AVING
C OUNT (*) > 5
164
Vorlesung Datenbanken
Wintersemester 2013/14
Regeln für S ELECT–Statements mit G ROUP B Y und H AVING
1. Zuerst wird der Join der Relationen aus der F ROM–Klausel basierend
auf den Selektions– und Joinbedingungen der W HERE–Klausel
berechnet.
2. Dann wird mittels G ROUP B Y die Gruppierung vorgenommen, und die
Aggregationsfunktionen werden auf die entstandenen Gruppen
angewendet; Nullwerte werden bei der Aggregation ignoriert.
3. Die H AVING–Klausel selektiert dann einzelne Gruppen.
4. In der S ELECT–Klausel sollten nur Attributwerte, nach denen gruppiert
wurde, ohne Aggregatsfunktion vorkommen – zusammen mit den
berechneten Aggregatswerten AGG (A).
5. Alle nicht aggregierten Attribute aus der S ELECT–Klausel sollten
normalerweise im G ROUP B Y vorkommen.
Prof. Dr. Dietmar Seipel
165
Vorlesung Datenbanken
Wintersemester 2013/14
Vergleich von S ELECT– und G ROUP B Y–Klausel
In der folgenden Anfrage treten genau die Attributwerte, nach denen
gruppiert wird, in der S ELECT–Klausel ohne Aggregatsfunktion auf.
S ELECT
DNO, C OUNT (*)
F ROM
EMPLOYEE
G ROUP B Y
DNO
Das Ergebnis zeigt wie sich die 8 Mitarbeiter auf die 3 Abteilungen
(DNO=1,4,5) verteilen.
Prof. Dr. Dietmar Seipel
DNO
C OUNT (*)
1
1
4
3
5
4
166
Vorlesung Datenbanken
Wintersemester 2013/14
Man kann auch nach DNO gruppieren, ohne daß DNO in der
S ELECT–Klausel auftritt.
S ELECT
C OUNT (*)
F ROM
EMPLOYEE
G ROUP B Y
DNO
Dann wird für jede Abteilung die Anzahl der Mitarbeiter berechnet,
allerdings ohne Zuordnung zur Abteilungsnummer.
C OUNT (*)
1
3
4
Prof. Dr. Dietmar Seipel
167
Vorlesung Datenbanken
Wintersemester 2013/14
In MyS QL können in der S ELECT–Klausel sogar Attributwerte, nach denen
nicht gruppiert wurde, ohne Aggregatsfunktion auftreten.
Dann ist allerdings das Ergebnis meist unsinnig.
S ELECT
DNO, C OUNT (*)
F ROM
EMPLOYEE
Diese Anfrage berechnet die Anzahl 8 aller Mitarbeiter der gesamten Firma
und gibt sie zusammen mit der Abteilungsnummer 1 aus.
Prof. Dr. Dietmar Seipel
DNO
C OUNT (*)
1
8
168
Vorlesung Datenbanken
Wintersemester 2013/14
Teilstring–Vergleiche
Anfrage 25
Gesucht sind alle Angestellten, die in Houston, Texas, leben.
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
ADDRESS L IKE ’%Houston, TX%’
Anfrage 26
Finde alle Angestellten heraus, die in den 50er Jahren geboren wurden.
S ELECT
FNAME, LNAME
F ROM
EMPLOYEE
W HERE
BDATE L IKE ’_ _ 5 _–_ _–_ _’
Alternative: BDATE L IKE ’_ _ 5%’
Prof. Dr. Dietmar Seipel
169
Vorlesung Datenbanken
Wintersemester 2013/14
Arithmetische Operationen, Ordnung und Formatierung der Ausgabe
Anfrage 27
Zeige die Gehälter, die die Angestellten, die am ProductX arbeiten, im Falle
einer 10%–igen Lohnerhöhung bekommen würden.
S ELECT
FNAME, LNAME, 1.1*SALARY
F ROM
EMPLOYEE, WORKS_ON, PROJECT
W HERE
SSN = ESSN
A ND
PNO = PNUMBER A ND PNAME = ’ProductX’
O RDER B Y
FNAME, LNAME
Um FNAME absteigend zu sortieren, würde man die O RDER B Y–Klausel
ersetzen durch: O RDER B Y FNAME D ESC, LNAME A SC.
Mittels S ELECT FNAME, ’:’, LNAME, ’:’, 1.1*SALARY könnte man
eine ’:’–separierte Ausgabe zum Import in MS Excel erzeugen.
Prof. Dr. Dietmar Seipel
170
Vorlesung Datenbanken
Wintersemester 2013/14
Falls nichts angegeben wurde, sie ist die Standard–Ordnung aufsteigend.
Anfrage 28
S ELECT
DNAME, FNAME, LNAME, PNAME
F ROM
DEPARTMENT, EMPLOYEE,
WORKS_ON, PROJECT
Prof. Dr. Dietmar Seipel
W HERE
DNUMBER = DNO
A ND
SSN = ESSN
A ND
PNO = PNUMBER
O RDER B Y
DNAME D ESC, LNAME, FNAME
171
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.3 Update–Statements in SQL
Zum Einfügen eines einzelnen Tupels in eine Relation (I NSERT) gibt es zwei
Möglichkeiten:
1. Man listet die Werte des neuen Tupels in derselben Reihenfolge auf, in
der die zugehörigen Attribute beim C REATE TABLE angegeben wurden.
2. Man gibt die Attribute zu den Werten des neuen Tupels in
entsprechender Reihenfolge explizit an.
In MySQL können auch mehrere Tupel durch Kommata getrennt in einem
einzigen I NSERT–Statement angegeben werden.
Prof. Dr. Dietmar Seipel
172
Vorlesung Datenbanken
Wintersemester 2013/14
Beispiel
I NSERT I NTO
EMPLOYEE
VALUES
(’Richard’, ’K’, ’Marini’, ’111222333’, ’1952-12-30’,
’98 Oak Forest, Katy, TX’, ’M’, 37000 , ’333333333’, 4 )
I NSERT I NTO
EMPLOYEE(FNAME, LNAME, SSN, DNO)
VALUES
(’Richard’, ’Marini’, ’111222333’, 4),
(’Robert’, ’Hatcher’, ’555666777’, 2)
Die Überprüfung der Integritätsbedingungen ist systemabhängig. Die
folgende Operation wird abgelehnt, da kein Wert für “SSN” angegeben ist:
Prof. Dr. Dietmar Seipel
I NSERT I NTO
EMPLOYEE(FNAME, LNAME, DNO)
VALUES
(’Robert’, ’Hatcher’, 2)
173
Vorlesung Datenbanken
Wintersemester 2013/14
Es kann auch eine ganze mittels eines S ELECT–Statements erzeugte Relation
eingefügt werden:
CREATE TABLE
DNAME
EMPS
TOTAL_SAL
INSERT INTO
SELECT
FROM
WHERE
GROUP BY
Prof. Dr. Dietmar Seipel
DEPT (
VARCHAR(15),
INTEGER,
INTEGER );
DEPT
DNAME
EMPS
TOTAL_SAL
Administration
3
93000
Headquarters
1
55000
Research
4
133000
DEPT
DNAME, COUNT(*), SUM(SALARY)
DEPARTMENT, EMPLOYEE
DNUMBER = DNO
DNAME
174
Vorlesung Datenbanken
Wintersemester 2013/14
Das Löschen von Tupeln erfolgt mit D ELETE; auch hier ist die Überprüfung
von referentiellen Integritätsbedingungen systemabhängig.
D ELETE F ROM
EMPLOYEE
W HERE
LNAME=’Brown’
D ELETE F ROM
EMPLOYEE
W HERE
DNO I N (
D ELETE F ROM
S ELECT
DNUMBER
F ROM
DEPARTMENT
W HERE
DNAME = ’Research’ )
EMPLOYEE
Das letzte Statement – ohne W HERE–Teil – löscht dabei alle Tupel.
Prof. Dr. Dietmar Seipel
175
Vorlesung Datenbanken
Wintersemester 2013/14
Das U PDATE–Kommando
• In einer W HERE–Klausel werden die zu modifizierenden Tupel
spezifiziert.
• Eine S ET–Klausel spezifiziert die zu modifizierenden Attribute sowie
die zugehörigen Attributwerte.
U PDATE
PROJECT
S ET
PLOCATION=’Bellaire’, DNUM=5
W HERE
PNUMBER=10
Prof. Dr. Dietmar Seipel
176
Vorlesung Datenbanken
Wintersemester 2013/14
Es können mehrere Tupel gleichzeitig mittels einer Berechnungsregel
modifiziert werden:
U PDATE
EMPLOYEE
S ET
SALARY = SALARY * 1.1
W HERE
DNO I N (
S ELECT
DNUMBER
F ROM
DEPARTMENT
W HERE
DNAME = ’Research’ )
Wie bei Programmiersprachen bezeichnet ein Attributname auf der rechten
Seite einer Zuweisung den alten Wert, während er auf der linken Seite den
neuen Wert bezeichnet.
Prof. Dr. Dietmar Seipel
177
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.4 Views (Sichten) in SQL
Ein View ist eine virtuelle Tabelle, welche von anderen Tabellen
(gespeicherten Basistabellen oder auch anderen Views) abgeleitet ist.
Diese anderen Tabellen werden definierende Tabellen genannt.
Ein View hat einen (virtuellen) Tabellennamen, eine Liste von Attributen,
und sein Inhalt wird durch ein S ELECT–Statement spezifiziert:
C REATE V IEW
E_WORKS_ON_P
AS
S ELECT
FNAME, LNAME, PNAME, HOURS
F ROM
EMPLOYEE, WORKS_ON, PROJECT
W HERE
SSN=ESSN A ND PNO=PNUMBER
Prof. Dr. Dietmar Seipel
178
Vorlesung Datenbanken
Wintersemester 2013/14
E_W ORKS _O N _P
Prof. Dr. Dietmar Seipel
FNAME
LNAME
PNAME
HOURS
John
Smith
ProductX
32.5
John
Smith
ProductY
7.5
Ramesh
Narayan
ProductZ
40.0
Joyce
English
ProductX
20.0
Joyce
English
ProductY
20.0
Franklin
Wong
ProductY
10.0
Franklin
Wong
ProductZ
10.0
Alicia
Zelaya
Newbenefits
30.0
Alicia
Zelaya
Computerization
10.0
Ahmad
Jabbar
Computerization
35.5
Ahmad
Jabbar
Newbenefits
5.0
Jennifer
Wallace
Newbenefits
20.0
Jennifer
Wallace
Reorganization
15.0
James
Borg
Reorganization
NULL
179
Vorlesung Datenbanken
Wintersemester 2013/14
C REATE V IEW
DEPT (DNAME, EMPS, TOTAL_SAL)
AS
S ELECT
DNAME, C OUNT (*), S UM (SALARY)
F ROM
DEPARTMENT,
EMPLOYEE
W HERE
DNUMBER=DNO
G ROUP B Y
DNAME
DEPT
DNAME
EMPS
TOTAL_SAL
Administration
3
93000
Headquarters
1
55000
Research
4
133000
Sobald man in einer Sitzung einen View nicht mehr benötigt, sollte man ihn
aus Effizienzgründen löschen:
D ROP V IEW
E_WORKS_ON_P
D ROP V IEW
DEPT
Prof. Dr. Dietmar Seipel
180
Vorlesung Datenbanken
Wintersemester 2013/14
An Views können in üblicher Weise Anfragen gestellt werden. Dies
erleichtert das wiederholte Stellen ähnlicher Anfragen.
S ELECT
FNAME, LNAME
F ROM
E_WORKS_ON_P
W HERE
PNAME = ’ProjectX’
Ein View ist immer aktuell, er reflektiert automatisch Änderungen, welche
sich aus Modifikationen der definierenden Tabellen ergeben.
Zur effizienten Implementierung gibt es zwei wichtige Strategien:
1. Anfrage–Modifizierung: bildet eine Anfrage an einen View auf
Anfragen an die definierenden Relationen ab.
2. View–Materialisierung: bei der ersten Anfrage an einen View wird
dieser berechnet; mittels einer inkrementellen Methode werden
Änderungen der definierenden Relationen auf den View propagiert.
Prof. Dr. Dietmar Seipel
181
Vorlesung Datenbanken
Wintersemester 2013/14
View–Updates
U PDATE–Operationen auf einem View können (manchmal) auf die
definierenden Tabellen propagiert werden.
U PDATE
E_WORKS_ON_P
S ET
PNAME = ’ProductY’
W HERE
LNAME = ’Smith’
A ND
FNAME = ’John’
A ND
PNAME = ’ProductX’
Leider sind bei Views (wie dem obigen), welche durch Joins aus mehreren
definierenden Tabellen abgeleitet sind, die erforderlichen Operationen auf
den definierenden Tabellen meist nicht eindeutig.
Prof. Dr. Dietmar Seipel
182
Vorlesung Datenbanken
Wintersemester 2013/14
Die erste der folgenden drei Alternativen wäre wohl am sinnvollsten:
1. Alternative: Änderung in WORKS_ON
U PDATE
WORKS_ON
S ET
PNO = ( S ELECT PNUMBER F ROM PROJECT
W HERE PNAME=’ProductY’ )
W HERE
ESSN = ( S ELECT SSN F ROM EMPLOYEE
W HERE LNAME=’Smith’ A ND FNAME=’John’ )
A ND
PNO = ( S ELECT PNUMBER F ROM PROJECT
W HERE PNAME=’ProductX’ )
Anstelle von ProductX arbeitet John Smith zukünftig dieselbe
Stundenanzahl an ProductY.
Prof. Dr. Dietmar Seipel
183
Vorlesung Datenbanken
Wintersemester 2013/14
2. Alternative: Änderung in PROJECT
D ELETE
F ROM PROJECT
W HERE
PNAME=’ProductY’
U PDATE
PROJECT
S ET
PNAME=’ProductY’
W HERE
PNAME=’ProductX’
ProductY wird gelöscht, und ProductX wird in ProductY umbenannt.
3. Alternative: Änderung in EMPLOYEE wäre relativ sinnlos.
Prof. Dr. Dietmar Seipel
184
Vorlesung Datenbanken
Wintersemester 2013/14
In den meisten Datenbanksystemen gelten folgende Regeln:
• Ein View über einer einzigen definierenden Tabelle ist modifizierbar,
falls die View–Attribute den Primärschlüssel oder einen anderen
Schlüsselkandidaten der Basisrelation enthalten, denn dann entspricht
jedes virtuelle Tupel des Views genau einem Basis–Tupel.
C REATE V IEW
EMP ( LNAME, SSN )
AS
S ELECT
LNAME, SSN
F ROM
EMPLOYEE
U PDATE
Prof. Dr. Dietmar Seipel
EMP
EMP
LNAME
SSN
Smith
444444444
Wong
222222222
Zelaya
777777777
Wallace
333333333
S ET
LNAME = ’Armstrong’
Narayan
555555555
W HERE
SSN = ’444444444’
English
666666666
Jabbar
888888888
Borg
111111111
185
Vorlesung Datenbanken
Wintersemester 2013/14
• Views, welche mittels Gruppierung und Aggregatsfunktionen definiert
sind, sind nicht modifizierbar.
U PDATE
DEPT
S ET
TOTAL_SAL = 100000
W HERE
DNAME=’Research’
DEPT
DNAME
EMPS
TOTAL_SAL
Administration
3
93000
Headquarters
1
55000
Research
4
133000
• Views über mehreren definierenden Tabellen (mit Joins) sind i.a. nicht
modifizierbar.
In SQL2 muß am Ende der View–Definition die Klausel
WITH CHECK OPTION
stehen, wenn der View modifizierbar sein soll.
Prof. Dr. Dietmar Seipel
186
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.5 Zusätzliche Bedingungen
Integritätsbedingungen
CREATE ASSERTION SALARY_CONSTRAINT
CHECK NOT EXISTS ( SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D
WHERE E.SALARY > M.SALARY
AND E.DNO = D.DNUMBER AND D.MGRSSN = M.SSN ) );
Trigger geben eine Bedingung an und eine Aktion,
welche ausgeführt werden soll, sobald die Bedingung verletzt wird.
DEFINE TRIGGER SALARY_TRIGGER
ON EMPLOYEE E, EMPLOYEE M, DEPARTMENT D:
E.SALARY > M.SALARY AND
E.DNO = D.DNUMBER AND D.MGRSSN = M.SSN
ACTION_PROCEDURE INFORM_MANAGER (D.MGRSSN);
Prof. Dr. Dietmar Seipel
187
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.6 Indexe in SQL
Ein Index ist eine physikalische Zugriffsstruktur. Er erleichtert den Zugriff
auf Tupel über Bedingungen, welche die indexierten Attribute betreffen.
CREATE INDEX LNAME_INDEX
ON EMPLOYEE (LNAME);
CREATE INDEX NAMES_INDEX
ON EMPLOYEE (LNAME ASC, FNAME DESC, MINIT);
Zum Primärschlüssel und zu den Sekundärschlüsseln wird automatisch –
beim C REATE TABLE – ein Index angelegt.
Prof. Dr. Dietmar Seipel
188
Vorlesung Datenbanken
Wintersemester 2013/14
Das Schlüsselwort U NIQUE hinter dem C REATE erklärt die Indexattribute
zum Schlüssel.
CREATE UNIQUE INDEX NAMES_INDEX
ON EMPLOYEE (LNAME, FNAME, MINIT);
Eine Basisrelation kann höchstens einen “Clusterindex” – nach diesem sind
die Datensätze physisch auf der Festplatte organisiert – haben, aber beliebig
viele andere Indexe.
CREATE INDEX DNO_INDEX
ON EMPLOYEE (DNO)
CLUSTER;
Da die Pflege eines Index bei Updates der Basisrelation aufwendig ist, sollte
man den Index löschen, sobald er nicht mehr benötigt wird.
DROP INDEX DNO_INDEX;
Prof. Dr. Dietmar Seipel
189
Vorlesung Datenbanken
Wintersemester 2013/14
2.3.7 Java Database Connectivity (JDBC)
S QL kann im Zusammenhang mit herkömmlichen, prozeduralen
Programmiersprachen wie JAVA, C++ oder C benutzt werden.
Diese Sprachen nennt man dann Hostsprachen.
import
import
import
import
import
java.io.*;
java.sql.*;
java.util.*;
java.text.*;
java.lang.reflect.*;
class MySQL_Query {
protected
protected
protected
protected
Prof. Dr. Dietmar Seipel
final
final
String
String
String dbUser = "seipel";
String dbPassword = "...";
jdbcURL = "jdbc:mysql://localhost/company";
jdbcDriver = "org.gjt.mm.mysql.Driver";
190
Vorlesung Datenbanken
Wintersemester 2013/14
/* connection to the database */
protected Connection con = null;
public static void main(String args[])
{
MySQL_Query query = new MySQL_Query();
query.initConnection();
query.salary_query();
}
protected void initConnection()
...
private void salary_query()
...
}
Prof. Dr. Dietmar Seipel
191
Vorlesung Datenbanken
Wintersemester 2013/14
protected void initConnection()
{
try {
Class.forName(jdbcDriver).newInstance();
con = DriverManager.getConnection(
jdbcURL, dbUser, dbPassword );
}
catch (InstantiationException ie) {
System.out.println(ie + ie.getMessage());
}
catch (IllegalAccessException iae) {
System.out.println(iae + iae.getMessage());
}
catch (SQLException sqle) {
System.out.println(sqle + sqle.getMessage());
}
catch (ClassNotFoundException cnfe) {
System.out.println(cnfe + cnfe.getMessage());
}
}
Prof. Dr. Dietmar Seipel
192
Vorlesung Datenbanken
Wintersemester 2013/14
private void salary_query()
{
try {
String query =
"SELECT * FROM employee WHERE salary >= 30000";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String fname = rs.getString(1);
String minit = rs.getString(2);
String lname = rs.getString(3);
String salary = rs.getString(8);
System.out.println( "( " + fname + ", " + minit +
", " + lname + ", " + salary + " )" );
}
}
catch (Exception mye){
System.out.println(mye.toString());
}
}
Prof. Dr. Dietmar Seipel
193