Datenbanksysteme I

SQL
Felix Naumann
15.5.2019
SQL – Historie

■ SEQUEL (1974, IBM Research Labs San Jose)

■ SEQUEL2 (1976, IBM Research Labs San Jose)
□ System R
■ SQL (1982, IBM)

Don Chamberlin, IBM

[Link]
chamberlin/[Link]
Felix Naumann
Datenbanksysteme I
Sommer 2019

2
SQL – Standardisierung

■ SQL1 von ANSI als Standard verabschiedet (1986)

■ SQL1 von der (ISO) als Standard verabschiedet (1987)
□ 1989 nochmals überarbeitet.
■ SQL2 oder SQL-92 von der ISO verabschiedet (1992)
■ SQL3 oder SQL:1999 verabschiedet
Trotz Standardisierung:
□ Trigger, rekursive Anfragen Inkompatibilitäten
□ Objektrelationale Erweiterungen zwischen Systemen der
■ SQL:2003 von der ISO verabschiedet einzelnen Hersteller
□ XML-Support durch SQL/XML
■ SQL/XML:2006
□ XQuery eingebunden
■ SQL:2008
Felix Naumann
□ Updates auf Sichten, logisches Löschen (TRUNCATE), … Datenbanksysteme I
■ SQL:2011 Sommer 2019

□ Adds temporal data (PERIOD FOR)

■ SQL:2016
□ Adds row pattern matching, polymorphic table functions, JSON. 3
SQL:2008 Struktur

■ Part 1: Framework (SQL/Framework) – 82 Seiten

□ Überblick
■ Part 2: Foundation (SQL/Foundation) – 1316 Seiten
□ Datenmodell, DDL, DML, Abfragen
■ Part 3: Call-Level Interface (SQL/CLI) – 389 Seiten
□ Zugriff auf DBMS mittels Funktionsaufrufen aus anderen Programmiersprachen
■ Part 4: Persistent Stored Modules (SQL/PSM) – 188 Seiten
□ Prozedurale Erweiterungen
■ Part 9: Management of External Data (SQL/MED) – 484 Seiten
□ Neue Datentypen und Funktionen
■ Part 10: Object Language Bindings (SQL/OLB) – 396 Seiten
□ Auch (SQLJ); zur Einbettung von SQL in Java
■ Part 11: Information and Definition Schemas (SQL/Schemata) – 286 Seiten
□ DBMS werden selbst-beschreibend durch normierten Katalog Felix Naumann
Datenbanksysteme I
■ Part 13: SQL Routines and Types (SQL/JRT) – 198 Seiten Sommer 2019
□ Externe Java Routinen als „stored procedures“
■ Part 14: XML-Related Specifications (SQL/XML) – 438 Seiten
□ XML Datentyp und Erweiterung von SQL um XQuery Zusammen:
3777 Seiten 4
Motivation für SQL

■ Meist-verbreitete Datenbankanfragesprache
■ Ad-hoc und einfach
■ Deklarativ
□ Nicht prozedural / imperativ
□ Optimierbar
■ Very-High-Level Language
■ Anfragen an relationale Algebra angelehnt
□ Hinzu kommt DDL: Data definition language
□ Hinzu kommt DML: Data manipulation language
■ Achtung: Syntax kann sich von System zu System leicht unterscheiden. Felix Naumann
Datenbanksysteme I
■ Achtung: Funktionalität kann sich von System zu System leicht Sommer 2019
unterscheiden.

5
SQL Referenzen

■ DBMS-spezifische Webseiten
■ DB2
□ [Link]
□ SQL Reference Volume 1:
[Link]
[Link]
□ SQL Reference Volume 2:
[Link]
[Link]
■ Oracle
□ [Link] Felix Naumann
Datenbanksysteme I
■ MS SQL Server Sommer 2019

□ [Link]

6
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

7
Beispielschema

Studio Name Adresse VorsitzenderID

Film Titel Jahr Länge inFarbe StudioName ProduzentID

spielt_in FilmTitel FilmJahr Name

Schauspieler Name Adresse Geschlecht Geburtstag

Felix Naumann
Datenbanksysteme I
Sommer 2019

Manager Name Adresse ManagerID Gehalt

8
SELECT … FROM … WHERE …

■ SELECT *
FROM Film
WHERE StudioName = ‘Disney‘ AND Jahr= 1990;

■ Lesereihenfolge (und Schreibreihenfolge):

1. FROM: Relation(en) aus denen die Daten stammen
2. WHERE: Bedingung(en) an die Daten
3. SELECT: Schema der Ergebnisrelation
– *: Alle Attribute der Inputrelationen

Felix Naumann
■ Ausführung Datenbanksysteme I
Sommer 2019
□ Für jedes Tupel aus „Film“ prüfe die Bedingungen und gebe gültige Tupel aus.

9
Groß- und Kleinschreibung

■ In SQL wird Groß- und Kleinschreibung nicht beachtet

□ From = FROM = from = FrOm

■ Auch bei Attribut- und Relationennamen

□ SELect vorNAMe fROm fiLM
□ SELECT vorname FROM film

■ Natürlich nicht bei Konstanten:

□ ‘FROM‘ ≠ ‘from‘ ≠ from = FROM
Felix Naumann
Datenbanksysteme I
■ Konvention zur Lesbarkeit Sommer 2019

□ Schlüsselworte großschreiben
□ Schemaelemente kleinschreiben
10
Projektion in SQL (SELECT, π)

■ Spezifikation in der SELECT Klausel

□ SELECT * FROM Film
– Alle Attribute
□ SELECT Titel, Jahr, inFarbe FROM Film
– Projektion auf die drei Attribute

■ Erweiterte Projektion
□ Umbenennung:
– SELECT Titel AS Name, Jahr AS Zeit FROM Film
□ Arithmetischer Ausdruck:
– SELECT Titel, Länge * 0.016667 AS Stunden FROM Film Felix Naumann
Datenbanksysteme I
□ Konstanten: Sommer 2019
– SELECT Titel, Länge * 0.016667 AS Stunden,
‘std.‘ AS inStunden FROM Film

11
Selektion in SQL (WHERE, σ)

■ Spezifikation in der WHERE Klausel

□ Bedingungen wie in einer Programmiersprache
□ Sechs Vergleichsoperatoren
– =, <>, <, >, <=, >=
– <>, <=, >= entspricht ≠, ≤, ≥
□ Operanden
– Konstanten und Attributnamen
– Auch Attribute, die nicht in der SELECT Klausel genannt werden.
□ Arithmetische Ausdrücke für numerische Attribute
– Z.B.: (Jahr - 1930) * (Jahr - 1930) <= 100 Felix Naumann
Datenbanksysteme I
□ Konkatenation für Strings Sommer 2019

– ‘Star‘ || ‘Wars‘ entspricht ‘StarWars‘

– Vorname || ` ` || Nachname = `Felix Naumann`
12
Selektion in SQL

■ Ergebnis ist stets Boole‘scher Wert

□ TRUE oder FALSE
□ Können mit AND, OR und NOT verknüpft werden.
– Klammerungen sind erlaubt.
□ Nur wenn insgesamt auf TRUE evaluiert wird, erscheint das entsprechende
Tupel im Ergebnis.

■ Beispiele
□ SELECT Titel
FROM Film
WHERE Jahr > 1970 AND NOT inFarbe; Felix Naumann
Datenbanksysteme I
□ SELECT Titel Sommer 2019
FROM Film
WHERE (Jahr > 1970 OR Länge < 90) AND StudioName = ‘MGM‘;

13
Stringvergleiche

■ Datentypen
□ Array fester Länge, Buchstabenliste variabler Länge, Konstanten
□ SQL erlaubt viele Vergleiche über Datentypen hinweg
■ foo _ _ _ _ _ = foo = ‘foo‘

■ Vergleiche mit =, <, >, <=, >=, <>

□ Lexikographischer Vergleich
□ ‘fodder‘ < ‘foo‘; ‘bar‘ < ‘bargain‘;
□ Sortierreihenfolge upper-case/lower-case usw. je nach DBMS
□ Achtung: Auch nicht immer identisch zur Reihenfolge in Felix Naumann
Programmiersprachen Datenbanksysteme I
Sommer 2019

14
String-Mustervergleiche mit LIKE

■ string LIKE pattern

□ bzw. string NOT LIKE pattern

■ Pattern hat spezielle Zeichen

□ ‘%‘: Beliebige Sequenz von 0 oder mehr Zeichen
□ ‘_‘: Ein beliebiges Zeichen

■ SELECT Titel FROM Film

WHERE Titel LIKE ‘Star _ _ _ _‘;
□ Star Wars und Star Trek
Felix Naumann
Datenbanksysteme I
Sommer 2019
■ SELECT Titel FROM Film WHERE Titel LIKE ‘%War%‘;

