Garten - Daten – Was ist das? Wer braucht das?prg2/SS2019/folien/teil2/4_db_2019.pdf ·...

Daten – Bank

4. Vorlesung

Dr. Karsten Tolle – PRG2 – SS 2019 2

Bisher …

SQL:

• create

• insert

• select

(0,n) (0,n) Person

AusweisNr. Name Vorname

lebt_in

von bis

Ort

PLZ Ortsname


Heute …

SQL:

• create

• insert

• select

(0,n) (0,n) Person

AusweisNr. Name Vorname

lebt_in

von bis

Ort

PLZ Ortsname

• 1. Normalform

• Schlüssel und Funktionale Abhängigkeiten

DatenaustauschformateViews


CSV (Comma Separated Values)

CSV-Dateien dienen zum Speichern/Austausch einer Tabelle

id,vorname,nachname,telefon1,Max,Mustermann,938479283472,Peter,Mustermann,92378409238473,Miss,Mustermann,9832488

Daten(austausch)formate


Workbench oder „SELECT ... INTO OUTFILE”

eXtensible Markup Language (XML) – a simple example

<?xml version="1.0" ?>

<contact>

<address type="business">

<name>Tolle</name>

<firstname>Karsten</firstname>

<street>Robert-Mayer-Str.</street>

<town>Frankfurt</town>

</address>

</contact>

… erste W3C Recommendation von 1998!


XML Syntax and Grammar

[1] document ::= prolog element Misc*

...

