Aufgabenstellung

In diesem Teil sollen zunächst Anfragen auf der in Teil 1 erstellten Datenbank formuliert werden. Anschließend sollen Änderungsoperationen auf der geladenen Datenbank durchgeführt werden, wobei auf die Wahrung der Datenkonsistenz zu achten ist. Im dritten Teil soll eine Prozedur formuliert und in einer Anfrage verwendet werden.

2a. Sichtenerstellung

Die Freundschaftsbeziehung ist als gerichtete Beziehung gespeichert, um Anfragen bzgl. der Freundschaftsbeziehung komfortabel zu lösen sollen die Beziehungen ungerichtet gespeichert werden. Diesbezüglich sollen Sie eine Sicht “pkp_symmetric” erstellen, die beide Richtungen enthält.

2b. Anfragen auf der Datenbank

Formulieren Sie SQL-Anfragen, um folgende Fragen zu beantworten.

1. In wie vielen verschiedenen afrikanischen Städten gibt es eine Universität?
2. Wie viele Forenbeiträge (Posts) hat die älteste Person verfasst (Ausgabe: Name, #Forenbeiträge)?
3. Wie viele Kommentare zu Posts gibt es aus jedem Land (Ausgabe aufsteigend sortiert nach Kommentaranzahl)? Die Liste soll auch Länder enthalten, für die keine Post-Kommentare existieren, d.h. die Kommentaranzahl = 0 ist! (Funktion Coalesce)
4. Aus welchen Städten stammen die meisten Nutzer (Ausgabe Name + Einwohnerzahl)?
5. Mit wem ist ‘Hans Johansson’ befreundet?
6. Wer sind die “echten” Freundesfreunde von ‘Hans Johansson’? “Echte” Freundesfreunde dürfen nicht gleichzeitig direkte Freunde von ‘Hans Johansson’ sein. Sortieren Sie die Ausgabe alphabetisch nach dem Nachnamen.
7. Welche Nutzer sind Mitglied in allen Foren, in denen auch ‘Mehmet Koksal’ Mitglied ist (Angabe Name)?
8. Geben Sie die prozentuale Verteilung der Nutzer bzgl. ihrer Herkunft aus verschiedenen Kontinenten an!
9. Zu welchen Themen (‘tag classes’) gibt es die meisten Posts? Geben Sie die Namen der Top 10 ‘tag classes’ mit ihrer Häufigkeit aus!
10. Welche Personen haben noch nie ein “Like” für einen Kommentar oder Post bekommen? Sortieren Sie die Ausgabe alphabetisch nach dem Nachnamen.
11. Welche Foren enthalten mehr Posts als die durchschnittliche Anzahl von Posts in Foren (Ausgabe alphabetisch sortiert nach Forumtitel)?
12. Welche Personen sind mit der Person befreundet, die die meisten Likes auf einen Post bekommen hat? Sortieren Sie die Ausgabe alphabetisch nach dem Nachnamen.
13. Welche Personen sind direkt oder indirekt mit ‘Jun Hu’ (id 94) verbunden (befreundet)? Geben Sie für jede Person die Distanz zu Jun an.
14. Erweitern Sie die Anfrage zu Aufgabe 13 indem Sie zusätzlich zur Distanz den Pfad zwischen den Nutzern ausgeben.

2c. Änderungen in der erzeugten Datenbank

Es soll ein Mechanismus umgesetzt werden, um die Beendigung eines Arbeitsverhältnisses zu dokumentieren. Der entsprechende Eintrag in person_workAt_company soll mittels SQL-Anweisung gelöscht werden. Um die Datenmanipulation nachvollziehen zu können, soll der Löschvorgang in einer separaten Tabelle protokolliert werden. Dabei soll zusätzlich hinterlegt werden, wann das Arbeitsverhältnis beendet wurde (orientieren Sie sich am Löschzeitpunkt). Die Protokollierung soll automatisch erfolgen, wenn ein Mitarbeiter sein Arbeitsverhältnis bei einem Unternehmen beendet (Löschung in person_workAt_company).

Abgabe vor dem Testat

Für Teil 2a

• Eine Datei, die die SQL-Anweisung für die Sichtdefinition enthält.

Für Teil 2b

• Eine Datei, die die SQL-Anfragen, Ergebnisdatensätze (ggf. gekürzt) und die jeweilige Anzahl der Ergebnistupel enthält. Die Anfragen sind zu nummerieren und gut lesbar zu formatieren.

Für Teil 2c

• Eine Datei, mit den entsprechenden SQL-Statements. Der Protokollierungsmechanismus wird während des Testats ausgeführt und getestet.