Dr. Brigitte Mathiak Kapitel 5 SQL DDL + DML. Datenbanken, WS 12/13 Kapitel 5: SQL22 Lernziele Fähigkeit zur praktischen Anwendung von SQL-Anweisungen

Dr. Brigitte Mathiak

Kapitel 5

SQL DDL + DML

Datenbanken, WS 12/13 Kapitel 5: SQL2 2

Lernziele

Fähigkeit zur praktischen Anwendung von SQL-Anweisungen in den Bereichen: Schema-Definitionen einschl. referentieller Integrität Einfügen von Daten Ändern von Daten Löschen von Daten

Varianten von SQL kennen lernen


SQL: Komponenten der vollständigen DB-Sprache

Datenmanipulation (Data Manipulation Language DML) Einfügen, Löschen und Ändern von individuellen Tupeln

und von Mengen von Tupeln Zuweisung von ganzen Relationen

Datendefinition (Data Definition Language DDL) Definition von Wertebereichen, Attributen und Relationen Definition von verschiedenen Sichten auf Relationen

Datenkontrolle Spezifikation von Bedingungen zur Zugriffskontrolle Spezifikation von Zusicherungen (assertions) zur semantischen

Integritätskontrolle

Datenabfragen Abbildung von Eingaberelationen durch Auswertung von Bedingungen

auf die Ergebnisrelation

Kopplung mit einer Wirtssprache (z.B. C++) deskriptive Auswahl von Mengen von Tupeln sukzessive Bereitstellung einzelner Tupeln

Das war Kapitel 2

In diesem Kapitel

In diesem Kapitel


Beispiel: Universitätsschema

hörenStudenten Vorlesungen

* *MatrNrName

Semester

VorlNrTitelSWS

Professoren

*

*

PersNrNameRangRaum

voraussetzen

lesen

*

11

*

PrüfenNote

Assistenten

PersNrName

Fachgebiet

arbeitenFür*1


Professoren

PersNr Name Rang Raum

2125 Sokrates C4 226

2126 Russel C4 232

2127 Kopernikus C3 310

2133 Popper C3 52

2134 Augustinus C3 309

2136 Curie C4 36

2137 Kant C4 7

Studenten

MatrNr Name Semester

24002 Xenokrates 18

25403 Jonas 12

26120 Fichte 10

26830 Aristoxenos 8

27550 Schopenhauer

6

28106 Carnap 3

29120 Theophrastos 2

29555 Feuerbach 2

Vorlesungen

VorlNr Titel SWS gelesenVon

5001 Grundzüge 4 2137

5041 Ethik 4 2125

5043 Erkenntnistheorie 3 2126

5049 Mäeutik 2 2125

4052 Logik 4 2125

5052 Wissenschaftstheorie

3 2126

5216 Bioethik 2 2126

5259 Der Wiener Kreis 2 2133

5022 Glaube und Wissen 2 2134

4630 Die 3 Kritiken 4 2137

voraussetzen

Vorgänger Nachfolger

5001 5041

5001 5043

5001 5049

5041 5216

5043 5052

5041 5052

5052 5259

hören

MatrNr VorlNr

26120 5001

27550 5001

27550 4052

28106 5041

28106 5052

28106 5216

29120 5001

29120 5041

29120 5049

25403 5022

Assistenten

PerslNr Name Fachgebiet Boss

3002 Platon Ideenlehre 2125

3003 Aristoteles Syllogistik 2125

3004 Wittgenstein Sprachtheorie 2126

3005 Rhetikus Planetenbewegung 2127

3006 Newton Keplersche Gesetze 2127

3007 Spinoza Gott und Natur 2126

prüfen

MatrNr VorlNr PersNr Note

28106 5001 2126 1

25403 5041 2125 2


Relationale Invarianten

Integritätsbedingungen des Relationenmodells

Primärschlüsselbedingung (Entity-Integrität)- Eindeutigkeit des Primärschlüssels- keine Nullwerte!

Referentielle Integrität: - Darstellung von Beziehungen durch Fremdschlüssel (foreign key):

