TU Wien:Datenbanksysteme VU (Hose)/2024-05-04 Prüfung Gedächtnisprotokoll

Prüfung am 24. Mai 2024

Die korrekten Antworten sind fett + unterstrichen gekennzeichnet.

I. SQL (Fortsetzung Aufgabenblock I)

Finden Sie für jedes Fachgebiet die Gesamtzahl der Termine deren Datum nach dem heutigen Datum liegen. Wenn für ein Fachgebiet keine Termine vorhanden sind, sollte das Fachgebiet nicht in die Ergebnisse aufgenommen werden. Hinweis: Nehmen Sie an, dass Sie auf das heutige Datum mit der Funktion CURRENT_DATE zugreifen können.

SELECT [box 1] AS fachgebietName, [box 2] AS anzahlTermine
FROM termine AS t, [box 3] AS x
WHERE t.[box 4] = [box 5] AND t.datum > CURRENT_DATE
GROUP BY [box 6]

5. box 1

• x.fachgebiet
• x.name
• x.name.fachgebiet
• x.fachgebiet.name
• t.arzt
• t.arzt.fachgebiet

6. box 2

• t.date
• MAX(*)
• COUNT(*)
• x.termin
• *
• t.anzahl
• x.anzahl
• SUM(*)

7. box 3

• warteliste
• fachgebiet
• arzt
• patient
• termin

8. box 4

• arzt.svnr
• patient
• patient.svnr
• arzt
• svnr

9. box 5

• x.fachgebiet.name
• x.arzt.svnr
• x.patient.svnr
• x.name.fachgebiet
• x.patient
• x.svnr
• x.arzt
• x.name
• x.fachgebiet

10. box 6

• x.patient.svnr
• x.fachgebiet.name
• x.fachgebiet
• x.patient
• x.name
• x.name.fachgebiet
• x.arzt.svnr
• x.svnr

II. Relationale Algebra (15 Punkte)

Betrachten Sie noch einmal das relationale Schema von Aufgabenblock I, das wie folgt aussieht.

• termin(datum, zeit, arzt → arzt.svnr, patient → patient.svnr, beschreibung)
• warteliste(arzt → arzt.svnr, patient → patient.svnr, vorgesetzter → arzt.svnr)
• arzt(svnr, name, fachgebiet, hausarzt → fachgebietVon)
• patient(svnr, name, telefon, unterfachgebietVon)
• fachgebiet(name, beschreibung, unterfachgebietVon)

Wobei die Primärschlüssel unterstrichen sind und die Fremdschlüssel durch Pfeile dargestellt werden.

Identifizieren Sie die fehlenden Informationen für die folgende Aussage, die in die Felder eingetragen werden müssen, um eine Anfrage für die angeforderten Informationen zu erstellen.

Finden Sie für jedes Fachgebiet die Gesamtzahl der Termine für Daten nach dem '02.06.2020'. Wenn für ein Fachgebiet keine Termine vorhanden sind, sollte das Fachgebiet mit der Anzahl der Termine 0 in die Ergebnisse aufgenommen werden. Hinweis: Datumsangaben haben das folgende Format, z. B. '28.02.2020'.

${\gamma }_{f.name,\mathrm{c}\mathrm{o}\mathrm{u}\mathrm{n}\mathrm{t}(\ast )}(([box2]\bowtie [box3]\bowtie [box4]){\sigma }_{t.datum>\text{'02.06.2020'}}([box7]))$

11. box 1

• a.fachgebiet
• t.datum
• *
• p.name
• f.name
• t.patient
• t.arzt
• a.name

12. box 2

• patient
• ρ_t(termin)
• ρ_fachgebiet(f)
• fachgebiet
• ρ_patient(p)
• termin
• ρ_arzt(a)
• arzt
• ρ_a(arzt)
• ρ_termin(t)
• ρ_p(patient)
• ρ_f(fachgebiet)

13. box 3

• f.name = p.name
• fachgebiet.name = a.fachgebiet
• fachgebiet.name = p.name
• TRUE
• *
• f.name = a.name
• FALSE
• f.name = a.fachgebiet
• fachgebiet.name = a.name

