Script for a particular SQL library

Question

Imagine that a client contacted and requested the creation of a system to register the books of the private library, in your conversation with him you arrived in the following tables that the system should contain:

• CATEGORY
• PUBLISHING HOUSE
• AUTHOR
• BOOK

In your conversation you have defined the following Keys and References:

Table CATEGORY:

• ID is primary key.

AUTHOR TABLE:

• ID is primary key.

PUBLISHER’S TABLE:

• ID is primary key.

Table BOOK:

• ID is primary key.
• ID_CATEGORIA is a foreign key and ID reference of the CATEGORY table.
• ID_AUTOR is a foreign key and reference ID of the AUTHOR table.
• ID_EDITORA is a foreign key and reference ID of the PUBLISHER table.

Based on the tables, keys and references established with your client:

1. The ERI (Entity and Relationship Diagram).
2. A script containing the SQL commands to create the tables along with the fields (attributes) and their respective types (using the concepts applied in class and their creativity).
3. Create the insertion commands for each table, where each table should have at least 5 records.

Heed:
Don’t forget to create the primary keys and the foreign relationships between the tables.

The faculty refuses to take any doubt or correct something because it is a job worth note, then I ask for your help. It follows what I have done so far:

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,
FOREIGN KEY (ID_CATEGORIA) REFERENCES CATEGORIA(ID),
FOREIGN KEY (ID_AUTOR)     REFERENCES AUTOR(ID),
FOREIGN KEY (ID_EDITORA) REFERENCES EDITORA(ID)
); 


INSERT INTO CATEGORIA(DESCRICAO) VAUES('ADMINISTRACAO');

INSERT INTO CATEGORIA(DESCRICAO) VAUES('TECNOLOGIA');

INSERT INTO CATEGORIA(DESCRICAO) VAUES('GASTRONOMIA');

INSERT INTO CATEGORIA(DESCRICAO) VAUES('EDUCACAO FISICA');

INSERT INTO CATEGORIA(DESCRICAO) VAUES('MEDICINA');

INSERT INTO EDITORA(NOME) VAUES('ARTICA');

INSERT INTO EDITORA(NOME) VAUES('MEDIUM');

INSERT INTO EDITORA(NOME) VAUES('DIAD');

INSERT INTO EDITORA(NOME) VAUES('ZEN');

INSERT INTO EDITORA(NOME) VAUES('MERIZ');

INSERT INTO AUTOR(NOME) VAUES('MERLINDO PC');

INSERT INTO AUTOR(NOME) VAUES('COSTA VAZ');

INSERT INTO AUTOR(NOME) VAUES('LERIANA LUP');

INSERT INTO AUTOR(NOME) VAUES('AREHT BLAE');

INSERT INTO AUTOR(NOME) VAUES('MOMA FENG');

INSERT INTO LIVRO(NOME,ID_CATEGORIA,ID_AUTOR,ID_EDITORA) VALUES('ERA TOYOTA',1,1,1);

INSERT INTO LIVRO(NOME,ID_CATEGORIA,ID_AUTOR,ID_EDITORA) VALUES('Angular 10',2,2,2);

INSERT INTO LIVRO(NOME,ID_CATEGORIA,ID_AUTOR,ID_EDITORA) VALUES('Salada de Frutas - A a Z',3,3,3);

INSERT INTO LIVRO(NOME,ID_CATEGORIA,ID_AUTOR,ID_EDITORA) VALUES('Exercícios de Rotina',4,4,4);

INSERT INTO LIVRO(NOME,ID_CATEGORIA,ID_AUTOR,ID_EDITORA) VALUES('Medicina Alternativa',5,5,5);

On line 19 of the script, when and to create the category Fkey, presents me this error:

ERROR: column "id_categoria" referenced in foreign key constraint does not exist

Answer 1

Now seeing the whole script I could see the problem, which is here:

CREATE TABLE LIVRO(
ID SERIAL PRIMARY KEY,
NOME VARCHAR (50) NOT NULL,
FOREIGN KEY (ID_CATEGORIA) REFERENCES CATEGORIA(ID),  <- ONDE ESTÁ ID_CATEGORIA?
FOREIGN KEY (ID_AUTOR)     REFERENCES AUTOR(ID),
FOREIGN KEY (ID_EDITORA) REFERENCES EDITORA(ID)
); 

Note that you are referencing a column that does not exist in your "ID_CATEGORIA" table, as well as "ID_AUTOR" and "ID_EDITORA". To add to contraint from the foreign key, the column needs to exist no table, just add these columns:

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