Teil 2. DBMS-Struktur, Tabellen und Datentypen

Erster Teil Wir entwickeln weiterhin unseren einfachen Börsenemulator. Folgendes werden wir tun:

• Lassen Sie uns ein Datenbank-Organisationsdiagramm erstellen.
• Wir beschreiben, was, wie und wo gespeichert wird.
• Lassen Sie uns herausfinden, wie Daten miteinander zusammenhängen.
• Beginnen wir mit dem Erlernen der Grundlagen von SQL am Beispiel des SQL-Tabellenerstellungsbefehls CREATE TABLE , Data Definition Language ( DDL ) der SQL-Sprache.
• Fahren wir mit dem Schreiben des Java-Programms fort. Wir implementieren die Hauptfunktionen des DBMS in Form von java.sql für die programmgesteuerte Erstellung unserer Datenbank unter Verwendung von JDBC und einer dreistufigen Architektur.

Diese beiden Teile erwiesen sich als umfangreicher, da wir uns von innen mit den Grundlagen von SQL und der Organisation eines DBMS vertraut machen und Analogien zu Java ziehen müssen. Um Sie nicht mit Code-Auflistungen zu langweilen, finden Sie am Ende Links zum entsprechenden Commit-Github-Repository mit dem Programm.

DBMS-Design

Anwendungsbeschreibung

Sie haben bereits gehört, dass die Organisation der Datenspeicherung ein wesentlicher Bestandteil der Programmierung ist. Ich möchte Sie daran erinnern, dass der Zweck unserer Anwendung die einfachste Exchange-Emulation ist:

• Es gibt Aktien, deren Wert sich im Laufe des Handelstages nach vorgegebenen Regeln ändern kann;
• es gibt Händler mit Anfangskapital;
• Händler können Aktien gemäß ihrem Algorithmus kaufen und verkaufen.

Der Austausch erfolgt in Ticks – festen Zeiträumen (in unserem Fall 1 Minute). Während eines Ticks kann sich der Aktienkurs ändern und der Händler kann dann Aktien kaufen oder verkaufen.

Datenstruktur der Exchange-Emulation

Nennen wir einzelne Börseneinheiten Modelle. Um Rundungsfehler zu vermeiden, werden wir mit Finanzbeträgen über eine Klasse arbeiten BigDecimal(Details finden Sie im Link am Ende des Artikels). Lassen Sie uns die Struktur jedes Modells genauer beschreiben: Aktion: Aktienkurs: Händler: Händleraktionen: Um die Einzigartigkeit jedes Modells sicherzustellen, fügen wir ein Attribut idvom Typ long hinzu . Dieses Attribut ist innerhalb der Modellinstanzen eindeutig und identifiziert sie eindeutig. Attribute, die auf andere Modelle verweisen (Händler, Aktie, Aktienkurs), können dieses verwenden, idum das entsprechende Modell eindeutig zu identifizieren. Map<Long, Object> Mir kommt sofort der Gedanke in den Sinn, dass wir zum Speichern solcher Daten Objectdas entsprechende Modell verwenden könnten . Versuchen Sie jedoch, dies unter den folgenden Bedingungen im Code zu implementieren:

• die Datengröße übersteigt die Menge des verfügbaren RAM deutlich;
• Der Zugriff auf Daten wird von einem Dutzend verschiedener Stellen aus erwartet.
• die Fähigkeit, Daten gleichzeitig zu ändern und zu lesen, ist erforderlich;
• es ist notwendig, Regeln für die Bildung und Integrität von Daten sicherzustellen;

...und Sie werden mit Aufgaben konfrontiert, für deren Umsetzung entsprechende Qualifikationen und Zeit erforderlich sind. Es besteht keine Notwendigkeit, „das Rad neu zu erfinden“. Vieles wurde bereits für uns erdacht und geschrieben. Wir werden also das verwenden, was bereits über die Jahre getestet wurde.

Daten in Java speichern

Betrachten wir die Aktion. In Java haben wir für dieses Modell eine spezielle Klasse Sharemit den Feldern name, changeProbability, startPrice, erstellt delta. Und viele Freigaben wurden als gespeichert Map<Long, Share>, wobei der Schlüssel eine eindeutige Kennung für jede Freigabe ist.

