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 7
Musterlösung
Aufgabe 1: Typisierte Sichten
Auf den vorherigen Übungsblättern sind Typ- und Tabellenhierarchien für Kunden, Angestellte, Personen und Bestellungen angegeben worden.
1. Realisieren Sie unter Einsatz typisierter Sichten folgende Dienste“ mittels
”
SQL:1999:
(a) Einem Adresshandel werden die Nachnamen, die Adressen und die Telefonnummern aller Kunden verkauft. Stellen Sie eine typisierte Sicht zur
Verfügung, wobei keine weiteren Schutzmechanismen vorgesehen werden müssen.
CREATE TYPE AdresshandelTyp AS
(
Nachname VARCHAR(30),
Anschrift AdressTyp,
Telefonnummern VARCHAR(20) ARRAY[3]
)
NOT FINAL
REF FROM Nachname;
CREATE VIEW Adresshandel OF AdresshandelTyp
(
REF IS oid DERIVED
)
AS SELECT Name, Adresse, Telefone
FROM ONLY(Kunden);
(b) Den Mitarbeitern einer Telefonmarketingfirma sollen nur der Nachname und die Telefonnummern von Kunden angezeigt werden, wobei
natürlich nur die Kunden von Interesse sind, die auch ein Telefon haben.
Diese Firma darf und soll allerdings auch die Telefonnummern aktualisieren können. Es muss aber sichergestellt sein, dass auch anschliessend
ein Kunde noch über mindestens eine Telefonnummer verfügt.
CREATE TYPE TelefonmarketingTyp AS
(
Nachname
VARCHAR(30),
Telefonnummern VARCHAR(20) ARRAY[3]
)
NOT FINAL
REF USING VARCHAR(30);
CREATE VIEW Telefonmarketing OF TelefonmarketingTyp
(
REF IS oid USER GENERATED
)
AS SELECT TelefonmarketingTyp(Name), Name, Telefone
FROM ONLY(Kunden)
WHERE CARDINALITY(Telefone)>0
WITH CHECK OPTION;
(c) Die Telefonmarketingfirma möchte nun nur die Kunden anrufen, die eine Telefonnummer in Zürich haben. Wie ermitteln Sie diese Kunden?
SELECT *
FROM Telefonmarketing
WHERE Telefonnummern[1] LIKE "0041-1%" OR
Telefonnummern[2] LIKE "0041-1%" OR
Telefonnummern[3] LIKE "0041-1%";
2. Setzen Sie nun die obigen Aufgaben mittels DB2SQL um!
Die Umsetzung in DB2SQL muss entsprechend der vorangegangenen Musterlösungen angepasst werden. Wir definieren zunächst eine Tabelle, in denen wir die Telefonnummern der Kunden verwalten und füllen diese mit
Beispieldaten. Aus Demonstrationsgründen spezifizieren wir einen strukturierten Typ für Anschriften, mit dem wir die Generierung von strukturierten
Werten aus einzelnen atomaren Werten zeigen wollen. Diesem Typ geben wir
auch zwei Methoden mit, welche die Anschriften als VARCHAR in einer besonderen Form zurückliefern.
Der AdresshandelTyp wird gemäss den DB2SQL-Vorgaben umgesetzt. Da
DB2 keine Kollektionen unterstützt, definieren wir drei Attribute für die maximal drei zulässigen Telefonnummern.
CREATE TABLE Telefone
(
Person REF(PersonTyp),
Nummer1 VARCHAR(20),
Nummer2 VARCHAR(20),
Nummer3 VARCHAR(20)
) $
INSERT INTO Telefone
VALUES ((SELECT oid FROM Kunden WHERE Kundennummer = 1234),
’0041-1-9876543’, ’0049-172-3432222’, ’0049-611-6733219’) $
INSERT INTO Telefone
VALUES ((SELECT oid FROM Kunden WHERE Kundennummer = 2345),
’0041-1-2612345’, NULL, NULL) $
CREATE TYPE AdressTyp AS
(
Strasse VARCHAR(30),
Nr DECIMAL(4),
PLZ DECIMAL(5),
Ort VARCHAR(25),
Land VARCHAR(20)
)
MODE DB2SQL
NOT FINAL
WITH FUNCTION ACCESS
REF USING INTEGER
METHOD Wohnort() RETURNS VARCHAR(31) LANGUAGE SQL,
METHOD Adresse() RETURNS VARCHAR(89) LANGUAGE SQL $
CREATE METHOD Wohnort()
RETURNS VARCHAR(31)
FOR AdressTyp
RETURN (RTRIM(CAST(CAST(SELF..PLZ AS INT) AS CHAR(5))) || ’ ’ ||
SELF..Ort) $
CREATE METHOD Adresse()
RETURNS VARCHAR(87)
FOR AdressTyp
RETURN (SELF..Strasse || ’ ’ ||
RTRIM(CAST(CAST(SELF..Nr AS INT) AS CHAR(5))) || ’, ’ ||
RTRIM(CAST(CAST(SELF..PLZ AS INT) AS CHAR(5))) || ’ ’ ||
SELF..Ort || ’, ’ ||
SELF..Land) $
CREATE TYPE AdresshandelTyp AS
(
Nachname VARCHAR(30),
Anschrift AdressTyp,
Telefon1 VARCHAR(20),
Telefon2 VARCHAR(20),
Telefon3 VARCHAR(20)
)
MODE DB2SQL
NOT FINAL $
CREATE VIEW Adresshandel OF AdresshandelTyp MODE DB2SQL
(
REF IS oid USER GENERATED UNCHECKED
)
AS
SELECT AdresshandelTyp(CAST(Kundennummer AS CHAR(13))),
Name,
AdressTyp()..Strasse(Strasse)..Nr(Nr)..PLZ(PLZ)..Ort(Ort)),
(SELECT Nummer1 FROM Telefone WHERE Person = k.oid),
(SELECT Nummer2 FROM Telefone WHERE Person = k.oid),
(SELECT Nummer3 FROM Telefone WHERE Person = k.oid)
FROM ONLY(Kunden) k$
-- Die Sicht Adresshandel ist nicht änderbar. Typisierte Sichten, die
-- berechnete Attribute haben, sind generell nicht in DB2 änderbar!
SELECT oid, Nachname, Anschrift..Adresse() AS Anschrift,
Telefon1, Telefon2, Telefon3
FROM Adresshandel $
SELECT Nachname,
CASE WHEN Telefon1 LIKE ’0041-1%’ THEN Telefon1 END AS T1,
CASE WHEN Telefon2 LIKE ’0041-1%’ THEN Telefon2 END AS T2,
CASE WHEN Telefon3 LIKE ’0041-1%’ THEN Telefon3 END AS T3
FROM Adresshandel
WHERE Telefon1 LIKE ’0041-1%’ OR
Telefon2 LIKE ’0041-1%’ OR
Telefon3 LIKE ’0041-1%’ $
CREATE TYPE MarketingTyp AS
(
Nachname VARCHAR(30),
Telefon1 VARCHAR(20),
Telefon2 VARCHAR(20),
Telefon3 VARCHAR(20)
)
MODE DB2SQL
NOT FINAL $
CREATE VIEW Telefonmarketing OF MarketingTyp MODE
(
REF IS oid USER GENERATED UNCHECKED
)
AS
SELECT MarketingTyp(Name),
Name,
(SELECT Nummer1 FROM Telefone WHERE Person
(SELECT Nummer2 FROM Telefone WHERE Person
(SELECT Nummer3 FROM Telefone WHERE Person
FROM ONLY(Kunden) k
WHERE EXISTS(SELECT *
FROM Telefone
WHERE Person = k.oid AND (Nummer1 IS
OR Nummer2 IS
OR Nummer3 IS
DB2SQL
= k.oid),
= k.oid),
= k.oid)
NOT NULL
NOT NULL
NOT NULL)) $
INSERT INTO Kunden
VALUES (KundeTyp(’123459’), ’Richie’, ’Bahnhofstrasse’, 13,
8006, ’Zürich’, CURRENT_DATE, ’[email protected]’,
7733, Franken(4500)) $
SELECT *
FROM Telefonmarketing
WHERE Telefon1 LIKE ’0041-1%’ OR
Telefon2 LIKE ’0041-1%’ OR
Telefon3 LIKE ’0041-1%’ $
Aufgabe 2: Rekursion
Gegeben sei folgende Definition einer Tabelle, die Verwandtschaftsbeziehungen
verwaltet:
CREATE TABLE Stammbaum
(
MenschID
INTEGER PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
GebDatum
Mutter
Vater
DATE,
INTEGER REFERENCES Stammbaum,
INTEGER REFERENCES Stammbaum
);
1. Ermitteln Sie mittels SQL:1999 alle Vorfahren von ’Jim’.
WITH RECURSIVE Vorfahren(MenschID, Vorfahr) AS
(
SELECT MenschID, Mutter
FROM Stammbaum
WHERE Name = ’Jim’
UNION
SELECT MenschID, Vater
FROM Stammbaum
WHERE Name = ’Jim’
UNION
SELECT v.MenschID, s.Mutter
FROM Vorfahren v, Stammbaum s
WHERE v.Vorfahr = s.MenschID
UNION
SELECT v.MenschID, s.Vater
FROM Vorfahren v, Stammbaum s
WHERE v.Vorfahr = s.MenschID
)
SELECT *
FROM Vorfahren
WHERE Vorfahr IS NOT NULL
ORDER BY MenschID;
2. Ermitteln Sie mittels SQL:1999 alle Nachkommen von ’Bob’.
WITH RECURSIVE Nachkommen(MenschID, Nachkomme) AS
(
SELECT Mutter, MenschID
FROM Stammbaum
UNION
SELECT Vater, MenschID
FROM Stammbaum
UNION
SELECT v.MenschID, s.MenschID
FROM Nachkommen v, Stammbaum s
WHERE v.Nachkomme = s.Mutter
UNION
SELECT v.MenschID, s.MenschID
FROM Nachkommen v, Stammbaum s
WHERE v.Nachkomme = s.Vater
)
SELECT *
FROM Nachkommen
WHERE MenschID IN (SELECT MenschID
FROM Stammbaum
WHERE Name = ’Bob’)
ORDER BY MenschID;
3. Setzen Sie die obige Tabelle und die rekursiven Anfragen in DB2 um.
DROP TABLE Stammbaum $
CREATE TABLE Stammbaum
(
MenschID
INTEGER NOT NULL PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
GebDatum
DATE,
Mutter
INTEGER REFERENCES Stammbaum,
Vater
INTEGER REFERENCES Stammbaum
) $
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
Stammbaum
Stammbaum
Stammbaum
Stammbaum
Stammbaum
Stammbaum
Stammbaum
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
(1,
(2,
(7,
(3,
(4,
(5,
(6,
’Bob’, ’11-29-1927’, NULL, NULL) $
’Joe’, ’10-23-1952’, NULL, 1) $
’Mark’, ’08-30-1957’, NULL, NULL) $
’Joanna’, ’09-12-1954’, NULL, NULL) $
’Kim’, ’03-15-1997’, 3, 2) $
’Jim’, ’12-24-1995’, 3, 2) $
’Cloe’, ’05-07-1999’, 3, 7) $
WITH Vorfahren(MenschID, Vorfahr) AS
(
SELECT MenschID, Mutter
FROM Stammbaum
WHERE Name = ’Jim’
UNION ALL
SELECT MenschID, Vater
FROM Stammbaum
WHERE Name = ’Jim’
UNION ALL
SELECT v.MenschID, s.Mutter
FROM Vorfahren v, Stammbaum s
WHERE v.Vorfahr = s.MenschID
UNION ALL
SELECT v.MenschID, s.Vater
FROM Vorfahren v, Stammbaum s
WHERE v.Vorfahr = s.MenschID
)
SELECT *
FROM Vorfahren
WHERE Vorfahr IS NOT NULL
ORDER BY MenschID $
WITH Nachkommen(MenschID, Nachkomme) AS
(
SELECT Mutter, MenschID
FROM Stammbaum
UNION ALL
SELECT Vater, MenschID
FROM Stammbaum
UNION ALL
SELECT v.MenschID, s.MenschID
FROM Nachkommen v, Stammbaum s
WHERE v.Nachkomme = s.Mutter
UNION ALL
SELECT v.MenschID, s.MenschID
FROM Nachkommen v, Stammbaum s
WHERE v.Nachkomme = s.Vater
)
SELECT *
FROM Nachkommen
WHERE MenschID IN (SELECT MenschID
FROM Stammbaum
WHERE Name = ’Bob’)
ORDER BY MenschID $