Artigo SQL Magazine 6 - Projeto de Banco Dados parte V: Modelo Físico

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (0)

Artigo da Revista SQL Magazine -Edição 6.

Atenção: por essa edição ser muito antiga não há arquivo PDF para download.
Os artigos dessa edição estão disponíveis somente através do formato HTML.

Clique aqui para ler todos os artigos desta edição

Projeto de Banco de Dados Parte V: Modelo Físico

Por  Vinicius Lourenço de Sousa

 

Continuando a construção do projeto de banco de dados da livraria Book.NET, veremos mais alguns recursos do modelo físico: normalização, chaves primárias e desnormalização.

 

Normalização

Normalizar significa organizar as tabelas de forma que informações redundantes não sejam armazenadas. A redundância gera uma série de problemas para o banco de dados – veja o artigo “Normalização e Técnicas”, publicado nesta edição, para maiores detalhes. 

A normalização é composta por  várias fases, onde cada uma é chamada de forma normal (FN). As tabelas, para serem consideradas bem projetadas, devem respeitar a definição das FNs. Neste artigo serão estudadas e aplicadas as três primeiras formas normais, já que as demais FNs são muito específicas e não são utilizadas na maioria das aplicações comerciais.

A seguir, daremos início a normalização aplicando a primeira forma normal nas tabelas do nosso projeto.

 

Nota: Muitos conceitos utilizados aqui são explicados no artigo “Normalização e Técnicas”, publicado nesta edição.

 

Primeira Forma Normal (1FN)

A regra da 1FN é simples: cada campo de uma tabela deve ser atômico, ou seja, indivisível. A 1FN geralmente é violada de três formas em um projeto de banco de dados:

 

1) Campos compostos: por exemplo, o campo Endereco da tabela Cliente. Nesse caso, obter a 1FN significa separar esse campo em partes indivisíveis. Veja o resultado da divisão na figura 1.

Definir se um campo é composto ou atômico não é uma tarefa simples. Normalmente, essa decisão é tomada com base na regra de negócio da aplicação. Por exemplo: o campo Nome deve ser dividido em partes menores (primeiro nome, sobrenome etc.)? A resposta é “depende” – em algumas aplicações (como venda de passagens) a separação do nome é importante; em outras, não.

 

2) Campos repetidos: imagine uma tabela de notas com campos Nota1, Nota2, Nota3 etc. Uma estrutura como essa tem problemas pois não é suficientemente flexível, desperdiça espaço e é ineficiente para realizar alguns tipos de busca. Se você armazenar uma única nota, estará desperdiçando espaço com colunas vazias. Se precisar armazenar quatro notas, terá que criar colunas extras.

Uma solução eficiente é criar uma tabela auxiliar e relacioná-la com a tabela principal através de uma chave estrangeira. Observe a figura 2.

 

3) Ocorrência de tabelas aninhadas ou atributos multivalorados: ambos possuem múltiplos valores para um mesmo atributo. A entidade Cliente possui quatro atributos multivalorados: telefone, email, homepage e endereço (observe no modelo que a cardinalidade destes atributos é 1:N).

A solução é criar uma tabela auxiliar, semelhante ao que é feito com atributos repetidos. Como exemplo, veja a tabela auxiliar Endereço na figura 3.

O mesmo procedimento foi feito para os campos email, homepage e telefone. O modelo atualizado após a aplicação da 1FN pode ser visualizado na figura 4.

Nota: Observe que as chaves primárias de Endereço, Telefone, Email e Homepage possuem o campo Cod_cliente, indicando que as tabelas são fracas em relação a Cliente.

 

Segunda Forma Normal (2FN)

Uma tabela está em 2FN quando está em 1FN e cada campo que não faz parte da chave primária depende de todos os campos da chave primária. Uma tabela que não se encontra na segunda forma normal contém dependências funcionais parciais.

Veja a tabela Item_Nota_Fiscal abaixo:

 

Numero_Nota_Fiscal

Codigo

Isbn_Livro

1

1

154

1

2

546

2

1

512

 

Essa tabela atende a 2FN, pois para obtermos o valor do campo ISBN_livro precisamos saber o valor dos campos Numero_nota_fiscal e Codigo (que são a chave primária).

