Fachbeitrag «Datenbanken»

Datenbankdesign und SQL

Inhalt

1. Einführung
1.1 Anforderungen an eine DB
1.2 Daten verwalten heisst
1.3 Datenbankmodelle
1.4 Definition Datenbank
1.5 Die Themenkreise für das Arbeiten mit einer DB
1.6 Begriffserklärungen
1.7 Praxisüberlegungen

2. Das Entitätenblockdiagramm
2.1 Grundlegende Komponenten
2.2 Kardinalität
2.3 Mögliche Beziehungstypen
2.4 Die Umsetzung der 1:1-Beziehung
2.5 Die Umsetzung der 1:mc-Beziehung
2.6 Die Umsetzung der mc:mc-Beziehung
2.7 Referentielle Integrität
2.8 Tabellenbeschreibung

3. DB-Modellierung
3.1 Die Tabelle
3.2 Primär- und Fremdschlüssel
3.3 Normalisierung von Relationen
3.4 Der Normalisierungsprozess
3.4.1 Erste Normalform
3.4.2 Zweite Normalform
3.4.3 Dritte Normalform
3.4.4 Die Tabellen unseres Beispiels als Resultat der Normalisierung
3.4.5 Abfragen und Formulare erstellen
3.5 Optional: Rekursive Beziehungen
3.6 Optional: Generalisierung/Spezialisierung

4. DB-Transaktionen
4.1 SQL - Structured Query Language
4.2 Einfache Abfragen mit dem SELECT-Befehl
4.2.1 SELECT / FROM-Klausel
4.2.2 WHERE-Klausel
4.2.3 ORDER BY-Klausel
4.3 Verknüpfung von Tabellen
4.4 Unterabfragen SUBQUERY
4.5 Tabelle erzeugen mit CREATE
4.6 Einmaligkeit durch den Unique-Constraint
4.7 Tabelleneintrag mit INSERT
4.8 Tabelleneintrag löschen mit DELETE
4.9 Unterschied zwischen LEFT JOIN, RIGHT JOIN und INNER JOIN

1. Einführung

1.1 Anforderungen an eine DB

  • Einfachen Zugriff auf die Daten, ohne Kenntnisse des inneren Aufbaus
  • Flexibilität: Die Datenbank lässt sich mit vertretbarem Aufwand erweitern und ändern
  • Datenschutz: Die Benutzer haben unterschiedliche Sichten auf die Daten und verschiedene Rechte bezüglich lesen, schreiben (einfügen und ändern) und löschen der Daten. Alle Benutzer haben eine gemeinsame Datenbasis
  • Datensicherheit: Schutz vor Datenverlust oder Verfälschung durch Fehlmanipulation
  • Datenkonsistenz/Redundanzfrei>: In einer Datenbank dürfen alle Informationen über eine bestimmte Sache nur einmal vorkommen. Damit wird verhindert, dass widersprüchliche Informationen über eine Sache entstehen können
  • Datenunabhängige Applikationen>: Die Applikationen (Masken, Programme etc.) sind idealerweise unabhängig von den Daten. Das heisst, falls sich Daten ändern, müssen keine Änderungen an den Applikationen gemacht werden

1.2 Daten verwalten heisst

  • Daten erfassen
  • Daten mutieren
  • Daten löschen
  • Daten auswerten

1.3 Datenbankmodelle

Hierarchisch: Älteste Datenbankkonzept / Beschränkt brauchbar weil nur 1:mc Beziehungen redundanzfrei realisierbar sind / Wenig flexibel, jedoch schnell in der Abfrage, da alle Daten sequentiell in einem einzigen File abgelegt sind / Veraltet

Netzwerkförmig: Basieren im Prinzip auf den hierarchischen DB, lassen aber mc:mc Beziehungen zu / Wenig flexibel und veraltet

Relational: Pro Themenkreis (Entitätsmenge) existiert ein File (Tabelle oder Relation) / Tabellen stehen in Beziehung zueinander / Beziehung werden mittels Identifikationsschlüsseln bzw. Fremdschlüsseln geschaffen

Im Folgenden wird die relationale Datenbank (DB) behandelt

1.4 Definition Datenbank

Eine Datenbank ist ein System zur Beschreibung, Speicherung und Wiedergewinnung von umfangreichen Datenmengen, die von mehreren Anwendungsprogrammen (-gleichzeitig-) benutzt werden können. Sie besteht aus zwei Hauptteilen, den eigentlichen Daten und den Verwaltungsprogrammen (Datenbank-Management-System, Datenbanksoftware), welche gemäss einer vorgegebenen Beschreibung Daten speichern, suchen, löschen oder ändern kann.

Der Benutzer bzw. seine Applikation greift niemals direkt auf die Daten zu, sondern immer über ein Kontroll- und Verwaltungssystem. Dieses heisst Datenbank-Management-System (DBMS).

Die wesentlichen Funktionen eines DBMS sind:
  • Speicherung, Überschreibung und Löschung von Daten
  • Verwaltung der Metadaten
  • Vorkehrungen zur Datensicherheit
  • Vorkehrungen zum Datenschutz
  • Vorkehrungen zur Datenintegrität
  • Ermöglichung des Mehrbenutzerbetriebs durch das Transaktionskonzept
  • Optimierung von Abfragen
  • Ermöglichung von Triggern und Stored Procedures
  • Bereitstellung von Kennzahlen über Technik und Betrieb des DBMS
Das DBMS stellt als Schnittstelle eine Datenbanksprache für die folgenden Zwecke zur Verfügung:
  • Datenbeschreibungsteil oder Sprache (DDL)
  • Datenmanipulationsteil oder Sprache (DML)
  • Datenkontrollteil oder Sprache (DCL)
Datenmanipulationssprachen sind Sprachen, die es ermöglichen, Daten in einer Datenbank abzufragen (Query-Language). Bsp.: SQL.

1.5 Die Themenkreise für das Arbeiten mit einer DB

Designmethode

«Der Design bestimmt die Qualität der Applikation»
  • Konzeptionelles Datenmodell Diese höchste Abstraktionsebene ermöglicht es, die gesamte für eine Applikation relevante Information in einem datenbankunabhängigen Schema festzuhalten und verständlich darzustellen. (Entity-Relationship-Modell)
  • Logisches Datenmodell Abbildung des konzeptionellen Datenmodell auf einen bestimmten Datenbanktyp
  • Integritätsbedingungen Festhalten von Datentypen, Bedingungen zwischen Daten in Form von Regeln oder Prüfroutinen. (Datadictionary)

