Testdaten erzeugen

(Einführung in SQL: Testdaten erzeugen)

(Einführung in SQL: Testdaten erzeugen)

In diesem Kapitel werden wir die Erkenntnisse aus den vorhergehenden Kapiteln benutzen, um die Beispieldatenbank um eine Vielzahl zusätzlicher Datensätze zu ergänzen.

Auch hier gilt: Wegen der vielen Varianten bei den DBMS beschränkt sich dieses Kapitel bei den Hinweisen und Beispielen auf Firebird. Zusammen mit den speziellen Hinweisen zur SQL-Programmierung sollten sie problemlos an andere DBMS-Regeln angepasst werden können. Da es sich um ein Wikibook handelt, dürfen Sie das Buch gerne um weitere Hinweise und andere Beispiele ergänzen.

Allgemeine Hinweise

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 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 mindestens soviele Fahrzeuge mit dem Kfz-Kennzeichen 'D' (Stadt Düsseldorf) wie mit 'RE' (Stadt- und Landkreis Recklinghausen) geben; ähnlich sollten auch die Versicherungsnehmer auf Stadt- und Landkreis passend verteilt sein.

	Hinweis: Die hier genannten Schritte sind nur ein denkbares Verfahren. Wichtig ist, dass Sie einen Einblick in mögliche Vorgehensweisen und Probleme bekommen.

Neue Fahrzeuge registrieren

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

• Bei den Prozeduren werden mit einer WHILE-Schleife mehrere zufällige Werte für neue Zeilen zusammengesetzt.
• Dies geht auch als Routine ohne feste Speicherung.

Beide Varianten sind nur sinnvoll, wenn eine geringe Anzahl von Spalten mit einer geringen Anzahl möglicher Werte „vervielfältigt“ werden.

Neue Versicherungsverträge registrieren

Die Prozedur (siehe dort) Insert_Versicherungsvertrag 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. Damit die erforderlichen Beziehungen gesichert sind, müssen zuerst Fahrzeuge und Versicherungsnehmer erstellt werden; erst zuletzt dürfen die Verträge registriert werden.

Die folgenden Maßnahmen sind im Skript-Testdaten der Download-Seite zusammengefasst. Dort stehen auch diejenigen (einfacheren) SQL-Befehle, die im Folgenden nicht ausführlich angegeben und besprochen werden. Der Vermerk ➤ Skript verweist bei den folgenden Schritten auf die Befehle, die in diesem Skript stehen.

Die Tabelle Fahrzeug

Eine Menge von Fahrzeugen wurde bereits durch die Prozedur Insert_Into_Fahrzeug 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);

Ausgabe

COUNT : 120

Wenn das noch nicht genügt, speichern Sie jetzt mit der o. g. Prozedur weitere Datensätze. Auf diese Daten greifen wir in den nächsten Schritten zurück, damit alle Einträge zusammenpassen.

Bei den späteren Maßnahmen gibt es Anmerkungen dazu, wie viele Datensätze vorkommen können. Die Zahl der Fahrzeuge sollte dem ungefähr entsprechen.

Hilfstabellen

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

• TempName mit einer Liste von 20 Nachnamen, TempVorname mit einer Liste von 20 Vornamen und Geschlecht ➤ Skript
• TempPLZOrt mit einer Liste von etwa 30 Postleitzahlen und zugehörigen Ortsnamen ➤ Skript
• TempStrasse mit einer Liste von etwa 20 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) ➤ Skript

Die Tabelle Versicherungsnehmer

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

Aufgabe

Erzeuge viele Testdaten Versicherungsnehmer durch das kartesische Produkt aus den Hilfstabellen.

Firebird-Version

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

Diese Anweisung kombiniert jeden Eintrag der beteiligten Tabellen mit jedem anderen Eintrag und erzeugt damit eine „Unmenge“ potenzieller Kunden (es gibt keine Verknüpfung zwischen den vier Hilfstabellen). Dies ist also das „kartesische Produkt“, das als im Normalfall ungeeignet bezeichnet wurde, aber in dieser Spezialsituation nützlich ist.