A tabela Compra não está na segunda forma normal. Veja na figura 5 um exemplo de instância de Compra (como vimos na edição anterior, numa compra de vários livros essa tabela armazena a informação de cada item comprado em um registro diferente).

O campo que desobedece a 2FN é Numero_nota_fiscal – ele não necessita de toda a chave primária para ser identificado. Observe que apenas com o valor dos campos Cod_cliente e Dt_hora já conseguimos obter a nota fiscal correspondente:

 

Para normalizar essa tabela, devemos: 

 

1)     Mover os campos com dependência funcional parcial – nesse caso, Numero_nota_fiscal – e colocá-los em uma nova tabela;

2)     A chave primária da tabela criada será formada pelo(s) campo(s) que era(m) determinante(s), na tabela original, dos campos movidos;

3)     Repetir os passos até eliminar a dependência parcial.

 

Veja na figura 6 que a tabela Compra foi decomposta em duas tabelas e que a dependência funcional parcial deixou de existir. Para melhorar a legibilidade, vamos chamar a tabela nova de Compra e a tabela original de Itens_Compra (que era o que ela realmente representava).

Dessa forma, a nova tabela (Compra) terá um relacionamento 1:1 com Nota_Fiscal e um relacionamento 1:N com a tabela original (Itens_Compra). 

A tabela Aluguel também viola a 2FN. Os campos dessa tabela são determinados da seguinte forma:

 

Cod_cliente, ISBN_livro e Dt_hora: Compõem a chave primária;

Período Aluguel, Dt_devolução e Vr_Multa: Violam a 2FN pois são determinados somente por Cod_cliente e Dt_hora;

Promoção: determinado por Cod_cliente, ISBN_livro e Dt_hora. Não viola a 2FN.

 

Através da especificação levantada com o cliente na parte I desta série, o aluguel foi modelado de forma simples: mesmo que o cliente leve mais de um livro, ele é obrigado a definir um período de locação igual para todos os livros e terá que devolvê-los na mesma data (o sistema não poderá dar baixa no aluguel se algum livro estiver faltando). Se o cliente necessitar de um período de aluguel diferente para cada livro, o atendente terá que cadastrar aluguéis diferentes. Se o cliente entregar os livros depois do período definido, um valor de multa fixo será aplicado para cada livro.

De acordo com essa modelagem, os campos Periodo_aluguel, Dt_devolucao e Vr_multa não precisam do campo ISBN_Livro para que sejam identificados. Veja um exemplo de instância da tabela Aluguel na figura 7.

Devemos seguir o mesmo roteiro visto na normalização da tabela Compra para remover a dependência funcional parcial de Aluguel. Veja o resultado na figura 8.

A princípio, a tabela Reserva não viola a 2FN pois todos os seus campos pertencem a chave primária. No entanto, temos uma errata na modelagem dessa entidade: não foi criado um atributo para indicar se a reserva está ou não em aberto. Por simplificação, a aplicação só permite uma reserva em aberto por vez para cada livro (não modelamos o conceito de “fila” de reservas).

Esse novo campo será chamado de Fg_Ativo e terá o valor ‘fechado’ quando: i) o cliente desistir da reserva; ii) o cliente alugar a fita.

Após a criação do campo a tabela Reserva passa a violar a 2FN, pois Fg_ativo é determinado apenas por Cod_cliente e Dt_hora. O processo de normalização é o mesmo realizado nas tabelas Compra e Aluguel. O modelo completo na 2FN pode ser visualizado na figura 9.

 

Terceira Forma Normal (3FN)

A 3FN procura eliminar campos de uma tabela que não dependam somente da chave primária. Em outras palavras, uma tabela estará em 3FN se já estiver em 2FN e se nenhum campo não chave depender de outro campo não chave. Outra forma de interpretar essa forma normal é dizer que não pode haver dependências funcionais transitivas.

         No modelo da livraria nenhuma tabela viola a 3FN. Por isso, veremos a aplicação dessa regra em um exemplo fictício: um sistema de controle acadêmico que gerencia a sala de trabalho dos coordenadores. Nele temos que: i) um coordenador pode ser responsável por várias disciplinas e uma disciplina é de responsabilidade de um único coordenador; ii) um coordenador trabalha em apenas uma sala. Veja a tabela Disciplina da figura 10.

