10. Große Übung

Jan-Christoph Kalo Florian Plötzky

Institut für Informationssysteme

Technische Universität Braunschweig www.ifis.cs.tu-bs.de

10. Große Übung

Anwendungsprogrammierung 1

Gegeben sei folgendes relationale Schema:

Kunde(kunnr, vorname, name, gebdat) Konto(ktno, kunde → Kunde, typ)

Buchung(konto → Konto, bid, Datum, Uhrzeit, Betrag, FremdesKonto → Konto)

Aufgabe 1 - Views

Erzeugen Sie eine View, welche alle Vor- und Nachnamen von Kunden enthält, die sowohl über ein Spar- als auch über ein Girokonto

verfügen.

Aufgabe 1.1

Kunde(kunnr, vorname, name, gebdat) Konto(ktno, kunde→Kunde, typ)

CREATE VIEW kunden_spar_giro AS WITH rel_ku AS (

SELECT kunde FROM Konto WHERE typ = ‘Sparkonto’

INTERSECT

SELECT kunde FROM Konto WHERE typ = ‘Girokonto’

)

SELECT vorname, name FROM Kunde k

JOIN rel_ku r ON r.kunde = k.kunnr

Aufgabe 1.2

Erzeugen Sie eine View, welche die Nachnamen aller Kunden enthält, die über mindestens ein Konto ohne Abbuchung verfügen. Die

Spaltenbezeichnung für den Nachnamen in der View soll “NN” lauten.

CREATE VIEW nn_ohne_buchung(nn) AS SELECT name

FROM Kunde ku

JOIN Konto ko ON ko.kunde = ku.kunnr WHERE NOT EXISTS (

SELECT * FROM Buchung WHERE konto = ko.ktno )

Aufgabe 1.3

Erzeugen Sie eine materialisierteView, welche die Kontonummern von Kunden enthält, die mindestens 3x an einem beliebigen aber gleichen Datum eine

Überweisung an das Konto 0815 getätigt haben (FremdesKonto = ‘0815’). Die View soll sich automatisch bei Datenänderung aktualisieren.

Kunde(kunnr, vorname, name, gebdat) Konto(ktno, kunde→Kunde, typ)

CREATE TABLE kontos_0815 AS SELECT konto

FROM Buchung

WHERE FremdesKonto = ‘0815’

GROUP BY konto, Datum HAVING COUNT(*) >= 3 REFRESH IMMEDIATE

Nehmen Sie folgendes vereinfachtes Schema an:

Kunde(kunnr, konto → Konto, name) Konto(ktonr, bemerkung)

Erzeugen Sie einen Index auf dem Fremdschlüssel in Relation Kunde.

Aufgabe 2 - Indizes

CREATE INDEX kunde_konto_fk ON Kunde(konto)

Aufgabe 2

… hier recht einfach, technische Fortsetzung in RDB 2

ABER

• Eine kurze Detour zu Indizes in Postgres

– In dieser Detour neu eingeführte Konzepte sind nicht prüfungsrelevant

• Gegeben sei das Schema aus Aufgabe 2 mit 1 Million Datensätzen für Tabelle Konto und 2 Millionen Datensätzen für Tabelle Kunde

Indexing in Postgres

• Mittels EXPLAIN lässt sich der Query-Plan einer Anfrage ermitteln

– Vgl. Vorlesung 6 aus RDB2

– Für uns ist hier nur die Variante EXPLAIN

ANALYZE interessant, welche die Query ausführt und die Ausführungszeit misst

SQL EXPLAIN

• SELECT *

FROM Kunde ku

JOIN Konto ko ON ku.konto = ko.ktonr WHERE ko.bemerkung = ‘Bemerkung 42‘

• Hier bspw. in ca. 392ms

Beispielquery

• Ein einzelner Wert reicht nicht aus: also ein paar Werte sammeln

Mehr Werte

Lauf 1 2 3 4 5 6 7 8 9 10

Zeit [ms]

392 413 418 412 423 431 422 446 425 459

Durchschnittliche Laufzeit hier also µ

= 424 ms

(𝜎

= 18)

• Join zwischen Kunde und Konto braucht Zeit.

– Die rechte Seite des Joins (Konto) findet auf dem

Primärindex (ktonr) statt, auf der linken Seite (Kunde) muss entweder sequentiell die gesamte Tabelle gelesen oder anderweitig vorgesorgt werden (bspw. durch

Hashing), beide Optionen kosten Zeit

• Idee: Setze Sekundärindex auf Kunde.konto

Problem

CREATE INDEX kunde_konto_fk ON Kunde(konto)

Index und neue Messung

Lauf 1 2 3 4 5 6 7 8 9 10

Zeit [ms]

90 89 98 99 95 104 93 99 88 89

Durchschnittliche Laufzeit hier also µ

= 94.4 ms

(𝜎

= 5)

• Deutliche Performancesteigerung!

