close

Anmelden

Neues Passwort anfordern?

Anmeldung mit OpenID

1 Was sind Datenbanken? 4 TU Ilmenau/Uni Magdeburg, WS - ITI

EinbettenHerunterladen
1 Was sind Datenbanken?
Datenbanksysteme
Prof. Dr.-Ing. Kai-Uwe Sattler1
Prof. Dr. Gunter Saake2
1 TU Ilmenau
FG Datenbanken & Informationssysteme
2 Universität Magdeburg
Institut für Technische Informationssysteme
Wintersemester 2006/7
Datenbanksysteme
Sattler / Saake
Wintersemester 2006/7
0–1
Wintersemester 2006/7
1–2
Einführung
Zugrundeliegendes Lehrbuch
A. Heuer; G. Saake:
Datenbanken — Konzepte und
Sprachen
2. Auflage, mitp-Verlag, 2000
704 Seiten, 35.28 e
3. Auflage in Vorbereitung
Sattler / Saake
Datenbanksysteme
Einführung
Überblick
1
Was sind Datenbanken – Grundlegende Konzepte
2
Relationale Datenbanken – Daten als Tabellen
3
Datenbankentwurf im ER-Modell
4
Relationaler DB-Entwurf und Entwurfstheorie
5
Grundlagen von Anfragen: Algebra & Kalkül
6
SQL und weitere relationale Anfragesprachen
7
Integrität, Transaktionen und Trigger
8
Sichten und Zugriffskontrolle
9
Datenaustausch mit XML
Sattler / Saake
4
Datenbanksysteme
Wintersemester 2006/7
1–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
Einführung
Organisatorisches: Ilmenau
Dozent: Kai-Uwe Sattler
Infos (Zeiten, Räume) & Folienkopien unter
http://www.tu-ilmenau.de/dbis/ → Lehre
Übungen (siehe Übungsplan)
Übungen zum Vorlesungsstoff; „Hausaufgaben“
Projektübungen: praktische Arbeit mit SQL
Prüfung
Klausur 60 min
Feedback, Fragen, . . .
Meckerecke: Vorlesungsseite
Sprechzeiten: CC 410, Di 14:00-15:00 Uhr
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
1–4
Einführung
Organisatorisches: Magdeburg
Dozent: Gunter Saake
(Büro: 29-110, email: saake@iti.cs.uni-magdeburg.de)
Übungsleiter: Andreas Lübcke, Axel Hoffmann
begleitende Übungen (ab dritter Woche):
Di 11:00 - 13:00, G05-211, Andreas Lübcke
Mi 9:00 - 11:00, G22A-120, Axel Hoffmann
Mi 11:00 - 13:00, G22A-110, Andreas Lübcke
Do 13:00 - 15:00, G05-211, Axel Hoffmann
Fr 15:00 - 17:00, G22A-122, N.N.
Scheinkriterien: Übungsleiter
Prüfung: Klausur
Sprechzeiten: ALG 110, Fr 10:30-11:15 Uhr
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
1–5
Wintersemester 2006/7
1–6
Einführung
Weitere Literatur
G. Vossen.
Datenbankmodelle, Datenbanksprachen und
Datenbankmanagement-Systeme.
Oldenbourg-Verlag, München, 2000
R. Elmasri, S.B. Navathe.
Grundlagen von Datenbanksystemen.
Pearson, 2002
A. Kemper, A. Eickler.
Datenbanksysteme. Eine Einführung.
Oldenbourg-Verlag, München, 2004
A. Heuer, G. Saake, K. Sattler.
Datenbanken kompakt
2. Aufl., mitp-Verlag, Bonn, August 2003
G. Lausen.
Datenbanken – Grundlagen und XML-Technologien
Elsevier GmbH, 2005
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
5
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Teil II
Was sind Datenbanken?
Was sind Datenbanken?
Was sind Datenbanken?
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–1
Motivation & Historie
Charakteristika von Datenbanken
Eine Datenbank hat die (langfristige) Aufbewahrung von Daten als
Aufgabe.
Die Sicherheit vor Verlusten ist eine Hauptmotivation, etwas „auf
die Bank zu bringen“.
Eine Bank bietet Dienstleistungen für mehrere Kunden an, um
effizient arbeiten zu können.
Sattler / Saake
4
Datenbanksysteme
Wintersemester 2006/7
2–2
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Motivation & Historie
Anwendungsbeispiel: Musikversand
Titel
Musiker
Jahr
Preis
Rezension(en)
Tracks
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–3
Motivation & Historie
Daten in Tabellenform (SQL)
Albumtabelle speichert relevante Informationen zu CD-Alben
(Titel, Erscheinungsjahr, Genre, etc.); jedes Album wird über die
Albumnummer identifiziert
Musikertabelle mit Informationen zu den Künstlern (Name, ggf.
Vorname, Herkunftsland, . . . ); Identifikation erfolgt durch eine
Personennummer
die einzelnen Songs werden mit Titel, Position auf dem Album und
Laufzeit in der Tracktabelle gespeichert
weitere Tabelle(n) mit Bewertungen, Kritiken usw.
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–4
Wintersemester 2006/7
2–5
Motivation & Historie
Daten in Tabellenform — Beispiel
Musiker MNr
103
104
105
Album ANr
1014
1015
1016
1021
Track ANr
1014
1014
1014
...
1015
Sattler / Saake
Name
Apocalyptica
Subway To Sally
Rammstein
Titel
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Land
Finnland
Deutschland
Deutschland
Jahr
2006
2005
2005
2003
Genre
Rock
Rock
Rock
Rock
MNr
103
104
105
104
Pos
1
2
3
Titel
Enter Sandmann
Haramgeddon
Nothing Else Matters
Zeit
3:41
4:55
4:45
1
Saraband de Noir
0:55
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
5
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Was sind Datenbanken?
Motivation & Historie
Ohne Datenbanken: Datenredundanz
Basis- oder Anwendungssoftware verwaltet ihre eigenen Daten in
ihren eigenen (Datei-)Formaten, z.B. in Unternehmen:
Textverarbeitung: Texte, Artikel und Adressen
Buchhaltung: Artikel, Adressen
Lagerverwaltung: Artikel, Aufträge
Auftragsverwaltung: Aufträge, Artikel, Adressen
CAD-System: Artikel, Technische Bausteine
Daten sind redundant: mehrfach gespeichert; Probleme:
Verschwendung von Speicherplatz, „Vergessen“ von Änderungen;
keine zentrale, „genormte“ Datenhaltung
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–6
Motivation & Historie
Ohne Datenbanken: Datenredundanz /2
Andere Software-Systeme können große Mengen von Daten nicht
effizient verarbeiten
Mehrere Benutzer oder Anwendungen können nicht parallel auf
den gleichen Daten arbeiten, ohne sich zu stören
Anwendungsprogrammierer / Benutzer können Anwendungen
nicht programmieren / benutzen, ohne
interne Darstellung der Daten
Speichermedien oder Rechner
zu kennen (Datenunabhängigkeit nicht gewährleistet)
Datenschutz und Datensicherheit sind nicht gewährleistet
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–7
Motivation & Historie
Mit Datenbanken: Datenintegration
Die gesamte Basis- und Anwendungssoftware arbeitet auf
denselben Daten, z.B. Adressen und Artikel werden nur einmal
gespeichert
Datenbanksysteme können große Datenmengen effizient verwalten
(Anfragesprachen, Optimierung, Interne Ebene)
Benutzer können parallel auf Datenbanken arbeiten
(Transaktionskonzept)
Datenunabhängigkeit durch 3-Ebenen-Konzept
Datenschutz (kein unbefugter Zugriff) und Datensicherheit (kein
ungewollter Datenverlust) werden vom System gewährleistet
Sattler / Saake
6
Datenbanksysteme
Wintersemester 2006/7
2–8
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Motivation & Historie
Historie
Anfang 60er Jahre: elementare Dateien, anwendungsspezifische
Datenorganisation (geräteabhängig, redundant, inkonsistent)
Ende 60er Jahre: Dateiverwaltungssysteme (SAM, ISAM) mit
Dienstprogrammen (Sortieren) (geräteunabhängig, aber
redundant und inkonsistent)
70er Jahre: Datenbanksysteme (Geräte- und
Datenunabhängigkeit, redundanzfrei, konsistent)
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
Was sind Datenbanken?
2–9
Motivation & Historie
Historie von RDBMS
1970: Ted Codd (IBM) → Relationenmodell als konzeptionelle
Grundlage relationaler DBS
1974: System R (IBM) → erster Prototyp eines RDBMS
zwei Module: RDS, RSS; ca. 80.000 LOC (PL/1, PL/S, Assembler),
ca. 1,2 MB Codegröße
Anfragesprache SEQUEL
erste Installation 1977
1975: University of California at Berkeley (UCB) → Ingres
Anfragesprache QUEL
Vorgänger von Postgres, Sybase, . . .
1979: Oracle Version 2
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–10
Wintersemester 2006/7
2–11
Motivation & Historie
Prinzipien
DBMS: Datenbankmanagementsystem
DBS: Datenbanksystem (DBMS + Datenbank)
Anwendung
...
Anwendung
DBMS
Datenbank
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
7
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Was sind Datenbanken?
Motivation & Historie
Begriffsbildung
Kürzel
DB
Begriff
Datenbank
DBMS
Datenbankmanagementsystem
Datenbanksystem
DBS
Erläuterung
Strukturierter, von DBMS
verwalteter Datenbestand
Software zur Verwaltung
von Datenbanken
DBMS plus Datenbank(en)
Datenbanksysteme
Sattler / Saake
Was sind Datenbanken?
Wintersemester 2006/7
2–12
Motivation & Historie
Prinzipien /2
Grundmerkmale von DBMS
verwalten persistente (langfristig zu haltende) Daten
verwalten große Datenmengen effizient
Datenbankmodell, mit dessen Konzepten alle Daten einheitlich
beschrieben werden (Integration)
Operationen und Sprachen sind deskriptiv, getrennt von einer
Programmiersprache
Transaktionskonzept, Concurrency Control: logisch
zusammenhängende Operationen atomar (unteilbar),
Auswirkungen langlebig, können parallel durchgeführt werden
Datenschutz, Datenintegrität (Konsistenz), Datensicherheit
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–13
Motivation & Historie
Prinzipien /3
Grundprinzip moderner Datenbanksysteme
3-Ebenen-Architektur (physische Datenunabhängigkeit, logische
Datenunabhängigkeit)
Trennung zwischen Schema (etwa Tabellenstruktur) und Instanz
(etwa Tabelleninhalt)
angelehnt an 9 Codd’sche Regeln
Sattler / Saake
8
Datenbanksysteme
Wintersemester 2006/7
2–14
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Motivation & Historie
Die neun Codd’schen Regeln
1
2
3
4
5
6
7
8
9
Integration: einheitliche, nichtredundante Datenverwaltung
Operationen: Speichern, Suchen, Ändern
Katalog: Zugriffe auf Datenbankbeschreibungen im Data
Dictionary
Benutzersichten
Integritätssicherung: Korrektheit des Datenbankinhalts
Datenschutz: Ausschluss unauthorisierter Zugriffe
Transaktionen: mehrere DB-Operationen als Funktionseinheit
Synchronisation: parallele Transaktionen koordinieren
Datensicherung: Wiederherstellung von Daten nach
Systemfehlern
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–15
Architektur
System-Architekturen
Beschreibung der Komponenten eines Datenbanksystems
Standardisierung der Schnittstellen zwischen Komponenten
Architekturvorschläge
ANSI-SPARC-Architektur
Drei-Ebenen-Architektur
Fünf-Schichten-Architektur
beschreibt Transformationskomponenten im Detail
in weiterführenden Büchern und Vorlesungen
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–16
Architektur
ANSI-SPARC-Architektur
ANSI: American National Standards Institute
SPARC: Standards Planning and Requirement Committee
Vorschlag von 1978
Im Wesentlichen Grobarchitektur verfeinert
Interne Ebene / Betriebssystem verfeinert
Mehr Interaktive und Programmier-Komponenten
Schnittstellen bezeichnet und normiert
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
2–17
9
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Was sind Datenbanken?
Architektur
ANSI-SPARC-Architektur /2
Externe Ebene
Konzeptuelle Ebene
Interne Ebene
Optimierer
Plattenzugriff
Anfragen
Auswertung
Updates
P1
DBOperationen
...
Einbettung
Pn
Masken
Data
Dictionary
Sichtdefinition
Datendefinition
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Dateiorganisation
Wintersemester 2006/7
2–18
Architektur
Klassifizierung der Komponenten
Definitionskomponenten: Datendefinition, Dateiorganisation,
Sichtdefinition
Programmierkomponenten: DB-Programmierung mit
eingebetteten DB-Operationen
Benutzerkomponenten: Anwendungsprogramme, Anfrage und
Update interaktiv
Transformationskomponenten: Optimierer, Auswertung,
Plattenzugriffssteuerung
Data Dictionary (Datenwörterbuch): Aufnahme der Daten aus
Definitionskomponenten, Versorgung der anderen Komponenten
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–19
Datenunabhängigkeit
Datenunabhängigkeit und Schemata
Basierend auf DBMS-Grobarchitektur
Entkopplung von Benutzer- und Implementierungssicht
Ziele u.a.:
Trennung von Modellierungssicht und interner Speicherung
Portierbarkeit
Tuning vereinfachen
standardisierte Schnitstellen
Sattler / Saake
10
Datenbanksysteme
Wintersemester 2006/7
2–20
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Datenunabhängigkeit
Schema-Architektur
Zusammenhang zwischen
Konzeptuellen Schema (Ergebnis der Datendefinition)
Internen Schema (Festlegung der Dateiorganisationen und
Zugriffspfade)
Externen Schema (Ergebnis der Sichtdefinition)
Anwendungsprogrammen (Ergebnis der
Anwendungsprogrammierung)
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
Was sind Datenbanken?
2–21
Datenunabhängigkeit
Schema-Architektur /2
Trennung Schema — Instanz
Schema (Metadaten, Datenbeschreibungen)
Instanz (Anwenderdaten, Datenbankzustand oder -ausprägung)
Datenbankschema besteht aus
internem, konzeptuellen, externen Schema und den
Anwendungsprogrammen
im konzeptuellen Schema etwa:
Strukturbeschreibungen
Integritätsbedingungen
Autorisierungsregeln (pro Benutzer für erlaubte DB-Zugriffe)
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–22
Datenunabhängigkeit
Schema-Architektur /3
externes
Schema 1
Konzeptuelles
Schema
internes
Schema
Datenbanksysteme
externes
Schema N
Datendarstellung
Anfragebearbeitung
Sattler / Saake
...
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
2–23
11
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Was sind Datenbanken?
Datenunabhängigkeit
Datenunabhängigkeit /2
Stabilität der Benutzerschnittstelle gegen Änderungen
physisch: Änderungen der Dateiorganisationen und Zugriffspfade
haben keinen Einfluss auf das konzeptuelle Schema
logisch: Änderungen am konzeptuellen und gewissen externen
Schemata haben keine Auswirkungen auf andere externe
Schemata und Anwendungsprogramme
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–24
Datenunabhängigkeit
Datenunabhängigkeit /3
mögliche Auswirkungen von Änderungen am konzeptuellen
Schema:
eventuell externe Schemata betroffen (Ändern von Attributen)
eventuell Anwendungsprogramme betroffen (Rekompilieren der
Anwendungsprogramme, eventuell Änderungen nötig)
nötige Änderungen werden jedoch vom DBMS erkannt und
überwacht
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–25
Datenunabhängigkeit
Ebenen-Architektur am Beispiel
Konzeptuelle Sicht: relationale Darstellung
Musiker MNr
103
104
105
Album ANr
1014
1015
1016
1021
1025
Sattler / Saake
12
Name
Apocalyptica
Subway To Sally
Rammstein
Titel
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Reflections
Land
Finnland
Deutschland
Deutschland
Jahr
2006
2005
2005
2003
2006
Genre
Rock
Rock
Rock
Rock
Rock
Datenbanksysteme
MNr → Musiker
103
104
105
104
103
Wintersemester 2006/7
2–26
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Datenunabhängigkeit
Ebenen-Architektur am Beispiel /2
Externe Sicht: Daten in einer flachen Relation
ANr
1014
1015
1016
1021
1025
Titel
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Reflections
Sattler / Saake
Jahr
2006
2005
2005
2003
2006
Genre
Rock
Rock
Rock
Rock
Rock
Musiker
Apocalyptica
Subway To Sally
Rammstein
Subway To Sally
Apocalyptica
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–27
Datenunabhängigkeit
Ebenen-Architektur am Beispiel /3
Externe Sicht: Daten in einer hierarchisch aufgebauten Relation
Musiker
Album
Jahr
Titel
Apolcalyptica
Subway To Sally
Rammstein
Sattler / Saake
Amplified
Reflections
Nord Nord Ost
Engelskrieger
Rosenrot
2006
2003
2005
2003
2005
Datenbanksysteme
Was sind Datenbanken?
Genre
Rock
Rock
Metal
Rock
Rock
Wintersemester 2006/7
2–28
Datenunabhängigkeit
Ebenen-Architektur am Beispiel /4
Interne Darstellung
1000
1500
2000
Baumzugriff
über
Albumnummer
teilweises
Speichern
der Datensätze
im Baum
1014 Amplified 2006
1015 Nord Nord Ost 2005
19.99 Rock
15.99 Rock
Sattler / Saake
103
104
....
....
Überlaufbereich für
Datensätze
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
2–29
13
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Was sind Datenbanken?
Datenunabhängigkeit
Transaktionen
Transaktion: Einheit der Verarbeitung im Mehrbenutzerbetrieb
Commit: erfolgreicher Abschluss
Abort: Abbruch
zu synchronisierende Einheit im Mehrbenutzerbetrieb
Einheit für Datenwiederherstellung (keine partiellen Effekte)
Datenbanksysteme
Sattler / Saake
Was sind Datenbanken?
Wintersemester 2006/7
2–30
Einsatzgebiete
Eigenschaften aktueller DBMS
Drei-Ebenen-Architektur nach ANSI-SPARC,
einheitliche Datenbanksprache (SQL; Structured Query
Language),
Einbettung dieser Sprache in kommerzielle
Programmiersprachen,
diverse Werkzeuge für die Definition, Anfrage und Darstellung von
Daten und den Entwurf von Datenbank-Anwendungsprogrammen
und der Benutzer-Interaktion, sowie
kontrollierter Mehrbenutzerbetrieb, Zugriffskontrolle und
Datensicherheitsmechanismen
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–31
Einsatzgebiete
Einige konkrete Systeme
(Objekt-)Relationale DBMS
Oracle10g, IBM DB2 V.9, Microsoft SQL Server 2005
MySQL (www.mysql.org), PostgreSQL
(www.postgresql.org), FireBird (www.firebirdsql.org)
Pseudo-DBMS
MS Access
Objektorientierte DBMS
Poet, Versant, ObjectStore
XML-DBMS
Tamino (Software AG), eXcelon
Sattler / Saake
14
Datenbanksysteme
Wintersemester 2006/7
2–32
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Einsatzgebiete
Einsatzgebiete
Klassische Einsatzgebiete:
viele Objekte (15000 Bücher, 300 Benutzer, 100 Ausleihvorgänge
pro Woche, . . . )
wenige Objekttypen (BUCH, BENUTZER, AUSLEIHUNG)
etwa Buchhaltungssysteme, Auftragserfassungssysteme,
Bibliothekssysteme, . . .
Aktuelle Anwendungen:
E-Commerce, entscheidungsunterstützende Systeme (Data
Warehouses, OLAP), NASA’s Earth Observation System
(Petabyte-Datenbanken), Data Mining
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–33
Wintersemester 2006/7
2–34
Einsatzgebiete
Datenbankgrößen
Yahoo! Data Warehouse
Oracle DBMS, Unix, SMP
100 TB
WalMart Data Warehouse
0,5 PB(?)
NCR TeraData;
Produktinfos (Verkäufe etc.) von 2.900 Märkten;
50.000 Anfragen/Woche
Amazon.com
Oracle RAC DBMS, Linux
25 TB
US Library of Congress
nicht digitalisiert
10-20 TB
Google Index
?? TB
> 8 Mrd. Einträge (Dokumente)
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Einsatzgebiete
Datenbankgrößen /2
SAP R/3-Installation der Deutschen Telekom AG (1998)
Financial Accounting: Rechnungen, Zahlungsaufforderungen,
Lastschriften, Mahnungen etc.
15 SAP R/3-Systeme; jedes
verarbeitet 200.000 Rechnungen, 12.000 Mahnungen, 10.000
Änderungen von Kundendaten pro Tag
bis zu jeweils 1000 Nutzer gleichzeitig
über 13.000 Datenbanktabellen
Hardware: 51 Unix Enterprise Servern, 34 EMC-Speichersysteme
(30 TB), 68 Magnetbandsysteme für Backup (Backup in 2h)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
2–35
15
1
1 Was sind Datenbanken?
1 Was sind Datenbanken?
Was sind Datenbanken?
Einsatzgebiete
Entwicklungslinien: 60er Jahre
DBS basierend auf hierarchischem Modell, Netzwerkmodell
Zeigerstrukturen zwischen Daten
Schwache Trennung interne / konzeptuelle Ebene
Navigierende DML
Trennung DML / Programmiersprache
Datenbanksysteme
Sattler / Saake
Was sind Datenbanken?
Wintersemester 2006/7
2–36
Wintersemester 2006/7
2–37
Einsatzgebiete
Entwicklungslinien: 70er und 80er Jahre
Relationale Datenbanksysteme
Daten in Tabellenstrukturen
3-Ebenen-Konzept
Deklarative DML
Trennung DML / Programmiersprache
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Einsatzgebiete
Entwicklungslinien: (80er und) 90er Jahre
Wissensbanksysteme
Daten in Tabellenstrukturen
Stark deklarative DML, integrierte Datenbankprogrammiersprache
Objektorientierte Datenbanksysteme
Daten in komplexeren Objektstrukturen (Trennung Objekt und seine
Daten)
Deklarative oder navigierende DML
Oft integrierte Datenbankprogrammiersprache
Oft keine vollständige Ebenentrennung
Sattler / Saake
16
Datenbanksysteme
Wintersemester 2006/7
2–38
TU Ilmenau/Uni Magdeburg, WS 2006/07
Was sind Datenbanken?
Einsatzgebiete
Entwicklungslinien: heute
Unterstützung für spezielle Anwendungen
Multimediadatenbanken: Verwaltung multimedialer Objekte (Bilder,
Audio, Video)
XML-Datenbanken: Verwaltung semistrukturierter Daten
(XML-Dokumente)
Verteilte Datenbanken: Verteilung von Daten auf verschiedene
Rechnerknoten
Föderierte Datenbanken, Multidatenbanken, Mediatoren:
Integration von Daten aus heterogenen Quellen (Datenbanken,
Dateien, Web-Quellen)
Mobile Datenbanken: Datenverwaltung auf Kleinstgeräten (PDA,
Handy, . . . )
Sattler / Saake
Datenbanksysteme
Was sind Datenbanken?
Wintersemester 2006/7
2–39
Einsatzgebiete
Zusammenfassung
Motivation für Einsatz von Datenbanksystemen
Codd’sche Regeln
3-Ebenen-Schema-Architektur & Datenunabhängigkeit
Einsatzgebiete
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
2–40
17
1
1 Was sind Datenbanken?
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
Relationale Datenbanken – Daten als Tabellen
1
Relationen für tabellarische Daten
2
SQL-Datendefinition
3
Grundoperationen: Die Relationenalgebra
4
SQL als Anfragesprache
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–1
Relationen für tabellarische Daten
Relationenmodell
Konzeptuell ist die Datenbank eine Menge von Tabellen
Musiker MNr
102
103
104
105
106
Album ANr
1014
1015
1016
1021
1027
Name
Land
Neil Young
Apocalyptica
Subway To Sally
Rammstein
Tokio Hotel
Kanada
Finnland
Deutschland
Deutschland
Deutschland
Titel
Jahr
Genre
Preis
MNr
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
2006
2005
2005
2003
2006
Rock
Rock
Rock
Rock
Rock
22,95
8,95
17,95
12,95
16,95
103
104
105
104
102
Tabelle = „Relation“
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–2
Relationen für tabellarische Daten
Darstellung von Relationen und Begriffe
Fett geschriebene Zeilen: Relationenschema
Weitere Einträge in der Tabelle: Relation
Eine Zeile der Tabelle: Tupel
Eine Spaltenüberschrift: Attribut
Ein Eintrag: Attributwert
Attribut
Relationenname
R
Tupel
A1
...
...
An
...
Relationenschema
Relation
...
Sattler / Saake
20
Datenbanksysteme
Wintersemester 2006/7
3–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
2
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
Relationen für tabellarische Daten
Integritätsbedingungen: Schlüssel
Attribute einer Spalte identifizieren eindeutig gespeicherte Tupel:
Schlüsseleigenschaft
etwa MNr für Tabelle Musiker
Musiker MNr
102
103
104
105
Name
Land
Neil Young
Apocalyptica
Subway To Sally
Rammstein
Kanada
Finnland
Deutschland
Deutschland
auch Attributkombinationen können Schlüssel sein!
Schlüssel können durch Unterstreichen gekennzeichnet werden
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–4
Relationen für tabellarische Daten
Integritätsbedingungen: Fremdschlüssel
Schlüssel einer Tabelle können in einer anderen (oder derselben!)
Tabelle als eindeutige Verweise genutzt werden: Fremdschlüssel,
referenzielle Integrität
etwa MNr als Verweise auf Musiker
ein Fremdschlüssel ist ein Schlüssel in einer „fremden“ Tabelle
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–5
Relationen für tabellarische Daten
Fremdschlüssel /2
Musiker MNr
102
103
104
105
106
Album ANr
1014
1015
1016
1021
1027
Sattler / Saake
Name
Land
Neil Young
Apocalyptica
Subway To Sally
Rammstein
Tokio Hotel
Kanada
Finnland
Deutschland
Deutschland
Deutschland
Titel
Jahr
Genre
Preis
MNr → Musiker
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
2006
2005
2005
2003
2006
Rock
Rock
Rock
Rock
Rock
22,95
8,95
17,95
12,95
16,95
103
104
105
104
102
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
3–6
21
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL-Datendefinition
Die Anweisung create table
create table basisrelationenname (
spaltenname1 wertebereich1 [not null],
...
spaltennamek wertebereichk [not null])
Wirkung dieses Kommandos ist sowohl
die Ablage des Relationenschemas im Data Dictionary, als auch
die Vorbereitung einer „leeren Basisrelation“ in der Datenbank
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–7
SQL-Datendefinition
Mögliche Wertebereiche in SQL
integer (oder auch integer4, int),
smallint (oder auch integer2),
float(p) (oder auch kurz float),
decimal(p,q) und numeric(p,q) mit jeweils q
Nachkommastellen,
character(n) (oder kurz char(n), bei n = 1 auch char) für
Zeichenketten (Strings) fester Länge n,
character varying(n) (oder kurz varchar(n) für Strings
variabler Länge bis zur Maximallänge n,
bit(n) oder bit varying(n) analog für Bitfolgen, und
date, time bzw. timestamp für Datums-, Zeit- und kombinierte
Datums-Zeit-Angaben
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–8
SQL-Datendefinition
Beispiel für create table
create table Musiker (
MNr int primary key,
Name varchar(20),
Land varchar(30));
primary key kennzeichnet Spalte als Schlüsselattribut
Sattler / Saake
22
Datenbanksysteme
Wintersemester 2006/7
3–9
TU Ilmenau/Uni Magdeburg, WS 2006/07
2
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL-Datendefinition
create table mit Fremdschlüssel
create table Album (
ANr int primary key,
Titel varchar(50) not null,
Jahr int not null,
Genre varchar(10),
Preis decimal(6,2),
MNr int,
foreign key (MNr)
references Musiker (MNr));
foreign key kennzeichnet Spalte als Fremdschlüssel
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–10
SQL-Datendefinition
Nullwerte
not null schließt in bestimmten Spalten Nullwerte als
Attributwerte aus
Kennzeichnung von Nullwerte in SQL durch null; hier ⊥
null repräsentiert die Bedeutung „Wert unbekannt“, „Wert nicht
anwendbar“ oder „Wert existiert nicht“, gehört aber zu keinem
Wertebereich
null kann in allen Spalten auftauchen, außer in
Schlüsselattributen und den mit not null gekennzeichneten
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–11
SQL-Datendefinition
Weiteres zur Datendefinition in SQL
Neben Primär- und Fremdschlüsseln können in SQL angegeben
werden:
mit der default-Klausel Defaultwerte für Attribute,
mit der create domain-Anweisung benutzerdefinierte
Wertebereiche und
mit der check-Klausel weitere lokale Integritätsbedingungen
innerhalb der zu definierenden Wertebereiche, Attribute und
Relationenschemata
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
3–12
23
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
Grundoperationen: Die Relationenalgebra
Anfrageoperationen auf Tabellen
Basisoperationen auf Tabellen, die die Berechnung von neuen
Ergebnistabellen aus gespeicherten Datenbanktabellen erlauben
Operationen werden zur sogenannten Relationenalgebra
zusammengefasst
Mathematik: Algebra ist definiert durch Wertebereich sowie darauf
definierten Operationen
→ für Datenbankanfragen entsprechen die Inhalte der Datenbank
den Werten, Operationen sind dagegen Funktionen zum
Berechnen der Anfrageergebnisse
Anfrageoperationen sind beliebig kombinierbar und bilden eine
Algebra zum „Rechnen mit Tabellen“ – die sogenannte relationale
Algebra oder auch Relationenalgebra
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
3–13
Grundoperationen: Die Relationenalgebra
Relationenalgebra: Übersicht
Selektion
Projektion
Verbund
Sattler / Saake
a1
b2
b2
c3
a1
b2
c3
a2
b2
b3
c4
a2
b2
c3
a2
b3
b4
c5
a2
b3
c4
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–14
Grundoperationen: Die Relationenalgebra
Selektion σ
Selektion: Auswahl von Zeilen einer Tabelle anhand eines
Selektionsprädikats
σJahr=2006 (Album)
Album ANr
1014
1027
Sattler / Saake
24
Titel
Jahr
Genre
Preis
MNr
Amplified
Living With War
2006
2006
Rock
Rock
22,95
16,95
103
102
Datenbanksysteme
Wintersemester 2006/7
3–15
TU Ilmenau/Uni Magdeburg, WS 2006/07
2
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
Grundoperationen: Die Relationenalgebra
Projektion π
Projektion: Auswahl von Spalten durch Angabe einer Attributliste
πLand (Musiker)
Musiker Land
Kanada
Finnland
Deutschland
Die Projektion entfernt doppelte Tupel.
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–16
Grundoperationen: Die Relationenalgebra
Natürlicher Verbund
Verbund (engl. join): verknüpft Tabellen über gleichbenannte
Spalten, indem er jeweils zwei Tupel verschmilzt, falls sie dort
gleiche Werte aufweisen
Album
Musiker
ANr
Titel
Jahr
...
MNr
Name
Land
1014
1015
1016
1021
1027
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
2006
2005
2005
2003
2006
...
...
...
...
...
103
104
105
104
102
Apocalyptica
Subway To Sally
Rammstein
Subway To Sally
Neil Young
Finnland
Deutschl.
Deutschl.
Deutschl.
Kanada
„Tokio Hotel“ ist im Ergebnis verschwunden
Tupel, die keinen
Partner finden (dangling tuples), werden eliminiert
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–17
Grundoperationen: Die Relationenalgebra
Kombination von Operationen
πTitel,Jahr,MNr (σJahr>2003 (Album))
σLand=’Deutschland’ (Musiker)
ergibt
Titel
Jahr
MNr
Name
Land
Nord Nord Ost
Rosenrot
2005
2005
104
105
Subway To Sally
Rammstein
Deutschl.
Deutschl.
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
3–18
25
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
Grundoperationen: Die Relationenalgebra
Umbenennung β
Anpassung von Attributnamen mittels Umbenennung:
Musiker Vorname
Neil
Herbert
Dick
Name
Young
Grönemeyer
Brave
Komponist Vorname
Nachname
Neil
Herbert
Johann Sebastian
Eicca
Young
Grönemeyer
Bach
Toppinen
Angleichen durch:
βName←Nachname (Komponist)
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–19
Grundoperationen: Die Relationenalgebra
Mengenoperationen
Vereinigung r1 ∪ r2 von zwei Relationen r1 und r2 : sammelt die
Tupelmengen zweier Relationen unter einem gemeinsamen
Schema auf
Attributmengen beider Relationen müssen identisch sein
Musiker ∪ βName←Nachname (Komponist)
Vorname
Neil
Herbert
Dick
Johann Sebastian
Eicca
Sattler / Saake
Name
Young
Grönemeyer
Brave
Bach
Toppinen
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–20
Grundoperationen: Die Relationenalgebra
Mengenoperationen /2
Differenz r1 − r2 eliminiert die Tupel aus der ersten Relation, die
auch in der zweiten Relation vorkommen
(βName←Nachname (Komponist)) − Musiker
ergibt:
Vorname
Johann Sebastian
Eicca
Sattler / Saake
26
Datenbanksysteme
Name
Bach
Toppinen
Wintersemester 2006/7
3–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
2
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
Grundoperationen: Die Relationenalgebra
Mengenoperationen /3
Durchschnitt r1 ∩ r2 : ergibt die Tupel, die in beiden Relationen
gemeinsam vorkommen
Musiker ∩ βName←Nachname (Komponist)
liefert:
Vorname
Neil
Herbert
Sattler / Saake
Name
Young
Grönemeyer
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–22
SQL als Anfragesprache
SQL-Anfrage als Standardsprache
Anfrage an eine einzelne Tabelle
select Titel, Jahr
from Album
where Genre = ’Rock’
SQL hat Multimengensemantik — Duplikate in Tabellen werden in
SQL nicht automatisch unterdrückt!
Mengensemantik durch distinct
select distinct Land
from Musiker
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–23
SQL als Anfragesprache
Verknüpfung von Tabellen
Kreuzprodukt als Basisverknüpfung
select *
from Album, Musiker
Verbund durch Angabe einer Verbundbedingung!
select *
from Album, Musiker
where Album.MNr = Musiker.MNr
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
3–24
27
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Kombination von Bedingungen
Ausdruck in Relationenalgebra
πTitel,Jahr,MNr (σJahr>2003 (Album))
σLand=’Deutschland’ (Musiker)
Anfrage in SQL
select Titel, Jahr, Album.MNr, Name, Land
from Album, Musiker
where Jahr > 2003 and
Land = ’Deutschland’ and
Album.MNr = Musiker.MNr
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–25
Wintersemester 2006/7
3–26
SQL als Anfragesprache
Mengenoperationen in SQL
Vereinigung in SQL explizit mit union
Differenzbildung durch geschachtelte Anfragen
select *
from Musiker
where Name not in (
select Nachname
from Komponist)
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Änderungsoperationen in SQL
insert: Einfügen eines oder mehrerer Tupel in eine Basisrelation
oder Sicht
update: Ändern von einem oder mehreren Tupel in einer
Basisrelation oder Sicht
delete: Löschen eines oder mehrerer Tupel aus einer
Basisrelation oder Sicht
Lokale und globale Integritätsbedingungen müssen bei
Änderungsoperationen automatisch vom System überprüft
werden
Sattler / Saake
28
Datenbanksysteme
Wintersemester 2006/7
3–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
2
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Die update-Anweisung
Syntax:
update basisrelation
set
attribut1 = ausdruck1
...
attributn = ausdruckn
[ where bedingung ]
Datenbanksysteme
Sattler / Saake
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–28
SQL als Anfragesprache
Beispiel für update
Album ANr
1014
1015
1016
1021
1027
Titel
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
Jahr
2006
2005
2005
2003
2006
Genre
Rock
Rock
Rock
Rock
Rock
Preis
22,95
8,95
17,95
12,95
16,95
MNr
103
104
105
104
102
update Album
set Preis = Preis - 2,50
where Jahr < 2005
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–29
SQL als Anfragesprache
Beispiel für update: neue Werte
Album ANr
1014
1015
1016
1021
1027
Sattler / Saake
Titel
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
Jahr
2006
2005
2005
2003
2006
Datenbanksysteme
Genre
Rock
Rock
Rock
Rock
Rock
Preis
22,95
8,95
17,95
10,45
16,95
MNr
103
104
105
104
102
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
3–30
29
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Weiteres zu update
Realsierung von Eintupel-Operation mittels Primärschlüssel:
update Album
set Preis = Preis * 0.9
where ANr = 1021
Änderung der gesamten Relation:
update Album
set Genre = ’Rock’
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–31
Wintersemester 2006/7
3–32
Wintersemester 2006/7
3–33
SQL als Anfragesprache
Die delete-Anweisung
Syntax:
delete
from basisrelation
[ where bedingung ]
Löschen eines Tupels in der Musiker-Relation:
delete from Musiker
where Name = ’Tokio Hotel’
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Weiteres zu delete
Standardfall ist das Löschen mehrerer Tupel:
delete from Album
where Genre = ’Pop’
Löschen der gesamten Relation:
delete from Album
Sattler / Saake
30
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
2
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Weiteres zu delete /2
Löschoperationen können zur Verletzung von
Integritätsbedingungen führen!
Beispiel: Verletzung der Fremdschlüsseleigenschaft, falls es noch
Bestellposten zu dieser Bestellung gibt:
delete from Musiker
where Name = ’Subway To Sally’
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–34
SQL als Anfragesprache
Die insert-Anweisung
Syntax:
insert
into basisrelation
[ (attribut1 , ..., attributn ) ]
values (konstante1 , ..., konstanten )
optionale Attributliste ermöglicht das Einfügen von
unvollständigen Tupeln
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–35
SQL als Anfragesprache
insert-Beispiele
insert
into Musiker (MNr, Name)
values (110, ’In Extremo’)
nicht alle Attribute angegeben
Land wird null
Wert des fehlenden Attribut
insert
into Musiker
values (110, ’In Extremo’, ’Deutschland’)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
3–36
31
2 Relationale Datenbanken
Relationale Datenbanken – Daten als Tabellen
SQL als Anfragesprache
Einfügen von berechneten Daten
Syntax:
insert
into basisrelation
[ (attribut1 , ..., attributn ) ]
SQL-anfrage
Beispiel:
insert into Album (ANr, Titel, Jahr, Genre, MNr)
( select SingleNr, Titel, Jahr, ’Rock’, MNr
from Singles
where Genre = ’Rock’ )
Sattler / Saake
Datenbanksysteme
Relationale Datenbanken – Daten als Tabellen
Wintersemester 2006/7
3–37
SQL als Anfragesprache
Zusammenfassung
Relationenmodell: Datenbank als Sammlung von Tabellen
Integritätsbedingungen im Relationenmodell
Tabellendefinition in SQL
Relationenalgebra: Anfrageoperatoren
Grundkonzepte von SQL-Anfragen und -Änderungen
Sattler / Saake
32
Datenbanksysteme
Wintersemester 2006/7
3–38
TU Ilmenau/Uni Magdeburg, WS 2006/07
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
DB-Entwurf im ER-Modell
1
Datenbankentwurf
2
Datenbankmodell
3
ER-Modell
4
Erweiterungen des ER-Modells
5
Weiteres Vorgehen beim Entwurf
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–1
Datenbankentwurf
Entwurfsaufgabe
Datenhaltung für mehrere Anwendungssysteme und mehrere
Jahre
daher: besondere Bedeutung
Anforderungen an Entwurf
Anwendungsdaten jeder Anwendung sollen aus Daten der
Datenbank ableitbar sein (und zwar möglichst effizient)
nur „vernünftige“ (wirklich benötigte) Daten sollen gespeichert
werden
nicht-redundante Speicherung
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–2
Datenbankentwurf
Phasenmodell
Anforderungsanalyse
Konzeptioneller
Entwurf
Verteilungsentwurf
Logischer Entwurf
Datendefinition
Physischer Entwurf
Implementierung &
Wartung
Sattler / Saake
34
Datenbanksysteme
Wintersemester 2006/7
4–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Datenbankentwurf
Anforderungsanalyse
Vorgehensweise: Sammlung des Informationsbedarfs in den
Fachabteilungen
Ergebnis:
informale Beschreibung (Texte, tabellarische Aufstellungen,
Formblätter, usw.) des Fachproblems
Trennen der Information über Daten (Datenanalyse) von den
Information über Funktionen (Funktionsanalyse)
„Klassischer“ DB-Entwurf:
nur Datenanalyse und Folgeschritte
Funktionsentwurf:
siehe Methoden des Software Engineering
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–4
Datenbankentwurf
Konzeptioneller Entwurf
erste formale Beschreibung des Fachproblems
Sprachmittel: semantisches Datenmodell
Vorgehensweise:
Modellierung von Sichten z.B. für verschiedene Fachabteilungen
Analyse der vorliegenden Sichten in Bezug auf Konflikte
Integration der Sichten in ein Gesamtschema
Ergebnis: konzeptionelles Gesamtschema, z.B. (E)ER-Diagramm
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–5
Datenbankmodell
Grundlagen von Datenbankmodellen
Ein Datenbankmodell ist ein System von Konzepten zur
Beschreibung von Datenbanken. Es legt Syntax und Semantik von
Datenbankbeschreibungen für ein Datenbanksystem fest.
Datenbankbeschreibungen = Datenbankschemata
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–6
35
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Datenbankmodell
Ein Datenbankmodell legt fest...
1
statische Eigenschaften
1
2
2
inklusive der Standard-Datentypen, die Daten über die
Beziehungen und Objekte darstellen können,
dynamische Eigenschaften wie
1
2
3
Objekte
Beziehungen
Operationen
Beziehungen zwischen Operationen,
Integritätsbedingungen an
1
2
Objekte
Operationen
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–7
Datenbankmodell
Datenbankmodelle
Klassische Datenbankmodelle sind speziell geeignet für
große Informationsmengen mit relativ starrer Struktur und
die Darstellung statischer Eigenschaften und
Integritätsbedingungen (also die Bereiche 1(a), 1(b) und 3(a))
Entwurfsmodelle: (E)ER-Modell, UML, . . .
Realisierungsmodelle: Relationenmodell, objektorientierte
Modelle, . . .
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–8
ER-Modell
Das ER-Modell
Entity: Objekt der realen oder der Vorstellungswelt, über das
Informationen zu speichern sind, z.B. Produkte (CD,
Album), Musiker oder Kunde; aber auch Informationen
über Ereignisse, wie z.B. Bestellungen
Relationship: beschreibt eine Beziehung zwischen Entities, z.B. ein
Kunde bestellt ein Album oder ein Album wird von einem
Musiker eingespielt
Attribut: repräsentiert eine Eigenschaft von Entities oder
Beziehungen, z.B. Name eines Kunden, Titel eines
Albums oder Datum einer Bestellung
Sattler / Saake
36
Datenbanksysteme
Wintersemester 2006/7
4–9
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
ER-Beispiel
BestellNr
AlbumNr
Datum
Menge
Titel
Bestellung
Album
umfasst
Preis
eingespielt
von
Versand
bestellt
Telefon
Kunde
Musiker
Name
Land
Name
KundenNr
MNr
Name
Adresse
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–10
ER-Modell
Werte
Werte: primitive Datenelemente, die direkt darstellbar sind
Wertemengen sind beschrieben durch Datentypen, die neben
einer Wertemenge auch die Grundoperationen auf diesen Werten
charakterisieren
ER-Modell: vorgegebene Standard-Datentypen, etwa die ganzen
Zahlen int, die Zeichenketten string, Datumswerte date etc.
jeder Datentyp stellt Wertebereich mit Operationen und
Prädikaten dar
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–11
ER-Modell
Entities
Entities sind die in einer Datenbank zu repräsentierenden
Informationseinheiten
im Gegensatz zu Werten nicht direkt darstellbar, sondern nur über
ihre Eigenschaften beobachtbar
Entities sind eingeteilt in Entity-Typen, etwa E1 , E2 . . .
Album
Menge der aktuellen Entities:
σ(E1 ) = {e1 , e2 , . . . , en }
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–12
37
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Attribute
Attribute modellieren Eigenschaften von Entities oder auch
Beziehungen
alle Entities eines Entity-Typs haben dieselben Arten von
Eigenschaften; Attribute werden somit für Entity-Typen deklariert
AlbumNr
Titel
Album
Preis
textuelle Notation E(A1 : D1 , . . . , Am : Dm )
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–13
ER-Modell
Identifizierung durch Schlüssel
Schlüsselattribute: Teilmenge der gesamten Attribute eines
Entity-Typs E(A1 , . . . , Am )
{S1 , . . . , Sk } ⊆ {A1 , . . . , Am }
in jedem Datenbankzustand identifizieren die aktuellen Werte der
Schlüsselattribute eindeutig Instanzen des Entity-Typs E
bei mehreren möglichen Schlüsselkandidaten: Auswahl eines
Primärschlüssels
Notation: markieren durch Unterstreichung:
E(. . . , S1 , . . . , Si , . . .)
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–14
ER-Modell
Beziehungstypen
Beziehungen zwischen Entities werden zu Beziehungstypen
zusammengefasst
allgemein: beliebige Anzahl n ≥ 2 von Entity-Typen kann an einem
Beziehungstyp teilhaben
zu jedem n-stelligen Beziehungstyp R gehören n Entity-Typen
E1 , . . . , En
Ausprägung eines Beziehungstyps
σ(R) ⊆ σ(E1 ) × σ(E2 ) × · · · × σ(En )
Sattler / Saake
38
Datenbanksysteme
Wintersemester 2006/7
4–15
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Beziehungstypen /2
Notation
eingespielt
von
Album
Musiker
textuelle Notation: R(E1 , E2 , . . . , En )
wenn Entity-Typ mehrfach an einem Beziehungstyp beteiligt:
Vergabe von Rollennamen möglich
verheiratet(Frau: Person, Mann: Person)
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–16
ER-Modell
Beziehungsattribute
Beziehungen können ebenfalls Attribute besitzen
Attributdeklarationen werden beim Beziehungstyp vorgenommen;
gilt auch hier für alle Ausprägungen eines Beziehungstyps
Beziehungsattribute
Menge
Bestellung
umfasst
Album
textuelle Notation: R(E1 , . . . , En ; A1 , . . . , Ak )
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–17
ER-Modell
Merkmale von Beziehungen
Stelligkeit oder Grad:
Anzahl der beteiligten Entity-Typen
häufig: binär
Beispiel: Lieferant liefert Produkt
Kardinalität oder Funktionalität:
Anzahl der eingehenden Instanzen eines Entity-Typs
Formen: 1:1, 1:n, m:n
stellt Integritätsbedingung dar
Beispiel: maximal 5 Produkte pro Bestellung
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–18
39
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Zwei- vs. mehrstellige Beziehungen
ProdId
Produkt
ProdId
Produkt
Name
P-V
Name
K-P
Versand
Versand
bestellt
K-V
KundenNr
Kunde
KundenNr
Kunde
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–19
ER-Modell
Ausprägungen im Beispiel
Produkte
Kunden
K1
K1
P1
K2
P1
K2
P2
V1
Produkte
Kunden
P2
V1
V2
Versand
V2
Versand
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–20
ER-Modell
Rekonstruktion der Ausprägungen
Produkte
Kunden
K1
P1
K1 – P1 – V1
K2
K1 – P2 – V2
P2
K2 – P1 – V2
aber auch: K1 – P1 – V2
V1
V2
Versand
Sattler / Saake
40
Datenbanksysteme
Wintersemester 2006/7
4–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
1:1-Beziehungen
jedem Entity e1 vom Entity-Typ E1 ist maximal ein Entity e2 aus E2
zugeordnet und umgekehrt
Beispiele: Prospekt beschreibt Produkt, Mann ist verheiratet mit
Frau
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–22
ER-Modell
1:N-Beziehungen
jedem Entity e1 vom Entity-Typ E1 sind beliebig viele Entities E2
zugeordnet, aber zu jedem Entity e2 gibt es maximal ein e1 aus E1
Beispiele: Lieferant liefert Produkt, Mutter hat Kinder
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–23
ER-Modell
N:1-Beziehung
invers zu 1:N, auch funktionale Beziehung
zweistellige Beziehungen, die eine Funktion beschreiben:
Jedem Entity eines Entity-Typs E1 wird maximal ein Entity eines
Entity-Typs E2 zugeordnet.
R : E1 → E2
geliefert
von
Produkt
ProdId
Preis
Lieferant
Name
Adresse
Titel
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–24
41
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
M:N-Beziehungen
keine Restriktionen
Beispiel: Bestellung umfasst Produkte
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–25
ER-Modell
[min,max]-Notation
[min1, max1]
E1
[minn, maxn]
R
[min2, max2]
En
...
E2
schränkt die möglichen Teilnahmen von Instanzen der beteiligten
Entity-Typen an der Beziehung ein, indem ein minimaler und ein
maximaler Wert vorgegeben wird
Notation für Kardinalitätsangaben an einem Beziehungstyp
R(E1 , . . . , Ei [mini , maxi ], . . . , En )
Kardinalitätsbedingung: mini ≤ |{r | r ∈ R ∧ r.Ei = ei }| ≤ maxi
Spezielle Wertangabe für maxi ist ∗
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–26
ER-Modell
Kardinalitätsangaben
[0, ∗] legt keine Einschränkung fest (default)
R(E1 [0, 1], E2 ) entspricht einer (partiellen) funktionalen Beziehung
R : E1 → E2 , da jede Instanz aus E1 maximal einer Instanz aus E2
zugeordnet ist
totale funktionale Beziehung wird durch R(E1 [1, 1], E2 ) modelliert
Sattler / Saake
42
Datenbanksysteme
Wintersemester 2006/7
4–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Kardinalitätsangaben: Beispiele
partielle funktionale Beziehung
lagert_in(Produkt[0,1],Fach[0,3])
„Jedes Produkt ist im Lager in einem Fach abgelegt, allerdings
wird ausverkauften bzw. gegenwärtig nicht lieferbaren Produkte
kein Fach zugeordnet. Pro Fach können maximal drei Produkte
gelagert werden.“
totale funktionale Beziehung
liefert(Lieferant[0,*],Produkt[1,1])
„Jedes Produkt wird durch genau einen Lieferant geliefert, aber
ein Lieferant kann durchaus mehrere Produkte liefern.“
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–28
ER-Modell
Alternative Kardinalitätsangabe
[1,1]
geliefert
von
Produkt
N
geliefert
von
Produkt
Sattler / Saake
[0,*]
Lieferant
1
Lieferant
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–29
ER-Modell
Abhängige Entity-Typen
abhängiger Entity-Typ: Identifikation über funktionale Beziehung
Bestellposition
gehört zu
Produkt
PosNr
Bestellung
Datum
BestNr
Menge
Abhängige Entities im ER-Modell: Funktionale Beziehung als
Schlüssel
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–30
43
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Abhängige Entity-Typen /2
Mögliche Ausprägung für abhängige Entities
gehört zu
BestNr: 1011
Datum: 18.02.06
PosNr: 1
Menge: 2
Produkt: Amplified
gehört zu
PosNr: 2
Menge: 1
Produkt: Rosenrot
gehört zu
PosNr: 1
Menge: 1
Produkt: Living With War
BestNr: 1012
Datum: 22.11.06
Bestellposition
Bestellung
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–31
ER-Modell
Abhängige Entity-Typen /3
Alternative Notation
Bestellposition
Bestellung
gehört zu
Produkt
PosNr
BestNr
Datum
Menge
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–32
ER-Modell
Die IST-Beziehung
Spezialisierungs-/Generalisierungsbeziehung oder auch
Beziehung (engl. is-a relationship)
textuelle Notation: E1
IST-Beziehung
IST
IST-
E2
entspricht semantisch einer injektiven funktionalen
Beziehung
IST
Album
Genre
Produkt
ProdId
Laufzeit
Sattler / Saake
44
Preis
Titel
Datenbanksysteme
Wintersemester 2006/7
4–33
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Eigenschaften der IST-Beziehung
Jeder Album-Instanz ist genau eine Produkt-Instanz zugeordnet
Album-Instanzen werden durch die funktionale IST-Beziehung
identifiziert
Nicht jedes Produkt ist zugleich ein Album (z.B. Single, Film, . . . ).
Attribute des Entity-Typs Produkt treffen auch auf Alben zu:
„vererbte“ Attribute
Album(Produkt#,Titel,Preis,Genre,Laufzeit)
von Produkt
nicht nur die Attributdeklarationen vererben sich, sondern auch
jeweils die aktuellen Werte für eine Instanz
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
DB-Entwurf im ER-Modell
4–34
ER-Modell
Alternative Notation für IST-Beziehung
Album
Produkt
Genre
ProdId
Laufzeit
Preis
Titel
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–35
ER-Modell
Kardinalitätsangaben: IST
für Beziehung E1
IST
E2 gilt immer:
IST(E1 [1, 1], E2 [0, 1])
Jede Instanz von E1 nimmt genau einmal an der IST-Beziehung
teil, während Instanzen des Obertyps E2 nicht teilnehmen müssen
Aspekte wie Attributvererbung werden hiervon nicht erfasst
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–36
45
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
ER-Modell
Optionalität von Attributen
Kunde
KundenNr
Adresse
Name
Telefon
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–37
ER-Modell
Weitere Konzepte
Strukturierte Attributwerte im ER-Modell
Kunde
KundenNr
Ort
Adresse
PLZ
Name
Straße
Telefon
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–38
ER-Modell
Weitere Konzepte
Abgeleitete Attributwerte im ER-Modell
Album
Nettopreis
Nettopreis := Preis * 1,17
AlbumNr
Preis
Titel
Sattler / Saake
46
Datenbanksysteme
Wintersemester 2006/7
4–39
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Erweiterungen des ER-Modells
Erweiterungen des ER-Modells
Spezialisierung und Generalisierung
Spezialisierung enstpricht IST-Beziehung:
Album Spezialisierung von Produkt
Generalisierung: Entities in einen allgemeineren Kontext.
Album oder Film als Produkt
Partitionierung: Spezialfall der Spezialisierung, mehrere disjunkte
Entity-Typen.
Partitionierung von Produkten in Album und Film.
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–40
Erweiterungen des ER-Modells
Erweiterungen des ER-Modells /2
Komplexe Objekte
Aggregierung: Entity aus einzelnen Instanzen anderer
Entity-Typen zusammengesetzt.
„Album zusammengesetzt aus Titeln, Bonus-Video, Booklet“
Sammlung oder Assoziation: Mengenbildung.
„Team als Gruppe von Personen“
Beziehungen höheren Typs
Spezialisierung und Generalisierung auch für Beziehungstypen.
Beispiel: Beziehung bestellt zu bestelltPerExpress
spezialisiert.
Beziehungen zwischen Beziehungsinstanzen: Beziehungen
zweiter und höherer Ordnung
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–41
Weiteres Vorgehen beim Entwurf
Weiteres Vorgehen beim Entwurf
ER-Modellierung von verschiedenen Sichten auf
Gesamtinformation, z.B. für verschiedene Fachabteilungen eines
Unternehmens
konzeptueller Entwurf
Analyse und Integration der Sichten
Ergebnis: konzeptionelles Gesamtschema
Verteilungsentwurf bei verteilter Speicherung
Abbildung auf konkretes Implementierungsmodell (z.B.
Relationenmodell)
logischer Entwurf
Datendefinition, Implementierung und Wartung
Entwurf
Sattler / Saake
Datenbanksysteme
physischer
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–42
47
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Weiteres Vorgehen beim Entwurf
Sichtenintegration
Analyse der vorliegenden Sichten in Bezug auf Konflikte
Integration der Sichten in ein Gesamtschema
Sicht #2
Sicht #1
Konsoli-
Globales
Schema
dierung
Sicht #3
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–43
Weiteres Vorgehen beim Entwurf
Integrationskonflikte
Namenskonflikte: Homonyme / Synonyme
Homonyme: Schloss; Kunde
Synonyme: Auto, KFZ, Fahrzeug
Typkonflikte: verschiedene Strukturen für das gleiche Element
Wertebereichskonflikte: verschiedene Wertebereiche für ein
Element
Bedingungskonflikte: z.B. verschiedene Schlüssel für ein
Element
Strukturkonflikte: gleicher Sachverhalt durch unterschiedliche
Konstrukte ausgedrückt
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–44
Weiteres Vorgehen beim Entwurf
Verteilungsentwurf
sollen Daten auf mehreren Rechnern verteilt vorliegen, muss Art
und Weise der verteilten Speicherung festgelegt werden
z.B. bei einer Relation
KUNDE (KNr, Name, Adresse, PLZ, Konto)
horizontale Verteilung:
KUNDE_1 (KNr, Name, Adresse, PLZ, Konto)
where PLZ < 50.000
KUNDE_2 (KNr, Name, Adresse, PLZ, Konto)
where PLZ >= 50.000
vertikale Verteilung (Verbindung über KNr Attribut):
KUNDE_Adr (KNr, Name, Adresse, PLZ)
KUNDE_Konto (KNr, Konto)
Sattler / Saake
48
Datenbanksysteme
Wintersemester 2006/7
4–45
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Weiteres Vorgehen beim Entwurf
Logischer Entwurf
Sprachmittel: Datenmodell des ausgewählten
„Realisierungs“-DBMS z.B. relationales Modell
Vorgehensweise:
1
2
(automatische) Transformation des konzeptionellen Schemas z.B.
ER → relationales Modell
Verbesserung des relationalen Schemas anhand von Gütekriterien
(Normalisierung, siehe Kapitel 5):
Entwurfsziele: Redundanzvermeidung, . . .
Ergebnis: logisches Schema, z.B. Sammlung von
Relationenschemata
Datenbanksysteme
Sattler / Saake
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–46
Weiteres Vorgehen beim Entwurf
Datendefinition
Umsetzung des logischen Schemas in ein konkretes Schema
Sprachmittel: DDL und DML eines DBMS z.B. Oracle, DB2, SQL
Server
Datenbankdeklaration in der DDL des DBMS
Realisierung der Integritätssicherung
Definition der Benutzersichten
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–47
Weiteres Vorgehen beim Entwurf
Physischer Entwurf
Ergänzen des physischen Entwurfs um Zugriffsunterstützung bzgl.
Effizienzverbesserung, z.B. Definition von Indexen
Index
Zugriffspfad: Datenstruktur für zusätzlichen, schlüsselbasierten
Zugriff auf Tupel ( Schlüsselattributwert, Tupeladresse )
meist als B*-Baum realisiert
Sprachmittel: Speicherstruktursprache SSL
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–48
49
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Weiteres Vorgehen beim Entwurf
Indexe in SQL
create [ unique ] index indexname
on relname (
attrname [ asc | desc ],
attrname [ asc | desc ],
...
)
Beispiel
create index AlbumIdx on Album (Titel)
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–49
Weiteres Vorgehen beim Entwurf
Notwendigkeit für Zugriffspfade
Beispiel: Tabelle mit 10 GB Daten, Festplattentransferrate ca. 10
MB/s
Operation: Suchen eines Tupels (Selektion)
Implementierung: sequentielles Durchsuchen
Aufwand: 10.240/10 = 1.024 sec. ≈ 17 min.
Sattler / Saake
Datenbanksysteme
DB-Entwurf im ER-Modell
Wintersemester 2006/7
4–50
Weiteres Vorgehen beim Entwurf
Implementierung und Wartung
Phasen
der Wartung,
der weiteren Optimierung der physischen Ebene,
der Anpassung an neue Anforderungen und Systemplattformen,
der Portierung auf neue Datenbankmanagementsysteme
etc.
Sattler / Saake
50
Datenbanksysteme
Wintersemester 2006/7
4–51
TU Ilmenau/Uni Magdeburg, WS 2006/07
3
3 Datenbankentwurf im ER-Modell
DB-Entwurf im ER-Modell
Weiteres Vorgehen beim Entwurf
Zusammenfassung
Phasen des Datenbankentwurfs
Datenbankmodell, Datenbankschema, Datenbank(instanz)
Entity-Relationship-Modell
ER-Erweiterungen: Spezialisierung, Generalisierung,
Partitionierung
weitere Entwurfsschritte
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
4–52
51
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
1
Zielmodell des logischen Entwurfs
2
ER-Abbildung
3
Relationaler DB-Entwurf
4
Normalformen
5
Transformationseigenschaften
6
Entwurfsverfahren
7
Weitere Abhängigkeiten
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–1
Zielmodell des logischen Entwurfs
Relationenmodell
Musiker MNr
102
103
104
105
106
Name
Land
Neil Young
Apocalyptica
Subway To Sally
Rammstein
Tokio Hotel
Kanada
Finnland
Deutschland
Deutschland
Deutschland
Album AlbumNr
1014
1015
1016
1021
1027
Titel
Jahr
Genre
Preis
MNr
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
2006
2005
2005
2003
2006
Rock
Rock
Rock
Rock
Rock
22,95
8,95
17,95
12,95
16,95
103
104
105
104
102
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–2
Zielmodell des logischen Entwurfs
Begriffe des Relationenmodells
Begriff
Attribut
Wertebereich
Attributwert
Relationenschema
Relation
Tupel
Datenbankschema
Datenbank
Sattler / Saake
54
Informale Bedeutung
Spalte einer Tabelle
mögliche Werte eines Attributs (auch Domäne)
Element eines Wertebereichs
Menge von Attributen
Menge von Zeilen einer Tabelle
Zeile einer Tabelle
Menge von Relationenschemata
Menge von Relationen (Basisrelationen)
Datenbanksysteme
Wintersemester 2006/7
5–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Zielmodell des logischen Entwurfs
Begriff
Schlüssel
Primärschlüssel
Fremdschlüssel
Fremdschlüsselbedingung
Sattler / Saake
4
Begriffe des Relationenmodells /2
Informale Bedeutung
minimale Menge von Attributen, deren
Werte ein Tupel einer Tabelle eindeutig
identifizieren
ein beim Datenbankentwurf ausgezeichneter Schlüssel
Attributmenge, die in einer anderen
Relation Schlüssel ist
alle Attributwerte des Fremdschlüssels
tauchen in der anderen Relation als
Werte des Schlüssels auf
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–4
Zielmodell des logischen Entwurfs
Formalisierung Relationenmodell
Attribute und Domänen
U nichtleere, endliche Menge: Universum
A ∈ U: Attribut
D = {D1 , . . . , Dm } Menge endlicher, nichtleerer Mengen: jedes Di :
Wertebereich oder Domäne
total definierte Funktion dom : U −→ D
dom(A): Domäne von A
w ∈ dom(A): Attributwert für A
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–5
Zielmodell des logischen Entwurfs
Formalisierung Relationenmodell /2
Relationenschemata und Relationen
R ⊆ U: Relationenschema
Relation r über R = {A1 , . . . , An } (kurz: r(R)) ist endliche Menge von
m
Abbildungen t : R −→ i=1 Di , Tupel genannt
Es gilt t(A) ∈ dom(A) (t(A) Restriktion von t auf A ∈ R)
für X ⊆ R analog t(X) X-Wert von t
Menge aller Relationen über R: REL(R) := {r | r(R)}
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–6
55
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Zielmodell des logischen Entwurfs
Formalisierung Relationenmodell /3
Datenbankschema und Datenbank
Menge von Relationenschemata S := {R1 , . . . , Rp }:
Datenbankschema
Datenbank über S: Menge von Relationen d := {r1 , . . . , rp }, wobei
ri (Ri )
Datenbank d über S: d(S)
Relation r ∈ d: Basisrelation
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–7
Zielmodell des logischen Entwurfs
Integritätsbedingungen
Identifizierende Attributmenge K := {B1 , . . . , Bk } ⊆ R:
∀t1 , t2 ∈ r [t1 = t2
=⇒
∃B ∈ K : t1 (B) = t2 (B)]
Schlüssel: ist minimale identifizierende Attributmenge
{Vorname, Nachname, PLZ, Geburtsdatum} und
{KNr} für Kunde
Primattribut: Element eines Schlüssels
Primärschlüssel: ausgezeichneter Schlüssel
Fremdschlüssel: X(R1 ) → Y(R2 )
{t(X)|t ∈ r1 } ⊆ {t(Y)|t ∈ r2 }
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–8
ER-Abbildung
Kapazitätserhöhende Abbildung
A
B
E1
R
R = { A, B }
R = { A, B }
K= {{A}}
K = { { A }, { B } }
A
B
A
B
A
B
1
2
3
3
1
2
3
4
1
2
3
4
Kapazitätserhöhend
Sattler / Saake
56
E2
Kapazitätserhaltend
Datenbanksysteme
Wintersemester 2006/7
5–9
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
ER-Abbildung
A
B
E1
R
E2
R = { A, B }
R = { A, B }
K= {{A}}
K = { { A, B } }
A
B
1
2
3
3
?
A
B
A
B
1
2
3
4
2
2
3
3
4
4
Kapazitätserhaltend
Kapazitätsvermindernd
Sattler / Saake
4
Kapazitätsvermindernde Abbildung
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–10
ER-Abbildung
ER-Abbildung auf Relationen
Entity-Typen und Beziehungstypen: jeweils auf
Relationenschemata
Attribute: Attribute des Relationenschemas, Schlüssel werden
übernommen
Kardinalitäten der Beziehungen: durch Wahl der Schlüssel bei
den zugehörigen Relationenschemata ausgedrücken
in einigen Fällen: Verschmelzen der Relationenschemata von
Entity- und Beziehungstypen
zwischen den verbleibenden Relationenschemata diverse
Fremdschlüsselbedingungen einführen
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–11
ER-Abbildung
Abbildung von Beziehungstypen
neues Relationenschema mit allen Attributen des Beziehungstyps,
zusätzlich Übernahme aller Primärschlüssel der beteiligten EntityTypen
Festlegung der Schlüssel:
m:n-Beziehung: beide Primärschlüssel zusammen werden
Schlüssel im neuen Relationenschema
1:n-Beziehung: Primärschlüssel der n-Seite (bei der funktionalen
Notation die Seite ohne Pfeilspitze) wird Schlüssel im neuen
Relationenschema
1:1-Beziehung: beide Primärschlüssel werden je ein Schlüssel im
neuen Relationenschema, der Primärschlüssel wird dann aus
diesen Schlüsseln gewählt
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–12
57
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
ER-Abbildung
n:m-Beziehungen
BestellNr
ProdNr
Datum
Menge
Bezeichnung
Bestellung
Produkt
umfasst
Preis
Umsetzung
Bestellung (BestellNr, Datum)
Produkt (ProdNr, Bezeichnung, Preis)
Umfasst (BestellNr → Bestellung,
ProdNr → Produkt, Menge)
Attribute BestellNr und ProdNr sind gemeinsam Schlüssel
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–13
ER-Abbildung
1:n-Beziehungen
AlbumNr
Titel
Name
MNr
Preis
Land
eingespielt
von
Album
Musiker
Umsetzung (zunächst)
Album (AlbumNr, Titel, Preis)
Musiker (MNr, Name, Land)
EingespieltVon(AlbumNr, MNr)
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–14
ER-Abbildung
Abhängige Entity-Typen
Bestellposition
gehört zu
Produkt
PosNr
Bestellung
BestNr
Datum
Menge
Umsetzung
Bestellposition(PosNr, BestNr → Bestellung,
Menge, Produkt)
Bestellung(BestNr, Datum)
Sattler / Saake
58
Datenbanksysteme
Wintersemester 2006/7
5–15
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
ER-Abbildung
4
Mögliche Verschmelzungen
optionale Beziehungen ([0,1] oder [0,n]) werden nicht
verschmolzen
bei Kardinalitäten [1,1] oder [1,n] (zwingende Beziehungen)
Verschmelzung möglich:
1:n-Beziehung: das Entity-Relationenschema der n-Seite kann in
das Relationenschema der Beziehung integriert werden
1:1-Beziehung: beide Entity-Relationenschemata können in das
Relationenschema der Beziehung integriert werden
Sattler / Saake
Wintersemester 2006/7
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
5–16
ER-Abbildung
Verschmelzung bei 1:n-Beziehung
AlbumNr
Titel
Name
MNr
Preis
Land
eingespielt
von
Album
Musiker
da Album von einem Musiker/Band eingespielt werden werden
muss (zwingende Beziehung), können Relationenschemata
Album und EingespieltVon verschmolzen werden
Umsetzung
Album (AlbumNr, Titel, Preis,
MNr → Musiker)
Musiker (MNr, Name, Land)
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–17
ER-Abbildung
1:1-Beziehung
Titel
ProdNr
Laufzeit
Format
AlbumNr
Album
gehört zu
DVD-Video
Titel
Standardumsetzung (ohne Verschmelzung):
Album (AlbumNr, Titel, Laufzeit)
DVDVideo (ProdNr, Format, Titel)
Gehört_Zu (AlbumNr → Album,
ProdNr → DVDVideo)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–18
59
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
ER-Abbildung
Umsetzung der 1:1-Beziehung
Umsetzung mit Verschmelzung:
AlbumNr
102
103
Album-Titel
Laufzeit
Raue Spree Live
Live aus Berlin
59:30
52:14
ProdNr
1024
1032
Format
DVD-Titel
PAL
PAL
Raue Spree
Live aus Berlin
Effekt bei „unechter“ 1:1-Beziehung:
AlbumNr
102
103
87
⊥
Album-Titel
Laufzeit
Raue Spree Live
Live aus Berlin
Made in Japan
⊥
59:30
52:14
47:50
⊥
Sattler / Saake
ProdNr
1024
1032
⊥
1142
Format
DVD-Titel
PAL
PAL
⊥
PAL
Raue Spree 2005
Live aus Berlin
⊥
The Life Burns Tour
Wintersemester 2006/7
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
5–19
ER-Abbildung
IST-Beziehung
Titel
Preis
ProdNr
Produkt
Laufzeit
ISBN
Album
Buch
Genre
Verlag
Umsetzung
Produkt (ProdNr, Titel, Preis)
Album (ProdNr → Produkt,
Genre, Laufzeit)
Buch (ProdNr → Produkt, ISBN, Verlag)
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–20
ER-Abbildung
Rekursive Beziehungen
ProdNr
Zubehör
Bezeichnung
ist Zubehör
für
Produkt
Preis
Basis
Umsetzung
Produkt (ProdNr, Bezeichnung, Preis)
Ist_Zubehör_Für (Basis → Produkt,
Zubehör → Produkt)
Sattler / Saake
60
Datenbanksysteme
Wintersemester 2006/7
5–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
ER-Abbildung
Bestellung
BestellNr
4
Mehrstellige Beziehungen
Versand
bestellt
Datum
Telefon
Kunde
Name
Name
KundenNr
Adresse
jeder beteiligte Entity-Typ wird nach den obigen Regeln behandelt
für Beziehung Bestellt werden Primärschlüssel der drei beteiligten EntityTypen in das resultierende Relationenschema aufgenommen
Beziehung ist allgemeiner Art (k:m:n-Beziehung): alle Primärschlüssel bilden
zusammen den Schlüssel
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–22
ER-Abbildung
Übersicht über die Transformationen
ER-Konzept
wird abgebildet auf relationales Konzept
Entity-Typ Ei
Attribute von Ei
Primärschlüssel Pi
Beziehungstyp
Relationenschema Ri
Attribute von Ri
Primärschlüssel Pi
Relationenschema
Attribute: P1 , P2
weitere Attribute
P2 wird Primärschlüssel der Beziehung
P1 und P2 werden Schlüssel der Beziehung
P1 ∪ P2 wird Primärschlüssel der Beziehung
R1 erhält zusätzlichen Schlüssel P2
dessen Attribute
1:n
1:1
m:n
IST -Beziehung
E1 , E2 : an Beziehung beteiligte Entity-Typen,
P1 , P2 : deren Primärschlüssel,
1 : n-Beziehung: E2 ist n-Seite,
IST -Beziehung:
E1 ist speziellerer Entity-Typ
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–23
Relationaler DB-Entwurf
Relationaler DB-Entwurf: Überblick
Verfeinern des logischen Entwurfs
Ziel: Vermeidung von Redundanzen durch Aufspalten von
Relationenschemata, ohne gleichzeitig
semantische Informationen zu verlieren (Abhängigkeitstreue)
die Möglichkeit zur Rekonstruktion der Relationen zu verlieren
(Verbundtreue)
Redundanzvermeidung durch Normalformen (s.u.)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–24
61
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf
Relation mit Redundanzen
AlbumNr
1003
1014
1015
1016
1021
1027
1030
Titel
Jahr
Genre
MNr
Name
Land
Reflections
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
Raue See
2003
2006
2005
2005
2003
2006
2006
Rock
Rock
Rock
Rock
Rock
Rock
Rock
103
103
104
105
104
102
107
Apocalyptica
Apocalyptica
Subway To Sally
Rammstein
Subway To Sally
Neil Young
In Extremo
Finnland
Finnland
BRD
BRD
BRD
Kanada
BRD
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–25
Relationaler DB-Entwurf
Redundanzen
Redundanzen in Basisrelationen aus mehreren Gründen
unerwünscht:
Redundante Informationen belegen unnötigen Speicherplatz
Änderungsoperationen auf Basisrelationen mit Redundanzen nur
schwer korrekt umsetzbar: wenn eine Information redundant
vorkommt, muss eine Änderung diese Information in allen ihren
Vorkommen verändern
mit normalen relationalen Änderungsoperationen und den in
relationalen Systemen vorkommenden lokalen
Integritätsbedingungen (Schlüsseln) nur schwer realisierbar
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–26
Relationaler DB-Entwurf
Update-Anomalien
Einfügen in die mit Redundanzen behaftete Album-Relation:
AlbumNr
1029
Titel
Jahr
Genre
MNr
Name
Land
7
2003
Rock
104
In Extremo
Frankreich
Integritätsbedingung(en) verletzt, die in dieser Relation durch eine
Schlüsselbedingung nicht spezifiziert werden kann
Band mit der MNr 104 ist eigentlich „Subway To Sally“
„In Extremo“ kommt aus Deutschland (siehe AlbumNr 130)
Sattler / Saake
62
Datenbanksysteme
Wintersemester 2006/7
5–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf
4
Funktionale Abhängigkeiten
funktionale Abhängigkeit zwischen Attributmengen X und Y einer
Relation
Wenn in jedem Tupel der Relation der Attributwert unter den XKomponenten den Attributwert unter den Y-Komponenten festlegt.
Unterscheiden sich zwei Tupel in den X-Attributen nicht, so haben
sie auch gleiche Werte für alle Y-Attribute
Notation für funktionale Abhängigkeit (FD, von functional
dependency): X → Y
Beispiel:
AlbumNr → Titel, MNr, Name
MNr
→ Land
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–28
Relationaler DB-Entwurf
Schlüssel als Spezialfall
für Beispiel auf Folie 5-25
AlbumNr → Titel, Jahr, Genre, MNr, Name,
Land
Immer: AlbumNr → AlbumNr, dann gesamtes Schema auf
rechter Seite
Wenn linke Seite minimal: Schlüssel
Formal: Schlüssel X liegt vor, wenn für Relationenschema R FD
X → R gilt und X minimal
Ziel des Datenbankentwurfs: alle gegebenen funktionalen
Abhängigkeiten in „Schlüsselabhängigkeiten“ umformen, ohne dabei
semantische Information zu verlieren
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–29
Wintersemester 2006/7
5–30
Relationaler DB-Entwurf
Ableitung von FDs
r A
a1
a2
a3
a4
B
b1
b1
b2
b1
C
c1
c1
c1
c1
genügt A → B und B → C
dann gilt auch A → C
nicht ableitbar C → A oder C → B
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
63
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf
Ableitung von FDs /2
Gilt für f über R SATR (F) ⊆ SATR (f ), dann impliziert F die FD f
(kurz: F |= f )
obiges Beispiel:
F = {A → B, B → C} |= A → C
Hüllenbildung F +
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–31
Relationaler DB-Entwurf
Ableitungsregeln
R1
R2
R3
R4
R5
R6
Reflexivität
Augmentation
Transitivität
Dekomposition
Vereinigung
Pseudotransitivität
X ⊇ Y =⇒ X → Y
{X → Y} =⇒ XZ → YZ
{X → Y, Y → Z} =⇒ X → Z
{X → YZ} =⇒ X → Y
{X → Y, X → Z} =⇒ X → YZ
{X → Y, WY → Z} =⇒ WX → Z
R1 -R3 bekannt als Armstrong-Axiome (sound, complete)
gültig (sound): Regeln leiten keine FDs ab, die logisch nicht
impliziert
vollständig (complete): alle implizierten FDs werden abgeleitet
unabhängig (independent) oder auch bzgl. ⊆ minimal: keine
Regel kann weggelassen werden
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–32
Relationaler DB-Entwurf
Beweis: R1
Annahme: X ⊇ Y, X, Y ⊂ R, t1 , t2 ∈ r(R) mit t1 (X) = t2 (X)
dann folgt: t1 (Y) = t2 (Y) wegen X ⊇ Y
daraus folgt: X → Y
Sattler / Saake
64
Datenbanksysteme
Wintersemester 2006/7
5–33
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf
4
Beweis: R2
Annahme: X → Y gilt in r(R), jedoch nicht: XZ → YZ
dann müssen zwei Tupel t1 , t2 ∈ r(R) existieren, so dass gilt
(1)
(2)
(3)
(4)
t1 (X) = t2 (X)
t1 (Y) = t2 (Y)
t1 (XZ) = t2 (XZ)
t1 (YZ) = t2 (YZ)
Widerspruch wegen t1 (Z) = t2 (Z) aus (1) und (3), woraus folgt:
t1 (YZ) = t2 (YZ) (in Verbindung mit (4))
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–34
Relationaler DB-Entwurf
Beweis: R3
Annahme: in r(R) gelten:
(1) X → Y
(2) Y → Z
demzufolge für zwei beliebige Tupel t1 , t2 ∈ r(R) mit t1 (X) = t2 (X)
muss gelten:
(3) t1 (Y) = t2 (Y) (wegen (1))
(4) t1 (Z) = t2 (Z) (wegen (3) und (2))
daher gilt: X → Z
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–35
Relationaler DB-Entwurf
Membership-Problem
Kann eine bestimmte FD X → Y aus der vorgegebenen Menge F
abgeleitet werden, d.h. wird sie von F impliziert?
Membership-Problem: „X → Y ∈ F + ?“
Hülle einer Attributmenge X bzgl. F ist XF+ := {A | X → A ∈ F + }
Membership-Problem kann durch das modifizierte Problem
Membership-Problem (2): „Y ⊆ XF+ ?“
in linearer Zeit gelöst werden
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–36
65
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf
Algorithmus C LOSURE
Ermittlung von XF+ : die Hülle von X bzgl. F
C LOSURE(F, X):
X + := X
repeat
+
X := X +
forall FDs Y → Z ∈ F
if Y ⊆ X + then
X + := X + ∪ Z
+
until X + = X
+
return X
Beispiel:
A → C ∈ {A → B, B → C}+ ?
f1
Sattler / Saake
f2
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–37
Relationaler DB-Entwurf
Überdeckungen
F heißt äquivalent zu G
oder: F Überdeckung von G; kurz: F ≡ G falls F + = G+
d.h.:
∀g ∈ G : g ∈ F + ∧ ∀f ∈ F : f ∈ G+
Test durch
Berechne X + bzgl. F für jede FD X → Y ∈ G
enthält X + die Attribute von Y?
wenn dies für jede FD in G zutrifft, dann überdeckt F die Menge G
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–38
Relationaler DB-Entwurf
Minimale (oder kanonische) Überdeckungen
forall FD X → Y ∈ F /* Linksreduktion */
forall A ∈ X /* A überflüssig ? */
if Y ⊆ C LOSURE(F, X − {A})
then ersetze X → Y durch (X − A) → Y in F
forall verbleibende FD X → Y ∈ F /* Rechtsreduktion */
forall B ∈ Y /* B überflüssig ? */
if B ⊆ C LOSURE(F − {X → Y} ∪ {X → (Y − B)}, X)
then ersetze X → Y durch X → (Y − B)
Elimiere FDs der Form X → ∅
Vereinige FDs der Form X → Y1 , X → Y2 , . . . zu X → Y1 Y2 . . .
Sattler / Saake
66
Datenbanksysteme
Wintersemester 2006/7
5–39
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Normalformen
4
Schemaeigenschaften
Relationenschemata, Schlüssel und Fremdschlüssel so wählen,
dass
1
2
3
alle Anwendungsdaten aus den Basisrelationen hergeleitet werden
können,
nur semantisch sinnvolle und konsistente Anwendungsdaten
dargestellt werden können und
die Anwendungsdaten möglichst nicht-redundant dargestellt
werden.
Hier: Forderung 3
Redundanzen innerhalb einer Relation: Normalformen
globale Redundanzen: Minimalität
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–40
Normalformen
Normalformen
legen Eigenschaften von Relationenschemata fest
verbieten bestimmte Kombinationen von funktionalen
Abhängigkeiten in Relationen
sollen Redundanzen und Anomalien vermeiden
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–41
Normalformen
Erste Normalform
erlaubt nur atomare Attribute in den Relationenschemata, d.h. als
Attributwerte sind Elemente von Standard-Datentypen wie
integer oder string erlaubt, aber keine Konstruktoren wie
array oder set
Nicht in 1NF:
Bestellung BestNr
21
42
69
Sattler / Saake
Datum
18.02.2006
03.05.2006
16.09.2006
KNr
101
102
103
Datenbanksysteme
Versand
TNT
DHL
Oops
AlbumNr
1014
1015, 1021
1014, 1016, 1030
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–42
67
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Normalformen
Erste Normalform /2
in erster Normalform:
Bestellung BestNr
21
42
42
69
69
69
Datum
18.02.2006
03.05.2006
03.05.2006
16.09.2006
16.09.2006
16.09.2006
Sattler / Saake
KNr
101
102
102
103
103
103
Versand
TNT
DHL
DHL
Oops
Oops
Oops
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
AlbumNr
1014
1015
1021
1014
1016
1030
Wintersemester 2006/7
5–43
Normalformen
Zweite Normalform
partielle Abhängigkeit liegt vor, wenn ein Attribut funktional schon
von einem Teil des Schlüssels abhängt
Einkäufe KNr
101
102
102
101
KName
AlbumNr
Titel
Leo Pren
Just Vorfan
Just Vorfan
Leo Pren
1014
1015
1021
1021
Amplified
Nord Nord Ost
Engelskrieger
Engelskrieger
Datum
18.02.06
03.06.06
03.06.06
22.11.06
KNr, AlbumNr → KName, Titel, Datum
und
AlbumNr → Titel
KNr
→ KName
Zweite Normalform eliminiert derartige partielle Abhängigkeiten
bei Nichtschlüsselattributen
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–44
Wintersemester 2006/7
5–45
Normalformen
Eliminierung partieller Abhängigkeiten
Schlüssel K
Teil des
Schlüssels X
Sattler / Saake
68
abhängiges
Attribut A
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Normalformen
4
Dritte Normalform
eliminiert (zusätzlich) transitive Abhängigkeiten
etwa AlbumNr → MNr und MNr → Name in Relation auf Folie
5-25
man beachte: 3NF betrachtet nur Nicht-Schlüsselattribute als
Endpunkt transitiver Abhängigkeiten
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–46
Wintersemester 2006/7
5–47
Normalformen
Eliminierung transitiver Abhängigkeiten
Schlüssel K
Attributmenge X
Sattler / Saake
abhängiges
Attribut A
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Normalformen
Boyce-Codd-Normalform
Verschärfung der 3NF: Eliminierung transitiver Abhängigkeiten
auch zwischen Primattributen
Beispiel
Produkt(ProdNr, Hersteller, Lieferant, Preis)
FDs:
ProdNr, Hersteller → Preis
Hersteller → Lieferant
Lieferant → Hersteller
Schlüsselkandidaten: ProdNr, Hersteller und ProdNr,
Lieferant
in 3NF, nicht jedoch in BCNF
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–48
69
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Normalformen
Boyce-Codd-Normalform /2
Schema in BCNF:
Produkt(ProdNr, Hersteller, Preis)
HerstLief(Hersteller, Lieferant)
BCNF kann jedoch Abhängigkeitstreue verletzen, daher oft nur bis
3NF
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–49
Normalformen
Minimalität
Global Redundanzen vermeiden
andere Kriterien (wie Normalformen) mit möglichst wenig
Schemata erreichen
Beispiel: Attributmenge ABC, FD-Menge {A → B, B → C}
Datenbankschemata in dritter Normalform:
S = {(AB, {A}), (BC, {B})}
S = {(AB, {A}), (BC, {B}), (AC, {A})}
Redundanzen in S
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–50
Normalformen
Schemaeigenschaften
Kennung
S 1
Schemaeigenschaft
Kurzcharakteristik
1NF
2NF
nur atomare Attribute
keine partielle Abhängigkeit eines
Nicht-Primattributes von einem
Schlüssel
3NF
keine transitive Abhängigkeit eines Nicht-Primattributes von einem
Schlüssel
keine transitive Abhängigkeit eines
Attributes von einem Schlüssel
BCNF
S 2
Minimalität
Sattler / Saake
70
minimale Anzahl von Relationenschemata, die die anderen Eigenschaften erfüllt
Datenbanksysteme
Wintersemester 2006/7
5–51
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Transformationseigenschaften
4
Transformationseigenschaften
Bei einer Zerlegung einer Relation in mehrere Relationen ist
darauf zu achten, dass
1
2
nur semantisch sinnvolle und konsistente Anwendungsdaten
dargestellt (Abhängigkeitstreue) und
alle Anwendungsdaten aus den Basisrelationen hergeleitet werden
können (Verbundtreue)
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–52
Transformationseigenschaften
Abhängigkeitstreue
Abhängigkeitstreue: eine Menge von Abhängigkeiten kann
äquivalent in eine zweite Menge von Abhängigkeiten transformiert
werden
spezieller: in die Menge der Schlüsselabhängigkeiten, da diese
vom Datenbanksystem effizient überprüft werden kann
die Menge der Abhängigkeiten soll äquivalent zu der Menge der
Schlüsselbedingungen im resultierenden Datenbankschema sein
Äquivalenz sichert zu, dass mit den Schlüsselabhängigkeiten
semantisch genau die gleichen Integritätsbedingungen ausgedrückt
werden wie mit den funktionalen oder anderen Abhängigkeiten
vorher
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–53
Transformationseigenschaften
Abhängigkeitstreue: Beispiel
Zerlegung des Relationenschemas Album (Folie 5-25) in 3NF:
AlbumNr → Titel, Jahr, Preis, MNr
MNr
→ Name, Land
äquivalent zu Schlüsselabhängigkeiten
Sattler / Saake
Datenbanksysteme
abhängigkeitstreu
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–54
71
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Transformationseigenschaften
Abhängigkeitstreue: Beispiel /2
Postleitzahl-Struktur der Deutschen Post
PLZ (P), Ort (O), Strasse(S), Hausnummer(H)
und funktionalen Abhängigkeiten F
OSH → P, P → O
für ein Datenbankschema S bestehend aus dem einzigen
Relationenschema
(OSHP, {OSH}),
ist Menge der Schlüsselabhängigkeiten
{ OSH → OSHP }
nicht äquivalent zu F und somit S nicht abhängigkeitstreu
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–55
Transformationseigenschaften
Verbundtreue
zur Erfüllung des Kriteriums der Normalformen müssen
Relationenschemata teilweise in kleinere Relationenschemata
zerlegt werden
für Beschränkung auf „sinnvolle“ Zerlegungen gilt Forderung, dass
die Originalrelation wieder aus den zerlegten Relationen mit dem
natürlichen Verbund zurückgewonnen werden kann
Verbundtreue
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–56
Wintersemester 2006/7
5–57
Transformationseigenschaften
Verbundtreue: Beispiele
Zerlegung des Relationenschemas R = ABC in
R1 = AB und R2 = BC
Dekomposition bei Vorliegen der Abhängigkeiten
F = {A → B, C → B}
ist nicht verbundtreu
dagegen bei Vorliegen von
F = {A → B, B → C}
verbundtreu
Sattler / Saake
72
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Transformationseigenschaften
4
Verbundtreue Dekomposition
Originalrelation:
A
1
4
B
2
2
C
3
3
Dekomposition:
A
1
4
B
2
2
B
2
C
3
Verbund (verbundtreu):
A
1
4
Sattler / Saake
B
2
2
C
3
3
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–58
Wintersemester 2006/7
5–59
Transformationseigenschaften
Nicht verbundtreue Dekomposition
Originalrelation:
A
1
4
B
2
2
C
3
5
Dekomposition:
A
1
4
B
2
2
B
2
2
C
3
5
Verbund (nicht verbundtreu):
Sattler / Saake
A
B
C
1
4
1
4
2
2
2
2
3
5
5
3
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Entwurfsverfahren
Entwurfsverfahren
Universum U und FD-Menge F gegeben
lokal erweitertes Datenbankschema S = {(R1 , K1 ), . . . , (Rp , Kp )}
berechnen mit
T 1 : S charakterisiert vollständig F
S 1 : S ist in 3NF bezüglich F
T 2 : Dekomposition von U in R1 , . . . , Rp ist verbundtreu bezüglich
F
S 2 : Minimalität, d.h.
∃S : S erfüllt T 1 , S 1 , T 2 und |S | < |S|
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–60
73
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Entwurfsverfahren
Entwurfsverfahren /2
Datenbankschemata schlecht entworfen, wenn nur eins dieser
vier Kriterien nicht erfüllt
Beispiel: S = {(AB, {A}), (BC, {B}), (AC, {A})} erfüllt T 1 , S 1
und T 2 bezüglich F = {A → B, B → C, A → C}
in dritter Relation AC-Tupel redundant oder inkonsistent
korrekt: S = {(AB, {A}), (BC, {B})}
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–61
Entwurfsverfahren
Dekomposition
Geg.: initiales Universalrelationenschema R = (U, K(F)) mit allen
Attributen und einer von erfassten FDs F über R implizierten
Schlüsselmenge
Attributmenge U und eine FD-Menge F
suche alle K → U mit K minimal, für die K → U ∈ F + gilt (K(F))
Ges.: Zerlegung in D = {R1 , R2 , . . . } von
3NF-Relationenschemata
Algorithmus
Setze D := {R}
while Ri ∈ D, das 3NF nicht erfüllt
if Schlüssel K mit K → Y, Y → K, Y → A, A ∈ KY
then
R1 := R − A , R2 := YA
R1 := (R1 , K) , R2 := (R2 , K2 = {Y})
D := (D − RI ) ∪ {R1 } ∪ {R2 }
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–62
Entwurfsverfahren
Dekomposition: Bewertung
Vorteile: 3NF, Verbundtreue
Nachteile: restliche Kriterien nicht, reihenfolgeabhängig,
NP-vollständig (Schlüsselsuche)
Sattler / Saake
74
Datenbanksysteme
Wintersemester 2006/7
5–63
TU Ilmenau/Uni Magdeburg, WS 2006/07
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Entwurfsverfahren
4
Syntheseverfahren
Prinzip: Synthese formt Original-FD-Menge F in resultierende
Menge von Schlüsselabhängigkeiten G so um, daß F ≡ G gilt
„Abhängigkeitstreue“ im Verfahren verankert
3NF und Minimalität wird auch erreicht, reihenfolgeunabhängig
Zeitkomplexität: quadratisch
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–64
Entwurfsverfahren
Syntheseverfahren: Ablauf
Geg.: Relationschema R mit FDs F
Ges.: verlustfreie und abhängigkeitstreue Zerlegung in R1 , . . . Rn ,
wobei alle Ri in 3NF sind
Algorithmus:
Bestimme minimale Überdeckung F min zu F
Bilde maximale Klassen Ci von FDs aus F min
mit denselben linken Seiten, d.h.
Ci = {Xi → Ai1 , Xi → Ai2 , . . . }
Bilde zu jeder Klasse Ci ein Schema der Form
Ri = {Xi ∪ {Ai1 } ∪ {Ai2 } ∪ . . . }
if keines der Schemata Ri enthält einen Schlüssel von R
then dann erzeuge weiteres Relationenschema mit Attributen aus R,
die Schlüssel bilden
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–65
Weitere Abhängigkeiten
Weitere Abhängigkeiten
Mehrwertige Abhängigkeit (kurz: MVD)
innerhalb einer Relation r wird einem Attributwert von X eine
Menge von Y-Werten zugeordnet, unabhängig von den Werten der
restlichen Attribute
Bsp: Band →→ Person, ISBN →→ Autor
Vierte Normalform
Verbundabhängigkeit (kurz: JD)
kann R ohne Informationsverlust in R1 , . . . , Rp aufgetrennt werden:
[R1 , . . . , Rp ]
Inklusionsabhängigkeit (kurz: IND)
auf der rechten Seite einer Fremdschlüsselabhängigkeit nicht
unbedingt den Primärschlüssel einer Relation
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
5–66
75
4 Relationaler DB-Entwurf und Entwurfstheorie
Relationaler DB-Entwurf und Entwurfstheorie
Weitere Abhängigkeiten
Mehrwertige Abhängigkeiten
Folge der 1NF
Mehrwertige Abhängigkeiten erzeugen Redundanz:
Name
James Hetfield
James Hetfield
James Hetfield
James Hetfield
James Hetfield
James Hetfield
Album
Hobby
St. Anger
St. Anger
St. Anger
Load
Load
Load
Jagen
Snowboarden
Gitarren
Jagen
Snowboarden
Gitarren
eine (oder mehrere) Gruppe von Attributwerten ist von einem
Schlüssel bestimmt, unabhängig von anderen Attributen
hier: Menge von Alben plus Menge von Hobbies
Name →→ Album, Name →→ Hobby
Resultat: Redundanz durch Bildung aller Kombinationen
Sattler / Saake
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–67
Weitere Abhängigkeiten
Mehrwertige Abhängigkeiten und 4NF
Vierte Normalform fordert die Beseitigung derartiger
Redundanzen:
R ist in 4NF bzgl. der FD-Menge F, wenn Attributmenge X für jede
nicht-triviale MVD X →→ Y ein Superschlüssel von F + ist
triviale MVD X →→ Y: (a) Y ⊂ X oder (b) X ∪ Y = R
Prinzip
Elimination der rechten Seite einer der beiden mehrwertigen
Abhängigkeiten,
linke Seite mit dieser rechten Seite in neue Relation kopiert
Name
Album
James Hetfield
James Hetfield
St. Anger
Load
Sattler / Saake
Name
Hobby
James Hetfield
James Hetfield
James Hetfield
Jagen
Snowboarden
Gitarren
Datenbanksysteme
Relationaler DB-Entwurf und Entwurfstheorie
Wintersemester 2006/7
5–68
Weitere Abhängigkeiten
Zusammenfassung
Weitere Schritte des Entwurfsprozesses
Abbildung des ER-Schemas auf Relationenschemata
Verbesserung des Entwurfs durch Normalisierung
funktionale Abhängigkeiten
Normalformen (1NF - 3NF)
Abhängigkeitstreue und Verbundtreue
Entwurfsverfahren
Sattler / Saake
76
Datenbanksysteme
Wintersemester 2006/7
5–69
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Grundlagen von Anfragen: Algebra & Kalkül
1
Kriterien für Anfragesprachen
2
Anfragealgebren
3
Anfragekalküle
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–1
Kriterien für Anfragesprachen
Einführung
bisher:
Relationenschemata mit Basisrelationen, die in der Datenbank
gespeichert sind
jetzt:
„abgeleitete“ Relationenschemata mit virtuellen Relationen, die aus
den Basisrelationen berechnet werden (Basisrelationen bleiben
unverändert)
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–2
Kriterien für Anfragesprachen
Begriffe
Anfrage: Folge von Operationen, die aus den Basisrelationen eine
Ergebnisrelation berechnet
Ergebnisrelation interaktiv auf dem Bildschirm anzeigen oder
per Programm weiterverarbeiten („Einbettung“)
Sicht: Folge von Operationen, die unter einem Sichtnamen
langfristig abgespeichert wird und unter diesem Namen wieder
aufgerufen werden kann; ergibt eine Sichtrelation
Snapshot: Ergebnisrelation einer Anfrage, die unter einem
Snapshot-Namen abgelegt wird, aber nie ein zweites Mal (mit
geänderten Basisrelationen) berechnet wird (etwa
Jahresbilanzen)
Sattler / Saake
80
Datenbanksysteme
Wintersemester 2006/7
6–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Kriterien für Anfragesprachen
Kriterien für Anfragesprachen
Ad-Hoc-Formulierung: Benutzer soll eine Anfrage formulieren
können, ohne ein vollständiges Programm schreiben zu müssen
5
Deskriptivität: Benutzer soll formulieren „Was will ich haben?“
und nicht „Wie komme ich an das, was ich haben will?“
Mengenorientiertheit: jede Operation soll auf Mengen von Daten
gleichzeitig arbeiten, nicht navigierend nur auf einzelnen
Elementen („one-tuple-at-a-time“)
Abgeschlossenheit: Ergebnis ist wieder eine Relation und kann
wieder als Eingabe für die nächste Anfrage verwendet werden
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–4
Kriterien für Anfragesprachen
Kriterien für Anfragesprachen /2
Adäquatheit: alle Konstrukte des zugrundeliegenden
Datenmodells werden unterstützt
Orthogonalität: Sprachkonstrukte sind in ähnlichen Situationen
auch ähnlich anwendbar
Optimierbarkeit: Sprache besteht aus wenigen Operationen, für
die es Optimierungsregeln gibt
Effizienz: jede Operation ist effizient ausführbar (im
Relationenmodell hat jede Operation eine Komplexität ≤ O(n2 ), n
Anzahl der Tupel einer Relation).
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–5
Kriterien für Anfragesprachen
Kriterien für Anfragesprachen /3
Sicherheit: keine Anfrage, die syntaktisch korrekt ist, darf in eine
Endlosschleife geraten oder ein unendliches Ergebnis liefern
Eingeschränktheit: (folgt aus Sicherheit, Optimierbarkeit,
Effizienz) Anfragesprache darf keine komplette
Programmiersprache sein
Vollständigkeit: Sprache muss mindestens die Anfragen einer
Standardsprache (wie etwa die in diesem Kapitel einzuführende
Relationenalgebra oder den sicheren Relationenkalkül)
ausdrücken können
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–6
81
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Anfragealgebren
Mathematik: Algebra definiert durch Wertebereich und auf diesem
definierte Operatoren
für Datenbankanfragen: Inhalte der Datenbank sind Werte, und
Operatoren definieren Funktionen zum Berechnen von
Anfrageergebnissen
Relationenalgebra
Algebra-Erweiterungen
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–7
Wintersemester 2006/7
6–8
Anfragealgebren
Relationenalgebra
Spalten ausblenden: Projektion π
Zeilen heraussuchen: Selektion σ
Tabellen verknüpfen: Verbund (Join)
Tabellen vereinigen: Vereinigung ∪;
Tabellen voneinander abziehen: Differenz −
Spalten umbenennen: Umbenennung β
(wichtig für und ∪, −)
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Relationenalgebra: Übersicht
Selektion
Projektion
Verbund
Sattler / Saake
82
a1
b2
b2
c3
a1
b2
c3
a2
b2
b3
c4
a2
b2
c3
a2
b3
b4
c5
a2
b3
c4
Datenbanksysteme
Wintersemester 2006/7
6–9
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Beispieldaten
102
103
104
105
106
Album ANr
1014
1015
1016
1021
1027
Name
Land
Neil Young
Apocalyptica
Subway To Sally
Rammstein
Tokio Hotel
Kanada
Finnland
Deutschland
Deutschland
Deutschland
Titel
Jahr
Genre
Preis
MNr
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
2006
2005
2005
2003
2006
Rock
Rock
Rock
Rock
Rock
22,95
8,95
17,95
12,95
16,95
103
104
105
104
102
Komponist Vorname
5
Musiker MNr
Nachname
Neil
Herbert
Johann Sebastian
Eicca
Sattler / Saake
Young
Grönemeyer
Bach
Toppinen
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–10
Wintersemester 2006/7
6–11
Wintersemester 2006/7
6–12
Anfragealgebren
Projektion
Syntax
πAttributmenge (Relation)
Semantik
πX (r) := {t(X) | t ∈ r}
für r(R) und X ⊆ R Attributmenge in R
Eigenschaft für Y ⊆ X ⊆ R
πY (πX (r)) = πY (r)
Achtung: π entfernt Duplikate (Mengensemantik)
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Projektion: Beispiel
πLand (Musiker)
Musiker Land
Kanada
Finnland
Deutschland
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
83
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Selektion
Syntax
σBedingung (Relation)
Semantik (für A ∈ R)
σA=a (r) := {t ∈ r | t(A) = a}
Datenbanksysteme
Sattler / Saake
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–13
Anfragealgebren
Selektionsbedingungen
Konstantenselektion
Attribut θ Konstante
boolesches Prädikat θ ist = oder =, bei linear geordneten
Wertebereichen auch ≤, <, ≥ oder >
Attributselektion
Attribut1 θ Attribut2
logische Verknüpfung mehrerer Konstanten- oder AttributSelektionen mit ∧, ∨ oder ¬
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–14
Wintersemester 2006/7
6–15
Anfragealgebren
Selektion: Eigenschaften
Kommutativität
σA=a (σB=b (r)) = σB=b (σA=a (r))
falls A ∈ X, X ⊆ R
πX (σA=a (r)) = σA=a (πX (r))
Distributivität bzgl. ∪, ∩, −
σA=a (r ∪ s) = σA=a (r) ∪ σA=a (s)
Sattler / Saake
84
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Selektion: Beispiel
Album ANr
1014
1027
Titel
Jahr
Genre
Preis
MNr
Amplified
Living With War
2006
2006
Rock
Rock
22,95
16,95
103
102
Datenbanksysteme
Sattler / Saake
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
5
σJahr=2006 (Album)
6–16
Anfragealgebren
Verbund
Syntax des (natürlichen) Verbundes (engl.: natural join)
Relation1
Relation2
Semantik
r1
r2 := {t | t(R1 ∪ R2 ) ∧
[∀i ∈ {1, 2}∃ti ∈ ri : ti = t(Ri )]}
Verbund verknüpft Tabellen über gleichbenannten Spalten bei
gleichen Attributwerten
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–17
Anfragealgebren
Verbund: Eigenschaften
Schema für r(R)
RS = R ∪ S
r(S) ist Vereinigung der Attributmengen
aus R1 ∩ R2 = {} folgt r1
Kommutativität: r1
Assoziativität: (r1
daher erlaubt:
Sattler / Saake
r2 = r1 × r2
r2 = r2
r2 )
r1
r3 = r1
(r2
r3 )
p
i=1 ri
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–18
85
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Verbund: Beispiel
Album
Musiker
ANr
Titel
Jahr
...
MNr
Name
Land
1014
1015
1016
1021
1027
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
2006
2005
2005
2003
2006
...
...
...
...
...
103
104
105
104
102
Apocalyptica
Subway To Sally
Rammstein
Subway To Sally
Neil Young
Finnland
Deutschl.
Deutschl.
Deutschl.
Kanada
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–19
Anfragealgebren
Umbenennung
Syntax
βneu←alt (Relation)
Semantik
βB←A (r) := {t | ∃t ∈ r : t (R − A) = t(R − A) ∧ t (B) = t(A)}
ändert Attributnamen von alt in neu
βName←Nachname (Komponist)
durch Umbenennung nun möglich
Verbunde, wo bisher kartesische Produkte ausgeführt wurden
(unterschiedliche Attribute werden gleich benannt),
kartesische Produkte, wo bisher Verbunde ausgeführt wurden
(gleiche Attribute werden unterschiedlich genannt),
Mengenoperationen
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–20
Anfragealgebren
Berechnung des Kreuzproduktes
natürlicher Verbund entartet zum Kreuzprodukt, wenn keine
gemeinsamen Attribute existieren
Erzwingen durch Umbenennung
Beispiel: R1(A, B, C) und R2(C, D)
R1 × R2 ≡ R1
βE←C (R2)
Kreuzprodukt + Selektion simuliert natürlichen Verbund
R1
Sattler / Saake
86
R2 ≡ σR1.C=R2.C (R1 × R2)
Datenbanksysteme
Wintersemester 2006/7
6–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Mengenoperationen: Semantik
formal für r1 (R) und r2 (R)
5
Vereinigung r1 ∪ r2 := {t | t ∈ r1 ∨ t ∈ r2 }
Durchschnitt r1 ∩ r2 := {t | t ∈ r1 ∧ t ∈ r2 }
Differenz r1 − r2 := {t | t ∈ r1 ∧ t ∈ r2 }
Durchschnitt ∩ wegen r1 ∩ r2 = r1 − (r1 − r2 ) überflüssig
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–22
Anfragealgebren
Einige Beispiele
alle Alben aus dem Jahr 2006 inkl. der Musikernamen
σJahr=2006 (Album)
πMNr, Name (Musiker)
Musiker und Komponisten
πName (Musiker) ∪ βName←Nachname (πNachname (Komponist))
Musiker ohne Album
(πMNr (Musiker) − πMNr (Album))
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Musiker
Wintersemester 2006/7
6–23
Anfragealgebren
Unabhängigkeit und Vollständigkeit
Minimale Relationenalgebra:
Ω = π, σ,
, β, ∪ und −
unabhängig: kein Operator kann weggelassen werden ohne
Vollständigkeit zu verlieren
andere unabhängige Menge:
und β durch × ersetzen
Relationale Vollständigkeit: jede andere Menge von Operationen
genauso mächtig wie Ω
strenge relationale Vollständigkeit: zu jedem Ausdruck mit
Operatoren aus Ω gibt es einen Ausdruck auch mit der anderen
Menge von Operationen
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–24
87
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
Erweiterungen der Relationenalgebra
weitere Verbundoperationen
Division
Gruppierung (später zusammen mit SQL)
...
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–25
Anfragealgebren
Verbundvarianten
für L(AB), R(BC), S(DE)
Gleichverbund (engl. equi-join): Gleichheitsbedingung über
explizit angegebene und evtl. verschiedene Attribute
r(R)
r(S)
C=D
Theta-Verbund (engl. θ-join): beliebige Verbundbedingung
r(R)
r(S)
C>D
Semi-Verbund: nur Attribute eines Operanden erscheinen im
Ergebnis
r(L) r(R) = πL (r(L) r(R))
äußere Verbunde (engl. outer join)
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–26
Anfragealgebren
Äußere Verbunde
Übernahme von „dangling tuples“ in das Ergebnis und Auffüllen
mit Nullwerten
voller äußerer Verbund übernimmt alle Tupel beider Operanden
r
s
linker äußerer Verbund übernimmt alle Tupel des linken
Operanden
r
s
rechter äußerer Verbund übernimmt alle Tupel des rechten
Operanden
r
s
Sattler / Saake
88
Datenbanksysteme
Wintersemester 2006/7
6–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragealgebren
LINKS A
B
RECHTS B
C
A
B
C
1
2
2
3
3
4
4
5
2
3
4
A
B
C
A
B
C
A
B
C
1
2
⊥
2
3
4
⊥
4
5
1
2
2
3
⊥
4
2
⊥
3
4
4
5
natural join
left outer join
Sattler / Saake
right outer join
Grundlagen von Anfragen: Algebra & Kalkül
full outer join
Wintersemester 2006/7
Datenbanksysteme
5
Äußere Verbunde /2
6–28
Anfragealgebren
Problem: Quantoren
Allquantor in Relationenalgebra ausdrücken, obwohl in
Selektionsbedingungen nicht erlaubt
Division (kann aus Ω hergeleitet werden)
r1 (R1 ) und r2 (R2 ) gegeben mit R2 ⊆ R1 , R = R1 − R2 . Dann ist
r (R ) = {t | ∀t2 ∈ r2 ∃t1 ∈ r1 : t1 (R ) = t ∧ t1 (R2 ) = t2 }
= r1 ÷ r2
Division von r1 durch r2
r1 ÷ r2 = πR (r1 ) − πR ((πR (r1 )
Sattler / Saake
r2 ) − r1 )
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–29
Anfragealgebren
Division: Beispiel
r1
Musiker
Album
Ian Gillan
Ian Gillan
Ian Gillan
Steve Morse
Ritchie Blackmore
Ritchie Blackmore
Ritchie Blackmore
In Rock
Perfect Strangers
Purpendicular
Pupendicular
In Rock
Perfect Strangers
Slaves & Masters
Album
r2
r1 ÷ r2
Musiker
Ian Gillan
Sattler / Saake
Album
In Rock
Purpendicular
r3
In Rock
Perfect Strangers
Musiker
r1 ÷ r3
Datenbanksysteme
Ian Gillan
Ritchie Blackmore
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–30
89
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Anfragekalküle
Kalkül: eine formale logische Sprache zur Formulierung von
Aussagen
Ziel: Einsatz eines derartigen Kalküls zur Formulierung von
Datenbank-Anfragen
Logikbasierter Ansatz:
Datenbankinhalte entsprechen Belegungen von Prädikaten einer
Logik
Anfragen abgeleiteten Prädikaten
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–31
Anfragekalküle
Ein allgemeiner Kalkül
Motivation: mathematische Notation
{x2 | x ∈ N ∧ x3 > 0 ∧ x3 < 1000}
Anfrage hat die Form
{f (x) | p(x)}
x bezeichnet Menge von freien Variablen
x = {x1 : D1 , . . . , xn : Dn }
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–32
Anfragekalküle
Ein allgemeiner Kalkül /2
Funktion f bezeichnet Ergebnisfunktion über x
wichtige Spezialfälle: Angabe einer Variable selber (f ist hier die
Identitätsfunktion) und Tupelkonstruktion (Ergebnis vom Typ tuple
of)
p Selektionsprädikat über freien Variablen x
Terme aus Variablen, Konstanten und Funktionsanwendungen
Prädikate der Datentypen, etwa ≤, <, >, ≥, ...
→ atomare Formeln über Termen
Bezug zur aktuellen Datenbank → Datenbankprädikate, z.B.
Relationennamen im RM
prädikatenlogischen Operatoren ∧, ∨, ¬, ∀, ∃
→ Formeln
Sattler / Saake
90
Datenbanksysteme
Wintersemester 2006/7
6–33
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Ergebnisbestimmung einer Anfrage
1
2
5
x = {x1 : D1 , . . . , xn : Dn }
Bestimme aller Belegungen der freien Variablen in x, für die das
Prädikat p wahr wird.
Wende Funktion f auf die durch diese Belegungen gegebenen
Werte an.
Unter welchen Umständen liefern Kalkülanfragen endliche
Ergebnisse?
→ Sicherheit von Anfragen
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–34
Anfragekalküle
Relationale Kalküle
Bereichskalkül: Variablen nehmen Werte elementarer
Datentypen (Bereiche) an
Tupelkalkül: Variablen variieren über Tupelwerte (entsprechend
den Zeilen einer Relation)
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–35
Anfragekalküle
Bereichskalkül
Terme:
Konstanten, etwa 42 oder ’MZ-4’
Variablen zu Datentypen, etwa x
Datentypangabe erfolgt in der Regel implizit und wird nicht explizit
deklariert!
Funktionsanwendung f (t1 , . . . , tn ): Funktion f , Terme ti , etwa
plus(12, x) bzw. in Infixnotation 12 + x
Atomare Formeln:
Prädikatanwendung Θ(t1 , . . . , tn ), Θ ∈ {<, >, ≤, ≥, =, =, . . . }
Datentypprädikat, Terme ti
Zweistellige Prädikate wie üblich in Infix-Notation.
Beispiele: x = y, 42 > x oder 3 + 7 = 11.
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–36
91
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Bereichskalkül /2
Atomare Formeln (fortg.):
Prädikatanwendungen für Datenbankprädikate, notiert als
R(t1 , . . . , tn ) für einen Relationennamen R
Voraussetzung: n muss die Stelligkeit der Relation R sein und alle ti
müssen vom passenden Typ sein
Beispiel: Musiker(m, ’Apocalyptica’, ’Finnland’)
Formeln wie üblich mit ∧, ∨, ¬, ∀ und ∃
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–37
Anfragekalküle
Bereichskalkül /3
Anfragen: {x1 , . . . , xn | φ(x1 , . . . , xn )}
φ ist Formel über den in der Ergebnisliste aufgeführten Variablen x1
bis xn
Ergebnis ist eine Menge von Tupeln
Tupelkonstruktion erfolgt implizit aus den Werten der Variablen in
der Ergebnisliste
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–38
Anfragekalküle
Basiskalkül
Einschränkung des Bereichskalküls:
Wertebereich: ganze Zahlen
Datentypprädikate werden wie bei der Relationenalgebra auf
Gleichheit und elementare Vergleichsoperatoren eingeschränkt
Funktionsanwendungen sind nicht erlaubt; nur Konstanten dürfen
neben Bereichsvariablen als Terme verwendet werden
Sattler / Saake
92
Datenbanksysteme
Wintersemester 2006/7
6–39
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Sichere Anfragen
Sichere Anfragen (auch semantisch sichere Anfragen):
5
Anfragen, die für jeden Datenbankzustand σ(R) ein endliches
Ergebnis liefern
Beispiel für nicht sichere Anfrage:
{x, y | ¬R(x, y)}
Beispiel für sichere Anfrage:
{x, y | R(x, y)}
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–40
Anfragekalküle
Sichere Anfragen /2
Weiteres Beispiel für sichere Anfrage:
{x, y | y = 10 ∧ x > 0 ∧ x < 10}
Sicherheit folgt direkt aus den Regeln der Arithmetik.
Semantische Sicherheit ist im Allgemeinen nicht entscheidbar!
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–41
Anfragekalküle
Syntaktisch sichere Anfragen
Syntaktisch sichere Anfragen: Anfragen, die syntaktischen
Einschränkungen unterliegen, um die semantische Sicherheit zu
erzwingen.
Grundidee:
Jede freie Variable xi muss überall in φ(x1 , . . . ) durch positives
Auftreten xi = t oder R(. . . , xi , . . . ) an endliche Bereiche gebunden
werden.
Bindung an endliche Bereiche muss für die ganze Bedingung
gelten, also insbesondere für alle Zweige einer Disjunktion
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–42
93
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Relationen für Beispiele
Musiker(MNr, Name, Land)
Album(ANr, Titel, Jahr, Genre, MNr)
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–43
Anfragekalküle
Beispiele Bereichskalkül
Anfrage: „Alle (Namen von) Musikern aus Finnland“
{n | Musiker(m, n, l) ∧ l = ’Finnland’}
Vereinfachte Notation: Ansonsten ungebundene Variablen (hier m
und l) im Bedingungsteil existentiell mit ∃ gebunden
Vollständige Version:
{n | ∃m∃l Musiker(m, n, l) ∧ l = ’Finnland’}
Einsparung von Bereichsvariablen, indem Konstanten als
Parameter des Prädikats eingesetzt werden:
{n | Musiker(m, n, ’Finnland’)}
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–44
Anfragekalküle
Beispiele Bereichskalkül /2
Abkürzung für beliebige, unterschiedliche existentiell gebundene
Variablen ist _ Symbol:
{n | Musiker(_, n, l) ∧ l = ’Finnland’}
Verschiedene Auftreten des Symbols _ stehen hierbei für
paarweise verschiedene Variablen
Sattler / Saake
94
Datenbanksysteme
Wintersemester 2006/7
6–45
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Beispiele Bereichskalkül /3
Anfrage: „Länder mit mindestens zwei Musikern“
5
{l | Musiker(m, n, l) ∧ Musiker(m , n , l) ∧ n = n }
Anfrage zeigt eine Verbundbildung über das dritte Attribut der
Musiker-Relation
Verbundbildung kann einfach durch die Verwendung der selben
Bereichsvariablen als Parameter in verschiedenen
Relationsprädikaten erfolgen
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–46
Anfragekalküle
Beispiele Bereichskalkül /4
Anfrage: „Musiker mit Alben nach 2004“
{n | Musiker(m, n, l) ∧ Album(a, t, j, g, m) ∧ j > 2004}
Verbund über zwei Relationen
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–47
Anfragekalküle
Beispiele Bereichskalkül /5
Anfrage: „Wer hat Rock-Alben veröffentlicht?“
{n | Musiker(m, n, l)∧∃g(Album(_, _, _, g, m)∧g = ’Rock’)}
Einsatz einer existentiell gebundenen Unteranfrage
derartige Unteranfragen können aufgrund der Regeln der
Prädikatenlogik wie folgt aufgelöst werden:
{n | Musiker(m, n, l) ∧ (Album(_, _, _, g, m) ∧ g = ’Rock’)}
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–48
95
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Beispiele Bereichskalkül /6
Anfrage: „Wer hat nur Rock-Alben veröffentlicht?“
{n | Musiker(m, n, l) ∧ ∀a∀g (Album(a, t, j, g, m) ⇒ g =
’Rock’)}
universell gebundene Teilformeln können nicht aufgelöst werden
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–49
Anfragekalküle
Ausdrucksfähigkeit Bereichskalkül
Bereichskalkül ist streng relational vollständig, d.h. zu jedem Term τ
der Relationenalgebra gibt es einen äquivalenten (sicheren)
Ausdruck η des Bereichskalküls.
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–50
Anfragekalküle
Umsetzung von Relationenoperationen
Geg.: Relationenschemata R(A1 , . . . , An ) und S(B1 , . . . , Bm )
Vereinigung (für n = m)
R∪S=
ˆ {x1 . . . xn | R(x1 , . . . , xn ) ∨ S(x1 , . . . , xn )}
Differenz (für n = m)
R−S=
ˆ {x1 . . . xn | R(x1 , . . . , xn ) ∧ ¬S(x1 , . . . , xn )}
Natürlicher Verbund
R
S=
ˆ
{x1 . . . xn xn+1 . . . xn+m−i | R(x1 , . . . , xn ) ∧
S(x1 , . . . , xi , xn+1 , . . . , xn+m−i )}
Annahme: die ersten i Attribute von R und S sind die
Verbundattribute, also Aj = Bj für j = 1 . . . i
Sattler / Saake
96
Datenbanksysteme
Wintersemester 2006/7
6–51
TU Ilmenau/Uni Magdeburg, WS 2006/07
5 Grundlagen von Anfragen
Grundlagen von Anfragen: Algebra & Kalkül
Anfragekalküle
Umsetzung von Relationenoperationen /2
Projektion
5
πA (R) =
ˆ {y1 . . . yk | ∃x1 . . . ∃xn (R(x1 , . . . , xn ) ∧ y1 = xi1
∧ · · · ∧ yk = xik )}
Attributliste der Projektion: A = (Ai1 , . . . , Aik )
Selektion
σφ (R) =
ˆ {x1 . . . xn | R(x1 , . . . , xn ) ∧ φ }
φ wird aus φ gewonnen, indem Variable xi an Stelle der
Attributnamen Ai eingesetzt werden
Sattler / Saake
Datenbanksysteme
Grundlagen von Anfragen: Algebra & Kalkül
Wintersemester 2006/7
6–52
Anfragekalküle
Zusammenfassung
formale Modelle für Anfragen in Datenbanksystemen
Relationenalgebra
operationaler Ansatz
Anfrage als Schachtelung von Operatoren auf Relationen
Anfragekalkül
logikbasierter Ansatz
Anfrage als abgeleitete Prädikate
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
6–53
97
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
SQL und weitere relationale Anfragesprachen
1
Der SFW-Block in Detail
2
Erweiterungen des SFW-Blocks
3
Rekursion
4
Query by Example
5
JDBC & SQLJ
6
PL/SQL
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–1
Wintersemester 2006/7
7–2
Der SFW-Block in Detail
SQL-Kern
select
Projektionsliste
arithmetische Operationen und Aggregatfunktionen
from
zu verwendende Relationen, evtl. Umbenennungen
where
Selektions-, Verbundbedingungen
Geschachtelte Anfragen (wieder ein SFW-Block)
group by
Gruppierung für Aggregatfunktionen
having
Selektionsbedingungen an Gruppen
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Auswahl von Tabellen: Die from-Klausel
einfachste Form:
hinter jedem Relationennamen kann optional eine Tupelvariable
stehen
select *
from relationenliste
Beispielanfrage:
select *
from Album
Sattler / Saake
100
Datenbanksysteme
Wintersemester 2006/7
7–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Kartesisches Produkt
bei mehr als einer Relation wird das kartesische Produkt gebildet:
6
select *
from Album, Musiker
alle Kombinationen werden ausgegeben!
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–4
Der SFW-Block in Detail
Tupelvariablen für mehrfachen Zugriff
Einführung von Tupelvariablen erlaubt mehrfachen Zugriff auf eine
Relation:
select *
from Musiker eins, Musiker zwei
Spalten lauten dann:
eins.MNr, eins.Name, eins.Land,
zwei.MNr, zwei.Name, zwei.Land
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–5
Der SFW-Block in Detail
Natürlicher Verbund in SQL92
frühe SQL-Versionen
üblicherweise realisierter Standard in aktuellen Systemen
kennen nur Kreuzprodukt, keinen expliziten Verbundoperator
Verbund durch Prädikat hinter where realisieren
Beispiel für natürlichen Verbund:
select *
from Album, Musiker
where Album.MNr = Musiker.MNr
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–6
101
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Verbund explizit: natural join
neuere SQL-Versionen
kennen mehrere explizite Verbundoperatoren (engl. join)
als Abkürzung für die ausführliche Anfrage mit Kreuzprodukt
aufzufassen
select *
from Album natural join Musiker
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–7
Wintersemester 2006/7
7–8
Wintersemester 2006/7
7–9
Der SFW-Block in Detail
Verbunde als explizite Operatoren: join
Verbund mit beliebigem Prädikat:
select *
from Album join Musiker
on Alubm.MNr = Musiker.MNr
Gleichverbund mit using:
select *
from Album join Musiker
using (MNr)
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Verbund explizit: cross join
Kreuzprodukt
select *
from Album, Musiker
als cross join
select *
from Album cross join Musiker
Sattler / Saake
102
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Die select-Klausel
Festlegung der Projektionsattribute
6
select [distinct] { attribut |
arithmetischer-ausdruck |
aggregat-funktion }+
from ...
Attribute der hinter from stehenden Relationen, optional mit einem
Präfix, der den Relationennamen oder den Namen der
Tupelvariablen angibt
arithmetische Ausdrücke über Attributen dieser Relationen
passenden Konstanten
Aggregatfunktionen über Attributen dieser Relationen
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–10
Wintersemester 2006/7
7–11
Wintersemester 2006/7
7–12
Der SFW-Block in Detail
distinct eliminiert Duplikate
select Land from Musiker
liefert die Ergebnisrelation als Multimenge:
Land
Kanada
Finnland
Deutschland
Deutschland
Deutschland
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
distinct eliminiert Duplikate /2
select distinct Land from Musiker
ergibt Projektion aus der Relationenalgebra:
Land
Kanada
Finnland
Deutschland
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
103
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Tupelvariablen und Relationennamen
Anfrage
select Titel from Album
ist äquivalent zu
select Album.Titel from Album
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–13
Der SFW-Block in Detail
Präfixe für Eindeutigkeit
select Name, Land, Titel, Jahr,
MNr
(falsch!)
from Album, Musiker
where Album.MNr = Musiker.MNr
Attribut MNr existiert sowohl in der Tabelle Album als auch in
Musiker!
richtig mit Präfix:
select Name, Land, Titel, Jahr,
Musiker.MNr
from Album, Musiker
where Album.MNr = Musiker.MNr
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–14
Der SFW-Block in Detail
Tupelvariablen für Eindeutigkeit
bei der Verwendung von Tupelvariablen, kann der Name einer
Tupelvariablen zur Qualifizierung eines Attributs benutzt werden:
select eins.Name, zwei.Name
from Musiker eins, Musiker zwei
where eins.Land = zwei.Land and
eins.Name <> zwei.Name
Sattler / Saake
104
Datenbanksysteme
Wintersemester 2006/7
7–15
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Die where-Klausel
select ...from ...
where bedingung
Formen der Bedingung:
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
6
Vergleich eines Attributs mit einer Konstanten:
attribut θ konstante
mögliche Vergleichssymbole θ abhängig vom Wertebereich; etwa
=, <>, >, <, >= sowie <=.
Vergleich zwischen zwei Attributen mit kompatiblen
Wertebereichen:
attribut1 θ attribut2
logische Konnektoren or, and und not
7–16
Der SFW-Block in Detail
Verbundbedingung
Verbundbedingung hat die Form:
relation1.attribut = relation2.attribut
Beispiel:
select Album.Titel, Musiker.Name
from Album, Musiker
where Album.MNr = Musiker.MNr
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–17
Der SFW-Block in Detail
Schachtelung von Anfragen
für Vergleiche mit Wertemengen notwendig:
Standardvergleiche in Verbindung mit den Quantoren all (∀) oder
any (∃)
spezielle Prädikate für den Zugriff auf Mengen, in und exists
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–18
105
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
in-Prädikat und geschachtelte Anfragen
Notation:
attribut in ( SFW-block )
Beispiel:
select Name, Land from Musiker
where MNr in ( select MNr from Album)
not in kann für Bildung von Differenzen verwendet werden, z.B.
für die Anfrage „Musiker ohne Album“:
select Name, Land from Musiker
where MNr not in
(select MNr from Album)
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–19
Der SFW-Block in Detail
Mengenoperationen
Mengenoperationen erfordern kompatible Wertebereiche für
Paare korrespondierender Attribute:
beide Wertebereiche sind gleich oder
beide sind auf character basierende Wertebereiche (unabhängig
von der Länge der Strings) oder
beide sind numerische Wertebereiche (unabhängig von dem
genauen Typ) wie integer oder float
select A, B, C from R1
union
select A, C, D from R2
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–20
Der SFW-Block in Detail
Vereinigung zweier Relationen mit union
B
C
R2 A
C
D
1
2
2
3
3
4
2
5
2
3
3
2
R1 union R2 A
B
C
R1 union corresponding R2 A
C
1
2
2
5
2
3
2
3
3
4
3
2
1
2
2
5
3
4
2
3
Sattler / Saake
106
R1 A
Datenbanksysteme
Wintersemester 2006/7
7–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Der SFW-Block in Detail
Differenz
ältere SQL-Versionen kennen keinen Differenzoperator:
πMNr (Musiker) − πMNr (Album)
dargestellt durch
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
6
select MNr from Musiker
where MNr not in ( select MNr
from Album )
Wintersemester 2006/7
7–22
Der SFW-Block in Detail
Mengenoperationen im neuen SQL
Vereinigung, Durchschnitt und Differenz als union, intersect
und except
orthogonal einsetzbar:
select count(*)
from ( (select Name
from Musiker)
union
(select Vorname || ’ ’ || Nachname
from Komponist) )
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–23
Der SFW-Block in Detail
Mächtigkeit des SQL-Kerns
Relationenalgebra
Projektion
Selektion
Verbund
Umbenennung
Differenz
Durchschnitt
Vereinigung
Sattler / Saake
SQL
select distinct
where ohne Schachtelung
from, where
from mit join oder natural join
from mit Tupelvariable; as
where mit Schachtelung
except corresponding
where mit Schachtelung
intersect corresponding
union corresponding
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–24
107
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Weiteres zu SQL
Erweiterungen des SFW-Blocks
innerhalb der from-Klausel weitere Verbundoperationen (äußerer
Verbund),
innerhalb der where-Klausel weitere Arten von Bedingungen,
insbesondere Bedingungen mit „Wildcards“ und Bedingungen mit
Quantoren,
innerhalb der select-Klausel die Anwendung von skalaren
Operationen und Aggregatfunktionen,
zusätzliche Klauseln group by und having
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–25
Erweiterungen des SFW-Blocks
Äußere Verbunde
zusätzlich zu klassischen Verbund (inner join): in SQL-92
auch äußerer Verbund
Übernahme von „dangling tuples“ in das
Ergebnis und Auffüllen mit Nullwerten
outer join übernimmt alle Tupel beider Operanden
(Langfassung: full outer join)
left outer join bzw. right outer join übernimmt alle
Tupel des linken bzw. des rechten Operanden
äußerer natürlicher Verbund jeweils mit Schlüsselwort natural,
also z.B. natural left outer join
select *
from Musiker natural left outer join Album
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–26
Erweiterungen des SFW-Blocks
Weitere Selektionen in SQL
Bereichsselektion
Ungewissheitsselektion
Nullwerte
Bedingungen mit Quantoren
Unteranfragen mit exists
Sattler / Saake
108
Datenbanksysteme
Wintersemester 2006/7
7–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Bereichsselektion
Bereichsselektion
attrib between konstante1 and konstante2
ist Abkürzung für
6
attrib ≥ konstante1 and
attrib ≤ konstante2
schränkt damit Attributwerte auf das abgeschlossene Intervall
[konstante1, konstante2] ein
Beispiel:
select * from Album
where Jahr between 2003 and 2006
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–28
Erweiterungen des SFW-Blocks
Ungewissheitsselektion
Notation
attribut like spezialkonstante
Mustererkennung in Strings (Suche nach mehreren
Teilzeichenketten)
Spezialkonstante kann die Sondersymbole ‘%’ und ‘_’ beinhalten
‘%’ steht für kein oder beliebig viele Zeichen
‘_’ steht für genau ein Zeichen
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–29
Erweiterungen des SFW-Blocks
Ungewissheitsselektion /2
Beispiel
select * from Musiker
where Name like ’%ica’
ist Abkürzung für
select * from Musiker
where Name = Metallica’
or Name = ’Apocalyptica’
or Titel = ’Alcoholica’
or Titel = ’Musica’
or ...
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–30
109
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Selektionen nach Nullwerten
Null-Selektion wählt Tupel aus, die bei einem bestimmten Attribut
Nullwerte enthalten
Notation
attribut is null
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–31
Erweiterungen des SFW-Blocks
Bedingungen mit Quantoren
Quantoren: all, any, some und exists
Notation
attribut θ { all | any | some } (
select attribut
from ...where ...)
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–32
Erweiterungen des SFW-Blocks
Bedingungen mit Quantoren: Beispiele
select Name
from Musiker
where MNr = any ( select MNr
from Album where Genre = ’Rock’)
any und some haben identische Bedeutung!
select Titel
from Album
where Jahr >= all ( select Jahr
from Album )
Sattler / Saake
110
Datenbanksysteme
Wintersemester 2006/7
7–33
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Das exists/not exists-Prädikat
Musiker ohne Album in 2006:
6
select Name
from Musiker
where not exists
( select *
from Album
where Album.MNr = Musiker.MNr
and Jahr = 2006)
„Verzahnt“ geschachtelte (korrelierte) Anfrage: in der inneren
Anfrage wird Relationen- oder Tupelvariablen-Name aus dem
from-Teil der äußeren Anfrage verwendet
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–34
Erweiterungen des SFW-Blocks
Simulation des Allquantors
Beispiel von Folie 6-30 (Relationen MusikerAlben und Alben1)
mit doppelter Negation:
select distinct(Musiker)
from MusikerAlben m1
where not exists (
select * from Alben1 a
where not exists (
select * from MusikerAlben m2
where m2.Album=a.Album and m2.Musiker=m1.Musiker))
„Gib alle Musiker aus, so dass kein Album existiert, an dem sie
nicht beteiligt waren“. (also die Musiker, die an allen Alben beteiligt
waren . . . )
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–35
Erweiterungen des SFW-Blocks
Skalare Ausdrücke
skalare Operationen auf
numerischen Wertebereichen: etwa +, −, ∗ und /,
Strings: Operationen wie char_length (aktuelle Länge eines
Strings), die Konkatenation und die Operation substring
(Suchen einer Teilzeichenkette an bestimmten Positionen des
Strings),
Datumstypen und Zeitintervallen: Operationen wie current_date
(aktuelles Datum), current_time (aktuelle Zeit), +, − und ∗
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–36
111
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Skalare Ausdrücke /2
Umrechnung des Euro-Preises in den aktuellen Dollar-Preis:
select Titel, Preis * 1.2796 as DollarPreis
from Album
erzeugt folgende Ergebnistabelle:
Titel
Amplified
Nord Nord Ost
Rosenrot
Engelskrieger
Living With War
Sattler / Saake
DollarPreis
29.37
11.45
22.97
16.57
21.69
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–37
Erweiterungen des SFW-Blocks
Aggregatfunktionen und Gruppierung
Aggregatfunktionen berechnen neue Werte für eine gesamte
Spalte, etwa die Summe oder den Durchschnitt der Werte einer
Spalte
Beispiel: Ermittlung des Durchschnittspreises aller Artikel oder
des Gesamtumsatzes über alle verkauften Produkte
bei zusätzlicher Anwendung von Gruppierung: Berechnung der
Funktionen pro Gruppe, z.B. der Durchschnittspreis pro
Warengruppe oder der Gesamtumsatz pro Kunde
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–38
Erweiterungen des SFW-Blocks
Aggregatfunktionen
Aggregatfunktionen in Standard-SQL:
count: berechnet Anzahl der Werte einer Spalte oder alternativ (im
Spezialfall count(∗)) die Anzahl der Tupel einer Relation
sum: berechnet die Summe der Werte einer Spalte (nur bei
numerischen Wertebereichen)
avg: berechnet den arithmetischen Mittelwert der Werte einer
Spalte (nur bei numerischen Wertebereichen)
max bzw. min: berechnen den größten bzw. kleinsten Wert einer
Spalte
Sattler / Saake
112
Datenbanksysteme
Wintersemester 2006/7
7–39
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Aggregatfunktionen /2
Argumente einer Aggregatfunktion:
6
ein Attribut der durch die from-Klausel spezifizierten Relation,
ein gültiger skalarer Ausdruck oder
im Falle der count-Funktion auch das Symbol ∗
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–40
Erweiterungen des SFW-Blocks
Aggregatfunktionen /3
vor dem Argument (außer im Fall von count(∗)) optional auch die
Schlüsselwörter distinct oder all
distinct: vor Anwendung der Aggregatfunktion werden doppelte
Werte aus der Menge von Werten, auf die die Funktion
angewendet wird
all: Duplikate gehen mit in die Berechnung ein
(Default-Voreinstellung
Nullwerte werden in jedem Fall vor Anwendung der Funktion aus
der Wertemenge eliminiert (außer im Fall von count(∗))
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–41
Erweiterungen des SFW-Blocks
Aggregatfunktionen - Beispiele
Gesamtwert aller Alben:
select sum(Preis) from Album
ergibt
79.75
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–42
113
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Aggregatfunktionen - Beispiele /2
Anzahl der Alben:
select count(∗) from Album
Anzahl der Musiker mit Alben
select count(distinct MNr)
from
Album
Durchschnittspreis aller Alben:
select avg(all Preis) from Album
Verwendung von all, da bei distinct doppelte Preise
eliminiert werden würden, was den Durchschnittspreis verfälscht
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–43
Erweiterungen des SFW-Blocks
Aggregatfunktionen in where-Klausel
Aggregatfunktionen liefern nur einen Wert
Einsatz in
Konstanten-Selektionen der where-Klausel möglich
Beispiel: Alben, deren Preis unter dem Durchschnitt liegt:
select Titel, Preis
from
Album
where Preis < (
select avg(all Preis) from Album)
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–44
Erweiterungen des SFW-Blocks
group by und having
Notation
select ...
from ...
[where ...]
[group by attributliste ]
[having bedingung ]
Sattler / Saake
114
Datenbanksysteme
Wintersemester 2006/7
7–45
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Gruppierung: Schema
A
B
C
D
1
1
2
3
3
2
2
3
3
3
3
4
3
4
6
4
5
4
5
7
6
Relation REL:
...
Anfrage:
select A, sum(D) from REL where ...
group by A, B
having A<4 and sum(D)<10 and max(C)=4
Datenbanksysteme
Sattler / Saake
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–46
Erweiterungen des SFW-Blocks
Gruppierung: Schritt 1
from und where
A
1
1
2
3
3
B
2
2
3
3
3
C
3
4
3
4
6
D
4
5
4
5
7
224
...
Sattler / Saake
A
1
1
2
3
3
B
2
2
3
3
3
C
3
4
3
4
6
D
4
5
4
5
7
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–47
Erweiterungen des SFW-Blocks
Gruppierung: Schritt 2
group by A, B
A
1
1
2
3
3
B
2
2
3
3
3
C
3
4
3
4
6
Sattler / Saake
D
4
5
4
5
7
A
224
Datenbanksysteme
B
1
2
2
3
3
3
N
C
3
4
3
4
6
D
4
5
4
5
7
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–48
115
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Gruppierung: Schritt 3
select A, sum(D)
A
B
1
2
2
3
3
3
N
C
3
4
3
4
6
A
D
4
5
4
5
7
224
1
9
2
3
4
12
Datenbanksysteme
Sattler / Saake
SQL und weitere relationale Anfragesprachen
N
sum(D)
C
3
4
3
4
6
D
4
5
4
5
7
Wintersemester 2006/7
7–49
Erweiterungen des SFW-Blocks
Gruppierung: Schritt 4
having A<4 and sum(D)<10 and max(C)=4
A
N
sum(D)
1
9
2
3
4
12
C
3
4
3
4
6
D
4
5
4
5
7
Sattler / Saake
224
A
1
sum(D)
9
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–50
Erweiterungen des SFW-Blocks
Gruppierung - Beispiel
Anfrage „pro Musiker die Anzahl der zugehörigen Alben“:
select Name, count(*) as Anzahl
from Musiker natural join Album
group by Name
Ergebnisrelation:
Name
Apocalyptica
Subway To Sally
Rammstein
Neil Young
Sattler / Saake
116
Datenbanksysteme
Anzahl
1
2
1
1
Wintersemester 2006/7
7–51
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
having - Beispiel
Anfrage
6
select Name, count(*) as Anzahl
from Musiker natural join Album
group by Name having count(*) > 1
ergibt:
Name
Subway To Sally
Sattler / Saake
Anzahl
2
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–52
Erweiterungen des SFW-Blocks
Gruppierung - weitere Beispiele
Anfrage: „Gesamtwert der Alben pro Musiker“
select Name, sum(Preis) as Wert
from Musiker natural join Album
group by Name
Gruppierung ohne Ausgabe einer Aggregation:
select Name, sum(Preis) as Wert
from Musiker natural join Album
group by Name
having sum(Preis) > 100
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–53
Erweiterungen des SFW-Blocks
Attribute für Aggregation bzw. having
zulässige Attribute hinter select bei Gruppierung auf Relation
mit Schema R
Gruppierungsattribute G
Aggregationen auf Nicht-Gruppierungsattributen R − G
zulässige Attribute für having
dito
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–54
117
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Erweiterungen des SFW-Blocks
Sortierung mit order by
Notation
order by attributliste
Beispiel:
select Titel, Jahr
from
Album
order by Jahr asc
Sortierung aufsteigend (asc) oder absteigend (desc)
Sortierung als letzte Operation einer Anfrage
muss in der select-Klausel vorkommen
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Sortierattribut
Wintersemester 2006/7
7–55
Erweiterungen des SFW-Blocks
Behandlung von Nullwerten
skalare Ausdrücke: Ergebnis null, sobald Nullwert in die
Berechnung eingeht
in allen Aggregatfunktionen bis auf count(∗) werden Nullwerte
vor Anwendung der Funktion entfernt
fast alle Vergleiche mit Nullwert ergeben Wahrheitswert unknown
(statt true oder false)
Ausnahme: is null ergibt true, is not null ergibt false
Boolesche Ausdrücke basieren dann auf dreiwertiger Logik
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–56
Erweiterungen des SFW-Blocks
Behandlung von Nullwerten /2
and
true
unknown
false
or
true
unknown
false
true
true
unknown
false
true
true
true
true
not
true
unknown
false
Sattler / Saake
118
unknown
unknown
unknown
false
unknown
true
unknown
unknown
false
false
false
false
false
true
unknown
false
false
unknown
true
Datenbanksysteme
Wintersemester 2006/7
7–57
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Benannte Anfragen
Anfrageausdruck, der in der Anfrage mehrfach referenziert
werden kann
Notation
6
with anfrage-name [(spalten-liste) ] as
( anfrage-ausdruck )
Anfrage ohne with
select * from Album
where Preis >= 0.8 *
(select avg(Preis) from Album)
and Preis <= 1.2 *
(select avg(Preis) from Album)
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–58
Wintersemester 2006/7
7–59
Rekursion
Benannte Anfragen /2
Anfrage mit with
with Durchschnitt(Wert) as (
select avg(Preis)
from Album
)
select *
from Album, Durchschnitt
where Preis > 0.8 * Wert
and Preis <= 1.2 * Wert
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Rekursion
Rekursive Anfragen
Anwendung: Bill of Material-Anfragen, Berechnung der transitiven
Hülle (Flugverbindungen etc.)
Beispiel:
Flug Abflug
Erfurt
Erfurt
Leipzig
München
London
London
Sattler / Saake
Ankunft
Leizpig
München
London
Sydney
San Francisco
New York
Datenbanksysteme
Preis
100
150
200
1700
1300
1100
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–60
119
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Rekursive Anfragen /2
Flugverbindungen mit Standard-SQL
-- Direktflüge
select Abflug, Ankunft from Flug
union all -- 1-Stopp-Flüge
select F1.Abflug, F2.Ankunft
from Flug F1, Flug F2
where F1.Ankunft = F2.Abflug
union all -- 2-Stopp-Flüge
select F1.Abflug, F3.Ankunft
from Flug F1, Flug F2, Flug F3
where F1.Ankunft = F2.Abflug
and F2.Ankunft = F3.Abflug
union all -- 3-Stopp-Flüge
...
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–61
Rekursion
Rekursive Anfragen /3
Ansatz: selbstreferenzierende Anfrageausdrücke
Beispiel für konjunktive Anfragen (Datalog-Notation)
Verbindung(D, A) :- Flug(D, A).
Verbindung(D, A) :- Flug(D, A1), Verbindung(A1, A).
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–62
Rekursion
Rekursion in SQL:2003
Formulierung über erweiterte with recursive-Anfrage
Notation
with recursive rekursive-tabelle as (
anfrage-ausdruck -- rekursiver Teil
)
[traversierungsklausel] [zyklusklausel]
anfrage-ausdruck -- nicht rekursiver Teil
nicht rekursiver Teil: Anfrage auf Rekursionstabelle
Sattler / Saake
120
Datenbanksysteme
Wintersemester 2006/7
7–63
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Rekursion in SQL:2003 /2
rekursiver Teil:
Datenbanksysteme
Sattler / Saake
SQL und weitere relationale Anfragesprachen
6
-- Initialisierung
select ...
from tabelle where ...
-- Rekursionsschritt
union all
select ...
from tabelle, rekursionstabelle
where rekursionsbedingung
Wintersemester 2006/7
7–64
Rekursion
Rekursion in SQL:2003: Beispiel
with recursive Verbindung(Abflug, Ankunft)
as (
select Abflug, Ankunft
from Flug
where Abflug = ’Erfurt’
union all
select V.Abflug, F.Ankunft
from Verbindung V, Flug F
where V.Ankunft = F.Abflug )
select distinct * from Verbindung
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–65
Rekursion
Rekursion: Beispiel /2
Maschine Baugruppe
Auto
Motor
Motor
Motor
Getriebe
...
Sattler / Saake
Bauteil
Motor
Kolben
Kurbelwelle
Getriebe
Kupplung
...
Anzahl
1
4
1
1
1
...
Datenbanksysteme
TeilKosten
5356
124
89
560
290
...
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–66
121
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Rekursion: Beispiel /3
with recursive TListe (BNr, Menge, Kosten)
as (select Bauuteil, 1, 0.00
from Maschine
where Baugruppe = ’Auto’
union all
select m.Bauteil, m.Anzahl,
m.Anzahl * m.TeilKosten
from TListe t, Maschine m
where t.Bnr = m.Baugruppe)
select BNr, sum(Menge), sum(Kosten)
from TListe
group by BNr
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–67
Rekursion
Rekursion: Auswertung und Sicherheit
Auswertung implementierungsabhängig, etwa
Ableitung transitiver Verbindungen in jedem Rekursionsschritt (z.B.
aus der aktuellen Menge der Flughäfen erreichbare Ziele)
Hinzunahme dieser Verbindungen zur Ergebnismenge
Wiederholung bis keine weiteren Verbindungen ableitbar sind
Sicherheit: Endlichkeit der Rekursion
Voraussetzung: Monotonie, d.h. keine Änderung der
Rekursionstabelle
in SQL durch Steuerung der Zyklenerkennung und -behandlung
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–68
Rekursion
Rekursion: Zyklenbehandlung
Einschränkung der Rekursionstiefe
with recursive Verbindung(Abflug,Ankunft,Stopps)
as ( select Abflug, Ankunft, 0
from Flug where Abflug = ’Erfurt’
union all
select V.Abflug, F.Ankunft, Stopps+1
from Verbindung V, Flug F
where V.Ankunft = F.Abflug and Stopps < 1)
select * from Verbindung
Abflug
Erfurt
Erfurt
Erfurt
Erfurt
Sattler / Saake
122
Ankunft
Leizpig
München
London
Sydney
Datenbanksysteme
Stopps
0
0
1
1
Wintersemester 2006/7
7–69
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Rekursion: Zyklenbehandlung /2
Zyklusklausel
beim Erkennen von Duplikaten im Berechnungspfad von attrib:
cyclemark = ’Y’
Sicherstellen der Endlichkeit des Ergebnisses „von Hand“
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
6
cycle attrib set cyclemark to ’Y’ default ’N’
using cyclepath
7–70
Rekursion
Rekursion: Breiten- vs. Tiefensuche
Breitensuche
Sortierung nach Pfadlänge
Beispiel:
with recursive Verbindung(Abflug,Ankunft,Stopps)
as (
select Abflug, Ankunft, 0
from Flug
where Abflug = ’Erfurt’
union all
select V.Abflug, F.Ankunft, Stopps+1
from Verbindung V, Flug F
where V.Ankunft = F.Abflug)
select * from Verbindung
order by Stopps asc, Ankunft desc
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–71
Rekursion
Rekursion: Breiten- vs. Tiefensuche /2
Tiefensuche
Sortierung nach Pfaden
Beispiel:
with recursive Verbindung(Abflug, Ankunft, Weg)
as (
select Abflug, Ankunft, Abflug||Ankunft
from Flug
where Abflug = ’Erfurt’
union all
select V.Abflug, F.Ankunft, Weg||F.Ankunft
from Verbindung V, Flug F
where V.Ankunft = F.Abflug)
select * from Verbindung
order by Weg asc, Ankunft desc
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–72
123
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Rekursion: Breiten- vs. Tiefensuche /3
explizite Angabe
search breadth | depth first by attrib-liste
set ordng-spalte
select ... from ... order by ordng-spalte
Beispiel
with recursive Verbindung(Abflug, Ankunft)
as ( select Abflug, Ankunft
from Flug where Abflug = ’Erfurt’
union all
select V.Abflug, F.Ankunft
from Verbindung V, Flug F
where V.Ankunft = F.Abflug)
search breadth first by Ankunft set Weg
select * from Verbindung
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–73
Rekursion
SQL-Versionen
Geschichte
SEQUEL (1974, IBM Research Labs San Jose)
SEQUEL2 (1976, IBM Research Labs San Jose)
SQL (1982, IBM)
ANSI-SQL (SQL-86; 1986)
ISO-SQL (SQL-89; 1989; drei Sprachen Level 1, Level 2, + IEF)
(ANSI / ISO) SQL2 (als SQL-92 verabschiedet)
(ANSI / ISO) SQL3 (als SQL:1999 verabschiedet)
(ANSI / ISO) SQL:2003
trotz Standardisierung: teilweise Inkompatibilitäten zwischen
Systemen der einzelnen Hersteller
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–74
Rekursion
Oracle-Spezialitäten: Hierarchische Anfragen
Unterstützung hierarchischer Anfragen: rekursive Anfragen auf
azyklischen Strukturen
Notation
select ...
from tabelle
start with rekursionsinitialisierung
connect by rekursionsbedingung
Sattler / Saake
124
Datenbanksysteme
Wintersemester 2006/7
7–75
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Rekursion
Hierarchische Anfragen: Beispiele
6
select lpad(’ ’, 2 * (level-1)) || Abflug
|| ’-’ || Ankunft
from Flug
start with Abflug = ’Erfurt’
connect by prior Ankunft = Abflug
select Bauteil,sum(TeilKosten),sum(Anzahl)
from Maschine
start with Baugruppe = ’Auto’
connect by prior Bauteil = Baugruppe
group by Bauteil
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–76
Query by Example
Die Sprache QBE
„Query by Example“
Anfragen in QBE: Einträge in Tabellengerüsten
Intuition: Beispieleinträge in Tabellen
Vorläufer verschiedener tabellenbasierter Anfrageschnittstellen
kommerzieller Systeme
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–77
Query by Example
Anfragen in QBE
Anfrage: „Alle Rock-Alben aus den Jahren vor 2006“
Album
ANr
Titel
P.
Jahr
<2006
Genre
Rock
Preis
MNr
{t | Album(_, t, j, ’Rock’, _, _) ∧ j < 2006}
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–78
125
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
Anfragen in QBE /2
Anfrage: „Alle Rock-Alben von deutschen Musikern“
Album
ANr
Titel
P.
Musiker
Jahr
MNr
_musiker
Genre
Rock
Name
Preis
MNr
_musiker
Land
Deutschland
{t | Album(_, t, _, ’Rock’, _, m)
∧ Musiker(m, _, ’Deutschland’)}
Datenbanksysteme
Sattler / Saake
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–79
Query by Example
Anfragen in QBE /3
Anfrage: „Alben von Musikern aus Deutschland oder Österreich“
Album
ANr
Titel
P.
Musiker
Jahr
Genre
MNr
_musiker
Preis
Name
MNr
_musiker
Land
_land
CONDITIONS
_land = Deutschland or _land = Österreich
{t | Album(_, t, _, ’Rock’, _, m) ∧ Musiker(m, _, l)∧
(l = ’Deutschland’ ∨ l = ’Österreich’)}
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–80
Query by Example
Alternative Lösung
Album
ANr
Titel
P.
P.
Musiker
Jahr
MNr
_eins
_zwei
Genre
Name
Preis
MNr
_eins
_zwei
Land
Deutschland
Österreich
{t | Album(_, t, _, ’Rock’, _, m) ∧ Musiker(m, _, l)∧
(l = ’Deutschland’ ∨ l = ’Österreich’)}
Sattler / Saake
126
Datenbanksysteme
Wintersemester 2006/7
7–81
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
Anfragen in QBE /4
Anfrage: „Länder mit zwei oder mehr Musikern“
MNr
_eins
_zwei
Name
Land
P. _land
_land
6
Musiker
{l | Musiker(x, _, l) ∧ Musiker(y, _, l)}
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–82
Query by Example
Anfragen in QBE /5
Anfrage: „Infos zu Alben und Musikern“
Album
ANr
Titel
_titel
Musiker
Album_Info
P.
Jahr
MNr
_mnr
Titel
_titel
Sattler / Saake
Genre
_genre
Name
_name
Genre
_genre
Preis
_preis
Land
Musiker
_name
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
MNr
_mnr
Preis
_preis
Wintersemester 2006/7
7–83
Query by Example
Anfragen in QBE /6
Anfrage: „die aktuellsten Alben“
Album
P.
¬
ANr
Titel
Jahr
_jahr
> _jahr
Genre
Preis
MNr
{atjgpm | Album(a, t, j, g, p, m)∧
¬∃a ∃t ∃j ∃g ∃p ∃m (Album(a , t , j , g , p , m ) ∧ j > j)}
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–84
127
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
Sortierung in QBE
Anfrage: „alle Alben sortiert nach Jahr und Preis“
Album
P.
ANr
Titel
Jahr
AO(1).
Genre
Preis
AO(2).
MNr
AO: Ascending Order = aufsteigend
DO: Descending Order = absteigend
Parameter gibt Priorität an
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–85
Query by Example
Aggregierung in QBE
Anfrage: „Wert aller Rock-Alben“
Album
ANr
Titel
P.
Jahr
Genre
Rock
Preis
P.SUM.ALL. _x
MNr
Agregatfunktionen SUM., COUNT., AVG. etc.
Duplikateliminierung mit UN.ALL., Multimenge mit ALL.
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–86
Query by Example
Aggregierung in QBE /2
Anfrage: „Anzahl der Musiker“
Musiker
Sattler / Saake
128
MNr
Name
P.CNT.UN.ALL. _x
Land
Datenbanksysteme
Wintersemester 2006/7
7–87
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
Formale Semantik von QBE
Beispielelemente von QBE entsprechen Bereichsvariablen des
Bereichskalküls
6
Analog „_“-Symbolen im Kalkül entsprechen „leere
Spalteneinträge“ paarweise verschiedenen Bereichsvariablen;
leere Spalten: alle Positionen, an denen keine Bereichsvariable
steht (auch <10)
Jede Zeile in einer Relation R entspricht Teilformel
R(u1 , . . . , un ) ∧ φ
wobei ui Konstanten oder Bereichsvariablen und φ Konjunktion
der jeweiligen Zeilenbedingungen
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–88
Query by Example
Formale Semantik von QBE /2
allgemeine QBE-Anfrage (positive, negierte Zeilen, Einträge in der
Condition Box) entspricht dem folgendem
Bereichskalkül-Ausdruck:
{x1 . . . xm |
∃y1 . . . ∃yn
i-te positive Zeile
i
∧
¬[∃zj1 . . . ∃zjp j-te negierte Zeile ]
j
∧
k-te Bedingung in Condition Box }
k
x1 . . . xm Variablen mit P.
y1 . . . yn restliche Variablen in positiven Zeilen
zj1 . . . zjp restliche implizite Variablen in der j-ten negierten Zeile
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–89
Query by Example
Ausdrucksfähigkeit von QBE
QBE-Anfragen können nur Kalkülausdrücken der folgenden Form
entsprechen:
{· · · | ∃ . . . (∧ . . . ¬(∃ . . . ∃ . . . ) ∧ . . . }
{· · · | ∃ . . . ∀ . . . ∀ . . . Rest ohne Quantoren }
Der QBE-Kern ist relational vollständig.
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–90
129
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
Relationenalgebra und QBE
Algebra
Projektion
Selektion
Umbenennung
Verbund
QBE
mit P. markierte Spalten
1. Vergleiche als Spalteneinträge
2. Condition Box
explizite Ausgabetabelle
Verbindung zweier Tabellen mittels Beispielelementen (Bereichsvariablen)
Datenbanksysteme
Sattler / Saake
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–91
Query by Example
Änderungen in QBE
Operation: „Einfügen eines neuen Musikers.“
Musiker
I.
MNr
110
Sattler / Saake
Name
Deep Purple
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Land
GB
Wintersemester 2006/7
7–92
Query by Example
Änderungen in QBE /2
Operation: „Einfügen aller Rock-Singles als Alben.“
Album
I.
ANr
_anr
Titel
_titel
Jahr
_jahr
Genre
Rock
Preis
_preis
MNr
_mnr
Single
ANr
_anr
Titel
_titel
Jahr
_jahr
Genre
Rock
Preis
_preis
MNr
_mnr
Sattler / Saake
130
Datenbanksysteme
Wintersemester 2006/7
7–93
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
Änderungen in QBE /3
Operation: „Löschen aller französischen Musiker.“
MNr
Name
Land
Frankreich
Datenbanksysteme
Sattler / Saake
SQL und weitere relationale Anfragesprachen
6
Musiker
D.
Wintersemester 2006/7
7–94
Query by Example
Änderungen in QBE /4
Operation: „Preis aller Alben von 2004 reduzieren.“
Album
ANr
Titel
Jahr
2004
Genre
Preis
U. _wert * 0.9
MNr
Verkürzte Form der folgenden Änderung:
Album
ANr
Titel
U.
Jahr
2004
2004
Sattler / Saake
Genre
Preis
_wert
_wert * 0.9
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
MNr
Wintersemester 2006/7
7–95
Query by Example
Änderungen in QBE /5
Alternative Formulierung:
Album
U.
ANr
_anr
_anr
Titel
_titel
_titel
Jahr
2004
2004
Genre
_genre
_genre
Preis
_alt
_neu
MNr
_mnr
_mnr
Condition
_neu = _alt * 0.9
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–96
131
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
QBE in MS-Access
MS-Access: Datenbankprogramm für Windows
Basisrelationen mit Schlüsseln
Fremdschlüssel über graphische Angabe von Beziehungen
graphische Definition von Anfragen (SQL-ähnlich)
interaktive Definition von Formularen und Berichten
Unterstützung von QBE
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–97
Wintersemester 2006/7
7–98
Wintersemester 2006/7
7–99
Query by Example
Access: Projektion und Selektion
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Query by Example
Access: Projektion und Selektion /2
Sattler / Saake
132
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
Query by Example
6
Access: Ungewißheitsselektion
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–100
Wintersemester 2006/7
7–101
Wintersemester 2006/7
7–102
Query by Example
Access: Selektion mit Disjunktion
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Query by Example
Access: Verbund
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
133
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
Programmiersprachenanbindung
Kopplungsarten:
prozedurale oder CALL-Schnittstellen (call level interface)
Beispiele: SQL/CLI, ODBC, JDBC, . . .
Einbettung einer DB-Sprache in Programmiersprachen
statische Einbettung: Vorübersetzer-Prinzip
SQL-Anweisungen zur Übersetzungszeit festgelegt
Beispiele: Embedded SQL, SQLJ
dynamische Einbettung:
Konstruktion von SQL-Anweisungen zur Laufzeit
Spracherweiterungen und neue Sprachentwicklungen
Beispiele: SQL/PSM, PL/SQL, Transact-SQL, PL/pgSQL
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–103
Wintersemester 2006/7
7–104
JDBC & SQLJ
Cursor-Konzept
Anwendungsprogramm
Datenbank
Relation
Cursor
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
JDBC: Überblick
Datenbankzugriffsschnittstelle für Java
abstrakte, datenbankneutrale Schnittstelle
vergleichbar mit ODBC
Low-Level-API: direkte Nutzung von SQL
Java-Package java.sql
DriverManager: Einstiegspunkt, Laden von Treibern
Connection: Datenbankverbindung
Statement: Ausführung von Anweisungen über eine Verbindung
ResultSet: verwaltet Ergebnisse einer Anfrage, Zugriff auf
einzelne Spalten
Sattler / Saake
134
Datenbanksysteme
Wintersemester 2006/7
7–105
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
JDBC: Struktur
DriverManager
getConnection
Connection
Statement
6
createStatement
Statement
executeQuery
ResultSet
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
ResultSet
Wintersemester 2006/7
7–106
JDBC & SQLJ
JDBC: Treiberkonzept
JavaApplikation
JDBC-API
JDBCTreibermanager
NativeProtokollTreiber
JDBCNetTreiber
JDBCODBCBridge
NativeAPITreiber
DBMiddleware
ODBC
ClientBibliothek
ClientBibliothek
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–107
Wintersemester 2006/7
7–108
JDBC & SQLJ
JDBC: Ablauf
1
Aufbau einer Verbindung zur Datenbank
Angabe der Verbindungsinformationen
Auswahl und Laden des Treibers
2
Senden einer SQL-Anweisung
Definition der Anweisung
Belegung von Parametern
3
Verarbeiten der Anfrageergebnisse
Navigation über Ergebnisrelation
Zugriff auf Spalten
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
135
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
JDBC: Verbindungsaufbau
1
Treiber laden
Class.forName ("com.company.DBDriver");
2
Verbindung herstellen
Connection con;
String url = "jdbc:subprotocol:datasource";
con = DriverManager.getConnection
(url, "scott", "tiger");
JDBC-URL spezifiziert
Datenquelle/Datenbank
Verbindungsmechanismus (Protokoll, Server und Port)
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–109
JDBC & SQLJ
JDBC: Anfrageausführung
1
Anweisungsobjekt (Statement) erzeugen
Statement stmt = con.createStatement();
2
Anweisung ausführen
String query =
"SELECT Titel, Jahr FROM Album";
ResultSet rset = stmt.executeQuery (query);
Klasse java.sql.Statement
Ausführung von Anfragen (SELECT) mit executeQuery
Ausführung von Änderungsanweisungen (DELETE, INSERT,
UPDATE) mit executeUpdate
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–110
JDBC & SQLJ
JDBC: Ergebnisverarbeitung
1
Navigation über Ergebnismenge (Cursor-Prinzip)
while (rset.next()) {
// Verarbeitung der einzelnen Tupel
...
}
2
Zugriff auf Spaltenwerte über getType-Methoden
über Spaltenindex
String titel = rset.getString(1);
über Spaltenname
String titel = rset.getString("Titel");
Sattler / Saake
136
Datenbanksysteme
Wintersemester 2006/7
7–111
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
JDBC: Fehlerbehandlung
Fehlerbehandlung mittels Exception-Mechanismus
SQLException für alle SQL- und DBMS-Fehler
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
6
try {
// Aufruf von JDBC-Methoden
...
} catch (SQLException exc) {
System.out.println("SQLException: "+
exc.getMessage());
}
7–112
JDBC & SQLJ
JDBC: Änderungsoperationen
DDL- und DML-Operationen mittels executeUpdate
liefert Anzahl der betroffenen Zeilen (für DML-Operationen)
Statement stmt = con.createStatement();
int rows = stmt.executeUpdate(
"UPDATE Album " +
"SET Preis = Preis * 0.9 " +
"WHERE ANr = 1014");
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–113
JDBC & SQLJ
JDBC: Transaktionssteuerung
Methoden von Connection
commit ()
rollback ()
Auto-Commit-Modus
implizites Commit nach jeder Anweisung
Transaktion besteht nur aus einer Anweisung
Umschalten mittels setAutoCommit (boolean)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–114
137
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
SQLJ: Embedded SQL für Java
Einbettung von SQL-Anweisungen in Java-Quelltext
Vorübersetzung des erweiterten Quelltextes in echten Java-Code
durch Translator sqlj
Überprüfung der SQL-Anweisungen
korrekte Syntax
Übereinstimmung der Anweisungen mit DB-Schema
Typkompatibilität der für Datenaustausch genutzten Variablen
Nutzung von JDBC-Treibern
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–115
Wintersemester 2006/7
7–116
JDBC & SQLJ
SQLJ: Prinzip
SQLJ-Programm
Syntax- & Semantikprüfung
SQLJ-Programm
Java-Quellcode
SQLJ-Profile
Java-Compiler
Customizer
Bytecode
Custom-Profile
SQLJ-Laufzeitsystem
JDBC-Treiber
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
SQLJ-Anweisungen
Kennzeichnung durch #sql Deklarationen
Klassendefinitionen für Iteratoren
SQL-Anweisungen: Anfragen, DML- und DDL-Anweisungen
#sql { SQL-Operation };
Beispiel:
#sql { INSERT INTO Album VALUES
(1030, "Reflections", 2003, "Rock", 12.97,
103) };
Sattler / Saake
138
Datenbanksysteme
Wintersemester 2006/7
7–117
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
JDBC & SQLJ
Host-Variablen
Variablen einer Host-Sprache (hier Java), die in
SQL-Anweisungen auftreten können
Verwendung: Austausch von Daten zwischen Host-Sprache und
SQL
6
Kennzeichnung durch ":variable"
Beispiel:
String titel;
int jahr =2006;
#sql { SELECT Titel INTO :titel
FROM Album WHERE Jahr < :jahr };
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–118
JDBC & SQLJ
Iteratoren
1
Deklaration des Iterators
#sql public iterator AlbumIter (
String titel, int jahr);
2
Definition des Iteratorobjektes
AlbumIter iter;
3
Ausführung der Anweisung
#sql iter = { SELECT titel, jahr FROM Album };
4
Navigation
while (iter.next())
System.out.println(iter.titel () + " "
+ iter.jahr());
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–119
PL/SQL
Oracle PL/SQL
prozedurale Erweiterung von SQL: Blöcke, Kontrollstrukturen
Impementierung von gespeicherten Modulen (Prozeduren,
Funktionen, Triggern, etc.)
Code, der im DBS gespeichert ist und dort auch ausgeführt wird
Blockstruktur
declare
/* Deklaration von Variablen, Typen, ...*/
begin
/* prozedurale und SQL-Anweisungen */
exception
/* optionale Fehlerbehandlung */
end;
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
7–120
139
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
PL/SQL
PL/SQL: Variablen und Typen
unterstützte Typdeklarationen
SQL-Datentypen
spezifische PL/SQL-Typen (u.a. number, boolean)
Übernahme von Typdeklarationen
declare
preis number;
titel varchar(20);
mName Musiker.name%type;
mTupel Musiker%rowtype;
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–121
Wintersemester 2006/7
7–122
PL/SQL
PL/SQL: Kontrollstrukturen
if i = 10 then
/* ... */
else
/* sonst */
end if;
while i < 100 loop
/* ... */
i := i + 1;
end loop;
for i in 1..100 loop
/* ... */
end loop;
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
PL/SQL
PL/SQL: Cursor
Verarbeitung von Anfrageergebnissen über Cursor
declare
mTupel Musiker%rowtype;
cursor mCursor is select * from Musiker;
begin
open mCursor;
loop
fetch mCursor into mTupel;
exit when mCursor%notfound;
/* Zugriff auf mTupel.Titel, ... */
end loop;
close mCursor;
end;
Sattler / Saake
140
Datenbanksysteme
Wintersemester 2006/7
7–123
TU Ilmenau/Uni Magdeburg, WS 2006/07
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
PL/SQL
PL/SQL: Cursor /2
alternative Notation über for-Schleife
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
6
begin
for mTupel in mCursor loop
/* siehe oben */
end loop;
end;
Wintersemester 2006/7
7–124
PL/SQL
PL/SQL: Prozeduren
Definition einer Prozedur
create procedure teuerstes_album (jahr in int,
t_titel out varchar) as
declare
cursor aCurs (j in Album.Jahr%type) is
select titel from Album where jahr = j and
preis = ( select max(preis) from Album
where jahr = j);
begin
open aCurs (jahr);
fetch aCurs into t_titel;
close aCurs;
end;
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–125
Wintersemester 2006/7
7–126
PL/SQL
PL/SQL: Prozeduren /2
Aufruf
declare
titel varchar(50);
begin
teuerstes_album(2006, titel);
dbms_output.put_line(titel);
end;
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
141
6 SQL und weitere Sprachen
SQL und weitere relationale Anfragesprachen
PL/SQL
PL/SQL: Funktionen
Definition einer Funktion
create function euro2dollar (betrag in number)
as begin
return betrag * 1.21;
end;
Nutzung auch in Anfragen
select Titel, euro2dollar(Preis)
from Album
where euro2dollar(Preis) < 50;
Sattler / Saake
Datenbanksysteme
SQL und weitere relationale Anfragesprachen
Wintersemester 2006/7
7–127
PL/SQL
Zusammenfassung
SQL als Standardsprache
SQL-Kern mit Bezug zur Relationenalgebra
Erweiterungen: Gruppierung, Rekursion etc.
Query by Example
Schnittstellen zur Anwendungsentwicklung: JDBC und SQLJ
Prozedurale SQL-Erweiterungen
Sattler / Saake
142
Datenbanksysteme
Wintersemester 2006/7
7–128
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
1
Grundbegriffe
2
Transaktionsbegriff
3
Transaktionen in SQL
4
Integritätsbedingungen in SQL
5
Trigger
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–1
Grundbegriffe
Integrität
Integritätsbedingung (engl. integrity constraint oder assertion):
Bedingung für die „Zulässigkeit“ oder „Korrektheit“
in Bezug auf Datenbanken:
(einzelne) Datenbankzustände,
Zustandsübergänge vom alten in den neuen Datenbankzustand,
langfristige Datenbankentwicklungen
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–2
Grundbegriffe
Klassifikation von Integrität
Bedingungsklasse
statisch
dynamisch transitional
temporal
Sattler / Saake
144
zeitlicher Kontext
Datenbankzustand
Zustandsübergang
Zustandsfolge
Datenbanksysteme
Wintersemester 2006/7
8–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Grundbegriffe
Inhärente Integritätsbedingungen im RM
1
Typintegrität:
SQL erlaubt Angabe von Wertebereichen zu Attributen
Erlauben oder Verbieten von Nullwerten
2
Schlüsselintegrität:
3
Referentielle Integrität:
Angabe eines Schlüssels für eine Relation
7
die Angabe von Fremdschlüsseln
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–4
Transaktionsbegriff
Beispielszenarien
Platzreservierung für Flüge gleichzeitig aus vielen Reisebüros
→ Platz könnte mehrfach verkauft werden, wenn mehrere
Reisebüros den Platz als verfügbar identifizieren
überschneidende Kontooperationen einer Bank
statistische Datenbankoperationen
→ Ergebnisse sind verfälscht, wenn während der Berechnung
Daten geändert werden
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–5
Transaktionsbegriff
Transaktion
Eine Transaktion ist eine Folge von Operationen (Aktionen), die die
Datenbank von einem konsistenten Zustand in einen konsistenten,
eventuell veränderten, Zustand überführt, wobei das ACID-Prinzip
eingehalten werden muss.
Aspekte:
Semantische Integrität: Korrekter (konsistenter) DB-Zustand nach
Ende der Transaktion
Ablaufintegrität: Fehler durch „gleichzeitigen“ Zugriff mehrerer
Benutzer auf dieselben Daten vermeiden
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
8–6
145
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionsbegriff
ACID-Eigenschaften
Atomicity (Atomarität):
Transaktion wird entweder ganz oder gar nicht ausgeführt
Consistency (Konsistenz oder auch Integritätserhaltung):
Datenbank ist vor Beginn und nach Beendigung einer Transaktion
jeweils in einem konsistenten Zustand
Isolation (Isolation):
Nutzer, der mit einer Datenbank arbeitet, sollte den Eindruck
haben, dass er mit dieser Datenbank alleine arbeitet
Durability (Dauerhaftigkeit / Persistenz):
nach erfolgreichem Abschluss einer Transaktion muss das
Ergebnis dieser Transaktion „dauerhaft“ in der Datenbank
gespeichert werden
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–7
Transaktionsbegriff
Kommandos einer Transaktionssprache
Beginn einer Transaktion: Begin-of-Transaction-Kommando BOT
(in SQL implizit!)
commit: die Transaktion soll erfolgreich beendet werden
abort: die Transaktion soll abgebrochen werden
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–8
Transaktionsbegriff
Transaktion: Integritätsverletzung
Beispiel:
Übertragung eines Betrages B von einem Haushaltsposten K1 auf
einen anderen Posten K2
Bedingung: Summe der Kontostände der Haushaltsposten bleibt
konstant
vereinfachte Notation
Transfer = < K1:=K1-B; K2:=K2+B >;
Realisierung in SQL: als Sequenz zweier elementarer
Änderungen
Bedingung ist zwischen den einzelnen
Änderungsschritten nicht unbedingt erfüllt!
Sattler / Saake
146
Datenbanksysteme
Wintersemester 2006/7
8–9
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionsbegriff
Transaktion: Verhalten bei Systemabsturz
Fehler
T1
T2
T3
T4
7
T5
Zeit
tf
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–10
Transaktionsbegriff
Transaktion: Verhalten bei Systemabsturz /2
Folgen:
Inhalt des flüchtigen Speichers zum Zeitpunkt tf ist unbrauchbar →
Transaktionen in unterschiedlicher Weise davon betroffen
Transaktionszustände:
zum Fehlerzeitpunkt noch aktive Transaktionen (T2 und T4 )
bereits vor dem Fehlerzeitpunkt beendete Transaktionen (T1 , T3 und
T5 )
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–11
Transaktionsbegriff
Vereinfachtes Modell für Transaktion
Repräsentation von Datenbankänderungen einer Transaktion
read(A,x): weise den Wert des DB-Objektes A der Variablen x zu
write(x, A): speichere den Wert der Variablen x im DB-Objekt A
Beispiel einer Transaktion T:
read(A, x); x := x − 200; write(x, A);
read(B, y); y := y + 100; write(y, B);
Ausführungsvarianten für zwei Transaktionen T1 , T2 :
seriell, etwa T1 vor T2
„gemischt“, etwa abwechselnd Schritte von T1 und T2
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
8–12
147
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionsbegriff
Probleme im Mehrbenutzerbetrieb
Inkonsistentes Lesen: Nonrepeatable Read
Abhängigkeiten von nicht freigegebenen Daten: Dirty Read
Das Phantom-Problem
Verlorengegangenes Ändern: Lost Update
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–13
Transaktionsbegriff
Nonrepeatable Read
Beispiel:
Zusicherung x = A + B + C am Ende der Transaktion T1
x, y, z seien lokale Variablen
Ti ist die Transaktion i
Integritätsbedingung A + B + C = 0
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–14
Wintersemester 2006/7
8–15
Transaktionsbegriff
Beispiel für inkonsistentes Lesen
T1
read(A, x);
T2
read(A, y);
y := y/2;
write(y, A);
read(C, z);
z := z + y;
write(z, C);
commit;
read(B, y);
x := x + y;
read(C, z);
x := x + z;
commit;
Sattler / Saake
148
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionsbegriff
Dirty Read
T1
read(A, x);
x := x + 100;
write(x, A);
T2
7
read(A, x);
read(B, y);
y := y + x;
write(y, B);
commit;
abort;
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–16
Transaktionsbegriff
Das Phantom-Problem
T1
select count (*)
into X
from Kunde;
T2
insert
into Kunde
values (’Meier’, 0, . . . );
commit;
update Kunde
set Bonus =
Bonus +10000/X;
commit;
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–17
Wintersemester 2006/7
8–18
Transaktionsbegriff
Lost Update
T1
read(A, x);
x := x + 1;
write(x, A);
Sattler / Saake
T2
read(A, x);
x := x + 1;
write(x, A);
Datenbanksysteme
A
10
10
10
10
11
11
TU Ilmenau/Uni Magdeburg, WS 2006/07
149
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionsbegriff
Serialisierbarkeit
Eine verschränkte Ausführung mehrerer Transaktionen heißt
serialisierbar, wenn ihr Effekt identisch zum Effekt einer (beliebig
gewählten) seriellen Ausführung dieser Transaktionen ist.
Schedule: „Ablaufplan“ für Transaktion, bestehend aus Abfolge
von Transaktionsoperationen
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–19
Wintersemester 2006/7
8–20
Transaktionen in SQL
Transaktionen in SQL-DBS
Aufweichung von ACID in SQL: Isolationsebenen
set transaction
[ { read only | read write }, ]
[isolation level
{ read uncommitted |
read committed |
repeatable read |
serializable }, ]
[ diagnostics size ...]
Standardeinstellung:
set transaction read write,
isolation level serializable
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Transaktionen in SQL
Bedeutung der Isolationsebenen
read uncommitted
schwächste Stufe: Zugriff auf nicht geschriebene Daten, nur für
read only Transaktionen
statistische und ähnliche Transaktionen (ungefährer Überblick, nicht
korrekte Werte)
keine Sperren → effizient ausführbar, keine anderen Transaktionen
werden behindert
read committed
nur Lesen endgültig geschriebener Werte, aber nonrepeatable read
möglich
repeatable read
kein nonrepeatable read, aber Phantomproblem kann auftreten
serializable
garantierte Serialisierbarkeit
Sattler / Saake
150
Datenbanksysteme
Wintersemester 2006/7
8–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Transaktionen in SQL
Isolationsebenen: read committed
2
3
4
5
T2
update Album
set Titel = ’Amplified – A
Decade’
where ANr = 1014
7
1
T1
set transaction
isolation level
read committed
select Titel from
Album where ANr = 1014
−→ Amplified
select Titel from
Album where ANr = 1014
−→ Amplified
commit
select Titel from
Album where ANr = 1014
−→ Amplified – A Decade
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–22
Transaktionen in SQL
read committed /2
1
T1
set transaction
isolation level
read committed
select Titel from
Album where ANr = 1014
2
3
4
5
T2
update Album
set Titel = ’Amplified – A
Decade ...’
where ANr = 1014
update Album
set Titel = ’A Decade of’
where ANr = 1014
−→ blockiert
commit
commit
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–23
Transaktionen in SQL
Isolationsebenen: serializable
1
T1
set transaction
isolation level
serializable
select Titel from
Album where ANr = 1014
2
4
5
T2
update Album
set Titel = ’Amplified – A
Decade’
where ANr = 1014
commit
update Album
set Titel = ’A Decade of
...’
where ANr = 1014
−→ Abbruch
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
8–24
151
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Integritätsbedingungen in SQL
Integritätsbedingungen in SQL-DDL
not null: Nullwerte verboten
default: Angabe von Default-Werten
check ( search-condition ): Attributspezifische Bedingung
(in der Regel Ein-Tupel-Integritätsbedingung)
primary key: Angabe eines Primärschlüssel
foreign key ( Attribut(e))
references Tabelle( Attribut(e) ):
Angabe der referentiellen Integrität
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–25
Integritätsbedingungen in SQL
Integritätsbedingungen: Wertebereiche
create domain: Festlegung eines benutzerdefinierten
Wertebereichs
Beispiel
create domain MusikGenre varchar(20)
default ’Rock’
Anwendung
create table Album (
ANr int primary key,
Titel varchar(50),
Genre MusikGenre,
...
);
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–26
Integritätsbedingungen in SQL
Integritätsbedingungen: check-Klausel
check: Festlegung weitere lokale Integritätsbedingungen
innerhalb der zu definierenden Wertebereiche, Attribute und
Relationenschemata
Beispiel: Einschränkung der zulässigen Werte
Anwendung
create table Album (
ANr int primary key,
Titel varchar(50),
Jahr int check(Jahr between 1980 and 2010),
Genre MusikGenre,
...
);
Sattler / Saake
152
Datenbanksysteme
Wintersemester 2006/7
8–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Integritätsbedingungen in SQL
Erhaltung der referentiellen Integrität
Überprüfung der Fremdschlüsselbedingungen nach
Datenbankänderungen
für πA (r1 ) ⊆ πK (r2 ),
z.B. πMNr (Album) ⊆ πMNr (Musiker)
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
7
Tupel t wird eingefügt in r1 ⇒ überprüfen, ob t ∈ r2 existiert mit:
t (K) = t(A), d.h. t(A) ∈ πK (r2 )
falls nicht ⇒ abweisen
Tupel t wird aus r2 gelöscht ⇒ überprüfen, ob σA=t (K) (r1 ) = {},
d.h. kein Tupel aus r1 referenziert t
falls nicht leer ⇒ abweisen oder Tupel aus r1 , die t referenzieren,
löschen (bei kaskadierendem Löschen)
8–28
Integritätsbedingungen in SQL
Überprüfungsmodi von Bedingungen
on update | delete
Angabe eines Auslöseereignisses, das die Überprüfung der
Bedingung anstößt
cascade | set null | set default | no action
Kaskadierung: Behandlung einiger Integritätsverletzungen pflanzt
sich über mehrere Stufen fort, z.B. Löschen als Reaktion auf
Verletzung der referentieller Integrität
deferred | immediate legt Überprüfungszeitpunkt für eine
Bedingung fest
deferred: Zurückstellen an das Ende der Transaktion
immediate: sofortige Prüfung bei jeder relevanten
Datenbankänderung
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–29
Integritätsbedingungen in SQL
Überprüfungsmodi: Beispiel
Kaskadierendes Löschen
create table Album (
ANr int primary key,
Titel varchar(50) not null,
Preis float not null,
Jahr int not null,
MNr int,
foreign key (MNr)
references Musiker (MNr)
on delete cascade)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
8–30
153
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Integritätsbedingungen in SQL
Die assertion-Klausel
Assertion: Prädikat, das eine Bedingung ausdrückt, die von der
Datenbank immer erfüllt sein muss
Syntax (SQL:2003)
create assertion name check ( prädikat )
Beispiele:
create assertion Preise check
( ( select sum (Preis)
from Album) < 10000 )
create assertion Preise2 check
( not exists (
select * from Album where Preis 100) )
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–31
Trigger
Trigger
Trigger: Anweisung/Prozedur, die bei Eintreten eines bestimmten
Ereignisses automatisch vom DBMS ausgeführt wird
Anwendung:
Erzwingen von Integritätsbedingungen („Implementierung“ von
Integritätsregeln)
Auditing von DB-Aktionen
Propagierung von DB-Änderungen
Definition:
create trigger ...
after <Operation>
<Anweisungen>
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–32
Trigger
Beispiel für Trigger
Realisierung eines berechneten Attributs durch zwei Trigger:
Einfügen von neuen Aufträgen
create trigger Auftragszählung+
on insertion of Auftrag A:
update Kunde
set AnzAufträge = AnzAufträge + 1
where KName = new A.KName
analog für Löschen von Aufträgen:
create trigger Auftragszählungon deletion ...:
update ...- 1 ...
Sattler / Saake
154
Datenbanksysteme
Wintersemester 2006/7
8–33
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Trigger
Trigger: Entwurf und Implementierung
Spezifikation von
Ereignis und Bedingung für Aktivierung des Triggers
Aktion(en) zur Ausführung
Syntax in SQL:2003 festgelegt
7
verfügbar in den meisten kommerziellen Systemen (aber mit
anderer Syntax)
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–34
Wintersemester 2006/7
8–35
Trigger
SQL:2003-Trigger
Syntax:
create trigger <Name: >
after | before <Ereignis>
on <Relation>
[ when <Bedingung> ]
begin atomic < SQL-Anweisungen > end
Ereignis:
insert
update [ of <Liste von Attributen> ]
delete
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Trigger
Weitere Angaben bei Triggern
for each row bzw. for each statement: Aktivierung des
Triggers für jede Einzeländerungen einer mengenwertigen
Änderung oder nur einmal für die gesamte Änderung
before bzw. after: Aktivierung vor oder nach der Änderung
referencing new as bzw. referencing old as: Binden
einer Tupelvariable an die neu eingefügten bzw. gerade
gelöschten („alten’“) Tupel einer Relation
Tupel der Differenzrelationen
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
8–36
155
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Trigger
Beispiel für Trigger
Kein Kundenkonto darf unter 0 absinken:
create trigger bad_account
after update of Kto on KUNDE
referencing new as INSERTED
when (exists
(select * from INSERTED where Kto < 0)
)
begin atomic
rollback;
end
ähnlicher Trigger für insert
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–37
Trigger
Beispiel für Trigger /2
Kunden müssen gelöscht werden, wenn sie keine Waren mehr
bestellt haben:
create trigger bad_customer
delete on BESTELLUNG
referencing old as O
for each row
when (not exists
(select * from BESTELLUNG B
where B.KNr = O.KNr))
begin atomic
delete from KUNDE where KNr = O.KNr;
end
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–38
Trigger
Integritätssicherung durch Trigger
1
Bestimme Objekt oi , für das die Bedingung φ überwacht werden
soll
i.d.R. mehrere oi betrachten, wenn Bedingung
relationsübergreifend ist
Kandidaten für oi sind Tupel der Relationsnamen, die in φ
auftauchen
2
Bestimme die elementaren Datenbankänderungen uij auf
Objekten oi , die φ verletzen können
Regeln: z.B. Existenzforderungen beim Löschen und Ändern
prüfen, jedoch nicht beim Einfügen etc.
Sattler / Saake
156
Datenbanksysteme
Wintersemester 2006/7
8–39
TU Ilmenau/Uni Magdeburg, WS 2006/07
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Trigger
Integritätssicherung durch Trigger /2
3. Bestimme je nach Anwendung die Reaktion ri auf
Integritätsverletzung
Rücksetzen der Transaktion (rollback)
korrigierende Datenbankänderungen
4. Formuliere folgende Trigger:
7
create trigger t-phi-ij after uij on oi
when ¬φ
begin ri end
5. Wenn möglich, vereinfache entstandenen Trigger
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–40
Trigger
Trigger in Oracle
Implementierung in PL/SQL
Notation
create [ or replace ] trigger trigger-name
before | after
insert or update [ of spalten ]
or delete on tabelle
[ for each row
[ when ( prädikat ) ] ]
PL/SQL-Block
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–41
Trigger
Trigger in Oracle: Arten
Anweisungsebene (statement level trigger): Trigger wird ausgelöst
vor bzw. nach der DML-Anweisung
Tupelebene (row level trigger): Trigger wird vor bzw. nach jeder
einzelnen Modifikation ausgelöst (one tuple at a time)
Trigger auf Tupelebene:
Prädikat zur Einschränkung (when)
Zugriff auf altes (:old.col) bzw. neues (:new.col) Tupel
für delete: nur (:old.col)
für insert: nur (:new.col)
in when-Klausel nur (new.col) bzw. (old.col)
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
8–42
157
7 Transaktionen, Integrität und Trigger
Transaktionen, Integrität und Trigger
Trigger
Trigger in Oracle /2
Transaktionsabbruch durch
raise_application_error(code, message)
Unterscheidung der Art der DML-Anweisung
if deleting then ... end if;
if updating then ... end if;
if inserting then ... end if;
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–43
Trigger
Trigger in Oracle: Beispiel
Kein Kundenkonto darf unter 0 absinken:
create or replace trigger bad_account
after insert or update of Kto on KUNDE
for each row
when (:new.Kto < 0)
begin
raise_application_error(-20221,
’Nicht unter 0’);
end;
Sattler / Saake
Datenbanksysteme
Transaktionen, Integrität und Trigger
Wintersemester 2006/7
8–44
Trigger
Zusammenfassung
Zusicherung von Korrektheit bzw. Integrität der Daten
inhärente Integritätsbedingungen des Relationenmodells
zusätzliche SQL-Integritätsbedingungen: check-Klausel,
assertion-Anweisung
Trigger zur „Implementierung“ von Integritätsbedingungen bzw.
-regeln
Sattler / Saake
158
Datenbanksysteme
Wintersemester 2006/7
8–45
TU Ilmenau/Uni Magdeburg, WS 2006/07
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
1
Sichtenkonzept
2
Änderungen auf Sichten
3
Rechtevergabe
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–1
Sichtenkonzept
Sichten
Sichten: virtuelle Relationen (bzw virtuelle Datenbankobjekte in
anderen Datenmodellen) (englisch view)
Sichten sind externe DB-Schemata folgend der
3-Ebenen-Schemaarchitektur
Sichtdefinition
Relationenschema (implizit oder explizit)
Berechnungsvorschrift für virtuelle Relation, etwa SQL-Anfrage
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–2
Sichtenkonzept
Sichten /2
Vorteile
Vereinfachung von Anfragen für den Benutzer der Datenbank, etwa
indem oft benötigte Teilanfragen als Sicht realisiert werden
Möglichkeit der Strukturierung der Datenbankbeschreibung,
zugeschnitten auf Benutzerklassen
logische Datenunabhängigkeit ermöglicht Stabilität der Schnittstelle
für Anwendungen gegenüber Änderungen der Datenbankstruktur
(entsprechend in umgekehrter Richtung)
Beschränkung von Zugriffen auf eine Datenbank im
Zusammenhang mit der Zugriffskontrolle
Probleme
automatische Anfragetransformation
Durchführung von Änderungen auf Sichten
Sattler / Saake
160
Datenbanksysteme
Wintersemester 2006/7
9–3
TU Ilmenau/Uni Magdeburg, WS 2006/07
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Sichtenkonzept
Drei-Ebenen-Schema-Architektur
Konzeptuelles
Schema
internes
Schema
Datenbanksysteme
Sichten und Zugriffskontrolle
Datendarstellung
Anfragebearbeitung
Sattler / Saake
externes
Schema N
...
8
externes
Schema 1
Wintersemester 2006/7
9–4
Sichtenkonzept
Definition von Sichten in SQL
create view SichtName [ SchemaDeklaration ]
as SQLAnfrage
[ with check option ]
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–5
Sichtenkonzept
Sichten - Beispiele
1
alle Alben aus Genre „Rock“:
create view RockAlben as
select Titel, Name, Jahr
from Album, Musiker
where Album.MNr = Musiker.MNr
and Genre = ’Rock’
2
Musiker mit Alben aus dem aktuellen Jahr
create view NeueMusiker as
select Name, Land from Musiker
where MNr in (select MNr from Album
where Jahr = (select max(Jahr) from Album))
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
9–6
161
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Sichtenkonzept
Problembereiche bei Sichten
Durchführung von Änderungen auf Sichten
automatische Anfragetransformation
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–7
Änderungen auf Sichten
Kriterien für Änderungen auf Sichten
Effektkonformität
Benutzer sieht Effekt als wäre die Änderung auf der Sichtrelation
direkt ausgeführt worden
Minimalität
Basisdatenbank sollte nur minimal geändert werden, um den
erwähnten Effekt zu erhalten
Konsistenzerhaltung
Änderung einer Sicht darf zu keinen Integritätsverletzungen der
Basisdatenbank führen
Respektierung des Datenschutzes
Wird die Sicht aus Datenschutzgründen eingeführt, darf der
bewußt ausgeblendete Teil der Basisdatenbank von Änderungen
der Sicht nicht betroffen werden
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–8
Änderungen auf Sichten
Beispielszenario im Relationenmodell
MGA(Mitarbeiter, Gehalt, Abteilung)
AL(Abteilung, Leiter)
MGA speichert Daten über Zugehörigkeit von Mitarbeitern zu
Abteilungen und deren jeweiliges Gehalt
AL gibt für jede Abteilung den Abteilungsleiter an
Sattler / Saake
162
Datenbanksysteme
Wintersemester 2006/7
9–9
TU Ilmenau/Uni Magdeburg, WS 2006/07
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Änderungen auf Sichten
Projektionssicht
MA := πMitarbeiter,Abteilung (MGA)
In SQL mit create view-Anweisung:
create view MA as
select Mitarbeiter, Abteilung from MGA
Änderungsanweisung für die Sicht MA:
insert into MA values (’Zuse’, ’Info’)
8
Korrespondierende Anweisung auf der Basisrelation MGA:
insert into MGA values (’Zuse’, null, ’Info’)
→ Problem der Konsistenzerhaltung falls Gehalt als not null
deklariert!
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–10
Änderungen auf Sichten
Selektionssichten
MG := σGehalt>20 (πMitarbeiter,Gehalt (MGA))
create view MG as
select Mitarbeiter, Gehalt
from MGA
where Gehalt > 20
Tupelmigration: Ein Tupel MGA( Zuse , 25, Info ), wird aus der
Sicht „‘herausbewegt“’:
update MG set Gehalt = 15
where Mitarbeiter = ’Zuse’
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–11
Wintersemester 2006/7
9–12
Änderungen auf Sichten
Kontrolle der Tupelmigration
create view MG as
select Mitarbeiter, Gehalt
from MGA
where Gehalt > 20
with check option
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
163
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Änderungen auf Sichten
Verbundsichten
MGAL := MGA
AL
In SQL:
create view MGAL as
select Mitarbeiter, Gehalt,
MGA.Abteilung, Leiter
from MGA, AL
where MGA.Abteilung = AL.Abteilung
Änderungsoperationen in der Regel nicht eindeutig übersetzbar:
insert into MGAL
values (’Turing’, 30, ’Info’, ’Zuse’)
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–13
Änderungen auf Sichten
Verbundsichten /2
Änderung wird transformiert zu
insert into MGA values (’Turing’, 30, ’Info’)
plus
1
Einfügeanweisung auf AL:
insert into AL values (’Info’,’Zuse’)
2
oder alternativ:
update AL set Abteilung = ’Info’
where Leiter = ’Zuse’
besser bzgl. Minimalitätsforderung
widerspricht aber Effektkonformität!
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–14
Änderungen auf Sichten
Aggregierungssichten
create view AS (Abteilung, SummeGehalt)
as
select Abteilung, sum(Gehalt)
from MGA
group by Abteilung
Folgende Änderung ist nicht eindeutig umsetzbar:
update AS
set SummeGehalt = SummeGehalt + 1000
where Abteilung = ’Info’
Sattler / Saake
164
Datenbanksysteme
Wintersemester 2006/7
9–15
TU Ilmenau/Uni Magdeburg, WS 2006/07
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Änderungen auf Sichten
1
Verletzung der Schemadefinition (z.B. Einfügen von Nullwerten
bei Projektionssichten)
2
Datenschutz: Seiteneffekte auf nicht-sichtbaren Teil der
Datenbank vermeiden (Tupelmigration, Selektionssichten)
3
nicht immer eindeutige Transformation: Auswahlproblem
4
Aggregierungssichten (u.a.): keine sinnvolle Transformation
möglich
5
elementare Sichtänderung soll genau einer atomaren Änderung
auf Basisrelation entsprechen: 1:1-Beziehung zwischen
Sichttupeln und Tupeln der Basisrelation (kein Herausprojizieren
von Schlüsseln)
Datenbanksysteme
Sattler / Saake
Sichten und Zugriffskontrolle
Wintersemester 2006/7
8
Klassifikation der Problembereiche
9–16
Änderungen auf Sichten
Behandlung von Sichten in SQL
SQL-92-Standard
Integritätsverletzende Sichtänderungen nicht erlaubt
datenschutzverletzende Sichtänderungen: Benutzerkontrolle (with
check option)
Sichten mit nicht-eindeutiger Transformation: Sicht nicht änderbar
(SQL-92 restriktiver als notwendig)
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–17
Änderungen auf Sichten
Einschränkungen für Sichtänderungen
änderbar nur Selektions- und Projektionssichten (Verbund und
Mengenoperationen nicht erlaubt)
1:1-Zuordnung von Sichttupeln zu Basistupeln: kein distinct in
Projektionssichten
Arithmetik und Aggregatfunktionen im select-Teil sind verboten
genau eine Referenz auf einen Relationsnamen im from-Teil
erlaubt (auch kein Selbstverbund)
keine Unteranfragen mit „Selbstbezug“ im where-Teil erlaubt
(Relationsname im obersten SFW-Block nicht in from-Teilen von
Unteranfragen verwenden)
group by und having verboten
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
9–18
165
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Änderungen auf Sichten
Auswertung von Anfragen an Sichten
select: Sichtattribute evtl. umbenennen bzw. durch
Berechnungsterm ersetzen
from: Namen der Originalrelationen
konjunktive Verknüpfung der where-Klauseln von Sichtdefinition
und Anfrage (evtl. Umbenennungen)
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–19
Änderungen auf Sichten
Probleme bei Aggregierungssichten
create view DS (Abteilung, GehaltsSumme) as
select Abteilung, sum(Gehalt)
from MGA
group by Abteilung
Anfrage: Abteilungen mit hohen Gehaltsausgaben
select Abteilung
from DS
where GehaltsSumme > 500
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–20
Änderungen auf Sichten
Probleme bei Aggregierungssichten /2
Nach syntaktischer Transformation:
select Abteilung
from MGA
where sum(Gehalt) > 500
group by Abteilung
keine syntaktische korrekte SQL-Anfrage – Korrekt wäre:
select Abteilung
from MGA
group by Abteilung
having sum(Gehalt) > 500
Sattler / Saake
166
Datenbanksysteme
Wintersemester 2006/7
9–21
TU Ilmenau/Uni Magdeburg, WS 2006/07
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Änderungen auf Sichten
Probleme bei Aggregierungssichten /3
Anfrage
select avg (GehaltsSumme)
from DS
müsste wie folgt transformiert werden:
select avg(sum (Gehalt))
from MGA
group by Abteilung
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
8
Aber: Geschachtelte Aggregatfunktionen sind in SQL nicht
erlaubt!
9–22
Rechtevergabe
Rechtevergabe in Datenbanksystemen
Zugriffsrechte
(AutorisierungsID, DB-Ausschnitt, Operation)
AutorisierungsID ist interne Kennung eines „Datenbankbenutzers“
Datenbank-Ausschnitte: Relationen und Sichten
DB-Operationen: Lesens, Einfügen, Ändern, Löschen
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–23
Wintersemester 2006/7
9–24
Rechtevergabe
Rechtevergabe in SQL
grant <Rechte>
on <Tabelle>
to <BenutzerListe>
[with grant option]
Sattler / Saake
Datenbanksysteme
TU Ilmenau/Uni Magdeburg, WS 2006/07
167
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Rechtevergabe
Rechtevergabe in SQL /2
Erläuterungen:
In <Rechte>-Liste: all bzw. Langform all privileges oder
Liste aus select, insert, update, delete
Hinter on: Relationen- oder Sichtname
Hinter to: Autorisierungsidentifikatoren (auch public, group)
spezielles Recht: Recht auf die Weitergabe von Rechten (with
grant option)
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–25
Rechtevergabe
Autorisierung für public
create view MeineAufträge as
select *
from AUFTRAG
where KName = user;
grant select, insert
on MeineAufträge
to public;
„Jeder Benutzer kann seine Aufträge sehen und neue
Aufträge einfügen (aber nicht löschen!).“
Sattler / Saake
Datenbanksysteme
Sichten und Zugriffskontrolle
Wintersemester 2006/7
9–26
Rechtevergabe
Zurücknahme von Rechten
revoke <Rechte>
on <Tabelle>
from <BenutzerListe>
[restrict | cascade ]
restrict: Falls Recht bereits an Dritte weitergegeben: Abbruch
von revoke
cascade: Rücknahme des Rechts mittels revoke an alle
Benutzer propagiert, die es von diesem Benutzer mit grant
erhalten haben
Sattler / Saake
168
Datenbanksysteme
Wintersemester 2006/7
9–27
TU Ilmenau/Uni Magdeburg, WS 2006/07
8 Sichten und Zugriffskontrolle
Sichten und Zugriffskontrolle
Rechtevergabe
Zusammenfassung
Sichten zur Strukturierung von Datenbanken
Probleme bei Änderungen über Sichten
8
Rechtesystem in SQL-DBS
Sattler / Saake
Datenbanksysteme
Wintersemester 2006/7
TU Ilmenau/Uni Magdeburg, WS 2006/07
9–28
169
Document
Kategorie
Kunst und Fotos
Seitenansichten
14
Dateigröße
2 312 KB
Tags
1/--Seiten
melden