1 Grundlagen Datenbanksysteme - HS Niederrheindalitz/data/... · 1.4 SQL Einführung (3) SQL est omnis divisa in partes tres: DDL - Data Definition Language Definieren und Ändern

Dalitz DBS Kap1.1-3

1

Datenbanksysteme

Prof. Dr. Christoph DalitzSommersemester 2012

Vorlesung an derHochschule Niederrhein

2

Themen (1)

Anwendungs−

programm

Praxis−

Anforderungen

Speich−Verwal−

erungtung

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?

3

Themen (2)

1 Grundlagen

Definition und Bedeutung von DB’s Klassifikation von DB-Systemen Relationale Datenbanken Einführung in SQL Clientseitige Programmierung

2 Datenmodelle

Drei Schichten Modell und Entwurfsmethodik Relationales Modell Abhängigkeiten und Normalformen relationale Algebra Semantische Modelle (z.B. Entity-Relationship)

4

Themen (3)

3 Fortgeschrittene Themen

Weitere Datenobjekte (Sequence, View...) Serverseitige Programmierung (PL/SQL, Trigger) Datenbank Tuning Datensicherheit und Transaktionen

Nicht behandelte Themen:

Interne Implementierung von Datenbanksystemen Speicherstrukturen und Indizes Recovery bei Systemabsturz Abfrageverarbeitung

verteilte Datenbanken objektorientierte Datenbanken

Dalitz DBS Kap1.1-3

5

Literatur

Vorlesung:

Elmasri, Navathe: Grundlagen von Datenbanksystemen.

Pearson Studium 2005 (3. Auflage, Grundstudiumsausgabe)

Garcia-Molina, Ullman, Widom: Database Systems -

The Complete Book. Prentice Hall 2002

Praktikum:

PostgreSQL Online-Dokumentation: http://www.postgresql.org/docs/

Matthew, Stones: Beginning Databases with PostgreSQL.

Apress 2005

6

1.1 Definition, Bedeutung (1)

Aufgaben eines DBS:

Datendefinition Data Definition Laguage (DDL) System Catalog (Data Dictionary)

Datenmanipulation Data Manipulation Language (DML) Abfrageoptimierung

Datenschutz Benutzerrechte und Zugriffsbeschränkung

Datensicherheit Konsistenzprüfung, Integrity Constraints Transaktionen Concurrency Control Recovery bei Systemfehlern

7

1.1 Definition, Bedeutung (2)

Prozessor

DDL

Struktur−definition

Synchronisation

MehrbenutzerManager

Datenbank

Logbuch IndizesSystemKatalog

Daten−

basis

Prozessor

DML

Optimizer

Abfrage,Manipulation

Speicher

RDBMS

DBS−Architektur

D

BS

8

1.2 Klassifikation (1)

Einteilung von "Datenbank-Systemen" nach verschiedenen Kriterien

Leistungsumfang

Welche Aufgaben eines DBS sind implementiert?

=> Desktop, Client-Server, verteilte Datenbank

logische Datensicht ("Datenmodell")

Wie präsentieren sich Daten aus Sicht des Anwenders?

=> hierarchisch, relational, objektorientiert

Dalitz DBS Kap1.1-3

9


Pgm2

DB

Client 2

Client 1

Server

Pgm1

Pgm1

DBMS

BSDSockets

Domain SocketsBSDSockets

Client-Server DBS

DBS eigenständiger Prozess Zugriff über IPC-Mechanismen Multi-User Systeme

10


DB

DB−Lib

Pgm

DB−Lib

Pgm

DB−Lib

Pgm

DB

Fileserver

Client 1 Client 2

Desktop

Rechner

Desktop Datenbank

Direktzugriff auf Daten über dazugelinkte Library-Routinenen Mehrbenutzersynchronisation nur über primitives Filelocking Single-User Systeme

11


KD# Kunde

BK# Bankverb.

KS# Klausel

VS# Versicherg.

Hierarchisches Modell

1969 von IBM mit IMS eingeführt Datensätze in geordnetem Baum gespeichert Zugriff durch Direktzugriff über Schlüssel und Manövrieren in Baumhierarchie

=> Abhängigkeit von physischer Datenstruktur

12


KD# Kunde

BK# Bankverb.KD#

VS# Versicherg.KD#

KS# KlauselVS#

KD# Kunde

VS# Versicherg.KD#

VS# Versicherg.KD# Kunde

Relationales Modell

1970 von Codd vorgeschlagen Daten in Tabellen gespeichert Zugriff durch Erzeugung von neuen Tabellen aus alten

=> physische Datenunabhängigkeit

Dalitz DBS Kap1.1-3

13


Objektorientierte Datenbanken

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

=> Abhängigkeit von Klassenimplementierung

Objekt-Relationale Datenbanken

relationales Modell objektorientierte Erweiterungen: benutzerdefinierte Datentypen + Operatoren, Vererbung

=> beide Welten vereinigt

14


Name Hersteller Bemerkung

Oracle Oracle Marktfuhrer, sehr teuer, viele

Highend-Features, alle Platt-formen (AS400, Unix, Win32)

Informix (Informix) von IBM gekauftDB2 IBMInterbase Borland jetzt als “Firebird” OpenSourceSybase SybaseAccess Microsoft Desktop-Datenbank mit umfang-

reicher BenutzeroberflacheMS SQL-Server Microsoft nur Win32mySQL freie Software Teilfunktionalitat, speziell fur

manche Web-DB’s hinreichendPostgreSQL freie SoftwareSQLite freie Software verbreitet als embedded

SQL Datenbank

15

1.3 Relationale Datenbanken

Aspekte des relationalen Modells

Daten-Struktur:

Anwender sieht Daten als Tabellen

Daten-Integrität:

Tabellen erfüllen Integritätsbedingungen

Daten-Manipulation:

Operatoren werden auf Tabellen angewendet Output ist wieder eine Tabelle

16

1.3.1 Struktur (1)

Das relationale Modell

Aus Benutzersicht besteht DB aus Tabellen (logische Struktur). Physische Struktur bleibt dem Benutzer verborgen.

Kompletter Informationsgehalt dargestellt in einer Form: als Feldwerte in Tabellenzeilen. (Date: "The Information Principle")

Dalitz DBS Kap1.1-3

17

1.3.1 Struktur (2)

stadt :char(30)nr# :int4 name :char(30)

11

12

Thyssen

Siemens

Duisburg

München

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)

18

1.3.2 Integrität

Integrity Constraints

Bedingungen, die unzulässige Einträge verhindern

Primary Key eindeutiges Zeilenmerkmal; verhindert doppelte Zeilen

Foreign Key Verweis auf andere Tabelle; verhindert Verweis ins Leere

Produkt

nr#

name

herstellernr

Hersteller

nr#

name

19

1.3.3 Manipulation

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

Daten-Änderung

weist bestehenden Tabellen neue Werte zu SQL-Befehle: insert, update, delete

20

1.3.4 weitere Objekte (1)

weitere Datenbankobjekte zur Verwaltung

Objekt Bedeutung

index beschleunigter Zugriff

sequence automatische Schlusselgenerierung

trigger Callback Mechanismus

user Datenbankbenutzer

group Benutzergruppe

(vereinfacht Rechteverwaltung)

database logische Speichereinheit

schema Namespace

Die verfügbaren Objekttypen können je DBS variieren. Z.B. unterstützt Postgres schema erst in Version 7.3

Dalitz DBS Kap1.1-3

21

1.3.4 weitere Objekte (2)

Index

ermöglicht Direktzugriff über Spaltenwert(e) beschleunigt Zugriff, aber verlangsamt Änderungen sinnvoll insbesondere bei Foreign Keys (häufige Joins)

Produkt

pnr#

name

preis

automatischer Index

hnr

durch Primary Key

zusätzlicher Index

auf sinnvollhnr

Bemerkung: Zugriff über Index nicht notwendig schneller als sequentiell. Bei Postgres Statistikauswertung mit (vacuum) analyze nötig.

22

1.3.5 System Catalog (1)

Strukturinformationen werden vom DBS in Tabellen gespeichert

Sammlung dieser Tabellen heißt System Catalog

oder Data Dictionary

Beispiel:

pg attribute: PostgreSQL column meta data

attrelid The table this column belongs to

(references pg class.oid)

attname Column name

atttypid The data type of this column

(references pg type.oid)

... ...

23


System Catalog PostgreSQL:

Catalog Name Purpose

pg attribute table columns (“attributes”, “fields”)

pg class tables, indexes, sequences (“relations”)

pg database databases within this database cluster

pg group groups of database users

pg index additional index information

pg relcheck check constraints

pg trigger triggers

pg type data types

pg user database users

In psql können Beschreibungen mit \d abgefragt werden. \set ECHO_HIDDEN (oder psql -E) gibt Abfragen mit aus.

24


Data Dictionary Oracle:

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

View Purpose

* tables Shows all relational tables

* tab columns Shows all table and view columns

* sequences Lists all sequences in the database

* indexes Lists all indexes

* ind columns Lists all indexed columns

* users Lists all users

* role privs Lists all roles granted to users and other roles

Dalitz DBS Kap1.4

1

1.4 SQL Einführung (1)

Relationale Datenbanksprachen:

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

QUEL - Query Language zeitgleich zu SQL für Ingres an Berkeley University entwickelt trotz "Überlegenheit zu SQL in vielen Bereichen" (Date) keine Marktdurchdringung

QBE - Query by Example intuitiver grafischer Zugriff in Frontends für "Joe User" realisiert (z.B. MS Access)

2


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

Es gibt prozedurale Erweiterungen

PL/SQL (Oracle) und PL/PgSQL (PostgreSQL) Persistend Stored Modules (PSM) in ANSI SQL3

PL/SQL werden wir im Zusammenhang mit Stored Procedures und Triggern behandeln

3


SQL est omnis divisa in partes tres:

DDL - Data Definition Language Definieren und Ändern von Strukturen Kommandos: create, alter, drop

DML - Data Manipulation Language Abfrage und Manipulation der Daten Kommandos: select, insert, update, delete

DCL - Data Control Language Steuerung Datenzugriff und Datensicherheit Kommandos: grant, revoke, begin, commit, rollback

4


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’ )

einzeilige Kommentare durch Doppelminus (-- Kommentar ) mehrzeilige Kommentare wie in C (/*Kommentar*/ ) SQL3 erlaubt geschachtelte Kommentare (/*/*bla*/*/ )

Dalitz DBS Kap1.4

5


Laufendes Beispiel:

pnr#

P1

P2

P3

P4

name

Pritt

Uhu

Tinte

Füller

hnrpreis

2.70

2.50

3.20

12.98

H1

H1

H2

H2

hnr# name stadt

H1

H2

H3

Henkel

Pelikan

Brause

Düsseldorf

Hannover

Iserlohn

Produkt

Hersteller

6

1.4.1 SQL-DDL (1)

Anlegen einer Tabelle

CREATE TABLE tabellenname (

feld1 datentyp1, feld2 datentyp2, ... ...,

PRIMARY KEY (feldn, ...), weitere_constraints

);

Mögliche Datentypen hängen vom konkreten DBS ab

7

1.4.1 SQL-DDL (2)

Datentyp Beschreibung

character( n) String der Lange n, am Ende ggf.char( n) mit Blanks aufgefullt

char varying( n) String variabler Lange (maximal n)varchar( n) im allg. gegenuber char vorzuziehen

integer Ganzzahl mit Vorzeichenint Postgres unterscheidet int2, int4, int8

numeric( n, m) Dezimalzahl mit n Stellen, davon m nachnumeric( n) 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

8

1.4.1 SQL-DDL (3)

Constraints

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 example1 (

a integer, b integer, c varchar(2) REFERENCES example2(a),

PRIMARY KEY (a,b), CONSTRAINT check_b CHECK (b > 0) );

Dalitz DBS Kap1.4

9

1.4.1 SQL-DDL (4)

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

10

1.4.1 SQL-DDL (5)

Mögliche Constraints (2):

PRIMARY KEY ( felder)

formal identisch zu Unique + Not Null pro Tabelle nur maximal ein Primary Key möglich

FOREIGN KEY ( felder) REFERENCES tabelle ( felder)

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)

Ausführliche Dokumentation siehe ddl-constraints.ps.gz (Homepage zur Vorlesung)

11

1.4.1 SQL-DDL (6)

Anlegen des Beispiels hersteller, produkt:

12

1.4.1 SQL-DDL (7)

Auch andere Datenbankobjekte (z.B. index) werden mit create angelegt

Beispiel:

CREATE INDEX produkt_i1 ON produkt (hnr);

Eindeutiger Index:

CREATE UNIQUE INDEX ...

Dalitz DBS Kap1.4

13

1.4.1 SQL-DDL (8)

Ändern von Strukturen

Löschen mit drop

DROP INDEX produkt_i1; DROP TABLE produkt;

Ändern mit alter table

ALTER TABLE produkt ADD FOREIGN KEY (hnr) REFERENCES hersteller(hnr);

ALTER TABLE hersteller ADD COLUMN strasse VARCHAR(30);

14

1.4.2 SQL-DML (1)

Kommandos zur Datenmanipulation:

insert

Einfügen eines neuen Datensatzes

update

Änderung von Feldwerten

delete

Löschen von Datensätzen komplette Tabelle auch mit truncate

select

Auswahl von Daten. Ausgabe ist wieder Tabelle

15

1.4.2 SQL-DML (2)

Einfügen von Datensätzen

INSERT INTO hersteller (

hnr, name, stadt

) VALUES (

’H1’, ’Henkel’, ’Düsseldorf’

);

Die Attributliste kann weggelassen werden. Nicht zu empfehlen! (Warum?)

16

1.4.2 SQL-DML (3)

Es müssen nicht alle Spalten angegeben werden:

INSERT INTO produkt (pnr, name) VALUES (’P5’, ’Papier’);

Ergebnis:

P5 Papier NULL NULL

pnr#

P1

P2

P3

name

Pritt

Uhu

Tinte

hnrpreis

2.70

2.50

3.20

H1

H1

H2

P4 Füller 12.98 H2

Dalitz DBS Kap1.4

17

1.4.2 SQL-DML (4)

Anstelle der values-Klausel kann auch select stehen:

INSERT INTO produkt (pnr, name) SELECT a, b FROM other_table;

