Projekt 0 - Fakultät für Mathematik und Informatik - Friedrich

Friedrich-Schiller-Universität Jena
Fakultät für Mathematik und Informatik
Institut für Informatik
Lehrstuhl für Datenbanken und Informationssysteme
Prof. Dr. Klaus Küspert
Christoph Koch
Datenbanksysteme Projekt
SoSe 2015
Einarbeitung in PostgreSQL und Wiederholung
Ausgabe: 17.04.2015
Abgabe der Lösung: bis 11.05.2015
Einleitung
Das Einführungsprojekt dient der Einarbeitung in das relationale Datenbanksystem PostgreSQL V9.1 und der
Wiederholung und praktischen Erprobung wichtiger Konzepte aus Vorlesung und Übung. Ihnen stehen
insgesamt drei Wochen zur Bearbeitung zur Verfügung, allerdings überlappt sich diese Zeit mit dem Beginn des
eigentlichen Projektes.
Hinweise

Die Aufgaben sind selbständig zu lösen (keine Teamarbeit).

Die Ergebnisse sind zu dokumentieren und sowohl in Papierform als auch in elektronischer Form (EMail an christoph.koch.at.uni-jena.de) mit folgendem Inhalt abzugeben: Anfragen (bzw. Kommandos)
in lesbarer und strukturierter Form und Anfrageergebnisse bzw. Lösungswege mit kurzen
Erläuterungen und Begründungen.

Notieren Sie Ihren Namen und Login (prak??) auf dem ersten Blatt Ihrer Aufgabenlösungen. Geben Sie
bitte keine losen Blätter ab und drucken Sie maximal zwei Textseiten auf eine Papierseite.

Geben Sie stets die Zahl der Ergebniszeilen an. Sollte ein Ergebnis mehr als 10 Tabellenzeilen haben, so
sind nur die ersten 10 Zeilen des Ergebnisses in der Lösung abzugeben. Benutzen Sie dazu die Klausel
FETCH FIRST 10 ROWS ONLY in Ihrer SELECT-Anweisung.

Für die platzsparende Ausgabe von Attributen mit langen Datentypen (vor allem char) sollten Sie die
SQL-Funktion SUBSTRING (siehe PostgreSQL-Dokumentation) verwenden.

Beachten Sie die speziellen Hinweise bei den einzelnen Aufgabenteilen.
1
Teil 1: Anfragen an den terra-Katalog
Der erste Teil des Aufgabenblattes soll Sie mit der Benutzung des Datenbank-Katalogs für
Schemainformationen vertraut machen. Die verwendete Beispieldatenbank ist die historische geographische
Datenbank terra (Stand vor 1989), welche uns freundlicherweise vom Institut für Programmstrukturen und
Datenorganisation der Universität Karlsruhe überlassen wurde. Die Datenbank darf daher nicht kopiert oder
weitergegeben werden. Wie sie auf den Datenbankserver und die Datenbank zugreifen können, wird im
1
separat ausgehändigten Blatt „Informationen und Hinweise zur Lehrveranstaltung" erklärt.
Hinweise

Die Schemanamen (das Qualifikationsmerkmal) der für die Lösung interessanten Katalogsichten und
Katalogtabellen ist INFORMATION_SCHEMA (Sichten nach ANSI-Standard) und PG_CATALOG
(zusätzliche PostgreSQL-spezifische Sichten und Tabellen), derjenige der Nutzertabellen ist INSTMKF.

Relevante Katalogsichten sind: PG_ATTRIBUTE, PG_CLASS, PG_INDEX, PG_NAMESPACE, PG_TABLES,
COLUMNS, REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS, TABLE_PRIVILEGES und mögliche
weitere. Die Beschreibung der Sichten kann in der PostgreSQL Dokumentation unter Kapitel 34 „The
Information Schema“ und 47 „System Catalogs“ nachgelesen werden. Die Dokumentation ist zu finden
unter: http://www.postgresql.org/docs/9.1/interactive/index.html

