VL Datenbanksysteme - Wintersemester 2019/2020 · 2019-09-22 · Ebenen-ArchitekturamBeispiel • KonzeptuelleSicht:DarstellunginTabellen(Relationen) Fahrer FahrerID Name Telefon

VL DatenbanksystemeWintersemester 2019/2020

Prof. Dr.-Ing. Kai-Uwe Sattler1 Prof. Dr. Gunter Saake2

Letzte Änderung: Okt. 20181TU IlmenauFG Datenbanken & Informationssysteme

2Universität MagdeburgInstitut für Technische & Betriebliche Informationssysteme

Sattler/Saake | VL Datenbanksysteme | 22. September 2019 0–1

Zugrundeliegendes Lehrbuch

G. Saake; K. Sattler; A. Heuer:Datenbanken — Konzepte undSprachen

6. Auflage, mitp-Verlag, 2018


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte

2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen

3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell

4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf

5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie

6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL

7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül

8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger

9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle

10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken

11. Anwendungsprogrammierung mit Datenbanken


Überblick

1. Was sind Datenbanken – Grundlegende Konzepte2. Relationale Datenbanken – Daten als Tabellen3. Datenbankentwurf im ER-Modell4. Relationaler DB-Entwurf5. Relationale Entwurfstheorie6. Die Datenbanksprache SQL7. Grundlagen von Anfragen: Algebra & Kalkül8. Transaktionen, Integrität und Trigger9. Sichten und Zugriffskontrolle10. NoSQL-Datenbanken11. Anwendungsprogrammierung mit Datenbanken


Weitere Literatur

G. Vossen.Datenbankmodelle, Datenbanksprachen undDatenbankmanagement-Systeme.5. Auflage, Oldenbourg-Verlag, München, 2008

R. Elmasri, S.B. Navathe.Grundlagen von Datenbanksystemen.3. Auflage, Pearson Studium, 2002

A. Kemper, A. Eickler.Datenbanksysteme. Eine Einführung.7. Auflage, Oldenbourg-Verlag, München, 2009

A. Heuer, G. Saake, K. Sattler.Datenbanken kompakt2. Aufl., mitp-Verlag, Bonn, August 2003

G. Lausen.Datenbanken – Grundlagen und XML-TechnologienSpektrum Akademischer Verlag, 2005


Teil I

Was sind Datenbanken?


1. Überblick & Motivation

2. Architekturen

3. Einsatzgebiete

4. Historisches




2. Architekturen

3. Einsatzgebiete

4. Historisches




2. Architekturen

3. Einsatzgebiete

4. Historisches




2. Architekturen

3. Einsatzgebiete

4. Historisches


Lernziele für heute …

• Motivation für den Einsatz vonDatenbanksystemen

• Kenntnis grundlegender Architekturen



• Motivation für den Einsatz vonDatenbanksystemen

• Kenntnis grundlegender Architekturen


Überblick & Motivation


• Daten = logisch gruppierte Informationseinheiten• Bank = …

• Die Sicherheit vor Verlusten ist eineHauptmotivation, etwas „auf die Bankzu bringen“.

• Eine Bank bietet Dienstleistungen fürmehrere Kunden an, um effizientarbeiten zu können.

• Eine Datenbank hat die (langfristige)Aufbewahrung von Daten als Aufgabe.


Anwendungsbeispiele


Bedeutung der Datenverwaltung

• Daten als das Öl des 21. Jahrhunderts (Toonders @Wired2014)

• Ash Ashutosh (CEO Actifio):

• das größte Hotelunternehmen (AirBnB) hat keine Hotelsbzw. Zimmer

• das größte Taxiunternehmen (Uber) hat keine eigenenTaxis!




• Ash Ashutosh (CEO Actifio):

• das größte Hotelunternehmen (AirBnB) hat keine Hotelsbzw. Zimmer





• Ash Ashutosh (CEO Actifio):• das größte Hotelunternehmen (AirBnB) hat keine Hotelsbzw. Zimmer





• Ash Ashutosh (CEO Actifio):• das größte Hotelunternehmen (AirBnB) hat keine Hotelsbzw. Zimmer



Wie verwaltet man Datenbanken?

Ohne Datenbanken

• jedes Anwendungssystem verwaltet seine eigenen Daten• Daten sind mehrfach gespeichert redundant• Probleme

• Verschwendung von Speicherplatz• „Vergessen“ von Änderungen• keine zentrale, „genormte“ Datenhaltung


Probleme der Datenredundanz

• Andere Softwaresysteme können große Mengen von Datennicht effizient verarbeiten

• Mehrere Benutzer oder Anwendungen können nichtparallel auf den gleichen Daten arbeiten, ohne sich zustören

• Anwendungsprogrammierer / Benutzer könnenAnwendungen 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


Idee: Datenintegration durch Datenbanksysteme

Datenbank

...

DBMS

Anwendung Anwendung

strukturierter, von DBMSverwalteter Datenbestand

Datenbankmanagementsystem =Software zur Verwaltung von Datenbanken

DBS = Datenbanksystem


Motivation

• Datenbank-systeme sindHerzstück heutigerIT-Infrastrukturen

• …allgegenwärtig

• Datenbank-spezialisten sindgefragt


Motivation


Fragestellungen

1. Wie organisiert (modelliert und nutzt) man Daten?2. Wie werden Daten dauerhaft verlässlich gespeichert?3. Wie kann man riesige Datenmengen (≥ Terabytes) effizientverarbeiten?

4. Wie können viele Nutzer (≥ 10.000) gleichzeitig mit denDaten arbeiten?


Architekturen

Prinzipien: Die neun Codd’schen Regeln

1. Integration: einheitliche, nichtredundanteDatenverwaltung

2. Operationen: Speichern, Suchen, Ändern3. Katalog: Zugriffe auf Datenbankbeschreibungen im DataDictionary

4. Benutzersichten5. Integritätssicherung: Korrektheit des Datenbankinhalts6. Datenschutz: Ausschluss unauthorisierter Zugriffe7. Transaktionen: mehrere DB-Operationen alsFunktionseinheit

8. Synchronisation: parallele Transaktionen koordinieren9. Datensicherung: Wiederherstellung von Daten nachSystemfehlern


Datenunabhängigkeit und Schemata

• Basierend auf DBMS-Grobarchitektur• Entkopplung von Benutzer- und Implementierungssicht• Ziele u.a.:

• Trennung von Modellierungssicht und internerSpeicherung

• Portierbarkeit• Tuning vereinfachen• standardisierte Schnittstellen


Schemaarchitektur

• Zusammenhang zwischen• Konzeptuellem Schema (Ergebnis der Datendefinition)• Internem Schema (Festlegung der Dateiorganisationen undZugriffspfade)

• Externen Schemata (Ergebnis der Sichtdefinition)• Anwendungsprogrammen (Ergebnis derAnwendungsprogrammierung)


Schemaarchitektur /2

• Trennung Schema — Instanz• Schema (Metadaten, Datenbeschreibungen)• Instanz (Anwenderdaten, Datenbankzustand oder-ausprägung)

• Datenbankschema besteht aus• internem, konzeptuellem, externen Schemata und denAnwendungsprogrammen

• im konzeptuellen Schema etwa:• Strukturbeschreibungen• Integritätsbedingungen• Autorisierungsregeln (pro Benutzer für erlaubteDB-Zugriffe)


Schemaarchitektur /3

Konzeptuelles Schema

externesSchema 1

externesSchema N

internesSchema

...

Anfragebearbeitung

Datendarstellung


Datenunabhängigkeit /2

• Stabilität der Benutzerschnittstelle gegen Änderungen• physisch: Änderungen der Dateiorganisationen undZugriffspfade haben keinen Einfluss auf das konzeptuelleSchema

• logisch: Änderungen am konzeptuellen und gewissenexternen Schemata haben keine Auswirkungen auf andereexterne Schemata und Anwendungsprogramme


Datenunabhängigkeit /3

• mögliche Auswirkungen von Änderungen amkonzeptuellen Schema:

• eventuell externe Schemata betroffen (Ändern vonAttributen)

• eventuell Anwendungsprogramme betroffen(Rekompilieren der Anwendungsprogramme, eventuellÄnderungen nötig)

• nötige Änderungen werden jedoch vom DBMS erkannt undüberwacht


Anwendungsbeispiel: Mitfahrgelegenheit

• Angebote vonMitfahrgelegenheiten

• Wann? Von wo? Wohin?Wer? Plätze?

• Kontaktdaten• Reservierungsmöglichkeiten

CC-BY-2.0: Luo Shaoyang


Ebenen-Architektur am Beispiel

• Konzeptuelle Sicht: Darstellung in Tabellen (Relationen)

Fahrer FahrerID Name Telefon103 Lilo Pause 01234104 Just Vorfan 01246105 Heiko Heizer 01756

Mitfahrangebot ANr Abfahrt Ankunft Zeit FahrerID1014 Ilmenau Erfurt Freitag 10:00 1031015 Magdeburg Halle Freitag 15:00 1041016 Magdeburg Leipzig Freitag 15:00 1041021 Magdeburg Ilmenau Freitag 14:00 1051025 Ilmenau Jena Freitag 10:00 103


Ebenen-Architektur am Beispiel /2

• Externe Sicht: Daten in einer flachen Relation

ANr Abfahrt Ankunft Zeit Fahrer1014 Ilmenau Erfurt Freitag 10:00 Lilo Pause1015 Magdeburg Halle Freitag 15:00 Just Vorfan1016 Magdeburg Leipzig Freitag 15:00 Just Vorfan1021 Magdeburg Ilmenau Freitag 14:00 Heiko Heizer1025 Ilmenau Jena Freitag 10:00 Lilo Pause



• Externe Sicht: Daten in einer hierarchisch aufgebautenRelation

Fahrer MitfahrangebotAbfahrt Ankunft Zeit

Lilo Pause Ilmenau Erfurt Freitag 10:00Jena Freitag 10:00

Just Vorfan Magdeburg Halle Freitag 15:00Leipzig Freitag 15:00

Heiko Heizer Magdeburg Ilmenau Freitag 14:00



• Interne Darstellung

1000 1500 2000

1014 Ilmenau Erfurt

1015 Magdeburg Halle

Freitag 10:00 ….Freitag 15:00 …

Überlauf-bereich fürDatensätze

teilweisesSpeichern der

Datensätzeim Baum

BaumzugriffüberID


System-Architekturen

• Beschreibung der Komponenten eines Datenbanksystems• Standardisierung der Schnittstellen zwischenKomponenten

• Architekturvorschläge• ANSI-SPARC-Architektur Drei-Ebenen-Architektur

• Fünf-Schichten-Architektur beschreibt Transformationskomponenten im DetailVorlesung „Datenbank-Implementierungstechniken“


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


ANSI-SPARC-Architektur /2

Data Dictionary

Optimierer Auswertung PlattenzugriffAnfragen

Updates

SichtdefinitionDatendefinition

Datei-organisation

DB-Operationen

Einbettung

Masken

P1

Pn

...

Externe Ebene Konzeptuelle Ebene Interne Ebene


Klassifizierung der Komponenten

• Definitionskomponenten: Datendefinition,Dateiorganisation, Sichtdefinition

• Programmierkomponenten: DB-Programmierung miteingebetteten DB-Operationen

• Benutzerkomponenten: Anwendungsprogramme, Anfrageund Update interaktiv

• Transformationskomponenten: Optimierer, Auswertung,Plattenzugriffssteuerung

• Data Dictionary (Datenwörterbuch): Aufnahme der Datenaus Definitionskomponenten, Versorgung der anderenKomponenten


Fünf-Schichten-Architektur: Verfeinerung der Transformation

Datensystem

Zugriffssystem

Speichersystem

Pufferverwaltung

Betriebssystem

MengenorientierteSchnittstelle

SatzorientierteSchnittstelle

InterneSatzschnittstelle

Systempuffer-schnittstelle

Datei-schnittstelle

Geräteschnittstelle

Externspeicher

ÜbersetzungZugriffspfadwahl

Logische Zugriffspfade, Schemakatalog, Sortierung,Transaktionsverwaltung

Speicherungsstrukturen, Zugriffs-pfadverwaltung, Sperr-verwaltung, Logging, Recovery

Systempufferverwaltung, Seitenersetzung, Seitenzuordnung

Externspeicherverwaltung,Speicherzuordnung


Anwendungsarchitekturen

• Architektur von Datenbankanwendungen typischerweiseauf Basis des Client-Server-Modells: Server ≡Datenbanksystem

1. Anforderung

3. Antwort

2. Bearbeitung

Client(Dienstnehmer)

Server(Diensterbringer)


Anwendungsarchitekturen /2

• Aufteilung der Funktionalitäten einer Anwendung• Präsentation und Benutzerinteraktion• Anwendungslogik („Business“-Logik)• Datenmanagementfunktionen (Speichern, Anfragen, …).

Benutzerschnittstelle

Anwendungslogik

DB-Schnittstelle

DB-Server

Client

Zwei-Schichten-Architektur

Benutzerschnittstelle

Anwendungslogik

DB-Schnittstelle

Applikations-server

DB-Server

Client

Drei-Schichten-ArchitekturSattler/Saake | VL Datenbanksysteme | 22. September 2019 1–30

Einsatzgebiete

Einige konkrete Systeme

• (Objekt-)Relationale DBMS• Oracle12c, IBM DB2 V.11, Microsoft SQL Server 2016, SAPHANA

• MySQL (www.mysql.org), PostgreSQL(www.postgresql.org)

• Pseudo-DBMS• MS Access

• NoSQL-Systeme• Graph-Datenbanksysteme (InfiniteGraph, neo4j),Dokument-Datenbanken (MongoDB), Key-Value-Stores, ....


www.mysql.org

www.postgresql.org

Einsatzgebiete

• Klassische Einsatzgebiete:• viele Objekte (15000 Bücher, 300 Benutzer, 100Ausleihvorgänge pro Woche, …)

• wenige Objekttypen (BUCH, BENUTZER, AUSLEIHUNG)• etwa Buchhaltungssysteme, Auftragserfassungssysteme,Bibliothekssysteme, …

• Aktuelle Anwendungen:• E-Commerce, entscheidungsunterstützende Systeme (DataWarehouses, OLAP), NASA’s Earth Observation System(Petabyte-Datenbanken), Data Mining


Datenbankgrößen

eBay Data Warehouse 10 PB (≈ 10 · 1015 Bytes)Teradata DBMS, 72 Knoten, 10.000 Nutzer,mehrere Millionen Anfragen/Tag

WalMart Data Warehouse 2,5 PBTeradata DBMS, NCR MPP-Hardware;Produktinfos (Verkäufe etc.) von 2.900 Märkten;50.000 Anfragen/Woche

Facebook 400 TBx.000 MySQL-ServerHadoop/Hive, 610 Knoten, 15 TB/Tag

US Library of Congress 10-20 TBnicht digitalisiert


Historisches

Entwicklungslinien: 60er Jahre

• Anfang 60er Jahre: elementare Dateien,anwendungsspezifische Datenorganisation(geräteabhängig, redundant, inkonsistent)

• Ende 60er Jahre: Dateiverwaltungssysteme (SAM, ISAM) mitDienstprogrammen (Sortieren) (geräteunabhängig, aberredundant und inkonsistent)

• DBS basierend auf hierarchischem Modell,Netzwerkmodell

• Zeigerstrukturen zwischen Daten• Schwache Trennung interne / konzeptuelle Ebene• Navigierende DML• Trennung DML / Programmiersprache


Entwicklungslinien: 70er und 80er Jahre

• 70er Jahre: Datenbanksysteme (Geräte- undDatenunabhängigkeit, redundanzfrei, konsistent)

• Relationale Datenbanksysteme• Daten in Tabellenstrukturen• 3-Ebenen-Konzept• Deklarative DML• Trennung DML / Programmiersprache


Historie von RDBMS

• 1970: Ted Codd (IBM)→ Relationenmodell alskonzeptionelle 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


Entwicklungslinien: (80er und) 90er Jahre

• Wissensbanksysteme• Daten in Tabellenstrukturen• Stark deklarative DML, integrierteDatenbankprogrammiersprache

• Objektorientierte Datenbanksysteme• Daten in komplexeren Objektstrukturen (Trennung Objektund seine Daten)

• Deklarative oder navigierende DML• Oft integrierte Datenbankprogrammiersprache• Oft keine vollständige Ebenentrennung


Entwicklungslinien: heute

• Neue Hardwarearchitekturen• Multicore-Prozessoren, Hauptspeicher im TB-Bereich:In-Memory-Datenbanksysteme (z.B. SAP HANA)

• Unterstützung für spezielle Anwendungen• Cloud-Datenbanken: Hosting von Datenbanken, SkalierbareDatenmanagementlösungen (Amazon RDS, Microsoft Azure)

• Datenstromverarbeitung: Online-Verarbeitung vonLive-Daten, z.B. Börseninfos, Sensordaten, RFID-Daten,…(StreamBase, MS StreamInsight, IBM Infosphere Streams)

• Big Data: Umgang mit Datenmengen im PB-Bereich durchhochskalierbare, parallele Verarbeitung, Datenanalyse(Hadoop, Hive, Google Spanner & F1, …)


Entwicklungslinien: NoSQL

• NoSQL-Datenbanken („Not only SQL“):• nicht-relationale Datenbanken, flexibles Schema(dokumentenzentriert)

• „leichtgewichtig“ durch Weglassen vonSQL-Funktionalitäten wie Transaktionen, mächtigedeklarative Anfragesprachen mit Verbunden etc.

• Beispiele: CouchDB, MongoDB, Cassandra, …


Trends

• Nutzergenerierte Inhalte, z.B. Google:• Verarbeitung von 20 PB täglich• 15h Video-Upload auf YouTube in jeder Minute• Lesen von 20 PB würde 12 Jahre benötigen bei 50MB/s-Festplatte

• Linked Data und Data Web• Bereitstellung, Austausch und Verknüpfung vonstrukturierten Daten im Web

• ermöglicht Abfrage (mit Anfragesprachen wie SPARQL) undWeiterverarbeitung

• Beispiele: DBpedia, GeoNames


Zusammenfassung

• Motivation für Einsatz von Datenbanksystemen• Codd’sche Regeln• 3-Ebenen-Schemaarchitektur & Datenunabhängigkeit• Einsatzgebiete


Kontrollfragen

• Welchen Vorteil bieten Datenbanksystemegegenüber einer anwendungsspezifischenSpeicherung von Daten?

• Was versteht man unterDatenunabhängigkeit und wie wird sieerreicht?

• In welchen Bereichen kommenDatenbanksysteme zum Einsatz?


Kontrollfragen





Kontrollfragen





Teil II

Relationale Datenbanken – Daten alsTabellen

Relationale Datenbanken – Daten als Tabellen

1. Relationen für tabellarische Daten

2. SQL-Datendefinition

3. Grundoperationen: Die Relationenalgebra

4. SQL als Anfragesprache

5. Änderungsoperationen in SQL

6. Anwendungsbeispiel











































• Grundverständnis zur Strukturrelationaler Datenbanken

• Kenntnis der Basisoperationenrelationaler Anfragesprachen

• elementare Fähigkeiten in der Anwendungvon SQL












Relationen für tabellarische Daten

Relationenmodell

Konzeptuell: Datenbank = Menge von Tabellen (= Relationen)

WEINE WeinID Name Farbe Jahrgang Weingut1042 La Rose Grand Cru Rot 1998 Château ...2168 Creek Shiraz Rot 2003 Creek3456 Zinfandel Rot 2004 Helena2171 Pinot Noir Rot 2001 Creek3478 Pinot Noir Rot 1999 Helena4711 Riesling Reserve Weiß 1999 Müller4961 Chardonnay Weiß 2002 Bighorn

ERZEUGER Weingut Anbaugebiet RegionCreek Barossa Valley SüdaustralienHelena Napa Valley KalifornienChâteau La Rose Saint-Emilion BordeauxChâteau La Pointe Pomerol BordeauxMüller Rheingau HessenBighorn Napa Valley Kalifornien


Relationenmodell

Konzeptuell: Datenbank = Menge von Tabellen (= Relationen)WEINE WeinID Name Farbe Jahrgang Weingut

1042 La Rose Grand Cru Rot 1998 Château ...2168 Creek Shiraz Rot 2003 Creek3456 Zinfandel Rot 2004 Helena2171 Pinot Noir Rot 2001 Creek3478 Pinot Noir Rot 1999 Helena4711 Riesling Reserve Weiß 1999 Müller4961 Chardonnay Weiß 2002 Bighorn



Relationenmodell

Konzeptuell: Datenbank = Menge von Tabellen (= Relationen)WEINE WeinID Name Farbe Jahrgang Weingut

1042 La Rose Grand Cru Rot 1998 Château ...2168 Creek Shiraz Rot 2003 Creek3456 Zinfandel Rot 2004 Helena2171 Pinot Noir Rot 2001 Creek3478 Pinot Noir Rot 1999 Helena4711 Riesling Reserve Weiß 1999 Müller4961 Chardonnay Weiß 2002 Bighorn



Darstellung von Relationen und Begriffe

• „Tabellenkopf“: Relationenschema

• Eine Zeile der Tabelle: Tupel; Menge aller Einträge: Relation• Eine Spaltenüberschrift: Attribut• Ein Eintrag: Attributwert

A1 ... An

...

...

...

R

Relationenname Attribut

Tupel Relation

Relationenschema



• „Tabellenkopf“: Relationenschema• Eine Zeile der Tabelle: Tupel; Menge aller Einträge: Relation

• Eine Spaltenüberschrift: Attribut• Ein Eintrag: Attributwert

A1 ... An

...

...

...

R


Tupel Relation

Relationenschema



• „Tabellenkopf“: Relationenschema• Eine Zeile der Tabelle: Tupel; Menge aller Einträge: Relation• Eine Spaltenüberschrift: Attribut

• Ein Eintrag: Attributwert

A1 ... An

...

...

...

R


Tupel Relation

Relationenschema



• „Tabellenkopf“: Relationenschema• Eine Zeile der Tabelle: Tupel; Menge aller Einträge: Relation• Eine Spaltenüberschrift: Attribut• Ein Eintrag: Attributwert

A1 ... An

...

...

...

R


Tupel Relation

Relationenschema




A1 ... An

...

...

...

R


Tupel Relation

Relationenschema




A1 ... An

...

...

...

R


Tupel Relation

Relationenschema


Integritätsbedingungen: Schlüssel

• Attribute einer Spalte identifizieren eindeutiggespeicherte Tupel: Schlüsseleigenschaft

• etwa Weingut für Tabelle ERZEUGER


• auch Attributkombinationen können Schlüssel sein!• Schlüssel können durch Unterstreichen gekennzeichnetwerden
















• etwa Weingut für Tabelle ERZEUGERERZEUGER Weingut Anbaugebiet Region

Creek Barossa Valley SüdaustralienHelena Napa Valley KalifornienChâteau La Rose Saint-Emilion BordeauxChâteau La Pointe Pomerol BordeauxMüller Rheingau HessenBighorn Napa Valley Kalifornien

• auch Attributkombinationen können Schlüssel sein!

• Schlüssel können durch Unterstreichen gekennzeichnetwerden




• etwa Weingut für Tabelle ERZEUGERERZEUGER Weingut Anbaugebiet Region

Creek Barossa Valley SüdaustralienHelena Napa Valley KalifornienChâteau La Rose Saint-Emilion BordeauxChâteau La Pointe Pomerol BordeauxMüller Rheingau HessenBighorn Napa Valley Kalifornien



Integritätsbedingungen: Fremdschlüssel

• Schlüssel einer Tabelle können in einer anderen (oderderselben!) Tabelle als eindeutige Verweise genutztwerden: Fremdschlüssel, referenzielle Integrität

• etwa Weingut als Verweise auf ERZEUGER• ein Fremdschlüssel ist ein Schlüssel in einer „fremden“Tabelle




• etwa Weingut als Verweise auf ERZEUGER

• ein Fremdschlüssel ist ein Schlüssel in einer „fremden“Tabelle




• etwa Weingut als Verweise auf ERZEUGER• ein Fremdschlüssel ist ein Schlüssel in einer „fremden“Tabelle


Fremdschlüssel /2

WEINE WeinID Name Farbe Jahrgang Weingut→ ERZEUGER1042 La Rose ... Rot 1998 Château La Rose2168 Creek Shiraz Rot 2003 Creek3456 Zinfandel Rot 2004 Helena2171 Pinot Noir Rot 2001 Creek3478 Pinot Noir Rot 1999 Helena4711 Riesling ... Weiß 1999 Müller4961 Chardonnay Weiß 2002 Bighorn



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

• die Vorbereitung einer „leeren Basisrelation“ in derDatenbank


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

• die Vorbereitung einer „leeren Basisrelation“ in derDatenbank


Löschen einer Tabelle: drop table

• komplettes Löschen einer Tabelle (Inhalt und Eintrag imData Dictionary)

drop table basisrelationenname


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

• 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ürStrings variabler Länge bis zur Maximallänge n,

• bit(n) oder bit varying(n) analog für Bitfolgen, und• date, time bzw. datetime für Datums-, Zeit- undkombinierte Datums-Zeit-Angaben


Beispiel für create table

create table WEINE (WeinID int primary key,Name varchar(20) not null,Farbe varchar(10),Jahrgang int,Weingut varchar(20))

• primary key kennzeichnet Spalte als Schlüsselattribut


create table mit Fremdschlüssel

create table WEINE (WeinID int,Name varchar(20) not null,Farbe varchar(10),Jahrgang int,Weingut varchar(20),primary key(WeinID),foreign key(Weingut)

references ERZEUGER(Weingut))

• foreign key kennzeichnet Spalte als Fremdschlüssel


Nullwerte

• not null schließt in bestimmten Spalten Nullwerte alsAttributwerte aus

• Kennzeichnung von Nullwerte in SQL durch null; hier ⊥• null repräsentiert die Bedeutung „Wert unbekannt“,„Wert nicht anwendbar“ oder „Wert existiert nicht“, gehörtaber zu keinem Wertebereich

• null kann in allen Spalten auftauchen, außer inSchlüsselattributen und den mit not nullgekennzeichneten


Nullwerte


• Kennzeichnung von Nullwerte in SQL durch null; hier ⊥

• null repräsentiert die Bedeutung „Wert unbekannt“,„Wert nicht anwendbar“ oder „Wert existiert nicht“, gehörtaber zu keinem Wertebereich



Nullwerte





Nullwerte





Grundoperationen: DieRelationenalgebra

Anfrageoperationen auf Tabellen

• Basisoperationen auf Tabellen, die die Berechnung vonneuen Ergebnistabellen aus gespeichertenDatenbanktabellen erlauben

• Operationen werden zur sogenannten Relationenalgebrazusammengefasst

• Mathematik: Algebra ist definiert durch Wertebereichsowie darauf definierten Operationen→ für Datenbankanfragen entsprechen die Inhalte derDatenbank den Werten, Operationen sind dagegenFunktionen zum Berechnen der Anfrageergebnisse

• Anfrageoperationen sind beliebig kombinierbar undbilden eine Algebra zum „Rechnen mit Tabellen“ – dieRelationenalgebra




















Relationenalgebra: Übersicht

a1 b2

a2 b2

b2 c3

b3 c4

a2 b3 b4 c5

a1 b2

a2 b2

a2 b3

c3

c3

c4

Verbund

Selektion Projektion


Selektion σ

• Selektion: Auswahl von Zeilen einer Tabelle anhand einesSelektionsprädikats

σJahrgang>2000(WEINE)

WeinID Name Farbe Jahrgang Weingut2168 Creek Shiraz Rot 2003 Creek3456 Zinfandel Rot 2004 Helena2171 Pinot Noir Rot 2001 Creek4961 Chardonnay Weiß 2002 Bighorn


Selektion σ

• Selektion: Auswahl von Zeilen einer Tabelle anhand einesSelektionsprädikats




Projektion π

• Projektion: Auswahl von Spalten durch Angabe einerAttributliste

πRegion(ERZEUGER)

RegionSüdaustralienKalifornienBordeauxHessen

• Die Projektion entfernt doppelte Tupel.


Projektion π

• Projektion: Auswahl von Spalten durch Angabe einerAttributliste

πRegion(ERZEUGER)


• Die Projektion entfernt doppelte Tupel.


Natürlicher Verbund ⋊⋉

• Verbund (engl. join): verknüpft Tabellen übergleichbenannte Spalten, indem er jeweils zwei Tupelverschmilzt, falls sie dort gleiche Werte aufweisen


Natürlicher Verbund: Beispiel

WEINE ⋊⋉ ERZEUGER

WeinID Name . . . Weingut Anbaugebiet Region1042 La Rose Grand Cru . . . Ch. La Rose Saint-Emilion Bordeaux2168 Creek Shiraz . . . Creek Barossa Valley Südaustralien3456 Zinfandel . . . Helena Napa Valley Kalifornien2171 Pinot Noir . . . Creek Barossa Valley Südaustralien3478 Pinot Noir . . . Helena Napa Valley Kalifornien4711 Riesling Reserve . . . Müller Rheingau Hessen4961 Chardonnay . . . Bighorn Napa Valley Kalifornien

• Das Weingut „Château La Pointe“ ist im Ergebnisverschwunden Tupel, die keinen Partner finden(dangling tuples), werden eliminiert


Natürlicher Verbund: Beispiel



• Das Weingut „Château La Pointe“ ist im Ergebnisverschwunden Tupel, die keinen Partner finden(dangling tuples), werden eliminiert


Kombination von Operationen

πName,Farbe,Weingut(σJahrgang>2000(WEINE) ⋊⋉σRegion=’Kalifornien’(ERZEUGER))

ergibt

Name Farbe WeingutZinfandel Rot HelenaChardonnay Weiß Bighorn


Kombination von Operationen


ergibt

Name Farbe WeingutZinfandel Rot HelenaChardonnay Weiß Bighorn


Umbenennung β

• Anpassung von Attributnamen mittels Umbenennung:

WEINLISTE NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirRiesling Reserve

EMPFEHLUNG WeinLa Rose Grand CruRiesling ReserveMerlot SelectionSauvignon Blanc

• Angleichen durch:βName←Wein (EMPFEHLUNG)


Umbenennung β

• Anpassung von Attributnamen mittels Umbenennung:WEINLISTE Name

La Rose Grand CruCreek ShirazZinfandelPinot NoirRiesling Reserve

EMPFEHLUNG WeinLa Rose Grand CruRiesling ReserveMerlot SelectionSauvignon Blanc

• Angleichen durch:βName←Wein (EMPFEHLUNG)


Mengenoperationen

• Vereinigung r1 ∪ r2 von zwei Relationen r1 und r2:Gesamtheit der beiden Tupelmengen

• Attributmengen beider Relationen müssen identisch sein

WEINLISTE ∪ βName←Wein(EMPFEHLUNG)

NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirRiesling ReserveMerlot SelectionSauvignon Blanc


Mengenoperationen

• Vereinigung r1 ∪ r2 von zwei Relationen r1 und r2:Gesamtheit der beiden Tupelmengen

• Attributmengen beider Relationen müssen identisch sein

WEINLISTE ∪ βName←Wein(EMPFEHLUNG)

NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirRiesling ReserveMerlot SelectionSauvignon Blanc


Mengenoperationen /2

• Differenz r1 − r2 eliminiert die Tupel aus der erstenRelation, die auch in der zweiten Relation vorkommen

WEINLISTE− βName←Wein(EMPFEHLUNG)ergibt:

NameCreek ShirazZinfandelPinot Noir



• Differenz r1 − r2 eliminiert die Tupel aus der erstenRelation, die auch in der zweiten Relation vorkommen

WEINLISTE− βName←Wein(EMPFEHLUNG)ergibt:

NameCreek ShirazZinfandelPinot Noir



• Durchschnitt r1 ∩ r2: ergibt die Tupel, die in beidenRelationen gemeinsam vorkommen

WEINLISTE ∩ βName←Wein(EMPFEHLUNG)liefert:

NameLa Rose Grand CruRiesling Reserve



• Durchschnitt r1 ∩ r2: ergibt die Tupel, die in beidenRelationen gemeinsam vorkommen

WEINLISTE ∩ βName←Wein(EMPFEHLUNG)liefert:

NameLa Rose Grand CruRiesling Reserve


SQL als Anfragesprache

SQL-Anfrage als Standardsprache

• Anfrage an eine einzelne Tabelle

select Name, Farbefrom WEINEwhere Jahrgang = 2002

• SQL hat Multimengensemantik — Duplikate in Tabellenwerden in SQL nicht automatisch unterdrückt!

• Mengensemantik durch distinct

select distinct Name from WEINE























Verknüpfung von Tabellen

• Kreuzprodukt als Basisverknüpfung

select *from WEINE, ERZEUGER

• Verbund durch Operator natural join

select *from WEINE natural join ERZEUGER




















Verknüpfung von Tabellen /2

• Verbund alternativ durch Angabe einerVerbundbedingung!

select *from WEINE, ERZEUGERwhere WEINE.Weingut = ERZEUGER.Weingut


Verknüpfung von Tabellen /2

• Verbund alternativ durch Angabe einerVerbundbedingung!



Kombination von Bedingungen

• Ausdruck in Relationenalgebra


• Anfrage in SQL

select Name, Farbe, WEINE.Weingutfrom WEINE, ERZEUGERwhere Jahrgang > 2000 and

Region = 'Kalifornien' andWEINE.Weingut = ERZEUGER.Weingut





• Anfrage in SQL







• Anfrage in SQL




Mengenoperationen in SQL

• Vereinigung in SQL explizit mit union• Differenzbildung durch geschachtelte Anfragen

select *from WINZERwhere Name not in (

select Nachnamefrom KRITIKER)



• Vereinigung in SQL explizit mit union• Differenzbildung durch geschachtelte Anfragen

select *from WINZERwhere Name not in (

select Nachnamefrom KRITIKER)


Änderungsoperationen in SQL


• insert: Einfügen eines oder mehrerer Tupel in eineBasisrelation oder Sicht

• update: Ändern von einem oder mehreren Tupel in einerBasisrelation oder Sicht

• delete: Löschen eines oder mehrerer Tupel aus einerBasisrelation oder Sicht

• Lokale und globale Integritätsbedingungen müssen beiÄnderungsoperationen automatisch vom Systemüberprüft werden




















Die update-Anweisung

• Syntax:

update basisrelationset attribut1 = ausdruck1

…attributn = ausdruckn[ where bedingung ]


Beispiel für update

WEINE WeinID Name Jahrgang Weingut Preis2168 Creek Shiraz 2003 Creek 7.993456 Zinfandel 2004 Helena 5.992171 Pinot Noir 2001 Creek 10.993478 Pinot Noir 1999 Helena 19.994711 Riesling Reserve 1999 Müller 14.994961 Chardonnay 2002 Bighorn 9.90

update WEINEset Preis = Preis * 1.10where Jahrgang < 2000


Beispiel für update


update WEINEset Preis = Preis * 1.10where Jahrgang < 2000