Bemerkungen:

Beschreibung von select siehe unten

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)

18

1.4.2 SQL-DML (5)

Ändern von Datensätzen

UPDATE hersteller SET

name = ’Soennecken’, stadt = NULL

WHERE hnr = ’H3’;

Ergebnis:

hnr# name

H1

H2

H3

Henkel

Pelikan

Soennecken NULL

Hannover

Düsseldorf

stadt

19

1.4.2 SQL-DML (6)

Where-Klausel

Weglassen where-Klausel => alle Sätze betroffen

UPDATE produkt SET preis = preis * 2;

Zulässige Vergleichsoperatoren und Behandlung von NULL-Werten später bei select

Zugewiesener Wert kann auch Ergebnis eines select sein ("Subselect")

20

1.4.2 SQL-DML (7)

Löschen von Datensätzen

DELETE FROM produkt WHERE preis > 3;

Ergebnis:

Pritt 2.50 H1P1P2 Uhu 2.70 H1

pnr# name preis hnr

Löschen aller Datensätze

DELETE FROM produkt; TRUNCATE TABLE produkt;

Unterschied: truncate nicht in Transaktion

Dalitz DBS Kap1.4

21

1.4.2 SQL-DML (8)

Selektion von Daten

SELECT name, preis FROM produkt where preis > 3;

pnr#

P1

P2

P3

P4

hnrpreis

2.70

2.50

3.20

12.98

H1

H1

H2

H2

name

Pritt

Uhu

Tinte

FüllerRestrict

Project

22

1.4.2 SQL-DML (9)

Die Spaltenauswahl

Stern (* ) ist Abkürzung für alle Spalten

SELECT * FROM produkt;

Umbenennung Spalten des Ergebnisses möglich

SELECT name AS "Name", preis AS "Brutto" FROM produkt;

Auch Konstanten können selektiert werden

SELECT ’bla’, name FROM produkt;

23

1.4.2 SQL-DML (10)

Demonstrationsbeispiele

SELECT * FROM produkt;

SELECT name AS "Name", preis AS Brutto FROM produkt;

SELECT ’bla’, name FROM produkt WHERE name > ’S’;

SELECT ’bla’ FROM produkt;

24

1.4.2 SQL-DML (11)

Operatoren in where-Klausel

Operator Beschreibung

=,>,<,>=,<= gleich, großer, kleiner

<> ungleich, Postgres: auch !=

BETWEENx ANDy Bereichsprufung

LIKE Pattern matching mit Wildcards_ (ein Zeichen), %(beliebig viele)

SIMILAR TO Pattern matching mit Posix 1003.2regularen Ausdrucken (SQL3)

IS (NOT) NULL Prufung, ob Feld (nicht) leer ist

Verknüpfung von Bedingungen mit AND und OR Negation mit NOT

Dalitz DBS Kap1.4

25

1.4.2 SQL-DML (12)

Behandlung von NULL-Werten

NULL-Werte werden weder durch "=" noch durch "<>" oder "!=" erwischt

explizites Prüfen mit "IS NULL" nötig

dreiwertige Logik: true, false, unknown

Insbesondere ist z.B. das Gegenteil von

feld1 > 2

nicht "feld1 <= 2", sondern

(feld1 <= 2) OR (feld1 IS NULL)

26

1.4.2 SQL-DML (13)

SQL-Funktionen

SQL kennt zwei Klassen von Funktionen:

"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), ...

27

1.4.2 SQL-DML (14)

normale SQL-Funktionen

Typumwandlung kompatibler Datentypen mit cast :

CAST (preis AS FLOAT)

Bemerkungen:

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!

28

1.4.2 SQL-DML (15)

Cast kann mehrdeutig sein:

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

Lösung: Formatierte Umwandlung mit to_date

Analog: to_char, to_number, to_time(stamp)

Dalitz DBS Kap1.4

29

1.4.2 SQL-DML (16)

Datumsformatierung

to_date (’01.02.02’, ’DD.MM.YY’) to_char (einfuehrung, ’DD.MM.YYYY’)

Formatkennzeichen Beschreibung

YYYY, YY Jahr vierstellig, zweistelligMM Monat (01-12)Month, Mon Monat als Text (”Januar”, ”Jan”)DD, DDD Tag des Monats (01-31), Jahres (001-366)D Tag der Woche (1-7, Sonntag=1)HH24, HH am Stunde (00-23), (01-12) mit am/pmMI, SS Minute (00-59), Sekunde (00-59)

30

1.4.2 SQL-DML (17)

Beispielabfrage: Überblick aller seit 1982 eingeführten Produkte

31

1.4.2 SQL-DML (18)

Zahlenformatierung

to_number (’11-’, ’99S’) to_char (preis, ’099.99’)

Formatkennzeichen Beschreibung

9 Ziffer ohne fuhrende Nullen0 Ziffer mit fuhrender NullS Minus-Zeichen bei negativen ZahlenPL Minus- oder Plus-Zeichen. , Dezimalpunkt und TausendergruppeD G Dezimalpunkt und Tausendergruppe

unter Berucksichtigung von locale

32

1.4.2 SQL-DML (19)

Stringfunktionen

Funktion Beschreibung

str1 || str2 String Concatenationlower( str) , upper( str) Conversion to lower, upper casesubstr( str, pos, len) Extraktion Teilstring (pos0=1)substring( str FROMpos FORlen) abstruse SQL2 Syntaxtrim( str [, chars]) vorn und hinten abschneidentrim( [chars] FROMstr) SQL2 Syntaxtranslate( str, from, to) Character Translation

Beispiel:

SELECT upper(name) || ’ kostet ’ || trim(to_char(preis,’99D99’)) || ’ EUR.’ AS "Preisliste" FROM produkt;

Dalitz DBS Kap1.4

33

1.4.2 SQL-DML (20)

Mathematische Funktionen


+ - * / arithmetische Operatorenabs( x) Absolutwerttrunc( x [, n]) abschneiden auf n Nachkommastellenround( x [, n]) runden auf n Nachkommastellen

Datumsfunktionen


current_date Aktuelles Datum oder Uhrzeitcurrent_timestamp SQL3: keine Klammern!age( [ts1,] ts2) Intervall ts1 - ts2

extract( feld FROMts) Feldextraktion (z.B. year)

34

1.4.2 SQL-DML (21)

Aggregatfunktionen

Berechenen einen Wert aus kompletter Spalte

eines Abfrageergebnisses (z.B. Maximum)

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 ( * )

35

1.4.2 SQL-DML (22)

Überblick Aggregatfunktionen

Aggregatfunktion Beschreibung

count() Anzahlmin(), max() Minimum, Maximumsum(), avg() Summe, Mitelwert

Bemerkungen:

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)

36

1.4.2 SQL-DML (23)

Sortieren und Gruppieren

Sortierung mittels order by:

SELECT name, preis FROM produkt ORDER BY name ASC;

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)

Dalitz DBS Kap1.4

37

1.4.2 SQL-DML (24)

Gruppierung mittels group by

Frage: Welcher Hersteller stellt wieviele Produkte her?

Antwort: SELECT hnr, count(pnr) FROM produkt GROUP BY hnr;

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

38

1.4.2 SQL-DML (25)

Ergebniseinschränkung mittels having:

Frage: Welche Hersteller haben höchstens zwei Produkte?

Antwort: SELECT hnr, count(*) FROM produkt GROUP BY hnr HAVING count(*)<=2;

Bemerkung: Bedingung in where-Klausel nicht möglich

where-Bedingung wird vor Gruppenbildung angewandt

Aggregatfunktionen in where-Klausel sinnlos

39

1.4.2 SQL-DML (26)

Demonstration where versus having:

SELECT hnr, count(*), max(preis) FROM produkt WHERE preis > 5 GROUP BY hnr;

SELECT hnr, count(*), max(preis) FROM produkt GROUP BY hnr HAVING max(preis) > 5;

40

1.4.2 SQL-DML (27)

Joins

Wunsch: Produktliste mit Herstellername statt hnr

Naiver Ansatz

SELECT hersteller.name, produkt.name FROM produkt, hersteller ORDER BY 1,2;

liefert nicht gewünschtes Ergebnis (Warum?)

Lösung: Join über gemeinsame Spalte hnr

Dalitz DBS Kap1.4

41

1.4.2 SQL-DML (28)

SELECT hersteller.name, produkt.name FROM produkt, hersteller WHERE produkt.hnr = hersteller.hnr ORDER BY 1,2;

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".

42

1.4.2 SQL-DML (29)

Outer Join

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

43

1.4.2 SQL-DML (30)

Union, Intersect, Except

Kombination von Abfragen mit selben Ergebnisspalten durch Mengenoperationen

Union - Vereinigungsmenge

Intersect - Schnittmenge

Except - Differenzmenge

Achtung: Tatsächlich Mengenoperationen, dh. Doubletten werden entfernt! Wenn unerwünscht, Modifier ALL verwenden

44

1.4.2 SQL-DML (31)

Beispiel

SELECT ’Max’ AS " ", max(preis) AS "Wert" FROM produkt UNION SELECT ’Min’ AS " ", min(preis) AS "Wert" FROM produkt;

Bemerkungen: Selektierte Spalten müssen selben Typ haben Spaltenalias ist nicht zwingend erforderlich, aber für Überschrift sinnvoll

Dalitz DBS Kap1.4

45

1.4.2 SQL-DML (32)

Subqueries

Ergebnis einer Abfrage kann als Unterabfrage (engl. subquery) anstelle einer Tabelle verwendet werden

SELECT x.nr FROM ( SELECT nr, name FROM produkt WHERE preis > 5.0 ) x;

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

46

1.4.2 SQL-DML (33)

Wenn die Subquery nur eine Spalte selektiert, kann das Ergebnis einer Abfrage auch innerhalb einer where-Bedingung verwendet werden:

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

47

1.4.2 SQL-DML (34)

Verwendung Subquery in where-Klausel:

Vergleich mit "=", wenn Subquery genau einen Wert zurückliefert

Vergleich mit "IN", wenn Subquery mehrere Werte zurückliefern kann

Alternative zu "IN" ist Formulierung über "EXISTS" als correlated Subquery

Bemerkung: Die exists Variante ist oft dramatisch schneller

48

1.4.2 SQL-DML (35)

IN versus EXISTS

SELECT * FROM hersteller WHERE hnr IN ( SELECT hnr FROM produkt WHERE preis > ’3.0’ );

SELECT * FROM hersteller WHERE EXISTS ( SELECT hnr FROM produkt WHERE preis > ’3.0’ AND produkt.hnr = hersteller.hnr );

Dalitz DBS Kap1.4

49

1.4.2 SQL-DML (36)

SELECT * FROM hersteller WHERE EXISTS ( SELECT hnr FROM produkt WHERE preis > ’3.0’ AND produkt.hnr = hersteller.hnr );

Bemerkungen:

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 ...)

Correlated Subquery: Innerhalb der Subquery wird eine Tabelle der äußeren Abfrage referenziert (im Beispiel: hersteller)

50

1.4.2 SQL-DML (37)

ALL und ANY

Vergleichsoperationen wenn Subquery mehrere Tupel zurückgibt

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 );

51

1.4.3 SQL-DCL (1)

Data Control Language für Datenschutz/-sicherheit:

Sicherheit vor fehlerhaften Zugriffen Stichwort "Transaktionen" SQL-Kommandos: begin, commit, rollback

Schutz vor unberechtigten Zugriffen Stichwort "Benutzerrechte" SQL-Kommandos: grant, revoke

Vor allem wichtig im Mehrbenutzerbetrieb dh. bei Client-Server Datenbanken

52

1.4.3 SQL-DCL (2)

Transaktionen

− 500x + 500y

− 500x

x y

Stand Konto A Stand Konto B

y

UPDATE konto SET stand=stand−500 WHERE nr=’A’

UPDATE konto SET stand=stand+500 WHERE nr=’B’

Inkonsistenter

Zustand

Dalitz DBS Kap1.4

53

1.4.3 SQL-DCL (3)

Transaktionen erfüllen ACID-Prinzip

Atomicity Transaktion ist Einheit: Alles oder Nichts.

Consistency Transaktion überführt kosistenten Zustand in konsistenten Zustand. Innerhalb Transaktion Inkonsistenz möglich.

Isolation Änderungen in einer Transaktion sind bis zum Abschluss unsichtbar für andere Transaktionen.

Durability Nach Abschluss Transaktion bleiben Änderungen bestehen, auch im Fall eines folgenden Systemabsturzes

54

1.4.3 SQL-DCL (4)

SQL-Kommandos für Transaktionen

BEGIN [WORK] Start einer TransaktionBEGIN [TRANSACTION] Achtung: ggf. implizit (Oracle)COMMIT Abschluss Transaktion

mit Ubernahme der AnderungenROLLBACK Abschluss Transaktion

ohne Ubernahme der Anderungen

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

55

1.4.3 SQL-DCL (5)

Benutzerrechte

DBS hat eigene Benutzerverwaltung

Anlage mit CREATE USER ... Ändern mit ALTER USER ...

Kommandos sind nicht standardisiert. Beispiel Passwortänderung: Oracle: ALTER USER usr IDENTIFIED BY ’pwd’; PostgreSQL: ALTER USER usr WITH PASSWORD ’pwd’;

Auch Zuweisung Admin-Recht (DBA) systemspezifisch

56

1.4.3 SQL-DCL (6)

Der Anleger einer Tabelle ist ihr Owner. Sonst kann keiner auf die Tabelle zugreifen.

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;

Privileges werden erteilt mit GRANT ... TO ...

und entzogen mit REVOKE ... FROM ...

Dalitz DBS Kap1.4

57

1.4.3 SQL-DCL (7)

Überblick Privilegien:

Privileg Berechtigung

SELECT LesenINSERT Einfugen neuer SatzeUPDATE Andern bestehender SatzeDELETE Loschen... weitere Rechte je nach DBS

z.B. rule, references, trigger

Vereinfachungen: ALL kann für alle Privilegien verwendet werden PUBLIC kann für alle User verwendet werden

58

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;

59

Ausblick

SQL-Befehle können interaktiv über SQL-Interpreter eingegeben werden (Oracle: sqlplus, Postgres: psql)

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