14. box 4

• termin
• ρ_termin(t)
• ρ_arzt(a)
• fachgebiet
• ρ_p(patient)
• ρ_f(fachgebiet)
• patient
• ρ_t(termin)
• arzt
• ρ_fachgebiet(f)
• ρ_patient(p)
• ρ_a(arzt)

15. box 5

• a.svnr = a.vorgesetzter
• t.arzt = a.svnr
• t.patient = p.svnr
• f.name = f.unterfachgebietVon
• p.svnr = a.svnr
• f.name = a.fachgebiet

16. box 6

• datum
• termin.zeit
• zeit
• t.zeit
• a.datum
• a.zeit
• t.datum
• termin.datum

17. box 7

• termin
• ρ_arzt(a)
• ρ_f(fachgebiet)
• ρ_fachgebiet(f)
• patient
• ρ_p(patient)
• fachgebiet
• ρ_patient(p)
• arzt
• ρ_a(arzt)
• ρ_termin(t)
• ρ_t(termin)

III. ER-Modellierung (30 Punkte)

Betrachten Sie den folgenden Anwendungsfall für eine Event-Ticketing-App, den wir in einem ER-Diagramm modellieren möchten:

• Jede Veranstaltung wird durch einen Namen, eine eindeutige ID und einen oder mehrere Künstler:innen beschrieben.
• Jede Veranstaltung kann an verschiedenen Veranstaltungsorten und an verschiedenen Daten stattfinden.
• Jede Veranstaltung ermöglicht an einem bestimmten Datum den Besuch einer Veranstaltung an einem Veranstaltungsort.
• Jedes Ticket muss genau einem Datum, genau einer Veranstaltung, genau einem Veranstaltungsort und genau einem Preis zugeordnet sein.
• Es können an jedem beliebigen Datum keine oder mehrere Veranstaltungen stattfinden.
• Jeder Veranstaltungsort hat einen Namen, einen Breitengrad und einen Längengrad.
• Jeder Veranstaltungsort kann ein oder mehrere Veranstaltungen ausrichten.
• Jede:r Benutzer:in wird durch einen Namen, eine Adresse und eine Telefonnummer beschrieben.
• Jede:r Künstler:in wird durch einen Namen und eine eindeutige ID beschrieben.
• Jede:r Künstler:in performt bei einer oder mehreren Veranstaltungen.

Als Beispiel für eine Veranstaltung betrachten Sie die Tour 2020/2021 der Sängerin Yael Naim. Diese Veranstaltung findet an verschiedenen Veranstaltungsorten (z. B. Hoxton Hall in London, Hotel Cecil in Kopenhagen) an verschiedenen Daten (z. B. 6. Oktober, 13. Dezember) statt.

18. Wählen Sie das ER-Diagramm aus, das die oben genannten Informationen am besten widerspiegelt

(Textuelle Rekonstruktion der vier Diagramm-Varianten; im Original handgezeichnet)

Diagramm (a):

• Entität Veranstaltung (Name, VeranstaltungID)
• Entität Datum (Datum) — Beziehung hatD [M Datum : N Veranstaltung]
• Entität Preis (Preis) — Beziehung hatP [N Ticket : M Preis]
• Entität Ticket (TicketID) — Beziehung hatV [1 Veranstaltung : N Ticket]
• Beziehung hatVO [N Ticket : 1 Veranstaltungsort]
• Entität Veranstaltungsort (Name, Breitengrad, Längengrad)
• Beziehung kauft [N Ticket : 1 Benutzer:in]
• Entität Benutzer:in (Name, Adresse, Telefon)
• Beziehung performt [N Veranstaltung : M Künstler:in]
• Entität Künstler:in (Name, Künstler:inID)

Diagramm (b):

• Entität Veranstaltung (Name, VeranstaltungID, mehrwertige Attribute Künstler:inIDs, Künstler:inNamen, Breitengrad, Längengrad)
• Beziehung besucht [N Veranstaltung : M Benutzer:in] mit Beziehungsattributen Datum, Preis
• Entität Benutzer:in (Name, Adresse, Telefon)