Geburtsdatum und Fuehrerschein werden nachträgliche geändert. Der SELECT-Befehl wäre zu kompliziert, wenn zunächst das Geburtsdatum mit mehreren Zufallsfunktionen erstellt würde und davon abhängig mit weiteren Zufallszahlen das Datum des Führerscheinerwerbs berechnet würde.

	Achtung: Diese Art der Verknüpfung mehrerer Tabellen kann sehr viele Datensätze erzeugen und belastet deshalb das DBMS für längere Zeit erheblich.

Nur deshalb wird die WHERE-Bedingung benutzt. Mit allen Datensätzen der temporären Tabellen entstünden 20*20*47*22 (= 413 600) Zeilen, was bei einem Testlauf etwa 15 Minuten dauerte. Mit der Einschränkung und einer kleineren PLZ/Orte-Tabelle gibt es etwa 12*12*25*13 (= 46 800) Zeilen in 15 Sekunden.

Aufgabe

Nun werden zufällige Daten für Geburtsdatum und Fuehrerschein erzeugt und bei den neu erstellten Versicherungsnehmern gespeichert.

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;

Das Geburtsdatum kann dabei nicht später als der '31.12.1990' liegen; der Führerschein kann frühestens mit dem 18. Geburtstag erworben worden sein.

Die Tabelle Versicherungsvertrag

Um die bisherigen Teildaten zu Verträgen zusammenzufassen, müssen wir anders vorgehen. Folgende Bedingungen sind zu berücksichtigen:

• Jedes Fahrzeug darf nur einmal benutzt werden.
• Ein Versicherungsnehmer darf auch in mehreren Verträgen stehen.
• Aber der Wohnort des Kunden sollte zum Kfz-Kennzeichen passen; wir machen es zur Bedingung.
• Das Abschlussdatum des Vertrags muss nach dem Führerscheinerwerb liegen.
• Prämiensatz und Prämienänderung müssen ebenso vorbereitet werden wie im Kapitel Änderung der Datenbankstruktur.

Ein erster Versuch (zunächst beschränkt auf Fahrzeuge mit 'RE' als Kennzeichen) konnte nicht sinnvoll funktionieren – das DBMS „hängte sich auf“.:

 Firebird-Version

select fz.id, fz.Kennzeichen,
       vn.Id, vn.Name, vn.Vorname, vn.PLZ
  from Fahrzeug fz
       join Versicherungsnehmer vn on vn.ID = 
          ( select FIRST 1 ID
              from Versicherungsnehmer
             where ID not in ( select Versicherungsnehmer_ID
                                 from Versicherungsvertrag )
               and PLZ = ( select FIRST 1 PLZ
                             from TempPLZOrt
                            where Kreis = 'RE'
                            order by RAND()
                         )
             order by RAND() 
           )
 where fz.Kennzeichen STARTS WITH 'RE-';

Dieser Versuch sollte so vorgehen (lesen Sie den Befehl „von innen nach außen“):

• Hole nach Zufallsreihenfolge eine PLZ aus der Liste der Orte, die zum Kreis 'RE' gehören.
• Hole nach Zufallsreihenfolge einen Kunden mit dieser PLZ, sofern er noch nicht mit einem Vertrag registriert ist.
• Nur dessen Daten sollen mit einem einzelnen Fahrzeug verknüpft werden. Wenn man auf das JOIN verzichten würde, würde der erste Versicherungsnehmer mit jedem Fahrzeug verknüpft.

Dieses Verfahren verlangt bei jedem Fahrzeug zwei neue abhängige SELECT-Befehle, die per Zufallsfunktion aus fast 50 000 Datensätzen jeweils genau einen Datensatz liefern sollen – eine völlig unsachgemäße Belastung des DBMS.

Stattdessen wird mit einer weiteren Hilfstabelle TempVertrag schrittweise vorgegangen:

• Schreibe alle noch „freien“ Fahrzeuge aus den oben verwendeten Kreisen in die Hilfstabelle. ➤ Skript
• Sortiere sie nach Kreis und nummeriere sie in dieser Reihenfolge. ➤ Skript
• Hole per Zufallsreihenfolge – getrennt nach jedem Kreis – einen Eintrag aus der Tabelle Versicherungsnehmer.
• Übertrage diese Zusammenstellung in die Tabelle Versicherungsvertrag.
• Passe Prämiensatz und Prämienänderung an (wie im früheren Kapitel ausgeführt).

