Relational library model, huh?

Question

This model I made this cool or is redundant yet?

[Edited] The database has been modified. Can I now have better queries? Here’s how it is now:

Answer 1

1. That one model is pretty flawed. Obra and Exemplar are related 1-to-1, and with that, the result is that they are effectively a single logical entity or that Exemplar is specialization of Livro, and neither of those two things is what you wanted. It should be a 1-to-N relationship.

   The field idobra of Exemplar should be FK, but not PK. A new field idexemplar would be added there. Foreign key in Emprestimo would then be idexemplar.

2. In Obra, the field quantidade is redundant. For example, to obtain the amount from a given idobra, you would do that:

   SELECT COUNT(e.*)
   FROM Exemplar e
   WHERE e.idobra = :idobra
   

3. I see no need to specialize Obra in Livro and in Periodico. It may be necessary further ahead, but at the moment, this is not bringing much gain no. Having the fields ISBN and ISSN in Obra with a check Constraint which prohibits both of them from being NOT NULL simultaneously can be better. This check Constraint that would be it:

   CONSTRAINT livro_coerente
   CHECK ((isbn IS NULL) <> (issn IS NULL))
   

   However, if you prefer to keep Livro and Periodico separate (dispensed with this check Constraint), you do SELECT in them so:

   -- Seleciona periódicos.
   SELECT p.issn, o.*
   FROM Periodico p
   INNER JOIN Obra o ON p.idobra = o.idobra
   -- Pode acrescentar um WHERE se quiser.
   

   -- Seleciona livros.
   SELECT l.issn, o.*
   FROM Livro l
   INNER JOIN Obra o ON l.idobra = o.idobra
   -- Pode acrescentar um WHERE se quiser.
   

   If you’d rather keep it together:

   -- Seleciona periódicos.
   SELECT o.*
   FROM Obra o
   WHERE o.issn IS NOT NULL
   -- Pode acrescentar um AND se quiser.
   

   -- Seleciona livros.
   SELECT o.*
   FROM Obra o
   WHERE o.isbn IS NOT NULL
   -- Pode acrescentar um AND se quiser.
   

4. I don’t see how Devolucao is something that makes sense to be separated from Emprestimo. A loan can only have a refund and a refund can only belong to a loan. Until I understand that this way, you guarantee that the dataDevolucao and the idfuncionario return cannot stay in a state where one is null and the other is not. However, this type of restriction would be easier to deal with check Constraint. My suggestion is to set the field dataDevolucao DATE NULL in Emprestimo with the fields idFuncionarioEmprestimo INTEGER NOT NULL [FK] and idFuncionarioDevolucao INTEGER NOT NULL [FK]. To check Constraint would that be:

   CONSTRAINT devolucao_coerente
   CHECK ((dataDevolucao IS NULL) = (idFuncionarioDevolucao IS NULL))
   

5. The table Multa I also don’t see how it makes sense to be separated from Emprestimo. The field idusuario is redundant. The check Constraint that would apply to this (after putting the fields back in Emprestimo) it would be something like that:

   CONSTRAINT multa
   CHECK (
       (
           dataMulta IS NOT NULL
           AND motivoMulta IS NOT NULL
           AND valorMulta IS NOT NULL
           AND dataMulta > dataPrevistaRetorno
           AND dataDevolucao > dataPrevistaRetorno
       ) OR (
           dataMulta IS NULL
           AND motivoMulta IS NULL
           AND valorMulta IS NULL
       )
   )
   

6. Rename the table Cadastro for Endereco, for Cadastro is a very generic name. Similarly, the fields idcadastro turn into idendereco. Better yet, make the relationship N-to-N by adding the intermediate tables Fornecedor_Endereco, Usuario_Endereco and Funcionario_Endereco, because they may have more than one address.

7. Just like the table Endereco would be N-to-N with Usuario, with Fornecedor and with Funcionario, you could also have a table Email (with Fornecedor_Email, Usuario_Email and Funcionario_Email) because users and vendors may have multiple emails as well and these are independent of the physical address. With this, the field email is no longer on the table Cadastro Endereco.

8. You can also have a table Telefone which is N-to-N with Fornecedor, Usuario and Funcionario (Fornecedor_Telefone, Usuario_Telefone and Funcionario_Telefone). The field telefone comes off the table Fornecedor. On the table Telefone, you would have the area code (DDD), the number, the extension and maybe the DDI (in case it is in another country).

9. A final recommendation would be to do what our friend Lacobus suggested in His answer to another question from you. You would have the tables Sexo, Motivo_Multa, Situacao_Exemplar and Estado_Requisicao with fixed values and existing fields would be foreign keys to these tables. This approach causes far less headache than using check constraints to do this.

10. Again in the table Exemplar, the field numeroExemplar is there for nothing. The field idexemplar is already sufficient to identify only one copy.