The question asks:
Given two relations A and B, Both of which are over attributes x and y. Write a query under bag semantics that returns A if B is empty and B otherwise
The way I interpreted this question was “empty” meant there were no tuples in the relation.
The question is given in a general sense so there are no values associated with each relation.
Hope someone can give me guidance to get started.
Edit: So far I see that if B is empty then A union B would yield the correct answer. But in the case that B isn’t empty we would need to take the intersection with B i.e. (A union B) intersect B but this wouldn’t work when B is empty
Edit: To give an example in the case where A and B were both non-empty:
A: ((1,1), (2,2))
B: ((3,3), (4,4))
The query should return: ((3,3), (4,4))
But, if we change B to: ()
The query should return: ((1,1), (2,2))
I just cant wrap my head around what possible combination of set operators would allow me to achieve this.
Edit: Here’s a link to basically the exact slide deck used to teach us Relational Algebra, about 3/4 of the way through it defines Algebra on bags which is what my question uses. The textbook used in class is “Database Management Systems” by Ramakrishnan and Gehrke 3rd edition page 100, but this was an optional text and we were told our main reference should be the slide deck.
To condense the slides, our algebra has Selection, Projection, Renaming, Cartesian Product, Union and Difference. All other operations can be made up with a combination of these.
To address where the problem originated from it was from an old problem sheet I had for my databases module last semester, I revisited it recently to revise relational algebra for an upcoming project I have but this question has stumped me for a few days. The lecturer who came up with the problems has left so unfortunately I cant ask them.
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
As you note, A ∪ B returns A if B = ∅, otherwise it returns tuples from both A and B.
In other words, the result is correct, except it includes A when B is non-empty.
You need an extra expression E, which would return A if B is non-empty; otherwise ∅.
The required result would then be given by:
(A ∪ B) – E
That would remove A from the result only when B is non-empty.
To find that E, consider how A × B behaves when B = ∅.
That should give you enough to find a suitable E and solve your problem.
Spoiler solution below.
E = A ∩ πA (A × B)