The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
0 votes
91 views
Caption

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

 

asked in Databases by (149 points) | 91 views
0
1.(Select sid from supplier  where supplier.sid=catalog.sid and parts.pid=catalog.pid) -(Select sid from supplier  where supplier.sid=catalog.sid and parts.pid=catalog.pid and parts.color!=red)

2.Select sid from supplier  where ((supplier.sid=catalog.sid )and (parts.pid=catalog.pid ))and (parts.color=red)

3.Select sid from supplier  where supplier.sid=catalog.sid and parts.pid=catalog.pid and( parts.color=red) or

(parts.color=green)

4.

Select sid from supplier  where((supplier.sid=catalog.sid )and (parts.pid=catalog.pid)) and((parts.color=red) or

(parts.color!=green))
0
not in SQL, interested in RA
0
Ma'am can you please explain the 4th query? I am unable to get it.Does it require Sid of all parts which are not green?
0
plz write in R.A
0
$\prod. _{p.color=red\wedge p.color\neq green}\left ( \sigma _{\left ( s.sid=c.sid \right )\wedge \left ( c.pid=p.pid \right )}\left ( Supplier\times Parts\times Catalog \right ) \right )$

I have wriiten 4th one

now u try others
0
@Shaik

both same rt?

only notation different
0
0

@srestha mam, some of them ( 2,3,4 even with RA ) you did wrong

SQL is some what easy compared to RA

+1
Yes ma'am I understood your query but I am getting confused with the posted answer by @shaik.both seem to be correct.I am unable to identify the error and difference.can anybody help out with an example table?
+1

@Bhagyashree Mukherje,

Mam doesn't apply the condition " ALL RED "

and note that the 4th query in RA results

who has supplied atleast one red

 clearly observe that,

p.color=red ^ p.color != green   equivalent to p.color='red' only

why, with in a  tuple, on particular attribute you are applying two conditions

p.color =red means p.color != non-red

0
Yes exactly where my doubt was.Thanks.
0
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?
+1
mam,there is no need of suppliers table information
0
ok , yes u r right :)

1 Answer

+3 votes
Best answer

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

 

answered by Boss (26.8k points)
selected by
0

for 1st one

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

this also a correct query

right?

0
$\prod._{catalog.sid,p.color='red'}\left ( \sigma _{catalog.pid=parts.pid} (\text{Catalog join Parts} \right)) \div $$ \prod ._{parts.pid}$

$\left ( \sigma _{catalog.color='red'}\left ( \text{Parts} \right ) \right )$$-\prod ._{catalog.sid}\left ( \sigma _{catalog.pid=parts.pid\wedge parts.color='green'}\text{Catalog join Parts} \right )$
0
i didn't find any difference on your first comment and my answer... you simply copy and paste it...

 

which query, you second comment represent?
0
1st one I crossed "color=red " part

because without that too query give same result

right?
0

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

+1

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

0
yes rt


Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

39,584 questions
46,707 answers
140,136 comments
57,865 users