SFW-Block (1) SFW

SFW-Block (1)
l Grundgerüst einer SQL-Anfrage: SFW-Block
SELECT
FROM
[WHERE
[GROUP BY
[HAVING
Projektionsliste
Tabellenausdruck
Prädikat]
Attributliste]
Gruppenprädikat]
l Was ist das Ergebnis einer Anfrage in SQL-99?
–
–
–
ü
Tupeltabelle
ü
Objekttabelle
Wert bzw. Kollektion — (In SQL:200n soll dies möglich sein)
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-86
SFW-Block (2)
l SELECT: Was darf in der Projektionsliste stehen?
–
–
–
Attribute (auch abgeleitete, berechnete)
Methodenaufrufe
Unterabfragen
ü
ü
(ü)
Nur skalare Unterabfragen,
die genau einen Wert liefern!
l Beispiel:
SELECT m.Name, 12*m.Einkommen, (SELECT COUNT(*)
FROM Mitarbeiter
WHERE Vorgesetzter = REF(m))
FROM Manager m;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-87
SFW-Block (3)
l FROM: Welche Tabellenausdrücke sind erlaubt?
–
–
–
–
–
Tupeltabellen
Objekttabellen (auch flache Extensionen)
Kollektionsabgeleitete Tabellen
Methodenaufrufe (die Tabellen liefern)
Unterabfragen (abgeleitete Tabellen)
ü
ü
ü
—
ü
l Beispiele: Kollektionsabgeleitete bzw. mittels Unterabfragen abgeleitete Tabellen
SELECT *
FROM Manager m, UNNEST(m.Leitet) p;
SELECT *
FROM (SELECT * FROM (Tabelle1 UNION Tabelle2) INTERSECT Tabelle3);
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-88
SFW-Block (4)
l WHERE: Welche Prädikate sind erlaubt?
–
–
–
Prädikate über Attribute
Prädikate mit Methodenaufrufen
Prädikate mit Unterabfragen
ü
ü
ü
l Beispiel:
SELECT *
FROM Manager m
WHERE Name LIKE 'T%' AND Einkommen > 50000 AND
3 < (SELECT COUNT(*)
FROM Mitarbeiter
WHERE Vorgesetzter = REF(m));
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-89
Rekursive Anfragen
l Selbstreferenzierende Tabellenausdrücke
l Notwendig, um etwa Erreichbarkeitsfragen zu beantworten bzw. transitive
Hüllen zu berechnen
l 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 2001/2002 (Dr. C. Türker)
4-90
Rekursive Anfragen - Beispiel
CREATE TABLE Flug (
Abflug
VARCHAR(30),
Ziel
VARCHAR(30),
Carrier
VARCHAR(30),
Preis
Franken
);
Flug
Abflug
Ziel
Carrier Preis
Frankfurt Sydney
LH 1009
Zürich
London
BA 599
Paris New York AF 1299
Sydney New York TWA 1549
Zürich Frankfurt SR 499
WITH RECURSIVE Erreichbar(Abflug, Ziel) AS (
SELECT Abflug, Ziel
FROM Flug
Erreichbar'
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 2001/2002 (Dr. C. Türker)
Abflug
Ziel
Zürich London
Zürich Frankfurt
Zürich Sydney
Zürich New York
4-91
Rekursive Anfragen - Problem
l Sicherheit (Endlichkeit der Rekursion)
–
Fixpunkt existiert, wenn die Transformation der rechten Seite der Rekursion
monoton steigend ist
l Alle Konstrukte, die Elemente der linken Seite der Rekursion
(Resultatsmenge) ändern bzw. löschen gefährden die Sicherheit
–
Negation (Differenz), Aggregatfunktionen, skalare Funktionen
l SQL gewährleistet Sicherheit nicht durch Fixpunkteigenschaft, sondern
über die Angabe von Steuerungsparametern
–
Abarbeitungsreihenfolge, Suchtiefe, Zyklenerkennung
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-92
Auswertung einer rekursiven Anfragen
l 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 2001/2002 (Dr. C. Türker)
4-93
Zyklenbehandlung durch Einschränkung der Rekursionstiefe
Flug
l Finde alle Städte, die mit maximal einmal
Umsteigen von Zürich aus erreichbar sind
Abflug
Ziel
Carrier Preis
Frankfurt Sydney
LH 1009
Zürich
London
BA 599
Paris New York AF 1299
Sydney New York TWA 1549
Zürich Frankfurt SR 499
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';
Abflug
Ziel
Via
Zürich London
0
Zürich Frankfurt 0
Zürich Sydney
1
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-94
Einsatz von Steuerungsparameter - Beispiel
l 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 2001/2002 (Dr. C. Türker)
4-95
Breiten- vs. Tiefensuche (1)
l Art der Graphtraversierung ist entscheidend für geordnete Ergebnisausgabe
l 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
FROM Flug
Erreichbar'
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
Ziel
Via
Zürich Frankfurt 0
Zürich London
0
Zürich Sydney
1
Zürich New York 2
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-96
Breiten- vs. Tiefensuche (2)
l 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' Abflug
UNION
Zürich
SELECT e.Abflug, f.Ziel, e.Via||f.Ziel
Zürich
FROM Erreichbar e, Flug f
Zürich
WHERE e.Ziel = f.Abflug
Zürich
)
SELECT Abflug, Ziel FROM Erreichbar WHERE Abflug = 'Zürich'
ORDER BY Via, Ziel DESC;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
Ziel
Frankfurt
Sydney
New York
London
4-97
Breiten- vs. Tiefensuche (3)
l 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
)
DEPTH statt BREADTH
SEARCH BREADTH FIRST BY Ziel SET Via
SELECT * FROM Erreichbar WHERE Abflug = 'Zürich' liefert Tiefensuchordnung
ORDER BY Via;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-98
"Sichere" Negation (Differenz) in rekursiven Anfragen
l 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 (
Finde alle Städte, die von
SELECT Abflug, Ziel
einer Stadt erreichbar
FROM Flug
sind, die nicht Zürich ist
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 2001/2002 (Dr. C. Türker)
4-99
"Sichere" Aggregation in rekursiven Anfragen
l Aggregation ist sicher, wenn sie nur auf Tabellen angewandt wird, die zum
Anwendungszeitpunkt vollständig bekannt (berechnet) sind
WITH RECURSIVE Erreichbar(Abflug, Ziel, Kosten) AS ( Finde jeweils die
SELECT Abflug, Ziel, Kosten
günstigsten Verbindungen
FROM Flug
zwischen zwei Städten
UNION
SELECT e.Abflug, f.Ziel, e.Kosten+f.Kosten
FROM Erreichbar e, Flug f
WHERE e.Ziel = f.Abflug
)
SELECT Abflug, Ziel, MIN(Kosten)
FROM Erreichbar
GROUP BY e.Abflug, f.Ziel;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-100
Ausblick - Weitere Kollektionstypen (1)
l Kollektionentypen SET, MULTISET, LIST für SQL4 geplant
CREATE TABLE PrivatPersonen (
Name
VARCHAR(30),
Anschriften
LIST(ROW(Strasse VARCHAR(30),
Nr
DECIMAL(4),
PLZ DECIMAL(5),
Ort VARCHAR(40),
Land VARCHAR(25))),
Hobbies
SET(VARCHAR(15))
);
Name
[Anschriften]
{Hobbies}
<Anschrift>
Hobby
Strasse Nr PLZ Ort Land
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-101
Ausblick - Weitere Kollektionstypen (2)
l Verwendung von Kollektionskonstruktoren
INSERT INTO PrivatPersonen
VALUES ('Billy Bär',
LIST(ROW('Seefeldstrasse', 31, 8008, 'Zürich', 'CH'),
ROW('Editharing', 31, 65307, 'Bad Schwalbach', 'D')),
SET('Reisen', 'Lesen', 'Sport'));
l Denkbare Möglichkeit zur Manipulation einer Kollektion
UPDATE PrivatPersonen
SET Hobbies = SET('Reisen', 'Lesen', 'Sport', 'Essen', 'Ausgehen')
WHERE Name = 'Billy Bär';
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-102
Ausblick - Geschachtelte Anfragen auf Kollektionen (1)
l 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]
{Hobbies}
<Anschrift>
Hobby
Strasse Nr PLZ Ort Land
Tupel- und typerhaltende Anfrage
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-103
Ausblick - Geschachtelte Anfragen auf Kollektionen (2)
l 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]
{Hobbies}
<Anschrift>
Hobby
Strasse Nr PLZ Ort Land
Tupel- und typerhaltende Anfrage
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-104
Ausblick - Geschachtelte Anfragen auf Kollektionen (3)
l Projektion-Projektion kombiniert
SELECT Name, (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(ROW(VARCHAR(40)))))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
!?!
4-105
Ausblick - Geschachtelte Anfragen auf Kollektionen (4)
l Projektion-Selektion kombiniert
SELECT Name, (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 4000
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 2001/2002 (Dr. C. Türker)
4-106
Ausblick - Geschachtelte Anfragen auf Kollektionen (5)
l Kollektionsabgeleitete Tabellen in der FROM-Klausel
§ Transformation einer Kollektion in eine Tabelle für Anfragezwecke
SELECT Name, Anschrift.Ort, Anschrift.Land
FROM PrivatPersonen, UNNEST(Anschriften) Anschrift
WHERE 'Reisen' IN (SELECT * FROM UNNEST(Hobbies)) AND
CARDINALITY(Anschriften) > 1;
l Kollektionsabgeleitete Tabellen, die Sortierung bleibehalten
SELECT Name, Anschrift.Ort
FROM PrivatPersonen, UNNEST(Anschriften) Anschrift WITH ORDINALITY
ORDER BY Anschriften.Ort;
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-107
Ausblick - Kollektion(selemente) als Anfrageresultate
l Kollektion als Anfrageresultate
SELECT ITEM Name
FROM Personen;
Liefert eine Multimenge vom Typ
MULTISET(VARCHAR(30))
SELECT DISTINCT ITEM ROW Name
FROM Personen;
Liefert eine Menge vom Typ
SET(ROW(VARCHAR(30)))
l Element einer einelementigen Multimenge
THE(SELECT Hobbies
FROM Personen
WHERE Name = 'Billy');
Liefert ein Element vom Typ
LIST(VARCHAR(20))
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-108
Zusammenfassung
l SQL-99 : 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.
n Objektattributzugriff mittels Dot-Operator
n Dereferenzierung mittels DEREF und Pfeil-Operator
n Zugriff auf flache Extension einer Tabelle mittels ONLY
n Zugriff auf Typ eines Attributs bzw. Variablen mittels IS OF
n Zugriff auf Subtyp eines Attributs mittels TREAT
Vorlesung "Objektrelationale, erweiterbare Datenbanken" - WS 2001/2002 (Dr. C. Türker)
4-109