Attribut, das in Bezug auf den Primärschlüssel einer anderen (oder derselben) Relation definiert ist

- zugehöriger Primärschlüssel muss existieren, d.h. zu jedem Wert (ungleich Null) eines Fremdschlüsselattributs einer Relation R2muss ein gleicher Wert des Primärschlüssels in irgendeinem Tupel von Relation R1 vorhanden sein


Relationale Invarianten (1)

Fremdschlüssel und zugehöriger Primärschlüssel tragen wichtige interrelationale Informationen sie sind auf dem gleichen Wertebereich definiert sie gestatten die Verknüpfung von Relationen mit Hilfe von

Relationenoperationen

Fremdschlüssel können Nullwerte aufweisen, wenn sie nicht Teil

eines Primärschlüssels sind. ein Fremdschlüssel ist „zusammengesetzt“, wenn der zugehörige

Primärschlüssel „zusammengesetzt“ ist

Eine Relation kann mehrere Fremdschlüssel besitzen, die die gleiche oder verschiedene Relationen referenzieren

Zyklen sind möglich (geschlossener referentieller Pfad)Eine Relation kann zugleich referenzierende und referenzierte

Relation sein („self-referencing table“).


SQL: DDL Anweisungen

DDL-Spezifikation in SQL bei CREATE TABLE:

CREATE TABLE Assistenten(PersNr INT PRIMARY KEY, Name VARCHAR2 (100) NOT NULL, Fachgebiet VARCHAR2(100), Boss INT REFERENCES Professoren(PersNr));

Assistenten

PersNr Name Fachgebiet Boss







Professoren



2126 Russel C4 232


2133 Popper C3 52


2136 Curie C4 36

2137 Kant C4 7


(Einfache) Datendefinition in SQL

CREATE TABLE <tablename>( <columnname> <Datentyp> <Constraint>, <columnname2> …, …)

Wichtige Datentypen in Oracle: varchar2 (n) für Strings variabler LängeNUMBER(p,s) für Zahlen mit der angegebenen GenauigkeitPLS_INTEGER für Integerwert (bessere Performanz)blob oder raw für sehr große binäre Datenclob für sehr große String-Attributedate für Datumsangabenxml für XML-Dokumente…

Constraints

Mehr zu Constraints im Kapitel Integritätsbedingungen hier nur kurz die wichtigsten:

• PRIMARY KEY für den Primärschlüssel

• REFERENCES rel(col) für einen Fremdschlüssel, der auf die Relation rel und die Spalte col zeigt

• UNIQUE wenn es keine Duplikate in der Spalte geben darf (PRIMARY KEY ist automatisch UNIQUE)

• NOT NULL wenn Nullwerte nicht zugelassen sind


Schemaänderungen

ALTER TABLE <table_name> RENAME TO <new_table_name>;

Beispiel:ALTER TABLE suppliers

RENAME TO vendors;

Statt RENAME TO gibt es auch eine Reihe anderer Änderungsmöglichkeiten z.B.

• ADD <column> <Datentyp> <Constraint>• MODIFY <column> <neuer_Datentyp> <neues_Constraint>• DROP <column>• RENAME COLUMN <column> TO <neuer_Name>



Veränderung am Datenbestand

Einfügen von Tupeln:

insert into Studenten (MatrNr, Name)values (28121, 'Archimedes');

insert into hören

select MatrNr, VorlNr

from Studenten, Vorlesungen

where Titel= 'Logik' ;

StudentenMatrNr Name Semester

29120 Theophrastos 229555 Feuerbach 228121 Archimedes -

Null-Wert


Veränderungen am Datenbestand

Löschen von Tupeln

delete Studenten

where Semester > 13;

Verändern von Tupeln

update Studenten

set Semester = Semester + 1;


CREATE TABLE Assistenten(PersNr INT PRIMARY KEY, Name VARCHAR2 (100) NOT NULL, Fachgebiet VARCHAR2(100), Boss INT REFERENCES Professoren(PersNr));

