Aufgaben

Praktikum zur Vorlesung Datenbanken
BAI, Komedia, ISE CSCE/CE,
Mathematik/Technomathematik und Lehramt
Informatik
Michael Rist, M.Sc.
Darina Benikova, M.A.
Nikolas Gur, B.Sc.
Wintersemester 2016/2017
Datum
Team (Account)
Passwort
Aktuelle Informationen, Ansprechpartner, Materialien und Uploads unter:
http://www.is.inf.uni-due.de/courses/db_ws15/
v1.1
1
Inhaltsverzeichnis
0 Block 0: Einführung in Linux
3
1 Block 1: Modellierung einer Datenbank für eine Game of
Thrones-Informations-Website (10 Punkte)
4
2 Block 2: SQL mit DB2 (15 Punkte)
5
3 Block 3: Implementierung einer GoT-Informations-Website (20
Punkte)
8
A Spezikation der Website
9
A.1
Beschreibung der Mini-Welt . . . . . . . . . . . . . . . . . . . . .
A.2
Beschreibung der zu erstellenden Website
9
. . . . . . . . . . . . .
10
. . . . . . . . . . . . . . . . . . . . . . . .
10
A.2.1
Die Hauptseite
A.2.2
Detailseite für Personen
A.2.3
Detailseite für Tiere
A.2.4
Detailseite für Häuser
A.2.5
Detailseite für Orte . . . . . . . . . . . . . . . . . . . . . .
16
A.2.6
Detailseite für Episoden . . . . . . . . . . . . . . . . . . .
17
A.2.7
Detailseite für Staeln . . . . . . . . . . . . . . . . . . . .
17
A.2.8
Bewertungen für Detailseiten
. . . . . . . . . . . . . . . .
19
A.2.9
Komplette Listen . . . . . . . . . . . . . . . . . . . . . . .
19
A.2.10 Playlist
. . . . . . . . . . . . . . . . . . .
12
. . . . . . . . . . . . . . . . . . . . .
12
. . . . . . . . . . . . . . . . . . . .
15
. . . . . . . . . . . . . . . . . . . . . . . . . . . .
A.2.11 Neue Playlist anlegen
. . . . . . . . . . . . . . . . . . . .
B Beschreibung der Geodatenbank
19
19
24
B.1
Auistung sämtlicher Tabellen
B.2
Relationales Schema der Datenbank
. . . . . . . . . . . . . . . .
29
B.3
Darstellung von Verknüpfungen zwischen einzelnen Tabellen . . .
31
2
. . . . . . . . . . . . . . . . . . .
24
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 Arbeitsumge-
bung 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 eine Game of Thrones-Informations-Website (10
Punkte)
Abgabe bis 01.12.2016, 23:59 Uhr
3: Datenbank-Modellierung (6 Punkte)
In diesem Semester soll eine
Website für Informationen zu der Serie Game of Thrones entwickelt werden.
Dazu sollte als erstes die entsprechende Datenbank modelliert werden.
Im Anhang dieses Dokuments (siehe Anhang A.1 auf Seite 9) wird die generelle
Funktionsweise der zu erstellenden Website vorgestellt. Es ist dringend anzuraten, vor dem Entwurf neben der Beschreibung der Miniwelt auch die vollständige
Spezikation (siehe Anhang A.2 auf Seite 10) zu lesen und zu verstehen.
Abgabe:
ˆ
ER-Diagramm
4: CREATE TABLE- und Trigger-Statements (4 Punkte)
Aufgabe 3 entworfene Datenbank entsprechende
Gebt für die in
CREATE TABLE-Statements
an,
die die Datenbank-Tabellen in der DB2 anlegen.
Enthalten sein sollen darin
ˆ
sinnvolle Integritätsbedingungen für alle Attribute
ˆ
ein Trigger, der bei Anlegen einer neuen Playlist automatisch alle Trailer
in die Playlist hinzufügt. Also alle Episoden mit der Nummer 0.
Abgabe:
ˆ CREATE TABLE-Statements
und
Trigger-Statement
4
als txt-Datei
2 Block 2: SQL mit DB2 (15 Punkte)
Abgabe bis 23.12.2016, 11.59 Uhr
Für die folgenden Aufgaben steht eine Beschreibung der verwendeten Datenbank
im Anhang B zur Verfügung.
5: Katalogisieren (1 Punkt)
In diesem Praktikum werden wir die DB2 aus
der Kommandozeile heraus ansprechen. Es gibt auch grasche, Java-basierte
Werkzeuge, auf die wir jedoch hier verzichten.
Die Datenbank mit der wir arbeiten werden bendet sich auf einem entfernten Rechner und muss daher in Eurem Rechner importiert werden. Macht
Euch kurz mit den unterschiedlichen Modi vertraut, die in den Unterlagen be-
dbmaster
50005.
schrieben werden. Katalogisiert dann die Instanz
bijou.is.inf.uni-due.de
Katalogisiert die Datenbank
mit Server-Port
mondial
auf dem Rechner
lokal unter einem Alias.
Lasst Euch das Node Directory und das Database Directory anzeigen.
Abgabe:
ˆ
Einträge für Instanz (node) und Datenbank (database)
6: Sichten (1 Punkt)
dbpXXX1 .waters aller Gewässer (Seen und Flüsse) mit
ihrem Typ (sea, river) und den Ländern, in denen sie sich benden. Die
Sicht soll die drei Attribute name, type und country enthalten. Dabei soll
in name der Name des Gewässers und in country der Name des Landes
(a) Erstellt eine Sicht
(nicht das Kürzel!) enthalten sein.
(b) Erstellt aus der Tabelle
borders eine Sicht, die eine symmetrische Grenz-
relation 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
dbpXXX.waters,
um zu jedem Land die Anzahl der Ge-
wässer auszugeben.
(b) Benutzt die Sicht
dbpXXX.borders,
ihrer Grenzen auszugeben.
Abgabe:
ˆ
SQL-Befehle
1 Dabei
steht XXX für Eure Gruppenummer
5
um für alle Länder die Gesamtlänge
8: SQL-Anfragen (8 Punkte)
Formuliert folgende Anfragen in SQL:
(a) Ermittelt zu allen Provinzen die durchschnittliche Höhe aller Berge, die
in der jeweiligen Provinz liegen.
(b) Ermittelt zu jedem Land die dort am häugsten vertretene Religion und
gebt Landesname (nicht Kürzel!), Religion und Prozentanteil der Religion
aus.
(c) Ermittelt alle Länder, in denen alle Hauptstädte der Provinzen des Landes
an einem Fluss liegen.
(d) Ermittelt alle Paare von Ländern, die die gleiche Anzahl an Nachbarländer
haben, also Grenzen zu anderen Ländern. Ermittelt nur die Länderpaare
die mindestens 7 Nachbarländer haben. Jedes Länderpaar soll nur einmal
ausgegeben werden und keine Sichten verwendet werden.
(e) Ermittelt zu jedem Land in Europa die ächenmäÿig gröÿte Insel des Landes (wenn überhaupt eine Insel existiert/vermerkt ist). Die Ausgabe soll
nach Fläche der Insel sortiert werden.
(f ) Berechnet für jede Sprache wie viel Prozent der Weltbevölkerung diese
sprechen.
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) Angenommen die Datenbank enthält alle Städte eines Landes. Berechnet
den Anteil der städtischen Bevölkerung an der Gesamtbevölkerung des
jeweiligen Landes, für alle Länder in Asien.
(h) Bestimme alle Sprachen, die auf jedem Kontinent in mindestens einem
Land gesprochen wird. Der prozentuelle Anteil der Sprache in den Ländern
spielt keine Rolle.
Abgabe:
ˆ
SQL-Befehle
9: rekursive Anfragen (1 Punkt)
(a) Ermittelt mit Hilfe einer rekursiven Anfrage die Gesamtlänge der Zufüsse
und deren Zuüsse in das Kaspische Meer. Gesucht ist also eine einzige
SQL-Abfrage, die die Gesamtlänge des Flussnetzes wie oben beschrieben
ausgibt.
Hinweis: Das Kaspische Meer ist kein Ozean/Meer.
Abgabe:
ˆ
SQL-Befehl
6
10: Trigger (2 Punkte)
dbpXXX.orgacount
Erstellt
und
füllt
zunächst
eine
Tabelle
(ersetzt XXX wieder durch Eure Gruppennummer):
CREATE TABLE dbpXXX.orgacount (
orga VARCHAR(20),
countrycount INTEGER
)
INSERT INTO dbpXXX.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
dbpXXX.trigger2,
ˆ
Trigger
mit
dem
Namen
dbpXXX.trigger1
und
die folgendes leisten:
wenn neue Mitgliedschaften in die Tabelle
werden, soll das Attribut
countrycount
dbpXXX.ismember
eingefügt
für entsprechenden Organisatio-
nen erhöht werden
ˆ
wenn Mitgliedschaften aus der Tabelle
soll das Attribut
countrycount
dbpXXX.ismember gelöscht werden,
für die entsprechenden Organisationen
gesenkt werden
Hinweis: Die Tabellen dbpXXX.city und dbpXXX.ismember müssen vorher aus
dem Schema
dbmaster
in das eigene Schema kopiert werden.
Abgabe:
ˆ
SQL-Befehle
7
3 Block 3: Implementierung einer
Informations-Website (20 Punkte)
GoT-
Fertigstellung bis zum Abnahmetermin (7.2.2017 10.2.2017)
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 Bijou bereitgestellt wurde.
Abgabe:
ˆ
Keine.
12: Implementierung (20 Punkte)
Wenn Ihr ggf. Euer ER-Modell kor-
rigiert habt, entwickelt eine Web-Anwendung (Website) in Java, die die beschriebenen Anforderungen (siehe Beschreibung A.2 im Anhang A) umsetzt.
Die Struktur der Daten (und eventuelle Integritätsbedingungen) ist bereits aus
der Beschreibung der Miniwelt (siehe Anhang A.1) bekannt.
Folgende Anforderungen sind dabei zu beachten:
ˆ
ˆ
ˆ
ˆ
ˆ
Verwendung der DB2
2
Korrekte Verwendung von Transaktionen
Schlieÿen von DB-Ressourcen an sinnvollen Stellen
Verhinderung von SQL-Injection
Befüllen relevanter Tabellen mit einigen Beispieldaten, um die Website
testen zu können
Abgabe:
ˆ
Keine. Die Web-Anwendung wird bei einem separaten Abnahmetermin
zusammen mit den Veranstaltern vorgestellt und überprüft.
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. Zur Not sollte sich im Workspace Eures dbp-Accounts
eine Kopie Eures Endprojekts benden.
(b) Ihr könnt Eure Lösung auch auf einem Rechner im Computerpool vorführen. Dazu sollte sich die Endversion Eures Projekts im Workspace Eures
dbp-Accounts benden.
In beiden Fällen werden wir uns sowohl die Funktionalität des Programms als
auch den Quellcode anschauen! Für jedes Feature, was korrekt implementiert
wurde, werden Teilpunkte vergeben. Daher lohnt sich eine Anmeldung für die
Abnahme in jedem Fall.
8
Anhang
A Spezikation der Website
A.1 Beschreibung der Mini-Welt
ˆ
Alle Figuren haben einen Namen und Herkunftsort.
ˆ
Alle Figuren sind entweder Personen oder Tiere. Für Personen kann der
Titel und eine Biograe hinterlegt werden.
ˆ
Personen können einem oder mehrern Häusern angehören. (Durch Heirat
etc.)
ˆ
Personen können in einem Zeitraum (von Episoden x . . . bis Episoden
y) Mitglied eines Hauses sein. Eine Person die nicht mehr Mitglied eines
Hauses ist kann diesem
nicht
wieder angehören. Zudem ist es nicht ver-
pichtend eine Realtion zu der Tabelle Episoden zu erstellen; der Zeitraum
kann als einfaches Attribut gesehen werden ohne direkte Verbindung zu
einer Episode.
ˆ
Figuren Personen können in Beziehung zueinander Stehen z.B. Schwester,
Ehemann, Feind, etc.
ˆ
Häuser haben einen Name, Motto, Wappen und einen Sitz (Burg).
ˆ
Häuser haben mehrere Orte für einen gewissen Zeitraum (von Episoden x
. . . bis Episoden y) unter ihrer Herrschaft.
ˆ
Alle Burgen haben einen Namen und einen Standort.
ˆ
Es kann nicht sein das ein Ort der Standort für mehrere Burgen ist.
ˆ
Burgen können von verschiedenen Häusern als Sitz angesehen werden.
ˆ
Ein Herkunfts- oder Standort hat einen Namen. Es wird keine Unterscheidung gemacht zwischen einem Stand-, Herkunfts- oder Handlungsort.
ˆ
Jedes Tier kann maximal einen Besitzer (Person) haben.
ˆ
Episoden haben einen Titel, Nummer, Erstausstrahlungsdatum und eine
Inhaltsangabe.
ˆ
Jede Episode hat Figuren, die in ihr vorkommen.
ˆ
Jede Episode hat Handlungsorte, die in ihr vorkommen.
ˆ
Jede Episode muss zu einer Stael gehören.
ˆ
Eine Stael hat mehrere Episoden.
ˆ
Jede Stael hat eine Nummer, Startdatum und Episodenanzahl.
ˆ
Für jede Stael gibt es einen Trailer der als Episode 0 gespeichert wird.
ˆ
Playlisten enthalten Episoden.
ˆ
Benutzer haben einen Namen, eine Login-Kennung und ein Passwort.
9
ˆ
Benutzer können Bewertungen/Notizen schreiben zu Episoden, Staeln,
Figuren und Häusern.
ˆ
Bewertungen enthalten einen Text und ein numerisches Rating (im Bereich
von 0 bis 5).
ˆ
Playlisten sind an Benutzer gebunden; eine Playlist gehört genau einem
Benutzer.
ˆ
Playlisten haben die gleiche Reihenfolge wie in den Staeln.
A.2 Beschreibung der zu erstellenden Website
Zu erstellen ist also eine Website für Game of Thrones-Metainformationen.
In den hier aufgeführten Mockups (Wireframes) ist das Rechteck oben im jeweiligen Mockup das Seiten-Logo, das wir Euch im Eclipse-Workspace mitliefern.
Hinweis:
Zum Testen der Website sowie für die Abnahme, sollten einige Bei-
spieldaten in die Tabellen eingefügt werden. An einigen Stellen im Programmcode sind DB-Transaktionen erforderlich, um eventuelle Anomalien zu vermeiden.
Identiziert sinnvolle Stellen und implementiert anschlieÿend das Konzept an
diesen Stellen.
Die zu erstellende Website soll folgendermaÿen aufgebaut sein:
A.2.1 Die Hauptseite
Der Einstieg soll über die Hauptseite (siehe Abbildung 1) beginnen, die eine
Übersicht über die verfügbaren Daten liefert. Bei den anzuzeigenden Playlisten
handelt es sich um diejenigen, die dem aktuell angemeldeten Benutzer gehören. Aus der Beschreibung geht hervor, daÿ eine Playlist genau einem Benutzer
gehört.
Hinweis: Eine Anmeldefunktion (Login) ist nicht notwendig! Im Programmcode
soll ein Standardbenutzer fest vorgegeben (hartkodiert) sein. Alle benutzerspezischen Lese- und Schreiboperationen auf der Datenbank (z.B. das Anlegen
einer Playlist) sollen mit diesem Benutzer durchgeführt werden.
Zu den Haupt-Informationstypen sollen jeweils maximal fünf gelistet werden
wir schlagen vor, die letzten eingefügten Einträge aufzulisten. Ein Klick auf
einen Eintrag soll zur jeweiligen Detailseite führen. Da eine Figur entweder eine
Person oder Tier sein kann, sollen die Links bei einer Person auf die jeweilige
Detailseite führen.
Es soll möglich sein, Figuren anhand ihres Namens, Hauses und Titels suchen
zu können. Dazu sollte das LIKE-Prädikat verwendet werden. Die Ergebniÿeite
der Suche kann jeweils genau so dargestellt werden, wie die Liste aller entsprechenden Einträge (siehe Abbildung 1), nur, daÿ eben die angezeigten Elemente
der Suche entsprechen müÿen.
Um die Usability der Website zu verbeÿern, sollte auf jeder Unterseite ein Link
auftauchen, der auf die Hauptseite führt.
10
max. 5
Figuren
Zur Personen- oder
Tier-Seite
Figur A
Tier B
...
Alle
Suchen
Zur Ergebnisseite
Zur Liste aller Figuren
max. 5
Häuser
Name Haus 1
Name Haus 2
...
Zu den Häusern
Alle
Suchen
Zur Liste aller Häuser
max. 5
Staffeln
Zur Staffel
Titel Staffeln X
Titel Staffeln Y
...
Alle
Suchen
alle
Playlisten
Zur Liste aller Staffeln
Zur Playlist
Titel Playliste 1
Titel Playliste 2
...
Titel Playliste n
Neue Playlist
Zu "Neue Playlist"
Abbildung 1: Die Hauptseite
11
A.2.2 Detailseite für Personen
Hier (siehe Abbildung 2) soll neben dem Namen, Herkunftsort und der Biographie angezeigt werden, welchem/n Haus/Häusern eine Person angegehört,
welche Beziehungen sie hat und, falls vorhanden, welche Tiere zu ihr gehören.
Die Verlinkungen sind angegeben.
A.2.3 Detailseite für Tiere
Tiere haben keine Biographie, Beziehungen oder Häuser. Es soll lediglich der
Herkunftsort und der Besitzer angegeben werden 3). Der Namen des Besitzers
soll auf die jeweilige Personen-Seiten verlinken.
12
Abbildung 2: Die Personen-Detailseite
13
Ghost
Herkunftsort:
Winterfell
Zum Ort
Besitzer:
Ghost
Zur Tierseite
Abbildung 3: Die Tier-Detailseite
14
A.2.4 Detailseite für Häuser
Auf der Haus-Detailseite (siehe Abbildung 4) sollen der Name des Hauses sowie
deren Sitz angegeben werden. Darüber hinaus soll aufgelistet werden welche
Personen zum Haus gehören und welche Orte im Besitz des Hauses sind. Die
Verlinkungen sind angegeben.
Stark
Sitz: Burg(Ort)
Personen:
Arya Stark
John Snow
...
Besitzt: (Ort)
Winterfell
Hornwood
...
Zum Ort
Zur Person
Zum Ort
Abbildung 4: Die Detailseite für ein Haus
15
A.2.5 Detailseite für Orte
Auf der Ort-Detailseite (siehe Abbildung 5) steht zu welchem Haus der Ort
aktuell gehört und wenn eine Burg existiert wird diese angegeben. Des Weiteren wird aufgelistet welche Figuren dort herkommen und welche Episoden dort
spielen.
Winterfell
aktuell in Besitz von: Stark
Burg: Winterfell
Personen: (Herkunfsort)
Zur Person
Arya Stark
John Snow
...
Episoden: (Handlungsort)
Zur Episode
S01E02
S06E05
...
Abbildung 5: Die Detailseite für ein Ort
16
Zum Haus
Episode (Titel - Nummer)
Staffel
Zur Staffel
Handlung
Figuren:
Arya Stark
Zur Person/Tier
Orte:
Winterfell
Zum Ort
Abbildung 6: Detailseite für eine Episode
A.2.6 Detailseite für Episoden
Die Details für eine Episode sollen aufgeführt werden wie in Abbildung 6 gezeigt
wird. Neben Titel, Nummer, Stael und Handlung sollen auch die Figuren und
Orte aufgelistet werden, die in dieser Episode vorkommen.
A.2.7 Detailseite für Staeln
Staeln (siehe Abbildung 7) haben eine Nummer und es werden alle Episoden
der Stael angezeigt. Jede Episode führt zu ihrer jeweiligen Detailseite.
17
Staffel 1
Titel
Titel
...
1
2
Zur Episode
1.2.2015
8.2.2015
Abbildung 7: Detailseite für eine Kollektion
18
A.2.8 Bewertungen für Detailseiten
Benutzer können Bewertungen schreiben zu Figuren, Häusern, Staeln und Episoden. Diese bestehen aus einem numerischen Rating (im Bereich von 0 5) und
einem Text. Auf den entsprechenden Detailseiten (zu Figur, Haus, Stael und
Episode) soll im unteren Bereich der Seite das Bewertungÿystem eingebaut werden (siehe Abbildung 8).
Ein Benutzer kann über den Button Bewerten pro Eintrag maximal eine
Bewertung abgeben. Dies soll im Programmcode explizit überprüft werden. Bewertet der Benutzer einen Eintrag erneut, obwohl er diesen bereits bewertet hat,
soll die alte Bewertung gelöscht und die neue Bewertung übernommen werden.
Anschlieÿend soll die Detailseite aktualisiert werden.
Die Durchschnittsbewertung für einen Eintrag setzt sich aus dem arithmetischen
Mittel über alle Bewertungen zusammen.
Darüber hinaus sollen sämtliche Bewertungen (jeweils Benutzername, Rating
und Text) für diesen Eintrag angezeigt werden.
A.2.9 Komplette Listen
Einige Seiten (siehe Abbildungen 9) sollen alle Entitäten eines Typs auisten:
alle Figuren, Häuser oder Staeln. Auch hier führen die Einträge wieder zu den
entsprechenden Detailseiten.
A.2.10 Playlist
Eine Playlist-Seite (siehe Abbildung 10) soll die Episodem der jeweiligen Playlist
auisten (sortiert anhand der Reihenfolge der Staeln). Pro Episode soll ein Link
zur Detailseite der Episode angegeben werden.
A.2.11 Neue Playlist anlegen
Das Anlegen einer neuen Playlist (siehe Abbildung 11) ist sehr einfach gehalten:
der Name soll in die Textbox eingegeben werden und nach Klick auf Anlegen
soll die Liste angelegt und der Benutzer auf die (aktualisierte) Hauptseite geleitet werden. Wenn eine Playlist schon existiert, soll dieser Fehler still ignoriert
werden.
19
Abbildung 8: Bewertungen für Detailseiten
20
Alle [Figuren, Häuser, Staffeln]
Name
Name
...
Zum/zur [Figur, Haus, Staffel]
Abbildung 9: Auistungÿeiten
21
Diverse
1. S01E01
2. S03E04
...
Zur Episode
Play
Play
Anzahl: 2
Abbildung 10: Die Detailseite für eine Playlist
22
Abbildung 11: Erstellen einer neuen Playlist
23
B 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.
B.1 Auistung sämtlicher Tabellen
24
The relational schema of the Mondial database
Country: the countries (and similar areas) of the world with some data.
name: the country name
code: the car code
capital: the name of the capital
province: the province where the capital belongs to
area: the total area
population: the population number
Economy: economical information about the countries.
country: the country code
GDP: gross domestic product (in million $)
agriculture: percentage of agriculture of the GDP
service: percentage of services of the GDP
industry: percentage of industry of the GDP
inflation: inflation rate (per annum)
Population: information about the population of the countries.
country: the country code
population growth: population growth rate (per annum)
infant mortality: infant mortality (per thousand)
Politics: political information about the countries.
country: the country code
independence: date of independence (if independent)
wasdependent: the plotical body where the area was dependent of; usually a country (but not
always).
dependent: the country code where the area belongs to
government: type of government
Language: infomation about the languages spoken in a country
country: the country code
name: name of the languange
percentage: percentage of the language in this country
Religion: information about the religions in a country
country: the country code
name: name of the religion
percentage: percentage of the language in this country
EthnicGroup: information about the ethnic groups in a country
country: the country code
name: name of the religion
percentage: percentage of the language in this country
borders: informations about neighboring countries. Note that in this relation, for every pair of
neighboring countries (A,B), only one tuple is given – thus, the relation is not symmetric.
country1: a country code
country2: a country code
length: length of the border between country1 and country2
Continent: Information about continents.
name: name of the continent
area: total area of the continent
encompasses: information to which continents a country belongs.
country: the country code
continent: the continent name
percentage: percentage, how much of the area of a country belongs to the continent
City: information about cities.
name: the name of the city
country: the country code where it belongs to
province: the name of the province where it belongs to
population: population of the city
elevation: the elevation (above sea level) of the city
longitude: geographic longitude
latitude: geographic latitude
Province: information about administrative divisions.
name: the name of the administrative division
country: the country code where it belongs to
area: the total area of the province
population: the population of the province
capital: the name of the capital
capprov: the name of the province where the capital belongs to
note that capprov is not necessarily equal to name. E.g., the municipality of Bogota (Columbia)
is a province of its own, and Bogota is the capital of the surrounding province Cudinamarca.
Organization: information about political and economical organizations.
name: the full name of the organization
abbreviation: its abbreviation
city: the city where the headquarter is located
country: the country code where the headquarter is located
province: the province where the headquarter is located
established: date of establishment
isMember: memberships in political and economical organizations.
organization: the abbreviation of the organization
country: the code of the member country
type: the type of membership
Lake: information about lakes.
name: the name of the lake
area: the total area of the lake
depth: the depth of the lake
elevation: the elevation (above sea level) of the lake
river: the river that flows out of the lake (may be null)
type: the type of the lake, e.g., salt, caldera, ...
coordinates: its geographical coordinates as (longitude, latitude)
Sea: information about seas.
name: the name of the sea
depth: the maximal depth of the sea
River: information about rivers.
name: the name of the river
length: the length of the river
river: the river where it finally flows to
lake: the lake where it finally flows to
2
sea: the sea where it finally flows to;
(note that at most one out of {river,lake,sea} can be non-null)
source: the coordinates of its source
sourceElevation: the elevation (above sea level) of its source
mountains: the montains where its source is located
estuary: the coordinates of its estuary
RiverThrough: information about rivers flowing through lakes.
river: the name of the river
lake: the lake where it flows through
Mountain: information about mountains
name: the name of the mountain
mountains: the montains where the it belongs to
elevation: the maximal elevation of the summit of the mountain
type: the type of the mountain, e.g. volcanic, (active) volcano, ...
coordinates: its geographical coordinates as (longitude, latitude)
Island: information about islands
name: the name of the island
islands: the group of islands where it belongs to
area: the area of the island
elevation: the maximal elevation of the island
type: the type of the island, e.g. volcanic, coral, atoll, ...
coordinates: its geographical coordinates as (longitude, latitude)
Desert: information about deserts.
name: the name of the desert
area: the total area of the desert
coordinates: its geographical coordinates as (longitude, latitude)
geo Mountain: geographical information about mountains
mountain: the name of the mountain
country: the country code where it is located
province: the province of this country
Note that for a country there can be several provinces where the mountain is located in.
analogous for geo island, geo desert, geo river, geo lake, geo sea. Analogously, there are also
geo source and geo estuary.
mergesWith: information about neighboring seas. Note that in this relation, for every pair of
neighboring seas (A,B), only one tuple is given – thus, the relation is not symmetric.
sea1: a sea
sea2: a sea
located: information about cities located at rivers, lakes, and seas.
city: the name of the city
country: the country code where the city belongs to
province: the province where the city belongs to
river: the river where it is located at
lake: the lake where it is located at
sea: the sea where it is located at
Note that for a given city, there can be several lakes/seas/rivers where it is located at.
locatedOn: information about cities located in islands.
city: the name of the city
3
country: the country code where the city belongs to
province: the province where the city belongs to
island: the island it is (maybe only partially) located on
Note that for a given city, there can be several islands where it is located on.
islandIn: information the waters where the islands are located in.
island: the name of the island
sea: the sea where the island is located in
lake: the sea where the island is located in
river: the sea where the island is located in
Note that an island can have coasts to several seas.
MountainOnIsland: information which mountains are located in islands.
mountain: the name of the mountain
island: the name of the island
Incrementally extended version: Mondial-II, 2012
4
B.2 Relationales Schema der Datenbank
29
ER-Diagram of the Mondial Database
area
name
name
name
elevation
pop.
at
City
Language
abbrev
name
depth
elevation
type
elevation
establ.
out
at
speak
at
capital
coordinates
has
to
River
length
Estuary
has
is member
name
coordinates
percent
on
to
name
Ethnic Grp
pop
belong
area
code
Country
pop.grw
depth
length
name
inf.mort
borders
merges
Sea
in
name
believe
Religion
gdp
infl.
governm.
percent
territory
from
dependent
capital
in
percent
Continent
longitude
coordinates
name
name
coordinates
name
↑on
mountains
Mountain
elevation
coordinates
Province
pop.
latitude
in
elevation
type
type
wasdependent
coordinates:
Island
of
in
island in
islands
name
area
in
territory
from
encompasses
in
gdp.agr
gdp.serv
gdp.ind
in
island in
in
island in
indep.date
area
mountains
name
percent
type
Source
to
coordinates
headq
Organization
coordinates
Lake
area
in
name
area
Desert
coordinates
Mondial-II, 2012
B.3 Darstellung von Verknüpfungen zwischen einzelnen
Tabellen
31
Referential Dependencies of the Mondial
Database
Organization
name
✄ abbreviation
city
✄
country
✄
province
✄
established
City
✄ name
✄ country
✄ province
population
elevation
longitude
latitude
isMember
✁ organization
✁ country
type
Country
name
✄ code
capital
province
area
population
Province
name
✁ country
✁
area
population
✁ capital
✁ capprov
✄
✄
✄
Continent
name
area
borders
✁ country1
✁ country2
length
Population
✁ country
population growth
infant mortality
Politics
✁ country
✁ dependent
independence
✁ wasdependent
government
•
✁ City
Language
✁ country
name
percentage
Religion
✁ country
name
percentage
✁ City
EthnicGroup
✁ country
name
percentage
Transitive dependencies are omitted.
Incrementally extended version: Mondial-II, 2012
•
geo river
river
✁ country
✁ province
✁
✁
•
geo source
river
✁ country
✁ province
geo estuary
river
✁ country
✁ province
Lake
✄ name
area
depth
elevation
✁ river
type
coordinates
✄
•
✄
•
•
✄
•
•
•
✄
✁
encompasses
continent
✄
✁ country
percentage
✁ Country.code
Economy
✁ country
GDP
agriculture
service
industry
inflation
✄
✄
geo lake
lake
✁ country
✁ province
geo sea
sea
✁ country
✁ province
located
✁ city
✁ country
✁ province
river
lake
sea
geo island
island
✁ country
✁ province
locatedOn
✁ city
✁ country
✁ province
island
mergesWith
✁ sea1
✁ sea2
•
•
islandIn
✁ island
✁ sea
•
Island
✄ name
islands
area
elevation
type
coordinates
✄
✄
River
✄ name
✁ river
✁ lake
✁ sea
length
source
mountains
elevation
estuary
Sea
✄ name
depth
✄
✄
✄
✄
riverThrough
✁ river
✁ lake
•
mountainOnIsl.
✁ island
✁ mountain
geo mountain
mountain
✄
✁ country
✁ province
Mountain
✄ name
mountains
elevation
type
coordinates
geo desert
desert
✁ country
✁ province
Desert
✄ name
area
coordinates
✄