Parte 2. Estrutura, tabelas e tipos de dados do SGBD

Primeira parte Continuamos a criar nosso emulador simples de bolsa de valores. Aqui está o que faremos:

• Vamos criar um diagrama de organização do banco de dados.
• Descreveremos o que, como e onde ele é armazenado.
• Vamos descobrir como os dados estão relacionados entre si.
• Vamos começar a aprender o básico de SQL usando o exemplo do comando de criação de tabela SQL CREATE TABLE , Data Definition Language ( DDL ) da linguagem SQL.
• Vamos continuar escrevendo o programa Java. Implementamos as principais funções do SGBD em termos de java.sql para criar nosso banco de dados de forma programática, usando JDBC e uma arquitetura de três camadas.

Essas duas partes acabaram sendo mais volumosas, pois precisamos nos familiarizar com os fundamentos do SQL e da organização de um SGBD por dentro, e fazer analogias com Java. Para não aborrecê-lo com listagens de código, no final há links para o repositório github de commit correspondente com o programa.

Projeto de SGBD

Descrição do aplicativo

Você já ouviu falar que organizar o armazenamento de dados é parte integrante da programação. Deixe-me lembrá-lo de que o objetivo do nosso aplicativo é a emulação de troca mais simples:

• Existem ações cujo valor pode variar durante o dia de negociação de acordo com determinadas regras;
• existem traders com capital inicial;
• os comerciantes podem comprar e vender ações de acordo com seu algoritmo.

A bolsa opera em ticks - períodos fixos de tempo (no nosso caso - 1 minuto). Durante um tick, o preço das ações pode mudar e então o trader pode comprar ou vender ações.

Estrutura de dados de emulação do Exchange

Vamos chamar modelos de entidades de troca individuais. Para evitar erros de arredondamento, trabalharemos com valores financeiros por meio de uma aula BigDecimal(detalhes podem ser encontrados no link no final do artigo). Vamos descrever mais detalhadamente a estrutura de cada modelo: Promoção: Compartilhar preços: Comerciante: Ações do comerciante: Para garantir a exclusividade de cada modelo, adicionaremos um atributo iddo tipo long . Este atributo será exclusivo nas instâncias do modelo e o identificará exclusivamente. Atributos que fazem referência a outros modelos (comerciante, ação, preço da ação) podem usar este idpara identificar exclusivamente o modelo correspondente. Imediatamente vem à mente o pensamento de que poderíamos usar Map<Long, Object> para armazenar esses dados, onde Objectestá o modelo correspondente. No entanto, tente implementar isso no código nas seguintes condições:

• o tamanho dos dados excede significativamente a quantidade de RAM disponível;
• espera-se que o acesso aos dados venha de uma dúzia de lugares diferentes;
• é necessária a capacidade de modificar e ler dados simultaneamente;
• é necessário garantir regras de formação e integridade dos dados;

...e você se deparará com tarefas que exigem qualificações adequadas e tempo para serem implementadas. Não há necessidade de reinventar a roda". Muito já foi pensado e escrito para nós. Então usaremos o que já foi testado ao longo dos anos.

Armazenando dados em Java

Vamos considerar a ação. Em Java, criamos uma classe específica para este modelo Sharecom campos name, changeProbability, startPrice, delta. E muitos compartilhamentos foram armazenados como Map<Long, Share>, onde a chave é um identificador exclusivo para cada compartilhamento.

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

Para acessar a promoção desejada por ID, utilize o método shares.get(id). Para a tarefa de encontrar uma ação por nome ou preço, percorreríamos todos os registros procurando aquela de que precisamos e assim por diante. Mas iremos por outro caminho e armazenaremos os valores no SGBD.

Armazenamento de dados em um SGBD

Vamos formular um conjunto inicial de regras de armazenamento de dados para um SGBD:

• Os dados em um SGBD são organizados em tabelas ( TABLE ), que são um conjunto de registros.
• Todos os registros possuem os mesmos conjuntos de campos. Eles são definidos ao criar a tabela.
• O campo pode ser definido com um valor padrão ( DEFAULT ).
• Para uma tabela, você pode definir restrições ( CONSTRAINT ) que descrevem os requisitos de seus dados para garantir sua integridade. Isso pode ser feito na fase de criação da tabela ( CREATE TABLE ) ou adicionado posteriormente ( ALTER TABLE... ADD CONSTRAINT ).
• A RESTRIÇÃO mais comum :
  • A chave primária é PRIMARY (Id no nosso caso).
  • Campo de valor único UNIQUE (VIN para a tabela de veículos).
  • Verificando o campo CHECK (o valor percentual não pode ser maior que 100). Uma das restrições privadas em um campo é NOT NULL ou NULL , que proíbe/permite armazenar NULL em um campo de tabela.
  • Link para uma tabela de terceiros FOREIGN KEY (link para uma ação na tabela de preços de ações).
  • Indexar INDEX (indexar um campo para agilizar a busca por valores nele).
  • A modificação de um registro ( INSERT , UPDATE ) não ocorrerá se os valores de seus campos contrariarem as restrições (CONSTRAINT).
• Cada tabela pode ter um campo-chave (ou vários) que podem ser usados ​​para identificar exclusivamente um registro. Tal campo (ou campos, se formarem uma chave composta) forma a chave primária da tabela - PRIMARY KEY .
  • A chave primária garante a exclusividade de um registro na tabela; nela é criado um índice que dá acesso rápido a todo o registro com base no valor da chave.
  • Ter uma chave primária facilita muito a criação de links entre tabelas. A seguir, usaremos uma chave primária artificial: para o primeiro registro id = 1, cada registro subsequente será inserido na tabela com o valor do id aumentado em um. Essa chave geralmente é chamada de AutoIncrement ou AutoIdentity .

Na verdade, uma tabela de ações: É possível usar o nome da ação como chave neste caso? Em geral - sim, mas existe a possibilidade de que alguma empresa emita ações diferentes e as chame apenas pelo seu próprio nome. Neste caso, não haverá mais exclusividade. Na prática, uma chave primária artificial é usada com bastante frequência. Concordo, usar um nome completo como chave exclusiva em uma tabela contendo registros de pessoas não garantirá exclusividade. Além de usar uma combinação de nome completo e data de nascimento.

Tipos de dados em DBMS

Como qualquer outra linguagem de programação, SQL possui digitação de dados. Aqui estão os tipos de dados SQL mais comuns: Tipos inteiros Real Corda data e hora Armazenamento de grandes volumes de dados

Estilo de escrita SQL

Para muitas linguagens, existem diretrizes de formatação de código. Normalmente, esses documentos contêm regras para nomear variáveis, constantes, métodos e outras estruturas de linguagem. Então, para Python existe o PEP8, para Java - Oracle Code Conventions for Java . Vários conjuntos diferentes foram criados para SQL, que são ligeiramente diferentes uns dos outros. Independentemente disso, você deve desenvolver o hábito de seguir regras ao formatar seu código, especialmente se trabalhar em equipe. As regras poderiam ser, por exemplo, as seguintes (é claro, você pode desenvolver um conjunto diferente de regras para si mesmo, o principal é cumpri-las no futuro):

• Palavras-chave e palavras reservadas, incluindo comandos e operadores, devem ser escritas em letras maiúsculas: CREATE TABLE, CONSTRAINT...
• Os nomes de tabelas, campos e outros objetos não devem coincidir com as palavras-chave da linguagem SQL (veja o link no final do artigo), mas podem contê-las.
• Os nomes das tabelas devem refletir sua finalidade. Eles são escritos em letras minúsculas. As palavras do nome são separadas umas das outras por sublinhados. A palavra no final deve estar no plural : traders (comerciantes), share_rates (taxa de ações).
• Os nomes dos campos da tabela devem refletir sua finalidade. Devem ser escritas em letras minúsculas, as palavras do nome devem ser formatadas no estilo Camel Case , e a palavra no final deve ser usada no singular : name (nome), share_rates (taxa de ações).
• Os campos-chave artificiais devem conter a palavra id.
• Os nomes CONSTRAINT devem seguir as convenções de nomenclatura de tabelas. Devem também incluir os campos e tabelas neles envolvidos, começar com um prefixo semântico: check_ (verificar o valor do campo), pk_ (chave primária), fk_ (chave estrangeira), uniq_ (exclusividade do campo), idx_ (índice). Exemplo: pk_traider_share_actions_id (chave primária no campo id da tabela trader_share_actions).
• E assim por diante, conforme você estuda SQL, a lista de regras será reabastecida/alterada.

Projeto de SGBD

