Kap. 7: Objektrelationale Datenbanken und SQL:1999 ORDBMS im

Kap. 7: Objektrelationale Datenbanken und SQL:1999
•
Bei ODMG spricht man von revolutionärer Abkehr von RDBMS, hat aber im
Laufe der Zeit gesehen, dass eine grössere Unabhängigkeit von
Programmiersprachen vorteilhaft ist.
•
Bei ORDBMS ist die evolutionäre Erweiterung bestehender relationaler
Denkweise im Vordergrund. Man hat erkannt, dass Grundkonstrukte
semantischer Datenmodelle und (abstrakte) Benutzerdatentypen nötig sind.
Letzteres motiviert auch die Entwicklung einer (4GL-) Sprache für Methodenund Anwendungsentwicklung.
•
Häufig wird die Gleichung “ORDBMS = RDBMS + OODBMS” gesehen. Dies
drückt die Verwendung von Prinzipien aus.
•
SQL:1999 (oft auch SQL-99 oder SQL3 genannt):
– Aktueller DB-Standard
– Ziel: Vorteile von OO-Konzepten einführen, ohne bewährte SQL-Konzepte
aufzugeben
ORDB und SQL:1999-1
IS-K2002
ORDBMS im Überblick
Mächtigkeit
der Operationen
Relationales
Modell
Dateien
Komplexe Objekte/
Geschachtelte Relationen
Hierarchisches
Modell
Objekt-Relationales
Modell
NetzwerkModell
Semantisches
Datenmodell
Strukturelle Komplexität
IS-K2002
ORDB und SQL:1999-2
Charakterisierung von ORDBMS
Hauptmerkmale:
• Erweiterung der Basisdatentypen
– Attributtypen können benutzerdefiniert sein
• Komplexe Objekte durch Typkonstruktoren
– Tupel, Set, Multiset, List, Array, …
• Objektreferenzen
• Typvererbung und Subtabellen
• Methoden, Funktionen und Trigger
ORDB und SQL:1999-3
IS-K2002
ORDB - Tupeltabellen vs. Objekttabellen
Mitarbeiter (Tupeltabelle
(Tupeltabelle mit nichtnicht-atomaren Attributen)
Name
Adresse <Strasse, PLZ, Ort>
Hobbies
Bewerbun
g
Dienstwagen
Foto
VARCHA
R
ROW(VARCHAR, INT, VARCHAR)
SET(CHAR)
Object(...)
REF(Auto) SCOPE Wagen
BLOB
'Jim'
ROW('Seeblick', 8008, 'Zürich')
SET('Kino', 'Sport')
Object(...)
X'120332828474292'
X'...'
atomare
Spalte
tupelwertige
Spalte
referenzwertige
Spalte
LOBLOBSpalte
mengenwertige objektwertige
Spalte
Spalte
strukturierter Typ (Objekttyp)
Wagen (Objekttabelle)
OID
Hersteller
Fotos
Farbe
PS
Motor
VARCHAR
VARCHAR
SET(BLOB)
VARCHAR
INTEGER
Object(...)
X'120332828474292'
'Aston Martin'
SET(X'...', X'...')
'Silber'
324
Object(...)
OIDOID-Spalte
•
•
•
•
strukturierter Wert (Objektwert)
Objekttabelle basiert auf einem Objekttyp
Zeilen sind Objekte (Methoden können darauf aufgerufen werden)
Zeilen haben eine OID und sind damit mittels Referenzspalten referenzierbar
Tabellenhierarchien sind möglich
IS-K2002
ORDB und SQL:1999-4
Hintergrundinformation zu SQL:1999
SQL/89 mit Integrity Enhancement
120 Seiten
SQL/92
579 Seiten
Entry Level
Intermediate Level
Full Level
SQL:1999
> 3000 Seiten
SQL Foundation
SQL Call Level Interface
SQL Persistent Stored Modules
SQL Language Binding + Object Language Binding
SQL Java Types and Routines
SQL Multimedia and Application (Full Text, Spatial, Still Image)
SQL:2003
SQL XML-related Specifications
ORDB und SQL:1999-5
IS-K2002
SQL:1999 - Übersicht (1)
•
Typsystemerweiterung: Erweiterbarkeit, OO-Konzepte
– Typkonstruktoren für strukturierte Attribute (komplexe Werte):
... damit einige Konstrukte
• Tupeltypen (ROW Types)
des “erweiterten NF2• Kollektionstypen
Modells” enthalten
– Benutzerdefinierte Datentypen (UDT)
– Referenzdatentyp (REF)
– Subtypen und Subtabellen
... damit Konstrukte von
OODBMS enthalten
•
Neue vordefinierte Datentypen
– Boolean, Binary Large Objects, Character Large Objects
•
Rekursion (Berechnen der transitiven Hülle)
IS-K2002
ORDB und SQL:1999-6
SQL:1999- Übersicht (2)
•
Call Level Interface (CLI) → dynamisches SQL
•
Erweiterung von SQL zu voller Programmiersprache
(Procedural Extensions, SQL/PSM "Persistent Stored Modules")
– Module, Prozeduren, Funktionen im Server speichern / ausführen
– volle Berechenbarkeit
•
Primitive für Transaktionsmodelle
– Chained Transactions, Savepoints, etc.
•
Trigger: Integrität und Unterstützung aktiver Datenbanken
– mehrere Trigger pro Tabelle
– mehrere Statements als Aktion definierbar
•
Roles für verbesserte Autorisierung
IS-K2002
ORDB und SQL:1999-7
Die wichtigsten Typen in SQL:1999
•
Ausgangspunkt: Tabellendefinition (CREATE TABLE):
– Zeilen können wie folgt definiert werden
• implizit (wie bisher)
• explizit durch Row-Typdefinition
– Spalten können folgende Typen besitzen:
• vordefinierte Standarddatentypen (wie bisher)
• konstruierte Typen
- Typkonstruktoren: ROW / REF / ARRAY
- MULTISET (in SQL:2003)
• benutzerdefinierte Typen (UDT)
- aufbauend auf vorderfinierten, konstruierten, und/oder bereits
existierenden benutzerdefinierten Typen
•
•
Tabellen sind die einzigen Einstiegspunkte in die Datenbank
Instanzen von Typen, die innerhalb einer Tabelle vorkommen, sind automatisch
persistent
IS-K2002
ORDB und SQL:1999-8
Definition von strukturierten Typen (1)
• Strukturierte Typen als eine Form von benutzerdefinierten Typen
CREATE TYPE AdressTyp AS (
Strasse VARCHAR(30),
Ort
VARCHAR(40),
PLZ
INTEGER,
Land
VARCHAR(25)
) NOT FINAL;
vordefinierte Typen
benutzerdefinierter Typ
konstruierte Typen
CREATE TYPE PersonTyp AS (
Name
VARCHAR(30),
Anschrift
AdressTyp,
Ehepartner REF(PersonTyp),
Kinder
REF(PersonTyp) ARRAY[10]
) NOT FINAL;
Subtypen können vorhanden sein
ORDB und SQL:1999-9
IS-K2002
Definition von strukturierten Typen (2)
•
Objektverhalten kann in Methoden kodiert werden
•
Methoden sind Funktionen, die sich auf genau einen strukturierten Typen
beziehen
•
Beispiel für eine Methodendeklaration:
CREATE TYPE PersonTyp (
Name
VARCHAR(30),
Anschrift
AdressTyp,
Ehepartner REF(PersonTyp),
Kinder
REF(PersonTyp) ARRAY[10]
) NOT FINAL
METHOD AnzahlKinder() RETURNS INTEGER;
•
•
•
Realisierung über prozedurale SQL-Spracherweiterungen
Overloading / Late Binding: Methodennamen können überladen werden
Overriding: Methoden können in Subtypen überschrieben werden
– dynamisches Binden (Auswahl der Implementierung) zur Laufzeit
IS-K2002
ORDB und SQL:1999-10
Einkapselung von strukturierten Typen
•
Attribute gleich behandelt wie Methoden (Funktionen)
•
vollständige Einkapselung
– Attribute nur über Methoden zugreifbar
•
für jedes Attribut werden implizit Observer- und Mutator-Methoden
generiert
Observer: FUNCTION Strasse(AdressTyp)
FUNCTION Ort(AdressTyp)
FUNCTION Plz(AdressTyp)
FUNCTION Land(AdressTyp)
Mutator:
RETURNS VARCHAR(30);
RETURNS VARCHAR(40);
RETURNS INTEGER;
RETURNS VARCHAR(25);
FUNCTION Strasse(AdressTyp, VARCHAR(30))
FUNCTION Ort(AdressTyp, VARCHAR(40))
FUNCTION Plz(AdressTyp, INTEGER)
FUNCTION Land(AdressTyp, VARCHAR(25))
RETURNS AdressTyp;
RETURNS AdressTyp;
RETURNS AdressTyp;
RETURNS AdressTyp;
ORDB und SQL:1999-11
IS-K2002
Attributzugriff bei Instanzen strukturierter Typen
•
Zugriff über Funktionsaufrufe oder Dot-Operator (syntaktischer Zucker)
– X.Attributname
entspricht Attributname(X)
– SET X.Attributname = Wert entspricht Attributname(X, Wert)
•
Pfadausdrücke: nacheinander Anwendung des Dot-Operators
– navigierende Zugriffe
•
Beispiel:
BEGIN
...
DECLARE p PersonTyp;
SET p = PersonTyp();
SET p.Name = ‘Luke Skywalker’;
SET p.Anschrift.Ort = ‘Hollywood’;
...
END;
IS-K2002
ORDB und SQL:1999-12
Erzeugen von Instanzen strukturierter Typen
•
unter Verwendung des Default-Konstruktors
•
Initialisierung mittels
– Mutator-Methoden
– redefiniertem Konstruktor (kann beliebig überladen werden)
•
Beispiel: Erzeugen einer PersonTyp-Instanz, bei der das Attribut Name gesetzt
wird
CREATE FUNCTION PersonTyp (n VARCHAR(30))
RETURNS PersonTyp
RETURN PersonTyp().Name(n);
Pfadausdruck mit Aufruf des Default-Konstruktors
ORDB und SQL:1999-13
IS-K2002
Definition von typisierten Tabellen (Objekttabellen)
•
Typisierte Tabellen basieren auf einem strukturierten Typen
•
Instanzen (Zeilen) sind Objekte dieses Typs
•
Beispiel:
CREATE TABLE Personen OF PersonTyp
(REF IS oid SYSTEM GENERATED,
Ehepartner WITH OPTIONS SCOPE Personen,
Kinder WITH OPTIONS SCOPE Personen);
•
Referenzklausel (hier mit SYSTEM GENERATED) muss für jede Wurzeltabelle
angegeben werden
• Bestimmt die Art der Referenzgenerierung (OID-Erzeugung)
•
Scope-Klausel muss für jedes Referenzattribut definiert werden
• Bestimmt typisierte Tabelle, auf dessen Instanzen referenziert wird
IS-K2002
ORDB und SQL:1999-14
Definition von Tupeltypen (ROW Types)
•
Tupeltyp besteht aus einer Sequenz von Attribut/Datentyp-Paaren
•
eingebettet innerhalb von Typ- oder Tabellendefinitionen
•
Beispiel:
CREATE TABLE PersonenTupelTabelle (
Name
ROW( Vorname VARCHAR(15),
Nachname VARCHAR(15)),
Anschrift ROW( Strasse
VARCHAR(30),
Ort
VARCHAR(40),
PLZ
INTEGER,
Land
VARCHAR(25))
);
ORDB und SQL:1999-15
IS-K2002
Definition von Distinct-Typen
•
Zweite Form von benutzerdefinierten Typen
•
Erstellt Kopie eines existierenden Datentyps unter einem neuen Namen
– Wiederverwendung
– Distinct-Typen werden unterschieden (strenge Typisierung)
CREATE TYPE Franken AS NUMERIC(9,2);
CREATE TYPE Euro AS NUMERIC(9,2);
CREATE TABLE CHBank(Knr INTEGER, Stand Franken);
CREATE TABLE EuroBank(Knr INTEGER, Stand Euro);
SELECT DISTINCT c.Knr, c.Stand
FROM CHBank c, EuroBank e
WHERE c.Stand > e.Stand;
Fehler: Schweizer Franken und Euro
sind nicht vergleichbar
… WHERE c.Stand > Franken(CAST(e.Stand AS NUMERIC(9,2)));
IS-K2002
Korrekt:
Casting
ORDB und SQL:1999-16
Typhierarchie
•
Vererbung von Attributen und Methoden
– Sub-/Supertyp-Beziehung
•
Beispiel: Mitarbeiter als spezielle Personen
CREATE TYPE MitarbeiterTyp UNDER PersonTyp AS (
PNr
INTEGER,
Bewerbung
CLOB(50K),
Bild
BLOB(5M),
Vorgesetzter REF(MitarbeiterTyp),
Projekte
REF(ProjektTyp) ARRAY[10],
Gehalt
Franken
) NOT FINAL
METHOD Gehaltserhöhung() RETURNS Franken;
•
keine Mehrfachverbung (maximal ein direkter Supertyp)
IS-K2002
ORDB und SQL:1999-17
Beispiel: Definition von SQL-Methoden
CREATE METHOD AnzahlKinder()
RETURNS INTEGER
FOR PersonTyp
RETURN CARDINALITY(SELF.Kinder);
CREATE METHOD Gehaltserhöhung
RETURNS Franken
FOR MitarbeiterTyp
BEGIN
DECLARE altesGehalt Franken;
SET altesGehalt = SELF.Gehalt;
IF (SELF.AnzahlKinder < 3) OR (SELF.AnzahlProjekte < 2) THEN
SET SELF.Gehalt = 1,03 * altesGehalt;
ELSE
SET SELF.Gehalt = 1,05 * altesGehalt;
ENDIF
IF (SELF.Gehalt > 500000) THEN SIGNAL 'Gehalt zu hoch';
RETURN SELF.Gehalt;
END;
IS-K2002
ORDB und SQL:1999-18
Erweiterung von SQL zur Programmiersprache
compound statement
BEGIN … END;
SQL variable declaration
DECLARE Variablenname Typname;
if statement
IF Prädikat THEN … ELSE …;
case statement
CASE subject (nachr)
WHEN ‘SQL’ THEN …
WHEN … END CASE;
loop statement
LOOP … END LOOP;
while statement
WHILE Prädikat DO … END WHILE;
repeat statement
REPEAT … UNTIL Prädikat END REPEAT;
for statement
FOR Schleifenvariable AS … DO … END FOR;
return statement
RETURN Rückgabewert;
call statement
CALL Prozedurname(Parameter);
assignment statement
SET Variable = Wert;
signal / resignal statement SIGNAL Ausnahmebedingung;
ORDB und SQL:1999-19
IS-K2002
Tabellenhierarchie
•
Extensionale Teilmengen-Beziehung zwischen Sub-/Supertabelle
– Alle Instanzen einer Subtabelle sind auch Instanzen der zugehörigen
Supertabelle(n)
CREATE TABLE Mitarbeiter OF MitarbeiterTyp
UNDER Personen
(Vorgesetzter WITH OPTIONS SCOPE Mitarbeiter,
Projekte WITH OPTIONS SCOPE Projekt);
•
IS-K2002
Typ der Subtabelle muss ein direkter Subtyp des Typs der Supertabelle
ORDB und SQL:1999-20
Beispiel - typisierte Tabellen und Subtabellen
Personen OF PersonTyp
OID
Name
Anschrift
Ehepartner
Kinder
VARCHAR
VARCHAR
AdressTyp
REF(PersonTyp)
REF(PersonTyp) ARRAY[10]
UNDER
Mitarbeiter OF MitarbeiterTyp
OID
Name
Anschrift
Ehepartner
Kinder
PNr
Bewerbung
.
.
.
Gehalt
VARCH
AR
VARCH
AR
AdressT
yp
REF(PersonT
yp)
REF(PersonTyp)
ARRAY[10]
INTEG
ER
CLOB
.
.
.
Franke
n
ORDB und SQL:1999-21
IS-K2002
Einfügungen / Löschungen in Tabellenhierarchien
•
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';
IS-K2002
ORDB und SQL:1999-22
Einfügeoperationen
INSERT INTO Personen VALUES ('Bernstein',
AdressTyp().Strasse('Editharing 31').Ort('Zürich').PLZ(8092).Land('CH'),
NULL, NULL);
erzeugt eine Person
INSERT INTO Mitarbeiter VALUES ('Joe',
AdressTyp().Strasse('Kreuzstrasse 15').Ort('Zürich').PLZ(8008).Land('CH'),
NULL, NULL, 1234, NULL, NULL, NULL, Franken(5000));
erzeugt einen Mitarbeiter
INSERT INTO Mitarbeiter VALUES ('Jim',
AdressTyp().Strasse('Helenastrasse 67').Ort('Zürich').PLZ(8008).Land('CH'),
NULL, NULL, 6789, NULL, NULL,
(SELECT oid FROM Mitarbeiter WHERE Name = 'Joe'), Franken(3500));
erzeugt einen Mitarbeiter
Setzen einer Referenz
IS-K2002
ORDB und SQL:1999-23
Einfache Beispielanfragen (1)
SELECT * FROM Personen;
liefert alle Personen, auch die Mitarbeiter
SELECT * FROM Mitarbeiter;
liefert die vollständigen Tupel der Mitarbeiter
SELECT * FROM ONLY(Personen);
liefert alle Personen, die keine speziellen Personen
(z.B. Mitarbeiter) sind
IS-K2002
ORDB und SQL:1999-24
Einfache Beispielanfragen (2)
Dereferenzierung über Pfeil-Operator ->
SELECT *
FROM Mitarbeiter
WHERE Vorgesetzter->Name = 'Joe';
Dereferenzierung mit anschliessendem Komponentenzugriff
SELECT *
FROM Mitarbeiter
WHERE Vorgesetzter->Anschrift.PLZ = 8008;
Verwendung von CLOB-Prädikaten
SELECT Name
FROM Mitarbeiter
WHERE POSITION('Diplom' IN Lebenslauf) > 0;
ORDB und SQL:1999-25
IS-K2002
Referenzen & Deferenzierung
•
Dereferenzierung mittels DEREF-Operator
SELECT DEREF(Ehepartner)
FROM Mitarbeiter;
•
Zuweisen von Referenzen in Updates
UPDATE Projekt
SET
Leiter = (SELECT oid
FROM Mitarbeiter
WHERE Name = ‘Gates’)
WHERE AnzahlMitarbeiter > 10;
IS-K2002
ORDB und SQL:1999-26
Methoden-/Funktionsaufrufe
•
in der SELECT-Klausel
SELECT Anzahl(Projekte)
FROM Mitarbeiter;
•
in der FROM-Klausel
SELECT Name
FROM GuteMitarbeiter();
•
liefert einen Wert
liefert eine Menge von Mitarbeitertupeln
SQL:1999 unterstützt diese Variante nicht!
in der WHERE-Klausel
SELECT Name
FROM Mitarbeiter
WHERE Grossverdiener(Gehalt);
liefert TRUE oder FALSE
ORDB und SQL:1999-27
IS-K2002
Rekursive Anfragen
CREATE TABLE Flüge (
Abflug
VARCHAR(30),
Ziel
VARCHAR(30),
Carrier
VARCHAR(30),
Preis
Franken
);
Abflug
Zürich
Zürich
Frankfurt
Sydney
Paris
WITH RECURSIVE Erreichbar(Abflug, Ziel) AS (
SELECT Abflug, Ziel
FROM Flüge
UNION
SELECT e.Abflug, f.Ziel
FROM Erreichbar e, Flüge f
WHERE e.Ziel = f.Abflug
)
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich';
IS-K2002
Ziel
Frankfurt
London
Sydney
New York
New York
Abflug
Zürich
Zürich
Zürich
Zürich
Carrier
SR
BA
LH
TWA
AF
Preis
499
599
1099
1549
1299
Ziel
Frankfurt
London
Sydney
New York
ORDB und SQL:1999-28
Diskussion: Kollektionstypen und Typkonversion
CREATE TABLE Mitarbeiter (
PNr
INTEGER,
Name
VARCHAR(30),
Anschrift
AdressTyp,
Vorgesetzter
INTEGER,
Projekte
SET (INTEGER),
Fachgebiete
LIST (VARCHAR (20))
);
SET/ LIST / MULTISET sind a
priori keine TABLEs. Sie
werden mit UNNEST zu
solchen gemacht zum Zwecke
der Anfragen
SELECT m.Name
FROM Mitarbeiter m
WHERE ‘Datenbanken’ IN (SELECT * FROM UNNEST(m.Fachgebiete));
ORDB und SQL:1999-29
IS-K2002
Diskussion: TABLE innerhalb TABLE
CREATE TABLE Abteilungen (AbtNr
INTEGER PRIMARY KEY,
Manager INTEGER REFERENCES Mitarbeiter,
Projekte TABLE ( ProjNr
INTEGER,
ProjName CHAR (30),
Budget
DECIMAL (10, 2))
);
Finde die Nummer, den Gesamtetat, und den durchnittlichen Lohn jeder Abteilung:
SELECT a.AbtNr,
TABLE(SELECT
FROM
TABLE (SELECT
FROM
WHERE
FROM Abteilungen a;
IS-K2002
sum (Budget)
a.Projekte) AS Gesamtetat,
avg (Gehalt)
Mitarbeiter m
m.AbtNr = a.AbtNr ) AS Durchschnittsgehalt
ORDB und SQL:1999-30
Abschliessender Vergleich (1)
Konzept
RMSDM
KL-ONE
RM
NF 2
OO-Basis
ODMG
SQL:1999
9
9
Objekttypen
atomar/konkret Objekttypen
9
9
9
9
abstrakte Objekttypen
9
-
-
9
9
9
Subtypen
-
-
-
9
9
9
9
9
-
9
9
9
(9) (2)
9
-
konstruierte Objekttypen
-
konstruierte Tupel
9
konstruierte Set
-
(1)
konstruierte List
-
-
-
-
-
9
-
konstruierte Bag
-
-
-
-
-
9
-
konstruierte Array
-
-
-
-
-
9
9
-
-
-
-
-
(9)
Surrogat
ORDB und SQL:1999-31
IS-K2002
Abschliessender Vergleich (2)
Konzept
RMSDM
KL-ONE
RM
NF 2
OO-Basis
ODMG
SQL:1999
Objektklassen
9
benutzerdefiniert
durch prädikat P
Subklassen
P
(9) (4)
9
9
(3)
9
9
(9)
9
-
-
9
-
-
-
-
9
9
9
Bemerkungen:
(1) Sets treten im RM als Relationen = Menge von (Tupel-)Instanzen auf. Dies
sind daher Typenextensionen und erfassen damit benutzerdefinierte Klassen.
(2) Sets treten nicht explizit, sondern als mengenwertige Funktionen auf.
(3) Objektklassen über CLASS / EXTENT kombination unter INTERFACE
(4) In Verbindung mit Rollenrestriktion
IS-K2002
ORDB und SQL:1999-32
Literatur
•
ISO/ANSI/IEC 9075-2: Information Technology - Database Languages - SQL - Part
2: Foundation (SQL/Foundation), September 1999
•
J. Melton: Advanced SQL - Understanding the Object-Relational and Other
Advanced Features,
Features Morgan Kaufmann, 2002
•
A. Geppert: Objektrelationale und objektorientierte Datenbanksysteme,
Datenbanksysteme
dpunkt.Verlag, 2002
•
G. Saake, I. Schmitt, C. Türker: Objektdatenbanken - Konzepte, Sprachen,
Architekturen,
Architekturen International Thomson Publishing, 1997
•
A. Heuer: Objektorientierte Datenbanken - Konzepte, Modelle, Standards und
Systeme,
Systeme Addison-Wesley, 2. Auflage, 1997
IS-K2002
ORDB und SQL:1999-33