O campo sala possui dependência funcional transitiva, pois pode ser obtido através de Codigo_Disciplina (que é chave primária) ou de Coordenador. Para normalizar essa tabela devemos seguir o roteiro:

 

1)     Eliminar a transitividade. Os campos que causam a dependência transitiva devem ser movidos para uma nova tabela;

2)     Na tabela criada, definir uma chave primária com o(s) campo(s) que era(m) determinante(s) direto(s), na tabela original, dos campos movidos.

 

Veja o resultado na figura 11. Note que o campo determinante (Coordenador) permanece na tabela original como chave estrangeira. A tabela Disciplina agora está na terceira forma normal.

 

Desnormalização

Desnormalizar significa desfazer a normalização de uma ou mais tabelas. Normalmente o projetista utiliza esse recurso para ganhar performance nas consultas, pois a desnormalização diminui a quantidade de joins necessários para obter uma informação.

O custo desse recurso pode ser alto, pois a estrutura de armazenamento das tabelas volta a ficar desordenada. Na média a desnormalização cria mais desvantagens do que vantagens e deve ser feita de forma criteriosa mesmo por projetistas experientes.

Como exemplo, vamos desnormalizar as tabelas Telefone, Homepage e Email. Já que elas têm praticamente o mesmo objetivo (armazenar um meio de comunicação) iremos juntá-las em uma única tabela, com o intuito de aumentar a performance na busca pelas informações do cliente. A nova tabela será chamada de Meio_Comunicacao.

Observe que geralmente um cliente terá uma quantidade pequena de registros nessa tabela, diminuindo assim os efeitos colaterais da desnormalização. Veja na figura 12 o modelo atualizado.

 

Melhorando o modelo físico

O próximo passo é fazer o acabamento do modelo físico, a fim de otimizá-lo e de retirar as últimas redundâncias (nem todas as repetições podem ser eliminadas com as formas normais). Nesse momento não existem muitas regras a seguir e o que vale é a experiência do projetista. Veja a seguir as melhorias que podem ser feitas no modelo da livraria:

 

Tabelas Repetidas

Atualmente existem algumas tabelas que fazem praticamente a mesma coisa: Item_Compra - Item_Nota_Fiscal e Compra – Nota_Fiscal. De acordo com a regra de negócio da livraria podemos unir essas tabelas sem maiores transtornos. Assim, teremos apenas um relacionamento: Compra – Item_Compra (o número da nota fiscal será armazenado na tabela Compra).

Observe que não estamos desnormalizando, pois as tabelas Nota_Fiscal e Item_Nota_Fiscal não foram geradas a partir de uma normalização.

 

Histórico

Atualmente, a informação do preço de venda/aluguel é armazenada apenas na tabela Livro. Dessa forma, o sistema não mantém um histórico e cria uma inconsistência virtual: se o preço de um livro for alterado, todas as compras/notas fiscais que o referenciam serão modificadas.

Como solução criaremos um campo nas tabelas Item_Compra e Item_Aluguel para duplicar a informação do preço e manter o histórico. Quando o usuário iniciar uma venda/aluguel, esse campo deverá ser inicializado com o valor do preço de venda/aluguel da tabela Livro. Essa regra deve ser registrada no dicionário de dados.

Dessa forma o usuário também poderá fornecer descontos durante uma venda, apesar de isso não ter sido solicitado na especificação de requisitos.

 

NOTA: A tabela Livro possui dois campos referentes ao aluguel: Preco_aluguel e Preco_renovacao_aluguel. No entanto, a tabela Item_Aluguel receberá apenas um campo, chamado Preco_Aluguel. Quando o usuário cadastrar uma renovação o sistema criará um novo aluguel, inicializando o preço de cada livro com o valor de Preco_Renovacao_Aluguel. Esse comportamento deve ser cadastrado no dicionário de dados. O sistema não emite relatórios sobre aluguéis novos x renovações, por isso, nenhum controle adicional foi inserido.

 

