Projetando - designing

1 Como tabelas são criadas (`schema`)

Até o momento, aprendemos muitos comandos para realizar buscas em um banco de dados com tabelas já existentes. Mas como essas tabelas foram criadas? Podemos ver isso usando a palavra chave (do sqlite, não SQL) .schema.

Vamos ver quais comandos foram utilizados para criar o banco de dados longlist-2.db, primeiro acessando o banco pelo terminal de comandos, e depois pelo próprio SqliteStudio.

Terminal

Acesse o banco pelo termial, e digite .schema, a imagem abaixo deve aparecer:

A Imagem mostra todos os comando usados para criar as tabelas do banco. Parece muita coisa, mas eles estão todos juntos, olhando separadamente fica mais compreensível, por exemplo as primeiras 7 linhas:

CREATE TABLE IF NOT EXISTS "authors" (
    "id" INTEGER,
    "name" TEXT,
    "country" TEXT,
    "birth" TEXT,
    PRIMARY KEY ("id")
    );

O comando cria uma tabela chamada “authors”, se a mesma ainda não existe, e adiciona as colunas “id”, “name”, “country” e “birth”. Cada texto ao lado do nome das colunas, indica o tipo de dados da mesma: INTEGER e TEXT (inteiro e texto). Por fim a última linha indica que a chave primária da tabela é a coluna “id”.

Chamamos essa anotomia de como uma tabela foi criada de schema (o mesmo que já vimos em aulas passadas).

OBS: Podemos verificar o schema de tabelas individuais também, basta usar o comando .schema com o nome da tabela na frente.

SQLiteStudio

No SQLiteStudio, acessamos o schema de tabelas individuais. Para isso basta selecionar a tabela (na árvore de tabelas no canto esquerdo da tela) e abrir a tela DDL (data description language).

2 Projetando um banco

EXPLICAÇÃO DOS REQUISITOS DO BANCO

EXERCÍCIO: Crie o diagrama ER que modela os requisitos do banco ControleFinanceiro. Coloque o nome das colunas na representação (se preferir, procure alguam ferramenta online para criar os diagramas, ou faça usando qualquer software de desenho).

RESPOSTA

Diagrama ER ControleFinanceiro

3 Criando um banco de dados do zero

Para criar um banco de dados a partir do terminal do SQLite, basta abrir o terminal (na pasta onde se quer criar o banco), e digitar SQLiteo nome do mesmo (não se esqueça da extensão .db). Por exemplo, para criar um banco chamado ControleFinanceiro, basta digitar:

sqlite3 ControleFinanceiro.db

no terminal.

OBS: Se o comando acima for executado e o terminal for fechado sem que nenhum outro comando seja inserido, o banco não será criado! Portanto, após a criação do banco, digite

.schema

só para o arquivo ser de fato gerado. Note que ao usar o comando .schema nada aparece, pois ainda não existem tabelas no banco de dados.

4 CREATE TABLE - DROP TABLE

O comando para criar tabelas no SQL é o CREATE TABLE, seguido pelo nome da tabela, parênteses, e os nomes das colunas separados por vírgula. Vamos criar a tabela usuarios:

CREATE TABLE "USUARIOS" (
  "ID",
  "NOME",
  "SENHA",
  "DATA_CRIACAO"
);

Após a criação nada acontece. Porém, se digitarmos novamente o comando .schema podemos ver o comando usado para gerar a tabela. E também podemos ver a tabela no SQLiteStudio.

Para remover uma tabela pré-existente usamos o comando DROP TABLE seguido pelo nome da tabela a ser excluída. Por exemplo, para excluir a tabela recém criada de usuários:

DROP TABLE "usuarios";

EXERCÍCIO: Crie todas as tabelas do banco de dados ControleFinanceiro.

RESPOSTA

CREATE TABLE "USUARIOS" (
  "ID",
  "NOME",
  "SENHA",
  "DATA_CRIACAO"
);

CREATE TABLE "SALDO_CC" (
  "ID",
  "ID_USUARIO",
  "VALOR",
  "DATA_ATUALIZACAO"
);

CREATE TABLE "SALDO_CP" (
  "ID",
  "ID_USUARIO",
  "VALOR",
  "DATA_ATUALIZACAO"
);

CREATE TABLE "TRANSACOES_CC" (
  "ID",
  "ID_USUARIO",
  "ID_CATEGORIA",
  "TIPO",
  "VALOR",
  "DATA"
);

