Relational Algebra - Query in 3 tables

Asked

Viewed 212 times

-1

Good afternoon,I’m having a hard time returning a value from my bank when it has two attributes, I’m starting from this statement.

Select the names of the parts that use the materials with the names "Tamp Round 1m" and "Screw 3cm", (can not appear the part that uses only one of them).

I have this structure of tables

inserir a descrição da imagem aqui

I tried to imagine your sql query to be able to pass to Relational Algebra being this query:

  Select * from material_peca AS MP join peca AS P on P.codigop = MP.codigop JOIN material AS M on MP.codigom = M.codigom 
where M.nomem = "Tampo Redondo 1m" or M.nomem = "Parafuso 3cm";

However it returns all without exception, but I need it to return only when there are two ways, then tried with a select inside select:

Select * from material_peca AS MP join peca AS P on P.codigop = MP.codigop JOIN material AS M on MP.codigom = M.codigom 

where M.nomem = "Tampo Redondo 1m" and (select M.nomem from M where M.nomem = "Parafuso 3cm");

This is an exercise to be able to understand more about Algebra but I can not understand even if you want the bank, if anyone can help me... I did this part in Algebra but I do not know if it is correct:

π nomep ((Σ nomem = Round Top 1m (material_peca |x| material)) (Σ name = Screw (material_peca |x| material)))

mysql sql database

  • At your command there is no reference to the piece with the name "Medium Wooden Shelf" as the exercise asks. You did not enter the meaning of "when there are two ways". In your second SQL command you do not make sense of this AND (sub-select). It would be good for you to post the structure of your tables.

  • You want to return the name, when nomep is "Medium Wooden Shelf" that?

  • I would like to return the nomep when the nomem is "Round Top 1m" and "Screw 3cm"

  • Check if something like this: :Select * from material_peca AS MP Inner Join peca AS P on P.codigop = MP.codigop Inner JOIN material AS M on MP.codigom = M.codigom and (M.nomem = "Round Top 1m" or M.nomem = "Screw 3cm")

  • Since your comment above "I would like to return the name when the name is "Round Top 1m" and "Screw 3cm"", I can’t see what its relation to the statement "Select the names of the materials used in the manufacture of the piece with the name "Medium Wooden Shelf"". are two different things

  • There is no sense in the phrase "I would like to return the nomep when the nomem is "Round Top 1m" and "Screw 3cm"". A field cannot simultaneously contain 2 values (except for any multivariate attributes, which is not the case).

Show 1 more comment

3 answers

1


UNION operator must resolve, below the query:

Select * from material_peca AS MP 
inner join peca AS P on P.codigop = MP.codigop 
inner join material AS M on MP.codigom = M.codigom 
WHERE (M.nomem = "Tampo Redondo 1m")
UNION
Select * from material_peca AS MP 
inner join peca AS P on P.codigop = MP.codigop 
inner join material AS M on MP.codigom = M.codigom 
WHERE (M.nomem = "Parafuso 3cm")

This way it will bring the results that are in the 2 queries.

0

Taking into consideration the comment: I would like to return the nomep when the name is "Round Top 1m" and "Screw 3cm", I believe you can try something like:

Select * from material_peca AS MP inner join peca AS P on P.codigop = MP.codigop 
                                  inner JOIN material AS M on MP.codigom = M.codigom 
                                  and (M.nomem = "Tampo Redondo 1m" or M.nomem ="Parafuso 3cm")

  • In sql yes however I also need the Relational Algebra, could explain me?

0

Despite having studied Relational Algebra for a long time, I believe that the expression can be translated into SQL as:

SELECT P.nomep from material_peca AS MP JOIN peca AS P ON P.codigop = MP.codigop
WHERE EXISTS (SELECT * FROM material AS M WHERE M.codigom = MP.codigom AND M.nomem = 'Tampo Redondo 1m')
  AND EXISTS (SELECT * FROM material AS M WHERE M.codigom = MP.codigom AND M.nomem = 'Parafuso 3cm');

Is that what you want is not the intersection?

π nomep ((σ nomem = Tampo Redondo 1m (material_peca |x| material)) ⋂ (σ nomem = Parafuso (material_peca |x| material)))

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.