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 WS02/03 Übung 6 (Musterlösung) Besprechung: Mi. 11.12.2002, 17:15-18:00 h, IFW A32 Aufgabe 1: Benutzerdefinierte Ordnungsfunktionen in SQL:1999 1. In einer Datenbank sollen die Eigenschaften verschiedener Kunden grob charakterisiert abgelegt werden. Dazu wird angegeben, wieviel 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(Telefon) 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 Hobbies der Kunden erfasst. Lösungsvorschlag: CREATE TYPE VIPKundeTyp UNDER KundeTyp AS ( Hobbies 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 Hobbies aus. Lösungsvorschlag: SELECT Name, CASE WHEN DEREF(oid) IS OF (VIPKundeTyp) THEN TREAT(DEREF(oid) AS VIPKundeTyp).Hobbies END AS Hobbies 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 ( Hobbies 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)..Hobbies END AS Hobbies FROM Kunden $