Problem when performing Join in tables with generalization

Question

I’m having difficulty performing a JOIN in tables the problem is the following:

In a school bulletin system I have users, including students, guardians and school employees. I need to generate an SQL command that when informing the CPF of the responsible(s), it lists all students related to the CPF of that(s) responsible(is).

Follows the model:

Is this the best way to implement this "Generalization" and this relationship between the responsible ones and the students, since they are all users? Can someone help me?

SQL code:

-- -----------------------------------------------------
-- Table `testeboletim`.`tipo_usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`tipo_usuario` (
  `idtipo_usuario` INT NOT NULL AUTO_INCREMENT,
  `funcao` VARCHAR(45) NULL,
  PRIMARY KEY (`idtipo_usuario`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testeboletim`.`usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`usuario` (
  `idusuario` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `cpf` VARCHAR(20) NULL,
  `data_nasc` DATE NULL,
  `telefone` VARCHAR(20) NULL,
  `celular` VARCHAR(20) NULL,
  `email` VARCHAR(45) NULL,
  `tipo_usuario_idtipo_usuario` INT NOT NULL,
  PRIMARY KEY (`idusuario`, `tipo_usuario_idtipo_usuario`),
  INDEX `fk_usuario_tipo_usuario_idx` (`tipo_usuario_idtipo_usuario` ASC),
  CONSTRAINT `fk_usuario_tipo_usuario`
    FOREIGN KEY (`tipo_usuario_idtipo_usuario`)
    REFERENCES `testeboletim`.`tipo_usuario` (`idtipo_usuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testeboletim`.`aluno`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`aluno` (
  `idaluno` INT NOT NULL AUTO_INCREMENT,
  `usuario_idusuario` INT NOT NULL,
  `usuario_tipo_usuario_idtipo_usuario` INT NOT NULL,
  PRIMARY KEY (`idaluno`, `usuario_idusuario`, `usuario_tipo_usuario_idtipo_usuario`),
  INDEX `fk_aluno_usuario1_idx` (`usuario_idusuario` ASC, `usuario_tipo_usuario_idtipo_usuario` ASC),
  CONSTRAINT `fk_aluno_usuario1`
    FOREIGN KEY (`usuario_idusuario` , `usuario_tipo_usuario_idtipo_usuario`)
    REFERENCES `testeboletim`.`usuario` (`idusuario` , `tipo_usuario_idtipo_usuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testeboletim`.`responsavel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`responsavel` (
  `idresponsavel` INT NOT NULL AUTO_INCREMENT,
  `usuario_idusuario` INT NOT NULL,
  `usuario_tipo_usuario_idtipo_usuario` INT NOT NULL,
  PRIMARY KEY (`idresponsavel`, `usuario_idusuario`, `usuario_tipo_usuario_idtipo_usuario`),
  INDEX `fk_responsavel_usuario1_idx` (`usuario_idusuario` ASC, `usuario_tipo_usuario_idtipo_usuario` ASC),
  CONSTRAINT `fk_responsavel_usuario1`
    FOREIGN KEY (`usuario_idusuario` , `usuario_tipo_usuario_idtipo_usuario`)
    REFERENCES `testeboletim`.`usuario` (`idusuario` , `tipo_usuario_idtipo_usuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `testeboletim`.`aluno_has_responsavel`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `testeboletim`.`aluno_has_responsavel` (
  `aluno_idaluno` INT NOT NULL,
  `aluno_usuario_idusuario` INT NOT NULL,
  `responsavel_idresponsavel` INT NOT NULL,
  `responsavel_usuario_idusuario` INT NOT NULL,
  PRIMARY KEY (`aluno_idaluno`, `aluno_usuario_idusuario`, `responsavel_idresponsavel`, `responsavel_usuario_idusuario`),
  INDEX `fk_aluno_has_responsavel_responsavel1_idx` (`responsavel_idresponsavel` ASC, `responsavel_usuario_idusuario` ASC),
  INDEX `fk_aluno_has_responsavel_aluno1_idx` (`aluno_idaluno` ASC, `aluno_usuario_idusuario` ASC),
  CONSTRAINT `fk_aluno_has_responsavel_aluno1`
    FOREIGN KEY (`aluno_idaluno` , `aluno_usuario_idusuario`)
    REFERENCES `testeboletim`.`aluno` (`idaluno` , `usuario_idusuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_aluno_has_responsavel_responsavel1`
    FOREIGN KEY (`responsavel_idresponsavel` , `responsavel_usuario_idusuario`)
    REFERENCES `testeboletim`.`responsavel` (`idresponsavel` , `usuario_idusuario`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

In fact what you need is a smaller number of fields, tables and consequently relationships:

The search select would look something like:

SELECT
    A.*
FROM Responsavel R
INNER JOIN Usuario U ON R.idUsuario = U.idUsuario
INNER JOIN Aluno A ON A.idResponsavel = R.idResponsavel
WHERE
    R.cpf = '99.999.999-99'

Answer 2

A generalization relationship can be modeled in many ways. The way I think will serve you best is as follows::

• The most generic table has some primary key.

• The most specific tables have the same primary key as the most generic table.

• The primary key of the most specific tables are also foreign keys of the most generic table.

Also, if your table has a field AUTO_INCREMENT then that field is enough to be the primary key. That is, in this case you do not need to have more fields in the primary key than is marked as AUTO_INCREMENT. This will also simplify foreign keys that are exported from this table and the indexes used.

Never use ON DELETE NO ACTION or ON UPDATE NO ACTION. This will end your referential integrity. Even more that you are using the Innodb that was conceived with the idea of respecting the referential integrity (thing that Myisam does not).

Here’s what your SQL code would look like thinking about what I explained above:

CREATE TABLE IF NOT EXISTS `testeboletim`.`tipo_usuario` (
  `id_tipo_usuario` INT NOT NULL AUTO_INCREMENT,
  `funcao` VARCHAR(45) NULL,
  PRIMARY KEY (`id_tipo_usuario`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `testeboletim`.`usuario` (
  `id_usuario` INT NOT NULL AUTO_INCREMENT,
  `nome` VARCHAR(45) NULL,
  `cpf` VARCHAR(20) NULL,
  `data_nasc` DATE NULL,
  `telefone` VARCHAR(20) NULL,
  `celular` VARCHAR(20) NULL,
  `email` VARCHAR(45) NULL,
  `id_tipo_usuario` INT NOT NULL,
  PRIMARY KEY (`idusuario`),
  INDEX `fk_usuario_tipo_usuario_idx` (`tipo_usuario_idtipo_usuario` ASC),
  CONSTRAINT `fk_usuario_tipo_usuario`
    FOREIGN KEY (`id_tipo_usuario`)
    REFERENCES `testeboletim`.`tipo_usuario` (`id_tipo_usuario`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `testeboletim`.`aluno` (
  `id_aluno` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_aluno`),
  CONSTRAINT `fk_aluno_usuario`
    FOREIGN KEY (`id_aluno`)
    REFERENCES `testeboletim`.`usuario` (`id_usuario`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `testeboletim`.`responsavel` (
  `id_responsavel` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id_responsavel`),
  CONSTRAINT `fk_responsavel_usuario`
    FOREIGN KEY (`id_responsavel`)
    REFERENCES `testeboletim`.`usuario` (`id_usuario`)
) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `testeboletim`.`aluno_has_responsavel` (
  `id_aluno` INT NOT NULL,
  `id_responsavel` INT NOT NULL,
  PRIMARY KEY (`id_aluno`, `id_responsavel`),
  INDEX `fk_aluno_has_responsavel_responsavel_idx` (`id_responsavel` ASC),
  INDEX `fk_aluno_has_responsavel_aluno_idx` (`id_aluno` ASC),
  CONSTRAINT `fk_aluno_has_responsavel_aluno`
    FOREIGN KEY (`id_aluno`)
    REFERENCES `testeboletim`.`aluno` (`id_aluno`),
  CONSTRAINT `fk_aluno_has_responsavel_responsavel`
    FOREIGN KEY (`id_responsavel`)
    REFERENCES `testeboletim`.`responsavel` (`id_responsavel`)
) ENGINE = InnoDB;

Note that the foreign keys exported have become much simpler and the indexes will also be much simpler. Indices that match the primary key are unnecessary, since the database will already implicitly create them.

Also, you really don’t need that which is below, even though it was generated automatically. So I pulled out:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Finally, your SQL query would look like this:

SELECT DISTINCT ua.*
FROM usuario ua
INNER JOIN aluno a ON ua.id_usuario = a.id_aluno
INNER JOIN aluno_has_responsavel h ON a.id_aluno = h.id_aluno
INNER JOIN responsavel r ON h.id_responsavel = r.id_responsavel
INNER JOIN usuario ur ON ur.id_usuario = r.id_responsavel
WHERE ur.cpf = '12345678910';

Ah, and I don’t know if you really need the table tipo_usuario. If her idea is to know if a user is a student, collaborator or guardian, then she is unnecessary. For this, just see which of the tables it appears in.