Aufgabenstellung
In diesem Teil wird der erste Schritt der Erstellung einer vollständigen DB-Anwendung durchgeführt, der Datenbank-Entwurf. Dazu ist zunächst ein konzeptionelles Schema zu erstellen.
Folgende Teilaufgaben sind für das Bestehen des ersten Testats zu lösen:
- Überführen Sie das bereitgestellte UML-Diagramm in ein valides Relationenmodell. Benennen Sie alle Relationen, Attribute und Datentypen, die Sie für den Entwurf verwenden. Achten Sie darauf, dass Kindklassen in Vererbungshierarchien (z.B. City-[erbt von]->Place, Post-[erbt von]->Message, Company-[erbt von]->Organization) als eigene Relationen modelliert werden. Entscheiden Sie je nach Kardinalität einer Beziehung, ob es sich um eine 1:1, 1:n oder n:m Beziehung handelt und erzeugen Sie die beteiligten Relationen entsprechend.
- Entwickeln Sie anhand Ihres Relationenmodells ein SQL-Skript (DDL), welches die notwendigen Tabellen und Schlüssel-Constraints (Definieren Sie auch entsprechende Lösch- und Updateregeln für Fremdschlüsselbeziehungen) in PostgreSQL erzeugt. Formulieren Sie zusätzlich Constraints für folgende Attributbedingungen:
- Datentyp entsprechend der Quelldaten (z.B. timestamp fuer ‘creationDate’)
- Der Geburtstag einer Person darf nicht in der Zukunft liegen
- Die Email-Adresse einer Person muss “valide [1]” sein
- Importieren Sie die bereitgestellten CSV-Daten in Ihre Datenbank. Achten Sie darauf, dass die bereitgestellten CSV-Daten nicht dem Zielschema entsprechen. Das Laden der Daten kann auf zwei Wegen erfolgen:
- Variante 1 (Programm): Schreiben Sie ein Ladeprogramm, welches die CSV-Daten einliest, entsprechend Ihrem Zielschema konvertiert und in die Datenbank schreibt.
- Variante 2 (SQL): Nutzen Sie für die Transformation die von der Datenbank bereitgestellten Mittel. Laden Sie die CSV-Daten in temporäre Tabellen via COPY [2] und transformieren Sie die Daten in Ihr Zielschema via SQL.
Darstellung der Lösung
Abzugeben sind:
- Relationenmodell, inklusive aller in der Aufgabenstellung geforderten Angaben
- SQL-DDL Skript zum Erzeugen der Datenbank
- SQL-Skipt / Programm zum Laden der CSV-Daten in die Datenbank
Schema und Daten
- UML-Diagramm [3] der Miniwelt
- Testdatensatz [4] für den Datenimport
Miniweltbeschreibung
Das bereitgestellte UML-Diagramm definiert die Entitäten eines online sozialen Netzwerkes und ihre Beziehungen zueinander. Die bereitgestellten Daten repräsentieren die Aktivität innerhalb des sozialen Netzwerkes in einem festgelegten Zeitraum. Das Schema modelliert Entitäten, wie zum Beispiel Personen, Organisationen und Orte. Außerdem werden Beziehungen, wie zum Beispiel Freundschaften zwischen Personen und die Interaktion zwischen Personen und z.B. Nachrichten / Kommentaren abgebildet.
Entitäten
Entitäten besitzen in den bereitgestellten Daten eine eindeutige Identität. Diese ist zu verwenden für die PS.
Person: Eine Person repräsentiert eine real existierende Person innerhalb des sozialen Netzwerkes. Zur Person werden verschiedene Daten und Beziehungsinformationen abgelegt.Tag(Thema/Konzept): Ein Tag definiert ein gewisses Konzept, welches genutzt wird um zum Beispiel die Interessen von Personen zu definieren oder die Inhalte von Foren und Beiträgen auszuzeichnen.TagClass: Tags sind (evtl. mehreren) Klassen (TagClass) zugeordnet. Die Klassen selbst bilden eine Hierarchie.City(Stadt): Eine Stadt repräsentiert eine real existierende Stadt. Eine Stadt ist genau einem Land zugeordnet.Country(Land): Ein Land repräsentiert ein real existierendes Land. Länder enthalten mehrere Städte und sind Teil eines Kontinents.Continent(Kontinent): Ein Kontinent repräsentiert einen real existierenden Kontinent und ist somit nicht in einem anderen Ort enthalten.Company(Firma): Eine Firma ist ein Ort, an dem eine Person arbeitet. Eine Firma hat einen Hauptsitz in einem Land.University(Universität): Eine Universität ist ein Ort, an dem Personen studieren.Forum: Ein Forum ist ein Treffpunkt, an dem Personen Beiträge und Kommentare verfassen. Foren werden anhand von Tags charakterisiert. Es existieren drei Arten von Foren: Die persönliche “Wall” einer Person, Bilderalben und Gruppen. Die Unterscheidung erfolgt anhand des Forentitels.Post(Beitrag): Eine Nachricht, welche in einem Forum geschrieben wird. Beiträge sind genau einem Forum zugeordnet und können entweder textuellen Inhalt oder ein Bild beinhalten, niemals beides.Comment(Kommentar): Ein Kommentar wird von einer Person zu einer existierenden Nachricht (Message) abgegeben. Diese Nachricht kann entweder ein Post oder ein anderer Kommentar sein.
Beziehungen
Beziehungen verbinden Entitäten unter Verwendung ihrer jeweiligen Identität.
Forum_containerOf_Post: Beschreibt das Enthaltensein eines Beitrages in einem Forum.Message_hasCreator_Person: Definiert die Person, welche eine Nachricht (Post oder Comment) erzeugt hat.Forum_hasMember_Person: Definiert, dass eine Person Mitglied eines Forums ist.Forum_hasModerator_Person: Definiert, dass eine Person der Moderator eines Forums ist.Forum_hasTag_Tag: Definiert das Thema/Konzept eines Forums.Message_hasTag_Tag: Definiert das Thema einer Nachricht (Post oder Comment).Tag_hasType_TagClass: Definiert die Klasse eines Tags.TagClass_isSubclassOf_TagClass: Definiert eine Vererbungshierarchie zwischen Tag-Klassen.Company_isLocatedIn_Country: Legt fest, in welchem Land sich eine Firma befindet.University_isLocatedIn_City: Legt fest, in welcher Stadt sich eine Universität befindet.Person_isLocatedIn_City: Legt fest, in welcher Stadt sich eine Person befindet.Message_isLocatedIn_Country: Das Land, in dem die Nachricht verfasst wurde.Person_knows_Person: Legt fest, dass sich zwei Personen kennen.Person_likes_Message: Definiert, dass eine Person eine Nachricht (Post oder Kommentar) mag.Comment_replyOf_Message: Legt fest, dass ein Kommentar die Antwort auf ein anderes Kommentar oder auf einen Post ist.Person_studyAt_University: Legt fest, an welcher Universität eine Person studiert.Person_workAt_Company: Legt fest, in welcher Firma eine Person arbeitet.