Aufgabe: Denken Sie sich eine Insert, eine Delete und eine Update Anweisung aus, die vom System abgelehnt werden. (Jeder für sich mit Zettel und Stift; 5 min)Zusatzaufgabe: Denken Sie sich ein Alter Table aus, dass vom System abgelehnt wird

Assistenten

PersNr Name Fachgebiet Boss







Professoren



2126 Russel C4 232


2133 Popper C3 52


2136 Curie C4 36

2137 Kant C4 7

Bootstrapping

CREATE TABLE Sekretärin(PersNr INT PRIMARY KEY, …Boss INT REFERENCES Professor(PersNr));

CREATE TABLE Professor(PersNr INT PRIMARY KEY, …Sekräterin INT REFERENCES Sekretärin(PersNr));

Was geht da schief?

Welche Tabelle lege ich zuerst an?

Welche Daten füge ich zuerst ein?


Bootstrapping (Lösungen)

•ALTER TABLE MODIFY COLUMN Boss INT REFERENCES Professor(PersNr);

•Bei der Datenmodellierung darauf achten, solche zyklischen Abhängigkeiten zu vermeiden

•Transaktionen (s. spätere Kapitel)Es werden mehrere Operationen gleichzeitig ausgeführt

•Insert into Sekretärin values (4, …, Null);Insert into Professor values (7, …, 4);Update Sekretärin Set Boss = 7 Where PersNr = 4 ;


Varianten von SQL

Nicht alle Versionen von SQL sind identisch

Neben syntaktischen Varianten sind insbesondere die verschiedenen Join Operatoren und Lösungen für das Rekursionsproblem relevant.

Achtung:

Benutzen Sie in der Klausur ausschließlich Standardsyntax !!!


Joins in SQL-92cross join: Kreuzproduktselect *

from R1, R2;

natural join: natürlicher JoinJoin oder inner join: Theta-Joinleft, right oder full outer join: äußerer Joinunion join: Vereinigungs-Join (wird hier nicht vorgestellt)

Datenbanken, SS 12 Kapitel 2: SQL Anfragen 19

Inner Join

Anfrage:welcher Professor bietet welche Vorlesungen an?

SELECT p.Name, v.NameFROM Professoren p JOIN Vorlesungen v ON p.PersNr = v.gelesenVon ;

SELECT p.Name, v.NameFROM Professoren p, Vorlesungen vWHERE p.PersNr = v.gelesenVon ;


Natural Join

Anfrage: Welcher Student hört welche Vorlesung?

SELECT Studenten.Name, Vorlesungen.NameFROM Studenten NATURAL JOIN hören NATURAL JOIN Vorlesungen ;

SELECT s.Name, v.NameFROM Studenten s, hören h, Vorlesungen vWHERE s.MatrNr = h.MatrNr AND h.VorlNr = v.vorlNr ;


Cross Join

Anfrage: alle Paare "Professor – Student" :

SELECT Professoren.Name, Studenten.NameFROM Professoren CROSS JOIN Studenten ;

SELECT p.Name, s.NameFROM Professoren p, Studenten s ;


Outer Joins: Left Outer Join

SELECT p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Name

FROM Professoren p LEFT OUTER JOIN ( prüfen f LEFT OUTER JOIN Studenten s

ON f.MatrNr = s.MatrNr ) ON p.PersNr = f.PersNr ;

p.PersNr p.Name f.PersN

rf.Not

ef.MatrN

rs.Matr

Nr s.Name

2126 Russel 2126 1 28106 28106 Carnap

2125 Sokrates 2125 2 25403 25403 Jonas

2137 Kant 2137 2 27550 27550 Schopenhauer

2136 Curie Ø Ø Ø Ø Ø

… … … … … … …


Outer Joins: Right Outer Join

SELECT p.PersNr, p.Name, f.PersNr, f.Note,

f.MatrNr, s.MatrNr, s.Name

FROM Professoren p RIGHT OUTER JOIN

(prüfen f RIGHT OUTER JOIN

Studenten s ON f.MatrNr = p.MatrNr)

ON p.PersNr = f.PersNr ;

p.PersNr p.Name f.PersNr f.Note f.MatrN

r s.MatrNr s.Name

2126 Russel 2126 1 28106 28106 Carnap

