Aufgabe 1: CREATE TABLE (10P)

Abgabe-Deadline: 08.12.2020 12:00 im Moodle

Erstellen Sie CREATE TABLE-Befehle, um das folgende ER-Diagramm eines sozialen Netzwerkes in Datenbanktabellen zu überführen. Verwenden Sie zur Umsetzung der Generalisierung die vertikale Partitionierung. Achten Sie auf Primärschlüssel, Fremdschlüssel, UNIQUE-, CHECK- und NOT NULL-Constraints sowie DEFAULT-Werte und wählen Sie geeignete Datentypen. Das eingesetzte DBMS soll PostgreSQL sein.

[[ { _e: "Beitrag", pos: [110, 100], attributes: [ { _a:"BeitragsID", options:["primary"], pos: [15, 10] }, { _a:"gepostet_am", pos: [130, 10] }, { _a:"Beitragstext", pos: [250, 10] } ] }, { _e: "Sponsored_Post", pos: [10, 250], isa: {_e:"Beitrag"}, attributes: [ {_a:"Tagesbudget", pos: [35, 334]} ]}, { _e: "Foto", pos: [200, 250], isa: {_e:"Beitrag"}, attributes: [ { _a:"Dateiname", pos: [225, 334]} ]}, { _e: "Benutzer", pos: [470, 100], attributes: [ { _a:"Benutzernr", options:["primary"], pos: [410, 10] }, { _a:"Name", pos: [520, 10] } ] } ], [ { _r: "postet", _e: ["Beitrag", "Benutzer"], card: ["N", "1"], attributes: [ ] }, { _r: "verlinkt", pos: [505, 240], _e: ["Foto", "Benutzer"], card: ["N", "M"], attributes: [{_a:"pos_x", pos: [410, 334]}, {_a:"pos_y", pos: [520, 334]}] } ]]

Hinweise siehe nächste Seite.

Hinweise:

gepostet_am soll Datum und Uhrzeit beinhalten, wann der Beitrag gepostet wurde
Sponsored_Posts sind Werbebeiträge, die von Benutzern gepostet werden können. Für diese wird ein Tagesbudget in Euro und Cent festgelegt, welches bestimmt, wie oft der Beitrag auf den Profilen anderer Nutzer angezeigt wird. Das Mindestbudget beträgt 1 EUR pro Tag. Dies ist zugleich aus das Standard-Budget, wenn keins angegeben wird.
pos_x und pos_y beschreiben die Position auf dem Foto, an der sich die verlinkte Person befindet. Diese Zahlen haben keine Nachkommastellen. Sie sind optional.
Der Dateiname eines Fotos ist genau 32 Zeichen lang.
Die BeitragsID soll automatisch generiert werden.

Sie können das Schema entweder auf http://sqlfiddle.com/ erstellen (wählen Sie dort oben PostgreSQL 9.6 aus) oder sich mit der PostgreSQL der Hochschule (siehe Aufgabe 2) verbinden.

Aufgabe 2: SQL-Anfragen in PostgreSQL

Wichtig: Um außerhalb des Hochschulnetzwerks auf die PostgreSQL-Datenbank der OTH Regensburg zuzugreifen, müssen Sie eine VPN-Verbindung aufbauen (siehe https://www.oth-regensburg.de/supportwiki/doku.php?id=public:netz:vpn-forticlient).

Installieren Sie den SQL-Client DBeaver (https://dbeaver.io/) oder einen anderen Datenbank-Client und richten Sie dort eine Verbindung zu der folgenden PostgreSQL-Datenbank ein:

Host: hera.hs-regensburg.de
Datenbank: Ihr Hochschul-Kürzel (z. B. xyz12345)
Benutzer: Ihr Hochschul-Kürzel
Passwort: Ihr Hochschul-Kürzel
Schema: public (können Sie auswählen, nachdem Sie sich verbunden haben)

Da das Passwort gleich Ihrem Benutzernamen ist, wird empfohlen, es mit ALTER USER ihr_benutzername PASSWORD 'neues_passwort'; zu ändern.

Formulieren Sie diese Anfragen mit SQL:

1.	Legen Sie die Tabellen aus Aufgabe 1 an.
2.	Fügen Sie zwei Benutzer ein: Benutzernummer 1, Name Olivia und Benutzernummer 2, Name Yvonne.
3.	Geben Sie die Benutzertabelle vollständig aus.
4.	Ändern Sie den Namen von Benutzer 1 auf Olivia von Opel.
5.	Wie viele Benutzer gibt es?
6.	Welche Benutzer haben ein ' von ' im Namen?
7.	Löschen Sie Benutzer 1.

Aufgabe 3: SELECT-Anfragen

Auf der Webseite des Datenbankmanagementsystems Hyper können Sie SQL-Anfragen gegen das Uni-Schema aus dem Kemper-Datenbanksysteme-Buch stellen: http://hyper-db.de/interface.html

Professoren(PersNr, Name, Rang, Raum)
Studenten(MatrNr, Name, Semester)
Vorlesungen(VorlNr, Titel, SWS, gelesenVon)
hoeren(MatrNr, VorlNr)

Vorlesungen.gelesen_von ist Fremdschlüssel auf Professoren.PersNr
hoeren.MatrNr ist Fremdschlüssel auf Studenten.MatrNr
hoeren.VorlNr ist Fremdschlüssel auf Vorlesungen.VorlNr

Formulieren Sie die folgenden Anfragen mittels SQL. Verwenden Sie bitte nicht das Relax-Tool. Verwenden Sie bei den Fragen 1 bis 4 keine Unteranfragen oder CTEs. Richtige Antworten geben 2 Punkte. Für teilweise richtige Antworten können leider keine Teilpunkte gegeben werden, da ein automatisches Query-Bewertungssystem eingesetzt wird. Achten Sie daher auch darauf, dass keine Tippfehler in Ihren Anfragen sind.

1.	Wie viele Professoren, die den Rang C4 haben, gibt es? (2P)
2.	Geben Sie die Namen der Studierenden aus, die die Vorlesung "Grundzüge" hören, und zwar alphabetisch aufsteigend sortiert. (2P)
3.	Geben zu jedem Professor, der mindestens zwei Vorlesungen hält, den Namen sowie die Gesamt-SWS-Zahl der von ihm oder ihr gehaltenen Vorlesungen aus. (3P)
4.	Wie sind die Namen der Studierenden, die zusammen mit dem Studenten namens "Feuerbach" zumindest eine gemeinsame Vorlesung hören? Geben Sie keinen Namen doppelt aus. (3P)
5.	Welche Professoren haben den Rang C4 und halten keine einzige 4-SWS-Vorlesung.

Aufgabe 0: SQL Island (Bonusaufgabe, 10P)

Aufgabe 1: CREATE TABLE (10P)

Aufgabe 2: SQL-Anfragen in PostgreSQL

Aufgabe 3: SELECT-Anfragen