edited by
1,590 views
0 votes
0 votes
Caption

Can plz any one give solutions of e,f,g,h in relational algebra?

 

edited by

1 Answer

Best answer
3 votes
3 votes

Suppliers(Sid,Sname,rating) ------> (sid) is key

Parts(Pid,Pname,color) ------> (pid) is key

Catalog(sid,pid,cost) ------> (sid,pid) is key

 

1) Retrieve sid's supplied only RED parts

         it means the suppliers didn't supply atleast one non-red parts ===> ( total red suppliers ) - ( total non-red suppliers )

    πsid ( Catalog  ⊠ catalog.pid = Parts.pid   ( σcolor = 'red' ( Parts ) )  )  -  πsid ( Catalog  ⊠ catalog.pid = Parts.pid   ( σcolor ≠ 'red' ( Parts ) )  )

 

2) Retrieve sid's supplied EVERY RED part

     it means sid's who supplied every RED parts ===> when EVERY ( ALL ) term encounter, use DIVISION operator in RA

    πCatalog.sid,P.color ( Catalog  ⊠ Catalog.pid = Parts.pid  ( Parts ) )  ) /   πParts.pid (  ( σcolor = 'red' ( Parts ) )  )

 

3) Retrieve sid's supplied EVERY RED part or EVERY green part

     Union the RED and Green 

   ( πCatalog.sid,P.color ( Catalog  ⊠ Catalog.pid = Parts.pid  ( Parts ) )  )  /  πParts.pid (  ( σcolor = 'red' ( Parts ) )  ) )

πCatalog.sid,P.color ( Catalog  ⊠ Catalog.pid = Parts.pid  ( Parts ) )  )  /  πParts.pid (  ( σcolor = 'green' ( Parts ) )  ) )

 

4) Retrieve sid's supplied EVERY RED part but not ANY GREEN part

     Set difference of All RED and ANY Green 

   (  πCatalog.sid,P.color ( Catalog  ⊠ Catalog.pid = Parts.pid  ( Parts ) )  )  /  πParts.pid (  ( σcolor = 'red' ( Parts ) )  ) )

-

πCatalog.sid ( Catalog  ⊠ Catalog.pid = Parts.pid ( σcolor = 'green' ( Parts ) )  ) )

 

selected by

Related questions

–1 votes
–1 votes
0 answers
1
Himanshu Kashyap asked Jan 12, 2019
653 views
PLEASE EXPLAIN WHEATHER IT WOULD BE SOME EC DEPARMENT OR ALL EC DEPARTMENT I THINK ALL BECOZ IT IS SELECTION EID OF STUDENT WHO HAVE NOT ENROLLED IN ANY COURSE OF EC
1 votes
1 votes
1 answer
3
nikkey123 asked Jan 29, 2018
1,610 views
Consider the following database tableSupplier (Sid, Sname, rating)Parts (Pid, Pname, color)Catalog (Sid Pid, cost)Which of the following SQL query correct representation ...