Beispiel für update: neue Werte



Weiteres zu update

• Realisierung von Eintupel-Operation mittelsPrimärschlüssel:

update WEINEset Preis = 7.99where WeinID = 3456

• Änderung der gesamten Relation:

update WEINEset Preis = 11


Weiteres zu update






Weiteres zu update






Weiteres zu update






Die delete-Anweisung

• Syntax:

deletefrom basisrelation[ where bedingung ]

• Löschen eines Tupels in der WEINE-Relation:

delete from WEINEwhere WeinID = 4711



• Syntax:






• Syntax:





Weiteres zu delete

• Standardfall ist das Löschen mehrerer Tupel:

delete from WEINEwhere Farbe = 'Weiß'

• Löschen der gesamten Relation:

delete from WEINE


Weiteres zu delete




delete from WEINE


Weiteres zu delete




delete from WEINE


Weiteres zu delete




delete from WEINE


Weiteres zu delete /2

• Löschoperationen können zur Verletzung vonIntegritätsbedingungen führen!

• Beispiel: Verletzung der Fremdschlüsseleigenschaft, fallses noch Weine von diesem Erzeuger gibt:

delete from ERZEUGERwhere Anbaugebiet = 'Hessen'


Die insert-Anweisung

• Syntax:

insertinto basisrelation

[ (attribut1, …, attributn) ]values (konstante1, …, konstanten)

• optionale Attributliste ermöglicht das Einfügen vonunvollständigen Tupeln


insert-Beispiele

insert into ERZEUGER (Weingut, Region)values ('Wairau Hills', 'Marlborough')

• nicht alle Attribute angegeben Wert des fehlendenAttribut Land wird null

insert into ERZEUGERvalues ('Château Lafitte', 'Medoc', 'Bordeaux')


insert-Beispiele





insert-Beispiele





Einfügen von berechneten Daten

• Syntax:

insertinto basisrelation [ (attribut1, …, attributn) ]

SQL-anfrage

• Beispiel:

insert into WEINE (select ProdID, ProdName, 'Rot', ProdJahr,

'Château Lafitte'from LIEFERANT where LName = 'Wein-Kontor' )


Einfügen von berechneten Daten

• Syntax:

insertinto basisrelation [ (attribut1, …, attributn) ]

SQL-anfrage

• Beispiel:

insert into WEINE (select ProdID, ProdName, 'Rot', ProdJahr,

'Château Lafitte'from LIEFERANT where LName = 'Wein-Kontor' )


Anwendungsbeispiel

Mitfahrzentrale

• Welche Daten?• Mitfahrangebote: Wann?Von wo? Wohin? Wer?Plätze?

• Nutzer: Anmeldung,Kontaktdaten

• Reservierung: Wer? WelchesAngebot?



Mitfahrzentrale






Mitfahrzentrale






Mitfahrzentrale: Datenbank

AngebotIDVonNachDatumAnzahl PlätzePreisFahrer

Mitfahrangebot

NameKontakt

Nutzer

MitfahrangebotMitfahrer

Reservierung


Mitfahrzentrale: Datenbank in SQL

create table Nutzer (NutzerID varchar(10) primary key,Name varchar(100),Kontakt varchar(500));


Mitfahrzentrale: Datenbank in SQL /2

create table Mitfahrangebot (AngebotID int primary key,Von varchar(100) not null,Nach varchar(100) not null,Datum date not null,AnzPlaetze int,Preis decimal,Fahrer varchar(10)

references Nutzer(NutzerID));


Mitfahrzentrale: Datenbank in SQL /3

create table Reservierung (AngebotID int

references Mitfahrangebot(AngebotID),Mitfahrer varchar(10)

references Nutzer(NutzerID));


Mitfahrzentrale: Anfragen

• Welche Angebote gibt es heute von Ilmenau nach Erfurt?

select * from Mitfahrangebotwhere Von = 'Ilmenau' and Nach = 'Erfurt'

and Datum = date('now');

• Reservierung für eine bestimmte Mitfahrgelegenheit

insert into Reservierung values (1, 'holgi');


Mitfahrzentrale: Anfragen /2

• Wer will bei mir mitfahren?

select R.Mitfahrerfrom Reservierung R, Mitfahrangebot Mwhere R.AngebotID = M.AngebotID

and M.Fahrer = 'heike';


Zusammenfassung

• Relationenmodell: Datenbank als Sammlung von Tabellen• Integritätsbedingungen im Relationenmodell• Tabellendefinition in SQL• Relationenalgebra: Anfrageoperatoren• Grundkonzepte von SQL-Anfragen und -Änderungen


Kontrollfragen

• Was ist eine Relation?

• Was definiert die Relationenalgebra?• Wie wird eine Realweltobjekt in einerrelationalen Datenbank repräsentiert?

• Wie werden Tabellen in SQL definiert undmanipuliert?

• Was sind Integritätsbedingungen?


Kontrollfragen

• Was ist eine Relation?• Was definiert die Relationenalgebra?

• Wie wird eine Realweltobjekt in einerrelationalen Datenbank repräsentiert?




Kontrollfragen

• Was ist eine Relation?• Was definiert die Relationenalgebra?• Wie wird eine Realweltobjekt in einerrelationalen Datenbank repräsentiert?




Kontrollfragen





Kontrollfragen





Teil III

Entity-Relationship-Modell


1. Datenbankmodelle

2. ER-Modell

3. Weitere Konzepte im ER-Modell



1. Datenbankmodelle

2. ER-Modell




1. Datenbankmodelle

2. ER-Modell




• Kenntnis der Konzepte desEntity-Relationship-Modells

• Fähigkeiten zur konzeptuellenModellierung eines Anwendungsbereichs



• Kenntnis der Konzepte desEntity-Relationship-Modells

• Fähigkeiten zur konzeptuellenModellierung eines Anwendungsbereichs


Datenbankmodelle

Grundlagen von Datenbankmodellen

DatenbankmodellEin Datenbankmodell ist ein System von Konzepten zurBeschreibung von Datenbanken. Es legt Syntax und Semantikvon Datenbankbeschreibungen für ein Datenbanksystem fest.

• Datenbankbeschreibungen = Datenbankschemata


Ein Datenbankmodell legt fest...

1. statische Eigenschaften1.1 Objekte1.2 Beziehungen

inklusive der Standard-Datentypen, die Daten über dieBeziehungen und Objekte darstellen können,

2. dynamische Eigenschaften wie2.1 Operationen2.2 Beziehungen zwischen Operationen,

3. Integritätsbedingungen an3.1 Objekte3.2 Operationen


Datenbankmodelle

• Klassische Datenbankmodelle sind speziell geeignet für• große Informationsmengen mit relativ starrer Struktur und• die Darstellung statischer Eigenschaften undIntegritätsbedingungen (also die Bereiche 1(a), 1(b) und3(a))

• Entwurfsmodelle: (E)ER-Modell, UML, …• Realisierungsmodelle: Relationenmodell, objektorientierteModelle, …


Datenbanken versus Programmiersprachen

Datenbankkonzept Typsystem einerProgrammiersprache

Datenbankmodell TypsystemRelation, Attribut … int, struct …Datenbankschema Variablendeklaration

relation WEIN = (…) var x: int,y: struct Wein

Datenbank WerteWEIN(4961, ’Chardonnay’, 42, ’Cabernet Sauvignon’

’Weiß’, …) 42, ’Cabernet Sauvignon’


Abstraktionsstufen

Modelle Daten Algorithmenabstrakt Entity-Relationship-Modell Struktogrammekonkret Hierarchisches Modell Pascal

Netzwerkmodell C, C++Relationenmodell Java, C#


Datenbankmodelle im Überblick

HM

NWMRM

SQL

NF2

eNF2

ER

SDM

OEM

UMLORDM

SQL:1999

SQL:2003

ODMG

OODM(C++)

implementierungsnah abstrakt

2005

2000

1990

1980

1970

ab Mitte1960


Datenbankmodelle im Überblick /2

• HM: hierarchisches Modell, NWM: Netzwerkmodell, RM:Relationenmodell

• NF2: Modell der geschachtelten (Non-First-Normal-Form =

NF2) Relationen, eNF2: erweitertes NF2-Modell• ER: Entity-Relationship-Modell, SDM: semantischeDatenmodelle

• OODM / C++: objektorientierte Datenmodelle auf Basisobjektorientierter Programmiersprachen wie C++, OEM:objektorientierte Entwurfsmodelle (etwa UML), ORDM:objektrelationale Datenmodelle


ER-Modell

Das ER-Modell

Entity: Objekt der realen oder der Vorstellungswelt, überdas Informationen zu speichern sind, z.B.Produkte (Wein, Katalog), Winzer oder Kritiker;aber auch Informationen über Ereignisse, wie z.B.Bestellungen

Relationship: beschreibt eine Beziehung zwischen Entities, z.B.ein Kunde bestellt einen Wein oder ein Wein wirdvon einem Winzer angeboten

Attribut: repräsentiert eine Eigenschaft von Entities oderBeziehungen, z.B. Name eines Kunden, Farbeeines Weines oder Datum einer Bestellung


ER-Beispiel

Rebsorte

Anbaugebiet

Wein

sitzt in

produziertvon Erzeuger

hergestellt aus

empfiehlt

Gericht

Kritiker

[0,*]

[1,7]

[0,*]

[0,*]

[0,*]

Anteil

NameFarbe

Weingut Adresse

Name

Region

Name

Restsüße

Farbe

Jahrgang

Bezeichnung

Beilage

Name

Organisation

Land

Lizenz

besitzt

LizenzNr

Menge


Werte

• Werte: primitive Datenelemente, die direkt darstellbar sind• Wertemengen sind beschrieben durch Datentypen, dieneben einer Wertemenge auch die Grundoperationen aufdiesen Werten charakterisieren

• ER-Modell: vorgegebene Standard-Datentypen, etwa dieganzen Zahlen int, die Zeichenketten string,Datumswerte date etc.

• jeder Datentyp stellt Wertebereich mit Operationen undPrädikaten dar


Entities

• Entities sind die in einer Datenbank zu repräsentierendenInformationseinheiten

• im Gegensatz zu Werten nicht direkt darstellbar, sondernnur über ihre Eigenschaften beobachtbar

• Entities sind eingeteilt in Entity-Typen, etwa E1, E2 . . .

Wein

• Menge der aktuellen Entities: E = e1, e2, . . . , en


Attribute

• Attribute modellieren Eigenschaften von Entities oderauch Beziehungen

• alle Entities eines Entity-Typs haben dieselben Arten vonEigenschaften; Attribute werden somit für Entity-Typendeklariert

Wein

Name Farbe

Jahrgang

• textuelle Notation E(A1 : D1, . . . ,Am : Dm)


Identifizierung durch Schlüssel

• Schlüsselattribute: Teilmenge der gesamten Attributeeines Entity-Typs E(A1, . . . ,Am)

S1, . . . , Sk ⊆ A1, . . . ,Am

• in jedem Datenbankzustand identifizieren die aktuellenWerte der Schlüsselattribute eindeutig Instanzen desEntity-Typs E

• bei mehreren möglichen Schlüsselkandidaten: Auswahleines Primärschlüssels

• Notation: markieren durch Unterstreichung:

E(. . . , S1, . . . , Si, . . .)


Beziehungstypen

• Beziehungen zwischen Entities werden zuBeziehungstypen zusammengefasst

• allgemein: beliebige Anzahl n ≥ 2 von Entity-Typen kannan einem Beziehungstyp teilhaben

• zu jedem n-stelligen Beziehungstyp R gehören n Entity-Typen E1, . . . , En

• Ausprägung R eines Beziehungstyps

R ⊆ E1 × E2 × · · · × En


Beziehungstypen /2

• Notation

WeinErzeuger produziert

• textuelle Notation: R(E1, E2, . . . , En)• wenn Entity-Typ mehrfach an einem Beziehungstypbeteiligt: Vergabe von Rollennamen möglich

verheiratet(Frau: Person, Mann: Person)


Beziehungsattribute

• Beziehungen können ebenfalls Attribute besitzen• Attributdeklarationen werden beim Beziehungstypvorgenommen; gilt auch hier für alle Ausprägungen einesBeziehungstyps Beziehungsattribute

RebsorteWeinhergestellt

aus

Anteil

• textuelle Notation: R(E1, . . . , En;A1, . . . ,Ak)Sattler/Saake | VL Datenbanksysteme | 22. September 2019 3–18

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



• Stelligkeit oder Grad:• Anzahl der beteiligten Entity-Typen

• häufig: binär• Beispiel: Lieferant liefert Produkt





• Stelligkeit oder Grad:• Anzahl der beteiligten Entity-Typen• häufig: binär

• Beispiel: Lieferant liefert Produkt• Kardinalität oder Funktionalität:




• 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




• 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




• 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




• 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


Zwei- vs. mehrstellige Beziehungen

Weinempfiehlt

Gericht

Kritiker


Zwei- vs. mehrstellige Beziehungen

Weinempfiehlt

Gericht

Kritiker

WeinG-K

Gericht

Kritiker

G-W

K-W


Ausprägungen im Beispiel

Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2


Ausprägungen im Beispiel

Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2

Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2


Rekonstruktion der Ausprägungen

Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2

• g1 – k1 – w1• g1 – k2 – w2• g2 – k2 – w1• aber auch: g1 – k2 – w1



Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2

• g1 – k1 – w1

• g1 – k2 – w2• g2 – k2 – w1• aber auch: g1 – k2 – w1



Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2

• g1 – k1 – w1• g1 – k2 – w2

• g2 – k2 – w1• aber auch: g1 – k2 – w1



Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2

• g1 – k1 – w1• g1 – k2 – w2• g2 – k2 – w1

• aber auch: g1 – k2 – w1



Gericht

Kritiker

Wein

g1

g2

w1

w2

k1 k2

• g1 – k1 – w1• g1 – k2 – w2• g2 – k2 – w1• aber auch: g1 – k2 – w1


1:1-Beziehungen

• jedem Entity e1 vom Entity-Typ E1 ist maximal ein Entity e2aus E2 zugeordnet und umgekehrt

• Beispiele: Prospekt beschreibt Produkt, Mann istverheiratet mit Frau

E1 E2


1:N-Beziehungen

• jedem Entity e1 vom Entity-Typ E1 sind beliebig vieleEntities E2 zugeordnet, aber zu jedem Entity e2 gibt esmaximal ein e1 aus E1

• Beispiele: Lieferant liefert Produkt, Mutter hat Kinder

E1 E2


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 Entityeines Entity-Typs E2 zugeordnet.

R : E1 → E2

Weinproduziert

von Erzeuger


1:1-Beziehung

Erzeuger besitzt Lizenz


M:N-Beziehungen

• keine Restriktionen• Beispiel: Bestellung umfasst Produkte

E1 E2


[min,max]-Notation

E1 EnR[min1, max1] [minn, maxn]

E2

[min2, max2]...

• schränkt die möglichen Teilnahmen von Instanzen derbeteiligten Entity-Typen an der Beziehung ein, indem einminimaler und ein maximaler Wert vorgegeben wird


[min,max]-Notation /2

• Notation für Kardinalitätsangaben an einemBeziehungstyp

R(E1, . . . , Ei[mini,maxi], . . . , En)

• Kardinalitätsbedingung:mini ≤ |r | r ∈ R ∧ r.Ei = ei| ≤ maxi

• Spezielle Wertangabe für maxi ist ∗


Kardinalitätsangaben

• [0, ∗] legt keine Einschränkung fest (default)• R(E1[0, 1], E2) entspricht einer (partiellen) funktionalenBeziehung R : E1 → E2, da jede Instanz aus E1 maximaleiner Instanz aus E2 zugeordnet ist

• totale funktionale Beziehung wird durch R(E1[1, 1], E2)modelliert


Kardinalitätsangaben: Beispiele

• partielle funktionale Beziehunglagert_in(Produkt[0,1],Fach[0,3])

„Jedes Produkt ist im Lager in einem Fach abgelegt,allerdings wird ausverkauften bzw. gegenwärtig nichtlieferbaren Produkte kein Fach zugeordnet. Pro Fachkönnen maximal drei Produkte gelagert werden.“

• totale funktionale Beziehungliefert(Lieferant[0,*],Produkt[1,1])

„Jedes Produkt wird durch genau einen Lieferant geliefert,aber ein Lieferant kann durchaus mehrere Produkteliefern.“


Alternative Kardinalitätsangabe

geliefert vonProdukt Lieferant

[1,1] [0,*]

geliefert vonProdukt Lieferant

N 1


Weitere Konzepte im ER-Modell

Abhängige Entity-Typen

• abhängiger Entity-Typ: Identifikation über funktionaleBeziehung

WeinJahrgang Weingehört-zu

Jahr

Restsüße

Name

Farbe

• Abhängige Entities im ER-Modell: Funktionale Beziehungals Schlüssel


Abhängige Entity-Typen /2

• Mögliche Ausprägung für abhängige Entities

Name: Pinot NoirFarbe: Rot

Name: Riesling ReserveFarbe: Weiß

Name: ZinfandelFarbe: Rot

gehört-zu

gehört-zu

gehört-zu

Jahr: 2004Restsüße: 1,2




Abhängige Entity-Typen /3

• Alternative Notation

NWeinJahrgang Weingehört-zu

1

Jahr

Restsüße

Name

Farbe


Die ist-Beziehung

• Spezialisierungs-/Generalisierungsbeziehung oder auchist-Beziehung (engl. is-a relationship)

• textuelle Notation: E1 ist E2• ist-Beziehung entspricht semantisch einer injektivenfunktionalen Beziehung

WeinSchaumwein IST

Name

Farbe

Herstellung


Eigenschaften der ist-Beziehung

• Jeder Schaumwein-Instanz ist genau eine Wein-Instanzzugeordnet Schaumwein-Instanzen werden durch die funktionaleist-Beziehung identifiziert

• Nicht jeder Wein ist zugleich ein Schaumwein• Attribute des Entity-Typs Wein treffen auch aufSchaumweine zu: „vererbte“ Attribute

Schaumwein(Name,Farbe︸︷︷︸von Wein

,Herstellung)

• nicht nur die Attributdeklarationen vererben sich, sondernauch jeweils die aktuellen Werte für eine Instanz


Ausprägung für ist-Beziehung

Schaumweine

Weine

w1

w2

w3

w1

w2

w5

w4

w6

w4


Alternative Notation für ist-Beziehung

WeinSchaumwein

Name FarbeHerstellung


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 nichtteilnehmen müssen

• Aspekte wie Attributvererbung werden hiervon nichterfasst


Optionalität von Attributen

Anbaugebietsitzt inErzeuger

Weingut AdresseName

RegionLand


Konzepte im Überblick

Begriff Informale BedeutungEntity zu repräsentierende InformationseinheitEntity-Typ Gruppierung von Entitys mit gleichen Eigenschaf-

tenBeziehungstyp Gruppierung von Beziehungen zwischen EntitysAttribut datenwertige Eigenschaft eines Entitys oder einer

BeziehungSchlüssel identifizierende Eigenschaft von EntitysKardinalitäten Einschränkung von Beziehungstypen bezüglich

der mehrfachen Teilnahme von Entitys an der Be-ziehung

Stelligkeit Anzahl der an einem Beziehungstyp beteiligtenEntity-Typen

funktionale Beziehung Beziehungstyp mit Funktionseigenschaft


Konzepte im Überblick /2

Begriff Informale BedeutungEntity zu repräsentierende Informationseinheitabhängige Entitys Entitys, die nur abhängig von anderen Entitys exis-

tieren könnenist-Beziehung Spezialisierung von Entity-TypenOptionalität Attribute oder funktionale Beziehungen als parti-

elle Funktionen


Zusammenfassung

• Datenbankmodell, Datenbankschema, Datenbank(instanz)

• Entity-Relationship-Modell• Weitere Konzepte im ER-Modell• Basis: Kapitel 3 von [SSH13]


Zusammenfassung

• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell

• Weitere Konzepte im ER-Modell• Basis: Kapitel 3 von [SSH13]


Zusammenfassung

• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell• Weitere Konzepte im ER-Modell

• Basis: Kapitel 3 von [SSH13]


Zusammenfassung

• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell• Weitere Konzepte im ER-Modell• Basis: Kapitel 3 von [SSH13]


Kontrollfragen

• Was definiert ein Datenbankmodell? Wasunterscheidet Modell und Schema?

• Welche Konzepte definiert das ER-Modell?• Durch welche Eigenschaften sindBeziehungstypen charakterisiert?

• Was unterscheidet abhängigeEntity-Typen von normalen Entity-Typen?


Kontrollfragen


• Welche Konzepte definiert das ER-Modell?

• Durch welche Eigenschaften sindBeziehungstypen charakterisiert?



Kontrollfragen





Kontrollfragen





Teil IV

Datenbankentwurf

Datenbankentwurf

1. Phasen des Datenbankentwurfs

2. Weiteres Vorgehen beim Entwurf

3. Kapazitätserhaltende Abbildungen

4. ER-auf-RM-Abbildung


Datenbankentwurf






Datenbankentwurf






Datenbankentwurf







• Kenntnisse über Ziele und Ablauf desDatenbankentwurfsprozesses

• Kenntnisse der Regeln zur Abbildung vonER-Schemata auf Relationenschemata



• Kenntnisse über Ziele und Ablauf desDatenbankentwurfsprozesses

• Kenntnisse der Regeln zur Abbildung vonER-Schemata auf Relationenschemata


Phasen des Datenbankentwurfs

Entwurfsaufgabe

• Datenhaltung für mehrere Anwendungssysteme undmehrere Jahre

• daher: besondere Bedeutung• Anforderungen an Entwurf

• Anwendungsdaten jeder Anwendung sollen aus Daten derDatenbank ableitbar sein (und zwar möglichst effizient)

• nur „vernünftige“ (wirklich benötigte) Daten sollengespeichert werden

• nicht-redundante Speicherung


Entwurfsaufgabe


• daher: besondere Bedeutung

• Anforderungen an Entwurf





Entwurfsaufgabe







Entwurfsaufgabe







Entwurfsaufgabe







Entwurfsaufgabe







Phasenmodell

Anforderungsanalyse

KonzeptionellerEntwurf

Verteilungsentwurf

Logischer Entwurf

Datendefinition

Physischer Entwurf

Implementierung &Wartung


Anforderungsanalyse

• Vorgehensweise: Sammlung des Informationsbedarfs inden Fachabteilungen

• Ergebnis:

• informale Beschreibung (Texte, tabellarischeAufstellungen, Formblätter, usw.) des Fachproblems

• Trennen der Information über Daten (Datenanalyse) vonden Information über Funktionen (Funktionsanalyse)

• „Klassischer“ DB-Entwurf:

• nur Datenanalyse und Folgeschritte

• Funktionsentwurf:

• siehe Methoden des Software Engineering


Anforderungsanalyse


• Ergebnis:

• informale Beschreibung (Texte, tabellarischeAufstellungen, Formblätter, usw.) des Fachproblems







Anforderungsanalyse


• Ergebnis:• informale Beschreibung (Texte, tabellarischeAufstellungen, Formblätter, usw.) des Fachproblems







Anforderungsanalyse









Anforderungsanalyse





• nur Datenanalyse und Folgeschritte• Funktionsentwurf:



Anforderungsanalyse




• „Klassischer“ DB-Entwurf:• nur Datenanalyse und Folgeschritte




Anforderungsanalyse








Anforderungsanalyse





• Funktionsentwurf:• siehe Methoden des Software Engineering


Konzeptioneller Entwurf

• erste formale Beschreibung des Fachproblems

• Sprachmittel: semantisches Datenmodell• Vorgehensweise:

• Modellierung von Sichten z.B. für verschiedeneFachabteilungen

• Analyse der vorliegenden Sichten in Bezug auf Konflikte• Integration der Sichten in ein Gesamtschema

• Ergebnis: konzeptionelles Gesamtschema, z.B.ER-Diagramm



• erste formale Beschreibung des Fachproblems• Sprachmittel: semantisches Datenmodell

• Vorgehensweise:






• erste formale Beschreibung des Fachproblems• Sprachmittel: semantisches Datenmodell• Vorgehensweise:














• Analyse der vorliegenden Sichten in Bezug auf Konflikte

• Integration der Sichten in ein Gesamtschema















Phasen des konzeptionellen Entwurf

Sichtenentwurf

Sichtenanalyse

Sichtenintegration

konzeptioneller Entwurf


Weiteres Vorgehen beim Entwurf


• ER-Modellierung von verschiedenen Sichten aufGesamtinformation, z.B. für verschiedene Fachabteilungeneines 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 Wartungphysischer Entwurf




• Analyse und Integration der Sichten

• Ergebnis: konzeptionelles Gesamtschema













• Verteilungsentwurf bei verteilter Speicherung

• Abbildung auf konkretes Implementierungsmodell (z.B.Relationenmodell) logischer Entwurf















Sichtenintegration

• Analyse der vorliegenden Sichten in Bezug auf Konflikte

• Integration der Sichten in ein Gesamtschema

Sicht #1 Sicht #2

Sicht #3

GlobalesSchema

Konsoli-

dierung


Sichtenintegration


Sicht #1 Sicht #2

Sicht #3

GlobalesSchema

Konsoli-

dierung


Sichtenintegration


Sicht #1 Sicht #2

Sicht #3

GlobalesSchema

Konsoli-

dierung


Sichtenintegration


Sicht #1 Sicht #2

Sicht #3

GlobalesSchema

Konsoli-

dierung


Integrationskonflikte

• Namenskonflikte: Homonyme / Synonyme

• Homonyme: Schloss; Kunde• Synonyme: Auto, KFZ, Fahrzeug

• Typkonflikte: verschiedene Strukturen für das gleicheElement

• Wertebereichskonflikte: verschiedene Wertebereiche fürein Element

• Bedingungskonflikte: z.B. verschiedene Schlüssel für einElement

• Strukturkonflikte: gleicher Sachverhalt durchunterschiedliche Konstrukte ausgedrückt



• Namenskonflikte: Homonyme / Synonyme• Homonyme: Schloss; Kunde

• Synonyme: Auto, KFZ, Fahrzeug







• Namenskonflikte: Homonyme / Synonyme• Homonyme: Schloss; Kunde• Synonyme: Auto, KFZ, Fahrzeug


































Verteilungsentwurf

• sollen Daten auf mehreren Rechnern verteilt vorliegen,muss Art und Weise der verteilten Speicherung festgelegtwerden

• z.B. bei einer RelationKUNDE (KNr, Name, Adresse, PLZ, Konto)

• horizontale Verteilung:KUNDE_1 (KNr, Name, Adresse, PLZ, Konto)where PLZ < 50.000KUNDE_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)


Logischer Entwurf

• Sprachmittel: Datenmodell des ausgewählten„Realisierungs“-DBMS z.B. relationales Modell

• Vorgehensweise:

1. (automatische) Transformation des konzeptionellenSchemas z.B. ER→ relationales Modell

2. Verbesserung des relationalen Schemas anhand vonGütekriterien(Normalisierung, siehe Kapitel 5):Entwurfsziele: Redundanzvermeidung, …

• Ergebnis: logisches Schema, z.B. Sammlung vonRelationenschemata


Logischer Entwurf


• Vorgehensweise:

1. (automatische) Transformation des konzeptionellenSchemas z.B. ER→ relationales Modell




Logischer Entwurf


• Vorgehensweise:1. (automatische) Transformation des konzeptionellenSchemas z.B. ER→ relationales Modell




Logischer Entwurf






Logischer Entwurf






Datendefinition

• Umsetzung des logischen Schemas in ein konkretesSchema

• 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


Physischer Entwurf

• Ergänzen des physischen Entwurfs umZugriffsunterstü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


Indexe in SQL

create [ unique ] index indexnameon relname (

attrname [ asc | desc ],attrname [ asc | desc ],

…)

• Beispiel

create index WeinIdx on WEINE (Name)


Notwendigkeit für Zugriffspfade

• Beispiel: Tabelle mit 100 GB Daten, Festplattentransferrateca. 50 MB/s

• Operation: Suchen eines Tupels (Selektion)• Implementierung: sequentielles Durchsuchen• Aufwand: 102.400/50 = 2.048 sec. ≈ 34 min.




• Operation: Suchen eines Tupels (Selektion)

• Implementierung: sequentielles Durchsuchen• Aufwand: 102.400/50 = 2.048 sec. ≈ 34 min.




• Operation: Suchen eines Tupels (Selektion)• Implementierung: sequentielles Durchsuchen

• Aufwand: 102.400/50 = 2.048 sec. ≈ 34 min.




• Operation: Suchen eines Tupels (Selektion)• Implementierung: sequentielles Durchsuchen• Aufwand: 102.400/50 = 2.048 sec. ≈ 34 min.


Implementierung und Wartung

• Phasen• der Wartung,• der weiteren Optimierung der physischen Ebene,• der Anpassung an neue Anforderungen undSystemplattformen,

• der Portierung auf neue Datenbankmanagementsysteme• etc.


Kapazitätserhaltende Abbildungen

Umsetzung des konzeptionellen Schemas

• Umsetzung auf logisches Schema

• Beispiel: ER→ RM• korrekt?• Qualität der Abbildung?

• Erhaltung der Informationskapazität

• Kann man nach der Abbildung genau die selben Datenabspeichern wie vorher?

• ... oder etwa mehr?• ... oder etwa weniger?



• Umsetzung auf logisches Schema• Beispiel: ER→ RM

• korrekt?• Qualität der Abbildung?






• Umsetzung auf logisches Schema• Beispiel: ER→ RM• korrekt?

• Qualität der Abbildung?• Erhaltung der Informationskapazität





• Umsetzung auf logisches Schema• Beispiel: ER→ RM• korrekt?• Qualität der Abbildung?













• Erhaltung der Informationskapazität• Kann man nach der Abbildung genau die selben Datenabspeichern wie vorher?






• ... oder etwa mehr?

• ... oder etwa weniger?







Kapazitätserhöhende Abbildung

Lizenz besitzt Erzeuger

WeingutLizenzNo

• Abbildung auf R = LizenzNo,Weingut mit genaueinem Schlüssel K = LizenzNo

• mögliche ungültige Relation:BESITZT LizenzNo Weingut

007 Helena42 Helena




WeingutLizenzNo



007 Helena42 Helena




WeingutLizenzNo



007 Helena42 Helena


Kapazitätserhaltende Abbildung


WeingutLizenzNo

• korrekte AusprägungBESITZT LizenzNo Weingut

007 Helena42 Müller

• korrekte Schlüsselmenge

K = LizenzNo, Weingut




WeingutLizenzNo








WeingutLizenzNo






Kapazitätsvermindernde Abbildung

Wein enthält Rebsorte

SortennameWName

• Relationenschema mit einem Schlüssel WName• als Ausprägung nicht mehr möglich:

ENTHÄLT WName SortennameZinfandel Red Blossom ZinfandelBordeaux Blanc Cabernet SauvignonBordeaux Blanc Muscadelle




SortennameWName

• Relationenschema mit einem Schlüssel WName

• als Ausprägung nicht mehr möglich:ENTHÄLT WName Sortenname

Zinfandel Red Blossom ZinfandelBordeaux Blanc Cabernet SauvignonBordeaux Blanc Muscadelle




SortennameWName

• Relationenschema mit einem Schlüssel WName• als Ausprägung nicht mehr möglich:

ENTHÄLT WName SortennameZinfandel Red Blossom ZinfandelBordeaux Blanc Cabernet SauvignonBordeaux Blanc Muscadelle



• kapazitätserhaltend mit Schlüssel beider Entity-Typen imRelationenschema als neuer Schlüssel

K = WName,Sortenname


ER-auf-RM-Abbildung

Beispielabbildung ER-RM: Eingabe

Rebsorte

Wein

produziertErzeuger

enthält

Anteil

SortennameFarbe

Weingut Adresse

Restsüße

Farbe

Jahrgang

WName


Beispielabbildung ER-RM: Ergebnis

1. REBSORTE = Farbe,Sortenname2. ENTHÄLT = Sortenname,WName,Anteil3. WEIN = Farbe,WName,Jahrgang,Restsüße4. PRODUZIERT = WName,Weingut5. ERZEUGER = Weingut,Adresse


ER-Abbildung auf Relationen

• Entity-Typen und Beziehungstypen: jeweils aufRelationenschemata

• Attribute: Attribute des Relationenschemas, Schlüsselwerden übernommen

• Kardinalitäten der Beziehungen: durch Wahl der Schlüsselbei den zugehörigen Relationenschemata ausgedrückt

• in einigen Fällen: Verschmelzen der Relationenschematavon Entity- und Beziehungstypen

• zwischen den verbleibenden Relationenschemata diverseFremdschlüsselbedingungen einführen






























Abbildung von Beziehungstypen

• neues Relationenschema mit allen Attributen desBeziehungstyps, zusätzlich Übernahme allerPrimärschlüssel der beteiligten Entity-Typen

• Festlegung der Schlüssel:• m:n-Beziehung: beide Primärschlüssel zusammen werdenSchlüssel im neuen Relationenschema

• 1:n-Beziehung: Primärschlüssel der n-Seite (bei derfunktionalen Notation die Seite ohne Pfeilspitze) wirdSchlüssel im neuen Relationenschema

• 1:1-Beziehung: beide Primärschlüssel werden je einSchlüssel im neuen Relationenschema, derPrimärschlüssel wird dann aus diesen Schlüsseln gewählt














n:m-Beziehungen


Sortenname Farbe

WName

Restsüße

Farbe

Jahrgang

Anteil

• Umsetzung1. REBSORTE = Farbe,Sortenname2. ENTHÄLT = Sortenname,WName,Anteil3. WEIN = Farbe,WName,Jahrgang,Restsüße

• Attribute Sortenname und WName sind gemeinsamSchlüssel


n:m-Beziehungen


Sortenname Farbe

WName

Restsüße

Farbe

Jahrgang

Anteil

• Umsetzung1. REBSORTE = Farbe,Sortenname2. ENTHÄLT = Sortenname,WName,Anteil3. WEIN = Farbe,WName,Jahrgang,Restsüße

• Attribute Sortenname und WName sind gemeinsamSchlüssel


1:n-Beziehungen


Adresse RegionWeingut Name

• Umsetzung (zunächst)• ERZEUGER mit den Attributen Weingut und Adresse,• ANBAUGEBIET mit den Attributen Name und Region und• SITZT_IN mit den Attributen Weingut und Name unddem Primärschlüssel der n-Seite Weingut alsPrimärschlüssel dieses Schemas.


1:n-Beziehungen


Adresse RegionWeingut Name

• Umsetzung (zunächst)• ERZEUGER mit den Attributen Weingut und Adresse,• ANBAUGEBIET mit den Attributen Name und Region und• SITZT_IN mit den Attributen Weingut und Name unddem Primärschlüssel der n-Seite Weingut alsPrimärschlüssel dieses Schemas.


Mögliche Verschmelzungen

• optionale Beziehungen ([0,1] oder [0,n]) werden nichtverschmolzen