15
Datum und Uhrzeit

■ Spezielle Datentypen und Repräsentationen

□ Datumskonstante:
– DATE ‘YYYY-MM-DD‘
– DATE ‘1948-05-14‘
□ Zeitkonstante
– TIME ‘HH:MM:SS.S‘
– TIME ‘15:00:02.5‘
□ Zeitstempel
– TIMESTAMP ‘1948-05-14 15:00:02.5‘
□ Zeitvergleiche Felix Naumann
Datenbanksysteme I
– TIME ‘15:00:02.5‘ < TIME ‘15:02:02.5‘ ergibt TRUE Sommer 2019

– Selektion: ERSCHEINUNGSTAG >= DATE ‘1949-11-12‘

16
Nullwerte

■ Darstellung: NULL bzw. ⊥ ■ Regeln für Umgang mit Nullwerten

■ Mögliche Interpretationen □ Arithmetische Operationen mit NULL
□ Unbekannter Wert ergeben NULL
– Geburtstag eines Schauspielers □ Vergleich mit NULL ergibt
Wahrheitswert UNKNOWN
□ Wert unzulässig
□ NULL ist keine Konstante, sondern
– Ehegatte eines unverheirateten erscheint nur als Attributwert
Schauspielers
■ Beispiel (sei der Wert von x NULL):
□ Wert unterdrückt
□ x+3 ergibt NULL.
– Geheime Telefonnummer
□ NULL+3 ist kein zulässiger Ausdruck.
□ x = 3 ergibt UNKNOWN.
■ Prüfen von Nullwerten in WHERE Klausel Felix Naumann
Datenbanksysteme I
□ Geburtstag IS NULL Sommer 2019

□ bzw. Geburtstag IS NOT NULL

17
Wahrheitswerte

AND true unknown false

■ Rechenregeln

true true unknown false

