11. Große Übung

(1)

Jan-Christoph Kalo

Florian Plötzky und Denis Nagel Institut für Informationssysteme

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

11. Große Übung

Anwendungsprogrammierung 2

(2)

Das Schema ist heute:

Person(ausweisnr, vorname, name, land, geburtsdatum) Sportler(person →

Person, ist_gesperrt)

Funktionär(person →

Person, funktion)

Dopingtest(nummer, ausgang, sportler →

Sportler, funktionär

→

Funktionär)

Aufgabe 1

(3)

Ein positiver Dopingtest darf nicht aus der Datenbank entfernt werden können.

Aufgabe 1.1

Person(ausweisnr, vorname, name, land, geburtsdatum) Sportler(person→ Person, ist_gesperrt)

Funktionär(person→Person, funktion)

Dopingtest(nummer, ausgang, sportler→Sportler, funktionär→Funktionär)

CREATE TRIGGER error_delete_pos_dopingtest BEFORE DELETE ON Dopingtest

REFERENCING OLD AS old FOR EACH ROW

WHEN(

old.ausgang = ‘positive‘

)

RAISE_ERROR(‘Operation ist nicht zulässig‘)

(4)

Sportler dürfen beim Eintrag in die Datenbank maximal 35 Jahre alt sein (Vereinfachung: ein Jahresvergleich genügt).

Aufgabe 1.2

CREATE TRIGGER

error_sportler_35

BEFORE INSERT ON Sportler

REFERENCING NEW AS new FOR EACH ROW

WHEN(

((TODAY() / 10000) –

((SELECT geburtsdatum

FROM

Person p

WHERE

ausweisnr = new.person) / 10000)

)

> 35)

RAISE_ERROR(‘Operation ist nicht zulässig‘)

(5)

Wenn mehr als 2 Dopingtests für einen Sportler vorliegen, soll der betreffende Sportler gesperrt werden (zu prüfen nach Einfügen in Tabelle Dopingtest).

Aufgabe 1.3

CREATE TRIGGER correct_doping_tests AFTER INSERT ON Dopingtest

REFERENCING NEW AS new

NEW TABLE AS new_table FOR EACH ROW

WHEN(

(SELECT COUNT(*) FROM new_table WHERE sportler = new.sportler) > 2 )

BEGIN

UPDATE Sportler SET ist_gesperrt = ‘ ja‘ WHERE person = new.sportler END

(6)

Aufgabe 1.3 - Variation

CREATE TRIGGER correct_doping_tests AFTER INSERT ON Dopingtest

REFERENCING NEW AS new

NEW TABLE AS new_table FOR EACH ROW

WHEN(

EXISTS(

SELECT sportler, COUNT(nummer) AS n FROM new_table

WHERE sportler = new.sportler GROUP BY (sportler)

HAVING COUNT(nummer) > 2 ))

BEGIN

UPDATE Sportler SET ist_gesperrt = ‘ ja‘ WHERE person = new.sportler

(7)

• In der Onlineklausur wird reine TRC/DRC Syntax nicht realisierbar sein

– Zumindest nicht mit realistischem Aufwand …

• Verwenden Sie bitte folgende Notation

Aufgabe 2

DRC/TRC Element Onlineklausur

∧, ∨, ¬ AND, OR, NOT

∀, ∃ FORALL, EXISTS

→ ->

≤, ≥, ≠ <=, >=, !=

Beispiel: ku. vorname, ku. name Kunde ku ∧¬∃ko(Konto(ko)∧ ko. kunde = ku. kunr)}wird zu {ku.vorname, ku.name | Kunde(ku) AND NOT EXISTS ko(Konto(ko) AND ko.kunde = ku.kunr)}

(8)

Geben Sie die Vor- und Nachnamen von gesperrten Sportlern zurück.

Aufgabe 2.1