public class Share {
    private String name;
    private BigDecimal startPrice;
    private int changeProbability;
    private int delta;
}
Map<Long, Share> shares = new HashMap<>();
shares.put(1L, new Share("ibm", BigDecimal.valueOf(20.0), 15, 10));
shares.put(2L, new Share("apple", BigDecimal.valueOf(14.0), 25, 15));
shares.put(3L, new Share("google", BigDecimal.valueOf(12.0), 20, 8));
...
shares.put(50L, new Share("microsoft", BigDecimal.valueOf(17.5), 10,4 ));

Um auf die gewünschte Aktion nach ID zuzugreifen, verwenden Sie die Methode shares.get(id). Um eine Aktie anhand ihres Namens oder Preises zu finden, durchsuchen wir alle Datensätze nach der benötigten Aktie und so weiter. Aber wir gehen den anderen Weg und speichern die Werte im DBMS.

Datenspeicherung in einem DBMS

Lassen Sie uns einen ersten Satz von Datenspeicherregeln für ein DBMS formulieren:

• Daten in einem DBMS sind in Tabellen ( TABLE ) organisiert, bei denen es sich um eine Reihe von Datensätzen handelt.
• Alle Datensätze verfügen über die gleichen Feldsätze. Sie werden beim Erstellen der Tabelle festgelegt.
• Das Feld kann auf einen Standardwert ( DEFAULT ) gesetzt werden.
• Für eine Tabelle können Sie Einschränkungen ( CONSTRAINT ) festlegen, die die Anforderungen an ihre Daten beschreiben, um deren Integrität sicherzustellen. Dies kann bei der Tabellenerstellung erfolgen ( CREATE TABLE ) oder später hinzugefügt werden ( ALTER TABLE ... ADD CONSTRAINT ).
• Die häufigste EINSCHRÄNKUNG :
  • Der Primärschlüssel ist PRIMARY (in unserem Fall die ID).
  • Eindeutiges Wertfeld UNIQUE (VIN für die Fahrzeugtabelle).
  • Überprüfung des CHECK- Feldes (Prozentwert darf nicht größer als 100 sein). Eine der privaten Einschränkungen für ein Feld ist NOT NULL oder NULL , die das Speichern von NULL in einem Tabellenfeld verbietet/erlaubt.
  • Link zu einer Fremdtabelle FOREIGN KEY (Link zu einer Aktie in der Aktienkurstabelle).
  • Index INDEX (Indizieren eines Felds, um die Suche nach darin enthaltenen Werten zu beschleunigen).
  • Die Änderung eines Datensatzes ( INSERT , UPDATE ) erfolgt nicht, wenn die Werte seiner Felder den Einschränkungen (CONSTRAINT) widersprechen.
• Jede Tabelle kann ein Schlüsselfeld (oder mehrere) haben, das zur eindeutigen Identifizierung eines Datensatzes verwendet werden kann. Ein solches Feld (oder Felder, wenn sie einen zusammengesetzten Schlüssel bilden) bildet den Primärschlüssel der Tabelle – PRIMARY KEY .
  • Der Primärschlüssel stellt die Eindeutigkeit eines Datensatzes in der Tabelle sicher; auf ihm wird ein Index erstellt, der basierend auf dem Schlüsselwert einen schnellen Zugriff auf den gesamten Datensatz ermöglicht.
  • Mit einem Primärschlüssel ist es viel einfacher, Verknüpfungen zwischen Tabellen zu erstellen. Als nächstes verwenden wir einen künstlichen Primärschlüssel: Für den ersten Datensatz id = 1wird jeder nachfolgende Datensatz mit einem um eins erhöhten ID-Wert in die Tabelle eingefügt. Dieser Schlüssel wird oft AutoIncrement oder AutoIdentity genannt .

Eigentlich eine Aktientabelle: Ist es in diesem Fall möglich, den Aktiennamen als Schlüssel zu verwenden? Im Großen und Ganzen ja, aber es besteht die Möglichkeit, dass ein Unternehmen unterschiedliche Aktien ausgibt und diese nur unter seinem eigenen Namen nennt. In diesem Fall liegt keine Eindeutigkeit mehr vor. In der Praxis wird häufig ein künstlicher Primärschlüssel verwendet. Stimmen Sie zu, dass die Verwendung eines vollständigen Namens als eindeutiger Schlüssel in einer Tabelle mit Personendatensätzen keine Eindeutigkeit gewährleistet. Außerdem kann eine Kombination aus vollständigem Namen und Geburtsdatum verwendet werden.

Datentypen im DBMS

