Musterlösung

Eidgenössische
Technische Hochschule
Zürich
Ecole polytechnique fédérale de Zurich
Politecnico federale di Zurigo
Swiss Federal Institute of Technology Zurich
Dr. C. Türker
Objektrelationale, erweiterbare Datenbanken
WS03/04
Übung 4
Musterlösung
Aufgabe 1: Klassen: Typen und Extensionen / Objektalgebra
Im Anwendungsszenario sollen diesmal die Klassen Artikel und Haushaltswaren
näher betrachtet werden.
1. Definieren Sie die Funktionen (Attribute) für die Klasse Artikel und deren Subklasse Haushaltswaren. Die UML-Assoziationen können hier vernachlässigt werden.
Lösungsvorschlag:
(a) Fkt(Artikel) = {Nummer, Bezeichnung, Preis, Beschreibung, Bilder }
(b) Fkt(Haushaltswaren) = Fkt(Artikel) ∪ {Garantie, Farbe}
Haushaltswaren werden im Folgenden mit H, Artikel mit A abgekürzt.
2. Geben Sie für die Klasse Haushaltswaren eine Projektion an, um nur die Funktionen der Superklasse zu erhalten.
Lösungsvorschlag:
R1 = π [Nummer(x), Bezeichnung(x), Preis(x), Beschreibung(x), Bilder(x)] (x
IN H)
3. Schreiben Sie eine Anfrage, um alle Haushaltswaren zu erhalten, die weniger
als 5 Franken kosten.
Lösungsvorschlag:
R2 = σ [Preis(x) < 5] (x IN H)
4. Lassen Sie sich von den eben ausgewählten Haushaltswaren die Attribute
Nummer, Bezeichnung, Preis und Farbe anzeigen.
Lösungsvorschlag:
R3 = π [Nummer(y), Bezeichnung(y), Preis(y), Farbe(y)] (y IN R2)
5. Lassen Sie sich nun die Garantie der Haushaltswaren anzeigen, die weniger
als 5 Franken kosten.
Lösungsvorschlag:
R4 = π [Garantie(y)] (y IN R2)
6. Geben Sie für alle Artikel, die mehr als 25 Schweizer Franken kosten, die
Nummer und den Preis sowohl in Schweizer Franken als auch in Euro aus.
Der Umrechnungskurs sei 1 SFR = 0,66 Euro.
Lösungsvorschlag:
R5 = π [Nummer(x), Preis(x), 0.66 * Preis(x)] (σ [Preis(x) > 25] (x IN A))
7. Zeichnen Sie die Typ- bzw. Extensionshierarchien für obige Klassen und
Anfrageergebnisse auf und ermitteln Sie daraus die Ober-/UnterklassenBeziehungen.
Lösungsvorschlag:
Super-/Suptypbeziehungen:
(a) A=R1 ≥ H = R2
(b) R3 ≥ H
(c) R4 ≥ H
Extensionsbeziehungen:
(a) A ⊇ H = R1 ⊇ R2 = R3 = R4
(b) A ⊇ R5
Klassenbeziehungen:
(a) A À R1 À H À R2
(b) R3 À R2
(c) R4 À R2
Aufgabe 2: Distinct-Typen und Casting in SQL:1999
Zwei verschiedene Unternehmen, die jeweils über getrennte Materialdatenbanken
verfügen, wollen zusammenarbeiten. Da ein Unternehmen in Europa, das andere
in den USA sitzt, werden aber die Schmelztemperaturen der Materialien einmal
in Grad Celsius und einmal in Fahrenheit gespeichert. Nun soll trotzdem eine
einheitliche Sicht auf diese Daten möglich sein.
1. Definieren Sie in SQL:1999 entsprechende Distinct-Typen für die verschiedenen Einheiten, um Temperaturwerte speichern zu können.
Lösungsvorschlag:
CREATE TYPE Celsius AS DECIMAL(5,2) FINAL;
CREATE TYPE Fahrenheit AS DECIMAL(5,2) FINAL;
2. Definieren Sie in SQL:1999 eine Konvertierungsfunktion, um ein implizites
Casting zu ermöglichen. Hinweis: TF ahrenheit = TCelsius ∗ 9/5 + 32
Lösungsvorschlag:
CREATE FUNCTION Fahrenheit(c Celsius)
RETURNS Fahrenheit
RETURN Fahrenheit(CAST(c AS DECIMAL(5, 2)) * 9 / 5 + 32));
-- Auch möglich:
-- RETURN CAST((CAST(c AS DECIMAL(5, 2)) * 9 / 5 + 32)
AS Fahrenheit);
CREATE CAST (Celsius AS Fahrenheit)
WITH FUNCTION Fahrenheit(Celsius) AS ASSIGNMENT;
3. Es sollen die Schmelztemperaturen von Materialien verglichen werden,
die sich in verschiedenen Datenbanken befinden. Wenn man hierbei beispielsweise für ein Material aus der europäischen Datenbank alle Materialien aus der amerikanischen Datenbank ausgegeben bekommen möchte,
die eine niedrigere Schmelztemperatur haben, so müssen Werte der
Schmelztemperatur-Attributen der beiden Datenbanken verglichen werden.
Was passiert in einem solchen Falle, falls der Vergleich
(a) ohne Cast,
(b) mit explizitem Cast auf den Basisdatentyp bzw.
(c) mittels implizitem Cast (aus der vorigen Teilaufgabe)
erfolgt.
Lösungsvorschlag:
(a) Ohne Cast dürfen Werte verschiedener Distinct-Typen nicht verglichen
werden, weil SQL:1999 streng typisiert ist. Es würde also ein Typfehler
erzeugt.
Eine entsprechende (unzulässige) Anfrage hätte hierbei die folgende
Form:
SELECT u.id
FROM EuroMaterial e, USMaterial u
WHERE e.ID = 101 AND e.Schmelztemp < u.Schmelztemp
(b) Nach einem (einfachen) expliziten Cast auf den Basistyp sind die beiden Werte vergleichbar, d.h. eine solche Operation ist nun syntaktisch
zulässig. Allerdings geht mit diesem Cast die Semantik verloren, da –
anschaulich gesprochen – einfach die Einheiten weggelassen werden. So
sind nun 100 Grad Celsius und 212 Fahrenheit ungleich, oder anders 10
Grad Celsius wären gleich 10 Fahrenheit, was natürlich nicht korrekt ist.
Eine syntaktisch zulässige, aber sinnlose Anfrage ist zum Beispiel:
SELECT u.id
FROM EuroMaterial e, USMaterial u
WHERE e.ID = 101 AND
CAST(e.Schmelztemp AS DECIMAL(5, 2))
< CAST(u.Schmelztemp AS DECIMAL(5, 2));
(c) Jetzt geschieht genau das, was man erwartet: Die beiden Werte werden
miteinander verglichen, wobei eine Umrechnung der Einheiten erfolgt.
Die Anfrage sieht in diesem Falle genauso wie bei Punkt (a) aus!
SELECT u.id, u.name
FROM EuroMaterial e, USMaterial u
WHERE e.ID = 101 AND e.Schmelztemp < u.Schmelztemp
Anzumerken ist noch, dass natürlich auch ein impliziter Cast sinnlos
definiert werden kann, während bei Angabe der richtigen Umrechungsfunktion auch ein expliziter Cast das richtige Ergebnis liefert.
4. Setzen Sie die obigen Teilaufgaben — soweit es geht — mittels DB2-SQL um.
Beachten Sie dabei kleinere syntaktische Unterschiede zu SQL:1999. Für die
DB2-Syntax sei auf das DB2-Manual SQL Reference“ verwiesen.
”
Folgende Aufgaben sollten auf jeden Fall umgesetzt werden:
• Definition der Distinct-Typen Celsius und Fahrenheit.
• Definition der Tabellen EuroMaterial(ID, Schmelztemparatur in Celsius)
und USMaterial(ID, Schmelztemparatur in Fahrenheit).
• Erzeugen von Beispielzeilen für beide Tabellen. Dabei sollte ein EuroMaterial mit der ID=101 enthalten sein.
• Definition einer Funktion, die Celsius in Fahrenheit umrechnet.
• Formulierung einer Anfrage, die alle US-Materialen ermittelt, die eine
höhere Schmelztemparatur haben, als das Euro-Material mit der ID=101.
• Definition einer Sicht, die alle Euro- und US-Materialien mit ihrer
Schmelztemparatur in Fahrenheit enthält.
Da IBM DB2 Version 7.2 keine benutzerdefinierten Casts unterstützt, müssen
die Konvertierungen explizit durchgeführt werden.
Lösungsvorschlag:
CREATE DISTINCT TYPE Celsius AS DECIMAL(5,2)
WITH COMPARISONS;
CREATE DISTINCT TYPE Fahrenheit AS DECIMAL(5,2)
WITH COMPARISONS;
CREATE FUNCTION Fahrenheit(c Celsius)
RETURNS Fahrenheit
RETURN Fahrenheit(CAST(c AS DECIMAL(5, 2)) * 9 / 5 + 32);
CREATE TABLE EuroMaterial (ID INT, Schmelztemp Celsius);
CREATE TABLE USMaterial (ID INT, Schmelztemp Fahrenheit);
INSERT INTO EuroMaterial VALUES (101, Celsius(78.5));
INSERT INTO EuroMaterial VALUES (102, Celsius(69.23));
INSERT INTO EuroMaterial VALUES (103, Celsius(99.71));
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
USMaterial
USMaterial
USMaterial
USMaterial
VALUES
VALUES
VALUES
VALUES
(201,
(202,
(203,
(204,
Fahrenheit(198.5));
Fahrenheit(192.3));
Fahrenheit(141.9));
Fahrenheit(170.2));
SELECT e.ID AS EuroMaterial,
Fahrenheit(e.Schmelztemp) AS Temp,
u.ID AS USMaterial,
u.Schmelztemp AS Temp
FROM EuroMaterial e, USMaterial u
WHERE e.ID = 101 AND Fahrenheit(e.Schmelztemp) < u.Schmelztemp;
CREATE VIEW Material(ID, SchmelztempFahrenheit) AS
SELECT ID, Fahrenheit(Schmelztemp)
FROM EuroMaterial
UNION
SELECT ID, Schmelztemp
FROM USMaterial;