Doubt in MySQL table relationship Workbench

Question

Doubt in MySQL table relationship Workbench

Navigation

#1 by (3 votes)

5

Hello, I have a small problem creating a database.

My goal is to create a table to mark the attendance of people in the classes of the courses in which they are enrolled.

The 'presecas' table should contain the id of the person, the course and the respective class. My goal is to insert a tuple in the attendance table in which only the IDs of the classes that belong to the courses in which the person is enrolled appear.

I have tried several connections and in the insertion, in the attendance table, always appear IDs of the classes all, I need only appear the IDs of the classes that belong to the courses in which the person is enrolled.

This image should help you better understand what I want to do. Thanks in advance;)

mysql mysql-workbench

asked by anonymous 13.10.2015 / 02:51

1 answer

How to get a photo of Facebook profile? How to get the name of all user classes?

score 3 · Accepted Answer

My suggestion is that you remove the presence table altogether . You already have a table named pessoa_has_curso - I suggest you create a table named pessoa_has_aula , with FOREIGN KEYS pessoa and aula .

I do not have MySQL Workbench to demonstrate, but I created a shortened version of your DB.

Table curso :

CREATE TABLE curso (
curso_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
curso_nome VARCHAR(45),
PRIMARY KEY (curso_id)
);

Table aula :

CREATE TABLE aula (
aula_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
aula_nome VARCHAR(45),
curso_id INT UNSIGNED NOT NULL,
PRIMARY KEY (aula_id),
FOREIGN KEY (curso_id) REFERENCES curso (curso_id)
);

Table pessoa :

CREATE TABLE pessoa (
pessoa_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
pessoa_nome VARCHAR(45),
PRIMARY KEY (pessoa_id)
);

Table pessoa_has_curso :

CREATE TABLE pessoa_has_curso (
pessoa_id INT UNSIGNED NOT NULL,
curso_id INT UNSIGNED NOT NULL,
FOREIGN KEY (pessoa_id) REFERENCES pessoa (pessoa_id),
FOREIGN KEY (curso_id) REFERENCES curso (curso_id)
);

And now, the new table, pessoa_has_aula :

CREATE TABLE pessoa_has_aula (
pessoa_id INT UNSIGNED NOT NULL,
aula_id INT UNSIGNED NOT NULL,
FOREIGN KEY (pessoa_id) REFERENCES pessoa (pessoa_id),
FOREIGN KEY (aula_id) REFERENCES aula (aula_id)
);

I've made several insertions of people, courses and classes - but I will not put here for brevity. Putting a pessoa into a curso , is as follows:

INSERT INTO pessoa_has_curso (pessoa_id, curso_id) VALUES (3, 1);

I also put pessoa into aula :

INSERT INTO pessoa_has_aula (pessoa_id, aula_id) VALUES (1, 6);

Now, making the right queries, I believe you get all the the information you need.

For example, list items of pessoa that are also in curso :

SELECT pessoa.pessoa_nome AS Pessoas, curso.curso_nome AS Cursos
FROM pessoa_has_curso
INNER JOIN pessoa
ON pessoa_has_curso.pessoa_id = pessoa.pessoa_id
INNER JOIN curso
ON pessoa_has_curso.curso_id = curso.curso_id;

Result:

Nowlistingitemsofpessoathatarealsoinaula:

SELECTpessoa.pessoa_nomeASPessoas,aula.aula_nomeasAulasFROMpessoa_has_aulaINNERJOINpessoaONpessoa_has_aula.pessoa_id=pessoa.pessoa_idINNERJOINaulaONpessoa_has_aula.aula_id=aula.aula_id;

Result:

Now,toansweryourquestion,yousaidthatyouneedIDs aulawherepessoaisparticipating.

Aswenowhaveapessoa_has_aulatable,thissearchisdoneusingonlyit,withoutinvolvingthecursotable:

SELECTaula_idASAulaIDFROMpessoa_has_aulaINNERJOINpessoaONpessoa_has_aula.pessoa_id=pessoa.pessoa_idWHEREpessoa.pessoa_nome='Azurelle';

Result:

Takealookat SQL Fiddle .