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 5
Musterlösung
Aufgabe 1: Strukturierte Typen in SQL:1999
Wir betrachten nun wieder das Anwendungsbeispiel.
1. Setzen Sie die UML-Klasse Bestellung (ohne auf die Beziehungen zu den
UML-Klassen Position und Kunde einzugehen) in einen strukturierten Typ
um. Der Status soll dabei ein Wert einer Domäne sein, die folgende Zustände
festlegt: eingegangen“, in bearbeitung“, ausgeliefert“ und bezahlt“. Ge”
”
”
”
ben Sie die Definition der Domäne und des strukturierten Typs in SQL:1999Syntax an:
Lösungsvorschlag:
CREATE DOMAIN Bestellungsstatus VARCHAR(13)
DEFAULT ’eingegangen’
CHECK(VALUE IN (’eingegangen’, ’in bearbeitung’,
’ausgeliefert’, ’bezahlt’));
CREATE TYPE BestellungTyp AS
(
Bestellnummer INTEGER,
Eingangsdatum DATE,
Status
Bestellungsstatus
)
NOT FINAL;
2. Entwerfen Sie eine Typhierarchie für die Klassen Person, Kunde und Angestellter. Die Adresse soll hier als Tupeltyp umgesetzt werden. Weiterhin seien
maximal drei Telefonnummern pro Person zulässig. Achten Sie darauf, dass
auch den Kunden ihre Bestellungen zugeordnet werden können.
Lösungsvorschlag:
CREATE TYPE PersonTyp AS
(
Name
VARCHAR(30),
Adresse
ROW (Strasse VARCHAR(30),
Nr
DECIMAL(3,0),
PLZ
DECIMAL(5,0),
Ort
VARCHAR(30)),
Telefone
VARCHAR(20) ARRAY[3],
Geburtsdatum DATE,
Email
VARCHAR(30)
)
NOT INSTANTIABLE -- realisiert abstrakte Klasse
NOT FINAL;
CREATE TYPE AngestellterTyp UNDER PersonTyp AS
(
Personalnummer INTEGER,
Gehalt
Franken -- definiert wie in der Vorlesung
)
INSTANTIABLE
NOT FINAL;
CREATE TYPE KundeTyp UNDER PersonTyp AS
(
Kundennummer INTEGER,
Kreditlimit FRANKEN,
Bestellungen REF(BestellungTyp) ARRAY [100]
)
INSTANTIABLE
NOT FINAL;
Es fällt auf, dass offensichtlich bei Verwendung von Referenzen zur Nachbildung einer (1:n)-Beziehung Probleme auftreten, da keine Liste mit unbeschränkter Elementezahl in SQL:1999 zur Verfügung steht, sondern nur Arrays, bei denen aber eine Obergrenze existiert.
3. Sei die folgende Tupeltabelle in SQL:1999 definiert:
CREATE TABLE Mitarbeiter
(
Name VARCHAR(30),
Adresse ROW (Strasse VARCHAR(30),
Nr
DECIMAL(3,0),
PLZ
DECIMAL(5,0),
Ort
VARCHAR(30)),
Telefone VARCHAR(20) ARRAY[3],
Geburtsdatum DATE,
Personalnummer INTEGER,
Gehalt FRANKEN -- definiert wie in der Vorlesung
);
Fügen Sie in diese Tabelle den folgenden Mitarbeiter ein:
•
•
•
•
•
•
•
Name: Peter Müller
Strasse: ETH-Zentrum 34
Wohnort: 8092 Zürich
Telefonnummern: {01-6320815, 01-856987}
Geburtsdatum: 4.5.1965
Personalnummer: 5546
Gehalt: 4000 SFR
Lösungsvorschlag:
INSERT INTO Mitarbeiter(Name,
Adresse,
Telefnonnummern,
Geburtsdatum,
Personalnummer,
Gehalt)
VALUES (’Peter Müller’,
ROW(’ETH Zentrum’, 34, 8092, ’Zürich’),
ARRAY[’01-6320815’, ’01-856987’],
DATE ’1964-4-5’,
5546,
Franken(4000));
4. Erhöhen Sie die Gehälter aller Mitarbeiter obiger Tabelle, die nach 1968 geboren sind, um 500 SFR.
Lösungsvorschlag:
UPDATE Mitarbeiter
SET Gehalt = Franken(CAST(Gehalt AS NUMERIC) + 500)
WHERE Geburtsdatum >= DATE ’1969-01-01’;
5. Ändern Sie die Adresse von Peter Müller“ in Seefeldstrasse 13, 8092
”
”
Zürich“.
Lösungsvorschlag:
UPDATE Mitarbeiter
SET Adresse = ROW(’Seefeldstrasse’, 13, 8092, ’Zürich’)
WHERE Name = ’Peter Müller’;
6. Bei der letzten Änderung wurde eine falsche Postleitzahl angegeben. Ändern
nun die falsche“ Postleitzahl 8092 in die richtige“ Postleitzahl 8008.
”
”
Lösungsvorschlag:
UPDATE Mitarbeiter
SET Adresse.PLZ = 8008
WHERE Name = ’Peter Müller’;
Aufgabe 2: Methoden in SQL:1999
1. Definieren Sie für PersonTyp eine Methode, die den Wohnort der Person
zurückliefert.
Lösungsvorschlag:
CREATE METHOD Wohnort()
RETURNS VARCHAR(30)
FOR PersonTyp
RETURN SELF.Adresse.Ort;
Man beachte, dass zuvor die Typdefinition von PersonTyp um die Deklaration der Methodensignatur erweitert werden muss.
2. Überladen Sie den Konstruktor für KundeTyp: Beim Erzeugen einer neuen
KundeTyp-Instanz soll, falls alle Attribute (bzw. deren Werte) mit Ausnahme
des Kreditlimits übergeben werden, letzteres auf 2.500 SFR gesetzt werden.
Lösungsvorschlag:
CREATE FUNCTION KundeTyp(n VARCHAR(30),
a ROW (s VARCHAR(30),
n DECIMAL(3,0),
p DECIMAL(5,0),
o VARCHAR(30)),
t VARCHAR(20) ARRAY[3],
g DATE,
e VARCHAR(30),
k INTEGER)
RETURNS KundeTyp
BEGIN
DECLARE k KundeTyp;
SET k = KundeTyp();
SET k.Name = n;
SET k.Adresse = a;
SET k.Telefone = t;
SET k.Geburtsdatum = g;
SET k.Email = e;
SET k.Kundennummer = k;
SET k.Kreditlimit = Franken(2500);
SET k.Bestellungen = NULL;
RETURN k;
END;
Kurzfassung unter Ausnutzung von Pfadausdrücken:
CREATE FUNCTION KundeTyp(n VARCHAR(30),
a ROW (s VARCHAR(30),
n DECIMAL(3,0),
p DECIMAL(5,0),
o VARCHAR(30)),
t VARCHAR(20) ARRAY[3],
g DATE,
e VARCHAR(30),
k INTEGER)
RETURNS KundeTyp
RETURN KundeTyp().Name(n).Adresse(a).Telefone(t)
.Geburtsdatum(g).Email(e).Kundennummer(k)
.Kreditlimit(Franken(2500));
END;
3. Entwerfen Sie eine Methode, mit der man eine weitere, neue Telefonnummer
einer Person eintragen kann. Falls bereits drei gespeichert sind, so soll die
bisher erste überschrieben werden, ansonsten wird sie hinter die bisherigen
eingefügt.
Lösungsvorschlag:
Wir nehmen an, dass das Array immer von Beginn an aufgefüllt wird und
dieses auch beim Entfernen von Telefonnummern garantiert wird.
CREATE METHOD NeueTelefonnummer (telneu VARCHAR(20))
RETURNS BOOLEAN
FOR PersonTyp
BEGIN
DECLARE i INTEGER;
IF (CARDINALITY(SELF.Telefonnummern)<3) THEN
SET i = CARDINALITY(SELF.Telefonnummern)+1;
ELSE
SET i = 1;
ENDIF;
SET SELF.Telefonnummern[i] = telneu;
RETURN TRUE;
END;
Aufgabe 3: Umsetzung mit DB2SQL
Setzen die Aufgaben 1.1, 1.2, 2.1 und 2.2 praktisch mit Hilfe von DB2SQL um. Beachten Sie dabei, dass DB2 das Konzept der Domäne nicht kennt. Ebenso gibt es
keine Tupel- und Arraytypen. Welche dieser Probleme können Sie auf der Typebene (d.h. durch Typdefinitionen) lösen? Für welche müssen Sie die Extensionsebene
(d.h. Tabellendefinitionen) einsetzen?
CREATE DISTINCT TYPE Franken AS DECIMAL(12,2)
WITH COMPARISONS $
-------------------------------------------------------------- Die gültigen Statuswerte müssen später in
-- allen Tabellen, die Bestelldaten verwalten, mit
-- Hilfe von Check-Bedingungen durchgesetzt werden
-- -> erhöhter Aufwand plus Konsistenzproblematik,
-- z.B. wenn neue Statuswerte erlaubt werden bzw.
-- alte Werte umbenannt und gar entfernt werden.
CREATE TYPE BestellungTyp AS
(
Bestellnummer INTEGER,
Eingangsdatum DATE,
Status
VARCHAR(13)
)
MODE DB2SQL
NOT FINAL $
-------------------------------------------------------------- Das Problem mit dem Tupeltyp kann einfach durch Weglassen
-- der Struktur beseitigt werden. Allerdings geht damit eben
-- die Strukturierung verloren. Strasse, Nr, etc. sind nun
-- direkte Eigenschaften von Personen. Eine Adresse ist nicht
-- mehr als eine Einheit verfügbar.
-- Das Problem mit dem Arraytyp lässt sich jedoch nicht auf
-- der Typebene lösen. Die Information über die Telefonnummern
-- muss in einer separaten Tabelle verwaltet werden.
CREATE TYPE PersonTyp AS
(
Name
VARCHAR(30),
Strasse
VARCHAR(30),
Nr
DECIMAL(3,0),
PLZ
DECIMAL(5,0),
Ort
VARCHAR(30),
Geburtsdatum DATE,
Email
VARCHAR(30)
)
MODE DB2SQL
NOT INSTANTIABLE
NOT FINAL
METHOD Wohnort() RETURNS VARCHAR(30) $
-------------------------------------------------------------- AngestellterTyp kann (fast) wie in SQL:1999 definiert
-- werden. Nur die obligatorische DB2SQL-Klausel muss
-- zusätzlich angegeben werden - wie bei allen anderen
-- strukturierten Typen auch.
CREATE TYPE AngestellterTyp UNDER PersonTyp AS
(
Personalnummer INTEGER,
Gehalt
Franken
)
MODE DB2SQL
INSTANTIABLE
NOT FINAL $
-------------------------------------------------------------- Das arraywertige Attribut Bestellungen wurde entfernt.
-- Diese Information muss später in einer separaten Tabelle
-- gespeichert werden.
CREATE TYPE KundeTyp UNDER PersonTyp AS
(
Kundennummer INTEGER,
Kreditlimit Franken
)
MODE DB2SQL
INSTANTIABLE
NOT FINAL $
-------------------------------------------------------------- Achtung: Das Schlüsselwort INSTANCE ist in DB2SQL
-- nicht vorgesehen.
CREATE METHOD Wohnort()
RETURNS VARCHAR(30)
FOR PersonTyp
RETURN SELF..Ort
$
-------------------------------------------------------------
-- Durch das Wegfallen der ehemals tupel- bzw.
-- arraywertigen Attribute muss die Definition des
-- überladenden Konstruktors entsprechend angepasst werden.
CREATE FUNCTION KundeTyp(n VARCHAR(30),
s VARCHAR(30),
nr DECIMAL(3,0),
p VARCHAR(5,0),
o VARCHAR(30),
g DATE,
e VARCHAR(30),
k INTEGER)
RETURNS KundeTyp
RETURN KundeTyp()..Name(n)..Strasse(s)..Nr(nr)..PLZ(p)
..Ort(o)..Geburtsdatum(g)..Email(e)
..Kundennummer(k)..Kreditlimit(Franken(2500)) $