Wie greift man aus einem Programm auf DB zu? => nächstes Kapitel

Dalitz DBS Kap1.5

1

1.5 Client Programmierung (1)

Interaktive Eingabe von SQL

DBS liefern SQL-Interpreter mit (Oracle: sqlplus, PostgreSQL: psql)

Nicht praktikabel für Endanwender

Frage: Wie DB-Zugriffe automatisieren (programmieren)?

Serverseitige Programmierung Im DB-Server hinterlegt und von allen Anwendungen genutzt

Clientseitige Programmierung SQL-Kommandos werden aus Anwendungsprogramm aufgerufen

2


Serverseitige Programmierung

proc()

DBMSDB

DBS

SELECT

...FROM

proc()

Anwendg.

stored Procedure

Hauptanwendungsgebiet: Trigger Vorteil: greift unabhängig davon, wie auf die Daten zugegriffen wird (z.B. auch SQL-Interpreter)

3


Clientseitige Programmierung

vorherrschende Form der DB-Programmierung

Anwendungsprogramm führt nur elementare SQL-Kommandos durch

Ablauflogik wird in anderer Sprache (Host Language, z.B. C++) programmiert

Frage: Wie können aus Host Language heraus SQL-Kommandos ausgeführt werden?

4


SQL und Host Language

Methode Beschreibung

SQL-Script Batch Aufruf SQL-Interpreter.Keine Einbindung in Host Language.

embedded SQL Mischen von SQL und Host Language.(ESQL) Praprozessor ubersetzt exec sql Statements.Call Level Routinen in Host Language, SQL-KommandoInterface (CLI) ggf. als Parameter.

Natives CLI: auf konkretes DBS zugeschnitteneBibliothek, z.B. oci (Oracle), libpq (Postgres)Abstraktes CLI: DBS-unabhangige abstrakteBibliothek. Fur konkretes DBS “Treiber” notig.Beipiele: odbc, bde, perl-dbi

Dalitz DBS Kap1.5

5


Typische Einsatzgebiete

Schnittstelle Einsatzgebiet

SQL-Script Einfache Administrative Aufgaben,z.B. User anlegen, DB-Schema einspielen

ODBC DB-unabhangige Massensoftware,z.B. Office-Pakete

Perl-DBI cron-gesteuerte Serverprozesse,als CGI-Script in Web-Programmierung

ESQL Implementierung eigener abstrakternative CLI’s Interfaces, DBS-spezifische Tools,

Individualsoftware, ProgrammierungTreiber fur abstrakte CLI’s

6

1.5.1 SQL-Script (1)

SQL-Interpreter wie psql können nicht nur interaktiv verwendet werden:

Ausführen einer externen Datei (z.B. script.sql) mit SQL-Kommandos

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

Beispiel: psql -c "truncate table produkt;"

7


Alternative: "Fernsteuerung" des SQL-Interpreters durch Umlenkung von stdin

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()

8


Nachteile

keine Kontrollflusssteuerung (nur SQL)

Fehlerbehandlung schwierig

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

=> Einsatz begrenzt auf einfache Aufgaben

Dalitz DBS Kap1.5

9


Beispiel: dropuser von PostgreSQL

#!/bin/sh

# (...) # Commandline Parsing schaufelt # zu löschenden User in Variable $DelUser

psql $PSQLOPT -d template1 -c "DROP USER $DelUser"

# Abfragen Exitcode if [ "$?" -ne 0 ]; then echo "deletion of user \"$DelUser\" failed" 1>&2 exit 1 fi

exit 0

10

1.5.2 natives CLI (1)

Call Level Interface

Zugriff über vom DBS-Hersteller bereitgestellte Bibliotheksroutinen (Oracle: oci, Postgres: libpq)

PostgreSQLServerprozess

Client−programm

...PQconnectdb PQexec

libpq

11


Erzeugung Clientprogramm:

Linker

Header

Library

Compiler

#include

Executable

Code

Object

pgm.c

pgm.o

pgm

libpq.alibpq.sooder

libpq−fe.hSourceCode

12


Konkrete Schritte der Programmierung:

Source Code in Editor erstellen Funktionsprototypen mit #include <libpq-fe.h> einbinden

Zu Object Code compilieren 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

Dalitz DBS Kap1.5

13


Klassifikation libpq-Routinen:

Verbindungsaufbau, -abbau PQconnectdb(), PQfinish(), PQstatus()

Ausführen von SQL-Statements PQexec(), PQresultStatus(), PQcmdTuples(), PQclear()

Verarbeiten von Abfrageergebnissen PQntuples(), PQgetvalue(), PQgetlength()

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

14


Hauptobjekte in libpq:

Datentyp Konstruktor Destruktor

PGconn PQconnectdb() PQfinish()PGresult PQexec() PQclear()

PGconn

pgm

PGresult

Variable

PQconnectdb()

PQexec()PQclear()

PQfinish()

SQL

PQgetvalue()

15


Beispiel Verbindungsaufbau, -abbau

PGconn* conn;

/* Login */ conn = PQconnectdb("dbname=db user=usr ...");

/* Fehlerprüfung */ if (PQstatus(conn) == CONNECTION_BAD) { /* ... */ }

/* (...) */

/* Logout */ PQfinish(conn);

16


Beispiel Non-Select

PGconn *conn; PGresult *res;

/* Absetzen SQL-Statement */ res = PQexec(conn, "DELETE FROM produkt WHERE preis>’3.0’");

if (PQresultStatus(res) == PGRES_COMMAND_OK) { /* Rückmeldung Auswirkungen */ printf("%s Sätze gelöscht\n", PQcmdTuples(res)); } else { /* Fehlerbehandlung */ }

/* Speicher freigeben nicht vergessen! */ PQclear(res);

Dalitz DBS Kap1.5

17


Beispiel Select

PGconn *conn; PGresult *res;

/* Absetzen SQL-Statement */ res = PQexec(conn, "SELECT usename FROM pg_user");

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);

18


weiterführende Literatur zu libpq:

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/

Hartwig beschreibt auch die C++ Bibliothek libpq++. Achtung: Informationen sind veraltet!

19

1.5.3 abstraktes CLI (1)

abstraktes Call Level Interface

Zugriff über DBS-unabhängige Bibliotheksroutinen passender "Treiber" wird zur Laufzeit vom "Treiber-Manager" geladen

DB−Server

Manager

Treiber ODBCTreiber

natives

CLI

odbc−lib

SQLFetch ...SQLGetData

Clientprogramm

20


Vorteile 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

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

Dalitz DBS Kap1.5

21


Überblick

Abstraktion Host Language Hersteller

Open Database C Microsoft

Connectivity (ODBC) Visual Basic offener Standard

Java Database Java Sun

Connectivity (JDBC) offener Standard

Borland Database Object Pascal Borland

Engine (BDE) C++

Perl Database Perl Tim Bunce

Interface (DBI) offener Standard

Wir schauen uns konkret an: ODBC (prinzipieller Aufbau) Perl-DBI (Praktikumsaufgabe)

22


verbreitete ODBC-Irrtümer

ODBC ist nur für Windows-Programme 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

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

Single Tier: Treiber implementiert SQL-Abfragen Multiple Tier: Treiber reicht SQL an DBS weiter

23


Programm

ODBCTreiber

SQL

DB

Programm

ODBCTreiber

DB

mentierung

SQL Imple−

SQL

Tier

Single

SQL

DBS

DBMS

Multiple

Tier

24


ODBC Data Sources

Abstraktion Verbindungsparameter zu verwendender ODBC-Treiber Treiber-spezifische Parameter (z.B. pghost, pgdatabase, ...)

einem Satz Verbindungsparameter wird ein Data Source Name (DSN) zugewiesen

statt Parameter beim Login zu übergeben, gibt das Anwendungsprogramm den DSN an

Zuordnung DSN zu Parametern: hinterlegt in Datei (Unix) oder Registry (Win32) ODBC-Infrastruktur stellt Config-Tool(s) bereit

Dalitz DBS Kap1.5

25


DSN-Konfiguration

26


TreiberManager

ConfigTool(s)

DSNKonfiguration

System User

Applikation DBS

ODBCInfrastruktur

Treiber

27


Vereinfachtes Beispiel ODBC-Connection:

SQLHENV sqlenv; /* Handle ODBC environment */ long rc; /* result of functions */ SQLHDBC sqlconn; /* 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 */ }

/* Free Resources */ SQLFreeHandle(SQL_HANDLE_DBC, sqlconn); SQLFreeHandle(SQL_HANDLE_ENV, sqlenv);

28


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)

ODBC Referenzen:

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/

Dalitz DBS Kap1.5

29


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

Meinungen zu Perl: "Practical Extracting and Report Language" "Perl is awk with skin cancer." "Perl wird nicht mehr weiterentwickelt, weil alle Sonderzeichen aufgebraucht sind."

30


Programme in Scriptsprachen können nicht direkt vom OS ausgeführt werden, sondern brauchen eine Laufzeitumgebung, den Script-Interpreter.

Aufruf des Perlscripts script: perl script arg1 arg2 ... arg1, ... sind Kommandozeilenargumente für script

Unter Unix (oder bei Aufruf aus der Cygwin-Shell) kann script direkt ausführbar gemacht werden: als erste Zeile des Scripts einfügen: #!/usr/bin/perl Script ausführbar machen mit: chmod +x script

Methode geht mit vielen Scriptsprachen (sh, perl, wish)

31


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

32


Beispiel für Perl Code:

# erstelle %list mit Usern und Passwörtern srand();

for ($nr = 1; $nr <= 35; $nr++) { # Username: Prefix "dbs" + laufende Nr $uid = sprintf("dbs%02d", $nr);

# zufällige Generierung Passwortzeichen $pwd = ""; for ($i = 0; $i < 8; $i++) { $pwd .= chr(int(rand 42) + 48); }

# Zuordnung User => Passwort $list{$uid} = $pwd; }

Dalitz DBS Kap1.5

33


Perl DBI

Wie bei ODBC braucht der DB-Zugriff mit dem Perl Database Interface (DBI) zwei Komponenten:

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");

34


DBI arbeitet als objektorientierte Bibliothek ähnlich wie libpq mit zwei Objekten:

Objekt Bedeutung

$dbh Database handle object

$sth Statement handle object

$dbh

$sth

script

$dbh−>disconnectDBI−>connect

$dbh−>prepare

$sth−>execute

$sth−>fetchrow_array

SQL

$sth−>finish

@row

35


Verbindungsaufbau

$dsn = "dbi:Pg:dbname=test;host=dbs;port=5432"; %attr = (AutoCommit => 0, PrintError => 0, RaiseError => 0);

$dbh = DBI->connect($dsn, "uid", "pwd", \%attr); if (!$dbh) { # Fehlerbehandlung }

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, ...})

36


Wichtige DBI-Attribute

Attribut Bedeutung

AutoCommit Nach jedem Statement automatisch

Commit (0 = off, 1 = on (default))

PrintError Fehler werden automatisch nach stderrausgegeben (0 = off, 1 = on (default))

RaiseError Bei Fehler wird Exception (vgl. C++)

geworfen (0 = off (default), 1 = on)

Attribute werden als Hash-Referenz im vierten Argument bei DBI->connect übergeben:

DBI->connect($dsn, $uid, $pwd, {AutoCommit => 0, PrintError => 0});

Dalitz DBS Kap1.5

37


Beispiel Non-Select

$sth = $dbh->prepare("delete from produkt");

