Artigo SQL Magazine 32 - Desafio SQL Magazine

Desafio SQL Magazine

Consultas em SQL: Utilização de relacionamentos 1:1 e 1:n

Leitura obrigatória: SQL Magazine 31, Desafio SQL Magazine.

Caros leitores, na edição anterior apresentamos um primeiro desafio de modelagem de dados onde a ênfase era exercitar a construção de relacionamentos 1:1 e 1:n.

Neste segundo desafio, vamos propor a construção de um conjunto de consultas em SQL a serem desenvolvidas utilizando o modelo proposto no primeiro desafio. Antes disso, precisamos discutir as possibilidades de solução do desafio anterior (leia na íntegra o desafio no Box 1), lembrando que não existe uma única solução correta. Assim, se sua solução estiver um pouco diferente da que estaremos apresentando a seguir, não quer dizer necessariamente que esteja errada.

Box 1. Desafio apresentado na edição 31 da SQL Magazine

Deseja-se construir um sistema de controle de biblioteca. Como requisitos iniciais foram identificados:

·         Devem ser cadastradas as obras do acervo, que representam livros, periódicos (revistas, jornais) e qualquer outro elemento do acervo da biblioteca. Inicialmente, obras devem possuir um código que as identifique: título, autor principal, ano de publicação, situação (disponível, emprestada) e editora. Editoras, por sua vez, possuem um código, nome e cidade. Uma obra sempre é de uma editora e uma editora pode possuir diversas obras;

·         Devem ser cadastrados usuários da biblioteca, que devem ter uma identificação única, nome, endereço completo, telefone de contato e CPF;

·         Os funcionários da biblioteca também devem ser cadastrados. Funcionários têm um número de matrícula, seu nome completo e departamento em que trabalha. Departamentos, por sua vez, possuem código e nome. Todo funcionário obrigatoriamente é vinculado a um departamento, que pode ter vários funcionários. Além disso, todo departamento possui um único chefe;

·         Usuários devem poder realizar empréstimo de obras. Um empréstimo deve conter uma única obra e ser de um único usuário, obrigatoriamente. Empréstimos ainda devem registrar a data e horário do empréstimo, data prevista de retorno, bem como o funcionário que o realizou. Quando da devolução da obra em empréstimo, deve-se registrar a data e horário da devolução, bem como o funcionário responsável;

·         Usuários ainda podem realizar reservas de obras. Uma reserva deve conter uma única obra e ser de um único usuário, obrigatoriamente. Reservas ainda devem registrar a data e horário da reserva e data na qual a obra será retirada.

A Figura 1 apresenta o diagrama entidade-relacionamento contendo a solução para o desafio anterior. Optamos por não representar os atributos neste diagrama para não dificultar a legibilidade. Os atributos serão apresentados posteriormente na estrutura das entidades.

Figura 1. DER do desafio apresentado na SQL Magazine 31.

Vale lembrar que, apesar de uma prática comum, não é obrigatório nomear os relacionamentos, exceto quando estes irão originar tabelas, que é o caso de relacionamentos n:n ou relacionamentos que possuem atributos. Nestes casos, o nome dos relacionamentos normalmente são os nomes das tabelas resultantes. Entretanto, nomear relacionamentos é importante para uma melhor compreensão do modelo e vamos utilizar este recurso quando for necessário. Neste exemplo, nomeamos os relacionamentos entre as entidades Funcionario e Departamento, uma vez que seu entendimento fica dificultado se os relacionamentos não forem nomeados por existirem dois relacionamentos entre estas entidades.

A seguir, é descrita a estrutura inicial das entidades, onde o atributo determinante está sublinhado. Observe que, propositadamente, deixamos os atributos da editora dentro da entidade Obra:

·         Obra (cod_obra, titulo, autor_principal, ano_publicacao, situacao_obra, tipo_obra, cod_editora, nome_editora, cidade);

·         Usuario (cod_usuario, nome_usuario, endereco, telefone, CPF);

·         Emprestimo (cod_emprestimo, cod_obra, cod_usuario, data_emprestimo, horario_emprestimo, data_prevista_retorno, num_matricula_funcionario);

·         Devolucao (cod_emprestimo, data_devolucao, horario_devolucao, num_matricula_funcionario);

·         Funcionario (num_matricula, nome_funcionario, cod_departamento);

·         Departamento (cod_departamento, nome_departamento, num_matricula_chefe);

·         Reserva (cod_reserva, cod_usuario, cod_obra, data_reserva, horario_reserva, data_retirada).

Para a construção deste diagrama, algumas decisões de projeto foram tomadas e valem a pena serem discutidas:

