Post on 19-Dec-2016
transcript
Vorlesung Datenbanken Wintersemester 2013/14
2 Das Relationenmodell: Sprachen und Systeme
E.F. Codd, 1970
2.1 Grundlegende Definitionen
SeiU eine endliche,universelle Attributmenge. SeiX eine abzählbare,
universelle Wertemenge. Sei NULL ∈ X ein ausgezeichneterNullwert.
Seidom : U → 2X eine Funktion, welche jedem AttributA ∈ U einen
nichtleerenWertebereichdom(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. EinTupelt überV ⊆ U ist eine Abbildung, welche jedem Attribut
A ∈ V einen Wertt(A) ∈ dom(A) zuordnet.
def (t) := V heißtDefinitionsbereichvon t.
Die Projektiont[X ] von t aufX ⊆ V ist die Abbildung mit dem
eingeschränkten DefinitionsbereichX .
2. Eine Menger von Tupeln überV heißtRelationüberV .
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.
FNAME MINIT LNAME SSN . . . SUPERSSN DNO
John B Smith 444444444 . . . 222222222 5
Prof. Dr. Dietmar Seipel 66
Vorlesung Datenbanken Wintersemester 2013/14
Definition (Relationenschema)
1. R = (V,K,NOTNULL) heißtRelationenschemamit
K ⊆ NOTNULL ⊆ V ⊆ U
2. def (R) := V heißt auchDefinitionsbereichvonR.
3. K und NOTNULL spezifizierenintrarelationale Bedingungenfür R:
• K heißtPrimärschlüsselvonR,
• NOTNULL heißt die Menge der NOTNULL–Attribute vonR.
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)
SeiR = (V,K,NOTNULL) ein Relationenschema.
EineRelationr für R ist eine Menge von Tupeln überV ,
die zusätzlich die intrarelationalen Bedingungen vonR erfüllen:
1. ∀ t, t′ ∈ r : t[K] = t′[K] ⇒ t = t′,
2. ∀ t ∈ r ∀A ∈ NOTNULL : t(A) 6= NULL.
Beispiel (EMPLOYEE)
FNAME MINIT LNAME SSN . . . SUPERSSN DNO
John B Smith 444444444 . . . 222222222 5
. . . . . . . . . . . . . . . . . . . . .
James E Borg 111111111 . . . NULL 1
Prof. Dr. Dietmar Seipel 68
Vorlesung Datenbanken Wintersemester 2013/14
Definition (Fremdschlüsselbedingung)
SeiR = {R1, . . . , Rn } eine Menge von Relationenschemata
Ri = (Vi, Ki,NOTNULLi)
undR = (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 ∈
{ NO ACTION, SET NULL , SET DEFAULT, CASCADE }
R heißtreferenzierendesRelationenschema, dieRi heißenreferenzierte
Relationenschemata.
Prof. Dr. Dietmar Seipel 69
Vorlesung Datenbanken Wintersemester 2013/14
Eine TeilmengeKF ⊆ V der AttributmengeV vonR referenziert dieSchlüsselKi der RelationenschemataRi.
R = (V,K, . . . )
R1 = (V1,K1, . . . ) Rn = (Vn,Kn, . . . ). . .
� ^
KF
K1 Kn. . .
� ^
• Seir eine erlaubte Instanz vonR, und
• seiri eine erlaubte Instanz vonRi, für 1 ≤ i ≤ n.
Ein Tupelt ∈ r referenziertdie Relationri,falls es ein Tupelti ∈ ri gibt, mit t[KF ] = ti[Ki].
DaKi der Schlüssel vonRi ist, gibt es zu jedemt ∈ r maximal ein solchesreferenziertes Tupelti ∈ ri.
Prof. Dr. Dietmar Seipel 70
Vorlesung Datenbanken Wintersemester 2013/14
Ein Tupelt ∈ r referenziertri, falls es ein Tupelti ∈ ri gibt, mit
t[KF ] = ti[Ki].
. . . KF . . .
t[KF ]
. . . K1 . . .
t1[K1]
. . . Kn . . .
tn[Kn]
. . .
� U
Prof. Dr. Dietmar Seipel 71
Vorlesung Datenbanken Wintersemester 2013/14
Die FremdschlüsselbedingungFK wird von
• der erlaubten Instanzr vonR und
• den erlaubten Instanzenri vonRi, für 1 ≤ i ≤ n,
erfüllt, falls für alle Tupelt ∈ r gilt:
(∀A ∈ KF : t(A) = NULL) ∨
(QFIER = exactly one⇒ t referenziert genau einri) ∨
(QFIER = at least one⇒ t referenziert mindestens einri) ∨
(QFIER = all ⇒ t referenziert alleri).
Der Normalfall istn = 1.
Dann fallen “exactly one”, “at least one” und “all” zusammen.
In SQL können momentan nur Fremdschlüsselbedingungen mitn = 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),
wobeitNULL das Tupel überKF 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 EMPLOYEE gilt folgendes:
• Wenn ein Angestellter einen von NULL verschiedenen
SUPERSSN–Eintrag hat, dann muß es genau ein referenziertes Tupel in
EMPLOYEE geben mit diesem SSN–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 DNO muß jeder Angestellte
einer Abteilung zugeordnet sein. Die von NULL verschiedeneDNO
referenziert genau ein Tupel in DEPARTMENT mit demselben
DNUMBER–Eintrag.
Da SSN und DNUMBER die Primärschlüssel ihrer Tabellen sind, gibt es
jeweils maximal ein referenziertes Tupel.
Prof. Dr. Dietmar Seipel 74
Vorlesung Datenbanken Wintersemester 2013/14
EMPLOYEE
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
DEPARTMENT
DNAME DNUMBER MGRSSN MGRSTARTDATE
Headquarters 1 111111111 1971-06-19
Administration 4 333333333 1985-01-01
Research 5 222222222 1978-05-22
6
6
Jede DNO–Zelle in EMPLOYEE referenziert genau eine DNUMBER–Zelle in
DEPARTMENT mit demselben Attributwert. Analog für SUPERSSN.
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 alsAttribut MGRSSN in die Relation DEPARTMENT integriert wurde,führt zu
FK3 = ( DEPARTMENT, {MGRSSN}, _, {EMPLOYEE}, _, _ ).
Das Attribut MGRSSN von DEPARTMENT referenziert den SchlüsselSSN von EMPLOYEE.
4. Die N:M–Beziehung WORKS_ON, welche zu einer eigenen Relationwurde, führt zu je einer Fremdschlüsselbedingung auf die beteiligtenRelationen:
FK4 = ( WORKS_ON, {ESSN}, _, {EMPLOYEE}, _, _ )
FK5 = ( WORKS_ON, {PNO}, _, {PROJECT}, _, _ )
Das Attribut ESSN von WORKS_ON referenziert den Schlüssel SSNvon EMPLOYEE. Das Attribut PNO von WORKS_ON referenziert denSchlü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 Eibzw. Ee.
Prof. Dr. Dietmar Seipel 78
Vorlesung Datenbanken Wintersemester 2013/14
Fremdschlüssel stelleninter–relationaleBedingungen für
Datenbankschemata dar.
Definition (Datenbankschema, Datenbank–Instanz)
1. Ein relationales DatenbankschemaS = (R,FK) besteht aus
• einer Menge von Relationenschemata,
R = {R1, . . . , Rn },
• und einer MengeFK von Fremdschlüsseln aufR.
2. Eine erlaubteDatenbank–InstanzvonS ist eine Menge
I = { r1, . . . , rn }
von Relationen für die RelationenschemataRi, welche die
Fremdschlüsselbedingungen ausFK erfüllen.
3. Dann istDB = (S, I) einerelationale 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.
DELETE – undUPDATE–Modi für Fremdschlüsselbedingungen
NO ACTION: Es erfolgt keine Aktion.
SET NULL , SET DEFAULT:Beim Löschen und Update von referenzierten Tupeln werden die Werteder referenzierenden Attribute auf NULL bzw. auf den DEFAULT–Wertgesetzt. Dies erfolgt nur, soweit keine NOTNULL–Bedingungen verletztwerden; ansonsten muß die Operation abgelehnt werden.
CASCADE:Beim Löschen von referenzierten Tupeln werden alle referenzierendenTupel ebenfalls gelöscht. Beim Update werden die Werte derreferenzierenden Fremdschlüsselattribute auf die neuen Werte derreferenzierten Attribute gesetzt.
Prof. Dr. Dietmar Seipel 80
Vorlesung Datenbanken Wintersemester 2013/14
Zyklische Fremdschlüsselbedingungen müssen geeignet behandelt werden.
Beispiel (Zyklus)
DEPARTMENT
EMPLOYEE
DNUMBER MGRSSN
SSN DNO......
... ...
Prof. Dr. Dietmar Seipel 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 Tupelt = (t1, . . . , tn) undt′ = (t′1, . . . , t′m) ist
t× t′ = (t1, . . . , tn, t′1, . . . , t
′m).
2. Für zwei Relationenr unds überVr bzw.Vs betrachten wir daskartesische Produktr × s = { t× t′ | t ∈ r, t′ ∈ s } als eine Relationüber der VereinigungV = Vr ∪· Vs mit Duplikaten;V ist eine Multimenge, fallsVr ∩ Vs 6= ∅. r × s enthält|r| · |s| Tupel.
r
A B
1 2
5 6
s
C D
2 5
2 6
3 7
⇒
r × s
A B C D
1 2 2 5
1 2 2 6
1 2 3 7
5 6 2 5
5 6 2 6
5 6 3 7
Prof. Dr. Dietmar Seipel 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 Tupelt mit def (θ) ⊆ def (t) gilt genau dannθ(t) = true,
wennt(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 Tupelt mit def (θ) ⊆ def (t) gilt genau dannθ(t) = true,
wennt(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 Tupelt mit def (θ1) ∪ def (θ2) ⊆ def (t) gilt
(θ1 AND θ2)(t) = θ1(t) AND θ2(t),
(θ1 OR θ2)(t) = θ1(t) OR θ2(t).
Für ein Tupelt 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 Definitionsbereichdef (θ) eines Suchprädikatsθ ist also immer die
Menge aller inθ vorkommenden AttributeA ∈ U .
Prof. Dr. Dietmar Seipel 85
Vorlesung Datenbanken Wintersemester 2013/14
spezifische relationale Operatoren
SieR die Menge aller Relationen über einer AttributmengeV ⊆ U .
Selektion: σθ : R −→ R
Fallsdef (θ) ⊆ def (r), so enthältσθ(r) alle Tupel ausr, welche dasSuchprädikatθ erfüllen:
σθ(r) := { t ∈ r | θ(t) = true}.
Berechnung:|r| Schritte
Projektion : πV : R −→ R
FallsV ⊆ def (r), so erhalten wirπV (r) durch Projektion allerTupel ausr aufV :
πV (r) := { t[V ] | t ∈ r }.
Berechnung:|r| Schritte
Prof. Dr. Dietmar Seipel 86
Vorlesung Datenbanken Wintersemester 2013/14
Selektion und Projektion:
r
A B
1 2
4 3
5 6
⇒
σA<B(r)
A B
1 2
5 6
ΠA(r)
A
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) undV = V1 ∪· V2 die
Vereinigung mit Duplikaten.
Fallsdef (θ) ⊆ V , so istr1 ⊲⊳θ r2 die Relation überV mit allen
Tupelnt des kartesischen Produktsr1 × 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 Tupelt1 ∈ r1 alle Tupelt2 ∈ 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 überr2 verwenden.−→ |r1| · log2 |r2| Schritte
Beispiel:EMPLOYEE⊲⊳DNO=DNUMBER DEPARTMENT
Prof. Dr. Dietmar Seipel 88
Vorlesung Datenbanken Wintersemester 2013/14
Equi–Join:
r
A B
1 2
3 4
5 6
s
C D
2 5
2 6
3 7
4 8
⇒
r ⊲⊳B=C s
A B C D
1 2 2 5
1 2 2 6
3 4 4 8
Das kartesische Produkt vonr unds hat3 · 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
SieVi = def (ri) undV = V1 ∪ V2 die Vereinigung ohne Duplikate.Fallsdef (θ) ⊆ V , so ist der Natural Joinr1 ⊲⊳ r2 die Menge allerTupel überV , deren Projektionen aufVi in ri liegen:
r1 ⊲⊳ r2 := { t | def (t) = V ∧ t[V1] ∈ r1 ∧ t[V2] ∈ r2 }.
Zwei Tupelti ∈ ri werden zu einem Tupelt ∈ r1 ⊲⊳ r2 verbunden,falls sie auf den gemeinsamen Attributen übereinstimmen:t1[V1 ∩ V2] = t2[V1 ∩ V2] :
t(A) =
t1(A), für A ∈ V1 \ V2,
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 überV1 ∩ 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′).
r
A B
1 2
3 4
5 6
s′
B D
2 5
2 6
3 7
4 8
⇒
r ⊲⊳ r.B=s′.B s′
A B B D
1 2 2 5
1 2 2 6
3 4 4 8
r ⊲⊳ s′
A B D
1 2 5
1 2 6
3 4 8
Der Equi–Joinr ⊲⊳ r.B=s′.B s′ ohne Projektion würde hingegen eine
Relation erzeugen, in der das AttributB 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 ausr bzw.s, die keinen Join–Partner finden, werden durchNULL–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 Joinu auf die beiden AttributmengenVr bzw.Vs und Elimination der reinenNULL–Tupel erhält manwieder die ursprünglichen Relationen:
r = σA 6=NULL OR B 6=NULL (ΠA,B(u)),
s = σC 6=NULL OR D 6=NULL (ΠC,D(u)).
Prof. Dr. Dietmar Seipel 92
Vorlesung Datenbanken Wintersemester 2013/14
Division (Faktorisierung): ÷ : R×R −→ R
Fallsdef (s) ⊆ def (r), so definieren wirr ÷ s als die maximaleRelation überdef (r) \ def (s), deren kartesisches Produkt mits 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 derAttribute. FallsV = {A1, . . . , Ak } ⊆ def (r), so erhält man dieRelation̺Θ(r), indem jedes AttributAi in Bi umbenannt wird;AttributeA von r, mit A 6∈ V , bleiben unverändert.
Prof. Dr. Dietmar Seipel 93
Vorlesung Datenbanken Wintersemester 2013/14
r =
A B C
7 1 2
7 3 4
9 1 2
s =
B C
1 2
3 4
⇒ r ÷ s =A
7
r =
A B C
7 1 2
7 3 4
9 1 2
9 3 4
s =
B C
1 2
3 4
⇒ r ÷ s =
A
7
9
Für ganze Zahlen gilt z.B.3÷ 2 = 1 und4÷ 2 = 2.
Prof. Dr. Dietmar Seipel 94
Vorlesung Datenbanken Wintersemester 2013/14
Beispiel (COMPANY)
Finde die Namen aller Angestellten heraus, die an allen Projektenmitarbeiten, 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)).
r1
PNO
1
2
3
r2
ESSN PNO
222222222 2
444444444 1
444444444 2
444444444 3
555555555 3
666666666 1
666666666 2
666666666 3
666666666 10
r2 ÷ r1
ESSN
444444444
666666666
r3
FNAME LNAME
John Smith
Joyce English
Der Join zur Berechnung vonr3 könnte nach Umbenennung vonr2 ÷ 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 Algebrafrühzeitig angewendet werden, da sie die Anzahl der Tupel einer Relationunter Umständen deutlich verkleinern können:
r
A B
a b1
. . . . . .
a bn
s
A C
a c1
. . . . . .
a cn
⇒
ΠA(r)
A
a
σC=ci(s)
A C
a ci
Der Joinr ⊲⊳ s enthältn2 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ß manr ⊲⊳ s
berechnen muß. Hier enthälts′ nur1 Tupel, denns′ = σC=ci(s).
Prof. Dr. Dietmar Seipel 96
Vorlesung Datenbanken Wintersemester 2013/14
2.3 Die relationale Datenbanksprache SQL
RelationenalgebraDer Benutzer muß beim Formulieren der Anfrage angeben, auf welche Weise
das gewünschte Resultat berechnet werden soll.
Deklarative High–Level–SprachschnittstellenDer 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
CREATE 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 AttributkombinationK wird mittelsPRIMARY 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 AttributkombinationenX können mittelsUNIQUE (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 AttributkombinationenX auf
einen SchlüsselK einer TabelleT werden mittels
FOREIGN K EY (X) REFERENCES T (K)
vereinbart.
Erzeugen einer Datenbank
Vor dem ersteCREATE TABLE –Statement muß man die Datenbank
erzeugen und eine Verbindung damit aufbauen:
CREATE DATABASE COMPANY;
USE COMPANY;
Alle folgenden SQL–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 NULL,
MINIT CHAR,
LNAME VARCHAR(15) NOT NULL,
SSN CHAR(9) NOT NULL,
BDATE DATE,
ADDRESS VARCHAR(30),
SEX CHAR,
SALARY DECIMAL(10,2),
SUPERSSN CHAR(9),
DNO INT NOT NULL,
Prof. Dr. Dietmar Seipel 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) NOT NULL,
DNUMBER INT NOT NULL,
MGRSSN CHAR(9) NOT NULL,
MGRSTARTDATE DATE,
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN)
REFERENCES EMPLOYEE(SSN) );
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
SELECT–FROM –WHERE–Block:
Verglichen mit derRelationenalgebrarealisiert derSELECT–Teil eine
Projektion auf die interessierenden Attribute, derFROM–Teil realisiert den
Join der genannten Tabellen, und derWHERE–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 überSchlüssel/Fremdschlüssel–Beziehungen; dies hilft
beim Finden geeigneter Join–Bedingungen. Außerdem könnendie
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.
SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME=’John’ AND MINIT=’B’ AND LNAME=’Smith’
Ergebnis:
BDATE ADDRESS
1955-01-09 731 Fondren, Houston, TX
In der Relationenalgebra:
ΠBDATE, ADDRESS(σFNAME=’John’ ∧ M INIT=’B’ ∧ LNAME=’Smith’(EMPLOYEE)).
Prof. Dr. Dietmar Seipel 117
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage (Projektion und Join)
Bestimme für alle Angestellten – gegeben durch Vornamen undNachnamen – ihre Projekte – gegeben durch die Projektnamen –und die Anzahl der daran gearbeiteten Stunden.
SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSNAND PNO=PNUMBER
Jeder Tabellenname imFROM–Teil steht für ein Tupel.
EMPLOYEE
FNAME . . . LNAME SSN . . .
WORKS_ON
ESSN PNO HOURS
PROJECT
PNAME PNUMBER . . .
In der Relationenalgebra:
ΠFNAME , LNAME , PNAME , HOURS(
EMPLOYEE ⊲⊳SSN = ESSN
WORKS_ON ⊲⊳PNO = PNUMBER PROJECT).
Prof. Dr. Dietmar Seipel 118
Vorlesung Datenbanken Wintersemester 2013/14
Für jedes Tripel von Tupeln, welches dieWHERE–Bedingung erfüllt, kann man
durch Projektion auf die Attribute aus demSELECT –Teil ein Resultatstupel bilden.
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
Prof. Dr. Dietmar Seipel 119
Vorlesung Datenbanken Wintersemester 2013/14
Mit binären Joins in der Relationenalgebra:
ΠFNAME , LNAME , PNAME , HOURS(
EMPLOYEE ⊲⊳SSN = ESSN ( WORKS_ON ⊲⊳PNO = PNUMBER PROJECT) ).
Zugehöriger Operatorbaum:
O1:
ΠFNAME , LNAME , PNAME , HOURS
⊲⊳SSN = ESSN
EMPLOYEE ⊲⊳PNO = PNUMBER
WORKS_ON PROJECT
I�
I�
6
Prof. Dr. Dietmar Seipel 120
Vorlesung Datenbanken Wintersemester 2013/14
Mit binären Joins in der Relationenalgebra:
ΠFNAME , LNAME , PNAME , HOURS(
( EMPLOYEE ⊲⊳SSN = ESSN WORKS_ON ) ⊲⊳PNO = PNUMBER PROJECT).
Zugehöriger Operatorbaum:
O2:
ΠFNAME , LNAME , PNAME , HOURS
⊲⊳SSN = ESSN
EMPLOYEE
⊲⊳PNO = PNUMBER
WORKS_ON
PROJECT
I�
I�
6
Prof. Dr. Dietmar Seipel 121
Vorlesung Datenbanken Wintersemester 2013/14
Das folgendeMengendiagrammzeigt die Attributmengen der beteiligtenRelationen. Es ist angereichert um die Join–Bedingungen (in blau und rot).Es sind nur die Projektions– bzw. Join–Attribute angezeigt.
EMPLOYEE WORKS_ON PROJECT
FNAME
LNAMESSN ESSN
HOURS
PNOPNUM-
BER PNAME
Der Join sollte immer von der zentralen Relation WORKS_ON ausgehen.Es ist sinnlos, zuerst EMPLOYEE und PROJECTzu joinen, denn dies wäre einkartesisches Produkt.
Hier erscheint auch einternärer (Drei–Wege–) Join sinnvoll, bei dem ineinem Durchlauf für jedes Tupel aus WORKS_ON indexunterstützt diepassenden 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.
SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME=’Research’AND DNUMBER=DNO
Ergebnis:
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:ΠFNAME , LNAME , ADDRESS( σDNAME=’Research’(
EMPLOYEE ⊲⊳DNUMBER=DNO DEPARTMENT) ).
Prof. Dr. Dietmar Seipel 123
Vorlesung Datenbanken Wintersemester 2013/14
In der Relationenalgebra:
ΠFNAME , LNAME , ADDRESS( σDNAME=’Research’(
EMPLOYEE ⊲⊳DNUMBER=DNO DEPARTMENT) ).
Zugehöriger Operatorbaum:
O1:
ΠFNAME , LNAME , ADDRESS
σDNAME=’Research’
⊲⊳DNUMBER=DNO
EMPLOYEE DEPARTMENT
6
6
I�
Prof. Dr. Dietmar Seipel 124
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage–Optimierung: Push Selection into Join
ΠFNAME , LNAME , ADDRESS(
EMPLOYEE ⊲⊳DNUMBER=DNO σDNAME=’Research’(DEPARTMENT) ).
Zugehöriger Operatorbaum:
O2:
ΠFNAME , LNAME , ADDRESS
σDNAME=’Research’
⊲⊳DNUMBER=DNO
EMPLOYEE
DEPARTMENT
6
6
I�
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.
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN
AND PLOCATION=’Stafford’
Ergebnis:
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
Prof. Dr. Dietmar Seipel 126
Vorlesung Datenbanken Wintersemester 2013/14
Zusammenhang mit der Relationenalgebra
SELECT DISTINCT eliminiert doppelte Tupel aus dem Resultat
1. Projektion: πA1, ... ,Ak(R), mit {A1, . . . , Ak} ⊆ def (R):
SELECT DISTINCT A1, . . . , Ak FROM R
2. Selektion: σA=B(R), mit A,B ∈ def (R):
SELECT DISTINCT *
FROM R
WHERE A = B
Prof. Dr. Dietmar Seipel 127
Vorlesung Datenbanken Wintersemester 2013/14
3. Vereinigung | Durchschnitt | Differenz: R ∪ S, R ∩ S, R \ S
SELECT DISTINCT * FROM R
UNION | I NTERSECT | EXCEPT
SELECT DISTINCT * FROM 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}
SELECT DISTINCT A1, . . . , An, R.B1, . . . , R.Bm, C1, . . . , Cl
FROM R, S
WHERE R.B1 = S.B1 AND . . . AND 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 AttributnamenA durch Voranstellen des
RelationennamensR eindeutig machen (R.A), und
• man kann ein AliasR′ für einen RelationennamenR vergeben (R R′,
z.B. EMPLOYEE E).
Beides ist z.B. erforderlich, wenn man eine Relation mit sich selbst joint.
Anfrage 8
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE E, EMPLOYEE S
WHERE E.SUPERSSN=S.SSN
Prof. Dr. Dietmar Seipel 129
Vorlesung Datenbanken Wintersemester 2013/14
Die Vorgesetzten–Hierarchie:
4444444445: John Smith
5555555555: Ramesh Narayan
6666666665: Joyce English
7777777774: Alicia Zelaya
8888888884: Ahmad Jabbar
2222222225: Franklin Wong
3333333334: Jennifer Wallace
1111111111: James Borg
� j? R
� j
Prof. Dr. Dietmar Seipel 130
Vorlesung Datenbanken Wintersemester 2013/14
EMPLOYEE
FNAME MINIT LNAME SSN . . . SUPERSSN . . .
John B Smith 444444444 . . . 222222222 . . .
Franklin T Wong 222222222 . . . 111111111 . . .
. . . . . . . . . . . . . . . . . . . . .
E
S
Ergebnis:
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
Prof. Dr. Dietmar Seipel 131
Vorlesung Datenbanken Wintersemester 2013/14
Anfragen ohne WHERE–Klausel, Benutzung von ’*’ (Wildcard)
Anfrage 9 (Projektion)
Suche alle EMPLOYEE SSNs in der Datenbank.
SELECT SSN
FROM EMPLOYEE
Ergebnis:
SSN
444444444
222222222
777777777
333333333
555555555
666666666
888888888
111111111
in der Relationenalgebra:ΠSSN(EMPLOYEE)
Prof. Dr. Dietmar Seipel 132
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 1C (Selektion)
SELECT *
FROM EMPLOYEE
WHERE 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: σDNO=5(EMPLOYEE)
Prof. Dr. Dietmar Seipel 133
Vorlesung Datenbanken Wintersemester 2013/14
Tabellen als Mengen
Anfrage 11
Hole das Gehalt aller Angestellten aus der Datenbank.
SELECT SALARY
FROM EMPLOYEE
SELECT DISTINCT SALARY
FROM 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.
( SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSN=SSNAND LNAME=’Smith’ )
UNION
( SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER
AND MGRSSN=SSNAND LNAME=’Smith’ )
Prof. Dr. Dietmar Seipel 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’:
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE (PNO, HOURS) I N (
SELECT PNO, HOURS
FROM WORKS_ON
WHERE 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:
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE SALARY > ALL I N (
SELECT SALARY
FROM EMPLOYEE
WHERE DNO = 5 );
Auch möglich mit<,=, <=, >=, <>, SOME, ANY
Prof. Dr. Dietmar Seipel 137
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 4A
SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER I N (
SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM = DNUMBER
AND MGRSSN = SSNAND LNAME=’Smith’ )
OR PNUMBER I N (
SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSN = SSNAND 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.
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE SSN I N (
SELECT ESSN
FROM DEPENDENT
WHERE SSN = ESSN
AND FNAME = DEPENDENT_NAME
AND E.SEX = SEX )
Das Attribut SEX ohne Präfix E im innerenSELECT –Statement bezieht sich
auf DEPENDENT.
Prof. Dr. Dietmar Seipel 139
Vorlesung Datenbanken Wintersemester 2013/14
Eine alternative Formulierung mit einem flachen SELECT–Statement wäre:
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E, DEPENDENT D
WHERE E.SSN = D.ESSN
AND E.FNAME = D.DEPENDENT_NAME
AND E.SEX = D.SEX
Hier sind zwei Aliase, E und D, erforderlich, um das AttributSEX 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 Angestelltenheraus,die an einem der Projekte 1, 2 oder 3 arbeiten.
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE PNO I N (1, 2, 3)
Alternativ – aber weniger elegant – könnte man die folgende,etwas längereWHERE–Bedingung schreiben:
WHERE PNO = 1
OR PNO = 2
OR PNO = 3
Prof. Dr. Dietmar Seipel 141
Vorlesung Datenbanken Wintersemester 2013/14
NULL–Werte
Anfrage 14
Finde die Namen aller Angestellten heraus, die keinen Vorgesetzten haben.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSNI S NULL
Eigentlich würde bereits “SUPERSSN =NULL ” 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 Projektenmitarbeiten, welche von Abteilung Nummer 5 kontrolliert werden.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ( ( SELECT PNO
FROM WORKS_ON
WHERE SSN = ESSN )
CONTAINS
( SELECT PNUMBER
FROM PROJECT
WHERE DNUM = 5 ) )
Diese Anfrage mitCONTAINS kann in der Relationenalgebra mittels einerDivision ausgewertet werden.
Prof. Dr. Dietmar Seipel 143
Vorlesung Datenbanken Wintersemester 2013/14
EXISTS
Anfrage 12B
Suche die Namen aller Angestellten, die einen Angehörigen mit gleichem
Vornamen und Geschlecht haben.
SELECT E.FNAME, E.LNAME
FROM EMPLOYEE E
WHERE EXISTS (
SELECT *
FROM DEPENDENT
WHERE E.SSN = ESSN
AND E.SEX = SEX
AND E.FNAME = DEPENDENT_NAME )
Prof. Dr. Dietmar Seipel 144
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 6
Suche die Namen aller Angestellten, die keine Angehörigen haben.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS (
SELECT *
FROM DEPENDENT
WHERE SSN = ESSN )
Prof. Dr. Dietmar Seipel 145
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 7
Führe die Namen aller Abteilungsleiter auf, die mindestenseinen
Angehörigen haben.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS (
SELECT *
FROM DEPENDENT
WHERE SSN = ESSN )
AND EXISTS (
SELECT *
FROM DEPARTMENT
WHERE SSN = MGRSSN )
Prof. Dr. Dietmar Seipel 146
Vorlesung Datenbanken Wintersemester 2013/14
Eine alternative Formulierung mit einem flachen SELECT–Statement, diezusätzlich noch die Vornamen der jeweiligen Angehörigen ausgibt, wäre:
SELECT FNAME, LNAME, DEPENDENT_NAME
FROM EMPLOYEE, DEPENDENT, DEPARTMENT
WHERE SSN = ESSN
AND 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 mittelsDISTINCT 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.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS
(SELECT *
FROM WORKS_ON B
WHERE B.PNOI N (SELECT PNUMBER
FROM PROJECT
WHERE DNUM=5)
AND NOT EXISTS (SELECT *
FROM WORKS_ON C
WHERE C.ESSN=SSN
AND C.PNO=B.PNO))
Prof. Dr. Dietmar Seipel 148
Vorlesung Datenbanken Wintersemester 2013/14
Umbenennung von Attributen und explizite Joins
Anfrage 8A
SELECT E.LNAME AS EMPLOYEE,
S.LNAME AS SUPERVISOR
FROM EMPLOYEEAS E, EMPLOYEEAS S
WHERE E.SUPERSSN=S.SSN
Ergebnis:
EMPLOYEE SUPERVISOR
Smith Wong
Wong Borg
Zelaya Wallace
. . . . . .
Prof. Dr. Dietmar Seipel 149
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 1A
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEEJOIN DEPARTMENTON DNO=DNUMBER)
WHERE DNAME=’Research’
Die Join–Bedingung kann in denFROM–Teil gezogen werden.
Anfrage 1B
SELECT FNAME, LNAME, ADDRESS
FROM (EMPLOYEENATURAL JOIN
(DEPARTMENTAS D(DNAME, DNO, X, Y)))
WHERE DNAME=’Research’
Die Join–Bedingung DNO=DNUMBER wird beim NATURAL JOIN in 1B
dadurch realisiert, daß DNUMBER von DEPARTMENT mittelsAS 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:
SELECT E.LNAME AS EMPLOYEE,
S.LNAME AS SUPERVISOR
FROM (EMPLOYEE ELEFT 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 rechtenTabelle 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
SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM ( ( PROJECT
JOIN DEPARTMENTON DNUM=DNUMBER )
JOIN EMPLOYEEON MGRSSN=SSN )
WHERE 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 Durchschnittder Gehälter aller Angestellten.
SELECT SUM (SALARY), M AX (SALARY),
M IN (SALARY), AVG (SALARY)
FROM EMPLOYEE
Nullwerte (beiSALARY) werden bei der Aggregation nicht berücksichtigt.
Anfrage 18
Bestimme die Anzahl der Mitarbeiter in der Abteilung ’Research’.
SELECT COUNT(*)FROM EMPLOYEE, DEPARTMENT
WHERE DNO=DNUMBER AND DNAME=’Research’
Prof. Dr. Dietmar Seipel 153
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 19
Bestimme die Anzahl verschiedener Löhne in der Datenbank.
SELECT COUNT (DISTINCT SALARY)
FROM EMPLOYEE
Anfrage 20
Bestimme für alle Abteilungen die Abteilungsnummer, die Anzahl der
Angestellten und das Durchschnittsgehalt.
SELECT DNO, COUNT(*) A S EMPS,AVG (SALARY)
FROM EMPLOYEE
GROUP BY DNO
Prof. Dr. Dietmar Seipel 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}
Danach werden die Gruppen zusammengefaßt.
DNO EMPS AVG(SALARY)
5 4 33250
4 3 31000
1 1 55000
Prof. Dr. Dietmar Seipel 155
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 5
Bestimme die Namen und Vornamen aller Angestellten, die mindestens zwei
Angehörige haben.
SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE ( SELECT COUNT(*)
FROM DEPENDENT
WHERE SSN = ESSN )>= 2
Prof. Dr. Dietmar Seipel 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.
SELECT PNUMBER, PNAME,COUNT(*) A S EMPS
FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME
Prof. Dr. Dietmar Seipel 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.
SELECT PNUMBER, PNAME,COUNT(*) A S EMPS
FROM PROJECT, WORKS_ON
WHERE PNUMBER=PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT(*) > 2
Prof. Dr. Dietmar Seipel 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.
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
Prof. Dr. Dietmar Seipel 159
Vorlesung Datenbanken Wintersemester 2013/14
Dann wird die Bedingung “HAVING COUNT(*) > 2” ausgewertet.
PNAME PNUMBER . . . 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 30 . . . 777777777 30 30.0
Prof. Dr. Dietmar Seipel 160
Vorlesung Datenbanken Wintersemester 2013/14
Und schließlich werden noch die Gruppen zusammengefaßt.
PNUMBER PNAME EMPS
2 ProductY 3
10 Computerization 3
20 Reorganization 3
30 Newbenefits 3
Prof. Dr. Dietmar Seipel 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.
SELECT PNUMBER, PNAME,COUNT(*) A S EMPS
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER=PNOAND SSN=ESSNAND DNO=5
GROUP BY PNUMBER, PNAME
Prof. Dr. Dietmar Seipel 162
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 24
Es sind alle Abteilungen gesucht, die mehr als 5 Angestelltehaben; zu diesenAbteilungen ist auch die Anzahl der Angestellten mit mehr als 40.000 $Verdienst gesucht.
SELECT DNAME, COUNT(*) A S EMPS
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO AND SALARY > 40000
AND DNO I N (
SELECT DNO
FROM EMPLOYEE
GROUP BY DNO
HAVING COUNT(*) > 5 )
GROUP BY DNAME
Prof. Dr. Dietmar Seipel 163
Vorlesung Datenbanken Wintersemester 2013/14
Anfrage 24A
Es sind alle Abteilungen gesucht, die mehr als 5 Angestelltemit mehr als
40.000 $ Verdienst haben; zu diesen Abteilungen ist auch dieAnzahl dieser
Angestellten gesucht.
SELECT DNAME, COUNT(*) A S EMPS
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO AND SALARY > 40000
GROUP BY DNAME
HAVING COUNT(*) > 5
Prof. Dr. Dietmar Seipel 164
Vorlesung Datenbanken Wintersemester 2013/14
Regeln für SELECT –Statements mit GROUP BY und HAVING
1. Zuerst wird der Join der Relationen aus derFROM–Klausel basierend
auf den Selektions– und Joinbedingungen derWHERE–Klausel
berechnet.
2. Dann wird mittelsGROUP BY die Gruppierung vorgenommen, und die
Aggregationsfunktionen werden auf die entstandenen Gruppen
angewendet; Nullwerte werden bei der Aggregation ignoriert.
3. DieHAVING –Klausel selektiert dann einzelne Gruppen.
4. In derSELECT –Klausel sollten nur Attributwerte, nach denen gruppiert
wurde, ohne Aggregatsfunktion vorkommen – zusammen mit den
berechneten AggregatswertenAGG(A).
5. Alle nicht aggregierten Attribute aus derSELECT –Klausel sollten
normalerweise imGROUP BY vorkommen.
Prof. Dr. Dietmar Seipel 165
Vorlesung Datenbanken Wintersemester 2013/14
Vergleich von SELECT – und GROUP BY–Klausel
In der folgenden Anfrage treten genau die Attributwerte, nach denengruppiert wird, in derSELECT –Klausel ohne Aggregatsfunktion auf.
SELECT DNO, COUNT(*)
FROM EMPLOYEE
GROUP BY DNO
Das Ergebnis zeigt wie sich die 8 Mitarbeiter auf die 3 Abteilungen
(DNO=1,4,5) verteilen.
DNO COUNT(*)
1 1
4 3
5 4
Prof. Dr. Dietmar Seipel 166
Vorlesung Datenbanken Wintersemester 2013/14
Man kann auch nach DNO gruppieren, ohne daß DNO in der
SELECT –Klausel auftritt.
SELECT COUNT(*)
FROM EMPLOYEE
GROUP BY DNO
Dann wird für jede Abteilung die Anzahl der Mitarbeiter berechnet,
allerdings ohne Zuordnung zur Abteilungsnummer.
COUNT(*)
1
3
4
Prof. Dr. Dietmar Seipel 167
Vorlesung Datenbanken Wintersemester 2013/14
In MySQL können in derSELECT –Klausel sogar Attributwerte, nach denen
nicht gruppiert wurde, ohne Aggregatsfunktion auftreten.
Dann ist allerdings das Ergebnis meist unsinnig.
SELECT DNO, COUNT(*)
FROM EMPLOYEE
Diese Anfrage berechnet die Anzahl 8 aller Mitarbeiter der gesamten Firma
und gibt sie zusammen mit der Abteilungsnummer 1 aus.
DNO COUNT(*)
1 8
Prof. Dr. Dietmar Seipel 168
Vorlesung Datenbanken Wintersemester 2013/14
Teilstring–Vergleiche
Anfrage 25
Gesucht sind alle Angestellten, die in Houston, Texas, leben.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESSL IKE ’%Houston, TX%’
Anfrage 26
Finde alle Angestellten heraus, die in den 50er Jahren geboren wurden.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE L IKE ’_ _ 5 _–_ _–_ _’
Alternative: BDATEL 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 Falleeiner 10%–igen Lohnerhöhung bekommen würden.
SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN = ESSN
AND PNO = PNUMBERAND PNAME = ’ProductX’
ORDER BY FNAME, LNAME
Um FNAME absteigend zu sortieren, würde man dieORDER BY–Klausel
ersetzen durch: ORDER BY FNAME DESC, LNAME ASC.
Mittels SELECT 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–Ordnungaufsteigend.
Anfrage 28
SELECT DNAME, FNAME, LNAME, PNAME
FROM DEPARTMENT, EMPLOYEE,
WORKS_ON, PROJECT
WHERE DNUMBER = DNO
AND SSN = ESSN
AND PNO = PNUMBER
ORDER BY DNAME DESC, LNAME, FNAME
Prof. Dr. Dietmar Seipel 171
Vorlesung Datenbanken Wintersemester 2013/14
2.3.3 Update–Statements in SQL
Zum Einfügen eines einzelnen Tupelsin 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 beimCREATE 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 ineinem
einzigenI 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 derIntegritätsbedingungenist systemabhängig. Die
folgende Operation wird abgelehnt, da kein Wert für “SSN” angegeben ist:
I NSERT I NTO EMPLOYEE(FNAME, LNAME, DNO)
VALUES (’Robert’, ’Hatcher’, 2)
Prof. Dr. Dietmar Seipel 173
Vorlesung Datenbanken Wintersemester 2013/14
Es kann auch eine ganze mittels einesSELECT –Statements erzeugteRelation
eingefügt werden:
CREATE TABLE DEPT (
DNAME VARCHAR(15),
EMPS INTEGER,
TOTAL_SAL INTEGER );
DEPT
DNAME EMPS TOTAL_SAL
Administration 3 93000
Headquarters 1 55000
Research 4 133000INSERT INTO DEPT
SELECT DNAME, COUNT(* ), SUM(SALARY)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER = DNO
GROUP BY DNAME
Prof. Dr. Dietmar Seipel 174
Vorlesung Datenbanken Wintersemester 2013/14
DasLöschenvon Tupeln erfolgt mitDELETE ; auch hier ist die Überprüfung
von referentiellen Integritätsbedingungen systemabhängig.
DELETE FROM EMPLOYEE
WHERE LNAME=’Brown’
DELETE FROM EMPLOYEE
WHERE DNO I N (
SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = ’Research’ )
DELETE FROM EMPLOYEE
Das letzte Statement – ohneWHERE–Teil – löscht dabei alle Tupel.
Prof. Dr. Dietmar Seipel 175
Vorlesung Datenbanken Wintersemester 2013/14
DasUPDATE–Kommando
• In einerWHERE–Klausel werden die zu modifizierenden Tupel
spezifiziert.
• EineSET–Klausel spezifiziert die zu modifizierenden Attribute sowie
die zugehörigen Attributwerte.
UPDATE PROJECT
SET PLOCATION=’Bellaire’, DNUM=5
WHERE PNUMBER=10
Prof. Dr. Dietmar Seipel 176
Vorlesung Datenbanken Wintersemester 2013/14
Es können mehrere Tupel gleichzeitig mittels einerBerechnungsregel
modifiziert werden:
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.1
WHERE DNO I N (
SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = ’Research’ )
Wie bei Programmiersprachen bezeichnet ein Attributname auf der rechten
Seite einerZuweisungden 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 einevirtuelle 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 einSELECT –Statement spezifiziert:
CREATE V IEW E_WORKS_ON_P
AS SELECT FNAME, LNAME, PNAME, HOURS
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSNAND PNO=PNUMBER
Prof. Dr. Dietmar Seipel 178
Vorlesung Datenbanken Wintersemester 2013/14
E_WORKS_ON_P
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
Prof. Dr. Dietmar Seipel 179
Vorlesung Datenbanken Wintersemester 2013/14
CREATE V IEW DEPT (DNAME, EMPS, TOTAL_SAL)
AS SELECT DNAME, COUNT(*) , SUM (SALARY)
FROM DEPARTMENT,
EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY 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:
DROP V IEW E_WORKS_ON_P
DROP V IEW DEPT
Prof. Dr. Dietmar Seipel 180
Vorlesung Datenbanken Wintersemester 2013/14
An Views können in üblicher WeiseAnfragengestellt werden. Dieserleichtert daswiederholteStellen ähnlicher Anfragen.
SELECT FNAME, LNAME
FROM E_WORKS_ON_P
WHERE PNAME = ’ProjectX’
Ein View ist immeraktuell, er reflektiert automatisch Änderungen, welchesich aus Modifikationen der definierenden Tabellen ergeben.
Zur effizienten Implementierunggibt es zwei wichtige Strategien:
1. Anfrage–Modifizierung: bildet eine Anfrage an einen ViewaufAnfragen an die definierenden Relationen ab.
2. View–Materialisierung: bei der ersten Anfrage an einen View wirddieser 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
UPDATE–Operationen auf einem View können (manchmal) auf die
definierenden Tabellen propagiert werden.
UPDATE E_WORKS_ON_P
SET PNAME = ’ProductY’
WHERE LNAME = ’Smith’
AND FNAME = ’John’
AND 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 meistnicht 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
UPDATE WORKS_ON
SET PNO = (SELECT PNUMBERFROM PROJECT
WHERE PNAME=’ProductY’ )
WHERE ESSN = (SELECT SSNFROM EMPLOYEE
WHERE LNAME=’Smith’ AND FNAME=’John’ )
AND PNO = (SELECT PNUMBERFROM PROJECT
WHERE 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
DELETE FROM PROJECT
WHERE PNAME=’ProductY’
UPDATE PROJECT
SET PNAME=’ProductY’
WHERE 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 folgendeRegeln:
• Ein View über einer einzigen definierenden Tabelle ist modifizierbar,
falls die View–Attribute denPrimärschlüsseloder einen anderen
Schlüsselkandidatender Basisrelation enthalten, denn dann entspricht
jedes virtuelle Tupel des Views genau einem Basis–Tupel.
CREATE V IEW EMP ( LNAME, SSN )
AS SELECT LNAME, SSN
FROM EMPLOYEE
UPDATE EMP
SET LNAME = ’Armstrong’
WHERE SSN = ’444444444’
EMP
LNAME SSN
Smith 444444444
Wong 222222222
Zelaya 777777777
Wallace 333333333
Narayan 555555555
English 666666666
Jabbar 888888888
Borg 111111111
Prof. Dr. Dietmar Seipel 185
Vorlesung Datenbanken Wintersemester 2013/14
• Views, welche mittelsGruppierungundAggregatsfunktionendefiniert
sind, sind nicht modifizierbar.
UPDATE DEPT
SET TOTAL_SAL = 100000
WHERE DNAME=’Research’
DEPT
DNAME EMPS TOTAL_SAL
Administration 3 93000
Headquarters 1 55000
Research 4 133000
• Views über mehreren definierenden Tabellen (mit Joins) sindi.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 eineBedingungan und eineAktion,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 einephysikalische 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 –
beimCREATE TABLE – ein Index angelegt.
Prof. Dr. Dietmar Seipel 188
Vorlesung Datenbanken Wintersemester 2013/14
Das SchlüsselwortUNIQUE hinter demCREATE 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 denIndex 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)
SQL kann im Zusammenhang mit herkömmlichen,prozeduralen
Programmiersprachenwie JAVA , C++ oder C benutzt werden.Diese Sprachen nennt man dannHostsprachen.
import java.io. * ;
import java.sql. * ;
import java.util. * ;
import java.text. * ;
import java.lang.reflect. * ;
class MySQL_Query {
protected final String dbUser = "seipel";
protected final String dbPassword = "...";
protected String jdbcURL = "jdbc:mysql://localhost/comp any";
protected String jdbcDriver = "org.gjt.mm.mysql.Driver" ;
Prof. Dr. Dietmar Seipel 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