□ TRUE =1
FALSE =0
unknown unknown unknown false UNKNOWN = ½
false false false false □ AND: Minimum der beiden Werte
OR true unknown false □ OR: Maximum der beiden Werte
true true true true □ NOT: 1 – Wert
unknown true unknown unknown
false true unknown false □ Beispiel
– TRUE AND (FALSE OR Felix Naumann
NOT NOT(UNKNOWN)) Datenbanksysteme I
Sommer 2019
true false = MIN(1, MAX(0, (1 - ½ )))
unknown unknown = MIN(1, MAX(0, ½ )
= MIN(1, ½ ) = ½.
false true 18
Wahrheitswerte

Titel Jahr Länge inFarbe Studio ProduzentID

Total Recall 1990 NULL True Fox 12345

■ Überraschendes Verhalten
□ SELECT *
FROM Film
WHERE Länge <= 90 OR Länge > 90;

UNKNOWN UNKNOWN

UNKNOWN
Felix Naumann
□ Tupel erscheint nicht im Ergebnis. Datenbanksysteme I
Sommer 2019

■ Ausführungspriorität: NOT vor AND vor OR

19
Sortierung

■ ORDER BY Klausel ans Ende der Anfrage

□ ORDER BY <Attributliste> DESC/ASC
□ ASC (aufsteigend) ist default
□ SELECT *
FROM Film
WHERE StudioName = ‘Disney‘ AND Jahr = 1990
ORDER BY Länge, Titel;

□ SELECT *
FROM Film
WHERE StudioName = ‘Disney‘ AND Jahr = 1990
Felix Naumann
ORDER BY Länge ASC, Titel DESC; Datenbanksysteme I
Sommer 2019

20
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

21
Motivation

■ Hauptstärke der Relationalen Algebra ist die Kombination von Relationen

■ Erst mit mehreren Relationen sind viele interessante Anfragen möglich.

■ Nennung der beteiligten Relationen in der FROM Klausel

Felix Naumann
Datenbanksysteme I
Sommer 2019

22
Kreuzprodukt und Join

■ Film(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)

■ Manager(Name, Adresse, ManagerID, Gehalt)
Kreuzprodukt
■ SELECT Name
FROM Film, Manager Selektionsbedingung
WHERE Titel = ‘Star Wars‘
AND ProduzentID = ManagerID;
Joinbedingung
■ Semantik
□ Betrachte jedes Tupelpaar der Relationen Film und Manager.
□ Wende Bedingung der WHERE Klausel auf jedes Tupelpaar an
□ Falls Bedingung erfüllt, produziere ein Ergebnistupel.
Felix Naumann
■ Kreuzprodukt gefolgt von Selektion: Join Datenbanksysteme I
Sommer 2019
■ Reihenfolge der WHERE Bedingungen egal
□ SELECT Name FROM Film, Manager
WHERE ProduzentID = ManagerID
AND Titel = ‘Star Wars‘; 23
Uneindeutige Attributnamen

■ Schauspieler(Name, Adresse, Geschlecht, Geburtstag)

■ Manager(Name, Adresse, ManagerID, Gehalt)

■ Bei gleichen Attributnamen aus mehreren beteiligten Relationen:

□ Relationenname als Präfix:
– SELECT [Link], [Link]
FROM Schauspieler, Manager
WHERE [Link] = [Link];

□ Präfix ist auch erlaubt wenn Attributname eindeutig ist.

Felix Naumann
– Erleichtert das Lesen von SQL Anfragen Datenbanksysteme I
Sommer 2019

24
Tupelvariablen

■ Zur eindeutigen Kennzeichnung von Tupeln beteiligter Relationen

□ „Alias“ einer Relation
□ Insbesondere: Bei der mehrfachen Verwendung einer Relation in einer
Anfrage
■ Gesucht: Schauspieler, die zusammen leben
□ SELECT [Link], [Link]
FROM Schauspieler Star1, Schauspieler Star2
WHERE [Link] = [Link]
Äquivalent zu
Schauspieler AS Star2
■ Auch sinnvoll als abkürzenden Schreibweise
□ SELECT [Link], [Link] Felix Naumann
FROM Schauspieler S, Manager M Datenbanksysteme I
Sommer 2019
WHERE [Link] = [Link];
■ Ohne explizites Angeben einer Tupelvariablen wird der Relationenname als
Tupelvariable verwendet.
25
 Tupelvariablen – Selfjoin

Name Adresse Geschlecht Geburt

Carrie Fisher 123 Maple St., Hollywood F 9/9/99
Mark Hamill 456 Oak Rd., Brentwood M 8/8/88
Brad Pitt 123 Maple St., Hollywood M 7/7/77
[Link] [Link]
SELECT [Link], [Link] Carrie Fisher Carrie Fisher
FROM Schauspieler Star1, Schauspieler Star2
WHERE [Link] = [Link]; Carrie Fisher Brad Pitt
Brad Pitt Carrie Fisher
SELECT [Link], [Link] Brad Pitt Brad Pitt
FROM Schauspieler Star1, Schauspieler Star2
WHERE [Link] = [Link] Mark Hamill Mark Hamill
AND [Link] <> [Link];
[Link] [Link] Felix Naumann
Datenbanksysteme I
Carrie Fisher Brad Pitt
SELECT [Link], [Link] Sommer 2019

FROM Schauspieler Star1, Schauspieler Star2 Brad Pitt Carrie Fisher

WHERE [Link] = [Link]
AND [Link] < [Link]; [Link] [Link]
Brad Pitt Carrie Fisher 26
Interpretation von Anfragen

■ Drei Interpretationsvarianten für Anfragen mit mehreren Relationen

□ Nested Loops (geschachtelte Schleifen)
– Bei mehreren Tupelvariablen: Eine geschachtelte Schleife für jede Variable
□ Parallele Zuordnung
– Alle Kombinationen werden parallel bezüglich der Bedingungen geprüft.
□ Relationale Algebra
– Bilde Kreuzprodukt
– Wende Selektionsbedingungen auf jedes Resultat-Tupel an

Felix Naumann
Datenbanksysteme I
Sommer 2019

27
Interpretation von Anfragen

■ Gegeben drei Relationen: R(A), S(A) und T(A)

■ Gesucht: R ∩ (S ∪ T) (= (R ∩ S) ∪ (R ∩ T) )
□ SELECT R.A
FROM R, S, T
WHERE R.A = S.A SELECT *
OR R.A = T.A; FROM
(
(SELECT A FROM R)
■ Problemfall: T ist leer, hat also kein Tupel INTERSECT
(SELECT * FROM
■ Vermeintliches Resultat: R ∩ S (SELECT A FROM S)
UNION
■ Tatsächliches Resultat: leere Menge (SELECT A FROM T)
□ Ausführung als ) Felix Naumann
) Datenbanksysteme I
drei geschachtelte Schleifen Sommer 2019

28
Joins

■ Man kann Joins auch auf andere Weise ausdrücken.

□ Geschmacksfrage
□ Film CROSS JOIN spielt_in
– Kreuzprodukt
– Doppelte Attributnamen werden mit Präfix der Relation aufgelöst
□ Film JOIN spielt_in
ON Titel = FilmTitel AND Jahr = FilmJahr
– Theta-Join
– SELECT Titel, Jahr, Länge, inFarbe, StudioName,
ProduzentID, SchauspielerName
FROM Film JOIN spielt_in ON Titel = FilmTitel
AND Jahr = FilmJahr;
– Eliminiert redundante Attribute FilmTitel und FilmJahr
□ SELECT Titel, Jahr Felix Naumann
Datenbanksysteme I
FROM Film JOIN spielt_in ON Titel = FilmTitel AND Jahr = FilmJahr Sommer 2019
JOIN Schauspieler ON spielt_in.Name = [Link]
WHERE Geschlecht = ‘w‘;
□ Schauspieler NATURAL JOIN Manager
– Natural Join; Eliminiert redundante Attribute 29
The TPC-H Schema

Felix Naumann
Datenbanksysteme I
Sommer 2019

30
TPC Query 2 - Minimum Cost Supplier

SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address,

s_phone, s_comment
FROM part, supplier, partsupp, nation, region
WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey
AND p_size = [SIZE] AND p_type like '%[TYPE]'
AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey
AND r_name = '[REGION]'
AND ps_supplycost =
(SELECT min(ps_supplycost)
FROM partsupp, supplier, nation, region
WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey
AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey
AND r_name = '[REGION]' )
ORDER BY s_acctbal desc, n_name, s_name, p_partkey;
Felix Naumann
Datenbanksysteme I
SIZE is randomly selected within [1. 50]; Sommer 2019

TYPE is randomly selected within the list Syllable 3 defined for Types
REGION is randomly selected within the list of values defined for R_NAME
31
The TPC-H Universal Table

SELECT l_linenumber, l_quantity, l_extendedprice, l_discount, l_tax,

l_returnflag, l_linestatus, l_shipdate, l_commitdate, l_receiptdate,
l_shipinstruct, l_shipmode, l_comment, o_orderkey, o_orderstatus, o_totalprice,
o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment, ps_availqty,
ps_supplycost, ps_comment, p_partkey, p_name, p_mfgr, p_brand, p_type, p_size,
p_container, p_retailprice, p_comment, c_custkey, c_name, c_address, c_phone,
c_acctbal, c_mktsegment, c_comment, s_suppkey, s_name, s_address, s_phone,
s_acctbal, s_comment, n_nationkey, n_name, n_comment, r_regionkey, r_name,
r_comment
FROM lineitem, orders, partsupp, part, customer, supplier, nation, region
WHERE p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND n_nationkey = s_nationkey
AND r_regionkey = n_regionkey
AND c_custkey = o_custkey Felix Naumann
Datenbanksysteme I
AND ps_partkey = l_partkey Sommer 2019

AND ps_suppkey = l_suppkey

AND o_orderkey = l_orderkey

32
Outer Joins

■ Schauspieler(Name, Adresse, Geschlecht, Geburtstag)

■ Manager(Name, Adresse, ManagerID, Gehalt)
■ Schauspieler, die zugleich Manager sind
□ SELECT Name, Adresse, Geburtstag, Gehalt
FROM Schauspieler NATURAL INNER JOIN Manager
■ Schauspieler und gegebenenfalls ihre Managerinfo
□ …FROM Schauspieler NATURAL LEFT OUTER JOIN Manager
□ Gehalt bleibt gegebenenfalls NULL
■ Manager und gegebenenfalls ihre Schauspielerinfo
□ …FROM Schauspieler NATURAL RIGHT OUTER JOIN Manager
□ Geburtstag bleibt gegebenenfalls NULL Felix Naumann
■ Alle Schauspieler und Manager Datenbanksysteme I
Sommer 2019
□ …FROM Schauspieler NATURAL FULL OUTER JOIN Manager
□ Geburtstag oder Gehalt bleiben gegebenenfalls leer
□ Unterschied zu UNION: Nur eine Zeile pro Person 33
Outer Joins

Manager
Schauspieler

Geburtstag Name Adresse Gehalt

1.2.1960 Mark Hamill LA NULL
27.5.1969 Carrie Fischer New York NULL
11.12.1940 Alec Guinness London NULL
22.3.1981 Ben Affleck Boston 5Mio
23.8.1973 Quentin Tarantino Berlin 10Mio
Felix Naumann
NULL George Lukas San Jose 100Mio Datenbanksysteme I
Sommer 2019

NULL Steven Spielberg LA 500Mio

34
Kreuzprodukt

■ Alle Paare aus Tupeln der beteiligten Relationen

□ SELECT *
FROM Schauspieler CROSS JOIN Film
□ SELECT *
FROM Schauspieler, Film

■ Selten verwendet
■ Grundbaustein für Joins

Felix Naumann
Datenbanksysteme I
Sommer 2019

35
Mengenoperationen in SQL

■ Vereinigung: UNION
■ Schnittmenge: INTERSECT
■ Differenz: EXCEPT / MINUS
■ Mengenoperationen nur zwischen geklammerten Anfrageergebnissen
■ Mengenoperationen haben implizit eine Mengensemantik
□ Wandeln Input-Relationen in Mengen um
□ Wandeln Output in Menge um
■ Input-Relationen müssen gleiche Schemata haben
□ Gleiche Attributnamen
□ Gleiche Datentypen Felix Naumann
Datenbanksysteme I
Sommer 2019

■ Schauspieler(Name, Adresse, Geschlecht, Geburtstag)

■ Manager(Name, Adresse, ManagerID, Gehalt)
36
Schnittmenge: INTERSECT

■ Entspricht dem logischen „und“

■ (SELECT Name, Adresse FROM Schauspieler)
INTERSECT
(SELECT Name, Adresse FROM Manager);

■ (SELECT Name, Adresse

FROM Schauspieler
WHERE Geschlecht = ‘F‘)
INTERSECT
(SELECT Name, Adresse
FROM Manager
WHERE Gehalt > 1000000) Felix Naumann
Datenbanksysteme I
Sommer 2019

■ Multimengen-Semantik: INTERSECT ALL

37
Vereinigung: UNION

■ Entspricht dem logischen „oder“

□ (SELECT Name, Adresse FROM Schauspieler)
UNION
(SELECT Name, Adresse FROM Manager);

■ Multimenge: UNION ALL

□ Beliebt, da schnell
□ Verwenden falls
– Semantik egal
Felix Naumann
– Multimengensemantik erwünscht Datenbanksysteme I
Sommer 2019
– Mengeneigenschaft von Input und Output bereits bekannt

38
Differenz: EXCEPT

■ Auch MINUS

□ (SELECT Titel, Jahr

FROM Film)
EXCEPT
(SELECT FilmTitel AS Titel, FilmJahr AS Jahr
FROM spielt_in)

■ Multimenge: EXCEPT ALL

Felix Naumann
Datenbanksysteme I
Sommer 2019

39
Klammerung

SELECT *
FROM
(
(SELECT A FROM R)
INTERSECT
(SELECT * FROM
(SELECT A FROM S)
UNION
(SELECT A FROM T)
)
)
Felix Naumann
Datenbanksysteme I
Sommer 2019

40
Zusammenfassung der Semantik

R1 R2 UNION ALL UNION EXCEPT ALL EXCEPT INTERSECT ALL INTERSECT

1 1 1 1 1 2 1 1
1 1 1 2 2 5 1 3
1 3 1 3 2 3 4
2 3 1 4 2 4
2 3 1 5 4
2 3 2 5
3 4 2
4 2
4 3
5 3
3
3
Felix Naumann
3 Datenbanksysteme I
4 Sommer 2019

4
4
[Link]
5
c/[Link]/doc/[Link] 41
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

42
Motivation

■ Eine Anfrage kann Teil einer anderen Anfrage sein.

□ Theoretisch beliebig tiefe Schachtelung

■ Drei Varianten
1. Subanfrage erzeugt einen einzigen Wert, der in der WHERE-Klausel mit
einem anderen Wert verglichen werden kann.
2. Subanfrage erzeugt eine Relation, die auf verschiedene Weise in WHERE-
Klausel verwendet werden kann.
3. Subanfrage erzeugt eine Relation, die in der FROM Klausel verwendet
werden kann.
– Wie jede normale Relation Felix Naumann
Datenbanksysteme I
Sommer 2019

43
Skalare Subanfragen

■ Allgemeine Anfragen produzieren Relationen.

□ Mit mehreren Attributen
– Zugriff auf ein bestimmtes Attribut ist möglich
□ i.A. mit mehreren Tupeln
□ Manchmal (garantiert) nur maximal ein Tupel und Projektion auf nur ein
Attribut
– „Skalare Anfrage“
– Verwendung wie eine Konstante möglich
– Falls keine Zeile: null

SUM Felix Naumann

Datenbanksysteme I
17 Sommer 2019

44
Skalare Subanfragen

■ Manager(Name, Adresse, ManagerID, Gehalt)

■ Film(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)
■ Gesucht: Produzent von Star Wars
□ SELECT Name
FROM Film, Manager
WHERE Titel = ‘Star Wars‘ AND Jahr = ‘1977‘
AND ProduzentID = ManagerID;
■ Oder aber
□ SELECT Name
FROM Manager
WHERE ManagerID =
( SELECT ProduzentID
FROM Film Felix Naumann
WHERE Titel = ‘Star Wars‘ AND Jahr = ‘1977‘ ); Datenbanksysteme I
Sommer 2019
■ DBMS erwartet maximal ein Tupel als Ergebnis der Teilanfrage
□ Falls kein Tupel: null
□ Falls mehr als ein Tupel: Laufzeitfehler
45
Skalare Subanfragen – Beispiel

■ Abteilungen, deren durchschnittliche Bonuszahlungen höher sind als deren

durchschnittliches Gehalt.

■ SELECT [Link], [Link]

FROM Abteilung a
WHERE (SELECT AVG(bonus)
FROM personal p
WHERE [Link] = [Link])
>
(SELECT AVG(gehalt)
FROM personal p
WHERE [Link] = [Link]) Felix Naumann
Datenbanksysteme I
Sommer 2019

46
Skalare Subanfragen – Beispiel

■ Alle Potsdamer Abteilungen mit ihrem Maximalgehalt.

□ SELECT [Link], [Link],
(SELECT MAX(Gehalt)
FROM Personal p
WHERE [Link] = [Link]) AS maxGehalt
FROM Abteilung a
WHERE [Link] = ‘Potsdam‘
■ Anmerkung: Auch Abteilungen ohne Mitarbeiter erscheinen im Ergebnis.
■ Nicht so in der folgenden Anfrage:
□ SELECT [Link], [Link], MAX([Link]) AS maxGehalt
FROM Abteilung a, Personal p
WHERE [Link] = ‘Potsdam‘ Felix Naumann
Datenbanksysteme I
AND [Link] = [Link] Sommer 2019

GROUP BY [Link], [Link]

47
Bedingungen mit Relationen

■ Bestimmte SQL Operatoren auf Relationen erzeugen Boole‘sche Werte

□ EXISTS R
– TRUE, falls R nicht leer
□ x IN R
– TRUE falls x gleich einem Wert in R ist (R hat nur ein Attribut)
– Verallgemeinerung auf Tupel später
– x NOT IN R: TRUE falls x keinem Wert in R gleicht
□ x > ALL R
– TRUE falls x größer als jeder Wert in R ist (R hat nur ein Attribut)
– Alternativ: <, >, <=, >=, <>, =
– x <> ALL R: Entspricht x NOT IN R bzw. auch NOT(x in R)
□ x > ANY R
Felix Naumann
– TRUE falls x größer als mindestens ein Wert in R ist (R hat nur ein Attribut) Datenbanksysteme I
– Alternativ: <, >, <=, >=, <>, = Sommer 2019

– x = ANY R: Entspricht x IN R
– Alternativer Befehl: SOME
□ Negation mit NOT(…) ist immer möglich. 48
EXISTS Beispiele

■ ISBNs aller ausgeliehenen Bücher

□ SELECT ISBN
FROM BuchExemplar
WHERE EXISTS
(SELECT *
FROM Ausleihe
WHERE [Link] = [Link])

Felix Naumann
Datenbanksysteme I
Sommer 2019

49
EXISTS Beispiele

■ Lehrstuhlbezeichnungen der Professoren, die alle von ihnen gelesenen

Vorlesungen auch schon einmal geprüft haben.
■ bzw. Lehrstuhlbezeichnungen von Professoren, so dass keine von diesem
gelesene Vorlesung existiert, die von ihm nicht geprüft wurde.
□ SELECT Lehrstuhlbezeichnung
FROM Prof
WHERE NOT EXISTS
(SELECT *
FROM Liest
WHERE [Link] = [Link]
AND NOT EXISTS (SELECT *
FROM Prüft Felix Naumann
WHERE Prü[Link] = [Link] Datenbanksysteme I
Sommer 2019
AND Prüft.VL_NR = Liest.VL_NR)
)

50
IN Beispiele

■ Eine Auswahl an Büchern

□ SELECT Titel
FROM Bücher
WHERE ISBN IN (‘3898644006‘, ‘1608452204‘, ‘0130319953‘)

■ Matrikel der Studenten, die zumindest einen Prüfer gemeinsam mit dem
Studenten der Matrikel ‚123456‘ haben
□ SELECT DISTINCT Matrikel
FROM Prüft
WHERE Prüfer IN ( SELECT Prüfer
FROM Prüft
WHERE Matrikel = ‘123456‘) Felix Naumann
Datenbanksysteme I

□ SELECT DISTINCT [Link] Sommer 2019

FROM Prüft P1, Prüft P2

WHERE [Link] = `123456`
AND [Link]üfer = [Link]üfer 51
IN Beispiele

■ Nachnamen aller Professoren, die schon einmal eine 1,0 vergeben haben.
□ SELECT Nachname
FROM Prof
WHERE 1.0 IN ( SELECT Note
FROM Prüft
WHERE Prüfer = [Link] )

■ Achtung: Korrelierte Subanfrage

Felix Naumann
Datenbanksysteme I
Sommer 2019

52
ALL und ANY Beispiele

■ Die schlechteste Note des Studenten mit Matrikel 123456

□ SELECT Note
FROM Prüft
WHERE Matrikel = ‘123456‘
AND Note >= ALL (SELECT Note
FROM Prüft
WHERE Matrikel = ‘123456‘)

■ Alle Studenten, die mindestens eine Prüfung absolvierten

□ SELECT Name, Matrikel
FROM Student
WHERE Matrikel = ANY (SELECT Matrikel Felix Naumann
Datenbanksysteme I
FROM Prüft) Sommer 2019

53
Bedingungen mit Tupeln

■ Verallgemeinerung von IN, ALL und ANY auf Tupel

□ t IN R
– TRUE falls t ein Tupel in R ist (mehr als ein Attribut möglich)
– Setzt gleiche Schemata voraus
– Setzt gleiche Reihenfolge der Attribute voraus
□ t > ALL R
– TRUE falls t größer als jedes Tupel in R ist
– Vergleiche in Standardreihenfolge der Attribute
□ t <> ANY R
– TRUE falls R mindestens ein Tupel hat, das ungleich t ist Felix Naumann
Datenbanksysteme I
Sommer 2019

54
Bedingungen mit Tupeln

■ Namen von Produzenten von Filmen mit Harrison Ford

□ SELECT Name
FROM Manager
WHERE ManagerID IN
( SELECT ProduzentID
FROM Film
WHERE (Titel, Jahr) IN
( SELECT FilmTitel AS Titel, FilmJahr AS Jahr
FROM spielt_in
WHERE SchauspielerName = ‘Harrison Ford‘
));
■ Analyse am besten von innen nach außen
■ Alternative Formulierung
□ SELECT Name Felix Naumann
FROM Manager, Film, spielt_in Datenbanksysteme I
WHERE ManagerID = ProduzentID Sommer 2019

AND Titel = FilmTitel

AND Jahr = FilmJahr
AND SchauspielerName = ‘Harrison Ford‘;
55
Subanfragen in FROM-Klausel

■ Bisher: Nur Subanfragen in WHERE-Klausel

□ Anstelle einfacher Relation steht eine geklammerte Subanfrage
□ Es muss ein Alias vergeben werden.

□ SELECT [Link]
FROM Manager M, (SELECT ProduzentID AS ID
FROM Film, spielt_in
WHERE Titel = FilmTitel
AND Jahr = FilmJahr
AND Schauspieler = ‘Harrison Ford‘) Produzent
WHERE [Link] = [Link];
Felix Naumann
Datenbanksysteme I
Sommer 2019

56
Korrelierte Subanfragen

■ Unkorreliert: Subanfragen einmalig ausführen und das Ergebnis

weiterverwenden
■ Korrelierte Subanfragen werden mehrfach ausgeführt, einmal pro Bindung
der korrelierten Variable der äußeren Anfrage

■ Alle mehrfachen Filme mit Ausnahme der jeweils jüngsten Ausgabe

□ SELECT Titel, Jahr Scope: Attributnamen
FROM Film Alt gehören zunächst zur
WHERE Jahr < ANY Tupelvariablen der aktuellen
( SELECT Jahr Anfrage. Sonst: Suche von
FROM Film innen nach außen.
WHERE Titel = [Link]); Felix Naumann
Datenbanksysteme I
Sommer 2019
□ Ausführung der Subanfrage für jedes Tupel in Filme

57
Korrelierte Subanfragen – Beispiel

■ Unkorreliert: ■ Korreliert:
Name und Gehalt aller Mitarbeiter in Name und Gehalt aller Mitarbeiter,
Potsdam deren Gehalt höher als 10% des
Abteilungsbudgets ist.

■ SELECT Name, Gehalt ■ SELECT Name, Gehalt

FROM Personal p FROM Personal p
WHERE AbtID IN WHERE Gehalt >
(SELECT AbtID (SELECT 0.1*Budget
FROM Abteilung FROM Abteilung a
WHERE Ort = ‘Potsdam‘) WHERE [Link] = [Link])
Felix Naumann
Datenbanksysteme I
Sommer 2019

58
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

59
Duplikateliminierung

■ Relationale DBMS verwenden i.d.R. Multimengensemantik, nicht

Mengensemantik.
□ Duplikate entstehen durch
– Einfügen von Duplikaten in Basisrelation
– Veränderung von Tupeln in Basisrelation
– Projektion in Anfragen
– Durch Subanfragen (UNION ALL)
– Vermehrung von Duplikaten durch Kreuzprodukt
■ Duplikateliminierung
□ SELECT DISTINCT Attributnamen
Felix Naumann
□ Kosten sind hoch: Sortierung oder hashing Datenbanksysteme I
Sommer 2019
■ Alle Filme, in denen mindestens ein Schauspieler mitspielt
□ SELECT DISTINCT Titel, Jahr
FROM spielt_in
60
Duplikateliminierung

SELECT ManagerID, Name SELECT ManagerID, Name

FROM Manager FROM Manager, Film, spielt_in
WHERE ManagerID IN Anfragen WHERE ManagerID = ProduzentID
( SELECT ProduzentID äquivalent? AND Titel = FilmTitel
FROM Film AND Jahr = FilmJahr
WHERE (Titel, Jahr) IN AND SchauspielerName =
( SELECT FilmTitel, ‘Harrison Ford‘;
FilmJahr
FROM spielt_in
WHERE SchauspielerName = SELECT DISTINCT ManagerID, Name
‘Harrison Ford‘ FROM Manager, Film, spielt_in
)); WHERE ManagerID = ProduzentID
AND Titel = FilmTitel
AND Jahr = FilmJahr
AND SchauspielerName = Felix Naumann
Datenbanksysteme I
‘Harrison Ford‘; Sommer 2019

61
Wdh.: Duplikateliminierung bei Mengenoperationen

■ Mengenoperationen in SQL entfernen Duplikate

□ UNION, INTERSECT, EXCEPT
□ wandeln Multimengen in Mengen um und verwenden Mengensemantik
□ Solche Duplikateliminierung verhindern durch ALL
– (SELECT Titel, Jahr, FROM Film)
UNION ALL
(SELECT FilmTitel AS Titel, FilmJahr AS Jahr FROM spielt_in);
– Film mit drei Schauspielern erscheint also 4 Mal im Ergebnis
□ R INTERSECT ALL S
□ R EXCEPT ALL S
Felix Naumann
Datenbanksysteme I
Sommer 2019

62
Aggregation

■ Standardaggregationsoperatoren
□ SUM, AVG, MIN, MAX, COUNT
□ Angewendet auf einzelne Attribute in der SELECT-Klausel
■ Typische weitere Aggregationsoperatoren
□ VAR, STDDEV
■ COUNT(*) zählt Anzahl der Tupel
□ in der Relation, die durch die FROM und WHERE Klauseln definiert wird.

■ Kombination mit DISTINCT

□ COUNT(DISTINCT Jahr) Felix Naumann
□ SUM(DISTINCT Gehalt) Datenbanksysteme I
Sommer 2019

63
Aggregation – Beispiele

■ SELECT AVG(Gehalt)
FROM Manager;

■ SELECT COUNT(*)
FROM spielt_in;

■ SELECT COUNT(Schauspieler)
FROM spielt_in;

■ SELECT COUNT(DISTINCT Schauspieler)

FROM spielt_in Felix Naumann
WHERE Jahr = 1990; Datenbanksysteme I
Sommer 2019

64
Gruppierung, Aggregation und NULL

■ NULL wird bei Aggregation ignoriert.

□ Trägt also nicht zu SUM, AVG oder COUNT bei.
□ Ist nicht MIN oder MAX
□ Anzahl Tupel: SELECT COUNT(*) FROM spielt_in;
□ Anzahl nicht-NULL Werte: SELECT COUNT(Länge) FROM Film;

■ NULL ist ein eigener Gruppierungswert

□ Es gibt also z.B. die NULL-Gruppe
□ SELECT A, COUNT(B) FROM R GROUP BY A; A B
– Ergebnis: (NULL, 0) NULL NULL Felix Naumann
□ SELECT A, SUM(B) FROM R GROUP BY A; Datenbanksysteme I
Sommer 2019

– Ergebnis: (NULL, NULL)

65
Gruppierung

■ Gruppierung mittels GROUP BY nach der WHERE-Klausel

■ SELECT StudioName, SUM(Länge)
FROM Film
GROUP BY StudioName
■ In SELECT-Klausel zwei „Sorten“ von Attributen
1. Gruppierungsattribute
2. Aggregierte Attribute
□ Nicht-aggregierte Werte der SELECT-Klausel müssen in der GROUP BY-
Klausel erscheinen.
□ Keine der beiden Sorten muss erscheinen.
■ SELECT StudioName Felix Naumann
Datenbanksysteme I
FROM Film Sommer 2019
GROUP BY StudioName
■ SELECT SUM(Länge)
FROM Film 66
GROUP BY StudioName
Gruppierung

■ Gruppierung bei Verwendung mehrerer Relationen wird am Schluss

durchgeführt.
□ SELECT Name, SUM(Länge)
FROM Manager, Film
WHERE ManagerID = ProduzentID
GROUP BY Name

■ Reihenfolge der Ausführung (und des Lesens)

1. FROM-Klausel
2. WHERE-Klausel
3. GROUP BY-Klausel Felix Naumann
Datenbanksysteme I
4. SELECT-Klausel Sommer 2019

67
Gruppierung

■ Einschränkung der Ergebnismenge nach der Gruppierung durch HAVING

□ SELECT Name, SUM(Länge)
FROM Manager, Film
WHERE ManagerID = ProduzentID
AND Gehalt > 1000000
GROUP BY Name
□ SELECT Name, SUM(Länge)
FROM Manager, Film
WHERE ManagerID = ProduzentID
GROUP BY Name
HAVING SUM(Länge) > 1000
□ SELECT Name
FROM Manager, Film
WHERE ManagerID = ProduzentID Felix Naumann
GROUP BY Name Datenbanksysteme I
Sommer 2019
HAVING SUM(Länge) > 1000
■ Aggregationen in HAVING-Klausel beziehen sich nur auf aktuelle Gruppe.
■ Nur Gruppierungsattribute dürfen un-aggregiert in HAVING Klausel erscheinen
(wie bei SELECT-Klausel). 68
Zusammenfassung SQL

■ Grundbausteine einer SQL Anfrage (mit empfohlener Lesereihenfolge)

□ 6. SELECT
□ 1. FROM
□ 2. WHERE
□ 3. GROUP BY
□ 4. HAVING
□ 5. ORDER BY

■ SELECT … FROM … sind Pflicht.

□ Ausnahme: z.B. SELECT 7 + 3 Felix Naumann
■ HAVING darf nur in Kombination mit GROUP BY erscheinen. Datenbanksysteme I
Sommer 2019

69
Felix Naumann
Datenbanksysteme I
Sommer 2019

70
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

71
Überblick

■ CRUD: Create, Read, Update, Delete

■ Einfügen
□ INSERT INTO … VALUES…

■ Löschen
□ DELETE FROM … WHERE …

■ Ändern
□ UPDATE … SET … WHERE … Felix Naumann
Datenbanksysteme I
Sommer 2019

72
Einfügen

■ Grundbaustein
□ INSERT INTO R(A1, …, An) VALUES (v1,…,vn);

□ Bei fehlenden Attributen

– Default-Wert aus Tabellendefinition (NULL, falls nicht anders angegeben)
□ Beispiel
– INSERT INTO spielt_in(FilmTitel, FilmJahr, Schauspieler)
VALUES (‘Star Wars‘, 1977, ‘Alec Guinness‘);
– Reihenfolge der Werte und Attribute wird beachtet.
□ Falls alle Attribute gesetzt werden, kann Attributliste fehlen:
– INSERT INTO spielt_in
VALUES (‘Star Wars‘, 1977, ‘Alec Guinness‘);
Felix Naumann
Datenbanksysteme I
Sommer 2019
– Reihenfolge entsprechend der Spezifikation des Schemas
(CREATE TABLE …)

73
Einfügen per Anfrage

■ Füge in Studio-Tabelle alle Studios der Filme-Relation ein

□ Film(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)
□ Studio(Name, Adresse, VorsitzenderID)

□ INSERT INTO Studio(Name)

SELECT DISTINCT StudioName
FROM Film Adresse und VorsitzenderID
WHERE StudioName NOT IN bleiben NULL.
(SELECT Name
FROM Studio);

Felix Naumann
Datenbanksysteme I
□ Erzeugt im Allgemeinen Redundanz und sollte vermieden werden. Sommer 2019

74
Ausführungsreihenfolge beim Einfügen

■ Wann wird eingefügt? INSERT INTO Studio(Name)

□ Nach vollständiger Ausführung der SELECT DISTINCT StudioName
SELECT FROM WHERE Anfrage? FROM Film
□ Sofort? WHERE StudioName NOT IN
– Schnellere Implementation
(SELECT Name
FROM Studio);

□ Was passiert jeweils bei Anfrage 1?

□ Was passiert jeweils bei Anfrage 2? INSERT INTO Studio(Name)
SELECT StudioName
FROM Film
■ SQL Standard: Erst gesamte Anfrage WHERE StudioName NOT IN
ausführen
(SELECT Name Felix Naumann
FROM Studio); Datenbanksysteme I
Sommer 2019

75
Bulk insert

■ INSERT
□ Zeilenbasiertes Einfügen aus SQL statements
■ IMPORT
□ Zeilen-basiertes Einfügen aus Datei
□ Trigger und Nebenbedingungen bleiben aktiv
□ Indizes werden laufend aktualisiert
■ LOAD
□ Seiten-basiertes Einfügen aus Datei
□ Trigger und Nebenbedingungen werden deaktiviert
□ Deutlich effizienter Felix Naumann
Datenbanksysteme I
□ Indizes werden am Ende neu generiert Sommer 2019

■ Syntax ist jeweils DBMS-spezifisch.

□ Viele Parameter 76
Löschen

■ Grundbaustein
□ DELETE FROM R WHERE …
□ Lösche alle Tupel in R, für die die Bedingung wahr ist.
□ DELETE FROM spielt_in
WHERE FilmTitel = ‘The Maltese Falcon‘
AND FilmJahr = 1942
AND Schauspieler = ‘Sydney Greenstreet‘;

■ Tupel können im Gegensatz zum Einfügen nicht direkt angegeben werden,

sondern müssen umschrieben werden.
□ DELETE FROM Manager Felix Naumann
Datenbanksysteme I
WHERE Gehalt < 10000000; Sommer 2019

■ Alle Manager-Tupel löschen: DELETE FROM Manager;

77
Verändern (update)

■ Grundbaustein
□ UPDATE R SET … WHERE …

□ SET Klausel
– Wertzuweisungen
– Komma-separiert

□ UPDATE Manager
SET Name = ‘Präs. ‘ || Name
WHERE ManagerID IN
(SELECT PräsidentID FROM Studios); Felix Naumann
Datenbanksysteme I
Sommer 2019

78
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

79
Überblick

■ Datentypen
■ Tabellen
■ Default-Werte
■ Indizes

Felix Naumann
Datenbanksysteme I
Sommer 2019

80
Datentypen

■ Jedes Attribut muss einen Datentyp haben.

□ CHAR(n)
– String fester Länge (n Zeichen)
□ VARCHAR(n)
– String variabler Länge, maximal n Zeichen
□ BIT(n) bzw. BIT VARYING(n)
– Wie CHAR, aber Bits
□ BOOLEAN
– TRUE, FALSE oder UNKNOWN
□ INT / INTEGER bzw. SHORTINT
– 8 bzw. 4 Byte
□ FLOAT / REAL bzw. DOUBLE PRECISION Felix Naumann
Datenbanksysteme I
□ DECIMAL (n,d) Sommer 2019
– Z.B. Gehalt DECIMAL(7,2) – 7 Stellen,
davon 2 Nachkommastellen
□ CLOB und BLOB
81
Überblick DB2 Datentypen

Felix Naumann
Datenbanksysteme I
Sommer 2019

82

[Link]
Tabellen

■ Grundbaustein zum Erzeugen

□ CREATE TABLE R …
□ CREATE TABLE Schauspieler (
Name CHAR(30),
Adresse VARCHAR(255),
Geschlecht CHAR(1),
Geburtstag DATE );
■ Löschen
□ DROP TABLE Schauspieler;
■ Verändern
□ ALTER TABLE Schauspieler ADD Telefon CHAR(6); Felix Naumann
Datenbanksysteme I
Sommer 2019
– Nullwerte entstehen
□ ALTER TABLE Schauspieler DROP Geburtstag;
□ ALTER TABLE Schauspieler MODIFY Telefon CHAR(10);
83
Default-Werte

■ CREATE TABLE Schauspieler (

Name CHAR(30),
Adresse VARCHAR(255),
Geschlecht CHAR(1) DEFAULT ‚?‘,
Geburtstag DATE DEFAULT DATE ‚0000-00-00‘);

■ ALTER TABLE Schauspieler

ADD Telefon CHAR(16) DEFAULT ‚unbekannt‘;

Felix Naumann
Datenbanksysteme I
Sommer 2019

84
Constraints und Trigger

■ Weitere Optionen für Tabellen

□ PRIMARY KEY
□ UNIQUE
□ FOREIGN KEY … REFERENCES …
□ NOT NULL
□ CHECK
□ CREATE ASSERTION
□ CREATE TRIGGER

■ Siehe separater Foliensatz Felix Naumann

Datenbanksysteme I
Sommer 2019

85
Indizes

■ Ein Index auf einem Attribut ist eine Datenstruktur, die es dem DBMS erleichtert,
Tupel mit einem bekannten Wert des Attributs zu finden.
□ Nicht SQL-Standard, aber in (fast) jedem DBMS verfügbar.

■ Motivation
□ SELECT *
FROM Film
WHERE StudioName = ‘Disney‘ AND Jahr = ‘1990‘;
□ Variante 1: Alle 100.000 Tupel durchsuchen und WHERE Bedingung prüfen
□ Variante 2: Direkt alle 2000 Filme aus 1990 betrachten und auf ‚Disney‘ prüfen.
– CREATE INDEX JahrIndex ON Film(Jahr); Felix Naumann
Datenbanksysteme I
□ Variante 3: Direkt alle 100 Filme aus 1990 von ‘Disney‘ holen. Sommer 2019

– CREATE INDEX JahrStudioIndex

ON Film(Jahr, Studioname);
86
Indizes

■ Indizes auf einzelnen Attributen

□ CREATE INDEX JahrIndex ON Film(Jahr);

■ Indizes auf mehreren Attributen

□ CREATE INDEX JahrStudioIndex
ON Film(Jahr, Studioname);
□ Reihenfolge wichtig! Warum?

■ Löschen
□ DROP INDEX JahrIndex;
Felix Naumann
Datenbanksysteme I
Sommer 2019

87
Indexwahl

■ Abwägung
□ Index beschleunigt Punkt- (und Bereichs-) Anfragen und Join-Anfragen
erheblich.
□ Index verlangsamt das Einfügen, Löschen und Verändern von Tupeln der
Relation.
– Index muss jeweils zusätzlich aktualisiert werden.
□ Indizes benötigen Speicherplatz.

■ Wahl der besten Indizes ist eine der schwierigsten Aufgaben des
Datenbankdesigns.
□ Vorhersage der query workload und update-Frequenz Felix Naumann
Datenbanksysteme I
□ Wahl der Attribute Sommer 2019

– Häufiger Vergleich mit Konstanten

– Häufiges Joinattribut
88
Indexwahl – Vorüberlegungen

■ Relationen sind typischerweise über mehrere Diskblöcke gespeichert.

□ Wichtigste Datenbankkosten sind die Anzahl der Diskblöcke, die in den
Hauptspeicher gelesen werden müssen.
□ Bei Punktanfragen mit Index müssen statt aller Blöcke nur ein Block
gelesen werden.
□ Aber Index selbst muss ebenfalls gespeichert und gelesen werden.
– IdR viel mehr Tupel pro Block repräsentiert: Nur Schlüsselwert und
Speicheradresse, keine Daten
□ Updates kosten sogar doppelt: Lesen und Schreiben auch der Index-Blöcke

Felix Naumann
Datenbanksysteme I
Sommer 2019

89
Indexwahl – Beispiel

■ spielt_in(FilmTitel, FilmJahr, Schauspieler)

■ Drei typische Anfragen
□ SELECT FilmTitel, FilmJahr FROM spielt_in
WHERE Schauspieler = s;
□ SELECT Schauspieler FROM spielt_in
WHERE FilmTitel = t AND FilmJahr = j;
□ INSERT INTO spielt_in VALUES(t, j, s);
■ Annahmen
□ spielt_in ist auf 10 Disk-Blöcke verteilt.
□ Durchschnittlich habe jeder Film 3 Schauspieler.
Wird gleich noch geprüft
□ Durchschnittlich spiele jeder Schauspieler in 3 Filmen. Felix Naumann
Datenbanksysteme I
□ Annahme des Schlimmsten: 3 Tupel sind auf 3 Blöcke verteilt Sommer 2019
□ Index ist auf 1 Block gespeichert.
□ Lesen und Schreiben kostet 1.
□ Update und Insert kosten jeweils 2. 90
Indexwahl – Beispiel

Kein Index Schauspie- Film- Beide

Anfrage lerIndex Index Indizes

Schauspieler = s 10 4 10 4

FilmTitel = t 10 10 4 4
AND FilmJahr = j
INSERT INTO spielt_in 2 4 4 6

Gesamtkosten 2+8p1+8p2 4+6p2 4+6p1 6-2p1-2p2

■ p1: Anteil Anfrage 1

■ p2: Anteil Anfrage 2 Felix Naumann
Datenbanksysteme I
■ 1‒p1‒p2: Anteil Anfrage 3 Sommer 2019

91
Verteilung in IMDB
(Real-World Daten)

Felix Naumann
Datenbanksysteme I
Sommer 2019

92
Verteilung in IMDB
(Real-world Daten, Stand ca. 2010)

■ WITH
m AS
(SELECT count(*) AS ZahlMovies FROM [Link]),
actress AS
(SELECT count(*) AS ZahlActress FROM [Link]),
actor AS
(SELECT count(*) AS ZahlActor FROM [Link]),
actors AS
(SELECT (ZahlActress + ZahlActor) AS GesamtActors
FROM actress, actor)
SELECT DOUBLE([Link]) / DOUBLE([Link])
FROM m, actors
■ Schauspieler pro Spielfilm: 8,7
■ WITH
actors AS (SELECT * FROM [Link] UNION
SELECT * FROM [Link]),
counts AS (SELECT name, count(movie_id) AS m Felix Naumann
FROM actors GROUP BY name) Datenbanksysteme I
Sommer 2019
SELECT AVG(DOUBLE(m)) FROM counts
■ Spielfilme pro Schauspieler: 4,2

93
Überblick

1. Einfache Anfragen
2. Anfragen über mehrere Relationen
3. Geschachtelte Anfragen
4. Operationen auf einer Relation
5. Datenbearbeitung (DML)
6. Schemata (DDL)
7. Sichten

Felix Naumann
Datenbanksysteme I
Sommer 2019

94
Virtuelle Relationen

■ Relationen aus CREATE TABLE Ausdrücken existieren tatsächlich

(materialisiert, physisch) in der Datenbank.
□ Persistenz
□ Updates sind möglich

■ Die Daten aus Sichten (views) existieren nur virtuell.

□ Sichten entsprechen Anfragen, denen man einen Namen gibt. Sie wirken
wie physische Relationen.
□ Updates sind nur manchmal möglich.

Felix Naumann
Datenbanksysteme I
Sommer 2019

95
Michael Stonebraker: Implementation of Integrity Constraints and
Views by Query Modification. SIGMOD Conference 1975: 65-78

Felix Naumann
Datenbanksysteme I
Sommer 2019

96
Sichten in SQL

■ CREATE VIEW Name AS Anfrage

■ CREATE VIEW ParamountFilme AS
SELECT Titel, Jahr
FROM Film
WHERE StudioName = ‘Paramount‘;
□ Auch mehr als eine Relation möglich!
■ Bedeutung einer Anfrage an die Sicht
1. Ausführung der Anfrage aus der Sichdefinition
2. Die ursprüngliche Anfrage verwendet dann das Ergebnis als Relation.

■ Daten der Sicht ändern sich mit der Änderung der zugrundeliegenden Relationen.
■ Entfernen der Sicht: DROP VIEW ParamountFilme Felix Naumann
Datenbanksysteme I
□ Basisdaten bleiben unverändert. Sommer 2019

97
Anfragen an Sichten

■ CREATE VIEW ParamountFilme AS

SELECT Titel, Jahr
FROM Film
WHERE StudioName = ‘Paramount‘;
■ SELECT Titel
FROM ParamountFilme
WHERE Jahr = 1979;
■ Umwandlung der ursprünglichen Anfrage in eine Anfrage an Basisrelationen
□ SELECT Titel
FROM Film
WHERE StudioName = ‘Paramount‘ AND Jahr = 1979;
□ Übersetzung durch DBMS
Felix Naumann
■ Anfrage zugleich an Sichten und Basisrelationen möglich Datenbanksysteme I
Sommer 2019
□ SELECT DISTINCT Schauspieler
FROM ParamountFilme, spielt_in
WHERE Titel = FilmTitel AND Jahr = FilmJahr;
98
Anfragen an Sichten

■ Film(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)

■ Manager(Name, Adresse, ManagerID, Gehalt)
■ CREATE VIEW FilmeProduzenten AS
SELECT Titel, Name
FROM Film, Manager
WHERE ProduzentID = ManagerID;
■ Anfrage
□ SELECT Name
FROM FilmeProduzenten
WHERE Titel = ‘Gone with the Wind‘
■ Bedeutung
□ SELECT Name Felix Naumann
Datenbanksysteme I
FROM Film, Manager Sommer 2019
WHERE ProduzentID = ManagerID
AND Titel = ‘Gone with the Wind‘;

99
Attributumbenennung mittels Sichten

■ Nebenbei: Umbenennung von Attributen

□ CREATE VIEW FilmeProduzenten(FilmTitel, Produzentenname) AS
SELECT Titel, Name
FROM Film, Manager
WHERE ProduzentID = ManagerID;

■ Oder auch: Sicht einfach nur zur Umbenennung

□ CREATE VIEW Movie(title, year, length, inColor, studio,
producerID) AS
SELECT *
FROM Film;
Felix Naumann
Datenbanksysteme I
Sommer 2019

100
Diskussion

■ Vorteile
□ Vereinfachung von Anfragen
□ Strukturierung der Datenbank
□ Logische Datenunabhängigkeit
– Sichten stabil bei Änderungen der Datenbankstruktur
– Sichtdefinitionen müssen gegebenenfalls angepasst werden
– Stabilität nicht bei jeder Änderung
□ Beschränkung von Zugriffen (Datenschutz)
□ Optimierung durch materialisierte Sichten

Felix Naumann
■ Probleme Datenbanksysteme I
Sommer 2019
□ Automatische Anfragetransformation schwierig
□ Änderungen auf Sichten
□ Updatepropagierung für materialisierte Sichten
101
Updates auf Sichten

■ In einigen Fällen ist es möglich, Einfüge-, Lösch- oder Updateoperationen auf Sichten
durchzuführen.
□ Wo speichern?
□ Welche Relation?
□ Zuordnung der Änderung zur Sicht?

■ Übersetzung der Update-Operation auf eine Update-Operation der zugrunde liegenden

Basisrelationen
□ Nur bei einer Relation
– Keine Subanfragen mit Selbstbezug
□ Nur bei normalem SELECT
Felix Naumann
– Kein DISTINCT Datenbanksysteme I
Sommer 2019
□ Nur falls genug Attribute verwendet werden, so dass alle anderen Attribute mit NULL
oder dem Default-Wert gefüllt werden können.

102
Einfügen auf Sichten – Beispiel

■ Filme(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)

■ CREATE VIEW ParamountFilme AS
SELECT Titel, Jahr
FROM Filme
WHERE StudioName = ‚Paramount‘;
■ INSERT INTO ParamountFilme
VALUES (‚Star Trek‘, 1979);
□ Wert für Studioname?
□ Einfügen also nicht erlaubt.
■ CREATE VIEW ParamountFilme AS
SELECT Titel, Jahr, StudioName
FROM Filme Dies muss Paramount sein.
WHERE StudioName = ‚Paramount‘; Ein anderes Studio Felix Naumann
■ INSERT INTO ParamountFilme entspricht nicht der View. Datenbanksysteme I
Sommer 2019
VALUES (‚Star Trek‘, 1979, ‚Paramount‘);
■ Neues Tupel (‚Star Trek‘, 1979, 0, NULL, ‚Paramount‘, NULL)

103
Löschen und Updates auf Sichten

■ Löschen
□ DELETE FROM ParamountFilme
WHERE Titel LIKE ‚%Trek%‘;
■ Wird umgeschrieben zu
□ DELETE FROM Filme
WHERE Titel LIKE ‚%Trek%‘ AND StudioName = ‚Paramount‘;
■ Update
□ UPDATE ParamountFilme
SET Jahr = 1979
WHERE Titel = ‚Star Trek the Movie‘;
■ Wird zu
□ UPDATE Filme Felix Naumann
Datenbanksysteme I
SET Jahr = 1979 Sommer 2019

WHERE Titel = ‚Star Trek the Movie‘ AND StudioName = ‚Paramount‘;

104
Tupelmigration

■ Manager(Name, Adresse, ManagerID, Gehalt)

□ CREATE VIEW Reiche AS
SELECT Name, Gehalt, ManagerID
FROM Manager
WHERE Gehalt > 2000000;
■ Tupelmigration:
□ Ein Tupel (‚Eisner`, ‚Hollywood‘, 25, 3000000) wird aus der Sicht
„herausbewegt“.
□ UPDATE Reiche SET Gehalt = 1500000
WHERE ManagerID = 25;
■ Vorsicht bei der Implementierung, oder explizite Verhinderung:
□ CREATE VIEW Reiche AS
SELECT Name, Gehalt Felix Naumann
FROM Manager Datenbanksysteme I
Sommer 2019
WHERE Gehalt > 2000000
WITH CHECK OPTION;
□ Verhindert Tupelmigration durch Ablehnung problematischer Updates.
105
Zusammenfassung: Einschränkungen für Sichtänderungen

■ Änderbar nur Selektions- und Projektionssichten

□ Join und Mengenoperationen nicht erlaubt
■ 1:1-Zuordnung von Sichttupeln zu Basistupeln
□ Kein DISTINCT in Projektionssichten
■ Keine Arithmetik und Aggregatfunktionen im SELECT
■ Genau eine Referenz auf einen Relationennamen im FROM
□ Auch kein self-JOIN
■ Keine korrellierten Subanfragen
□ Relationsname im obersten SFW-Block nicht in FROM-Klauseln von
Subanfragen verwenden
Felix Naumann
■ GROUP BY und HAVING verboten Datenbanksysteme I
Sommer 2019

106
Anfrageplanung mit Sichten

■ Baumdarstellung von Anfragen

□ Blätter repräsentieren Relationen
– Basisrelationen
– Sichten
□ Ersetzung der Sichten durch die Sichtdefinition
– Als Subanfrage

Q Q Felix Naumann
Datenbanksysteme I
Sommer 2019
V W
Def. V Def. W

Z
107
Anfrageplanung mit Sichten

■ Sicht
□ CREATE VIEW ParamountFilme AS
SELECT Titel, Jahr
FROM Filme
WHERE StudioName = ‚Paramount‘; πTitel

■ Anfrage
□ SELECT Titel σJahr = 1979
FROM ParamountFilme
WHERE Jahr = 1979;
πTitel,Jahr πTitel πTitel,Jahr
Felix Naumann
Datenbanksysteme I
Sommer 2019
σStudioName = ‚Paramount‘ σJahr = 1979 σStudioName = ‚Paramount‘

Filme ParamountFilme Filme 108

Materialisierte Sichten

■ Viele Anfragen an eine Datenbank wiederholen sich häufig

□ Business Reports, Bilanzen, Umsätze
□ Bestellungsplanung, Produktionsplanung
□ Kennzahlenberechnung
■ Viele Anfragen sind Variationen mit gemeinsamem Kern
■ Idee: Einmaliges Berechnen der Anfrage als Sicht
□ Automatische, transparente Verwendung in folgenden Anfragen
□ Materialisierte Sicht (materialized view, MV)

Felix Naumann
Datenbanksysteme I
Sommer 2019

109
Drei Folien nach Prof. Ulf Leser, HU Berlin
MV – Themen und Probleme

■ Wahl von Views zur Materialisierung

□ MVs kosten: Platz und Aktualisierungsaufwand
□ Wahl der optimalen MVs hängt von Workload ab
□ Auswahl der „optimalen“ Menge von MVs
■ Automatische Aktualisierung von MVs
□ Aktualisierung bei Änderungen der Basisrelationen
□ U.U. schwierig: Aggregate, Joins, Outer-Joins, ...
□ Algorithmen zur inkrementellen Aktualisierung
■ Automatische Verwendung von MV
□ „Answering Queries using Views“
Felix Naumann
□ Umschreiben der Anfrage notwendig Datenbanksysteme I
Sommer 2019
□ Schwierigkeit hängt von Komplexität der Anfrage / Views ab
□ Algorithmen zur transparenten und kostenoptimalen Verwendung der materialisierten
Sichten
110
„Answering Queries using Views“

■ Gegeben
□ Eine Anfrage Q
□ Eine Menge V (materialisierten) von Sichten

■ Fragen
□ Kann man Q überhaupt unter Verwendung von V beantworten?
□ Kann man Q nur mit V beantworten?
□ Kann man Q mit V vollständig beantworten?
□ Ist es günstig, Sichten aus V zur Beantwortung von Q zu verwenden?
Welche?
Felix Naumann
Datenbanksysteme I
Sommer 2019

111
Local-as-View – Beispiel

Globales Schema
Lehrt(prof,kurs_id, sem, eval, univ)
Kurs(kurs_id, titel, univ)
Quelle 1: Alle Datenbankveranstalt. Quelle 2: Alle HPI-Vorlesungen
CREATE VIEW DB-kurs AS CREATE VIEW HPI-VL AS
SELECT [Link], [Link], K.kurs_id, [Link] SELECT [Link], [Link], K.kurs_id, [Link]
FROM Lehrt L, Kurs K FROM Lehrt L, Kurs K
WHERE L.kurs_id = K.kurs_id WHERE L.kurs_id = K.kurs_id
AND [Link] = [Link] AND [Link] = „HPI“
AND [Link] LIKE „%_Datenbanken“ AND [Link] = „HPI“
AND [Link] LIKE „%VL_%“
Globale Anfrage
SELECT prof
Umgeschriebene Anfrage
FROM Lehrt L, Kurs K Felix Naumann

WHERE L.kurs_id = K.kurs_id SELECT prof Datenbanksysteme I

Sommer 2019
AND [Link] LIKE „%_Datenbanken“ FROM DB-kurs D
AND [Link] = „HPI“ WHERE [Link] = „HPI“
Frage: Warum nicht Quelle 2 einbeziehen?
112
Antwort: Weil Quelle 1 ja schon ALLE DB-Veranstaltungen liefert.
Local-as-View – Beispiel

Ausschnitt Globales Schema

Lehrt(prof,kurs_id, sem, eval, univ)
Kurs(kurs_id, titel, univ)

Quelle 1: Alle Datenbankveranstalt. Quelle 2: Alle HPI-Vorlesungen

CREATE VIEW DB-kurs AS CREATE VIEW HPI-VL AS
SELECT [Link], [Link], K.kurs_id, [Link] SELECT [Link], [Link], K.kurs_id, [Link]
FROM Lehrt L, Kurs K FROM Lehrt L, Kurs K
WHERE L.kurs_id = K.kurs_id WHERE L.kurs_id = K.kurs_id
AND [Link] = [Link] AND [Link] = „HPI“
AND [Link] LIKE „%_Datenbanken“ AND [Link] = „HPI“
AND [Link] LIKE „%VL_%“

Umgeschriebene Anfrage
Globale Anfrage SELECT titel, kurs_id
SELECT titel, kurs_id FROM DB-kurs D
Felix Naumann
Datenbanksysteme I
FROM Kurs WHERE [Link] = „HPI“ Sommer 2019
WHERE univ = „HPI“ UNION
SELECT titel, kurs_id Frage:
FROM HPI-VL Warum hier doch
113
Quelle 2 einbeziehen?
Zusammenfassung

■ Die Anfragesprache SQL ■ Gruppierung und Aggregation

■ Der SFW Block □ MIN, MAX, COUNT
■ Subanfragen □ GROUP BY, HAVING
□ In FROM und WHERE ■ Datenbankveränderungen
□ EXISTS, IN, ALL, ANY □ INSERT, UPDATE, DELETE
■ Mengenoperationen ■ Schemata und Datentypen
□ UNION, INTERSECT, EXCEPT □ CREATE TABLE
■ Joins und Outerjoins □ ALTER TABLE
■ Nullwerte ■ Indizes
■ Mengen vs. Multimengen ■ Sichten Felix Naumann
Datenbanksysteme I
□ DISTINCT, ALL □ Anfragen (und updates) Sommer 2019

□ Materialisierte Sichten

114