Verwaltungssprache

«Die Sprache bestimmt die Funktionalität der Datenbank»
  • Datendefinitionssprache DDLDefinition von Datenstrukturen, Datentypen und Integritätsbedingungen
  • Datenmanipulationssprache DMLEinfügen, Aendern, Löschen und Abfragen einer Datenbank
  • Datenkontrollsprache DCLRegeln von Zugriffsrechten, Setzen von Systemparametern, Optimierung der physischen Organisation etc.
SQL = DDL + DML (SQL = Structured Query Language)

Physische Struktur

«Die physische Ebene bestimmt Performance und Stabilität der Datenbank»
  • Datenbanksystem (DBMS) Nimmt Befehle und Anfragen in der Verwaltungssprache entgegen und stellt die eigentliche DB in Form von Speicherplatz auf Disk dar.
  • Die Routinen führen Zugriff auf Daten durch und sorgen für sicheren fehlerfreien Betrieb.
  • Bei Crash während einer Modifikation Zurücksetzen der DB in konsistenten Zustand.

1.6 Begriffserklärungen

  • Datenintegrität Jede Datensammlung wird innert kurzen Zeit unbrauchbar, wenn ihre Integrität nicht gewährleistet ist. Man betrachte dazu als Beispiel die Telefonbücher in öffentlichen Telefonkabinen. Da werden Seiten herausgerissen, gelegentlich Unflätigkeiten zu einzelnen Abonnentennamen hinzugefügt, während Nachträge von Neuabonnenten (begreiflicherweise) fehlen. Zum Glück gibt’s da noch den Auskunftsdienst ! Von einer Datenbank erwarten wir somit nicht bloss schnelle Datenzugriffe und Flexibilität bei der Datenspeicherung, sondern auch Hilfe zur Abwehr unerwünschter Ereignisse.
  • Concurrency Control Die Art, wie das Datenbank-Managementsystem gleichzeitig laufende Transaktionen bedient. Der Hauptmechanismus des Concurrency-Control ist das Sperren von Daten (Locking)
  • Recovery Wiederherstellen eines konsistenten DB-Zustandes Konsistenz. Eine DB ist konsistent, wenn alle Daten der Realität entsprechen, alle relevanten Daten vollständig vorhanden und alle Integritätsbedingungen erfüllt sind. Wird ein konsistenter Zustand in einen anderen konsistenten Zustand überführt, so nennt man dies eine Transaktion.

1.7 Praxisüberlegungen

Im folgenden einige Gesichtspunkte, die während der Datenbank-Pflichtenheftphase einer Klärung bedarfen und die Wahl der Software entscheidend beeinflussen können:
  • Wie kompliziert sind die Datenstrukturen
  • Wie hoch ist die Anzahl Transaktionen pro Zeiteinheit und die Anzahl gleichzeitiger Benutzer
  • Wie hoch ist der maximale Platzbedarf und mit welcher Geschwindigkeit wächst die Datenmenge
  • Genügt die zu erwartende Performance der Spezifikation
  • Wie häufig müssen die Daten gesichert werden
  • Wie wichtig ist die Konsistenz der Daten
  • Über welche Schnittstellen müssen die Daten zur Verfügung stehen
  • Wieviel Aufwand muss für Datenschutz betrieben werden
  • Müssen bestehende Daten in eine neue Applikation übernommen werden
  • Welches sind geeignete Datenbankprodukte und wie hoch sind die Kosten für DBMS und seine Tools

2. Das Entitätenblockdiagramm

Die grafische Darstellung von Entitätstypen und Beziehungstypen wird Entity-Relationship-Diagramm (ERD) oder Entitätenblockdiagramm genannt.

Das Entity-Relationship-Modell (ERM) dient dazu, im Rahmen der semantischen Datenmodellierung einen in einem gegebenen Kontext relevanten Ausschnitt der realen Welt zu beschreiben. Das ERM besteht aus einer Grafik (ERD) und einer Beschreibung der darin verwendeten Elemente.


2.1 Grundlegende Komponenten

  • Entität (Entity) Individuell identifizierbares Objekt der Wirklichkeit; z. B. der Angestellte Müller, das Projekt XY
  • Beziehung (Relationship) Verknüpfung / Zusammenhang zwischen zwei oder mehreren Entitäten; z. B. Angestellter Müller leitet Projekt XY
  • Eigenschaft (Attribute) Was über eine Entität (im Kontext) von Interesse ist; z. B. das Eintrittsdatum des Angestellten Müller
Im Rahmen der Modellierung werden aus den vorgenannten Sachverhalten gleichartige Typen gebildet. Alle identifizierten Typ-Ausprägungen werden im Modell exakt definiert und beschrieben. Diese Typen unterscheiden sich nach:
  • Entitätstyp Typisierung gleichartiger Entitäten z. B. Angestellter, Projekt, Buch, Autor, Verlag
  • Beziehungstyp (Relationship-Typ) Typisierung gleichartiger Beziehungen; z. B. Angestellter leitet Projekt
  • Attribut Typisierung gleichartiger Eigenschaften, z. B. Nachname, Vorname und Eintrittsdatum für den Entitätstyp Angestellter. Das Attribut oder die Attributkombination, deren Wert(e) die Entität eindeutig beschreiben, d. h. diese identifizieren, heißen identifizierende(s) Attribut(e), zum Beispiel ist das Attribut Projektnummer identifizierend für den Entitätstyp Projekt.

2.2 Kardinalität

Kardinalitäten sind Mengenangaben, mit denen in der Datenmodellierung für Entity-Relationship-Diagramme (ER-Diagramme) für jeden Beziehungstyp festgelegt wird, wie viele Entitäten eines Entitätstyps mit genau einer Entität des anderen am Beziehungstyp beteiligten Entitätstyps (und umgekehrt) in Beziehung stehen können oder müssen.


2.3 Mögliche Beziehungstypen

Wobei gilt:
  • 1 = Ein Datensatz aus Tabelle1 entspricht einem Datensatz aus Tabelle2
  • c = Ein Datensatz aus Tabelle1 entspricht keinem oder genau einem Datensatz aus Tabelle2
  • m = Ein Datensatz aus Tabelle1 entspricht mindestens einem Datensatz aus Tabelle2
  • mc = Ein Datensatz aus Tabelle1 entspricht keinem oder beliebig vielen Datensätzen aus Tabelle2