{p.vorname, p.name |

{p.vorname, p.name | Person(p) AND EXISTS s(Sportler(s) AND s.person = p.ausweisnr AND s.ist_gesperrt

= ‚ja')}

Person(p)

⋀ ∃s(Sportler(s) ⋀

s.person = p.ausweisnr

⋀

s.ist_gesperrt

= ‚ja')}

(9)

Bestimmen Sie alle Sportler, die ausschließlich über positive Dopingtests verfügen.

Variante A

Aufgabe 2.2

{s.person | Sportler(s)

⋀ ∃d(Dopingtest(d) ⋀

d.sportler = s.person

⋀

∀d'((Dopingtest(d') ⋀

d'.sportler = s.person)

→

(d'.ausgang = 'positiv'))) } {s.person | Sportler(s)

AND FORALL d'((Dopingtest(d') AND d'.sportler = s.person) -> (d'.ausgang = 'positiv')) }

AND EXISTS d(Dopingtest(d) AND d.sportler = s.person

)

(10)

Bestimmen Sie alle Sportler, die ausschließlich über positive Dopingtests verfügen.

Variante B

Aufgabe 2.2

{s.person | Sportler(s) AND EXISTS d(Dopingtest(d) AND d.sportler =

s.person AND NOT EXISTS d'(Dopingtest(d') AND d'.sportler = s.person AND d'.ausgang = 'negativ'))}

{s.person | Sportler(s)

⋀ ∃d(Dopingtest(d) ⋀

d.sportler = s.person

⋀

¬∃d'(Dopingtest(d') ⋀ d'.sportler = s.person

⋀

d'.ausgang = 'negativ'))}

(11)

EXISTS p,p'(Person(p) AND Person(p') AND p.ausweisnr = f.person AND p'.ausweisnr = f'.person

Bestimmen Sie das jüngste Komiteemitglied (d.h. Funktionär.funktion =

‚Komiteemitglied‘).

TRC

Aufgabe 2.3

AND p'.geburtsdatum <= p.geburtsdatum))}

{f.person | Funktionär(f) ⋀ f.funktion = 'Komiteemitglied' ⋀ ∀f'((Funktionär(f')

⋀ f.funktion = 'Komiteemitglied' ⋀ f'.person ≠ f.person) → ∃p,p'(Person(p) ⋀ Person(p') ⋀ p.ausweisnr = f.person ⋀ p'.ausweisnr = f'.person ⋀

p'.geburtsdatum ≤ p.geburtsdatum))}

{f.person | Funktionär(f) AND f.funktion = 'Komiteemitglied'

AND FORALL f'((Funktionär(f') AND f‘.funktion = 'Komiteemitglied' AND f'.person != f.person) ->

(12)

Bestimmen Sie das jüngste Komiteemitglied (d.h. Funktionär.funktion =

‚Komiteemitglied‘).

DRC

Aufgabe 2.3

{id |

{id | Funktionär(id, 'Komiteemitglied') ⋀ ∀id'((Funktionär(id', 'Komiteemitglied') ⋀ id' ≠ id) →

∃vn,n,l,g,vn',n',l',g'(Person(id, vn, n, l, g) ⋀

Person(id', vn', n', l', g') ⋀ g'

≤

g))}

(EXISTS vn,n,l,g,vn',n',l',g'(Person(id, vn, n, l, g) AND Person(id', vn', n', l', g‘) Funktionär(id, 'Komiteemitglied‘)

AND FORALL id'((Funktionär(id', 'Komiteemitglied') AND id' != id) ->

AND g' <= g))}

(13)

Gegeben sei das Schema R(A, B, C, D, E) mit den funktionalen Abhängigkeiten:

FD = {{A,B} → C, {A} → {D}, {D} → {E}, {C} → {B}}

Aufgabe 3

A B C D E

(14)

Bringen Sie das Schema in die Boyce-Codd Normalform (BCNF).

Aufgabe 3

(15)

• Zunächst die 1. Normalform:

– Hier: Trivialerweise erfüllt

• Keine Multi-Valued Attributes

Aufgabe 3 – 1NF

A B C D E

(16)

• 2. Normalform:

– 1. Normalform erreicht

– Kein Nichtschlüsselattribut ist von einer echten Teilmenge eines Kandidatenschlüssels abhängig

• Kandidatenschlüssel = {{A,B}, {A,C}}

Aufgabe 3 – 2NF

A B C D E

Relation ist nicht in 2NF!

D ist abhängig von A und damit von

(17)

Aufteilen der Relation nach {A} → {D} und {D} → {E}

Aufgabe 3 – 2NF

A B C D E

A B C A D E

(18)