Objektrelationale, erweiterbare Datenbanken WS 04/05 Übung 8

Institut für Informationssysteme
Dr. C. Türker
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
END;
/
Objektrelationale, erweiterbare Datenbanken WS 04/05
Übung 8
Beispiellösung
Aufgabe 1: Objektrelationales Oracle-SQL
Setzen Sie die Klassenhierarchie Artikel (Klassen: Artikel, ArtikelSet, Lebensmittel, Haushaltswaren) und die Klasse Katalog aus dem UML-Diagramm von Übung 1 mit Hilfe der objektrelationalen Erweiterungen von Oracle um. Verwenden Sie dabei Objekttypen-/tabellen.
Beispiellösung: Oracle unterstützt keine Subtabellen. Daher muss die UML-Klassenhierarchie
auf einem anderen Weg auf Oracle-SQL abgebildet werden. Die folgende Lösung nutzt die
Konzepte der Objekttypen, Subtypbildung und Substituierbarkeit, um die komplette Klassenhierarchie auf eine Objekttabelle abzubilden, wobei die einzelnen Klassen durch Objektsichten
repräsentiert werden.
Bei den Aggregationsbeziehungen wurden die Kardinalitäten vernachlässigt.
CREATE TYPE BildKollektionsTyp AS TABLE OF BLOB;
/
CREATE TYPE ArtikelTyp AS OBJECT (
Nummer
CHAR(12),
Bezeichnung VARCHAR(40),
Preis
DECIMAL(12,2),
Beschreibung VARCHAR(2000),
Bilder
BildKollektionsTyp
) NOT FINAL;
/
CREATE TYPE LebensmittelTyp UNDER ArtikelTyp (
Gewicht DECIMAL(10),
Zusammensetzung VARCHAR(400),
MEMBER FUNCTION GewichtInKilo RETURN INTEGER
) FINAL;
/
CREATE TYPE BODY LebensmittelTyp AS
MEMBER FUNCTION GewichtInKilo RETURN INTEGER AS
BEGIN
RETURN SELF.Gewicht / 1000;
END;
END;
/
CREATE TYPE HaushaltswarenTyp UNDER ArtikelTyp (
Garantie DATE,
Farbe
VARCHAR(30)
) FINAL;
/
CREATE TYPE KatalogTyp AS OBJECT (
Titel
VARCHAR(30),
Jahr
DECIMAL(4),
Sommer CHAR(1),
Artikel ArtikelKollektionsTyp,
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER
) NOT FINAL;
/
CREATE TYPE ArtikelKollektionsTyp AS TABLE OF REF ArtikelTyp;
/
CREATE TYPE ArtikelSetTyp UNDER ArtikelTyp (
Artikel
ArtikelKollektionsTyp,
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER
) FINAL;
/
CREATE TYPE BODY KatalogTyp AS
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER AS
BEGIN
RETURN SELF.Artikel.COUNT;
END;
END;
/
CREATE TYPE BODY ArtikelSetTyp AS
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER AS
BEGIN
RETURN SELF.Artikel.COUNT;
END;
CREATE TABLE Katalog OF KatalogTyp (
PRIMARY KEY(Jahr, Sommer),
Titel NOT NULL
)
NESTED TABLE Artikel STORE AS KatalogArtikel;
1
2
CREATE TABLE Artikel OF ArtikelTyp (
Nummer
PRIMARY KEY,
Bezeichnung NOT NULL
)
NESTED TABLE Bilder STORE AS ArtikelBilder;
CREATE VIEW ArtikelSet OF ArtikelSetTyp
AS SELECT TREAT(VALUE(a) AS ArtikelSetTyp)
FROM Artikel a
WHERE VALUE(a) IS OF (ONLY ArtikelSetTyp);
CREATE VIEW AllgemeineArtikel OF ArtikelTyp
AS SELECT TREAT(VALUE(a) AS ArtikelTyp)
FROM Artikel a
WHERE VALUE(a) IS OF (ONLY ArtikelTyp);
CREATE VIEW Lebensmittel OF LebensmittelTyp
AS SELECT TREAT(VALUE(a) AS LebensmittelTyp)
FROM Artikel a
WHERE VALUE(a) IS OF (ONLY LebensmittelTyp);
’Auf den Bergen von ...’,
NULL, 700, ’Keine Ahnung’));
INSERT INTO Artikel VALUES
(HaushaltswarenTyp(’343-1197-71’, ’Mixer’, 59.99,
’Super leicht ...’,
NULL, CURRENT_DATE, ’Weiss’));
SELECT Nummer, Bezeichnung, Preis, Beschreibung
FROM Artikel;
SELECT Nummer, Bezeichnung, Preis, Beschreibung
FROM AllgemeineArtikel;
SELECT Nummer, Bezeichnung, Preis, Beschreibung,
Artikel, a.AnzahlArtikel()
FROM ArtikelSet a;
SELECT Nummer, Bezeichnung, Preis, Beschreibung,
Gewicht, Zusammensetzung
FROM Lebensmittel;
CREATE VIEW Haushaltswaren OF HaushaltswarenTyp
AS SELECT TREAT(VALUE(a) AS HaushaltswarenTyp)
FROM Artikel a
WHERE VALUE(a) IS OF (ONLY HaushaltswarenTyp);
SELECT Nummer, Bezeichnung, Preis, Beschreibung,
Garantie, Farbe
FROM Haushaltswaren;
INSERT INTO Artikel VALUES
(ArtikelTyp(’123-4567-98’, ’DVD’, 24.99,
’Goldeneye ...’, NULL));
Aufgabe 2: Entwurf einer Funktion in Oracle-PL/SQL
INSERT INTO Artikel VALUES
(ArtikelTyp(’123-4567-99’, ’DVD’, 24.99,
’Goldfinger ...’, NULL));
Schreiben Sie eine Funktion, die als Parameter einen Primärschlüssel und das monatliche Einkommen eines Kunden erhält, dessen Kreditlimit errechnet werden soll. Dieses Limit beträgt
normalerweise das dreifache seines Gehalts, minimal aber 500 und maximal 5000 Franken. Der
so ermittelte Wert soll zurückgegeben werden.
Aufgabe 3: Entwurf einer Prozedur in Oracle-PL/SQL
INSERT INTO Artikel VALUES
(ArtikelSetTyp(’989-4567-98’, ’DVD-Box’, 559.99,
’Die Box enthält ...’, NULL,
CAST(MULTISET(SELECT REF(a)
FROM Artikel a
WHERE Bezeichnung = ’DVD’
AND Beschreibung LIKE ’Gold%’)
AS ArtikelKollektionsTyp)));
INSERT INTO Artikel VALUES
(LebensmittelTyp(’481-4427-15’, ’Wein’, 34.99,
3
Entwerfen Sie eine Prozedur, die wiederum den Primärschlüssel eines Kunden und eine neue
Telefonnummer übergeben bekommt. Diese Telefonnummer soll nun zusätzlich zu den bisher
vorhandenen gespeichert werden.
Beispiellösung für Aufgabe 1 und 2:
Bevor wir die Funktion bzw. Prozedur umsetzen, wollen wir die zugrunde liegende Tabellen erzeugen, damit wir die Routinen später auch testen können. In Übung 8 hatten wir
bereits einen Teil des Anwendungsszenarios aus Übung 1 umgesetzt. Darauf aufbauend
implementieren wir zunächst die restlichen Klassen und danach die geforderten Routinen.
4
CREATE TYPE TelefonTabellenTyp AS TABLE OF VARCHAR(20); /
CREATE TYPE AdresseTyp AS OBJECT (
Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)
) NOT FINAL; /
CHECK(Status IN (’InBearbeitung’, ’Eingegangen’,
’Geliefert’, ’Bezahlt’))
) NESTED TABLE Positionen STORE AS Positionen;
CREATE TABLE Kunde OF KundeTyp (
PRIMARY KEY(Kundennummer),
Name
NOT NULL,
Anschrift
NOT NULL,
CHECK(Anschrift.PLZ IS NOT NULL),
CHECK(Kreditlimit <= 5000)
) NESTED TABLE Bestellungen STORE AS Kundenbestellungen NESTED TABLE
Telefone STORE AS Kundentelefone;
CREATE TYPE PositionTyp AS OBJECT (
Artikel REF ArtikelTyp,
Anzahl INTEGER
); /
CREATE TYPE PositionTabellenTyp AS TABLE OF PositionTyp; /
CREATE TYPE BestellungsTyp AS OBJECT (
Bestellnummer INTEGER,
Eingangsdatum DATE,
Status VARCHAR(15),
Positionen PositionTabellenTyp
); /
CREATE TYPE BestellungsTabellenTyp AS TABLE OF REF BestellungsTyp; /
CREATE TYPE PersonTyp AS OBJECT (
Name
VARCHAR(30),
Anschrift
AdresseTyp,
Telefone
TelefonTabellenTyp,
Email
VARCHAR(25)
) NOT FINAL NOT INSTANTIABLE; /
CREATE TYPE KundeTyp UNDER PersonTyp (
Kundennummer INTEGER,
Bestellungen BestellungsTabellenTyp,
Kreditlimit DECIMAL(12,2)
) FINAL; /
CREATE TYPE AngestellterTyp UNDER PersonTyp (
Personalnummer INTEGER,
Gehalt
DECIMAL(12,2)
) FINAL; /
CREATE TABLE Bestellung OF BestellungsTyp (
PRIMARY KEY(Bestellnummer),
5
CREATE TABLE Angestellte OF AngestellterTyp (
PRIMARY KEY(Personalnummer),
Name
NOT NULL,
Anschrift
NOT NULL,
CHECK(Anschrift.PLZ IS NOT NULL),
CHECK(Gehalt >= 2000)
) NESTED TABLE Telefone STORE AS Angestelltentelefone;
---------------------------------------------------- Funktion Kundenkredit berechnet das Kreditlimit
-- basierend auf dem Gehalt eines Kunden
--------------------------------------------------CREATE FUNCTION Kundenkredit(gehalt DECIMAL) RETURN DECIMAL AS
limit DECIMAL;
BEGIN
limit := 3 * gehalt;
IF limit < 500.0 THEN limit:= 500;
ELSE IF limit > 5000.0 THEN limit:= 5000; END IF;
END IF;
RETURN limit;
END; /
---------------------------------------------------- PROCEDURE NeueNummer fügt eine (neue)
-- Telefonnummer in die tabellenwertige Spalte
-- Telefone eines bestimmten Kunden ein
--------------------------------------------------CREATE PROCEDURE NeueNummer(knr INTEGER, nn VARCHAR) AS BEGIN
INSERT INTO TABLE(SELECT Telefone
FROM Kunde
WHERE Kundennummer = knr)
VALUES(nn);
END; /
6
---------------------------------------------------- Basierend auf den Artikel-Daten aus Uebung 8
-- erzeugen wir nun Daten für Bestellungen und
-- Kunden, um die neue Routinen zu testen
--------------------------------------------------INSERT INTO Bestellung VALUES (7989,
CURRENT_DATE,
’Eingegangen’,
PositionTabellenTyp(
PositionTyp((SELECT REF(a)
FROM Artikel a
WHERE Nummer = ’123-4567-98’),
1)));
INSERT INTO Bestellung VALUES (6134,
CURRENT_DATE,
’InBearbeitung’,
PositionTabellenTyp(
PositionTyp((SELECT REF(a)
FROM Artikel a
WHERE Nummer = ’989-4567-98’),
1),
PositionTyp((SELECT REF(a)
FROM Artikel a
WHERE Nummer = ’481-4427-15’),
12)));
---------------------------------------------------- Funktion Kundenkredit wird beim Erzeugen
-- eines Kunden verwendet
--------------------------------------------------INSERT INTO Kunde VALUES(’James’,
AdresseTyp(’Haldeneggsteig’, 4, 8092, ’Zürich’, ’CH’),
TelefonTabellenTyp(’0041-1-9876543’,
’0049-611-6733219’),
’[email protected]’,
1704,
CAST(MULTISET(SELECT REF(b)
FROM Bestellung b
WHERE Bestellnummer = 7989)
AS BestellungsTabellenTyp),
Kundenkredit(1000.0));
INSERT INTO Kunde VALUES(’John’,
AdresseTyp(’Seestrasse’, 13, 8008, ’Zürich’, ’CH’),
7
TelefonTabellenTyp(’0041-1-2612345’),
’[email protected]’,
6970,
NULL,
Kundenkredit(3000.0));
---------------------------------------------------- Aufruf der Prozedur NeueNummer
--------------------------------------------------CALL NeueNummer(1704, ’0041-1-45678912’);
---------------------------------------------------- Es ist auch möglich, die folgende Funktion zu
-- definieren
--------------------------------------------------CREATE FUNCTION Kundenlimit(knr INTEGER,
gehalt DECIMAL)
RETURN DECIMAL AS
limit DECIMAL;
BEGIN
limit := 3 * gehalt;
IF limit < 500.0 THEN limit:= 500;
ELSE IF limit > 5000.0 THEN limit:= 5000; END IF;
END IF;
UPDATE Kunde
SET Kreditlimit = limit
WHERE Kundennummer = knr;
RETURN limit;
END; /
---------------------------------------------------- Ein Aufruf dieser Funktion ist innerhalb einer
-- Anfrage nicht erlaubt
--------------------------------------------------SELECT Kundenlimit(Kundennummer, 1500)
FROM Kunde
WHERE Kundennummer = 6970;
---------------------------------------------------- Oracle gibt die folgende Fehlermeldung aus:
---------------------------------------------------- FEHLER in Zeile 1:
-- ORA-14551: DML-Vorgang kann innerhalb einer
-Abfrage nicht ausgeführt werden
-- ORA-06512: in "SYSTEM.KUNDENLIMIT", Zeile 10
8
---------------------------------------------------- Ebenso ist ein Aufruf ein solchen Funktion
-- nicht in einer DML-Anweisung gestattet
--------------------------------------------------UPDATE Kunde
SET Kreditlimit = Kundenlimit(Kundennummer, 1500)
WHERE Kundennummer = 6970;
---------------------------------------------------- Oracle gibt die folgende Fehlermeldung aus:
---------------------------------------------------- FEHLER in Zeile 2:
-- ORA-04091: Tabelle SYSTEM.KUNDE wird gerade
-geändert, Trigger/Funktion sieht
-dies
möglicherweise nicht
-- ORA-06512: in "SYSTEM.KUNDENLIMIT", Zeile 10
---------------------------------------------------- Folgender Aufruf in einem PL/SQL-Block ist
-- dagegen möglich
--------------------------------------------------DECLARE
test DECIMAL;
Begin test := Kundenlimit(6970, 1500); END; /
9
Institut für Informationssysteme
Dr. C. Türker
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
Objektrelationale, erweiterbare Datenbanken WS 04/05
Übung 8 (Extra)
Beispiellösung
Aufgabe 1: Rekursion
Gegeben sei folgende Definition einer Tabelle, die Verwandtschaftsbeziehungen verwaltet:
CREATE TABLE Stammbaum
(
MenschID
INTEGER PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
GebDatum
DATE,
Mutter
INTEGER REFERENCES Stammbaum,
Vater
INTEGER REFERENCES Stammbaum
);
1. Ermitteln Sie mittels SQL:2003 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:2003 alle Nachkommen von ’Bob’.
1
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 INTO Stammbaum VALUES (1, ’Bob’, ’11-29-1927’, NULL, NULL) $
INSERT INTO Stammbaum VALUES (2, ’Joe’, ’10-23-1952’, NULL, 1) $
INSERT INTO Stammbaum VALUES (7, ’Mark’, ’08-30-1957’, NULL, NULL) $
INSERT INTO Stammbaum VALUES (3, ’Joanna’, ’09-12-1954’, NULL, NULL)
$ INSERT INTO Stammbaum VALUES (4, ’Kim’, ’03-15-1997’, 3, 2) $
INSERT INTO Stammbaum VALUES (5, ’Jim’, ’12-24-1995’, 3, 2) $ INSERT
INTO Stammbaum VALUES (6, ’Cloe’, ’05-07-1999’, 3, 7) $
WITH Vorfahren(MenschID, Vorfahr) AS (
SELECT MenschID, Mutter
FROM Stammbaum
WHERE Name = ’Jim’
UNION ALL
SELECT MenschID, Vater
2
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 $
3
Institut für Informationssysteme
Dr. C. Türker
Eidgenössische Technische Hochschule Zürich
Swiss Federal Institute of Technology Zurich
Objektrelationale, erweiterbare Datenbanken WS 04/05
Übung 8 (Extra)
Beispiellösung
Aufgabe 1: Rekursion
Gegeben sei folgende Definition einer Tabelle, die Verwandtschaftsbeziehungen verwaltet:
CREATE TABLE Stammbaum
(
MenschID
INTEGER PRIMARY KEY,
Name
VARCHAR(30) NOT NULL,
GebDatum
DATE,
Mutter
INTEGER REFERENCES Stammbaum,
Vater
INTEGER REFERENCES Stammbaum
);
1. Ermitteln Sie mittels SQL:2003 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:2003 alle Nachkommen von ’Bob’.
1
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 INTO Stammbaum VALUES (1, ’Bob’, ’11-29-1927’, NULL, NULL) $
INSERT INTO Stammbaum VALUES (2, ’Joe’, ’10-23-1952’, NULL, 1) $
INSERT INTO Stammbaum VALUES (7, ’Mark’, ’08-30-1957’, NULL, NULL) $
INSERT INTO Stammbaum VALUES (3, ’Joanna’, ’09-12-1954’, NULL, NULL)
$ INSERT INTO Stammbaum VALUES (4, ’Kim’, ’03-15-1997’, 3, 2) $
INSERT INTO Stammbaum VALUES (5, ’Jim’, ’12-24-1995’, 3, 2) $ INSERT
INTO Stammbaum VALUES (6, ’Cloe’, ’05-07-1999’, 3, 7) $
WITH Vorfahren(MenschID, Vorfahr) AS (
SELECT MenschID, Mutter
FROM Stammbaum
WHERE Name = ’Jim’
UNION ALL
SELECT MenschID, Vater
2
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 $
3