Skript-Testdaten.sql

Aus Wikibooks
Zur Navigation springen Zur Suche springen

Seitentitel: Einführung in SQL: Downloads: 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;