Skript-Testdaten.sql
(Einführung in SQL: Downloads: Skript-Testdaten.sql)
Die Befehle auf dieser Seite erweitern die Beispieldatenbank um „viele“ weitere Daten. Die Anleitung steht im Anhang B Downloads.
/***********************************************************
* Neue Fahrzeuge registrieren
***********************************************************/
/* Erläuterungen siehe Prozeduren: Testdaten in einer Tabelle erzeugen */
EXECUTE PROCEDURE Insert_into_Fahrzeug (500);
/***********************************************************
* Hilfstabelle für Nachnamen
***********************************************************/
CREATE TABLE TempName ( text VARCHAR(30) );
commit;
INSERT INTO TempName VALUES ('Babel');
INSERT INTO TempName VALUES ('Broszat');
INSERT INTO TempName VALUES ('Heenemann');
INSERT INTO TempName VALUES ('Frese');
INSERT INTO TempName VALUES ('Dittmann');
INSERT INTO TempName VALUES ('Kaynak');
INSERT INTO TempName VALUES ('Ludewig');
INSERT INTO TempName VALUES ('Remmele');
INSERT INTO TempName VALUES ('Schmidt');
INSERT INTO TempName VALUES ('Meier');
INSERT INTO TempName VALUES ('Ullrich');
INSERT INTO TempName VALUES ('Wichert');
INSERT INTO TempName VALUES ('Antonius');
INSERT INTO TempName VALUES ('Zachert');
INSERT INTO TempName VALUES ('Christensen');
INSERT INTO TempName VALUES ('Virck');
INSERT INTO TempName VALUES ('Eisenmann');
INSERT INTO TempName VALUES ('Trakan');
INSERT INTO TempName VALUES ('Grossert');
INSERT INTO TempName VALUES ('Paulsen');
COMMIT;
/***********************************************************
* Hilfstabelle für Vornamen
***********************************************************/
/* Hier wird auch gleich das Geschlecht registriert. */
CREATE TABLE TempVorname ( text VARCHAR(30), Geschlecht CHAR(1) DEFAULT 'W' );
COMMIT;
INSERT INTO TempVorname (Text) VALUES ('Maria');
INSERT INTO TempVorname (Text) VALUES ('Teresa');
INSERT INTO TempVorname (Text) VALUES ('Christine');
INSERT INTO TempVorname (Text) VALUES ('Emily');
INSERT INTO TempVorname (Text) VALUES ('Gudrun');
INSERT INTO TempVorname (Text) VALUES ('Shirin');
INSERT INTO TempVorname (Text) VALUES ('Petra');
INSERT INTO TempVorname (Text) VALUES ('Yasemin');
INSERT INTO TempVorname (Text) VALUES ('Susanna');
INSERT INTO TempVorname (Text) VALUES ('Alexandra');
INSERT INTO TempVorname VALUES ('Justus', 'M');
INSERT INTO TempVorname VALUES ('Biral', 'M');
INSERT INTO TempVorname VALUES ('Detlef', 'M');
INSERT INTO TempVorname VALUES ('Frantisek', 'M');
INSERT INTO TempVorname VALUES ('Siegmund', 'M');
INSERT INTO TempVorname VALUES ('Zacharias', 'M');
INSERT INTO TempVorname VALUES ('Wolfgang', 'M');
INSERT INTO TempVorname VALUES ('Marcus', 'M');
INSERT INTO TempVorname VALUES ('Recep', 'M');
INSERT INTO TempVorname VALUES ('Konstantin', 'M');
COMMIT;
/***********************************************************
* Hilfstabelle für PLZ und Ortsnamen
***********************************************************/
/* Die Spaltenlänge von 24 Zeichen bei Orts- und Straßennamen ist
auf die Datenstruktur der Deutschen Post AG zurückzuführen. */
CREATE TABLE TempPLZOrt (
PLZ CHAR(5),
Ort VARCHAR(24),
Kreis VARCHAR(3)
);
COMMIT;
INSERT INTO TempPLZOrt VALUES ('44135', 'Dortmund', 'DO' );
INSERT INTO TempPLZOrt VALUES ('44289', 'Dortmund', 'DO' );
INSERT INTO TempPLZOrt VALUES ('44575', 'Castrop-Rauxel', 'RE' );
INSERT INTO TempPLZOrt VALUES ('44625', 'Herne', 'HER' );
INSERT INTO TempPLZOrt VALUES ('44649', 'Herne', 'HER' );
INSERT INTO TempPLZOrt VALUES ('44801', 'Bochum', 'BO' );
INSERT INTO TempPLZOrt VALUES ('44892', 'Bochum', 'BO' );
INSERT INTO TempPLZOrt VALUES ('45127', 'Essen', 'E' );
INSERT INTO TempPLZOrt VALUES ('45276', 'Essen', 'E' );
INSERT INTO TempPLZOrt VALUES ('45277', 'Essen', 'E' );
INSERT INTO TempPLZOrt VALUES ('45525', 'Hattingen', 'BO' );
INSERT INTO TempPLZOrt VALUES ('45657', 'Recklinghausen', 'RE' );
INSERT INTO TempPLZOrt VALUES ('45699', 'Herten', 'RE' );
INSERT INTO TempPLZOrt VALUES ('45721', 'Haltern am See', 'RE' );
INSERT INTO TempPLZOrt VALUES ('45768', 'Marl', 'RE' );
INSERT INTO TempPLZOrt VALUES ('45879', 'Gelsenkirchen', 'GE' );
INSERT INTO TempPLZOrt VALUES ('45889', 'Gelsenkirchen', 'GE' );
INSERT INTO TempPLZOrt VALUES ('45964', 'Gladbeck', 'RE' );
INSERT INTO TempPLZOrt VALUES ('46045', 'Oberhausen', 'OB' );
INSERT INTO TempPLZOrt VALUES ('46117', 'Oberhausen', 'OB' );
INSERT INTO TempPLZOrt VALUES ('46236', 'Bottrop', 'BOT' );
INSERT INTO TempPLZOrt VALUES ('46244', 'Bottrop', 'BOT' );
INSERT INTO TempPLZOrt VALUES ('46325', 'Borken', 'BOR' );
INSERT INTO TempPLZOrt VALUES ('46342', 'Velen', 'BOR' );
INSERT INTO TempPLZOrt VALUES ('46395', 'Bocholt', 'BOR' );
INSERT INTO TempPLZOrt VALUES ('47119', 'Duisburg', 'DU' );
INSERT INTO TempPLZOrt VALUES ('47137', 'Duisburg', 'DU' );
INSERT INTO TempPLZOrt VALUES ('58313', 'Herdecke', 'EN' );
INSERT INTO TempPLZOrt VALUES ('58332', 'Schwelm', 'EN' );
INSERT INTO TempPLZOrt VALUES ('59069', 'Hamm', 'HAM' );
INSERT INTO TempPLZOrt VALUES ('59071', 'Hamm', 'HAM' );
COMMIT;
/***********************************************************
* Hilfstabelle für Straßen
***********************************************************/
CREATE TABLE TempStrasse (Name varchar(24));
COMMIT;
INSERT INTO TempStrasse VALUES ('Goethestr.');
INSERT INTO TempStrasse VALUES ('Schillerstr.');
INSERT INTO TempStrasse VALUES ('Lessingstr.');
INSERT INTO TempStrasse VALUES ('Badstr.');
INSERT INTO TempStrasse VALUES ('Turmstr.');
INSERT INTO TempStrasse VALUES ('Chausseestr.');
INSERT INTO TempStrasse VALUES ('Elisenstr.');
INSERT INTO TempStrasse VALUES ('Poststr.');
INSERT INTO TempStrasse VALUES ('Hafenstr.');
INSERT INTO TempStrasse VALUES ('Seestr.');
INSERT INTO TempStrasse VALUES ('Neue Str.');
INSERT INTO TempStrasse VALUES ('Münchener Str.');
INSERT INTO TempStrasse VALUES ('Wiener Str.');
INSERT INTO TempStrasse VALUES ('Berliner Str.');
INSERT INTO TempStrasse VALUES ('Museumsstr.');
INSERT INTO TempStrasse VALUES ('Theaterstr.');
INSERT INTO TempStrasse VALUES ('Opernplatz');
INSERT INTO TempStrasse VALUES ('Rathausplatz');
INSERT INTO TempStrasse VALUES ('Bahnhofstr.');
INSERT INTO TempStrasse VALUES ('Hauptstr.');
INSERT INTO TempStrasse VALUES ('Parkstr.');
INSERT INTO TempStrasse VALUES ('Schlossallee');
COMMIT;
/**************************************************************
* Tabelle der Versicherungsnehmer mit den Hilfstabellen füllen
**************************************************************/
/* Die WHERE-Bedingung sorgt dafür, dass nicht ganz so viele Daten erstellt werden,
siehe Hinweis im Kapitel "Testdaten erzeugen." */
INSERT INTO Versicherungsnehmer
( Name, Vorname, Geschlecht,
PLZ, Ort,
Strasse,
Hausnummer,
Geburtsdatum, Fuehrerschein, Eigener_Kunde, Versicherungsgesellschaft_ID )
SELECT n.Text, v.Text, v.Geschlecht,
p.Plz, p.Ort,
s.Name,
CAST( CAST( FLOOR( 1 + RAND()* 98) AS INTEGER) AS VARCHAR(10)),
'01.01.1950', '31.12.2009', 'J', NULL
FROM TempName n, TempVorname v, TempPLZOrt p, TempStrasse s
WHERE n.Text <= 'M' AND v.Text <= 'P' AND s.Name >= 'M';
COMMIT;
/***********************************************************
* Geburtsdatum und Führerschein zufällig ändern
***********************************************************/
UPDATE Versicherungsnehmer
SET Geburtsdatum = DATEADD( DAY, CAST( FLOOR(RAND()*27) AS INTEGER),
DATEADD( MONTH, CAST( FLOOR(RAND()*11) AS INTEGER),
DATEADD( YEAR, CAST( FLOOR(RAND()*40) AS INTEGER), Geburtsdatum)))
WHERE Geburtsdatum = '01.01.1950';
COMMIT;
UPDATE Versicherungsnehmer
SET Fuehrerschein = DATEADD( DAY, CAST( FLOOR(RAND()*27) AS INTEGER),
DATEADD( MONTH, CAST( FLOOR(RAND()*11) AS INTEGER),
DATEADD( YEAR, CAST( FLOOR(18 + RAND()*(DATEDIFF
(YEAR, Geburtsdatum, CAST('31.12.2008' AS DATE))-18)
) AS INTEGER), Geburtsdatum)))
WHERE Fuehrerschein = '31.12.2009';
COMMIT;
/***********************************************************
* Hilfstabelle TempVertrag
***********************************************************/
/* Die Struktur der Tabelle erstellen: */
CREATE TABLE TEMPVERTRAG (
NR INTEGER,
FZ_ID INTEGER,
FZ_KENNZEICHEN VARCHAR(10),
FZ_KREIS VARCHAR(3),
VN_ID INTEGER,
VN_NAME CHAR(2),
ABSCHLUSSDATUM DATE
);
/* Zunächst werden die freien Fahrzeuge registriert: */
INSERT INTO Tempvertrag
( fz_ID, fz_Kennzeichen, fz_Kreis )
SELECT ID, Kennzeichen,
SUBSTRING( Kennzeichen FROM 1 FOR Position('-', Kennzeichen) - 1)
FROM Fahrzeug
WHERE ID NOT IN ( SELECT Fahrzeug_id FROM Versicherungsvertrag )
AND ( Kennzeichen starts WITH 'BO-'
OR Kennzeichen starts WITH 'BOR-'
OR Kennzeichen starts WITH 'BOT-'
OR Kennzeichen starts WITH 'E-'
OR Kennzeichen starts WITH 'DU-'
OR Kennzeichen starts WITH 'RE-'
OR Kennzeichen starts WITH 'OB-'
OR Kennzeichen starts WITH 'GE-'
OR Kennzeichen starts WITH 'HER-'
OR Kennzeichen starts WITH 'HAM-'
OR Kennzeichen starts WITH 'EN-'
OR Kennzeichen starts WITH 'DO-' );
/* Die Beschränkung auf einige Kennzeichen ist sinnvoll, weil unter "Neue Fahrzeuge registrieren"
erheblich mehr Datensätze notiert werden als sinnvoll und bei den potenziellen Kunden
nur die jetzt genannten Kennzeichen berücksichtigt werden. */
/* Sortiere sie nach Kreis und nummeriere sie in dieser Reihenfolge: */
EXECUTE BLOCK
AS
DECLARE VARIABLE nextid INTEGER = 0;
DECLARE VARIABLE tempid INTEGER;
BEGIN
FOR SELECT fz_id
FROM TempVertrag
ORDER BY fz_Kreis, fz_ID
INTO :Tempid
DO BEGIN
nextid = nextid + 1;
UPDATE TempVertrag
SET Nr = :nextid
WHERE fz_ID = :Tempid;
END
END
/* Ordne per Zufallsreihenfolge – getrennt nach jedem Kreis – jedem Fahrzeug
einen Eintrag aus der Tabelle Versicherungsnehmer zu. */
EXECUTE BLOCK
AS
DECLARE VARIABLE nextid INTEGER = 0;
DECLARE VARIABLE tempid INTEGER;
DECLARE VARIABLE tkreis VARCHAR(3);
DECLARE VARIABLE Tname CHAR(2);
DECLARE VARIABLE minnr INTEGER;
DECLARE VARIABLE maxnr INTEGER;
DECLARE VARIABLE Tdatum DATE;
BEGIN
FOR SELECT fz_Kreis, Min(Nr), Max(Nr)
FROM TempVertrag
GROUP BY fz_Kreis
ORDER BY fz_Kreis
INTO :tkreis, :Minnr, :Maxnr
DO BEGIN
/* hole alle möglichen potenziellen Kunden für diesen Kreis
in Zufallsreihenfolge */
nextid = :Minnr - 1;
FOR SELECT ID,
/* diese komplizierte Konstruktion mit TRIM, CAST ist wegen SUBSTRING nötig */
CAST( TRIM(SUBSTRING(Name FROM 1 FOR 1))
|| TRIM(SUBSTRING(Ort FROM 1 FOR 1)) AS CHAR(2)),
Fuehrerschein
FROM Versicherungsnehmer
WHERE PLZ IN ( SELECT PLZ
FROM TempPLZOrt
WHERE Kreis = :Tkreis
)
AND ID NOT IN
( SELECT Versicherungsnehmer_ID
FROM Versicherungsvertrag
)
ORDER BY RAND()
INTO :Tempid, :Tname, :Tdatum
DO BEGIN
/* registriere jeden dieser Kunden nacheinander
für eines der Fahrzeuge in diesem Kreis */
nextid = nextid + 1;
UPDATE TempVertrag
SET vn_ID = :Tempid,
vn_Name = :Tname,
/* per Zufall variable Daten vorbereiten */
Abschlussdatum = DATEADD( DAY, CAST( FLOOR(RAND()*27) AS INTEGER),
DATEADD( MONTH, CAST( FLOOR(RAND()*11) AS INTEGER),
DATEADD( YEAR, CAST( FLOOR(RAND()*
(DATEDIFF (YEAR, :Tdatum, CAST('31.12.2008' AS DATE))
) ) AS INTEGER), :Tdatum)))
WHERE Nr = :nextid;
END
END
END
/***********************************************************
* Tabelle Versicherungsvertrag
***********************************************************/
/* Übertrage die Daten aus TempVertrag in die eigentliche Tabelle. */
INSERT INTO Versicherungsvertrag
SELECT NULL, /* ID nach Generator */
Vn_name || '-' || CAST( Nr AS VARCHAR(3) ), /* Vertragsnummer */
Abschlussdatum, /* Vertragsabschluss */
CASE MOD(Fz_id, 4) /* Vertragsart nach Fahrzeug-ID */
WHEN 0 THEN 'VK'
WHEN 1 THEN 'HP'
ELSE 'TK'
END,
CASE MOD(Fz_id, 4) /* Mitarbeiter-ID nach Fahrzeug-ID */
WHEN 0 THEN 9
WHEN 1 THEN 10
WHEN 2 THEN 11
ELSE 12
END,
Fz_id, /* Fahrzeug-ID */
Vn_id, /* Versicherungsnehmer-ID */
100, /* Prämiensatz */
Abschlussdatum, /* letzte Prämienänderung */
CASE MOD(Fz_id, 4) /* Basisprämie nach Vertragsart */
WHEN 0 THEN 800
WHEN 1 THEN 500
ELSE 550
END
FROM TempVertrag t;
/* Passe Prämiensatz und Prämienänderung an; Erläuterung siehe Änderung der Datenbankstruktur. */
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
/* Berechne die weiteren Werte nach der aktuellen Entwicklung: */
EXECUTE PROCEDURE Update_Praemiensatz('31.12.2007');
COMMIT;
EXECUTE PROCEDURE Update_Praemiensatz('31.12.2008');
COMMIT;
EXECUTE PROCEDURE Update_Praemiensatz('31.12.2009');
COMMIT;