SQL database structure of a library

Question

I need to write and populate a Mysql database relative to the control of a library. This involves: 3 tables: | publisher | book | author |

The 'publisher' table must have: id, name, city, state, country.

The table 'book' should have: ISBN, name, year.

The table 'author' should have: Cpf, date of birth, first name, second name, country.

For the creation of the database and each table I am not having problems. The problem is that I need to organize these tables in a way that correlates, for example, when I want to delete all book records from a single author (which would involve two different tables for a single command).

I read about it and I think it’s the subject "joins", but I don’t even know where to start.

Answer 1

Create the table editora then the table autor, when creating the table livro make the relationship between them. EX:

create table editora
(
   id integer(11) primary key NOT NULL,
   nome varchar(50) NOT NULL,
   endereco varchar(50) NOT NULL
);

create table autor 
(
  id integer(11) primary key NOT NULL,
  nome varchar(50) NOT NULL,
  idade integer(3) NOT NULL
);

create table livro 
(
  id integer(11) primary key NOT NULL,
  fk_autor integer(11) NOT NULL,
  fk_editora integer(11) NULL,
  titulo varchar(50) NOT NULL,
  valor float NOT NULL,
  foreign key(fk_autor) references autor(id),
  foreign key(fk_editora) references editora(id)
);