if (!$sth->execute) { # Fehler printf ("%s\n", $dbh->errstr); } else { # Rückmeldung über Erfolg printf ("%d Zeilen gelöscht\n", $sth->rows); }

Bemerkung: Für Non-Select Statements gibt es auch die Methode $dbh->do() (Zusammenfassung von prepare + execute)

38


Beispiel Select

$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;

39


Referenzen

Online Kurse Perl: http://www.phy.uni-bayreuth.de/~btpa25/perl/perl_inhalt.html http://www.pronix.de/perl/perl.html

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

Beispielprogramm bsp-perldbi auf Homepage

40

1.5.4 embedded SQL (1)

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

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

Dalitz DBS Kap1.5

41


Ein Beispiel:

EXEC SQL BEGIN DECLARE SECTION; int res; EXEC SQL END DECLARE SECTION;

int main() { EXEC SQL CONNECT TO dbname USER uid/passwd;

EXEC SQL SELECT COUNT(*) INTO :res FROM pg_user;

printf("Anzahl User: %d\n", res);

return 0; }

42


Übersetzen ESQL-Programm:

pgm.o pgm

pgm.pgc

pgm.c

ESQLCode

reinerC Code

ObjectCode

Compiler Linker

ecpgtype.hecpglib.hecpgerrno.h

libecpg.alibpq.a

Executable

Library

Header

#include

Präprozessor

43


Konkrete Schritte der Programmierung:

ESQL Source Code in Editor erstellen Dateinamenskonvention: *.pgc

ESQL-Präprozessor aufrufen ecpg pgm.pgc (erzeugt pgm.c)

Zu Object Code compilieren gcc -c -I/usr/include/pgsql pgm.c "/usr/inlude/pgsql" ist Verzeichnis mit Postgres-Headern

Mit libecpg und libpq linken gcc -o pgm pgm.o -L/usr/lib/pgsql -lecpg -lpq "/usr/lib/pgsql" ist Verzeichnis mit Postgres-Libraries

44


Probleme bei "Zwittercode"

Problem Losung

Variablenaustausch Definiton von gemeinsamen Variablen

in exec sql declare Abschnitt

Laufzeit-Generierung Ubernahme von Statements ausvon SQL-Statements Variablen mit exec sql prepare

(“dynamic SQL”)

Navigation in Select- SQL-Erweiterung: Cursor

Ergebnissen (Tabellen)

Fehlerkommunikation globale Variable (structure) sqlca

(“SQL Communication Area”)

Dalitz DBS Kap1.5

45


gemeinsame Variablen

Shared Variables werden als C Variablen in der Declare Section deklariert

EXEC SQL BEGIN DECLARE SECTION; int nr; char name[30]; EXEC SQL END DECLARE SECTION;

Verwendung in SQL mit vorangestelltem Doppelpunkt

EXEC SQL DELETE FROM produkt WHERE nr=:nr;

46


C- und SQL-Datentyp müssen kompatibel sein

SQL-Typ C-Typ

INTEGER int, char[]NUMERIC double, char[]CHAR(n), VARCHAR( n) char[ n+1]DATE char[12]TIMESTAMP char[28]

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

47


dynamic SQL

Shared Variables können auch SQL-Kommandos enthalten. Zum Ausführen zwei Schritte nötig:

Definition SQL Statement-Variable mit prepare

strcpy(stmt, "delete from produkt"); EXEC SQL PREPARE sqlstmt FROM :stmt;

Ausführen der Statements mit execute

EXEC SQL EXECUTE sqlstmt;

48


Select-Ergebnisse

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

Ergebnis kann mehrere Zeilen enthalten => einzelne Zeilen müssen mit einem Cursor abgearbeitet werden

Dalitz DBS Kap1.5

49


Single-Row Select

/* Deklaration Shared Variables */ EXEC SQL BEGIN DECLARE SECTION; double preis; char einf[12]; EXEC SQL END DECLARE SECTION;

/* Füllen Shared Variables in SQL */ EXEC SQL SELECT preis, to_char(einfuehrung,’DD.MM.YY’) INTO :preis, :einf FROM produkt WHERE pnr = ’P1’;

/* Benutzung Shared Variables in C */ printf("preis: %0.2f; einf: %s\n", preis, einf);

50


Tabellen-Retrieval (1)

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

Ausführen des Statements und Positionierung des Cursors vor die erste Zeile mit open

Auslesen der Zeilen mit fetch in einer Schleife

Schließen des Cursors mit close

51


Tabellen-Retrieval (2)

EXEC SQL BEGIN DECLARE SECTION; char name[256]; EXEC SQL END DECLARE SECTION;

/* Cursor Definition */ EXEC SQL DECLARE cursor1 CURSOR FOR SELECT usename FROM pg_user; EXEC SQL OPEN cursor1;

/* Loop über Ergebnisse */ EXEC SQL WHENEVER NOT FOUND DO break; while (1) { EXEC SQL FETCH cursor1 INTO :name; printf("%s\n", name); }

/* Cursor Schliessen */ EXEC SQL CLOSE cursor1;

52


Fehlerbehandlung

Kommunikation über Erfolg der SQL-Statements über globale Variable sqlca (SQL Communication Area)

Einbindung mit EXEC SQL INCLUDE sqlca;

In C Struktur. Wichtigste Felder:

sqlca.sqlcode Fehlercode letztes Statement

(alternativ Makro: SQLCODE)sqlca.sqlerrm.sqlerrmc Fehlermeldung vom DBS

Achtung: Struktur von sqlca in SQL2 nicht festgelegt Nur Werte für das Makro SQLCODE sind festgelegt

Dalitz DBS Kap1.5

53


Werte für sqlca.sqlcode bzw. SQLCODE:

Wert Bedeutung

0 kein Fehler

100 keine Datensatze gefunden

< 0 Fehler bei Ausfuhrung Statement

konkrete Codes DBS-spezifisch> 0 Warnung (z.B. String zu klein)6= 100 konkrete Codes DBS-spezifisch

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

54


Beispiel Fehlerbehandlung

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


Verbindungsaufbau und -abbau

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])

Fehlerabfrage über SQLCODE bzw. sqlca.sqlcode

56

1.5.5 weitere Ansätze (1)

Webanwendungen

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

Dalitz DBS Kap1.5

57


Persistent Database Connections

childprocesses

parentprocess

WebClient DBS

Webserver

Voraussetzung: Verarbeitung nicht über CGI sondern Script-Modul

58


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

59


Fourth Generation Language (4GL)

proprietäre Sprache des DBS-Herstellers, die SQL und GUI-Programmierkonzepte vereinigt (Beispiele: Informix-4GL, Sybase PowerBuilder)

Bezeichnung ist Marketing-Gag durch Gleichsetzung Machinencode=1GL, Assembler=2GL, C etc.=3GL

Weiterführung dieser Idee sind spezielle Application Development Frameworks einzelner Hersteller

Vorteil: Customizing statt Programmierung => niedrige Entwicklungskosten

Dalitz DBS Kap2.1-3

1

2 Datenmodelle

Bisher:

Datenbankpraxis

Welche Objekte bietet relationale Datenbank? Wie manipuliert man Datenbank-Objekte? Wie greift man aus Programm auf Datenbank zu?

Datenbankstruktur als gegeben betrachtet

Jetzt:

Datenmodellierung

Wie designt man eine "vernünftige" Datenbankstruktur? Welche Strukturen sind "(un)vernünftig"?

2

2.1 Überblick (1)

Ebenen des Datenbankentwurfs:

konzeptionelle Ebene logische Gesamtsicht des Anwenders auf die Daten unabhängig vom eingesetzten DBS-Typ

Implementierungsebene konzeptionelle Datenstrukturen im Rahmen des eingesetzten DBS bei relationalem DBS z.B. Tabellen

physische Ebene konkrete Implementierung der Strukturen im Rahmen des eingesetzten DBS betrachtete Strukturen: Datenblöcke, Zeiger, Indexstrukturen

3

2.1 Überblick (2)

ANSI/SPARC Architektur

externe Ebene Benutzersicht einzelner Endanwender auf die Daten z.B. in Masken des User-Interface manifestiert mehrere Sichten möglich

konzeptionelle Ebene logische Gesamtsicht auf die Daten das umgangssprachliche "Datenmodell" der Anwendung

interne Ebene physische Datenstruktur auf dem Rechner

Verhältnis zu den Ebenen des Datenbankentwurfs?

4

2.1 Überblick (3)

konzeptio−nelle Ebene

Implementie−rungsebene

physischeEbene

konzeptio−nelle Ebene

interneEbene

Datenbankentwurf ANSI/SPARC

Benutzer−sichten

Dalitz DBS Kap2.1-3

5

2.1 Überblick (4)

Themeneinordnung

Thema Entwurfsebene

2.2 Relationales Modell: ImplementierungsebeneDefinition, Normalisierung

2.3 Relationales Modell: ImplementierungsebeneRedundanzvermeidung,Abhangigkeiten, Normalformen

2.4 Semantischer Ansatz konzeptionelle EbeneEntity/Relationship

2.5 Relationale Algebra Implementierungsebeneund relationaler Kalkul

. . . Speicherstrukturen physische Ebene

6

2.1 Überblick (5)

Der Entwurfsprozess

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

Implementierung Umsetzen konzeptionelles Design in relationales Modell Formulierung in Data Defintion Language (DDL)

physisches Design erfolgt durch DBS aufgrund DDL

Anwendungsprogrammierung Umsetzung Funktionen in User-Interface und weitere Programme

7

2.1 Überblick (6)

Anforderungs−Analyse

physisches Design

konzeptionelles Design

Implementierung

Anwendungs−programmierung

Funtionale Anforderungen Datenstruktur Anforderungen

DBS−spezifisch

DBS−unabhängig

8

2.1 Überblick (7)

Entwurfsprozess ist keine Einbahnstraße

Analyse Schritt 1 Schritt 2 Schritt 3 Einsatz

Aber Faustregel: Kosten für Änderung wachsen exponentiell in Schrittnummer, d.h. 1 Euro Änderungskosten in Analysephase entsprechen 100 Euro in Realisierung

Dalitz DBS Kap2.1-3

9

2.1 Überblick (8)

Vorgehen Anforderungsanalyse:

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

10

2.2 Das relationale Modell

2.2.1 Relationen

Grundbegriffe einer Relation:

Wertebereich,Domain

stadthnr :char(2) name :varchar(30) :varchar(30)

Pelikan

Henkel

Hannover

DüsseldorfH1

H2

Attribut

Tupel

11

2.2.1 Relationen (2)

Vergleich der Begriffe Relation und Tabelle

relationaler Begriff “Umgangssprache”

Relationsschema Tabellendefinition

Relation Tabelle(-szustand, -sinstanz)

Attribut Spaltenname

Domain (Wertebereich) Datentyp

Tupel Reihe, Zeile

Bemerkungen Eine Relationen variiert mit der Zeit, während das Relationsschema weitgehend konstant bleibt Unterschiede Tabelle und Relation später

12


Bestandteile eines Relationsschemas

R(A1:dom(A1), A2:dom(A2), . . . , An:dom(An))

• Relationsname R

• Attributliste A1, . . . , An

Jedem Attribut Ai ist eine Menge moglicherWerte zugeordnet, die Domain dom(Ai).Domains konnen endlich oder unendlich sein.

Relationsschema beschreibt Relation mit Namen R. Die Attributanzahl n heißt Grad der Relation.

Dalitz DBS Kap2.1-3

13


Eine Relation r(R) des Relationsschemas R(A1, .., An)ist eine endliche Menge von n-Tupeln

ti = (x1, . . . , xn) mit xj ∈ dom(Aj) ∪ {NULL}

NULL ist ein spezieller Nullwert.

Eine Relation r konnen wir also schreiben:

r =

m⋃

i=1

{ti}

Fur den j-ten Wert xj in Tupel t (dh. fur den Wert desAttributs Aj im Tupel t) schreiben wir auch t[Aj ].

14


Umformulierung Relations-Definition:

Eine Relation r(R) ist eine mathematische Relation

n-ten Grades auf den Mengen dom(A1), ..., dom(An).

D.h. r(R) ist eine Teilmenge des kartesischenProdukts der Wertebereiche:

r(R) ⊆ dom(A1) × dom(A2) × · · · × dom(An)

Konsequenzen der Definition als Menge:

keine Ordnung auf den Tupeln definiert keine zwei identischen Tupel (Doubletten) möglich

15


Relationale Datenbanken und SQL basieren nicht auf Relationen (Mengen von Tupeln), sondern auf ungeordneten Listen (engl. Bags, Multisets), d.h.

Tupel sind ungeordnet Doubletten sind möglich

Teilweise erlaubt SQL Wahl (distinct versus all)

Vorteile von Bags:

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

16


Weitere Verallgemeinerung:

Attributreihenfolge in Relation unerheblich in formaler Defintion Tupel nicht mehr als geordnete Liste von Werten auffassen, sondern als Menge von Attribut/Wert Paaren:

t =

n⋃

i=1

{Ai : xi} wobei xi ∈ dom(Ai) ∪ {NULL}

Voraussetzung: keine gleichnamigen Attribute in Relation Wir nehmen im Folgenden Attributreihenfolge als egal an, verwenden aber die (einfachere) Listennotation

Wie verhält sich SQL diesbezüglich?

gleichnamige Attribute in Relation unzulässig Attribute können in beliebiger Reihenfolge angegeben werden Aber: INSERT ohne Attributnamen gemäß fester Reihenfolge

Dalitz DBS Kap2.1-3

17


Notationskonventionen

R(A1, A2, ..., An) Relationsschema n-ten GradesRelationsnamen: Q, R, S

r(R), r Relationszustand. Buchstaben: q, r, s

t = (x1, x2, ..., xn) Tupel ∈ r. Buchstaben: t, u, v

t[Ai] Wert des Attributs Ai im Tupel t

t[Ai1 , Ai2 , ..., Aik] Subtupel (t[Ai1 ], t[Ai2 ], ..., t[Aik

])

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

18

2.2.2 Integrität (1)

Primärschlüssel

Eine Teilmenge {Ak1, ..., Akm

} von Attributen heißtSchlusselobermenge (Superkey) des Relationssche-mas R, wenn es keine zwei Tupel geben kann, die inallen Werten dieser Attribute gleich sind, d.h.

ti[Ak1, ..., Akm

] 6= tj [Ak1, ..., Akm

] fur alle i 6= j

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


Ein Schlüsselkandidat ist ein minimaler Superkey, d.h. ein Superkey, aus dem kein Attribut entfernt werden kann ohne die Eindeutigkeits-Bedingung zu verletzen.

Primärschlüssel: im allg. hat Relation mehrere Schlüsselkandidaten ein Kandidat wird als Primärschlüssel ausgewählt

Bemerkungen: 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

20


Beispiel:

X83554

fahrgestellnr

A69352

hersteller

Ford

VW

modell

Beetle

KA

jahr

2000

2001

KR−AD 102

DU−PW 430

amtlkz#

auto

Fragen:

Welche Superkeys gibt es? Wieviele Superkeys gibt es? Welche Schlüsselkandidaten gibt es?

Dalitz DBS Kap2.1-3

21


Ein Primärschlüssel ist eine Integritätsbedingung für eine einzelne Relation:

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:

erzwingt die Existenz eines Tupels in einer Relation, wenn sich ein Tupel einer anderen Relation darauf bezieht

realisiert durch Fremdschlüssel

22


Eine Attributmenge F ⊆ {A1, ..., An} der Relation R1

ist ein Fremdschlussel von R1, der die Relation R2

referenziert, wenn gilt:

• die Attribute in F haben dieselben Domainswie die Primarschlusselattribute von R2

• die Werte t1[F ] in einem Tupel t1 ∈ R1 kommenentweder als Primarschlusselwerte in einemTupel t2 ∈ R2 vor, oder sie sind alle NULL

Wir sagen: “t1 referenziert t2”.R1 ist die referenzierende, R2 die referenzierte Relation.

23


Beispiel für Fremdschlüssel:

pnr#

P1

P2

name

Pritt

Papier

hnrpreis

5.30

2.50 H1

NULL

hnr# name stadt

H1

H2

Henkel

Pelikan

Düsseldorf

Hannover

produkthersteller

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

24


Komponenten eines relationalen Datenbankschemas:

Menge von Relationsschemas Menge von Integritätsbedingungen

mögliche Darstellungen:

textuelle Darstellung (z.B. SQL-DDL) grafische Darstellung mit Pfeilen für Fremdschlüssel

hersteller

namehnr#

stadt

pnr#namehnrpreis

produkt

Dalitz DBS Kap2.1-3

25


Relationales Schema in SQL-DDL

CREATE TABLE hersteller ( hnr CHAR(2), name VARCHAR(30), stadt VARCHAR(30), PRIMARY KEY(hnr) );

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) );

26


Grafische Darstellung Variante 1: Relationsschema als Spaltenvektor

hersteller

namehnr#

stadtdatum

pnamephnr

lnr#

lieferung

name#

produkt

hnr#

preis

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?)

27


Grafische Darstellung Variante 2: Relationsschema als Zeilenvektor