CREATE TABLE "TRANSACOES_CP" (
  "ID",
  "ID_USUARIO",
  "TIPO",
  "VALOR",
  "DATA"
);

CREATE TABLE "CATEGORIAS" (
  "ID",
  "NOME",
  "DESCRICAO"
);

5 Definindo tipos de dados

Note que ao criarmos a tabelas acima, não especificamos quais são os tipos de dados de cada coluna das tabelas. SQLite possui 5 tipos de classes de armazenamento de dados, são elas:

Classes de armazenamento
Tipo de classe	Descrição
NULL	Valores nulos
INTEGER	Valores inteiros
REAL	Valores reais
NUMERIC	Inteiros ou reais
TEXT	Textos e strings
BLOB	Binary Large Object - armazena os dados da forma como estão, por exmplo: audios, imagens.

Um classe de armazenamento contém vários tipos de dados, por exemplo a classe INTEGER, possui os seguintes tipos de dados:

0-byte integer
1-byte integer
2-byte integer
3-byte integer
4-byte integer
6-byte integer
8-byte integer

De forma que quando definimos que uma coluna deve ser da classe INTEGER, o SQLite automaticamente vai determinar o tipo de dado (dependendo do tamanho do número passado). OBS: Essa é uma simplificação do conceito de *Type Affinity* (https://www.sqlite.org/datatype3.html).

Por exemplo, considerando a tabela que criamos USUARIOS, os tipos de dados de cada uma poderiam ser como mostra abaixo:

ID - INTEGER
NOME - TEXT
SENHA - TEXT
DATA - TEXT

Já a tabela SALDO_CC:

ID - INTEGER
ID_USUARIO - INTEGER
VALOR - REAL
DATA - TEXT

Para adicionarmos essas informações na criação da tabela, basta inserir as classes de armazenamento logo após o nome das colunas. Veja o exemplo da tabela USUARIOS:

CREATE TABLE "USUARIOS" (
  "ID" INTEGER,
  "NOME" TEXT,
  "SENHA" TEXT,
  "DATA_CRIACAO" INTEGER
);

EXERCÍCIO: Atualize o diagrama ER com todos os tipos de dados das colunas.

RESPOSTA

ER - Tipos de dados

EXERCÍCIO: Atualize as tabelas (schema) do banco de dados com as novas informações de tipos de dados (exclua todas as tabelas já criadas usando o comando DROP TABLE )

RESPOSTA

CREATE TABLE "USUARIOS" (
  "ID" INTEGER,
  "NOME" TEXT,
  "SENHA" TEXT,
  "DATA_CRIACAO" TEXT
);

CREATE TABLE "SALDO_CC" (
    "ID" INTEGER,
    "ID_USUARIO" INTEGER,
    "VALOR" REAL,
    "DATA_ATUALIZACAO" TEXT
);

CREATE TABLE "SALDO_CP" (
    "ID" INTEGER,
    "ID_USUARIO" INTEGER,
    "VALOR" REAL,
    "DATA_ATUALIZACAO" TEXT
);

CREATE TABLE "TRANSACOES_CC" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "ID_CATEGORIA" INTEGER,
  "TIPO" INTEGER,
  "VALOR" REAL,
  "DATA" TEXT
);

CREATE TABLE "TRANSACOES_CP" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "TIPO" INTEGER,
  "VALOR" REAL,
  "DATA" TEXT
);

CREATE TABLE "CATEGORIAS" (
  "ID" INTEGER,
  "NOME" TEXT,
  "DESCRICAO" TEXT
);

6 Importando um schema

Para não precisar digitar os comandos de criar as tabelas direto no console, é possível criar um arquivo .sql com todos os comandos prontos e simplesmente importá-lo diretamente no SQLite. Para isso:

Salve os comandos em um arquivo com a extensão .sql na mesma pasta do banco de dados.
Acesse o banco de dados pelo terminal e exclua todas as tabelas criadas (para testar).
Verifique se todas foram excluídas com o comando .schema (não é para aparecer nada).
Importe o arquivo usando o comando .read schema.sql. Digite .schema novamente para ver que todas as tabelas foram criadas.

Agora podemos fazer todas as alterações em um único arquivo.

7 Restrições de tabelas

Agora já temos todos os tipos de dados das colunas. No entanto, existem algumas restrições entre esses dados que precisamos adicionar. Retomando o conceito de chaves primárias e estrangeiras visto na aula de relações, temos que toda tabela possui um campo que deve ser uma chave primária:

A chave primária de uma tabela é um atributo (coluna) que define únicamente os elementos de cada linha da mesma.

Como podemos definir qual é a chave primária de uma tabela? Ainda, vimos também o conveito de chave estrangeira: quando a chave primária de uma tabela é usada como um campo em outra tabela. Por exemplo, considerando o modelo de ER do banco de dados, na relação entre as tabelas TRANSACOES_CP e USUÁRIOS, temos que ID_USUARIO é uma chave estrangeira da tabela USUARIOS, ou seja, aqui temos mais uma restrição: não pode existir um ID_USUÁRIO na tabela TRANSACOES_CP que não esteja cadastrado também na tabela USUARIOS.

7.1 Chave primária

Podemos definir uma chave primária adicionando o comando PRIMARY KEY("nome_da_coluna") no fim da criação da tabela. Considerando a tabela USUARIOS, temos então:

CREATE TABLE "USUARIOS" (
  "ID" INTEGER,
  "NOME" TEXT,
  "SENHA" TEXT,
  "DATA_CRIACAO" TEXT,
  PRIMARY KEY("ID")
);

No entanto, se uma coluna não for especificada como chave primária, o próprio SQLite faz a criação de uma chamada `row ID``.

7.2 Chave estrangeira

Para adicionar a chave estrangeira, é necessário também informar o seu nome na tabela original, ou seja, referenciar o nome da coluna orignal. A sintaxe é FOREIGN KEY("nome_da_coluna_tabela_atual") REFERENCES "tabela_original"("nome_da_coluna_tabela_original"). Por exemplo, considerando a tabela TRANSACOES_CP, temos que a coluna ID_USUARIO é uma referência a coluna ID da tabela USUARIOS, portanto o comando fica da seguinte forma:

CREATE TABLE "TRANSACOES_CP" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "TIPO" INTEGER,
  "VALOR" REAL,
  "DATA" TEXT,
  FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID")
);

EXERCÍCIO: Atualize o diagrama ER com todas as restrições de chaves primárias e estrangeiras das tabelas (coloque ao lado das colunas o tipo de chave).

RESPOSTA

ER - Restrições chaves

EXERCÍCIO: Atualize as tabelas (schema) do banco de dados com as restrições de chaves primárias e estrangeiras (exclua todas as tabelas já criadas usando o comando DROP TABLE )

RESPOSTA

CREATE TABLE "USUARIOS" (
  "ID" INTEGER,
  "NOME" TEXT,
  "SENHA" TEXT,
  "DATA_CRIACAO" TEXT,
  PRIMARY KEY("ID")
);

CREATE TABLE "SALDO_CC" (
    "ID" INTEGER,
    "ID_USUARIO" INTEGER,
    "VALOR" REAL,
    "DATA_ATUALIZACAO" TEXT,
    PRIMARY KEY("ID"),
    FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID")
);

CREATE TABLE "SALDO_CP" (
    "ID" INTEGER,
    "ID_USUARIO" INTEGER,
    "VALOR" REAL,
    "DATA_ATUALIZACAO" TEXT,
    PRIMARY KEY("ID"),
    FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID")
);

CREATE TABLE "TRANSACOES_CC" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "ID_CATEGORIA" INTEGER,
  "TIPO" INTEGER,
  "VALOR" REAL,
  "DATA" TEXT,
  PRIMARY KEY("ID"),
  FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID"),
  FOREIGN KEY("ID_CATEGORIA") REFERENCES "CATEGORIAS"("ID")
);

CREATE TABLE "TRANSACOES_CP" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "TIPO" INTEGER,
  "VALOR" REAL,
  "DATA" TEXT,
  PRIMARY KEY("ID"),
  FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID"),
);

CREATE TABLE "CATEGORIAS" (
  "ID" INTEGER,
  "NOME" TEXT,
  "DESCRICAO" TEXT,
  PRIMARY KEY("ID")
);

8 Restrições de colunas

As restrições de colunas se aplicam a colunas específicas de uma tabela, por exemplo, podemos definir que uma determinada coluna não possa ter valores nulos. Existem quatro tipo de restrições de colunas em SQLite:

Restrição	Definição
CHECK	Permite verificar alguma condição (ex: verficar se um valor é maior do que 0)
DEFAULT	É um valor adicionado como padrão, quando nada é inserido no campo
NOT NULL	Não é possível deixar valores inseridos
UNIQUE	Garante que cada linha da coluna é única

Vamos verificar como usar o valor NOT NULL. Podemos definir que a coluna NOME da tabela USUARIOS não pode ficar sem preenchimento. Usamos a sintaxe NOT NULL depois da definição da classe de dados:

CREATE TABLE "USUARIOS" (
  "ID" INTEGER,
  "NOME" TEXT NOT NULL,
  "SENHA" TEXT,
  "DATA_CRIACAO" TEXT,
  PRIMARY KEY("ID")
);

Será que é necessário, então, adicionar a condição NOT NULL para todas as chaves primárias, dado que por definição elas devem existir e não podem se repetir?

Isso poderia ser feito para fins de completude, mas não é necessário. Ao inserirmos a restrição de tabela PRIMARY KEY uma série de restrições de coluna já são criadas, como NOT NULL e UNIQUE. O mesmo se aplica a chaves estrangeiras: como uma chave estrangeria é uma chave primária de outra tabela, a mesma deve existir (na tabela original).

Podemos definir que o campo NOME em categorias deve existir, e não pode se repetir:

CREATE TABLE "CATEGORIAS" (
  "ID" INTEGER,
  "NOME" TEXT NOT NULL UNIQUE,
  "DESCRICAO" TEXT,
  PRIMARY KEY("ID")
);

EXERCÍCIO: Altere o schema do banco de dados com as restrições de coluna que achar necessárias (verifique como usar DEFAULT e CHECK). Sugestões: 1. Datas como default de da data atual. 2. Cerificar se valores monetários são >= 0. 3. Garantir que o TIPO de transações seja somente 0 ou 1 (saída ou entrada).

CREATE TABLE "USUARIOS" (
  "ID" INTEGER,
  "NOME" TEXT NOT NULL,
  "SENHA" TEXT NOT NULL,
  "DATA_CRIACAO" TEXT DEFAULT(CURRENT_TIMESTAMP),
  PRIMARY KEY("ID")
);

CREATE TABLE "SALDO_CC" (
    "ID" INTEGER,
    "ID_USUARIO" INTEGER,
    "VALOR" REAL CHECK("VALOR" >= 0),
    "DATA_ATUALIZACAO" TEXT DEFAULT(CURRENT_TIMESTAMP),
    PRIMARY KEY("ID"),
    FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID")
);

CREATE TABLE "SALDO_CP" (
    "ID" INTEGER,
    "ID_USUARIO" INTEGER,
    "VALOR" REAL CHECK("VALOR" >= 0),
    "DATA_ATUALIZACAO" TEXT DEFAULT(CURRENT_TIMESTAMP),
    PRIMARY KEY("ID"),
    FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID")
);

CREATE TABLE "TRANSACOES_CC" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "ID_CATEGORIA" INTEGER,
  "TIPO" INTEGER NOT NULL CHECK("TIPO" IN (0,1)),
  "VALOR" REAL CHECK("VALOR" >= 0),
  "DATA" TEXT DEFAULT(CURRENT_TIMESTAMP),
  PRIMARY KEY("ID"),
  FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID"),
  FOREIGN KEY("ID_CATEGORIA") REFERENCES "CATEGORIAS"("ID")
);

CREATE TABLE "TRANSACOES_CP" (
  "ID" INTEGER,
  "ID_USUARIO" INTEGER,
  "TIPO" INTEGER NOT NULL CHECK("TIPO" IN (0,1)),
  "VALOR" REAL CHECK("VALOR" >= 0),
  "DATA" TEXT DEFAULT(CURRENT_TIMESTAMP),
  PRIMARY KEY("ID"),
  FOREIGN KEY("ID_USUARIO") REFERENCES "USUARIOS"("ID")
);

CREATE TABLE "CATEGORIAS" (
  "ID" INTEGER,
  "NOME" TEXT NOT NULL UNIQUE,
  "DESCRICAO" TEXT DEFAULT "-",
  PRIMARY KEY("ID")
);

Projetando - designing

1 Como tabelas são criadas (schema)

2 Projetando um banco

3 Criando um banco de dados do zero

4 CREATE TABLE - DROP TABLE

5 Definindo tipos de dados

6 Importando um schema

7 Restrições de tabelas

7.1 Chave primária

7.2 Chave estrangeira

8 Restrições de colunas

1 Como tabelas são criadas (`schema`)