2.4 Die Umsetzung der 1:1-Beziehung

Hier wird der Primärschlüssel einer der beiden Tabellen als Fremdschlüssel der anderen Tabelle in eine zusätzliche Spalte aufgenommen. Bei welcher der Tabellen das geschieht, ist technisch irrelevant. Praktisch versucht man die reale Abhängigkeit darzustellen, indem man den Primärschlüssel der Master-Tabelle in eine zusätzliche Spalte der Detail-Tabelle aufnimmt. Zusätzlich muss sichergestellt werden, dass die Werte in der Spalte mit dem Fremdschlüssel nur einmal vorkommen (z. B. durch Trigger, UNIQUE-Constraints o. ä.).

2.5 Die Umsetzung der 1:mc-Beziehung

Die Detail-Tabelle erhält eine zusätzliche Spalte, die als Fremdschlüssel den Primärschlüssel der Master-Tabelle aufnimmt. Bei einer 1:mc-Beziehung nennt man das Attribut, das mehrere «Instanzen» haben kann, also jenes der mc-Seite «Mehrwertiges Attribut». Sollte es sich um eine 1:m-Beziehung handeln, muss mit einem NOT-NULL-Constraint sichergestellt werden, dass ein Datensatz aus Entität1 mindestens einem Datensatz aus Entität2 entspricht.

2.6 Die Umsetzung der mc:mc-Beziehung

mc:mc-Beziehungen können in den meisten relationalen Datenbanken nicht direkt umgesetzt werden. Zur Realisierung wird eine zusätzliche Tabelle erstellt, die die Primärschlüssel beider Tabellen als Fremdschlüssel enthält. Die mc:mc-Beziehung wird also aufgelöst, und man erhält eine weitere Datenbanktabelle, die zwei 1:mc-Beziehungen realisiert. Bei m:mc oder m:m-Beziehungen muss wiederum mit NOT-NULL-Constraints sichergestellt werden, dass ein Datensatz aus Entität1 mindestens einem Datensatz aus Entität2 entspricht.

Oft werden für die Bezeichnung der die mc:mc-Beziehung realisierenden Tabelle die Bezeichnungen der beiden daran beteiligten Tabellen verwendet; bei den Tabellen «Student» und «Professor» könnte so die zusätzliche Tabelle «StudentProfessor» heissen.

Gehören zur mc:mc-Beziehung weitere Attribute, so wird häufig bereits im ER-Modell ein eigener Entitätstyp gebildet, womit zwei getrennte 1:mc-Beziehungen entstehen. Beispiel: Hotel ist reserviert für Person; neuer Entitätstyp «Reservierung» – mit mc:1-Beziehungen zu Person und Hotel und weiteren Attributen wie Reservierungszeitraum, Reservierungsstatus etc.

2.7 Referentielle Integrität

Um gemäss «Referentieller Integrität» bzw. «Beziehungsintegrität» Datenintegrität zu gewährleisten, dürfen Datensätze über ihre Fremdschlüssel nur auf existierende Datensätze verweisen. Das bedeutet:
  • Ein neuer Datensatz mit einem Fremdschlüssel kann nur dann in die Tabelle eingefügt werden, wenn in der referenzierten Tabelle ein Datensatz mit entsprechendem Wert im Primärschlüssel bereits existiert.
  • Eine Datensatzlöschung oder Änderung des Schlüssels in einem Primär-Datensatz ist nur dann möglich, wenn zu diesem Datensatz keine abhängigen Datensätze in Beziehung stehen.
Nützliche Besonderheiten und Erweiterungen
  • Änderungsweitergabe: Wenn der eindeutige Schlüssel eines Datensatzes geändert wird, kann das DBMS die Fremdschlüssel in allen abhängigen Datensätzen anpassen – anstatt die Änderung abzulehnen
  • Löschweitergabe: Falls abhängige Datensätze bei Löschung des Masterdatensatzes mitzulöschen sind
  • Kaskadierung: Lösch- oder Änderungsweitergabe, die mehrstufig wirkt
  • Beziehung auf sich selbst: In bestimmten Situationen kann ein Detaildatensatz auch auf sich selbst verweisen

Beispiel zu referentieller Integrität

Dieses OpenOfficeBase-Beispiel zeigt den vereinfachten Zusammenhang zwischen Lehrer, Lernenden, Kurse und Tests auf:

  • T_Test zu T_Lernender: Jeder Test referenziert einen Lernenden. Falls der Lernende die Schule vorzeitig verlässt und somit sein Eintrag in der Tabelle T_Lernender gelöscht wird, ist auch ein allfällig bereits geleisteter Test obsolet. Die Löschoption bei der Beziehung zwischen den Tabellen T_Lernender und T_Test «Kask. Löschen» (= kaskadiert Löschen) führt dazu, dass ein Test gelöscht wird, falls der referenzierte Lernende gelöscht wird. (Integritätsbedingung: Ohne Lernenden gibt es auch keinen Test)
  • T_Test zu T_Kurs: Jeder Test bezieht sich auf einen Kurs. Solange noch ein Test für einen Kurs existiert, darf dieser Kurs nicht aus der Datenbank gelöscht werden. Die Löschoption bei der Beziehung zwischen den Tabellen T_Kurs und T_Test «Keine Aktion» verhindert, dass ein referenzierter Datensatz aus der Tabelle T_Kurs gelöscht wird. (Integritätsbedingung: Zu jedem Test gibt es auch einen Kurs)
  • T_Kurs zu T_Lehrer: Eine Kurs referenziert einen Lehrer. Falls der Lehrer die Schule verlässt und somit sein Eintrag in der Tabelle T_Lehrer gelöscht wird, bleibt der Kurs trotzdem bestehen, bzw. die Löschoption bei der Beziehung zwischen den Tabellen T_Lehrer und T_Kurs «Null setzen» bewirkt, dass in der Tabelle T_Kurs die Referenz auf den Lehrer auf NULL gesetzt wird. (Integritätsbedingung: der referenzierte Lehrer führt den Kurs)

Die Einhaltung dieser Bedingungen gewährleistet die Datenbank!

2.8 Tabellenbeschreibung

