Einführung in SQL: Änderung der Datenbankstruktur

Aus Wikibooks

Inhaltsverzeichnis: Erweiterungen Zurück zu: Trigger Weiter zu: Transaktionen

In diesem Kapitel werden wir die Erkenntnisse aus den vorhergehenden Kapiteln benutzen, um die Beispieldatenbank nach den Anmerkungen zur Beispieldatenbank zu ergänzen und zu erweitern.

[Bearbeiten] Spalten hinzufügen und ändern

Dazu wird der Befehl ALTER TABLE ... ADD/ALTER verwendet.

Die hier besprochenen Änderungen sowie weitere Angaben zu Spalten sind unter Skript "Spalten" zusammengefasst.

[Bearbeiten] Neue Spalten einfügen

Die Tabelle Versicherungsvertrag benötigt mehrere zusätzliche Spalten wegen Prämienberechnung und Schadenfreiheitsrabatt.

ALTER TABLE Versicherungsvertrag
  ADD [COLUMN] Basispraemie DECIMAL
      DEFAULT 500 NOT NULL
      CONSTRAINT Vertrag_Basispraemie_Check CHECK(Basispraemie > 0),
  ADD [COLUMN] Praemiensatz INTEGER
      DEFAULT 100 NOT NULL
      CONSTRAINT Vertrag_Praemiensatz_Check CHECK(Praemiensatz > 0),
  ADD [COLUMN] Praemienaenderung DATE;

Die CHECK-Prüfungen werden als CONSTRAINT mit Namen festgelegt. Ob das Wort COLUMN benötigt wird oder weggelassen werden kann oder muss, hängt (wie mehrfach gesagt) vom DBMS ab.

Der Vorgabewert wird bei den einzelnen Verträgen noch an die Versicherungsart angepasst, siehe unter Weitere Anpassungen, ebenso Prämiensatz und Prämienänderung auf einen einheitlichen Stand zum 1. Januar 2007. Danach werden sie durch eine Stored Procedure nach und nach auf den aktuellen Stand gebracht, siehe Beispiel einer Prozedur: automatisches UPDATE gemäß Bedingungen.

Normalerweise sollte der Vorgabewert automatisch überall eingetragen werden. Falls das nicht geschieht, genügt ein einziger UPDATE-Befehl dafür.

Hoch zum Seitenanfang

[Bearbeiten] Datentyp einer Spalte ändern

Bei dieser Aufgabe muss unbedingt darauf geachtet werden, dass die bisherigen Daten zum neuen Datentyp passen, d.h. dass sie automatisch (implizit) konvertiert werden können.

In der Tabelle Abteilung ist die Spalte Kuerzel als VARCHAR(10) festgelegt. Tatsächlich sind die Kürzel maximal vier Zeichen lang. Deshalb soll die Definition an die Realität angepasst werden.

ALTER TABLE Abteilung
ALTER COLUMN Kuerzel TYPE CHAR(4);

Bei MySql wird dieser Befehl ausgeführt; Firebird erkennt die Möglichkeit der impliziten Konvertierung durch Abschneiden zu langer Werte nicht. (Zu lange Werte gibt es sowieso nur in der Theorie, aber nicht wirklich.)

Dann sind, wie schon unter DDL - Einzelheiten erwähnt, mehrere Einzelmaßnahmen mit einer temporären Spalte erforderlich:

/* Erzeugen Sie eine neue, temporäre Spalte. */
ALTER TABLE Abteilung    ADD [COLUMN] TEMP VARCHAR(10);
 
/* Kopieren Sie alle Inhalte aus der "alten" Spalte in die temporäre Spalte. */
UPDATE      Abteilung    SET temp = Kuerzel;
 
/* Löschen Sie die "alte" Spalte. */
ALTER TABLE Abteilung   DROP [COLUMN] Kuerzel;
 
/* Erzeugen Sie eine neue Spalte unter dem "alten" Namen mit den "neuen" Eigenschaften. */
ALTER TABLE Abteilung    ADD [COLUMN] Kuerzel CHAR(4) NOT NULL;
 
/* Kopieren Sie alle Inhalte aus der temporären Spalte in die neue Spalte, 
   wobei sie passend konvertiert werden müssen. */
UPDATE      Abteilung    SET Kuerzel = SUBSTRING(temp FROM 1 FOR 4);
 
/* Löschen Sie die temporäre Spalte. */
ALTER TABLE Abteilung   DROP [COLUMN] temp;

Hoch zum Seitenanfang

[Bearbeiten] Vorgabewert hinzufügen

Für diese Aufgabe gibt es die verschiedensten Varianten.

In der Tabelle Mitarbeiter soll die Spalte Ist_Leiter in der Regel den Wert 'N' erhalten.

ALTER TABLE Abteilung    MODIFY Ist_Leiter CHAR(1) NOT NULL DEFAULT 'N';

Bei Firebird beispielsweise kann ein Default-Wert nicht einfach hinzugefügt werden; dann ist der "Umweg" mit einer temporären Spalte nötig.

Die gleiche Änderung wäre auch denkbar in der Tabelle Versicherungsnehmer für die Spalte Eigener_Kunde. Da aber nicht abgeschätzt werden kann, welcher Fall "wesentlich häufiger" vorkommt, lohnt sich eine solche Einschränkung nicht.

Hoch zum Seitenanfang

[Bearbeiten] Einschränkungen auf Spalten

Dazu wird der Befehl ALTER TABLE – ADD CONSTRAINT verwendet.

Die hier besprochenen Änderungen sowie weitere Einschränkungen sind unter Skript "Constraints" zusammengefasst.

[Bearbeiten] Eindeutigkeit festlegen

Bei mehreren Tabellen gibt es Spalten, deren Werte eindeutig sein sollen. Dies wird durch eine UNIQUE-Einschränkung geregelt.

In der Tabelle Mitarbeiter ist die Personalnummer nicht als Primary Key vorgesehen, muss aber dennoch eindeutig sein.

ALTER TABLE Mitarbeiter
  ADD CONSTRAINT  Mitarbeiter_Nummer UNIQUE (Personalnummer) 
      USING INDEX Mitarbeiter_Nummer_UK;

Wir benutzen dafür einen CONSTRAINT mit Namen und legen außerdem fest, dass ein bestimmter Index (ebenfalls mit Namen) benutzt werden soll.

Hoch zum Seitenanfang

[Bearbeiten] CHECK-Bedingungen für eine Spalte

Bei verschiedenen Spalten in mehreren Tabellen sind nur bestimmte Werte zulässig. Das wurde oben unter Neue Spalten einfügen bereits berücksichtigt, gilt aber auch für einige schon vorhandene Spalten.

In der Tabelle Versicherungsvertrag gibt es für die Spalte Art nur eine begrenzte Anzahl von Werten.

ALTER TABLE Versicherungsvertrag
  ADD CONSTRAINT  Versicherungsvertrag_Art
      CHECK( Art = 'HP' OR Art = 'TK' OR Art = 'VK' );

In gleicher Weise ist es vor allem bei den Spalten zu regeln, die als Ersatz für ein boolesches Feld nur die Werte 'J' und 'N' akzeptieren.

Hoch zum Seitenanfang

[Bearbeiten] CHECK-Bedingungen für mehrere Spalten

Die bisherigen Prüfungen beziehen sich immer auf eine einzige Spalte. Genauso kann auch geprüft werden, ob die Werte mehrerer Spalten zusammenpassen. Bitte beachten Sie, dass für das folgende Beispiel die Spalte Geschlecht benutzt wird, die im obigen Abschnitt Neue Spalten einfügen durch das Skript "Spalten" eingefügt wird.

In der Tabelle Versicherungsnehmer muss es sich entweder um eine Firma handeln, d.h. Geschlecht IS NULL, oder eine Reihe von anderen Spalten benötigen (für Personen) geeignete Werte.

ALTER TABLE Versicherungsnehmer
  ADD CONSTRAINT Versicherungsnehmer_Person
      CHECK( ( Geschlecht IS NULL )
          OR ( Vorname         IS NOT NULL
             AND Geburtsdatum  IS NOT NULL
             AND Fuehrerschein IS NOT NULL
             AND Fuehrerschein >= Geburtsdatum + 365*16 )
           );

Gleichzeitig wird geprüft, dass der Versicherungsnehmer den Führerschein frühestens im Alter von 16 Jahren (vereinfachte Berechnung) erhalten haben kann.

Hoch zum Seitenanfang

[Bearbeiten] Indizes

Dazu wird der Befehl CREATE INDEX verwendet.

Die hier besprochenen Indizes sowie weitere Suchschlüssel sind unter Skript "Indizes" zusammengefasst.

In der Tabelle Versicherungsvertrag ist häufig nach dem Fahrzeug, also nach Fahrzeug_ID zu suchen.

CREATE      INDEX Versicherungsvertrag_FZ
    ON Versicherungsvertrag (Fahrzeug_ID);

In der Tabelle Schadensfall ist häufig nach dem Datum zu suchen; jüngere Schadensfälle sollten zuerst kommen.

CREATE DESC INDEX Schadensfall_Datum
    ON Schadensfall (Datum);

Für diesen Zweck benötigen wir also mit DESC einen absteigenden Index.

In der Tabelle Versicherungsnehmer ist häufig nach dem Namen – mit oder ohne Vorname – zu suchen.

CREATE      INDEX Versicherungsnehmer_Name
    ON Versicherungsnehmer (Name, Vorname);

Sie sehen, dass ein Schlüssel auch mehrere Spalten benutzen kann. Die DBMS arbeiten unterschiedlich, ob daraus für Name ein eigener Index wird oder ob es nur ein "einheitlicher, gemeinsamer" Index ist.

In der Tabelle Versicherungsnehmer ist oft auch nach der Versicherungsgesellschaft zu suchen.

CREATE      INDEX Versicherungsnehmer_Ges 
    ON Versicherungsnehmer (Versicherungsgesellschaft_ID);

Es ist nicht sicher, dass ein solcher Index eingerichtet werden kann; denn diese Spalte ist als NOT NULL deklariert, und dabei verhalten sich die DBMS unterschiedlich.

Hoch zum Seitenanfang

[Bearbeiten] Fremdschlüssel

Dazu wird der Befehl ALTER TABLE – ADD CONSTRAINT – FOREIGN KEY verwendet. Alle Einzelheiten dazu wurden im Kapitel Fremdschlüssel-Beziehungen behandelt.

Die Fremdschlüssel, die für die Beispieldatenbank benötigt werden, sind unter Skript "ForeignKeys" zusammengefasst.

Hoch zum Seitenanfang

[Bearbeiten] Weitere Anpassungen

Durch die nachträglichen Änderungen in den vorstehenden Abschnitten müssen die bisherigen Daten angepasst werden. Das wollen wir jetzt erledigen.

Die hier besprochenen Anpassungen sind im Skript "Anpassung" zusammengefasst. Dort ist jeweils eine der Versionen mit allen Werten enthalten.

[Bearbeiten] Geschlecht ändern

In den Tabellen Mitarbeiter und Versicherungsnehmer wurde als Standardwert 'W' (= weiblich) eingetragen. Wir müssen also noch die Männer markieren; das geht durch manuelle Änderung jeder einzelnen Zeile, durch Bezug auf männliche Vornamen oder mit einer Liste der IDs von Männern. Im folgenden Code werden alle drei Varianten notiert; sinnvoll ist es aber, nur eine davon zu benutzen.

UPDATE Mitarbeiter
   SET Geschlecht = 'M'
 WHERE ID = 1
    OR Vorname IN ('Kurt', 'Walter', 'Michael')
    OR ID IN (10, 11, 12);

Die gleiche Änderung ist in der Tabelle Versicherungsnehmer nötig. Mangels Vorgabewert setzen wir vorher alle Kunden, die keine Firma sind, auf 'W'.

UPDATE Versicherungsnehmer
   SET Geschlecht = 'W'
 WHERE Vorname IS NOT NULL;

Operation violates CHECK constraint  on view or table.
Operation violates CHECK constraint VERSICHERUNGSNEHMER_PERSON 
       on view or table VERSICHERUNGSNEHMER.

Ach, was ist da denn wieder passiert? Bei der Firma mit ID=1 steht der Vorname als leere Zeichenkette, aber der oben eingerichtete CHECK mit der Prüfung "Ist Person" vergleicht den Vornamen mit dem NULL-Wert. Also muss entweder der konkrete Wert auf NULL gesetzt werden, oder der CHECK muss geändert werden. Bei einer "echten" Datenbank wäre der zweite Weg unbedingt vorzuziehen; wir machen es uns hier einfacher:

UPDATE Versicherungsnehmer
   SET Vorname = NULL
 WHERE Vorname = '';

Danach funktioniert der obige Befehl; wir müssten also wie folgt vorgehen:

• CHECK für "Ist Person" löschen.
• CHECK für "Ist Person" neu registrieren, sodass auch eine leere Zeichenkette für den Vornamen bei einer Firma zulässig ist.
• Geschlecht auf den Vorgabewert 'W' setzen
• alle männlichen Personen auf 'M' ändern wie im ersten Beispiel

Hoch zum Seitenanfang

[Bearbeiten] Vorgabewert der Basisprämie

In der Tabelle Versicherungsvertrag wurde dieser Wert allgemein auf 500 (Euro pro Jahr) gesetzt. Damit wir später richtig damit arbeiten können, muss er natürlich von der Vertragsart abhängen. (Wir unterscheiden dabei nicht nach Fahrzeugtypen und ignorieren Prämienänderungen im Laufe der Jahre, sondern tun so, als ob sich Versicherungsprämien niemals erhöhen würden.) Da der Vorgabewert sowieso auf 500 gesetzt wurde, brauchen nur die davon abweichenden Werte geändert zu werden.

UPDATE Versicherungsvertrag
   SET Basispraemie = CASE Art
                      WHEN 'TK' THEN 550
                      WHEN 'VK' THEN 800
                      END ;

 The update failed because a column definition includes validation constraints.
 validation error for column BASISPRAEMIE, value "*** null ***".

Natürlich, man muss aufpassen. Unter Nützliche Erweiterungen hieß es bei der CASE-Anweisung: "wenn ELSE nicht vorhanden ist, wird NULL als Wert genommen." Der bisherige Vorgabewert darf also nicht weggelassen werden:

UPDATE Versicherungsvertrag
   SET Basispraemie = CASE Art
                      WHEN 'TK' THEN 550
                      WHEN 'VK' THEN 800
                      ELSE 500
                      END ;

Hoch zum Seitenanfang

[Bearbeiten] Prämiensatz und Prämienänderung vorbereiten

Da sich unser Datenbestand nicht in der Wirklichkeit entwickelt hat, müssen wir diese Entwicklung simulieren. Die ersten Schadensfälle sind 2007 registriert; also muss der Datenbestand auf das Jahresende 2006 gebracht werden. Dazu benutzen wir eine Routine ohne feste Speicherung (SQL-Programmierung).

Diese Berechnung kann nur für eigene Kunden ausgeführt werden. Deshalb benötigen wir (Zeile 9, 11) die Verknüpfung mit der Tabelle Versicherungsnehmer und können es nicht mit einem einzigen UPDATE-Befehl erledigen.

Der Prämiensatz 2006 errechnet sich aus der Dauer des Vertrags. Also benötigen wir das Abschlussdatum und außerdem natürlich den bisher notierten pauschalen Prämiensatz. Im UPDATE-Befehl werden für jeden Vertrag – notiert in der Variablen T1 – die neuen Werte berechnet:

• Das Datum Praemienaenderung wird vom Abschlussdatum aus auf das Jahr 2006 weitergesetzt.
• Der Prämiensatz wird neu berechnet, und zwar:
  • für Fahranfänger um je 20 Punkte pro Jahr bis zu einem Minimum von 80
  • für andere Fahrer um je 10 Punkte pro Jahr bis zu einem Minimum von 30
  • Werte, die von den Anfangssätzen 100 oder 200 abweichen, bleiben unverändert

Hoch zum Seitenanfang

[Bearbeiten] Weitere Testdaten erzeugen

Damit man in einer Datenbank viele Tests ausführen kann, sind umfangreiche, möglichst unterschiedliche Testdaten nötig. Dies wollen wir jetzt ansatzweise besprechen und dabei die bisherigen Erkenntnisse aus diesem und den vorigen Kapiteln benutzen.

Folgende Grundgedanken bei solchen Testdaten sind immer zu beachten:

• Die einzelnen Datensätze sollen zufällig erzeugte Werte erhalten.
• Die Werte in den Spalten sollen halbwegs realistisch sein: Ein Vorname wie "Blabla" oder eine Schadenshöhe wie 0,15 € verbieten sich.
• Verschiedene Spalten müssen zusammenpassen: Zur PLZ 50667 darf es nur den Ortsnamen "Köln" geben.
• Am besten sollte auch die Verteilung der Datensätze halbwegs realistisch sein: Es sollte etwa doppelt soviele Fahrzeuge mit dem Kfz-Kennzeichen "K" (Stadt Köln) wie mit "RE" (Stadt- und Landkreis Recklinghausen) geben; ähnlich sollten auch die Versicherungsnehmer auf Stadt- und Landkreis passend verteilt sein.

[Bearbeiten] Neue Fahrzeuge registrieren

Für viele neue Datensätze in der Tabelle Fahrzeug lernten wir bereits zwei Varianten des gleichen Verfahrens kennen:

• Unter Prozeduren: Testdaten in einer Tabelle erzeugen werden mit einer WHILE-Schleife mehrere zufällige Werte für neue Zeilen zusammengesetzt.
• Dies geht auch als Routine ohne feste Speicherung.

Dieses Vorgehen ist nur sinnvoll, wenn eine geringe Anzahl von Spalten mit einer geringen Anzahl möglicher Werte "vervielfältigt" werden.

Hoch zum Seitenanfang

[Bearbeiten] Neue Versicherungsverträge registrieren

Die Prozedur Insert_Versicherungsvertrag – siehe INSERT in mehrere Tabellen – ist für einen solchen automatischen Vorgang nicht geeignet, weil sie immer wieder mit neuen Werten aufgerufen werden müsste.

In diesem Kapitel sollen deshalb neue Datensätze durch das kartesische Produkt zusammengestellt werden, wie unter Mehrere Tabellen beschrieben. Damit die erforderlichen Beziehungen gesichert sind, müssen zuerst Fahrzeuge und Versicherungsnehmer erstellt werden; erst zuletzt dürfen die Verträge registriert werden.

[Bearbeiten] Die Tabelle Fahrzeug

Eine Menge von Fahrzeugen wurde bereits durch Insert_Into_Fahrzeug in Prozeduren gespeichert. Wir prüfen zunächst, wie viele Fahrzeuge (noch) nicht zu einem Vertrag gehören:

SELECT COUNT(ID) FROM Fahrzeug
 WHERE ID NOT IN (SELECT Fahrzeug_ID FROM Versicherungsvertrag);

COUNT : 120

Wenn das noch nicht genügt, speichern Sie jetzt noch mehr Datensätze. Diese Zahl benutzen wir in den nächsten Schritten, damit alle Einträge zusammenpassen.

[Bearbeiten] Hilfstabellen

Für die zufällige Verknüpfung erstellen wir uns zunächst einige Tabellen mit passenden Werten:

• TempName mit einer Liste von Nachnamen, TempVorname mit einer Liste von Vornamen und Geschlecht
• TempPLZOrt mit einer Liste von Postleitzahlen und zugehörigen Ortsnamen
• TempStrasse mit einer Liste von Straßennamen (eigentlich müssten diese zu den Orten passen; aber da es kein "freies" Straßenverzeichnis für kleinere Städte und Orte gibt, soll uns das nicht interessieren)

[Bearbeiten] Versicherungsnehmer

Für die Tabelle Versicherungsnehmer benutzen wir eine Mischung aus Zufallsfunktionen und der direkten Verknüpfung der Daten aus den Hilfstabellen.

Hoch zum Seitenanfang

[Bearbeiten] Probleme mit Testdaten

In gleicher Weise könnten wir noch viele weitere Datensätze in den Tabellen der Beispieldatenbank erzeugen; vor allem weitere Schadensfälle wären äußerst nützlich. Dabei sind aber viele Probleme zu beachten:

• Jeder Schadensfall benötigt eine Schadenssumme, die nicht nur zufällig, sondern auch sinnvoll festgelegt werden sollte.
• Die Beschreibung des Schadensfalls sollte wenigstens annähernd realistisch zur Schadenssumme passen.
• Wir benötigen Schadensfälle mit 1, 2 oder "vielen" beteiligten Fahrzeugen. Auch deren Anzahl sowie anteilige Schadenssumme sollte annähernd realistisch zur Schadenssumme und zur Beschreibung passen.
• Wenn ein beteiligtes Fahrzeug (genauer: der Versicherungsnehmer) nicht zu den eigenen Kunden gehört, muss gleichzeitig ein Versicherungsvertrag (mit Versicherungsnehmer und Fahrzeugdaten) gespeichert werden.
• Zumindest in der Praxis gibt es auch Unfallfahrer ohne Versicherungsschutz. Wie soll man das berücksichtigen?

Eher unproblematisch sind Ergänzungen für die folgenden Tabellen; aber weil es sich um in der Regel um kleinere Tabellen handelt, muss man sich darüber auch keine Gedanken machen, sondern kann jederzeit einzelne Datensätze oder kleine Prozeduren erstellen:

• Weitere Abteilungen sind kaum nötig; bei Bedarf macht man einzelne INSERT-Befehle.
• Weitere Mitarbeiter sind nur nötig, wenn die Datenbank auch für andere Arbeitsbereiche benutzt werden soll.
• Ebenso spielen weitere Dienstwagen keine Rolle.
• Viele weitere Fahrzeugtypen und ihre Hersteller wären nützlich, und zwar schon vor der Maßnahme mit den vielen neuen Versicherungsverträgen. Dafür sind aber keine zufällig entstehenden Kombinationen sinnvoll; besser ist eine einfache Prozedur: Der neue Typ und sein Hersteller werden im Klartext angegeben; wenn der Hersteller noch nicht existiert, wird er im gleichen Schritt (automatisch) registriert (siehe Übungen zu den Prozeduren).

Insgesamt gibt es so viele praktische Probleme, die nur sehr schwer in zufällig erzeugten Datensätzen berücksichtigt werden könnten. Wir verzichten deshalb darauf. Aber für Ihre praktische Arbeit sollten Sie an alle solche "Folgeprobleme" denken.

Hoch zum Seitenanfang

[Bearbeiten] Zusammenfassung

In diesem Kapitel wurden alle Änderungen, die sich für die Beispieldatenbank als sinnvoll erwiesen, besprochen. Für jede Situation wurden Beispiele angegeben; alle erforderlichen Befehle sind in Skripten zusammengefasst:

• Spalten zum Hinzufügen und Ändern einzelner Spalten
• Constraints zum Anlegen von UNIQUE KEYs und CHECK-Constraints
• Indizes zum Anlegen weiterer Schlüssel
• ForeignKeys zum Anlegen der Fremdschlüssel

Hoch zum Seitenanfang

[Bearbeiten] Übungen

Hoch zum Seitenanfang

Inhaltsverzeichnis: Erweiterungen Zurück zu: Trigger Weiter zu: Transaktionen