Datenbanksysteme 1 - DVS Public Files

Data & Knowledge Engineering
Prof. Alejandro Buchmann
Prof. Johannes Fürnkranz
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 1
Allgemeines/Organisatorisches
 Prof. Alejandro Buchmann
(Data Engineering)
 Prof. Johannes Fürnkranz
(Knowledge Engineering)
 Sprechstunde: nach Vereinb.
 Sprechstunde: Mi 10:00-11:00
 buchmann@informatik...
 fuernkranz@informatik...
 Sekretariat: Frau Tiedemann
 Sekretariat: Frau Ploch
 tiedem@dvs.tu-darmstadt.de
 gploch@ke.informatik...
http://www.dvs.tu-darmstadt.de/teaching/dke/
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 2
Allgemeines/Organisatorisches
 Vorlesung
 Übung
 Dienstag
 Freitag
 9:50 Uhr bis 11:30 Uhr
 8:00 Uhr bis 8:45 Uhr
 C205
 C205
 Prof. A. Buchmann (DE)
 Rober Rehner (DE)
Prof. Fürnkranz (KE)
Eneldo Loza Mencía et al. (KE)
 Die ersten 2 Wochen nur Vorlesungen (siehe Webseite)
 1. Übungsbesprechung am 8.5.2014
 Übungsaufgaben selbstständig lösen
 Lösung werden in der Saalübung besprochen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 3
Allgemeines/Organisatorisches
Tutorium
 Als Ergänzung zur Übung, kein Ersatz!
 Fragen können in kleinem Rahmen gestellt werden
 häufige Fragen werden an Übungsleiter weitergeleitet
Montag 9:50 – 11:30
S103/25
Michael Staab
Das Tutorium beginnt am 20.4.2015
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 4
Literatur
 Elmasri, Navathe: “Fundamentals of Database Systems”, Addison-Wesley
 Ullman, Widom: “A First Course in Database Systems”, Prentice Hall
 Ullman: “Data and Knowledge Engineering” Vols. 1,2
 Kemper, Eickler: “Datenbanksysteme”, Oldenbourg
 C.J. Date: “An Introduction to Database Systems”, Addison Wesley
 Heuer, Saake: “Datenbanken-Konzepte und Sprachen”, Thomson Publishing
 Melton, Simon: “Understanding the new SQL”, Morgan Kaufmann
 Literaturhinweise zu Knowledge Engineering im Laufe der Vorlesung
 Software zum Ausprobieren
 PostgreSQL, Datalog, etc.
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 9
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 14
Einführung
 Daten sind wichtiger Aktivposten eines Unternehmens
 Daten → Information → Wissen = Macht!
 Datenbanken ermöglichen die anwendungsübergreifende
Nutzung von Daten (data sharing)
 Datenbanksystem isoliert Anwenderprogramme von Hardware
und Betriebssystem
Anwendungsprogramme
DBMS
Betriebssystem
Hardware
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 15
Datenbanken und DBMS
 Datenbank: Einheitlich beschriebene Darstellung eines
Weltausschnittes mittels diskreter Daten auf externen und
persistenten Speichermedien (Platte, zunehmend SSD oder
Hauptspeicher mit asynchroner persistenter Speicherung)
 Daten in der DB können nur über das Datenbank Management
System (DBMS) eingefügt, gelesen, geändert oder gelöscht
werden
 Ein DBMS - viele DB!
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 16
Datenbanken und DBMS (2)
 Struktur, Operationen und Konsistenzregeln werden durch ein
Datenmodell definiert
 Attributtypen vs. Attributwerte
 Aggregation von Attributen zu Sätzen (Records) / Tupeln / Objekten
 Beschreibende Information (Metadaten)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 17
DBMS
 DBMS: Softwaresystem, welches die einheitliche Beschreibung
und sichere Bearbeitung einer Datenbank ermöglicht
 Aufgaben/Eigenschaften eines DBMS:
 Verwaltung von persistenten Daten (lange Lebensdauer, Konsistenz)
 Datenunabhängigkeit
 Effizienter Zugriff auf große Datenmengen (Gbytes - PBytes)
 ...
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 18
DBMS (2)
 Aufgaben/Eigenschaften eines DBMS:
 ...
 Deklarative Query-Sprachen (optimierbar)
 Flexibler Mehrbenutzerbetrieb (concurrency control)
 Sicherheit vor Systemabsturz und fehlerhaften Transaktionen
(recovery)
 Feinkörnige Zugriffskontrolle
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 19
Datenunabhängigkeit
 Datenunabhängigkeit (nach Date): “Data independence is the
immunity of applications to change in storage and access
strategy”
 Idee: Einbau von Indirektion und Abstraktion
 Physische Datenunabhängigkeit:
 Änderungen an den Speicherstrukturen und Zugriffspfaden sind für
Anwenderprogramme und ad-hoc Queries unsichtbar
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 20
Datenunabhängigkeit (2)
 Logische Datenunabhängigkeit:
 Änderungen an der logischen Sicht sind für Anwenderprogramme
(AP) und Queries unsichtbar
 Jede Anwendung kann eigene Sicht auf gemeinsame DB erhalten
 AP1: Matrikel#,Name,Fach,Note
 AP2: Matrikel#,Fach,Note
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 21
Zugriff über Dateisystem
 Dateisystem bietet auch persistente Speicherung und Zugriff
auf Daten aber
 Datenorganisation ist im Anwendungsprogram (AP) kodiert
 Zugriffspfade sind im AP kodiert
 Zugriffskontrolle auf Dateiebene
→ Keine Datenunabhängigkeit
→ Wenig Parallelität
→ Keine gemeinsame Nutzung eines Datenbestands
→ Redundanz
→ Hoher Wartungsaufwand
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 22
Datenschutz
 DBMS kontrolliert den Zugriff auf die Daten und deren
Sichtbarkeit
 Gesetzgebung regelt erlaubte Nutzung und Veröffentlichung von
Daten
 Datenbank Administrator ist für Einhaltung der
Datenschutzgesetze selbst verantwortlich!
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 23
Effizienter Zugriff und Queries
 Zugriff auf große Datenmengen sollte trotz
zusätzlicher Indirektion effizient sein
 Leistungsverhalten ist DBMS Problem
 Indexstrukturen
 speziell vs. generisch
 DBA ist auf DB-Probleme und Optimierung spezialisiert
 Deklarative Sprachen ermöglichen ad-hoc Query Formulierung
 Benutzer können selbst Queries schreiben
 Queries werden vom DBMS und nicht vom Benutzer optimiert
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 24
Mehrbenutzerbetrieb
 DBMS garantiert, dass parallele Transaktionen isoliert und
konsistent abgearbeitet werden
 Isolation
 Illusion einer Transaktion allein Zugriff auf die Datenbank zu haben
 Eine Transaktion sieht nur einen konsistenten Zustand
der Datenbank
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 25
Ausfallsicherheit - Recovery
 Transaktion ist atomare Prozesseinheit im DB-System
 DBMS garantiert, dass DB immer von einem konsistenten
Zustand in einen anderen konsistenten Zustand geführt wird
 atomar
↔ alles oder gar nichts
 dauerhaft ↔ keine Änderungen gehen verloren
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 26
Vorteile eines DB Systems
 Integrierter Datenbestand → Redundanzfreiheit, datasharing
 Verwaltung der Daten durch DBMS → Konsistenz, höhere
Qualität des Datenbestandes
 Kompatible Mechanismen für Zugriffskontrolle, Recovery,
Concurrency Control
 System garantiert Korrektheit bei Nebenläufigkeit und
Systemfehlern
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 27
Vorteile eines DB Systems (2)
 Anwendungsbezogene Sichten eines gemeinsamen
Datenbestandes
 Physische und logische Datenunabhängigkeit
 Schnellere Programmentwicklung und Wartung (explizite
Strukturbeschreibung, Nutzung von 4GL, QL, Reportgeneratoren)
 Optimierbare Abfragesprachen (query language)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 28
Nachteile eines DB Systems
 Schwergewichtig: DBMS bietet oft zu viel gebündelte
Funktionalität
 General-purpose SW oft weniger effizient als spezialisierte oder
schlanke SW
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 29
Nachteile eines DB Systems (2)
 Bei konkurrierenden Anwendungen kann DBS nur für einen Teil
der Anwendungen optimiert werden
 Kosten: DBMS und zusätzliche HW
 Qualifiziertes Personal (DBA)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 30
Datenbankarchitektur
Benutzer /
Anwendergruppe 1
Benutzer /
Anwendergruppe n
Welt dar
Benutzerschnittstelle
 Struktur (Intension) → Typ
 Werte (Extension) → Instanzmenge
externes
Schema 1
externes
Schema n
Schnittstelle
externes <-> konzeptionelles Schema
 Datenunabhängigkeit erfordert
Indirektion
konzeptionelles Schema
Schnittstelle
konzeptionelles <-> internes Schema
internes Schema
 3-Schema Architektur
 (nächste Folie)
Schnittstelle
internes Schema <-> Datenbank
Datenbank
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 31
Datenbankverwaltungssystem
(Database Management System DBMS)
 Daten stellen einen Ausschnitt der
Datenbankarchitektur (2)
 3-Schema Architektur
Benutzer /
Anwendergruppe 1
Benutzer /
Anwendergruppe n
 Internes Schema: Speicher-strukturen
(Records, Seiten), Zugriffspfade
externes
Schema 1
 Konzeptuelles (logisches) Schema:
logische Sicht der gesamten DB
externes
Schema n
Schnittstelle
externes <-> konzeptionelles Schema
konzeptionelles Schema
 Externe Schemata: Anwendungs-
spezifische Sichten, Filter
Schnittstelle
konzeptionelles <-> internes Schema
internes Schema
Schnittstelle
internes Schema <-> Datenbank
Datenbank
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 32
Datenbankverwaltungssystem
(Database Management System DBMS)
Benutzerschnittstelle
3-Schema Architektur
 Schemata sind DB-spezifisch
 beschreiben eine Datenbank
 Verwaltungssoftware und Schnittstellen
 DB-Unabhängigkeit ist gegeben
 DBMS wird durch diese dargestellt
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 33
3-Schema Architektur (2)
 Definierende Dokumente (3-Schichten)
 1975 ANSI/X3/SPARC Study Group on Data Base Management
Systems Interim Report
 B. Yormark “The ANSI/X3/SPARC Architecture” in “The
ANSI/X3/SPARC DBMS Model”, D.A. Jardine (Ed.) North Holland
1977.
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 34
Schemadarstellung
 Schemadarstellung
 Beschrieben in Daten-Definitionssprache
(Data Definition Language = DDL)
 Datenmanipulation
 Geschieht mit Daten-Manipulationssprache
(Data Manipulation Language = DML)
 DCL (Data Control Language)
 Sicherheit, Berechtigungen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 35
Schemadarstellung (2)
 Ad-hoc Queries
 Ausgedrückt in einer optimierbaren Query-Sprache
(Query Language = QL)
 SQL vereint alle Aspekte in einer Sprache
 Klauseln für Schemadefinition und Datenmanipulation
sind unterschiedlich
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 36
Erscheinungsformen der DML bzw. QL
 Erscheinungsformen der DML bzw. QL
 Eigenständige Dialogsprache
 Eingebettet in Programmiersprache (host language)
 COBOL, FORTRAN, C, C++, Java, …
Deklarative DML
Navigierende DML
Spezifiziert durch Prädikate,
was gesucht wird
Art der Suche durch anfängliche
Positionierung und Verfolgung
von Zeigerketten
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 37
 Anforderungsermittlung &
Anforderungsanalyse
 Konzeptualisierung
MW1
 Integrierung
TS1
Miniwelt2
MW3
TS2
Teilsicht3
Entwurf
DBS Entwurfsprozess
Integrierte Sicht (Entw.)
 Abbildung
Logisch → Physisch (Tuning)
Logisches
Schema (Op)
Internes
Schema
DB
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 38
ES1
ES2
ES3
Betrieb
 Abbildung
Konzeptuell. → Logisch
5-Layer Architecture (Härder)
App. Programs w. embedded Transactions
Logical Data Structures
Logical Access Paths
Storage Structures
Page Mapping and Buffering
Physical Storage Mapping
Set-oriented interface, declarative QL
Record-oriented interface
Internal interface
DB-buffer interface
File interface
Device interface
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 39
Komponenten eines DBMS (Vossen)
User
IO-Prozessor
Output-Generierung
Parser
DBMS
Precompiler
Autorisierung
UpdateProzessor
Integr.
Zugriffspfadgenerierung
Recovery Mgr.
QueryProzessor
Optimierer
TXMgr.
Log
Dictionary Mgr.
Geräte/Speichermgr.
Data Dictionary
Externe Schemata
Konzeptuelle Schemata
Interne Schemata
Datenbank
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 40
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 42
Datenmodelle
 System von Konzepten zur abstrakten Darstellung eines
Ausschnitts der realen Welt mittels Daten
 Verschiedene Abstraktionsebenen
 konzeptuell,
 logisch,
 physisch
 Unterschiedliche Modelle für Entwurf und Betrieb
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 43
Datenmodelle (2)
 Datenmodelle bestehen aus
 Strukturen (statische Eigenschaften)
 Operatoren (dynamische Eigenschaften)
 Constraints (Korrektheitsbedingungen)
301
2000 19 5
302
2000 18 2
303
2000 22 9
 Werte ohne Struktur sinnlos
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 44
Datenmodelle - Struktur
 Logische Struktur erlaubt Interpretation
 Operatoren zum Bearbeiten
 INSERT 305 2000 5 23
 Constraints garantieren syntaktische
Tag Jahr
301
2000 19
5
302
2000 18
2
303
2000 20
9
und semantische Korrektheit einer Operation
 Tmax ≥Tmin
Konsistenzregeln sind in den Strukturen inhärent und für den
Benutzer intuitiv verständlich, müssen aber für das DBMS explizit
dargestellt werden!
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 45
Datenmodelle - Grundbegriffe
 Objekt / Objektinstanz (O)
 O ist die Menge von Objekten/Objektinstanzen
 o ∈ O Darstellung eines Objekts oder einer Tatsache der realen Welt
 Objekttyp (OT)
 Menge von Merkmalsfunktionen oder Attributen mit vorgegebenen
Wertbereichen
 Attribut (A)
 A ∈ OT ist eine Funktion A:O → dom A mit Definitionsbereich O und
Wertbereich W ≔ dom A
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 46
Datenmodelle - Grundbegriffe (2)
 Wertbereich (W)
 W ≔ dom(A)
 Objekttyp Buch(ISBN:char[13], Titel:char[35], Autor:char[50])
Objekt
(0-8053-1748-1,Database Systems, Elmasri)
Attribut
Titel bildet ab auf Zeichenketten in dom(Titel)
Wertbereich
Menge der Zeichenfolgen mit maximal 35
Zeichen des Alphabets und Leerzeichen
Attributwert
Database Systems
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 47
Datenmodelle - Grundbegriffe (3)
 Objektklasse vs. Objekttyp
 Begriffe werden in verschiedenen Modellen unterschiedlich benutzt
 ODMG: Typ = abstrakte Schnittstellen-Definition (behavior)
 Klasse = abstraktes Verhalten plus eine spezifische Realisierung
 Extent: Menge aller Instanzen einer Klasse
 Objektidentifikation
 Oid: vom System zugewiesener, nicht veränderbarer Identifikator
 Schlüssel: Attribut(-kombination) dessen (deren) Werte eine
Objektinstanz eindeutig identifiziert
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 48
Typkonstruktoren
 Typkonstruktoren
 Mechanismen zur Erstellung von (komplexen) Objekttypen und zur
Beschreibung der Beziehungen von Objekttypen
 Klassifikation
 Elementarer Konstruktor im DBS zur Unterscheidung von Objekttyp
und Objektinstanz
Objekttyp:
InformatikProf(SocSec#, Name, Uni)
Objektinstanz:
(461338899, Garcia-Molina, Stanford)
(451335234, Elmasri, UT-Arlington)
(567123456, Navathe, GATech)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 49
Typkonstruktoren (2)
 Aggregation: Bildung eines neuen Objekttyps aus Komponenten
 Standard Datentypen (int, real, char, …, date, time, array)
 Andere Objekttypen
 Tupel sind die einfachsten Aggregate und bestehen aus
Standard Datentypen
InformatikProf(SocSec#:long, Name:char[30], Uni:char[20])
(461338899,Garcia-Molina ,Stanford)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 50
Typkonstruktoren (3)
 Rekursive und Kombinierte Anwendung möglich
(siehe: Beispiel zu Typkonstruktoren)
 Durch (rekursive) Anwendung auf heterogene Objekttypen
werden komplexe Objekte konstruiert
 Part-of Beziehung (üblich)
 Andere Beziehungen mit spezieller Semantik möglich
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 51
Typkonstruktoren (4)
 Assoziation (auch Mengenaggregation): Bildung eines
Objekttyps als Menge von homogenen Objekten
 Objekttyp Y ist Menge von Objekten x des Typs X
 Semesterapparat ist Menge von Büchern
 Instanz von Semesterapparat für D&KE ist
{Elmasri-Navathe, Ullman-Widom, Heuer-Saake, Melton-Eisenberg}
 Merke: Assoziation ist Mengenaggregation als Typkonstruktor
(nicht mit Assoziation als Beziehung in UML verwechseln)!
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 52
Typkonstruktoren - Beispiel
 Beispiel (in Anlehnung an Heuer, OO Datenbanken)
Aggregation
Buch
Assoziation
String
ISBN
Titel
Autoren
Versionen
Stichworte
Verlag
Version
Autor
Stichwort
Auflage
Jahr
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 53
Spezialisierung / Generalisierung
 Spezialisierung als Beziehung zwischen zwei Entity-Typen
 IS_A Beziehung (limousine IS_A car, cabrio IS_A car)
 Alle Eigenschaften des Supertypen gelten für den Subtypen
 Subtypen haben eigene, differenzierende Attribute
 Generalisierung ist die Ableitung eines Supertypen von den
Eigenschaften der Subtypen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 54
Totale und Exklusive Spezialisierung
 Spezialisierungs- / Generalisierungshierarchien benutzen
die IS_A Beziehung (mit möglichen Einschränkungen)
 Disjunktheitsbedingung
 Instanzen der Subtypen sind disjunkt
 Überdeckungsbedingung
 Alle speziellen Instanzen sind auch Instanzen des allgemeinen Typs
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 55
Totale und Exklusive
Spezialisierung (2)
 Exklusive (oder disjunkte) Spezialisierung
 Alle Instanzen der Unterklassen befolgen
die Disjunktheitsbedingung
 Partitionierung
 Alle Instanzen der Unterklassen erfüllen Disjunktheits- und
Überdeckungsbedingung
 Projekte = interne Projekte ⋃ externe Projekte
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 56
Spezialisierung / Generalisierung
 Generalisierung und Spezialisierung unterscheiden sich in der
Insert-Operation
 Generalisierung: erst Instanz des speziellen Typs, dann Instanz des
Supertyps
 Spezialisierung: erst Instanz des allgemeinen Typs, dann Instanz
des speziellen Typs
 Unterschied wichtig, wenn IS_A Beziehung als Integritätsbedingung
benutzt wird (spezielles Objekt darf nicht ohne Instanz des
generellen Objekts existieren → referentielle Integrität)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 57
Gespeicherte vs. Abgeleitete Attribute
 Attribute wurden als Abbildungsfunktionen definiert
 A ∈ OT ist eine Funktion A:O → dom(A)
 Funktion kann eine Schreiboperation sein
 Gespeicherte Attributswerte
 Funktion kann eine Berechnungsfunktion sein
 Abgeleitete Attributswerte
 Beispiel: Alter = Datumheute - Geburtsdatum
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 58
Abgeleitete Typen
 Abgeleitete Typen werden durch Prädikatsdefinitionen gebildet
 Teenager ist eine Spezialisierung von Person mit dem Prädikat
Alter < 20 AND Alter > 12
 Abgeleitete Typen werden auch als Sichtdefinitionen bezeichnet
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 59
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 61
Entity-Relationship-Modell
 Chen, P.P.; “The Entity-Relationship Model: Toward a Unified
View of Data”, ACM TODS, 1,1, Jan. 1976.
 Erweiterungen zum ERM in Elmasri/Navathe
 ERM ist ein graphischer Formalismus zur Datenmodellierung
 ERM in Originalversion erfüllt kaum die Bedingungen eines
Datenmodells
 nur Strukturbeschreibung
 keine Operatoren
 nur minimale Mechanismen zur Beschreibung von Constraints
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 62
Konstrukte des ERM: Entities
 Entity
 identifizierbare Objekte der modellierten Welt
 Entity-Type (auch Entity-Set genannt)
 gemeinsamer Typ von Objekten mit gleichen Merkmalen
(gebildet durch Klassifikation)
Mitarbeiter
 Deklaration
 E= attr E ,K
 attr E = A1 , A2 , …, Am jeweils mit dom Ai
 K ist eine Untermenge von attr(E)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 63
Konstrukte des ERM: Attribute
 Attribut(e)
 Eigenschaft(en) der modellierten Entities
(eine Funktion, die Entities auf Domains abbildet)
Farbe
PKW
 Schlüsselattribut(e)
Kennzeichen
 Menge von Attributen, die ein Entity eindeutig identifiziert
 Minimalitätsbedingung (minimality)
 Eindeutigkeitsbedingung (uniqueness)
 Dargestellt durch Unterstreichen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 64
Konstrukte des ERM: Attribute
 Abgeleitete Attribute können durch eine Funktion von anderen
Attributen abgeleitet werden
 Alter = DatumHeute – Geburtsdatum
 Darstellung mit gestrichelten Linien
Alter
Geburtsdatum
Angestellter
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 65
Konstrukte des ERM: Beziehungen
 Relationship (Beziehung)
 Beziehung zwischen zwei oder mehr Entities,
in einem bestimmten Rollenverhältnis
 Relationship-set
 Menge von Relationships (Beziehungen) gleichen Typs
Datum
Ort
Frau
Ehe
Mann
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 66
Konstrukte des ERM: Beziehungen (2)
 Im Sprachgebrauch wird allgemein von Entity und
Relationship als Typ gesprochen (wenn unzweideutig)
 Beziehungen dürfen eigene Attribute besitzen
 R = ({ent(R)}, attr(R))
Datum
Ort
Frau
Ehe
Mann
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 67
Konstrukte des ERM: Beziehungen (3)
 Grad einer Beziehung
 Anzahl der Entity-Typen, die an einer Beziehung teilnehmen
 Binäre Beziehungen sind häufigster Fall
 spielt = (Spieler, Verein)
Spieler
N
spielt
1
Verein
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 68
Konstrukte des ERM: Beziehungen (4)
 Höhergradige Beziehungen
 Rekursive Beziehungen
sind möglich (z.B. ternäre)
N
Person
Kunde
Mutter_von
1
Beziehung nicht eindeutig
besitzt
Tochter
N
Person
Bank
Konto
Mutter_von
1
Mutter
Explizite Rollennamen
erforderlich
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 69
Ternäre Beziehung
Professor
empfiehlt
Buch
Empfiehlt
Vorlesung
Professor
Vorlesung Buch
Heuer
DB1
1-234
Heuer
DB2
9-876
Saake
DB1
9-876
Saake
DB2
9-876
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 70
Abbildung Ternäre → Binäre
Beziehungen
liest
Professor
empfiehlt
Informationsverlust!
Vorlesung
Liest
Prof.
Vorl.
Heuer
DB1
Heuer
Buch
benutzt
Benutzt
Vorl.
Buch
Empfiehlt Prof.
DB2
DB1
1-234
Heuer
1-234
Saake
DB1
DB2
9-876
Heuer
9-876
Saake
DB2
DB1
9-876
Saake
9-876
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 71
Buch
Konstrukte des ERM: Weak Entities
 Schwache Entitäten werden durch eine funktionale Beziehung
identifiziert
 Existenz der schwachen Entität hängt von Existenz der
bestimmenden Entität ab
 Partieller Schlüssel identifiziert schwache Entität
Exemplar
Ex-Nr
Datum
Ausleiher
von
Buch
ISBN
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 72
Titel
Constraints im ERM
 Strukturelle Integritätsbedingungen können durch Kardinalität
und Komplexität der Beziehungen ausgedrückt werden
Sprudel-Kiste
1
(0,4)
enthält
N
(0,1)
Sprudel-Flasche
 Anzahl von Entities in einer Beziehung
 Kardinalität (hier z. Bsp. 1 oder N)
 Anzahl der Beziehungen, in denen ein Entity teilnimmt
 Komplexität (hier z. Bsp. (0,4) oder (0,1))
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 73
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 78
Das Relationale Modell
 Das relationale Modell geht auf klassische Arbeit von E.F. Codd
zurück
 Codd, E.F.; “A Relational Model for Large Shared Data Banks”,
Comm. ACM, Vol. 13, No. 6, June 1970, pp377-387
 Darstellung der zu modellierenden Welt mit einem einzigen
Konstrukt: Relationen
 Eine Relation ist eine Teilmenge des kartesischen Produkts von
Wertbereichen (Domains)
𝑅 ⊆ 𝐷1 × 𝐷2 × … × 𝐷𝑘
 D1 ×D2 × … ×Dk ist die Menge aller Tupel (v1 ,v2 , …, vk ) mit
Grad k bei denen v1 ein Wert in D1 ist, v2 ein Wert in D2 , etc.
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 79
Relationen
 Bei Datenbanken sprechen wir grundsätzlich von endlichen
Relationen
 Relationen können auch als Tabellen angesehen werden
 Der Tabellenkopf gibt die Struktur an
(Zuordnung von Attributen zu Spalten)
 Zeilen (auch Tupel genannt) sind die Ausprägungen
Lieferanten: LNR
Lname
Lort
12345
XYZ.com
Darmstadt
98765
Meier&Co
Frankfurt
54321
Apple
Cupertino
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 80
Relationenmodell - Übersicht
 Operatoren nehmen Relationen als Input und erzeugen
Relationen (geschlossen)
 Operatoren können in drei Formen ausgedrückt werden:
 Relationenalgebra (relational Algebra)
 Relationen-Tupelkalkül (tuple relational calculus)
 Relationen-Wertbereichkalkül (domain relational calculus)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 81
Relationenmodell - Übersicht (2)
Relationenalgebra
RelationenTupelkalkül
RelationenWertbereichkalkül
 Alle drei sind in der Aussagekraft gleichwertig
 Führen jedoch zu unterschiedlichen Sprachen
 Entwurfstheorie erstellt Richtlinien, besagt was „gut
strukturierte“ Relationen sind
 Relationen, die unter den Operationen der Relationenalgebra
bzw. des Relationenkalküls sicher / geschlossen sind
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 82
Relationenalgebra (RA)
 Attributnamen können den Spalten zugeordnet werden
(müssen aber nicht)
 Ordnung innerhalb eines Tupels bedeutungslos,
wenn Spalten benannt sind
 Ordnung innerhalb eines Tupels wichtig,
wenn Spalten unbenannt sind
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 83
Relationenalgebra (RA) (2)
 5 Basisoperatoren genügen, um die Algebra zu definieren und
andere Operatoren damit auszudrücken
 Vereinigung (union)
𝑅∪𝑆
 Mengendifferenz (set Difference)
𝑅−𝑆
 Kartesisches Produkt (cartesian product)
𝑅×𝑆
 Projektion (projection)
𝜋𝑘 (𝑅)
 Selektion (selection)
𝜎𝐹 (𝑅)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 84
Vereinigung (union)
R:
S:
a
b
c
b
g
a
d
a
f
d
a
f
c
b
d
 Vereinigung: R ∪ S
 die Menge aller Tupel, die in R oder S
oder in beiden Relationen enthalten sind
(Relationen müssen gleichen Grad haben)
R ∪ S:
a
b
c
d
a
f
c
b
d
b
g
a
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 85
Mengendifferenz (set difference)
R:
S:
a
b
c
b
g
a
d
a
f
d
a
f
c
b
d
 Mengendifferenz: R – S
 Menge der Tupel, die in R aber nicht in S
R - S:
enthalten sind (Relationen müssen
a
b
c
gleichen Grad haben)
c
b
d
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 86
Kartesisches Produkt
(cartesian product)
 Seien R und S Relationen mit Grad k1 und k2
 Grad ist die Anzahl der Attribute
R:
S:
a
b
c
b
g
a
d
a
f
d
a
f
c
b
d
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 87
Kartesisches Produkt
(cartesian product)
 Kartesisches Produkt: R × S
 ist die Menge aller (k1 + k2 )-gradigen Tupel
 erste k1 Elemente sind ein Tupel in R
 letzte k2 Elemente sind ein Tupel aus S
R × S:
a
b
c
b
g
a
a
b
c
d
a
f
d
a
f
b
g
a
d
a
f
d
a
f
c
b
d
b
g
a
c
b
d
d
a
f
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 88
Projektion (projection)
 Sei R eine Relation mit Grad k. Dann ist 𝜋𝑖1, 𝑖2, … , 𝑖𝑚 (𝑅) die
Projektion von R auf die Komponenten 𝑖1 , 𝑖2 , … , 𝑖𝑚 (𝑚 < 𝑘).
 Die entstehende Relation ist vom Grad 𝑚
Beispiel:
R:
a
b
c
d
a
f
c
b
d
a
a
c
𝝅𝒊𝟏 ,𝒊𝟑 (𝑹):
a
c
d
f
c
d
 Abbildung einzelner Spalten in neue Relation (hier zwei Spalten)
 Ergebnis ist eine Menge
 Eliminierung von Duplikaten in neuer Relation
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 89
Selektion (selection)
 Sei 𝜎𝐹 (𝑅) eine Selektion auf R und F eine Formel
 Die Formel F besteht aus
 Operanden (Konstante oder Attribute einer Relation)
 Arithmetische Vergleichsoperatoren (=, <, >, ≤, ≥)
 Logische Operatoren (AND, OR, NOT)
 Sei F: B=b
R:
A
B
C
a
b
c
c
b
d
d
a
a
a
𝝈𝐹 𝑅 :
A
B
C
f
a
b
c
c
c
b
d
 Abbildung einzelner Tupel, für die F wahr ist, in neue Relation
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 90
Weitere Relationale Operatoren
 Die eben vorgestellten fünf Basisoperatoren genügen,
um alle anderen Operatoren der Relationenalgebra abzuleiten
 Relationenalgebra ist damit vollständig ausdrückbar
 Aus praktischen Gründen werden im Folgendem
zusätzliche Operatoren definiert
 Um praktisch verwendbar zu sein, müssen Operatoren
für Insert, Delete und Update definiert werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 91
Schnittmenge (intersection)
 Gegeben seien die Relationen R und S
R:
S:
a
b
c
b
g
a
d
a
f
d
a
f
c
b
d
 Schnittmenge: R ∩ S = R - (R - S)
 Menge aller Tupel, die in
R und in S enthalten sind
R ∩ S:
d
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 92
a
f
Quotient (division)
 Gegeben seien die Relationen R und S
R ÷ S:
R: A
B
C
D
a
b
c
a
b
b
C
D
d
c
d
e
f
e
f
c
e
f
A
B
e
d
c
d
a
b
e
d
e
f
e
d
a
b
d
e
((𝜋
𝑅−𝑆
 Quotient: R ÷ S = 𝜋
𝑅−𝑆
𝑅 −𝜋
𝑅−𝑆
S:
𝑅 × 𝑆) − 𝑅)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 93
Join
 Wichtigste nicht-elementare Operation (Verbund)
 Allgemeine Form des Join ist der 𝜃-Join (Theta-Join)
 Dabei ist 𝜃 ein arithmetischer Vergleichsoperator (=, <, >, ≤, ≥)
 Resultierende Relation enthält alle Tupel der Relation,
für die i θ j gilt (i und j sind verschiedene Attributnamen)
 Handelt es sich bei θ um den Gleichheitsoperator (=),
so spricht man von einem Equijoin
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 94
Theta-Join
R: A
B
C
S: D
E
1
2
3
3
1
4
5
6
6
2
5
7
8
 Theta-Join: R ⋈ S = 𝜎𝑖 𝜃 𝑗 (𝑅 × 𝑆)
𝑖𝜃𝑗
 Hier: 𝑖 𝜃 𝑗 = 𝐵 < 𝐷
R ⋈ S:
𝐵<𝐷
A
B
C
D
E
1
2
3
3
1
1
2
3
6
2
4
5
6
6
2
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 95
Equijoin
R: A
B
S: C
D
a
b
b
c
d
b
b
d
b
b
a
d
c
a
 Equijoin: R ⋈ S = 𝜎𝑖=𝑗 (𝑅 × 𝑆)
𝑖=𝑗
R⋈S:
𝑨=𝑪
A
B
C
D
a
b
a
d
b
b
b
c
b
b
b
d
 Theta-Join wobei θ der
Vergleichsoperator (=) ist
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 96
Natural Join
R: A
B
C
S: B
C
D
R⋈S: A
B
C
D
a
b
c
b
c
d
a
b
c
d
d
b
c
b
c
e
a
b
c
e
b
b
f
a
d
b
d
b
c
d
c
a
d
d
b
c
e
c
a
d
b
 Natural Join: R⋈S = 𝜎𝑖=𝑖 (𝑅 × 𝑆)
 Selektiere Menge von Tupeln aus dem kartesischen Produkt mit
gleichen Attributen für gleiche Attributnamen aus den Relationen
 Beachte Notation des Natural Join Operators
 Vergleichsoperator wird weggelassen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 97
SPJ-Queries (Select-Project-Join)
 (Natural) Join ist äußerst wichtig
 Relationen, die aus entwurfstheoretischen Gründen zerlegt wurden,
werden während der Abfrage wieder kombiniert
 Meistbenutzte Operatoren der Relationenalgebra sind
Select, Project und Join
 SPJ-Queries enthalten nur Select, Project und Join Operationen
 Relationale DBMS optimieren hauptsächlich SPJ-Queries
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 98
Weitere Join Operatoren
 Weitere Join-Operatoren für „inkompatible“ Tabellen
 „inkompatible“ Tabellen sind z. Bsp. Tabellen aus
verschiedenen Datenbanken
 Fehlende Werte werden mit NULL-Marken aufgefüllt
(outer-join, left outer-join, right outer-join, natural full outer-join,
union join)
 Semijoin: R ⋉ S = 𝜋𝑅 (R ⋈ S) = R ⋈ 𝜋𝑅∩𝑆 𝑆
 (nächste Folien)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 99
Semijoin
 Gegeben seien die Relationen R und S
B
C
D
E
𝝅𝑹∩𝑺 𝑺 : B
C
c
b
c
d
e
b
c
b
c
b
c
e
f
a
d
b
b
f
a
d
b
g
c
a
d
R: A
B
C
a
b
d
S:
 Semijoin: 𝑅 ⋉ 𝑆 = 𝜋𝑅 𝑅 ⋈ 𝑆 = 𝑅 ⋈ 𝜋𝑅∩𝑆 (𝑆)
𝑹 ⋉ 𝑺:
 Besonders für Optimierung von Queries in
verteilten Datenbanken geeignet
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 100
A
B
C
a
b
c
d
b
c
c
a
d
Beispiel: Relationenalgebra
PKW: Hersteller Modell
Werk
VW
Polo
Wolfsburg
VW
Golf
Puebla
VW
Golf
Wolfsburg
VW
Käfer
Puebla
Ford
Mondeo
Köln
Ford
Focus
Nogales
STORT:
Werk
Land
Wolfsburg BRD
Puebla
Mexico
Köln
BRD
Nogales
Mexico
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 102
Beispiel: Relationenalgebra (2)
 Finde alle Werke, in denen VW Polo oder VW Käfer
oder Ford Focus hergestellt werden
 S = 𝜋𝑊𝑒𝑟𝑘 𝜎𝐹 𝑃𝐾𝑊
 Mit F: Modell = „Käfer“
S: Werk
Wolfsburg
OR Modell = „Polo“
Puebla
OR Modell = „Focus“
Nogales
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 103
Beispiel: Relationenalgebra (3)
 Finde alle Hersteller und Modelle,
die in der BRD hergestellt werden
 Y = 𝜋𝐻𝑒𝑟𝑠𝑡𝑒𝑙𝑙𝑒𝑟, 𝑀𝑜𝑑𝑒𝑙𝑙 (𝜎𝐿𝑎𝑛𝑑="𝐵𝑅𝐷" (𝑃𝐾𝑊 ⋈ 𝑆𝑇𝑂𝑅𝑇))
𝑆
S: Hersteller Modell
Werk
Land
VW
Polo
Wolfsburg BRD
VW
Golf
Puebla
VW
Golf
Wolfsburg BRD
VW
Käfer
Puebla
Ford
Mondeo Köln
BRD
Ford
Focus
Mexico
Nogales
Mexico
Mexico
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 104
Beispiel: Relationenalgebra (3.1)
 Finde alle Hersteller und Modelle,
die in der BRD hergestellt werden
 Y = 𝜋𝐻𝑒𝑟𝑠𝑡𝑒𝑙𝑙𝑒𝑟, 𝑀𝑜𝑑𝑒𝑙𝑙 (𝜎𝐿𝑎𝑛𝑑="𝐵𝑅𝐷" 𝑃𝐾𝑊 ⋈ 𝑆𝑇𝑂𝑅𝑇 )
𝑇
T: Hersteller Modell
Werk
Land
Y: Hersteller Modell
VW
Polo
Wolfsburg BRD
VW
Polo
VW
Golf
Wolfsburg BRD
VW
Golf
Ford
Mondeo Köln
Ford
Mondeo
BRD
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 105
Beispiel Relationenalgebra (4)
 Bessere Strategie (wird vom Optimierer erzeugt)
𝑆
 Y = 𝜋𝐻𝑒𝑟𝑠𝑡𝑒𝑙𝑙𝑒𝑟, 𝑀𝑜𝑑𝑒𝑙𝑙 (𝑃𝐾𝑊 ⋈ 𝜎𝐿𝑎𝑛𝑑="𝐵𝑅𝐷" 𝑆𝑇𝑂𝑅𝑇 )
𝑇
S: Werk
Land
Wolfsburg BRD
Köln
BRD
T: Hersteller Modell
Werk
Land
Y: Hersteller Modell
VW
Polo
Wolfsburg BRD
VW
Polo
VW
Golf
Wolfsburg BRD
VW
Golf
Ford
Mondeo Köln
Ford
Mondeo
BRD
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 106
Relationentupelkalkül (RTK)
 Allgemeiner Ausdruck im RTK hat die Form 𝑡 𝜓(𝑡)}
 t ist eine Tupel-Variable, die ein Tupel fester Länge darstellt
 𝜓 ist eine Formel, die aus folgenden Elementen besteht:
 Relation R(s) wobei s eine stellvertretende Tupel-Variable
für ein Tupel in R ist
 s i θ u j wobei s und u Tupel-Variablen sind und
θ ein arithmetischer Vergleichsoperator
 s i θ a wobei s eine Tupel-Variable ist, θ ein arithmetischer
Vergleichsoperator und a eine Konstante (Elemente einer Formel können
durch logische Operatoren verknüpft werden)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 107
Relationentupelkalkül (RTK) (2)
 RTK Ausdrücke sind auf endliche Relationen beschränkt
 Relationentupelkalkül und Relationenalgebra sind äquivalent
 Operatoren der Relationenalgebra können daher als Ausdrücke des
Relationentupelkalküls geschrieben werden
 Relationentupelkalkül beschreibt nur die Eigenschaften,
die die Tupel der Ergebnismenge erfüllen müssen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 108
Relationentupelkalkül (RTK) (3)
 Vereinigung (union): 𝑅 ∪ 𝑆 = 𝑡 𝑅 𝑡 ∨ 𝑆(𝑡)
 Mengendifferenz (set difference): 𝑅 − 𝑆 = 𝑡 𝑅 𝑡 ∧ ¬ 𝑆 𝑡
 Projektion (projection):
𝑡(𝑘) ∃𝑢 𝑅 (𝑅 𝑢 ∧ 𝑡 1 = 𝑢 𝑖1 ∧ … ∧ 𝑡 𝑘 = 𝑢[𝑖𝑘 ]
 Selektion (selection): 𝑡(𝑟) 𝑅 𝑡 ∧ 𝐹 ′
 wobei 𝐹′ die Formel F ist, bei der jeder i-te Operand durch das i-te
Element im Tupel t ersetzt wird
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 109
Relationentupelkalkül (RTK) (4)
 Kartesisches Produkt (cartesian product):
𝑅 × 𝑆 = {𝑡(𝑟 + 𝑠)│(∃𝑢(𝑅)) (∃𝑣(𝑆))
(𝑅(𝑢) ∧ 𝑆(𝑣) ∧ 𝑡[1] = 𝑢 1 ∧ … ∧ 𝑡[𝑟] = 𝑢[𝑟]
∧ 𝑡[𝑟 + 1] = 𝑣[1] ∧ 𝑡[𝑟 + 𝑠] = 𝑣[𝑠]) }
 Menge aller Tupel t mit Grad r+s, sodass eine Tupel Variable u in R
und eine Tupel Variable v in S existiert
 Für jedes Tupel in R und S gilt, dass das erste Element im
Ergebnistupel mit dem ersten Element von u gleich ist
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 110
Tupel-Variable
 Stellt ein Tupel in einer Formel dar
 Freie Tupel-Variable entsprechen globalen Variablen in einer
Programmiersprache (außerhalb gegenwärtiger Prozedur definiert)
 Gebundene Tupel-Variable entsprechen lokalen Variablen
(innerhalb gegenwärtiger Prozedur definiert und kann nicht von
außen referenziert werden)
 Jedes Atom ist eine Formel
 Tupel-Variable(n) in diesen Atomen sind freie Variablen
 Tupel-Variablen werden durch Quantoren gebunden
(Allquantor ∀, Existenzquantor ∃)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 111
Relationenwertbereichkalkül (RWK)
 RWK 1977 von Lacroix und Pirotte eingeführt („Domain-Oriented
Relational Languages“, Proc. 3. VLDB, Tokyo, Okt. 1977)
 Atome werden analog zu RTK definiert, sind jedoch über
Wertbereich-Variable(n) anstatt Tupel-Variable(n) definiert
 Wertbereich-Variable reicht über die Werte in einem Wertbereich
und bestimmt eine Komponente in einem Tupel
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 112
Relationenwertbereichkalkül (RWK)
(2)
 Atome einer Formel im RWK sind
 R(x1 x2 …xk ) wobei R eine k-gradige Relation ist
und xi eine Wertbereich-Variable oder Konstante
 x θ y ist ein Verhältnis zwischen zwei Wertbereichs-Variablen
oder einer Wertbereichs-Variablen und einer Konstanten
und θ ist ein arithmetischer Vergleichsoperator
 Existenz- und Allquantor werden genau wie im RTK verwendet
 Analog zum RTK definiert man
 Wohlgeformte Formeln der Form 𝑥1 𝑥2 … 𝑥𝑘 𝜓 𝑥1 𝑥2 … 𝑥
 Einschränkung auf sichere Formeln
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 113
𝑘
RA, RTK & RWK vs. Query Sprachen
 Äquivalenz kann bewiesen werden (siehe Ullman oder Vossen)
 Query Sprachen, die auf den unterschiedlichen Ausprägungen
aufbauen haben unterschiedliche Eigenschaften
 Quel = reine RTK-basierte QL (UC Berkeley, Ingres)
 SQL = Mischung von RA und RTK (IBM)
 QBE = reine RWK-basierte QL
(Query by Example, Form-basierte QL)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 114
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 115
Entwurfstheorie
 Wird am relationalen Modell erläutert, weil sie hier am besten
definiert ist und das Relationenmodell weit verbreitet ist
 Ähnliche Prinzipien gelten auch bei Abbildung auf andere Modelle
 Zwei essentielle Schritte:
 Abbildung von ERM (oder UML Klassendiagramm) auf Relationen
 Verbesserung der Relationenschemata durch Normalisierung
 Ein Relationenschema ist eine Menge von Relationen-definitionen
(Tabellen) und Integritätsbedingungen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 116
Abbildung
ERM Entity-Set → Relationen
 Jedes Entity-Set (Typ) der Form E = (attr(E),K) stellt eine
Relation dar
 𝑅1 ∶ 𝑎1 , 𝑎2 , … , 𝑎𝑛 wobei Schlüsselattribute K
in 𝑎1 , 𝑎2 , … , 𝑎𝑛 enthalten sind
 Sei B ein schwaches Entity-Set mit Attributen (𝑏1 , 𝑏2 , … , 𝑏𝑛 ), das
durch Entities des Typs A mit Schlüssel 𝑎1 , 𝑎2 , … , 𝑎𝑘 eindeutig
identifiziert wird
 bildet eine Relation 𝑆 ∶ 𝑎1 , 𝑎2 , … , 𝑎𝑘 , 𝑏1 , 𝑏2 , … , 𝑏𝑛 deren Schlüssel aus
den Schlüsselattributen 𝑎1 , … , 𝑎𝑘 ∪ 𝑏𝑖 besteht
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 117
Schlüssel - Eigenschaften
 Schlüsseleindeutigkeit
 Genau ein Tupel wird von einem Wert von k identifiziert (Injektivität)
 Minimaler Schlüssel
 Seien 𝑘1 und 𝑘2 Mengen von Attributen die ein Entity identifizieren können,
dann bezeichnet man diese als Superschlüssel
 Ein Schlüsselkandidat ist ein Superschlüssel für den kein 𝑘1 ⊂ 𝑘2 existiert.
 Es existiert also keine Teilmenge von 𝑘2 , die Schlüssel ist und die SchlüsselEindeutigkeits-Eigenschaft wahrt.
 Da 𝑘2 kann nicht weiter verkleinert werden kann, ist 𝑘2 minimal.
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 118
Schlüssel
 (nach Date) Eine Attributmenge k einer Relation R ist ein
Schlüsselkandidat ⟺
 k wahrt die Eigenschaft der Schlüsseleindeutigkeit
 k ist minimal
 (nach Vossen) Schlüssel sind Attribute, deren Wert ein Entity
identifiziert
 Schlüssel sind minimal
 Eventuelle Probleme bei Normalisierung, falls Schlüssel nicht
minimal ist
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 119
Abbildung
ERM Beziehung → Relationen
 Beziehungen im ERM können eigene Relationen bilden
 Beziehungen der Form 𝑅 = 𝑒𝑛𝑡 𝑅 , 𝑎𝑡𝑡𝑟 𝑅
zwischen Entity-Sets
der Form 𝐸𝑖 = 𝑎𝑡𝑡𝑟 𝐸𝑖 , 𝐾𝑖 bilden die Relation S mit folgender
Schemadefinition S:
𝑛
𝑘𝑖 ∪ 𝑎1 , 𝑎2 , … , 𝑎𝑘
𝑖=1
 Primärschlüssel der beteiligten Entity-Sets sind immer in der
Abbildung einer ERM-Beziehung enthalten
 Beziehungseigene Attribute (falls vorhanden) in S enthalten
 Namenskonflikte werden durch Umbenennung gelöst
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 120
Abbildung: Schlüssel und Spezialfälle
 Schlüssel bei Relationen, die aus binären Beziehungen abgeleitet
werden
 m:n Beziehung - Schlüssel ist 𝑘1 ∪ 𝑘2, da nur die Festlegung aller
Attribute in 𝑘1 ∪ 𝑘2 ein Tupel in der Beziehungsrelation identifiziert
 1:n Beziehung - Schlüssel ist 𝑘2 , da zu jedem Wert von 𝑘2 nur ein
Wert von 𝑘1 zugeordnet ist
 1:1 Beziehung - 𝑘1 oder 𝑘2 kann als Schlüssel gewählt werden
 1:1 und 1:n Beziehung wird durch Fremdschlüssel abgebildet
 Beziehungseigene Attribute existieren nicht
 Kardinalität oder Komplexität in der Form 0..1 oder 0..N
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 121
Beispiel:
Abbildung ERM → Relationen
city
S#
Supplier (S)
sname
P#
N
status
SP
qty
S:
(s#, sname, city, status)
P:
(p#, pname, weight, color, city)
M
city
Product (P)
pname
SP: (s#, p#, qty)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 122
weight
color
Beispiel: (häufiger) Spezialfall
author
ISBN
Book (B)
title
editor
N
BP
status
1
Publisher (P)
pname
B: (ISBN, titel, author, status, pname)
P: (pname, address, editor)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 123
address
Integritätsbedingungen
 Sind Modell-inhärent oder extern (durch die Anwendung bedingt)
 Modell-inhärente Integritätsbedingungen im Relationenmodell
 Schlüsseleindeutigkeit (key uniqueness),
intrarelationale Abhängigkeit
 Funktionale Abhängigkeit (functional dependency),
intrarelationale Abhängigkeit, Grundlage für Normalisierung
 Referentielle Integrität (referential integrity),
interrelationale Abhängigkeit
 Externe Integritätsbedingungen (z. Bsp. IS_A Beziehung, welche
schwerer im Relationenmodell abzubilden ist)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 124
Normalisierung … Wann eigentlich?
Auftrag- und Miniwelt
Definition
Modellierung
Abbildung auf Relationen
Normalisierung
Test und Betrieb
Datenbank
Anwendungsentwicklung
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 125
Datenbankschema
Erstellung
Motivation für Normalisierung
sname
saddress
item
price
Abbildung auf Relationen
Suppliers (S)
Suppliers
SNAME SADDRESS
ITEM
PRICE
…
…
…
…
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 127
Datenbankschema
Erstellung
Motivation für Normalisierung –
Redundanz vermeiden
 Für jeden bestellten Gegenstand wird die Lieferantenadresse
gespeichert (Redundanzen treten auf!)
 Änderung der Adresse schwer
 Inkonsistenz wahrscheinlich
 Speicherverschwendung
 Lieferanten und Bestellungen
Suppliers:
SNAME SADDRESS
ITEM
PRICE
ABC
Frankfurterstr. 1
123
12 €
XYZ
Wiesbadenerstr. 1 123
14 €
ABC
Frankfurterstr. 1
567
579 €
ABC
Frankfurterstr. 1
793
33 €
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 128
Überspezifikation
 Wegen Überspezifikation lassen sich gewisse Informationen nicht
mehr in der Datenbank speichern
 z. B. kann man keine zwei Adressen für Lieferanten in der
Datenbank speichern, da SNAME die SADRESSE funktional bestimmt
Suppliers:
SNAME SADDRESS
ITEM
PRICE
ABC
Frankfurterstr. 1
123
12 €
XYZ
Wiesbadenerstr. 1 123
14 €
ABC
Frankfurterstr. 1
567
579 €
ABC
Frankfurterstr. 1
793
33 €
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 129
Motivation für Normalisierung Update Anomalien
 Update Anomalie (update anomaly)
 Beim Update der Anschrift müssen alle Tupel,
in denen die Anschrift vorkommt, aktualisiert werden
Suppliers:
SNAME SADDRESS
ITEM
PRICE
ABC
Frankfurterstr. 1
123
12 €
XYZ
Wiesbadenerstr. 1 123
14 €
ABC
Frankfurterstr. 1
567
579 €
ABC
Frankfurterstr. 1
793
33 €
ABC | Frankfurterstr. 1
ABC | Wiesbadenerstr. 9
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 130
Motivation für Normalisierung Einfüge Anomalien
 Einfüge Anomalie (insertion anomaly):
 SNAME und ITEM bilden den Schlüssel
 Kann Lieferanten nur dann speichern,
wenn Artikel bestellt wurde (keine NULL-wertigen Schlüssel!)
Suppliers:
SNAME SADDRESS
ITEM
PRICE
ABC
Frankfurterstr. 1
123
12 €
XYZ
Wiesbadenerstr. 1 123
14 €
ABC
Frankfurterstr. 1
567
579 €
ABC
Frankfurterstr. 1
793
33 €
DEF Münchnerstr. 1
–
–
–
–
931
42 €
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 131
Motivation für Normalisierung –
Lösch-Anomalien
 Lösch-Anomalie (deletion anomaly):
 Beim Löschen der letzten Bestellung bei einem Lieferanten verlieren
wir seine Anschrift
SNAME
SADDRESS
ITEM
PRICE
ABC
Frankfurterstr. 1
123
12 €
XYZ
Wiesbadenerstr. 1 123
14 €
ABC
Frankfurterstr. 1
579 €
ABC
Frankfurterstr. 1 793
567
33 €
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 132
Motivation für Normalisierung Lösung
Schema mit
schlechter
Qualität
Redundanz
Normalisierung
Anomalien
Suppliers
SNAME SADDRESS
Orders
ITEM
PRICE
Relationen werden aufgeteilt
ABER WIE?
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 133
?
Funktionale Abhängigkeit (FD)
 Gegeben sei die Relation R mit Attributen A und B
 B ist von A funktional abhängig (R.A → R.B) ⟺ für jeden Wert
von A existiert genau ein Wert von B
 Suppliers.SNAME → Suppliers.SADDRESS
Suppliers:
 Volle funktionale Abhängigkeit
SNAME
SADDRESS
ABC
Frankfurterstr. 1
XYZ
Wiesbadenerstr. 1
DEF
Rheinstr. 1
 Abhängige Attribut B ist von der Attributmenge A abhängig aber
nicht von einer nicht-trivialen Untermenge
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 134
Semantik in FDs
 Funktionale Abhängigkeiten definieren die Semantik in der
Datenbank
 Normalisierung ist abhängig davon
 Eine andere Interpretation führt zu anderen Schemata
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 135
Beispiel: Semantik in FDs
 Konto(konto#, filiale, kunde)
Konto.konto#
Konto.konto#, Konto.filiale
 Annahme 1
→ Konto.kunde
→ Konto.kunde
 Erste FD impliziert eine globale Kontonummer oder dass die Bank nur aus
einer Niederlassung besteht (oder dass der modellierte Weltausschnitt nur
aus einer Filiale besteht)
 Annahme 2
 Zweite FD impliziert, dass Kontonummern lokal für eine Filiale vergeben
werden
 Unter Annahme 1 ist die zweite FD keine volle FD
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 136
Funktionale Abhängigkeiten (FDs)
 Funktionale Abhängigkeiten
 Beeinflussen das relationale Schema über Normalisierungsprozess
 Müssen in ihrer Gesamtheit betrachtet werden
(inklusive solcher, die nicht explizit aufgelistet wurden)
 Gegeben sei die Menge von Funktionalen Abhängigkeiten
 {A → B, A → C, B → H, CG → H, CG → I}
 Funktionale Abhängigkeit A → H implizit gegeben,
da A → B und B → H (Transitivität)
 Die transitive Hülle (transitive closure) F+ ist die Menge aller FDs,
die von der bekannten Menge von FDs impliziert werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 137
Armstrongs Axiome für FDs
 Seien W,X,Y und Z Attributmengen
 Armstrongs Axiome erlauben Ermittlung der Hülle
 Reflexivität
 Wenn Y Teilmenge von X ist, dann gilt X → Y
 Erweiterung
 Wenn X → Y, dann gilt XZ → YZ
 Transitivität
 Wenn X → Y und Y → Z, dann gilt X → Z
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 138
Armstrongs Axiome für FDs (2)
 Zusätzliche Regeln
 Additivität (union rule)
 Wenn X → Y und X → Z, dann gilt X → YZ
 Projektivität (decomposition rule)
 Wenn X → YZ, dann gilt X → Z und X → Y
 Pseudotransitivität (pseudotransitivity rule)
 Wenn X → Y und WY → Z, dann gilt XW → Z
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 139
Beispiel: Armstrongs Axiome
 Gegeben sei
 die Relation R: (A, B, C, G, H, I) und
 die Menge von FDs F = {A → B, A → C, CG → H, CG → I, B → H}
 Durch Anwendung der Armstrongschen Axiome und den davon
abgeleiteten Regeln erhält man weitere FDs
 A → H durch Transitivität von A → B und B → H
 CG → HI durch Additivität von CG → H und CG → I
 AG → I durch Erweiterung von A → C und durch Transitivität
 Algorithmus zur Berechnung der Hülle von FDs
 Bernstein, TODS 1,4,1976
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 140
Beispiel: Normalisierung
city
S#
Supplier (S)
sname
status
P#
N
SP
M
qty
S:
(s#, sname, city, status)
P:
(p#, pname, weight, color, city)
city
Product (P)
pname
weight
SP: (s#, p#, qty)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 141
color
Beispiel: Normalisierung (2)
city
S#
Supplier (S)
sname
status
P#
N
SP
M
qty
city
Product (P)
pname
color
weight
 Annahmen:
 sname muss nicht eindeutig sein
s# → {sname, status, s.city}
 Teile werden nur in einer Farbe
p# → {pname, color, weight, p.city}
hergestellt und an einem Ort
city → status
gelagert
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 142
Normalformen - Übersicht
nicht normalisierte Relationen
1 NF
2 NF
BCNF
3 NF
4 NF
5 NF
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 144
Normalformen
 Bezeichnung: 1NF, 2NF, 3NF, BCNF (3.5NF), 4NF, PJNF (5NF)
 historisch, deutet aber auch strengere Bedingungen an
 Caveat
 Höhere Normalform bedeutet Vermeidung von Anomalien, jedoch mehr
Aufwand
 Verzicht auf höhere Normalform aus Gründen
der Performanz möglich
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 146
1NF
 Eine Relation ist in 1NF, wenn alle Attributwerte atomar sind
 Wichtig hierbei, wie man die Attribute, d.h. die
darunterliegenden Wertbereiche interpretiert
 Buch(ISBN, Titel, Autor)
 (0070447527, Database System Concepts, {Korth, Silberschatz})
 Nicht in 1NF, da Autor als mengenwertiges Attribut interpretiert wird
 (0070447527, Database System Concepts, Korth & Silberschatz)
 In 1NF, da Autor als unteilbare Zeichenkette interpretiert wird
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 147
2NF
 Eine Relation ist in 2NF, wenn sie in 1NF ist und alle
Nichtschlüsselattribute vollständig vom Schlüsselkandidaten
abhängen
 F: (s#, sname, status, city, p#, qty) ist in 1NF
 Aus der Menge der FDs ergibt sich folgendes
Abhängigkeitsdiagramm
qty
S#
P#
 Welche Anomalien tauchen hier auf?
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 148
sname
status
city
2NF
 F: (s#, sname, status, city, p#, qty) muss aufgeteilt werden
 G: (s#, sname, status, city)
 H: (s#, p#, qty)
sname
S#
status
s#, p#
city
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 149
qty
3NF
 Eine Relation ist in 3NF, wenn alle Nichtschlüsselattribute nicht
transitiv vom Schlüsselkandidaten abhängen
 Transitive Abhängigkeit führt zu neuen Anomalien
 Obwohl status von city abhängig ist (stadtspezifischer Status),
muss Lieferant gespeichert werden, bevor Status einer Stadt
gespeichert werden kann
 Aufbrechen von G: (s#, sname, status, city) in
 I: (s#, sname, city)
 J: (city, status)
S#
status
city
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 150
Normalisierung und Semantik
 Normalisierung hängt von der Semantik der Daten ab
 Semantik wird über die FDs ausgedrückt
 Andere Interpretation führt zu anderen Schemata
 Beispiel:
 Status wird nicht als lokal/nicht-lokal sondern als Zuverlässigkeit
eines Lieferanten interpretiert.
 Dadurch wird die FD city → status sinnlos und
die Relation G: (s#, sname, status, city) wäre in 3NF
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 151
Boyce-Codd Normalform (BCNF)
 Eine Relation ist in BCNF, wenn jeder Determinant ein
Schlüsselkandidat ist
 Ein Determinant ist eine Attributmenge, von der irgendein anderes
Attribut vollständig abhängig ist
 Verschärfung der 3NF für Relationen mit mehreren
zusammengesetzten Schlüsselkandidaten mit gemeinsamen
Attributen
 „Each field must represent a fact about the key, the whole key, and
nothing but the key“ (Bill Kent, HP)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 152
Beispiel: BCNF
 Erweitern von city in J zu J: (zip, city, street, status)
 J ist in 3 NF aber nicht in BCNF
 Schlüsselkandidaten: {city, street} und {zip, street}
 3 NF erlaubt Abhängigkeiten zwischen Attributen der
Schlüsselkandidaten!
 Zu beachten ist
 zip ↔ city, street
 Änderung der Semantik
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 153
Beispiel: BCNF (2)
 FDs
 street, zip → status
 street, city → status
 street, city → zip
 zip → city
 Aufbrechen von J (zip, city, street, status) in
 W (zip, street, status), Z (zip, city)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 154
Projektionskriterien
 Gegeben sei die Relation G: (s#, sname, city, status)
Aufbrechen von G auf 2 unterschiedliche Arten I, J oder P, Q
 Beide Ansätze gültig unter BCNF. Welcher ist besser?
 I und J können unabhängig voneinander verändert werden,
P und Q erfordern gleichzeitiges Ändern von P.city und Q.status
 I und J ist vorzuziehen
 city → status ist ein interrelationales Constraint geworden
FDs:
I: (s#, sname, city)
P:(s#, sname, city)
J: (city, status)
Q: (s#, status)
s#
→ sname
s#
→ city
sname
→ city
city
→ status
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 155
4NF
 Es gibt Relationen, die in BCNF sind, aber trotzdem noch
unnötige Redundanzen aufweisen
 STARS: Name
Street
City
Title
C. Fisher
123 Maple Str.
Hollywood StarWars
C. Fisher
5 Locust ln.
Malibu
C. Fisher
123 Maple Str.
Hollywood Empire Strikes Back
C. Fisher
5 Locust ln.
Malibu
C. Fisher
123 Maple Str.
Hollywood Return of the Jedi
C. Fisher
5 Locust ln.
Malibu
StarWars
Empire Strikes Back
Return of the Jedi
 Redundanz entsteht, weil 2 unabhängige 1:N Beziehungen in einer
Relation abgebildet wurden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 156
4NF (2)
 Redundante 1:N Beziehungen sind:
 Stars.Name →→ Stars.Address
 Stars.Name →→ Stars.Title
 Die einzige Beziehung zwischen Filmtitel und Adresse ist über
den Namen des Filmstars
 Aufbrechen in 2 Projektionen bietet Lösung
StarAddress:
(Name, Street, City)
StarMovie:
(Name, Title)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 157
Mehrwertige Abhängigkeit
(MVD: multivalued dependency)
 Definition MVD:
 In einer Relation R(A, B, C) existiert eine mehrwertige Abhängigkeit
R.A →→ R.B ⇔ die Menge von B-Werten, die in einem Wertepaar
(A-Wert, C-Wert) in R nur von A, nicht aber von C abhängt
 Definition nicht-triviale MVD:
 Eine MVD 𝐴1 𝐴2 … 𝐴𝑛 →→ 𝐵1 𝐵2 … 𝐵𝑛 ist nicht-trivial, wenn kein
Attribut aus 𝐵1 𝐵2 … 𝐵𝑛 in 𝐴1 𝐴2 … 𝐴𝑛 enthalten ist und nicht alle
Attribute der Relation in A oder B enthalten sind
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 158
4NF
 Definition 4NF:
 Eine Relation R mit zugeordneter Menge D von funktionalen und
mehrwertigen Abhängigkeiten ist in 4NF, wenn für jede
MVD A →→ B eine der folgenden Bedingungen gilt:
 Die MVD ist trivial
 A ist ein Superschlüssel von R
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 159
Beispiel: 4NF
Assis:
(PersNr, Name, FG, Boss, Spr, ProgSpr)
FDs: PersNr
→ Name
PersNr
→ FG
PersNr
→ Boss
MVDs:
PersNr
→→ Spr
PersNr
→→ ProgSpr
 Erster Schritt:
 Assis: (PersNr, Name, FG, Boss)
 Fähigkeiten: (PersNr, Spr, ProgSpr)
 Wegen der beiden MVDs muss Fähigkeiten zerlegt werden:
 Sprachen: (PersNr, Spr)
 ProgrammierSprachen: (PersNr, ProgSpr)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 160
5NF (PJNF: Project-Join Normalform)
 Einige Relationen können nicht durch 2-wegige Joins
wiederhergestellt werden (aber doch durch mehrwegige Joins)
 Niedergradiges Join erzeugt Fremdtupel, die vom nächsten Join
wieder entfernt werden
 Definition 5NF:
 Eine Relation ist in PJNF, wenn jede Join-Abhängigkeit eine
Konsequenz der Schlüsselkandidaten ist
 Definition zur Vollständigkeit, nur durch Beispiel illustriert
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 161
Lossy Join
(Verlust an Info, Fremdtupel)
 Ursprüngliche Relation:
Kredit: Filiale
Kto#
Saldo
Kunde
FrankfurterStr.
123
1000
Meier
FrankfurterStr.
146
1000
Schulze
Rheinstr.
256
3000
Schmidt
Rheinstr.
257
1000
Petersen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 162
Lossy Join
(Verlust an Info, Fremdtupel) (2)
 Zerlegte Relationen:
Kreditinfo:
Betragsinfo:
Filiale
Kto# Saldo
FrankfurterStr.
123
1000
FrankfurterStr.
146
1000
Rheinstr.
256
3000
Rheinstr.
257
1000
Saldo Kunde
1000
Meier
1000
Schulze
3000
Schmidt
1000
Petersen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 163
Lossy Join
(Verlust an Info, Fremdtupel) (3)
 Kreditinfo ⋈ Betraginfo (auf Saldo):
Filiale
Kto# Saldo Kunde
FrankfurterStr.
123
1000
Meier
FrankfurterStr.
123
1000
Schulze
FrankfurterStr.
123
1000
Petersen
FrankfurterStr.
146
1000
Meier
FrankfurterStr.
146
1000
Schulze
FrankfurterStr.
146
1000
Petersen
Rheinstr.
256
3000
Schmidt
Rheinstr.
257
1000
Petersen
Rheinstr.
257
1000
Schulze
Rheinstr.
257
1000
Petersen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 164
Beispiel: PJNF
 Gegeben sei SPJ:
SPJ:
SP: S# P#
SP⋈PJ
S#
P#
J#
S1
P1
S# P#
J#
S1
P1
J2
S1
P2
S1
P1
J2
S1
P1
J1
S2
P1
S1
P2
J1
S1
P2
J1
S2
P1
J1
S2
P1
J2
S1
P1
J1
S2
P1
J1
S# P#
J#
S1
P1
J2
S1
P2
J1
S2
P1
S1
P1
(SP⋈PJ)⋈JS
PJ:
JS:
P#
J#
P1
J2
P2
J1
P1
J1
J# S#
J2
S1
J1
J1
S1
J1
J1
S2
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 165
Denormalisierung
 Verzicht auf höhere Normalform aus Gründen der Performanz
möglich:
 Aufgeteilte Tabellen, da keine Redundanz und Anomalien durch
Normalisierung
 Zusammenführen von Datensätzen (Join) bewirkt Leistungsverlust!
(beim Lesen)
Employees
ENAME Salary Dept#
?
Department
Dept# DeptName
Employees
ENAME Salary Dept# DeptName
…
…
…
…
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 166
Werkzeuge
 MS Access – Table Analyzer
 MS SQL Server – Import Wizard
 MySQL Data Import ?
 http://www.sqldbu.com/eng/sections/tips/mysqlimport.html
Keine adäquate Hilfe beim
Erstellen der FDs!
„Each field must represent a fact about the
key, the whole key, and nothing but the
key“ (Bill Kent, HP)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 167
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 168
SQL
 SQL hat zwei Teile
 SQL als DDL (Definition von Tabellen)
 SQL als Query Sprache
 Melton, Simon; „Understanding the New SQL: A Complete
Guide“, Morgan Kaufmann Publishers
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 170
SQL (2)
 SQL ist internationaler Standard
 SQL 89 (minimalistischer Standard - kleinster gemeinsamer Nenner
der Hersteller)
 SQL 92 (gegenwärtig größtenteils umgesetzt)
 entry, intermediate, full
 SQL 99 (objekt-relationale Erweiterungen)
 SQL 2003 (XML Erweiterungen, SQL Erweiterungen)
 SQL 2006 (XML Erweiterungen, XPath, XQuery)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 171
SQL (3)
 Nur SQL Standard Syntax verwenden!
 Vorlesung, Übungen, Klausur
 Keine Proprietäre Erweiterungen
 z. Bsp. DISTINCT ON, LIMIT oder OFFSET
 Ziel der Vorlesung
 Hier gelernte SQL Syntax überall verwenden zu können
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 172
Database Objects
SQL environment
Implement.
Authorization
identifier
Catalog
Module
Privilege
Schema
Character Set
Translation
Collation
Table
Viewed Table
Domain
Column
Constraint
Column
Constraint
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 173
Assertion
Catalogs & Schemas
 In SQL-89 wurden Tabellen Schemata durch den Namen des
Besitzers qualifiziert (authorization identifier)
 In SQL-92 enthalten die Kataloge die Schemata, jedes Schema
hat einen Eigentümer
 Objekte innerhalb eines Schema werden entweder explizit oder
implizit referenziert
 FROM studentsunqualified
 FROM colleges.studentspartiallyqualified
 FROM mycat.colleges.studentsfullyqualified
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 174
Tables
 Tabellen sind die Basiskonstrukte in SQL
 Tabellen sind Multisets (auch „bags“ genannt)
 Relationen sind Mengen ohne Duplikate
 Tabellen sind Multisets und können Duplikate enthalten
 Tabellen Typen sind
 base tables
 viewed tables (Sichten)
 derived tables
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 175
Base Tables
 Tabellen bestehen aus einer Tabellendefinition (Intension) und
Ausprägungen (Extension)
 Vier Typen von Base Tables
 Persistent Base Table
 Global Temporary Table
 Created Local Temporary Table
 Declared Local Temporary Table
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 176
Base Tables
 Persistent Base Tables haben einen persistenten
(d.h. DB-Session übergreifenden) Extent
 Temporary Tables haben eine im Schema definierte Intension,
ihre Ausprägung ist jedoch nur auf eine DB-Session beschränkt
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 177
Temporary vs. Persistent Tables
 Temporary Tables
 Überleben eine Session nicht
 Global Temporary Tables
 Können von verschiedenen Modulen oder embedded SQL
Programmen innerhalb einer Session benutzt werden
 Created Local Temporary Tables
 Können nicht programmübergreifend benutzt werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 178
Temporary vs. Persistent Tables (2)
 Declared Local Temporary Tables
 Definition nicht im Schema hinterlegt, sondern im Modul
(Programm) das sie nutzt
 Local Temporary Tables
 Auf ein Programm beschränkt und nicht persistent, daher bessere
Performanz bei Manipulation von Zwischenergebnissen
 Bei allen temporären Tabellen:
 ON COMMIT PRESERVE ROWS
erhält Tupel in der Session
 ON COMMIT DELETE ROWS
löscht Tupel beim Commit der TX
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 179
Derived Tables und Views
 Derived Tables
 Ergebnis einer Query auf einer oder mehreren Tabellen
 Views (Sichten) sind mit Namen bezeichnete,
aus Basisrelationen abgeleitete, virtuelle Relationen
 Im Gegensatz zu Derived Tables
 Definition von Views im Schema durch ein CREATE VIEW
 Sichten werden über eine Query definiert (später mehr dazu)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 180
SQL - Data Definition Language (DDL)
CREATE TABLE
Erstellen der Tabellendefinition
DROP TABLE
Löschen einer Tabellendefinition
ALTER TABLE
Ändern einer Tabellendefintion
CREATE VIEW
Erstellen einer Sichtdefinition
DROP VIEW
Löschen einer Sichtdefinition
CREATE INDEX
Erstellen eines Index
DROP INDEX
Löschen eines Index
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 181
CREATE TABLE
 Basis Tabellen (Persistent Base Table) werden über ein CREATE
TABLE definiert
 CREATE TABLE base-table
(column-definition [, column-definition] ...
[, primary-key-definition]
[, foreign-key-definition [, foreign-key-definition] ... ]);
 Der Ausdruck column-definition hat die Form
 column data-type [NOT NULL]
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 182
Beispiel: CREATE TABLE
 CREATE TABLE MOVIES_STARS (
MOVIE_TITLE
CHARACTER(30) NOT NULL,
YEAR_RELEASED
DATE,
ACTOR_LAST_NAME
CHARACTER (35) NOT NULL,
ACTOR_FIRST_NAME
CHARACTER (25)
);
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 183
NULL
 Nullmarken können verschiedene Bedeutung haben:
Bedeutung:
Attribut trifft bei einem Tupel
nicht zu
(Provision bei Angestellten mit festem Gehalt,
Entbindungen bei männlichen Patienten)
Wert existiert, ist aber unbekannt
(unbekanntes Gehalt)
Wert existiert nicht
(Name des Ehegatten)
Wert ist nicht definiert
(Maximalwert einer leeren Menge)
Wert ist ungültig
(Alter eines Angestellten ist 98 Jahre)
Wert wurde nicht angegeben
(Fragebögen)
Eingefügte Werte bei outer-join
und outer-union
 Unterscheidung zwischen prinzipiellen Eigenschaften von Nullmarken
und deren Realisierung in SQL
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 184
NULL (2)
 Mit NULL kann nicht gerechnet werden
 Vergleiche mit NULL ergeben immer FALSE
 Felder, die mit NOT NULL gekennzeichnet sind,
dürfen keine NULL-Marken enthalten
 Felder, die als PRIMARY KEY dienen müssen NOT NULL sein
 Defaults können an Stelle von NULL-Marken definiert werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 185
Temporary Tables
 Created Temporary Tables
 Persistente Definition im Schema
 Für jede SQL-Session wird neue Instanz geschaffen
 Aus verschiedenen Modulen kann auf eine Tabelle zugegriffen
werden
 CREATE GLOBAL TEMPORARY TABLE EMPLOYEES (
NAME
CHARACTER VARYING(30),
SALARY
DECIMAL(7,2))
ON COMMIT PRESERVE ROWS;
 Tabelle überlebt Session nicht,
Zwischenergebnisse können aber eine Transaktion überleben
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 186
Temporary Tables (2)
 Created Local Temporary Tables
 CREATE LOCAL TEMPORARY TABLE EMPLOYEES (
NAME
CHARACTER VARYING(30),
SALARY
DECIMAL(7,2))
ON COMMIT DELETE ROWS;
 Declared Local Temporary Tables
 (eine Instanz pro SQL-Session oder Modul):
 DECLARE LOCAL TEMPORARY TABLE MODULE M1 (...)
ON COMMIT DELETE ROWS;
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 187
Derived Tables
 Mit derived tables können SQL-statements in der FROM Klausel
benutzt werden (ab SQL-92)
 SELECT AVG (n_courses)
FROM (SELECT last, first, COUNT(*)
FROM enrollments
GROUP BY last, first
) AS intermediate (last, first, n_courses)
WHERE intermediate.last LIKE `J´;
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 188
ALTER TABLE
 Definition
 ALTER TABLE base-table ADD column data-type;
 Ausführung
 ALTER TABLE MOVIES ADD PRODUCER CHARACTER(30);
 Eingefügtes Attribut darf nicht NOT NULL sein
 Definition im Katalog wird erweitert
 Bei nächstem Zugriff auf ein Tupel wird NULL eingefügt,
bevor der Benutzer es sieht
 Bei der nächsten Schreiboperation wird das erweiterte Tupel geschrieben,
wenn ein nicht-NULL Wert eingegeben wird
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 189
DROP TABLE
 Definition
 DROP TABLE base-table;
 Ausführung
 DROP TABLE MOVIES;
 DROP TABLE eliminiert eine Relation
 Bei dem Löschen der Basis-Relation werden darauf
definierte Sichten und Index-Strukturen mitgelöscht
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 190
CREATE INDEX
 Definition
 CREATE [ UNIQUE ] INDEX index-name
ON base-table ( column [ order ] [ , column [ order ] ... ] )
[ CLUSTER ];
 Ausführung
 CREATE INDEX X ON konto (filiale, kname) CLUSTER;
 Order kann ASC (default) oder DESC sein
 CLUSTER besagt, dass es ein „clustering Index“ ist
(höchstens einer pro Relation, physisches Gruppieren)
 UNIQUE besagt, dass keine Duplikate erlaubt sind
(Wahrung der Schlüsseleindeutigkeit)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 191
DROP INDEX
 Definition
 DROP INDEX index-name;
 Ausführung
 DROP INDEX konto.X;
 Löscht einen Index
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 192
SQL als Data Manipulation Language
(DML)
 Basisstruktur besteht aus drei Klauseln
 SELECT - entspricht einer Projektion und zählt die Attribute der
Ergebnistabelle auf (* = alle)
 FROM - beschreibt die Liste der Tabellen, auf denen die Operationen
ausgeführt werden
 WHERE - Selektionsprädikat bzw. Joinprädikat
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 193
SQL als Data Manipulation Language
(DML) (2)
 Basisstruktur
SELECT
𝐴1 , 𝐴2 , … , 𝐴𝑛
FROM
𝑇1 , 𝑇2 , … , 𝑇𝑚
WHERE
P
 Die Basisstruktur einer Query entspricht dem Ausdruck
𝜋𝐴1,𝐴2,…,𝐴𝑛 𝜎𝑃 𝑇1 × 𝑇2 × … × 𝑇𝑚
 Wenn P nicht angegeben ist, gilt P = TRUE
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 194
SQL
 Ausgeben aller Attribute einer Tabelle
 SELECT *
FROM MOVIES_STARS;
 Ausgeben einer Spalte ohne Duplikate
 SELECT DISTINCT ACTOR_LAST_NAME
FROM MOVIES_STARS;
 Selektieren mit einem Selektionsprädikat auf einer Tabelle
 SELECT *
FROM MOVIES_STARS
WHERE ACTOR_LAST_NAME = ‘Streisand‘;
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 195
„Ähnlichkeitssuchen“ in SQL
 Definition
 match-expression [ NOT ] LIKE pattern [ ESCAPE escape-character ]
 Keyword LIKE (NOT negiert dieses Keyword)
 Wildcard für n viele Zeichen: %
 Wildcard für ein Zeichen: _
 Wildcard für ein bestimmes Zeichen: [charlist]
 Negation: [!charlist] oder auch [^charlist]
 Keyword ESCAPE
 Escape-Sequenz eines Zeichens des LIKE patterns
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 196
Beispiel:
„Ähnlichkeitssuchen“ in SQL (2)
 Gegeben seien die Strings ABCDE, ABCD und %ABC
 LIKE ‘ABC%‘ findet ABCDE und ABCD
 LIKE ‘ABC_‘ findet nur ABCD
 LIKE ‘$%%‘ ESCAPE ‘$‘ findet alle Zeilen die mit % beginnen,
also %ABC
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 197
SQL Query mit LIKE
 SELECT title
 SELECT title
FROM movie_titles
FROM movie_titles
WHERE title LIKE ‘Bev%’ ;
WHERE title LIKE ‘%Bev%’ ;
 Ergebnis
 Ergebnis
title
title
Beverly Hills Cop
Beverly Hills Cop
Beverly Hills Cop II
Beverly Hills Cop II
Down and Out in Beverly Hills
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 198
Kombination von Queries
 Kombination von Queries ergibt gemeinsame Ergebnismenge
 Queries (Ergebnismengen) können über INTERSECT, UNION und
EXCEPT kombiniert werden
 Gegeben seien die Tabellen
 Movie( title, year, length, inColor, studioName, producer )
 StarsIn( movieTitle, movieYear, starName )
(SELECT title, year FROM Movie)
UNION
(SELECT movieTitle AS title, movieYear AS year FROM StarsIn)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 199
Subqueries
 Bisher waren Prädikate immer als Vergleich
mit skalaren Werten definiert
 Nun ist das Ziel Vergleiche mit ganzen Tabellen
ausführen zu können
 Man definiere diesbezüglich Subqueries
 Subqueries sind SQL Ausdrücke, deren Ergebnis eine Tabelle ist
 In komplexen Prädikaten können Subqueries
(d.h. deren Ergebnismengen) verglichen werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 200
Bedingungen über Relationen
 Operatoren werden auf eine Relation R angewandt
und ergeben einen booleschen Wert
 R kann das Ergebnis einer SELECT-FROM-WHERE Query sein
 EXISTS R - ergibt TRUE wenn R nicht leer ist
 s IN R - sei fortfolgend s ein skalarer Wert und R habe Grad eins
 ergibt TRUE ⇔ s in R enthalten ist
 s > ALL R - ergibt TRUE ⇔ s größer als alle Werte in R ist
 s > ANY R - ergibt TRUE ⇔ s größer als irgendein Wert in R ist
 EXISTS, ALL und ANY können durch Verknüpfung mit NOT
negiert werden - was ist dann die Semantik?
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 201
Negierung von EXISTS, ANY und ALL
 NOT EXISTS R - ergibt TRUE ⇔ R leer ist
 NOT s > ALL R - ergibt TRUE ⇔ kleiner als der oder
gleich dem Maximalwert
 NOT s > ANY R - ergibt TRUE ⇔ s kleiner als der
oder gleich dem Minimalwert
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 202
Bedingungen mit Tupeln
 Um Tupel mit den Tupeln einer Relation vergleichen zu können,
müssen diese den gleichen Grad haben
 Vergleiche benutzen die Standardordnung
 Gegeben seien die Tabellen
 Movie(title, year, length, inColor, studioName, producerC#)
 StarsIn(movieTitle, movieYear, starName)
 MovieExec(name, address, cert#, netWorth)
 Query: Finde die Produzenten von Harrison Ford Filmen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 203
Beispiel: Nested Queries
SELECT name
FROM MovieExec
WHERE cert# IN
(SELECT producerC#
FROM Movie
WHERE (title,year) IN
(SELECT movieTitle, movieYear
FROM StarsIn
WHERE starName = ‘Harrison Ford’)
);
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 204
Erklärung des Beispiels
 Queries werden von innen nach außen analysiert
 Innere Query gibt movieTitle und movieYear für H.F. Filme (beide
Attribute sind als Schlüssel für Tabelle Movies nötig)
 Mittlere Query ergibt die producerC# für jeden Film von H.F. aus
dem Zwischenergebnis
 Äußere Query macht den letzten Join über producerC# und cert#
und gibt die Namen der Produzenten an
 Gibt es alternative Formulierungen?
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 205
Alternative Formulierung
SELECT name
FROM MovieExec, Movie, StarsIn
WHERE cert# = producerC#
AND title = movieTitle
AND year = movieYear
AND starName = ‘Harrison Ford’;
 Was passiert mit Duplikaten?
 SELECT DISTINCT name
Wie werden Duplikate eliminiert? (sortieren, hashen)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 206
JOIN
students
enrollments
LNAME FNAME NICK
LAST
James
Robert
Bob
James Robert
CS101
Long
Robert
Bobby
Smith
CS200
FIRST
Diane
 SQL-89 inner joins (natural inner join)
 SELECT lname, nick, course
FROM students, enrollments
WHERE students.lname = enrollments.last
AND students.fname = enrollments.first;
LNAME
NICK
COURSE
James
Bob
CS101
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 207
COURSE
SQL-92 JOINs - CROSS JOIN
 SQL-92 führt verschiedene zusätzliche Joins ein
 cross join (kartesisches Produkt), union join, outer join
 CROSS JOIN
 SELECT *
FROM (students CROSS JOIN enrollments);
LNAME FNAME NICK
LAST
FIRST
James
Robert
Bob
James Robert
CS101
James
Robert
Bob
Smith
CS200
Long
Robert
Bobby
James Robert
CS101
Long
Robert
Bobby
Smith
CS200
Diane
Diane
COURSE
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 208
SQL-92 JOINs - UNION JOIN
 UNION JOIN
 SELECT *
FROM (students UNION JOIN enrollments) AS result;
LNAME FNAME NICK
LAST
FIRST
COURSE
James
Robert
Bob
NULL
NULL
NULL
Long
Robert
Bobby
NULL
NULL
NULL
NULL
NULL
NULL
James
Robert
CS101
NULL
NULL
NULL
Smith
Diane
CS200
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 209
SQL-92 JOINs –
NATURAL FULL OUTER JOIN
 NATURAL FULL OUTER JOIN
 SELECT *
FROM (students NATURAL FULL OUTER JOIN v_enrollments)
AS result;
students
v_enrollments
LNAME FNAME NICK
LNAME FNAME COURSE
James
Robert
Bob
James
Robert
CS101
Long
Robert
Bobby
Smith
Diane
CS200
LNAME
FNAME NICK
COURSE
James
Robert
Bob
CS101
Long
Robert
Bobby NULL
Smith
Diane
NULL
CS200
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 210
SQL-92 JOINs - LEFT OUTER JOIN
 LEFT OUTER JOIN
 SELECT *
FROM (students LEFT OUTER JOIN enrollments
ON lname = last
AND fname = first)
AS result ;
LNAME FNAME NICK
LAST
FIRST
James
Robert
Bob
James Robert
Long
Robert
Bobby NULL
NULL
COURSE
CS101
NULL
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 211
SQL-92 JOINs - RIGHT OUTER JOIN
 RIGHT OUTER JOIN
 SELECT *
FROM (students RIGHT OUTER JOIN enrollments
ON lname = last
AND fname = first)
AS result;
LNAME FNAME NICK
LAST
FIRST
James
Robert
Bob
James Robert
CS101
NULL
NULL
NULL
Smith
CS200
Diane
COURSE
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 212
Range Queries
 Bezug auf Vergleiche mit Wertbereichen in der WHERE Klausel
 Zwei Arten von Range Queries:
 Gegeben sei die Tabelle
 Movie(title, year, length, inColor, studioName, producerC#)
 SELECT * FROM Movie WHERE year IN {1998, 1999, 2000};
 SELECT * FROM Movie WHERE year >= 1998;
 Zusätzlich BETWEEN
 val1 BETWEEN val2 AND val3
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 213
Aggregierung: Set Functions
 Berechnungsfunktionen auf Tabellen
 COUNT zählt die Zeilen in einer Tabelle
 COUNT(*) zählt alle Zeilen
 Weitere Qualifikation durch ALL, DISTINCT, Attributnamen
oder Prädikate
 SELECT COUNT(*)
FROM MOVIES_STARS
WHERE ACTOR_LAST_NAME = ‘Moore’;
 SELECT COUNT (DISTINCT MOVIE_TITLE)
FROM MOVIES_STARS ;
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 214
Aggregierung: MAX, MIN, SUM, AVG
 MAX wählt den höchsten Wert aus
 MIN wählt den Minimalwert aus
 SUM summiert alle Instanzen
 AVG bildet den arithmetischen Durchschnitt
 Vorsicht! Umgang mit NULL!
 COUNT(*) zählt alle Zeilen, inkl. die mit NULL
 SUM ignoriert NULL in der Summe
 AVG zählt beim Durchschnitt nur die Tupel, die nicht NULL im
summierten Attribut haben
 AVG  SUM / COUNT
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 215
SQL Funktionen
 Weitere Funktionen in SQL-92 vom System vordefiniert
 SUBSTRING
 TRIM
 POSITION
 EXTRACT
 Interval Value Expressions
 Benutzer-definierte Funktionen in SQL99 (später)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 216
GROUP BY
 Definition
 GROUP BY grouping-column [ , grouping-column ...]
 Ausführung
 SELECT movie_type, AVG (current_rental_price)
FROM movie_titles
GROUP BY movie_type;
 Table expressions produzieren virtuelle Tabellen
 Ordnet Tupel nach einem Gruppierungskriterium und kann auch
Werte nach dieser Klassifikation aggregieren
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 217
Beispiel: GROUP BY
title
movie_type
rental_price
movie_type
price
Lethal Weapon
Action
2.99
Action
2.66
Unforgiven
Western
3.99
War
2.99
Outlaw
Western
2.99
Western
3.49
Kelly’s Heroes
War
2.99
Shaft
Action
2.99
Shaft’s Big Score
Action
1.99
SELECT movie_type, AVG (rental_price) AS price
FROM movie_titles
GROUP BY movie_type;
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 218
Beispiel: GROUP BY (2)
 Auswertungsreihenfolge?
 SELECT movie_type, AVG (rental_price)
FROM movie_titles
WHERE movie_studio IN (Paramount, Universal)
GROUP BY movie_type;
 FROM-CLAUSE
[ WHERE-CLAUSE ]
[ GROUP-BY-CLAUSE]
[ HAVING-CLAUSE]
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 219
HAVING
 Definition
 HAVING search-condition
 Ausführung
 HAVING movie_type = ‘Western‘ OR movie_type = ‘War’
 Ist ein zusätzlicher Filter
 Wirkt auf die Relation der vorhergehenden Klausel
 Bezieht sich auf die Werte der Grouping Klausel
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 220
Datenbank-Änderungen
 Im relationalen Modell sind Änderungen (Updates) konzeptuell
eine Kombination von Einfügen und Löschen
 Löschen wird anfrageähnlich beschrieben
 DELETE R WHERE P
 löscht alle Tupel in R, die das Prädikat P erfüllen
 Löschen bezieht sich immer auf eine Relation (obwohl andere
Relationen in der Selektionsformel vorkommen dürfen)
 Fehlendes Prädikat (WHERE-Klausel) gilt als TRUE
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 221
Delete
 Definition
 DELETE R WHERE P;
 Ausführung
 DELETE KONTO
WHERE saldo < (
SELECT AVG(saldo)
FROM KONTO
);
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 222
Delete (2)
 Prädikate können beliebig komplex sein
(z.B. geschachtelte Queries)
 DELETE KONTO
WHERE FILIALE IN (
SELECT FILIALE
FROM BANK
WHERE STANDORT = ‘Darmstadt‘
);
 Tupel werden als gelöscht gekennzeichnet und nur am Ende
einer Operation wirklich gelöscht (commit)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 223
Insert
 Definition
 INSERT INTO R
VALUES (< … >);
 Ausführung
 INSERT INTO KONTO
VALUES (‘Frankfurter Str.’, 1234, ’Meier’, 1000);
 Zu speichernde Tupel, müssen einzeln definiert werden
(auf Grad und Wertbereiche achten!)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 224
Insert (2)
 INSERT INTO Konto
SELECT filiale, kredit#, kname, 200
FROM Kredit
WHERE filiale = ‘Frankfurter Str.’;
 Jedem Kunden, der einen Kredit in der Filiale Frankfurter Str. hat,
erhält ein Konto mit 200€ Guthaben und gleicher Nummer wie der
Kredit
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 225
Update
 Definition
 UPDATE R
SET newvalue = < ... >
WHERE P;
 Ausführung
 UPDATE Konto SET saldo = saldo*1.05;
 Wenn nur einzelne Attribute verändert werden macht Delete und
Insert keinen Sinn (Effizienz, Clustering, Timestamp, etc.)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 226
Update (2)
 WHERE Klausel im UPDATE hat gleiches Format wie bei SELECT
 Beispiel: Erhöhe alle Konten um Zinsbetrag, wobei Konten mit mehr
als 100.000€ und Laufzeit = 12 Monate 6% erhalten und kleinere
Beträge oder kürzere Laufzeiten 5% erhalten
 UPDATE Konto SET saldo = saldo * 1.06
WHERE saldo > 100000 AND frist = 12;
 UPDATE Konto SET saldo = saldo*1.05
WHERE saldo <= 100000 OR frist < 12
 Reihenfolge der Ausführung ist wichtig!
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 227
Sichten (Views)
 Sicht (View): benannte, abgeleitete, virtuelle Relation
 Sichten können von Basisrelationen und anderen Sichten
abgeleitet werden
 Korrespondenz zum externen Schema bei ANSI/SPARC, d.h.
View agiert als Filter (Unterschied: Benutzer sieht ein externes
Schema kann aber viele Sichten und Tabellen sehen)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 228
Sichten (Views) (2)
 Definitionen
 CREATE VIEW view [ (column-commalist) ] AS query-exp
[ WITH [ CASCADED l LOCAL ] CHECK OPTION];
 CHECK kann beliebig komplexes Constraint sein
 DROP VIEW view;
 Ausführung
 CREATE VIEW ArmeInformatiker (pnr, name, beruf, gehalt) AS
SELECT pnr, name, beruf, gehalt FROM Mitarbeiter
WHERE beruf = ‘Informatiker’ AND gehalt < 70000;
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 229
Sichten (Views) (3)
 Sichten werden im Schema definiert und Intension wird im
Katalog gespeichert
 Sicht agiert als Filter durch ausblenden von Attributen und
selektieren von Tupeln
 Benutzerfreundlichkeit
 Datenschutz
 Datenunabhängigkeit
 Bei dem Speichern von Sichten (materialized views) muss
Konsistenz gewahrt werden!!!
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 230
Sichten (Views) (4)
 Views werden durch eine Query definiert,
ergo sind Sichtnamen und Queries austauschbar
 Sichten werden i.a. nicht permanent gespeichert
(werden on-the-fly erstellt)
 SELECTs auf Views unproblematisch (wirkt wie Konjunktion der
Selektionsprädikate der Query und der Sichtdefinition)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 231
Sichten (Views) (5)
 Abbildungsprozess für Sichten kann mehrstufig sein
 Abbildungsmächtigkeit ist eingeschränkt
(keine Schachtelung von GROUP BY möglich)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 232
Updatable Views
 Updates auf Sichten können problematisch sein
 Änderungsoperationen auf Sichten erfordern eindeutige
Zuordnung der Tupel zwischen Sicht und Basisrelation
 Sichten auf Basisrelation sind nur aktualisierbar,
wenn der Primärschlüssel in der Sicht enthalten ist
 Sichten sind nicht aktualisierbar wenn sie
 Über Aggregatfunktionen und GROUP BY definiert sind
 Über mehr als eine Relation definiert sind (i.a. nicht aktualisierbar)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 233
View Updates
 Wenn Tupel über eine Sicht eingefügt werden
 Muss der Primärschlüssel in Sicht enthalten sein
 Müssen alle nicht-NULL Attribute in Sichtdefinition enthalten sein
 Müssen alle nicht-NULL Attribute zumindest Default-Werte
zugewiesen bekommen
 Eingefügte Tupel müssen das die Sicht definierende Prädikat erfüllen
(CHECK-Option)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 234
Constraints
 Integritätsbedingungen können entweder über das
Applikationsprogramm oder das DBMS definiert und überwacht
werden
 Vorteile von Definition und Überwachung der
Constraints im DBMS
 Constraints werden immer geprüft
(Programme könnten oder auch nicht die Constraints prüfen)
 Constraints sind an einer Stelle für alle Benutzer einsehbar
 Constraints sind im DBMS leichter programmierbar als im
Anwendungsprogramm (Unterstützung durch das DBMS)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 235
Constraints (2)
 Modellinhärente Constraints
 NOT NULL von bestimmten Attributen
 UNIQUE Eindeutigkeit (insbesondere von Schlüsseln)
 PRIMARY / FOREIGN KEY Referentielle Integrität
 Anwendungsbezogene Constraints
 CHECK Überprüfung von Bedingungen auf einer Tabelle
(Wertbereichsdefinitionen oder selektives nicht-NULL)
 Bedingungen in CHECK Klausel können komplex sein
(z.B. Ausschluss aus anderer Tabelle)
 ASSERTIONS Tabellenübergreifende Constraints
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 236
Uniqueness Constraint
 SQL-89
 Alle Felder mussten NOT NULL sein, wenn sie unter ein
UniquenessConstraint fielen
 SQL-92
 NULL kann in Attributen enthalten sein,
die unter ein UniquenessConstraint fallen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 237
Uniqueness Constraint (2)
 Zwei Zeilen mit NULL in einem Feld werden als unterschiedliche
Werte behandelt, welche als UNIQUE bezeichnet werden
 ALTER TABLE enrollments ADD CONSTRAINT
UNIQUE (last, first, course);
Enrollments
Doe
John
Ch307
Doe
John
NULL
Doe
John
NULL
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 238
Referentielle Integrität
 Referentielle Integrität
 Für jeden Wert eines Fremdschlüssels muss ein Tupel
mit einem Wert als Primärschlüssel existieren
 Einfache Version der referentiellen Integrität in SQL89
 CREATE TABLE enrollments (...,
FOREIGN KEY (last, first)
REFERENCES students (lname, fname));
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 239
Referentielle Integrität (2)
 Interpretation der referentiellen Integrität in SQL
 Spalten der referenzierten Tabelle müssen in einem
Uniqueness-Constraint spezifiziert sein
 Gleichheit der Fremd- und Primärschlüssel
 NULL im Fremdschlüssel bedeutet Zeile wird nicht geprüft
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 240
Beispiel: Referentielle Integrität
STUDENTS (referenced)
ENROLLMENTS (referencing)
LNAME FNAME NICK
LAST
FIRST
James
Robert
Bob
James Robert
CS101
Long
Robert
Bobby
Long
Robert
PH201
Smith
NULL
EF101
NULL
Robert
CS221
NULL
NULL
CH301
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 241
COURSE
Beispiel: Referentielle Integrität (2)
 Zusätzlich in SQL-92 hinzugekommen
 partially null foreign keys
 match partial
 Teil des FOREIGN KEY kann NULL sein
 match full
 FOREIGN KEY muss entweder ganz NULL oder ganz non-NULL sein
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 242
FOREIGN KEY
 Definition
 <foreignkeyclause> ::= FOREIGN KEY (<referencingcolumns>)
REFERENCES <tablename> [(<referencedcolumns>)]
[<foreignkeyactions>]
 Für die Aktion gilt:
 <foreignkeyaction> ::= <event><action>
 <event> ::= ON UPDATE l ON DELETE
 <action> ::= CASCADE l SET DEFAULT l SET NULL
l NO ACTION
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 243
FOREIGN KEY (2)
 CASCADE
 Propagiert updates oder deletes
(Änderungen im Schlüssel/referenzierten Attribut werden zu den
Tupeln der referenzierenden Relation propagiert)
 SET NULL
 Setzt Fremdschlüsselattribute auf NULL wenn der Wert nicht länger
als Primärschlüssel existiert
 SET DEFAULT
 setzt die nicht übereinstimmenden Attribute zu einem Default
 NO ACTION
 lässt die Änderung nicht zu
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 244
FOREIGN KEY (3)
 Primärschlüssel der referenzierten Tabelle
 Referencingcolumns muss spezifiziert werden
 Referencedcolumns darf weggelassen werden, Default ist der
Primärschlüssel
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 245
Assertions (SQL-92)
 Definition
 <SQL92 assertion> ::= CREATE ASSERTION <constraintname>
CHECK ( <condition> ) [ <constraintevaluation> ]
 <constraintevaluation> ::= [ NOT ] DEFERRABLE
[ { INITIALLY DEFERRED l INITIALLY IMMEDIATE } ]
 Condition kann irgendein SQL Prädikat sein
 Immediate bedeutet, dass Constraint direkt nach der Ausführung des
Statements ausgewertet wird
 Deferred bedeutet, daß Constraint direkt vor dem commit ausgewertet wird
 Initially gibt Default an, kann geändert werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 246
Assertion Beispiel
CREATE ASSERTION sample_table_never_empty
CHECK ( ( SELECT COUNT (*) FROM sample) > 0)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 247
Assertions (SQL-99)
 Definition
 <SQL-99 assertion> ::= CREATE ASSERTION <constraint name>
{ BEFORE COMMIT l AFTER <assertion event> }
CHECK <condition>
[ FOR EACH ROW ] <table name> <constraint evaluation>
 <assertion event> ::= { INSERT l DELETE l UPDATE
[ OF <column name> ] } ON <table name>
 FOR EACH ROW
 tuple granularity, sonst statement granularity
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 248
Trigger
 Definition
 <SQL-99 trigger>::= CREATE TRIGGER <triggername>
{ BEFORE l AFTER l INSTEAD OF } <triggerevent>
ON <tablename> [ ORDER <order value> ]
[ REFERENCING <reference> ]
WHEN(<condition>) <SQL procedurestatements>
[ FOR EACH { ROW l STATEMENT } ]
 <triggerevent> ::= INSERT l DELETE l UPDATE
 <reference> ::= OLD AS <oldvaluetuplename> l NEW AS
<newvaluetuplename> l OLD_TABLE AS <oldvaluetablename>
NEW_TABLE AS <newvaluetablename>
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 249
Domains
 Persistente Definition (Katalog)
 Datentyp
 (optionaler) Default
 (optionale) Constraints
 (optionale) Ordnung
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 250
Domains
 Kann an Stelle der Datentypdefinition in Spaltendefinition
verwendet werden
 CREATE DOMAIN money AS DECIMAL (7,2);
 CREATE DOMAIN shirt_size AS CHAR (1)
DEFAULT ‘M’ CONSTRAINT valid_sizes
CHECK (value IN (‘S’, ’M’, ’L’, ’X’));
 CREATE TABLE shirts (
style CHAR(5), size shirt_size, list_price money);
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 251
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 253
SQL und Programmiersprachen
Wie greift man auf eine Datenbank aus
einer Anwendung heraus zu?
Probleme:
(Die meisten) Programmiersprachen
haben keinen Mengenbegriff
Wie erhält man Daten aus der
Datenbank? Wie führt man SQL aus?
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 254
Cursor Begriff (siehe auch Iterator)
Anfragen, die eine mengenwertige Antwort erzeugen, müssen
über einen Cursor im Programm bearbeitet werden
Ein Cursor durchläuft die Tupel einer Relation einzeln
Relation kann gespeichert oder Ergebnisrelation sein
In JDBC hat man den gleichen Begriff als Iterator
Ein Cursor muss
 deklariert werden
 initialisiert werden
 geschlossen werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 255
Embedded SQL
Program in Host Language
(ADA,C,COBOL,FORTRAN,C++,Java...)
+
Embedded SQL
Preprocessor
Host Language + Function Calls
Host Language Compiler
Host Language Program
with Function Calls
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 256
SQL Library
Static vs. Dynamic SQL
‘Static’ SQL statements traditionally embedded in programs
 precompilation
 optimization
 works well when DB accesses can be anticipated
‘Dynamic’ SQL required when
 using ad-hoc queries or interactive on-line data analysis
 SQL statements are passed to more generic SQL queries
 on the fly compilation and optimization
Call Level Interface (CLI) provides even more flexibility
 ODBC and JDBC are popular implementations of SQL/CLI
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 257
Datenbank Schnittstellen
 CLI (Call Level Interface)
 ODBC (Open DataBase Connectivity)
 JDBC
 ORM (Object-Relational Mapper)
 Java: Hibernate, EclipseLink, ObjectDB, …
 C++: LiteSQL, ODB, …
 .NET: LINQ (included), NHibernate, …
 usw.
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 258
Stack für Daten Zugriffe
 Datenquelle:
 enthält die eigentlichen Daten
Anwendung
 RDBMS, Spreadsheet,
XML-Datei, usw.
Treiber Manager
Treiber
Datenquelle
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 259
Stack für Daten Zugriffe
 Treiber:
 steuert die Datenquelle an
Anwendung
 bietet Interface an (z.B. ODBC, JDBC)
 ein Treiber pro Datenquelle
und Interface benötigt
 z.B.: http://jdbc.postgresql.org/
Treiber Manager
Treiber
Datenquelle
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 260
Stack für Datenbank Zugriffe
 Treiber Manager:
 vermittelt zwischen
Anwendung
Programmiersprache und Treiber
 Beispiel:
 Cursor-Abstraktion zum
Treiber Manager
Umgang mit Mengen
 z.B.: Java: JDBC API
Treiber
Datenquelle
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 261
Stack für Datenbank Zugriffe
 Anwendung:
 viele Anwendungen nutzen
Anwendung
einen Treiber Manager
 z.B.: viele Java Anwendungen
Treiber Manager
nutzen die JDBC API
Treiber
Datenquelle
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 262
ORM
 Object Relational Mappers (ORM) können Objekte
einer OO-Sprache in einer relationalen Datenbank abbilden
 Sehr hoher Abstraktionsgrad
 ORM verwaltet Indizes, Constraints, etc.
 Benutzen oft vorhandene Treiber Manager
 Folgen aber nicht immer diesem Modell
 Beispiel: ObjectDB ist ein ORM für Java inkl. DBMS, bietet aber
keinen JDBC Treiber
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 263
ORM - Beispiel
@Entity(name = "Recipe")
public class RecipeImpl extends Recipe {
Name
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String title;
private int portions;
private String preparation = "";
private byte[] image;
@OneToMany(orphanRemoval = true)
private List<Ingredient> ingredients;
@ManyToMany
private List<Category> categories;
@ManyToMany
private List<Side> sides;
Primärschlüssel
„auto increment“
@Transient
private ResourceBundle messages =
ResourceBundle.getBundle("ExceptionMessages");
nicht speichern
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 264
1:N
N:M
N:M
ORM - Beispiel
SELECT:
CriteriaBuilder cb = EntityManager.getCriteriaBuilder();
CriteriaQuery<RecipeImpl> query = cb.createQuery(RecipeImpl.class);
query.from(RecipeImpl.class);
List<RecipeImpl> result = EntityManager.createQuery(query).getResultList();
INSERT:
EntityManager.getTransaction().begin();
// ensure ingredients of the recipe are persisted
for (Ingredient i : recipe.getIngredients()) {
EntityManager.persist(i);
}
// [...]
EntityManager.persist(recipe);
EntityManager.getTransaction().commit();
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 265
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 274
Transaktionen
 Definition
 Atomarer Prozess der die Datenbank von einem konsistenten
Zustand in einen anderen konsistenten Zustand überführt
 Minimale Prozesseinheiten im Datenbank System
 Durch BOT (begin of transaction) und EOT (end of transaction)
Statements begrenzt
 Transaktion zwischen BOT und EOT besteht aus semantisch und
syntaktisch korrekten DML (Data Manipulating Language)
und PL (Programming Language) Statements
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 275
Konsistenz
 Transaktionskonsistenz
 Transaktion bildet einen konsistenten Datenbankzustand auf einen
anderen konsistenten Zustand ab
 Nebenläufige Transaktionen behindern sich nicht und produzieren
keine inkonsistenten Zustände
 Datenbank Konsistenz
 Eine Datenbank ist konsistent, wenn sie alle auf ihr definierten
Konsistenzregeln erfüllt
 Die Datenbank muss vor und nach der Ausführung einer Transaktion
konsistent sein
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 277
Transaktionen: Terminierung
 Transaktionen müssen immer terminieren
 Normale Terminierung – commit
 Änderungen werden in der Datenbank permanent
 Anormale Terminierung – abort
 Änderungen werden zurückgerollt, DB verbleibt im gleichen Zustand
den sie vor dem BOT hatte
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 278
Transaktionen: Terminierung (2)
 Abort kann unterschiedlich eingeleitet werden
 Benutzer oder Anwendungsprogramm
 System
 Abgebrochene Transaktionen kann man
 Neu starten, wenn Transaktion durch einen Hardware (HW) oder
Systemfehler (z. Bsp. deadlock) abgebrochen wurde
 Entfernen fehlerhafte Transaktionen
(z. Bsp. Null Division)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 279
ACID Eigenschaften
 Transaktionen sollten die ACID Eigenschaften einhalten
 Atomicity (Atomarität)
 Transaktion wird komplett oder gar nicht ausgeführt
 Consistency (Konsistenz)
 Transaktionen produzieren keine inkonsistenten Zustände
 Isolation (Isolation)
 Veränderungen werden nur nach dem Commit der Transaktion sichtbar
 Durability (Dauerhaftigkeit)
 Veränderungen sind permanent
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 280
Korrektheitskriterien
 Bestimmen die erlaubten Sequenzen von Befehlen
 Die sequentielle Ausführung von Transaktionen ist per Definition
korrekt (da jede Transaktion individuell korrekt sein muss)
 Jede nebenläufige Ausführung von Transaktionen die mit einer
sequentiellen Ausführung äquivalent ist, ist korrekt (= serialisierbar)
 Serialisierbarkeit ist eines von mehreren Korrektheitskriterien
(aber das mächtigste und bestverstandene)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 281
Konfliktserialisierbarkeit
 Zwei Transaktionsabläufe sind äquivalent
 Über die gleichen Transaktionen definiert
 Inkompatiblen Operationen in diesen Transaktionen werden in der
gleichen Folge ausgeführt
(vorausgesetzt keine der Transaktionen wurde abgebrochen)
 Für 𝑝𝑖 in 𝑇𝑖 und 𝑞𝑘 in 𝑇𝑘 (𝑎𝑖 , 𝑎𝑘 nicht im Transaktionsablauf)
 𝑝𝑖 < 𝑞𝑘 in Ablauf eins und 𝑝𝑖 < 𝑞𝑘 in Ablauf zwei
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 282
Konfliktserialisierbarkeit (2)
 Nur die Reihenfolge der inkompatiblen Operationen ist relevant
 Implementierung durch Sperren
 Exklusive locks
(Schreibsperren)
 Shared locks
(Lesesperren)
S
E
S True
False
E False
False
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 283
Zwei Phasen Sperrprotokoll
Two Phase Locking Protocol (2PL)
 Elementares Two Phase Locking
Sperren
 1) Scheduler erhält Befehl vom
Transaktionsmanager (TM) und
prüft ob Sperre gesetzt ist
 Sperre ist gesetzt → Befehle direkt an DM
 Sperre nicht gesetzt → prüfe Verfügbarkeit
BOT
 Ja - setze Sperre und reiche Befehl weiter
 Nein - prüfe Kompatibilität
 kompatibel - inkrementiere Referenzzähler, reiche Befehl weiter
 inkompatibel - warte
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 284
EOT
Zwei Phasen Sperrprotokoll
Two Phase Locking Protocol (2PL)
 2) Frühester Zeitpunkt zum Freigeben
Sperren
von Sperren ist wenn DM Ausführung des
Befehls bestätigt (handshake Prinzip)
 3) Wenn eine Transaktion 𝑇𝑖
eine Sperre freigegeben hat,
darf 𝑇𝑖 keine neue Sperre anfordern
BOT
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 285
EOT
Eigenschaften von 2PL
 2PL garantiert konfliktserialisierbare Abläufe
 2PL ist nicht deadlock-frei
 Grund für die Möglichkeit von deadlocks ist die dynamische
Anforderungen von Ressourcen
 Transaktion 1 sperrt 𝐷1 , bearbeitet 𝐷1 und versucht 𝐷2 zu lesen
 blockiert da 𝑇2 Sperre auf 𝐷2 hält
 Transaktion 2 sperrt 𝐷2 , bearbeitet 𝐷2 und möchte 𝐷1 lesen
 blockiert da 𝑇1 𝐷1 sperrt
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 286
Deadlocks
 Mehr als zwei Transaktionen können in einen Deadlock
verwickelt sein
 Deadlocks können durch aktives prüfen von Zyklen im
Wait-For Graph (WFG) detektiert werden
 Transaktionen sind Knoten, Anforderungen von gesperrten
Ressourcen (Daten) sind die Kanten
 Falls ein Zyklus im WFG existiert, dann besteht ein Deadlock
 Deadlocks können auch passiv über Timeouts bestimmt werden
 Falls ein Deadlock auftritt, muss eine Transaktion „abortiert“ werden,
um die Ressourcen frei zu setzen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 287
Warum Serialisierbarkeit nicht
ausreichend ist?
 Serialisierbarkeit (wie vom 2PL garantiert) wird eingehalten
wenn alle Transaktionen korrekt abschließen
 Angesichts von Fehlern, brauchen wir zusätzliche Kriterien, die
korrekte Abläufe garantieren
 recoverable (wiederherstellbar)
 free of cascading aborts (frei von kaskadierenden Aborts)
 strict (strikt)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 288
Warum Serialisierbarkeit nicht
ausreichend ist? (2)
 Das sicherste Korrektheitskriterium ist die strikte
Serialisierbarkeit
(wie sie von einem strikten 2PL geboten wird)
 Aus Performanzgründen bieten kommerzielle Systeme auch
weniger sichere Mechanismen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 289
Striktes 2PL
 Sperren werden nur nach dem Commit (oder Abort) freigegeben
 Commit und Abort
 frühesten Zeitpunkte im Ablauf einer Transaktion wo keine
zusätzlichen Befehle für diese Transaktion ausgeführt werden
müssen
 Scheduler bestimmt dies eindeutig
# locks
ohne zusätzliche Informationen
t
 Striktheit
 Bezug auf die Freigabe der Sperren
BOT
EOT
(wie sie angefordert wurden ist irrelevant, solange es 2PL entspricht)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 290
Levels of isolation
 Levels of isolation
 Werden von kommerziellen DBMSs und SQL benutzt
 Kontrolliert das Ausmaß, in dem eine Transaktion den Aktionen
konkurrierender Transaktionen ausgesetzt ist
 Es existieren fünf levels of isolation in SQL unter denen
der Benutzer auswählen kann
 READ UNCOMMITTED
 READ COMMITTED
 REPEATABLE READ
 SERIALIZABLE
 SNAPSHOT
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 291
Levels of isolation (2)
 Definition (SQL)
 SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ
| SNAPSHOT | SERIALIZABLE };
 Wahl des entsprechenden level of isolation
 Benutzer kann zwischen höherer Performanz und
höherer Sicherheit vor nebenläufigen Änderungen wählen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 292
Isolation level: SERIALIZABLE
 Isolation level SERIALIZABLE
 Entspricht (dem von uns besprochenen) strikten 2PL
 Sperren werden nach 2PL angefordert
und zum Commit freigegeben
 SERIALIZABLE setzt zusätzlich Index-Sperren
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 293
Isolation level: REPEATABLE READ
 Isolation level REPEATABLE READ
 Eine Transaktion sieht nur die Änderungen von Transaktionen, die
mit Commit abgeschlossen wurden
 Von T gelesen oder geschriebene Werte, werden nicht verändert,
bis T abgeschlossen ist
 Benutzt die gleiche Sperrstrategie wie „isolation level serializable“,
setzt aber keine Indexsperren
 Phantome können auftauchen (d.h. Datensätze, die von einer
nebenläufigen Transaktion eingefügt wurden,
werden manchmal gesehen und manchmal nicht)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 294
Isolation level: READ COMMITTED
 Isolation level READ COMMITTED
 Transaktion liest nur von Transaktionen, die mit Commit
abgeschlossen wurden
 Von T geschriebene Werte können nicht von einer anderen
Transaktion verändert werden, bevor T abschließt
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 295
Isolation level: READ COMMITTED (2)
 Isolation level READ COMMITTED
 Nebenläufige Transaktionen können jeden von T gelesenen Wert
verändern
 Exklusive Sperren werden vor dem Schreiben gesetzt und bis zum
Ende der Transaktion gehalten
 Lesesperren werden vor dem Lesen angefordert, aber wieder frei
gegeben (garantiert nur, dass Werte von korrekt abgeschlossenen
Transaktionen gelesen werden)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 296
Isolation level: READ UNCOMMITTED
 Isolation level READ UNCOMMITTED
 Erlaubt einer Transaktion die Werte zu lesen,
die eine nebenläufige Transaktion geschrieben hat,
bevor diese terminiert
 Das gelesene Objekt kann im Laufe der Transaktion
noch weiterhin verändert werden
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 297
Isolation level: SNAPHSOT
 Isolation level SNAPSHOT
 Innerhalb einer Transaktion gelesene Daten spiegeln niemals
Änderungen wider, die von anderen gleichzeitigen Transaktionen
durchgeführt wurden
 Erstellt keine Sperren beim Lesen von Daten
 Blockiert das Schreiben von Daten durch andere Transaktionen nicht
 Beim Schreiben von Daten blockieren Transaktionen das Lesen von
Daten durch andere Transaktionen nicht
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 298
Struktur der Vorlesung
Data Engineering
Überblick,
Architektur
Datenmodelle
Entwicklung und
Ausblick
Transaktionen
SQL und
Programmiersprachen
ER-Datenmodell
Relationales
Datenmodell
SQL
Entwurfstheorie
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 300
Hist. Überblick: Steinzeit 1960-1970
 Direkter Zugriff auf Daten
über Adresse (Platten)
Girokonto
AP+Dateiverw
Girokonto
Daten
 Große Datenmengen weiterhin
auf Magnetband
 Speicherung als anwendungs-
Sparkonto
AP+Dateiverw
spezifische Daten in Dateien
 Zugriffsmechanismen in Anwendung
eingebettet
 Batch dominiert, erste online Versuche
Hypotheken
AP+Dateiverw
 Strukturen: Sätze (fest + variabel)
 Zugriff: sequentiell, direkt (hash), index-sequentiell
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 301
Sparkonto
Daten
Hypotheken
Daten
Mittelalter (1965-1970)
 Datenzugriffscode wird aus Anwendung herausgenommen
 Sekundärindizes
 Synchronisierung (falls vorhanden) im AP
 Keine Inhaltskontrolle (Redundanz)
 Keine Datenunabhängigkeit
 Kein Recovery / Backup
 Zugriffskontrolle auf
Girokonto
Appl. &
Dateiverw.
Zugriffsmech.
Sparkonto
Appl. &
Dateiverw.
Zugriffsmech.
Daten
Dateiebene
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 302
Daten
Standard DBMS (1970 +)
 Strukturen
 Feste / variable Datensätze (Records, Tupel)
 Zugriff
 Sequentiell, verschiedene Indexmethoden,
batch / online navigierend / mengenorientiert
 Inhaltskontrolle durch Schema
 Redundanz
 Integrität (Format, key-uniqueness, referentielle Integrität)
 Transaktionsverhalten (ACID)
 Nebenläufigkeit, Recovery
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 303
Standard DBMS (1970 +)
 Datenunabhängigkeit (3 Schema Architektur)
 Extern
 Benutzersicht
Girokonto
 Konzeptuell
Zugriff
 Globale Sicht
 Intern
DBMS
Synchron.
Sparkonto
 Physische Abbildung
Sicherheit
Konsistenz
Kredite
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 304
Giro
SparKreditdaten
Objektorientierte DBS (1985+)
 Strukturen
 Elementare und komplexe Objekte mit Typ / Klassen-Hierarchie
 Objekte kapseln Datenstrukturen und objektspezifische Ops.
 Inhaltskontrolle
 Redundanz und Konsistenz durch Vererbung und Typhierarchie
 Bei Missbrauch unkontrollierte Redundanz durch
benutzerspezifizierte Objekte
 Datenunabhängigkeit
 Kapselung und einheitliche externe Schnittstelle
 Kontrolle mehr durch Nutzer und weniger durch DBA
 Nicht-standard Anwendungen (CAD, GIS)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 305
Objekt-relationale DBMS (1995 +)
 Kombination von Stärken der relationalen DBMS mit OOEigenschaften
 SQL, Optimierung, einfache Datenstrukturen, Robustheit
 Benutzerdefinierte Typen, komplexe Objekte, benutzerdefinierte
Funktionen, Extender / Datablades / Cartridges
 Existierende Datenbestände können weiterbenutzt werden
 Einbindung von Legacy-Systemen
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 306
XML-basierte Systeme (1998 +)
 XML bringt Struktur und (etwas) Semantik in Web-basierte
Daten
 XML ist Untermenge von SGML (= Metasprache)
 HTML (= Anwendung)
 Speicherung von XML Dokumenten in der Datenbank
 Tabellen
 BLOBS (Binary Large Objects)
 Besondere Speicherstrukturen
 Kombinierter Zugriff auf strukturierte (tabellarische) Daten und
XML Dokumente
 Kombination aus SQL und XML Anfragestandards
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 307
Client / Server und Verteilte DBS
 2-tier Client / Server
 Alle Daten beim Server gespeichert,
Server kontrolliert TX, Client nur für Präsentation
 3-tier Client / Server
 Daten bei (mehreren) Servern, Clients für Präsentation,
TP Monitor kontrolliert Ablauf der Transaktion,
load-balancing, asynchrone Transaktionsbearbeitung
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 308
Client / Server und Verteilte DBS
 Homogene verteilte DBS
 Volle DBMS Funktionalität an jedem Knoten,
Verteilung für Benutzer transparent
 Alle Knoten stark gekoppelt und unter Kontrolle eines DBMS,
alle TX über gemeinsames DBMS, synchrones 2PC
 Heterogene verteilte DBS
 Knoten benutzen heterogene DBMS, lokale TX möglich
 Schwache Kopplung, gut für Zugriff,
bei Update schwer Konsistenz zu wahren
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 309
Small Footprint, alternativer
Speicher(2002 +)
 Datenbanken auf Kleinstrechnern
 PDA, Chipkarten, Mobiltelefon, Wireless Sensor Node, usw.
 Datenspeicher besteht aus alternativen Speichermedien (Flash/SSD,
PCM)
 Asymmetrisches Schreiben/Lesen
 2-3 Gößenordnungen schneller als Magnetplatte  Parallelität weniger wichtig
 Neue DBMS-Architekturen
 Nebenläufigkeit (Concurrency Control)
 Recovery
 Indexing
 Optimierung
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 310
Streaming Data
 Cyberphysical Systems
 Daten werden durch Sensorik erfasst (keine menschliche Intervention
 Daten „fließen“
 Filter statt Queries
 Definition von Fenstern (windows)
 Aggregieren von Daten in Datenströmen
 Asynchrone Speicherung
 Umkehrung der typischen Datenzugriffe
query
Data
DB
result
Data
result
Data
DB query
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 311
Filter
Agg.
DB
2002+ Hadoop, MapReduce and
NoSQL
RDBMS
MapReduce
Größe
GB – TB
TB – PB
Zugriff
Interaktiv & Batch
Batch
Update
Wiederholt
lesen/schreiben
Einmaliges Schreiben,
Wiederholtes Lesen
Struktur
Statisches Schema
Dynamisches Schema
Integrität
hoch
niedrig
Skalierbarkeit
linear
linear
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 312
Graphendatenbanken
Daten und deren Beziehungen werden als gerichteter Graph
dargestellt
• Knoten im Graphen sind typischerweise Subjekte/Objekte
• Kanten sind die Beziehungen zwischen den Knoten
liebt
Bob
Alice
kennt
kennt
hasst
Ted
liebt
Carol
(liebt, Bob, Alice)
(liebt, Ted, Carol)
(liebt, Carol, Ted)
(hasst, Alice, Carol)
(kennt, Bob, Carol)
(kennt, Bon, Ted)
liebt
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 313
Column Stores
• Herkömmliche Datenbanksysteme speichern die Daten reihenweise (als
Records in den Seiten des Plattenspeichers)
• Zugriff auf ein Attribut in vielen Tupeln ist ineffizient
• Data Warehousing  Aggregierung aller Werte eines Attributs (max, avg, etc.)
• 10 Attribute, wollen Durchschnittswert von einem Attribute
• 90% Ersparnis gegenüber zeilenweise Speicherung (ein Plattenzugriff bringt mehr und
nur relevante Werte in den Hauptspeicher)
• Leichter zu komprimieren
• Tupel können über Join wieder zusammengefügt werden (effizient weil positionsbasiert)
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 314
Hauptspeicherdatenbanken
• Billiger Hauptspeicher ermöglicht für viele Anwendungen die
gesamte Datenbank im Hauptspeicher zu halten
• Geschwindigkeit
• Keine Kontextwechsel
• Serielle Ausführung von Transaktionen dadurch kein Overhead
(Sperrenverwaltung), bei vielen Prozessoren andere CC Methoden
• Müssen aus Sicherheitsgründen physikalische Datenbank auf nicht-
flüchtigem Speicher halten (asynchron)
• Recovery (einlesen von Back-up) sehr langsam, daher Spiegelung
mit hot-standby
12.06.2015 | Fachbereich Informatik | Datenbanken und Verteilte Systeme | Kanonik DKE | 315