Ein wichtiger Teil der Systemdokumentation ist neben dem ERM die Tabellenbeschreibungen. Damit kann nun ein Datenbankentwickler Tabellen und Beziehungen erstellen. Die Tabellenbeschreibung gibt Auskunft über alle Attribute (Spalten), deren Datentyp, Primär- oder Fremdschlüsselfunktion, ob zwingend eine Eingabe erforderlich ist (NOT NULL) und die Eingabe einmalig (UNIQUE) sein muss.

Beispiel einer Tabellenbeschreibung:

3. DB-Modellierung

3.1 Die Tabelle

In relationalen Datenbanksystemen werden alle Daten in Tabellen abgelegt. Eine Tabelle ist eine Beschreibung eines bestimmten Objektes unserer realen Welt. Zusammengehörende Daten, die sich auf ein bestimmtes einzelnes Objekt beziehen, werden als eine Zeile in der Tabelle gespeichert. Es gibt vier grundlegende Begriffe :
  • Tabelle (Relation, Entitätstyp)
  • Spalte (Merkmal, Attribut)
  • Zeile (Entität, n-Tupel, Datensatz)
  • Feld (Attributwert)
Eine Tabelle ist in senkrechte und waagrechte Spalten unterteilt. Die Schnittfläche die sich durch Überschneidung einer senkrechten Spalte mit einer waagrechten Zeile ergibt, wird als Feld bezeichnet. Nur Felder können Werte enthalten.

3.2 Primär- und Fremdschlüssel

  • Primärschlüssel: Jede Zeile soll eindeutig identifiziert werden können. Oft wird dazu eine zusätzliche eingerichtete Spalte benutzt, deren Attributwerte keinerlei eigene Bedeutung besitzen und nur der Eindeutigkeit dienen. Diese Tabellenspalte wird als Primärschlüssel bezeichnet.
  • Fremdschlüssel: Bei voneinander abhängigen Tabellen wird die Abhängigkeit häufig in Form einer Fremdschlüsselspalte in der untergeordneten Tabelle dargestellt. Dabei finden sich die Fremdschlüsselwerte in der Primärschlüsselspalte der übergeordneten Tabelle wieder und bilden somit die Beziehung zwischen Zeilen beider Tabellen ab.
Zeile = Entität / Spalte = Attribut / Feld = Attributwert

3.3 Normalisierung von Relationen

Die Normalisierung ist die Zerlegung von Tabellen nach bestimmten Vorschriften. Ziele des Normalisierungsprozesses sind :
  • Die gesamte Information in der Datenbank ist redundanzfrei, dh. jedes Faktum, jeder Wert eines Attributes ist genau an einem Ort festgehalten
  • Durch Modifikations- oder Einfügeoperationen können keine Widersprüche in den Daten auftreten (Speicheranomalien)
  • Die Abfrage und Bearbeitung von Daten soll einfach und sicher sein. Normalisierte Tabellen bilden die Grundlage zu einer syntaktisch einfachen Datenmanipulationssprache

Speicheranomalien

Die Normalisierung hat unter anderem den Zweck, Widersprüche (Anomalien) zu verhindern. Ein Widerspruch ist sofort anhand der Daten in der Datenbank erkennbar, ohne dass man die zugrundeliegende Realität kennen muss. Falschinformation ist nur erkennbar, wenn man die Realität kennt. Das Erzeugen von Falschinformationen kann ein Datenbank-Management-System nicht verhindern, das Auftreten von Widersprüchen aber ohne weiteres. Wichtig ist die Tatsache, dass eine redundanzfreie Datenbank niemals Widersprüche enthalten kann. Die Idee bei relationalen Datenbanken ist daher, dass man eine Darstellungsform findet, in die niemals Redundanz eingebaut werden kann.

3.4 Der Normalisierungsprozess

Der Normalisierungsprozess sei hier erklärt anhand des folgenden Beispiels:
Ausgangspunkt ist eine nicht-normalisierte Tabelle, in der Mitarbeiter in einer Garage, deren Abteilung und Aufträge erfasst sind:

Würde man die Zeile mit «Max Rüti» neu in die Tabelle einfügen, wird dies zu einem Widerspruch führen. Ein DBMS könnte diesen Widerspruch sofort erkennen, wenn die Tabelle der 3. Normalform genügen würde.
Im allgemeinen erreicht man beim Erstellen von Datenmodellen praktisch von selbst Tabellen, die sich in der ersten, zweiten oder sogar dritten Normalform befinden. Trotzdem müssen wir aber alle drei Normalformen kennenlernen und beginnen mit der obigen nicht-normalisierten Tabelle.

Die Regeln zu dieser Garagen-Datenbank sind:

  • Ein Mitarbeiter hat einen Namen
  • Ein Mitarbeiter hat einen Wohnort
  • Ein Mitarbeiter arbeitet in einer Abteilung
  • Ein Mitarbeiter ist an mehreren Jobs beteiligt
  • Jeder Job erfordert pro Mitarbeiter eine bestimmte Zeit
  • Eine Abteilung hat einen Namen Jeder Job hat eine Nummer und einen Namen
  • In einer Abteilung sind mehrere Mitarbeiter angestellt
  • An einem Job sind mehrere Mitarbeiter beteiligt

3.4.1 Erste Normalform

In der ersten Normalform sind keine mehrfachen Werte eines Attributes mehr zugelassen. Durch Angabe des Primärschlüssels ist der Wert von jedem Attribut eindeutig bestimmt.

Um eine Relation in die erste Normalform zu bringen, müssen alle existierenden Zeilen sooft kopiert und die mehrfach belegten Attribute auf die Kopien aufgeteilt werden, dass in jedem Feld nur noch ein Wert steht.

Hinweis: Bei PNr handelt es sich um eine einfache Personalnummer. Diese stellt aber noch keinen Primärschlüssel dar!

Vorteile:
  • Sehr übersichtlich
Nachteile:
  • Sehr hohe Redundanz
  • Ändern und Einfügen schwierig
  • Speicheranomalien immer noch möglich

3.4.2 Zweite Normalform

In obiger unnormalisierten Tabelle fällt auf, dass sie Attribute enthält, die gar nicht in einem inhaltlichen Zusammenhang miteinander stehen. Was hat zB. der Jobname mit dem Wohnort zu tun? In der zweiten Normalform wird die ursprüngliche Tabelle in kleinere Tabellen zerlegt, die nur noch Attribute enthalten, die effektiv miteinander in Beziehung stehen.