Der erste, entscheidende Schritt ist die Zuordnung eines potenziellen Kunden zu jedem „freien“ Fahrzeug.

Aufgabe

Erzeuge eine zufällige Reihenfolge der Kunden und trage sie in die (temporäre) Liste der Fahrzeuge ein.

Firebird-Version

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))

Dieser Arbeitsablauf berücksichtigt die o. g. Grundgedanken:

• In der Hauptschleife werden die Fahrzeuge nach dem Kreis gruppiert.
• Für jeden Kreis werden der kleinste und der größte Wert der laufenden Nummer aus der Hilfstabelle notiert.
• Der nächste SELECT bereitet eine Schleife mit potenziellen Kunden vor:
  • Zum aktuellen Kreis werden alle Postleitzahlen aus der Hilfstabelle TempPLZOrte registriert.
  • Die Einträge der Kunden, die zu diesen PLZ gehören, werden in eine zufällige Reihenfolge gebracht.
• In dieser Schleife wird jeder dieser Kunden bei einem Fahrzeug mit der nächsten laufenden Nummer eingetragen.
• Der Anfangsbuchstabe vom Namen und Ort wird registriert, damit dieser Wert für die Vertragsnummer zur Verfügung steht.
• Zusätzlich wird als Abschlussdatum des Vertrags ein zufälliges Datum zwischen dem Führerscheinerwerb und einem festen Schlussdatum erzeugt.

Aufgabe

Jetzt wird für jede dieser Kombinationen von Fahrzeugen und Kunden aus der Hilfstabelle TempVertrag ein neuer Versicherungsvertrag erzeugt:

Firebird-Version

insert into Versicherungsvertrag
select null,                                      /* ID nach Generator */
       Vn_name

Dabei wird jede Spalte in der Tabelle Versicherungsvertrag mit einem Wert versehen, überwiegend direkt aus der Hilfstabelle TempVertrag. Einige Werte werden statt einer weiteren Zufallsfunktion aus der Fahrzeug-ID errechnet.

Probleme mit Testdaten

Bei den vorstehenden Einzelschritten sind wiederholt Probleme aufgetreten. Das gilt natürlich auch für viele andere Versuche. Bitte vergessen Sie deshalb niemals diesen Ratschlag, der fast immer für jeden einzelnen Teilschritt gilt:

	Merke Vor jeder umfangreichen oder wichtigen Änderung ist eine Datensicherung vorzunehmen.

Die hier behandelten Verfahren sind beileibe nicht die einzig möglichen. Sie sollten aber sehen, wie zum einen verschiedene konstante Daten kombiniert und zum anderen Zufallswerte erzeugt werden können, um viele unterschiedliche Daten mit sinnvollen Kombinationen in verschiedenen Tabellen einzufügen. 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 die anteiligen Schadenssummen sollten 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 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 sind weitere Dienstwagen nicht wichtig.
• Viele weitere Fahrzeugtypen und Fahrzeughersteller 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 die Ü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.

	Hinweis: Für Ihre praktische Arbeit sollten Sie solche „Folgeprobleme“ immer beachten.

Zusammenfassung

In diesem Kapitel wurden eine Reihe Verfahren besprochen, mit denen automatisch eine Menge zusätzlicher Datensätze erstellt werden können:

• Mit dem kartesischen Produkt können aus Hilfstabellen schnell sehr viele Datensätze erzeugt werden.
• Prozeduren (oder nicht gespeicherte Routinen) sind in anderen Fällen hilfreich.
• In aller Regel sind viele Nebenbedingungen zu berücksichtigen.

Siehe auch

Dieses Kapitel benutzt Erkenntnisse der folgenden Kapitel.

• Einfache Tabellenverknüpfung zum „kartesischen Produkt“
• SQL-Programmierung mit allgemeiner Beschreibung dazu sowie dem Abschnitt „Routinen ohne feste Speicherung“
• Prozeduren mit den Abschnitten „Testdaten in einer Tabelle erzeugen“, „INSERT in mehrere Tabellen“ und den Übungen
• Änderung der Datenbankstruktur mit dem Abschnitt „Prämiensatz und Prämienänderung vorbereiten“

Das Skript-Testdaten – siehe Downloads – enthält alle Befehle, die für die Erzeugung der Testdaten benutzt werden.