Parte 2. Struttura del DBMS, tabelle e tipi di dati

Prima parte Continuiamo a creare il nostro semplice emulatore di borsa. Ecco cosa faremo:

• Creiamo un diagramma di organizzazione del database.
• Descriveremo cosa, come e dove viene archiviato.
• Scopriamo come i dati sono correlati tra loro.
• Iniziamo ad apprendere le basi di SQL utilizzando l'esempio del comando di creazione di tabelle SQL CREATE TABLE , Data Definition Language ( DDL ) del linguaggio SQL.
• Continuiamo a scrivere il programma Java. Implementiamo le principali funzioni del DBMS in termini di java.sql per creare il nostro database a livello di programmazione, utilizzando JDBC e un'architettura a tre livelli.

Queste due parti si sono rivelate più voluminose, poiché dobbiamo familiarizzare con le basi di SQL e con l'organizzazione di un DBMS dall'interno e tracciare analogie con Java. Per non annoiarvi con i listati dei codici, alla fine ci sono i collegamenti al corrispondente repository github di commit con il programma.

Progettazione DBMS

Descrizione dell'applicazione

Hai già sentito che l'organizzazione dell'archiviazione dei dati è parte integrante della programmazione. Permettimi di ricordarti che lo scopo della nostra applicazione è la più semplice emulazione di scambio:

• Ci sono azioni il cui valore può variare durante la giornata di negoziazione secondo determinate regole;
• ci sono trader con capitale iniziale;
• i trader possono acquistare e vendere azioni in base al loro algoritmo.

Lo scambio opera in tick - periodi di tempo fissi (nel nostro caso - 1 minuto). Durante un tick, il prezzo delle azioni può cambiare e quindi il trader può acquistare o vendere azioni.

Scambia la struttura dei dati di emulazione

