GATE CSE 2012 | Question: 50

Question

Consider the following relations $A, B$ and $C:$

$$\overset{\text{A}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age}  \\\hline\text{12}& \text{Arun} & \text{60} \\\hline\text{15}& \text{Shreya} & \text{24} \\\hline\text{99}& \text{Rohit} & \text{11}  \\\hline \end{array}} \qquad
\overset{\text{B}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Name} & \text {Age} \\\hline  \text{15}& \text{Shreya} & \text{24} \\\hline\text{25}& \text{Hari} & \text{40} \\\hline\text{98}& \text{Rohit} & \text{20} \\\hline\text{99}& \text{Rohit} & \text{11}  \\\hline \end{array}}\qquad
\overset{\text{C}}{\begin{array}{|c|c|c|} \hline \text {ID} & \text {Phone} & \text {Area} \\\hline  \text{10}& \text{2200} & \text{02} \\\hline\text{99}& \text{2100} & \text{01} \\\hline \end{array}}$$

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of $A\cup B$ is the same as that of $A$.

$$(A\cup B)\bowtie _{A.Id > 40 \vee C.Id < 15} C$$

• A. $7$
• B. $4$
• C. $5$
• D. $9$

Comments

NOTE:

“We will adopt the convention that the resulting relation has the same attribute names as the first relation R. It is always possible to rename the attributes in the result using the rename operator.”

So, (A U B).id = A.id

Navathe 7^th edition Page 248.

Taken from :: https://www.auhd.site/upfiles/elibrary/Azal2020-01-22-12-28-11-76901.pdf

 

---

Here, chances of doing silly mistake is more except that question is easy.

---

here what this question releted to :- joins
there are 3 table they want to join ok,

------------------------------------------------------------------------------------------------------------------------------------------------
here, something that present in the qustion
1) union

2. join 
   ----------------------------------------------------------------------------------------------------------------------------------
   usually vertical 8 like symbol use for natuaral join but if they but some condtion with this then this convert into conditional cross product.
   -------------------------------------------------------------------------------------------------------------------------------
   now here in order to solve this question remember relational algebra always eliminate duplicatiy

so the order in this question is good to solve 

 do union------>eleminate duplicate tuple------>then aplpy condition on the table a union b------>and if some tuple satified this condition they will do cross product with right side table.

1) when you will do union and after eliminating duplcay you will get 5 tuple.

2) after that if you will look at this condition they are like union means if you will look at first table and if they  satisfyed

 
a.id>40 then they will get cross product with c even with out checking condition becuse this is or and T or anything always true .
3) from second point if you will do then in  aUb two tuple will satisfied condition of a.id>40 so these two tuple make pair

 

with c and tuple they will give is 4 (simply like cross product )
now there tuple left in aUb which doesnot satisfied condition  of id>40 so they will go and check for c>15 so only one

 

tuple will satisfed from c tablle so these 3 tuple will get cross product with that only one tuple so totol 7 tuple will come

in the result.

 

Answer 1

The join mentioned in the query is theta join ( join does the cross product whenever the condition is matched).

Query: $(AUB)\Join _{A.Id>15 VC.Id<15}C$. First we find AUB.

AUB

Id	Name	Age
12	Arun	60
15	Shreya	24
25	Hari	40
98	Rohit	20
99	Rohit	11

C

Id	Phone	Area
10	2200	02
99	2100	01

Now, if any one of the join condition matches, we do the cross product of tuples where condition becomes TRUE.

For first four tuples of (AUB), A.ID>15 remains FALSE, but C.ID<15 becomes TRUE.

So, First tuple of C appears cross product with first four tuples of (AUB).

And for last two tuples of (AUB) A.ID>15 becomes TRUE, but C.ID<15 remains FALSE.

So, last two tuples of (AUB) are cross product with each of the two tuples of C.

We get the final result as:

Query Result

Id	Name	Age	Id	Phone	Area
12	Arun	60	10	2200	02
15	Shreya	24	10	2200	02
25	Hari	40	10	2200	02
98	Rohit	20	10	2200	02
98	Rohit	20	99	2100	01
99	Rohit	11	10	2200	02
99	Rohit	11	99	2100	01

So, total 7 tuples are in result.

Ans: (A).

Answer 2

Answer (A)

Result of AUB will be following table

Id   Name    Age
----------------
12   Arun    60
15   Shreya  24
99   Rohit   11
25   Hari    40
98   Rohit   20

The result of given relational algebra expression will be

Id   Name    Age  Id   Phone Area
---------------------------------
12   Arun    60   10   2200   02 
15   Shreya  24   10   2200   02   
99   Rohit   11   10   2200   02 
25   Hari    40   10   2200   02 
98   Rohit   20   10   2200   02 
99   Rohit   11   99   2100   01
98   Rohit   20   99   2100   01