SELECT

Kapitel 4
Objektrelationale Datenmodell- und
Spracherweiterungen kommerzieller
Datenbanksysteme
IBM DB2 Universal Database, Version 8.1
Oracle 9i, Release 2
IBM Informix Dynamic Server, Version 9.3
PostgreSQL, Version 7.3.1
Disclaimer
Dieses Kapitel basiert auf dem folgenden Buch, welches auch zur weiteren
Vertiefung empfohlen wird:
Can Türker:
SQL:1999 & SQL:2003 —
Objektrelationales SQL, SQLJ & SQL/XML.
dpunkt-Verlag, Heidelberg, 2003.
ISBN: 3-89864-219-4
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-2
Objektrelationale Erweiterungen in DB2
z
Neue Basisdatentypen
z
Benutzerdefinierte Typen
–
–
Distinct-Typen
Strukturierte Typen (mit Typvererbung)
z
Referenztyp
z
Typisierte Tabellen (mit Subtabellenbildung)
z
Typisierte Sichten (mit Subsichtenbildung)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-3
Neue Basisdatentypen
z
Large Objects
–
–
–
–
z
BLOB (Binary Large Object)
CLOB (Character Large Object)
Operationen
„ SUBSTR, POSSTR, || (CONCAT)
„ IS [NOT] NULL, LIKE, LENGTH
Nicht erlaubt
„ BLOB/CLOB-Attribute als Teil eines Schlüssels oder in booleschen bzw.
arithmetischen Ausdrücken
„ GROUP BY, ORDER BY, EQUALS, GREATER/LESS THAN
BOOLEAN Datentyp nicht unterstützt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-4
Basisdatentypen
Basisdatentyp
Bedeutung
SMALLINT
(16 Bit) ganze Zahl [-32768, 32767]
INTEGER
(32 Bit) ganze Zahl [-2147483648, 2147483647]
BIGINT
(64 Bit) ganze Zahl [-9223372036854775808, 9223372036854775807]
DECIMAL(p,q)
NUMERIC(p,q)
Festkommazahl mit Genauigkeit p und q Nachkommastellen (p ≤ 31)
FLOAT(p)
REAL
DOUBLE
Fließkommazahl mit Genauigkeit p (≤ 31)
CHAR(q)
Alphanumerische Zeichenkette mit fester Länge q (≤ 254)
VARCHAR(q)
Alphanumerische Zeichenkette mit variabler Länge q (≤ 3745)
CLOB
Alphanumerische Zeichenkette mit variabler Länge bis 2GB
BLOB
Binäre Zeichenkette mit variabler Länge bis 2GB
DATE
Datum
TIME
Zeit
TIMESTAMP
Zeitstempel
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-5
Verwendung neuer Basisdatentypen
Definition einer Tabelle mit LOB-Attributen:
CREATE TABLE MitarbeiterTupelTabelle (
Name
VARCHAR(30),
Bild
BLOB(1M),
Bewerbung CLOB(50k)
);
Erzeugen von LOB-Attributen:
INSERT INTO MitarbeiterTupelTabelle
VALUES ( 'Billy',
BLOB('Bilddaten innerhalb einer Anwendung zuweisen!'),
CLOB('Bewerbung innerhalb einer Anwendung zuweisen!')
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-6
Distinct-Typen
z
Distinct-Typ ist eine Kopie eines Basisdatentyps
z
Syntax:
z
Distinct-Typdefinition generiert automatisch
–
–
CREATE DISTINCT TYPE Distinct-Typname AS Basisdatentyp
WITH COMPARISONS
Cast-Funktionen zur Konversion zwischen Distinct-Typ und Ausgangstyp
Vergleichsoperatoren (=, <, >, <=, <=, <>) für Instanzen des Distinct-Typs
„ Gilt nicht für die Typen BLOB, CLOB, LONG VARCHAR, LONG VARGRAPHIC
(hier darf WITH COMPARISONS nicht verwendet werden)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-7
Beispieldefinition und Verwendung von Distinct-Typen
Definition von Distinct-Typen:
CREATE DISTINCT TYPE Franken AS DECIMAL(9,2) WITH COMPARISONS;
CREATE DISTINCT TYPE Euro AS DECIMAL(9,2) WITH COMPARISONS;
Verwendung von Distinct-Typen in Tabellendefinitionen:
CREATE TABLE CHBank(Nr INTEGER, Stand Franken);
CREATE TABLE EuroBank(Nr INTEGER, Stand Euro);
Anfrage basierend auf Distinct-Typen und generierten Cast-Funktionen:
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE CAST(c.Stand AS DECIMAL) < CAST(e.Stand AS DECIMAL);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-8
Strukturierte Typen
z
Abstrakter Objekttyp mit Attributen und Methoden
–
–
–
–
–
z
Defaultwerte und Integritätsbedingungen werden nicht unterstützt
Keine kollektionswertigen Attribute
Attribut eines strukturierten Typs darf weder denselben Typ noch einen
Subtyp dieses strukturierten Typs haben
Typvererbung: Ein Subtyp erbt die Attribute und Methoden des Supertyps
Substituierbarkeit: Subtypinstanz ist als Supertypinstanz verwendbar
Typdefinition generiert automatisch
–
–
–
Methoden zum Zugriff auf und Ändern der Attributwerte
Konstruktoren zum Erzeugen von Instanzen
Operatoren zum Vergleichen von Instanzen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-9
Strukturierte Typen – Definition von Wurzeltypen
z
Syntax:
–
CREATE TYPE Typname AS
(Attributdefinitionsliste)
[[NOT] INSTANTIABLE]
[NOT FINAL]
MODE DB2SQL
[REF USING Typ]
[CAST (SOURCE AS REF) WITH Funktionsname]
[CAST (REF AS SOURCE) WITH Funktionsname]
[Methodendeklarationsliste]
Jede Instanz eines strukturierten Typs kann mit einer OID assoziiert werden
„ Default-Typ der OID ist VARCHAR(16)
„ Typ der OID kann mit REF USING geändert werden
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-10
Strukturierte Typen – Beispiele
CREATE TYPE AdresseTyp AS (
Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)
) MODE DB2SQL;
CREATE TYPE KundeTyp AS (
KNr
INTEGER,
Name
VARCHAR(30),
Anschrift AdresseTyp
) REF USING INTEGER
MODE DB2SQL;
CREATE TYPE LieferantTyp AS (
LNr
INTEGER,
Name
VARCHAR(25),
Anschrift AdresseTyp
) MODE DB2SQL;
CREATE TYPE AuftragTyp AS (
ANr
INTEGER,
Kunde
REF(KundeTyp)
Eingang
DATE,
Bearbeitet DATE,
Lieferant REF(LieferantTyp)
) MODE DB2SQL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-11
Referenztyp
z
REF: Referenz auf ein Objekt eines strukturierten Typs
–
z
Attributwert ist eine OID (object identifier)
Beispiel: Ein Teil referenziert ein anderes Teil
CREATE TYPE TeilTyp AS (
Nr
DECIMAL(10),
Bezeichnung
VARCHAR(25),
Farbe
VARCHAR(15),
IstTeilVon
REF(TeilTyp),
Preis
Franken
) MODE DB2SQL
METHOD Gesamtpreis() RETURNS Franken LANGUAGE SQL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-12
Methodenimplementierung
z
Methodendeklaration und -implementierung erfolgt analog zu SQL:1999
–
Deklaration erfolgt innerhalb der Definition/Änderung des Objekttyps
ALTER TYPE TeilTyp ADD METHOD AnzahlUnterteile()
RETURNS INTEGER LANGUAGE SQL;
–
Implementierung in einer separaten Anweisung (hier SQL-Variante)
CREATE METHOD AnzahlUnterteile()
RETURNS INTEGER
FOR TeilTyp
RETURN (SELECT COUNT(*)
FROM Teil
WHERE IstTeilVon->Bezeichnung = SELF..Bezeichnung);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-13
Subtypbildung – Aufbau von Typhierarchien
z
Subtypdefinition mittels UNDER-Klausel
–
–
–
–
z
Subtyp erbt alle Attribute und Methoden des Supertyps
Supertyp muss selbst ein strukturierter Typ sein
Subtyp darf nur maximal einen direkten Supertyp haben
„ Keine (direkte) Mehrfachvererbung möglich
Geerbte Methoden können überschrieben werden (OVERRIDING)
Syntax:
CREATE TYPE Typname
UNDER Supertypname AS
(Attributdefinitionsliste)
[[NOT] INSTANTIABLE]
[NOT FINAL]
MODE DB2SQL
[Methodendeklarationsliste]
Beispiel:
CREATE TYPE BWKundeTyp
UNDER KundeTyp AS
( Interessen VARCHAR(50) )
MODE DB2SQL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-14
Typisierte Tabelle
z
Basiert auf einem strukturierten Typen
z
Speichert Instanz des strukturierten Typs als Zeile der Tabelle
z
OID-Attribut ist sichtbar!
–
–
–
z
Muss für alle Wurzeltabellen explizit angegeben werden
Wert wird innerhalb von INSERT belegt und ist danach unveränderlich
Wert muss NOT NULL und eindeutig innerhalb der Tabellenhierarchie sein
Kann Integritätsbedingungen enthalten
–
Primär-/Unique-/Fremdschlüssel, Not Null, Check-Klausel, Scope-Klausel
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-15
Typisierte Tabellen – Definition von Wurzeltabellen
•
Syntax:
CREATE TABLE Tabellenname OF StrukturierterTyp (
REF IS OID-Attributname USER GENERATED
[Attributoptionsliste]
)
•
Attributoption:
Attributname WITH OPTIONS Optionsliste
| CHECK-Integritätsbedingung
| UNIQUE-Integritätsbedingung
•
Optionen:
SCOPE TypisierteTabelle
| DEFAULT Wert
| Integritätsbedingung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-16
Typisierte Tabelle – Beispiele
CREATE TABLE Teil OF TeilTyp (
REF IS oid USER GENERATED,
Nr WITH OPTIONS NOT NULL PRIMARY KEY,
IstTeilVon WITH OPTIONS SCOPE Teil
);
CREATE TABLE Kunde OF KundeTyp (
REF IS oid USER GENERATED,
KNr WITH OPTIONS NOT NULL PRIMARY KEY,
Name WITH OPTIONS NOT NULL
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-17
Typisierte Tabelle – Weitere Beispiele
CREATE TABLE Auftrag OF AuftragTyp (
REF IS oid USER GENERATED,
ANr WITH OPTIONS NOT NULL PRIMARY KEY
);
CREATE TABLE Lieferant OF LieferantTyp (
REF IS oid USER GENERATED,
LNr WITH OPTIONS NOT NULL PRIMARY KEY,
Name WITH OPTIONS NOT NULL
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-18
Operationen auf typisierten Tabellen
-- OID (erstes Attribut) durch
INSERT INTO Kunde
-- Referenzkonstruktoraufruf erzeugt
VALUES (KundeTyp(17), 17, 'Billy',
AdresseTyp()..Strasse('Seefeldstrasse')..Nr(31)..Plz(8008)
..Ort('Zurich')..Land('Schweiz'));
UPDATE Kunde
SET Anschrift= AdresseTyp()..Strasse('Kreuzstrasse')..Nr(31)..Plz(8008)
..Ort('Zurich')..Land('Schweiz')
WHERE Name = 'Billy';
DELETE FROM Kunde
WHERE Anschrift..Ort = 'Zurich';
Zugriff auf Attribute/Methoden einer
Instanz eines strukturierten Typs
erfolgt mittels dem Doppel-DotOperator ..
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-19
Attributbelegungen
z
Ändern von strukturierten Attributen
UPDATE Kunde
SET Anschrift..Stadt = 'Zuerich'
WHERE Anschrift..Stadt = 'Zurich';
z
Ändern von Referenzattributen
UPDATE Teil
SET IstTeilVon =(SELECT oid FROM Teil WHERE Bezeichnung = 'Fahrrad')
WHERE Bezeichnung = 'Rad';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-20
Überladen des Konstruktors
Definition eines Konstruktors:
CREATE FUNCTION AdresseTyp (S VARCHAR(30),
N DECIMAL(4),
P DECIMAL(5),
O VARCHAR(25),
L VARCHAR(20))
RETURNS AdresseTyp
LANGUAGE SQL
RETURN AdresseTyp()..Strasse(S)..Nr(N)..PLZ(P)..Ort(O)..Land(L);
Einfügen eines neuen Kunden mit Hilfe des neuen Konstruktors:
INSERT INTO Kunde
VALUES (KundeTyp(17), 17, 'Billy',
AdresseTyp('Seefeldstrasse', 31, 8008, 'Zurich', 'Schweiz'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-21
OID-Erzeugung
z
OIDs sind "systemgenerierbar", wenn der strukturierte Typ ohne REF
USING definiert wurde
INSERT INTO Teil
VALUES (TeilTyp(GENERATE_UNIQUE()), 13, 'Fahrrad', 'Silber-Blau', NULL);
z
Nutzer kann weiterhin OIDs vergeben
INSERT INTO Teil
VALUES (TeilTyp('RYWZBA12WS'), 18, 'Rahmen', 'Blau',
(SELECT oid FROM Teil WHERE Nr =13));
–
Referenzattributbelegung mit einer Anfrage (oben) oder Direktangabe (unten)
INSERT INTO Teil
VALUES (TeilTyp(GENERATE_UNIQUE()), 56, 'Gabel', 'Blau',
TeilTyp('RYWZBA12WS'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-22
Einfache Anfragen – Beispiele
Zugriff auf OID-Attribut:
SELECT oid FROM Kunde;
Zugriff auf eine Komponente eines objektwertigen Attributs:
SELECT Anschrift..Stadt FROM Kunde;
Zugriff auf ein Referenzattribut (OID des referenzierten Objekts):
SELECT IstTeilVon FROM Teil;
Zugriff auf ein Attribut eines referenzierten Objekts (Dereferenzierung mit
anschliessendem Komponentenzugriff):
SELECT IstTeilVon->Bezeichnung FROM Teil;
SELECT DEREF(IstTeilVon)..Bezeichnung FROM Teil;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-23
Subtabellenbildung – Aufbau von Tabellenhierarchien
z
Syntax: CREATE TABLE Tabellenname OF StrukturierterTyp
UNDER Supertabelle INHERIT SELECT PRIVILEGES
[( Attributoptionsliste )]
–
–
–
–
–
Typ der Subtabelle muss ein direkter Subtyp des Typs der Supertabelle sein
(Tiefe) Extension der Subtabelle muss immer eine Untermenge der (tiefen)
Extension der Supertabelle sein
„ Instanzen der Subtabelle sind auch Mitglieder der zugehörigen
Supertabellen
Subtabelle darf nur maximal eine direkte Supertabelle haben
„ Keine (direkte) Mehrfachspezialisierung möglich
Subtabelle kann neue Integritätsbedingungen hinzudefinieren
Subtabellen besitzen keine eigene Referenzklausel
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-24
Subtabelle – Beispiel
CREATE TABLE BWKunde OF BWKundeTyp
UNDER Kunde INHERIT SELECT PRIVILEGES
(
Interessen WITH OPTIONS NOT NULL
);
z
Achtung:
–
–
Schlüsselwort INHERIT SELECT PRIVILEGES ist nicht optional
Strukturierter Typ der Subtabelle mindestens ein "eigenes" Attribut definieren
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-25
Anfragen an Tabellenhierarchien
SELECT Name FROM Kunde;
liefert die Namen aller Kunden
(inklusive der BWKunden)
SELECT Name FROM BWKunde;
Zugriff auf tiefe Extension einer
Supertabelle
Zugriff auf tiefe Extension einer Subtabelle
liefert die Namen aller BWKunden
SELECT Name FROM ONLY(Kunde);
Zugriff auf flache Extension einer Supertabelle
liefert nur die Namen der Kunden,
die keine BWKunden sind
SELECT Name, Interessen FROM OUTER(Kunde);
liefert Namen und Interessen aller
Kunden
Zugriff auf Attribute der
Subtabelle auf Supertabellenebene
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-26
Typisierte Sichten
z
Syntax: Definition einer typisierten Sicht
CREATE VIEW Sichtenname OF StrukturierterTyp MODE DB2SQL (
REF IS OID-Attributname USER GENERATED
[Attributoptionsliste]
)
AS Anfrageausdruck
[WITH CHECK OPTION]
–
–
–
Referenzklausel ist zwingend notwendig für Wurzelsichten
Sichtanfrage über genau einer Tabelle bzw. Sicht
nur benutzerdefinierte OIDs sind erlaubt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-27
Subsichtenbildung – Aufbau von Sichtenhierarchien
z
Syntax: Definition einer Subsicht
CREATE VIEW Sichtenname OF StrukturierterTyp MODE DB2SQL
UNDER Supersicht INHERIT SELECT PRIVILEGES
[( Attributoptionsliste )]
AS Anfrageausdruck
[WITH CHECK OPTION]
–
–
–
–
Typ der Subsicht muss ein direkter Subtyp des Typs der Supersicht sein
Supersicht muss auf eine flache Extension zugreifen (ONLY)
Supersicht darf keine andere Subsicht desselben Subtyps besitzen
Subsicht erweitert Supersicht: Alle Instanzen der Subsicht sind auch
automatisch in der Supersicht sichtbar
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-28
Typisierte Sichten – Beispiele
Definition einer Wurzelsicht:
CREATE VIEW ZuercherKunde OF KundeTyp MODE DB2SQL
(REF IS oid USER GENERATED)
AS (SELECT * FROM ONLY(Kunde) WHERE Anschrift..Ort = 'Zurich');
Definition einer Subsicht:
CREATE VIEW AKunde OF BWKundeTyp MODE DB2SQL
UNDER ZuercherKunde INHERIT SELECT PRIVILEGES
AS (SELECT * FROM BWKunde WHERE Name LIKE 'A%');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-29
DB2 – Datenmodell
SET
MULTISET
OBJECT
ROW
REF
Einstiegspunkte
Typisierte Tabelle:
Untypisierte Tabelle:
Basisdatentyp
Subtypbeziehung
SET(OBJECT(...))
MULTISET(ROW(...))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Untermengenbeziehung
4-30
DB2 – Datenmodellüberblick
UNDER
Attributtypen
Basistyp
Referenztyp
Distinct-Typ
Strukturierter Typ
Attributtypen
OF
Tupeltabelle
(Multimenge von Tupeln
eines Tupeltyps)
Typisierte Tabelle
(Menge von Objekten
eines strukturierten Typs)
Sichtberechnung
basiert auf
Tupelsicht
(Multimenge von Tupeln
eines Tupeltyps)
UNDER
UNDER
OF
Typisierte Sicht
(Multimenge von Objekten
eines strukturierten Typs)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-31
Definition von Funktionen – Beispiele
CREATE FUNCTION AnzahlAuftraege (k VARCHAR(30))
RETURNS INTEGER
LANGUAGE SQL
RETURN (SELECT COUNT(*)
FROM Auftrag
WHERE Kunde->Name = k);
CREATE FUNCTION AnzahlAuftraege ()
RETURNS TABLE(Name VARCHAR(30), Anzahl INTEGER)
LANGUAGE SQL
RETURN (SELECT Kunde->Name, COUNT(*)
FROM Auftrag
GROUP BY Kunde->Name);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Funktion, die einen
"atomaren" Wert
liefert!
Funktion, die eine
Tabelle liefert!
4-32
SFW-Block (1)
z
SELECT: Was darf in der Projektionsliste stehen?
–
–
–
z
Attribute (auch abgeleitete, berechnete)
Methoden-/Funktionsaufrufe
Unterabfragen
9
9
(9) Nur skalare Unterabfragen,
die genau einen Wert liefern!
Beispiel:
SELECT Name, AnzahlAuftraege(Name),
(SELECT COUNT(*)
FROM Auftrag a
WHERE a.Kunde->Name = k.Name)
FROM Kunde k;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-33
SFW-Block (2)
z
FROM: Welche Tabellenausdrücke sind erlaubt?
–
–
–
–
–
z
Tupeltabellen
Objekttabellen (auch flache Extensionen)
Kollektionsabgeleitete Tabellen
Methoden-/Funktionsaufrufe (die Tabellen liefern)
Unterabfragen (abgeleitete Tabellen)
9
9
—
9
9 auch rekursive Tabellen!
Beispiel: Funktionsabgeleitete bzw. mittels Unterabfragen abgeleitete Tabellen
SELECT *
FROM TABLE(AnzahlAuftraege()) a;
SELECT *
FROM (SELECT Kunde FROM Auftrag) k;
Korrelationsvariable muss in
beiden Fällen angegeben werden!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-34
SFW-Block (3)
z
WHERE: Welche Prädikate sind erlaubt?
–
–
–
z
Prädikate über Attribute
9
Prädikate mit Methoden-/Funktionsaufrufen 9
Prädikate mit Unterabfragen
9
Beispiel:
SELECT *
FROM Kunde k
WHERE Name LIKE 'C%' AND
AnzahlAuftraege(Name) > 5 AND
EXISTS(SELECT * FROM Lieferant l WHERE k.Name = l.Name);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-35
Objektrelationale Erweiterungen in Oracle9i
z
Neue Basisdatentypen
z
Referenztypkonstruktor
z
Benutzerdefinierte Typen
–
–
Kollektionstypen
„ Tabellentyp
„ Variabler Arraytyp
Objekttypen (mit Subtypenbildung)
z
Objekttabellen
z
Objektsichten (mit Subsichtenbildung)
z
Hierarchische Anfragen (als beschränkte Rekursion)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-36
Neue Basisdatentypen
z
Large Objects
–
–
–
–
–
z
BLOB ("Internes" Binary Large Object)
CLOB ("Internes" Character Large Object)
BFILE ("Externes" Large Object)
Operationen
„ EMPTY_BLOB, EMPTY_CLOB, BFILENAME
„ IS [NOT] NULL
Nicht erlaubt
„ BLOB/CLOB-Attribute als Teil eines Schlüssels oder in booleschen bzw.
arithmetischen Ausdrücken
„ GROUP BY, ORDER BY
Kein BOOLEAN Datentyp unterstützt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-37
Basisdatentypen
Basisdatentyp
Bedeutung
NUMBER(p,q)
Zahl mit p ≤ 38 und -84≤ q ≤ 127 [1.0*10-130, 9.9…9*10125]
SMALLINT
NUMBER(38)
INTEGER
NUMBER(38)
DECIMAL(p,q)
NUMERIC(p,q)
NUMBER(p,q)
FLOAT(p)
REAL
DOUBLE PRECISION
NUMBER mit Genauigkeit p (≤ 126)
CHAR(q)
Alphanumerische Zeichenkette mit fester Länge q (≤ 2000)
VARCHAR(q)
Alphanumerische Zeichenkette mit variabler Länge q (≤ 4000)
CLOB
Alphanumerische Zeichenkette mit variabler Länge bis 4GB
BLOB
Binäre Zeichenkette mit variabler Länge bis 4GB
BFILE
Lokator auf externe Datei der Grösse bis 4GB
LONG RAW
Binäre Zeichenkette mit variabler Länge bis 2GB
RAW
Binäre Zeichenkette mit variabler Länge bis 32KB
DATE
Datum
TIMESTAMP
Zeitstempel
INTERVAL
Zeitinterval
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-38
Verwendung neuer Basisdatentypen
Definition einer Tabelle mit LOB-Attributen:
CREATE TABLE MitarbeiterTupelTabelle (
Name
VARCHAR(30),
Bild
BLOB,
BildDatei
BFILE,
Bewerbung
CLOB,
BewerbungDatei BFILE
);
Definition von Alias-Ordnern, in denen externe LOBs zu finden sind:
CREATE DIRECTORY MITARBEITER_BILDER AS 'D:\ImgSamp\';
CREATE DIRECTORY MITARBEITER_BEWERBUNGEN AS 'D:\RsmSamp\';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-39
Erzeugen von LOBs
Erzeugen von LOB-Attributen:
INSERT INTO MitarbeiterTupelTabelle
VALUES ('Billy',
EMPTY_BLOB(),
BFILENAME('MITARBEITER_BILDER', 'billy.gif'),
EMPTY_CLOB(),
BFILENAME('MITARBEITER_BEWERBUNGEN', 'billy.rsm')
);
EMPTY_BLOB() bzw. EMPTY_CLOB() initialisieren LOB-Lokatoren (notwendig
für spätere Zuweisung)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-40
Prozedur zum Belegung eines LOBs
CREATE PROCEDURE SetBewerbung (InputName IN VARCHAR2) IS
textdaten CLOB;
datei BFILE;
BEGIN
SELECT Bewerbung, BewerbungDatei INTO textdaten, datei
FROM MitarbeiterTupelTabelle
WHERE Name = InputName;
DBMS_LOB.FILEOPEN(datei, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE(textdaten, datei, DBMS_LOB.GETLENGTH(datei));
DBMS_LOB.FILECLOSE(datei);
UPDATE MitarbeiterTupelTabelle
SET Bewerbung = textdaten
WHERE Name = InputName;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-41
Prozedur zum Anzeigen eines LOBs
CREATE PROCEDURE ShowBewerbung (InputName IN VARCHAR2) IS
puffer VARCHAR(60);
textdaten CLOB;
textlaenge DECIMAL;
position DECIMAL;
BEGIN
textlaenge := 60;
position := 1;
SELECT Bewerbung INTO textdaten
FROM MitarbeiterTupelTabelle
WHERE Name = InputName;
DBMS_LOB.READ(textdaten, textlaenge, position, puffer);
DBMS_OUTPUT.PUT_LINE(puffer);
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-42
Referenztyp
z
REF: Referenz auf eine Instanz eines Objekttyps
–
z
Wert eines Referenzattributs ist eine OID
Beispiel: Ein Teil referenziert ein anderes Teil
CREATE TYPE TeilTyp AS OBJECT (
Nr
NUMBER(10),
Bezeichnung
VARCHAR(25),
Farbe
VARCHAR(15),
IstTeilVon
REF TeilTyp
);
z
Vorwärtsdeklaration ermöglicht zyklische Typdefinitionen
CREATE TYPE LieferantTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-43
Benutzerdefinierte Datentypen – Tabellentyp
z
TABLE: Geordnete Multimenge mit indiziertem Zugriff
–
–
–
–
–
Ohne Grössenbeschränkung
Alle Elemente besitzen denselben Typ
Elementindex als Iterator
Anfragen auf geschachtelte Tabelle anwendbar
Daten der geschachtelte Tabelle sind auch indizierbar
z
Syntax:
CREATE TYPE Tabellentypname AS TABLE OF Elementtyp
z
Beispiel:
CREATE TYPE HobbiesTabellenTyp AS TABLE OF VARCHAR(20);
ALTER TABLE MitarbeiterTupelTabelle
ADD Hobbies HobbiesTabellenTyp
NESTED TABLE Hobbies STORE AS Kundenhobbies;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-44
Benutzerdefinierte Datentypen – Variabler Arraytyp
z
VARRAY: Geordnete Multimenge mit indiziertem Zugriff
–
–
–
–
Grösse wird bei der Instanzierung festgelegt
Alle Elemente besitzen denselben Typ
Elementindex als Iterator
Achtung: VARRAY-Werte nicht direkt in SQL, sondern nur in PL/SQL nutzbar
z
Syntax:
CREATE TYPE Arraytypname AS VARRAY(Arraygrösse) OF Elementtyp
z
Beispiel:
CREATE TYPE TelefonArrayTyp AS VARRAY(30) OF VARCHAR(20);
ALTER TABLE MitarbeiterTupelTabelle ADD Telefone TelefonArrayTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-45
Verwendung von Konstruktoren
Erzeugen von Instanzen eines Array- bzw. Tabellentyps:
INSERT INTO MitarbeiterTupelTabelle (Name, Hobbies, Telefone)
VALUES ('Johnny',
HobbiesTabellenTyp('Reisen', 'Sport', 'Kino'),
TelefonArrayTyp('0041-1-6327248', '0041-1-7337947'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-46
Kollektionen und Nullwerte
Werte für ein kollektionswertiges Attribut (hier Telefone):
TelefonArraytyp('0041-1-223456', NULL, '0041-1-3458122')
TelefonArraytyp('0090-313-2445561', NULL)
TelefonArraytyp('0049-6124-13297')
TelefonArraytyp(NULL)
TelefonArraytyp()
NULL
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Kardinalität:
3
2
1
1
0
NULL
4-47
Selektion von array-und tabellenwertigen Attributen
z
Zugriff auf komplette Kollektion erfolgt analog zu anderen Attributen
SELECT Name, Telefone, Hobbies
FROM MitarbeiterTupelTabelle;
z
Selektion und Belegung einzelner Arrayelemente nur innerhalb von
PL/SQL-Anweisungen möglich
z
Selektion und Belegung einzelner Elemente einer inneren Tabelle
hingegen auch in SQL-Anweisungen möglich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-48
PL/SQL-Methoden für Kollektionen
(i) liefert i-tes Element
COUNT gibt Kardinalität aus
EXTEND vergrössert Kollektion um ein NULL-Element
FIRST bzw. LAST liefern den kleinsten bzw. grössten verwendeten Index
PRIOR bzw. NEXT liefern den vorhergehenden bzw. nachfolgenden Index
DELETE(i) löscht das i-te Element
TRIM(i) löscht die letzten i Elemente
LIMIT liefert maximal zulässige Anzahl von Elementen eines Arrays
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-49
Selektion einzelner Elemente aus Arrays/inneren Tabellen
SET SERVEROUTPUT ON;
DECLARE
CURSOR mcursor IS SELECT * FROM MitarbeiterTupelTabelle;
mitarbeiter mcursor%ROWTYPE;
BEGIN
FOR mitarbeiter IN mcursor LOOP
DBMS_OUTPUT.PUT_LINE('Kundenname: ' || mitarbeiter.Name);
FOR i IN 1..mitarbeiter.Telefone.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(i || '.Telefon: ' || mitarbeiter.Telefone(i));
END LOOP;
FOR i IN 1..mitarbeiter.Hobbies.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(i || '.Hobby: ' || mitarbeiter.Hobbies(i));
END LOOP;
END LOOP;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-50
Einfügen einzelner Elemente in Arrays
CREATE PROCEDURE NeueNummer (n VARCHAR, nr VARCHAR)
AS
t TelefonArrayTyp;
BEGIN
SELECT Telefone INTO t
FROM MitarbeiterTupelTabelle
WHERE Name = n;
IF t IS NULL THEN t := TelefonArrayTyp(nr);
ELSE
IF t.COUNT < t.LIMIT THEN
t.EXTEND;
END IF;
t(t.LAST) := nr;
END IF;
UPDATE MitarbeiterTupelTabelle
SET Telefone = t
WHERE Name = n;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-51
Belegung kollektionswertiger Attribute
mittels SFW-Query
Grundmuster:
INSERT INTO Tabelle (Kollektionsattribut)
VALUES ([CAST(MULTISET] (SFW-Query) [AS Tabellentypkonstruktor)])
Beispiel:
INSERT INTO MitarbeiterTupelTabelle (Name, Hobbies, Telefone)
VALUES ('John',
(CAST (MULTISET(SELECT DISTINCT VALUE(h)
FROM MitarbeiterTupelTabelle, TABLE(Hobbies) h
WHERE name = 'Billy' OR name = 'Can')
AS HobbiesTabellenTyp)),
(SELECT Telefone FROM MitarbeiterTupelTabelle WHERE Name = 'Billy'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-52
Bearbeitung innerer Tabellen
Tupel in eine innere Tabelle einfügen:
INSERT INTO TABLE(SFW-Query-Liefert-Innere-Tabelle)
VALUES (...);
Tupel einer inneren Tabelle ändern:
UPDATE TABLE(SFW-Query-Liefert-Innere-Tabelle)
SET ...
WHERE ...;
Tupel aus einer inneren Tabelle löschen:
DELETE FROM TABLE(SFW-Query-Liefert-Innere-Tabelle)
WHERE ...;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-53
Bearbeitung innerer Tabellen – Beispiele
INSERT INTO TABLE(SELECT Hobbies
FROM MitarbeiterTupelTabelle
WHERE Name = 'Billy') VALUES('Kino');
UPDATE TABLE(SELECT Hobbies
FROM MitarbeiterTupelTabelle
WHERE Name = 'Billy') h
SET VALUE(h) = 'TV'
WHERE VALUE(h) = 'Kino';
VALUE ist hier notwendig, um
auf den Wert der Tupel der
inneren Tabelle zuzugreifen!
DELETE TABLE(SELECT Hobbies
FROM MitarbeiterTupelTabelle
WHERE Name = 'Billy') h
WHERE VALUE(h) = 'Reisen';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-54
Benutzerdefinierte Datentypen – Objekttyp
z
OBJECT: Strukturierter Typ mit Methoden und Subtypbildung
–
z
Defaultwerte und Integritätsbedingungen werden nicht unterstützt
Syntax für die Definition eines Wurzeltyps:
CREATE TYPE Typname AS OBJECT (
Attributdefinitions-und-Methodendeklarationsliste
[Abbildungs-bzw-Ordnungsmethode]
)
[[NOT] FINAL]
[[NOT] INSTANTIABLE]
–
Default: FINAL, INSTANTIABLE
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-55
Wurzeltypen – Beispiele (1)
CREATE TYPE PositionTyp AS OBJECT (
PosNr INTEGER,
Teil
REF TeilTyp,
Menge INTEGER,
Preis INTEGER
);
CREATE TYPE AuftragTabellenTyp
AS TABLE OF REF AuftragTyp;
CREATE TYPE VorratTyp AS OBJECT (
Anzahl DECIMAL(10),
Kosten DECIMAL(12,2),
RefTeil REF TeilTyp
);
CREATE TYPE PositionTabellenTyp
AS TABLE OF PositionTyp;
CREATE TYPE AuftragTyp AS OBJECT (
ANr
INTEGER,
Lieferant
REF LieferantTyp,
Eingang
DATE,
Bearbeitet
DATE,
Positionen PositionTabellenTyp,
MEMBER FUNCTION Anzahl
RETURN INTEGER
);
CREATE TYPE VorratTabellenTyp
AS TABLE OF VorratTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-56
Wurzeltypen – Beispiele (2)
CREATE TYPE AdresseTyp AS OBJECT (
Strasse
VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)
) NOT FINAL;
CREATE TYPE LieferantTyp AS OBJECT (
LNr
INTEGER,
Name
VARCHAR(25),
Adresse AdresseTyp,
Telefone TelefonArrayTyp,
Vorrat
VorratTabellenTyp
);
CREATE TYPE KundeTyp AS OBJECT (
KNr
INTEGER,
Name
VARCHAR(30),
Anschrift AdresseTyp,
Telefone TelefonArrayTyp,
Auftraege AuftragTabellenTyp,
MEMBER FUNCTION NeueNummer(t VARCHAR) RETURN TelefonArrayTyp
) NOT FINAL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-57
Subtypbildung – Aufbau von Typhierarchien
z
Syntax:
–
–
–
z
CREATE TYPE Subtypname UNDER Supertypname (
Attributdefinitions-und-Methodendeklarationsliste
[Überschreibende-Abbildungsmethode]
)
[[NOT] FINAL]
[[NOT] INSTANTIABLE]
Subtyp erbt alle Attribute und Methoden des Supertyps
Subtyp hat nur einen direkten Supertyp (keine Mehrfachvererbung)
Geerbte Methoden sind überschreibbar
Beispiel:
CREATE TYPE BWKundeTyp UNDER KundeTyp (
Hobbies HobbiesTabellenTyp,
Kredit
DECIMAL(9,2)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-58
Operationen auf Objekttypen
z
Default-Konstruktor heisst wie der Objekttyp
und hat für jedes Attribut einen Parameter
Objekttypname(p1, ..., pn)
z
Attributzugriff mittels Punktnotation
o.Attributname
z
Objektvergleich basiert auf flacher Gleichheit
o1 = o2
z
Referenzvergleich ist auch möglich
REF(o1) = REF(o2)
z
Typtest mittels IS OF
o IS OF (Objekttypname)
z
Temporäre Typanpassung mittels TREAT
TREAT (o AS Objekttypname)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-59
Methodendeklaration
z
Syntax:
[[NOT] OVERRIDING]
[[NOT] FINAL]
[[NOT] INSTANTIABLE]
{MEMBER|STATIC}
{FUNCTION|PROCEDURE}
Methodenname
[(Parameterliste)]
[RETURN Rückgabetyp]
OVERRIDING: überschreibende Methode
FINAL: nicht-überschreibbare Methode
INSTANTIABLE: abstrakte Methoden;
Objekttyp bietet keine Implementierung
DEFAULT:
NOT OVERRIDING, NOT FINAL, INSTANTIABLE
MEMBER: Instanzmethode wird auf einem Objekt aufgerufen
(besitzt impliziten SELF-Parameter)
STATIC: statische Methode wird auf dem Objekttyp aufgerufen
PROCEDURE: hat keinen Rückgabewert
FUNCTION: hat einen Rückgabewert, darf aber keine Objektattributwerte ändern
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-60
Methodenimplementierung
z
Methodendeklaration und -implementierung erfolgen strikt getrennt
–
–
z
Deklaration erfolgt innerhalb der Definition des Objekttyps
Implementierung in einer separaten Anweisung (hier in PL/SQL)
Beispiel:
CREATE TYPE BODY AuftragTyp AS
MEMBER FUNCTION Anzahl RETURN INTEGER
AS
a INTEGER;
BEGIN
FOR i IN 1..SELF.Positionen.COUNT LOOP
a := a + SELF.Positionen(i).Menge;
END LOOP;
RETURN a;
END;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-61
Methodenimplementierung – Weiteres Beispiel
CREATE TYPE BODY KundeTyp AS
MEMBER FUNCTION NeueNummer(nr VARCHAR) RETURN TelefonArrayTyp
IS
t TelefonArrayTyp := SELF.Telefone;
BEGIN
IF t IS NULL THEN t := TelefonArrayTyp(nr);
ELSE
IF t.COUNT < t.LIMIT THEN
t.EXTEND;
END IF;
t(t.LAST) := nr;
END IF;
RETURN t;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-62
Verwendung von benutzerdefinierten Typen – Beispiel
CREATE TABLE KundeTupelTabelle (
KNr
INTEGER,
Name
VARCHAR(30),
Anschrift AdresseTyp,
Telefone TelefonArrayTyp,
Auftraege AuftragTabellenTyp
)
NESTED TABLE Auftraege STORE AS Kundenauftraege;
objektwertiges Attribut
arraywertiges Attribut
kollektionswertiges Attribut
INSERT INTO KundeTupelTabelle
VALUES ( 13,
'Billy',
AdresseTyp('Whitesteet', 31, 65307, 'Newtown', 'USA'),
TelefonArrayTyp('0041-1-6789332', '0049-6124-91267'),
AuftragTabellenTyp(NULL));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-63
Benutzerdefinierte Gleichheit von Objekten
z
Systemdefinierte Gleichtheit von Instanzen von Objekttypen basiert auf
paarweise Gleichheit der Attributwerte (Flache Gleichheit)
z
Benutzerdefinierte Gleichheit bzw. Ordnung
–
–
z
MAP: Vergleich bzw. Ordnung basiert auf dem Ergebnis einer
Abbildungsfunktion, die Objektwerte auf Werte von Basisdatentypen
(Zahlen, Zeichenketten, Datum) abbildet
ORDER: Ordnungsfunktion ordnet jeweils zwei Instanzen
Syntax:
MAP MEMBER FUNCTION Funktioname RETURN Typ
ORDER MEMBER FUNCTION Funktioname (o Objekttyp) RETURN INTEGER
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-64
Benutzerdefinierte Gleichheit von Objekten (Forts.)
Für jeden Objekttyp darf max. eine Map- oder Order-Funktion definiert sein
CREATE TYPE AdresseTyp AS OBJECT (
Attributdefinitionen,
...
MAP MEMBER FUNCTION AdresseMap RETURN VARCHAR2
-- Alternativ könnte folgende ORDER-Funktion definiert werden
-- ORDER MEMBER FUNCTION AdresseOrder (o AdresseTyp) RETURN INT
);
Beispiel: Implementierung einer Map-Funktion
CREATE TYPE BODY AdresseTyp AS
MAP MEMBER FUNCTION AdresseMap
RETURN VARCHAR2
AS
BEGIN
RETURN Land || Ort || PLZ || Strasse || Nr;
END;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-65
Beispiel: Implementierung einer Order-Funktion
CREATE TYPE BODY AdresseTyp AS
ORDER MEMBER FUNCTION AdresseOrder (a AdresseTyp)
RETURN INTEGER
AS
a1 VARCHAR2(200);
a2 VARCHAR2(200);
BEGIN
a1 := SELF.Land || SELF.Ort || SELF.PLZ || SELF.Strasse || SELF.Nr;
a2 := a.Land || a.Ort || a.PLZ || a.Strasse || a.Nr;
IF (a1 = a2) THEN RETURN 0;
ELSE IF (a1 < a2) THEN RETURN -1;
ELSE RETURN 1;
END IF;
END IF;
END;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-66
Verwendung von Map- bzw. Order-Funktionen
Beispielanfragen, in denen eine Map- bzw. Order-Funktion zum Einsatz
kommt:
SELECT Name, Anschrift
FROM KundeTupelTabelle
ORDER BY Anschrift;
SELECT Name, Anschrift
FROM KundeTupelTabelle
WHERE Anschrift < AdresseTyp('Seefeldstrasse', 31, 8008, 'Zurich', 'CH');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-67
Objekttabelle
z
Basiert auf einem Objekttyp
z
Speichert pro Zeile eine Instanz des Objekttyps
z
Kann Integritätsbedingungen enthalten
– Primär-/Unique-/Fremdschlüssel, Not Null, Check-Klausel, Scope-Klausel
z
Muss für jedes tabellenwertige Attribut je eine STORE-Klausel enthalten
z
Subtabellenbildung ist nicht möglich
z
Syntax:
CREATE TABLE Tabellenname OF Objektttypname
[Substitutionsklausel]
[(Integritätsbedingungsdefinitionsliste)]
[OID-Klausel]
[STORE-Klausel]
[Attributsubstitutionsklauseln]
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-68
Objekttabellen (Fort.)
z
OID-Klausel legt Art der Referenzgenerierung fest
– Systemgeneriert oder vom Primärschlüssel abgeleitet
OBJECT IDENTIFIER IS {SYSTEM GENERATED | PRIMARY KEY}
z
Eine Objekttabelle darf auch Instanzen eines Subtyps aufnehmen (Prinzip der
Substituierbarkeit)
– Substitutionsklausel schliesst Substitution der Instanzen aus
NOT SUBSTITUTABLE AT ALL LEVELS
z
Attributsubstitutionsklausel schliesst Substitution eines objektwertigen Attributs
aus
COLUMN Attributname
{NOT SUBSTITUTABLE AT ALL LEVELS | IS OF (ONLY Objekttypname)}
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-69
Objekttabellen – Beispiele (1)
CREATE TABLE Kunde OF KundeTyp (
KNr
PRIMARY KEY,
Name
NOT NULL,
Anschrift NOT NULL,
CHECK(Anschrift.PLZ IS NOT NULL)
)
NESTED TABLE Auftraege STORE AS Kundenauftraege
COLUMN Anschrift IS OF (ONLY AdresseTyp);
CREATE TABLE Auftrag OF AuftragTyp
NOT SUBSTITUTABLE AT ALL LEVELS
(
ANr
PRIMARY KEY
)
NESTED TABLE Positionen STORE AS Positionen;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-70
Objekttabellen – Beispiele (2)
Beispiel für eine Objekttabelle, die nur Instanzen eines Subtyps aufnimmt
CREATE TABLE BWKunde OF BWKundeTyp
NOT SUBSTITUTABLE AT ALL LEVELS (
KNr
PRIMARY KEY,
Name
NOT NULL,
Anschrift NOT NULL,
CHECK(Anschrift.PLZ IS NOT NULL),
CHECK(Kredit > 0),
)
NESTED TABLE Auftraege STORE AS BWKundenauftraege
NESTED TABLE Hobbies STORE AS BWKundenhobbies
COLUMN Anschrift IS OF (ONLY AdresseTyp);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-71
Operationen auf Objekttabellen
INSERT INTO Kunde
VALUES ( 34, 'Johnny',
AdresseTyp('Fifth Avenue', 45, 45566, 'Bigtown', 'USA'),
TelefonArrayTyp('0041-1-6725655', '0049-454-364666'),
AuftragTabellenTyp(NULL));
UPDATE Kunde
SET Anschrift = AdresseTyp('Kreuzstrasse', 21, 8008, 'Zurich', 'CH')
WHERE Name = 'Johnny';
DELETE FROM Kunde k
WHERE k.Anschrift.Ort = 'Zurich';
Achtung: Korrelationsvariable k ist
notwendig, um auf Objektattribute
zugreifen zu können!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-72
Substituierbarkeit
Erzeugen eines Kunden mittels explizitem Konstruktor-Aufrufs:
INSERT INTO Kunde
VALUES (KundeTyp( 34, 'Johnny',
AdresseTyp('Fifth Avenue', 45, 45566, 'Bigtown', 'USA'),
TelefonArrayTyp('0041-1-6725655', '0049-454-364666'),
AuftragTabellenTyp(NULL)));
Erzeugen eines BWKunden mittels explizitem Konstruktor-Aufrufs:
INSERT INTO Kunde
VALUES (BWKundeTyp( 23,
'Jim',
AdresseTyp('Fifth Avenue', 45, 45566, 'Bigtown', 'USA'),
TelefonArrayTyp('0041-1-6761256'),
AuftragTabellenTyp(NULL),
HobbiesTabellenTyp(NULL),
10000));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-73
Updates – Attributwertbelegungen
z
Belegen von arraywertigen Attributen
UPDATE Tabelle SET Arraywertiges-Attribut =VArraytypkonstruktor()
z
Belegen von tabellenwertigen Attributen
UPDATE Tabelle SET Tabellenwertiges-Attribut
=([CAST(MULTISET] (SFW-Query) [AS Tabellentypkonstruktor)])
z
Belegen von Referenzattributen
UPDATE Tabelle SET Referenzattribut =(SELECT REF(t)
FROM ReferenzierteTabelle t)
z
Belegen von objektwertigen Attributen
UPDATE Tabelle SET objektwertiges-Attribut=Objekttypkonstruktor(...)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-74
Objektsichten
z
Objektsicht = abgeleitete, virtuelle Objekttabelle
–
–
z
kann Daten aus mehreren Tabellen enthalten
objekterhaltende Sicht ist nicht auf die flache Extension einer Objekttabelle
beschränkt
Syntax zur Definition einer Wurzelsicht:
CREATE VIEW Sichtenname OF Objekttyp
[WITH OBJECT IDENTIFIER (Attributliste)]
AS Anfrageausdruck
[WITH {CHECK OPTION |READ ONLY}]
–
–
–
objektgenerierende Sicht muss WITH-OBJECT-IDENTIFIER-Klausel enthalten
CHECK OPTION = geänderte Daten dürfen nicht aus der Sicht verschwinden
READ ONLY = nicht-änderbare Sicht
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-75
Wurzelsichten – Beispiele
CREATE VIEW ZHKunde OF KundeTyp
AS (SELECT VALUE(k)
FROM Kunde k
WHERE VALUE(k) IS OF (ONLY KundeTyp) AND
k.Anschrift.Ort = 'Zurich');
Objektsicht basierend
auf Objekttabelle
→ Objekterhaltung
CREATE VIEW ZHKundeTupel OF KundeTyp
WITH OBJECT IDENTIFIER (KNr)
AS (SELECT KNr, Name, Anschrift, Telefone, Auftraege
FROM KundeTupelTabelle k
WHERE k.Anschrift.Ort = 'Zurich');
Objektsicht basierend
auf Tupeltabelle
→ Objektgenerierung
Eine objektgenerierende Sicht kann auch auf einer Objekttabelle definiert sein
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-76
Subsichten
z
Syntax zur Definition einer Subsicht:
CREATE VIEW Subsichtenname OF Subtyp
UNDER Supersichtenname
AS Anfrageausdruck
[WITH {CHECK OPTION |READ ONLY}]
–
–
–
–
Typ der Subsicht muss ein direkter Subtyp des Typs der Supersicht sein
Extension der Subsicht ist immer eine Untermenge der Extension der
Supersicht
„ Genauer: Subsicht erweitert die Extension der Supersicht
Subsicht hat genau eine direkte Supersicht
„ Keine (direkte) Mehrfachspezialisierung möglich
Jede Objektsicht darf pro Subtyp maximal eine Subsicht haben
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-77
Subsichten – Beispiele
CREATE VIEW ZHBWKunde OF BWKundeTyp
UNDER ZHKunde
AS (SELECT VALUE(k)
FROM BWKunde k
WHERE VALUE(k) IS OF (ONLY BWKundeTyp) AND
k.Anschrift.Ort = 'Zurich');
Objekterhaltende
Subsicht
CREATE VIEW ZHBWKundeObjekt OF BWKundeTyp
UNDER ZHKundeTupel
AS (SELECT KNr, Name, Anschrift, Telefone, Auftraege,
NULL, NULL
FROM Kunde k
WHERE VALUE(k) IS OF (ONLY BWKundeTyp) AND
k.Anschrift.Ort = 'Zurich');
Objektgenerierende
Subsicht
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-78
Oracle9i – Datenmodell
SET
MULTISET
OBJECT
ROW
REF
Einstiegspunkte
Objekttabelle:
Tupeltabelle:
VARRAY
TABLE
SET(OBJECT(...))
MULTISET(ROW(...))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Basisdatentyp
Subtypbeziehung
4-79
Oracle 9i – Datenmodellüberblick
UNDER
Attributtypen
Basistyp
Tabellentyp
Arraytyp
Referenztyp
Attributtypen
OF
Tupeltabelle
(Menge von Tupeln
eines Tupeltyps)
Tupelsicht
(Menge von Tupeln
eines Tupeltyps)
Objekttyp
Objekttabelle
(Menge von Objekten
eines Objekttyps)
Sichtberechnung
basiert auf
UNDER
OF
Objektsicht
(Menge von Objekten
eines Objekttyps)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-80
Definition einer Funktion – Beispiel
CREATE FUNCTION AnzahlBestellteArtikel(k INTEGER) RETURN INTEGER
IS
auftragsmenge AuftragTabellenTyp;
postab PositionTabellenTyp;
gesamt INTEGER := 0;
BEGIN
SELECT Auftraege INTO auftragsmenge
FROM Kunde WHERE KNr = k AND Auftraege IS NOT NULL;
IF auftragsmenge IS NOT NULL THEN
FOR c IN 1..auftragsmenge.COUNT LOOP
IF auftragsmenge(c) IS NOT NULL THEN
SELECT Positionen INTO postab
FROM Auftrag a
WHERE REF(a) = auftragsmenge(c);
FOR d IN 1..postab.COUNT LOOP
gesamt := gesamt + postab(d).Menge;
END LOOP;
END IF;
END LOOP;
END IF;
RETURN gesamt;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-81
SFW-Block (1)
z
SELECT: Was darf in der Projektionsliste stehen?
–
–
–
z
Attribute (auch abgeleitete, berechnete)
Methoden-/Funktionsaufrufe
Unterabfragen
9
9
9
Beispiel:
SELECT Name, AnzahlBestellteArtikel(Name),
(SELECT COUNT(*)
FROM TABLE(Telefone) t
WHERE VALUE(t) LIKE '0041%'),
(CAST(MULTISET(SELECT *
FROM TABLE(Telefone) t
WHERE VALUE(t) LIKE '0049%')
AS TelefonarrayTyp))
FROM Kunde k;
Skalare Unterabfrage, die
genau einen Wert liefert
Unterabfragen, die mehrere
Werte liefern, müssen mit
MULTISET umhüllt und in
Kollektionstyp gecastet werden
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-82
SQL-Erweiterungen – Neue Ausdrücke
z
Zugriff auf kollektionswertiges Attribut
SELECT Kollektionswertiges-Attribut FROM Tabelle
z
Zugriff auf Referenz (OID des referenzierten Objekts)
SELECT Referenzattribut FROM Tabelle
z
Zugriff auf eingebettetes Objekt bzw. Objektwert
SELECT Objektwertiges-Attribut FROM Tabelle
z
Zugriff auf Objektwert
SELECT VALUE(v) FROM Objekttabelle v
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-83
SQL-Erweiterungen – Neue Ausdrücke
z
Zugriff auf Referenz (Objektidentifikator in interner Darstellung)
SELECT REF(v) FROM Objekttabelle v
z
Dereferenzierung (liefert Wert des referenzierten Objekts)
SELECT DEREF(Referenzattribut) FROM Tabelle
–
z
DEREF ist nicht direkt anwendbar auf (Objekttabellen-)Variablen
Methodenaufrufe erlaubt in SELECT- und WHERE-Klauseln
SELECT v.Methodenname(Parameter) FROM Objekttabelle v;
z
Typkonvertierungen
CAST(Arraywertiges-Attribut AS Tabellen-Typ)
CAST(MULTISET(SFW-Query) AS Tabellen-Typ)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-84
SQL-Erweiterungen – Beispiele für Pfadausdrücke (1)
SELECT Anschrift FROM Kunde;
liefert die eingebetteten Anschrift-Objekte aller Kunden
SELECT k.Anschrift.Ort FROM Kunde k;
liefert die Wohnorte aller Kunden
SELECT Lieferant FROM Auftrag;
liefert die Objektidentifikatoren aller referenzierten Lieferanten
SELECT k.Lieferant.LNr FROM Auftrag k;
liefert die Lieferantennummer aller referenzierten Lieferanten
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-85
SQL-Erweiterungen – Beispiele für Pfadausdrücke (2)
SELECT DEREF(Lieferant) FROM Auftrag;
liefert die Objektwerte der referenzierten Lieferanten
Ergebnistyp=LieferantTyp(LNr, Name, ...)
SELECT a.ANr
FROM
Auftrag
WHERE
a.Lieferant.Name
= 'ETH
bestimmt
alle
Aufträge
ana den
Lieferanten
"ETH Zurich
" Zurich';
Alternative Formulierung mit einem Verbund über Referenzen
SELECT a.ANr
FROM Auftrag a, Lieferant l
WHERE a.Lieferant = REF(l) AND l.Name = 'ETH Zurich';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-86
SQL-Erweiterungen – Beispiel für
Konvertierungsfunktionen
SELECT a.Eingang, CAST(MULTISET(SELECT REF(b)
FROM Auftrag b
WHERE a.Eingang=b.Eingang)
AS AuftragTabellenTyp)
FROM Auftrag a;
liefert zu jedem Eingangsdatum die Menge der Aufträge
z
Verwendungszweck von MULTISET ist beschränkt
–
–
Kann nicht einer Aggregatfunktion übergeben werden
Kann nicht mit Mengenoperationen weiterverarbeitet werden
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-87
SFW-Block (2)
z
FROM: Welche Tabellenausdrücke sind erlaubt?
–
–
–
–
–
z
Tupeltabellen
Objekttabellen
Kollektionsabgeleitete Tabellen
Funktionsaufrufe (die Tabellen liefern)
Unterabfragen (abgeleitete Tabellen)
9
9
9
—
9
Beispiel: Kollektionsabgeleitete bzw. mittels Unterabfragen abgeleitete Tabellen
SELECT * FROM Kunde k, TABLE(k.Auftraege) a;
SELECT * FROM (SELECT Auftraege FROM Kunde);
SELECT * FROM TABLE(SELECT Auftraege FROM Kunde WHERE Name = 'Billy');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-88
SQL-Erweiterungen – Beispiele
z
Unteranfrage als Wertebereich für eine Variable
SELECT ANr, AVG(AnzahlKundenAuftraege)
FROM (SELECT ANr, COUNT(*) AS AnzahlKundenAuftraege
FROM Auftrag GROUP BY ANr)
GROUP BY ANr;
z
Qualifikation einer inneren Tabelle als Wertebereich
SELECT *
FROM TABLE(SELECT Positionen FROM Auftrag WHERE ANr=1704);
–
Ausdruck TABLE(SFW-Query) bezeichnet einen neuen Wertebereich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-89
SFW-Block (3)
z
WHERE: Welche Prädikate sind erlaubt?
–
–
–
z
Prädikate über Attribute
Prädikate mit Funktionsaufrufen
Prädikate mit Unterabfragen
9
9
9
Beispiel:
SELECT *
FROM Kunde k
WHERE Name LIKE 'C%' AND AnzahlBestellteArtikel(Name) > 5 AND
Telefone IS NOT NULL AND
EXISTS(SELECT *
FROM TABLE(Telefone) t
WHERE VALUE(t) LIKE '0041%');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-90
Beispiel: Anfragen auf geschachtelten
Tabellen
A AN
{VA}
VNR VTB
Abteilungen
{TA}
TNR TTB
{LG}
LN J
75
1 Forschung
121
Bibliothek
511 Programmentwicklung 1
122
Sektretariat
2 76
130
Übersetzung
5 79
140 Patente
552 Grundlagen
1
82
169 Sektretariat
2 79
678 Planung
2 76
4 82
2 Entwicklung 119
Sektretariat 650 Design
1
75
125
Übersetzung
4 77
135
Beschaffung 780 Wartung
1
75
981 Planung
2 81
3
82
4 Test
NULL
952 Design
1
75
4 77
82
573 Planung
3
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-91
Umsetzung mit Tabellen- und Objekttypen
CREATE TYPE Lehrgang AS OBJECT (LN INTEGER, J INTEGER);
CREATE TYPE Lehrgaenge AS TABLE OF Lehrgang;
CREATE TYPE TechnischerAngestellter AS OBJECT (TNR INTEGER,
TTB VARCHAR(25),
LG Lehrgaenge);
CREATE TYPE TechnischeAngestellte AS TABLE OF TechnischerAngestellter;
CREATE TYPE VerwaltungsAngestellter AS OBJECT (VNR INTEGER,
VTB VARCHAR(25));
CREATE TYPE VerwaltungsAngestellte AS TABLE OF VerwaltungsAngestellter;
CREATE TYPE Abteilung AS OBJECT (A INTEGER,
AN VARCHAR(20),
VA VerwaltungsAngestellte,
TA TechnischeAngestellte);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-92
Objekttabelle mit geschachtelten,
tabellenwertigen Attributen
CREATE TABLE Abteilungen OF Abteilung
(
A PRIMARY KEY,
AN NOT NULL
)
NESTED TABLE VA STORE AS VA
NESTED TABLE TA STORE AS TA
(NESTED TABLE LG STORE AS LG);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-93
Einfügen geschachtelter Objekte (1)
INSERT INTO Abteilungen VALUES (
1,
'Forschung',
VerwaltungsAngestellte(VerwaltungsAngestellter(121, 'Bibliothek'),
VerwaltungsAngestellter(122, 'Sekretariat'),
VerwaltungsAngestellter(130, 'Uebersetzung'),
VerwaltungsAngestellter(140, 'Patente'),
VerwaltungsAngestellter(169, 'Sekretariat')),
TechnischeAngestellte(TechnischerAngestellter(511, 'Programmentwicklung',
Lehrgaenge(Lehrgang(1,75), Lehrgang(2,76),
Lehrgang(5,79))),
TechnischerAngestellter(552, 'Grundlagen',
Lehrgaenge(Lehrgang(1,82),Lehrgang(2,79))),
TechnischerAngestellter(678, 'Planung',
Lehrgaenge(Lehrgang(2,76), Lehrgang(4,82))))
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-94
Einfügen geschachtelter Objekte (2)
INSERT INTO Abteilungen VALUES (
2,
'Entwicklung',
VerwaltungsAngestellte(VerwaltungsAngestellter(119, 'Sekretariat'),
VerwaltungsAngestellter(125, 'Uebersetzung'),
VerwaltungsAngestellter(135, 'Beschaffung')),
TechnischeAngestellte (TechnischerAngestellter(650, 'Design',
Lehrgaenge(Lehrgang(1,75), Lehrgang(4,77))),
TechnischerAngestellter(780, 'Wartung',
Lehrgaenge(Lehrgang(3,82))),
TechnischerAngestellter(981, 'Planung',
Lehrgaenge(Lehrgang(2,81), Lehrgang(3,82))))
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-95
Einfügen geschachtelter Objekte (3)
INSERT INTO Abteilungen VALUES (
4,
'Test',
NULL,
TechnischeAngestellte(TechnischerAngestellter(952, 'Design',
Lehrgaenge(Lehrgang(1,75), Lehrgang(4,77))),
TechnischerAngestellter(573, 'Planung',
Lehrgaenge(Lehrgang(3,82))))
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-96
Geschachtelte Projektionen
SELECT A, CURSOR(SELECT TNR, TTB
FROM TABLE(TA)) AS TA
FROM Abteilungen;
A
TNR
1 511
552
678
2 650
780
981
4 952
573
{TA}
TTB
Programmentwicklung
Grundlagen
Planung
Design
Wartung
Planung
Design
Planung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-97
Geschachtelte Projektionen –
Variante mit Ergebistabellentyp
CREATE TYPE ATYP AS OBJECT (NR INTEGER, TB VARCHAR(20));
CREATE TYPE ATAB AS TABLE OF ATYP;
SELECT A, CAST(MULTISET(SELECT TNR, TTB
FROM TABLE(TA)) AS ATAB) AS TA
FROM Abteilungen;
Darstellung
(in Oracle)
A
TA(NR, TB)
1 ATAB(ATYP(511,
Programmentwicklung),
ATYP(552, Grundlagen),
ATYP(678, Planung))
2 ATAB(ATYP(650, Design),
ATYP(780, Wartung),
ATYP(981, Planung))
4 ATAB(ATYP(952, Design),
ATYP(573, Planung))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-98
Geschachtelte Selektionen
SELECT *
FROM Abteilungen
WHERE EXISTS(SELECT *
FROM TABLE(VA)
WHERE VTB = 'Patente');
A AN
1
{VA}
VNR VTB
Forschung 121
122
130
140
169
{TA}
TNR TTB
{LG}
LN J
75
Bibliothek
511 Programmentwicklung 1
Sektretariat
2 76
Übersetzung
5 79
Patente
552 Grundlagen
1
82
Sektretariat
2 79
678 Planung
2 76
4 82
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-99
Geschachtelte Selektion innerhalb einer Projektion
SELECT A, CURSOR(SELECT VNR
FROM TABLE(VA)
WHERE VTB = 'Sekretariat') AS VA
FROM Abteilungen;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
A {VA}
VNR
1 122
169
2 119
4 NULL
4-100
Geschachtelte Selektion innerhalb einer Projektion –
Variante mit Ergebistabellentyp
CREATE TYPE BTYP AS OBJECT (NR INTEGER);
CREATE TYPE BTAB AS TABLE OF BTYP;
SELECT A, CAST(MULTISET(SELECT VNR
FROM TABLE(VA)
WHERE VTB = 'Sekretariat') AS BTAB) AS VA
FROM Abteilungen;
Darstellung
(in Oracle)
A
1
2
4
VA(NR)
BTAB(BTYP(122), BTYP(169))
BTAB(BTYP(119))
BTAB()
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-101
Geschachtelte Projektion innerhalb einer Selektion
SELECT *
FROM Abteilungen
WHERE 'Design' IN (SELECT TTB FROM TABLE(TA));
A AN
{VA}
VNR VTB
2
Entwicklun
g
119
125
135
4
Test
NULL
{TA}
TNR TTB
Sektretariat 650
Übersetzung
Beschaffung 780
981
952
573
{LG}
LN J
Design
1
75
4
77
Wartung 1
75
Planung 2
81
3
82
Design
1
75
4
77
Planung 3
82
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-102
"Entschachtelung"
SELECT A, AN, VNR, VTB
FROM Abteilungen, TABLE(VA);
Entschachtelung entfernt äussere
Tupel/Objekte, deren innere Tabelle
NULL ist
Dies kann analog zum OUTER JOIN
wie folgt verhindert werden:
SELECT A, AN, VNR, VTB
FROM Abteilungen, TABLE(VA)(+);
A
1
1
1
1
1
2
2
2
AN
Forschung
Forschung
Forschung
Forschung
Forschung
Entwicklung
Entwicklung
Entwicklung
VNR
121
122
130
140
169
119
125
135
VTB
Bibliothek
Sektretariat
Übersetzung
Patente
Sektretariat
Sektretariat
Übersetzung
Beschaffung
A
1
1
1
1
1
2
2
2
4
AN
Forschung
Forschung
Forschung
Forschung
Forschung
Entwicklung
Entwicklung
Entwicklung
Test
VNR
121
122
130
140
169
119
125
135
NULL
VTB
Bibliothek
Sektretariat
Übersetzung
Patente
Sektretariat
Sektretariat
Übersetzung
Beschaffung
NULL
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-103
Komplett entschachtelte Darstellung
SELECT A, AN, VNR, VTB,
TNR, TTB, LN, J
FROM Abteilungen a,
TABLE(a.VA) v,
TABLE(a.TA) t,
TABLE(t.LG) l
WHERE a.A = 2;
Achtung: SELECT * würde
hier folgende 11 Spalten liefern:
A, AN, VA(VNR, VTB),
TA(TNR, TTB, LG(LN, J)),
VNR, VTB, TNR, TTB,
LG(LN, J), LN, J
A
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
AN
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
Entwicklung
VNR
119
119
119
119
119
125
125
125
125
125
135
135
135
135
135
VTB
Sektretariat
Sektretariat
Sektretariat
Sektretariat
Sektretariat
Übersetzung
Übersetzung
Übersetzung
Übersetzung
Übersetzung
Beschaffung
Beschaffung
Beschaffung
Beschaffung
Beschaffung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
TNR
650
650
780
981
981
650
650
780
981
981
650
650
780
981
981
TTB
Design
Design
Wartung
Planung
Planung
Design
Design
Wartung
Planung
Planung
Design
Design
Wartung
Planung
Planung
LN
1
4
1
2
3
1
4
1
2
3
1
4
1
2
3
J
75
77
75
81
82
75
77
75
81
82
75
77
75
81
82
4-104
Hierarchische Anfragen (1)
Oracle bietet mit dem Konstrukt
START WITH ... CONNECT BY PRIOR die
Möglichkeit, hierarchische Anfragen zu
formulieren (eingeschränkt auf
azyklische Graphen; Hierarchien;
Bäume)
Ermittle alle (transitiven)
Flugverbindungen von Zürich
ausgehend
SELECT LPAD(' ', 3*(LEVEL-1))
|| Abflug
|| ' -> '
|| Ziel AS Flugverbindung
FROM Flug
START WITH Abflug = 'Zürich'
CONNECT BY PRIOR Ziel = Abflug;
Flug FlugNr
LH131
BA170
AF691
UA103
SR163
TK911
BA297
Abflug
Frankfurt
Zürich
Paris
Sydney
Zürich
Paris
London
Ziel
Sydney
London
New York
New York
Frankfurt
Istanbul
Frankfurt
Preis
1009
599
1299
1549
499
799
299
Flugverbindung
Zürich -> London
London -> Frankfurt
Frankfurt -> Sydney
Sydney -> New York
Zürich -> Frankfurt
Frankfurt -> Sydney
Sydney -> New York
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-105
Hierarchische Anfragen (2)
Flug FlugNr
LH131
BA170
AF691
UA103
SR163
TK911
BA297
Abflug
Frankfurt
Zürich
Paris
Sydney
Zürich
Paris
London
Ziel
Sydney
London
New York
New York
Frankfurt
Istanbul
Frankfurt
Preis
1009
599
1299
1549
499
799
299
Gebe alle Städte aus, die von
Zürich aus erreichbar sind
SELECT Ziel
FROM Flug
START WITH Abflug = 'Zürich'
CONNECT BY PRIOR Ziel = Abflug;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Ziel
London
Frankfurt
New York
Sydney
4-106
Hierarchische Anfragen (3)
Finde alle Städte, die von
Zürich mit maximal
zweimal Umsteigen
erreichbar sind
Flug FlugNr
LH131
BA170
AF691
UA103
SR163
TK911
BA297
SELECT DISTINCT 'Zürich' AS Abflug, LEVEL-1 AS Via
FROM Flug
WHERE LEVEL <= 3
START WITH Abflug = 'Zürich'
CONNECT BY PRIOR Ziel = Abflug
ORDER BY via;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Abflug
Frankfurt
Zürich
Paris
Sydney
Zürich
Paris
London
Abflug
Zürich
Zürich
Zürich
Zürich
Zürich
Zürich
Ziel
Sydney
London
New York
New York
Frankfurt
Istanbul
Frankfurt
Preis
1009
599
1299
1549
499
799
299
Ziel
Via
Frankfurt 0
London
0
Frankfurt
1
Sydney
1
New York 2
Sydney
2
4-107
Objektrelationale Erweiterungen in Informix
z
Neue Basisdatentypen
z
Typkonstruktoren
z
Benutzerdefinierte Typen
–
–
Distinct-Typen
Benannte Tupeltypen (mit Typvererbung)
z
Typisierte Tabellen (mit Subtabellenbildung)
z
Typisierte Sichten
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-108
Neue Basisdatentypen
z
BOOLEAN
–
z
Werte: 'T', 't', 'F', 'f', NULL
Large Objects
–
–
–
–
BLOB (Binary Large Object)
CLOB (Character Large Object)
Operationen
„ FILETOBLOB, FILETOCLOB, LOTOFILE, LOCOPY
„ = (bitweise Gleichheit), IS [NOT] NULL
Nicht erlaubt
„ BLOB/CLOB-Attribute als Teil eines Schlüssels oder in booleschen bzw.
arithmetischen Ausdrücken
„ GROUP BY, ORDER BY
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-109
Basisdatentypen
Basisdatentyp
Bedeutung
SMALLINT
(16 Bit) ganze Zahl [-32768, 32767]
INTEGER
(32 Bit) ganze Zahl [-2147483648, 2147483647]
INT8
(64 Bit) ganze Zahl [-9223372036854775808, 9223372036854775807]
DECIMAL(p,q)
NUMERIC(p,q)
Festkommazahl mit Genauigkeit p und q Nachkommastellen (p ≤ 32)
FLOAT(p)
REAL
DOUBLE PRECISION
Fließkommazahl mit Genauigkeit p (≤ 16)
CHAR(q)
Alphanumerische Zeichenkette mit fester Länge q (≤ 254)
VARCHAR(q)
Alphanumerische Zeichenkette mit variabler Länge q (≤ 254)
CLOB
Alphanumerische Zeichenkette mit variabler Länge bis 4TB
BLOB
Binäre Zeichenkette mit variabler Länge bis 2GB
DATE
Datum
DATETIME
Zeit
INTERVAL
Zeitinterval
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-110
Verwendung neuer Basisdatentypen
Definition einer Tabelle mit BOOLEAN- und LOB-Attributen:
CREATE TABLE MitarbeiterTupelTabelle (
Name
VARCHAR(30),
Vollzeit
BOOLEAN,
Bild
BLOB,
Bewerbung CLOB
);
Erzeugen von BOOLEAN- bzw. LOB-Attributen:
INSERT INTO MitarbeiterTupelTabelle
VALUES ('Billy', 'T',
FILETOBLOB('D:\ImgSamp\billy.gif', 'client'),
FILETOCLOB('D:\RsmSamp\billy.rsm', 'server'));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-111
Verwendung neuer Basisdatentypen (Forts.)
Kopieren von LOB-Attributwerten:
UPDATE MitarbeiterTupelTabelle
SET Bewerbung = (SELECT LOCOPY(Lebenslauf)
FROM PersonenTupelTabelle
WHERE Name = 'Billy')
WHERE Name = 'Billy';
Verwendung von BOOLEAN- und LOB-Attributen:
SELECT LOTOFILE(Bewerbung, 'D:\RsmSamp\' || Name || '.rsm', 'server')
FROM MitarbeiterTupelTabelle
WHERE Vollzeit = 'T';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-112
Typkonstruktoren
z
Tupeltypkonstruktor: ROW
–
z
Kollektionstypen: SET, MULTISET, LIST
–
–
z
Enthaltenseinstest (IN)
Kardinalität (CARDINALITY)
Einschränkungen
–
–
z
Tupelkomponentenselektion mittels Dot-Operator
Kollektionselemente müssen NOT NULL sein
Für tupelwertige bzw. kollektionswertige Attribute sind abgesehen von
NOT NULL keine Integritätsbedingungen erlaubt
Typkonstruktoren sind orthogonal (beliebig kombinierbar)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-113
Verwendung von Typkonstruktoren in
Tabellendefinitionen
CREATE TABLE PersonenTupelTabelle (
Name
VARCHAR(30),
Anschrift
ROW( Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land VARCHAR(25)),
Telefone
LIST(VARCHAR(20) NOT NULL),
Hobbies
SET(ROW(Hobby VARCHAR(15),
Aktiv BOOLEAN) NOT NULL),
Lebenslauf CLOB
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-114
Verwendung von Typkonstruktoren in
Einfügeoperationen
z
Einfügen eines neuen Kunden
INSERT INTO PersonenTupelTabelle
VALUES ('Billy',
ROW('Seefeldstrasse', 31, 8008, 'Zurich', 'CH'),
"LIST{'0041-1-6327248', '0041-1-7337947'}",
"SET{ROW('Reisen', 'T'), ROW('Sport', 'F')}" ,
FILETOCLOB('D:\RsmSamp\billy.rsm', 'server'));
–
Ungewöhnliche Instanziierung von Kollektionstypen mit Anführungsstrichen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-115
Selektion und Update von Tupelkomponenten
z
Zugriff auf Tupelkomponenten mittels Dot-Notation möglich
SELECT Name, Anschrift.Ort
FROM KundeTupelTabelle
WHERE Anschrift.Land = 'CH';
z
Tupelwertige Attribute sind nur als Ganzes änderbar
–
Ändern einzelner Tupelkomponenten nur innerhalb von SPL-Routinen möglich
UPDATE KundeTupelTabelle
SET Anschrift = ROW('Kreuzstrasse', 21, 8008, 'Zurich', 'Schweiz')
WHERE Name = 'Billy';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-116
Behandlung einzelner Tupelkomponenten
z
Nur innerhalb von SPL-Routinen unterstützt
CREATE PROCEDURE Prozedurname(Parameterliste)
...
END PROCEDURE;
z
Deklaration einer Tupelvariablen
DEFINE a ROW(Strasse VARCHAR(30), Nr DECIMAL(4),
PLZ DECIMAL(5), Ort VARCHAR(40), Land VARCHAR(25));
z
Zuweisung eines Werts an eine Tupelkomponente
LET a.Ort = 'Wiesbaden';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-117
Prozedur zum Ändern des Wohnortes einer Anschrift
CREATE PROCEDURE NeuerOrt(kname VARCHAR(30), kort VARCHAR(40))
DEFINE a ROW(Strasse VARCHAR(30),
Nr DECIMAL(4),
PLZ DECIMAL(5),
Ort VARCHAR(40),
Land VARCHAR(25));
SELECT Anschrift INTO a
FROM KundeTupelTabelle
WHERE Name = kname;
LET a.Ort = kort;
UPDATE KundeTupelTabelle
SET Anschrift = a
WHERE Name = kname;
END PROCEDURE;
Änderung durch
Prozeduraufruf:
CALL PROCEDURE
NeuerOrt('kname', 'kort');
Wünschenwert wäre
dennoch eine direkte
Manipulutation der Form:
UPDATE KundeTupelTabelle
SET Anschrift.Ort = 'kort';
WHERE Name = 'kname';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-118
Selektion und Update von Kollektionen
z
Zugriff auf ganze Kollektion
SELECT Name, Telefone
FROM KundeTupelTabelle
WHERE Anschrift.Land = 'CH';
z
Kollektionswertige Attribute sind nur als Ganzes änderbar
–
Einfügen, Ändern oder Löschen einzelner Kollektionselemente nur
innerhalb von SPL-Routinen möglich
UPDATE KundeTupelTabelle
SET Telefone = "LIST{'0041-1-6327248', '0041-1-7337947',
'0049-6151-295479'}"
WHERE Name = 'Billy';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-119
Operationen auf Kollektionen
z
Enthaltenseinstest (Nicht-Enthaltensein NOT IN)
SELECT Name
FROM KundeTupelTabelle
WHERE '0041-1-9439470' IN Telefone;
liefert den Namen der Kunden mit der Telefonnummer '0041-1-9439470'
z
Kardinalität
SELECT Name, CARDINALITY(Telefone)
FROM KundeTupelTabelle;
liefert für jeden Kunden die zugehörige Anzahl von Telefonnummern
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-120
Operationen auf Kollektionen (Forts.)
z
Kollektion in Tabelle umwandeln (Doppelklammerung bei SFW-Ausdrücken nötig)
SELECT *
FROM TABLE((SELECT Auftraege
FROM KundeTupelTabelle
WHERE Name='Billy')) a
WHERE a.Anzahl > 10;
liefert alle Aufträge vom Kunden 'Billy', die eine bestimmte Anzahl überschreiten
SELECT Name, (SELECT a.Anzahl
FROM TABLE(KundeTupelTabelle.Auftraege) a
WHERE a.ProduktNr = '435-663-AB')
FROM KundeTupelTabelle;
liefert die Kunden und die Anzahl ihrer bestellten Produkte mit der Nummer 17
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-121
Behandlung einzelner Kollektionselemente (1)
z
Nur innerhalb von SPL-Routinen unterstützt
CREATE PROCEDURE Prozedurname(Parameterliste)
...
END PROCEDURE;
z
Deklaration einer Kollektionsvariablen sowie einer Elementvariablen
DEFINE telefonnummern LIST(VARCHAR(20) NOT NULL);
DEFINE telefonnummer VARCHAR(20);
z
Belegen einer Kollektionsvariablen
LET telefonnummern = "LIST{'0041-1-9876543'}";
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-122
Behandlung einzelner Kollektionselemente (2)
z
Belegen einer Kollektionsvariable mittels SFW-Klausel
SELECT Telefone INTO telefonnummern
FROM KundeTupelTabelle
WHERE Name = 'Billy';
z
Einfügen eines Elements in eine Liste auf der Position 1
INSERT AT 1 INTO TABLE(telefonnummern) VALUES('0041-1-1872321');
z
Änderungen von Kollektionsvariablen haben keine Wirkung auf der DB
–
Änderungen müssen explizit mittels UPDATE in die DB eingebracht werden
UPDATE KundeTupelTabelle
SET Telefone = telefonnummern
WHERE Name = 'Billy';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-123
Behandlung einzelner Kollektionselemente (3)
z
Cursor für Kollektionsvariablen
FOREACH c FOR
SELECT Telefone INTO telefonnummern FROM KundeTupelTabelle
FOREACH d FOR
SELECT * INTO telefonnummer FROM TABLE(telefonnummern)
IF telefonnummer[1,4] = '0049' THEN
DELETE FROM TABLE(telefonnummern) WHERE CURRENT OF d;
END IF;
END FOREACH;
UPDATE KundeTupelTabelle
SET Telefone = telefonnummern
WHERE CURRENT OF c;
END FOREACH;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-124
Prozedur zum Einfügen einer neuen Telefonnummer
CREATE PROCEDURE NeueNummer(kname VARCHAR(30), tnr VARCHAR(20))
DEFINE telefonnummern LIST(VARCHAR(20) NOT NULL);
DEFINE telefonnummer VARCHAR(20);
SELECT Telefone INTO telefonnummern
FROM KundeTupelTabelle
WHERE Name = kname;
INSERT AT 1 INTO TABLE(telefonnummern)
VALUES(tnr);
UPDATE KundeTupelTabelle
SET Telefone = telefonnummern
WHERE Name = kname;
END PROCEDURE;
Einfügen durch Prozeduraufruf:
CALL PROCEDURE
NeueNummer('kname', 'tnr');
Wünschenwert wäre dennoch
eine direkte Manipulutation der
Form:
INSERT AT 1 INTO
TABLE(SELECT Telefone
FROM KundeTupelTabelle
WHERE Name = 'kname')
VALUES('tnr');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-125
Prozedur zum Entfernen einer bestimmten
Telefonnummer
CREATE PROCEDURE EntferneTelefonnummer(tnr VARCHAR(20))
DEFINE nummern LIST(VARCHAR(20) NOT NULL);
DEFINE nummer VARCHAR(20);
FOREACH c FOR
SELECT Telefone INTO nummern FROM KundeTupelTabelle WHERE tnr IN Telefone
FOREACH d FOR
SELECT * INTO nummer FROM TABLE(nummern)
IF nummer = tnr THEN
DELETE FROM TABLE(nummern) WHERE CURRENT OF d;
END IF;
END FOREACH;
UPDATE KundeTupelTabelle SET Telefone = nummern WHERE CURRENT OF c;
END FOREACH;
END PROCEDURE;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-126
Distinct-Typen
z
Kopie eines bereits existierendes Typs
–
–
–
z
Distinct-Typen dürfen auch auf benutzerdefinierten Typen basieren
Explizite Cast-Funktionen zur Konversion zwischen Distinct-Typ und Quelltyp
und umgekehrt automatisch bereitgestellt
Definition impliziter Cast-Funktionen erfordert vorheriges Löschen obiger
Funktionen
Syntax:
CREATE DISTINCT TYPE Distinct-Typname AS Quelltyp
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-127
Beispieldefinition und Verwendung von Distinct-Typen
Definition von Distinct-Typen:
CREATE DISTINCT TYPE Franken AS DECIMAL(12,2);
CREATE DISTINCT TYPE Euro AS DECIMAL(12,2);
Verwendung von Distinct-Typen in Tabellendefinitionen:
CREATE TABLE CHBank(Nr INTEGER, Stand Franken);
CREATE TABLE EuroBank(Nr INTEGER, Stand Euro);
Anfrage basierend auf Distinct-Typen:
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand > e.Stand;
Ungültige Anfrage: Schweizer Franken
und Euro nicht vergleichbar
→ Konvertierung ist notwendig!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-128
Verwendung von Cast-Funktionen
z
Alternative, äquivalente Formulierungen
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand::DECIMAL > e.Stand::DECIMAL;
... WHERE CAST(c.Stand AS DECIMAL) < CAST(e.Stand AS DECIMAL);
... WHERE c.Stand::DECIMAL < CAST(e.Stand AS DECIMAL);
... WHERE CAST(e.Stand AS DECIMAL) < e.Stand::DECIMAL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-129
Benutzerdefinierte Cast-Funktionen
z
Konversion zwischen unterschiedlichen Datentypen
z
Syntax:
–
–
–
–
CREATE [EXPLICIT|IMPLICIT] CAST (Quelltyp AS Zieltyp
WITH Konvertierungsfunktion)
Konvertierungsfunktion hat genau einen Parameter vom Typ Quelltyp
sowie Zieltyp als Rückgabetyp
Quelltyp und Zieltyp dürfen nicht vom Typ LIST, SET, MULTISET, ROW,
BLOB oder CLOB sein
IMPLICIT: Implizite Cast-Funktion (wird automatisch aufgerufen)
Defaulteinstellung: EXPLICIT
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-130
Explizite benutzerdefinierte Cast-Funktionen - Beispiel
Definition einer Konvertierungsfunktion:
CREATE FUNCTION EuroToFranken(e Euro)
RETURNS Franken
RETURN CAST((e::DECIMAL * 1.5) AS Franken);
END FUNCTION;
Definition einer expliziten Cast-Funktion:
CREATE CAST (Euro AS Franken WITH EuroToFranken);
Vorherige Anfrage nun mit direkter Konvertierung von Euro nach Franken:
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand > CAST(e.Stand AS Franken);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-131
Implizite benutzerdefinierte Cast-Funktionen - Beispiel
Definition einer Konvertierungsfunktion:
CREATE FUNCTION FrankenToEuro(f Franken)
RETURNS Euro
RETURN CAST((CAST(f AS DECIMAL) * 0.65) AS Euro);
END FUNCTION;
Definition einer impliziten Cast-Funktion:
CREATE IMPLICIT CAST (Franken AS Euro WITH FrankenToEuro);
Vorherige Anfrage nun mit impliziter Konvertierung von Franken nach Euro:
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand > e.Stand;
Achtung: Falls implizite Cast-Funktionen
in beiden Richtungen vorhanden sind,
wird automatisch nur der rechte Ausdruck
des Vergleichsprädikats konvertiert!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-132
Benannte Tupeltypen (Named Row Types)
z
Wiederverwendbare Tupeltypen
–
–
–
Typ eines Attributes kann auch ein Tupeltyp (ROW; benannt oder unbenannt)
oder Kollektionstyp (SET, MULTISET, LIST) sein
Typvererbung (Subtyp erbt alle Attribute des Supertyp) und Substituierbarkeit
Nicht unterstützt werden
„ Referenztypen und Objektidentifikatoren
„ Kapselung und Methoden
„ Defaultwerte und Integritätsbedingungen (abgesehen von NOT NULL)
z
Syntax für Wurzeltypdefinition:
z
Attributdefinition:
CREATE ROW TYPE Typname
(Attributdefinitionsliste)
Attributname Typ [NOT NULL]
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-133
Benannte Tupeltypen – Beispiele
CREATE ROW TYPE AdresseTyp (
Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)
);
CREATE ROW TYPE AuftragTyp (
ANr
INTEGER,
Lieferant VARCHAR(20),
Positionen LIST(ROW(ArtikelNr INTEGER,
Anzahl INTEGER)
NOT NULL)
);
CREATE ROW TYPE KundeTyp (
KNr
INTEGER NOT NULL,
Name
ROW(Vorname VARCHAR(25),
Nachname VARCHAR(35)) NOT NULL,
Anschrift
AdresseTyp,
Telefone
LIST(VARCHAR(20) NOT NULL),
Auftraege
SET(AuftragTyp NOT NULL)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-134
Attributtypen für Tupeltabellen
z
Mögliche Attributtypen:
–
–
–
Basisdatentypen
Mittels der Typkonstruktoren konstruierte (unbenannte) Typen
Benannte Tupeltypen
CREATE TABLE KundeTupelTabelle (
KNr
INTEGER NOT NULL,
Name
ROW(Vorname VARCHAR(25),
Nachname VARCHAR(35)) NOT NULL,
Anschrift
AdresseTyp,
Telefone
LIST(VARCHAR(20) NOT NULL),
Auftraege
SET(AuftragTyp NOT NULL)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-135
Typ-Instanziierung in Einfügeoperationen
z
Einfügen eines neuen Kunden
INSERT INTO KundeTupelTabelle
VALUES (17,
ROW('Billy', 'Schwarz'),
ROW('Seefeldstrasse', 31, 8008, 'Zurich', 'Schweiz')::AdresseTyp,
"LIST{'0041-1-6327248', '0041-1-7337947'}",
"SET{ROW(13, 'ETHWorld', LIST{ROW(453, 10)}),
ROW(70, 'XYZ', LIST{ROW(959, 50), ROW(911, 100)})}");
–
–
Tupeltypkonstruktoraufruf mit explizitem Casting (Doppel-Doppelpunkt)
notwendig, um eine Instanz eines benannten Tupeltyps zu erzeugen
Achtung: Innerhalb von Schachtelungen fallen sowohl das Casting bei
benannten Tupeltypen als auch die doppelten Anführungsstriche bei
Kollektionstypen weg
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-136
Subtypbildung – Aufbau von Typhierarchien
z
Syntax:
–
–
–
z
CREATE ROW TYPE Typname
(Attributdefinitionsliste)
UNDER Supertypname
Subtyp erbt alle Attribute des Supertyps
Supertyp muss ein selbst ein benannter Tupeltyp sein
Subtyp darf nur maximal einen direkten Supertyp haben
„ Keine (direkte) Mehrfachvererbung möglich
Beispiel:
CREATE ROW TYPE BWKundeTyp (
Interessen LIST(VARCHAR(20) NOT NULL)
) UNDER KundeTyp;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-137
Überladen von Routinen und Substituierbarkeit
z
Routinen (Prozeduren und Funktionen) sind überladbar
–
z
Instanz eines Subtyps kann in jedem Kontext benutzt werden, wo eine
Instanz eines Supertyps nutzbar ist
–
–
z
Routinen mit demselben Namen, aber unterschiedlicher Signatur sind erlaubt
Eingabeargumente für Routinen, deren formale Parameter auf dem Supertyp
definiert sind
Rückgabewert einer Routine, für die der Supertyp als Typ definiert wurde
Informix spricht hier von "Vererbung von Routinen" und sagt, dass
"geerbte" Routinen überladen, aber nicht überschrieben werden dürfen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-138
Definition von typisierten Tabellen
z
Typisierte Tabelle basiert auf einem benannten Tupeltyp
–
–
–
Speichert Instanz dieses Typs als Zeile der Tabelle
Besitzt kein OID-Attribut
„ Konzept der Objektidentifikatoren und Referenzen wird nicht unterstützt
Kann Integritätsbedingungen enthalten
„ Primär-/Unique-/Fremdschlüssel, Check-Klausel
z
Syntax:
CREATE TABLE Tabellenname OF TYPE BenannterTupeltyp
[(Integritätsbedingungsliste)]
z
Beispiel:
CREATE TABLE Kunde OF TYPE KundeTyp (
PRIMARY KEY(KNr)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-139
Operationen auf typisierten Tabellen
INSERT INTO Kunde
VALUES (17,
ROW('Billy', 'Schwarz'),
ROW('Seefeldstrasse', 31, 8008, 'Zurich', 'CH')::AdresseTyp,
"LIST{'0041-1-6327248', '0041-1-7337947'}",
"SET{ROW(13, 'ETHWorld', LIST{ROW(453, 10)}),
ROW(70, 'XYZ', LIST{ROW(959, 50), ROW(911, 100)})}");
UPDATE Kunde
SET Anschrift= ROW('Kreuzstrasse', 21, 8008, 'Zurich', 'CH')::AdresseTyp
WHERE Name.Vorname = 'Billy';
DELETE FROM Kunde
WHERE Anschrift.Ort = 'Zurich';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-140
Subtabellenbildung – Aufbau von Tabellenhierarchien
z
Syntax:
–
–
–
–
z
CREATE TABLE Tabellenname OF TYPE BenannterTupeltyp
[(Integritätsbedingungsliste)]
UNDER Supertabelle
Typ der Subtabelle muss ein direkter Subtyp des Typs der Supertabelle sein
(Tiefe) Extension der Subtabelle muss immer eine Untermenge der (tiefen)
Extension der Supertabelle sein
„ Instanzen der Subtabelle sind auch Mitglieder der zugehörigen
Supertabellen
Subtabelle darf nur maximal eine direkte Supertabelle haben
„ Keine (direkte) Mehrfachspezialisierung möglich
Subtabelle kann neue Integritätsbedingungen hinzudefinieren
Beispiel:
CREATE TABLE BWKunde OF TYPE BWKundeTyp
(CHECK(Telefone IS NOT NULL))
UNDER Kunde;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-141
Anfragen an Tabellenhierarchien
SELECT * FROM Kunde;
liefert alle Attributwerte der Kunden
SELECT * FROM BWKunde;
Zugriff auf tiefe Extension einer
Supertabelle
Zugriff auf tiefe Extension einer Subtabelle
liefert alle Attributwerte der
"Besonders Wichtigen" Kunden
SELECT * FROM ONLY(Kunde);
liefert alle Attributwerte der
"Nicht Besonders Wichtigen" Kunden
Zugriff auf flache Extension einer
Supertabelle
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-142
Typisierte Sichten
z
Analog zu typsierten Tabellen sind typisierte Sichten definierbar
–
Subsichten nicht unterstützt
z
Syntax:
CREATE VIEW Sichtenname OF TYPE BenannterTupeltyp
AS Anfrageausdruck
[WITH CHECK OPTION]
z
Beispiel:
CREATE VIEW GuterKunde OF TYPE KundeTyp
AS (SELECT *
FROM Kunde
WHERE CARDINALITY(Auftraege) > 10);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-143
Informix – Datenmodell
SET
MULTISET
ROW
ROW
SET
Einstiegspunkte
Typisierte Tabelle:
Untypisierte Tabelle:
MULTISET
LIST
Basisdatentyp
Subtypbeziehung
MULTISET(ROW(...))
MULTISET(ROW(...))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Untermengenbeziehung
4-144
Informix – Datenmodellüberblick
Basistyp
Mengentyp
Multimengentyp
Listentyp
Tupeltyp
Attributtypen
Attributtypen
UNDER
Distincttyp
Benannter
Tupeltyp
UNDER
Tupeltabelle
(Multimenge von Tupeln
eines Tupeltyps)
OF
Typisierte Tabelle
(Multimenge von Tupeln
eines benannten Tupeltyps)
Sichtberechnung
basiert auf
Tupelsicht
(Multimenge von Tupeln
eines Tupeltyps)
OF
Typisierte Sicht
(Multimenge von Tupeln
eines benannten Tupeltyps)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-145
Definition einer Funktion – Beispiel
CREATE FUNCTION AnzahlBestellteArtikel(k VARCHAR(20))
RETURNS INTEGER;
DEFINE auftragsmenge SET(AuftragTyp NOT NULL);
DEFINE auftrag AuftragTyp;
DEFINE gesamt INTEGER;
DEFINE i INTEGER;
LET gesamt = 0;
SELECT (CASE WHEN Auftraege IS NULL THEN 1 ELSE 0 END) INTO i
FROM Kunde
WHERE Name.Vorname = k;
IF i <> 1 THEN
SELECT Auftraege INTO auftragsmenge FROM Kunde WHERE Name.Vorname = k;
FOREACH c FOR
SELECT (SELECT SUM(Anzahl) FROM TABLE(a.Positionen)) INTO i
FROM TABLE(auftragsmenge) a
WHERE a.Positionen IS NOT NULL
LET gesamt = gesamt + i;
END FOREACH;
END IF;
RETURN gesamt;
END FUNCTION;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-146
SFW-Block (1)
z
SELECT: Was darf in der Projektionsliste stehen?
–
–
–
z
Attribute (auch abgeleitete, berechnete)
Funktionsaufrufe
Unterabfragen
9
9
9
Skalare Unterabfrage, die
genau einen Wert liefert
Beispiel:
SELECT Name.Vorname, AnzahlBestellteArtikel(k),
(SELECT COUNT(*)
FROM TABLE(Telefone) (nummer)
WHERE nummer LIKE '0041%'),
(MULTISET(SELECT Anr
FROM TABLE(Auftraege))
FROM Kunde k;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
Unterabfragen, die mehrere
Werte liefern, müssen mit
MULTISET umhüllt werden
4-147
SFW-Block (2)
z
FROM: Welche Tabellenausdrücke sind erlaubt?
–
–
–
–
–
z
(Untypisierte) Tabellen
Typisierte Tabellen (auch flache Extensionen)
Kollektionsabgeleitete Tabellen
Funktionsaufrufe (die Tabellen liefern)
Unterabfragen (abgeleitete Tabellen)
9
9
9
—
9
Beispiel: Kollektionsabgeleitete bzw. mittels Unterabfragen abgeleitete Tabellen
SELECT *
FROM TABLE((SELECT Auftraege FROM Kunde WHERE Name.Vorname = 'Billy'));
SELECT *
FROM TABLE(MULTISET(SELECT Auftraege FROM Kunde));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-148
SFW-Block (3)
z
WHERE: Welche Prädikate sind erlaubt?
–
–
–
z
Prädikate über Attribute
Prädikate mit Funktionsaufrufen
Prädikate mit Unterabfragen
9
9
9
Beispiel:
SELECT *
FROM Kunde
WHERE Name.Vorname LIKE 'C%' AND
AnzahlBestellteArtikel(Name.Vorname) > 5 AND
Telefone IS NOT NULL AND
EXISTS(SELECT *
FROM TABLE(Telefone) (nummer)
WHERE nummer LIKE '0041%');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-149
Objektrelationale Erweiterungen in Postgres
z
Neue Basisdatentypen
z
Domänen
z
Arraytypkonstruktor
z
Benannte Tupeltypen
z
Subtabellenbildung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-150
Neue Basisdatentypen
z
BOOLEAN
–
z
Large Objects
–
–
–
–
z
Werte: 'true', 't', '1', 'yes', 'y', 'false', 'f', '0', 'no', 'n', NULL
TYPEA (Binary Large Object)
TEXT (Character Large Object)
Operationen
„ FILETOBLOB, FILETOCLOB, LOTOFILE, LOCOPY
„ = (bitweise Gleichheit), IS [NOT] NULL
Nicht erlaubt
„ BLOB/CLOB-Attribute als Teil eines Schlüssels oder in booleschen bzw.
arithmetischen Ausdrücken
„ GROUP BY, ORDER BY
Geometrische Datentypen
–
POINT, PATH, BOX, CIRCLE, POLYGON
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-151
Basisdatentypen
Basisdatentyp
Bedeutung
SMALLINT
(16 Bit) ganze Zahl [-32768, 32767]
INTEGER
(32 Bit) ganze Zahl [-2147483648, 2147483647]
BIGINT
(64 Bit) ganze Zahl [-9223372036854775808, 9223372036854775807]
DECIMAL(p,q)
NUMERIC(p,q)
Festkommazahl mit Genauigkeit p und q Nachkommastellen (p ≤ 32)
REAL
DOUBLE PRECISION
Fließkommazahl mit Genauigkeit p (≤ 16)
CHAR(q)
Alphanumerische Zeichenkette mit fester Länge q (≤ 254)
VARCHAR(q)
Alphanumerische Zeichenkette mit variabler Länge q (≤ 254)
TEXT
Alphanumerische Zeichenkette mit variabler Länge bis 1GB
BIT(q)
Bitfolge mit fester Länge q
BIT VARYING(q)
Bitfolge mit variabler Länge bis zur Maximallänge q
BYTEA
Binäre Zeichenkette mit variabler Länge bis 2GB
OID
externer LOB-Datentyp
DATE
Datum
DATETIME
Zeit
TIMESTAMP
Zeitstempel
INTERVAL
Zeitinterval
Postgres bietet ausserdem diverse geometrische Datentypen wie Box, Circle, ….
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-152
Domänen
z
Syntax:
CREATE DOMAIN Name [AS] Datentyp
[DEFAULT Wert]
[[CONSTRAINT Name] [NOT] NULL]
z
Unterschied zum Standard:
–
nur NOT NULL möglich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-153
Arraytypkonstruktor
z
Definition eines (multidimensionalen) Arraytyps:
<Elementtyp> [<Max-Kardinalität>] ... [<Max-Kardinalität>]
z
Beispiel:
CREATE TABLE MitarbeiterTupeltabelle (
Name
VARCHAR(30),
Gehalt
DECIMAL(12,2),
Telefone
VARCHAR(20) [3],
Hobbies
VARCHAR(15) []
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-154
Operationen auf Arraytypen (1)
Erzeugen eines Arrays mit dem Arraykonstruktor (heisst genauso wie der
Arraytypkonstruktor):
'{'Deutsch', 'Englisch'}'
Direkter Zugriff auf das
i-te Arrayfeld mittels [i]:
SELECT Sprachkenntnisse[2]
FROM MitarbeiterTupeltabelle;
Direkter Zugriff auf ein
Subarray mittels [i:j]:
SELECT Sprachkenntnisse[2:3]
FROM MitarbeiterTupeltabelle;
Elementiterator:
SELECT Name
FROM MitarbeiterTupeltabelle
WHERE Sprachkenntnisse *= 'Deutsch';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-155
Operationen auf Arraytypen (2)
Änderung eines
einzelnen Arrayelements:
UPDATE MitarbeiterTupeltabelle
SET
Sprachkenntnisse[1] = 'Türkisch';
Änderung des
kompletten Arraywerts:
UPDATE MitarbeiterTupeltabelle
SET
Sprachkenntnisse = '{'Türkisch'}';
Kardinalität liefert Anzahl
der Arrayelemente:
ARRAY_DIMS('{'Deutsch', Türkisch'})
-- liefert den Wert 2
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-156
Benannte Tupeltypen
z
Wiederverwendbare Tupeltypen
–
–
ähnlich zu Informix
aktuell nur beschränkt nutzbar (als Rückgabetyp einer tabellenwertigen
Funktion)
z
Syntax:
z
Attributdefinition: Attributname Typ [NOT NULL]
z
Beispiel: CREATE TYPE NameGehaltTyp AS (
CREATE TYPE Typname (Attributdefinitionsliste)
Name VARCHAR(30),
Gehalt DECIMAL(12,2)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-157
Benutzerdefinierte Cast-Funktionen
z
Konversion zwischen unterschiedlichen Datentypen
z
Syntax:
–
–
–
CREATE CAST (Quelltyp AS Zieltyp)
WITH FUNCTION Funktionsname (Quelltyp)
[AS ASSIGNMENT | AS IMPLICIT]
Konvertierungsfunktion hat genau einen Parameter vom Typ Quelltyp sowie
Zieltyp als Rückgabetyp
AS ASSIGNMENT: Cast-Funktion, die implizit bei Zuweisungen aufgerufen
wird
AS IMPLICIT: Cast-Funktion, die implizit aufgerufen wird
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-158
Explizite benutzerdefinierte Cast-Funktionen - Beispiel
Definition einer Konvertierungsfunktion:
CREATE FUNCTION EuroToFranken(e Euro)
RETURNS Franken
RETURN CAST((e::DECIMAL * 1.5) AS Franken);
END FUNCTION;
Definition einer expliziten Cast-Funktion:
CREATE CAST (Euro AS Franken WITH EuroToFranken);
Vorherige Anfrage nun mit direkter Konvertierung von Euro nach Franken:
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand > CAST(e.Stand AS Franken);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-159
Implizite benutzerdefinierte Cast-Funktionen - Beispiel
Definition einer Konvertierungsfunktion:
CREATE FUNCTION FrankenToEuro(f Franken)
RETURNS Euro
RETURN CAST((CAST(f AS DECIMAL) * 0.65) AS Euro);
END FUNCTION;
Definition einer impliziten Cast-Funktion:
CREATE IMPLICIT CAST (Franken AS Euro WITH FrankenToEuro);
Vorherige Anfrage nun mit impliziter Konvertierung von Franken nach Euro:
SELECT c.Nr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand > e.Stand;
Achtung: Falls implizite Cast-Funktionen
in beiden Richtungen vorhanden sind,
wird automatisch nur der rechte Ausdruck
des Vergleichsprädikats konvertiert!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-160
Subtabellenbildung – Aufbau von Tabellenhierarchien
z
Syntax:
–
–
z
CREATE TABLE Tabellenname
( Tabellenelementdefinitionsliste)
INHERITS Supertabellennamensliste
Hierarchie basiert auf Tupeltabellen
„ keine Trennung zwischen Typ- und Tabellenhierarchie
Mehrfachspezialisierung: Subtabelle kann mehrere Supertabellen haben
„ Subtabelle erbt Elemente (Spalten und Integritätsbedingungen) von den
Supertabellen
„ nicht erlaubt bei Namenskonflikten, d.h., wenn zwei geerbte Attribute
denselben Namen tragen
„ Subtabelle kann neue Integritätsbedingungen hinzudefinieren
Beispiel:
CREATE TABLE ManagerTupeltabelle (
Bonus DECIMAL(12,2)
)
INHERITS MitarbeiterTupeltabelle;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-161
Anfragen an Tabellenhierarchien
SELECT * FROM MitarbeiterTupeltabelle;
liefert alle Attributwerte der Mitarbeiter
SELECT * FROM ManagerTupeltabelle;
liefert alle Attributwerte der Manager
SELECT * FROM ONLY MitarbeiterTupeltabelle;
liefert alle Attributwerte der
Mitarbeiter, die keine Manager sind
Zugriff auf tiefe Extension einer
Supertabelle
Zugriff auf tiefe Extension einer
Subtabelle
Zugriff auf flache Extension einer
Supertabelle
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-162
Definition einer Funktion
Beispiel: $1 dient als Platzhalter für den ersten Parameter der Funktion:
CREATE FUNCTION Muttersprache (INTEGER)
RETURNS VARCHAR;
AS 'SELECT Sprachkenntnisse[1] FROM Mitarbeiter WHERE Nr = $1'
LANGUAGE SQL;
Beispiel für eine tabellenwertige Funktion:
CREATE FUNCTION ReicheMitarbeiter (DECIMAL)
RETURNS SETOF NameGehaltTyp
AS 'SELECT Name, Gehalt FROM Mitarbeiter WHERE Gehalt = $1'
LANGUAGE SQL;
Der benannte Tupeltyp muss dem Zeilentyp der zurückgelieferten Tabelle
entsprechen!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-163
Definition von Triggern
CREATE TRIGGER Name
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OR {INSERT | DELETE | UPDATE ...}]
ON Tabellenname
FOR EACH ROW
EXECUTE PROCEDURE Funktionsname(Parameterliste)
Unterschied zum SQL-Standard
z
Trigger durch verschiedene Ereignisse aktivierbar
z
Nur Zeilentrigger
z
keine Transitionsvariablen und -tabellen
z
Triggerrumpf ist ein Prozeduraufruf
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-164
Postgres – Datenmodell
MULTISET
ROW
ARRAY
Einstiegspunkt
Untypisierte Tabelle:
Basisdatentyp
Untermengenbeziehung
MULTISET(ROW(...))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-165
Postgres – Datenmodellüberblick
Basistyp
UNDER
Arraytyp
Attributtypen
Tupeltabelle
(Multimenge von Tupeln
eines Tupeltyps)
Tupelsicht
(Multimenge von Tupeln
eines Tupeltyps)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-166
Postgres – SFW-Block
z
SELECT: Was darf in der Projektionsliste stehen?
–
–
–
z
9
9
9
Skalare Unterabfrage, die
genau einen Wert liefert
FROM: Welche Tabellenausdrücke sind erlaubt?
–
–
–
–
–
z
Attribute (auch abgeleitete, berechnete)
Funktionsaufrufe
Unterabfragen
(Untypisierte) Tabellen
Flache Extensionen
Kollektionsabgeleitete Tabellen
Funktionsaufrufe (die Tabellen liefern)
Unterabfragen (abgeleitete Tabellen)
9
9
—
—
9
WHERE: Welche Prädikate sind erlaubt?
–
–
–
Prädikate über Attribute
Prädikate mit Funktionsaufrufen
Prädikate mit Unterabfragen
9
9
9
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-167
Vergleich der objektrelationalen Modelle und Resümee
z
Neue Basistypen
z
Typkonstruktoren
z
Benutzerdefinierte Typen
z
Objekttabellen
z
Objektsichten
z
SQL-Spracherweiterungen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-168
Vergleich – Neue Basisdatentypen
Typ
SQL:1999 Oracle DB2 Informix Postgres
BOOLEAN
9
—
—
9
9
CLOB
9
9
9
9
9
BLOB
9
9
9
9
9
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-169
Vergleich – Typkonstruktoren
Typen
Benannte
SQL:1999 Oracle DB2 Informix Postgres
DISTINCT
9
—
9
9
—
OBJECT (strukturiert)
9
9
9
—
—
ROW
—
—
—
9
9
VARRAY
—
9
—
—
—
TABLE
—
9
—
—
—
OPAQUE
—
—
—
9
9
9
—
—
9
—
—
—
—
9
—
SQL:2003
—
—
9
—
LIST
—
—
—
9
—
ARRAY
9
—
—
—
9
REF
9
9
9
—
—
Unbenannte ROW
SET
MULTISET
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-170
Vergleich – Typ- und Tabellenhierarchien
SQL:1999 Oracle DB2 Informix Postgres
Benannte Tupeltypen
—
—
—
9
9
Benannte Objekttypen
9
9
9
—
—
Typhierarchien
9
9
9
9
—
Typisierte Tupeltabellen
—
—
—
9
—
Typisierte Objekttabellen
9
9
9
—
—
Tabellenhierarchien
9
—
9
9
9
Typisierte Tupelsichten
—
—
—
9
—
Typisierte Objektsichten
9
9
9
—
—
Sichtenhierarchien
9
9
9
—
—
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-171
Vergleich – Allgemeine und weitere Konzepte
SQL:1999 Oracle DB2 Informix Postgres
Objektkonzept mit OIDs & Methoden
9
9
9
—
—
Benutzerdefinierte Objektgleichheit
(Abbildungs-/Ordnungsfunktionen)
9
9
—
—
—
Benutzerdefinierte Cast-Funktionen
(Explizit/Implizit)
9
—
—
9
9
Benutzerdefinierte Prozeduren &
Funktionen
9
9
9
9
9
Rekursion
9
9
9
—
—
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-172
Vergleich – Projektionsliste - SELECT
SQL:1999 Oracle DB2 Informix Postgres
Attribute (Attributausdrücke)
9
9
9
9
9
Methoden-/Funktionsaufrufe
9
9
9
9
9
Einwertige Unterabfragen
9
9
9
9
9
Mehrwertige Unterabfragen
—
9
—
9
—
Oracle & Informix: Unteranfragen, die mehr als einen (Tupel-)Wert liefern,
müssen explizit mit MULTISET umhüllt werden
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-173
Vergleich – Tabellenausdrücke - FROM
SQL:1999 Oracle DB2 Informix Postgres
Tupeltabellen (tiefe Extension)
9
9
9
9
9
Tupeltabellen (flache Extension)
—
—
—
—
9
Typisierte Tabellen (tiefe Extension)
9
9
9
9
—
Typisierte Tabellen (flache Extension)
9
—
9
9
—
Kollektionsabgeleitete Tabellen
9
9
—
9
—
Funktionsaufrufe (die Tabellen liefern)
—
—
9
—
—
Unterabfragen (abgeleitete Tabellen)
9
9
9
9
9
Umwandlung einer Kollektion in eine Tabelle
SQL:1999: UNNEST vs. ORACLE & Informix: TABLE
Rekursive Tabellenausdrücke in SQL:1999 & DB2 unterstützt!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-174
Vergleich – Prädikate - WHERE
SQL:1999 Oracle DB2 Informix Postgres
Prädikate über Attribute
9
9
9
9
9
Prädikate mit Funktionsaufrufen
9
9
9
9
9
Prädikate mit Methodensaufrufen
9
9
9
—
—
Prädikate mit Unterabfragen
9
9
9
9
9
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-175
Vergleich – Deklarative Integritätsbedingungen
SQL:1999 Oracle DB2 Informix Postgres
NOT NULL, DEFAULT, UNIQUE, PRIMARY KEY
FOREIGN KEY MATCH
SIMPLE
PARTIAL
FULL
ON DELETE
NO ACTION
RESTRICT
CASCADE
SET NULL
SET DEFAULT
ON UPDATE
NO ACTION
RESTRICT
CASCADE
SET NULL
SET DEFAULT
CHECK
Attribut-/Tupel/Objektebene
Tabellen-/Datenbankebene
DOMAIN
ASSERTION
9
9
9
9
9
9
9
9
9
9
9
9
9
9
9
9
9
9
9
(9)
(9)
9
(9)
9
(9)
9
9
9
9
(9)
(9)
9
9
(9)
9
9
9
9
9
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
9
9
(9)
9
9
9
9
9
9
9
9
9
9
9
9
9
4-176
Vergleich – NOT NULL und OR-Erweiterungen
NOT NULL auf
z
Oracle DB2 Informix Postgres
objektwertigen Attributen
9
9
(9)
•
Referenzattributen
Referenzattributen
9
9
•
•
kollektionswertigen Attributen
—
•
(9)
9
Beispiel
CREATE TYPE NameTyp (Vorname VARCHAR(20), Nachname VARCHAR(25));
CREATE TABLE Person (Name NameTyp NOT NULL, ...);
INSERT INTO Person(Name) VALUES (NULL);
INSERT INTO Person(Name) VALUES (NameTyp(NULL, NULL));
CHECK((Name.Vorname IS NOT NULL) AND (Name.Nachname IS NOT NULL));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-177
Vergleich – Semantik von Eindeutigkeitsbedingungen
DB2 & Informix: Schlüsselattributwerte müssen eindeutig sein
Oracle: Schlüsselattributwerte müssen eindeutig sein
(wenn sie nicht komplett aus Nullwerten bestehen)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-178
Vergleich – Eindeutigkeitsbedingungen
z
Was passiert, wenn mehrere Eindeutigkeitsbedingungen auf gleichen
Attributen definiert werden?
–
–
z
DB2 weist die Definition einer Eindeutigkeitsbedingung zurück, wenn auf
derselben Tabelle bereits eine andere Eindeutigkeitsbedingung mit denselben
Attributen existiert
Oracle & Informix weist die Definition einer Eindeutigkeitsbedingung nur
dann zurück, wenn zusätzlich die Reihenfolge der Attribute übereinstimmt
Was geben die Systeme aus, wenn mehrere Eindeutigkeitsbedingungen
gleichzeitig verletzt sind?
–
–
Oracle gibt den Namen der ältesten Eindeutigkeitsbedingung aus
DB2 & Informix sagen nur, dass eine Eindeutigkeitsbedingung verletzt ist
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-179
Vergleich – Eindeutigkeitsbedingungen und ORErweiterungen
UNIQUE auf
Oracle DB2 Informix Postgres
objektwertigen Attributen
(9)
—
•
•
Referenzattributen
—
9
•
•
kollektionswertigen
kollektionswertigen Attributen
—
•
—
—
z
Oracle unterstützt Eindeutigkeitsbedingungen auf Komponenten eines
objektwertigen Attributs, wenn der Typ der Komponente ein Basistyp ist
z
Vererbung von Eindeutigkeitsbedingungen
(Primärschlüsselbedingungen)
–
–
DB2 verbietet explizite Eindeutigkeitsbedingungen für Subtabellen
Informix: Jede Tabelle darf maximal eine explizite Primärschlüsselbedingung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-180
Vergleich – Fremdschlüssel & Referenzattribute
z
DB2: Wenn eine Tabelle mehrere Fremdschlüssel enthält, müssen alle
Fremdschlüssel die gleiche DELETE-Regel befolgen
–
–
z
In diesem die DELETE-Regel des ersten Fremdschlüssels
Überraschenderweise sind mehrere Fremdschlüssel nicht erlaubt, wenn die
erste DELETE-Regel SET NULL ist
Integritätsbedingungen auf Referenzattributen
Attributbasiert:
Tabellenbasiert:
–
ref_column REF ref_type SCOPE IS typed_table
SCOPE FOR ref_column IS typed_table
Oracle unterstützt beide Formen, DB2 nur attributbasiert
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-181
Vergleich – Trigger
z
Syntax und Semantik kommerzieller Trigger sind sehr unterschiedlich
–
Grund: Trigger wurden erst mit SQL:1999 standardisiert, obwohl Trigger seit
über zehn Jahren von kommerziellen Systemen angeboten werden
SQL:1999 Oracle DB2 Informix Postgres
#Ereignisse pro Trigger
1
*
1
1
*
#Trigger pro Ereignis
*
*
*
1
1
Granularität (ROW/STATEMENT)
R/S
R/S
R/S
R/S
R/S
Aktivierungszeit (BEFORE/AFTER)
B/A
B/A
B/A
B/A
B/A
9
9
9
9
—
Aktivierungsbedingung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-182
Vergleich – Evolution von IB
ADD
ENABLE
VALIDATE
VALIDATE + EXC
NOVALIDATE
DISABLE
DROP
RESTRICT
CASCADE
ENABLE VALIDATE
VALIDATE + EXCEPTION
NOVALIDATE
DISABLE RESTRICT
CASCADE
SET
INITIALLY IMMEDIATE
DEFERRED
DEFERRABLE
NOT DEFERRABLE
SQL:1999 Oracle DB2 Informix Postgres
(9)
9
(9)
9
(9)
9
9
9
9
9
9
9
(9)
(9)
9
9
(9)
9
9
(9)
9
9
9
9
(9)
(9)
9
9
9
(9)
(9)
9
9
9
9
9
9
(9)
(9)
9
9
9
9
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-183
Vergleich – Hinzufügen neuer IB
z
ADD+ENABLE+VALIDATE+EXCEPTION
Oracle:
Informix:
z
ADD+DISABLE
Oracle:
Informix:
z
ALTER TABLE t ADD cons-spec EXCEPTIONS INTO e;
ALTER TABLE t ADD cons-spec FILTERING;
ALTER TABLE t ADD cons-spec DISABLE;
ALTER TABLE t ADD cons-spec DISABLED;
ADD+ENABLE+NOVALIDATE
Oracle:
ALTER TABLE t ADD cons-spec NOVALIDATE;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-184
Vergleich – Aktivierung & Deaktivierung von IB
z
ENABLE+VALIDATE
Oracle:
Informix:
z
ENABLE+NOVALIDATE
Oracle:
z
ALTER TABLE t ENABLE NOVALIDATE cons;
DISABLE
Oracle:
Informix:
z
ALTER TABLE t ENABLE cons;
SET CONSTRAINTS cons-name ENABLED;
ALTER TABLE t DISABLE cons;
SET CONSTRAINTS cons-name DISABLED;
SQL:1999 & DB2 bieten hier keine Unterstützung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-185
Vergleich – Metasichten für IB & Trigger
z
z
Enthalten Informationen über definierte IB & Trigger
SQL:1999
Oracle
DB2
Informix
table_constraints
check_constraint
referential_constraints
key_columns_usage
triggers
xxx_constraints
xxx_constraints
xxx_constraints
xxx_cons_columns
xxx_triggers
syscat.tabconst
syscat.checks
syscat.references
syscat.keycoluse
syscat.triggers
sysconstraints
syschecks
sysreferences
syscoldepend
systriggers
Oracle & DB2:
–
z
Namen müssen in Anfragen in grossen Buchstaben geschrieben sein (auch
wenn bei der Definition kleine Buchstaben verwendet wurden)
Informix: Namen immer in Kleinbuchstaben
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-186
Resümee
z
Objektrelationale Modellerweiterungen können sowohl
Schemadefinition als auch Anfrageformulierung erleichtern (aber auch
erschweren)
–
–
z
Syntax und Semantik der in kommerziellen DBMS implementierten
objektrelationalen Konzepte variieren teilweise
–
z
Intuitivere Anfrageformulierung
Effizientere Anfrageauswertung (Pfadausdrücke vs. Joins)
Datenmodellheterogenität erschwert Verständlichkeit und Portabilität
Unterschiede besonders sichtbar bei
–
–
–
–
Typkonstruktoren und benutzerdefinierten Datentypen
OIDs und Referenzen
Mengenwertigen Attributen und geschachtelten Tabellen
Typ- und Tabellenhierarchien
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-187
Resümee (Fortsetzung)
z
Weder SQL:1999 noch kommerzielle DBMS definieren ein "sauberes"
objektrelationales Datenmodell bzw. eine "saubere" Datenbanksprache
–
–
–
–
–
z
Fehlende Orthogonalität der Typkonstruktoren
Implizite Typumwandlungen
Unterschiedliche Anwendung einiger Typkonstruktoren innerhalb von
geschachtelten Anfragen (Informix)
Benutzergenerierte Referenzen - OIDs (DB2)
Objektrelationale Spracherweiterungen von Standard-SQL sowie kommerzieller
SQL-Dialekte sind derzeit zum Teil wenig intuitiv, das u.a. durch die fehlende
Orthogonalität der Sprachkonstrukte begründet wird
Forderung nach Aufwärtskompatibilität steht einem durchgängigen,
sauberen Sprachentwurf entgegen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. C. Türker)
4-188