Eine Relation genügt der zweiten Normalform, wenn sie sich in der ersten Normalform befindet und jeder Attributwert eine Funktion des ganzen Schlüssel, und nicht nur eines Teils davon ist.

Schrittfolge zur Herstellung der 2. Normalform:
  1. Primärschlüssel der gegebenen Relation festlegen, falls dieser nur aus einem Attribut besteht, liegt bereits 2.NF vor (Hinweis: Eine einfache Zeilennummerierung ist in diesem Sinne kein geeigneter Primärschlüssel!)
  2. Untersuchung, ob aus Teilschlüsselattributen bereits weitere Attribute folgen. Falls nicht, liegt bereits die 2. NF vor - Falls Abhängigkeiten gefunden werden, dann...
  3. Neue Relation bilden, die das Teilschlüsselattribut und alle von diesem abhängigen Nichtschlüsselattribute anthalten - Das Teilschlüsselattribut wird in der neuen Relation der Primärschlüssel
  4. Löschen der ausgelagerten Nichtschlüsselattribute in der Ausgangsrelation
  5. Vorgang ab 2. wiederholen, bis alle Nichtschlüsselattribute vom gesamten Schlüssel funktional abhängig sind.
Vorteile:
  • Reduktion von Redundanz
Nachteile:
  • Durch die Aufspaltung müssen bei einer Abfrage die Daten wieder zusammengesetzt werden
  • Speicheranomalien sind immer noch möglich aber nur noch in der Tabelle PersonZweiteNormalform

3.4.3 Dritte Normalform

Aus der noch möglichen Speicheranomalie in der zweiten Normalform ergibt sich der Wunsch nach einer dritten Normalform.

Eine Relation befindet sich in der dritten Normalform, wenn sie den Forderungen der ersten und zweiten Normalform genügt und wenn alle Attribute, die nicht dem Schlüssel angehören, untereinander keine funktionale Abhängigkeit aufweisen.

Ist ein Attribut von einem anderen nicht dem Schlüssel angehörenden funktional abhängig, so muss dieses aus der Relation entfernt und zusammen mit dem Attribut von dem es abhängig ist in eine neu erstellte Relation eingefügt werden.

Schrittfolge zur Herstellung der 3. Normalform:
  • Untersuchung, ob aus Nichtschlüsselattributen andere Nichtschlüsselattribute folgen. Falls nicht liegt bereits die 3. NF vor - Falls Abhängigkeiten gefunden werden, dann...
  • Neue Relation bilden, die das Nichtschlüsselattribut (wird nun Primärschlüssel der neuen Relation) und die von ihm abhängigen Attribute enthält
  • Löschen der ausgelagerten Nichtschlüsselattribute mit Ausnahme des Attributes, das in der neuen Relation Primärschlüssel ist
  • Vorgang ab 2. wiederholen, bis keine Abhängigkeiten mehr bestehen
Vorteile:
  • Sehr hohe Redundanzfreiheit
  • Widersprüche in der Datenbank sind ausgeschlossen
  • Durch Einfügungen sind keine Speicheranomalien mehr möglich

3.4.4 Die Tabellen unseres Beispiels als Resultat der Normalisierung

Ausgangslage war die folgende: Die Tabellen so, dass sie der dritten Normalform genügen:

3.4.5 Abfragen und Formulare erstellen

Nachdem die Datenbasis (Tabellen und Beziehungen) erstellt wurde, soll sie nun mit Inhalt befüllt werden. Dazu benötigen wir ein entsprechendes Hilfsmittel, den sogenannten Maskengenerator, welcher die Entwicklung von benutzerdefinierten Eingabemasken unterstützen und eine Verbindung zwischen dem Benutzer und der Datenbank darstellt.

Die Benutzermaske hat folgende Aufgaben:
  • Ermöglicht das komfortable Editieren und Abfragen von Daten
  • Trägt massgeblich zur Erhaltung der Datenkonsistenz bei, indem Benutzereingaben und Aktionen auf deren Richtigkeit hin überprüft werden
  • Kann komplexe Transaktionen durchführen, welche die Arbeit der Benutzer wesentlich vereinfacht

Im weiteren möchte man Daten nicht nur verwalten, sondern auch auswerten. Dies geschieht, indem man Abfragen definiert und die daraus entstandenen Daten formatiert. Die formatierten Daten werden in Form von Listen am Bildschirm angezeigt oder ausgedruckt. Solche Abfragen können sehr komplex aufgebaut sein und man benötigt dafür ein geeignetes Werkzeug, welches Reportgenerator genannt wird.

  • Formulare entsprechen den hier beschriebenen Benutzermasken und beziehen sich auf eine Tabelle
  • Abfragen dienen als Basis für den Reportgenerator
  • Berichte ist der eigentliche Reportgenerator der formatierte Listen erstellt
Formularaufteilung in:
  • Stammdaten: Beschreiben Geschäftsobjekte (z.B. der Kundenstamm, Artikelgruppe etc.), die nur in Ausnahmefällen verändert werden. Diese Daten werden meist schon vor dem Produktiveinsatz angelegt oder aus Altanwendungen übernommen. Änderungen und Erweiterungen an diesen Datenbeständen werden bei Bedarf vorgenommen, sind aber nicht sehr häufig. Stammdaten zeichnen sich somit durch eine gewisse Dauerhaftigkeit aus.
  • Bestandsdaten: Weisen Bestände (z.B. Anzahl Exemplare des Produkts XY) aus und beschreiben somit einen Zustand. Sie kennzeichnen die betriebliche Mengen- und Wertestruktur und werden fortlaufend aktualisiert.
  • Bewegungsdaten: Bewegungsdaten beschreiben Ereignisse (z.B. Bestellungen, Aufträge, Mahnungen etc.) und zeichnen sich durch ihren Zeitbezug aus, d.h. Bewegungsdaten werden bei jedem Geschäftsvorfall erfasst. Sie dienen der Abbildung der Wertflüsse und Bestandsveränderungen im System in Form von mengen- oder wertmäßigen Zu- und Abgängen.
Sie finden auf www.youtube.com unter dem Begriff «lucky müller openoffice» zehn hervorragende Videotutorials zu Tabellen und Formularerstellung unter OpenOfficeBase. Auch Formularentwürfe für Tabellenbefüllungen bei m-zu-n-Beziehungen werden dort ausführlich demonstriert.

3.5 Optional: Rekursive Beziehungen