2125 Sokrates 2125 2 25403 25403 Jonas


Ø Ø Ø Ø Ø 26120 Fichte

… … … … … … …


p.PersNr p.Name f.PersNr f.Note f.MatrNr s.MatrNr s.Name

2126 Russel 2126 1 28106 28106 Carnap

2125 Sokrates 2125 2 25403 25403 Jonas


Ø Ø Ø Ø Ø 26120 Fichte

… … … … … … …

2136 Curie Ø Ø Ø Ø Ø… … … … … … …

SELECT p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Name

FROM Professoren p FULL OUTER JOIN

(prüfen f FULL OUTER JOIN Studenten s

ON f.MatrNr = s.MatrNr)

ON p.PersNr = f.PersNr ;

Outer Joins: Full Outer Join


Outer Joins: Oracle-Syntax

SELECT * FROM Studenten LEFT OUTER JOIN hörenUSING (MatrNr)

SELECT * FROM Studenten s, hören h WHERE s.MatrNr = h.MatrNr (+)

Anfrage: welche Studenten hören welche Vorlesungen

Äußere Joinsselect p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Namefrom Professoren p left outer join

(prüfen f left outer join Studenten s on f.MatrNr= s.MatrNr)

on p.PersNr=f.PersNr;

PersNrp.Name f.PersNr f.Note f.MatrNr s.MatrNr

s.Name

2126 Russel 2126 1 28106 28106 Carnap2125 Sokrate

s2125 2 25403 25403 Jonas

2137 Kant 2137 2 27550 27550 Schopen-hauer

2136 Curie - - - - -

Äußere Joins

select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Namefrom Professoren p right outer join

(prüfen f right outer join Studenten s on f.MatrNr= s.MatrNr) on p.PersNr=f.PersNr;PersNrp.Name f.PersNr f.Note f.MatrNr s.MatrN

rs.Name


s2125 2 25403 25403 Jonas


- - - - - 26120 Fichte

Äußere Joins

select p.PersNr, p.Name, f.PersNr, f.Note, f.MatrNr, s.MatrNr, s.Namefrom Professoren p full outer join

(prüfen f full outer join Studenten s on f.MatrNr= s.MatrNr)on p.PersNr=f.PersNr;

p.PersNr

p.Name f.PersNr f.Note f.MatrNr s.MatrNr

s.Name


s2125 2 25403 25403 Jonas


- - - - - 26120 Fichte

2136 Curie - - - - -

Rekursion

select Vorgänger

from voraussetzen, Vorlesungen

where Nachfolger= VorlNr and

Titel= `Der Wiener Kreis´

Der Wiener Kreis

Wissenschaftstheorie

Bioethik

Erkenntnistheorie Ethik Mäeutik

Grundzüge

Rekursion

select v1.Vorgänger

from voraussetzen v1, voraussetzen v2, Vorlesungen v

where v1.Nachfolger= v2.Vorgänger and

v2.Nachfolger= v.VorlNr and

v.Titel=`Der Wiener Kreis´

select v1.Vorgängerfrom voraussetzen v1

voraussetzen vn_minus_1voraussetzen vn,

Vorlesungen v

where v1.Nachfolger= v2.Vorgänger and

vn_minus_1.Nachfolger= vn.Vorgänger and

vn.Nachfolger = v.VorlNr and

v.Titel= `Der Wiener Kreis´

Vorgänger des „Wiener Kreises“ der Tiefe n

Wollen wir das wirklich?

Grundproblem: Transitive Hülle

transA,B(R)= {(a,b) k IN (1, ..., k R (

1.A= 2.B

k-1.A= k.B

1.A= a

k.B= b))}

Der Wiener Kreis

Wissenschaftstheorie

Bioethik

Erkenntnistheorie Ethik Mäeutik

Grundzüge

Die connect by-Klausel (Oracle)

select Titel

from Vorlesungen

where VorlNr in (select Vorgänger

fromvoraussetzen

connect by Nachfolger= prior Vorgänger

start with Nachfolger= (select VorlNr

from Vorlesungen

where Titel= `Der