Imediatamente antes de criar um SGBD, ele precisa ser projetado. O esquema final contém tabelas, um conjunto de campos, CONSTRAINT, chaves, condições padrão para campos, relacionamentos entre tabelas e outras entidades do banco de dados. Na Internet você pode encontrar muitos designers online/offline gratuitos para projetar pequenos SGBDs. Tente digitar algo como “Database designer free” em um mecanismo de busca. Esses aplicativos têm propriedades adicionais úteis:

• Pode gerar comandos SQL para criar um SGBD.
• Exiba visualmente as configurações no diagrama.
• Permite mover tabelas para melhor visualização.
• Mostre chaves, índices, relacionamentos, valores padrão e similares no diagrama.
• Eles podem armazenar remotamente o esquema DBMS.

Por exemplo, dbdiffo.com destaca chaves, mostra campos não vazios e contadores AI (AutoIncrement) com o rótulo NN:

Criando tabelas em um SGBD

Então temos um diagrama. Agora vamos passar à criação de tabelas (CREATE TABLE). Para isso, é aconselhável que tenhamos dados preliminares:

• Nome da tabela
• nomes e tipos de campos
• restrições (RESTRIÇÕES) em campos
• valores padrão para campos (se disponíveis)
• chave primária (CHAVE PRIMÁRIA) se disponível
• conexões entre tabelas (FOREIGN KEY)

Não estudaremos detalhadamente todas as opções do comando CREATE TABLE, veremos os fundamentos do SQL usando o exemplo de criação de uma tabela para traders:

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)

Vamos olhar mais de perto:

• CREATE TABLE traiders(descrição do campo) - cria uma tabela com o nome especificado; na descrição os campos são separados por vírgula. Qualquer comando termina com ponto e vírgula.
• A descrição do campo começa com seu nome, seguido por seu tipo, CONSTRAINT e valor padrão.
• id BIGINT AUTO_INCREMENT PRIMARY KEY– o campo id de tipo inteiro é uma chave primária e um contador incremental (para cada novo registro para o campo id será gerado um valor maior que o criado anteriormente para esta tabela).
• cash DECIMAL(15,2) NOT NULL DEFAULT 1000– campo caixa, decimal, 15 dígitos antes da vírgula e dois depois (dados financeiros, por exemplo, dólares e centavos). Não é possível aceitar valores NULL. Se nenhum valor for fornecido, obterá o valor 1000.
• about VARCHAR(255) NULL– o campo about, uma string de até 255 caracteres, pode aceitar valores vazios.

Observe que podemos definir parte das condições CONSTRAINT após criar a tabela. Vamos considerar a construção para modificar a estrutura da tabela e seus campos: ALTER TABLE nome_tabela ADD CONSTRAINT nome_restrição CHECK (condição) usando exemplos:

• CHECK(tradingMethod IN (1,2,3))– o campo tradingMethod só pode assumir valores 1,2,3
• CHECK(changeProbability <= 100 AND changeProbability > 0)– o campo changeProbability pode assumir valores inteiros no intervalo de 1 a 100

Relacionamentos entre tabelas

Para analisar a descrição dos relacionamentos entre tabelas, vejamos a criação de 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)

Um link para os valores de outra tabela pode ser definido da seguinte forma: ALTER TABLEtable_from_ which_is_referred ADD FOREIGN KEY(field_ which_referred) REFERENCEStable_to_ which_is_referenced (field_ which_is_referenced) Deixe em ações temos registros de ações, por exemplo, para id=50 armazenamos ações da Microsoft com um preço inicial de 17,5 , delta 20 e chance de mudança de 4%. Para a tabela share_rates obtemos três propriedades principais:

• Basta armazenar no campo share apenas o valor da chave id da tabela de compartilhamentos para utilizá-lo para obter as demais informações (nome, etc.) da tabela de compartilhamentos.
• Não podemos criar uma tarifa para uma promoção inexistente. Não é possível inserir no campo de compartilhamento um valor inexistente (para o qual não haja registro na tabela de compartilhamentos com este id), pois não haverá correspondência entre as tabelas.
• Não podemos excluir uma entrada de ações para as quais as taxas estão definidas em share_rates.

Os dois últimos pontos servem para garantir a integridade dos dados armazenados. Você pode ver a criação de tabelas SQL de nossa emulação e exemplos de consultas SQL na implementação Java dos métodos das classes correspondentes usando o link para o repositório github no final do artigo. A terceira parte