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

what does it mean : "Assume that the schema of A∪B is the same as that of A" 

if A is

A

Id	Name	Age
12	Arun	60
15	Shreya	24
99	Rohit	11

it gives 4 tuples

or if A is

A

Id	Name	Age
12	Arun	60
15	Shreya	24
99	Rohit	11

25	Hari	40
98	Rohit	20

it gives 7 tuples;

---

I have also the same doubt bro. Could anyone explain the question which says "Assume that the schema of AUB is the same as that of A?"

---

Schema is nothing but attribute name or column names.

A U B schema 

Id	Name	Age

---

thanks for clearing my doubt bro

---

$(A∪B)⋈_{A.Id>40∨C.Id<15}C$ =  $(_{A.Id>40}((A∪B) \times C)) $ $∪$ $(_{C.Id<15}((A∪B) \times C)) $

---

thanks.

---

In case of Aid>40 here we take only attributes tuple of A which is 99 not AUB  that return only two tuples after join

+

Cid<15 only 10 in C which return 5 tuples after join

So total 7

---

it is given to make it clear that the relations are union compatible

---

Mistakes you can possibly do while solving this question

1. forget Rohit 98 is different then Rohit 99
2. Forget to do the cartesian product before applying join condition

Remember better thing to do here is first do the cartesian product and then select

this is how join works right

 

What is join, it is not a basic SQL operator, it is composed of ( $\times$ and $\sigma$)

follow this

rest is easy

 

7 is answer

---

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

Given the 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}}$$ This is an example of theta join and we know: $R⋈_{\theta}S = \sigma_{\theta}(R\times S)$

$\therefore (A \cup B)⋈_{A.Id>40 \vee C.Id<15}C =  (_{A.Id>40}((A\cup B) \times C)) \cup (_{C.Id<15}((A\cup B) \times C)) $

To make the query more efficient we can perform the select operation before the cross product.

$\therefore (A\cup B)⋈_{A.Id>40 \vee C.Id<15}C =  (_{A.Id>40}(A \cup B) \times C) \cup ((A \cup B) \times _{C.Id<15}C)$

Now calculate $A\cup B:$ $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} \\\hline 12 & \text{Arun} & 60 \\\hline 15 & \text{Shreya} & 24 \\\hline 25 & \text{Hari} & 40 \\\hline 98 & \text{Rohit} & 20 \\\hline 99 & \text{Rohit} & 11 \end{array}$$ Please note that union is a set operation and duplicates will not be included by default.

First perform cross-product $(_{A.Id>40}(A\cup B) \times C)$, i.e., Multiply each row of $_{A.Id>40}(A\cup B)$ with each row of $C:$ $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} & \textbf{C.ID} & \textbf{Phone} & \textbf{Area} \\\hline 98 & \text{Rohit} & 20 & 10 & 2200 & 02 \\\hline 98 & \text{Rohit} & 20 & 99 & 2100 & 01 \\\hline 99 & \text{Rohit} & 11 & 10 & 2200 & 02 \\\hline 99 & \text{Rohit} & 11 & 99 & 2100 & 01  \end{array}$$ Now perform cross-product $((A \cup B) \times _{C.Id<15}C) $, i.e., Multiply each row of $(A∪B)$ with each row of $_{C.Id<15}C:$ $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} & \textbf{C.ID} & \textbf{Phone} & \textbf{Area} \\\hline 12 & \text{Arun} & 60 & 10 & 2200 & 02 \\\hline 15 & \text{Shreya} & 24 & 10 & 2200 & 02 \\\hline 25 & \text{Hari} & 40 & 10 & 2200 & 02  \\\hline 98 & \text{Rohit} & 20 & 10 & 2200 & 02\\\hline  99 & \text{Rohit} & 11 & 10 & 2200 & 02  \end{array}$$ Now take the union: $(_{A.Id>40}(A\cup B) \times C) \cup ((A\cup B) \times _{C.Id<15}C) $

We will get: $$\begin{array}{c c c } \hline \textbf{ID} & \textbf{Name} & \textbf{Age} & \textbf{C.ID} & \textbf{Phone} & \textbf{Area} \\\hline 12 & \text{Arun} & 60 & 10 & 2200 & 02 \\\hline 15 & \text{Shreya} & 24 & 10 & 2200 & 02 \\\hline 25 & \text{Hari} & 40 & 10 & 2200 & 02 \\\hline 98 & \text{Rohit} & 20 & 10 & 2200 & 02 \\\hline  98 & \text{Rohit} & 20 & 99 & 2100 & 01 \\\hline  99 & \text{Rohit} & 11 & 10 & 2200 & 02 \\\hline  99 & \text{Rohit} & 11 & 99 & 2100 & 01  \end{array}$$ which has $7$ Tuples, hence answer is $A.$

Comments

kiioo Yes We can because Schema is same in both A and B.

---

Hey, the answer for (A U B) X C.id < 15 C is partially right. Because the result should have 5 tuples and not just 3. The tuples with A.Id = 98 and 99 also match the join condition C.Id < 15.

---

Fixed now 👍

Answer 2

50. For C.ID = 10, all tuples from $A \cup B$ satisfies the join condition, hence 5 tuples (union of A and B has only 5 tuples are 2 of them are repeating for Shreya and Rohit)  will be returned. Now, for C.ID = 99,  A.ID = 99 and A.ID = 98 (for A.ID = 98, we need to assume A ∪ B, has the same schema s A as told in the question) satisfies the condition A.ID>40, and hence two tuples are returned. So, number of tuples = 5 + 2 = 7.

The output will be:
$$\begin{array}{llllll}\\\hline \textbf{Id}  &  \textbf{Name} & \textbf{Age}  &  \textbf{Id} &  \textbf{Phone} & \text{Area} \\\hline \text{12} & \text{Arun}& \text{60} & \text{10}& \text{2200} & \text{02}\\ \text{15} & \text{Shreya}& \text{24} & \text{10}& \text{2200} & \text{02}\\ \text{99} & \text{Rohit}& \text{11} & \text{10}& \text{2200} & \text{02}\\ \text{25} & \text{Hari}& \text{40} & \text{10}& \text{2200} & \text{02}\\ \text{98} & \text{Rohit}& \text{20} & \text{10}& \text{2200} & \text{02}\\ \text{99} & \text{Rohit}& \text{11} & \text{99}& \text{2100} & \text{01} \\ \text{98} & \text{Rohit}& \text{20} & \text{99}& \text{2100} & \text{01}\\\hline  \end{array}$$

Correct Answer: $A$

Comments

What is the need of considering AuB schema similar to A , just for A.id=98.

If we are only doing the union between A and B , and finding out the tuples validating the conditions we still get 7 tuples as output.

Plz explain.

---

agree

---

Is ID column for A and B and ID for C are different ID?Here will join work like Cartesian product?

Answer 3

Hope you find this helpful...

Comments

For easy understanding:

Let A = AUB

Tuples(A.Id>40 union C.Id<15) = Tuples(A.Id>40) + Tuples(C.Id<15) – Tuples(A.Id>40 and C.Id<15)

 = 4 + 5 – 2

=7

Answer 4

SIMPLY WE TRRAT JOIN OPERATOR AS CROSS JOIN on (AUB) cross join C then we have 10 tuples now if we apply cpnstrain on cross join A.id <40 and C.id>45 then we hav three combination

12, 99

15 ,99

25 ,99

if we subtract 3 combination from 10 it will give 7 tuples

 

now for 2nd ques inner sql give 0 and A.age will be greater tan 0

so, 3 tuples will be selectd