1.4.2 SQL-DML (3) - Hochschule Niederrhein

Themen (2)
1 Grundlagen
Datenbanksysteme
Definition und Bedeutung von DB’s
Klassifikation von DB-Systemen
Relationale Datenbanken
Einführung in SQL
Clientseitige Programmierung
Prof. Dr. Christoph Dalitz
Sommersemester 2012
2 Datenmodelle
Drei Schichten Modell und Entwurfsmethodik
Relationales Modell
Abhängigkeiten und Normalformen
relationale Algebra
Semantische Modelle (z.B. Entity-Relationship)
Vorlesung an der
Hochschule Niederrhein
1
Themen (1)
Themen (3)
3 Fortgeschrittene Themen
Praxis−
Anforderungen
Anwendungs−
programm
3
Weitere Datenobjekte (Sequence, View...)
Serverseitige Programmierung (PL/SQL, Trigger)
Datenbank Tuning
Datensicherheit und Transaktionen
Verwal−
tung
Speich−
erung
Nicht behandelte Themen:
DBS
1) Datenbankpraxis
Was bietet ein DBS? Wie nutzt man es?
2) Datenmodellierung
Umsetzung realer Datenstruktur in relationale DB
3) DBS-Implementierung
Wie ist ein DBS intern implementiert?
Interne Implementierung von Datenbanksystemen
Speicherstrukturen und Indizes
Recovery bei Systemabsturz
Abfrageverarbeitung
verteilte Datenbanken
objektorientierte Datenbanken
2
4
Dalitz DBS Kap1.1-3
Literatur
1.1 Definition, Bedeutung (2)
Vorlesung:
DBS−Architektur
Elmasri, Navathe: Grundlagen von Datenbanksystemen.
Pearson Studium 2005 (3. Auflage, Grundstudiumsausgabe)
Garcia-Molina, Ullman, Widom: Database Systems The Complete Book. Prentice Hall 2002
Abfrage,
Manipulation
Struktur−
definition
DML
Prozessor
DDL
Prozessor
Optimizer
D
B
S
Praktikum:
PostgreSQL Online-Dokumentation:
http://www.postgresql.org/docs/
Datenbank
Manager
Mehrbenutzer
Synchronisation
RDBMS
Matthew, Stones: Beginning Databases with PostgreSQL.
Apress 2005
Logbuch
Indizes
Daten−
basis
System
Katalog
Speicher
5
1.1 Definition, Bedeutung (1)
7
1.2 Klassifikation (1)
Aufgaben eines DBS:
Einteilung von "Datenbank-Systemen"
nach verschiedenen Kriterien
Datendefinition
Data Definition Laguage (DDL)
System Catalog (Data Dictionary)
Leistungsumfang
Datenmanipulation
Welche Aufgaben eines DBS sind implementiert?
=> Desktop, Client-Server, verteilte Datenbank
Data Manipulation Language (DML)
Abfrageoptimierung
Datenschutz
logische Datensicht ("Datenmodell")
Benutzerrechte und Zugriffsbeschränkung
Wie präsentieren sich Daten aus Sicht des Anwenders?
=> hierarchisch, relational, objektorientiert
Datensicherheit
Konsistenzprüfung, Integrity Constraints
Transaktionen
Concurrency Control
Recovery bei Systemfehlern
6
8
Dalitz DBS Kap1.1-3
1.2 Klassifikation (2)
1.2 Klassifikation (4)
KD# Kunde
Pgm2
Pgm1
BSD
Sockets
Client 1
Domain Sockets
VS# Versicherg.
BK# Bankverb.
DBMS
KS# Klausel
BSD
Sockets
Pgm1
Hierarchisches Modell
DB
1969 von IBM mit IMS eingeführt
Datensätze in geordnetem Baum gespeichert
Zugriff durch Direktzugriff über Schlüssel
und Manövrieren in Baumhierarchie
Server
Client 2
Client-Server DBS
DBS eigenständiger Prozess
Zugriff über IPC-Mechanismen
Multi-User Systeme
=> Abhängigkeit von physischer Datenstruktur
9
1.2 Klassifikation (3)
1.2 Klassifikation (5)
Pgm
Pgm
KD# Kunde
Pgm
DB−Lib
DB−Lib
11
DB−Lib
VS# KD# Versicherg.
BK# KD# Bankverb.
KD# Kunde
Client 1
DB
Desktop
Rechner
Client 2
VS# KD# Versicherg.
KS# VS# Klausel
DB
Fileserver
VS# KD# Versicherg. Kunde
Relationales Modell
Desktop Datenbank
1970 von Codd vorgeschlagen
Daten in Tabellen gespeichert
Zugriff durch Erzeugung von neuen Tabellen aus alten
Direktzugriff auf Daten über dazugelinkte Library-Routinenen
Mehrbenutzersynchronisation nur über primitives Filelocking
Single-User Systeme
10
=> physische Datenunabhängigkeit
12
Dalitz DBS Kap1.1-3
1.2 Klassifikation (6)
1.3 Relationale Datenbanken
Objektorientierte Datenbanken
Aspekte des relationalen Modells
seit 1985 für komplexe Anforderungen (z.B. CAD) entwickelt
Daten in benutzerdefinierten Strukturen gespeichert
Zugriff über benutzerdefinierte Funktionen
DBS ist "Bausatz" für eigene DB-Implementierung
Daten-Struktur:
Anwender sieht Daten als Tabellen
=> Abhängigkeit von Klassenimplementierung
Daten-Integrität:
Tabellen erfüllen Integritätsbedingungen
Objekt-Relationale Datenbanken
relationales Modell
objektorientierte Erweiterungen:
benutzerdefinierte Datentypen + Operatoren, Vererbung
Daten-Manipulation:
Operatoren werden auf Tabellen angewendet
Output ist wieder eine Tabelle
=> beide Welten vereinigt
13
1.2 Klassifikation (7)
Name
Oracle
Hersteller
Oracle
Informix
DB2
Interbase
Sybase
Access
(Informix)
IBM
Borland
Sybase
Microsoft
MS SQL-Server
mySQL
Microsoft
freie Software
PostgreSQL
SQLite
freie Software
freie Software
1.3.1 Struktur (1)
Bemerkung
Marktführer, sehr teuer, viele
Highend-Features, alle Plattformen (AS400, Unix, Win32)
von IBM gekauft
Das relationale Modell
Aus Benutzersicht besteht DB aus Tabellen
(logische Struktur). Physische Struktur bleibt
dem Benutzer verborgen.
jetzt als “Firebird” OpenSource
Desktop-Datenbank mit umfangreicher Benutzeroberfläche
nur Win32
Teilfunktionalität, speziell für
manche Web-DB’s hinreichend
verbreitet als embedded
SQL Datenbank
15
Kompletter Informationsgehalt dargestellt
in einer Form: als Feldwerte in Tabellenzeilen.
(Date: "The Information Principle")
14
16
Dalitz DBS Kap1.1-3
1.3.1 Struktur (2)
1.3.3 Manipulation
nr# :int4
name :char(30)
stadt :char(30)
11
Thyssen
Duisburg
12
Siemens
München
Erfolgt in relationaler Datenbanksprache
Internationaler Standard ist SQL
Daten-Auswertung
erzeugt aus bestehenden Tabellen neue Tabellen
=> Ausgabe kann wieder Input für weitere
Auswertung sein
SQL-Befehl: select
Eigenschaften einer Tabelle:
alle Spalten haben skalaren Typ
alle Spaltenbezeichner sind verschieden
jede Zeile hat eindeutigen Primary Key
Felder können leer (NULL) sein
(Ausnahme: Primary Key)
Daten-Änderung
weist bestehenden Tabellen neue Werte zu
SQL-Befehle: insert, update, delete
17
19
1.3.2 Integrität
1.3.4 weitere Objekte (1)
Integrity Constraints
weitere Datenbankobjekte zur Verwaltung
Bedingungen, die unzulässige Einträge verhindern
Objekt
index
sequence
trigger
user
group
Primary Key
eindeutiges Zeilenmerkmal; verhindert doppelte Zeilen
Foreign Key
Verweis auf andere Tabelle; verhindert Verweis ins Leere
Hersteller
Produkt
nr#
nr#
name
name
database
schema
herstellernr
18
Bedeutung
beschleunigter Zugriff
automatische Schlüsselgenerierung
Callback Mechanismus
Datenbankbenutzer
Benutzergruppe
(vereinfacht Rechteverwaltung)
logische Speichereinheit
Namespace
Die verfügbaren Objekttypen können je DBS variieren.
Z.B. unterstützt Postgres schema erst in Version 7.3
20
Dalitz DBS Kap1.1-3
1.3.4 weitere Objekte (2)
1.3.5 System Catalog (2)
Index
System Catalog PostgreSQL:
ermöglicht Direktzugriff über Spaltenwert(e)
beschleunigt Zugriff, aber verlangsamt Änderungen
sinnvoll insbesondere bei Foreign Keys (häufige Joins)
Produkt
pnr#
Catalog Name
pg attribute
pg class
pg database
pg group
pg index
pg relcheck
pg trigger
pg type
pg user
automatischer Index
durch Primary Key
name
preis
hnr
zusätzlicher Index
auf hnr sinnvoll
Bemerkung:
Zugriff über Index nicht notwendig schneller als sequentiell.
Bei Postgres Statistikauswertung mit (vacuum) analyze nötig.
21
1.3.5 System Catalog (1)
statt Tabellen Views, die Benutzerrechte berücksichtigen
Präfix USER_ => eigene Objekte
Präfix ALL_ => alle Objekte auf die User zugreifen darf
Präfix DBA_ => alle Objekte
pg attribute: PostgreSQL column meta data
attname
atttypid
...
23
Data Dictionary Oracle:
Beispiel:
The table this column belongs to
(references pg class.oid)
Column name
The data type of this column
(references pg type.oid)
...
In psql können Beschreibungen mit \d abgefragt werden.
\set ECHO_HIDDEN (oder psql -E) gibt Abfragen mit aus.
1.3.5 System Catalog (3)
Strukturinformationen werden vom DBS
in Tabellen gespeichert
Sammlung dieser Tabellen heißt System Catalog
oder Data Dictionary
attrelid
Purpose
table columns (“attributes”, “fields”)
tables, indexes, sequences (“relations”)
databases within this database cluster
groups of database users
additional index information
check constraints
triggers
data types
database users
22
View
* tables
* tab columns
* sequences
* indexes
* ind columns
* users
* role privs
Purpose
Shows all relational tables
Shows all table and view columns
Lists all sequences in the database
Lists all indexes
Lists all indexed columns
Lists all users
Lists all roles granted to users and other roles
24
Dalitz DBS Kap1.1-3
1.4 SQL Einführung (1)
1.4 SQL Einführung (3)
Relationale Datenbanksprachen:
SQL est omnis divisa in partes tres:
SQL - Structured Query Language
Als SEQUEL 1974-77 bei IBM für System R entwickelt
ANSI/ISO Standards: SQL (1986), SQL2 (1992), SQL3 (1999)
die meisten heutigen DBS unterstützen
eine Obermenge einer Untermenge von SQL2
DDL - Data Definition Language
Definieren und Ändern von Strukturen
Kommandos: create, alter, drop
QUEL - Query Language
DML - Data Manipulation Language
zeitgleich zu SQL für Ingres an Berkeley University entwickelt
trotz "Überlegenheit zu SQL in vielen Bereichen" (Date)
keine Marktdurchdringung
Abfrage und Manipulation der Daten
Kommandos: select, insert, update, delete
DCL - Data Control Language
QBE - Query by Example
Steuerung Datenzugriff und Datensicherheit
Kommandos: grant, revoke, begin, commit, rollback
intuitiver grafischer Zugriff
in Frontends für "Joe User" realisiert (z.B. MS Access)
1
3
1.4 SQL Einführung (2)
1.4 SQL Einführung (4)
SQL rein sequentielle Sprache
Kommandos werden sequentiell abgearbeitet
keine Programmiersprache. Insbesondere fehlen
Variablen, Kontrollflusssteuerung, Prozeduren
SQL muss in im allgemeinen in "Host-Language"
eingebettet werden
SQL Syntax:
Kommandos durch Semikolon (;) getrennt
(nicht immer: in SQL-Interpreter ja, in ESQL nicht)
Key words und Identifier nicht case sensitive
Ausnahme: quoted Identifier (z.B. "Bla" ≠ bla)
Zulässige Identifier: [_a-z][_a-z0-9]*
String Konstanten in single quotes (’bla bla’)
Single quotes in Strings escaped durch verdoppeln
(’Peter’’s house’)
Es gibt prozedurale Erweiterungen
PL/SQL (Oracle) und PL/PgSQL (PostgreSQL)
Persistend Stored Modules (PSM) in ANSI SQL3
einzeilige Kommentare durch Doppelminus (-- Kommentar)
mehrzeilige Kommentare wie in C (/*Kommentar*/)
SQL3 erlaubt geschachtelte Kommentare (/*/*bla*/*/)
PL/SQL werden wir im Zusammenhang mit
Stored Procedures und Triggern behandeln
2
4
Dalitz DBS Kap1.4
1.4 SQL Einführung (5)
1.4.1 SQL-DDL (2)
Laufendes Beispiel:
Hersteller
hnr#
name
stadt
H1
H2
H3
Henkel
Pelikan
Brause
Düsseldorf
Hannover
Iserlohn
pnr#
name
preis
hnr
P1
P2
P3
P4
Pritt
Uhu
Tinte
Füller
2.50
2.70
3.20
12.98
H1
H1
H2
H2
Produkt
5
1.4.1 SQL-DDL (1)
Datentyp
Beschreibung
character(n)
char(n)
String der Länge n, am Ende ggf.
mit Blanks aufgefüllt
char varying(n)
varchar(n)
String variabler Länge (maximal n)
im allg. gegenüber char vorzuziehen
integer
int
Ganzzahl mit Vorzeichen
Postgres unterscheidet int2, int4, int8
numeric(n,m)
numeric(n)
Dezimalzahl mit n Stellen, davon m nach
dem Komma; numeric(n) = numeric(n,0)
bool
true, false oder unknown (NULL)
date
Datum (4 Bytes, tagesgenau)
time
Uhrzeit (8 Bytes, mikrosekundengenau)
timestamp
Datum und Uhrzeit
1.4.1 SQL-DDL (3)
Constraints
Anlegen einer Tabelle
Constraints werden nach den Felddefinitionen angegeben
Constraints, die sich nur auf ein Feld beziehen,
können direkt bei der Felddefinition angegeben werden
optional können Constraints mit Namen versehen werden
CREATE TABLE tabellenname (
feld1 datentyp1,
feld2 datentyp2,
...
...,
CREATE TABLE example1 (
a integer,
b integer,
c varchar(2) REFERENCES example2(a),
PRIMARY KEY (feldn, ...),
weitere_constraints
);
Mögliche Datentypen hängen vom konkreten DBS ab
7
PRIMARY KEY (a,b),
CONSTRAINT check_b CHECK (b > 0)
);
6
8
Dalitz DBS Kap1.4
1.4.1 SQL-DDL (4)
1.4.1 SQL-DDL (6)
Anlegen des Beispiels hersteller, produkt:
Mögliche Constraints (1):
NOT NULL
Feld darf nicht leer (NULL) sein
Angabe nur möglich in Spaltendefinition
UNIQUE
Feldwert muss eindeutig sein
mehrfache Nullwerte sind aber zulässig
CHECK (bedingung)
Bedingungen für Feldwerte
alle Felder in Bedingung müssen aus der Tabelle stammen
9
11
1.4.1 SQL-DDL (5)
1.4.1 SQL-DDL (7)
Mögliche Constraints (2):
Auch andere Datenbankobjekte (z.B. index)
werden mit create angelegt
PRIMARY KEY (felder)
formal identisch zu Unique + Not Null
pro Tabelle nur maximal ein Primary Key möglich
FOREIGN KEY (felder) REFERENCES
Beispiel:
tabelle (felder)
CREATE INDEX produkt_i1
ON produkt (hnr);
referenziert Primary Key anderer Tabelle
Wert darf aber NULL sein
Verhalten bei Änderung in Referenztabelle spezifizierbar
(z.B. on update cascade, on delete set null)
Eindeutiger Index:
Ausführliche Dokumentation siehe
ddl-constraints.ps.gz (Homepage zur Vorlesung)
CREATE UNIQUE INDEX ...
10
12
Dalitz DBS Kap1.4
1.4.1 SQL-DDL (8)
1.4.2 SQL-DML (2)
Ändern von Strukturen
Einfügen von Datensätzen
Löschen mit drop
INSERT INTO hersteller (
hnr, name, stadt
) VALUES (
’H1’, ’Henkel’, ’Düsseldorf’
);
DROP INDEX produkt_i1;
DROP TABLE produkt;
Ändern mit alter table
ALTER TABLE produkt
ADD FOREIGN KEY (hnr)
REFERENCES hersteller(hnr);
Die Attributliste kann weggelassen werden.
Nicht zu empfehlen! (Warum?)
ALTER TABLE hersteller
ADD COLUMN strasse VARCHAR(30);
13
1.4.2 SQL-DML (1)
15
1.4.2 SQL-DML (3)
Kommandos zur Datenmanipulation:
Es müssen nicht alle Spalten angegeben werden:
insert
Einfügen eines neuen Datensatzes
INSERT INTO produkt (pnr, name)
VALUES (’P5’, ’Papier’);
update
Änderung von Feldwerten
Ergebnis:
delete
Löschen von Datensätzen
komplette Tabelle auch mit truncate
select
Auswahl von Daten. Ausgabe ist wieder Tabelle
14
pnr#
name
preis
hnr
P1
P2
P3
P4
P5
Pritt
Uhu
Tinte
Füller
Papier
2.50
2.70
3.20
12.98
NULL
H1
H1
H2
H2
NULL
16
Dalitz DBS Kap1.4
1.4.2 SQL-DML (4)
1.4.2 SQL-DML (6)
Anstelle der values-Klausel kann auch select stehen:
Where-Klausel
Weglassen where-Klausel
=> alle Sätze betroffen
INSERT INTO produkt (pnr, name)
SELECT a, b FROM other_table;
UPDATE produkt
SET preis = preis * 2;
Bemerkungen:
Beschreibung von select siehe unten
Zulässige Vergleichsoperatoren und Behandlung
von NULL-Werten später bei select
Datentypen von a,b und pnr,name müssen zusammenpassen
Ermöglicht Einlesen von Daten aus anderer Tabelle,
nicht jedoch aus einer Datei.
Dafür gibt es Datenbankspezifische Tools, z.B. sqlldr (Oracle)
oder den \copy-Befehl in psql (PostgreSQL)
Zugewiesener Wert kann auch Ergebnis eines
select sein ("Subselect")
17
19
1.4.2 SQL-DML (5)
1.4.2 SQL-DML (7)
Ändern von Datensätzen
Löschen von Datensätzen
DELETE FROM produkt WHERE preis > 3;
UPDATE hersteller SET
name = ’Soennecken’,
stadt = NULL
WHERE hnr = ’H3’;
Ergebnis:
pnr#
name
P1
P2
Pritt
Uhu
preis
2.50
2.70
hnr
H1
H1
Ergebnis:
hnr#
name
stadt
H1
H2
H3
Henkel
Pelikan
Soennecken
Düsseldorf
Hannover
NULL
Löschen aller Datensätze
DELETE FROM produkt;
TRUNCATE TABLE produkt;
18
Unterschied: truncate nicht in Transaktion
20
Dalitz DBS Kap1.4
1.4.2 SQL-DML (8)
1.4.2 SQL-DML (10)
Selektion von Daten
Demonstrationsbeispiele
SELECT name, preis FROM produkt
where preis > 3;
SELECT * FROM produkt;
SELECT name AS "Name",
preis AS Brutto FROM produkt;
Project
pnr#
P1
P2
P3
Restrict
P4
name
preis
hnr
Pritt
Uhu
Tinte
Füller
2.50
2.70
3.20
12.98
H1
H1
H2
H2
SELECT ’bla’, name FROM produkt
WHERE name > ’S’;
SELECT ’bla’ FROM produkt;
21
23
1.4.2 SQL-DML (9)
1.4.2 SQL-DML (11)
Die Spaltenauswahl
Operatoren in where-Klausel
Stern (*) ist Abkürzung für alle Spalten
SELECT * FROM produkt;
Operator
Beschreibung
=,>,<,>=,<=
gleich, größer, kleiner
Umbenennung Spalten des Ergebnisses möglich
SELECT
name AS "Name",
preis AS "Brutto"
FROM produkt;
<>
ungleich, Postgres: auch !=
BETWEEN x AND y
Bereichsprüfung
LIKE
Pattern matching mit Wildcards
_ (ein Zeichen), % (beliebig viele)
SIMILAR TO
Pattern matching mit Posix 1003.2
regulären Ausdrücken (SQL3)
Auch Konstanten können selektiert werden
SELECT ’bla’, name FROM produkt;
IS (NOT) NULL
Prüfung, ob Feld (nicht) leer ist
22
Verknüpfung von Bedingungen mit AND und OR
Negation mit NOT
24
Dalitz DBS Kap1.4
1.4.2 SQL-DML (12)
1.4.2 SQL-DML (14)
Behandlung von NULL-Werten
normale SQL-Funktionen
NULL-Werte werden weder durch "="
noch durch "<>" oder "!=" erwischt
Typumwandlung kompatibler Datentypen mit cast :
explizites Prüfen mit "IS NULL" nötig
CAST (preis AS FLOAT)
dreiwertige Logik: true, false, unknown
Bemerkungen:
Insbesondere ist z.B. das Gegenteil von
feld1 > 2
nicht "feld1 <= 2", sondern
(feld1 <= 2) OR (feld1 IS NULL)
bitte absurde Syntax beachten: "AS" statt ","
Beispiel für Unstrukturiertheit von "S"QL
die meisten DBS führen auch implizite Casts durch
z.B. castet Postgres ’...’ - Konstanten nach Bedarf
Empfehlung: keine optimistischen Annahmen machen!
25
27
1.4.2 SQL-DML (13)
1.4.2 SQL-DML (15)
SQL-Funktionen
Cast kann mehrdeutig sein:
SQL kennt zwei Klassen von Funktionen:
CAST (’01.02.02’ AS DATE)
Ergebnis abhängig vom eingestellten Datumsformat
(Parameter des Servers oder der Client-Session):
01. Februar 2002
02. Januar 2002
02. Februar 2001
"normale" Funktionen
werden auf einzelne Argumente angewandt
Typumwandlung, binäre Operatoren, Stringfunktionen,
Datumsfunktionen, ...
Aggregatfunktionen
werden auf komplette Spalte einer Query angewandt
Maximum, Summe, Mittelwert, Anzahl,
Auswahl verschiedener Werte (distinct), ...
Lösung:
Formatierte Umwandlung mit to_date
Analog: to_char, to_number, to_time(stamp)
26
28
Dalitz DBS Kap1.4
1.4.2 SQL-DML (16)
1.4.2 SQL-DML (18)
Datumsformatierung
Zahlenformatierung
to_date (’01.02.02’, ’DD.MM.YY’)
to_char (einfuehrung, ’DD.MM.YYYY’)
to_number (’11-’, ’99S’)
to_char (preis, ’099.99’)
Formatkennzeichen
Beschreibung
Formatkennzeichen
Beschreibung
YYYY, YY
MM
Month, Mon
DD, DDD
D
HH24, HH am
MI, SS
Jahr vierstellig, zweistellig
Monat (01-12)
Monat als Text (”Januar”, ”Jan”)
Tag des Monats (01-31), Jahres (001-366)
Tag der Woche (1-7, Sonntag=1)
Stunde (00-23), (01-12) mit am/pm
Minute (00-59), Sekunde (00-59)
29
9
0
S
PL
. ,
D G
Ziffer ohne führende Nullen
Ziffer mit führender Null
Minus-Zeichen bei negativen Zahlen
Minus- oder Plus-Zeichen
Dezimalpunkt und Tausendergruppe
Dezimalpunkt und Tausendergruppe
unter Berücksichtigung von locale
1.4.2 SQL-DML (17)
1.4.2 SQL-DML (19)
Beispielabfrage:
Überblick aller seit 1982 eingeführten Produkte
Stringfunktionen
30
31
Funktion
Beschreibung
str1 || str2
lower(str), upper(str)
substr(str, pos, len)
substring(str FROM pos FOR len)
trim(str [, chars])
trim([chars] FROM str)
translate(str, from, to)
String Concatenation
Conversion to lower, upper case
Extraktion Teilstring (pos0=1)
abstruse SQL2 Syntax
vorn und hinten abschneiden
SQL2 Syntax
Character Translation
Beispiel:
SELECT upper(name) || ’ kostet ’ ||
trim(to_char(preis,’99D99’)) || ’ EUR.’
AS "Preisliste" FROM produkt;
32
Dalitz DBS Kap1.4
1.4.2 SQL-DML (20)
1.4.2 SQL-DML (22)
Mathematische Funktionen
Überblick Aggregatfunktionen
Funktion
Beschreibung
Aggregatfunktion
Beschreibung
+ - * /
abs(x)
trunc(x [, n])
round(x [, n])
arithmetische Operatoren
Absolutwert
abschneiden auf n Nachkommastellen
runden auf n Nachkommastellen
count()
min(), max()
sum(), avg()
Anzahl
Minimum, Maximum
Summe, Mitelwert
Bemerkungen:
Datumsfunktionen
Funktion
Beschreibung
current_date
current_timestamp
age([ts1,] ts2)
extract(feld FROM ts)
Aktuelles Datum oder Uhrzeit
SQL3: keine Klammern!
Intervall ts1 - ts2
Feldextraktion (z.B. year)
Bei "Stern" Aufrufmodus wird keine Spalte angegeben
=> nur bei count sinnvoll
Außer bei count wird NULL zurückgegeben, wenn
nichts selektiert wurde
Insbesondere gibt sum dann nicht 0 zurück, sondern NULL
(kann bei Bedarf mit case umgangen werden)
33
35
1.4.2 SQL-DML (21)
1.4.2 SQL-DML (23)
Aggregatfunktionen
Sortieren und Gruppieren
Sortierung mittels order by:
Berechenen einen Wert aus kompletter Spalte
eines Abfrageergebnisses (z.B. Maximum)
SELECT name, preis FROM produkt
ORDER BY name ASC;
Aufrufmodi
Berücksichtigung aller not null Werte
aggregate_func (expression)
aggregate_func (ALL expression)
Berücksichtigung verschiedener not null Werte
aggregate_func (DISTINCT expression)
Berücksichtigung sämtlicher (incl. NULL) Werte
aggregate_func ( * )
Bemerkungen:
Modifier asc (default) oder desc für Sortierreihenfolge
Sortierspalte muss in Spaltenliste vorkommen
mehrere Spalten in order by durch Komma trennen
statt Spaltenname kann auch Index in vorheriger
Spaltenliste verwendet werden (order by 1)
34
36
Dalitz DBS Kap1.4
1.4.2 SQL-DML (24)
1.4.2 SQL-DML (26)
Gruppierung mittels group by
Demonstration where versus having:
Frage:
Welcher Hersteller stellt wieviele Produkte her?
SELECT hnr, count(*), max(preis)
FROM produkt
WHERE preis > 5
GROUP BY hnr;
Antwort:
SELECT hnr, count(pnr) FROM produkt
GROUP BY hnr;
SELECT hnr, count(*), max(preis)
FROM produkt
GROUP BY hnr
HAVING max(preis) > 5;
Bemerkungen:
ohne group by ist Abfrage fehlerhaft (Warum?)
pnr muss aggregiert werden, da verschiedene Werte
innerhalb einer Gruppe auftreten können
Gruppierspalte muss in Spaltenliste auftauchen
37
39
1.4.2 SQL-DML (25)
1.4.2 SQL-DML (27)
Ergebniseinschränkung mittels having:
Joins
Frage:
Welche Hersteller haben höchstens zwei Produkte?
Wunsch:
Produktliste mit Herstellername statt hnr
Antwort:
SELECT hnr, count(*) FROM produkt
GROUP BY hnr HAVING count(*)<=2;
Naiver Ansatz
Bemerkung:
Bedingung in where-Klausel nicht möglich
liefert nicht gewünschtes Ergebnis (Warum?)
SELECT hersteller.name, produkt.name
FROM produkt, hersteller ORDER BY 1,2;
Lösung:
Join über gemeinsame Spalte hnr
where-Bedingung wird vor Gruppenbildung angewandt
Aggregatfunktionen in where-Klausel sinnlos
38
40
Dalitz DBS Kap1.4
1.4.2 SQL-DML (28)
1.4.2 SQL-DML (30)
Union, Intersect, Except
SELECT hersteller.name, produkt.name
FROM produkt, hersteller
WHERE produkt.hnr = hersteller.hnr
ORDER BY 1,2;
Kombination von Abfragen mit selben Ergebnisspalten
durch Mengenoperationen
Union - Vereinigungsmenge
Intersect - Schnittmenge
Except - Differenzmenge
Bemerkungen:
Verbundspalte muss selben Typ haben (ggf. casten)
Tabellen können über Aliasnamen referenziert werden
SELECT a.name, b.name
FROM hersteller a, produkt b ...
keine Doubletten im Ergebnis mittels select distinct ...
Hierbei handelt es sich um einen Inner Join:
Ergebnis enthält nur Sätze, die in beiden Tabellen
vorkommen. Im Beispiel fehlt also "Brause".
Achtung:
Tatsächlich Mengenoperationen, dh.
Doubletten werden entfernt!
Wenn unerwünscht, Modifier ALL verwenden
41
1.4.2 SQL-DML (29)
43
1.4.2 SQL-DML (31)
Beispiel
Outer Join
SELECT ’Max’ AS
max(preis) AS
FROM produkt
UNION
SELECT ’Min’ AS
min(preis) AS
FROM produkt;
SELECT hersteller.name, produkt.name
FROM hersteller /* statt Komma: */
LEFT OUTER JOIN produkt
ON hersteller.hnr = produkt.hnr;
Bemerkungen:
Schlüsselwort OUTER ist optional
Modifier LEFT, RIGHT oder FULL bestimmt, welche Tabelle
vollständig ausgewertet werden soll
Mehrere Join-Spalten durch AND in ON-Klausel kombinieren
Zusätzliche WHERE-Klausel kann nach ON-Klausel folgen
" ",
"Wert"
" ",
"Wert"
Bemerkungen:
Selektierte Spalten müssen selben Typ haben
Spaltenalias ist nicht zwingend erforderlich, aber für
Überschrift sinnvoll
42
44
Dalitz DBS Kap1.4
1.4.2 SQL-DML (32)
1.4.2 SQL-DML (34)
Subqueries
Verwendung Subquery in where-Klausel:
Vergleich mit "=", wenn Subquery genau
einen Wert zurückliefert
Ergebnis einer Abfrage kann als Unterabfrage (engl.
subquery) anstelle einer Tabelle verwendet werden
Vergleich mit "IN", wenn Subquery
mehrere Werte zurückliefern kann
SELECT x.nr FROM
( SELECT nr, name FROM produkt
WHERE preis > 5.0 ) x;
Alternative zu "IN" ist Formulierung über
"EXISTS" als correlated Subquery
Bemerkungen:
x ist Bezeichner für die von Subquery zurückgegebene Tabelle
die Klammern (...) und der Name x um die select-Anweisung
wirken als Tabellenkonstruktor einer neuen Tabelle x
Bemerkung:
Die exists Variante ist oft dramatisch schneller
45
47
1.4.2 SQL-DML (33)
1.4.2 SQL-DML (35)
Wenn die Subquery nur eine Spalte selektiert,
kann das Ergebnis einer Abfrage auch innerhalb
einer where-Bedingung verwendet werden:
IN versus EXISTS
SELECT * FROM hersteller
WHERE hnr IN (
SELECT hnr FROM produkt
WHERE preis > ’3.0’
);
SELECT * FROM produkt
WHERE preis =
(SELECT max(preis) FROM produkt);
Bemerkungen:
Abfrage select *, max(preis) from produkt;
nicht möglich (Warum?)
hier kein Tabellenkonstruktor, weil Ergebnis nicht als
Tabelle verwendet wird
Verwendung in where-Klausel von update, delete genauso
46
SELECT * FROM hersteller
WHERE EXISTS (
SELECT hnr FROM produkt
WHERE preis > ’3.0’
AND produkt.hnr = hersteller.hnr
);
48
Dalitz DBS Kap1.4
1.4.2 SQL-DML (36)
1.4.3 SQL-DCL (1)
Data Control Language für Datenschutz/-sicherheit:
SELECT * FROM hersteller WHERE EXISTS
( SELECT hnr FROM produkt
WHERE preis > ’3.0’
AND produkt.hnr = hersteller.hnr );
Sicherheit vor fehlerhaften Zugriffen
Stichwort "Transaktionen"
SQL-Kommandos: begin, commit, rollback
Bemerkungen:
Schutz vor unberechtigten Zugriffen
exists-Bedingung ist wahr, wenn Subquery irgendein
Ergebnis liefert. Was die Subquery selektiert, ist egal.
Insbesondere könnte innere Abfrage auch lauten:
(select ’ ’ from produkt ...)
Stichwort "Benutzerrechte"
SQL-Kommandos: grant, revoke
Vor allem wichtig im Mehrbenutzerbetrieb
dh. bei Client-Server Datenbanken
Correlated Subquery:
Innerhalb der Subquery wird eine Tabelle der äußeren
Abfrage referenziert (im Beispiel: hersteller)
49
51
1.4.2 SQL-DML (37)
1.4.3 SQL-DCL (2)
ALL und ANY
Transaktionen
Vergleichsoperationen wenn Subquery
mehrere Tupel zurückgibt
Stand Konto A
x
Bedingung muss auf alle Treffer passen:
SELECT * FROM produkt
WHERE preis >= ALL (
SELECT preis FROM produkt
);
Bedingung muss auf mindestens einen Treffer passen:
SELECT * FROM produkt
WHERE preis > ANY (
SELECT preis FROM produkt
);
Stand Konto B
y
UPDATE konto SET stand=stand−500 WHERE nr=’A’
x − 500
Inkonsistenter
Zustand
y
UPDATE konto SET stand=stand+500 WHERE nr=’B’
50
x − 500
y + 500
52
Dalitz DBS Kap1.4
1.4.3 SQL-DCL (3)
1.4.3 SQL-DCL (5)
Transaktionen erfüllen ACID-Prinzip
Benutzerrechte
Atomicity
DBS hat eigene Benutzerverwaltung
Transaktion ist Einheit: Alles oder Nichts.
Consistency
Anlage mit CREATE USER ...
Ändern mit ALTER USER ...
Transaktion überführt kosistenten Zustand in konsistenten
Zustand. Innerhalb Transaktion Inkonsistenz möglich.
Isolation
Kommandos sind nicht standardisiert.
Beispiel Passwortänderung:
Änderungen in einer Transaktion sind bis zum Abschluss
unsichtbar für andere Transaktionen.
Oracle: ALTER USER usr IDENTIFIED BY ’pwd’;
PostgreSQL: ALTER USER usr WITH PASSWORD ’pwd’;
Durability
Nach Abschluss Transaktion bleiben Änderungen bestehen,
auch im Fall eines folgenden Systemabsturzes
Auch Zuweisung Admin-Recht (DBA) systemspezifisch
53
55
1.4.3 SQL-DCL (4)
1.4.3 SQL-DCL (6)
SQL-Kommandos für Transaktionen
Der Anleger einer Tabelle ist ihr Owner.
Sonst kann keiner auf die Tabelle zugreifen.
BEGIN [WORK]
BEGIN [TRANSACTION]
COMMIT
ROLLBACK
Start einer Transaktion
Achtung: ggf. implizit (Oracle)
Abschluss Transaktion
mit Übernahme der Änderungen
Abschluss Transaktion
ohne Übernahme der Änderungen
Wenn auch andere User die Tabelle nutzen sollen,
muss der Owner ihnen Privileges erteilen:
GRANT SELECT ON produkt TO PUBLIC;
GRANT UPDATE ON produkt TO peter;
Bemerkung:
In Oracle und SQL2 beginnt Transaktion implizit mit
jedem "transaction-initiating" Kommando
Die meisten anderen DBS (auch PostgreSQL) machen dagegen
ein auto-commit nach jedem Statement, wenn nicht explizit
eine längere Transaktion mit BEGIN gestartet wird
Privileges werden erteilt mit GRANT ... TO ...
und entzogen mit REVOKE ... FROM ...
54
56
Dalitz DBS Kap1.4
1.4.3 SQL-DCL (7)
Ausblick
Überblick Privilegien:
SQL-Befehle können interaktiv über SQL-Interpreter
eingegeben werden (Oracle: sqlplus, Postgres: psql)
Privileg
Berechtigung
SELECT
INSERT
UPDATE
DELETE
...
Lesen
Einfügen neuer Sätze
Ändern bestehender Sätze
Löschen
weitere Rechte je nach DBS
z.B. rule, references, trigger
Metakommandos
Befehle an den Interpreter
In psql durch Backslash gekennzeichnet, z.B
\d (describe), \i (import script), \set (set psql option)
Liste aller Metakommandos: man psql
SQL-Kommandos
werden an den Datenbankserver weitergereicht
Vereinfachungen:
ALL kann für alle Privilegien verwendet werden
PUBLIC kann für alle User verwendet werden
57
Wie greift man aus einem Programm auf DB zu?
=> nächstes Kapitel
59
1.4.3 SQL-DCL (8)
Einfachere Rechteverwaltung mit Groups
Anlegen Gruppe mit CREATE GROUP grp;
Privilegien dieser Gruppe zuweisen mit
GRANT ... TO GROUP grp;
User in die Gruppe aufnehmen mit
ALTER GROUP grp ADD USER usr;
User können aus Gruppe entfernt werden mit
ALTER GROUP grp DROP USER usr;
58
Dalitz DBS Kap1.4
1.5 Client Programmierung (1)
1.5 Client Programmierung (3)
Interaktive Eingabe von SQL
Clientseitige Programmierung
DBS liefern SQL-Interpreter mit
(Oracle: sqlplus, PostgreSQL: psql)
Nicht praktikabel für Endanwender
vorherrschende Form der DB-Programmierung
Anwendungsprogramm führt nur
elementare SQL-Kommandos durch
Frage:
Wie DB-Zugriffe automatisieren (programmieren)?
Ablauflogik wird in anderer Sprache
(Host Language, z.B. C++) programmiert
Serverseitige Programmierung
Frage:
Wie können aus Host Language heraus
SQL-Kommandos ausgeführt werden?
Im DB-Server hinterlegt und von allen Anwendungen genutzt
Clientseitige Programmierung
SQL-Kommandos werden aus Anwendungsprogramm aufgerufen
1
3
1.5 Client Programmierung (2)
1.5 Client Programmierung (4)
Serverseitige Programmierung
SQL und Host Language
Anwendg.
SELECT
proc()
FROM
...
DB
DBMS
DBS
Hauptanwendungsgebiet: Trigger
Vorteil: greift unabhängig davon, wie auf die
Daten zugegriffen wird (z.B. auch SQL-Interpreter)
Beschreibung
SQL-Script
Batch Aufruf SQL-Interpreter.
Keine Einbindung in Host Language.
Mischen von SQL und Host Language.
Präprozessor übersetzt exec sql Statements.
Routinen in Host Language, SQL-Kommando
ggf. als Parameter.
Natives CLI: auf konkretes DBS zugeschnittene
Bibliothek, z.B. oci (Oracle), libpq (Postgres)
Abstraktes CLI: DBS-unabhängige abstrakte
Bibliothek. Für konkretes DBS “Treiber” nötig.
Beipiele: odbc, bde, perl-dbi
embedded SQL
(ESQL)
Call Level
Interface (CLI)
proc()
stored Procedure
Methode
2
4
Dalitz DBS Kap1.5
1.5 Client Programmierung (5)
1.5.1 SQL-Script (2)
Typische Einsatzgebiete
Alternative: "Fernsteuerung" des SQL-Interpreters
durch Umlenkung von stdin
Schnittstelle
Einsatzgebiet
SQL-Script
Einfache Administrative Aufgaben,
z.B. User anlegen, DB-Schema einspielen
DB-unabhängige Massensoftware,
z.B. Office-Pakete
cron-gesteuerte Serverprozesse,
als CGI-Script in Web-Programmierung
Implementierung eigener abstrakter
Interfaces, DBS-spezifische Tools,
Individualsoftware, Programmierung
Treiber für abstrakte CLI’s
ODBC
Perl-DBI
ESQL
native CLI’s
In der Shell:
#!/bin/sh
psql <<EOF
/* SQL-Kommandos */
EOF
Im C-Programm:
unter Unix mit popen() (Einweg-Pipe)
oder pipe() + fork() + dup2() + exec() (Zweiwege-Pipe)
unter Windows NT/2000/XP mit
CreatePipe() + DuplicateHandle() + CreateProcess() +
CreateThread()
5
7
1.5.1 SQL-Script (1)
1.5.1 SQL-Script (3)
SQL-Interpreter wie psql können nicht nur
interaktiv verwendet werden:
Nachteile
keine Kontrollflusssteuerung (nur SQL)
Fehlerbehandlung schwierig
Ausführen einer externen Datei (z.B. script.sql)
mit SQL-Kommandos
Kann umgangen werden bei psql -c
Kontrollfluss durch Shell-Befehle
Abfragen des Exit-Codes möglich
Aber: großer Overhead,
da pro SQL-Befehl ein Aufruf von psql
innerhalb psql-Session mit Metakommando: \i script.sql
mit entsprechender Aufrufoption: psql -f script.sql
script.sql kann auch Metakommandos enthalten
Übergabe eines SQL-Kommandos als
Kommandozeilenparameter
=> Einsatz begrenzt auf einfache Aufgaben
Beispiel: psql -c "truncate table produkt;"
6
8
Dalitz DBS Kap1.5
1.5.1 SQL-Script (4)
1.5.2 natives CLI (2)
Beispiel: dropuser von PostgreSQL
Erzeugung Clientprogramm:
#!/bin/sh
Source
Code
# (...)
# Commandline Parsing schaufelt
# zu löschenden User in Variable $DelUser
psql $PSQLOPT -d template1 -c "DROP USER $DelUser"
pgm.c
#include
Object
Code
pgm.o
Linker
exit 0
pgm
9
1.5.2 natives CLI (1)
1.5.2 natives CLI (3)
Call Level Interface
Konkrete Schritte der Programmierung:
Zugriff über vom DBS-Hersteller bereitgestellte
Bibliotheksroutinen (Oracle: oci, Postgres: libpq)
Executable
11
Source Code in Editor erstellen
Funktionsprototypen mit #include <libpq-fe.h> einbinden
Zu Object Code compilieren
Client−
programm
PQconnectdb PQexec
Header
libpq.a
oder libpq.so Library
Compiler
# Abfragen Exitcode
if [ "$?" -ne 0 ]; then
echo "deletion of user \"$DelUser\" failed" 1>&2
exit 1
fi
libpq−fe.h
PostgreSQL
Serverprozess
gcc -c -I/usr/include/pgsql pgm.c
"/usr/inlude/pgsql" ist Verzeichnis mit Postgres-Headern
...
Mit libpq linken
gcc -o pgm pgm.o -L/usr/lib/pgsql -lpq
"/usr/lib/pgsql" ist Verzeichnis mit Postgres-Libraries
libpq
10
12
Dalitz DBS Kap1.5
1.5.2 natives CLI (4)
1.5.2 natives CLI (6)
Klassifikation libpq-Routinen:
Beispiel Verbindungsaufbau, -abbau
Verbindungsaufbau, -abbau
PQconnectdb(), PQfinish(), PQstatus()
PGconn* conn;
Ausführen von SQL-Statements
PQexec(), PQresultStatus(), PQcmdTuples(), PQclear()
/* Login */
conn = PQconnectdb("dbname=db user=usr ...");
Verarbeiten von Abfrageergebnissen
PQntuples(), PQgetvalue(), PQgetlength()
/* Fehlerprüfung */
if (PQstatus(conn) == CONNECTION_BAD) {
/* ... */
}
Wie die ANSI C stdio-Bibliothek ist libpq eine mit
C-Mittlen realisierte objektorientierte Bibliothek
/* (...) */
Funktionsparameter zuvor konstruierte Strukturen
Destruktoren müssen selbst aufgerufen werden
/* Logout */
PQfinish(conn);
13
15
1.5.2 natives CLI (5)
1.5.2 natives CLI (7)
Hauptobjekte in libpq:
Beispiel Non-Select
Datentyp
PGconn
PGresult
Konstruktor
PQconnectdb()
PQexec()
Destruktor
PQfinish()
PQclear()
PGconn *conn;
PGresult *res;
/* Absetzen SQL-Statement */
res = PQexec(conn,
"DELETE FROM produkt WHERE preis>’3.0’");
PGconn
PQconnectdb()
PGresult
PQexec()
PQgetvalue()
pgm
SQL
Variable
if (PQresultStatus(res) == PGRES_COMMAND_OK) {
/* Rückmeldung Auswirkungen */
printf("%s Sätze gelöscht\n", PQcmdTuples(res));
}
else {
/* Fehlerbehandlung */
}
PQfinish()
PQclear()
14
/* Speicher freigeben nicht vergessen! */
PQclear(res);
16
Dalitz DBS Kap1.5
1.5.2 natives CLI (8)
1.5.3 abstraktes CLI (1)
Beispiel Select
abstraktes Call Level Interface
Zugriff über DBS-unabhängige Bibliotheksroutinen
passender "Treiber" wird zur Laufzeit vom
"Treiber-Manager" geladen
PGconn *conn;
PGresult *res;
/* Absetzen SQL-Statement */
res = PQexec(conn, "SELECT usename FROM pg_user");
Clientprogramm
if (PQresultStatus(res) == PGRES_TUPLES_OK) {
/* Ausgabe Ergebnisse */
for (i = 0; i < PQntuples(res); i++)
printf("%2d. %s\n", i+1, PQgetvalue(res,i,0));
}
else {
/* Fehlerbehandlung */
}
/* Speicher freigeben nicht vergessen! */
PQclear(res);
SQLFetch SQLGetData
DB−Server
...
odbc−lib
Treiber
Manager
17
ODBC
Treiber
1.5.2 natives CLI (9)
1.5.3 abstraktes CLI (2)
weiterführende Literatur zu libpq:
Vorteile
natives
CLI
19
Programm läuft (im Prinzip) mit beliebigem DBS
Aber: ggf. abhängig vom SQL-Dialekt
keine Bindung an konkretes DBS zur Compilezeit
=> geeignet für Massensoftware (z.B. Office-Pakete)
kann auch ohne DBS verwendet werden:
z.B. gibt es Perl-DBI Treiber für Text Files
Hartwig: PostgreSQL - Professionell und praxisnah
Kapitel 9.1 (Semesterapparat TWY Hart)
PostgreSQL Programmer’s Guide:
Client Interfaces - libpq. Im PG-Paket enthalten.
Online verfügbar unter
http://www.postgresql.org/docs/
Nachteile
kleinster gemeinsamer Nenner
fortgeschrittene DBS-Features nicht nutzbar
langsamer als direkt natives CLI
erfordert Infrastruktur und Konfiguration
im Einzelfall doch Fallunterscheidung DBS nötig
Beispiel: implizite Transaktionen in Oracle
Hartwig beschreibt auch die C++ Bibliothek libpq++.
Achtung: Informationen sind veraltet!
18
20
Dalitz DBS Kap1.5
1.5.3 abstraktes CLI (3)
1.5.3 abstraktes CLI (5)
Überblick
Abstraktion
Open Database
Connectivity (ODBC)
Java Database
Connectivity (JDBC)
Borland Database
Engine (BDE)
Perl Database
Interface (DBI)
Host Language
C
Visual Basic
Java
Object Pascal
C++
Perl
Single
Tier
Hersteller
Microsoft
offener Standard
Sun
offener Standard
Borland
Multiple
Tier
Programm
SQL
SQL
ODBC
Treiber
ODBC
Treiber
SQL Imple−
mentierung
Tim Bunce
offener Standard
Wir schauen uns konkret an:
ODBC (prinzipieller Aufbau)
Perl-DBI (Praktikumsaufgabe)
Programm
SQL
DBMS
DB
DB
21
1.5.3 abstraktes CLI (4)
DBS
23
1.5.3 abstraktes CLI (6)
verbreitete ODBC-Irrtümer
ODBC Data Sources
ODBC ist nur für Windows-Programme
Abstraktion Verbindungsparameter
Infrastruktur gibt es für Win32, Unix, MacOS, OS/2
ODBC-Treiber aber oft vom DBS-Hersteller nur für Win32
mitgeliefert => Treiber von Drittanbieter beziehen
zu verwendender ODBC-Treiber
Treiber-spezifische Parameter (z.B. pghost, pgdatabase, ...)
einem Satz Verbindungsparameter wird ein
Data Source Name (DSN) zugewiesen
ODBC ist langsam
unzulässige Verallgemeinerung der Erfahrungen mit
Access + VisualBasic (historisch erste ODBC-Umgebung)
nicht gültig für "Multiple-Tier" Treiber, da dabei
die ODBC-Abstraktionsschicht nur geringer Overhead ist
statt Parameter beim Login zu übergeben,
gibt das Anwendungsprogramm den DSN an
Zuordnung DSN zu Parametern:
Single Tier: Treiber implementiert SQL-Abfragen
Multiple Tier: Treiber reicht SQL an DBS weiter
hinterlegt in Datei (Unix) oder Registry (Win32)
ODBC-Infrastruktur stellt Config-Tool(s) bereit
22
24
Dalitz DBS Kap1.5
1.5.3 abstraktes CLI (7)
1.5.3 abstraktes CLI (9)
Vereinfachtes Beispiel ODBC-Connection:
DSN-Konfiguration
SQLHENV
long
SQLHDBC
sqlenv;
rc;
sqlconn;
/* Handle ODBC environment */
/* result of functions */
/* Handle connection */
/* 1. Allocate Environment Handle and register Version */
rc = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&sqlenv);
rc = SQLSetEnvAttr(sqlenv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
/* 2. Allocate Connection Handle, set Timeout */
rc = SQLAllocHandle(SQL_HANDLE_DBC, sqlenv, &sqlconn);
SQLSetConnectAttr(sqlconn, SQL_LOGIN_TIMEOUT, (SQLPOINTER*)5, 0);
/* 3. Connect to the Datasource "web" */
rc = SQLConnect(sqlconn, (SQLCHAR*) "web", SQL_NTS,
(SQLCHAR*) "christa", SQL_NTS, (SQLCHAR*) "", SQL_NTS);
/* Typical Errorcheck */
if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
{ /* Error Handling */ }
25
1.5.3 abstraktes CLI (8)
DBS
Treiber
Manager
Treiber
Weitergehende ODBC-Features:
Anfragen an die Fähigkeiten des Treibers
Anfragen an den System Catalog
Anfragen über verfügbare Datenquellen und gesetzte Optionen
ODBC verwendet eigenen SQL-Dialekt, der
in SQL-Dialekt des DBS übersetzt wird
kompliziertere SQL-Statements können an DBS
"durchgereicht" werden (=> Abhängigkeit von SQL-Dialekt)
DSN
Konfiguration
ODBC Referenzen:
System User
ODBC
Infrastruktur
27
1.5.3 abstraktes CLI (10)
Applikation
Config
Tool(s)
/* Free Resources */
SQLFreeHandle(SQL_HANDLE_DBC, sqlconn);
SQLFreeHandle(SQL_HANDLE_ENV, sqlenv);
Kyle Geiger: Inside ODBC. Microsoft Press 1995
ODBC Infrastruktur für Unix: http://www.unixodbc.org/
Microsoft ODBC Seite: http://www.microsoft.com/data/odbc/
26
28
Dalitz DBS Kap1.5
1.5.3 abstraktes CLI (11)
1.5.3 abstraktes CLI (13)
Was ist Perl?
portable Scriptsprache von Larry Wall
Mischung aus C, awk und sh
Hauptsächlich C mit zahlreichen abkürzenden
Notationen zur Emulation von awk
=> leicht zu schreiben, aber evtl. schwer zu lesen
Perl "Crash-Kurs" später => Praktikumsaufgabe
An dieser Stelle nur Kurzüberblick:
Kommentare von ’#’ bis Zeilenende
Variablen haben als erstes Zeichen Typkennung:
$bla - skalare Variable
@bla - Array Variable
%bla - Hash Variable (Array mit Key statt Index)
keine weitergehende explizite Typunterscheidung
(insbesondere kein int, float, char)
ansonsten sehr C-ähnlich:
Kommandoabschluss mit ’;’
Blockbildung mit ’{ ... }’
Kontrollflusssteuerung mit if, for, while
Meinungen zu Perl:
"Practical Extracting and Report Language"
"Perl is awk with skin cancer."
"Perl wird nicht mehr weiterentwickelt,
weil alle Sonderzeichen aufgebraucht sind."
29
31
1.5.3 abstraktes CLI (12)
1.5.3 abstraktes CLI (14)
Programme in Scriptsprachen können nicht direkt
vom OS ausgeführt werden, sondern brauchen
eine Laufzeitumgebung, den Script-Interpreter.
Beispiel für Perl Code:
# erstelle %list mit Usern und Passwörtern
srand();
Aufruf des Perlscripts script:
perl script arg1 arg2 ...
arg1, ... sind Kommandozeilenargumente für script
for ($nr = 1; $nr <= 35; $nr++)
{
# Username: Prefix "dbs" + laufende Nr
$uid = sprintf("dbs%02d", $nr);
Unter Unix (oder bei Aufruf aus der Cygwin-Shell)
kann script direkt ausführbar gemacht werden:
# zufällige Generierung Passwortzeichen
$pwd = "";
for ($i = 0; $i < 8; $i++) {
$pwd .= chr(int(rand 42) + 48);
}
als erste Zeile des Scripts einfügen: #!/usr/bin/perl
Script ausführbar machen mit: chmod +x script
# Zuordnung User => Passwort
$list{$uid} = $pwd;
Methode geht mit vielen Scriptsprachen (sh, perl, wish)
30
}
32
Dalitz DBS Kap1.5
1.5.3 abstraktes CLI (15)
1.5.3 abstraktes CLI (17)
Perl DBI
Verbindungsaufbau
$dsn = "dbi:Pg:dbname=test;host=dbs;port=5432";
%attr = (AutoCommit => 0,
PrintError => 0, RaiseError => 0);
Wie bei ODBC braucht der DB-Zugriff mit dem
Perl Database Interface (DBI) zwei Komponenten:
$dbh = DBI->connect($dsn, "uid", "pwd", \%attr);
if (!$dbh) {
# Fehlerbehandlung
}
Die abstrakte DBI-Bibliothek wird als Modul
eingebunden mit
use DBI;
Der Database Driver (DBD) wird dynamisch beim
Aufruf der connect Methode geladen
DBI->connect("dbi:Pg:", "uid", "pwd");
DBI->connect("dbi:Oracle:", "uid", "pwd");
Parameter $dsn entspricht "Data Source Name" bei ODBC:
enthält Treiber + (optional) treiberspezifische Optionen
DBI-Attribute %attr können auch direkt angegeben werden:
DBI->connect(..., {AutoCommit => 0, ...})
33
35
1.5.3 abstraktes CLI (16)
1.5.3 abstraktes CLI (18)
DBI arbeitet als objektorientierte Bibliothek
ähnlich wie libpq mit zwei Objekten:
Wichtige DBI-Attribute
Objekt
$dbh
$sth
Bedeutung
Database handle object
Statement handle object
Attribut
AutoCommit
PrintError
$dbh
RaiseError
$sth
DBI−>connect
$dbh−>prepare
$dbh−>disconnect
script
SQL
Attribute werden als Hash-Referenz im
vierten Argument bei DBI->connect übergeben:
$sth−>fetchrow_array
$sth−>execute
$sth−>finish
@row
Bedeutung
Nach jedem Statement automatisch
Commit (0 = off, 1 = on (default))
Fehler werden automatisch nach stderr
ausgegeben (0 = off, 1 = on (default))
Bei Fehler wird Exception (vgl. C++)
geworfen (0 = off (default), 1 = on)
34
DBI->connect($dsn, $uid, $pwd,
{AutoCommit => 0, PrintError => 0});
36
Dalitz DBS Kap1.5
1.5.3 abstraktes CLI (19)
1.5.3 abstraktes CLI (21)
Beispiel Non-Select
Referenzen
$sth = $dbh->prepare("delete from produkt");
Online Kurse Perl:
http://www.phy.uni-bayreuth.de/~btpa25/perl/perl_inhalt.html
http://www.pronix.de/perl/perl.html
if (!$sth->execute) {
# Fehler
printf ("%s\n", $dbh->errstr);
}
else {
# Rückmeldung über Erfolg
printf ("%d Zeilen gelöscht\n", $sth->rows);
}
Offizielle Perl-DBI Dokumentation:
http://www.perldoc.com/cpan/DBI.html
Die beim DBI mitgelieferte Dokumentation kann mit dem
Kommando perldoc DBI wie eine Manpage gelesen werden
Bemerkung:
Für Non-Select Statements gibt es auch die Methode
$dbh->do() (Zusammenfassung von prepare + execute)
Beispielprogramm bsp-perldbi auf Homepage
37
39
1.5.3 abstraktes CLI (20)
1.5.4 embedded SQL (1)
Beispiel Select
Historie
historisch erste Möglichkeit, aus Programm
SQL-Kommandos abzusetzen
1992 in SQL2-Standard aufgenommen
wird von den meisten DBS unterstützt
(Oracle: Pro*C, PostgreSQL: ecpg)
=> ESQL Source Code leicht portabel
$sth = $dbh->prepare("select * from produkt");
if (!$sth->execute) {
# Fehler
printf ("%s\n", $dbh->errstr);
}
else {
# Verarbeitung der Ergebniszeilen
while (@row = $sth->fetchrow_array) {
# ...
}
}
# meist optional:
$sth->finish;
Konzept
C-Code und SQL-Code werden gemischt
SQL-Code wird durch exec sql kenntlich gemacht
SQL-Code wird von Präprozessor in C-Code übersetzt
38
40
Dalitz DBS Kap1.5
1.5.4 embedded SQL (2)
1.5.4 embedded SQL (4)
Ein Beispiel:
Konkrete Schritte der Programmierung:
ESQL Source Code in Editor erstellen
EXEC SQL BEGIN DECLARE SECTION;
int res;
EXEC SQL END DECLARE SECTION;
Dateinamenskonvention: *.pgc
ESQL-Präprozessor aufrufen
ecpg pgm.pgc
int main()
{
EXEC SQL CONNECT TO dbname USER uid/passwd;
(erzeugt pgm.c)
Zu Object Code compilieren
gcc -c -I/usr/include/pgsql pgm.c
"/usr/inlude/pgsql" ist Verzeichnis mit Postgres-Headern
EXEC SQL SELECT COUNT(*) INTO :res FROM pg_user;
Mit libecpg und libpq linken
printf("Anzahl User: %d\n", res);
gcc -o pgm pgm.o -L/usr/lib/pgsql -lecpg -lpq
"/usr/lib/pgsql" ist Verzeichnis mit Postgres-Libraries
return 0;
}
41
1.5.4 embedded SQL (3)
1.5.4 embedded SQL (5)
Übersetzen ESQL-Programm:
ESQL
Code
Probleme bei "Zwittercode"
pgm.pgc
e
Präprozessor
reiner
C Code
Object
Code
ud
pgm.o
ecpgtype.h
ecpglib.h
ecpgerrno.h
Header
l
nc
Problem
Lösung
Variablenaustausch
Definiton von gemeinsamen Variablen
in exec sql declare Abschnitt
Laufzeit-Generierung
von SQL-Statements
Übernahme von Statements aus
Variablen mit exec sql prepare
(“dynamic SQL”)
Navigation in SelectErgebnissen (Tabellen)
SQL-Erweiterung: Cursor
Fehlerkommunikation
globale Variable (structure) sqlca
(“SQL Communication Area”)
#i
libecpg.a
libpq.a
pgm.c
Compiler
43
Library
Linker
pgm
Executable 42
44
Dalitz DBS Kap1.5
1.5.4 embedded SQL (6)
1.5.4 embedded SQL (8)
gemeinsame Variablen
dynamic SQL
Shared Variables werden als C Variablen
in der Declare Section deklariert
EXEC
int
char
EXEC
Shared Variables können auch SQL-Kommandos
enthalten. Zum Ausführen zwei Schritte nötig:
SQL BEGIN DECLARE SECTION;
nr;
name[30];
SQL END DECLARE SECTION;
Definition SQL Statement-Variable mit prepare
strcpy(stmt, "delete from produkt");
EXEC SQL PREPARE sqlstmt FROM :stmt;
Verwendung in SQL mit vorangestelltem
Doppelpunkt
Ausführen der Statements mit execute
EXEC SQL EXECUTE sqlstmt;
EXEC SQL DELETE FROM produkt WHERE nr=:nr;
45
47
1.5.4 embedded SQL (7)
1.5.4 embedded SQL (9)
C- und SQL-Datentyp müssen kompatibel sein
Select-Ergebnisse
SQL-Typ
C-Typ
INTEGER
NUMERIC
CHAR(n), VARCHAR(n)
DATE
TIMESTAMP
int, char[]
double, char[]
char[n+1]
char[12]
char[28]
Da Select-Statements im allg. Tabellen liefern, sind
zwei Fälle bei der Auswertung zu unterscheiden:
Ergebnis kann nur aus einer Zeile bestehen
=> Spaltenwerte können direkt in Shared Variables
übergeben werden mit select into
Bemerkungen:
C-Typ char-Array ist immer möglich
wenn SQL-String länger als C-String, wird abgeschnitten
(aber Achtung: ggf. fehlt dann Abschluss-Null)
Datumstypen vorzugsweise mit to_char, to_date formatieren
Ergebnis kann mehrere Zeilen enthalten
=> einzelne Zeilen müssen mit einem Cursor
abgearbeitet werden
46
48
Dalitz DBS Kap1.5
1.5.4 embedded SQL (10)
1.5.4 embedded SQL (12)
Single-Row Select
Tabellen-Retrieval (2)
EXEC SQL BEGIN DECLARE SECTION;
char name[256];
EXEC SQL END DECLARE SECTION;
/* Deklaration Shared Variables */
EXEC SQL BEGIN DECLARE SECTION;
double preis;
char
einf[12];
EXEC SQL END DECLARE SECTION;
/* Cursor Definition */
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT usename FROM pg_user;
EXEC SQL OPEN cursor1;
/* Füllen Shared Variables in SQL */
EXEC SQL
SELECT preis, to_char(einfuehrung,’DD.MM.YY’)
INTO :preis, :einf
FROM produkt WHERE pnr = ’P1’;
/* Loop über Ergebnisse */
EXEC SQL WHENEVER NOT FOUND DO break;
while (1) {
EXEC SQL FETCH cursor1 INTO :name;
printf("%s\n", name);
}
/* Benutzung Shared Variables in C */
printf("preis: %0.2f; einf: %s\n", preis, einf);
49
/* Cursor Schliessen */
EXEC SQL CLOSE cursor1;
51
1.5.4 embedded SQL (11)
1.5.4 embedded SQL (13)
Tabellen-Retrieval (1)
Fehlerbehandlung
Kommunikation über Erfolg der SQL-Statements
über globale Variable sqlca (SQL Communication Area)
Definition eines Cursors mit declare cursor
und Verknüpfung mit Tabellenausdruck
Tabellenausdruck muss Select-Statement oder
entspr. SQL Statement-Variable sein
bei Cursordefinition wird Statement noch nicht ausgeführt
Einbindung mit EXEC SQL INCLUDE sqlca;
In C Struktur. Wichtigste Felder:
Ausführen des Statements und Positionierung
des Cursors vor die erste Zeile mit open
sqlca.sqlcode
(alternativ Makro: SQLCODE)
sqlca.sqlerrm.sqlerrmc
Auslesen der Zeilen mit fetch in einer Schleife
Fehlercode letztes Statement
Fehlermeldung vom DBS
Achtung: Struktur von sqlca in SQL2 nicht festgelegt
Nur Werte für das Makro SQLCODE sind festgelegt
Schließen des Cursors mit close
50
52
Dalitz DBS Kap1.5
1.5.4 embedded SQL (14)
1.5.4 embedded SQL (16)
Werte für sqlca.sqlcode bzw. SQLCODE:
Verbindungsaufbau und -abbau
Wert
0
100
< 0
> 0
6 100
=
Bedeutung
kein Fehler
keine Datensätze gefunden
Fehler bei Ausführung Statement
konkrete Codes DBS-spezifisch
Warnung (z.B. String zu klein)
konkrete Codes DBS-spezifisch
DB-Login ist in SQL nicht vorgesehen
=> spezielles ESQL-Statement erforderlich
EXEC SQL CONNECT TO db [USER uid[/pwd]];
EXEC SQL DISCONNECT;
Parameter db kann DBS-spezifische Erweiterungen
unterstützen (z.B. Postgres: dbname[@host])
Bemerkung
Fehlerabfrage in SQL2 vereinfacht mit whenever
EXEC SQL WHENEVER bedingung aktion;
Mögliche bedingung NOT FOUND oder SQLERROR
Fügt Abfrage hinter jedes Statement ein und führt
ggf. aktion (z.B. DO break) aus
Fehlerabfrage über SQLCODE bzw. sqlca.sqlcode
53
1.5.4 embedded SQL (15)
1.5.5 weitere Ansätze (1)
Beispiel Fehlerbehandlung
Webanwendungen
EXEC SQL DECLARE cursor1 CURSOR FOR
SELECT usename FROM pg_user;
EXEC SQL OPEN cursor1;
if (sqlca.sqlcode) {
/* Fehler aufgetreten */
printf("%s\n", sqlca.sqlerrm.sqlerrmc);
}
else {
/* Loop über Ergebnisse */
EXEC SQL WHENEVER NOT FOUND DO break;
while (1) {
EXEC SQL FETCH cursor1 INTO :name;
printf("%s\n", name);
}
}
EXEC SQL CLOSE cursor1;
55
normale Client-Server Anwendungen sind
session-orientiert:
Login -> umfangreiche Verarbeitung -> Logout
Webanwendungen sind session-los:
einzelne Seiten werden ohne weitere Verpflichtung angefordert
=> für jeden Seitenaufbau Login großer Overhead
Lösung: Persistent Database Connections
54
56
Dalitz DBS Kap1.5
1.5.5 weitere Ansätze (2)
1.5.5 weitere Ansätze (4)
Persistent Database Connections
Fourth Generation Language (4GL)
proprietäre Sprache des DBS-Herstellers, die
SQL und GUI-Programmierkonzepte vereinigt
(Beispiele: Informix-4GL, Sybase PowerBuilder)
child
processes
Web
Client
parent
process
Bezeichnung ist Marketing-Gag durch Gleichsetzung
Machinencode=1GL, Assembler=2GL, C etc.=3GL
DBS
Weiterführung dieser Idee sind spezielle Application
Development Frameworks einzelner Hersteller
Webserver
Voraussetzung: Verarbeitung nicht über CGI sondern Script-Modul
57
Vorteil: Customizing statt Programmierung
=> niedrige Entwicklungskosten
59
1.5.5 weitere Ansätze (3)
Application Server
Anwendungen wollen eigentlich gar keinen
Datenbank-Zugriff sondern spezielle Funktionen
Idee:
entwerfe Protokoll für diese Funktionen
implementiere dieses Protokoll in eigenem Client-Server Modell
Clients greifen über dieses Protokoll auf Application Server zu
Application Server implementiert DB-Zugriffe
Vorteile:
Datenbank-Logik zentral in Application Server
einfachere Client-Programmierung
58
Dalitz DBS Kap1.5
2 Datenmodelle
2.1 Überblick (2)
Bisher:
ANSI/SPARC Architektur
Datenbankpraxis
externe Ebene
Benutzersicht einzelner Endanwender auf die Daten
z.B. in Masken des User-Interface manifestiert
mehrere Sichten möglich
Welche Objekte bietet relationale Datenbank?
Wie manipuliert man Datenbank-Objekte?
Wie greift man aus Programm auf Datenbank zu?
Datenbankstruktur als gegeben betrachtet
konzeptionelle Ebene
logische Gesamtsicht auf die Daten
das umgangssprachliche "Datenmodell" der Anwendung
Jetzt:
interne Ebene
Datenmodellierung
physische Datenstruktur auf dem Rechner
Wie designt man eine "vernünftige" Datenbankstruktur?
Welche Strukturen sind "(un)vernünftig"?
Verhältnis zu den Ebenen des Datenbankentwurfs?
1
2.1 Überblick (1)
3
2.1 Überblick (3)
Ebenen des Datenbankentwurfs:
Benutzer−
sichten
konzeptionelle Ebene
logische Gesamtsicht des Anwenders auf die Daten
unabhängig vom eingesetzten DBS-Typ
konzeptio−
nelle Ebene
Implementierungsebene
Implementie−
rungsebene
konzeptionelle Datenstrukturen im Rahmen des
eingesetzten DBS
bei relationalem DBS z.B. Tabellen
physische
Ebene
physische Ebene
konkrete Implementierung der Strukturen im Rahmen
des eingesetzten DBS
betrachtete Strukturen: Datenblöcke, Zeiger, Indexstrukturen
2
Datenbankentwurf
konzeptio−
nelle Ebene
interne
Ebene
ANSI/SPARC
4
Dalitz DBS Kap2.1-3
2.1 Überblick (4)
2.1 Überblick (6)
Themeneinordnung
Anforderungs−
Analyse
Thema
Entwurfsebene
2.2 Relationales Modell:
Definition, Normalisierung
Implementierungsebene
2.3 Relationales Modell:
Redundanzvermeidung,
Abhängigkeiten, Normalformen
Implementierungsebene
2.4 Semantischer Ansatz
Entity/Relationship
konzeptionelle Ebene
2.5 Relationale Algebra
und relationaler Kalkül
Implementierungsebene
. . . Speicherstrukturen
physische Ebene
Funtionale Anforderungen
Datenstruktur Anforderungen
konzeptionelles Design
DBS−unabhängig
DBS−spezifisch
Implementierung
Anwendungs−
programmierung
physisches Design
5
2.1 Überblick (5)
7
2.1 Überblick (7)
Der Entwurfsprozess
Entwurfsprozess ist keine Einbahnstraße
Anforderungsanalyse
im Gespräch mit den zukünftigen Anwendern
Ergebnis ist Pflichtenheft
umfasst Anforderungen an Datenstruktur und Funktionen
konzeptionelles Design
Beschreibung der Datenstrukturen auf konzeptioneller Ebene
Analyse
Implementierung
Schritt 1
Schritt 2
Schritt 3
Einsatz
Umsetzen konzeptionelles Design in relationales Modell
Formulierung in Data Defintion Language (DDL)
Aber Faustregel:
Kosten für Änderung wachsen exponentiell in
Schrittnummer, d.h. 1 Euro Änderungskosten in
Analysephase entsprechen 100 Euro in Realisierung
physisches Design
erfolgt durch DBS aufgrund DDL
Anwendungsprogrammierung
Umsetzung Funktionen in User-Interface und weitere Programme
6
8
Dalitz DBS Kap2.1-3
2.1 Überblick (8)
2.2.1 Relationen (2)
Vorgehen Anforderungsanalyse:
Vergleich der Begriffe Relation und Tabelle
Identifikation von Organisationeinheiten, Nutzerkreisen
und zu unterstützenden Aufgaben
Ermittlung zu befragender Personen und Sammlung
Anforderungen
Filterung der Informationen bzgl. Verständlichkeit
Eindeutigkeit; iterative Rücksprache bzw. weitere Befragung
Anforderungen klassifizieren bzgl. zu verwaltender Daten
und Operationen auf den Daten
Formalisieren der Anforderungen in einem Pflichtenheft,
das vom fachlichen Projektbetreuer verstanden und
bestätigt werden muss
relationaler Begriff
“Umgangssprache”
Relationsschema
Tabellendefinition
Relation
(-szustand, -sinstanz)
Tabelle
Attribut
Spaltenname
Domain (Wertebereich)
Datentyp
Tupel
Reihe, Zeile
Bemerkungen
9
Eine Relationen variiert mit der Zeit, während das
Relationsschema weitgehend konstant bleibt
Unterschiede Tabelle und Relation später
2.2 Das relationale Modell
2.2.1 Relationen (3)
2.2.1 Relationen
Bestandteile eines Relationsschemas
R(A1 :dom(A1 ), A2 :dom(A2 ), . . . , An :dom(An ))
Grundbegriffe einer Relation:
Wertebereich,
Domain
Attribut
11
• Relationsname R
• Attributliste A1 , . . . , An
hnr :char(2)
name :varchar(30)
stadt :varchar(30)
H1
Henkel
Düsseldorf
H2
Pelikan
Hannover
Tupel
Jedem Attribut Ai ist eine Menge möglicher
Werte zugeordnet, die Domain dom(Ai ).
Domains können endlich oder unendlich sein.
10
Relationsschema beschreibt Relation mit Namen R.
Die Attributanzahl n heißt Grad der Relation.
12
Dalitz DBS Kap2.1-3
2.2.1 Relationen (4)
2.2.1 Relationen (6)
Eine Relation r(R) des Relationsschemas R(A1 , .., An )
ist eine endliche Menge von n-Tupeln
Relationale Datenbanken und SQL basieren nicht
auf Relationen (Mengen von Tupeln), sondern auf
ungeordneten Listen (engl. Bags, Multisets), d.h.
ti = (x1 , . . . , xn )
mit xj ∈ dom(Aj ) ∪ {NULL}
Tupel sind ungeordnet
Doubletten sind möglich
NULL ist ein spezieller Nullwert.
Teilweise erlaubt SQL Wahl (distinct versus all)
Eine Relation r können wir also schreiben:
r=
m
[
Vorteile von Bags:
{ti }
i=1
Für den j-ten Wert xj in Tupel t (dh. für den Wert des
13
Attributs Aj im Tupel t) schreiben wir auch t[Aj ].
2.2.1 Relationen (5)
15
2.2.1 Relationen (7)
Weitere Verallgemeinerung:
Umformulierung Relations-Definition:
Attributreihenfolge in Relation unerheblich
in formaler Defintion Tupel nicht mehr als geordnete Liste von
Werten auffassen, sondern als Menge von Attribut/Wert Paaren:
Eine Relation r(R) ist eine mathematische Relation
n-ten Grades auf den Mengen dom(A1 ), ..., dom(An ).
t=
D.h. r(R) ist eine Teilmenge des kartesischen
Produkts der Wertebereiche:
n
[
{Ai : xi } wobei xi ∈ dom(Ai ) ∪ {NULL}
i=1
Voraussetzung: keine gleichnamigen Attribute in Relation
Wir nehmen im Folgenden Attributreihenfolge als egal an,
verwenden aber die (einfachere) Listennotation
r(R) ⊆ dom(A1 ) × dom(A2 ) × · · · × dom(An )
Wie verhält sich SQL diesbezüglich?
Konsequenzen der Definition als Menge:
keine Ordnung auf den Tupeln definiert
keine zwei identischen Tupel (Doubletten) möglich
Eliminierung von Doubletten ist aufwendige Operation
=> Bags sind deutlich effizienter als Mengen
stillschweigendes Verschwinden von Tupeln ist insbes.
bei Projektion auf wenige Spalten meist unerwünscht
Aggregatfunktionen wie count, avg oft sinnlos auf Mengen
gleichnamige Attribute in Relation unzulässig
Attribute können in beliebiger Reihenfolge angegeben werden
Aber: INSERT ohne Attributnamen gemäß fester Reihenfolge
14
16
Dalitz DBS Kap2.1-3
2.2.1 Relationen (8)
2.2.2 Integrität (2)
Notationskonventionen
Ein Schlüsselkandidat ist ein minimaler Superkey, d.h.
ein Superkey, aus dem kein Attribut entfernt werden
kann ohne die Eindeutigkeits-Bedingung zu verletzen.
R(A1 , A2 , ..., An )
r(R), r
t = (x1 , x2 , ..., xn )
t[Ai ]
t[Ai1 , Ai2 , ..., Aik ]
Relationsschema n-ten Grades
Relationsnamen: Q, R, S
Relationszustand. Buchstaben: q, r, s
Tupel ∈ r. Buchstaben: t, u, v
Wert des Attributs Ai im Tupel t
Subtupel (t[Ai1 ], t[Ai2 ], ..., t[Aik ])
Primärschlüssel:
im allg. hat Relation mehrere Schlüsselkandidaten
ein Kandidat wird als Primärschlüssel ausgewählt
Bemerkungen:
Weitere Konventionen:
Name des Relationsschemas (z.B. hersteller) bezeichne auch
aktuelle Tupelmenge (Relationszustand). Dagegen bezeichnet
hersteller(hnr, name, stadt) nur das Relationsschema
Attribut kann mit Relationsnamen qualifiziert werden
mittels Punktnotation, z.B. hersteller.name
17
Primärschlüssel besteht im allg. aus mehreren Attributen
gesamtes Tupel (dh. alle Attribute) ist durch Angabe der
Primärschlüsselwerte identifiziert
Notationskonvention: Primärschlüsselattribute durch
angehängtes Doppelkreuz ’#’ kennzeichnen
2.2.2 Integrität (1)
2.2.2 Integrität (3)
Primärschlüssel
Beispiel:
Eine Teilmenge {Ak1 , ..., Akm } von Attributen heißt
Schlüsselobermenge (Superkey) des Relationsschemas R, wenn es keine zwei Tupel geben kann, die in
allen Werten dieser Attribute gleich sind, d.h.
auto
ti [Ak1 , ..., Akm ] 6= tj [Ak1 , ..., Akm ] für alle i 6= j
amtlkz#
fahrgestellnr hersteller
modell
jahr
KR−AD 102
A69352
2000
DU−PW 430
X83554
Beetle
KA
VW
Ford
2001
Fragen:
Bemerkungen:
Ob die Eindeutigkeits-Bedingung erfüllt ist, kann nur aufgrund
der Bedeutung der Attributwerte entschieden werden
Jede Relation hat mindestens einen Superkey
(Welchen und Warum?)
im allgemeinen gibt es mehrere Superkeys
19
Welche Superkeys gibt es?
Wieviele Superkeys gibt es?
Welche Schlüsselkandidaten gibt es?
18
20
Dalitz DBS Kap2.1-3
2.2.2 Integrität (4)
2.2.2 Integrität (6)
Ein Primärschlüssel ist eine Integritätsbedingung
für eine einzelne Relation:
Beispiel für Fremdschlüssel:
hersteller
keine zwei Tupel dürfen dieselben Primärschlüsselwerte haben
kein Primärschlüsselwert darf NULL sein
Neben diesen Entity Integrity Constraints gibt es auch
noch Referential Integrity Constraints, die sich auf zwei
Relationen beziehen:
produkt
hnr#
name
stadt
pnr#
name
H1
H2
Henkel
Pelikan
Düsseldorf
Hannover
P1
P2
Pritt
Papier
preis
2.50
5.30
hnr
H1
NULL
Fremdschlüssel {hnr} von produkt referenziert hersteller
für das Attribut produkt.hnr sind nur die Werte ’H1’, ’H2’
oder NULL zulässig
Tupel s = (’P1’, ’Pritt’, 2.50, ’H1’) aus produkt referenziert
t = (’H1’, ’Henkel’, ’Düsseldorf’) aus hersteller
Tupel mit hersteller.hnr = ’H2’ darf gelöscht werden,
Tupel mit hersteller.hnr = ’H1’ nicht
erzwingt die Existenz eines Tupels in einer Relation, wenn
sich ein Tupel einer anderen Relation darauf bezieht
realisiert durch Fremdschlüssel
21
23
2.2.2 Integrität (5)
2.2.2 Integrität (7)
Eine Attributmenge F ⊆ {A1 , ..., An } der Relation R1
ist ein Fremdschlüssel von R1 , der die Relation R2
referenziert, wenn gilt:
Komponenten eines relationalen Datenbankschemas:
Menge von Relationsschemas
Menge von Integritätsbedingungen
• die Attribute in F haben dieselben Domains
wie die Primärschlüsselattribute von R2
mögliche Darstellungen:
textuelle Darstellung (z.B. SQL-DDL)
grafische Darstellung mit Pfeilen für Fremdschlüssel
• die Werte t1 [F ] in einem Tupel t1 ∈ R1 kommen
entweder als Primärschlüsselwerte in einem
Tupel t2 ∈ R2 vor, oder sie sind alle NULL
hersteller
hnr#
name
stadt
Wir sagen: “t1 referenziert t2 ”.
R1 ist die referenzierende, R2 die referenzierte Relation.
22
produkt
pnr#
name
hnr
preis
24
Dalitz DBS Kap2.1-3
2.2.2 Integrität (8)
2.2.2 Integrität (10)
Relationales Schema in SQL-DDL
Grafische Darstellung
Variante 2: Relationsschema als Zeilenvektor
CREATE TABLE hersteller (
hnr CHAR(2), name VARCHAR(30), stadt VARCHAR(30),
PRIMARY KEY(hnr)
);
hersteller
hnr# name stadt
CREATE TABLE produkt (
hnr CHAR(2), name VARCHAR(30), preis NUMERIC(8,2),
PRIMARY KEY(hnr,name),
FOREIGN KEY(hnr) REFERENCES hersteller(hnr)
);
CREATE TABLE lieferung (
lnr INT, datum DATE,
phnr CHAR(2), pname VARCHAR(30),
PRIMARY KEY(lnr),
FOREIGN KEY(phnr,pname) REFERENCES produkt(hnr,name)
);
produkt
hnr# name# preis
lieferung
lnr#
datum phnr
pname
25
27
2.2.2 Integrität (9)
2.2.3 Normalisierung (1)
Grafische Darstellung
Variante 1: Relationsschema als Spaltenvektor
Relationales Modell nach Codd (1970):
hersteller
produkt
lieferung
hnr#
name
stadt
hnr#
name#
preis
lnr#
datum
phnr
pname
Domains enthalten nur atomare Werte
Attributwert ist Einzelwert aus Domain
Ein relationales Modell mit dieser Eigenschaft
ist in erster Normalform (1NF).
Da diese Bedingung Teil der Definition des relationalen
Modells ist, ist ein relationales Schema immer in 1NF.
Notationen:
Darstellung ähnelt UML-Klassendiagramm
Primärschlüssel durch Doppelkreuz (#)
Fremdschlüssel durch Pfeil zum referenzierten Schlüsselattribut
bei zusammengesetzten Schlüsseln mehrere Pfeile (Warum?)
Das Umformulieren eines Schemas derart, dass die
erste Normalform erfüllt ist, heißt Normalisierung.
26
28
Dalitz DBS Kap2.1-3
2.2.3 Normalisierung (2)
2.2.3 Normalisierung (5)
Unzulässig in 1NF:
Lösung 1:
hersteller
hnr#
H1
name
adresse
branche
Henkelstr. 67
{Klebstoff, Wasch−
Henkel
mittel, Kosmetik}
40191 Düsseldorf
Bemerkungen:
29
2.2.3 Normalisierung (3)
name
H1
Henkel
Henkel Henkelstr. 67 40191
strasse
plz
hnr#
branche#
H1
H1
H1
Kosmetik
Waschmittel
Klebstoff
ort
Düsseldorf
hersteller_branche
entferne Attribut branche aus Relation hersteller
bilde neue Relation hersteller_branche, die branche
und den Primärschlüssel von hersteller enthält
Primärschlüssel von hersteller_branche ist {hnr, branche}
für jede Branche eigenes Tupel in hersteller_branche
31
2.2.3 Normalisierung (6)
hersteller
hnr#
name
H1
hersteller
adresse besteht aus drei Unterwerten
branche ist Menge von Werten, d.h. selber wieder Relation
Objektrelationale DBS (z.B. PostgreSQL) erlauben
benutzerdefinierte zusammengesetzte Datentypen und Arrays.
Erfordert Möglichkeit, eigene Operatoren zu definieren.
Achtung: manche Autoren (z.B. Date) lassen frei definierte
Datentypen auch im rein relationalen Modell zu
Verschachteltes relationales Modell erlaubt Relationen als Werte
hnr#
Lösung 2:
adresse
branche
hersteller
Henkelstr. 67
{Klebstoff, Wasch−
40191 Düsseldorf mittel, Kosmetik}
hnr# name
Wie kann dieses Schema normalisiert werden?
zusammengesetzter Wert für adresse:
ersetze adresse durch drei Felder strasse, plz, ort
strasse
plz
ort
branche#
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
H1
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
Füge in hersteller pro branche ein Tupel ein, so dass für einen
Hersteller mit n Branchen n Tupel vorhanden sind
Der Primärschlüssel von hersteller muss dann erweitert werden
zu {hnr, branche} (Warum?)
Mehrfachwerte für branche:
verschiedene Lösungen möglich
30
32
Dalitz DBS Kap2.1-3
2.2.3 Normalisierung (7)
2.2.3 Normalisierung (9)
Lösung 3:
Redundanzen und Anomalien
hersteller
hnr#
name
strasse
H1
Henkel Henkelstr. 67 40191
branche1
plz
branche2
Klebstoff Waschmittel
Die in Lösung 2 eingeführten Redundanzen sind
besonders unerwünscht, weil sie zu Inkonsistenzen
bei Änderungen führen (Update-Anomalien)
ort
Düsseldorf
...
...
Das Problem ist, dass bei Änderungen mehrere Tupel
verändert werden müssen, wenn die Information nicht
inkonsistent werden soll.
brancheN
NULL
Problem betrifft alle Änderungen:
möglich, wenn maximale Anzahl Branchen N bekannt
ersetze in hersteller das Attribut branche durch die N Attribute
branche1, branche2, ..., brancheN
bei weniger Branchen Werte mit NULL auffüllen
Einfüge-Anomalien
Lösch-Anomalien
Modifikations-Anomalien
33
2.2.3 Normalisierung (8)
35
2.2.3 Normalisierung (10)
hersteller
Bewertung der drei Lösungen
hnr# name
Nachteile von Lösung 3:
begrenzt maximale Anzahl Branchen
führt Nullwerte ein für Hersteller mit weniger als N Branchen
Nachteile von Lösung 2:
strasse
plz
ort
branche#
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
H1
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
H1
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
Einfüge-Anomalien (1)
führt Redundanzen ein: überflüssige Mehrfachspeicherung
der Attribute hnr, name, strasse, plz, ort
=> Speicherplatzverschwendung
=> Updates kompliziert, da Gefahr der Inkonsistenz
Wenn zum Hersteller ’H1’ eine weitere Branche ergänzt
werden soll, müssen für name, strasse, plz, ort die passenden
Werte eingefügt werden (sonst Inkonsistenz!)
Korrektheit dieser Werte lässt sich (außer über Trigger)
nicht einfach durch Integrity Constraints erzwingen
=> keine Garantie für konsistenten Inhalt
Lösung 1 ist unbedingt vorzuziehen
34
36
Dalitz DBS Kap2.1-3
2.2.3 Normalisierung (11)
2.2.3 Normalisierung (13)
Lösch-Anomalien
hersteller
Wenn ein Hersteller gelöscht wird, der zufällig als einziger
einer bestimmten Branche angehört, werden die Informationen
über diese Branche alle mitgelöscht
hnr# name
Problem betrifft auch Lösung 1 und kann durch Einführung
einer Referenztabelle branche gelöst werden
hersteller
hnr#
H1
hnr#
H1
H1
H1
bnr#
B3
B2
B1
name
Kosmetik
Waschmittel
Klebstoff
hnr#
H1
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
H1
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
39
Inhaltliche Bedeutung (Semantik) der Relationen
und Attribute sollte leicht verständlich sein
branche
bnr#
B3
B2
B1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
H1
Informelle Qualitätsmaße für den Entwurf eines
Relationsschemas:
name
strasse
plz
ort
Henkel Henkelstr. 67 40191 Düsseldorf
bnr#
B3
B2
B1
H1
2.2.4 Designrichtlinien
hersteller
hersteller_branche
branche#
37
2.2.3 Normalisierung (12)
hnr#
H1
ort
Wenn sich die Adresse des Herstellers ’H1’ ändert, muss sie
in allen Tupeln gleich geändert werden
eine nicht gleichartige oder nur teilweise Änderung
lässt sich nicht automatisch abfangen und abweisen
branche
bnr#
B3
B2
B1
plz
Modifikations-Anomalien
name
strasse
plz
ort
Henkel Henkelstr. 67 40191 Düsseldorf
hersteller_branche
strasse
name
Kosmetik
Waschmittel
Klebstoff
Reduzierung redundanter Werte in Tupeln
Einfüge-Anomalien (2)
Reduzierung der Nullwerte in Tupeln
Lösung mit Referenztabelle branche ermöglicht auch
das Einfügen von Branchen unabhängig von Herstellern
insbesondere wird es dadurch möglich, Branchen einzufügen,
denen noch kein Hersteller zugeteilt ist
Verhinderung der Erzeugung "unechter" Tupel
38
40
Dalitz DBS Kap2.1-3
2.3 Normalformen (1)
2.3.1 Abhängigkeit (1)
Bei Normalisierung enstanden Modelle mit selbem
Informationsgehalt, aber unterschiedlicher "Güte":
2.3.1 Funktionale Abhängigkeit
Variante 1
hersteller
hersteller_branche
hnr# name strasse plz ort
Variante 2
Eine funktionale Abhängigkeit “X → Y ” zwischen zwei
Attributmengen X, Y eines Relationsschemas R
besteht, wenn für beliebige Tupel t1 , t2 ∈ r(R) gilt:
hnr# branche#
hersteller
t1 [X] = t2 [X]
hnr# name strasse plz ort branche#
Variante 2 führt zu unerwünschten Redundanzen,
die Update-Anomalien verursachen
Variante 1 hat diesen Nachteil nicht
=⇒
t1 [Y ] = t2 [Y ]
Die Werte der X-Komponente eines Tupels bestimmen
also eindeutig alle Werte der Y -Komponente.
Die Y -Werte sind also eine Funktion der X-Werte:
Ziele:
formale Definition von Redundanzen
Kriterien für Redundanzfreiheit
41
t[Y ] = f (t[X])
43
2.3 Normalformen (2)
2.3.1 Abhängigkeit (2)
Redundanz eines Attributs:
Ein Attribut ist redundant, wenn einzelne
Attributwerte ohne Informationsverlust
weggelassen werden können.
Beispiel
hersteller
hnr# name
hersteller
hnr# name
redundante Attribute
strasse
plz
ort
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
ort
branche#
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
{hnr, branche} → {name, strasse, ort, plz}
{hnr} → {strasse, ort}
{hnr, name} → {plz}
Es gilt nicht
überflüssige Werte
Wie können wir Redundanz formal definieren?
plz
Es gilt
branche#
H1
strasse
{hnr} → {branche}
{branche} → {name, plz}
42
44
Dalitz DBS Kap2.1-3
2.3.1 Abhängigkeit (3)
2.3.1 Abhängigkeit (5)
hersteller
hnr# name
strasse
plz
ort
Armstrongs Regeln
branche#
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
H1
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
IR1 Reflexivität:
Y ⊆X
Bemerkungen (1)
X→Y
In obiger Beipielrelation gilt {strasse} → {ort}
Trotzdem keine funktionale Abhängigkeit
45
2.3.1 Abhängigkeit (4)
⇒
X→Z
47
(Beweis der Regeln an der Tafel)
2.3.1 Abhängigkeit (6)
hersteller
ort
X ∪Z →Y ∪Z
X → Y und Y → Z
keine beweisbaren Tatsachen
erfordert Intuition des Datenbankdesigners
plz
⇒
IR3 Transitivität:
funktionale Abhängigkeit ist Eigenschaft der Bedeutung
(Semantik) der Attributwerte
strasse
X→Y
IR2 Augmentation:
funktionale Abhängigkeit ist Eigenschaft des Relationsschemas R
und nicht eines Relationszustands r(R)
hnr# name
⇒
Anmerkungen zu Armstrongs Regeln:
branche#
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
H1
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
Regeln IR1 und IR2 aus Buch von Elmasri, Navathe sind falsch!
Die durch IR1 gegebenen Abhängigkeiten heißen trivial
Armstrong hat 1974 gezeigt, dass die Regeln IR1-3
vollständig sind:
Wenn man diese Regeln solange auf eine Menge F von
funktionalen Abhängigkeiten anwendet, bis keine neuen
Abhängigkeiten mehr erzeugt werden, so erhält man alle
Abhängigkeiten, die aus F herleitbar sind
Bemerkungen (2)
Wenn P = {hnr, branche} die Menge der Primärschlüsselattribute
ist, dann gilt für jede beliebige Attributmenge X: P → X
Aus gegebenen funktionalen Abhängigkeiten lassen sich weitere
Abhängigkeiten ohne Kenntnis der Attributbedeutung ableiten
Die Menge aller aus F herleitbaren Abhängigkeiten
heißt Hülle (Closure) von F (symbolisch F+)
z.B. folgt aus {hnr} → {strasse, ort} automatisch {hnr} → {strasse}
Für die Ableitung weiterer Abhängigkeiten gibt es Regeln,
die sog. Inferenzregeln (Inference Rules)
46
48
Dalitz DBS Kap2.1-3
2.3.1 Abhängigkeit (7)
2.3.1 Abhängigkeit (9)
Erste Anwendung Abhängigkeiten:
Beispiel:
hersteller
hnr# name
strasse
plz
ort
Schema 1
branche#
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
H1
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
Schema 2
hersteller
hnr# name strasse plz ort branche#
hersteller
hnr# name strasse plz ort
{hnr} → {name}
{hnr, branche} → {hnr, name, strasse, plz, ort, branche}
49
Frage: Wann ist Zerlegung verlustfrei (additiver Join)?
2.3.1 Abhängigkeit (8)
2.3.1 Abhängigkeit (10)
Weitere, aus IR1-3 ableitbare Inferenzregeln:
Theorem (Heath 1971):
IR4 Zerlegung:
⇒
IR5 Vereinigung:
⇒
Wenn die funktionale Abhängigkeit A → B gilt,
dann ist jeder Relationszustand r(R) gleich dem
Join seiner Projektionen über {A,B} und {A,C}
X →Y ∪Z
IR6 Pseudotransitivität:
X → Y und W ∪ Y → Z
⇒
W ∪X →Z
Bemerkungen:
IR7 Komposition:
X → Y und V → W
⇒
X ∪V →Y ∪W
(Beweis: IR5+7 an Tafel, IR4+6 Übung)
51
Sei R(A,B,C) ein Relationsschema, wobei A, B und C
Attributmengen sind.
X → Y und X → Z
X → Y und X → Z
hnr# branche#
Schema 2 kann als Zerlegung von Schema 1 aufgefasst werden
Relation von Schema 1 lässt sich aus den Relationen von
Schema 2 komplett rekonstruieren:
SELECT h.hnr,name,strasse,plz,ort,branche
FROM hersteller h, hersteller_branche b
[ LEFT OUTER ] JOIN ON h.hnr = b.hnr;
Aus {hnr} → {name, strasse, plz, ort} lässt sich nur
mit Armstrongs Regeln ableiten (Wie?):
X →Y ∪Z
hersteller_branche
50
die Bedingung ist hinreichend, aber nicht notwendig
äquivalentes Kriterium über mehrwertige Abhängigkeiten (später)
Dieses Theorem ist die Grundlage des Normalisierungs-Prozesses
52
Dalitz DBS Kap2.1-3
2.3.1 Abhängigkeit (11)
hnr name
H1
H1
H2
strasse
plz
ort
2.3.2 2NF und 3NF (2)
NF’s auf Basis projektiver Zerlegung
branche
Erste Normalform (1NF)
Henkelstr. 67 40191 Düsseldorf Kosmetik
Henkel
Henkelstr. 67 40191 Düsseldorf Waschmittel
Henkel
Unilever Dammtorwall 15 20355 Hamburg
Waschmittel
Zerlegung 1
Dritte Normalform (3NF)
hst2
hst1
hnr
Zweite Normalform (2NF)
branche
strasse plz ort
hnr
Boyce−Codd Normalform (BCNF)
name
Vierte Normalform (4NF)
Zerlegung 2
hst3
branche name strasse plz ort
hst4
Fünfte Normalform (5NF)
nur triviale
Verbundabhängigkeiten
hnr branche
Join von Zerlegung 1 erzeugt wieder Ursprungstabelle
Muss laut Heath’s Theorem so sein wegen {hnr} → {name}
Join von Zerlegung 2 erzeugt Zusatztupel (Welche?)
Bezeichnung in Literatur: "nicht additiver Join"
Da Heath’s Theorem nur hinreichende Bedingung angibt,
kann das nicht aus fehlender Abhängigkeit gefolgert werden
keine mehrwertigen Abhängigkeiten
nur Abhängigkeiten vom Schlüssel
keine transitiven Abhängigkeiten
alle Attribute voll vom Schlüssel abhängig
53
55
beliebige Relation mit atomaren Werten
2.3.2 2NF und 3NF (1)
2.3.2 2NF und 3NF (3)
2.3.2 Zweite und Dritte Normalform
Bemerkungen zu den Normalformen:
jede Stufe definiert echt strengere Kriterien, d.h. ein
Relationsschema in x-NF ist auch in y-NF für alle y < x
Normalisierungsprozess
Formalismus zum Erkennen von Redundanzen (Normalformtests)
Zerlegungsregeln um redundante Schemas durch weniger
redundante Schemas zu ersetzen (Randbedingung: additiver Join)
Die verschiedenen Normalformen unterscheiden sich durch
die Art der Redundanzen, die noch zugelassen werden.
Höhere Normalform = strengere Kriterien
2NF, 3NF und BCNF basieren auf funktionalen Abhängigkeiten
4NF und 5NF basieren auf mehrwertigen Abhängigkeiten
in der Praxis beschränkt man sich meist auf 3NF bzw. BCNF,
da die Abhängigkeiten für 4NF relativ selten sind und
die Abhängigkeiten für 5NF schwer zu erkennen sind
Denormalisierung
es gibt weitere Normalformen, die allerdings nicht (ausschließlich)
auf Projektion und Join beruhen, z.B. Domain-Key NF
oft werden Redundanzen geduldet. Mögliche Gründe:
Performance: Join über viele Tabellen oft zeitraubend
Praktikabilität: Anwender hat kein Recht Referenzwerte zu pflegen
Zulassung von Redundanzen heißt Denormalisierung
54
56
Dalitz DBS Kap2.1-3
2.3.2 2NF und 3NF (3)
redundante Attribute
hersteller
hnr# name
2.3.2 2NF und 3NF (5)
strasse
plz
ort
Beispiel:
hst1
branche#
H1
Henkel Henkelstr. 67 40191 Düsseldorf Kosmetik
H1
H1
Henkel Henkelstr. 67 40191 Düsseldorf Waschmittel
Henkel Henkelstr. 67 40191 Düsseldorf Klebstoff
hnr# name strasse plz ort branche#
hst2
hnr# name strasse plz ort
hst3
hnr# branche#
überflüssige Werte
Fragen:
Wodurch kommt Redundanz der Attribute?
Formale Definition über funktionale Abhängigkeit möglich?
Antwort:
redundante Attribute hängen funktional von einem Teil (hnr)
des Primärschlüssels (hnr, branche) ab
57
Relation hst1 ist nicht in zweiter Normalform, denn
Attribut name hängt von hnr ab, Primärschlüssel ist
aber {hnr, branche}
Relation hst2 ist in zweiter Normalform, denn der
Primärschlüssel enthält nur ein Attribut
Relation hst3 ist in zweiter Normalform, denn es
gibt keine nicht primären Attribute
2.3.2 2NF und 3NF (4)
2.3.2 2NF und 3NF (6)
Definition zweite Normalform
(Annahme: nur ein Schlüsselkandidat)
Ein Relationsschema, das nicht in 2NF ist,
kann in mehrere Schemas in 2NF zerlegt werden
59
a) Fasse alle nicht primären Attribute, die nur von einem
Teilschlüssel abhängen, mit diesem Teilschlüssel als
Primärschlüssel in einer eigenen Tabelle zusammen.
Ein Relationsschema ist in zweiter Normalform (2NF),
wenn jedes nicht primäre Attribut voll funktional vom
Primärschlüssel abhängt.
b) Alle Attribute, die vom selben Teilschlüssel abhängen,
müssen in derselben Tabelle zusammengefasst werden.
c) Entferne die ausgelagerten nichtprimären Attribute aus
der Ursprungstabelle.
Erläuterungen:
primäre Attribute heißen die Attribute des Primärschlüssels
Eine Attributmenge Y heißt von einer Attributmenge X
voll funktional abhängig, wenn sie von keiner echten Teilmenge
von X abhängt. Tut sie es doch, heißt sie partiell abhängig von X.
Bemerkungen:
58
Bedingung a) und Heath’s Theorem gewährleisten additiven Join
Bedingung b) nötig, um Verlust von funktionalen Abhängigkeiten
zu vermeiden
60
Dalitz DBS Kap2.1-3
2.3.2 2NF und 3NF (7)
2.3.2 2NF und 3NF (9)
hst1
Definition Dritte Normalform
(Annahme: nur ein Schlüsselkandidat)
hnr# name strasse plz ort branche#
hst2
hst3
hnr# name strasse plz ort
hnr# branche#
Zerlegung 1
hst3
hst4
hnr# name strasse plz
hnr# ort
Zerlegung 2
Ein Relationsschema ist in dritter Normalform (3NF),
wenn es in 2NF ist und kein nicht primäres Attribut
transitiv vom Primärschlüssel abhängt.
hst5
hnr# branche#
Bemerkungen:
Zerlegung 1 gemäß Regeln a), b) und c)
B heißt transitiv abhängig von A, wenn es eine
Attributmenge C gibt mit A → C und C → B
die beiden Abhängigkeiten bei der Transitivität
dürfen nicht trivial (C ⊆ A oder B ⊆ C) sein
Zerlegung 2 verletzt Regel b), denn ort und plz hängen vom
selben Teilschlüssel hnr ab, sind aber nicht in derselben
Relation zusammengefasst
=> funktionale Abhängigkeit {plz} → {ort} geht verloren
beide Zerlegungen erfüllen "additiven Join"
61
63
2.3.2 2NF und 3NF (8)
2.3.2 2NF und 3NF (10)
Mit der 2NF werden bestimmte Redundanzen
ausgeschlossen. Andere aber nicht:
Gelegentlich andere "Definition" in Literatur:
hnr# name
H1
H2
strasse
de Beukelaer Arnoldstr. 62
Polyant
Speefeld 7
plz
Ein Relationsschema ist in 3NF, wenn es in 2NF ist
und kein nicht primäres Attribut von einer Menge
anderer nichtprimärer Attribute abhängt.
ort
Dies ist keine äquivalente 3NF Definition, sondern nur eine
notwendige (aber nicht hinreichende!) Bedingung für 3NF
47906 Kempen
47906 Kempen
Gegenbeispiel:
redundanter Wert
Relation ist in 2NF (Warum?)
Attribut ort ist redundant
Ursache:
funktionale Abhängigkeit von nichtprimärem Attribut plz
bzw. transitive Abhängigkeit {hnr} → {plz} → {ort}
p1#
p2#
x1
x2
x3
erfüllt obige "Definition"
enthält aber transitive Abhängigkeit {p1#,p2#} → {p2#,x1} → {x3}
62
64
Dalitz DBS Kap2.1-3
2.3.2 2NF und 3NF (11)
2.3.2 2NF und 3NF (13)
Ein Relationsschema, das nicht in 3NF ist,
kann in mehrere Schemas in 3NF zerlegt werden
Beispiel:
Sprachcode
a) Fasse die transitiv abhängigen nicht primären Attribute
mit den Attributen, von denen sie direkt abhängen,
in einer eigenen Tabelle zusammen.
Verlagscode
isbn1 isbn2 isbn3 autor titel verlag sprache jahr
b) Entferne die ausgelagerten abhängigen Attribute aus
der Ursprungstabelle.
hst
hnr# name
strasse plz
hst
hnr# name
Schlüsselkandidat 1
ort
plz
strasse plz
plz# ort
65
2.3.2 2NF und 3NF (12)
Schlüsselkandidat 2
Relation buch hat mehrere Schlüsselkandidaten
Relation ist in Codd-2NF, da alle Attribute zu
Schlüsselkandidaten gehören
Relation ist nicht in Kent-2NF, da sprache partiell abhängig
ist vom Teil isbn1 der ISBN-Nr
67
2.3.2 2NF und 3NF (14)
Verallgemeinerung Definition 2NF auf
beliebige Anzahl Schlüsselkandidaten
Verallgemeinerung Definition 3NF auf
beliebige Anzahl Schlüsselkandidaten
Definition 2NF Codd (1971)
Definition 3NF Codd
Jedes Attribut, das zu keinem Schlüsselkandidaten gehört,
ist von jedem Schlüsselkandidaten voll funktional abhängig.
2NF und kein Attribut, das zu keinem Schlüsselkandidaten
gehört, ist von einem Schlüsselkandidaten transitiv abhängig.
Definition 2NF Kent (1973)
Definition 3NF Kent
Jedes Attribut im Komplement eines Schlüsselkandidaten
ist von diesem Schlüsselkandidaten voll funktional abhängig.
2NF und kein Attribut im Komplement eines Schlüsselkandidaten ist von diesem Schlüsselkandidaten transitiv abhängig.
Bemerkungen:
Bemerkungen:
Definitionen stimmen für nur einen Schlüsselkandidaten überein
im allgemeinen Fall ist die Codd-2NF schwächer (d.h. lässt
mehr Redundanzen zu) als die Kent-2NF (vgl. Übung 5.1)
Definitionen stimmen für nur einen Schlüsselkandidaten überein
wieder ist die Codd-3NF schwächer
66
68
Dalitz DBS Kap2.1-3
2.3.2 2NF und 3NF (15)
2.3.3 4NF (1)
Boyce/Codd Normalform (BCNF)
Mehrwertige Abhängigkeiten
Für jede nicht triviale Abhängigkeit X → A
ist X ein Superschlüssel der Relation.
Betrachte Relation mit mehreren unabhängigen
mengenwertigen Attributen (d.h. nicht in 1NF!)
Bemerkungen:
kette#
erfordert keine Überprüfung von 2NF
ursprünglich als einfachere Definition der 3NF vorgeschlagen,
dann aber nachgewiesen, dass BCNF strenger als 3NF ist:
BCNF ⇒ 3NF, aber nicht umgekehrt:
A#
B#
C
Relation in 3NF,
aber nicht in BCNF
Leffers
69
2.3.2 2NF und 3NF (16)
fd3
hnr# name
ort
fd1
hst2
hnr# name
plz
strasse plz
fd4
{Bekleidung}
{Dortmund,
Hannover}
Ersetzen durch skalare Attribute führt zu Redundanzen,
die keiner funktionalen Abhängigkeit entsprechen
71
Schlechte Umformung in 1NF:
fd2
strasse plz
standort
2.3.3 4NF (2)
Relation R ist in BCNF, wenn für jede nicht triviale Abhängigkeit X → A die Menge X ein Superschlüssel von R ist.
hst1
branche
Karstadt {Lebensmittel, {Essen,
Bekleidung}
Dortmund}
kette#
branche#
standort#
Karstadt
Karstadt
Karstadt
Karstadt
Leffers
Leffers
Lebensmittel
Lebensmittel
Bekleidung
Bekleidung
Bekleidung
Bekleidung
Essen
Dortmund
Essen
Dortmund
Dortmund
Hannover
Tabelle ist redundant:
pro Kette jede Branche mit jedem Standort kombiniert
(nötig, da Branche und Standort unabhängige Eigenschaften)
Trotzdem ist die Relation in BCNF (Warum?)
plz# ort
fd5
Abhängigkeiten fd1 in Relation hst1 sind mit BCNF verträglich
Abhängigkeit fd3 auch (Warum?), aber fd2 nicht
Relationen hst2 und plz sind in BCNF
Brauchen weiteres Kriterium für Redundanz:
mehrwertige Abhängigkeiten
70
72
Dalitz DBS Kap2.1-3
2.3.3 4NF (3)
2.3.3 4NF (5)
Definition:
Die Attributmenge Y ∈ R ist mehrwertig abhängig
von der Attributmenge X ∈ R, symbolisch X →
→Y ,
wenn für alle Tupel t1 , t2 mit t1 [X] = t2 [X]
Tupel t3 , t4 existieren mit
• t3 [X] = t4 [X] = t1 [X] = t2 [X]
branche#
standort#
Lebensmittel
Lebensmittel
Bekleidung
Bekleidung
Bekleidung
Bekleidung
Essen
Dortmund
Essen
Dortmund
Dortmund
Hannover
Anschauliche Bedeutung von X
• t3 [Y ] = t1 [Y ] und t4 [Y ] = t2 [Y ]
Bemerkungen:
!!!!
die vier Tupel sind nicht notwendig verschieden
wegen der Symmetrie in Definition folgt aus X
Y
auch X
Z. Man schreibt deshalb auch X
Y|Z
!!
!!
Y | Z:
Das Ergebnis des Statements
SELECT DISTINCT Y FROM R
WHERE X = ’..’ AND Z = ’..’;
ist für alle (vorhandenen) Werte von Z gleich
• t3 [Z] = t2 [Z] und t4 [Z] = t1 [Z] wobei Z = R \ (X ∪ Y )
73
2.3.3 4NF (4)
Existieren zwei Tupel mit gleichem X aber unterschiedlichem Y,
so müssen diese Y-Werte in getrennten Tupeln für jeden
unterschiedlichen Wert von Z wiederholt werden
75
2.3.3 4NF (6)
Zu t1 , t2 mit t1 [X] = t2 [X] existieren t3 , t4 mit
Triviale mehrwertige Abhängigkeiten
• t3 [X] = t4 [X] = t1 [X] = t2 [X]
!!
wenn Y Teilmenge von X ist oder X ∪ Y = R,
folgt automatisch X
Y
• t3 [Y ] = t1 [Y ] und t4 [Y ] = t2 [Y ]
• t3 [Z] = t2 [Z] und t4 [Z] = t1 [Z] wobei Z = R \ (X ∪ Y )
sagt also nichts Wesentliches aus:
"triviale" Abhängigkeit
Beispiel:
kette#
branche#
standort#
Karstadt
Karstadt
Karstadt
Karstadt
Leffers
Leffers
Lebensmittel
Lebensmittel
Bekleidung
Bekleidung
Bekleidung
Bekleidung
Essen
Dortmund
Essen
Dortmund
Dortmund
Hannover
!!
kette#
Karstadt
Karstadt
Karstadt
Karstadt
Leffers
Leffers
es gilt {kette}
{standort} und {kette}
es gilt nicht {standort}
{kette}
!!
!!
t1
t4
Zusammenhang zu funktionaler Abhängigkeit
t3
!!
t2
aus X → Y folgt X
Umkehrung gilt nicht
{branche}
74
Y
76
Dalitz DBS Kap2.1-3
2.3.3 4NF (7)
2.3.3 4NF (9)
Theorem (Fagin 1977):
Beispiel:
Sei R(A,B,C) ein Relationsschema, wobei A, B und C
Attributmengen sind.
Jeder Relationszustand r(R) ist gleich dem Join
seiner Projektionen über {A,B} und {A,C}, genau
dann wenn die Abhängigkeit A
B | C gilt.
!!
kette#
branche#
standort#
Karstadt
Karstadt
Karstadt
Karstadt
Leffers
Leffers
Lebensmittel
Lebensmittel
Bekleidung
Bekleidung
Bekleidung
Bekleidung
Essen
Dortmund
Essen
Dortmund
Dortmund
Hannover
!!
Relation ist nicht in 4NF, denn es gilt {kette}
{standort},
aber weder standort noch branche sind funktional abhängig
von kette
Bemerkungen:
Im Unterschied zu Heath’s Theorem äquivalente Bedingung für
"additiven Join" (Heath lieferte nur hinreichende Bedingung)
Heath’s Theorem folgt direkt aus diesem Theorem (Warum?)
77
2.3.3 4NF (8)
Beispiel ist typisch: die meisten Relationen mit nichttrivialen
mehrwertigen Abhängigkeiten enthalten nur Schlüsselattribute
79
2.3.3 4NF (10)
Umformung in 4NF:
Vierte Normalform (4NF)
!!
Wenn eine nichttriviale mehrwertige Abhängigkeit
A
B gilt, dann sind alle Attribute der Relation
funktional abhängig von A.
Anders ausgedrückt:
kette#
branche#
standort#
Karstadt
Karstadt
Karstadt
Karstadt
Leffers
Leffers
Lebensmittel
Lebensmittel
Bekleidung
Bekleidung
Bekleidung
Bekleidung
Essen
Dortmund
Essen
Dortmund
Dortmund
Hannover
kette#
Karstadt
Karstadt
Leffers
alle nichttrivialen Abhängigkeiten (mehrwertig oder funktional)
sind funktionale Abhängigkeiten von einem Superkey.
Somit ist eine Relation in 4NF auch immer in BCNF.
4NF = BCNF und alle mehrwertigen Abhängigkeiten sind
funktionale Abhängigkeiten von Schlüsseln.
78
branche#
Lebensmittel
Bekleidung
Bekleidung
kette#
Karstadt
Karstadt
Leffers
Leffers
standort#
Essen
Dortmund
Dortmund
Hannover
!!
Zerlege Relation R=(A,B,C) mit Abhängigkeit A
C
per Projektion in zwei Relationen R1=(A,B) und R2=(A,C)
R1 und R2 sind in 4NF (Warum?)
Aufgrund Fagin’s Theorem ist diese Zerlegung additiv
80
Dalitz DBS Kap2.1-3
2.3.3 4NF (11)
kette#
branche
standort
Karstadt {Lebensmittel, {Essen,
Bekleidung}
Dortmund}
Leffers
{Bekleidung}
{Dortmund,
Hannover}
Mehrwertige Abhängigkeiten hätten früh im Design
vermieden werden können bei Umformung in 1NF
Faustregel: unabhängige mengenwertige Attribute
in separate Relationen trennen!
Theorie der 4NF gibt strenge Begründung
für diese Faustregel
81
2.3.3 4NF (12)
Weitere Normalformen
Project-Join Normalform oder 5NF
betrachtet "Join Dependencies", das sind Bedingungen
bzgl. der projektiven Zerlegung über Attributmengen
Verletzungen treten in Praxis selten auf (pathologische Fälle?)
und sind schwer zu entdecken
=> geringe praktische Bedeutung
Domain-Key Normalform (DKNF)
erfasst beliebige Constraints
alle Constraints sollen aus Domain-Constraints und
Key-Constraints folgen
impliziert 5NF
Bedingungen ungeklärt, wann DKNF möglich
82
Dalitz DBS Kap2.1-3
2.4 Semantische Modelle
2.4.1 Semantischer Ansatz (3)
2.4.1 Der semantische Ansatz
Basiselemente Semantischer Datenmodelle:
Ebenen des Datenbankentwurfs (Wdh.):
Entity - unterscheidbares "Real World" Objekt
Property - Eigenschaft, die ein Objekt beschreibt
Relationship - Zusammenhang zwischen Entities
konzeptionelle Ebene
logische Gesamtsicht des Anwenders auf die Daten
unabhängig vom eingesetzten DBS-Typ
Implementierungsebene
Semantische Modelle unterschieden sich
konzeptionelle Datenstrukturen im Rahmen des
eingesetzten DBS
bei relationalem DBS z.B. Tabellen
in der Art der unterstützten Relationships und
den Abstraktionsmechanismen für Relationships
(z.B. Abhängigkeit, Aggregation, Vererbung)
in der Darstellung der Basiselemente und insbesondere
der Relationships, z.B. durch
physische Ebene
konkrete Implementierung der Strukturen im Rahmen
des eingesetzten DBS
betrachtete Strukturen: Datenblöcke, Zeiger, Indexstrukturen
spezielle Diagrammsymbole und Linien (Entity-Relationship Modell)
Darstellung als Funktionen (Funktionales Datenmodell)
1
2.4.1 Semantischer Ansatz (2)
2.4.1 Semantischer Ansatz (4)
Auch Relationales Modell hat diese Basiselemente:
Semantisches
Modell
Entity: Relation
Property: Attribut, Primary Key Constraint
Relationship: Foreign Key Constraint
konzeptionelle Ebene
Implementierungsebene
Hierarchisches
Modell
Relationales
Modell
3
Semantik ist aber unzureichend dargestellt:
Objektrelato−
nales Modell
Schema 1
kunde
kdnr#
name
adresse
Semantisches Datenmodell
versucht mehr von Daten-Bedeutung (Semantik) zu erfassen
modelliert auf konzeptioneller Ebene
unabhängig vom eingesetzten Datenbank-System
kann in verschiedene DBS-Typen implementiert werden
bei Übergang zu Implementierungsebene geht Information
verloren => Designschritt nicht reversibel
2
Schema 2
kd_ansp
kdnr#
apnr#
name
telefon
kunde
kdnr#
name
adresse
kd_ansp
kdnr
apnr#
name
telefon
Tabelle kd_ansp ist abhängig von kunde, d.h. representiert
eigentlich eine Eigenschaft von Kunde
Zusammenhang in Schema 1 nur implizit representiert (Wodurch?)
diese Bedeutung ist in Schema 2 gar nicht representiert
4
Dalitz DBS Kap2.4
2.4.1 Semantischer Ansatz (5)
2.4.2 ER Modell (2)
Vorteile semantischer Modelle
Entity
intuitiver verständlich (auch für Nichtexperten!)
leichtere Modellierung durch größere Nähe zur "realen Welt"
Designer wird von Details der DBS entlastet (CASE-Tools)
grafische Notation (Diagramme) anschaulich und
(wenn grob vereinfacht) "pflichtenhefttauglich"
strong (regular) Entity
eigenständiges Objekt;
kann unabhängig von anderen Objekten existieren
weak Entity
abhängiges Objekt;
kann nur existieren wenn ein Objekt aus einer
anderen Entity existiert
Nachteile semantischer Modelle
Diagramme bei größeren Modellen nicht mehr praktikabel
Schritt zu SQL-DDL ist irreversibel
=> kein "Reverse Engineering" möglich
=> Änderungen an implementiertem Modell schwierig
geringe Unterstützung für Constraints und Tuning-Parameter
Unterscheidung Entity/Relationship oft künstlich
=> direkte relationale Modellierung manchmal intuitiver
strong
Entity
weak
Entity
5
7
2.4.2 ER Modell (1)
2.4.2 ER Modell (3)
Das verbreitetste semantische Modell ist
das Entity-Relationship Modell (ER Modell):
Beispiel
1976 von Chen vorgeschlagen
beinhaltet bestimmte Diagrammnotation (ER Diagramm)
ist später erweitert worden um Varianten der Vererbung
(Spezialisierung, Generalisierung, Kategorien)
Einige Autoren (z.B. Elmasri, Navathe) unterscheiden zwischen
der originalen Formulierung von Chen und den Erweiterungen
("extended ER" bzw. "EER")
Erweiterung des ER Modells auf allgemeine Softwareentwicklung
(nicht nur DB-Design) in Form von OMT und UML
UML (Unified Modelling "Language") benutzt aber andere
Diagrammsymbole und Begriffe
Kunde
angestellt
bei
Ansprech−
partner
Ansprechpartner ist kein unabhängiges Objekt
existiert nur, wenn entsprechender Kunde auch existiert
Bemerkungen:
6
Es ist oft nicht offensichtlich, ob eine Entity "weak" ist
z.B. kann im Hersteller/Produkt Beispiel die Entity produkt
sowohl als strong, als auch als weak aufgefasst werden (Warum?)
ob weak oder strong hängt von logischer Sicht auf die Daten ab
=> Semantik der Entities wird modelliert
8
Dalitz DBS Kap2.4
2.4.2 ER Modell (4)
2.4.2 ER Modell (6)
Property (Attribut)
Relationship
atomar, zusammengesetzt
stellt Zusammenhang zwischen Entities her
Darstellung durch Raute mit Linien zu
beteiligten Entities
Anzahl beteiligter Entities heißt
Grad der Relationship
Attribute werden durch Blasen dargestellt, zusammengesetzte
Attribute (Strukturen) durch Zerlegung in weitere Blasen
Schlüssel
Schlüsselattribute werden unterstrichen
mehrwertig (mengenwertig)
mehrwertige Attribute erhalten doppelten Rand
Feld1
Attribut
Key
Struktur
mehrw.
Attribut
E3
Feld2
E1
R1
E2
(Grad 2)
R2
(Grad 3)
Feld3
Entity
E4
9
2.4.2 ER Modell (5)
2.4.2 ER Modell (7)
Beispiel
Relationship kann auch reflexiv sein, d.h. Objekte
derselben Entity miteinander verknüpfen:
strasse
name
ort
adresse
hnr
Mitarbeiter
branche
hersteller
Vorgesetzter
von
Relationship zwischen strong und weak Entity
durch doppelten Rahmen gekennzeichnet:
Bemerkungen:
zusammengesetzte und mehrwertige Attribute sind eigentlich
überflüssig (siehe Diskussion zum Thema 1NF)
mehrwertige Attribute machen aber Semantik klarer
bei großer Zahl Attribute sind Blasen nicht mehr darstellbar
=> als Spaltenvektor darstellen (vgl. UML Klassendiagramm)
11
Kunde
10
angestellt
bei
Ansprechpartner
12
Dalitz DBS Kap2.4
2.4.2 ER Modell (8)
2.4.2 ER Modell (10)
Kardinalität einer Relationship
Relationships können auch Attribute haben
gibt an wieviele Elemente derselben Entity an
Relationship beteiligt sein können
durch Zahlen an Verbindungslinien angegeben
Lieferant
Produkt
m
n
beliefert
Beispiel:
Rabatt
Hersteller
1
n
stellt
her
k
Produkt
1:n Beziehung zwischen Hersteller:Produkt
ein Hersteller stellt mehrere (n) Produkte her
ein Produkt hat einen (1) Hersteller
Kunde
13
2.4.2 ER Modell (9)
Verwischt Grenze zwischen Entity und Relationship
Chen spricht von "Relationship Relation"
2.4.2 ER Modell (11)
Komplettes Beispiel:
Kardinalitätstypen
1:1 (One-to-One), 1:n (One-to-Many),
n:m (Many-to-Many). Werte für n,m > 0
Wenn auch kein Element zulässig, explizit Null mit
angeben, z.B. 0,1:1 oder 1:0,n
n
nr
branche
name
Lieferant
1
stellt
her
nr
m
n
adresse
name
Produkt
preis
beliefert
Produkt
strasse
Hersteller
15
n
plz
Rabatt
ort
k
nr
ausge−
liefert
telefon
name
Kunde
1
name
strasse
adresse
plz
0,m
Lieferung
nr
14
Ansprechpartner
0,n
ange−
stellt
ort
16
Dalitz DBS Kap2.4
2.4.2 ER Modell (12)
2.4.3 ER -> Relational (2)
Zusammenfassung ER-Notationen:
Strong Entities
strong Entity
weak Entity
Relationship
Zuordnung
strong/weak Entity
zusammengesetzte und mehrwertige Attribute können
schon auf der ER-Ebene nach dem Muster der Normalisierung
(siehe erste Normalform) umgeformt werden
hnr
name
hnr
name
ort
Attribut
partieller Schlüssel
einer weak Entity
mehrwertiges
Attribut
branche
E2
1
hersteller
0,n
ort
Relationship
mit Kardinalitäten
R
0,m
strasse
branche
17
2.4.3 ER -> Relational (1)
strasse
hst−
branche
adresse
E1
0,n
Schlüssel−
attribut
hersteller
nr
name
19
2.4.3 ER -> Relational (3)
Weak Entities (1)
Umwandlung ER in Relationales Schema
eigene Relation
Primary Key = eigene Key Attribute + PK strong Entity
ODER eigener künstlicher Schlüssel (nicht empfohlen (Warum?))
wenn zusammengesetzte Keys unerwünscht sind
Allgemeines Vorgehen
jede Entity wird Relation
Attribute und Primary Key (PK) werden übernommen
jede Relationship wird Relation
Primary Key = alle PK’s beteiligter Entities
name
nr
strasse
Kunde
ort
1
Feinheiten
ange−
stellt
mehrwertige und zusammengesetzte Attribute
Behandlung von weak Entities
nicht alle Relationships brauchen eigene Relation
=> abhängig von Kardinalität der Relationship
Art der Foreign Key Constraints
0,n
Ansprechpartner
nr
18
name
telefon
kunde
nr#
name
strasse
ort
ansprechpartner
kundenr#
nr#
name
telefon
20
Dalitz DBS Kap2.4
2.4.3 ER -> Relational (4)
2.4.3 ER -> Relational (6)
Weak Entities (2)
One-To-Many Relationship (1)
abhängige Entity ist an andere strong Entity gebunden
=> bei Foreign Key Constraint folgende Optionen nötig:
ON DELETE CASCADE
ON UPDATE CASCADE
CREATE TABLE kunde (
nr
INT8,
name
VARCHAR(30),
strasse VARCHAR(30),
ort
VARCHAR(30),
PRIMARY KEY (nr)
);
1:n Relationship kann wie n:m Relationship umgesetzt werden
Unterschied: PK der Entity am "1-Ende" nicht in PK der
"Relationship-Relation" mit aufnehmen (Warum?)
nr
CREATE TABLE ansprechpartner (
kundenr INT8 REFERENCES kunde(nr)
ON DELETE CASCADE ON UPDATE CASCADE,
nr
INT,
name
VARCHAR(30),
telefon VARCHAR(30),
PRIMARY KEY (kundenr, nr)
);
on delete cascade bewirkt Löschung von Ansprechpartner
wenn referenzierter Kunde gelöscht wird
on update cascade ändert Fremdschlüssel in Ansprechpartner
mit bei Schlüsseländerung des referenzierten Kunden
nr# name
1
stellt her
n
nr# name
21
...
23
Beobachtung: separate Relation hersteller_produkt unnötig
Zusammenlegung mit produkt ergibt:
nr# name
hersteller
nr# name
kunde
kunde_produkt
kundenr# produktnr#
hersteller
hersteller_produkt
herstellernr produktnr#
...
nr# name
m
produkt
nr# name herstellernr produkt
Vorteile:
Produkt
nr# name
nr
produkt
name
One-To-Many Relationship (2)
name
bezieht
nr
2.4.3 ER -> Relational (7)
n:m Relationships werden eine eigene Relation
Primary Key = PK’s aller beteiligten Entities
Kunde
n
hersteller_produkt
herstellernr produktnr#
Produkt
Many-To-Many Relationship
nr# name
hersteller
Hersteller
2.4.3 ER -> Relational (5)
nr
name
produkt
name
Foreign Key Constraints mit on update cascade Option
22
weniger Relationen
klarere Semantik: Hersteller Eigenschaft von Produkt
Aber: wenn oft kein Hersteller bekannt, vermeidet
linke Lösung NULL-Werte in Foreign Key Feld
24
Dalitz DBS Kap2.4
2.4.3 ER -> Relational (8)
2.4.3 ER -> Relational (10)
One-To-One Relationship
Zusammenfassung
auch bei 1:1 Relationship keine eigene Relation nötig
PK einer Entity als Foreign Key in andere Entity aufnehmen
nr
name
nr# name
Personal
1
personal
nr# name chefnr abteilung
Chef von
0,1
nr# name chefvon personal
ER Modell
Relationales Modell
Entity
einfaches Attribut
zusammengesetztes Attribut
Schlüsselattribut
mehrwertiges Attribut
1:1 oder 1:N Relationship
Relation
Attribut
mehrere Attribute
Primary Key
Relation mit Fremdschlüssel
Fremdschlüssel in Relation auf
Seite der höheren Kardinalität
(Alternative: separate Relation)
Relation mit zwei Fremdschlüsseln
Relation mit n Fremdschlüsseln
Abteilung
nr
nr# name
name
N:M Relationship
Relationship n-ten Grades
abteilung
obere Lösung ist besser (Warum?)
Regel: erweitere Tabelle am "0-Ende"
25
27
2.4.3 ER -> Relational (9)
2.4.4 Entwurfsfragen (1)
Relationship höheren Grades
Anforderungen an Datenmodell
eigene Relation
PK beteiligter Relationen als Foreign Keys
PK = PK’s beteiligter Relationen mit Kardinalität > 1
Produkt
Lieferant
lieferant
a) vollständig
b) minimal bzw. redundanzfrei
c) einfach und verständlich
produkt
Damit zusammenhängende Aspekte
m
n
beliefert
belieferung
lfnr# pdnr# kdnr# rabatt
Namenskonventionen
Auswahl des Elements (Entity, Attribut, Relationship)
Mehrwert (?) von ER versus Relational
allgemeines Vorgehen (Top-Down, Bottom-Up)
Rabatt
k
Kunde
kunde
26
28
Dalitz DBS Kap2.4
2.4.4 Entwurfsfragen (2)
2.4.4 Entwurfsfragen (4)
Namenskonvention
Wahl des Basiselements
Oft gibt es mehrere Möglichkeiten, einen
Sachverhalt zu representieren
Namen von Objekten des Modells sollen Bedeutung
entsprechen => leichter verständlich
Konventionen erleichtern Verwendung
(leichter merkbar, Fremdschlüssel erkennbar)
a) Attribut versus Entity und Relationship
b) Relationship versus Entity
Alternative a) ist echte Designfrage mit Auswirkungen
auf Dateneingabe und Datenkonsistenz.
Beipielkonventionen (1)
Enitäten konsistent im Singular oder Plural. Beides sinnvoll:
select * from kunden;
select kunde.nr,kunde.name from kunde ...;
übliche Konvention: Singular
Alternative b) ist künstliches Problem im ER Modell
Name von Key und Bedeutung einheitlich, z.B. nr und name
29
im Relationalen Modell kein Unterschied
ER Modell unterstützt Foreign Keys nur implizit
durch Relationships und weak Entities
neuere Modelle und CASE-Tools erweitern ER um Relationale
Konzepte => Unterscheidung Entity/Relationship aufgehoben
2.4.4 Entwurfsfragen (3)
2.4.4 Entwurfsfragen (5)
Beispielkonventionen (2)
Attribut versus Entity + Relationship (1)
Name Fremdschlüsselattribut = referenzierte Tabelle + PK
hersteller
nr#
name
branchenr
Betrachte Eigenschaft "Branche" eines Herstellers
Lösung 1:
branche
nr#
name
name
nr
Name Relationship-Relation zusammengesetzt aus Namen
der beteiligten Entities; ebenso bei weak Entities
Branche
n
Anspr.
n
1
m
Kunde
31
branche
hersteller
ER Modell
branche
branche_kunde
kunde
kunde_ansp
30
hersteller
nr#
name
hst_branche
hstnr#
branche#
Relationales Modell
Branche als mehrwertiges Attribut modelliert
freie Text-Eingabe für Branche möglich
=> leichte Eingabe (keine Referenzdatenpflege)
=> Auswertung über Branche schwierig (z.B. Tippfehler)
Branchen nicht separat pflegbar sondern abhängig von Hersteller
32
Dalitz DBS Kap2.4
2.4.4 Entwurfsfragen (6)
2.4.4 Entwurfsfragen (8)
Attribut versus Entity + Relationship (2)
Entity versus Relationship (1)
Lösung 2:
Betrachte Modellierung eines Schachturniers
nr
ER Modell
name
hersteller
nr#
name
hersteller
spieler
nr#
name
elozahl
herstellernr# branchenr# hst_branche
nr
name
Relationales Modell ist offensichtlich
Relationa−
les Modell
branche
nr#
name
partie
runde#
weissnr#
schwarznr#
ergebnis
branche
Branche als eigene, Hersteller-unabhängige Entity modelliert
keine freie Eingabe möglich, sondern Auswahlliste
=> Pflege separater Referenztabelle branche nötig
=> Auswertungen über Branchenschlüssel möglich
ER Modell ist weniger offensichtlich
Ursache:
33
Foreign Keys kennt das ER Modell nicht
tauchen nur implizit auf bei Relationship oder weak Entity
=> zwei Modellierungsalternativen
2.4.4 Entwurfsfragen (7)
2.4.4 Entwurfsfragen (9)
Attribut versus Entity + Relationship (3)
Entity versus Relationship (2)
Alternative besteht nicht nur bei mehrwertigen
Attributen, sondern bei allen Attributen
Lösung 1:
Verwende Referenztabelle, wenn Attributwerte nicht beliebig
sind, sondern aus (konfigurierbarer!) Werteliste kommen sollen
Unterschied zu Domain-Constraint (Check-Constraint):
Werteliste änderbar ohne Schemaänderung
klausel
art
Versicherung
nr
zahlweise
nr
name
elozahl
art und klausel müssen über
Referenztabelle modelliert werden
zahlweise kann über Constraint
modelliert werden:
Werte 1,2,4,6,12 sind fest
Werte tragen Bedeutung
für Berechnungen
n
35
runde
trifft
auf
Spieler
m
ergebnis
Partien modelliert als Relationship zwischen zwei Spielern
Probleme:
34
selbe Begegnung mehrmals möglich (gelöst über zusätzlichen Teilschlüssel runde)
Wer hat Weiß, wer Schwarz (wäre allerdings bei anderer Sportart egal)?
Eigentlich interessierendes Objekt "Partie" taucht gar nicht auf!
36
Dalitz DBS Kap2.4
2.4.4 Entwurfsfragen (10)
2.4.4 Entwurfsfragen (12)
Entity versus Relationship (3)
allgemeines Vorgehen
Lösung 2:
name
Top-Down
weiß
nr
n
1
Spieler
Partie
1
elozahl
starte mit grobem Entwurf, der zunehmend verfeinert wird
(z.B. durch Einführung von Referenztabellen)
Erleichtert Verständnis des Systems, da zunächst aus der
"Vogelperspektive" modelliert wird
runde
n
ergebnis
schwarz
Bottom-Up
arbeite Details aus und füge sie zu Gesamtsystem zusammen
Gesamtverständnis des Systems ist so schwerer zu gewinnen,
Gefahr des Verlierens im Detail wegen "Käferperspektive"
Partien modelliert als weak Entity, die von zwei strong
Entities abhängt (=> FK’s gehen in PK ein)
trotz dieses kuriosen Konstrukts angemessener:
Information "weiss/schwarz" dargestellt
"Partie" als Hauptgegenstand der Anforderung taucht explizit auf
37
39
2.4.4 Entwurfsfragen (11)
2.4.4 Entwurfsfragen (13)
Entity versus Relationship (4)
In Praxis liefert Anforderungsanalyse meist vor allem Detailwissen
=> zunächst Bottom-Up Analyse nötig vor Top-Down Design
Interessanterweise führen beide ER-Lösungen
zu demselben Relationalen Modell
spieler
nr#
name
elozahl
Gesamt−
überblick
partie
runde#
spieler1nr#
spieler2nr#
ergebnis
Folgerungen:
Übergang ER -> Relational ist irreversibel:
aus Relationalen Schema lässt sich nicht mehr rekonstruieren,
aus welchem ER Schema es erzeugt wurde
Semantik von Lösung 1 (beide Spieler gleichwertig) geht
im Relationalen Modell verloren
Top−Down
Design
Bottom−Up
Analyse
Anforderungs−
analyse
38
Datenmodell
Abgleich Ergebnis
40
Dalitz DBS Kap2.4
2.4.4 Entwurfsfragen (14)
2.4.5 Vererbung (2)
Mögliches Top-Down Vorgehen:
Beispiel
Fahrzeug
Modellieren der wesentlichen Entities und Relationships
(zwecks besseren Überblicks noch keine Attribute)
is−a
Ergänzen der Schlüsselattribute
Modellieren aller Entity-Eigenschaften als (ggf. mehrwertige)
Attribute
PKW
wo Auswahlliste für Attributwerte gewünscht, Attribute
durch Relationships mit Referenztabellen ersetzen
wenn ein Attribut von mehreren Entities verwendet wird,
ebenfalls durch Referenzen auf neue Entity ersetzen (Warum?)
41
LKW
ein Objekt der Entity PKW gehört auch zur Entity Fahrzeug
ein Objekt der Entity Fahrzeug kann zugleich zu einer oder
(nicht hier, aber im allg.) auch zu mehreren Subklassen gehören
da IS-A Relationship sich auf genau ein Objekt bezieht,
ist es immer eine 1:1 Beziehung => Kardinalitätsangabe unnötig
Subklassen können weitere spezifische Attribute haben
2.4.5 Vererbung (1)
2.4.5 Vererbung (3)
Oft enthält Entity A Objekte mit speziellen
Eigenschaften, die nicht für alle Objekte aus A
relevant sind.
=> definiere special-case Entity B für diese Objekte
Vergleich mit Vererbung in C++
abgeleitete Klasse erbt alle Eigenschaften der Oberklasse,
d.h. Subklasse = Superklasse + spezielle Eigenschaften
Objekt der Subklasse kann als Objekt der Superklasse
behandelt werden
Unterschied
in C++ hat Objekt einen bestimmten Datentyp, d.h. gehört
zu genau einer Klasse
im ER Modell kann ein Objekt zu einer ganzen Hierarchie
von Entites gehören
allerdings: in C++ Typumwandlung möglich mittels dynamic cast
B
is−a
C
43
Übereinstimmung
B heißt Subklasse der Superklasse A
Superklasse wird auch Generalisierung genannt,
Subklasse auch Spezialisierung
zwischen B und A besteht "IS-A" Relationship.
grafische Darstellung dieser Relationship durch Dreieck
im allgemeinen mehrere Subklassen zu einer Superklasse
A
Motorrad
42
44
Dalitz DBS Kap2.4
2.4.5 Vererbung (4)
2.4.5 Vererbung (6)
Spezialisierung einer Superklasse in mehrere
Subklassen hat zwei verschiedene Eigenschaften:
Umwandlung in Relationales Modell
Betrachte Spezialisierung der Superklasse
A(k, a1 , . . . , an ) (k ist der Schlüssel von A)
mit m Subklassen B1 , . . . , Bm
disjunkt oder überlappend
kann ein Element der Superklasse in höchstens einer
Subklasse sein, ist die Spezialisierung disjunkt
total oder partiell
k
Da beide Eigenschaften voneinander unabhängig sind,
ergeben sich vier verschiedene Kombinationen
2.4.5 Vererbung (5)
Es gibt vier verschiedene Umwandlungsoptionen
Erstelle für A die Relation A(k#, a1 , . . . , an ) und für jede Subklasse
Bi eine Relation mit den Attributen {k#} ∪ {Attribute von Bi }.
disjunkt
partiell
PKW
LKW
k#
B1
Gitarre
Lebewesen
überlappend
total
akustisch
elektrisch
47
Option 1:
Fahrzeug
Frau
Bm
2.4.5 Vererbung (7)
Mensch
Mann
...
B1
45
an
A
muss ein Element der Superklasse in mindestens einer
Subklasse sein, ist die Spezialisierung total
disjunkt
total
...
a1
k#
überlappend
partiell
Tier
b1 1
... b1 k1
a1
...
an
...
A
Bm
k# bm 1
... bm km
erzeugt viele Relationen
geeignet für disjunkt und überlappend
geeignet für total und partiell
Mensch
46
48
Dalitz DBS Kap2.4
2.4.5 Vererbung (8)
2.4.5 Vererbung (10)
Option 2:
Option 4:
Erstelle eine Relation A mit den Attributen von A, den Attributen aller Subklassen und m boolschen Attributen t1 , . . . , tm ,
die Flags für Subklassenzugehörigkeiten sind.
Erstelle für jede Subklasse Bi eine Relation mit den
Attributen {k#, a1 , . . . , an } ∪ {Attribute von Bi }.
a1
...
an
b1 1
... b1 k1
B1
k# a1
...
k#
k#
a1
...
an
bm 1 ... bm km
Bm
...
an
t1
...
t m b1 1
... b1 k1
...
A
bm 1
... bm km
Flags für Subklassenzugehörigkeit
erzeugt eine Relation weniger (die Superklasse)
nur geeignet für disjunkte und totale Spezialisierung:
nicht total => Objekte nicht in Subklasse nicht speicherbar
nicht disjunkt => Redundanz durch Mehrfachspeicherung
erzeugt nur eine einzige Relation
geeignet für überlappende Spezialisierung
wie bei Option 3 ggf. zahlreiche Nullwerte
49
51
2.4.5 Vererbung (9)
Option 3:
Erstelle eine Relation A mit den Attributen von A,
den Attributen aller Subklassen und einem Typ-Attribut t,
das die Subklassenzugehörigkeit angibt.
k#
a1
...
an
t
b1 1
... b1 k1
...
bm 1
... bm km
A
Typ−Attribut
erzeugt nur eine einzige Relation
nur geeignet für disjunkte Spezialisierung (Warum?)
erzeugt ggf. zahlreiche Nullwerte (Welche?)
50
Dalitz DBS Kap2.4
3.1 weitere DB-Objekte
3.1.1 Sequence (2)
wichtige fortgeschrittene Datenbankobjekte:
Naiver Ansatz für Primärschlüsselerzeugung
(select max(nr) + 1) ist problematisch:
Sequence
Client 1
generiert eindeutige Werte
nicht in SQL2 spezifiziert, aber von fast allen DBS unterstützt,
wobei Syntax der Verwendung variiert
Tabelle
Client 2
select
max(nr)+1
Schema
select
max(nr)+1
1022
Namespaces zum Trennen von Usern/Anwendungen
in SQL2 gefordert, aber ungenau spezifiziert
=> DBS-spezifische Unterschiede im Detail
1022
bearbeiten
bearbeiten
insert
View
Select-Statement als Tabelle
in SQL2 spezifiziert; wesentlicher Bestandteil aller
relationalen Datenbanksysteme
insert
1
3
Fehler
3.1.1 Sequence (1)
3.1.1 Sequence (3)
Beschreibung
Verwendung Sequence zur Schlüsselgenerierung:
Anlegen der Sequenz
Sequence ist ein Zähler
wesentliche Eigenschaft: einmal vergebener Wert wird nicht
nochmal vergeben (auch nicht in anderen Transaktionen)
=> Sequence-Werte sind über Transaktionsgrenzen
hinweg eindeutig
CREATE SEQUENCE s_person
START 100000 INCREMENT 1;
Verwendung als Default-Wert für Primärschlüssel
CREATE TABLE person (
nr
numeric(6) DEFAULT nextval(’s_person’),
name varchar(30),
/* ... */
PRIMARY KEY (nr)
);
Anwendungsgebiete
automatische Generierung Primärschlüsselwerte
Erzeugung eindeutiger Namen für temporäre Tabellen
(oft besser: Verwendung von create local temporary table)
2
Bemerkung:
PostgreSQL Datentyp serial macht das automatisch
4
Dalitz DBS Kap3.1-3
3.1.2 Schema (1)
3.1.2 Schema (3)
Hierarchieebenen einer DBS-Instanz:
Was ist ein Schema?
ein Schema ist ein Namespace: derselbe Tabellenname kann
parallel in verschiedenen Schemas verwendet werden
jede Tabelle ist genau einem Schema zugeordnet;
angesprochen wird Tabelle mit schemaname.tabellenname
User kann in derselben Sitzung (Datenbank-Verbindung)
Objekte aus mehreren Schemas ansprechen
auf Schemas können Zugriffsrechte erteilt werden
Database
Cluster
Database Cluster
Database
User
Group
Schema
Tabelle
View
Database
Sequence
Schema
Index
Constraint
Group
User
Wozu braucht man Schemas?
Tabelle
View
Constraint
Index
damit mehrere User dieselbe Datenbank ohne Kollisionen
nutzen können
um mehrere Applikationen auf derselben Datenbank zu betreiben
logische Gruppierung von Objekten mit leichterer Verwaltung
...
5
3.1.2 Schema (2)
3.1.2 Schema (4)
Benutzung von Schemas
Datenbank Cluster
Sammlung mehrerer Datenbanken, die von einem
Datenbank-Serverprozess verwaltet werden
User und Gruppen auf Clusterebene, aber einstellbar
wer auf welche Datenbank zugreifen darf
(PostgreSQL: pg_hba.conf, Oracle: grant/revoke connect)
Schemaanlage
create schema <schemaname>;
per Default vorhanden: Schema "public"
Tabellenanlage
create table [schemaname.] tabellenname (...);
ohne schemaname wird Tabelle in erstem (existierenden)
Schema aus Suchpfad angelegt
Datenbank
Sammlung von Tabellen, Views, Constraints, Indizes, ...,
die in Schemas zusammengefasst sind
eine Verbindung zum DB-Server wird immer mit genau
einer Datenbank hergestellt
datenbankübergreifende SQL-Statements sind nach SQL2
nicht möglich, können aber in Oracle mit Datenbank-Links
emuliert werden (auch über Clustergrenzen hinweg!)
7
Schema Suchpfad
unqualifizierte Tabellennamen werden im Schema Suchpfad
gesucht
wie Suchpfad gesetzt wird ist systemspezifisch
typischer Defaultwert: username, public
6
8
Dalitz DBS Kap3.1-3
3.1.2 Schema (5)
3.1.3 View (2)
Typische Konfiguration:
Definition eines Views
Jeder User, der Tabellen anlegt (das ist normalerweise
pro Applikation nur ein einziger User!) hat ein eigenes
Schema mit seiner Userid als Namen
Alle Tabellen der Applikation in diesem Schema anlegen;
Suchpfad Applikationsaccount beginnt mit Usernamen
Endanwender (andere Accounts!) müssen Tabellen qualifizieren
und dürfen DML aber kein DDL ausführen
produkt
pnr# name preis einfuehrung auslauf
π...(σ...( produkt ))
produkt_aktuell
Emulation schemalose Datenbank:
pnr# name preis einfuehrung
View, der nur aktuelle Produkte enthält:
erforderlich zwecks Kompatibilität zu DBS, die keine Schemas
unterstützen (z.B. PostgreSQL vor Version 7.3)
keine expliziten Schemas anlegen und nur "public" Schema
benutzen (=> alle User im selben Namespace)
9
CREATE VIEW produkt_aktuell AS
SELECT pnr,name,preis,einfuehrung
FROM produkt
WHERE auslauf > current_date
OR auslauf IS NULL;
3.1.3 View (1)
3.1.3 View (3)
Was ist ein View?
Angabe der Attributnamen im View
"virtuelle Tabelle", deren Inhalt dynamisch über
relationalen Algebra Ausdruck berechnet wird
11
implizit über Liste selektierter Attribute:
CREATE VIEW produkt_aktuell AS
SELECT pnr, name AS produkt, ...
FROM produkt WHERE ...
im relationalen Modell als abgeleitete Relation
bezeichnet, im Gegensatz zu Basisrelation (Tabelle)
explizite Angabe hinter View-Namen:
verhält sich aus Anwendersicht wie Tabelle:
CREATE VIEW
produkt_aktuell (pnr, produkt, ...)
AS SELECT pnr, name, ...
FROM produkt WHERE ...
Abfrage mit select
explizite Rechtevergabe mit grant/revoke
Aber: Änderung (insert, update, delete) im allg. nicht möglich
10
12
Dalitz DBS Kap3.1-3
3.1.3 View (4)
3.1.3 View (6)
Abfragen über Views
Rechtebeschränkung mit Views
Problem:
führen zu Abfragebaum (s. 2.5.2) mit Views an Blättern
Anwender benutzt Abfrage-Frontend, das keine Rechtebeschränkung ermöglicht (z.B. MS Access, SQL-Prompt)
ersetze Views durch die Abfragebäume, die in ihrer Definition
hinterlegt sind => Abfragebaum hat nur Tabellen an Blättern
Lösung:
richte Views ein, deren Select-Klausel das Rechteprofil
des Anwenders berücksichtigen
richte für Anwender eigenen Datenbank-User ein
gebe diesem User nur das Zugriffsrecht auf die Views
und entziehe ihm den Zugriff auf alle anderen Tabellen
Abfrage
Baum
R
V
R
S
T
13
15
3.1.3 View (5)
3.1.3 View (7)
Wozu sind Views gut?
Beispiel:
System-Catalog, in dem jeder nur seine eigenen
Tabellen sieht
Kapselung komplexer Queries
Anwender braucht Abfrage nicht zu kennen
Abfrage kann geändert werden, ohne Applikation anzupassen
evtl. bessere Performance ("materialized Views")
mögliche Lösung:
Tabelle all_tables (tblid, name, owner,...) enthält Tabellen
aller User
definiere View, in dem jeder nur seine Tabellen sieht:
CREATE VIEW user_tables AS
SELECT * FROM all_tables
WHERE owner = current_user;
Bemerkungen:
Einschränkung von Zugriffsrechten
normalerweise Rechte über Zugriffsfrontend gesteuert
wird kein anwendungsspezifisches Frontend verwendet
(z.B. DB-Frontends aus Office-Paketen), trotzdem Rechtebeschränkung mit Views möglich
Vermeidung Redundanzen
current_user ist die SQL2-Funktion für die aktuelle Benutzerkennung
obwohl alle auf denselben View zugreifen, sieht jeder User andere Daten
abgeleitete Attribute können dynamisch berechnet werden
14
16
Dalitz DBS Kap3.1-3
3.1.3 View (8)
3.1.3 View (10)
Data Dictionary Oracle:
Bessere Lösung bei berechenbarer Abhängigkeit f:
vom System definierte Views, die Benutzerrechte berücksichtigen
Präfix USER_ => eigene Objekte
Präfix ALL_ => alle Objekte auf die User zugreifen darf
Präfix DBA_ => alle Objekte
View
* tables
* tab columns
* sequences
* indexes
* ind columns
* users
* role privs
Purpose
Shows all relational tables
Shows all table and view columns
Lists all sequences in the database
Lists all indexes
Lists all indexed columns
Lists all users
Lists all roles granted to users and other roles
definiere View Vb mithilfe Berechnungsregel von f,
der Primärschlüssel von T und berechnete Werte enthält
T
k1# a1 a2
c1
b1
b1 = f (a1, a2)
k1# a1 a2
c1
c2
Vb
Ta
17
3.1.3 View (11)
Redundanzvermeidung mit Views
Beispiel:
lnr# produkt menge netto
k1# a1 a2
c1
b1
c2
001
002
003
A = { a1, a2 }
B = { b1 }
k1# a1 a2
c1
c2
Ta
a1# a2#
Buch A
Buch B
Software
1
1
1
49.35
116.94
38.90
19
mwst brutto datum
7.0
7.0
16.0
52.80
125.13
43.40
01.12.2002
05.01.2003
01.08.2002
Tabelle lieferung enthält Redundanzen wegen
100 + mwst
netto ∗
= brutto
100
Abspalten berechenbares Attribut mittels View:
b1
Tb
Rückblick Normalisierung:
Redundanzen formal beschrieben durch funktionale
Abhängigkeit A → B, d.h. B = f(A)
Redundanzvermeidung durch projektive Zerlegung
Im allg. ist Funktion f nicht berechenbar (vgl. THI)
=> Darstellung durch Wertetabelle Tb erforderlich
k1# f (a1, a2)
geht nur, wenn f genügend "einfach" ist, um mit relationaler
Algebra berechnet werden zu können (Aber: allgemeiner
berechenbarer Fall mit stored Procedures prinzipiell möglich)
3.1.3 View (9)
T
c2
Tabelle lieferung
18
lnr#
produkt
menge
netto
mwst
datum
View lieferung_brutto
SELECT
lnr,
netto*(1+mwst/100)
FROM lieferung
20
Dalitz DBS Kap3.1-3
3.1.3 View (12)
3.1.3 View (14)
Weitergehende Möglichkeiten:
Projektionsviews
pnr# name preis einfuehrung auslauf
mit Views lassen sich z.T. auch Redundanzen vermeiden,
die nicht durch Normalisierung (projektive Zerlegung)
beseitigt werden können
Beispiel:
Lieferungen mit mehreren Positionen und Gesamtsumme
lieferung (lnr#, datum, summe)
position (lnr#, posnr#, produkt, preis)
summe kann aus preis der zugehörigen Positionen berechnet
werden => Redundanz mit Updateanomalien
Redundanz kann eleminiert werden durch einen View,
der entsprechende Beträge aggregiert
(wie lautet die genaue Definition dieses Views?)
Probleme
produkt
π ...
bei insert wird für ausgeblendete Attribute NULL oder
der bei Tabellenanlage angegebene default eingesetzt
=> ggf. Integritätsverletzung (Not Null Constraint)
bei Ausblendung Primary Key kein insert möglich
weitere Effekte bei Ausblendung Primary Key
21
verschiedene Tupel können als Doubletten im View auftreten
=> keine gezielte Änderung möglich
bei select distinct entsprechen einem View-Tupel im allg. mehrere Basistupel
3.1.3 View (13)
3.1.3 View (15)
Änderungen auf Views
Selektionsviews (1)
Anforderungen:
pnr# name preis einfuehrung auslauf
Korrektheit
Änderung in Basisrelation(en) wirkt sich so aus, als
ob der View direkt geändert würde
Eindeutigkeit und Minimalität
welche Sätze zu ändern sind, darf nicht mehrdeutig sein
diese Sätze werden minimal geändert für gewünschten Effekt
Integritätserhaltung
Änderung darf zu keinen Integritätsverletzungen führen
keine Auswirkung auf "unsichtbare" Tupel der Basisrelationen
billigprodukt :=
23
produkt
σ preis < 5.0 ( produkt )
Probleme
Änderung kann ausgeblendeten Teil betreffen
DELETE FROM produkt WHERE preis > ’2.0’;
Minimalitätsprinzip: keine Auswirkung auf unsichtbare Tupel
Anforderungen im allgemeinen nicht alle erfüllbar
Untersuche Bedingungen für Erfüllbarkeit
22
Verschieben von sichtbar zu unsichtbar
UPDATE produkt SET preis = ’8.5’ ...
kann in SQL2 mit with check option unterdrückt werden
24
Dalitz DBS Kap3.1-3
3.1.3 View (16)
3.1.3 View (18)
Selektionsviews (2)
Ansätze für änderbare Views:
automatisch änderbare Views
Betrachte Viewdefinition über Subquery:
definiere (hinreichende) Bedingungen, wann View änderbar ist
solche Views sind änderbar gemäß festdefinierten Regeln
bei allen anderen Views sind keine Änderungen zulässig
diese Lösung wird von SQL2 gewählt
Bedingungen sind aber sehr restriktiv (=> geringer Nutzen)
CREATE VIEW teuerstes_produkt AS
SELECT * FROM produkt WHERE preis = (
SELECT max(preis) FROM produkt
);
Anforderung der Korrektheit für Änderungen nicht erfüllbar
Wie wäre nämlich z.B. delete from teuerstes_produkt umzusetzen?
Was ist mit updates und inserts?
selbstdefinierbare Regeln für Änderungen
ermögliche Definition von Regeln (Rules), was bei
insert, update, delete gemacht werden soll
nur Views mit solchen Rules sind änderbar
Lösung wird von PostgreSQL gewählt
flexibel, aber kein Automatismus für triviale Fälle
Problem: where-Klausel wird durch Änderung mitverändert
Views, die Subqueries mit Selbstbezug enthalten,
sind daher in SQL2 nicht änderbar
25
3.1.3 View (17)
3.1.3 View (19)
Verbundviews (Joins)
hersteller
hnr# hersteller stadt
Probleme
27
Änderbare Views in SQL2
produkt
SQL2 unterscheidet nicht zwischen insert, update und delete,
sondern spricht allgemein von "updatable Views"
pnr# produkt preis hnr
hersteller_produkt := hersteller
ein "updatable View" ist ein select [all] (kein select distinct)
auf genau eine Basistabelle, mit folgenden Zusatzbedingungen:
produkt
der View enthält keine berechneten Attribute
Gruppierung und Aggregation ist unzulässig
Subselect auf dieselbe Basistablle ist unzulässig
alle nicht im View enthaltenen Attribute dürfen in der Basistabelle NULL sein
oder haben einen Default-Wert definiert (M.a.W. ein insert schlägt nicht fehl)
Änderungen nicht eindeutig einem Basistupel zugeordnet
z.B. Löschung eines View-Tupels auf drei Arten möglich:
Löschung des Produkts aus produkt
Löschung des Herstellers aus hersteller
Löschung Produkt und Hersteller
create view bietet Parameter with check option, mit dem eine
"Tupelmigration" in unsichtbaren Bereich der Basistabelle
verhindert werden kann
In letzten zwei Fällen ist Ergebnis nicht korrekt, da immer
weitere Tupel aus hersteller_produkt mitgelöscht werden
in SQL2 Änderungen auf Verbundsichten verboten
26
28
Dalitz DBS Kap3.1-3
3.1.4 Rule (1)
3.1.4 Rule (3)
Nachteile SQL2 Lösung:
Anwendung auf Löschproblem Verbundview:
hersteller
Bedingungen für Eindeutigkeit decken nur triviale Fälle ab
mehrdeutige Fälle können prinzipiell nicht erfasst werden
durch "automatische" Umsetzung Statements auf Basistabellen
hnr# hersteller stadt
pnr# produkt preis hnr
hersteller_produkt := hersteller
allgemeinere Lösung mit Rules:
produkt
es soll nur das Produkt, nicht der Hersteller gelöscht werden:
CREATE RULE hersteller_produkt_del AS ON DELETE
TO hersteller_produkt DO INSTEAD
DELETE FROM produkt WHERE pnr = old.pnr;
Rule redefiniert, was im Falle eines insert, update, delete, select
gemacht werden soll
nicht nur auf Views beschränkt, auch auf Tabellen anwendbar
verwandt mit dem Trigger
kein Bestandteil eines SQL-Standards, sondern PostgreSQLspezifische Erweiterung
29
Bemerkung:
Die Pseudorelationen old und new enthalten das betroffene
Tupel vor bzw. nach Durchführung der auslösenden Operation
Bei delete braucht man also nur old, bei insert nur new
und bei update beides
3.1.4 Rule (2)
3.1.4 Rule (4)
Anlegen einer Rule:
Wozu sind Select-Rules gut?
CREATE RULE rule_name AS ON event
TO object [WHERE rule_qualification]
DO [INSTEAD] [action | (actions) | NOTHING];
31
Protokollierung von Zugriffen
problematisch bzgl. Performance
besser: Logfile-Auswertung (ggf. Auditing Tool verwenden)
Beispiele:
verhindere Update’s an Tabelle hersteller:
CREATE RULE hersteller_no_upd AS ON UPDATE
TO hersteller DO INSTEAD NOTHING;
(kein gutes Beispiel: wie macht man das besser?)
Setze statt physischer Löschung Löschkennzeichen:
CREATE RULE hersteller_del AS ON DELETE
TO hersteller DO INSTEAD
UPDATE hersteller SET geloescht = TRUE
WHERE hnr = old.hnr;
produkt
PostgreSQL-interne Implementierung von Views:
CREATE VIEW myview AS SELECT * FROM mytab;
wird umgesetzt als
CREATE TABLE myview (attribute list of mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview
DO INSTEAD SELECT * FROM mytab;
30
32
Dalitz DBS Kap3.1-3
3.1.4 Rule (5)
3.2 Serverprogrammierung (2)
Idee serverseitige Programmierung:
Rules sind spezieller experimenteller Ansatz, der in
den meisten DBS-Lehrbüchern nicht behandelt wird
Verlagere Datenmanipulation mit fester Ablauflogik
vom Client auf den Server
Programme werden als stored Procedures im DBS hinterlegt
und vom Clientprogramm per SQL-Befehl gestartet
Originale Forschungsliteratur zum Thema "Rules":
Anwendg.
Stonebraker, Jhingran, Goh, Potamianos:
On Rules, Procedures, Caching and Views in Database Systems.
URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/papers/ERL-M90-36.pdf
SELECT
proc()
FROM
...
DB
DBMS
proc()
stored Procedure
Ong, J. Goh: A Unified Framework for Version Modeling
Using Production Rules in a Database System.
ERL Technical Memorandum M90/33,
University of California, April, 1990
DBS
Vorteile
33
stored Procedures stehen in jedem Client zur Verfügung
(sogar im SQL-Prompt)
bei Änderung Ablauflogik müssen Clients nicht angepasst
werden, sondern nur zentrale Prozedur
3.2 Serverprogrammierung (1)
3.2 Serverprogrammierung (3)
Datenbankzugriff aus Clientprogramm:
Komponenten serverseitiger Programmierung
35
stored Procedures
DB−Server
die im Server hinterlegten Programme
SQL nicht Turing-vollständig => Procedures können i. allg.
nicht in reinem SQL sein (Host Language oder PL/SQL)
Programm
SQL
Verar−
beitung
SQL
prozedurale SQL-Erweiterungen
Verar−
beitung
ermöglichen direkte Erzeugung von Prozeduren mit SQL
Quasistandard: PL/SQL von Oracle
Programm holt Daten per SQL
abhängig von Verarbeitungslogik werden weitere
SQL-Statements abgesetzt usw.
Verarbeitungslogik kann fest programmiert sein
(z.B. Praktikum 3+4) oder interaktiv vom Anwender
bestimmt werden (Extremfall: SQL-Prompt)
Trigger
Auslösen von stored Procedures beim Eintreten bestimmter
Ereignisse, z.B. Update einer bestimmten Tabelle
"aktive" Datenbankobjekte, die nicht direkt vom Anwender
angesprochen werden
34
36
Dalitz DBS Kap3.1-3
3.2 Serverprogrammierung (4)
3.2.1 Stored Procedures (2)
"Standards" der serverseitigen Programmierung
Beispiel:
PL/SQL
DROP FUNCTION bruttofunc(NUMERIC);
-- berechne Bruttobetrag incl. Mwst
CREATE FUNCTION bruttofunc(numeric)
RETURNS numeric AS ’
SELECT $1 * CAST(1.16 AS numeric);
’ LANGUAGE ’SQL’;
prozedurale SQL-Erweiterung von Oracle
vollwertige moderne prozedurale Programmiersprache
(Funktionen mit Defaultargs und Überladen, Exceptions,...)
Vorbild für PL/pgSQL von Postgres
PSM
Bemerkungen:
Persistent Stored Modules (PSM) 1996 in ANSI SQL-Standard
aufgenommen; Bestandteil von SQL3 (1999)
spezifiziert drei Aspekte
Überladung möglich => Argumente beim drop mit angeben
Argumente referenzierbar mit $1, $2 etc.
Rückgabewert = Ergebnis letztes Select-Statement
Funktion ist mit reinem SQL implementiert (kein PL/SQL)
Implementierungssprache im Parameter language angegeben
auch andere Sprachen möglich (plpgsql, C, plperl, pltcl, plpython)
Definition und Aufruf von Prozeduren und Funktionen
Zusammenfassen von Funktionen zu Modulen
prozedurale SQL-Erweiterung
wegen zu PL/SQL inkompatibler Syntax (noch?) kaum umgesetzt
37
39
3.2.1 Stored Procedures (1)
3.2.1 Stored Procedures (3)
Definition und Aufruf
Funktionen können nicht nur zum Berechnen,
sondern auch für Operationen verwendet werden:
SQL3 unterscheidet zwischen Prozedur und Funktion
-- Abbuchung in Tabelle Konto durchführen
CREATE FUNCTION abbuchung(varchar, numeric)
RETURNS numeric AS ’
UPDATE konto
SET stand = stand - $2
WHERE nr = $1;
SELECT stand FROM konto WHERE nr = $1;
’ LANGUAGE ’SQL’;
Anlage mit create procedure bzw. create function
Aufruf Prozedur mit SQL-Befehl call <procname>
Aufruf Funktion im Rahmen von select-Statement. Beispiel:
select bruttofunc(preis) from produkt;
PostgreSQL macht diese Unterscheidung nicht
expliziter Aufruf nur über select möglich
=> explizit aufrufbare Funktion muss Rückgabewert haben
Funktionen ohne Rückgabewert haben Rückgabetyp trigger
=> können nur implizit vom DBS aufgerufen werden
(z.B. über Trigger)
Bemerkung
38
letztes select nötig, da Funktion Wert zurückgeben muss
könnte aber auch durch triviales select ersetzt werden,
z.B. select ’1’;
40
Dalitz DBS Kap3.1-3
3.2.1 Stored Procedures (4)
3.2.2 prozedurales SQL (2)
PostgreSQL unterstützt auch C-Funktionen
Sprachen-Framework in PostgreSQL
Funktion muss in Shared-Library (*.so) bereitgestellt werden
Shared-Library wird auf DB-Server abgelegt
Parameterübergabe über spezielle libpq-Makros
SQL Funktionsdefinition:
CREATE FUNCTION bruttofunc(numeric)
RETURNS numeric AS ’bruttofunc.so’
LANGUAGE ’C’;
Suchpfad für Shared-Library ist konfigurierbar
stored Procedures
name
userfunc
source
...
sprache
pl
Sprachen
name
pl
pl_handler plmodul.so C
handler
pl_handler
Nachteile:
Zugriff auf Betriebssystem erforderlich
DBS
im allgemeinen nur durch DBA möglich
plattformabhängig
DBS kann nicht optimieren
plmodul.so
41
3.2.2 prozedurales SQL (1)
3.2.2 prozedurales SQL (3)
SQL nur begrenzte Möglichkeiten (relationale Algebra)
=> für "inline" Definition von Funktionen ist
prozedurale SQL-Erweiterung nötig
Installation Sprache in PostgreSQL (durch DBA)
43
a) Compilieren und Bereitstellen Objectfile für den
Language Handler (ggf. schon vorinstalliert, z.B. bei plpgsql)
b) Deklaration Handler Funktion
Ansätze
feste Implementierung von PL/SQL im DBS
CREATE FUNCTION handler_function_name() RETURNS LANGUAGE_HANDLER
AS ’path-to-shared-object’ LANGUAGE C;
b) Deklaration der Sprache
von Oracle gewählte Lösung
PL/SQL immer verfügbar (auch außerhalb von Funktionen!)
CREATE [TRUSTED] LANGUAGE language-name
HANDLER handler_function_name;
Hinweise:
Framework zum Bereitstellen von Sprachen
für mitgeliefert Sprachen (plpgsql, plperl, ...) kann Script
createlang verwendet werden, z.B. createlang plpgsql template1
bei Installation in Template werden Sprachen an (danach!)
angelegte Datenbanken vererbt
von PostgreSQL gewählte Lösung
Sprachen müssen separat ins DBS eingebunden werden
Sprache in Funktionsdefinition angeben
beliebig erweiterbarer Ansatz
42
44
Dalitz DBS Kap3.1-3
3.2.2 prozedurales SQL (4)
3.2.2 prozedurales SQL (6)
Sicherheitsaspekt
Beispielfunktion in SQL:
-- berechne Bruttobetrag incl. Mwst
CREATE FUNCTION bruttofunc(numeric)
RETURNS numeric AS ’
SELECT $1 * CAST(1.16 AS numeric);
’ LANGUAGE ’SQL’;
Sprachen können Aufruf von System-Kommandos
ermöglichen => ggf. Sicherheitsproblem
Beispiel: Prozedur xp_cmd_shell in MS SQL-Server
=> jeder DB-User darf beliebige Systemkommandos mit
der Userid ausführen, unter der das DBS läuft
Dieselbe Funktion in PL/pgSQL:
Lösungsansätze:
lasse Systemcalls nicht zu in Sprache ("trusted Language")
=> Sprache darf von jedem benutzt werden
lasse "untrusted Languages" nur für spezielle User zu
in PostgreSQL Parameter trusted bei create language
Benutzung "untrusted" Language erfordert DBA-Rechte
45
CREATE FUNCTION bruttofunc(numeric)
RETURNS numeric AS ’
DECLARE
res numeric;
BEGIN
res := $1 * CAST(1.16 AS numeric);
RETURN res;
END;
’ LANGUAGE ’plpgsql’;
3.2.2 prozedurales SQL (5)
3.2.2 prozedurales SQL (7)
PL/SQL und PL/pgSQL
Mögliche Deklarationen:
-- normaler SQL-Datentyp
name VARCHAR(30);
allgemeine Struktur ist blockorientiert:
-- Vorbelegung jedesmal, wenn Block aufgerufen
menge INT DEFAULT 0; /*oder: menge INT := 0;*/
[ DECLARE
declarations ]
BEGIN
statements
END;
-- Datentyp von Tabellenattribut übernehmen
preis produkt.preis%TYPE;
-- Aliasname für Funktionsparameter
arg1 ALIAS FOR $1;
Statements werden mit Semikolon (;) angeschlossen
jedes Statement kann selber wieder Block sein
Deklarationen gelten nur im jeweiligen Block
begin nicht zu verwechseln mit Transaktionsstart:
PostgreSQL erlaubt keine verschachtelten Transaktionen
und damit auch keine Transaktionen innerhalb von Funktionen
Oracle kennt kein begin work (nur impliziter Transaktionsbeginn)
47
-- zusammengesetzter Datentyp (Tabellentupel)
prod produkt%ROWTYPE;
46
-- Platzhalter für SELECT-Ergebnis
-- (d.h. ROWTYPE mit beliebiger Struktur)
rec RECORD;
48
Dalitz DBS Kap3.1-3
3.2.2 prozedurales SQL (8)
3.2.2 prozedurales SQL (10)
Kontrollstrukturen:
Multiple-Row Select
einfache Variante (nur Postgres): Select in For-Loop
Verzweigungen
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM produkt LOOP
/* Verarbeitung */
END LOOP;
END;
if - then - [else -] end if;
Loops
sowohl while- als auch for-Loops:
WHILE bedingung LOOP
anweisungen
END LOOP;
FOR var IN start .. ende LOOP
anweisungen
END LOOP;
komplizierte Variante (Postgres und Oracle): Cursor
Abbruch aus Schleife mit exit
auch Loops über Select-Ergebnisse möglich:
FOR rec IN SELECT * FROM produkt LOOP
IF (rec.preis < 5) THEN
zahler := zaehler + 1;
END IF;
END LOOP;
49
3.2.2 prozedurales SQL (9)
DECLARE
cur CURSOR IS SELECT * FROM produkt;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO variablelist;
EXIT WHEN cur%NOTFOUND; /*Postgres: EXIT WHEN NOT FOUND;*/
/* Verarbeitung */
END LOOP;
CLOSE cur;
END;
51
3.2.2 prozedurales SQL (11)
Überprüfung ob Select Ergebnis lieferte:
SQL-Statements
Postgres
update, insert, delete direkt formulierbar
select ist komplizierter:
Abfragen globale boolesche Variable found
Oracle
Was ist, wenn mehr als ein Tupel zurückliefert wird?
Wie wird erkannt, ob überhaupt Ergebnis gefunden?
wenn select into nichts liefert, wird Exception vom Typ
no_data_found geworfen
bei fetch into Cursorattribut %NOTFOUND abfragen
Single-Row Select
Dynamische Statements
kann mit select into erfolgen:
SELECT max(preis) INTO maxpreis FROM produkt;
komplettes Tupel kann in record oder rowtype Variable
eingelesen werden; Attributwerte ansprechbar mit rec.att
Statements, die erst zur Laufzeit zusammengesetzt werden,
können mit execute ausgeführt werden
execute auch dann nötig, wenn Statement Tabellen referenziert,
deren OID zur Compilezeit noch nicht bekannt ist
(trifft z.B. auf DDL-Statements zu)
50
52
Dalitz DBS Kap3.1-3
3.2.2 prozedurales SQL (12)
3.2.2 prozedurales SQL (14)
Postgres ermöglicht auch Aggregatfunktionen
Fehlerbehandlung
Attributwerte
erfolgt grundsätzlich über Exceptions
Postgres
stark eingeschränktes Fehlerhandling: Exceptions können
zwar mit raise exception geworfen, aber nicht gefangen werden
schlägt SQL-Statement fehl, wird aktuelle Transaktion mit
rollback abgebrochen
x1
y1
xi
x i+1
yi
y i+1
xn
yn
y
n+1
Aggregatwert
Oracle
n+1
)
eigentliche Aggregatdefinition verweist dann auf die zwei
Hilfsfunktionen und legt Startwert für Zustandsvariable fest
3.2.2 prozedurales SQL (13)
3.2.3 Trigger (1)
Komplettes Beispiel:
Was ist ein Trigger?
55
Trigger verknüpfen ein Ereignis in einer Tabelle
mit bestimmten Aktionen
Lieferung
mwst
gueltigab#
lnr# produkt netto
14.00
16.00
01.01.1990
01.04.1997
1
2
50.12
82.50
datum
auslösendes Ereignis kann insert, update oder delete sein;
ist immer an genau eine Tabelle gebunden
ausgelöste Aktion kann beliebige stored Procedure sein
(kann also auch andere Tabellen betreffen)
auch als Event-Condition-Action Rules (ECA) bezeichnet
01.12.1992
01.12.1999
Funktion zur Mehrwertsteuerberechnung
Trigger können von Anwendern nicht direkt
angestossen werden (nur indirekt durch Ereignis)
zwei Argumente: Nettobetrag, Datum
sucht zu Datum passenden Mwst-Satz und berechnet Bruttobetrag
wenn zu Datum kein passender Mwst-Satz hinterlegt
=> Fehler (Alternative: Rückgabe von NULL)
Siehe plsqldemo.tar.gz auf Homepage
finalfunc ( y
Übergangsfunktion sfunc verarbeitet einzelne Attributwerte
Kommunikation über Zustandsvariable (state variable)
(optionale) Abschlussfunktion berechnet aus Zustandsvariable den Aggregatwert
53
Pritt
Uhu
sfunc ( x i , y i )
erfordert Definition zweier Hilfsfunktionen
PL/SQL Blöcke haben zusätzlichen exception Abschnitt,
in dem auf Exceptions je nach Typ verschieden reagiert
werden kann
Mwst
Zustandsvariable
von meisten DBS unterstützt und in SQL3 enthalten,
aber zahlreiche Unterschiede im Detail
54
56
Dalitz DBS Kap3.1-3
3.2.3 Trigger (2)
3.2.3 Trigger (4)
Wozu sind Trigger gut?
Anlegen eines Triggers
Automatisierung Ablauflogik
CREATE TRIGGER trigger [ BEFORE | AFTER ] event
ON relation FOR EACH [ ROW | STATEMENT ]
EXECUTE PROCEDURE procedure();
Abläufe können im DB-Server hinterlegt werden, ohne dass
Clientprogramm spezielle Funktionen aufrufen muss
Abläufe können unabhängig vom Client erzwungen werden
auslösendes Ereignis (event) kann insert, update oder delete sein;
auch Kombinationen mit or möglich
Triggerfunktion kann vor (before) oder nach (after) dem
auslösenden Ereignis aufgerufen werden
Ausführen für jede vom event betroffene Zeile (for each row)
oder nur einmal pro gesamtes Statement (for each statement)
Unterschiede im Detail:
komplexe Integrity Constraints
Variante 1: erzeuge Fehler (Exception) bei Integritätsverletzung
Variante 2: korrigiere fehlerhafte Eingabe automatisch
Berechnung redundanter Werte
aus Performancegründen oft keine Redundanzfreiheit
Update-Anomalien können durch Trigger aufgelöst werden
execute procedure ist Postgres-spezifisch
Oracle erlaubt inline Definition mittels PL/SQL-Block
SQL3 verlangt SQL-Code statt Funktionsangabe und erlaubt zusätzliche
when Klausel zur Einschränkung des auslösenden Ereignisses
Wichtig: nur dosiert und mit Bedacht einsetzen!
57
59
3.2.3 Trigger (3)
3.2.3 Trigger (5)
Problematische Eigenschaften von Triggern
Die Triggerfunktion
Strukturierung
CREATE FUNCTION triggerfunc() RETURNS TRIGGER
AS ’ ... ’ LANGUAGE ’plpgsql’;
es fehlen z.Zt. Abstraktionsmechanismen um Trigger zu
logischen Einheiten zusammenzufassen
Terminierung
Funktion als Triggerfunktion markiert (Rückgabewert trigger)
(Achtung: Postgres < 7.3 braucht Rückgabewert opaque)
innerhalb der Funktion enthalten spezielle Variablen
Informationen über auslösendes Ereignis und Zustand.
Variablen sind DBS-spezifisch. Bei Postgres:
Operationen in Triggerfunktionen können andere Trigger
(evtl. auch sich selber!) auslösen
Terminiert diese Triggerkette?
Frage ist für beliebige Kombinationen unentscheidbar (vgl. THI)
Konfluenz
dasselbe Ereignis kann mehrere Trigger parallel auslösen
Ist das Ergebnis unabhängig von der Abarbeitungsreihenfolge?
auch diese Frage ist im allg. unentscheidbar
58
Variable
OLD
NEW
TG_NAME
TG_RELNAME
TG_OP
Typ
RECORD
RECORD
NAME
NAME
TEXT
Bedeutung
Datensatz vor Ausführung Ereignis
Datensatz nach Ausführung Ereignis
Name auslösender Trigger
Name auslösende Tabelle
Art auslösendes Ereignis (insert,...)
60
Dalitz DBS Kap3.1-3
3.2.3 Trigger (6)
3.2.3 Trigger (8)
Beispiel: Protokollierung letztes Update
Komplettes Beispiel:
Tabelle habe Attribut lastchange für Zeitpunkt letztes Update
Jahresabschluss
Definition der Triggerfunktion:
CREATE FUNCTION changelog() RETURNS TRIGGER AS ’
begin
new.lastchange := current_timestamp;
end;
return new;
’ LANGUAGE ’plpgsql’;
Definition des eigentlichen Triggers:
CREATE TRIGGER tg_mytable
BEFORE UPDATE ON mytable
FOR EACH ROW EXECUTE PROCEDURE changelog();
Kosten
lnr# artnr
datum
15.01.2001
K001
U003
1
2
kst
netto
datum
7020
7030
50.12
82.50
01.12.2000
29.02.2002
Verhinderung unzulässiges Kostendatum
nach erfolgtem Jahresabschluss dürfen keine Kosten davor
mehr angelegt oder geändert werden
Tabelle Jahresabschluss enthält letztes Abschlussdatum
Trigger auf Kosten überprüft Integritätsbedingung
61
Siehe plsqldemo.tar.gz auf Homepage
63
3.2.3 Trigger (7)
3.2.3 Trigger (9)
Trigger für Integrity Constraints
Auch Foreign Key Constraints können über
Trigger realisiert werden
begrenzter Leistungsumfang eingebauter Constraints
not null, check bezieht sich nur auf aktuelles Tupel
(aber: flexibler, wenn check Subselects zulässt)
unique, primary key, foreign key prüfen Vorkommen in Relation
Wieviele und was für Trigger sind z.B. für die folgende
Foreign Key Constraint erforderlich?
Hersteller
hnr#
name
SQL3 assertion hat sich nicht durchgesetzt
sehr schwierig zu implementieren
Trigger sind flexibler, weil zusätzliche Operationen möglich
Trigger können beliebige Bedingungen prüfen
durch PL/SQL nicht auf relationale Algebra beschränkt
62
on update
cascade
Produkt
pnr#
name
hnr
preis
Tatsächlich realisiert Postgres Foreign Key Constraints
intern mit Triggern
64
Dalitz DBS Kap3.1-3
3.2.3 Trigger (10)
3.3 DB-Tuning (2)
Trigger zur Berechnung redundanter Werte
Ansätze zum Datenbanktuning
Wir sahen: Redundanz durch berechnete Attribute kann
durch Views vermieden werden
gelegentlich aber aus Performancegründen explizites Speichern
redundanter Werte erforderlich
Update-Anomalien können durch Trigger vermieden werden
Konfiguration Nutzung OS-Resourcen *)
Indizes
Abfragemodifikation
Denormalisierung *)
Transaktionsablauf
Vermeidung Client-Server Pingpong
Beispiel:
Lagerverwaltung mit Artikeln und Lagerbewegungen
Lagerbestand bei jedem Zugriff dynamisch zu berechnen
wäre zu aufwändig => hinterlege beim Artikel aktuellen Bestand
beim Insert einer Bewegung kann Bestand durch Trigger
automatisch aktualisiert werden
*) untersuchen wir im Folgenden nicht näher
65
67
3.3 DB-Tuning (1)
3.3 DB-Tuning (3)
Ziele Datenmodellierung:
Was ist ein Index?
einfache und klare Semantik
Redundanzfreiheit
Datenstruktur, die direkten Zugriff auf Tupel anhand eines
Attributwerts ermöglicht (im Ggs. zu sequentiellem Scan)
Index wird vom DBS getrennt von Tabelle gespeichert
Ziele Datenbanktuning:
Was bewirkt ein Index?
Beschleunigung von Abfragen
hoher Importdurchsatz
Vermeidung Verklemmungen (Deadlocks)
deutliche Beschleunigung Suche über Attributwert
(aber nicht immer: oft auch sequentieller Scan schneller)
Beschleunigung Sortierung und Join über Attributwert
Verlangsamung Änderungen an Tabelle (Warum?)
Beide verfolgen also orthogonale Ziele, beeinflussen
sich aber teilweise => ggf. im Einzelfall abwägen
Sorgfältige Indexwahl wichtigstes Tuningmittel
Fehlender Index häufigstes Performanceproblem
66
68
Dalitz DBS Kap3.1-3
3.3 DB-Tuning (4)
3.3 DB-Tuning (6)
Indextypen
Gründe für Nichtnutzung von Indizes:
normaler Attributindex
falsche Statistikinfos zu Tabellen
Anlage mit create [unique] index indname on tblname (att1, ...)
Achtung: bei Multicolumn Index beschleunigter Zugriff
über alle Attribute gemeinsam oder erstes Attribut
bei Postgres muss Statistik explizit aktualisiert werden
mit analyze (am besten cron Job einrichten; siehe auch
autovacuum daemon im contrib-Verzeichnis ab Postgres 7.4)
Statistik enthält Zufallsauswahl => ggf. irreführend
Bitmap Index
Verwendung von Funktionen, Mehrfachindex,
pattern matching Operator (like, similar)
erheblich schneller auf Attributen mit wenigen Werten
partieller Index
Ausschließen bestimmter Tupel aus Index
Type Mismatch zwischen Feldern
funktionaler Index
Query Optimizer schwach bei bestimmten Queries
Index auf berechnete Werte, z.B. create index ... on lower(att)
insbesondere nötig bei Caseinsensitiver Suche
69
bei Joins ggf. explizites join on ausprobieren
verschiedene Varianten desselben Statements testen
3.3 DB-Tuning (5)
3.3 DB-Tuning (7)
Nutzung von Indizes durch DBS
Beispiel für äquivalente Abfragen (vgl. Übungen)
71
DBS benutzt existierende Indizes nicht unbedingt
SELECT * FROM person WHERE pnr IN
(SELECT regie FROM film);
oft ist Zugriff über Index langsamer als squentieller Scan;
Query Optimizer trifft Entscheidung aufgrund statistischer
Informationen über Tabelleninhalt; ggf. kann Nutzung
von Indizes auch immer erzwungen werden (Serverparameter)
SELECT * FROM person WHERE EXISTS
(SELECT ’ ’ FROM film WHERE regie = person.pnr);
meisten DBS bieten SQL Kommando zur Abfrage Query Plan
Beispiel: Ausgabe des Postgres SQL-Befehls explain
SELECT DISTINCT p.* FROM person p, film f
WHERE f.regie = p.pnr;
dbname=# explain select b,c from testi where a=99999;
NOTICE: QUERY PLAN:
Seq Scan on test (cost=0.00..1987.20 rows=1 width=14)
SELECT DISTINCT p.* FROM person p INNER JOIN film f
ON f.regie = p.pnr;
dbname=# explain select b,c from test where a=cast(99999 as int8);
NOTICE: QUERY PLAN:
Index Scan using test_a_key on test (cost=0.00..3.01 rows=1 width=14)
70
72
Dalitz DBS Kap3.1-3
3.3 DB-Tuning (8)
Transaktionsablauf
Interaktive Bearbeitung
Transaktionen sollten kurz sein (Deadlockgefahr)
kein Table-Lock, sondern select for update (Row-Level Lock)
Massenimport
auf keinen Fall nach jedem Statement commit,
besser erst nach Blöcken vieler Datensätze
evtl. Indizes vorher droppen und hinterher neuanlegen
Trigger und Constraints ggf. droppen/disablen
evtl. unterstützt DBS Import am Transaction Manager vorbei
(z.B. "raw Import" bei Oracles sqlldr oder Postgres copy)
wenn möglich, fsync deaktivieren (Verzicht auf "Durability"
in "ACID"); sinnvoll z.B. beim Backup-Einspielen
73
3.3 DB-Tuning (9)
Vermeidung Client-Server Pingpong
Probleme Client-Server Anwendung
Netztransfer großer Datenmengen (bei DB’s meist kein Problem)
Ketten von Frage-Antwort Logik
Lösungsmöglichkeiten
eine komplexe SQL-Abfrage ist sehr viel schneller als viele kleine
=> fortgeschrittene SQL-Features nutzen (Subquery, Union, ...)
Abfragen mittels Views komplett im DB-Server hinterlegen
für Abfragen, die mit SQL nicht machbar sind (z.B. transitive
Hülle berechnen), stored Procedures schreiben
74
Dalitz DBS Kap3.1-3