• bei Kardinalitäten [1,1] oder [1,n] (zwingende Beziehungen)Verschmelzung möglich:

• 1:n-Beziehung: das Entity-Relationenschema der n-Seitekann in das Relationenschema der Beziehung integriertwerden

• 1:1-Beziehung: beide Entity-Relationenschemata können indas Relationenschema der Beziehung integriert werden




















1:1-Beziehungen


Weingut AdresseHektoLiterLizenzNo

• Umsetzung (zunächst)• ERZEUGER mit den Attributen Weingut und Adresse• LIZENZ mit den beiden Attributen LizenzNo undHektoliter

• BESITZT mit den Primärschlüsseln der beiden beteiligtenEntity-Typen jeweils als Schlüssel dieses Schemas, alsoLizenzNo und Weingut


1:1-Beziehungen: Verschmelzung

ERZEUGER Weingut Adresse LizenzNo HektoliterRotkäppchen Freiberg 42-007 10.000Weingut Müller Dagstuhl 42-009 250


1:1-Beziehungen: Verschmelzung /2

Erzeuger ohne Lizenz erfordern Nullwerte:

ERZEUGER Weingut Adresse LizenzNo HektoliterRotkäppchen Freiberg 42-007 10.000Weingut Müller Dagstuhl ⊥ ⊥

freie Lizenzen führen zu weiteren Nullwerten:

ERZEUGER Weingut Adresse LizenzNo HektoliterRotkäppchen Freiberg 42-007 10.000Weingut Müller Dagstuhl ⊥ ⊥⊥ ⊥ 42-003 100.000



























NWeinJahrgang Weingehört-zu1

JahrRestsüße WName Farbe

• Umsetzung

1. WEINJAHRGANG = WName,Jahr,Restsüße2. WEIN = Farbe,WName• Attribut WName in WEINJAHRGANG ist Fremdschlüssel zurRelation WEIN





• Umsetzung1. WEINJAHRGANG = WName,Jahr,Restsüße

2. WEIN = Farbe,WName• Attribut WName in WEINJAHRGANG ist Fremdschlüssel zurRelation WEIN





• Umsetzung1. WEINJAHRGANG = WName,Jahr,Restsüße2. WEIN = Farbe,WName

• Attribut WName in WEINJAHRGANG ist Fremdschlüssel zurRelation WEIN





• Umsetzung1. WEINJAHRGANG = WName,Jahr,Restsüße2. WEIN = Farbe,WName• Attribut WName in WEINJAHRGANG ist Fremdschlüssel zurRelation WEIN


ist-Beziehung

WeinSchaumwein

WName FarbeHerstellung

• Umsetzung

1. WEIN = Farbe,WName,Jahrgang,Restsüße2. SCHAUMWEIN = WName,Herstellung• WName in SCHAUMWEIN ist Fremdschlüssel bezüglich derRelation WEIN


ist-Beziehung

WeinSchaumwein


• Umsetzung1. WEIN = Farbe,WName,Jahrgang,Restsüße

2. SCHAUMWEIN = WName,Herstellung• WName in SCHAUMWEIN ist Fremdschlüssel bezüglich derRelation WEIN


ist-Beziehung

WeinSchaumwein


• Umsetzung1. WEIN = Farbe,WName,Jahrgang,Restsüße2. SCHAUMWEIN = WName,Herstellung

• WName in SCHAUMWEIN ist Fremdschlüssel bezüglich derRelation WEIN


ist-Beziehung

WeinSchaumwein


• Umsetzung1. WEIN = Farbe,WName,Jahrgang,Restsüße2. SCHAUMWEIN = WName,Herstellung• WName in SCHAUMWEIN ist Fremdschlüssel bezüglich derRelation WEIN


Rekursive Beziehungen

Anbaugebiet grenzt-an

nachName

Regionvon

• Umsetzung

1. ANBAUGEBIET = Name,Region2. GRENZT_AN = nach,von




nachName

Regionvon

• Umsetzung1. ANBAUGEBIET = Name,Region

2. GRENZT_AN = nach,von




nachName

Regionvon

• Umsetzung1. ANBAUGEBIET = Name,Region2. GRENZT_AN = nach,von


Rekursive funktionale Beziehungen

Kritiker SchülerVon

MentorName

Organisation Schüler

• Umsetzung

1. KRITIKER = Name,Organisation,Mentorname• Mentorname ist Fremdschlüssel auf das Attribut Name derRelation KRITIKER.




MentorName


• Umsetzung1. KRITIKER = Name,Organisation,Mentorname

• Mentorname ist Fremdschlüssel auf das Attribut Name derRelation KRITIKER.




MentorName


• Umsetzung1. KRITIKER = Name,Organisation,Mentorname• Mentorname ist Fremdschlüssel auf das Attribut Name derRelation KRITIKER.


Mehrstellige Beziehungen

Weinempfiehlt

Gericht

Kritiker

WName

Restsüße

Farbe

Jahrgang

Bezeichnung Beilage

Name Organisation


Mehrstellige Beziehungen: Ergebnis

• jeder beteiligte Entity-Typ wird nach den obigen Regelnbehandelt

• für Beziehung Empfiehlt werden Primärschlüssel derdrei beteiligten Entity-Typen in das resultierendeRelationenschema aufgenommen

• Beziehung ist allgemeiner Art (k:m:n-Beziehung): allePrimärschlüssel bilden zusammen den Schlüssel

1. EMPFIEHLT = WName,Bezeichnung,Name2. GERICHT = Bezeichnung,Beilage3. WEIN = Farbe,WName,Jahrgang,Restsüße4. KRITIKER = Name,Organisation

• Die drei Schlüsselattribute von EMPFIEHLT sindwiederum Fremdschlüssel



















• Beziehung ist allgemeiner Art (k:m:n-Beziehung): allePrimärschlüssel bilden zusammen den Schlüssel1. EMPFIEHLT = WName,Bezeichnung,Name

2. GERICHT = Bezeichnung,Beilage3. WEIN = Farbe,WName,Jahrgang,Restsüße4. KRITIKER = Name,Organisation






• Beziehung ist allgemeiner Art (k:m:n-Beziehung): allePrimärschlüssel bilden zusammen den Schlüssel1. EMPFIEHLT = WName,Bezeichnung,Name2. GERICHT = Bezeichnung,Beilage

3. WEIN = Farbe,WName,Jahrgang,Restsüße4. KRITIKER = Name,Organisation






• Beziehung ist allgemeiner Art (k:m:n-Beziehung): allePrimärschlüssel bilden zusammen den Schlüssel1. EMPFIEHLT = WName,Bezeichnung,Name2. GERICHT = Bezeichnung,Beilage3. WEIN = Farbe,WName,Jahrgang,Restsüße

4. KRITIKER = Name,Organisation• Die drei Schlüsselattribute von EMPFIEHLT sindwiederum Fremdschlüssel





• Beziehung ist allgemeiner Art (k:m:n-Beziehung): allePrimärschlüssel bilden zusammen den Schlüssel1. EMPFIEHLT = WName,Bezeichnung,Name2. GERICHT = Bezeichnung,Beilage3. WEIN = Farbe,WName,Jahrgang,Restsüße4. KRITIKER = Name,Organisation






• Beziehung ist allgemeiner Art (k:m:n-Beziehung): allePrimärschlüssel bilden zusammen den Schlüssel1. EMPFIEHLT = WName,Bezeichnung,Name2. GERICHT = Bezeichnung,Beilage3. WEIN = Farbe,WName,Jahrgang,Restsüße4. KRITIKER = Name,Organisation



Übersicht über die Transformationen

ER-Konzept wird abgebildet auf relationales KonzeptEntity-Typ Ei Relationenschema RiAttribute von Ei Attribute von RiPrimärschlüssel Pi Primärschlüssel PiBeziehungstyp Relationenschema

Attribute: P1, P2dessen Attribute weitere Attribute1 : n P2 wird Primärschlüssel der Beziehung1 : 1 P1 und P2 werden Schlüssel der Beziehungm : n P1 ∪ P2 wird Primärschlüssel der Beziehungist-Beziehung R1 erhält zusätzlichen Schlüssel P2

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


Zusammenfassung

• Phasen des Datenbankentwurfs

• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell• ER-Erweiterungen: Spezialisierung, Generalisierung,Partitionierung

• weitere Entwurfsschritte


Zusammenfassung

• Phasen des Datenbankentwurfs• Datenbankmodell, Datenbankschema, Datenbank(instanz)

• Entity-Relationship-Modell• ER-Erweiterungen: Spezialisierung, Generalisierung,Partitionierung



Zusammenfassung

• Phasen des Datenbankentwurfs• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell

• ER-Erweiterungen: Spezialisierung, Generalisierung,Partitionierung



Zusammenfassung

• Phasen des Datenbankentwurfs• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell• ER-Erweiterungen: Spezialisierung, Generalisierung,Partitionierung



Zusammenfassung

• Phasen des Datenbankentwurfs• Datenbankmodell, Datenbankschema, Datenbank(instanz)• Entity-Relationship-Modell• ER-Erweiterungen: Spezialisierung, Generalisierung,Partitionierung



Kontrollfragen

• Welche Schritte umfasst derDatenbankentwurfsprozess?

• Welche Forderungen müssen dieAbbildungen (Transformationen) zwischenden einzelnen Entwurfsschritten erfüllen?Warum?

• Wie werden die Konzepte des ER-Modellsauf die des Relationenmodell abgebildet?

• Wie werden die verschiedenenKardinalitäten von Beziehungstypen beider Abbildung berücksichtigt?


Kontrollfragen






Kontrollfragen






Kontrollfragen






Teil V

Relationaler Entwurf


1. Zielmodell des logischen Entwurfs

2. Relationaler DB-Entwurf

3. Normalformen

4. Transformationseigenschaften

5. Weitere Abhängigkeiten





3. Normalformen







3. Normalformen







3. Normalformen







3. Normalformen





• Kenntnisse zur Verfeinerung desrelationalen Entwurfs

• Verständnis der Normalformen• Methodik und Verfahren zurNormalisierung




• Verständnis der Normalformen

• Methodik und Verfahren zurNormalisierung




• Verständnis der Normalformen• Methodik und Verfahren zurNormalisierung


Zielmodell des logischen Entwurfs

Relationenmodell

WEINE WeinID Name Farbe Jahrgang Weingut1042 La Rose … Rot 1998 Château …2168 Creek Shiraz Rot 2003 Creek3456 Zinfandel Rot 2004 Helena2171 Pinot Noir Rot 2001 Creek3478 Pinot Noir Rot 1999 Helena4711 Riesling … Weiß 1999 Müller4961 Chardonnay Weiß 2002 Bighorn



Begriffe des Relationenmodells

Begriff Informale BedeutungAttribut Spalte einer TabelleWertebereich mögliche Werte eines Attributs (auch Do-

mäne)Attributwert Element eines WertebereichsRelationenschema Menge von AttributenRelation Menge von Zeilen einer TabelleTupel Zeile einer TabelleDatenbankschema Menge von RelationenschemataDatenbank Menge von Relationen (Basisrelationen)


Begriffe des Relationenmodells /2

Begriff Informale BedeutungSchlüssel minimale Menge von Attributen, deren

Werte ein Tupel einer Tabelle eindeutigidentifizieren

Primärschlüssel ein beim Datenbankentwurf ausge-zeichneter Schlüssel

Fremdschlüssel Attributmenge, die in einer anderenRelation Schlüssel ist

Fremdschlüsselbedingung alle Attributwerte des Fremdschlüsselstauchen in der anderen Relation alsWerte des Schlüssels auf


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 Aw ∈ dom(A): Attributwert für A


Formalisierung Relationenmodell /2

• Relationenschemata und Relationen• R ⊆ U : Relationenschema• Relation r über R = A1, . . . ,An (kurz: r(R)) ist endlicheMenge von Abbildungen t : R −→

∪mi=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)


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


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

• Name, Jahrgang, Weingut und• WeinID für WEINE

• Primattribut: Element eines Schlüssels• Primärschlüssel: ausgezeichneter Schlüssel• Oberschlüssel oder Superkey: jede Obermenge einesSchlüssels (= identifizierende Attributmenge)

• Fremdschlüssel: X(R1) → Y(R2)

t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2




∀t1, t2 ∈ r [t1 = t2 =⇒ ∃B ∈ K : t1(B) = t2(B)]• Schlüssel: ist minimale identifizierende Attributmenge




t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2





• Name, Jahrgang, Weingut und

• WeinID für WEINE• Primattribut: Element eines Schlüssels• Primärschlüssel: ausgezeichneter Schlüssel• Oberschlüssel oder Superkey: jede Obermenge einesSchlüssels (= identifizierende Attributmenge)


t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2








t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2






• Primattribut: Element eines Schlüssels

• Primärschlüssel: ausgezeichneter Schlüssel• Oberschlüssel oder Superkey: jede Obermenge einesSchlüssels (= identifizierende Attributmenge)


t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2






• Primattribut: Element eines Schlüssels• Primärschlüssel: ausgezeichneter Schlüssel

• Oberschlüssel oder Superkey: jede Obermenge einesSchlüssels (= identifizierende Attributmenge)


t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2








t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2








t(X)|t ∈ r1 ⊆ t(Y)|t ∈ r2


Relationaler DB-Entwurf

Relationaler DB-Entwurf: Überblick

• Verfeinern des logischen Entwurfs

• Ziel: Vermeidung von Redundanzen durch Aufspalten vonRelationenschemata, ohne gleichzeitig

• semantische Informationen zu verlieren(Abhängigkeitstreue)

• die Möglichkeit zur Rekonstruktion der Relationen zuverlieren (Verbundtreue)

• Redundanzvermeidung durch Normalformen (s.u.)



• Verfeinern des logischen Entwurfs• Ziel: Vermeidung von Redundanzen durch Aufspalten vonRelationenschemata, ohne gleichzeitig























Relation WEINE mit Redundanzen

WeinID Name … Weingut Anbaugebiet Region1042 La Rose Gr. Cru … Ch. La Rose Saint-Emilion Bordeaux2168 Creek Shiraz … Creek Barossa Valley Südaustralien3456 Zinfandel … Helena Napa Valley Kalifornien2171 Pinot Noir … Creek Barossa Valley Südaustralien3478 Pinot Noir … Helena Napa Valley Kalifornien4711 Riesling Res. … Müller Rheingau Hessen4961 Chardonnay … Bighorn Napa Valley Kalifornien


Redundanzen

• Redundanzen in Basisrelationen aus mehreren Gründenunerwünscht:

• Redundante Informationen belegen unnötigenSpeicherplatz

• Änderungsoperationen auf Basisrelationen mitRedundanzen nur schwer korrekt umsetzbar: wenn eineInformation redundant vorkommt, muss eine Änderungdiese Information in allen ihren Vorkommen verändern

• mit normalen relationalen Änderungsoperationen und denin relationalen Systemen vorkommenden lokalenIntegritätsbedingungen (Schlüsseln) nur schwer realisierbar


Redundanzen






Redundanzen






Redundanzen






Änderungsanomalien

• Einfügen in die redundanzbehaftete WEINE-Relation:insert into WEINE (WeinID, Name, Farbe,

Jahrgang, Weingut, Anbaugebiet, Region)values (4711, 'Chardonnay', 'Weiß', 2004,

'Helena', 'Rheingau', 'Kalifornien')

• WeinID 4711 bereits anderem Wein zugeordnet: verletzt FDWeinID→Name

• Weingut Helena war bisher im Napa Valley angesiedelt:verletzt FD Weingut→Anbaugebiet

• Rheingau liegt nicht in Kalifornien: verletzt FDAnbaugebiet→Region

• auch update- und delete-Anomalien


Änderungsanomalien

• Einfügen in die redundanzbehaftete WEINE-Relation:insert into WEINE (WeinID, Name, Farbe,

Jahrgang, Weingut, Anbaugebiet, Region)values (4711, 'Chardonnay', 'Weiß', 2004,

'Helena', 'Rheingau', 'Kalifornien')

• WeinID 4711 bereits anderem Wein zugeordnet: verletzt FDWeinID→Name

• Weingut Helena war bisher im Napa Valley angesiedelt:verletzt FD Weingut→Anbaugebiet

• Rheingau liegt nicht in Kalifornien: verletzt FDAnbaugebiet→Region

• auch update- und delete-Anomalien


Funktionale Abhängigkeiten

Funktionale Abhängigkeit zwischen Attributemengen X und YWenn in jedem Tupel der Relation der Attributwert unter denX-Komponenten den Attributwert unter den Y-Komponentenfestlegt.

• 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 functionaldependency): X→Y

• Beispiel:WeinID →Name, WeingutAnbaugebiet→Region

• aber nicht: Weingut→Name


Funktionale Abhängigkeiten

Funktionale Abhängigkeit zwischen Attributemengen X und YWenn in jedem Tupel der Relation der Attributwert unter denX-Komponenten den Attributwert unter den Y-Komponentenfestlegt.

• 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 functionaldependency): X→Y

• Beispiel:WeinID →Name, WeingutAnbaugebiet→Region

• aber nicht: Weingut→Name


Schlüssel als Spezialfall

• für Beispiel auf Folie 5-11WeinID→Name, Farbe, Jahrgang, Weingut,

Anbaugebiet, Region• Immer: WeinID→WeinID,dann gesamtes Schema auf rechter Seite

• Wenn linke Seite minimal: Schlüssel• Formal: Schlüssel X liegt vor, wenn für RelationenschemaR FD X→R gilt und X minimal

Ziel des Datenbankentwurfsalle gegebenen funktionalen Abhängigkeiten inSchlüsselabhängigkeiten umformen, ohne dabei semantischeInformation zu verlieren


Schlüssel als Spezialfall

• für Beispiel auf Folie 5-11WeinID→Name, Farbe, Jahrgang, Weingut,

Anbaugebiet, Region• Immer: WeinID→WeinID,dann gesamtes Schema auf rechter Seite

• Wenn linke Seite minimal: Schlüssel• Formal: Schlüssel X liegt vor, wenn für RelationenschemaR FD X→R gilt und X minimal

Ziel des Datenbankentwurfsalle gegebenen funktionalen Abhängigkeiten inSchlüsselabhängigkeiten umformen, ohne dabei semantischeInformation zu verlieren


Ableitung von FDs

r A B Ca1 b1 c1a2 b1 c1a3 b2 c1a4 b1 c1

• Tabelle genügt A→B und B→C

• dann gilt auch A→C• nicht ableitbar C→A oder C→BFormalisierung im nächsten Abschnitt!


Ableitung von FDs


• Tabelle genügt A→B und B→C• dann gilt auch A→C

• nicht ableitbar C→A oder C→BFormalisierung im nächsten Abschnitt!


Ableitung von FDs


• Tabelle genügt A→B und B→C• dann gilt auch A→C• nicht ableitbar C→A oder C→B

Formalisierung im nächsten Abschnitt!


Ableitung von FDs


• Tabelle genügt A→B und B→C• dann gilt auch A→C• nicht ableitbar C→A oder C→B

Formalisierung im nächsten Abschnitt!


Ableitung von FDs


• Tabelle genügt A→B und B→C• dann gilt auch A→C• nicht ableitbar C→A oder C→BFormalisierung im nächsten Abschnitt!


Normalformen

Schemaeigenschaften

• Relationenschemata, Schlüssel und Fremdschlüssel sowählen, dass

1. alle Anwendungsdaten aus den Basisrelationen hergeleitetwerden können,

2. nur semantisch sinnvolle und konsistenteAnwendungsdaten dargestellt werden können und

3. die Anwendungsdaten möglichst nicht-redundantdargestellt werden.

• Hier: Forderung 3

• Redundanzen innerhalb einer Relation: Normalformen• globale Redundanzen: Minimalität


Schemaeigenschaften

• Relationenschemata, Schlüssel und Fremdschlüssel sowählen, dass1. alle Anwendungsdaten aus den Basisrelationen hergeleitetwerden können,






Schemaeigenschaften







Schemaeigenschaften







Schemaeigenschaften







Schemaeigenschaften




• Hier: Forderung 3• Redundanzen innerhalb einer Relation: Normalformen

• globale Redundanzen: Minimalität


Schemaeigenschaften




• Hier: Forderung 3• Redundanzen innerhalb einer Relation: Normalformen• globale Redundanzen: Minimalität


Normalformen

• legen Eigenschaften von Relationenschemata fest

• verbieten bestimmte Kombinationen von funktionalenAbhängigkeiten in Relationen

• sollen Redundanzen und Anomalien vermeiden


Normalformen

• legen Eigenschaften von Relationenschemata fest• verbieten bestimmte Kombinationen von funktionalenAbhängigkeiten in Relationen



Normalformen

• legen Eigenschaften von Relationenschemata fest• verbieten bestimmte Kombinationen von funktionalenAbhängigkeiten in Relationen



Erste Normalform

• erlaubt nur atomare Attribute in den Relationenschemata,d.h. als Attributwerte sind Elemente von Standard-Datentypen wie integer oder string erlaubt, aberkeine Konstruktoren wie array oder set

• Nicht in 1NF:

Weingut Anbaugebiet Region WNameCh. La Rose Saint-Emilion Bordeaux La Rose Grand CruCreek Barossa Valley Südaustralien Creek Shiraz, Pinot NoirHelena Napa Valley Kalifornien Zinfandel, Pinot NoirMüller Rheingau Hessen Riesling ReserveBighorn Napa Valley Kalifornien Chardonnay


Erste Normalform


• Nicht in 1NF:



Erste Normalform


• Nicht in 1NF:



Erste Normalform


• Nicht in 1NF:



Erste Normalform /2

• in erster Normalform:

Weingut Anbaugebiet Region WNameCh. La Rose Saint-Emilion Bordeaux La Rose Grand CruCreek Barossa Valley Südaustralien Creek ShirazCreek Barossa Valley Südaustralien Pinot NoirHelena Napa Valley Kalifornien ZinfandelHelena Napa Valley Kalifornien Pinot NoirMüller Rheingau Hessen Riesling ReserveBighorn Napa Valley Kalifornien Chardonnay


Zweite Normalform

• partielle Abhängigkeit liegt vor, wenn ein Attributfunktional schon von einem Teil des Schlüssels abhängt

Name Weingut Farbe Anbaugebiet Region PreisLa Rose … Ch. La Rose Rot Saint-Emilion Bordeaux 39.00Creek Shiraz Creek Rot Barossa Valley Südaustralien 7.99Pinot Noir Creek Rot Barossa Valley Südaustralien 10.99Zinfandel Helena Rot Napa Valley Kalifornien 5.99Pinot Noir Helena Rot Napa Valley Kalifornien 19.99Riesling Reserve Müller Weiß Rheingau Hessen 14.99Chardonnay Bighorn Weiß Napa Valley Kalifornien 9.90

f1: Name, Weingut→Preisf2: Name →Farbef3: Weingut →Anbaugebiet, Regionf4: Anbaugebiet →Region


Zweite Normalform





Zweite Normalform





Zweite Normalform

Zweite NormalformZweite Normalform eliminiert derartige partielleAbhängigkeiten bei Nichtschlüsselattributen


Eliminierung partieller Abhängigkeiten

Schlüssel K

abhängigesAttribut ATeil des

Schlüssels XSattler/Saake | VL Datenbanksysteme | 22. September 2019 5–23

Zweite Normalform /2

• Beispielrelation in 2NFR1(Name, Weingut, Preis)R2(Name, Farbe)R3(Weingut, Anbaugebiet, Region)


Dritte Normalform

• eliminiert (zusätzlich) transitive Abhängigkeiten

• etwa Weingut → Anbaugebiet und Anbaugebiet →Region in Relation auf Folie 5-21

• man beachte: 3NF betrachtet nur Nicht-Schlüsselattributeals Endpunkt transitiver Abhängigkeiten


Dritte Normalform

• eliminiert (zusätzlich) transitive Abhängigkeiten• etwa Weingut → Anbaugebiet und Anbaugebiet →Region in Relation auf Folie 5-21



Dritte Normalform

• eliminiert (zusätzlich) transitive Abhängigkeiten• etwa Weingut → Anbaugebiet und Anbaugebiet →Region in Relation auf Folie 5-21



Eliminierung transitiver Abhängigkeiten

Schlüssel K

abhängigesAttribut AAttributmenge X


Dritte Normalform /2

• transitive Abhängigkeit in R3, d.h. R3 verletzt 3NF• Beispielrelation in 3NFR3_1(Weingut, Anbaugebiet)R3_2(Anbaugebiet, Region)


Dritte Normalform: formal

Relationenschema R, X ⊆ R und F ist eine FD-Menge über R

Dritte Normalform• A ∈ R heißt transitiv abhängig von X bezüglich F genaudann, wenn es ein Y ⊆ R gibt mit X→Y, Y→X, Y→A,A ∈ XY

• erweitertes Relationenschema R = (R,K) ist in 3NFbezüglich F genau dann, wenn ∃A ∈ R:

• A ist Nicht-Primattribut in R• ∧ A transitiv abhängig von einem K ∈ K bezüglich Fi.

• Nicht-Primattribut: A ist in keinem Schlüssel von Renthalten


Boyce-Codd-Normalform

Verschärfung der 3NF: Eliminierung transitiver Abhängigkeitenauch zwischen PrimattributenName Weingut Händler PreisLa Rose Grand Cru Château La Rose Weinkontor 39.90Creek Shiraz Creek Wein.de 7.99Pinot Noir Creek Wein.de 10.99Zinfandel Helena GreatWines.com 5.99Pinot Noir Helena GreatWines.com 19.99Riesling Reserve Müller Weinkeller 19.99Chardonnay Bighorn Wein-Dealer 9.90



Name, Weingut→PreisWeingut →HändlerHändler →Weingut

• Schlüsselkandidaten: Name, Weingut und Name,Händler

• in 3NF, nicht jedoch in BCNF












Boyce-Codd-Normalform /2

• erweitertes Relationenschema R = (R,K), FD-Menge F• BCNF formal:


∃A ∈ R : A transitiv abhängig von einem K ∈ K bezüglich F.

• Schema in BCNF:WEINE(Name, Weingut, Preis)WEINHANDEL(Weingut, Händler)

• BCNF kann jedoch Abhängigkeitstreue verletzen, daher oftnur bis 3NF


Boyce-Codd-Normalform /2

• erweitertes Relationenschema R = (R,K), FD-Menge F• BCNF formal:


∃A ∈ R : A transitiv abhängig von einem K ∈ K bezüglich F.

• Schema in BCNF:WEINE(Name, Weingut, Preis)WEINHANDEL(Weingut, Händler)

• BCNF kann jedoch Abhängigkeitstreue verletzen, daher oftnur bis 3NF


Minimalität

• Global Redundanzen vermeiden

• andere Kriterien (wie Normalformen) mit möglichst wenigSchemata 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′


Minimalität

• Global Redundanzen vermeiden• andere Kriterien (wie Normalformen) mit möglichst wenigSchemata erreichen


S = (AB, A), (BC, B)

S′ = (AB, A), (BC, B), (AC, A)

Redundanzen in S′


Minimalität


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


Minimalität



S = (AB, A), (BC, B)

S′ = (AB, A), (BC, B), (AC, A)

Redundanzen in S′


Schemaeigenschaften

Kennung Schemaeigenschaft Kurzcharakteristik1NF nur atomare Attribute2NF keine partielle Abhängigkeit eines

Nicht-Primattributes von einemSchlüssel

S1 3NF keine transitive Abhängigkeit ei-nes Nicht-Primattributes von einemSchlüssel

BCNF keine transitive Abhängigkeit einesAttributes von einem Schlüssel

S2 Minimalität minimale Anzahl von Relationen-schemata, die die anderen Eigen-schaften erfüllt


Transformationseigenschaften


• Bei einer Zerlegung einer Relation in mehrere Relationenist darauf zu achten, dass

1. nur semantisch sinnvolle und konsistenteAnwendungsdaten dargestellt (Abhängigkeitstreue) und

2. alle Anwendungsdaten aus den Basisrelationen hergeleitetwerden können (Verbundtreue)



• Bei einer Zerlegung einer Relation in mehrere Relationenist darauf zu achten, dass1. nur semantisch sinnvolle und konsistenteAnwendungsdaten dargestellt (Abhängigkeitstreue) und




• Bei einer Zerlegung einer Relation in mehrere Relationenist darauf zu achten, dass1. nur semantisch sinnvolle und konsistenteAnwendungsdaten dargestellt (Abhängigkeitstreue) und



Abhängigkeitstreue

• Abhängigkeitstreue: eine Menge von Abhängigkeiten kannäquivalent in eine zweite Menge von Abhängigkeitentransformiert werden

• spezieller: in die Menge der Schlüsselabhängigkeiten, dadiese vom Datenbanksystem effizient überprüft werdenkann

• die Menge der Abhängigkeiten soll äquivalent zu derMenge der Schlüsselbedingungen im resultierendenDatenbankschema sein

• Äquivalenz sichert zu, dass mit denSchlüsselabhängigkeiten semantisch genau die gleichenIntegritätsbedingungen ausgedrückt werden wie mit denfunktionalen oder anderen Abhängigkeiten vorher


Abhängigkeitstreue






Abhängigkeitstreue






Abhängigkeitstreue






Abhängigkeitstreue: Beispiel

• Zerlegung des Relationenschemas WEINE (Folie 5-21) in3NF:

R1(Name, Weingut, Preis)R2(Name, Farbe)R3_1(Weingut, Anbaugebiet)R3_2(Anbaugebiet, Region)

mit SchlüsselabhängigkeitenName, Weingut→PreisName →FarbeWeingut →AnbaugebietAnbaugebiet →Region

• äquivalent zu FDs f1 . . . f4 (Folie 5-21) abhängigkeitstreu


Abhängigkeitstreue: Beispiel /2

• Postleitzahl-Struktur der Deutschen PostADRESSE(PLZ (P), Ort (O), Strasse(S),

Hausnummer(H))und funktionalen Abhängigkeiten F

OSH→P, P→O• Schlüsselkandidaten: OSH und PSH 3NF• nicht in BCNF (wegen PSH→P→O): daher Zerlegung vonADRESSE

• aber: jede Zerlegung würde OSH→P zerstören• Menge der sich ergebenden FDs ist nicht äquivalent zu F,die Zerlegung damit nicht abhängigkeitstreu


Abhängigkeitstreue formal

• lokal erweitertes DatenbankschemaS = (R1,K1), . . . , (Rp,Kp); ein Menge F lokalerAbhängigkeiten

AbhängigkeitstreueS charakterisiert vollständig F (oder: ist abhängigkeitstreubezüglich F) genau dann, wenn

F ≡ K→R | (R,K) ∈ S, K ∈ K


Verbundtreue

• zur Erfüllung des Kriteriums der Normalformen müssenRelationenschemata teilweise in kleinereRelationenschemata zerlegt werden

• für Beschränkung auf „sinnvolle“ Zerlegungen giltForderung, dass die Originalrelation wieder aus denzerlegten Relationen mit dem natürlichen Verbundzurückgewonnen werden kann Verbundtreue


Verbundtreue

• zur Erfüllung des Kriteriums der Normalformen müssenRelationenschemata teilweise in kleinereRelationenschemata zerlegt werden

• für Beschränkung auf „sinnvolle“ Zerlegungen giltForderung, dass die Originalrelation wieder aus denzerlegten Relationen mit dem natürlichen Verbundzurückgewonnen werden kann Verbundtreue


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


Verbundtreue Dekomposition

• Originalrelation:

A B C1 2 34 2 3

• Dekomposition:

A B1 24 2

B C2 3

• Verbund (verbundtreu):

A B C1 2 34 2 3


Nicht verbundtreue Dekomposition

• Originalrelation:A B C1 2 34 2 5

• Dekomposition:A B1 24 2

B C2 32 5

• Verbund (nicht verbundtreu):A B C1 2 34 2 51 2 54 2 3


Verbundtreue formal

VerbundtreueDie Dekomposition einer Attributmenge X in X1, . . . , Xp mitX =

∪pi=1 Xi heißt verbundtreu (π ▷◁-treu, lossless) bezüglich

einer Menge von Abhängigkeiten F über X genau dann, wenn

∀r ∈ SATX(F) : πX1(r) ▷◁ · · · ▷◁ πXp(r) = r

gilt.

• einfaches Kriterium für Verbundtreue bei Dekompositionin zwei Relationenschemata: Dekomposition von X in X1und X2 ist verbundtreu bzgl. F, wenn X1 ∩ X2→X1 ∈ F+ oderX1 ∩ X2→X2 ∈ F+



Kennung Transformationseigenschaft KurzcharakteristikT1 Abhängigkeitstreue alle gegebenen Abhängigkeiten

sind durch Schlüssel repräsentiertT2 Verbundtreue Originalrelationen können durch

den Verbund der Basisrelationenwiedergewonnen werden


Weitere Abhängigkeiten


• Mehrwertige Abhängigkeit (kurz: MVD)

• innerhalb einer Relation r wird einem Attributwert von Xeine Menge von Y-Werten zugeordnet, unabhängig von denWerten der restlichen Attribute Vierte Normalform

• Verbundabhängigkeit (kurz: JD)

• R kann ohne Informationsverlust in R1, . . . ,Rp aufgetrenntwerden: ▷◁ [R1, . . . ,Rp]

• Inklusionsabhängigkeit (kurz: IND)

• auf der rechten Seite einer Fremdschlüsselabhängigkeitnicht unbedingt der Primärschlüssel einer Relation



• Mehrwertige Abhängigkeit (kurz: MVD)• innerhalb einer Relation r wird einem Attributwert von Xeine Menge von Y-Werten zugeordnet, unabhängig von denWerten der restlichen Attribute Vierte Normalform















• Verbundabhängigkeit (kurz: JD)• R kann ohne Informationsverlust in R1, . . . ,Rp aufgetrenntwerden: ▷◁ [R1, . . . ,Rp]













• Inklusionsabhängigkeit (kurz: IND)• auf der rechten Seite einer Fremdschlüsselabhängigkeitnicht unbedingt der Primärschlüssel einer Relation


Mehrwertige Abhängigkeiten

• Folge der 1NF: Mehrwertige Abhängigkeiten erzeugenRedundanz:

WEIN_EMPFEHLUNG WName Jahrgang GerichtChardonnay 2002 GeflügelChardonnay 2002 FischChardonnay 2003 FischChardonnay 2003 GeflügelShiraz 2003 WildShiraz 2003 LammShiraz 2004 WildShiraz 2004 Lamm


Mehrwertige Abhängigkeiten /2

• eine (oder mehrere) Gruppe von Attributwerten ist voneinem Schlüssel bestimmt, unabhängig von anderenAttributen

• hier: Menge von Jahrgängen plus Menge von GerichtenWName →→ Jahrgang, WName →→ Gericht

• Resultat: Redundanz durch Bildung aller Kombinationen


Mehrwertige Abhängigkeiten formal

• Relation r(R) mit X, Y ⊆ R, Z := R− (X ∪ Y) genügt der MVDX→→Y gdw.