Geração de tabelas auxiliares

O campo Autor, da tabela Livro, tem grande potencial de repetição, já que ao longo do tempo vários livros do mesmo autor podem ser cadastrados. Como medida pró-ativa, criaremos uma tabela auxiliar para armazenar os autores - essa tabela terá um relacionamento 1:N com Livro (um autor pode escrever vários livros).

O modelo com as alterações propostas pode ser visualizado na figura 13.

 

Nota: Por motivos de simplificação, desde a parte I não foi considerada a possibilidade de um livro ser escrito por mais de um autor.

 

Chaves Primárias

Qualquer conjunto de campos que possa identificar um registro é referido como chave candidata. De todas as chaves candidatas possíveis, devemos escolher a melhor e definí-la como chave primária. Essa escolha deve ser baseada em dois fatores:

 

Imutabilidade: A chave primária ideal é aquela que nunca tem seu valor alterado. De acordo com as regras de integridade, se modificarmos o valor de uma chave primária teremos que atualizar todos as chaves estrangeiras que a referenciam, criando mais um processo no sistema.

 

Minimização: Quanto menor o número de campos na chave primária, mais rápida serão as operações sobre a tabela.

 

Atualmente, a maioria dos projetistas utiliza uma chave artificial auto-incremento, conhecida como chave substituta (surrogate key), para compor a chave primária. Esse tipo de chave, por não fazer parte da modelagem natural do banco de dados, oferece máxima imutabilidade e minimização – ela nunca precisará ser modificada e sempre será composta por apenas um campo.

Todas as chaves primárias foram minimizadas através desse recurso. O modelo físico completo pode ser visualizado nas figuras 14 e 15.

 

Conclusão

 

Chegamos ao final do modelo físico. É claro que o artigo, por mais prático que tenha tentado ser, não reflete com exatidão todos os passos que podem ocorrer na modelagem real de um banco de dados. Mesmo para aplicações simples, um banco costuma ser mais complexo e ter uma quantidade bem maior de entidades.

Esse foi o pontapé inicial – ao praticar tudo o que vimos, você descobrirá que a beleza teórica demonstrada aqui nem sempre poderá ser seguida; afinal, vivemos num mundo imperfeito.

Na próxima edição daremos início à implementação do banco de dados. Espero você lá!

 

 

BOX: Mais um exemplo de 2FN

Para esclarecer ainda mais a 2FN, temos a tabela Curso abaixo:

 

Tabela Curso

Matricula

Cód_Disciplina

Nome

Endereco

Descricao

Coordenador

Sala

100936

IG005

Eduardo

Rua das Flores, 23

Inglês

Luis

100

101256

GF005

Felipe

Rua XV de Novembro, 12

Geografia

Antonio

202

101256

IG005

Felipe

Rua XV de Novembro, 12

Inglês

Luis

100

 

Note que apenas com o campo Matricula podemos obter as informações do aluno (nome e endereço), caracterizando a dependência funcional parcial. Observe também que apenas com o campo Cod_Disciplina podemos obter a descrição da disciplina, o nome e a sala do coordenador responsável. Neste caso, devemos fazer o seguinte:

 

1)     Eliminar os campos Nome e Endereco da tabela de curso;

 

Tabela Curso

Matricula

Cód_Disciplina

Descrição

Coordenador

Sala

100936

MT001

Matemática

João

100

101256

GF005

Geografia

Antonio

202

101256

IG005

Inglês

Luis

100

 

2)     Criar a tabela de aluno, com os campos Matricula, Nome e Endereco;

 

Tabela Aluno

Matricula

Nome

Endereço

100936

Eduardo

Rua das Flores, 23

101256

Felipe

Rua XV de Novembro, 12

 

3)     Eliminar os campos Descricao, Coordenador e Sala da tabela de Curso

 

Tabela Curso

Matricula

Cod_Disciplina

100936

MT001

101256

GF005

101256

IG005

 

4)     Criar a tabela Disciplina

 

Tabela Disciplina

Cod_Disciplina

Descricao

Coordenador

Sala

MT001

Matemática

João

100

GF005

Geografia

Antonio

202

IG005

Inglês

Luis

100

 

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?