Aufgaben

Praktikum zur Vorlesung Datenbanken
BAI, Komedia, ISE CSCE/CE,
Mathematik/Technomathematik und Lehramt
Informatik
Dipl.-Inform. Vu Tran,
Ioannis Karatassis, B.Sc.,
Matthias Brzezinksi
Wintersemester 2014/2015
Datum
Team (Account)
Passwort
Aktuelle Informationen, Ansprechpartner, Materialien und Uploads unter:
http://www.is.inf.uni-due.de/courses/db_ws14/
v1.41
1
Inhaltsverzeichnis
0 Block 0: Einführung in Linux
3
1 Block 1: Modellierung einer Datenbank für ein Soziales Netzwerk
4
2 Block 2: SQL mit DB2
5
3 Block 3: Implementierung eines Sozialen Netzwerks
als Webanwendung
8
4 Beschreibung der Geodatenbank
9
2
0
Block 0: Einführung in Linux
keine Abgabe
1: Einrichten der Arbeitsumgebung (0 Punkte) Bereitet zunächst Eure
Arbeitsumgebung vor, so dass Ihr in den nächsten Wochen damit komfortabel arbeiten könnt. Die meisten Einstellungen können direkt über das Menü in
der rechten oberen Ecke (eine Sprechblase mit Eurem Login-Namen) und dann
Systemeinstellungen vorgenommen werden.
Abgabe:
• Keine.
2: Linux-Umgebung (0 Punkte) Zur Eingewöhnung in die Arbeitsumgebung werden einige vom Praktikumsleiter vorgegebene, einfache Aufgaben bearbeitet.
Am Ende der Aufgabe solltet Ihr in der Lage sein, mit der Linux-Shell Verzeichnisse zu navigieren, Verzeichnisse anzuzeigen und Dateien anzusehen. Ihr solltet
wissen, wo Ihr einen Text-Editor, Dateibrowser oder Webbrowser aufrufen könnt
und wie Ihr aus einer Textdatei ein PDF erzeugt.
Abgabe:
• Keine.
3
1
Block 1: Modellierung einer Datenbank für ein
Soziales Netzwerk
Abgabe bis 1.12.2014, 7.59 Uhr
3: Datenbank-Modellierung (6 Punkte) In diesem Semester soll ein soziales Netzwerk entwickelt werden. Dazu sollte als erstes die entsprechende Datenbank modelliert werden.
Zentral in unserem Modell sind dabei die Benutzer, die einen Vor- und einen
Nachnamen haben, ein Profil-Bild, ein Passwort, eine Selbstbeschreibung und
eine E-Mail-Adresse. Benutzer können einer Datenschutzerklärung zugestimmt
haben.
Der Login-Name eines Benutzers soll änderbar sein (wie bei Twitter).
Benutzer können Freunde von beliebig vielen anderen Benutzern sein.
Freundschftsbeziehungen sind dabei nicht unbedingt symmetrisch – Wenn Benutzer A also „Freund“ von B ist, muss B nicht automatisch Freund von A sein.
Benutzer können Status-Nachrichten veröffentlichen, deren komplette Historie zusammen mit dem Zeitpunkt des Veröffentlichens gespeichert wird. Diese
Status-Nachrichten sind für alle Freunde bestimmt.
Direkt-Nachrichten können von einem Benutzer an einen anderen Benutzer gesendet werden. Direkt-Nachrichten bestehen aus einem Text, einem Betreff und
einem Datum.
Benutzer haben eine „Wall“, auf die andere Benutzer Mitteilungen setzen können.
Benutzer können Gruppen gründen, die einen Namen haben und eine Beschreibung. Bereits existierenden Gruppen können Benutzer beitreten.
Benutzer können Dinge mögen, die einen Namen haben und evtl. eine URL.
Abgabe:
• ER-Diagramm
4: CREATE TABLE-Statements (4 Punkte) Gebt für die in Aufgabe 3
entworfene Datenbank entsprechende CREATE TABLE-Statements an, die die
Datenbank-Tabellen in der DB2 anlegen, incl. Integritätsbedingungen und einem
Trigger, der bei Aktualisierung eines Profilbildes automatisch ein Statusupdate
für den entsprechenden Benutzer erstellt.
Abgabe:
• CREATE TABLE-Statements
4
2
Block 2: SQL mit DB2
Abgabe bis 2.2.2015, 7.59 Uhr
Für die folgenden Aufgaben steht eine Beschreibung der verwendeten Datenbank
im Anhang 4 zur Verfügung.
5: Katalogisieren (1 Punkt) In diesem Praktikum werden wir die DB2 aus
der Kommandozeile heraus ansprechen. Es gibt auch grafische, Java-basierte
Werkzeuge, auf die wir jedoch hier verzichten.
Macht Euch kurz mit den unterschiedlichen Modi vertraut, die in den Unterlagen
beschrieben werden. Katalogisiert dann die Instanz dbmaster auf dem Rechner
bijou.is.inf.uni-due.de mit Server-Port 50005.
Katalogisiert die Datenbank mondial lokal unter einem Alias.
Lasst Euch das Node Directory und das Database Directory anzeigen.
Abgabe:
• Einträge für Instanz und Datenbank
6: Sichten (1 Punkt)
(a) Erstellt eine Sicht dbpXX1 .landmarks aller Landmarken (Berge und Ästuare) mit ihrem Typ (mountain, estuary) und den Ländern, in denen sie
sich befinden. Die Sicht soll die drei Attribute name, type und country
enthalten. Dabei soll in name der Name der Landmarke und in country
der Name des Landes (nicht das Kürzel!) enthalten sein.
(b) Erstellt aus der Tabelle borders eine Sicht, die eine symmetrische Grenzrelation enthält. D.h. zu Ländern A und B, die aneinander grenzen, soll
die Tabelle zwei Tupel (A,B,Länge) und (B,A,Länge) enthalten. A und B
sind dabei die Kürzel (Schlüssel) der Länder.
Abgabe:
• SQL-Befehle
7: Verwendung von Sichten (2 Punkte)
(a) Benutzt die Sicht dbpXX.landmarks, um zu jedem Land die Anzahl von
Landmarken auszugeben.
(b) Benutzt die Sicht dbpXX.borders, um für alle Länder die Gesamtlänge
ihrer Grenzen auszugeben.
Abgabe:
• SQL-Befehle
1 Dabei
steht „XX“ für Eure Gruppenummer
5
8: SQL-Anfragen (8 Punkte) Formuliert folgende Anfragen in SQL:
(a) Ermittelt zu allen Meeren die Gesamtfläche aller Inseln, die in jeweils
einem Meer liegen.
(b) Ermittelt zu jedem Land die dort am häufigsten gesprochene Sprache und
gebt Landesname (nicht Kürzel!), Sprache und Prozentanteil der Sprache
aus.
(c) Ermittelt alle Organisationen, in denen alle Länder mit Wüsten Mitglied
sind (nicht: alle Mitgliedsländer Wüsten haben).
(d) Ermittelt alle Paare von Ländern mit mindestens 10 Provinzen, die die
gleiche Zahl von Provinzen haben. Jedes Länderpaar soll nur einmal ausgegeben werden.
(e) Ermittelt zu jedem Land in Europa den längsten Fluss, der durch das
Land fließt (wenn es überhaupt einen solchen Fluss gibt). Die Ausgabe
soll nach Länge des Flusses sortiert werden.
(f) Berechnet den prozentualen Anteil der Angehörigen jeder ethnischen
Gruppe an der Weltbevölkerung.
Tip: Wenn man zwei Ganzzahlen (Integer) durcheinander teilt, so erhält
man als Ergebnis eine Ganzzahl, daher sollten die Operanden für die Berechnung von Prozentanteilen zunächst in einen anderen Datentyp gewandelt werden.
Tip: Beim Aufsummieren oder Durchschnittbilden von großen Ganzzahlen
kann es leicht zum Überlauf kommen. Daher gegebenfalls vorher in einen
anderen Datentyp wandeln.
(g) Die in der Datenbank aufgeführten Städte seien alle existierenden Städte.
Berechnet den Anteil der städtischen Bevölkerung an der Gesamtbevölkerung für die Länder Europas.
(h) Bestimme alle Organisationen, die auf jedem Kontinent mindestens ein
Mitgliedsland besitzen (die Art der Mitgliedschaft soll keine Rolle spielen).
Abgabe:
• SQL-Befehle
9: rekursive Anfragen (1 Punkt)
(a) Ermittelt mit Hilfe einer rekursiven Anfrage alle Zuflüsse des Rheins direkte und indirekte) und berechnet die Gesamtlänge des Flussnetzes. Gesucht
ist also eine einzige SQL-Abfrage, die die Gesamtlänge des Flussnetzes wie
oben beschrieben ausgibt.
Abgabe:
• SQL-Befehl
(Bitte umblättern)
6
10: Trigger (2 Punkte) Erstellt und füllt zunächst eine Tabelle
dbpXX.orgacount (ersetzt XX wieder durch Eure Gruppennummer):
CREATE TABLE dbpXX.orgacount (
orga VARCHAR(20),
countrycount INTEGER
)
INSERT INTO dbpXX.orgacount
SELECT o.abbreviation, COUNT(m.organization)
FROM organization o JOIN ismember m
ON o.abbreviation=m.organization
GROUP BY o.abbreviation
Schreibt nun zwei Trigger mit dem
dbpXX.trigger2, die folgendes leisten:
Namen
dbpXX.trigger1
und
• wenn neue Mitgliedschaften in die Tabelle dbpXX.ismember eingefügt werden, soll das Attribut countrycount für entsprechenden Organisationen
erhöht werden
• wenn Mitgliedschaften aus der Tabelle dbpXX.ismember gelöscht werden,
soll das Attribut countrycount für die entsprechenden Organisationen
gesenkt werden
Hinweis: Die Tabellen dbpXX.city und dbpXX.ismember müssen vorher aus dem
Schema dbmaster in das eigene Schema kopiert werden.
Abgabe:
• SQL-Befehle
7
3
Block 3: Implementierung eines Sozialen Netzwerks
als Webanwendung
Abnahme im Zeitraum 2.2.2014 bis 6.2.2014
11: Korrektur des Entwurfs (0 Punkte) Wenn Ihr die Korrekturen zu
eurem ER-Modell erhalten habt, überarbeitet euren Entwurf und beseitigt evtl.
Fehler und Probleme. Alternativ könnt ihr auch die Musterlösung benutzen, die
auf der Veranstaltungs-Webseite veröffentlicht wird.
Abgabe:
• Keine.
12: Implementierung (20 Punkte) Wenn Ihr ggf. euer ER-Modell korrigiert habt, entwickelt eine Web-Anwendung (z.B. in Java), die die folgenden
Anforderungen umsetzt:
• Einfaches Hinzufügen von Nutzern über eine Registrierungs-Seite ohne
E-Mail-Bestätigung etc. (1 Punkt)
• Benutzer können sich einloggen und gelangen dann auf ihre Profilseite (1
Punkt)
• Benutzer-Profilseite incl. (9 Punkte)
– Profildaten (Name, Vorname, Selbstbeschreibung)
– letztes eigenes Status-Update
– navigierbare Liste der Freunde des Benutzers (ein Klick auf einen
Freund führt zur Profilseite des Freundes)
– navigierbare Liste der Gruppen, in der der Benutzer Mitglied ist (ein
Klick auf eine Gruppe führt auf eine navigierbare Seite mit den Mitgliedern der Gruppe)
– Sachen, die der Benutzer mag
– Liste der letzten 10 Status-Updates der Freunde des Benutzers
• Navigierbare Liste der Mitglieder einer Gruppe (2 Punkte)
• Veröffentlichen von Status-Updates (2 Punkte)
Auf korrekte Verwendung von Transaktionen, das Schließen von DB-Resourcen
und die Verhinderung von SQL-Injection ist zu achten.
Von folgenden Features sollen zwei implementiert werden (5 Punkte):
• Hochladen eines Profil-Bildes
• Versenden von Direkt-Nachrichten an einen Freund
• Änderung des Login-Namens
8
• Eine Wall. Die Wall soll eine Möglichkeit für andere Benutzer sein, öffentliche Mitteilungen an diesen Benutzer zu veröffentlichen. Die Wall enthält
also eine Liste dieser Mitteilungen und eine Möglichkeit zum Hinzufügen
einer neuen Mitteilung.
• Möglichkeit für den Benutzer, Dinge einzugeben, die er mag
Folgende Anforderungen sind dabei zu beachten:
• Verwendung der DB22
• korrekte Verwendung von Transaktionen
• Schließen von DB-Ressourcen an sinnvollen Stellen
• Verhinderung von SQL-Injection
Abgabe:
• Keine. Die Webapp wird bei einem separaten Abnahme-Termin zusammen
mit den Veranstaltern vorgestellt und überprüft (s.u.)
Beim Abnahmetermin gibt es zwei Möglichkeiten:
(a) Ihr könnt Eure Lösung abnehmen lassen, indem Ihr sie auf Eurem eigenen Laptop etc. vorführt. Dazu bringt Ihr das Gerät zum Termin mit und
denkt bitte an Netzteil und/oder an genug Akkuladung. Testet die Vorführung bitte vorher – u.U. ist es in unserem Büro schwierig, eine Netzwerkverbindung zu bekommen. Falls Ihr definitiv eine benötigt, schickt
uns bitte vor dem Termin die Kabel-MAC-Adresse Eures Rechners per
Mail.
(b) Ihr könnt Eure Lösung auch auf einem Rechner im Pool vorführen. In
diesem Fall treffen wir uns zur Abnahme auch in unserem Büro im Erdgeschoss, aber verbinden uns per Internet auf den Rechner im Pool.
In beiden Fällen werden wir uns sowohl die Funktionalität des Programms angucken als auch den Quellcode!
4
Beschreibung der Geodatenbank
Für die SQL-Aufgaben ist eine Beispieldatenbank mit geographischen Daten
vorgegeben. Diese Datenbank mondial solltet Ihr in der ersten Woche unter
einem lokalen Alias katalogisiert haben.
Der relevante Ausschnitt des Schemas der Datenbank ist im Anschluss beschrieben. Ihr könnt den Aufbau der Tabellen auch direkt von der Datenbank erfragen.
Die Daten der Tabellen geben teilweise kein vollständiges Bild der Welt wieder,
sollen aber für unsere Zwecke genügen.
Country: Länder
name: der Name des Landes
2 Mit
anderen Worten: andere Datenbanken, die nicht die IBM DB2 sind, sind nicht zugelassen und führen bei Verwendung zum Nichtbestehen.
9
code: der Ländercode (Kfz) des Landes
capital: der Name der Hauptstadt
province: der Name der Hauptstadt-Provinz
area: die Fläche des Landes
population: die Bevölkerungszahl
Economy: Wirtschaftsdaten
country: der Ländercode (Kfz) des Landes
gdp: Bruttoinlandsprodukt in Mio. US-Dollar
Language: gesprochene Sprachen
country: der Ländercode
name: Name einer im Land gesprochenen Sprache
percentage: Anteil der Sprache als Muttersprache im Land
Religion: ausgeübte Religionen
country: der Ländercode
name: Name einer im Land ausgeübten Religion
percentage: Anteil der Religion an der Bevölkerung des Landes
borders: in dieser Beziehung ist für ein Paar benachbarter Länder A und B
nur ein Tupel angegeben, die Relation ist nicht symmetrisch
country1: der Ländercode des ersten Landes
country2: der Ländercode des angrenzenden Landes
length: Länge der Grenze zwischen country1 und country2
Continent: Kontinente
name: der Name des Kontinents
area: die Gesamtfläche des Kontinents
encompasses: Lage von Ländern
country: der Ländercode
continent: der Name des Kontinents, auf dem das Land liegt
percentage: wie viel Prozent der Landesfläche auf dem Kontinent liegt
City: Städte
name: der Name der Stadt
country: der Ländercode des Landes, in dem die Stadt liegt
province: die Provinz, in der die Stadt liegt
population: Bevölkerung der Stadt
longitude: geographischer Längengrad
10
latitude: geographischer Breitengrad
Organization: internationale Organisationen
name: der Name der internationalen Organisation
abbreviation: das Kürzel der Organisation
city: der Sitz der Organisation
country: der Ländercode des Landes, in dem der Sitz liegt
province: die Provinz, in der der Sitz liegt
established: Gründungsdatum
ismember: Mitgliedschaften
organization: das Kürzel der Organisation
country: der Ländercode des Mitgliedslandes
type: die Art der Mitgliedschaft
Mountain: Berge
name: der Name des Bergs
mountains: der Name des Gebirges, in dem der Berg liegt
height: die Höhe des Bergs
type: die Art des Bergs
longitude: geographischer Längengrad
latitude: geographischer Breitengrad
Lake: Seen
name: der Name des Sees
area: die Fläche des Sees
River: Flüsse
name: der Name des Flusses
length: die Länge des Flusses
river: der Name des Flusses, in den der Fluss mündet
lake: der Name des Sees, in den der Fluss mündet
sea: der Name des Meeres, in den der Fluss mündet
Sea: Meere
name: der Name des Meeres
depth: die maximale Tiefe des Meeres
located: Lage von Städten (einzelne der Attribute sind in dieser Tabelle oft
NULL, wenn eine Stadt z.B. nicht an einem Fluss liegt)
city: Name der Stadt, die an dem Gewässer liegt
11
country: Name des Landes, in dem die Stadt liegt
province: Name der Provinz, in der die Stadt liegt
river: Name des Flusses, an dem die Stadt liegt
lake: Name des Sees, an dem die Stadt liegt
sea: Name des Meeres, an dem die Stadt liegt
geo_sea, geo_lake, geo_river: Lage von Meeren, Seen und Flüssen
sea, lake, river: Name des Gewässers
country: Name des Landes, an den das Gewässer grenzt
province: Name der Provinz, an die das Gewässer grenzt
geo_mountain: Lage von Bergen
mountain: Name des Bergs
country: Name des Landes, in dem der Berg liegt
province: Name der Provinz, in dem der Berg liegt
12