To fix the problem of relationship I will pass your script with the necessary changes, have a comment already explaining why the change
DROP TABLE IF EXISTS usuario;
CREATE TABLE usuario(
id INTEGER NOT NULL AUTO_INCREMENT,
email VARCHAR(50) NOT NULL UNIQUE,
nome VARCHAR(40) NOT NULL,
data_nascimento DATE NOT NULL,
PRIMARY KEY(id));
DROP TABLE IF EXISTS filme;
CREATE TABLE filme(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
duracao INT NOT NULL,
PRIMARY KEY(id));
DROP TABLE IF EXISTS genero;
CREATE TABLE genero(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
PRIMARY KEY(id));
DROP TABLE IF EXISTS rel_usuario_filme;
CREATE TABLE rel_usuario_filme(
id_usuario INTEGER NOT NULL,
id_filme INTEGER NOT NULL,
PRIMARY KEY(id_usuario, id_filme),
CONSTRAINT fk_usuario FOREIGN KEY (id_usuario) REFERENCES usuario(id),
CONSTRAINT fk_filme FOREIGN KEY (id_filme) REFERENCES filme(id));
DROP TABLE IF EXISTS rel_filme_genero;
CREATE TABLE rel_filme_genero(
id_filme INTEGER NOT NULL,
id_genero INTEGER NOT NULL,
PRIMARY KEY(id_filme, id_genero),
CONSTRAINT fk_rel_fg_genero FOREIGN KEY (id_genero) REFERENCES genero(id),
CONSTRAINT fk_rel_fg_filme FOREIGN KEY (id_filme) REFERENCES filme(id));
ALTER TABLE `test`.`rel_filme_genero`
DROP FOREIGN KEY `fk_rel_fg_filme`;
ALTER TABLE `test`.`rel_filme_genero`
ADD COLUMN `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id_filme e id_genero nao pode ser chave composta pois 1 filme pode ser comedia romantica por exemplo, entao as duas sao apenas chaves estrangeiras aqui' FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`),
ADD INDEX `fk_rel_fg_filme_idx` (`id_filme` ASC);
ALTER TABLE `test`.`rel_filme_genero`
DROP FOREIGN KEY `fk_rel_fg_genero`;
ALTER TABLE `test`.`rel_filme_genero` ADD CONSTRAINT `fk_rel_fg_genero`
FOREIGN KEY (`id_genero`)
REFERENCES `test`.`genero` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_rel_fg_filme`
FOREIGN KEY (`id_filme`)
REFERENCES `test`.`filme` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;
For your question about the total of films user 1 that is of gender 3, the rule of the normal form says not to store information that can be obtained through calculation and/or query ( do not remember the rule number and possibly not with these words ) then the correct one would be a query that makes that total p/ you
SELECT u.nome,
(select sum(1) from rel_usuario_filme ruf, rel_filme_genero rfg where ruf.id_usuario=u.id and ruf.id_filme=rfg.id_filme and rfg.id_genero=3) as total_filmes
FROM usuario u
where
-- u.nome = 'joao' ou pelo id abaixo
u.id = 1;
Please avoid long discussions in the comments; your talk was moved to the chat
– Maniero