Beispiel für eine rekursive Beziehung «Klassisches Orchester»
Es gilt die Rahmenbedingung, dass ein Musiker auch gleichzeitig Dirigent sein kann und jeder Dirigent auch Musiker ist. Somit lassen sich folgende Beziehungen formulieren. Die 1-c Beziehung bedeutet, dass ein Musiker Dirigent sein kann und jeder Dirigent gleichzeitig auch Musiker ist. Die 1-m Beziehung bedeutet, dass jeder Musiker von genau einem Dirigenten dirigiert wird und dass jeder Dirigent mindestens einen Musiker dirigiert. Die beiden Beziehungen werden angeschrieben, damit der Sinn eindeutig erkennbar ist. Die entsprechende Tabelle müsste dann folgenden Aufbau haben:

Diese Tabelle besitzt den Id-Schlüssel «MNr» (Musiker-Nr.) und den Fremdschlüssel «DNr» (Dirigenten-Nr.), welcher aus den1 Id-Schlüssel «MNr» gebildet wurde. Auf den ersten Blick scheint dies zu funktionieren. Es lässt sich klar bestimmen, dass Karajan ein Dirigent ist, welcher die Musiker Schmid, Bernstein und Müller dirigiert, weil seine Musiker-Nr. im Attribut «DNr» vorkommt. Allerdings treten nun folgende Unstimmigkeiten auf:

  • Der Fremdschlüssel «DNr» müsste «MNr» heissen, weil «DNr» ja aus dem Id-Schlüssel «MNr» gebildet wurde. Dann hätten aber zwei Attribute die gleiche Bezeichnung.
  • Wenn man wissen möchte, ob Karajan ein Dirigent ist, muss man alle Attributwerte von «DNr» nach den1 Id-Schlüsselwert von Karajan durchsuchen. Dies ist bei einer grossen Entitätsmenge unübersichtlich.

Das gewählte Beispiel soll verdeutlichen, dass eine rekursive Beziehung nicht verwendet werden darf. Die Tabelle «Musiker» wird deshalb in die Tabellen «Musiker» und «Dirigenten» transformiert, wobei dann zwischen den beiden Tabellen eine Mehrfachbeziehung entsteht. Die zwei Beziehungen werden ebenfalls der Übersichtlichkeit wegen angeschrieben. Nun sind die Dirigenten klar von den Musikern abgetrennt. Die Beziehung «Zugehörigkeit» beschreibt, welche Musiker von welchen Dirigenten dirigiert werden, während die Beziehung «Status» angibt, welche Musiker Dirigenten sind. Die zwei Tabellen haben nun folgenden Aufbau:

In der Tabelle «Musiker» sind nun diejenigen Daten aufgeführt, welche alle Musiker (und damit auch die Dirigenten) betreffen, während man in der Tabelle «Dirigenten» spezifische Attribute einführen könnte, welche bestimmte Eigenschaften der Dirigenten beschreiben würden (z.B. die Anzahl Dirigentenjahre).
Man sieht nun aber, dass der Id-Schlüssel «MNr» vom Fremdschlüssel «DNr» abhängt und andererseits der Id-Schlüssel «DNr» vom Fremdschlüssel «MNr» abhängig ist. Dieser Sachverhalt macht sich bemerkbar, sobald man versucht, einen Musiker einzugeben. Dies gelingt nämlich nur, wenn die Dirigentennummer bekannt ist, weil Nullwerte in Fremdschlüsseln ja nicht erlaubt sind. Die Dirigentennummer hingegen bekommt man erst, wenn die Musikernummer bekannt ist. Die Beziehungen «Zugehörigkeit» und «Status» zwischen den beiden Tabellen «Musiker» und «Dirigenten» sind indirekt rekursiver Art und erzwingen die vorübergehende Verwendung von Nullwerten. Ergo ist auch diese Beziehung verboten, was zu einer erneuten Transformation führt.
Es musste eine neue Tabelle «Orchester» eingeführt werden, welche mit der Tabelle «Dirigenten» in einer 1-1 Beziehung steht. Die drei Tabellen haben nun folgenden Aufbau:

Mit dieser Transformation wurden alle rekursiven Beziehungen beseitigt und der Id-Schlüssel «DNr» eliminiert. Bei der Dateneingabe müssen nun zuerst die Orchester definiert werden. Danach können die Musiker und zuletzt ‚die Dirigenten eingegeben werden. Es ist eindeutig definiert, dass der Musiker «Schmid» bei den Wiener Philharmonikern spielt und damit von Karajan dirigiert wird. Dieses Beispiel zeigt, dass auch eine 1-1 Beziehung ihre Berechtigung hat, denn eine Zusammenlegung der beiden Tabellen «Orchester» und «Dirigenten» ist nicht möglich, weil sonst Nullwerte beim Fremdschlüssel MNr entstehen könnten.

3.6 Optional: Generalisierung/Spezialisierung

Es gibt den Spezialfall, dass die Entitätsmengen von zwei- oder mehreren Tabellen Teilmengen einer übergeordneten Entitätsmenge sind. Beispielsweise könnte eine übergeordnete Entitätsmenge «Firmenpersonal» in die Untermengen «Angestellte» und «Aushilfen» aufgeteilt werden. In der Tabelle «Angestellte» können dann zusätzliche Attribute verwendet werden, welche die speziellen Eigenschaften eines Angestellten beschreiben und in der Tabelle «Firmenpersonal» nicht vorhanden sind, weil sie nicht generell für alle Personen gelten. Die Tabelle «Firmenpersonal» bezeichnet man als generalisierte Tabelle, während die Tabellen «Aushilfen» und ««Angestellte» als spezialisierte Tabellen bezeichnet werden. Die generalisierte Entitätsmenge umfasst dabei die spezialisierten Entitätsmengen vollständig. Es existieren also keine Tupel in den spezialisierten Tabellen, welche in der generalisierten Tabelle nicht vorkommen. Diese Unter- und Obermengenbeziehungen lassen sich in drei verschiedene Fälle einteilen:

  • Spezialisierte Entitätsmengen mit zugelassener Überlappung
  • Generalisierte Entitätsmenge mit vollständiger Überdeckung
  • Spezialisierte Entitätsmengen ohne Überlappung

Die drei Fälle sollen anhand eines Beispiels eines Flugsportvereins mit diversen Flugzeugtypen (Segelflieger, Motorsegler, Sportflugzeug und Heissluftballon) aufgezeigt werden.

