Teil XI Datenbanken - Grundlagen der Informatik für Ingenieure

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
561/719
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
562/719
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
563/719
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
564/719
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
565/719
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
566/719
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
567/719
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
568/719
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
569/719
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
570/719
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
571/719
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
572/719
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
573/719
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
574/719
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
575/719
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
577/719
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
578/719
RDBMS Grundkonzepte: Tabellen
Student
Name
Vorname
Geburt
Müller
Schulze
Meier
Eva
Peter
Sebastian
5.9.1982
6.4.1987
13.4.1985
Schulze
Peter
1.7.1988
Schmidt
Lisa
8.1.1988
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
579/719
RDBMS Grundkonzepte: Schlüssel
Student
MatrNr
Name
Vorname
Geburt
174551
173212
167555
Müller
Schulze
Meier
Eva
Peter
Sebastian
5.9.1982
6.4.1987
13.4.1985
177351
Schulze
Peter
1.7.1988
177352
Schmidt
Lisa
8.1.1988
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
580/719
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
581/719
RDBMS Grundkonzepte: Fremdschlüssel N:1
Student
MatrNr Name
Vorname
Geburt
SGID
174551
Müller
Eva
5.9.1982
MB
173212
Schulze
Peter
6.4.1987
WMB
167555
Meier
Sebastian
13.4.1985
MB
…
…
…
…
…
Studiengang
Eike Schallehn, FIN/ITI
SGID
Bezeichnung
MB
Maschinenbau
WMB
Wirtschaftsingenieur Maschinenbau
…
…
Grundlagen der Informatik für Ingenieure
582/719
RDBMS Grundkonzepte: Fremdschlüssel N:M
Student
Vorlesung
MatrNr Name
…
VID
Bezeichnung
…
174551
Müller
…
GIF
Grundlagen der Informatik
…
173212
Schulze
…
KE
Konstruktionselemente
…
167555
Meier
…
TM
Technische Mechanik
…
…
…
…
…
…
…
Teilnahme
Eike Schallehn, FIN/ITI
MatrNr VID
Semester
174551
GIF
WiSe0809
174551
GIF
SoSe09
174551
TM
SoSe09
173212
KE
WiSe0708
…
…
…
Grundlagen der Informatik für Ingenieure
583/719
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
584/719
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
585/719
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
586/719
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
587/719
Ü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
588/719
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
589/719
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
590/719
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 vonGrundlagen
Indexen
über ALTER und DROP
der Informatik für Ingenieure
Eike Schallehn, FIN/ITI
591/719
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
592/719
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
593/719
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
594/719
SQL Anfragesprache: Projektion Beispiel
Student
MatrNr Name
Vorname
Geburt
SGID
174551
Müller
Eva
5.9.1982
MB
173212
Schulze
Peter
6.4.1987
WMB
MB
167555
Meier
Sebastian
13.4.1985
177351
Schulze
Peter
1.7.1988
PH
177352
Schmidt
Lisa
8.1.1988
WMB
SELECT name, vorname
FROM student;
Eike Schallehn, FIN/ITI
Name
Vorname
Müller
Eva
Schulze
Peter
Meier
Sebastian
Schulze
Peter
Schmidt
Lisa
Grundlagen der Informatik für Ingenieure
595/719
SQL Anfragesprache: Projektion mit
Duplikateliminierung
SELECT DISTINCT name, vorname
FROM student;
Name
Vorname
Müller
Eva
Schulze
Peter
Meier
Sebastian
Schmidt
Lisa
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
596/719
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
597/719
SQL Anfragesprache: Selektion Beispiel
SELECT *
FROM student
WHERE name = ‘Müller‘
OR name = ‘Schulze‘
MatrNr Name
Vorname
Geburt
SGID
174551
Müller
Eva
5.9.1982
MB
173212
Schulze
Peter
6.4.1987
WMB
177351
Schulze
Peter
1.7.1988
PH
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
598/719
SQL Anfragesprache: Selektionsprädikate
SELECT *
FROM student
WHERE name LIKE
‘S%‘;
MatrNr Name
Vorname
Geburt
SGID
WMB
173212
Schulze
Peter
6.4.1987
177351
Schulze
Peter
1.7.1988
PH
177352
Schmidt
Lisa
8.1.1988
WMB
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
599/719
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
600/719
SQL Anfragesprache: Verbund Eingabe
Student
MatrNr Name
Vorname
Geburt
SGID
174551
Müller
Eva
5.9.1982
MB
173212
Schulze
Peter
6.4.1987
WMB
167555
Meier
Sebastian
13.4.1985
MB
177351
Schulze
Peter
1.7.1988
PH
177352
Schmidt
Lisa
8.1.1988
WMB
Studiengang
SGID
Bezeichnung
MB
Maschinenbau
WMB
Wirtschaftsingenieur Maschinenbau
PH
Physik
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
601/719
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
Name
Vorname Studiengang
Müller
Eva
Maschinenbau
Schulze
Peter
Wirtschaftsingenieur Maschinenbau
Meier
Sebastian
Maschinenbau
Schulze
Peter
Physik
Schmidt
Lisa
Wirtschaftsingenieur Maschinenbau
Grundlagen der Informatik für Ingenieure
602/719
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
603/719
SQL Anfragesprache: Kartesisches Produkt
T1
A
B
1
2
3
4
SELECT *
FROM t1,t2;
T2
C
D
5
6
7
8
9
10
Eike Schallehn, FIN/ITI
A
B
C
D
1
2
5
6
1
2
7
8
1
2
9
10
3
4
5
6
3
4
7
8
3
4
9
10
Grundlagen der Informatik für Ingenieure
604/719
SQL Anfragesprache: Gruppierung
SELECT sgid, COUNT(*) AS anzahl
FROM student
GROUP BY sgid;
SGID
Anzahl
MB
2
WMB
2
PH
1
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
605/719
SQL Anfragesprache: Sortierung
SELECT *
FROM student
ORDER BY matrnr ASC;
MatrNr Name
Vorname
Geburt
167555
Meier
Sebastian
13.4.1985
SGID
MB
173212
Schulze
Peter
6.4.1987
WMB
MB
174551
Müller
Eva
5.9.1982
177351
Schulze
Peter
1.7.1988
PH
177352
Schmidt
Lisa
8.1.1988
WMB
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
606/719
SQL Anfragesprache: Mengenoperationen
SELECT *
FROM t1
UNION
SELECT *
FROM t2;
A
B
1
2
3
4
5
6
7
8
9
10
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
607/719
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
608/719
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
609/719
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
610/719
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
611/719
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
612/719
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
613/719
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
614/719
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
615/719
ER Modell: Einführendes Beispiel
Entity Student repräsentiert
alle Objekte vom Typ Student
Student
Relationship besucht repräsentiert
alle existierenden Beziehungen
zwischen Studentenobjekten
und Vorlesungsobjekten
besucht
MatrNr
Name
Vorlesung
ID
Semester
Bezeichnung
Vorname
Attribute der Entity Student
werden dieser zugeordnet,
Schlüsselattribute werden
unterstrichen
Eike Schallehn, FIN/ITI
Auch Relationships können
Attribute haben, der Schlüssel
ergibt sich aber immer aus den
Schlüsseln der beteiligten Entities
Grundlagen der Informatik für Ingenieure
616/719
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
617/719
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
618/719
ER Modell: Kardinalitäten 1:N
Dozent
[1,*]
hält
[1,1]
Vorlesung
ist äquivalent zu:
Dozent
1
hält
N
Vorlesung
1:N-Beziehung: ein Objekt darf mit beliebig vielen eines
anderen Typs in Beziehung stehen, aber eindeutige
Zuordnung in die andere Richtung
Alternative Min/Max-Notation
Angabe der minimimalen und maximalen Anzahl, in der das
Objekt in Beziehung stehen kann
Partizipationssemnatik: wie oft darf das Objekt an der
Beziehung teilnehmen („umgekehrt“ zu herkömmlichen
Kardinalitäten)
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
619/719
ER Modell: Kardinalitäten N:M
Student
Vorlesung
besucht
ist äquivalent zu:
Student
N
besucht
M
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
Eike Schallehn, FIN/ITI
Grundlagen der Informatik für Ingenieure
620/719
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
621/719
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
622/719
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
Preis
Eike Schallehn, FIN/ITI
1
Kunde
Bestellung
Anzahl
Rabat
Name
Datum
Anschrift
ArtikelNr
Bezeichnung
N
in
BestellNr
Grundlagen der Informatik für Ingenieure
von
KundenNr
623/719
Abbildung von ER-Diagrammen: Entities
Artikel
Artikel
ArtikelNr
Bezeichnung
Preis
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
624/719
Abbildung von ER-Diagrammen:
N:M-Beziehungen
Artikel
N
M
in
Bestellung
ArtikelNr
BestellNr
Anzahl
Bezeichnung
Rabat
Preis
Datum
Artikel
Bestellung
ArtikelNr
Bezeichnung
Preis
BestellNr
Rabat
Datum
…
…
…
…
…
…
…
…
ArtikelBestellung
Eike Schallehn, FIN/ITI
ArtikelNr
BestellNr Anzahl
…
…
…
Grundlagen der Informatik für Ingenieure
625/719
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
626/719
Abbildung von ER-Diagrammen:
1:N-Beziehungen
Bestellung
N
1
von
Kunde
KundenNr
BestellNr
Rabat
Name
Datum
Anschrift
Kunde
Bestellung
BestellNr
Rabat Datum
KundenNr
KundenNr
Name
Anschrift
…
…
…
…
…
…
…
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
627/719
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
628/719
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
629/719
Redundanz und Inkonsistenzen
Anschrift
Name
Vorname
PLZ
Stadt
Adresse
Müller
Eva
39104
Magdeburg
Leiterstraße 1
Schulze
Peter
39104
Zentrum
Ulrichplatz 17
Sommer
Siegfried
39218
Schönebeck
Am Anger 77
Sommer
Siegfried
39218
Schönebeck
Anger 77
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
630/719
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
631/719
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
632/719
1. Normalform: Problem
Musikgruppen
Band
Grü
Gründung
Genre
Radiohead
1986
Alternative Rock, Art-Rock, Britpop
Wilco
1994
Alternative Country, Independent
Pavement
1989
Independent, Noise Pop
…
…
…
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
633/719
1. Normalform: Lösung
Musikgruppen
GruppenGenre
Band
Grü
Gründung
Band
Genre
Radiohead
1986
Radiohead
Britpop
Wilco
1994
Radiohead
Art-Rock
Pavement
1989
Radiohead
Alternative Rock
…
…
Wilco
Independent
Wilco
Alternative Country
Pavement
Noise Pop
Pavement
Independent
…
…
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
634/719
2. Normalform: Problem
Wahlergebnis
Wahlbezirk Stadt
Bundesland
Beteiligung
Zentrum
Magdeburg
Sachsen-Anhalt
34%
Sudenburg
Magdeburg
Sachsen-Anhalt
35%
Stadtfeld
Magdeburg
Sachsen-Anhalt
42%
Groß Klein
Rostock
Mecklenburg- Vorpommern 29%
Zentrum
Rostock
Mecklenburg- Vorpommern 47%
…
…
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
635/719
2. Normalform: Lösung
Wahlergebnis
StadtBundesland
Stadt
Bundesland
Zentrum
Magdeburg 34%
Magdeburg
Sachsen-Anhalt
Sudenburg
Magdeburg 35%
Rostock
Mecklenburg- Vorpommern
Stadtfeld
Magdeburg 42%
…
Groß Klein
Rostock
29%
Zentrum
Rostock
47%
…
…
Wahlbezirk Stadt
Bet.
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
636/719
3. Normalform: Problem
Student
MatrNr
Name
Vorname
PLZ
Stadt
Adresse
154372
Müller
Eva
39104
Magdeburg
Leiterstraße 1
166733
Schulze
Peter
39104
Magdeburg
Ulrichplatz 17
168777
Sommer Siegfried
39218
Schönebeck
Am Anger 77
175483
Winter
39524
Wust
Dorfplatz 22
Robert
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
637/719
3. Normalform: Lösung
Student
MatrNr
Name
Vorname
PLZ
Adresse
154372
Müller
Eva
39104
Leiterstraße 1
166733
Schulze
Peter
39104
Ulrichplatz 17
168777
Sommer Siegfried
39218
Am Anger 77
175483
Winter
39524
Dorfplatz 22
Robert
PLZStadt
PLZ
Stadt
39104
Magdeburg
39218
Schönebeck
39524
Wust
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
638/719
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
639/719
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
640/719
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
641/719
Aufgaben von Programmierschnittstellen
Verbindung zum DBMS
Zugriff auf konkrete Datenbank
Absetzen von Anfragen
Anwendung
Schnittstelle
Treiber
DBMS
Server
Zugriff auf Ergebnisse
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
Client
Kapselung der Datenbankfunktionalität
durch geeignete Funktionen / Strukturen /
Klassen für
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
642/719
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
643/719
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
644/719
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
645/719
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
646/719
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
647/719
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
648/719
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
649/719
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
650/719
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
651/719
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
652/719
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
653/719
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
654/719
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
655/719
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
656/719
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
657/719
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
658/719
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
659/719
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
660/719