I would like your opinion about an Entity-Relationship Diagram of a POSTGRESQL script

Question

I am doing a work of the college whose goal is to make a database of a particular library and to elaborate an entity-relationship diagram of it , I made the script in postgresql but I have a lot of difficulty in the time to pass to make the diagram in brmodelo, Especially on the cardinality part, could you help me? Here is my library script. Below I will post the DER.

CREATE TABLE CATEGORIA(
ID SERIAL PRIMARY KEY,
DESCRICAO VARCHAR (50) NOT NULL
);

CREATE TABLE EDITORA(
ID SERIAL PRIMARY KEY,
NOME VARCHAR (50) NOT NULL
);

CREATE TABLE AUTOR(
ID SERIAL PRIMARY KEY,
NOME VARCHAR (50) NOT NULL
);

CREATE TABLE LIVRO(
  ID SERIAL PRIMARY KEY,
  NOME VARCHAR (50) NOT NULL,
  ID_CATEGORIA INT,
  ID_AUTOR INT,
  ID_EDITORA INT,
  FOREIGN KEY (ID_CATEGORIA) REFERENCES CATEGORIA(ID),
  FOREIGN KEY (ID_AUTOR)     REFERENCES AUTOR(ID),
  FOREIGN KEY (ID_EDITORA) REFERENCES EDITORA(ID)
);

THE DER:

Answer 1

The MER (relationship entity model) should be the starting point for you Elaborate your exercise. Of course, later you will develop your own Method to be able to accurately map entities, their relationships and transform Is

With practice, you will realize that it is much easier to view the model in reverse (from the bank to the model and realize the deficiencies).

Instead of answering your question completely and, incidentally, designing it depends a lot on the level you want to reach, we can simplify too much or leave too complete (this is the risk that all modeling runs)I will propose to answer your question in a way that will teach you to reflect on abstraction and modeling. That’s what you need from what I noticed in your question. And it’s not hard.

Come on...

Nouns tend to be Entities in the Model: Verbs tend to be roles / relationships

Nouns: Book, Author, Publisher

Now let’s think about the verbs to see the relationships between the entities (all in the singular)

Book is CLASSIFIED in Category <<>> Category CLASSIFIES book

Author REDIGE Book <<>> Book is WRITTEN by Author

Translator Translates Book <<>> Book is TRANSLATED by Translator

(But why do we need to think about the proofreading translators etc? Because in a library system the classification considers several data, look at the catalogue of a book).

Editora PUBLICA Livro Book is PUBLISHED by Publisher

When the book is published, there is an edition! Therefore it is not the Book that is published, but the EDITION of it, which it has, number, date etc.

Now, let’s take a closer look at the relationships

Does the Publishing House actually publish the Book? No, physically the publication is associated with an edition that has print run, edition, date, revision and many other attributes.

Author writes book? How does co-author? And the translator? Wouldn’t it be better instead to have an Entity called AUTHOR have a so-called person, and it takes on a participation role? Oops! Participation? In what? Participation in the Book.

Then see an example for entities only:

PERSON <<<< PARTICIPATION >>>> BOOK

The rest I’ll leave for you to finish your work ;-) (Train a lot modeling!)

**Now the pump for you to think: ** BUT WHAT WOULD MY MODEL LOOK LIKE IF IN THE SECOND EDITION THE TRANSLATOR WERE SOMEONE ELSE FROM A PARTICULAR BOOK? By the way, what if we changed the name of the relationship Participation to "Edition"? (now it’s time for you to exercise around :-D)