Datenbanken mit InstaHub

Datenbank, Datenbanken, InstaHub

InstaHub ist ein soziales Netzwerk, welches im Unterricht eingesetzt wird, um die Themen Datenbanken, Umgang mit sozialen Netzen und Datenschutz zu lehren.¹⁾ Es wird ein soziales Netzwerk simuliert, in dem du die administrative Rolle einnimmst. Wir lassen also die theoretische Entity-Relationship-Modellierung hinter uns und wollen nun praktisch arbeiten.

Erstelle dir dein eigenes soziales Netzwerk, auch Hub genannt, auf InstaHub:

Informiere dich über den Benutzername deiner Informatiklehrkraft im Lernplan auf LernSax. Diesen brauchst du, damit dein Hub der korrekten Lehrkraft zugeordnet wird.
Folge den Anweisungen der Videoanleitung.
Gedulde dich bis der Hub durch deine Lehrkraft freigeschaltet wurde. Dies geschieht manuell und kann ein wenig dauern.

Solltest du dir bereits im Unterricht einen Account angelegt haben, so erstelle dir nun einen neuen. Notiere dir deine Zugangsdaten und die Adresse des Hubs genau.

Nun sollst du dich mit der grundlegenden Bedienung des sozialen Netzwerks vertraut machen.

Logge dich hierfür mit den dir bekannten Zugangsdaten aus Aufgabe 1 in deinen eigenen Hub ein.
Suche die Nutzerin stella439 und like ihr Drohnenbild.
Füge unter dem Bild auch einen Kommentar hinzu, indem du auf die „Sprechblase“ drückst.

Lösung: So sieht die Seite von stella439 aus.

Du gelangst auf die Seite von stella439, indem du ihren Namen in die Suche eingibst.

SQL ist eine Datenbanksprache zum Verwalten und Auswerten einer Datenbasis. Wir wollen sie erlernen, um an erweiterte Informationen unseres Hubs zu kommen.

Um einen ersten Einblick zu erlangen, schaue dir das Video zu SQL-Abfragen an. Achte dabei besonders auf die Struktur von SQL-Abfragen.

Groß- und Kleinschreibung ist bei SQL-Abfragen unwichtig. Gewöhne dir dennoch an SQL-Befehle GROß zu schreiben.

Das Ausführen des SQL-Befehls

SELECT id, username, name, city 
FROM users;

liefert die folgende Ergebnistabelle: Sie zeigt einen Ausschnitt aus der Entitymenge users mit den gewählten Attributen id, username, name, city. Zu jeder Entität sind zu den verschiedenen Eigenschaften nun auch Attributwerte eingetragen. Diese Informationen konnten den bisherigen ER-Diagrammen nicht entnommen werden.

SQL-Abfragen sind stets gleich aufgebaut. Sie beginnen mit einer Auswahl von Attributen, gefolgt von Tabellennamen und gegebenenfalls weiterer Einschränkungen oder Bedingungen.

SELECT attribut1, attribut2, ...
FROM tabelle
WHERE bedingung
ORDER BY attribut;

Notiere dir in deinem digitalen Hefter die allgemeine Struktur von SQL-Abfragen.

Löse folgende Aufgaben und schreibe dir die SQL-Befehle ebenfalls in deinen digitalen Hefter. Kontrolliere die Anzahl der Ergebnisse mit der Vorgabe in Klammern. Diese Angaben gelten nur, wenn du bisher keine weiteren Änderungen an der Datenbasis vorgenommen hast.

Zeige alle Attribute von users. (204)
Erstelle eine Übersicht von users bestehend aus id und dem Echtnamen (name). (204)
Gib die Anzahl an Fotos an, die in deinem Hub gespeichert sind. (1522)
Zeige eine Tabelle, die die Städte der Benutzer*innen ausgibt. (204)
Manche Städte erscheinen mehrfach: Aus wie vielen verschiedenen Städten kommen die Nutzer*innen des Hubs? Informiere dich hierzu zum SQL-Befehl DISTINCT. (111)
Welche der Städte ist bei alphabetischer Sortierung an erster Stelle? Informiere dich hierzu zum SQL-Befehl ORDER BY. (Albertshofen)
Erstelle eine Liste, die Benutzername und dazugehörige Stadt zeigt. Diese Übersicht soll absteigend nach Stadt sortiert sein. (204)
Überprüfe, dass jeder Benutzer nur aus zwei Geschlechtern wählen konnte. (2)
Ermittle den Namen der kleinsten Person des Hubs. (Marie Bohm)
Ermittle die Namen der größten Personen des Hubs. Ordne diese aufsteigend nach Namen (name). (Fabian Probst, …, Yves Bach)

Lösung

```
SELECT * 
FROM users;
```
```
SELECT id, name 
FROM users;
```
```
SELECT * 
FROM photos;
```
```
SELECT city 
FROM users;
```
```
SELECT DISTINCT city 
FROM users;
```
```
SELECT city 
FROM users 
ORDER BY city;
```

SELECT username, city 
FROM users 
ORDER BY city DESC;

```
SELECT DISTINCT gender 
FROM users;
```

SELECT name, centimeters 
FROM users 
ORDER BY centimeters;

SELECT name, centimeters 
FROM users
ORDER BY centimeters DESC, name;

Bei den bisherigen Aufgaben genügte jeweils das Filtern von Daten in einer Tabelle - sogenannte einfache Abfragen. Nun soll es schrittweise schwieriger werden. Notiere dir neue Befehle und dazugehörige Beispiele in den Hefter. Los geht's:

