SELECT - Die DBS

Kapitel 3
SQL:1999 – Der objektrelationale
Datenbankstandard
Rückblick SQL-92
Übersicht SQL:1999
Trigger
Benutzerdefinierte Datentypen
Typisierte Tabellen
Typ- und Tabellenhierarchien
Ausblick SQL:2003
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. Can Türker)
3-2
SQL-Standard – Allgemeine Informationen
SQL:1999 ist in mehreren Parts definiert:
z
z
z
z
z
z
z
z
Part 1: Framework, 85 Seiten, September 1999
Part 2: Foundation, 1147 Seiten, September 1999
Part 3: Call Level Interface (SQL/CLI), 421 Seiten, September 1999
Part 4: Persistent Stored Modules (SQL/PSM), 170 Seiten, September 1999
Part 5: Host Language Bindings (SQL/Bindings), 261 Seiten, September 1999
Part 9: Management of External Data (SQL/MED), 500 Seiten, Juli 2001
Part 10: Object Language Bindings (SQL/OLB), 238 Seiten, Februar 2000
Part 13: SQL Routines and Types Using the Java Programming Language (SQL/JRT), 206 Seiten,
Juli 2002
Separater Standard: SQL Multimedia and Application Packages (SQL/MM)
z
z
z
Part 2: Full Text (208 Seiten; Mai 2000)
Part 3: Spatial (343 Seiten; Mai 2000)
Part 5: Still-Image (170 Seiten; Mai 2001)
SQL:2003 - Drafts:
z
z
Part 11: Information and Definition Schemas (SQL/Schemata), 298 Seiten, August 2002
Part 14: XML-related Specifications (SQL/XML), 150 Seiten, August 2002
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-3
Basiskonstrukte von SQL-92
z
Tupeltabellen
–
–
–
–
z
Basisdatentypen
–
z
INTEGER, SMALLINT, NUMERIC, DECIMAL,REAL,
FLOAT, CHARACTER, DATE, TIME, BIT, ...
MULTISET
ROW
Basisdatentyp
Integritätsbedingungen
–
–
z
Basistabellen zur Speicherung von Daten
Sichten (Views): abgeleitete Tabellen
Typ eines Attributs ist ein Basisdatentyp (1NF)
Zeilen (Tupel) setzen sich aus Instanzen der
jeweiligen Wertebereiche zusammen
Primär-/Fremdschlüssel, Check-Klauseln
Assertions: Bedingungen über mehrere Tabellen
Zugriffsrechte (Grants)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-4
Basisdatentypen
Basisdatentyp
Bedeutung
Beispielwerte
SMALLINT
INTEGER
ganze Zahl
1704, 4070
DECIMAL(p,q)
NUMERIC(p,q)
Festkommazahl mit Genauigkeit p
und q Nachkommastellen
1003.44, 160139.9
FLOAT(p)
REAL
DOUBLE PRECISION
Fließkommazahl mit Genauigkeit p
1.5E-4, 10E20
CHAR(q)
VARCHAR(q)
Zeichenkette mit fester bzw. variabler
Länge bis zur Maximallänge q
’Das ist eine Zeichenkette’, ’SQL:1999’
BIT(q)
BIT VARYING(q)
Bitfolge mit fester bzw. variabler
Länge bis zur Maximallänge q
B’01101’, X’9E’
DATE
Datum
DATE ’1997-06-19’, DATE ’2001-08-23’
TIME
Zeit
TIME ’20:30:45’, TIME ’00:15:30’
TIMESTAMP
Zeitstempel
TIMESTAMP ’2002-08-23 14:15:00’
INTERVAL
Zeitintervall
INTERVAL ’11:15’ HOUR TO MINUTE
XML
XML-Wert
<Titel>SQL:2003 und SQL/XML</Titel>
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-5
Tupeltabellen
z
Eine Tupeltabelle ist eine traditionelle Tabelle im Sinne von SQL-92
z
Eine solche Tabelle besteht aus einer (Multi-)Menge von Tupeln
Tabellenname
Spalten (Columns) = Attribute
R
A1
...
An
...
...
Zeilen (Rows) = Tupel
...
Attributwerte
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-6
Definition von Tupeltabellen
z
Syntax:
z
Tabellenelement:
z
CREATE TABLE Tabellenname (Tabellenelementliste)
Attributdefinition
| Tabellenbedingung
–
Attributdefinition beinhaltet Festlegung des Datentyps und eventuell die
Angabe eines Defaultwerts sowie von Attibutbedingungen
–
Definition von Tabellenbedingungen
Integritätsbedingungen (IB)
–
–
–
–
–
–
–
NOT NULL
DEFAULT
CHECK (Boolesches Prädikat)
UNIQUE/PRIMARY KEY (Eindeutigkeits-/Schlüsselbedingungen)
FOREIGN KEY ... REFERENCES (Fremdschlüsselbedingungen)
DOMAIN
ASSERTION
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-7
Beispiele für Domänen, Tupeltabellen mit IB, Assertions
CREATE DOMAIN Jobs CHAR(10)
DEFAULT 'Verkaeufer' CHECK(VALUE IN ('Verkaeufer', 'Manager', 'Buchhalter'));
CREATE TABLE Mitarbeiter (
MNr
SMALLINT PRIMARY KEY,
Nachname VARCHAR(25) NOT NULL,
Vorname
VARCHAR(20) NOT NULL,
Job
Jobs,
Vorgesetzter SMALLINT REFERENCES Mitarbeiter(MNr),
Anstellung DATE DEFAULT CURRENT_DATE,
Gehalt
DECIMAL(7,2) NOT NULL CHECK(Gehalt > 2000),
Bonus
DECIMAL(7,2) NOT NULL CHECK(Bonus < 5000),
CONSTRAINT EindeutigeNamenskombination UNIQUE(Nachname, Vorname),
CONSTRAINT CheckGesamtgehalt CHECK(Gehalt + Bonus < 25000));
CREATE ASSERTION MaxJahresgehlt
CHECK((SELECT 12*SUM(Gehalt + Bonus) FROM Mitarbeiter) < 5000000);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-8
Lesestoff
Komponenten deklarativer Integritätsbedingungen
z
Name (DBMS generiert einen falls keiner explizit angegeben wurde)
–
z
Verzögerungsmodus
–
z
DEFERRABLE / NOT DEFERRABLE
(Initialer) Überprüfungsmodus
–
z
Notwendig für Debugging und Administration (Löschen und (De-)Aktivieren
von IB)
IMMEDIATE / DEFERRED
IMMEDIATE DEFERRED
DEFERRABLE
9
9
NOT DEFERRABLE
9
–
Überprüfungsmodus ändern
SET CONSTRAINTS constraint-name DEFERRED;
SET CONSTRAINTS ALL IMMEDIATE;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-9
Lesestoff
Semantik von Eindeutigkeitsbedingungen
UNIQUE(u1, ..., un) gilt für Tabelle R
PRIMARY KEY(u1, ..., un) gilt für Tabelle R
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-10
Lesestoff
Fremdschlüsselsemantiken (1)
z
R(f1, ..., fn) REFERENCES S(u1, ..., un)
SIMPLE MATCH
PARTIAL MATCH
FULL MATCH
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-11
Lesestoff
Fremdschlüsselsemantiken (2)
z
Allgemein gilt:
FULL = SIMPLE + CHECK((f1 IS NULL AND ... AND fn IS NULL) OR
(f1 IS NOT NULL AND ... AND fn IS NOT NULL))
z
SIMPLE, PARTIAL und FULL MATCH sind äquivalent, wenn gilt:
–
–
Fremdschlüssel ist auf nur einem Attribut definiert und/oder
Alle referenzierenden und/oder referenzierten Attribute sind NOT NULL
z
Referenzierte Eindeutigkeitsbedingung muss NON-DEFERRABLE sein
z
Referenzierte Tabelle muss eine Basistabelle sein
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-12
Fremdschlüssel und referenzielle Aktionen
z
Fremdschlüsselbedingung verletzbar durch
–
–
z
INSERT oder UPDATE auf der referenzierenden Tabelle
DELETE oder UPDATE auf der referenzierten Tabelle
Mögliche referentielle Reaktionen: ON DELETE/UPDATE ...
–
–
–
–
–
NO ACTION: verbietet DELETE/UPDATE eines referenzierten Tupels (Objekts)
RESTRICT: analog zu NO ACTION; wird aber "sofort" überprüft
CASCADE: automatisches DELETE/UPDATE der referenzierenden Tupel (Objekte)
SET NULL: setzt korrespondierende Fremdschlüssel auf NULL
SET DEFAULT: setzt korrespondierende Fremdschlüssel auf DEFAULT
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-13
Probleme mit referenziellen Aktionen
CREATE TABLE dept (dept_id INT PRIMARY KEY);
Effekt einer Löschung bzw.
Änderung hängt von der
Überprüfungsreihenfolge ab
CREATE TABLE proj (
proj_id INT,
dept_id INT,
PRIMARY KEY(proj_id,dept_id),
FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE emp (
emp_id INT PRIMARY KEY,
dept_id INT NOT NULL,
proj_id INT NOT NULL,
FOREIGN KEY (proj_id, dept_id)
REFERENCES proj (proj_id, dept_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
ON DELETE CASCADE ON UPDATE CASCADE);
DELETE FROM dept
WHERE dept_id = 1311
dept dept_id
1311
proj proj_id dept_id
1704
1311
emp emp_id proj_id dept_id
5643
1704
1311
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-14
Erweiterungen in SQL:1999 & SQL:2003
z
Trigger
z
Neue Basisdatentypen: BOOLEAN, BLOB, CLOB, BIGINT
z
Neue Typkonstruktoren: ROW, ARRAY, REF, MULTISET
z
Benutzerdefinierte Datentypen (Distinct-Typ und strukturierte Typen)
z
Typhierarchien (Subtypen)
z
Typisierte Tabellen und Tabellenhierarchien (Subtabellen)
z
Typisierte Sichten und Sichthierarchien (Subsichten)
z
Rekursion
z
Generierte Spalten, Sequenzgeneratoren, Identitätsspalten
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-15
Trigger
z
Konzept, um automatisch auf vordefinierte Ereignisse in der Datenbank
zu reagieren
z
Neu im Standard, obwohl kommerzielle Systeme schon lange Trigger
unterstützen (Sybase führte Trigger bereits 1987 ein)
z
Trigger, sind auch für die Wahrung von Integritätsbedingungen
einsetzbar
–
–
Integritätsbedingungen innerhalb eines Triggers überprüft
Probleme:
„ Zusammenspiel der Trigger (Terminierung und Konfluenz)
„ Zusammenspiel zwischen Trigger und deklarativen IB (Determinismus)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-16
Triggerkonzept am Beispiel
INSERT INTO Mitarbeiter
VALUES (Ken, 1000, ...)
DBMS
Mitarbeiter
Name
...
...
Ken
Gehalt
...
...
1000
...
...
...
...
CREATE TRIGGER trig1
AFTER INSERT ON Mitarbeiter
REFERENCING NEW AS NM
FOR EACH ROW
INSERT INTO MitarbBackup
VALUES (NM.Name, NM.Gehalt)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
MitarbBackup
Name
...
...
Ken
Gehalt
...
...
1000
3-17
Trigger – Beispieldefinition
Folgender Trigger implementiert die ON UPDATE CASCADE Aktion
(Kommerzielle Datenbanksysteme unterstützen diese referentielle Aktion nicht):
CREATE TRIGGER OnUpdateCascadeMitarbeiter
AFTER UPDATE OF ID ON MitarbeiterTupeltabelle
REFERENCING OLD AS AltMit NEW AS NeuMit
FOR EACH ROW
WHEN(EXISTS(SELECT * FROM Dept WHERE Manager= AltMit.ID))
BEGIN ATOMIC
UPDATE Dept
SET Manager=NeuMit.ID
WHERE Manager=AltMit.ID;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-18
Trigger – Komponenten
z
Triggerereignis:
–
z
Triggeraktivierungszeitpunkt:
–
z
FOR EACH ROW/STATEMENT
Triggerbedingung:
–
z
BEFORE/AFTER Triggerereignis
Triggergranularität:
–
z
INSERT/DELETE/UPDATE auf Basistabelle
SQL-Bedingung; Aktion nur dann ausgeführt, wenn die Bedingung erfüllt ist
Triggeraktion:
–
Sequenz von SQL-Anweisungen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-19
IB-Überprüfung and Triggerausführung
SQL-Anweisung
Bestimme Menge der betroffenen Tupel/Objekte
Führe BEFORE Trigger aus
Führe Änderungen der SQL-Anweisung aus
Überprüfe alle RESTRICT Fremdschlüssel
Überprüfe alle CASCADE, SET NULL, SET DEFAULT Fremdschlüssel
Überprüfe alle NO ACTION Fremdschlüssel and alle anderen IB
Führe AFTER Trigger aus
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-20
Effekt einer Integritätsverletzung
z
Transaktion:
–
z
Menge von SQL-Anweisungen, die eine Datenbank von einem konsistenten in
einen anderen konsistenten Zustand überführt
SQL:1999 unterstützt implizit Konsistenz auf Anweisungsebene
–
–
–
Führt eine SQL-Anweisung zu einer Verletzung einer IB, wird eine Ausnahme
signalisiert und die Änderungen der Anweisung rückgängig gemacht
Dies gilt auch für alle automatisch durch referentielle Aktionen oder Trigger
durchgeführte Änderungen
Rücksetzen der gesamten Transaktion muss explizit durch den
Benutzer/Anwendung mittels des Befehls ROLLBACK durchgeführt werden
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-21
Neue Basisdatentypen – BOOLEAN
z
Werte
–
–
–
z
TRUE
FALSE
UNKNOWN
Operationen
–
–
–
–
NOT
AND
OR
IS [NOT]
NOT
TRUE
FALSE
AND
TRUE
TRUE
TRUE
FALSE
FALSE
UNKNOWN UNKNOWN
OR
TRUE
FALSE
UNKNOWN
TRUE
TRUE
TRUE
TRUE
IS
TRUE
FALSE
UNKNOWN
TRUE
TRUE
FALSE
FALSE
FALSE
TRUE
UNKNOWN
UNKNOWN
FALSE
FALSE
FALSE
FALSE
UNKNOWN
UNKNOWN
FALSE
UNKNOWN
FALSE
UNKNOWN
TRUE
TRUE
FALSE
UNKNOWN
UNKNOWN UNKNOWN
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
FALSE
FALSE
TRUE
FALSE
UNKNOWN
FALSE
FALSE
TRUE
3-22
Neue Basisdatentypen – Large Objects
z
LOB-Typen
–
–
z
Operationen
–
–
z
BLOB (Binary Large Object)
CLOB (Character Large Object)
SUBSTRING, OVERLAY, || (Konkatenation)
IS [NOT] NULL, LIKE, EQUALS, POSITION, LENGTH, SIMILAR
Nicht erlaubt
–
–
BLOB/CLOB-Attribute als Teil eines Schlüssels
GROUP BY, ORDER BY, GREATER/LESS THAN
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-23
Neue Basisdatentypen – Beispiel
Definition einer Tabelle mit BOOLEAN- und LOB-Attributen:
CREATE TABLE MitarbeiterTupelTabelle (
Name
VARCHAR(30),
Vollzeit
BOOLEAN,
Bild
BLOB(1M),
Bewerbung CLOB(50k)
);
Verwendung von BOOLEAN- und LOB-Attributen:
SELECT Name
FROM MitarbeiterTupeltabelle
WHERE Vollzeit IS TRUE AND
POSITION ('Datenbanken' IN Bewerbung) > 0;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-24
Tupeltypkonstruktor – ROW
Erzeugen eines Tupeltyps:
ROW(Feldname_1 Datentyp_1, ..., Feldname_n Datentyp_n)
Beispiel:
ALTER TABLE MitarbeiterTupeltabelle
ADD COLUMN Anschrift ROW(Strasse VARCHAR(30),
Nr DECIMAL(4),
PLZ DECIMAL(5),
Ort VARCHAR(40),
Land VARCHAR(25));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-25
Operationen auf Tupeltypen
Erzeugen eines Tupels mit dem Tupelkonstruktor (heisst genauso wie der
Tupeltypkonstruktor):
ROW('Seestrasse', 31, 8008, 'Zürich', 'Schweiz')
Zugriff auf ein Tupelfeld
mittels Punktnotation:
SELECT Name, Anschrift.ORT
FROM MitarbeiterTupeltabelle
Vergleich zweier Tupel:
ROW(1, 2, 7) = ROW(1, 2, 7)
-- liefert TRUE
ROW(1, 2, 7) = ROW(1, 3, 7)
-- liefert FALSE
ROW(1, 2, 7) = ROW(1, NULL, 7) -- liefert UNKNOWN
ROW(1, 2, 7) < ROW(1, 3, 5)
ROW(1, 2, 7) < ROW(1, 1, 7)
ROW(1, 2, 7) < ROW(1, NULL, 7)
ROW(1, 2, 7) < ROW(1, 3, NULL)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
-- liefert TRUE
-- liefert FALSE
-- liefert UNKNOWN
-- liefert TRUE
3-26
Arraytypkonstruktor – ARRAY
Erzeugen eines Arraytyps:
Elementtyp ARRAY[Maximale-Kardinalität]
Beispiel:
ALTER TABLE MitarbeiterTupeltabelle
ADD COLUMN Sprachkenntnisse VARCHAR(15) ARRAY[8];
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-27
Operationen auf Arraytypen (1)
Erzeugen eines Arrays mit dem Arraykonstruktor (heisst genauso wie der
Arraytypkonstruktor):
ARRAY['Deutsch', 'Englisch']
Direkter Zugriff auf das
i-te Arrayfeld mittels [i]:
SELECT Sprachkenntnisse[2]
FROM MitarbeiterTupeltabelle;
Deklarativer
Elementzugriff
mittels Entschachtelung:
SELECT s
FROM MitarbeiterTupeltabelle,
UNNEST(Sprachkenntnisse) s;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-28
Operationen auf Arraytypen (2)
Änderung eines
einzelnen
Arrayelements:
UPDATE MitarbeiterTupeltabelle
SET
Sprachkenntnisse[1] = 'Türkisch';
Änderung des
kompletten Arraywerts:
UPDATE MitarbeiterTupeltabelle
SET
Sprachkenntnisse = ARRAY['Türkisch'];
Kardinalität liefert Anzahl
der Arrayelemente:
CARDINALITY(ARRAY['Deutsch', Türkisch'])
-- liefert den Wert 2
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-29
Operationen auf Arraytypen (3)
Konkatenation zweier Arrays:
ARRAY['Deutsch', Türkisch'] || ARRAY['Englisch']
-- erzeugt ARRAY['Deutsch', Türkisch', 'Englisch']
Vergleich zweier Arrays:
—
Zwei Arrays sind vergleichbar g.d.w. ihre Elementtypen vergleichbar sind
—
Zwei vergleichbare Array A1 und A2 sind gleich (A1=A2) g.d.w
(1) sie die gleiche Kardinalität besitzen und
(2) alle Elemente paarweise gleich sind
ARRAY['Deutsch', Türkisch'] <> ARRAY['Deutsch', Türkisch', 'Englisch'] -- liefert TRUE
ARRAY['Deutsch', Türkisch'] = ARRAY['Deutsch', Türkisch', NULL]
-- liefert FALSE
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-30
Referenztypkonstruktor – REF
Erzeugen eines Referenztyps:
REF(Strukturierter-Typ) [SCOPE (TypisierteTabelle)]
–
–
referenzierter Typ muss ein strukturierter Typ sein
Instanzen eines Referenztyp können nur dann dereferenziert werden, wenn
eine entsprechend typisierte Tabelle als Wertebereich (Scope) festgelegt
wurde
Beispiel:
ALTER TABLE MitarbeiterTupeltabelle
ADD COLUMN Abteilung REF(AbteilungsTyp) SCOPE Abteilungen;
–
AbteilungsTyp sei hier ein strukturierter Typ und Abteilung eine auf diesem
Typ basierende typisierte Tabelle
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-31
Operationen auf Referenztypen
Erzeugen einer Referenz auf ein Objekt durch Zuweisung der zugehörigen OID an
ein Referenzattribut bzw. Referenzvariable
Dereferenzierung mittels
Pfeil-Operator:
SELECT Abteilung->Name
FROM MitarbeiterTupeltabelle;
Referenzauflösung mittels
DEREF-Operator:
SELECT DEREF(Abteilung)
FROM MitarbeiterTupeltabelle;
Vergleich zweier Referenzen:
— Zwei Referenzen sind vergleichbar g.d.w. ihre referenzierten Typen gleich sind
— Zwei vergleichbare Referenzen R1 und R2 sind gleich (R1=R2) g.d.w sie
denselben Referenzwert aufweisen (d.h. auf dasselbe Objekte verweisen)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-32
Integritätsbedingungen für Referenzattribute
z
Referentielle Reaktionen analog zu Fremdschlüsseln
–
–
–
–
z
NO ACTION/RESTRICT: verbietet DELETE eines referenzierten Objekts
CASCADE: löscht automatisch alle referenzierenden Tupel und Objekte
SET NULL: setzt korrespondierende Referenzattribute auf NULL
SET DEFAULT: setzt korrespondierende Referenzattribute auf DEFAULT
„ SQL:1999 erlaubt derzeit nur NULL als Defaultwert für ein Referenzattribut
Beispiel: Hinzufügen eines Referenzattributs mit Scope und referenzieller
Reaktion
ALTER TABLE MitarbeiterTupeltabelle
ADD Projekt REF(ProjektTyp)
SCOPE InterneProjekte
REFERENCES ARE CHECKED ON DELETE CASCADE;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-33
Neue Typkonstruktoren – Beispiele (1)
Verwendung des Tupel- bzw. Arraytypkonstruktors:
INSERT INTO MitarbeiterTupeltabelle(Name, Sprachkenntnisse)
VALUES (ROW('Billy', 'Clintwood'), ARRAY['Deutsch', 'Englisch', 'Türkisch']);
Zugriff auf tupelwertige Attribute bzw. Verwendung arraywertiger Attribute:
SELECT Name.Nachname
FROM MitarbeiterTupeltabelle
WHERE Name.Vorname = 'John' AND
'Deutsch' IN (SELECT * FROM UNNEST(Sprachkenntnisse));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-34
Neue Typkonstruktoren – Beispiele (2)
Änderung eines Tupelfeldes:
UPDATE MitarbeiterTupeltabelle
SET Name.Nachname = 'Türker'
WHERE Name.Nachname = 'Tuerker';
Änderung eines Arrayelements mittels direktem Positionszugriff:
UPDATE MitarbeiterTupeltabelle
SET Sprachkenntnisse[4] = 'Französisch'
WHERE Name.Nachname = 'Türker';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-35
Distinct-Typen
Kopie eines existierenden Datentyps mit neuem Namen
–
–
–
–
–
Wiederverwendung
Strenge Typisierung (basiert auf Namensäquivalenz)
Nicht-optionales Schlüsselwort FINAL schliesst Subtypbildung aus
Systemdefinierte Vergleichsoperatoren basierend auf dem Quelltyp
Cast-Operatoren zur Konversion zwischen Distinct- und Quelltyp
Erzeugen eines
Distinct-Typs:
Beispiele:
CREATE TYPE Distinct-Typname AS (Quelltyp) FINAL
[CAST (SOURCE AS DISTINCT) WITH Funktionsname]
[CAST (DISTINCT AS SOURCE) WITH Funktionsname]
CREATE TYPE Franken AS DECIMAL(12,2) FINAL;
CREATE TYPE Euro AS DECIMAL(12,2) FINAL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-36
Operationen auf Distinct-Typen
Erzeugen einer Instanz eines Distinct-Typs:
Distinct-Typname(Quelltypwert)
Beispiele für Distinct-Werte:
Franken(1311.69)
Euro(170470.13)
Vergleich zweier Distinct-Werte:
— Distinct-Typen unterliegen der strengen Typisierung
— Zwei Distinct-Werte sind vergleichbar g.d.w. ihre Distinct-Typen identisch sind
— Beispiel: Franken(1000.00) = Euro(1000.00) ergibt einen Typkonflikt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-37
Verwendung von Distinct-Typen (1)
Beispiel: Distinct-Typen in Tabellendefinitionen:
CREATE TABLE CHBank(Nr INTEGER, Stand Franken);
CREATE TABLE EuroBank(Nr INTEGER, Stand Euro);
Anfrage basierend auf Distinct-Typen:
SELECT e.Nr
FROM CHBank c, EuroBank e
WHERE c.Nr= 234302 AND c.Stand > e.Stand;
Anfrage ergibt Typkonflikt:
Schweizer Franken und Euro
sind nicht vergleichbar
→ Typkonvertierung notwendig!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-38
Verwendung von Distinct-Typen (2)
Vorherige Anfrage nun mit expliziter Konvertierung von Euro nach Franken:
SELECT e.Nr
FROM CHBank c, EuroBank e
WHERE c.Nr= 234302 AND
CAST(c.Stand AS DECIMAL) > CAST(e.Stand AS DECIMAL);
Problem: Derartige Konvertierungen sind nicht immer "semantisch" sinnvoll!
Oftmals sind benutzerdefinierte Cast-Funktionen notwendig, die nicht nur
den Typ, sondern auch den Wert konvertieren bzw. neu berechnen.
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-39
Benutzerdefinierte Cast-Funktionen
z
Konversion zwischen unterschiedlichen Datentypen
z
Syntax:
CREATE CAST (Typ1 AS Typ2)
WITH FUNCTION Konvertierungsfunktion(Parameter Typ1)
[AS ASSIGNMENT]
–
–
–
Typ1 oder Typ2 muss ein benutzerdefinierter Typ oder ein Referenztyp sein
Konvertierungsfunktion
– hat genau einen Parameter vom Typ Typ1 und Typ2 als Rückgabetyp
– ist deterministisch und liest und ändert keine SQL-Daten
AS ASSIGNMENT: Implizite Cast-Funktion (wird automatisch aufgerufen)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-40
Explizite Cast-Funktionen – Beispiel
Definition einer Konvertierungsfunktion:
CREATE FUNCTION Franken(e Euro)
RETURNS Franken
RETURN CAST(CAST(e AS DECIMAL) * 1.5 AS Franken);
Definition einer expliziten Cast-Funktion:
CREATE CAST (Euro AS Franken) WITH FUNCTION Franken(Euro);
Vorherige Anfrage nun mit direkter Konvertierung von Euro nach Franken:
SELECT e.Nr
FROM CHBank c, EuroBank e
WHERE c.Nr= 234302 AND c.Stand > CAST(e.Stand AS Franken);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-41
Implizite Cast-Funktionen – Beispiel
Definition einer Konvertierungsfunktion:
CREATE FUNCTION Euro(f Franken)
RETURNS Euro
RETURN CAST(CAST(f AS DECIMAL) * 0.65 AS Euro);
Definition einer impliziten Cast-Funktion:
CREATE CAST (Franken AS Euro) WITH FUNCTION Euro(Franken)
AS ASSIGNMENT;
Vorherige Anfrage nun mit impliziter Konvertierung von Franken nach Euro:
SELECT e.Nr
FROM CHBank c, EuroBank e
WHERE c.Nr= 234302 AND c.Stand > e.Stand;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-42
Strukturierte Typen
Abstrakte Objekttypen mit Verhalten und eingekapselter Struktur
–
Optional: Instanziierung verbieten
–
Nicht-optionales Schlüsselwort NOT FINAL erlaubt Subtypbildung
–
Optional: Bestimmen der Referenzgenerierung (OID-Erzeugung)
–
Optional: Objektverhalten in Methoden kodieren
–
Optional: Subtypbildung mittels UNDER-Klausel
Strukturierte Typen, die keine Subtypen sind, heissen Wurzeltypen
Syntax: Definition von Wurzeltypen
CREATE TYPE Typname
AS (Attributdefinitionsliste)
[[NOT] INSTANTIABLE]
NOT FINAL
[Referenzgenerierung [Referenzcasting]]
[Methodendeklarationsliste]
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
FINAL (ab SQL:2003 als
Alternative möglich)
3-43
Attributdefinition
Syntax:
Attributname Typ
[REFERENCES ARE NOT CHECKED
| REFERENCES ARE CHECKED ON DELETE NO ACTION]
[DEFAULT Defaultwert]
–
Bei Referenzattributen, die mit einer SCOPE-Klausel versehen sind,
muss auch die REFERENCES-Klausel spezifiziert werden
–
Default: REFERENCES ARE NOT CHECKED
„ Ungültige Referenzen sind möglich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-44
Referenzgenerierung und Referenzcasting
z
Nur für Wurzeltypen definierbar
z
Syntax:
REF USING Typ
| REF FROM (Attributliste)
| REF IS SYSTEM GENERATED
CAST (SOURCE AS REF) WITH FUNCTION Name
CAST (REF AS SOURCE) WITH FUNCTION Name
–
REF USING Typ
ƒ OID-Werte sind von einem bestimmten Basisdatentyp, etwa INTEGER
–
REF FROM (Attributliste)
ƒ OID aus vorhandenen Attributwerten eines Schlüssels funktional
bestimmt
–
REF IS SYSTEM GENERATED
ƒ OID-Generierung durch das System (Defaulteinstellung)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-45
Strukturierte Typen – Beispiele
Instanzen strukturierter Typen heissen Objekte
CREATE TYPE AdressTyp AS (
Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)
) NOT FINAL;
Basisdatentypen
CREATE TYPE PersonTyp AS (
Name
VARCHAR(30),
Anschrift
AdressTyp,
Ehepartner
REF(PersonTyp),
Kinder
REF(PersonTyp) ARRAY[10]
) NOT FINAL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Strukturierter Typ
Einbettung
Referenz
Konstruierte Typen
3-46
Operationen auf strukturierten Typen (1)
Erzeugen einer Instanz eines strukturierten Typs mit dem Default-Konstruktor,
der genauso heisst wie der zugehörige strukturierte Typ:
z.B. AdressTyp() oder PersonTyp()
Attributzugriff erfolgt mittels Funktionsaufrufe sowie Punkt-Operator
Vergleich zweier Objekte (Instanzen strukturierter Typen):
— keine Ordnungsrelationen implizit bereitgestellt
— benutzerdefinierte Ordnungsfunktionen können aber definiert werden
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-47
Operationen auf strukturierten Typen (1)
Typtest:
—
—
Objekt IS [NOT] OF (Typnamensliste)
Prädikat IS OF wird TRUE, wenn die Liste den Typ des Objekts bzw. einen
Supertyp davon enthält
Letzteres kann durch die Verwendung von ONLY ausgeschlossen werden, z.B.
Objekt IS OF (ONLY(Mitarbeiter))
Temporäre Typanpassung entlang einer Typhierarchie:
(Objekt AS Supertypname)
TREAT(Objekt AS Subtypname)
-- Anpassung nach oben
-- Anpassung nach unten
Bemerkung: Objekt = Instanz eines strukturierten Typs (strukturierter Wert)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-48
Einkapselung von strukturierten Typen
z
Vollständige Einkapselung
– Attribute nur über Methoden zugreifbar
– Attributzugriffe und Methodenaufrufe gleich behandelt
z
Implizite Observer- und Mutator-Methoden für jedes Attribut
Observer:
CREATE METHOD Strasse()
CREATE METHOD Nr()
CREATE METHOD PLZ()
CREATE METHOD Ort()
CREATE METHOD Land()
FOR AdressTyp
FOR AdressTyp
FOR AdressTyp
FOR AdressTyp
FOR AdressTyp
Mutator:
CREATE METHOD Strasse(VARCHAR(30))
CREATE METHOD Nr(DECIMAL(5))
CREATE METHOD PLZ(DECIMAL(5))
CREATE METHOD Ort(VARCHAR(40))
CREATE METHOD Land(VARCHAR(25))
RETURNS VARCHAR(30) …;
RETURNS DECIMAL(4) …;
RETURNS DECIMAL(5) …;
RETURNS VARCHAR(40) …;
RETURNS VARCHAR(25) …;
FOR AdressTyp
FOR AdressTyp
FOR AdressTyp
FOR AdressTyp
FOR AdressTyp
RETURNS AdressTyp …;
RETURNS AdressTyp …;
RETURNS AdressTyp …;
RETURNS AdressTyp …;
RETURNS AdressTyp …;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-49
Attributzugriff bei Instanzen eines strukturierten Typs
z
Zugriff über Funktionsaufrufe oder Punkt-Operator
–
–
z
entspricht X.Atributname()
entspricht X.Attributname(Wert)
Pfadausdrücke
–
–
z
X.Attributname
SET X.Attributname = Wert
Nacheinander Anwendung des Punkt-Operators
Navigierende Zugriffe
Beispiel:
BEGIN
DECLARE p PersonTyp;
SET p = PersonTyp();
SET p.Name = 'Luke Skywalker';
SET p.Anschrift.Ort = 'Hollywood';
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-50
Erzeugen von Instanzen eines strukturierten Typs
z
Verwendung des Default-Konstruktors: Typname()
z
Initialisierung mittels Mutators oder überladenen Konstruktor
z
Beispiele für das Überladen eines Konstruktors:
CREATE FUNCTION AdressTyp (s VARCHAR(30),
n DECIMAL(4),
p DECIMAL(5),
o VARCHAR(40),
l VARCHAR(25))
RETURNS AdressTyp
RETURN AdressTyp().Strasse(s).Nr(n).PLZ(p).Ort(o).Land(l);
CREATE FUNCTION PersonTyp (n VARCHAR(30), a AdressTyp)
RETURNS PersonTyp
BEGIN
DECLARE p PersonTyp;
SET p = PersonTyp();
SET p.Name = n;
SET p.Anschrift = a;
RETURN p;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Aufruf des
DefaultKonstruktors
3-51
Subtypbildung – Aufbau von Typhierarchien
CREATE TYPE Typname UNDER Supertypname
AS (Attributdefinitionsliste)
[[NOT] INSTANTIABLE]
NOT FINAL
[Methodendeklarationsliste]
z
Syntax:
z
Subtypdefinition mittels UNDER-Klausel
–
Subtyp hat genau einen direkten Supertyp (keine direkte Mehrfachvererbung)
–
Supertyp muss selbst ein strukturierter Typ sein
–
Subtyp erbt alle Attribute und Methoden des Supertyps
–
Subtyp kann geerbte Methoden überschreiben und überladen
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-52
Subtypen – Beispiele
CREATE TYPE MitarbeiterTyp UNDER PersonTyp AS (
PNr
INTEGER,
Bewerbung
CLOB(50K),
Bild
BLOB(5M),
Vorgesetzter REF(MitarbeiterTyp),
Projekte
REF(ProjektTyp) ARRAY[50],
Gehalt
Franken
) NOT FINAL;
CREATE TYPE ManagerTyp UNDER MitarbeiterTyp AS (
Leitet
REF(ProjektTyp) ARRAY[100],
Bonus
Franken
) NOT FINAL;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-53
Methoden
z
Objektverhalten in Methoden kodiert
z
Methoden sind Funktionen, die zu einem strukturierten Typen gehören
–
–
z
Deklaration erfolgt innerhalb der Typdefinition
Besitzen impliziten SELF-Parameter
Overloading, Overriding und Late Binding
–
–
Überladen von Methodennamen
„ mehrere gleichnamige Methoden mit unterschiedlichen Parametern
Dynamisches Binden von überschriebenen Methoden zur Laufzeit
„ Auswahl der Implementierung hängt vom Objekttyp ab
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-54
Methodendeklaration
z
Syntax: Deklaration einer Methode
[INSTANCE | STATIC]
METHOD Methodenname
(Parameter)
RETURNS Rückgabetyp
[Methodencharakteristikaliste]
z
STATIC definiert Tabellenmethode
Syntax: Deklaration einer "überschreibenden" Methode
OVERRIDING
METHOD Methodenname
(Parameter)
RETURNS Rückgabetyp
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-55
Methodencharakteristika (1)
z
Syntax:
[LANGUAGE {ADA | C | COBOL | FORTRAN | MUMPS | PASCAL | SQL | JAVA}]
[PARAMETER STYLE {SQL | GENERAL | JAVA}]
[[NOT] DETERMINISTIC]
[NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA]
[{RETURNS NULL | CALLED} ON NULL INPUT]
[TRANSFORM GROUP Gruppenname [FOR TYPE StrukturierterTyp]]
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-56
Methodencharakteristika (2)
z
Angabe, ob die Berechnung deterministisch ist oder nicht
–
Relevant für CHECK- und CASE-Definition
NOT DETERMINISTIC
Zwei aufeinanderfolgende Aufrufe der Methode mit
den gleichen Argumenten können selbst bei gleichen
DB-Zuständen unterschiedliche Resultate liefern
(Beispiel: Methoden, die DATETIME abfragen)
DETERMINISTIC
Methode hat in zwei aufeinanderfolgenden Aufrufen
mit den gleichen Argumenten auf dem gleichen DBZustand den gleichen Effekt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-57
Methodencharakteristika (3)
z
Angabe, ob und welche Art von SQL-Anweisungen eine Methode enthält
NO SQL
Enthält keine SQL-Anweisungen
CONTAINS SQL
Enthält SQL-Anweisungen
READS SQL DATA
Liest SQL-Daten
MODIFIES SQL DATA
Schreibt SQL-Daten
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-58
Methodencharakteristika (4)
z
Angabe, ob Methode aufrufbar ist, wenn ein Parameter NULL ist
RETURNS NULL
ON NULL INPUT
Ist beim Aufruf der Methode mindestens einer der
Parameter NULL, dann wird die Methode nicht
ausgeführt und NULL zurückgeliefert
CALLED
ON NULL INPUT
Methode wird auch dann ausgeführt, wenn einer der
Parameter NULL ist
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-59
Methodencharakteristika (5)
z
Transformgruppen enthalten Funktionen, die automatisch aufgerufen
werden, wenn benutzerdefinierte Typen von der SQL-Umgebung in die
Host-Sprachumgebung (und umgekehrt) transferiert werden
z
Syntax: Definition einer Transformgruppe
CREATE TRANSFORM FOR BDTyp Gruppenname (
FROM SQL WITH Funktionsname(Parameter),
TO SQL WITH Funktionsname(Parameter)
)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-60
Methodencharakteristika (5)
z
Defaulteinstellungen
INSTANCE
STATIC schliesst OVERRIDING aus
LANGUAGE SQL
LANGUAGE SQL schliesst NO SQL,
Parameterstyle-Klausel, Transform-Klausel aus
NOT DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-61
Methodendeklaration – Beispiele
CREATE TYPE PersonTyp AS (
...
) NOT FINAL
METHOD AnzahlKinder() RETURNS INTEGER;
CREATE TYPE MitarbeiterTyp AS (
...
) NOT FINAL
METHOD Einkommen() RETURNS Franken,
METHOD Gehaltserhöhung() RETURNS Franken;
CREATE TYPE ManagerTyp AS (
...
) NOT FINAL
OVERRIDING METHOD Einkommen() RETURNS Franken,
OVERRIDING METHOD Gehaltserhöhung() RETURNS Franken;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-62
Methodendefinition
z
Implementierung einer deklarierten Methode
z
Syntax:
CREATE [INSTANCE | STATIC] METHOD Methodenname (Parameter)
RETURNS Rückgabetyp
FOR StrukturierterTyp
Methodenrumpf
z
Methodenrumpf besteht aus einer SQL-Prozedur-Anweisung
–
–
–
Schemadefinitions- oder Schemamanipulationsanweisung
INSERT, UPDATE, DELETE, SELECT (SQL-Datenänderungsanweisungen)
...
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-63
Methodendefinition – Beispiele
z
Definition einer "lesenden" Methode
CREATE METHOD Einkommen()
RETURNS Franken
FOR MitarbeiterTyp
RETURN SELF.Gehalt;
z
Überschreiben einer "lesenden" Methodendefinition
CREATE METHOD Einkommen()
RETURNS Franken
FOR ManagerTyp
RETURN Franken(CAST(SELF.Gehalt AS NUMERIC) +
CAST(SELF.Bonus AS NUMERIC));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-64
Erweiterung von SQL zur Programmiersprache
Compound statement
BEGIN [ATOMIC] SQL-Anweisungen END;
SQL variable declaration
DECLARE Variable Datentyp;
IF statement
IF Prädikat THEN SQL-Anweisungen
ELSE SQL-Anweisungen END IF;
CASE statement
CASE X WHEN Wert THEN SQL-Anweisungen
ELSE SQL-Anweisungen END CASE;
LOOP statement
LOOP SQL-Anweisungen END LOOP;
WHILE statement
WHILE Prädikat DO SQL-Anweisungen END WHILE;
REPEAT statement
REPEAT SQL-Anweisungen UNTIL Prädikat END REPEAT;
FOR statement
FOR Loop-Variable AS Cursor-Spezifikation
DO SQL-Anweisungen END FOR;
RETURN statement
RETURN Rückgabewert;
CALL statement
CALL Routine(Parameterliste);
Assignment statement
SET Variable = Wert;
SIGNAL statement
SIGNAL Ausnahme;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-65
Definition einer "schreibenden" Methode
CREATE METHOD GehaltsErhöhung()
RETURNS Franken
FOR MitarbeiterTyp
BEGIN
DECLARE altesGehalt NUMERIC(12,2);
SET altesGehalt = CAST(SELF.Gehalt AS NUMERIC);
IF (SELF.AnzahlKinder < 3) OR (SELF.AnzahlProjekte < 2) THEN
SET SELF.Gehalt = Franken(1.03 * altesGehalt);
ELSE
SET SELF.Gehalt = Franken(1.05 * altesGehalt);
END IF;
IF (CAST(SELF.Gehalt AS NUMERIC) > 500000) THEN
CALL raise_error('Mitarbeitergehalt zu hoch!');
END IF;
RETURN SELF.Gehalt;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-66
Überschreiben einer "schreibenden" Methode
CREATE METHOD GehaltsErhöhung()
RETURNS Franken
FOR ManagerTyp
BEGIN
DECLARE altesGehalt NUMERIC(12,2);
SET altesGehalt = CAST(SELF.Gehalt AS NUMERIC);
SET SELF.Gehalt = Franken(altesGehalt * (1+0.01*(SELF.AnzahlProjekte +
SELF.AnzahlKinder)));
IF (SELF.Gehalt > 25000000) THEN
CALL raise_error('Managergehalt zu hoch!');
END IF;
RETURN SELF.Gehalt;
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-67
Einsatz strukturierter Typen als ...
z
Attributtyp anderer strukturierter Typen
z
Parametertyp von Methoden, Funktionen und Prozeduren
z
Typ von SQL-Variablen
z
Typ einer typisierten Tabelle
z
Typ von Tabellenspalten
CREATE TABLE PersonenTupeltabelle (
Stammdaten PersonTyp,
Bild
BLOB(1M),
Lebenslauf
CLOB(50k)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-68
Instanziierung und Verwendung strukturierter Typen
z
Instanziierung mittels (Typ-)Konstruktor
–
Geschachtelte Aufrufe über überladene Konstruktoren
INSERT INTO PersonenTupeltabelle
VALUES (PersonTyp('Billy Bär',
AdressTyp('Seefeldstrasse', 31, 8008, 'Zürich', 'CH'),
NULL,
NULL),
NULL, NULL);
z
Zugriff auf Objektattributwerte (und Aufruf von Methoden)
SELECT Stammdaten.Name, Stammdaten.AnzahlKinder
FROM PersonenTupeltabelle
WHERE Stammdaten.Anschrift.Ort = 'Zürich' AND
POSITION('Diplom' IN Lebenslauf) > 0;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-69
Benutzerdefinierte Gleichheit von Instanzen
z
Vergleichbarkeit und Ordnung von Instanzen benutzerdefinierter Typen
–
–
z
Ordnungsformen:
„ NONE
„ EQUALS ONLY
„ ORDER FULL
Ordnungskategorien:
„ RELATIVE: Ordnungsfunktion ordnet jeweils zwei Instanzen
„ MAP: Vergleich bzw. Ordnung basiert auf dem Ergebnis einer
Abbildungsfunktion, die Instanzen auf Werte von Basisdatentypen
abbildet
„ STATE: Vergleich basiert auf paarweise Gleichheit der Attributwerte
Syntax:
CREATE ORDERING FOR BDTyp
{EQUALS ONLY | ORDER FULL} BY
{RELATIVE WITH FUNCTION Ordnungsfunktion(P1 BDTyp, P2 BDTyp)
| MAP WITH FUNCTION Abbildungsfunktion(Parameter BDTyp)
| STATE [Schemaname]}
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-70
Benutzerdefinierte Gleichheit von Instanzen (Forts.)
z
Einschränkungen
–
–
–
–
–
z
Alle Typen einer Typhierarchie müssen gleiche Ordnungsform besitzen
FULL erfordert RELATIVE oder MAP
STATE nur für strukturierte Typen definierbar
RELATIVE und STATE nur für Wurzeltypen definierbar
Distinct-Typen: ORDER FULL
Definition von STATE für PersonTyp erzeugt automatisch:
CREATE FUNCTION EQUALS(p1 PersonTyp, p2 PersonTyp)
RETURNS BOOLEAN
RETURN (p1.SPECIFICTYPE =p2.SPECIFICTYPE AND
p1.Name = p2.Name AND
p1.Anschrift = p2.Anschrift AND
p1.Ehepartner = p2.Ehepartner AND
p1.Kinder = p2.Kinder);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-71
Ordnungs- und Abbildungsfunktionen
z
Seien X, Y Instanzen eines benutzerdefinierten Typs Z
– Sei RF eine Ordnungsfunktion für Z. Dann gelten folgende
Ordnungsrelationen:
X = Y ⇔ RF(X, Y) = 0
X < Y ⇔ RF(X, Y) = -1
X > Y ⇔ RF(X, Y) = 1
–
X ≠ Y ⇔ RF(X, Y) ≠ 0
X ≤ Y ⇔ RF(X, Y) ∈{-1, 0}
X ≥ Y ⇔ RF(X, Y) ∈{0, 1}
Sei MF eine Abbildungsfunktion für Z. Dann gelten folgende
Ordnungsrelationen:
X θ Y ⇔ MF(X) θ MF(Y), wobei θ ∈{<, ≤, =, ≠, ≥, >}
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-72
Ordnung durch benutzerdefinierte
Abbildungsfunktionen
Definition einer Abbildungsfunktion für strukturierten Typ AdressTyp:
CREATE FUNCTION AdresseMap(a AdressTyp)
RETURNS VARCHAR(104)
RETURN a.Land || a.Ort || a.Strasse || CAST(a.Nr AS CHAR(4)) ||
CAST(a.PLZ AS CHAR(5));
Definition einer Ordnungsfunktion für strukturierten Typ AdressTyp:
CREATE ORDERING FOR AdressTyp
ORDER FULL BY
MAP WITH FUNCTION AdresseMap(AdressTyp);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-73
Verwendung benutzerdefinierter Ordnungsfunktionen
Beispielanfragen, in denen eine Ordnungsfunktion zum Einsatz kommt:
– Anfragen mit ORDER BY:
SELECT Stammdaten.Name, Stammdaten.Anschrift
FROM PersonTupeltabelle
ORDER BY Stammdaten.Anschrift;
– Anfragen mit Kleiner/Grösser(Gleich)-Prädikaten:
SELECT Stammdaten.Name, Stammdaten.Anschrift
FROM PersonTupeltabelle
WHERE Stammdaten.Anschrift < AdressTyp('Seestrasse', 31, 8008, 'Zürich', 'CH');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-74
Zusammenfassung – Benutzerdefinierte Typen
z
Distinct-Typen als Kopien vorhandener Typen
–
z
Strenge Typisierung (Typkompatibilität über Namensäquivalenz)
Strukturierte Typen als abstrakte Objekttypen
–
–
–
–
–
–
Objekteinbettung vs. Objektreferenzierung (Kopier- vs. Referenzsemantik)
Kapselung (Implizite Observer- und Mutator-Methoden für alle Attribute)
Aggregationshierarchien (Strukturierte Typen zusammengesetzt aus anderen
strukturierten Typen)
Typhierarchien (Subtypbildung)
Overloading und Overriding von Methoden plus dynamisches Binden
Vergleichbarkeit und Ordnung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-75
Tabellen
z
Einziges Konzept (Container), um Daten persistent speichern zu können
z
Bisher in SQL-92
–
–
z
Tabelle als Multimenge von Tupeln
TYPE(Tabelle) = MULTISET(ROW(TYPE(A1), ..., TYPE(An)))
„ wobei TYPE(Ai) ein Basisdatentyp ist
Erweiterungen in SQL:1999
–
–
–
Attribute können arraywertig, tupelwertig, objektwertig oder referenzwertig
sein
Typ einer Tabelle kann durch einen strukturierten Typ festgelegt werden
„ Derartige Tabellen heissen typisierte Tabellen (oft auch Objekttabellen)
„ Zeilen entsprechen Objekten (Instanzen) des festgelegten Typs
Subtabellen (Tabellenhierarchien)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-76
Tabellen (Forts.)
z
Tupel- vs. Objekttabellen
–
–
–
z
Tupeltabelle = Tabelle, die einer Multimenge von Tupeln entspricht
Objekttabelle = Tabelle, die einer Menge von Objekten
(Instanzen eines strukturierten Typs) entspricht
Typisierte Tabelle = Tabelle, dessen Typ ein benutzerdefinierter Typ ist
Aufgepasst
–
–
–
–
Tupeltabellen können objektwertige Attribute enthalten
Prinzipiell sind die gleichen Attributtypen für Tupel- und Objekttabellen erlaubt
SQL:1999 verwendet den Begriff Typisierte Tabelle mit Objekttabellensemantik
Prinzipiell kann aber auch eine Tupeltabelle typisiert sein (siehe Informix)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-77
Definition von Tupeltabellen – Revisited
z
Syntax:
z
Tabellenelement:
CREATE TABLE Tabellenname (Tabellenelementliste)
Attributdefinition
| Tabellenbedingung
| LIKE Tabellenname
–
Attributdefinition beinhaltet Festlegung des Datentyps und eventuell die
Angabe eines Defaultwerts sowie von Attibutbedingungen
„ Datentyp ist ein Basisdatentyp, ein mittels (geschachtelter) Anwendung
von Typkonstruktoren konstruierter Typ oder ein benutzerdefinierter Typ
„ SCOPE-Festlegung bei Referenzattributen zwingend notwendig
–
Definition von Tabellenbedingungen wie bisher in SQL-92
–
LIKE-Klausel kopiert Attributdefinitionen einer anderen Tabelle (ohne
Attributbedingungen und Defaultwerte)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-78
Definition von Tupeltabellen – Beispiel
Instanzen einer Tupeltabelle heissen Tupel
CREATE TABLE PersonTupeltabelle (
Name
ROW(Vorname VARCHAR(15),
Nachname VARCHAR(25)),
Anschrift
AdressTyp,
Bild
BLOB(1M),
Lebenslauf
CLOB(50k),
Sprachkenntnisse VARCHAR(15) ARRAY[8],
Hobbys
VARCHAR(20) ARRAY[10]
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-79
Anfrage an Tupeltabellen – Beispiel
Die Anfrage
SELECT *
FROM PersonTupeltabelle;
liefert ein Ergebnis vom Typ
MULTISET(ROW( ROW(VARCHAR(15), VARCHAR(25)),
AdressTyp,
BLOB(1M),
CLOB(50k),
ARRAY[8](VARCHAR(15)),
ARRAY[10](VARCHAR(20)))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-80
Typisierte Tabellen
z
Eine typisierte Tabelle ist eine Objekttabelle, dessen Objekte von einem
strukturierten Typ ST sind
z
Eine solche Tabelle besteht aus einer Menge von Objekten
Tabellenname
OIDs sind
vom Typ
REF(ST)
OID-Spalte
Spalten (Columns) = Attribute
R
...
OID A1
An
...
...
Zeilen (Rows) = Objekte
...
Attributwerte
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-81
Tupeltabellen vs. typisierte Tabellen
Mitarbeiter (Tupeltabelle mit nicht-atomaren Attributen)
Name
Anschrift <Strasse, PLZ, Ort>
Hobbys
Bewerbung
Dienstwagen
Foto
VARCHAR
ROW(VARCHAR, INT, VARCHAR)
VARCHAR(10) Array[3]
Bewerbung(...)
REF(Auto)
BLOB
'Jim'
ROW('Seeblick', 8008, 'Zürich')
Array('Kino', 'Sport')
Bewerbung(...)
X'12033282292'
X'...'
atomare
Spalte
tupelwertige
Spalte
mengenwertige
Spalte
objektwertige referenzwertige LOBSpalte
Spalte
Spalte
strukturierter Typ
Wagen (Typisierte Tabelle)
OID
Hersteller
Fotos
Farbe
PS
Motor
VARCHAR
VARCHAR
BLOB MULTISET
VARCHAR
INTEGER
Object(...)
X'120332828474292'
'Aston Martin'
MULTISET(X'...', X'...')
'Silber'
324
Object(...)
OID-Spalte
strukturierter Wert (erhält man durch Aufruf von DEREF(OID))
z
Typisierte Tabelle basiert auf einem strukturierten Typ (Objekttyp)
z
Zeilen haben eine OID und sind damit mittels Referenzspalten referenzierbar
z
Methoden können über die Dereferenzierung von OIDs auf Zeilen aufgerufen werden
z
Tabellenhierarchien sind möglich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-82
Definition von typisierten Tabellen
• Typisierte Tabelle basiert auf einem strukturierten Typen
• Syntax: Definition einer Wurzeltabelle
CREATE TABLE Tabellenname OF StrukturierterTyp (
Referenzgenerierungsoption
[Attributoptionsliste]
)
• Attributoption:
Attributname WITH OPTIONS Optionsliste
• Optionen:
SCOPE TypisierteTabelle
| DEFAULT Wert
| Integritätsbedingung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-83
Referenzgenerierung
z
Legt OID-Attribut und die Art der OID-Generierung fest (letzteres ist durch den
zugrunde liegenden Referenztyp bestimmt)
z
Muss für jede Wurzeltabelle angegeben werden
z
Darf nicht bei Subtabellen angegeben werden
z
Referenzgenerierungsoptionen
–
REF IS oid USER GENERATED
ƒ OID-Generierung durch den Benutzer
–
REF IS oid SYSTEM GENERATED
ƒ OID-Generierung durch das System
–
REF IS oid DERIVED (Attributliste)
ƒ OID aus vorhandenen Attributwerten eines Schlüssels funktional
bestimmt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-84
Definition von typisierten Tabellen – Beispiel
CREATE TABLE Personen OF PersonTyp (
REF IS oid SYSTEM GENERATED,
Ehepartner WITH OPTIONS SCOPE Personen,
Kinder WITH OPTIONS SCOPE Personen
);
z
Instanzen einer typisierten Tabelle heissen Objekte
z
Nur Instanzen einer typisierten Tabelle können mittels eines
Referenzattributs referenziert werden!
–
z
SCOPE-Klausel bestimmt die typisierte Tabelle, die referenziert wird
Aufruf der Methoden des zugrunde liegenden strukturierten Typs:
DEREF(oid).Methode(Parameter)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-85
Anfrage an typisierte Tabellen – Beispiel
Was ist der Ergebnistyp der folgenden Anfrage?
SELECT *
FROM Personen;
Vielleicht
SET(PersonTyp)
?
Nein, sondern
MULTISET(ROW(REF(PersonTyp),
VARCHAR(30),
AdressTyp,
REF(PersonTyp),
ARRAY[10](REF(PersonTyp))))
!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-86
Einfügen und Ändern in typisierten Tabellen – Beispiel
Erzeugen von Tabelleninhalten (impliziter Aufruf des PersonTyp-Konstruktors):
INSERT INTO Personen
VALUES ('Billy Bär',
AdressTyp('Seefeldstrasse', 31, 8008, 'Zürich', 'CH'),
(SELECT oid FROM Personen p WHERE p.Name = 'Mama Bär'),
NULL);
Belegen von Referenzattributen:
UPDATE Personen
SET Kinder[1] = (SELECT oid
FROM Personen p
WHERE p.Name = 'Baby Bär')
WHERE Name = 'Billy Bär';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-87
Selektion auf typisierten Tabellen – Beispiel
Zugriff auf ein Attribut eines eingebetteten Objektes erfolgt (analog zum
Tupelfeldzugriff) mittels Dot-Operator:
SELECT Name, Anschrift.Ort
FROM Personen;
Zugriff auf ein Attribut eines referenziertes Objekts erfolgt mittels PfeilOperator (entspricht Dereferenzierung plus Attributselektion):
SELECT Name, Ehepartner->Name
FROM Personen;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-88
Subtabellenbildung – Aufbau von Tabellenhierarchien
z
Subtabellendefinition mittels UNDER-Klausel
–
–
–
–
z
Subtabelle hat genau eine direkte Supertabelle (keine direkte
Mehrfachspezialisierung)
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 kann eigene Integritätsbedingungen definieren
Syntax:
CREATE TABLE Tabellenname OF StrukturierterTyp UNDER Supertabelle
[(Attributoptionsliste)]
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-89
Subtabellenbildung – Beispiele
CREATE TABLE Mitarbeiter OF MitarbeiterTyp
UNDER Personen
(
Vorgesetzter WITH OPTIONS SCOPE Mitarbeiter,
Projekte WITH OPTIONS SCOPE Projekte
);
CREATE TABLE Manager
OF ManagerTyp
UNDER Mitarbeiter;
Personen OF PersonTyp
OID
Name
Anschrift
Ehepartner
Kinder
VARCHAR
VARCHAR
AdressTyp
REF(PersonTyp)
REF(PersonTyp) ARRAY[10]
Mitarbeiter OF MitarbeiterTyp
UNDER
OID
Name
Anschrift
Ehepartner
Kinder
PNr
...
Gehalt
VARCHAR
VARCHAR
AdressTyp
REF(PersonTyp)
REF(PersonTyp) ARRAY[10]
INTEGER
...
Franken
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-90
Typ- versus Tabellenhierarchie
CREATE TABLE Spieler OF PersonTyp
Typ
CREATE TYPE PersonTyp
Typ
CREATE TABLE Personen OF PersonTyp
Supertyp
CREATE TYPE MitarbeiterTyp
UNDER PersonTyp
Supertabelle
Typ
CREATE TABLE Mitarbeiter
OF MitarbeiterTyp UNDER Personen
Typ
CREATE TABLE Angestellter
OF MitarbeiterTyp
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-91
Einfügungen und Löschungen in Tabellenhierarchien
z
INSERT an Supertabellen propagiert und DELETE wirkt auf Sub- und Supertabellen
Personen
PersonTyp
INSERT INTO Personen(Name) VALUES('Billy');
('Billy', ...)
DELETE FROM Personen WHERE Name='Joe';
('Joe', ...)
('Jim', ...)
UNDER
Mitarbeiter
MitarbeiterTyp
INSERT INTO Mitarbeiter(Name) VALUES('Joe');
('Joe', ...)
INSERT INTO Mitarbeiter(Name) VALUES('Jim');
('Jim', ...)
DELETE FROM Mitarbeiter WHERE Name='Jim';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-92
Anfragen an Tabellenhierarchien
SELECT * FROM Personen;
Zugriff auf tiefe Extension einer Supertabelle
liefert alle Personen, auch die Mitarbeiter
SELECT * FROM Mitarbeiter;
Zugriff auf tiefe Extension einer Subtabelle
liefert alle Mitarbeiter-Objekte mit den geerbten Personen-Attributen
SELECT * FROM ONLY(Personen);
Zugriff auf flache Extension einer Supertabelle
liefert alle Personen, die keine “speziellen” Personen (z.B. Mitarbeiter) sind
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-93
Dereferenzierung und Navigation
Dereferenzierung mittels DEREF (liefert Attributwerte eines referenzierten Objekts):
SELECT DEREF(Vorgesetzter)
FROM Mitarbeiter;
Dereferenzierung (von Referenzattributwerten) über Pfeil-Operator:
SELECT *
FROM Mitarbeiter
WHERE Vorgesetzter->Name = 'Billy';
Dereferenzierung mit anschliessendem Komponentenzugriff:
SELECT *
FROM Mitarbeiter
WHERE Vorgesetzter->Anschrift.PLZ = 65307;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-94
Typinformation und Konversion
Methoden werden auf dem speziellsten, deklarierten Typ aufgerufen!
Explizite Konversion vom Sub- zum Supertyp:
SELECT (DEREF(oid) AS MitarbeiterTyp).Einkommen()
FROM Mitarbeiter;
Zugriff auf allgemeinere
Einkommensmethode
des Typs MitarbeiterTyp
Explizite Konversion vom Super- zum Subtyp:
SELECT TREAT(DEREF(oid) AS ManagerTyp).Einkommen()
FROM Mitarbeiter
WHERE DEREF(oid) IS OF (ManagerTyp);
Zugriff auf speziellere
Einkommensmethode
des Typs ManagerTyp
Sei t eine Instanz eines benutzerdefinierten Typs und T eine Menge von
benutzerdefinierten Typen (inklusive all derer Subtypen), dann liefert das
Typprädikat t IS OF (T) TRUE g.d.w. der speziellste Typ von t Element von T ist
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-95
Substituierbarkeit
z
Instanz eines Subtyps kann in jedem Kontext benutzt werden, wo eine
Instanz eines Supertyps nutzbar ist
–
–
–
z
Eingabeargumente für Methoden, deren formale Parameter auf dem Supertyp
definiert sind
Rückgabewert einer Methode oder Funktion, für das der Supertyp als formaler
Typ definiert wurde
Zuweisungen zu Variablen oder Attributen des Supertyps
Beispiel: Tabelle mit Spalte, deren Typ ein strukturierter Typ ist
Stammdaten
PersonTyp
…
…
PersonTyp().Name('Joe')
Mitarbeiter().Name(’Jim’).Gehalt(4000)
...
...
Manager().Name(’Kim’).Gehalt(7000).Bonus(2000)
...
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-96
Overloading, Overriding und dynamisches Binden
z
Eine Methode kann mit einer gleichnamigen Methode überladen werden
z
Eine Methode kann eine geerbte Methode, die exakt die gleiche Signatur
besitzt, überschreiben
–
–
z
Dynamisches Binden erfolgt zur Laufzeit ("dynamic dispatch")
Bei identischer Methodensignatur wird die Methodenimplementierung
entsprechend des dynamischen Typs ausgewählt
Beispiel:
–
Folgende Anfrage berechnet das Einkommen von Personen, wobei die
allgemeine Berechnungsfunktion verwendet wird
SELECT CASE WHEN DEREF(oid) IS OF (ManagerTyp)
THEN (DEREF(oid) AS MitarbeiterTyp).Einkommen
ELSE DEREF(oid).Einkommen
END
FROM Mitarbeiter;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-97
Vererbung von Integritätsbedingungen
z
Integritätsbedingungen einer Supertabelle gelten implizit für alle Subtabellen
–
Ersetzen einer geerbten Integritätsbedingungen nicht unterstützt
z
Pro Tabelle maximal ein impliziter bzw. expliziter Primärschlüssel
z
Primärschlüssel sind nur auf Wurzeltabellen definierbar
z
Beispiel:
CREATE TABLE Mitarbeiter OF MitarbeiterTyp (
MNr WITH OPTIONS PRIMARY KEY,
Gehalt WITH OPTIONS NOT NULL,
CHECK(Gehalt >2000)
);
CREATE TABLE Manager OF ManagerTyp UNDER Mitarbeiter (
CHECK(Gehalt >5000)
);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-98
Definition von typisierten Sichten
• Typisierte Sichten basieren analog zu typisierten Tabellen auf einem
strukturierten Typen
• Wurzelsicht = typisierte Sicht ohne Under-Klausel
–
benötigt Angabe der Referenzgenerierung
„ SYSTEM GENERATED nicht erlaubt
„ USER GENERATED notwendig, falls Subsichten erzeugt werden sollen
• Syntax:
CREATE VIEW Sichtenname OF StrukturierterTyp (
Referenzgenerierungsoption
[Attributoptionsliste]
)
AS Anfrageausdruck
[WITH CHECK OPTION]
• Attributoption:
Attributname WITH OPTIONS SCOPE TypisierteSicht
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-99
Definition von typisierten Sichten (2)
z
Einschränkungen für die Sichtanfrage
–
–
–
z
darf nur eine Tabelle oder Sicht enthalten
ist diese Tabelle bzw. Sicht selbst typisiert, beschränkt sich der Zugriff auf die
flache Extension (ONLY)
Verbundoperationen und Gruppierung sind nicht erlaubt
Typisierte Sicht versus Tupelsicht
–
–
–
Zeilen einer typisierten Sicht haben eine OID und sind somit referenzierbar
Methoden sind auf Zeilen einer typisierten Sicht aufrufbar
Typisierte Sicht kann Subsicht einer anderen typisierten Sicht sein
„ Erweiterung der Supersicht
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-100
Definition von Wurzelsichten – Beispiele
CREATE VIEW GutBezahlteMitarbeiter OF MitarbeiterTyp
(REF IS oid USER GENERATED)
AS (SELECT * FROM ONLY(Mitarbeiter)
WHERE Gehalt > Franken(10000));
CREATE VIEW GuteManager OF ManagerTyp
(REF IS oid USER GENERATED)
AS (SELECT * FROM ONLY(Manager)
WHERE CARDINALITY(Leitet) > 3);
CREATE VIEW MehrsprachigePersonen OF PersonTyp
(REF IS oid USER GENERATED)
AS (SELECT * FROM ONLY(Personen)
WHERE CARDINALITY(Sprachkenntnisse) > 1);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-101
Subsichtenbildung – Aufbau von Sichtenhierarchien
z
Syntax: CREATE VIEW Sichtenname OF StrukturierterTyp
UNDER Supersicht
[(Attributoptionsliste)]
AS Anfrageausdruck
[WITH CHECK OPTION]
–
–
–
Subsicht hat genau eine direkte Supersicht (keine Mehrfachspezialisierung)
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
Erweiterte Supersichtanfrage = SELECT * FROM OriginalSupersichtanfrage
UNION ALL CORRESPONDING
SELECT * FROM Subsichtanfrage
„
UNION ALL CORRESPONDING vereinigt Sichten über die
Supersichtattribute (Schnittmenge beider Sichten)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-102
Subsichtenbildung – Beispiele
CREATE VIEW KinderreicheManager OF ManagerTyp
UNDER GutBezahlteMitarbeiter
AS (SELECT *
FROM ONLY(Manager)
WHERE CARDINALITY(Kinder) > 4);
Die Supersicht
GutBezahlteMitarbeiter
umfasst nun auch alle
"kinderreichen" Manager!
CREATE VIEW GutBezahlteManager OF ManagerTyp
UNDER GutBezahlteMitarbeiter
AS (SELECT *
FROM ONLY(Manager)
WHERE Gehalt > Franken(25000));
Die Supersicht
GutBezahlteMitarbeiter
umfasst nun auch alle
"gut bezahlten" Manager!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-103
Tabellen- vs. Sichtenhierarchie
CREATE TABLE Personen
OF PersonTyp
CREATE VIEW MehrsprachigePersonen
OF PersonTyp
Supertabelle
CREATE TABLE Mitarbeiter
OF MitarbeiterTyp
UNDER Personen
CREATE VIEW GutBezahlteMitarbeiter
OF MitarbeiterTyp
Supertabelle
CREATE TABLE Manager
OF ManagerTyp
UNDER Mitarbeiter
Supersicht
CREATE VIEW GutBezahlteManager
OF ManagerTyp
UNDER GutBezahlteMitarbeiter
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-104
Einfügungen und Löschungen in Sichtenhierarchien
z
z
z
Person
INSERTs und DELETEs an die
zugrundeliegenden Tabelle
propagiert
Mit=GutBezahlteMitarbeiter
Mng=GutBezahlteManager
INSERT INTO Mit(Name)
VALUES('Billy');
('Billy', ...)
('Joe', ...)
UNDER
Mit MitarbeiterTyp
INSERT INTO Mng(Name)
VALUES('Joe');
Mitarbeiter MitarbeiterTyp
('Billy', ...)
('Billy', ...)
('Joe', ...)
('Joe', ...)
DELETE FROM Mit
WHERE Name='Joe';
UNDER
UNDER
Mng
PersonTyp
ManagerTyp
('Joe', ...)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Manager
ManagerTyp
('Joe', ...)
3-105
SQL:1999 – Datenmodell
SET
MULTISET
OBJECT
ROW
ARRAY
REF
Einstiegspunkte in die Datenbank:
Typisierte Tabelle:
SET(OBJECT(...))
Untypisierte Tabelle:
MULTISET(ROW(...))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Basisdatentyp
Subtypbeziehung
Untermengenbeziehung
3-106
SFW-Block (1)
z
Grundgerüst einer SQL-Anfrage: SFW-Block
SELECT
Projektionsliste
FROM
Tabellenausdruck
[WHERE
Prädikat]
[GROUP BY Attributliste]
[HAVING Gruppenprädikat]
z
Was ist das Ergebnis einer Anfrage in SQL:1999?
–
–
–
Tupeltabelle
Objekttabelle
Wert bzw. Kollektion
9
9
–
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-107
SFW-Block (2)
z
SELECT: Was darf in der Projektionsliste stehen?
–
–
–
z
Attribute (auch abgeleitete, berechnete)
Methodenaufrufe
Unterabfragen
9
9
(9)
Nur skalare Unterabfragen,
die genau einen Wert liefern!
Beispiel:
SELECT m.Name,
12* DEREF(oid).Einkommen,
(SELECT COUNT(*)
FROM Mitarbeiter
WHERE Vorgesetzter = m.oid)
FROM Manager m;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-108
SFW-Block (3)
z
FROM: Welche Tabellenausdrücke sind erlaubt?
–
–
–
–
–
z
Tupeltabellen
Objekttabellen (auch flache Extensionen)
Kollektionsabgeleitete Tabellen
Methodenaufrufe (die Tabellen liefern)
Unterabfragen (abgeleitete Tabellen)
9
9
9
–
9
Beispiele: Kollektionsabgeleitete bzw. mittels Unterabfragen abgeleitete
Tabellen
SELECT *
FROM Manager m, UNNEST(m.Leitet) p;
SELECT *
FROM (SELECT * FROM Tabelle1 UNION SELECT * FROM Tabelle2);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-109
SFW-Block (4)
z
WHERE: Welche Prädikate sind erlaubt?
–
–
–
z
Prädikate über Attribute
Prädikate mit Methodenaufrufen
Prädikate mit Unterabfragen
9
9
9
Beispiel:
SELECT *
FROM Manager m
WHERE Name LIKE 'T%' AND
DEREF(oid).Einkommen > Franken(50000) AND
3 < (SELECT COUNT(*)
FROM Mitarbeiter
WHERE Vorgesetzter = m.oid);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-110
Objektrelationale Anfragen (1)
z
Navigierende Anfrage
SELECT MNr, Vorgesetzter->MNr AS Boss
FROM Mitarbeiter;
WHERE Anschrift.Ort = Vorgesetzter->Anschrift.Ort;
z
Anfrage, die Methodenaufrufe enthält
SELECT DEREF(oid).Jahresgehalt
FROM Mitarbeiter;
WHERE DEREF(oid).AnzahlKinder > 3;
z
Anfrage auf flacher Extension
SELECT *
FROM ONLY(Mitarbeiter);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-111
Objektrelationale Anfragen (2)
z
Anfragen auf ausgewählten typisierten Tabelle einer Tabellenhierarchie
SELECT *
FROM Mitarbeiter EXCEPT CORRESPONDING TABLE Manager;
z
Typspezifische Anfrage
SELECT *
FROM Mitarbeiter;
WHERE DEREF(oid) IS OF (ManagerTyp);
z
Anfragen mit temporärer Typanpassung
SELECT (DEREF(oid) AS MitarbeiterTyp).Jahresgehalt AS GehaltNachMitFkt,
DEREF(oid).Jahresgehalt AS GehaltNachMngFkt
FROM Manager;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-112
Rekursive Anfragen
z
Selbstreferenzierende Tabellenausdrücke
z
Notwendig, um etwa Erreichbarkeitsfragen zu beantworten bzw.
transitive Hüllen zu berechnen
z
Grundgerüst einer rekursiven Anfrage
WITH RECURSIVE RekursiveTabelle AS
(SELECT ... FROM Tabelle WHERE ...
UNION
SELECT ... FROM Tabelle, RekursiveTabelle WHERE ...)
SELECT * FROM RekursiveTabelle;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-113
Rekursive Anfragen – Beispiel
CREATE TABLE Flug (
Abflug VARCHAR(30),
Ziel
VARCHAR(30),
Carrier VARCHAR(30),
Preis
Franken
);
Flug Abflug
Frankfurt
Zürich
Paris
Sydney
Zürich
Ziel
Sydney
London
New York
New York
Frankfurt
WITH RECURSIVE Erreichbar(Abflug, Ziel) AS (
SELECT Abflug, Ziel
Erreichbar'
FROM Flug
UNION
SELECT e.Abflug, f.Ziel
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Carrier
LH
BA
AF
TWA
SR
Abflug
Zürich
Zürich
Zürich
Zürich
Preis
1009
599
1299
1549
499
Ziel
London
Frankfurt
Sydney
New York
3-114
Rekursive Anfragen – Problem
z
Sicherheit (Endlichkeit der Rekursion)
–
z
Alle Konstrukte, die Elemente der linken Seite der Rekursion
(Resultatsmenge) ändern bzw. löschen gefährden die Sicherheit
–
z
Fixpunkt existiert, wenn die Transformation der rechten Seite der Rekursion
monoton steigend ist
Negation (Differenz), Aggregatfunktionen, skalare Funktionen
SQL gewährleistet Sicherheit nicht durch Fixpunkteigenschaft, sondern
über die Angabe von Steuerungsparametern
–
Abarbeitungsreihenfolge, Suchtiefe, Zyklenerkennung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-115
Auswertung einer rekursiven Anfragen
z
Semi-naive Auswertungsstrategie
–
–
Erreichbar ist monoton steigend ("Neue transitive Verbindungen kommen
hinzu")
Sobald sich Erreichbar nicht mehr ändert, d.h. keine neuen transitiven
Verbindungen abgeleitet werden können, ist der Fixpunkt erreicht
BEGIN
Erreichbar := FLUG;
REPEAT
Temp := Erreichbar;
Erreichbar := Erreichbar UNION (SELECT e.Abflug, f.Ziel
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug);
UNTIL Temp = Erreichbar (Fixpunkt erreicht);
END;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-116
Zyklenbehandlung durch
Einschränkung der Rekursionstiefe
Flug Abflug
Frankfurt
Zürich
Finde alle Städte, die mit maximal
Paris
einmal Umsteigen von Zürich aus
Sydney
erreichbar sind
Zürich
WITH RECURSIVE Erreichbar(Abflug, Ziel, Via) AS (
SELECT Abflug, Ziel, 0
FROM Flug
Erreichbar'
UNION
SELECT e.Abflug, f.Ziel, e.Via+1
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug AND e.Via < 1
)
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich';
Ziel
Sydney
London
New York
New York
Frankfurt
Abflug
Zürich
Zürich
Zürich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Carrier
LH
BA
AF
TWA
SR
Ziel
London
Frankfurt
Sydney
Preis
1009
599
1299
1549
499
Via
0
0
1
3-117
Einsatz von Steuerungsparameter – Beispiel
z
SQL bietet deklaratives Konstrukt, um Zyklen zu behandeln
ƒ Angabe von Attributen, die zur Zyklenerkennung dienen bzw. Zyklen notieren
ƒ Unten: In Tabelle Via werden sich Zyklen gemerkt (Wert '1')
WITH RECURSIVE Erreichbar(Abflug, Ziel) AS (
SELECT Abflug, Ziel
FROM Flug
UNION
SELECT e.Abflug, f.Ziel
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
CYCLE Ziel SET Abflug TO '1' DEFAULT '0' USING Via
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-118
Breiten- vs. Tiefensuche (1)
z
z
Art der Graphtraversierung ist entscheidend für geordnete Ergebnisausgabe
Rekursive Anfrage mittels Breitensuche benötigt keine neuen SQL-Konstrukte
ƒ Sortierung nach Pfadtiefe ergibt Ordnung nach Breitensuche
ƒ Unten: Sortierung nach Anzahl des Umsteigens
WITH RECURSIVE Erreichbar(Abflug, Ziel, Via) AS (
SELECT Abflug, Ziel, 0
Erreichbar'
FROM Flug
UNION
SELECT e.Abflug, f.Ziel, e.Via+1
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich'
ORDER BY Via, Ziel;
Abflug
Zürich
Zürich
Zürich
Zürich
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Ziel
Frankfurt
London
Sydney
New York
Via
0
0
1
2
3-119
Breiten- vs. Tiefensuche (2)
z
Rekursive Anfrage mittels Tiefensuche benötigt keine neuen SQL-Konstrukte
ƒ Sortierung nach konkatenierten Pfaden ergibt Ordnung nach Tiefensuche
ƒ Unten: Pfad wird mit jedem Umsteigen erweitert
WITH RECURSIVE Erreichbar(Abflug, Ziel, Via) AS (
SELECT Abflug, Ziel, CAST(Abflug||Ziel AS VARCHAR(2500))
FROM Flug
Erreichbar'
UNION
SELECT e.Abflug, f.Ziel, e.Via||f.Ziel
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SELECT Abflug, Ziel FROM Erreichbar WHERE Abflug = 'Zürich'
ORDER BY Via, Ziel DESC;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Abflug
Zürich
Zürich
Zürich
Zürich
Ziel
Frankfurt
Sydney
New York
London
3-120
Breiten- vs. Tiefensuche (3)
z
SQL bietet dennoch Konstrukte, um Breiten- bzw. Tiefensuche deklarativ
festzulegen
ƒ Folgende Anfrage hat denselben Effekt wie die vorige für Breitensuche
WITH RECURSIVE Erreichbar(Abflug, Ziel) AS (
SELECT Abflug, Ziel
FROM Flug
UNION
SELECT e.Abflug, f.Ziel
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SEARCH BREADTH FIRST BY Ziel SET Via
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
DEPTH statt BREADTH
liefert Tiefensuchordnung
3-121
"Sichere" Negation (Differenz) in rekursiven Anfragen
z
Negation (Differenz) ist sicher, wenn sie nur auf Tabellen angewandt wird, die
zum Anwendungszeitpunkt vollständig bekannt (berechnet) sind
WITH RECURSIVE Erreichbar(Abflug, Ziel) AS (
SELECT Abflug, Ziel
FROM Flug
UNION
SELECT e.Abflug, f.Ziel
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SELECT * FROM Erreichbar
EXCEPT
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Finde alle Städte, die von
einer Stadt aus erreichbar
sind, die nicht Zürich ist
3-122
"Sichere" Aggregation in rekursiven Anfragen
z
Aggregation ist sicher, wenn sie nur auf Tabellen angewandt wird, die zum
Anwendungszeitpunkt vollständig bekannt (berechnet) sind
WITH RECURSIVE Erreichbar(Abflug, Ziel, Preis) AS (
SELECT Abflug, Ziel, Preis
FROM Flug
UNION
SELECT e.Abflug, f.Ziel, e.Preis+f.Preis
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SELECT Abflug, Ziel, MIN(Preis)
FROM Erreichbar
GROUP BY Abflug, Ziel;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Finde jeweils die
günstigsten Verbindungen
zwischen zwei Städten
3-123
Zusammenfassung
z
SQL:1999 : Objektrelationale Erweiterung von SQL-92
–
–
–
–
–
–
Neue Basisdatentypen und Typkonstruktoren
Benutzerdefinierte Datentypen und Typhierarchien
Typisierte Tabellen und Tabellenhierarchien
Typisierte Sichten und Sichtenhierarchien
Rekursion
Neue Anfragenkonstrukte, z.B.
„ Objektattributzugriff mittels Dot-Operator
„ Dereferenzierung mittels DEREF und Pfeil-Operator
„ Zugriff auf flache Extension einer Tabelle mittels ONLY
„ Zugriff auf Typ eines Attributs bzw. Variablen mittels IS OF
„ Zugriff auf Subtyp eines Attributs mittels TREAT
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-124
Ausblick auf SQL:2003
Part 2 von SQL:2003 definiert folgende Erweiterungen
z
Basisdatentyp BIGINT
z
Typkonstruktor MULTISET
z
Generierte Spalten
z
Sequenzgeneratoren
z
Identitätsspalten
z
Tabellenwertige Funktionen
z
MERGE-Anweisung
Part 14 bietet darüber hinaus
z
Basisdatentyp XML
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-125
Multimengentypkonstruktor – MULTISET
Erzeugen eines Multimengentyps (Ungeordnete Kollektion mit variabler Länge):
Elementtyp MULTISET
Beispiel:
ALTER TABLE MitarbeiterTupeltabelle
ADD COLUMN Fehltage VARCHAR MULTISET;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-126
Operationen auf Multimengentypen (1)
Erzeugen einer Multimenge mit dem Multimengenkonstruktor (heisst genauso
wie der Multimengentypkonstruktor)
Leere Multimenge:
MULTISET[]
Mit Werten initialisierte
Multimenge:
MULTISET[17, 4, 7, 4]
Aus einer Tabelle/Anfrage
erzeugte Multimenge:
MULTISET(SELECT Gehalt
FROM MitarbeiterTupeltabelle
WHERE Gehalt > 4500)
Elementtyp wird durch den Zeilentyp der Tabelle bestimmt
–
Tabelle hat genau ein Attribut: Elementtyp = Type(A1)
–
Tabelle hat mehr als ein Attribut: Elementtyp = ROW(Type(A1), …, Type(An))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-127
Operationen auf Multimengentypen (2)
Kardinalität liefert Anzahl
der Multimengenelemente:
CARDINALITY(MULTISET[17, 4, 7, 4])
-- liefert den Wert 4
Element einer einelementigen
Multimenge extrahieren:
ELEMENT(MULTISET(17))
-- liefert den Wert 17
Erzeugen einer Menge
(Duplikateliminierung):
SET(MULTISET[17, 4, 7, 4])
-- liefert MULTISET[17, 4, 7]
Deklarativer
Elementzugriff
mittels Entschachtelung:
SELECT s
FROM MitarbeiterTupeltabelle,
UNNEST(Fehltage) s;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-128
Operationen auf Multimengentypen (3)
Duplikatstest
(IS [NOT] A SET):
MULTISET[17, 4, 7, 4] IS A SET
-- liefert FALSE
Elementinklusionstest
([NOT] MEMBER [OF]):
24 MEMBER (MULTISET[17, 4, 7, 4])
-- liefert FALSE
4 MEMBER (MULTISET[17, 4, 7, 4)])
-- liefert TRUE
Multimengeninklusionstest MULTISET[17, 4, 7, 4] SUBMULTISET MULTISET[17, 4, 7]
([NOT] SUBMULTISET [OF]): -- liefert FALSE
MULTISET[4, 7] SUBMULTISET MULTISET[17, 4, 7, 4]
-- liefert TRUE
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-129
Operationen auf Multimengentypen (4)
Vereinigung: UNION [ALL | DISTINCT]
MULTISET[17, 4, 7, 4, 4] MULTISET UNION MULTISET[4, 24, 7]
-- liefert MULTISET[17, 4, 7, 4, 4, 4, 24, 7]
MULTISET[17, 4, 7, 4, 4] MULTISET UNION DISTINCT MULTISET[4, 24, 7]
-- liefert MULTISET[17, 4, 7, 24]
Differenz: EXCEPT [ALL | DISTINCT]
MULTISET[17, 4, 7, 4, 4] MULTISET EXCEPT MULTISET[4, 24, 7]
-- liefert MULTISET[17, 4, 4]
MULTISET[17, 4, 7, 4, 4] MULTISET EXCEPT DISTINCT MULTISET[4, 24, 7]
-- liefert MULTISET[17, 4]
Durchschnitt: INTERSECT [ALL | DISTINCT]
MULTISET[17, 4, 7, 4, 4] MULTISET INTERSECT MULTISET[4, 24, 7, 4]
-- liefert MULTISET[4, 7, 4]
MULTISET(17, 4, 7, 4, 4) MULTISET INTERSECT DISTINCT MULTISET[4, 24, 7, 4]
-- liefert MULTISET[4, 7]
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-130
Operationen auf Multimengentypen (5)
Vergleich zweier Multimengen:
—
Zwei Multimengen sind vergleichbar g.d.w. ihre Elementtypen vergleichbar
sind
—
Zwei vergleichbare Multimengen A1 und A2 sind gleich (A1=A2) g.d.w. sie
exakt die gleichen Elemente mit der jeweils gleichen Kardinalität enthalten
MULTISET[17, 4, 7, 4] = MULTISET[1, 4, 7, 4]
-- liefert FALSE
MULTISET[17, 4, 7, 4] = MULTISET[17, 4, 7, 4, 4] -- liefert FALSE
MULTISET[17, 4, 7, 4] = MULTISET[4, 7, 17, 4]
-- liefert TRUE
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-131
Formale Semantik von Multimengenoperationen
OP1, OP2 seien Multimengenwerte, SOP sei UNION, EXCEPT oder INTERSECT, und
SQ sei ALL oder DISTINCT
OP1 MULTISET SOP SQ OP2
::= CASE WHEN OP1 IS NULL OR OP2 IS NULL THEN NULL
ELSE MULTISET ( SELECT T1.V FROM UNNEST (OP1) AS T1(V)
SOP SQ
SELECT T2.V FROM UNNEST (OP2) AS T2(V) )
END
SET(OP1)
::= CASE WHEN OP1 IS NULL THEN NULL
ELSE MULTISET ( SELECT DISTINCT M.E FROM UNNEST (OP1) AS M(E) )
END
OP1 IS A SET::= CARDINALITY ( OP1 ) = CARDINALITY ( SET ( OP1 ) )
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-132
Multimengenwertige Attribute
CREATE TABLE PrivatPersonen (
Name
VARCHAR(30),
Anschriften ROW(Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ
DECIMAL(5),
Ort
VARCHAR(40),
Land
VARCHAR(25)) MULTISET,
Hobbys
VARCHAR(15) MULTISET
);
Name
{Anschriften}
<Anschrift>
Strasse Nr PLZ Ort Land
{Hobbys}
Hobby
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-133
Erzeugen und Ändern von Multimengenwerten
z
Erzeugen mittels Multimengenkonstruktor
INSERT INTO PrivatPersonen
VALUES ('Billy Bär',
MULTISET[ROW('Seefeldstrasse', 31, 8008, 'Zürich', 'CH'),
ROW('Editharing', 31, 65307, 'Bad Schwalbach', 'D')],
MULTISET['Reisen', 'Lesen', 'Sport']);
z
Manipulation mittels Multimengenkonstruktor
UPDATE PrivatPersonen
SET Hobbys = MULTISET['Reisen', 'Lesen', 'Sport', 'Essen', 'Ausgehen']
WHERE Name = 'Billy Bär';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-134
Geschachtelte Anfragen auf Kollektionen (1)
z
Selektion-Selektion kombiniert
SELECT *
FROM PrivatPersonen
WHERE EXISTS(SELECT Anschrift
FROM UNNEST(Anschriften) Anschrift
WHERE Anschrift.PLZ > 40000);
–
Liefert alle Personen, die mindestens einen Wohnsitz in einer Stadt mit einer
PLZ grösser als 40000 haben
Name
–
{Anschriften}
<Anschrift>
Strasse Nr PLZ Ort
{Hobbys}
Hobby
Land
Tupel- und typerhaltende Anfrage
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-135
Geschachtelte Anfragen auf Kollektionen (2)
z
Selektion-Projektion kombiniert
SELECT *
FROM PrivatPersonen
WHERE 'Seestrasse' IN (SELECT Anschrift.Strasse
FROM UNNEST(Anschriften) Anschrift);
–
Liefert alle Personen, die mindestens einen Wohnsitz an "einer" Seestrasse
haben
Name
–
{Anschriften}
<Anschrift>
Strasse Nr PLZ Ort
{Hobbys}
Hobby
Land
Tupel- und typerhaltende Anfrage
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-136
Geschachtelte Anfragen auf Kollektionen (3)
z
Projektion-Projektion kombiniert
SELECT Name, MULTISET(SELECT Anschrift.Ort
FROM UNNEST(Anschriften) Anschrift) AS Orte
FROM PrivatPersonen;
–
Liefert die Namen und alle zugehörigen Wohnorte
Name
–
–
{Orte}
<Ort>
Tupelerhaltende, aber typändernde Anfrage
Typ des Anfrageresultats
MULTISET(ROW(VARCHAR(30), MULTISET(VARCHAR(40))))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
!
3-137
Geschachtelte Anfragen auf Kollektionen (4)
z
Projektion-Selektion kombiniert
SELECT Name, MULTISET(SELECT Anschrift
FROM UNNEST(Anschriften) Anschrift
WHERE Anschrift.PLZ > 40000) AS Anschriften
FROM PrivatPersonen;
–
Liefert die Namen und alle Anschriften mit einer PLZ grösser als 40000
Name
–
{Anschriften}
<Anschrift>
Strasse Nr PLZ Ort Land
Typ des Anfrageresultats
MULTISET(ROW(VARCHAR(30),
MULTISET(ROW(VARCHAR(30), DECIMAL(4),
DECIMAL(5), VARCHAR(40),
VARCHAR(25)))))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
!
3-138
Multimengenspezifische Aggregatfunktionen
COLLECT erzeugt eine Multimenge aus den Werten einer Gruppe
SELECT Anschrift.Ort, COLLECT(Name)
FROM PrivatPersonen
GROUP BY Anschrift.Ort;
FUSION vereinigt die Multimengen einer Gruppe zu einer Multimenge
SELECT Anschrift.Ort, FUSION(Hobbys)
FROM PrivatPersonen
GROUP BY Anschrift.Ort;
INTERSECTION bildet den Schnitt aus den Multimengen einer Gruppe
SELECT Anschrift.Ort, INTERSECTION(Hobbys)
FROM PrivatPersonen
GROUP BY Anschrift.Ort;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-139
Generierte Spalten
z
Spaltenwert wird aus den Spaltenwerten berechnet
z
Definition:
–
z
Spaltenname GENERATED ALWAYS AS (Wertausdruck)
Wertausdruck darf nur folgendes als Variablen enthalten:
„ nicht-generierte Spalten derselben Zeile
„ Funktionen, die weder SQL-Anfragen noch DML-Anweisungen enthalten
Beispiel:
ALTER TABLE MitarbeiterTupeltabelle
ADD COLUMN Gesamteinkommen GENERATED ALWAYS AS (Gehalt + Bonus);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-140
Sequenzgeneratoren
z
Erzeugen Sequenzen von numerischen Werten
z
Definition:
–
–
–
–
CREATE SEQUENCE Sequenzname AS Typname
[START WITH Initialwert]
[INCREMENT BY Inkrementwert]
[NO MINVALUE | MINVALUE Minimalwert]
[NO MAXVALUE | MAXVALUE Maximalwert]
[NO CYCLE | CYCLE]
Typ muss ein numerischer Basisdatentyp sein
Angabe der Sequenzoptionen erfolgt reihenfolgeunabhängig
Ist der Inkrementwert negativ, erfolgt eine Dekrementierung
Cycle-Klausel bestimmt über den nächsten Schritt, wenn beim Inkrementieren
der Maximalwert (bzw. beim Dekrementieren der Minimalwert) erreicht wird
„ NO CYCLE beendet Generierung mit einer Ausnahmebedingung
„ CYCLE setzt die Generierung mit dem Minimalwert fort
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-141
Sequenzgeneratoren – Beispiel
CREATE SEQUENCE MitarbeiterID AS BIGINT
START WITH 1000000
INCREMENT BY 2
MINVALUE 1000000
MAXVALUE 9999999
CYCLE;
Zugriff auf den nächsten Sequenzwert mit
NEXT VALUE FOR Sequenzname
zum Beispiel in einer INSERT-Anweisung:
INSERT INTO MitarbeiterTupeltabelle (MNr, Name)
VALUES (NEXT VALUE FOR MitarbeiterID, 'Harry');
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-142
Identitätsspalten
z
Sind Spalten, deren Werte die Hilfe eines impliziten Sequenzgenerators
generiert werden
–
z
Definition:
–
–
z
Werte sind immer definiert und eindeutig
Spaltenname GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY
[(Sequenzoptionen)]
ALWAYS schliesst Update der Spalte aus
BY DEFAULT erlaubt Inserts und Updates
Identitätsspalten sind nicht mit OID-Spalten zu verwechseln:
Identitätsspalte
Werte
eindeutig, änderbar
Mittels Referenzspalten referenzierbar? Nein!
Erlaubt in Tupeltabellen?
Ja, maximal einmal!
Erlaubt in typisierten Tabellen?
Ja, maximal einmal!
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
OID-Spalte
eindeutig, unveränderbar
Ja!
Nein!
Muss genau einmal!
3-143
Tabellenwertige Funktionen
z
liefern eine Tabelle zurück
z
Definition des Rückgabetyps:
RETURNS TABLE (Spaltenliste)
z
Beispiel:
CREATE FUNCTION ReicheMitarbeiter (f Franken)
RETURNS TABLE (Name VARCHAR(30), Gehalt Franken)
RETURN (SELECT Name, Gehalt FROM Mitarbeiter WHERE Gehalt > f);
z
Einsatz einer tabellenwertigen Funktion in Anfragen:
SELECT *
FROM TABLE(ReicheMitarbeiter(Franken(10000)));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-144
SQL:2003 – Datenmodell
SET
MULTISET
OBJECT
ROW
MULTISET
ARRAY
REF
Einstiegspunkte in die Datenbank:
Typisierte Tabelle:
SET(OBJECT(...))
Untypisierte Tabelle:
MULTISET(ROW(...))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
Basisdatentyp
Subtypbeziehung
Untermengenbeziehung
3-145
SQL:2003 – Zusammenfassung
z
Im wesentlichen eine Konsolidierung und Bereinigung von SQL:1999
(in dieser Vorlesung nicht weiter vertieft)
z
Im Vergleich zu SQL:1999 bietet SQL:2003 wenig neue Konstrukte
z
Erfreulich: Einschränkung, dass Kollektionen (Arrays) nicht geschachtelt
werden dürfen, aufgehoben
–
z
Kollektionstypkonstruktoren ARRAY und MULTISET sind orthogonal, d.h.
beliebig kombinierbar
Kollektionstypkonstruktoren SET und LIST weiterhin nicht berücksichtigt
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2003/2004 (Dr. Can Türker)
3-146