Praktikum zur Vorlesung DatenbankenBAI und ISE

Dipl.-Inform. Matthias JordanDipl.-Inform. Thomas Beckers

Ioannis KaratassisJens Landmann

Wintersemester 2011/2012

DatumTeam (Account)Passwort

Aktuelle Informationen, Ansprechpartner, Materialien und Uploads unter:http://www.is.inf.uni-due.de/courses/db_ws11/

1

Inhaltsverzeichnis1 Block 0: Einführung in Linux 3

2 Block 1: Modellierung einer Datenbank für eine Musik-Informations-Website 4

3 Block 2: SQL mit DB2 5

4 Block 3: Implementierung einer Musik-Informations-Website 8

A Spezifikation der Website 9A.1 Beschreibung der Mini-Welt . . . . . . . . . . . . . . . . . . . . . 9A.2 Beschreibung der zu erstellenden Website . . . . . . . . . . . . . 10

A.2.1 Die Homepage . . . . . . . . . . . . . . . . . . . . . . . . 10A.2.2 Detail-Seite für Personen . . . . . . . . . . . . . . . . . . 10A.2.3 Detail-Seite für Bands . . . . . . . . . . . . . . . . . . . . 10A.2.4 Komplette Listen . . . . . . . . . . . . . . . . . . . . . . . 13A.2.5 Detail-Seite für eine Aufnahme . . . . . . . . . . . . . . . 13A.2.6 Playlist . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13A.2.7 Neue Playlist anlegen . . . . . . . . . . . . . . . . . . . . 16A.2.8 Detail-Seite für ein Musikstück . . . . . . . . . . . . . . . 16A.2.9 Detail-Seite für eine Kollektion . . . . . . . . . . . . . . . 16

2

1 Block 0: Einführung in Linux

keine Abgabe

1: Einrichten der Arbeitsumgebung (0 Punkte) Bereitet zunächst EureArbeitsumgebung vor, so dass Ihr in den nächsten Wochen damit komforta-bel arbeiten könnt. Die meisten Einstellungen können über das Menü entwe-der direkt unter System → Einstellungen oder über das GNOME-Kontrollzentrum(ebenfalls im System-Menü zu finden) vorgenommen werden.

Eine andere nützliche Einstellung kann das Hinzufügen zusätzlicher Anwendun-gen (z.B. des Terminals oder eines Texteditors) in der Schnellstartleiste sein(enthält standardmäßig einen Web-Browser und ein E-Mail-Programm). Mit ei-nem Rechtsklick auf das obere Panel und der Option Zum Panel hinzufügen. . .erhaltet Ihr ein Menü, aus dem Ihr mit Anwendungsstarter. . . (zweite Optionvon oben) eine Anwendung aus dem Menü in die Schnellstartleiste legen könnt.

Abgabe:

• Keine.

2: Linux-Umgebung (0 Punkte) Zur Eingewöhnung in die Arbeitsumge-bung werden einige vom Praktikumsleiter vorgegebene, einfache Aufgaben be-arbeitet.

Am Ende der Aufgabe solltet Ihr in der Lage sein, mit der Linux-Shell Verzeich-nisse zu navigieren, Verzeichnisse anzuzeigen und Dateien anzusehen. Ihr solltetwissen, wo Ihr einen Text-Editor, Dateibrowser oder Webbrowser aufrufen könntund wie Ihr aus einer Textdatei ein PDF erzeugt.

Abgabe:

• Keine.

3

2 Block 1: Modellierung einer Datenbank für ei-ne Musik-Informations-Website

Abgabe bis 4.12.2011, 23:59:59 Uhr

3: Datenbank-Modellierung (6 Punkte) In diesem Semester soll eineWebsite für Musik-Informationen entwickelt werden. Dazu sollte als erstes dieentsprechende Datenbank modelliert werden.

Im Anhang dieses Dokuments (siehe Anhang A auf Seite 9) wird die generelleFunktionsweise der zu erstellenden Website vorgestellt. Es ist dringend anzura-ten, vor dem Entwurf diese Spezifikation zu lesen und zu verstehen.

