Datenbanksysteme Skriptum

Mag. Walter Petrisch
Datenbanksysteme
Einführung
Stand: 11.09.2005
Copyright © Walter Petrisch
Datenbanksysteme
1.
2.
3.
Historische Entwicklung....................................................................................... 5
Anwendungsgebiete von Datenbanken ............................................................... 7
Skalierung gebräuchlicher Datenbanken............................................................. 8
3.1. Low-End DBMS............................................................................................ 8
3.2. Standard-DBMS ........................................................................................... 8
3.3. High-End DBMS ........................................................................................... 8
3.4. Embedded / Mobile DBMS ........................................................................... 9
4. Datenbanken Grundbegriffe .............................................................................. 10
4.1. Definitionen ................................................................................................ 10
4.2. Das 3-Ebenen-Konzept .............................................................................. 10
5. Datenmodellierung, Normalisierung .................................................................. 12
5.1. DB-Konzepte und Anforderungen .............................................................. 12
5.2. Relationales Modell .................................................................................... 13
5.2.1. Terminologie ....................................................................................... 13
5.2.2. Eigenschaften des relationalen Datenbankmodells............................. 14
5.3. Normalisierung und Normalformen............................................................. 15
5.3.1. Basistypen im relationalen Modell....................................................... 15
5.3.1.1.
Entitäten....................................................................................... 15
5.3.1.2.
Beziehungen ................................................................................ 16
5.3.2. Kardinalitäten ...................................................................................... 16
5.3.3. Anomalien ........................................................................................... 17
5.3.3.1.
Einfüge-Anomalie......................................................................... 17
5.3.3.2.
Lösch-Anomalie ........................................................................... 17
5.3.3.3.
Änderungs-Anomalie ................................................................... 17
5.3.4. Abhängigkeiten ................................................................................... 17
5.3.4.1.
Funktionale Abhängigkeit............................................................. 17
5.3.4.2.
Voll funktionale Abhängigkeit ....................................................... 18
5.3.4.3.
Transitive Abhängigkeit................................................................ 18
5.3.5. Normalisierungsprozess...................................................................... 18
5.3.5.1.
Obsthändler Krämers erste Datenbank........................................ 18
5.3.5.2.
Die 1. Normalform........................................................................ 19
5.3.5.3.
Die 2. Normalform........................................................................ 19
5.3.5.4.
Die 3. Normalform (3NF).............................................................. 20
5.3.5.5.
Weitere Normalformen (BCNF, 4NF, 5NF) .................................. 21
5.3.5.6.
Weitergehende Normalformen ..................................................... 22
5.3.5.7.
Zusammenfassung Normalformen............................................... 22
6. Praxisteil: Postleitzahlen mit MS Access ........................................................... 24
7. Codd’sche Regeln ............................................................................................. 26
7.1. Einleitung und Hintergrund......................................................................... 26
7.2. Die 13 Regeln ............................................................................................ 26
7.2.1. Basisregel ........................................................................................... 26
7.2.2. Logische Speicherung......................................................................... 27
7.2.3. Eindeutigkeit........................................................................................ 27
7.2.4. Unterstützung von NULL-Werten ........................................................ 27
7.2.5. Einbindung eines Systemkataloges .................................................... 28
7.2.6. Unterstützung von Abfragesprachen................................................... 28
7.2.7. Aktualisierungsmöglichkeiten .............................................................. 28
7.2.8. Abfragen und Bearbeiten ganzer Tabellen.......................................... 28
7.2.9. Physische Datenunabhängigkeit ......................................................... 28
Seite 2/88
11.09.2005 10:18
Datenbanksysteme
7.2.10.
Logische Datenunabhängigkeit ....................................................... 29
7.2.11.
Unabhängigkeit der Integrität........................................................... 29
7.2.12.
Verteilungsunabhängigkeit .............................................................. 29
7.2.13.
Unterwanderungsverbot .................................................................. 29
7.3. Codd’sche Regeln - Zusammenfassung .................................................... 30
8. ER-Diagramme.................................................................................................. 31
8.1. Entitäten ..................................................................................................... 31
8.2. Beziehungen .............................................................................................. 31
8.3. Beziehungen mit Kardinalitäten.................................................................. 31
8.4. Syntax von ER-Diagrammen...................................................................... 32
8.5. Umwandlung von ER-Diagrammen in das relationale Schema.................. 32
9. SQL (Structured Query Language).................................................................... 34
9.1. Historische Entwicklung ............................................................................. 34
9.2. Elemente von SQL ..................................................................................... 34
9.2.1. DDL (Data Definition Language) ......................................................... 34
9.2.2. DML (Data Manipulation Language) ................................................... 34
9.2.3. Eigenschaften von SQL ...................................................................... 35
9.3. SQL-Anweisungen ..................................................................................... 35
9.3.1. Übungsdatenbank in Access............................................................... 35
9.3.2. SELECT .............................................................................................. 35
9.3.2.1.
Übungsumgebung........................................................................ 35
9.3.2.2.
Grundsätzlicher Aufbau der Select-Anweisung............................ 36
9.3.2.3.
Schreibweise von Attributen......................................................... 36
9.3.2.4.
Select mit Konstanten .................................................................. 36
9.3.2.5.
Rechnen mit Attributen ................................................................ 37
9.3.2.6.
Priorität von Rechenoperationen.................................................. 37
9.3.2.7.
Arithmetische Vergleichsoperatoren ............................................ 38
9.3.2.8.
Zeichenkettenvergleich mit LIKE.................................................. 38
9.3.2.9.
Behandlung von Nullwerten ......................................................... 39
9.3.2.10. Zeichenverkettung ....................................................................... 40
9.3.2.11. Logische Operatoren ................................................................... 40
9.3.2.12. DISTINCT-Klausel ....................................................................... 41
9.3.2.13. Mengenoperationen ..................................................................... 42
9.3.2.13.1. UNION ................................................................................... 42
9.3.2.13.2. INTERSECT .......................................................................... 43
9.3.2.13.3. MINUS ................................................................................... 43
9.3.2.14. ORDER BY-Klausel ..................................................................... 44
9.3.2.15. Funktionen ................................................................................... 44
9.3.2.15.1. Arithmetische Funktionen ...................................................... 45
9.3.2.15.2. Zeichenkettenfunktionen........................................................ 46
9.3.2.15.3. Datumsfunktionen .................................................................. 47
9.3.2.15.4. Aggregatfunktionen................................................................ 47
9.3.2.16. Die Klausel GROUP BY ............................................................... 47
9.3.2.16.1. Verschiedene Funktionen ...................................................... 54
9.3.2.17. JOIN - Abfragen über mehrere Tabellen ...................................... 54
9.3.2.17.1. Einleitung ............................................................................... 54
9.3.2.17.2. EQUI-JOIN............................................................................. 56
9.3.2.17.3. OUTER-JOIN......................................................................... 57
9.3.2.17.4. SELF-JOIN ............................................................................ 62
9.3.2.17.5. Joins - Zusammenfassung..................................................... 66
9.3.2.18. Unterabfragen (Subqueries)......................................................... 67
11.09.2005 10:18
Seite 3/88
Datenbanksysteme
9.3.2.18.1. Unterabfragen - Zusammenfassung ...................................... 72
9.3.2.19. Korrelierte (synchronisierte) Unterabfragen ................................. 72
9.3.2.20. EXISTS ........................................................................................ 73
9.3.2.21. ANY und SOME ........................................................................... 74
9.3.2.22. ALL .............................................................................................. 74
9.3.2.22.1. Vorstellung der Datenbankstruktur „EDV-Inventar“................ 74
10.
Weitere elementare Datenbankkonzepte....................................................... 78
10.1.
Transaktionen ......................................................................................... 78
10.1.1.
Sperrmechanismen ......................................................................... 80
10.1.2.
Isolation-Level ................................................................................. 80
10.1.3.
Deadlocks........................................................................................ 81
10.2.
Zugriffsrechte und Rollen........................................................................ 82
10.2.1.
Rollen und Gruppen ........................................................................ 82
10.3.
Datenbankprozeduren ............................................................................ 83
10.4.
Funktionen .............................................................................................. 83
10.5.
Trigger .................................................................................................... 84
10.5.1.
Anwendungsmöglichkeiten von Triggern ......................................... 85
10.6.
Das Cursorkonzept ................................................................................. 85
11.
Overheadfolien .............................................................................................. 87
12.
Referenzen .................................................................................................... 88
12.1.
Abbildungsverzeichnis ............................................................................ 88
Seite 4/88
11.09.2005 10:18
Datenbanksysteme
1.
Historische Entwicklung
1890 Hermann Hollerith, Erfinder der Lochkarten, Volkszählung in USA
1950 Batchverarbeitung (Stapelbetrieb), Eingabe/Verarbeitung/Ausgabe –
verbunden mit langem Warten auf Ergebnisse
1970 Erste interaktive Systeme; Terminalbetrieb (häufig Anbindung über
Telefonleitungen (300bd! – heute 100MBit)
1970 Entwicklung des Hierarchischen Modells (IBM IMS)
Es besteht eine Hierarchie zwischen den Datensätzen (Owner -> Member)
Member gehört immer zu einer Hierarchie
Zugriff: Navigation nur von Owner zu Member (Zeigerstrukturen)
Beispiel (Abteilung – Mitarbeiter – Geräte – Projekte):
Abbildung 1-1: Hierarchisches Datenbankmodell
1971 Netzwerkmodell (Codasyl, Siemens UDS)
Netzwerk von Datensätzen
Binäre 1:n-Beziehungen (Owner/member)
11.09.2005 10:18
Seite 5/88
Datenbanksysteme
Zugriff: Navigation von Owner zu Member, erstmals aber auch umgekehrt
von Member zu Owner
Abbildung 1-2: Netzwerkmodell
1972 Transaktionsorientierte Systeme
Relationenmodell, RDBMS („SQL-Datenbanken“)
Vertreter:
IBM mit DB/2, Ingres, Oracle, Informix, Sybase, Microsoft SQL Server
Häufig: Datenbanken wurden selbst programmiert (Cobol, C). Dafür wurden
indexsequentielle Zugriffsmechanismen verwendet. Umständlich bei
Änderungen in der Datenstruktur, Performanceoptimierung sehr schwierig. Zu
Gunsten besserer Performance wurden häufig bewusst konzeptionelle
Schwächen in Kauf genommen.
80er und 90er Jahre:
Wissensbanksysteme (zb. Rechtsinformationsystem, http://www.ris.bka.gv.at)
Objektrelationale Datenbanksysteme (ORDBMS) (Informix, Oracle)
Trends heute:
Unterstützung spezieller Anwendungen:
• Multimediadatenbanken: Verwaltung multimedialer Objekte (Bilder, Audio,
Video)
• XML-Datenbanken: Semistrukturierte Daten (XML-Dokumente)
• Verteilte Datenbanken: Verteilung auf verschiedene Rechnerknoten
• Mobile Datenbanken: Datenverwaltung auf Kleinstgeräten (PDA, Handy
etc...)
Seite 6/88
11.09.2005 10:18
Datenbanksysteme
2.
Anwendungsgebiete von Datenbanken
Beispielanwendung:
SAP R/3-lnstallation der Deutschen Telekom AG (1998)
• Financial Accounting: Rechnungen, Zahlungsaufforderungen,
Lastschriften, Mahnungen etc.
• 15 SAP R13-Systerne; jedes verarbeitet 200.000 Rechnungen, 12.000
Mahnungen, 10.000 Änderungen von Kundendaten pro Tag
• bis zu jeweils 1000 Nutzer gleichzeitig
• über 13.000 Datenbanktabellen
• Hardware: 51 Unix Enterprise Servern, 34 EMC-Speichersysteme (30 TB),
68 Magnetbandsysteme für Backup (Backup in 2h)
Weitere Anwendungen:
Sloan Digital Sky Survey 40 TB
- Himmelsdaten (Bilder und Objektinformationen); bis 2004
WalMart Data Warehouse 24 TB
- Produktinfos (Verkäufe etc.) von 2.900 Märkten, 50.000.Anfragen/Woche
lndexierbares WWW (1999) 6 TB
- ca. 800 Mill. Dokumente
Microsofts TerraServer
3,5 TB
- Landkarten, unkomprimierte Bilder/Karten (komprimiert; ca. 1 TB); 174 Mill.
Tupel
Gentechnologie, DNS-Erkundung
11.09.2005 10:18
Seite 7/88
Datenbanksysteme
3.
Skalierung gebräuchlicher Datenbanken
3.1.
•
•
•
•
•
•
•
•
Low-End DBMS
Extremfall: „Excel as a database“
Benutzer: 1-n , n klein
- Betrieb ggf. auf gleichem Rechner wie Anwendungen Speicherung nur mit
Mitteln des Betriebssystems
- Verzicht auf Logging, Recovery
- Grobgranulare (oder keine) Sperren
+ Minimaler Overhead
+ Usability: komfortable GUIs, Wizards
+ Komfortable Aufbereitungsfunktionalität („Reports“)
Einsatz:
Marktsegment „SOHO“ (Small Office, Home Office)
Kleinst-Anwendungen, „Quick & dirty“-Lösungen, Rapid Prototyping
3.2.
•
•
•
•
•
•
•
•
•
Standard-DBMS
Mehrbenutzerbetrieb
Client-Server-Organisation
+ Standardisierte Schnittstellen und Sprachen (SQL) z.T. jedoch mit
proprietären Erweiterungen
+ Eigenständige Organisation der Speicherbereiche: Betriebssystem reserviert
lediglich Plattenplatz (Dateien oder eigene Partitionen)
+ Mitlaufende Protokollierung
+ Sicherung von Daten- und Log-Dateien
+ Mittlere Sperrgranularität (Blockebene)
+ Eigene Benutzer- und Rechteverwaltung
+Trennung von Laufzeit-System und Entwicklungswerkzeugen
Einsatz:
Betriebliche (kommerzielle) Anwendungen i.w.S.
Vertreter:
MS Access, mySQL
3.3.
•
•
•
•
•
•
•
•
High-End DBMS
+ Ausgefeilte Optimierer, umfangreiche Tuning-Möglichkeiten
+ Parallelisierung von Zugriffen
+ Replikation und Verteilung:
+ Synchronisierte Verwaltung von Teilen oder Kopien derselben Datenbank
auf verschiedenen Rechnern
+ Sperrgranularität auf einzelnen Datensatz möglich
+ Dienste-Integration: Web Server, Application Server
+ Hohe Ausfallsicherheit
+ Unterbrechungsfreie Wartung
Seite 8/88
11.09.2005 10:18
Datenbanksysteme
•
- Bindung an High-End-Betriebssysteme, zertifizierte Hardware
Einsatz:
Unternehmenskritische Anwendungen („24*7“-Betrieb)
Große Benutzerzahlen und Datenvolumina
Vertreter:
DB/2, SQL-Server, Oracle
3.4.
•
•
•
•
Embedded / Mobile DBMS
DBMS für mobile Geräte (Laptop, PDA’s, Handheld, Mobiltelefon, ...)
+ DBMS-Kern mit Standard-Schnittstellen (SQL)
- Einschränkungen hinsichtlich Concurrency Control
+ Zusatzfunktionalität: regelmäßiger Abgleich des Datenbestands mit Server
Einsatz:
Caching von zentral verwalteten Daten im Mobilgerät, zb Kalenderfunktion am PDA
11.09.2005 10:18
Seite 9/88
Datenbanksysteme
4.
4.1.
Datenbanken Grundbegriffe
Definitionen
Eine Datenbank repräsentiert einen bestimmten Aspekt der realen Welt („miniworld“).
Sie enthält Daten für einen bestimmten Zweck für bestimmte Anwender und
Applikationen.
Relation: Benannte Tabelle
Relationenschema: „Tabellenkopf“ und Strukturdefinition
Attribut: Benannte Spalte mit zugeordnetem Datentyp
Tupel: Zeile bzw. Datensatz; (ein Wert je Attribut)
Schlüssel: Teilmenge der Attribute, anhand derer jedes Tupel eindeutig identifiziert
werden kann
Primärschlüssel: Schlüssel, nach dem die Relation sortiert gespeichert ist
Fremdschlüssel: (referentielle Integrität, sh. später)
Beispieltabelle:
PERSON
Name, SVNR (Primärschlüssel), GebDat, Nationalität (Fremdschlüssel), Geschlecht
Darstellung:
Name
SVNR
GebDat
Nationalität
Geschlecht
Abbildung 4-1: Beispieltabelle
4.2.
•
•
•
Das 3-Ebenen-Konzept
Interne Ebene
Reservierung von Plattenplatz, Aufbau von Datensätzen, Packen von
Datensätzen mit Spielraum für Ausdehnung (z.B. varchar-Felder)
Sekundärspeicherstrukturen (Seiten/Blöcke, Sätze, Zeiger, Indizes, Speicher,
Adresse, Sektor etc.)
Satz- und seitenorientierte Operationen, Abarbeitungsstrategien
Sperrverwaltung, Protokollierung, Pufferung von Seiten im Hauptspeicher,
Ersetzung von Seiten (paging), Zurückschreiben nach Änderungen
Bewahrung von Transaktionszuständen
Indexerstellung und –pflege: Indizes sind zusätzliche Datenstrukturen zum
schnellen Wiederauffinden von Datensätzen (kontrollierte Redundanz,
transparent!); Z.B.: B-Bäume, Hash-Tabellen
Konzeptuelle Ebene
Datenbankschema (Datenstrukturen, Constraints)
‚Architektur’ des jeweiligen DB-Systems (Relationen, Attribute)
Operationen für satz- und mengenorientierte Zugriffe
Externe Ebene (Benutzerpräsentation, Views, GUI’s)
An die Bedürfnisse und Rechte von Benutzern/Anwendungen angepasste
Seite 10/88
11.09.2005 10:18
Datenbanksysteme
Sichten auf das konzeptuelle Schema. Überwachung von Besitz- und
Zugriffsrechten
Abbildung 4-2: 3-Ebenen-Konzept
11.09.2005 10:18
Seite 11/88
Datenbanksysteme
5.
5.1.
Datenmodellierung, Normalisierung
DB-Konzepte und Anforderungen
Abbildung 5-1: Struktur eines Datenbanksystems
Datenbank-Managementsystems (DBMS) zur Gewährleistung dieser Anforderungen
o Eine Datenbank besteht also aus DBMS und Datenbestand.
Strukturierte Daten, kontrollierte Redundanz
o Redundanz ist nur dort vorhanden, wo sie unbedingt nötig ist (etwa zum
schnelleren Zugriff)
Datenintegrität
o Datenkonsistenz: Eingabekontrollen müssen verhindern, dass
widersprüchliche Information erfasst wird
o Einhaltung von Wertebereichen
o Vollständigkeit von Datensätzen
o Eindeutigkeit identifizierender Merkmale (i.a. Schlüssel)
Seite 12/88
11.09.2005 10:18
Datenbanksysteme
o Referentielle Integrität: Existenz von Datensätzen, auf die andere Datensätze
verweisen
Datensicherheit
o Maßnahmen (z.B. Backup-Strategien) gegen Verlust durch Systemfehler.
Problem: Sicherung im lfd. Betrieb
Datenschutz
o Maßnahmen gegen unberechtigten Zugriff, z.B. abgestufte Zugriffsrechte,
spezielle Datensichten (views).
Parallele Benutzbarkeit:
o Setzt voraus, dass alle Transaktionen atomar sind.
Eindeutige Primärschlüsselwerte
Keine Duplikate
Einhaltung der referentiellen Integrität:
o Attribute, die als Fremdschlüssel verwendet werden, dürfen nur Werte
annehmen, zu denen in der anderen Relation ein Tupel existiert.
Konsistenzbedingungen:
o Updates, inklusive Einfügen und Löschen von Tupeln müssen
konsistenzerhaltend durchgeführt werden. Um dies sicherzustellen, werden
diese mittels Transaktionen implementiert
o Eine Transaktion wird entweder ganz oder gar nicht ausgeführt.
Bsp.: Buchung auf 2 Konten: Überweisung von Konto A auf Konto B von
10000€
Konto A: Saldo - 10000 / anschl. Systemabsturz ??
o Eine abgebrochene Transaktion muss vollständig annullierbar sein.
o Parallele Transaktionen (durch verschiedene Benutzer) dürfen sich nicht
stören - häufig Sperren verwendet - Gefahr eines deadlock, -> sh.
Transaktionen
o Ineffiziente Implementierung von Transaktionen bewirkt lange Sperrzeiten.
5.2.
Relationales Modell
Entwickelt von Edgar CODD 1970.
5.2.1. Terminologie
o Relationale Datenbank: Eine aus verschiedenen Relationen (Bestands- und
ggf. Beziehungsrelationen) aufgebaute Datenbank – alle Daten befinden sich
in Tabellen
o Relation: eine Tabelle, in der in zweidimensionaler Anordnung die
Datenelemente erfaßt sind
o Bestandsrelation (Entitätsrelation): bildet eine Objektklasse mit
identischen Merkmalen (Feldern) ab
o Beziehungsrelation: schafft eine Beziehung zwischen zwei
verschiedenen Bestandsrelationen
11.09.2005 10:18
Seite 13/88
Datenbanksysteme
o Tupel: ein Datensatz bzw. eine einzelne Zeile in der Tabelle; enthält alle auf
ein Objekt bezogenen Feldwerte bzw. Merkmalsausprägungen
o Attribut: einzelne Spalte in der Tabelle (Feld von einem bestimmten
Datenfeldtyp)
o Domäne: Menge der verschiedenen Feldwerte eines Attributs
o Schlüsselfeld: dient der eindeutigen Identifikation eines Tupels in einer
Relation und der Herstellung von Beziehungen zwischen verschiedenen
Relationen
o Abfrageergebnisse sind ebenfalls Tabellen (Transformationen von Tabellen)
o Selektion: Auswahl bestimmter Zeilen aus einer Tabelle
o Projektion: Auswahl bestimmter Spalten (Attribute) aus einer Tabelle
5.2.2. Eigenschaften des relationalen
Datenbankmodells
o
o
o
o
Feste Anzahl von Spalten, beliebige Anzahl von Zeilen
Reihenfolge der Zeilen und Spalten spielt keine Rolle
Die Spalten enthalten die Attribute des Datensatzes
Es kann keine zwei identischen Zeilen geben
Abbildung 5-2: Relationendarstellung allgemein
Seite 14/88
11.09.2005 10:18
Datenbanksysteme
Abbildung 5-3: Relationendarstellung am Beispiel eines Buchverleihs
Die Integritätsbedingungen lauten hier:
INV.NR ist Primärschlüssel für BUCH
INV.NR darf nicht doppelt vorkommen
INV.NR in AUSLEIH ist Fremdschlüssel bezüglich BUCH
INV.NR taucht in einem anderen Relationenschema als Schlüssel auf
5.3.
Normalisierung und Normalformen
Bei komplexen Systemen mit vielen Objekttypen und Beziehungen ist die
Modellierung der Datenstruktur nicht unproblematisch. Hierzu wurden Regeln
aufgestellt, nach denen man die Einträge in den Tabellen ordnet und aufteilt. Die
wichtigsten Ziele der Normalisierung einer oder mehreren Tabellen sind:
•
•
•
Vermeidung von Redundanzen (doppelt gespeicherten Werten)
Vereinfachung des Aufbaues von Tabellen
Einfache Datenpflege
5.3.1. Basistypen im relationalen Modell
5.3.1.1.
Entitäten
Eine Entität ist ein individuelles und identifizierbares Exemplar von Dingen, Personen
oder Begriffen der realen oder der Vorstellungswelt.
Beispiele:
Individuum (Person, Mitarbeiter, Einwohner, Schüler)
Reales Objekt (Maschine, Gebäude, Produkt, Artikel)
Abstraktes Konzept (Fachgebiet, Vorlesung)
Ereignis (Kursanmeldung, Bestellung, Lieferung einer Ware)
Eine Entität ist immer eine eindeutig identifizierbare Einheit.
11.09.2005 10:18
Seite 15/88
Datenbanksysteme
5.3.1.2.
Beziehungen
Eine Beziehung assoziiert wechselseitig zwei (möglicherweise aber auch mehr als
zwei) Entitäten.
Beispiele:
BETREUT (Arzt, Patient)
PRODUZIERT (Mitarbeiter, Werkstück)
PRÜFT (Professor, Student):
5.3.2. Kardinalitäten
a) 1:1 (Ein Student ist verheiratet mit einer Studentin)
b) 1:n bzw. n:1 (Mehrere Schüler gehören zu einer Klasse)
c) n:m (Mehrere Professoren prüfen mehrere Studenten)
Abbildung 5-4: Kardinalitäten
Seite 16/88
11.09.2005 10:18
Datenbanksysteme
5.3.3. Anomalien
5.3.3.1.
Einfüge-Anomalie
Ein Datensatz kann in einer Relation nur dann eingefügt werden, wenn auch
bestimmte Merkmale, welche in einer anderen Relation gespeichert sind, bekannt
sind.
Beispiel: Ein Kunde kann nur dann eingetragen werden, wenn er auch eine Ware
kauft.
5.3.3.2.
Lösch-Anomalie
Beim Löschen eines Tupels aus einer Relation werden Informationen, welche in
einer anderen Relation gespeichert sind, unbrauchbar oder ebenfalls gelöscht.
Beispiel: Wird die einzige Lieferung an einen Kunden gelöscht, weil diese zb nicht
angekommen ist, sind auch die Informationen über Wohnort, Anschrift des
Kunden verloren.
5.3.3.3.
Änderungs-Anomalie
Bei der Änderung eines Attributs in einer Relation sind mehrere Änderungen in
der Datenbank vorzunehmen.
Beispiel: Falls ein Kunde umzieht, so sind mehrere Tupel eventuell nachträglich
zu ändern, da ansonsten die Rechnungen sowohl an die alte sowie auch an die
neue Anschrift adressiert werden.
5.3.4. Abhängigkeiten
5.3.4.1.
Funktionale Abhängigkeit
Definition:
In einer Relation R(A,B) ist das Attribut B von dem Attribut A funktional abhängig,
falls zu jedem Wert des Attributs A genau ein Wert des Attributs B gehört.
Beispiele:
ISBN Titel (sprich: ‚ISBN bedingt Titel’)
weil: bei einer bestimmten ISBN ist Titel IMMER derselbe.
ISBN Verlag
weil: bei einer bestimmten ISBN ist Verlag IMMER derselbe.
NICHT funktional abhängig sind:
ISBN Autor
ISBN Stichwort
(es kann MEHRERE Autoren bei einem Buch geben, ein Buch hat mehrere
Stichwörter!)
Beispiel:
Ein Angestellter arbeitet in EINER Abteilung, eine Abteilung ist an EINEM Ort
(AngestNr, AbtNr, Ort)
AngestNr AbtNr
AbtNr Ort
11.09.2005 10:18
Seite 17/88
Datenbanksysteme
5.3.4.2.
Voll funktionale Abhängigkeit
Definition:
In einer Relation R(S1/S2,A) ist das Attribut A von den Attributen (Schlüsseln) S1,S2
voll funktional abhängig, wenn A von den zusammengesetzten Attributen (S1,S2)
funktional abhängig ist, nicht aber von einem einzelnen Attribut S1 oder S2.
Anders:
S1/S2 A
S1 nicht A
S2 nicht A
Beispiel:
Ein bestimmter Mitarbeiter braucht Zeit zur Herstellung eines bestimmten Produkts:
DAUER (MITARBEITER_ID, PROD_ID, ZEIT)
ZEIT ist nicht abhängig von MITARBEITER_ID („Ein Mitarbeiter braucht Zeit...“)
ZEIT ist nicht abhängig von PROD_ID („Ein Produkt benötigt Zeit...“)
5.3.4.3.
Transitive Abhängigkeit
Definition:
In einer Relation R(S,A,B) ist das Attribut B vom Attribut S transitiv abhängig, wenn A
von S funktional abhängig ist, und B von A funktional abhängig ist.
Anders:
S A B Daher: B ist von S transitiv abhängig
Beispiel: Relation (Person#, AbtNr#, AbtName)
Person AbtNr
AbtNr# AbtName
Daher: AbtName ist von Person# transitiv abhängig.
5.3.5. Normalisierungsprozess
5.3.5.1.
Obsthändler Krämers erste Datenbank
Wie eine völlig unerfahrene Person eine Datenbank planen würde... Obsthändler
Krämer hat folgenden Datenbankentwurf erstellt:
Auftragsnr.
1
1
2
2
2
3
3
3
4
4
Datum
1.1.99
1.1.99
1.1.99
1.1.99
1.1.99
2.2.99
2.2.99
2.2.99
2.2.99
2.2.99
Kunde
1 Schmitt, Bonn
1 Schmitt, Bonn
2 Müller, Köln
2 Müller, Köln
2 Müller, Köln
1 Schmitz, Bonn
1 Schmitz, Bonn
1 Schmitz, Bonn
45 Lehmann, Jülich
45 Lehmenn, Jülich
Artikelnr.
134
135
140
160
160
103
134
135
30
27
Bezeichnung
Coxorange
Kiwi
Butterbirne
Kürbis, rot
Kürbis, gelb
Johannisbeeren
Coxorange
Kiwi
Bananen
Ananas
Menge
4 Kisten
4 Kisten
2 Kisten
2 Stück
10 Stück
5 Kilo
12 Kisten
2 Kisten
12 Kilo
60 Stück
Abbildung 5-5: Unnormalisierte Datenbankrelation
Seite 18/88
11.09.2005 10:18
Datenbanksysteme
Herr Krämer hat die Datenbank nach seiner Intuition mit ACCESS erstellt. Wie man
sehen kann, hat er die Daten so eingegeben, wie er es seit vielen Jahren auf dem
Papier auch tut. Im Prinzip tut es dieser Datenbankentwurf auch, nur leider gibt es
hier viele Probleme:
1. Viele gleiche Einträge (Redundanz) sorgen nach ein paar Monaten dafür, dass
die Datenbank unnötig groß wird.
2. Tippfehler (letzte Zeile: Lehmenn) machen das Auffinden aller Lieferungen an
Lehmann, Jülich unmöglich.
3. In der Spalte Kunde sind sowohl Kundennummer, Name und Ort zugleich
eingetragen. Herr Krämer muss sich stets selber die Kundennummer merken und
alle Daten bei jedem neuen Auftrag stets neu eintippen.
4. Artikelnummer und Bezeichnung könnten als Synonyme verwendet werden, sind
aber hier jeweils für sich in getrennten Spalten gespeichert.
5. Die Farbe des Kürbis gibt eine andere Art an, die eine neue Artikelnummer
erfordert.
6. Die Spalte Menge enthält sowohl die Stückzahl als auch die Einheit der Ware
(Kiste, Stück, Kilo). Die Einheit ist aber stets an die Art der Ware gekoppelt.
Schwächen des DB-Entwurfs sind an den Anomalien (sh. oben) erkennbar:
Hier noch einmal der ursprüngliche Datenbankentwurf:
Auftragsnr. Datum
1
1.1.99
....
4
2.2.99
Kunde
Artikelnr. Bezeichnung
1 Schmitt, Bonn 134
Coxorange
Menge
4 Kisten
45 Lehmenn, Jül. 27
60 Stück
Ananas
Abbildung 5-6: Auszug unnormalisierte Datenbankrelation
5.3.5.2.
Die 1. Normalform
Umwandlung in die erste Normalform:
1NF:
AuftrNr. Datum
KundenNr.
Name
Ort
ArtNr.
Bez.
Menge
Es wurde in der Spalte Kunde die Abhängigkeit von Kundennummer, Name und
Ort aufgelöst. Dafür sind weitere Spalten entstanden.
Nach der Trennung entspricht der Entwurf der Forderung nach atomaren Werten
(von atomos = unteilbar).
Definition der 1. Normalform (1NF):
• Jedes Attribut ist eine atomare (nicht weiter zerlegbare) Dateneinheit.
• Jedes Attribut darf in einer Relation nur einmal vorkommen
• Es gibt einen Primärschlüssel (daher kann jeder Satz in einer Tabelle/Relation
nur einmal vorkommen). Es darf keine Wiederholungsgruppen geben.
5.3.5.3.
Die 2. Normalform
Nochmals die 1NF:
11.09.2005 10:18
Seite 19/88
Datenbanksysteme
AuftrNr. Datum KundenNr.
Name
Ort
ArtNr. Bez.
Menge
Umwandlung in die 2. Normalform:
AUFTR-KUNDE:
AuftrNr#
Datum
KundenNr.
Name
AUFTR-ART:
AuftrNr.
ArtNr.
Bez.
Menge
•
•
Ort
Was geschah? Aufteilung der Datenbank in 2 Teile.
Warum? Zum einen gehören Kundennummer, Name, Ort (und Anschrift)
einerseits und Artikelnummer, Bezeichnung und Menge andererseits jeweils
zusammen.
Primärschlüssel: Die Auftragsnummer in der 1. Tabelle
Fremdschlüssel: Auftragsnummer in der 2. Tabelle
Wir müssen diese beiden Tabellen durch eine Relation miteinander verknüpft
betrachten: Kunde x (kauft) Ware y. Diese Verknüpfung erfolgt über die
Auftragsnummer. Sie ist das Bindeglied zwischen den Tabellen und in beiden
Relationen enthalten. Sonst kann man die Waren nicht mehr dem Käufer zuordnen.
Die Forderung, dass alle Attribute von dem Schlüssel voll funktional abhängig sein
sollten, konnte nur dadurch erfüllt werden, dass die Tabelle in zwei Tabellen
aufgeteilt wurde, bei denen die Attribute von ihrem Schlüssel funktional voll abhängig
sind.
Definition der 2. Normalform (2NF):
Eine Relation befindet sich in 2. Normalform, wenn sie sich in erster Normalform
befindet und jedes Attribut, das nicht zum Primärschlüssel gehört, VOLL funktional
abhängig vom Primärschlüssel ist.
Zusammenfassung:
Die 2NF regelt die Beziehungen zwischen Schlüssel und Attributen einer Tabelle.
5.3.5.4.
Die 3. Normalform (3NF)
Nochmals die 2NF:
AUFTR-KUNDE:
AuftrNr.
Datum KundenNr.
AUFTR-ART:
AuftrNr.
ArtNr. Bez.
Name
Ort
Menge
Es gibt transitive Abhängigkeiten:
AuftrNr KundenNr Name
AuftrNr ArtNr Bez.
Seite 20/88
11.09.2005 10:18
Datenbanksysteme
Aufgabe der 3. Normalform ist die Entfernung von transitiven Abhängigkeiten.
Die Transitivität wird gebrochen, d.h. das transitiv abhängige Attribut ist in ein neues
Relationenschema zu übertragen:
ARTIKEL:
ArtNr.
Bez.
AUFTR-ART:
AuftrNr.
Menge
ArtNr. (FS)
AUFTR-KUNDE:
AuftrNr.
KundenNr. (FS)
Datum
KUNDE:
KundenNr.
Name
Ort
(Anschrift...)
Es werden nur die wirklich zusammengehörenden Attribute, wie Kundennummer,
Name, Ort, (Anschrift) in einer Tabelle gespeichert, und eines dieser Attribute wird
als Schlüsselattribut definiert.
Merke: Damit alle Attribute in 4 Tabellen miteinander abfragbar werden, müssen
zumindest 2 Tabellen jeweils über einen Primärschlüssel und einen Fremdschlüssel
verfügen, während die restlichen beiden nur über einen Primärschlüssel verfügen
müssen.
Definition der 3. Normalform (3NF):
Die 3. Normalform ist erfüllt, wenn die 2. Normalform erfüllt ist und die NichtSchlüssel-Attribute funktional unabhängig voneinander sind.
Merksatz:
Your attributes shall depend on the key,
the whole key and nothing but the key,
so help me Codd!”
Zusammenfassung:
Die 3NF regelt die Beziehungen von Attributen einer Relation untereinander.
5.3.5.5.
Weitere Normalformen (BCNF, 4NF, 5NF)
Die meisten Relationen, die sich in der zweiten und dritten Normalform befinden,
sind meistens auch schon in der Boyce-Codd-Normalform (BCNF). Eine
Ausnahme tritt nur dann ein, wenn:
•
•
•
der Primärschlüssel eine Zusammensetzung aus mehreren
Schlüsselattributen ist
es mehr als einen Schlüssel gibt (d.h. sowohl Primär- als auch
Fremdschlüssel)
die Schlüssel nicht disjunkt zueinander sind, d.h. mehrere Attributwerte der
Schlüssel den selben Wert haben.
Besonders die letzte Bedingung sagt aus, dass BCNF nur sehr selten vorkommt.
Z.B.: Relation (A#, B#, C, D)
B# ist außerdem Fremdschlüssel
11.09.2005 10:18
Seite 21/88
Datenbanksysteme
Auch hier werden Attribute extrahiert, die funktional abhängig sind.
Es ist jedoch zu beachten, dass solche Dekompositionen in manchen Fällen zu
erheblichen Schwierigkeiten führen können, die einen Verzicht als die bessere
Wahl erscheinen lassen.
Definition der BCNF:
Eine Relation ist dann in BCNF, wenn kein Attribut funktional abhängig von einer
Attributgruppe ohne Schlüsseleigenschaft ist.
5.3.5.6.
Weitergehende Normalformen
Über die dargestellten Normalformen hinaus, die sich mit der Eliminierung
funktionaler Abhängigkeiten befassen, existieren weitergehende Normalformen
(vierte und fünfte Normalform) zum Ausschluss mehrwertiger Abhängigkeiten und
sogenannter Join Dependencies, die aber in der Praxis ähnlich wie bereits die
BCNF kaum eine Rolle spielen.
Beispiel für die fünfte Normalform... (Quelle: C.J. Date/ Database Systems):
Abbildung 5-7: 5. Normalform
5.3.5.7.
Zusammenfassung Normalformen
Erste Normalform (1NF):
• Atomare Attribute (keine Wiederholungsgruppen)
Zweite Normalform (2NF):
• Attribute voll funktional abhängig vom Primärschlüssel
Dritte Normalform (3NF):
• Abhängigkeit der Nichtschlüsselattribute untereinander
Seite 22/88
11.09.2005 10:18
Datenbanksysteme
Boyce-Codd-Normalform (BCNF):
• Abhängigkeiten vom Schlüssel ausgehend
11.09.2005 10:18
Seite 23/88
Datenbanksysteme
6.
Praxisteil: Postleitzahlen mit MS Access
Ziel der Übungsaufgabe: Erstellen einer Kundentabelle; Bei Eingabe in das Feld PLZ
(Postleitzahl) in dieser Tabelle soll automatisch der dazu passende Ort erscheinen.
1) Benötigt werden daher 2 Basistabellen:
•
•
T_Name
T_Ort
NAME, PLZ
PLZ, ORT
2) Beziehungen zwischen den Tabellen anlegen:
Abbildung 6-1: Symbol für Beziehungsfenster
3) Beide Tabellen ins Beziehungsfenster einbinden und Verknüpfung zwischen den
PLZ-Attributen herstellen: Von Orte (das ist die 1:-Relation!) nach Name (das ist die
n-Relation) ziehen:
Abbildung 6-2: Beziehung zwischen zwei Tabellen herstellen
4) Testdaten in Tabelle Orte eingeben
5) Ein Formular (mit Formularassistent) anlegen. Die erforderlichen Felder sind:
Name, PLZ, Ort
Seite 24/88
11.09.2005 10:18
Datenbanksysteme
Abbildung 6-3: Eingabeformular für Namen
6) Datensätze in Formular eingeben bei gültiger PLZ erscheint entsprechender Ort
automatisch
7) Feld Ort auf Eingabe sperren
Das Feld ‚Ort’ wird direkt über die Eingabe der PLZ angesprochen und ausgegeben,
und sollte daher für allfällige Eingaben gesperrt werden:
Abbildung 6-4: Eigenschaftenfenster des Feldes „Ort“ im Formular
11.09.2005 10:18
Seite 25/88
Datenbanksysteme
7.
7.1.
Codd’sche Regeln
Einleitung und Hintergrund
E.F. Codd und seine Mitarbeiter benötigten von ihren ersten theoretischen Ansätzen
bis zur Formulierung der inzwischen berühmt gewordenen zwölf Regeln mehr als 15
Jahre. Rechnet man als Geburtsstunde des PC den ersten Rechner von IBM aus
dem Jahre 1982, so ist die Geschichte der Theorie wie auch der Praxis relationaler
Datenbanken deutlich länger. Im Oktober 1985 veröffentlichte E.F. Codd zum
ersten Mal in der "Computerworld" seine Regeln, die sein Mitarbeiter C.J. Date knapp
ein Jahr später um weitere zwölf Regeln für virtuelle RDBMS erweiterte.
Die seither entstandenen DBMS nähern sich dem relationalen Modell jedoch nur
sehr langsam. So gibt es heute noch kaum einen Hersteller, der das mit SQL92 per
Standard eingeführte Domänenpronzip umsetzt. Außerdem bieten nahezu alle
Hersteller als Abfragesprache SQL mit zahlreichen Abweichungen untereinander. Es
wird fast immer nur ein Teil der Relationalen Algebra unterstützt.
Dazu kommt noch, dass neben Datenbanken, die man als relational bezeichnen
kann, noch Systeme existieren, die ursprünglich eindeutig nicht-relational waren
und wo die Hersteller versuchten und versuchen, diese Schritt für Schritt zu "drehen".
Manche bezeichnen ihre Systeme schon als relational, weil sie ihnen eine SQLSchnittstelle verpassten. Andere Systeme hießen relational, weil sie die von Codd
als relational bezeichneten Tabellenstrukturen nachempfanden. Die im PC-Bereich
bisher weit verbreiteten x-Base-Systeme sind der beste Beweis für solche
Datenbanken. Dabei ist die Tabellenstruktur fast das einzige relationale Merkmal von
x-Base-Systemen wie FoxPro, dBase oder Clipper.
Um sich hier richtig zu orientieren und einschätzen zu können, in welchem Grad ein
DBMS relational ist oder nicht, sind die zwölf Regeln ein sehr nützliches und auch
einfaches Hilfsmittel. Deutlich aufwendiger wäre es, zu unterscheiden, inwieweit die
einzelnen DBMS das vollständige relationale Modell unterstützen.
Streng genommen sind es sogar dreizehn Regeln, doch E.F. Codd trennte die erste
ab, weil diese fundamental ist und alle anderen auf dieser "Regel" aufbauen.
7.2.
Die 13 Regeln
7.2.1. Basisregel
Jedes relationale Datenbanksystem muss in der Lage sein, die gesamte Datenbank
mit seinen relationalen Fähigkeiten, wie dies im relationalen Modell spezifiziert ist,
selbst zu verwalten. Dies muss auch dann möglich sein, wenn das DBMS zusätzlich
nichtrelationale Fähigkeiten unterstützt. Außerdem muss die Sprache, mit der auf die
Daten zugegriffen wird, auf relationalem Niveau sein, d.h. jedes Select, jedes
Commit, Update oder Delete (eben SQL als Zugriffssprache) muss den Zugriff auf
mehrere Datensätze (Tupel) gleichzeitig unterstützen. Die Auswahl eines oder keines
Datensatzes ist in diesem Zusammenhang als Spezialfall der Umschreibung
"mehrerer Datensätze" zu betrachten.
Seite 26/88
11.09.2005 10:18
Datenbanksysteme
7.2.2. Logische Speicherung
In einer relationalen Datenbank werden alle Informationen ausschließlich auf einer
logischen Ebene und nur auf genau eine Art und Weise durch Werte in Relationen
(Tabellen) dargestellt. Damit spielt es keine Rolle, wie das DBMS die Daten
physikalisch auf Server und Festplatten verteilt. Es wird ausdrücklich untersagt, auf
die logischen, dem Benutzer zugänglichen Ebene Mechanismen wie Pointer,
hardwarenahe Adressen, Sektornummern u.ä. zu benutzen.
Neben den Anwenderdaten, den eigentlichen Nutzinformationen, müssen auch die
Namen der Relationen, Spalten und Domänen einer Datenbank in Form von
Zeichenketten in Relationen dargestellt werden. Soche Relationen sind
normalerweise im sogenannten Systemkatalog abgelegt. Der Systemkatalog wird
somit praktisch ein Teil der Datenbank, er ist dynamisch veränderbar und während
jeder Datenbanksitzung aktiv.
Diese erste Regel wird oft auch al Informationsregel bezeichnet, denn mit ihr ist die
Datenbank-Administration (DBA) in der Lage, die Integrität der Datenbank zu
erhalten, ja sie vereinfacht seine Arbeit erheblich. Mit Hilfe des Systemkatalogs ist es
jederzeit möglich, Zustände der Datenbank abzufragen, Aussagen über den Umfang
der Tabellen, deren Indizierung und Struktur zu treffen.
7.2.3. Eindeutigkeit
Jedes einzelne Element in einer relationalen Datenbank ist immer durch eine
logische Kombination aus dem Namen der Relation, einem Primärschlüsselwert und
dem Spaltennamen erreichbar.
Der Zugriffsmechanismus findet auf der logischen Ebene statt, physikalische
Adressierungen sind nicht erlaubt. Fundamentale Bedeutung hat auch die
entsprechende Umsetzung des relationalen Modells, nach der jede Relation einen
eindeutigen Primärschlüssel ohne NULL-Werte besitzen muss.
7.2.4. Unterstützung von NULL-Werten
Das relationale System muss unabhängig vom Datentyp Indikatoren unterstützen,
die auf der logischen Ebene fehlende Informationen ersetzen. Sie müssen sich
außerdem von solchen Konstrukten wie leere Zeichenketten für Stringfelder, der
Ziffer Null für numerische Felder oder des Datums 01.01.0000 unterscheiden.
In der Praxis wird dieser Forderung durch die Unterstützung des sogenannten NULLWertes entsprochen. Die ursprünglich und in zahlreichen PC-Datenbanksystemen
heute noch üblichen speziellen Werte sind im relationalen Modell ungeeignet, da der
Benutzer für jede Spalte oder Domäne eine dem Datentyp angepasste Technik
anwenden müsste.
Beispiel:
In einem Formular wird ein Antragsteller nach der Zugehörigkeit zu einer bestimmten
Versicherungsorganisation gefragt. Falls er dort mit "ja" quittiert, muss er in einem
weiteren Feld das Beitrittsdatum angeben. Doch was macht die Erfassungsroutine,
falls der Antragsteller mit "nein" quittiert und es demzufolge kein Beitrittsdatum gibt ?
11.09.2005 10:18
Seite 27/88
Datenbanksysteme
Wird an dieser Stelle der eben erwähnte spezielle Wert 01.01.0000 (der übrigens
korrekt ist) gespeichert, müssen alle Operationen der Datenbank, die diese Spalte
benutzen, diesen Wert speziell abarbeiten. Dies kann bei großen oder verteilten
Systemen, bei zahlreichen Anwendern und Anwendungsprogrammen schwierig sein;
deshalb wird der NULL-Wert gespeichert.
Um die Integrität der Datenbank zu gewährleisten, muss es allerdings möglich sein,
Felder zwingend als "NOT NULL" zu definieren. Primärschlüsselfelder müssen z.B.
immer einen Wert enthalten.
7.2.5. Einbindung eines Systemkataloges
Die Beschreibung der Datenbank (der sogenannte Systemkatalog) erfolgt auf einer
logischen Ebene, und zwar in genau der gleichen Art und Weise wie die Darstellung
der Nutzdaten. Es muss daher für autorisierte Benutzer möglich sein, mit der
gleichen Zugriffssprache auf diese Systemdaten zuzugreifen wie auf die normalen
Daten.
Dies ist ein Feature, das nicht-relationale Datenbanken normalerweise nicht bieten.
Jeder Anwender, egal ob End-User, Programmierer oder Administrator, muss nur
eine Sprache lernen. Autorisierte Benutzer können den Katalog leicht erweitern.
7.2.6. Unterstützung von Abfragesprachen
Um auf die gespeicherten Datenbestände zugreifen zu können, muss das DBMS
mindestens eine Abfragesprache unterstützen, um die Daten geeignet ansprechen
zu können.
Die Sprache muss umfassend sein und Kommandos zur Daten- und Viewdefinition,
zur Manipulation der Daten, zur Autorisierung des Zugriffs, zur Sicherung der
Integrität und zum Verpacken in Pakete (Transaktionen) enthalten.
7.2.7. Aktualisierungsmöglichkeiten
Das relationale DBMS enthält einen Algorithmus, mit dem zum Definitionszeitpunkt
einer View festgelegt werden kann, ob in dieser View Datensätze eingefügt oder
gelöscht und welche Spalten verändert werden dürfen. Die Definition dieser
Eigenschaft muss im Systemkatalog gespeichert werden.
7.2.8. Abfragen und Bearbeiten ganzer Tabellen
Eine Basis- oder Ergebnistabelle kann man in einem relationalen DBMS wie einen
Operanden benutzen. Daher besteht die Möglichkeit, ganze Tabellen einzufügen, zu
ändern oder zu löschen.
Ziel dieser Regel ist es, dem System einen größeren Spielraum bei der Optimierung
seiner Laufzeitaktionen zu geben.
7.2.9. Physische Datenunabhängigkeit
Anwendungsprogramme und deren Oberfläche bleiben für den Benutzer logisch
unverändert, auch wenn Veränderungen an der Speicherstruktur oder der
Zugriffsmethode vorgenommen werden.
Das bedeutet nichts anderes, als dass das relationale DBMS die hardwarenahen
Momente (wie Speicherausstattung, CPU, schnelles Netzwerk usw.) ganz klar vom
logischen Aufbau der Datenbank abkoppelt. Dies hat zur Folge, dass beispielsweise
Seite 28/88
11.09.2005 10:18
Datenbanksysteme
ein Tuning der Datenbank vorgenommen werden kann oder die Datenbank auf ein
größeres leistungsfähigeres System portiert wird, ohne dass ein
Anwendungsprogramm verändert werden muss. Damit ist auch eine strikte Trennung
der Aufgaben des Server-Systems von denen des Client-Systems möglich.
Die Benutzung und Verwaltung der Indizes ist Sache des DBMS.
7.2.10. Logische Datenunabhängigkeit
Manche Änderungen an der Struktur der Datenbank können vor den
Anwendungsprogrammen verborgen werden, indem einfach eine View definiert wird,
die der alten Struktur entspricht.
Anwendungsprogramme bleiben logisch unbeeinträchtigt von Veränderungen an den
Basisrelationen, wenn es theoretisch möglich ist, diese Unabhängigkeit zu
gewährleisten.
So kann es beispielsweise notwendig werden, dass eine sehr große Tabelle
physisch auf zwei oder gar mehr Festplatten verteilt werden muss. Das kann
entweder zeilenweise über den Zeileninhalt oder spaltenweise über den
Spaltennamen vorgenommen werden. Damit bleiben die Primärschlüssel in beiden
Teilrelationen erhalten.
Auch das Gegenteil, die Zusammenführung zweier Relationen in eine Tabelle ist
denkbar. Dieser Vorgang wird auch „verlustfreier Join“ genannt.
Mit der 9. Regel kann das logische Datenbankdesign regelmäßig verändert werden,
um etwa das Performance-Verhalten zu verbessern, ohne dass dies Auswirkungen
auf ein Anwenderprogramm hat.
7.2.11. Unabhängigkeit der Integrität
Alle Integritätsbedingungen, die spezifisch für eine Datenbank gelten, müssen mit
Hilfe der relationalen Datenbankbeschreibungssprache definierbar sein. Außerdem
müssen sie im Systemkatalog (im sog. Data Dictionary) abgelegt werden. Ihre
Definition in den Anwendungsprogrammen ist unzulässig.
Damit ist eine Verwaltung der Integritätsregeln an zentraler Stelle möglich.
7.2.12. Verteilungsunabhängigkeit
Ein relationales Datenbank-Managementsystem mit einer Zugriffssprache bleibt
logisch unbeeinträchtigt, wenn entweder die verteilte Datenhaltung eingeführt oder
umgekehrt verteilte Daten auf einem System zusammengeführt werden.
7.2.13. Unterwanderungsverbot
Es darf keine Möglichkeiten geben, die Regeln für relationale System zu
unterwandern.
Falls also ein relationales System über eine sogenannte Low-Level-Sprache verfügt,
so dürfen damit nicht die mit der High-Level-Sprache ausgedrückten Integritätsregeln
und Zwangsbedingungen verletzt oder umgangen werden. Unter Low-Level-Sprache
wird eine Sprache verstanden, die einen satzweisen Zugriff auf die Datenbestände
erlaubt, während eine High-Level-Sprache mengenorientiert arbeitet.
11.09.2005 10:18
Seite 29/88
Datenbanksysteme
An dieser Regel scheitern die meisten nicht-relationalen Systeme, da sie häufig eine
Sprache oder Programmierschnittstelle unterhalb der relationalen Sprache besitzen.
Auch wenn mit Zugangsregeln geprüft wird, wer die Low-Level-Sprache benutzen
darf, bleibt die Regel nach Codd verletzt.
7.3.
Codd’sche Regeln - Zusammenfassung
Auch wenn es hin und wieder gar nicht einfach ist, zu bestimmen, welches System
den Regeln gemäß relational oder nicht-relational sind, für den größten Teil der
existierenden Datenbanksysteme lassen sich die relationalen Regeln anwenden oder
erweisen sich als Hürden.
Dies trifft auf die meisten PC-Datenbanken zu. Diese wurden ursprünglich für den
Einsatz in einer Single-User-Umgebung entworfen, und auch die Speicherung von
Daten auf einem Fileserver macht solch ein System nicht relational.
Vorsichtig muss man weiters bei der Auswahl der Front-End-Systeme sein. Es gibt
Systeme, die auf Tabellen eines relationalen Systems zugreifen können. Doch diese
binden die Tabellen nur ein, bei einem Zugriff wird die Tabelle in den Speicher des
PC geladen, damit dort satzweise zugegriffen werden kann.
Für Verwirrung sorgt in diesem Zusammenhang immer wieder der sogenannte
ODBC-Standard von Microsoft. Es wird als die Standardschnittstelle für relationale
Systeme gepriesen. Es müsste jedoch genauer heißen, dass man mit Hilfe von
Windows-Anwendungen über eine einheitliche Schnittstelle auf relationale DBMS
zugreifen kann.
Um ein DBMS darauf zu prüfen, ob es relational ist oder nicht, genügen auch die
vorgenannten zwölf Regeln nicht. Sie geben jedoch wichtige Kriterien, die fast immer
die Spreu vom Weizen trennen.
Seite 30/88
11.09.2005 10:18
Datenbanksysteme
8.
8.1.
ER-Diagramme
Entitäten
Entität
Entität
X
X, Y, Z
Y
Z
Abbildung 8-1: Darstellung von Entitäten im ER-Diagramm
Attribute der Entität sind X, Y und Z.
Schlüsselattribute (hier: zusammengesetzte Attribute) sind X und Y.
8.2.
Beziehungen
Entität A
Entität B
Beziehung
Abbildung 8-2: Beziehungen im ER-Diagramm
Beziehungen zwischen zwei Entitäten (hier: binäre Beziehung)
Anmerkung: Die Attribute der Entitäten wurden aus Vereinfachungsgründen hier
nicht dargestellt.
8.3.
Beziehungen mit Kardinalitäten
Entität A
1,n
11.09.2005 10:18
Entität B
Beziehung
0,2
Seite 31/88
Datenbanksysteme
Abbildung 8-3: Beziehungen mit Kardinalitäten (ER-Diagramm)
Folgende Aussagen können obenstehendem ER-Diagramm entnommen werden:
•
Ein Element (i.a. Tupel) der Entität A steht in Beziehung zu keinem, einem
oder (maximal) zwei Elementen der Entität B.
•
Ein Element der Entität B steht in Beziehung zu mindestens einem oder
beliebig vielen Elementen der Entität A.
A
B
Abbildung 8-4: Beziehungen mit Kardinalitäten (Mengendarstellung)
8.4.
Syntax von ER-Diagrammen
•
Zwei Entitätsmengen (Rechtecke) dürfen nie direkt nebeneinander liegen; sie
dürfen nur über eine Beziehungsmenge (Raute) miteinander verbunden
werden.
•
Zwei Beziehungsmengen (Rauten) dürfen nie direkt nebeneinander liegen,
dazwischen muss immer eine Entitätsmenge (Rechteck) liegen.
•
Attribute (Ellipsen) können nur direkt entweder an eine Entitätsmenge
(Rechteck) oder eine Beziehungsmenge (Raute) angehängt werden.
8.5.
Umwandlung von ER-Diagrammen in das
relationale Schema
1. Aus jeder Entität entsteht eine Relation (Tabelle)
2. Aus Beziehungen können auch Relationen entstehen
Nicht notwendig ist dies, wenn
- die Kardinalität 1:1 bzw. 1:n ist (siehe Abbildung 8-5: Kardinalität und Tabelle)
und
- wenn es keine Beziehungsattribute an diesem Beziehungstyp gibt (siehe Abbildung
8-6: Beziehungsattribute und Tabelle)
Seite 32/88
11.09.2005 10:18
Datenbanksysteme
Beziehung
Entität A
Entität B
Beziehungstyp:
1
N
1
N
:
:
:
:
Keine Tabelle
Keine Tabelle
Keine Tabelle
Tabelle
1
1
N
M
Abbildung 8-5: Kardinalität und Tabelle
Entität A
Beziehung
Entität B
Beziehungsattribute
Abbildung 8-6: Beziehungsattribute und Tabelle
Wenn aus einer Beziehung eine neue Tabelle entsteht, enthält diese:
•
beide benachbarten Schlüssel der Entitäten (wieder als Schlüssel
gekennzeichnet) und
•
alle (evtl.) vorhandenen Beziehungsattribute (bei n:m müssen diese nicht
vorhanden sein).
11.09.2005 10:18
Seite 33/88
Datenbanksysteme
9.
SQL (Structured Query Language)
9.1.
Historische Entwicklung
1974
1977
1978
1981
1982
1985
1986
1992
1999
2003
9.2.
SEQUEL – ein erster Entwurf einer Datenbankabfragesprache
Oracle implementiert SQL auf Mainframes
Oracle liefert SQL aus
IBM liefert SQL/DS aus
IBM stellt DB/2 vor
Ingres und Informix stellen auf SQL um
X/Open entscheidet sich für SQL
SQL-92 wird veröffentlicht
SQL:1999 wird veröffentlicht und löst SQL-92 ab
SQL:2003 wird im Entwurfsstadium präsentiert
Elemente von SQL
9.2.1. DDL (Data Definition Language)
•
•
•
•
•
Anlage und Verwaltung von Schemata
Definition von Domänen
Relationsdefinition inkl. Konsistenzbedingungen
Definition von Views
Benutzerverwaltung und Zugriffsorganisation
Bausteine der DDL:
CREATE (Table, View, Index)
ALTER (Table, Sequence)
DROP (Table, View, Synonym)
GRANT; REVOKE
9.2.2. DML (Data Manipulation Language)
• Eingabe von Daten in eine (bestehende) Tabelle
• Ändern und Löschen von Daten in Tabellen
• Abfragen über mehrere Tabellen hinweg
Weiters:
• Transaktions- und Sessionsteuerung
• Indexdefinition
Bausteine der DML:
SELECT
INSERT
UPDATE
DELETE
Bei der Transaktionssteuerung werden verwendet:
COMMIT
Seite 34/88
11.09.2005 10:18
Datenbanksysteme
ROLLBACK
SAVEPOINT
SET TRANSACTION
9.2.3. Eigenschaften von SQL
SQL ist nichtprozedural:
Es wird nicht der Ablauf der Datengewinnung beschrieben (wie in üblichen
Programmiersprachen wie z.B. C++). Vielmehr wird das gewünschte Ergebnis
beschrieben.
9.3.
SQL-Anweisungen
9.3.1. Übungsdatenbank in Access
Zur Ausführung der in diesem Kapitel beschriebenen Übungen dient die MS AccessDatenbank ‚Austria 97’. Sie besteht aus 3 Relationen mit folgenden Attributen:
Abbildung 9-1: Relationen von Austria 97
9.3.2. SELECT
9.3.2.1.
Übungsumgebung
Für die hier angeführten Beispiele wird eine neue Abfrage erstellt, und das Fenster
‚Tabellen anzeigen’ ohne Hinzufügung von Tabellen geschlossen:
11.09.2005 10:18
Seite 35/88
Datenbanksysteme
Abbildung 9-2: Einbindung von Tabellen in den Abfrageentwurf
Die SQL-Ansicht der Abfrage erreicht man über die Menüleiste ‚Ansicht’ oder das
Symbol SQL in der Symbolleiste:
Abbildung 9-3: Umschalten auf SQL-Ansicht
9.3.2.2.
Grundsätzlicher Aufbau der Select-Anweisung
SELECT attribut(e)
FROM tabelle(n)
[WHERE kriterien]
[ORDER BY attribut(e)];
9.3.2.3.
WAS soll angezeigt werden?
WO sind die Informationen zu finden?
WELCHE Einschränkungen gibt es?
WIE sollen die Daten dargestellt werden?
Schreibweise von Attributen
SELECT VORWAHL, [VORWAHL], [ORTE].[VORWAHL]
FROM orte;
Alle drei Schreibweisen liefern in diesem Fall das selbe Ergebnis.
Die Schreibweise mit eckigen Klammern ist jedoch bei Attributsbezeichnungen
erforderlich, welche Sonderzeichen oder Leerzeichen enthalten:
[Personal-Nr]
[Personal Nr]
Die sogenannte ‚volle Qualifizierung’ (zusätzliche Angabe des Tabellennamens) ist
erforderlich, wenn in einer Datenbank dasselbe Attribut mit gleichem Namen in
mehreren Tabellen vorkommt.
In unserer Datenbank ist dies das Attribut ORT, welches sowohl in Orte als auch in
der Tabelle PLZ vorkommt.
Daher müssen wir schreiben:
orte.ort
bzw.
plz.ort
9.3.2.4.
Select mit Konstanten
Rechenoperationen:
SELECT 3*5 + 18;
Seite 36/88
11.09.2005 10:18
Datenbanksysteme
Select 19 mod 7;
Modulo (Divisionsrest):
Select 3*5 +18 mod 12;
(warum erscheint hier 21 und nicht 9?)
Select (3*5 +18) mod 12;
String-Concatenation:
Select „Daten“ + „bank“;
Abfrage von Tabellenattributen:
Select “Klein” + ORT + “chen”
FROM ORTE;
Benennung von Ausgabespalten:
Select “Klein” + ORT + “chen” AS städtchen
FROM ORTE;
9.3.2.5.
Rechnen mit Attributen
Addition/Subtraktion:
Höhe aller Orte über Wien:
SELECT Meereshöhe, [Meereshöhe]-171
FROM ORTE;
Vorzeichenwechsel:
Select -PLZ AS negativ_plz
FROM orte;
Multiplikation/Division:
Höhenangabe in Fuss:
SELECT Meereshöhe, Meereshöhe * 3,28
FROM ORTE;
oder
SELECT Meereshöhe, Meereshöhe / 0,295879
FROM ORTE;
Select PLZ*10 AS zehnfachplz
FROM orte;
Nördliche Breite in Bogenmass umrechnen:
SELECT Nördl_Breite, Nördl_Breite * 3.14 / 180 AS rad
FROM ORTE;
Mehrere Konstanten in einem SQL-Statement:
Select 8+5, „a“ + „b“, 8 mod 3, 3.14159 + 2.71828; (Dezimalstelle als Punkt!)
9.3.2.6.
Priorität von Rechenoperationen
Höchste Priorität:
11.09.2005 10:18
Klammersetzung
Vorzeichenoperator (-PLZ)
Seite 37/88
Datenbanksysteme
MOD
Punktrechnung
Niedrigste Priorität: Strichrechnung
9.3.2.7.
Arithmetische Vergleichsoperatoren
Diese liefern als Ergebnis True oder False oder Unknown.
Vergleichsoperatoren sind:
=
> , >=
< , <=
<> oder !=
LIKE
BETWEEN
Gleichheit
Größer als, größer gleich
Kleiner als, kleiner gleich
Ungleichheit
Zeichenkettenvergleich
Numerische Wertebereiche
Beispiele:
SELECT *
FROM orte;
(7214 Tupel)
SELECT *
FROM orte
WHERE meereshöhe > 0;
(4911)
SELECT *
FROM orte
WHERE meereshöhe = 0;
(2303) (Kontrolle: 2303+4911=7214)
Modulo:
Jene Orte anzeigen, bei denen der Divisionsrest der Meereshöhe durch 100 den
Wert 50 ergibt:
SELECT Meereshöhe
FROM ORTE
WHERE (Meereshöhe Mod 100)=50; (133)
9.3.2.8.
Zeichenkettenvergleich mit LIKE
Folgende Wildcardoperatoren werden unterstützt:
*
keines oder beliebig viele Zeichen
?
Genau ein beliebiges Zeichen
Implementierungsabhängig:
#
Numerisches Zeichen
[a-z] Bereich von Characters
[!a-z] Kein Buchstabe
[!0-9] Keine Ziffer
Orte anzeigen, die alphabetisch hinter Wien liegen:
SELECT *
FROM orte
WHERE ort >”Wien”;
(321)
Orte anzeigen, die mit ‚Wien’ beginnen:
SELECT *
Seite 38/88
11.09.2005 10:18
Datenbanksysteme
FROM orte
WHERE ort LIKE ”Wien*”; (7)
Orte anzeigen, die ‚Wien’ beinhalten:
SELECT *
FROM orte
WHERE ort LIKE >”*Wien*”;
(10)
Orte anzeigen, die ‚berg’ beinhalten, gefolgt von genau zwei Buchstaben:
SELECT *
FROM orte
WHERE ort LIKE "berg??";
(2)
Orte anzeigen, die ‚berg’ beinhalten, gefolgt von mindestens zwei Buchstaben:
SELECT *
FROM orte
WHERE ort LIKE "berg??*";
(16)
Orte anzeigen, die ‚Wien’ beinhalten, gefolgt von zumindest einem Buchstaben:
SELECT *
FROM orte
WHERE ort LIKE >”*Wien*[a-z]”; (9, Schottwien fällt weg)
9.3.2.9.
Behandlung von Nullwerten
Der Wert NULL ist ein spezieller Datenwert, der anzeigt, dass kein Wert vorhanden
ist. Dieser Wert ist nicht identisch mit einer numerischen Null oder mit einem
Leerzeichen!
Wenn eine Spalte den Wert NULL nicht annehmen darf, muss dies beim Erstellen
der Tabelle mit der Eigenschaft "NOT NULL" (ungleich Null) spezifiziert werden.
Wenn Werte abgefragt werden müssen, bei welchen Nullwerte eingetragen sein
könnten (NULL), ist der Operator IS NULL (bzw. IS NOT NULL) zu verwenden.
Falsch wäre also z.B. folgende Schreibweise: VORNAME=NULL
SELECT *
FROM orte;
(7214)
SELECT *
FROM orte
WHERE PLZ = 0;
(167)
SELECT *
FROM orte
WHERE PLZ > 0;
(1193)
SELECT *
FROM orte
WHERE PLZ IS NULL;
(5854; Kontrolle: 5854+1193+167=7214)
SELECT *
FROM orte
WHERE PLZ IS NOT NULL;
(1360, ist 167+1193))
11.09.2005 10:18
Seite 39/88
Datenbanksysteme
9.3.2.10.
Zeichenverkettung
Diese erfolgt mit „|“ (Pipe) bzw. in Access mit „&“.
Beispiel:
SELECT plz & " " & ort AS plzort
FROM orte;
9.3.2.11.
Logische Operatoren
Um mehrere Bedingungen miteinander zu verknüpfen, verwenden wir
Vergleichsoperatoren. Wir kennen 3 logische Operatoren:
AND
Alle Ausdrücke müssen TRUE ergeben
OR
Mindestens einer der Ausdrücke muss TRUE ergeben
NOT
Der Aussagewert eines Ausdrucks wird invertiert
Beispiele:
Liste jene Orte, die über 400 Meter Seehöhe liegen und mehr als 10.000 Haushalte
haben.
Select *
FROM orte
WHERE meereshöhe > 400 AND haushalte > 10000;
(4)
Liste jene Orte, die über 2000 Meter Seehöhe liegen sowie zusätzlich alle, die im
Bundesland Wien liegen:
SELECT *
FROM orte
WHERE Meereshöhe>2000 OR Bdld="w";
(55)
Liste alle Orte in Kärnten über 700m Seehöhe, und zusätzlich alle Orte in Tirol mit
mehr als 5000 Haushalten:
SELECT *
FROM orte
WHERE (Meereshöhe>700 AND bdld="k") OR (bdld="t" AND
haushalte>5000);
(13)
Orte im Burgenland mit mehr als 2000 Haushalten:
Select *
from Orte
WHERE bdld = „B“ and haushalte >2000;
(5)
Verwendung von BETWEEN:
Gesucht sind jene Orte, die Haushalte zwischen 5000 und 10000 haben:
SELECT * FROM orte
WHERE haushalte BETWEEN 5000 and 10000;
Hinweis: Die Grenzwerte (in unserem Beispiel 5000 bzw. 10000) werden in die
Abfrage mit einbezogen.
Die obige Abfrage ist daher ident mit folgender:
SELECT * FROM orte
WHERE haushalte >= 5000 AND haushalte <=10000;
Kombinierte Abfragen:
Seite 40/88
11.09.2005 10:18
Datenbanksysteme
Liste jene Orte in NÖ, die nicht mit der PLZ 2xxx beginnen und die mehr als 10.000
Haushalte haben, und zusätzlich jene Südtiroler Orte (PLZ-Bereich 9xxx), die über
1400m Seehöhe liegen:
SELECT *
FROM orte
WHERE (BDLD="N" AND PLZ Not Like "2*" AND HAUSHALTE>10000) OR
(BDLD="T" AND Meereshöhe>1400 AND PLZ Like "9*"); (St. Pölten,
Obertilliach)
Liste jene Orte, die zwar ein Bundesland, aber keine PLZ eingetragen haben:
SELECT *
FROM orte
WHERE PLZ Is Null AND BDLD Is Not Null;
(73)
Finde jenen Ort in der Steiermark (160), der mehr als 8000 Haushalte hat (4), nicht
mit ‚K’ beginnt (2) und nicht mit ‚mur’ endet
SELECT *
FROM orte
WHERE BDLD="st" AND HAUSHALTE>8000
AND Ort Not Like "k*" AND Ort Not Like "*mur";
(Leoben)
Liste jene Orte, bei welchen eine gültige (!) PLZ eingetragen ist (1193), die mit ‚Ma’
beginnen (29)‚ die Zeichenkette ‚am’ im Ortsnamen tragen, und mit der Vorwahl 5***
beginnen.
SELECT *
FROM orte
WHERE PLZ Is Not Null AND PLZ>0 AND Ort Like "ma*" AND Ort Like "*am*"
AND VORWAHL Like "5*";
(Matrei am Brenner)
9.3.2.12.
DISTINCT-Klausel
Die Option DISTINCT in der SELECT-Klausel schließt doppelt vorkommende
(redundante) Zeilen aus der abgerufenen Menge aus. Nur die einmaligen (unique)
Zeilen werden bei der Abfrage ausgegeben.
SELECT DISTINCT attribut(e)
FROM relation(en)
WHERE bedingung(en).
Der DISTINCT-Operator sortiert bei den meisten SQL-Implementierungen die
Ausgabe aufsteigend.
Beispiel:
Aus der Tabelle ORTE sollen alle vorkommenden Eintragungen zum Attribut ‚BDLD’
(Bundesland) angezeigt werden.
SELECT DISTINCT bdld
FROM ORTE;
11.09.2005 10:18
Seite 41/88
Datenbanksysteme
Wir erhalten, je Attribut jeweils nur ein einziges Vorkommen desselben.
In einem späteren Kapitel werden wir klären, wie viele Datensätze in dieser Relation
je Bundesland gespeichert sind (sh. COUNT-Funktion).
9.3.2.13.
Mengenoperationen
( Sollten erst später bei den Joins gebracht werden!!)
9.3.2.13.1.
UNION
Union fasst zwei oder mehr Relationen spaltenweise zusammen und entfernt gleiche
Tupel aus der Ergebnisrelation.
Beispiel:
In der Abbildung 9-4 sind auf der linken Seite zwei Relationen Tabelle1 und
Tabelle1a dargestellt.
Die Union-Operation
SELECT a, b FROM tabelle1
UNION
SELECT a, b FROM tabelle1a;
liefert das unten rechts abgebildete Ergebnis.
Abbildung 9-4 UNION
Seite 42/88
11.09.2005 10:18
Datenbanksysteme
9.3.2.13.2.
INTERSECT
Intersect liefert jene Tupel, welche in zwei Relationen ident auftreten. Die Syntax ist
analog zum UNION-Statement.
Intersect ist in MS Access nicht implementiert, es muss daher wie folgt realisiert
werden:
SELECT a
FROM tabelle1
WHERE a In
(SELECT a FROM tabelle1a);
Als Ergebnis (unter Heranziehung der Tabellen aus obigem Beispiel) erhalten wir die
bezüglich Attribut a identen Zeilen:
Abbildung 9-5 INTERSECT
Schwieriger ist die Abfrage, wenn alle Tupel in das Ergebnis einbezogen werden
sollen. Mit der Abfrage
SELECT a & b
FROM tabelle1
WHERE a & b IN
(SELECT a & b FROM tabelle1a):
erhalten wir:
Abbildung 9-6 INTERSECT für alle Tupel
9.3.2.13.3.
MINUS
MINUS liefert jene Tupel, welche zwar in einer Basisrelation, nicht jedoch in einer
anderen Relationen auftreten. Die Syntax lautet:
SELECT a, b FROM tabelle1
MINUS
SELECT a, b FROM tabelle1a;
Auch MINUS ist in MS Access nicht implementiert, die Umsetzung wird in diesem
Skriptum später behandelt.
11.09.2005 10:18
Seite 43/88
Datenbanksysteme
9.3.2.14.
ORDER BY-Klausel
Die ORDER BY-Klausel bestimmt die Kriterien der Zeilensortierung bei
Abfrageergebnissen.
Standard ist aufsteigende Reihenfolge (ASC – Ascending), aber auch absteigende
Reihenfolge (DESC – Descending) ist möglich.
Die Sortierung erfolgt in dieser Form:
• für ASCII-Codes: 0 bis 9, A bis Z (von links nach rechts Zeichen für Zeichen),
• für EBCDIC-Codes (Großrechner): A bis Z, O bis 9
• für Zahlen (Ganzzahlen, reelle Zahlen): - unendlich bis + unendlich
NULL-Werte werden bei aufsteigender Sortierung am Ende angezeigt; bei
absteigender Sortierung werden sie zuerst dargestellt.
Die ORDER BY-Klausel:
• kann Ausdrücke von Spaltenwerten enthalten
• kann Spaltenaliasnamen (AS...) enthalten
• kann ein mehrfach verschachteltes Kriterium enthalten
• Priorität von links nach rechts.
Beispiel:
Sortiere jene Orte, wo ein Bundesland angegeben ist, aufsteigend nach Bundesland
und anschließend absteigend nach der Anzahl der Haushalte.
SELECT *
FROM orte
WHERE bdld Is Not Null
ORDER BY bdld, haushalte DESC;
9.3.2.15.
Funktionen
Diese werden in Ausdrücken verwendet, die Spaltenwerte enthalten.
Beispiele:
• SELECT-Klausel
• WHERE -Klausel
• ORDER BY-Klausel
Es gibt folgende Typen von Funktionen:
• Arithmetische Funktionen
• Zeichenkettenfunktionen
• Datumsfunktionen
• Aggregatfunktionen
• Verschiedene
Die Syntax von Funktionen ist immer nach folgendem Muster aufgebaut:
funktionsname (Argument)
Sind in der Klammer mehrere Argumente zulässig, sind sie durch Kommata
voneinander getrennt.
Seite 44/88
11.09.2005 10:18
Datenbanksysteme
Das Argument kann ein Wert, eine Zeichenkette, ein Ausdruck, eine Spalte oder
wieder eine Funktion sein.
9.3.2.15.1.
Achtung:
Arithmetische Funktionen
Bei allen trigonometrischen Funktionen ist der Winkel im
Bogenmaß anzugeben!
ABS (n)
Ermittelt wird der Absolutwert (vorzeichenlose Wert) einer Zahl.
abs(-3)=3
COS (n)
Ermittelt den Cosinus einer Zahl.
COSH (n)
Ermittelt wird der Kosinus hyperbolicus einer Zahl.
cosh (0) = 1
EXP (n)
Ist die Funktion e hoch "n", mit e = 2.71828183
exp (4) = 54.59815
LN (n)
Ermittelt wird der natürliche Logarithmus einer Zahl.
In (95) = 4.55387689
LOG (m,n)
Ermittelt wird der Logarithmus "n" zur Basis "m". Beide Zahlen
müssen positiv und größer als 0 und die Basis m ungleich 1 sein.
log (10, 100) = 2
MOD (m,n)
Ermittelt wird der Rest der ersten Zahl geteilt durch die zweite Zahl.
mod(12,7)
12 mod 7
=5
mod(12.3, 7.1) 12.3 mod 7.1
= 5.2
oder
M MOD n
POWER (m,n)
Ermittelt wird die n-te Potenz von m. Wenn m negativ ist, muss n
ganzzahlig sein.
power (64,1/3) = 4
ROUND (n [,m]) Rundet auf Anzahl Stellen, die mit m angegeben wird. Wird m nicht
angegeben, wird 0 angenommen.
round(327.8292,2) = 327.83
round(327.8292,-2) = 300
round(327.8292) = 327
SIGN (n)
Liefert das Vorzeichen des Arguments.
SIGN(13)=1
SIGN(-13)=-1
SIGN(0)=0
SIN (n)
Ermittelt wird der Sinus einer Zahl
SINH(n)
Sinus Hyperbolicus
sinh (1) = 1. 17520119
11.09.2005 10:18
Seite 45/88
Datenbanksysteme
SQRT (n)
Ermittelt wird die Quadratwurzel einer Zahl mit n >= 0.
TAN (n)
Ermittelt wird der Tangens einer Zahl.
tan (45 * 3.14159265359/180) = 1
TANH (n)
Ermittelt den Tangens hyperbolicus einer Zahl.
tanh (0.5) = 0.462117157
TRUNC (n [,m]) Abschneiden von Dezimalstellen. Wenn m nicht definiert ist, wird
beim Dezimalpunkt abgeschnitten. Die zweite Zahl kann auch einen
negativen Wert annehmen, um die Stellen links vom Dezimalpunkt
abzuschneiden.
trunc (327.2) = 327
trunc (3.2789,2) = 3.27
trunc (327.89,-2) = 300
TRUNC ist in MS Access als FIX() implementiert.
9.3.2.15.2.
Zeichenkettenfunktionen
CHR(a)
Liefert den Buchstaben, der durch den ASCII-Code ‚a’ repräsentiert
wird.
UCASE(str)
Wandelt den String ‚str’ in Grossbuchstaben um
LCASE(str)
Wandelt den String ‚str’ in Kleinbuchstaben um
LEN(str)
Liefert die Länge des Strings ‚str’ zurück
MID(str,m,n)
Liefert einen Teilstring von ‘str’, der an Position m beginnt und n
Zeichen lang ist
LEFT(str.m)
Liefert den linken Teil von ‘str’ in der Länge m
RIGHT(str.m)
Liefert den rechten Teil von ‘str’ in der Länge m
CSTR(m)
Wandelt die Zahl ‚m’ in einen String um
Str(str)
Wandelt den String ‚str’ in eine Zahl um
Beispiele:
Das SQL-Statement
SELECT chr(66), UCase("ABCdef") ,lCase("ABCdef"), len("länge"), mid
("abcdef",2,3), left("abcdef",2), right ("abcdef",2), cstr(30), str("238");
liefert folgendes Ergebnis:
Seite 46/88
11.09.2005 10:18
Datenbanksysteme
9.3.2.15.3.
Datumsfunktionen
To be continued ☺...
9.3.2.15.4.
AVG
COUNT
MAX
MIN
SUM
STDDEV
VAR
Aggregatfunktionen
Mittelwert einer Spalte
Anzahl der selektierten Zeilen
Größter Wert einer Spalte
Kleinster Wert einer Spalte
Summe aller Werte einer Spalte
Standardabweichung
Varianz
Die Funktionen COUNT, MAX und MIN sind anwendbar auf Daten vom Typ CHAR,
DATE und NUMBER.
Die übrigen Gruppenfunktionen sind lediglich auf Daten vom Typ NUMBER
anwendbar.
Beispiele:
Wie hoch ist die durchschnittliche Meereshöhe aller Orte in Österreich?
SELECT avg(meereshöhe)
FROM ORTE;
(345,77m)
Wie viele Orte gibt es, die mehr als 20.000 Haushalte haben?
SELECT count(ort)
FROM ORTE
WHERE haushalte > 20.000;
(5)
Wie hoch liegt der höchste Ort Österreichs?
SELECT max(meereshöhe)
FROM ORTE;
(2.837m)
Wie viele Haushalte gibt es im Burgendland?
SELECT sum(haushalte)
FROM ORTE
WHERE bdld=“B“;
9.3.2.16.
(71.934 Haushalte)
Die Klausel GROUP BY
Zur Aggregierung (Zusammenfassung) von Daten wird die Klausel GROUP BY
benutzt, um die Eigenschaften einer Menge von Zeilen zu ermitteln.
11.09.2005 10:18
Seite 47/88
Datenbanksysteme
Wenn mehr als eine Menge (d.h. Relation) abgefragt wird, steuert die GROUP BYKlausel die Gruppierung der Zeilen aus der ursprünglichen Tabelle in eine neue
virtuelle temporäre Tabelle.
Die HAVING-Klausel wieder entscheidet, welche Gruppenergebnisse ausgegeben
werden sollen. Die Klauseln werden zur Laufzeit in folgender Reihenfolge ausgeführt:
•
•
•
•
WHERE
GROUP BY
HAVING
ORDER BY
Filtern von Zeilen
Erstellung einer neuen Gruppentabelle
Filtern von Gruppenergebnissen
Sortieren der Ausgabe
Erläuterungen:
Mit ‚WHERE’ werden zunächst die Sätze der Relation gefiltert. Nur diese gefilterte
Ergebnismenge wird dann der Klausel ‚GROUP BY’ übergeben.
Mittels ‚GROUP BY’ wird nun nach jenen Attributen gruppiert, die in der Klausel
angeführt sind.
‚HAVING’ filtert in den bereits gruppierten Ergebnissen nur jene Tupel heraus, die in
dieser Bedingung definiert sind.
‚ORDER BY’ schließlich sortiert die übrig bleibende Ergebnismenge nach den
angegebenen Attributen.
Beispiele:
(Anmerkung: Die nun folgenden Beispiele beziehen sich wieder auf die Datenbank
‚Austria 97’.)
1) Wie viele Datensätze je Bundesland gibt es in der Relation Orte?
SELECT BDLD, Count(BDLD) AS [Anzahl von BDLD]
FROM ORTE
GROUP BY BDLD;
Warum erscheinen hier in Summe nur einige hundert Sätze, obwohl die Relation
über 7.000 Tupel beinhaltet?
Die Funktion COUNT zählt den Wert NULL nicht mit, es sei denn, die NVL-Funktion
(‚Null values’) wird verwendet.
Seite 48/88
11.09.2005 10:18
Datenbanksysteme
Wie hätte nun das SELECT-Statement lauten müssen, wenn wir (ohne Verwendung
der NVL-Funktion) alle Sätze in der Relation zählen wollten?
SELECT BDLD, Count(*) AS [Anzahl von BDLD]
FROM ORTE
GROUP BY BDLD;
Der *-Operator bezieht sich auf sämtliche Attribute eines Tupels und kann somit
keine Nullwerte enthalten.
2) Liste je Bundesland den in der Datenbank höchsten eingetragenen Wert der
Meereshöhe sowie den durchschnittlichen gerundeten Wert je Bundesland
SELECT BDLD, Max(Meereshöhe) AS [Max von Meereshöhe],
round(Avg(Meereshöhe)) AS [Mittelwert von Meereshöhe]
FROM ORTE
GROUP BY BDLD;
3) Adaptiere obige Abfrage so, dass nur jene Bundesländer aufgelistet werden,
deren höchster Ort über 1200m Meereshöhe liegt. Sortiere das Ergebnis absteigend
nach Meereshöhe.
SELECT BDLD, Max(Meereshöhe) AS [Max von Meereshöhe],
round(Avg(Meereshöhe)) AS [Mittelwert von Meereshöhe]
FROM ORTE
GROUP BY ORTE.BDLD
HAVING Max(Meereshöhe)>1200
ORDER BY Max(Meereshöhe) DESC;
11.09.2005 10:18
Seite 49/88
Datenbanksysteme
4) Erstelle eine Liste, welche je Bundesland die Anzahl der Haushalte auflistet.
SELECT BDLD, Sum(HAUSHALTE) AS [Summe von HAUSHALTE]
FROM ORTE
GROUP BY BDLD;
5) Modifiziere die obige Abfrage so, dass die leeren Eintragungen und die Einträge
von ‚Wien’ nicht erscheinen.
SELECT BDLD, Sum(HAUSHALTE) AS [Summe von HAUSHALTE]
FROM ORTE
GROUP BY BDLD
HAVING Sum(HAUSHALTE)>0;
Seite 50/88
11.09.2005 10:18
Datenbanksysteme
6) Erstelle eine Liste je Bundesland, in der die durchschnittliche gerundete
Meereshöhe aller Orte dieses Bundeslandes dargestellt wird. Nicht berücksichtigt
werden dürfen jedoch Orte, deren Meereshöhe 0 oder unbekannt ist.
SELECT BDLD, round(Avg([Meereshöhe]))
FROM ORTE
WHERE Meereshöhe>0 Or Meereshöhe Is Null
GROUP BY BDLD
HAVING Avg(Meereshöhe) >100;
7) Wieviele Orte gibt es, die jeweils zwischen 0-1000, 1000-2000, und 2000-3000m
Meereshöhe liegen?
SELECT round(Fix([Meereshöhe]/1000)) & "xxx" , Count(Meereshöhe) AS [Anzahl
von Meereshöhe]
FROM ORTE
GROUP BY round(Fix([Meereshöhe]/1000));
Hinweis: Die nun folgenden Beispiele beziehen sich auf die Beispieldatenbank
“EDV-Inventar”!
8) Gesucht ist eine Abfrage, in der die Gehaltssummen der Mitarbeiter je Abteilung
aufgelistet werden.
SELECT [person].[Abteilung-ID], Sum(Gehalt) AS [Summe von Gehalt]
FROM Person
11.09.2005 10:18
Seite 51/88
Datenbanksysteme
GROUP BY [person].[Abteilung-ID];
Anmerkung: Bei diesem Select-Statement ist die referenzierte Tabelle zusätzlich
anzugeben, da das Attribut „Abteilung-ID“ in mehr als einer Tabelle vorkommt.
9) Ermittle das durchschnittliche Jahresgehalt der Mitarbeiter in jeder Abteilung
SELECT Person.[Abteilung-ID], Avg(Person.Gehalt) AS [Mittelwert von Gehalt]
FROM Person
GROUP BY Person.[Abteilung-ID];
10) Ermittle das durchschnittliche Gehalt je Abteilung mit mindestens 5 Mitarbeitern
SELECT [Abteilung-ID], Avg(Person.Gehalt) AS [Mittelwert von Gehalt]
FROM Person
GROUP BY [Abteilung-ID]
HAVING Count(PersonalNr) > 5;
11) Wie viele Mitarbeiter jedes Tätigkeitsbereichs gibt es je Abteilung? Wie hoch ist
deren mittleres Jahresgehalt?
Seite 52/88
11.09.2005 10:18
Datenbanksysteme
SELECT [Abteilung-ID], Tätigkeit, Avg(Gehalt) AS [Mittelwert von Gehalt]
FROM Person
GROUP BY [Abteilung-ID], Tätigkeit;
12) In welcher Abteilung arbeitet mehr als ein Mitarbeiter je Tätigkeitsfeld, und wie
hoch ist das mittlere Gehalt dieser Mitarbeiter? Ausgabe nach Mittelwerten der
Gehälter absteigend sortiert.
SELECT [Abteilung-ID], Tätigkeit, Count(PersonalNr) AS [Anzahl von PersonalNr],
Avg(Gehalt) AS [Mittelwert von Gehalt]
FROM Person
GROUP BY [Abteilung-ID], Tätigkeit
HAVING Count(PersonalNr) > 1
ORDER BY Avg(Gehalt) DESC;
11.09.2005 10:18
Seite 53/88
Datenbanksysteme
Anmerkung: Chef, Leiter und Chefsekretärin scheinen hier nicht auf, da diese
Tätigkeit nur einmal vertreten ist.
13) Zusammenfassendes Beispiel zu GROUP BY:
Gesucht sind jene Abteilungen (Abteilung-ID), in denen das durchschnittliche Gehalt
der Verkäufer größer als 30.000 ist. Sortiere absteigend nach diesem
Durchschnittsgehalt.
a) Einschränkung auf Tätigkeit als Verkäufer:
SELECT [Abteilung-ID]
FROM Person
WHERE Tätigkeit="Verkäufer";
b) Anzeigen des durchschnittlichen Gehalts in jeder einzelnen Abteilung:
SELECT [Abteilung-ID], AVG(Gehalt)
FROM Person
WHERE Tätigkeit="Verkäufer"
GROUP BY [Abteilung-ID], Tätigkeit;
Anmerkung:
Frage: Warum muss ‚Tätigkeit’ in der GROUP BY-Klausel stehen?
Antwort: ALLE in der WHERE-Klausel befindlichen Attribute müssen in der GROUP
BY-Klausel wieder aufgezählt werden.
c) Einschränkung auf Gehalt > 30.000 (das ist eine Einschränkung auf eine
aggregierte Größe!)
SELECT [Abteilung-ID], Avg(Gehalt)
FROM Person
WHERE Tätigkeit="Verkäufer"
GROUP BY [Abteilung-ID], Tätigkeit
HAVING AVG(Gehalt)>30000;
d) Sortiere absteigend nach dem Durchschnittsgehalt
SELECT [Abteilung-ID], Avg(Gehalt)
FROM Person
WHERE Tätigkeit="Verkäufer"
GROUP BY [Abteilung-ID], Tätigkeit
HAVING Avg(Gehalt)>30000
ORDER BY AVG(Gehalt) DESC;
9.3.2.16.1.
Verschiedene Funktionen
t.b.c. ☺
9.3.2.17.
9.3.2.17.1.
JOIN - Abfragen über mehrere Tabellen
Einleitung
Bisher wurden sämtliche Abfragen basierend auf einer einzigen Relation
durchgeführt. SQL bietet jedoch die Möglichkeit, Abfragen über beliebig viele
Seite 54/88
11.09.2005 10:18
Datenbanksysteme
Tabellen in einem DBMS zu formulieren. Derartige Abfragen werden über sog. Joins
(Verbindung, Kombination) realisiert.
Ein Join ist immer dann notwendig, wenn Daten in mehreren Tabellen gehalten
werden, diese Tabellen aber in einer Beziehung zueinander stehen und daher
zusammen angezeigt werden müssen. Diese Beziehung wird meist über die
Primärschlüssel- und Fremdschlüsselspalten realisiert.
Die Verbindung der Tabellen über passende Attribute ist besonders wichtig, da es
sonst zur Bildung des kartesischen Produkts zweier oder mehrerer Tabellen kommt.
In letzterem (zumeist ungewünschten) Fall werden alle Tupel der einen Tabelle mit
allen Tupeln der anderen Tabelle in allen Kombinationsmöglichkeiten verknüpft. Die
Ergebnisrelation zweier Tabellen mit 100 bzw. 250 Datensätzen hat bei Bildung
eines kartesischen Produkts bereits 25.000 Datensätze!
Folgende Joins können unterschieden werden:
a) Inner Joins und Outer Joins
Beim INNER-Join werden nur die Ergebnisse zweier abgefragter Tabellen
präsentiert, bei denen in beiden Tabellen entsprechende Tupel vorhanden sind.
MS Access: INNER JOIN
Im Gegensatz dazu werden im OUTER-Join auch Datensätze, die der JoinBedingung nicht in beiden verknüpften Tabellen genügen, in den Ergebnisreport mit
einbezogen.
Der Outer Join wird in der aktuellen Implementierung von SQL (definiert in der
Spezifikation ‚SQL 92’) noch wie folgt unterteilt:
Outer Joins
Left Outer Join
Right Outer Join
Full Outer Join
Implementierung in MS Access
LEFT JOIN
RIGHT JOIN
-
b) Equi-Joins und Non-Equi-Joins
EQUI-Join bedeutet, dass die Join-Bedingung mit dem Gleichheitsoperator (=)
gebildet wurde, was sicherlich den bei weitem häufigsten Fall darstellt.
NON EQUI-Join bedeutet, dass ein beliebiger Operator (<,>,<>) in der JoinBedingung vorkommt.
Jeder Join ist somit entweder ein Inner oder Outer Join, welcher als Equi Join oder
als Non Equi Join ausgelegt sein kann.
Sonderfall: Self Joins
SELF-Join bedeutet, dass eine Tabelle mit sich selbst verknüpft wird. Hier ist es
notwendig, den Tabellen in der FROM-Klausel des SELECT-Statements alternative
Tabellenbezeichnungen (table aliases) zuzuweisen.
Sonderfall: Natural Joins
11.09.2005 10:18
Seite 55/88
Datenbanksysteme
Bei Natural Joins müssen die zu verbindenden Attribute in den beiden Tabellen den
selben Namen haben. Dann werden nur noch die zu verknüpfenden Tabellen
definiert, die entsprechenden Attribute werden automatisch aufgrund dieser
Namensgleichheit ausgewählt.
In der Praxis hat sich die Erstellung von Natural Joins nicht sehr bewährt, da bei
kleinen Änderungen in der Datenbankstruktur oder in der Namensgebung von
Attributen schwere Fehler in bereits einwandfrei laufenden Programmteilen auftreten
können.
In der Beispieldatenbank „EDV-Inventar“ sind die Tabellen „Person“ und „Abteilung“
über die Spalte „Abteilung-ID“ modelliert, die in der Abteilung-Tabelle
Primärschlüssel und in der Person-Tabelle Fremdschlüssel ist.
In einer Joinabfrage können auch mehr als zwei Tabellen vorkommen. Bei n
Tabellen müssen mindestens jedoch n -1 Join-Bedingungen formuliert werden.
9.3.2.17.2.
EQUI-JOIN
Im folgenden werden nun SELECT-Statements vorgestellt, die auf mehr als eine
Tabelle zugreifen.
1) Erstelle eine Liste aller Mitarbeiter (Vorname, Zuname) sowie der Bezeichnung
der Abteilung, in welcher dieser Mitarbeiter beschäftigt ist.
SELECT Person.Vorname, Person.Zuname, Abteilung.Bezeichnung
FROM Abteilung, person
WHERE Abteilung.[Abteilung-ID] = Person.[Abteilung-ID];
Die oben angeführte Syntax des Select-Statements war in älteren SQLImplementierungen die einzige Möglichkeit, Joins abzufragen.
Seit der Definition des SQL-Standards 1992 (‚SQL 92’) wird verstärkt zu folgender
Schreibweise übergegangen:
SELECT Person.Vorname, Person.Zuname, Abteilung.Bezeichnung
Seite 56/88
11.09.2005 10:18
Datenbanksysteme
FROM Abteilung INNER JOIN Person
ON Abteilung.[Abteilung-ID] = Person.[Abteilung-ID];
Beachte: Bei der neuen SQL-Schreibweise wird das die beiden Tabellen
verbindende gemeinsame Attribut durch die ON-Bedingung (statt wie bisher durch
die WHERE-Klausel) definiert.
Anmerkung: In dieser Darstellung erscheinen nur 40 Mitarbeiter, obwohl im
Unternehmen 44 Mitarbeiter angestellt sind. Ein Blick in die Tabelle Person zeigt uns,
dass diese 4 fehlenden Mitarbeiter keiner Abteilung zugewiesen sind.
Um auch die fehlenden Mitarbeiter anzuzeigen, müssen wir einen Outer Join
verwenden.
9.3.2.17.3.
OUTER-JOIN
2a) Modifiziere obiges Beispiel so, dass sämtliche Mitarbeiter dargestellt werden.
SELECT Vorname, Zuname, Bezeichnung, Standort
FROM person LEFT JOIN Abteilung
ON person.[Abteilung-ID] = Abteilung.[Abteilung-ID];
Durch die Formulierung
FROM Person LEFT JOIN Abteilung
werden alle Mitarbeiter der Tabelle Person ausgegeben, unabhängig davon, ob sie
einer Abteilung angehören. Der Begriff ‚LEFT’ bezieht sich auf jene (linksstehende
Tabelle, von der alle Tupel ausgegeben werden sollen (hier: ‚Person’).
Das obige SELECT-Statement ist daher gleichbedeutend mit jenem:
FROM Abteilung RIGHT JOIN Person
11.09.2005 10:18
Seite 57/88
Datenbanksysteme
2b) Ändere obiges Beispiel so, dass sämtliche Abteilungen dargestellt werden.
SELECT Vorname, Zuname, Bezeichnung, Standort
FROM Abteilung LEFT JOIN Person
ON person.[Abteilung-ID] = Abteilung.[Abteilung-ID];
Durch die Formulierung
FROM Abteilung LEFT JOIN Person
bzw.
FROM Person RIGHT JOIN Abteilung
werden alle Abteilungen der Tabelle Abteilung ausgegeben, unabhängig davon, ob
sie einen Mitarbeiter haben oder nicht.
Es ist daher sehr wesentlich zu analysieren, welches Ergebnis von der Abfrage
erwartet wird, und dementsprechend den Outer-Join zu formulieren.
3) Der Computer mit dem Betriebssystem „win 3.11“ soll nun endlich upgegradet
werden. An welchen Standort muss der Techniker fahren?
Hinweis: Das Betriebsystem finden wir in Tabelle Computer.
Der Standort liegt in Tabelle Abteilung.
Die beiden Tabellen sind über Person miteinander verbunden.
Wir bauen die Lösung somit über die Reihenfolge der Abfrage der Tabellen auf:
In einem ersten Schritt soll jene Person und deren Abteilungs-ID angezeigt werden,
wo der Computer steht:
SELECT Zuname, person.[Abteilung-ID]
FROM
Computer LEFT JOIN Person ON Computer.Person_ID = person.PersonalNr
WHERE Betriebssystem="win 3.11";
Seite 58/88
11.09.2005 10:18
Datenbanksysteme
Da diese Abfrage richtig läuft, erweitern wir sie um die Tabelle Abteilung:1
1
2
3
4
5
SELECT Zuname, person.[Abteilung-ID], standort
FROM
(Computer LEFT JOIN Person ON Computer.Person_ID = person.PersonalNr)
LEFT JOIN Abteilung ON person.[abteilung-id] = abteilung.[abteilung-id]
WHERE Betriebssystem="win 3.11";
Es ist unschwer zu erkennen, dass der Ausdruck aus Zeile 3 aus der vorherigen
Abfrage übernommen wurde. Diese Ergebnismenge wurde nun mit einem weiteren
LEFT Join mit der Tabelle Abteilung verknüpft (Zeile 4).
Auch für den zweiten Join ist das verbindende Attribut (hier die Abteilungs-ID) in der
ON-Klausel anzugeben.
Als Ergebnis obiger Abfrage erhalten wir nun die gewünschte Information:
4) Liste alle Monitore, die im Shop stehen!
Diese Abfrage erstreckt sich nun über 4 Tabellen:
Vom Standort ‚Shop’ in der Abteilung gelangen wir zu Person, und von dort über
Computer schließlich zu Monitor.
Wir können auch diese Abfrage sukzessive aufbauen:
4a) Welche Personen arbeiten im Shop?
SELECT PersonalNr, Zuname, Standort
FROM
Abteilung RIGHT JOIN Person ON Abteilung.[Abteilung-ID] = Person.[Abteilung-ID]
WHERE Standort="SHOP";
1
Anmerkung: Die Zeilennummerierung dient lediglich der besseren Dokumentation, bei Ausführung
der Abfrage in MS Access ist sie zu entfernen.
Es gibt jedoch Systeme (z.B. Oracle), bei denen das System während der Erstellung von SQLStatements ebenfalls eine derartige Nummerierung automatisch durchführt. Diese Nummerierung ist
jedoch nie Bestandteil des eigentlichen Statements.
11.09.2005 10:18
Seite 59/88
Datenbanksysteme
So weit, so gut.
4b) Welche Inventarnummern haben die Computer der Personen, die im Shop
arbeiten 2?
SELECT PersonalNr, Zuname, Standort, Computer.[Inv-Nr]
FROM
(Abteilung RIGHT JOIN Person ON Abteilung.[Abteilung-ID] = Person.[Abteilung-ID])
LEFT JOIN
Computer ON person.PersonalNr = computer.Person_ID
WHERE Standort="SHOP"
ORDER BY computer.[inv-nr];
Auch hier lässt sich aus der dritten Zeile gut erkennen, wie das SQL-Statement aus
dem vorhergegangenen (4a)) abgeleitet wird.
Wir erhalten:
Nur die zwei Mitarbeiter Waltl und Leinthaler haben je einen PC.
4c) Welche Monitore hängen nun an den obigen PC’s?
SELECT PersonalNr, Zuname, Abteilung.Standort,
2
Zur leichteren Lesbarkeit werden mehr Attribute dargestellt, als in der Frage formuliert wurden.
Seite 60/88
11.09.2005 10:18
Datenbanksysteme
Computer.[Inv-Nr], Monitor.InvNr, Bauart, Hersteller, Type
FROM
(
(Abteilung RIGHT JOIN Person ON Abteilung.[Abteilung-ID] =
Person.[Abteilung-ID]
)
LEFT JOIN Computer ON Person.PersonalNr = Computer.Person_ID
)
LEFT JOIN Monitor ON Computer.[Inv-Nr] = Monitor.Comp_ID
WHERE Standort="SHOP"
ORDER BY Computer.[Inv-Nr];
Womit wir das eingangs gefragte Ergebnis erhalten:
4d) Bitte nur die Monitore, keine Mitarbeiter, die gar keinen Computer haben!
Wir erweitern die WHERE-Klausel
WHERE Standort="SHOP" AND (Monitor.Invnr IS NOT NULL)
und erhalten schließlich:
Die letzte Aufgabenstellung hätten wir auch mit einem EQUI-Join realisieren können,
wie uns das nun folgende Beispiel zeigen wird:
5) Welche Drucker stehen in Abteilungen, die über weniger als 20.000€ Budget
verfügen können?
Hinweis:
Diese Abfrage involviert die Tabellen Abteilung (wegen des dort stehenden Budgets),
Person, Computer, Comp_Druck und Drucker.
SELECT Drucker.InvNr, Hersteller, Drucker.Bezeichnung, Bauart, Farbdrucker
FROM
Drucker INNER JOIN
11.09.2005 10:18
Seite 61/88
Datenbanksysteme
(
(
(Abteilung INNER JOIN Person
ON Abteilung.[Abteilung-ID] = Person.[Abteilung-ID]
)
INNER JOIN Computer
ON Person.PersonalNr = Computer.Person_ID
)
INNER JOIN Comp_Druck
ON Computer.[Inv-Nr] = Comp_Druck.Comp
)
ON Drucker.InvNr = Comp_Druck.Druck
WHERE Abteilung.Budget < 20000;
Wir erhalten:
9.3.2.17.4.
SELF-JOIN
Beim Self-Join wird – wie bereits erwähnt – ein und dieselbe Tabelle mehrfach
verknüpft.
Betrachten wir dazu folgendes Beispiel:
1) Mitarbeiter inklusive ihrem Vorgesetzten auflisten
In der Tabelle Person gibt es ein Attribut, welches den jeweiligen Vorgesetzten der
einzelnen Mitarbeiter abbildet.
Wenn wir nun eine Liste aller Mitarbeiter mit Angabe ihres jeweiligen Chefs
benötigen, listen wir die Tabelle Person auf, müssen aber dann vom Attribut
Vorgesetzter nochmals auf die Tabelle Person referenzieren, um nun auch den
Namen des Vorgesetzten zu erhalten.
Anhand eines Equi-Joins verknüpfen wir die Tabelle Person zwei mal. Für jede
Verknüpfung legen wir einen eigenen Aliasnamen (Mitarb, Vorges) fest, um die
Tabellen in der Abfrage besser unterscheiden zu können (beachte die FROM-Klausel
des Select-Statements):
SELECT Mitarb.PersonalNr, Mitarb.Vorname, Mitarb.Zuname,
Mitarb.[Abteilung-ID], Mitarb.Tätigkeit, Mitarb.Vorgesetzter, Vorges.Zuname
FROM Person AS Mitarb , Person AS Vorges
WHERE Mitarb.Vorgesetzter = Vorges.PersonalNr;
Als Ergebnis erhalten wir (auszugsweise):
Seite 62/88
11.09.2005 10:18
Datenbanksysteme
Obwohl wir 44 Mitarbeiter in der Firma haben, erscheinen hier nur 39. Jene
Mitarbeiter, die keinen Chef haben, werden nicht aufgelistet. Ein Outer Join hilft uns
auch hier:
SELECT Mitarb.PersonalNr, Mitarb.Vorname, Mitarb.Zuname, Mitarb.[Abteilung-ID],
Mitarb.Tätigkeit, Mitarb.Vorgesetzter, Vorges.Zuname
FROM Person AS Mitarb LEFT JOIN Person AS Vorges
ON Mitarb.Vorgesetzter = Vorges.PersonalNr;
Nun sehen wir auch, wo die ‚fehlenden’ Mitarbeiter verblieben sind:
2) Welche Mitarbeiter verdienen mehr als ihr unmittelbarer Vorgesetzter? Sortiere die
Ausgabe absteigend nach dem Differenzbetrag der Gehälter.
11.09.2005 10:18
Seite 63/88
Datenbanksysteme
SELECT Mitarb.Zuname, Mitarb.Gehalt, Vorges.Zuname, Vorges.Gehalt
FROM Person AS Mitarb LEFT JOIN Person AS Vorges
ON Mitarb.Vorgesetzter = Vorges.PersonalNr
WHERE Mitarb.Gehalt > Vorges.Gehalt
ORDER BY Mitarb.Gehalt - Vorges.Gehalt DESC;
3) Wer verdient mehr oder gleich viel wie Nico Erlacher?
Diese Abfrage könnte natürlich in Stufen erfolgen:
a) Was verdient Nico Erlacher?
b) SELECT-Statement mit dem entsprechenden WHERE-Kriterium.
In einer Abfrage werden wir jedoch wie folgt vorgehen:
SELECT Person.Gehalt, Person.Vorname, Person.Zuname
FROM Person, Person AS Nico
WHERE Person.Gehalt >= nico.gehalt AND Nico.Zuname="erlacher"
ORDER BY Person.gehalt;
Auch hier wurde ein Self-Join verwendet, der Aliasname der zweiten Tabelle ist Nico.
Seite 64/88
11.09.2005 10:18
Datenbanksysteme
4) Welche Personen in unserer Firma sind am selben Tag geboren?
Hier verwenden wir einen Self-Join auf Person, der als Equi-Join ausgeprägt ist.
Die beiden Auspägungen von Person nennen wir z1 bzw. z2.
SELECT DISTINCT z1.Zuname, z1.Vorname, z1.PersonalNr, z1.Geburtsdatum
FROM Person AS z1 INNER JOIN Person AS z2
ON z1.Geburtsdatum = z2.Geburtsdatum
WHERE
z1.PersonalNr <> z2.personalnr AND
z2.Geburtsdatum = z1.geburtsdatum;
Warum ist in der WHERE-Klausel die zusätzliche Bedingung z1.PersonalNr <>
z2.personalnr notwendig? Jeder Mensch hat schliesslich am selben Tag Geburtstag
wie er selbst – wir würden ohne dieser Zusatzbedingung alle Mitarbeiter erhalten.
Warum ist die DISTINCT-Klausel hier erforderlich? Bei unseren Drillingen würde
sonst ein kartesisches Produkt gebildet werden.
5) Welche Mitarbeiter im ‚SHOP’ verdienen mehr als der Durchschnitt (Mittelwert)
ihrer Kollegen in der eigenen Abteilung?
Um den Standort ‚SHOP’ einzubeziehen, ist die Tabelle Abteilung notwendig. Das
Gehalt der einzelnen Mitarbeiter erhalten wir aus der Tabelle Person.
Für den Mittelwert je Abteilung bilden wir einen Self-Join auf Person (Tabelle mw).
SELECT mw.[Abteilung-ID], Avg(mw.Gehalt) AS [Mittelwert von Gehalt],
Person.Gehalt, Person.Zuname, Person.Vorname, Abteilung.Standort
FROM Person AS mw, Person INNER JOIN Abteilung ON Person.[Abteilung-ID] =
Abteilung.[Abteilung-ID]
WHERE Standort = "shop"
GROUP BY mw.[Abteilung-ID], Person.[Abteilung-ID], Person.Gehalt,
Person.Zuname, Person.Vorname, Abteilung.Standort
HAVING Avg(mw.Gehalt) < [person].[gehalt] AND
Person.[Abteilung-ID]=[mw].[abteilung-id];
Erläuterungen:
11.09.2005 10:18
Seite 65/88
Datenbanksysteme
FROM-Klausel:
Person wird einerseits als Equi-Join auf Abteilung abgebildet, wobei die
Abteilungsnummer in der Personentabelle mit jener aus der Abteilungstabelle
übereinstimmen muss.
Für den Mittelwert der Gehälter wird der bereits oben erwähnte Self-Join mit dem
Tabellenalias mw gebildet.
Die WHERE-Bedingung schränkt die Ergebnismenge ein. Ohne dieser würde die
Auskunft über sämtliche Mitarbeiter der Firma laufen. Somit kommen nur die
Abteilungen 2 und 3 in die Ergebnistabelle.
SELECT: Die Abteilung, das Durchschnittsgehalt der Abteilung, das Gehalt der
slektirtern Person, Name und Standort sind auf der Ergebnisrelation auszugeben.
GROUP BY:
Eine Gruppierung hat auf die Abteilung sowie auf alle zu der Person auszugebenden
Kriterien zu erfolgen.
HAVING:
Der Mittelwert der Gehälter einer Abteilung hat kleiner zu sein als das Gehalt des
einzelnen Mitarbeiters.
Die letzte Bedingung in HAVING schränkt insoferne ein, dass nur Gehälter und deren
Mittelwerte zu vergleichen sind, wenn der Mitarbeiter auch in dieser Abteilung
arbeitet.
9.3.2.17.5.
•
•
•
•
Joins - Zusammenfassung
Joins können beliebig viele Tabellen umfassen
In der SELECT-Klausel können Spalten aus mehreren Tabellen angegeben
werden. Wenn die Bezeichnung nicht eindeutig ist (z.B. weil ein Attribut mit
dem selben Namen in mehreren Tabellen vorkommt), ist eine Qualifizierung
des Attributs in der Form tabellenname.attribut erforderlich.
Das JOIN-Kriterium wird beim EQUI-Join in der WHERE-Klausel angegeben,
beim OUTER-Join in der ON-Bedingung.
Das JOIN-Kriterium für zwei Tabellen kann auch mehrere Spalten umfassen.
Seite 66/88
11.09.2005 10:18
Datenbanksysteme
•
•
•
•
•
•
•
Als Verbindungsmerkmal zwischen zwei Tabellen sind entsprechende
Attribute zu selektieren. Andernfalls kommt es zur Bildung des kartesischen
Produkts zweier Tabellen.
Equi-Joins (hier wird als Join-Kriterium das „=“-Zeichen verwendet)
Non Equi-Joins (Join-Kriterium ist nicht das „=“-Zeichen)
Self-Join (Verknüpfung einer Tabelle mit sich selbst)
Outer-Join (auch Tabellenwerte, die in einer anderen Tabelle keine
Entsprechung haben, werden angezeigt und ggf. mit NULL-Werten aufgefüllt)
Bei n Tabellen sind im Normalfall n-1 Bedingungen erforderlich, um die
Tabellen miteinander zu verknüpfen
Ein Join kann immer durch eine Unterabfrage (siehe Kapitel 9.3.2.18)
ausgedrückt werden. Eine Unterabfrage kann oft, aber nicht immer durch
einen Join ausgedrückt werden.
9.3.2.18.
Unterabfragen (Subqueries)
Bei Unterabfragen wird die Ergebnismenge einer SELECT-Abfrage (die innere
Abfrage) an eine übergeordnete Abfrage (äußere Abfrage) zur weiteren Auswertung
übergeben. Damit lässt sich eine praktisch beliebige Schachtelungstiefe erreichen.
Betrachten wir dazu nochmals das Beispiel aus dem Kapitel ‚Self-Join’ (siehe Seite
64):
1) Wer verdient mehr oder gleich viel wie Nico Erlacher?
Wie bereits erwähnt, könnte die Abfrage mehrstufig erfolgen:
SELECT Gehalt
FROM PERSON
WHERE Zuname="erlacher";
Wir erhalten als Ergebnis den Wert 37.000€.
Im zweiten Schritt könnte das SELECT-Statement nun so lauten:
SELECT ZUNAME, VORNAME
FROM PERSON
WHERE GEHALT >= 37000;
Es ist leicht einzusehen, dass dieser Weg nicht besonders gelungen ist und in
manchen Fällen (z.B. bei vorgefertigten Abfragen) nicht zum Ziel führt.
Wir fassen daher beide obigen Statements zu einem einzigen wie folgt zusammen:
SELECT ZUNAME, VORNAME, GEHALT
FROM PERSON
WHERE GEHALT >= (
SELECT GEHALT
FROM PERSON
WHERE ZUNAME = 'Erlacher');
11.09.2005 10:18
Seite 67/88
Datenbanksysteme
Die in Klammern gesetzte ‚innere’ SELECT-Anweisung wird zuerst ausgeführt und
liefert als Ergebnismenge in unserem Beispiel lediglich einen einzigen Wert zurück –
nämlich 37000€.
Diese Menge wird nun an die darüber liegende Anweisung weitergegeben, sodass
diese wie folgt ausgeführt wird:
SELECT ZUNAME, VORNAME, GEHALT
FROM PERSON
WHERE GEHALT >= (37000);
Das Ergebnis der zusammengesetzten Abfrage:
2) Welche Mitarbeiter sind im ‘Shop’ beschäftigt?
Die innere Abfrage lautet hier:
SELECT Abteilung.[Abteilung-ID]
FROM Abteilung
WHERE Abteilung.Standort="shop";
Hier liefert die Abfrage mehr als einen Wert: Wir erhalten als Ergebnismenge für die
Abteilungen: {2,3,6}
Dies sind jene Abteilungen, die den Standort ‚Shop’ haben.
Wollen wir nun die in diesen Abteilungen beschäftigten Mitarbeiter listen, würden wir
schreiben:
SELECT Person.Zuname, Person.[Abteilung-ID]
FROM Person
WHERE person.[Abteilung-ID] IN (2,3,6);
Die entsprechende Unterabfrage lautet somit:
SELECT Person.Zuname, Person.[Abteilung-ID]
FROM Person
WHERE person.[Abteilung-ID] IN (
SELECT Abteilung.[Abteilung-ID]
Seite 68/88
11.09.2005 10:18
Datenbanksysteme
FROM Abteilung
WHERE Abteilung.Standort="shop");
3) Aufzulisten sind Name, Geburtsdatum und Tätigkeit von Mitarbeitern der Abteilung
Nr. 2, die die gleiche Tätigkeit ausüben wie ein beliebiger Mitarbeiter der Abteilung 3.
Die ‚innere’ Abfrage lautet:
SELECT DISTINCT Tätigkeit
FROM Person
WHERE [Abteilung-ID] = 3;
und liefert: {Hotline, Leiter, Verkäufer}.
Die gesamte Abfrage:
SELECT Zuname, Geburtsdatum, Tätigkeit, [abteilung-ID]
FROM Person
WHERE [Abteilung-ID] = 2 AND Tätigkeit IN
(SELECT DISTINCT Tätigkeit
FROM Person
WHERE [Abteilung-ID] = 3);
4) Welche Drucker stehen am Standort ‚Zentrale’?
Diese Abfrage ist in ähnlicher Form bereits im Kapitel ‚Joins’ behandelt worden – nun
wollen wir sie mit einer geschachtelten Unterabfrage lösen:
Ein erster iterativer Ansatz, der die Tabellen Abteilung, Person und Computer
einbezieht, sieht so aus:
select [inv-nr]
from computer
where person_ID
in
(select personalNr
from person
where person.[abteilung-id]
11.09.2005 10:18
Seite 69/88
Datenbanksysteme
in
(select abteilung.[abteilung-id]
from abteilung
where standort = 'zentrale'));
Als Zwischenergebnis erhalten wir die Inventarnummer des Computers, der dem
Mitarbeiter gehört, der in einer Abteilung mit dem Standort ‚Shop’ arbeitet:
Nehmen wir nun auch noch die Tabellen Comp_Druck und Drucker hinzu, erhalten
wir:
select invnr, hersteller, bezeichnung
from drucker
where invnr
in
(select druck
from comp_druck
where comp
in
(select [inv-nr]
from computer
where person_ID
in
(select personalNr
from person
where person.[abteilung-id]
in
(select abteilung.[abteilung-id]
from abteilung
where standort = 'zentrale'
)
)
)
);
(Die Klammersetzung wurde in diesem Beispiel nur wegen der besseren
Übersichtlichkeit gewählt.)
und führt zum gewünschten Endergebnis:
Seite 70/88
11.09.2005 10:18
Datenbanksysteme
5) Welche Mitarbeiter arbeiten in der Zentrale oder verdienen mehr als ihr direkter
Vorgesetzter?
Die Mitarbeiter in der Zentrale erhalten wir über:
SELECT Person.PersonalNr
FROM Person
WHERE Person.[Abteilung-ID] IN (SELECT Abteilung.[Abteilung-ID]
FROM Abteilung
WHERE Standort="zentrale");
Als Zwischenergebnis die Menge der zutreffenden Personalnummern:
{2, 10, 20, 21, 27, 34, 35, 37, 39, 43}
Jene Mitarbeiter, die mehr als ihr direkter Vorgesetzter verdienen, bilden wir diesmal
in Form einer Unterabfrage ab (siehe dazu auch das Beispiel auf Seite 63, wo wir
einen Left-Join verwendet haben):
SELECT Person.PersonalNr
FROM Person, Person AS Vorges
WHERE
Person.Gehalt > [vorges].[gehalt] AND
Person.Vorgesetzter = [vorges].[personalnr];
Die Ergebnismenge obiger Abfrage: {7, 14, 18, 22, 23, 25, 32, 39}.
Verpacken wir die beiden obigen SELECT-Statements nun in eines und verknüpfen
mit ‚oder’, erhalten wir:
SELECT Person.PersonalNr, Person.Vorname, Person.Zuname
FROM Person, Person AS Vorges
WHERE
(Person.[Abteilung-ID] IN (SELECT Abteilung.[Abteilung-ID]
FROM Abteilung
WHERE Standort="zentrale"
)
)
OR
(Person.Gehalt > [Vorges].[Gehalt] AND
Person.Vorgesetzter = [Vorges].[Personalnr]
);
11.09.2005 10:18
Seite 71/88
Datenbanksysteme
9.3.2.18.1.
•
•
•
•
•
•
•
Unterabfragen - Zusammenfassung
Bei einer Unterabfrage wird eine (innere) SELECT-Anweisung in die WHEREoder HAVING-Klausel einer anderen (äußeren) SELECT-Anweisung
‚eingebaut’.
Die innere (untergeordnete) Unterabfrage wird zuerst ausgeführt und das
Ergebnis in Form einer Mengenoperation in das übergeordnete SELECT
eingesetzt.
Die innere Abfrage kann sich auch auf Informationen aus einer anderen
Tabelle beziehen
Die von der inneren Abfrage ausgegebenen Attribute müssen hinsichtlich
Anzahl und Datentyp mit jenen übereinstimmen, die in der nächst äußeren
Abfrage verwendet werden.
Ist von der inneren Abfrage eine Menge von Werten zu erwarten, ist das
Schlüsselwort ‚IN’ zu verwenden.
Unterabfragen können praktisch beliebig tief verschachtelt werden (meistens
bis zu 255 Ebenen).
In einer Unterabfrage darf keine ORDER BY-Klausel verwendet werden (dies
hätte auch nicht viel Sinn, da die Unterabfrage nur eine Menge zurückliefert;
Mengen sind jedoch definitionsgemäß unsortiert).
9.3.2.19.
Korrelierte (synchronisierte) Unterabfragen
Eine Unterabfrage kann auch für wiederholte Ausführung aufgebaut sein. In diesem
Fall wird sie einmal je Zeile der übergeordneten Abfrage ausgeführt.
Synchronisierte Unterabfragen werden eingesetzt, wenn eine verschachtelte
Unterabfrage für jede Zeile, die von der äußeren Abfrage selektiert wird, ein anderes
Ergebnis bzw. eine andere Ergebnismenge ausgeben muss.
Seite 72/88
11.09.2005 10:18
Datenbanksysteme
Die Unterabfrage hängt somit vom Ablauf der äußeren Abfrage ab.
Eine korrelierte Unterabfrage ist daran erkennbar, dass in der WHERE-Klausel der
Unterabfrage auf eine Tabelle zugegriffen wird, welche auch in der äußeren Abfrage
angesprochen wird.
Der aktuelle Wert der relevanten Spalte in der äußeren SELECT-Anweisung
1) Welche Mitarbeiter verdienen mehr als der Durchschnitt in ihrer Abteilung?
Das DBMS muss also, je nachdem, welche Mitarbeiter in der äußeren Abfrage
gerade ‚an der Reihe’ ist, den Abteilungsdurchschnitt ermitteln, und diesen mit dem
Gehalt des Mitarbeiters vergleichen.
SELECT Zuname, Gehalt, [Abteilung-ID]
FROM Person
WHERE Gehalt > (SELECT AVG(Gehalt)
FROM Person AS Mittelw
WHERE Person.[Abteilung-Id] = Mittelw.[Abteilung-ID]
);
9.3.2.20.
EXISTS
SDO54
Liste alle Mitarbeiter, die jemandem anderen vorgesetzt sind.
SELECT Zuname
FROM Person AS Vorges
WHERE EXISTS
11.09.2005 10:18
Seite 73/88
Datenbanksysteme
(SELECT * FROM Person WHERE Vorgesetzter = Vorges.Personalnr);
9.3.2.21.
ANY und SOME
SDO55
9.3.2.22.
ALL
SDO56
Zu diesem Zweck verwenden wir eine neue Musterdatenbank ‚EDV-Inventar’,
anhand der dies dargestellt werden kann.
9.3.2.22.1.
Vorstellung der Datenbankstruktur „EDV-Inventar“
Im vorliegenden Beispiel besteht die Anforderung, die EDV-Landschaft einer kleinen
Firma zu inventarisieren und in einer Datenbank zu dokumentieren. Des weiteren soll
eine kleine Mitarbeiterverwaltung enthalten sein.
Seite 74/88
11.09.2005 10:18
Datenbanksysteme
Es gibt Workstations (Tabelle ‚Computer’), die normalerweise von einer Person
benutzt werden. Es kann jedoch auch vorkommen, dass ausrangierte Computer
keinem Mitarbeiter zugeordnet sind.
Ein Monitor ist i.a. einer oder auch keiner Workstation zugeordnet.
Von den Workstations ist eine Inventarnummer (sehr gut als primary key
verwendbar), ein Workstationname, die darauf arbeitende Person, der Standort, das
Betriebssystem und der Hauptspeicher zu erfassen.
Beim Monitor interessieren uns ebenfalls die Inventarnummer, die Bauart (Röhre ‚R’
oder Flachbildschirm ‚F’), Hersteller, Type und Bildschirmdiagonale sowie ggf. der
Computer, an dem dieser Monitor angeschlossen ist.
Von den Mitarbeitern soll in diesem Datenmodell die Personalnummer sowie der
Name, das Geburtsdatum, die Abteilung, die Tätigkeit und das Gehalt sowie den
jeweiligen Vorgesetzten gespeichert werden. Abteilungen haben neben ihrer
eindeutigen Nummer eine Bezeichnung, sie befinden sich an einem bestimmten
Standort und haben ein vorgegebenes Budget.
Bei den Druckern ist die Sache etwas komplizierter:
An einem Computer können mehrere Drucker angeschlossen werden, ein Drucker
kann jedoch mittels Druckerumschaltboxen auch mehrere Computer servicieren.
Trotzdem gibt es Drucker, die nirgendwo angeschlossen sind (z.b. defekte), und
manche Computer haben auch keinen Drucker.
Attribute der einzelnen Drucker sind die Inventarnummer, Hersteller, Bezeichnung,
Bauart und ein Kennzeichen (J/N), ob es sich um einen Farbdrucker handelt.
Nach der Analyse der Problemstellung und Normalisierung ergibt sich somit
folgendes Relationenmodell:
11.09.2005 10:18
Seite 75/88
Datenbanksysteme
Die Beziehungen Computer <> Monitor sowie Computer <> Person sind so
aufgebaut, dass keine referentielle Integrität gefordert ist. Das Feld Comp_ID in
Monitor bzw. Person_ID in Computer kann daher auch leer bleiben.
Zwischen Computer und Drucker wurde eine Beziehungsrelation Comp_Druck
eingeschoben, welche die m:n-Beziehung zwischen den beiden Entitäten Computer
und Drucker jeweils auf 1:n-Entitäten abbildet.
Sowohl zwischen Computer und Comp_Druck als auch zwischen Comp_Druck und
Drucker wird referentielle Integrität erzwungen. In Comp_Druck kann daher kein nicht
existierender Computer bzw. Drucker eingetragen werden.
Letztgenannte referentielle Integritäten wurden auch als lösch- und aktualisierbar
definiert. Würde sich die Inv-Nr eines Computers ändern, wird diese Änderung auch
in Comp_Druck nachvollzogen (also entsprechende geändert werden). Wird ein
Computer in der Relation ‚Computer’ gelöscht, werden die entsprechenden Tupel
auch in Comp_Druck gelöscht.
Analoges gilt zwischen den Relationen Drucker und Comp_druck.
Die meisten Mitarbeiter des Unternehmens haben keinen Computer, da sie im
Außendienst tätig sind. Weiters gibt es Mitarbeiter, die keiner Abteilung zugeordnet
sind (z.B. Karenz), und es gibt Abteilungen, welche keine Mitarbeiter (mehr) haben.
Die Beziehungen Computer  Person sowie Person  Abteilung wurde daher
ohne Durchsetzung der referentiellen Integrität modelliert.
Für die folgenden Abfragen über mehrere Tabellen wird die Datenbank wie folgt
befüllt:
Seite 76/88
11.09.2005 10:18
Datenbanksysteme
In unserem Beispiel haben wir es also mit 5 Computern zu tun, einer davon ist nicht
in Verwendung. Es gibt viele Mitarbeiter, jedoch nur 4 davon haben einen PC
zugewiesen.
Von den 3 Druckern wird einer von 2 Computern angesprochen (#1), einer wird von 1
Computer angesprochen (#2), und Drucker Nr. 3 ist stillgelegt.
Wir haben schließlich 5 Monitore, Nr. 5 ist jedoch nicht in Verwendung.
ICH BIN MIR NICHT SICHER; OB DIESES BEISPIEL NOCH WEITER
ENTWICKELT WERDEN SOLLTE UND WENN JA, WOHIN.
11.09.2005 10:18
Seite 77/88
Datenbanksysteme
10. Weitere elementare Datenbankkonzepte
10.1. Transaktionen
Bei Datenbanken, die von mehreren Benutzern gleichzeitig benutzt werden, muss
der Zugriff und die (fast) gleichzeitige Änderung von Daten berücksichtigt werden.
Transaktionen werden immer dann eingesetzt, wenn in einer Datenbank mehrere
Änderungen gleichzeitig durchzuführen sind, welche zu einer logischen Einheit
zusammengefasst sind. Würde nur ein Teil dieser Änderungen durchgeführt, wäre
die Konsistenz der gesamten Datenbank gefährdet. Erst dann, wenn alle
Einzelschritte erfolgreich umgesetzt worden sind, ist die Datenbank wieder in einem
konsistenten Zustand.
Mit dem Begriff der Transaktion ist das ACID-Konzept stark verbunden:
ACID:
Atomicity – Atomarität:
Änderungstransaktionen werden nur entweder ganz oder gar nicht wirksam
Consistency – Konsistenzsicherung:
Eine Transaktion führt ein DBMS von einem konsistenten Datenbankzustand in einen
anderen konsistenten Datenbankzustand über.
Änderungen finden daher nur so statt, dass das System konsistent bleibt.
Die Forderung nach Konsistenz steht in starkem Widerspruch zum
Mehrbenutzerbetrieb (‚Concurrency’). Konsistenz bedeutet in seiner Extremform,
dass eine Datenbank zu einem Zeitpunkt nur einem einzigen Benutzer zur Verfügung
steht.
Lösung: Zu ändernde Daten werden dem Zugriff durch andere Benutzern während
einer Transaktion entzogen.
Probleme:
- Wie weit geht diese Sperre (gesamte Datenbank – Relation – ausgewählte Tupel,
ausgewählte Spalten, bis auf die einzelne Feldebene)? Je differenzierter, desto
aufwändiger die Verwaltung und der damit verbundene Datenbank-Overhead.
- Die erforderliche Sperre ergibt sich nur aus der logischen Betrachtung des
Sachverhalts. Wie soll das DB-System wissen, was nun wirklich zu sperren ist?
Lösungsansätze:
- Kompromisse
- Optimistische Methoden: „Die Wahrscheinlichkeit, dass Konflikte auftreten ist relativ
gering, daher werden eher weniger Sperren vorgenommen, erst im Konfliktfall
werden Maßnahmen zur Konsistenzsicherung ergriffen. Worst case: Ein Benutzer
muss eine Transaktion nochmals durchführen...“.
Isolation – Isoliertheit:
Nebenläufige (d.h. zeitgleich ablaufende) Transaktionen (z.B. von anderen
Benutzern) beeinflussen sich nicht wechselseitig: Zwischenzustände einer
Transaktion bleiben anderen verborgen.
Seite 78/88
11.09.2005 10:18
Datenbanksysteme
Erreicht wird die Isolation durch die Serialisierbarkeit von Transaktionen: Mehrere
gleichzeitig ablaufende Transaktionen bewirken dasselbe Resultat, als wären sie in
zeitlicher Abfolge hintereinander abgelaufen.
Durability – Dauerhaftigkeit, Beständigkeit:
Wenn eine Transaktion erfolgreich ausgeführt wurde (Commit) und der Folgezustand
konsistent ist, ist diese Transaktion auch dauerhaft (und durch Recovery
wiederherstellbar).
Beispiele für transaktionsorientierte Prozesse:
a) Geldbehebung am Bankomat:
Auch wenn der Geldautomat nach der Auszahlung abstürzt, taucht die Auszahlung
am Kontoauszug auf.
b) Banküberweisung: Buchung eines bestimmten Geldbetrages von Konto A
(Verringerung des Kontostandes) auf Konto B (Erhöhung des Kontostandes).
Konsistenz: Nach der Änderung des ersten Kontostandes ist die Datenbank in einem
inkonsistenten Zustand.
Atomarität: Die beiden Buchungen werden komplett oder gar nicht ausgeführt.
Isoliertheit: Wenn während unserer Transaktion eine andere Transaktion auf eines
der beiden Konten zugreift, muss am Ende dasselbe Ergebnis in der Datenbank
gespeichert sein, wie wenn diese beiden Transaktionen hintereinander abgelaufen
wären.
Dauerhaftigkeit:
Nach der zweiten Buchung werden die neuen Kontostände gespeichert, die
Änderungen können nicht mehr verloren gehen.
Achtung: DB-Transaktionen schützen nicht vor der Notwendigkeit regelmäßiger
Datensicherungen! Backups sind immer dann erforderlich, wenn eine Datenbank
physisch zerstört wurde (z.B. Betriebssystemfehler, Hardwarefehler, Stromausfall
etc.).
Das Konsistenthalten von Datenbanken ist insbesondere bei (regional) verteilten
Datenbanken eine technische Herausforderung.
Das Wiederaufsetzen von Datenbanken bedient sich zweier Methoden:
After Image-Protokolle:
Von einem konsistenten Zustand ausgehen, werden alle Transaktionen in einem
Logfile mitgeschrieben. Nach einem Systemabsturz wird das Backup (Image) wieder
hergestellt und nur noch die einzelnen Transaktionen aus dem After Image-Protokoll
nachverarbeitet.
Before Image-Protokolle:
Wenn eine Datenbank inkonsistent wird, werden dieses Logdaten verwendet, um die
Datenbank auf den letzten konsistenten Zustand ‚zurückzurollen’.
Transaktionen in SQL:
Start:
Erfolgreiche Beendigung:
Zurücksetzen der Transaktion:
11.09.2005 10:18
START TRANSACTION
COMMIT
ROLLBACK
Seite 79/88
Datenbanksysteme
Weiter erwähnenswert sind hierbei (speziell bei längeren Transaktionen) sog.
Savepoints. Es werden gesicherte Zwischenstati der Datenbank gebildet, um bei
einem Systemabsturz möglichst wenige Schritte nochmals durchführen zu müssen.
Problem hierbei: Wann besteht ein gesicherter Zwischenstatus?
Bei Transaktionen im programmierten Bereich kann die Programmlogik entscheiden,
wie eine Transaktion in der DB abgeschlossen werden soll:
COMMIT:
Alle innerhalb der Transaktion durchgeführten Änderungen sollen in der Datenbank
wirksam werden
ROLLBACK:
Die Transaktion muss zur Gänze zurückgerollt werden und hat somit nicht
stattgefunden
10.1.1. Sperrmechanismen
Zwei Typen von Sperren (‘locks’) sind üblich:
Exclusive locks (X-lock)
Shared locks (S-lock)
S-lock erlaubt die gleichzeitige Bearbeitung von Objekten durch eine andere
Transaktion, die ebenfalls ein s-lock benötigt.
X-lock entzieht das Objekt jeglichem anderen Zugriff.
Beispiele:
- Transaktion A hat s-lock:
Transaktion B kann ebenfalls s-lock setzen und den Zugriff ausführen
B kann kein x-lock mehr setzen.
- A hat x-lock gesetzt:
B kann weder s-lock noch x-lock setzen.
10.1.2. Isolation-Level
Damit wird dem DB-System bei Transaktionen mitgeteilt, welche Auswirkungen
nebenläufige Transaktionen haben können bzw. welche ausgeschlossen sind.
Wir kennen folgende Isolation Levels:
READ UNCOMMITTED
Transaktion A kann Objekte (Tupel) lesen, die von einer zweiten Transaktion (B)
geändert werden, obwohl diese noch nicht mit COMMIT festgeschrieben wurden.
Wenn B mit ROLLBACK die Änderungen zurücknimmt, hat A ebenfalls falsche
Informationen erhalten. Dieser Isolation level wird auch als ‚dirty read’ bezeichnet.
Anwendungsbereiche: Aggregatbildung von Daten aus der Datenbank, z.B.
statistische Auswertungen, Management Information System, Data Warehousing.
READ COMMITTED
Dirty read ist hier ausgeschlossen. Transaktion A sieht nur jene Daten, die von B
auch mit COMMIT in die DB eingestellt wurden. Bei diesem Isolation level ist es
jedoch möglich, dass innerhalb einer Transaktion A eine Abfrage, zu zwei
Seite 80/88
11.09.2005 10:18
Datenbanksysteme
verschiedenen Zeitpunkten ausgeführt, zu unterschiedlichen Ergebnissen führen
kann.
REPEATABLE READ
Das obige Problem ist bei diesem Isolation level ausgeschlossen. Hier kann es
jedoch zu einem neuen Problem kommen: A selektiert gewisse Tupel, B fügt ein
Tupel ein, und eine zweite Selektion in A ‚sieht’ nun auch dieses Tupel. Dieses
Phänomen bezeichnet man auch als „phantom read“.
SERIALIZABLE
Diese Anforderung ist die strengste bezüglich Isolation Levels. Nur sie gewährleistet
die volle Erfüllung aller Eigenschaften einer Transaktion, die im ACID-Prinzip
beschrieben sind.
Tabellarisch dargestellt ergeben sich somit folgende Zusammenhänge zwischen dem
Isolation level und den möglichen Verstößen gegen das ACID-Prinzip:
Isolation level
Dirty read
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
J
N
N
N
Nonrepeatable
read
J
J
N
N
Phantom read
J
J
J
N
10.1.3. Deadlocks
Wenn mehrere Transaktionen wechselseitig auf die Freigabe von Ressourcen
warten, spricht man von einer Verklemmung („deadlock“).
Beispiel:
Transaktion A
Transaktion B
Liest einen Satz aus der Relation Kunde
und setzt einen s-lock
Liest den gleichen Satz aus der Relation
Kunde und setzt ebenfalls einen s-lock
Ändert den Inhalt des Tupels und will die
Änderung zurückschreiben, will also xlock setzen – dies ist nicht möglich, da B
bereits ein s-lock hat.
Prozess muss warten....
Auch B ändert den Satz und will ihn
zurückschreiben – x-lock ist wegen A
erfolglos
Prozess muss warten...
.......
.......
Behandlung von Deadlocks:
Ein DBMS muss deadlocks automatisch erkennen und so weit beheben können,
dass zumindest eine der beteiligten Transaktionen erfolgreich zu Ende geführt
werden kann. Die benachteiligte Transaktion wird mittels Rollback eliminiert und eine
entsprechende Meldung an den Benutzer bzw. das Anwendungsprogramm
gesendet. Im Idealfall wird jene Transaktion abgebrochen, die am wenigsten
fortgeschritten war.
11.09.2005 10:18
Seite 81/88
Datenbanksysteme
10.2. Zugriffsrechte und Rollen
Im Mehrbenutzerbetrieb sind die Zugriffsrechte auf eine Datenbank i.a. durch eine
Benutzerverwaltung geregelt.
Im betrieblichen Umfeld ist es erforderlich, den Zugriff von Benutzern auf die
Datenbank in feinen Stufen („hohe Granularität“) zu definieren.
Grundprinzip: Niemand hat in einem DBMS Zugriff auf Daten, außer dies ist explizit
erlaubt (Privilegien, „privileges“). Benutzer haben daher in einer entsprechenden
Anmeldeprozedur ihre Berechtigungen nachzuweisen.
Spezielle Benutzer sind die DB-Administratoren, welche z.B.
auf alle DB-Objekte zugreifen und diese ändern kann
anderen Benutzern Privilegien erteilen und widerrufen kann
Änderungen an der physikalischen Datenorganisation vornehmen kann
(Partitionierung, Indizierung, Verlegung von Objekten auf andere
Destinationen bei verteilten Datenbanken)
Zugriffssynonyme vergeben kann
Sicherungen der Datenbank durchführen und bestimmte Objekte exportieren
kann.
10.2.1. Rollen und Gruppen
Bei größeren DBMS kann die Verwaltung der Benutzerrechte ein sehr aufwändiger
Prozess sein. Im praktischen Umfeld ist es sehr häufig erforderlich, einer Vielzahl
von Benutzern (z.B. die Mitarbeiter einer ganzen Abteilung) identische Privilegien auf
das DBMS zu gewährleisten.
Im DBMS werden die grundlegenden Objektklassen Benutzer und Privileg
unterschieden.
Ein Benutzer identifiziert eine physische Person, welche bestimmte Tätigkeiten in
einem DBMS ausführen kann.
Ein Privileg stellt eine bestimmte Funktionalität in einem DBMS dar. Mögliche
Privilegien sind beispielsweise (nach aufsteigender Granularität sortiert):
Vollständiger Zugriff auf das gesamte DBMS
Vollständiger Zugriff auf alle Relationen der Marketingabteilung
Vollständiger Zugriff auf die Relation Kunde
Lesezugriff auf ‚Kunde’
Lesezugriff auf Kunde, jedoch ohne der Spalte ‚Umsatz’
Lesezugriff auf Kunden im eigenen Verantwortungsbereich (z.B:
Sachbearbeiter in einer Filiale des Unternehmens)
Benutzer
Privileg
Aus Vereinfachungsgründen und aus Gründen der Übersichtlichkeit wird zwischen
die beiden Objekttypen eine weitere Schicht eingeschoben: Rollen bzw. Gruppen.
Seite 82/88
11.09.2005 10:18
Datenbanksysteme
Das dahinter stehende Prinzip ist das gleiche, die Konzeption ist aber
unterschiedlich.
Eine Rolle fasst mehrere Privilegien zusammen, während eine Gruppe mehrere
Benutzer subsummiert.
In der Praxis häufiger im Einsatz ist das Rollenkonzept (z.B. bei Oracle).
Es ergibt sich somit folgendes Systemkonzept aus der Sicht der
Zugriffsmechanismen:
Benutzer
Rolle bzw.
Gruppe
Privileg
10.3. Datenbankprozeduren
Um Benutzern mit beschränkten Rechten die Möglichkeit zu geben, bestimmte
Änderungen am DBMS vorzunehmen (z.B. gekoppelte Datenänderungen), werden
Datenbankprozeduren (sog. „stored procedures“) verwendet.
Der Benutzer kann damit jene Aufgaben erledigen, die für seinen Tätigkeitsbereich
erforderlich sind, er kann aber die zugrundliegenden Daten nicht direkt manipulieren
(Beispiel: Buchungen und Gegenbuchung auf Konten).
Für DB-Prozeduren gibt es keine allgemeingültige Norm, diese sind von der
jeweiligen Implementierung des DBMS abhängig, elementare Konzepte sind jedoch
in SQL/PSM (Persistent Stored Modules) definiert worden.
Sehr häufig erfolgt mittels den in SQL/PSM beschriebenen Konzepten auch ein
Zugriff auf ein DBMS mittels imperativer Programmiersprachen (Cobol, C, C++,
Pascal, Java).
Anwendungsmöglichkeiten von Datenbankprozeduren
Unterstützung der Datenbankkonsistenz
Komplexe, aus mehreren elementaren Operationen bestehende
Funktionalitäten sind zu implementieren
Effizientere Ausführung komplexer Operationen
Z.B. die Daten werden in Form der Prozedur nur einmal zum Server gesandt,
dort verarbeitet und eine (relativ) kleine Ergebnismenge wird dem Benutzer
wieder aufbereitet.
Erweiterung der Möglichkeiten des relationalen Datenmodells
Die DML von SQL beinhaltet gewisse Einschränkungen, die auf diese Weise
umgangen werden können (z.B. keine Möglichkeit, prozedurale Abläufe zu
formulieren, keine rekursiven Techniken).
10.4. Funktionen
Funktionen in Datenbanken haben einen ähnlichen Aufbau wie Prozeduren.
Sie werden mit Eingabeargumenten befüllt und liefern nur einen einzelnen Wert
zurück.
11.09.2005 10:18
Seite 83/88
Datenbanksysteme
Funktionen können überall dort eingesetzt werden, wo auch Ausdrücke erlaubt sind
(d.h. speziell auch in der SELECT-Klausel).
Funktionen stellen häufig vereinfachte Methoden der Abfrageprogrammierung dar
(z.B. wenn bestimmte gleichbleibende Aufrufe sehr häufig benötigt werden, wird
dafür eine Funktion nur ein einziges Mal erstellt, getestet und ggf. gewartet).
Sie sind jedoch auch dort sehr nützlich, wo nicht-standardisierte Abfrageergebnisse
benötigt werden:
Spezifische Umwandlung von Codes in Elementen der Datenbank in Langtext
Berechnung von betriebsinternen Werten (z.B. komplexe Formeln für die
Ermittlung des Rohaufschlags bestimmter Produkte in einem Unternehmen)
Berechnung von Standardfunktionen (z.B. Ermittlung der Oster-abhängigen
Feiertage eines Kalenderjahres, Ermittlung von innerbetrieblichen Feiertagen
etc.)
Bildung eines Nummerierungssystem nach betriebsintern vorgegebenen
Regeln (z.B. System zur Vergabe von Artikelnummern, Kundennummern etc.)
10.5. Trigger
Während Datenbankprozeduren bewusst vom Benutzer (oder von anderen
Anwendungsprogrammen) aufgerufen werden, sind Trigger an Ereignisse innerhalb
des DBMS gekoppelt und werden bei Eintreten des Ereignisses zwangsläufig
ausgelöst („getriggert“).
Ein Trigger kann – aus Sicht des Ereignisses - folgendermaßen definiert werden:
TRIGGER BEFORE
Der Trigger wird verarbeitet, bevor ein bestimmtes Ereignis
eintritt
TRIGGER INSTEAD OF Die Triggerprozedur wird anstelle des Ereignisses
ausgeführt (z.B. wird anstelle der tatsächlichen Löschung
eines Satzes nur ein Flag gesetzt, dass dieser Satz als
gelöscht zu betrachten ist)
TRIGGER AFTER
Der Trigger wird verarbeitet, nachdem ein bestimmtes
Ereignis eingetreten ist
Bezüglich der Art der angeforderten Funktion lassen sich Trigger setzen auf:
TRIGGER INSERT
TRIGGER UPDATE
TRIGGER DELETE
Beim Einfügen eines neuen Satzes, z.B. Prüfung des
Satzes auf bestimmte Elemente und Nebenbedingungen
Optional: Angabe der Spaltenbezeichnung.
Wenn sich ein Tupel in der/den angegebenen Spalte(n)
ändert, wird der Trigger aktiviert
Beim Löschen eines Tupels
Bezüglich des Mehrfachaufrufs von Triggern kann unterschieden werden:
TRIGGER ROW
Wenn mehrere Zeilen gleichzeitig geändert werden sollen
(z.B. samthafte Änderung aller Preise), wird der Trigger für
jede einzelne Änderung aufgerufen – auch die
entsprechende Aktion basiert auf Zeilenebene.
Seite 84/88
11.09.2005 10:18
Datenbanksysteme
TRIGGER STATEMENT Hier wird dem Trigger der Zustand einer Relation vor und
nach der Änderung zur Verfügung gestellt, die beliebig
ausgewertet werden kann.
10.5.1. Anwendungsmöglichkeiten von Triggern
Konsistenzbedingungen
Bezüglich der Konsistenzbedingungen von DBMS bestehen häufig nur
eingeschränkte Möglichkeiten. Bei Triggern kann in eigenen
Anwendungsprogrammen genau bestimmt werden, welche Konsistenzbedingungen
eine Änderung in der DB zu erfüllen hat.
File Transfer
Wenn das Bankkonto eines Kunden aufgelöst (gelöscht) wird, werden alle
dazugehörigen Daten an die Zentrale der Bank übermittelt.
Notrufsignalisierung
Wenn am Helpdesk eines großen Unternehmens (Telekommunikationsanbieter) in
einem kurzen Zeitabschnitt vermehrt viele Anfragen einlangen (z.B. durch Störung
eines wichtigen Übertragungsbereichs), wird über ein GSM-Modul automatisch ein
diensthabender Techniker herbeigerufen.
Kommerzielle Anwendung
Bei Unterschreitung eines bestimmten (definierten) Mindestbestandes eines Artikels
wird automatisch eine Mail an den Lieferanten des Artikels und auch an den
verantwortlichen Einkäufer im Unternehmen gesandt.
10.6. Das Cursorkonzept
Worin liegen die Unterschiede zwischen dem relationalen Ansatz und der
imperativen Programmierung?
Relational:
Verknüpfung von Relationen, Selektion der erforderlichen Daten, Verknüpfung der
Ergebnisrelation mit anderen Relationen, so lange, bis das gewünschte Ergebnis
vorliegt.
Imperative Programmierung:
Lesen eines Satzes aus einer Datei, bis der gewünschte Satz gefunden ist,
Zwischenergebnisse berechnen, am Dateiende Zwischenergebnisse ausgeben
Dies bewirkt eine Paradigmenkluft, die auch als „impedance mismatch“ bezeichnet
wird.
Natürlich wäre es theoretisch möglich, eine Relation in einem Programm wie eine
sequentielle Datei zu sehen, und diese abzuarbeiten, das Programm müsste jedoch
alle Rahmenbedingungen, die im DBMS gespeichert sind, zusätzlich berücksichtigen
(wie z.B. referentielle Integrität, Schlüsselabhängigkeiten, Wertebereiche, Domänen
etc.).
Hier kommt der sog. „Cursor“ zur Wirkung (nicht zu verwechseln mit der Marke in
11.09.2005 10:18
Seite 85/88
Datenbanksysteme
z.B. Textverarbeitungsprogrammen).
Mittels des Cursors wird ein beliebiges SQL-Statement aufgesetzt, dessen Ergebnis
dem Anwendungsprogramm zur Verfügung steht und dort sequentiell abgearbeitet
werden kann.
Der Cursor stellt somit eine logische Verbindung zum DBMS dar (wie in vielen EDVSystemen innerhalb von Programmen auch mittels logischer Verknüpfungen auf
Dateien zugegriffen wird).
Syntax für Definition eines Cursors:
DECLARE curs CURSOR FOR ...
OPEN curs
FETCH curs
CLOSE curs
Seite 86/88
Hier wird ein logischer Name des Cursors
(curs) mit einem beliebig komplexen SQLStatement vergeben (FOR…)
Die DB-Engine bereitet die Bereitstellung der
vom Cursor angeforderten Daten vor
Der erste (oder nächste, letzte, beliebige) Satz
wird an das aufrufende Programm übergeben
Die DB-Engine wird informiert, dass keine
weitere Anforderung mehr benötigt wird
11.09.2005 10:18
Datenbanksysteme
11. Overheadfolien
EDV-INVENTAR
Computer
InventarNr, Computername, Mitarbeiter, Standort,
Betriebssystem, Hauptspeichergröße (in MB)
Monitore
InventarNr, Bauart (Röhre/TFT), Hersteller, Type,
Bildschirmdiagonale (cm), Computer
Mitarbeiter
Personalnummer, Name, Geburtsdatum,
Abteilung, Vorgesetzter, Gehalt
Abteilung
AbteilungsNr, Bezeichnung, Standort, Budget
Drucker
InventarNr, Hersteller, Bezeichnung, Bauart
(Matrix/Laser/Tinte), Farbdrucker (J/N)
11.09.2005 10:18
Seite 87/88
Datenbanksysteme
12. Referenzen
12.1. Abbildungsverzeichnis
Abbildung 1-1: Hierarchisches Datenbankmodell ....................................................... 5
Abbildung 1-2: Netzwerkmodell .................................................................................. 6
Abbildung 4-1: Beispieltabelle .................................................................................. 10
Abbildung 4-2: 3-Ebenen-Konzept............................................................................ 11
Abbildung 5-1: Struktur eines Datenbanksystems .................................................... 12
Abbildung 5-2: Relationendarstellung allgemein....................................................... 14
Abbildung 5-3: Relationendarstellung am Beispiel eines Buchverleihs .................... 15
Abbildung 5-4: Kardinalitäten.................................................................................... 16
Abbildung 5-5: Unnormalisierte Datenbankrelation .................................................. 18
Abbildung 5-6: Auszug unnormalisierte Datenbankrelation ...................................... 19
Abbildung 5-7: 5. Normalform................................................................................... 22
Abbildung 6-1: Symbol für Beziehungsfenster.......................................................... 24
Abbildung 6-2: Beziehung zwischen zwei Tabellen herstellen.................................. 24
Abbildung 6-3: Eingabeformular für Namen.............................................................. 25
Abbildung 6-4: Eigenschaftenfenster des Feldes „Ort“ im Formular ......................... 25
Abbildung 8-1: Darstellung von Entitäten im ER-Diagramm ..................................... 31
Abbildung 8-2: Beziehungen im ER-Diagramm ........................................................ 31
Abbildung 8-3: Beziehungen mit Kardinalitäten (ER-Diagramm) .............................. 32
Abbildung 8-4: Beziehungen mit Kardinalitäten (Mengendarstellung) ...................... 32
Abbildung 8-5: Kardinalität und Tabelle .................................................................... 33
Abbildung 8-6: Beziehungsattribute und Tabelle ...................................................... 33
Abbildung 9-1: Relationen von Austria 97 ................................................................ 35
Abbildung 9-2: Einbindung von Tabellen in den Abfrageentwurf .............................. 36
Abbildung 9-3: Umschalten auf SQL-Ansicht............................................................ 36
Abbildung 9-4 UNION............................................................................................... 42
Abbildung 9-5 INTERSECT ...................................................................................... 43
Abbildung 9-6 INTERSECT für alle Tupel ................................................................ 43
Seite 88/88
11.09.2005 10:18