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.

 

---

Related qn - https://gateoverflow.in/43313/gate-cse-2012-question-51

---

True!! I understood more about conditional join!

---

join between AUB and C is not a natural join, its a cross product, so now the question will solved

---

We can easily do this by :
n(A U B) = n(A) + n(B) - n(A ^ B)

n(A) when only A.id > 40  ---> 4 cases.

n(B) when only C.id < 15  --> 5 cases

n(A ^ B ) when A.id > 40 and C.id < 15 -> 2 cases only .

n(A U B) = 4 + 5 - 2 = 7.

---

Assume that the schema of $A \cup B$ is the same as that of $A$.

this means the resulting relation of $(A \cup B)$ has attributes named $A.ID$, $A.Name$, and $A.Age$ (or simply inherits $A$'s attribute names).

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

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 👍

---

By convention we prepend the name of the relation, with a dot (.), on left, to the common attributes resulting from operations like Union, Difference, Intersection.

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

Comments

This approach is different but correct, just need some spelling check and proper condition fixing otherwise answer and approach are great.

What approach is doing exactly, it is first taking cross product of both table and subtracting non required rows from the whole cross product.

The condition of not required row can be obtained by negating the given condition i.e. applying de morgan's law. or->and, and->or '>' -> '<=' and ' < ' -> '>=' this changes will be applied when negation of given condition is taken.

!(A.id > 40 or C.id <15) = ( A.id <= 40 and C.id >= 15)

And this gives below row as output

(12,99)

(15,99)

(25,99)

and subtracting this from whole cross product i.e. 10 rows gives us 7 as answer.

Answer 5

BEST ANSWER

Answer 6

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).