Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


sábado, 14 de março de 2009

Um pouco de modelagem ...

Por Eduardo Legatti

Olá,

Saindo um pouco do assunto Oracle e indo mais para a análise e modelagem de dados, sabemos que por trás de um ambiente de banco de dados existem alguns conceitos e técnicas muito importantes e imprescindíveis para a correta implantação e operação de qualquer sistema. Além do modelo conceitual que é a fase onde iniciamos um projeto de banco de dados utilizando um conceito mais abstrato de forma a representar os dados de uma área de negócio, utilizamos também o modelo de dados lógico para descrever os principais elementos abordados no modelo de dados conceitual. Bem, podemos dizer que o projeto lógico constitui a fase imediatamente posterior à modelagem conceitual do banco de dados na qual procura detalhar a forma como as entidades, atributos e relacionamentos serão representadas no banco de dados. É nesta fase que escolhemos se o banco de dados terá uma abordagem relacional (baseado em lógica e na teoria de conjuntos), objeto-relacional ou orientada à objetos para então em um segundo momento, fazer a escolha de qual será o gerenciador de banco de dados (SGBD) a ser adotado para armazenar tal modelo de dados.

Existe também uma terceira fase, teoricamente posterior ao projeto lógico chamada de projeto físico, onde é realizado o detalhamento dos dispositivos e recursos físicos necessários a serem alocados para suportar o conteúdo proposto para o banco de dados, tais como índices (btree, bitmap, reverso), particionamentos (list, hash, range), tabelas (Heap, cluster, IOT), discos, etc...

No mais, o real motivo de eu estar dissertando sobre este assunto partiu de um projeto lógico de banco de dados iniciado pelos estagiários da área de desenvolvimento de sistemas ... e como uma das tarefas de qualquer DBA é a de prover suporte à equipe de desenvolvimento junto ao DA (Data Administrator) no que se refere à modelagem de dados quando necessário, lá fui eu dar alguns "pitacos" no quesito relacionamentos entre entidades e integridades referenciais.

Em um primeiro momento ao abrir o modelo lógico, percebi um emaranhado de relacionamentos excessivos (aquela famosa teia de aranha) para até então poucas tabelas, foi quando então percebi inúmeros relacionamentos desnecessários de integridades referenciais (Foreign Keys) no modelo. Pode parecer básico, mas acredito que muitos iniciantes cometam tais "erros" ...

Para exemplificar, irei demonstrar abaixo um exemplo de modelo lógico simples na qual teremos um relacionamento (integridade referencial) inútil e desnecessário entre as tabelas envolvidas, e que talvez não tenha impacto direto no modelo físico em relação à performance na manipulação dos dados, mas que sem sombra de dúvida irá "poluir" o modelo lógico passando a impressão de ser mais complexo do que é, além de ficar bem confuso e de difícil entendimento aos olhos dos analistas e desenvolvedores. Vamos partir do script abaixo:

SQL> create table TABELA_A (
  2   codigo number not null,
  3   constraint pk_tabela_a primary key (codigo)
  4  );

Tabela criada.

SQL> create table TABELA_B (
  2   codigo number not null,
  3   id number not null,
  4   constraint pk_tabela_b primary key (codigo,id)
  5  );

Tabela criada.

SQL> create table TABELA_C (
  2   codigo number not null,
  3   id number not null,
  4   item number not null,
  5   constraint pk_tabela_c primary key (codigo,id,item)
  6  );

Tabela criada.

SQL> alter table tabela_b add constraint FK_B_A foreign key (codigo)
  2  references tabela_a (codigo);

Tabela alterada.

SQL> alter table tabela_c add constraint FK_C_A foreign key (codigo)
  2  references tabela_a (codigo);

Tabela alterada.

SQL> alter table tabela_c add constraint FK_C_B foreign key (codigo,id)
  2  references tabela_b (codigo,id);

Tabela alterada.
De acordo com o script acima, podemos ver em um primeiro momento nada de errado entre as tabelas e seus relacionamentos, mas, se extrairmos o modelo lógico à partir do modelo físico, iremos ver a seguinte figura abaixo:


De acordo com as leis que regem a modelagem relacional de dados, uma chave estrangeira (Foreign Key) representa os relacionamentos entre tabelas através de atributos de relacionamento. Essa técnica consiste na distribuição de alguns atributos-chave pelas tabelas que representam as entidades envolvidas no relacionamento, de forma que seja possível a associação lógica entre as linhas das tabelas com base na comparação de suas chaves. Na prática uma chave estrangeira é uma coluna (ou combinação de colunas) que indica um valor que deve existir como chave primária em uma outra tabela (chamada de Tabela Pai). A principal finalidade da chave estrangeira é garantir a integridade dos dados referenciais, pois apenas serão permitidos valores que supostamente devem existir na base de dados. Bem, no caso do modelo acima, o relacionamento FK_C_A é realmente necessário?

Podemos perceber que a TABELA_A é pai da TABELA_B que é pai da TABELA_C, ou seja, existe uma chave estrangeira FK_B_A na TABELA_B que referencia a TABELA_A uma chave estrangeira FK_C_B na TABELA_C que referencia a TABELA_B.

Partindo do pressuposto de que não é possível inserir registros na TABELA_B que não existam na TABELA_A e nem inserir registros na TABELA_C que não existam na TABELA_B, estamos garantindo a integridade referencial das informações contidas nestas tabelas. Portanto, o relacionamento realizado entre as tabelas TABELA_C e TABELA_A, através da restrição FK_C_A é totalmente inútil e desnecessário, exatamente pelo fato de que a restrição já imposta pela chave estrangeira FK_C_B garantirá tal integridade.

Dependendo da ferramenta Case utilizada, a maioria permite realizar uma validação do modelo, e com certeza, este modelo seria reprovado com alguma mensagem do tipo: O atributo "CODIGO" na entidade "TABELA_C" é chave estrangeira para mais de uma chave primária.

Portanto, de acordo com o que foi demonstrado acima podemos perceber uma certa "poluição" no modelo lógico no que se refere ao relacionamento entre as tabelas TABELA_C e TABELA_A. Já conseguiu imaginar essa situação em um modelo contendo, 500, 1000 ou até mesmo mais de 2000 entidades?

Google+

7 comentários:

Ray Silva disse...

Meu caro, tudo bem?

Excelente blog!

Por ventura tem MSN para que eu possa falar com você?

Me add, ou me manda um e-mail por gentileza:

rayzitu@msn.com

abraços!

Eduardo Legatti disse...

Olá Ray,

Tudo bem? Qualquer coisa você poderá me contactar através das comunidades Oracle Community em http://www.oraclecommunity.net/ ou Oracle Brazilians DBA's em http://orclbrdba.ning.com/

Até mais ...

Henrique de Castro disse...

Gostei da idéia de fugir um pouco do Oracle, espero mais posts desse tipo :D

Sérgio Augusto disse...

Olá Eduardo, tudo bem?

Tenho gostado muito dos assuntos postados no seu blog.
Nesse caso do relacionamento desnecessário, caso alguma consulta necessite fazer join entre as tabelas A e C, eu preciso envolver a tabela B?

Eduardo Legatti disse...

Olá Sérgio,

Isso irá depender de qual resultado você realmente deseja alcançar, independente se a integridade referencial criada entre as tabelas C e A existe ou não.

Caso seja necessário obter alguma informação das colunas existentes dos registros na tabela B, então obrigatoriamente a tabela B precisará ser envolvida no join do seu SQL, caso contrário, você correrá o risco de gerar um resultado com registros duplicados, insuficientes ou até mesmo inconsistentes, pois os dados retornados da tabela C não teriam nenhuma associação com os registros (pais) de quem realmente as gerou. Neste caso, seria a tabela B.

Enfim, quando você estiver realizando "joins" e, ao executar o SQL, você perceber que os registros retornados estão duplicados, pode ter certeza de que algum "join" com alguma outra tabela deixou de ser realizada no seu SQL.

Abraços e até mais ...

Tio Helio disse...

Caro Eduardo

