I'm working on some book exercises and can't find an explanation on how to express the following in relational algebra. I did find an answer for SQL though but I'm interested in whether there are any alternatives to solve it.
The question from the book is: Find those pairs of PC models that have both the same speed and RAM. A pair should be listed only once; e.g., list(i,j) but not (j,i).
The schema for PC is:
PC (
model INTEGER NOT NULL PRIMARY KEY,
speed NUMERIC,
ram INTEGER,
hd INTEGER,
price INTEGER);
and the query I made:
SELECT PC.model, PC1.model
FROM PC, PC AS PC1
WHERE PC.model != PC1.model AND PC.speed = PC1.speed AND PC.ram = PC1.ram;
which returns:
model | model
-------+-------
1004 | 1012
1012 | 1004
The relational algebra expression I constructed according to:
So in both the SQL query and the relational algebra the matching results will be listed twice but in reversed order. How do I make it be listed only once irrespective of order?
Just use the fact that if PC.model != PC1.model
, then one is smaller than the other. So if you need one of these pairs, just use either PC.model < PC1.model
or PC.model > PC1.model
(depending on which pair you want to preserve).
SELECT PC.model, PC1.model
FROM PC, PC AS PC1
WHERE PC.model < PC1.model AND PC.speed = PC1.speed AND PC.ram = PC1.ram;