hnr# name# preis

produkt

lnr# datum phnr pname

lieferung

hnr# stadtname

hersteller

28

2.2.3 Normalisierung (1)

Relationales Modell nach Codd (1970):

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.

Das Umformulieren eines Schemas derart, dass die erste Normalform erfüllt ist, heißt Normalisierung.

Dalitz DBS Kap2.1-3

29


Unzulässig in 1NF:

hnr# name adresse

40191

branche

H1 HenkelHenkelstr. 67 {Klebstoff, Wasch−

mittel, Kosmetik}Düsseldorf

hersteller

adresse besteht aus drei Unterwerten branche ist Menge von Werten, d.h. selber wieder Relation

Bemerkungen: 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

30


hnr# name adresse

40191

branche

H1 HenkelHenkelstr. 67 {Klebstoff, Wasch−

mittel, Kosmetik}Düsseldorf

hersteller

Wie kann dieses Schema normalisiert werden?

zusammengesetzter Wert für adresse: ersetze adresse durch drei Felder strasse, plz, ort

Mehrfachwerte für branche: verschiedene Lösungen möglich

31


Lösung 1:

hnr# branche#

H1 KlebstoffH1 WaschmittelH1 Kosmetik

Henkelstr. 67 40191Henkel Düsseldorf

name plz ortstrasse

H1

hnr#

hersteller

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

32


Lösung 2:

KlebstoffH1 Henkel Henkelstr. 67 40191 Düsseldorf

WaschmittelH1 Henkel Henkelstr. 67 40191 Düsseldorf

KosmetikH1 Henkel Henkelstr. 67 40191 Düsseldorf

hnr# name strasse plz ort branche#

hersteller

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?)

Dalitz DBS Kap2.1-3

33


Lösung 3:

hersteller

name plz ortstrassehnr#

Henkelstr. 67 40191Henkel DüsseldorfH1

branche2

WaschmittelKlebstoff

branche1 brancheN

NULL

...

...

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

34


Bewertung der drei Lösungen

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: führt Redundanzen ein: überflüssige Mehrfachspeicherung der Attribute hnr, name, strasse, plz, ort

=> Speicherplatzverschwendung => Updates kompliziert, da Gefahr der Inkonsistenz

Lösung 1 ist unbedingt vorzuziehen

35


Redundanzen und Anomalien

Die in Lösung 2 eingeführten Redundanzen sind besonders unerwünscht, weil sie zu Inkonsistenzen bei Änderungen führen (Update-Anomalien)

Das Problem ist, dass bei Änderungen mehrere Tupel verändert werden müssen, wenn die Information nicht inkonsistent werden soll.

Problem betrifft alle Änderungen:

Einfüge-Anomalien Lösch-Anomalien Modifikations-Anomalien

36






hersteller

Einfüge-Anomalien (1)

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

Dalitz DBS Kap2.1-3

37


Lösch-Anomalien

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

Problem betrifft auch Lösung 1 und kann durch Einführung einer Referenztabelle branche gelöst werden

hnr# bnr#

H1 B1H1 B2H1 B3

bnr# name

B1 KlebstoffB2 WaschmittelB3 Kosmetik


name plz ortstrasse

H1

hnr#

hersteller_branche

hersteller

branche

38


hnr# bnr#

H1 B1H1 B2H1 B3

bnr# name

B1 KlebstoffB2 WaschmittelB3 Kosmetik


name plz ortstrasse

H1

hnr#

hersteller_branche

hersteller

branche

Einfüge-Anomalien (2)

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

39






hersteller

Modifikations-Anomalien

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

40

2.2.4 Designrichtlinien

Informelle Qualitätsmaße für den Entwurf eines Relationsschemas:

Inhaltliche Bedeutung (Semantik) der Relationen und Attribute sollte leicht verständlich sein

Reduzierung redundanter Werte in Tupeln

Reduzierung der Nullwerte in Tupeln

Verhinderung der Erzeugung "unechter" Tupel

Dalitz DBS Kap2.1-3

41

2.3 Normalformen (1)

Bei Normalisierung enstanden Modelle mit selbem Informationsgehalt, aber unterschiedlicher "Güte":

hnr# name strasse plz ort

hersteller hersteller_branche

hnr# branche#

hnr# name strasse plz branche#ort

herstellerVariante 2

Variante 1

Variante 2 führt zu unerwünschten Redundanzen, die Update-Anomalien verursachen Variante 1 hat diesen Nachteil nicht

Ziele: formale Definition von Redundanzen Kriterien für Redundanzfreiheit

42

2.3 Normalformen (2)

Redundanz eines Attributs: Ein Attribut ist redundant, wenn einzelne Attributwerte ohne Informationsverlust weggelassen werden können.

hersteller





redundante Attribute

überflüssige Werte

Wie können wir Redundanz formal definieren?

43

2.3.1 Abhängigkeit (1)

2.3.1 Funktionale Abhängigkeit

Eine funktionale Abhangigkeit “X → Y ” zwischen zweiAttributmengen X, Y eines Relationsschemas R

besteht, wenn fur beliebige Tupel t1, t2 ∈ r(R) gilt:

t1[X ] = t2[X ] =⇒ t1[Y ] = t2[Y ]

Die Werte der X-Komponente eines Tupels bestimmenalso eindeutig alle Werte der Y -Komponente.Die Y -Werte sind also eine Funktion der X-Werte:

t[Y ] = f(t[X ])

44


Beispiel





hersteller

Es gilt {hnr, branche} → {name, strasse, ort, plz} {hnr} → {strasse, ort} {hnr, name} → {plz}

Es gilt nicht {hnr} → {branche} {branche} → {name, plz}

Dalitz DBS Kap2.1-3

45






hersteller

Bemerkungen (1)

funktionale Abhängigkeit ist Eigenschaft des Relationsschemas R

und nicht eines Relationszustands r(R) In obiger Beipielrelation gilt {strasse} → {ort} Trotzdem keine funktionale Abhängigkeit

funktionale Abhängigkeit ist Eigenschaft der Bedeutung

(Semantik) der Attributwerte keine beweisbaren Tatsachen erfordert Intuition des Datenbankdesigners

46






hersteller

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

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)

47


Armstrongs Regeln

IR1 Reflexivität:

Y ⊆ X ⇒ X → Y

IR2 Augmentation:

X → Y ⇒ X ∪ Z → Y ∪ Z

IR3 Transitivität:

X → Y und Y → Z ⇒ X → Z

(Beweis der Regeln an der Tafel)

48


Anmerkungen zu Armstrongs Regeln:

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

Die Menge aller aus F herleitbaren Abhängigkeiten heißt Hülle (Closure) von F (symbolisch F+)

Dalitz DBS Kap2.1-3

49


Beispiel:





hersteller

Aus {hnr} → {name, strasse, plz, ort} lässt sich nur mit Armstrongs Regeln ableiten (Wie?):

{hnr} → {name}

{hnr, branche} → {hnr, name, strasse, plz, ort, branche}

50


Weitere, aus IR1-3 ableitbare Inferenzregeln:

IR4 Zerlegung:

X → Y ∪ Z ⇒ X → Y und X → Z

IR5 Vereinigung:

X → Y und X → Z ⇒ X → Y ∪ Z

IR6 Pseudotransitivität:

X → Y und W ∪ Y → Z ⇒ W ∪ X → Z

IR7 Komposition:

X → Y und V → W ⇒ X ∪ V → Y ∪ W

(Beweis: IR5+7 an Tafel, IR4+6 Übung)

51


Erste Anwendung Abhängigkeiten:


hersteller hersteller_branche

hnr# branche#Schema 2

Schema 1hnr# name strasse plz branche#ort

hersteller

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;

Frage: Wann ist Zerlegung verlustfrei (additiver Join)?

52


Theorem (Heath 1971):

Sei R(A,B,C) ein Relationsschema, wobei A, B und C

Attributmengen sind.

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}

Bemerkungen: die Bedingung ist hinreichend, aber nicht notwendig äquivalentes Kriterium über mehrwertige Abhängigkeiten (später) Dieses Theorem ist die Grundlage des Normalisierungs-Prozesses

Dalitz DBS Kap2.1-3

53


Zerlegung 1hst1

hnrstrasse plz ort name

hst2

hnr branche

Zerlegung 2hst3

name strasse plz ort branchehnr

hst4

branche

plz ort branche

Waschmittel20355 Hamburg

Waschmittel40191 Düsseldorf

Kosmetik40191 Düsseldorf

hnr strasse

H2 Dammtorwall 15

H1 Henkelstr. 67

H1 Henkelstr. 67

Unilever

Henkel

Henkel

name

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

54

2.3.2 2NF und 3NF (1)

2.3.2 Zweite und Dritte Normalform

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

Denormalisierung 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

55

2.3.2 2NF und 3NF (2)

NF’s auf Basis projektiver Zerlegung

nur trivialeVerbundabhängigkeiten

Dritte Normalform (3NF)

Boyce−Codd Normalform (BCNF)

Fünfte Normalform (5NF)

Erste Normalform (1NF)

nur Abhängigkeiten vom Schlüssel

keine transitiven Abhängigkeiten

Zweite Normalform (2NF)

keine mehrwertigen Abhängigkeiten

Vierte Normalform (4NF)

alle Attribute voll vom Schlüssel abhängig

beliebige Relation mit atomaren Werten

56

2.3.2 2NF und 3NF (3)

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

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

es gibt weitere Normalformen, die allerdings nicht (ausschließlich) auf Projektion und Join beruhen, z.B. Domain-Key NF

Dalitz DBS Kap2.1-3

57

2.3.2 2NF und 3NF (3)

hersteller





redundante Attribute

ü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

58

2.3.2 2NF und 3NF (4)

Definition zweite Normalform (Annahme: nur ein Schlüsselkandidat)

Ein Relationsschema ist in zweiter Normalform (2NF),

wenn jedes nicht primäre Attribut voll funktional vom Primärschlüssel abhängt.

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.

59

2.3.2 2NF und 3NF (5)

Beispiel:



hst1

hst2 hst3

hnr# branche#

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

60

2.3.2 2NF und 3NF (6)

Ein Relationsschema, das nicht in 2NF ist, kann in mehrere Schemas in 2NF zerlegt werden

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.

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.

Bemerkungen: Bedingung a) und Heath’s Theorem gewährleisten additiven Join Bedingung b) nötig, um Verlust von funktionalen Abhängigkeiten zu vermeiden

Dalitz DBS Kap2.1-3

61

2.3.2 2NF und 3NF (7)


hst2 hst3

hnr# branche#Zerlegung 1

hst4

hnr# ort

hst5

hnr# branche#


hst1

hst3

Zerlegung 2 hnr# name strasse plz

Zerlegung 1 gemäß Regeln a), b) und c)

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"

62

2.3.2 2NF und 3NF (8)

Mit der 2NF werden bestimmte Redundanzen ausgeschlossen. Andere aber nicht:

namehnr# plzstrasse

PolyantH2 Speefeld 7 Kempen47906

de BeukelaerH1 Arnoldstr. 62 Kempen47906

ort

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}

63

2.3.2 2NF und 3NF (9)

Definition Dritte Normalform (Annahme: nur ein Schlüsselkandidat)

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.

Bemerkungen:

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

64

2.3.2 2NF und 3NF (10)

Gelegentlich andere "Definition" in Literatur:

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.

Dies ist keine äquivalente 3NF Definition, sondern nur eine notwendige (aber nicht hinreichende!) Bedingung für 3NF

Gegenbeispiel:

p1# p2# x1 x3x2

erfüllt obige "Definition" enthält aber transitive Abhängigkeit {p1#,p2#} → {p2#,x1} → {x3}

Dalitz DBS Kap2.1-3

65

2.3.2 2NF und 3NF (11)

Ein Relationsschema, das nicht in 3NF ist, kann in mehrere Schemas in 3NF zerlegt werden

a) Fasse die transitiv abhängigen nicht primären Attribute mit den Attributen, von denen sie direkt abhängen, in einer eigenen Tabelle zusammen.

b) Entferne die ausgelagerten abhängigen Attribute aus der Ursprungstabelle.


plz# orthnr# name strasse plz

hst

hst plz

66

2.3.2 2NF und 3NF (12)

Verallgemeinerung Definition 2NF auf beliebige Anzahl Schlüsselkandidaten

Definition 2NF Codd (1971) Jedes Attribut, das zu keinem Schlüsselkandidaten gehört, ist von jedem Schlüsselkandidaten voll funktional abhängig.

Definition 2NF Kent (1973) Jedes Attribut im Komplement eines Schlüsselkandidaten ist von diesem Schlüsselkandidaten voll funktional abhängig.

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)

67

2.3.2 2NF und 3NF (13)

Beispiel:

isbn1 isbn2 isbn3 autor titel verlag sprache jahr

Schlüsselkandidat 2Schlüsselkandidat 1

Verlagscode

Sprachcode

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

68

2.3.2 2NF und 3NF (14)

Verallgemeinerung Definition 3NF auf beliebige Anzahl Schlüsselkandidaten

Definition 3NF Codd 2NF und kein Attribut, das zu keinem Schlüsselkandidaten gehört, ist von einem Schlüsselkandidaten transitiv abhängig.

Definition 3NF Kent 2NF und kein Attribut im Komplement eines Schlüsselkandi- daten ist von diesem Schlüsselkandidaten transitiv abhängig.

Bemerkungen:

Definitionen stimmen für nur einen Schlüsselkandidaten überein wieder ist die Codd-3NF schwächer

Dalitz DBS Kap2.1-3

69

2.3.2 2NF und 3NF (15)

Boyce/Codd Normalform (BCNF)

Für jede nicht triviale Abhängigkeit X → A ist X ein Superschlüssel der Relation.

Bemerkungen: 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

aber nicht in BCNFRelation in 3NF,

70

2.3.2 2NF und 3NF (16)

Relation R ist in BCNF, wenn für jede nicht triviale Abhäng- igkeit X → A die Menge X ein Superschlüssel von R ist.


plz# ort

hst1

hnr# name strasse plz

hst2 plz

fd5fd4

fd1

fd2fd3

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

71

2.3.3 4NF (1)

Mehrwertige Abhängigkeiten

