Lager Lageradresse

Daten – Bank
5. Vorlesung
Dr. Karsten Tolle – PRG2 – SS 2013
4. Vorlesung
• 1. Normalform
• Funktionale Abhängigkeiten und
Schlüsselkandidaten
Dr. Karsten Tolle – PRG2 – SS 2013
2
vorrat
(Teil
Lager
Menge
Lageradresse)
101
1
25
Waag. 10
102
3
410
Krugerstr. 42
102
1
F = { Teil Lager → Menge Lageradresse
Lager → Lageradresse
112 }
4
300
Waag. 10
10
Brunnerstr. 105
F = { Teil Lager → Menge Lageradresse
Lager → Lageradresse }
Dr. Karsten Tolle – PRG2 – SS 2013
3
vorrat
F = { Teil Lager → Menge Lageradresse
Lager → Lageradresse }
(Teil
Lager
Menge
Lageradresse)
101
1
25
Waag. 10
102
3
410
Krugerstr. 42
102
1
300
Waag. 10
112
4
10
Brunnerstr. 105
1
Probleme:
Redundanz
Einfüge-Anomalie
Lösch-Anomalie
– Lageradresse für jedes Teil im Lager
redundant gespeichert
– Lager ohne Teile kann nicht eingefügt werden
– Lager ohne Teile würden gelöscht werden
Dr. Karsten Tolle – PRG2 – SS 2013
4
vorrat
(Teil
Lager Menge Lageradresse)
101
1
25
Waag. 10
102
3
410
Krugerstr. 42
102
1
300
Waag. 10
112
4
10
Brunnerstr. 105
F = { Teil Lager → Menge Lageradresse
Lager → Lageradresse }
Fremdschlüssel
Lösung: Zerlegung der Relation
vorrat (Teil Lager Menge)
lager (Lager Lageradresse)
101
1
25
1
Waag. 10
102
3
410
2
RMS 10
102
1
300
3
Krugerstr. 42
112
4
10
4
Brunnerstr. 105
Dr. Karsten Tolle – PRG2 – SS 2013
5
Prim
Ein Attribut A heißt prim in R, wenn es in einem
Schlüssel von R enthalten ist, sonst heiße es
nicht prim.
A ist prim
⇔
X: X ist Schlüssel, A∈X
Dr. Karsten Tolle – PRG2 – SS 2013
6
volle funktionale Abhängigkeit
Eine funktionale Abhängigkeit X → Y heißt volle
funktionale Abhängigkeit, wenn für keine
Teilmenge X´ ⊂ X, X´ → Y gilt. Y heißt dann voll
funktional abhängig von X.
X → Y voll funktional
⇔
∃ X´⊂ X : X´ → Y
Dr. Karsten Tolle – PRG2 – SS 2013
7
2. Normalform
Ein Relationenschema R ist in 2. Normalform
(2NF), wenn es in 1NF ist und jedes nicht prime
Attribut voll funktional von jedem Schlüssel von
R abhängig ist.
2. NF
⇔
A nicht prim ⇒ Schlüssel → A voll funktional
Die 2. NF ist verletzt, wenn ein Teil eines Schlüssels
ein Nicht-Schlüsselattribut funktional bestimmt.
Dr. Karsten Tolle – PRG2 – SS 2013
8
Beispiel 1
vorrat
(Teil
Lager
Menge Lageradresse)
101
1
25
Waag. 10
102
3
410
Krugerstr. 42
102
1
300
Waag. 10
112
4
10
Brunnerstr. 105
F = { Teil Lager → Menge Lageradresse
Lager → Lageradresse }
vorrat
(Teil
Lager Menge
101
1
25
1
Waag. 10
102
3
410
3
Krugerstr. 42
102
1
300
4
Brunnerstr. 105
112
4
10
Dr. Karsten Tolle – PRG2 – SS 2013
lager
(Lager Lageradresse)
9
Beispiel 2
R = ABCD F = {AB→C, B→D}
B→D verletzt 2. NF
Zerlegung in:
R1 = (ABC) mit F1 = {AB→C}
R2 = (BD) mit F2 = {B→D}
Dr. Karsten Tolle – PRG2 – SS 2013
10
Übung
R = ABCDE
F = {AB→C, B→D, D→E}
B→D verletzt 2. NF
Zerlegung in:
R1 = (ABCE) und
R2 = (BD)
… dann geht D
Zerlegung in:
R1 = (ABC) und
R2 = (BD) und
R3 = (DE)
E verloren!
Dr. Karsten Tolle – PRG2 – SS 2013
11
Vorgehen beim Prüfen auf NF
(WICHTIG!!!)
Ausgangspunkt: Relation R (mind. 1. NF) und
Menge der FDs gegeben.
1. Welche Schlüssel gibt es? was sind die
nicht primen Attribute!
2. Prüfe auf 2. NF (voll funktional abhängig von
ALLEN Schlüsseln?)
Dr. Karsten Tolle – PRG2 – SS 2013
12
Fahrzeug
ID
Hersteller
Zugel_Ges_Gew
Führerschein
ID
HERSTELLER
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
bis 3.5
B
2
MB
bis 7.5
C1
3
MB
bis 8.5
C
4
MAN
bis 12
C
5
MB
bis 12
C
6
VW
bis 12
C
…
…
…
F = { ID R,
ZUGEL_GES_GEW
Dr. Karsten Tolle – PRG2 – SS 2013
2. NF?
FÜHRERSCHEIN}
13
Trotzdem Probleme !
ID
HERSTELLER
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
bis 3.5
B
2
MB
bis 7.5
C1
3
MB
bis 8.5
C
4
MAN
bis 12
C
5
MB
bis 12
C
6
VW
bis 12
C
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2013
14
Normalisierung …
ID
HERSTELLER
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
bis 3.5
B
2
MB
bis 7.5
C1
3
MB
bis 8.5
C
4
MAN
bis 12
C
5
MB
bis 12
C
6
VW
bis 12
C
…
…
…
F = { ID R,
ZUGEL_GES_GEW
FÜHRERSCHEIN}
ID
HERSTELLER
ZUGEL_GES_GEW
ZUGEL_GES_GEW
FÜHRERSCHEIN
1
VW
bis 3.5
bis 3.5
B
2
MB
bis 7.5
bis 7.5
C1
3
MB
bis 8.5
bis 8.5
C
4
MAN
bis 12
bis 12
C
5
MB
bis 12
bis 12
C
6
VW
bis 12
bis 12
C
…
…
…
…
Dr. Karsten Tolle – PRG2 – SS 2013
15
Normalformen
Über die Normalformen (NF) werden Kriterien
definiert, um Redundanzen und Anomalien zu
verhindern.
Es gibt: 1 NF 2 NF 3 NF BCNF
4 NF 5 NF 6 NF
1 NF
2 NF
3 NF
Dr. Karsten Tolle – PRG2 – SS 2013
…
16
Normalisierung / Denormalisierung
• Normalisierung: Der Prozess des Aufspaltens
von Relationen, um Anomalien/Redundanzen
zu verhindern.
• Denormalisierung: Der Prozess des
Zusammenlegens von Relationen, um
Performanz zu gewinnen.
Dr. Karsten Tolle – PRG2 – SS 2013
17
seit
Person
AusweisNr.
(u:v)
lebt_in
(w:x)
PLZ
Name Vorname
Abhängig von u, v, w und x
Ort
PERSON
Ortsname
(AusweisNr., Name, Vorname)
ORT (PLZ, Ortsname)
PERSON
ORT
LEBT_IN
(AusweisNr., Name, Vorname)
(PLZ, Ortsname)
(AusweisNr., PLZ, seit)
LEBT_IN
PERSON
(AusweisNr., PLZ, seit)
(AusweisNr., Name, Vorname)
ORT (PLZ, Ortsname)
PERSON
(AusweisNr., Name, Vorname)
LEBT_IN
(AusweisNr., PLZ, seit)
ORT (PLZ, Ortsname, AusweisNr., seit)
PERSON
(AusweisNr., Name, Vorname, PLZ, seit)
ORT (PLZ, Ortsname)
…
(3:5)
Auto
KFZ-Kennzeichen
Hersteller
hat_Räder
(0:1)
Rad
Fabr.-Nr.
PERSON_ORT (AusweisNr., Name, Vorname, PLZ, Ortsname, seit)
PERSON_ORT (AusweisNr., Name, Vorname, PLZ, Ortsname, seit)
Dr. Karsten Tolle – PRG2 – SS 2013
18
Breite
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)
Kto.-Nr.
Kunde
Möglichkeit 2:
KONTO (Kto.Nr., Kunde, Kto.Stand)
GIROKONTO (Kto.Nr., Kreditrahmen)
SPARKONTO (Kto.Nr., Zinssatz)
Kto.Stand
Konto
Girokonto
Kreditrahmen
Sparkonto
Zinssatz
Möglichkeit 3:
KONTO (Kto.Nr., Kunde, Kto.Stand, Kreditrahmen, Zinssatz)
Dr. Karsten Tolle – PRG2 – SS 2013
19
rel. Modell
ER ????
Gegeben
R (A, B, C)
S (D, E)
T (B, D, F)
U (A, G)
…
Was ist Entity-Typ, was Beziehungstyp?
Bilden S und U zusammen ein Entity-Typ?
Sind R.B und T.B überhaupt GLEICH?
R und U könnten eine Generalisierung darstellen…
????
Dr. Karsten Tolle – PRG2 – SS 2013
20
Tabellen Ansicht ≠ ER-Modell
Dr. Karsten Tolle – PRG2 – SS 2013
21
OO (ER) und rel. Modell
• Als „Object-relational Impedance Mismatch“
(auch objekt-relationale Unverträglichkeit
genannt) bezeichnet man die Unverträglichkeit
zwischen dem relationalem Datenmodell und
dem objektorientieren Programmierparadigma.
• Unverträglichkeit bedeutet, dass die Abbildungen
von dem einen ins andere Modell (und zurück)
nicht eindeutig ohne Zusatzwissen möglich ist.
Dr. Karsten Tolle – PRG2 – SS 2013
22
(Fast) Alle Wege führen nach Rom ….
Prüfe die Anforderungen und denke an die Zukunft!
Dr. Karsten Tolle – PRG2 – SS 2013
23
Prinzipien des digitalen Speicherns I
Ein Datensatz sollte offensichtlich/eindeutig und
schnell mit dem Objekt, welches es repräsentiert,
in Verbindung gebracht werden können.
ISBN
TITLE
3-12-517154-7
…
ISBN Number
Dr. Karsten Tolle – PRG2 – SS 2013
24
Prinzipien des digitalen Speicherns II
Atomisierung der Daten – teile die Daten in ihre
kleinste Einheit auf 1. Normalform
ISBN
AUTHORS
0-13-221211-0
Jeffrey Hoffer, Mary
Prescott and Fred
McFadden
TITLE
…
…
ISBN
TITLE
0-13-221211-0
…
Dr. Karsten Tolle – PRG2 – SS 2013
…
ISBN
AUTHORS
0-13-221211-0
Jeffrey Hoffer
0-13-221211-0
Mary Prescott
0-13-221211-0
Fred McFadden
25
Prinzipien des digitalen Speicherns III
… ein weiteres Beispiel (aus der Realität).
…
RULER
…
…
Hadrian (Sabina)
…
Bedeutet: Hadrian war der Prägeherr und
prägte die Münze für (zu ehren von) Sabina.
…
RULER
COINED_FOR
Hadrian
Sabina
…
…
Dr. Karsten Tolle – PRG2 – SS 2013
26
Prinzipien des digitalen Speicherns IV
Handhabung unsicherer Informationen:
…
RULER
…
…
Hadrian?
…
?Nero
Nero
Bedeutung: … nicht wirklich sicher / abgeleitet
aus anderen Informationen.
…
…
Dr. Karsten Tolle – PRG2 – SS 2013
RULER
QUALIFICATION
Hadrian
uncertain
Nero
inferred
Nero
certain
…
27
Prinzipien des digitalen Speicherns V
Konsistenz in der Namensgebung für Tabellen und
Attribute.
coinlist
metal_information
ID
…
CODE
…
besser
coin_list
metal_information
ID
…
Dr. Karsten Tolle – PRG2 – SS 2013
ID
…
28
Prinzipien des digitalen Speicherns VI
Ausnutzung der referenziellen Integrität wo immer
möglich!
Dr. Karsten Tolle – PRG2 – SS 2013
29
Sicherheit
Jahr für Jahr kosten Datenpannen Firmen mehr Geld. Quelle: Symantec
Nutzung von Views
Mitarbeiter
Empno
Vorname
Nachname
Gehalt
1
Ines
Müller
75000
2
Fred
Wagner
60000
3
Ingo
Meyer
72000
4
Max
Müller
26000
Jemand der die Vor- und Nachnamen der
Mitarbeiter benötigt, würde z.B. die SelectRechte an der Tabelle „Mitarbeiter“ erhalten
und so auch die Gehälter erfahren
Dr. Karsten Tolle – PRG2 – SS 2013
31
Nutzung von Views
Mitarbeiter
Empno
Vorname
Nachname
Gehalt
1
Ines
Müller
75000
2
Fred
Wagner
60000
3
Ingo
Meyer
72000
4
Max
Müller
26000
CREATE VIEW Mitarbeiter_Namen AS
select Vorname, Nachname from Mitarbeiter;
Vorname
Nachname
Ines
Müller
Fred
Wagner
Ingo
Meyer
Max
Müller
Dr. Karsten Tolle – PRG2 – SS 2013
32
Mitarbeiter
Abteilung
Empno
Vorname
Nachname
Gehalt
Depno
Name
Head
1
Ines
Müller
75000
1
Finanzen
2
2
Fred
Wagner
60000
2
Einkauf
1
3
Ingo
Meyer
72000
3
HR
1
4
Max
Müller
26000
CREATE VIEW Mitarbeiter_Dept AS
select m.Empno, m.Nachname, d.Name as Abt
from Mitarbeiter m, Abteilung d, Arbeitet_in a
where m.Empno = a.Empno
and a.Depno = d.Depno;
…
Arbeitet_in
Empno
Depno
1
2
1
3
2
1
3
3
4
2
…
Mitarbeiter_Dept
Empno
Nachname
Abt
1
Müller
Einkauf
1
Müller
HR
2
Wagner
Finanzen
3
Meyer
HR
4 – SS 2013
Müller
Dr. Karsten Tolle – PRG2
Mitarbeiter_Dept
Einkauf
33
Datenlecks zum Stöbern …
• http://www.projekt-datenschutz.de/
• http://opalkatze.wordpress.com/to-becontinue/datenpannen/datenpannen-2013/
• http://www.datenleck.net/
Dr. Karsten Tolle – PRG2 – SS 2013
34
Benutzer und Rechte
• CREATE USER test IDENTIFIED BY PASSWORD
‘li720lhjLJ2' – erzeugt einen neuen Benutzer
• GRANT select ON star_trek.* TO test – weist dem
Benutzer test die select Rechte für alle Tabellen
der DB star_trek zu
• REVOKE select ON star_trek.* from test – nimmt
ihm die Rechte wieder
Siehe auch: http://dev.mysql.com/doc/refman/5.1/de/grant.html
Dr. Karsten Tolle – PRG2 – SS 2013
35
Grant und Revoke unter Workbench
Dr. Karsten Tolle – PRG2 – SS 2013
36
Absichern am Bsp. MySQL
• keine Standard Benutzer – z.B. „root“
mysql> RENAME USER root TO new_user;
• „show databases“ nicht unterstützen
• Netzwerkzugang nur erlauben, wenn nötig
• Firewall installieren
• …
Siehe auch: http://dev.mysql.com/doc/refman/5.6/en/general-security-issues.html
Dr. Karsten Tolle – PRG2 – SS 2013
37
SQL Injection
… (böswilliges) Einschleusen oder
Verändern von SQL-Befehlen an die
Datenbank durch den Benutzer.
Web Related Vulnerabilities 2011
(commercial applications)
Dr. Karsten
TolleTrends
– PRG2Report
– SS 2013
Application
Security
– März 2012 - CENZIC
39
Mögliche Gefahren
Zusätzliche Bedingungen einzufügen:
Eingabe:
1 or id > 1 or name like ‘%‘
select * from mytable where id = 1 or id > 1 or name like ‘%‘??
Daten der Tabelle können ausgespäht werden.
Dr. Karsten Tolle – PRG2 – SS 2013
40
Mögliche Gefahren
Zusätzliche Statements einzufügen:
Eingabe:
1; drop mytable #
select * from mytable where id = 1; drop mytable # ??
Direkter Schaden an der Datenbank, eventuell Ausfall
der Applikation.
Dr. Karsten Tolle – PRG2 – SS 2013
41
Was möglich ist hängt ab von …
• DBMS
– Erlaubt es mehrere SQL-Statements gleichzeitig
auszuführen?
– Welche Zeichen werden als Kommentare
interpretiert? (z.B.: -- oder #)
• Verbindungsart / Programmiersprache
– JDBC, ODBC, … ???
Dr. Karsten Tolle – PRG2 – SS 2013
42
Gegenmaßnahmen
• DB-Verbindung nur mit den Rechten, die nötig sind
aufbauen! (siehe GRANT und REVOKE)
• Nutzung von Prepared Statements (in Java)
• Nutzung von statischem SQL, z.B. SQLJ
• Möglichst wenig Informationen über die Datenbank
nach außen geben! … z.B. bei Fehlermeldungen
• Eingaben auf Sonderzeichen und ungültige Werte
prüfen (möglichst zurückweisen!)
• Anwendung diesbezüglich testen (testen lassen)
Dr. Karsten Tolle – PRG2 – SS 2013
43
Gegenmaßnahmen PHP
• Positivlisten Methode – wurde eingegeben was man
erwartet?
• Negativlisten Methode – Ablehnung von
Schlüsselwörtern
• Gegebene Funktion in PHP:
mysql_real_escape_string()
Sie Maskiert alle Zeichen mit denen man aus den
Anführungszeichen ausbrechen kann.
• Kombination aller 3 ist die sicherste Variante.
Siehe auch:
– http://www.inside-php.de/tutorial/PHP-Sicherheit-13/SQL-Injections-vermeiden.html
– http://php.net/manual/en/security.database.sql-injection.php
Dr. Karsten Tolle – PRG2 – SS 2013
44
Literaturhinweise
• Handling Java Web Application Input, Part 1
von Stephen Enright
– http://today.java.net/pub/a/today/2005/09/08/handling-java-web-app-input.html
• Advanced SQL Injection In SQL Server
Applications von Chris Anley
– http://www.cgisecurity.com/lib/advanced_sql_injection.pdf
– (more)
http://www.encription.co.uk/downloads/more_advanced_sql_injection.pdf
Dr. Karsten Tolle – PRG2 – SS 2013
45