∀t1, t2 ∈ r : [(t1 = t2 ∧ t1(X) = t2(X))=⇒ ∃t3 ∈ r : t3(X) = t1(X) ∧ t3(Y) = t1(Y) ∧

t3(Z) = t2(Z)]• Relation r(R) mit R = XYZ und X→→Y:

• wenn (x1, y1, z1) ∈ r und (x1, y2, z2) ∈ r• dann auch: (x1, y1, z2) ∈ r und (x1, y2, z1) ∈ r

• Bsp.: wegen (’Chardonnay’, 2002, ’Geflügel’) und(’Chardonnay’, 2003, ’Fisch’) müssen auch(’Chardonnay’, 2002, ’Fisch’) und(’Chardonnay’, 2003, ’Geflügel’) enthalten sein


Mehrwertige Abhängigkeiten und 4NF

• wünschenswerte Schemaeigenschaft bei Vorliegen vonMVDs: vierte Normalform

• fordert die Beseitigung derartiger Redundanzen: keinezwei MVDs zwischen Attributen einer Relation

• Beispiel von Folie 5-46 verletzt diese Forderung• Prinzip

• Elimination der rechten Seite einer der beidenmehrwertigen Abhängigkeiten,

• linke Seite mit dieser rechten Seite in neue Relationkopiert












• Beispiel von Folie 5-46 verletzt diese Forderung

• Prinzip

























Vierte Normalform

WEIN_JAHR WName JahrgangChardonnay 2002Chardonnay 2003Shiraz 2003Shiraz 2004

WEIN_GERICHT WName GerichtChardonnay GeflügelChardonnay FischShiraz WildShiraz Lamm


Vierte Normalform formal

• Relationenschema R mit X, Y ⊆ R, MVD-Menge M über R• MVD X→→Y heißt trivial genau dann, wenn Y ⊆ X oderX ∪ Y = R

Vierte Normalformerweitertes Relationenschema R = (R,K) ist in vierterNormalform (4NF) bezüglich M genau dann, wenn für alleX→→Y ∈ M+ gilt:

X→→Y ist trivial oder X ⊇ K für ein K ∈ K.


Nichttriviale MVDs

• Erweiterung der Relation WEIN_JAHR von Folie 5-50 umAttribute Farbe und Restsüße

• MVD WName→→Jahrgang ist nicht mehr trivial• Zerlegung:

WEIN_JAHR1(WName, Jahrgang)WEIN_JAHR2(WName, Farbe, Restsüße)


Nichttriviale MVDs


• MVD WName→→Jahrgang ist nicht mehr trivial

• Zerlegung:WEIN_JAHR1(WName, Jahrgang)WEIN_JAHR2(WName, Farbe, Restsüße)


Nichttriviale MVDs


• MVD WName→→Jahrgang ist nicht mehr trivial• Zerlegung:

WEIN_JAHR1(WName, Jahrgang)WEIN_JAHR2(WName, Farbe, Restsüße)


Zusammenfassung

• funktionale Abhängigkeiten• Normalformen (1NF - 3NF, BCNF)• Abhängigkeitstreue und Verbundtreue• Entwurfsverfahren• mehrwertige Abhängigkeiten


Kontrollfragen

• Welches Ziel hat die Normalisierungrelationaler Schemata?

• Welche Eigenschaften relationalerSchemata werden bei den Normalformenberücksichtigt?

• Was unterscheidet 3NF und BCNF?• Was fordern Abhängigkeitstreue undVerbundtreue?


Kontrollfragen





Kontrollfragen



• Was unterscheidet 3NF und BCNF?

• Was fordern Abhängigkeitstreue undVerbundtreue?


Kontrollfragen





Teil VI

Relationale Theorie

Relationale Theorie

1. Formalisierung

2. Rechnen mit FDs

3. Mehr zu Normalformen

4. Entwurfsverfahren


Relationale Theorie

1. Formalisierung

2. Rechnen mit FDs




Relationale Theorie

1. Formalisierung

2. Rechnen mit FDs




Relationale Theorie

1. Formalisierung

2. Rechnen mit FDs





• Vertiefte Kenntnisse der theoretischenGrundlagen des relationalen Entwurfs

• Korrektheit der Normalisierung• Details des Syntheseverfahrens




• Korrektheit der Normalisierung

• Details des Syntheseverfahrens




• Korrektheit der Normalisierung• Details des Syntheseverfahrens


Formalisierung

Wiederholung: 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 Aw ∈ dom(A): Attributwert für A


Wiederholung: Formalisierung Relationenmodell /2

• Relationenschemata und Relationen• R ⊆ U : Relationenschema• Relation r über R = A1, . . . ,An (kurz: r(R)) ist endlicheMenge von Abbildungen t : R −→

∪mi=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)


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


Rechnen mit FDs

Wiederholung: Ableitung von FDs


• genügt A→B und B→C• dann gilt auch A→C• nicht ableitbar C→A oder C→B


Formale Ableitung von FDs

• Gilt für f über R SATR(F) ⊆ SATR(f), dann impliziert F die FDf (kurz: F |= f)

• obiges Beispiel:

F = A→B,B→C |= A→C

• Hüllenbildung: Ermittlung aller funktionalenAbhängigkeiten, die aus einer gegebenen FD-Mengeabgeleitet werden können

• Hülle F+R := f | (f FD über R) ∧ F |= f• Beispiel:

A→B,B→C+ = A→B,B→C,A→C,AB→C,A→BC, . . . ,AB→AB, . . .


Ableitungsregeln

F1 Reflexivität X ⊇ Y =⇒ X→YF2 Augmentation X→Y =⇒ XZ→YZ sowie XZ→YF3 Transitivität X→Y, Y→Z =⇒ X→ZF4 Dekomposition X→YZ =⇒ X→YF5 Vereinigung X→Y, X→Z =⇒ X→YZF6 Pseudotransitivität X→Y,WY→Z =⇒ WX→Z

F1-F3 bekannt als Armstrong-Axiome (sound, complete)• gültig (sound): Regeln leiten keine FDs ab, die logischnicht impliziert

• vollständig (complete): alle implizierten FDs werdenabgeleitet

• unabhängig (independent) oder auch bzgl. ⊆ minimal:keine Regel kann weggelassen werden


Beweis: F1

• 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


Beweis: F2

• Annahme: X→Y gilt in r(R), jedoch nicht: XZ→YZ• dann müssen zwei Tupel t1, t2 ∈ r(R) existieren, so dassgilt(1) t1(X) = t2(X)(2) t1(Y) = t2(Y)(3) t1(XZ) = t2(XZ)(4) t1(YZ) = t2(YZ)

• Widerspruch wegen t1(Z) = t2(Z) aus (1) und (3), worausfolgt: t1(YZ) = t2(YZ) (in Verbindung mit (4))


Beweis: F3

• Annahme: in r(R) gelten:(1) X→Y(2) Y→Z

• demzufolge für zwei beliebige Tupel t1, t2 ∈ r(R) mitt1(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


Alternative Regelmenge

• B-Axiome oder RAP-Regeln

R Reflexivität =⇒ X→XA Akkumulation X→YZ, Z→AW =⇒ X→YZAP Projektivität X→YZ =⇒ X→Y

• Regelmenge ist vollständig, da Armstrong-Axiome darausabgeleitet werden können


Membership-Problem

Membership-ProblemKann eine bestimmte FD X→Y aus der vorgegebenen MengeF abgeleitet werden, d.h. wird sie von F impliziert?

Membership-Problem: „X→Y ∈ F+ ?“

• Hülle einer Attributmenge X bzgl. F istX+F := A | X→A ∈ F+

• Membership-Problem kann durch das modifizierteProblem

Membership-Problem (2): „Y ⊆ X+F ?“in linearer Zeit gelöst werden


Algorithmus Closure: Ermittlung der Hülle X+F von X bzgl. F

Closure(F, X):X+ := Xrepeat

X+ := X+ /* R-Regel */forall FDs Y→Z ∈ F

if Y ⊆ X+ then X+ := X+ ∪ Z /* A-Regel */until X+ = X+

return X+

Member(F, X→Y): /* Test auf X→Y ∈ F+ */return Y ⊆Closure(F, X) /* P-Regel */


Algorithmus Closure: Beispiel

A→C ∈ A→B︸︷︷︸f1

,B→C︸︷︷︸f2

+?

• Member(f1, f2,A→C)• C ⊆ Closure(f1, f2,A)• X+ ist initial A, schrittweises Hinzunehmen von B und C


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

• wichtige Entwurfsaufgabe: Finden einer Überdeckung, die• einerseits so wenig Attribute wie möglich in ihrenfunktionalen Abhängigkeiten und

• andererseits möglichst wenig funktionale Abhängigkeiteninsgesamt enthält

• verschiedene Formen von Überdeckung: nicht-redundant,reduziert, minimal, ringförmig


Reduktionsoperationen

• Ziel: Entfernen überflüssiger Attribute auf linker bzw.rechter Seite von FDs

• Linksreduktion: entfernt unwesentliche Attribute auf derlinken Seite einer FD

• Rechtsreduktion: entsprechend auf der rechten Seite• erw. Relationenschema R = (R,K), FD-Menge F über R, Aist ein Attribut aus R und X→Y eine FD aus F

Unwesentliche AttributeA heißt unwesentlich in X→Y bzgl. F, wenn

• X = AZ, Z = X =⇒ (F− X→Y) ∪ Z→Y ≡ F oder• Y = AW,W = Y =⇒ (F− X→Y) ∪ X→W ≡ F


Reduktionsoperationen /2

• A kann also aus der FD X→Y entfernt werden, ohne dasssich die Hülle von F ändert

• FD X→Y heißt linksreduziert, wenn kein Attribut in Xunwesentlich ist.

• FD X→Y heißt rechtsreduziert, wenn kein Attribut in Yunwesentlich ist.


Minimale Überdeckung

• Eine minimale Überdeckung ist eine Überdeckung, dieeine minimale Anzahl von FDs enthält

• Auswahl der kleinsten aller nicht-redundantenÜberdeckungen

• FD-Menge F heißt minimal gdw.

∀F′[F′ ≡ F⇒ |F| ≤ |F′|

]• Bestimmung etwa durch reduzierte Überdeckung mitanschließender Äquivalenzklassenbildung (später)


Reduzierte Überdeckung

ReducedCover(F):forall FD X→Y ∈ F /* Linksreduktion */

forall A ∈ X /* A unwesentlich ? */if Y ⊆ Closure(F, X− A)then ersetze X→Y durch (X− A)→Y in F

forall verbleibende FD X→Y ∈ F /* Rechtsreduktion */forall B ∈ Y /* B unwesentlich ? */

if B ⊆ Closure(F− X→Y ∪ X→(Y− B), X)then ersetze X→Y durch X→(Y− B)

Eliminiere FDs der Form X→∅Vereinige FDs der Form X→Y1, X→Y2, . . . zu X→Y1Y2 . . .return resultierende FDs


Reduzierte Überdeckung: Beispiel

• Geg.: FD-Menge

F = f1 : A→B, f2 : AB→C, f3 : A→C, f4 : B→A, f5 : C→E

1. Linksreduktion: bei FD f2 Attribut A streichen, daC ⊆ Closure(F, A) (wegen f3)

2. Rechtsreduktion: FD f3 durch A→ ersetzt, daC ⊆ Closure(A→B,B→C,A→,B→A, C→E, A)

3. Streichen von A→

• Ergebnis:

ReducedCover(F) = A→B,B→C,B→A, C→E


Äquivalenzklassen

• FDs mit äquivalenten linken Seiten werden zu einerÄquivalenzklasse zusammengefasst

• FDs X1→Y1 und X2→Y2 liegen in einer Äquivalenzklasse,wenn X1→X2 und X2→X1 gelten

• In einigen Fällen können nun zwei solche FDs in einerÄquivalenzklasse zu einer FD X→Y1Y2 zusammengefasstwerden

• Da die FDs einer Äquivalenzklasse in die FormX1→X2, X2→X3, . . . , Xn→X1, X1→Y überführt werdenkönnen, nennt man eine Überdeckung dieser Form eineringförmige Überdeckung


Äquivalenzklassen /2

• linke Seiten sind äquivalent, wenn sie sich gegenseitigfunktional bestimmen

• Relationenschema R mit Xi, Y ⊂ R, FD-MengeXi→Xj und Xi→Y mit 1 ≤ i, j ≤ n kann dargestellt werdendurch (X1, X2, . . . , Xn)→Y

X4

X3 X1

X2

Y


Mehr zu Normalformen

Wiederholung: Zweite Normalform















Formale Definition der zweiten Normalform

• Hinweis: partiell abhängiges Attribut stören nur, wenn eskein Primattribut ist

• 2NF formal: erweitertes Relationenschema R = (R,K),FD-Menge F über R

Zweite Normalform

• Y hängt partiell von X bzgl. F ab, wenn die FD X→Y nichtlinksreduziert ist

• Y hängt voll von X ab, wenn die FD X→Y linksreduziert ist• R ist in 2NF, wenn R in 1NF ist und jedes Nicht-Primattribut von R voll von jedem Schlüssel von Rabhängt


Entwurfsverfahren

Entwurfsverfahren: Ziele

• Universum U und FD-Menge F gegeben• lokal erweitertes DatenbankschemaS = (R1,K1), . . . , (Rp,Kp)berechnen mit

• T1: S charakterisiert vollständig F• S1: S ist in 3NF bezüglich F• T2: Dekomposition von U in R1, . . . ,Rp ist verbundtreubezüglich F

• S2: Minimalität, d.h. ∃S′ : S′ erfüllt T1, S1, T2 und |S′| < |S|


Entwurfsverfahren: Beispiel

• Datenbankschemata schlecht entworfen, wenn nur einsdieser vier Kriterien nicht erfüllt

• Beispiel: S = (AB, A), (BC, B), (AC, A) erfüllt T1, S1und T2 bezüglich F = A→B,B→C,A→Cin dritter Relation AC-Tupel redundant oder inkonsistent

• korrekt: S′ = (AB, A), (BC, B)


Dekomposition

• Geg.: initiales Universalrelationenschema R = (U ,K(F))mit allen Attributen und einer von erfassten FDs F über Rimplizierten 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, . . . von3NF-Relationenschemata


Dekomposition: Algorithmus

Decompose(R):Setze D := Rwhile R′ ∈ D, das 3NF nicht erfüllt

/* Finde Attribut A, das transitiv von K abhängig ist */if Schlüssel K mit K→Y, Y→K, Y→A,A ∈ KY then

/* Zerlege Relationenschema R bzgl. A */R1 := R− A , R2 := YAR1 := (R1,K) , R2 := (R2,K2 = Y)D := (D−R′) ∪ R1 ∪ R2

end ifend whilereturn D


Dekomposition: Beispiel

• initiales Relationenschema R = ABC• funktionale Abhängigkeiten F = A→B,B→C• Schlüssel K = A


Dekomposition: Beispiel /2

• initiales Relationenschema R mit Name, Weingut, Preis,Farbe, Anbaugebiet, Region

• funktionale Abhängigkeiten

f1: Name, Weingut→Preisf2: Name, Weingut→Weingutf3: Name, Weingut→Namef4: Name →Farbef5: Weingut →Anbaugebiet, Regionf6: Anbaugebiet →Region


Dekomposition: Bewertung

• Vorteile: 3NF, Verbundtreue• Nachteile: restliche Kriterien nicht, reihenfolgeabhängig,NP-vollständig (Schlüsselsuche)


Details zum Syntheseverfahren

• Prinzip: Synthese formt Original-FD-Menge F inresultierende Menge von Schlüsselabhängigkeiten G soum, dass F ≡ G gilt

• „Abhängigkeitstreue“ im Verfahren verankert• 3NF und Minimalität wird auch erreicht,reihenfolgeunabhängig

• Zeitkomplexität: quadratisch


Vergleich Dekomposition — Synthese

WEINE WeinID Name Farbe Jahrgang Weingut

ERZEUGER Weingut Anbaugebiet Region

WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn

Dekomposition Synthese

. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i

...



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i

...



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i



WINZER Weingut Name

. . .R1,K1 Rn,Kn . . .R1,K1 Rn,Kn


. . .R!1,K !

1 R!n,K !

n FDs F !!

FDs F !

FDs F

R,K

U, FDs F

!

!

!

!

!

!

i

...



Syntheseverfahren für Relationenschema R mit FDs F

Ges.: verlustfreie und abhängigkeitstreue Zerlegung inR1, . . .Rn, wobei alle Ri in 3NF sind

Synthesize(F):F := MinimalCover(F) /* Bestimme minimale Überdeckung */Bilde Äquivalenzklassen Ci von FDs aus F mit gleichen oder

äquivalenten linken Seiten, d.h. Ci = Xi→Ai1, Xi→Ai2, . . . Bilde zu jeder Äquivalenzklasse Ci ein Schema der Form

RCi = Xi ∪ Ai1 ∪ Ai2 ∪ . . . if keines der Schemata RCi enthält einen Schlüssel von Rthen erzeuge weiteres Relationenschema RK mit Attributen

aus R, die Schlüssel bildenreturn RK,RC1 ,RC2 , . . .


Synthese Beispiel

• FD-Menge

F = A→B,AB→C,A→C,B→A, C→E

• minimale Überdeckung

F = A→B,B→C,B→A, C→E

• Zusammenfassung zu Äquivalenzklassen

C1 = A→B,B→C,B→AC2 = C→E

• Syntheseergebnis

(ABC, A, B), (CE, C)


Erreichung der Verbundtreue

• Erreichen der Verbundtreue durch einfachen „Trick“:• Erweitern der Original-FD-Menge F um U→δ umDummy-Attribut δ

• δ wird nach Synthese entfernt• Beispiel: A→B, C→E

• Syntheseergebnis (AB, A), (CE, C) ist nicht verbundtreu,da Universalschlüssel in keinem Schema enthalten ist

• Dummy-FD ABCE→δ; reduziert auf AC→δ

• liefert drittes Relationenschema

(AC, AC)


Synthese: Beispiel

• Relationenschema und FD-Menge von Folie 5-21• Ablauf

1. minimale Überdeckung: Entfernen von f2, f3 sowie Regionin f5

2. Äquivalenzklassen:

C1 = Name,Weingut→PreisC2 = Name→FarbeC3 = Weingut→AnbaugebietC4 = Anbaugebiet→Region

3. Ableitung der Relationenschemata


Zusammenfassung

• Formalisierung des Relationenmodells und derfunktionalen Abhängigkeiten

• Algorithmen zur Normalisierung


Kontrollfragen

• Was muß beim Syntheseverfahrenbeachtet werden, um Spezialfälle wiezyklische Abhängigkeiten oder fehlendeSchlüssel zu berücksichtigen?


Teil VII

Die relationale Anfragesprache SQL


1. Aufbau von SQL-Anfragen

2. Erweiterungen des SFW-Blocks

3. Aggregatfunktionen und Gruppierungen

4. Rekursion






4. Rekursion






4. Rekursion






4. Rekursion



• Erweiterte Kenntnisse zum relationalenSQL

• Kenntnisse von Erweiterungen desSFW-Blocks

• Verständnis der Formulierung undAuswertung rekursiver Anfragen












Aufbau von SQL-Anfragen

Struktur einer SQL-Anfrage

-- Anfrageselect projektionslistefrom relationenliste[ where bedingung ]

select• Projektionsliste• arithmetische Operationen und Aggregatfunktionen

from• zu verwendende Relationen, evtl. Umbenennungen

where• Selektions-, Verbundbedingungen• Geschachtelte Anfragen (wieder ein SFW-Block)




















Auswahl von Tabellen: Die from-Klausel

• einfachste Form; hinter jedem Relationennamen kannoptional eine Tupelvariable stehen

select *from relationenliste

• Beispielanfrage:

select *from WEINE






select *from WEINE






select *from WEINE






select *from WEINE


Die select-Klausel

• Festlegung der Projektionsattribute

select [distinct] projektionslistefrom …

• mitprojektionsliste := attribut |

arithmetischer-ausdruck |aggregat-funktion [, …]


Die select-Klausel



• mit

projektionsliste := attribut |arithmetischer-ausdruck |aggregat-funktion [, …]


Die select-Klausel



• mitprojektionsliste := attribut |

arithmetischer-ausdruck |aggregat-funktion [, …]


Die select-Klausel: Projektionsliste

• Attribute der hinter from stehenden Relationen, optionalmit Präfix, der Relationennamen oder Namen derTupelvariablen angibt

• arithmetische Ausdrücke über Attributen dieserRelationen und passenden Konstanten

• Aggregatfunktionen über Attributen dieser Relationen












Die select-Klausel

• Spezialfall der Projektionsliste: *• liefert alle Attribute der Relation(en) aus dem from-Teil

select *from WEINE


Die select-Klausel

• Spezialfall der Projektionsliste: *• liefert alle Attribute der Relation(en) aus dem from-Teil

select *from WEINE


distinct eliminiert Duplikate

select Name from WEINE

• liefert die Ergebnisrelation als Multimenge:NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirPinot NoirRiesling ReserveChardonnay




• liefert die Ergebnisrelation als Multimenge:

NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirPinot NoirRiesling ReserveChardonnay




• liefert die Ergebnisrelation als Multimenge:NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirPinot NoirRiesling ReserveChardonnay


distinct eliminiert Duplikate /2


• ergibt Projektion aus der Relationenalgebra:NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirRiesling ReserveChardonnay


distinct eliminiert Duplikate /2


• ergibt Projektion aus der Relationenalgebra:NameLa Rose Grand CruCreek ShirazZinfandelPinot NoirRiesling ReserveChardonnay


Tupelvariablen und Relationennamen

• Anfrage


• ist äquivalent zu

select WEINE.Name from WEINE

• und

select W.Name from WEINE W



• Anfrage




• und




• Anfrage




• und



Verbunde

Kartesisches Produkt

• bei mehr als einer Relation wird das kartesische Produktgebildet:


• alle Kombinationen werden ausgegeben!


Kartesisches Produkt

• bei mehr als einer Relation wird das kartesische Produktgebildet:


• alle Kombinationen werden ausgegeben!


Tupelvariablen für mehrfachen Zugriff

• Einführung von Tupelvariablen erlaubt mehrfachen Zugriffauf eine Relation:

select *from WEINE w1, WEINE w2

• Spalten lauten dann:

w1.WeinID, w1.Name, w1.Farbe, w1.Jahrgang,w1.Weingut,

w2.WeinID, w2.Name, w2.Farbe, w2.Jahrgang,w2.Weingut



• Einführung von Tupelvariablen erlaubt mehrfachen Zugriffauf eine Relation:select *from WEINE w1, WEINE w2






• Einführung von Tupelvariablen erlaubt mehrfachen Zugriffauf eine Relation:select *from WEINE w1, WEINE w2





Natürlicher Verbund in SQL92

• frühe SQL-Versionen

• üblicherweise realisierter Standard in aktuellen Systemen• kennen nur Kreuzprodukt, keinen explizitenVerbundoperator

• Verbund durch Prädikat hinter where realisieren• Beispiel für natürlichen Verbund:




• frühe SQL-Versionen• üblicherweise realisierter Standard in aktuellen Systemen

• kennen nur Kreuzprodukt, keinen explizitenVerbundoperator





• frühe SQL-Versionen• üblicherweise realisierter Standard in aktuellen Systemen• kennen nur Kreuzprodukt, keinen explizitenVerbundoperator






• Verbund durch Prädikat hinter where realisieren

• Beispiel für natürlichen Verbund:


















Verbund explizit: natural join

• neuere SQL-Versionen

• kennen mehrere explizite Verbundoperatoren (engl. join)• als Abkürzung für die ausführliche Anfrage mitKreuzprodukt aufzufassen




• neuere SQL-Versionen• kennen mehrere explizite Verbundoperatoren (engl. join)

• als Abkürzung für die ausführliche Anfrage mitKreuzprodukt aufzufassen




• neuere SQL-Versionen• kennen mehrere explizite Verbundoperatoren (engl. join)• als Abkürzung für die ausführliche Anfrage mitKreuzprodukt aufzufassen











Verbunde als explizite Operatoren: join

• Verbund mit beliebigem Prädikat:

select *from WEINE join ERZEUGER

on WEINE.Weingut = ERZEUGER.Weingut

• Gleichverbund mit using:

select *from WEINE join ERZEUGER

using (Weingut)


Verbund explizit: cross join

• Kreuzprodukt


• als cross join

select *from WEINE cross join ERZEUGER


Tupelvariable für Zwischenergebnisse

• „Zwischenrelationen“ aus SQL-Operationen oder einemSFW-Block können über Tupelvariablen mit Namenversehen werdenselect Ergebnis.Weingutfrom (WEINE natural join ERZEUGER) as Ergebnis

• für from sind Tupelvariablen Pflicht• as ist optional


Präfixe für Eindeutigkeit

select Name, Jahrgang, Weingut -- (falsch!)from WEINE natural join ERZEUGER

• Attribut Weingut existiert sowohl in der Tabelle WEINEals auch in ERZEUGER!

• richtig mit Präfix:

select Name, Jahrgang, ERZEUGER.Weingutfrom WEINE natural join ERZEUGER


Tupelvariablen für Eindeutigkeit

• bei der Verwendung von Tupelvariablen, kann der Nameeiner Tupelvariablen zur Qualifizierung eines Attributsbenutzt werden:

select w1.Name, w2.Weingutfrom WEINE w1, WEINE w2


Selektionen

Die where-Klausel

select …from …where bedingung

• Formen der Bedingung:• Vergleich eines Attributs mit einer Konstanten:

attribut θ konstantemögliche Vergleichssymbole θ abhängig vom Wertebereich;etwa =, <>, >, <, >= sowie <=.

• Vergleich zwischen zwei Attributen mit kompatiblenWertebereichen:

attribut1 θ attribut2• logische Konnektoren or, and und not


Verbundbedingung

• Verbundbedingung hat die Form:

relation1.attribut = relation2.attribut

• Beispiel:

select Name, Jahrgang, ERZEUGER.Weingutfrom WEINE, ERZEUGERwhere WEINE.Weingut = ERZEUGER.Weingut


Bereichsselektion

• Bereichsselektionattrib between konstante1 and konstante2

ist Abkürzung für

attrib ≥ konstante1 andattrib ≤ konstante2

• schränkt damit Attributwerte auf das abgeschlosseneIntervall [konstante1, konstante2] ein

• Beispiel:

select * from WEINEwhere Jahrgang between 2000 and 2005


Ungewissheitsselektion

• Notationattribut like spezialkonstante

• Mustererkennung in Strings (Suche nach mehrerenTeilzeichenketten)

• Spezialkonstante kann die Sondersymbole ‘%’ und ‘_’beinhalten

• ‘%’ steht für kein oder beliebig viele Zeichen• ‘_’ steht für genau ein Zeichen


Ungewissheitsselektion /2

select * from WEINEwhere Name like 'La Rose%'

ist Abkürzung für

select * from WEINEwhere Name = 'La Rose'

or Name = 'La RoseA' or Name = 'La RoseAA' …or Name = 'La RoseB' or Name = 'La RoseBB' ……or Name = 'La Rose Grand Cru' …or Name = 'La Rose Grand Cru Classe' ……or Name = 'La RoseZZZZZZZZZZZZZ' …


Mengenoperationen

Mengenoperationen

• Mengenoperationen erfordern kompatible Wertebereichefü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 vondem genauen Typ) wie integer oder float

• Ergebnisschema := Schema der „linken“ Relation

select A, B, C from R1unionselect A, C, D from R2



• Vereinigung, Durchschnitt und Differenz als union,intersect und except

• orthogonal einsetzbar:

select *from (select Weingut from ERZEUGER

except select Weingut from WEINE)

äquivalent zu

select *from ERZEUGER except corresponding WEINE


Mengenoperationen in SQL /2

• über corresponding by-Klausel: Angabe derAttributliste, über die Mengenoperation ausgeführt wird

select *from ERZEUGER except corresponding by (Weingut)

WEINE

• bei Vereinigung: Defaultfall ist Duplikateliminierung(union distinct); ohne Duplikateliminierung durchunion all



R A B C1 2 32 3 4

S A C D2 3 42 4 5

R union S A B C1 2 32 3 42 4 5

R union all S A B C1 2 32 3 42 3 42 4 5



R A B C1 2 32 3 4

S A C D2 3 42 4 5

R union corresponding S A C1 32 42 3

R union corresponding by (A) S A12


Geschachtelte Anfragen /1

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 undexists


in-Prädikat und geschachtelte Anfragen

• Notation:

attribut in ( SFW-block )

• Beispiel:

select Namefrom WEINEwhere Weingut in (

select Weingut from ERZEUGERwhere Region = 'Bordeaux')



• Notation:


• Beispiel:





• Notation:


• Beispiel:




Auswertung von geschachtelten Anfragen

1. Auswertung der inneren Anfrage zu den Weingütern ausBordeaux

2. Einsetzen des Ergebnisses als Menge von Konstanten indie äußere Anfrage hinter in

3. Auswertung der modifizierten Anfrage

select Name from WEINEwhere Weingut in (

'Château La Rose', 'Château La Pointe')

NameLa Rose Grand Cru










































Auswertung von geschachtelten Anfragen /2

• interne Auswertung: Umformung in einen Verbund

select Namefrom WEINE natural join ERZEUGERwhere Region = 'Bordeaux'


Auswertung von geschachtelten Anfragen /2

• interne Auswertung: Umformung in einen Verbund

select Namefrom WEINE natural join ERZEUGERwhere Region = 'Bordeaux'


Negation des in-Prädikats

• Simulation des Differenzoperators

πWeingut(ERZEUGER)− πWeingut(WEINE)

durch SQL-Anfrage

select Weingutfrom ERZEUGERwhere Weingut not in (

select Weingut from WEINE )





durch SQL-Anfrage







durch SQL-Anfrage




Mächtigkeit des SQL-Kerns

Relationenalgebra SQLProjektion select distinctSelektion where ohne SchachtelungVerbund from, where

from mit join oder natural joinUmbenennung from mit Tupelvariable; asDifferenz where mit Schachtelung

except correspondingDurchschnitt where mit Schachtelung

intersect correspondingVereinigung union corresponding


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 vonBedingungen und Bedingungen mit Quantoren,

• innerhalb der select-Klausel die Anwendung vonskalaren Operationen und Aggregatfunktionen,

• zusätzliche Klauseln group by und having• rekursive Anfragen


Skalare Ausdrücke

Skalare Ausdrücke

• Umbenennung von Spalten: ausdruck as neuer-name• skalare Operationen auf

• numerischen Wertebereichen: etwa +, −, ∗ und /,• Strings: Operationen wie char_length (aktuelle Längeeines Strings), die Konkatenation ∥ und die Operationsubstring (Suchen einer Teilzeichenkette an bestimmtenPositionen des Strings),

• Datumstypen und Zeitintervallen: Operationen wiecurrent_date (aktuelles Datum), current_time(aktuelle Zeit), +, − und ∗

• bedingte Ausdrücke• Typkonvertierung


Skalare Ausdrücke: Hinweise

• skalare Ausdrücke können mehrere Attribute umfassen• Anwendung ist tupelweise: pro Eingabetupel entsteht einErgebnistupel


Skalare Ausdrücke: Beispiele

• Ausgabe der Namen aller Grand Cru-Weine

select substring(Name from 1 for(char_length(Name) -position('Grand Cru' in Name)))

from WEINE where Name like '%Grand Cru'


Skalare Ausdrücke: Beispiele /2

• Annahme: zusätzliches Attribut HerstDatum in WEINEalter table WEINE add column HerstDatum date

update WEINE set HerstDatum = date '2004-08-13'where Name = 'Zinfandel'

• Anfrage:

select Name,year(current_date - HerstDatum) as Alter

from WEINE


Bedingte Ausdrücke

• case-Anweisung: Ausgabe eines Wertes in Abhängigkeitvon der Auswertung eines Prädikats

casewhen prädikat1 then ausdruck1…when prädikatn−1 then ausdruckn−1[ else ausdruckn ]

end


Bedingte Ausdrücke: Beispiele

• Einsatz in select- und where-Klauselselect case

when Farbe = 'Rot' then 'Rotwein'when Farbe = 'Weiß' then 'Weißwein'else 'Sonstiges'

end as Weinart, Name from WEINE


Typkonvertierung

• explizite Konvertierung des Typs von Ausdrücken

cast(ausdruck as typname)

• Beispiel: int-Werte als Zeichenkette fürKonkatenationsoperator

select cast(Jahrgang as varchar) || 'er ' ||Name as Bezeichnung

from WEINE


Geschachtelte Anfragen /2

Quantoren und Mengenvergleiche

• Quantoren: all, any, some und exists• Notation

attribut θ all | any | some (select attributfrom …where …)

• all: where-Bedingung wird erfüllt, wenn für alle Tupeldes inneren SFW-Blocks der θ-Vergleich mit attribut truewird

• any bzw. some: where-Bedingung wird erfüllt, wenn derθ-Vergleich mit mindestens einem Tupel des innerenSFW-Blocks true wird


Bedingungen mit Quantoren: Beispiele

• Bestimmung des ältesten Weines

select *from WEINEwhere Jahrgang <= all (

select Jahrgang from WEINE)

• alle Weingüter, die Rotweine produzieren

select *from ERZEUGERwhere Weingut = any (

select Weingut from WEINEwhere Farbe = 'Rot')


Vergleich von Wertemengen

• Test auf Gleichheit zweier Mengen allein mit Quantorennicht möglich

• Beispiel: „Gib alle Erzeuger aus, die sowohl Rot- als auchWeißweine produzieren.“

• falsche Anfrage

select Weingutfrom WEINEwhere Farbe = 'Rot' and Farbe = 'Weiß'


Vergleich von Wertemengen /2

• richtige Formulierung

select w1.Weingutfrom WEINE w1, WEINE w2where w1.Weingut = w2.Weingut

and w1.Farbe = 'Rot' and w2.Farbe = 'Weiß'


Das exists/not exists-Prädikat

• einfache Form der Schachtelung

exists ( SFW-block )

• liefert true, wenn das Ergebnis der inneren Anfrage nichtleer ist

• speziell bei verzahnt geschachtelten (korrelierte) Anfragensinnvoll

• in der inneren Anfrage wird Relationen- oderTupelvariablen-Name aus dem from-Teil der äußerenAnfrage verwendet


Verzahnt geschachtelte Anfragen

• Weingüter mit 1999er Rotwein

select * from ERZEUGERwhere 1999 in (

select Jahrgang from WEINEwhere Farbe='Rot' and

WEINE.Weingut = ERZEUGER.Weingut)


Verzahnt geschachtelte Anfragen: konzeptionelle Auswertung

1. Untersuchung des ersten ERZEUGER-Tupels in deräußeren Anfrage (Creek) und Einsetzen in innere Anfrage

2. Auswertung der inneren Anfrage

select Jahrgang from WEINEwhere Farbe='Rot' and WEINE.Weingut = 'Creek'

3. Weiter bei 1. mit zweitem Tupel …

Alternative: Umformulierung in Verbund