Beachten Sie, dass derjenige Nutzer, der mittels CREATE TABLE eine Tabelle erzeugt hat, alle Rechte
auf der Tabelle besitzt (alle anderen Nutzer haben zunächst keine Rechte auf der Tabelle). Er ist
automatisch Besitzer („Owner") der neu erzeugten Tabelle.
Aufgabe1
Die Tabellen der Datenbank terra haben die gleichen Namen wie die Entitytypen und Beziehungstypen in der
2
beiliegenden Schemaskizze auf Seite 6. Stellen Sie mit einer Anfrage an den Katalog fest, welche Entitytypen
und welche Beziehungstypen direkt in Tabellen abgebildet wurden. Wie wurden die nicht explizit umgesetzten
Beziehungen und die „liegt an“-Beziehungen realisiert?
Aufgabe 2
Lösen Sie die folgenden Teilaufgaben jeweils durch (mehrere) SQL-Anfragen an den Katalog.
a)
Bestimmen Sie für alle Nutzertabellen auf die Sie Zugriff haben die zugehörigen Attribute und ihre
Typen/Domains. Welche PostgreSQL-Basisdatentypen verbergen sich hinter den Domains?
b) Auf welchen für sie zugreifbaren Nutzertabellen wurden Primärschlüssel definiert?
c)
Zwischen welchen für sie zugreifbaren Tabellen wurden Fremdschlüsselbedingungen definiert?
d) Für welche Tabellen wurden Indexe erstellt? (Zusatz: Bezüglich welcher Attribute?) Geben Sie für jede
Tabelle an, wie viele Indexe für sie erstellt wurden. Greifen Sie zur Lösung der Aufgabe auf die
3
PG_CATALOG-Sichten/Tabellen zu und verknüpfen diese geschickt mithilfe von OIDs .
Aufgabe 3
Für welche Tabellen der Datenbank terra haben Sie keine Leseberechtigung? Überprüfen Sie Ihre
Lösungsmenge durch Anfragen an die Daten.
1
http://www.informatik.uni-jena.de/dbis/lehre/ss2015/dbproj/Hinweise.pdf
Bei der Skizze handelt es sich um kein vollständiges E/R-Diagramm.
3
http://www.postgresql.org/docs/9.1/static/datatype-oid.html
2
2
Teil 2: SQL-Anfragen an die Nutzdaten von terra
Im zweiten Teil des Aufgabenblattes sollen Sie Ihre SQL-Kenntnisse anwenden, indem Sie die folgenden
natürlichsprachlich formulierten Anfragen auf der terra-Datenbank mit SQL ausführen.
Hinweise

Die Aufgaben sind (falls möglich) mit jeweils einer einzigen Anfrage zu lösen.

Falls nichts anderes gefordert wird, sind die Ergebnisse nach der ersten in der Aufgabenstellung
genannten Spalte zu sortieren.

Beachten Sie die Hinweise zur Arbeit mit Domains in „Informationen und Hinweise zum
Datenbankpraktikum".
Aufgabe 1
Stellen Sie geeignete SQL-Anfragen:
a)
Geben Sie für alle Städte Kanadas (bzw. Canada) mit mehr als 200000 Einwohnern ihre geographischen
Koordinaten und ihre Einwohnerzahl an. Ordnen Sie die Städte dabei mit der Westküste beginnend in
Richtung Atlantik.
b) Ermitteln Sie jeweils für die Anden und Rocky Mountains die Anzahl und durchschnittliche Höhe ihrer
verzeichneten Berge.
c)
Geben Sie alle Flüsse an, in die kein anderer mündet.
d) Vergleichen Sie die Zahl der Einwohner eines Landes mit der Summe der Einwohnerzahlen der Landesteile
des entsprechenden Landes und geben Sie die Länder an, bei denen die Abweichung größer als 5% ist.
e)
Aus wie vielen Inseln bestehen die einzelnen Inselstaaten? Hinweis: Als Inselstaaten werden hier die
Länder bezeichnet, die mit keinem anderen Land eine gemeinsame Grenze besitzen.
Aufgabe 2
Zusatzaufgabe
a)
Suchen Sie alle Quellflüsse des Zaire, d.h. alle Flüsse, deren Wasser direkt oder indirekt in den Zaire fließt
und in die kein anderer Fluss mündet.
b) Errechnen Sie die Gesamtlänge des Zaire-Flusssystems.
Teil 3: Anfrageauswertungsstrategien in PostgreSQL
Der dritte Teil beschäftigt sich mit den Anfrageauswertungsstrategien von PostgreSQL. Exemplarisch soll
angeschaut und von Ihnen erläutert werden, wie PostrgreSQL SQL-Anfragen intern abarbeitet, d.h. wie SQLAnweisungen intern optimiert und in prozedurale Ablaufpläne umgesetzt werden, um diese dann (ggf. unter
Verwendung von Zugriffspfaden, Erzeugen von Zwischenergebnissen, Einsatz des Sortieroperators etc.) auf der
Datenbank auszuführen. Ein Verständnis der Anfrageauswertungsstrategien ist auch für den „normalen“ DBMSBenutzer und Anwendungsentwickler wichtig, um Performance-Implikationen von SQL-Anweisungen
abschätzen zu können (z.B. „Benötigt meine Anfrage voraussichtlich Sekunden, Minuten oder Stunden
Ausführungszeit?“).
Wir verfolgen hier nicht das Ziel, die PostgreSQL-Anfrageauswertungsstrategien komplett zu durchleuchten.
Auch ist zu berücksichtigen, dass nur eine bestimmte PostgreSQL-Version (PostgreSQL V9.1) angeschaut wird;
3
andere PostgreSQL-Versionen
abweichen.
können
in
Punkto
Optimierung/Anfrageauswertungsstrategien
davon
Hinweise