Wie jede andere Programmiersprache verfügt auch SQL über Datentypisierung. Hier sind die gängigsten SQL-Datentypen: Ganzzahltypen Real Zeichenfolge Datum (und Uhrzeit Speicherung großer Datenmengen

SQL-Schreibstil

Für viele Sprachen gibt es Richtlinien zur Codeformatierung. Typischerweise enthalten solche Dokumente Regeln für die Benennung von Variablen, Konstanten, Methoden und anderen Sprachstrukturen. Für Python gibt es also PEP8, für Java - Oracle Code Conventions for Java . Für SQL wurden mehrere verschiedene Sets erstellt, die sich geringfügig voneinander unterscheiden. Unabhängig davon sollten Sie es sich zur Gewohnheit machen, bei der Formatierung Ihres Codes Regeln zu befolgen, insbesondere wenn Sie in einem Team arbeiten. Die Regeln könnten zum Beispiel folgende sein (Sie können natürlich auch ein anderes Regelwerk für sich entwickeln, Hauptsache, Sie halten sich in Zukunft daran):

• Schlüsselwörter und reservierte Wörter, einschließlich Befehle und Operatoren, müssen in Großbuchstaben geschrieben werden: CREATE TABLE, CONSTRAINT...
• Die Namen von Tabellen, Feldern und anderen Objekten sollten nicht mit Schlüsselwörtern der SQL-Sprache übereinstimmen (siehe Link am Ende des Artikels), können diese aber enthalten.
• Tabellennamen sollten ihren Zweck widerspiegeln. Sie werden in Kleinbuchstaben geschrieben. Wörter im Namen werden durch Unterstriche voneinander getrennt. Das Wort am Ende muss im Plural stehen : traders (Händler), share_rates (Aktienkurs).
• Tabellenfeldnamen sollten ihren Zweck widerspiegeln. Sie müssen in Kleinbuchstaben geschrieben werden, die Wörter im Namen müssen im Camel Case- Stil formatiert sein und das Wort am Ende muss im Singular verwendet werden : name (Name), share_rates (share rate).
• Künstliche Schlüsselfelder müssen das Wort id enthalten.
• CONSTRAINT-Namen müssen den Tabellenbenennungskonventionen entsprechen. Sie müssen außerdem die beteiligten Felder und Tabellen enthalten und mit einem semantischen Präfix beginnen: check_ (Überprüfung des Feldwerts), pk_ (Primärschlüssel), fk_ (Fremdschlüssel), uniq_ (Feldeindeutigkeit), idx_ (Index). Beispiel: pk_traider_share_actions_id (Primärschlüssel im ID-Feld für die Tabelle „trader_share_actions“).
• Und so weiter, während Sie SQL studieren, wird die Liste der Regeln ergänzt/geändert.

DBMS-Design

Unmittelbar vor der Erstellung eines DBMS muss es entworfen werden. Das endgültige Schema enthält Tabellen, eine Reihe von Feldern, CONSTRAINT, Schlüssel, Standardbedingungen für Felder, Beziehungen zwischen Tabellen und anderen Datenbankeinheiten. Im Internet finden Sie viele kostenlose Online-/Offline-Designer zum Entwerfen kleiner DBMS. Versuchen Sie, etwas wie „Datenbankdesigner kostenlos“ in eine Suchmaschine einzugeben. Solche Anwendungen verfügen über nützliche Zusatzeigenschaften:

• Kann SQL-Befehle generieren, um ein DBMS zu erstellen.
• Zeigen Sie die Einstellungen visuell im Diagramm an.
• Ermöglicht das Verschieben von Tabellen zur besseren Visualisierung.
• Zeigen Sie Schlüssel, Indizes, Beziehungen, Standardwerte und dergleichen im Diagramm an.
• Sie können das DBMS-Schema remote speichern.

Beispielsweise hebt dbdiffo.com Schlüssel hervor, zeigt nicht leere Felder und AI-Zähler (AutoIncrement) mit der Bezeichnung NN an:

Erstellen von Tabellen in einem DBMS

Wir haben also ein Diagramm. Kommen wir nun zum Erstellen von Tabellen (CREATE TABLE). Hierzu ist es ratsam, dass wir über vorläufige Daten verfügen:

• Tabellenname
• Feldnamen und -typ
• Einschränkungen (CONSTRAINTS) für Felder
• Standardwerte für Felder (falls verfügbar)
• Primärschlüssel (PRIMARY KEY), falls verfügbar
• Verbindungen zwischen Tabellen (FOREIGN KEY)

Wir werden nicht alle Optionen des CREATE TABLE-Befehls im Detail untersuchen, sondern uns die Grundlagen von SQL am Beispiel der Erstellung einer Tabelle für Händler ansehen:

CREATE TABLE traiders(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	freqTiсk INTEGER NOT NULL,
	cash  DECIMAL(15,2) NOT NULL DEFAULT 1000,
	tradingMethod INTEGER NOT NULL,
	changeProbability INTEGER NOT NULL DEFAULT 50,
	about VARCHAR(255) NULL
);
ALTER TABLE traiders ADD CONSTRAINT check_traiders_tradingMethod
	CHECK(tradingMethod IN (1,2,3));
ALTER TABLE traiders ADD CONSTRAINT check_traiders_changeProbability
	CHECK(changeProbability <= 100 AND changeProbability > 0)

Lass uns genauer hinschauen:

• CREATE TABLE traiders(Feldbeschreibung) – erstellt eine Tabelle mit dem angegebenen Namen; in der Beschreibung werden die Felder durch ein Komma getrennt. Jeder Befehl endet mit einem Semikolon.
• Die Feldbeschreibung beginnt mit ihrem Namen, gefolgt von Typ, CONSTRAINT und Standardwert.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– Das ID-Feld eines Ganzzahltyps ist ein Primärschlüssel und ein inkrementeller Zähler (für jeden neuen Datensatz für das ID-Feld wird ein Wert generiert, der um eins größer ist als der zuvor für diese Tabelle erstellte).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– Bargeldfeld, dezimal, 15 Stellen vor dem Komma und zwei danach (Finanzdaten, zum Beispiel Dollar und Cent). NULL-Werte können nicht akzeptiert werden. Wenn kein Wert angegeben wird, erhält es den Wert 1000.
• about VARCHAR(255) NULL– Das About-Feld, eine bis zu 255 Zeichen lange Zeichenfolge, kann leere Werte akzeptieren.

Beachten Sie, dass wir einen Teil der CONSTRAINT- Bedingungen nach dem Erstellen der Tabelle festlegen können . Betrachten wir die Konstruktion zum Ändern der Tabellenstruktur und ihrer Felder: ALTER TABLE Tabellenname ADD CONSTRAINT Einschränkungsname CHECK (Bedingung) anhand von Beispielen:

• CHECK(tradingMethod IN (1,2,3))– Das Feld TradingMethod kann nur die Werte 1,2,3 annehmen
• CHECK(changeProbability <= 100 AND changeProbability > 0)– Das Feld „changeProbability“ kann ganzzahlige Werte im Bereich von 1 bis 100 annehmen

Beziehungen zwischen Tabellen

Um die Beschreibung der Beziehungen zwischen Tabellen zu analysieren, schauen wir uns die Erstellung von share_rates an:

CREATE TABLE share_rates(
	id BIGINT AUTO_INCREMENT PRIMARY KEY,
	operDate datetime NOT NULL,
	share BIGINT NOT NULL,
	rate DECIMAL(15,2) NOT NULL
);
ALTER TABLE share_rates ADD FOREIGN KEY (share) REFERENCES shares(id)

Ein Verweis auf die Werte einer anderen Tabelle kann wie folgt gesetzt werden: ALTER TABLEtable_from_which_referred ADD FOREIGN KEY(field_that_referred) REFERENCEStable_to_which_referred (field_that_referred to) Für Aktien haben wir beispielsweise Datensätze zu Aktien, für id=50 speichern wir beispielsweise Microsoft-Aktien mit einem Anfangspreis von 17,5, ein Delta von 20 und eine Änderungschance von 4 %. Für die Tabelle „share_rates“ erhalten wir drei Haupteigenschaften:

• Wir müssen lediglich den Wert des ID-Schlüssels aus der Shares-Tabelle im Share-Feld speichern, um damit die restlichen Informationen (Name usw.) aus der Shares-Tabelle zu erhalten.
• Wir können keinen Preis für eine nicht vorhandene Aktion erstellen. Sie können keinen nicht vorhandenen Wert in das Aktienfeld einfügen (für den es in der Aktientabelle keinen Datensatz mit dieser ID gibt), da zwischen den Tabellen keine Entsprechung besteht.
• Wir können keinen Anteilseintrag in Anteilen löschen, für die in share_rates Tarife festgelegt sind.

Die letzten beiden Punkte dienen der Sicherstellung der Integrität der gespeicherten Daten. Die Erstellung von SQL-Tabellen unserer Emulation und Beispiele von SQL-Abfragen in der Java-Implementierung von Methoden der entsprechenden Klassen können Sie über den Link zum Github-Repository am Ende des Artikels sehen. Der dritte Teil