Database question

relational algebra

Page 1 of 1

5 Replies - 1301 Views - Last Post: 02 August 2010 - 03:17 PM Rate Topic: -----

#1 vasdueva  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 141
  • Joined: 03-April 07

Database question

Posted 18 April 2010 - 01:45 PM

The following table is used for the problem.

Table
sId | sName
-----------

Where sId is an integer and the primary key.

The question asks:
--return all sId pairs where sName is the same for both sId's.
--(n,n) should not be in the table
--if (n1,n2) is in the table, then (n2,n1) should not be

New Table
sId1 | sId2
-----------

I came up with the following that returns all the pairs, but it also allows permutations(it will have (n1,n2) and (n2,n1) in the table)

Any help on removing the permutations or coming up with a different approach would be greatly appreciated.

I'll denote operations with the normally subscripted parameters in [param]

A <-- Rename[sId1,sName](Table)
B <-- Rename[sId2,sName](Table)

C <-- A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](B)

Project[sId1,sId2]©

Is This A Good Question/Topic? 0
  • +

Replies To: Database question

#2 nkasei28  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 93
  • Joined: 10-May 09

Re: Database question

Posted 03 May 2010 - 04:28 AM

Why don't you post this in the database forum?
Was This Post Helpful? 0
  • +
  • -

#3 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 90
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Re: Database question

Posted 10 May 2010 - 08:40 AM

Moved to database forum.
Was This Post Helpful? 0
  • +
  • -

#4 meherzad4u  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 07-November 09

Re: Database question

Posted 11 May 2010 - 02:10 AM

View Postvasdueva, on 18 April 2010 - 12:45 PM, said:

The following table is used for the problem.

Table
sId | sName
-----------

Where sId is an integer and the primary key.

The question asks:
--return all sId pairs where sName is the same for both sId's.
--(n,n) should not be in the table
--if (n1,n2) is in the table, then (n2,n1) should not be

New Table
sId1 | sId2
-----------

I came up with the following that returns all the pairs, but it also allows permutations(it will have (n1,n2) and (n2,n1) in the table)

Any help on removing the permutations or coming up with a different approach would be greatly appreciated.

I'll denote operations with the normally subscripted parameters in [param]

A <-- Rename[sId1,sName](Table)
B <-- Rename[sId2,sName](Table)

C <-- A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](B)

Project[sId1,sId2]©







select sid from table as a,table as b where a.sid<>b.sid and a.sname=b.sname;


Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5848
  • View blog
  • Posts: 12,707
  • Joined: 16-October 07

Re: Database question

Posted 11 May 2010 - 04:05 AM

View Postvasdueva, on 18 April 2010 - 02:45 PM, said:

C <-- A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](B)


That's some wonky syntax, there. I don't believe I've ever seen a caret (^) for a logical and.

The standard SQL syntax is what you want, unless your instructor has come up with some home grown language for it.

This should meet the requirements:
select distinct a.sId as sId1, b.sId as sId2
	from Table a
		inner join Table b
			on a.sName=b.sName
				and a.sId!=b.sId


Was This Post Helpful? 0
  • +
  • -

#6 vasdueva  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 141
  • Joined: 03-April 07

Re: Database question

Posted 02 August 2010 - 03:17 PM

View Postbaavgai, on 11 May 2010 - 03:05 AM, said:

View Postvasdueva, on 18 April 2010 - 02:45 PM, said:

C <-- A Join[A.sId1!=B.sId2 ^ A.sName=B.sName](B)


That's some wonky syntax, there. I don't believe I've ever seen a caret (^) for a logical and.

The standard SQL syntax is what you want, unless your instructor has come up with some home grown language for it.

This should meet the requirements:
select distinct a.sId as sId1, b.sId as sId2
	from Table a
		inner join Table b
			on a.sName=b.sName
				and a.sId!=b.sId




I feel the urge to defend my syntax even though this thread is months old. I wasn't try to write sql. I was trying to write relational algebra. The syntax for that isn't easy to put in text without Latex or some other math markup. The carat and upside down carat(not sure what symbol name) are used pretty commonly for the logical and and or in an algebraic syntax.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1