– n=10 ist natürlich recht wenig und wir haben die Signifikanz der Abweichung nicht gezeigt. Für die Intuition sollte es aber genügen

• Sollte man nicht grundsätzlich Sekundärindizes auf Fremdschlüssel legen?

– Nein!

• Wenn viele INSERT und/oder UPDATE Statements auf eine

Schlussfolgerung

• Schema:

– Geschäft (id, Eigentümer, Stadt)

– Produkt (id, Name, Kategorie, Preis)

– Einkauf (id, gid → Geschäft, Datum, Uhrzeit) – In (eid → Einkauf, pid → Produkt)

Aufgabe 3 – SQL and RA

1. Finden Sie den Preis des teuersten Einkaufs.

• SQL:

– Hier funktioniert kein HAVING MAX(kosten) = kosten

Aufgabe 3 – SQL and RA

WITH Einkaufspreise AS (

SELECT e.id AS einkauf, SUM(Preis) AS kosten FROM Produkt p

JOIN In i ON p.id=i.pid

JOIN Einkauf e ON e.id = i.eid GROUP BY e.id)

SELECT MAX(kosten) FROM Einkaufspreise

𝐸𝑖𝑛𝑘𝑎𝑢𝑓𝑠𝑝𝑟𝑒𝑖𝑠𝑒

= 𝜌 𝔉 (𝑃𝑟𝑜𝑑𝑢𝑘𝑡 ⋈ 𝐼𝑛 ⋈ 𝐸𝑖𝑛𝑘𝑎𝑢𝑓)

2. Finden Sie die Einkäufe in denen alle Produkte unter 1€ gekostet haben.

SQL:

• RA:

Aufgabe 3 – SQL and RA

Geschäft (id, Eigentümer, Stadt) Produkt (id,Name, Kategorie, Preis)

Einkauf (id, gid → Geschäft, Datum, Uhrzeit) In (eid→ Einkauf, pid→ Produkt)

SELECT e.id FROM Produkt p

JOIN In i ON p.id=i.pid

JOIN Einkauf e ON e.id = i.eid GROUP BY e.id

HAVING MAX(preis) < 1.00

𝑡𝑒𝑢𝑒𝑟𝑠𝑡𝑒𝑠𝑃𝑟𝑜𝑑𝑢𝑘𝑡

= 𝜌𝑒𝑖𝑛𝑘𝑎𝑢𝑓,max_𝑝𝑟𝑒𝑖𝑠 𝐸𝑖𝑛𝑘𝑎𝑢𝑓.𝑖𝑑 𝔉_{𝑚𝑎𝑥 𝑃𝑟𝑒𝑖𝑠} (𝑃𝑟𝑜𝑑𝑢𝑘𝑡 ⋈_{𝑖𝑑=𝑝𝑖𝑑} 𝐼𝑛 ⋈_{𝑒𝑖𝑑=𝑖𝑑} 𝐸𝑖𝑛𝑘𝑎𝑢𝑓) 𝜎 _max

𝑝𝑟𝑒𝑖𝑠<1.0 𝑡𝑒𝑢𝑒𝑟𝑠𝑡𝑒𝑠𝑃𝑟𝑜𝑑𝑢𝑘𝑡

SELECT DISTINCT i.eid FROM Einkauf e WHERE 1.00 > ALL(

SELECT preis FROM produkt p

JOIN In i ON p.id = i.pid WHERE i.eid = e.id)

3. Finden Sie die prozentuale Umsatzsteigerung vom Jahr 2019 aufs Jahr 2020.

SQL:

Aufgabe 3 – SQL and RA

WITH jahr_2019 AS (SELECT‘2019' AS jahr,

sum(preis) AS summe

FROM einkauf AS e JOIN in AS i ON i.eid=e.id JOIN produkt AS p ON in_n_a.pid=p.id WHERE datum BETWEEN 20190101 AND 20191231),

jahr_2020 AS

(SELECT‘2020' AS jahr, sum(preis) AS summe

FROM einkauf AS e JOIN in AS I ON i.eid=e.id JOIN produkt AS p ON in_n_a.pid=p.id WHERE datum BETWEEN 20200101 AND 20201231)

SELECT (jahr_2020. summe - jahr_2019. summe) / jahr_2019. summe *100 AS prozent_erhohen

3. Finden Sie die prozentuale Umsatzsteigerung vom Jahr 2019 aufs Jahr 2020.

RA:

Aufgabe 3 – SQL and RA

Geschäft (id, Eigentümer, Stadt) Produkt (id,Name, Kategorie, Preis)

Einkauf (id, gid → Geschäft, Datum, Uhrzeit) In (eid→ Einkauf, pid→ Produkt)

Jahr2019