Betrachte Relation mit mehreren unabhängigen mengenwertigen Attributen (d.h. nicht in 1NF!)

{Essen, Dortmund} Bekleidung}

{Lebensmittel,

{Dortmund, Hannover}

kette# standortbranche

Karstadt

Leffers {Bekleidung}

Ersetzen durch skalare Attribute führt zu Redundanzen, die keiner funktionalen Abhängigkeit entsprechen

72

2.3.3 4NF (2)

Schlechte Umformung in 1NF:

EssenDortmundEssenDortmund

KarstadtKarstadtKarstadtKarstadtLeffersLeffers

kette# standort#branche#

LebensmittelLebensmittelBekleidungBekleidungBekleidungBekleidung

DortmundHannover

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?)

Brauchen weiteres Kriterium für Redundanz: mehrwertige Abhängigkeiten

Dalitz DBS Kap2.1-3

73

2.3.3 4NF (3)

Definition:

Die Attributmenge Y ∈ R ist mehrwertig abhangig

von der Attributmenge X ∈ R, symbolisch X →→ Y ,wenn fur alle Tupel t1, t2 mit t1[X ] = t2[X ]Tupel t3, t4 existieren mit

• t3[X ] = t4[X ] = t1[X ] = t2[X ]

• t3[Y ] = t1[Y ] und t4[Y ] = t2[Y ]

• t3[Z] = t2[Z] und t4[Z] = t1[Z] wobei Z = R \ (X ∪ 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!! !!

74

2.3.3 4NF (4)

Zu t1, t2 mit t1[X ] = t2[X ] existieren t3, t4 mit

• t3[X ] = t4[X ] = t1[X ] = t2[X ]

• t3[Y ] = t1[Y ] und t4[Y ] = t2[Y ]

• t3[Z] = t2[Z] und t4[Z] = t1[Z] wobei Z = R \ (X ∪ Y )

Beispiel:

t1

t2



DortmundHannover



t3

t4

es gilt {kette} {standort} und {kette} {branche}!! !!

es gilt nicht {standort} {kette}!!

75

2.3.3 4NF (5)





DortmundHannover

Anschauliche Bedeutung von X Y | Z:!!

Das Ergebnis des Statements SELECT DISTINCT Y FROM R WHERE X = ’..’ AND Z = ’..’; ist für alle (vorhandenen) Werte von Z gleich

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

76

2.3.3 4NF (6)

Triviale mehrwertige Abhängigkeiten

wenn Y Teilmenge von X ist oder X ∪ Y = R, folgt automatisch X Y!!

sagt also nichts Wesentliches aus: "triviale" Abhängigkeit

Zusammenhang zu funktionaler Abhängigkeit

aus X → Y folgt X Y!!

Umkehrung gilt nicht

Dalitz DBS Kap2.1-3

77

2.3.3 4NF (7)

Theorem (Fagin 1977):

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.!!

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?)

78

2.3.3 4NF (8)

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:

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.

79

2.3.3 4NF (9)

Beispiel:





DortmundHannover

Relation ist nicht in 4NF, denn es gilt {kette} {standort},!! aber weder standort noch branche sind funktional abhängig von kette

Beispiel ist typisch: die meisten Relationen mit nichttrivialen mehrwertigen Abhängigkeiten enthalten nur Schlüsselattribute

80

2.3.3 4NF (10)

Umformung in 4NF:


Karstadt LebensmittelKarstadt BekleidungLeffers Bekleidung

kette# branche#

EssenDortmund

KarstadtKarstadtLeffersLeffers

DortmundHannover

kette# standort#



DortmundHannover


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

Dalitz DBS Kap2.1-3

81

2.3.3 4NF (11)

{Essen, Dortmund} Bekleidung}

{Lebensmittel,

{Dortmund, Hannover}

kette# standortbranche

Karstadt

Leffers {Bekleidung}

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

82

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

Dalitz DBS Kap2.4

1

2.4 Semantische Modelle

2.4.1 Der semantische Ansatz

Ebenen des Datenbankentwurfs (Wdh.):

konzeptionelle Ebene logische Gesamtsicht des Anwenders auf die Daten unabhängig vom eingesetzten DBS-Typ

Implementierungsebene konzeptionelle Datenstrukturen im Rahmen des eingesetzten DBS bei relationalem DBS z.B. Tabellen

physische Ebene konkrete Implementierung der Strukturen im Rahmen des eingesetzten DBS betrachtete Strukturen: Datenblöcke, Zeiger, Indexstrukturen

2

2.4.1 Semantischer Ansatz (2)

Objektrelato−nales Modell

SemantischesModell

RelationalesModell

HierarchischesModell

Implementierungsebene

konzeptionelle Ebene

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

3


Basiselemente Semantischer Datenmodelle:

Entity - unterscheidbares "Real World" Objekt Property - Eigenschaft, die ein Objekt beschreibt Relationship - Zusammenhang zwischen Entities

Semantische Modelle unterschieden sich

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 spezielle Diagrammsymbole und Linien (Entity-Relationship Modell) Darstellung als Funktionen (Funktionales Datenmodell)

4


Auch Relationales Modell hat diese Basiselemente: Entity: Relation Property: Attribut, Primary Key Constraint Relationship: Foreign Key Constraint

Semantik ist aber unzureichend dargestellt:

kundekdnr#nameadresse

kd_anspkdnr#apnr#nametelefon

kundekdnr#nameadresse

kd_anspkdnrapnr#nametelefon

Schema 1 Schema 2

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

Dalitz DBS Kap2.4

5


Vorteile semantischer Modelle

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"

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

6

2.4.2 ER Modell (1)

Das verbreitetste semantische Modell ist das Entity-Relationship Modell (ER Modell):

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

7

2.4.2 ER Modell (2)

Entity

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

strongEntity

weakEntity

8

2.4.2 ER Modell (3)

Beispiel

Ansprech−partnerKunde

angestellt

bei

Ansprechpartner ist kein unabhängiges Objekt existiert nur, wenn entsprechender Kunde auch existiert

Bemerkungen:

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

Dalitz DBS Kap2.4

9

2.4.2 ER Modell (4)

Property (Attribut)

atomar, zusammengesetzt 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

mehrw.Attribut

AttributKeyFeld1

Feld2

Feld3Struktur

Entity

10

2.4.2 ER Modell (5)

Beispiel

hersteller

name adresse

strasse ort

hnr branche

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

2.4.2 ER Modell (6)

Relationship

stellt Zusammenhang zwischen Entities her Darstellung durch Raute mit Linien zu beteiligten Entities Anzahl beteiligter Entities heißt Grad der Relationship

R1(Grad 2)

E3

E1R2

(Grad 3)E2

E4

12

2.4.2 ER Modell (7)

Relationship kann auch reflexiv sein, d.h. Objekte derselben Entity miteinander verknüpfen:

Mitarbeiter Vorgesetztervon

Relationship zwischen strong und weak Entity durch doppelten Rahmen gekennzeichnet:

bei

angestelltKunde Ansprechpartner

Dalitz DBS Kap2.4

13

2.4.2 ER Modell (8)

Kardinalität einer Relationship

gibt an wieviele Elemente derselben Entity an Relationship beteiligt sein können durch Zahlen an Verbindungslinien angegeben

Beispiel:

Produkt

1 nstelltHersteller her

1:n Beziehung zwischen Hersteller:Produkt ein Hersteller stellt mehrere (n) Produkte her ein Produkt hat einen (1) Hersteller

14

2.4.2 ER Modell (9)

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

Produkt

stellther

Lieferung

ausge−liefert

Hersteller1

n

0,m

n

15

2.4.2 ER Modell (10)

Relationships können auch Attribute haben

Kunde

Produkt

beliefert

Lieferant

k

n m

Rabatt

Verwischt Grenze zwischen Entity und Relationship Chen spricht von "Relationship Relation"

16


Komplettes Beispiel:

beliefert

name

adresse

nr nr

preis

plz

adresse

plz

strasse

ort

ortstrasse

name

branche

nr

name

ange−stellt

telefon name

Ansprechpartner

k

n m

Rabatt

Lieferant Produkt

Kunde

0,n

1

nr

Dalitz DBS Kap2.4

17


Zusammenfassung ER-Notationen:

Schlüssel−

attributR

E1

E2

0,n

1

Relationshipmit Kardinalitäten

Zuordnungstrong/weak Entity

partieller Schlüsseleiner weak Entity

mehrwertigesAttribut

strong Entity

Attribut

Relationship

weak Entity

18

2.4.3 ER -> Relational (1)

Umwandlung ER in Relationales Schema

Allgemeines Vorgehen

jede Entity wird Relation Attribute und Primary Key (PK) werden übernommen

jede Relationship wird Relation Primary Key = alle PK’s beteiligter Entities

Feinheiten 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

19


Strong Entities

zusammengesetzte und mehrwertige Attribute können schon auf der ER-Ebene nach dem Muster der Normalisierung (siehe erste Normalform) umgeformt werden

hersteller

hnrname

hersteller

namehnr

branche

branchename

nr

branchehst−

adresse

strasseort

ort

strasse

0,m

0,n

20


Weak Entities (1)

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

Kunde

ange−stellt

Ansprechpartner

name telefon

name strasse

ortnr

kundenr#nr#nametelefon

ansprechpartner

nr#namestrasseort

kunde

0,n

1

nr

Dalitz DBS Kap2.4

21


Weak Entities (2)

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 ( CREATE TABLE ansprechpartner ( nr INT8, kundenr INT8 REFERENCES kunde(nr) name VARCHAR(30), ON DELETE CASCADE ON UPDATE CASCADE, strasse VARCHAR(30), nr INT, ort VARCHAR(30), name VARCHAR(30), PRIMARY KEY (nr) 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

22


Many-To-Many Relationship

n:m Relationships werden eine eigene Relation Primary Key = PK’s aller beteiligten Entities

bezieht

Kunde

Produkt

nr name

nr name

kundenr# produktnr# ...

produktnr# name

kundenr# name

m

n

...kunde_produkt

Foreign Key Constraints mit on update cascade Option

23


One-To-Many Relationship (1)

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?)

Produkt

name

nr name

Hersteller

n

stellt her

1

herstellernr# name

produktnr# name

herstellernr produktnr#

hersteller_produkt

nr

24


One-To-Many Relationship (2)

Beobachtung: separate Relation hersteller_produkt unnötig Zusammenlegung mit produkt ergibt:

herstellernr# name

produktnr# name

herstellernr# name

herstellernr produktnr#

hersteller_produkt

nr# name herstellernr produkt

Vorteile: weniger Relationen klarere Semantik: Hersteller Eigenschaft von Produkt Aber: wenn oft kein Hersteller bekannt, vermeidet linke Lösung NULL-Werte in Foreign Key Feld

Dalitz DBS Kap2.4

25


One-To-One Relationship

auch bei 1:1 Relationship keine eigene Relation nötig PK einer Entity als Foreign Key in andere Entity aufnehmen

nr name

nr name

Abteilung

personalnr# name

nr# name chefnr abteilung

nr# name abteilung

nr# name chefvon personal

1

Personal

Chef von

0,1

obere Lösung ist besser (Warum?) Regel: erweitere Tabelle am "0-Ende"

26


Relationship höheren Grades

eigene Relation PK beteiligter Relationen als Foreign Keys PK = PK’s beteiligter Relationen mit Kardinalität > 1

beliefert

Rabatt

ProduktLieferant

Kunde

n m

k

lfnr# pdnr# kdnr# rabatt

lieferant produkt

kunde

belieferung

27


Zusammenfassung

ER Modell Relationales Modell

Entity Relation

einfaches Attribut Attribut

zusammengesetztes Attribut mehrere Attribute

Schlusselattribut Primary Key

mehrwertiges Attribut Relation mit Fremdschlussel

1:1 oder 1:N Relationship Fremdschlussel in Relation auf

Seite der hoheren Kardinalitat

(Alternative: separate Relation)

N:M Relationship Relation mit zwei Fremdschlusseln

Relationship n-ten Grades Relation mit n Fremdschlusseln

28

2.4.4 Entwurfsfragen (1)

Anforderungen an Datenmodell

a) vollständig b) minimal bzw. redundanzfrei c) einfach und verständlich

Damit zusammenhängende Aspekte

Namenskonventionen Auswahl des Elements (Entity, Attribut, Relationship) Mehrwert (?) von ER versus Relational allgemeines Vorgehen (Top-Down, Bottom-Up)

Dalitz DBS Kap2.4

29


Namenskonvention

Namen von Objekten des Modells sollen Bedeutung entsprechen => leichter verständlich

Konventionen erleichtern Verwendung (leichter merkbar, Fremdschlüssel erkennbar)

Beipielkonventionen (1)

Enitäten konsistent im Singular oder Plural. Beides sinnvoll:

select * from kunden; select kunde.nr,kunde.name from kunde ...;

übliche Konvention: Singular

Name von Key und Bedeutung einheitlich, z.B. nr und name

30


Beispielkonventionen (2)

Name Fremdschlüsselattribut = referenzierte Tabelle + PK

nr#namebranchenr

hersteller

nr#name

branche

Name Relationship-Relation zusammengesetzt aus Namen der beteiligten Entities; ebenso bei weak Entities

Kunde

Branche

m

n

1nAnspr.

branche

kunde_ansp

kunde

branche_kunde

31


Wahl des Basiselements

Oft gibt es mehrere Möglichkeiten, einen Sachverhalt zu representieren

a) Attribut versus Entity und Relationship b) Relationship versus Entity

Alternative a) ist echte Designfrage mit Auswirkungen auf Dateneingabe und Datenkonsistenz.

Alternative b) ist künstliches Problem im ER Modell 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

32


Attribut versus Entity + Relationship (1)

Betrachte Eigenschaft "Branche" eines Herstellers

Lösung 1:

nr#name

hersteller

herstellernr

name branche

hstnr#branche#

ER Modell Relationales Modell

hst_branche

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

Dalitz DBS Kap2.4

33



Lösung 2:

hersteller

namenr

branche

les Modell

Relationa−

nr

name

nr# name hersteller

herstellernr# branchenr# hst_branche

nr# name branche

ER Modell

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

34



Alternative besteht nicht nur bei mehrwertigen Attributen, sondern bei allen Attributen