Chiamiamo modelli di entità di scambio individuali. Per evitare errori di arrotondamento, lavoreremo con gli importi finanziari attraverso una classe BigDecimal(i dettagli sono disponibili nel collegamento alla fine dell'articolo). Descriviamo più nel dettaglio la struttura di ciascun modello: Promozione: Prezzo delle azioni: Commerciante: Azioni del commerciante: Per garantire l'unicità di ciascun modello, aggiungeremo un attributo iddi tipo long . Questo attributo sarà univoco all'interno delle istanze del modello e lo identificherà in modo univoco. Gli attributi che fanno riferimento ad altri modelli (trader, azioni, prezzo delle azioni) possono utilizzare questo idper identificare in modo univoco il modello corrispondente. Viene subito in mente il pensiero che potremmo utilizzare Map<Long, Object> per archiviare tali dati, dov'è Objectil modello corrispondente. Tuttavia, prova a implementarlo nel codice nelle seguenti condizioni:

• la dimensione dei dati supera notevolmente la quantità di RAM disponibile;
• è previsto l'accesso ai dati da una dozzina di luoghi diversi;
• è richiesta la capacità di modificare e leggere simultaneamente i dati;
• è necessario garantire regole per la formazione e l'integrità dei dati;

...e ti troverai ad affrontare compiti che richiedono qualifiche adeguate e tempo per essere implementati. Non è necessario “reinventare la ruota”. Molto è già stato pensato e scritto per noi. Quindi utilizzeremo quanto già sperimentato negli anni.

Memorizzazione dei dati in Java

Consideriamo l'azione. In Java, abbiamo creato una classe specifica per questo modello Sharecon i campi name, changeProbability, startPrice, delta. E molte condivisioni sono state archiviate come Map<Long, Share>, dove la chiave è un identificatore univoco per ciascuna condivisione.

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

Per accedere alla promozione desiderata tramite ID utilizzare il metodo shares.get(id). Per trovare un titolo in base al nome o al prezzo, dovremmo scorrere tutti i record cercando quello di cui abbiamo bisogno e così via. Ma andremo dall'altra parte e memorizzeremo i valori nel DBMS.

Archiviazione dei dati in un DBMS

Formuliamo un insieme iniziale di regole di archiviazione dei dati per un DBMS:

• I dati in un DBMS sono organizzati in tabelle ( TABLE ), che sono un insieme di record.
• Tutti i record hanno gli stessi set di campi. Vengono impostati durante la creazione della tabella.
• Il campo può essere impostato su un valore predefinito ( DEFAULT ).
• Per una tabella è possibile impostare dei vincoli ( CONSTRAINT ) che descrivono i requisiti affinché i suoi dati ne garantiscano l'integrità. Questo può essere fatto nella fase di creazione della tabella ( CREATE TABLE ) o aggiunto successivamente ( ALTER TABLE ... ADD CONSTRAINT ).
• I VINCOLI più comuni :
  • La chiave primaria è PRIMARIA (Id nel nostro caso).
  • Campo valore univoco UNICO (VIN per la tabella veicoli).
  • Verifica del campo CHECK (il valore percentuale non può essere maggiore di 100). Una delle restrizioni private su un campo è NOT NULL o NULL , che proibisce/consente di memorizzare NULL in un campo di tabella.
  • Collegamento a una tabella di terze parti FOREIGN KEY (collegamento a un'azione nella tabella dei prezzi delle azioni).
  • Index INDEX (indicizzazione di un campo per velocizzare la ricerca dei valori al suo interno).
  • La modifica di un record ( INSERT , UPDATE ) non avverrà se i valori dei suoi campi contraddicono le restrizioni (CONSTRAINT).
• Ogni tabella può avere uno o più campi chiave che possono essere utilizzati per identificare in modo univoco un record. Tale campo (o campi, se formano una chiave composta) costituisce la chiave primaria della tabella - PRIMARY KEY .
  • La chiave primaria garantisce l'unicità di un record nella tabella; su di essa viene creato un indice che dà accesso rapido all'intero record in base al valore della chiave.
  • Avere una chiave primaria rende molto più semplice creare collegamenti tra tabelle. Successivamente utilizzeremo una chiave primaria artificiale: per il primo record id = 1, ogni record successivo verrà inserito nella tabella con il valore id aumentato di uno. Questa chiave è spesso chiamata AutoIncrement o AutoIdentity .

In realtà, una tabella di azioni: è possibile utilizzare il nome delle azioni come chiave in questo caso? In generale sì, ma esiste la possibilità che alcune società emettano azioni diverse e le chiami solo con il proprio nome. In questo caso non ci sarà più unicità. In pratica, una chiave primaria artificiale viene utilizzata abbastanza spesso. D'accordo, l'utilizzo di un nome completo come chiave univoca in una tabella contenente record di persone non garantirà l'unicità. Oltre a utilizzare una combinazione di nome completo e data di nascita.

Tipi di dati nel DBMS

Come qualsiasi altro linguaggio di programmazione, SQL prevede la tipizzazione dei dati. Ecco i tipi di dati SQL più comuni: Tipi interi Vero Corda data e ora Archiviazione di grandi volumi di dati

Stile di scrittura SQL

Per molte lingue esistono linee guida per la formattazione del codice. Tipicamente, tali documenti contengono regole per nominare variabili, costanti, metodi e altre strutture linguistiche. Quindi, per Python c'è PEP8, per Java - Oracle Code Conventions for Java . Sono stati creati diversi set per SQL, leggermente diversi tra loro. Indipendentemente da ciò, dovresti sviluppare l'abitudine di seguire le regole durante la formattazione del codice, soprattutto se lavori in gruppo. Le regole potrebbero essere, ad esempio, le seguenti (ovviamente puoi sviluppare un diverso insieme di regole per te stesso, l'importante è attenervisi in futuro):

• Le parole chiave e le parole riservate, inclusi comandi e operatori, devono essere scritte in maiuscolo: CREATE TABLE, CONSTRAINT...
• I nomi di tabelle, campi e altri oggetti non devono coincidere con le parole chiave del linguaggio SQL (vedi collegamento a fine articolo), ma possono contenerle.
• I nomi delle tabelle dovrebbero riflettere il loro scopo. Sono scritti in lettere minuscole. Le parole nel nome sono separate l'una dall'altra da un trattino basso. La parola alla fine deve essere al plurale : traders (commercianti), share_rates (tasso azionario).
• I nomi dei campi della tabella dovrebbero riflettere il loro scopo. Devono essere scritti in lettere minuscole, le parole nel nome devono essere formattate in stile Camel Case e la parola alla fine deve essere utilizzata al singolare : name (nome), share_rates (tasso di partecipazione).
• I campi chiave artificiali devono contenere la parola id.
• I nomi VINCENTI devono seguire le convenzioni di denominazione delle tabelle. Devono inoltre includere i campi e le tabelle in essi coinvolti, che iniziano con un prefisso semantico: check_ (controllo del valore del campo), pk_ (chiave primaria), fk_ (chiave esterna), uniq_ (unicità del campo), idx_ (indice). Esempio: pk_traider_share_actions_id (chiave primaria nel campo id per la tabella trader_share_actions).
• E così via, mentre studi SQL, l'elenco delle regole verrà riempito/modificato.

Progettazione DBMS

Immediatamente prima di creare un DBMS, è necessario progettarlo. Lo schema finale contiene tabelle, un insieme di campi, VINCOLI, chiavi, condizioni predefinite per i campi, relazioni tra tabelle e altre entità del database. Su Internet puoi trovare molti designer gratuiti online/offline per la progettazione di piccoli DBMS. Prova a digitare qualcosa come "Database Designer gratuito" in un motore di ricerca. Tali applicazioni hanno utili proprietà aggiuntive:

• Può generare comandi SQL per creare un DBMS.
• Visualizzare visivamente le impostazioni sul diagramma.
• Consente di spostare le tabelle per una migliore visualizzazione.
• Mostra chiavi, indici, relazioni, valori predefiniti e simili nel diagramma.
• Possono archiviare in remoto lo schema DBMS.

Ad esempio, dbdiffo.com evidenzia le chiavi, mostra i campi non vuoti e i contatori AI (AutoIncrement) con l'etichetta NN:

Creazione di tabelle in un DBMS

Quindi abbiamo un diagramma. Passiamo ora alla creazione delle tabelle (CREATE TABLE). Per fare ciò, è consigliabile disporre di dati preliminari:

• nome della tabella
• nomi e tipi di campo
• restrizioni (VINCOLI) sui campi
• valori predefiniti per i campi (se disponibili)
• chiave primaria (PRIMARY KEY) se disponibile
• connessioni tra tabelle (CHIAVE ESTERA)

Non studieremo in dettaglio tutte le opzioni del comando CREATE TABLE, esamineremo le basi di SQL usando l'esempio della creazione di una tabella per i trader:

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)

Diamo uno sguardo più da vicino:

• CREATE TABLE traiders(descrizione campo) - crea una tabella con il nome specificato; nella descrizione i campi sono separati da una virgola. Qualsiasi comando termina con un punto e virgola.
• La descrizione del campo inizia con il nome, seguito dal tipo, CONSTRAINT e dal valore predefinito.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– il campo id di tipo intero è una chiave primaria e un contatore incrementale (per ogni nuovo record per il campo id verrà generato un valore che è uno in più rispetto a quello precedentemente creato per questa tabella).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– campo cassa, decimale, 15 cifre prima della virgola e due dopo (dati finanziari, ad esempio, dollari e centesimi). Non è possibile accettare valori NULL. Se non viene fornito alcun valore, otterrà il valore 1000.
• about VARCHAR(255) NULL– il campo about, una stringa lunga fino a 255 caratteri, può accettare valori vuoti.

Tieni presente che possiamo impostare parte delle condizioni CONSTRAINT dopo aver creato la tabella. Consideriamo la costruzione per modificare la struttura della tabella e i suoi campi: ALTER TABLE nome_tabella ADD CONSTRAINT nome_vincolo CHECK (condizione) utilizzando gli esempi:

• CHECK(tradingMethod IN (1,2,3))– il campo tradingMethod può assumere solo valori 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– il campo changeProbability può assumere valori interi compresi tra 1 e 100

Relazioni tra tabelle

Per analizzare la descrizione delle relazioni tra le tabelle, diamo un'occhiata alla creazione di share_rates:

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)

Un riferimento ai valori di un'altra tabella può essere impostato come segue: ALTER TABLEtabella_da_quale_riferito ADD FOREIGN KEY(campo_quello_riferito) REFERENCEStabella_a_quale_riferito (campo_che_riferito a) Lasciamo in share abbiamo record sulle azioni, ad esempio, per id=50 memorizziamo azioni Microsoft con un prezzo iniziale di 17,5, un delta di 20 e una possibilità di variazione del 4%. Per la tabella share_rates otteniamo tre proprietà principali:

• Dobbiamo solo memorizzare nel campo share solo il valore della chiave id della tabella share per poterlo utilizzare per ottenere le restanti informazioni (nome, ecc.) dalla tabella share.
• Non possiamo creare una tariffa per una promozione inesistente. Non è possibile inserire un valore inesistente nel campo condivisione (per il quale non esiste alcun record nella tabella condivisioni con questo id), poiché non ci sarà corrispondenza tra le tabelle.
• Non possiamo eliminare una voce azionaria nelle azioni per le quali i tassi sono impostati in share_rates.

Gli ultimi due punti servono a garantire l'integrità dei dati memorizzati. Puoi vedere la creazione di tabelle SQL della nostra emulazione ed esempi di query SQL nell'implementazione Java dei metodi delle classi corrispondenti utilizzando il collegamento al repository github alla fine dell'articolo. La terza parte