Beispiel für exists

• Weingüter aus Bordeaux ohne gespeicherte Weine

select * from ERZEUGER ewhere Region = 'Bordeaux' and not exists (

select * from WEINEwhere Weingut = e.Weingut)


Aggregatfunktionen undGruppierungen

Aggregatfunktionen und Gruppierung

• Aggregatfunktionen berechnen neue Werte für einegesamte Spalte, etwa die Summe oder den Durchschnittder Werte einer Spalte

• Beispiel: Ermittlung des Durchschnittspreises aller Artikeloder des Gesamtumsatzes über alle verkauften Produkte

• bei zusätzlicher Anwendung von Gruppierung: Berechnungder Funktionen pro Gruppe, z.B. der Durchschnittspreispro Warengruppe oder der Gesamtumsatz pro Kunde


Aggregatfunktionen

• Aggregatfunktionen in Standard-SQL:• count: berechnet Anzahl der Werte einer Spalte oderalternativ (im Spezialfall count(∗)) die Anzahl der Tupeleiner Relation

• sum: berechnet die Summe der Werte einer Spalte (nur beinumerischen Wertebereichen)

• avg: berechnet den arithmetischen Mittelwert der Werteeiner Spalte (nur bei numerischen Wertebereichen)

• max bzw. min: berechnen den größten bzw. kleinsten Werteiner Spalte


Aggregatfunktionen /2

• Argumente einer Aggregatfunktion:• ein Attribut der durch die from-Klausel spezifiziertenRelation,

• ein gültiger skalarer Ausdruck oder• im Falle der count-Funktion auch das Symbol ∗



• vor dem Argument (außer im Fall von count(∗)) optionalauch die Schlüsselwörter distinct oder all

• distinct: vor Anwendung der Aggregatfunktion werdendoppelte Werte aus der Menge von Werten, auf die dieFunktion angewendet wird

• all: Duplikate gehen mit in die Berechnung ein(Default-Voreinstellung)

• Nullwerte werden in jedem Fall vor Anwendung derFunktion aus der Wertemenge eliminiert (außer im Fall voncount(∗))


Aggregatfunktionen - Beispiele

• Anzahl der Weine:select count(*) as Anzahlfrom WEINE

ergibtAnzahl7


Aggregatfunktionen - Beispiele /2

• Anzahl der verschiedenen Weinregionen:

select count(distinct Region)from ERZEUGER

• Weine, die älter als der Durchschnitt sind:

select Name, Jahrgangfrom WEINEwhere Jahrgang < (

select avg(Jahrgang) from WEINE)



• Schachtelung von Aggregatfunktionen nicht erlaubt

select f1(f2(A)) as Ergebnisfrom R … -- (falsch!)

• mögliche Formulierung:

select f1(Temp) as Ergebnisfrom ( select f2(A) as Temp from R …)


Aggregatfunktionen in where-Klausel

• Aggregatfunktionen liefern nur einen Wert Einsatz inKonstanten-Selektionen der where-Klausel möglich

• alle Weingüter, die nur einen Wein liefern:

select * from ERZEUGER ewhere 1 = (

select count(*) from WEINE wwhere w.Weingut = e.Weingut)


group by und having

• Notation

select …from …[where …][group by attributliste ][having bedingung ]


Gruppierung: Schema

• Relation REL:A B C D1 2 3 41 2 4 52 3 3 43 3 4 53 3 6 7

…

• Anfrage:

select A, sum(D) from REL where …group by A, Bhaving A<4 and sum(D)<10 and max(C)=4


Gruppierung: Schritt 1

• from und where

A B C D1 2 3 41 2 4 52 3 3 43 3 4 53 3 6 7

…

à

A B C D1 2 3 41 2 4 52 3 3 43 3 4 53 3 6 7



• group by A, B

A B C D1 2 3 41 2 4 52 3 3 43 3 4 53 3 6 7

à

A B NC D

1 2 3 44 5

2 3 3 43 3 4 5

6 7



• select A, sum(D)

A B NC D

1 2 3 44 5

2 3 3 43 3 4 5

6 7

à

A sum(D) NC D

1 9 3 44 5

2 4 3 43 12 4 5

6 7



• having A<4 and sum(D)<10 and max(C)=4

A sum(D) NC D

1 9 3 44 5

2 4 3 43 12 4 5

6 7

àA sum(D)1 9


Gruppierung - Beispiel

• Anzahl der Rot- und Weißweine:

select Farbe, count(*) as Anzahlfrom WEINEgroup by Farbe

• Ergebnisrelation:Farbe AnzahlRot 5Weiß 2


having - Beispiel

• Regionen mit mehr als einem Wein

select Region, count(*) as Anzahlfrom ERZEUGER natural join WEINEgroup by Regionhaving count(*) > 1


Attribute für Aggregation bzw. having

• zulässige Attribute hinter select bei Gruppierung aufRelation mit Schema R

• Gruppierungsattribute G• Aggregationen auf Nicht-Gruppierungsattributen R− G

• zulässige Attribute für having• dito


Äußere Verbunde

Äußere Verbunde

• zusätzlich zu klassischen Verbund (inner join): inSQL-92 auch äußerer Verbund Übernahme von„dangling tuples“ in das Ergebnis und Auffüllen mitNullwerten

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

• äußerer natürlicher Verbund jeweils mit Schlüsselwortnatural, also z.B. natural left outer join


Äußere Verbunde /2

LINKS A B1 22 3

RECHTS B C3 44 5

NATURAL JOIN A B C2 3 4

OUTER A B C1 2 ⊥2 3 4⊥ 4 5

LEFT A B C1 2 ⊥2 3 4

RIGHT A B C2 3 4⊥ 4 5


Äußerer Verbund: Beispiel

select Anbaugebiet, count(WeinID) as Anzahlfrom ERZEUGER natural left outer join WEINEgroup by Anbaugebiet

Anbaugebiet AnzahlBarossa Valley 2Napa Valley 3Saint-Emilion 1Pomerol 0Rheingau 1


Simulation des (linken) äußeren Verbundes

select *from ERZEUGER natural join WEINE

union allselect ERZEUGER.*, cast(null as int),

cast(null as varchar(20)),cast(null as varchar(10)), cast(null as int),cast(null as varchar(20))

from ERZEUGER ewhere not exists (

select * from WEINEwhere WEINE.Weingut = e.Weingut)


Sortierung

Sortierung mit order by

• Notationorder by attributliste

• Beispiel:

select *from WEINEorder by Jahrgang

• Sortierung aufsteigend (asc) oder absteigend (desc)• Sortierung als letzte Operation einer AnfrageSortierattribut muss in der select-Klausel vorkommen


Sortierung /2

• Sortierung auch mit berechneten Attributen (Aggregaten)als Sortierkriterium

select Weingut, count(*) as Anzahlfrom ERZEUGER natural join WEINEgroup by Weingutorder by Anzahl desc


Sortierung: Top-k-Anfragen

• Anfrage, die die besten k Elemente bzgl. einerRangfunktion liefert

select w1.Name, count(*) as Rangfrom WEINE w1, WEINE w2where w1.Jahrgang <= w2.Jahrgang -- Schritt 1group by w1.Name, w1.WeinID -- Schritt 2having count(*) <= 4 -- Schritt 3order by Rang -- Schritt 4


Sortierung: Top-k-Anfragen

• Ermittlung der k = 4 jüngste Weine• Erläuterung

• Schritt 1: Zuordnung aller Weine die älter sind• Schritt 2: Gruppierung nach Namen, Berechnung des Rangs• Schritt 3: Beschränkung auf Ränge ≤ 4• Schritt 4: Sortierung nach Rang

Name RangZinfandel 1Creek Shiraz 2Chardonnay 3Pinot Noir 4


Nullwerte

Behandlung von Nullwerten

• skalare Ausdrücke: Ergebnis null, sobald Nullwert in dieBerechnung eingeht

• in allen Aggregatfunktionen bis auf count(∗) werdenNullwerte vor Anwendung der Funktion entfernt

• fast alle Vergleiche mit Nullwert ergeben Wahrheitswertunknown (statt true oder false)

• Ausnahme: is null ergibt true, is not null ergibtfalse

• Boolesche Ausdrücke basieren dann auf dreiwertiger Logik






























Behandlung von Nullwerten /2

and true unknown falsetrue true unknown falseunknown unknown unknown falsefalse false false false

or true unknown falsetrue true true trueunknown true unknown unknownfalse true unknown false

nottrue falseunknown unknownfalse true


Selektionen nach Nullwerten

• Null-Selektion wählt Tupel aus, die bei einem bestimmtenAttribut Nullwerte enthalten

• Notationattribut is null

• Beispiel

select * from ERZEUGERwhere Anbaugebiet is null


Selektionen nach Nullwerten

• Null-Selektion wählt Tupel aus, die bei einem bestimmtenAttribut Nullwerte enthalten

• Notationattribut is null

• Beispiel

select * from ERZEUGERwhere Anbaugebiet is null


Rekursion

Benannte Anfragen

• Anfrageausdruck, der in der Anfrage mehrfach referenziertwerden kann

with anfrage-name [(spalten-liste) ] as( anfrage-ausdruck )

• Anfrage ohne with

select *from WEINEwhere Jahrgang >= (

select avg(Jahrgang) from WEINE) - 2and Jahrgang <= (

select avg(Jahrgang) from WEINE) + 2


Benannte Anfragen








Benannte Anfragen








Benannte Anfragen



• Anfrage ohne withselect *from WEINEwhere Jahrgang >= (




Benannte Anfragen /2

• Anfrage mit with

with ALTER(Durchschnitt) as (select avg(Jahrgang) from WEINE)

select *from WEINE, ALTERwhere Jahrgang >= Durchschnitt - 2and Jahrgang <= Durchschnitt + 2


Rekursive Anfragen

• Anwendung: Bill of Material-Anfragen, Berechnung dertransitiven Hülle (Flugverbindungen etc.)

• Beispiel:

BUSLINIE Abfahrt Ankunft DistanzNuriootpa Penrice 7Nuriootpa Tanunda 7Tanunda Seppeltsfield 9Tanunda Bethany 4Bethany Lyndoch 14


Rekursive Anfragen


• Beispiel:



Rekursive Anfragen


• Beispiel:



Rekursive Anfragen


• Beispiel:BUSLINIE Abfahrt Ankunft Distanz

Nuriootpa Penrice 7Nuriootpa Tanunda 7Tanunda Seppeltsfield 9Tanunda Bethany 4Bethany Lyndoch 14


Rekursive Anfrage: Busfahrt mit max. 2x Umsteigen

select Abfahrt, Ankunft from BUSLINIEwhere Abfahrt = 'Nuriootpa'

unionselect B1.Abfahrt, B2.Ankunftfrom BUSLINIE B1, BUSLINIE B2where B1.Abfahrt = 'Nuriootpa'

and B1.Ankunft = B2.Abfahrtunion

select B1.Abfahrt, B3.Ankunftfrom BUSLINIE B1, BUSLINIE B2, BUSLINIE B3where B1.Abfahrt = 'Nuriootpa'

and B1.Ankunft = B2.Abfahrtand B2.Ankunft = B3.Abfahrt


Rekursion in SQL:2003

• Formulierung über erweiterte with recursive-Anfrage• Notationwith recursive rekursive-tabelle as (

anfrage-ausdruck -- rekursiver Teil)[traversierungsklausel] [zyklusklausel]anfrage-ausdruck -- nicht rekursiver Teil

• nicht rekursiver Teil: Anfrage auf Rekursionstabelle


Rekursion in SQL:2003 /2

• rekursiver Teil:-- Initialisierungselect …from tabelle where …-- Rekursionsschrittunion allselect …from tabelle, rekursionstabellewhere rekursionsbedingung


Rekursion in SQL:2003: Beispiel

with recursive TOUR(Abfahrt, Ankunft) as (select Abfahrt, Ankunftfrom BUSLINIEwhere Abfahrt = 'Nuriootpa'

union allselect T.Abfahrt, B.Ankunftfrom TOUR T, BUSLINIE Bwhere T.Ankunft = B.Abfahrt)

select distinct * from TOUR


Schrittweiser Aufbau der Rekursionstabelle TOUR

InitialisierungAbfahrt AnkunftNuriootpa PenriceNuriootpa Tanunda

Schritt 1Abfahrt AnkunftNuriootpa PenriceNuriootpa TanundaNuriootpa SeppeltsfieldNuriootpa Bethany

Schritt 2Abfahrt AnkunftNuriootpa PenriceNuriootpa TanundaNuriootpa SeppeltsfieldNuriootpa BethanyNuriootpa Lyndoch


Rekursion: Beispiel /2

• arithmetische Operationen im Rekursionsschritt

with recursive TOUR(Abfahrt, Ankunft, Strecke) as (select Abfahrt, Ankunft, Distanz as Streckefrom BUSLINIEwhere Abfahrt = 'Nuriootpa'

union allselect T.Abfahrt, B.Ankunft,

Strecke + Distanz as Streckefrom TOUR T, BUSLINIE Bwhere T.Ankunft = B.Abfahrt)



Sicherheit rekursiver Anfragen

• Sicherheit (= Endlichkeit der Berechnung) ist wichtigeAnforderung an Anfragesprache

• Problem: Zyklen bei Rekursion

insert into BUSLINIE (Abfahrt, Ankunft, Distanz)values ('Lyndoch', 'Tanunda', 12)

• Behandlung in SQL• Begrenzung der Rekursionstiefe• Zyklenerkennung


Sicherheit rekursiver Anfragen /2

• Einschränkung der Rekursionstiefe

with recursive TOUR(Abfahrt, Ankunft, Umsteigen) as (select Abfahrt, Ankunft, 0from BUSLINIEwhere Abfahrt = 'Nuriootpa'

union allselect T.Abfahrt, B.Ankunft, Umsteigen + 1from TOUR T, BUSLINIE Bwhere T.Ankunft = B.Abfahrt and Umsteigen < 2)



Sicherheit durch Zyklenerkennung

• Zyklusklausel• beim Erkennen von Duplikaten im Berechnungspfad vonattrib: Zyklus = '*' (Pseudospalte vom Typ char(1))

• Sicherstellen der Endlichkeit des Ergebnisses „von Hand“

cycle attrib set marke to '*' default '-'


Sicherheit durch Zyklenerkennung

with recursive TOUR(Abfahrt, Ankunft, Weg) as (select Abfahrt, Ankunft,

Abfahrt || '-' || Ankunft as Wegfrom BUSLINIE where Abfahrt = 'Nuriootpa'

union allselect T.Abfahrt, B.Ankunft,

Weg || '-' || B.Ankunft as Wegfrom TOUR T, BUSLINIE B

where T.Ankunft = B.Abfahrt)cycle Ankunft set Zyklus to '*' default '-'select Weg, Zyklus from TOUR


Sicherheit durch Zyklenerkennung /2

Weg ZyklusNuriootpa-Penrice -Nuriootpa-Tanunda -Nuriootpa-Tanunda-Seppeltsfield -Nuriootpa-Tanunda-Bethany -Nuriootpa-Tanunda-Bethany-Lyndoch -Nuriootpa-Tanunda-Bethany-Lyndoch-Tanunda *


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 …aktuell SQL:2011

• trotz Standardisierung: teilweise Inkompatibilitätenzwischen Systemen der einzelnen Hersteller


Zusammenfassung

• SQL als Standardsprache• SQL-Kern mit Bezug zur Relationenalgebra• Erweiterungen: Gruppierung, Rekursion etc.


Kontrollfragen

• Welche Möglichkeiten der Formulierungvon Verbunden gibt es?

• Was berechnen Aggregationen undGruppierungen?

• Welche Operationen stehen für denUmgang mit Nullwerten zur Verfügung?

• Welchem Zweck dienen rekursiveAnfragen in SQL?


Kontrollfragen






Kontrollfragen






Kontrollfragen






Teil VIII

Grundlagen von Anfragen: Algebra &Kalkül

Grundlagen von Anfragen: Algebra & Kalkül

1. Kriterien für Anfragesprachen

2. Anfragealgebren

3. Erweiterungen der Relationenalgebra

4. Anfragekalküle

5. Beispiele für Bereichskalkül

6. Eigenschaften des Bereichskalküls




2. Anfragealgebren


4. Anfragekalküle






2. Anfragealgebren


4. Anfragekalküle






2. Anfragealgebren


4. Anfragekalküle






2. Anfragealgebren


4. Anfragekalküle






2. Anfragealgebren


4. Anfragekalküle





• Verständnis der formalen Grundlagenrelationaler Anfragesprachen

• Kenntnisse zur Formulierung vonAnfragen in der relationalen Algebra

• Kenntnisse zur Formulierung vonKalkülanfragen












Kriterien für Anfragesprachen

Einführung

• bisher:

• Relationenschemata mit Basisrelationen, die in derDatenbank gespeichert sind

• jetzt:

• „abgeleitete“ Relationenschemata mit virtuellenRelationen, die aus den Basisrelationen berechnet werden(Basisrelationen bleiben unverändert)


Einführung

• bisher:• Relationenschemata mit Basisrelationen, die in derDatenbank gespeichert sind

• jetzt:



Einführung


• jetzt:



Einführung


• jetzt:• „abgeleitete“ Relationenschemata mit virtuellenRelationen, die aus den Basisrelationen berechnet werden(Basisrelationen bleiben unverändert)


Begriffe

• Anfrage: Folge von Operationen, die aus denBasisrelationen eine Ergebnisrelation berechnet

• Ergebnisrelation interaktiv auf dem Bildschirm anzeigenoder

• per Programm weiterverarbeiten („Einbettung“)

• Sicht: Folge von Operationen, die unter einem Sichtnamenlangfristig abgespeichert wird und unter diesem Namenwieder aufgerufen werden kann; ergibt eine Sichtrelation

• Snapshot: Ergebnisrelation einer Anfrage, die unter einemSnapshot-Namen abgelegt wird, aber nie ein zweites Mal(mit geänderten Basisrelationen) berechnet wird (etwaJahresbilanzen)


Begriffe







Begriffe







Begriffe







Begriffe








• Ad-Hoc-Formulierung: Benutzer soll eine Anfrageformulieren können, ohne ein vollständiges Programmschreiben zu müssen

• Deskriptivität: Benutzer soll formulieren „Was will ichhaben?“ und nicht „Wie komme ich an das, was ich habenwill?“

• Mengenorientiertheit: jede Operation soll auf Mengen vonDaten gleichzeitig arbeiten, nicht navigierend nur aufeinzelnen Elementen („one-tuple-at-a-time“)

• Abgeschlossenheit: Ergebnis ist wieder eine Relation undkann wieder als Eingabe für die nächste Anfrageverwendet werden




















Kriterien für Anfragesprachen /2

• Adäquatheit: alle Konstrukte des zugrundeliegendenDatenmodells werden unterstützt

• Orthogonalität: Sprachkonstrukte sind in ähnlichenSituationen auch ähnlich anwendbar

• Optimierbarkeit: Sprache besteht aus wenigenOperationen, für die es Optimierungsregeln gibt

• Effizienz: jede Operation ist effizient ausführbar (imRelationenmodell hat jede Operation eine Komplexität≤ O(n2),n Anzahl der Tupel einer Relation).





















• Sicherheit: keine Anfrage, die syntaktisch korrekt ist, darfin eine Endlosschleife geraten oder ein unendlichesErgebnis liefern

• Eingeschränktheit: (folgt aus Sicherheit, Optimierbarkeit,Effizienz) Anfragesprache darf keine kompletteProgrammiersprache sein

• Vollständigkeit: Sprache muss mindestens die Anfrageneiner Standardsprache (wie etwa die in diesem Kapiteleinzuführende Relationenalgebra oder den sicherenRelationenkalkül) ausdrücken können












Anfragealgebren

Anfragealgebren

• Mathematik: Algebra definiert durch Wertebereich und aufdiesem definierte Operatoren

• für Datenbankanfragen: Inhalte der Datenbank sind Werte,und Operatoren definieren Funktionen zum Berechnenvon Anfrageergebnissen

• Relationenalgebra• Algebra-Erweiterungen


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 ∪,−)


Relationenalgebra





Relationenalgebra



• Tabellen verknüpfen: Verbund (Join) ⋊⋉

• Tabellen vereinigen: Vereinigung ∪• Tabellen voneinander abziehen: Differenz −• Spalten umbenennen: Umbenennung β(wichtig für ⋊⋉ und ∪,−)


Relationenalgebra



• Tabellen verknüpfen: Verbund (Join) ⋊⋉• Tabellen vereinigen: Vereinigung ∪

• Tabellen voneinander abziehen: Differenz −• Spalten umbenennen: Umbenennung β(wichtig für ⋊⋉ und ∪,−)


Relationenalgebra



• Tabellen verknüpfen: Verbund (Join) ⋊⋉• Tabellen vereinigen: Vereinigung ∪• Tabellen voneinander abziehen: Differenz −

• Spalten umbenennen: Umbenennung β(wichtig für ⋊⋉ und ∪,−)


Relationenalgebra





Relationenalgebra: Übersicht

a1 b2

a2 b2

b2 c3

b3 c4

a2 b3 b4 c5

a1 b2

a2 b2

a2 b3

c3

c3

c4

Verbund

Selektion Projektion


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)


Projektion: Beispiel

πRegion(ERZEUGER)



Projektion: Beispiel

πRegion(ERZEUGER)



Projektion: Beispiel 2

πAnbaugebiet,Region(ERZEUGER)

Anbaugebiet RegionBarossa Valley SüdaustralienNapa Valley KalifornienSaint-Emilion BordeauxPomerol BordeauxRheingau Hessen


Projektion: Beispiel 2

πAnbaugebiet,Region(ERZEUGER)

Anbaugebiet RegionBarossa Valley SüdaustralienNapa Valley KalifornienSaint-Emilion BordeauxPomerol BordeauxRheingau Hessen


Selektion

• SyntaxσBedingung(Relation)

• Semantik (für A ∈ R)

σA=a(r) := t ∈ r | t(A) = a


Selektionsbedingungen

• KonstantenselektionAttribut θ Konstante

boolesches Prädikat θ ist = oder =, bei linear geordnetenWertebereichen auch ≤, <, ≥ oder >

• AttributselektionAttribut1 θ Attribut2

• logische Verknüpfung mehrerer Konstanten- oderAttribut-Selektionen mit ∧,∨ oder ¬


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)


Selektion: Beispiel




Selektion: Beispiel




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 Spaltenbei gleichen Attributwerten


Verbund: Eigenschaften

• Schema für r(R) ⋊⋉ r(S) ist Vereinigung der AttributmengenRS = R ∪ S

• aus R1 ∩ R2 = folgt r1 ⋊⋉ r2 = r1 × r2• Kommutativität: r1 ⋊⋉ r2 = r2 ⋊⋉ r1• Assoziativität: (r1 ⋊⋉ r2) ⋊⋉ r3 = r1 ⋊⋉ (r2 ⋊⋉ r3)• daher erlaubt:

⋊⋉pi=1 ri




• aus R1 ∩ R2 = folgt r1 ⋊⋉ r2 = r1 × r2

• Kommutativität: r1 ⋊⋉ r2 = r2 ⋊⋉ r1• Assoziativität: (r1 ⋊⋉ r2) ⋊⋉ r3 = r1 ⋊⋉ (r2 ⋊⋉ r3)• daher erlaubt:

⋊⋉pi=1 ri




• aus R1 ∩ R2 = folgt r1 ⋊⋉ r2 = r1 × r2• Kommutativität: r1 ⋊⋉ r2 = r2 ⋊⋉ r1

• Assoziativität: (r1 ⋊⋉ r2) ⋊⋉ r3 = r1 ⋊⋉ (r2 ⋊⋉ r3)• daher erlaubt:

⋊⋉pi=1 ri




• aus R1 ∩ R2 = folgt r1 ⋊⋉ r2 = r1 × r2• Kommutativität: r1 ⋊⋉ r2 = r2 ⋊⋉ r1• Assoziativität: (r1 ⋊⋉ r2) ⋊⋉ r3 = r1 ⋊⋉ (r2 ⋊⋉ r3)

• daher erlaubt:⋊⋉pi=1 ri




• aus R1 ∩ R2 = folgt r1 ⋊⋉ r2 = r1 × r2• Kommutativität: r1 ⋊⋉ r2 = r2 ⋊⋉ r1• Assoziativität: (r1 ⋊⋉ r2) ⋊⋉ r3 = r1 ⋊⋉ (r2 ⋊⋉ r3)• daher erlaubt:

⋊⋉pi=1 ri


Verbund: Beispiel




Verbund: Beispiel




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

• durch Umbenennung nun möglich• Verbunde, wo bisher kartesische Produkte ausgeführtwurden (unterschiedliche Attribute werden gleichbenannt),

• kartesische Produkte, wo bisher Verbunde ausgeführtwurden (gleiche Attribute werden unterschiedlichgenannt),

• Mengenoperationen


Umbenennung






• Mengenoperationen


Umbenennung






• MengenoperationenSattler/Saake | VL Datenbanksysteme | 22. September 2019 8–21

Berechnung des Kreuzproduktes

• natürlicher Verbund entartet zum Kreuzprodukt, wennkeine 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 VerbundR1 ⋊⋉ R2 ≡ σR1.C=R2.C(R1× R2)




• Erzwingen durch Umbenennung

• Beispiel: R1(A,B, C) und R2(C,D)R1× R2 ≡ R1 ⋊⋉ βE←C(R2)





• Erzwingen durch Umbenennung• Beispiel: R1(A,B, C) und R2(C,D)

R1× R2 ≡ R1 ⋊⋉ βE←C(R2)





• Erzwingen durch Umbenennung• Beispiel: R1(A,B, C) und R2(C,D)

R1× R2 ≡ R1 ⋊⋉ βE←C(R2)



Mengenoperationen: Semantik

• formal für r1(R) und r2(R)

• 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



• formal für r1(R) und r2(R)• Vereinigung r1 ∪ r2 := t | t ∈ r1 ∨ t ∈ r2

• Durchschnitt r1 ∩ r2 := t | t ∈ r1 ∧ t ∈ r2• Differenz r1 − r2 := t | t ∈ r1 ∧ t ∈ r2




• formal für r1(R) und r2(R)• Vereinigung r1 ∪ r2 := t | t ∈ r1 ∨ t ∈ r2• Durchschnitt r1 ∩ r2 := t | t ∈ r1 ∧ t ∈ r2

• Differenz r1 − r2 := t | t ∈ r1 ∧ t ∈ r2




• formal für r1(R) und r2(R)• Vereinigung r1 ∪ r2 := t | t ∈ r1 ∨ t ∈ r2• Durchschnitt r1 ∩ r2 := t | t ∈ r1 ∧ t ∈ r2• Differenz r1 − r2 := t | t ∈ r1 ∧ t ∈ r2




• formal für r1(R) und r2(R)• Vereinigung r1 ∪ r2 := t | t ∈ r1 ∨ t ∈ r2• Durchschnitt r1 ∩ r2 := t | t ∈ r1 ∧ t ∈ r2• Differenz r1 − r2 := t | t ∈ r1 ∧ t ∈ r2



Unabhängigkeit und Vollständigkeit

• Minimale Relationenalgebra:Ω = π, σ, ⋊⋉, β, ∪ und −

• unabhängig: kein Operator kann weggelassen werdenohne Vollständigkeit zu verlieren

• andere unabhängige Menge: ⋊⋉ und β durch × ersetzen• Relationale Vollständigkeit: jede andere Menge vonOperationen genauso mächtig wie Ω

• strenge relationale Vollständigkeit: zu jedem Ausdruck mitOperatoren aus Ω gibt es einen Ausdruck auch mit deranderen Menge von Operationen











• andere unabhängige Menge: ⋊⋉ und β durch × ersetzen

• Relationale Vollständigkeit: jede andere Menge vonOperationen genauso mächtig wie Ω















Erweiterungen derRelationenalgebra

Erweiterungen der Relationenalgebra

• weitere Verbundoperationen• Division• Gruppierung und geschachtelte Relationen• …


Verbundvarianten

• für L(AB), R(BC), S(DE)

• Gleichverbund (engl. equi-join): Gleichheitsbedingungüber explizit angegebene und evtl. verschiedene Attribute

r(R) ⋊⋉C=D r(S)

• Theta-Verbund (engl. θ-join): beliebige Verbundbedingung

r(R) ⋊⋉C>D r(S)

• Semi-Verbund: nur Attribute eines Operanden erscheinenim Ergebnis

r(L)⋉ r(R) = πL(r(L) ⋊⋉ r(R))

• äußere Verbunde (engl. outer join)


Verbundvarianten

• für L(AB), R(BC), S(DE)• Gleichverbund (engl. equi-join): Gleichheitsbedingungüber explizit angegebene und evtl. verschiedene Attribute

r(R) ⋊⋉C=D r(S)


r(R) ⋊⋉C>D r(S)


r(L)⋉ r(R) = πL(r(L) ⋊⋉ r(R))



Verbundvarianten


r(R) ⋊⋉C=D r(S)


r(R) ⋊⋉C>D r(S)


r(L)⋉ r(R) = πL(r(L) ⋊⋉ r(R))



Verbundvarianten


r(R) ⋊⋉C=D r(S)


r(R) ⋊⋉C>D r(S)


r(L)⋉ r(R) = πL(r(L) ⋊⋉ r(R))



Verbundvarianten


r(R) ⋊⋉C=D r(S)


r(R) ⋊⋉C>D r(S)


r(L)⋉ r(R) = πL(r(L) ⋊⋉ r(R))



Äußere Verbunde

• Übernahme von „dangling tuples“ in das Ergebnis undAuffüllen mit Nullwerten

• voller äußerer Verbund übernimmt alle Tupel beiderOperanden

r ⊐▷◁⊏ s• linker äußerer Verbund übernimmt alle Tupel des linkenOperanden

r ⊐▷◁ s• rechter äußerer Verbund übernimmt alle Tupel desrechten Operanden

r ▷◁⊏ s


Äußere Verbunde



r ⊐▷◁⊏ s

• linker äußerer Verbund übernimmt alle Tupel des linkenOperanden


r ▷◁⊏ s


Äußere Verbunde




r ⊐▷◁ s

• rechter äußerer Verbund übernimmt alle Tupel desrechten Operanden

r ▷◁⊏ s


Äußere Verbunde





r ▷◁⊏ s


Äußere Verbunde /2

LINKS A B1 22 3

RECHTS B C3 44 5

⋊⋉ A B C2 3 4

⊐▷◁⊏ A B C1 2 ⊥2 3 4⊥ 4 5

⊐▷◁ A B C1 2 ⊥2 3 4

▷◁⊏ A B C2 3 4⊥ 4 5

left outer join full outer joinright outer joinnatural join


Problem: Quantoren

• Allquantor in Relationenalgebra ausdrücken, obwohl inSelektionsbedingungen nicht erlaubt

• Division (kann aus Ω hergeleitet werden)• r1(R1) und r2(R2) gegeben mit R2 ⊆ R1, R′ = R1 − R2. Dannist

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) ⋊⋉ r2)− r1)


Problem: Quantoren


• Division (kann aus Ω hergeleitet werden)

• r1(R1) und r2(R2) gegeben mit R2 ⊆ R1, R′ = R1 − R2. Dannist



r1 ÷ r2 = πR′(r1)− πR′((πR′(r1) ⋊⋉ r2)− r1)


Problem: Quantoren





r1 ÷ r2 = πR′(r1)− πR′((πR′(r1) ⋊⋉ r2)− r1)


Problem: Quantoren





r1 ÷ r2 = πR′(r1)− πR′((πR′(r1) ⋊⋉ r2)− r1)


Division: Beispiel

WEIN_EMPFEHLUNG Wein KritikerLa Rose Grand Cru ParkerPinot Noir ParkerRiesling Reserve ParkerLa Rose Grand Cru ClarkePinot Noir ClarkeRiesling Reserve Gault-Millau

GUIDES1 KritikerParkerClarke

GUIDES2 KritikerParkerGault-Millau


Divisionsbeispiele

• Division mit erster Tabelle

WEIN_EMPFEHLUNG÷ GUIDES1liefert

WeinLa Rose Grand CruPinot Noir

• Division mit zweiter Kritikerliste


WeinRiesling Reserve


Divisionsbeispiele

• Division mit erster Tabelle


WeinLa Rose Grand CruPinot Noir

• Division mit zweiter Kritikerliste


WeinRiesling Reserve


Begriff Division

• Analogie zur arithmetischen Operation der ganzzahligenDivision

DivisionDie ganzzahlige Division ist in dem Sinne die Inverse zurMultiplikation, indem sie als Ergebnis die größte Zahl liefert,für die die Multiplikation mit dem Divisor kleiner ist als derDividend.

Analog gilt: r = r1 ÷ r2 ist die größte Relation, für dier ⋊⋉ r2 ⊆ r1 ist.


Division in SQL: Simulation des Allquantors

select distinct Weinfrom WEIN_EMPFEHLUNG w1where not exists (

select * from GUIDES2 gwhere not exists (

select * from WEIN_EMPFEHLUNG w2where g.Kritiker = w2.Kritiker

and w1.Wein = w2.Wein))

• „Gib alle Weine aus, so dass kein Wein existiert, der nichtvon allen Kritikern in der Relation GUIDES2 empfohlenwird“.


Division in SQL: Simulation des Allquantors

select distinct Weinfrom WEIN_EMPFEHLUNG w1where not exists (

select * from GUIDES2 gwhere not exists (

select * from WEIN_EMPFEHLUNG w2where g.Kritiker = w2.Kritiker

and w1.Wein = w2.Wein))

• „Gib alle Weine aus, so dass kein Wein existiert, der nichtvon allen Kritikern in der Relation GUIDES2 empfohlenwird“.


Gruppierungsoperator γ

γf1(x1),f2(x2),...,fn(xn);A(r(R))

• erweitert Attributschema von r(R) um neue Attribute, diemit den Funktionsanwendungen f1(x1), f2(x2), . . . , fn(xn)korrespondieren

• Anwendung der Funktionen fi(xi) auf die Teilmengederjenigen Tupel von r(R) die gleiche Attributwerte für dieAttribute A haben

select f1(x1), f2(x2), …, fn(xn), Afrom Rgroup by A



γf1(x1),f2(x2),...,fn(xn);A(r(R))






γf1(x1),f2(x2),...,fn(xn);A(r(R))





Semantik des Gruppierungsoperators

• leere Attributmenge A = ∅:

γF(X);∅(r(R)) = r(R)× r(R)F(X)

mit r(R)F(X) ist Relation mit Attribut F(X) und einem Tupelals Wert von F(X) auf r(R)

• ohne Funktion:

γ∅;∅(r(R)) = r(R)

• allgemeiner Fall:

γF(X);A(r(R)) =∪t∈R

γF(X);∅(σA=t.A(r(R)))




γF(X);∅(r(R)) = r(R)× r(R)F(X)


• ohne Funktion:

γ∅;∅(r(R)) = r(R)







γF(X);∅(r(R)) = r(R)× r(R)F(X)