3.6.1 Zugelassene Überlappung
Wenn man die Entitätsmengen dieser Beziehungsart grafisch darstellt, ergibts sich folgendes Bild:

Die Entitätsmenge der Tabelle «Flugapparate» beinhaltet die Entitätsmengen der Tabellen «Segelflugzeuge» und «Motorflugzeuge». In der Tabelle «Flugapparate» können nun Tupel existieren, deren Id-Schlüsselwert als Fremdschlüssel...

  • nicht in den Tabellen «Segelflugzeuge» und «Motorflugzeuge» vorkommt
  • in beiden Tabellen «Segelflugzeuge» und «Motorflugzeuge» vorkommt
  • nur in einer der beiden Tabellen «Segelflugzeuge» und «Motorflugzeuge» vorkommt

Das Entitätenblockdiagramm und die Tabellen sehen folgendermassen aus:

Die Tabelle «Flugapparate» umfasst alle flugtauglichen Objekte des Flugsportvereins. Die zweite Tabelle «Segelflugzeuge» beinhaltet alle nichtmotorisierten Tragflächenflugzeuge und die dritte Tabelle «Motorflugzeuge» beinhaltet alle motorisierten Flugzeuge.
In der Entitätsmenge «Flugapparate» kommen auch Flugapparate wie der Heissluftballon vor, welche nicht den Untermengen «Segelflugzeuge» und «Motorflugzeuge» angehören. Der Flugapparat Nr. 6 ist ein Segelflugzeug mit Hilfsmotor und gehört sowohl der Entitätsmenge «Segelflugzeuge» als auch der Entitätsmenge «Motorflugzeuge» an. Als Id-Schlüssel der Tabellen «Segelflugzeuge» und «Motorflugzeuge» findet der Id-Schlüssel «FNr» der Tabelle «Flugapparate» verwendung.

3.6.2 Vollständige Überdeckung
Wenn man die Entitätsmengen dieser Beziehungsart grafisch darstellt, ergibt sich folgendes Bild: 

Die Entitätsmenge der Tabelle «Flugapparate» («Flugapparate» besteht hier vollständig aus den Entitätsmengen der Tabellen «Segelflugzeuge» und «Motorflugzeuge»). In der Tabelle «Flugapparate» existieren keine Tupel, deren Id-Schlüsselwert nicht in den Tabellen «Segelflugzeuge» und «Motorflugzeuge» als Fremdschlüssel vorkommt.
Das Entitätenblockdiagramm und die Tabellen sehen folgendermassen aus:

Die Tabelle «Flugapparate» umfasst wiederum alle Flugzeuge des Flugsportvereins. Die Tabelle «Segelflugzeuge» beinhaltet alle Segelflugzeuge und die Tabelle «Motorflugzeuge» beinhaltet alle Motorflugzeuge. Diesmal existieren in der Tabelle «Flugapparate» nur Tupel, deren Id-Schlüssel als Fremdschlüssel entweder in der Tabelle «Segelflugzeuge» oder in der Tabelle «Motorflugzeuge» vorkommt.
Das Attribut «Klasse» gibt an, in welcher spezialisierten Tabelle ein Tupel zu finden ist (S = Segelflugzeuge, M = Motorflugzeuge). Es wird als diskriminierendes Attribut bezeichnet. Im Gegensatz zur zugelassenen Überlappung, kann hier für jedes Tupel klar angegeben werden, zu welcher spezialisierten Tabelle es gehört.

3.6.3 Überlappung nicht zugelassen
Wenn man die Entitätsmengen dieser Beziehungsart grafisch darstellt, ergibt sich folgendes Bild:

Dieser Fall präsentiert sich ähnlich, wie die zugelassene Überlappung. Der einzige Unterschied besteht darin, dass sich die spezialisierten Entitätsmengen nicht überlappen. Auch hier kann klar angegeben werden, zu welcher spezialisierten Tabelle ein Tupel gehört. Somit muss, wie beim Fall der vollständigen Überdeckung, ein diskriminierendes Attribut verwendet werden.
Das Entitätenblockdiagramm und die Tabellen sehen folgendermassen aus:

Die Tabelle «Flugapparate» umfasst wiederum alle Flugzeuge des Flugsportvereins. Die Tabelle «Segelflugzeuge» beinhaltet alle Segelflugzeuge und die Tabelle «Motorflugzeuge» beinhaltet alle Motorflugzeuge. Diesmal existieren in der Tabelle «Flugapparate» keine Tupel, deren Id-Schlüsselwert sowohl in der Tabelle «Segelflugzeuge» als auch in der Tabelle «Motorflugzeuge» vorkommt.
Der Attributwert «A» im diskriminierenden Attribut «Klasse» bedeutet «anderes Fluggerät». Das Fluggerät Nr. 3 kommt in den spezialisierten Tabellen nicht vor.


4. DB-Transaktionen

Folgende Operationen sollen effizient, widerspruchsfrei (Konsistenz) und makellos (Integrität) abgearbeitet werden:

4.1 SQL - Structured Query Language

SQL ist eine Universalsprache für Datenbanken. Der Name SQL war ursprünglich die Abkürzung für «Structured Query Language», was jedoch nicht bedeutet, dass SQL nur eine Abfragesprache ist. Heute ist SQL eine Sprache zur strukturierten Abfrage, Aktualisierung, Datendefinition, Datenüberprüfung, Sicherung der Konsistenz und des gleichzeitigen Zugriffs, ferner zur Pflege des Datenkataloges und vieles mehr.

Die Sprache SQL wird in verschiedene Sprachbereiche eingeteilt:

Data Definition Language Beinhaltet Kommandos zur Definition und Veränderung der Struktur von DB-Objekten und Kommandos zur Datensicherheit. Data Manipulation Language Manipulieren und Fragen Daten aus existierenden Objekten ab. Transaction Control Kommando Tragen zur Transaktionssteuerung bei. Session Control Kommando Regeln dynamisch die Eigenschaften einer User-Session. Es würde den Rahmen dieser Einführung sprengen, alle obigen Kommandos und Befehle zu erörtern. Wir werden aber anhand einiger Übungen den SELECT-Befehl etwas genauer ansehen. Die Übersicht über den Select-Befehls ist nicht abschliessend. Wer es genauer wissen möchte, beschaffe sich eingängige Literatur des jeweiligen Datenbankherstellers.