Die Aufgaben sind verbal mit eigenen Worten (keine Übersetzung der Dokumentation) zu beantworten.

Benutzen Sie für die Erzeugung der Statistiken und zur Auswertung von Anfrageplänen das PostgreSQL
4
Administrations-Tool (pgAdmin ):

o
Selektieren einer Datenbank (terra) im Object Browser, über die Menüleiste Tools das Query
Tool (Strg+E) öffnen, anschließend Anfrage eingeben und Explain (F7) wählen
o
Statistiken erzeugen mit Auswahl der Tabelle . . .  terra  Tables  . . . im Object Browser
und im Kontextmenü über Auswahl Maintenance die Operation ANALYZE
o
Eine Beschreibung der einzelnen Operatoren eines Anfrageplans findet sich unter:
http://etutorials.org/SQL/Postgresql/Part+I+General+PostgreSQL+Use/Chapter+4.+Performa
nce/Understanding+How+PostgreSQL+Executes+a+Query/
Alternativ kann das EXPLAIN Kommando der SQL Shell verwendet werden.
Aufgabe 1
Erzeugen Sie eine Kopie der Tabelle STADT mit Namen PRAK??.STADT mit entsprechenden Primär- und
Fremdschlüsseln und einem Index auf dem Attribut EINWOHNER. Abzugeben sind die von Ihnen ausgeführten
SQL-Anweisungen. Sie benötigen die STADT-Kopie zur Bearbeitung der Aufgabe 6.
Aufgabe 2
Lassen Sie sich für die SQL-Anweisung
SELECT *
FROM instmkf.stadt
den Anfrageausführungssplan anzeigen.
Erläutern Sie die Bedeutung und Funktion der einzelnen Operatoren. Beispiel Return-Operator: Dieser übergibt
die Daten von der Anfrage an die Anwendung und führt dabei evtl. noch notwendige Transformationen aus.
Detaillierte Informationen zu den einzelnen Operationen erhält man durch Doppelklick mit der linken
Maustaste auf das jeweilige graphische Symbol im angezeigten Plan.
Aufgabe 3
Interpretieren Sie kurz für die folgende SQL-Anweisung
SELECT einwohner
FROM instmkf.stadt
den Anfrageauswertungsplan (d.h. was geschieht und warum es geschieht).
4
Das pgAdmin Tool kann dabei entweder per SSH-X11 Verbindung serverseitig genutzt, lokal auf Ihrem Rechner
oder als Portable Version auf einem USB-Stick installiert werden. Beachten Sie dazu bitte jeweils die
„Informationen und Hinweise zur Lehrveranstaltung".
4
Aufgabe 4
Lassen Sie sich für die SQL-Anweisung
SELECT einwohner
FROM instmkf.stadt
WHERE einwohner > 10000000
ORDER BY einwohner
den Anfrageauswertungsplan anzeigen und interpretieren Sie diesen gemäß Aufgabe 3. Wie wird die ORDER
BY-Klausel umgesetzt, d.h. was „sehen" Sie diesbezüglich?
Aufgabe 5
Tun Sie für die SQL-Anweisung
SELECT einwohner
FROM instmkf.stadt
WHERE einwohner > 10000000
ORDER BY laenge
gleiches. Welche Unterschiede bemerken Sie im Vergleich mit der vorhergehenden Anfrage?
Aufgabe 6
Führen Sie die Aufgaben 2-5 auf der von Ihnen am Anfang erstellten Tabelle namens PRAK??.STADT aus,
nachdem Sie die Statistiken für diese Tabelle erzeugt haben. Wie haben sich die Ausführungspläne geändert?
Welche Änderung hätten Sie aufgrund des neuen Index aus Aufgabe 1 noch erwartet und warum? Geben Sie
eine Erklärung.
Teil 4: Tabellenbrowser
Abschließend machen Sie sich mit der Programmierung von Java-Anwendungen vertraut. Implementieren Sie
Client-seitig eine Java-Applikation, welche die Inhalte einer gegebenen Tabelle ausgibt. Die Applikation erhält
den Namen der Datenbank, des Nutzers (und sein Passwort) sowie den Namen der Tabelle als Eingabe.
Optional können noch die auszugebenden Spalten und die Anzahl der auszugebenden Tupel angegeben
werden. Die Schnittstelle zwischen der Anwendung und PostgreSQL ist mittels JDBC umzusetzen.
Für die Arbeit an dem Tabellenbrowser können Sie bspw. als Entwicklungsumgebung Eclipse nutzen. Beachten
5
Sie bitte, die von PostgreSQL benötigten JDBC-Treiber (in den Build-Path) einzubinden, damit ein JDBC-Zugriff
auf den PostgreSQL-Server erfolgen kann.
5
http://jdbc.postgresql.org/download.html
5
Anhang: terra
6