Gib aus, wie viele Mitglieder in Berlin wohnen. (14) Erweitere hierzu die SQL-Befehle um WHERE und nutze die Bedingung city=„Berlin“. Zusatztipp: Um eine elegantere Ausgabe zu erzeugen, nutzen wir in der Sekundarstufe II den SQL-Befehl COUNT().
Ersetze in der vorhergehenden Abfrage „=“ zu „!=“. Beschreibe, welche Änderung dies bewirkt. Betrachte dazu die Ausgabe der dazugehörigen Abfrage. (190)
Ermittle alle Städte, die mit B beginnen. Informiere dich hier zum SQL-Befehl LIKE und den Platzhalter % sowie _. (31)
Erstelle eine Übersicht aller Mitglieder, deren Namen auf „mann“ endet. Ordne diese in alphabetisch aufsteigender Reihenfolge. (15)
Frauen sind in Deutschland im Mittel 166 cm groß. Gib die Namen aller Nutzerinnen aus, die unterdurchschnittlich groß sind. (69) Um mehrere Bedingungen zu verknüpfen, informiere dich über die SQL-Befehle AND und OR.
Männer sind in Deutschland im Mittel 180 cm groß. Gib die Namen aller Nutzer aus, die unterdurchschnittlich groß sind. (57)
Verbinde die beiden vorhergehenden Abfragen und erzeuge so eine Liste mit allen „kleinen“ Nutzer*innen. (126) Tipp: Klammern setzen.
Finde heraus, welche Fotos die Nutzerin „stella439“ kommentiert hat. Du kannst es Schritt für Schritt in ggf. mehreren Abfragen erledigen oder du gestaltest die Abfrage so geschickt, dass du sie in einer bündelst. Tipp: Du brauchst users und comments und solltest wissen, dass users.id und comments.user_id gleich sind. Hast du die Punktnotation bemerkt?
Anna teilt ihrem Freund das nächste Urlaubsziel verschlüsselt mit: Die Initialien der Nutzerin, die mit „Kann man das Photo irgendwo kaufen?“ kommentiert, weisen auf das Land unseres nächsten Urlaubs hin. Finde heraus, wohin die Reise geht.

Lösung

SELECT * 
FROM users 
WHERE city = "Berlin";

SELECT * 
FROM users 
WHERE city != "Berlin";
-- "!=" steht für ungleich. Folglich werden alle Mitglieder*innen ausgegeben, die nicht in Berlin wohnen.

SELECT * 
FROM users 
WHERE city LIKE "B%";
-- % ist ein Platzhalter für beliebig viele Zeichen.
-- _ ist ein Platzhalter für genau ein Zeichen.

SELECT * 
FROM users 
WHERE name LIKE "%mann"
ORDER BY name ASC;

SELECT name 
FROM users 
WHERE centimeters < 166 AND gender = "female";
-- Durch die Und-Verknüpfung werden nur Dateneinträge gezeigt, die gleichzeitig beide Bedingungen erfüllen. Bei jedem Eintrag gilt also, dass die Person kleiner als 166 cm und weiblich ist.

SELECT name 
FROM users 
WHERE centimeters < 180 AND gender = "male";

SELECT name 
FROM users 
WHERE (centimeters < 166 AND gender = "female") OR (centimeters < 180 AND gender = "male");
-- Die Klammerung bewirkt, dass beide Bedingungen getrennt voneinander bearbeitet werden. Durch die Oder-Verknüpfung werden dann alle Datensätze gelistet, bei denen die 1., die 2. oder beide Bedingungen erfüllt sind.

SELECT * 
FROM users, comments 
WHERE users.username = "stella439" AND users.id = comments.user_id;
-- Bei dieser Abfrage müssen zwei Tabellen abgefragt werden. Beide Tabellen haben die Nutzer-ID gemeinsam, weshalb gilt: users.id = comments.user_id. Da die Abfrage über mehrere Tabellen geht, müssen die Attribute mittels der Punktnotation der korrekten Tabelle zugeordnet werden. Dies kennst du bereits aus dem KOAM-Modell.

SELECT users.name 
FROM users, comments 
WHERE body LIKE "Kann man das Photo irgendwo kaufen?" AND users.id = comments.user_id AND users.gender = "female";
-- Es erscheint: Greta Bauer mit den Initialien GB. Es geht also in das Vereinigte Königreich.

Mittlerweile hast du einen Einblick in die Datenorganisation deines InstaHubs gewonnen. Aber wie schaut eigentlich das ER-Diagramm zu InstaHub aus?

Erstelle zuletzt das ER-Diagramm deines InstaHubs. Fokussiere dich auf die Zusammenhänge zwischen users, photos, comments, likes und tags. Lasse Attribute außen vor - notiere aber die Entitymengen, Beziehungen und Primärschlüssel.

Lösungsvorschlag

Hier siehst du einen Lösungsvorschlag. Aus einem konkreten Datenbanksystem das ER-Modell zu erstellen, ist keine einfache Aufgabe. Deine Lösung weicht gegebenenfalls ab. Du solltest jedoch nachvollziehen können, wieso auch diese Variante möglich ist und insbesondere die Kardinalitätsangaben begründen können.

¹⁾

https://instahub.org/

Datenbanken mit InstaHub

Eigenen Hub erstellen

Zurechtfinden im Hub

SQL-Abfragen

Übung 1

Übung 2

Mithilfe des Datenbanksystems zum ER-Diagramm