• ohne Funktion:

γ∅;∅(r(R)) = r(R)





Anfragekalküle

Anfragekalküle

• Kalkül: eine formale logische Sprache zur Formulierungvon Aussagen

• Ziel: Einsatz eines derartigen Kalküls zur Formulierung vonDatenbank-Anfragen

• Logikbasierter Ansatz:

• Datenbankinhalte entsprechen Belegungen von Prädikateneiner Logik

• Anfragen abgeleiteten Prädikaten


Anfragekalküle







Anfragekalküle







Anfragekalküle



• Logikbasierter Ansatz:• Datenbankinhalte entsprechen Belegungen von Prädikateneiner Logik



Anfragekalküle



• Logikbasierter Ansatz:• Datenbankinhalte entsprechen Belegungen von Prädikateneiner Logik



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




x2 | x ∈ N ∧ x3 > 0 ∧ x3 < 1000


f(x) | p(x)


x = x1 : D1, . . . , xn : Dn




x2 | x ∈ N ∧ x3 > 0 ∧ x3 < 1000


f(x) | p(x)


x = x1 : D1, . . . , xn : Dn


Ein allgemeiner Kalkül /2

• Funktion f bezeichnet Ergebnisfunktion über x

• wichtige Spezialfälle: Angabe einer Variable selber (f isthier die Identitätsfunktion) und Tupelkonstruktion(Ergebnis vom Typ tuple of)

• p Selektionsprädikat über freien Variablen x

• Terme aus Variablen, Konstanten undFunktionsanwendungen

• Prädikate der Datentypen, etwa ≤, <, >, ≥, ...→ atomare Formeln über Termen

• Bezug zur aktuellen Datenbank→ Datenbankprädikate, z.B.Relationennamen im RM

• prädikatenlogischen Operatoren ∧, ∨, ¬, ∀, ∃→ Formeln



• Funktion f bezeichnet Ergebnisfunktion über x• wichtige Spezialfälle: Angabe einer Variable selber (f isthier die Identitätsfunktion) und Tupelkonstruktion(Ergebnis vom Typ tuple of)

















• p Selektionsprädikat über freien Variablen x• Terme aus Variablen, Konstanten undFunktionsanwendungen


























Ergebnisbestimmung einer Anfrage

x = x1 : D1, . . . , xn : Dn

1. Bestimme aller Belegungen der freien Variablen in x, fürdie das Prädikat p wahr wird.

2. Wende Funktion f auf die durch diese Belegungengegebenen Werte an.

Sicherheit von AnfragenUnter welchen Umständen liefern Kalkülanfragen endlicheErgebnisse?

→ Sicherheit von Anfragen



x = x1 : D1, . . . , xn : Dn







x = x1 : D1, . . . , xn : Dn







x = x1 : D1, . . . , xn : Dn







x = x1 : D1, . . . , xn : Dn






Relationale Kalküle

• Bereichskalkül: Variablen nehmen Werte elementarerDatentypen (Bereiche) an

• Tupelkalkül: Variablen variieren über Tupelwerte(entsprechend den Zeilen einer Relation)


Relationale Kalküle

• Bereichskalkül: Variablen nehmen Werte elementarerDatentypen (Bereiche) an

• Tupelkalkül: Variablen variieren über Tupelwerte(entsprechend den Zeilen einer Relation)


Tupelkalkül

• Grundlage von SFW-Anfragen in SQL

• Variablen sind tupelwertig• Beispiel:

w | w ∈ WEINE ∧ w.Farbe = 'Rot'


Tupelkalkül

• Grundlage von SFW-Anfragen in SQL• Variablen sind tupelwertig

• Beispiel:



Tupelkalkül

• Grundlage von SFW-Anfragen in SQL• Variablen sind tupelwertig• Beispiel:



Tupelkalkül: Beispiele

• konstruierte Tupel

⟨w.Name,w.Weingut⟩ | w ∈ WEINE ∧ w.Farbe = 'Rot'

• Verbund

⟨e.Weingut⟩ | e ∈ ERZEUGER ∧ w ∈ WEINE∧ e.Weingut = w.Weingut

• Schachtelung

⟨w.Name,w.Weingut⟩ | w ∈ WEINE∧∃e ∈ ERZEUGER(w.Weingut = e.Weingut∧

e.Region = 'Bordeaux')




⟨w.Name,w.Weingut⟩ | w ∈ WEINE ∧ w.Farbe = 'Rot'• Verbund


• Schachtelung






⟨w.Name,w.Weingut⟩ | w ∈ WEINE ∧ w.Farbe = 'Rot'• Verbund


• Schachtelung




Motivation: Die Sprache QBE

• „Query by Example“• Anfragen in QBE: Einträge in Tabellengerüsten• Intuition: Beispieleinträge in Tabellen• Vorläufer verschiedener tabellenbasierterAnfrageschnittstellen kommerzieller Systeme

• basiert auf logischem Kalkül mit Bereichsvariablen


Anfragen in QBE: Selektion und Projektion

• Anfrage: „Alle Rotweine, die vor 2015 produziert wurden“

WEINE Name Weingut Farbe JahrgangP. Rot < 2015

n | WEINE(n, _,'Rot', j) ∧ j < 2015





n | WEINE(n, _,'Rot', j) ∧ j < 2015





n | WEINE(n, _,'Rot', j) ∧ j < 2015





n | WEINE(n, _,'Rot', j) ∧ j < 2015


Anfragen in QBE: Verbund

• Anfrage: „Alle Rotweine aus der Region Bordeaux“

WEINE Name Weingut Farbe JahrgangP. _w Rot

ERZEUGER Weingut Region Anbaugebiet_w Bordeaux

n | WEINE(n,w,'Rot', _) ∧ ERZEUGER(w,'Bordeaux', _)


























Anfragen in QBE: Selbstverbund

• Anfrage: „Regionen mit zwei oder mehr Erzeugern“

ERZEUGER Weingut Region Anbaugebiet_eins P. _region¬ _eins _region

r | ERZEUGER(x, r, _) ∧ ERZEUGER(y, r, _) ∧ x = y












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


Access: Projektion und Selektion


Access: Verbund


Bereichskalkül

• Terme:

• Konstanten, etwa 42 oder 'MZ-4'• Variablen zu Datentypen, etwa xDatentypangabe erfolgt in der Regel implizit und wird nichtexplizit deklariert!

• Funktionsanwendung f(t1, . . . , tn): Funktion f, Terme ti, etwaplus(12, x) bzw. in Infixnotation 12+ x

• Atomare Formeln:

• Prädikatanwendung Θ(t1, . . . , tn),Θ ∈ <,>,≤,≥, =,=, . . . Datentypprädikat, Terme tiZweistellige Prädikate wie üblich in Infix-Notation.Beispiele: x = y, 42 > x oder 3+ 7 = 11.


Bereichskalkül

• Terme:• Konstanten, etwa 42 oder 'MZ-4'

• Variablen zu Datentypen, etwa xDatentypangabe erfolgt in der Regel implizit und wird nichtexplizit deklariert!





Bereichskalkül

• Terme:• Konstanten, etwa 42 oder 'MZ-4'• Variablen zu Datentypen, etwa xDatentypangabe erfolgt in der Regel implizit und wird nichtexplizit deklariert!





Bereichskalkül






Bereichskalkül






Bereichskalkül



• Atomare Formeln:• Prädikatanwendung Θ(t1, . . . , tn),Θ ∈ <,>,≤,≥, =,=, . . . Datentypprädikat, Terme tiZweistellige Prädikate wie üblich in Infix-Notation.Beispiele: x = y, 42 > x oder 3+ 7 = 11.


Bereichskalkül /2

• Atomare Formeln (fortg.):

• Prädikatanwendungen für Datenbankprädikate, notiert alsR(t1, . . . , tn) für einen Relationennamen RVoraussetzung: n muss die Stelligkeit der Relation R seinund alle ti müssen vom passenden Typ seinBeispiel: ERZEUGER(x, ’Hessen’, z)

• Formeln wie üblich mit ∧, ∨, ¬, ∀ und ∃


Bereichskalkül /2

• Atomare Formeln (fortg.):• Prädikatanwendungen für Datenbankprädikate, notiert alsR(t1, . . . , tn) für einen Relationennamen RVoraussetzung: n muss die Stelligkeit der Relation R seinund alle ti müssen vom passenden Typ seinBeispiel: ERZEUGER(x, ’Hessen’, z)



Bereichskalkül /2

• Atomare Formeln (fortg.):• Prädikatanwendungen für Datenbankprädikate, notiert alsR(t1, . . . , tn) für einen Relationennamen RVoraussetzung: n muss die Stelligkeit der Relation R seinund alle ti müssen vom passenden Typ seinBeispiel: ERZEUGER(x, ’Hessen’, z)



Bereichskalkül /3

• Anfragen: x1, . . . , xn | ϕ(x1, . . . , xn)

• ϕ ist Formel über den in der Ergebnisliste aufgeführtenVariablen x1 bis xn

• Ergebnis ist eine Menge von Tupeln• Tupelkonstruktion erfolgt implizit aus den Werten derVariablen in der Ergebnisliste

• Beispiel

x | ERZEUGER(x, y, z) ∧ z = ’Hessen’


Bereichskalkül /3

• Anfragen: x1, . . . , xn | ϕ(x1, . . . , xn)• ϕ ist Formel über den in der Ergebnisliste aufgeführtenVariablen x1 bis xn


• Beispiel



Bereichskalkül /3


• Ergebnis ist eine Menge von Tupeln

• Tupelkonstruktion erfolgt implizit aus den Werten derVariablen in der Ergebnisliste

• Beispiel



Bereichskalkül /3



• Beispiel



Bereichskalkül /3



• Beispiel



Basiskalkül

• Einschränkung des Bereichskalküls:• Wertebereich: ganze Zahlen• Datentypprädikate werden wie bei der Relationenalgebraauf Gleichheit und elementare Vergleichsoperatoreneingeschränkt

• Funktionsanwendungen sind nicht erlaubt; nur Konstantendürfen neben Bereichsvariablen als Terme verwendetwerden


Sichere Anfragen

Semantisch sichere AnfragenAnfragen, die für jeden Datenbankzustand σ(R) einendliches Ergebnis liefern

• Beispiel für nicht sichere Anfrage:

x, y | ¬R(x, y)

• Beispiel für sichere Anfrage:

x, y | R(x, y)


Sichere Anfragen



x, y | ¬R(x, y)


x, y | R(x, y)


Sichere Anfragen



x, y | ¬R(x, y)


x, y | R(x, y)


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 SicherheitSemantische Sicherheit ist im Allgemeinen nichtentscheidbar!


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 SicherheitSemantische Sicherheit ist im Allgemeinen nichtentscheidbar!


Syntaktisch sichere Anfragen

• Syntaktisch sichere Anfragen: Anfragen, die syntaktischenEinschränkungen unterliegen, um die semantischeSicherheit zu erzwingen.

• Grundidee:

Syntaktische SicherheitJede freie Variable xi muss überall in ϕ(x1, . . . ) durchpositives Auftreten xi = t oder R(. . . , xi, . . . ) an endlicheBereiche gebunden werden.

• Bindung an endliche Bereiche muss für die ganzeBedingung gelten, also insbesondere für alle Zweige einerDisjunktion




• Grundidee:

Syntaktische SicherheitJede freie Variable xi muss überall in ϕ(x1, . . . ) durchpositives Auftreten xi = t oder R(. . . , xi, . . . ) an endlicheBereiche gebunden werden.





• Grundidee:Syntaktische SicherheitJede freie Variable xi muss überall in ϕ(x1, . . . ) durchpositives Auftreten xi = t oder R(. . . , xi, . . . ) an endlicheBereiche gebunden werden.





• Grundidee:Syntaktische SicherheitJede freie Variable xi muss überall in ϕ(x1, . . . ) durchpositives Auftreten xi = t oder R(. . . , xi, . . . ) an endlicheBereiche gebunden werden.



Sichere Anfragen im Überblick

syntaktisch sichere Anfragen

sichere Anfragen

Anfragen


Beispiele für Bereichskalkül

Beispiele Bereichskalkül

• Anfrage: „Alle Weingüter von Erzeugern in Hessen.“


• Vereinfachte Notation: Ansonsten ungebundene Variablen(hier y und z) im Bedingungsteil existentiell mit ∃gebunden

• Vollständige Version:

x | ∃y∃zERZEUGER(x, y, z) ∧ z = ’Hessen’

• Einsparung von Bereichsvariablen, indem Konstanten alsParameter des Prädikats eingesetzt werden:

x | ERZEUGER(x, y, ’Hessen’)





























Beispiele Bereichskalkül /2

• Abkürzung für beliebige, unterschiedliche existentiellgebundene Variablen ist _ Symbol:

x | ERZEUGER(x, _, z) ∧ z = ’Hessen’

• Verschiedene Auftreten des Symbols _ stehen hierbei fürpaarweise verschiedene Variablen



• Anfrage: „Regionen mit mehr als zwei Weingütern.“

z | ERZEUGER(x, y, z) ∧ ERZEUGER(x′, y′, z) ∧ x = x′

• Anfrage zeigt eine Verbundbildung über das dritte Attributder ERZEUGER-Relation

• Verbundbildung kann einfach durch die Verwendung derselben Bereichsvariablen als Parameter in verschiedenenRelationsprädikaten erfolgen















• Anfrage: „Aus welcher Region sind welche Weine mitJahrgang vor 1970 im Angebot?“

y, r | WEINE(x, y, z, j,w) ∧ ERZEUGER(w,a, r) ∧ j < 1970

• Verbund über zwei Relationen



• Anfrage: „Aus welcher Region sind welche Weine mitJahrgang vor 1970 im Angebot?“

y, r | WEINE(x, y, z, j,w) ∧ ERZEUGER(w,a, r) ∧ j < 1970

• Verbund über zwei Relationen



• Anfrage: „Aus welchen Regionen gibt es Rotweine?“

z | ERZEUGER(x, y, z)∧∃a∃b∃c∃d(WEIN(a,b, c,d, x)∧c = ’Rot’)

• Einsatz einer existentiell gebundenen Unteranfrage• derartige Unteranfragen können aufgrund der Regeln derPrädikatenlogik wie folgt aufgelöst werden:

z | ERZEUGER(x, y, z) ∧ (WEIN(a,b, c,d, x) ∧ c = ’Rot’)





• Einsatz einer existentiell gebundenen Unteranfrage

• derartige Unteranfragen können aufgrund der Regeln derPrädikatenlogik wie folgt aufgelöst werden:






• Einsatz einer existentiell gebundenen Unteranfrage• derartige Unteranfragen können aufgrund der Regeln derPrädikatenlogik wie folgt aufgelöst werden:




• Anfrage: „Welches Weingut hat nur Weine mit Jahrgangnach 1995 im Angebot?“

x | ERZEUGER(x, y, z) ∧ ∀a∀b∀c∀d(WEIN(a,b, c,d, x) =⇒ d > 1995)

• universell gebundene Teilformeln können nicht aufgelöstwerden



• Anfrage: „Welches Weingut hat nur Weine mit Jahrgangnach 1995 im Angebot?“

x | ERZEUGER(x, y, z) ∧ ∀a∀b∀c∀d(WEIN(a,b, c,d, x) =⇒ d > 1995)

• universell gebundene Teilformeln können nicht aufgelöstwerden


Eigenschaften des Bereichskalküls

Ausdrucksfähigkeit Bereichskalkül

Ausdrucksfähigkeit des BereichskalkülsBereichskalkül ist streng relational vollständig, d.h. zu jedemTerm τ der Relationenalgebra gibt es einen äquivalenten(sicheren) Ausdruck η des Bereichskalküls.


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 . . . xnxn+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 dieVerbundattribute, also Aj = Bj für j = 1 . . . i





R ∪ S = x1 . . . xn | R(x1, . . . , xn) ∨ S(x1, . . . , xn)


R− S = x1 . . . xn | R(x1, . . . , xn) ∧ ¬S(x1, . . . , xn)








R ∪ S = x1 . . . xn | R(x1, . . . , xn) ∨ S(x1, . . . , xn)


R− S = x1 . . . xn | R(x1, . . . , xn) ∧ ¬S(x1, . . . , xn)





Umsetzung von Relationenoperationen /2

• Projektion

π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 derAttributnamen Ai eingesetzt werden


Umsetzung von Relationenoperationen /2

• Projektion

π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 derAttributnamen Ai eingesetzt werden


Zusammenfassung

• formale Modelle für Anfragen in Datenbanksystemen• Relationenalgebra

• operationaler Ansatz• Anfrage als Schachtelung von Operatoren auf Relationen

• Anfragekalküle• logikbasierter Ansatz• Anfragen als abgeleitete Prädikate• im Buch: Abschnitte 4.2.3, 4.2.4 und 9.3


Kontrollfragen

• Welche Bedeutung haben Äquivalenz,Unabhängigkeit und Vollständigkeit in derRelationenalgebra?

• Wie lässt sich die Semantik vonerweiterten SQL-Operationen in derRelationenalgebra ausdrücken?

• Was unterscheidet Relationenalgebra undrelationale Anfragekalküle?

• Welche Rolle spielt die Sicherheit vonAnfragen?


Kontrollfragen






Kontrollfragen






Kontrollfragen






Teil IX

Transaktionen, Integrität und Trigger


1. Grundbegriffe

2. Transaktionsbegriff

3. Transaktionen in SQL

4. Integritätsbedingungen in SQL

5. Trigger

6. Schemaevolution



1. Grundbegriffe




5. Trigger

6. Schemaevolution



1. Grundbegriffe




5. Trigger

6. Schemaevolution



1. Grundbegriffe




5. Trigger

6. Schemaevolution



1. Grundbegriffe




5. Trigger

6. Schemaevolution



1. Grundbegriffe




5. Trigger

6. Schemaevolution



• Verständnis des Transaktionskonzeptes inDatenbanken

• Verständnis der Grundlagen derIntegritätssicherung in Datenbanken

• Kenntnisse zur Formulierung undImplementierung vonIntegritätsbedingungen sowieSchemaänderungen












Grundbegriffe

Integrität

• Integritätsbedingung (engl. integrity constraint oderassertion): Bedingung für die „Zulässigkeit“ oder„Korrektheit“

• in Bezug auf Datenbanken:

• (einzelne) Datenbankzustände,• Zustandsübergänge vom alten in den neuenDatenbankzustand,

• langfristige Datenbankentwicklungen


Integrität


• in Bezug auf Datenbanken:

• (einzelne) Datenbankzustände,• Zustandsübergänge vom alten in den neuenDatenbankzustand,



Integrität


• in Bezug auf Datenbanken:• (einzelne) Datenbankzustände,

• Zustandsübergänge vom alten in den neuenDatenbankzustand,



Integrität


• in Bezug auf Datenbanken:• (einzelne) Datenbankzustände,• Zustandsübergänge vom alten in den neuenDatenbankzustand,



Integrität


• in Bezug auf Datenbanken:• (einzelne) Datenbankzustände,• Zustandsübergänge vom alten in den neuenDatenbankzustand,



Klassifikation von Integrität

Bedingungsklasse zeitlicher Kontextstatisch Datenbankzustanddynamisch transitional Zustandsübergang

temporal Zustandsfolge


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:

• Angabe eines Schlüssels für eine Relation

3. Referentielle Integrität:

• die Angabe von Fremdschlüsseln



1. Typintegrität:• SQL erlaubt Angabe von Wertebereichen zu Attributen

• Erlauben oder Verbieten von Nullwerten2. Schlüsselintegrität:






1. Typintegrität:• SQL erlaubt Angabe von Wertebereichen zu Attributen• Erlauben oder Verbieten von Nullwerten









• Angabe eines Schlüssels für eine Relation3. Referentielle Integrität:





2. Schlüsselintegrität:• Angabe eines Schlüssels für eine Relation













3. Referentielle Integrität:• die Angabe von Fremdschlüsseln


Transaktionsbegriff

Beispielszenarien

• Platzreservierung für Flüge gleichzeitig aus vielenReisebüros→ Platz könnte mehrfach verkauft werden, wenn mehrereReisebüros den Platz als verfügbar identifizieren

• überschneidende Kontooperationen einer Bank• statistische Datenbankoperationen→ Ergebnisse sind verfälscht, wenn während derBerechnung Daten geändert werden


Beispielszenarien


• überschneidende Kontooperationen einer Bank

• statistische Datenbankoperationen→ Ergebnisse sind verfälscht, wenn während derBerechnung Daten geändert werden


Beispielszenarien


• überschneidende Kontooperationen einer Bank• statistische Datenbankoperationen→ Ergebnisse sind verfälscht, wenn während derBerechnung Daten geändert werden


Transaktion

TransaktionEine Transaktion ist eine Folge von Operationen (Aktionen),die die Datenbank von einem konsistenten Zustand in einenkonsistenten, 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“ Zugriffmehrerer Benutzer auf dieselben Daten vermeiden


Transaktion


• Aspekte:

• Semantische Integrität: Korrekter (konsistenter)DB-Zustand nach Ende der Transaktion



Transaktion


• Aspekte:• Semantische Integrität: Korrekter (konsistenter)DB-Zustand nach Ende der Transaktion



Transaktion


• Aspekte:• Semantische Integrität: Korrekter (konsistenter)DB-Zustand nach Ende der Transaktion



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 einerTransaktion jeweils in einem konsistenten Zustand

• Isolation (Isolation):Nutzer, der mit einer Datenbank arbeitet, sollte denEindruck haben, dass er mit dieser Datenbank alleinearbeitet

• Durability (Dauerhaftigkeit / Persistenz):nach erfolgreichem Abschluss einer Transaktion muss dasErgebnis dieser Transaktion „dauerhaft“ in der Datenbankgespeichert werden


ACID-Eigenschaften






ACID-Eigenschaften






ACID-Eigenschaften






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




• commit: die Transaktion soll erfolgreich beendet werden

• abort: die Transaktion soll abgebrochen werden




• commit: die Transaktion soll erfolgreich beendet werden• abort: die Transaktion soll abgebrochen werden


Transaktion: Integritätsverletzung

• Beispiel:

• Übertragung eines Betrages B von einem HaushaltspostenK1 auf einen anderen Posten K2

• Bedingung: Summe der Kontostände der Haushaltspostenbleibt 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!



• Beispiel:• Übertragung eines Betrages B von einem HaushaltspostenK1 auf einen anderen Posten K2























• vereinfachte NotationTransfer = < K1:=K1-B; K2:=K2+B >;






• vereinfachte NotationTransfer = < K1:=K1-B; K2:=K2+B >;



Transaktion: Verhalten bei Systemabsturz

1T

2T

3T

4T

5T

tZeit

Fehler

f


Transaktion: Verhalten bei Systemabsturz /2

• Folgen:

• Inhalt des flüchtigen Speichers zum Zeitpunkt tf istunbrauchbar→ Transaktionen in unterschiedlicher Weisedavon betroffen

• Transaktionszustände:

• zum Fehlerzeitpunkt noch aktive Transaktionen (T2 und T4)• bereits vor dem Fehlerzeitpunkt beendete Transaktionen(T1, T3 und T5)



• Folgen:• Inhalt des flüchtigen Speichers zum Zeitpunkt tf istunbrauchbar→ Transaktionen in unterschiedlicher Weisedavon betroffen











• Transaktionszustände:• zum Fehlerzeitpunkt noch aktive Transaktionen (T2 und T4)

• bereits vor dem Fehlerzeitpunkt beendete Transaktionen(T1, T3 und T5)




• Transaktionszustände:• zum Fehlerzeitpunkt noch aktive Transaktionen (T2 und T4)• bereits vor dem Fehlerzeitpunkt beendete Transaktionen(T1, T3 und T5)


Vereinfachtes Modell für Transaktion

• Repräsentation von Datenbankänderungen einerTransaktion

• read(A,x): weise den Wert des DB-Objektes A der Variablenx zu

• write(x, A): speichere den Wert der Variablen x imDB-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












































• Ausführungsvarianten für zwei Transaktionen T1, T2:• seriell, etwa T1 vor T2

• „gemischt“, etwa abwechselnd Schritte von T1 und T2








• Ausführungsvarianten für zwei Transaktionen T1, T2:• seriell, etwa T1 vor T2• „gemischt“, etwa abwechselnd Schritte von T1 und T2


Probleme im Mehrbenutzerbetrieb

• Inkonsistentes Lesen: Nonrepeatable Read• Abhängigkeiten von nicht freigegebenen Daten: Dirty Read• Das Phantom-Problem• Verlorengegangenes Ändern: Lost Update


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


Beispiel für inkonsistentes Lesen

T1 T2read(A, x);

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;


Dirty Read

T1 T2read(A, x);x := x+ 100;write(x,A);

read(A, x);read(B, y);y := y+ x;write(y,B);commit;

abort;


Das Phantom-Problem

T1 T2select count (*)into Xfrom Kunde;

insertinto Kundevalues (’Meier’, 0, …);commit;

update Kundeset Bonus =

Bonus +10000/X;commit;


Lost Update

T1 T2 Aread(A, x); 10

read(A, x); 10x := x+ 1; 10

x := x+ 1; 10write(x,A); 11

write(x,A); 11


Serialisierbarkeit

SerialisierbarkeitEine verschränkte Ausführung mehrerer Transaktionen heißtserialisierbar, wenn ihr Effekt identisch zum Effekt einer(beliebig gewählten) seriellen Ausführung dieserTransaktionen ist.

• Schedule: „Ablaufplan“ für Transaktion, bestehend ausAbfolge von Transaktionsoperationen


Serialisierbarkeit

SerialisierbarkeitEine verschränkte Ausführung mehrerer Transaktionen heißtserialisierbar, wenn ihr Effekt identisch zum Effekt einer(beliebig gewählten) seriellen Ausführung dieserTransaktionen ist.

• Schedule: „Ablaufplan“ für Transaktion, bestehend ausAbfolge von Transaktionsoperationen


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


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


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 anderenTransaktionen werden behindert

• read committed

• nur Lesen endgültig geschriebener Werte, abernonrepeatable read möglich

• repeatable read

• kein nonrepeatable read, aber Phantomproblem kannauftreten

• serializable

• garantierte Serialisierbarkeit



• read uncommitted• schwächste Stufe: Zugriff auf nicht geschriebene Daten,nur für read only Transaktionen



• read committed


• repeatable read


• serializable







• read committed


• repeatable read


• serializable







• read committed


• repeatable read


• serializable







• read committed


• repeatable read


• serializable







• read committed• nur Lesen endgültig geschriebener Werte, abernonrepeatable read möglich

• repeatable read


• serializable








• repeatable read


• serializable








• repeatable read• kein nonrepeatable read, aber Phantomproblem kannauftreten

• serializable









• serializable









• serializable• garantierte Serialisierbarkeit


Isolationsebenen: read committed

T1 T2set transaction

isolation levelread committed

1 select Name from WEINE whereWeinID = 1014−→ Riesling

2 update WEINEset Name = 'Riesling Superiore'where WeinID = 1014


4 commit5 select Name from WEINE where

WeinID = 1014−→ Riesling Superiore



































4 commit

5 select Name from WEINE whereWeinID = 1014−→ Riesling Superiore











read committed /2



1 select Name from WEINE whereWeinID = 1014

2 update WEINEset Name = 'Riesling Superore'where WeinID = 1014

3 update WEINEset Name = 'Superiore Riesling'where WeinID = 1014−→ blockiert

4 commit5 commit


read committed /2






4 commit5 commit


read committed /2






4 commit5 commit


read committed /2






4 commit5 commit


read committed /2






4 commit5 commit


Isolationsebenen: serializable


isolation levelserializable

1 select Name into N fromWEINE where WeinID = 1014

−→ N := Riesling2 update WEINE

set Name = 'Riesling Superiore'where WeinID = 1014

4 commit5 update WEINE

set Name = 'Superior' || Nwhere WeinID = 1014−→ Abbruch






−→ N := Riesling




















4 commit

5 update WEINEset Name = 'Superior' || Nwhere WeinID = 1014−→ Abbruch











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



• not null: Nullwerte verboten• default: Angabe von Default-Werten

• check ( search-condition ): Attributspezifische Bedingung(in der Regel Ein-Tupel-Integritätsbedingung)




• 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






Integritätsbedingungen: Wertebereiche

• create domain: Festlegung eines benutzerdefiniertenWertebereichs

• Beispiel

create domain WeinFarbe varchar(4)default 'Rot'check (value in ('Rot', 'Weiß', 'Rose'))

• Anwendung

create table WEINE (WeinID int primary key,Name varchar(20) not null,Farbe WeinFarbe, …)




• Beispiel


• Anwendung





• Beispiel


• Anwendung



Integritätsbedingungen: check-Klausel

• check: Festlegung weitere lokale Integritätsbedingungeninnerhalb der zu definierenden Wertebereiche, Attributeund Relationenschemata

• Beispiel: Einschränkung der zulässigen Werte• Anwendung

create table WEINE (WeinID int primary key,Name varchar(20) not null,Jahr int check(Jahr between 1980 and 2010),…

)




• Beispiel: Einschränkung der zulässigen Werte

• Anwendung


)




• Beispiel: Einschränkung der zulässigen Werte• Anwendung


)


Erhaltung der referentiellen Integrität

• Überprüfung der Fremdschlüsselbedingungen nachDatenbankänderungen

• für πA(r1) ⊆ πK(r2),z.B. πWeingut(WEINE) ⊆ πWeingut(ERZEUGER)

• Tupel t wird eingefügt in r1 ⇒ überprüfen, ob t′ ∈ r2existiert 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)




















Überprüfungsmodi von Bedingungen

• on update | deleteAngabe eines Auslöseereignisses, das die Überprüfungder Bedingung anstößt

• cascade | set null | set default | no actionKaskadierung: Behandlung einiger Integritätsverletzungenpflanzt sich über mehrere Stufen fort, z.B. Löschen alsReaktion auf Verletzung der referentieller Integrität

• deferred | immediate legt Überprüfungszeitpunkt füreine Bedingung fest

• deferred: Zurückstellen an das Ende der Transaktion• immediate: sofortige Prüfung bei jeder relevantenDatenbankänderung


















• deferred: Zurückstellen an das Ende der Transaktion

• immediate: sofortige Prüfung bei jeder relevantenDatenbankänderung








Überprüfungsmodi: Beispiel

• Kaskadierendes Löschen

create table WEINE (WeinID int primary key,Name varchar(50) not null,Preis float not null,Jahr int not null,Weingut varchar(30),foreign key (Weingut)

references ERZEUGER (Weingut)on delete cascade)


Die assertion-Klausel

• Assertion: Prädikat, das eine Bedingung ausdrückt, die vonder Datenbank immer erfüllt sein muss

• Syntax (SQL:2003)

create assertion name check ( prädikat )

• Beispiele:

create assertion Preise check( ( select sum (Preis)

from WEINE) < 10000 );create assertion Preise2 check

( not exists (select * from WEINE where Preis > 200) )






• Beispiele:









• Beispiele:





Trigger

Trigger

• Trigger: Anweisung/Prozedur, die bei Eintreten einesbestimmten Ereignisses automatisch vom DBMSausgeführt wird

• Anwendung:

• Erzwingen von Integritätsbedingungen („Implementierung“von Integritätsregeln)

• Auditing von DB-Aktionen• Propagierung von DB-Änderungen

• Definition:create trigger …after OperationAnweisungen


Trigger


• Anwendung:

• Erzwingen von Integritätsbedingungen („Implementierung“von Integritätsregeln)




Trigger


• Anwendung:• Erzwingen von Integritätsbedingungen („Implementierung“von Integritätsregeln)




Trigger



• Auditing von DB-Aktionen

• Propagierung von DB-Änderungen• Definition:create trigger …after OperationAnweisungen


Trigger






Trigger






Beispiel für Trigger

• Realisierung eines berechneten Attributs durch zweiTrigger:

• Einfügen von neuen Aufträgen

create trigger Auftragszählung+on insertion of Auftrag A:update Kundeset AnzAufträge = AnzAufträge + 1where KName = new A.KName

• analog für Löschen von Aufträgen:

create trigger Auftragszählung-on deletion …:update …- 1 …
















Trigger: Entwurf und Implementierung

• Spezifikation von

• Ereignis und Bedingung für Aktivierung des Triggers• Aktion(en) zur Ausführung

• Syntax in SQL:2003 festgelegt• verfügbar in den meisten kommerziellen Systemen (abermit anderer Syntax)



• Spezifikation von• Ereignis und Bedingung für Aktivierung des Triggers

• Aktion(en) zur Ausführung




• Spezifikation von• Ereignis und Bedingung für Aktivierung des Triggers• Aktion(en) zur Ausführung





• Syntax in SQL:2003 festgelegt

• verfügbar in den meisten kommerziellen Systemen (abermit anderer Syntax)






SQL:2003-Trigger

• Syntax:

create trigger Nameafter | before Ereignison Relation[ when Bedingung ]

begin atomic SQL-Anweisungen end

• Ereignis:

• insert• update [ of Attributliste ]• delete


SQL:2003-Trigger

• Syntax:



• Ereignis:

• insert• update [ of Attributliste ]• delete


SQL:2003-Trigger

• Syntax:



• Ereignis:• insert

• update [ of Attributliste ]• delete


SQL:2003-Trigger

• Syntax:



• Ereignis:• insert• update [ of Attributliste ]

• delete


SQL:2003-Trigger

• Syntax:



• Ereignis:• insert• update [ of Attributliste ]• delete


Weitere Angaben bei Triggern

• for each row bzw. for each statement: Aktivierungdes Triggers für jede Einzeländerungen einermengenwertigen Änderung oder nur einmal für diegesamte Ä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













• Kein Kundenkonto darf unter 0 absinken:

create trigger bad_accountafter update of Kto on KUNDEreferencing new as INSERTEDwhen (exists

(select * from INSERTED where Kto < 0))begin atomic

rollback;end

ähnlicher Trigger für insert






rollback;end







rollback;end



Beispiel für Trigger /2

• Erzeuger müssen gelöscht werden, wenn sie keine Weinemehr anbieten:

create trigger unnützes_Weingutafter delete on WEINEreferencing old as Ofor each rowwhen (not exists

(select * from WEINE Wwhere W.Weingut = O.Weingut))

begin atomicdelete from ERZEUGER where Weingut = O.Weingut;

end


Beispiel für Trigger /2

• Erzeuger müssen gelöscht werden, wenn sie keine Weinemehr anbieten:

create trigger unnützes_Weingutafter delete on WEINEreferencing old as Ofor each rowwhen (not exists

(select * from WEINE Wwhere W.Weingut = O.Weingut))

begin atomicdelete from ERZEUGER where Weingut = O.Weingut;

end


Integritätssicherung durch Trigger

1. Bestimme Objekt oi, für das die Bedingung ϕ überwachtwerden soll

• i.d.R. mehrere oi betrachten, wenn Bedingungrelationsübergreifend ist

• Kandidaten für oi sind Tupel der Relationsnamen, die in ϕ