Abgabe:

• EER-Diagramm

4: CREATE TABLE-Statements (4 Punkte) Gebt für die in Aufgabe 3entworfene Datenbank entsprechende CREATE TABLE-Statements an, die dieDatenbank-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 Aufnah-men in die Playlist hinzufügt, deren Attribut „Promotion“ den Wert „1“hat.

Abgabe:

• CREATE TABLE-Statements

4

3 Block 2: SQL mit DB2

Abgabe bis 10.2.2012, 23:59:59 Uhr

5: Katalogisieren (1 Punkt) In diesem Praktikum werden wir die DB2 ausder Kommandozeile heraus ansprechen. Es gibt auch grafische, Java-basierteWerkzeuge, auf die wir jedoch hier verzichten.

Macht Euch kurz mit den unterschiedlichen Modi vertraut, die in den Unterlagenbeschrieben werden. Katalogisiert dann die Instanz dbmaster auf dem Rechnerbijou.is.inf.uni-due.de mit Server-Port 50005.

Katalogisiert die Datenbank imdb lokal unter einem Alias.

Lasst Euch das Node Directory und das Database Directory anzeigen.

Abgabe:

• Einträge für Instanz und Datenbank

6: Arbeiten mit Schema und Tabellen (2 Punkte) Verbindet Euch mitder neukatalogisierten Datenbank und lasst Euch anzeigen, welche Tabellen exi-stieren. Das Schema der Tabellen ist dbmaster.

Schaut Euch zu verschiedenen Tabellen an, wie diese definiert sind. Experimen-tiert mit einigen einfachen SQL-Anfragen auf der Beispieldatenbank.

Abgabe:

• eine Liste der Tabellen in imdb

• die Liste der Attribute der Tabelle title

• Anzahl der Tupel in der Tabelle title

• Anzahl unterschiedlicher Produktions-Jahre (production_year) in derTabelle title

7: Sichten (2 Punkte) Erstellt eine Sicht, die eine symmetrische Relati-on von allen Paaren von Schauspielern des Films „Inception“ enthält. D.h. zuSchauspielern A und B, die mitspielen, soll die Tabelle zwei Tupel (A,B) und(B,A) enthalten.

Abgabe:

• SQL-Anweisung

8: Verwendung von Sichten (2 Punkte) Legt die Sicht aus Aufgabe 7mit dem Namen dbpXXX.inceptionactors an und benutzt die Sicht, um dieAnzahl der Paare zu ermitteln.

Abgabe:

• Die SQL-Anweisung incl. der Ausgabe

5

9: SQL-Anfragen (6 Punkte) Bei den nun folgenden Aufgaben sind SQL-Anfragen gesucht.

Bitte achtet bei Euren Anfragen darauf, dass Ihr keine „Abkürzungen“ benutzt,indem Ihr für bestimmte Werte Zwischenwerte ermittelt. Wenn z.B. nach denFilmen eines Schauspielers („actor“) gefragt wird, dürft Ihr nicht die ID derRolle „actor“ ermitteln (sagen wir mal „100“) und dann in Eurer SQL-Anfrageden ermittelten Wert von 100 verwenden in der Form ... WHERE role_id=100. Die SQL-Anfragen sollen also so formuliert sein, dass als Auswahlkriterien nurdie jeweiligen Stichwörter aus der Aufgabe eingehen.

Formuliert folgende Anfragen in SQL:

(a) Wie viele Filme sind mit dem Schlagwort „love“ getagged?

(b) Ermittle für jedes der 30 häufigsten Schlagworte (keyword), wie oft es inden Filmen der Jahre 1941 bis 1945 vorkam und sortiere die Liste nachabsteigender Vorkommenshäufigkeit.

(c) Wie viele Folgen (episode) gibt es in der 5. Staffel (season) der Serie(kind_id) „The X Files“?

(d) Ermittle die Namen und Produktions-Jahre aller Filme (kind_ty-pe=’movie’), die Kevin Bacon (I) produziert hat (role=’producer’). Dabeisteht die „I“ im Namen für den Wert im Feld imdb_index. Dieses Feldwird benutzt, um mehrere Personen gleichen Namens in der Datenbankzu unterscheiden.

