AusweisNr Name Vorname

Daten – Bank
3. Vorlesung
Dr. Karsten Tolle – PRG2 – SS 2012
Bisher …
SQL:
•
•
•
•
create
insert
select
alter
GeoPosition
von
Person
AusweisNr.
(0,n)
Name Vorname
Dr. Karsten Tolle – PRG2 – SS 2012
lebt_in
bis
(0,n)
Ort
PLZ
Ortsname
2
ER-Modell gegeben
von
Person
AusweisNr.
(0,n)
Name Vorname
lebt_in
bis
(0,n)
Ort
PLZ
Ortsname
Frage 1: Wie daraus Tabellen (Relationen) bauen?
Frage 2: Welche Alternativen habe ich?
… später heute:
Wie baue ich diese Tabellen in SQL?
Dr. Karsten Tolle – PRG2 – SS 2012
3
ER-Abbildung zu Relationen
Entitätstypen
• Ein Entitätstyp wird zu einer Relation (Tabelle), dessen Relationenschema
aus allen Attributen des Entitätstyps besteht.
• Jedes Tupel der Tabelle entspricht dann genau einer Entität des
Entitätstyps.
• Etwaige Schlüssel werden übernommen und üblicherweise an den Anfang
des Relationenschemas gestellt.
„Regel“:
Schlüssel
Attribut_A
Attribut_B
Entitätstyp E
E (Schlüssel, Attribut_A, Attribut_B)
Dr. Karsten Tolle – PRG2 – SS 2012
4
Beispiel
Angestellter
PersNr
Name Vorname
ANGESTELLTER (PersNr, Name, Vorname)
ANGESTELLTER
Dr. Karsten Tolle – PRG2 – SS 2012
PersNr
Name
Vorname
001
Jon
Lucas
003
Jon
Smith
103
Lucas
Jon
5
Beziehungstyp
B1
B2
Schlüssel1
A_1
Schlüssel2
A_2
Entität_1
Beziehung
ENTITÄT_1 (Schlüssel1, A_1)
Entität_2
ENTITÄT_2 (Schlüssel2, A_2)
BEZIEHUNG (Schlüssel1, Schlüssel2, B1, B2)
Schlüssel für die Tabelle/Relation Beziehung?
Dr. Karsten Tolle – PRG2 – SS 2012
6
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. (aus ER!)
• 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
7
Schlüssel?
Raum
Tutorium
Name
TutorName
Raum
Wochentag
Startzeit
NM103
Sadik
NM117
Montag
08:15
NM117
Alina
NM117
Montag
12:15
NM123
Max
NM117
Donnerstag
14:15
SR9
Max
SR9
Donnerstag
12:15
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
8
Oberschlüssel
• K ist ein Oberschlüssel (super key) der
Relation, falls K einen Schlüssel enthält.
• … also aus Schlüssel
Oberschlüssel
(aber nicht umgekehrt)
Oberschlüssel
Schlüssel
Dr. Karsten Tolle – PRG2 – SS 2012
9
Wichtig!!!!!!!!!
• Eine Relation kann mehrere Schlüssel besitzen. Man spricht
dann auch von Schlüsselkandidaten.
• Im Allgemeinen wird ein Schlüssel als Primärschlüssel
ausgezeichnet. Dieser wird im Relationenschema durch
Unterstreichen gekennzeichnet.
Dr. Karsten Tolle – PRG2 – SS 2012
10
Beispiel
von
Person
AusweisNr
(0,n)
Name Vorname
PERSON
ORT
LEBT_IN
Dr. Karsten Tolle – PRG2 – SS 2012
lebt_in
bis
(0,n)
Ort
PLZ
Ortsname
(AusweisNr, Name, Vorname)
(PLZ, Ortsname)
(AusweisNr, PLZ, von, bis)
11
Beispiel mit Instanzen
PERSON
ORT
AusweisNr
Name
Vorname
PLZ
Ortsname
001
Jon
Lucas
501
Buli
003
Jon
Smith
503
Wali
103
Lucas
Jon
603
Kali
LEBT_IN
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
Jon Lucas (001) lebt(e)_in Buli (501), vom 23.12.2000 bis zum 25.12.2010!
Dr. Karsten Tolle – PRG2 – SS 2012
12
PERSON
ORT
AusweisNr
Name
Vorname
PLZ
Ortsname
001
Jon
Lucas
501
Buli
003
Jon
Smith
503
Wali
103
Lucas
Jon
603
Kali
LEBT_IN
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Jon Lucas (001) lebt(e)_in Buli (501), vom 23.12.2000 bis zum 25.12.2010!
Jon Lucas (001) lebt(e)_in Buli (501), vom 01.01.2012 bis heute!
Dr. Karsten Tolle – PRG2 – SS 2012
Attribut „von“ der Relation
muss auch Teil des Schlüssels
sein!
13
(0,n) und (0,n)
B1
B2
Schlüssel1
A_1
Entität_1
Schlüssel2
A_2
(0,n)
Beziehung
ENTITÄT_1 (Schlüssel1, A_1)
(0,n)
Entität_2
ENTITÄT_2 (Schlüssel2, A_2)
BEZIEHUNG (Schlüssel1, Schlüssel2, B1, B2)
Dr. Karsten Tolle – PRG2 – SS 2012
14
(0,1) und (0,n)
B
Schlüssel1
A_1
Entität_1
Schlüssel2
A_2
(0,1)
Beziehung
(0,n)
Entität_2
ENTITÄT_1 (Schlüssel1, A_1)
ENTITÄT_2 (Schlüssel2, A_2)
BEZIEHUNG (Schlüssel1, Schlüssel2, B)
Dr. Karsten Tolle – PRG2 – SS 2012
15
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
16
Beispiel mit Instanzen
BUCH
ENTLEIHER
BuchNr
Titel
Autor
Nummer
Name
001
Vom Winde
Lucas
501
Bully
003
Per Anhalter
Smith
503
Wally
103
Vom Winde
Lucas
603
Kally
VERLIEHEN_AN
BuchNr
Nummer
Datum
001
501
23.12.2011
103
603
17.08.2011
003
503
01.01.1999
Das Buch (001) wurde verliehen an Bully am 23.12.2011.
Dr. Karsten Tolle – PRG2 – SS 2012
17
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
18
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
19
(1,1)
B
Schlüssel1
A_1
Schlüssel2
A_2
(1,1)
Entität_1
ENTITÄT_1
ENTITÄT_2
Beziehung
(0,n)
Entität_2
(Schlüssel1, A_1, Schlüssel2, B)
(Schlüssel2, A_2)
Hier sind nur noch zwei Relationen notwendig!
Dr. Karsten Tolle – PRG2 – SS 2012
20
Beispiel
Datum
Person
AusweisNr.
(1,1)
Name Vorname
geboren
in
(0,n)
Ort
PLZ
Ortsname
PERSON (AusweisNr., Name, Vorname, PLZ, Datum)
ORT
(PLZ, Ortsname)
Dr. Karsten Tolle – PRG2 – SS 2012
21
… aber auch möglich! (geht immer☺)
B
Schlüssel1
A_1
Entität_1
Schlüssel2
A_2
(1,1)
Beziehung
(0,n)
Entität_2
ENTITÄT_1(Schlüssel1, A_1)
ENTITÄT_2 (Schlüssel2, A_2)
Datum
BEZIEHUNG (Schlüssel1, Schlüssel2, B)
Person
AusweisNr.
(1,1)
Name Vorname
geboren
in
(0,n)
Ort
PLZ
Ortsname
PERSON (AusweisNr., Name, Vorname)
ORT (PLZ, Ortsname)
GEBOREN_IN (AusweisNr., PLZ, Datum)
Dr. Karsten Tolle – PRG2 – SS 2012
22
… Unterschied?
Datum
Person
AusweisNr.
(1,1)
Name Vorname
PERSON (AusweisNr., Name, Vorname, PLZ, Datum)
ORT (PLZ, Ortsname)
Dr. Karsten Tolle – PRG2 – SS 2012
geboren
in
(0,n)
Ort
PLZ
Ortsname
PERSON (AusweisNr., Name, Vorname)
ORT (PLZ, Ortsname)
GEBOREN_IN (AusweisNr., PLZ, Datum)
23
(1,1) und (0,1)
Sind beide min-Kardinalitäten = 0, so muss das allgemeine Verfahren ((o,n)
und (0,n)) angewendet werden.
Ist nur eine min-Kardinalität = 1, so wendet man die Abbildung der one-tomany bzw. many-to-one Beziehung an.
B
Schlüssel1
A_1
Entität_1
ENTITÄT_1
ENTITÄT_2
Dr. Karsten Tolle – PRG2 – SS 2012
Schlüssel2
A_2
(1,1)
Beziehung
(0,1)
Entität_2
(Schlüssel1, A_1, Schlüssel2, B)
(Schlüssel2, A_2)
24
Beispiel
seit
Abteilung
AbteilungsNr.
(1,1)
Bezeichnung
geleitet
von
(0,1)
Mitarbeiter
Pers.Nr.
Name
ABTEILUNG (AbteilungsNr., Bezeichnung, Pers.Nr., seit)
MITARBEITER (Pers.Nr., Name)
Dr. Karsten Tolle – PRG2 – SS 2012
25
(1,1) und (1,1)
B
Schlüssel1
A_1
Entität_1
Schlüssel2
A_2
(1,1)
Beziehung
(1,1)
Entität_2
ENTITÄT_1_2 (Schlüssel1, A_1, Schlüssel2, A_2, B)
oder
ENTITÄT_1_2 (Schlüssel1, A_1, Schlüssel2, A_2, B)
Nur noch eine Relation notwendig!
Dr. Karsten Tolle – PRG2 – SS 2012
26
Beispiel
AblaufDatum
Ausweis
AusweisNr. Behörde
(1,1)
gehört
(1,1)
Person
ID
Nachname
PERSON (AusweisNr., Behörde, Ablaufdatum, ID, Nachname)
Dr. Karsten Tolle – PRG2 – SS 2012
27
Sonderfälle
(3,5)
Auto
KFZ-Kennzeichen
Hersteller
hat_Räder
(0,1)
Rad
Fabr.-Nr.
Breite
(Hier sind RAD1 – RAD3 verbindlich, also NOT NULL, während
RAD4 und RAD5 durchaus Nullwerte beinhalten dürfen.)
AUTO (KFZ-Kennzeichen, Hersteller, RAD1, ... RAD5)
RAD (Fabr.-Nr., Breite)
Dr. Karsten Tolle – PRG2 – SS 2012
28
Überführung in SQL
von
Person
AusweisNr.
(0,n)
Name Vorname
PERSON
ORT
LEBT_IN
Dr. Karsten Tolle – PRG2 – SS 2012
lebt_in
bis
(0,n)
Ort
PLZ
Ortsname
(AusweisNr., Name, Vorname)
(PLZ, Ortsname)
(AusweisNr., PLZ, von, bis)
29
PERSON
ORT
AusweisNr
Name
Vorname
PLZ
Ortsname
001
Jon
Lucas
501
Buli
003
Jon
Smith
503
Wali
103
Lucas
Jon
603
Kali
LEBT_IN
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Fragen:
1. Welche Datentypen für die Attribute?
2. Welche Attribute dürfen Null-Values enthalten?
3. Wie erzeuge ich die referenzielle Integrität?
Dr. Karsten Tolle – PRG2 – SS 2012
30
LEBT_IN
ORT
PLZ
Ortsname
501
Buli
503
Wali
603
Kali
z.B. varchar(40)
oder besser
varchar(45)? …
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Ist auch „01“ ein gültiger Wert?
Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch ist ein
walisischer Ortsname in Irland mit 58 Zeichen.
Dr. Karsten Tolle – PRG2 – SS 2012
31
LEBT_IN
ORT
PLZ
Ortsname
501
Buli
503
Wali
603
Kali
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Werte hier sollen eine Referenz
auf Objekte darstellen. Sie sollten
enthalten sein in der Tabelle ORT!
Solche Verweise auf Schlüssel anderer Tabellen nennt man „Fremdschlüssel“!
Dr. Karsten Tolle – PRG2 – SS 2012
32
LEBT_IN
ORT
PLZ
Ortsname
501
Buli
503
Wali
603
Kali
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
CREATE TABLE lebt_in (
AusweisNr INT NOT NULL ,
PLZ INT NOT NULL ,
von DATE NOT NULL ,
bis DATE NULL ,
PRIMARY KEY (AusweisNr, PLZ, von) ,
FOREIGN KEY (PLZ) REFERENCES ort (PLZ),
FOREIGN KEY (AusweisNr) REFERENCES person (AusweisNr));
Dr. Karsten Tolle – PRG2 – SS 2012
33
Definition
Referenzielle Integrität bedeutet die Konsistenz
zwischen verbundenen Tabellen. Referenzielle
Integrität wird durch die Kombination von
Primärschlüssel und Fremdschlüssel erzwungen.
Um die referenzielle Integrität zu erhalten darf
jedes Feld einer Tabelle, das als Fremdschlüssel
deklariert worden ist, nur Werte des
entsprechenden Primärschlüssels der „Eltern
Tabelle“ annehmen.
Dr. Karsten Tolle – PRG2 – SS 2012
34
Abbildung der Generalisierung
Schlüssel
Attribut_A
Attribut_B
Oberklasse
Subklasse_1
Attribut_A_1
Attribut_B_1
Subklasse_2
Attribut_A_2
Es gibt unterschiedliche Möglichkeiten
dies ins rel. Modell abzubilden.
nicht klausurrelevant
Dr. Karsten Tolle – PRG2 – SS 2012
35
Generalisierung ER
rel. Modell
Möglichkeit 1:
KONTO (Kto.Nr., Kunde, Kto.Stand)
GIROKONTO (Kto.Nr., Kunde, Kto.Stand, Kreditrahmen)
SPARKONTO (Kto.Nr., Kunde, Kto.Stand, Zinssatz)
Verknüpfung zwischen
Kto.Nr. über Fremdschlüssel!
Kto.-Nr.
Kunde
Kto.Stand
Möglichkeit 2:
KONTO (Kto.Nr., Kunde, Kto.Stand)
GIROKONTO (Kto.Nr., Kreditrahmen)
SPARKONTO (Kto.Nr., Zinssatz)
Konto
Girokonto
Kreditrahmen
Sparkonto
Zinssatz
Möglichkeit 3:
KONTO (Kto.Nr., Kunde, Kto.Stand, Kreditrahmen, Zinssatz)
Dr. Karsten Tolle – PRG2 – SS 2012
nicht klausurrelevant☺
☺
36
Mitarbeiter
(0,n)
Woche
Jahr
(1,1)
gehört
Dienstplan
(0,n)
(0,n)
Mitarbeiter
SteuerungsAbt
legt_fest
A
B
Facharzt
(1,1)
zugeordnet
(0,n)
Abteilung
(1,1)
(0,n)
leitet
Abteilungsleiter
(2,n)
hat
(0,n)
StvAbtleiter
(1,n)
ausgebildet
in
Note
(0,n)
Nr
Fachausrichtung
Patient
C
Nachname
(1,n)
auf
nehmen
Aufnahme
(0,n)
Krankenhaus
Entlassung
SQL
• select * from ort o where o.PLZ = 501; - - Verwendung eines Alias
• select ortsname as Name from ort; -- Umbenennung der Ausgabe
Dr. Karsten Tolle – PRG2 – SS 2012
38
Wieder verbinden in Anfragen?
PERSON
ORT
AusweisNr
Name
Vorname
PLZ
Ortsname
001
Jon
Lucas
501
Buli
003
Jon
Smith
503
Wali
103
Lucas
Jon
603
Kali
LEBT_IN
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Welche Personen leben aktuell in Buli?
Dr. Karsten Tolle – PRG2 – SS 2012
select
A1,A2, ... ,An
from
R1, R2, ... ,Rm
[where
conditions]
[group by clause]
[having
clause]
39
[order by clause];
Wieder verbinden in Anfragen?
PERSON
ORT
AusweisNr
Name
Vorname
PLZ
Ortsname
001
Jon
Lucas
501
Buli
003
Jon
Smith
503
Wali
103
Lucas
Jon
603
Kali
LEBT_IN
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Welche Personen leben aktuell in Buli?
SELECT p.* FROM lebt_in l, person p, ort o
where
o.Ortsname = 'Buli' and
o.plz = l.plz and
p.ausweisnr = l.ausweisnr and
l.bis is null;
Dr. Karsten Tolle – PRG2 – SS 2012
40
Wieder verbinden in Anfragen?
PERSON
ORT
AusweisNr
Name
Vorname
PLZ
Ortsname
001
Jon
Lucas
501
Buli
003
Jon
Smith
503
Wali
103
Lucas
Jon
603
Kali
LEBT_IN
AusweisNr
PLZ
von
bis
001
501
23.12.2000
25.12.2010
003
501
17.08.2004
Null
001
503
01.01.1999
01.01.2012
001
501
01.01.2012
Null
Welche Personen leben aktuell in Buli?
SELECT p.* FROM lebt_in l
join person p on (l.ausweisnr=p.ausweisnr)
join ort o on (o.plz=l.plz)
where
o.Ortsname = 'Buli' and l.bis is null;
Dr. Karsten Tolle – PRG2 – SS 2012
41
Zusammenfassung
• Übersetzung ER-Modell ins Relationenmodell
(nicht eindeutig!)
• Referenzielle Integrität mit Fremdschlüssel
• Joins mit SQL
Dr. Karsten Tolle – PRG2 – SS 2012
42
Wichtiges
• Bachelor Informatik-Studenten:
Onlineanmeldung zu Klausur B-PRG2 im LFS ist
freigeschaltet.
• Zusätzlich zur Klausur anmelden unter:
http://www.dbis.informatik.uni-frankfurt.de/anmeldung/prg2/klausur/
Achtung: Dies ersetzt nicht eine Anmeldung
beim zuständigen Prüfungsamt, wenn dies
nötig ist!!!!
Dr. Karsten Tolle – PRG2 – SS 2012
43
Diesen Freitag!
Auch mit Public Viewing des EM-Spiels!