Wiener Kreis´));

GrundzügeEthik

ErkenntnistheorieWissenschaftstheorie

Rekursion in DB2/SQL99: gleiche Anfrage

with TransVorl (Vorg, Nachf)

as (select Vorgänger, Nachfolger from voraussetzen

union all

select t.Vorg, v.Nachfolger

from TransVorl t, voraussetzen v

where t.Nachf= v.Vorgänger)

select Titel from Vorlesungen where VorlNr in

(select Vorg from TransVorl where Nachf in

(select VorlNr from Vorlesungen

where Titel= `Der Wiener Kreis´) )

zuerst wird eine temporäre Sicht TransVorl mit der with-Klausel angelegt

Diese Sicht TransVorl ist rekursiv definiert, da sie selbst in der Definition vorkommt

Aus dieser Sicht werden dann die gewünschten Tupel extrahiert

Ergebnis ist natürlich wie gehabt


Fazit Rekursion/transitive Hülle

In SQL nur mühsam lösbar

Vorhandene Lösungen sind technologieabhängig

Die praktisch beste Lösung ist fast immer das Ändern der Datenstruktur oder das Anlegen von Hilfstabellen

voraussetzen_recVorgänge

rNachfolger

5001 5041

5001 5043

5001 5049

5041 5216

5043 5052

5041 5052

5052 5259

5001 5216

5001 5052

5001 5043

5001 5052

5043 5259

5041 5259

Achtung!Solche Datenstrukturensind oft schwer aktuell zu halten -> Datenintegrität


Syntaktische Beschreibung einer Select Anweisung

"Grobsyntax":

select_block { { UNION | INTERSECT | EXCEPT } [ALL] select_block ...} [ORDER BY result_column [ASC | DESC] {, result_column [ASC | DESC] …}

mit select_block ::=

SELECT [ALL | DISTINCT] {column | {expression [AS result_column]}} {, {column | {expression [AS result_column]}} …}FROM table [correlation_var] {, table [correlation_var] …}[WHERE search_condition][GROUP BY column {, column …} [HAVING search_condition] ]

Typische Select-Pattern

Paare von gleichartigen Objekten (z.B. Welche Paare von Studenten haben …?)SELECT a1.name, a2.name FROM a a1, a a2 WHERE a1.ID < a2.ID AND ….

Aggregation (z.B. Wer hört wie viele Vorlesungen?)SELECT a.name, aggr(b.ID) FROM a,b WHERE a.ID=b.Fkey GROUP BY a.name

Choosing group (z.B. Wer hört mehr als zwei Vorlesungen?)SELECT a.name FROM a,b WHERE a.ID=b.Fkey GROUP BY a.name HAVING …


Verhindert, dass Paare zweimal vorkommen

Doppelte Aggregation

Double aggregation (z.B. Wie viele Vorlesungen hören Studenten im Durchschnitt?)Warum doppelt?Ich muss erst die Vorlesungen zählen und dann den Durchschnitt bestimmen.Einfachste Variante:

SELECT aggr1(aggr2(b.ID)) FROM a,b WHERE a.ID=b.Fkey GROUP BY a.ID

System unabhängig: SELECT aggr1(t.temp) FROM(SELECT aggr2 AS temp FROM a,b WHERE a.ID=b.Fkey GROUP BY a.ID) t


Leider nicht durch den Standard vorgesehen!!!

Choice-by-aggregate

(z.B. Welche Vorlesungen haben die meisten SWS?)SELECT a.name, x FROM a WHERE x = (

SELECT aggr(x) FROM a)

Allerdings müssen beide a exakt identisch sein (inkl. WHERE, GROUP BY und HAVING Klauseln), sonst kann es zu einem Mismatch kommen. Bei komplexen a kann es sich daher lohnen eine View anzulegen (Kap. 8)



Constraints and Views...

..to be continued.

Documents

Dr. Brigitte Mathiak Kapitel 5 SQL DDL + DML. Datenbanken, WS 12/13 Kapitel 5: SQL22 Lernziele Fähigkeit zur praktischen Anwendung von SQL-Anweisungen