auftauchen2. Bestimme die elementaren Datenbankänderungen uij aufObjekten oi, die ϕ verletzen können

• Regeln: z.B. Existenzforderungen beim Löschen und Ändernprüfen, jedoch nicht beim Einfügen etc.













auftauchen

2. Bestimme die elementaren Datenbankänderungen uij aufObjekten oi, die ϕ verletzen können

















Integritätssicherung durch Trigger /2

3. Bestimme je nach Anwendung die Reaktion ri aufIntegritätsverletzung

• Rücksetzen der Transaktion (rollback)• korrigierende Datenbankänderungen

4. Formuliere folgende Trigger:

create trigger t-phi-ij after uij on oiwhen ¬ϕbegin ri end

5. Wenn möglich, vereinfache entstandenen Trigger




• Rücksetzen der Transaktion (rollback)

• korrigierende Datenbankänderungen


























Trigger in Oracle

• Implementierung in PL/SQL• Notationcreate [ or replace ] trigger trigger-name

before | afterinsert or update [ of spalten ]

or delete on tabelle[ for each row[ when ( prädikat ) ] ]PL/SQL-Block


Trigger in Oracle: Arten

• Anweisungsebene (statement level trigger): Trigger wirdausgelöst vor bzw. nach der DML-Anweisung

• Tupelebene (row level trigger): Trigger wird vor bzw. nachjeder einzelnen Modifikation ausgelöst (one tuple at atime)

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)


Trigger in Oracle /2

• Transaktionsabbruch durchraise_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;


Trigger in Oracle: Beispiel


create or replace trigger bad_accountafter insert or update of Kto on KUNDEfor each rowwhen (:new.Kto < 0)begin

raise_application_error(-20221,'Nicht unter 0');

end;


Schemaevolution

Schemaevolution und Datenbankmigration

• Änderung eines Datenbankschemas durchneue/veränderte Anforderungen

• Hinzufügen oder Löschen von Tabellen, Spalten,Integritätsbedingungen

• Umbenennen oder Datentypänderungen

• erfordert oft auch Anpassung/Übertragung dervorhandenen Datenbank Datenbankmigration

• leider nur eingeschränkte Unterstützung durchDB-Werkzeuge (DDL + Export/Import der Daten)






























SQL-DDL zum Löschen von Tabellen

• Löschen von Tabellendefinitionen(beachte Unterschied zu delete)

drop table relationenname [ restrict | cascade ]

• cascade: erzwingt Löschen aller Sichten undIntegritätsbedingungen, die zu dieser Basisrelationgehören

• restrict (Defaultfall): das drop-Kommando wirdzurückgewiesen, falls noch solche Sichten undIntegritätsbedingungen existieren














SQL-DDL zur Änderung von Tabellen

alter table relationenname modifikation

• add column spaltendefinition fügt eine neue Spaltehinzu; alle bereits in der Tabelle existierenden Tupelerhalten als Wert der neuen Spalte den angegebenenDefaultwert bzw. den null-Wert

• drop column spaltenname löscht die angegebene Spalte(inkl. restrict- bzw. cascade)

• alter column spaltenname set default defaultwertverändert Defaultwert der Spalte




















Änderung von Tabellen: Beispiele

alter table WEINEadd column Preis decimal(5,2)

alter table WEINEalter column Jahrgang set default 2007


Änderung von Tabellen: Beispiele

alter table WEINEadd column Preis decimal(5,2)

alter table WEINEalter column Jahrgang set default 2007


Änderung von Integritätsbedingungen

• nachträgliches Hinzufügen/Löschen vonTabellenbedingungen über alter table

• Vergabe von Namen für Bedingungen über constraintbed-name-Klausel

alter table WEINEadd constraint WeinBed_Eindeutigunique (Name, Weingut)

• Löschen über Namenalter table WEINE

drop constraint WeinBed_Eindeutig




















• Löschen über Namen

alter table WEINEdrop constraint WeinBed_Eindeutig






• Löschen über Namen

alter table WEINEdrop constraint WeinBed_Eindeutig









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ätsbedingungenbzw. -regeln


Kontrollfragen

• Welchem Zweck dient dieIntegritätssicherung? Welche Formen vonIntegritätsbedingungen gibt es?

• Wie lassen sich Integritätsbedingungenund -regeln in SQL-Systemenformulieren?

• Welche Forderungen ergeben sich ausdem ACID-Prinzip? Wie werden diese inDatenbanksystemen erreicht?


Kontrollfragen





Kontrollfragen





Teil X

Sichten und Zugriffskontrolle


1. Sichtenkonzept

2. Änderungen auf Sichten

3. Rechtevergabe

4. Privacy-Aspekte



1. Sichtenkonzept


3. Rechtevergabe

4. Privacy-Aspekte



1. Sichtenkonzept


3. Rechtevergabe

4. Privacy-Aspekte



1. Sichtenkonzept


3. Rechtevergabe

4. Privacy-Aspekte



• Verständnis des Sichtenkonzeptes vonDatenbanken

• Kenntnisse zur Formulierung und Nutzungvon Sichten in SQL

• Verständnis der Probleme beiÄnderungen über Sichten

• Verständnis zu Datenschutzaspekten imZusammenhang mitaggregierten/statistischen Daten




















Sichtenkonzept

Sichten

Sichtenvirtuelle Relationen (bzw virtuelle Datenbankobjekte inanderen Datenmodellen) (englisch view)

• Sichten sind externe DB-Schemata folgend der3-Ebenen-Schemaarchitektur

• Sichtdefinition

• Relationenschema (implizit oder explizit)• Berechnungsvorschrift für virtuelle Relation, etwaSQL-Anfrage


Sichten



• Sichtdefinition



Sichten



• Sichtdefinition



Sichten



• Sichtdefinition• Relationenschema (implizit oder explizit)

• Berechnungsvorschrift für virtuelle Relation, etwaSQL-Anfrage


Sichten



• Sichtdefinition• Relationenschema (implizit oder explizit)• Berechnungsvorschrift für virtuelle Relation, etwaSQL-Anfrage


Sichten /2

• Vorteile

• Vereinfachung von Anfragen für den Benutzer derDatenbank, etwa indem oft benötigte Teilanfragen als Sichtrealisiert werden

• Möglichkeit der Strukturierung derDatenbankbeschreibung, zugeschnitten aufBenutzerklassen

• logische Datenunabhängigkeit ermöglicht Stabilität derSchnittstelle für Anwendungen gegenüber Änderungen derDatenbankstruktur

• Beschränkung von Zugriffen auf eine Datenbank imZusammenhang mit der Zugriffskontrolle

• Probleme

• automatische Anfragetransformation• Durchführung von Änderungen auf Sichten


Sichten /2

• Vorteile• Vereinfachung von Anfragen für den Benutzer derDatenbank, etwa indem oft benötigte Teilanfragen als Sichtrealisiert werden




• Probleme



Sichten /2





• Probleme



Sichten /2





• Probleme



Sichten /2





• Probleme



Sichten /2





• Probleme



Sichten /2





• Probleme• automatische Anfragetransformation

• Durchführung von Änderungen auf Sichten


Sichten /2





• Probleme• automatische Anfragetransformation• Durchführung von Änderungen auf Sichten


Drei-Ebenen-Schema-Architektur

Konzeptuelles Schema

externesSchema 1

externesSchema N

internesSchema

...

Anfragebearbeitung

Datendarstellung


Definition von Sichten in SQL

create view SichtName [ SchemaDeklaration ]as SQLAnfrage[ with check option ]


Sichten - Beispiel

• alle Rotweine aus Bordeaux

create view Rotweine asselect Name, Jahrgang, WEINE.Weingutfrom WEINE natural join ERZEUGERwhere Farbe = 'Rot'

and Region = 'Bordeaux'


Sichten - Beispiel

• alle Rotweine aus Bordeaux

create view Rotweine asselect Name, Jahrgang, WEINE.Weingutfrom WEINE natural join ERZEUGERwhere Farbe = 'Rot'

and Region = 'Bordeaux'


Änderungen auf Sichten

Kriterien für Änderungen auf Sichten

• EffektkonformitätBenutzer sieht Effekt als wäre die Änderung auf derSichtrelation direkt ausgeführt worden

• MinimalitätBasisdatenbank sollte nur minimal geändert werden, umden erwähnten Effekt zu erhalten

• KonsistenzerhaltungÄnderung einer Sicht darf zu keinenIntegritätsverletzungen der Basisdatenbank führen

• Respektierung des DatenschutzesWird die Sicht aus Datenschutzgründen eingeführt, darfder bewusst ausgeblendete Teil der Basisdatenbank vonÄnderungen der Sicht nicht betroffen werden




















Projektionssicht

WNW := πWeinID,Name,Weingut(WEINE)

• In SQL mit create view-Anweisung:create view WNW as

select WeinID, Name, Weingut from WEINE


Projektionssicht /2

• Änderungsanweisung für die Sicht WNW:insert into WNWvalues (3333, 'Dornfelder', 'Müller')

• Korrespondierende Anweisung auf der BasisrelationWEINE:insert into WEINE

values (3333, 'Dornfelder',null, null, 'Müller')

→ Problem der Konsistenzerhaltung falls Farbe oderJahrgang als not null deklariert!


Projektionssicht /2

• Änderungsanweisung für die Sicht WNW:insert into WNWvalues (3333, 'Dornfelder', 'Müller')

• Korrespondierende Anweisung auf der BasisrelationWEINE:insert into WEINE

values (3333, 'Dornfelder',null, null, 'Müller')

→ Problem der Konsistenzerhaltung falls Farbe oderJahrgang als not null deklariert!


Selektionssichten

WJ := σJahrgang>2000(πWeinID,Jahrgang(WEINE))

create view WJ asselect WeinID, Jahrgangfrom WEINEwhere Jahrgang > 2000


Selektionssichten /2

• Tupelmigration: TupelWEINE(3456, ’Zinfandel’, ’Rot’, 2004, ’Helena’), wird aus derSicht „herausbewegt“:

update WJset Jahrgang = 1998where WeinID = 3456


Kontrolle der Tupelmigration

create view WJ asselect WeinID, Jahrgangfrom WEINEwhere Jahrgang > 2000with check option


Verbundsichten

WE := WEINE ▷◁ ERZEUGER

• In SQL:

create view WE asselect WeinID, Name, Farbe, Jahrgang,

WEINE.Weingut, Anbaugebiet, Regionfrom WEINE, ERZEUGERwhere WEINE.Weingut = ERZEUGER.Weingut


Verbundsichten /2

• Änderungsoperationen hier in der Regel nicht eindeutigübersetzbar:insert into WEvalues (3333, 'Dornfelder', 'Rot', 2002,

'Helena', 'Barossa Valley', 'Südaustralien')

• Änderung wird transformiert zu

insert into WEINEvalues (3333, 'Dornfelder', 'Rot', 2002,

'Helena')

• plus Änderung auf ERZEUGER !


Verbundsichten /2





'Helena')



Verbundsichten /2





'Helena')



Verbundsichten /3

• zusätzliche Aktionen auf ERZEUGER

1. Einfügeanweisung auf ERZEUGER:

insert into ERZEUGERvalues ('Helena', 'Barossa Valley', 'Südaustralien')

2. oder alternativ:

update ERZEUGERset Anbaugebiet = 'Barossa Valley',

Region = 'Südaustralien'where Weingut = 'Helena'

besser bzgl. Minimalitätsforderung, widerspricht aberEffektkonformität!


Verbundsichten /3

• zusätzliche Aktionen auf ERZEUGER1. Einfügeanweisung auf ERZEUGER:


2. oder alternativ:





Verbundsichten /3

• zusätzliche Aktionen auf ERZEUGER1. Einfügeanweisung auf ERZEUGER:


2. oder alternativ:





Aggregierungssichten

create view FM (Farbe, MinJahrgang) asselect Farbe, min(Jahrgang)from WEINEgroup by Farbe

• Folgende Änderung ist nicht eindeutig umsetzbar:

update FMset MinJahrgang = 1993where Farbe = 'Rot'


Aggregierungssichten

create view FM (Farbe, MinJahrgang) asselect Farbe, min(Jahrgang)from WEINEgroup by Farbe

• Folgende Änderung ist nicht eindeutig umsetzbar:

update FMset MinJahrgang = 1993where Farbe = 'Rot'


Klassifikation der Problembereiche

1. Verletzung der Schemadefinition (z.B. Einfügen vonNullwerten bei Projektionssichten)

2. Datenschutz: Seiteneffekte auf nicht-sichtbaren Teil derDatenbank vermeiden (Tupelmigration, Selektionssichten)

3. nicht immer eindeutige Transformation: Auswahlproblem4. Aggregierungssichten (u.a.): keine sinnvolleTransformation möglich

5. elementare Sichtänderung soll genau einer atomarenÄnderung auf Basisrelation entsprechen: 1:1-Beziehungzwischen Sichttupeln und Tupeln der Basisrelation (keinHerausprojizieren von Schlüsseln)











3. nicht immer eindeutige Transformation: Auswahlproblem

4. Aggregierungssichten (u.a.): keine sinnvolleTransformation möglich















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)



• SQL-92-Standard• Integritätsverletzende Sichtänderungen nicht erlaubt

• datenschutzverletzende Sichtänderungen:Benutzerkontrolle (with check option)




• SQL-92-Standard• Integritätsverletzende Sichtänderungen nicht erlaubt• datenschutzverletzende Sichtänderungen:Benutzerkontrolle (with check option)




• SQL-92-Standard• Integritätsverletzende Sichtänderungen nicht erlaubt• datenschutzverletzende Sichtänderungen:Benutzerkontrolle (with check option)



Einschränkungen für Sichtänderungen

• änderbar nur Selektions- und Projektionssichten(Verbund und Mengenoperationen nicht erlaubt)

• 1:1-Zuordnung von Sichttupeln zu Basistupeln: keindistinct in Projektionssichten

• Arithmetik und Aggregatfunktionen im select-Teil sindverboten

• genau eine Referenz auf einen Relationsnamen im from-Teil erlaubt (auch kein Selbstverbund)

• keine Unteranfragen mit „Selbstbezug“ im where-Teilerlaubt (Relationsname im obersten SFW-Block nicht infrom-Teilen von Unteranfragen verwenden)

• group by und having verboten










































Sichtänderungen in SQL:2003

• seit SQL:2003 Aufhebung einiger Einschränkungen,insbesondere

• Updates auf union all-Sichten (ohneDuplikateliminierung)

• Inserts in Verbundsichten mitPrimär-/Fremdschlüsselbeziehungen (mit einigenEinschränkungen)

• Updates auf Verbundsichten mit Cursor (siehe folgendesKapitel)




















Alternative: Sichtänderungen mit Instead-of-Triggern

• Definition von Triggern auf Sichten zuranwendungsspezifischen Propagierung der Änderungenauf die Basistabellen

create view V_WEINERZEUGER asselect * from WEINE natural join ERZEUGER;

create trigger V_WEINERZEUGER_Insertinstead of insert on V_WEINERZEUGER

referencing new as Nfor each rowbegin

insert into WEINE values (:N.WeinID, :N.Name,:N.Farbe, :N.Jahrgang, :N.Weingut);

end;


Alternative: Sichtänderungen mit Instead-of-Triggern

• Definition von Triggern auf Sichten zuranwendungsspezifischen Propagierung der Änderungenauf die Basistabellen

create view V_WEINERZEUGER asselect * from WEINE natural join ERZEUGER;

create trigger V_WEINERZEUGER_Insertinstead of insert on V_WEINERZEUGER

referencing new as Nfor each rowbegin

insert into WEINE values (:N.WeinID, :N.Name,:N.Farbe, :N.Jahrgang, :N.Weingut);

end;


Auswertung von Anfragen an Sichten

• select: Sichtattribute evtl. umbenennen bzw. durchBerechnungsterm ersetzen

• from: Namen der Originalrelationen• konjunktive Verknüpfung der where-Klauseln vonSichtdefinition und Anfrage (evtl. Umbenennungen)

• Vorsicht bei Aggregationssichten!

• having versus where• keine geschachtelten Aggregationen in SQL




• from: Namen der Originalrelationen

• konjunktive Verknüpfung der where-Klauseln vonSichtdefinition und Anfrage (evtl. Umbenennungen)



















• Vorsicht bei Aggregationssichten!• having versus where

• keine geschachtelten Aggregationen in SQL





• Vorsicht bei Aggregationssichten!• having versus where• keine geschachtelten Aggregationen in SQL


Rechtevergabe

Rechtevergabe in Datenbanksystemen

• Zugriffsrechte(AutorisierungsID, DB-Ausschnitt,

Operation)

• AutorisierungsID ist interne Kennung eines„Datenbankbenutzers“

• Datenbank-Ausschnitte: Relationen und Sichten• DB-Operationen: Lesen, Einfügen, Ändern, Löschen




Operation)• AutorisierungsID ist interne Kennung eines„Datenbankbenutzers“






• Datenbank-Ausschnitte: Relationen und Sichten

• DB-Operationen: Lesen, Einfügen, Ändern, Löschen







Rechtevergabe in SQL

grant Rechteon Tabelleto BenutzerListe[with grant option]


Rechtevergabe in SQL /2

• Erläuterungen:• In Rechte-Liste: all bzw. Langform all privilegesoder 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)




• Hinter on: Relationen- oder Sichtname

• Hinter to: Autorisierungsidentifikatoren (auch public,group)













Autorisierung für public

create view MeineAufträge asselect *from AUFTRAGwhere KName = user;

grant select, inserton MeineAufträgeto public;

„Jeder Benutzer kann seine Aufträge sehen und neueAufträge einfügen (aber nicht löschen!).“


Zurücknahme von Rechten

revoke Rechteon Tabellefrom BenutzerListe[restrict | cascade ]

• restrict: Falls Recht bereits an Dritte weitergegeben:Abbruch von revoke

• cascade: Rücknahme des Rechts mittels revoke an alleBenutzer propagiert, die es von diesem Benutzer mitgrant erhalten haben


Zurücknahme von Rechten

revoke Rechteon Tabellefrom BenutzerListe[restrict | cascade ]

• restrict: Falls Recht bereits an Dritte weitergegeben:Abbruch von revoke

• cascade: Rücknahme des Rechts mittels revoke an alleBenutzer propagiert, die es von diesem Benutzer mitgrant erhalten haben


Privacy-Aspekte

Privacy: Begriff und Anwendungsgebiete

Privacy (Privatsphäre)das Recht jedes Einzelnen auf einen geschützten privatenRaum, der von anderen nur in definierten Ausnahmefällenverletzt werden darf

• elektronische Autobahn-Mautsysteme: Überwachung vonFahrzeugen

• Kreditkartenaktivitäten und diverse Payback- bzw.Rabattkarten: Kaufverhalten von Kunden

• Mobilfunksysteme: Bewegungsprofile der Nutzer• RFID-Technologie: etwa im Einzelhandel Kaufverhalten,Warenflüsse, etc.


















• Mobilfunksysteme: Bewegungsprofile der Nutzer

• RFID-Technologie: etwa im Einzelhandel Kaufverhalten,Warenflüsse, etc.








Statistische Datenbanken

• Datenbanken, in denen die Einzeleinträge demDatenschutz unterliegen, aber statistische Informationenallen Benutzern zugänglich sind

• statistische Information = aggregierte Daten(Durchschnittseinkommen etc.)

• Problem: Gewinnung von Einzelinformationen durchindirekte Anfragen












Statistische Datenbanken: Beispiel

• Benutzer X darf Daten über Kontoinhaber sowie statistische Datenabfragen, jedoch keine einzelnen Kontostände

1. Verfeinerung des Suchkriteriums (nur ein Kunde)

select count (*) from KONTOwhere Ort = 'Manebach' and Alter = 24 and …

2. Name des Kontoinhabersselect Name from KONTOwhere Ort = 'Manebach' and Alter = 24 and …

3. statistische Anfrage, die tatsächlich aber Einzeleintrag liefert

select sum(Kontostand) from KONTOwhere Ort = 'Manebach' and Alter = 24 and …

• Abhilfe: keine Anfragen, die weniger als n Tupel selektieren



• Benutzer X darf Daten über Kontoinhaber sowie statistische Datenabfragen, jedoch keine einzelnen Kontostände1. Verfeinerung des Suchkriteriums (nur ein Kunde)































Statistische Datenbanken: Beispiel /2

• X will Kontostand von Y herausfinden

• X weiss, dass Y nicht in Ilmenau lebt• X hat abgefragt, dass in Ilmenau mehr als n Kontoinhaber leben

1. Gesamtkontostand der Ilmenauer Kunden

select sum(Kontostand) from KONTOwhere Ort = 'Ilmenau'

2. Gesamtkontostand der Ilmenauer Kunden + Kunde Y

select sum(Kontostand) from KONTOwhere Name = :Y or Ort = 'Ilmenau'

3. Differenz der Ergebnisse liefert Kontostand von Y

• Abhilfe: statistische Anfragen nicht erlauben, die paarweise einenDurchschnitt von mehr als m vorgegebenen Tupeln betreffen



• X will Kontostand von Y herausfinden• X weiss, dass Y nicht in Ilmenau lebt

• X hat abgefragt, dass in Ilmenau mehr als n Kontoinhaber leben









• X will Kontostand von Y herausfinden• X weiss, dass Y nicht in Ilmenau lebt• X hat abgefragt, dass in Ilmenau mehr als n Kontoinhaber leben












































Statistische Datenbanken: Fazit

• kritische Parameter

• Ergebnisgröße n• Größe der Überlappung der Ergebnismengen m

Sind nur Ergebnisse von Aggregatfunktionen erlaubt, dannbenötigt eine Person 1+ (n− 2)/m Anfragen, um eineneinzelnen Attributwert zu ermitteln



• kritische Parameter• Ergebnisgröße n

• Größe der Überlappung der Ergebnismengen m




• kritische Parameter• Ergebnisgröße n• Größe der Überlappung der Ergebnismengen m











k-Anonymität

• für viele Zwecke (klinische Studien etc.) werden auchDetaildaten (Mikrodaten) benötigt

Name Alter PLZ Geschlecht FamStand Krankheit***** 38 98693 männl. verh. Schnupfen***** 29 39114 weibl. ledig Fieber***** 29 39114 weibl. ledig Anämie***** 34 98693 männl. verh. Husten***** 34 98693 männl. verh. Knochenbruch***** 27 18055 weibl. ledig Fieber***** 27 18055 weibl. ledig Schnupfen


k-Anonymität

• für viele Zwecke (klinische Studien etc.) werden auchDetaildaten (Mikrodaten) benötigt

Name Alter PLZ Geschlecht FamStand Krankheit***** 38 98693 männl. verh. Schnupfen***** 29 39114 weibl. ledig Fieber***** 29 39114 weibl. ledig Anämie***** 34 98693 männl. verh. Husten***** 34 98693 männl. verh. Knochenbruch***** 27 18055 weibl. ledig Fieber***** 27 18055 weibl. ledig Schnupfen


k-Anonymität: Problem

• ist von einer Person aus dieser Relation bekannt, dass sie

• männlich• 38 Jahre alt• verheiratet ist• in 98693 Ilmenau wohnt

• Schnupfen• weitere Zuordnungen (Namen etc.) etwa durch Verbundmit anderen Daten möglich?

• Lösung: Data Swapping (??)

• Vertauschen von Attributwerten einzelner Tupel• statistische Analysen noch gültig?



• ist von einer Person aus dieser Relation bekannt, dass sie• männlich

• 38 Jahre alt• verheiratet ist• in 98693 Ilmenau wohnt






• ist von einer Person aus dieser Relation bekannt, dass sie• männlich• 38 Jahre alt

• verheiratet ist• in 98693 Ilmenau wohnt






• ist von einer Person aus dieser Relation bekannt, dass sie• männlich• 38 Jahre alt• verheiratet ist

• in 98693 Ilmenau wohnt






• ist von einer Person aus dieser Relation bekannt, dass sie• männlich• 38 Jahre alt• verheiratet ist• in 98693 Ilmenau wohnt







• Schnupfen

• weitere Zuordnungen (Namen etc.) etwa durch Verbundmit anderen Daten möglich?



















• Lösung: Data Swapping (??)• Vertauschen von Attributwerten einzelner Tupel

• statistische Analysen noch gültig?





• Lösung: Data Swapping (??)• Vertauschen von Attributwerten einzelner Tupel• statistische Analysen noch gültig?


k-Anonymität

k-Anonymitätein bestimmter Sachverhalt kann nicht zwischen einervorgegebenen Anzahl k von Tupeln unterschieden werden

• eine Anfrage nach einer beliebigen Kombination von Alter,Geschlecht, Familienstand und Postleitzahl liefertentweder eine leere Relation oder mindestens k Tupel


k-Anonymität

k-Anonymitätein bestimmter Sachverhalt kann nicht zwischen einervorgegebenen Anzahl k von Tupeln unterschieden werden

• eine Anfrage nach einer beliebigen Kombination von Alter,Geschlecht, Familienstand und Postleitzahl liefertentweder eine leere Relation oder mindestens k Tupel


k-Anonymität: Ansätze

• Generalisierung: Attributwerte durch allgemeinere Werteersetzen, die einer Generalisierungshierarchieentnommen sind

• die Verallgemeinerung des Alters einer Person zuAltersklassen: 35, 39 30-40

• Weglassen von Stellen bei Postleitzahlen: 39106, 39114 39***

• Unterdrücken von Tupeln: Löschen von Tupeln, welche diek-Anonymität verletzen und damit identifizierbar sind




















Zusammenfassung

• Sichten zur Strukturierung von Datenbanken• Probleme bei Änderungen über Sichten• Rechtesystem in SQL-DBS• Privacy-Aspekte


Kontrollfragen

• Was versteht man unter einerDatenbank-Sicht? Wie werden Sichtendefiniert?

• Sind Sichten änderbar? Unter welchenBedingungen?

• Wie kann in Datenbanken derDatenschutz erreicht werden?


Kontrollfragen





Kontrollfragen





Teil XI

NoSQL

NoSQL

1. Motivation für NoSQL

2. Datenmodelle für NoSQL

3. KV-Stores und Wide Column

4. Document Stores

5. Graph Stores


NoSQL




4. Document Stores

5. Graph Stores


NoSQL




4. Document Stores

5. Graph Stores


NoSQL




4. Document Stores

5. Graph Stores


NoSQL




4. Document Stores

5. Graph Stores


Motivation für NoSQL

Motivation für NoSQL

NoSQL = Not only SQL

• im Umfeld vieler aktueller Buzzwords• NoSQL• Big Data• BASE• ....

• oft einfach als Etikett einer Neuentwicklung eines DBMSpauschal vergeben


Was ist NoSQL?

• SQL - No!• SQL-Datenbanken sind zu komplex, nicht skalierbar, ...• man braucht was einfacheres!

• Not only SQL• SQL-Datenbanken haben zu wenig (oder die falsche)Funktionalität

• Operationen auf Graphen, Data Mining Operatoren, ...• New SQL

• SQL-Datenbanken sind (software-technisch) in die Jahregekommen

• eine neue Generation von DBMS muss her (ohne dieetablierten Vorteile von SQL zu ignorieren)


Kritik an RDBMS / SQL

• nicht skalierbar• Normalisierung von Relationen, vieleIntegritätsbedingungen zu prüfen

• kann man in RDBMS auch vermeiden!• starre Tabellen nicht flexibel genug

• schwach typisierte Tabellen (Tupel weichen in dentatsächlich genutzten Attributen ab)

• viele Nullwerte wenn alle potentiellen Attribute definiert• alternativ Aufspaltung auf viele Tabellen• Schema-Evolution mit alter table skaliert bei Big Data nicht

• tatsächlich in vielen Anwendungen ein Problem• Integration von spezifischen Operationen(Graphtraversierung, Data-Mining-Primitive) mit StoredProcedures zwar möglich führt aber oft zu schwerinterpretierbarem Code


Datenmodelle für NoSQL

Datenmodelle für NoSQL

• KV-Stores• Wide Column Stores• Dokumenten-orientierte Datenhaltung• Graph-Speicher• ....


Anfragesprachen für NoSQL

• unterschiedliche Ansätze:• einfache funktionale API• Programmiermodell für parallele Funktionen• angelehnt an SQL-Syntax• ....


KV-Stores und Wide Column

Datenmodell: Key-Value-Stores

• Key-Value-Store: binäre Relationen, bestehend aus• einem Zugriffsschlüssel (dem Key) und• den Nutzdaten (dem Value)

• Nutzdaten• binäre Daten ohne Einschränkung,• Dateien oder Dokumente,→ Document Databases

• oder schwachstrukturierte Tupel→ Wide Column Store


Anfragen an KV-Stores

• einfache APIstore.put(key, value)value = store.get(key)store.delete(key)

• aufgesetzte höherer Sprache angelehnt an SQL• Map-Reduce

• Framework zur Programmierung parallelerDatenaggregation auf KV-Stores


Beispielsysteme für KV-Stores

• Amazon DynamoDB• Riak


Datenmodell: Wide Column

• Basisidee: KV-Store mit schwachstrukturiertem Tupel alsValue

• Value = Liste von Attributname-Attributwert-Paaren• schwache Typisierung für Attributwerte (auchWiederholgruppen)

• nicht alle Einträge haben die selben Attributnamen• offene Tupel• Hinzufügen eines neuen Attributs unproblematisch• Nullwerte aus SQL ersetzt durch fehlende Einträge

• Beispiel in DynamoDB


Datenmodell: Wide Column /2

Key Value (Attributliste)WeinID = 1 Name = Zinfandel Farbe = Rot Jahrgang = 2004WeinID = 2 Name = Pinot Noir Weingut = Creek,

HelenaWeinID = 3 Name = Chardonnay Jahrgang = 2002 Weingut = Bighorn


Anfragen bei Wide Column

• CRUD: Create, Read, Update und Delete• in DynamoDB

• PutItem fügt einen neuen Datensatz mit der gegebenenAttribut-Wert-Liste ein bzw. ersetzt einen existierendenDatensatz mit gleichem Schlüssel.

• GetItem-Operation liest alle Felder eines über einenPrimärschlüssel identifizierten Datensatzes.

• Scan erlaubt einen Lauf über alle Datensätze mit Angabevon Filterkriterien.

• Aufruf über HTTP oder aus Programmiersprachen heraus


Beispielanfrage in DynamoDB

POST / HTTP/1.1x-amz-target: DynamoDB_20111205.GetItemcontent-type: application/x-amz-json-1.0

"TableName": "Weine"," Key ":

"HashKeyElement": "N": "1" "RangeKeyElement": "S": "Zinfandel"

,"AttributesToGet": ["Farbe", "Jahrgang"],"ConsistentRead": false

• Primärschlüssel (HashKeyElement) ist numerisch (N)• Feld Name ist Bereichsschlüssel vom Typ String (S)


Beispielanfrage in DynamoDB: Ergebnis

HTTP/1.1 200x-amzn-RequestId: …content-type: application/x-amz-json-1.0content-length: …

"Item":"Farbe": "S": "Rot" ,"Jahrgang": "N": "2004" ,

"ConsumedCapacityUnits": 0.5


Document Stores

Datenmodell: dokumentenorientierte Speicherung

• Basisidee: KV-Store mit (hierarchisch) strukturiertemDokument als Value

• strukturiertes Dokument:• JSON-Format

• geschachtelte Wide Column-Daten• XML (eher unüblich auf KV-Stores)


Beispiel für Dokument in JSON

"id" : "kritiker08154711","Name" : "Bond","Vorname" : "Jamie","Alter" : 42,"Adresse" :

"Strasse" : "Breiter Weg 1","PLZ" : 39007,"Stadt" : "Machdeburch"

,"Telefon" : [7007, 110]


Anfragen bei dokumentenorientierter Speicherung

• CRUD erweitert um dokumentspezifische Suche• Beispiele (MongoDB mit BSON statt JSON)

db.kritiker.find(Name: "Bond")db.kritiker.find(Alter: 40)db.kritiker.find(Alter$lt: 50)db.kritiker.find(Name: "Bond", Alter: 42)db.kritiker.find($or[Name: "Bond",

Alter: 42])


Beispielsysteme für dokumentenorientierte Speicherung

• MongoDB• CouchDB


Graph Stores

Graph-Datenmodelle: Grundlagen

• spezielle Form der Datenrepräsentation = Graphen, insb.Beziehungen zwischen Objekten

• Anwendungsgebiete:• Transportnetze• Networking: Email-Verkehr, Mobilfunk-Nutzer• Soziale Netzwerke: Eigenschaften, Communities• Web: Verlinkte Dokumente• Chemie: Struktur chemischer Komponenten• Bioinformatik: Proteinstrukturen, metabolische Pathways,Genexpressionen


Grundbegriffe

• Graph G = (V, E)• V: Menge der Knoten (vertices)• E ⊆ V× V: Menge der Kanten (edges)

v1 v2

v4v3

• Kanten können mit Gewicht versehen werden


Grundbegriffe: Adjazenzmatrix

• Repräsentation von Graphen durch Matrix (Knoten alsZeilen und Spalten)

• ungerichteter Graph: symmetrische Matrix• ungewichteter Graph: Zellen nur 0 oder 1

v1 v2

v4v3

3

1

1

2

2

5

05 1v4 2

0v3 20 0

v2 0 10 0

v1 0 003

v4v3v2v1

nach

von


Grundbegriffe: Knotengrad

• Eigenschaft eines Knotens: Anzahl der verbundenenKnoten

• bei gerichteren Graphen: Unterscheidung in Eingangs- undAusgangsgrad

v1 v2

v4v3

3

1

1

2

2

5

01 1v4 1

0v3 10 0

v2 0 10 0

v1 0 001

v4v3v2v1

nach

von

3

1

1

1

2121

Ausgangsgrad

Eingangsgrad


Grundbegriffe: Traversierung

• Tiefensuche (DFS): zunächst rekursiv alle Kindknotenbesuchen bevor alle Geschwisterknoten besucht werden

• Bestimmung der Zusammenhangskomponente• Wegsuche um Labyrinth

• Breitensuche (BFS): zunächst alle Geschwisterknotenbesuchen bevor die Kindknoten besucht werden

• Bestimmung des kürzesten Weges


Subjekt-Prädikat-Objekt-Modell: RDF

• Sprache zur Repräsentation von Informationen über(Web)-Ressourcen

• Ziel: automatisierte Verarbeitung• zentraler Bestandteil von Semantic Web, Linked (Open)Data

• Repräsentation von Daten, aber auchWissensrepräsentation (z.B. Ontologie)


Ontologien

• Ontologie = formale Spezifikation einerKonzeptualisierung, d.h. einer Repräsentation vonBegriffen (Konzepten) und deren Beziehungen

• Anwendung: Annotation von Daten, semantische Suche

Wein

Dessertwein Schaumwein Spätlese

Getränk

Weinbeschreibung

Farbe

Süße

Geschmack

Körper Abgang

Weingut wird beschriebenhat Hersteller

produziert

inverse zu

ist


RDF: Graphen & Tripel

