0

Given the following table schema

customer (name: string, credit: integer)
allowance (no: string, type: string)
asker(cname: string, lno: string)

asker.cname and asker.lno are foreign keys referencing customer, respectively allowance , whose keys are name, respectively no (number)

I am trying to write the relational algebra for the query to find pairs of names of customers who share the same allowance. Avoid listing a customer with himself (Tim, Tim) and avoid listing the same over and over (ex. (Tim, Jane) and (Jane, Tim) should be one)

I have tried is: 

 ρ(Cust1, π no (allownace)
 ρ(Cust2, π no (allownace)
 π name, name((Cust1 ∩ Cust2)(customer))

I believe this is incorrect. Specially, I am having trouble where I need to find customers with the same allowance and also avoiding the customer himself and repeating.

Raj Kona
  • 9
  • 1
  • 3
  • You don't seem to be using the algebra properly. You haven't given old and new names for ρ. You can't project on the same attribute twice or have two same-named attributes unless they are referenced by *relation.attribute*. Please correct. Please give a reference to the versions of the algebra & relations you are supposed to use. – philipxy Nov 20 '15 at 01:44
  • RENAME/ρ does not rename bases or assign relations to names. It returns a relation that is like a given relation but with a given old attribute name replaced by a given new attribute name. – philipxy Nov 24 '15 at 01:00
  • Try [this online relational algebra calculator](http://138.232.66.66/ra/) to get your syntax correct (at least according to theirs) & test values. – philipxy Nov 24 '15 at 09:31

1 Answers1

1

Eliminate (Tim, Tim) via σ (name1 <> name2). (The relation(ship) is non-reflexive.)

Eliminate just one of (Tim, Jane) & (Jane, Tim) but not (Tim, Tim) via σ (name1 op name2) where op is one of <= or >=. (The relation(ship) is anti-symmetric.)

Eliminate both cases by σ (name1 op name2) where op is one of < or >. (The relationship is non-reflexive and anti-symmetric.)

(Re querying & algebra see this answer.)

Community
  • 1
  • 1
philipxy
  • 14,867
  • 6
  • 39
  • 83