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 8
Musterlö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 UMLKlassenhierarchie 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 ArtikelKollektionsTyp AS TABLE OF REF ArtikelTyp;
/
CREATE TYPE ArtikelSetTyp UNDER ArtikelTyp (
Artikel
ArtikelKollektionsTyp,
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER
) FINAL;
/
CREATE TYPE BODY ArtikelSetTyp AS
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER AS
BEGIN
RETURN SELF.Artikel.COUNT;
END;
END;
/
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 BODY KatalogTyp AS
MEMBER FUNCTION AnzahlArtikel RETURN INTEGER AS
BEGIN
RETURN SELF.Artikel.COUNT;
END;
END;
/
CREATE TABLE Katalog OF KatalogTyp (
PRIMARY KEY(Jahr, Sommer),
Titel NOT NULL
)
NESTED TABLE Artikel STORE AS KatalogArtikel;
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);
CREATE VIEW Haushaltswaren OF HaushaltswarenTyp
AS SELECT TREAT(VALUE(a) AS HaushaltswarenTyp)
FROM Artikel a
WHERE VALUE(a) IS OF (ONLY HaushaltswarenTyp);
INSERT INTO Artikel VALUES
(ArtikelTyp(’123-4567-98’, ’DVD’, 24.99,
’Goldeneye ...’, NULL));
INSERT INTO Artikel VALUES
(ArtikelTyp(’123-4567-99’, ’DVD’, 24.99,
’Goldfinger ...’, NULL));
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,
’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;
SELECT Nummer, Bezeichnung, Preis, Beschreibung,
Garantie, Farbe
FROM Haushaltswaren;