• Graph = Menge von Tripeln, die Aussagen überWeb-Ressourcen repräsentieren

• Identifikation der Web-Ressourcen über Uniform ResourceIdentifier (URI)

• Tripel:subjekt prädikat objekt .

• Beispiel<http://weindb.org/weine/2171> \

<http://weindb.org/ontologie/name> "Pinot Noir".

Pinot Noirhttp://weindb.org/weine/3478 http://weindb.org/ontologie/name


RDF: Graphen & Tripel

• Subjekt: URI-Referenz, d.h. Ressource, auf die sich dieAussage bezieht

• Prädikat: Eigenschaft, ebenfalls in Form einerURI-Referenz

• Objekt: Wert der Eigenschaft als Literal (Konstante) oderURI- Referenz


RDF: Abkürzende Notation

• abkürzende Notation für Namensräume über Präfixe:

prefix wo: <http://weindb.org/ontologie/>prefix weine: <http://weindb.org/weine/>

weine:2171 wo:name "Pinot Noir".


RDF: Komplexe Graphen

• mehrere Aussagen zum gleichen Subjekt• Objekte nicht nur Literale sondern selbst Objekte (URI)

weine:2171 wo:name "Pinot Noir".weine:2171 wo:farbe "Rot".weine:2171 wo:jahrgang "1999".weine:2171 wo:erzeuger werzeuger:567 .

Rot

Pinot Noir

1999

werzeuger:567

weine:3478wo:farbe

wo:name

wo:jahrgang

wo:erzeuger


RDF: Repräsentation, Schema und Vokabulare

• Repräsentation von RDF-Daten: N-Tripel (siehe oben),RDF/XML

• RDF Schema:• objektorientierte Spezifikationssprache• erweitert RDF um Typsystem: Definition von Klassen undKlassenhierarchien mit Eigenschaften, Ressourcen alsInstanzen von Klassen

• RDF Schema ist selbst RDF-Spezifikation


RDF: Repräsentation, Schema und Vokabulare /2

• Beispiel RDF SchemaWein rdf:type rdfs:Class .Schaumwein rdf:type rdfs:Class .Schaumwein rdfs:subClassOf Wein .Name rdf:type rdf:Property .Jahrgang rdf:type rdf:Property .Jahrgang rdfs:domain Wein .Jahrgang rdfs:range xsd:integer .

• für komplexere Ontologien: OWL (Web Ontology Language)


RDF: Repräsentation, Schema und Vokabulare /3

• Vokabular: vordefinierte Klassen und Eigenschaften• Bsp: Dublin Core (Metadaten für Dokumente), FOAF (SozialeNetze), ...

• wichtig z.B. für Linked Open Data


SPARQL als RDF-Anfragesprache

• SPARQL Protocol And RDF Query Language:Anfragesprache für RDF

• W3C-Recommendation• unterschiedliche Implementierungen möglich:

• Aufsatz für SQL-Backends (z.B. DB2, Oracle)• Triple Stores (RDF-Datenbank)• SPARQL-Endpoints

• syntaktisch an SQL angelehnt, aber Unterstützung fürGraph-Anfragen


SPARQL-Elemente

• Grundelemente: select-where-Block und Tripelmuster?wein wo:name ?name .

• Auswertung: finden aller Belegungen (Bindung) fürVariable (?name) bei Übereinstimmung mitnicht-variablen Teilen

<http://weindb.org/weine/2171> wo:name "Pinot Noir".<http://weindb.org/weine/2168> wo:name "Creek Shiraz".<http://weindb.org/weine/2169> wo:name "Chardonnay".


SPARQL: Basic Graph Pattern

• Graphmuster (BGP = Basic Graph Pattern): Kombinationvon Tripelmustern über gemeinsame Variablen

?wein wo:name ?name .?wein wo:farbe ?farbe .?wein wo:erzeuger ?erzeuger .?erzeuger wo:weingut ?ename .

• Einsatz in SPARQL-Anfragen im where-Teil

select ?wein ?name ?farbe ?enamewhere ?wein wo:name ?name .

?wein wo:farbe ?farbe .?wein wo:erzeuger ?erzeuger .?erzeuger wo:weingut ?ename .


SPARQL: Weitere Elemente

• filter: Filterbedingungen für Bindungen• optional: optionale Muster – erfordern nicht zwingendein Matching

prefix wo: <http://weindb.org/ontologie/>select ?namewhere ?wein wo:name ?name .

optional ?wein wo:jahrgang ?jahrgang .filter ( bound(?jahrgang) && ?jahrgang < 2010 )


Property-Graph-Modell

• Knoten und (gerichtete) Kanten mit Eigenschaften(Properties)

• nicht streng typisiert, d.h. Eigenschaften alsName-Wert-Paare

• Unterstützung in diversen Graph-Datenbanksystemen:neo4j, Microsoft Azure Cosmos DB, OrientDB, AmazonNeptune, …

Helena

Pinot Noir

produziert

Spätbur-gundername: Helena

farbe: Rotjahrgang: 1999

beerenfarbe: Schwarzherkunft: Frankreich

anteil: 100%besteht aus


Property-Graph-Modell in Neo4j

• Elemente: Nodes, Relationships, Properties, Labels• Properties = Key-Value-Paare: Key (=String), Value(=Java-Datentypen + Felder)

• Nodes mit Labels (≈ Klassenname)• Relationships: sind gerichtet, mit Namen und ggf.Properties


Property-Graph-Modell: Beispiel

anteil: 100%

Napa Valley

Pinot Noir

liegt in

produziert

Spätbur-gunder

besteht aus

gebiet: Napa Valleyregion: Kalifornien


beerenfarbe: Schwarzherkunft: Frankreich

baut an

Merlot

baut an

Zinfandel

produziert

Helena


beerenfarbe: Schwarzblauherkunft: Frankreich

Bighorn

name: Bighorn

liegt in

grenzt an

name: Helena


Anfragen auf Graphen

• keine Standardsprache• aber wiederkehrende Grundelemente

• Graph Matching: Knoten, Kanten, Pfade (siehe BGP inSPARQL)

• Filter für Knoten- und Kanteneigenschaften• Konstruktion neuer Graphen

• hier: Cypher (neo4j)


Anfragen in Cypher

• Basis: Muster der Form „Knoten→ Kante→ Knoten …“(von)-[:relationship]->(nach)

• Beschränkung über Label und Properties(e:ERZEUGER)-[:LIEGT_IN]->(a:ANBAUGEBIET

gebiet: 'Napa Valley' )


Cypher: Klauseln

• match: Beispielmuster für Matching• return: Festlegung der Rückgabedaten (Projektion)• where: Filterbedingung für „gematchte“ Daten• create: Erzeugen von Knoten oder Beziehungen• set: Ändern von Property-Werten• …


Cypher: Beispiele

• Anlegen von Daten

create(napavalley:ANBAUGEBIET

gebiet: 'Napa Valley', region: 'Kalifornien' ),(helena:ERZEUGER name: 'Helena' ),…(helena)-[:LIEGT_IN]->(napavalley),…


Cypher: Beispiele

• Alle Weingüter aus dem Napa Valleymatch (e:ERZEUGER)-[:LIEGT_IN]->(a:ANBAUGEBIET

gebiet: 'Napa Valley' )return e

• Alle Weingüter, die die Merlot-Traube anbauenmatch (r:REBE name: 'Merlot' )<-[:BAUT_AN]-(w) \

-[:LIEGT_IN]->(g)return g


Cypher: Beispiele /2

• Alle Weingüter, die Weine mit eiem Spätburgunder-Anteilvon mehr als 50% produzieren sowie die Anzahl dieserWeine pro Weingut

match (e:ERZEUGER)-[:PRODUZIERT]->(w:WEIN)-[b:BESTEHT_AUS]->(r:REBE name: 'Spätburgunder' )

where b.anteil > 50return e.name, count(w.name)

• Alle Weingüter, direkt an das Weingut Helena grenzenoder an ein Weingut, das direkt an Helena grenzt

match (e1:ERZEUGER name: 'Helena' )-[:GRENZT_AN*..2] \-(e2:ERZEUGER)

return e2


Cypher: Beispiele /3

• alle Knoten des Typs WEINEmatch (w)where w:WEINEreturn w

• Knotengrade pro Knoten im Graphmatch (n)-[r]-()return n, count(r)


Zusammenfassung

• NoSQL als Oberbegriff für diverse Datenbanktechniken• große Bandbreite: von einfachen KV-Stores bis zuGraphdatenbanken

• höhere Skalierbarkeit / Performance gegenüberSQL-DBMS meist durch Einschränkungen erkauft

• Abschwächung von ACID-Eigenschaften• begrenzte Anfragefunktionalität• Nicht-Standard bzw. proprietäre Schnittstellen


Weiterführende Literatur

• Lena Wiese: Advanced Data Management for SQL, NoSQL,Cloud and Distributed Databases. De Gruyter / Oldenburg,2015

• Ian Robinson, Jim Webber, Emil Eifrem: Graph Databases.O’Reilly, 2015


Teil XII

Anwendungsprogrammierung


1. Programmiersprachenanbindung

2. JDBC

3. SQLJ

4. LINQ

5. Objekt-relationales Mapping

6. Prozedurale SQL-Erweiterungen: SQL/PSM




2. JDBC

3. SQLJ

4. LINQ






2. JDBC

3. SQLJ

4. LINQ






2. JDBC

3. SQLJ

4. LINQ






2. JDBC

3. SQLJ

4. LINQ






2. JDBC

3. SQLJ

4. LINQ





• Wissen zu Konzepten und Schnittstellenzum Zugriff auf SQL-Datenbanken ausProgrammiersprachen heraus

• Verständnis prozeduraler Schnittstellenam Beispiel von JDBC

• Kenntnisse zu Embedded SQL undprozeduralen SQL-Erweiterungen

• Grundverständnis objektrelationalerAbbildungen




















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



• Kopplungsarten:• prozedurale oder CALL-Schnittstellen (call level interface)

• Beispiele: SQL/CLI, ODBC, JDBC, …• Einbettung einer DB-Sprache in Programmiersprachen








• Beispiele: SQL/CLI, ODBC, JDBC, …

• Einbettung einer DB-Sprache in Programmiersprachen


























• 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


Cursor-Konzept

• Cursor: Iterator über Liste von Tupeln (Anfrageergebnis)

Relation

Cursor

Anwendungsprogramm Datenbank


JDBC

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 eineVerbindung

• ResultSet: verwaltet Ergebnisse einer Anfrage, Zugriff aufeinzelne Spalten


JDBC: Überblick

• Datenbankzugriffsschnittstelle für Java• abstrakte, datenbankneutrale Schnittstelle

• vergleichbar mit ODBC• Low-Level-API: direkte Nutzung von SQL• Java-Package java.sql




JDBC: Überblick

• Datenbankzugriffsschnittstelle für Java• abstrakte, datenbankneutrale Schnittstelle• vergleichbar mit ODBC

• Low-Level-API: direkte Nutzung von SQL• Java-Package java.sql




JDBC: Überblick

• Datenbankzugriffsschnittstelle für Java• abstrakte, datenbankneutrale Schnittstelle• vergleichbar mit ODBC• Low-Level-API: direkte Nutzung von SQL

• Java-Package java.sql




JDBC: Überblick

• Datenbankzugriffsschnittstelle für Java• abstrakte, datenbankneutrale Schnittstelle• vergleichbar mit ODBC• Low-Level-API: direkte Nutzung von SQL• Java-Package java.sql




JDBC: Überblick


• DriverManager: Einstiegspunkt, Laden von Treibern

• Connection: Datenbankverbindung• Statement: Ausführung von Anweisungen über eineVerbindung



JDBC: Überblick


• DriverManager: Einstiegspunkt, Laden von Treibern• Connection: Datenbankverbindung

• Statement: Ausführung von Anweisungen über eineVerbindung



JDBC: Überblick





JDBC: Überblick





JDBC: Struktur

DriverManager Connection

StatementStatement

ResultSet ResultSet

getConnection

createStatement

executeQuery


JDBC: Treiberkonzept

Java-Applikation

JDBC-Treibermanager

Native-API-

Treiber

JDBC-ODBC-Bridge

JDBC-Net-

Treiber

Native-Protokoll-Treiber

Client-Bibliothek

Client-Bibliothek

ODBCDB-

Middleware

JDBC-API


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


JDBC: Ablauf

1. Aufbau einer Verbindung zur Datenbank• Angabe der Verbindungsinformationen

• Auswahl und Laden des Treibers2. Senden einer SQL-Anweisung





JDBC: Ablauf

1. Aufbau einer Verbindung zur Datenbank• Angabe der Verbindungsinformationen• Auswahl und Laden des Treibers






JDBC: Ablauf







JDBC: Ablauf


2. Senden einer SQL-Anweisung• Definition der Anweisung

• Belegung von Parametern3. Verarbeiten der Anfrageergebnisse



JDBC: Ablauf


2. Senden einer SQL-Anweisung• Definition der Anweisung• Belegung von Parametern




JDBC: Ablauf






JDBC: Ablauf



3. Verarbeiten der Anfrageergebnisse• Navigation über Ergebnisrelation

• Zugriff auf Spalten


JDBC: Ablauf



3. Verarbeiten der Anfrageergebnisse• Navigation über Ergebnisrelation• Zugriff auf Spalten


JDBC: Verbindungsaufbau

1. Treiber ladenClass.forName("com.company.DBDriver");

2. Verbindung herstellen

String url = "jdbc:subprotocol:datasource";Connection con = DriverManager.getConnection

(url, "scott", "tiger");

JDBC-URL spezifiziert

• Datenquelle/Datenbank• Verbindungsmechanismus (Protokoll, Server und Port)


























JDBC: Anfrageausführung

1. Anweisungsobjekt (Statement) erzeugen

Statement stmt = con.createStatement();

2. Anweisung ausführen

String query = "select Name, Jahrgang from WEINE";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


























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 wName = rSet.getString(1);

• über Spaltenname

String wName = rSet.getString("Name");





2. Zugriff auf Spaltenwerte über getType-Methoden

• über Spaltenindex








2. Zugriff auf Spaltenwerte über getType-Methoden• über Spaltenindex








2. Zugriff auf Spaltenwerte über getType-Methoden• über Spaltenindex





JDBC: Fehlerbehandlung

• Fehlerbehandlung mittels Exception-Mechanismus

• SQLException für alle SQL- und DBMS-Fehler

try // Aufruf von JDBC-Methoden…

catch (SQLException exc) System.out.println("SQLException: " +

exc.getMessage());


JDBC: Fehlerbehandlung

• Fehlerbehandlung mittels Exception-Mechanismus• SQLException für alle SQL- und DBMS-Fehler

try // Aufruf von JDBC-Methoden…

catch (SQLException exc) System.out.println("SQLException: " +

exc.getMessage());


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 WEINE set Preis = Preis * 1.1 " +"where Jahrgang < 2000");


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 WEINE set Preis = Preis * 1.1 " +"where Jahrgang < 2000");


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)



• Methoden von Connection• commit ()

• rollback ()

Auto-Commit-Modus




• Methoden von Connection• commit ()• rollback ()

Auto-Commit-Modus





Auto-Commit-Modus• implizites Commit nach jeder Anweisung

• Transaktion besteht nur aus einer Anweisung• Umschalten mittels setAutoCommit (boolean)




Auto-Commit-Modus• implizites Commit nach jeder Anweisung• Transaktion besteht nur aus einer Anweisung

• Umschalten mittels setAutoCommit (boolean)




Auto-Commit-Modus• implizites Commit nach jeder Anweisung• Transaktion besteht nur aus einer Anweisung• Umschalten mittels setAutoCommit (boolean)


SQLJ

SQLJ: Embedded SQL für Java

• Einbettung von SQL-Anweisungen in Java-Quelltext

• Vorübersetzung des erweiterten Quelltextes in echtenJava-Code durch Translator sqlj

• Überprüfung der SQL-Anweisungen

• korrekte Syntax• Übereinstimmung der Anweisungen mit DB-Schema• Typkompatibilität der für Datenaustausch genutztenVariablen

• Nutzung von JDBC-Treibern



• Einbettung von SQL-Anweisungen in Java-Quelltext• Vorübersetzung des erweiterten Quelltextes in echtenJava-Code durch Translator sqlj













• Überprüfung der SQL-Anweisungen• korrekte Syntax

• Übereinstimmung der Anweisungen mit DB-Schema• Typkompatibilität der für Datenaustausch genutztenVariablen





• Überprüfung der SQL-Anweisungen• korrekte Syntax• Übereinstimmung der Anweisungen mit DB-Schema

• Typkompatibilität der für Datenaustausch genutztenVariablen





• Überprüfung der SQL-Anweisungen• korrekte Syntax• Übereinstimmung der Anweisungen mit DB-Schema• Typkompatibilität der für Datenaustausch genutztenVariablen





• Überprüfung der SQL-Anweisungen• korrekte Syntax• Übereinstimmung der Anweisungen mit DB-Schema• Typkompatibilität der für Datenaustausch genutztenVariablen



SQLJ: Prinzip

SQLJ-Programm

SQLJ-Translator

Java-Quellcode SQLJ-Profile

Java-Compiler Customizer

Bytecode Custom-Profile

JDBC-Treiber

SQLJ-Laufzeitsystem

Syntax- & Semantik-prüfung


SQLJ-Anweisungen

• Kennzeichnung durch #sql Deklarationen

• Klassendefinitionen für Iteratoren• SQL-Anweisungen: Anfragen, DML- und DDL-Anweisungen

#sql SQL-Operation ;

• Beispiel:

#sql insert into ERZEUGER (Weingut, Region) values( 'Wairau Hills', 'Marlborough') ;


SQLJ-Anweisungen

• Kennzeichnung durch #sql Deklarationen• Klassendefinitionen für Iteratoren

• SQL-Anweisungen: Anfragen, DML- und DDL-Anweisungen


• Beispiel:



SQLJ-Anweisungen

• Kennzeichnung durch #sql Deklarationen• Klassendefinitionen für Iteratoren• SQL-Anweisungen: Anfragen, DML- und DDL-Anweisungen


• Beispiel:



SQLJ-Anweisungen

• Kennzeichnung durch #sql Deklarationen• Klassendefinitionen für Iteratoren• SQL-Anweisungen: Anfragen, DML- und DDL-Anweisungen


• Beispiel:



Host-Variablen

• Variablen einer Host-Sprache (hier Java), die inSQL-Anweisungen auftreten können

• Verwendung: Austausch von Daten zwischen Host-Spracheund SQL

• Kennzeichnung durch ":variable"• Beispiel:

String name;int weinID = 4711;#sql select Name into :name

from WEINE where WeinID = :weinID ;System.out.println("Wein = " + name);

• Nullwerte: Indikatorvariable ":variable:indvar"


Host-Variablen








Host-Variablen



• Kennzeichnung durch ":variable"

• Beispiel:





Host-Variablen








Host-Variablen






• Nullwerte: Indikatorvariable ":variable:indvar"Sattler/Saake | VL Datenbanksysteme | 22. September 2019 12–18

Iteratoren

1. Deklaration des Iterators#sql public iterator WeinIter(String Name, String Weingut,

int Jahrgang);

2. Definition des IteratorobjektesWeinIter iter;

3. Ausführung der Anweisung#sql iter = select Name, Weingut, Jahrgang from WEINE ;

4. Navigationwhile (iter.next())

System.out.println(iter.Name() + " " iter.Weingut());


Iteratoren


int Jahrgang);






Iteratoren


int Jahrgang);






Iteratoren


int Jahrgang);






Dynamic SQL

• SQL-Statements als zur Laufzeit konstruierte Strings

exec sql begin declare section;AnfrageString char(256) varying;

exec sql end declare section;exec sql declare AnfrageObjekt statement;AnfrageString =

'delete from WEINE where WeinID = 4711';…exec sql prepare AnfrageObjekt from :AnfrageString;exec sql execute AnfrageObjekt;


LINQ

Language Integrated Query (LINQ)

• Einbettung einer DB-Sprache (SQL) in eineProgrammiersprache (C#)

• spezielle Klassenmethoden

IEnumerable<string> res = weine.Where(w => w.Farbe == "Rot").Select(w => new w.Name );

• eigene Sprachkonstrukte (ab C# 3.0)

IEnumerable<op> res = from w in weinewhere w.Farbe == "Rot"select new w.Name ;
















Objekt-relationales Mapping


• Einsatz von

• relationalen Backends (SQL-DBMS)• objektrelationalen Anwendungen, Applikationsservern,Middleware, …

• Implementierung von „Geschäftslogik“ in Form vonObjekten (Kunde, Bestellung, Vorgang, …)

• z.B. als Java Bean, CORBA-Objekt

• erfordert: Abbildung Klasse↔ Relation• Aspekte:

• konzeptionelle Abbildung• Laufzeitunterstützung

• Technologien/Produkte: JDO, Hibernate, ADO.NET EntityFramework…



• Einsatz von• relationalen Backends (SQL-DBMS)

• objektrelationalen Anwendungen, Applikationsservern,Middleware, …








• Einsatz von• relationalen Backends (SQL-DBMS)• objektrelationalen Anwendungen, Applikationsservern,Middleware, …



























• erfordert: Abbildung Klasse↔ Relation

• Aspekte:

















• konzeptionelle Abbildung

• Laufzeitunterstützung



















Objekt-relationales Mapping: Prinzip

Laufzeitsystem

Applikation

Anwendungs-objekte

Objekt-modell

Datenbank-schema

Abbildungs-vorschrift


Klassen und Tabellen

• OO: Klasse definiert Eigenschaften von Objekten(Intension) + umfasst Menge aller Objekte (Extension)

• RM: Relation umfasst alle Tupel, Relationenschemabeschreibt Struktur

• naheliegend: Klasse = Tabelle• aber: Normalisierung zerlegt Relationen!

• 1 Klasse = 1 Tabelle• 1 Klasse = n Tabellen• n Klassen = 1 Tabelle











• naheliegend: Klasse = Tabelle

• aber: Normalisierung zerlegt Relationen!













• 1 Klasse = 1 Tabelle

• 1 Klasse = n Tabellen• n Klassen = 1 Tabelle






• 1 Klasse = 1 Tabelle• 1 Klasse = n Tabellen

• n Klassen = 1 Tabelle








Klassen und Tabellen: Beispiel

WeinId : intName : stringFarbe : stringJahr : integerWeingut : string


i


Beziehungen

• eingebetteter Fremdschlüssel in der Relation der Klasse,d.h. der Identifikator des assoziierten Objektes wird alsFremdschlüssel in zusätzlichen Spalten gespeichert

• Fremdschlüsseltabellen: die Beziehungsinstanz wird alsTupel mit den Schlüsseln der beteiligten Objekterepräsentiert

• Abbildung der in Beziehung stehenden Klassen auf eineeinzelne Tabelle: Verletzung der Normalformen


Beziehungen





Beziehungen





Beziehungen: konkrete Abbildung

• 1:1-Beziehungen: eingebettete Fremdschlüssel

• 1:n-Beziehungen: eingebettete Fremdschlüssel oderFremdschlüsseltabellen

• Beziehungen mit Attributen: Fremdschlüsseltabellen• m:n-Beziehungen: Fremdschlüsseltabellen• Drei- und mehrstellige Beziehungen:Fremdschlüsseltabellen



• 1:1-Beziehungen: eingebettete Fremdschlüssel• 1:n-Beziehungen: eingebettete Fremdschlüssel oderFremdschlüsseltabellen





• Beziehungen mit Attributen: Fremdschlüsseltabellen

• m:n-Beziehungen: Fremdschlüsseltabellen• Drei- und mehrstellige Beziehungen:Fremdschlüsseltabellen




• Beziehungen mit Attributen: Fremdschlüsseltabellen• m:n-Beziehungen: Fremdschlüsseltabellen

• Drei- und mehrstellige Beziehungen:Fremdschlüsseltabellen






Beziehungen /2



i

ErzeugerName : stringAnbaugebiet : stringRegion : stringWinzer: list of string



WINZER Weingut Name

i


Hibernate

• Java-Framework für objekt-relationales Mapping

• Idee: Abbildung von Java-Objekten auf Tupel einerrelationalen Datenbank

• Prinzip: Java-Klasse + Abbildungsvorschrift SQL-Tabelle• keine expliziten SQL-Anweisungen nötig!• Unterstützung der Navigation über Beziehungen(automatisches Nachladen der referenzierten Objekte)

• Anfragen über eigene Sprache (HQL bzw. QBC/QBE)


Hibernate

• Java-Framework für objekt-relationales Mapping• Idee: Abbildung von Java-Objekten auf Tupel einerrelationalen Datenbank




Hibernate


• Prinzip: Java-Klasse + Abbildungsvorschrift SQL-Tabelle

• keine expliziten SQL-Anweisungen nötig!• Unterstützung der Navigation über Beziehungen(automatisches Nachladen der referenzierten Objekte)



Hibernate


• Prinzip: Java-Klasse + Abbildungsvorschrift SQL-Tabelle• keine expliziten SQL-Anweisungen nötig!

• Unterstützung der Navigation über Beziehungen(automatisches Nachladen der referenzierten Objekte)



Hibernate





Hibernate





Hibernate: Beispiel

public class Wein private int id;private String name;private String farbe;private int jahr;private String weingut;

public void setName(String n) name = n; public String getName() return name; public void setFarbe(String f) farbe = f; public String getFarbe() return farbe; public void setJahr(int j) jahr = j; public int getJahr() return jahr; …


Hibernate: Beispiel /2

• Deklaration der Abbildung in einer XML-Mapping-Datei

• Abbildungsvorschrift wird zur Systemlaufzeit interpretiert

<hibernate-mapping><class name="Wein" table="WEINE">

<id name="id"><generator class="native" />

</id><property name="name" /><property name="farbe" /><property name="jahr" column="jahrgang"/><property name="weingut" />

</class></hibernate-mapping>



• Deklaration der Abbildung in einer XML-Mapping-Datei• Abbildungsvorschrift wird zur Systemlaufzeit interpretiert




















Hibernate: Objekterzeugung

Transaction tx = null;

Wein wein = new Wein();wein.setName("Pinot Noir");wein.setFarbe("Rot");wein.setJahr(1999);wein.setWeingut("Helena");

try tx = session.beginTransaction();session.save(wein);tx.commit();

catch (HibernateException exc) if (tx != null) tx.rollback();


Hibernate: Anfragen

• Anfragen über Hibernate-eigene Anfragesprache HQL

• Formulierung auf dem konzeptuellen Schema(Java-Klassen)

• Select-Klausel nicht benötigt (Ergebnisse sind immerObjekte)

• Beispiel

Query query =session.createQuery("from Wein where Farbe = 'Rot'");

Iterator iter = query.iterate();while (iter.hasNext())

Wein wein = (Wein) iter.next();…


Hibernate: Anfragen

• Anfragen über Hibernate-eigene Anfragesprache HQL• Formulierung auf dem konzeptuellen Schema(Java-Klassen)


• Beispiel





Hibernate: Anfragen



• Beispiel





Hibernate: Anfragen



• Beispiel





Hibernate: Anfragen



• Beispiel





Hibernate: Anfragen



• Beispiel





Prozedurale SQL-Erweiterungen:SQL/PSM

SQL/PSM: Der Standard

• SQL-Standard für prozedurale Erweiterungen

• PSM: Persistent Stored Modules

• gespeicherte Module aus Prozeduren und Funktionen• Einzelroutinen• Einbindung externer Routinen (implementiert in C, Java, …)• syntaktische Konstrukte für Schleifen, Bedingungen etc.• Basis für Methodenimplementierung für objektrelationaleKonzepte



• SQL-Standard für prozedurale Erweiterungen• PSM: Persistent Stored Modules





• gespeicherte Module aus Prozeduren und Funktionen

• Einzelroutinen• Einbindung externer Routinen (implementiert in C, Java, …)• syntaktische Konstrukte für Schleifen, Bedingungen etc.• Basis für Methodenimplementierung für objektrelationaleKonzepte




• gespeicherte Module aus Prozeduren und Funktionen• Einzelroutinen

• Einbindung externer Routinen (implementiert in C, Java, …)• syntaktische Konstrukte für Schleifen, Bedingungen etc.• Basis für Methodenimplementierung für objektrelationaleKonzepte




• gespeicherte Module aus Prozeduren und Funktionen• Einzelroutinen• Einbindung externer Routinen (implementiert in C, Java, …)

• syntaktische Konstrukte für Schleifen, Bedingungen etc.• Basis für Methodenimplementierung für objektrelationaleKonzepte




• gespeicherte Module aus Prozeduren und Funktionen• Einzelroutinen• Einbindung externer Routinen (implementiert in C, Java, …)• syntaktische Konstrukte für Schleifen, Bedingungen etc.

• Basis für Methodenimplementierung für objektrelationaleKonzepte






Vorteile gespeicherter Prozeduren

• bewährtes Strukturierungsmittel

• Angabe der Funktionen und Prozeduren erfolgt in derDatenbanksprache selbst; daher nur vom DBMS abhängig

• Optimierung durch DBMS möglich• Ausführung der Prozeduren erfolgt vollständig unterKontrolle des DBMS

• zentrale Kontrolle der Prozeduren ermöglicht eineredundanzfreie Darstellung relevanter Aspekte derAnwendungsfunktionalität

• Konzepte und Mechanismen der Rechtevergabe des DBMSkönnen auf Prozeduren erweitert werden

• Prozeduren können in der Integritätssicherung verwendetwerden (etwa als Aktionsteil von Triggern)



• bewährtes Strukturierungsmittel• Angabe der Funktionen und Prozeduren erfolgt in derDatenbanksprache selbst; daher nur vom DBMS abhängig








• Optimierung durch DBMS möglich

• Ausführung der Prozeduren erfolgt vollständig unterKontrolle des DBMS

































SQL/PSM: Variablendeklaration

• Variablen vor Gebrauch deklarieren

• Angabe von Bezeichner und Datentyp• optional mit Initialwert

declare Preis float;declare Name varchar(50);declare Menge int default 0;



• Variablen vor Gebrauch deklarieren• Angabe von Bezeichner und Datentyp

• optional mit Initialwert




• Variablen vor Gebrauch deklarieren• Angabe von Bezeichner und Datentyp• optional mit Initialwert



SQL/PSM: Ablaufkontrolle

• Zuweisung

set var = 42;

• Bedingte Verzweigungen

if Bedingung then Anweisungen[ else Anweisungen ] end if;


SQL/PSM: Ablaufkontrolle

• Zuweisung

set var = 42;

• Bedingte Verzweigungen

if Bedingung then Anweisungen[ else Anweisungen ] end if;


SQL/PSM: Ablaufkontrolle /2

• Schleifenloop Anweisungen end loop;while Bedingung do

Anweisungen end while;repeat Anweisungen

until Bedingung end repeat;



• Schleifen mit Cursorfor SchleifenVariable as CursorName cursor for

CursorDeklarationdo

Anweisungenend for;



declare wliste varchar(500) default ' ';declare pos integer default 0;

for w as WeinCurs cursor forselect Name from WEINE where Weingut = 'Helena'

doif pos > 0 then

set wliste = wliste || ',' || w.Name;else

set wliste = w.Name;end if;set pos = pos + 1;

end for;


SQL/PSM: Ausnahmebehandlung

• Auslösen einer Ausnahme (Condition)signal ConditionName;

• Deklarieren von Ausnahmendeclare fehlendes_weingut condition;declare ungueltige_region

condition for sqlstate value '40123';


SQL/PSM: Ausnahmebehandlung

• Auslösen einer Ausnahme (Condition)signal ConditionName;

• Deklarieren von Ausnahmendeclare fehlendes_weingut condition;declare ungueltige_region

condition for sqlstate value '40123';


SQL/PSM: Ausnahmebehandlung /2

• Ausnahmebehandlung

begindeclare exit handler for ConditionNamebegin

-- Anweisungen zur Ausnahmebehandlungend-- Anweisungen, die Ausnahmen auslösen können

end


SQL/PSM: Funktionen

• Funktionsdefinitioncreate function geschmack (rz int)

returns varchar(20)begin

return casewhen rz <= 9 then 'Trocken'when rz > 9 and rz <= 18 then 'Halbtrocken'when rz > 18 and rz <= 45 then 'Lieblich'else 'Süß'

endend


SQL/PSM: Funktionen /2

• Aufruf innerhalb einer Anfrage

select Name, Weingut, geschmack(Restzucker)from WEINEwhere Farbe = 'Rot' and

geschmack(Restzucker) = 'Trocken'

• Nutzung außerhalb von Anfragen

set wein_geschmack = geschmack (12);


SQL/PSM: Funktionen /2

• Aufruf innerhalb einer Anfrage

select Name, Weingut, geschmack(Restzucker)from WEINEwhere Farbe = 'Rot' and

geschmack(Restzucker) = 'Trocken'

• Nutzung außerhalb von Anfragen

set wein_geschmack = geschmack (12);


SQL/PSM: Prozeduren

• Prozedurdefinitioncreate procedure weinliste (in erz varchar(30),

out wliste varchar(500))begin

declare pos integer default 0;

for w as WeinCurs cursor forselect Name from WEINE where Weingut = erz

do-- siehe Beispiel von Folie 12-40

end for;end; end;


SQL/PSM: Prozeduren /2

• Nutzung über call-Anweisungdeclare wliste varchar(500);call weinliste ('Helena', wliste);


SQL/PSM: Zugriffscharakteristik

• Eigenschaften von Prozeduren, die Anfrageausführungund -optimierung beeinflussen

• deterministic: Routine liefert für gleiche Parametergleiche Ergebnisse

• no sql: Routine enthält keine SQL-Anweisungen• contains sql:Routine enthält SQL-Anweisungen(Standard für SQL-Routinen)

• reads sql data: Routine führt SQL-Anfragen(select-Anweisungen) aus

• modifies sql data: Routine, die DML-Anweisungen(insert, update, delete) enthält












• no sql: Routine enthält keine SQL-Anweisungen

• contains sql:Routine enthält SQL-Anweisungen(Standard für SQL-Routinen)

























Zusammenfassung

• Verbindung zwischen SQL und imperativen Sprachen• Call-Level-Schnittstelle vs. Embedded SQL• objektrelationales Mapping• SQL/PSM: imperative Erweiterungen von SQL→Implementierung von Funktionen und Prozeduren


Kontrollfragen

• Welche Konzepte gibt es, um aufSQL-Datenbanken zuzugreifen?

• Was sind die Vor- und Nachteile vonCall-Level-Schnittstellen wie JDBC imVergleich zur Einbettung von SQL?

• Wie lassen sich Anwendungsobjekte aufSQL-Tabellen abbilden? Welche Aufgabenbestehen dabei?


Kontrollfragen





Kontrollfragen





Documents

VL Datenbanksysteme - Wintersemester 2019/2020 · 2019-09-22 · Ebenen-ArchitekturamBeispiel • KonzeptuelleSicht:DarstellunginTabellen(Relationen) Fahrer FahrerID Name Telefon