Diagramm (c):

• Entität Veranstaltung (Name, VeranstaltungID) — Beziehung hatV [1 Veranstaltung : N Ticket]
• Entität Ticket (Datum, Preis)
• Beziehung hatVO [N Ticket : 1 Veranstaltungsort]
• Entität Veranstaltungsort (Name, Breitengrad, Längengrad)
• Beziehung performt [1 Veranstaltung : N Künstler:in]
• Entität Künstler:in (Künstler:inID, Name)
• Beziehung kauft [N Ticket : 1 Benutzer:in]
• Entität Benutzer:in (Name, Adresse, Telefon)

Diagramm (d):

• Entität Veranstaltung (Name, VeranstaltungID)
• Beziehung kauft [N Veranstaltung : M Veranstaltungsort] mit Beziehungsattributen Datum, Preis; Kardinalität P (partiell) Richtung Benutzer:in
• Entität Veranstaltungsort (Name, Breitengrad, Längengrad)
• Beziehung performt [N Veranstaltung : M Künstler:in]
• Entität Künstler:in (Künstler:inID, Name)
• Entität Benutzer:in (Telefon, Name, Adresse)

19. Betrachten Sie den Entitätstyp Veranstaltungsort mit den Attributen Name, Breitengrad und Längengrad, wie in den ER-Diagrammen (a), (c) und (d) gezeigt. Welche Attribute sollten als Primärschlüssel verwendet werden? Sie können ein oder mehrere auswählen.

• Längengrad
• Breitengrad
• Name

20. Welche der folgenden Entitätstypen sollte(n) im ER-Diagramm (c) als schwacher Entitätstyp modelliert werden? Sie können null, einen oder mehrere Entitätstypen auswählen; um null auszuwählen, wählen Sie bitte die Option "(e) Keiner".

• Veranstaltung
• Künstler:in
• Preis
• Datum
• Keiner
• Veranstaltungsort
• Benutzer:in
• Ticket

21. Betrachten Sie das folgende ERD-Fragment

Das ERD zeigt die Entitäten Veranstaltung (VeranstaltungsID, Name) und Künstler:in (Künstler:inID, Name), die über die Beziehung „performt“ miteinander verbunden sind. Die Teilnahmebedingungen werden durch Kardinalitäten der Form [min,max] auf beiden Seiten beschrieben.

In diesem Fragment wird die [min, max]-Notation verwendet, um Kardinalitätsbeschränkungen festzulegen. Identifizieren Sie die Werte für [min_x, max_x] und [min_y, max_y], die es ermöglichen, die anfängliche textuelle Beschreibung zu erfassen.

• [0, *] und [1, *]
• [0, *] und [0, *]
• [1, *] und [0, *]
• [1, *] und [1, *]

22. Betrachten Sie das folgende ERD-Fragment (Veranstaltung [N] – performt – [M] Künstler:in)

Wie viele Relationen sollten erstellt werden, wenn dieses Fragment in Relationen umgewandelt wird? Gesucht ist ein gutes Design, das Redundanz und Unvollständigkeit minimiert. Das heißt, wie es entstehen würde, wenn der in den Vorlesungen behandelte Ansatz befolgt wird, um die anfängliche textuelle Beschreibung zu erfassen.

• 4
• 2
• 1
• 3
• 0

IV. Transaktionen (10 Punkte)

23. Gegeben sei folgender Schedule

$r_1(x)\to r_2(y)\to w_2(y)\to r_3(z)\to r_1(x)\to r_3(y)\to w_3(y)\to r_4(z)\to r_3(y)\to w_3(z)\to w_1(x)\to c_1\to c_2$

wobei

• $r_1(x)$ bedeutet, dass T₁ das Datenobjekt x liest (w repräsentiert einen Schreibzugriff)
• $c_1$ bedeutet, dass T₁ ein Commit ausführt
• $\to$ die Reihenfolge angibt, in denen die Operationen ausgeführt werden

Welche der folgenden gerichteten Kanten sind Teil des Conflict Graphs? Sie können eine oder mehrere Kanten auswählen.

