Database modeling helps you!

Asked

Viewed 2,143 times

1

I need to make a library system for book loans, in which I drew up this database diagram. The professor said that this diagram is inconsistent. What needs to be done, it’s just these tables that I’m really going to need?

inserir a descrição da imagem aqui

sql-server modeling database

  • t_loan can not repeat fields, cd_Aluno already has in the relationship table so pq it is there? this is an example of inconsistency.

1 answer

4

When modeling tables in a relational database should begin by trying not to repeat the information in different tables by making use of the relations.

For example there is a table t_alunos_has_t_emprestimos that seems to serve to relate loans with students and at the same time with books. But the information of the student seems to already exist in the table t_emprestimos.

I would advise the following process:

  1. Define your entities.
    • In this case at least the following Alunos, Emprestimos and Livros. However, depending on the requirements you may decide to add more entities. For example, imagine the case where a Student has more than one contact. Maybe it makes sense to add an entity Contacto. The decision is yours.
    • Be wary of tables with many columns. Often this is a sign of the existence of a new entity that deserves its own table.
  2. In each of your entities add columns that only relate to that entity.
    • For example fields like nome for the entity Aluno. Or titulo, ano, isbn for the entity Livros.
  3. Think of relationships. They will be "one for many" or "many for many"?
    • As an example a Emprestimo is related only to a Aluno, is a "one to many" relation. You must put a field aluno on the table Emprestimo.
    • Imagine that each Emprestimo may contain several Livros. It is a "many to many" relation. This type of relation requires a new table. You must define a table (pivot) that contains the relation between Emprestimo and Livro.

Read more about database normalization. You can find some information about "Normal Forms" here or, if you feel comfortable with English, here. Try to follow the Third Normal Form.

It’s hard to help otherwise because it all depends on your requirements.

Browser other questions tagged sql-server modeling database

You are not signed in. Login or sign up in order to post.