Aufgaben

Praktikum zur Vorlesung Datenbanken
BAI, Komedia, ISE CSCE/CE,
Mathematik/Technomathematik und Lehramt
Informatik
Ioannis Karatassis, M.Sc.
Henning Schumann, B.Sc.
Nikolas Gur, B.Sc.
Wintersemester 2015/2016
Block 3 aktualisiert am 1. Dezember 2015
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 MusikInformations-Website (10 Punkte)
4
2 Block 2: SQL mit DB2 (15 Punkte)
5
3 Block 3: Implementierung einer Musik-Informations-Website
(20 Punkte)
8
A Spezifikation der Website
A.1 Beschreibung der Mini-Welt . . . . . . . .
A.2 Beschreibung der zu erstellenden Website
A.2.1 Die Hauptseite . . . . . . . . . . .
A.2.2 Detailseite für Personen . . . . . .
A.2.3 Detailseite für Bands . . . . . . . .
A.2.4 Detailseite für Aufnahmen . . . . .
A.2.5 Detailseite für Musikstücke . . . .
A.2.6 Detailseite für Kollektionen . . . .
A.2.7 Bewertungen für Detailseiten . . .
A.2.8 Komplette Listen . . . . . . . . . .
A.2.9 Playlist . . . . . . . . . . . . . . .
A.2.10 Neue Playlist anlegen . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
9
9
10
10
11
11
15
16
16
18
18
18
18
B Beschreibung der Geodatenbank
22
B.1 Auflistung sämtlicher Tabellen . . . . . . . . . . . . . . . . . . . 22
B.2 Relationales Schema der Datenbank . . . . . . . . . . . . . . . . 27
B.3 Darstellung von Verknüpfungen zwischen einzelnen Tabellen . . . 29
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 eine Musik-Informations-Website (10 Punkte)
Abgabe bis 30.11.2015, 11:59 Uhr
3: Datenbank-Modellierung (6 Punkte) In diesem Semester soll eine
Website für Musik-Informationen 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
Spezifikation (siehe Anhang A.2 auf Seite 10) zu lesen und zu verstehen.
Abgabe:
• EER-Diagramm
4: CREATE TABLE- und Trigger-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.
Enthalten sein sollen darin
• sinnvolle Integritätsbedingungen für alle Attribute
• ein Trigger, der bei Anlegen einer neuen Playlist automatisch alle Aufnahmen in die Playlist hinzufügt, deren Attribut Promotion“ den Wert 1“
”
”
hat.
Abgabe:
• CREATE TABLE-Statements und Trigger-Statement
4
2
Block 2: SQL mit DB2 (15 Punkte)
Abgabe bis 8.2.2016, 11.59 Uhr
Für die folgenden Aufgaben steht eine Beschreibung der verwendeten Datenbank
im Anhang B zur Verfügung.
Hinweis: Block 2 ist unabhängig von Block 1 und 3. Letztere bauen aufeinander
auf.
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.
Die Datenbank mit der wir arbeiten werden befindet 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 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 (node) und Datenbank (database)
6: Sichten (1 Punkt)
(a) Erstellt eine Sicht dblabXX1 .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 dblabXX.landmarks, um zu jedem Land die Anzahl von
Landmarken auszugeben.
(b) Benutzt die Sicht dblabXX.borders, um für alle Länder die Gesamtlänge
ihrer Grenzen auszugeben.
1 Dabei
steht XX“ für Eure Gruppenummer
”
5
Abgabe:
• SQL-Befehle
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
6
10: Trigger (2 Punkte) Erstellt und füllt zunächst eine Tabelle
dblabXX.orgacount (ersetzt XX wieder durch Eure Gruppennummer):
CREATE TABLE dblabXX.orgacount (
orga VARCHAR(20),
countrycount INTEGER
)
INSERT INTO dblabXX.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 Namen dblabXX.trigger1 und
dblabXX.trigger2, die folgendes leisten:
• wenn neue Mitgliedschaften in die Tabelle dblabXX.ismember eingefügt
werden, soll das Attribut countrycount für entsprechenden Organisationen erhöht werden
• wenn Mitgliedschaften aus der Tabelle dblabXX.ismember gelöscht werden, soll das Attribut countrycount für die entsprechenden Organisationen gesenkt werden
Hinweis: Die Tabellen dblabXX.city und dblabXX.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)
Musik-
Fertigstellung bis zum Abnahmetermin (9.2.2016 – 12.2.2016)
11: Korrektur des Entwurfs (0 Punkte) Wenn Ihr die Korrekturen zu Eurem EER-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 EER-Modell korrigiert 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 DB22
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 dblab-Accounts
eine Kopie Eures Endprojekts befinden.
(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
dblab-Accounts befinden.
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
A.1
Spezifikation der Website
Beschreibung der Mini-Welt
• Alle Künstler haben einen Namen.3
• Alle Künstler sind entweder eine Person oder eine Gruppe (auch Band“
”
genannt).4 Für Personen kann eine Biografie hinterlegt werden.
• Personen können in einem Zeitraum (von Datum … bis Datum) Mitglied
einer Gruppe sein.
• Musikstücke haben einen Titel.
• Musikstücke können miteinander verwandt sein. Das Verwandschaftsverhältnis soll durch eine Notiz beschrieben werden können (z.B. Ice Ice
”
Baby“ (Musikstück) benutzt dieselbe Baseline wie“ (Beschreibung) Un”
”
der Pressure“ (Musikstück)).
• Aufnahmen haben eine Länge, ein Veröffentlichungsjahr und einen -Ort
sowie eine Audiodatei. Darüber hinaus ein Feld Promotion, welches die
Werte 0 oder 1 annehmen kann.
• Aufnahmen sind Aufführungen von Musikstücken. Eine Aufnahme kann
mehrere Musikstücke enthalten (Medley, etc.). In diesem Fall wird die
Startzeit des jeweiligen Musikstücks innerhalb der Aufnahme festgehalten. Beispiel: Ein Medley enthält ab Sekunde 0 das Musikstück Strong
”
Enough“ und ab Sekunde 33 das Stück The Goldfish Song“ etc. pp. Jede
”
Aufnahme enthält aber mindestens ein Musikstück.
• Kollektionen (z.B. CD, LP, etc.) haben ein Veröffentlichungsdatum, einen
Namen und ein Cover-Bild.
• Kollektionen enthalten mehrere Aufnahmen. Zu jeder Aufnahmen wird die
fortlaufende Nummer des jeweiligen Stückes in der Kollektion festgehalten.
• Aufnahmen können in mehreren Kollektionen enthalten sein (OriginalAlbum, Best-Of, etc.)
• Rollen haben einen Namen.
• Künstler können in bestimmten Rollen an Musikstücken beteiligt sein.
• Instrumente haben einen Namen. Z.B. voc“, p“, tb“ oder Gesang“,
”
”
”
”
Piano“, Posaune“ etc.
”
”
3 Ausnahmen, wie der Künstler, der früher mal als Prince“ aufgetreten ist, sollen nicht
”
berücksichtigt werden.
4 Verwende für die Vererbung ein Konstrukt von EER-Modellen (Erweiterte EntityRelationship) (s. http://www.is.inf.uni-due.de/courses/db_ws10/folien/Kap04a.pdf, S.
3)
9
• Künstler können an Aufnahmen beteiligt sein, in denen sie von einer Startsekunde bis zu einer Endsekunde ein bestimmtes Instrument spielen.
• Für jede Aufnahme werden ein oder mehrere Haupt-Künstler festgehalten, die die Aufnahme hauptsächlich geschaffen haben. Z.B. Jay-Z“ und
”
Linkin Park“. Diese Information soll in Listen verwendet werden, um
”
auszugeben, von welchem Künstler eine Aufnahme ist, auch wenn z.B.
Background-Sänger beteiligt waren.
• Benutzer haben einen Namen, eine Login-Kennung und ein Passwort.
• Benutzer können Bewertungen schreiben zu Aufnahmen, Kollektionen,
Künstlern und Musikstücken.
• Bewertungen enthalten einen Text und ein numerisches Rating (im Bereich
von 0 bis 5).
• Playlisten haben einen Namen und enthalten Aufnahmen an einer bestimmten Position. Z.B. Encore“ an Position 1, und Dirt Off Your Shoul”
”
der“ an Position 2. (Playlisten können dann in der Reihenfolge der Positionen ihrer Aufnahmen aufgelistet werden.)
• Playlisten sind an Benutzer gebunden; eine Playlist gehört genau einem
Benutzer.
A.2
Beschreibung der zu erstellenden Website
Zu erstellen ist also eine Website für Musik-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 Beispieldaten in die Tabellen eingefügt werden. An einigen Stellen im Programmcode sind DB-Transaktionen erforderlich, um eventuelle Anomalien zu vermeiden. Identifiziert 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, dass eine Playlist genau einem Benutzer
gehört.
Hinweis: Eine Anmeldefunktion (Login) ist nicht notwendig! Im Programmcode soll ein Standardbenutzer fest vorgegeben (hartkodiert) sein. Alle benutzerspezifischen Lese- und Schreiboperationen auf der Datenbank (z.B. das Anlegen
einer Playlist) sollen mit diesem Benutzer durchgeführt werden.
10
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 Künstler entweder einzelne
Personen oder eine Band sein können, sollen die Links bei den Künstlern auf
die jeweilige Detailseite führen.
Es soll möglich sein, Künstler anhand ihres Namens, und Aufnahmen und Musikstücke anhand ihres Titels suchen zu können. Dazu sollte das LIKE-Prädikat
verwendet werden. Die Ergebnisseite der Suche kann jeweils genau so dargestellt
werden, wie die Liste aller entsprechenden Einträge (siehe Abbildung 1), nur,
dass eben die angezeigten Elemente der Suche entsprechen müssen.
Um die Usability der Website zu verbessern, sollte auf jeder Unterseite ein Link
auftauchen, der auf die Hauptseite führt.
A.2.2
Detailseite für Personen
Hier (siehe Abbildung 2) soll neben dem Namen und der Biographie angezeigt
werden, welche Aufnahmen eine Person veröffentlicht hat und an welchen Aufnahmen bzw. Musikstücken sie wie beteiligt war. Die Verlinkungen sind angegeben.
A.2.3
Detailseite für Bands
Bands haben keine Biographie. Bands haben aber Mitglieder, die aufgelistet
werden sollen (siehe Abbildung 3). Die Namen der Mitglieder sollen auf die
jeweiligen Personen-Seiten verlinken.
Die Veröffentlichungen der Band sollen ebenfalls aufgelistet werden inkl. einer
Verlinkung auf die Seite der jeweiligen Aufnahme.
11
Abbildung 1: Die Hauptseite
12
James Hetfield
Biographie:
Lorem ipsum dolor ...
Veröffentlichte:
Alle meine Entchen
1989
Zur Aufnahme
Beteiligt:
Zum Musikstück
Harvester of Sorrow (Komposition, Gitarre)
Alle meine Entchen (Gitarre)
Zur Aufnahme
Abbildung 2: Die Personen-Detailseite
13
Metallica
Mitglieder:
James Hetfield
Lars Ulrich
Cliff Burton
...
1981 - heute
1981 - heute
1982 - 1986
Zum Künstler
Veröffentlichte:
... and Justice for All
...
1988
Zur Aufnahme
Abbildung 3: Die Band-Detailseite
14
A.2.4
Detailseite für Aufnahmen
Auf der Aufnahmen-Detailseite sollen Details zu einer Aufnahme aufgelistet
werden.
Die Dropdown-Liste, die im Mockup als Diverse“ beschriftet ist (siehe Abbil”
dung 4), soll alle Playlisten des aktuell angemeldeten Benutzers beinhalten.
Der Button Zu Playlist hinzufügen“ soll die Aufnahme zu der Playliste hinzu”
fügen, die in der Dropdown-Liste ausgewählt ist und zwar an die letzte Position.
Dies soll nur geschehen, wenn die Aufnahme nicht bereits in der ausgewählten
Playliste vorhanden ist. Anschließend wird die Detailseite aktualisiert.
Dropdown-Feld enthält alle Playlisten
des Benutzers
Abbildung 4: Die Detailseite für eine Aufnahme
15
Abbildung 5: Detailseite für ein Musikstück
A.2.5
Detailseite für Musikstücke
Die Details für ein Musikstück sollen aufgeführt werden wie in Abbildung 5
gezeigt wird.
A.2.6
Detailseite für Kollektionen
Kollektionen haben normalerweise auch ein Plattencover. Das Cover-Bild soll in
dem entsprechenden Rechteck angezeigt werden. Ansonsten soll diese Seite so
funktionieren wie eine Playlist-Detailseite. (Das Cover-Bild kann eine einfache
Datei sein, auf die verlinkt wird.)
16
Abbildung 6: Detailseite für eine Kollektion
17
A.2.7
Bewertungen für Detailseiten
Benutzer können Bewertungen schreiben zu Aufnahmen, Kollektionen, Künstlern und Musikstücken. Diese bestehen aus einem numerischen Rating (im Bereich von 0 – 5) und einem Text. Auf den entsprechenden Detailseiten (zu Aufnahmen, Kollektionen, Personen, Bands und Musikstücken) soll im unteren Bereich der Seite das Bewertungssystem eingebaut werden (siehe Abbildung 7).
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.8
Komplette Listen
Einige Seiten (siehe Abbildungen 8 und 9) sollen alle Entitäten eines Typs auflisten: alle Künstler (Bands und Personen) bzw. alle Aufnahmen.
A.2.9
Playlist
Eine Playlist-Seite (siehe Abbildung 10) soll die Stücke der jeweiligen Playlist
auflisten (sortiert anhand den Positionen) sowie die Gesamtlänge aller Stücke.
Pro Aufnahme soll ein Link zum Downloaden der Musik-Datei angeboten werden
(ein einfacher Link auf eine Musik-Datei genügt).
A.2.10
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.
18
Abbildung 7: Bewertungen für Detailseiten
19
Künstler
Zum Künstler
John Doe
Jane Doe
Guns 'N' Flowers
...
Abbildung 8: Die Liste aller Künstler
Aufnahmen
Titel
Titel
...
3:30
2:54
Künstler A 2001
Künstler B 1999
Zum Künstler
Zur Aufnahme
Abbildung 9: Die Liste aller Aufnahmen
20
Abbildung 10: Die Detailseite für eine Playlist
Abbildung 11: Erstellen einer neuen Playlist
21
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
Auflistung sämtlicher Tabellen
22
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
27
name
Language
name
Ethnic Grp
name
headq
capital
establ.
name
gdp.agr
gdp.serv
of
name
pop.
borders
length
name
City
pop.
in
in
in
in
in
in
on
at
at
coordinates
in
elevation
capital
Province
area
at
type
name
area
type
Lake
out
River
name
Sea
coordinates
Source
mountains
coordinates
Estuary
coordinates
merges
elevation
to
has
to
has
to
coordinates
elevation
islands
island in
island in
island in
Island
↑on
coordinates
in
Mondial-II, 2012
elevation
mountains
Mountain
Desert
area
coordinates
name
type
area
name
depth
length
name
depth
elevation
name
ER-Diagram of the Mondial Database
abbrev
is member
pop
code
Country
infl.
dependent
territory
territory
gdp.ind
gdp
type
Organization
name
area
from
coordinates:
coordinates
latitude
longitude
wasdependent
from
governm.
indep.date
pop.grw
inf.mort
percent
speak
percent
belong
believe
percent
encompasses
Religion
percent
name
Continent
area
in
B.3
Darstellung von Verknüpfungen zwischen einzelnen
Tabellen
29
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
•
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
Transitive dependencies are omitted.
Incrementally extended version: Mondial-II, 2012
✄