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 6
Musterlösung
Aufgabe 1: Benutzerdefinierte Ordnungsfunktionen in SQL:1999
1. In einer Datenbank sollen die Eigenschaften verschiedener Kunden grob charakterisiert abgelegt werden. Dazu wird angegeben, wie viel Wert ein Kunde
auf Qualität, Service und Preis legt, um für ihn optimale Produkte anpreisen
zu können. Hierzu gibt es jeweils eine Skala von -10 bis 10, die man vereinfacht durch INTEGER darstellen kann.
Hierzu wird der folgende Typ KundenMerkmal definiert:
CREATE TYPE KundenMerkmal AS (
Qualitaet INTEGER,
Service INTEGER,
Preis INTEGER
) NOT FINAL;
(a) Welche der Ordnungsformen EQUALS ONLY oder ORDER FULL sollte
hier gewählt werden? Begründen Sie Ihre Entscheidung!
Lösungsvorschlag: EQUALS ONLY, weil es zunächst keine Anhaltspunkte gibt, wie die Ordnung aussehen sollte.
(b) Welche der Ordnungskategorien RELATIVE, MAP, STATE bietet sich
weshalb an?
Lösungsvorschlag: STATE u.a. weil vorher EQUALS ONLY gewählt wurde und keine weiteren Gleichheitsformen sinnvoll erscheinen.
(c) Setzen Sie die getroffenen Entscheidungen in ein SQL:1999-Konstrukt
um.
Lösungsvorschlag:
CREATE ORDERING FOR KundenMerkmal
EQUALS ONLY BY STATE;
2. Nun sollen anspruchsvolle“ Kunden, die also viel für wenig Geld wollen,
”
von weniger anspruchsvollen Kunden unterschieden werden können und
eine entsprechende Rangfolge“ aufgestellt werden, welche alle drei genann”
ten Kategorien zusammenfasst. Wieder ist eine Ordnungsform, eine Ordnungskategorie und eine Umsetzung in ein SQL:1999-Konstrukt gefragt.
Lösungsvorschlag: Da eine Rangfolge“ aufgestellt werden soll, ist als Ord”
nungsfunktion ORDER FULL zu verwenden, als Ordnungskategorie bietet
sich ein MAP an, wobei beispielsweise als Funktion KundenAnsprueche(q,
s, p) := q + s + p als Abbildungsfunktion verwendet werden könnte.
CREATE FUNCTION KundenAnsprueche(kc KundenMerkmal)
RETURNS INTEGER
RETURN kc.Qualitaet + kc.Service + kc.Preis;
CREATE ORDERING FOR KundenMerkmal
ORDER FULL BY
MAP WITH FUNCTION KundenAnsprueche(KundenMerkmal);
Aufgabe 2: Typisierte Tabellen/Typ- und Tabellenhierarchie
1. Auf Übungsblatt 5 sind Typen für Bestellungen, Personen, Kunden und Angestellte definiert worden. Definieren Sie darauf aufbauend typisierte Tabellen. Vergessen Sie dabei nicht, die Integritätsbedingungen aus dem ersten
Übungsblatt (UML-Schema) umzusetzen.
Lösungsvorschlag:
CREATE TABLE Bestellung OF BestellungTyp
( REF IS oid SYSTEM GENERATED );
CREATE TABLE Angestellter OF AngestellterTyp
(
REF IS oid SYSTEM GENERATED,
CHECK(CAST(Gehalt AS DECIMAL(12,2)) > 2000)
);
CREATE TABLE Kunden OF KundeTyp
(
REF IS oid SYSTEM GENERATED,
CHECK(CAST(Kreditlimit AS DECIMAL(12,2)) < 5000)
);
Achtung: Es können keine typisierten Tabellen für nicht-instantiierbare strukturierte Typen definiert werden. Daher können wir in diesem Fall auch keine
Tabellenhierarchie definieren.
2. Erzeugen Sie Angestellten- und Kundendaten!
Lösungsvorschlag:
INSERT INTO Angestellte
VALUES (’Jim’, ’Seestrasse’, 12, 8008, ’Zürich’,
CURRENT_DATE, ’[email protected]’, 1234, Franken(3000));
INSERT INTO Angestellte
VALUES (’Joe’, ’Seefeldstrasse’, 31, 8008, ’Zürich’,
DATE’09/30/1948’, ’[email protected]’, 2345, Franken(3500));
INSERT INTO Kunden
VALUES (’Kim’, ’Seefeldstrasse’, 31, 8008, ’Zürich’,
DATE’06/15/1965’, ’[email protected]’, 1234, Franken(1000));
INSERT INTO Kunden
VALUES (’Sam’, ’Seestrasse’, 11, 8008, ’Zürich’,
DATE’1952-11-13’, ’[email protected]’, 2345, Franken(5000));
3. Schreiben Sie folgende Anfragen:
(a) Welche Kunden haben ein Kreditlimit, das grösser als 4000 SFR ist?
Lösungsvorschlag:
SELECT *
FROM Kunde
WHERE CAST(Kreditlimit AS DECIMAL(12,2)) > 4000;
(b) Gesucht werden die Angestellten, die kein Telefon haben.
Lösungsvorschlag:
SELECT *
FROM Angestellte
WHERE Telefone IS NULL OR CARDINALITY(Telefone) = 0;
(c) Geben Sie alle Kunden aus, die ein Telefon mit einer Schweizer Vorwahl
haben. Hier sind zwei Varianten denkbar, um diese Menge zu erhalten.
Geben Sie beide an.
Lösungsvorschlag:
SELECT *
FROM Kunde
WHERE EXISTS (SELECT *
FROM UNNEST(Telefone) t
WHERE t LIKE ’0041%’);
SELECT *
FROM Kunde
WHERE (Telefone[1] LIKE ’0041%’) OR
(Telefone[2] LIKE ’0041%’) OR
(Telefone[3] LIKE ’0041%’);
Bei der zweiten Variante ist zu beachten, dass ein Laufzeitfehler vorliegt,
wenn bei einem Telefonarray nicht alle drei Felder gesetzt sind.
4. Nun wollen wir die VIP-Kunden“ gesondert verwalten. Definieren Sie einen
”
entsprechenden Subtyp, der als zusätzliches Merkmal die Hobbys der Kunden erfasst.
Lösungsvorschlag:
CREATE TYPE VIPKundeTyp UNDER KundeTyp AS
(
Hobbys VARCHAR(20) ARRAY[10]
)
NOT FINAL;
5. Definieren Sie basierend auf diesem Subtyp eine entsprechende Subtabelle
(unter Kunden).
Lösungsvorschlag:
CREATE TABLE VIPKunden OF VIPKundeTyp UNDER Kunden;
6. Erzeugen Sie einen VIP-Kunden.
Lösungsvorschlag:
INSERT INTO VIPKunden
VALUES (’Don’, ’Seeblick’, 5, 8008, ’Zürich’,
’1964-08-02’, 5678, Franken(10000),
’[email protected]’, ARRAY[’Schwimmen’, Tanzen’]);
7. Geben Sie für alle Kunden ihren Namen und ihren Typ (VIP vs. NON-VIP)
aus.
Lösungsvorschlag:
SELECT Name, CASE WHEN DEREF(oid) IS OF (VIPKundeTyp)
THEN ’VIP’
ELSE ’NON-VIP’
END AS Kundentyp
FROM Kunden;
oder alternativ: VIP-Information als boolesche Spalte:
SELECT Name, DEREF(oid) IS OF (VIPKundeTyp) AS VIP
FROM Kunden;
8. Geben Sie für alle Kunden ihren Namen und bei VIP-Kunden zusätzlich noch
ihre Hobbys aus.
Lösungsvorschlag:
SELECT Name, CASE WHEN DEREF(oid) IS OF (VIPKundeTyp)
THEN TREAT(DEREF(oid) AS VIPKundeTyp).Hobbys
END AS Hobbys
FROM Kunden;
Aufgabe 3: Umsetzung mit DB2SQL
Setzen Sie die Aufgabe 2 mit Hilfe von DB2SQL um. Beachten Sie dabei, dass DB2
keine Tupel- und Arraytypen kennt. Ausserdem erlaubt DB2 zwar die Angabe
von systemgenerierten OIDs, aber dennoch muss beim Insert die OID benutzerdefiniert mitgegeben werden. Dies geschieht durch den Aufruf des Typkonstruktors
mit einer in der Tabellenhierarchie eindeutigen Zeichenkette.
Lösungsvorschlag:
CREATE TABLE Bestellung OF BestellungTyp
( REF IS oid USER GENERATED ) $
CREATE TABLE Angestellte OF AngestellterTyp
(
REF IS oid USER GENERATED,
CHECK(CAST(Gehalt AS DECIMAL(12,2)) > 2000)
) $
CREATE TABLE Kunden OF KundeTyp
(
REF IS oid USER GENERATED,
CHECK(CAST(Kreditlimit AS DECIMAL(12,2)) < 5000)
) $
INSERT INTO Angestellte
VALUES (AngestellterTyp(’123456’), ’Jim’, ’Seestrasse’, 12,
8008, ’Zürich’, CURRENT_DATE, ’[email protected]’, 1234,
Franken(3000)) $
INSERT INTO Angestellte
VALUES (AngestellterTyp(’123457’), ’Joe’, ’Seefeldstrasse’, 31,
8008, ’Zürich’, ’09/30/1948’, ’[email protected]’, 2345,
Franken(3500)) $
INSERT INTO Kunden
VALUES (KundeTyp(’123456’), ’Kim’, ’Seefeldstrasse’, 31,
8008, ’Zürich’, ’06/15/1965’, ’[email protected]’, 1234,
Franken(1000)) $
INSERT INTO Kunden
VALUES (KundeTyp(’123457’), ’Sam’, ’Seestrasse’, 11,
8008, ’Zürich’, ’1952-11-13’, ’[email protected]’, 2345,
Franken(5000)) $
SELECT *
FROM Kunden
WHERE CAST(Kreditlimit AS DECIMAL(12,2)) > 4000 $
CREATE TYPE VIPKundeTyp UNDER KundeTyp AS
(
Hobbys VARCHAR(250)
) MODE DB2SQL
NOT FINAL $
CREATE TABLE VIPKunden OF VIPKundeTyp
UNDER Kunden
INHERIT SELECT PRIVILEGES $
INSERT INTO VIPKunden
VALUES (VIPKundeTyp(’123458’), ’Don’, ’Seeblick’, 5,
8008, ’Zürich’, ’1964-08-02’, ’[email protected]’, 5678,
Franken(10000), ’Schwimmen Tanzen’) $
SELECT Name, CASE WHEN DEREF(oid) IS OF (VIPKundeTyp)
THEN ’VIP’
ELSE ’NON-VIP’
END AS Kundentyp
FROM Kunden $
SELECT Name, CASE WHEN DEREF(oid) IS OF (VIPKundeTyp)
THEN TREAT(DEREF(oid) AS VIPKundeTyp)..Hobbys
END AS Hobbys
FROM Kunden $