MadeEasy Workbook: Databases - Relational Algebra

Question

Caption

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

 

Comments

yes @Shaik I understood it need division operation

but here we need to join 3 tables Supplier, Parts and Catalog

right?

plz tel me why u havenot done that?

---

mam,there is no need of suppliers table information

---

ok , yes u r right :)

Answer 1

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 ) )  ) )

 

Comments

2nd comment is for number h) i.e. 4th one

chk u missed p.color=red

and u havenot directly join with Catalog with Parts

just minor update I have done :)

---

for 1st one... Yes, that didn't make any difference... Your query also right...

for 4th one...

i use p.color="red" at division query

your query also correct but there is a minor mistake in that

used p.colour="red" used with projection operator but it should be with selection operator

---

yes rt