[3] S ::= (#x20 | #x9 | #xD | #xA)+ | space, carriage returns, line feeds, or tabs

[4] NameChar ::= Letter | Digit | '.' | '-' | '_' | ':' | CombiningChar | Extender

[5] Name ::= (Letter | '_' | ':') (NameChar)*

[6] Names ::= Name (#x20 Name)*

[7] Nmtoken ::= (NameChar)+

[8] Nmtokens ::= Nmtoken (#x20 Nmtoken)*

...

[89] Extender ::= #x00B7 | #x02D0 | #x02D1 | #x0387 | #x0640 | #x0E46

| #x0EC6 | #x3005 | [#x3031-#x3035] | [#x309D-#x309E] | [#x30FC-#x30FE]

Siehe auch: https://www.w3.org/TR/xml/

https://www.w3.org/TR/xml/


JavaScript Object Notation (JSON)

{"contact": {"address": {"-type": "business","name": "Tolle","firstname": "Karsten","street": "Robert-Mayer-Str.","town": "Frankfurt"

}}

}


<contact>


<name>Tolle</name>




</address>

</contact>

http://www.utilities-online.info/xmltojson/

… erstmals 2005 vorgestellt!

eXtensible Markup Language (XML) – example multiple phones


<contact>


<name>Tolle</name>




<phone>98237498273</phone>

<phone>098234</phone>

<phone>09802347</phone>

</address>

</contact>

https://www.wikidata.org/wiki/Wikidata:Database_download

JSON dumpsRDF dumpsXML dumps

https://www.wikidata.org/wiki/Wikidata:Database_download

Sample XML Document in Japanese


Resource Description Framework (RDF)

• Ausgangspunkt: Menschliche Sprache – Subjekt, Prädikat, Objekt

Objekt (Literal)Subjekt

Prädikat

Subjekt

Prädikat

Objekt (mit URI)


Resource Description Framework (RDF)

• Basiert auf einen gerichteten, benannten Graphen

• Knoten, welche durch einen Uniform ResourceIdentifier (URI) identifiziert werden, können als Subjekt oder Objekt verwendet werden

• Knoten, welche keine URI besitzen (Literals) können nur als Objekte verwendet werden

• Prädikate (Properties) werden ebenfalls durch URIs identifiziert

Tripeldarstellung:

[http://publikationen.ub.uni-frankfurt.de/frontdoor/index/index/docId/2492 http://www.my.de/vok#creator „Karsten Tolle“ ]

Graf:

RDF/XML:<?xml version="1.0"?>

<rdf:RDF

xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#"

xmlns:rdfs="http://www.w3.org/2000/01/rdf-schema#"

xmlns:my="http://www.myns.de/vok#" >

<rdf:Description rdf:about="http://publikationen.ub.uni-frankfurt.de/frontdoor/index/index/docId/2492">

<my:creator>Karsten Tolle</my:creator>

</rdf:Description>

</rdf:RDF>

Karsten Tollehttp://publikationen.ub.uni-frankfurt.de/frontdoor/index/index/docId/2492

my:creator

Tripeldarstellung:

[ http://publikationen.ub.uni-frankfurt.de/frontdoor/index/index/docId/2492 my:creator _:1 ]

[ _:1 rdf:type my:Person ]

[ _:1 my:name “Karsten Tolle” ]

Graf:

Karsten Tolle

http://publikationen.ub.uni-frankfurt.de/frontdoor/index/index/docId/2492

my:creator

my:Person

my:name

rdf:type

UnterschiedeCSV JSON XML RDF

Datenstruktur Tabelle Baum Baum gerichteter, benannter Graph

Attribut/Element gleich gleich unterschiedlich gleich

Sonst - JSON Schema

(Struktur)

XML Schema Definition (XSD)

(Struktur)

Ontology(Bedeutung)

…


Tools … ?• MySQL Workbench

• Heidi SQL

• OpenRefine (Google)

• …


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

… … … … … … …


1. Normalform

Definition:

Ein Relationenschema R ist in 1. Normalform (1NF), wenn die Wertebereiche aller Attribute von R atomar sind.

1.NF

Wertebereiche atomar


1. Normalform


Jon Lucas 12.03.1969 Zeil 12 Frankfurt 60313

Jon Lucas 12.03.1969 Lange Str. 114 Frankfurt 60313


… … … … … … …


Jon Lucas 12.03.1969 {Zeil, Lange Str.}

{12, 114} {Frankfurt, Frankfurt}

{60313,60313}


… … … … … … …


Diskutieren


Nachteile nicht 1. NF

• Finden wird erschwert bis unmöglich

• Sortierungen werden sinnlos bis unmöglich


Nutzung von Views

Empno Vorname Nachname Gehalt

1 Ines Müller 75000

2 Fred Wagner 60000

3 Ingo Meyer 72000

4 Max Müller 26000

Mitarbeiter

select Vorname, Nachname from Mitarbeiter;

Vorname Nachname

Ines Müller

Fred Wagner

Ingo Meyer

Max Müller

Ergebnis einer SQL-Select-Anfrage istimmer wieder eine Relation (Tabelle)!

CREATE VIEW Mitarbeiter_Namen ASselect Vorname, Nachname from Mitarbeiter;

Dr. Karsten Tolle – PRG2 – SS 2019 25Mitarbeiter_Dept

Empno Vorname Nachname Gehalt

1 Ines Müller 75000

2 Fred Wagner 60000

3 Ingo Meyer 72000

4 Max Müller 26000

Depno Name Head …

1 Finanzen 2

2 Einkauf 1

3 HR 1

Empno Depno …

1 2

1 3

2 1

3 3

4 2

CREATE VIEW Mitarbeiter_Dept ASselect m.Empno, m.Nachname, d.Name as Abtfrom Mitarbeiter m, Abteilung d, Arbeitet_in awhere m.Empno = a.Empnoand a.Depno = d.Depno;

Mitarbeiter Abteilung

Arbeitet_in

Empno Nachname Abt

1 Müller Einkauf

1 Müller HR

2 Wagner Finanzen

3 Meyer HR

4 Müller Einkauf

Mitarbeiter_Dept


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


Schlüssel ?

PLZ ORT STRASSE NR




37308 Geismar Bergstraße 1



Schlüssel ?

keine Abhängigkeiten

Schlüssel: PLZ, ORT, STRASSE, NR

PLZ ORT STRASSE NR



37308 Schirmberg Bergstraße 1






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?


Fahrzeuge der Firma AB

HERSTELLER KENNZEICHEN ZUGEL_GES_GEW FÜHRERSCHEIN

VW F-AB 123 3.5 B

MB F-AB 234 7.5 C1

MB F-AB 235 7.5 C1

MAN F-AB 236 12 C

MB F-AB 239 12 C

MB F-AB 230 17 C

… … … …

… aus ZUGEL_GES_GEW folgt hier FÜHRERSCHEIN:ZUGEL_GES_GEW FÜHRERSCHEIN… umgekehrt nicht! bis 17 Tonnen


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)

Funktionale Abhängigkeit (FD)


Beispiel

Gegeben ist die Relation r(R):

Geben Sie an, welche der folgenden Abhängigkeiten r nicht widerspricht:

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

A DAB DC BDEE AA EA BC


Axiome von Armstrong

1. β ⊆ α ⇛ 𝛼 → 𝛽 (Reflexivität)

2. 𝛼 → 𝛽 ⇛ 𝛼𝛾 → 𝛽𝛾 (Erweiterungsregel)

3. 𝛼 → 𝛽 ∧ 𝛽 → 𝛾 ⇛ 𝛼 → 𝛾 (Transitivität)


FDs?

Stadt Stadt Kreis, Stadt Kreis, Stadt…Stadt KreisKreis BundeslandStadt Bundesland

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


Übung FDs – Situation welche FDs gelten?

• Briefe und Porto

Brief_ID Gewicht_in_gr Porto_in_euro

1 18 0,80

2 12 1,55

3 7 0,80

4 19 0,95

5 75 1,55

Brief_ID Gewicht_in_gr Porto_in_euro

1 18 0,80

2 12 1,55

3 7 0,50

4 19 0,95

5 75 1,55

6 ?? 0,80

7 23 ??


Übung FDs – Situation welche FDs gelten?

• Briefe und Porto

Brief_ID L_in_cm B_in_cm H_in_cm Gewicht_in_gr Porto_in_euro

1 15 11,5 0,1 18 0,80

2 15 11,5 1,5 12 1,55

3 15 11,5 0,2 7 0,80

4 15 11,5 0,4 23 0,95

5 15 11,5 1,5 75 1,55

http://www.deutschepost.de/de/b/briefe-in-deutschland.html


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 Kvon 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.


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 (AX: X\A R)


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 = {AB, CDAC}

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

… ist dies bereits ein Schlüssel, sind wir fertig!


Gegeben Relationschema R und eine Menge F von FDs

z.B. R = (A, B, C, D, E) und FD = {AB, CDAC}

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 kombinatorisch ausprobieren. Bem.: Auch C gehört hier zu allen Schlüsseln!

Es steht zwar auf der rechten Seite, jedoch auch links.In diesem Fall darf man das C rechts streichen (nicht aber links).

Schlüssel = {CDE}


Übung

R = (A, B, C, D, E)

a. FD = {ABD, CDAC, ED}

b. FD = {AEBD, CDAC, ED}

a. Schlüssel = {CE}b. Schlüssel = {AC, CD, CE}


Wie viele Eintragungen gibt für die PLZ = 37308 ?

PLZ ORT STRASSE_NR








Wie viele Eintragungen gibt es pro PLZ?

select PLZ, count(*) from Adresse where PLZ = 37308;

PLZ count(*)

37308 3

Adresse


SQL - Anfragen

• group by PLZ

select A1,A2, ... ,An

from R1, R2, ... ,Rm

[where conditions]

[group by clause]

[having clause]

[order by clause];

PLZ ORT STRASSE_NR









Group by …• zum Gruppieren nach Spaltenwerte

Wie viele Eintragungen gibt es pro PLZ?

select PLZ, count(*) from Adresse group by PLZ;

PLZ count(*)

30419 2

37308 3

35279 1

53577 1

Adresse

PLZ ORT STRASSE_NR









Group by …• zum Gruppieren nach Spaltenwerte

PLZ ORT STRASSE_NR








Wie viele Eintragungen gibt es pro Kombination aus PLZ und ORT?

select PLZ, Ort, count(*) from Adresse group by PLZ, Ort;

PLZ ORT count(*)

30419 Hannover 2

37308 Schirmberg 1

37308 Bodenrode 1

37308 Geismar 1

35279 Neustadt 1

53577 Neustadt 1


Group by … having …• zum Gruppieren nach Spaltenwerte mit

Bedingung an die Gruppe!PLZ ORT STRASSE_NR








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;

PLZ ORT count(*)

30419 Hannover 2


Funktionen …

Wie viele Arbeitslose gibt es pro Bundesland?-- Summe aller AL pro Bundeslandselect name, sum(arbeitslose) as 'Anzahl der Arbeitslosen' from bundeslaender b, gemeindedaten g where b.ags = substring(g.ags from 1 for 2)group by b.ags;

gemeindedaten bundeslaender

MySQL Funktion: SUBSTRING(<attribut> FROM <start> FOR <anzahl>)SUBSTRING(AGS FROM 1 FOR 2) liefert die ersten beiden Zeichen


Funktionen MySQL:https://dev.mysql.com/doc/refman/8.0/en/functions.html

• Blatt 8: DAYOFWEEK(date) (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-

functions.html#function_dayofweek)

• Funktionen unterscheiden sich zwischen den verschiedenen DBMS!

https://dev.mysql.com/doc/refman/8.0/en/functions.html

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_dayofweek

Garten - Daten – Was ist das? Wer braucht das?prg2/SS2019/folien/teil2/4_db_2019.pdf ·...

Documents

Transcript of Garten - Daten – Was ist das? Wer braucht das?prg2/SS2019/folien/teil2/4_db_2019.pdf ·...