Übungsblatt 04 - Institut für Informatik

Universität Augsburg, Institut für Informatik
Prof. Dr. W. Kießling
Dr. M. Endres, Dr.-Ing. S. Mandl
SS 2011
27. Mai. 2011
Übungsblatt 4
Datenbankprogrammierung (Oracle)
Aufgabe 1: Zyklische Constraints
Im Skript (Kapitel 4 - Teil 2, Folie 37) wurde die Transformation einer 1:1 Beziehung ohne optionale Entitäts-Typen
dargestellt. Bearbeiten Sie folgende Teilaufgaben:
a) Erstellen Sie die Relationen Person, Handy und R. Verwenden Sie anstelle von R einen sinnvollen Namen.
b) Fügen Sie in alle Tabellen beliebige Daten ein. Auf welche Probleme stoßen Sie dabei und wie sind diese zu lösen?
Allgemeine Anmerkung: Das Anlegen von FOREIGN KEYs auf nicht primäre oder nicht eindeutige Attribute wird
von Oracle nicht unterstützt. Hier müssen alternative Darstellungen gewählt werden, z.B. die Zusammenfassung zu
einer Tabelle.
Aufgabe 2: Vereinigung von ER-Diagrammen
Gegeben seien die folgenden ER-Diagramme:
publishes
Publisher
contains
written_at
Book
Institution
Publication
Keyword
title
name
name
contains
address
title
title
address
Topic-area
code
publisher
name
code
research-area
Bearbeiten Sie folgende Aufgaben:
a) Verbinden Sie die beiden Diagramme zu einem Gesamt-Diagramm. Beachten Sie dabei die folgenden Punkte:
• Ein Buch ist eine Publikation. Bringen Sie diese Vererbung im Diagramm unter.
• Ein Attribut aus einem Diagramm kann im Gesamt-Diagramm manchmal sinnvoller als Entität modelliert
werden.
• In den beiden ursprünglichen Diagrammen können an sich semantisch identische Attribute oder Entitäten
unterschiedlich benannt worden sein.
• Es gibt beim Verbinden mehrerer ER-Diagramme im Allgemeinen keine eindeutig beste Lösung.
b) Eine weitere Art von Publikation soll eingefügt werden: Zeitschriften. Eine Zeitschrift besitzt Herausgeber (Publisher), Titel, Erscheinungsintervall und genau einen Themenbereich (Topic). Integrieren Sie
Zeitschrift als Entität sinnvoll in Ihr Diagramm.
c) Transformieren Sie ihr Diagramm mit Hilfe des Oracle Data Modelers in entsprechende SQL-Anweisungen. Überprüfen Sie das Ergebnis auf Konsistenz und Integritätbedingungen.
1
Aufgabe 3: Views (Standard SQL)
Views sind virtuelle Relationen, die nur die Definitionen, aber nicht den Inhalt speichern. Views werden für Benutzersichten, z.B. zum Ausblenden von Informationen, verwendet. Die Syntax zum Anlegen eines Views ist:
CREATE VIEW <name> (<attr1>, ..., <attrn>) AS
SELECT <arg1>, ..., <argn>
FROM ...
[WITH {CHECK OPTION | READ ONLY}]
-- relevant bei updatable Views.
-- Es wird getestet, ob geaenderte
-- bzw. eingefuegte Tupel im View sichtbar sind.
a) Können Views auf Views definiert werden? Können diese auch zyklisch sein? Anmerkung: Die Tabellen, auf die
sich Views stützen, werden Mastertabellen genannt.
b) Wann können die Daten von Views geändert werden (Stichwort UPDATABLE VIEWS).
c) Wie werden Views gelöscht? Was passiert dann mit der Mastertabelle? Falls die Mastertabelle gelöscht wird, wird
dann auch der View gelöscht?
d) Legen Sie die Tabelle Mastertabelle mit ihrem Inhalt an. Erstellen Sie den angegebenen View Mt View,
welcher ID und Betrag Euro enthalten soll. Im View sollen nur Tupel sichtbar sein, deren Beträge ≥ 500 Euro
sind.
Mastertable
ID
1
2
3
Datum
20.04.2011
03.05.2011
NULL
Betrag
200
700
NULL
Mt View
ID
1
2
3
Betrag Euro
200
700
NULL
i) Was passiert, wenn Sie dem View Daten hinzufügen? Sind diese auch in der Mastertabelle sichtbar?
ii) Was passiert, wenn Sie der Mastertabelle Daten hinzufügen? Sind diese auch im View sichtbar? Welchen
Einfluß hat dabei die WHERE Klausel beim Erstellen des Views. Was passiert in diesem Fall ohne WITH
CHECK OPTION?
iii) Was passiert, wenn Datum mit NOT NULL gekennzeichnet ist. Was passiert beim Einfügen. Was passiert mit
Datum in der Mastertabelle?
Aufgabe 4: SQL (Standard SQL)
In der Oracle-Datenbank finden Sie die beiden folgenden Tabellen eines Gebrauchtwagenhändlers:
• used cars in stock (id, make, price, color, age)
• used cars sold (id, make, price, color, age)
Die erste Tabelle enthält Informationen über im Lager vorhandene Autos, die zweite über verkaufte Autos. Bearbeiten
Sie die folgenden Aufgaben:
a) Geben Sie eine Liste aller Fahrzeuge in der Datenbank aus. Für jedes Auto soll in einer zusätzlichen Spalte status
angegeben werden, ob es im Lager (”in stock”) oder bereits verkauft (”sold”) ist.
b) Autos mancher Marken verkaufen sich in bestimmten Farben nur sehr schlecht oder gar nicht. Geben Sie die Autos
aus dem Lager aus, für deren Kombination aus Marke und Farbe es keine Verkäufe gibt.
c) Geben Sie das billigste Fahrzeug aus, das bereits verkauft wurde. Benutzen Sie dazu keine Aggregationsfunktion!
d) Geben Sie das teuerste Fahrzeug aus, das entweder im Lager ist oder bereits verkauft wurde. Benutzen Sie auch
hier keine Aggregationsfunktion!
e) Ermitteln Sie diejenigen bereits verkauften Fahrzeuge, bei denen sowohl Preis als auch Alter höher waren als
bei anderen verkauften Fahrzeugen mit gleichem Hersteller und gleicher Farbe. Ordnen Sie die Fahrzeuge nach
Hersteller und Farbe.
f) Geben sie alle möglichen Kombinationen von Farbe und Hersteller aus. Gehen Sie davon aus, dass in der Datenbank
mindestens ein Auto jeder Marke und mindestens ein Auto jeder Farbe vorhanden ist (möglicherweise aber eben
nicht alle möglichen Kombinationen). Gibt es mindestens ein Auto in jeder dieser Kombinationen im Lager?
2