·         Na entidade Emprestimo, uma possibilidade para a definição do atributo determinante seria a concatenação dos atributos cod_usuario e cod_obra, caracterizando uma chave composta. Entretanto, esta chave impediria que o mesmo usuário realizasse o empréstimo da mesma obra em datas diferentes. Uma possibilidade seria incluir também o atributo data_emprestimo na chave. Neste caso, preferimos incluir um atributo cod_emprestimo como atributo determinante. Este tipo de situação é chamada de chave cega, onde um novo atributo é inserido por dificuldades na determinação da chave da entidade. O mesmo raciocínio foi utilizado para determinar a chave da entidade Reserva;

·         O relacionamento 1:1 entre as entidades Emprestimo e Devolucao não necessariamente precisaria existir. Relacionamentos com esta cardinalidade muitas vezes podem ser eliminados e os atributos das duas entidades podem ser unificados, principalmente neste caso onde os atributos determinantes são os mesmos (a entidade Devolucao poderia ter um atributo determinante diferente, como cod_devolucao mas, neste caso, seria necessário definir um outro atributo que fizesse a ligação com a entidade Emprestimo). Assim, uma possibilidade seria transferir para a entidade Emprestimo todos os atributos da entidade Devolucao e eliminar esta entidade. Com isso, o empréstimo também teria os dados de sua devolução, sendo necessário ter um outro relacionamento com a entidade Funcionario, para representar o funcionário responsável pela devolução. Neste exemplo, preferimos manter as entidades separadas, uma vez que são eventos que representam situações diferentes e acontecem em momentos distintos e, no caso da devolução, pode nem acontecer;

·         Os dois relacionamentos entre as entidades Funcionario e Departamento são importantes, uma vez que representam ligações diferentes entre as entidades, um representando lotação e outro chefia. Neste caso, o relacionamento 1:1 não poderia ser eliminado, uma vez que existe um segundo relacionamento entre as mesmas entidades;

·         Optamos por não fazer um relacionamento entre as entidades Emprestimo e Reserva, representando que um empréstimo possa ter sido efetivado em função de uma reserva. Esta decisão foi tomada uma vez que não existe a necessidade de estar armazenando definitivamente as reservas, podendo as mesmas serem eliminadas após a data da reserva ter sido vencida;

·         Observe ainda que temos um problema na entidade Obra. Caso tenhamos vários exemplares da mesma obra, teremos que cadastrar a obra várias vezes, uma para cada exemplar. Este problema resolvermos mais tarde, num próximo desafio.

Análise das formas normais

O próximo passo é analisar se as entidades representadas estão normalizadas, podendo transformar-se em relações. Segue esta análise:

·         1ª FN: esta forma normal não foi satisfeita, uma vez que o atributo Endereco da entidade Usuario não é atômico. Para satisfazer esta FN, este atributo deve ser desmembrado em Logradouro, Numero, Complemento, Bairro, Cidade, UF, CEP;

·         2ª FN: o modelo encontra-se na segunda forma normal, que determina que todo atributo não chave deve ser totalmente funcionalmente dependente da chave primária, e não de parte dela. Só faz sentido preocupar-se com esta forma normal para aquelas relações que possuem chave primária composta. No estudo de caso em questão, como todas as relações possuem chaves primárias simples (contendo apenas um atributo), não é necessário preocupar-se com esta forma normal;

·         3ª FN: pode-se observar um problema com esta forma normal ao analisar a relação Obra. Neste caso, os atributos nome da editora e cidade da editora dependem funcionalmente apenas do atributo código da editora, que é um atributo não chave. Lembrando, a 3ª FN indica que todos os atributos não chave devem ser mutuamente independentes. Assim, apesar dos dados da editora poderem ser considerados como atributos da obra, torna-se importante separar estas entidades. Primeiro para não ter estes atributos redundantes. Segundo, a digitação diferente do nome de uma editora, por exemplo, faz com que consultas indiquem editoras diferentes, o que não é desejável. Outra situação indesejável acontece se uma editora mudar de cidade, ocasionando uma mudança no valor deste atributo em todas as obras desta editora.

A Figura 2 apresenta o DTR (Diagrama de Tabelas Relacionais) do modelo após a etapa de normalização. Novamente os atributos foram suprimidos para facilitar a visualização e serão detalhados a seguir.

Figura 2. O DTR do desafio anterior.

As Tabelas 1, 2, 3, 4, 5, 6, 7 e 8 apresentam as estruturas das tabelas, onde PK (Primary Key) representa a chave primária da tabela (que deve ser obrigatória) e FK (Foreign Key) representa uma chave estrangeira, onde o valor do atributo deve ser correspondente a uma chave primária da tabela a qual está referenciando, ou ser nulo, quando não for obrigatório. Isto se chama integridade referencial. Pode-se observar ainda que os tipos de dados são genéricos, não sendo particulares de nenhum SGBD específico.