Análise do Banco de Dados

Quando vamos desenvolver aplicações, após a Análise dos Requisitos de um sistema, precisamos definir a estrutura lógica e física do banco de dados. Neste estudo vamos abordar os passos (em síntese) desse procedimento.

Requisitos do Sistema (PASSO 1)
Nosso sistema precisará atender os seguintes requisitos (avaliando apenas o contexto dos dados, e não das regras de negócio - Core Business). Em síntese, o sistema deverá cadastrar as empresas Clientes que compram os produtos manufaturados pela empresa proprietária do sistema.

1. O Banco de Dados precisa estar implementado no Oracle.
   
2. O sistema precisará armazenar Bancos através do ID e Descrição (Ex. 01, Itaú).
3. O sistema deve guardar dados dos Clientes através do ID, Descrição, Tipo e Banco.
4. O Tipo deve estar associado às possibilidades: "Microempresa", "Empresa de Pequeno Porte" e "Empresa de Médio Porte".
5. O Cliente não necessariamente precisa ter um Banco, porém sempre deverá estar associado a um Tipo.
   

Vamos iniciar a análise.

Diagrama de Entidade e Relacionamento (PASSO 2)
Bem, o processo de análise é interessante! Precisamos entender o contexto do sistema e documentar os tópicos de forma estruturada e compatível com as ferramentas computacionais necessárias para produzí-lo (que viagem heim?). Ou seja, os requisitos levantados nos trazem uma perspectiva para responder a pergunta "O que vamos construir?". A análise fará com que encontremos a resposta para a pergunta "como vamos construir?". Como chegaremos aos comandos SQL (se tratando de uma análise do banco de dados) mais apropriados para a construção coerente do sistema? Através da Análise! Entendeu?

Seja como for, vamos ao primeiro diagrama: Entidade e Relacionamento. Esse diagrama foi introduzido por Peter Chen em 1976. Ele é utilizado para documentar as Entidades (futuras Tabelas) do banco e seus Relacionamentos. Certas literaturas apresentam um DER (Diagrama de Entidade e Relacionamento) com os respectivos atributos. Particularmente prefiro focar quais as entidades e seus relacionamentos. Os atributos serão melhor estruturados no próximo passo da análise.

Vale ressaltar que a imagem abaixo foi construída através da fantástica ferramenta on-line http://www.drawanywhere.com/.
Abaixo temos o DER para os Requisitos levantados:

Entendendo o diagrama: os Retângulos indicam as Entidades (futuras Tabelas). Os Losangos indicam as associações. Observe que, obrigatoriamente, o nome das Entidades devem estar no singular. Podemos entender que UM Banco contém (ou pertence a) no MÍNIMO zero e no MÁXIMO n Clientes (0:N do lado da Entidade Cliente) e que UM Cliente contém no NÍNIMO zero e no MÁXIMO um Banco (0:1 do lado do Banco). Ainda podemos entender que UM Cliente é um tipo de no MÍNIMO um e no MÁXIMO um Tipo_Cliente (1:1 ao lado da Entidade Tipo_Cliente) e que UM Tipo_Cliente está relacionado com no MÍNIMO zero e no MÁXIMO n Cliente(s) (0:N ao lado do Cliente). Perceba que isso representa exatamente os requisitos levantados: Cliente precisa SEMPRE ter um Tipo_Cliente (1:1), e pode (ou não) estar associado a um Banco (0:1).

Já temos nossas entidades definidas (e seus relacionamentos). Precisamos focar nos atributos e a composição dos relacionamentos (com as Foreign Keys).

Diagrama de Estrutura de Dados (PASSO 3)
Esse é um importante diagrama que deve ser elaborado após o DER. Sua ideia principal é definir os atributos com os respectivos domínios e a composição dos relacionamentos e chaves primárias. Deve-se atentar que, podemos definir o domínio dos atributos com base no Banco de Dados, ou seja, dizer que "Descricao" é do tipo VARCHAR2(60) implica no tipo de dado VARCHAR2 do banco Oracle. Contudo, isso não é aconselhado, pois nossos diagramas precisam ser compatíveis com qualquer banco de dados, mesmo que os requisitos do sistema indiquem a tecnologia (item 1 dos requisitos). Podemos deixar esse grau de especificação para a próxima etapa (dentro de um processo de gerenciamento de projetos de software, o projeto técnico, por exemplo).
Vamos ao diagrama:

Esse diagrama, conforme podemos observar, apresenta os tipos de dados genericamente. O C indica domínios como CHAR ou VARCHAR. O N indica tipo numéricos como NUMBER, NUMERIC, INTEGER e muitos outros. Podemos também especificar datas com D e outros campos que forem necessários (como CLOB ou BLOB). Veja que a seta com origem no campo "id" da entidade "Banco" aponta para o campo "banco" (com a indicação "(FK)") da entidade "Cliente". Isso significa que o campo "banco" precisa ser composto por um dos elementos presentes em "Banco", porém, lembre-se que um "Cliente" poderá ou não estar associado ao "Banco". Nossa regra CONSTRAINT para definição da chave estrangeira será distinta. Já a seta com origem do "id" de "Tipo_Cliente" aponta para um campo com a indicação "(FK)". Isso (igual ao "Banco") nos indica a necessidade da CONSTRAINT FOREIGN KEY. Nesta associação, contudo, em todo caso de inserção na tabela de "Cliente", obrigatoriamente precisaremos definir um valor para "tipo" coerente com os valores existentes na tabela "Tipo_Cliente". Ambas situações serão abordadas.

Vamos agora precisar os termos técnicos do banco através da próxima etapa de análise.

Dicionário de Dados (PASSO 4)
A convenção mais conhecida do Dicionário de Dados pode ser vista em http://pt.wikipedia.org/wiki/Dicionário_de_dados. Porém, prefiro utilizar uma notação mais simples em formato de tabela (veja um exemplo em http://www.luis.blog.br/dicionario-de-dados.aspx, ou um exemplo prático em http://www.inf.ufsc.br/~fileto/Agrissolos/res/Agrissolos_BD.html ). Nesta etapa definimos o projeto técnico da especificação analisada nos diagramas anteriores. Para cada Entidade precisaremos especificar uma Tabela (e nos casos de relacionamentos N:N também). Podemos nos concentrar na tecnologia, portanto, substituiremos os domínios genéricos (N) para os tipos físicos do banco (NUMBER).

Entidade: Banco Tabela: bancos Registro de Bancos.
ColumnName	DataType	Primary Key	References	Not Null	Flags	Default Value	Comment	AutoInc
id	NUMBER(4)	PK		NN	UNSIGNED		Identificador do Banco	AI
descricao	VARCHAR2(40)			NN			Nome do Banco
Index Name			Index Type			Columns
pk_bancos_id			Primary Key			id
unique_bancos_descricao			Unique Index			descricao

Entidade: Tipo_Cliente Tabela: tipo_cliente Registro dos tipos de Clientes existentes. Valores possíveis: Microempresa Empresa de Pequeno Porte Empresa de Médio Porte
ColumnName	DataType	Primary Key	References	Not Null	Flags	Default Value	Comment	AutoInc
id	NUMBER(4)	PK		NN	UNSIGNED		Identificador do Tipo de Cliente	AI
descricao	VARCHAR2(40)			NN			Descrição do Tipo
Index Name			Index Type			Columns
pk_tipo_cliente_id			Primary Key			id
unique_tipo_cliente_descricao			Unique Index			descricao

Entidade: Cliente Tabela: clientes Registro de Clientes. A Chave Estrangeira de associação entre Clientes e Tipo Clientes deve conter uma restrição padrão. A Chave Estrangeira de associação entre Clientes e Bancos deve conter a restrição ON DELETE SET NULL.
ColumnName	DataType	Primary Key	References	Not Null	Flags	Default Value	Comment	AutoInc
id	NUMBER(4)	PK		NN	UNSIGNED		Identificador do Cliente	AI
nome	VARCHAR2(80)			NN			Nome do Cliente
tipo	NUMBER(4)		tipo_cliente(id)	NN	UNSIGNED		Definição do Tipo de Cliente
banco	NUMBER(4)		bancos(id)		UNSIGNED		Definição do Banco do Cliente
Index Name			Index Type			Columns
pk_clientes_id			Primary Key			id
unique_clientes_nome			Unique Index			nome
fk_clientes_tipo_cliente			Foreign Key			tipo
fk_clientes_bancos			Foreign Key			banco

