Praktikumsaufgabe 1 - Hochschule Niederrhein

Dalitz
Datenbanken
WS 2012/13
Praktikumsaufgabe 1
Lernziele
Kennenlernen der Arbeitsumgebung. Übung der Arbeit mit dem SQL-Interpreter und Schreiben von
SQL-Scripts.
Vorbereitung
Folgende Informationen müssen Sie recherchieren und als schriftliche Notiz mitbringen:
• Was machen die elementaren Linux/MacOS X/Unix-Dateiverwaltungskommandos ls, cd, rm,
mkdir, mv, chmod? ([1] Kap. 4)
• Was bedeutet es, wenn in der Shell an einen Befehl ein “&” angehängt wird,
z.B. gedit bla.sql &?
• Wie legt man in SQL eine Tabelle an? ([2] Kap. 1.4.1)
• Was machen die psql Metakommandos \q, \d und \i? ([3] Kap. “Reference, Client Applications”)
Zur Arbeitsumgebung
Eine Linux-Umgebung mit einer PostgreSQL-Installation ist auf den Rechnern als virtuelle Maschine
eingerichtet. Im Startmenü sind ein Terminal, ein Editor und ein Webbrowser eingetragen.
Der installierte Editor ist gedit, der bei Dateien mit der Endung sql automatisch die Syntax hervorhebt.
Eine neue SQL-Datei legen Sie z.B. mit dem Befehl
$ gedit create.sql &
Aufgaben
1.1 Online-Hilfe. Stellen Sie sicher, dass Sie auf die PostgreSQL Online-Dokumentation zugreifen
können. Starten Sie dazu den Webbrowser und richten Sie Lesezeichen auf die SQL-Referenz,
die Client-Application psql, das Client-Interface libq sowie den Index der PostgreSQl-Doku an.
1.2 Datenbank-Zugriff. Die Datenbank sollte so eingerichtet sein, dass Sie sich ohne Kennwort
direkt anmelden können. Test Sie dies, indem Sie die folgenden Befehle eingeben (“$” ist der
Shell-Prompt und “psql>” der SQL-Prompt):
$ psql
psql=> select current date;
psql=> \q
1
Dalitz
Datenbanken
WS 2012/13
1.3 psql. Führen Sie folgende Aufgaben durch:
1. Anlegen einer Tabelle mit nur einer Spalte direkt in psql
2. Anzeigen der Struktur dieser Tabelle (welches Metakommando?)
3. Erstellen eines SQL-Scripts insert.sql (Editorbefehl: gedit) mit Befehlen zum Befüllen der
Tabelle und Ausführen dieses Scripts in psql (welches Metakommando?)
4. Entfernen der Tabelle
1.4 SQL. Erstellen Sie ein SQL-Script zum Anlegen des folgenden Datenmodells:
person
abteilung
import
nr#
name
vorname
geburt
abteilungnr
gehalt
ort
nr#
name
nr
ort
geburt
name
vorname
abteilungname
abteilungnr
gehalt
Die Tabelle import wird nur vorübergehend für den Datenimport benötigt und sollte nur aus
varchar Feldern bestehen.
Für die weiteren Aufgaben benötigen Sie die Datei dbsnam-utf8.csv, die Sie z.B. von der Webseite zur Veranstaltung kopieren können. Importieren Sie die Datei dbsnam-utf8.csv in die Tabelle import mit dem Befehl
(Achtung: den Befehl nicht aus dieser PDF-Datei kopieren!)
\copy import from ’dbsnam-utf8.csv’ delimiter ’;’
Listen Sie auf wieviele Daten in import gelandet sind und testen Sie ob Umlaute richtig angezeigt werden (ggf. das Encoding vor dem Import auf utf8 umstellen mit dem Metakommando
\encoding).
Schreiben Sie ein SQL-Script zum Kopieren der Daten aus der Tabelle import in die Zieltabellen. Hinweise:
• in der insert-Anweisung kann die values-Klausel durch einen select-Befehl ersetzt werden.
• verwenden Sie für die Abteilungstabelle select distinct
Führen Sie anschließend folgende Abfragen auf den Zieltabellen durch:
• Aus welchem Ort kommen wieviele Mitarbeiter? (group by)
• Wie heißt die Abteilung, in der Norbert Herrling arbeitet? (join)
• Was ist das Durchschnittsgehalt je Abteilung? (avg() und group by)
2
Dalitz
Datenbanken
WS 2012/13
Anmerkung
Sie dürfen auch Ihren eigenen Rechner im Praktikum verwenden. Beachten Sie aber bitte, dass Sie sich
in diesem Fall selber um eine korrekte Installation und Einrichtung der Software kümmern müssen.
In dieser Anmerkung gebe möchte ich nur einen wichtigen Hinweis geben für ein typisches Problem
beim Zugriff auf die installierte Software.
Wenn Sie PostgreSQL auf Ihrem eigenen Rechner installiert haben und dabei eine Installation aus den
Quellen gewählt haben, ist zu beachten dass die komplette Software standardmäßig in das Verzeichnis /usr/local/pgsql installiert wird, das sich nicht im Suchpfad für Programme, Bibliotheken oder
Manpages befindet.
Durch folgende Einträge in der Datei $HOME/.profile können sie die entsprechenden Pfade bekannt
machen:
PGROOT=/usr/local/pgsql
# zum Abkürzen
PATH=$PATH:$PGROOT/bin
# Suchpfad Executables
MANPATH=$MANPATH:$PGROOT/man
# Suchpfad Manpages
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGROOT/lib # Suchpfad Libs
export PATH MANPATH LD_LIBRARY_PATH # weiterleiten an Subshells
Unter MacOS X ist die Variable LD_LIBRARY_PATH zu ersetzen durch DYLD_LIBRARY_PATH,
so dass die letzten beiden Zeilen statt dessen lauten
DYLD_LIBRARY_PATH=$DYLD_LIBRARY_PATH:$PGROOT/lib # Suchpfad Libs
export PATH MANPATH DYLD_LIBRARY_PATH # weiterleiten an Subshells
Wenn Sie die Datenbank nur lokal auf Ihrem Rechner zum Testen und nicht produktiv nutzen, können
Sie auch in der Postgres-Konfigurationsdatei pg hba.conf die Authentifizierungsmethode “trust” für
alle Benutzer wählen: dann kann sich jeder ohne Kennwort anmelden. In dem Fall sollten Sie natürlich
nicht den Netzzugriff auf die Datenbank freischalten (das ist bei einer Installation aus den Quellen
standardmäßig ausgestellt, kann aber ggf. bei Binärpaketen von Linux-Distributionen standardmäßig
aktiv sein).
Referenzen
[1] Welsh, Kaufmann: Linux - Wegweiser zur Installation & Anwendung. O’Reilly (1998)
Semesterapparat (TWR Wels)
[2] Dalitz: Datenbanksystme. Vorlesung an der Hochschule Niederrhein, Vorlesungsskript (2012)
Semesterapparat (TWR Kofl)
[3] The PostgreSQL Global Development Group:
PostgreSQL 8.3.9 Dokumentation. http://www.postgresql.org/docs/ (2008)
3