Übungsblatt 05 - Informatik

Universität Augsburg, Institut für Informatik
Datenbanken u. Informationssysteme
Übungsblatt 5
Datenbankprogrammierung (Oracle)
Aufgabe 1: Objekte in Oracle
In der Vorlesung haben Sie Nested Tables für die Darstellung von mehrwertigen Attributen kennengelernt. Nested
Tables sind beschränkt auf eine Spalte. Eine Erweiterung dieses Konzepts sind Objekte.
Unter anderem unterstützt Oracle das Ablegen von Objekten innerhalb von Relationen (objekt-relationale Daten). Um
ein Objekt in einer Relation abzuspeichern, muss - analog zu Nested Tables - zunächst ein entsprechender Datentyp
erzeugt werden:
CREATE TYPE meineKlasse AS OBJECT (
attribut01 INTEGER,
attribut02 VARCHAR2(20),
... );
Analog zu Nested Tables gibt es für Objekte einen Konstruktor bestehend aus Typ-Name als Schlüsselwort und den
Attributen als Parameter: meineKlasse(12, ’attribut02-Wert’, ...)
Der so erstellte Typ kann in jeder Tabelle als Datentyp verwendet werden. Auch kann damit eine Objekt-Tabelle
angelegt werden:
CREATE TABLE tabelle OF meineKlasse (PRIMARY KEY(attribut01));
Der Table Constraint gibt an, dass ein Attribut des Objekts als Primärschlüssel verwendet wird. Wird das Objekt nur
als Spalte in einer Relation verwendet, so muss der Spaltenname ebenfalls angegeben werden:
PRIMARY KEY(<SPALTE>.<ATTRIBUT>).
Um in einer anderen Tabelle einen Fremdschlüssel auf ein Element einer Objekt-Tabelle anzulegen, muss eine Spaltendefinition mit dem Schlüsselwort REF (eine Referenz) angegeben werden:
CREATE TABLE meineTabelle (
elem REF meineKlasse REFERENCES tabelle,
...
);
In diese Tabelle können Sie dann Referenzen auf Objekte aus tabelle einfügen. Eine Referenz erhalten Sie mit der
Funktion REF. Die Funktion DEREF dient der Dereferenzierung. Ausgehend von einer Referenz erhalten Sie dadurch
das entsprechende Objekt zurück.
SELECT REF(t), DEREF(REF(t)) FROM tabelle t WHERE ...
Gegeben sei nun das folgende ER-Diagramm:
contains
Publication
Keyword
title
title
code
code
publisher
research−area
a) Erstellen Sie für beide Entitäten Objekt-Typen und Tabellen, die die Objekte aufnehmen können. Die Relationstabelle soll Referenzen auf Publikations- bzw. Keyword-Einträge enthalten.
b) Fügen Sie einige Einträge in die erstellten Relationen ein.
c) Geben Sie die Einträge aus Publikation mit den passenden Keywords aus. Attribute von Objekten werden in
Oracle-SQL mit Punkt-Syntax ausgewählt.
1
Aufgabe 2: Materialized Views (Oracle)
Bei Views, die auf komplexen Abfragen definiert sind, kann es sinnvoll sein, die View-Daten nicht bei jedem Aufruf
neu generieren zu müssen, sondern sie zu materialisieren, d. h. als Kopie abzuspeichern und nur zu bestimmten
Zeitpunkten zu aktualisieren.
Oracle bietet hierfür das Konstrukt der materialisierten Views. Für materialisierte Views gibt es eine Trennung von
Abfragen: Einfache Abragen selektieren nur Zeilen aus einer einzigen Tabelle (keinem View!) und führen keine Mengenoperationen, Joins oder Gruppierungen aus. Jede Ergebniszeile einer einfachen Abfrage entspricht einer Zeile der
Ursprungstabelle. Bei komplexen Abfragen ist das nicht der Fall.
Die Syntax zur Erstellung eines materialisierten Views lautet:
CREATE MATERIALIZED VIEW <name>
[ REFRESH [ FAST | COMPLETE | FORCE ]
[ ON {DEMAND | COMMIT} ]
[ START WITH <startdatum> ] [ NEXT <datum> ]
[ WITH {PRIMARY KEY | ROWID} ] | NEVER REFRESH ]
[ FOR UPDATE ]
AS
SELECT ...
• Die Refresh-Option legt fest, wann und wie Oracle die Daten des materialisierten Views aktualisiert.
– FAST, nur für einfache Views. Es werden nur die veränderten/neuen Zeilen der Mastertabelle übertragen.
Um diese Methode verwenden zu können, muss zusätzlich ein View Log auf die Mastertabelle erzeugt
werden:
CREATE MATERIALIZED VIEW LOG ON <mastertabelle>;
– COMPLETE: Der View wird komplett neu aufgebaut.
– FORCE: veranlasst Oracle, zunächst einen FAST-Refresh zu versuchen. Ist das nicht möglich, wird der
View komplett neu aufgebaut.
• Der Zeitpunkt von Aktualisierungen wird festgelegt mittels
– ON COMMIT: Ein Update wird bei einem COMMIT auf die Mastertabelle(n) durchgeführt.
– ON DEMAND: Ein Update erfolgt erst bei Eingabe eines Refresh-Befehls. Dies kann (unter anderem) durch
den Aufruf des Befehls
EXECUTE DBMS_MVIEW.REFRESH(’<Refresh-Group-Name>’);
erfolgen. Mit diesem Befehl können auch materialisierte Views aktualisiert werden, für die eine andere
Refresh-Methode angegeben wurde.
• START WITH ... NEXT ...: Ein Intervall, das zeitgesteuerte Aktualisierungen ermöglicht, , in dem mit
START WITH ein Zeitwert und mit NEXT als darauffolgendem Zeitwert der Startzeitpunkt und die Intervalllänge angegeben werden. Ein täglicher Refresh um 03:00 Uhr morgens, beginnend am nächsten Tag, lässt
sich wie folgt realisieren:
START WITH TRUNC(sysdate) + 3/24 NEXT trunc(sysdate + 1) + 3/24
TRUNC entfernt dabei die Stundenzahl des aktuellen Zeitwerts, den SYSDATE liefert. Danach kann die Zeit in
Tagen hinzuaddiert werden, wobei 3 genau 03:00 Uhr morgens ist.
• NEVER REFRESH: Es wird nie eine Aktualisierung durchgeführt.
• Über WITH PRIMARY KEY | ROWID wird der Bezug zur Mastertabelle hergestellt.
• FOR UPDATE gibt an, dass der materialisierte View nicht schreibgeschützt ist.
Bearbeiten Sie nun folgende Aufgabe:
Gegeben sei die Relation katalog(id, name, beschreibung, ek preis, vk preis). ek preis ist der Einkaufspreis, vk preis
der Verkaufspreis. Erstellen Sie die Relation in der Datenbank und füllen Sie sie mit ein paar Beispieldaten.
Für Geschäftskunden soll ein materialisierter View erstellt werden. Der Einkaufspreis soll ausgeblendet und der
Netto-Verkaufspreis ohne Mehrwertsteuer angezeigt werden (vk preis = netto preis ∗ 1, 19). Im View sollen keine
Veränderungen an Daten ausgeführt werden können. Ein (möglichst resourcensparendes) automatisches Update soll
täglich um 2:00 Uhr morgens ausgeführt werden.
2
Aufgabe 3: SQL: Formatierung (Oracle)
Von Übungsblatt 4, Aufgabe 4 kennen Sie bereits die Tabellen eines Gebrauchtwagenhändlers.
Geben Sie für jedes Auto aus USED_CARS_SOLD die Nationalität des Herstellers aus, aufsteigend sortiert nach ID.
Der Beginn der Ergebnisrelation soll wie folgt aussehen:
CARS
make
BMW
Fiat
BMW
Peugeot
...
country
Germany
Italy
Germany
France
...
color
black
blue
white
green
...
price
6482
5444
7041
4516
...
age
8
12
5
11
...
Die Nationalitäten der einzelnen Automarken erfahren Sie im Internet, zum Beispiel bei
http://www.wikipedia.de.
Aufgabe 4: Berichte
Die Tabelle BESTELLUNGEN von Übungsblatt 2 kennen Sie bereits. Erstellen Sie eine nach Monaten gegliederte
Umsatzaufstellung für das Jahr 2008. Das Ergebnis soll die folgende Form aufweisen:
UMSATZ
Monat
2008-01
2008-02
2008-03
2008-04
2008-05
2008-06
2008-07
2008-08
2008-09
2008-10
2008-11
2008-12
Betrag
0
0
0
500
0
700
500
700
800
1400
0
0
Eine mögliche Strategie zur Lösung der Aufgabe könnte wie folgt aussehen:
• Sie benötigen eine Zeile für jeden Monat. Überlegen Sie sich eine Abfrage, die (unabhängig von Tabelle
BESTELLUNGEN) ein Tupel für jeden Monat des Jahres 2008 zurückliefert.
• Für jeden Monat müssen Sie nun die vorhandenen Umsätze zusammenzählen bzw. 0 ausgeben.
• Eine passende Formatierung des Datumswerts erreichen Sie mit Hilfe der Funktion
TO_CHAR(datum, ’YYYY-MM’).
Aufgabe 5: Subqueries
Gegeben ist folgende unkorrelierte Subquery.
SELECT veranstaltung
FROM teilnahme
WHERE student IN (SELECT matrikelnr
FROM student
WHERE name = ’Maier’)
Ändern Sie die Anfrage ab, so dass anstelle der Subquery ein Join verwendet wird.
3