Pronto. Vamos entender o DD (Dicionário de Dados). Para cada Entidade temos duas tabelas: a primeira tabela apresenta os campos ColumnName (para o nome do atributo), DataType (domínio do atributo especificado de acordo com o banco de dados), Primary Key (que indica se o campo é chave primária), References (utilizado para os campos de Chave Estrangeira, indicando a tabela referenciada), Not Null, Flags (indica flags, como UNSIGNED - valor apenas positivo - por exemplo), Default Value, Comment (uma descrição do atributo) e AutoInc (para indicar se o campo é ou não auto incrementado). A segunda tabela indica os índices criados, e contém as colunas Index Name (para o nome do índice), Index Type (para os tipos de índice) e Columns (para representar as colunas associadas ao índice especificado). Como exemplo geral, observe a tabela "clientes", associada à Entidade "Cliente". Ela contém os campos "id", "nome", "tipo" e "banco". O campo "id" é uma chave primária, NOT NULL, sem sinal (UNSIGNED - valor positivo, apenas) e deve ser incrementada automaticamente. O campo "nome" é um VARCHAR2(80). Observe que os campos "tipo" e "banco" devem ser NUMBER(4) para coincidirem com as chaves primárias das Entidades "Tipo_Cliente" e "Banco". O campo "tipo" referencia o campo "id" da tabela "tipo_cliente" e o campo "banco" o "id" da tabela "bancos". Conforme definido na descrição desta tabela, as CONSTRAINTs serão definidas de acordo com a necessidade individual da associação. Uma FOREIGN KEY padrão (a de "tipo_cliente" com "clientes") indica que o SGBD (Sistema gerenciador de banco de dados - no caso, o Oracle) não permitirá a exclusão de um "Tipo_Cliente" se o mesmo estiver associado com um "Cliente", além de que, como o campo "tipo" é NOT NULL, ao inserir um cliente, precisamos obrigatoriamente definir um "Tipo_Cliente" existente. Já com a condição ON DELETE SET NULL, o banco definirá o valor NULL para o campo "banco" da tabela "clientes" caso seja excluído um banco no qual o cliente esteve associado. Por isso definimos "banco" como NULL (pode ser nulo).
Observe na segunda tabela da Entidade "Cliente" que deve ser definido uma regra UNIQUE para a coluna "nome".
Como resultado deste DD, temos complementadas todas as informações necessárias para a criação de nossas tabelas (e outro objetos necessários).

Criando os Objetos do Banco (PASSO 5)
Agora vamos implementar no Oracle 10g Express Edition todos os elementos necessários para nosso estudo ser concretizado. Sabe-se da necessidade das tabelas pelos diagramas. Porém, implicitamente, percebemos a necessidade da criação de SEQUENCEs e TRIGGERs para a concretização do auto-incremento. Primeiramente vamos criar as tabelas.

Para informações sobre o banco Oracle 10g Express Edition, vide os tutoriais "Instalando Oracle 10g no Ubuntu" e "Administrando Objetos no Oracle 10g Express Edition".

Como a tabela "clientes" necessita das outras, ela será a última a ser implementada. Vamos começar com a tabela "bancos":

create table bancos (
  id number(4) not null,
  descricao varchar2(40) not null,
  constraint pk_bancos_id primary key (id),
  constraint unique_bancos_descricao unique(descricao)
);

A flag UNSIGNED será definida pelo escopo de números retornados da SEQUENCE de auto-incremento do "id". Vamos criar a tabela "tipo_cliente":

create table tipo_cliente (
  id number(4) not null,
  descricao varchar2(40) not null,
  constraint pk_tipo_cliente_id primary key (id),
  constraint unique_tipo_cliente_descricao unique(descricao)
);

Por fim, vamos criar a tabela "clientes":

create table clientes (
  id number(4) not null,
  nome varchar2(80) not null,
  tipo number(4) not null,
  banco number(4),
  constraint pk_clientes_id primary key (id),
  constraint unique_clientes_nome unique(nome),
  constraint fk_clientes_tipo_clientes foreign key (tipo) references tipo_cliente(id),
  constraint fk_clientes_bancos foreign key (banco) references bancos(id) on delete set null
);
 
Observe que não foi criada a CONSTRAINT para a chave estrangeira do campo "banco", conforme nossa especificação. Vamos agora a criação das SEQUENCEs (clique aqui para maiores informações). Primeiro vamos criar a SEQUENCE da tabela "bancos":

