Einführung in SQL: Beispieldatenbank
Aus Wikibooks
Dieses Kapitel bespricht die Grundlagen der Beispieldatenbank. Alle Einzelheiten der Tabellen stehen im Anhang unter Tabellenstruktur der Beispieldatenbank.
Inhaltsverzeichnis |
[Bearbeiten] Sachverhalt
Die Beispieldatenbank in diesem Buch versucht eine Versicherungsgesellschaft für Kfz-Versicherungen abzubilden. Das Beispiel ist eine starke Vereinfachung der Realität; zum Beispiel fehlen alle Teile der laufenden Abrechnung der Prämien und Schadensfälle.
Die relevanten Begriffe sind für eine bessere Übersichtlichkeit fett gekennzeichnet. Folgender Sachverhalt wird in der Datenbank abgebildet:
Die Versicherungsgesellschaft UnsereFirma verwaltet mit dieser Datenbank ihre Kundendaten und die Schadensfälle. Wegen der Schadensfälle müssen auch Daten "fremder" Kunden und Versicherungen gespeichert werden.
Jeder Versicherungsvertrag wird mit einem Versicherungsnehmer über genau ein Fahrzeug abgeschlossen. Der Versicherungsvertrag wird durch folgende Eigenschaften gekennzeichnet:
- Vertragsnummer (Pflicht, eindeutig)
- Datum des Abschlusses, Art des Vertrages (Pflicht); dabei gibt es die Arten Haftpflicht (HP), Haftpflicht mit Teilkasko (TK), Vollkasko (VK).
- Verweis auf den Versicherungsnehmer (Pflicht)
- Verweis auf das Fahrzeug (Pflicht, eindeutig)
- Verweis auf den Mitarbeiter, der den Vertrag bearbeitet (Pflicht)
Der Versicherungsnehmer ist gekennzeichnet durch verschiedene Eigenschaften:
- Kundennummer (Pflicht, eindeutig)
- Name und Anschrift: PLZ, Ort, Straße, Hausnummer (Pflicht)
- bei natürlichen Personen zusätzlich durch Vorname, Geburtsdatum, Datum des Führerscheins (optional)
- Verweis auf eine "Fremdversicherung", wenn ein (fremdes) Fahrzeug an einem Unfall beteiligt ist (optional)
Das Fahrzeug ist gekennzeichnet durch diese Eigenschaften:
- polizeiliches Kennzeichen (Pflicht, eindeutig)
- Farbe (optional)
- Fahrzeugtyp und damit indirekt auch den Fahrzeughersteller (Pflicht)
Ein Mitarbeiter ist gekennzeichnet durch diese Eigenschaften:
- Name, Vorname, Geburtsdatum (Pflicht)
- Personalnummer (Pflicht, eindeutig)
- Verweis auf Abteilung, Vermerk, ob es sich um den Leiter der Abteilung handelt (Pflicht)
- Kontaktdaten wie Telefon, Mobiltelefon, Email, Raum (optional)
Die Abteilung ist gekennzeichnet durch diese Eigenschaften:
- Nummer (Pflicht, eindeutig)
- Kurzbezeichnung, Bezeichnung (Pflicht, eindeutig)
- Ort (optional)
Zusätzlich gibt es Dienstwagen. Dabei handelt es sich um eine Tabelle mit den gleichen Eigenschaften wie bei Fahrzeug und zusätzlich:
- Verweis auf den Mitarbeiter, zu dem ein Dienstwagen gehört (optional)
- denn es gibt auch Firmenwagen, die keinem Mitarbeiter persönlich zugeordnet sind
Ein Schadensfall ist gekennzeichnet durch diese Eigenschaften:
- Datum, Ort und Umstände des Unfalls (Pflicht)
- Vermerk, ob es Verletzte gab, sowie Höhe des Gesamtschadens (optional, denn die Angaben könnten erst später bekannt werden)
- Verweis auf den Mitarbeiter, der den Schadensfall bearbeitet (Pflicht)
An einem Schadensfall können mehrere Fahrzeuge unterschiedlicher Versicherungen beteiligt sein. (Unfälle mit Radfahrern und Fußgängern werden nicht betrachtet.) Deshalb gibt es eine weitere Tabelle Zuordnung_SF_FZ:
- Liste aller Schadensfälle und aller beteiligten Fahrzeuge (Pflicht)
- anteilige Schadenshöhe eines Fahrzeugs an dem betreffenden Unfall (optional)
Über die Verbindung Schadensfall → Fahrzeug → Versicherungsvertrag → Versicherungsnehmer → Versicherungsgesellschaft können alle beteiligten Gesellschaften festgestellt und in die Schadensabwicklung eingebunden werden.
Bitte beachten Sie auch die Hinweise unter Fehlende Spalten und Einschränkungen.
[Bearbeiten] Schematische Darstellung
Die vorstehende Struktur kann in folgendem Diagramm dargestellt werden. Die Verweise, nämlich die Verknüpfungen zwischen den Tabellen sind daraus so abzulesen:
- Von jeder Tabelle gibt es einen Verweis auf einen Eintrag in der direkt darunter stehenden Tabelle.
- Zu jedem Vertrag gehört ein Sachbearbeiter; das wird durch den Pfeil nach links außen angedeutet, der in der Tabelle Mitarbeiter von rechts hereinkommt.
- Zu jedem Vertrag gehört genau ein Fahrzeug; zu jedem Fahrzeug gehört ein Vertrag.
- Jeder Eintrag in der Liste der Zuordnungen Schadensfall, Fahrzeug bezieht sich auf ein Fahrzeug und einen Schadensfall; dabei gilt:
- Zu jedem Schadensfall gehören ein oder mehrere Fahrzeuge.
- Nicht jedes Fahrzeug hat einen Schadensfall.
- Ein Dienstwagen kann einem Mitarbeiter zugeordnet sein, muss es aber nicht.
| ⇐ | Versicherungs- vertrag VV |
Zuordnung SF / FZ ZU |
⇒ | Schadensfall SF |
|||||||
| ⇓ | ⇓ ⇑ | ⇓ | ⇓ | ||||||||
| Versicherungs- nehmer VN |
Fahrzeug FZ |
Dienstwagen DW |
→ | Mitarbeiter MI |
⇐ | ||||||
| ↓ | ⇓ | ⇓ | ⇓ | ||||||||
| Versicherungs- gesellschaft VG |
Fahrzeugtyp FT |
Abteilung AB |
|||||||||
| ⇓ | |||||||||||
| Fahrzeug- hersteller FH |
|||||||||||
Doppelte Pfeile bezeichnen eine Muss-Verknüpfung, einfache eine Kann-Verknüpfung.
Die Kürzel bei den Tabellennamen werden künftig immer wieder als "Alias" anstelle des "Langnamens" verwendet. In dieser Übersicht wurden sie wegen der Deutlichkeit großgeschrieben; künftig werden sie kleingeschrieben.
[Bearbeiten] Tabellenstruktur
Im Anhang ist die Tabellenstruktur der Beispieldatenbank beschrieben. Mit dem Skript zur Erstellung der Datenbank wird sie bereits mit einigen Datensätzen für die Übungsaufgaben gefüllt. Innerhalb des Buches wird die Datenbankstruktur erweitert werden; dabei werden einige der Anmerkungen beachtet.
Für das Erstellen der Datenbank-Tabellen stehen folgende Skripte zur Verfügung (die Datenbank selbst muss zz. vorher manuell erstellt werden):
Eine kurze Beschreibung für die Installation ist in den Skripten enthalten. Für Details nutzen Sie bitte die Dokumentation des jeweiligen Datenbankmanagementsystems; Links dazu stehen in der Einleitung sowie unter Weblinks.
|
|
Achtung Die Datenbankstruktur wird zurzeit überarbeitet und stimmt deshalb nur teilweise mit dem Buch überein. Die Firebird-Version entspricht den aktuellen Überlegungen; die Beispiele wurden inzwischen angepasst. Die anderen Skripte zum Anlegen der Beispieldatenbank müssen noch überarbeitet werden. Zwischenstand am 30.10.2009; sobald alles erledigt ist, wird dieser Vermerk wieder entfernt. |
|---|
Im Kapitel Änderung der Datenbankstruktur wird auch ein Verfahren behandelt, mit dem schnell viele Datensätze zum Testen erzeugt werden können.
- Im Buch Perl-Programmierung gibt es ein Beispielskript, das ebenso eine große Menge Datensätze in die Beispieldatenbank einfügt. Dadurch stehen erheblich mehr Datensätze bereit als im "Erstellungsskript", sodass Abfragen praxisnäher ausgeführt werden. Mit MySQL auf einem UNIX/LINUX-System führen sie "perl beispielskript.pl|mysql -u Datenbankbenutzer -p MeineDatenbank" durch, und schon kann eine Abfrage ein Ergebnis erzeugen.
- Achtung: Auch dieses Perl-Skript muss an die geänderten Spalten- und Tabellennamen angepasst werden.
[Bearbeiten] Anmerkungen
Die Beispieldatenbank wurde erstellt und in dieses Buch eingefügt, als sich die Entwicklung des Buches noch ziemlich am Anfang befand. Im Laufe der Zeit wurden bei der Konzeption und der Umsetzung in Beispielen Unstimmigkeiten und Mängel festgestellt. Auf diese soll hier hingewiesen werden; teilweise wurden sie beseitigt, teilweise werden wir die Datenstruktur in späteren Kapiteln ändern, teilweise belassen wir es dabei.
[Bearbeiten] Namen von Tabellen und Spalten
Es ist üblicher, eine Tabelle im Singular zu bezeichnen.
- Es ist einfacher zu sagen: Ein Versicherungsvertrag hat diese Eigenschaften… Umständlicher wäre: Ein Datensatz der Tabelle Versicherungsverträge hat diese Eigenschaften…
- Vor allem beim Programmieren werden die Namen mit anderen Begriffen verknüpft; es käme dann etwas wie VertraegePositionenRow zustande – also die Positionen von Verträgen –, wo nur eine einzelne Zeile, nämlich eine Position eines Vertrags gemeint ist.
- Aus diesem Grund wurden die o.g. SQL-Skripte nochmals vollständig überarbeitet; alle Tabellennamen benutzen jetzt den Singular.
- Kürzlich gab es in einem Programmiererforum eine kleine Diskussion über "Singular oder Plural". Dort wurde das als uraltes Streitthema bezeichnet, das zu heftigsten Auseinandersetzungen führen kann. Im Ergebnis sollte es eher als Geschmackssache angesehen werden. Also gilt in diesem Buch mein Geschmack. Wer mag, kann für sich alle Tabellennamen (im Skript für die Beispieldatenbank und in allen Beispielen und Übungen) ändern. Benutzer:Juetho
Die Spalten von Tabellen sollen den Inhalt klar angeben; das ist durchgehend berücksichtigt worden. Es ist nicht üblich, ein Tabellenkürzel als Teil des Spaltennamens zu verwenden; denn ein Spaltenname wird immer in Zusammenhang mit einer bestimmten Tabelle benutzt.
- Bei einer früheren Version der Beispieldatenbank wurden diese Kürzel entfernt.
Es ist praktischer, englische Begriffe für Tabellen und Spalten zu verwenden. Programmierer müssen die Namen weiterverarbeiten; weil sowohl Datenbanken als auch Programmiersprachen mit Englisch arbeiten, entstehen ständig komplexe Namen. Dabei ist ein Begriff wie CustomerNumberChanged (also reines Englisch) immer noch besser als etwas wie KundeNummerChanged, also denglischer Mischmasch.
- Auf diese Änderung verzichte ich in diesem Buch, weil ich nicht alle Beispiele so umfangreich ändern will. Außerdem spielt die Weiterverwendung durch Programmierer in diesem Buch eigentlich keine Rolle.
[Bearbeiten] Aufteilung der Tabellen
Die Aufgaben der Versicherungsgesellschaft können nur eingeschränkt behandelt werden. Es fehlen Tabellen für Abrechnung und Zahlung der Prämien sowie Abrechnung der Kosten für Schadensfälle.
- Auf diese Aufgaben wird hier verzichtet.
Die Tarife und damit die Höhe der Basisprämie gehören in eine eigene Tabelle; ein Versicherungsvertrag müsste darauf verweisen.
- Auf diese Aufteilung wird wegen der Vereinfachung verzichtet.
Sowohl Mitarbeiter als auch Versicherungsnehmer sind Personen: Auch zu einem Versicherungsnehmer passen Kontaktdaten; auch bei einem Mitarbeiter ist die Privatanschrift von Bedeutung. Es wäre deshalb vernünftig, eine gemeinsame Tabelle Person einzurichten; die Tabelle Mitarbeiter würde Verweise auf diese Tabelle erhalten.
- Auf diese Änderung wird wegen der Vereinfachung verzichtet.
Man kann darüber streiten, ob der optionale Verweis auf eine fremde Versicherungsgesellschaft besser zum Versicherungsvertrag oder zum Versicherungsnehmer gehört.
- Wegen dieser Unklarheit wird auf eine Änderung verzichtet.
Die Verknüpfungen zwischen den Tabellen fehlten in der Ursprungsversion der Beispieldatenbank vollständig. Sie werden in den Kapiteln Fremdschlüssel-Beziehungen besprochen und in Änderung der Datenbankstruktur eingebaut.
[Bearbeiten] Fehlende Spalten und Einschränkungen
Neben den bereits genannten Punkten sind weitere Spalten sinnvoll bzw. notwendig:
- Tabelle Versicherungsvertrag: Basisprämie, Prämiensatz, letzte Änderung des Prämiensatzes
- Tabelle Zuordnung_SC_FZ: Anteil am Verschulden
- Tabellen Versicherungsnehmer und Mitarbeiter: Geschlecht m/w wegen der Anrede in Briefen
Viele Einschränkungen fehlen in der ursprünglichen Version:
- Spalten, deren Werte eindeutig sein müssen
- Spalten, deren Werte nur bestimmte Werte annehmen dürfen
- Tabellen, bei denen Spaltenwerte in gewisser Beziehung stehen müssen
Diese Änderungen werden in DDL - Einzelheiten besprochen und in Änderung der Datenbankstruktur eingebaut.