= 𝜌_{𝑠𝑢𝑚𝑚𝑒} 𝔉_{𝑠𝑢𝑚 𝑃𝑟𝑒𝑖𝑠} 𝜎𝑑𝑎𝑡𝑢𝑚≥20190101 ٿ 𝑑𝑎𝑡𝑢𝑚≤20191231(𝑃𝑟𝑜𝑑𝑢𝑘𝑡 ⋈_{𝑖𝑑=𝑝𝑖𝑑} 𝐼𝑛 ⋈_{𝑒𝑖𝑑=𝑖𝑑} 𝐸𝑖𝑛𝑘𝑎𝑢𝑓) Jahr2020

= 𝜌_{𝑠𝑢𝑚𝑚𝑒} 𝔉_{𝑠𝑢𝑚 𝑃𝑟𝑒𝑖𝑠} 𝜎𝑑𝑎𝑡𝑢𝑚≥20200101 ٿ 𝑑𝑎𝑡𝑢𝑚≤20201231(𝑃𝑟𝑜𝑑𝑢𝑘𝑡 ⋈_{𝑖𝑑=𝑝𝑖𝑑} 𝐼𝑛 ⋈_{𝑒𝑖𝑑=𝑖𝑑} 𝐸𝑖𝑛𝑘𝑎𝑢𝑓)

((Jahr2020 - Jahr2019 ) / Jahr2019))*100

4. Finden Sie Produkte, die in Braunschweig, aber nicht in Hannover verkauft werden

SQL:

– Falsch: WHERE g.stadt='Braunschweig AND g.stadt <> ‘Hannover’

Aufgabe 3 – SQL and RA

SELECT in_b.pid FROM geschaft AS g

JOIN einkauf AS e ON e.gid=g.id JOIN in AS in_b ON in_b.eid=e.id WHERE g.stadt='Braunschweig' EXCEPT

SELECT in_h.pid FROM geschaft AS g

JOIN einkauf AS e ON e.gid=g.id JOIN in AS in_h ON in_h.eid=e.id WHERE g.stadt='Hannover'

4. Finden Sie Produkte, die in Braunschweig, aber nicht in Hannover verkauft werden

RA:

Aufgabe 3 – SQL and RA

Geschäft (id, Eigentümer, Stadt) Produkt (id,Name, Kategorie, Preis)

Einkauf (id, gid → Geschäft, Datum, Uhrzeit) In (eid→ Einkauf, pid→ Produkt)

𝜋_𝑝𝑖𝑑𝜎_{𝑠𝑡𝑎𝑑𝑡=}^′𝐵𝑟𝑎𝑢𝑛𝑠𝑐ℎ𝑤𝑒𝑖𝑔′𝐺𝑒𝑠𝑐ℎä𝑓𝑡 ⋈_{𝑖𝑑=𝑔𝑖𝑑} 𝐸𝑖𝑛𝑘𝑎𝑢𝑓 ⋈_{𝑖𝑑=𝑒𝑖𝑑}In

\

𝜋_𝑝𝑖𝑑𝜎_{𝑠𝑡𝑎𝑑𝑡=}′𝐻𝑎𝑛𝑛𝑜𝑣𝑒𝑟′𝐺𝑒𝑠𝑐ℎä𝑓𝑡 ⋈_{𝑖𝑑=𝑔𝑖𝑑} 𝐸𝑖𝑛𝑘𝑎𝑢𝑓 ⋈_{𝑖𝑑=𝑒𝑖𝑑}In

Rückblick - Normalisierung

Funktionale Abhängigkeiten

– F = {𝐻 → 𝐺, 𝐷 , 𝐸 → 𝐷, 𝐻 → 𝐶, 𝐸 , {𝐵, 𝐷} →A}

1. Finden Sie die Schlüsselkandidaten

• B, F, H, I tauchen auf keiner rechten Seite auf

– Jeder Schlüsselkandidat enthält mindestens B, F, H, I

• ( 𝐵, 𝐹, 𝐻, 𝐼 , 𝐹)⁺ ausrechnen

– Closure = {B, F, H, I}

– Anwendung von 𝐻 → 𝐺, 𝐷 . Closure = {B, D, F, G, H, I}

– Anwendung von H → 𝐶, 𝐸 . Closure = {B, C, D, E, F, G, H, I}

– Anwendung von {B, D} → A. Closure = {𝐀, 𝐁, 𝐂, 𝐃, 𝐄, 𝐅, 𝐆, 𝐇, 𝐈}

• ( 𝐵, 𝐹, 𝐻, 𝐼 , 𝐹)⁺ enthält alle Attribute von R

– B,F,H,I ist der einzige Schlüsselkandidat

Rückblick - Normalisierung

– Nicht in 2.NF: A hängt nicht vom gesamten Schlüssel ab

• Sondern nur von der Schlüsselmenge {B} und transitive von H über {D}.

• Überführen in die 2. NF über Zerlegung mit {𝐵, 𝐷} →A

– Der Schlüssel von B,D,A ändert sich!

– Nicht in 2.NF: C, E, G, D hängen nur von H ab

A B C D E F G H I

A

B D B C D E F G H I

B F H I

C D E G H

• Programmiersprachen und DBMS 2

• Active Databases

• Grundlagen Security

Nächste Woche