Give an expression in relational algebra to express each of the following querie

Question

Give an expression in relational algebra to express each of the following queries:

Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) Give an expression In the relational algebra to express each of the following queries. Find the names of suppliers who supply some blue part. Find the sids of suppliers who supply some red part or are at 102 Pine Street. Find the sids of suppliers who supply every blue part. Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid. Find the pids of the least expensive parts supplied by suppliers named Ramebit Asrim.

Explanation / Answer

[a]

Select a.sname
From Suppliers a
JOIN Catalog b
ON (a.sid = b.sid)
where b.pid SOME (Select pid
                From parts
                Where color = 'blue');
    
[b]

Select a.sid
From Suppliers a
JOIN Catalog b
ON (a.sid = b.sid)
where b.pid SOME (Select pid
                From parts
                Where color = 'blue')
AND a.address = '102 Pine;

[c]

Select a.sid
From Catalog a
where b.pid IN (Select pid
                From parts
                Where color = 'blue');
    
[d] For this we have to use self join on catalog table

select a.sid , b.sid
    FROM Catalog a
JOIN Catalog b
ON (a.cost > b.cost);

[e]

SELECT a.pid
FROM Catalog a
WHERE a.cost = (SELECT MIN(COST)
               FROM Catalog a
      JOIN Suppliers b
                  ON (a.sid = b.sid)
                  Where b.sname = 'Ramebit Asrim') ;
               

    

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.