É possível fazer "ao contrário"?
Isto é, garantir que cada linha da tabela master tenha pelo menos uma linha a ela vinculada na tabela detalhe.
Por exemplo:
1. Não existe item de pedido que não esteja vinculado a um pedido. Assim, na tabela ITENS criamos uma FK apontando para a tabela PEDIDOS.
2. Mas também não podemos criar um pedido sem pelo menos um item...
Tem como garantir isto pelo banco ou só pelo aplicativo?

Eduardo Legatti disse...

Olá Hélio,

Ótima pergunta. Sei exatamente o que você está querendo dizer... e acho que poderá até ser um assunto na lista de "a serem abordados" em futuros artigos.

Bom, de acordo com a teoria, em um banco de dados relacional, quando um registro aponta para o outro, e você quer que ele seja dependente deste outro, é necessário que se faça regras para que o registro "pai" não possa ser excluído se ele tiver "filhos" (as suas dependências). São as chamadas integridades referenciais. A integridade referencial garante a não inconsistência dos dados, de modo a não haver como existir um registro "filho" sem um registro "pai". Mas, o contrário, só será possível se as tabelas envolvidas tiverem apenas uma coluna como chave primária (mais na frente você irá entender). A verdade é que soaria bem estranho um "pai" ser "filho" do próprio "filho", e o "filho" ser "pai" do próprio "pai", não? ;-)

No mais, acredito que o ideal seja colocar esta regra de negócio na camada da aplicação mesmo. Contudo, a título de demonstração, veja o exemplo abaixo onde eu irei criar restrições de integridade adiáveis (DEFERRABLE). Por padrão, as restrições de integridade (primary key, foreign key, entre outras...) são criadas como "NOT DEFERRABLE IMMEDIATE". Na view de dicionário dados USER_CONSTRAINTS você poderá checar esta informação.

Isto significa que a validação da restrição de integridade é checada imediatamente durante o DML, como demonstrado abaixo:

SQL> create table a (id number constraint pk_a primary key);

Tabela criada.

SQL> create table b (id number constraint pk_b primary key);

Tabela criada.

SQL> alter table b add constraint fk_b_a foreign key (id) references a;

Tabela alterada.

SQL> alter table a add constraint fk_a_b foreign key (id) references b;

Tabela alterada.

SQL> insert into a values (1);
insert into a values (1)
*
ERRO na linha 1:
ORA-02291: restrição de integridade (SYSTEM.FK_A_B) violada - chave mãe não localizada

SQL> insert into b values (1);
insert into b values (1)
*
ERRO na linha 1:
ORA-02291: restrição de integridade (SYSTEM.FK_B_A) violada - chave mãe não localizada


Agora, se criarmos as restrições como adiáveis, ou seja, a validação será checada durante o COMMIT, poderemos chegar a um caso de uso na qual você mencionou onde queremos um "pai" que tenha pelo menos "um filho", e vice-versa.

SQL> alter table b add constraint fk_b_a foreign key (id) references a deferrable initially deferred;

Tabela alterada.

SQL> alter table a add constraint fk_a_b foreign key (id) references b deferrable initially deferred;

Tabela alterada.

SQL> insert into b values (1);

1 linha criada.

SQL> insert into a values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> insert into a values (2);

1 linha criada.

SQL> insert into b values (3);

1 linha criada.

SQL> commit;
commit
*
ERRO na linha 1:
ORA-02091: transação repetida
ORA-02291: restrição de integridade (SYSTEM.FK_B_A) violada - chave mãe não localizada

SQL> delete from a;

1 linha deletada.

SQL> commit;
commit
*
ERRO na linha 1:
ORA-02091: transação repetida
ORA-02292: restrição de integridade (SYSTEM.FK_B_A) violada - registro filho localizado

SQL> delete from b;

1 linha deletada.

SQL> commit;
commit
*
ERRO na linha 1:
ORA-02091: transação repetida
ORA-02292: restrição de integridade (SYSTEM.FK_A_B) violada - registro filho localizado

SQL> delete from a;

1 linha deletada.

SQL> delete from b;

1 linha deletada.

SQL> commit;

Commit concluído.

Conclusão: O "filho" só poderá ser excluído, se o pai também for "excluído" e vice-versa.

Agora, tente simular esta mesma demonstração tendo chaves primárias compostas ...

Abraços e até mais ...

Postagens populares