4.2 Einfache Abfragen mit dem SELECT-Befehl

  • Die SELECT-Klausel wählt bestimmte Spalten oder abgeleitete Spaltenwerte aus
  • Nach FROM folgt die Angabe der Tabelle oder eine Liste von Tabellen, mit denen im SELECT-Befehl gearbeitet wird
  • WHERE-Klausel : Die Werte in den Feldern müssen einer spezifischen Bedingung oder einer Anzahl von Bedingungen entsprechen
  • ORDER BY spezifiziert die Sortierkriterien, nach denen das Abfrageergebnis ausgegeben werden soll.

Dazu nun einige SQL-Beispiele, die sich auf die beiden folgenden Tabellen beziehen:


4.2.1 SELECT / FROM-Klausel

  • Der Select dient zur Spezifizierung des Ergebnisses der Abfrage. Der Stern «*» soll heissen, dass alle Spalten einer Tabelle ausgegeben werden sollen
  • Ein Select-Ausdruck kann Konstanten, arithmetische Ausdrücke, und Funktionen enthalten
  • Zum SELECT gehört immer ein FROM, mit dem man die Tabelle (oder mehrere Tabellen) auswählt, aus welcher Spaltenwerte ausgelesen werden sollen

select *
from personal;

select Pers_Nr, Name
from Personal;

select sum(Gehalt*13), sum(Bonus*13)
from Personal;

select Name as "MITARBEITER", Gehalt*13 as "JAHRESSALAER"
from Personal;


4.2.2 WHERE-Klausel

  • Enthält eine oder mehrere Suchbedingungen, denen die Zeilen genügen müssen, um angezeigt zu werden
  • Um mehrere Bedingungen miteinander zu verknüpfen, stehen arithmetische Vergleichsoperatoren und logischen Operatoren wie NOT, AND und OR zur Verfügung

select *
from Personal
where Name = "Keller“

select Name, Gehalt
from Personal
where Gehalt between 4000 and 6000;

select *
from Personal
where Name like "*ei*“;

select Name, Bonus
from Personal
where Bonus is not null;

select Name, Abt_Nr
from Personal
where Abt_Nr not in (10, 30);

select sum(Gehalt*13), sum(Bonus*13)
from Personal
where Beruf = "Verkaeufer";

select 13*avg(Gehalt + Bonus) as "Durchschnitt Jahressalaer Verkauf"
from Personal
where Beruf = "Verkaeufer";

select max(Gehalt) as "MAX", min(Gehalt)
as "MIN", max(Gehalt) – min(Gehalt) as "DIFF"
from Personal;


4.2.3 ORDER BY-Klausel

  • Order by bestimmt die Kriterien der Zeilensortierung bei Abfrageergebnissen

select Name, Gehalt
from Personal
order by Gehalt;


4.3 Verknüpfung von Tabellen

Vielleicht haben Sie schon bemerkt, dass alle bisher verwendeten SQL-Anweisungen auf nur einer Tabelle (Personal) beruhen. Die ist jedoch nicht Bedingung. Sie können mit Leichtigkeit auch Daten selektieren, die aus mehreren Tabellen stammen. Das Konstrukt dass uns dies ermöglicht, nennt man Join. Ein Join ist immer dann notwendig, wenn Daten in mehreren Tabellen gehalten werden, diese Tabellen (Personal, Abteilung) aber in einer Beziehung zueinander stehen und daher zusammen angezeigt werden müssen. Diese Beziehung wird meist über die Primärschlüssel und Fremsschlüsselspalte realisiert. In den Besipieltabellen Personal und Abteilung ist die Beziehung über die Spalte Abt_Nr modelliert, die in der Abteilung-Tabelle Primärschlüssel und in der Personal-Tabelle Fremdschlüssel ist.

Tabellenverknüpfung EQUI-Join
Verknüpfung zweier Tabellen mittels einer gemeinsamen Spalte.

select Name, Beruf, Abt_Name, Arbeitsort
from Personal, Abteilung
where Personal.Abt_Nr = Abteilung.Abt_Nr;

Tabellenverknüpfung SELF-Join
Die Tabelle Personal wird zweimal angesprochen, also mit sich selber verknüpft. Das wird möglich durch die Verwendung der unterschiedlichen Alias-Namen angestellter und vorgesetzter.

select angestellter.Name, vorgesetzter.Name as "BOSS"
from Personal angestellter, Personal vorgesetzter
where angestellter.Vorgesetzter = vorgesetzter.Pers_Nr;

select Name, Beruf
from Personal p, Abteilung a
where p.Abt_Nr = a.Abt_Nr and Arbeitsort = "Genf";


4.4 Unterabfragen SUBQUERY

Anstatt das Problem in Bsp. 16 mit einem Equi-Join zu lösen, wäre auch eine sogenannte Subquery denkbar. Dabei wird in die where-Klausel eine vollständige select-Anweisung eingebunden.

select Name, Beruf
from Personal
where Abt_Nr in (select Abt_Nr from Abteilung where Arbeitsort = "Genf");


4.5 Tabelle erzeugen mit CREATE

Erzeugen neuer Tabellen mit "create table" (Data Definition Language) Im folgenden ein Beispiel für das Kreieren einer Tabelle.

create table adressen
(
p_id int NOT NULL UNIQUE,
name char(50),
vorname char(30),
jahrgang integer,
telefonnr char(15),
strasse char(100),
plz char(6),
ort char(30),
kfz_kontrollschild char(16)
);

Man beachte den NOT NULL SQL-Constraint und den UNIQUE-Constraint: Damit erzwingt man für den Primärschlüssel «p_id» einen einzigartigen Eintrag.


4.6 Einmaligkeit durch den Unique-Constraint

alter table adressen add UNIQUE kfz_kontrollschild;

Damit erreicht man, dass kein Wert in «kfz_kontrollschild» mehrmals vorkommt.


4.7 Tabelleneintrag mit INSERT

insert into adressen values
(
'Keller',
'Hans',
64,
'031 111 22 33',
'Bahnhofstrasse 1',
'3000',
'Bern'
);


4.8 Tabelleneintrag löschen mit DELETE

delete from adressen
where name = 'Keller' and vorname = 'Hans';


4.9 Unterschied zwischen LEFT JOIN, RIGHT JOIN und INNER JOIN

Anhand von Code-Beispielen soll der Unterschied der JOINs (unter Access) aufgezeigt werden: