Teil XI Datenbanken - Lehrveranstaltung "Grundlagen der Informatik

Teil XI
Datenbanken
Überblick
1
Einführung
Grundlegende Begriffe
Motivation
2
Relationale Datenbanksysteme
Das Relationale Datenmodell
SQL
3
Entwurf von Datenbanken
Der Datenbankentwurfsprozess
Das Entity Relationship (ER) Modell
Abbildung von ER-Diagrammen auf Relationenschemata
Normalformen
4
DB-Anwendungsprogrammierung
Programmierschnittstellen
Transaktionen
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
545/644
Datenbanken
Typische Aufgabe von Informationssystemen: Verwaltung
von großen Datenbeständen = Datenbanken (DB)
Zugriff auf Daten durch potentiell große Anzahl von
Nutzern
Hohe Anforderungen bezüglich
Effizienz der Zugriffe (lesend und schreibend)
Konsistenz (Widerspruchsfreiheit) der Daten
Schnittstellen für einfache Nutzbarkeit
Erfüllung dieser Anforderungen durch Programmieren
eigener Lösungen für jede Anwendung extrem aufwändig
Deshalb: spezielle Softwaresysteme zur Verwaltung von
Datenbanken = Datenbankmanagementsysteme
(DBMS)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
546/644
Datenbanken: Grundbegriffe
DBMS
Ein Datenbankmanagementsystem
(DBMS) ist ein Sammlung von
ausführbaren Programmen, welche zur
Umsetzung aller Zugriffe auf eine
Datenbank verwendet werden.
Eine Datenbank (DB) ist ein Sammlung
strukturiert und dauerhaft gespeicherter
Fakten für ein konkretes Anwendungsszenario
DB
DBS = DB + DBMS
Eike Schallehn, FIN/ITI
Ein Datenbanksystem (DBS) ist eine
durch ein DBMS zugreifbare Datenbank
für ein konkretes Anwendungsszenario.
Grundlagen der Informatik für Ingenieure
547/644
Datenbanksysteme: Anwendungsarchitektur
...
Anwendung 1
Anwendung 2
...
Datenbanksystem
Administrator
Ein Datenbanksystem (DBS) kann Daten für viele (oder eine)
Anwendungen bereitstellen
Nutzer können über die Anwendungen (oder direkt) auf im DBS
verwaltete Daten zugreifen
Administratoren als spezielle Nutzer zur Steuerung und
Kontrolle des DBS
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
548/644
Datenbanksysteme: Kommunikationsarchitektur
Anw1
Anw2
Anw1
Anw2
DBS
Heute meist Client Server-Architektur:
Anwendungen können von zahlreichen Installationen
(Clients) auf verschiedenen Rechner über ein Netzwerk auf
das DBS zugreifen
Das DBS läuft auf einem (oder mehreren = verteilt)
Rechnern (Server)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
549/644
Datenbankschema
Struktur der Daten für eine konkrete
Datenbank/Anwendung = Datenbankschema, z.B.
Schema für eine DB mit Studentendaten
Schema für eine DB mit Produktdaten
Schema für eine DB mit Kundendaten
Schema ist formale Festlegung und verwendet ein
Daten(bank)modell als „Sprache“ zur Datendarstellung
Datenmodell umfaßt alle möglichen Mittel zur
Beschreibung der Struktur der Daten, ist
anwendungsunabhängig und durch das verwendete DBMS
festgelegt
Beispiel: das soziale Netzwerk StudiVZ (= DBS)
speichert Daten über Studenten, Freunde, etc. (=
Datenbankschema)
in verschiedenen Tabellen mit Spalten etc. (= Datenmodell)
in einer MySQL (= DBMS)
Datenbank
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
550/644
Datenbanksysteme: Beispiele /1
eBay Online Auktionshaus:
WWW-basierte Plattform zum Kauf oder Verkauf
beliebiger Waren auf Auktionsbasis
212 Millionen Nutzer
26 Millionen Zugriffe pro Tag
2 Petabyte Datenvolumen ≈ 400.000 DVDs voll
Daten
DBMS: Oracle Database, Analysen über Teradata
UnivIS der OvGU:
WWW-basiertes Informationssystem zu
Lehrangeboten an der
Otto-von-Guericke-Universität
Daten zu über 5000 Lehrveranstaltungen, über
5000 Personen, über 400 Räume, etc.
Über 300 schreibberechtigte Nutzer
Ca 1.5 Millionen Anfragen pro Monat
DBMS: eigene Lösung des Anbieters
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
551/644
Datenbanksysteme: Beispiele /2
Wal-Mart Data Warehouse:
System zur Warenkorbanalyse von Daten der
Registrierkassen bei der amerikanischen
Handelskette
500 Terabyte Daten ≈ 100.000 Daten-DVDs
DBMS: Teradata
StudiVZ.net:
WWW-basiertes soziales Netzwerk
Persönliche Daten von über 6 Millionen Nutzern
DBMS: MySQL
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
552/644
Datenbanksysteme: Beispiele /3
SAP ERP :
DB-basiertes Anwendungssystem
Unternehmensweites Informationssystem zur
Unterstützung zahlreicher geschäftsrelevanter
Bereiche
Datenvolumen und Nutzerzahl abhängig vom
konkreten Unternehmen
DBMS: zahlreiche verschiedene DBMS können
verwendet werden
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
553/644
DBS Motivation
Wozu benötigen wir DBMS?
Warum speichern wir die Daten nicht
einfach in Dateien, die wir aus unseren
Anwendungen auslesen?
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
554/644
DBS Motivation: Große Datenmengen
Große Datenmengen vor allem problematisch bzgl. Effizienz
Wie können für einen Nutzer relevante Daten in riesigen
Datenmengen schnell gefunden werden? Zum Beispiel
Eine konkrete Auktion bei eBay aus vielen Terabyte
Auktionsdaten?
Eine bestimmte Person bei StudiVZ aus vielen Millionen?
Wie können große Datenmenge effizient ausgewertet und
analysiert werden? Zum Beispiel
Welche Produkte in Wal-Mart-Filialen wurden im Vergleich
zu den Vorjahren weniger oft verkauft? Und: warum?
→ DBMS bieten für Festplatten optimierte Datenstrukturen und
hoch-effiziente Operationen an
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
555/644
DBS Motivation: Viele Nutzer
Eine große Nutzeranzahl impliziert zwei Anforderungen
Effizienz, zum Beispiel
Wie werden die zahlreichen parallelen Zugriff auf
Web-Datenbanken wie StudiVZ oder eBay umgesetzt?
Wie können diese so ausgeführt werden, dass sie sich
möglichst wenig gegenseitig beeinflussen?
Konsistenz, zum Beispiel
Wie kann sichergestellt werden, dass zwei geleichzeitige
Nutzer von UnivIS ihre Eingaben zu einer Vorlesung nicht
gegenseitig überschreiben?
Wie kann die korrekte Reihenfolgen von Geboten bei eBay
sichergestellt werden?
→ DBMS bieten effiziente Lösungen zur Synchronisation
paralleler Zugriffe
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
556/644
DBS Motivation: Konsistenz
Ablaufkonsistenz bei parallelen Zugriffen: s.o.
Widerspruchsfreiheit durch Vermeidung von
Redundanzen (Problem: mehrfache Abspeicherung), zum
Beispiel
Wie kann vermieden werden, dass zwei Mitarbeiter eines
Unternehmens zwei unterschiedliche Kostenkalkulationen
für ein Produkt erstellen?
Erzwingung konsistenter Datenbankzustände, zum
Beispiel
Wie kann in UnivIS vermieden werden, dass zwei
Vorlesungen zur selben Zeit im selben Hörsaal stattfinden?
Wie kann vermieden werden, dass das Alter einer Person
einen negativen Wert annimmt?
Wie kann vermieden werden, dass zwei Studenten
dieselbe Matrikelnummer haben?
→ DBS zur integrierten (zentralen) Speicherung mit
umfangreichen Mittel zur Sicherstellung der Korrektheit der
Daten
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
557/644
DBS Motivation: Datenschutz und -sicherheit
Datenschutz, zum Beispiel
Wie kann sichergestellt werden, dass nur meine Freunde
bei StudiVZ bestimmte persönliche Daten sehen?
Wie kann eine Firma bestimmte Daten aus SAP ERP ihren
Kunden zur Verfügung stellen, interne Daten aber vor
unberechtigten Einblicken „verstecken“?
Datensicherheit, zum Beispiel
Was passiert mit meinen Daten, wenn mein Rechner
abstürzt?
Was passiert mit Daten, wenn die Festplatte, auf der diese
gespeichert sind, einen irreparablen Schaden hat?
→ DBMS bieten umfangreiche Mechanismen zum Schutz vor
Datenverlust und unberechtigten Zugriffen
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
558/644
DBS Motivation: Einfache Nutzung
Der Nutzer sollte für den Zugriff sein Informationsbedürfnis
beschreiben, nicht aber den Weg, wie dieses erfüllt wird
(deklarative Sprache)
Zugriff auf die Daten sollten möglichst auch ohne
Programmierung (Ad Hoc) möglich sein
Es sollte egal sein, mit welcher Hardware-Plattform der
Nutzer arbeitet
Bei der Nutzung von Daten aus einer Anwendung sollte die
verwendete Programmiersprache beliebig gewählt werden
können
Die Entwicklung von Anwendungsprogrammen sollte
möglichst unabhängig von der Entwicklung der Datenbank
erfolgen können
→ DBMS setzen Zugriff über standardisierte Anfragesprachen
und Programmierschnittstellen um
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
559/644
Warum ... Datenbanken für Ingenieure?
Die Anforderungen von zahlreichen Ingenieuranwendungen
sind typisch für datenbankbasierte Systeme:
Große Datenmengen für Produktmodelle
Zahlreiche Mitarbeiter (Teams von Ingenieuren u.a.) die
gemeinsam diese Daten bearbeiten
Hohe Anforderungen an Konsistenz, Sicherheit und Schutz
der Produktmodelldaten
Deshalb sind zahlreiche Ingenieuranwendungen wie zum Beispiel EDM- oder CAD-Systeme oft unter Nutzung von DBMS
umgesetzt. Auch im Arbeitsumfeld finden sich zahlreiche DBbasierte System wie SAP ERP oder Workflow ManagementSysteme.
Relationale Datenbanksysteme
Einfache Grundidee: speichere alle Daten in Tabellen
Relational, weil ... abgeleitet vom mathematischen Konzept
der Relationen als Menge von Tupeln (etwa:
Tabellenzeilen) mit Werten für Attribute mit
unterschiedlichen Wertebereichen (Tabellenspalten)
Überwiegende Mehrheit aktueller DBMS sind relationale
DBMS → RDBMS
Die standardisierte Datenbanksprache SQL
implementiert relationales Datenmodell (mit kleinen
Abweichungen von der Theorie und von verwendeten
Begriffen)
Hinweis: im folgenden gehen wir von in SQL verwendeten
Begriffen aus
Gegenwärtiger Stand: objekt-relationale DBMS
(ORDBMS) (SQL:2008) mit objektorientierten
Erweiterungen (in dieser Vorlesung nicht behandelt)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
561/644
Aktuell verbreitete DBMS
Kommerzielle relationale DBMS, z.B.
Oracle Database
IBM DB2
Microsoft SQL Server
Freie (Open Source) RDBMS, z.B.
MySQL
PostgreSQL
Speziallösungen:
Für Analyse großer Datenmengen in Data Warehouse
Systemen, z.B. Teradata
Andere Datenmodelle, wie z.B. objektrorientierte DBMS
(Objectivity, Versant) oder XML DBMS (Xindice, eXist)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
562/644
RDBMS Grundkonzepte: Tabellen
6WXGHQW
1DPH
9RUQDPH
*HEXUW
0¾OOHU
6FKXO]H
0HLHU
(YD
3HWHU
6HEDVWLDQ
6FKXO]H
3HWHU
6FKPLGW
/LVD
Tabellen haben Namen und bestehen aus Spalten und Zeilen
Schema der Tabelle besteht aus fester Anzahl von Spalten
Spalten repräsentieren Eigenschaften – haben Namen und
festgelegten Datentyp
Zeilen repräsentieren eigentliche Daten – haben für jede Spalte
einen Spaltenwert
Tabelle hat beliebiebige Anzahl von Zeilen (inklusive leerer
Tabelle)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
563/644
RDBMS Grundkonzepte: Schlüssel
6WXGHQW
0DWU1U
1DPH
9RUQDPH
*HEXUW
0¾OOHU
6FKXO]H
0HLHU
(YD
3HWHU
6HEDVWLDQ
6FKXO]H
3HWHU
6FKPLGW
/LVD
Schlüssel (auch Primärschlüssel) erlauben eindeutige
Identifizierung von Datensätzen (Zeilen) innerhalb einer Tabelle
Einzelne Spalte oder Kombination mehrerer Spalten, deren
Wert(ekombination) innerhalb der Tabelle einmalig ist
Existieren solche Spalten nicht, kann eine Spalte mit künstlich
erzeugten eindeutigen Werten (Surrogatschlüssel) eingeführt
werden
Dient vor allem der Referenzierung der Daten aus anderen
Tabellen → Fremdschlüssel
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
564/644
RDBMS Grundkonzepte: Fremdschlüssel
Tabellen beinhalten bloß Zeilen mit fester Anzahl von
atomaren Werten
Komplexere Beziehungen zwischen Daten werden über
Fremdschlüsselbeziehungen zwischen Zeilen
dargestellt: Verwendung des Schlüssels einer Zeile als
spezieller Spaltenwert in einer anderen Zeile(meist aus
einer anderen Tabelle)
N:1-Beziehung: eine beliebige Anzahl (N) Datensätze in
einer Tabelle beziehen sich auf einen anderen Datensatz
Beispiel: Studenten wird genau ein Studiengang
zugeordnet, ein Studiengang umfaßt viele Studenten
N:M-Beziehung: beliebig viele (N) Datensätze einer
Tabelle können sich auf beliebig viele (M) andere
Datensätze beziehen
Beispiel: ein Student kann viele Vorlesungen besuchen,
eine Vorlesung wird von vielen Studenten besucht
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
565/644
RDBMS Grundkonzepte: Fremdschlüssel N:1
6WXGHQW
0DWU1U 1DPH
9RUQDPH
*HEXUW
6*,'
0¾OOHU
(YD
0%
6FKXO]H
3HWHU
:0%
0HLHU
6HEDVWLDQ
0%
ಹ
ಹ
ಹ
ಹ
ಹ
6WXGLHQJDQJ
Eike Schallehn, FIN/ITI
6*,'
%H]HLFKQXQJ
0%
0DVFKLQHQEDX
:0%
:LUWVFKDIWVLQJHQLHXU0DVFKLQHQEDX
ಹ
ಹ
Grundlagen der Informatik für Ingenieure
566/644
RDBMS Grundkonzepte: Fremdschlüssel N:M
6WXGHQW
9RUOHVXQJ
0DWU1U 1DPH
ಹ
9,'
%H]HLFKQXQJ
ಹ
0¾OOHU
ಹ
*,)
*UXQGODJHQGHU,QIRUPDWLN
ಹ
6FKXO]H
ಹ
.(
.RQVWUXNWLRQVHOHPHQWH
ಹ
0HLHU
ಹ
70
7HFKQLVFKH0HFKDQLN
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
7HLOQDKPH
0DWU1U 9,'
Eike Schallehn, FIN/ITI
6HPHVWHU
*,)
:L6H
*,)
6R6H
70
6R6H
.(
:L6H
ಹ
ಹ
ಹ
Grundlagen der Informatik für Ingenieure
567/644
Weitere RDBMS Konzepte
NULL-Werte: kann ein Spaltenwert nicht angegeben
werden (weil z.B. nicht bekannt oder nicht existent), kann
der vordefinierte und typunabhängige Wert NULL
verwendet werden
Für Spalten und Tabellen können Integritätsbedingungen
(Integrity Constraints) angegeben werden, die
konsistenten Zustand beschreiben
Eindeutigkeit von Spaltenwerten (UNIQUE)
Spaltenwert muss angegeben werden (NOT NULL)
Spaltenwert ist Schlüssel (PRIMARY KEY = UNIQUE +
NOT NULL)
Wertebereichseinschränkungen
Referentielle Integrität: Fremschlüsselwert muss als
Primärschlüssel in korrespondierender Tabelle existieren
...
Zahlreiche weitere Konzepte hier nicht diskutiert
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
568/644
Operationen auf Tabellen
Anfrageoperationen basieren auf Relationaler Algebra
Eingabe: Relation(en)
Ausgabe: Relation(en)
Grundlegende Operationen
Selektion: Auswahl von Tupeln (Zeilen) durch Angabe
einer Auswahlbedingung
Projektion: Auswahl von Attributen (Spalten) durch
Angabe von deren Namen
Verbundoperationen: (engl. Joins) Zusammenführen von
Tupeln verschiedener Relationen (Tabellen) über
Verfolgung von Fremdschlüsselbeziehungen oder durch die
Angabe von Verbundbedingungen
Mengenoperationen: zum Beispiel Vereinigung oder
Schnittmenge von Relationen
→ umgesetzt durch Anfragesprache SQL
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
569/644
SQL – Die Structured Query Language
Deklarative Anfragesprache
SQL Anfrage beschreibt lediglich zu liefernde Daten
RDBMS entscheidet selbständig, wie Ergebnis effizient
berechnet werden kann
Im Gegensatz zu imperativen Programmiersprachen, die
genauen Ablauf der Berechnung festlegen
Geschichte
Entwickelt in den 1970ern bei IBM
Erfolgreiche Standardisierung seit 1986
SQL-92 umfaßt relationalen Sprachkern und wird von
vielen RDBMS vollständig unterstützt
Aktuelle Version SQL:2008 umfaßt zahlreiche
Erweiterungen (Objektorientierung, XML, Multimedia, etc.)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
570/644
Teile von SQL
Anfragesprache (SQL-Kern): lesende Zugriffe durch
Umsetzung der relationalen Operationen zum Auswahl von
Zeilen, Spalten sowie Verbund und Mengenoperationen
auf Tabellen + SQL-spezifische Erweiterungen (z.B.
Sortierung, Gruppierung, etc.)
Data Manipulation Language (DML): Erzeugen, Ändern
und Löschen von Datensätzen in Tabellen
Data Definition Language (DDL): Erzeugen, Ändern und
Löschen von Tabellen sowie Indexen (Baum- oder
Hash-Datenstrukturen für Zugriffsbeschleunigung) und
Sichten (aus Anfragen definierte virtuelle Tabellen)
Weitere Teile:
Zugriffsrechte (Data Control Language)
Transaktionen zur Steuerung der Ablaufkonsistenz
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
571/644
Überblick SQL
Im folgenden Teile von SQL erklärt entsprechend Reihenfolge
der Nutzung – entspricht nicht unbedingt Bedeutung
1
Erzeugung von Tabellen → DDL → Einmalig genutzt beim
Erstellen der Datenbank
2
Einfügen von Daten → DML → Erzeugung und
Modifikation in meisten Anwendungen seltener als ...
3
Lesen der Daten → Anfragesprache → meist sehr oft
angewandt
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
572/644
SQL DDL: Operationen für Tabellen
Erzeugen einer Tabelle
CREATE TABLE student (
matrnr
CHAR(6) PRIMARY KEY,
name
VARCHAR(50) NOT NULL,
vorname VARCHAR(50)NOT NULL,
geburt
DATE,
sgid
CHAR(5)
);
Ändern einer Tabelle: Hinzufügen/Löschen/Ändern von
Spalten, Constraints, etc.
ALTER TABLE student (ADD|DROP|MODIFY|CHANGE) ...;
Löschen einer Tabelle
DROP TABLE student;
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
573/644
SQL DDL: Basisdatentypen laut SQL Standard
Ganzzahlige Datentypen: smallint, int bzw. integer,
bigint
Festkommazahlen (garantierte Genauigkeit der
Nachkommastellen): numeric (n, m) bzw. decimal
(n, m)
Gleitkommazahlen: float (m), real, double
Zeichenketten character (n) bzw. char (n),
varchar (n) bzw. character varying (n)
Zeiten und Datumsangaben: date, time, timestamp
Logische Werte: boolean
Große Binär- oder Textdaten blob (n) bzw. binary
large object (n), clob
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
574/644
SQL DDL: Indexe und Sichten
Erzeugen einer logischen Sicht (virtuelle Tabelle) durch Anfrage
(→)
CREATE VIEW alte_studenten AS
SELECT * FROM student WHERE geburt < ’1980-01-01’;
Sicht kann (mit Einschränkungen bzgl. Änderungen) wie eine
normale Tabelle genutzt werden
Daten werden aber nicht erneut (redundant) abgespeichert
Erzeugen eines Index
CREATE INDEX studenten_name ON student (name);
Erzeugt eine Indexdatenstruktur – in den meisten DBMS einen
B-Baum – welche eine schnelle Suche nach Datensätzen mit der
angegebenen Spalte als Suchkriterium, z.B. bei
SELECT * FROM student WHERE name = ’Müller’;
System erkennt automatisch, dass hier der Index verwendet
werden kann
Ändern und Löschen von Indexen über ALTER und DROP
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
575/644
SQL DML: Daten Einfügen, Ändern, Löschen
Gebräuchlichste Form des INSERT-Statements zum
Einfügen von Zeilen
INSERT INTO student
VALUES (’174551’,’Müller’,’Eva’,’1982-09-05’,’MB’);
Ändern und Löschen von Zeilen basiert auf Angabe einer
Bedingung in WHERE-Klausel (siehe Anfragesprache →),
welche Zeilen davon betroffen sein sollen
UPDATE student
SET name = ’Meier’
WHERE matrnr = ’174551’;
DELETE FROM student
WHERE matrnr = ’173212’;
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
576/644
SQL Anfragesprache
Grundaufbau durch SFW-Block
SELECT <Projektion auf Ausgabespalten>
FROM <Eingabetabellen ggf. mit Verbund>
WHERE <Selektionsbedingungen>;
SELECT und FROM müssen angegeben werden
WHERE ist optional aber meist verwendet
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
577/644
SQL Anfragesprache: Projektion
Projektion ist die Auswahl von in der Ergebnisrelation
enthaltenen Spalten (Auswahl aus Eingaberelation)
In SQL umgesetzt in der SELECT Klausel:
Erfordert Angabe der Spaltennamen
Erlaubt auch Umbenennung durch AS, z.B.
SELECT name AS nachname ...;
Erlaubt im Zusammenhang mit Gruppierung (→) auch
Aufruf von Aggregatfunktionen zur Berechnung von einem
einzelnen Spaltenwert aus ggf. vielen Gruppenwerten (z.B.
Mittelwert, Anzahl, Summe, Minimum, Maximum, ...)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
578/644
SQL Anfragesprache: Projektion Beispiel
6WXGHQW
0DWU1U 1DPH
9RUQDPH
*HEXUW
6*,'
0¾OOHU
(YD
0%
6FKXO]H
3HWHU
:0%
0HLHU
6HEDVWLDQ
0%
6FKXO]H
3HWHU
3+
6FKPLGW
/LVD
:0%
SELECT name, vorname
FROM student;
Eike Schallehn, FIN/ITI
1DPH
9RUQDPH
0¾OOHU
(YD
6FKXO]H
3HWHU
0HLHU
6HEDVWLDQ
6FKXO]H
3HWHU
6FKPLGW
/LVD
Grundlagen der Informatik für Ingenieure
579/644
SQL Anfragesprache: Projektion mit
Duplikateliminierung
SELECT DISTINCT name, vorname
FROM student;
1DPH
9RUQDPH
0¾OOHU
(YD
6FKXO]H
3HWHU
0HLHU
6HEDVWLDQ
6FKPLGW
/LVD
Eliminierung von Duplikaten passiert (im Gegensatz zur
Theorie der relationalen Algebra) in SQL nicht automatisch
Erfordert Angabe des Schlüsselworts DISTINCT
Vorsicht: Duplikateliminierung ggf. sehr aufwändige
Operation, da u.U. Sortierung oder Erstellung einer
Hash-Tabelle notwendig ist
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
580/644
SQL Anfragesprache: Selektion
Selektion ist die Auswahl von Zeilen der Eingabetabelle
für die Ergebnistabelle
In SQL durch die WHERE-Klausel umgesetzt
Selektion hat als Parameter eine Bedingung, welche das
Auswahlkriterium umfaßt
Prädikate sind einfache (atomare) Bedingungen, zum
Beispiel
name = ’Müller’
kontostand > 0
student.sgid = studiengang.sgid
immaDatum < exmaDatum
Komplexe Bedingungen können durch logische
Operatoren AND, OR, NOT (Negation) etc. sowie
Klammerung gebildet werden
Auch existenz- und allquantifizierte geschachtelte
Anfragen als Prädikate möglich (hier nicht behandelt)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
581/644
SQL Anfragesprache: Selektion Beispiel
SELECT *
FROM student
WHERE name = ‘Müller‘
OR name = ‘Schulze‘
0DWU1U 1DPH
9RUQDPH
*HEXUW
6*,'
0¾OOHU
(YD
0%
6FKXO]H
3HWHU
:0%
6FKXO]H
3HWHU
3+
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
582/644
SQL Anfragesprache: Selektionsprädikate
SELECT *
FROM student
WHERE name LIKE
‘S%‘;
0DWU1U 1DPH
9RUQDPH
*HEXUW
6*,'
3HWHU
:0%
6FKXO]H
6FKXO]H
3HWHU
3+
6FKPLGW
/LVD
:0%
SQL beinhaltet zahlreiche spezielle Prädikate, als Operatoren
oder Funktionen
Hier: häufig verwendete Textähnlichkeit durch Wildcard-Muster
mit LIKE (% als Auslassung einer Zeichenfolge beliebiger
Länge, _ als Auslassung eines einzelnen Zeichens)
Im Beispiel: alle Studenten, deren Nachname mit S beginnt
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
583/644
SQL Anfragesprache: Verbund
Verbund (engl. Join) macht aus Zeilen zweier (oder
mehrerer) Eingabetabellen eine Zeile der Ergebnistabelle
Sehr wichtige Operation, da wegen einfacher Struktur des
relationalen Datemodells zusammengehörige Daten meist
über mehrere Tabellen verteilt abgespeichert werden
müssen (z.B. durch Normalisierung, s.u.)
Zahlreiche spezielle Verbundoperationen in SQL durch
verschiedenen Syntax unterstützt
Einfachste und gebräuchlichste Form des Verbundes in
SQL:
Angabe der zu verbindenden Tabellen in der FROM-Klausel
(kommasepariert)
Angabe einer Verbundbedingung (z.B. Primärschlüssel =
Fremdschlüssel) in der WHERE-Klausel
Wichtige Alternativen: Natural Join und Kartesisches
Produkt (s.u.)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
584/644
SQL Anfragesprache: Verbund Eingabe
6WXGHQW
0DWU1U 1DPH
9RUQDPH
*HEXUW
6*,'
0¾OOHU
(YD
0%
6FKXO]H
3HWHU
:0%
0HLHU
6HEDVWLDQ
0%
6FKXO]H
3HWHU
3+
6FKPLGW
/LVD
:0%
6WXGLHQJDQJ
6*,'
%H]HLFKQXQJ
0%
0DVFKLQHQEDX
:0%
:LUWVFKDIWVLQJHQLHXU0DVFKLQHQEDX
3+
3K\VLN
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
585/644
SQL Anfragesprache: Verbund Ausgabe
s.name, s.vorname,
sg.bezeichnung AS studiengang
FROM student s, studiengang sg
WHERE s.sgid = sg.sgid;
SELECT
Eike Schallehn, FIN/ITI
1DPH
9RUQDPH 6WXGLHQJDQJ
0¾OOHU
(YD
0DVFKLQHQEDX
6FKXO]H
3HWHU
:LUWVFKDIWVLQJHQLHXU0DVFKLQHQEDX
0HLHU
6HEDVWLDQ
0DVFKLQHQEDX
6FKXO]H
3HWHU
3K\VLN
6FKPLGW
/LVD
:LUWVFKDIWVLQJHQLHXU0DVFKLQHQEDX
Grundlagen der Informatik für Ingenieure
586/644
SQL Anfragesprache: Weitere
Verbundoperationen
Gleiches Ergebnis alternativ über NATURAL JOIN möglich
SELECT name, vorname, bezeichnung AS studiengang
FROM student NATURAL JOIN studiengang;
Kann direkt in der FROM-Klausel angegeben werden
Funktioniert nur, wenn namensgleiche Spalten in beiden
Tabellen existieren
Für diese Spalten werden Zeilen mit gleichen
Spaltenwerten verbunden
Was passiert, wenn keine Verbundbedingung angegeben
wird? → Berechnung des kartesischen Produkts
(Kreuzprodukt)
Jede Zeile der einen Eingabetabelle wird mit jeder Zeile der
anderen Eingabetabelle verbunden (alle möglichen
Kombinationen)
Vorsicht: Ergebnis kann u.U. sehr groß sein
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
587/644
SQL Anfragesprache: Kartesisches Produkt
T1
$
%
SELECT *
FROM t1,t2;
T2
&
'
Eike Schallehn, FIN/ITI
$
%
&
'
Grundlagen der Informatik für Ingenieure
588/644
SQL Anfragesprache: Gruppierung
SELECT sgid, COUNT(*) AS anzahl
FROM student
GROUP BY sgid;
6*,'
$Q]DKO
0%
:0%
3+
Gruppierung fasst Zeilen mit gleichen Werten für
Gruppierungsspalten zu einer Zeile zusammen
Spalten, die nicht Gruppierungsspalten sind, und somit
keine gleichen Werte haben, können mit
Aggregatfunktionen zusammengefaßt werden, z.B
COUNT() - Anzahl von Werten
SUM() - Summe der Werte
AVG() - Mittelwert
MIN() - Minimum
MAX() - Maximum
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
589/644
SQL Anfragesprache: Sortierung
SELECT *
FROM student
ORDER BY matrnr ASC;
0DWU1U 1DPH
9RUQDPH
*HEXUW
0HLHU
6HEDVWLDQ
6*,'
0%
6FKXO]H
3HWHU
:0%
0¾OOHU
(YD
0%
6FKXO]H
3HWHU
3+
6FKPLGW
/LVD
:0%
Angabe eines Sortierkriteriums für die Ergebnistabelle
bestehend aus Spalte(n) und Reihenfolge ASC (ascending =
aufsteigend, default) oder DESC (descending = absteigend)
Reihenfolge der Zeilen in der Ergebnistabelle erhält damit
konkrete Bedeutung → Tabelle entspricht dann Datentyp Liste,
ohne Sortierung Multimenge
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
590/644
SQL Anfragesprache: Mengenoperationen
SELECT *
FROM t1
UNION
SELECT *
FROM t2;
$
%
Mengenoperationen UNION (Vereinigung), INTERSECT
(Schnittmenge) und EXCEPT (Mengendifferenz)
Erwartet für Eingabetabellen kompatible Schemata
(gleiche Spaltenanzahl mit kompatiblen Datentypen)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
591/644
Zusammenfassung: RDBMS
Relationales Datenmodell heute Standard im Bereich
Datenbanken
Darstellung von Daten in Form von Tabellen mit
festgelegter Struktur
Zeilen repräsentieren Datenobjekte
Spalten legen Wertebereiche für einzelne Eigenschaften
fest
Komplexere Beziehungen durch Schlüsselbeziehungen
über verschiedene Tabellen hinweg dargestellt
SQL als deklarative Anfragesprache für RDBMS
SELECT ... FROM ... WHERE-Block für lesende
Zugriffe
INSERT, UPDATE und DELETE zur Modifikation von Daten
(DML)
CREATE, ALTER und DROP zur Veränderung der Schemata
(Tabellendefinitionen) (DDL)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
592/644
Entwurf von Datenbanken
Bisher: was sind Datenbanken? Wie funktionieren sie?
Im Folgenden: wie entwickle ich eine Datenbank? Was ist
eine gute Datenbank?
Der Datenbankentwurfsprozess
Das Entity Relationship (ER) Modell
Abbildung von ER-Diagrammen auf Relationenschemata
Normalformen als Qualitätskriterien
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
593/644
Der Datenbankentwurfsprozess
Datenbankentwurfsprozess beschreibt systematische
Vorgehensweise zur Entwicklung einer Datenbanklösung:
Ausgehend von Anforderungen an zu entwickelnde Lösung
über eine
schrittweise Verfeinerung des Entwurfs bis hin zur
Implementierung und zum Einsatz der Lösung
Angelehnt an Software-Entwicklungsprozess (→) zur
Entwicklung allgemeiner Software-Lösungen
Unabhängig von konkretem Anwendungsszenario
Im folgenden: Entwurf relationaler Datenbanken
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
594/644
Phasen des Datenbankentwurfsprozesses
Anforderungsanalyse
Dokumentatation
Eike Schallehn, FIN/ITI
Konzeptueller
Entwurf
Konzeptuelles
Schema
Logischer
Entwurf
z.B. Entity
Relationship
Diagramm
Logisches
Schema
Datendefintion und
Implementierung
= Tabellen- und
Spaltendefinition
Datenbank
Grundlagen der Informatik für Ingenieure
595/644
Phasen des Datenbankentwurfs /1
Anforderungsanalyse: Sammlung von Anforderungen, die zu
entwickelndes Datenbanksystem beschreiben
Z.B. Informationsbedarf zukünftiger
Anwender, zu unterstützende Abläufe, etc.
Ergebnis: informell festgehaltene
Dokumentation der Anforderungen
Konzeptueller Entwurf: Entwicklung eines
implementierungsunabhängigen (abstrakt,
high-level) Datenbankschemas
Erste Strukturierung für Anwendungsdaten
Dient der schrittweisen Verfeinerung des
Entwurfs sowie der Diskussion verschiedener
Entwickler untereinander und mit Anwendern
Ergebnis: konzeptuelles Schema, z.B. als
Entity Relationship Diagramm
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
596/644
Phasen des Datenbankentwurfs /2
Verteilungsentwurf (optional): nur für verteilte Systeme
Festlegung des Speicherorts der Daten im Netz
Prinzipiell unabhängig vom
Implementierungsmodell (nächster Schritt)
Erfolgt meist aber als Teil des physischen
Entwurfs
Ergebnis: Verteilungsschema
Logischer Entwurf: Überführung in relationales Datenmodell
für Implementierung sowie Erfüllung von
Qualitätskriterien (Normalformen) durch
Normalisierung
Entwurf geeigneter Tabellenstrukturen zur
Darstellung der Anwendungsdaten
Qualitätskriterium: Strukturen vermeiden
Abspeicherung widersprüchlicher Daten
Ergebnis: logisches Schema
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
597/644
Phasen des Datenbankentwurfs /3
Physischer Entwurf: ermöglicht Beeinflussung interner
Speicherstrukturen zu Zwecken der Performance
Optimierung
Festlegen von Indextsrukturen
(Hash-Tabellen, B-Bäume) für Zugriffspfade
Weitere Mittel: materialisierte Sichten
(Vorberechnung) sowie Partitionierung (Teile
und Herrsche)
Datendefinition und Implementierung: Erstellen
enstprechender DDL-Statements und deren
Ausführung
Erzeugung von Tabellen, Sichten und
Indexstrukturen
Ergebnis: (leere) Datenbank
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
598/644
Das Entity Relationship (ER) Modell
Standard für die konzeptuelle Modellierung von
Datenbankschemata
Ziel: Darstellung der Inhalte und Bedeutung (auch semantische
Modellierung)
Was wird durch das Schema dargestellt (welche Daten)?
Nicht: wie werden die Daten dargestellt
(Implementierung)?
Dient der Diskussion (Entwickler und Anwender) und
Verfeinerung der Schemata
Deshalb möglichst einfache Modellierungskonstrukte:
Gegenstände (Entities), deren Beziehungen untereinander
(Relationships) und Eigenschaften (Attributes)
Eigentliche Modellierung auf Typebene: Gegenstände mit
gleichen Eigenschaften und Beziehungen werden zu einem
Entity Type zusammgefaßt (analog Relationship Types)
Begriffe Entity und Relationship werden meist verkürzend für
Entity Types bzw. Relationship Types verwendet
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
599/644
ER Modell: Einführendes Beispiel
(QWLW\ 6WXGHQW UHSUlVHQWLHUW
DOOH2EMHNWHYRP7\S6WXGHQW
Student
5HODWLRQVKLS EHVXFKW UHSUlVHQWLHUW
DOOHH[LVWLHUHQGHQ%H]LHKXQJHQ
]ZLVFKHQ6WXGHQWHQREMHNWHQ
XQG9RUOHVXQJVREMHNWHQ
besucht
MatrNr
Name
Vorlesung
ID
Semester
Bezeichnung
Vorname
$WWULEXWHGHU(QWLW\ 6WXGHQW
ZHUGHQ GLHVHU]XJHRUGQHW
6FKOVVHODWWULEXWHZHUGHQ
XQWHUVWULFKHQ
Eike Schallehn, FIN/ITI
$XFK5HODWLRQVKLSV N|QQHQ
$WWULEXWHKDEHQGHU6FKOVVHO
HUJLEWVLFKDEHULPPHUDXVGHQ
6FKOVVHOQGHUEHWHLOLJWHQ(QWLWLHV
Grundlagen der Informatik für Ingenieure
600/644
ER Modell: Grundlegende Grafische Notation
Entity (Type): Rechteck mit Typbezeichner
Relationship (Type): Raute mit Typbezeichner
Attribut: abgerundete Box oder Ellipse mit
Attributbezeichner, Schlüssel mit Unterstreichung
Zahlreiche abweichende grafische Darstellungen in
verwandten Ansätzen und Entwicklungs-Tools mit gleicher
oder ähnlicher Bedeutung sowie ggf. Erweiterungen
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
601/644
ER Modell: Kardinalitäten
Kardinalitäten geben numerische Grenzen an, wie Objekte
verschiedener Typen miteinander in Beziehung stehen
können
Beispiele:
Ein Student kann beliebig viele Vorlesungen besuchen
Eine Vorlesung kann (je nach Kapazität des Hörsaals) von
vielen Studenten besucht werden
Eine Vorlesung wird von genau einem Dozenten angeboten
Eine Person kann mit maximal einer anderen Person
verheiratet sein (optional)
Jede Person hat genau eine Mutter und genau einen Vater
Von entscheidender Bedeutung bei Überführung in das
Relationenmodell
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
602/644
ER Modell: Kardinalitäten 1:N
Dozent
[1,1]
hält
[1,*]
Vorlesung
ist äquivalent zu:
Dozent
1
hält
*
Vorlesung
1:N-Beziehung: ein Objekt darf mit beliebig vielen eines
anderen Typs in Beziehung stehen, aber eindeutige
Zuordnung in die andere Richtung
Min/Max-Notation: Angabe der minimimalen und
maximalen Anzahl, in der das Objekt in Beziehung stehen
kann
Abkürzende Schreibweise verwendet nur Obergrenze
(Optionalität mit Untergrenze 0 so aber schlecht abbildbar)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
603/644
ER Modell: Kardinalitäten N:M
Student
Vorlesung
besucht
ist äquivalent zu:
Student
*
besucht
*
Vorlesung
N:M-Beziehungen (Objekte beider beteiligter Typen
können beliebig oft in Beziehung stehen) sind bei keiner
Angabe von Kardinalität der angenommene Standardfall
Oft auch auch N und M als Notation für Kardinalitäten
verwendet
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
604/644
ER Modell: Optionale Beziehungen
verheiratet
[0,1]
Person
[0,1]
Beispiel für eine optionale Beziehung
Außerdem selbst-bezüglich auf Typ-Ebene: auch Objekte
des selben Typs können in Beziehungen zueinander
stehen
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
605/644
ER Modell: Weitere Konstrukte
Dozent
Eike Schallehn, FIN/ITI
Vorlesung
Gebäude
hält
hat
Raum
Raum
Mehrstellige
Beziehungstypen
Schwache (existentiell
abhängige) Entitätstypen
Grundlagen der Informatik für Ingenieure
606/644
Abbildung von ER-Diagrammen auf
Relationenschemata
ER Modell ist prinzipiell unabhängig vom
Implementierungsmodell
In der Praxis meist eingesetzt als Entwurfsmittel für
relationale Datenbanken
Überführung von ER Diagrammen auf
Relationenschemata geschieht nach einfachen Regeln
Im folgenden illustriert an folgendem einfachen Beispiel:
Artikel
*
in
ArtikelNr
Bezeichnung
Preis
Eike Schallehn, FIN/ITI
*
Bestellung
*
BestellNr
Anzahl
von
1
Kunde
KundenNr
Rabat
Name
Datum
Anschrift
Grundlagen der Informatik für Ingenieure
607/644
Abbildung von ER-Diagrammen: Entities
Artikel
Artikel
$UWLNHO1U
%H]HLFKQXQJ
3UHLV
ArtikelNr
ಹ
ಹ
ಹ
Bezeichnung
Preis
Alle Entities werden auf separate Tabellen abgebildet
Attribute werden Spalten, konkrete Datentypen müssen
festgelegt werden
Schlüsselattribute werden Schlüssel der Tabelle
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
608/644
Abbildung von ER-Diagrammen:
N:M-Beziehungen
Artikel
*
*
in
Bestellung
ArtikelNr
BestellNr
Anzahl
Bezeichnung
Rabat
Preis
Datum
Artikel
Bestellung
$UWLNHO1U
%H]HLFKQXQJ
3UHLV
%HVWHOO1U
5DEDW
'DWXP
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
ArtikelBestellung
Eike Schallehn, FIN/ITI
$UWLNHO1U
%HVWHOO1U $Q]DKO
ಹ
ಹ
ಹ
Grundlagen der Informatik für Ingenieure
609/644
Abbildung von ER-Diagrammen:
N:M-Beziehungen /2
N:M-Beziehungen müssen generell auf separate Tabellen
abgebildet werden
Schlüssel der Beziehungstabelle bildet sich aus
zusammengesetzten Schlüsseln der in Beziehung stehen
Entity-Tabellen
Teilschlüssel dienen als Fremdschlüssel auf Entity-Tabellen
Attribute der Beziehung werden Spalten der
Beziehungstabelle
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
610/644
Abbildung von ER-Diagrammen:
1:N-Beziehungen
Bestellung
*
1
von
Kunde
BestellNr
KundenNr
Rabat
Name
Datum
Anschrift
Kunde
Bestellung
%HVWHOO1U
5DEDW 'DWXP
.XQGHQ1U
.XQGHQ1U
1DPH
$QVFKULIW
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
ಹ
Bei 1:N-Beziehungen Verschmelzung der
Beziehungstabelle mit der Entity-Tabelle der N-Kardinalität
möglich
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
611/644
Abbildung von ER-Diagrammen: Optionale
Beziehungen
Optionale Beziehungen, egal ob N:M, 1:N oder 1:1, sollten
als separate Tabelle umgesetzt werden
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
612/644
Schemakonsistenz
Ergebnis der Überführung ist relationales Datenbankschema
Zweiter Teilschritt des logischen Entwurfs umfaßt Sicherstellung
der Schemakonsistenz
Allgemein drei wichtige Kriterien der Konsistenz
(Widerspruchsfreiheit) für Schemata und Daten
Modellkonsistenz: reale Informationen können im Schema
korrekt dargestellt werden → muss durch konzeptuellen
Entwurf und korrekte Überführung in Relationenmodell
sichergestellt werden
Semantische Konsistenz: die gespeicherten Daten sind
korrekt (stehen nicht im Widerspruch zur Wirklichkeit) →
kann durch Integritätsbedingungen und Anwendungslogik
unterstützt werden, letzten Endes aber Verantwortlichkeit
der Anwender
Schemakonsistenz: Daten müssen untereinander
widerspruchsfrei sein → Sicherstellung durch Vermeidung
mehrfacher Abspeicherung von Informationen (Redundanz)
→ Normalformen als Qualitätskriterium
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
613/644
Redundanz und Inkonsistenzen
$QVFKULIW
1DPH
9RUQDPH
3/=
6WDGW
$GUHVVH
0¾OOHU
(YD
0DJGHEXUJ
/HLWHUVWUD¡H
6FKXO]H
3HWHU
=HQWUXP
8OULFKSODW]
6RPPHU
6LHJIULHG
6FK¸QHEHFN
$P$QJHU
6RPPHU
6LHJIULHG
6FK¸QHEHFN
$QJHU
Mehrfache Speicherung der selben Realweltfakten
(Redundanz) ermöglicht Dateninkonsistenzen
Erkennbar an „Abhängigkeiten zwischen Attributwerten“
Sollen durch Normalisierung vermieden werden
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
614/644
Funktionale Abhängikeiten
Funktionale Abhängikeiten in einer Tabelle liegen vor,
wenn Werte einer Spalte (oder einer Gruppe von Spalten)
einen eindeutigen Schluss auf die Werte einer anderen
(Gruppe von) Spalte(n) zulassen
„Funktional“, weil ... eindeutige Werteabbildung entspricht
mathematischem Konzept der Funktion: für einen
Eingabewert ist nur ein Ergebniswert möglich
(Eindeutigkeit)
Beispiele:
Die Postleitzahl bestimmt eindeutig den Ort
Die Matrikelnummer (Schlüssel) bestimmt alle weiteren
Eigenschaften eines Studenten
Vorwahl und Telefonnummer bestimmen eindeutig alle
Eigenschaften des Anschlusses
Semester, Termin und Raum bestimmen eindeutig Vorlesungstitel
und Dozenten
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
615/644
Normalformen
Ziel der Normalisierung: alle Spalten einer Tabelle sollen
nur vom vollständigen Schlüssel abhängen, d.h. dadurch
bestimmt sein (3. Normalform)
Erreichen von Normalformen z.B. durch schrittweises
Zerlegen
Wichtigste Normalformen:
1. Normalform: nur atomare Werte in jeder Spalte
2. Normalform: keine funktionalen Abhängigkeiten von
einem Teil des Schlüssels
3. Normalform: keine funktionalen Abhängigkeiten
zwischen Nicht-Schlüsselattributen
Zahlreiche weitere Normalformen existieren
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
616/644
1. Normalform: Problem
0XVLNJUXSSHQ
%DQG
*U¾
*U¾QGXQJ
*HQUH
5DGLRKHDG
$OWHUQDWLYH5RFN$UW5RFN%ULWSRS
:LOFR
$OWHUQDWLYH&RXQWU\,QGHSHQGHQW
3DYHPHQW
,QGHSHQGHQW1RLVH 3RS
ಹ
ಹ
ಹ
1. Normalform: nur atomare Werte in jeder Spalte
(grundlegende Anforderung im Relationenmodell)
Problem: mengen- oder listenwertige Spalten
Eigentlich kein Problem bzgl. Redundanz, aber
Voraussetzung für weitere Normalformen
Erleichtert Lesen und Modifikation von Daten
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
617/644
1. Normalform: Lösung
0XVLNJUXSSHQ
*UXSSHQ*HQUH
%DQG
*U¾
*U¾QGXQJ
%DQG
*HQUH
5DGLRKHDG
5DGLRKHDG
%ULWSRS
:LOFR
5DGLRKHDG
$UW5RFN
3DYHPHQW
5DGLRKHDG
$OWHUQDWLYH5RFN
ಹ
ಹ
:LOFR
,QGHSHQGHQW
:LOFR
$OWHUQDWLYH&RXQWU\
3DYHPHQW
1RLVH 3RS
3DYHPHQW
,QGHSHQGHQW
ಹ
ಹ
Abspalten einer separaten Tabelle mit folgenden Spalten:
Schlüssel der Ursprungstabelle
Spalte für einzelne Einträge der Menge
Schlüssel der neuen Tabelle sind beide Spalten
gemeinsam
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
618/644
2. Normalform: Problem
:DKOHUJHEQLV
:DKOEH]LUN 6WDGW
%XQGHVODQG
%HWHLOLJXQJ
=HQWUXP
0DJGHEXUJ
6DFKVHQ$QKDOW
6XGHQEXUJ
0DJGHEXUJ
6DFKVHQ$QKDOW
6WDGWIHOG
0DJGHEXUJ
6DFKVHQ$QKDOW
*UR¡ .OHLQ
5RVWRFN
0HFNOHQEXUJ 9RUSRPPHUQ =HQWUXP
5RVWRFN
0HFNOHQEXUJ 9RUSRPPHUQ ಹ
ಹ
2. Normalform: 1. Normalform + keine funktionalen
Abhängigkeiten von nur einem Teil des Schlüssels
Problem: mögliche Redundanzen durch sich oft
wiederholende Wertepaare
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
619/644
2. Normalform: Lösung
:DKOHUJHEQLV
:DKOEH]LUN 6WDGW
6WDGW%XQGHVODQG
%HW
6WDGW
%XQGHVODQG
0DJGHEXUJ 0DJGHEXUJ
6DFKVHQ$QKDOW
6XGHQEXUJ
0DJGHEXUJ 5RVWRFN
0HFNOHQEXUJ 9RUSRPPHUQ
6WDGWIHOG
0DJGHEXUJ ಹ
*UR¡ .OHLQ
5RVWRFN
=HQWUXP
5RVWRFN
ಹ
ಹ
=HQWUXP
Abspalten einer separaten Tabelle mit folgenden Spalten:
Teilschlüssel der Ursprungstabelle, von welchem andere
Spalte(n) abhängig
Alle vom Teilschlüssel abhängig Spalten
Abhängige Spalten werden aus der Originaltabelle entfernt
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
620/644
3. Normalform: Problem
6WXGHQW
0DWU1U
1DPH
9RUQDPH
3/=
6WDGW
$GUHVVH
0¾OOHU
(YD
0DJGHEXUJ
/HLWHUVWUD¡H
6FKXO]H
3HWHU
0DJGHEXUJ
8OULFKSODW]
6RPPHU 6LHJIULHG
6FK¸QHEHFN
$P$QJHU
:LQWHU
:XVW
'RUISODW]
5REHUW
3. Normalform: 2. Normalform + keine funktionalen
Abhängigkeiten zwischen Nicht-Schlüsselattributen
Problem: mögliche Redundanzen durch sich oft
wiederholende Wertepaare
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
621/644
3. Normalform: Lösung
6WXGHQW
0DWU1U
1DPH
9RUQDPH
3/=
$GUHVVH
0¾OOHU
(YD
/HLWHUVWUD¡H
6FKXO]H
3HWHU
8OULFKSODW]
6RPPHU 6LHJIULHG
$P$QJHU
:LQWHU
'RUISODW]
5REHUW
3/=6WDGW
3/=
6WDGW
0DJGHEXUJ
6FK¸QHEHFN
:XVW
Abspalten einer separaten Tabelle mit folgenden Spalten:
Bestimmende Spalte(n) als Schlüssel
Alle davon abhängigen Spalten
Abhängige Spalten werden aus der Originaltabelle entfernt
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
622/644
Normalformen in der Praxis
Praktisch relevant zur Vermeidung von Inkonsistenzen
Aber: Zerlegung von Tabellen führt zu höherem Aufwand
bei der Anfragebearbeitung durch mehr
Verbundoperationen
Deshalb oft Abstriche von Normalformen → kontrollierte
Redundanz
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
623/644
Zusammenfassung: DB-Entwurf
Entwurfsprozess für Datenbanken angelehnt an
allgemeine Entwurfsprozesse: Analyse des Problems,
schrittweise Verfeinerung der Lösung bis hin zur
Implementierung
ER-Modell als implementierungsunabhängige
Modellierungsmethode für Datenbankschemata
Überführung in das Relationenmodell entsprechend festen
Regeln
Normalformen als Qualitätskriterien für Tabellen
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
624/644
DB-Anwendungsprogrammierung
Hauptaufgabe: Abbildung der unterschiedlichen Datenmodelle
und Zugriffsparadigmen zwischen Programmiersprache und
dem DBMS, z.B.
C++
SQL
Basisdatentypen und flexible
Typkonstruktoren wie
Strukturen und Klassen
Tabellen
(Multimengen/Listen) von
Zeilen mit Attributwerten von
Basisdatentypen
Basisdatentypen
entsprechend C++ Standard
Plattform- und
Programmiersprachen
unabhängige
Basisdatentypen
Imperative
Programmiersprache (wie
wird das Ergebnis berechnet)
Deklarative Anfragesprache
(was soll das Ergebnis sein)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
625/644
Aufgaben von Programmierschnittstellen
Verbindung zum DBMS
Zugriff auf konkrete Datenbank
Absetzen von Anfragen
Anwendung
Schnittstelle
Client
Kapselung der Datenbankfunktionalität
durch geeignete Funktionen / Strukturen /
Klassen für
Treiber
Zugriff auf Ergebnisse
DBMS
Server
Geeignete Datenstrukturen für
mengenwertige Anfrageergebnisse
Zugriff über imperative
Programmiersprache → Cursor- oder
Iterator-Konzept zum zeilenweisen
Auslesen der Ergebnisse
Zugriff auf Beschreibung von Tabellen und
Anfrageergebnissen, z.B. welche Spalten
hat das gerade übertragene Ergebnis
DB
Umgesetzt als Bibliotheken, die auf Treiber
(optional) und Protokoll zur Kommunikation
mit DBMS Server abbilden
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
626/644
Programmierschnittstellen
engl. Application Programming Interface (API)
Zahlreiche verschiedene Schnittstellen existieren
Unterscheidung nach verschiedenen Kriterien möglich
Abstraktionsstufe: Low-level (Absetzen von Anfragen,
generische Ergebnistypen) bis High-level (z.B.
definierte/definierbare Abbildung auf Anwendungsobjekte)
Abhängigkeit oder Unabhängigkeit von
Programmiersprache
Hardware-/Betriebssystemplattform
konkretem DBMS
Im folgenden 2 Beispiele: ODBC und proprietäre MySQL
Anbindung
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
627/644
ODBC
Open Database Connectivity
Low-level: Aufbau von Verbindungen, Absetzen von
Anfragen, Lesen generischer Ergebnisse)
Unabhängig von Programmiersprache: Schnittstelle
bestehend aus Funktionen mit Handles (Strukturen) zur
Verwaltung der Zustandsinformationen
Unabhängig von Hardware und Betriebssystem:
ursprünglich Umsetzung des CLI-Standards (Call Level
Interface) für Microsoft Windows, mittlerweile aber auf
vielen Plattformen
Unabhängig vom verwendeten DBMS: Treiber für fast alle
kommerziellen DBMS verfügbar
Extrem flexibel, dafür aber nicht sehr einfach in der
Handhabung → siehe folgendes Beispielprogramm zum
Auslesen der Tabelle Studenten
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
628/644
ODBC Beispiel
Aufbau einer Datenbankverbindung und komplettes Lesen
der Student-Tabelle
Code auf der Web-Seite zur Vorlesung
Übersetzung und Ausführung des Beispiels erfordern
Installiertes MySQL DBMS
Beispieldatenbank entsprechend Script auf Web-Seite zur
Vorlesung
Installierten MySQL ODBC Treiber
Konfiguration der MySQL Datenbank als ODBC-Quelle
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
629/644
ODBC Beispiel /1
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <iostream>
using namespace std;
int main() {
SQLHENV sql_hEnv = 0;
SQLHDBC sql_hDBC = 0;
SQLHSTMT sql_hStmt = 0;
SQLSMALLINT nSize = 0;
SQLRETURN sqlRet;
...
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
630/644
ODBC Beispiel /2
...
SQLAllocHandle( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&sql_hEnv );
SQLSetEnvAttr( sql_hEnv,
SQL_ATTR_ODBC_VERSION,
(void*) SQL_OV_ODBC3,
0 );
SQLAllocHandle(SQL_HANDLE_DBC,
sql_hEnv,
&sql_hDBC );
sqlRet = SQLConnect( sql_hDBC,
(SQLCHAR*)”gif”,
SQL_NTS,
(SQLCHAR*)””,
SQL_NTS,
(SQLCHAR*)””,
SQL_NTS );
...
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
631/644
ODBC Beispiel /3
...
if (SQL_SUCCEEDED(sqlRet)) {
sqlRet = SQLAllocHandle( SQL_HANDLE_STMT,
sql_hDBC,
&sql_hStmt );
sqlRet = SQLExecDirect( sql_hStmt,
(SQLCHAR*)”SELECT * FROM gif.student;”,
SQL_NTS );
SQLSMALLINT nCols = 0;
SQLINTEGER nRows = 0;
SQLINTEGER nIdicator = 0;
SQLCHAR buf[1024] = {0};
SQLNumResultCols( sql_hStmt, &nCols );
SQLRowCount( sql_hStmt, &nRows );
...
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
632/644
ODBC Beispiel /4
...
while(SQL_SUCCEEDED(sqlRet = SQLFetch(sql_hStmt))) {
cout << ”Student: ” ;
for (int i=1; i <= nCols; ++i ) {
sqlRet = SQLGetData( sql_hStmt,
i,
SQL_C_CHAR,
buf,
1024,
&nIdicator );
if (SQL_SUCCEEDED( sqlRet )) {
cout << buf;
}
if (i==nCols) cout << endl;
else cout << ”, ”;
}
}
...
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
633/644
ODBC Beispiel /5
...
SQLFreeHandle( SQL_HANDLE_STMT, sql_hStmt );
SQLDisconnect( sql_hDBC );
}
else {
cout << ”Fehler bei der Verbindung zur Datenbank!”
<< endl;
}
SQLFreeHandle( SQL_HANDLE_DBC, sql_hDBC );
SQLFreeHandle( SQL_HANDLE_ENV, sql_hEnv );
return 0;
}
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
634/644
MySQL Connector/C++
Proprietäre Schnittstelle für MySQL DBMS
Low-level
Nur für C++: objektorientierte Schnittstelle mit Klassen und
Methoden, aber angelehnt an JDBC (Industriestandard für
Datenbankzugriffe in Programmiersprache Java) und
ähnliche zu MySQL Connector-Implementierungen für
andere Programmiersprachen
Unabhängig von Hardware und Betriebssystem: Bibliothek
für zahlreiche Plattformen verfügbar
Abhängig vom verwendeten DBMS: funktioniert nur mit
MySQL
Vergleichsweise einfache und intuitive Nutzung
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
635/644
MySQL Beispiel
Aufbau einer Datenbankverbindung und Lesen von 2
Spalten der Student-Tabelle
Code auf der Web-Seite zur Vorlesung
Übersetzung und Ausführung des Beispiels erfordern
Installiertes MySQL DBMS
Beispieldatenbank entsprechend Script auf Web-Seite zur
Vorlesung
Installierten MySQL Connector/C++
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
636/644
MySQL Beispiel /1
#include
#include
#include
#include
#include
#include
#include
<stdlib.h>
<iostream>
”mysql_connection.h”
<cppconn/driver.h>
<cppconn/exception.h>
<cppconn/resultset.h>
<cppconn/statement.h>
using namespace std;
int main() {
try {
sql::Driver *driver;
sql::Connection *con;
sql::Statement *stmt;
sql::ResultSet *res;
...
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
637/644
MySQL Beispiel /2
...
driver = get_driver_instance();
con = driver->connect(””, ””, ””);
con->setSchema(”gif”);
stmt = con->createStatement();
res = stmt->executeQuery(”SELECT * FROM student”);
while (res->next()) {
cout << res->getString(”name”) << ”, ”;
cout << res->getString(”vorname”) << endl;
}
...
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
638/644
MySQL Beispiel /3
...
delete res;
delete stmt;
delete con;
} catch (sql::SQLException &e) {
cout << ”ERROR: ” << e.what();
cout << ” MySQL error code: ” << e.getErrorCode()
<< endl;
}
cout << endl;
return 0;
}
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
639/644
Transaktionen
Transaktion: Folgen von Datenbankoperationen, die für
die Ausführung als logische Einheit betrachtet werden
Transaktion:
Checke Konto X:
Überweisung(X, Y, Betrag)
SELECT ...
X = X - Betrag:
UPDATE ...
Checke Konto Y:
SELECT ...
Y = Y + Betrag:
UPDATE ...
Erfolgreich beendet:
Commit
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
640/644
ACID-Eigenschaften
Transaktion müssen dem ACID-Prinzip entsprechend vom
DBMS ausgegeführt werden:
Atomicity (Atomarität): eine Transaktion muss als Einheit ausgeführt
werden, d.h. entweder ganz oder gar nicht
Consistency (Konsistenz): eine Transaktion muss die Datenbank
immer von einem konsistenten Zustand in einen
konsistenten Zustand überführen (auch wenn
Zwischenzustände ggf. inkonsistent sein können)
Isolation (Schutz bei Nebenläufigkeit): bei der zeitgleichen
Ausführung von Transaktionen (z.B. durch mehrere
Nutzer) dürfen in einer Transaktion keine Effekte
paralleler, noch nicht abgeschlossener Transaktionen
sichtbar sein
Durability (Dauerhaftigkeit): wird eine Transaktion erfolgreich
beendet, so kann der von ihr erzielte Effekt nicht
nachträglich rückgängig gemacht werden
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
641/644
Beispiel: Problem Atomarität
TXN: Überweisung
Checke Konto X
X = X - Betrag
Checke Konto Y:
Fehler: Konto gesperrt
ABBRUCH
Zurücksetzen
aller zuvor
gemachten
Änderungen
Beenden der
Transaktion
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
642/644
Beispiel: Problem Isolation
Parallele Ausführung zweier Transaktionen:
TXN: Überweisung
TXN: Zinsen
Checke Konto X
Lies Konto X
X = X - Betrag
Zinsen = X * Zinssatz
X = X + Zinsen
Inkonsistenter Zustand, der die Überweisung
des Betrages überschreibt, muss durch DBMS
vermieden werden
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
643/644
Umsetzung in SQL oder Programmiersprachen
Möglichkeiten zum Start einer Transaktion
SQL: START TRANSACTION
Impliziter Transaktionsbeginn: spezieller Modus in vielen
DBMS, der bei erstem Datenzugriff eine Transaktion
beginnt, wleche bis zu explizitem Beenden (s.u.) läuft
Transaktion pro Statement: spezieller Modus in vielen
DBMS, der für jedes Statement (Anfrage, Update, etc.) eine
einzelne Transaktion startet
Erfolgreiches Beenden einer Transaktion
SQL: COMMIT
Abbruch einer Transaktion (mit Rücksetzen aller bisherigen
Ergebnisse:
SQL: ROLLBACK
Programmierschnittstellen bieten oft eigene Schnittstellen
(Funktionen, Transaktionsklassen) zur Steuerung von
Transaktionen
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
644/644