(e) Ermittle, in welchem Land (Ländercode reicht) die meisten Filme produ-ziert werden. (Filme werden von Firmen produziert, die den Typ ’produc-tion companies’ haben. Für Firmen kennt die Datenbank den Ländercode.)Ordne die Liste absteigend nach Anzahl der Filme.

(f) Ermittle die Namen und Produktions-Jahre der Filme, in denen ChuckNorris und Dolph Lundgren zusammen auftreten.

Abgabe:

• SQL-Anweisungen

10: rekursive Anfragen (2 Punkte) Das Spiel „Six Degrees of Kevin Ba-con“ geht so (Beschreibung von http://www.cs.virginia.edu/oracle):

The object of the game is to start with any actor or actress who has been in a movie andconnect them to Kevin Bacon in the smallest number of links possible. Two people arelinked if they’ve been in a movie together. We do not consider links through televisionshows, made-for-tv movies, writers, producers, directors, etc. For example, you mightwonder how Alfred Hitchcock can be connected to Kevin Bacon. One answer is that:

Alfred Hitchcock was in Show Business at War (1943) with Orson Welles, andOrson Welles was in A Safe Place (1971) with Jack Nicholson, andJack Nicholson was in A Few Good Men (1992) with Kevin Bacon!

Then we can count how many links were necessary and assign the actor or actress aBacon number.

6

Findet alle Schauspieler und Schauspielerinnen mit einer maximalen Bacon-Nummer von 4. Als Gedankenübung schreibt ein rekursives SQL-Statement,dass die Schauspieler und Schauspielerinnen für beliebige Bacon-Nummern fin-det.

Tipp: In SELECT-Statements kann man sich das Ergebnis von Berechnungenzurückliefern lassen. Beispiel: SELECT 1+3 FROM title;.

Abgabe:

• SQL-Anweisung

7

4 Block 3: Implementierung einer Musik-Informations-Website

Fertigstellung bis 10.2.2012, 23:59:59 Uhr

11: Korrektur des Entwurfs (0 Punkte) Wenn Ihr die Korrekturen zuEurem ER-Modell erhalten habt, überarbeitet Euren Entwurf und beseitigt evtl.Fehler und Probleme. Alternativ könnt Ihr auch die Musterlösung benutzen, dieauf der Veranstaltungs-Webseite veröffentlicht wird.

Abgabe:

• Keine.

12: Implementierung (20 Punkte) Wenn Ihr ggf. Euer ER-Modell kor-rigiert habt, entwickelt eine Web-Anwendung in Java, die die beschriebenenAnforderungen (siehe Beschreibung im Anhang A) umsetzt.

Folgende Anforderungen sind dabei zu beachten:

• Verwendung der DB21

• korrekte Verwendung von Transaktionen

• Schließen von DB-Resourcen an sinnvollen Stellen

• Verhinderung von SQL-Injection

Abgabe:

• Keine. Die Webapp wird bei einem separaten Abnahme-Termin zusammenmit den Veranstaltern vorgestellt und überprüft.

1Mit anderen Worten: andere Datenbanken, die nicht die IBM DB2 sind, sind nicht zuge-lassen und führen bei Verwendung zum Nichtbestehen.

8

A Spezifikation der Website

A.1 Beschreibung der Mini-Welt

• Alle Künstler haben einen Namen.2

• Alle Künstler sind entweder eine Person oder eine Gruppe (auch „Band“genannt).

• Personen können in einem Zeitraum (von Datum . . . bis Datum) Mitgliedeiner Gruppe sein.

• Musikstücke haben einen Titel.

• Musikstücke können miteinander verwandt sein. Das Verwandschaftsver-hältnis soll durch eine Notiz beschrieben werden können (z.B. „Ice Ice Ba-by“ (Musikstück) „benutzt dieselbe Baseline wie“ (Beschreibung) „UnderPressure“ (Musikstück)).

• Aufnahmen haben eine Länge, ein Veröffentlichungsjahr und einen -Ortsowie eine Audiodatei.

• Aufnahmen sind Aufführungen von Musikstücken. Eine Aufnahme kannmehrere Musikstücke enthalten (Medley, etc.). In diesem Fall wird dieStartzeit des jeweiligen Musikstücks innerhalb der Aufnahme festgehal-ten. Beispiel: Ein Medley enthält ab Sekunde 0 das Musikstück „StrongEnough“ und ab Sekunde 33 das Stück „The Goldfish Song“ etc. pp. JedeAufnahme enthält aber mindestens ein Musikstück.

• Kollektionen (z.B. CD, LP, etc.) haben ein Veröffentlichungsdatum, einenNamen und ein Cover-Bild.

• Kollektionen enthalten mehrere Aufnahmen. Zu jeder Aufnahmen wird diefortlaufende Nummer des jeweiligen Stückes in der Kollektion festgehalten.

• Aufnahmen können in mehreren Kollektionen enthalten sein (Original-Album, 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“, „Pia-no“, „Posaune“ etc.

• Künstler können an Aufnahmen beteiligt sein, in denen sie von einer Start-sekunde bis zu einer Endsekunde ein bestimmtes Instrument spielen.

• Für jede Aufnahme werden ein oder mehrere Haupt-Künstler festgehal-ten, die die Aufnahme hauptsächlich geschaffen haben. Z.B. „Jay-Z“ und„Linkin Park“. Diese Information soll in Listen verwendet werden, umauszugeben, von welchem Künstler eine Aufnahme ist, auch wenn z.B.Background-Sänger beteiligt waren.

2Ausnahmen, wie der Künstler, der früher mal als „Prince“ aufgetreten ist, sollen nichtberücksichtigt werden.

9

• 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 Bereichvon 0 bis 5).

• Playlisten haben einen Namen und enthalten Aufnahmen an einer be-stimmten Position. Z.B. „Encore“ an Position 1, und „Dirt Off Your Shoul-der“ an Position 2. (Playlisten können dann in der Reihenfolge der Posi-tionen ihrer Aufnahmen aufgelistet werden.)

A.2 Beschreibung der zu erstellenden Website

Zu erstellen ist also eine Website für Musik-Metainformationen.

In den hier aufgeführten Wireframes ist das Rechteck oben im jeweiligen Wire-frame das Seiten-Logo, das wir Euch im Eclipse-Workspace mitliefern.

Die zu erstellende Seite soll folgendermaßen aufgebaut sein:

A.2.1 Die Homepage

Der Einstieg soll über die Homepage (siehe Abbildung A.2.1) beginnen, die eineÜbersicht über die verfügbaren Daten liefert.

Zu den Haupt-Informationstypen sollen jeweils maximal fünf gelistet werden– wir schlagen vor, die letzten eingefügten Einträge aufzulisten. Ein Klick aufdie entsprechenden Einträge sollen zu den jeweiligen Detail-Seiten führen. DaKünstler entweder einzelne Personen oder eine Band sein können, sollen dieLinks bei den Künstlern auf die jeweilige Detail-Seite führen.

Die Ergebnisseite der Suche kann jeweils genau so dargestellt werden, wie dieListe aller entsprechenden Einträge, nur dass eben die angezeigten Elemente derSuche entsprechen müssen.

A.2.2 Detail-Seite für Personen

Hier (siehe Abbildung A.2.2) soll neben dem Namen und der Biographie ange-zeigt werden, welche Aufnahmen eine Person veröffentlicht hat und an welchenAufnahmen sie wie beteiligt war. Die Verlinkungen sind angegeben.

A.2.3 Detail-Seite für Bands

Bands haben keine Biographie. Bands haben aber Mitglieder, die aufgelistetwerden sollen (siehe Abbildung A.2.3). Die Namen der Mitglieder sollen auf diejeweiligen Personen-Seiten verlinken.

Die Veröffentlichungen der Band sollen ebenfalls aufgelistet werden incl. einerVerlinkung auf die Seite der jeweiligen Aufnahme.

10

Abbildung 1: Die Homepage

11

James Hetfield

Biographie:Lorem ipsum dolor ...

Beteiligt:

Harvester of Sorrow (Komposition, Gitarre)

Alle meine Entchen (Gitarre)

Veröffentlichte:

Alle meine Entchen 1989

Zur Aufnahme

Zur Aufnahme

Zum Musikstück

Abbildung 2: Die Personen-Detailseite

12

Metallica

Mitglieder:James Hetfield 1981 - heute

Lars Ulrich 1981 - heute

Cliff Burton 1982 - 1986

...

Veröffentlichte:

... and Justice for All 1988

...

Zum Künstler

Zur Aufnahme

Abbildung 3: Die Band-Detailseite

A.2.4 Komplette Listen

Einige Seiten (siehe Abbildungen A.2.4 und A.2.4) sollen alle Entitäten einesTyps auflisten: alle Künstler (Bands und Personen) bzw. alle Aufnahmen.

A.2.5 Detail-Seite für eine Aufnahme

Auf der Aufnahmen-Detailseite (siehe Abbildung A.2.5) sollen Details zu einerAufnahme aufgelistet werden.

Die Dropdown-Liste, die imWireframe als „Diverse“ belabelt ist, soll beim erstenLaden mit einem leeren Text initialisiert werden, wenn die Aufnahme in keinerPlaylist aufgeführt ist. Wenn es Playlisten gibt, die dieses Stück enthalten, solldas Label aus dem Titel irgend einer Playlist bestehen.

A.2.6 Playlist

Eine Playlist-Seite (siehe Abbildung A.2.6) soll die Stücke der jeweiligen Playlistauflisten sowie die Gesamtlänge aller Stücke. Pro Aufnahme soll ein Link zumDownloaden der Musik-Datei angeboten werden.

13

KünstlerJohn Doe

Jane Doe

Guns 'N' Flowers

...

Zum Künstler

Abbildung 4: Die Liste aller Künstler

AufnahmenTitel 3:30 Künstler A 2001

Titel 2:54 Künstler B 1999

...

Zum Künstler

Zur Aufnahme

Abbildung 5: Die Liste aller Aufnahmen

14

Zum Künstler

Zum Künstler

Alle meine Entchen

aus The Duck Tape

von Metallica

James Hetfield: Gitarre, Gesang

Lars Ulrich: Schlagzeug

...

Zu Playlist hinzufügen Diverse

Play

Zur Kollektion

wieder zu dieser Seite

Die Auswahl der Playlisten ist leer, wenn der Song zu keiner

Playlist gehört. Sonst ist irgend eine Playlist ausgewählt.

Abbildung 6: Die Detailseite für eine Aufnahme

Abbildung 7: Die Detailseite für eine Playlist

15

Neue Playlist

Name Anlegen Zur Homepage

Abbildung 8: Erstellen einer neuen Playlist

A.2.7 Neue Playlist anlegen

Das Anlegen einer neuen Playlist (siehe Abbildung A.2.7) ist sehr einfach ge-halten: der Name soll in die Textbox eingegeben werden und nach Klick aufÄnlegenßoll die Liste angelegt und der Benutzer auf die (aktualisierte) Home-page geleitet werden. Wenn eine Playlist schon existiert, soll dieser Fehler stillignoriert werden.

A.2.8 Detail-Seite für ein Musikstück

Die Details für ein Musikstück (siehe Abbildung A.2.8) sollen aufgeführt werdenwie in der Abbildung gezeigt.

A.2.9 Detail-Seite für eine Kollektion

Kollektionen haben normalerweise auch ein Plattencover. Das Cover-Bild soll indem entsprechenden Rechteck angezeigt werden. Ansonsten soll diese Seite sofunktionieren wie eine Playlist-Detailseite.

16

Stairway to Heaven

Verwandt:zitiert "Alle meine Entchen"...

Aufnahmen:Metallica Los Angeles 1981...

Beteiligt:Robert Plant: Text, KompositionJimmy Page: Komposition

Zum Musikstück

Zum Künstler

Zum Künstler

Abbildung 9: Detailseite für ein Musikstück

17

Abbildung 10: Detailseite für eine Kollektion

18