Verwende Referenztabelle, wenn Attributwerte nicht beliebig sind, sondern aus (konfigurierbarer!) Werteliste kommen sollen Unterschied zu Domain-Constraint (Check-Constraint): Werteliste änderbar ohne Schemaänderung

Versicherung

art

zahlweisenr

klauselart und klausel müssen überReferenztabelle modelliert werden

zahlweise kann über Constraintmodelliert werden:

für BerechnungenWerte tragen Bedeutung

Werte 1,2,4,6,12 sind fest

35


Entity versus Relationship (1)

Betrachte Modellierung eines Schachturniers

Relationales Modell ist offensichtlich

spieler

nr#nameelozahl

partie

runde#weissnr#schwarznr#ergebnis

ER Modell ist weniger offensichtlich Ursache: Foreign Keys kennt das ER Modell nicht tauchen nur implizit auf bei Relationship oder weak Entity => zwei Modellierungsalternativen

36



Lösung 1:

trifftaufSpielername

elozahl

nr

ergebnis

runden

m

Partien modelliert als Relationship zwischen zwei Spielern Probleme: 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!

Dalitz DBS Kap2.4

37



Lösung 2:

name

elozahl

nr

Spieler

weiß

Partie

schwarz

ergebnis

runden

1 n

1

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

38



Interessanterweise führen beide ER-Lösungen zu demselben Relationalen Modell

spieler

nr#nameelozahl

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

39


allgemeines Vorgehen

Top-Down

starte mit grobem Entwurf, der zunehmend verfeinert wird (z.B. durch Einführung von Referenztabellen)

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"

Erleichtert Verständnis des Systems, da zunächst aus der "Vogelperspektive" modelliert wird

40


In Praxis liefert Anforderungsanalyse meist vor allem Detailwissen => zunächst Bottom-Up Analyse nötig vor Top-Down Design

Anforderungs−analyse

Gesamt−überblick

Datenmodell

Bottom−Up

Abgleich Ergebnis

Top−Down

DesignAnalyse

Dalitz DBS Kap2.4

41


Mögliches Top-Down Vorgehen:

Modellieren der wesentlichen Entities und Relationships (zwecks besseren Überblicks noch keine Attribute)

Ergänzen der Schlüsselattribute

Modellieren aller Entity-Eigenschaften als (ggf. mehrwertige) Attribute

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?)

42

2.4.5 Vererbung (1)

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

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

C

B

A is−a

43

2.4.5 Vererbung (2)

Beispiel

Fahrzeug

PKW LKW Motorrad

is−a

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

44

2.4.5 Vererbung (3)

Vergleich mit Vererbung in C++

Übereinstimmung 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

Dalitz DBS Kap2.4

45

2.4.5 Vererbung (4)

Spezialisierung einer Superklasse in mehrere Subklassen hat zwei verschiedene Eigenschaften:

disjunkt oder überlappend kann ein Element der Superklasse in höchstens einer Subklasse sein, ist die Spezialisierung disjunkt

total oder partiell muss ein Element der Superklasse in mindestens einer Subklasse sein, ist die Spezialisierung total

Da beide Eigenschaften voneinander unabhängig sind, ergeben sich vier verschiedene Kombinationen

46

2.4.5 Vererbung (5)

Mann Frau

Mensch

Gitarre

Tier

Lebewesen

Fahrzeug

disjunkttotal

disjunktpartiell

überlappendtotal

überlappendpartiell

Mensch

PKW LKW

akustisch elektrisch

47

2.4.5 Vererbung (6)

Umwandlung in Relationales Modell

Betrachte Spezialisierung der Superklasse

A(k, a1, . . . , an) (k ist der Schlussel von A)

mit m Subklassen B1, . . . , Bm

Aan

a1 ...

...1B mB

k

Es gibt vier verschiedene Umwandlungsoptionen

48

2.4.5 Vererbung (7)

Option 1:

Erstelle fur A die Relation A(k#, a1, . . . , an) und fur jede Subklasse

Bi eine Relation mit den Attributen {k#} ∪ {Attribute von Bi}.

1B

b11 b1k1 bm1 bmkm

mB

k#k# ... ......

ank# a1 ... A

erzeugt viele Relationen geeignet für disjunkt und überlappend geeignet für total und partiell

Dalitz DBS Kap2.4

49

2.4.5 Vererbung (8)

Option 2:

Erstelle fur jede Subklasse Bi eine Relation mit den

Attributen {k#, a1, . . . , an} ∪ {Attribute von Bi}.

b11 b1k1 1Bank# a1 ...

bm1 bmkm mB...k# a1 ... an

...

...

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

50

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 Subklassenzugehorigkeit angibt.

b11 b1k1 bm1 bmkmank# a1 ... ... ...... A

Typ−Attribut

t

erzeugt nur eine einzige Relation nur geeignet für disjunkte Spezialisierung (Warum?) erzeugt ggf. zahlreiche Nullwerte (Welche?)

51

2.4.5 Vererbung (10)

Option 4:

Erstelle eine Relation A mit den Attributen von A, den Attri-buten aller Subklassen und m boolschen Attributen t1, . . . , tm,die Flags fur Subklassenzugehorigkeiten sind.

b11 b1k1 bm1 bmkm... ......k# a1 ... an ... tmt1

Flags für Subklassenzugehörigkeit

A

erzeugt nur eine einzige Relation geeignet für überlappende Spezialisierung wie bei Option 3 ggf. zahlreiche Nullwerte

Dalitz DBS Kap3.1-3

1

3.1 weitere DB-Objekte

wichtige fortgeschrittene Datenbankobjekte:

Sequence

generiert eindeutige Werte nicht in SQL2 spezifiziert, aber von fast allen DBS unterstützt, wobei Syntax der Verwendung variiert

Schema

View

Namespaces zum Trennen von Usern/Anwendungen in SQL2 gefordert, aber ungenau spezifiziert => DBS-spezifische Unterschiede im Detail

Select-Statement als Tabelle in SQL2 spezifiziert; wesentlicher Bestandteil aller relationalen Datenbanksysteme

2

3.1.1 Sequence (1)

Beschreibung

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

Anwendungsgebiete

automatische Generierung Primärschlüsselwerte

Erzeugung eindeutiger Namen für temporäre Tabellen (oft besser: Verwendung von create local temporary table)

3

3.1.1 Sequence (2)

Naiver Ansatz für Primärschlüsselerzeugung (select max(nr) + 1) ist problematisch:

selectmax(nr)+1

selectmax(nr)+1

TabelleClient 1 Client 2

1022

insert

1022

insert

Fehler

bearbeiten

bearbeiten

4

3.1.1 Sequence (3)

Verwendung Sequence zur Schlüsselgenerierung:

Anlegen der Sequenz

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) );

Bemerkung: PostgreSQL Datentyp serial macht das automatisch

Dalitz DBS Kap3.1-3

5

3.1.2 Schema (1)

Hierarchieebenen einer DBS-Instanz:

Database Cluster

Group

User

Database

Schema

TabelleViewConstraintIndex...

ClusterDatabase

Database

Schema

Constraint

View

TabelleIndex

Sequence

User

Group

6

3.1.2 Schema (2)

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)

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

3.1.2 Schema (3)

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

Wozu braucht man Schemas?

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

8

3.1.2 Schema (4)

Benutzung von Schemas

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

Schema Suchpfad unqualifizierte Tabellennamen werden im Schema Suchpfad gesucht wie Suchpfad gesetzt wird ist systemspezifisch typischer Defaultwert: username, public

Dalitz DBS Kap3.1-3

9

3.1.2 Schema (5)

Typische Konfiguration:

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

Emulation schemalose Datenbank:

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)

10

3.1.3 View (1)

Was ist ein View?

"virtuelle Tabelle", deren Inhalt dynamisch über relationalen Algebra Ausdruck berechnet wird

im relationalen Modell als abgeleitete Relation bezeichnet, im Gegensatz zu Basisrelation (Tabelle)

verhält sich aus Anwendersicht wie Tabelle: Abfrage mit select explizite Rechtevergabe mit grant/revoke Aber: Änderung (insert, update, delete) im allg. nicht möglich

11

3.1.3 View (2)

Definition eines Views

pnr# name preis einfuehrung auslauf

pnr# name preis einfuehrung

produkt

produkt_aktuell

π...(σ...( produkt))

View, der nur aktuelle Produkte enthält:

CREATE VIEW produkt_aktuell AS SELECT pnr,name,preis,einfuehrung FROM produkt WHERE auslauf > current_date OR auslauf IS NULL;

12

3.1.3 View (3)

Angabe der Attributnamen im View

implizit über Liste selektierter Attribute:

CREATE VIEW produkt_aktuell AS SELECT pnr, name AS produkt, ... FROM produkt WHERE ...

explizite Angabe hinter View-Namen:

CREATE VIEW produkt_aktuell (pnr, produkt, ...) AS SELECT pnr, name, ... FROM produkt WHERE ...

Dalitz DBS Kap3.1-3

13

3.1.3 View (4)

Abfragen über Views

führen zu Abfragebaum (s. 2.5.2) mit Views an Blättern

ersetze Views durch die Abfragebäume, die in ihrer Definition hinterlegt sind => Abfragebaum hat nur Tabellen an Blättern

AbfrageBaum

S T

VR R

14

3.1.3 View (5)

Wozu sind Views gut?

Kapselung komplexer Queries Anwender braucht Abfrage nicht zu kennen Abfrage kann geändert werden, ohne Applikation anzupassen evtl. bessere Performance ("materialized Views")

Einschränkung von Zugriffsrechten normalerweise Rechte über Zugriffsfrontend gesteuert wird kein anwendungsspezifisches Frontend verwendet (z.B. DB-Frontends aus Office-Paketen), trotzdem Rechte- beschränkung mit Views möglich

Vermeidung Redundanzen abgeleitete Attribute können dynamisch berechnet werden

15

3.1.3 View (6)

Rechtebeschränkung mit Views

Problem:

Anwender benutzt Abfrage-Frontend, das keine Rechtebe- schränkung ermöglicht (z.B. MS Access, SQL-Prompt)

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

16

3.1.3 View (7)

Beispiel: System-Catalog, in dem jeder nur seine eigenen

Tabellen sieht

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: current_user ist die SQL2-Funktion für die aktuelle Benutzerkennung obwohl alle auf denselben View zugreifen, sieht jeder User andere Daten

Dalitz DBS Kap3.1-3

17

3.1.3 View (8)

Data Dictionary Oracle:

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 Purpose

* tables Shows all relational tables* tab columns Shows all table and view columns* sequences Lists all sequences in the database* indexes Lists all indexes* ind columns Lists all indexed columns* users Lists all users* role privs Lists all roles granted to users and other roles

18

3.1.3 View (9)

Redundanzvermeidung mit Views

c2k1# a1 a2 c1 b1

a1# a2# b1

= { b1 }B

= { a1, a2 }AT

Ta

k1# a1 a2 c1 c2

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

19

3.1.3 View (10)

Bessere Lösung bei berechenbarer Abhängigkeit f:

definiere View Vb mithilfe Berechnungsregel von f, der Primärschlüssel von T und berechnete Werte enthält

c2k1# a1 a2 c1 b1T

Ta

k1# a1 a2 c1 c2 k1#

Vb

(a1, a2)

b1 = (a1, a2)

f

f

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)

20

3.1.3 View (11)

Beispiel:

001 Buch A 1 52.807.0 01.12.200249.35

lnr# produkt menge netto brutto datummwst

003 Software 1 43.40 01.08.200238.90 16.0002 Buch B 1 7.0 05.01.2003116.94 125.13

Tabelle lieferung enthält Redundanzen wegen

netto ∗

100 + mwst

100= brutto

Abspalten berechenbares Attribut mittels View:

lnr, netto*(1+mwst/100) FROM lieferung

SELECTlnr#produkt

nettomenge

mwstdatum

Tabelle lieferung View lieferung_brutto

Dalitz DBS Kap3.1-3

21

3.1.3 View (12)

Weitergehende Möglichkeiten:

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?)

22

3.1.3 View (13)

Änderungen auf Views

Anforderungen:

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

Anforderungen im allgemeinen nicht alle erfüllbar Untersuche Bedingungen für Erfüllbarkeit

23

3.1.3 View (14)

Projektionsviews

pnr# name preis einfuehrung auslauf produkt

π ... Probleme

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 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

24

3.1.3 View (15)

Selektionsviews (1)

σ preis < 5.0 ( )produktbilligprodukt :=

pnr# name preis einfuehrung auslauf produkt

Probleme

Änderung kann ausgeblendeten Teil betreffen

DELETE FROM produkt WHERE preis > ’2.0’;

Minimalitätsprinzip: keine Auswirkung auf unsichtbare Tupel

Verschieben von sichtbar zu unsichtbar

UPDATE produkt SET preis = ’8.5’ ...

kann in SQL2 mit with check option unterdrückt werden

Dalitz DBS Kap3.1-3

25

3.1.3 View (16)

Selektionsviews (2)

Betrachte Viewdefinition über Subquery:

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?

Problem: where-Klausel wird durch Änderung mitverändert

Views, die Subqueries mit Selbstbezug enthalten, sind daher in SQL2 nicht änderbar

26

3.1.3 View (17)

Verbundviews (Joins)

pnr# produkt preis hnrhnr# hersteller stadt

hersteller_produkt produkthersteller:=

hersteller produkt

Probleme

Ä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

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

27

3.1.3 View (18)

Ansätze für änderbare Views:

automatisch änderbare Views

selbstdefinierbare Regeln für Änderungen

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)

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

28

3.1.3 View (19)

Änderbare Views in SQL2

SQL2 unterscheidet nicht zwischen insert, update und delete, sondern spricht allgemein von "updatable Views"

ein "updatable View" ist ein select [all] (kein select distinct) auf genau eine Basistabelle, mit folgenden Zusatzbedingungen:

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)

create view bietet Parameter with check option, mit dem eine "Tupelmigration" in unsichtbaren Bereich der Basistabelle verhindert werden kann

Dalitz DBS Kap3.1-3

29

3.1.4 Rule (1)

Nachteile SQL2 Lösung:

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

allgemeinere Lösung mit Rules:

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 PostgreSQL- spezifische Erweiterung

30

3.1.4 Rule (2)

Anlegen einer Rule:

CREATE RULE rule_name AS ON event TO object [WHERE rule_qualification] DO [INSTEAD] [action | (actions) | NOTHING];

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;

31

3.1.4 Rule (3)

Anwendung auf Löschproblem Verbundview:

pnr# produkt preis hnrhnr# hersteller stadt

hersteller_produkt produkthersteller:=

hersteller 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;

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

32

3.1.4 Rule (4)

Wozu sind Select-Rules gut?

Protokollierung von Zugriffen

problematisch bzgl. Performance

besser: Logfile-Auswertung (ggf. Auditing Tool verwenden)

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;

Dalitz DBS Kap3.1-3

33

3.1.4 Rule (5)

Rules sind spezieller experimenteller Ansatz, der in den meisten DBS-Lehrbüchern nicht behandelt wird

Originale Forschungsliteratur zum Thema "Rules":

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

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

34

3.2 Serverprogrammierung (1)

Datenbankzugriff aus Clientprogramm:

SQL

beitungVerar− SQL

beitungVerar−

DB−Server

Programm

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)

35


Idee serverseitige Programmierung: 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

proc()

DBMSDB

DBS

SELECT

...FROM

proc()

Anwendg.

stored Procedure

Vorteile 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

36


Komponenten serverseitiger Programmierung

stored Procedures 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)

prozedurale SQL-Erweiterungen ermöglichen direkte Erzeugung von Prozeduren mit SQL Quasistandard: PL/SQL von Oracle

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

Dalitz DBS Kap3.1-3

37


"Standards" der serverseitigen Programmierung

PL/SQL prozedurale SQL-Erweiterung von Oracle vollwertige moderne prozedurale Programmiersprache (Funktionen mit Defaultargs und Überladen, Exceptions,...) Vorbild für PL/pgSQL von Postgres

PSM Persistent Stored Modules (PSM) 1996 in ANSI SQL-Standard aufgenommen; Bestandteil von SQL3 (1999) spezifiziert drei Aspekte Definition und Aufruf von Prozeduren und Funktionen Zusammenfassen von Funktionen zu Modulen prozedurale SQL-Erweiterung

wegen zu PL/SQL inkompatibler Syntax (noch?) kaum umgesetzt

38

3.2.1 Stored Procedures (1)

Definition und Aufruf

SQL3 unterscheidet zwischen Prozedur und Funktion

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)

39


Beispiel:

DROP FUNCTION bruttofunc(NUMERIC);

-- berechne Bruttobetrag incl. Mwst CREATE FUNCTION bruttofunc(numeric) RETURNS numeric AS ’ SELECT $1 * CAST(1.16 AS numeric); ’ LANGUAGE ’SQL’;

Bemerkungen: Ü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)

40


Funktionen können nicht nur zum Berechnen, sondern auch für Operationen verwendet werden:

-- 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’;

Bemerkung

letztes select nötig, da Funktion Wert zurückgeben muss könnte aber auch durch triviales select ersetzt werden, z.B. select ’1’;

Dalitz DBS Kap3.1-3

41


PostgreSQL unterstützt auch C-Funktionen

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

Nachteile: Zugriff auf Betriebssystem erforderlich im allgemeinen nur durch DBA möglich plattformabhängig

DBS kann nicht optimieren

42

3.2.2 prozedurales SQL (1)

SQL nur begrenzte Möglichkeiten (relationale Algebra)

=> für "inline" Definition von Funktionen ist prozedurale SQL-Erweiterung nötig

Ansätze

feste Implementierung von PL/SQL im DBS von Oracle gewählte Lösung PL/SQL immer verfügbar (auch außerhalb von Funktionen!)

Framework zum Bereitstellen von Sprachen von PostgreSQL gewählte Lösung Sprachen müssen separat ins DBS eingebunden werden Sprache in Funktionsdefinition angeben beliebig erweiterbarer Ansatz

43


Sprachen-Framework in PostgreSQL

source

... pl

sprache

userfunc

name

stored Procedures

name handler

Sprachen

pl_handlerplpl_handler plmodul.so C

DBS

plmodul.so

44


Installation Sprache in PostgreSQL (durch DBA)

a) Compilieren und Bereitstellen Objectfile für den Language Handler (ggf. schon vorinstalliert, z.B. bei plpgsql)

b) Deklaration Handler Funktion CREATE FUNCTION handler_function_name() RETURNS LANGUAGE_HANDLER AS ’path-to-shared-object’ LANGUAGE C;

b) Deklaration der Sprache CREATE [TRUSTED] LANGUAGE language-name HANDLER handler_function_name;

Hinweise: 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

Dalitz DBS Kap3.1-3

45


Sicherheitsaspekt

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

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

46


PL/SQL und PL/pgSQL

allgemeine Struktur ist blockorientiert:

[ DECLARE declarations ] BEGIN statements END;

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


Beispielfunktion in SQL:

-- berechne Bruttobetrag incl. Mwst CREATE FUNCTION bruttofunc(numeric) RETURNS numeric AS ’ SELECT $1 * CAST(1.16 AS numeric); ’ LANGUAGE ’SQL’;

Dieselbe Funktion in PL/pgSQL:

CREATE FUNCTION bruttofunc(numeric) RETURNS numeric AS ’ DECLARE res numeric; BEGIN res := $1 * CAST(1.16 AS numeric); RETURN res; END; ’ LANGUAGE ’plpgsql’;

48


Mögliche Deklarationen:

-- normaler SQL-Datentyp name VARCHAR(30);

-- Vorbelegung jedesmal, wenn Block aufgerufen menge INT DEFAULT 0; /*oder: menge INT := 0;*/

-- Datentyp von Tabellenattribut übernehmen preis produkt.preis%TYPE;

-- Aliasname für Funktionsparameter arg1 ALIAS FOR $1;

-- zusammengesetzter Datentyp (Tabellentupel) prod produkt%ROWTYPE;

-- Platzhalter für SELECT-Ergebnis -- (d.h. ROWTYPE mit beliebiger Struktur) rec RECORD;

Dalitz DBS Kap3.1-3

49


Kontrollstrukturen:

Verzweigungen if - then - [else -] end if;

Loops sowohl while- als auch for-Loops:

WHILE bedingung LOOP FOR var IN start .. ende LOOP anweisungen anweisungen END LOOP; END LOOP;

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;

50


SQL-Statements

update, insert, delete direkt formulierbar select ist komplizierter: Was ist, wenn mehr als ein Tupel zurückliefert wird? Wie wird erkannt, ob überhaupt Ergebnis gefunden?

Single-Row Select

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

51


Multiple-Row Select

einfache Variante (nur Postgres): Select in For-Loop DECLARE rec RECORD; BEGIN FOR rec IN SELECT * FROM produkt LOOP /* Verarbeitung */ END LOOP; END;

komplizierte Variante (Postgres und Oracle): Cursor 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;

52


Überprüfung ob Select Ergebnis lieferte:

Postgres Abfragen globale boolesche Variable found

Oracle wenn select into nichts liefert, wird Exception vom Typ no_data_found geworfen bei fetch into Cursorattribut %NOTFOUND abfragen

Dynamische Statements 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)

Dalitz DBS Kap3.1-3

53


Fehlerbehandlung

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

Oracle PL/SQL Blöcke haben zusätzlichen exception Abschnitt, in dem auf Exceptions je nach Typ verschieden reagiert werden kann

54



14.0016.00

01.01.199001.04.1997

12

PrittUhu

01.12.199201.12.1999

50.1282.50

Lieferung

mwst gueltigab#

Mwst

lnr# produkt datumnetto

Funktion zur Mehrwertsteuerberechnung

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

55


Postgres ermöglicht auch Aggregatfunktionen

xi

1x

i+

xn

1x

yi

1y

i+

yn

1y

xi

yi

sfunc ( , )

yn+1

yn+1

( )finalfunc

ZustandsvariableAttributwerte

Aggregatwert

erfordert Definition zweier Hilfsfunktionen Übergangsfunktion sfunc verarbeitet einzelne Attributwerte Kommunikation über Zustandsvariable (state variable) (optionale) Abschlussfunktion berechnet aus Zustandsvariable den Aggregatwert

eigentliche Aggregatdefinition verweist dann auf die zwei Hilfsfunktionen und legt Startwert für Zustandsvariable fest

56

3.2.3 Trigger (1)

Was ist ein Trigger?

Trigger verknüpfen ein Ereignis in einer Tabelle mit bestimmten Aktionen 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

Trigger können von Anwendern nicht direkt angestossen werden (nur indirekt durch Ereignis)

von meisten DBS unterstützt und in SQL3 enthalten, aber zahlreiche Unterschiede im Detail

Dalitz DBS Kap3.1-3

57

3.2.3 Trigger (2)

Wozu sind Trigger gut?

Automatisierung Ablauflogik 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

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

Wichtig: nur dosiert und mit Bedacht einsetzen!

58

3.2.3 Trigger (3)

Problematische Eigenschaften von Triggern

Strukturierung es fehlen z.Zt. Abstraktionsmechanismen um Trigger zu logischen Einheiten zusammenzufassen

Terminierung 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

59

3.2.3 Trigger (4)

Anlegen eines Triggers

CREATE TRIGGER trigger [ BEFORE | AFTER ] event ON relation FOR EACH [ ROW | STATEMENT ] EXECUTE PROCEDURE procedure();

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: 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

60

3.2.3 Trigger (5)

Die Triggerfunktion

CREATE FUNCTION triggerfunc() RETURNS TRIGGER AS ’ ... ’ LANGUAGE ’plpgsql’;

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:

Variable Typ Bedeutung

OLD RECORD Datensatz vor Ausfuhrung EreignisNEW RECORD Datensatz nach Ausfuhrung EreignisTG_NAME NAME Name auslosender TriggerTG_RELNAME NAME Name auslosende TabelleTG_OP TEXT Art auslosendes Ereignis (insert,...)

Dalitz DBS Kap3.1-3

61

3.2.3 Trigger (6)

Beispiel: Protokollierung letztes Update

Tabelle habe Attribut lastchange für Zeitpunkt letztes Update

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();

62

3.2.3 Trigger (7)

Trigger für Integrity Constraints

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

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

63

3.2.3 Trigger (8)


12

K001U003

50.1282.50

netto70207030

01.12.200029.02.2002

datum15.01.2001

Jahresabschluss

lnr# artnr kst datum

Kosten

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

Siehe plsqldemo.tar.gz auf Homepage

64

3.2.3 Trigger (9)

Auch Foreign Key Constraints können über Trigger realisiert werden

Wieviele und was für Trigger sind z.B. für die folgende Foreign Key Constraint erforderlich?

pnr#namehnrpreis

Produkt

hnr#name

Hersteller on update

cascade

Tatsächlich realisiert Postgres Foreign Key Constraints intern mit Triggern

Dalitz DBS Kap3.1-3

65

3.2.3 Trigger (10)

Trigger zur Berechnung redundanter Werte

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

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

66

3.3 DB-Tuning (1)

Ziele Datenmodellierung:

einfache und klare Semantik Redundanzfreiheit

Ziele Datenbanktuning:

Beschleunigung von Abfragen hoher Importdurchsatz Vermeidung Verklemmungen (Deadlocks)

Beide verfolgen also orthogonale Ziele, beeinflussen sich aber teilweise => ggf. im Einzelfall abwägen

67

3.3 DB-Tuning (2)

Ansätze zum Datenbanktuning

Konfiguration Nutzung OS-Resourcen *)

Indizes

Abfragemodifikation

Denormalisierung *)

Transaktionsablauf

Vermeidung Client-Server Pingpong

*) untersuchen wir im Folgenden nicht näher

68

3.3 DB-Tuning (3)

Was ist ein Index?

Datenstruktur, die direkten Zugriff auf Tupel anhand eines Attributwerts ermöglicht (im Ggs. zu sequentiellem Scan) Index wird vom DBS getrennt von Tabelle gespeichert

Was bewirkt ein Index?

deutliche Beschleunigung Suche über Attributwert (aber nicht immer: oft auch sequentieller Scan schneller) Beschleunigung Sortierung und Join über Attributwert Verlangsamung Änderungen an Tabelle (Warum?)

Sorgfältige Indexwahl wichtigstes Tuningmittel Fehlender Index häufigstes Performanceproblem

Dalitz DBS Kap3.1-3

69

3.3 DB-Tuning (4)

Indextypen

normaler Attributindex Anlage mit create [unique] index indname on tblname (att1, ...) Achtung: bei Multicolumn Index beschleunigter Zugriff über alle Attribute gemeinsam oder erstes Attribut

Bitmap Index erheblich schneller auf Attributen mit wenigen Werten

partieller Index Ausschließen bestimmter Tupel aus Index

funktionaler Index Index auf berechnete Werte, z.B. create index ... on lower(att) insbesondere nötig bei Caseinsensitiver Suche

70

3.3 DB-Tuning (5)

Nutzung von Indizes durch DBS

DBS benutzt existierende Indizes nicht unbedingt

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)

meisten DBS bieten SQL Kommando zur Abfrage Query Plan Beispiel: Ausgabe des Postgres SQL-Befehls explain

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)

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)

71

3.3 DB-Tuning (6)

Gründe für Nichtnutzung von Indizes:

falsche Statistikinfos zu Tabellen 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

Verwendung von Funktionen, Mehrfachindex, pattern matching Operator (like, similar)

Type Mismatch zwischen Feldern

Query Optimizer schwach bei bestimmten Queries bei Joins ggf. explizites join on ausprobieren verschiedene Varianten desselben Statements testen

72

3.3 DB-Tuning (7)

Beispiel für äquivalente Abfragen (vgl. Übungen)

SELECT * FROM person WHERE pnr IN (SELECT regie FROM film);

SELECT * FROM person WHERE EXISTS (SELECT ’ ’ FROM film WHERE regie = person.pnr);

SELECT DISTINCT p.* FROM person p, film f WHERE f.regie = p.pnr;

SELECT DISTINCT p.* FROM person p INNER JOIN film f ON f.regie = p.pnr;

Dalitz DBS Kap3.1-3

73

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

74

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

Documents

1 Grundlagen Datenbanksysteme - HS Niederrheindalitz/data/... · 1.4 SQL Einführung (3) SQL est omnis divisa in partes tres: DDL - Data Definition Language Definieren und Ändern