Library relational model?

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

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

In Obra , the quantidade field is redundant. For example, to get the quantity from a given idobra , you would do this:

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

I do not see a need to specialize Obra in Livro and Periodico . It may be necessary to go further, but at the moment, this is not bringing much gain. Having the fields ISBN and ISSN in Obra with a check constraint that prohibits both NOT NULL simultaneously can be better. This check constraint would be this:

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

However, if you prefer to keep Livro and Periodico apart (bypassing this check constraint ), you do SELECT in them like this:

-- 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 prefer to keep 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.

I do not see how Devolucao is something that makes sense to be separate from Emprestimo . A loan can only have a return and a return can only belong to a loan. Even I understand that this way, you guarantee that the dataDevolucao and idfuncionario of the return can not be in a state where one is null and the other not. However, this type of constraint would be easier to deal with check constraint . My suggestion is to put the dataDevolucao DATE NULL field in Emprestimo with the idFuncionarioEmprestimo INTEGER NOT NULL [FK] and idFuncionarioDevolucao INTEGER NOT NULL [FK] fields. The check constraint would be this:

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

The Multa table also does not see as something that makes sense to be separate from Emprestimo . The idusuario field on it is redundant. The check constraint that would apply to this (after putting the fields back in Emprestimo ) would look something like this:

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
    )
)

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

Just as the Endereco table would be N-to-N with Usuario , with Fornecedor and Funcionario , you could also have a Email Fornecedor_Email and Usuario_Email ) because users and providers can have multiple e-mails as well and these are independent of the physical address. With this, the Funcionario_Email field is no longer in the email Cadastro table.

You can also have a Endereco table that is N-to-N with Telefone , Fornecedor and Usuario ( Funcionario , Fornecedor_Telefone and Usuario_Telefone ). The Funcionario_Telefone field exits the telefone table. In the Fornecedor table, you would have the area code (DDD), the number, the extension, and perhaps the DDI (if it is in another country).

One last recommendation would be to do what our friend Lacobus suggested in his response to another question of his a>. You would have the Telefone , Sexo , Motivo_Multa , and Situacao_Exemplar tables with fixed values, and the existing fields would be foreign keys for these tables. This approach causes far less headache than using check constraints to do this.

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