(Knoten: T1, T2, T3, T4)

• (T3, T1)
• (T1, T2)
• (T2, T4)
• (T4, T1)
• (T1, T3)
• (T3, T2)
• (T3, T4)
• (T2, T3)
• (T2, T1)
• (T4, T3)
• (T1, T4)
• (T4, T2)

24. Gegeben seien die folgenden Conflict Graphs für Schedules. Identifizieren Sie jene Schedules, die (conflict) serializable sind. Sie können einen oder mehrere Graphen auswählen.

Hinweis: Die vier Graphen (a)–(d) bestehen jeweils aus den Transaktionsknoten T1–T9 (in drei Ebenen: T1–T3, T4–T6, T7–T9) mit gerichteten Kanten zwischen den Ebenen. Die genaue Kantenstruktur jedes einzelnen Graphen lässt sich aus der vorliegenden Fotoaufnahme nicht mit ausreichender Sicherheit 1:1 rekonstruieren – für die exakten Pfeile bitte das Originaldokument konsultieren. Gesucht sind jene Graphen, die azyklisch sind (= conflict serializable).

• Graph (a) — T1,T2,T3 / T4,T5,T6 / T7,T8,T9
• Graph (b) — T1,T2,T3 / T4,T5,T6 / T7,T8,T9
• Graph (c) — T1,T2,T3 / T4,T5,T6 / T7,T8,T9
• Graph (d) — T1,T2,T3 / T4,T5,T6 / T7,T8,T9

V. Entwurfstheorie (20 Punkte)

25. Funktionale Abhängigkeiten

Gegeben sei die folgende Tabelle:

mid	title	year	country	genre	actor	director
8371	A Night to Remember	1942	USA	Comedy	Sidney Toler	Randall Wallace
8371	A Night to Remember	1942	USA	Mystery	Sidney Toler	Randall Wallace
8371	A Night to Remember	1942	USA	Romance	Sidney Toler	Randall Wallace
16676	Dark Alibi	1946	USA	Crime	Sidney Toler	Phil Karlson
16676	Dark Alibi	1946	USA	Drama	Sidney Toler	Phil Karlson
16676	Dark Alibi	1946	USA	Mystery	Sidney Toler	Phil Karlson
16676	Dark Alibi	1946	USA	Thriller	Sidney Toler	Phil Karlson
15696	Coney Island	1943	USA	Comedy	Yakima Canutt	Tracy Casper Lang
15696	Coney Island	1943	USA	Musical	Yakima Canutt	Tracy Casper Lang

Bestimmen Sie, welche der folgenden funktionalen Abhängigkeiten in dieser Tabelle erfüllt sind. Sie können eine oder mehrere funktionale Abhängigkeiten auswählen.

• {title} → {actor}
• {year, actor} → {title}
• {title} → {year, actor}
• {actor} → {title}

Schlüssel

Für die nächsten drei Fragen gelten das relationale Schema

$ℛ=\{[A,B,C,D,E]\}$

und die folgende Menge $F$ an funktionalen Abhängigkeiten:

$\{A\}\to \{B,D\}$

$\{B\}\to \{A,C,E\}$

$\{A,C\}\to \{B\}$

$\{B,E\}\to \{D\}$

26. Welche der folgenden Attributmengen sind Superschlüssel? Sie können eine oder mehrere Attributmengen auswählen.

• {C, E}
• {B, C}
• {A, B}
• {C}
• {B}
• {A, C}
• {E}
• {A, E}
• {A}
• {B, E}

27. Welche der folgenden Attributmengen sind Schlüsselkandidaten? Sie können eine oder mehrere Attributmengen auswählen.

• {A, B}
• {C, E}
• {B, C}
• {A, C}
• {B, E}
• {B}
• {E}
• {A}
• {A, E}
• {C}

27. Welche der folgenden Aussagen bezüglich der gegebenen funktionalen Abhängigkeiten und Normalformen sind wahr? [MC]

• (R, F) erfüllt BCNF
• (R, F) erfüllt 3NF
• (R, F) erfüllt nicht BCNF
• (R, F) erfüllt nicht 3NF