Schlüssel

Daten – Bank
4. Vorlesung
Dr. Karsten Tolle – PRG2 – SS 2012
Beispiel
Datum
(0,1)
Buch
BuchNr
Titel
Autor
verliehen
an
(0,n)
Entleiher
Nummer
Name
BUCH
(BuchNr, Titel, Autor)
ENTLEIHER
(Nummer, Name)
VERLIEHEN_AN(BuchNr, Nummer, Datum)
Dr. Karsten Tolle – PRG2 – SS 2012
2
Beispiel (besser!)
RückgabeDatum
(0,1)
Buch
BuchNr
Titel
Autor
verliehen
an
(0,n)
Entleiher
EntleiherNr Nachname
BUCH
(BuchNr, Titel, Autor)
ENTLEIHER
(EntleiherNr, Name)
VERLIEHEN_AN(BuchNr, EntleiherNr, RückgabeDatum)
Dr. Karsten Tolle – PRG2 – SS 2012
3
Beispiel (besser!)
RückgabeDatum
(0,1)
Buch
Nr
Titel
Autor
verliehen
an
(0,n)
Entleiher
Nr Nachname
BUCH
(Nr, Titel, Autor)
ENTLEIHER
(Nr, Name)
VERLIEHEN_AN(BuchNr, EntleiherNr, RückgabeDatum)
Dr. Karsten Tolle – PRG2 – SS 2012
4
SQL - Anfragen
• join
• group by …
• group by … having …
select
A1,A2, ... ,An
from
R1, R2, ... ,Rm
[where
conditions]
[group by clause]
[having
clause]
[order by clause];
Dr. Karsten Tolle – PRG2 – SS 2012
5
Group by …
• zum Gruppieren nach Spalten
Adresse
PLZ
ORT
STRASSE_NR
30419
Hannover
Schaumburgstr. 2
30419
Hannover
Quetlinburger Weg 12
37308
Schirmberg
Bergstraße 1
37308
Bodenrode
Hauptstraße 12
37308
Geismar
Bergstraße 1
PLZ
count(*)
35279
Neustadt
Gartenstraße 7
30419
2
53577
Neustadt
Gartenstraße 7
37308
3
35279
1
53577
1
Wie viele Eintragungen gibt es pro PLZ?
select PLZ, count(*) from Adresse group by PLZ;
Dr. Karsten Tolle – PRG2 – SS 2012
6
Group by …
• zum Gruppieren nach Spalten
PLZ
ORT
STRASSE_NR
30419
Hannover
Schaumburgstr. 2
PLZ
ORT
count(*)
30419
Hannover
Quetlinburger Weg 12
30419
Hannover
2
37308
Schirmberg
Bergstraße 1
37308
Schirmberg
1
37308
Bodenrode
Hauptstraße 12
37308
Bodenrode
1
37308
Geismar
Bergstraße 1
37308
Geismar
1
35279
Neustadt
Gartenstraße 7
35279
Neustadt
1
53577
Neustadt
Gartenstraße 7
53577
Neustadt
1
Wie viele Eintragungen gibt es pro Kombination aus PLZ und ORT?
select PLZ, Ort, count(*) from Adresse
group by PLZ, Ort;
Dr. Karsten Tolle – PRG2 – SS 2012
7
Group by … having …
• zum Gruppieren nach Spalten mit Bedingung
an die Gruppe!
PLZ
ORT
STRASSE_NR
30419
Hannover
Schaumburgstr. 2
30419
Hannover
Quetlinburger Weg 12
37308
Schirmberg
Bergstraße 1
37308
Bodenrode
Hauptstraße 12
37308
Geismar
Bergstraße 1
PLZ
ORT
count(*)
35279
Neustadt
Gartenstraße 7
30419
Hannover
2
53577
Neustadt
Gartenstraße 7
Wie viele Eintragungen gibt es pro Kombination aus PLZ und ORT,
beschränkt auf die Gruppen mit mindestens 2 Einträgen?
select PLZ, Ort, count(*) from Adresse
group by PLZ, Ort having count(*) >= 2;
Dr. Karsten Tolle – PRG2 – SS 2012
8
• Datenbankmanagementsystem (DBMS)
• Datenbank (DB)
• Datenbanksystem (DBS)
Dr. Karsten Tolle – PRG2 – SS 2012
9
Embedded SQL und C - Beispiel
…
EXEC SQL BEGIN DECLARE SECTION;
char dieBar[21], dasBier[21];
float preis;
EXEC SQL END DECLARE SECTION;
/* holen der Werte für dieBar und dasBier */
EXEC SQL SELECT price INTO :preis
FROM Verkauft
WHERE bar = :dieBar AND bier = :dasBier;
/* die Variable preis kann nun verwendet werden*/
…
Dr. Karsten Tolle – PRG2 – SS 2012
10
Embedded SQL definiert in SQL92
Für C, COBOL, Fortran
und andere
SQL
LIB
STD
LIB
Quelltext mit
SQL-Befehlen
Präcompiler
Quelltext in
reiner host
language
Compiler
Objektdatei
Linker
Ausführbares
Programm
Dr. Karsten Tolle – PRG2 – SS 2012
11
Anwendung 1
Elementare
Zugriffsoperationen
Anwendung 2
Elementare
Zugriffsoperationen
DatenbankManagementsystem
(DBMS)
Datenbank
(DB)
Anwendung 3
Elementare
Zugriffsoperationen
DBS
Anwendung 1
Anwendung 2
Anwendung 3
Zugriffsoperation
en in Progr.Sprache enthalten
Zugriffsoperation
en in Progr.Sprache enthalten
Zugriffsoperation
en in Progr.Sprache enthalten
Call Level Interface
DatenbankManagementsystem
(DBMS)
Datenbank
(DB)
DBS
Zum Selberlesen …
• Die CLI-Implementierung bildet die Befehlssätze
verschiedener DBMS auf eine immer gleiche
Funktionsbibliotek ab.
für das Programm an sich ist es unwichtig, mit welcher
Datenbank es arbeitet.
• Das CLI hat also die Funktion eines Übersetzers, der
Programmaufrufe in eine „Sprache“ übersetzt, die
die Datenbank versteht und umgekehrt Daten, die
von der Datenbank zurückgeliefert werden, so
formatiert, dass sie vom Programm verarbeitet
werden können.
Dr. Karsten Tolle – PRG2 – SS 2012
13
Vorteile CLI zu ESQL
Anwendung 2
Anwendung 1
• Kein Präcompiler nötig.
• Vorteil für Client/Server
Architektur, da unabhägig(er)
von der Zieldatenbank
Anwendung 3
Call Level Interface
Oracle
IBM DB2
Datenbank
(DB)
Datenbank
(DB)
• Programmierschnittstellen, die das CLIKonzept umsetzen
(CLI-Implementierungen), sind z.B.:
– Open Database Connectivity (ODBC),
– Java Database Connectivity (JDBC).
Dr. Karsten Tolle – PRG2 – SS 2012
15
Zugriff aus einem Programm heraus …
• Warum sind SQL-Statements wie:
SELECT * FROM …
INSERT INTO <table> VALUES …
zu vermeiden?
Besser:
SELECT a1, a2, a3, … FROM …
INSERT INTO <table> (a1, a2, a3, …) VALUES …
Dr. Karsten Tolle – PRG2 – SS 2012
16
FIRSTNME
LASTNME
BIRTH
STREET
NUMBER
TOWN
ZIP
Jon
Lucas
12.03.1969
{Zeil, Lange
Str.}
{12, 114}
{Frankfurt,
Frankfurt}
{60313,
60313}
Lucas
Jon
24.12.1980
Gräfstr.
27
Frankfurt
60325
…
…
…
…
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2012
17
1. Normalform
Definition:
Ein Relationenschema R ist in 1. Normalform
(1NF), wenn die Wertebereiche aller Attribute
von R atomar sind.
1.NF
⇔
Wertebereiche atomar
Dr. Karsten Tolle – PRG2 – SS 2012
18
1. Normalform
FIRSTNME
LASTNME
BIRTH
STREET
NUMBER
TOWN
ZIP
Jon
Lucas
12.03.1969
{Zeil, Lange
Str.}
{12, 114}
{Frankfurt,
Frankfurt}
{60313,
60313}
Lucas
Jon
24.12.1980
Gräfstr.
27
Frankfurt
60325
…
…
…
…
…
…
…
FIRSTNME
LASTNME
BIRTH
STREET
NUMBER
TOWN
ZIP
Jon
Lucas
12.03.1969
Zeil
12
Frankfurt
60313
Jon
Lucas
12.03.1969
Lange Str.
114
Frankfurt
60313
Lucas
Jon
24.12.1980
Gräfstr.
27
Frankfurt
60325
…
…
…
…
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2012
19
Diskutieren NF1
Dr. Karsten Tolle – PRG2 – SS 2012
20
Diskutieren NF1
PLZ
ORT
STRASSE_NR
Telefon
30419
Hannover
Schaumburgstr. 2
0511 271635
30419
Hannover
Quetlinburger Weg 12
0049 (0)511 279230
37308
Bodenrode
Hauptstraße 12
934790
35279
Neustadt
Gartenstraße 7
kukuk
Dr. Karsten Tolle – PRG2 – SS 2012
21
Nachteile nicht 1. NF
Dr. Karsten Tolle – PRG2 – SS 2012
22
Nachteile nicht 1. NF
• Finden wird erschwert bis unmöglich
• Sortierungen werden sinnlos bis unmöglich
Dr. Karsten Tolle – PRG2 – SS 2012
23
Schlüssel ?
PLZ
ORT
STRASSE_NR
30419
Hannover
Schaumburgstr. 2
30419
Hannover
Quetlinburger Weg 12
37308
Bodenrode
Hauptstraße 12
35279
Neustadt
Gartenstraße 7
Dr. Karsten Tolle – PRG2 – SS 2012
24
Schlüssel ?
PLZ
ORT
STRASSE_NR
30419
Hannover
Schaumburgstr. 2
30419
Hannover
Quetlinburger Weg 12
37308
Bodenrode
Hauptstraße 12
37308
Geismar
Bergstraße 1
35279
Neustadt
Gartenstraße 7
Dr. Karsten Tolle – PRG2 – SS 2012
25
Schlüssel ?
PLZ
ORT
STRASSE_NR
30419
Hannover
Schaumburgstr. 2
30419
Hannover
Quetlinburger Weg 12
37308
Schirmberg
Bergstraße 1
37308
Bodenrode
Hauptstraße 12
37308
Geismar
Bergstraße 1
35279
Neustadt
Gartenstraße 7
53577
Neustadt
Gartenstraße 7
keine Abhängigkeiten
Schlüssel: PLZ, ORT, STRASSE_NR
Dr. Karsten Tolle – PRG2 – SS 2012
26
Fahrzeuge der Firma AB
HERSTELLER
KENNZEICHEN
ZUGEL_GES_GEW
FÜHRERSCHEIN
VW
F-AB 123
bis 3.5t
B
MB
F-AB 234
bis 7.5t
C1
MB
F-AB 235
bis 7.5t
C1
MAN
F-AB 236
bis 12t
C
MB
F-AB 239
bis 12t
C
…
…
…
Abhängigkeiten?
Dr. Karsten Tolle – PRG2 – SS 2012
27
Fahrzeuge der Firma AB
HERSTELLER
KENNZEICHEN
ZUGEL_GES_GEW
FÜHRERSCHEIN
VW
F-AB 123
bis 3.5t
B
MB
F-AB 234
bis 7.5t
C1
MB
F-AB 235
bis 7.5t
C1
MAN
F-AB 236
bis 12t
C
MB
F-AB 239
bis 12t
C
MB
F-AB 230
bis 17t
C
…
…
…
…
… aus ZUGEL_GES_GEW folgt hier FÜHRERSCHEIN:
ZUGEL_GES_GEW FÜHRERSCHEIN
… umgekehrt nicht!
Dr. Karsten Tolle – PRG2 – SS 2012
28
Funktionale Abhängigkeit (FD)
Seien X und Y Teilmengen von R.
Eine Relation r(R) erfüllt (satisfies) die funktionale Abhängigkeit
(functional dependency) FD X → Y, wenn für je zwei (beliebige)
Tupel u, v ∈ r(R) gilt:
u(X) = v(X) ⇒ u(Y) = v(Y).
X → Y ⇔ u(X) = v(X) ⇒ u(Y) = v(Y)
Dr. Karsten Tolle – PRG2 – SS 2012
29
Beispiel
Gegeben ist die Relation r(R):
A
B
C
D
E
a1
b1
c1
d1
e1
a1
b2
c2
d2
e1
a2
b1
c3
d2
e1
a2
b1
c4
d3
e1
a3
b2
c5
d1
e1
Geben Sie an, welche der folgenden Abhängigkeiten r nicht widerspricht:
A→D
AB → D
C → BDE
E→A
A→E
A → BC
Dr. Karsten Tolle – PRG2 – SS 2012
30
Axiome von Armstrong
1. β ⊆ α ⇛ → (Reflexivität - trivial)
2.
→
⇛
3.
→
∧
(Erweiterungsregel)
→
→
Dr. Karsten Tolle – PRG2 – SS 2012
⇛
→
(Transitivität)
31
FDs?
Stadt
Kreis
Bundesland
Rüsselsheim
Groß-Gerau
Hessen
Mörfelden-Walldorf
Groß-Gerau
Hessen
Dietzenbach
Offenbach
Hessen
Dreieich
Offenbach
Hessen
Walldorf (Baden)
Rhein-Neckar-Kreis
Baden-Württemberg
Stadt Stadt
Kreis, Stadt Kreis
…
Stadt Kreis
Kreis Bundesland
Stadt Bundesland
Dr. Karsten Tolle – PRG2 – SS 2012
32
Schlüssel
Ein Schlüssel identifiziert eine Entität. Er besteht aus einer Menge von
Attributen, deren Werte alle Instanzen einer Entität eindeutig
bestimmen.
Ein Schlüssel (key) einer Relation r(R) ist eine minimale Teilmenge K
von R, so dass für je zwei verschiedene Tupel t1, t2 ∈ r gilt:
t1(K) ≠ t2(K)
und
keine echte Teilmenge K' von K hat diese Eigenschaft.
Ein Schlüssel kann als Integritätsbedingung angesehen werden. Falls K
Schlüssel von r(R), t1 ∈ r, t1(K) = t2(K), t1 ≠ t2 dann dürfte t2 nicht in r(R)
eingefügt werden.
Dr. Karsten Tolle – PRG2 – SS 2012
33
Schlüssel
Gegeben seien ein Relationenschema R und eine Menge F von
FDs.
X ⊆ R ist ein Oberschlüssel für R
⇔
X→R
X ist ein Schlüssel für R
⇔
X → R und X minimal (¬(∀A∈X: X\A → R))
Dr. Karsten Tolle – PRG2 – SS 2012
34
Wie finde ich alle Schlüssel wenn FDs
gegeben?
Gegeben Relationschema R und eine Menge F
von FDs
z.B. R = (A, B, C, D, E) und FD = {A B, CD AC}
Dr. Karsten Tolle – PRG2 – SS 2012
35
Wie finde ich alle Schlüssel wenn FDs
gegeben?
Gegeben Relationschema R und eine Menge F
von FDs
z.B. R = (A, B, C, D, E) und FD = {A B, CD AC}
Gibt es Attribute, die nicht auf der rechten Seite
der FDs vorkommen?
Falls ja, gehören diese zu jedem Schlüssel!
Hier: D und E
Dr. Karsten Tolle – PRG2 – SS 2012
36
Gegeben Relationschema R und eine Menge F
von FDs
z.B. R = (A, B, C, D, E) und FD = {A B, CD AC}
Gibt es Attribute, die nicht auf der rechten Seite
der FDs vorkommen?
Falls ja, gehören diese zu jedem Schlüssel!
Hier: D und E (kann aber auch ∅sein)
… ist dies bereits ein Schlüssel, sind wir fertig!
Dr. Karsten Tolle – PRG2 – SS 2012
37
Gegeben Relationschema R und eine Menge F
von FDs
z.B. R = (A, B, C, D, E) und FD = {A B, CD AC}
Gibt es Attribute, die nicht auf der rechten Seite
der FDs vorkommen?
Falls ja, gehören diese zu jedem Schlüssel!
Hier: D und E (kann aber auch ∅sein)
… ist dies kein Schlüssel
ausprobieren.
Dr. Karsten Tolle – PRG2 – SS 2012
kombinatorisch
38