create sequence seq_bancos_id start with 1 maxvalue 9999 increment by 1 nocycle;

Agora para a tabela "tipo_cliente":
 
create sequence seq_tipo_cliente_id start with 1 maxvalue 9999 increment by 1 nocycle;

Por fim, para a tabela "clientes":

create sequence seq_clientes_id start with 1 maxvalue 9999 increment by 1 nocycle;

Agora precisamos criar as TRIGGERs necessárias para que as SEQUENCEs sejam associadas automaticamente às tabelas. Em primeiro lugar, temos a TRIGGER para "bancos":

create trigger trigger_bancos_id
before insert on bancos
for each row
begin
  select seq_bancos_id.nextval into :new.id from dual;
end trigger_bancos_id;

Observe que a TRIGGER vai ser disparada antes dos eventos DML do tipo INSERT para a tabelas "bancos", sendo que, o valor do atributo "id" da nova linha que está sendo inserida (:new) receberá o próximo valor da SEQUENCE (seq_bancos_id.nextval). As outras TRIGGERs são similares a essa.
Agora a TRIGGER para "tipo_cliente":

create trigger trigger_tipo_cliente_id
before insert on tipo_cliente
for each row
begin
  select seq_tipo_cliente_id.nextval into :new.id from dual;
end trigger_tipo_cliente_id;

Por último, a TRIGGER de "clientes":

create trigger trigger_clientes_id
before insert on clientes
for each row
begin
  select seq_clientes_id.nextval into :new.id from dual;
end trigger_clientes_id;

Pronto, nosso banco foi devidamente criado e está coerente com as especificações.

Conclusões
Você pode estar pensando: porque preciso ter essa trabalheira toda se posso simplesmente criar minhas SQLs na cara e coragem? Bom, certamente, com sua experiência em banco de dados, você pode achar desnecessário passar por essas cinto etapas. Acredito que possa realmente passar direto para a "PARTE 5" e criar as tabelas necessárias para o sistema. A questão apropriada para responder essa pergunta é: E se seu sistema tiver 100 tabelas? 100 tabelas? Isso é possível? Claro que é! Inclusive, hoje, dia 29 de Dezembro de 2008, participo de um sistema (SIPWEB - funciona em Intranet) que possui muitas e muitas tabelas, mais que 100, inclusive. Claro que trabalhamos com geração automatica da base pelo JPA e EJB3 (Java). Mas se houvesse a necessidade de implementar um banco na mão? Se em vez de criarmos as tabelas a partir das classes (do Model) Java, precisássemos criar as classes Java (do Model) a partir das tabelas? Inclusive, também estou participando de um outro projeto (IKPONTO - talvez você possa acessá-lo em ikponto.linu.com.br - digo talvez porque ele fica no ar apenas para testes) no qual esse foi o procedimento. Não existem tantas tabelas quanto o primeiro projeto, porém, o banco já existe em MySQL. As classes do Model foram geradas automaticamente por uma ferramenta da IDE Eclipse. Nesses casos, você acha que poderíamos manter 100% de confiabilidade do banco implementando as tabelas diretamente na "unha"? Minha resposta com base em experiências próprias: Não. Para sistemas grandes, é essencial que se tenham documentadas as etapas de análise do banco, se possível, através do DER, DED e DD. Podemos ainda considerar a manutenção de sistemas criados por outras equipes/programadores. Nesses casos (que particularmente, também já tive a oportunidade de trabalhar), é extremamente difícil de manutenir a base pela falta de documentação.

Bom, é isso ai! A análise é muito importante, e tão divertida quanto a programação das SQLs (hehehe). Portanto, use e abuse dos diagramas!

Referências:
http://en.wikipedia.org/wiki/Entity-relationship_model
http://en.wikipedia.org/wiki/Peter_Chen
http://www.drawanywhere.com/
http://www.guilhermepontes.eti.br/sgbd/index.html
http://pt.wikipedia.org/wiki/Dicionário_de_dados
http://www.luis.blog.br/dicionario-de-dados.aspx
http://www.inf.ufsc.br/~fileto/Agrissolos/res/Agrissolos_BD.html
http://www.inf.ufsc.br/~fileto/Agrissolos/res/Agrissolos_BD_grande.png


Obrigado pela companhia.