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

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.

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: