Änderung der Datenbankstruktur

In diesem Kapitel werden wir die Erkenntnisse aus den vorhergehenden Kapiteln benutzen, um die Beispieldatenbank zu ergänzen und zu erweitern, wie es dort in den Anmerkungen angesprochen wurde.

(Einführung in SQL: Änderung der Datenbankstruktur)

Spalten hinzufügen und ändern

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

Die hier besprochenen Änderungen sowie weitere Angaben zu Spalten sind im Skript-Spalten^[1] zusammengefasst.

Neue Spalten einfügen

Aufgabe

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 später 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 das Beispiel für eine 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.

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.

Aufgabe

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.

MySQL-Version

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 bei den DDL-Einzelheiten für ALTER COLUMN erwähnt, mehrere Einzelmaßnahmen mit einer temporären Spalte erforderlich:

Firebird-Version

/* 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;

Vorgabewert hinzufügen

Für diese Aufgabe gibt es mehrere Varianten; wir beschränken uns auf eine.

Aufgabe

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

MySQL-Version

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.

Einschränkungen auf Spalten

Dazu wird der Befehl ALTER TABLE – ADD CONSTRAINT verwendet.

Die hier besprochenen Änderungen sowie weitere Einschränkungen sind im Skript-Constraints zusammengefasst.

Eindeutigkeit festlegen

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

Aufgabe

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.

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.

Aufgabe

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.

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 oben durch das Skript-Spalten eingefügt wird.

Aufgabe

In der Tabelle Versicherungsnehmer muss es sich entweder um eine Firma handeln, d. h. Geschlecht IS NULL, oder eine Reihe anderer Spalten benötigt 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, soweit möglich) erhalten haben kann.

Indizes

Dazu wird der Befehl CREATE INDEX verwendet.

Die hier besprochenen Indizes sowie weitere Suchschlüssel sind im Skript-Indizes zusammengefasst.

Aufgabe

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

CREATE      INDEX Versicherungsvertrag_FZ
    ON Versicherungsvertrag (Fahrzeug_ID);

Aufgabe

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.

Aufgabe

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 den „einheitlichen, gemeinsamen“ Index gibt.

Aufgabe

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 kann auch NULL-Werte enthalten (nämlich für die „Eigenen Kunden“), und dabei verhalten sich die DBMS unterschiedlich.

Fremdschlüssel

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

Die Fremdschlüssel, die für die Beispieldatenbank vorzusehen sind, sind im Skript-ForeignKeys zusammengefasst.

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-Anpassen zusammengefasst. Dort ist eine der Versionen mit allen Werten enthalten.

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'.

Fehler

update Versicherungsnehmer
   set Geschlecht = 'W'
 where Vorname is not null;

Ausgabe

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.

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.

Fehler

update Versicherungsvertrag
   set Basispraemie = CASE Art
                      WHEN 'TK' THEN 550
                      WHEN 'VK' THEN 800
                      END ;

Ausgabe

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

Natürlich, man muss aufpassen. Bei den „Nützlichen Erweiterungen“ hieß es zur 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 ;

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 (siehe SQL-Programmierung).

Firebird-Version

EXECUTE BLOCK
AS
  DECLARE VARIABLE jj INTEGER;
  DECLARE VARIABLE T1 INTEGER;
  DECLARE VARIABLE T2 INTEGER;
BEGIN
  for SELECT vv.ID, EXTRACT(YEAR from Abschlussdatum), Praemiensatz
        from Versicherungsvertrag vv
             join Versicherungsnehmer vn on vn.Id = vv.Versicherungsnehmer_Id
       where Praemienaenderung <= '31.12.2006'
         and vn.Eigener_kunde = 'J'
        into :T1, :jj, :T2
  DO BEGIN
     update Versicherungsvertrag
        set Praemienaenderung = DATEADD( YEAR, 2006 - :jj, Abschlussdatum ),
            Praemiensatz = CASE :T2
                           WHEN 200 then CASE
                                         WHEN :jj <= 2000 THEN   80
                                         ELSE 200 - (2006 - :jj)*20
                                         END
                           WHEN 100 then CASE
                                         WHEN :jj <= 2000 THEN   30
                                         ELSE 100 - (2006 - :jj)*10
                                         END
                           ELSE :T2
                           END
      where ID = :T1;
  END
END

Diese Berechnung kann nur für eigene Kunden ausgeführt werden. Deshalb benötigen wir 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 nach dem Abschlussdatum sowie natürlich dem 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 neuere Verträge um je 20 Punkte pro Jahr bis zu einem Minimum von 80
- für andere Verträge um je 10 Punkte pro Jahr bis zu einem Minimum von 30
- Werte, die von den Anfangssätzen 100 oder 200 abweichen, bleiben stehen

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:

Skript-Spalten zum Hinzufügen und Ändern einzelner Spalten
Skript-Constraints zum Anlegen von UNIQUE KEYs und CHECK-Constraints
Skript-Indizes zum Anlegen weiterer Schlüssel
Skript-ForeignKeys zum Anlegen der Fremdschlüssel
Skript-Anpassung zum Anpassen der betroffenen Datensätze

Weitere Hinweise dazu gibt es im Kapitel Downloads.

Siehe auch

Die hier verwendeten Verfahren werden in den folgenden Kapiteln behandelt:

Was ist ein Skript?

Unter einem Skript versteht man in der EDV eine Liste von Befehlen, die durch einen einzelnen Befehl aufgerufen und automatisch nacheinander ausgeführt werden. Diese Befehle sind meist in einer Datei zusammengefasst und werden dadurch ausgeführt, dass die betreffende Datei aufgerufen wird.

Bei SQL sind solche Dateien unter Windows üblicherweise mit der Endung ".sql" gespeichert.

Hinweis

↑ Erläuterungen zu diesem und den anderen Skripts stehen am Ende dieses Kapitels.

Inhaltsverzeichnis: Erweiterungen

Zurück zu: Tipps und Tricks

Weiter zu: Testdaten erzeugen

[1] Erläuterungen zu diesem und den anderen Skripts stehen am Ende dieses Kapitels.

[1]