Ä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.
Spalten hinzufügen und ändern
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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 bei den DDL-Einzelheiten für ALTER COLUMN 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;
Vorgabewert hinzufügen
[Bearbeiten]Für diese Aufgabe gibt es mehrere Varianten; wir beschränken uns auf eine.
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.
Einschränkungen auf Spalten
[Bearbeiten]Dazu wird der Befehl ALTER TABLE – ADD CONSTRAINT verwendet.
Die hier besprochenen Änderungen sowie weitere Einschränkungen sind im Skript-Constraints zusammengefasst.
Eindeutigkeit festlegen
[Bearbeiten]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.
CHECK-Bedingungen für eine Spalte
[Bearbeiten]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.
CHECK-Bedingungen für mehrere Spalten
[Bearbeiten]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.
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
[Bearbeiten]Dazu wird der Befehl CREATE INDEX verwendet.
Die hier besprochenen Indizes sowie weitere Suchschlüssel sind im 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 den „einheitlichen, gemeinsamen“ Index gibt.
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
[Bearbeiten]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
[Bearbeiten]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
[Bearbeiten]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'.
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
[Bearbeiten]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.
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
[Bearbeiten]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).
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
[Bearbeiten]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
[Bearbeiten]Die hier verwendeten Verfahren werden in den folgenden Kapiteln behandelt:
- Anmerkungen zur Beispieldatenbank
- Beispiel einer StoredProcedure: automatisches UPDATE gemäß Bedingungen
- DDL - Einzelheiten: ALTER COLUMN
- Fremdschlüssel-Beziehungen
- Nützliche Erweiterungen
- SQL-Programmierung: Routine ohne feste Speicherung.
Was ist ein Skript?
[Bearbeiten]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
[Bearbeiten]- ↑ Erläuterungen zu diesem und den anderen Skripts stehen am Ende dieses Kapitels.