Blatt 04 - Informationssysteme

Uni Duisburg
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
Übungsblatt 4
Kai Großjohann
Aufgabe 1:
Abgabe bis 13. November 2002
Lieferanten-Beispiel
Zur Erinnerung: In unserer Miniwelt sollen Lieferanten Produkte zu bestimmten
Preisen liefern. Daraus ergibt sich eine Relation mit folgendem Schema:
Lief : {[ LName : string, PName : string, Preis : integer ]}
Die natürlichsprachige Formulierung der Anfragen ist:
1. Welche Lieferanten liefern Milch?
2. Welche Lieferanten liefern Eier?
3. Welche Lieferanten liefern Milch oder Eier?
4. Welche Lieferanten liefern sowohl Milch als auch Eier?
5. Wer liefert Milch am billigsten?
6. Wer ist der zweitbilligste Milchlieferant?
7. Wie teuer sind Eier beim billigsten Milchlieferanten?
8. Welche Lieferanten liefern Eier billiger als der billigste Milchlieferant?
Formuliere diese Anfragen in SQL. Verwende dazu die freie SQL-Schnittstelle
in Passau, deren URL laut Vorlesung wie folgt lautet:
http://www.db.fmi.uni-passau.de/local/db2sql/
Auf der Webseite der Vorlesung gibt es einen Link auf ein SQL-Skript, mit dem
man die Tabelle lief löschen und dann neu erzeugen kann. Da die Daten in
der dortigen Datenbank regelmäßig gelöscht werden, könnte dieses Skript von
Nöten sein. Das Skript ist auch hier in Tabelle 1 abgedruckt. Falls du denkst, es
wäre besser, noch weitere Beispiel-Tupel hinzuzfügen, so schicke mir eine Mail.
Die Verwendung von min, max, avg, count (und den anderen Aggregationsfunktionen) sowie von geschachtelten SELECTs ist verboten.
3 Punkte
Aufgabe 2:
Zusammenhang mit Algebra und Kalkül
Schreibe die Relationenalgebra-Ausdrücke zu „Welche Lieferanten liefern Milch
oder Eier“ und zu „Welche Lieferanten liefern sowohl Milch als auch Eier“ hin.
Dokumentiere an diesen beiden Beispielen, wie man Relationenalgebraausdrücke
in SQL-Anfragen umwandelt.
Schreibe die gleichen zwei Anfragen in Tupelkalkül oder Bereichskalkül hin.
Dokumentiere an diesen Beispielen, wie man Kalkülausdrücke in SQL-Anfragen
umwandelt.
Übungsblatt 4
Seite 1 von 3
Uni Duisburg
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
DROP TABLE lief;
CREATE TABLE lief(
lname
varchar(30) not null,
pname
varchar(30) not null,
preis
integer not null);
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
lief
lief
lief
lief
lief
lief
lief
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(’schmidt’, ’milch’, 10);
(’schmidt’, ’eier’, 10);
(’mueller’, ’milch’, 20);
(’huber’, ’milch’, 10);
(’hahn’, ’eier’, 5);
(’metzger’, ’schnitzel’, 25);
(’gruen’, ’schnittlauch’, 1);
Tabelle 1: SQL-Skript zum Erzeugen der Tabelle lief.
Unter „dokumentieren“ verstehe ich eine Erklärung, aus der die einzelnen Schritte ersichtlich sind. Es reicht nicht, die SQL-Anfragen hinzuschreiben! Zum besseren Verständnis ist es vermutlich günstig, in mehreren Schritten vorzugehen
und jeden Schritt zu erläutern.
Vergleiche die entstandenen SQL-Anfragen. Welche Anfragen sind besser? Welche sind schöner? Warum? Wie gut ist der Zusammenhang zwischen der Algebra
und SQL bzw. der Zusammenhang zwischen dem Kalkül und SQL zu erkennen?
Warum kommt in den SQL-Anfragen keine Aggregationsfunktion vor?
3 Punkte
Aufgabe 3:
SQL
Hier sind Aggregationsfunktionen erlaubt. Formuliere folgende Anfragen in SQL.
Verwende Zwischenschritte und erläutere dein Vorgehen.
• Wer liefert Milch am billigsten?
• Wieviele Lieferanten liefern Eier billiger als der billigste Milchlieferant?
• Welches Produkt hat den niedrigsten Durchschnittspreis?
• Welches Produkt hat den niedrigsten minimalen Preis?
2 Punkte
Aufgabe 4:
Krankenhausbeispiel
Formuliere in SQL die beiden Anfragen aus Blatt 2, Aufgabe 3:
• Welche Gerichte darf Herr Schmidt nicht essen?
• Wieviel Salz ist höchstens in den Gerichten, die Herr Schmidt essen darf?
Erläutere dein Vorgehen.
1 Punkt
Übungsblatt 4
Seite 2 von 3
Uni Duisburg
Aufgabe 5:
Fachgebiet Informationssysteme
Prof. Dr. N. Fuhr
SQL
Seien P und Q zwei Select-Statements auf der gleichen Relation,1 deren Ergebnisse das gleiche Schema haben. Gib einen Algorithmus an, wie man P union
Q in einem Select-Statement ohne union ausdrücken kann. Gib einen Algorithmus an, wie man P intersect Q in einem Select-Statement ohne intersect
ausdrücken kann.
1 Punkt
Aufgabe 6:
Obfuscated SQL Contest
You know the drill. . . Schreibe eine schön scheußliche SQL-Anfrage für das Lieferanten-Beispiel hin und erkläre sie.2
1 Gemeint
2 Ich
ist die in der From-Klausel angegebene Relation.
nehme nicht teil.
Übungsblatt 4
Seite 3 von 3